19 Sales Audit

The purpose of Sales Audit is to accept transaction data from point-of-sale (POS) and order management (OMS) solutions and move the data through a series of processes that culminate in "clean" data. Data that Sales Audit finds to be inaccurate is brought to the attention of the auditors who can use the features in Sales Audit to correct the exceptions.

Sales Audit uses several batch-processing modules to:

• Import POS/OMS transaction data sent from the store to the Sales Audit database

• Produce totals from user-defined totaling calculation rules that a user can review during the interactive audit

• Validate transaction and total data with user-defined audit rules that generate errors whenever data does not meet the criteria

• Create and export files in formats suitable for transfer to other applications

• Update previously exported data with adjustments received from external systems

The term store day is used throughout this chapter. Store day describes all transactions that occur in one business day at one store or location. Because retailers need the ability to audit transactions on a store-by-store basis for a defined period of time, store day data is maintained separately, beginning with the initial import of data from the POS/OMS system.

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

Note:

All integrations are not depicted in this diagram.

Figure 19-1 Oracle Retail Sales Audit Dataflow Diagram

Import Process

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

Preparing for Import

• Create Store Day for Expected Transactions (sastdycr) prepares the Sales Audit tables for data upload.

• Get Reference Data for Sales Audit Import Processing (sagetref) creates a number of reference files to be used for validation in the POS File Validation/Upload Process.

Importing Data

See the Merchandising Operations Guide Volume 2 for details on the following import programs:

• Import of Unaudited Transaction data from POS to Sales Audit (saimptlog/saimptlogi) validates files and uploads their transactions into the Sales Audit tables. This includes (as necessary) creating errors for the auditors to address.

• Sales Audit Voucher Upload (savouch) processes voucher sales and redemptions.

• Import Total Value Adjustments From External Systems to Sales Audit (saimpadj) imports adjustments to previously imported data.

• Customer Engagement Promotion Service (CePromoBatch.ksh) calls the ORCE webservice to retrieve promotion information, if using that solution to create promotions.

Import Processing Programs

• Processing to Allow Re-Upload of Deleted Transactions (saimptlogtdup_upd) fetches deleted transactions for a store day and modifies the tdup files remove deleted transactions in order to facilitate the saimptlog/saimptlogi uploads of deleted transactions again.

• Complete Transaction Import Processing (saimptlogfin) executes a number of import cleanup processes.

Figure 19-2 Oracle Retail Sales Import Process

Auditing Processing Programs

In addition to the base validations performed during auditing, there is the ability to define custom rules and totals. Custom rules allow you to define specific rules that are important for your business to validate for transaction. Custom totals provide the ability for you to define specific totals that you want calculated by Sales Audit during the auditing process. These totals are usually used for integrating to the General Ledger but can also be used for other integrations as well. Other programs in this section are helper programs used during the import, export, or auditing processes, or used for overall data maintenance.

Sales Audit Processing Programs

• Calculate Totals Based on Client Defined Rules (satotals) totals transactions based on calculation definitions that you create using the online Totals Calculation Definition.

• Evaluate Transactions and Totals based on Client Defined Rules (sarules) audits transactions for retailer-defined audit rules.

• Prevent Duplicate Export of Total Values from ReSA (sapreexp) 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.

• Generate Next Sequence for Escheatment Processing (saescheat_nextesn) gets the next free sequence for use in the saescheat process.

• Pre/Post Helper Processes for ReSA Batch Programs (saprepost) 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.

• Purge Aged RTLOG Data (sartlogdatapurge) drops the partitions from the history and reject tables used by the Sales Service to load RTLOG files.

• Purge Aged Store/Day Transaction, Total Value and Error Data from Sales Audit (sapurge) removes aged data from Sales Audit.

• Purge the Invalid In-progress Sales Bucket (sainprogresspurge) deletes records from in-progress staging tables for the Store Days that have been closed and for which all the sales data has been exported to Merchandising.

There are also some background jobs that can be run as an alternative to some of these audit processing programs. These include:

• Calculate Totals Based on Client Defined Rules (sa_totals_calc_job)

• Evaluate Transactions and Totals based on Client Defined Rules (sa_rules_eval_job)

