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 19.0 F23583-03 |
|
Previous |
Next |
Problem: Most of the Retail applications (RMS/ReIM) are on cloud but EBS is on premises. It utilizes a hybrid cloud model for the integration to happen. There is a limitation in hybrid cloud integration. ODI has issues interacting with remote database tables using JDBC.
Solution: Oracle Retail Bulk Data Integration (BDI) is able to move the data from one end (source) to the other end (destination) using REST service calls. This moves the retail schema data from the cloud to the on-premises retail proxy schema where ODI looks for further data processing. BDI implementation has to take place at the retail application side (RMS, REIM and retail integration application, RFI).
The Oracle Retail Financial Integration (RFI) layer serves as an intermediate thin layer of application between the financial application (EBS) and the Oracle Retail applications. This integration remains synchronized with the Edge applications.
The RMS-Edge application (sender side) jobs are used to read the data from the retail schema (RMS) tables and write the data to the outbound tables of the integration schema. The BDI-RMS jobs are used to read data from the outbound tables and write to csv files.
The BDI-RFI application (receiver side) includes uploader and importer jobs. The uploader job moves the data from the csv files to the retail proxy schema inbound tables. Once data is available in the inbound tables, the importer job moves the data from the inbound tables to invoice matching financial tables. After moving the data to the retail proxy schema, ODI scripts transfer the data to DVM and EBS tables.
RMS-Edge Application
Embedded jobs: FinGenLdgr_Tx_ExtractorJob
DB Procedures: FIN_GEN_LDGR_UP
DB Package: BDI_FINANCIAL_SQL
This procedure is written by the RMS application to populate the bdi *_OUT tables present in the BDI integration schema using the BDI Extractor API. The Edge application schema should have write permission to the BDI integration schema
The FIN_GEN_LDGR_UP procedure reads data from the Stg_Fif_Gl_Data table and writes to the Fif_Gl_Data_Out table.
Embedded jobs: InvcMtchAp_Tx_ExtractorJob, InvcMtchFin_Tx_ExtractorJob
DB Procedures: INVC_MTCH_AP_STAGE_UP, INVC_MTCH_FIN_STAGE_UP
DB Package: REIM_BDI_SQL
These procedures are written by the RMS application to populate the bdi *_OUT tables present in the BDI integration schema using the BDI Extractor API. The Edge application user should have write permission to the BDI integration schema
The INVC_MTCH_AP_STAGE_UP procedure reads data from the Im_Ap_Stage_Head and Im_Ap_Stage_Detail tables and writes to the Im_Ap_Head_Out and Im_Ap_Detail_Out tables respectively.
The INVC_MTCH_FIN_STAGE_UP procedure reads data from Ithe m_Financials_Stage table and writes to the Im_Financials_Out table.
Embedded jobs: FinGenLdgr_Tx_DownloaderAndTransporterToRfiJob
The above BDI job reads data from the Fif_Gl_Data_Out table and writes to a csv file.
Embedded jobs: InvcMtchAp_Tx_DownloaderAndTransporterToRfiJob InvcMtchFin_Tx_DownloaderAndTransporterToRfiJob
The InvcMtchAp_Tx_DownloaderAndTransporterToRfiJob BDI job reads data from the Im_Ap_Head_Out and Im_Ap_Detail_Out tables and writes to the corresponding csv files.
The InvcMtchFin_Tx_DownloaderAndTransporterToRfiJob BDI job reads data from the Im_Financials_Out table and writes to the corresponding csv file.
BDI-RFI Application
Embedded jobs: InvcMtchAp_Tx_UploaderJob, InvcMtchFin_Tx_UploaderJob, InvcMtchFin_Tx_OdiTransformerJob,FinGenLdgr_Tx_UploaderJob, InvcMtchAp_Tx_ImporterJob, InvcMtchFin_Tx_ ImporterJob, InvcMtchAp_Tx_OdiTransformerJob ,FinGenLdgr_Tx_ImporterJob, FinGenLdgr_Tx_OdiTranformerJob
DB Procedures: RFI_FinGenLdgr_Tx_Import.import_FinGenLdgr_Tx RFI_InvcMtchAp_Tx_Import.import_InvcMtchAp_Tx RFI_InvcMtchFin_Tx_Import.import_InvcMtchFin_Tx
The InvcMtchAp_Tx_UploaderJob BDI job reads data from the csv files and writes to the Im_Ap_Head_In and Im_Ap_Detail_In tables respectively.
The InvcMtchFin_Tx_ UploaderJob BDI job reads data from the csv file and writes to the Im_Financials_In table.
The FinGenLdgr_Tx_UploaderJob BDI job reads data from the csv file and writes to the Fif_Gl_Data_In table.
The InvcMtchFin_Tx_OdiTransformerJob job starts a odi scenario which transfers data from RMS proxy schema to EBS.
The InvcMtchAp_Tx_UploaderJob job starts a odi scenario which transfers data from RMS proxy schema to EBS.
The FinGenLdgr_Tx_UploaderJob job starts a odi scenario which transfers data from RMS proxy schema to EBS.
The RFI_InvcMtchAp_Tx_Import.import_InvcMtchAp_Tx procedure moves the data from Im_Ap_Head_In, Im_Ap_Detail_In tables to Im_Ap_Stage_Head and Im_Ap_Stage_Detail respectively.
The RFI_InvcMtchFin_Tx_Import.import_InvcMtchFin_Tx procedure moves the data from the Im_Financials_In table to Im_Financials_Stage.
The RFI_FinGenLdgr_Tx_Import.import_FinGenLdgr_Tx procedure moves the data from the Fif_Gl_Data_In table to Stg_Fif_Gl_Data.
Purge Process: After successful execution of the ODI scripts, the already processed records in the transaction tables present in the BDI RFI INT schema should be cleared. The execution of purge logic is based on the respective column values present in the transaction tables given below.
Table 7-2 Transaction Table Values
Table | Column | Value |
---|---|---|
STG_FIF_GL_DATA |
PROCESSED_FLAG |
Y |
IM_AP_STAGE_HEAD |
ODI_SESSION_NUM |
-1 |
IM_AP_STAGE_DETAIL |
ODI_SESSION_NUM |
-1 |
IM_FINANCIALS_STAGE |
ODI_SESSION_NUM |
-1 |
After each successful process flow execution, the import process purges the data which satisfies the above condition.
BDI-Processflow Application
There are three different process flows for this integration.
InvcMtchAp_Tx_ProcessFlow_From_RMS.flo: Launching this process moves data from the Im_Ap_Stage_Head and Im_Ap_Stage_Detail tables of the RMS schema (cloud side) to the same tables present in the RMS proxy schema (on-premises). InvcMtchAp_Tx_UploaderJob post importer activity transfers from RMS proxy schema to EBS schema.
InvcMtchFin_Tx_ProcessFlow_From_RMS.flo: Launching this process moves data from the Im_Financials_Stage table of the RMS schema (cloud side) to the same table present in the RMS proxy schema (on-premises). InvcMtchFin_Tx _UploaderJob post importer activity transfers from RMS proxy schema to EBS schema.
FinGenLdgr_Tx_ProcessFlow_From_RMS.flo: Launching this process moves data from Stg_Fif_Gl_Data table of the RMS schema (cloud side) to the same table present in the RMS proxy schema (on-premises). FinGenLdgr_Tx _UploaderJob post importer activity transfers from RMS proxy schema to EBS schema.
Note: The Rms-Edge-App and Bdi-Processflow-App will be deployed in a cloud environment. |
BDI-Scheduler Application
The application can be deployed in any domain but it is recommended that you deploy it in the BDI domain with a separate managed server.
Below are the schedules which will trigger the respective process flows at scheduled interval of time.
InvcMtchAp_Tx_From_REIM_Schedule_Action.sch -> InvcMtchAp_Tx_ProcessFlow_From_REIM.flo
InvcMtchFin_Tx_From_REIM_Schedule_Action.sch -> InvcMtchFin_Tx_ProcessFlow_From_REIM.flo
FinGenLdgr_Tx_From_RMS_Schedule_Action.sch -> FinGenLdgr_Tx_ProcessFlow_From_RMS.flo
Implementation Details: Three different BDI process flows as follows:
rms_rfi-no_split_flows
reim_rfi-no_split_flows
Table and Interface Details
Table 7-3 Table and Interface Details
App Name | Table Name | Interface Nam,e | Interface Short Name | BDI Out Table Name | BDI In Table Name |
---|---|---|---|---|---|
RMS |
Im_Financials_Stage |
InvcMtchFin_Tx |
Im_Financials |
Im_Financials_Out |
Im_Financials_In |
RMS |
Im_Ap_Stage_Head |
InvcMtchAp_Tx |
Im_Ap_Head |
Im_Ap_Head_Out |
Im_Ap_Head_In |
RMS |
Im_Ap_Stage_Detail |
InvcMtchAp_Tx |
Im_Ap_Detail |
Im_Ap_Detail_Out |
Im_Ap_Detail_In |
RMS |
Stg_Fif_Gl_Data |
FinGenLdgr_Tx |
Fif_Gl_Data |
Fif_Gl_Data_Out |
Fif_Gl_Data_In |
Process Flow and Job Details
Table 7-4 Process Flow and Job Details
Process Flow Name | Extractor Job -> App Name | Downloader Transporter Job -> App Name | Uploader Job -> App Name | Importer Job -> App Name |
---|---|---|---|---|
InvcMtchFin_Tx _ProcessFlow_ From_RMS.flo |
InvcMtchFin_Tx_ ExtractorJob.xml -----> Bdi-Edge-App-RMS |
InvcMtchFin_Tx_ DownloaderAnd TransporterToRfi Job.xml ----> Edge-App-ReIM |
InvcMtchFin_Tx_ UploaderJob.xml ----> bdi-edge-app-rfi |
InvcMtchFin_Tx_ ImporterJob.xml ----> bdi-edge-app-rfi |
InvcMtchAp_Tx _ProcessFlow_ From_RMS.flo |
InvcMtchAp_Tx_ ExtractorJob.xml -----> Bdi-Edge-App-RMS |
InvcMtchAp_Tx_ DownloaderAnd TransporterToRfi Job.xml ----> Edge-App-ReIM |
InvcMtchAp_Tx_ UploaderJob.xml ----> bdi-edge-app-rfi |
InvcMtchAp_Tx_ ImporterJob.xml ----> bdi-edge-app-rfi |
FinGenLdgr_Tx _ProcessFlow_ From_RMS.flo |
FinGenLdgr_Tx_ ExtractorJob.xml -----> Bdi-Edge-App-Rms |
FinGenLdgr_Tx _DownloaderAnd TransporterToRfi Job.xml ----> Edge-App-Rms |
FinGenLdgr_Tx_ UploaderJob.xml ----> bdi-edge-app-rfi |
FinGenLdgr_Tx_ ImporterJob.xml ----> bdi-edge-app-rfi |