Category: DSTAT

If your email says this below, please see instructions below:

The DSTAT Income Audit was attempted for ‘Test Hotel ZZ999’ for ##/##/2018 with the following result: Choice Advantage Back Office File ZZ999_##_##_2018 was not found for import. Check that all the necessary PMS files were transmitted for this date. This must be fixed to create a Daily report and Journal Entry for this date.

Click on Reports, then go to the Accounting Reports list, click on the Back Office Report.

Make sure the date is yesterday or date you are trying to generate, click the SUBMIT button.

Once report is generated, open a new internet window to https://ciasoft.brickftp.com
(using username and password provide by CIA, if applicable)

Click on “Show in folder”

This will open a window with the file.

Use mouse to drag the new generated file to the website in the other window.

Once you have moved the file over to the website window, you can drop the file.

Once you have confirmed that the file is there, you can close the website window.

Open DSTAT

In DSTAT, click on “Enter Yesterday’s Totals” or “Edit Prior Day’s Totals”.

If there are rates that have not been mapped and you get the message below, click “OK”

In the next screen, you will find rate codes that do not have Market Segments associated with them.

Click on the down arrow and choose the Proper Market Segment.

Once you have filled them all in, click finish, this will complete the import of the files and generate the Daily Report and the Journal Entry.

If You Need Help

CIA has a Help Desk online to assist you.  You can access the Help Desk and submit a ticket by going to our website www.ciasoft.com and click the Help Desk link.  You can also submit your ticket by sending an email to support@ciasoft.com.

Technical Support is available between 9:00am and 5:00pm ET. Monday – Friday.   When submitting your ticket, please include the following:

• Company Name
• Property Name
• Your Name
• Phone Number where you can be reached
• Your Email Address
• A time that you’re likely to be near your phone (if expecting a call back)
• Which Program is causing your issue

• • DSTAT
  • CIA Financial Planning
  • DREV
  • FBREV
  • Buddy

• Is your issue affecting:
  • One computer
  • All Computers
  • System Completely Down
• A description of the problem

• If there is an Error Message, please type the message into the body of the email or paste a screen capture of the message into your email

Typically, our first response will be via email.  If a follow-up is required, we may need to setup a phone appointment.

 

The user’s registry stores the Windows location from the last time a program was opened. Sometimes this gets messed up so the program opens but the
location is “off screen”. We see this mainly when users have multiple monitors.

Log in as the user (registry settings are by user). Run ‘Regedit.exe’ Delete these key values: MainHeight, MainLeft, MainState, Maintop, mainwidth

f your email was similar to the below message, find the Section # that is out of balance in the email and the corresponding notes below for that section:

The DSTAT Income Audit was attempted for ‘Hotel ZZ999’ for##/##/20## with the following result: There is a non-zero Balance Check in Section # on ##/##/##. You will need to access DSTAT to fix the Out-Of-Balance. This must be fixed in DSTAT so that data will be complete and balanced.

Section 1 = Room Statistics

Section 2 = Room Revenue

Section 3 = Transactions

Section 1 Room Statistics – It is not very common that Section 1 is out of balance on its own, but if both Section 1 & 2 are out of balance, it is usually a sign that there is a Rate Plan that has not been mapped to its proper Market Segment. If this is the case see “There are Rate Plans in the PMS that are not mapped to Market Segments”

Section 2 Room Revenue – If both Section 1 & 2 are out of balance see “There are Rate Plans in the PMS that are not mapped to Market Segments”

Two other possibilities:

1. There was a misposting with respect to complimentary rooms.  If the user added a rate code to the reservation but neglected to zero out the rate, there is a possibility that the Comp room ran with a rate attached.  See images below that show the amounts in the “For Out of Balance Reference Only” section at the bottom by the “Balance Check” and the JE Total = -79.00 (see bottom left of image below)
   If the revenue is correct and was not supposed to be classified as Comp, you can make an adjustment in the center column above, placing it in the proper Market Segment.  If the revenue is an overstatement and should not have been posted, you will want to make an adjustment in the center column above, placing it into a Market Segment. Then make the correction in PMS to reverse the overstatement.  If you leave it Out of Balance, you will not be able to generate a balanced Journal entry. (See example below).
2. If NoShow is posted using the “RM” transaction code, instead of the “NS” code, it will create an out of balance situation.  These images show the amounts in the “For Out of Balance Reference Only” section at the bottom by the “Balance Check” and that the JE Total = -25.00 (see bottom left of image below).  To correct this, go to the Transaction section, using the Adjustment Column, credit the Room Revenue “RM” transaction and move it to the NoShow “NS” transaction code.Tthis will balance the Room Revenue Section (See image on the Right).
   Section 3 Transactions – The most common cause of an Out of Balance in the Transactions Section is the use of a new Transaction Code.  In this case, please email Support@ciasoft.com and we can add it to your setup or assist in finding the issue.

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 3^rd 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

CHECK BUDGET VALUES

(You can skip this if you are certain your budget is correct in Buddy)

1. Go into Buddy
2. From the Reports Menu, run the “BFC” “Period Year Over Year” report
3. Choose the month you are currently running in DSTAT
4. When the report is displayed, page down through the detail and verify that their are budget numbers present for the items in question.

If there are budget values, proceed to the next steps.

If there are NOT budget values, please notify us by email.

RELOADING BUDGET VALUES TO DSTAT

1. On the CIA main menu, click on Administrative Options.
2. From the menu, select “Export Forecast & Budget for DSTAT”.
3. BE CAREFUL – When the Month Selection Box is displayed it will likely be set to export next month. If it is, you will need to click no and enter the current period number before proceeding.
4. Click the Ok button after entering the current month.
5. When the export completes, exit CIA.
6. Open DSTAT
7. Select FILE from the pull down menu.
8. Click on “import Forecast & Budget from CIA”

If this does not resolve your issue, please notify us via email.