| Oracle® Retail Sales Audit Cloud Service User Guide Release 19.3.000 F83171-01 |
|
 Previous |
 Next |
| Oracle® Retail Sales Audit Cloud Service User Guide Release 19.3.000 F83171-01 |
|
Previous |
Next |
A total is a summation or count of two or more entities. For example, a total can be a summation of the quantity of items sold in the store on a particular store day.
You can use the Totals Definition Maintenance module of Sales Audit to define the totals, these totals can either be imported as transactional data from the POS/OMS system into Sales Audit through a RTLOG file or calculated by Sales Audit.
For more information on importing data through an RTLOG file, see Chapter 5, "Importing Transactional Data".
Source data can include transaction data and external data, such as money order totals or lottery ticket sales. You can choose the tables and columns for the total, build query statements to yield the desired information, and associate these totals with categories, such as stores or POS values.
You can use totals for the following:
Evaluating and consolidating data
Determining whether transactions meet defined tolerances
Determining over or shorts exported to external systems
Once the totals are calculated, you can export them to an external application.
This chapter contains the following topics to help you understand and manage total definition:
The Total Maintenance wizard allows you to create and update a total definition. It involves a series of steps where various details such as, total type, total entity, data source, roll up level, total usage can be specified. Each window in the wizard has a text box to the right that displays help text for each field. You should understand the following concepts before you create or update a total:
Revision Number
Each total definition is assigned a revision number because updates to the total definitions are saved as new revisions and the existing definition record is not updated. This allows you to maintain a full audit trail and ensures that if an export is restarted after the total definition has changed, the system still has the total definition of the original export.
Status
Each total definition is assigned a status. Based on the status of the total, the values it produces are stored in respective tables. For example, if a total definition is in Worksheet status, the values it calculates are written to the specific tables. This allows you to test total definitions to ensure the definition meets the functional need before introducing the total into the main test or production system.
The Overview window allows you to enter basic information for a total. You can enter the following information on the Overview window:
| Field | Description |
|---|---|
| Total | A unique ID and a description for the total. It is recommended that you add a description that is relevant to the objective you are trying to achieve with this total. |
| Description | A description for the total, any alpha numeric value is acceptable in this field. |
| Start Date and End Date | The period for which the total is active. |
| Update Date/Time | The system time when the total was last updated. This field is system generated and you cannot edit it. |
| Total Category | A grouping mechanism that helps to organize totals for display on the Over/Short and Miscellaneous Total windows. It does not influence the total in any way. It only helps to organize the total values for display. It determines the screens on which the total values are displayed. For over/short totals, you must specify an Over/Short Group and an Over/Short Operator. |
| Over/Short Group | Determines if the total values are ’Accounted For' (actual in hand money) or ’Accountable For' (based on transaction data, money the store should have). |
| Over/Short Operator | Used to determine whether the values produced by the total are added or subtracted from either the ’Accounted For' or ’Accountable For' groups. |
| Raw Data or Existing Total | A raw data total is defined using transaction data in the database. A combined total is defined by combining existing totals. While creating a raw data total, the total definition involves building an SQL statement. |
The Attributes window allows you to identify from where can the values for this total come from. The following are the options on the Attributes window:
| Field | Description |
|---|---|
| Corresponds to a value from the RTLOG | Determines whether the total is uploaded from the POS/OMS through an RTLOG file. |
| System calculated value | Determines whether the total is calculated by the system. For system calculated totals, you must either build a SQL statement or define a total through total maintenance wizard. |
| Count or sum | Determines whether the total values represent a number of occurrences (count) or an amount resulting from them (sum). |
| Store or system balancing level | Determines whether one value for the total is produced per store, or if one value is produced per cashier/store (if the balancing level is cashier) or per register/store (if the balancing level is register). |
| Created with the wizard | Determines whether the wizard is used to actually write the code for the total definition. |
| Required by the system | Determines whether the total is system required. The only totals that are required by the system and must be set up before production usage are:OVRSHT_S – Used for the store level over/short total.OVRSHT_B – Used for the balancing level over/short total (this is only used if the SA_SYSTEM_OPTIONS. BALANCE_LEVEL_IND is either ’C' – cashier or ’R' – register. |
| Display Group | Determines the order in which the total appears on the Over/Short and Miscellaneous totals form. |
The Realms section allows you to add existing realms (tables) to the total. The list of available realms is displayed in the window.
Availability of realms is determined by whether possible joins exist with the realms that are already added to the total definition (the wizard does not allow incomplete joins that create Cartesian products).
In general, you should add the general/parent tables, and then add the specific/child tables. If at any time, a realm that you want to use is unavailable, you should remove the realms already added to the total, and try again in another order.
The Joins section displays how the realms are joined together. These joins are based on metadata defined in the system.
The Parameters section allows you to choose the parameters for the total definition. You can only choose parameters (columns) that are available in one of the realms that were previously added to the total definition.Begin by choosing one of the realms. The parameter list then displays a list of all of the parameters that belong to the selected realm. You must choose the parameter that you need to count or sum and any other parameters that may be used to limit the data set.
The Roll Ups section allows you to define the parameter that is counted (or summed). It also allows you to define up to three parameters to group by. Only parameters that were previously added to the total definition can be either counted (summed) or grouped by. The system by default rolls up the totals to either store or balancing level (cashier/register) level based on whether the total is defined at the store level or cashier/register level
The Restrictions window allows you to limit the data set that is included in the total values. You can only restrict with parameters that were previously added to the total definition.
Restrict Results By Table Values
Restricting allows you to make numerical as well as logical comparisons on the set of parameter values that have been added earlier to the total definition.
Restrict Results By Constant Values
Restricting by a constant value allows you to define totals that only apply to a simpler data set that can be identified by a constant.
The Combined Total Details window allows you to combine two existing totals. In order to be combined, the totals must be compatible. To be compatible, totals must be defined at the same balancing level and have the same roll-ups.
The Location Traits window allows you to associate total definitions with stores. The totals are calculated for each store that has the location trait. If multiple traits are added and have overlapping stores, the total is calculated once per store. Location Traits are defined in Merchandising. It is recommended that when Sales Audit is used, you create an All Stores location trait and associate that trait with all available stores. While some regional or loss prevention totals can be associated with limited groups of stores, you must associate other totals (such as, Over/Short) with every store that uploads data to Sales Audit.
The Usages window allows you to define what should be done with values calculated by the total definition. There is a usage for each export. Total definitions that have export usages flag their resulting total values so that these values are selected and written to the export files. There is a usage for the Flash Totals Report and a Flash Sales Year to Date report too.
You can manage totals using the following procedures:
To create a total definition:
From the Tasks menu, select Create Total Definition. The Total window appears.
In the Total field, enter the ID and description of the total definition.
In the Start Date and End Date fields, enter the dates for which the total definition is effective.
|
Note: If you leave the End Date field blank, the total is calculated indefinitely. |
Select a category for the total in the Total Category field.
Click Next to navigate through the wizard. Help for the selected field and button appears in the section on the right side of the window. For more information about the fields on each window, see Frequently Asked Questions.
|
Note: Select Raw Data or Existing Total from the Total Overview panel to indicate how the total is defined. If you select Raw Data, you create a completely new rule. To do this, you need a thorough knowledge of the tables and columns in the database. If you select Existing Data, you create a sum of existing totals.You must associate all totals defined with a specific location list. For more information, see Defining Location Traits |
Click Save to create the total definition.
You can combine existing totals to form a combined total using the Combined Total Details window.
To create a combined total:
From the Tasks menu, select Foundation Data > Create Total Definition. The Total Maintenance window appears.
In the Total field, enter the ID and description of the total definition.
In the Start Date and End Date fields, enter the dates for which the total definition is effective.
|
Note: If you leave the End Date field blank, the total is calculated indefinitely. |
Select a category for the total in the Total Category field.
Click Next. The Attributes window appears.
In the Display Group field, select the order in which the total appears on the Over/Short and Miscellaneous totals window.
Click Next. The Combined Total Details window appears.
Select Actions > Add. The Add Totals window appears.
In the Operator field, select the operator.
In the Total field, select an existing total.
Click OK to add the total. You can add more totals by clicking OK and Add Another.
Click Next to navigate through the wizard. Help for the selected field and button appears in the section on the right side of the window.
Click Save to create the combined total.
To view a total definition:
From the Tasks menu, select Foundation Data > Manage Total Definitions. The Total Search window appears.
Enter additional criteria as desired to make the search more restrictive.
Click Search. The totals that match the search criteria are displayed in a table.
Select the total you want to view and from the Actions menu select View.
The Total Maintenance window for the selected total appears.
To edit a total definition:
From the Tasks menu, select Foundation Data > Manage Total Definitions. The Total Search window appears.
Search for and select the total definition you want to edit.
From the Action menu, select Edit or click the Edit icon.
The Total Maintenance window appears.
Select the page you want to edit.
Make the necessary changes and click Save to save the changes.
The default status of a defined total is Worksheet. You can set the status of a total definition to one of the following actions:
Submit a total definition for approval
You can submit a total definition using the Submit option, this changes the status to Submitted.
Approve a total definition
When a total definition is submitted for approval, you can approve it by changing the status of the total to Approved by using the Approve option.
Disable a total definition
You can disable a total definition by changing the status to Disabled by using the Disable option.
Delete a total definition
You can delete a total definition by changing the status to Deleted by using the Delete option. You cannot delete an approved total.
To change the status of a total definition:
From the Tasks menu, select Foundation Data > Manage Total Definition. The Total Search window appears.
Search for and retrieve the total definition in Edit mode. The Total Maintenance window appears.
|
Note: If you want to submit a total definition for approval, restrict the search to the totals in Worksheet status. |
From the Submit menu, select a status for the total. You can select from Submit, Approve, Disable, or Delete.
When the system prompts you for a confirmation of status, click Yes.
Click Save and Close to save your changes and close the window.
What is the difference between a Total and an Audit Rule in Sales Audit?
Totals in Sales Audit can either come from the POS or other external systems through the RTLOG. Sales Audit 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 Sales Audit and exporting the transactions as well as totals. Audit Rules are a set of rules defined by the Sales Audit user to detect any error or exception in the data imported from external systems. Based on the given conditions if a rule fails, the system generates an exception, 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, Sales Audit still exports it to other systems as long as they are not defined in the system impact for the given error.
On the Total Maintenance wizard in Sales Audit, is the question "Is this total a component of the Over/Short or Miscellaneous total?" only used to define on which total screen the defined total appears?
Yes, this is only used to define the window that displays the total, the Over/Short or the Miscellaneous Totals form. Auditors use Over/Short Totals to perform store balancing (tender balance).
On the Total Maintenance wizard in Sales Audit, there is a question ”Does this total correspond to a value in RTLOG?” Where is the total value displayed when this is set to ’Yes' or ’No'?
If this is set to ’Yes', it indicates the total is expected in the RTLOG such as a Declared Tender Total; therefore, the value is displayed in the RTLOG Reported column.
If this is set to ’No', it indicates that Sales Audit calculates this value based on either raw data or existing totals; therefore, the value is displayed in the System Calculated column.
On the Total Maintenance 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 is displayed on the defined form in Sales Audit. 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 are displayed alphabetically. This information is not held on one specific field, but rather queries multiple fields on the sa_total_head table.
What is the difference between Accounted For and Accountable For?
Accounted For – These totals represent what is physically counted in the till. (for example, total cash on hand, total checks).
Accountable For – These represent what should be in the till (for example, Sales, Returns). These totals can either come from the RTLOG or Sales Audit can calculate these totals. Auditors use Miscellaneous Totals for other types of analysis (for example, loss prevention totals), which can have no relevance to the Over/Short screen.
What is the impact on store days when a total is edited?
If you make any changes and re-approve the total, then all the store days that fall in the total date range gets re-audited when total is moved back from approved to worksheet.