• Purge Into History Tables (b8d_sa_purge)

Lastly, there are two programs that are used for migrating totals and rules between environments:

• Extract Totals and Rules (sa_rules_total_extract)

• Migrate Totals and Rules (sa_rules_total_upload)

Export Process

Another key function of Sales Audit is to export audited data to other solutions. This includes Merchandising, Invoice Matching, within the Merchandising suite of solutions, but also commonly includes exports to store inventory (SIM/SIOCS), Oracle Retail Insights Cloud Service (ORI), and external financials institutions.

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 the Sales Audit system options. There are two units of work, transaction or store day. If the unit of work selection is transaction, Sales Audit exports transactions 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.

Full Disclosure and Post-export Changes

If you modify data during the interactive audit that was previously exported to Merchandising, Sales Audit export batch modules re-export the modified data in accordance with a process called full disclosure. Full disclosure means that any previously exported values are fully backed out before the new value is sent.

Export Programs

See the Merchandising Operations Guide Volume 2 for details on the following export programs:

• Download from Sales Audit to Account Clearing House (ACH) System (saexpach)

• Download of Escheated Vouchers from Sales Audit for Payment (saescheat)

• Export DSD and Escheatment from Sales Audit to Invoice Matching (saexpim)

• Export from Sales Audit to Oracle Retail Analytics (saexpdw)

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

• Export of Revised Sale/Return Transactions from Sales Audit to SIM (saexpsim)

• Export of POS Transactions from Sales Audit to Merchandising (saexprms)

• Export to Universal Account Reconciliation System from Sales Audit (saexpuar)

• Extract of POS Transactions by Store/Date from Sales Audit for Web Search (ang_saplgen.ksh)

• Post User Defined Totals from Sales Audit to General Ledger (saexpgl)

Calculate Totals Based on Client Defined Rules (sa_totals_calc_job)

Module Name	sa_totals_calc_job
Description	Calculate Totals based on Client Defined Rules
Functional Area	Sales Audit, Totals
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from the Sales Audit Store/Day table for all stores wherein auditing status is "Re-Totaling/Auditing Required". 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 Sales Audit is dynamic. Sales Audit 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 Sales Audit 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 program runs. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will process the records for totals build-up and calculation by calling SA_BUILD_TOTAL_SQL.PROCESS_CALC_TOTALS for each store day captured. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 19-1 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_SA_TOTALS_CALC_STG	Yes	Yes	No	Yes
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

Design Assumptions

N/A

Calculate Totals Based on Client Defined Rules (satotals)

Module Name	satotals.pc
Description	Calculate Totals based on Client Defined Rules
Functional Area	Sales Audit, Totals
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RSA16
Wrapper Script	batch_satotals.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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 Sales Audit is dynamic. Sales Audit 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 Sales Audit does not calculate. Whenever you create new calculation definitions or edit existing ones, they become part of the automated totaling process the next time that this process runs.

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.

Design Assumptions

N/A

Complete Transaction Import Processing (saimptlogfin)

Module Name	saimptlogfin.pc
Description	Complete Transaction Import Processing
Functional Area	Oracle Retail Sales Audit
Module Type	Admin
Module Technology	ProC
Catalog ID	RSA38
Wrapper Script	batch_saimptlogfin.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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 Sales Audit import log as partially or fully loaded. This will unlock the store day records after all store transactions are imported. This will also close the store day for the previous day for an online store, if there was no DCLOSE transaction received for it.

Restart/Recovery

N/A

Design Assumptions

N/A

Create Store Day for Expected Transactions (sastdycr)

Module Name	sastdycr.pc
Description	Create Store Day for Expected Transactions
Functional Area	Oracle Retail Sales Audit
Module Type	Business Processing
Module Technology	ProC
Catalog ID	RSA15
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

This program will also create Store/days a few days prior to the actual business date for the online stores, based on SA_SYSTEM_OPTIONS.CREATE_STORE_DAY_PRIOR.

This will be taken into consideration only when the program is executed without any date input.

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.

Design Assumptions

N/A

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

