Oracle Retail's Integration with Financial Systems

Welcome again Oracle Retail Fans! This was one of the most requested articles and I finally got some time to write about. In this article, we will be discussing Oracle Retail’s integration with Finance. Oracle out of the box supports integration between Oracle Retail and E-Business and PeopleSoft with its own integrator tool called Oracle Retail Financial Integration (ORFI) for E-Business Financials and AIA PIP for PeopleSoft, however Retailer’s use many other ways to integrate RMS with Finance System. Most common way is using tools supporting ETL or some Messaging Systems. Our focus in this article will be limited to E-Business Integration only and not to any other Finance system, however, details mentioned in this article are common for other fiancé systems too.

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.

We’ll be covering following areas in detail:

1.     Oracle Retail Financial Integration (RFI) for E-Business Financials – Overview

2.     Oracle Retail Sales Financial Business Process Flow

3.     Oracle Retail Inventory Financial Business Process Flow

4.     Oracle Retail Merchandise Procure to Pay Business Process Flow

5.     Integration Process for Currency Exchange Rate

6.     Integration Process Suppliers Information

7.     Integration Process for Requesting Chart of Accounts Validation

8.     Integration Process for Inventory Valuation and Revenue Recognition Accounting

9.     Integration Process for Retail Merchandise Procure to Pay

10.  Oracle Retail GL/Finance Related System Configurations

a.     RMS Business Processes Configuration

b.     ReIM Data Setup and Configuration

11.  Oracle Retail General Ledger (GL) Batches

12. Tran-Data Codes Impacting General Ledger (GL)

1.   Oracle Retail Financial Integration (RFI) for E-Business Financials – Overview 

Oracle Retail supports direct integration Oracle Retail Merchandising System (RMS), Oracle Retail Sales Audit (ReSA) and Oracle Retail Invoice Matching (ReIM) can be integrated with Oracle E-Business Suite 12.x. This integration allows RMS to obtain the following key data from Oracle E-Business Suite:

·         General ledger accounting data

·         Supplier and supplier site data

·         Currency rates

·         Freight terms

·         Payment terms

The Retail Financial Integration (RFI) for E-Business Financials provides integration to a robust enterprise financial system to complement the Oracle Retail Merchandising system in a retail customer environment.

Retail Financial Integration (RFI) includes the following four processes: 

1.     Life Cycle Data Management

This process provides data synchronization for the initial load prior to implementation and incremental data creation and maintenance after implementation. This process synchronizes supplier and currency exchange rates from the E-Business applications to the Oracle Retail Merchandising System (RMS). Oracle Payables is the source of suppliers, payment terms, and freight terms. Because the freight and payment terms are static in nature and their volume is very low, they are synchronized between the two systems manually. This process enables users of the Oracle Retail Merchandise suite to carry out functions with data that is shared with the E-Business applications while creating and updating this data.

2.     Inventory Valuation (Retail stock ledger)

This process enables the posting of accounting entries generated from transactions that change the value of sellable products at a retailer’s inventory locations (stores and warehouses) to the appropriate ledgers from Oracle Retail Merchandising – stock ledger to Oracle General Ledger (GL). This process records the financial impact of changes in the sellable inventory in store and warehouse locations. Valuation of sellable inventory in the stores and warehouses is based on the processing of transactions for movement, pricing, costing, and sale of the inventory. This valuation is captured and processed in Oracle Retail stock ledger. These transactions include sales, shipments from warehouse to store, store receipts, store transfers, returns to vendors, price changes, stock counts, and shrinkage due to theft or damage.

3.     Retail Revenue Recognition

This process enables the posting of accounting entries generated from sales and returns transactions from the retailer’s stores for revenue and cash reconciliation to the appropriate ledgers. In this process, the data flows from Oracle Retail Sales Audit (ReSA) to Oracle General Ledger (GL). This process records the financial impact of sale/return, cash reconciliation, and void transactions from stores. The Revenue Recognition process begins when store transactions (sales and returns) are processed by ReSA. For each store transaction, ReSA generates the appropriate accounting entries to be posted to the Oracle GL. Each accounting entry has a valid account code segment combination based on the transaction type, business unit, and location (store or warehouse).

4.     Retail Merchandising Procure to Pay

