|
|
|
Define Records
These Define Records menu options are used to describe the structure of the export files for the transfer. If you use a standard back office interface or one of the existing interfaces which were customized for a special external system and want to customize an existing interface to your special requirements you will have to make changes here.
Each of these records is the main record definition for the export. Each record can have multiple detail records defined, which forms the different columns of the export.
Details. Displays the list of detail records for this record. These are described in detail below.
New. To add a new record. The main sequence number has to be unique, and therefore two records cannot have the same sequence number.
Edit. Highlight one of the records and select Edit to modify the record definition.
Delete. Deletes the record. Cannot delete the record unless the detail records are deleted for that record.
File Type. Indicates the file being created is a Header, Details, or Footer (HDR, DTL, or TTL). The Normal convention is to have the Header (HDR) record once on top of the export file. Multiple records of each type can exist.
Record Description. The Header, Detail, or Footer export file description.
Export File Location. The directory that the export file will be written to. In a Thick Client environment, this directory structure needs to be accessible by and common to any workstation that may run the export. In a Thin Client environment, the directory where the export will be created will be defined in the registry setting for FORMS90_OUTPUT. In a Thin Client environment the given export file path in the table should exist on the Application Server as a physical path, and not a mapped drive. Therefore, in the following example <\\NT10365\BOFEXP> \\NT10365 could be the application server and \BOFEXP should be a physical path on the application server. In a Thick Client environment, any valid mapped drive or physical paths can be used.
Export File Name. This is the definition of the export file name. This should be a valid SQL formula returning a character value. For example: 'RV'||to char(rev trns date,'rrrrmmdd')||'.txt' would create a filename of RV20010214.txt for a revenue transfer for Feb 14, 2001. Similarly, a file name of mkt trns date can be used for market statistics export, sta trns date can be used for daily statistics export, and city trns date can be used for city ledger exports to get the current export date.
Note: When the export file is created, the resulting filename will display just as it was configured in the Export File Name field. This includes the case of the text, rather it all be capitalized, all lower case, or a combination of capitalized and lower case text.
Always Append to File?. Enter N (for Header records), so if previous transfer file remains in directory current transfer would not be added to it, but would overwrite previous export with current. Enter Y (for Detail and/or Footer records) to allow the details to be added to the file created during the Header export process. Also if you are to run in Batch mode, and you wish to append to a file, you must mark all the record types to be transferred for Always Append to File? as Y. If you indicate N on one of the batch files, it will overwrite the previously transferred file if the file name is the same.
Main Sequence Number. This sequence number will be used to define the order in which these two records will be written to a file. You cannot use the same sequence number for two different records.
Table Definitions. Name of the table from which the export data is to be retrieved. The following are the Back Office export data tables :
HDR - Header |
|
Table Name |
Table Description |
BOF_EXPORT_HEADER |
Header Record |
BOF_STATISTIC_EXPORT |
Statistics Export |
DTL - Detail & TTL - Footer |
|
Table Name |
Table Description |
BOF_CITYLED_RECORDS |
Details (City Ledger) |
BOF_COUNTRYSTATS_RECORD |
Details (Country Statistics) |
BOF_CREDIT_CARD_RECORDS |
Details of Credit Card Transactions |
BOF_DAILY_STATISTICS_RECORD |
Details (Daily Statistics) |
BOF_EXPORT_HEADER |
Header Export |
BOF_FOLIOTAX_AND_CC_PAYMENTS |
Details (Folio Tax and Credit Card Payments) |
BOF_FTREVENUE_MARKET_RECORDS |
Similar to BOF_REVENUE_RECORDS, but the amounts are taken from the financial_transactions_view. |
BOF_FTREVENUE_RECORDS |
This is similar to BOF_REVENUE_RECORDS view, but the amounts will be taken from the financial_transactions_view, and not calculated as in BOF_REVENUE_RECORDS. The amounts would be in sync with the trial balance report. |
BOF_FTREVENUE_ROOMTYPE_RECORDS |
Similar to BOF_REVENUE_ROOMTYPE_RECORDS. |
BOF_MARKET_STATS_RECORD |
Details (Market Statistics) |
BOF_REVENUE_MARKET_RECORDS |
Details (Revenue Market) |
BOF_REVENUE_RECORDS |
Details (Revenue) |
BOF_REVENUE_ROOMTYPE_RECORDS |
Details (Revenue Room Type) |
BOF_REVENUE_TAX_RECORDS |
Details (Revenue Tax) |
BOF_STATISTIC_EXPORT |
Statistics Export |
Pre Export Procedure. Define a procedure to run before the export procedure. Defining a procedure here can greatly reduce the amount of time it takes for the back office export to complete running.
Note: For all backoffice exports that use the view BOF_BILL_TRX_CODE_TOTALS, the Pre Export Procedure configuration needs to be set to the following:
BOF_VIEW_REF.CREATE_BOF_TCODE_TOTALS
Post Export Procedure. Define a procedure to run before the export procedure. Defining a procedure here can greatly reduce the amount of time it takes for the back office export to complete running.
Note: For all backoffice exports that use the view BOF_BILL_TRX_CODE_TOTALS, the Post Export Procedure configuration needs to be set to the following:
EXP_CREATE_DATA.DELETE_REPORT_TABLE
General Condition. Expression by which all records are evaluated. If the record meets the condition, then that record will be written to the export file. As an example, to exclude zero postings for revenue exports, the condition would be nvl(NET AMOUNT,0)<>0.
Group By. The column name used for totaling. For example, if a column of type sum(net amount) is used anywhere in the export, a column name for which all net amounts would be totaled must be entered here This is equivalent to using the following statement in SQL SELECT SUM(NET AMOUNT) FROM BOF REVENUE RECORDS GROUP BY TRX DATE. Since the grouping is done on the TRX DATE, TRX DATE would be entered here.
Display the details of the information that will be exported. The steps from this point forward will require information from the third party accounting system on the file specs that are required, as well as a good understanding of SQL and the OPERA database. At this point you will be creating the valid expression that enables you to retrieve the required data from one of the OPERA files. During the creation of the transfer file, these expressions will be carried out in the order of their Sequence and will build the export file.
In the Details Record Sequence screen shown above, the + sign on the left side of the screen opens the Details record of each sequence.
Delete. When deleting a complete sequence it is recommended that you delete the detailed orders first. You cannot delete a collapsed line that has columns defined. To delete you must expand the records (+) and starting from the last order number, delete each column working back to the first column for that SEQ. The examples show one view collapsed and the second view expanded.
New. Add a new record. A new order will be append to the list. It is recommended to follow the sequencing on these tables.
Edit. Modify an existing record.
New/Edit. Enter new or modify the columns that will be exported. As the export line begins to have the columns added to it , it can be expanded or collapsed by clicking on the + or - sign in the extreme left column.
Order. Accept the Order default (should be 1 for a new column). If you will be adding more than one line of data in your export details section then you will need to enter 1 at the Order field for when you want to start a new line, it will give it a new Sequence number.
Data Type. From the drop down list for Data Type select one of five choices:
If this field is left blank and you type SPACE in the Column Name field you can program a blank filler space.
Length. The field length setup will default to the length of the column in the OPERA database but can also be changed. Enter the Length of the column that will be exported. Back Office does not have delimited ASCII exports, but rather a fixed length export. The length of the different columns can be customized.
Column Name. Depending on the Data Type selected when you click on the down arrow at Column Name you will see a list to choose what you need. You can also elaborate and create SQL formulas (ABS(NET AMOUNT)*100) here for the information that will be exported for that column.
Align. Used to place the text left or right. You can toggle the option button for either Align Right or Align Left of the exported column data.
Format. Enter the format for the column (Example: YYYYMMDD for a DATE column). All Number and Date columns must have a format entered.
The current active Back Office export can be exported in the End of Day Routine automatically. For this to be done, the procedure BOF PACKAGE.EXPORT BOF DATA should exist in the End of Day Routine sequence, and should be active. During the End of Day Routine all defined extracts (Revenue, Market Statistics, Daily Statistics, City Ledger) will take place.
See Also
