7 Retail Hybrid Integration with EBS using BDI Applications

Hybrid Cloud Integration

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.

Figure 7-1 RFI EBS Hybrid Cloud Architecture

Table 7-1 Hybrid Cloud Topology

Applications to be installed	Hybrid Topology	On-Premises Topology
RMS-EDGE	Required	N/A
BDI-RFI	Required	N/A
BDI-PROCESSFLOW	Required	N/A
BDI-SCHEDULER	Optional	N/A
ODI	Required	Required
RFI	Required	Required
EBS	Required	Required
RIB, RIB-RMS	Required	Required

Implementation Details

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.

BDI Job Admin Application Implementation

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 Process Flow Application Implementation

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 Implementation

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

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