Oracle Retail Sales Audit Batch Process and Designs

Oracle Retail Sales Audit Dataflow Diagram

The following diagram illustrates how data flows within ReSA and between ReSA and other applications.

Note:

All integrations are not depicted in this diagram.

Figure 29-1 Oracle Retail Sales Audit Dataflow Diagram

Oracle Retail Sales Import Process

Importing data from the POS to ReSA is a multi-step process that involves several ReSA batch processes.

Figure 29-2 Oracle Retail Sales Import Process

sastdycr.pc prepares the ReSA tables for data upload.
Reference File Creation involves two processes:
1. 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.
2. 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:
1. saimptlogi.c validates files and uploads their transactions into the ReSA tables. This includes (as necessary) creating errors for the auditors to address
2. 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.
3. 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.

POS File Validation/Upload Sub-Process saimiptlog vs saimptlogi

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.

Total Calculations and Rules

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.

Totals when Transactions are Modified

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.

Oracle Retail Sales Export Process

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.

Full Disclosure and Post Export Changes

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.

Batch Design Summary of ReSA Modules

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.

Import Process Programs

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)

Totals/Rules Programs

satotals.pc (Calculate Totals based on Client Defined Rules
sarules.pc (Evaluate Transactions and Totals based on Client Defined Rules)

Export Programs

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)

Other Programs

saprepost.pc (Pre/Post Helper Processes for ReSA Batch Programs)
sapurge.pc (Purge Aged Store/Day Transaction, Total Value, and Error Data from ReSA

sastdycr (Create Store Day for Expected Transactions)

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

Design Overview

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

Scheduling Constraints

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

Restart/Recovery

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.

Key Tables Affected

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

Integration Contract

Integration Type	NA
File Name	NA
Integration Contract	NA

Design Assumptions

NA

sagetref (Get Reference Data for Sales Audit Import Processing)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

NA

Key Tables Affected

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 Contract

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)

File Name: Item File

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

File Name: Waste Data File

The Waste Data File file name (wastefile) is not fixed; it is determined by a runtime parameter.

Table 29-6 wastefile - File Layout

Field Name	Field Type	Default Value	Description
Item	Char(25)	NA	Item number
Waste type	Char(6)	NA	Waste type
Waste pct	Number(12,4)	NA	Waste pct

File Name: Reference Item Data

The Reference Item Data file name (ref_itemfile) is not fixed; it is determined by a runtime parameter.

Table 29-7 Ref_itemfile - File Layout

Field Name	Field Type	Default Value	Description
Ref Item	Char(25)	NA	Reference Item number
Item	Char(25)	NA	Item number

File Name: Primary Variant Data File

The Primary Variant Data File file name (prim_variantfile) is not fixed; it is determined by a runtime parameter.

Table 29-8 prim_variantfile - File Layout

Field Name	Field Type	Default Value	Description
Location	Number(10)	NA	Location number
Item	Char(25)	NA	Item number
Prim Variant	Char(25)	NA	Primary variant

File Name: Variable Weight UPC Definition File

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

File Name: Valid Store/Day Combination File

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

File Name: Codes File

The Codes File file name (codesfile) is not fixed; it is determined by a runtime parameter.

Table 29-11 codefile - File Layout

Field Name	Field Type	Default Value	Description
Code type	Char(4)	NA	Code type
Code	Char(6)	NA	Code ID
Code seq	Number(4)	NA	Code sequence

File Name: Error Information File

The Error Information File file name (errorfile) is not fixed; it is determined by a runtime parameter.

Table 29-12 errorfile- File Layout

Field Name	Field Type	Default Value	Description
Error code	Char(25)	NA	Error code
System Code	Char(6)	NA	System Code
Error desc	Char(255)	NA	Error description
Rec solution	Char(255)	NA	Error rectify solution

File Name: Store POS Mapping File

The Store POS Mapping File file name (storeposfile) is not fixed; it is determined by a runtime parameter.

Table 29-13 storeposfile- File Layout

Field Name	Field Type	Default Value	Description
Store	Number(10)	NA	Store
POS Type	Char(6)	NA	Point Of Sale type
Start Tran No.	Number(10)	NA	Start transaction number
End Tran No.	Number(10)	NA	End transaction number

File Name: Tender Type Mapping File

