Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
Previous |
This chapter describes the batch processing modules that ReSA uses.
The term store day is used throughout this chapter. Store day describes all transactions that occur in one business day at one store or location. Because retailers need the ability to audit transactions on a store-by-store basis for a defined period of time, store day data is maintained separately beginning with the initial import of data from the POS/OMS system.
The following diagram illustrates how data flows within ReSA and between ReSA and other applications.
Note: All integrations are not depicted in this diagram. |
Importing data from the POS to ReSA is a multi-step process that involves several ReSA batch processes.
sastdycr.pc prepares the ReSA tables for data upload.
Reference File Creation involves two processes:
sagetref.pc creates a number of reference files to be used for validation in the File Validation/Upload Process.
One of the reference data files created by sagetref contains code values from the RMS CODE_HEAD and CODE_DETAIL tables. If the codes that ReSA uses are customized during the implementation, a library must be recompiled. This is discussed in detail in the Design Assumptions section of the sagetref program level information below.
The way primary variants are set up in RMS affects the data collected by sagetref and used in the File Validation/Upload Process. This is discussed in detail in the Design Assumptions section of the sagetref program level information below.
rmst_saimptlog_promo.ksh transforms a promotions file from Oracle Retail Price Management (RPM) to the ReSA reference file format
The POS File Validation/Upload Process can be executed one of two sub-processes:
saimptlogi.c validates files and uploads their transactions into the ReSA tables. This includes (as necessary) creating errors for the auditors to address
saimptlog.c validates POS files and creates Sql*Loader Files. This includes (as necessary) creating errors for the auditors to address.
A Sql*Load process moves the transactions and errors into the ReSA tables.
Both saimptlog and saimptlogi create a voucher file to be used in later processing.
saimptlogfin.pc executes a number of import cleanup processes.
savouch.pc processes voucher sales and redemptions.
saimpadj.pc imports adjustments.
Each of the processes related to the import process are discussed in more detail at the program level later in this chapter.
saimptlogi.c and saimptlog.c perform the same business functions. Saimptlogi.c inserts directly into the database. Saimptlog uses SQL*Load to insert data. A retailer trickle polling or exporting a relatively small TLOG would be a good candidate to use saimptlogi.c. The detail discussion of these programs below contains more detail about the processing of these jobs.
By providing additional values against which auditors can compare receipts, totaling is integral to the auditing process. Totaling also provides quick access to other numeric figures about the day's transactions.
Totaling in ReSA is dynamic. ReSA automatically totals transactions based on calculation definitions that the retailer's users create using the online Totals Calculation Definition Wizard. In addition, the retailer is able to define totals that come from the POS, but that ReSA does not calculate. Whenever users create new calculation definitions or edit existing ones, they become part of the automated totaling process the next time that satotals.pc runs.
Evaluating rules is also integral to the auditing process. Rules make the comparisons among data from various sources. These comparisons find data errors that could be the result of either honest mistakes or fraud. Finding these mistakes during the auditing process prevents these errors from being passed on to other systems, (for example, a merchandising system, a data warehouse system, and so on).
Like totaling, rules in ReSA are dynamic. They are not predefined in the system-retailers have the ability to define them through the online Rules Calculation Definition Wizard.
Errors uncovered by these rules are available for review during the interactive audit. Like satotals.pc, after users modify existing rules or create new ones, they become part of the rules the next time that sarules.pc runs.
If a retailer modifies transactions during the ReSA interactive audit process, the totaling and auditing processes run again to recalculate store day totals. The batch module sapreexp.pc tracks all changed totals for the store day since the last export by comparing the latest prioritized version of each total defined for export with the version that was previously sent to each system. The module writes the changes to revision tables that the export modules later recognize as ready for export.
ReSA prepares data for export to applications after:
Some or all of the transactions for the day are imported (depending upon the application receiving ReSA's export).
Totals have run.
Audit rules have run.
Errors in transactions and totals relevant for the system receiving the associated data are eliminated or overridden. Depending upon the application, exported data consists of either transaction data or totals, or both. The process of exporting transaction data varies according to the unit of work selected in ReSA's system options. There are two units of work, transaction and store day. If the unit of work selection is transaction, ReSA exports its transactions to downstream applications, (for example, RMS, SIM, RA, and so on.) as soon as they are free of errors. If the unit of work selection is store day, transactions are not exported until all errors for that store day are either overridden or corrected. The data export jobs, to the various downstream applications, can be run multiple times in a day.
If a retailer modifies data during the interactive audit that was previously exported to RMS, ReSA export batch modules re-export the modified data in accordance with a process called full disclosure. Full disclosure means that any previously exported values (dollars, units, and so on) are fully backed out before the new value is sent.
For example: a transaction originally shows a sale of 12 items, and that transaction is exported. During the interactive audit, a retailer determines that the correct amount is 15 items, (where three are more than the original amount) and makes the change. ReSA then flags the corrected amount for export to the application.
The detail discussion of these programs below contains more detail about the processing of these jobs.
The following list summarizes the ReSA batch modules that are involved with processing POS/OMS transaction data, audit totals and rules, exports to other applications, and modifications and adjustments.
sastdycr.pc (Create Store Day for Expected Transactions)
sagetref.pc (Get Reference Data for Sales Audit Import Processing)
rmst_saimptlog_promo.ksh (Transform Promotion Reference File from RPM format to Sales Audit Import Processing File Format)
saimptlog.c/saimptlogi.c (Import of Unaudited Transaction data from POS to ReSA)
saimptloglogtdup_upd (Processing to Allow Re-Upload of Deleted Transactions)
saimptlogfin.pc (Complete Transaction Import Processing)
savouch.pc (Sales Audit Voucher Upload)
saimpadj.pc (Import Total Value Adjustments From External Systems to ReSA)
satotals.pc (Calculate Totals based on Client Defined Rules
sarules.pc (Evaluate Transactions and Totals based on Client Defined Rules)
sapreexp.pc (Prevent Duplicate Export of Total Values from ReSA)
saexprms.pc (Export of POS transactions from ReSA to RMS)
saordinvexp.pc (Export Inventory Reservation/Release for In Store Customer Order and Layaway Transactions from ReSA)
saexpdw.pc (Export from ReSA to Oracle Retail Analytics)
saexpsim.pc (Export of Revised Sale/Return Transactions from ReSA to SIM)
saexpim.pc (Export DSD and Escheatment from ReSA to Invoice Matching)
saexpgl.pc (Post User Defined Totals from ReSA to General Ledger)
ang_saplgen.ksh (Extract of POS Transactions by Store/Date from ReSA for Web Search )
saescheat.pc (Download of Escheated Vouchers from ReSA for Payment)
saescheat_nextesn.pc (Generate Next Sequence for Escheatment Processing)
saexpach.pc (Download from ReSA to Account Clearing House (ACH) System)
saexpuar.pc (Export to Universal Account Reconciliation System from ReSA)
Module Name | sastdycr.pc |
Description | Create Store Day for Expected Transactions |
Functional Area | Oracle Retail Sales Audit |
Module Type | Business Processing |
Module Technology | ProC |
Integration Catalog ID | RSA15 |
The sastdycr batch program will create store/day, import log, and export log records. This program should run prior to uploading the sales data from POS/OMS for a given store/day. Store/days will be created for any open store expecting sales
Table 29-1 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Daily - In the date set phase. |
Scheduling Considerations |
It should run before the DTESYS batch program and before the next store/day's transactions are received. |
Pre-Processing |
NA |
Post-Processing |
dtesys |
Threading Scheme |
NA |
The logical unit of work in this program is store. Records are committed to the database when the commit counter is reached. The commit counter is defined by the value of INCREMENT_BY on the ALL_SEQUENCE table for the sequence SA_STORE_DAY_SEQ_NO_SEQUENCE.
Table 29-2 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
ALL_SEQUENCES |
Yes |
No |
No |
No |
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
STORE |
Yes |
No |
No |
No |
SA_STORE_DAY |
Yes |
Yes |
No |
No |
COMPANY_CLOSED |
Yes |
No |
No |
No |
COMPANY_CLOSED_EXCEP |
Yes |
No |
No |
No |
LOCATION_CLOSED |
Yes |
No |
No |
No |
PERIOD |
Yes |
No |
No |
No |
SA_STORE_DATA |
Yes |
No |
No |
No |
SA_IMPORT_LOG |
No |
Yes |
No |
No |
SA_EXPORT_LOG |
No |
Yes |
No |
No |
SA_FLASH_SALES |
No |
Yes |
No |
No |
Module Name | sagetref.pc |
Description | Get Reference Data for Sales Audit Import Processing |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA00 |
This program will fetch all reference information needed by SAIMPTLOG.PC for validation purposes and write this information out to various output files. The following files are produced:
Items - contains a listing of all items in the system.
Wastage - contains information about all items that have wastage associated with them.
Reference Items - contains reference items, or below transaction-level items.
Primary Variant - contains primary variant information.
Variable Weight UPC - contains all variable weight Universal Product Code (UPC) definitions in the system.
Store/Days - contains all of the valid store/day combinations in the system.
Codes and Code Types - contains all code types and codes used in field level validation.
Error Codes and Descriptions - contains all error codes, error descriptions, and systems affected by the error.
Store POS Mappings
Tender Types
Merchants
Partners
Suppliers
ReSA Employees
Banners
Currency Codes
Promotions (from RPM)
Physical Warehouses
Inventory Statuses
These files will be used by the automated audit to validate information without repeatedly hitting the database.
When running sagetref.pc, retailers can either create and specify the output files, or create only the output that they desire. For example, a retailer interested in only creating a more recent employeefile would simply place a hyphen (-) in place of all the other parameters, but still specify an employeefile name. This technique can be applied to as many or as few of the parameters as retailers wish. Note, however, that the item-related files (itemfile, refitemfile, wastefile, and primvariantfile) contain significant interdependence. Thus, item files must all be created or not created together.
In the list of reference data files above, standard UOM is part of the itemfile. To obtain the value, ReSA converts the selling Unit of Measure (UOM) to the standard UOM during batch processing. This conversion enables ReSA to later export the standard UOM to the systems that require its use.
Table 29-3 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This module should be executed in the earliest phase, before the first import of RTLOGs into ReSA. |
Pre-Processing |
Sastdycr.pc |
Post-Processing |
Saimptlog.c or saimptlogi.c |
Threading Scheme |
NA |
Table 29-4 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
ITEM_MASTER |
Yes |
No |
No |
No |
ITEM_LOC |
Yes |
No |
No |
No |
VAR_UPC_EAN |
Yes |
No |
No |
No |
SA_STORE_DAY |
Yes |
No |
No |
No |
SA_STORE |
Yes |
No |
No |
No |
SA_IMPORT_LOG |
Yes |
No |
No |
No |
CURRENCIES |
Yes |
No |
No |
No |
ADDR |
Yes |
No |
No |
No |
CODE_DETAIL |
Yes |
No |
No |
No |
SA_ERROR_CODES |
Yes |
No |
No |
No |
SA_STORE_POS |
Yes |
No |
No |
No |
POS_TENDER_TYPE_HEAD |
Yes |
No |
No |
No |
NON_MERCH_CODE_HEAD |
Yes |
No |
No |
No |
PARTNER |
Yes |
No |
No |
No |
SUPS |
Yes |
No |
No |
No |
SA_STORE_EMP |
Yes |
No |
No |
No |
STORE |
Yes |
No |
No |
No |
BANNER |
Yes |
No |
No |
No |
CHANNELS |
Yes |
No |
No |
No |
CLASS |
Yes |
No |
No |
No |
VAT_CODES |
Yes |
No |
No |
No |
RPM_PROMO_OFFER_V |
Yes |
No |
No |
No |
WH |
Yes |
No |
No |
No |
INV_STATUS_CODES |
Yes |
No |
No |
No |
SA_STORE_DATA |
Yes |
No |
No |
No |
Integration Type | Download from RMS |
File Name | Determined by runtime parameter |
Integration Contract | IntCon000113 (itemfile)
IntCon000114 (wastefile) IntCon000115 (refitemfile) IntCon000116 (primvariantfile) IntCon000117 (varupcfile) IntCon000118 (storedayfile) IntCon000119 (promfile) IntCon000120 (codesfile) IntCon000121 (errorfile) IntCon000122 (storeposfile) IntCon000123 (tendertypefile) IntCon000124 (merchcodesfile) IntCon000125 (partnerfile) IntCon000126 (supplierfile) IntCon000127 (employeefile) IntCon000128 (bannerfile) IntCon000129 (promfile) IntCon000130 (whfile) IntCon000131 (invstatusfile) |
The ItemFile file name (Itemfile) is not fixed; it is determined by a runtime parameter.
Table 29-5 Itemfile - File Layout
Field Name | Field Type | Default Value | Description |
---|---|---|---|
Item |
Char(25) |
NA |
Item number |
Dept |
Number(4) |
NA |
Department ID |
Class |
Number(4) |
NA |
Class |
Subclass |
Number(4) |
NA |
Subclass ID |
Standard UOM |
Char(4) |
NA |
Standard Unit of Measure |
Catchweight Ind |
Char(1) |
NA |
Catch weight indicator |
Class vat Ind |
Char(1) |
NA |
Class Vat Ind |
The Waste Data File file name (wastefile) is not fixed; it is determined by a runtime parameter.
The Reference Item Data file name (ref_itemfile) is not fixed; it is determined by a runtime parameter.
The Primary Variant Data File file name (prim_variantfile) is not fixed; it is determined by a runtime parameter.
The Variable Weight UPC Definition File file name (varupcfile) is not fixed; it is determined by a runtime parameter.
Table 29-9 varupcfile - File Layout
Field Name | Field Type | Default Value | Description |
---|---|---|---|
Format Id |
Char(1) |
NA |
Format ID |
Format desc |
Char(20) |
NA |
Format description |
Prefix length |
Number(1) |
NA |
Pefix Length |
Begin item digit |
Number(2) |
NA |
Item digit begin |
Begin var digit |
Number(2) |
NA |
Var digit begin |
Check digit |
Number(2) |
NA |
Check digit |
Default prefix |
Number(1) |
NA |
Default prefix |
Prefix |
Number(1) |
NA |
Prefix |
The Valid Store/Day Combination File file name (storedayfile) is not fixed; it is determined by a runtime parameter.
Table 29-10 storedayfile - File Layout
Field Name | Field Type | Default Value | Description |
---|---|---|---|
Store |
Number(10) |
NA |
Store number |
Business date |
Char(8) |
NA |
Business date in YYYYMMDD format |
Store day seq no |
Number(20) |
NA |
Store day sequence number |
Day |
Number(3) |
NA |
Day |
Tran no generated |
Char(6) |
NA |
Generated transaction number |
POS data expected |
Char(1) |
NA |
If system_code is POS, then Y; otherwise N |
Currency rtl dec |
Number(1) |
NA |
Currency rtl dec |
Currency code |
Char(3) |
NA |
Currency code |
Country id |
Char(3) |
NA |
Country ID |
Vat Include Ind |
Char(1) |
NA |
Vat Include Indicator |
The Codes File file name (codesfile) is not fixed; it is determined by a runtime parameter.
The Error Information File file name (errorfile) is not fixed; it is determined by a runtime parameter.
The Store POS Mapping File file name (storeposfile) is not fixed; it is determined by a runtime parameter.
The Tender Type Mapping File file name (tendertypefile) is not fixed; it is determined by a runtime parameter.
The Merchant Code Mapping File file name (merchcodesfile) is not fixed; it is determined by a runtime parameter.
The Partner Mapping File file name (partnerfile) is not fixed; it is determined by a runtime parameter.
The Supplier Mapping File file name (supplierfile) is not fixed; it is determined by a runtime parameter.
The Employee Mapping File file name (employeefile) is not fixed; it is determined by a runtime parameter.
The Banner Information File file name (bannerfile) is not fixed; it is determined by a runtime parameter
The Currency Information File file name (currencyfile) is not fixed; it is determined by a runtime parameter.
The Promotion Information File file name (promfile) is not fixed; it is determined by a runtime parameter.
This section deals with:
After making changes in the code_detail table for code_types that ReSA uses, the library programs must be recompiled. Follow these steps:
Navigate to the $l directory and recompile libresa.a and install:
make -f retek.mk resa make -f retek.mk install
Navigate to the $c directory and recompile the next libraries:
make -f mts.mk resa-libchange
make -f mts.mk resa
Recompile the appropriate library depending upon which of the following products is being used:
resa-rms
resa-rdw
resa-ach
resa-uar
resa-im
make -f mts.mk (name of library)
make -f mts.mk resa-install
Depending upon a retailer's system parameters, the retailer designates the primary variant during item setup (through the front-end) for several reasons. One of the reasons is that, in some cases, an item may be identified at the POS by the item parent, but the item parent may have several variants.
The primary variant is established through a form at the item location level. The retailer designates which variant item is the primary variant for the current transaction level item. For more information about the new item structure in RMS, see the Oracle Retail Merchandising System User Guide.
In the example shown in the diagram below, the retailer has established their transaction level as an Item Level 2. Note that the level of the primary variant is Item Level 1, and Item Level 3 is the sub-transaction level (the refitem).
The retailer set up golf shirts in the merchandising system as its Item Level 1 above the transaction level. The retailer set up two items at level 2 (the transaction level) based on size (small and medium). Note that the retailer assigned the level 2 items to all of the available locations (Minneapolis, China, and Fargo). The retailer also designated a primary variant for a single location - a medium golf shirt, in the case of Minneapolis, and a small golf shirt, in the case of China. The retailer failed to designate a primary variant for Fargo.
The primary variant affects ReSA in the following way. Sometimes a POS system does not provide ReSA with item level 2 (transaction item) data. For example, assume that the POS system in Minneapolis sold 10 medium golf shirts and 10 small golf shirts but only informed ReSA that 20 golf shirts were sold. 20 golf shirts presents a problem for ReSA because it can only interpret items at item level 2 (the transaction level). Thus, because medium golf shirts was the chosen primary variant for Minneapolis, the SAGETREF.PC module automatically transforms the 20 golf shirts into 20 medium golf shirts. If the same type of POS system in China informed ReSA of 20 golf shirts (instead of the 10 medium and 10 small that were sold), the sagetref.pc module would transform the 20 golf shirts sold in China into 20 small golf shirts. As the table shows, small golf shirts was the chosen primary variant for the China location. ReSA then goes on to export the data at the item 2 level (the transaction level) to, for example, a merchandising system, a data warehouse, and so on.
Note: Depending upon system parameters, if a retailer fails to set up the primary variant for a location, an invalid item error is generated during batch processing. In the example below, if the POS system in Fargo sold 10 medium golf shirts and 10 small golf shirts, but only informed ReSA that 20 golf shirts were sold, the sagetref.pc module would not have a way to transform those 20 golf shirts to the transaction level. Because ReSA can only interpret items above the transaction level in conjunction with a primary variant, the invalid item error would occur during batch processing. |
Module Name | saimptlog.c
saimptlogi.c |
Description | Import of Unaudited Transaction data from POS to ReSA |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Catalog ID | RSA11a
RSA11b |
Runtime Parameters | NA |
Importing POS and Order Management System (OMS) data to ReSA is a five or six-step process depending on whether saimptlogi or saimptlog is used. Saimptlog produces SQL*Loader files while saimptlogi does inserts directly into the database. Saimptlogi is meant for use in a trickle feed environment.
To import POS and OMS data, perform the following:
SAGETREF must be run to generate the current reference files:
Items
Wastage
Sub-transaction level items
Primary variant relationships
Variable weight PLU
Store business day
Code types
Error codes
Store POS
Tender type
Merchant code types
Partner vendors
Supplier vendors
Employee ids
Banner ids
Currency File
Promotions File
Warehouse File
Inventory Status File
These files are all used as input to SAIMPTLOG and SAIMPTLOGI. Because SAIMPTLOG and SAIMPTLOGI can be threaded, this boosts performance by limiting interaction with the database.
Either SAIMPTLOG or SAIMPTLOGI must be run against each file. The files are the transaction log files in Oracle Retail compatible format called RTLOG. The retailer is responsible for converting its transaction logs to RTLOGs. Both SAIMPTLOG and SAIMPTLOGI create a write lock, depending on the locking level specified in the ReSA System Options. It will create a write lock for a store/day combination on ReSA tables if the locking level indicated is Store Day. Otherwise, it will create a write lock for a transaction on ReSA Tables if the locking level indicated is Transaction. It will then set the data_status to loading until SAIMPTLOGFIN is executed. SAIMPTLOG generates distinct SQL*Loader files for that store/day for the sa_tran_head, sa_tran_item, sa_tran_disc, sa_tran_igtax (item Level Tax not VAT), sa_tran_payment (Payment details), sa_tran_tax, sa_tran_tender, sa_error, sa_customer, sa_cust_attrib, sa_tran_write_lock and sa_missing_tran tables, whereas SAIMPTLOGI inserts data to the database directly. Both produce an Oracle Retail formatted voucher file for processing.
SQL*Loader is executed to load the transaction tables from the files created by SAIMPTLOG. The store/day SQL*Loader files can be concatenated into a single file per table to optimize load times. Alternatively, multiple SQL*Loader files can be used as input to SQL*Loader. SQL*Loader may not be run in parallel with itself when loading a table. Header data (primary keys) must be loaded before ancillary data (foreign keys). This means that the sa_tran_head table must be loaded first, sa_tran_item before sa_tran_disc, and sa_customer before sa_cust_attrib. The remaining tables may be loaded in parallel.
SAVOUCH is executed to load each of the voucher files in Oracle Retail standard formatted. SAVOUCH may not be multi-threaded.
SAIMPTLOGFIN is executed to populate the sa_balance_group table, cancel post voided transactions and vouchers, validate missing transactions, and to mark the import as either partially or fully complete loaded. SAIMPTLOGFIN may not be multi-threaded.
Note: This design covers only Steps 2 and 3. |
Table 29-24 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
saimptlog.c or saimptlogi.c should run after the sagetref.pc to get the reference files as input. RTLOGs must also be ready as input files. |
Pre-Processing |
saprepost saimptlog pre - change constraints on ReSA tables OR saprepost saimptlogi pre - change constraints on ReSA tables. |
Post-Processing |
saprepost saimptlog post - change back constraints on ReSA tables OR saprepost saimptlogi post - change back constraints on ReSA tables sqlldr - use sql loader to load data into ReSA tables (for saimptlog only). |
Threading Scheme |
saimptlog and saimptlogi may be threaded as long as the parallel executions do not include the same store/day. |
Table 29-25 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_ROUNDING_RULE_HEAD |
Yes |
No |
No |
No |
SA_ROUNDING_RULE_DETAIL |
Yes |
No |
No |
No |
SA_STORE_DAY |
Yes |
No |
Yes |
No |
SA_TRAN_HEAD |
No |
Yes |
No |
No |
SA_CUSTOMER |
No |
Yes |
No |
No |
SA_CUST_ATTRIB |
No |
Yes |
No |
No |
SA_TRAN_ITEM |
No |
Yes |
No |
No |
SA_TRAN_IGTAX |
No |
Yes |
No |
No |
SA_TRAN_DISC |
No |
Yes |
No |
No |
SA_TRAN_TAX |
No |
Yes |
No |
No |
SA_TRAN_TENDER |
No |
Yes |
No |
No |
SA_TRAN_PAYMENT |
No |
Yes |
No |
No |
SA_ERROR |
No |
Yes |
No |
No |
SA_MISSING_TRAN |
No |
Yes |
No |
No |
ALL_SEQUENCES |
Yes |
No |
No |
No |
SA_TRAN_WRITE_LOCK |
Yes |
Yes |
No |
No |
Integration Type | Upload to ReSA |
File Name | Determined by runtime parameter |
Integration Contract | Inputs from sagetref.pc:
IntCon000113 (itemfile) IntCon000114 (wastefile) IntCon000115 (refitemfile) IntCon000116 (primvariantfile) IntCon000117 (varupcfile) IntCon000118 (storedayfile) IntCon000119 (promfile) IntCon000120 (codesfile) IntCon000121 (errorfile) IntCon000122 (storeposfile) IntCon000123 (tendertypefile) IntCon000124 (merchcodesfile) IntCon000125 (partnerfile) IntCon000126 (supplierfile) IntCon000127 (employeefile) IntCon000128 (bannerfile) IntCon000129 (promfile) IntCon000130 (whfile) IntCon000131 (invstatusfile) Inputs from POS: IntCon000048 (RTLOG) Outputs (if using saimptlog SQL Loader Option note that saimptlogi inserts directly into ReSA tables and does not create these output files) IntCon000160 (SAVO) IntCon000161 (satdisc.ctl) IntCon000162 (saigtax.ctl) IntCon000163 (sacust.ctl) IntCon000164 (sathead.ctl) IntCon000165 (satitem.ctl) IntCon000166 (sattend.ctl) IntCon000167 (satypmt.ctl) IntCon000168 (samisstr.ctl) IntCon000169 (sattax.ctl) IntCon000170 (sacustatt.ctl) IntCon000171 (saerror.ctl) (satwritelock.ctl) |
The input files for this program are reference files generated by sagetref.pc and RTLOGs. Refer to the details for the sagetref.pc program for the input file specifications.
Table 29-26 File Name: SAVO (Sales Audit Voucher File)
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
File Type Record Descriptor |
Char(5) |
FHEAD |
File type Record descriptor |
SA File Line No |
Char(10) |
NA |
Sales Audit File Line number |
|
Translator Id |
Char(5) |
SAVO |
Identifies transaction type |
|
Sys Date |
Char(14) |
NA |
System date in YYYYMMDDHHMMSS format |
|
Is business date |
Char(8) |
NA |
Business date in YYYYMMDD format |
|
FDETL |
Record Descriptor |
Char(5) |
FDETL |
File Type Record descriptor |
SA File Line No |
Number(10) |
NA |
Sales Audit File Line number |
|
Voucher seq Number |
Number(20) |
NA |
Unique identifier for an entry to sa_voucher table |
|
Voucher No |
Char(25) |
NA |
Voucher Number |
|
Voucher Type |
Number(6) |
NA |
Voucher Type |
|
Assigned Business Date |
Char(8) |
NA |
Business date in YYYYMMDD format |
|
Assigned Store |
Number(10) |
NA |
Store to which the voucher is assigned |
|
Issuing Date |
Char(8) |
NA |
Date this document was issued |
|
Issuing store |
Number(10) |
NA |
Store this document was issued from |
|
Issuing POS Register |
Char(5) |
NA |
Issuing Point Of Sale register |
|
Issuing Cashier |
Char(10) |
NA |
Issuing cashier |
|
Issued Tran Seq No. |
Number(20) |
NA |
Transaction sequence number |
|
Issued item seq number |
Number(4) |
NA |
Will hold the item sequence of the item when the voucher is sold as an item (gift voucher) |
|
Issued Tender Seq No. |
Number(4) |
NA |
Tender sequence number |
|
Issued Amount |
Number(20) |
NA |
Issued Amount * 10000 (4 implied digits) |
|
Issued Cust Name |
Char(120) |
NA |
Issued customer name |
|
Issued Customer Addr1 |
Char(240) |
NA |
Issued customer addr1 |
|
Issued Customer Addr2 |
Char(240) |
NA |
Issued customer addr 2 |
|
Issued Customer City |
Char(120) |
NA |
City of the customer, the voucher is issued |
|
Issued Customer State |
Char(3) |
NA |
State of the customer |
|
Issued Customer Postal Code |
Char(30) |
NA |
Postal address of the customer |
|
Issued Customer Country |
Char(3) |
NA |
Country of the customer the voucher was issued |
|
Recipient Name |
Char(120) |
NA |
Name of the intended recipient |
|
Recipient State |
Char(3) |
NA |
The state of the intended recipient |
|
Recipient Country |
Char(3) |
NA |
The country of the intended recipient |
|
Redemption Date |
Char(8) |
NA |
Date the voucher was redeemed |
|
Redemption Store |
Number(10) |
NA |
Store, the voucher was redeemed at |
|
Redemption Register |
Char(5) |
NA |
Register, the document was redeemed at |
|
Redemption cashier |
Char(10) |
NA |
Cashier redeeming the voucher |
|
Redemption tran seq number |
Number(20) |
NA |
Transaction number when the document was redeemed |
|
Redemption Tender seq number |
Number(4) |
NA |
This column will hold the tender sequence of the tender within the transaction when a voucher is redeemed as tender |
|
Redemption Amount |
Number(20) |
NA |
Amount the document was redeemed for*10000 (4 implied decimal places) |
|
Expiry Date |
Char(8) |
NA |
Expiry date |
|
Status |
Char(1) |
NA |
Indicator showing the document's status, issued or redeemed. Valid values = I - Issued, R - Redeemed |
|
Comments |
Char(2000) |
NA |
Comments |
|
FTAIL |
Record Descriptor |
Char(5) |
FTAIL |
File Type Record descriptor |
SA File Line No. |
Number(10) |
NA |
Sales Audit File Line Number |
|
#lines |
Number(10) |
NA |
Total number of transaction lines in file (not including FHEAD and FTAIL) |
Table 29-27 File Name: Satdisc.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
SA_TRAN_DISC |
TRAN_SEQ_NO |
INTEGER EXTERNAL |
20 |
1:20 |
NA |
ITEM_SEQ_NO |
INTEGER EXTERNAL |
4 |
21:24 |
NA |
|
DISCOUNT_SEQ_NO |
INTEGER EXTERNAL |
4 |
25:28 |
NA |
|
RMS_PROMO_TYPE |
CHAR |
6 |
29:34 |
NA |
|
PROMOTION |
INTEGER EXTERNAL |
10 |
35:44 |
NA |
|
DISC_TYPE |
CHAR |
6 |
45:50 |
NA |
|
COUPON_NO |
CHAR |
40 |
51:90 |
NA |
|
COUPON_REF_NO |
CHAR |
16 |
91:106 |
NA |
|
QTY |
DECIMAL EXTERNAL |
14 |
107:120 |
NA |
|
UNIT_DISCOUNT_AMT |
DECIMAL EXTERNAL |
21 |
121: 141 |
NA |
|
STANDARD_QTY |
DECIMAL EXTERNAL |
14 |
142:155 |
NA |
|
STANDARD_UNIT_DISC_AMT |
DECIMAL EXTERNAL |
21 |
156:176 |
NA |
|
REF_NO13 |
CHAR |
30 |
177:206 |
NA |
|
REF_NO14 |
CHAR |
30 |
207:236 |
NA |
|
REF_NO15 |
CHAR |
30 |
237:266 |
NA |
|
REF_NO16 |
CHAR |
30 |
267:296 |
NA |
|
ERROR_IND |
CHAR |
1 |
297:297 |
NA |
|
CATCHWEIGHT_IND |
CHAR |
1 |
298:298 |
NA |
|
UOM_QUANTITY |
INTEGER EXTERNAL |
12 |
299:310 |
NA |
|
PROMO_COMP |
INTEGER EXTERNAL |
10 |
311:320 |
This field maps to the OFFER_ID field from RPM |
|
STORE |
INTEGER EXTERNAL |
10 |
321:330 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
331:333 |
NA |
Table 29-28 File Name: Saigtax.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
SA_TRAN_IGTAX |
TRAN_SEQ_NO |
INTEGER EXTERNAL |
20 |
1:20 |
NA |
ITEM_SEQ_NO |
INTEGER EXTERNAL |
4 |
21:24 |
NA |
|
IGTAX_SEQ_NO |
INTEGER EXTERNAL |
4 |
25:28 |
NA |
|
TAX_AUTHORITY |
CHAR |
10 |
29:38 |
NA |
|
IGTAX_CODE |
CHAR |
6 |
39:44 |
NA |
|
IGTAX_RATE |
DECIMAL EXTERNAL |
11 |
45:65 |
NA |
|
TOTAL_IGTAX_AMT |
DECIMAL EXTERNAL |
22 |
66:87 |
NA |
|
STANDARD_QTY |
DECIMAL EXTERNAL |
14 |
88:101 |
NA |
|
STANDARD_UNIT_IGTAX_AMT |
DECIMAL EXTERNAL |
21 |
102:122 |
NA |
|
ERROR_IND |
CHAR |
1 |
123:123 |
NA |
|
REF_NO_21 |
CHAR |
30 |
124:153 |
NA |
|
REF_NO_22 |
CHAR |
30 |
154:183 |
NA |
|
REF_NO_23 |
CHAR |
30 |
184:213 |
NA |
|
REF_NO_24 |
CHAR |
30 |
214:243 |
NA |
|
STORE |
INTEGER EXTERNAL |
10 |
244:253 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
254:256 |
NA |
Table 29-29 File Name: Sacust.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
SA_CUSTOMER |
tran_seq_no |
INTEGER EXTERNAL DATE |
20 |
1 :20 |
NA |
cust_id |
CHAR |
16 |
21 :36 |
NA |
|
cust_id_type |
CHAR |
6 |
37 :42 |
NA |
|
name |
CHAR |
240 |
43 :162 |
NA |
|
addr1 |
CHAR |
240 |
163:402 |
NA |
|
addr2 |
CHAR |
240 |
403:642 |
NA |
|
city |
CHAR |
240 |
643:762 |
NA |
|
state |
CHAR |
3 |
763:765 |
NA |
|
postal_code |
CHAR |
30 |
766:795 |
NA |
|
country |
CHAR |
3 |
796:798 |
NA |
|
home_phone |
CHAR |
20 |
799:818 |
NA |
|
work_phone |
CHAR |
20 |
819:838 |
NA |
|
e_mail |
CHAR |
100 |
839:938 |
NA |
|
birthdate |
DATE |
8 |
939:946 |
Format is YYYYMMDD |
|
STORE |
INTEGER EXTERNAL |
10 |
947:956 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
957:959 |
NA |
Table 29-30 File Name: Sathead.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
Sa_tran_head |
Tran_seq_no |
Integer external |
20 |
1:20 |
NA |
Rev_no |
Integer external |
3 |
21:23 |
NA |
|
Store_day_seq_no |
Integer external |
20 |
24:43 |
NA |
|
Tran_datetime |
date |
14 |
44:57 |
Format is YYYYMM DDHH24MI SS |
|
Register |
char |
5 |
58:62 |
NA |
|
Tran_no |
Integer external |
10 |
63:72 |
NA |
|
Cashier |
char |
10 |
73:82 |
NA |
|
Salesperson |
char |
10 |
83:92 |
NA |
|
Tran_type |
char |
6 |
93:98 |
NA |
|
Sub_tran_type |
char |
6 |
99:104 |
NA |
|
Orig_tran_no |
Integer external |
10 |
105:114 |
NA |
|
Orig_reg_no |
char |
5 |
115:119 |
NA |
|
Ref_no1 |
char |
30 |
120:149 |
NA |
|
Ref_no2 |
char |
30 |
150:179 |
NA |
|
Ref_no3 |
char |
30 |
180:209 |
NA |
|
Ref_no4 |
char |
30 |
210:239 |
NA |
|
Reason_code |
char |
6 |
240:245 |
NA |
|
Vendor_no |
char |
10 |
246:255 |
NA |
|
Vendor_invc_no |
char |
30 |
256:285 |
NA |
|
Payment_ref_no |
char |
16 |
286:301 |
NA |
|
Proof_of_delivery_no |
char |
30 |
302:331 |
NA |
|
Status |
char |
6 |
332:337 |
NA |
|
Value |
char |
22 |
338:359 |
Includes an optional negative sign and a decimal point |
|
Pos_tran_ind |
char |
1 |
360:360 |
NA |
|
Update_id |
char |
30 |
361:390 |
NA |
|
Update_datetime |
date |
14 |
391:404 |
Format is YYYYMM DDHH24MI SS |
|
Error_ind |
char |
1 |
405:405 |
NA |
|
Banner_no |
Integer external |
4 |
406:409 |
NA |
|
round_amt |
Integer external |
22 |
410:431 |
NA |
|
ROUNDED_OFF_AMT |
INTEGER EXTERNAL |
22 |
432:453 |
NA |
|
CREDIT_PROMOTION_ID |
INTEGER EXTERNAL |
10 |
454:463 |
NA |
|
REF_NO25 |
CHAR |
30 |
464:493 |
NA |
|
REF_NO26 |
CHAR |
30 |
494:523 |
NA |
|
REF_NO27 |
CHAR |
30 |
524:553 |
NA |
|
STORE |
INTEGER EXTERNAL |
10 |
554:563 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
564:566 |
NA |
|
RTLOG_ORIG_SYS |
CHAR |
3 |
567:569 |
NA |
|
TRAN_PROCESS_SYS |
CHAR |
3 |
570:572 |
NA |
Table 29-31 File Name: Satitem.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
SA_TRAN_ITEM |
TRAN_SEQ_NO |
INTEGER EXTERNAL |
20 |
1:20 |
NA |
ITEM_SEQ_NO |
INTEGER EXTERNAL |
4 |
21:24 |
NA |
|
ITEM_STATUS |
CHAR |
6 |
25:30 |
NA |
|
ITEM_TYPE |
CHAR |
6 |
31:36 |
NA |
|
ITEM |
CHAR |
25 |
37:61 |
NA |
|
REF_ITEM |
CHAR |
25 |
62:86 |
NA |
|
NON_MERCH_ITEM |
CHAR |
25 |
87:111 |
NA |
|
VOUCHER_NO |
CHAR |
25 |
112:136 |
NA |
|
DEPT |
INTEGER EXTERNAL |
4 |
137:140 |
NA |
|
CLASS |
INTEGER EXTERNAL |
4 |
141:144 |
NA |
|
SUBCLASS |
INTEGER EXTERNAL |
4 |
145:148 |
NA |
|
QTY |
DECIMAL EXTERNAL |
14 |
149:162 |
Includes an optional negative sign and a decimal point |
|
UNIT_RETAIL |
DECIMAL EXTERNAL |
21 |
163:183 |
Includes a decimal point |
|
UNIT_RETAIL_VAT_INCL |
CHAR |
1 |
184:184 |
Indicates whether unit retail includes or excludes VAT |
|
SELLING UOM |
CHAR |
4 |
185:188 |
NA |
|
OVERRIDE_REASON |
CHAR |
6 |
189:194 |
NA |
|
ORIG_UNIT_RETAIL |
DECIMAL EXTERNAL |
21 |
195:215 |
Includes a decimal point |
|
STANDARD_ORIG_UNIT_RETAIL |
DECIMAL EXTERNAL |
21 |
216:236 |
NA |
|
TAX_IND |
CHAR |
1 |
237:237 |
NA |
|
ITEM_SWIPED_IND |
CHAR |
1 |
238:238 |
NA |
|
ERROR_IND |
CHAR |
1 |
239:239 |
NA |
|
DROP_SHIP_IND |
CHAR |
1 |
240:240 |
NA |
|
WASTE_TYPE |
CHAR |
6 |
241:246 |
NA |
|
WASTE_PCT |
DECIMAL EXTERNAL |
12 |
247:258 |
Includes a decimal point |
|
PUMP |
CHAR |
8 |
259:266 |
NA |
|
RETURN_REASON_CODE |
CHAR |
6 |
267:272 |
NA |
|
SALESPERSON |
CHAR |
10 |
273:282 |
NA |
|
EXPIRATION_DATE |
DATE |
8 |
283:290 |
Format is YYYYMMDD |
|
STANDARD_QTY |
DECIMAL EXTERNAL |
14 |
291:304 |
Includes an optional negative sign and a decimal point |
|
STANDARD_UNIT_RETAIL |
DECIMAL EXTERNAL |
21 |
305:325 |
Includes a decimal point |
|
STANDARD_UOM |
CHAR |
4 |
326:329 |
NA |
|
REF_NO5 |
CHAR |
30 |
330:359 |
NA |
|
REF_NO6 |
CHAR |
30 |
360:389 |
NA |
|
REF_NO7 |
CHAR |
30 |
390:419 |
NA |
|
REF_NO8 |
CHAR |
30 |
420:449 |
NA |
|
CATCHWEIGHT_IND |
CHAR |
1 |
450:450 |
NA |
|
SELLING_ITEM |
CHAR |
25 |
451:475 |
NA |
|
CUSTOMER_ORDER_LINE_NO |
INTEGER EXTERNAL |
6 |
476:481 |
NA |
|
MEDIA_ID |
INTEGER EXTERNAL |
10 |
482:491 |
NA |
|
UOM_QUANTITY |
INTEGER EXTERNAL |
12 |
492:503 |
NA |
|
TOTAL_IGTAX_AMT |
DECIMAL EXTERNAL |
504:524 |
NA |
||
UNIQUE_ID |
CHAR |
25 |
525:652 |
NA |
|
STORE |
INTEGER EXTERNAL |
10 |
653:662 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
663:665 |
NA |
|
CUST_ORDER_NO |
CHAR |
48 |
666:713 |
NA |
|
CUST_ORDER_DATE |
DATE |
14 |
714:727 |
Format is YYYYMMDDHH24MISS |
|
FULFILL_ORDER_NO |
CHAR |
48 |
728:775 |
NA |
|
NO_INV_RET_IND |
CHAR |
1 |
776:776 |
NA |
|
SALES_TYPE |
CHAR |
1 |
777:777 |
NA |
|
RETURN_WH |
INTEGER EXTERNAL |
10 |
778:787 |
NA |
|
RETURN_DISPOSITION |
CHAR |
10 |
788:797 |
NA |
|
ORIG_STORE |
INTEGER EXTERNAL |
10 |
798:807 |
NA |
|
ORIG_TRAN_NO |
INTEGER EXTERNAL |
10 |
808:817 |
NA |
Table 29-32 File Name: Sattend.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
Sa_tran_tender |
Tran_seq_no |
Integer external |
20 |
1:20 |
NA |
Tender_seq_no |
Integer external |
4 |
21:24 |
NA |
|
Tender_type_group |
char |
6 |
25:30 |
NA |
|
Tender_type_id |
Integer external |
6 |
31:36 |
NA |
|
Tender_amt |
decimal external |
22 |
37:58 |
Includes an optional negative sign and a decimal point. |
|
Cc_no |
Integer external |
40 |
59:98 |
NA |
|
Cc_exp_date |
date |
8 |
99:106 |
FORMAT IS YYYYMMDD |
|
Cc_auth_no |
char |
16 |
107:122 |
NA |
|
Cc_auth_src |
char |
6 |
123:128 |
NA |
|
Cc_entry_mode |
char |
6 |
129:134 |
NA |
|
Cc_cardholder_verf |
char |
6 |
135:140 |
NA |
|
Cc_term_id |
char |
5 |
141:145 |
NA |
|
Cc_spec_cond |
char |
6 |
146:151 |
NA |
|
CC_TOKEN |
CHAR |
40 |
152:191 |
NA |
|
Voucher_no |
char |
25 |
192:216 |
NA |
|
Coupon_no |
char |
40 |
217:256 |
NA |
|
Coupon_ref_no |
char |
16 |
257:272 |
NA |
|
CHECK_ACCT_NO |
CHAR |
30 |
273:302 |
NA |
|
CHECK_NO |
INTEGER EXTERNAL |
10 |
303:312 |
NA |
|
IDENTI_METHOD |
CHAR |
6 |
313:318 |
NA |
|
IDENTI_ID |
CHAR |
40 |
319:358 |
NA |
|
ORIG_CURRENCY |
CHAR |
3 |
359:361 |
NA |
|
ORIG_CURR_AMT |
DECIMAL EXTERNAL |
22 |
362:383 |
NA |
|
Ref_no9 |
char |
30 |
384:413 |
NA |
|
Ref_no10 |
char |
30 |
414:443 |
NA |
|
Ref_no11 |
char |
30 |
444:473 |
NA |
|
Ref_no12 |
char |
30 |
474:503 |
NA |
|
Error_ind |
char |
1 |
504:504 |
NA |
|
STORE |
INTEGER EXTERNAL |
10 |
505:514 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
515:517 |
NA |
Table 29-33 File Name: Satpymt.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
SA_TRAN_PAYMENT |
TRAN_SEQ_NO |
INTEGER EXTERNAL |
20 |
1:20 |
NA |
PAYMENT_SEQ_NO |
INTEGER EXTERNAL |
20 |
21:24 |
NA |
|
PAYMENT_AMT |
DECIMAL EXTERNAL |
5 |
25:46 |
NA |
|
ERROR_IND |
CHAR |
10 |
47:47 |
NA |
|
STORE |
INTEGER EXTERNAL |
6 |
48:57 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
58:60 |
NA |
Table 29-34 File Name: Samisstr.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
SA_MISSING_TRAN |
MISS_TRAN_SEQ_NO |
INTEGER EXTERNAL |
20 |
1:20 |
NA |
STORE_DAY_SEQ_NO |
INTEGER EXTERNAL |
20 |
21:40 |
NA |
|
REGISTER |
CHAR |
5 |
41:45 |
NA |
|
TRAN_NO |
INTEGER EXTERNAL |
10 |
46:55 |
NA |
|
STATUS |
CHAR |
6 |
56:61 |
NA |
|
RTLOG_ORIG_SYS |
CHAR |
3 |
62:64 |
NA |
Table 29-35 File Name: Sattax.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
SA_TRAN_TAX |
TRAN_SEQ_NO |
INTEGER EXTERNAL |
20 |
1:20 |
NA |
TAX_CODE |
CHAR |
6 |
21:26 |
NA |
|
TAX_SEQ_NO |
INTEGER EXTERNAL |
4 |
27:30 |
NA |
|
TAX_AMT |
DECIMAL EXTERNAL |
22 |
31:52 |
Includes an optional negative sign and a decimal point |
|
ERROR_IND |
CHAR |
1 |
53:53 |
NA |
|
REF_NO17 |
CHAR |
30 |
54:83 |
NA |
|
REF_NO18 |
CHAR |
30 |
84:113 |
NA |
|
REF_NO19 |
CHAR |
30 |
114:143 |
NA |
|
REF_NO20 |
CHAR |
30 |
144:173 |
NA |
|
STORE |
INTEGER EXTERNAL |
10 |
174:183 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
184:186 |
NA |
Table 29-36 File Name: Sacustatt.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
SA_CUST_ATTRIB |
TRAN_SEQ_NO |
INTEGER EXTERNAL |
20 |
1:20 |
NA |
ATTRIB_SEQSO |
CHAR |
4 |
21:24 |
NA |
|
ATTRIB_TYPE |
CHAR |
6 |
25:30 |
NA |
|
ATTRIB_VALUE |
CHAR |
6 |
31:36 |
NA |
|
STORE |
INTEGER EXTERNAL |
10 |
37:46 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
47:49 |
NA |
Table 29-37 File Name: Saerror.ctl
Table Name | Column Name | Field Type | Field Width | Position | Description |
---|---|---|---|---|---|
SA_ERROR |
ERROR_SEQ_NO |
INTEGER EXTERNAL |
20 |
1:20 |
NA |
STORE_DAY_SEQ_NO |
INTEGER EXTERNAL |
20 |
21:40 |
NA |
|
BAL_GROUP_SEQ_NO |
INTEGER EXTERNAL |
20 |
41:60 |
NA |
|
TOTAL_SEQ_NO |
INTEGER EXTERNAL |
20 |
61:80 |
NA |
|
TRAN_SEQ_NO |
INTEGER EXTERNAL |
20 |
81:100 |
NA |
|
ERROR_CODE |
CHAR |
25 |
101:125 |
NA |
|
KEY_VALUE_1 |
INTEGER EXTERNAL |
4 |
126:129 |
NA |
|
KEY_VALUE_2 |
INTEGER EXTERNAL |
4 |
130:133 |
NA |
|
REC_TYPE |
CHAR |
6 |
134:139 |
NA |
|
STORE_OVERRIDE_IND |
CHAR |
1 |
140:140 |
NA |
|
HQ_OVERRIDE_IND |
CHAR |
1 |
141:141 |
NA |
|
UPDATE_ID |
CHAR |
30 |
142:171 |
NA |
|
UPDATE_DATE TIME |
DATE |
14 |
172:185 |
Format is YYYYMMDDHH24MISS |
|
ORIG_VALUE |
CHAR |
70 |
186:255 |
NA |
|
STORE |
INTEGER EXTERNAL |
10 |
256:265 |
NA |
|
DAY |
INTEGER EXTERNAL |
3 |
266:268 |
NA |
The following illustrates the file layout format of the Oracle Retail TLOG. The content of each Oracle Retail TLOG file is per store per day. The filename convention is RTLOG_STORE_DATETIME.DAT (for example, RTLOG_1234_01221989010000.DAT).
Involves round off fields, credit promotion id, tax (vat) at item level and payment amount of customer orders.
Document has been modified regarding tender types, logic of handling both VAT-TAX in the system has been added.
FHEAD (Only 1 per file, required) THEAD (Multiple expected, one per transaction, required for each transaction) TCUST (Only 1 per THEAD record allowed, optional for some transaction types, see table below) CATT (Attribute record specific to the TCUST record - Multiple allowed, only valid if TCUST exists) TITEM (Multiple allowed per transaction, optional for some transaction types, see table below) IDISC (Discount record specific to the TITEM record - Multiple allowed per item, optional see table below) IGTAX (Vat/Tax record specific to the TITEM record - Multiple allowed per item, optional. Either TTAX or IGTAX should appear in a given RTLOG, but not both, see table below) TTAX (Vat/Tax record specific to the THEAD record - Multiple allowed per transaction, optional. Either TTAX or IGTAX should appear in a given RTLOG, but not both, see table below) TPYMT (Multiple allowed per transaction, will have the deposit amount for pickup/delivery/layaway orders, optional see table below) TTEND (Multiple allowed per transaction, optional for some transaction types, see table below) TTAIL (1 per THEAD, required) FTAIL (1 per file, required)
The order of the records within the transaction layout above is important. It aids processing by ensuring that the information is present when it is needed.
Table 29-39 File Name: rtlog
Record Name | Field Name | Field Type | Default Value | Description | Required? | Justification/Padding |
---|---|---|---|---|---|---|
File Header |
File Type Record Descriptor |
Char(5) |
FHEAD |
Identifies file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
File Type Definition |
Char(4) |
RTLG |
Identifies file as Oracle Retail TLOG. |
Y |
Left/Blank |
|
File Create Date |
Char(14) |
Create date |
Date and time file was written by external system (YYYYMMDDHHMMSS). |
Y |
Left/None |
|
Business Date |
Char(8) |
Business Date to process |
Business date of transactions (YYYYMMDD). |
Y |
Left/None |
|
Location Number |
Char(10) |
Specified by external system |
Store or warehouse identifier. |
Y |
Left/None |
|
Reference Number |
Char(30) |
Specified by external system |
This may contain the Polling ID associated with the consolidated TLOG file or used for other purpose. |
N |
Left/Blank |
|
RTLOG Originating System |
Char(3) |
POS |
Identifies the system the RTLOG file originated from. Valid values are OMS and POS. |
Y |
Left/None |
|
Transaction Header |
File Type Record Descriptor |
Char(5) |
Char(5) THEAD |
Identifies file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
Register |
Char(5) |
Transaction date |
Till used at the store. |
Y |
Left/Blank |
|
Transaction Date |
Char(14) |
NA |
Date for the transactions that were processed at the POS (YYYYMMDDHHMMSS). |
Y |
Left/None |
|
Transaction Number |
Number(10) |
NA |
Transaction identifier. If sa_system_options, wkstation_tran_append_ind is Y, then the first 3 digits indicate the workstation ID and last 7 digits indicate the transaction number. |
Y |
Right/0 |
|
Cashier |
Char(10) |
NA |
Cashier identifier. |
N |
Left/Blank |
|
Salesperson |
Char(10) |
NA |
Salesperson identifier. |
N |
Left/Blank |
|
Transaction Type |
Char(6) |
Refer to TRAT code_type for a list of valid types. |
Transaction type. |
Y |
Left/Blank |
|
Sub-transaction type |
Char(6) |
Refer to TRAS code_type for a list of valid types. |
Sub-transaction type. For sale, it can be employee, drive-off, and so on. |
N |
Left/Blank |
|
Orig_tran_no |
Number(10) |
NA |
Populated only for post-void transactions. Transaction number for the original transaction that will be cancelled. |
N |
Right/0 |
|
Orig_reg_no |
Char(5) |
NA |
Populated only for post-void even exchange and return transactions. Register number from the original transaction |
N |
Left/Blank |
|
Reason Code |
Char(6) |
Refer to REAC code_type for a list of valid codes. If the transaction type is PAIDOU and the sub transaction type is MV or EV, than the valid codes come from the non_merch_code_head table. |
Reason entered by the cashier for some transaction types. Required for Paid In and Paid out transaction types, but can also be used for voids, returns, and so on. |
N |
Left/Blank |
|
Vendor Number |
Char(10) |
NA |
Supplier ID for a merchandise vendor paid out transaction; partner ID for an expense vendor paid out transaction. |
N |
Left/Blank |
|
Vendor Invoice Number |
Char(30) |
NA |
Invoice number for a vendor paid out transaction. |
N |
Left/Blank |
|
Payment Reference Number |
Char(16) |
NA |
The reference number of the tender used for a vendor payout. This could be the money order number, check number, and so on. |
N |
Left/Blank |
|
Proof of Delivery Number |
Char(30) |
NA |
Proof of receipt number given by the vendor at the time of delivery. This field is populated for a vendor paid out transaction. |
N |
Left/Blank |
|
Reference Number 1 |
Char(30) |
Na |
Number associated with a particular transaction, for example, whether for a Store Conditions transaction. The SA_REFERENCE table defines what this field can contain for each transaction type. |
N |
Left/Blank |
|
Reference Number 2 |
Char(30) |
NA |
Char(30) |
N |
Left/Blank |
|
Reference Number 3 |
Char(30) |
NA |
Third generic reference number. |
N |
Left/Blank |
|
Reference Number 4 |
Char(30) |
NA |
Fourth generic reference number. |
N |
Left/Blank |
|
Value Sign |
Char(1) |
Refer to SIGN code_type for a list of valid codes. |
Sign of the value. |
Y if Value is present. |
Left/None |
|
Value |
Number(20) |
NA |
Value, with 4 implied decimal places. Populated by the retailer for TOTAL transaction, populated by ReSA for SALE and RETURN transactions. |
Y if tran is a TOTAL |
Right/0 when value is present. Blank when no value is sent. |
|
Banner id |
Number(4) |
NA |
Banner ID of the location. |
Y |
Right/0 when value is present. Blank when no value is sent |
|
Rounded Amount Sign |
Char(1) |
Refer to SIGN code_type for a list of valid codes. |
Sign of rounded amount. Amount Sign is not used. |
Y |
Left/None |
|
Rounded Amount |
Number(20) |
NA |
Total rounded amount, with 4 implied decimal places. Rounded Amount is not used. |
Y |
Right/0 when RoundedAmount is present otherwise blank |
|
Rounded Off Sign |
Char(1) |
Refer to SIGN code_type for a list of valid codes. |
Rounded Off Sign is not used. |
Y |
Left/None |
|
Rounded Off Amount |
Number(20) |
NA |
Rounded off amount, with 4 implied decimal places. Rounded Off Amount is not used. |
Y |
Right/0 when RoundedAmount is present otherwise blank |
|
Credit Promotion Id |
Char(10) |
NA |
Credit Promotional ID. |
Y |
Left/None |
|
Reference Number 25 |
Char(30) |
NA |
NA |
N |
Left/Blank |
|
Reference Number 26 |
Char(30) |
NA |
NA |
N |
Left/Blank |
|
Reference Number 27 |
Char(30) |
NA |
NA |
N |
Left/Blank |
|
Transaction Processing System |
Char(3) |
Valid values are OMS and POS. |
Contains the ID of the system that processed the transaction. |
N |
Left/None |
|
Transaction Customer |
File Type Record Descriptor |
Char(5) |
TCUST |
Identifies the file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file |
Y |
Right/0 |
|
Customer ID |
Char(16) |
Customer identifier |
The ID number of a customer. |
Y |
Left/Blank |
|
Customer Type ID |
Char(6) |
Refer to CIDT code_type for a list of valid types. |
Customer ID type. |
Y |
Left/Blank |
|
Customer Name |
Char(120) |
NA |
Customer name. |
N |
Left/Blank |
|
Address 1 |
Char(240) |
NA |
Customer address. |
N |
Left/Blank |
|
Address 2 |
Char(240) |
NA |
Additional field for customer address. |
N |
Left/Blank |
|
City |
Char(120) |
NA |
City. |
N |
Left/Blank |
|
State |
Char(12) |
State identifier |
State. |
N |
Left/Blank |
|
Zip Code |
Char(30) |
Zip identifier |
Zip code. |
N |
Left/Blank |
|
Country |
Char(3) |
NA |
Country. |
N |
Left/Blank |
|
Home Phone |
Char(20) |
NA |
Telephone number at home. |
N |
Left/Blank |
|
Work Phone |
Char(20) |
NA |
Telephone number at work. |
N |
Left/Blank |
|
|
Char(100) |
NA |
E-mail address. |
N |
Left/Blank |
|
Birthdate |
Char(8) |
NA |
Date of birth. (YYYYMMDD) |
N |
Left/Blank |
|
Customer Attribute |
File Type Record Descriptor |
Char(5) |
CATT |
Identifies file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
Attribute type |
Char(6) |
Refer to SACA code_type for a list of valid types. |
Type of customer attribute |
Y |
Left/Blank |
|
Attribute value |
Char(6) |
Refer to members of SACA code_type for a list of valid values. |
Value of customer attribute. |
Y |
Left/Blank |
|
Transaction Item |
File Type Record Descriptor |
Char(5) |
TITEM |
Identifies file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
Item Status |
Char(6) |
Refer to SASI code_type for a list of valid codes. |
Status of the item within the transaction. Valid values are: V - Void item S - Sold item R - Returned item ORI - Order Initiate ORC - Order Cancel ORD - Order Complete LIN - Layaway Initiate LCA - Layaway Cancel LCO - Layaway Complete |
Y |
Left/Blank |
|
Item Type |
Char(6) |
Refer to SAIT code_type for a list of valid codes. |
Identifies what type of item is transmitted. |
Y |
Left/Blank |
|
Item number type |
Char(6) |
Refer to UPCT code_type for a list of valid codes. |
Identifies the type of item number if the item type is ITEM or REF |
N |
Left/Blank |
|
Format ID |
Char(1) |
VPLU format ID |
Used to interpret VPLU items. |
N |
Left/Blank |
|
Item |
Char(25) |
Item identifier |
Identifies the merchandise item. |
N |
Left/Blank |
|
Reference Item |
Char(25) |
Item identifier |
Identifies the sub-transaction level merchandise item. |
N |
Left/Blank |
|
Non-Merchandise Item |
Char(25) |
Item identifier |
Item identifier Identifies a non-merchandise item. |
N |
Left/Blank |
|
Voucher |
Char(25) |
NA |
Gift certificate number. |
N |
Right/0 |
|
Department |
Number(4) |
NA |
Identifies the department to which this item belongs. This is filled in by saimptlog. |
N |
Right/Blank |
|
Class |
Number(4) |
Class of the item |
Class of item sold or returned. Not required from a retailer; populated by ReSA. This is filled in by saimptlog. |
N |
Right/Blank |
|
Subclass |
Number(4) |
Subclass of the item |
Subclass of the item sold or returned. Not required from a retailer; populated by ReSA. This is filled in by saimptlog. |
N |
Right/Blank |
|
Quantity Sign |
Char(1) |
Refer to SIGN code_type for a list of valid codes. |
Sign of the quantity |
Y |
Left/None |
|
Quantity |
Number(12) |
NA |
Number of items purchased, with 4 decimal places. |
Y |
Right/0 |
|
Selling Unit of Measure |
Char(4) |
NA |
Unit of measure of the item's quantity. |
Y |
Left/None |
|
Unit Retail |
Number(20) |
NA |
Unit retail, with 4 implied decimal places. |
Y |
Right/0 |
|
Override Reason |
Char(6) |
Refer to ORRC code_type for a list of valid codes. |
This column is populated when an item's price has been overridden at the POS to define why it was overridden. |
Y if unit retail was manually entered |
Left/Blank |
|
Original Unit Retail |
Number(20) |
NA |
Value, with 4 implied decimal places. This column is populated when the item's price was overridden at the POS and the item's original unit retail is known. |
Y if unit retail was manually entered |
Right/0 |
|
Taxable Indicator |
Char(1) |
Refer to YSNO code_type for a list of valid codes. |
Indicates whether or not item is taxable. |
Y |
Left/None |
|
Pump |
Char(8) |
NA |
Fuel pump identifier. |
N |
Left/Blank |
|
Reference Number 5 |
Char(30) |
NA |
Number associated with a particular item within a transaction, for example, special order number. The sa_reference table defines what this field can contain for each transaction type. |
N |
Left/Blank |
|
Reference Number 6 |
Char(30) |
NA |
Second generic reference number at the item level. |
N |
Left/Blank |
|
Reference Number 7 |
Char(30) |
NA |
Third generic reference number at the item level. |
N |
Left/Blank |
|
Reference Number 8 |
Char(30) |
NA |
Fourth generic reference number at the item level. |
N |
Left/Blank |
|
Item_swiped_ind |
Char(1) |
Refer to YSNO code_type for a list of valid codes |
Indicates if the item was automatically entered into the POS system or if it had to be manually keyed. |
Y |
Left/None |
|
Return Reason Code |
Char(6) |
Refer to SARR code_type for a list of valid codes. |
The reason an item was returned. |
N |
Left/Blank |
|
Salesperson |
Char(10) |
NA |
The salesperson who sold the item. |
N |
Left/Blank |
|
Expiration_date |
Char(8) |
NA |
Gift certificate expiration date (YYYYMMDD). |
N |
||
Drop Ship Ind |
Char(1) |
Refer to YSNO code type for a list of valid codes. |
Indicates whether the item is part of a drop shipment. |
Y |
Left/None |
|
Uom_qty |
Number(12) |
NA |
Quantity of items purchased in the given UOM, with 4 decimal places. |
Y |
Right/0 |
|
Catchweight_ind |
Char(1) |
Valid values are Y and N. |
Identifies if the item is a catchweight item. |
Left/None |
||
Selling item |
Char(25) |
Item identifier |
Identifies the selling item. |
N |
Left/Blank |
|
Customer order line no |
Number(6) |
NA |
Identifies the customer order number. |
N |
Left/Blank |
|
Media id |
Number(10) |
NA |
Identifies the customer media ID. |
N |
Left/Blank |
|
Total Igtax Amount |
Number(21) |
NA |
Contains the Igtax amount. |
N |
Right/0 |
|
Unique ID |
Char(128) |
NA |
N |
Left/Blank |
||
Customer Order Number |
Char(48) |
NA |
Contains the customer order ID. |
N |
Left/None |
|
Customer Order Date |
Char(14) |
NA |
Contains the customer order date. Format is: YYYYMMDDHHMMSS Customer orders and layaways require customer order date. |
N |
Left/Blank |
|
Fulfillment Order Number |
Char(48) |
NA |
Contains the order ID of the fulfillment order. |
N |
Left/None |
|
No Inventory Return |
Char(1) |
NA |
Indicates if there is an associated inventory with the return transaction with an External Customer Order sales type. |
N |
Left/Blank |
|
Sales Type |
Char(1) |
NA |
Indicates if the transaction is an In Store Customer Order, External Customer Order, or Regular Sale |
N |
Left/Blank |
|
Return Warehouse |
Char(10) |
NA |
Contains the ID of the physical warehouse to which the inventory is returned. |
N |
Left/Blank |
|
Return Disposition |
Char(10) |
NA |
Contains the return disposition of the returned items. |
N |
Left/Blank |
|
Original Store |
Char(10) |
Contains the original store. |
N |
Left/Blank |
||
Original Transaction No |
Char(10) |
Contains the original transaction no. |
N |
Left/Blank |
||
Item Discount |
File Type Record Descriptor |
Char(5) |
IDISC |
Identifies the file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
RMS Promotion Number |
Char(6) |
Refer to PRMT code_type for a list of valid types |
The RMS promotion type. |
Y |
Left/Blank |
|
Discount Reference Number |
Number(10) |
NA |
Discount reference number associated with the discount type. For example, if the discount type is a promotion, this contains the promotion number. |
N |
Left/Blank |
|
Discount Type |
Char(6) |
Refer to SADT code_type for a list of valid types. |
The type of discount within a promotion. This allows a retailer to further break down coupon discounts within the In-store promotion, for example. |
N |
Left/Blank |
|
Coupon Number |
Char(40) |
NA |
Number of a store coupon used as a discount. |
Y if coupon |
Left/Blank |
|
Coupon Reference Number |
Char(16) |
NA |
Additional information about the coupon, usually contained in a second bar code on the coupon. |
Y if coupon |
Left/Blank |
|
Quantity Sign |
Char(1) |
Refer to SIGN code_type for a list of valid codes. |
Sign of the quantity. |
Y |
Left/None |
|
Quantity |
Number(12) |
NA |
The quantity purchased for which the discount is applied, with 4 implied decimal places. |
Y |
Right/0 |
|
Unit Discount Amount |
Number(20) |
NA |
Unit discount amount for this item, with 4 implied decimal places. |
Y |
Right/0 |
|
Reference Number 13 |
Char(30) |
NA |
Number associated with a particular transaction type at the discount level. The sa_reference table defines what this field can contain for each transaction type. |
N |
Left/Blank |
|
Reference Number 14 |
Char(30) |
NA |
Second generic reference number at the discount level. |
N |
Left/Blank |
|
Reference Number 15 |
Char(30) |
NA |
Third generic reference number at the discount level. |
N |
Left/Blank |
|
Reference Number 16 |
Char(30) |
NA |
Fourth generic reference number at the discount level. |
N |
Left/Blank |
|
Uom_qty |
Number(12) |
NA |
Quantity of items purchased in the given UOM with 4 decimal places. |
Y |
Right/0 |
|
Catchweight_ind |
Char(1) |
Valid values are Y and N. |
Identifies if the item is a catchweight item. |
Left/None |
||
Promo component |
Number(10) |
NA |
If the discount is a promotion, this field contains the promotion component value associated with the promotion (discount reference number). |
N |
Left/Blank |
|
Item Tax |
File Type Record Descriptor |
Char(5) |
IGTAX |
Identifies the file record type |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
Tax Authority |
Char(10) |
NA |
NA |
Y |
Left/Blank |
|
Igtax Code |
Char(6) |
Refer to tax_code/vat_code of tax_codes/vat_codes tables. |
IGtax code (tax code/VAT code) to represent whether it is a State, City, or some other tax code/VAT code. |
Y |
Left/Blank |
|
Igtax Rate |
Number(20) |
NA |
Igtax rate, with 4 implied decimal places. |
Y |
Right/0 |
|
Igtax Amount Sign |
Char(1) |
Refer to SIGN code_type for a list of valid codes. |
Sign of the Igtax amount. |
Y |
Left/None |
|
Igtax Amount |
Number(21) |
NA |
Total igtax amount for this item, with 5 implied decimal places. |
Y |
Right/0 |
|
Reference Number 21 |
Char(30) |
NA |
NA |
N |
Left/None |
|
Reference Number 22 |
Char(30) |
NA |
NA |
N |
Left/None |
|
Reference Number 23 |
Char(30) |
NA |
NA |
N |
Left/None |
|
Reference Number 24 |
Char(30) |
NA |
NA |
N |
Left/None |
|
Transaction Tax |
File Type Record Descriptor |
Char(5) |
TTAX |
Identifies the file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
Tax Code |
Char(6) |
Refer to TAXC code_type for as list of valid types. |
Tax code (tax code/VAT code) to represent whether it is a State, City, or some other tax code/VAT code. |
Y |
Left/Blank |
|
Tax Sign |
Char(1) |
Refer to SIGN code_type for a list of valid codes |
Sign of the tax amount. |
Y |
Left/None |
|
Tax Amount |
Number(20) |
NA |
Total Tax amount for this item, with 4 implied decimal places. |
Y |
Right/0 |
|
Reference Number 17 |
Char(30) |
NA |
NA |
N |
Left/None |
|
Reference Number 18 |
Char(30) |
NA |
NA |
N |
Left/None |
|
Reference Number 19 |
Char(30) |
NA |
NA |
N |
Left/None |
|
Reference Number 20 |
Char(30) |
NA |
NA |
N |
Left/None |
|
Transaction payment |
File Type Record Descriptor |
Char(5) |
TPYMT |
Identifies the file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
Payment Sign |
Char(1) |
Refer to SIGN code_type for a list of valid codes. |
Sign of the deposit amount. |
Y |
Left/None |
|
Payment Amount |
Number(20) |
NA |
Deposit amount paid, with 4 implied decimal places. |
Y |
Right/0 |
|
Transaction Tender |
File Type Record Descriptor |
Char(5) |
TTEND |
Identifies the file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
Tender Type Group |
Char(6) |
Refer to TENT code_type for as list of valid types |
High-level grouping of tender types. |
Y |
Left/Blank |
|
Tender Type ID |
Number(6) |
Refer to the pos_tender_type_head table for as list of valid types. |
Low-level grouping of tender types. |
Y |
Left/Blank |
|
Tender Sign |
Char(1) |
Refer to SIGN code_type for a list of valid codes. |
Sign of the value. |
Y |
Left/None |
|
Tender Amount |
Number(20) |
NA |
Amount paid with this tender in the transaction, with 4 implied decimal places. |
Y |
Right/0 |
|
Cc_no |
Char(40) |
NA |
Credit card number. The value sent in the RTLOG should be masked. |
Y if credit card |
Left/Blank |
|
Cc_auth_no |
Char(16) |
NA |
Authorization number for a credit card. |
Y if credit card |
Left/Blank |
|
cc authorization source |
Char(6) |
Refer to CCAS code_type for as list of valid types. |
NA |
Y if credit card |
Left/Blank |
|
cc cardholder verification |
Char(6) |
Refer to CCVF code_type for as list of valid types |
NA |
Y if credit card |
Left/Blank |
|
cc expiration date |
Char(8) |
NA |
YYYYMMDD |
Y if credit card |
Left/Blank |
|
cc entry mode |
Char(6) |
Refer to CCEM code_type for as list of valid types. |
Indicates whether the credit card was swiped, thus automatically entered, or manually keyed. |
Y if credit card |
Left/Blank |
|
cc terminal id |
Char(5) |
NA |
Terminal number from which the transaction was sent. |
N |
Left/Blank |
|
cc special condition |
Char(6) |
Refer to CCSC code_type for as list of valid types. |
NA |
Y if credit card |
Left/Blank |
|
cc token |
Char(40) |
NA |
Holds unique token when the tender type used is credit, debit card, PayPal, Fonacot or Others. |
N |
Left/Blank |
|
Voucher_no |
Char(25) |
NA |
Gift certificate or credit voucher serial number. Voucher number needs to be included If a voucher is voided from a transaction. |
Y if voucher |
Right/0 |
|
Coupon Number |
Char(40) |
NA |
Number of a manufacturer's coupon used as a tender. |
Y if coupon |
Left/Blank |
|
Coupon Reference Number |
Char(16) |
NA |
Additional information about the coupon, usually contained in a second bar code on the coupon. |
Y if coupon |
Left/Blank |
|
Check Account Number |
Char(30) |
NA |
Account number of the check. The value sent in the RTLOG is masked. |
N |
Left/Blank |
|
Check Number |
Number(10) |
NA |
Check number. |
Required for the tender type CHECK |
Right/0 |
|
Identification Method |
Char(6) |
Refer to IDMH code_type for list of valid types. |
Identification Method (such as a driver's license number or photo credit card). |
N |
Left/Blank |
|
Identification Id |
Char(40) |
NA |
Identification ID (license ID or photo card number). |
N |
Left/Blank |
|
Original Currency |
Char(3) |
Refer to the CURRENCIES table for valid currency codes. |
The original currency with which the customer made the payment. |
N |
Left/Blank |
|
Original Currency Amount |
Number(20) |
NA |
Amount paid with this tender in the original currency, with 4 implied decimal places. |
N |
Right/0 |
|
Reference No 9 |
Char(30) |
NA |
Number associated with a particular transaction type at the tender level. The sa_reference table defines what this field can contain for each transaction type. |
N |
Left/Blank |
|
Reference No 10 |
Char(30) |
NA |
Second generic reference number at the tender level. |
N |
Left/Blank |
|
Reference No 11 |
Char(30) |
NA |
Third generic reference number at the tender level. |
N |
Left/Blank |
|
Reference No 12 |
Char(30) |
NA |
Fourth generic reference number at the tender level. |
N |
Left/Blank |
|
Transaction Trailer |
File Type Record Descriptor |
Char(5) |
TTAIL |
Identifies file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
Transaction Record Counter |
Number(10) |
NA |
Number of records processed in the current transaction (only those records between transaction head and tail). |
NA |
NA |
|
File Trailer |
File Type Record Descriptor |
Char(5) |
FTAIL |
Identifies the file record type. |
Y |
Left/Blank |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Y |
Right/0 |
|
File Record Counte |
Number(10) |
NA |
Number of transactions processed in the current file (only the records between the file head and tail). |
Y |
Right/0 |
The RTLOG file is imported into the Sales Audit tables after validation by the batch program saimptlog. This section describes the requirements and validations performed on the records.
This section details the common requirements and validations performed on all transactions. The following sections describe the specific requirements of each type of transaction. If a transaction is not mentioned, it does not have specific requirements.
Table 29-40 Common Requirements and Validations
Transaction Type | Includes item records? | Includes tender records? | Includes tax records? IG TAX? | Includes customer records? |
---|---|---|---|---|
OPEN |
No |
No |
No |
No |
NOSALE |
No |
Optional |
No |
No |
VOID |
Optional |
Optional |
Optional |
Optional |
PVOID |
No |
No |
No |
No |
SALE |
Optional |
Yes |
Optional |
Optional |
RETURN |
Yes |
Yes |
Optional |
Optional |
EEXCH |
Yes |
No |
Optional |
Optional |
PAIDIN |
No |
Yes |
No |
No |
PAIDOU |
No |
Yes |
No |
No |
PULL |
No |
Yes |
No |
No |
LOAN |
No |
Yes |
No |
No |
COND |
No |
No |
No |
No |
CLOSE |
No |
No |
No |
No |
TOTAL |
No |
No |
No |
No |
REFUND |
This transaction is not sent through the RTLOG. It is entered at the HQ level. The TITEM and TCUST records are optional. The TTEND record is required. A TTAX record should not be included if IGTAX appears in a transaction. IGTAX is an item-level tax and TTAX is a transaction-level tax. Either IGTAX or TTAX can be used, but not both. |
|||
METER |
Yes |
No |
No |
No |
PUMPT |
Yes |
No |
No |
No |
TANKDP |
Yes |
No |
No |
No |
TERM |
TERM records are created by saimptlog and then loaded into the database. They do not come from the RTLOG file. They require one TITEM, one TTEND, one TTAX, one TCUST record, and one CATT record, IGTAX, and one TPYMT which is newly coming up. |
|||
DCLOSE |
No |
No |
No |
No |
SPLORD |
Optional |
Yes |
Optional |
Optional |
Table 29-41 Requirements per Record Type
Record Type | Requirements |
---|---|
IDISC |
IDISC records must immediately follow their associated TITEM record. |
IGTAX |
IGTAX will immediately follow TITEM if IDISC is not coming, otherwise it should follow IDISC. Even if IGTAX is coming prior to IDISC, it will be processed, but for maintaining proper format, ReSA expects it to come after IDISC. |
TTAX |
Either this record or IGTAX should appear in the transaction. IGTAX and TTAX cannot be both used at the same time. |
TPYMT |
This record should be right before the TTEND record. It contains the deposit amount for pickup/delivery/layaway orders. |
CATT |
CATT records must immediately follow their associated TCUST record. |
Table 29-42 Code Type Validations
Record Name | Field Name | Code Type |
---|---|---|
Transaction Header |
Transaction Type |
TRAT |
Sub-transaction Type |
TRAS |
|
Reason Code |
REAC or values from non_merch_code_head if the transaction type is PAIDOU and the sub-transaction type is MV or EV. |
|
Value Sign |
SIGN |
|
Vender No |
If the transaction type is PAIDOU and the sub-transaction type is MV, this field is validated against the supplier table. If the transaction type is PAIDOU and the sub-transaction type is EV, this field is validated against the partner table. |
|
Transaction Processing System |
TSYS |
|
Transaction Item |
Item Type |
SAIT |
Item Status |
SASI |
|
Item Number Type |
UPCT |
|
Quantity Sign |
SIGN |
|
Taxable Indicator |
YSNO |
|
Price Override Reason Code |
ORRC |
|
Item Swiped Indicator |
YSNO |
|
Sales Type |
SASY |
|
Return Disposition |
INV_STATUS_CODES table |
|
No Inventory Return |
YSNO |
|
Return Reason Code |
SARR |
|
Item Discount |
RMS Promotion Type |
PRMT |
Discount Type |
SADT |
|
Quantity Sign |
SIGN |
|
Transaction Customer |
Customer ID Type |
CIDT |
Customer Attribute |
Attribute Type |
SACA |
Attribute value |
Code types from the codes in SACA. |
|
Transaction Tax |
Tax code |
TAXC from the CODE_DETAIL table or VATC from the VAT_CODES table. |
Tax sign |
SIGN |
|
Transaction Payment |
Payment (Deposit Amount) Sign |
SIGN |
Transaction Tender |
Transaction Tender Tender Type Group |
TENT |
Tender Sign |
SIGN |
|
Tender Type ID |
Pos_tender_type_head table |
|
CC Authorization Source |
CCAS |
|
CC Cardholder Verification |
CCVF |
|
CC Entry Mode |
CCEM |
|
CC Special Condition |
CCSC |
The following dates are validated: Business Date, Transaction Date, and Expiration Date. Also, saimptlog accepts only business dates that are within the PERIOD.VDATE minus the SA_SYSTEM_OPTIONS.DAYS_POST_SALE value.
The store number is validated against the STORE table. Numeric fields are checked for non-numeric characters.
For transactions of type SALE, RETURN, and EEXCH, saimptlog checks whether a transaction is in balance. With the introduction of the Item level tax and Payment amount lines, the balancing logic has been changed as below. Also with introduction of handling VAT/TAX, the logic of balancing has been modified as below.
When TAX is on in the system (system_options.default_tax_type equals SALES):
Transaction Items (Unit Retail * Unit Retail Sign * Quantity) of items which are on Regular Sale, Return, or EEXCH
+ Item Discounts (Unit Discount Amount * Unit Discount Sign * Quantity) of items which are on Regular Sale, Return, or EEXCH
+ Item Level Tax (Total Igtax Amount) of items which are on Regular Sale, Return, or EEXCH
+ Transaction Tax (Tax Amount * Tax Sign)
+ Transaction payment (Payment Amount * Payment Sign)
equals Transaction Tenders (Tender Amount * Tender Sign)
saimptlog will populate the Value field (on THEAD) with the transaction's sales value (item value minus discount value plus tax value) from the preceding calculation if it was not provided in the RTLOG. The following change is made in the sale total balancing: Value field in THEAD will be: (item value - discount value + tax value) for items which are on Regular Sale, Return, or EEXCH + payment value.
Note: If this Value field is being used in creating some totals, then accordingly, these totals needs to be modified to accommodate the extra amount coming in. |
When VAT is on in the system (system_options.default_tax_type in GTAX, SVAT), look for other system options, along with class level and store level VAT indicators, which tell whether the unit retail is inclusive or exclusive of VAT. The logic of balancing will vary:
Transaction Items (Unit Retail * Unit Retail Sign * Quantity) of items which are on Regular Sale, Return, or EEXCH
+ Item Discounts (Unit Discount Amount * Unit Discount Sign * Quantity) of items which are on Regular Sale, Return, or EEXCH
+ Item Level Tax (Total Igtax Amount) of items which are on Regular Sale, Return, or EEXCH (when VAT is off at the item level).
+ Transaction Tax (Tax Amount * Tax Sign)
+ Transaction Payment (Payment Amount * Payment Sign)
equals Transaction Tenders (Tender Amount * Tender Sign)
Vouchers are treated as follows:
If an item sold is as a gift certificate (Transaction Item, Voucher field has a value), the issued information is written to the SA_VOUCHER table.
If the Transaction Type is RETURN, and the Transaction Tender Type Group is voucher (VOUCH), the issued information is written to the SA_VOUCHER table.
If the Transaction Type is SALE and the Transaction Tender Type Group is a voucher (VOUCH), the redeemed information is written to the SA_VOUCHER table.
When a gift certificate is sold, the customer information should always be included. A receiving customer name value should be populated in the ref_no5 field, receiving customer state value should be populated in the ref_no6 field, and receiving customer country should be populated in the ref_no7 field. These reference fields can be changed by updating the sa_reference table, but the code needs to be modified as well. The expiration date is put in the expiration_date field in the TITEM record.
Other validations and points to consider:
The salesperson in the TITEM record takes precedence over the salesperson in the THEAD record.
If an item sold is a sub-transaction (REF) item (Transaction Item, reference item field has a value and item does not), it will be converted to the corresponding transaction level item (ITEM).
If an item sold is an ITEM (Transaction Item, item field has a value), it will be validated against the RMS item tables.
The corresponding Department, Class, Subclass, and Taxable Indicator will be selected from the RMS tables and populated for an item.
The balancing level determines whether the register or the cashier fields are required:
If the balancing level is R (register), the register field on the THEAD must be populated.
If the balancing level is C (cashier), the cashier field on the THEAD must be populated.
If the balancing level is S (store), neither field is required to be populated.
The tax_ind and the item_swiped_ind fields can only accept Y or N values. If an invalid value is passed through the RTLOG, an error will be flagged and the value will be defaulted to Y.
A transaction of type SALE is generated whenever an item is sold. If a sale is for an employee, the sub-transaction type is EMP. If it is a drive-off sale, when someone drives off with unpaid gas, the sub-transaction type is DRIVEO. A special type of sale is an odd exchange, sub-transaction type EXCH, where items are sold and returned in the same transaction. If the net value of the exchange is positive, then it is a sale. If the net value is negative, it is a return.
Requirements per record type (other than what is described in the preceding Layout section):
Table 29-43 Requirements per Record Type
Record Type | Requirements |
---|---|
THEAD |
NA |
TITEM |
|
IDISC |
|
IGTAX |
|
TTAX |
|
TPYMT |
Payment (Deposit amount) sign and Payment (Deposit) amount fields are necessary if this line is appearing. Basically, this is the accumulation of various items being considered in one transaction, which are on pick up/delivery/lay away. |
TTEND |
If the tender type group is COUPON, the Coupon Number field must be populated. The Coupon Reference Number field is optional. |
Meaning of reference number fields:
Note: The meaning of these reference number fields may be changed through the sa_reference table. The transaction type SPLORD is the same as SALE, but the inventory will not be reserved for the orders at its line level. |
Table 29-44 Meaning of Reference Number Fields
Transaction Type | Sub-transaction Type | Item Type | Tender Type Group | Reference Number Field | Meaning of Reference Field | Req? |
---|---|---|---|---|---|---|
SALE |
NA |
NA |
NA |
1 |
Speed Sale Number |
Y |
SALE |
NA |
GCN |
NA |
5 |
Recipient Name |
N |
SALE |
NA |
GCN |
NA |
6 |
Recipient State |
N |
SALE |
NA |
GCN |
NA |
7 |
Recipient Country |
N |
SALE |
NA |
NA |
CHECK |
9 |
Check Number |
N |
SALE |
NA |
NA |
CHECK |
10 |
Driver's License Number |
N |
SALE |
NA |
NA |
CHECK |
11 |
Credit Card Number |
N |
SALE |
DRIVEO |
NA |
NA |
1 |
Incident Number |
Y |
SALE |
EMP |
NA |
NA |
3 |
Employee Number of the employee receiving the goods. |
N |
Table 29-45 Expected Values for Sign Fields
TRANSACTION TYPE | TITEM.Quantity Sign | TEND.Tender Sign | TTAX.Tax Sign | IDISC.Quantity Sign |
---|---|---|---|---|
SALE |
P if item is sold; N if item is returned; reverse of original item if item is voided. |
P |
P |
P if item is sold; N if item is returned; reverse of original item if item is voided. |
SALE |
P if item is on ORI, LIN, ORD, or LCO; N if item is on ORC or LCA. |
P |
P |
P if item is on ORI, LIN, ORD, or LCO; N if item is on ORC or LCA. |
This transaction is generated at the register when another transaction is being post voided. The orig_tran_no and orig_reg_no fields must be populated with the appropriate information for the transaction being post voided. The PVOID transaction must be associated with the same store day as the original transaction. If the PVOID needs to be generated after the store day is closed, the transaction needs to be created using the forms.
This transaction is generated when a customer returns an item.
This type of transaction has similar record type requirements as a SALE transaction.
Meaning of reference number fields:
Note: The assumption is that new item statuses will not come under transaction type RETURN. |
If a customer wants to return the items (ORI, LIN), these will come under SALE but with item statuses as ORC or LCA.
Note: The meaning of these reference number fields may be changed through the sa_reference table. |
Table 29-46 Meaning of Reference Number Fields
Transaction Type | Sub-transaction Type | Reference Number Field | Meaning of Reference Field | Req? |
---|---|---|---|---|
RETURN |
NA |
1 |
Receipt Indicator (Y/N) |
Y |
RETURN |
NA |
2 |
Refund Reference Number |
N |
RETURN |
EMP |
3 |
Employee Number of the employee returning the goods. |
N |
Table 29-47 Expected Values for Sign Fields
TRANSACTION TYPE | TITEM.Quantity Sign | TEND.Tender Sign | TTAX.Tax Sign | IDISC.Quantity Sign |
---|---|---|---|---|
RETURN |
P if item is sold; N if item is returned; reverse of original item if item is voided. |
N |
N |
P if item is sold; N if item is returned; reverse of original item if item is voided |
This transaction is generated when a customer picks up an item, which is not in stock. The item status can be ORI, ORC, or ORD. (Order Initiate, Order Cancel, or Order Complete).
This transaction is generated when there is an even exchange.
This type of transaction has similar record type requirements as a SALE transaction.
It is expected that the number of items returned equals the number of items sold. However, this validation is not performed by saimptlog. An audit rule could be created for this. Saimptlog only expects that there would be at least two item records.
No tender changes hands in this transaction.
Meaning of reference number fields:
Note: The items, which are on customer order or layaway, should not be come under this transaction type.The meaning of these reference number fields may be changed through the sa_reference table. |
This type of transaction has only one TTEND record.
A reason code is required.
Meaning of reference number fields:
Note: The meaning of these reference number fields may be changed through the sa_reference table. |
This type of transaction has only one TTEND record.
A reason code is required (code type REAC). If the sub-transaction type is EV or MV, the reason code comes from the non_merch_codes_head table.
If the sub-transaction type is EV or MV, then at least one field among the vendor number, vendor invoice number, payment reference number, and proof of delivery number fields should be populated.
If the sub-transaction type is EV, the vendor number comes from the partner table. If the sub-transaction type is MV, the vendor number comes from the supplier table.
Meaning of reference number fields:
Note: The meaning of these reference number fields may be changed through the sa_reference table. |
This transaction is generated when cash is withdrawn from the register.
This type of transaction has only one TTEND record.
Expected values for sign fields
This transaction is generated when cash is added to the register.
This type of transaction has only one TTEND record.
Expected values for sign fields:
This transaction records the condition at the store when it opens. There can be at most one COND record containing weather information and at most one COND record containing temperature information. Both of these pieces of information may be in the same COND record. There may be any number of COND records containing traffic and construction information.
This type of transaction does not have TITEM, IDISC, IGTAX, TTAX, TPYMT, or TTEND records
Note: The meaning of these reference number fields may be changed through the sa_reference table. |
This transaction records the totals that are reported by the POS and OMS. The value field must be populated. Some systems generate only one transaction number for all totals. In order to avoid duplicate errors being reported, only one total transaction can have a transaction number and the subsequent ones can have blank transaction numbers. In other words, a TOTAL transaction is not required to have a transaction number.
This type of transaction does not have TITEM, IDISC, IGTAX, TTAX, TPYMT, TTEND records.
This transaction is generated when a meter reading of a fuel pump is taken.
This type of transaction has only TITEM records.
Meaning of reference number fields:
Note: The meaning of these reference number fields may be changed through the sa_reference table. |
Table 29-54 Meaning of Reference Number Fields
Reference Number Column | Meaning | Req? |
---|---|---|
1 |
Reading Type: (A for adjustment, S for shift change, P for price change, or C for store close) |
Y |
5 |
Opening Meter Readings |
Y |
6 |
Closing Meter Reading |
Y |
7 |
If the reading type is P for price change, the old unit retail should be placed here. Decimal places are required. |
Y |
8 |
Closing Meter Value |
Y |
This transaction is generated when a pump test is performed. This type of transaction has only TITEM records.
This transaction is generated when a tank dip measurement is taken.
This type of transaction has only TITEM records.
Meaning of reference number fields:
Note: The meaning of these reference number fields may be changed through the sa_reference table. |
This transaction is generated when the day closed. The transaction number for this type of transaction has to be blank.
Note: Vouchers are minimally handled by saimptlog. Voucher information is written to the savouch file which is passed to the program savouch.pc. |
A voucher will appear on the TITEM record only if it was sold. When saimptlog encounters a SALE transaction with a voucher, it writes the voucher to the savouch file as an I for Issued voucher.
A voucher will be issued when it appears on the TTEND record of transactions of type RETURN and PAIDOU. In other words, saimptlog will write it to the savouch file with status I.
A voucher will be redeemed when it appears on the TTEND record of transactions of type SALE and PAIDIN. In other words, saimptlog will write it to the savouch file with status R.
Vouchers may not be returned. However, a transaction of type PAIDOU may be generated when the customer exchanges a voucher for another form of tender.
Table 29-56 ReSA Valid Transaction Type
Transaction Code | Transaction Type |
---|---|
OPEN |
Open |
CLOSE |
Close |
COND |
Daily Store Conditions |
DCLOSE |
Day close indicator |
LOAN |
Loan |
METER |
Meter Reading for Fuel |
NOSALE |
No Sale |
PAIDIN |
Paid In |
PAIDOU |
Paid Out |
PULL |
Pull |
PUMPT |
Pump Test for Fuel |
PVOID |
Post Void (A transaction that was rung later into the register to void something that occurred earlier at the same store/day. A post void updates the original transaction's sub-transaction type.) |
REFUND |
Return of customer's original check. |
RETURN |
Return |
SALE |
Sale |
TANKDP |
Tank Dip |
TOTAL |
POS generated totals |
EEXCH |
Even exchange |
VOID |
Void (aborted transaction) |
OTHER |
Others |
When the retailer is sending only one file to the system, SAIMPTLOG.PC marks the store day record in the ReSA import log as partially or fully loaded in the database by looking for a transaction type of DCLOSE. However, if the retailer is sending more than one file (as in, for example, a trickle polling situation), the retailer can specify the number of files that the system should expect in combination with the DCLOSE transaction type. This ensures that the system receives all of the files, even if the DCLOSE transaction type is, for some reason, received before the final file.
For example, if 24 files are expected over a given amount of time, and the file with the DCLOSE transaction type is, for some reason, sent before the 24th file, the RMS system waits until the last file arrives before marking the store day record as partially or fully loaded in the database.
The import process is completed after SAIMPTLOGFIN.PC has updated the store, data, and audit status of each store day record.
Module Name | saimptlogtdup_upd.pc |
Description | Processing to Allow Re-Upload of Deleted Transactions |
Functional Area | Oracle Retail Sales Audit |
Module Type | Admin |
Module Technology | ProC |
Integration Catalog ID | RSA19 |
The purpose of this batch module is to fetch all deleted transactions for a store day and modify the tdup<Store><rtlog originating system>.dat file to remove deleted transactions, from the tdup range, in order to facilitate the saimptlog/saimptlogi batch to upload deleted transactions again. The batch will process all the store day with data status in Partially Loaded and Ready For Import and a business date that lies between the vdate minus the sa_syatem_options. day_post_sale and the vdate. The batch will not process a store day, if the tdup<Store><rtlog originating system>.dat file does not exist. The batch is designed to work only if sa_system_options.check_dup_miss_tran is set to Y, otherwise, do nothing and come out with successful completion. Also, the batch will not terminate with an error, if the deleted transaction to be removed from tdup range does not exist in the tdup<Store><rtlog originating system>.dat file.
Module Name | saimptlogfin.pc |
Description | Complete Transaction Import Processing |
Functional Area | Oracle Retail Sales Audit |
Module Type | Admin |
Module Technology | ProC |
Integration Catalog ID | RSA38 |
The saimptlogfin program creates the balances (over or under) by store, register, or cashier and populates it in the SA_BALANCE_GROUP table. It also cancels post voided transactions and vouchers and validates missing transactions. It marks the store day record in the ReSA import log as partially or fully loaded. This will unlock the store day records after all store transactions are imported.
Table 29-59 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
Run towards the end of loading POS transaction data into ReSA. It should run after SAIMPTLOG or SAIMPTLOGI and SAVOUCH, but before SATOTALS.PC. |
Pre-Processing |
Saimptlog/saimptlogi, savouch |
Post-Processing |
satotals |
Threading Scheme |
NA |
Table 29-60 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
STORE_DAY_SEQ_NO |
Yes |
No |
No |
No |
STORE |
Yes |
No |
No |
No |
SA_STORE_DAY |
Yes |
No |
Yes |
No |
SA_CUST_ATTRIB |
Yes |
No |
No |
Yes |
SA_CUSTOMER |
Yes |
No |
No |
Yes |
SA_TRAN_DISC |
Yes |
No |
No |
Yes |
SA_TRAN_ITEM |
Yes |
No |
No |
Yes |
SA_TRAN_TAX |
Yes |
No |
No |
Yes |
SA_TRAN_TENDER |
Yes |
No |
No |
Yes |
SA_ERROR |
Yes |
No |
Yes |
Yes |
SA_MISSING_TRAN |
Yes |
No |
No |
Yes |
SA_TRAN_HEAD |
Yes |
No |
Yes |
Yes |
SA_BALANCE_GROUP |
Yes |
Yes |
No |
No |
SA_TRAN_HEAD CANCEL |
Yes |
No |
No |
No |
SA_STORE_DATA |
Yes |
No |
No |
No |
SA_IMPORT_LOG |
No |
No |
Yes |
No |
SA_TRAN_HEAD_REV |
No |
Yes |
No |
No |
SA_TRAN_ITEM_REV |
No |
Yes |
No |
No |
SA_TRAN_TENDER_REV |
No |
Yes |
No |
No |
SA_TRAN_TAX_REV |
No |
Yes |
No |
No |
SA_TRAN_DISC_REV |
No |
Yes |
No |
No |
SA_ERROR_REV |
No |
Yes |
No |
No |
SA_EXPORTED_REV |
No |
Yes |
No |
No |
SA_TRAN_WRITE_LOCK |
Yes |
No |
No |
Yes |
Module Name | savouch.pc |
Description | Sales Audit Voucher Upload |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA08 |
Because gift certificates can enter the Sales Audit system as either items or tender, processing must be done to match up the sales and redemptions. This module is used to aggregate gift certificate and voucher records. It compares records in the input files to the database. If a record for the voucher does not exist on the database, the record is inserted. If the voucher already exists on the database, the record should be updated with the appropriate information. The voucher details are updated to SA_VOUCHER table.
Some retailers assign gift certificates to a given store, which means that before a gift certificate is sold at a store, it is assigned to a given store. When a retailer assigns a gift certificate to a given store, a record is written to the database. When the gift certificate is then sold by the store and redeemed by the consumer, this existing record must be updated to include the sale and redemption information. Some retailers choose not to assign gift certificates and instead simply sell gift certificates. In that case, the record will be inserted into the database when the gift certificate is sold and then updated when the gift certificate is redeemed.
Table 29-61 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This program needs to be scheduled after either saimpltog.pc and its sqlldr process, or saimpltogi.pc, but before saimpltogfin.pc. |
Pre-Processing |
saimptlog/saimptlogi |
Post-Processing |
Saimptlogfin.pc |
Restart/recovery logic for file-based processing is used. Records will be committed to the database when the commit_max_ctr defined in the RESTART_CONTROL table is reached.
Integration Type | Upload to ReSA |
File Name | The input file name is not fixed; the input file name is determined by a runtime parameter. Records rejected by the import process are written to a reject file. The reject file name is not fixed; the reject file name is determined by a runtime parameter. |
Integration Contract | IntCon000160 (SAVO) |
Table 29-63 Input File Layout
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
Record descriptor/ |
Char(5) |
FHEAD |
File head marker. |
Line id |
Number(10) |
0000000001 |
Unique line ID. |
|
Translator id |
Char(5) |
SAVO |
Identifies transaction type. |
|
File create date |
Char(14) |
NA |
Vdate in YYYYMMDDHH24MISS format. |
|
Business Date |
Char(8) |
Business Date |
Vdate in YYYYMMDD format. |
|
FDETL |
Record descriptor/ |
Char(5) |
FDETL |
File head marker. |
Line id |
Number(10) |
NA |
Unique line ID. |
|
Voucher seq Number |
Number(20) |
NA |
Unique identifier for an entry to the SA_VOUCHER table. |
|
Voucher No |
Char(16) |
NA |
Serial Number of the voucher. |
|
Tender Type Id |
Number(6) |
NA |
Type of Voucher (Valid values for tender type are maintained in the pos_tender_type_head table with tender_type_group as VOUCH. |
|
Assigned Date |
Char(8) |
NA |
Date the voucher was assigned. |
|
Assigned store |
Number(10) |
NA |
Store to which the voucher is assigned. |
|
Issuing Date |
Char(8) |
NA |
Date this document was issued. |
|
Issuing store |
Number(10) |
NA |
Store this document was issued from. |
|
Issuing Register |
Char(5) |
NA |
Register this document was issued from. |
|
Issuing Cashier |
Char(10) |
NA |
Cashier issuing the document. |
|
Issued transaction number |
Number(20) |
NA |
Transaction number at the time of issuance. |
|
Issued item seq number |
Number(4) |
NA |
Will hold the item sequence of the item when the voucher is sold as an item (gift voucher). |
|
Issued tender seq number |
Number(4) |
NA |
Will hold the tender sequence of the tender when the voucher is sold as a tender (Merchandise Credit). |
|
Issued Amount |
Number(20) |
NA |
Amount the voucher was issued for*10000 (4 implied decimal places). |
|
Issued Customer Name |
Char(120) |
NA |
Name of the customer, who was issued the voucher. |
|
Issued Customer Addr1 |
Char(240) |
NA |
The address of the customer who was issued the voucher. |
|
Issued Customer Addr2 |
Char(240) |
NA |
The second line address of the customer who was issued the voucher. |
|
Issued Customer City |
Char(120) |
NA |
City of the customer, the voucher is issued. |
|
Issued Customer State |
Char(3) |
NA |
State of the customer. |
|
Issued Customer Postal Code |
Char(30) |
NA |
Postal address of the customer. |
|
Issued Customer Country |
Char(3) |
NA |
Country of the customer where the voucher was issued. |
|
Recipient Name |
Char(120) |
NA |
Name of the intended recipient. |
|
Recipient State |
Char(3) |
NA |
The state of the intended recipient. |
|
Recipient Country |
Char(3) |
NA |
The country of the intended recipient. |
|
Redemption Date |
Char(8) |
NA |
Date the voucher was redeemed. |
|
Redemption Store |
Number(10) |
NA |
Store at which the voucher was redeemed. |
|
Redemption Register |
Char(5) |
NA |
Register at which the document was redeemed. |
|
Redemption cashier |
Char(10) |
NA |
Cashier redeeming the voucher. |
|
Redemption tran seq number |
Number(20) |
NA |
Transaction Number when the document was redeemed. |
|
Redemption Tender seq number |
Number(4) |
NA |
This column will hold the tender sequence of the tender within the transaction when a voucher is redeemed as tender. |
|
Redemption Amount |
Number(20) |
NA |
Amount the voucher was redeemed for*10000 (4 implied decimal places). |
|
Expiry Date |
Char(8) |
NA |
Expiry Date. |
|
Status |
Char(1) |
NA |
ndicator showing the document's status - issued or redeemed. Valid values = I - Issued, R - Redeemed. |
|
Comments |
Char(2000) |
NA |
Comments. |
|
FTAIL |
Record type |
Char(5) |
FTAIL |
Describes file record and marks the end of file. |
Line id |
Number(10) |
NA |
Unique file line ID. |
|
#lines |
Number(10) |
NA |
Total number of transaction lines in file (not including FHEAD and FTAIL). |
Module Name | saimpadj.pc |
Description | Import Total Value Adjustments From External Systems to ReSA |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA07 |
This module posts external system adjustments to the Sales Audit total value table.
The sales audit adjustments are passed to the module in an external file.
Records that fail necessary validations would be written to the reject file. The input and reject file names are passed as arguments.
Table 29-64 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This module should be executed after the ReSA transaction import process and before the ReSA totaling process. |
Pre-Processing |
Saimptlogfin.pc |
Post-Processing |
Satotoals.pc |
Threading Scheme |
NA |
Restart/recovery logic for file-based processing is used. The logical unit of work for this module is a parameterized number defined in the restart tables.
Record level locking is done on sa_store_day before updating.
Integration Type | Upload to ReSA |
File Name | Determined by runtime parameters. |
Integration Contract | IntCon000047 |
Table 29-66 Input File Layout
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
File Type Record Descriptor |
Char(5) |
FHEAD |
Identifies file record type (the beginning of the input file). |
File Line Identifier |
Number(10) |
Sequential number |
ID of the current line being read from input file. |
|
File head descriptor |
Char(4) |
IMPA |
Describes file line type. |
|
Current date |
Char(14) |
NA |
File date in YYYYMMDDHH24MISS format. |
|
FDETL |
File Type Record Descriptor |
Char(5) |
FDETL |
Identifies the file record type to upload a new deal header. |
File Line Identifier |
Number(10) |
Sequential number |
ID of the current line being read from input file. |
|
Data source |
Char(6) |
NA |
Name of the external system that produced the file. |
|
New value sign |
Char(1) |
NA |
Sign(+/-) for the new value. |
|
New Value |
Number(20) |
NA |
Value for the total entered by Headquarters user*10000 (4 implied decimal places). |
|
Total seq no |
Number(20) |
NA |
Identifies the unique result set for this total ID, total revision, or store/day. Balancing group and index values. |
|
Store |
Number(10) |
NA |
Store number for a store/day combination. |
|
Business Date |
Char(8) |
NA |
Date for store/day combination. |
|
Total id |
Char(10) |
NA |
ID to uniquely identify the total. |
|
Ref no 1 |
Char(30) |
NA |
The first reference value based by which the total is grouped. |
|
Ref no 2 |
Char(30) |
NA |
The second reference value based by which the total is grouped. |
|
Ref no 3 |
Char(30) |
NA |
The third reference value based by which the total is grouped. |
|
FTAIL |
File Type record descriptor |
Char(5) |
FTAIL |
Identifies the file record type (the end of the input file). |
File Line Identifier |
Number(10) |
Sequential number |
ID of the current line being read from input file. |
|
File Record Counter |
Number(10) |
Sequential number |
Number of records/transactions in the current file (only records between head and tail). |
Module Name | satotals.pc |
Description | Calculate Totals based on Client Defined Rules |
Functional Area | Sales Audit, Totals |
Module Type | Business Processing |
Module Technology | ProC |
Integration Catalog ID | RSA16 |
This module produces totals from user-defined total calculation rules. Totaling is integral to the sales auditing process. Totaling provides the values against which auditors can compare receipts. These comparisons find data errors that could be the result of either honest mistakes or fraud. Finding these mistakes during the sales auditing process prevents these errors from being passed on to merchandising and data warehouse systems. Totaling also provides quick access to other numeric figures about the day's sales transactions.
Totaling in ReSA is dynamic. ReSA automatically totals transactions based on calculation definitions that the retailer's users create using the online Totals Calculation Definition Wizard. In addition, the retailer is able to define totals that come from the POS, but that ReSA does not calculate. Whenever users create new calculation definitions or edit existing ones, they become part of the automated totaling process the next time that this process runs.
Table 29-67 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This program will run after the batch program saimptlogfin.pc and before sarules.pc. |
Pre-Processing |
Saimptlogfin.pc |
Post-Processing |
Sarules.pc |
Threading Scheme |
This program runs against a single store at a time. |
The logical unit of work for this program is a SA_STORE_DAY record. Records are committed to the database when the commit_max_ctr defined for SATOTALS on the RESTART_CONTROL table is reached. This program achieves inherent restart/recovery due to the fact that store/day records that are processed will be updated to an audit_status of T for Totaled and will not be fetched by the driving cursor when the program restarts.
Table 29-68 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_STORE_DAY |
Yes |
No |
Yes |
No |
SA_TOTAL |
No |
Yes |
No |
No |
SA_TOTAL_HEAD |
Yes |
No |
No |
No |
SA_ERROR |
No |
Yes |
No |
Yes |
SA_ERROR_WKSHT |
No |
Yes |
No |
Yes |
SA_POS_VALUE |
No |
Yes |
No |
No |
SA_POS_VALUE_WKSHT |
No |
Yes |
No |
No |
SA_SYS_VALUE |
No |
Yes |
No |
No |
SA_SYS_VALUE_WKSHT |
No |
Yes |
No |
No |
SA_ERROR_REV |
No |
Yes |
No |
No |
SA_EXPORTED_REV |
No |
Yes |
No |
No |
SA_EXPORTED |
No |
No |
No |
Yes |
Module Name | sarules.pc |
Description | Evaluate Transactions and Totals based on Client Defined Rules |
Functional Area | Oracle Retail Sales Audit |
Module Type | Business Processing |
Module Technology | ProC |
Integration Catalog ID | RSA17 |
Evaluating rules is integral to the sales auditing process. Rules make the comparisons between data from various sources. These comparisons find data errors that could be the result of either honest mistakes or fraud. Finding these mistakes during the sales auditing process prevents these errors from being passed on to merchandising and data warehouse systems.
Rules in ReSA are dynamic. Aside from basic data validations, rules are not predefined in the system. Retailers have the ability to define them through the online Rule Definition Wizard. Errors uncovered by these rules are available for review online during the interactive audit process. After users modify existing rules or create new ones, they become part of the rules the next time that sarules.pc runs.
Table 29-69 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This program will run after SATOTALS and before SAESCHEAT. It should also be run before SAPREEXP and any of the ReSA export modules that extract store/day transaction data to other applications (for example, SAEXPRMS, SAEXPIM, SAEXPRDW, SAEXPACH, SAEXPUAR, and SAEXPGL). |
Pre-Processing |
satotoals |
Post-Processing |
Saescheat, sapreexp |
Threading Scheme |
This program runs against a single store at a time. |
The logical unit of work for this program is a SA_STORE_DAY record. Records are committed to the database when the commit_max_ctr defined for SARULES on the RESTART_CONTROL table is reached. This program achieves inherent restart/recovery due to the fact that store/day records that are processed will be updated to an audit_status of A (audited), H (HQ errors pending), or S (store errors pending) and will not be fetched by the driving cursor when the program restarts.
Table 29-70 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_STORE_DAY |
Yes |
No |
Yes |
No |
SA_RULE_HEAD |
Yes |
No |
No |
No |
SA_RULE_LOC_TRAIT |
Yes |
No |
No |
No |
SA_ERROR_WKSHT |
No |
Yes |
No |
Yes |
SA_ERROR_TEMP |
No |
Yes |
No |
No |
SA_ERROR |
No |
Yes |
Yes |
Yes |
SA_TOTAL |
No |
No |
Yes |
No |
SA_TRAN_HEAD |
No |
No |
Yes |
No |
SA_TRAN_ITEM |
No |
No |
Yes |
No |
SA_TRAN_DISC |
No |
No |
Yes |
No |
SA_TRAN_TENDER |
No |
No |
Yes |
No |
SA_TRAN_TAX |
No |
No |
Yes |
No |
Module Name | sapreexp.pc |
Description | Prevent Duplicate Export of Total Values from ReSA |
Functional Area | Oracle Retail Sales Audit |
Module Type | Admin |
Module Technology | ProC |
Integration Catalog ID | RSA20 |
When a user modifies or revises a transaction through the ReSAuser application, numerous totals may be affected and require re-totaling. The sales audit pre-export module is designed to compare the latest prioritized version of each total defined for export with the version that was previously sent to each system. If they are the same, an SA_EXPORTED entry is created for the total for that particular system, so that the same value will not be exported twice. By determining which totals have not changed since the last export date time (SA_EXPORTED_REV), this module will then create entries on SA_EXPORTED to prohibit any third-party application from receiving multiple export revisions.
Table 29-71 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This module should be run after the ReSA auditing process and before any export processes. |
Pre-Processing |
Sarules.pc |
Post-Processing |
saexpach saexpgl saexpim saexpdw saexpsim saexprms saexpuar |
Threading Scheme |
NA |
The logical unit of work for this module is defined as a unique store/day combination. Only two commits will be done. One to establish the store/day lock (this will be done by the package) and one at the end after a store/day or store/day/total has been completely processed.
Module Name | saexprms.pc |
Description | Export of POS transactions from ReSA to RMS |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA01 |
The purpose of this batch module is to fetch all sale and return transactions that do not have RMS errors from the ReSA database tables for transmission to Oracle Retail Merchandising System (RMS). Transaction data is rolled up to the item/store/day/price point/sales type level for the SALES transaction type and item/store/day/price point/sales type/no inventory return indicator/return disposition/return warehouse level for the RETURN transaction types.
If the Unit of Work system parameter is defined as S, the whole store/day is skipped if any RMS error is found. If this value is T, only transactions with RMS errors are skipped.
If the transaction has a status of Deleted and it has previously been transmitted, a reversal of the transaction will be sent.
A file is generated for each store/day.
Table 29-73 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This program should run towards the end of the Sales Auditing cycle where the total (SATOTALS.PC) and rule (SARULES.PC) data are ready to be exported to the external systems. |
Pre-Processing |
NA |
Post-Processing |
saprepost saexprms post |
Threading Scheme |
Multi-threaded by store |
The logical unit of work for this module is defined as a unique store/day combination. Records will be fetched, updated, and inserted in batches of pl_commit_max_ctr. Only two commits will be done, one to establish the store/day lock and another at the end, to release the lock after a store/day has been completely processed. The POSU formatted output file will be created with a temporary name and renamed just before the end of store/day commit.In case of failure, all work done will be rolled back to the point right after the call to get_lock() and the lock will be released. Thus, the rollback segment should be large enough to hold all inserts into sa_exported for one store/day.
Table 29-74 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_STORE_DAY |
Yes |
No |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
Yes |
No |
V_RESTART_STORE |
Yes |
No |
No |
No |
STORE |
Yes |
No |
No |
No |
CURRENCIES |
Yes |
No |
No |
No |
SA_TRAN_HEAD |
Yes |
No |
No |
No |
SA_ERROR |
Yes |
No |
No |
No |
SA_ERROR_IMPACT |
Yes |
No |
No |
No |
SA_EXPORTED |
Yes |
Yes |
No |
No |
SA_TRAN_SEQ_TEMP |
No |
Yes |
No |
Yes |
SA_TRAN_HEAD_REV |
Yes |
No |
No |
No |
SA_EXPORTED_REV |
Yes |
No |
No |
No |
SA_SYSTEM_OPTIONS |
Yes |
No |
No |
No |
SA_TRAN_ITEM_REV |
Yes |
No |
No |
No |
ITEM_MASTER |
Yes |
No |
No |
No |
SA_TRAN_DISC_REV |
Yes |
No |
No |
No |
SA_TRAN_DISC |
Yes |
No |
No |
No |
SA_TRAN_ITEM |
Yes |
No |
No |
No |
SA_TRAN_SEQ_TEMP |
Yes |
Yes |
No |
Yes |
SA_STORE_DAY_READ_LOCK |
No |
Yes |
No |
Yes |
SA_TRAN_IGTAX_REV |
Yes |
No |
No |
No |
SA_TRAN_IGTAX |
Yes |
No |
No |
No |
SA_TRAN_TAX |
Yes |
No |
No |
No |
SA_TRAN_TAX_REV |
Yes |
No |
No |
No |
VAT_ITEM |
Yes |
No |
No |
No |
Integration Type | Download from ReSA |
File Name | POSU_ appended with store number, business date and system date |
Integration Contract | IntCon000044 |
Table 29-75 File Layout
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
Record descriptor |
Char(5) |
FHEAD |
Identifies the file record type. |
File Line Id |
Char(10) |
0000000001 |
Sequential file line number. |
|
File type definition |
Char(4) |
POSU |
Identifies the file type |
|
File Create Date |
Char(14) |
NA |
File Create Date in YYYYMMDDHHMMSS format. |
|
Store |
Number(10) |
NA |
Store location. |
|
Vat include indicator |
Char(1) |
NA |
Determines whether or not the store values include VAT. Not required, but populated by ReSA. |
|
Vat region |
Number(4) |
NA |
VAT region the given location is in. Not required, but populated by ReSA. |
|
Currency code |
Char(3) |
NA |
Currency of the given location. Not required, but populated by ReSA. |
|
Currency retail decimals |
Number(1) |
NA |
Number of decimals supported by given the currency for retails. Not required, but populated by ReSA. |
|
THEAD |
Record descriptor |
Char(5) |
THEAD |
Identifies the file record type. |
File Line Id |
Char(10) |
NA |
Sequential file line number. |
|
Transaction date |
Char(14) |
NA |
Transaction date in YYYYMMDDHHMMSS format. Corresponds to the date that the sale/return transaction was processed at the POS. |
|
Item Type |
Char(3) |
REF or ITM |
Can be REF or ITM. |
|
Item |
Char(25) |
NA |
ID number of the ITM or REF. |
|
Dept |
Number(4) |
NA |
Department of item sold or returned. |
|
Class |
Number(4) |
NA |
Class of item sold or returned. |
|
Sub Class |
Number(4) |
NA |
Subclass of item sold or returned. |
|
Pack Ind |
Char(1) |
NA |
Pack indicator of item sold or returned. |
|
Item Level |
Number(1) |
NA |
Item level of item sold or returned. |
|
Tran level |
Number(1) |
NA |
Transaction level of item sold or returned. |
|
Wastage Type |
Char(6) |
NA |
Wastage type of item sold or returned. |
|
Wastage pct |
Number(12) |
NA |
Waste pct (4 implied decimal places). |
|
Tran type |
Char(1) |
NA |
Transaction type code to specify whether transaction is a sale or a return. |
|
Drop Shipment indicator |
Char(1) |
NA |
Indicates whether the transaction is a drop shipment or not. |
|
Total sales qty |
Number(12) |
NA |
Total sales quantity (4 implied decimal places). |
|
Selling UOM |
Char(4) |
NA |
Selling Unit of Measure for the item. |
|
Sales sign |
Char(1) |
NA |
Determines if the Total Sales Quantity and Total Sales Value are positive or negative. |
|
Total Sales Value |
Number(20) |
NA |
Total sales value of goods sold/returned (4 implied decimal places). |
|
Last Date time modified |
Char(14) |
NA |
Date and time of last modification in YYYYMMDDHHMMSS format. |
|
Catchweight indicator |
Char(1) |
NA |
Indicates if item is a catchweight item. |
|
Total weight |
Number(12) |
NA |
The actual weight of the item, only populated if catchweight_ind = Y. |
|
Sub Tran type indicator |
Char(1) |
NA |
Transction type for ReSA. Valid values are A, D, and NULL. |
|
Total IGTAX Value |
Number(20) |
NA |
This indicates total of all IGTAX amount for the item. |
|
Sales Type |
Char(1) |
NA |
This column indicates whether the line item is a Regular Sale, a customer order serviced by OMS (External CO), or a customer order serviced by a store (In Store CO). |
|
No Inventory Return Indicator |
Char(1) |
NA |
This column contains an indicator that identifies a return without inventory. This is generally a non-required column, but in the case of Returns, this is required. |
|
Return Disposition |
Char(10) |
NA |
This column contains the disposition code published by Oracle Retail Warehouse Management System (RWMS0 as part of the Returns upload to OMS. |
|
Return Warehouse |
Char(10) |
NA |
This column contains the physical warehouse ID for the warehouse identifier where the item was returned. |
|
TTAX |
Record descriptor |
Char(5) |
TTAX |
Identifies the file record type. |
File Line Id |
Char(10) |
NA |
Sequential file line number. |
|
Tax Code |
Char(6) |
NA |
The Tax Code of the item. |
|
Tax Rate |
Number(20) |
NA |
The tax rate of the item (10 implied decimal places). |
|
Total Tax Amount |
Number(20) |
NA |
The item level tax or prorated transaction level tax of the item (4 implied decimal places). |
|
TDETL |
Record descriptor |
Char(5) |
TDETL |
Identifies the file record type. |
File Line Id |
Char(10) |
NA |
Sequential file line number. |
|
Promo Tran Type |
Char(6) |
NA |
Code for the promotional type from code_detail where code_type equals PRMT. |
|
Promotion Number |
Number(10) |
NA |
Promotion number from RMS. |
|
Sales quantity |
Number(12) |
NA |
Sales quantity sold for this promotion type (4 implied decimal places). |
|
Sales value |
Number(20) |
NA |
Sales value for this promotion type (4 implied decimal places). |
|
Discount value |
Number(20) |
NA |
Discount value for this promotion type (4 implied decimal places). |
|
Promotion component |
Number(10) |
NA |
Links the promotion to additional pricing attributes. Contains the offer ID from RPM. |
|
TTAIL |
Record descriptor |
Char(5) |
TTAIL |
Identifies the file record type. |
File Line Id |
Char(10) |
NA |
Sequential file line number. |
|
Tran Record Counter |
Number(6) |
NA |
Number of TDETL records in this transaction set. |
|
FTAIL |
Record descriptor |
Char(5) |
FTAIL |
Identifies the file record type. |
File Line Id |
Number(10) |
NA |
Sequential file line number. |
|
File Record counter |
Number(10) |
NA |
Number of records/transactions processed in the current file (only records between head and tail). |
Tax can be sent either in TTAX or IGTAX regardless of the default_tax_type of SVAT, GTAX, or SALES. But prorated tax in TTAX will only be sent to RMS in an SVAT configuration since proration is based on VAT_ITEM and VAT_ITEM and is only defined for SVAT.
POS can send either transactional level tax details in TTAX lines or item-level tax details in IGTAX lines through the RTLOG file to ReSA. These tax details will be passed on to RMS in the TTAX lines of the POSU file. Even though POS can pass multiple IGTAX/TTAX lines to ReSA and from ReSA to RMS, RMS only supports one tax code per item. If multiple taxes for an item are sent from POS to ReSA, they will be summed to a single tax in RMS sales upload process and assigned one of the applicable tax codes when writing tran_data 88.
Module Name | saordinvexp.pc |
Description | Export Inventory Reservation/Release for In Store Customer Order & Layaway Transactions from ReSA |
Functional Area | Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA12 |
This batch program will generate a flat file to reserve or un-reserve the inventory for items on in-store customer order or layaway transactions. Inventory will be reserved for items on customer order/layaway initiate and un-reserved for customer order/layaway cancel or complete transactions.
Customer orders can be categorized into two categories: In-Store Customer Orders and External Customer Orders. The In-Store Customer Orders are defined as orders that are serviced at the store and inventory reservation is done in Oracle Retail Store Inventory Management (SIM). While the External Customer orders are serviced by an external order management system, no inventory reservation will be made at the store in SIM.
This batch should only process records where the sales type is not equal to External Customer Sales, as it handles only the in-store type orders.
The logical unit of work for this module is defined as a unique store/day combination.Records are fetched, updated, and inserted in batches of pl_commit_max_ctr. Only two commits are done, one to establish the store/day lock and another at the end, to releasethe lock after a store/day is completely processed. The ORIN formatted output file is created with a temporary name and renamed just before the end of store/day commit.In case of failure, all work done is rolled back to the point right after the call to get_lock() and the lock is released. Thus, the rollback segment should be large enough to hold all inserts into sa_exported for one store/day.
Table 29-77 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_SYSTEM_OPTIONS |
Yes |
No |
No |
No |
SA_EXPORTED |
Yes |
Yes |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
Yes |
No |
SA_STORE_DAY |
Yes |
No |
No |
No |
SA_ERROR |
Yes |
No |
No |
No |
SA_ERROR_IMPACT |
Yes |
No |
No |
No |
SA_TRAN_HEAD |
Yes |
No |
No |
No |
SA_TRAN_HEAD_REV |
Yes |
No |
No |
No |
SA_TRAN_ITEM |
Yes |
No |
No |
No |
SA_TRAN_ITEM_REV |
Yes |
No |
No |
No |
ITEM_MASTER |
Yes |
No |
No |
No |
Integration Type | Inventory Export from ReSA to RMS |
File Name | ORIN_<store>_<tran_date>_<sysdate> |
Integration Contract | IntCon000049 |
Table 29-78 Output File Layout
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
Record descriptor |
Char(5) |
FHEAD |
Identifies the file record type. |
File Line Id |
Char(10) |
0000000001 |
Sequential file line number. |
|
File type Definition |
Char(4) |
ORIN |
Identifies the file type. |
|
File Create Date |
Char(14) |
NA |
File Create Date in YYYYMMDDHHMMSS format. |
|
Location |
Number(10) |
NA |
Store location number. |
|
THEAD |
Record descriptor |
Char(5) |
THEAD |
Identifies the file record type. |
File Line Id |
Char(10) |
Sequential file line number. |
||
Transaction Date & Time |
Char(14) |
Transaction Date |
Date and time of the order processed. |
|
Transaction Type |
Char(6) |
SALE |
Transaction type code specifies whether the transaction is sale or return. |
|
TDETL |
Record descriptor |
Char(5) |
TDETL |
Identifies the file record type. |
File Line Id |
Char(10) |
NA |
Sequential file line number. |
|
Item Type |
Char(3) |
REF or ITM |
Can be REF or ITM. |
|
Item |
Char(25) |
NA |
ID number of the ITM or REF. |
|
Item Status |
Char(6) |
LIN - Layaway Initiate LCA - Layaway Cancel LCO - Layaway Complete PVLCO - Post void of Layaway complete ORI - Pickup/delivery Initiate ORC - Pickup/delivery Cancel ORD - Pickup/delivery Complete PVORD - Post void of Pick-up/delivery complete |
Type of transaction. |
|
Dept |
Number(4) |
NA |
Department of item sold or returned. |
|
Class |
Number(4) |
NA |
Class of item sold or returned |
|
Sub class |
Number(4) |
NA |
Subclass of item sold or returned. |
|
Pack Ind |
Char(1) |
NA |
Pack indicator of item sold or returned. |
|
Quantity Sign |
Chanr(1) |
P or N |
Sign of the quantity. |
|
Quantity |
Number(12) |
NA |
Quantity * 10000 (4 implied decimal places), number of units for the given order (item) status. |
|
Selling UOM |
Char(4) |
NA |
UOM at which this item was sold. |
|
Catchweight Ind |
Char(1) |
NA |
Indicates if the item is a catchweight item. Valid values are Y or NULL. |
|
Customer Order number |
Char(48) |
NA |
Customer Order number. |
|
TTAIL |
File Type Record Descriptor |
Char(5) |
TTAIL |
Identifies file record type. |
File Line Identifier |
Number(10) |
Specified by ReSA |
ID of current line being processed by input file. |
|
Transaction count |
Number(6) |
Specified by ReSA |
Number of TDETL records in this transaction set. |
|
FTAIL |
File Type Record Descriptor |
Char(5) |
FTAIL |
Identifies file record type. |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
|
File Record Counter |
Number(10) |
Number of records/transactions processed in the current file (only records between FHEAD and FTAIL). |
Module Name | saexpdw.pc |
Description | Export from ReSA to Oracle Retail Analytics |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Catalog ID | RSA02 |
Runtime Parameters | NA |
The purpose of this batch module is to fetch all sales and return transactions that do not have Retail Analytics errors from the ReSA database tables for transmission to the Oracle Retail Analytics application. The data will be sent at the store day level. If the transaction has a status of Deleted, and if it has been previously Transmitted, a reversal of the transaction will be sent.
Note: This batch program can be run in two modes - trickle mode and batch mode. If 'Y' is passed as a parameter while running the batch program, then the batch runs in trickle mode. If 'N' or no parameter is passed, it runs in normal batch mode. |
The logical unit of work for this module is defined as a unique store/day combination. Records will be fetched, updated, and inserted based on the commit_max_ctr. Only two commits will be done: one to establish the store/day lock and another at the end, to release the lock after a store/day has been completely processed. The RDWT, RDWF, RDWS, and RDWC formatted output files will be created with temporary names and renamed just before the end of store/day commit.In case of a failure, all the work done will be rolled back to the point right after the call to get_lock() and the lock is released. Thus, the rollback segment should be large enough to hold all inserts into sa_exported for one store/day.
Table 29-80 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_STORE_DAY |
Yes |
No |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
No |
No |
V_RESTART_STORE |
Yes |
No |
No |
No |
SA_STORE_PRICE_HIST_TEMP |
No |
Yes |
No |
No |
SA_TRAN_HEAD |
Yes |
No |
No |
No |
SA_CUSTOMER |
Yes |
No |
No |
No |
SA_STORE_EMP |
Yes |
No |
No |
No |
SA_ERROR |
Yes |
No |
No |
No |
SA_ERROR_IMPACT |
Yes |
No |
No |
No |
SA_EXPORTED |
Yes |
No |
No |
No |
SA_TRAN_HEAD_REV |
Yes |
No |
No |
No |
SA_EXPORTED_REV |
Yes |
No |
No |
No |
SA_TRAN_ITEM |
Yes |
No |
No |
No |
SA_TRAN_ITEM_REV |
Yes |
No |
No |
No |
SA_TRAN_DISC |
Yes |
No |
No |
No |
SA_TRAN_DISC_REV |
Yes |
No |
No |
No |
SA_TRAN_TENDER |
Yes |
No |
No |
No |
SA_VOUCHER |
Yes |
No |
No |
No |
SA_TRAN_TENDER_REV |
Yes |
No |
No |
No |
SA_STORE_DAY_READ_LOCK |
No |
Yes |
No |
Yes |
Integration Type | Download from ReSA |
File Name | RDWT_ appended with store number, business date, and system date.
RDWF_ appended with store number, business date, and system date. RDWS_ appended with store number, business date, and system date. RDWC_ appended with store number, business date, and system date. |
Integration Contract | IntCon000041 (RDWT)
IntCon000156 (RDWF) IntCon000157 (RDWS) IntCon000158 (RDWC) |
Four output files will be created for each store_day:
RDWT - Transaction File
RDWF - Form of Payment (Tender) file
RDWS - Store Totals output file
RDWC - Cashier output File
Each output file is converted into a format for loading into Retail Analytics by the resa2dw Perl script.
File layouts for the interface between sales audit and Retail Analytics.
Char fields are left justified and blank filled.
Number fields are right justified and zero filled. They can contain only numbers.
Numeric fields are left justified and blank filled. They can contain only numbers.
Table 29-81 RDWT File Layout
Record Name | Field Name | Field Type | Default Value | Description | Required |
---|---|---|---|---|---|
File Header |
File Type Record Descriptor |
Char(5) |
FHEAD |
Identifies file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
File Type Definition |
Char(4) |
RDWT |
Identifies file as Retail Analytics Transaction file. |
Yes |
|
File Create Date |
Number(14) |
Create date |
Date file was written by external system. Format YYYYMMDDHH24MISS |
Yes |
|
Transaction Header |
File Type Record Descriptor |
Char(5) |
THEAD |
Identifies transaction record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
Business date |
Number(8) |
NA |
Format YYYYMMDD (Note: This is the date the Retail Analytics will consider the transaction date.) |
Yes |
|
Transaction Date |
Number(14) |
Transaction date |
Date sale/return transaction was processed at the POS. Format YYYYMMDDHH24MISS (Note: the Retail Analytics only uses the HH24MI part of this date.) |
Yes |
|
Location |
Number(10) |
Specified by external system |
Store or warehouse identifier |
Yes |
|
Register ID |
Char(5) |
NA |
The register identifier. |
Yes, -1 for null |
|
Banner ID |
Char(4) |
NA |
The unique identifier of the banner. |
Yes, -1 for null |
|
Line Media ID |
Char(10) |
NA |
The identifier of the media for the order line. For non-merchandise items, such as Shipping & Handling, Service Lines. and gift certificates, the media code will be that of the order line with which it is associated. |
Yes, -1 for null |
|
Selling Item ID |
Char(25) |
NA |
The unique identifier of a selling item. |
Yes, -1 for null |
|
Customer Order Header ID |
Char(48) |
NA |
The unique identifier of a customer order. |
Yes, -1 for null |
|
Customer Order Line ID |
Char(30) |
NA |
The identifier of a customer order line. For a Value Added Service, such as monogramming, this will be the line number for the item which the service was applied. |
Yes, -1 for null |
|
Customer Order Create Date |
Char(8) |
NA |
The date when the customer order was created/placed. |
Yes, -1 for null |
|
Cashier Identifier |
Char(10) |
NA |
The cashier number. This will be the unique employee number. |
Yes, -1 for null |
|
Salesperson Identifier |
Char(10) |
NA |
The salesperson number. This will be the unique employee number. |
Yes, -1 for null |
|
Customer ID Type |
Char(6) |
NA |
The type of ID number used by this customer. |
Yes, -1 for null |
|
Customer ID Number |
Char(16) |
NA |
Customer ID associated with the transaction. |
Yes, -1 for null |
|
Transaction Number |
Number(10) |
NA |
The unique transaction reference number generated by the POS. |
Yes |
|
Original Register ID |
Char(5) |
NA |
Register ID of the original transaction. |
Yes for a transaction type of PVOID. |
|
Original Transaction Number |
Number(10) |
NA |
Transaction number of the original transaction. |
Yes for a transaction type of 'PVOID, EEXCGH and RETURN |
|
Transaction Header Number |
Numeric(20) |
NA |
Unique reference used within sales audit to represent the date/store/register/tran_no. |
Yes |
|
Revision number |
Number(3) |
NA |
Number used to identify the version of the transaction being sent. |
Yes |
|
Sales Sign |
Char(1) |
P - positive N - negative |
Determines if the Total Sales Quantity and Total Sales Value are positive or negative. |
Yes |
|
Transaction Type |
Char(6) |
NA |
Transaction type code. |
Yes |
|
Sub Transaction Type |
Char(6) |
NA |
The Sub Transaction type. |
Yes, -1 for null |
|
Retail Type |
Char(1) |
R (Regular), P (Promo), or C (Clearance) |
NA |
Yes |
|
Item_Seq_No |
Number(4) |
NA |
The order in which items were entered during the transaction. |
No |
|
Employee Number (Cashier) |
Char(10) |
NA |
Employee identification number. This will only be populated if the sub transaction type is EMP. |
Yes, -1 for null |
|
Receipt Indicator |
Char(1) |
NA |
Flag that identifies returns that have been processed without a receipt. This field will only be populated if the transaction type is RETURN. |
No |
|
Reason Code |
Char(6) |
NA |
A reason is required with a Paid In/Out transaction type, and optional with a return transaction. |
Yes, -1 for null |
|
Vendor number |
Numeric(10) |
NA |
This will only get populated when the paid in code is Expense Vendor. |
No |
|
Item Type |
Char(6) |
item type identifier |
Type of item sold: ITEM, REF, GCN (gift certificate number), or NMITEM. |
No |
|
Item |
Char(25) |
NA |
ID number of the item or gift certificate. |
No. Required if Item Type is not null. |
|
Ref Item |
Char(25) |
NA |
Sub-transaction level item. |
No. Also, this field can never be populated without a transaction level item in the item field. |
|
Taxable Indicator |
Char(1) |
NA |
Taxable/non-taxable status indicator. |
No |
|
Entry/mode |
Char(6) |
NA |
Indicator that identifies whether the item was scanned or manually entered. |
No |
|
Department |
Number(4) |
NA |
Department of item sold or returned. Need to validate if using ReSA. |
No |
|
Class |
Number(4) |
NA |
Class of item sold or returned. Need to validate if using ReSA. |
No |
|
Subclass |
Number(4) |
NA |
Subclass of item sold or returned. Need to validate if using ReSA. |
No |
|
Total Sales Quantity |
Number(12) |
NA |
Number of units sold at a particular location, with 4 implied decimal places. |
No |
|
Total Transaction Value |
Number(20) |
NA |
Sales value, net sales value of goods sold/returned, with 4 implied decimal places. |
No |
|
Override Reason |
Char(6) |
NA |
This column will be populated when an item's price has been overridden at the POS to define why it was overridden. This will also always be sent if the transaction originated in RCOM. |
Yes, -1 for null |
|
Return Reason |
Char(6) |
NA |
The reason an item was returned. |
Yes, -1 for null |
|
Total original sign |
Char(1) |
'P'- positive 'N' - negative |
NA |
No |
|
Total Original Sales Value |
Number(20) |
NA |
This column will be populated when the item's price was overridden at the POS and the item's original unit retail is known. This will always be written when the transaction originated in RCOM. This has 4 implied decimals. |
No |
|
Weather |
Char(6) |
NA |
For transaction types of COND, this field will store the type of weather for the store-day. |
No |
|
Temperature |
Char(6) |
NA |
For transaction types of COND, this field will store the type of temperature for the store-day. |
No |
|
Traffic |
Char(6) |
NA |
For transaction types of COND, this field will store the type of traffic for the store-day. |
No |
|
Construction |
Char(6) |
NA |
For transaction types of COND, this field will store information regarding any construction on that store-day. |
No |
|
Drop Shipment Indicator |
Char(1) |
Y or N |
Indicates whether the item is involved in a drop shipment. |
No |
|
Item Status |
Char(6) |
NA |
The status of the item, required for voided or exchanged items. Valid values are found in the code_detail table under code_type SASI. |
Y, -1 for null |
|
Tran Process Sys |
Char(3) |
NA |
This column holds the name of the system that processed the transaction. This will be used for filtering duplicate transactions coming from the different systems for export to downstream systems. Expected values are POS - Point of Sale, OMS - Order Management System and, SIM - Store Inventory Management. |
Y, -1 for null |
|
Return Wh |
Number(10) |
NA |
This column contains the physical warehouse ID for the warehouse identifier where the item was returned. |
N, -1 for null |
|
Fulfill Order No |
Char(48) |
NA |
This column holds the number from OMS related to the fulfillment details. One or more fulfillment orders could relate back to a single customer order in OMS. This column is required if the order is a cross channel order (that is, Sales Type equals E) and the item status is ORD. |
N, -1 for null |
|
No Inventory Return Ind |
Char(1) |
NA |
This column contains an indicator that identifies a return without inventory. This is generally a non-required column, but in the case of returns, this is required. |
No |
|
Sales Type |
Char(1) |
NA |
This column indicates whether the line item is a Regular Sale, a customer order serviced by OMS (External CO), or a customer order serviced by a store (In Store CO). |
Yes |
|
Return Disposition |
Char(10) |
NA |
This column will contain the disposition code published by RWMS as part of the Returns upload to OMS. |
N, -1 for null |
|
Original Store |
Char(10) |
This column contains the store ID for the original store. |
N |
||
Original Transaction Number |
Number(10) |
Original transaction number for the returned item. |
N |
||
Transaction Detail |
File Type Record Descriptor |
Char(5) |
TDETL |
Identifies transaction record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
Discount Type |
Char(6) |
NA |
Code for discount type from code_detail, code_type equals SADT. |
No |
|
Promotional Transaction Type |
Char(6) |
NA |
Code for promotional type from code_detail, code_type equals PRMT. |
Yes |
|
Promotion Number |
Numeric(10) |
Promotion number |
Promotion number from the RMS. |
No |
|
Promotion Component Number |
Numeric(10) |
Offer ID from RPM |
NA |
Required if it is a promotional sale. |
|
Coupon Number |
Char(40) |
NA |
NA |
Yes, if Discount Type is SCOUP. |
|
Coupon Reference Number |
Char(16) |
NA |
NA |
No |
|
Sales Quantity |
Number(12) |
NA |
Number of units sold in this promotion type, with 4 implied decimal places. |
No |
|
Transaction Sign |
Char(1) |
P- positive N - negative |
NA |
Yes |
|
Transaction Value |
Number(20) |
NA |
Value of units sold in this promotion type, with 4 implied decimal places. |
Yes |
|
Discount Value |
Number(20) |
NA |
Value of discount given in this promotion type, with 4 implied decimal places. |
Yes |
|
Transaction Trailer |
File Type Record Descriptor |
Char(5) |
TTAIL |
Identifies file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of current line being processed by input file. |
Yes |
|
Transaction Count |
Number(6) |
Specified by external system |
Number of TDETL records in this transaction set. |
Yes |
|
File Trailer |
File Type Record Descriptor |
Char(5) |
FTAIL |
Identifies file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
File Record Counter |
Number(10) |
NA |
Number of records/transactions processed in the current file (only records between head and tail). |
Yes |
Table 29-82 File Layout
Field Name | Field Type | Default Value | Description | Required |
---|---|---|---|---|
Business date |
Number(8) |
NA |
Format YYYYMMDD. |
Yes |
Transaction Date |
Number(14) |
Transaction Date |
Date sale/return transaction was processed at the POS. Format YYYYMMDDHH24MISS |
Yes |
Location |
Number(10) |
Specified by external system |
Store or warehouse identifier. |
Yes |
Register ID |
Char(5) |
NA |
The register identifier. |
Yes, -1 for null |
Banner ID |
Char(4) |
NA |
The unique identifier of the banner. |
Yes, -1 for null |
Line Media ID |
Char(10) |
NA |
The identifier of the order line media. For non-merchandise items, such as Shipping & Handling, Service Lines, and gift certificates, the media code will be that of the order line with which is it is associated. |
Yes, -1 for null |
Selling Item ID |
Char(25) |
NA |
The unique identifier of a selling item. |
Yes, -1 for null |
Customer Order Header ID |
Char(48) |
NA |
The unique identifier of a customer order. |
Yes, -1 for null |
Customer Order Line ID |
Char(30) |
NA |
The identifier of a customer order line. For a Value Added Service, such as monogramming, this will be the line number for the item, which the service was applied. |
Yes, -1 for null |
Customer Order Create Date |
Number(8) |
NA |
The customer order creation date. |
Yes, transaction date for null |
Cashier Identifier |
Char(10) |
NA |
The cashier number. This will be the unique employee number. |
Yes, -1 for null |
Salesperson Identifier |
Char(10) |
NA |
The salesperson number. This will be the unique employee number. |
Yes, -1 for null |
Customer ID Type |
Char(6) |
NA |
The type of ID number used by this customer. |
Yes, -1 for null |
Customer ID Number |
Char(16) |
NA |
Customer ID associated with the transaction. |
Yes, -1 for null |
Transaction Number |
Number(10) |
NA |
The unique transaction reference number generated by the POS. |
Yes |
Original Register ID |
Char(5) |
NA |
Register ID of the original transaction. |
Yes for a transaction type of 'PVOID'. |
Original Transaction Number |
Number(10) |
NA |
Transaction number of the original transaction. |
Yes for a transaction type of 'PVOID'. |
Transaction Header Number |
Numeric(20) |
NA |
Unique reference used within sales audit to represent the date/store/register/tran_no. |
Yes |
Revision number |
Number(3) |
NA |
Number used to identify the version of the transaction being sent. |
Yes |
Sales Sign |
Char(1) |
P - positive N - negative |
Determines if the Total Sales Quantity and Total Sales Value are positive or negative. |
Yes |
Transaction Type |
Char(6) |
NA |
Transaction type code. |
Yes |
Sub Transaction Type |
Char(6) |
NA |
The Sub Transaction type. |
Yes, -1 for null |
Retail Type |
Char(1) |
R (Regular), P (Promo), or C (Clearance) |
NA |
Yes |
Item_Seq_No |
Number(4) |
NA |
The order in which items were entered during the transaction. |
No |
Employee Number (Cashier) |
Char(10) |
NA |
Employee identification number. This will only be populated if the sub transaction type is EMP. |
Yes, -1 for null |
Receipt Indicator |
Char(1) |
NA |
Flag that identifies returns that have been processed without a receipt. This field will only be populated if the transaction type is RETURN. |
No |
Reason Code |
Char(6) |
NA |
A reason is required with a Paid In/Out transaction type, and optional with a return transaction. |
Yes, -1 for null |
Vendor number |
Numeric(10) |
NA |
This will only get populated when the paid in code is Expense Vendor |
No |
Item Type |
Char(6) |
Item type identifier |
Type of item sold, ITEM, REF, GCN (gift certificate number), or IMITEM. |
No |
Item |
Char(25) |
NA |
ID number of the item or gift certificate. |
No. Required if Item Type is not null. |
Ref Item |
Char(25) |
NA |
Sub-transaction level item. |
No. Also, this field can never be populated without a transaction level item in the item field. |
Taxable Indicator |
Char(1) |
NA |
Taxable/non-taxable status indicator. |
No |
Entry/mode |
Char(6) |
NA |
Indicator that identifies whether the item was scanned or manually entered. |
No |
Department |
Number(4) |
NA |
Department of item sold or returned. Need to validate if using ReSA. |
No |
Class |
Number(4) |
NA |
Class of item sold or returned. Need to validate if using ReSA. |
No |
Subclass |
Number(4) |
NA |
Subclass of item sold or returned. Need to validate if using ReSA. |
No |
Total Sales Quantity |
Number(12) |
NA |
Number of units sold at a particular location, with 4 implied decimal places. |
No |
Total Transaction Value |
Number(20) |
NA |
Sales value, net sales value of goods sold/returned, with 4 implied decimal places. |
No |
Override Reason |
Char(6) |
NA |
This column will be populated when an item price has been overridden at the POS to define why it was overridden. This will always be sent if the transaction originated in RCOM. |
Yes, -1 for null |
Return Reason |
Char(6) |
NA |
The reason an item was returned. |
Yes, -1 for null |
Total original sign |
Char(1) |
P- positive N - negative |
NA |
No |
Total Original Sales Value |
Number(20) |
NA |
This column will be populated when the item's price was overridden at the POS and the item's original unit retail is known. This will always be sent if the transaction originated in RCOM. This has 4 implied decimals. |
No |
Weather |
Char(6) |
NA |
For transaction types of COND, this field will store the type of weather for the store-day. |
No |
Temperature |
Char(6) |
NA |
For transaction types of COND, this field will store the type of temperature for the store-day. |
No |
Traffic |
Char(6) |
NA |
For transaction types of COND, this field will store the type of traffic for the store-day. |
No |
Construction |
Char(6) |
NA |
For transaction types of COND, this field will store information regarding any construction on that store-day. |
No |
Drop Shipment Indicator |
Char(1) |
Y or N |
Indicates whether the item is involved in a drop shipment. |
No |
Item Status |
Char(6) |
NA |
The status of the item, required for voided or exchanged items. Valid values are found in the code_detail table under code_type SASI. |
Y, -1 for null |
Tran Process Sys |
Char(3) |
NA |
This column holds the name of the system that processed the transaction. This will be used for filtering duplicate transactions coming from the different systems for export to downstream systems. Expected values are POS - Point of Sale, OMS - Order Management System and, SIM - Store Inventory Management. |
Y, -1 for null |
Return Wh |
Number(10) |
NA |
This column contains the physical warehouse ID for the warehouse identifier where the item was returned. |
N, -1 for null |
Fulfill Order No |
Char(48) |
NA |
This column holds the number from OMS related to the fulfillment details. One or more fulfillment orders could relate back to a single customer order in OMS. This column is required if the order is a cross channel order (that is, Sales Type equals E) and the item status is ORD. |
N, -1 for null |
No Inventory Return Ind |
Char(1) |
NA |
This column contains an indicator that identifies a return without inventory. This is generally a non-required column, but in case of returns, this is required. |
N |
Sales Type |
Char(1) |
NA |
This column indicates whether the line item is a Regular Sale, a customer order serviced by OMS (External CO), or a customer order serviced by a store (In Store CO). |
Y |
Return Disposition |
Char(10) |
NA |
This column will contain the disposition code published by RWMS as part of the Returns upload to OMS. |
N, -1 for null |
Original Store |
Char(10) |
NA |
This column contains the store ID for the original store. |
No |
Original Transaction Number |
Number(10) |
NA |
Original transaction number for the returned item. |
No |
Discount Type |
Char(6) |
NA |
Code for discount type from code_detail, code_type equals SADT. |
No |
Promotional Transaction Type |
Char(6) |
NA |
Code for promotional type from code_detail, code_type equals PRMT. |
Yes |
Promotion Number |
Numeric(10) |
Promotion number |
Promotion number Promotion number from the RMS. |
No |
Promotion Component Number |
Numeric(10) |
Offer ID from RPM |
NA |
Required if it is a promotional sale. |
Coupon Number |
Char(40) |
NA |
NA |
Yes if Discount Type is SCOUP. |
Coupon Reference Number |
Char(16) |
NA |
NA |
No |
Sales Quantity |
Number(12) |
NA |
Number of units sold in this promotion type, with 4 implied decimal places. |
No |
Transaction Sign |
Char(1) |
P - positive N - negative |
NA |
Yes |
Transaction Value |
Number(20) |
NA |
Value of units sold in this promotion type, with 4 implied decimal places. |
Yes |
Discount Value |
Number(20) |
NA |
Value of discount given in this promotion type, with 4 implied decimal places. |
Yes |
Table 29-83 RDWF File
Record Name | Field Name | Field Type | Default Value | Description | Required |
---|---|---|---|---|---|
File Header |
File Type Record Descriptor |
Char(5) |
FHEAD |
Identifies file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
File Type Definition |
Char(4) |
RDWF |
Identifies the file as a Retail Analytics Form of Payment (Tender) file. |
Yes |
|
File Create Date |
Numeric(14) |
Create date |
Date the file was written by external system. Format YYYYMMDDHH24MISS. |
Yes |
|
File Detail |
File Type Record Descriptor |
Char(5) |
FDETL |
Identifies file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
Business date |
Numeric(8) |
NA |
Format YYYYMMDD |
Yes |
|
Transaction Date |
Numeric(14) |
Transaction date |
Date sale/return transaction was processed at the POS. Format YYYYMMDDHH24MISS |
Yes |
|
Location |
Number(10) |
Specified by external system |
Store or warehouse identifier. |
Yes |
|
Cashier Identifier |
Char(10) |
NA |
The cashier number. This will be the unique employee number. |
Yes, -1 for null |
|
Register Identifier |
Char(5) |
NA |
NA |
Yes, -1 for null |
|
Sales Sign |
Char(1) |
P - positive N - negative |
Determines if the Total Sales Quantity and Total Sales Value are positive or negative. |
Yes |
|
Transaction Sequence Number |
Numeric(20) |
NA |
Unique reference used within sales audit to represent the date/store/register/transaction number. |
Yes |
|
Revision number |
Number(3) |
NA |
Number used to identify the version of the transaction being sent. |
Yes |
|
Transaction Type |
Char(6) |
NA |
Transaction type code. |
Yes |
|
Tender type group |
Char(6) |
NA |
NA |
Yes |
|
Tender type id |
Numeric(6) |
NA |
Tender type code. |
Yes |
|
Tender amount |
Number(20) |
NA |
Tender amount. |
Yes |
|
Credit Card Number |
Numeric(40) |
NA |
This value is masked. |
No |
|
Credit Card Expiration Date |
Numeric(8) |
NA |
Format YYYYMMDD |
No |
|
Credit Card Authorization Number |
Char(16) |
NA |
NA |
No |
|
Credit Card Authorization Source |
Char(6) |
NA |
Contains whether the authorization number was electronically transmitted or manually keyed in after obtaining it through a telephone call. The code type for this field is CCAS. |
No |
|
Credit Card Entry Mode |
Char(6) |
NA |
Contains the method in which the transaction was entered at the POS. Possible entry modes could include: Terminal Used, Magnetic Strip Track One Read, Magnetic Strip Two Read, Magnetic Strip One Transmitted, or Magnetic Strip Two Transmitted. The code type for this field is CCEM. |
No |
|
Credit Card Cardholder Verification |
Char(6) |
NA |
Contains the method of identification that was used by the cardholder to verify their identity. Possible values include Signature Verified (S), Card Shown (C), PIN Entered (P), Mail Order / Phone (M). The code type for this field is CCVF. |
No |
|
Credit Card Terminal ID |
Char(5) |
NA |
Contains the identification code of the terminal within the store that the transaction was transmitted. |
No |
|
Credit Card Special Conditions |
Char(6) |
NA |
Contains the special condition of the transaction (mail, phone or electronic-secured or non-secured authentication). The code type for this field is CCSC. |
No |
|
Voucher Number |
Char(25) |
NA |
NA |
No |
|
Voucher Age |
Numeric(5) |
NA |
Age of the gift certificate. Redeemed date minus sold date. |
Yes if Tender Type Group is VOUCH. |
|
Escheat Date |
Numeric(5) |
NA |
Date on which this gift certificate escheats. Format is YYYYMMDD. |
Yes if voucher can escheat. |
|
Coupon Number |
Char(40) |
NA |
NA |
Yes if Tender Type Group is COUPON. |
|
Coupon Reference Number |
Char(16) |
NA |
NA |
No. Only if Tender Type Group is COUPON. |
|
Transaction Status |
Char(1) |
Determines if the transaction is Present ('P') or Voided/Deleted ('R' - Reverse) |
No |
||
File Trailer |
File Type Record Descriptor |
Char(5) |
FTAIL |
Identifies file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
File Record Counter |
Number(10) |
NA |
Number of records/transaction processed in the current file (only records between head and tail). |
Yes |
Table 29-84 Form of Payment File
Field Name | Field Type | Default Value | Description | Required |
---|---|---|---|---|
Business date |
Numeric(8) |
NA |
Format YYYYMMDD |
Yes |
Transaction Date |
Numeric(14) |
Transaction date |
Date the sale/return transaction was processed at the POS. Format YYYYMMDDHH24MISS |
Yes |
Location |
Number(10) |
Specified by external system |
Store or warehouse identifier. |
Yes |
Cashier Identifier |
Char(10) |
NA |
The cashier number. This will be the unique employee number. |
Yes, -1 for null |
Register Identifier |
Char(5) |
NA |
NA |
Yes, -1 for null |
Sales Sign |
Char(1) |
P - positive N - negative |
Determines if the Total Sales Quantity and Total Sales Value are positive or negative. |
Yes |
Transaction Sequence Number |
Numeric(20) |
NA |
Unique reference used within sales audit to represent the date/store/register/transaction number. |
Yes |
Revision number |
Number(3) |
NA |
Number used to identify the version of the transaction being sent. |
Yes |
Transaction Type |
Char(6) |
NA |
Transaction type code. |
Yes |
Tender type group |
Char(6) |
NA |
NA |
Yes |
Tender type id |
Numeric(6) |
NA |
Tender type code. |
Yes |
Tender amount |
Number(20) |
NA |
Tender amount. |
Yes |
Credit Card Number |
Numeric(40) |
NA |
This value is masked. |
No |
Credit Card Expiration Date |
Numeric(8) |
NA |
Format YYYYMMDD |
No |
Credit Card Authorization Number |
Char(16) |
NA |
NA |
No |
Credit Card Authorization Source |
Char(6) |
NA |
Contains whether the authorization number was electronically transmitted or manually keyed in after obtaining it through a telephone call. The code type for this field is CCAS. |
No |
Credit Card Entry Mode |
Char(6) |
NA |
Contains the method in which the transaction was entered at the POS. Possible entry modes could include: Terminal Used, Magnetic Strip Track One Read, Magnetic Strip Two Read, Magnetic Strip One Transmitted, or Magnetic Strip Two Transmitted. The code type for this field is CCEM. |
No |
Credit Card Cardholder Verification |
Char(6) |
NA |
Contains the method of identification that was used by the cardholder to verify their identity. Possible values include Signature Verified (S), Card Shown (C), PIN Entered (P), Mail Order / Phone (M). The code type for this field is CCVF. |
No |
Credit Card Terminal ID |
Char(5) |
NA |
Contains the identification code of the terminal within the store where the transaction was transmitted. |
No |
Credit Card Special Conditions |
Char(6) |
NA |
Contains the special condition of the transaction (mail, phone or electronic-secured or non-secured authentication). The code type for this field is CCSC. |
No |
Voucher Number |
Char(25) |
NA |
NA |
No |
Voucher Age |
Numeric(5) |
NA |
Age of the gift certificate. Redeemed date minus sold date. |
Yes if Tender Type Group is VOUCH. |
Escheat Date |
Numeric(8) |
NA |
Date on which this gift certificate escheats. Format is YYYYMMDD. |
Yes if voucher can escheat. |
Coupon Number |
Char(40) |
NA |
NA |
Yes if Tender Type Group is COUPON. |
Coupon Reference Number |
Char(16) |
NA |
NA |
No. Only if Tender Type Group is COUPON. |
Transaction Status |
Char(1) |
NA |
Determines if the transaction is Present ('P') or Voided/Deleted ('R' - Reverse) |
No |
Table 29-85 RDWS File
Record Name | Field Name | Field Type | Default Value | Description | Required |
---|---|---|---|---|---|
File Header |
File Type Record Descripto |
Char(5) |
FHEAD |
Identifies file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
File Type Definition |
Char(4) |
RDWS |
Identifies file as a Retail Analytics Store Totals file. |
Yes |
|
File Create Date |
Char(4) |
Create date |
Date file was written by the external system. Format YYYYMMDDHH24MISS |
Yes |
|
File Detail |
File Type Record Descriptor |
Char(5) |
FDETL |
Identifies the transaction record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
Business date |
Number(8) |
NA |
Format YYYYMMDD |
Yes |
|
Location |
Number(10) |
Specified by external system |
Store or warehouse identifier. |
Yes |
|
Sales Sign |
Char(1) |
P - positive N - negative |
Determines if the Total Sales Quantity and Total Sales Value are positive or negative. |
Yes |
|
Total ID |
Char(10) |
NA |
Category identifier used to determine the type of total. |
Yes |
|
Reference Number 1 |
Char(30) |
NA |
NA |
No |
|
Reference Number 2 |
Char(30) |
NA |
NA |
No |
|
Reference Number 3 |
Char(30) |
NA |
NA |
No |
|
Total Sign |
Char(1) |
P - positive N - negative |
NA |
Yes |
|
Total Amount |
Number(20) |
NA |
Total over/short amount, with 4 implied decimal places. |
Yes |
|
File Trailer |
File Type Record Descriptor |
Char(5) |
FTAIL |
Identifies the file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
File Record Counter |
Number(10) |
NA |
Number of records/transactions processed in the current file (only records between head and tail). |
Yes |
Table 29-86 Store Totals Information
Field Name | Field Type | Default Value | Description | Required |
---|---|---|---|---|
Business date |
Number(8) |
NA |
Format YYYYMMDD |
Yes |
Location |
Number(10) |
Specified by external system |
Store or warehouse identifier. |
Yes |
Sales Sign |
Char(1) |
P - positive N - negative |
Determines if the Total Sales Quantity and Total Sales Value are positive or negative. |
Yes |
Total ID |
Char(10) |
NA |
Category identifier used to determine the type of total. |
Yes |
Reference Number 1 |
Char(30) |
NA |
NA |
No |
Reference Number 2 |
Char(30) |
NA |
NA |
No |
Reference Number 3 |
Char(30) |
NA |
NA |
No |
Total Sign |
Char(1) |
P - positive N - negative |
NA |
Yes |
Total Amount |
Number(20) |
NA |
Total over/short amount, with 4 implied decimal places. |
Yes |
Table 29-87 RDWC File
Record Name | Field Name | Field Type | Default Value | Description | Required |
---|---|---|---|---|---|
File Header |
File Type Record Descriptor |
Char(5) |
FHEAD |
Identifies file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
File Type Definition |
Char(4) |
RDWC |
Identifies the file as a Retail Analytics Cashier/Register Totals file. |
Yes |
|
File Create Date |
Numeric(14) |
Create date |
Date the file was written by the external system. Format YYYYMMDDHH24MISS |
Yes |
|
File Detail |
File Type Record Descriptor |
Char(5) |
FDETL |
Identifies the transaction record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
Business date |
Number(8) |
NA |
Format YYYYMMDD |
Yes |
|
Location |
Number(10) |
Specified by external system |
Store or warehouse identifie |
Yes |
|
Cashier Identifier |
Char(10) |
NA |
The cashier number. |
If Cashier_id is NULL, then Register_id has value. If Cashier_id has value, then Register_id is NULL. Yes, -1 for null |
|
Register ID |
Char(5) |
NA |
The register identifier. |
If Cashier_id is NULL, then Register_id has value. If Cashier_id has value, then Register_id is NULL. Yes, -1 for null |
|
Sales Sign |
Char(1) |
P - positive N - negative |
Determines if the Total Sales Quantity and Total Sales Value are positive or negative |
Yes |
|
Total ID |
Char(10) |
NA |
Category identifier used to determine the type of total. |
Yes |
|
Reference Number 1 |
Char(30) |
NA |
NA |
No |
|
Reference Number 2 |
Char(30) |
NA |
NA |
No |
|
Reference Number 3 |
Char(30) |
NA |
NA |
No |
|
Total Sign |
Char(1) |
P - positive N - negative |
NA |
Yes |
|
Total Amount |
Number(20) |
Total over/short amount, with 4 implied decimal places. |
Yes |
||
File Trailer |
File Type Record Descriptor |
Char(5) |
FTAIL |
Identifies the file record type. |
NA |
File Line Identifier |
Number(10) |
Specified by external system |
ID of the current line being processed by input file. |
Yes |
|
File Record Counter |
Number(10) |
NA |
Number of records/transactions processed in the current file (only records between head and tail). |
Yes |
Table 29-88 Cashier/Register Totals Information
Field Name | Field Type | Default Value | Description | Required |
---|---|---|---|---|
Business date |
Number(8) |
NA |
Format YYYYMMDD |
Yes |
Location |
Number(10) |
Specified by external system |
Store or warehouse identifier |
Yes |
Cashier Identifier |
Char(10) |
NA |
The cashier number |
If Cashier_id is NULL, then Register_id has value. If Cashier_id has value, then Register_id is NULL. Yes, -1 for null |
Register ID |
Char(5) |
NA |
The register identifier. |
If Cashier_id is NULL, then Register_id has value. If Cashier_id has value, then Register_id is NULL. Yes, -1 for null |
Sales Sign |
Char(1) |
P - positive N - negative |
Determines if the Total Sales Quantity and Total Sales Value are positive or negative. |
Yes |
Total ID |
Char(10) |
NA |
Category identifier used to determine the type of total. |
Yes |
Reference Number 1 |
Char(30) |
NA |
NA |
No |
Reference Number 2 |
Char(30) |
NA |
NA |
No |
Reference Number 3 |
Char(30) |
NA |
NA |
No |
Total Sign |
Char(1) |
P - positive N - negative |
NA |
Yes |
Total Amount |
Number(20) |
NA |
Total over/short amount, with 4 implied decimal places. |
Yes |
Module Name | Saexpsim.pc |
Description | Export of Revised Sale/Return Transactions from ReSA to SIM |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA14 |
The purpose of this batch module is to fetch all revised sale and return transactions that do not have SIM errors from the ReSA database tables for transmission to SIM. It retrieves all quantity revision transaction data for SALES, RETURN, EEXCH, VOID, and SPLORD transaction types.
If sa_system_options.unit_of_work is S, the whole store/day is skipped if any SIM error is found. If this value is T, then only transactions with SIM errors are skipped.
The batch will only export transactions whose quantity has been revised. The batch will write these revised transactions to the output file along with a reversal of the quantity.
A file of type SIMT is generated for each store/day.
Table 29-89 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Scheduling Considerations |
This program should run towards the end of the Sales Auditing cycle where the total (SATOTALS.PC) and rule (SARULES.PC) data are ready to be exported to the external systems. |
Pre-Processing |
Satotals, sarules, sapreexp |
Post-Processing |
saprepost saexpsim post, resa2sim |
Threading Scheme |
Multi-threaded by store |
The logical unit of work for this module is defined as a unique store/day combination. Records will be fetched, updated, and inserted in batches of pl_commit_max_ctr. Only two commits will be done, one to establish the store/day lock and another at the end, to release the lock after a store/day has been completely processed. The SIMT formatted output file will be created with a temporary name and renamed just before the end of store/day commit.
In case of failure, all work done will be rolled back to the point right after the call to get_lock() and the lock released. Thus, the rollback segment should be large enough to hold all inserts into SA_EXPORTED for one store/day.
Table 29-90 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_STORE_DAY |
Yes |
No |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
Yes |
No |
V_RESTART_STORE |
Yes |
No |
No |
No |
STORE |
Yes |
No |
No |
No |
SA_TRAN_HEAD |
Yes |
No |
No |
No |
SA_ERROR |
Yes |
No |
No |
No |
SA_ERROR_IMPACT |
Yes |
No |
No |
No |
SA_EXPORTED |
Yes |
Yes |
No |
No |
SA_TRAN_HEAD_REV |
Yes |
No |
No |
No |
SA_EXPORTED_REV |
Yes |
No |
No |
No |
SA_SYSTEM_OPTIONS |
Yes |
No |
No |
No |
SA_TRAN_ITEM_REV |
Yes |
No |
No |
No |
ITEM_MASTER |
Yes |
No |
No |
No |
SA_TRAN_ITEM |
Yes |
No |
No |
No |
SA_STORE_DAY_READ_LOCK |
No |
Yes |
No |
Yes |
Integration Type | Download from ReSA |
File Name | SIMT_ appended by store number, business date, and system date |
Integration Contract | IntCon000045 |
Table 29-91 Output File
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
Record descriptor |
Char(5) |
FHEAD |
Identifies the file record type. |
File Line Id |
Char(10) |
0000000001 |
Sequential file line number. |
|
File type definition |
Char(4) |
SIMT |
Identifies the file type. |
|
Store |
Number(10) |
NA |
Store location. |
|
Business Date |
Char(8) |
NA |
Business Date in YYYYMMDD format. |
|
File Create Date |
Char(14) |
NA |
File Create Date in YYYYMMDDHHMMSS format. |
|
THEAD |
Record descriptor |
Char(5) |
THEAD |
Identifies the file record type. |
File Line Id |
Char(10) |
NA |
Sequential file line number. |
|
Transaction Number |
Number(10) |
NA |
Transaction Identifier. |
|
Revision Number |
Number(3) |
NA |
Revision Number of the transaction. |
|
Transaction date |
Char(14) |
NA |
Transaction date in YYYYMMDDHHMMSS format. Corresponds to the date that the transaction occurred. |
|
Transaction Type |
Char(6) |
NA |
Transaction Type. |
|
POS Transaction Indicator |
Char(1) |
NA |
Indicates if the transaction was received from POS or manually created. Valid values: Y - POS N - Manual |
|
TDETL |
Record descriptor |
Char(5) |
TDETL |
Identifies the file record type. |
File Line Id |
Char(10) |
NA |
Sequential file line number. |
|
Item Sequence Number |
Number(4) |
NA |
Item sequence number. |
|
Item |
Char(25) |
NA |
Identifies the merchandise item. |
|
Item number type |
Char(6) |
NA |
Identifies the type of item number if the item type is ITEM or REF. |
|
Item Status |
Char(6) |
NA |
Status of the item within the transaction, V for item void, S for sold item, R for returned item. ORI - Order Initiate ORC - Order Cancel ORD - Order Complete LIN - Layaway Initiate LCA - Layaway Cancel LCO - Layaway Complete |
|
Serial Number |
Char(128) |
NA |
Unique ID. |
|
Pack Indicator |
Char(1) |
NA |
Pack Indicator. |
|
Catchweight Indicator |
Char(1) |
NA |
Catchweight Indicator. |
|
Quantity Sign |
Char(1) |
NA |
Sign of the quantity. |
|
Quantity Value |
Number(12) |
NA |
Number of items, with 4 implied decimal places. |
|
Standard Unit of Measure |
Char(4) |
NA |
Standard Unit of Measure of the item. |
|
Selling Unit of Measure |
Char(4) |
NA |
Unit of Measure of the quantity value. |
|
Waste Type |
Char(6) |
NA |
Waste Type. |
|
Waste Percent |
Number(12) |
NA |
Waste Percent. |
|
Drop Ship Indicator |
Char(1) |
NA |
Indicates whether the item is part of a drop shipment. |
|
Actual Weight |
Number(12) |
NA |
Contains the weight of the item sold, with 4 implied decimal places. |
|
Actual Weight Sign |
Char(1) |
NA |
Sign of the actual weight. |
|
Reason Code |
Char(6) |
NA |
Reason entered by the cashier for some transaction types. |
|
Sales Value |
Number(20) |
NA |
Transaction value, with 4 implied decimal places |
|
Sales Value Sign |
Char(1) |
NA |
Transaction value sign. |
|
Unit Retail |
Number(20) |
NA |
Unit retail, with 4 implied decimal places. |
|
Sales Type |
Char(1) |
NA |
Indicates if the transaction is an In Store Customer Order, External Customer Order, or Regular Sale. |
|
Customer Order Number |
Char(48) |
NA |
Contains the customer order ID. |
|
Customer Order Type |
Char(6) |
NA |
Customer order type. |
|
Fulfillment Order Number |
Char(48) |
NA |
Contains the order ID of the fulfillment order. |
|
TTAIL |
Record descriptor |
Char(5) |
TTAIL |
Identifies the file record type. |
File Line Id |
Char(10) |
NA |
Sequential file line number. |
|
Tran Record Counter |
Number(6) |
NA |
Number of TDETL records in this transaction set. |
|
FTAIL |
Record descriptor |
Char(5) |
FTAIL |
Identifies the file record type. |
File Line Id |
Number(10) |
NA |
Sequential file line number. |
|
File Record counter |
Number(10) |
NA |
Number of records/transactions processed in the current file (only records between head and tail). |
Module Name | saexpim.pc |
Description | Export DSD and Escheatment from ReSA to Invoice Matching |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA04 |
The purpose of this program is to support interfacing invoices from Direct Store Delivery and Escheatment sales audit transactions to the Oracle Retail Invoice Matching (ReIM) application. Direct Store Delivery invoices refer to products or services that are delivered to the store and paid for at the store. This program will take DSD invoices that have been staged to the SA_TRAN_HEAD table by the saimptlog.pc program and move them into the INVC_HEAD table. All DSD transactions will be assumed paid. They can be assumed received if there is a proof of delivery number listed on them. Transactions with a vendor invoice ID or a proof of delivery number should be matched to any existing invoice in INVC_HEAD, and that invoice updated with the new information being interfaced. Invoices that do not match an existing invoice in INVC_HEAD will need to be inserted. Each transaction will be exported to INVC_HEAD table only once.
The Sales Audit Transaction type used to identify invoices for Direct Store Delivery transactions will be Paid Out. The Paid Out transaction has a code of PAIDOU. The Sales Audit sub-transaction types will be used to identify whether the invoice is an Expense Vendor Payout or a Merchandise Vendor Payout. The codes are EV for Expense Vendor Payout and MV for Merchandise Vendor Payout. Any Paid Out transaction with a sub-transaction type of Expense Vendor will create a non-merchandise invoice and cause a record to be written to the INVC_NON_MERCH table. ReSA will store non-merchandise codes in the reason_code field on sa_tran_head. Valid values for these reason codes should correspond to the codes stored on the non_merch_code_head table.
In addition to DSD invoices, this program will also interface Escheatment totals to Invoice Matching. Escheatment is the process where an unredeemed gift certificate/voucher or credit voucher will, after a set period of time, be paid out as income to the issuing retailer, or in some states, the state receives this escheatment income. ReSA will be the governing system that determines who receives this income, but Invoice Matching will send the totals, with the related Partner, to an Accounts Payable system. Escheatment information will be stored on the ReSA SA_TOTALS table and will be used to create non-merchandise invoices in Invoice Matching. These invoices will be assumed not paid.
Table 29-92 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This module should be executed after the ReSA transaction import process after sapreexp. Ideally, after saescheat (if run before, some transactions will not be posted until the next run). |
Pre-Processing |
Sapreexp, saescheat |
Post-Processing |
NA |
Threading Scheme |
NA |
The logical unit of work for this module is defined as a unique store/day combination. Records will be fetched, updated, and inserted based on the commit_max_ctr specified on the restart_control table. Only two commits will be done, one to establish the store/day lock and another at the end, to release the lock after a store/day has been completely processed.In case of failure, all work done will be rolled back to the point right after the call to get_lock and releases the lock. Thus, the rollback segment should be large enough to hold all inserts into sa_exported for one store_day.
Table 29-93 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
SA_STORE_DAY |
Yes |
No |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
No |
No |
SA_TRAN_HEAD |
Yes |
No |
No |
No |
SA_TRAN_TENDER |
Yes |
No |
No |
No |
SA_EXPORTED |
Yes |
Yes |
No |
No |
INVC_HEAD |
Yes |
Yes |
Yes |
No |
INVC_NON_MERCH |
No |
Yes |
Yes |
No |
INVC_XREF |
No |
Yes |
No |
No |
TERMS |
Yes |
No |
No |
No |
SUPS |
Yes |
No |
No |
No |
PARTNER |
Yes |
No |
No |
No |
CURRENCY_RATES |
Yes |
No |
No |
No |
ADDR |
Yes |
No |
No |
No |
SA_ERROR |
Yes |
No |
No |
No |
SA_ERROR_IMPACT |
Yes |
No |
No |
No |
Module Name | saexpgl.pc |
Description | Post User Defined Totals from ReSA to General Ledger |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA09 |
The purpose of this module is to post all properly configured user-defined ReSA totals to a general ledger application (Oracle or PeopleSoft). Totals without errors will be posted to the appropriate accounting ledger, as defined in the Sales Audit GL cross-reference module. Depending on the unit of work system parameter, the data will be sent at either the store/day or individual total level. Newly revised totals, that have already been posted to the ledger, will have their previous revision reversed, and the new total posted to the appropriate accounts. Transactions that are from previous periods will be posted to the current period.
Table 29-94 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This program should run after the ReSA Totaling process (satotals.pc) and Audit Rules process (sarules.pc) and sapreexp.pc. |
Pre-Processing |
Satotals. Sarules, sapreexp |
Post-Processing |
NA |
Threading Scheme |
NA |
The logical unit of work for this module is defined as a unique store/day combination. Records will be fetched, updated, and inserted in batches the size of commit max counter. Only one commit will be done after a store/day has been completely processed. A call to release_lock() performs a commit.
Table 29-95 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
Yes |
No |
STORE |
Yes |
No |
No |
No |
SA_FIF_GL_CROSS_REF |
Yes |
No |
No |
No |
STG_FIF_GL_DATA |
No |
Yes |
No |
No |
IF_ERRORS |
No |
Yes |
No |
No |
SA_EXPORTED |
No |
Yes |
Yes |
No |
MV_LOC_SOB |
Yes |
No |
No |
No |
KEY_MAP_GL |
No |
Yes |
No |
No |
SA_GL_REF_DATA |
No |
Yes |
No |
No |
SYSTEM_VARIABLES |
Yes |
No |
No |
No |
Module Name | ang_saplgen.pc |
Description | Extract of POS Transactions by Store/Date from ReSA for Web Search |
Functional Area | Oracle Retail Sales Audit (ReSA) |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RMS162 |
The purpose of this batch module is to fetch all corrected sale and return transactions that do not have RMS errors from the ReSA database tables for transmission to an external web search engine. If the transaction has a status of Deleted or Post Voided and has previously been transmitted, a reversal of the transaction will be sent. A file of type POSLOG is generated for each store/day.
Table 29-96 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This program should run towards the end of the Sales Auditing cycle and before SAEXPRMS.PC. |
Pre-Processing |
NA |
Post-Processing |
NA |
Threading Scheme |
Multi-threaded by store |
The logical unit of work for this module is defined as a unique store/day combination. Records will be fetched, in batches of pl_commit_max_ctr. The POSLOG formatted output file will be created with a completion of store/day looping.
Table 29-97 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_STORE_DAY |
Yes |
No |
No |
No |
SA_TRAN_HEAD |
Yes |
No |
No |
No |
SA_TRAN_ITEM |
Yes |
No |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
No |
No |
SA_EXPORTED |
Yes |
No |
No |
No |
SA_ERROR |
Yes |
No |
No |
No |
SA_ERROR_IMPACT |
Yes |
No |
No |
No |
STORE |
Yes |
No |
No |
No |
ITEM_MASTER |
Yes |
No |
No |
No |
ITEM_LOC |
Yes |
No |
No |
No |
DEPS |
Yes |
No |
No |
No |
CODE_DETAIL |
Yes |
No |
No |
No |
RPM_ITEM_ZONE_PRICE |
Yes |
No |
No |
No |
RPM_ZONE_LOCATION |
Yes |
No |
No |
No |
Integration Type | Download from ReSA |
File Name | POSLOG_<store>_<business date>_<system date>.xml |
Integration Contract | IntCon000018 |
Table 29-98 Output File Layout
Field Name | Field Type | Description |
---|---|---|
BatchID |
CHAR(18) |
A concatenation of store number and business date for a store. |
RetailStoreID |
CHAR(10) |
The store number for which the POSLog file has to be extracted. |
WorkStationID |
CHAR(5) |
RegistryID for the store. |
TillID |
CHAR(5) |
RegistryID for the store. |
SequenceNumber |
CHAR(10) |
Point of Sale system defined transaction number associated with a transaction. |
BeginDate |
CHAR(8) |
Starting date time of the transaction. |
EndDate |
CHAR(8) |
End date time of the transaction. |
CurrencyCode |
CHAR(3) |
Code of the currency used during the transaction. |
VoidFlag |
CHAR(5) |
Indicates if the item in the transaction is voided or not. Valid values are TRUE and FALSE. |
Item_Status |
CHAR(40) |
Status of the item is required for voided, exchanged, or returned item. |
MerchandisingHierarchy |
CHAR(4) |
Department number to which the item belongs |
Description |
CHAR(250) |
Item description that has been sold. |
Item |
CHAR(25) |
Item number. |
TaxIncludedInPrice |
CHAR(5) |
Indicates if the item is being taxed or not. Valid values are TRUE and FALSE. |
RegularSalesUnitPrice |
CHAR(20) |
Field holds the unit retail in the standard unit of retail for the item/location combination. |
ActualSalesUnitPrice |
CHAR(20) |
Retail price for the item. |
ExtendedAmount |
CHAR(20) |
Total sales for the item in the detail level. |
Qty |
CHAR(21) |
Unit sold of the item. |
Module Name | saescheat.pc |
Description | Download of Escheated Vouchers from ReSA for Payment |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA05 |
The laws of individual states and countries may require a retailer to return monies for aged, unclaimed gift certificates, and vouchers. This process is called escheatment. This program writes records for this data to tables that are read into Oracle Retail Invoice matching (ReIM) by the program saexpim.pc. The data can then be sent as invoices approved for payment to a financial application.
The saescheat batch program will set the status of vouchers that have met certain state's escheats rules or have expired to the proper status and produce a total for later export to Invoice Matching. The rules for escheatment are defined on the sa_escheatment_options table. This program calls the function nextEscheatSeqNo () in saescheat_nextesn batch program, which will select a block of available sequence numbers.
Table 29-99 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Monthly |
Scheduling Considerations |
Should run after ReSA Totaling and Auditing process (satotals.pc and sarules.pc) and before the export to Invoice Matching (saexpim.pc) and Sales Audit purge (sapurge.pc). |
Pre-Processing |
NA |
Post-Processing |
NA |
Threading Scheme |
NA |
The logical unit of work is a store/day. The program commits when the number of store/day records processed has reached the commit_max_ctr.
Table 29-100 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_STORE_DAY |
Yes |
No |
No |
No |
SA_VOUCHER |
Yes |
No |
Yes |
No |
STORE |
Yes |
No |
No |
No |
ADDR |
Yes |
No |
No |
No |
SA_VOUCHER_OPTIONS |
Yes |
No |
No |
No |
SA_ESCHEAT_VOUCHER |
No |
Yes |
No |
No |
SA_ESCHEAT_TOTAL |
No |
Yes |
No |
No |
SA_ESCHEAT_OPTIONS |
Yes |
No |
No |
No |
COMPHEAD |
Yes |
No |
No |
No |
Module Name | saescheat_nextesn.pc |
Description | Generate Next Sequence for Escheatment Processing |
Functional Area | Oracle Retail Sales Audit |
Module Type | Admin |
Module Technology | ProC |
Integration Catalog ID | RSA25 |
This batch program gets the next free sequence for use in the saescheat.pc process. This routine goes and gets a block of numbers when starting, and parcels them out as needed. Once they are all used up, it gets another block and returns a pointer to the string containing the next available number or NULL if an error occurs.
Module Name | saexpash.pc |
Description | Download from ReSA to Account Clearing House (ACH) System |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA03 |
This module will post store/day deposit totals to the SA_STORE_ACH table and bank deposit totals for a given day in a file formatted for export to an ACH (Account Clearing House). The ACH export deviations from the typical Sales Audit export in that store/days must be exported even though errors may have occurred for a given day or store (depending on the unit of work defined), and also, the store/day does not need to be closed for the export to occur. The nature of the ACH process is such that as much money as possible must be sent as soon as possible to the consolidating bank. Any adjustments to the amount sent can be made using the sabnkach screen in the online system.
Deposits for store/days that have not been Fully (F) loaded will not be transferred to the consolidating bank. After they are fully loaded, their deposits will be picked up by the next run of this program.
Table 29-103 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This module should be run towards the end of the Sales Auditing cycle where the total (SATOTALS.PC) and rule (SARULES.PC) data are ready to be exported to the external systems. |
Pre-Processing |
SAPREEXP.PC (preprocessing of sales auditing export modules that require totals), SATOTALS.PC and SARULES.PC |
Post-Processing |
NA |
Threading Scheme |
NA |
This module is in two distinct parts, with two different logical units of work. Thus, restart/recovery has to be implemented so that the first part does not get reprocessed in case the program is being restarted. Details on the implementation follow.The first driving cursor in this module retrieves a store/day to generate ACH totals. Once the first cursor is complete, the second retrieves bank locations by account numbers.The first Logical Unit of Work (LUW) is defined as a unique store/day combination. Records will be fetched, using the first driving cursor, in batches of commit_max_ctr, but processed and committed one store/day at a time.The first driving cursor will fetch all store/days that have been Fully Loaded (F), whose audit status is Audited (A), HQ Errors Pending (H), or Store Errors Pending (S) and that are ready to be exported to ACH. Before processing starts, a write lock is obtained using get_lock (). This driving cursor only fetches store/days with a sa_export_log.status of SAES_R. After a store/day is processed, sa_export_log.status is set to SAES_P so that this store/day will not be selected again if the program is restarted. The commit is performed using retek_force_commit after each store/day has been processed and sa_export_log updated, so as to release the lock.In case a store/day could not be processed due to locking, the store/day information is placed on a list (called locked store/day list) and the next store/day is processed. This list is kept in memory and is available only during processing. If the store for a store/day obtained from the first driving cursor, is on the locked store/day list, then this store/day cannot be processed. This is the case because there is a data dependency such that data from a particular store/day is dependent on data for the same store but at an earlier date. Thus, if a store/day cannot be processed, then subsequent store/days for the same store cannot be processed either. After the driving cursor returns no more data, the program attempts to process each store/day on the list two more times. If the store/day is still locked, then it is skipped entirely and a message is printed to the error log.The second LUW is a bank account number. Again, records will be fetched in batches of commit_max_ctr. The second driving cursor cannot retrieve information by the LUW because it is possible for the store's currency to be different from the local bank's currency. In that case, a currency conversion is needed.For each store/day, the query should retrieve the required ACH transfer. The latter is determined by adding the estimated deposit for the next day, the adjustment to the estimate for the current day, and any manual adjustment to the estimate.Since a store can be associated with different accounts at different banks, only accounts that are consolidated should be retrieved. Since it is possible for the local bank to be in a different country than the consolidating bank, the currency of the partner should also be fetched.Since processing is dependent on the type of account at the RDFI, the account type should be fetched by this cursor.Due to differences in transaction processing in cases when the bank is outside the United States, the partner's country should also be fetched. The results of the query should be sorted by partner country. The results of the query should also be ordered by accounts.
The fact that this program automates the transfer of funds on behalf of the user makes it a likely target for electronic theft. It must be made clear that the responsibility of electronic protection lies with the users themselves.
Following are some tips and recommendation to users:
A specific user should be used to run the program. This user would be the only one (or one of a few) who has access to this program.
The umask for this user should be set up so as to prevent other users from reading/writing its files. This would ensure that when the output file is created, it would not be accessible to other users.
The appropriate permissions should be set up on the directory, which holds the ACH files. The most restrictive decision would be to not allow any other user to view the contents of the directory.
A secure means of communication should be implemented for transferring the file from where it has been created to the ACH network. This may be done through encryption, or by copying the file to a disk and trusting the courier to deliver the files intact.
The ACH network needs to be secure.
Table 29-104 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_ACH_INFO |
Yes |
No |
No |
No |
COMPHEAD |
Yes |
No |
No |
No |
SA_STORE_DAY |
Yes |
No |
No |
No |
COMPANY_CLOSED |
Yes |
No |
No |
No |
COMPANY_CLOSED_EXCEP |
Yes |
No |
No |
No |
LOCATION_CLOSED |
Yes |
No |
No |
No |
SA_STORE_ACH |
Yes |
Yes |
Yes |
No |
SA_BANK_STORE |
Yes |
No |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
Yes |
No |
STORE |
Yes |
No |
No |
No |
PARTNER |
Yes |
No |
No |
No |
CURRENCY_RATES |
Yes |
No |
No |
No |
SA_BANK_ACH |
Yes |
Yes |
Yes |
No |
Integration Type | Download from ReSA |
File Name | ACH_ appended with the consolidating routing number, consolidating account number, and current system date. |
Integration Contract | IntCon000040 |
Table 29-105 Output File
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
ACH File Header |
Section No. |
Number(3) |
101 |
Constant number. |
Console Route No |
Number(10) |
NA |
The routing number of the consolidating bank. |
|
Sender ID |
Char(10) |
NA |
ID used by the Originator to identify itself. |
|
Current Date |
Char(6) |
NA |
Vdate in YYMMDD format. |
|
Day Time |
Char(4) |
NA |
Time of file creation in HH24MM format. |
|
File Header No. |
Number(7) |
0094101 |
Constant number. |
|
Console Bank Name |
Char(23) |
NA |
Name of the Originating Financial Depository Institution. |
|
Company Name |
Char(23) |
NA |
The name of the company name. |
|
Ref Code |
Char (8) |
NA |
Reference code. |
|
ACH CCD Batch Header |
Section No. |
Number(4) |
5225 |
Constant number. |
Company Name |
Char(16) |
NA |
The name of the company. |
|
Comp Disc Data |
Char(20) |
NULL |
Any kind of data specific to the company. |
|
Comp Id |
Char(10) |
NA |
Alphanumeric code to identify the company. |
|
CCD Header Id |
Char(3) |
CCD |
Constant value. |
|
Comp Entry Desc |
Char(10) |
CONSOL |
A short description from the Originator about the purpose of the entry. |
|
Tomorrow |
Char(6) |
NA |
Vdate+1 in YYMMDD format. |
|
Tomorrow |
Char(6) |
NA |
Vdate+1 in YYMMDD format. |
|
Settle Date |
Char(3) |
NULL |
This is inserted by receiving the ACH Operator. |
|
Reserved |
Number(1) |
1 |
Constant number. |
|
Odfi Id |
Number(8) |
8-digit routing number of the ODFI. |
||
Batch No |
Number(7) |
Batch number. |
||
ACH CBR Batch Header |
Section No. |
Number(4) |
5225 |
Constant number. |
Company Name |
Char(16) |
NA |
The name of the company. |
|
Reserved |
Char(3) |
FV1 |
Constant value. |
|
Exch Rate |
Number(15) |
Exchange rate for the specified currency. |
||
Reserved |
Char(2) |
US |
Constant value. |
|
Comp Id |
Char(10) |
Alphanumeric code to identify the company |
||
CBR Header Id |
Char(3) |
CBR |
Constant value. |
|
Comp Entry Desc |
Char(10) |
"CONSOL " |
A short description from the Originator about the purpose of the entry. |
|
Partner Curr Code |
Char(3) |
NA |
Code identifying the currency the partner uses for business transactions. |
|
Reserved |
Char(3) |
USD |
Constant value. |
|
Tomorrow |
Char(6) |
NA |
Vdate+1 in YYMMDD forma. |
|
Settle Date |
Char(3) |
NULL |
This is inserted by the receiving ACH Operator. |
|
Reserved |
Number(1) |
1 |
Constant number. |
|
Odfi Id |
Number(8) |
NA |
8-digit routing number of the ODFI. |
|
Batch No |
Number(7) |
NA |
Batch number. |
|
ACH CCD Entry |
Section No. |
Number(1) |
6 |
Constant number. |
Trans Code |
Char(2) |
Code used to identify the type of debit and credit. Value accepted are 27 and 37. |
||
Routing No |
Number(9) |
Routing number for the bank account. |
||
Acct No |
Char(17) |
Account number of the bank. |
||
Deposit |
Number(10) |
The amount involved in the transaction* 10000 (4 implied decimal places). |
||
Id |
Char(15) |
Null |
Identification number. Optional field containing a number used by the Originator to insert its own number for tracing purposes. |
|
Store Name |
Char(22) |
Name of the local store. |
||
Disc Data |
Char(2) |
Null |
Discretionary data. Any kind of data specific to the transaction. |
|
Reserved |
Number(1) |
0 |
Constant number. |
|
Trace No |
Number(15) |
Used to uniquely identify each entry within a batch. The first 8 digits contain the routing number of the ODFI and the other 7 contains a sequence number. |
||
ACH CBR Entry |
Section No. |
Number(1) |
6 |
Constant number. |
Trans Code |
Char(2) |
NA |
Code used to identify the type of debit and credit. Values accepted are 27 and 37. |
|
Routing No |
Number(9) |
NA |
Routing number for the bank account. |
|
Acct No |
Char(17) |
NA |
Account number of the bank |
|
Deposit |
Number(10) |
NA |
The amount involved in the transaction* 10000 (4 implied decimal places). |
|
Id |
Char(15) |
NULL |
Identification number. Optional field containing a number used by the Originator to insert its own number for tracing purposes. |
|
Store Name |
Char(22) |
NA |
Name of the local store. |
|
Disc Data |
Char(2) |
NULL |
Discretionary data. Any kind of data specific to the transaction. |
|
Reserved |
Number(1) |
1 |
Constant number. |
|
Trace No |
Number(15) |
NA |
Used to uniquely identify each entry within a batch. The first 8 digits contain the routing number of the ODFI and the other 7 contains a sequence number. |
|
ACH CBR Addendum |
Section No. |
Number(3) |
701 |
Constant number. |
Payment Info |
Char(80) |
Null |
Payment related information. |
|
Reserved |
Number(4) |
0001 |
Constant number |
|
Trace Seq No |
Number(7) |
NA |
Sequence number part of the Trace Number of the entry record to which this addendum is referring. |
|
ACH Batch Control |
Section No. |
Number(4) |
8225 |
Constant number. |
Batch Line Count |
Number(6) |
NA |
The number of entries and addenda in the batch. |
|
Hash Count |
Number(10) |
NA |
Sum of the RDFI IDs in the detail records. |
|
Total Batch Debit |
Number(12) |
NA |
Contains the accumulated debit and debit for the file * 10000 (4 implied decimal places). |
|
Total Batch Credit |
Number(12) |
NA |
Contains the accumulated credit and credit for the file * 10000 (4 implied decimal places). |
|
Comp Id |
Char(10) |
NA |
An alphanumeric code identifying the company. |
|
Auth |
Char(19) |
Null |
Message Authentication Code. The first 8 characters represent a code from the Data Encryption Standard (DES) algorithm. The remaining eleven characters are blanks. |
|
Reserved |
Char(6) |
Null |
Reserved. |
|
ODFI Id |
Number(8) |
NA |
8-digit routing number of the ODFI. |
|
Batch No |
Number(7) |
NA |
Batch number. |
|
ACH File Control |
Section No. |
Number(1) |
9 |
Constant number. |
Batch count |
Number(6) |
NA |
The number of batches sent in the file. |
|
Block count |
Number(6) |
NA |
The number of physical blocks in the file, including both File Header and File Control Records. This is the ceiling of the number of records divided by the blocking factor, which is 10. |
|
Entry count |
Number(8) |
NA |
The number of entries and addenda in the file. |
|
Total hash count |
Number(10) |
NA |
Sum of the Entry Hash fields on the Batch Control Records. |
|
Total file debit |
Number(12) |
NA |
Contains the accumulated debit and debit for the file * 10000 (4 implied decimal places). |
|
Total file credit, |
Number(12) |
NA |
Contains the accumulated credit and credit for the file * 10000 (4 implied decimal places). |
|
Reserved |
Char(39) |
NULL |
Reserved. |
|
ACH Completed Block |
End string |
Char(94) |
NA |
Mark the end of the file: a string of 94 '9' characters. The number of end lines with a string of 94 '9' characters is identified by the following equation: 10 - mod (number of lines in the file, 10). |
Module Name | saexpuar.pc |
Description | Export to Universal Account Reconciliation System from ReSA |
Functional Area | Oracle Retail Sales Audit |
Module Type | Integration |
Module Technology | ProC |
Integration Catalog ID | RSA06 |
The SAEXPUAR program is used to select the lottery, bank deposit, money order, and credit card totals and write them to output files for export to an external account clearing house application. For each store day, saexpuar posts specified totals to their appropriate output files.
The logical unit of work for this module is defined as a unique store/day combination. Records will be fetched, updated, and inserted in batches of commit_max_ctr. Only two commits will be done. One to establish the store/day lock (this will be done by the package) and the other is done at the end, after a store/day has been completely processed.
Table 29-107 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_STORE_DAY |
Yes |
No |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
Yes |
No |
SA_EXPORTED |
No |
Yes |
Yes |
No |
SA_EXPORTED_REV |
Yes |
No |
No |
No |
SA_TOTAL |
Yes |
No |
No |
No |
SA_TOTAL_HEAD |
Yes |
No |
No |
No |
SA_HQ_VALUE |
Yes |
No |
No |
No |
SA_STORE_VALUE |
Yes |
No |
No |
No |
SA_SYS_VALUE |
Yes |
No |
No |
No |
SA_POS_VALUE |
Yes |
No |
No |
No |
SA_TOTAL_USAGE |
Yes |
No |
No |
No |
SA_STORE_DAY_WRITE_LOCK |
Yes |
No |
No |
No |
SA_STORE_DAY_READ_LOCK |
Yes |
Yes |
No |
Yes |
Integration Type | Download from ReSA |
File Name | UAR usage type appended with system date. |
Integration Contract | IntCon000046 |
The output file will contain one line for each store/day detail record in a comma-delimited format. The fields are surrounded by double quotes. For example, a record for store 1000 on May 20, 2001 with an amount of 19.99 will look something like this:
"1", "1000", "1999", "20010520","2","","1","","","","","","","","","MN","RET"
Table 29-108 Output File
Field Name | Field Type | Description |
---|---|---|
Detail Flag |
Char |
”1” for detail record. |
Store |
Number |
Store number. |
Amount |
Number |
Total Value * 100 (with 2 implied decimal places). |
TranDate |
Char |
Transaction Date in YYYYMMDD format. |
UAR TranCode |
Char |
Transaction Code. ”1” for negative amount, ”2” for positive amount. |
User Defined Value 1 |
Char |
Ref Number 1 on SA_TOTAL. |
User Defined Value 2 |
Char |
Total Seq Number on SA_TOTAL. |
User Defined Value 3 |
Char |
Ref Number 2 on SA_TOTAL. |
User Defined Value 4 |
Char |
Ref Number 3 on SA_TOTAL. |
User Defined Value 5 |
Char |
Not used. |
User Defined Value 6 |
Char |
Not used. |
User Defined Value 7 |
Char |
Not used. |
User Defined Value 8 |
Char |
Not used. |
User Defined Value 9 |
Char |
Not used. |
User Defined Value 10 |
Char |
Not used. |
State |
Char |
State. |
Account |
Char |
Total Identification on SA_TOTAL. |
Module Name | saprepost.pc |
Description | Pre/Post Helper Processes for ReSA Batch Programs |
Functional Area | Oracle Retail Sales Audit |
Module Type | Admin |
Module Technology | ProC |
Integration Catalog ID | RSA26 |
The Sales Audit pre/post module facilitates multi-threading by allowing general system administration functions (such as table deletions or mass updates) to be completed after all threads of a particular Sales Audit program have been processed.
This program will take three parameters: username/password to log in to Oracle, a program before or after which this script must run, and an indicator of whether the script is a pre or post function. It will act as a shell script for running all pre-program and post-program updates and purges.
saprepost contains the following helper functions, which are should be individually scheduled with the related main programs.
Table 29-109 Helper Functions
Catalog ID | Saprepost Job | Related Main Program |
---|---|---|
NA |
saprepost saimptlog saimptlogi pre saprepost saimptlog saimptlogi post saprepost sapurge pre saprepost sapurge post |
NA |
RSA27 |
saprepost saexprms post |
saexprms |
RSA28 |
saprepost saexpdw post |
saexpdw |
RSA29 |
saprepost saordinvexp post |
saordinvexp |
RSA30 |
saprepost saexpsfm post |
NA |
RSA31 |
saprepost saexpsim post |
saexpsim |
RSA32 |
saprepost (saimptlog) (saimptlogi) pre |
Either saimptlog or saimptlogi, depending on which is being run. See the detail design for information about how the two related jobs differ |
RSA33 |
saprepost saimptlog saimptlogi post |
Either saimptlog or saimptlogi, depending on which is being run. See the detail design for information about how the two related jobs differ. |
RSA34 |
saprepost sapurge pre |
Sapurge.pc |
RSA35 |
saprepost sapurge post |
Sapurge.pc |
Table 29-111 Key Tables Affected
Table | Select | Insert | Update | Index | Delete | Truncate | Trigger |
---|---|---|---|---|---|---|---|
SA_EXPORT_LOG |
Yes |
Yes |
No |
No |
No |
No |
No |
SA_STORE_DAY |
Yes |
No |
No |
No |
No |
No |
No |
SA_TRAN_SEQ_TEMP |
No |
No |
No |
No |
No |
Yes |
No |
ALL_OBJECTS |
Yes |
No |
No |
No |
No |
No |
No |
ALL_SYNONYMS |
Yes |
No |
No |
No |
No |
No |
No |
ALL_CONSTRAINTS |
Yes |
No |
No |
No |
No |
No |
No |
DBA_OBJECTS |
Yes |
No |
No |
No |
No |
No |
No |
SA_EXPORTED |
No |
No |
No |
Yes |
No |
No |
No |
RESTART_PROGRAM_STATUS |
Yes |
No |
Yes |
No |
No |
No |
No |
Module Name | sapurge.pc |
Description | Purge Aged Store/Day Transaction, Total Value and Error Data from ReSA |
Functional Area | Oracle Retail Sales Audit |
Module Type | Admin |
Module Technology | ProC |
Integration Catalog ID | RSA21 |
This program will be run daily to control the size of the tables in the sales audit database. Older information will be deleted to ensure optimal performance of the system as a whole.
Different kinds of data need to be kept in the system for different amounts of time. Transactions, all associated transaction details, and Totals calculated or reported for a store day will be deleted when they meet the following criteria:
The Business Date for those transactions and totals is older than or equal to today's date minus the days_before_purge parameter set up on the sales audit system parameters.
No locks exist on the store/day.
One of the two following statements is true for the store/day:
Fully loaded, and all errors either corrected or overridden (sa_store_day.audit_status is A (Audited) and sa_store_day.data_status equals F (Fully loaded)). In addition, there are no outstanding exports (records for the store/day in the sa_export_log table where sa_export_log.status equals R (Ready for export)).
Never loaded (sa_store_day.audit_status is U (Unaudited) and sa_store_day.data_status equals R (Ready for import)).
Flash Sales data will be deleted when it meets the following criteria:
Date is two years before today's date minus the days_before_purge parameter set up on the sales audit system parameters.
Company open and close dates will also need to be kept for two years plus days_before_purge, so that the historical comparisons in flash sales reporting carry the appropriate weight.
Voucher data will be deleted when it meets the following criteria:
The redeemed date or the escheat date for the specific voucher type is before today's date minus the purge_no_days on sales audit voucher options table for the corresponding voucher type.
The program can also take in a list of store_day_seq_no to delete. For example, the command line could be: sapurge userid/passwd 1000 1001 1002, where 1000, 1001 and 1003 are store_day_seq_nos that the user wants to delete. These must also meet the criteria defined above. If a store_day_seq_no is passed to this program, but does not meet the criteria, an error will be written out to the error log.
An output file will be created to store a record for each store and business date that was purged. The file name must be passed in at the command line as a parameter to sapurge.
Table 29-112 Scheduling Constraints
Schedule Information | Description |
---|---|
Processing Cycle |
Ad Hoc |
Frequency |
Daily |
Scheduling Considerations |
This program should be run as the last program in the ReSA batch flow. It can be run as part of the daily or monthly ReSA schedules. |
Pre-Processing |
saprepost sapurge pre |
Post-Processing |
saprepost sapurge post |
Threading Scheme |
Threaded by store |
Restart/recovery is implicit in purge programs. The program only needs to be run again to restart appropriately.
Table 29-113 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
SA_STORE_DAY |
Yes |
No |
Yes |
Yes |
SA_SYSTEM_OPTIONS |
Yes |
No |
No |
No |
SA_EXPORT_LOG |
Yes |
No |
No |
Yes |
SA_TRAN_ITEM_REV |
Yes |
No |
No |
Yes |
SA_TRAN_HEAD |
Yes |
No |
No |
Yes |
SA_TRAN_HEAD_TEMP |
Yes |
Yes |
No |
Yes |
SA_TOTAL |
Yes |
No |
No |
Yes |
SA_BALANCE_GROUP |
Yes |
No |
No |
Yes |
SA_ESCHEAT_TOTAL |
Yes |
No |
No |
Yes |
SA_VOUCHER_OPTIONS |
Yes |
No |
No |
No |
SA_EXPORTED |
No |
No |
No |
Yes |
SA_EXPORTED_REV |
No |
No |
No |
Yes |
SA_ERROR_REV |
No |
No |
No |
Yes |
SA_TRAN_TAX_REV |
No |
No |
No |
Yes |
SA_TRAN_DISC_REV |
No |
No |
No |
Yes |
SA_TRAN_TENDER_REV |
No |
No |
No |
Yes |
SA_TRAN_TAX |
No |
No |
No |
Yes |
SA_TRAN_DISC |
No |
No |
No |
Yes |
SA_TRAN_ITEM |
No |
No |
No |
Yes |
SA_TRAN_TENDER |
No |
No |
No |
Yes |
SA_CUST_ATTRIB |
No |
No |
No |
Yes |
SA_CUSTOMER |
No |
No |
No |
Yes |
SA_COMMENTS |
No |
No |
No |
Yes |
SA_ERROR |
No |
No |
No |
Yes |
SA_POS_VALUE |
No |
No |
No |
Yes |
SA_POS_VALUE_WKSHT |
No |
No |
No |
Yes |
SA_SYS_VALUE |
No |
No |
No |
Yes |
SA_SYS_VALUE_WKSHT |
No |
No |
No |
Yes |
SA_STORE_VALUE |
No |
No |
No |
Yes |
SA_HQ_VALUE |
No |
No |
No |
Yes |
SA_ERROR_WKSHT |
No |
No |
No |
Yes |
SA_MISSING_TRAN |
No |
No |
No |
Yes |
SA_IMPORT_LOG |
No |
No |
No |
Yes |
SA_BANK_ACH |
No |
No |
No |
Yes |
SA_ESCHEAT_VOUCHER |
No |
No |
No |
Yes |
SA_FLASH_SALES |
No |
No |
No |
Yes |
SA_VOUCHER |
No |
No |
No |
Yes |
SA_STORE_ACH |
No |
No |
No |
Yes |
KEY_MAP_GL |
No |
No |
No |
Yes |
SA_GL_REF_DATA |
No |
No |
No |
Yes |
SA_TRAN_PAYMENT_REV |
No |
No |
No |
Yes |
SA_TRAN_IGTAX_REV |
No |
No |
No |
Yes |
SA_TRAN_ITEM_TEMP |
Yes |
Yes |
No |
Yes |
SA_TRAN_IGTAX |
No |
No |
No |
Yes |
SA_TRAN_PAYMENT |
No |
No |
No |
Yes |