Module Name	sa_rules_eval_job
Description	Evaluate Transactions and Totals based on Client Defined Rules
Functional Area	Oracle Retail Sales Audit
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from the Sales Audit Store/Day table for all stores wherein auditing status is "Totaled". 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 Sales Audit are dynamic. Aside from basic data validations rules are not predefined in the system. Retailers have the ability to define through the online Rule Definition Wizard. Errors uncovered by these rules are available for review on-line during the interactive audit process. After users modify existing rules or create new ones, they become part of the rules the next time that this program runs. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will process the records for auditing evaluation by calling SA_AUDIT_RULES_SQL.PROCESS_AUDIT_RULES for each store day captured. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 19-2 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_SA_RULES_EVAL_STG	Yes	No	Yes	Yes
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

Design Assumptions

N/A

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

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
Catalog ID	RSA17
Wrapper Script	batch_sarules.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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 Sales Audit 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 you modify existing rules or create new ones, they become part of the rules the next time that sarules.pc runs.

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.

Design Assumptions

N/A

Extract Totals and Rules (sa_rules_total_extract)

Module Name	sa_rules_total_extract.ksh
Description	Extracts totals and rules, along with their related information from a source environment.
Functional Area	Oracle Retail Sales Audit
Module Type	Admin - Ad hoc
Module Technology	ksh
Catalog ID
Wrapper Script	rmswrap_shell_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This program is one of a set of processes, along with Rules and Totals Upload, that migrates customer-defined totals and rules from a source environment into the destination environment. For example, this may be used to extract totals and rules set up in a pre-production environment to production prior to final cutover. When these programs are run, existing totals and rules information are extracted from the source environment, and then uploaded into the destination environment, overlaying the totals and rules previously configured in the target environment.

This program is the first step in the two-step process: The latest version of all totals and rules will be extracted from the source environment and written out to flat files.

The following information is extracted from the source environment:

• Parm Type

• Realm Type

• Location Traits

• Parm with the highest sequence number

• Realm with the highest sequence number

• VR Head

• VR Realm

• VR Parms

• VR Links

• Total Header - latest revision for the total

• Total Header Translation - latest revision for the total

• Total Location Traits - latest revision for the total

• Total Restrictions - latest revision for the total

• Total Usage - latest revision for the total

• Rule Header - latest revision for the rule

• Rule Header Translation - latest revision for the rule

• Rule Location Trait - latest revision for the rule

• Rule Components - latest revision for the rule

• Rule Component Restrictions - latest revision for the rule

• Rule Errors - latest revision for the rule

Restart/Recovery

N/A

Design Assumptions

N/A

Generate Next Sequence for Escheatment Processing (saescheat_nextesn)

Module Name	saescheat_nextesn.pc
Description	Generate Next Sequence for Escheatment Processing
Functional Area	Oracle Retail Sales Audit
Module Type	Admin
Module Technology	ProC
Catalog ID	RSA25
Wrapper Script	N/A

Schedule

Oracle Retail Merchandising Batch Schedule

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. This process is executed as part of the saexcheat.pc processing.

Restart/Recovery

NA

Design Assumptions

N/A

Get Reference Data for Sales Audit Import Processing (sagetref)

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
Catalog ID	RSA00
Wrapper Script	batch_sagetref.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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

• Sales Audit Employees

• Banners

• Currency Codes

• Promotions

• 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, Sales Audit converts the selling Unit of Measure (UOM) to the standard UOM during batch processing. This conversion enables Sales Audit to later export the standard UOM to the systems that require its use.

Restart/Recovery

N/A

I/O Specification

Integration Type	Download from Merchandising
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 19-3 Itemfile - File Layout

Field Name	Field Type	Default Value	Description
Item	Char(25)	N/A	Item number
Dept	Number(4)	N/A	Department ID
Class	Number(4)	N/A	Class
Subclass	Number(4)	N/A	Subclass ID
Standard UOM	Char(4)	N/A	Standard Unit of Measure
Catchweight Ind	Char(1)	N/A	Catch weight indicator
Class vat Ind	Char(1)	N/A	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 19-4 wastefile - File Layout

