Complete Oracle Retail Tutorials and Training Materials by Nagesh Mishra

Welcome again Oracle Retail Fans. Somewhere I read a question asking that “Why Stock Ledger is difficult?” One thing I can assure you that after going through this article, at least we should not have this misconception and you should have a solid understanding of all about Stock Ledger in RMS (Oracle Retail Merchandising System). This is one of the most interesting topics to discuss and write about. Please feel free to drop a note if you have any question.

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.

I’ll be covering following details about Stock Ledger:

Stock Ledger Overview
Understanding Stock Ledger Attributes
Stock Ledger Transactions
Retail v/s Cost Inventory Method of Accounting and its Usage
Stock Ledger Integration with GL
Stock Ledger Batches
Stock Ledger Data Model and Flow
Stock Ledger RMS Application Forms

We’ll be trying to understand about various Stock Ledger concepts and attributes before diving deep into the implementation of Stock Ledger using two different accounting methods.

1. Stock Ledger Overview

Stock Ledger is a record of the value and volume of stock in any business and its location. It records and summarizes the financial results of transactions taking place in the various merchandising processes, which include buying, selling, price changes, stock adjustments, and transfers etc. RMS Stock Ledger is used to record the merchandising transactions taking place in the retail business. These transactions can broadly be divided into three categories:

Below is the simple high level flow for Stock Ledger Inventory Movements:

Ø Purchase Order Receipt and Transfer In transactions cause an increment of stock on hand. These are recorded as part of transaction data and eventually affect the stock ledger.

Ø Return to Vendor and Transfer Out transactions result in the shipping out of merchandise and cause reduction in stock on hand. These transactions are recorded as part of transaction data and affect the stock ledger.

Ø Transactions such as Stock Count and Inventory Adjustments do not result in actual movement of inventory, but these nonetheless affect the inventory position. In addition, these form a part of the transaction data set and are written to the stock ledger.

2. Understanding Stock Ledger Attributes

It is important to understand the various Stock Ledger attributes in RMS before getting into the actual implementations and understand how those transactions are posted and where all it impacts.

Following are the key Stock Ledger attributes. Please refer RMS System Options & Configurations for the detailed explanation and location for each of these.

Calendar

RMS supports a normal or Gregorian calendar, as well as a standard retail or 4-5-4 calendar. For details on Calendar, please refer detailed articles on RMS Calendar

Currency

Base Currency

A base currency for the system can be set up through the parameter CURRENCY_CODE. Values for this field are validated from the table CURRENCIES in RMS. All currency conversion rates in RMS are defined in terms of the system's base currency.

Multiple Currency

The RMS stock ledger gives you the flexibility to use multiple currencies in a single instance of RMS. There can be different currencies at the location level and corporate level. This is to facilitate recording location level transactions in the local currency but corporate level reporting in a standard base currency.

The indicator ‘MULTI_CURRENCY_IND’ indicates whether RMS supports multiple currencies. The only support value is ‘Y’. However, this does not require that multiple currencies are used in the system.

Default Tax Type

The parameter ‘DEFAULT_TAX_TYPE’ determines what type of taxation is used in RMS. The possible options are Simple VAT (SVAT), Sales Tax (SALES), and Global Tax (GTAX).

Stock Ledger Retail VAT Inclusive

The indicator ‘STKLDGR_VAT_INCL_RETL_IND’ specifies whether retail values in stock ledger are VAT inclusive or not.

Stock Ledger Location Level

The system option ‘STOCK_LEDGER_LOC_LEVEL_CODE’ determines the location level the stock ledger runs at - Location or All Locations

Stock Ledger Product Level

The system option ‘STOCK_LEDGER_PROD_LEVEL_CODE’ determines the product level the stock ledger runs at - SKU (K), Subclass (S), Class (C), or Department (D).

Consolidated Exchange Rate

The Consolidation Exchange Rate indicator (CONSOLIDATION_IND) determines whether a consolidation or operational exchange rate is used for currency conversion and thereby whether Oracle Retail supports the addition, maintenance, and viewing for the consolidation exchange rate in the Pending Exchange Rate Maintenance process

Time Interval

The Time Interval indicator ‘STOCK_LEDGER_TIME_LEVEL_CODE’ determines the time periods that are used to run the RMS stock ledger. The valid values for this parameter are Month (M) and Week (W).

Cost Method

The STD_AV_IND parameter indicates whether standard cost or average cost is used for inventory and gross profit calculations. This processing is applicable to all the departments, whether configured to use the cost or retail method of accounting.

Valid values are ‘S’ for standard and ‘A’ for average

Budgeted Shrink Indicator

The Budgeted Shrink Indicator (BUD_SHRINK_IND) designates whether actual shrinkage amounts are calculated based on inventory adjustments and cyclical counts, or based on budgeted shrinkage percent in the period ending inventory calculations in the stock ledger.

Estimated Landed Cost

The landed costs functions allow you to define expenses, assessments, up charges, and combinations in order to track the costs involved in purchasing and moving goods from the manufacturer to a distribution center or store. Estimated landed cost (ELC) is the bottom-up cost estimate created by the buying organization. ELC is comprised of cost components from the supplier, trading partners, item, origin country, and banks which are brought together during the purchase order (PO) creation in order to develop an estimate of costs associated with purchasing a particular item on the current PO.

Within RMS, landed cost is defined using computation value bases (CVB), expenses, and assessments. Expenses and assessments are more generically referred to as cost components. Computation value bases describe how expenses and assessments are combined in order to provide a base for the calculation of other expenses and assessments. Assessments differ from expenses in that they are defined by a government agency.

The ‘ELC_IND’ indicator determines if the ELC is used in the system. ELC is primarily used for estimating the cost of the item as close as possible to the final, actual landed cost by adding cost components to the PO cost. Note that this functionality is not compatible with the use of standard cost. While posting transaction records for purchases (tran code 20), the purchase value net of ELC is also recorded in the TOTAL_COST_EXCL_ELC column.

General Ledger Rollup Level

The system option ‘GL_ROLLUP’ specifies the roll up level of Oracle Retail’s general ledger information when bridged to a financial system. Valid values are – ‘D’ - Department, ‘C’ - Class, and ‘S’ - Subclass.

Multiple set of Books

From version 13.0 onwards, RMS enables multiple set of books in the system. Therefore, a retailer can create multiple financial books in the general ledger, and the RMS stock ledger can integrate to these different sets of books.

An inter-company transfer occurs when product is transferred between locations with different transfer entities or locations with different set of books. RMS gives the flexibility to determine whether a transfer is an intercompany transfer based on its being between locations with different set of books or different transfer entities using the ‘INTERCOMPANY_TRANSFER_BASIS’ system option.

A transfer entity is a group of locations that share legal requirements around product management and are part of the same legal entity.

The indicator ‘INTERCOMPANY_TRANSFER_IND’ indicates whether or not inter-company transfers are used within RMS. When this indicator is set to Y, each stockholding location (store, warehouse, and external finisher) is associated with a transfer entity. When this indicator is set to N, inter-company transfer functionality is not used.

Financial Integration

The RMS stock ledger may or may not be integrated with an external financial system. The indicator ‘FINANCIAL_IND’ identifies whether an external financial application is integrated. Valid values are ‘Y’ and ‘N’.

The indicator ‘FINANCIAL_AP’ indicates the external financial system being used by the business. Valid values are ‘O’ (Oracle EBS Financials), ‘A’ (PeopleSoft) and NULL (third party). This setting controls supplier entry and general ledger (GL) cross reference maintenance.

Minimum / Maximum Cumulative Mark-on Percent

Cumulative Mark-on Percent is used to estimate the ending inventory at cost when using the retail method of accounting (more on this later in this section) in RMS. RMS also gives the flexibility to set a minimum and maximum cumulative mark-on percentage (MAX_CUM_MARKON_PCT and MIN_CUM_MARKON_PCT).

The cumulative mark on % is calculated by the weekly stock ledger program (or monthly if not using weekly accounting). It is calculated using beginning balances and beginning markon % and then adding transactions from the current week to establish the new value. Transactions for the following week then use the cumulative markon % for the transactions (for example, transfers, sales, and so on) that was established at the end of the previous week (or month).

If the calculated value lies outside the max and min tolerances, then the system uses the value defined as Budgeted Intake % (BUD_INT). This value is also defined at the department level. If it lies within the tolerances, the calculated percent is used in calculating the ending inventory at cost. The use of the ‘MAX_CUM_MARKON_PCT’ and the ‘MIN_CUM_MARKON_PCT’ is optional.

Receiver Cost Adjustment Type

