Oracle Retail Trade Management (RTM)

Retail Trade Management Overview
=============================

• It Is An Import Management System design to Integrate International Trade               transactions.
• It is used to manage import process.
• Any Additional cost other than supplier cost will be maintained in the RTM.

The following is a list of functionality that is part of the Oracle Retail Trade Management (RTM) product.

1. Freight Type Maintenance*
2. Freight Size Maintenance*
3. SCAC Maintenance (Standard Carrier Alpha Codes)*
4. Partner Maintenance (Banks, Freight Forwarders, Manufacturers, Import Authorities, Brokers, Agents, Factories) - Expense Profiles
5. Supplier Maintenance– Import Attributes*– Expense Profiles– Documents
6. Country Maintenance– Tariff Treatments*– Expense Profiles– Documents
7. Outside Location Maintenance Clearing Zones* Lading Ports Discharge Ports
8. Document Maintenance (Required Documents, Bank, Sender, and Additional Instructions)
9. Cost Component Maintenance (Expenses, Assessments*)
10. CVB Maintenance
11. HTS Maintenance*– HTS Headings* Documents*– HTS Heading Restraints*– Quota Categories*– Tariff Treatments*– Fees and Taxes*– OGAs (Other Government Agencies) *– HTS Reference*– HTS CVD (Countervailing Duty) and AD (Anti-Dumping) *
12. Item Maintenance– Import Attributes*– Eligible Tariff Treatments*–HTS/Assessments*– Documents– Item Supplier – Expenses– Item Supplier Origin Country – Expenses
13. Purchase Order Maintenance– Import Attributes– Letter of Credit*– Order Item - HTS/Assessments*– Order Item Location - Expenses– Documents
14. Letter of Credit*
15. Transportation*
16. Obligations*
17. Customs Entry*
18. Actual Landed Cost (ALC)* Estimates versus Actuals Comparison* Actuals Finalization*

Note : * denotes RTM core functionality

Simplified RTM - Merchandising
==========================

Below is a list of functionality that is available when running Simplified RTM which is part of the Merchandising Standard Edition.

1. Partner Maintenance (Banks, Freight Forwarders, Manufacturers, Import Authorities, Brokers, Agents, Factories) 
2. Expense Profiles
3. Supplier Maintenance- Import Attributes* Expense Profiles Documents
4. Country Maintenance- Tariff Treatments* Expense Profiles Documents
5. Outside Location Maintenance Clearing Zones* Lading Ports-Discharge Ports
6. Document Maintenance (Required Documents, Bank, Sender, and Additional Instructions)
7. Cost Component Maintenance (Expenses, Assessments*)
8. CVB Maintenance
9. HTS Maintenance - * HTS Headings*o Documents -* HTS Heading Restraints* Quota Categories* Tariff Treatments* Fees and Taxes* OGAs (Other Government Agencies) * HTS Reference* HTS CVD (Countervailing Duty) and AD (Anti-Dumping) *
10. Item Maintenance Import Attributes* Eligible Tariff Treatments* Documents HTS/Assessments* Item Supplier – Expenses Item Supplier Origin Country - Expenses
11. Purchase Order Maintenance Letter of Credit* - This dialog is available with Std. Edition, but it is not required when an Order’s Payment Method is ‘Letter of Credit’ Order Item - HTS/Assessments* Order Item Location - Expenses Documents
12. Letter of Credit* - This dialog is available with Std. Edition, but it is not required when an Order’s Payment Method is ‘Letter of Credit’

Note : * denotes RTM core functionality

RTM Product/Solution Summary
==========================

High Level RTM Flow 
===================

Foundation data setup for RTM includes:

1. HTS codes fileUpload

  • HTS (Harmonized Tariff schedule)details can be manually created or uploaded through a flat file        which is in Oracle retail file format.
  • If HTS codes already exists for item then upon uploading HTS details, HTS details gets updated at      item and Order levels.
  • The HTS mass update report displays the updated HTS details after HTS upload process.

