File Export Setup for DSTAT with SFTP Delivery
Much of the data that is required daily for the accounting process is contained within the Opera PMS. This procedure is used to create a daily “export” process in Opera that will automatically export the necessary data following the night audit process making it available to accounting. The setup of these exports can only be done at an Opera PMS workstation logged in as a user with Opera Supervisory rights.
There are three daily exports to be created as follows:
- DSTATALLTRANS – all financial transactions for the day; with details of debit & credit totals for each ledger
- DSTATDAILYSTAT – basic room statistics such as occupied rooms, out of order, no shows, etc.
- MARKETEXPORT – room nights and revenue by market segment
Since this setup process must occur at the property, we are asking a representative at each of the Prime Hotels to follow this procedure as soon as possible so that the data can be collected. Assistance by phone is available through Carlson’s 3rd party contractor, Computer Ingenuity Associates (CIA). You can get help from Chris or Jim at CIA by calling (770)381-1721.
Using Cut & Paste
The steps outlined in this procedure contain a number of complex text strings. These make sense to a database programmer, but may not have much meaning to you. Do not worry about that; and just enter the text actually as it appears. To make the process go faster and be more accurate, use Cut & Paste as much as possible.
This document is laid out so that complex text appears in tables and can be easily highlighted with the mouse. Highlight text and press the Ctrl-C key combination to copy the text to the computer’s “clipboard”. The key combination Ctrl-V can be used then to paste the text into the appropriate place on the Opera screen.
Getting Started
In Opera, click on the Miscellaneous button on the top toolbar; then click File Export on the side toolbar. If the File Export button is grayed out, you do not have rights to this feature. You must then log in as a user that has Opera Supervisory rights (such as GM).

File Export
After clicking File Export, click the General button.

The initial screen contains a list of the export processes in the PMS. It is likely that your system will start with 2 “exports” named CHECKOUT_GST_DETAILS and CHECKOUT_TRX_DETAILS. These were created when Opera was installed at your property. The list shown below contains other processes (some which run automatically and some that are run on demand) and is similar to the list you’ll see after the following procedures are followed.

To start configuring the first export, click the New button. When asked if you want to start with a template, answer No.
Export #1: DSTATALLTRANS
This first export retrieves all the financial transactions for the day by transaction code. You will fill in the entries as shown in the screen below:

Note: Remember to use Cut (Ctrl-C), and Paste (Ctrl-V) to save time and prevent typing errors. Export Source can be chosen from the dropdown list by clicking on the button with the down arrow.
File Type |
DSTATALLTRANS |
File Description |
DSTAT All Transactions Daily |
Export Source |
choose EXP_ALL_TRANSACTIONS_VIEW from the list |
File Name Formula |
exp_create_data.get_hotel_code||to_char(pms_p.business_date,’yyyymmdd’) |
File Extn Formula |
‘DTB’ |
Filter Condition |
trx_date=pms_p.business_date order by trx_code |
Also:
- Make sure to check the ‘Night Audit Export’ box
- Enter the Col Separator | (this vertical bar is called a pipe character)
For delivery method put a check mark or select SFTP Upload then click to save to, click “Delivery Config” and use the following:
Authentication |
SFTP Password Authentication |
Host/URL |
Ciasoft.brickftp.com |
User Id |
TDB |
Password |
TDB |
Target Directory |
/ |
Once you have all the fields input properly, click “Validate”, after a couple seconds the work “Valid” will appear in the lower left corner of the window.

Click OK twice to save your entries. This will return you to the File Export List. Click on DSTATALLTRANS and then the Edit button. Then, click the Export Columns button (lower left).
The Export Column Details screen will contain a blank list at first. This is where we’ll define what data appears in the export.
Click the New button. This will display a list of column names. Click in the box next to each of the column names required in the following order:
- TRX_CODE
- AR_LED_CREDIT
- AR_LED_DEBIT
- CASHIER_CREDIT
- CASHIER_DEBIT
- GUEST_ACCOUNT_CREDIT
- GUEST_ACCOUNT_DEBIT
- PACKAGE_CREDIT
- PACKAGE_DEBIT
- TRX_DATE
- DEP_LED_CREDIT
- DEP_LED_DEBIT
- TRX_AMOUNT
- TB_AMOUNT_NET
- IS_DEBIT_01
- IS_CREDIT_01