This process begins with the Oracle Retail Invoice Matching (ReIM) application. Invoices from suppliers for retail merchandise are matched to the original purchase order (PO) for the merchandise and the receipt of the merchandise by the retailer. A proper match of invoice, PO, and receipt trigger the payment authorization of the supplier’s invoice. Invoices may be authorized for payment prior to receipt of goods for which prepayment is required. When the authorization for payment is generated, the appropriate accounting distribution is also generated to support the payment authorization. The Retail Merchandise Procure to Pay integration automates the processing of invoice payments, adjustments, and write-offs from ReIM to Oracle Payables and GL. Other accounting transactions are generated from ReIM to write off aged receipts that were never invoiced and to post accounting distribution for manually paid or prepaid invoices after receipt.

2.   Retail Sales Financial Business Process Flow 

The Retail Sales Financial business process consists of the post channel sales, cash, and deposits from ReSA to Oracle GL integration flow. This diagram illustrates the Retail Sales Financial business process flow:

ReSA sends summarized sales audit information to Oracle GL for the Sales Journal. The sales audit information includes channel sales, cash, and deposits. The ReSA Export processes select and format corrected and pre audited data from the ReSA database so that it can be sent to E-Business Financials.

ReSA includes programs to automatically extract the required totals data and to format it to generic data files from a financial staging table for import into Oracle GL. Sales audit data from ReSA is also posted directly to the RMS stock ledger and can be integrated into Oracle GL through the stock ledger to the financial staging table and the accounting entry table. Before data is imported into Oracle GL, a batch process writes balanced records to the financial staging table using the appropriate General Ledger account combinations (maintained in Cross Reference tables in ReSA) and the Currency Exchange Rates.

For journal entries, an Oracle Data Integrator (ODI) process is used to load the accounting entries into the GL_Interface table. A concurrent request, Journal Import, is then run to move the journal entries into the GL transaction tables. 

3.   Retail Inventory Financial Business Process Flow 

The Retail Inventory Financial business process consists of the following integration flows:

·         Post stock ledger from RMS to Oracle GL

·         Post write-offs (aged receipts, not invoiced to ledger) from RMS to Oracle GL

Below diagram illustrates the Retail Inventory Financial business process flow:

The stock ledger in RMS records financial results of the merchandising processes that occur in the Retail system, such as buying, selling, price changes, transfers, and so on. All of these transactions are recorded in the RMS stock ledger and rolled up to the subclass or location level for days, weeks, and months. Daily and period-based financial information is scheduled to be loaded into the E-Business Suite Financials. RMS sends three levels of stock ledger information to Oracle GL:

·         Monthly – no access to detailed reference information

·         Daily by subclass, class, or department – no access to detailed reference information.

·         Daily by transaction 

The stock ledger transactions to be loaded into E-Business Suite Financials are placed on the financial staging table through the use of table triggers or batch, by means of the appropriate General Ledger account combinations (maintained in the RMS cross-reference table in Oracle Retail) and the currency exchange rates.

For journal entries, an ODI process is used to load the accounting entries into the GL_Interface table. Then a concurrent request, Journal Import, is run to move the journal entries into the GL transaction tables.

4.   Oracle Retail Merchandise Procure to Pay Business Process Flow 

The Retail Merchandise Procure to Pay business process consists of the following integration flows:

·         Post matched prepaid invoices from ReIM to Oracle GL

·         Post manually matched paid Invoices from ReIM to Oracle GL

·         Post receipt write-offs from ReIM to Oracle GL

·         Post matched invoices for payment from ReIM to Oracle Payables

·         Post credit notes (matched or unmatched) for payment adjustment from ReIM to Oracle Payables

·         Post debit or credit memos for payment adjustment from ReIM to Oracle Payables

·         Post rebates for payment adjustment from ReIM to Oracle Payables

·         Post unmatched invoices for prepayment from ReIM to Oracle Payables

This diagram illustrates the Retail Merchandise Procure to Pay business process flow:

Retail Merchandise Procure to Pay business process flow (1 of 2)

Retail Merchandise Procure to Pay business process flow (2 of 2)

The Retail Merchandise Procure to Pay business process flow enables posting of matched invoices, matched credit notes, debit and credit memos, rebates, and unmatched invoices for prepayment from ReIM to Oracle Payables. The payables invoices are placed in the AP Interface tables. Then a concurrent request, Payables Open Invoice Import, is run to move the payables invoice into the payables transaction tables.

5.   Integration Process for Currency Exchange Rate Integration 