How do we get the HTS codes file /tapes to upload?


Customs department of each import country will release HTS codes/Tapes every year with updated duty rates. These files can be upload into the system.

2. ELC cost components which affects the cost.

ELC cost components include Expenses, Assessments.

• Expenses are broken down to “Country level” and “Zone level”. 

           • Country level expenses incurred when moving goods from a country of sourcing to the discharge port in the country of import.
           • Zone level expenses incurred when moving goods from a discharge port in the country of import to a final destination (Warehouse).

• Assessments specifically refer to taxes, fees, or duties which are paid to the government. The tax, fee, and duty assessments roll up to become a landed cost component.

3. CVB Maintenance - used for complex calculations.

Computation Value Bases describe how expenses and assessments are combined to provide a base for the calculation. Basically CVB’s are used for complex calculations.

4. Expense profiles 

• Supplier and partner expense profiles are broken down to “Country” and “Zone”. Country level supplier and partner profiles are for expenses incurred when moving goods from a country of sourcing to the discharge port in the country of import. Zone level profiles are for expenses incurred when moving goods from a discharge port in the country of import to a final destination cost zone (Warehouse or Store).
• A mass maintenance capability exists to change the rate on a cost component in the ELC Cost Component Maintenance, Expense Profiles, and Item Expense dialogs and have it automatically update expense profiles, item expenses, and purchase order/item expenses where applicable on a designated date.
• This is extremely useful for freight rates which change with a fair amount of frequency. Assessment rates are driven by the HTS rate values. When a new file is uploaded items and PO/items can be updated

5. Documents 

• Documents can added at different levels I.e. supplier / partner / country / item /PO.
• IF the documents are added at higher level they will be updated to the lower levels.

6. Outside Locations

Outside locations are the location that are located outside the system where inventory will not be tracked. They are used in expenses profiles and updates the expenses based on lading and discharge ports.

7. Freight type /size

Freight type and freight size information basically used in transportation. This details will help Salam in to know which merchandise is coming in which freight and what type of freight it is.

These are Freight types:

• 20’ foot container.

• 40’ foot container, etc.

Below are the freight types that can be noted on the Bill of Lading:

• CFS/CFS: Container Freight Station to Container Freight Station

• A type of steamship line service in which cargo is transported between container freight stations, where containers may be stuffed, stripped, or consolidated. Usually for less than container load shipments.

• CY/CY: Container Yard to Container Yard

• A type of steamship line service in which freight is transported from origin container yard to destination container yard.

• LCL: Less than Container Load

• Usually LCL cargo is consolidated with other small lots for shipment by container.

• LTL: Less than Truckload

• Similar, used more in domestic freight movement.

8. Standard Carrier Alpha Codes (SCAC) Maintenance

A unique code used to identify transportation companies. It is typically two to four alphabetic letters long. Developed by the National Motor Freight Traffic Association in the1960s to help the transportation industry computerize data and records. The container can have a different SCAC code than the vessel. In RTM the container SCAC is entered in the Transportation Maintenance – Freight window.

Example: 

FDEG FEDEX GROUND

FDEN FEDEX (AIR)

FEXF FedEx Freight

FXFE FedEx LTL Freight East

FXFW FedEx LTL Freight West (formerly VIKN - Viking)

FXNL FedEx Freight National (formerly Watkins)

How SCAC codes are useful?

This is another level of tracking the goods during the transportation, SCAC code information could be useful to identify the containers of a particular freight forwarder.

ItemMaintenance

==============

1. Documents

• Documents can be added at items level which will updated on to the PO.

2. HTS/Assessments

•Initially HTS codes need to be attached manually for each item, during HTS upload HTS detail will be updated for all the item/HTS combination.

3. Eligible Tariff treatments

• Tariff treatments completely depends on the agreements between the countries.

