Export APIs
The following topics provide details on available Export APIs.
- Create HESTA Statistics Exports
- Post HESTA Statistics Export
- Set / View Global Variable Name Reference
- Get Report Table ID for Last / Current Export
- Get Cached Value of Export Data Passed from Package Global Variable
- Set Start Date for Exports
- Set End Date for Exports
- Get Currency Exchange Rate
- Get Block Code for Resort / Allotment
- Get Reservation External Reference Number
- Get Reservation Guest Name
- Check for Existing Resort Export
- Get Current Business Date
- Get Parameter Value Configured for Logged-in Resort
- Get the Resort Code of the Logged-in Resort
- Set Business Date for a Given Session
- Get the Property’s Date
- Get BOF Interface Code2
- Get Checked Out Reservations for Specified Dates
- Get Considered Date of a Reservations Export
- Get Considered Date of an Export
- Get External Reference Value from Membership
- Get Hotel Code
- Get Include Guests without VAT Number Indicator
- Get Minimum Amount
- Get Report ID of the Export Data
- Get the Export Country
- Get the Export’s “From Date”
- Get the Export’s “To Date”
- Initialize Buckets Summed Value
- Initialize/Set Export Type
- Set the Export’s “End Date”
Parent topic: Exports
Create HESTA Statistics Exports
EXP_COUNTRY_CH.CREATE_HESTA_STAT_EXP(IN_DATE_FROM’,’IN_DATE_TO)This procedure acts as the pre export procedure for HESTA Statistics Exports. The procedure finds the eligible reservations in the given date range for HESTA statistics exports. If the in_date_from and in_date_to are passed as null, the procedure finds the reservations for the last 30 days based on the current business date.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_DATE_FROM |
Yes |
Date |
Beginning date range. |
|
IN_DATE_TO |
Yes |
Date |
Ending date range. |
EXAMPLE
EXP_COUNTRY_CH.CREATE_HESTA_STAT_EXP(01-JAN-2021’,’10-JAN-2021)
Parent topic: Export APIs
Post HESTA Statistics Export
EXP_COUNTRY_CH.POST_HESTA_STAT_EXPThis procedure acts as the Post Export procedure for HESTA Statistics export. The API deletes the temporary table entries made for the HESTA export creation. The API has relevance only with HESTA statistics exports.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
No |
Post export procedure. |
EXAMPLE
EXP_COUNTRY_CH.POST_HESTA_STAT_EXP;
Parent topic: Export APIs
Set / View Global Variable Name Reference
NAME_REF.SET_VIEW_INACTIVE_FLAG(IN_YN)This function sets the Global variable name_ref. g_View_Inactive_Flag with the value passed with the argument IN_YN.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_YN (DEFAULT’N’) |
Y |
VARCHAR2 |
Sets Inactive flag view. |
EXAMPLE
NAME_REF.SET_VIEW_INACTIVE_FLAG(‘Y’) This sets the global variable as Yes.
NAME_REF.SET_VIEW_INACTIVE_FLAG(‘N’) This sets the global variable as No.
Parent topic: Export APIs
Get Report Table ID for Last / Current Export
BACKLOG_BLOCKS.GET_LAST_REPORT_IDThis function returns the unique ID associated with the report_table for the Last/Current Export execution. Currently, this API is used in the WHERE CONDITION of Backlog and Room Pace Exports. The Report ID is set in the pre-export procedure of these exports.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
No |
Returns report ID for last/current export. |
EXAMPLE
WHERE_CLAUSE=export_id=backlog_blocks.get_last_report_id
Parent topic: Export APIs
Get Cached Value of Export Data Passed from Package Global Variable
EXPORT_DATA_P.GET_CACHED_NUM(IN_CODE)This function returns the value associated with the code passed in the input argument from the package global variable.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_CODE |
Yes |
VARCHAR2 |
Value of code passed. |
EXAMPLE
EXPORT_DATA_P.GET_CACHED_NUM
Parent topic: Export APIs
Set Start Date for Exports
REP_RESV_STATISTICS.SET_START_DATE(IN_DATE)This API sets the Start date for Exports like Stats by Market Code, Tiers, and Room Types. If the IN_DATE is passed as NULL, the business date will be considered as the start date for the export.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_DATE |
Yes |
Date |
Sets the Start date of Exports. |
EXAMPLE
rep_resv_statistics.SET_start_date(EXP_GENERAL_RESV_PKG.EXP_START_DATE)
Here, EXP_GENERAL_RESV_PKG.EXP_START_DATE will contain the date provided in the export parameter screen.
Parent topic: Export APIs
Set End Date for Exports
REP_RESV_STATISTICS.SET_END_DATE(IN_DATE)This API sets the end date for exports such as Stats by Market Code, Tiers, and Room Types. If the IN_DATE is passed as NULL, the business date will be considered as the end date for the export.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_DATE |
Yes |
Date |
Sets end date of exports. |
EXAMPLE
rep_resv_statistics.SET_END_date(EXP_GENERAL_RESV_PKG.EXP_start_DATE)
Here, EXP_GENERAL_RESV_PKG.EXP_START_DATE will contain the date provided in the export parameter screen.
Parent topic: Export APIs
Get Currency Exchange Rate
MULTI_CURRENCY.GET_EXCHANGE_RATEThis function converts an amount from one currency to another based on a specific exchange type, property, and date.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
FROM_CURRENCY |
Yes |
VARCHAR2 |
Returns the currency type. |
|
FROM_CURRENCY_AMT |
No |
NUMBER |
Transaction amount converted into a specific currency. |
|
TO_CURRENCY |
No |
VARCHAR2 |
Sets the currency type. |
|
EXCH_TYPE |
No |
VARCHAR2 |
Sets the currency exchange type. |
|
IN_DATE |
No |
DATE |
Sets the exchange date. |
|
IN_RESORT |
No |
VARCHAR2 |
Identifies the property. |
EXAMPLE
MULTI_CURRENCY.GET_EXCHANGE_RATE ('USD','100','INR','P','15-SEP-2021','BANGKOK')
Parent topic: Export APIs
Get Block Code for Resort / Allotment
RESERVATION_REF.GET_BLOCK_CODE(IN_RESORT','IN_ALLOTMENT_HEADER_ID)This function returns the block for a given resort/allotment header ID combination.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_RESORT |
Yes |
VARCHAR2 |
Returns block for given resort. |
|
IN_ALLOTMENT_HEADER_ID |
Yes |
NUMBER |
Returns block for given allotment header. |
EXAMPLE
Vblockcode := RESERVATION_REF.GET_BLOCK_CODE(‘TOKYO’,112356);
Parent topic: Export APIs
Get Reservation External Reference Number
RESERVATION_REF.GET_EXTERNAL_REFERENCE(IN_RESORT','IN_RESV_NAME_ID)This function returns the external reference number of a reservation. The resort and resv name id will be passed as IN arguments.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_RESORT |
Yes |
VARCHAR2 |
Passes resort name. |
|
IN_RESV_NAME_ID |
Yes |
NUMBER |
Passes resv name ID. |
EXAMPLE
Vrefno := RESERVATION_REF.GET_EXTERNAL_REFERENCE(‘TOKYO’,11223);
Parent topic: Export APIs
Get Reservation Guest Name
Vname := RESERVATION_REF.GET_NAME(IN_NAME_ID,’IN_NAME_PREFERENCE’,’IN_EXTENDED_YN')Vname := RESERVATION_REF.GET_NAME('IN_RESORT’,IN_RESV_NAME_ID,’IN_NAME_PREFERENCE’,’IN_EXTENDED_YN')This function is overloaded. It can be either invoked with NAME ID, NAME PREFERENCE AND EXTENDED_YN OR with RESORT, RESV NAME ID, NAME PREFERENCE AND EXTENDED_YN. This function returns the guest name based on the given arguments.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_NAME_ID |
Yes |
NUMBER |
Returns guest name ID. |
|
IN_NAME_PREFERENCE (DEFAULT ‘L’) |
Yes |
VARCHAR2 |
Returns guest name preference. |
|
IN_EXTENDED_YN (DEFAULT ‘N’) |
Yes |
VARCHAR2 |
Indicates extended: yes, no. |
|
IN_RESORT |
Yes |
VARCHAR2 |
Returns resort name. |
|
IN_RESV_NAME_ID |
Yes |
NUMBER |
Returns reservation ID. |
|
IN_NAME_PREFERENCE (DEFAULT ‘L’) |
Yes |
VARCHAR2 |
Returns guest name preference. |
|
IN_EXTENDED_YN (DEFAULT ‘N’) |
Yes |
VARCHAR2 |
Indicates extended: yes, no. |
EXAMPLE
Vname := RESERVATION_REF.GET_NAME(1000,’L’,’N’)
Vname := RESERVATION_REF.GET_NAME(‘TOKYO’,100010,‘L’,‘N’)
Parent topic: Export APIs
Check for Existing Resort Export
EXP_GEN.EXPORTS_EXIST(IN_EXPORT_TYPE’,’IN_RESORT)This API checks whether an export exists for a given resort.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_EXPORT_TYPE |
No |
VARCHAR2 |
Export type that maps to field: export_type in table exp_file_hdr. |
|
IN_RESORT |
Yes |
VARCHAR2 |
Resort that the export belongs to (value of the Resort column from the Resort table). |
EXAMPLE
TRX_GENERATES_EXPORT = Transaction Codes Configuration with Generate Information (Template Export).
You can use this in a formula column in export configuration, for example, (CASE WHEN exp_gen.exports_exist('TRXGEN') = 'N' THEN 'Y' ELSE FULL_REFRESH_YN END).
Parent topic: Export APIs
Get Current Business Date
PMS_P.BUSINESS_DATEThis API gets the current business date for the logged-in property.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
No |
Date |
Gets logged-in property’s current business date. |
EXAMPLE
TO_CHAR(pms_p.business_date,'MM-DD-YYYY')
This example shows a formula column created to get the current business date.
Parent topic: Export APIs
Get Parameter Value Configured for Logged-in Resort
PMS_P.GET_PARAMETER(IN_PARAMETER)Use this API to get the parameter value configured for the parameter of the logged-in resort.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_PARAMETER |
No |
VARCHAR2 |
OPERA parameter name |
EXAMPLE
PMS_P.GET_PARAMETER('DEFAULT_CURRENCY')
You can use this in the Pre Export Procedure field.
Parent topic: Export APIs
Get the Resort Code of the Logged-in Resort
PMS_P.RESORTYou can use this in a formula column to get the hotel code of the logged-in resort.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
No |
Gets resort code. |
EXAMPLE
EXP_CREATE_DATA.get_hotel_code(pms_p.resort)
Parent topic: Export APIs
Set Business Date for a Given Session
PMS_P.SET_BUSINESS_DATE(IN_NEW_DATE)You can use this to set the business date for a given session. The default value is SysDate (system date)
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_NEW_DATE |
Yes |
Date |
Sets session business date. |
EXAMPLE
PMS_P.SET_BUSINESS_DATE(12-JUN-21)
Parent topic: Export APIs
Get the Property’s Date
TZ.GET_PROPERTY_DATE(IN_RESORT’,’IN_FR_DATE)This API returns the new date-time for the given property based on Database time zone.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_RESORT |
Y |
VARCHAR2 |
Resort (value of RESORT column from RESORT table). |
|
IN_FR_DATE |
Y |
DATE |
Date that needs conversion (mostly sysdate is passed). |
EXAMPLE
tz.get_property_date(pms_p.resort, sysdate)
Parent topic: Export APIs
Get BOF Interface Code2
EXP_CREATE_BTR.GET_PROD_IDThis procedure returns the bof_code2 of an active BOF interface of the current resort.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
NONE |
NA |
NA |
NA |
EXAMPLE
EXP_CREATE_BTR.GET_PROD_ID
Parent topic: Export APIs
Get Checked Out Reservations for Specified Dates
EXP_CREATE_DATA2.CHKOUT_RES_DATAThis procedure provides Information about checked out reservations between the specified start and end dates.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
in_from_Date |
No |
DATE |
From Date. Defaults to pms_p.business_date -1 |
|
in_to_Date |
No |
DATE |
To Date. Defaults to pms_p.business_date -1 |
EXAMPLE
EXP_CREATE_DATA2.CHKOUT_RES_DATA (pms_p.business_date-1, pms_p.business_date)
Parent topic: Export APIs
Get Considered Date of a Reservations Export
EXP_GENERAL_RESV_PKG.GET_CONSIDERED_DATEThis procedure returns the From_Date of the Guests Reservations Export.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
NONE |
NA |
NA |
NA |
EXAMPLE
EXP_GENERAL_RESV_PKG.GET_CONSIDERED_DATE
Parent topic: Export APIs
Get Considered Date of an Export
EXP_CREATE_DATA.GET_CONSIDERED_DATEThis procedure returns the From_Date of the export execution.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_Default/DATE (default null) |
No |
DATE |
Export From Date. Defaults to Null |
EXAMPLE
EXP_CREATE_DATA.GET_CONSIDERED_DATE
Parent topic: Export APIs
Get External Reference Value from Membership
EXP_CREATE_DATA.GET_EXTERNAL_REFThis procedure returns the External Reference Value of the input active Membership and External Ref Types.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
IN_Resort |
YES |
VARCHAR2 |
Resort |
|
IN_Membership_Type |
YES |
VARCHAR2 |
Membership Type |
|
IN_Ref_Type |
YES |
VARCHAR2 |
Reference Type |
EXAMPLE
EXP_CREATE_DATA.GET_EXTERNAL_REF('TOKYO','VIP', 'REF1')
Parent topic: Export APIs
Get Hotel Code
EXP_CREATE_DATA.GET_HOTEL_CODEThis procedure returns the Hotel Code that corresponds to the Resort parameter, if not found than return default hotel code as in the input parameter.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
in_default |
No |
VARCHAR2 |
Hotel Code. Defaults to Null |
|
in_resort |
No |
VARCHAR2 |
Resort. Defaults to PMS_P.RESORT |
EXAMPLE
EXP_CREATE_DATA.GET_HOTEL_CODE('EXTTOKYO',pms_p.resort)
Parent topic: Export APIs
Get Include Guests without VAT Number Indicator
EXP_GENERAL_RESV_PKG.GET_INCL_PROFThis is set with exp_country.tax_export’s parameter form "TAX1". This form has a checkbox "Include Guests without VAT Number" which when checked, the EXP_GENERAL_RESV_PKG.GET_INCL_PROF will return Y, else N.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
NONE |
NA |
NA |
NA |
EXAMPLE
EXP_GENERAL_RESV_PKG.GET_INCL_PROF
Parent topic: Export APIs
Get Minimum Amount
EXP_GENERAL_RESV_PKG.GET_MIN_AMOUNTThis procedure returns the minimum amount value set for the Export.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
NONE |
NA |
NA |
NA |
EXAMPLE
EXP_GENERAL_RESV_PKG.GET_MIN_AMOUNT
Parent topic: Export APIs
Get Report ID of the Export Data
EXP_CREATE_DATA.GET_LAST_REPORT_IDThis procedure returns the last report ID stored in the global variable g_report_id, that corresponds to the REPORT_TABLE data created by other functions for the data export.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
NONE |
NA |
NA |
NA |
EXAMPLE
EXP_CREATE_DATA.GET_LAST_REPORT_ID
Parent topic: Export APIs
Get the Export Country
EXP_GENERAL_RESV_PKG.GET_EXP_COUNTRYThis procedure returns the export country of the Guests Reservations Export.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
NONE |
NA |
NA |
NA |
EXAMPLE
EXP_GENERAL_RESV_PKG.GET_EXP_COUNTRY
Parent topic: Export APIs
Get the Export’s “From Date”
EXP_GENERAL_RESV_PKG.EXP_START_DATEThis procedure returns the Export’s start date.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
NONE |
NA |
NA |
NA |
EXAMPLE
EXP_GENERAL_RESV_PKG.EXP_START_DATE
Parent topic: Export APIs
Get the Export’s “To Date”
EXP_GENERAL_RESV_PKG.EXP_END_DATEThis procedure returns the Export’s end date.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
NONE |
NA |
NA |
NA |
EXAMPLE
EXP_GENERAL_RESV_PKG.EXP_END_DATE
Parent topic: Export APIs
Initialize Buckets Summed Value
EXP_CREATE_DATA.SUM_BUCKET_INITThis procedure is used to initialize all buckets to 0 for the input “in_max_buckets” rows.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
in_max_buckets |
No |
NUMBER |
Number of Buckets. Defaults to Null |
EXAMPLE
EXP_CREATE_DATA.SUM_BUCKET_INT(10)
Parent topic: Export APIs
Initialize/Set Export Type
EXP_CREATE_DATA2.SET_EXPORT_TYPEThis procedure sets the export type to the value provided.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
in_type |
Yes |
VARCHAR2 |
Export Type |
EXAMPLE
EXP_CREATE_DATA2.SET_EXPORT_TYPE(' EFOLIO ')
Parent topic: Export APIs
Set the Export’s “End Date”
EXP_GENERAL_RESV_PKG.SET_EXP_END_DATEThis procedure sets the export end date to the value provided.
| Parameter | Required | Type | Description |
|---|---|---|---|
|
in_date |
Yes |
DATE |
Date |
EXAMPLE
EXP_GENERAL_RESV_PKG.SET_EXP_END_DATE(PMS_P.BUSINESS_DATE)
Parent topic: Export APIs