The Tender Type Mapping File file name (tendertypefile) is not fixed; it is determined by a runtime parameter.

Table 29-14 tendertypefile - File Layout

Field Name	Field Type	Default Value	Description
Group	Char(6)	NA	Tender type Group
Id	Number(6)	NA	Tender type ID
Desc	Char(120)	NA	Tender type description

File Name: Merchant Code Mapping File

The Merchant Code Mapping File file name (merchcodesfile) is not fixed; it is determined by a runtime parameter.

Table 29-15 merchcodesfile - File Layout

Field Name	Field Type	Default Value	Description
Non Merch Code	Char (6)	NA	Non-Merchant Code

File Name: Partner Mapping File

The Partner Mapping File file name (partnerfile) is not fixed; it is determined by a runtime parameter.

Table 29-16 partnerfile - File Layout

Field Name	Field Type	Default Value	Description
Partner Type	Char(6)	NA	Partner Type
Partner Id	Char(10)	NA	Partner ID

File Name: Supplier Mapping File

The Supplier Mapping File file name (supplierfile) is not fixed; it is determined by a runtime parameter.

Table 29-17 supplierfile - File Layout

Field Name	Field Type	Default Value	Description
Supplier	Number(10)	NA	Supplier ID
Sup status	Char(1)	NA	Supplier status

File Name: Employee Mapping File

The Employee Mapping File file name (employeefile) is not fixed; it is determined by a runtime parameter.

Table 29-18 employeefile - File Layout

Field Name	Field Type	Default Value	Description
Store	Number(10)	NA	Store ID
POS Id	Char(10)	NA	Point Of Sale ID
Emp Id	Char(10)	NA	Employee ID

File Name: Banner Information File

The Banner Information File file name (bannerfile) is not fixed; it is determined by a runtime parameter

Table 29-19 bannerfile - File Layout

Field Name	Field Type	Default Value	Description
Store	Number(10)	NA	Store ID
Banner data	Number(4)	NA	Banner ID

File Name: Currency Information File

The Currency Information File file name (currencyfile) is not fixed; it is determined by a runtime parameter.

Table 29-20 currencyfile - File Layout

Field Name	Field Type	Default Value	Description
Currency Code	Char(1)	NA	Currency Code

File Name: Promotion Information File

The Promotion Information File file name (promfile) is not fixed; it is determined by a runtime parameter.

Table 29-21 promfile - File Layout

Field Name	Field Type	Default Value	Description
Promotion	Number(10)	NA	Promotion ID
Component	Number(10)	NA	This contains the Offer ID value from RPM.

File Name: Physical Warehouse Information File

The Physical Warehouse Information File filename (whfile) is not fixed; it is determined by a runtime parameter.

Table 29-22 whfile - File Layout

Field Name	Field Type	Default Value	Description
Warehouse	Number(10)	NA	Warehouse ID

File Name: Inventory Status Information File

The Inventory Status Information File file name (invstatusfile) is not fixed; it is determined by a runtime parameter.

Table 29-23 invstatusfile - File Layout

Field Name	Field Type	Default Value	Description
Inventory Status	Char(10)	NA	Inventory Status

Design Assumptions

This section deals with:

Making Changes in the CODE_DETAIL Table
Primary Variant Relationships

Making Changes in the CODE_DETAIL Table

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
1. 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)
2. make -f mts.mk resa-install

Primary Variant Relationships

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.

Figure 29-3 Primary Variant Relationships

saimptlog/saimptlogi (Import of Unaudited Transaction Data from POS to ReSA)

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

Design Overview

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.

Scheduling Constraints

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.

Restart and Recovery

NA

Key Tables Affected

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 Contract

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.

Output File Layout

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)

Control Files

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

Table 29-38 File Name: Satwritelock.ctl

Table Name	Column Name	Field Type	Field Width	Position	Description
SA_TRAN_WRITE_LOCK	STORE_DAY_SEQ_NO	INTEGER EXTERNAL DATE	20	1:20	NA
TRAN_SEQ_NO	INTEGER EXTERNAL DATE	20	21:40	NA

Table Name

Column Name

Field Type

Field Width

Position

Description

SA_TRAN_WRITE_LOCK

STORE_DAY_SEQ_NO

INTEGER EXTERNAL