• Example: The import duty between GCC countries would be zero like free trade, another example if any client importing the goods from India, based on the agreements between India and Qatar tariff treatments would differ.

4. Item supplier Expenses

• Expenses can be added at item level and gets updated onto the PO. Expenses added at this level are called Zone level expenses .expenses which are incurred from discharge port to cost zone locations are called zone level expenses.

5. Item supplier origin country –expenses

• Expenses can be added at item level and gets updated onto the PO. Expenses added at this level are called country level expenses .expenses which are incurred from lading port to discharge port are called country level expenses.

PO Maintenance

==============

1. Order item- HTS /Assessments.

• As soon as item added to PO, HTS /assessments details gets updated on to the PO.

2. Order item Location – expenses

• Expenses on the order can be added using partner’s/lading /discharge port combinations or if items already have expenses then expenses will be updated automatically.

Transportation

=============

• Transportation details can be upload through a flat file.

• Transportation information is used to track the items during transportation at many levels  

• Example: BOL number, Lot no, Seal ID, SCAC codes, freight size, freight type and Packing list information.

• Finalizing the transportation will create a customs entry records automatically.

Customs entry

============

• Client can maintain customs entry information for all import countries in custom entry module. This module also records the different types import duties paid to the government.

• When the transportation records are finalized, a Customs Entry is automatically created using information from the transportation record. This process ensures consistent and accurate data.

• Custom entry charges can be changed in custom entry module which would affect the weighted average cost of an item.

• Once the Customs Entry is completed, Salam studios & stores can choose to allocate the duty and assessment to Actual Landed Cost (ALC). 

• When the Customs Entry is Confirmed status, a non-merchandise invoice will be created automatically in Oracle Retail Invoice Matching.

Obligation

==========

• Obligations are invoices typically provided by partners (e.g. Freight forwarder, agents, etc.) and/or suppliers that capture the actual expenses incurred to bring the item from origin country to final destination (Warehouse). 

• Since Estimated Landed Cost (ELC) is only an estimate of the expenses incurred, the actual expenses (actual landed cost-ALC) can be captured to ensure that any variance between ELC and ALC is recorded and (optionally) allocated to the item’s costs.

• Negative obligation can also be added only if there is user error during entering the value.

• If the actual expenses are not equal to estimated expenses a cost adjustment will be generated for ELC / ALC difference and posted to stock ledger

• Approved and allocated obligations in Oracle Retail Trade Management will generate non-merchandise invoices in Oracle Retail Invoice Matching. 

• The actual obligation costs can be allocated to item/loc through the ALC module. 

ALC Finalization

• Actual landed Cost (ALC) is the sum of all actual expenses and assessments involved in bringing an item on a particular purchase order from the origin country to the final destination. 

• The incurred expenses are received in the form of Obligations provided by partners or suppliers. Incurred duties, in the form of assessments, are received and recorded through Customs Entry. ALC amounts can be viewed at the PO, PO/Item, shipment or entry level.

• Client can easily view the variance between the Estimated Landed cost (ELC) and ALC. 

• After analysis, Salam studios & stores have the option to finalize the actual landed costs. ALC finalization will update the stock ledger and WAC appropriately based on the actual cost instead of the estimated cost if inventory exists for the item/location. 

• If inventory does not exist for the item/location a cost variance record will be written to update the GL but not the stock ledger.

Technical Interview Question

SQL/PLSQL Questions 
===================

1) What is the use of 'Group By' & 'Having' clause & in your project where you used.
2) What is the use of cursor & types of cursor.
3) What are analytical functions in PLSQL.
4) What are bind variables in PLSQL.
5) What are different types of Triggers.
6) What is the difference between Function & Procedure.


Unix Questions
=============

1) How can you look the content of the file without opening it.
2) What is the difference between touch & cat command.
3) Difference between move & copy command.
4) How to delete a folder with multiple files.
5) If you want to move a set of files from one server to another server. How would you achieve it.


Proc Programs & Shell Scripting
==========================

