| Oracle® Invoice Matching Operations Guide Release 15.0 E68579-02 |
|
 Previous |
 Next |
This chapter describes the integration between Oracle Retail systems and Oracle E-Business Suite Financials (including Oracle General Ledger and Oracle Payables), as developed and supported by Oracle Retail Financial Integration (ORFI).
When the option to integrate is chosen, the selected information is shared between the systems. Integration and validation services are in place to ensure the shared data matches.
|
Note: This chapter addresses the points within Oracle Retail systems that are essential to integration. For more information about the entire integration process, including mapping to Oracle E-Business Suite data and settings, see the ORFI document, Oracle Retail Financial Integration for Oracle Retail Merchandising Suite and Oracle E-Business Suite Financials - Implementation Guide. For more information about Web services, see the following chapters in the Oracle Retail Merchandising System Operations Guide, Volume 2: "Service Provider Implementations API Designs" and "Web Services." |
The following Oracle Retail applications are included in the integration covered by this chapter:
Oracle Retail Merchandising System (RMS)
Oracle Retail Sales Audit (ReSA)
Oracle Retail Invoice Matching (ReIM)
Oracle Retail Integration Bus (RIB)
The option to integrate must be selected during initial set up of the RMS system.
ReIM accesses RMS to determine if integration is active. The RMS set up must be done before integrating with REIM.
The URLs for the RFI Web services that are necessary for this integration are maintained in the RETAIL_SERVICE_REPORT_URL table and in the ReIM IM_SYSTEM_OPTIONS table.
Real time account validation is done only when the financial integration with Oracle E-Business Suite is ON.
Partners must be set up as suppliers in Oracle E-Business Suite. Then the partner must be manually set up in RMS using the RMS Supplier ID that was generated when the Oracle E-Business Suite supplier was interfaced to Oracle Retail. Partner functionality within RMS and ReIM can then proceed normally. The RMS supplier generated as part of this process is not used.
Payment terms and freight terms are manually maintained.
Integration of Oracle Retail applications and Oracle E-Business Suite Financials relies on synchronization of essential data, such as currency exchange rates and suppliers. Through careful discussions, the users of both systems determine the common codes and descriptions that will best serve their business needs.
When an agreement is reached, this information is set up and maintained. Depending on the volume, some shared information is set up in Oracle Retail applications or in Oracle E-Business Suite and electronically transferred to other systems. Otherwise, shared information is set up manually within each system, and the users of both systems must ensure that the code and the description match.
This section describes setup considerations for the RMS data.
As part of the RMS system options setup, set the following options as indicated:
This SYSTEM_OPTION indicates that the Oracle Retail system is integrated with a financial system:
FINANCIAL_AP=A
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 D/S/C
SUPPLIER_SITE_IND = Y
ORG_UNIT_IND = Y
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 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:
Within RMS, supplier information (such as Order From and Remit To addresses) is used for generating the purchase orders. Oracle Payables uses supplier information for payment generation. It is important 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, if a supplier exists in RMS, all data values for the supplier (except supplier name and status) are 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 and 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 check boxes.
|
Note: Supplier information is created, updated and inactivated only in Oracle Payables. This information is transferred from Oracle Payables to the participating Oracle Retail applications, where additional retail-specific attributes are maintained. |
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.
The financial calendar within Oracle Retail systems is manually set up and maintained separately from the Oracle General Ledger financial calendar.
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 are also maintained in Oracle Payables.
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.
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 allow for the association of each store and warehouse with an Org Unit. The following is an example of the Organizational Unit form:
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 multi-channel operation, the organizational unit is set up at the virtual warehouse level.
Financial sales audit and inventory information is 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:
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 is 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.
Select 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.
Select ReSA task list > Foundation Data > Data Loading > Manage Data. The manage data screen opens. Through this screen, the user can choose the General Ledger template to maintain the data using spreadsheet download and upload.
When SYSTEM_OPTIONS.FINANCIAL_AP is A, the upload validates entries of valid segment combinations.
This section describes setup considerations for ReIM data.
As part of the RMS system options setup script, set the following options as indicated:
FINANCIAL_AP =A
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. Account validation is done while executing Financial Posting batch:
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:
Table 12-3
| 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 added to IM_POSTING_DOC_ERRORS table; "One or more accounts subjected to posting is invalid”.
Segments 1 and 2 may be set up as dynamic at the Location level, or Segments 4 and 5 can be dynamic at the Department and Class level respectively. Segments defined as dynamic are allowed to be null for certain types of Basic Transaction or Reason Code cross-reference types. When a segment is null, the segment is assigned dynamically when transactions are posted. (Non-dynamic segments cannot be blank). Validation applies to the segment combination, not to individual segments.
|
Note: For Tran code TAP, each segment must have a value regardless of whether the segment is dynamic. |
The initial load for ReIM is run by Oracle E-Business Suite and includes the following information:
Suppliers
Currency Rates
|
Note: The view, mv_currency_conversion_rates should be refreshed once the initial loads of currencies from Oracle General Ledger are loaded to ReIM. |
To accommodate integration, the IM_SYSTEM_OPTOINS table should be configured with the following properties.
WS_FIN_ACC_VALID_URL - This attribute holds the URL for the financial account validation web service. WS_FIN_ACC_VALID_CRED - This attribute indicates if the account validation web service call is authenticated or not. Y means Authentication enabled. N means Authentication is not enabled (See configuring web service credentials in weblogic server). WS_FIN_DRILL_FWD_URL - This attribute holds the URL for the financial drill forward web service. WS_FIN_DRILL_FWD_CRED - This attribute indicates if the drill forward web service call is authenticated or not. Y means Authentication enabled. N means Authentication is not enabled (See configuring web service credentials in weblogic server).
The above attributes are configured in the System Options screen.
The credentials for the web service call are configured in the default domain credential store of the WebLogic server through the admin console.
Create Map:
Creating Credentials:
Select the map as Reim. The Key name should always be WS-ALIAS. Any user name / password can be assigned to the key.
Reports are created by Business Intelligence Publisher for the following:
Table 12-4
| Document Type | Report Name | Sample Report URL |
|---|---|---|
|
MRCHI |
Merchandise invoice document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/invreport.xdo |
|
NMRCHI |
Non-Merchandise invoice document Report |
http://hostname:portno /xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/invreport.xdo |
|
CRDNT |
Credit Note document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/crnreport.xdo |
|
CRDMEC |
Credit Memo cost document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/memoreport.xdo |
|
CRDMEQ |
Credit Memo quantity document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/memoreport.xdo |
|
DEBMEC |
Debit Memo cost document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/imemoreport.xdo |
|
DEBMEQ |
Debit Memo quantity document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/memoreport.xdo |
|
DEBMEV |
Debit Memo Tax document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/memoreport.xdo |
|
RWO |
Receipt Write Off document Report |
http://hostname:portno/xmlpserver_nonsso/ Guest/REIM13/Finance/invreport/rworeport.xdo |
The URL for each report must be updated in the table, RETAIL_SERVICE_REPORT_URL. The following table provides sample URLs.
Integration to Oracle General Ledger includes a number of transactions, as described below.
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:
Table 12-5
| 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 |
|
Note: Transaction Amount is taken from the database column, IM_FINANCIALS_STAGE.AMOUNT. |
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.
Table 12-6
| From | To | Transactions |
|---|---|---|
|
ReIM |
Oracle Payables |
|
|
ReIM |
Oracle General Ledger |
General Ledger accounting entries resulting from the Invoice Matching process, including:
|
|
RMS |
Oracle General Ledger |
Accounting entry data (potentially very high volume) |
|
ReSA |
Oracle General Ledger |
Accounting entry data (potentially very high volume) |
Valid chart of accounts are stored in the ReIM table, IM_VALID_ACCOUNTS, which includes the Set of Books ID (sob_id) and 20 segments. An ORFI Web service validates accounts against the Oracle General Ledger. Valid accounts are posted to IM_VALID_ACCOUNTS; invalid accounts are posted to IM_POSTING_DOC_ERROR. The following steps describe the validation process:
The ReIM system invokes the validation Web service to validate the chart of accounts. (A URL for the ORFI Web service is configured in the SYSTEM_OPTIONS table
The posting batch job checks the accounts to be posted against the IM_VALID_ACCOUNTS table.
If the chart of accounts is in the table, the transaction is posted to staging tables.
If the chart of account does not exist in the table, a collection of accounts is built. These collected accounts are validated against the Oracle General Ledger, and a status is returned.
If the status of the collected accounts is valid, the accounts are inserted in the IM_VALID_ACCOUNTS table, and the transactions are posted to the staging tables.
If the status of the accounts is NOT valid, the entire collection is flagged as errors, and transactions are posted to IM_POSTING_DOC_ERROR.
|
Note: ReIM completes the first level of account validation and posts the transaction to staging tables. It is assumed the second level of account validation is done at the end of the extraction process (where transactions are moved from ReIM staging tables to Oracle General Ledger). If account validation fails at this point, Oracle General Ledger must change the account information before transactions are loaded to Oracle General Ledger, and the chart of accounts must be re-validated in ReIM. |
A Merchandise document with a ready for match status can be pre paid before matching. The Document Search screen provides this option.
Valid chart of accounts are stored in the ReIM table, IM_VALID_ACCOUNTS, which includes the Set of Books ID (sob_id) and 20 segments. An ORFI Web service validates accounts against the Oracle General Ledger. Valid accounts are posted to IM_VALID_ACCOUNTS; invalid accounts are posted to IM_POSTING_DOC_ERROR. The following steps describe the validation process:
The ReIM system invokes the validation Web service to validate the chart of accounts. (A URL for the ORFI Web service is configured in the SYSTEM_OPTIONS table.
The Prepay operation checks the accounts to be posted against the IM_VALID_ACCOUNTS table.
If the chart of accounts is in the table, the transaction is posted to staging tables.
If the chart of account does not exist in the table, a collection of accounts is built. These collected accounts are validated against the Oracle General Ledger, and a status is returned.
If the status of the collected accounts is valid, the accounts are inserted in the IM_VALID_ACCOUNTS table, and the transactions are posted to the staging tables.
If the status of the accounts is NOT valid, the entire collection is flagged as errors, and transactions are posted to IM_POSTING_DOC_ERROR and the user will be shown an error message "One or more accounts subjected to posting is invalid".
As account information is changed in the Oracle General Ledger, Retail must re-validate the locally stored chart of accounts. Oracle General Ledger will not propagate chart of account changes to Retail. The AccountWorkspacePurge Batch can clear all valid accounts in the IM_VALID_ACCOUNTS table or only those that are considered updates in Oracle E-Business Suite.
Usage
AccountWorkspacePurge userid/password PURGE [ALL | <Accounts>]
Where:
The argument is a combination of user ID and password.
The argument is the word PURGE.
The argument is either ALL or specific accounts to be deleted from the local table.