This indicator determines how the receiver cost adjustment is carried out in the system. The valid values of F (FIFO) or S (Standard) are stored in the CODE_DETAIL table with code type of ‘RUCA’.

Retail Markdown for Transfer

This parameter determines which location takes the markdown ‘hit’ in the case where a transfer between locations have different retails at the sending and receiving locations.

There are four indicators for controlling the markdown recording in RMS for different combinations of ‘To Location’ and ‘From Location’. For each of the following system options, the user can specify whether sending location ‘S’ or receiving location ‘R’ will get the retail markdown difference

Retain Transaction Data

The parameter ‘TRAN_DATA_RETAINED_DAYS_NO’ specifies the number of days that transaction-level stock ledger data is kept in the system.

Stock Count Processing

The indicator ‘CLOSE_MTH_WITH_OPN_CNT_IND’ is used to determine whether or not the current fiscal month is allowed to be closed while containing an open ‘Unit and Value’ stock count.

NWP Processing

NWP refers to ‘Niederstwertprinzip’ and is a legal German accounting financial inventory reporting requirement for calculating year-end inventory position based on the last receipt cost. The NWP Indicator supports this German specific inventory reporting requirement. For German customers, this setting needs to be 'Y' to allow for the annual NWP calculations and processes. This setting is not relevant for customers outside Germany.

NWP calculates the end of year inventory values in each store as of December 31st of the previous year. To determine the correct end of year inventory value for this report, the following process occurs:

· A stock count is performed for every store in the early part of the year.

· The variance determined in this count is applied to the book stock as of the end of the year to determine more accurate end of year inventory values.

· End of the year stock units are revalued based on the lower of last received cost or weighted average cost for the items at each location during the previous year

3. Stock Ledger Transactions

To facilitate calculation of inventory by the stock ledger, RMS records every transaction involving movement of merchandise with a TRAN_DATA record. For every transaction, RMS captures its Department, Class, Subclass, Transaction Date, Units, Cost and Retail. In case of transactions involving packs, transaction data is recorded for the component items and not packs.

These transactions are recorded in multiple tables in the system in various stages of processing. The three tables that are used in combination for the processing of transaction data are TRAN_DATA, IF_TRAN_DATA, and TRAN_DATA_HISTORY. Initially, for each type of transaction that occurs in the system involving the movement of merchandise, a record is written to the transaction data table at the item/location/transaction level. (Note that there are a set of tables called TRAN_DATA_A and TRAN_DATA_B that allow for continuous processing of transactions and recording of TRAN_DATA records in RMS during the batch cycle. TRAN_DATA is a database view.)

Following are the transaction codes that are currently used in RMS as on v 13.x.

