Vouchers are items when they are sold to a customer. Vouchers are tenders when they are redeemed by a customer for merchandise.
No, V_SA_TOTAL_VALUE view should be used to get the latest Total Value.
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).
Totals in ReSA can either come from the Point of Sale (POS) or other external systems via the Retail Traction Log (RTLOG). ReSA can also calculate the total itself based on raw traction data or existing totals. Totals are used in performing store balancing (over/short), analysis within ReSA and exporting to existing tractions 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 traction or total to the systems defined in the system impact. Even if the traction 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.
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:
Tractions can be exported to Retail Data Warehouse (RDW) for reporting. Tractions can be stored online in ReSA for current usage or purged when they are no longer needed.
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:
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
Saimptlogfin.pc creates a balance group entry (SA_BALANCE_GROUP table) for the store/day, cancels post voided tractions, updates the Data Status to ‘Fully Loaded’ if the ‘DCLOSE’ traction is received (sets Store Status to ‘Closed’ if single level audit). It also removes missing traction records that are now present due to loading data out of sequence.
The count or number of tractions 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).
The Traction Find Window through Traction maintenance menu is used to create new or view, edit tractions or create post void. While the store / close audit screen is used to audit days tractions.
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 tractions for those stores as well.
Since the Traction Find Window through Traction Maintenance menu is used to create new or view, edit tractions 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.
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 Traction Find Window through Traction
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
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.
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.
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 ds_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.
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 tractions 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
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 tractions from a single store and day. This way the Traction Find window works for a single store. But the user needs to visit the screen through 'Store Close Audit' menu.
So, the Traction Find Window through Traction maintenance menu is not restricted to the users home store. It is used to search for tractions across multiple stores and/or days.
sa_system_options is not updated from RESA UI but is populated at time of installation by running the script sa_system_required.sql
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.
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.
If you have a scenario at Point Of Sale (POS) where in you give a discount of 100 at a Total Bill of 10@This discount does not apply to any item but is at a Bill level.
Retail Merchandising System (RMS) does not supports bill/traction level discounts, RTLOG file will not have a traction-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/traction 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 10@And it has two items priced differently then the traction-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 Traction records should have an additional TTEND record to denote the discounts obtained by those coupons or vouchers for a particular traction.
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.
ACH is a United States based banking network used to electronically trfer funds. The National Automated Clearing House Association (NACHA) regulates this network and maintains the standards.
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.
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.
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.
Stock Counts, Receipts, Trfers and Inventory Adjustments are not sent through ReSA. These types of tractions can either be supported by Retail Store Systems (RSS) or loaded directly into the Retail Merchandise System (RMS) from the POS. Trfer Ins, Trfer Outs, Receipts, and Return to Vendor (RTVs) can be loaded directly into RMS using upload files.
Main tractions processed by ReSA are the following:
NOTE: Not all POS systems support all of these tractions.
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.
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 traction can contain any variety of items with different statuses; however, ReSA identifies a Sale traction as net money being paid into the till and a Return traction as net money being paid out of the till. ReSA identifies an Exchange traction when no money changes hands.
NOTE: The traction type is held in SA_TRAN_HEAD while the individual traction type is recorded in the SA_TRAN_ITEM table.
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 @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 SCARLET.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.27@If the first range is defined as 0 - 0.2659; the next range will start at 0.2660
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.
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.
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:
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’.
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.
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.
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.
There is no setting in ReSA to define if a store will be trickle polled. The only requirement is to send a ‘DCLOSE’ traction to ReSA at the end of the day. If a Retail Traction Log (RTLOG) is loaded but does not contain the ‘DCLOSE’ traction, the Data Status is updated to ‘Partially Loaded’. If the ‘DCLOSE’ traction is received the Data Status is updated to ‘Fully Loaded’.
SAEXPGL exports totals to Oracle Financial Application general ledger for:
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.)
SA ESCHEAT.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
When deleting a Store/Day, it is actually the traction 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 tractions will not be accessible through the Traction Maintenance screen.
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 Traction Log (RTLOG) compatible systems as ‘Paid Out’ tractions.
Included in this Paid Out traction are the following data:
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 vendor store. RMS will export this traction to ReIM as well. ReIM will match this RMS traction with the ReSA traction 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).
When doing a Post Void on a traction from ReSA forms and running SAEXPRMS batch, the traction will only be picked up if it is already exported. But if it is post voided before export, it basically me it is canceled and no data need to be sent to RMS for this traction.
Yes, it is multi-threaded and restart/recoverable.
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).
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.
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
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…
The Item Summary form serves three purposes:
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 tractions where a specific tender type group was used for a given store/day.
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’ tractions even if errors impacting RMS exist in other tractions. 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 Traction, only clean tractions 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’ tractions to RDW (Retail Data Warehouse) just like how it is doing it for RMS.