DATE

20

1:20

NA

TRAN_SEQ_NO

INTEGER EXTERNAL

DATE

20

21:40

NA

ReSA Interface File Layout [rtlog]

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
	E-mail	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
Transaction Item	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.

Common Requirements/Validations

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

Requirements per Record Types

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.

Code Type Validations

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
Customer Attribute	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.
Transaction Tax	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.

Transaction of Type SALE

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	Item Status is a required field; it determines whether the item is Sold (S), Returned (R), or Voided (V). If the item status is S, the quantity sign is expected to be P. If the item status is R, the quantity sign is expected to be N. Also, if the item status is ORI, LIN, ORD, or LCO, the quantity sign should be P. In the case of ORC or LCA, it should be N. If the item status is V, the quantity sign is the reverse of the quantity sign of the voided item. That is, if an item with status S is voided, the quantity sign would be N. Furthermore, the sum of the quantities being voided cannot exceed the sum of the quantities that are Sold or Returned. Note: Neither of the two validations are performed by saimptlog, but an audit rule could be created to check this. The following item statuses are used for handling items on customer order layaway: ORI - Order Initiate ORD - Order Complete ORC - Order Cancel LIN - Layaway Initiate LCA - Layaway Cancel LCO - Layaway Complete In a typical sale, the items all have a status of S. In the case of an odd exchange, some items will have a status of R. In a typical return, the items all have a status of R. In the case of an odd exchange, some items will have a status of S. If an item has status R, then the Return Reason Code field may be populated. If it is, it will be validated against code type SARR. Also, it is better to capture the Return Reason Code in the case of items on ORC or LCA, but it is not mandatory. No validation is kept for these new item statuses for checking of SARR. If the price of an item is overridden, the Override Reason and Original Unit Retail fields must be populated.
IDISC	The RMS Promotion Type field must always be populated with values of code type PRMT. The Promotion field is validated, when a value is passed, against the promhead table. If the promotion is In Store (code 1004), the Discount Type field must be populated with values of code type SADT. The Discount Reference Number is a promotion number which is of status A, E, or M. If the Discount Type is SCOUP for Store Coupon, the Coupon Number field must be populated. The Coupon Reference Number field is optional.
IGTAX	The IGTAX_CODE field must always be populated depending on the system's default tax type. For a default tax type of SALES, this field will be populated with values of code_type TAXC. For a default tax type of SVAT or GTAX,, this field will be populated with VATC (vat_code from vat_codes table). IGTAX is an Item-level tax. The TAX_AUTHORITY field must always be populated when the default tax type is GTAX.
TTAX	The TAX_CODE field must always be populated depending on the system's default tax type. For a default tax type of SALES, this field will be populated with values of code_type TAXC. For a default tax type of GTAX or SVAT, this field will be populated with VATC (vat_code from vat_codes table). TTAX is a Transaction-level tax.
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.

Transaction of Type PVOID

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.

Transaction of type RETURN

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

Transaction of type SPLORD

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).

Transaction of type EEXCH

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.

Table 29-48 Meaning of Reference Number Fields

Transaction Type	Sub-transaction Type	Reference Number Field	Meaning of Reference Field	Req?
EEXCH	NA	1	Receipt Indicator (Y/N)	Y
EEXCH	EMP	3	Employee Number of the employee exchanging the goods.	N

Transaction of type PAIDIN

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.

Table 29-49 Meaning of Reference Number Fields

Reason Code	Reference Number Column	Meaning	Req?
NSF	1	NFS Check Credit Number	N
ACCT	1	Account Number	N

Transaction Type PAIDOU

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.

Table 29-50 Meaning of Reference Number Fields

Sub Transaction Type	Reason Code	Reference Number Column	Meaning	Req?
EV	NA	2	Personal ID Number	N
EV	NA	3	Routing Number	N
EV	NA	4	Account Number	N
NA	PAYRL	1	Money Order Number	N
NA	PAYRL	2	Employee Number	N
NA	INC	1	Incident Number	N

Transaction of Type PULL

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

Table 29-51 Expected Values for Sign Fields

TRANSACTION TYPE	TITEM.Quantity Sign	TEND.Tender Sign	TTAX.Tax Sign	IDISC.Quantity Sign
PULL	NA	N	NA	NA

