Welcome ReSA Fans. This post is continuation Understanding ReSA Deep Dive Page. Please refer preivious posts in case you are new to Oracle Retail Sales Audit. In this series, I'm going to cover all about ReSA Totals. This includes understanding first basic ReSA Total Concepts and then how to setup any Totals.
ReSA allows
users to define the totals they need for their business. The ReSA totaling process allows users to
sum, count or create combination total on any data. Totals can be used to roll up data for export
to external systems, as part of loss control schemes or for general reporting. Totals always relate to one business date at
a time.
There are
several concepts that must be understood before totaling is examined in detail:
1.
Total Definition
The user defines a total through a GUI wizard. The total definition is held on tables in the
database. The definition controls the
values that will be produced, so it is especially important to plan and test
your total definitions in worksheet status before approving the totals. Total definitions must be created before any
total values will be produced.
2.
Total Level
The total level is defined within the total definition. The total level relates to the balancing
level that has been chosen for the system.
If a company balances by store, it will only be
able to create totals for entire store/day.
If a company balances by register, it will be
able to create totals for an entire store/day or for each register during the
store/day.
If a company balances by cashier, it will be
able to create totals for an entire store/day or for each cashier during the
store day.
3.
Total Values
A total value is an actual numeric result of a total
definition. Total Value represents the total definition applied to the data
set. Values can come from many data
sources (see below). Values are always
saved for a complete audit trail.
4.
Total Sources
Totals can have one or any
combination of several data sources:
o
POS
Uploaded
Total declared by Store. Values for
the total will be uploaded from the POS in the RTLOG. When values for totals are uploaded, the
reference fields on the transaction header (SA_TRAN_HEAD). The value will be copied to the SA_POS_VALUE
table.
o Store User Updated.
Under multi-level audit scenario, this represents the store
manager override value. A Store level user can enter values for the total. Any values store users enter will be written
to the SA_STORE_VALUE table.
o HQ User Updated
An HQ level user can enter values for the total. Any values HQ users enter will be written to
the SA_HQ_VALUE table.
o System Calculated
The system will calculate values for the total. When the system is expected to calculate a
value for the total, the total definition is more complex and involves defining
the tables and columns that the system should sum or count. Any values the system calculates will be
written to the SA_SYS_VALUE table.
5.
Total Types
§ Raw Totals
These are single totals and executed
on raw data.
For example:
T1 = Total Sales by Store
T2 = Total Returns by Store
§ Combined Totals
Combined totals are sums of
already existing totals, in other words, Total of totals.
For example, a user might create a
total that counts the number of transactions per store/day. The user might create another total that
counts the number of SALE transactions per day.
The user could them combine these totals (Number of transactions per day
– Number of SALE Transactions per day) to calculate the number of non-SALE
transactions per day.
To be combined, totals must ‘match’
in type, meaning that a sum and a count can’t be combined (adding together the
monetary sum of all transactions for a day and the number of transactions in a
day does not make sense).
Also, totals must be at the same
balancing level (a count of the number of SALE transactions for an entire store
cannot be added to a count of the number of RETURN transactions for a single
cashier).
6.
Total Metadata
Metadata is data about data. The user is able to pick tables
and columns because ReSA contains a metadata repository. What this means is that ReSA contains tables
that contain information about the other tables in the system. There is a table that lists all of the tables
in the system (SA_REALM). There is
another table that describes all of the columns on each table (SA_PARM). Using the information in these tables, ReSA
is able to build valid SELECT statements for any Total.
Although, coding and SQL experience is not necessary to use
the totals wizard (the most important thing is to have a good understanding of
the business question and the data model).
However, some familiarity with what is going on behind the scenes makes
the process easier to understand.
§ Realm
A realm is basically equivalent to a
table that will be used in a total definition.
They are called realms because realms can be more than just tables. They can also be views.
§ Parameter
A parameter is basically equivalent
to a column in a table that will be used in a total definition.
§ Join
A join is equivalent to the part of
the WHERE clause in the SQL SELECT statement that joins multiple tables
together. This allows information on multiple tables to be linked
together. Joins will be defaulted by
ReSA, bust displayed to the user.
§ Restriction
A restriction is equivalent to the
part of the WHERE clause in the SQL SELECT statement that limits the rows
returned (e.g. TENDER_TYPE_GROUP='CASH').
§ Roll
Ups
A roll up 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.
7.
Over/Short Total
ReSA does not deliver a predefined Over/Short total with the
product since every retailer may define this total differently; therefore, the
retailer must define all the component totals to be used in the calculation of
their Over/Short as well as the Over/Short total itself. These component totals may include totals
such as Total Sales, Total Cash Tendered, etc…
Once all the component totals have been defined the retailer will then
define their Over/Short total.
The main over/short total must be named according to strict
convention so that the correct total values are defined in the Over/Short
form. The correct names for this
total (depending on balancing level are):
§
OVRSHT_S – Used for the store level over/short total.
§
OVRSHT_B – Used for the balancing level over/short total (this
will only be used if Balancing Level is either ‘C’ – cashier or
‘R’ – register.
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.
1 comments :
Post a Comment