Oracle Retail Merchandising System (RMS) - Stock Counts

Welcome again Oracle Retail Fans. When was the last time you got detailed documentations on RMS Stock Ledger? Well, there aren’t many, but you don’t need to look further. In this article, I’m going to cover all aspects of Stock Count & Cycle Counts in RMS. This article will be more focused on Stock Count process in RMS and very limited to the SIM (Store Inventory Management) Application. We’ll be covering SIM related detail under SIM Application’s articles. 

Please do note that this post is a continuation of Oracle Retail Merchandising System Deep Dive article and if you are a beginner, you may want to visit Oracle Retail Merchandising System Overview article before proceeding on this article, however, it is not completely necessary. Also, as mentioned in my earlier posts, this article covers each and every detail required for Item related questions in Oracle Retail Merchandising Systems (RMS) Certification (Pearson Vue Exam Number 1Z0-453) preparations. 

We'll be discussing following Stock Count areas in detail:

·         Stock Count Concepts Overview

·         Stock Count Detailed Process Flow

o    Schedule of Stock Counts in RMS

o    Creating Stock Count Requests

o    Review Stock Count Snapshots

o    Uploading Stock Counts in RMS

o    Review & Update Unit Count Results

o    Accept and Processing Unit Count Results

·         Stock Count Batches and Process Flow

·         Stock Data Model

1. Stock Count Concepts Overview

Stock Counts are the Physical verification of the Merchandise Inventory with the numbers kept within the merchandise management system, which makes reporting, planning, and ordering more accurate. Stock count (also known as Physical Inventory) is a process, whereby stocks at stores are physically counted by a 3rd party (mostly, but can be in-house too) to account for the shrinkage at store.  Retailers normally go for regular Cycle Counts for the Stores (more frequent than Warehouse) and once or twice for Warehouse in a financial year, however RMS supports as many as possible.

There are two types of stock counts supported in RMS:

·         Unit

·         Unit & Value

Unit

A “Unit Stock Count” request is generated in order to check the on-hand quantities for one or more departments, classes, subclasses for any selected Item List. Purpose of Unit Only counts is to validate On Hand shrinkage not the Cost or Retail. It can be ad hoc or a scheduled process. Unit Only Stock counts:

·         Uses an item list or one or more departments, classes or subclasses

·         Stock levels adjusted according to the physical count

·         Stock Ledger is not adjusted         

·         Shrinkage dollars / percent are not calculated

·         It can be ad hoc or a scheduled process

·         The Unit type of stock count can be set up in RMS using

o    an item list or

o    one or more departments, classes or subclasses

o    all departments

·         Unit Only Stock Counts adjustments affect inventory similar to an inventory adjustment

·         Posted Stock on hand quantities are updated and stock adjustments are written to the stock ledger

Unit & Value

Unit & Value Stock Count is a complete Physical Inventory which not reconciles the Stock On Hand but also the value of it. Unit & Value type stock counts are done either for an entire location or by merchandise hierarchy (department, class or subclass level only). In Unit & Value counts there is a two step approval process whereby  first stock on hand is adjusted based on the count results and then shrinkage is calculated based on the difference between the expected value of inventory in the stock ledger and the value of the count. One or more locations can be added to both types of stock counts, but a stock count can only contain stores or warehouses, not both. Additionally, for warehouses, counting is done at a physical warehouse level, so all stockholding virtual warehouses within a physical warehouse, including internal finishers, must be added to a count together

2. Stock Count High Level Flow

1. Scheduling of Stock Counts in RMS

Stock Counts can be scheduled in RMS manually or uploaded from an external system such as SIM or RWMS. Stores and warehouses handle stock counts in different ways. When scheduling is done within RMS it’s assumed that the count will also done in RMS. Also, note that Scheduling details do not flow down to WMS/SIM. Stock counting done in SIM should be scheduled in SIM

Both Unit and Unit & Value types of stock counts can be scheduled to trigger on the specified dates and also to re-occur on regular intervals. Any stockholding store can be added to a count, including both company and franchise stores. Scheduling of Stock Count varies  based on type of Count:

·   Unit Only

o    Scheduling can be done only for Item List of any Store, WH, LOC List, External Finisher

·   Unit and Value

o    Scheduling can be done for Dept/Class/Subclass at Store, WH, LOC List or External Finisher level