Field Name	Field Type	Default Value	Description
Item	Char(25)	N/A	Item number
Waste type	Char(6)	N/A	Waste type
Waste pct	Number(12,4)	N/A	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 19-5 Ref_itemfile - File Layout

Field Name	Field Type	Default Value	Description
Ref Item	Char(25)	N/A	Reference Item number
Item	Char(25)	N/A	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 19-6 prim_variantfile - File Layout

Field Name	Field Type	Default Value	Description
Location	Number(10)	N/A	Location number
Item	Char(25)	N/A	Item number
Prim Variant	Char(25)	N/A	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 19-7 varupcfile - File Layout

Field Name	Field Type	Default Value	Description
Format Id	Char(1)	N/A	Format ID
Format desc	Char(20)	N/A	Format description
Prefix length	Number(1)	N/A	Pefix Length
Begin item digit	Number(2)	N/A	Item digit begin
Begin var digit	Number(2)	N/A	Var digit begin
Check digit	Number(2)	N/A	Check digit
Default prefix	Number(1)	N/A	Default prefix
Prefix	Number(1)	N/A	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 19-8 storedayfile - File Layout

Field Name	Field Type	Default Value	Description
Store	Number(10)	N/A	Store number
Business date	Char(8)	N/A	Business date in YYYYMMDD format
Store day seq no	Number(20)	N/A	Store day sequence number
Day	Number(3)	N/A	Day
Tran no generated	Char(6)	N/A	Generated transaction number
POS data expected	Char(1)	N/A	If system_code is POS, then Y; otherwise N
Currency rtl dec	Number(1)	N/A	Currency rtl dec
Currency code	Char(3)	N/A	Currency code
Country id	Char(3)	N/A	Country ID
Vat Include Ind	Char(1)	N/A	Vat Include Indicator

File Name: Codes File

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

Table 19-9 codefile - File Layout

Field Name	Field Type	Default Value	Description
Code type	Char(4)	N/A	Code type
Code	Char(6)	N/A	Code ID
Code seq	Number(4)	N/A	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 19-10 errorfile- File Layout

Field Name	Field Type	Default Value	Description
Error code	Char(25)	N/A	Error code
System Code	Char(6)	N/A	System Code
Error desc	Char(255)	N/A	Error description
Rec solution	Char(255)	N/A	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 19-11 storeposfile- File Layout

Field Name	Field Type	Default Value	Description
Store	Number(10)	N/A	Store
POS Type	Char(6)	N/A	Point Of Sale type
Start Tran No.	Number(10)	N/A	Start transaction number
End Tran No.	Number(10)	N/A	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 19-12 tendertypefile - File Layout

Field Name	Field Type	Default Value	Description
Group	Char(6)	N/A	Tender type Group
Id	Number(6)	N/A	Tender type ID
Desc	Char(120)	N/A	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 19-13 merchcodesfile - File Layout

Field Name	Field Type	Default Value	Description
Non Merch Code	Char (6)	N/A	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 19-14 partnerfile - File Layout

Field Name	Field Type	Default Value	Description
Partner Type	Char(6)	N/A	Partner Type
Partner Id	Char(10)	N/A	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 19-15 supplierfile - File Layout

Field Name	Field Type	Default Value	Description
Supplier	Number(10)	N/A	Supplier ID
Sup status	Char(1)	N/A	Supplier status
Supplier Parent	Number(10)	N/A	Supplier Parent ID

File Name: Employee Mapping File

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

Table 19-16 employeefile - File Layout

Field Name	Field Type	Default Value	Description
Store	Number(10)	N/A	Store ID
POS Id	Char(10)	N/A	Point Of Sale ID
Emp Id	Char(10)	N/A	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 19-17 bannerfile - File Layout

Field Name	Field Type	Default Value	Description
Store	Number(10)	N/A	Store ID
Banner data	Number(4)	N/A	Banner ID
Stockholding Ind	Char(1)	N/A	Stockholding Indicator
Customer Order Loc Ind	Char(1)		Customer Order Location Indicator

File Name: Currency Information File

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

Table 19-18 currencyfile - File Layout

Field Name	Field Type	Default Value	Description
Currency Code	Char(1)	N/A	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 19-19 promfile - File Layout

