DB_STAY_NIGHT and DB_STAY_DAY Export Data Definition

The DB Stay Night Export can be automatically generated and transmitted to the application server during the End of Day procedure in Opera. (Night Audit Export should be selected on the Export File Details setup form in order to run the export during End of Day processing.) The DB Stay Day Export can be manually run for any past date by selecting Miscellaneous>File Export>General or Configuration>External>Export Files.

Configuration and Setup Requirements

OPP_EXP Export Files license is required.
OPP_MEM Membership license is required.
Revenue Buckets are setup in Configuration>Cashiering>Revenue Buckets>Bucket Type and Bucket Codes. The Revenue bucket type used for this export is DBSTAY and Buckets codes (Room, F&B, Banquet, Other) are attached this bucket type.
Membership revenue rules are setup to calculate eligible revenue for the membership type defined in the parameter setting for DEFAULT_MEMBERSHIP_TYPE(in the PROFILES parameter group)
The hotel loyalty Membership Type would be setup as the DEFAULT_MEMBERSHIP_TYPE(in the PROFILES parameter group).

The DB STAY export provides a breakout of the revenue buckets for the stay for all reservations checked out for a given date. The export first provides a summary record for each reservation checked out on a given date and then one row for each day of the stay/Billing screen window combination showing:

Totals of each revenue bucket (Room, F&B, Banquet, Other) as configured for the property.
Totals of membership-points-eligible revenue and membership- points-eligible nights for the stay as defined by the rules set up for the default membership type. (The default membership type is set by the Memberships>Default Membership Type application setting.)

File transfer can be completed via the OXI_HUB Interface, with an HTTP address that is set up for the export file via the Export File Details screen.

The file is located at:

\\<app_server>\micros\Opera\Export\<Opera_schema>\<Property>

Views Used by this Export

DBSTAY Export View: EXP_DBSTAY.

Pre Export Procedure

exp_create_dbstay.export

Explanation of the Procedure

This procedure populates the export view.

The procedure will retrieve all the checked out guests for the given date and calculate the required information.

1. DBSTAY_DETAIL : The Summary record

One row for each guest checkout for the day.
This will contain the totals for the revenue buckets for the stay,
This will contain the totals eligible revenue and eligible nights for the stay.

2. DBSTAY_DAILY_DETAIL: The details records for each day of stay

One row for each day of the stay per folio window.
Even if no revenue exists for the day, there will be a row for the day with 0 revenue information.
Revenue information will be calculated based on the revenue bucket setup. The revenue bucket totals will be total per day per folio window for each guest. The revenue buckets will be calculated as part of the export procedure and not picked up from the already calculated values in the EXPORT_BUCKET_AMOUNTS table.
Membership eligible revenue will also be calculated in the export procedure, based on the membership type setup and will not use the already existing MEMBERSHIP_TRANSACTIONS table. For the membership type defined in the parameter for DEFAULT_MEMBERSHIP_TYPE.
Membership eligible nights will also be calculated in the export procedure. For the membership type defined in the parameter for DEFAULT_MEMBERSHIP_TYPE.

Parameters Used for the Procedure

None

Post Export Procedure

exp_create_data.delete_report_table - Cleans up the data added by the pre-export procedure.

Parameter Form

Input Parameters

DB_STAY_DAY prompts for the date for which you wish to run the export. Only past dates are valid.

Filter Conditions

None

File Name Formula

trim(pms_p.resort)||to_char(considered_date,'MMDDYY')

File Extension Formula

'Z'

Export Header Record

COL_NAME	COL_LENGTH	COL_TYPE	Formula/Description
Next Record To Write	5	NUMBER	Can be all blanks.
Property Code	5	VARCHAR2	Property Code
Filler	613	VARCHAR2	Always blanks.

Export Summary Record Details