The currency exchange rate is the reference information used in the translation of monetary values from one currency to another. The exchange rate expresses the value of one currency in terms of another. The process integration for currency exchange rates enables Retailers to use Oracle Financials as an accounting engine and Oracle Retail for sales audit and stock ledger transactions.

The process integration for currency exchange rates supports the following integration flows:

·         Load initial currency exchange rate from Oracle GL to RMS: Enables the loading of all historical, current and future effective dated currency exchange rates from Oracle GL to RMS for a new instance (logical or physical) of RMS.

·         Incremental creation and updates of currency exchange rates from Oracle GL to RMS: Enables the synchronization of incremental creation and updates of the historical, current and future effective dated currency exchange rates from Oracle GL to RMS.

6.   Integration Process Suppliers Information 

Between the two systems, Oracle Payables is the source of valid suppliers (vendors in Oracle Payables) and their Remit to Location and Order from addresses. The supplier integration synchronizes supplier’s information from Oracle Payables to RMS through these integration flows:

·         Load initial suppliers from Oracle Payables to RMS: Enables the loading of all active merchandise suppliers, the current effective supplier locations and their current effective remit and order to addresses.

·         Incremental creation and updates of suppliers from Oracle Payables to RMS: Enables the synchronization of incremental creation and updates of the active suppliers from Oracle Payables to RMS.

7.   Integration Process for Requesting Chart of Accounts Validation

Oracle General Ledger (GL) is the system of record for chart of accounts segment combinations. Chart of accounts (segments) combinations are set up manually in Oracle Retail Merchandising System (RMS), Retail Invoice Matching (ReIM), and Retail Sales Audit (ReSA). This manual setup enables assignment of transaction data in sales audit, stock ledger, and invoice match to specific account codes.

ReIM also creates segment combinations dynamically during the invoice match transaction processing.

While creating the valid segment combinations, Oracle Retail validates each created combination individually against Oracle GL. Any valid segment combination in Oracle Retail must exist as a valid combination in Oracle GL. Oracle Retail publishes the segment combination to Oracle GL for validation. Oracle GL verifies the combination and returns the status to Oracle Retail. If the combination is valid, the combination is stored in the Oracle retail database tables for future validation purpose.

Oracle Retail sends these data for validation to Oracle GL:

·         Requesting system (RMS, ReIM, or ReSA)

·         Set of books (GL business unit)

·         Segment or ChartField combination values

Oracle GL sends the validation status whether the combination is valid. The RFI layer copies all the other information from the original request and sends these response data to Oracle Retail:

·         Requesting system (RSM, ReIM, or ReSA)

·         Set of books (GL business unit)

·         Date

·         Validation status – valid or invalid

8.   Integration Process for Inventory Valuation and Revenue Recognition Accounting

The process integration for accounting entries enables records the financial impact of changes to sellable store and warehouse inventory. It also records the financial impact of sales and returns, cash reconciliation, and void transactions from stores.

The system sends sales audit and stock ledger data in Oracle Retail to Oracle GL through accounting entries. Similarly the system sends the prepaid invoice reversals and write-offs of aged receipts Oracle GL. RMS, ReIM, and ReSA are the source for accounting entry. After successful completion of data transfer, the system deletes the data from the source interface tables.

This integration uses the Oracle Data Integrator (ODI) application to transfer the data from the Oracle Retail applications to the Oracle GL application. The process integration between Oracle Retail (RMS, ReSA, and ReIM) and Oracle GL supports the following integration flows:

·         Post stock ledger from RMS to Oracle GL

·         Post channel sales, cash, and deposits from ReSA to Oracle GL

·         Post write-offs (aged receipts, not invoiced to ledger) from RMS to Oracle GL

·         Post prepaid invoice reversals after receipt from ReIM to Oracle GL

9.   Integration Process for Retail Merchandise Procure to Pay

ReIM is the source of matched invoices, credit notes, debit memos, and rebates. Oracle Payables requires these details for payment to the suppliers. Invoices from suppliers for retail merchandise are matched to the original purchase order (PO) for the merchandise and the receipt of the merchandise by the retailer. A proper match of invoice, PO, and receipt trigger the payment authorization of the supplier’s invoice. Invoices may be authorized for payment prior to receipt of goods for which prepayment is required. When the authorization for payment is generated, the appropriate accounting distribution is also generated to support the payment authorization. The Retail Merchandise Procure to Pay integration automates the processing of invoice payments, adjustments, and write-offs from ReIM to Oracle Payables and General Ledger. Other accounting transactions are generated from ReIM to write off aged receipts that were never invoiced and to post accounting distribution for manually paid or prepaid invoices after receipt.

