Setup Opera File Export for DSTAT with SFTP

You are here:
< Back

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