RMS Tran Codes	High Level Discriptions
01 - Sales/Returns	This record captures the units sold (or returned), the retail value of the units, and the cost of the units (average or standard cost based on system option setting). The retail value is the equivalent of the sale price captured at the POS and not the value in RMS. For example, it includes any promotional discounts applied to the sale, which may not be reflected in RMS retail price. Any difference in the unit retail in RMS and actual sales retail is accounted through specific tran codes for markup/ markdown. The quantity, retail, and cost amounts are positive numbers for sales and negative numbers for returns. The cost and retail values captured in these records are rolled up to the subclass/day, subclass/week, and subclass/month levels and are used in the ending inventory calculations for those periods. This tran code will include VAT in the value, if used in RMS.
02 - Sales excluding VAT	This transaction type is similar to Net Sales (Tran Code – 01) and written along with tran code 01 in the cases when tax type selected at the system level is SVAT or GTAX and ‘Stock Ledger Retail VAT Inclusive’ = Yes (STKLDGR_VAT_INCL_IND = 'Y'). This record gives visibility to sales without the VAT value. If VAT is not included in the stock ledger, then records are not written with this transaction code. These transactions are rolled up to the subclass/day, subclass/week, and subclass/month levels; however it is not used in the ending inventory calculations.
03 - Non-inventory sale	This type of transaction is also recorded in RMS by the sales upload process, and it includes all the sales/ return transactions at POS for non-inventory items. These records are the items for which item’s inventory indicator (INVENTORY_IND) is ‘N’. For these transactions too, sales are positive records and returns are negative. Retail is the item’s sales price, and cost is WAC or standard cost, depending on system option. The summarization of sales and return transactions is similar as Net Sales (Tran Code – 1). Sales recorded under this tran code is not part of the Net Sales (Tran Code – 1). These records are not used for the ending inventory calculations but rolled up to subclass/location level for day, week and month in the stock ledger to interface it to the general ledger.
04 - Customer Returns	This transaction type is recorded for all customer returns processed through the sales upload process in RMS. This is the only place in the system in which there is visibility to customer returns, since customer returns are not written to the sales history tables in the system. Units returned, total retail, and total cost is captured for each return transaction. Return transactions are rolled up to the subclass/day, subclass/week and subclass/month level. However, because customer returns are part of the ‘Net Sales’ transaction, ‘Returns’ are not used in the ending inventory calculations.
05 - Non-inventory sale excluding VAT	This transaction type is written for the same criteria as the 'Non-inventory Items Sales/Returns (Tran Code - 03)' transaction described above, if the system parameter is set that indicates VAT should be included in records written to the stock ledger. When it is written, it is in conjunction with the ‘Non-inventory Items Sales/Returns’ record in order to give visibility to sales without VAT. If VAT is not included in the stock ledger, then records are not written with this transaction code. This transaction code is similar to Tran Code – 02. These records are not used for the ending inventory calculations but are rolled up to subclass/location level for day, week and month in the stock ledger to interface it to the general ledger.
06 - Deal Income - Sales	This transaction code is recorded to post income generated from bill back rebate or vendor funded promotion types of deals that are calculated based on sales. These are the deals from which the retailer gets income as certain part of sales. The amount of income may differ based on predefined threshold sales levels and percentage thereof. For example – A supplier may give 10% discount on every $10,000 worth of sales and 15% discount if the sales reaches $20,000. Since, this transaction is based on sales; the posting is at retail only. This transaction is created by the daily deal income and sales upload batch programs. The latter posts deal income transactions only in case of Vendor Funded Promotions. For these transactions the daily data roll up table is updated by the daily deal income batch rather than sales daily batch. Additionally, deal ID is also recorded in the reference number column. These transactions are rolled up to the subclass/day, subclass/week, and subclass/month levels; however, it is not used in the ending inventory calculations.
07 - Deal Income – purchases	This transaction is recorded to post income generated from bill back or bill back rebate deals that are calculated based on purchases. The income from these deals is dependent upon the quantity of purchases made by the retailer from the vendor, and deal income may differ based on predefined threshold levels of purchases. For example – A supplier may give 10% discount if the purchase of a particular item is more than $10,000, and if the total purchase in a year reaches $200,000 then extra 2% is given to the retailer. These transactions are posted at cost based on the purchase cost. This transaction is created by the daily deal income batch program. For these transactions the daily data roll up table is updated by the daily deal income batch rather than sales daily batch program. Additionally, deal ID is also recorded in the reference number column. These transactions are rolled up to the subclass/day, subclass/week, and subclass/month levels; however it is not used in the ending inventory calculations.
08 - Fixed Income Accrual	Income arising out of fixed deals is recorded under this transaction code. Fixed deals are a lump sum deal based on proof of performance. For example: if a retailer highlights a particular product in their weekly sales flyer (and sends that flyer to the product’s manufacturer as proof), then the manufacturer gives the retailer $100. This transaction is recorded at cost. These transactions are not recorded in TRAN_DATA table. The deal fixed income batch program writes these transactions to the financial staging table for the general ledger posting process. It splits deal income over all dept/class/subclass locations on the deal. This prorated income is written to the general ledger under a suitable cost center mapping.
10 - Weight Variance	This transaction is used to capture the variance arising out of the difference between nominal weight and actual weight during the sale and inventory adjustment of a catch weight type 3 or 4 item. · Catch Weight Type 3 Items – This type of item is purchase in fixed weight simple packs containing a fixed number of eaches and sold by variable weight eaches (for example, pre packaged cheese) · Catch Weight Type 4 Items– This type of item is purchase in variable weight simple packs containing a fixed number of eaches and sold by variable weight eaches (for example, pre-packaged sirloin steak) These types of catch weight items have a nominal weight defined for them. At the time of sales, actual weight is captured, which may be different from the nominal weight defined. For example: A bag of apples can have nominal weight as 1 kg but actual weight can be more or less than 1 kg. This is posted at retail and not used for inventory calculation.
11 - Markup	When the retail price of an item in a transaction is over its current price, this is recorded as a markup. Markup is recorded in the following cases: Price Changes When the retail price of an item is increased over its current price because of a permanent price change, it is recorded as markup under this tran code. However in some cases, a clearance or promotional markdown can also end up increasing the price. In these exceptional cases, a markup transaction will also be captured. This record is a location specific record and the unit quantity considered for posting is the stock on hand for the item in that location, along with the in-transit quantity. The time for posting this record is the effective date of the price change. That is, this transaction is posted at the point in time when the new price for that item becomes effective in RMS. Price changes are inserted in RMS by RPM daily batch. Pack Component Sales In the case of pack sales, when the retail value of the sale is prorated to the component items, a markup may be written if the prorated retail is higher than the current retail price of the item in RMS. This record is a location specific record and the unit quantity considered for posting is quantity sold (pack quantity sold * no. of components in pack) in that transaction. The time of posting this transaction is when sale is recorded. Example for Sales: Item A is a component of Pack X at location Z. Pack X contains 15 units of item A and has a retail of $300. The unit retail of item A is $15. Store Z sells 2 units of Pack X on January 2nd. The total retail value posted as markup on January 2nd is $150. This is calculated as: Markup Retail = Component Units sold * ((Pack unit retail/No. of components) – Per unit retail of component item) Markup Retail = 30 * ((300/15) – 15) = $150 Transfers This transaction is written in the case of transfer if the unit retail at the sending location is less than the receiving location’s retail. The location for which it is posted will be as per the system option (defined in the System Options section) for determining markdown location – either sending or receiving location. The quantity considered will be the quantity in the shipment and it will be posted when transfer status is moved to ‘shipped’. The transfer number is also captured in the reference number field of this transaction. Example for Transfer: Item A regular retail is $10 at location X and $12 at location Y. 10 units are transferred from location X to location Y. If the system option is set for the receiving location to take the markup, then the transfer will be valued at $100 ($1010) and a markup of $20 (($12-$10)10) for the receiving location. If the system option is set for the sending location to take the markup, then the transfer will be valued at $120 ($1210) and a markup of $20 ($12-$10)10) is written for the sending location. In the daily and periodic stock ledger processes, these transactions are rolled up to the subclass/day, subclass/week, and subclass/month levels. Only a retail value is associated with a markup, and therefore markups are only included in the ending inventory calculations for retail.
12 - Markup Cancel (requires customization)	A markup cancellation is the decreasing of the retail price of an item until the point of its original retail. In other words, this is cancelling a previous markup in part or full. Because this transaction can be misused to disguise the markdown, RMS does not systematically capture this transaction code in the transaction data tables. Instead, it captures all decreases in price as a markdown. If a retailer wants to automatically record this in certain cases, it is a customization of the system in order to do so.
13 - Markdown	When the retail price of an item in a transaction is below its current price, this is recorded as a Promotional Markdown. When the regular retail of an item changes, and is lowered, it this will also be recorded as a Permanent Markdown. A permanent markdown is recorded in the following cases: Price Change When retail price of an item is reduced below its current price because of a permanent or regular price change, it is recorded as permanent markdown under this tran code. They can also happen because of changing of a zone group for an item or the changing of the zone in which stores exists for a particular zone group. This record is location specific and the unit quantity considered for posting is the stock on hand for the item in that location, along with the in-transit quantity. The time for posting this record is the effective date of the price change. That is, this transaction will be posted at the point in time when new price for that item becomes effective. Price changes are inserted in RMS by the RPM daily batch. The total retail value is calculated as the difference between the new retail and the old retail, multiplied by the total unit quantity. Pack Component Sales In the case of pack sales, when the retail value of the sale is prorated to the component items, a permanent markdown may be written if the prorated retail is lower than the current retail price of the item in RMS. This transaction code is inserted daily by the Sales upload batch program to record the difference. This record is location specific and the unit quantity considered for posting is quantity sold (pack quantity sold * no. of components in pack) in that transaction. The time of posting this transaction is when sale is recorded. Example for Sales: Item A is a component of Pack X at location Z. Pack X contains 15 units of item A and has unit retail of $150. Unit retail of item A is $15. Store Z sells 2 Unit of Pack X on January 2nd. The total retail value posted as markdown on January 2nd will be $30. This is calculated as: Markdown Retail = Component Units sold * (Per unit retail of component item – (Pack unit retail/No. of components)) Permanent Markdown Retail = 30 * (15 – (150/15)) = $150 Transfers This transaction is written in case of transfer, if the unit retail at the sending location and the receiving location is different. The location for which it is posted is as per the system options (defined in the System Options section) for determining markdown location – either the shipping or receiving location. The quantity considered is the quantity in the shipment, and it will be posted when transfer status is moved to ‘shipped’. The transfer number is also captured in the reference number field of this tran code. Example for Transfer: Item A regular retail is $12 at location X and $10 at location Y. 10 units are transferred from location X to location Y. If the system option is set for the receiving location to take the markdown, then the transfer will be valued at $120 ($1210) and a markdown of $20 (($12-$10)10) for the receiving location. If the system option is set for the sending location to take the markdown, then the transfer will be valued at $100 ($1010) and a markdown of $20 ($12-$10)10) will be written for the sending location. These transactions are rolled up to the subclass/day, subclass/week, and subclass/month levels. Only a retail value is associated with a markdown and therefore these are only included in the ending inventory calculations for retail.
14 - Markdown Cancel	This transaction is recorded when an item had a price change decreasing it from the original price and now it is increased over its current retail but below its original retail. It is basically cancelling the part of markdown already applied on the price. A markdown cancel will typically result from a permanent price change. Markdown cancels are recorded at the point in time that the new price for the item goes into effect in RMS (that is, the effective date of the price change). The unit quantity that makes up the transaction record is the stock on hand for the item at the location, along with any in-transit quantity. The total retail value is calculated as the difference between the new retail and the old retail, multiplied by the total unit quantity. These transactions are inserted by the RPM daily batch when a price change goes into effect. The ‘Markdown Cancel’ transactions are rolled up to the subclass/day, subclass/week, subclass/month levels and also used in the ending inventory calculations for a particular period. Only a retail value is associated with a markdown cancel and therefore markdown cancels are only included in the ending inventory calculations for retail.
15 - Promotional Markdown	Promotional Markdown is the result of a temporary reduction in the price of an item for a limited time period, but is only recorded for actual units sold. This temporary reduction is recorded through this transaction code in the following cases: Promotional Price Change Promotional Markdown is recorded at the time of sales of an item for which a temporary reduction in the price is created in RPM. This is basically a planned markdown and usually it is for a limited time period for promoting sales. Promotional markdowns are not taken at the time a promotion goes into effect, but rather at the time of a sale. This transaction is inserted by the sales upload batch program. For Example: New promotional price of $15 for Item A gets effective from January 2nd (Original price $17). 5 units sold on January 22nd. Total retail value posted as promotional markdown on January 2nd will be $10. This is calculated as: Promotional Markdown Retail = (SOHOld Retail) – (SOHNew Retail) Promotional Markdown Retail = (517) – (515) = $10 Promotional markdowns are also recorded when a price of an item is overridden at the time of actual sales and sold at a higher or lower price but not part of an RMS/RPM initiated promotional event. In the case of a price reduction, there is a positive value for this transaction and in the case of price increase, there is a negative value recorded equal to the difference between regular price and new price. This transaction is inserted by the sales upload batch program. The unit quantity that makes up the promotional markdown transaction record is the units sold for the item at the location. The total retail value is calculated as the difference between the new retail and the old retail, multiplied by the total unit quantity. The ‘Promotional Markdown’ transactions are rolled up to the subclass/day, subclass/week, subclass/month levels and also used in the ending inventory calculations for a particular period. Only a retail value is associated with a promotional markdown and therefore promotional markdowns are only included in the ending inventory calculations for retail.
16 - Clearance Markdown	Clearance Markdown is a permanent decrease in the retail price of an item resulting from a clearance event created in RPM. These are recorded at the point in time that the new clearance price for the item goes into effect in RMS (that is, the effective dates of the clearance markdowns). The unit quantity that makes up the transaction record is the stock on hand for the item at the location on the effective date, along with any in-transit quantity. The total retail value is calculated as the difference between the new retail and the old retail, multiplied by the total unit quantity. For Example: New clearance price for Item A ($15) gets effective from January 2nd (Original Retail $17). SOH on that day for item A at location X is 150 units. Total retail value posted as clearance markdown on January 2nd will be $300. This is calculated as: Clearance Markdown Retail = (SOHOld Retail) – (SOHNew Retail) Clearance Markdown Retail = (15017) – (15015) = $300 The ‘Clearance Markdown’ transactions are rolled up to the subclass/day, subclass/week, and subclass/month levels and also used in the ending inventory calculations for a particular period. Only a retail value is associated with a clearance markdown, and therefore clearance markdowns are only included in the ending inventory calculations for retail.
17 - Inter-company Markup (sending location)	When goods are transferred between two locations which are under different business entities then these transfers are known as Intercompany Transfers. (Refer to the ‘Multiple Set of Books’ in the RMS system options section for options related to intercompany transfers.) Intercompany Transfers are treated as a purchase/sale between business entities rather than just a movement of merchandise. In the case of intercompany transfers, if the Transfer Price specified on the transfer is more than the retail price of the item at the sending location, the difference between transfer price and the retail is recorded as Intercompany Markup. Because intercompany transfers are treated as sales in the sending location, in the case of markup, it is recorded for the sending location. These transaction data records are rolled up to the subclass/day, subclass/week, and subclass/month levels and also used in the ending inventory calculations for a particular period.
18 - Inter-company Markdown (sending location)	This transaction is recorded when the price of an Intercompany Transfer is less than the retail value of the merchandise at the sending location. The difference between the transfer price and the retail is recorded as Intercompany Markdown. As intercompany transfers are treated as sales in the sending location, in case of a markdown, it is recorded for the sending location. This transaction data is rolled up to the subclass/day, subclass/week, and subclass/month levels and also used in the ending inventory calculations for a particular period.
20 - Purchases	This transaction is written for purchases of merchandise made by the retailer from a vendor when RMS processes the receipt of a purchase order. Also, in the case of a (receiver cost adjustment) RCA or receiver unit adjustment (RUA), this record is written. This transaction writes the units received, total cost and total retail for all the items on the receipt. The cost recorded is the cost on the purchase order, that is, the purchase cost for the particular receipt, plus ELC, minus off invoice deals, if applicable. The retail recorded is the regular retail or clearance retail (whichever is effective at the time of receiving) of the item at the receiving location. Purchase order number and shipment number for the receipt is also recorded for reference in this transaction record. In the case of RCA and RUA, ‘C’ and ‘U’ respectively is recorded under the adjustment code column of this transaction record.
22 - Stock Adjustments	This transaction code is written in three cases: first, when there is an adjustment to the total stock on hand; secondly, when a unit stock count results in adjustment to total stock on hand; and thirdly, when spoilage type wastage configured for an item is processed. In the case of an adjustment to the total stock on hand, the reason code associated with the adjustment determines the tran code to which the adjustment is written – either 22 or 23. Those that are classified as ‘Cost of Goods Sold’ adjustments write to tran code 23; others write to tran code 22. The reason code is captured on the transaction data record for the reporting purposes in the GL reference number field. For stock counts, stock adjustment records are written under this tran code only in the case of a ‘Unit Only’ count. ‘Unit and Value Counts’ are recorded under tran code – 41, for interfacing to Retail Analytics. In this case, stock count number is recorded in the first reference field. For both cases, the units adjusted are recorded to the table, along with the cost and retail extensions. Total cost = Number of units * Average or Standard Cost (depending on the system parameter setting) Total retail = Number of units * Unit Retail. This transaction code is also inserted when waste adjustment is processed for items with a waste type of ‘SP’ (spoilage). This waste type is used to account for natural wastage that occurs over the shelf life of the product. The waste adjustment batch program (wasteadj.pc) writes this transaction. The cost can be weighted average cost (WAC) or standard cost depending on the options settings and retail is the regular retail. Stock adjustments transactions are rolled up to the subclass/day, subclass/week and subclass/month levels. If a Unit & Value inventory is not performed within the stock ledger period (month), and budgeted shrink is not used, then the total cost and retail value of all stock adjustments for the period are summed up and used as the shrinkage amount in the ending inventory calculations. For details of various scenarios please refer to the ‘Budgeted Shrink Indicator’ section under RMS System Options.
23 - Stock Adjustment COGS	This transaction code is used similarly to how the tran code 22 is used for inventory adjustments. RMS determines which tran code to record based on the reason code for the inventory adjustment. Those with the COGS indicator = Y will write to this tran code. Inventory adjustments that are flagged with the COGS indicator = Y, unlike tran code 22, will always be included in the ending inventory calculation, regardless of the budgeted shrink indicator.
24 - RTV From Inventory	Transactions with this code are written when the inventory is returned to vendor from owned inventory. This record is inserted as soon as the RTV shipment is processed by RMS. The RTV transaction is written based on the number of units returned to the vendor and the cost and retail of the unit on the RTV. When an RTV is created, a cost can be specified for the item differing from the average or unit cost of the item, representing the amount of money that the vendor is willing to pay the retailer for the goods. This is the cost that is used to create the RTV transaction in the stock ledger. If no cost is specified, then the average cost of the item/location is used as the RTV cost or the last receipt cost based on the system option (RTV_UNIT_COST_IND) selected. Valid values for this option are ‘R’ (last receipt cost), ‘S’ (standard cost) or ‘A’ (average cost). In this case, the retailer is using standard cost as the cost method, this can be only be ‘S’ or ‘R’. Similarly, when using average cost as cost method, this can be only be ‘A’ or ‘R’. Additionally, the RTV order number is written to the transactional data record in first reference column for reporting and reference purposes. The ‘Return to Vendor’ transactions are rolled up to the subclass/day, subclass/week, and subclass/month levels and also used in the ending inventory calculations for a particular period. When the RTV cost is different from the inventory cost of the item, then the cost variance transaction code 71 or 72 is recorded depending on the department uses cost or retail method of accounting respectively.
25 - Unavailable Inventory Transfer	The Unavailable Inventory Transfer type of record is written when inventory is moved from regular stock or ‘available’ status to an ‘unavailable’ status in RMS or vice versa. This is inserted immediately upon completion of an inventory adjustment with an inventory status other than Total Stock On hand. This is also created when there is an RTV from unavailable inventory or a transfer of unavailable inventory. In the case of a transfer, this is posted for ‘from’ location at the time of shipment and at the time of receipt at the receiving location, if received in unavailable inventory status. This transaction is recorded with the associated units, but no cost or retail, because moving stock to unavailable status does not have a financial impact on the inventory. This type of transaction is for reporting purposes only and is not used in the ending inventory calculations. Additionally, the inventory status code, which corresponds to the unavailable bucket to which the stock was moved, is also captured in the second reference column and in the case of transfer of unavailable inventory, the transfer number is captured in the first reference column with the transaction for reporting and reference purposes.
26 - Freight Cost (Customization needed)	The freight transaction is a cost transaction that is used in the retail method only. However, data is not currently captured in this transaction type as part of the base RMS functionality. Freight is included in the ending inventory calculations for retail, so modifications can be made to the system in order to begin capturing data in this transaction type, depending on the data that is needed and how it needs to be collected.
27 - RTV From QC	This transaction was originally written to record return of those goods to the vendor that fail the quality test at the time of receipt. This Tran Code is no longer used.
28 - Up Charge Profit – Receiving location	Up charges are miscellaneous charges, for example: transportation costs, which can be added to the cost of the transfer or allocation. These charges are applied to the receiving location’s transfer cost based on dept/from/to location configuration and transfer type configuration. These can be specified as either ‘Profit’ or ‘Expense’ to facilitate general ledger posting in different accounts as per retailer’s preference. While there is no specific functionality that differs between Profit and Expense type Up Charges, various tran codes are used to allow for different general ledger treatment. Examples of Profit type Up Charges could include “Warehouse Handling Fees” or “Storage Fees” that a company’s warehouses may charge to help them operate as a profit center. Expense type up charges would be things such as “Freight” or “Insurance,” which are actual internal costs, but are applied as an estimate with Up Charges. In this transaction code, Location Up Charge components that have been added to Transfers or Allocations with an Up Charge Type of ‘Profit’ are recorded. This record is inserted for the receiving location as soon as the shipment of the transfer or allocation is processed by RMS. The transfer or allocation number is recorded in the first reference field of this tran data record. These transactions are rolled up to the subclass/day, subclass/week, and subclass/month levels; and used in the ending inventory calculations at cost only. These costs are added to the stock ledger cost value of the item and the weighted average cost at the receiving location.
29 - Up Charge Expense – Receiving location	Up charges are miscellaneous charges, for example: transportation costs, which can be added to the cost of the transfer. These charges are applied to the receiving location’s transfer cost based on dept/from/to location configuration and transfer type configuration. In this transaction code, Location Up Charge components that have been added to Transfers or Allocations with an Up Charge Type of ‘Expense’ are recorded. This record is inserted for the receiving location as soon as the shipment of the transfer or allocation is processed by RMS. The transfer or allocation number is recorded in the first reference field of this tran data record. These transactions are rolled up to the subclass/day, subclass/week, and subclass/month levels; and used in the ending inventory calculations at cost only. These costs are added to the stock ledger cost value of the item and the weighted average cost at the receiving location.
30 - Transfer In	This transaction type is used to capture movement of inventory into a location based on the transfer or allocation of merchandise from another location. These records are inserted when the transfer is processed by RMS and moves to ‘Shipped’ status in conjunction with the ‘Transfer Out’ record. This is because at the point the inventory leaves the sending location it is financially owned by the receiving location. This tran code applies for all transactions that involve inventory moving between two company owned stockholding locations in the same transfer entity or set of books – both transfers and allocations. Under this transaction type, number of units, total cost and total retail of the transaction are captured. The cost and retail is the same as the cost and retail on the accompanying ‘transfer out’ transaction (see transaction code 32 for details) for the sending location. Additionally, the RMS transfer number is captured as part of the transaction record for reporting and reference purposes. The ‘Transfer In’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and used in the ending inventory calculations for a particular period.
31 - Book Transfer In	This transaction type is used to record a transfer with transfer type of ‘Book Transfer’ for the receiving location. This transfer is created between virtual locations. This record is inserted when the transfer status is changed to ‘approved’. This transaction is written along with Book Transfer Out (Transaction Code – 33) and uses the same cost and retail as transaction code 33. See transaction code 33 for details of the cost and retail calculation. The ‘Book Transfer In’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and used in the ending inventory calculations for a particular period. Additionally, the RMS transfer number is captured as part of the transaction record for reporting and reference purposes.
32 - Transfer Out	This transaction type is used to capture movement out of inventory from a location based on a transfer or allocation of merchandise to another location. These records are inserted when the transfer is processed by RMS and moves to ‘Shipped’ status in conjunction with the ‘Transfer in’ record. This is because at the point the inventory leaves the sending location it is financially owned by the receiving location. This tran code applies for all transactions that involve inventory moving between two company-owned stockholding locations in the same transfer entity or set of books – both transfers and allocations. Under this transaction type, number of units, total cost and the total retail of the transaction are captured. Additionally, the RMS transfer number is captured as part of the transaction record for reporting and reference purposes. The ‘Transfer out’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and used in the ending inventory calculations for a particular period.
33 - Book Transfer Out	This transaction type is used to record a transfer with transfer type of ‘Book Transfer’ for the sending location. This transfer is created between virtual locations. This record is inserted when the transfer status is changed to ‘approved’. This transaction is written along with Book Transfer In (Transaction Code – 31) and uses the same cost and retail of sending location. The cost and the retail will be calculated as follows: o Total Cost (cost accounting method): Number of units transferred * Average Cost or standard cost at sending location o Total Cost (retail accounting method): (Number of units transferred * Unit Retail at sending location) * (1 – Cumulative Mark on %) o Total Retail: Number of units transferred * Unit Retail at sending location The ‘Book Transfer Out’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and used in the ending inventory calculations for a particular period. Additionally, the RMS transfer number is captured as part of the transaction record for reporting and reference purposes.
34 - Reclassification In	Reclassification In transactions are written when an item is moved, or reclassified, from one department/class/subclass to another, to record the movement of inventory ‘in’ to the new subclass. Transaction data records are written for each location in which the item being reclassified exists in the system based on owned inventory at the location. This transaction is written when a reclassification event is processed by the batch process Reclassification of Item (reclsdly). For every ‘Reclassification In’ transaction for the location, a ‘Reclassification Out’ transaction also exists and the two transaction data records balance one another. The units recorded in this transaction are calculated based on total stock on hand and in-transit for each item/location. The total cost and retail are calculated based on the extended weighted average cost (or standard cost) and unit retail for the item/location. The effect is to move the current inventory value of the item from one department/class/subclass to another. The cost and the retail are calculated as follows: o Total Cost (cost accounting method): Number of units on hand * A o Total Cost (retail accounting method): (Number of units on hand * Unit Retail at the location) * (1 – Cumulative Mark on %) o Total Retail: Number of units on hand * Unit Retail at the location The ‘Reclassification In’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and are used in the ending inventory calculations for a particular period. Cost or standard cost at the location
36 - Reclassification Out	‘Reclassification Out’ transactions are written when an item is moved, or reclassified, from one department/class/subclass to another, to record the movement of inventory ‘out’ from the old subclass. The transaction data records are written for each location in which the item being reclassified exists in the system based on owned inventory at the location. This transaction is written when a reclassification event is processed by the batch process Reclassification of Item (reclsdly). For every ‘Reclassification Out’ transaction for the location, a ‘Reclassification In’ transaction also exists and the two transaction data records balance each other. The units recorded in this transaction are calculated based on total stock on hand and in-transit for each item/location. The cost and retail are the same as the ‘Reclassification In’ transaction (Transaction Code – 34). The ‘Reclassification Out’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and are used in the ending inventory calculations for a particular period.
37 - Inter-company Transfer In	This transaction code is recorded for the receiving location at the time of shipping of a Transfer or Allocation out of a location where the receiving location is in a different legal entity. Because the movement of inventory is between different legal entities, it needs to be treated as a Purchase transaction rather than an inventory movement, thus the need for a different transaction code. This transaction is posted in conjunction with the transaction ‘Intercompany Transfer Out’ (Transaction Code 38). Under this transaction type, number of units, total cost and total retail of the transaction are captured. Total cost and retail are calculated as follows: o Total Cost: Number of units transferred * Per Unit agreed upon ‘Selling Price’ or average cost or standard cost at the sending location (if no transfer price specified) o Total Retail: Number of units transferred * Per unit current retail at the receiving location. Additionally, for reporting and reference purposes, the RMS transfer number is captured as part of the transaction record in ref_no_1 field, shipment number is captured in ref_no_2 field and location ID of the sending location in GL_REF_NO field. The ‘Intercompany Transfer In’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and used in the ending inventory calculations for a particular period.
38 - Inter-company Transfer Out	This transaction code is recorded for the sending location at the time of shipping of a Transfer or Allocation out of a location where the receiving location is in a different legal entity. As the movement of inventory is between different legal entities, it needs to be treated as a Sale transaction rather than an inventory movement, thus the need for a different transaction code. This transaction is posted in conjunction with the transaction ‘Intercompany Transfer In’ (Transaction Code 37). Under this transaction type, number of units, total cost and total retail of the transaction are captured. Total cost and retail are calculated as follows: o Total Cost (cost accounting method): Number of units transferred * Average Cost or standard cost at sending location o Total Cost (retail accounting method): (Number of units transferred * Unit Retail at sending location) * (1 – Cumulative Mark on %) o Total Retail: Number of units transferred * Agreed upon ‘Selling Price’ or average cost/standard cost at the sending location (if no transfer price specified) Any variance between agreed upon ‘selling price’ and regular retail for the sending location triggers the posting of either a markup or markdown transaction through transaction code 17 (intercompany markup) or transaction code18 (intercompany markdown). Additionally, for reporting and reference purposes, the RMS transfer number is captured as part of the transaction record in ref_no_1 field; shipment number is captured in ref_no_2 field and location ID of the receiving location in GL_REF_NO field. The ‘Intercompany Transfer out’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and used in the ending inventory calculations for a particular period.
39 - Intercompany Margin	RMS calculates intercompany margin differently based on whether the department uses the cost or retail method of accounting. o Inter-company Margin (Cost method): Inter-company Out at Retail – Inter-company Out at Cost o Inter-company Margin (Retail method): Inter-company Out at Retail * (1-CUM%) This is recorded in the month data and week data tables not in TRAN DATA
41 - RDW Stock Count Adjustment	This transaction is for ‘Retail Analytics’ (RA) and records inventory adjustment for unit and value counts. This transaction code is required for inventory variance reporting in RA because RMS does not use a transaction code for these transactions but captures variance at month level for unit and value counts.
44 - RDW Inbound Transfer Receipt	This transaction is for ‘Retail Analytics’ (RA). It was created to notify RA that the in-transit transfer quantity for the item/location has been received and moved to the total stock on hand. Because there is no other transaction code is recorded at the time of receipt of a transfer at a location, this record also serves as an audit record for receipt.
50 - Open Stock	This is the beginning of period stock on hand in value terms for a location. This is equal to the closing stock on hand for the previous period. This is recorded in the month data and week data tables not in TRAN DATA. Separate values are recorded at cost as well as retail.
51 - Budgeted Shrink	This is the calculated shrinkage value for the period if the Budgeted Shrink Indicator (BUD_SHRINK_IND) field is set to ‘Y’ in the system options. Budgeted shrinkage is calculated using budgeted shrinkage percent (stored on HALF_DATA_BUDGET table as SHRINKAGE_PCT field) at the department/location level for a financial half multiplied by sales at retail or at cost, depending on whether retail or cost accounting method is used, respectively.
52 - Close Stock	This is the end of period stock on hand in value terms for a location. This is a calculated value and recorded in the month data and week data tables at cost as well as retail. This is not recorded in TRAN_DATA.
53 - Gross Margin	This is a calculated gross margin value earned by the business during the period. This is recorded in the month data and week data tables at retail not in TRAN DATA.
54 - HTD GAFS	This is ‘Half-to-date Goods Available For Sale’. HTD GAFS value is used in the retail method of accounting for calculating cumulative markon percent which in turn is used in inventory calculation at cost. This is recorded in the month data and week data tables at cost as well as retail
55 - Inter Stocktake Sales Amount	This is the cumulative net sales amount since the last time a Unit & Value stock count was taken for a subclass/location. It is valued at cost for a cost department and at retail for a retail department. It is reset to 0 when the Unit & Value count is processed for a subclass/location, after the actual shrinkage has been calculated. This is recorded in the month data and half data tables.
56 - Inter Stocktake Shrink Amount	This is the cumulative estimated (or budgeted) shrinkage amount since the last time a Unit & Value stock count was taken for a subclass/location. It is valued at cost for the cost department and at retail for the retail department. It is reset to 0 when the count is processed for this subclass/location, after the actual shrinkage has been calculated. This is recorded in the month data and half data tables.
57 - Stocktake MTD Sales Amount	This is Month-to-date net sales amount for a subclass/location. It is valued at cost for the cost department and at retail for the retail department. It is calculated only when inventory Unit & Value stock count is taken, as of the date the count is processed. This is recorded in the month data table
58 - Stocktake MTD Shrink Amount	This is Month-to-date estimated (or budgeted) shrinkage amount for a subclass/location. It is valued at cost for the cost department and at retail for the retail department. It is calculated only when inventory Unit & Value stock count is taken, as of the date the count is processed. This is recorded in the month data table.
59 - Stocktake Book Stock Cost	This is the book stock value at cost for a subclass/location. It is calculated only when inventory Unit & Value stock count is taken, as of the date the count is processed. This is recorded in the month data table.
60 - Employee Discount	This transaction code is used for recording employee discounts extended to the employees of a retailer when he or she makes a purchase of the items that have an employee discount identified on them. These are inserted through the sales upload process when a promotional type of ‘Employee Discount’ is passed in from the point of sale (POS). This transaction captures the amount of discount given at the POS based on a sale to an employee and is therefore a retail only transaction. The transactional stock ledger record contains the number of units to which the discount applies and the total retail value of the discount (units * discount amount). The ‘Employee Discount’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels. These values are then used to calculate the period ending inventory and margin values for retail.
61 - Stocktake Actual Stock Cost/Retail	This is actual stock amount at retail for a subclass/location when the Unit & Value stock count is taken, as of the date the count is processed. This is recorded in the month data table at both cost and retail.
62 - Freight Claim	This transaction code is used to record the loss when there is difference between expected quantity and actual received quantity due to freight claims in a transfer. Freight claims can only be selected as a reconciliation method online in RMS using the Stock Order Exception Reconciliation functionality in RMS. This is always recorded for receiving locations and the units and value are the difference between shipped and received. The ‘Freight Claim’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and used in the ending inventory calculations for a particular period. This is a reduction to ending inventory.
63 - Work Order Activity (finisher) Cost: Update Inventory	This transaction code is used to record the cost of work-order activity for an item that occurs at a finisher location. During transfer, an item can be routed through an internal or external finisher location for repair or value additions. The costs associated with activities carried out on merchandise at the finisher location are referred to as work order activity cost and are defined using the work order cost dialogue in RMS. Under this transaction code, the work order activity cost that adds value to the inventory and which are meant to update inventory and thereby recalculate WAC (weighted average cost) is recorded. The ‘Work-order Activity Cost – Update Inventory’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels. These values are then used to calculate the period ending inventory values for cost method of accounting.
64 - Work Order Activity (finisher) Cost: Post to Financial	This transaction code is used to record the cost of work-order activity for an item that occurs at a finisher location. During a transfer, an item can be routed through an internal or external finisher location for repair or value additions. The costs associated with activities carried out on merchandise at the finisher location are referred as a work order activity cost and are defined using the work order cost dialogue in RMS. Under this transaction code, work order activity costs that should not be included in the value of the inventory and which do not recalculate WAC but are directly posted to financial as expenses are recorded. The ‘Work-order Activity Cost – Post to Financials’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels and posted to the G/L, but are not used to calculate the period ending inventory values.
65 - Restocking fee	This transaction code is used to record the location restocking fee associated with the return to vendor transactions. A restocking fee can be defined either as percentage of return unit cost or particular monetary amount. The ‘Restocking Fee’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels for a location and posted to G/L. These values are treated as inventory additions at cost.
70 - Cost Variance	In the case of standard cost method, this transaction is written each time the cost of the transaction differs from the ‘standard’ or base cost that is expected and records the difference between the standard cost and the transaction cost. This is also written in case of supplier cost change. In case an average cost method is being used, this record is written in the case of an average cost adjustment done in the system. Also, this transaction is written in case of WAC calculation when the stock on hand is negative. In this case, WAC is not recalculated but set to PO unit cost in case of PO receipt or Transfer Price in case of transfer. Refer to the ‘average cost’ section for more information. In the case of receiver cost adjustments (RCA), if the Receiver Cost Adjustment Type is set to 'Standard' in the system options through the indicator RCV_COST_ADJ_TYPE and stock on hand is less than the receipt, there will be a tran data record for this tran code for the units no longer available at the receiving location. For both cases, the values recorded in the transactional record are the number of units on-hand and in-transit for an item/location and the total cost of the transaction (units * cost difference). Additionally, depending on the type of transaction the Transfer Order Number and Shipment or RTV Number are recorded in the reference columns of this transaction record. The ‘Cost Variance’ transactions that are captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels. These values are then used to calculate the period ending inventory values for cost method of accounting.
71 - Cost variance from RTV (retail method of acct)	In some scenarios because of a mutual agreement between the retailer and supplier or between two locations, the cost on the RTV or intra-company transfer respectively can be different from the sending location’s cost. This transaction code is used for recording this difference between the transaction cost and sending location’s cost for a Return-to-Vendor (RTV) or Intra-Company Transfer to ensure its proper accounting in case of the department using retail method of accounting. This transaction is not used in the period ending inventory calculations in stock ledger. In this case the cost variance would be calculated as the cost difference * (1 – CMO %)* units; where CMO is the Cumulative Mark-On. The cost recorded can be a positive or negative value.
72 - Cost variance from RTV (cost method of acct)	In some scenarios because of a mutual agreement between the retailer and supplier or between two locations, the cost on the RTV or intra-company transfer respectively can be different from the sending location’s cost. This transaction code is used for recording this difference between the transaction cost and sending location’s cost for a return-to-vendor (RTV) or intra-company transfer to ensure its proper accounting in case of the department using cost method of accounting. The values recorded in the transactional record are the number of units in the RTV/Transfer transaction and the total cost of the transaction (units * cost difference). The cost recorded can be a positive or negative value. The ‘Cost Variance – Cost Accounting’ transactions that are captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels. These values are then used to calculate the period ending inventory values for cost method of accounting.
73 - Receiver Cost Adjustment Cost variance FIFO	This transaction code is used for Receiver Cost Adjustments (RCA) when the available inventory units, based on a FIFO method, is less than the number of units included in the cost adjustment. This is applicable in cases where Receiver Cost Adjustment Type is set to 'FIFO' in the system options through the indicator RCV_COST_ADJ_TYPE and for cases where RCA inventory is not available at the time of the adjustment and WAC is not recalculated. Retailers can use the information on transaction data for this transaction code to determine how to manually adjust the WAC at stores where stock was distributed. This transaction code is not rolled up to the month data for the GL interface.
80 - Workroom Cost / other cost of sales	This transaction is not a part of the base RMS functionality but is provided along with logic in stock ledger calculations when the department uses the retail method of accounting. This transaction is intended for use in capturing all miscellaneous transactions that affect the cost of sales and therefore the gross margin. Although data for this transaction is not captured anywhere within the base RMS functionality, this transaction type is taken into account when calculating gross margin for the stock ledger at the subclass/week and subclass/month levels. Therefore, a modification could be made to the system to feed this data in and have it accounted for in the stock ledger.
81 - Cash Discount	This transaction is not a part of the base RMS functionality, but is provided along with logic in stock ledger calculations when the department uses the retail method of accounting. This transaction is intended for use in capturing all cash discount transactions that affect the cost of sales and therefore the gross margin. Although data for this transaction is not captured anywhere within the base RMS functionality, this transaction type is taken into account when calculating gross margin for the stock ledger at the subclass/week and subclass/month levels. Therefore, a modification could be made to the system to feed this data in and have it accounted for in the stock ledger.
82 - Wholesale/Franchise Sales	This transaction code is used to record sales made to a franchise entity. When a product is shipped from the retailer’s warehouse to a franchise store there is a sales transaction registered to the warehouse and this transaction is recorded. This record captures the units sold, the retail value of the units (cost to the franchise), and the cost of the units (average or standard cost based on system option setting). Additionally, the first reference column captures if the transaction is a franchise or wholesale transaction. ‘1’ represents wholesale and ‘2’ represents franchise. The second reference column is used for capturing the transfer number. These transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels. These values are then used to calculate the period ending inventory values for both cost and retail method of accounting.
83 - Wholesale/Franchise Returns	This transaction code is used to record returns made from a franchise entity. When a product is returned to the retailer’s warehouse from a wholesale or franchise store there is a return transaction registered to the warehouse and this transaction is recorded. This record captures the units sold, the retail value of the units (cost to the franchise), and the cost of the units (average or standard cost based on system option setting). Additionally, the first reference column captures if the transaction is a franchise or wholesale transaction. ‘1’ represents wholesale and ‘2’ represents franchise. The second reference column is used for capturing the transfer number. These transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels. These values are then used to calculate the period ending inventory values for both cost and retail method of accounting.
84 - Wholesale/Franchise Markups	When the sale price on a wholesale/franchise order is greater than the warehouse retail price, it is considered a markup and the difference is recorded as a Wholesale/Franchise Markup under this transaction code. This record captures the units sold and difference between the current regular or clearance retail and the retail value on the transaction multiplied by number of units as transaction value. Additionally, the first reference column captures if the transaction is a franchise or wholesale transaction. ‘1’ represents wholesale and ‘2’ represents franchise. The second reference column is used for capturing the transfer number. The Wholesale/Franchise Markup transactions that are captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels. These values are then used to calculate the period ending inventory values for both cost and retail method of accounting
85 - Wholesale/Franchise Markdowns	When the sale price on a wholesale/franchise order is less than the warehouse retail price, it is considered a markdown and the difference is recorded as Wholesale/Franchise Markdown under this transaction code. This record captures the units sold and difference between the current regular or clearance retail and the retail value on the transaction multiplied by number of units as transaction value. Additionally, the first reference column captures if the transaction is a franchise or wholesale transaction. ‘1’ represents wholesale and ‘2’ represents franchise. The second reference column is used for capturing the transfer number. The Wholesale/Franchise Markdown transactions that are captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels. These values are then used to calculate the period ending inventory values for both cost and retail method of accounting.
86 - Wholesale/Franchise Restocking Fee	This transaction code is used to record any restocking payments made by wholesale or franchise customers to the retailer against the items being returned. This is an optional fee sometimes charged by the retailer to compensate against the expenses incurred for restocking the returned merchandise. This would typically be income for the warehouse. This record captures the units returned and restocking fee at cost. Additionally, the first reference column captures if the transaction is a franchise or wholesale transaction. ‘1’ represents wholesale and ‘2’ represents franchise. The second reference column is used for capturing the transfer number. The Wholesale/Franchise Restocking Fee transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels for a location and posted to G/L and used in the end of period inventory calculations.
87 - VAT In Cost	If VAT is being used in the system, every purchase transaction will entail Input VAT and every sales transaction will entail Output VAT. For their proper accounting, all the VAT values need to be properly calculated and captured. This transaction code is used for recording Input VAT amount for purchases (tran code – 20), RUA and RCA transactions (tran code – 20U and 20C), consignment sales (tran code – 20) and return to vendor (tran code – 24) transactions. This record captures the units in the transaction and VAT amount at cost. For purchases, RUAs, RCAs and consignment sales, VAT amount is a positive value and is calculated as (cost * VAT rate/100). For return to vendor transactions, the VAT amount is a negative value and calculated as ((cost – restocking fee) * VAT rate/100). The VAT rate is retrieved from VAT_ITEM table. Additionally, the Order Number or RTV Order Number are recorded in the first reference field. The ‘VAT In Cost’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels for a location and posted to G/L. They are not included in inventory calculations.
88 - VAT Out Retail	If VAT is being used in the system, every purchase transaction entails Input VAT and every sales transaction entails Output VAT. For their proper accounting, all the VAT values need to be properly calculated and captured. This transaction code is used for recording Output VAT amount for regular sales transactions (tran code – 1 and 3), sales return (tran code – 4), franchise sales (tran code – 82) and franchise returns (tran code – 83). This record captures the units in the transaction and VAT amount at retail. For sales transactions VAT amount will be positive value and for return transactions, VAT amount will be negative value. VAT rate is retrieved from VAT_ITEM table. Additionally, for franchise transactions, the associated transfer number is captured in the first reference column. The ‘VAT Out Retail’ transactions captured in RMS are rolled up to the subclass/day, subclass/week and subclass/month levels for a location and posted to G/L. They are not included in inventory calculations.