This integration uses the Oracle Data Integrator (ODI) application to transfer the data from ReIM to Oracle Payables. The process integration between ReIM and Oracle Payables supports the following integration flows:

·         Post matched invoices for payment from ReIM to Oracle Payables.

·         Post credit notes (matched or unmatched) for payment adjustment from ReIM to Oracle Payables.

·         Post debit or credit memos for payment adjustment from ReIM to Oracle Payables.

·         Post rebates for payment adjustment from ReIM to Oracle Payables.

·         Post unmatched invoices for prepayment from ReIM to Oracle Payables.

10.        Oracle Retail GL/Finance Related System Configurations

a.   RMS Business Processes Configuration

RMS System Options

To set RMS SYSTEMS_OPTION table:

1.     Set the FINANCIAL_IND=Y, SYSTEM_OPTION indicates that the Oracle Retail system is integrated with a financial system:

2.     Set FINANCIAL_AP=A.

3.     A value of A indicates that the financial system to which RMS is interfaced is Oracle E-Business Suite through Oracle Retail Financial Integration (ORFI).  GL_ROLL_UP can be either be value D, S, or C.

·         MULTIPLE_SET_OF_BOOKS_IND = Y

·         SUPPLIER_SITE_IND = Y

·         ORG_UNIT_IND = Y

Organization Units

Use the Organizational Unit window (RMS Start Menu > Control > Setup > Org Unit >Edit) to define organizational units in RMS that match those being setup in Oracle E-Business Suite. When an organizational unit is entered in RMS, the valid organizational units are those associated with the Set Of Books (SOB) used for the general ledger interface.

Currency Exchange Rates

Currency exchange rate is used to translate the monetary value of one currency in terms of another. Depending on business needs, a Currency Exchange Rate Type of Operational or Consolidation is selected for use in all transactions.

This value is set up manually in RMS and mapped to Oracle E-Business Suite through the Currency Exchange Type mapping window. Currency Exchange Rate data is owned by Oracle General Ledger and updates are sent to Oracle Retail applications.

Determine the Exchange Type being sent by Oracle General Ledger (for example, Consolidation or Operational) that you want RMS to use. Update the FIF_CURRENCY_XREF for mapping the external exchange type being sent by Oracle General Ledger with RMS Exchange Type.

For example, for Consolidation and Operational exchange types, the FIF_CURRENCY_XREF table holds the following entries:

FIF_EXCHANGE_TYPE	RMS_EXCHANGE_TYPE
C	C
O	O

Supplier Address Types

Within RMS, supplier information (such as Order From and Remit To addresses) is used in generating purchase orders. PeopleSoft uses supplier information for payment generation. It is important, then, that this information is synchronized.

Suppliers are created in Oracle Payables and exported to RMS. When FINANCIAL_AP is set to A, suppliers cannot be created using the RMS forms. However, after the supplier exists in RMS, all data values for the supplier (except supplier name and status) continue to be updated using the RMS forms. The association of supplier sites to organization units is accessed only in view mode through RMS forms. One supplier site per supplier organization unit combination can be marked as primary payment site.

Where SYSTEM_OPTIONS.FINANCIAL_AP is A, disable auto-generate supplier/partner numbers and associated checkboxes.

Country Codes