Transaction of Type LOAN

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:

Table 29-52 Expected Values for Sign Fields

TRANSACTION TYPE	TITEM.Quantity Sign	TEND.Tender Sign	TTAX.Tax Sign	IDISC.Quantity Sign
LOAN	NA	P	NA	NA

Transaction Type Cond

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.

Table 29-53 Meaning of Reference Number Fields

Reference Number Column	Meaning	Req?
1	Weather - code type WEAT	N
2	Temperature - a signed 3 digit number.	N
3	Traffic - code_type TRAF	N
4	Construction - code_type CONS	N

Transaction of Type TOTAL

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.

Transaction of Type METER

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

Transaction of Type PUMPT

This transaction is generated when a pump test is performed. This type of transaction has only TITEM records.

Transactions of Type TANKDP

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.

Table 29-55 Meaning of Reference Number Fields

Reference Number Column	Meaning of Reference Field	Req?
1	Tank identifier	Y
5	Dip Type (FUEL, WATER, and so on)	Y
6	Dip Height Major (decimal places required)	Y
7	Dip Height Minor (decimal places required)	Y

Transaction of Type DCLOSE

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.

Transaction of Type OTHER

This transaction is a generic transaction type to support Micros Xstore integration. This will identify all the other transaction types that are not currently supported. This type of transaction has only THEAD and TTAIL records.

Design Assumptions

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

DCLOSE Transaction Type

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.

saimptlogtdup_upd (Processing to Allow Re-Upload of Deleted Transactions)

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

Design Overview

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.

Scheduling Constraints

Table 29-57 Scheduling Constraints

Schedule Information	Description
Processing Cycle	Adhoc
Scheduling Considerations	This program should be run before running saimptlog/saimptlogi if any Store-Day's have been deleted.
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 29-58 Key Tables Affected

Table	Select	Insert	Update	Delete
SA_STORE_DAY	Yes	No	Yes	No
SA_TRAN_HEAD	Yes	No	No	No

Integration Contract

Integration Type	NA
File Name	NA
Integration Contract	NA

Design Assumptions

NA

saimptlogfin (Complete Transaction Import Processing)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

NA

Key Tables Affected

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

Design Assumptions

NA

savouch (Sales Audit Voucher Upload)

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

Design Overview

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.

Scheduling Constraints

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

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.

Key Tables Affected

Table 29-62 Key Tables Affected

Table	Select	Insert	Update	Delete
SA_VOUCHER	Yes	Yes	Yes	No

Integration Contract

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)

Input File Layout

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).

Design Assumptions

NA

saimpadj (Import Total Value Adjustments From External Systems to ReSA)

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

Design Overview

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.

Scheduling Constraints

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

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.

Key Tables Affected

Table 29-65 Key Tables Affected

Table	Select	Insert	Update	Delete
SA_TOTAL	Yes	No	No	No
SA_HQ_VALUE	No	Yes	No	No
SA_STORE_DAY	Yes	No	Yes	No
SA_EXPORT_LOG	Yes	Yes	No	No
SA_TOTAL_USAGE	Yes	No	No	No

Integration Contract

Integration Type	Upload to ReSA
File Name	Determined by runtime parameters.
Integration Contract	IntCon000047

Input File Layout

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).

Design Assumptions

NA

satotals (Calculate Totals based on Client Defined Rules)

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

Design Overview

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.

Scheduling Constraints

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.

Restart/Recovery

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.

Key Tables Affected

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

Integration Contract

Integration Type	NA
File Name	NA
Integration Contract	NA

Design Assumptions

NA

sarules (Evaluate Transactions and Totals based on Client Defined Rules)

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

Design Overview

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.

Scheduling Constraints

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.

Restart/Recovery

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.

Key Tables Affected

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

Integration Contract

Integration Type	NA
File Name	NA
Integration Contract	NA

Design Assumptions

NA

sapreexp (Prevent Duplicate Export of Total Values from ReSA)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

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.

Key Tables Affected

Table 29-72 Key Tables Affected

Table	Select	Insert	Update	Delete
SA_EXPORT_LOG	Yes	No	No	No
SA_STORE_DAY	Yes	No	No	No
SA_TOTAL_USAGE	Yes	No	No	No
SA_EXPORT_LOG	Yes	No	No	No
SA_EXPORTED	Yes	Yes	No	No
SA_EXPORTED_REV	Yes	No	No	No