The number of days in advance of a count that the requests are created is based on the system parameter setting, Stock Count Review Days (STAKE_REVIEW_DAYS). Also, note that an extract does not exist to send stock counts created in RMS to external system. Building this is considered an implementation activity.

Unit Only Stock Count Scheduling

Navigation: rtkstrt -> Inventory -> Stock Count Schedule -> Edit

Unit & Value Stock Count Scheduling

Navigation: rtkstrt -> Inventory -> Stock Count Schedule -> Edit

Scheduling Store Count in SIM

·  For Unit stock counts performed in SIM no count information is sent to RMS until the count is complete

· Upon authorization of the count in SIM, inventory adjustments are sent to RMS to account for an inventory variances uncovered during the count

· For Unit & Value counts, on scheduling the stock count in SIM, stock count schedule messages are published to the RIB to communicate Unit & Value stock count schedules to RMS

Scheduling Warehouse Counts in RWMS

·  Because there is no RIB interface for stock count between RMS and RWMS, once a stock count is scheduled in RWMS, the same information has to be created through RMS online screens

2.    Create Stock Count Request

Apart from scheduling, Unit and Unit & Value stock counts can also be requested on an ad hoc basis through the Stock Count Request screen. This screen allows for creating

·         Unit counts by item list level or product group level (all departments, department, class, or subclass) and

·         Unit & Value counts by product group level

If a department, class or subclass is selected for either of the count types, one or more departments, classes or subclasses can be added to the count. For Unit counts, only ‘all departments’ can be selected for the product group level for warehouses. A count can be created for either stores or warehouses, not both. Any stockholding store can be added to a count, including both company and franchise stores. Locations can be added to the count individually or by selecting a group of locations, such as a location list, all stores, and so on. Please note that if multiple locations are added to the count, then the count will only be able to be finalized once the counts for all locations are completed.

The number of days in advance of a count that the requests are created is based on the system parameter setting, Stock Count Lockout Days (STAKE_LOCKOUT_DAYS). Once the stock count has entered its lockout period, changes to the count are no longer allowed, although the count still can be deleted

RMS only scenario – Users can create stock count for each store. While RMS can handle multiple stores in a stock count, it is preferable from flexibility point of view to create a unique stock count per store.

Navigation: rtkstrt -> Inventory -> Stock Count -> Action: New, Action Type: Request

RMS and SIM scenario – Users can create stock count in SIM that will be interfaced to RMS. In both cases, following are the data required/created by RMS:

·         Stock Count ID

·         Store ID

·         Stock Count Date – For evening stock counts, i.e. the counts happening in the night after the store is closed for the day, this date will be the date for that day.  For morning stock counts, i.e. the counts happening in the morning before the store is opened for the day, this date will be the prior day’s date.

·         Stock Count Type

·         Merchandise

Once set up, stock counts can be edited until the lockout date.  The lockout date is based on a system setting of lockout days and is calculated as the RMS date (also called virtual date) minus lockout days.

3.    Prepare Stock Count Data (RMS)

On the night of "Lockout Date", a RMS batch process will look at the stock counts that are set up. For "Unit and Value" stock counts, SKU level records from RMS perpetual inventory tables will be extracted and put into stock count tables in RMS.

4.    Take Snapshot

On the night of the "Stock Count Date", a nightly batch job in RMS will take a snapshot of the data (stock on hand, in transit, cost and Retail) for stores being counted. The cost captured is either the current WAC or the standard cost for the item, depending on the accounting method used. Cost and retail values are both captured in local currency.

For transformable items that are put on stock count, which are ordered and inventoried in different form and sold in different form, sellable only items would have a snapshot of zero on hand and in transit captured, but will have a retail value captured orderable only item will have a stock on hand, but no retail value. These values are used to compare against the count results and, for Unit & Value counts, it is used to calculate the value of the inventory to compare against the book value calculated in the stock ledger

RMS assumes that no inventory transactions occur between the time when the snapshot is taken and the count. It also assumes that the inventory is relatively clean, meaning that all in progress receipts have been completed; sales have been audited, and so on. However, late transactions can still occur and they will update the snapshot, if the stock count quantity has not yet been finalized.

The batch job runs after all the known inventory movement for the day; such as receiving, adjustments, transfers and sales have processed for the store. This snapshot copies the stock on hand, in transit and unit retail value for the SKU's (Level 2 items) in the stock count tables.

