| Oracle® Invoice Matching Operations Guide Release 13.2.9 E73506-01 |
|
 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. Initial set up of RMS must occur prior to the integration of ReIM.
The URLs for the RFI Web services that are a necessary for this integration must be maintained in the RMS_RETAIL_SERVICE_REPORT_URL table and in the ReIM integration.properties file.
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.
Once 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 the other systems. Otherwise, shared information is set up manually within each system, and the user 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:
FINANCIAL_IND=Y
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
MULTIPLE_SET_OF_BOOKS_IND = Y
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) that is being 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:
| FIF_EXCHANGE_TYPE | RMS_EXCHANGE_TYPE |
|---|---|
|
C |
C |
|
O |
O |
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, 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 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 may be 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.
| EXT_SYSTEM_ID | COMMON_ID | RETL_ID |
|---|---|---|
|
USA |
700 |
US |
|
CAN |
701 |
CA |
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 also are 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 allows 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 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:
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 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.
This section describes setup considerations for ReIM data.
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.
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:
|
Note: The Chart of accounts is updated in Oracle Retail applications only after the account is validated through Oracle General Ledger. |
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."
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 integration.properties file within ReIM must be updated with the appropriate URLs for the account validation and drill forward Web services, as listed below:
|
Note: Drill forward and drill backward functionality is applicable for Peoplesoft - RMS integration only. |
#webservice WSDL URL for drill forward webservice.financial.drill.forward.wsdl=@webservice.drill.forward.wsdl@ webservice.financial.drill.forward.url.targetnamespace= webservice.financial.drill.forward.targetsystem= #webservice WSDL URL for account validation webservice.financial.account.validation=@webservice.account.validation@ webservice.financial.account.validation.namespace=http://www.oracle.com/retail/fin/integration/services/GlAccountValidationService/v1 webservice.financial.account.validation.local.code=GlAccountValidationService #webservice username and password for account validation webservice.financial.account.validation.username=@webservice.account.validation.username@ webservice.financial.account.validation.password=@webservice.account.validation.password @
Reports are created by Business Intelligence Publisher for the following:
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:
| Row Description | DEBIT_CREDIT_IND | TRANS_AMOUNT Value |
|---|---|---|
|
Normal |
Debit |
Transaction Amount |
|
Normal |
Credit |
(-1) * Transaction Amount |
|
VAT |
Debit |
Transaction Amount * Tax Rate |
|
VAT |
Credit |
(-1) * Transaction Amount * Tax 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
| 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 integration.properties file.)
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. |
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 AccountPurge 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
reimaccountworkspacepurge batch-alias-name PURGE [ALL | <Accounts>]
Where
The argument is batch alias name.
The argument is the word PURGE.
The argument is either ALL or specific accounts to be deleted from the local table.