COL_NAME	COL_LENGTH	COL_TYPE	Formula/Description
ROOM	4	VARCHAR2	Room Number
ROOM_TYPE	4	VARCHAR2	Room Type
CONFIDENTAL RATE?	1	FORMULA	'0'
FILLER	2	FORMULA	' '
NO_OF_ROOMS	1	NUMBER	Number of Rooms
PMS_CONF_NO	9	VARCHAR2	The PMS Confirmation Number is divided into 3 parts: a 2-character 'HH' prefix; a 6-character number; and a 1- character suffix. The PMS Confirmation Number matches the three values contained in the Daily Record Details RESERVATION PREFIX, RESERVATION NUMBER, and RESERVATION SUFFIX.
ARRIVAL_DATE	6	FORMULA	DECODE(RECORD_TYPE,3,NVL(TO_CHAR (ARRIVAL_DATE,'MMDDYY'),RPAD(' ',6)),null) Arrival Date
DEPARTURE_DATE	6	FORMULA	DECODE(RECORD_TYPE,3,NVL(TO_CHAR (DEPARTURE_DATE,'MMDDYY'),RPAD(' ',6)),null) Departure date
RATE_CODE	6	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RATE_CODE,' ') ,6),null) Rate Code
TOUR_NUMBER	4	FORMULA	substr(block_code,instr(block_code,'-')+1) Block Code
RATE_CATEGORY	4	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RATE_CATEGORY,' ') ,4),null) Rate category
GUEST_LAST_NAME	15	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(GUEST_LAST_NAME,' '), 15),null) Last Name
GUEST_FIRST_NAME	10	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(GUEST_FIRST_NAME,' ') ,10),null) First name
GUARANTEE_CODE	1	VARCHAR2	'C'
PACKAGE_PLAN	4	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(SUBSTR(RATE_CODE,1,4) ,' '),4),null) Rate Code
ADULTS	1	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(TO_CHAR(ADULTS),' ') ,1),null) Adults
CHILDREN	1	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(TO_CHAR(CHILDREN),' ') ,1),NULL) Children
QUOTED_COST	7	FORMULA	DECODE(RECORD_TYPE,3,to_char(share_amount,'FM0000D00') ,NULL) Rate Amount
PAYMENT_CODE	2	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(PAYMENT_CODE,' '),2) ,NULL) Last Payment Method
AR_ACCOUNT_NO	5	VARCHAR2	AR Account Number
PAYMENT_DESCRIPTION	25	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(PAYMENT_CODE\|\| payment_credit_card_no,' '),25),NULL) Payment Method Description
CRS_CONFIRMATION_NO	10	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(CRS_CONFIRMATION_NO,' '),10),NULL) External Reference Number
SPECIAL_SERVICE_CODES	29	FORMULA	DECODE(RECORD_TYPE,3,guest_specials_codes,NULL) Specials
LOYALTY_MEMBER_NO	10	FORMULA	DECODE(RECORD_TYPE,3,LPAD(NVL(LOYALTY_MEMBER_NO,NVL (AIRLINE_MEMBER_NO,'0')),10),null) Loyalty Membership Number
GUEST_ADDRESS_1	25	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(GUEST_ADDRESS_1,' ') ,25),NULL) Address Line 1
GUEST_ADDRESS_2	25	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(GUEST_ADDRESS_2,' ') ,25),NULL) Address Line 2
GUEST_CITY	18	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(GUEST_CITY,' '),18),NULL) City
GUEST_STATE_CODE	2	VARCHAR2	State
GUEST_POSTAL_CODE	9	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(GUEST_POSTAL_CODE,' '),9),NULL) Postal Code
GUEST_TELEPHONE	12	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(GUEST_TELEPHONE,nvl (udfc20,' ')),12),NULL) Guest Phone Number
GUARANTEED_BY	25	FORMULA	DECODE(RECORD_TYPE,3,payment_type\|\| payment_credit_card_no,null) Payment Method on Reservation
INSERT_DATE	6	FORMULA	DECODE(RECORD_TYPE,3,NVL(TO_CHAR(INSERT_DATE, 'MMDDYY'),RPAD(' ',6)),NULL)
CHECKED_IN_DATE	6	FORMULA	DECODE(RECORD_TYPE,3,NVL(TO_CHAR(ARRIVAL_DATE, 'MMDDYY'),RPAD(' ',6)),NULL)
ACTUAL_CHECKEDOUT_DATE	6	FORMULA	DECODE(RECORD_TYPE,3,NVL(TO_CHAR (ACTUAL_CHECK_OUT_DATE,'MMDDYY'),RPAD(' ',6)),NULL)
RECORD_TYPE_C	1	FORMULA	'C'
COMPANY_CORP_ID	10	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_CORP_ID,' ') ,10),null) Corp. ID for the Company Profile
COMPANY_NAME	25	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_NAME,' ') ,25),null) Company Profile Name
RECORD_TYPE_T	1	FORMULA	'T'
AGENT_IATA_NO	10	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(AGENT_IATA_NO,' ') ,10),NULL)
AGENT_NAME	25	VARCHAR2	Travel Agent Profile Name
RECORD_TYPE_S	1	FORMULA	'S'
SOURCE_NAME	25	VARCHAR2	Source Profile Name
SOURCE_IATA_NO	10	FORMULA	' '
REVENUE_BKT1_AMOUNT_1	10	NUMBER	Total Charges for Transactions in Revenue Bucket 1
REVENUE_BKT1_AMOUNT_2	10	NUMBER	Total Charges for Transactions in Revenue Bucket 2
REVENUE_BKT1_AMOUNT_3	10	NUMBER	Total Charges for Transactions in Revenue Bucket 3
REVENUE_BKT1_AMOUNT_4	10	NUMBER	Total Charges for Transactions in Revenue Bucket 4
GOLD_PASSPORT_DOLLARS_EARNED	10	FORMULA	decode(record_type,3,to_char(NVL (LOYALTY_BASE_REVENUE,0) +NVL(LOYALTY_BONUS_REVENUE,0)+NVL (PSEUDO_TOTAL_POINTS,0),'FM0000000D00'),'0000000.00')
PROPERTY_CODE	5	VARCHAR2	Hotel Code from Property Configuration
AIRLINE_FF_NO	22	FORMULA	airline_member_no
FILLER	2	FORMULA	' '
RESERVATION_STATUS	1	FORMULA	DECODE(RESERVATION_STATUS,'CHECKED IN','I', DECODE(RESERVATION_STATUS,'CHECKED OUT','O',RESERVATION_STATUS)) Current Reservation Status
DATE_OF_LAST_CHANGE	6	FORMULA	DECODE(RECORD_TYPE,3,NVL(TO_CHAR(DEPARTURE_DATE, 'MMDDYY'),RPAD(' ',6)),NULL) Departure Date
GUEST_HISTORY_ID	10	FORMULA	DECODE(RECORD_TYPE,3,EXP_CREATE_DATA.GET_PROF_INFO (GUEST_NAME_ID,'IATA_CORP_NO'),NULL) Guest Name ID
PMS_ROOM_TYPE_DESCRIPTION	20	FORMULA	' ' PMS Room Type Description
PHONE_TYPE_INDICATOR	1	FORMULA	' '
SPIRIT_RATE_PLAN	6	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RATE_CODE,' '),6),NULL) Rate Code
COUNTRY_CODE	2	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL (GUEST_COUNTRY_CODE,' '),2),NULL) Country
CHANNEL_CODE	5	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(CHANNEL_CODE,' ') ,5),NULL)
GUEST_HISTORY_RETENTION	1	FORMULA	'1'
FAX/EMAIL/MAIL STUFF	62	FORMULA	udfc21
GDS_SOURCE	5	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(CHANNEL_CODE,' ') ,5),NULL)
FILLER_FOR_CODEPLUS	2	FORMULA	' '
GDS_SOURCE_DESCRIPTION	32	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(CHANNEL_CODE,' ') ,32),NULL)
BONUS_CODE	10	FORMULA	DECODE(RECORD_TYPE,3,RPAD(udfc40,10),null)
POV	2	VARCHAR2	Market code on reservation.
CHECKIN BY ID	3		User ID of the User that Checked In the Guest
CHECKED-OUT BY ID	3		User ID of the User that Checked Out the Guest
CHECK-IN TIME	5		HH:MM Guest Actual Check In Time
CHECK-OUT TIME	5		HH:MM Guest Actual Check Out Time
WALKIN FLAG	1		"Y" or "N"