Field Name	Field Type	Default Value	Description
Promotion	Number(10)	N/A	Promotion ID
Component	Number(10)	N/A	This contains the Offer ID value from Pricing.

File Name: Warehouse Information File

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

Table 19-20 whfile - File Layout

Field Name	Field Type	Default Value	Description
Warehouse	Number(10)	N/A	Warehouse ID
Physical Warehouse	Number(10)	N/A	Physical Warehouse ID
Customer Order Loc Ind	Char(1)	N/A	Customer Order Location Indicator

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 19-21 invstatusfile - File Layout

Field Name	Field Type	Default Value	Description
Inventory Status	Char(10)	N/A	Inventory Status

Design Assumptions

N/A

A Note about 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 Merchandising, 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:

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:

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 Sales Audit in the following way. Sometimes a POS system does not provide Sales Audit 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 Sales Audit that 20 golf shirts were sold. 20 golf shirts presents a problem for Sales Audit 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 Sales Audit 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. Sales Audit 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 Sales Audit 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 Sales Audit can only interpret items above the transaction level in conjunction with a primary variant, the invalid item error would occur during batch processing.

Figure 19-3 Primary Variant Relationships

Migrate Totals and Rules (sa_rules_total_upload)

Module Name	saprepost.pc
Description	Pre/Post Helper Processes for Sales Audit Batch Programs
Functional Area	Oracle Retail Sales Audit
Module Type	Admin
Module Technology	ProC
Catalog ID	RSA26
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This program is one of a set of processes, along with Rules and Totals Extract, that migrates customer-defined totals and rules from a source environment into the destination environment. For example, this may be used to extract totals and rules set up in a pre-production environment to production prior to final cutover. When the processes are run, existing totals and rules information are extracted from the source environment, and then uploaded into the destination environment, overlaying the totals and rules previously configured in the target environment.

This program is the second step in a two-step process: The latest version of all totals and rules will be uploaded into the destination environment.

The following information is uploaded into the destination environment:

• Parm Type

• Realm Type

• Location Traits

• Parm

• Realm

• VR Head

• VR Realm

• VR Parms

• VR Links

• Total Header

• Total Header Translation

• Total Location Traits

• Total Restrictions

• Total Usage

• Rule Header

• Rule Header Translations

• Rule Location Trait

• Rule Components

• Rule Component Restrictions

• Rule Errors

The tables that this information will be uploaded into will first be cleared out of any existing data, and then the statements in the files generated by the extract process will be run to upload the information from the source environment. After the upload, the sequences for realms, parms, and VR header will be updated to set the last value on the sequence to the maximum value of the ID fields.

When the rules and totals are uploaded, they will be rebuilt in the destination environment, using existing functions.

Restart/Recovery

N/A

Design Assumptions

N/A

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

Module Name	saprepost.pc
Description	Pre/Post Helper Processes for Sales Audit Batch Programs
Functional Area	Oracle Retail Sales Audit
Module Type	Admin
Module Technology	ProC
Catalog ID	RSA26
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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 19-22 Helper Functions

Catalog ID	Saprepost Job	Related Main Program
RSA47	saprepost saexprms post	saexprms
RSA48	saprepost saexpdw post	saexpdw
RSA39	saprepost saordinvexp post	saordinvexp
RSA51	saprepost saexpsim post	saexpsim
	saprepost sapreexp post	sapreexp

Restart/Recovery

NA

Design Assumptions

N/A

Prevent Duplicate Export of Total Values from ReSA (sapreexp)

Module Name	sapreexp.pc
Description	Prevent Duplicate Export of Total Values from Sales Audit
Functional Area	Oracle Retail Sales Audit
Module Type	Admin
Module Technology	ProC
Catalog ID	RSA20
Wrapper Script	rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

When you modify or revise a transaction through the Sales Audit user 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.

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.

Design Assumptions

N/A

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

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
Catalog ID	RSA19
Wrapper Script	batch_saimptlogtdup_upd.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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.

Restart/Recovery

N/A

Design Assumptions

N/A

Purge Aged RTLOG Data (sartlogdatapurge)

