| Oracle® Retail Financial Integration Cloud Service for Oracle Retail Merchandise Operations Management and Oracle Financials Oracle® Retail Financial Integration Cloud Service for Oracle Retail Merchandise Operations Management and Oracle Financials Release 19.1.000 F31808-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 19.1.000, RMS Operations Guide, version 19.1.000 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, PeopleSoft, and CFIN 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 or CFIN 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 or CFIN) |
|
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 application |
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 or CFIN |
Payment Term Id |
Manual Sync for EBS & CFIN Integration Adapter sync for PeopleSoft |
|
FREIGHT_TERMS_XREF (manual sync) |
Freight Term Value in Retail |
Freight Term Value in EBS or PeopleSoft or CFIN |
Freight Term Id |
Manual Sync |
|
ORG_ID_XREF |
ORG_UNIT_ID in Retail |
ORGANIZATION_ID value in EBS Or AP Business Unit in PeopleSoft or CFIN |
Organization Unit |
Manual Sync. |
|
SUPPLIER_XREF |
Supplier Id in Retail |
Vendor id in Oracle Financials (EBS or PeopleSoft or CFIN) |
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 or CFIN) |
Supplier Site Id |
Xref value for Retail is Supplier Site id. The value for Oracle Financials will be the VendorSiteID .Populated during the supplier integration. |
|
SUPPLIER_SITE_NAME_XREF |
Supplier Site Id in Retail |
Supplier Site id in retail plus the supplier site description (common for retail and cfin) |
Supplier Site name |
Xref value for Retail is Supplier site id. The value for EXT_ID will be the SupplierSiteID SupplierSiteName .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 | RETL_ID (example) | EXT_SYSTEM_ID (example) | EXT System |
|---|---|---|---|---|---|
|
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 and CFIN Table: GL_LEDGERS Retail Table: FIF_GL_SETUP |
111 |
abc |
Applicable for all systems (EBS/PSFT/CFIN) |
|
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". |
True, False, |
TRUE/FALSE for EBS or PSFT, Valid/Invalid for CFIN |
Applicable for all systems (EBS/PSFT/CFIN) |
|
ADDRESS_COUNTRY_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the supplier address country code value mapping between RMS and financial system. |
US |
US |
Applicable for all systems (EBS/PSFT/CFIN) |
|
CURR_CONV_TYPE_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
RMS has some set exchange rate type values (Operational, Consolidated, etc.) and CFIN and other financials systems also have exchange rate types (and these may be configurable). A Common exchange rate type that would be used in Financials would be "Spot" and this would correspond to Operational in RMS. So, a DVM between these two values should exist. For ReIM, when we send documents to AP we send an exchange rate when needed, but the rate type we send is always "USER" to ensure that CFIN doesn't try to validate that it is the correct rate for a given date. So we also need to have DVM for "User" rate types. |
A,User |
CRRNT, User |
Applicable for all systems (EBS/PSFT/CFIN) |
|
CURR_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the currency code value mapping between RMS and financial system. |
USD |
USD |
Applicable for all systems (EBS/PSFT/CFIN) |
|
LANGUAGE_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the supplier language code value mapping between RMS and financial system. |
EN |
EN |
Applicable for all systems (EBS/PSFT/CFIN) |
|
COA_GLELEMENT_REIM_DVM |
RETL_ID, EXT_SYSTEM_ID |
Segment name mappings between REIM and EBS or CFIN. The value lookup has the entry like < Set of Books ID >-SEGMENT1 for Retail value and < Ledger id>-SEGMENT1 for the E-business suite or CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for all systems (EBS/PSFT/CFIN) |
|
COA_GLELEMENT_RESA_DVM |
Segment name mappings between RESA and EBS or CFIN. The value lookup has the entry like < Set of Books ID >-SEGMENT1 for Retail value and < Ledger id>-SEGMENT1 for the E-business suite or CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for all systems (EBS/PSFT/CFIN) |
|
|
COA_GLELEMENT_RMS_DVM |
Segment name mappings between RMS and EBS or CFIN. The value lookup has the entry like < Set of Books ID >-SEGMENT1 for Retail value and < Ledger id>-SEGMENT1 for the E-business suite or CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for all systems (EBS/PSFT/CFIN) |
|
|
STATE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the supplier address State code value mapping between RMS and financial system. |
MN |
MN |
Applicable for all systems (EBS/PSFT/CFIN) |
|
SUPPLIER_ADDRESS_TYPE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the supplier Site type value (Pay/Purchase) mapping between RMS and financial system. RFI sends only two types of address to RMS(Pay/Purchase). RMS adds 01 type of supplier address in each site by default. Primary supplier in RMS takes the types from first site it processes. |
04,06 |
PURCHASING_SITE_FLAG. PAY_SITE_FLAG |
Applicable for all systems (EBS/PSFT/CFIN) |
|
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 or CFIN. |
I,A |
I,A |
Applicable for all systems (EBS/PSFT/CFIN) |
|
TAX_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the Tax code (VAT) between Oracle Retail and EBS or CFIN |
S |
S |
Applicable for all systems (EBS/PSFT/CFIN) |
|
SUPPLIER_PRIMARY_SITE_FLAG_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the Primary site flag value mapping, If site coming from finacial system is primary then same information is passed to RMS wuith this flag value. |
Y |
Y |
Applicable for all systems (EBS/PSFT/CFIN) |
|
FREIGHT_TERMS_XREF |
RETL_ID, EXT_SYSTEM_ID |
Holds Freight terms mapping. |
1 |
ORIGIN |
Applicable for all systems (EBS/PSFT/CFIN) |
|
PAYMENT_TERMS_XREF |
RETL_ID, EXT_SYSTEM_ID |
Manual Sync for EBS and Inserted by Pay terms integration adapter for PeopleSoft. |
111 |
xyz corporation |
Applicable for all systems (EBS/PSFT/CFIN) |
|
ORG_ID_XREF |
111 |
123 |
Applicable for all systems (EBS/PSFT/CFIN) |
||
|
SUPPLIER_XREF |
400 |
445566 |
Applicable for all systems (EBS/PSFT/CFIN) |
||
|
SUPPLIER_SITE_NAME_XREF |
401 |
401 suppliner site name |
Applicable for all systems (EBS/PSFT/CFIN) |
||
|
VENDOR_FILTER_XREF |
Supplier Filter Criteria |
'VENDOR_TYPE_LOOKUP_CODE, Any column from rfi_Supp_stage like attributes column |
TAX AUTHORITY, Any other value under selected colun length limit |
Applicable for all systems (EBS/PSFT/CFIN) |
|
|
COA_GLELEMENT_LEDGER_GROUP_DVM |
11111 |
US001-RECORDING |
Applicable for all systems (PSFT) |
||
|
TSF_ENTITY_XREF |
111 |
abc |
Applicable for all systems (EBS/PSFT/CFIN) |
||
|
PROC_ORG_UNIT_TO_FIN_ORG_UNIT_DVM |
111 |
abc |
Applicable for all systems (EBS/PSFT/CFIN) |
||
|
SOB_ID_TO_LEDGER_ID_DVM |
111 |
1 |
Applicable for all systems (EBS/PSFT/CFIN) |
||
|
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 and CFIN Table: GL_LEDGERS Retail Table: FIF_GL_SETUP |
111 |
abc |
Applicable for all systems (EBS/PSFT/CFIN) |
|
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". |
True, False, |
TRUE/FALSE for EBS or PSFT, Valid/Invalid for CFIN |
Applicable for all systems (EBS/PSFT/CFIN) |
|
ADDRESS_COUNTRY_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the supplier address country code value mapping between RMS and financial system. |
US |
US |
Applicable for all systems (EBS/PSFT/CFIN) |
|
CURR_CONV_TYPE_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
RMS has some set exchange rate type values (Operational, Consolidated, etc.) and CFIN and other financials systems also have exchange rate types (and these may be configurable). A Common exchange rate type that would be used in Financials would be "Spot" and this would correspond to Operational in RMS. So, a DVM between these two values should exist. For ReIM, when we send documents to AP we send an exchange rate when needed, but the rate type we send is always "USER" to ensure that CFIN doesn't try to validate that it is the correct rate for a given date. So we also need to have DVM for "User" rate types. |
A,User |
CRRNT, User |
Applicable for all systems (EBS/PSFT/CFIN) |
|
CURR_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the currency code value mapping between RMS and financial system. |
USD |
USD |
Applicable for all systems (EBS/PSFT/CFIN) |
|
LANGUAGE_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the supplier language code value mapping between RMS and financial system. |
EN |
EN |
Applicable for all systems (EBS/PSFT/CFIN) |
|
COA_GLELEMENT_REIM_DVM |
RETL_ID, EXT_SYSTEM_ID |
Segment name mappings between REIM and EBS or CFIN. The value lookup has the entry like < Set of Books ID >-SEGMENT1 for Retail value and < Ledger id>-SEGMENT1 for the E-business suite or CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for all systems (EBS/PSFT/CFIN) |
|
COA_GLELEMENT_RESA_DVM |
Segment name mappings between RESA and EBS or CFIN. The value lookup has the entry like < Set of Books ID >-SEGMENT1 for Retail value and < Ledger id>-SEGMENT1 for the E-business suite or CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for all systems (EBS/PSFT/CFIN) |
|
|
COA_GLELEMENT_RMS_DVM |
Segment name mappings between RMS and EBS or CFIN. The value lookup has the entry like < Set of Books ID >-SEGMENT1 for Retail value and < Ledger id>-SEGMENT1 for the E-business suite or CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for all systems (EBS/PSFT/CFIN) |
|
|
STATE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the supplier address State code value mapping between RMS and financial system. |
MN |
MN |
Applicable for all systems (EBS/PSFT/CFIN) |
|
SUPPLIER_ADDRESS_TYPE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the supplier Site type value (Pay/Purchase) mapping between RMS and financial system. RFI sends only two types of address to RMS(Pay/Purchase). RMS adds 01 type of supplier address in each site by default. Primary supplier in RMS takes the types from first site it processes. |
04, 06 |
PURCHASING_SITE_FLAG. PAY_SITE_FLAG |
Applicable for all systems (EBS/PSFT/CFIN) |
|
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 or CFIN. |
I, A |
I, A |
Applicable for all systems (EBS/PSFT/CFIN) |
|
TAX_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the Tax code (VAT) between Oracle Retail and EBS or CFIN |
S |
S |
Applicable for all systems (EBS/PSFT/CFIN) |
|
SUPPLIER_PRIMARY_SITE_FLAG_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the Primary site flag value mapping, If site coming from finacial system is primary then same information is passed to RMS wuith this flag value. |
Y |
Y |
Applicable for all systems (EBS/PSFT/CFIN) |
|
FREIGHT_TERMS_XREF |
RETL_ID, EXT_SYSTEM_ID |
Holds Freight terms mapping. |
1 |
ORIGIN |
Applicable for all systems (EBS/PSFT/CFIN) |
|
PAYMENT_TERMS_XREF |
RETL_ID, EXT_SYSTEM_ID |
Manual Sync for EBS and Inserted by Pay terms integration adapter for PeopleSoft. |
111 |
xyz corporation |
Applicable for all systems (EBS/PSFT/CFIN) |
|
ORG_ID_XREF |
RETL_ID, EXT_SYSTEM_ID |
Holds Org Unit mapping between retail and financial system |
111 |
123 |
Applicable for all systems (EBS/PSFT/CFIN) |
|
SUPPLIER_XREF |
RETL_ID, EXT_SYSTEM_ID |
Holds supplier id mapping between retail and financial system, This xref gets automatically created when new supplier is passed from financial system. If this xref is already present then supplier will be considered for update else it will be considered as new. |
400 |
445566 |
Applicable for all systems (EBS/PSFT/CFIN) |
|
SUPPLIER_SITE_XREF |
RETL_ID, EXT_SYSTEM_ID |
Holds supplier site id mapping between retail and financial system, This xref gets automatically created when new supplier site is passed along with supplier info from financial system. If this xref is already present then supplier site will be considered for update else it will be considered as new. |
401 |
445577 |
Applicable for all systems (EBS/PSFT/CFIN) |
|
SUPPLIER_SITE_NAME_XREF |
RETL_ID, EXT_SYSTEM_ID |
Holds supplier site id with supplier site name mapping between retail and financial system, This xref gets automatically created when new supplier site is passed along with supplier info from financial system. If this xref is already present then supplier site will be considered for update else it will be considered as new. This Xref is used in CFIN Invoice interface to pass the site code in outgoing file. |
401 |
401 suppliner site name |
Applicable for all systems (EBS/PSFT/CFIN) |
|
VENDOR_FILTER_XREF |
RETL_ID, EXT_SYSTEM_ID |
Supplier Filter Criteria |
'VENDOR_TYPE_LOOKUP_CODE, Any column from rfi_Supp_stage like attributes column |
TAX AUTHORITY, Any other value under selected colun length limit |
Applicable for all systems (EBS/PSFT/CFIN) |
|
COA_GLELEMENT_LEDGER_GROUP_DVM |
RETL_ID, EXT_SYSTEM_ID |
Ledger Group mapping with Business Unit. Used only when PeopleSoft is the Oracle Financials application |
11111 |
US001-RECORDING |
Applicable for all systems (PSFT) |
|
TSF_ENTITY_XREF |
RETL_ID, EXT_SYSTEM_ID |
Transfer entity to legal entity. Maps RMS TSF_ENTITY_ID value from TSF_ENTITY table. |
111 |
abc |
Applicable for all systems (EBS/PSFT/CFIN) |
|
PROC_ORG_UNIT_TO_FIN_ORG_UNIT_DVM |
RETL_ID, EXT_SYSTEM_ID |
Proc org unit to financial org unit. Maps RMS ORG_UNIT_ID value from ORG_UNIT table. |
111 |
abc |
Applicable for all systems (EBS/PSFT/CFIN) |
|
SOB_ID_TO_LEDGER_ID_DVM |
RETL_ID, EXT_SYSTEM_ID |
Maps the set of books id to ledger id |
111 |
1 |
Applicable for all systems (EBS/PSFT/CFIN) |
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 or CFIN value |
|
EXT_SYSTEM |
External system name. Hardcode to "EBS" or "PSFT" based on integrating oracle Financial application. |
The RFI UI includes a page "Manage DVM" for viewing, searching, browsing and editing the Cross References (XREF) and DVM records setup in RFI.
When user navigates to the Manage DVM 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'.
User can also Update, Delete or Add the DVM/xref data using this screen. User needs to select the record by clicking on row level checkbox in order to make any modification to any record.
By default the supplier related xrefs (SUPPLIER_XREF, SUPPLIER_SITE_XREF, SUPPLIER SITE_NAME_XREF) will not be loaded in initial load of page as well as on search to improve the performance of search. A checkbox is present to enforce the supplier related xref inclusion in search. This check box will auto unchecked after page gets refreshed.
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>
Whenever an error occurs during a Transform or a Dump process due to a missing DVM/XREF, missing system options or a case of an invoice detail with no invoice header associated, last one will only occur for the RFI Invoice Entry Interface, the specific status table of that interface will be updated.
Table 6-6 Status Table of each RFI Interface
| RFI Interface | Status Table |
|---|---|
|
RMS Account Entry |
RFI_CFIN_RMS_ACNT_ENTRY_STATUS |
|
ReIM Account Entry |
RFI_CFIN_REIM_ACNT_ENT_STATUS |
|
ReIM Invoice Entry |
RFI_CFIN_AP_INV_INT_STATUS |
As shown in the table above, each interface has its own Status table to monitor how the process is running. Because of that the troubleshooting process can differ from entity to entity.
Whenever an error occurs during the Transform or Dump process the values for the "STATUS" column on the "RFI_CFIN_RMS_ACNT_ENTRY_STATUS" table are updated with the values showed in the table below. In order to make it easier to troubleshoot the problem that lead to the error, the error message (e.g "Missing XPTO_DVM cross reference.") is added to the column "ERROR_MESSAGE" present in the same table.
Table 6-7 Status Value for each Process
| Process | Table | Column | Value |
|---|---|---|---|
|
Transform |
RFI_CFIN_RMS_ACNT_ENTRY_STATUS |
STATUS |
TRANSFORM_ERROR |
|
Dump |
RFI_CFIN_RMS_ACNT_ENTRY_STATUS |
STATUS |
DUMP_ERROR |
After the issue that caused the records to fail is resolved, it's possible to re-process those records. In order to make this possible, is necessary to manually update the value of the column "STATUS" on the "RFI_CFIN_RMS_ACNT_ENTRY_STATUS" to "TO_RETRANSFORM" for all the records that had previously failed and are now solved, this status indicates that the records are ready to be re-processed in the next execution.
The ReIM Account Entry interface follow the same logic as RMS Account Entry interface to fix and re-process the records for the Transform and Dump execution. The only difference in this case is the status table. For this entity, the values for the "STATUS" column are updated on the "RFI_CFIN_REIM_ACNT_ENT_STATUS" table.
The ReIM Invoice Entry interface follows the same steps already described above for the ReIM Account Entry and the RMS Account Entry interfaces. In this case, the values for the "STATUS" column needed to be updated on the "RFI_CFIN_AP_INV_INT_STATUS" table.
Table 6-9 Status Value for each Process
| Process | Table | Column | Value |
|---|---|---|---|
|
Transform |
RFI_CFIN_AP_INV_INT_STATUS |
STATUS |
TRANSFORM_ERROR |
|
Dump |
RFI_CFIN_AP_INV_INT_STATUS |
STATUS |
DUMP_ERROR |
|
Dump |
RFI_CFIN_AP_INV_INT_STATUS |
STATUS |
DETAIL_WITH_NO_HEAD_ERROR |
Unlike the two previous interfaces, that only have one possible error status for the Transform process and other for the Dump process, the ReIM Invoice Entry interface has two status errors that can occur on a Dump process. This happens because of the head detail structure, in some situations, we can have invoices with no header leading to the "DETAIL_WITH_NO_HEAD_ERROR" error status.
The table below describe all the log files that are used to better troubleshoot and identify possible errors, all these can be viewed from the RFI Adapter Manager Screens.
Table 6-10 Log files written by RFI
| Log Name | Log Description |
|---|---|
|
CfinTransformInvoiceEntry.log |
Keeps track of the all the Transform and Dump processes for the ReIM Invoice Entry. |
|
CfinTransformReimAccountEntry.log |
Keeps track of the all the Transform and Dump processes for the ReIM Account Entry. |
|
CfinTransformRmsAccountEntry.log |
Keeps track of the all the Transform and Dump processes for the RMS Account Entry. |
|
InvoiceEntry.log |
Keeps track of the actions that are made when uploading an ReIM Invoice Entry to the Financial System. |
|
RMSAccountEntry.log |
Keeps track of the actions that are made when uploading an RMS Account Entry to the Financial System. |
|
REIMAccountEntry.log |
Keeps track of the actions that are made when uploading an ReIM Account Entry to the Financial System. |
|
ErrorNotification.log |
Keeps track of the actions that are needed to be made when sending the error notifications. |
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-11 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.
