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)
Lastly, there are two programs that are used for migrating totals and rules between environments:
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 |
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.
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 |
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 |
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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 |
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.
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 |
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
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 |
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.
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 |
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.
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 |
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 |
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.
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 |
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 |
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 |
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.
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 |
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.
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 |
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.
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 |
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.
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 |
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.
-
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.
-
If both indicators are set to 'N', then the records are deleted from the base tables without inserting into the history tables.
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 |
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
.