Module Name	sartlogdatapurge.ksh
Description	Purge Aged RTLOG Data
Functional Area	Oracle Retail Sales Audit
Module Type	Admin
Module Technology	Ksh
Catalog ID
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The purpose of this module is to drop the partitions from the history (SVC_RTLOG_DATA_LOAD_HIST) and reject tables (SVC_RTLOG_DATA_REJECT) populated by the RTLOG creation process, based on the given retention days. If retention days input is not provided then a default value of 30 days will be used.

Performance Considerations

The retention period for the archived data should be carefully considered. Disregarding this would result in the table space size reaching its limit and it would not be able to accommodate additional archive records.

Design Assumptions

N/A

Restart/Recovery

N/A

Tables Affected

Table	Select	Insert	Update	Delete
SVC_RTLOG_DATA_LOAD_HIST	Yes	No	No	Yes
SVC_RTLOG_DATA_REJECT	Yes	No	No	Yes

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

Module Name	sapurge.pc
Description	Purge Aged Store/Day Transaction, Total Value and Error Data from Sales Audit
Functional Area	Oracle Retail Sales Audit
Module Type	Admin
Module Technology	ProC
Catalog ID	RSA21
Wrapper Script	rmswrap_out.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

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 you want 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.

This program will also purge the data, which is being used for Sales Audit Auditor Framwork and purging criteria based on days_before_purge value from SA_SYSTEM_OPTIONS table.

Restart/Recovery

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

Design Assumptions

N/A

Purge Into History Tables (b8d_sa_purge)

Module Name	b8saprgb.pls/ b8saprgs.pls
Description	Purge records into History tables
Functional Area	Financial data
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records based on the Store/Day, Sales Audit System Options, and Period tables. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process records from the base tables based on Store Day Sequence Number, Store, and Business Day. Using bulk processing, this program will filter the records from the tables and insert the records into the history tables. Then the inserted records will be deleted from the base tables.

The decision to insert or not to insert the records into the history tables is based on the Archive Indicator and Archive Job Indicator from the Background Process Configuration table.

1. If the both the Archive Indicator and Archive Job Indicator values are Y, then the data from the base tables are inserted into the history tables.

2. If both indicators are set to 'N', then the records are deleted from the base tables without inserting into the history tables.

Restart/Recovery