RMS expects no inventory movement to happen at the time the stock is counted at the stores, as otherwise it may cause reconciliation issues.

SIM also takes snapshot just prior to starting the count at store.

5.    Physical Count

The 3^rd party (for example, RGIS) will upload a UPC dump from RMS into their scanning guns and will count the inventory at the stores. It is expected for the count file to have valid RMS items.

6.    Stock Count File Upload

Once the counts are reconciled, the count file will be uploaded to Oracle Retail. Stock Count can be uploaded in RMS via an upload from an external system (RWMS, SIM or a 3rd party counter).

·         Post-SIM – File will be in the SIM defined format and will be uploaded to SIM.  Unit variances will be reviewed in SIM and researched.  Any required adjustments to the count quantity will be made in SIM. Once Inventory Control is comfortable, variances will be posted in SIM, which will result in an update of stock on hand in SIM.  SIM will then create a flat file in RMS defined format that will be uploaded to RMS either by an ad-hoc batch or in a nightly batch.

·         Pre-SIM – File will be in the RMS defined format and will be uploaded to RMS either by an ad-hoc batch or in a nightly batch. The Upload Stock Count batch process is used to process the count information from these external systems and update the RMS count tables.

Following are the key validations performed at the time of upload:

·         If an item is included in the upload that was not part of the original count request, RMS will handle it differently, depending on how the count was originally created.

o    Unit & Value stock count - Unit & Value stock count upload will create item/location relationships if they do not exist, provided the item exists in a department, class, or subclass on the count

o    Unit stock counts - Unit stock counts will create item/location relationship if they were scheduled by Product Group and the item exists in a department, class or subclass on the count. If count is scheduled by Item List and does not have item/location relationship non-fatal errors will be returned when the count is uploaded

·         For items added to the count that are not captured in the snapshot, the snapshot values are captured as follows:

o    X On Hand = 0

o    X In Transit = 0

o    X Unit Cost = current item/location WAC or unit cost, depending on the cost method

o    X Unit Retail = current item/location unit retail

·         If the same item is uploaded more than once for same store or warehouse, the value is added to already counted value

·         So, if corrections are needed to a count from an external system, they can be made by sending positive or negative adjustments for the count through this upload process

·         For warehouses, stock count results will always be for the physical warehouse (PWH), since the warehouse itself has no visibility to the virtual warehouses (VWH) in which RMS holds inventory

Ø  Note: RWMS always sends the inventory for all items when a count occurs, regardless of which items RMS has designated for the count. RMS processes only the items it needs for the count

7.    Variance Reports and adjustments to counts

In the Pre-SIM environment, once the counts are loaded into RMS, Inventory Control will run stock variance reports and verify the variances. There are RMS reports that can be utilized for checking variances. These RMS reports report variances that are outside of predefined tolerances setup in system options. There are three system tolerances – quantity, cost and retail. If there are unit changes to variances that are required, then those can be made in the RMS stock variance screen.

Stock Count Variance report

·         After the results are uploaded into RMS, they are reviewed on-line using the Stock Count Variance report.

·         The variance report will display the Book Stock, Stock Count, Variance, and In-transit quantity for the items on the count for the selected stores or warehouses

·         Items are displayed in this report for the selected locations if they fall outside the acceptable unit or value variance

Navigation: rtkstrt -> Inventory -> Stock Count-> Action: New Action Type: Count Quantity

In the Post-SIM environment, research would have been done and counts adjusted in SIM already (please see step 5 above) and no further adjustments would be done in RMS. Stock variance reports and other analysis can still be done.

 

8.    Accepting Unit Variances (in RMS)

Once the Inventory Control team is comfortable with the variances posted in RMS, they can accept the variances in the RMS stock variance screen.

A nightly batch in RMS will update the stock on hand for SKU's at counted store based on the variances. This same nightly batch will calculate the total cost and total retail at subclass location (i.e. store) level. RMS does this in one of two ways:

·         First:

o    if RMS is configured to use stock count auto processing (STAKE_AUTO_PROCESSING = ‘Y’), then x number of days after the count occurs, variances will be automatically applied for all counts that have a snapshot and count results

o    The number of days after the count is based on the cycle count lag days parameter (CYCLE_COUNT_LAG_DAYS)

·         The second method is done using the Stock Count Variance form

o    To approve the count a user should apply all locations that have been verified and select the menu option “Create Stock Adjustments”

