GS Export Data Definition

The Guest Stay Export is automatically generated and transmitted to the application server during the Night Audit procedure in Opera. But this export can be manually ran by selecting Miscellaneous>File Export>General or Configuration>External>Export Files. This export file is available on-demand, to allow for external and local resources to produce more up to the minute information in case of an emergency. The file transfer from Opera to the database is completed by using the Opera OXI_HUB Interface, with an HTTP address that is setup for the actual export file itself (See Export Files for details).

Export File Header

FILE_GROUP_ID	FILE_TYPE_DESC	FILE and ZIP_FILE_NAME	FILE_EXTENSION
MISC	Guest Stay Export		'0'\|\|to_char(pms_p.business_date,'MM')

Export File Details

COL_NAME	COL_LENGTH	COL_TYPE	FORMULA
DEPARTURE_DATE	8	FORMULA	DECODE(RECORD_TYPE,1,NVL(TO_CHAR(DEPARTURE_DATE, 'YYYYMMDD'),RPAD(' ',8)),NULL)
FOLIO_NUMBER	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(' ',4),NULL)
SOURCE_CODE	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(SOURCE_CODE,' '),4),NULL)
PAYMENT_CODE	2	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(PAYMENT_CODE,' '),2),NULL)
GUEST_COUNTRY_CODE	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_COUNTRY_CODE,' '),4) ,NULL)
GROUP_NAME_ID	5	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(GROUP_NAME_ID),' ') ,5),NULL)
ROOM	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(ROOM,' '),4),NULL)
RESERVATION_FOLIO_NUMBER	24	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(RESERVATION_FOLIO _NUMBER),' '),24),NULL)
RESV_NAME_ID	24	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(RESV_NAME_ID),' ') ,24),NULL)
CHANNEL_CODE	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(CHANNEL_CODE,' '),4),NULL)
CRIBS	2	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(CRIBS),' '),2),NULL)
CHILDREN	2	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(CHILDREN),' '),2) ,NULL)
RATE_CODE	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(RATE_CODE,' '),4),NULL)
PACKAGE_PLAN	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(SUBSTR(RATE_CODE,5,4),' ') ,4),NULL)
EXTRA_BEDS	2	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(EXTRA_BEDS),' '),2) ,NULL)
CREDIT_CARD_NO	24	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(CREDIT_CARD_NO,' '),24),NULL)
CREDIT_CARD_EXPIRATION	4	FORMULA	DECODE(RECORD_TYPE,1,NVL(TO_CHAR(CREDIT_CARD_ EXPIRATION,'YYMM'),' '),NULL)
MARKET_GROUP	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(MARKET_GROUP,' '),4),NULL)
GUEST_LAST_NAME	24	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_LAST_NAME,' '),24) ,NULL)
GUEST_TITLE_CODE	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_TITLE_CODE,' '),4) ,NULL)
GUEST_FIRST_NAME	10	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_FIRST_NAME,' '),10) ,NULL)
GUEST_ADDRESS_1	24	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_ADDRESS_1,' '),24) ,NULL)
GUEST_ADDRESS_2	24	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_ADDRESS_2,' '),24) ,NULL)
GUEST_CITY	16	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_CITY,' '),16),NULL)
RECORD_DELIMITER	2	FORMULA	DECODE(RECORD_TYPE,1,CHR(13)\|\|CHR(10),NULL)
RECORD_TYPE_2	1	FORMULA	DECODE(RECORD_TYPE,1,'2',NULL)
GUEST_COUNTRY_DESCRIPTION	16	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_COUNTRY_ DESCRIPTION,' '),16),NULL)
GUEST_POSTAL_CODE	12	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_POSTAL_CODE,' ') ,12),NULL)
GOLD_CARD_INDICATOR	1	FORMULA	DECODE(RECORD_TYPE,1,' ',NULL)
GROUP_NAME	24	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GROUP_NAME,' '),24),NULL)
ROOM_TYPE	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(ROOM_TYPE,' '),4),NULL)
GUEST_TELEPHONE	12	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(GUEST_TELEPHONE,' '),12), NULL)
ARRIVAL_DATE	8	FORMULA	DECODE(RECORD_TYPE,1,NVL(TO_CHAR(ARRIVAL_DATE, 'YYYYMMDD'),RPAD(' ',8)),NULL)
POINT_OF_SALE	4	FORMULA	DECODE(RECORD_TYPE,1,RPAD(' ',4),NULL)
GUEST_NAME_ID	5	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(GUEST_NAME_ID) ,' '),5),NULL)
ROOM_NIGHTS_TOTAL	2	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(ROOM_NIGHTS_ TOTAL),' '),2),NULL)
ADULTS	2	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(ADULTS),' '),2) ,NULL)
CHILDREN	2	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(TO_CHAR(CHILDREN),' ') ,2),NULL)
BED_REQUIRED_INDICATOR	1	FORMULA	DECODE(RECORD_TYPE,1,DECODE(SUBSTR(ROOM_TYPE,1,1) ,'P','N','Y'),NULL)
CHECKOUT_TIME	4	FORMULA	DECODE(RECORD_TYPE,1,NVL(TO_CHAR(ACTUAL_CHECK_ OUT_DATE,'MIHH'),RPAD(' ',4)),NULL)
GROUP_MASTER_INDICATOR	1	FORMULA	DECODE(RECORD_TYPE,1,DECODE(SUBSTR(ROOM_TYPE,1,1) ,'P','Y','N'),NULL)
SOURCE_CODE	7	FORMULA	DECODE(RECORD_TYPE,1,RPAD(NVL(SOURCE_CODE,' '),7) ,NULL)
RECORD_TYPE_H	132	FORMULA	'H'\|\|RPAD(NVL(PROPERTY_CODE,' '),6)\|\|TO_CHAR(PMS_P. BUSINESS_DATE,'YYYYMMDD')\|\|RPAD(' ',115)\|\|CHR(13)\|\|CHR(10)
RECORD_TYPE_1	1	FORMULA	DECODE(RECORD_TYPE,1,RPAD('1',5),NULL)
POSTING_TYPE	3	FORMULA	DECODE(RECORD_TYPE,2,RPAD(NVL(SUBSTR(RTRIM(TRX_CODE) ,LENGTH(RTRIM(TRX_CODE))-3),' '),4),NULL)
POSTING_CATEGORY	2	FORMULA	DECODE(RECORD_TYPE,2,RPAD(NVL(EXPORT_BUCKET,' '),2) ,NULL)
PLAN_INDICATOR	1	FORMULA	DECODE(RECORD_TYPE,2,RPAD(POSTING_PLAN_INDICATOR, 1,' '),NULL)
CRLF	2	FORMULA	DECODE(RECORD_TYPE,2,CHR(13)\|\|CHR(10),NULL)
GNR_NUMBER	10	FORMULA	DECODE(RECORD_TYPE,3,'A'\|\|RPAD(NVL(GNR_NUMBER,' '), 10),NULL)
COMPANY_ADDRESS2	30	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_ADDRESS2, ' '),30),NULL)
COMPANY_ADDRESS3	30	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_ADDRESS3, ' '),30),NULL)
COMPANY_CITY	24	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_CITY,' '),24) ,NULL)
COMPANY_STATE	24	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_STATE,' '),24),NULL)
COMPANY_POSTAL_CODE	12	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_POSTAL_CODE,' ') ,12),NULL)
COMPANY_COUNTRY	4	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_COUNTRY,' '),4) ,NULL)
COMPANY_TELEPHONE	15	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_TELEPHONE,' ') ,15),NULL)
COMPANY_FAX	15	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_FAX,' '),15) ,NULL)
COMPANY_ID_TAG	2	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_ID_TAG,' '),2) ,NULL)
CRLF	2	FORMULA	DECODE(RECORD_TYPE,3,CHR(13)\|\|CHR(10),NULL)
RECORD_TYPE_9	1	FORMULA	'9'\|\|RPAD(' ',129)\|\|CHR(13)\|\|CHR(10)
INIT	0	FORMULA	DECODE(ROWNUM- 1,0,EXP_CREATE_DATA.SUM_BUCKET_INIT(1) ,NULL)
SUM_SAVE	0	FORMULA	DECODE(RECORD_TYPE,2,EXP_CREATE_DATA.SUM_BUCKET_SAVE (1,DECODE(MARKET_GROUP,NULL,0,1)),NULL)
RE_INIT	0	FORMULA	DECODE(RECORD_TYPE,2,DECODE(EXP_CREATE_DATA.SUM_ BUCKET_GET(1),999,EXP_CREATE_DATA.SUM_BUCKET_INIT(1) ,NULL),NULL)
CRS_CONFIRMATION_NO	10	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(CRS_CONFIRMATION_NO ,' '),10),NULL)
RATE_LEVEL	1	FORMULA	DECODE(RECORD_TYPE,3,' ',NULL)
SRP_CODE	6	FORMULA	DECODE(RECORD_TYPE,3,RPAD(' ',6),NULL)
QUOTED_COST	12	FORMULA	DECODE(RECORD_TYPE,3,RPAD(' ',12),NULL)
LOYALTY_MEMBER_NO	22	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(LOYALTY_MEMBER_NO,NVL (AIRLINE_MEMBER_NO,' ')),22),NULL)
CURRENCY_CODE	3	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(PROPERTY_CURRENCY_ CODE,' '),3),NULL)
GUEST_ID_TAG	2	FORMULA	DECODE(RECORD_TYPE,3,DECODE(AIRLINE_MEMBER_NO,NULL, DECODE(LOYALTY_MEMBER_NO,NULL,' ',DECODE(LOYALTY_ MEMBER_TYPE,'MM','MM','FG')),'FF'),NULL)
RATE_CATEGORY	32	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RATE_CATEGORY,' '),32), NULL)
PROFILE_PRIVACY	141	FORMULA	DECODE(RECORD_TYPE,3,RPAD(MAIL_LIST_YN\|\|MKT_RESEARCH _YN\|\|THIRD_PARTY_YN\|\|AUTOENROLL_YN,139)\|\|CHR(13)\|\|CHR(10) ,NULL)
RECORD_TYPE_B	1	FORMULA	DECODE(RECORD_TYPE,3,'B',NULL)
AGENT_IATA_NO	22	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(AGENT_IATA_NO,' '),22),NULL)
RESERVER_NAME	60	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_NAME,' '),60),NULL)
RESERVER_CONTACT_NAME	24	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_CONTACT_NAME,' ') ,24),NULL)
RESERVER_ADDRESS1	30	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_ADDRESS1,' '),30) ,NULL)
RESERVER_ADDRESS2	30	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_ADDRESS2,' '),30) ,NULL)
RESERVER_ADDRESS3	30	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_ADDRESS3,' '),30) ,NULL)
RESERVER_CITY	24	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_CITY,' '),24),NULL)
RESERVER_STATE	24	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_STATE,' '),24),NULL)
RESERVER_POSTAL_CODE	12	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_POSTAL_CODE,' ') ,12),NULL)
RESERVER_COUNTRY	4	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_COUNTRY,' '),4),NULL)
RESERVER_TELEPHONE	15	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_TELEPHONE,' '),15) ,NULL)
RESERVER_FAX	15	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_FAX,' '),15),NULL)
RESERVER_ID_FLAG	2	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(RESERVER_ID_TAG,' '),2),NULL)
CRLF	2	FORMULA	DECODE(RECORD_TYPE,3,CHR(13)\|\|CHR(10),NULL)
RECORD_TYPE_C	1	FORMULA	DECODE(RECORD_TYPE,3,'C',NULL)
COMPANY_CORP_ID	22	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_CORP_ID,' '),22) ,NULL)
COMPANY_NAME	60	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_NAME,' '),60),NULL)
COMPANY_CONTACT_NAME	24	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_CONTACT_NAME,' ') ,24),NULL)
COMPANY_ADDRESS1	30	FORMULA	DECODE(RECORD_TYPE,3,RPAD(NVL(COMPANY_ADDRESS1,' '),30) ,NULL)
COMPANY_TYPE	1	FORMULA	DECODE(RECORD_TYPE,1,' ',NULL)
INSERT_DATE	8	FORMULA	DECODE(RECORD_TYPE,1,NVL(TO_CHAR(INSERT_DATE,'YYYYMMDD') ,RPAD(' ',8)),NULL)
RECORD_DELIMITER	2	FORMULA	DECODE(RECORD_TYPE,1,CHR(13)\|\|CHR(10),NULL)
POSTING_DATE	8	FORMULA	DECODE(RECORD_TYPE,2,'4'\|\|NVL(TO_CHAR(TRX_DATE,'YYYYMMDD') ,RPAD(' ',8)),NULL)
POSTING_REF_NUMBER	6	FORMULA	DECODE(RECORD_TYPE,2,RPAD(DECODE(MARKET_GROUP,NULL,' ', NVL(SUBSTR(MARKET_CODE,1 ,3)\|\|TO_CHAR(EXP_CREATE_DATA.SUM_BUCKET_GET(1)),' ')),6),NULL)
POSTING_ADJUSTMENT_CODE	1	FORMULA	DECODE(RECORD_TYPE,2,'0',NULL)
POSTING_AMOUNT	10	FORMULA	DECODE(RECORD_TYPE,2,RPAD(NVL(TO_CHAR(ABS(TRX_AMOUNT)),' '),10),NULL)
POSTING_AMOUNT_SIGN	1	FORMULA	DECODE(RECORD_TYPE,2,DECODE(SIGN(TRX_AMOUNT),-1,'- ','+'),NULL)
POSTING_DESCRIPTION	14	FORMULA	DECODE(RECORD_TYPE,2,RPAD(NVL(TRX_DESCRIPTION,' '),14),NULL)
CASHIER_ID	3	FORMULA	DECODE(RECORD_TYPE,2,RPAD(NVL(TO_CHAR(CASHIER_ID),' '),3),NULL)
POSTING_PAYMENT_INDICATOR	1	FORMULA	DECODE(RECORD_TYPE,2,' ',NULL)