4. Understanding Transaction Data Impact on Stock Ledger

DATA Elements of RMS TRAN_DATA

o Transaction Level Item Number

o Department Number

o Class Number

o Subclass Number

o Pack Item Indicator

o Store Number

o Warehouse Number

o Transaction Date

o Post Date

o Transaction Code

o Adjustment Code

o Units

o Total Cost Value of Transaction

o Total Retail Value of Transaction

o Reference Number 1

o Reference Number 2

o Old Unit Retail

o New Unit Retail

o Program Name

o Sales Type

o Vat Rate

o Average Cost

o Time Stamp

5. Understanding Retail & Cost Accounting Methods

Various retailers, either because of legal requirements or their own accounting standards, may require using either cost or retail methods of accounting, or both. RMS supports this choice by allowing this to be set at the department level (on the DEPS table). The indicator ‘PROFIT_CALC_TYPE’ specifies whether all the subclasses in the department will use cost method of accounting or retail method of accounting. The valid values for this field are ‘1’=Cost and ‘2’=Retail.

While not commonly done, the system allows a retailer to operate some departments using Cost method and other departments using Retail method. In order to provide a more consistent view and method of determining inventory valuation and profitability, most retailers choose to use either Cost or Retail, but not both. Some retailers with a more diverse assortment may elect to use a mixture of methods. Examples of this approach can include department stores, where fashion departments may use the Retail method, while hard lines or food departments may use the Cost method.