o    This sets a flag that indicates the count quantities have been approved and can now be used to update the inventory tables.

o    A batch process will process the adjustments and update the inventory tables

For both methods, the variances are applied in a nightly batch process (Stock Count Stock on Hand Updates) which updates the stock on hand for the items/locations with variances On hand is updated by adding the variance to the current on hand value for the item/location.

·         For Unit counts, this process also posts Stock Adjustment transactions to the Stock Ledger

·         For Unit & Value counts, Stock Ledger records are also written, but to a different transaction code (RA Stock Ledger Adjustment) which is used to communicate the variance for reporting in Retail Analytics

Additionally, for Unit & Value counts, this batch process calculates the value of the count results by multiplying the count quantity by the cost or retail captured in the snapshot

Note: When integrating stock count results from SIM or RWMS, STAKE_AUTO_PROCESSING should be set to Y, as any adjustments to counts would be done in the source application and sent to RMS

9.   Financial Adjustments and Acceptance

Users have an opportunity to see financial results of the stock count in stock count results – dollar view window in RMS. While RMS allows adjustments to financials in this window, it is not recommended to do so. Very limited users should be having access to this form as it is considered a security risk to change the retail and/or cost at this point in the stock count process.

After reviewing the results, users have to accept the dollar count as well. This will stage the data in RMS for the batch to process the financial side of the stock counts.

10. Calculate and Post the Book Stock and Actual Stock

As mentioned earlier, posting unit variances, from a Unit only stock count, directly updates stock on hand and does not post transactions for the RMS stock ledger. Once data is staged by accepting the financial side of stock count results (unit and value stock count), a batch picks up the data to update the stock ledger. Based on the accounting method, closing stock value and shrinkage is calculated.

For each subclass/location in the stock count, book stock is calculated as of the date of the stock count by using the beginning of the month stock value from monthly stock ledger data and summing up the transaction from daily stock ledger data, since the beginning of the month to the date of the stock count. Then difference between book stock value and actual stock value is used to calculate the actual shrinkage amount.

11.  Late Transaction Postings

Scenario 1: Stock Count is open i.e. variances are not posted

Sometimes transactions that happen prior to counts are not processed in RMS due to system or business reasons. These transactions, when processed at a later date as compared to the date when it was supposed to process, are called late transactions. As noted earlier, RMS takes the snapshot after processing all the transactions on the day of stock count. This snapshot is then used throughout the stock count process to calculate variance and late on post adjustments. RMS appropriately updates the snapshot for all late transactions (sales, return, receiving, adjustments etc.) when the stock count is still open.

It is recommended to make sure all pending transactions for the stock count date and prior to that date are successfully processed in RMS before accepting the variances.

Scenario 2: Stock Count is posted (stock on hand updated)

When the stock count is already processed, i.e. stock on hand was already updated with the physical count quantity, in order to correctly offset the transaction, two steps are required. 

1.     Process the transaction as is with correct transaction code

2.     Create an offsetting inventory adjustment transaction to reverse the transaction impact.

Note: a direct journal entry can be made to manually adjust for the budgeted shrink.

3. Stock Count Batches Detail and Process Flow

STKXPLD – Stock Count Explode

–      This program inserts into STAKE_SKU_LOC and STAKE_PROD_LOC tables for all valid item / loc and department / class / subclass / loc combinations for unit and dollar stock counts, based on location, department, class and subclass information stored on stake_location and stake_product

–      This program also inserts into STAKE_SKU_LOC and deletes STAKE_PRODUCT for all unit only counts for warehouses

–      This executes for stock count when

o    Period.Vdate = Stake_Head.Stocktake_Date –                                                                            System_Options.Stake_Lockout_Days

STKUPD – Stock Count Snapshots Update

–      This batch program is used to get the stock on hand for each item-location record on the scheduled stock count day by updating STAKE_SKU_LOC

–      This executes for stock count when

–      Period.Vdate = Stake_Head.Stocktake_Date

STKUPLD – Upload Stock Count Results

–      This batch program accepts cycle count details from an external system and updates the physical_count_qty field on STAKE_SKU_LOC for a given item/location/cycle count combination

–      This program reads a user-created interface file of cycle counts

–      If a record doesn’t exist on STAKE_SKU_LOC the program validates that the item/location exists in system and inserts a record into STAKE_SKU_LOC

