|
|
|
Export Definition Export Data Definition (EXPORT_DEFINITION)
The Export Definition export provides information on the header, details (columns), and delivery method for each of a specific set of export files (the component export files). The export file contains information in the following order:
EXP_EXPORT_DEFINITION
This view is a join of three tables: EXP_FILE_HDR, EXP_FILE_DTL, and EXP_FILE_DELIVERY. The view includes all the columns of the three tables; column aliases have the prefix as follows:
Table |
Alias Prefix |
EXP_FILE_HDR |
HDR |
EXP_FILE_DTL |
DTL |
EXP_FILE_DELIVERY |
DLV |
For example, the column EXP_FILE_HDR.RESORT would have the alias HDR_RESORT.
None
None
None
None
None
By default this export collects configuration data only for exports that have the file following extensions: GSR, EFD, RMT, SRC, FOR, DST, SCA, BBI, SCG, SCH, SCE, GAL.
Note: The above file extensions are based on the original requirements from Carlson for their own export configuration and assigned extensions. Any other client wishing to adapt this export will need to add a list of extensions based on their own export configuration in the filter conditions for this export.
hotel_code+business_date.file_extension_formula
'DFN'
Compress Data : 'Y'
Delimiter: '|'
Export file details/component export file details are each on a separate line followed by their respective column details. The export components will be sequenced by file type (e.g., GSR, EFD, RMT, SRC, FOR, DST, SCA, BBI, SCG, SCH, SCE, GAL).
The datatype for all columns is VARCHAR; column length is variable as per the field lengths currently allowed for the component export file details.
Fields |
Value/Column Name |
Hotel Code / Property Code |
pms_p.resort |
Export Date (YYYYMMRR) |
TO_CHAR(pms_p.business_date,'YYYYMMDD') |
Extract Extension |
'DFN' |
Fields |
Value/Column Name |
Record Type |
'HDR' |
Hotel Code / Property Code |
HDR_RESORT |
Extract extension |
HDR_FILE_EXTENSION |
Export Source |
HDR_SOURCE_VIEW_CODE |
Source Description |
HDR_SOURCE_VIEW_DESCRIPTION |
File Type |
HDR_FILE_TYPE |
File Description |
HDR_FILE_TYPE_DESC |
Pre Export Procedure |
HDR_PRE_EXP_PROC |
Post Export Procedure |
HDR_POST_EXP_PROC |
Parameter Form |
HDR_PARAMETER_FORM |
XML |
HDR_XML_YN |
XML Doc |
HDR_XML_DOC_NAME |
File Name Formula |
HDR_FILE_NAME |
File Extn Formula |
HDR_FILE_EXTENSION |
Zip File name |
HDR_ZIP_FILE_NAME |
Compress data |
HDR_COMPRESSDATA_YN |
Col Separator |
HDR_COL_SEPARATOR |
Always Export Header and Footer |
HDR_ALWAYS_HDRFOOTER_YN |
Add Newline |
HDR_ADD_NEWLINE_YN |
Inactive |
HDR_INACTIVE_YN |
Component Export |
HDR_COMPONENT_EXPORT_YN |
Night Audit Export |
HDR_RUN_IN_NA_YN |
Frequency |
HDR_NA_FREQUENCY |
Day of Week |
HDR_NA_WHEN_TO_EXPORT |
OXI Export |
HDR_FTP_UPLOAD_YN |
Type |
HDR_UPLOAD_TYPE |
Interface |
HDR_OXI_INTERFACE_ID |
HTTP Upload |
HDR_HTTP_ADDRESS |
Protocol |
HDR_TRANSFER_METHOD |
SOAP Action |
HDR_SOAP_ACTION |
Filter Condition |
HDR_WHERE_CLAUSE |
ID* |
HDR_EXP_FILE_ID This is a header ID for a component export file details record. |
Parent ID* (for component exports) |
HDR_PARENT_ID This is a header ID for the parent export file details record. |
*The ID and Parent ID help in identification of the component export as a part of the parent export details.
Line 3 onwards is per the following format until all the data columns are covered. Header columns come first, followed by data columns, followed by footer columns. The sort order within each section is DTL_ORDER_BY column.
Fields |
Value/Column Name |
Record Type |
'DTL' |
Hotel Code/Property Code |
HDR_RESORT |
Exteract extension |
HDR_FILE_EXTENSION |
Data Type (Header/Data/Footer) |
DTL_GEN_DATA_TYPE |
Field number/sequence/order by |
DTL_ORDER_BY |
Column Name |
DTL_COL_NAME |
Column Type/ Data Type |
DTL_COL_TYPE |
Alignment |
DTL_COL_ALIGNMENT |
Format |
DTL_COL_FORMAT |
Length |
DTL_COL_LENGTH |
Default Value |
DTL_DEFAULT_VALUE |
Show Prompt |
DTL_PROMPT_YN |
Formula |
DTL_FORMULA |
Variable Length |
DTL_VARIABLE_LENGTH_YN |
Fields |
Value/Column Name |
Record Type |
'DLV' |
Hotel Code/Property Code |
HDR_RESORT |
Extract extension |
HDR_FILE_EXTENSION |
Delivery Type (HTTP/HTTPS, FTP, File System) |
DLV_COMM_TYPE |
Host/URL/Directory |
DLV_HOST_URL |
User ID |
DLV_USER_ID |
Password (Encrypted) |
DLV_PASSWORD |
Passive FTP |
DLV_FTP_PASSIVE_YN |
SOAP |
DLV_SOAP_YN |
SOAP Version |
DLV_SOAP_VERSION |
SOAP Action |
DLV_SOAP_ACTION |
Directory |
DLV_DIRECTORY |
Safe Create |
DLV_SAFE_CREATE_YN |
Temp Directory |
DLV_TEMP_DIRECTORY |
Compress |
DLV_COMPRESS_YN |
ASCII Transfer |
DLV_ASCII_TRANSFER_YN |
Delivery Time |
DLV_DELIVERY_TIME |
Retry Interval |
DLV_RETRY_INTERVAL_SEC |
Retry Count |
DLV_RETRY_COUNT |
System Type |
DLV_HX_SYSTEM_TYPE |
Version |
DLV_HX_VERSION_NO |
This will then be followed by the header of the next export type.
The definitions for each export file will follow one another and can be identified by the Extract Extension value. Following is an example:
Line 1
GRANDHOTEL|20080618|DFN -- This will be 1 line added to the beginning of each day's export. Format is Property Code|Business Date| File Extension
Line 2
HDR|GRANDHOTEL|GSR|EXP_ACCOMPANYING_RESV_VIEW|GENERIC_DEPARTURES|Reservations incl Accompanying Guests|General Departures Export|exp_create_data.create_export_data|exp_create_data.delete_report_table|EXP1|N|||(SUBSTR(exp_create_data.get_hotel_code(pms_p.resort),1,5))|'GSR'||N||N|Y|N|N|Y|||N|||N||||resv_status in ('CHECKED OUT') and trunc_departure=trunc(pms_p.business_date) and room_category not like '-%'|12345|
Line 3
Begin with Header Columns if any defined or else no row for header, move to data columns:
DTL|GRANDHOTEL|GSR|H|0|CONSIDERED_DATE|DATE|L|MMDDYYYY|8||N||N
DTL|GRANDHOTEL|GSR|D|5|GUEST_FIRST_NAME|VARCHAR2|L||20||N||N
DTL|GRANDHOTEL|GSR|D|10|GUEST_NAME|VARCHAR2|L||20||N||N
DTL|GRANDHOTEL|GSR|D|15|ARRIVAL|DATE|L|MMDDYYYY|8||N||N
DTL|GRANDHOTEL|GSR|D|20|DEPARTURE|DATE|L|MMDDYYYY|8||N||N
DTL|GRANDHOTEL|GSR|D|25|ROOM|VARCHAR2|L||5||N||N
Follow this with the footer columns.
Follow this with the delivery configuration information if any.
This will then be followed by the header column of the next export type.
The final export file will look something as below having definition for 1 export.
GRANDHOTEL|20080618|DFN
HDR|GRANDHOTEL|DST|EXP_ACCOMPANYING_RESV_VIEW|GENERIC_DEPARTURES|Reservations incl Accompanying Guests|General Departures Export|exp_create_data.create_export_data|exp_create_data.delete_report_table|EXP1|N|||(SUBSTR(exp_create_data.get_hotel_code(pms_p.resort),1,5))|'DST'||N||N|Y|N|N|Y|||N|||N||||resv_status in ('CHECKED OUT') and trunc_departure=trunc(pms_p.business_date) and room_category not like '-%'|12345|
DTL|GRANDHOTEL|DST|H|0|CONSIDERED_DATE|DATE|L|MMDDYYYY|8||N||N
DTL|GRANDHOTEL|DST|D|5|GUEST_FIRST_NAME|VARCHAR2|L||20||N||N
DTL|GRANDHOTEL|DST|D|10|GUEST_NAME|VARCHAR2|L||20||N||N
DTL|GRANDHOTEL|DST|D|15|ARRIVAL|DATE|L|MMDDYYYY|8||N||N
DTL|GRANDHOTEL|DST|D|20|DEPARTURE|DATE|L|MMDDYYYY|8||N||N
DTL|GRANDHOTEL|DST|D|25|ROOM|VARCHAR2|L||5||N||N
Note: In the export file, if there are values that are not defined, then a NULL value is inserted. All the fields that are possibly visible on the various export file related screens are included in this export. None of the export related fields are omitted in this export file.
