Wednesday, December 9, 2020

Common Issue for Null Field with MERGE Operation Using DB Adapter in OIC

 In almost every integration implementation we have the need to replicate Oracle Fusion data to transformation layer where we will be doing Validations of FBDI files  data before importing to Oracle Fusion Using OIC.

To Replicate data in transformation layer there are multiple ways one them is usi merge operation of DB adapter.

 Below are the high level step performed when we are doing through OIC Using DB Adapter merge operation:

1). Create a replication extract(BI Report) in Fusion

2). Create an ESS job  in Fusion (If we have large volume of data otherwise call the Report directly)

3).  Either use Bursting in BI Publisher to burst the output to some sftp or UCM

      Or Use ExportBulkdata operation of ErpIntegrationService web service (This would automatically

      upload the output file to UCM (This to be done in OIC)).

4). Create a scheduled integration , which will submit the ESS job in Fusion, create an app driven 

      integration which will receive the callback from Fusion when the job is completed.

5). Download the Zip file as attachment(it supports upto 1 GB) ->extract the csv output file from it

6). Read CSV output using stage file in segments(as the volume is high), use  the report

     csv output as schema.

7). Now configure the DB adapter to  use Merge Operation (UPSERT) in a particular table

8). Map the fields, save the integration , activate and run the integration.


--------------------------------

Issue with Null field in Mapping

---------------------------------

There will be some fields in the report output which will be null.

Now when we use the merge operation, the null fields are ignored, rest other fields are inserted/updated in that row.

----------------------------------------------------------------

Let us take a real time scenario:

We want to replicate "Supplier Site" from Fusion to transformation layer.

At first if the supplier site is inactive, then the inactive date filed will not be "Blank or Null" in Fusion it will have some date value.

When we replicate the above data, it will insert  row correctly.

below is screenshot of the first time merge operation in table:

We can see the inactive date is not empty:



Now when we activate the HOME 1 site then the inactive date field in Fusion is set to "Blank or Null" in Fusion.

If we do merge now using DB adapter in OIC this null will be ignored it will have the old inactive date. Rest other field will be updated.

Below is the screenshot from Fusion:




Below is the screenshot from DB after Merge:


We can see that other fields like last update date etc. is updated but inactive field is still having old date. It should have been Null

Now let us do another update in Fusion. We will rename HOME1 to HOME in Fusion.

Below is the screenshot from Fusion:



Now when we do MERGE again using DB adapter in OIC. All the fields will get updated except the inactive field. The inactive filed will still be holding old data.

Below is the screenshot from DB after MERGE





==============================================================
Now since we know the issue let us find out why it is happening.

We are reading file in segment( using CSV schema) and mapping to DB adapter fields, it doesn't pick the null filed for update/insert.

By default DetectOmissions in DB adapter is set to true. Which forbids to take null fields for merge operation.

If we see the XSL mapper for DB adapter we will notice that it has not set the inactive date field to Nillable True.
By default the mapper for Inactive date or any other field will be like below:



We can see that, by default the nillable true is not mentioned for the fields.

There can be many solutions to resolve this issue. We will see 3 of the common ones.

1). When we activate the site then put a large value for date in Fusion instead of keeping it blank. 
      This will avoid the code change in report or OIC side. 
       The only problem with this is that the user has to remember to put large value to inactive date
      instead of keeping it as blank. 
2). Setting "DetectOmissions" to false DB adapter .jca file to false. Export the  integration and extract the .iar file. look for the DB adapter(Where merge operation is
     being done) under resources. Look for the .jca file as shown in the below screenshot:
  
 
Open the .jca file and seacrh for "DetectOmissions"   property. By default it will be set to "true".

 
 
Set the "DetectOmissions" property to  false. Import the integration. The null filed will be taken care of now.


3). Changing in XSL mapper of DB adapter.Export the integration, find the exact mapper from the iar file and edit it by placing below text for 
      inactive date field or wherever we think it can be null. import the mapper i integration.
      
      
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"

Below is the screenshot for the same from modified mapper:


------------------------------------------------------------------------------------------------------------------

Sample mapper request to MERGE before making the change in mapper:
We can see the inactive date is not set to nillable true







Below is the sample mapper request to MERGE after making the change:














No comments:

Post a Comment