1) have you complied proc programs.
2) How you have done retofiting for any proc programs.
3) Once you open a proc how it look like & what you will check first in proc program.

MOM modules Interview Questions

Question for 3 -  5 years of Experience
===============================

1) Where you will get the references of 454 calendar.
2) Once calendar is setup, where we can check start & end dates.
3) have you worked End of Month process.
4) Explain stock ledger process.
5) Role of Trandata_A & Trandata_B table in RMS.
6) What kind of data salmnth job holds & on what parameters data will rolled up.
7) What kind of Transactional data picked by salmnth job.
8) Did you involved in performing cyclic count for any client.
9) How Inventory valuation performed in MOM modules.
10) What kind of regular issues you faced at client location while processing sales in ReSA & Further in retail system.
11) Why Missing_tran_big_gap issue was coming while uploading sales from their Legacy system at go-live phase.
12) What do you mean by Post-Void in ReSA & explain...?
13) How sales get processed further from ReSA, how you did for Other modules.
14) Did you found any change is Sales process V15 & other version like V14 & V14.1.
15) Use of SVC table for sales processing.
16) ITEM_LOC ranging issue while processing sales to RMS from Legacy staging area.
17) Have to come across error like 'No Retail Item' while processing POSU files, If yes please explain ...?
18) Have you involved in creating & compiling Jobs.
19) If any multi-threaded job failed then, How you will fix the issue & re-run the job.
20) What is the role of Commit_Max_Counter in batch execution.
21) Have you worked on Replenishment for any client & How Min-Max method will work.
22) What is Lead time & pickup Lead time.

VAT Process in RMS

VAT (value-added tax)
=================
In some countries VAT also known as a goods and services tax (GST). It is a type of tax that is assessed incrementally, based on the increase in value of a product or service at each stage of production or distribution. VAT essentially compensates for the shared services and infrastructure provided in a certain locality by a state and funded by its taxpayers that were utilized in the elaboration of that product or service.

VAT is usually implemented as a destination-based tax, where the tax rate is based on the location of the consumer and applied to the sales price. Confusingly, the terms VAT, GST, consumption tax and sales tax are sometimes used interchangeably.

Overview : 

The amount of VAT is decided by the state as percentage of the end-market price. As its name suggests, value-added tax is designed to tax only the value added by a business on top of the services and goods it can purchase from the market.

To understand what this means, consider a production process (e.g., take-away coffee starting from coffee beans) where products get successively more valuable at each stage of the process. When an end-consumer makes a purchase, they are not only paying for the VAT for the product at hand (e.g., a cup of coffee), but in effect, the VAT for the entire production process (e.g., the purchase of the coffee beans, their transportation, processing, cultivation, etc.), since VAT is always included in the prices.

The value-added effect is achieved by prohibiting end-consumers from recovering VAT on purchases, but permitting businesses to do so. The VAT collected by the state is computed as the difference between the VAT of sales earnings and the VAT of those goods and services upon which the product depends. The difference is the tax due to the value added by the business. In this way, the total tax levied at each stage in the economic chain of supply is a constant fraction.


VAT in Oracle Retail (RMS)
----------------------------------------

At a system level, the tax calculations in RMS are governed by the system option called Default Tax Type. The tax type options are:

1) SALES - which is used for US only implementations and results in no tax calculations within RMS.
2) GTAX - which is used for Brazil implementations and assumes the taxes are calculated by an external tax engine via Oracle Retail Fiscal Management (RFM).
3) SVAT - which is used for all other implementations of RMS in which one or more locations for the retailer are subject to value added tax (VAT).

For SVAT implementations, retailers can additionally configure the tax processing in RMS by VAT region as follows:

i) Simple - VAT Regions defined as having a tax calc type of 'Simple' will have VAT computation based on the existing logic in TAX_SQL that uses rates defined for items to compute VAT on the transaction.

