Oracle Retail Sales Audit Consolidated Frequently Asked Questions (FAQ's from multiple Oracle Retail Sources):

Welcome all ReSA Fans. Here is the complete set of Oracle Retail Sales Audit Frequent Asked Questions with all their answers. These are compiled from different Oracle Retail sources. So, no need to look into multiple places any more. (Do share your comments or if you have any further questions)

1.     What is significance of "Home Store" indicator in ReSA? Explain in detail?

If a store employee is assigned a home store in the Employee Maintenance form, the Store Day Find form will display only records for that given store when Edit/View is selected from the action box. To view all the stores assigned to the user in ReSA (the Employee Maintenance form), click the refresh button and select the Assigned Stores radio button. 

2.     How to populate "sa_export_options" using RESA User Interface (UI) ?

sa_system_options is not updated from RESA UI but is populated at time of installation by running the script sa_system_required.sql

3.     How can user know whether it is a single or multilevel audit scenario? Is there any indicator or value exist for this?

This information is maintained on the Store Data window of the Store Maintenance form in the Retail Merchandising System (RMS). The multi-level audit settings for individual stores are stored on the sa_store_data table. Stores are defined as multi-level audit stores when the system_code field is ‘MLA’and the imp_exp field is ‘I’ for a specific store. If these settings are not defined for a store, then ReSA defines the store as a single level audit store. Single level audit would be defined if the user chooses ‘Point Of Sale (POS)’ as an import for a given store.

4.     What condition should exist for store based on which saexpgl batch picks up the data to "STG_FIF_GL_DATA" table?

SAEXPGL exports totals to Oracle Financial Application general ledger for:
· Sales transactions
· Tenders
· Paid-ins
· Paid-outs
· Taxes

This export batch module works by first getting the store day. Then it finds the totals to export and verifies that individual totals contain no errors for either the store/day (if store/day is the unit-of-work defined by the user) or store/day/total (if store/day/total is the unit-of-work defined by the user). Next the module looks at the SA_FIF_ORA_CROSS_REF table to determine which Oracle account ID (Cost Center ID) it needs to export to. Determine if the selected store/day/total has been previously exported; if so, reverse the previous posting, and post the latest adjustments to the General Ledger (GL) staging table. Finally it moves the data to the Oracle staging GL_INTERFACE. (No RMS staging table is involved.)

5.     How to find out what roles a user have, and if those roles have the correct privileges?

To verify whether a user was granted a privilege, or a role was granted a privilege, you can use the following (for this example, RMSDEV101A is used):

Check to see if the user was granted the system privilege directly:

SQL> desc dba_sys_privs

SQL> select * from dba_sys_privs where grantee = 'RMSDEV101A';

If the result for the above query does not show the system privileges granted for the user, check to see what roles the user was granted, and then check to see what privs has been granted to that role:

SQL> desc dba_role_privs

SQL> select * from dba_sys_privs where grantee = 'DEVELOPER';

Whenever you want to see what roles and system privileges a user has and what system privileges are assigned to specific roles, you should look at the dba_role_privs and dba_sys_privs views.

From a ReSA perspective a user should have the following privileges to create totals and views:

Create any view
Create any procedure
Alter any procedure
Drop any view
Drop any procedure

6.     On the System Option form, in Retail Sales Audit (ReSA), there are two fields in the Compare Store Criteria block, Date to Determine Comp. Store Status and No. Elapsed Days to Determine Comp. Status. What are these fields used for in ReSA?

These are used for the Flash Sales reports – enabling the business to determine when a store has sufficient trading history. The date to determine comp. Status can be based on three factors: store open date, remodel date, or acquire date. These dates are held on the store table in the Retail Merchandising System (RMS).

     

7.     Does only Point of Sale (POS) data come through Retail Sales Audit (ReSA)?

Stock Counts, Receipts, Transfers and Inventory Adjustments are not sent through ReSA. These types of transactions can either be supported by Retail Store Systems (RSS) or loaded directly into the Retail Merchandise System (RMS) from the POS. Transfer Ins, Transfer Outs, Receipts, and Return to Vendor (RTVs) can be loaded directly into RMS using upload files.

Main transactions processed by ReSA are the following:

·      Sales

·      Returns

·      Exchanges

·      Voids

·      Post Voids

·      Paid In / Paid Out

·      No Sales

·      Pull

·      Loan

·      Open

·      Close

·      Fuel Transactions (e.g. Tank Dips, Meter Readings, etc…)

NOTE: Not all POS systems support all of these transactions.

8.      What does the ‘DCLOSE’ transaction signify in Retail Sales Audit (ReSA)?

ReSA requires the ‘DCLOSE’ transaction in both multi-level and single level audit scenarios. This transaction indicates to ReSA that all Point of Sales (POS) transactions have been received. Once the ‘DCLOSE’ transaction is received, ReSA will not accept any more RTLOGs for the given store/day.

9.      What happens when Retail Sales Audit (ReSA) receives the ‘DCLOSE’ transaction in both a multi-level and single level audit scenario?

When the ‘DCLOSE’ record is received from the Point of Sale (POS) in a multi-level audit scenario, ReSA updates the Data Status to ‘Fully Loaded’. If errors are found, ReSA sets the Audit Status to ‘Store Errors Pending’. If no errors are found, ReSA sets the Audit Status to ‘Audited’ and Store Status will be Worksheet.

 When the ‘DCLOSE’ record is received from the POS in single level audit scenario, ReSA updates the Data Status to ‘Fully Loaded’ and the Store Status to ‘Closed’. If errors are found, ReSA sets the Audit Status to ‘HQ Errors Pending’. If no errors are found, ReSA sets the Audit Status to ‘Audited’.

10.   How does Retail Sales Audit (ReSA) know that a store will be trickle polled?

There is no setting in ReSA to define if a store will be trickle polled. The only requirement is to send a ‘DCLOSE’ transaction to ReSA at the end of the day. If a Retail Transaction Log (RTLOG) is loaded but does not contain the ‘DCLOSE’ transaction, the Data Status is updated to ‘Partially Loaded’. If the ‘DCLOSE’ transaction is received the Data Status is updated to ‘Fully Loaded’.

11.    The sagetref.pc program, in Retail Sales Audit (ReSA), creates a “credit card mappings” file. What is in this file?

Sagetref.pc read the sa_cc_val and writes to the credit card mappings file. This table is used to maintain information for credit card validation. This table holds the prefix range, credit card number length, and type of validation (modulus 10 or none) for a specific credit card.

This table is maintained by the DBA or IT Group. There are no front-end screens.

12.   Does Retail Sales Audit (ReSA) have its own SKU/UPC tables that are populated by sagetref.pc?

ReSA does not have its own SKU/UPC tables. Sagetref.pc creates a number of output files from different RMS tables such as ITEM_MASTER, VAR_UPC_EAN etc and the saimptlog.pc batch program reads the files to perform field level validation. Some examples of these output files are a list of all valid SKUs, UPCs, promotions, codes, tenders, etc…

13.    What exactly does the saimptlogfin.pc batch program do in Retail Sales Audit (ReSA)?

Saimptlogfin.pc creates a balance group entry (SA_BALANCE_GROUP table) for the store/day, cancels post voided transactions, updates the Data Status to ‘Fully Loaded’ if the ‘DCLOSE’ transaction is received (sets Store Status to ‘Closed’ if single level audit). It also removes missing transaction records that are now present due to loading data out of sequence.

14.   Does the savouch.pc program in Retail Sales Audit (ReSA) perform validation on vouchers?

Yes, this program performs minimal validation (voucher amount is numeric). The validation does not check if a voucher has previously been redeemed. If this is necessary, a rule will need to be defined in ReSA to perform the validation.

15.    Can sales and returns occur on the same transaction record in Retail Sales Audit (ReSA)?

Yes, ReSA identifies a status at the item level (item_status field on the SA_TRAN_ITEM table).

The three statuses are Sale, Return and Void. A Sale or Return transaction can contain any variety of items with different statuses; however, ReSA identifies a Sale transaction as net money being paid into the till and a Return transaction as net money being paid out of the till. ReSA identifies an Exchange transaction when no money changes hands.

NOTE: The transaction type is held in SA_TRAN_HEAD while the individual transaction type is recorded in the SA_TRAN_ITEM table.

16.   On page 1 of the Totals Calculation Definition Wizard in Retail Sales Audit (ReSA), there is a question “Is this total a component of the Over/Short or Miscellaneous total?” Is this only used to define which total screen the defined total will appear?

Yes, this is only used to define the form the total will be displayed on, the Over/Short or the Miscellaneous Totals form.

Auditors use Over/Short Totals to perform store balancing (tender balance). In this screen there are 2 sections:

Accounted For – These totals represent what is physically counted in the till. (e.g. total cash on hand, total checks, etc…)

Accountable For – These represent what should be in the till (e.g. Sales, Returns, etc...). These totals can either come from the Point of Sale (POS) or ReSA can calculate these totals.

Auditors use Miscellaneous Totals for other types of analysis (e.g. loss prevention totals), which can have no relevance to the Over/Short screen.

17.    On the top of page 2 of the Totals Calculation Definition Wizard in Retail Sales Audit (ReSA), there is a question “Does this total correspond to a value in POS?” Where will the total value be displayed when this is set to ‘Yes’ or ‘No’?

If this is set to ‘Yes’, it indicates the total is expected from the Point of Sale (POS) such as a Declared Tender Total; therefore, the value will be displayed on the POS Reported column.

If this is set to ‘No’, it indicates the ReSA will calculate this value based on either raw data or existing totals; therefore, the value will be displayed on the System Calculated column.

18.   On the bottom of page 3 of the Totals Calculation Definition Wizard in Retail Sales Audit (ReSA), there is a list box for Display Group and a Display Group Details button. What tables hold this information and what purpose do they serve?

The Display Group list box defines in what order the total will be displayed on the defined form in ReSA. This information is held on the sa_total_head table in the display_order field.

The Display Group Details button displays all other totals that are defined to be displayed on the same form, total category and share the same display group setting for the given total. If more than one total has the same display order, then they will be displayed alphabetically. This information is not held on one specific field, but rather queries multiple fields on the sa_total_head table.

19.   What is the difference between a Total and an Audit Rule in Retail Sales Audit (ReSA)?

Totals in ReSA can either come from the Point of Sale (POS) or other external systems via the Retail Transaction Log (RTLOG). ReSA can also calculate the total itself based on raw transaction data or existing totals. Totals are used in performing store balancing (over/short), analysis within ReSA and exporting to existing transactions as well as totals. If a rule fails, the system generates an exception i.e; the retailer defines if the error can be overridden as well as what systems the error impacts. This system impact prevents a bottleneck in the export process by only prohibiting the export of the transaction or total to the systems defined in the system impact. Even if the transaction or total is in error, ReSA will still export it to other systems as long as they are not defined in the system impact for the given error.

20.   What is the difference between a table driven audit and a sub program driven audit in Retail Sales Audit (ReSA)?

Table driven audits are created using the Rules Calculation Definition Wizard in ReSA while sub program driven audits are hard coded audits such as the validations included in saimptlog.pc (e.g. card number validation).

21.   What is the “Record Type” list box used for in the Rules Calculation Definition Wizard in Retail Sales Audit (ReSA)?

The selection in this field determines the parameters and realms that must be associated with the rule. For example, if Transaction Tender Level Error is selected as the record type then the system will automatically default the sa_tran_head and sa_tran_tender tables as the necessary realms.

This is a way of supporting the creation of rules, by defaulting the realms foraround this and therefore no new Record Types can be added to the code_detail table.

22.   I am an HQ auditor, and I want to start working on a store day, but I can't get into the form in Edit Mode. What should I do?

In a multi-level environment, only the store employees can close the day - and this must be performed prior to HQ Auditing the store.

23.   What are the definitions of the different Store Status options on the Store Day Find form in Retail Sales Audit (ReSA)? What triggers a Store Status to change to Close? At what Store Status can the store/day be deleted?

Worksheet – When a store is defined as a multi-level audit store (both store employees and HQ auditors will use ReSA), the store is loaded into ReSA in a Worksheet status. The store/day can be deleted in this status.

Fuel Closed – This status is only used for fuel stores that will be exporting to Oracle Site Fuels Management (SFM). Stores are loaded into ReSA in a Worksheet status; however, the audit cannot begin until the day is closed in SFM first. When this is done, ReSA updates the status to Fuel Closed. The store/day can be deleted in this status.

NOTE: SFM will send back a file signifying that the store transactions have been processed and reconciled. This will signify Fuel Closed – and enable the data to be worked on in ReSA.

Closed – In a multi-level audit scenario, the store employee must close the store day once all errors have been corrected or overridden. This updates the store status to Closed and makes the store/day available to the HQ auditor. In a single level audit scenario where only HQ auditors are using ReSA, the store is loaded into ReSA in a Closed status. The store/day cannot be deleted in this status

24.   What is the difference between Accountable For and Accounted For totals on the Over/Short form in Retail Sales Audit (ReSA)?

Accounted For – These totals represent what is physically counted in the till. (e.g. total cash on hand, total checks, etc…). These totals are expected from the Point of Sale (POS) or some other external system.

Accountable For – These totals represent what should be in the till (e.g. Sales, Returns, etc...). These totals can either come from the POS or ReSA can calculate these totals.

25.   When are vouchers items and when are they tenders?

Vouchers are items when they are sold to a customer. Vouchers are tenders when they are redeemed by a customer for merchandise.

26.   What is the purpose of the Item Summary and Tender Summary forms in ReSA?

The Item Summary form serves three purposes:

·         It is used to search for all the transactions where the specific item exists for a given store/day.

·         The u specific transaction and view the details on the Transaction Detail form.

·      It is used to perform a mass item change for a specific item number to another valid item number for a given store/day.

If ReSA generates an invalid item number error, the retailer can create the specific item in the Retail Merchandising System (RMS) and use this form to re-validate the item number. Since the item now exists in RMS, ReSA will remove all invalid item number errors for the selected item for the given store/day.

The Tender Summary form is used to search for all the transactions where a specific tender type group was used for a given store/day.

27.   What is Escheatment?

Escheatment is the process of forwarding monies of outstanding, non-expiring vouchers to the proper governing authorities after a defined period of time from the date of issuance.

28.   How does a retailer turn this functionality off if they do not practice escheatment?

If a retailer does not want to use the escheatment functionality they will not check the Escheatment indicator on the System Options form. Since the retailer will not be escheating these monies to governing authorities, the retailer will claim these monies as income; therefore, they must define when these outstanding vouchers will be claimed as income on the Voucher Options window on the System Options form. The retailer still must run the saescheat.pc program in order to claim these vouchers as income.

29.   On the Escheatment window located on the System Options form, in ReSA, there are four indicators (Issue, Recipient, Store and HQ). What is the purpose of selecting these indicators, especially since all four may be set to yes simultaneously?

In the United States, each state has different escheatment rules. We have created a level of hierarchy in ReSA for escheatment rules (Store, Issuer, Recipient and HQ). When defining each escheatable state or country, the retailer will indicate how many levels of the hierarchy will be checked.

The voucher must be beyond the escheatable number of days (SA_VOUCHER_OPTIONS.ESCHEAT_NO_DAYS). The program will check if a state or country exists for the first level of the hierarchy, the Store. If so, it will check if this state or country is defined in the Escheatment Options and whether or not the Store indicator is set for that partner. If a match is found, ReSA will escheat the voucher amount to the state or country of the Store.
If no match is found at the store level, the program will perform the same checks as outlined above through the entire escheatment hierarchy until a match is found. If no match is found, the retailer will claim the voucher as income.

30.   How are the escheatment rules determined in ReSA? Are they determined by the state where the voucher was sold or the state where the recipient of the voucher resides?

The Escheatment functionality in ReSA is driven by the hierarchy of Store, Issuer, Recipient, & HQ. When performing escheatment, ReSA checks if there is an address for the first level of the hierarchy, the Store. If so, it checks if the state or country is defined in the Escheatment table in ReSA. If the state or country exists on the table it then checks to see if the Store indicator for that state or country is set to yes. If so, ReSA escheats these monies to that state or country. If no state or country exists for the store, or one does but does not exist on the Escheatment table, or it does exist on the table but the Store indicator is set to no, then ReSA checks the next level, Issuer, and so on. If no matches are found after ReSA checks all four levels of the hierarchy, then the retailer claims the voucher as income.

If claimed as revenue, this is a non-merchandise type of invoice.

An entry in the partner table (escheatment authority be defined to indicate to whom the monies will be sent.

If a voucher is redeemed after it has been escheated, it is the responsibility of the retailer to collect these monies from the government

Only the outstanding voucher amount is escheated.

31.   Does the SAESCHEAT.pc batch program in ReSA create a file or does it insert into the Retail Invoice Matching (ReIM) tables? If this populates the tables, what will happen if the retailer does not have ReIM?

SAESCHEAT.pc only creates a total and writes this to the SA_ESCHEAT_TOTAL table. The SAEXPIM.pc program reads from this table. This program is owned by ReIM.

If the retailer does not have ReIM, then a custom interface would need to be written to read the SA_ESCHEAT_TOTAL table to export the data into the retailer’s appropriate application

. .

32.   If the retailer does not perform escheatment, can the SAESCHEAT.pc program in ReSA be used to claim these outstanding vouchers as income?

Yes, the SAESCHEAT.pc program will support this function. If the retailer does not perform escheatment, they should not check the Escheatment Indicator on the System Options form. If this indicator is not set, the Escheatment button is disabled; however, the Vouch. Opt. button is still enabled. The retailer would click this button to open the Voucher Options window. In this window, the retailer will define in the first column when the voucher will be claimed as income (the number of days after issuance). In the second column, the retailer will define when the voucher will be purged from ReSA (the number of days after claiming the voucher as income). The retailer will define this criteria for each Tender Type ID assigned to the Tender Type Group of ‘Voucher’.

33.   Is there a complete standard output of data for archiving data in ReSA?

Transactions can be exported to Retail Data Warehouse (RDW) for reporting. Transactions can be stored online in ReSA for current usage or purged when they are no longer needed.

34.   What triggers the system to know ReSA can start exporting?

No special event in ReSA triggers the system to start exporting, rather the retailer defines when the export programs will run in the batch schedule. When these programs run (i.e. saexprms.pc, saexprdw.pc, saexpgl.pc, etc…), they check errors impacting that export exist for a store day. If not, then the data will be exported. The Retail Merchandising System (RMS) is the only system that ReSA will export some ‘clean’ transactions even if errors impacting RMS exist in other transactions. This is dependent on the Unit of Work setting in the System Options for ReSA. When set to Store, all RMS errors must be corrected or overridden for a store/day before any data is exported. When set to Transaction, only clean transactions are exported to RMS even when other RMS errors are outstanding. In later releases of ReSA 10.1.x, it can now export some ‘clean’ transactions to RDW (Retail Data Warehouse) just like how it is doing it for RMS.

35.   Does ReSA accept and process DSD (direct to store delivery) transactions?

ReSA only supports the DSD scenario when a payment is made by the store to the vendor. ReSA requires that these payments be entered in the Point of Sale (POS) or other Retail Transaction Log (RTLOG) compatible systems as ‘Paid Out’ transactions. Included in this Paid Out transaction are the following data:

·      Amount paid

·      Vendor ID

·      Invoice Number

·      Proof of Delivery Number

This information is loaded into ReSA and exported to Retail Invoice Matching (ReIM).

The retailer is also required to create an entry for the DSD in the Retail Merchandising System (RMS) using the Quick Order Entry screen. On this screen they will enter the vendor, invoice number or proof of delivery number, and indicate if the vendoe store. RMS will export this transaction to ReIM as well. ReIM will match this RMS transaction with the ReSA transaction to verify a payment was made. This invoice will then be exported to the retailer’s AP financial system with a ‘Paid’ indicator.

NOTE: ReSA does not handle the actual receiving of the stock. This has to be done using either RMS interfaces or Retail Store Systems (RSS).

NOTE: Not all financial software applications can receive payment history from other applications.

36.   Does the Retail Merchandising System (RMS) batch program POSUPLD.pc still perform the same validation routines if ReSA is installed?

If ReSA is on (system options.sales_audit_ind set to ‘Y’), some information is taken from the ReSA file rather that being looked up; however, quite a bit of validation is still performed. Value Added Tax (VAT) and currency information is not validated (on store and currencies). Merchandise hierarchy is not validated.

37.   According to the Operations Guide, the SAEXPRMS.pc program in ReSA rolls up sales by Item/store/day/price point level prior to exporting to the Retail Merchandising System (RMS). Does this export provide visibility to both sales and returns in this file?

ReSA sends one file with two Transaction Heads, one for Sales and one for Returns. ReSA uses the item status to determine if the item was sold or returned.

38.   What is Automated Clearing House (ACH)?

ACH is a United States based banking network used to electronically transfer funds. The National Automated Clearing House Association (NACHA) regulates this network and maintains the standards.

39.   What is the ACH estimation formula?

The ACH program estimates the following day’s bank deposit by averaging the previous four actual bank deposits for that given day of the week (e.g. the estimate for Monday will take the average of the actual bank deposit from the previous four Mondays). If the system only has three weeks of data, it will use the average of the previous three Mondays. If only two weeks of data exists, it will use the average of the previous two Monday. If only one week data exists, it will use the bank deposit from the previous Monday. If less than one week of data exists, it will use the previous day’s deposit and if no data exists, no estimate will be created.

The system also compares yesterday’s estimate for today’s bank deposit against today’s actual bank deposit. It will add the difference to the average for tomorrow to create an Estimated Deposit Adjustment if necessary.

40.   Why does ReSA send bank deposit estimates to the ACH?

ReSA is estimating the following day’s bank deposit and sending this amount to the consolidating bank via the ACH network. This is used to hedge the cash that is to be received from the stores.

If the retailer does not perform this function, they do not need to run the batch program (saexpach.pc). There is no impact to ReSA if this is not run.

41.   What is the Store ACH Maintenance form used for in ReSA?

This form is used to view the ACH data (Manual Deposit Adjustment, Estimated Deposit, Estimated Deposit Adjustment) for a specific store.

The retailer can also make a Manual Deposit Adjustment to the following day’s ACH Amount if they feel the actual deposit will be different than the estimated deposit for the following day. The Add button is used to enable the apply block to enter a Manual Deposit Adjustment for a store/day (e.g. Christmas time when the anticipated sales are greater than the average for the last four weeks). This Manual Deposit Adjustment must be made prior to the execution of the ACH program.

The sum of the Manual Deposit Adjustment, Estimated Deposit and Estimated Deposit Adjustment produces the ACH Amount for the store/day. This is the amount that will be sent via the ACH network to the consolidating bank.

42.   What happens in ReSA if the cost method of accounting is used?

The cost of sales is not used in ReSA - only revenue is captured.
Cost of sales information will still be determined in the Retail Merchandising System (RMS), not in ReSA.

43.   How can the count or number of transactions for a specific transaction type (e.g. SALE) be viewed in ReSA?

The count or number of transactions is not contained in the total of sales or returns. This information can be utilized in the Store Operations Workbench of the Retail Data Warehouse (RDW).

44.   If the flash reports in ReSA compare current sales against last year’s sales, where is this data obtained if the ReSA purge parameter is set to 30 days?

This information is stored in the SA_FLASH_SALES table. The SAPURGE.pc batch program deletes these records when they are 730 days old + the parameter defined in the ‘Number of Days Before Purge’.

In the SA_FLASH_SALES table, there is a column named NET_SALES_SUSPENDED. This column refers to the sales that were still in “suspend mode” at the time of closing the Point of Sale (POS). These sales are not classified as true sales, as there has been no final exchange of goods and money between the retailer and customer. These salesare not exported to the Retail Merchandising System (RMS) or Retail Data Warehouse (RDW).

45.   If a store is trickle polled, can the Flash Totals Report in ReSA still be generated during the day prior to receiving the ‘DCLOSE’ record in ReSA?

Yes, but the system must be set up to calculate totals prior to receiving the DCLOSE transaction. The batch schedule needs to be defined to run the totaling procedure during the day upon import of an RTLOG file.

46.   Is the SAPURGE.pc program of ReSA multi-threaded and restart/recoverable?

Yes, it is multi-threaded and restart/recoverable.

47.   When using the Delete Store Day functionality, will the Store/Day record be deleted or just the transactions for that Store/Day?

When deleting a Store/Day, it is actually the transaction records for the store/day that are deleted rather than the store/day record. SA_TRAN_HEAD.STATUS will be set to 'D' and these transactions will not be accessible through the Transaction Maintenance screen.

48.   When loading Retail Transaction Log (RTLOG) files, how will this affect the tdup* file?

When the ReSA System Option to check for duplicate/missing transactions is enabled, tdup* file is used to keep track of the transaction numbers (tran_no) used. When a RTLOG file is uploaded then a tdup* file is created. It is created for a Store (i.e. tdup.dat). When some new RTLOG file is uploaded for this store then the tdup* file will be appended. Note that tdup* file is created for a Store not for a Store/Day. It is never deleted through any batch. Depending on the Store's "Unique Tran.No. By" setup - Register or Store; tdup* file contains the Store/Day, Register and the Transaction number information. It ensures that no duplicate transaction can be loaded.

49.   What is the meaning of fields ROLL_UP_LEVEL 1 , ROLL_UP_LEVEL 2  and ROLL_UP_LEVEL 3 in ReSA GLCross Reference screen 'Saglcros.fmb'? 

Total - Roll Ups :

Roll-ups allow a total to produce multiple values, it is equivalent to the part of the GROUP BY clause in the SQL SELECT statement that combines any rows that would have duplicate values in the
grouped by field

For example :

SELECT count/sum(Value to be Totaled)
FROM realms
WHERE restrictions + joins
GROUP BY roll ups

50.   What are the possible ROLL_UP levels for the Total defined on register level and Store level totals? 

In the Total Definition wizard > Roll Ups screen
The Roll Ups screen allows the user to define the parameter that will actually be counted (or summed). It also allows the user to define up to three parameters to group by. Only parameters that have previously been added to the total definition can be either counted (summed) or grouped by.

If the total is at the store level, meaning the user only wants one value per store, the system automatically assumes a roll up to the store/day level. If the total was defined to be at the cashier/register balancing level, and the user wanted one total value per cashier/register, the system would assume a roll up to cashier/register.

51.   Is the GL amount equal to the basic level amount or rolled up level amount or some other amount?

According to RMS Data Model, the table SA_FIF_GL_CROSS_REF is used to associate an Oracle account code with the different types of totals that can be calculated by Sales Audit. Different account codes can be associated with the same total type at different locations and different levels of rollup. And also the 3 roll ups in this table are the same as rollup levels of total.

There is one more table FIF_GL_ACCT , which holds all the account related information, which will be bridged across from the respective Financial Application.

52.   Should the V_TOTAL_TOTALNAME table be used instead for SA_SYS_VALUE (for calculated totals) for getting the most recent value for a totals on a store/day?

No, V_SA_TOTAL_VALUE view should be used to get the latest Total Value.

53.   What is the difference between V_SA_TRAN_ITEM_NET_ROLLED and V_SA_TRAN_ITEM_NET and when would each be applicable?

i. The V_SA_TRAN_ITEM_NET_ROLLED gives the Retail price of one Item after all the Discounts applied to it.
ii. If one Item is having two discounts, the V_SA_TRAN_ITEM_NET_ROLLED view will show a single line for that item.
iii. Also V_SA_TRAN_ITEM_NET_ROLLED will have the total discount applied to it.
iv. On the other hand V_SA_TRAN_ITEM_NET will show two different lines each for one discount.

54.   Can we post void transactions in RESA (Retail Sales Audit) by changing the value of transaction type from 'Sales' to ‘Void’?

No, if it is required to void a sales transaction from the front-end, then this should be done using the post-void functionality in Transaction Detail screen (trandetl.fmb) and not by changing the TRAN_TYPE for the transaction.

55.   Why post voided transactions from ReSA forms are not picked by SAEXPRMS?

When doing a Post Void on a transaction from ReSA forms and running SAEXPRMS batch, the transaction will only be picked up if it is already exported. But if it is post voided before export, it basically means it is canceled and no data need to be sent to RMS for this transaction.

56.   How different discounts (in-store discount, employee discount, promotions) are maintained in Retail Sales Audit (ReSA) And Retail Merchandising System (RMS)? What are the transaction codes are used to maintain these transactions?

In ReSA, all the discounts are maintained in SA_TRAN_DISC table using the following codes:
Code Promotion Type
--------- ----------------------------
1004 - In Store Discount
1005 - Employee Discount
1006 - Off-retail (selling retail is less than actual retail of an item without discount)
9999 - RPM Promotion

In RMS, the following transaction codes are used to post different transactions to TRAN_DATA table:

Code Purpose
-------- -------------------------------------------
1     -  Sale transaction excluding VAT
2     -  Sale transaction including VAT (written only if system_options.stkldgr_vat_incl_retl_ind = Y)
4     -  Return transaction
15   -  In Store, Promotion discounts
60   -  Employee discount

Example:

Let's say:
Regular retail price = 15
Selling retail = 12 --implies an in-store discount of 3
promotion discount = 4
Employee discount = 1
Units sold = 2

In RMS, TRAN_DATA table will have the following entries for the above transaction:
TRAN_CODE UNITS TOTAL_RETAIL
---------------- ----- ------------
1                       2         14 --for sale
15                     2           6 --for in-store discount
15                     2           8 --for promotion discount
60                     2           2 --for employee discount

For the above sale transaction,
Total Retail = Units * (Original Retail - In-store disc. - promotion disc. - employee disc.)
                  = 2 * (15 - (15 - 12) - 4 - 1) = 2 * 7 = 14


In ReSA, SA_TRAN_DISC table will have the following entries for the above transaction:
TRAN_SEQ_NO RMS_PROMO_TYPE QTY UNIT_DISCOUNT_AMT
------------------ ------------------------- ----- -----------------
1                          1004                                 2           6 --for in-store discount
1                          9999                                 2           8 --for promotion discount
1                          1005                                 2           2 --for employee discount

Why the giftcard sales (associated with an item) are reaching Retail Sales Audit (ReSA) tables but are not being written to Retail Merchandising System (RMS) TRAN_DATA_HISTORY?

The expected functionality is:

1.   Once gift card is sold, data is populated in SA_VOUCHER table

2.   Once gift card is redeemed, it gets knocked off.

3.   The gift cards which are not redeemed, goes to the government through the escheatment process

4.   Gift card sale is not expected to go to Retail Merchandising System (RMS). So it will not be written to posu file and thus will not be populated into RMS

57.   Is it possible to export the data from Retail Salues Audit (ReSA) to General Ledger (GL), with the total amount for each type of credit card (segregated by bank)?

For example, for a sale transaction, following are the tender type vs. amount paid:
Amex card in Icci Bank = 1000
Amex Card in UTI bank = 2000

This functionality can be achieved by setting up the following in ReSA:
1. Create different tender types for different bank's credit cards.
2. Define Totals in ReSA based on the tender type (Action > Sales Audit > Control >
Totals Definition Maintenance).
3. Map GL account to the above Total definition.
4. Run SAEXPGL batch which exports data from ReSA to GL based on Total definition.

58.   Retail Sales Audit (ReSA) is a two level auditing system by default. It requires Store level and Headquarter level auditing.
How can it be changed to make ReSA only Store level auditing system?

Single level auditing/Multilevel auditing can be configured through Store Data settings that can be accessed via the Options tab on the Store form.
Stores are defined as Multilevel audit stores when the user adds ‘MLA’(Multilevel Audit) as a import data mode for a specific store.
Single level audit would be defined if the user chooses ‘Point Of Sale (POS)’ as an import mode for a given store and does not add MLA as an import option.

In a single level audit scenario, the system doesn’t restrict auditing privileges to employees of either type – store or HQ. However it is advisable to create a central auditing pool comprising of HQ employees (or Manager Level Store Employee) to ensure tighter audit control.

59.   Consider a transaction T1 with two items. Item 1 - Rs13.30 and Item 2 - Rs 12.10
Now the Transaction header value will be automatically calculated as Rs. 25.40. However, the
tender type amount will be Rs. 25 This will lead to transaction out of balance.How will this be handled?

The rounded off amount needs to be calculated per store day and sent to financials.How can we
achieve this?

Tender rounding issue can best be explained by an example: The customer buys a set of items
totaling 64,40 INR. The smallest payable amount is 0.5 INR. The customer therefore pays 64.50 INR.
ReSA does not accept that the Total Tendered does not match the Total Sales.

The currency rounding does not update the data, it just change the logic when validating
TRAN_OUT_BAL. SA_TRAN* data will still have 13.30 and 12.10 as the sales for the item and 25.10 as
the tender (from the Point Of Sale (POS) system / Retail Transaction Log (RTLOG)).

Rounding logic must work with how the tender type is defined in Tender Type Window (posttyp) “you can specify that the payment with this tender type must be the exact amount of the purchase” .If the tender type is defined with “Exact Change” checked, this rounding logic would override what is specified in posttyp.

Financially the overall discrepancy (Sales-Tender) is handled externally in General Ledger (GL) (both totals will be exported to GL, and the discrepancy resolved with manual or automatic GL postings, and the discrepancy is considered as part of the cost of business.).

In ReSA, The rounding rules are Applicable only on Total of sale amount. Not on the Tender amount.

The Rounding rules are:

Min Range	Max Range	Round Off To
0.0	0.4999	0
0.5	1	1

eg:

RTLOG File,

THEAD00000001704150 201012042354000000003018c1 201414 SALE P000000000000089400000001
TITEM0000000171S ITEM ITEM 100330171 00000000000000000000000002302 5 5P000000010000EA 00000000000001090000 00000000000000000000N Y 201414 N000000010000
IDISC00000001729999 6CNDPRC P00000001000000000000000000345000 000000010000N 2
IDISC00000001731004 HDRDIS P00000001000000000000000000186300 000000010000N
TITEM0000000174S ITEM ITEM 100216191 00000000000000000000000002302 5 5P000000010000EA 00000000000001090000 00000000000000000000N Y 201414 N000000010000
IDISC00000001751004 HDRDIS P00000001000000000000000000186300 000000010000N
IDISC00000001769999 6CNDPRC P00000001000000000000000000345000 000000010000N 2
TITEM0000000177S ITEM ITEM 100364451 00000000000000000000000002302 5 5P000000010000EA 00000000000001090000 00000000000000000000N Y 201414 N000000010000
IDISC00000001789999 6CNDPRC P00000001000000000000000000345000 000000010000N 2
IDISC00000001791004 HDRDIS P00000001000000000000000000186300 000000010000N
TITEM0000000180S ITEM ITEM 100287328 00000000000000000000000002302 5 5P000000010000EA 00000000000001090000 00000000000000000000N Y 201414 N000000010000
IDISC00000001811004 HDRDIS P00000001000000000000000000186300 000000010000N
IDISC00000001829999 6CNDPRC P00000001000000000000000000345000 000000010000N 2
TTEND0000000183CASH 1020 P00000000000002240000 0000000000000000000000000
TTAIL00000001840000000013


Form the Above RTLOG file, the total of the Sale = 4 * 109 - { (4*34.5 ) + (4*18.63)} = 436 - {138 + 74.52} = 223.48

The tender amount is 224.

So the rounded amount for total of the Sale will be 223 ( because this fall under first rule ).

From the data, SA_ERROR.ORIG_VALUE, the column value is S: 00000000000002230000 T: 00000000000002240000

So the S: value is rounded and compared to total of the of the tender amount. Since after rounding they were not matching, system throws an Tran out of balance error.

60.   Why is a Sales Audit table (prefixed with SA_) used in standard RMS (Oracle Retail Merchandising System) functionality as Currency Rounding in Price Management?

The Currency Rounding functionality was developed for ReSA and was extended to Pricing. Thus when it was decided to apply this functionality to Pricing, we already have the ReSA rounding rules table.

61.   Is the Currency-based rounding rules only used for ReSA?

No, Price Rounding also uses Currency-based rules.

Within the System Options dialogue there is a field called 'Pricing Rules for New Items'. This is represented in the database as SYSTEM_OPTIONS.PRICING_RULE. This field determines which pricing rules should be used when creating items and price changes, promotions, and clearances within Retail Merchandising System (RMS). Valid values for this field are 'R' for Rounding Rules or 'P' for Price Point. A value of 'R' indicates that the rounding rules, specified by the user, will be used when creating an item. If the user chooses the pricing point rules, then the price point rules will be applied.

When creating a price change, promotion or clearance we can determine how the price "will look", by choosing one of the following Adjustment Types:

·      EI - Ends In

·      PP - Price Point

·      RO - Round

·      NO - None

If Round (RO) is chosen, RMS will check for the existence of rounding rules for the currency or currency and country. If they exist, they will govern the rounding. If they do not, normal arithmetic rounding will be applied. If Round is the Adjustment Type chosen, RMS will use rounding rules based on the currency chosen in System Options provided that 'R'ounding is the value for the System_Options.Pricing_rule.

If Price Point (PP) is the Adjustment Type chosen, and SYSTEM_OPTIONS.PRICING_RULE is equal to Price Point, the Price Point rules as setup by the user will apply. These rules are defined within RMS and saved at the database level.

Default RMS rounding functionality dictates that when applying a price change in the clearance, promotion or price change forms, or during item setup, results in the price being rounded with two implied decimals (e.g. original price is 349, and with 10% off this is rounded to 314,10). Currently, RMS does not accommodate user defined rounding rules. Therefore, the default rounding functionality is used. However, with the increased use of foreign currency, there are several different rounding rules that may be used as an alternative.

Changes within RMS and ReSA have been made to implement foreign currency rounding rules.  

62.   What are the specific examples on how the currency-based rounding logic is implemented?

1.   Ability to round valid price change "ending" amounts based on currency rules.

2.   Allows for the fact that the amount in some cases rounds down to one or more fixed ending amounts, and in some cases rounds up or down to the nearest valid ending amount. Refer to the example table below.

3.   Also, the ending amount can be before or after the decimal point for the currency.

4.   Depending on a currency's lowest denomination, rounding rules will be defined.  Specific examples would be as follows (Note: these examples are not necessarily realistic, this just shows a few rules and will best explain the logic of the currency-based rounding):

Currency / Country	Ending Amount (From)	Ending Amount (To)	Rounds
CUR1 / CNT1	0.0000	0.2499	0
	0.2500	0.7499	0.50
	0.7500	1.0000	1.00
CUR1	0.0100	0.4901	0.45
	0.4902	0.7901	0.65
	0.7902	1.0000	1.00

5.   Rounding rules are defined by country / currency.  In the rounding rule definition, if a country is not specified, then the rounding rule will be applicable to all stores having that currency.  Except when there is another rule (say, rule 2) defined for that same currency for a specific country.  In this case, stores in that country will use rule 2. 

63.   In Currency Rounding Rules Detail Maintenance(SARRULDT) form, when defining the first range as 0 to 0.2600, why is it not possible to use 0.2700 as the Lowest Ending Amount for the next range?

After applying some computations, the resulting amount may come up as having 4 decimals. If rules with only 2 decimals are defined, then the 4 decimals won't be covered by any of the rules. That is, Rule 0 - 0.24 rounds to 0; 0.25 - 0.74 rounds
to 0.50 and 0.75 - 1 rounds to 1. Given 24.2455, the decimals 0.2455 does not belong within any of the defined range. The number of decimals for the Round Amount will be based on CURRENCIES.CURRENCY_RTL_DEC. For a currency defined as having 2 decimals, then it will be rounded to 2 decimals. Therefore, in the SARRULDT.FMB, the LOW_ENDING_AMT and HIGH_ENDING_AMT (for the range) have to be entered as 4 decimals.

The currency rounding rule range cannot overlap. Thus when the 1st range is 0 - 0.2700; the next range should start with 0.2701. If the first range is defined as 0 - 0.2659; the next range will start at 0.2660

64.   How to define totals/rules in Retail Sales Audit (ReSA) manually?

There are some steps that need to make sure that the manual total definition is executed at the same time as the wizard created total definitions.

User will need to use the Total Definition Wizard to do part of the definition – but on the Total Characteristics Cont. screen (satotal – page 3), answer ‘No’ to the question ‘Is the total created with the wizard’. Using the first sections of the wizard will ensure that the correct SA_TOTAL_HEAD record is created.

Naming

One must name the function according to the convention:

SA_T__

SATOTAL.PC uses the naming convention to select the correct objects on the database to run as part

of the totals process.

Function Content

Manually written functions should call SA_BUILD_TOTAL_SQL.INSERT_TOTAL to actually put the

calculated values onto the database.

View

One must create a view to represent the potential values of the total. This view should union together the sa_total and sa_sys_value tables, as well as the sa_pos_value (if the total can be POS uploaded), sa_store_value (if the total can be store user entered) and sa_hq_value (if the total can be HQ user entered). The view name must follow the convention:

V_TOTAL_

Create Metadata For the View

Even if you do not plan to use the total to create a rule, ReSA requires that you create metadata

for the view. Call the function SA_TOTAL_DEF_SQL.CREATE_WIZ_DATA to create the metadata

65.   How is Transaction level discount (i.e. slab discount) handled in RTLOG file in Retail Sales Audit (ReSA)?

If you have a scenario at Point Of Sale (POS) where in you give a discount of 100 at a Total Bill of 1000. This discount does not apply to any item but is at a Bill level.

Solution

Retail Merchandising System (RMS) does not supports bill/transaction level discounts, RTLOG file will not have a transaction-level discount (TDISC) record, so POS system should be sent item-level discount. But that does not mean ReSA only handles item-level discounts (IDISC). The slab discounts or bill/transaction level discounts are in fact apportioned amongst all the items in that bill in proportion to their prices & units sold.

If you have a scenario at POS where in you give a discount of 100 at a Total Bill of 1000. And it has two items priced differently then the transaction-level discount will be divided amongst these two items in accordance to their individual price & sold units :-

Item Price Quantity Discount

Item A 400 1 40

Item B 100 6 60

Also for Slab discounts, there may be a coupon or a pamphlet to describe this discount based on slabs.

If there is a voucher or coupon involved then the Transaction records should have an additional TTEND record to denote the discounts obtained by those coupons or vouchers for a particular transaction.

66.   If the 'Store Close Audit' menu shows the users home store automatically, then why is it not visible in the 'transaction find' screen?

While creating an employee through Employee Maintenance Screen, you can assign more than one stores to the employee.

But only one store can be selected as the Home Store of that employee. When this employee goes into Store Day Find screen through 'Store Close Audit' menu, Retail Sales Audit (ReSA) automatically queries the store/days the user must audit based on their employee type (store or headquarters) and assigned stores.

If a store employee is assigned a home store, the Store Day Find window initially displays only records for that given store.

The user can refresh the screen and select different criteria to view data for either all assigned stores or all stores.

(The ability to view all stores is restricted by location security in Retail Merchandising System (RMS).)

The user can use Store Day Summary window to view transactions from a single store and day. This way the Transaction Find window works for a single store. But the user needs to visit the screen through 'Store Close Audit' menu.

So, the Transaction Find Window through Transaction maintenance menu is not restricted to the users home store. It is used to search for transactions across multiple stores and/or days.

67.   Why is the 'Transaction Find' window through 'Transaction Maintenance' menu  not restricted to the users home store while the store / close audit screen is?

The Transaction Find Window through Transaction maintenance menu is used to create new or view, edit transactions or create post void. While the store / close audit screen is used to audit days transactions.

Thus generally the store / close audit screen by default fetches store/day for the user's home store. But as I described earlier, if the user has access to other stores also, he or she can access transactions for those stores as well.

Since the Transaction Find Window through Transaction Maintenance menu is used to create new or view, edit transactions or create post void, it does not automatically defaults to the user's home store.

But this restriction to the users home store can be achieved on both the screens if the user is restricted to his home store through RMS security option.

68.   Is it possible to restrict the ability to view all stores by location security in RMS? Is it the same restriction for the 'Transaction Find' window through 'Transaction Maintenance' menu ?

Yes, if one user is given restricted access to only his or her home store through RMS location security, he will be able to access his home store and nothing else. The same restriction holds for the Transaction Find Window through Transaction

maintenance menu. That is, if one user is given restricted access to only his or her home store through RMS location security, he will be able to access his home store and nothing else on this screen also

69.   How to perform Flash Reporting?

Solution

To be able to use Flash Reporting, follow through the steps below.

A. Run the necessary Retail Sales Audit (ReSA) batch programs to load sales transactions:

1.   Run the Sastdycr batch to create store day data in the SA_STORE_DAY database table

2.   Run the Sagetref batch to generate the data files

3.   Generate an RTLOG file with a Sale ('S') transaction and run the saimptlogi batch.

4.   Run the Saimptlogfin batch to finalize the transactions.

5.   Run the Satotals batch. The SA Totals batch provides automated totaling to identify errors and compare against  receipts.

6.   Run the Sarules batch.

7.   Run the Sapreexp batch.

8.   Store Status Check

9.   From RMS, navigate to Action > Sales Audit > Store Close Edit

10. Verify that the store for testing is Closed, Audited and the Data Status = Fully Loaded

11. Go to Options --> Store Day Summary to adjust as necessary.

12. Run Saexprms batch

13. Run Saexprdw batch

14. Run Posupld batch

B. Setup or define a Total:

1.   From rtkstrt, select Sales Audit > Control > Totals Definition Maintenance

2.   In satlfind, select New from the action drop-down list to create a new Total id for the user. 

3.   In page 1 of Total Def. Wizard screen (satotal):

o    Enter a Total id name in the Total field.

o    Enter a date in the Start Date as the first business date of data for which the total will be calculated and an End Date as the last business date of data. Make sure that the dates correspond to the business dates specified in the RTLOG file loaded earlier.

o    Select 'Sales' in the Total Category drop-down list.

o    Check the Miscellaneous as a total component.

4.   In page 2, choose 'YES' for all the characteristics of the total definition and choose 'sum' as the total representation.

5.   In page 3, select 'Store' as the balancing level and select 'YES' as the total definition will be built using the ReSA Total Definition Wizard.

6.   In page 4, populate the Selected Realms block with 'SA_STORE_DAY' from the available Realms.

7.   For pages 5 to 9, enter the required information.

8.   In page 10, add a Loc Trait in which the store of your transactions belong to.

9.   In page 11, add the Flash Totals Reporting (FLR) as one of the usages then click the Finish button.

10. Submit and Approve the Total Defined through Options > Status.

Note: Due to size constraints of the report, it is recommended that no more than seven totals be defined with the Flash Totals Report Usage.

C. View a report

1.   From rtkstrt, select Action > Sales Audit > Store/Close Audit. The Store Day Find window is displayed.

2.   Search for and retrieve a store in Edit mode. Select the store and business date specified in the rtlog.

3.   From the Options List, select Store Day Summary. The Store Day Summary window is displayed.

4.   From the Options menu, select Reports > Flash Totals Reports. (or select the Flash Sales Prior Year Comparison ? This report will show data only if previous year transactions are available to be used as point of comparison) 

5.   In Oracle Reports, enter the user password then submit.

6.   Choose 'Cache' as destype* then submit the query. Save the report file in local drive. 

7.   To view the report file: Rename the file with a .pdf extension then open in acrobat.

8.   Matching results are populated on the report

--------------------------------------------------------------------------------

70.   There are five totals displayed on Miscellaneous Totals Window (samisctl). What do they signify?

Solution

The Miscellaneous Totals window allows you to enter and maintain the totals in the system for a particular Store Day. This window allows you to enter your version of the total. This window contains a list of the Total Categories and a corresponding list of the totals that are contained in a selected Total Category. Following is the list of totals displayed on the screen:

1. POS Reported: This displays the value imported from an external system such as POS.

2. System Calculated: This displays the value calculated by Oracle Retail Sales Audit (ReSA) based on raw transaction data or existing totals.

3. Store Reported: This displays the value updated by a Store Employee of either the POS reported or System Calculated value.

4. HQ Reported: This displays the value updated by a Headquarter auditor of either the POS reported, System Calculated or Store Reported value.

5. Last Reported: This is the value that is taken from one of the 4 totals in that order- HQ Reported, Store Reported, System Calculated or POS Reported which is not NULL. If all the 4 totals are NULL, then Last Reported will be NULL.

— Share It —

• Facebook
• Twitter
• Google+
• Linkedin
• Pin It

About Nagesh Mishra

Nagesh Mishra - A Passionate Oracle Retail Certified Professional with more than 17 years of overall experience in IT industry and more than 15 years of domain expertise in Oracle Retail Applications. Worked extensively in diversified fields of Product Implementation, Business Consulting, Pre-Sales, Application Software Development, Maintenance and Support and Re-Engineering Oracle Retail projects.