|
|
|
Post Stay Export Data Definition
The Departures Export is automatically generated and transmitted to the application server during the End of Day procedure in Opera. But this export can be manually run 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.
File transfer can be completed via the Opera OXI_HUB Interface, with an HTTP address that is set up for the export file (see Export Files for details).
General Reservations Export View: EXP_GENERAL_RESV_VIEW - Export view for reservation details. This view requires the Pre-Export procedure EXP_GENERAL_RESV_PKG.CREATE_EXPORT(‘PS’) to populate data.
exp_general_resv_pkg.create_export('PS')
The ‘PS’ parameter is required; this parameter indicates that this export requires post stay data.
The pre-export procedure EXP_GENERAL_RESV_PKG.CREATE_EXPORT(Type, Export_bucket_type, from_Date, to_date) is used to get data for the reservations that fall within the from date and to date range. The procedure extracts the data required and stores it in a temporary table. The view reads from this table and creates the export data. The temporary table is deleted after the export is completed.
Type. . Export type. This procedure is used for different exports. Based on the type of export the data will be retrieved.
Parameters that can be used are:
‘PS’ – Post Stay export
‘PA’ – Pre Arrival export.
‘GV1’ – GV1_EXPORT Gold passport export.
‘GL1’ - GL1_EXPORT – Airline Guests export
‘GH’ – GST HIST EXPORT - Guest History Export
If no parameter is specified, the export will look at all reservations. This is not recommended.
Export bucket type. Default used is ‘DEFAULT’ bucket type. The revenue bucket information calculated as part of this view will be based on this bucket type.
From Date and To date. Start date and end date of the export.
Default is pms_p.business_date.
exp_create_data.delete_report_table – Cleans up the data added by the Pre-Export procedure.
EXP6
From Date. Start date of export.
To Date. End date of export.
Export Bucket Type. Bucket type to be used for the export.
Export Type. Type of Export (PA,PS,GL1,GV1, GH).
export_id=exp_create_data.get_last_report_id
'PS'||UPPER(SUBSTR(PROPERTY_CODE,1,5))
‘DAT’
COL_NAME |
COL_LENGTH |
COL_TYPE |
FORMULA |
CONTROL_CHAR |
2 |
FORMULA |
'H|' |
CREATION_DATE |
9 |
FORMULA |
TO_CHAR(PMS_P.BUSINESS_DATE,'MMDDYYYY')||'|' |
CREATION_TIME |
9 |
FORMULA |
TO_CHAR(SYSDATE,'HH:MI:SS')||'|' |
FILE_TYPE |
3 |
FORMULA |
'PS|' |
TOTAL_DEPARTURE_RECORDS |
5 |
FORMULA |
LTRIM(RTRIM(TO_CHAR(TOTAL_DEPARTURE_RECORDS)))||'|' |
RECORD_ID |
4 |
FORMULA |
'D|0|' |
CONFIRMATION_NO |
11 |
FORMULA |
RTRIM(LTRIM(TO_CHAR(CONFIRMATION_NO)))||'|' |
CRS_CONFIRMATION_NO ' |
8 |
FORMULA |
RTRIM(LTRIM(UPPER(CRS_CONFIRMATION_NO)))||' |
PROPERTY_CODE |
5 ' |
FORMULA |
LTRIM(RTRIM(SUBSTR(UPPER(PROPERTY_CODE),1,5)))||'|' |
GUEST_FIRST_NAME |
20 |
FORMULA |
LTRIM(RTRIM(UPPER(GUEST_FIRST_NAME)))||'|' |
GUEST_LAST_NAME |
20 |
FORMULA |
LTRIM(RTRIM(UPPER(GUEST_LAST_NAME)))||'|' |
ARRIVAL_DATE |
10 |
FORMULA |
TO_CHAR(ARRIVAL_DATE,'MM/DD/YYYY')||'|' |
ROOM_NIGHTS_TOTAL |
5 |
FORMULA |
LTRIM(RTRIM(TO_CHAR(ROOM_NIGHTS_TOTAL)))||'|' |
ROOM_TYPE |
5 |
FORMULA |
LTRIM(RTRIM(UPPER(ROOM_TYPE)))||'|' |
SPACE |
5 |
FORMULA |
' |' |
ROOM |
3 |
FORMULA |
LTRIM(RTRIM(UPPER(ROOM)))||'|' |
SHARE_AMOUNT |
18 |
FORMULA |
LTRIM(RTRIM(TO_CHAR(MULTI_CURRENCY.GET_EXCHANGE_RATE(pms_p.get_parameter('DEFAULT_CURRENCY'),SHARE_AMOUNT,'USD'),'FM9990D00')))||'|' |
TOTAL_GUESTS |
5 |
FORMULA |
LTRIM(RTRIM(TO_CHAR(ADULTS+CHILDREN)))||'|' |
ROOM_REVENUE |
8 |
FORMULA |
LTRIM(RTRIM(TO_CHAR(MULTI_CURRENCY.GET_EXCHANGE_RATE(PMS_P.GET_PARAMETER('DEFAULT_CURRENCY') |
FB_REVENUE |
8 |
FORMULA |
LTRIM(RTRIM(TO_CHAR(MULTI_CURRENCY.GET_EXCHANGE_RATE(PMS_P.GET_PARAMETER('DEFAULT_CURRENCY'),REVENUE_BKT_AMOUNT_2,'USD'),'FM999990D00')))||'|' |
OTHER_REVENUE |
8 |
FORMULA |
LTRIM(RTRIM(TO_CHAR(MULTI_CURRENCY.GET_EXCHANGE_RATE(PMS_P.GET_PARAMETER('DEFAULT_CURRENCY'),REVENUE_BKT_AMOUNT_3,'USD'),'FM999990D00')))||'|' |
RETAIL_REVENUE |
8 |
FORMULA |
LTRIM(RTRIM(TO_CHAR(MULTI_CURRENCY.GET_EXCHANGE_RATE(PMS_P.GET_PARAMETER('DEFAULT_CURRENCY'),REVENUE_BKT_AMOUNT_4,'USD'),'FM999990D00')))||'|' |
MEMBER_CARD_NO |
9 |
FORMULA |
LTRIM(RTRIM(DEFAULT_MEMBER_CARD_NO))||'|' |
STAY_PREF |
18 |
FORMULA |
UPPER(LTRIM(RTRIM(REPLACE(UDFC38,',',':')||DECODE(UDFC38,NULL,NULL,DECODE(GUEST_SPECIALS_CODES,NULL,NULL,':'))||REPLACE(GUEST_SPECIALS_CODES,',',':'))))||'|' |
TELEPHONE |
80 |
FORMULA |
LTRIM(RTRIM(EXP_GENERAL_RESV_PKG.GET_CHARS_NUMBERS(GUEST_TELEPHONE)))||'|' |
GUEST_ADDRESS_TYPE |
1 |
FORMULA |
NVL(DECODE(GUEST_CITY,NULL,UPPER(COMPANY_ADDRESS_TYPE),UPPER(GUEST_ADDRESS_TYPE)),'H')||'|' |
COMPANY_NAME |
40 |
FORMULA |
LTRIM(RTRIM(UPPER(GUEST_COMPANY_NAME)))||'|' |
ADDRESS_1 |
40 |
FORMULA |
LTRIM(RTRIM(DECODE(GUEST_CITY,NULL,UPPER(COMPANY_ADDRESS_1),UPPER(GUEST_ADDRESS_1))))||'|' |
ADDRESS_2 |
40 |
FORMULA |
LTRIM(RTRIM(DECODE(GUEST_CITY,NULL,UPPER(COMPANY_ADDRESS_2),UPPER(GUEST_ADDRESS_2))))||'|' |
CITY |
40 |
FORMULA |
LTRIM(RTRIM(DECODE(GUEST_CITY,NULL,UPPER(COMPANY_CITY),UPPER(GUEST_CITY))))||'|' |
STATE |
3 |
FORMULA |
LTRIM(RTRIM(DECODE(GUEST_CITY,NULL,UPPER(COMPANY_STATE_CODE),UPPER(GUEST_STATE_CODE))))||'|' |
COUNTRY |
2 |
FORMULA |
LTRIM(RTRIM(DECODE(GUEST_CITY,NULL,UPPER(COMPANY_COUNTRY_CODE),UPPER(GUEST_COUNTRY_CODE))))||'|' |
POSTAL_CODE |
12 |
FORMULA |
LTRIM(RTRIM(DECODE(GUEST_CITY,NULL,UPPER(COMPANY_POSTAL_CODE),UPPER(GUEST_POSTAL_CODE))))||'|' |
CREDIT_CARD_NO |
16 |
FORMULA |
LTRIM(RTRIM(CREDIT_CARD_NO))||'|' |
PAYMENT_METHOD |
2 |
FORMULA |
LTRIM(RTRIM(DECODE(CREDIT_CARD_NO,NULL,NULL,DECODE(RESERVATION_PAYMENT_METHOD,'VA','VI',RESERVATION_PAYMENT_METHOD))))||'|' |
CREDIT_CARD_EXPIRATION |
4 |
FORMULA |
LTRIM(RTRIM(TO_CHAR(CREDIT_CARD_EXPIRATION,'MMYY')))||'|' |
RATE_CODE |
5 |
FORMULA |
LTRIM(RTRIM(SUBSTR(UPPER(RATE_CODE),1,5)))||'|' |
RATE_CODE |
6 |
FORMULA |
LTRIM(RTRIM(SUBSTR(UPPER(RATE_CODE),1,6)))||'|' |
AGENT_IATA_NO |
10 |
FORMULA |
LTRIM(RTRIM(AGENT_IATA_NO))||'|' |
AGENT_NAME |
40 |
FORMULA L |
TRIM(RTRIM(UPPER(AGENT_NAME)))||'|' |
AGENT_ADDRESS_1 |
40 |
FORMULA |
LTRIM(RTRIM(UPPER(AGENT_ADDRESS_1)))||'|' |
AGENT_ADDRESS_2 |
40 |
FORMULA |
LTRIM(RTRIM(UPPER(AGENT_ADDRESS_2)))||'|' |
AGENT_CITY |
40 |
FORMULA |
LTRIM(RTRIM(UPPER(AGENT_CITY)))||'|' |
AGENT_STATE_CODE |
3 |
FORMULA |
LTRIM(RTRIM(UPPER(AGENT_STATE_CODE)))||'|' |
AGENT_COUNTRY_CODE |
2 |
FORMULA |
LTRIM(RTRIM(UPPER(AGENT_COUNTRY_CODE)))||'|' |
POSTAL_CODE |
12 |
FORMULA |
LTRIM(RTRIM(UPPER(AGENT_POSTAL_CODE)))||'|' |
AGENT_TELEPHONE |
12 |
FORMULA |
LTRIM(RTRIM(EXP_GENERAL_RESV_PKG.GET_CHARS_NUMBERS(AGENT_TELEPHONE)))||'||||' |
FOOTER |
2 |
FORMULA |
'Z|' |
See Also
