This Step is continuation of my previous post for Oracle Retail Sales Audit (ReSA) - Deep Dive - Understanding Totals and Setup Process - 1. In this post, I’m going to explain How to Setup Totals in ReSA and
Approving them. I’ll taking you through each and every Form, explaining definition
and purpose of each and every Form Fields and how best these can be utilized to
setup any Total in ReSA.
Feel Free to drop a note in case of any questions or
comments.
1. Setup New Total Definition
Total Search
Window
1.
In the Action menu, select New.
2.
Click OK. The Total Calculation Definition Wizard window opens.
Entire Total Setup is divided in following sections/wizards:
a)
Page 1 - Total Overview
Total
Calculation Definition Wizard Window
1.
In the Total field, enter the ID and description
of the total definition
2.
In the Start Date and End Date fields, enter the
dates the total definition is effective, or click the calendar button and select dates
3.
Click Next to navigate through the
wizard. Help for selected fields and buttons is displayed in the section on the
right side of the screen
Form Fields
Detail
Following are the field level details of Total Overview
window:
§ Total: A unique ID for the Total
§ Total Description: Recommended that the
description contain the business objective the user is trying to achieve with the
total
§ Start Date: The first business date for
which the Total will be calculated
§ End Date: The last business date for
which the Total will be calculated. This is an optional field. User may leave
this field blank.
|
Ø Note: If User
leave the End Date field blank, the total is calculated indefinitely
|
§ Version
•
When creating a Total definition for the first time, the Version will
always be 1
•
This field is populated automatically
•
The version changes if an existing Total definition is updated
•
Details of all previous version are maintained
•
Creation or edit of every Total is tracked
§ User ID
•
Displays the User ID of the person that created this version of the Total
•
Tracked for every version
§ Update Date/Time
•
Shows the time and date when this version of the Total definition was
created
§ Status
•
The status field displays the current status of the Total
•
Status of a Total can be changed via the Options menu
•
The option menu enables the Options which are valid based on the current
status of the Total
•
Total can have following different Status:
o
Worksheet: A
Total definition can be created or edited only when the Total is in ‘Worksheet’
status
o
Submit:
After creating a Total, the user can change the status to ‘Submit’
o
Approve:
Only ‘Approved’ Totals are processed. Right to ‘Approve’ a Total can be
accessed controlled
o
Disable:
‘Disabled’ Totals are excluded from processing. But the Total definition
details are maintained in the system.
o
Delete: The
Total definition and data related to the Total is purged from the system. The
Purging is done by a batch.
§ Total Category
•
Total Category is used to classify a Total
•
This is used in the ‘Miscellaneous Totals’ screen for display
purposes
•
Totals linked to one Category are displayed together
•
Aids in easier auditing
•
The values can be user-defined
•
Default values available are – Sales, Tax, Over /Short, Transaction
Type, Tender Type, Payment Totals
§ Over/Short vs. Miscellaneous
•
Over / Short
o
Refers to Totals which are used for balancing
o
There should be a POS Declared Total for every System Calculated Total
o
Compares the System Calculated Total with POS Declared Total to identify
any variance
o
If there is any difference then the store is set as Unbalanced
•
Miscellaneous
o
All other totals
o
Used for analysis & reporting
o
Can be used for posting data to GL
§ Over / Short Group
•
Controls the display of the Total is the appropriate area of the Over/Short
Total Screen
•
Over/Short form has two sections – Accounted
For, Accountable For
o
Accounted For
§ Refers to the Totals that are
physically counted at the Store (e.g. Total Cash)
o
Accountable For
§ Represents what should be
reported by the Store
§ These totals are system
calculated
§ Operator
•
Mathematical operator that should apply to the Total when calculating
variance
•
Available options are addition and subtraction
§ Raw Data vs. Existing Total
•
Determines the source of data for creating the total
•
Possible values - Raw Data, Existing Total
•
A ‘Raw Data’ total is defined using transaction data on the
database. A combined total is defined by
combining existing ‘raw data’ totals
•
Next options in the Total Definition Wizard depend on the above selection
|
Ø Note: Select
Raw Data or Existing Total on the Total Overview
panel to indicate how the total will be defined. If User selects Raw Data, User
is creating a completely new rule. User needs a thorough knowledge of the
tables and columns in the database. If User select Existing Data, User is creating
a sum of existing totals
|
Ø Clicking on the “Next”
button loads the next Total Characteristics form
b)
Page 2 & 3 - Total Characteristics
§ Click next to continue for
additional total characteristics values on Total Characteristics
form
Form Fields
Detail
Following are the field level details of Total
Characteristics Form:
§ Does
this total correspond to a value from the POS?
•
“Yes”
indicates that the value for this Total is passed from the point-of-sales or
from another external system
•
“No”
indicates that the value for this Total will not be passed from outside
•
This option works in tandem with the option - “Should a system value
be calculated for this total?”
§ Can
values for this be entered at the Store?
•
“Yes”
indicates that a store employee can enter or update the Total value
•
“No”
makes the value non-editable
•
Usually a system calculated value should be non-editable
•
Editing a Total Value, stores the new value under a new version
•
Audit trail of all edited values is maintained
§ Can
values for this be entered at the HQ?
•
“Yes”
indicates that a HQ auditor can enter or update the Total value
•
“No”
makes the value non-editable
•
Usually a system calculated value should be non-editable
•
A POS declared value can be set to “Yes”, to allow the HQ auditors to edit
POS declared deposits to settle variances
•
Editing a Total Value, stores the new value under a new version
•
Audit trail of all edited values is maintained
§ Should
a system value be calculated for this total?
•
“Yes”
indicates that the Totals value will be calculated by the ReSA automated
totaling process
•
“Yes”
requires to configure the criteria for calculating the Total
•
“No”
indicates that the Total value will be passed from external systems
•
This option works in tandem with the option - “Does this total
correspond to a value from the POS?”
§ Does
this total represent count or sum?
•
This option indicates if the Total is a count of values in a data set or if
it is a sum of values in a data set
o
Count -
Indicates that the Total will perform a count for the entities that are
selected satisfying the criteria
o
Sum -
Indicates that the Total will add up or sum up the entities that are selected
satisfying the criteria
§ Balancing
level:
•
This option indicates the balancing level at which the Total is defined
•
Potential levels : Store, Register, Cashier
•
If the balancing level is configured as “Store”, then totals can be created
only for the entire store ~ day
•
If the balancing level is configured as “Registers”, then totals can be
created for the entire store ~ day as well as by each register for each store ~
day
•
If the balancing level is configured as “Cashiers”, then totals can be
created for the entire store ~ day as well as by each cashier for each store ~
day
§ Is
this total created with the wizard?
•
“Yes”
indicates that the Total is created using the wizard
•
“Yes”
will enable the screens to define the criteria for calculating the total
•
“No”
indicates that the Total is not created with the wizard functionality
•
“No”
indicates that the Total is created backend by a developer
•
Commonly used Totals will be created from backend to address performance
requirements
§ Is
this total required by system?
•
“Yes”
indicates that total is marked as system required.
•
“No”
indicates that total is regular total and not marked as system required.
•
The only totals that are required by the system and must be set up before
production usage are:
o
OVRSHT_S –
Used for the store level over/short total.
o
OVRSHT_B –
Used for the balancing level over/short total
(this will only be used if the SA_SYSTEM_OPTIONS. BALANCE_LEVEL_IND is
either ‘C’ – cashier or ‘R’ – register.
§ Display
Group
•
Indicates the order in which the Total should be displayed on the
Over/Short and Miscellaneous totals forms within their total
categories
§ Display
Group Details
•
Clicking the Display Group Details displays all the other Totals and their
display order
Display
Group Details window
Ø Clicking on the “Next”
button loads the Realms Form to defines realms
c)
Page 4 - Realm Definition
Realm is a general
term for a database object and can be either a database table, view, and in
some cases, Total. The Realms screen allows the user to add tables to the total
definition. The list of available realms
is displayed on the lower portion of the screen.
Form Fields
Detail
§ Realms can be either a database
table, view, and in some cases, Total
§ Availability of realms is
determined by whether possible joins exist with the reams that have already
been added to the total definition (the wizard will not allow incomplete joins
that will create Cartesian products)
§ User should add the more
general/parent tables, and then add the more specific/child tables
§ If at any time, a realm that a
user wants to use is unavailable, the user should remove the realms already
added to the total, and try again in another order
§ As all totaling occurs on the
store/day level, the SA_STORE_DAY table is automatically added to
every total definition
Ø Clicking on the “Next”
button loads the Joins Form to defines realms
d)
Page 5 - Joins
The joins screen displays how the realms from the previous screen will be
joined together. The system defaults the
joins. They are only displayed so that
the user is aware of them.
Form Fields
Detail
§ This screen is auto populated by
the system and is a display only screen
§ Based on the realms selected by
the user, the relationship between the different selected objects is shown here
Ø Clicking on the “Next”
button loads the Parameters Form to defines realms
e)
Page 6 - Parameter Definition
The parameters screen allows the user to
choose the parameters that will be included in the total definition. The user may only choose parameters that
belong to realms that have been previously added to the total definition.
The user begins by choosing one of the
realms he had already added from the realms LOV. The parameter LOV will then display a list of
all of the parameters that belong to the realms. The user must choose the parameter that he
wishes to actually count or sum and any other parameters that may be used to
limit the data set.
Form Fields
Detail
§ Parameter is a general term for a
subset of a realm
§ It basically represents the
fields / columns of a table or view
§ User can add only those
parameters that are members of realms that have already been added to the
definition
§ The parameters added in this
screen determine the information that is available for use in the rest of the
definition
Ø Clicking on the “Next”
button loads the Roll Ups Form to defines roll ups
f)
Page 7 - Roll Ups
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.
Because the total is at the store level,
and therefore the user only wants one value per store, the system automatically
assumes a roll up to the store/day level.
If the total were defined to be at the cashier/register balancing level,
and therefore the user wanted one total value per cashier/register, the system
would assume a roll up to cashier/register.
For example, Roll up can be by tender,
departments etc.
Form Fields
Detail
§ Parameter to be Totaled: Parameter to be Totaled (Summed
or Counted)
§ Roll Up 1/ Roll Up2/ Roll Up 3:Three Level Roll Up Parameters (
Group By)
Ø Clicking on the “Next”
button loads the Total Restrictions Form to defines all
restrictions on total
g)
Page 8 - Restrictions Definition
The Restrictions screen allows users to further limit the
data set that is included in the total values. The user is only allowed to
restrict with parameters that have been previously added to the total
definition.
|
Ø Note: The
user may restrict using the following valid relational operators:
When using the operators between
and not between, the user must specify a low and high limit.
|
Form Fields
Detail
§
Restrict Results By Table Values
o
Restricting by a table values is used more in Rules than in Totaling.
o
It allows users to add restrictions by comparing values between two Table
Columns (parameters)
o
The user is only allowed to restrict with parameters that have been
previously added to the total definition
o
The third Parameter is only available when the relational operator is
'Between' or 'Not Between'
o
For example, ReSA stores both the pos declared value for a total and the
system calculated value for a total on the database (on the SA_POS_VALUE and
SA_SYS_VALUE tables respectfully), the user can use a restrict by table value
to only raise an error when these stored values don’t meet their tolerance.
§
Restrict Results By Constant Values
o
Restricting by a constant value allows the user to define totals that will
only apply to a simpler data set that can be identified by a constant
o
The user is only allowed to restrict with parameters that have been
previously added to the total definition
o
e.g. if Total to be created for the sum of all SALE transaction then
constant value for the transaction type
i.e. SALE is defined
Ø Clicking on the “Next”
button loads the Location Traits Form to associate location trait
to Total
h)
Page 10 - Location Traits Definition
The Location Traits screen allows the user
to associate total definitions with stores.
The total definition will be calculated for each store that has the
location trait. If multiple traits are
added and have overlapping stores, the total will only be calculated once per
store.
|
Ø Note: Location
Traits are defined in RMS. It is recommended that when ReSA is used, at least
one location trait is defined and associated with all stores. While some regional or loss prevention
totals may be associated with limited groups of stores, other totals (like
Over/Short) must be associated with every store that uploads data to ReSA.
|
Form Fields
Detail
§
Loc Trait & Loc Trait Description: Id and Description of the
Location Trait that needs to be associated with Total
Ø Clicking on the “Next”
button loads the Total Usage Form to associate usages to Total
i)
Page 11- Usage Definition
The Usages
screen allows the user to define what (beyond calculation) should be done with
values calculated by the total definition.
There is a usage for each export.
Total definitions that have export usages defined, their resulting total
values are selected and written to the export files.
Form Fields
Detail
§ Usage and Description: Id and Description of the Usage that
needs to be associated with Total
j)
Review & Finish the Totals Definition
To review and make any changes to
the definition, click Back to return to the appropriate area. User can
also directly go to the appropriate page by selecting it from the Skip to
Page dropdown field.
1.
Click Finish. The Totals Definition is
saved.
2.
Clicking on the finish button, creates the Total
in “Worksheet” status
3.
The ‘Finish’ button launches the
processing that actually creates the total view (and metadata for the view) and
builds the SQL statement into a function on the database
2. Define Combined Totals (Total of Totals)
Successful use of combined totals
requires the definition of many simple raw data totals. When used in the context of combined totals,
these simple totals are called component totals or existing totals. Component totals can be used to create
numerous combined totals.
Combined totals use much of the same
Totals Definition Wizard. However, the
process is much easier because there is no need to select realms and
parameters. Instead, the user only needs
to select existing totals to combine. Following are the specific/additional
details need to be defined for Combined Totals:
a)
Page 1 - Total Overview
Raw Data vs. Existing Total – The use of the Total Overview
screen is same for combined totals as it is for raw data totals. The only difference is marking the total
definition as being based on existing totals.
b)
Page 2 & 3 - Total Characteristics
Count vs. Sum - The count vs. sum indicator is one
of the attributes that determine whether or not totals can be combined. It is not valid to combine a sum and a count.
Store vs. Balancing Level – The store vs. balancing level
indicator is one of the attributes that determine whether or not totals can be
combined. It is not valid to combine a
total at the store level with and a total at either the cashier or register
balancing level.
c)
Page 9 – Combined Total Details
The Combined Totals Details screen
allows users to select existing totals and an operator to combine them.
Validation in this screen ensures
that all component totals within a combined total:
·
Are either count
or sum
·
Are either store
or balancing level
·
Have the same
‘Group Bys’
|
Ø Note: When
creating combined totals, users will not access screens 4 – 8
|
Form Fields
Detail
§ Allows to add the raw Totals to
use in arriving at a combined Total
§ The Operator drop-down menu
allows to select mathematical operators, such as, + (addition) and –
(subtraction) to combine multiple raw Totals
§ A raw Total can be added by
clicking the “Add Total” button
§ To view the raw Total details,
click the “Total Details” button
d)
Page 10 - Location Traits Definition
The Location Traits screen allows the user to assign location traits to
the assigned total. The combined total
can only have location traits that exist on its component totals.
3. Submit Total Definition
Total Calculation Definition Wizard
Window
1. From
the Options menu, select Status > Submit. User is prompted to
confirm the submission.
2. Click
Yes. The status is changed to Submitted.
3. Click
Finish to save your changes and close the window.
4. Approve Total Definition
Totals Calculation Definition
Maintenance Window
1. From
the Options menu, select Status > Approve. User is prompted to
confirm the approval.
2. Click
Yes. The status is changed to Approved.
3. Click
Finish to save your changes and close the window
 - Deep Dive - Understanding Totals and Setup Proces...){kind=link}
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.
0 comments :
Post a Comment