Export APIs

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)

Post HESTA Statistics Export

EXP_COUNTRY_CH.POST_HESTA_STAT_EXP

This 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;

Set / View Global Variable Name Reference

NAME_REF.SET_VIEW_INACTIVE_FLAG(IN–YN,'NO'','IN-YN'YES')

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	Y	VARCHAR2	Sets Inactive flag view.
(DEFAULT’N’)

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.

Get Report Table ID for Last / Current Export

BACKLOG_BLOCKS.GET_LAST_REPORT_ID

This 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

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

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.

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.

Get Currency Exchange Rate

MULTI_CURRENCY.GET_EXCHANGE_RATE

This 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')

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

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

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

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

Get Current Business Date

PMS_P.BUSINESS_DATE

This 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.

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.

Get the Resort Code of the Logged-in Resort

PMS_P.RESORT

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

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)

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)