CREATE VIEW CoAgentAllocAvail (CompanyCode, HotelCode, AvailDate, TierNumber, RoomType, RoomClass, HardRooms, SoftRooms, RoomsAvailable, RoomsReserved, UnsoldSubAllocations, RoomsArriving, RoomsStayingOver, GuaranteedRooms, ConfirmedRooms, UnconfirmedRooms, GroupRooms,RoomsCheckedIn, AdultGuests, ChildGuests, InfantGuests, UnnamedGroupRooms, ReleasedRooms) AS SELECT companies.companycode, availheader.hotelcode, availheader.availdate, availheader.tiernumber, availheader.roomtype, availheader.roomclass, availheader.hardallocrooms, availheader.softallocrooms, availheader.hardallocrooms - ifnull(availusage.hardsuballoccount,0) - ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0) - ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsAvailable, ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsReserved, ifnull(availusage.hardsuballoccount,0) as UnsoldSubAllocations, ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0)as RoomsArriving, ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsStayingOver, ifnull(availusage.incountgtd,0) + ifnull(availusage.staycountgtd,0) as GuaranteedRooms, ifnull(availusage.incountcnf,0) + ifnull(availusage.staycountcnf,0) as ConfirmedRooms, ifnull(availusage.incountuncnf,0) + ifnull(availusage.staycountuncnf,0) as UnconfirmedRooms, ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountgrprmshdfc,0) as GroupRooms, ifnull(availusage.incountcheckin,0) + ifnull(availusage.staycountcheckin,0) as RoomsCheckedIn, ifnull(availusage.inadults,0) + ifnull(availusage.stayadults,0) as AdultGuests, ifnull(availusage.inchildren,0) + ifnull(availusage.staychildren,0) as ChildGuests, ifnull(availusage.ininfants,0) + ifnull(availusage.stayinfants,0) as InfantGuests, ifnull(availusage.groupunsold,0) as UnsoldGroups, availheader.releasedgrouprooms as ReleasedRooms from availheader LEFT OUTER JOIN availusage ON availusage.availhdrptrsernum = availheader.serialnumber and availusage.availhdrptrdbnum = availheader.databasenumber, companies WHERE availheader.ownfilenumber = 1560 and availheader.ownfileptrsernum = companies.serialnumber and availheader.ownfileptrdbnum = companies.databasenumber # CREATE VIEW MarketSegAllocAvail (MarketSegmentCode, HotelCode, AvailDate, TierNumber, RoomType, RoomClass, HardRooms, SoftRooms, RoomsAvailable, RoomsReserved, UnsoldSubAllocations, RoomsArriving, RoomsStayingOver, GuaranteedRooms, ConfirmedRooms, UnconfirmedRooms, GroupRooms,RoomsCheckedIn, AdultGuests, ChildGuests, InfantGuests, UnnamedGroupRooms, ReleasedRooms) AS SELECT marketsegments.marketsegmentcode, availheader.hotelcode, availheader.availdate, availheader.tiernumber, availheader.roomtype, availheader.roomclass, availheader.hardallocrooms, availheader.softallocrooms, availheader.hardallocrooms - ifnull(availusage.hardsuballoccount,0) - ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0) - ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsAvailable, ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsReserved, ifnull(availusage.hardsuballoccount,0) as UnsoldSubAllocations, ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0)as RoomsArriving, ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsStayingOver, ifnull(availusage.incountgtd,0) + ifnull(availusage.staycountgtd,0) as GuaranteedRooms, ifnull(availusage.incountcnf,0) + ifnull(availusage.staycountcnf,0) as ConfirmedRooms, ifnull(availusage.incountuncnf,0) + ifnull(availusage.staycountuncnf,0) as UnconfirmedRooms, ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountgrprmshdfc,0) as GroupRooms, ifnull(availusage.incountcheckin,0) + ifnull(availusage.staycountcheckin,0) as RoomsCheckedIn, ifnull(availusage.inadults,0) + ifnull(availusage.stayadults,0) as AdultGuests, ifnull(availusage.inchildren,0) + ifnull(availusage.staychildren,0) as ChildGuests, ifnull(availusage.ininfants,0) + ifnull(availusage.stayinfants,0) as InfantGuests, ifnull(availusage.groupunsold,0) as UnsoldGroups, availheader.releasedgrouprooms as ReleasedRooms from availheader LEFT OUTER JOIN availusage ON availusage.availhdrptrsernum = availheader.serialnumber and availusage.availhdrptrdbnum = availheader.databasenumber, marketsegments WHERE availheader.ownfilenumber = 20 and availheader.ownfileptrsernum = marketsegments.serialnumber# CREATE VIEW BkgTypeAllocAvail (BkgTypeCode, HotelCode, AvailDate, TierNumber, RoomType, RoomClass, HardRooms, SoftRooms, RoomsAvailable, RoomsReserved, UnsoldSubAllocations, RoomsArriving, RoomsStayingOver, GuaranteedRooms, ConfirmedRooms, UnconfirmedRooms, GroupRooms,RoomsCheckedIn, AdultGuests, ChildGuests, InfantGuests, UnnamedGroupRooms, ReleasedRooms) AS SELECT tablelookups.code, availheader.hotelcode, availheader.availdate, availheader.tiernumber, availheader.roomtype, availheader.roomclass, availheader.hardallocrooms, availheader.softallocrooms, availheader.hardallocrooms - ifnull(availusage.hardsuballoccount,0) - ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0) - ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsAvailable, ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsReserved, ifnull(availusage.hardsuballoccount,0) as UnsoldSubAllocations, ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0)as RoomsArriving, ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsStayingOver, ifnull(availusage.incountgtd,0) + ifnull(availusage.staycountgtd,0) as GuaranteedRooms, ifnull(availusage.incountcnf,0) + ifnull(availusage.staycountcnf,0) as ConfirmedRooms, ifnull(availusage.incountuncnf,0) + ifnull(availusage.staycountuncnf,0) as UnconfirmedRooms, ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountgrprmshdfc,0) as GroupRooms, ifnull(availusage.incountcheckin,0) + ifnull(availusage.staycountcheckin,0) as RoomsCheckedIn, ifnull(availusage.inadults,0) + ifnull(availusage.stayadults,0) as AdultGuests, ifnull(availusage.inchildren,0) + ifnull(availusage.staychildren,0) as ChildGuests, ifnull(availusage.ininfants,0) + ifnull(availusage.stayinfants,0) as InfantGuests, ifnull(availusage.groupunsold,0) as UnsoldGroups, availheader.releasedgrouprooms as ReleasedRooms from availheader LEFT OUTER JOIN availusage ON availusage.availhdrptrsernum = availheader.serialnumber and availusage.availhdrptrdbnum = availheader.databasenumber, tablelookups WHERE availheader.ownfilenumber = 20 and availheader.ownfileptrsernum = tablelookups.serialnumber # CREATE VIEW GroupAvailability (GroupCode, GroupName, HotelCode, AvailDate, TierNumber, RoomType, RoomClass, HardRooms, SoftRooms, RoomsEntered) AS SELECT groupmaster.groupcode, names.lastname+names.firstname+names.title, availheader.hotelcode, availheader.availdate, availheader.tiernumber, availheader.roomtype, availheader.roomclass, availheader.hardallocrooms, availheader.softallocrooms, ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) FROM groupmaster, names, availheader LEFT OUTER JOIN availusage ON availusage.availhdrptrsernum = availheader.serialnumber and availusage.availhdrptrdbnum = availheader.databasenumber WHERE availheader.ownfilenumber = 1510 AND groupmaster.serialnumber = availheader.ownfileptrsernum AND groupmaster.databasenumber= availheader.ownfileptrdbnum and names.parentfilenumber=1510 and names.parentfileptrsernum=groupmaster.serialnumber and names.parentfileptrdbnum=groupmaster.databasenumber # CREATE VIEW HotelAvailability (HotelCode,AvailDate, TierNumber, RoomType, RoomClass, HardRooms, SoftRooms, RoomsAvailable, RoomsReserved, UnsoldAllocations, RoomsArriving, RoomsStayingOver, GuaranteedRooms, ConfirmedRooms, UnconfirmedRooms, GroupRooms, RoomsCheckedIn, AdultGuests, ChildGuests, InfantGuests, UnnamedGroupRooms) AS SELECT availheader.hotelcode, availheader.Availdate, availheader.tiernumber, availheader.roomtype, availheader.roomclass, availheader.hardallocrooms, availheader.softallocrooms, availheader.hardallocrooms - ifnull(availusage.hardsuballoccount,0) - ifnull(availusage.incountcheckin,0) - ifnull(availusage.incountgtd,0) - ifnull(availusage.incountcnf,0) - ifnull(availusage.incountuncnf,0) - ifnull(availusage.incountgrprmshdfc,0) - ifnull(availusage.staycountcheckin,0)- ifnull(availusage.staycountgtd,0) - ifnull(availusage.staycountcnf,0) - ifnull(availusage.staycountuncnf,0) - ifnull(availusage.staycountgrprmshdfc,0) as RoomsAvailable, ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsReserved, ifnull(availusage.hardsuballoccount,0) as UnsoldAllocations, ifnull(availusage.incountcheckin,0) + ifnull(availusage.incountgtd,0) + ifnull(availusage.incountcnf,0) + ifnull(availusage.incountuncnf,0) + ifnull(availusage.incountgrprmshdfc,0) as RoomsArriving, ifnull(availusage.staycountcheckin,0) + ifnull(availusage.staycountgtd,0) + ifnull(availusage.staycountcnf,0) + ifnull(availusage.staycountuncnf,0) + ifnull(availusage.staycountgrprmshdfc,0) as RoomsStayingOver, ifnull(availusage.incountgtd,0) + ifnull(availusage.staycountgtd,0) as GuaranteedRooms, ifnull(availusage.incountcnf,0) + ifnull(availusage.staycountcnf,0) as ConfirmedRooms, ifnull(availusage.incountuncnf,0) + ifnull(availusage.staycountuncnf,0) as UnconfirmedRooms, ifnull(availusage.incountgrprmshdfc,0) + ifnull(availusage.staycountgrprmshdfc,0) as GroupRooms, ifnull(availusage.incountcheckin,0) + ifnull(availusage.staycountcheckin,0) as CheckedInRooms, ifnull(availusage.inadults,0) + ifnull(availusage.stayadults,0) as AdultGuests, ifnull(availusage.inchildren,0) + ifnull(availusage.staychildren,0) as ChildGuests, ifnull(availusage.ininfants,0) + ifnull(availusage.stayinfants,0) as InfantGuests, ifnull(availusage.groupunsold,0) as UnnamedGroups FROM availheader LEFT OUTER JOIN availusage ON availusage.availhdrptrsernum = availheader.serialnumber and availusage.availhdrptrdbnum = availheader.databasenumber WHERE availheader.ownfilenumber = 1170 # CREATE VIEW OOOAvailability (JobCode, HotelCode, AvailDate, RoomType, RoomClass, NumberRooms) AS select maintenanceheader.maintenancenumber, availheader.hotelcode, availheader.availdate, availheader.roomtype, availheader.roomclass, availheader.hardallocrooms from maintenanceheader, availheader where availheader.ownfilenumber = 1600 and availheader.ownfileptrsernum = maintenanceheader.serialnumber and availheader.ownfileptrdbnum = maintenanceheader.databasenumber # CREATE VIEW RoomsAvailable (Hotel, Date, RoomType, RoomClass, HardRooms, SoftRooms, RoomsAvailable) AS select availheader.hotelcode as Hotel, availheader.availdate, availheader.roomtype as RoomType, availheader.roomclass as RoomClass, availheader.hardallocrooms as HardRooms, availheader.softallocrooms as SoftRooms, availheader.hardallocrooms - IFNULL(availusage.hardsuballoccount,0) - IFNULL(availusage.incountcheckin,0) - ifnull(availusage.incountgtd,0) - ifnull(availusage.incountcnf,0) - ifnull(availusage.incountuncnf,0) - ifnull(availusage.incountgrprmshdfc,0) - IFNULL(availusage.staycountcheckin,0) - ifnull(availusage.staycountgtd,0) - ifnull(availusage.staycountcnf,0)- ifnull(availusage.staycountuncnf,0) - ifnull(availusage.staycountgrprmshdfc,0) as RoomsAvailable from availheader LEFT OUTER JOIN availusage ON availusage.availhdrptrsernum = availheader.serialnumber and availusage.availhdrptrdbnum = availheader.databasenumber where availheader.ownfilenumber = 1170 #