When country codes are defined and seeded in RMS, ensure that country codes are mapped to Oracle E-Business Suite country codes through RFI DVM mapping. The following is an example of RFI DVM Mapping (Table RFI_XREF_DVM, available in RFI schema in Retail.

EXT_SYSTEM_ID	COMMON_ID	RETL_ID
USA	700	US
CAN	701	CA

Financial Calendar

The financial calendar within Oracle Retail systems is manually set up and maintained separately from the Oracle General Ledger financial calendar.

Freight Terms

A freight term is an agreement between the retailer and a supplier regarding transportation charges for goods delivered by the supplier. Freight terms are used by RMS as purchase orders are generated.

Within the RMS system, freight terms are set up and maintained manually. They also are maintained in Oracle Payables.

Payment Terms and Currency Exchange Rates

Currency exchange rates are created and updated in Oracle General Ledger and exported to RMS. Changes to Retail currency exchange rates are not propagated to Oracle General Ledger. Payment terms, however, are manually set up and maintained in each system.

Oracle E-Business Suite Financials Units and Site IDs

The data concepts of Org Units and Site IDs in RMS mirror the data maintained in Oracle E-Business Suite. RMS forms are used to manage and view Oracle Org Units and Site IDs. The RMS windows for Store and Warehouse maintenance allows for the association of each store and warehouse with an Org Unit. The following is an example of the Organizational Unit form:

Store and Warehouse Maintenance

The organizational unit is found in the Store Maintenance and Warehouse forms, which allow the Oracle E-Business Suite operating unit to be associated with the Store or Warehouse. When RMS is set up for single-channel operation, the organizational unit is set at the physical warehouse level. When RMS is set up for multi-channel operation, the organizational unit is set up at the virtual warehouse level. Financial sales audit and inventory information can then be identified through interface routines and posted to the appropriate general ledger accounts. An organizational unit must be designated for each Store and Warehouse location in the RMS.

The following are examples of the Store Maintenance and Warehouse Maintenance forms:

RMS Stock Ledger

RMS tracks all inventory movements within the system through its stock ledger. Daily and period-based financial information transfers can be scheduled to Oracle General Ledger. RMS allows three different levels to interface stock ledger information to Oracle General Ledger:

·         Monthly

·         Daily by subclass, class, or department

·         Daily by transaction

The daily summarized interface and the monthly interface do not provide any access to detailed reference information. This program summarizes stock ledger data from the monthly stock ledger table

(MONTH_DATA), based on the level of information required, and writes the data to the financial general ledger staging table. Stock ledger information can be rolled up at department, class, or subclass level. The level at which information is rolled up is determined by the gl_rollup field in the SYSTEM_OPTIONS table.

RMS General Ledger Setup

For RMS and ReSA, manual setup is required for validating the chart of accounts. Valid chart of accounts are created and stored in general ledger cross reference tables. Once the validation is completed, transaction data can be assigned to specific account codes.

Ongoing maintenance of the chart of accounts information (such as adding, changing, or deleting chart of accounts) requires re-validation. In this regard, Oracle General Ledger is the system of record, as it is used to verify the chart of accounts used by Oracle Retail applications. When these applications send a chart of accounts for validation, Oracle General Ledgers issues a message with:

• Valid or invalid status

• Response date

• Chart of accounts

The RMS table, FIF_GL_SETUP, stores the Oracle E-Business Suite Set of Books IDs to post financial information. This table must be setup manually after Set of Books IDs are determined. Where a system indicator Multiple Set of Books ID is set to N, FIF_GL_SETUP must hold a single Set of Books (SOB) record. The Set of Books IDs is associated with the chart of accounts when setting up general ledger cross-reference.

RMS General Ledger Cross Reference

Navigate: RMS Start Menu > Finance> GL Cross Reference. The General Ledger Search window opens. Map Chart of Accounts to department, Class, Subclass, Set Of Books, location, and transaction codes using the GL cross reference form in RMS.

ReSA General Ledger Cross Reference

Navigate: ReSA main menu > Action > Sales Audit > Control > Setup > GL Account Maintenance. The General Ledger Search Form window opens. Where SYSTEM_OPTIONS.FINANCIAL_AP is A, the form requires the entry of valid segment combinations.

b.   ReIM Data Setup and Configuration

System Options

As part of the RMS system options setup script, set the following options as indicated:

·         FINANCIAL_IND =Y

·         FINANCIAL_AP =A

As part of the ReIM system options setup script, DEFAULT_PAY_NOW_TERMS must be updated with the default term ID.

Chart of Accounts Setup

The chart of accounts is set up manually in Oracle Retail applications and in Oracle General Ledger. All account combinations are set up in each Set Of Books. The following is an example of the GL Cross Reference screen:

Segment Mapping

The retailer determines how many segments are populated. Up to 20 account segments can be specified. The following is an example of how segments are mapped between the ReIM transaction table and Oracle General Ledger:

ReIM Segments	Oracle General Ledger Chart of Accounts
Segment 1	PRODUCT
Segment 2	ACCOUNT
Segment 3	ALTACCT
Segment 4	OPERATING_UNIT
Segment 5	FUND_CODE
Segment 6	DEPTID
Segment 7	PROGRAM_CODE
Segment 8	CLASS_FLD
Segment 9	BUDGET_REF
Segment 10	BUSINESS_UNIT_PC
Segment 11	PROJECT_ID
Segment 12	ACTIVITY_ID
Segment 13	RESOURCE_TYPE
Segment 14	RESOURCE_CATEGORY
Segment 15	RESOURCE_SUB_CAT
Segment 16	CHARTFIELD1
Segment 17	CHARTFIELD2
Segment 18	CHARTFIELD3
Segment 19	AFFILIATE
Segment 20	AFFILIATE_INTRA1

If any one of the values in the 20 segments does not match the Oracle General Ledger, the account combination is considered as invalid. The following error message is issued to the user: "Account combination is invalid in the financial system."

ReIM Transactional Maintenance

Integration to Oracle General Ledger includes a number of transactions, as described below.

Calculation of TRANS_AMOUNT

The TRANS_AMOUNT field in the im_financial_stage table stores the value of the journal entry to be posted to Oracle General Ledger. (The currency for the calculated amount is the currency assigned to the transaction.) The TRANS_AMOUNT value is calculated as follows (Transaction Amount is taken from the database column, IM_FINANCIALS_STAGE.AMOUNT):

Row Description	DEBIT_CREDIT_IND	TRANS_AMOUNT Value
Normal	Debit	Transaction Amount
Normal	Credit	(-1) * Transaction Amount
VAT	Debit	Transaction Amount * VAT Rate
VAT	Credit	(-1) * Transaction Amount * VAT Rate

Generation of Outgoing Data

A staging table accommodates the outgoing transfer of data. The reference key assigned to each document or receipt is used to find data on this table.

From	To	Transactions
ReIM	Oracle Payables	Invoices Debit Memos Credit Memos Credit Notes
ReIM	Oracle General Ledger	General Ledger accounting entries resulting from the Invoice Matching process, including: Pre-paid invoices Receipt Write-offs
RMS	Oracle General Ledger	Accounting entry data (potentially very high volume)
ReSA	Oracle General Ledger	Accounting entry data (potentially very high volume

11.        Oracle Retail General Ledger (GL) Batches 

The following are the General Ledger related batch in RMS:

1.     DEALFINC.PC (Deal Fixed Income)

2.     FIFGLDN1.PC (Financial General Ledger Download 1)

3.     FIFGLDN2.PC (Financial General Ledger Download 2)

4.     FIFGLDN3.PC (Financial General Ledger Download 3)

1.  dealfinc (Deal Fixed Income)

This module writes to the STG_FIF_GL_DATA financial staging table to perform stock ledger processing for fixed deals. 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. Each merchandise level/location on the deal has an associated contrib_ratio to determine how much of the total amount will be apportioned to items in that merchant/location. Because the user could have entered any fraction of 1 into these fields, the contrib_ratios probably does not add up to 1. Therefore, the ratios are prorated across all locations so they add up to 1. This value is then apportioned between all subclasses for the general ledger.

2.  fifgldn1 (General Ledger Interface)

This program extracts the detailed stock ledger information for certain transaction types on a daily basis in order to bridge the information to an interfaced financial application.

The program reads from the IF_TRAN_DATA table for each transaction type/amount type and posts it to the Oracle Retail general ledger table (STG_FIF_GL_DATA) at the SKU detail level.

3.  fifgldn2 (General Ledger Interface)

This program summarizes stock ledger data from the transaction staging table (IF_TRAN_DATA) based on the level of information required and writes it to the financial general ledger staging table. The transactions extracted are determined by the code_type ‘GLRT’ (general ledger rolled transactions). The written information can then be extracted by the financial applications for general ledger purposes. Stock ledger information may be rolled-up at department, class or subclass level. The level at which information is rolled-up to is determined by the gl_rollup field on the SYSTEM_OPTIONS table.

4.  fifgldn3 (General Ledger Interface)

This program summarizes stock ledger data from the monthly stock ledger table (MONTH_DATA) based on the level of information required and writes it to the financial general ledger staging table. The transactions extracted are determined by the code_type ‘GLRT’ (general ledger rolled transactions). Written information is then sent to the financial applications for general ledger purposes. Stock ledger information may be rolled-up at department, class or subclass level. The level at which information is rolled-up to is determined by the gl_rollup field on the SYSTEM_OPTIONS table.

12.   Tran-Data Codes Impacting General Ledger (GL) 

Code description	Tran code	Cost/retail	Stock ledger?
Book Transfers In	31	Cost/retail	Yes
Book Transfers Out	33	Cost/retail	Yes
Cash Discount	81	Retail	Yes
Clearance Markdown	16	Retail	Yes
Close Stock	52
Cost Variance	70	Cost	Yes
Cost Variance - Cost Accounting	72	Cost	Yes
Cost Variance – Rec. Cost Adj. FiFO	73	Cost
Cost Variance - Retail Accounting	71	Cost	Yes
Deal Income (purchases)	7	Cost (represents income)	Yes
Deal Income (sales)	6	Retail (represents income)	Yes
Employee Discount	60	Retail	Yes
Expense Up Charge - Receiving Location	29	Cost	Yes
Fixed Income Accrual	8	Cost (represents income)	No
Freight	26	Cost	Yes
Freight claim	62	Cost/retail	Yes
Gross Margin	53
HTD GAFS	54	Retail	Yes
Inter Stocktake Sales Amt	55	Retail or cost depending on accounting method	No
Inter Stocktake Shrink Amt	56	Retail or cost depending on accounting method	No
Intercompany in	37	Cost/retail	Yes
Intercompany Margin	39	N/A (intercompany out at retail less intercompany out at cost)	No
Intercompany markdown	18	Retail	Yes
Intercompany markup	17	Retail	Yes
Intercompany Out	38	Cost/retail	Yes
Markdown Cancel	14	Retail	Yes
Markup	11	Retail	Yes
Markup Cancel	12	Retail	Yes
Net Sales	1	Cost/retail	Yes
Net Sales VAT Exclusive	2	Retail	Yes
Non-inventory Items Sales/Returns	3	Retail	No
Non-inventory VAT Exclusive Sales	5	Retail	No
Open Stock	50
Ordering	0
Permanent Markdown	13	Retail	Yes
Profit Up Charge - Receiving Location	28	Cost	Yes
Promotional Markdown	15	Retail	Yes
Purchases	20	Cost/retail	Yes
QC RTV	27	N/A	No
Reclassifications In	34	Cost/retail	Yes
Reclassifications Out	36	Cost/retail	Yes
Restocking Fee	65	Cost	Yes
Return to Vendor	24	Cost/retail	Yes
Returns	4	Cost/retail	Yes
Stock Adjustment	22	Cost/retail	Yes
Stock Adjustment – COGS	23	Cost/retail	Yes
Stocktake Actstk Cost/Retail	61	Cost/retail	No
Stocktake Bookstk Cost	59	Cost	No
Stocktake Mtd Sales Amt	57	Retail or cost depending on accounting method	No
Stocktake Mtd Shrink Amt	58	Retail or cost depending on accounting method	No
Transfers In	30	Cost/retail	Yes
Transfers Out	32	Cost/retail	Yes
Unavailable Inventory Transfer	25	Units only	No
Vat In Retail	87	Cost
Vat Out Retail	88	Retail
WO Activity – Post to Financials	64	Cost	Yes
WO Activity - Update Inventory	63	Cost	Yes
Wholesale/Franchise Markdowns	85	Retail	Yes
Wholesale/Franchise Markups	84	Retail	Yes
Wholesale/Franchise Restocking fee	86	N/A	No
Wholesale/Franchise Returns	83	Cost/retail	Yes
Wholesale/Franchise Sales	82	Cost/retail	Yes
Workroom/Other Cost of Sales	80	Retail	Yes

Summary Note

While this article was mainly focusing on Oracle Retail and E-Business Integration, majority of details mentioned above is common for integrating any other Financial System with RMS/Oracle Retail. Please do let me know in case of any question or feedback. In next article, we are going to strategies and best practices on Oracle Retail Application Testings. 

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.

— Share It —

• Facebook
• Twitter
• Google+
• Linkedin
• Pin It

About Nagesh Mishra

Nagesh Mishra - A Passionate Oracle Retail Certified Professional with more than 17 years of overall experience in IT industry and more than 15 years of domain expertise in Oracle Retail Applications. Worked extensively in diversified fields of Product Implementation, Business Consulting, Pre-Sales, Application Software Development, Maintenance and Support and Re-Engineering Oracle Retail projects.