6 Implementing Oracle Retail Merchandise Integration Pack for Oracle Financials (CFIN Financials)
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.
Setting Up Oracle Retail Applications
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 Cloud Service Operations Guide, Retail Merchandising Foundation guides, for setting up the Oracle Retail applications.
Setting Up Oracle General Ledger (GL)
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 Fusion Financial Guide.
Setting Up Oracle Payables
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 Fusion Financial Guide.
RFI Database Schema Topology
The diagrams below illustrates the Database schema topology for CFIN integration:-
Figure 6-1 RFI Database Schema Topology for Cloud Financials Integration
Setting Up Cross-References for Oracle Retail IDs and Oracle Entities
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.
Setting Up Oracle Domain Value Maps
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 |
Synchronizing Freight Terms Manually
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.
Synchronizing Currency Exchange Rate Types Manually
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 (CFIN).
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 (CFIN) 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 Fusion Finance documentation.
Synchronizing Currency Codes Manually
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 Fusion Finance documentation.
Synchronizing Chart of Accounts Segments Manually
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
Synchronizing Financial Calendar Manually
The calendars are manually maintained in Oracle Retail and Oracle Financials (CFIN) Suite; they are not automatically synchronized.
Working with Cross-References
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) |
Payment Term Value in Retail |
Payment Term Value in CFIN |
Payment Term Id |
Manual Sync for CFIN Integration |
FREIGHT_TERMS_XREF (manual sync) |
Freight Term Value in Retail |
Freight Term Value in CFIN |
Freight Term Id |
Manual Sync |
ORG_ID_XREF |
ORG_UNIT_ID in Retail |
ORGANIZATION_ID value in CFIN |
Organization Unit |
Manual Sync. |
SUPPLIER_XREF |
Supplier Id in Retail |
Vendor id in Oracle Financials (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 (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. |
Working with Domain Value Maps (DVM)
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 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, |
Valid/Invalid for CFIN |
Applicable for 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 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 CFIN |
CURR_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the currency code value mapping between RMS and financial system. |
USD |
USD |
Applicable for 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 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 CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for 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 CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for 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 CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for 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 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 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 CFIN. |
I,A |
I,A |
Applicable for CFIN |
TAX_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the Tax code (VAT) between Oracle Retail and CFIN |
S |
S |
Applicable for 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 CFIN |
FREIGHT_TERMS_XREF |
RETL_ID, EXT_SYSTEM_ID |
Holds Freight terms mapping. |
1 |
ORIGIN |
Applicable for CFIN |
PAYMENT_TERMS_XREF |
RETL_ID, EXT_SYSTEM_ID |
Manual Sync |
111 |
xyz corporation |
Applicable for CFIN |
ORG_ID_XREF |
111 |
123 |
Applicable for CFIN |
||
SUPPLIER_XREF |
400 |
445566 |
Applicable for CFIN |
||
SUPPLIER_SITE_NAME_XREF |
401 |
401 suppliner site name |
Applicable for 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 CFIN |
|
COA_GLELEMENT_LEDGER_GROUP_DVM |
11111 |
US001-RECORDING |
Applicable for CFIN |
||
TSF_ENTITY_XREF |
111 |
abc |
Applicable for CFIN |
||
PROC_ORG_UNIT_TO_FIN_ORG_UNIT_DVM |
111 |
abc |
Applicable for CFIN |
||
SOB_ID_TO_LEDGER_ID_DVM |
111 |
1 |
Applicable for 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). CFIN Table: GL_LEDGERS Retail Table: FIF_GL_SETUP |
111 |
abc |
Applicable for 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, |
Valid/Invalid for CFIN |
Applicable for 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 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 CFIN |
CURR_CODE_DVM |
RETL_ID, EXT_SYSTEM_ID |
Holds the currency code value mapping between RMS and financial system. |
USD |
USD |
Applicable for 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 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 CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for 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 CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for 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 CFIN value. |
111-SEGMENT1, 111-SEGMENT2, …111-SEGMENTN. |
123-SEGMENT1, 123-SEGMENT2, …123-SEGMENTN. |
Applicable for 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 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 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 CFIN. |
I, A |
I, A |
Applicable for CFIN |
TAX_DVM |
RETL_ID, EXT_SYSTEM_ID |
This maps the Tax code (VAT) between Oracle Retail and CFIN |
S |
S |
Applicable for 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 with this flag value. |
Y |
Y |
Applicable for CFIN |
FREIGHT_TERMS_XREF |
RETL_ID, EXT_SYSTEM_ID |
Holds Freight terms mapping. |
1 |
ORIGIN |
Applicable for CFIN |
PAYMENT_TERMS_XREF |
RETL_ID, EXT_SYSTEM_ID |
Manual Sync |
111 |
xyz corporation |
Applicable for CFIN |
ORG_ID_XREF |
RETL_ID, EXT_SYSTEM_ID |
Holds Org Unit mapping between retail and financial system |
111 |
123 |
Applicable for 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 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 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 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 CFIN |
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 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 CFIN |
SOB_ID_TO_ LEDGER_ID_ DVM |
RETL_ID, EXT_ SYSTEM_ID |
Maps the set of books id to ledger id |
111 |
abc |
Applicable for 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 |
CFIN value |
EXT_SYSTEM |
External system name. Hardcode to "CFIN". |
The DVM category types of COA_GLELEMENT_RMS_DVM, COA_GLELEMENT_REIM_DVM, COA_GLELEMENT_RESA_DVM need to maintain the increasing sequence order of COMMON_ID for Segments for GL account validation mapping to avoid issues for mapping between the segments between RFI and external system.
Below is an example for COA_GLELEMENT_RMS_DVM data showing the ordering:
Insert into RFI_XREF_DVM (CATEGORY_TYPE,CATEGORY_TYPE_DESC,COMMON_ID,RETL_ID,EXT_SYSTEM_ID,EXT_SYSTEM) values ('COA_GLELEMENT_RMS_DVM','ReIM Segment Deion Mapping&lt; Set of Books ID &gt;-SEGMENT1 for Retail value and &lt; ChartOfAccountIdentification&gt;-SEGMENT1 for CFIN value',25215,'111-SEGMENT1','1-SEGMENT1','CFIN'); Insert into RFI_XREF_DVM (CATEGORY_TYPE,CATEGORY_TYPE_DESC,COMMON_ID,RETL_ID,EXT_SYSTEM_ID,EXT_SYSTEM) values ('COA_GLELEMENT_RMS_DVM','ReIM Segment Deion Mapping&lt; Set of Books ID &gt;-SEGMENT2 for Retail value and &lt; ChartOfAccountIdentification&gt;-SEGMENT2 for CFIN value',25216,'111-SEGMENT2','1-SEGMENT2','CFIN'); Insert into RFI_XREF_DVM (CATEGORY_TYPE,CATEGORY_TYPE_DESC,COMMON_ID,RETL_ID,EXT_SYSTEM_ID,EXT_SYSTEM) values ('COA_GLELEMENT_RMS_DVM','ReIM Segment Deion Mapping&lt; Set of Books ID &gt;-SEGMENT3 for Retail value and &lt; ChartOfAccountIdentification&gt;-SEGMENT3 for CFIN value',25217,'111-SEGMENT3','1-SEGMENT3','CFIN'); Insert into RFI_XREF_DVM (CATEGORY_TYPE,CATEGORY_TYPE_DESC,COMMON_ID,RETL_ID,EXT_SYSTEM_ID,EXT_SYSTEM) values ('COA_GLELEMENT_RMS_DVM','ReIM Segment Deion Mapping&lt; Set of Books ID &gt;-SEGMENT4 for Retail value and &lt; ChartOfAccountIdentification&gt;-SEGMENT4 for CFIN value',25218,'111-SEGMENT4','1-SEGMENT4','CFIN');
Note:
During the transformation process, if the data set has errors due to incorrect or missing DVM's, the records that are marked as TRANSFORM_ERROR are eligible for an automatic re-transformation during the Adapter's next run. This feature is available if the newly added flag, AUTO_RETRANSFORM_IND in RFI_SYSTEM_OPTIONS table, is set to 'Y'. The default value of flag is 'N'. In order for the customer to update the value of this flag in the database, a support ticket is required. There is no UI option available for updating.
RFI - Manage DVM Graphical User Interface
The RFI UI includes a page "Manage DVM" for viewing, searching, browsing and editing the Cross References (XREF) and DVM records setup in RFI.
Figure 6-2 RFI - Manage DVM Graphical User Interface
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.
Handling Errors
This section discusses how you can handle errors in the application.
Supplier Information and GL Account Validation Integration Flows
The errors are logged in Supplier.log and 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
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>
Bulk Adapters Interfaces
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-5 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.
RMS Account Entry
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-6 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.
ReIM Account Entry
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.
Table 6-7 Status Value for each Process
Process | Table | Column | Value |
---|---|---|---|
Transform |
RFI_CFIN_REIM_ACNT_ENT_STATUS |
STATUS |
TRANSFORM_ERROR |
Dump |
RFI_CFIN_REIM_ACNT_ENT_STATUS |
STATUS |
DUMP_ERROR |
ReIM Invoice Entry
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-8 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.
Logs
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-9 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. |
Understanding the RFI Administration User Interface
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>
Scheduling Adapters
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 Supplier.log and GLAccount.log log.) 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.
Note:
RFI Error email notifications are turned off by default. Users can turn it ON anytime by updating the value of DVM 'PUBLISH_EMAIL_NOTIFICATION'.
Follow the steps below to enable error email notification.
-
Login to the RFI application with valid credentials.
-
Navigate to the Manage DVM tab.
-
Search for Category Type 'PUBLISH_EMAIL_NOTIFICATION'.
-
Update the value of the EXT_SYSTEM_ID column to N and save changes.
Figure 6-4 Enable Email Notification
Global Logs
Global Logs for Adapter Timers are logged in RfiGlobal.log file and can be viewed from the RFI Adapter Manager screens.
Figure 6-5 RFI Application Logs Screen
Figure 6-6 RFI Adapter Manager Screen - RSFT/EBS
Figure 6-7 RFI Adapter Manager Screen - CFIN
In the above screens, the option of Payterms Service Adapter would be available only if PeopleSoft is the Oracle Financials system.
Understanding the RFI Manage Configurations Page
RFI provides a Manage Configuration UI screen where the System level and Service level configurations are be updated without requiring support ticket and application bounce. It also provides Diagnostics screen where we can ping the web services consumed by RFI.
Runtime System Configurations
User can update configurations at RFI using various UI options available at RFI web page. But there are possibilities that these configurations may be overwritten while upgrading RFI from one version to other. RFI runtime configuration framework is developed as solution to these problems which preserves configurations updated via UI in database.
The runtime configurations help to preserve the customer specific configurations between the patches. These properties are preserved and restored from DB table RFI_RUNTIME_CONFIG_OVERRIDE and merged into rfi.properties. Any updates to these parameters are persisted in database. These properties are Adapter specific and for new configuration property a support ticket is required.
Below are some of the parameters which can be updated through the UI screens:
-
autoPurgeDelay. – Default 30
-
rfi.mail.to.addr.list
-
Adapter Timers – Interval default 10
Figure 6-8 RFI Manage System Configurations Screen
Service Configurations
The Service configurations UI Screen helps to provision to update the connection details for external application/services URLs. For example, to update hostname and port. Also, for provisioning updating of the security details for external application/services. For example, to update username and password for an alias of other Integrated RFI services like RMS, CFIN. This is useful during the password rotation scenarios i.e., when the other applications have their passwords rotated.
Figure 6-9 RFI Manage Service Configurations Screen
Figure 6-10 RFI Manage Service Configurations Screen
-
Users will be provided option either update connection details or security details or both. Update of connection and security details will apply to all the services in scope.
-
Updating connection details will update the service/URLs in the UI and the properties file
-
Updating security details will update the credentials in the UI and the corresponding wallet
Diagnostics Configurations
The diagnostics screen is added in RFI under Manage Configurations which will list all the web services consumed by RFI. This ping features checks connectivity from RFI to other Integrated systems. As depicted in the screenshot, the table shows the Service Name, WSDL URL, Alias and Ping button and status for each web service.
Ping All and reset button are also available to ping all listed services and to reset to default ping statuses.
The ping feature internally uses the rfi.properties to get the services list.
-
Supplier service: Supplier service from RMS offers ping operations. RFI supplier service includes method to invoke ping operation for supplier service from RMS.
-
Account Combination service: Account Combination service from CFIN does not offer any ping operations. RFI invokes account combination service offered by CFIN with default values. Successful response (status field of response is set to "invalid" as request has default combination.) from CFIN will confirm connectivity to CFIN from RFI.
-
If deep ping in step #2 is successful, it ensures that even credentials set up in RFI for CFIN web services are valid.
Figure 6-11 RFI Diagnostics Screen
-
Click the Ping button to check connectivity of listed service. Red in the Status column indicates an error in connectivity. Green in the Status column indicates successful connectivity.
-
Click the Ping All button to ping all listed web services. Check the Status column to know status of ping.
-
Click the Reset button to reset previous ping status.