|
|
|
Nightly Export File Standards (OPERA to Sales & Catering)
The third step in the Sales & Catering Interface configuration is setting up the export file generation process for the files that will be exported from OPERA to the Sales & Catering system. Every change to a block in Sales & Catering creates an ASCII file in the Transmit (Input) subfolder where the Sales & Catering interface constantly checks for HR*.* and DR*.* files. The files are picked up by the OPERA's Sales & Catering interface and converted into OPERA specific table information and then stored in the SM IFC folder.
ASCII files that are passed to OPERA contain a control flag. This flag determines whether the control of the Block lies with OPERA or with Sales & Catering. Read more about control of a block in Sales & Catering Control vs. OPERA Control. Certain circumstances dictate where changes are made depending on which program has control over the room block.
Files exported nightly from OPERA to Sales & Catering are created upon completion of the End of Day Routine and placed in the Receive (Output) folder on the OPERA/Sales & Catering Interface PC. The information contained within the export routine includes:
It is assumed for the purpose of the interface that the calculations in OPERA concerning room pickup, property availability and check out information are always correct. Should discrepancies be found when the export files are read by the third party Sales & Catering system, the information held is updated in Sales & Catering and a discrepancy report is generated.
Prefix |
Description |
FH |
New Block and Changes - Header |
FD |
New Block and Changes - Detail |
BD |
Reservation Group Block Detail (all block details are exported) |
GC |
Guest Check Out information (for previous night) |
PH |
Pickup Detail for In House Bookings (pickup and revenue as posted on a previous night) |
PR |
Pickup Returned for Future Bookings (same as BD but including pickup) |
RN |
Reservation Number Update |
FP |
Posted Catered Revenue - In House (not recorded by OPERA) |
TD |
Total Business Detail (availability by room type by date). Length of file will be determined by values set in the availability parameter. |
The naming convention for Export Files is the two designated External Sales and Marketing letters for a file followed by the date BD052101 Block Detail 5/21/2001. When Delphi exports are edited on the Export File Details form, the formula must be, for example:
'BD'||to char(pms p.business date, 'DDMMYY')
This creates the file name 'BD'DDMMYY on the export. Do not add a file extension as Sales &Catering does not read extensions. The 'txt' formula must be left blank in order for Sales & Catering to read the file.
Note: These steps apply to both thick and thin client environments.
File Name: FH000001
This file is passed from OPERA to the third party Sales & Catering system when new blocks or changed blocks have been made in OPERA. Like the HR and DR files a detailed record, FD, must be sent with the FH file with the same corresponding numbering scheme. OPERA checks the modify date of both the Block Header and the Block Grid to determine the change as it is possible that the grid changed, without changing the header. Only blocks with a Sales & Catering ID and in OPERA Control should be sent. In the event that either the FH or the FD file has been updated then all the respective rows will be sent. For example, if the block is for 3 room types for 1 night, then one FH and three FD records will be created. The Booking ID Record occurs once for each Booking.
The Header Record File contains the following record types:
Offset |
Field |
Format |
Comment |
1 |
Property Code |
Char(5) |
OPERA Property Code |
6 |
S&C Internal ID |
Char(18) |
S&C ID as transferred to OPERA with original booking |
24 |
Reservation ID |
Char(15) |
OPERA Block Code |
39 |
Booking Name (Post as Name) |
Char(40) |
OPERA Booking Name |
79 |
Arrival Date |
Date |
Booking Arrival Date YYYYMMDD |
87 |
Departure Date |
Date |
Booking Departure Date YYYYMMDD |
95 |
Booking Status |
Char(2) |
If OPERA Status Type = CANCEL, a 'C' is populated. If OPERA status type is a non deduct inventory type, a 'T' gets written. If OPERA status type is flagged as deduct inventory a 'D' gets written. |
97 |
Meeting Type |
Char(12) |
Not Used |
109 |
New, modified or status change |
Char(1) |
'N' indicates a new booking, 'M' indicates a modification to a booking, and 'C' indicates a canceled booking |
110 |
-- Reserved - - |
Char(1) |
Not Used |
111 |
Released Flag |
Char(1) |
Not Used |
112 |
Cutoff Date |
Date |
Cutoff Date from Allotment Header YYYYMMDD |
120 |
Date Definite |
Date |
Not Used |
128 |
Booked by |
Char(3) |
Not Used |
131 |
Guestroom Source |
Char(3) |
Not Used |
134 |
Catering Source |
Char(3) |
Not Used |
137 |
Service Manager |
Char(3) |
Not Used |
140 |
Outside Lead |
Char(3) |
Source Code from booking, converted to associated code in x-reference table for Source Code Conversion |
143 |
Housing By |
Char(2) |
Reservation Method Code from booking |
145 |
Rate Type |
Char(1) |
Not Used |
146 |
Billing Rooms |
Numeric(1) |
Not Used |
147 |
Billing Catered F&B |
Numeric(1) |
Not Used |
148 |
Control Flag |
Char(1) |
'D' for External S&C control, 'F' for OPERA OPERA Control |
149 |
Billing Lounge |
Numeric(1) |
Not Used |
150 |
Billing Incidentals |
Numeric(1) |
Not Used |
151 |
Booking Comments |
Char(300) |
Not Used |
451 |
Geographic Code |
Char(3) |
Not Used |
454 |
Contract Name |
Char(4) |
Not Used |
458 |
Contract Type |
Char(1) |
Not Used |
459 |
No. of Nights |
Numeric(4) |
Calculation: Booking End Date - Booking Begin Date |
463 |
Cutoff Type |
Char(1) (A or C) |
Not Used |
464 |
# of Cutoff Days |
Char(3) |
Cutoff Days from Allotment Header |
467 |
Status Trace Date |
Date |
Not Used |
475 |
Reserved |
Char(76) |
Not Used |
Offset |
Field |
Format |
Comment |
551 |
Account Name |
Char(60) |
OPERA Account Name |
611 |
Account ID |
Char(12) |
Account ID number |
623 |
Acc Market Segment |
Char(8) |
Not Used |
631 |
Acc Contact Name |
Char(40) |
Not Used |
671 |
Acc Contact Title |
Char(40) |
Not Used |
711 |
Acc Contact St(Line1) |
Char(40) |
Account Address Line 1 |
751 |
Acc Contact St(Line2) |
Char(40) |
Account Address Line 2 |
791 |
Acc Contact City |
Char(25) |
Account City |
816 |
Acc Contact State |
Char(5) |
Account State |
821 |
Acc Contact Postal |
Char(12) |
Account Zipcode |
833 |
Acc Contact Country |
Char(12) |
Account Country |
845 |
Acc Contact Phone |
Char(24) |
Account Primary Phone Number |
869 |
Acc Contact PhoneExt. |
Char(5) |
Not Used |
874 |
Acc Contact Fax |
Char(24) |
Account Primary Fax Number |
898 |
Acc Contact FaxExt. |
Char(5) |
Not Used |
903 |
Acc Contact Telex |
Char(24) |
Not Used |
927 |
Acc Contact TelexExt. |
Char(5) |
Not Used |
Offset |
Field |
Format |
Comment |
932 |
Agency Name |
Char(60) |
OPERA Agent Name |
992 |
Agency ID |
Char(12) |
Agent ID number |
1004 |
Agency Mkt Segment |
Char(8) |
Not Used |
1012 |
Agn Contact Name |
Char(40) |
Not Used |
1052 |
Agn Contact Title |
Char(40) |
Not Used |
1092 |
Agn Contact St(Line1) |
Char(40) |
Agent Address Line 1 |
1132 |
Agn Contact St(Line2) |
Char(40) |
Agent Address Line 2 |
1172 |
Agn Contact City |
Char(25) |
Agent City |
1197 |
Agn Contact State |
Char(5) |
Agent State |
1202 |
Agn Contact Postal |
Char(12) |
Agent Zipcode |
1214 |
Agn Contact Country |
Char(12) |
Agent Country |
1226 |
Agn Contact Phone |
Char(24) |
Agent Primary Phone Number |
1250 |
Agn Contact PhoneExt. |
Char(5) |
Not Used |
1255 |
Agn Contact Fax |
Char(24) |
Agent Primary Fax Number |
1279 |
Agn Contact FaxExt. |
Char(5) |
Not Used |
1284 |
Agn Contact Telex |
Char(24) |
Not Used |
1308 |
Agn Contact TelexExt. |
Char(5) |
Not Used |
Offset |
Field |
Format |
Comment |
1313 |
Bkg Contact Name |
Char(40) |
Not Used |
1353 |
Bkg Contact Title |
Char(40) |
Not Used |
1393 |
Bkg Contact St(Line1) |
Char(40) |
Not Used |
1433 |
Bkg Contact St(Line2) |
Char(40) |
Not Used |
1473 |
Bkg Contact City |
Char(25) |
Not Used |
1498 |
Bkg Contact State |
Char(5) |
Not Used |
1503 |
Bkg Contact Postal |
Char(12) |
Not Used |
1515 |
Bkg Contact Country |
Char(12) |
Not Used |
1527 |
Bkg Contact Phone |
Char(24) |
Not Used |
1551 |
Bkg Contact PhoneExt. |
Char(5) |
Not Used |
1556 |
Bkg Contact Fax |
Char(24) |
Not Used |
1580 |
Bkg Contact FaxExt. |
Char(5) |
Not Used |
1585 |
Bkg Contact Telex |
Char(24) |
Not Used |
1609 |
Bkg Contact TelexExt. |
Char(5) |
Not Used |
Offset |
Field |
Format |
Comment |
1614 |
Field 1 |
Char(5) |
Not Used |
1619 |
Field 2 |
Char(5) |
Not Used |
1624 |
Field 3 |
Char(5) |
Not Used |
1629 |
Field 4 |
Char(5) |
Not Used |
1634 |
Field 5 |
Char(5) |
Not Used |
1639 |
Field 6 |
Char(40) |
Not Used |
1679 |
Field 7 |
Char(40) |
Not Used |
1719 |
Field 8 |
Char(40) |
Not Used |
1759 |
Field 9 |
Char(40) |
Not Used |
1799 |
Field 10 |
Char(40) |
Not Used |
1839 |
Field 11 |
Numeric(9) |
Not Used |
1848 |
Field 12 |
Numeric(9) |
Not Used |
1857 |
Field 13 |
Numeric(9) |
Not Used |
1866 |
Field 14 |
Numeric(9) |
Not Used |
1875 |
Field 15 |
Numeric(9) |
Not Used |
The Detail Record is passed from OPERA to the third part Sales & Catering system anytime a booking is created or modified in the Sales & Catering system. It contains room type and block information for the booking by occupancy (single, double, triple and quad rooms). The Detail Record repeats for each Room Type and for each night.
Offset |
Field |
Format |
Comment |
1 |
S&C internal ID |
Char(18) |
S&C ID as transferred to OPERA with original booking |
19 |
Date |
Date |
Allotment Date from Allotment Detail YYYYMMDD |
27 |
Room Type |
Char(20) |
Room Type from Allotment Detail |
47 |
No. Rooms Block |
Numeric(7) |
Number of rooms blocked minus rooms released. If none blocked, number of rooms forecasted to sell from Allotment Details |
54 |
Single Rooms Block |
Numeric(7) |
Projected Rooms at Occ 1 from Allotment Details |
61 |
Double Rooms Block |
Numeric(7) |
Projected Rooms at Occ 2 from Allotment Details |
68 |
Triple Rooms Block |
Numeric(7) |
Projected Rooms at Occ3 from Allotment Details |
75 |
Quad Rooms Block |
Numeric(7) |
Projected Rooms at Occ4 from Allotment Details |
82 |
Single Rate |
Numeric(9) |
Rate Amount 1 from Allotment Details |
91 |
Double Rate |
Numeric(9) |
Rate Amount 2 from Allotment Details |
100 |
Triple Rate |
Numeric(9) |
Rate Amount 3 from Allotment Details |
109 |
Quad Rate |
Numeric(9) |
Rate Amount 4 from Allotment Details |
118 |
No. Rooms Agreed |
Numeric(7) |
Not Used |
125 |
Single Rooms Agreed |
Numeric(7) |
Not Used |
132 |
Double Rooms Agreed |
Numeric(7) |
Not Used |
139 |
Triple Rooms Agreed |
Numeric(7) |
Not Used |
146 |
Quad Rooms Agreed |
Numeric(7) |
Not Used |
153 |
-- Reserved -- |
Char (48) |
Not Used |
This file is used to ensure groups passed from the third party Sales & Catering system to OPERA have been properly created and/or modified. The BD file is passed from OPERA to Sales & Catering on a nightly basis and contains one record per booking for each night for at least the 12 months (a longer period may be sent) starting with the current day. The OPERA Block figures in this file will contain bookings received from Sales & Catering. These figures can be used to create a discrepancy report on the Sales & Catering system. This is recommended.
The record also contains an indicator for a market segment held for the day as generated by OPERA. Up to 20 market segments may be defined as held before the carriage return/line feed signaling the end of the record appears.
If a hold is generated by OPERA, it will remain in force in Sales & Catering until it is removed by OPERA through the interface. If a salesperson tries to block space or increase a group's room usage on that date, a message is displayed explaining that the date has been placed on hold by OPERA. Sort by S&C Internal ID, Date, Room Type.
Offset |
Field |
Format |
Comment |
1 |
Property Code |
Char(5) |
OPERA Property Code |
6 |
S&C Internal ID |
Char(18) |
S&C ID as transferred to OPERA with original booking |
24 |
Reservation ID |
Char(15) |
OPERA Block Code |
39 |
Booking Name(Post as Name) |
Char(40) |
OPERA Booking Name |
79 |
Date |
Date |
Allotment Date from Allotment Detail YYYYMMDD |
87 |
Room Type |
Char(20) |
Room Type from Allotment Details , converted to associated code in x-reference table for Room Type Code Conversion |
107 |
No. Rooms Blocked |
Numeric(7) |
Number of rooms blocked. If none blocked, number of rooms forecasted to sell from Allotment Details |
114 |
Hold All Mkt Segments |
Char(1) Yes/No |
Not Used |
115 |
Mkt Segment to Hold |
Char(8) |
Not Used |
123 |
Spaces |
Char(33) |
Note Used |
148 |
Block Control Flag |
Char(1) |
This is F or D based on who is in control of the block |
The GC file is passed from OPERA to the third party Sales & Catering system on a nightly basis and contains Guest Check Out information for the previous night. This file will be used to update the Sales & Catering system's FIT Bookings with actual room pickup information as well as store guest history information in Sales & Catering.
Offset |
Field |
Format |
Comment |
1 |
Property Code |
Char(5) |
OPERA Property Code |
6 |
S&C internal ID |
Char(18) |
S&C ID as transferred to OPERA with original booking |
24 |
Reservation ID |
Char(15) |
Confirmation Number |
39 |
OPERA Master Account Code |
Char(12) |
Not Used |
51 |
OPERA Account Code |
Char(12) |
Company ID of account linked to Reservation |
63 |
OPERA Account Name |
Char(40) |
Name of account linked to Reservation |
103 |
OPERA Master Agency Code |
Char(12) |
Not Used |
115 |
OPERA Agency Code |
Char(12) |
Company ID of agent linked to Reservation |
127 |
OPERA Agency Name |
Char(40) |
Name of agent linked to Reservation |
167 |
Arrival Date |
Date |
Arrival Date YYYYMMDD |
175 |
Departure Date |
Date |
Departure Date YYYYMMDD |
183 |
Room Type |
Char(20) |
Room Type associate with Reservation , converted to linked code in x- reference table for Room Type Code Conversion |
203 |
No. of Rooms Pickup |
Numeric(7) |
Number of rooms on reservations |
210 |
No. of Guests |
Numeric(7) |
Number of adults on reservation |
217 |
Single Rooms Pickup |
Numeric(7) |
Not Used |
224 |
Double Rooms Pickup |
Numeric(7) |
Not Used |
231 |
Triple Rooms Pickup |
Numeric(7) |
Not Used |
238 |
Quad Rooms Pickup |
Numeric(7) |
Not Used |
245 |
Guest Room Revenue |
Numeric(13) |
Room Revenue associated with Reservation |
258 |
Non Guest Room Revenue |
Numeric(13) |
Sum of F&B and Other Revenue associated with Reservation |
271 |
Guest ID |
Char(12) |
Name ID of Guest |
283 |
Guest Name |
Char(40) |
Full Guest Name |
323 |
Guest Addr(1) |
Char(40) |
Guest Primary Address Line 1 |
363 |
Guest Addr(2) |
Char(40) |
Guest Primary Address Line 2 |
403 |
Guest City |
Char(25) |
Guest Primary Address City |
428 |
Guest State/Province |
Char(12) |
Guest Primary Address State |
440 |
Guest Postal Code |
Char(12) |
Guest Primary Address Zip Code |
452 |
Guest Country |
Char(12) |
Guest Primary Address Country |
464 |
Guest Payment Method |
Char(12) |
Payment Method on Reservation |
476 |
Guest Credit Card |
Char(20) |
Credit Card Type on Reservation |
496 |
Guest Credit Card Num |
Char(20) |
Credit Card Number on Reservation |
516 |
Guest Credit Card Exp |
Char(5) MM/DD |
Credit Card Expiry on Reservation |
521 |
Guest Package Code |
Char(10) |
Package Codes on Reservation |
531 |
Guest Meal Code |
Char(10) |
Not Used |
The PH file is passed from OPERA to the third party Sales & Catering system on a nightly basis and contains one record per room type, with room and guest pickup by room type and revenue by room type as posted on the previous night (current night's End of Day Routine). This information will be used to update the Sales & Catering In-House Group bookings. For a block for multiple nights, it only sends rows for the current End of Day Routine and not all past dates. The details pertaining to future dates for the same block will be sent in the PR file. Total pickup numbers should be sent by room type in single, double, triple, and quad figures. Non-room revenue (F&B revenue) as actually posted to OPERA is also passed in this file.
The PR file is passed from OPERA to the third party Sales & Catering system on a nightly basis and contains one record per room type for each night of the booking for future dates. This information will be used to update pickup count/actualizations. In the event that a block has no Sales & Catering ID it should not be sent. For the actual night of the pickup the details of the block will be passed in the PH file. Sort by Sales & Catering Internal ID and Date.
Offset |
Field |
Format |
Comment |
1 |
Property Code |
Char(5) |
OPERA Property Code |
6 |
S&C Internal ID |
Char(18) |
S&C ID as transferred to OPERA with original booking |
24 |
Reservation ID |
Char(15) |
Booking ID (Allotment Header ID) |
39 |
Booking Name(Post as Name) |
Char(40) |
Booking Name |
79 |
Date |
Date |
Allotment Date from Allotment Detail YYYYMMDD |
87 |
Room Type |
Char(20) |
Room Type from Allotment Detail s, converted to linked code in x-reference table for Room Type Code Conversion |
107 |
No. Rooms Pickup |
Numeric(7) |
Room 'Sold' from Allotment Details |
114 |
No. of Guests |
Numeric(7) |
Number of pickup rooms at Occ 1 + (Number of pickup rooms at Occ 2 * 2) + (Number of pickup rooms at Occ3 *3) + (Number of pickup rooms at Occ4 *4) |
121 |
Single Rooms Pickup |
Numeric(7) |
Pickup at Occ 1 from Allotment Detail |
128 |
Double Rooms Pickup |
Numeric(7) |
Pickup at Occ 2 from Allotment Detail |
135 |
Triple Rooms Pickup |
Numeric(7) |
Pickup at Occ 3 from Allotment Detail |
142 |
Quad Rooms Pickup |
Numeric(7) |
Pickup at Occ 4 from Allotment Detail |
149 |
Single Rate |
Numeric(9) |
Rate Amount 1 from Allotment Details |
158 |
Double Rate |
Numeric(9) |
Rate Amount 2 from Allotment Details |
167 |
Triple Rate |
Numeric(9) |
Rate Amount 3 from Allotment Details |
176 |
Quad Rate |
Numeric(9) |
Rate Amount 4 from Allotment Details |
The RN file is passed from OPERA to the third party Sales & Catering system after each new booking is created and contains the record for the new booking passed to OPERA. This record updates the Sales & Catering system with the Reservation ID, which when known should be passed in all interface records pertaining to the booking.
Offset |
Field |
Format |
Comment |
1 |
Property Code |
Char(5) |
OPERA Property Code |
6 |
S&C ID |
Char(18) |
S&C ID as transferred to OPERA with original booking |
24 |
Reservation ID |
Char(15) |
Booking ID (Block Code) |
39 |
Booking Name (Post as Name) |
Char(40) |
Booking Name |
The FP file is passed from OPERA to Sales & Catering on a nightly basis and contains the catering revenue figures and additional revenue information (as defined by the property) for the bookings on the current day as they were actually posted.
The TD file is transferred from OPERA to the third party Sales & Catering system on a nightly basis. It contains transient sold figures for all Room Types for each day in the future beginning with the current date. Each record will supply the Sales & Catering system with the Room Type detail for Non-Group business. Sales & Catering's Space-Check then looks to these figures to display the most accurate, up-to-date availability for the Sales staff. Sort by Date.
Offset |
Field |
Format |
Comment |
1 |
Property Code |
Char(5) |
OPERA Property Code |
6 |
Date |
Date |
Considered Date from Reservation Summary YYYYMMDD |
14 |
Room Type |
Char(20) |
Room Type from Reservation Summary, converted to linked code in x-reference table for Room Type Code Conversion |
34 |
Non- Group |
Numeric(7) |
Number of non group rooms from reservation summary |
See Also