Under the Cost method, item level margins can be calculated because costs are determined at the item level. In the Retail method, margins can only be calculated at the level of the stock ledger (that is, the subclass), as that is the level at which cost is estimated.

Retail Accounting Method

The Retail method of accounting is the method by which inventory value is measured using the retail price amount. RMS records sales, purchases, return to vendor (RTV), reclassification and transfers at both cost and retail. Purchases and RTVs are recorded using the ‘actual’ cost but the Cost for Transfers, Reclassifications, Sales, and so on is based on the Cumulative Markon %.

Under this method, RMS determines ending inventory at cost based on percentage of the ending inventory’s retail value. For this calculation, the historical ratio of available inventory at cost to available inventory at retail to value inventory is used. This is known as the cumulative mark-on percentage (CUM %). RMS computes estimated value for ending inventory cost and cost of sales based on the value of the inventory at the current retail price (non promotional) multiplied by the cost complement (1 - CUM%). It uses the prior week’s Cumulative Mark-on Percentage (or prior month’s if weekly CUM% is NULL) for each subclass/location to calculate transfers and reclassifications at cost (sending store/source subclass).

Closing Stock (retail) =

Opening Stock (retail) + Retail Inventory Additions – Retail Inventory Reductions

GAFS (retail) =

Opening Stock (retail) + Retail Inventory Additions

