With this series, I'm going to start details on Oracle Retail Sales Audit Application. Wondering about what is this application and how it works? Keep following this post.
ReSA is a system that uploads transaction data, processes the
data against total and rule sets, allows users to make corrections to the data,
and eventually exports the data to external systems.
Key Facts:
1. Configuration - One
time setup of ReSA to customize it for each retailer
·
Balancing Level
o
Level at which system totals are compared to POS totals for
balancing
o
Can be defined at 3 levels: Store, Register, Cashier
o
Initial one time setup
o
Change to the level would require purging of existing sales
data
·
Store Level and HQ Level Auditing
o
At end of day, sales information is loaded into ReSA
o
Any errors present can either be corrected or overridden
o
Store employees can correct data only while the store is
still open
o
Once the store is closed, HQ Level auditor can edit data or
override errors
o
After HQ auditing, the store is marked as audited
·
Trickle Polling
o
DCLOSE - separate transaction type defined in ReSA to
support trickle polling
o
DCLOSE contains number of RTLOG files to expect for the
store day
o
Number of files already loaded into ReSA is tracked
internally
·
Import/Export
o
System Codes to be defined for each system from which data
has to be imported or exported
o
Mappings to be done between system codes and stores to
enable import & export
2.
Foundation - To use ReSA, first
foundation data must be established
·
Partners
o
Banks
o
Escheatable States
o
Escheatable Countries
·
ACH Setup
o
Bank/Store Relationships
§ Bank Account
Number/Routing Number
§ Consolidating
Account Number/Routing Number
o
ACH
functionality estimates following day’s bank deposit based on
§ Rolling average
of last 4 weeks data
§ Current day's
total
·
Store
Attributes
o Define expected Imports/Exports
o Define store close dates, company close dates and store exceptions to company close dates
o Unique transaction number by
§ Network
§ Register
o Integrated POS
·
Sales Audit
Employees
o Employee setup in RMS as Store or Headquarter Employee
o Only need to be set up
if they are going to be auditing or if you plan to validate employee ids
coming in on the RTLOG
·
Automated
Totals
o Combination of transactions or other totals defined according
to retailer specifications
o Totals may be audited and exported to downstream applications
o
Use Totals wizard to build totals
§ Builds a PL/SQL statement
§ Define in what exports the total will be included
§ Uses roll-ups to create sub-totals
§ Assign Location Traits to define the stores to which the
total applies
·
Audit Rules
o Defined to create an exception or error condition against a
transaction or a total, defined according to retailer specifications.
o Use the Audit Rule wizard to build rules
§ Builds a PL/SQL
statement
§ Assign the
appropriate error code
§ Build a rule
against either transaction or total data
§ Presence or
Absence rule
§ Assign
restrictions (tolerances)
§ Assign location
traits to define the store to which the rule applies
o Changing data resets the flag to re-totaling and auditing required
·
Error Codes
o Define if error can be overridden by a store or HQ employee
o Define the form/tab where the error can be corrected
o Define the exports which the error impacts (fatal error)
3.
ReSA Key Functionality
· Perform an interactive audit that includes:
o Review store/day details
o Review transaction details
o Fix errors
Screens
to assist Auditor
o Summary Screens
§ Store Level
Summary
§ Register Level
Summary
§ Cashier Level
Summary
§ Tender Summay
§ Item Summary
o Audit trails on transactions and totals
o Totaling Details
o Transaction Search and Maintenance
o Missing Transactions and Error List
o Over / Short tracking and analysis
·
Perform research
o Research on errors found in sales auditing can be done using
§ Review of
totals audit trail
§ Review of
transaction audit trail
·
Perform reporting
o Reports available in ReSA include
§ Credit card
summary report
§ Voucher activity report
§ Flash sales
report
§ Flash totals
report
o More reports are available in RDW
4.
ReSA Imports & Extracts
ReSA receives data from
·
Retail Merchandising System
(validation only)
·
Retail Customer Order
Management
·
Point of Sales Systems
ReSA sends data to
·
Retail Merchandising System
·
Retail Invoice Matching
·
Retail Data Warehouse
·
General Ledger
·
Other systems like ACH, NPC,
UAR, etc
·
Imports
o Sales
§ Sales and other
related transactions from store and customer order management systems
§ Includes
header, customer, items, discounts, tax, tender
§ Forms the basis
of all data exported from ReSA
o Totals Adjustment
§ Adjustments of
totals from external systems like UAR and GL
§ The totals will
be updated into ReSA in a separate HQ level table
·
Exports
Receiving
Application
|
Data
|
Remarks
|
ReSA
Batch
|
Retail
Merchandising System (RMS)
|
•
Sales
•
Returns
•
Exchanges
•
Other transactions detail
|
•
Inventory related transactions like sales, returns and
exchanges are extracted from ReSA
•
Transactions posted to tran_data
•
SOH of items updated based on these transactions
|
SAEXPRMS.PC
|
Retail
Invoice Matching (ReIM)
|
•
Invoice number
•
Vendor number
•
Payment reference number
•
Proof of delivery number
•
Payment date
•
Paid indicator
|
•
Support invoices from direct store delivery and paid out
at store
•
Escheatment of unredeemed gift certificates/vouchers paid
as income to issuing retailer
•
This data will be used to create invoices in ReIM
|
•
SAEXPIM.PC
•
SAESCHEAT.PC
|
Store
Inventory Management (SIM)
|
•
Sales
•
Returns
|
||
Account
Clearing House (ACH)
|
•
Bank deposit Totals
•
Store Day deposit Totals
|
•
Bank deposit totals for each store day is extracted from
ReSA
•
ACH uses this info to transfer deposits from the store's
bank account to the consolidating bank account
•
This data is used to calculate estimated deposits for next
day
|
•
SAEXPACH.PC
|
Retail Data
Warehouse (RDW)
|
•
All sales
•
Returns
•
Exchanges
•
Paid-ins
•
Paid-outs
•
No sales
•
Voids
•
Post voids
•
Store conditions (weather, traffic, and so on)
•
Transaction tender details
•
Store level Totals
•
Cashier or register over or short
•
Totals
|
•
All inventory and cash transactions are extracted from
ReSA
•
Includes sales, returns, exchanges, paid in, paid out, etc
•
Data loaded to RDW into daily tables
•
Aggregated to weekly and monthly levels
•
Used for reporting
|
•
SAEXPRDW.PC
|
Universal
Reconciliation System (UAR)
|
•
Lottery sales
•
Bank deposits
•
Money order Totals
•
Credit card Totals
|
•
Totals of specific tender types are extracted from ReSA
•
This data is sent to ReconNet (formerly UAR) for
reconciliation
|
•
SAEXPUAR.PC
|
General
Ledger (GL)
|
•
Sales transactions
•
Tenders
•
Paid-ins
•
Paid-outs
•
Taxes
|
•
Sales totals created in ReSA and mapped to GL Accounts
•
Total information is extracted from ReSA
•
The respective credit & debit entries are made for
each total in GL
|
•
SAEXPGL.PC
|