| Oracle® Retail Financial Integration for Oracle Retail Merchandise Operations Management and Oracle Financials Oracle® Retail Financial Integration for Oracle Retail MOM and Oracle Financials Release 16.0.027 E94816-01 |
|
 Previous |
 Next |
| Oracle® Retail Financial Integration for Oracle Retail Merchandise Operations Management and Oracle Financials Oracle® Retail Financial Integration for Oracle Retail MOM and Oracle Financials Release 16.0.027 E94816-01 |
|
Previous |
Next |
This chapter discusses the following:
Setting up Oracle Retail applications
Setting up Oracle General Ledger
Setting up Oracle Payables
Setting up Oracle Domain Value Maps
Synchronizing freight terms manually
Synchronizing currency rate types manually
Synchronizing currency codes manually
Synchronizing chart of accounts values manually
Synchronizing financial calendar manually
Mapping business and organization units
Working with cross-references
Working with Domain Value Maps (DVMs)
Handling errors
Setting configuration properties
Oracle Retail applications, Oracle General Ledger (GL), and Oracle Payables must be set up for the Oracle Retail to Oracle Retail Financial Integration (RFI) to work properly. This chapter describes these setups in detail.
Before integration, you must set up Oracle Retail Merchandising System (RMS), Oracle Retail Invoice Matching (ReIM), and oracle Retail Sales Audit.
For more information, see the ReIM Operations Guide, version 16.0.0, RMS Operations Guide, Volume 3, version 16.0.0 for setting up the Oracle Retail applications.
To set up Oracle GL, perform these actions:
Activate service operations, queue, and handlers
Activate or create routings
Set up end points (connector properties) for provided services
Setup recurring run controls for delay publish of effective dated objects
Activate validation service operations
Set up cross-reference tables
Synchronize currency exchange rate
For more information, see the Oracle General Ledger Implementation Guide, Release 12.2.4.
To set up Oracle Payables, perform these actions:
Activate service operations, queue, and handlers
Activate or create routings
Set up end points (connector properties) for provided services
Set up recurring run controls for delay publish of effective dated objects
Activate validation service operations
Set up cross-reference tables
Synchronize payment terms and vendors
For more information, see the Oracle Payables Implementation Guide, Release 12.2.4.
The diagrams below illustrates the Database schema topology for EBS and PeopleSoft integration:-
Before running the supplier flow, you must manually load ORG_ID_XREF, FREIGHT_TERMS_XREF cross-reference in RFI_XREF_DVM table. Because no automated process for this synchronization is available, you need to load this table manually.
If the EBS is oracle financials integrating system then PAYMENT_TERMS_XREF should also be manually loaded into RFI_XREF_DVM table. If PeopleSoft is oracle financials integrating system, PAYMENT_TERMS_XREF will be loaded using the initial load script provided with RFI pack.
Set up these Oracle Domain Value Maps (DVMs):
Table 6-1 Oracle Domain Value Maps (DVMs)
| Name | Description |
|---|---|
|
ADDRESS_COUNTRY_DVM |
Country Code DVM |
|
BUSINESS_UNIT_DVM |
Business Unit Mapping |
|
COA_STATUS_DVM |
DVM mappings for valid/invalid status in Retail to TRUE/FALSE in Oracle Financials (EBS or PeopleSoft) |
|
CURR_CODE_DVM |
Currency code mapping |
|
CURR_CONV_TYPE_CODE_DVM |
Currency Exchange Conversion Type Mapping |
|
LANGUAGE_CODE_DVM |
Language Code DVM |
|
STATE_DVM |
State Code DVM |
|
SUPPLIER_ADDRESS_TYPE_DVM |
Supplier Address Type DVM (Order and Remittance), 04/06 in Retail to PURCHASING_SITE_FLAG/ PAY_SITE_FLAG in EBS |
|
SUPPLIER_STATUS_CODE_DVM |
Supplier Status DVM |
|
TAX_DVM |
Tax code (VAT) DVM |
|
CURRENCY_NOTIFICATION_DVM |
Service specific DVM |
|
SUPPLIER_NOTIFICATION_DVM |
Service specific DVM |
|
PAYTERMS_NOTIFICATION_DVM |
Service specific DVM |
|
COA_GLELEMENT_DVM |
Chart of Account Element - Segment Mapping |
|
COA_GLELEMENT_LEDGER_GROUP_DVM |
Ledger Group mapping with Business Unit. Used only when PeopleSoft is the Oracle Financials applicaiton |
Freight is the transportation charge paid by retailers when they receive goods from a supplier. Freight term is an agreement between a retailer and a supplier regarding the type and payment of freight. Because the volume of freight terms is low, they are maintained and synchronized manually in Oracle Payables and RMS.
Oracle Retail allows for multiple currency exchange rate types (such as operational, consolidated, letter of credit or bank, purchase order, customs entry, and transportation) but uses only one type for the default processing-either Operational or Consolidation.
The Oracle Financials system supports any number of currency exchange rate types and allows for multiple currency exchange rate types. Because Oracle Retail uses only one currency exchange rate for the default processing, one exchange rate is selected. The selected currency exchange rate is used by all of the Oracle Retail products that integrate with Oracle Financials (EBS or PeopleSoft).
The Oracle Financials system can have separate currency exchange rate types that are not integrated with Oracle Retail. The relationship between Oracle Retail and Oracle rate types is mapped in a Domain Value Mapping (DVM) table.
When the selected currency exchange rate type is manually set up in RMS, RMS uses the Currency Exchange Type Mapping window to map the external exchange type sent by the Oracle Financials system. This currency exchange rate type is used by the Oracle Retail applications for all transactions.
Because the volume of currency exchange rate types is low, they are maintained and synchronized manually in Oracle GL (EBS or PeopleSoft) and RMS. These are not synchronized automatically. This synchronization is required for the synchronization of currency exchange rates between Oracle GL and RMS. For more information, see the Oracle General Ledger Implementation Guide, Release 12.2.4 or PeopleSoft GL 9.2.
Oracle Retail and Oracle GL synchronize the currency codes manually. The currency codes mapping between Oracle GL and Oracle Retail is required for the sales audit and stock ledger transactions. For more information, see the Oracle General Ledger Implementation Guide, Release 12.2.4 or PeopleSoft GL 9.2 release.
A chart of accounts is a listing of all the accounts in the general ledger. You can use a chart of accounts to view specific information about a transaction.
Oracle Retail uses the chart of accounts as reference information to create accounting entries for both sales and inventory transactions. Oracle General Ledger (GL) is the system of record for this information. Oracle Retail also stores and uses the GL accounts.
In Oracle Financials, the chart of accounts implies all the accounting segments and values such as chart of accounts, business unit, and setID. A chart of accounts includes account, department, operating unit, and other segments. The attributes under each segment can differ from those of other segments. For example, Account can have Account Number, Description, Account Type, and VAT Flag, and Department can have Department number, Description, and Manager.
Chart of accounts is manually synchronized from Oracle Payables to Oracle Retail. It is not synchronized automatically.
The chart of accounts is validated based on the currently chart of account. The most current active Chart of Account value row needs to be manually synchronized from Oracle GL to Oracle Retail.
The Oracle Financials system can also create future dated chart of accounts. These future dated charts of accounts need to be re-validated against Oracle Financials system. Also, chart of accounts can be end-dated or disable. The chart of account will need to be manually re-validated.
After the chart of account segment mappings have been set up in both systems, a validation occurs for each Oracle Retail transaction ready to be transmitted to Oracle Financials system
The calendars are manually maintained in Oracle Retail and Oracle Financials (EBS or PeopleSoft) Suite; they are not automatically synchronized.
Cross-references maps and connects the records within the application network, and they enable these applications to communicate in the same language. The RFI application stores the relationship between applications in a persistent way so that others can refer to it. The following table lists the Oracle Retail to Oracle Financials RFI cross-references:
Table 6-2 Oracle Retail to Oracle Financials RFI cross-references
| Cross Reference Table - RFI_XREF_DVMColumn: Category_Type | Retail Column Name RETL_ID | EBS Column Name - EXT_SYSTEM_ID | Description | Usage |
|---|---|---|---|---|
|
PAYMENT_TERMS_XREF (manual sync for EBS and RFI integration adapter sync for PeopleSoft) |
Payment Term Value in Retail |
Payment Term Value in EBS or PeopleSoft |
Payment Term Id |
Manual Sync for EBS & Integration Adapter sync for PeopleSoft |
|
FREIGHT_TERMS_XREF (manual sync) |
Freight Term Value in Retail |
Freight Term Value in EBS or PeopleSoft |
Freight Term Id |
Manual Sync |
|
ORG_ID_XREF |
ORG_UNIT_ID in Retail |
ORGANIZATION_ID value in EBS Or AP Business Unit in PeopleSoft |
Organization Unit |
Manual Sync. |
|
SUPPLIER_XREF |
Supplier Id in Retail |
Vendor id in Oracle Financials (EBS or PeopleSoft) |
Supplier Id |
Xref value for Retail is Supplier. The value for Oracle Financials will be Vendor ID. Populated during the supplier integration. |
|
SUPPLIER_SITE_XREF |
Supplier Site Id in Retail |
Vendor Site id in Oracle Financials (EBS or PeopleSoft) |
Supplier Site Id |
Xref value for Retail is Supplier. The value for Oracle Financials will be the VendorSiteID .Populated during the supplier integration. |
DVM's are tables containing mapping between related information in the participating applications. They enable you to equate lookup codes and other static values across applications, for example, FOOT and FT or US and USA. These DVM tables are maintained in the RFI layer. The RFI layer uses these DVM tables in transforming the messages from one system in the expected format of the other system.
DVM types are seeded for the Oracle Retail to Oracle Financials flows, and administrators can extend the list of mapped values by adding more maps. The DVM data should be synchronized with what the participating applications use. This synchronization should occur before any initial loads are run or any incremental transactional flows are initiated.
RFI uses the same table RFI_XREF_DVM to store XREF and DVM information, the differentiator is the CATEGORY_TYPE column.
|
Note: DVM and Xref (Manual Sync) is not pre-seeded, the values have to be entered manually as give in the sections below. |
The following table lists the DVMs for the Oracle Retail to Oracle Financials RFI:
Table 6-3 DVMs for Oracle Retail to Oracle Financials RFI
| DVM Type | DVM Column Name | Comments |
|---|---|---|
|
BUSINESS_UNIT_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps Oracle Retail's Set Of Books ID to Oracle GL business unit (Ledger Id). EBS Table : GL_LEDGERS Retail Table: FIF_GL_SETUP |
|
COA_STATUS_DVM |
RETL_ID, EXT_SYSTEM_ID |
This is a hard coded DVM and should not be changed. This maps the EBS value of "TRUE" or "FALSE" to Oracle Retail's value of "valid" or "invalid". |
|
ADDRESS_COUNTRY_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the country codes between Oracle Retail and Oracle system. |
|
CURR_CONV_TYPE_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the Currency Conversion Types between Oracle Retail and Oracle system. |
|
CURR_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the currency codes between Oracle Retail and Oracle system. |
|
LANGUAGE_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the language code between Oracle Retail and E-Business Suite system. This DVM is used to determine which language to be sent the translatable fields to Oracle Retail. |
|
COA_GLELEMENT_REIM_DVM COA_GLELEMENT_RESA_DVM COA_GLELEMENT_RMS_DVM |
RETL_ID, EXT_SYSTEM_ID |
Segment name mappings between Retail Requesting System and EBS The values are mapped like COA_GLELEMENT_<RequestingSystem>_DVM (fore.g if RMS is the requesting system, COA_GLELEMENT_RMS_DVM is used) lookup has the entry like < Set of Books ID >-SEGMENT1 for Retail value and < ChartOfAccountIdentification>-SEGMENT1 for the E-business suite value. |
|
STATE_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the state codes between Oracle Retail and Oracle Payables. |
|
SUPPLIER_ADDRESS_TYPE_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the supplier's address type between Oracle Retail and Oracle Payables. Order and Remittance Address type 04/06 in Retail to PURCHASING_SITE_FLAG/ PAY_SITE_FLAG in EBS |
|
SUPPLIER_STATUS_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the supplier's status between Oracle Retail and Oracle Payables. "I" for Inactive and "A" for Active status in both Retail and EBS |
|
TAX_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the Tax code (VAT) between Oracle Retail and EBS |
The DVM/XREF table RFI_XREF_DVM has the following table structure:
Table 6-4 RFI_XREF_DVM table structure
| Column Names | Description |
|---|---|
|
CATEGORY_TYPE |
The DVM or XREF name. The below DVM/XREF are Static Values and should be manually entered. The XREF here are manually synchronized. ADDRESS_COUNTRY_DVM BUSINESS_UNIT_DVM COA_GLELEMENT _DVM COA_GLELEMENT_RESA_DVM COA_GLELEMENT_RMS_DVM COA_STATUS_DVM CURR_CODE_DVM CURR_CONV_TYPE_CODE_DVM FREIGHT_TERMS_XREF LANGUAGE_CODE_DVM ORG_ID_XREF PAYMENT_TERMS_XREF RMS_GL_MSG_NOTIFICATION ReIM_AP_MSG_NOTIFICATION ReIM_GL_MSG_NOTIFICATION STATE_DVM SUPPLIER_ADDRESS_TYPE_DVM SUPPLIER_PRIMARY_SITE_FLAG_DVM SUPPLIER_STATUS_CODE_DVM TAX_DVM XREF values inserted by Supplier Integration Adapter SUPPLIER_XREF SUPPLIER_SITE_XREF |
|
CATEGORY_TYPE_DESC |
Description of the DVM or Xref |
|
COMMON_ID |
Common identifier for the record. Make sure the Common Id entered for DVM's are below 25000. The Integration Adapter uses database sequence RFI_XREF_SEQUENCE which starts from 25000. |
|
RETL_ID |
Retail value |
|
EXT_SYSTEM_ID |
EBS or PSFT value |
|
EXT_SYSTEM |
External system name. Hardcode to "EBS" or "PSFT" based on integrating oracle Financial application. |
The RFI console now includes a new page "Domain Data Cross Reference" for viewing, searching, browsing the Cross References (XREF) and DVM records setup in RFI.
When user navigates to the Domain Data Cross Reference page, it displays the list of all XREF and DVM records in RFI. User can search for specific records based on a field value using the Search By form in the page. In order to search for records, select corresponding field name in the 'Search By' list based on which the search needs to done. And enter the value to search for in the adjacent text box.
The search will query and display records that match the search string entered. The search is also case-insensitive and has wild card searches.
Example: select Search By 'Category Type' and enter 'dvm' (without quotes) in the search text box, and click 'Search', the page will display all the records with Category Type containing 'dvm' string, say, records with Category Type such as, 'COA_STATUS_DVM', 'SUPPLIER_ADDRESS_TYPE_DVM', 'CURR_CODE_DVM', 'DVM_STATE' , 'RFI_DVM_FOR_BUSINESS_UNIT' and so on.
Clicking 'Refresh' button in the page will refresh the page with list of all records. The page will also highlight the record count statistics i.e., 'No. Of Records' fetched / displayed.
In cases when the search string is not found or in the DVM table with matching criteria, error message is displayed indicating no record matching i.e 'No data found for the specified search criteria'.
This section discusses how you can handle errors in the application.
The errors are logged in CurrencyRate.log, Supplier.log, GLAccount.log log files respectively. These log files are created in the Application Server where RFI application is deployed.
The logs can be viewed from the RFI Adapter Manager Screens. The RFI Adapter Manager can be accessed using the URL:
http://<Application Server Name>:<Port>/<RFI Application Deployed Name>
The following information is available in the logs
DVM/XREF mapping not available
Any error returned by Web service
Any RFI application errors
The errors are logged in PaymentTerms.log log file. This log files is created in the Application Server where RFI application is deployed.
ODI interfaces log the messages in RFI_ODI_LOGS table in RFI Schema available in RFI database. If there are any transformation errors due to DVM/XREF, then the Retail Staging tables are updated with the following values and the number of records with transformation error is logged in the RFI_ODI_LOGS table.
Table 6-5 Transformation errors updated in Retail Staging tables
| Retail Staging Table | Column | Value |
|---|---|---|
|
STG_FIF_GL_DATA |
STATUS |
TRANSFORM_ERROR |
|
IM_FINANCIALS_STAGE |
ODI_SESSION_NUM |
-2 |
|
IM_AP_STAGE_HEAD |
ODI_SESSION_NUM |
-2 |
|
IM_AP_STAGE_DETAIL |
ODI_SESSION_NUM |
-2 |
Any other ODI errors during execution are logged in the RFI_ODI_LOGS table.
If there are any interface errors or transformation error records, an error notification is sent from RFI Adapter Manager to the To Email Address list specified during RFI deployment. The adapter logs can be viewed from the RFI Adapter Manager Screens. The RFI Adapter Manager screens can be accessed using the URL:
http://<Application Server Name>:<Port>/<RFI Application Deployed Name>
RFI provides an Administration UI where the Integration adapter timers could be started/stopped and also the logs can be viewed for the individual adapters. The RFI Administration user interface can be accessed using the URL:
http://<Application Server Name>:<Port>/<RFI Application Deployed Name>
Schedulers are created for the RFI Integration Adapters at the time of RFI Application deployment. The following adapter schedule can be updated by changing values of below mentioned properties configured at the time of deployment:
Currency Rate
scheduler.currencyRate.timer.delyToStart
Start time in minutes from the time of deployment or restart of Application Server.
scheduler.currencyRate.timer.interval
Interval, the time between the initial run to the next run in minutes
Similarly, for:
Supplier
scheduler.supplier.timer.delyToStart
scheduler.supplier.timer.interval
ODI Error Mail Notification
scheduler.mail.timer.delyToStart
scheduler.mail.timer.interval
Payment Terms - For PeopleSoft integration only
scheduler.paymentTerms.timer.delyToStart
scheduler.paymentTerms.timer.interval
The adapters can be stopped/started from the RFI Adapter Manager Screens Logs. The errors are logged in CurrencyRate.log, Supplier.log, GLAccount.log log ,PaymentTerms.log(only for PeopleSoft integration) files respectively. These log files are created in the Application Server where the RFI application is deployed. The logs can be viewed from the RFI Adapter Manager Screens. The RFI Adapter Manager can be accessed using the URL:
http://<Application Server Name>:<Port>/<RFI Application Deployed Name>
ODI interfaces logs the messages in RFI_ODI_LOGS table in RFI Schema available in Retail Database. If there are any transformation errors due to DVM/XREF, then the Retail Staging tables are updated with the following values:
Table 6-6 Transformation errors updated in Retail Staging tables
| Retail Staging Table | Column | Value |
|---|---|---|
|
STG_FIF_GL_DATA |
STATUS |
TRANSFORM_ERROR |
|
IM_FINANCIALS_STAGE |
ODI_SESSION_NUM |
-2 |
|
IM_AP_STAGE_HEAD |
ODI_SESSION_NUM |
-2 |
|
IM_AP_STAGE_DETAIL |
ODI_SESSION_NUM |
-2 |
The logs can be viewed from the RFI Adapter Manager Screens. The RFI Adapter Manager can be accessed using the URL:
http://<Application Server Name>:<Port>/<RFI Application Deployed Name>
If there are any interface errors or transformation error records, the Error Notification is sent from RFI Adapter Manager to the To Email Address list specified during RFI deployment.
