Previous Topic

Next Topic

GS Export Day 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(considered_date,'MM')

Export File Details

COL_NAME

COL_LENGTH

COL_TYPE

FORMULA

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)

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)

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)

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)

RECORD_TYPE_H

132

FORMULA

'H'||RPAD(NVL(PROPERTY_CODE,' '),6)||TO_CHAR(CONSIDERED_
DATE,'YYYYMMDD')||RPAD(' ',115)||CHR(13)||CHR(10)

RECORD_TYPE_1

1

FORMULA

DECODE(RECORD_TYPE,1,RPAD('1',5),NULL)

SOURCE_CODE

7

FORMULA

DECODE(RECORD_TYPE,1,RPAD(NVL(SOURCE_CODE,' '),7),NULL)

DEPARTURE_DATE

8

FORMULA

DECODE(RECORD_TYPE,1,NVL(TO_CHAR(DEPARTURE_DATE,
'YYYYMMDD'),RPAD(' ',8)),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)

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)

POSTING_TYPE

3

FORMULA

DECODE(RECORD_TYPE,2,RPAD(NVL(SUBSTR(RTRIM(TRX_CODE)
,LENGTH(RTRIM(TRX_CODE))-3),' '),4),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)

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_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)

See Also