Click the OK button so that the columns selected appear in the Export Column Details screen as shown above. Next, click on the TRX_DATE line to highlight it and then click the Edit button. Enter YYYYMMDD for the format and then click OK.
Click the Close button on the Export Column Details screen; click OK on the Export File Details screen. This will bring you back to the Export List. This procedure is now setup.

Export #2: DSTATDAILYSTAT
The second export retrieves daily rooms statistics. Fill in the fields as shown below:

File Type |
DSTATDAILYSTAT |
File Description |
DSTAT Daily Statistics |
Export Source |
choose EXP_MANAGER_REPORT from the list |
File Name Formula |
exp_create_data.get_hotel_code||to_char(pms_p.business_date,’yyyymmdd’) |
File Extn Formula |
‘DDS’ |
Filter Condition |
business_date=pms_p.business_date |
Also:
- Make sure to check the ‘Night Audit Export’ box
- Enter the Col Separator | (this vertical bar is called a pipe character)
For delivery method put a check mark or select SFTP Upload then click to save to, click “Delivery Config” and use the following:
Authentication |
SFTP Password Authentication |
Host/URL |
Ciasoft.brickftp.com |
User Id |
TDB |
Password |
TDB |
Target Directory |
/ |
Once you have all the fields input properly, click “Validate”, after a couple seconds the work “Valid” will appear in the lower left corner of the window.

Click OK twice to save your entries. This will return you to the File Export List. Click on DSTATDAILYSTAT and then the Edit button. Then, click the Export Columns button (lower left).

The system will ask if you want to add from a template, Choose “No”. (Note that in certain versions, the system will not ask you this question, which means you will need to add the second item in the list. Once you have added the second item, you can then choose not to add from a template. You will also want to make sure that you go back and change the “Order by” from 1 to 2 on the second item in the list.) For the first column, click the New button, then enter the following information:
Order By |
1 |
Column Name |
HOTEL_CODE |
Length |
4 |
Align |
L |
Ignore Length |
Checked |
Data Type |
Formula |
Formula |
exp_create_data.get_hotel_code |
Click OK. The first column, HOTEL_CODE will appear in the Export Column Details.
Click New again to add the additional columns, this time choose “Yes” for template question. In the box next to each of the column names required in the following order:
- BUSINESS_DATE
- ADULTS_DAY
- CHILDREN_DAY
- GUEST_DAY
- ARR_ROOM_DAY
- DEP_ROOM_DAY
- OCC_DAY
- COMP_DAY
- OOO_ROOMS_DAY
- AVAIL_ROOM_DAY
- NOSHOW_DAY
Click the OK button so that the columns selected appear in the Export Column Details screen. Next, click on the BUSINESS_DATE line to highlight it and then click the Edit button. Enter YYYYMMDD for the format and then click OK.
Click the Close button on the Export Column Details screen; click OK on the Export File Details screen. This will bring you back to the Export List. This procedure is now setup.
Export #3: Market Export
The third export retrieves daily rooms statistics. Fill in the fields as shown below:

File Type |
MARKETEXPORT |
File Description |
DSTAT Market Statistics |
Export Source |
choose EXP_MARKET_CODES_DAY from the list |
File Name Formula |
exp_create_data.get_hotel_code||to_char(pms_p.business_date,’yyyymmdd’) |
File Extn Formula |
‘DMS’ |
Filter Condition |
business_date=pms_p.business_date |
Also:
- Make sure to check the ‘Night Audit Export’ box
- Enter the Col Separator | (this vertical bar is called a pipe character)
For delivery method put a check mark or select SFTP Upload then click to save to, click “Delivery Config” and use the following:
Authentication |
SFTP Password Authentication |
Host/URL |
Ciasoft.brickftp.com |
User Id |
TDB |
Password |
TDB |
Target Directory |
/ |
Once you have all the fields input properly, click “Validate”, after a couple seconds the work “Valid” will appear in the lower left corner of the window.

Click OK twice to save your entries. This will return you to the File Export List. Click on DSTATDAILYSTAT and then the Edit button. Then, click the Export Columns button (lower left).