ii) Exempt - VAT Regions defined having a tax calc type of Exempt will not support VAT computation for transactions occurring in this region. For example, this is what would be used for US locations in an implementation that also contains locations for which VAT is applicable.

iii) Custom Tax - VAT Regions having a tax calc type of Custom will use Custom Taxation Rules that need to be defined by the retailer. The configuration required to implement this functionality is described in this section.


Note: If only one entity or location is passed into the tax function as described in the diagram above, then the tax calculation function behavior will be based on the tax calculation type of that entity or location. If the transaction involves two entities or locations (e.g. transfers), the tax calculation support will be based on the below matrix.

Role of VAT in RMS Application
-----------------------------------------------------

To setup the Vat in RMS, first the VAT Indicator needs to be enabled as a part of initial setup of RMS.

Next, to check, go to Control>System>System Variable>Localization. Here the user will be able to see the VAT being enabled.

Next, to see the VAT Codes, go to Control>Setup>VAT Code Maintainance. Here there will be a list of Codes.

The rates for each code will be shown, when user clicks on Rates at each Code.

Next to see the VAT regions that are setup, go to Control>Setup>VAT region maintenance 

To view the VAT Regions defined, Double click on VAT Region maintenance which has list of VAT Regions, with the VAT Code Type as defined earlier.

Defining of the VAT can be done either at Department or at the Class Level.

RMS by default is setup to define the VAT at department level. However if the VAT needs to be defined at Class Level, go to Control>System>System Parameter> Localization, and click on the “Class Level Tax” check box.

Setting up VAT at Department Level:

To setup the VAT at the Department Level, go to Action>Merchandise Hierarchy and select the Department.

Select a Specific Department and click on Options and Select VAT Maintenance to view the VAT regions assigned to the Department.

The list of all VAT Regions will be shown here, with the Rate% for each region.

Setting up the VAT Region for Store:

Go to Action>Organization Hierarchy>Stores. VAT Region will be mentioned here.

Setting up the VAT Region for Warehouse:

Go to Action>Organization Hierarchy>Warehouse. VAT Region will be mentioned here.

Setting up the VAT Region for Supplier Site:

Go to Control>Supplier. VAT Region will be mentioned here.

VAT availability at Item Level:

VAT Effect in Purchase Orders:

Once the VAT is enabled and defined in Department and defined in the Items, Purchase orders can be created for the same.

Total order Cost: 1000(1 EA)

Total Order Retail (Excl. VAT): 2000 (50% markup on cost using Cost Method of Accounting)

Total VAT: 400(20% of 2000 = VAT)

Total Order Retail (Incl. VAT) : 2400 (2000 + 400)

At this point, Data with Tran Code 87 will be updated in Tran_Data and further be send to GL.

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 negativeValue 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 areRecorded 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.

Whenever a Sale is performed, data with VAT is updated in Tran_Data with Tran Code 88 and sent to GL.

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.

Automation tool & Batches

Question for 3 -  5 years of Experience
============================

1) How restart & recovery can be handled for multi-threaded jobs.
2) Where the user specifies the number of threads for a particular job.
3) What is Oracle Wallet & how we can enable Oracle Wallet.
4) How to deploy the Automic Oracle Retail Template.
5) What recommended directories are to be setup for Automic Oracle Retail Template.
6) What is the role of Automic Rapid Automation (RA) Oracle Retail Agent & Automic Operating System Agent.
7) RA Oracle Retail agent is deployed in which components.
8) In implementation where & how FTP, or manage file transfer objects are used by client.
9) How do you get the notification of failed jobs & how do you fix it.
10) What kind of issues you come across while configuring retail jobs in Automation.
11) What do you mean by Test-run, Control-run and Dry-run.

Retail Batches & Scheduler Overview

"Hi Viewers ......This post consist of Retail Batches & Scheduler Overview"

Oracle Retail System Integration

"Viewers.....This post consisting of Integration concepts between Oracle Retail Applications "