ORACLE BI APPS 11.1.1.8.1 – HOW TO ENABLE SOFT DELETE PROCESS

What is the issue?

During the OBI Apps implementation and customization projects we were confronted with an uncommon issue. Even though we had run many validation processes and had acquired the agreed approvals within the Development, SIT and UAT environments, some metric issues appeared after going live with the Production environment.

One of the issues is the metric shown within the Oracle BI report was different from (normally greater than) the real number found in the data sources (i.e. EBS). The reason is some records were manually deleted in the EBS but not be reflected in the Data Warehouse.

In case you want to flag these records as deleted (soft delete) in the Data Warehouse, you must enable the related primary extract and delete mappings because this feature is disabled by default.

About Primary Extract and Delete Mappings Process

The primary extract mappings perform a full extract of the primary keys from the source system. Although many rows are generated from this extract, the data only extracts the Key ID and Source ID information from the source table. The primary extract mappings load these two columns into staging tables that are marked with a *_PE suffix.

The figure below provides an example of the beginning of the extract process. It shows the sequence of events over a two day period during which the information in the source table has changed. On day one, the data is extracted from a source table and loaded into the Oracle Business Analytics Warehouse table. On day two, Sales Order number three is deleted and a new sales order is received, creating a disparity between the Sales Order information in the two tables.

Above figure shows the primary extract and delete process that occurs when day two's information is extracted and loaded into the Oracle Business Analytics Warehouse from the source. The initial extract brings record four into the Oracle Business Analytics Warehouse. Then, using a primary extract mapping, the system extracts the Key IDs and the Source IDs from the source table and loads them into a primary extract staging table.

The extract mapping compares the keys in the primary extract staging table with the keys in the most current the Oracle Business Analytics Warehouse table. It looks for records that exist in the Oracle Business Analytics Warehouse but do not exist in the staging table (in the preceding example, record three), and sets the delete flag to Y in the Source Adapter, causing the corresponding record to be marked as deleted.

The extract mapping also looks for any new records that have been added to the source, and which do not already exist in the Oracle Business Analytics Warehouse; in this case, record four. Based on the information in the staging table, Sales Order number three is physically deleted from Oracle Business Analytics Warehouse, as shown in following figure. When the extract and load mappings run, the new sales order is added to the warehouse.

The following graph describes how the Primary Extract and Delete mappings interact with the database tables:

·         The _Primary mappings perform a full extract of the primary keys from EBS source system and load the result into the primary extract (_F_PE) table.

·         The _IdenfifyDelete mappings identify deleted records in the source by doing comparison between primary extract table (_F_PE) and the target table (_F) and load the results into a staging table (_F_DEL).

·         The _SoftDelete mappings update the delete flag column with a value ‘Y’ on the target table (_F) for all the records that were identified as ‘deleted’, driving from the staging area table (_F_DEL).

Enabling Soft Delete Process in Oracle BI Apps 11.1.1.8.1

In order to enable the Primary Extract and Delete mappings you will have to apply changes to the SOFT_DELETE_PREPROCESS data load parameter using Oracle BI Applications Configuration Manager (BIACM).

Here is the list of steps required:

1.       Log in to BIACM as the BI Applications Administrator user.

2.       Select the Manage Data Load Parameters link to display the Manage Data Load Parameter dialog.

3.       Select the Source Instance need to configure and search SOFT_DELETE_PREPROCESS parameter as shown below. Click Search.

1.       Select the SOFT_DELETE_PREPROCESS Parameter Code and you will see all the OOTB Dimensions/ Facts that you can enable soft delete process.

2.       Change the Parameter Value to ‘Yes’ in which Dimensions/ Facts you want to enable Primary Extract and Delete mapping, as shown below:

1.       By default, OBI Apps include the filter with the DELETE_FLG field (DELETE_FLG=’N’) but it would be good to double check.

You can check whether the filter is added in the Logical Table Source as screenshot below:

Soft Delete feature for new facts

The above instruction is just used for the pre-build facts in the Data Warehouse. For the new facts you need to see the OOTB Primary Extract and Delete mappings for reference and build the similar custom mappings for these new facts.

Below is the sample sql code generated by the Primary Extract and Delete Mappings process of the main process which populate the Purchase Cost data from EBS source to W_PURCH_COST_F table:

_Primary:

select    
    TO_CHAR(PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID)       C1_INTEGRATION_ID
from    BI_ACCNT.PO_DISTRIBUTIONS_ALL   PO_DISTRIBUTIONS_ALL
where    (1=1)
And (PO_DISTRIBUTIONS_ALL.CREATION_DATE>=TO_DATE(SUBSTR('#BIAPPS.INITIAL_EXTRACT_DATE',0,19),'YYYY-MM-DD HH24:MI:SS'))

_IdenfifyDelete:

insert into PMSAN3_DW.W_PURCH_COST_F_DEL
(DATASOURCE_NUM_ID, INTEGRATION_ID )
select
T.DATASOURCE_NUM_ID, T.INTEGRATION_ID 
from
PMSAN3_DW.W_PURCH_COST_F  T
left outer join PMSAN3_DW.W_PURCH_COST_F_PE  S
on T.DATASOURCE_NUM_ID =S.DATASOURCE_NUM_ID and
   T.INTEGRATION_ID =S.INTEGRATION_ID
where S.DATASOURCE_NUM_ID IS NULL
and S.INTEGRATION_ID IS NULL 
and T.DELETE_FLG = 'N' 
and T.CREATED_ON_DT > TO_DATE(SUBSTR('#BIAPPS.LAST_ARCHIVE_DATE',0,19),'YYYY-MM-DD HH24:MI:SS')
and exists
 (select 1
  from PMSAN3_DW.W_PURCH_COST_F_PE DS
  where T.DATASOURCE_NUM_ID = DS.DATASOURCE_NUM_ID
 )

               

_SoftDelete:

update PMSAN3_DW.W_PURCH_COST_F  T
set
 T.DELETE_FLG = 'Y'
,T.W_UPDATE_DT = SYSDATE
,T.ETL_PROC_WID = #BIAPPS.ETL_PROC_WID
where (T.DATASOURCE_NUM_ID, T.INTEGRATION_ID) IN
 (select D.DATASOURCE_NUM_ID, D.INTEGRATION_ID
  from PMSAN3_DW.W_PURCH_COST_F_DEL  D
 )

After you finish building Primary Extract and Delete mappings, remember that you should apply the filter (DELETE_FLG=’N’) in the Logical Table Source.