Mirror of Oracle documentation
Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.
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.
- 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.
-
Create RTLOG Files (sacreatertlog) creates RTLOG files from data populated by the Selling Solutions. The Selling Solutions invoke the Sales Service (ReST) to populate transaction data in the staging tables.
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 multithreading 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 Exported Sales Audit Transactions (saexpsim_export_purge_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 Revised Sale/Return Transactions from Sales Audit to SIM in a Direct Database integration (saexport_sales_to_sim)
-
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 |
Table 19-1 (Cont.) Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| 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 satotalssqlb.pls/satotalssqls.pls Description Calculate Totals based on Client Defined Rules Functional Area Sales Audit, Totals Module Type Business Processing Module Technology PL/SQL Catalog ID RSA16 Wrapper Script rmswrap_plsql.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. Each record has a unique STORE_DAY_SEQ_NO value that represents a store and day. Records are committed to the database when a store/day is successfully processed. 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.
If the consignment rate review process is used, then this batch also marks eligible consignment sales and return transaction as ‘Pending for Rate Review’ so that they can identified by the subsequent rate review process.
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 RTLOG Files (sacreatertlog)
Module Name Description Functional Area
sacreatertlog.ksh Create RTLOG files Oracle Retail Sales Audit
Module Type Integration Module Technology Ksh Catalog ID Wrapper Script batch_sacreatertlog.ksh
Schedule
Oracle Retail Merchandising Batch Schedule
Design Overview
The purpose of this module is to create the RTLOG files using the data from the SVC_RTLOG_DATA_LAOD table (populated by Selling Solutions, through a REST call) after successful validation. The records will be deleted from the mentioned table at the end of the batch processing.
This will decrease QUANTITY from the SA_INPROGRESS_SALES table and decrease IN_PROGRESS_SALES_QTY from the ITEM_LOC_SOH table (for a SALE type transaction only) if there is any validation failure. This also populates the history ( SVC_RTLOG_DATA_LOAD_HIST ) and reject tables ( SVC_RTLOG_DATA_REJECT ).
Performance Considerations
The process for creating RTLOG files will be called in chunks. Maximum chunk size used is defined in the table RMS_PLSQL_BATCH_CONFIG .
Design Assumptions
N/A
Restart/Recovery
N/A
Tables Affected
| TABLE | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
| SVC_RTLOG_DATA_LOAD | Yes | No | No | Yes |
| SVC_RTLOG_DATA_STAGING | Yes | Yes | Yes | Yes |
| SVC_RTLOG_DATA_LOAD_HIST | Yes | Yes | No | No |
| SVC_RTLOG_DATA_REJECT | No | Yes | No | No |
| SA_INPROGRESS_SALES | No | No | Yes | No |
| ITEM_LOC_SOH | No | No | Yes | No |
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 |
Table 19-2 (Cont.) Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| 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)
sa_rules_total_extract.ksh
Module Name sa_rules_total_extract.ksh Description environment. Functional Area Oracle Retail Sales Audit Module Type Admin - Ad hoc Module Technology ksh Catalog ID Wrapper Script rmswrap_shell_out.ksh
Extracts totals and rules, along with their related information from a source environment.
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 |
Table 19-6 (Cont.) prim_variantfile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
| 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 |
Table 19-12 (Cont.) tendertypefile - File Layout
| Field Name | Field Type | Default Value | Description |
|---|---|---|---|
| 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).
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
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
Restart/Recovery
N/A
Design Assumptions
N/A
Purge Aged Data from Error table of saexproc batch (saexprocsalespurge)
saexprocsalespurge.ksh
Module Name
Description Purge Aged Data from Error table of saexproc batch 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 SA_EXPROC_SALES_ERROR table populated by the saexproc batch while exporting transactions from Sales Audit to Merchandising on a direct table load, based on the number of retention days passed as a runtime parameter to the batch. If a retention days input is not provided, then a default value of 95 days is used.
Note
This feature will be made available in future releases.
Performance Considerations
The retention period for the archived data should be carefully considered. Disregarding this results in the table space size reaching its limit and an inability to accommodate additional archive records.
Design Assumptions
N/A
Restart/Recovery
N/A
Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| SA_EXPROC_SAL | Yes | No | No | Yes |
| ES_ERROR |
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 Exported Sales Audit Transactions (saexpsim_export_purge_job)
Module Name Description Functional Area Module Type Module Technology Catalog ID Wrapper Script
SAEXPSIM_EXPORT_PURGE_JOB
Purge exported transactions that have passed retention days from the Oracle Retail Sales Audit to Oracle Retail Store Inventory Management interface tables.
Sales Audit Admin – Ad hoc Background Processing b8dwrap.ksh
Schedule
Oracle Retail Merchandising Batch Schedule
Design Overview
This background job finds all records on interface tables SA_EXPSIM_HEAD and SA_EXPSIM_DETAIL that have passed retention days based on the Sales Audit system option expsim_export_purge_days and purge the records by dropping partitions. It gives the option to copy the data to SA_EXPSIM_HEAD_PRG_HIST and SA_EXPSIM_DETAIL_PRG_HIST before purging.
Restart/Recovery
N/A
Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| SA_EXPSIM_HEAD | Yes | No | No | Yes |
| SA_EXPSIM_DETAIL | Yes | No | No | Yes |
| SA_EXPSIM_HEAD_PRG_HIST | No | Yes | No | No |
| SA_EXPSIM_DETAIL_PRG_HIST | No | Yes | No | No |
| Table Select | Insert | Update | Delete |
|---|---|---|---|
| SA_SYSTEM_OPTIONS Yes | No | No | No |
| RMS_BATCH_STATUS Yes | No | No | No |
| B8D_PROCESS_CONFIG Yes | No | No | No |
| JOB_AUDIT_LOGS No History Tables (b8d_sa_purge) | Yes | No | No |
| 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 |
Purge Into History Tables (b8d_sa_purge)
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 |
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
| 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 |
In this guide
- Guide: Batch Operations Guide
- Previous: 18 Franchise Management
- Next: 2 Administration Batch
Related chapters
- 6 Scheduled Integration — Inbound and Outbound Integration Guide · shares
ES_ERROR,RESTART_CONTROL,RMS_PLSQL_BATCH_CONFIG,SA_BALANCE_GROUP - 5 ReSTful Web Services — Inbound and Outbound Integration Guide · shares
SA_ERROR,SA_HQ_VALUE,SA_INPROGRESS_SALES,SA_POS_VALUE