GAFS (cost) =

Opening Stock (cost) + Cost Inventory Additions

Cumulative Markon Percent =

(GAFS Retail – GAFS Cost) / GAFS Retail * 100

*Note the calculated cumulative markon percent can be constrained by a system level minimum and maximum value.

Closing Stock (cost) =

Closing Stock (retail) * (1 – Cumulative Markon Percent / 100)

Cost of Sales (COGS) =

Opening Stock (cost) + Cost Inventory Additions – Closing Stock (cost)

Gross Margin =

Net Sales (retail) – COGS

Intercompany Margin =

Intercompany Out (retail) * (1 – Cumulative Mark on Percent / 100)

The calculations for Retail Inventory Additions, Cost Inventory Additions and Retail Inventory Reductions are below:

Retail Inventory Additions =

Purchases (retail) – RTVs (retail) + Markups + Intercompany Markups – Markup Cancellations + Transfers In (retail) + Transfers In Book (retail) + Intercompany In (retail) – Transfers Out (retail) – Transfers Out Book (retail) + Reclassification In (retail) – Reclassification Out (retail)

Cost Inventory Additions =

Purchases (cost) + Restocking Fees (cost) – RTVs (cost) + Freight + Transfers In (cost) + Transfers In Book (cost) + Intercompany In (cost) – Transfers Out (cost) – Transfers Out Book (cost) + Reclassification In (cost) – Reclassification Out (cost) + Up charge Profit + Up charge Expenses