When the list of columns appears, close it without selecting any items. (Note that in certain versions, the system will not ask you this question, which means you will need to add the second item in the list. Once you have added the second item, you can then choose not to add from a template. You will also want to make sure that you go back and change the “Order by” from 1 to 2 on the second item in the list.) For the first column, click the New button, then enter the following information:
Order By |
1 |
Column Name |
HOTEL_CODE |
Length |
4 |
Align |
L |
Ignore Length |
Checked |
Data Type |
Formula |
Formula |
exp_create_data.get_hotel_code |
Click OK. The first column, HOTEL_CODE will appear in the Export Column Details.
Click New again to add the additional columns. In the box next to each of the column names required in the following order:
- MARKET_CODE
- BUSINESS_DATE
- DESCRIPTION
- NO_OF_ROOMS
- TOTAL_ROOM_REVENUE
Click the OK button so that the columns selected appear in the Export Column Details screen. Next, click on the BUSINESS_DATE line to highlight it and then click the Edit button. Enter YYYYMMDD for the format and then click OK.
Click the Close button on the Export Column Details screen; click OK on the Export File Details screen. This will bring you back to the Export List. This procedure is now setup.
Setup for Manual Process
At this point, you’ve setup 3 processes to run after Night Audit each day. The following procedures setup identical exports that can be run “manually”. That means that you’ll be able to retrieve the data on demand for any date. To save time, we can copy the night audit procedures as a starting point.

Click on the line containing DSTATALLTRANS; then click the Copy button. Name the new export DSTATALLTRANSM with a description of DSTAT All Transactions Manual. Click OK to insert it into the list. Next, click on DSTATALLTRANSM (just created) and then click the Edit button.

Make the following changes to the Export File Details:
File Type |
DSTATALLTRANSM |
File Description |
DSTAT All Transactions Manual |
Parameter Form |
EXP_FOR_DATE |
File Name Formula |
exp_create_data.get_hotel_code||to_char(exp_general_resv_pkg.exp_start_date(),’yyyymmdd’) |
Filter Condition |
trx_date=exp_general_resv_pkg.exp_start_date order by trx_code |
Also: Make sure to uncheck the ‘Night Audit Export’ box
Click OK to save the changes. This procedure is now ready to be run manually.

Click on the line containing DSTATDAILYSTAT; then click the Copy button. Name the new export DSTATDAILYSTATM with a description of ‘DSTAT Daily Statistics Manual’. Click OK to insert it into the list. Next, click on DSTATDAILYSTATM and then click the Edit button.

Make the following changes to the Export File Details:
File Type |
DSTATDAILYSTATM |
File Description |
DSTAT Daily Statistics Manual |
Pre Export Procedure |
rep_resv_statistics.set_start_date(exp_general_resv_pkg.exp_start_date) |
Parameter Form |
EXP_FOR_DATE |
File Name Formula |
exp_create_data.get_hotel_code||to_char(exp_general_resv_pkg.exp_start_date(),’yyyymmdd’) |
Filter Condition |
business_date=exp_general_resv_pkg.exp_start_date |
Also: Make sure to uncheck the ‘Night Audit Export’ box
Click OK to save the changes. This procedure is now ready to be run manually.

Click on the line containing MARKETEXPORT; then click the Copy button. Name the new export MARKETEXPORTM with a description of ‘DSTAT Market Statistics Manual’. Click OK to insert it into the list. Next, click on MARKETEXPORTM and then click the Edit button.

Make the following changes to the Export File Details:
File Type |
MARKETEXPORTM |
File Description |
DSTAT Market Statistics Manual |
Pre Export Procedure |
rep_resv_statistics.set_start_date(exp_general_resv_pkg.exp_start_date) |
Parameter Form |
EXP_FOR_DATE |
File Name Formula |
exp_create_data.get_hotel_code||to_char(exp_general_resv_pkg.exp_start_date(),’yyyymmdd’) |
Filter Condition |
business_date=exp_general_resv_pkg.exp_start_date |
Also: Make sure to uncheck the ‘Night Audit Export’ box
Click OK to save the changes. This procedure is now ready to be run manually.
NOTE: to run the manual export for a range of dates, set the Parameter Form to exp_by_daterange