N/A

Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
B8D_SA_PURGE_STG	No	Yes	No	No
ALL_PART_TABLES	Yes	No	No	No
SA_COMMENTS_HIST	No	Yes	No	No
SA_CUSTOMER_HIST	No	Yes	No	No
SA_CUST_ATTRIB_HIST	No	Yes	No	No
SA_ERROR_HIST	No	Yes	No	No
SA_ERROR_REV_HIST	No	Yes	No	No
SA_EXPORTED_HIST	No	Yes	No	No
SA_EXPORTED_REV_HIST	No	Yes	No	No
SA_EXPORT_LOG_HIST	No	Yes	No	No
SA_FLASH_SALES_HIST	No	Yes	No	No
SA_HQ_VALUE_HIST	No	Yes	No	No
SA_IMPORT_LOG_HIST	No	Yes	No	No
SA_MISSING_TRAN_HIST	No	Yes	No	No
SA_POS_VALUE_HIST	No	Yes	No	No
SA_STORE_DAY_HIST	No	Yes	No	No
SA_STORE_VALUE_HIST	No	Yes	No	No
SA_SYS_VALUE_HIST	No	Yes	No	No
SA_TOTAL_HIST	No	Yes	No	No
SA_TRAN_DISC_HIST	No	Yes	No	No
SA_TRAN_DISC_REV_HIST	No	Yes	No	No
SA_TRAN_HEAD_HIST	No	Yes	No	No
SA_TRAN_HEAD_REV_HIST	No	Yes	No	No
SA_TRAN_IGTAX_HIST	No	Yes	No	No
SA_TRAN_IGTAX_REV_HIST	No	Yes	No	No
SA_TRAN_ITEM_HIST	No	Yes	No	No
SA_TRAN_ITEM_REV_HIST	No	Yes	No	No
SA_TRAN_PAYMENT_HIST	No	Yes	No	No
SA_TRAN_PAYMENT_REV_HIST	No	Yes	No	No
SA_TRAN_TAX_HIST	No	Yes	No	No
SA_TRAN_TAX_REV_HIST	No	Yes	No	No
SA_TRAN_TENDER_HIST	No	Yes	No	No
SA_TRAN_TENDER_REV_HIST	No	Yes	No	No
SA_COMMENTS	Yes	No	No	Yes
SA_CUSTOMER	Yes	No	No	Yes
SA_CUST_ATTRIB	Yes	No	No	Yes
SA_ERROR	Yes	No	No	Yes
SA_ERROR_REV	Yes	No	No	Yes
SA_EXPORTED	Yes	No	No	Yes
SA_EXPORTED_REV	Yes	No	No	Yes
SA_EXPORT_LOG	Yes	No	No	Yes
SA_FLASH_SALES	Yes	No	No	Yes
SA_HQ_VALUE	Yes	No	No	Yes
SA_IMPORT_LOG	Yes	No	No	Yes
SA_MISSING_TRAN	Yes	No	No	Yes
SA_POS_VALUE	Yes	No	No	Yes
SA_STORE_DAY	Yes	No	No	Yes
SA_STORE_VALUE	Yes	No	No	Yes
SA_SYS_VALUE	Yes	No	No	Yes
SA_TOTAL	Yes	No	No	Yes
SA_TRAN_DISC	Yes	No	No	Yes
SA_TRAN_DISC_REV	Yes	No	No	Yes
SA_TRAN_HEAD	Yes	No	No	Yes
SA_TRAN_HEAD_REV	Yes	No	No	Yes
SA_TRAN_IGTAX	Yes	No	No	Yes
SA_TRAN_IGTAX_REV	Yes	No	No	Yes
SA_TRAN_ITEM	Yes	No	No	Yes
SA_TRAN_ITEM_REV	Yes	No	No	Yes
SA_TRAN_PAYMENT	Yes	No	No	Yes
SA_TRAN_PAYMENT_REV	Yes	No	No	Yes
SA_TRAN_TAX	Yes	No	No	Yes
SA_TRAN_TAX_REV	Yes	No	No	Yes
SA_TRAN_TENDER	Yes	No	No	Yes
SA_TRAN_TENDER_REV	Yes	No	No	Yes
SA_CUSTOMER	Yes	No	No	Yes
SA_POS_VALUE_WKSHT	Yes	No	No	Yes
SA_SYS_VALUE_WKSHT	Yes	No	No	Yes
SA_ERROR_WKSHT	Yes	No	No	Yes
SA_STORE_ACH	Yes	No	No	Yes
SA_ESCHEAT_VOUCHER	Yes	No	No	Yes
SA_ESCHEAT_TOTAL	Yes	No	No	Yes
KEY_MAP_GL	Yes	No	No	Yes
SA_GL_REF_DATA	Yes	No	No	Yes
SA_STORE_DAY_WRITE_LOCK	Yes	No	No	Yes

Purge the Invalid In-progress Sales Bucket (sainprogresspurge)

Module Name	sainprogresspurge.ksh
Description	Purge the invalid in-progress sales bucket
Functional Area	Oracle Retail Sales Audit
Module Type	Admin
Module Technology	Ksh
Catalog ID
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The purpose of this module is to delete the records from SA_INPROGRESS_SALES and SVC_INPROGRESS_SALES tables for the Store Days which have been closed and for which all the sales data has been exported to Merchandising. With that, it will also adjust the in-progress sales quantity in ITEM_LOC_SOH corresponding to the impacted item-location combination in SA_INPROGRESS_SALES.

Performance Considerations

N/A

Design Assumptions

N/A

Restart/Recovery

N/A

Tables Affected

Table	Select	Insert	Update	Delete
SA_INPROGRESS_SALES	Yes	No	No	Yes
SVC_INPROGRESS_SALES	No	No	No	Yes
ITEM_LOC_SOH	No	No	Yes	No
SA_TRAN_HEAD	Yes	No	No	No
SA_TRAN_ITEM	Yes	No	No	No
SA_STORE_DAY	Yes	No	No	No
SA_EXPORTED	Yes	No	No	No