Integration Contract

Integration Type	NA
File Name	NA
Integration Contract	NA

Design Assumptions

NA

saexprms (Export of POS transactions from ReSA to RMS)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

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.

Key Tables Affected

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 Contract

Integration Type	Download from ReSA
File Name	POSU_ appended with store number, business date and system date
Integration Contract	IntCon000044

File Layout

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).

Design Assumptions

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.

saordinvexp (Export Inventory Reservation/Release for In Store Customer Order & Layaway Transactions from ReSA)

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

Design Overview

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.

Scheduling Constraints

Table 29-76 Scheduling Constraints

Schedule Information	Description
Processing Cycle	NA
Frequency	NA
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	Multithreaded based on store number

Restart/Recovery

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.

Key Tables Affected

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 Contract

Integration Type	Inventory Export from ReSA to RMS
File Name	ORIN_<store>_<tran_date>_<sysdate>
Integration Contract	IntCon000049

Output File Layout

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).

Design Assumptions

NA

saexpdw (Export from ReSA to Oracle Retail Analytics)

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

Design Overview

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.

Scheduling Constraints

Table 29-79 Scheduling Constraints

Schedule Information	Description
Processing Cycle	Ad Hoc
Frequency	Daily
Scheduling Considerations	This will run after auditors have made corrections to the data.
Pre-Processing	sapreexp.pc
Post-Processing	resa2dw
Threading Scheme	Multi-threaded by store

Restart/Recovery

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.

Key Tables Affected

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 Contract

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.

Oracle Retail Sales Audit (ReSA) - File Layout - Retail Analytics

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.

RDWT File Layout

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

Transaction Item Information Produced by saexpdw.pc after Translation by resa2dw

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

RDWF File

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

Retail Analytics Form of Payment File after Translation by resa2dw

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

RDWS File

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

Store Totals Information after Translation by resa2dw

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

RDWC File

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

Cashier/ Register Totals Information after Translation by resa2dw

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

Design Assumptions

NA

saexpsim (Export of Revised Sale/Return Transactions from ReSA to SIM)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

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.

Key Tables Affected

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 Contract

Integration Type	Download from ReSA
File Name	SIMT_ appended by store number, business date, and system date
Integration Contract	IntCon000045

Output File

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).

Design Assumptions

NA

saexpim (Export DSD and Escheatment from ReSA to Invoice Matching)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

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.

Key Tables Affected

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

Integration Contract

Integration Type	Download from ReSA
File Name	NA
Integration Contract	IntCon00004 INVC_HEAD table

Design Assumptions

NA

saexpgl (Post User Defined Totals from ReSA to General Ledger)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

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.

Key Tables Affected

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

Integration Contract

Integration Type	Download from ReSA
File Name	NA
Integration Contract	IntCon000019 TG_FIF_GL_DATA

Design Assumptions

NA

ang_saplgen (Extract of POS Transactions by Store/Date from ReSA for Web Search)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

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.

Key Tables Affected

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 Contract

Integration Type	Download from ReSA
File Name	POSLOG_<store>_<business date>_<system date>.xml
Integration Contract	IntCon000018

Output File Layout

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.

Design Assumptions

NA

saescheat (Download of Escheated Vouchers from ReSA for Payment)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

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.

Key Tables Affected

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

Integration Contract

Integration Type	Download from ReSA
File Name	NA
Integration Contract	IntCon000039

Design Assumptions

NA

saescheat_nextesn (Generate Next Sequence for Escheatment Processing)

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

Design Overview

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.

Scheduling Constraints

Table 29-101 Scheduling Constraints

Schedule Information	Description
Processing Cycle	Ad Hoc
Frequency	Monthly
Scheduling Considerations	This process is executed as a part of the saescheat.pc processing.
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

Table 29-102 Key Tables Affected

Table	Select	Insert	Update	Delete
ALL_SEQUENCES	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No

Design Assumptions

NA

saexpach (Download from ReSA to Account Clearing House (ACH) System)

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

Design Overview

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.

Scheduling Constraints

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

Restart/Recovery

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.

Security Considerations

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.

Key Tables Affected

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 Contract