Retail Inventory Reductions =

Net Sales (retail) + Permanent Markdowns + Promotional Markdowns + Clearance Markdowns + Intercompany Markdowns – Markdown Cancellations + Employee Discount + Freight Claims (retail) – Stock Adjustments COGS (retail) + Shrinkage (retail) + Intercompany Out (retail)

Shrinkage (Budgeted) =

Shrinkage Percent * Net Sales (retail)

Shrinkage (Not Budgeted – i.e. Actual) =

(-1 * Stock Adjustments (retail)) + Stocktake Adjustments (retail)

Cost Accounting Method

The Cost method of accounting is the method in which inventory is controlled based on the cost of the merchandise. It is reliant on accurate perpetual inventories. Under this method, all purchases, transfers, sales, and RTVs are recorded at cost in order to calculate the value of the inventory.

While the Retail method is still widely used in certain verticals, across much of the retail industry, the Cost method is generally accepted as a ‘best practice’. There are many different ways that the Cost method can be implemented. Oracle Retail supports two methods of Cost accounting: Average Cost and Standard Cost.

Under the average cost method of cost accounting, the cost of an item at a location is recalculated every time inventory is received based on quantity and cost of the receipt. Because of this calculation method, this method is also referred to as the weighted average cost (WAC) or moving weighted average cost (MWAC).