Export Daily Record Details

COL_NAME	COL_LENGTH	COL_TYPE	Formula/Description
RECORD TYPE	1		D'
RESERVATION PREFIX	2		Hard coded 'HH'
RESERVATION NUMBER	6		PMS Confirmation Number
RESERVATION SUFFIX	1		Suffix
STAY YEAR	4		Year of Stay for Each Detailed Record
STAY MONTH	2		Month of Stay for Each Detailed Record
STAY DATE	2		Date of Stay for Each Detailed Record
FILLER	2
GROUPING NUMBER	4		Rate Code
TOUR NUMBER	4		Block Code
ROOM TYPE	4		Changed to field length of 4
GROUP CODE	6		Changed to field length of 6, Rate Code
PACKAGE PLAN	4		Rate Code
SPIRIT CONFIRMATION NUMBER	10
ROOM REVENUE FOR THIS STAY DAY (Bucket 1)	10		Changed to field length of 10. Total charges for transactions in Room Revenue Bucket.
HOTEL SPIRIT CODE	5
SPIRIT RATE PLAN	6		Rate Code
SPIRIT ROOM TYPE	4		Room Type
ORIGIN CODE 1	1		'C'
ORIGIN NUMBER 1	10		Corp. ID
ORIGIN CODE 2	1		T'
ORIGIN NUMBER 2	10		IATA Number
ORIGIN CODE 3	1		'S'
ORIGIN NUMBER 3	10		IATA Number
SPIRIT CONFIRMATION PAGE	2		Confirmation Leg Number
FOOD & BEVERAGE REVENUE OF THE DAY (Bucket 2)	10		Changed to field length of 10. Total charges for transactions in F&B Revenue Bucket.
BANQUET REVENUE OF THE DAY (Bucket 3)	10		Changed to field length of 10. Total charges for transactions in BQT Revenue Bucket.
OTHER REVENUE OF THE DAY (Bucket 4)	10		Changed to field length of 10. Total charges for transactions in Other Revenue Bucket.
GOLD PASSPORT REVENUE OF THE DAY	10		Changed to field length of 10 (1) Gold Passport member on eligible Rate Code, the value in this field will be total revenue eligible for points by folio window (2) Non Gold Passport member on eligible Rate code, the value in this field will be total revenue eligible for points by folio window (3) Gold Passport member on non eligible Rate Code, the value in this field will be "0" all folio windows (4) Non Gold Passport member on non eligible Rate Code, the value in this field will be "0" all folio windows
FILLER	97
FOLIO PAYMENT CODE	2		Changed to field length of 2. Last Payment Method of the window fo this Detail Record
FOLIO PAYMENT ACCOUNT	5
GUEST FOLIO NUMBER	1		Folio Number
GOLD PASSPORT ELIGIBLE REVENUE	1		(1) Gold Passport member on eligible Rate Code, the value in this field will be a "1" (2) Non Gold Passport member on eligible Rate Code, the value in this field will be a "1" (3) Gold Passport member on non eligible Rate Code, the value in this field will be a "0" (4) Non Gold Passport member on non eligible Rate Code, the value in this field will be a "0"
GOLD PASSPORT ELIGIBLE STAY	1		(1) Gold Passport member on eligible Rate Code, the value in this field will be a "1" (2) Non Gold Passport member on eligible Rate Code, the value in this field will be a "1" (3) Gold Passport member on non eligible Rate Code, the value in this field will be a "0" (4) Non Gold Passport member on non eligible Rate Code, the value in this field will be a "0"
FILLER	373