Integration Type	Download from ReSA
File Name	ACH_ appended with the consolidating routing number, consolidating account number, and current system date.
Integration Contract	IntCon000040

Output File

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).

Design Assumptions

NA

saexpuar (Export to Universal Account Reconciliation System from ReSA)

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

Design Overview

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.

Scheduling Constraints

Table 29-106 Scheduling Constraints

Schedule Information	Description
Processing Cycle	Ad Hoc
Frequency	Daily
Scheduling Considerations	This program should run after the ReSA Totaling process and Audit Rules process.
Pre-Processing	Satotals, sarules, sapreexp
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

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.

Key Tables Affected

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 Contract

Integration Type	Download from ReSA
File Name	UAR usage type appended with system date.
Integration Contract	IntCon000046

Output File

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.

Design Assumptions

NA

saprepost (Pre/Post Helper Processes for ReSA Batch Programs)

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

Design Overview

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

Scheduling Constraints

Table 29-110 Scheduling Constraints

Schedule Information	Description
Processing Cycle	Ad Hoc
Frequency	Daily
Scheduling Considerations	NA
Pre-Processing	NA
Post-Processing	NA
Threading Scheme	NA

Restart/Recovery

NA

Key Tables Affected

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

Integration Contract

Integration Type	NA
File Name	NA
Integration Contract	NA

Design Assumptions

NA

sapurge (Purge Aged Store/Day Transaction, Total Value and Error Data from ReSA)

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

Design Overview

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.

Scheduling Constraints

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

Restart/recovery is implicit in purge programs. The program only needs to be run again to restart appropriately.

Key Tables Affected

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

Integration Contract

Integration Type	NA
File Name	An optional output file name is passed into the program as a runtime parameter; the output file lists deleted items.
Integration Contract	NA

Design Assumptions

NA

29 Oracle Retail Sales Audit Batch Process and Designs

Oracle Retail Sales Audit Dataflow Diagram

Oracle Retail Sales Import Process

POS File Validation/Upload Sub-Process saimiptlog vs saimptlogi

Total Calculations and Rules

Totals when Transactions are Modified

Oracle Retail Sales Export Process

Full Disclosure and Post Export Changes

Batch Design Summary of ReSA Modules

Import Process Programs

Totals/Rules Programs

Export Programs

Other Programs

sastdycr (Create Store Day for Expected Transactions)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

Design Assumptions

sagetref (Get Reference Data for Sales Audit Import Processing)

Design Overview

Scheduling Constraints

Restart/Recovery

Key Tables Affected

Integration Contract

File Name: Item File

File Name: Waste Data File

File Name: Reference Item Data

File Name: Primary Variant Data File

File Name: Variable Weight UPC Definition File

File Name: Valid Store/Day Combination File

File Name: Codes File

File Name: Error Information File

File Name: Store POS Mapping File

File Name: Tender Type Mapping File

File Name: Merchant Code Mapping File

File Name: Partner Mapping File

File Name: Supplier Mapping File

File Name: Employee Mapping File

File Name: Banner Information File

File Name: Currency Information File

File Name: Promotion Information File

File Name: Physical Warehouse Information File

File Name: Inventory Status Information File

Design Assumptions

Making Changes in the CODE_DETAIL Table

Primary Variant Relationships

saimptlog/saimptlogi (Import of Unaudited Transaction Data from POS to ReSA)

Design Overview

Scheduling Constraints

Restart and Recovery

Key Tables Affected

Integration Contract

Output File Layout

Control Files

ReSA Interface File Layout [rtlog]

Common Requirements/Validations

Requirements per Record Types

Code Type Validations

Transaction of Type SALE

Transaction of Type PVOID

Transaction of type RETURN

Transaction of type SPLORD

Transaction of type EEXCH

Transaction of type PAIDIN

Transaction Type PAIDOU

Transaction of Type PULL

Transaction of Type LOAN

Transaction Type Cond

Transaction of Type TOTAL

Transaction of Type METER

Transaction of Type PUMPT

Transactions of Type TANKDP

Transaction of Type DCLOSE

Transaction of Type OTHER

Design Assumptions

DCLOSE Transaction Type

saimptlogtdup_upd (Processing to Allow Re-Upload of Deleted Transactions)

Design Overview