The WAC is the value of owned inventory at a location and is used to determine the cost of sales for an item sold at the location. It is also used as the cost for all transactions that occur for the location that are written to the transaction-level stock ledger. WAC is recalculated in the following cases:

o Purchase Order is received

o Transfer is Shipped (for receiving location only)

o Receiver adjustments occur

o Transfer Reconciliation (Selected scenarios)

Average cost is stored at item/location level in the table ITEM_LOC_SOH.

The WAC is calculated as follows:

WAC after PO Receipts = (((SOH Quantity + In-transit Quantity) * Old WAC) + (Receipt Quantity * Receipt Cost)) / ((SOH Quantity + In-transit Quantity) + Receipt Quantity)

WAC after Transfer Shipment (Receiving Location) = (((SOH Quantity + In-transit Quantity) * Old WAC) + (Quantity Transferred * Transfer Cost)) / ((SOH Quantity + In-transit Quantity) + Quantity Transferred)

In the case of pack items, pack component SOH quantity (PACK_COMP_SOH) and pack component in-transit quantity (PACK_COMP_INTRAN) is considered. In the case of simple pack catch weight item, the total cost of the pack is used to derive unit cost instead of receipt cost.

Closing Stock (cost) =

Beginning of Month Inventory

+ Purchases (20)

- Return to Vendor at Cost (24)

- Net Sales at Cost (01 or 02)

+Transfer in at Cost (30,31,37)

- Transfer out (32,33,38)

+ Up Charges (28,29)

- Shrinkage at Cost (calculated value)

+ Reclassification in at Cost (34)

- Reclassification out at Cost (36)

- Cost Variance (70)

- Freight Claims (62)

+ Inventory Adjustment at Cost (23)

+ Work Order Activity Updates (63)

- Cost Variance (Cost Accounting) (72)

- Wholesale/Franchise Net Sales (82)

Closing Stock (retail) =

Beginning of Month Inventory

+Purchases at Retail (20)

- Return to Vendor at Retail (24)

+ Markups at Retail (11)

- Markup Cancels (12)

+ Transfer In at Retail (30,31,37)

- Transfer Out at Retail (32,33,38)

- Freight Claims at Retail (62)

+ Inventory Adjustment at Retail (23]=)

+ Inventory Adjustment (if not using budgeted shrink) (22)

- Stocktake Adjustment at Retail ( )

+ Reclassification In at Retail (34)

- Reclassification Out at Retail (36)

- Net Sales at Retail (01)

- Markdowns at Retail (13)

- Promotional Markdowns at Retail(15)

- Clearance Markdowns at Retail (16)

+Markdown Cancels at Retail (14)

- Employee Discounts at Retail (60)

+ Intercompany Markup (17)

- Intercompany Markdown (18)

- Wholesale/Franchise Net Sales at Retail (82)

+ Wholesale/Franchise Markup at Retail (84)

- Wholesale/Franchise Markdown at Retail (85)

Gross Margin =

Net Sales Retail - Net Sales Cost

6. Stock Ledger Batch Jobs Process Flow and Data Model

7. Stock Ledger Integration with GL
RMS stock ledger information can be staged for upload into a retailer’s General Ledger system. Stock ledger information can be extracted from RMS at three different levels:

· Transaction Level – extract all transactions at the detail level (i.e. no rollups).

· Day Level – transactions by transaction code are rolled up to the department/location/day level, class/location/day level or subclass/location/day level.

· Month Level – information is extracted from the monthly stock ledger tables by transaction code and rolled up to a department/location level, class/location level or subclass/location level. As previously discussed, the month level data provides additional data not available at the transaction level or the day level, including:

o BOP Stock Position at cost and retail

o EOP Stock Position at cost and retail

o Gross Margin

o HTD GAFS at cost and retail

o Inter Stocktake Sales Amount and Shrink Amount

o Stocktake Book and Actual Amounts at cost and retail

The merchandise hierarchy roll-up level for the day and month level data extraction is determined by a system level option and can be set to either the RMS department level , RMS class level or RMS subclass level. Locations include both stores and warehouses.

8. Stock Ledger RMS Application Forms

Transaction Data Form

RMS Start Menu > Finance > Transaction Data View

Stock Ledger View Form

RMS Start Menu > Finance > Stock Ledger View

Stock Ledger Maintenance

Summary Note

This was all about Stock Leder in RMS. Please do let me know in case of any question or feedback. In next article, we are going to talk Best Practices and Details on Oracle Retail and Oracle Finance Integration.

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

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.

6 comments :

Ade said...: Thanks so much for providing such a great asset. Its been a life saver. Well done.; September 3, 2018 at 7:35 AM
Unknown said...: I have a question related to using RMS at a company that is on the Retail method. Cumulative markon percentage is an important aspect of the stock ledger when you are on the retail method. There are times when the cum markon percentage goes off track, and then future transfers and COGS are also off track from that point forward. We are seeking a solution to this, as finding and correcting every transaction that caused markon to go off track is virtually impossible.

That being said, the section about setting a min and max markon percentage got my attention. My question is, can that min and max percentage be set by dept or class or subclass, or is it one setting for the whole merch hierarchy? Although the section did suggest that the BUD_INT can be set at the dept level, it didn't say whether the min and max can also be set at the dept level. I am asking because our markon % varies widely by dept, so one setting would not be the ideal solution.

On a related note, any other suggestions on how to keep cumulative markon % accurate?; September 5, 2018 at 10:54 AM
Unknown said...: For interstocktake shrink amount ,once it is reset after the new stock count ,then how the difference between old shrink(cumulative )and the new shrink after new stock take is compensated to negate the cost variance .; September 10, 2019 at 9:11 PM
Shahid said...: Hi, This is very informative thanks for posting. I have one question regarding cost variance posting, will the tran code 70 is posted every time if old wac is not equal to new wac in case of PO receipt and TSF recipt , please answer.; May 17, 2020 at 1:40 PM
cryptocustomercare said...: Excellent information provided by you through this post. I follow all the mentioned information. If you want to Learn here Error While Selling BTC via Coinify in Ledger Live Mobile ?. if you find any difficulty to understand this concept then do not worry about anything we are here to help you. You can visit us at: cryptocustomercare.org; December 25, 2023 at 1:01 AM
Casino BTC Support said...: This comment has been removed by the author.; December 25, 2023 at 11:08 PM

Oracle Retail

Thursday, September 28, 2017

Oracle Retail Merchandising System (RMS) - Stock Ledger

About Nagesh Mishra

6 comments :

Followers

Featured Post

Oracle Retail Merchandising System (RMS) - Deep Dive Tutorial

Contact Form

SC