–      If a record is non-inventoried item then it is rejected

–      If a record is a pack – update / insert information on STAKE_SKU_LOC for all component items

STKUPLD – Upload Stock Count Results

–      If record is orderable-only transformed item then it is treated as if it is a regular item and marked ‘O’ on xform_item_type column in STAKE_SKU_LOC

–      If record is sellable-only transformed item then the program will roll the physical count quantity of the sellable-only transformed item up to its associated orderable-only transformed item since sellable-only transformed item has no snapshot and mark ‘S’ on xform_item_type column in STAKE_SKU_LOC

–      This program will probably be run at the start of the batch cycle during POS polling, or possibly at the end of the batch run if pending warehouse transactions exist. It can be scheduled to run multiple times throughout the day, as WMS or POS data becomes available

STKVAR – Stock Count Stock-on-hand updates

–      The program updates the stock-on-hand in item_loc_soh

–      It computes and updates the total cost and total retail in the stake_prod_loc

–      The program is driven by STAKE_CONT, in conjunction with STAKE_SKU_LOC where the item, location and cycle count on STAKE_SKU_LOC match those on STAKE_CONT, and where STAKE_CONT run_type = 'A' (for adjustment)

–      New Stock on hand in Item_loc_soh

–      = Item_loc_soh.stock_on_hand

–         – Stake_sku_loc.snapshot_stock_on_hand_qty

–         + Stake_sku_loc.physical_count_qty

STKVAR – Stock Count Stock-on-hand updates

–      STAKE_PROD_LOC total cost and total retail amounts are updated with the total cost and total retail for each department, class, subclass, location combination that exists on the cycle count

Stake_Prod_Loc.Total_Retail

= Stake_Sku_Loc.Snapshot_Unit_Retail

       * (Stake_Sku_Loc.Physical_Count_Qty +

                   Stake_Sku_Loc.Snapshot_In_Transit_Qty)

Stake_Prod_Loc.Total_Cost

= Stake_Sku_Loc.Snapshot_Unit_Cost

       * (Stake_Sku_Loc.Physical_Count_Qty +

                   Stake_Sku_Loc.Snapshot_In_Transit_Qty)

STKDLY – Stock count shrinkage update

–      This program is used only for ‘Unit & Dollar’ type of Stock Count

STKSCHEDXPLD  – Scheduled stock count explode

–      This program explodes schedules to item / location data

–      If a schedule is set to ‘0’, then this batch it is run every day

STKPRG – Purge Stock Count

–      This program is to delete records from the cycle count tables for stock takes with a

                  Stake_head.Stocktake_date 

                                          < System_variables.Last_eom_start_month

4. Stock Count Data Model and Flow

Data Model
Following are the key RMS Tables used in any Stock Count:

§  Stake_head

–      This table contains header level information about stock count events (stocktakes) in the system

§  Stake_location

–      This table contains location information for stock count events (stocktakes) in the system

–      Each stock count can have stores or warehouses, but not both

§  Stake_Product

–      This table contains department information for stock count events (stocktakes) in the system

§  Stake_SKU_Loc

–      This table contains a row for each SKU/location combination for a stock count

§  Stake_Prod_Loc

–      This table contains a row for each dept/class/subclass location combination in the company for which a Physical Inventory is scheduled

§  Stake_Qty

–      This table contains unit stock count result records that are captured for a SKU/location defined for a stock count

§  Stake_Cont

–      This table is used to control the processing during a stocktaking run

–      Only SKU / location combinations present in this table are processed during any particular run

–      All existing rows are deleted and new rows added to this table as part of a stocktaking control run, whether a unit run or a dollar run

§  Stake_Schedule

–      This table will hold stock count schedules

Stock Count Data Flow for Unit Counts at Store Locations

Stock Count Data Flow for Unit & Value Counts at Store Locations

Summary Note

This was a all about Stock Counts in RMS. Again to mention here that these details are per v13.x but not much new things introduces in later version of RMS for Stock Counts. This will still give you a solid foundation to start with RMS Stock Counts. In next article, we are going to talk all about Stock Ledger in RMShttps://oretail.blogspot.com/2017/09/oracle-retail-merchandising-system-rms_28.html which is one of the most interesting topic.

You may also want to refer to parent article Oracle Retail Merchandising Systems - Deep Dive. Do drop a note in case you have any question, comments or feedback.

— 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.