Pages

Monday 10 June 2013

Soft Delete Functionality in Oracle BI Applications

Couple of weeks ago I came across an issue with one of the report in OBIA ('Financials - Payables' subject area). The reports was showing incorrect remaining amount for number of accounts. Upon further investigation it was discovered that AP transactions fact table (W_AP_XACT_F) contains extra rows, compared to the source tables in Oracle EBS. Business users confirmed that invoices can be deleted manually (this is a standard functionality in Oracle EBS) although the best practice is to cancel the invoice. Another scenario which have resulted in the mentioned mismatch is when invoice payment schedule is being changed. This is also a standard functionality Oracle EBS.

In order to handle such situations OBIA uses soft delete functionality. The system described in this post is OBIA 7.9.6.1 which is integrated with Oracle EBS 12.1.1 source system. Having said this I believe the soft delete functionality has not change much in later versions of OBIA (i.e. 7.9.6.x) for Oracle EBS source system.

The soft delete functionality for Oracle EBS source system is implemented in OBIA in form of two types of mappings:
  • Primary extract mappings (*_Primary)
  • Delete mappings (*_IdentifyDelete and *_SoftDelete)
The primary extract mapping (*_Primary) extracts the integration IDs from source system and insert them into corresponding primary extract table (*_PE) along with the data source IDs. For the AP transactions fact table (W_AP_XACT_F) the primary extract table will be W_AP_XACT_F_PE. The identify delete mapping (*_IdentifyDelete) then compares integration IDs and data source IDs in primary extract table and the fact table (W_AP_XACT_F_PE versus W_AP_XACT_F for the AP transactions) and inserts the delete candidate integration IDs (along with data source IDs) into delete table (*_DEL). For the AP transactions fact table (W_AP_XACT_F) the delete table will be W_AP_XACT_F_DEL. Finally, the delete mapping (*_SoftDelete) will use the delete table (W_AP_XACT_F_DEL for the AP transactions) and mark all the rows in the fact table (W_AP_XACT_F for the AP transactions) as deleted by setting the DELETE_FLG column value to 'Y'.

Oracle EBS includes functionality to archive the historical records. As a result, historical records are moved to a septate database and the main database retains only the current records. In order to properly handle this scenario (and not treat the archived records as deleted records), the soft delete functionality in OBIA uses the $$LAST_ARCHIVE_DATE source system parameter. The value of this parameters should be set properly to reflect the last archive date in the Oracle EBS source system. Effectively, this parameter results in additional predicate in source qualifiers of the identify delete mappings (CREATED_ON_DT > '$$LAST_ARCHIVE_DATE').

The primary extract and soft delete mappings are inactive by default in OBIA 7.9.6.1. Naturally, we have to activate them before progressing any further. For the 'Financials - Payables' subject area (AP transactions) we have to activate the following mappings (I think managing soft delete functionality by means of configuration tags would be much elegant solution though):
  • SDE_ORA_APTransactionFact_ExpenseDistribution_Primary
  • SDE_ORA_APTransactionFact_LiabilityDistribution_Primary
  • SDE_ORA_APTransactionFact_PaymentSchedule_Primary
  • SDE_ORA_APTransactionFact_Payment_Primary
  • SIL_APTransactionFact_IdentifyDelete
  • SIL_APTransactionFact_SoftDelete
Note that SIL_APTransactionFact_IdentifyDelete and SIL_APTransactionFact_SoftDelete tasks above do not have a command for the full load. This does make sense indeed, because there is no point in performing a soft delete procedure while running a full load.

To support this exercise I have created an exact clone of the original 'Financials - Payables' subject area and called it 'Financials - Payables - Housekeeping':
After assembling the 'Financials - Payables - Housekeeping' subject area I have inactivated all the tasks except for the following ones:
  • Load Row into Run Table
  • SDE_ORA_APTransactionFact_ExpenseDistribution_Primary
  • SDE_ORA_APTransactionFact_LiabilityDistribution_Primary
  • SDE_ORA_APTransactionFact_PaymentSchedule_Primary
  • SDE_ORA_APTransactionFact_Payment_Primary
  • SIL_APTransactionFact_IdentifyDelete
  • Update Row into Run Table
I have intentionally left the SIL_APTransactionFact_SoftDelete task inactive because I'd like to evaluate the actual number of soft delete candidate rows prior to actually soft-deleting them:
I have also created a new execution plan called 'Financials - Payables - Housekeeping' and added the 'Financials - Payables - Housekeeping' subject area to it:

Before proceeding any further we have to set the value for $$LAST_ARCHIVE_DATE source system parameter. As it was mentioned in the beginning of this post, this parameter specifies the last archive date in the Oracle EBS source system and is used by soft delete mappings to properly process the archived records in Oracle Business Analytics Warehouse (OBAW). In my case no records were archived in the Oracle EBS source system and the value for $$LAST_ARCHIVE_DATE was set to '1980-01-01 00:00:00':

After generating parameters, assigning parameter values and building the new execution plan it's time to run it for the first time.
As it was expected (remember that both SIL_APTransactionFact_IdentifyDelete and SIL_APTransactionFact_SoftDelete tasks above did not have a command for the full load) the SIL_APTransactionFact_IdentifyDelete mapping did not identify any candidate rows for the soft deletion:
After the second run of the 'Financials - Payables - Housekeeping' execution plan the figures are different:
The SIL_APTransactionFact_IdentifyDelete task identified 2,007,170 soft delete candidate rows. This is quite a large number and a quick check on the SIL_APTransactionFact_SoftDelete task in Informatica PowerCenter Workflow Manager (Update as Update for target W_AP_XACT_F table) suggests that this mapping is not designed to process such a large number of rows:

In order to handle such a large number of soft delete candidates I've decided to run a manual update using the delete table. The ETL_PROC_WID is assigned the ETL process ID of the last execution of the 'Financials - Payables - Housekeeping' execution plan:
SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 32;

Session altered.

SQL> UPDATE W_AP_XACT_F F
  2  SET F.DELETE_FLG = 'Y',
  3    W_UPDATE_DT    = SYSDATE,
  4    ETL_PROC_WID   = 22840779
  5  WHERE EXISTS
  6    (SELECT FD.INTEGRATION_ID
  7    FROM W_AP_XACT_F_DEL FD
  8    WHERE FD.DATASOURCE_NUM_ID = F.DATASOURCE_NUM_ID
  9    AND FD.INTEGRATION_ID      = F.INTEGRATION_ID
 10    );

2007170 rows updated.

SQL> COMMIT;

Commit complete.

SQL>

Now that we have soft-deleted the appropriate records from the W_AP_XACT_F table, it's time to enable the SIL_APTransactionFact_SoftDelete task in 'Financials - Payables' subject area in order to have completely automated soft delete infrastructure:
In order to include the SIL_APTransactionFact_SoftDelete task in the 'Financials - Payables - Housekeeping' execution plan we should re-build the execution plan. A quick check on the 'Financials - Payables - Housekeeping' execution plan shows that the SIL_APTransactionFact_SoftDelete was properly included in the ordered tasks list:

Knowing that since the time of the manual soft delete (above) there were no invoice deletions, invoice payment amendments or any other activities that result in record deletions in the Oracle EBS source system, I assume the next execution of the soft delete mappings should not detect any soft delete candidate rows. The next run of the 'Financials - Payables - Housekeeping' execution plan proves this assumption:

The first important aspect to be considered at this stage is the inclusion of the soft delete tasks in the original 'Financials - Payables' subject area and the 'Financials - Payables' execution plan. The 'Financials - Payables - Housekeeping' subject area and the 'Financials - Payables - Housekeeping' execution plan were created specifically for the soft delete demonstration and have only soft delete related tasks enabled. In real world, when enabled, the soft delete SIL mappings (*_IdentifyDelete and *_SoftDelete) are placed in the ordered task list of the execution plan after the corresponding fact SIL mappings but before the aggregate PLP mappings (note the depth of the tasks):
This way the soft delete will happen before the aggregation and the aggregate PLP mappings will have a chance to properly handle the soft-deleted records.

The second important aspect to be considered is the handling of the soft-deleted records in the PLP mappings (AP transaction aggregates). The diagram below represents the ETL process flow diagram for AP transaction aggregates:
As it can be seen from the diagram, the PLP_APXacts_GroupAccount_A_Stage is the mapping where the filtering of the soft-deleted records is expected to be. A closer look at the custom SQL code for PLP_APXacts_GroupAccount_A_Stage.SQ_IA_AP_XACTS source qualifier suggests there is a bug and the soft-deleted records are selected along with the other records:
SELECT $$Hint1
W_AP_XACT_F.SUPPLIER_WID, 
W_AP_XACT_F.SPLR_ACCT_WID, 
W_AP_XACT_F.OPERATING_UNIT_ORG_WID,
W_AP_XACT_F.PAYABLES_ORG_WID,
W_AP_XACT_F.LEDGER_WID,
W_AP_XACT_F.COMPANY_ORG_WID, 
W_AP_XACT_F.BUSN_AREA_ORG_WID, 
W_AP_XACT_F.PROFIT_CENTER_WID, 
W_AP_XACT_F.POSTED_ON_DT_WID, 
W_AP_XACT_F.CLEARED_ON_DT_WID,
W_AP_XACT_F.PAYMENT_DUE_DT_WID,
W_AP_XACT_F.MCAL_CAL_WID,
W_AP_XACT_F.UOM_CODE, 
W_AP_XACT_F.DB_CR_IND, 
W_AP_XACT_F.DOC_CURR_CODE, 
W_AP_XACT_F.LOC_CURR_CODE, 
SUM(W_AP_XACT_F.AP_LOC_AMT), 
SUM($$Hint_Tera_Pre_Cast  $$Hint_Tera_Pre_Cast W_AP_XACT_F.AP_DOC_AMT* W_AP_XACT_F.GLOBAL1_EXCHANGE_RATE $$Hint_Tera_Post_Cast  $$Hint_Tera_Post_Cast) AS AP_GLOBAL1_AMT, 
SUM($$Hint_Tera_Pre_Cast  $$Hint_Tera_Pre_Cast W_AP_XACT_F.AP_DOC_AMT* W_AP_XACT_F.GLOBAL2_EXCHANGE_RATE $$Hint_Tera_Post_Cast  $$Hint_Tera_Post_Cast) AS AP_GLOBAL2_AMT, 
SUM($$Hint_Tera_Pre_Cast  $$Hint_Tera_Pre_Cast W_AP_XACT_F.AP_DOC_AMT* W_AP_XACT_F.GLOBAL3_EXCHANGE_RATE $$Hint_Tera_Post_Cast  $$Hint_Tera_Post_Cast) AS AP_GLOBAL3_AMT, 
SUM(W_AP_XACT_F.XACT_QTY), 
W_AP_XACT_F.DATASOURCE_NUM_ID, 
W_GL_ACCOUNT_D.GL_ACCOUNT_CAT_CODE, 
W_GL_ACCOUNT_D.FIN_STMT_ITEM_CODE, 
W_GL_ACCOUNT_D.CHART_OF_ACCOUNTS,
W_GL_ACCOUNT_D.GROUP_ACCOUNT_NUM, 
W_GL_ACCOUNT_D.ACCOUNT_GROUP_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER1_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER2_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER3_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER4_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER5_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER6_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER7_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER8_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER9_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER10_CODE, 
CASE WHEN W_STATUS_D.W_STATUS_CODE IN ('POSTED', 'REVERSED') THEN 'Y' ELSE 'N' END AS POSTED_FLG,
W_AP_XACT_F.DOC_TYPE_WID, 
W_AP_XACT_F.DOC_STATUS_WID, 
COUNT(*)  XACTS_COUNT,
SUM(CASE WHEN W_STATUS_D.W_SUBSTATUS_CODE LIKE 'CLEARED%' THEN (CASE WHEN  D2.JULIAN_DAY_NUM IS NULL OR D1.JULIAN_DAY_NUM IS NULL THEN 0 ELSE (D2.JULIAN_DAY_NUM- D1.JULIAN_DAY_NUM) END) ELSE 0 END) PMT_DAYS_COUNT,
SUM(CASE WHEN  D2.JULIAN_DAY_NUM IS NULL OR D1.JULIAN_DAY_NUM IS NULL THEN 0 ELSE (D2.JULIAN_DAY_NUM - D1.JULIAN_DAY_NUM)* W_AP_XACT_F.AP_DOC_AMT END) PMT_DAYS_TIMES_AMT,
CASE WHEN (W_STATUS_D.W_SUBSTATUS_CODE LIKE 'CLEARED%') THEN (CASE WHEN (D2.JULIAN_DAY_NUM > D3.JULIAN_DAY_NUM) THEN 'Y' ELSE 'N' END) ELSE 'N' END AS LATE_PAID_FLG,
W_AP_XACT_F.TENANT_ID
FROM
W_GL_ACCOUNT_D, 
W_STATUS_D, 
W_AP_XACT_F, 
W_XACT_TYPE_D,
W_DAY_D D1, 
W_DAY_D D2, 
W_DAY_D D3,
W_MCAL_DAY_D PREVQTR
WHERE 
W_AP_XACT_F.MCAL_CAL_WID = PREVQTR.MCAL_CAL_WID
AND W_AP_XACT_F.POSTED_ON_DT_WID  >= PREVQTR.MCAL_DAY_DT_WID 
AND PREVQTR.ADJUSTMENT_PERIOD_FLG = 'N'
AND PREVQTR.W_CURRENT_MCAL_QTR_CODE = 'Previous' AND PREVQTR.FST_DAY_MCAL_QTR_FLG = 'Y'
AND W_AP_XACT_F.INVOICED_ON_DT_WID = D1.ROW_WID
AND W_AP_XACT_F.CLEARED_ON_DT_WID = D2.ROW_WID
AND W_AP_XACT_F.PAYMENT_DUE_DT_WID = D3.ROW_WID
AND W_GL_ACCOUNT_D.ROW_WID = W_AP_XACT_F.GL_ACCOUNT_WID 
AND W_AP_XACT_F.DOC_STATUS_WID = W_STATUS_D.ROW_WID 
AND  W_AP_XACT_F.DOC_TYPE_WID = W_XACT_TYPE_D.ROW_WID
GROUP BY
W_AP_XACT_F.SUPPLIER_WID, 
W_AP_XACT_F.SPLR_ACCT_WID, 
W_AP_XACT_F.OPERATING_UNIT_ORG_WID,
W_AP_XACT_F.PAYABLES_ORG_WID,
W_AP_XACT_F.LEDGER_WID,
W_AP_XACT_F.COMPANY_ORG_WID, 
W_AP_XACT_F.BUSN_AREA_ORG_WID, 
W_AP_XACT_F.PROFIT_CENTER_WID, 
W_AP_XACT_F.POSTED_ON_DT_WID, 
W_AP_XACT_F.MCAL_CAL_WID,
W_AP_XACT_F.UOM_CODE, 
W_AP_XACT_F.DB_CR_IND, 
W_AP_XACT_F.DOC_CURR_CODE, 
W_AP_XACT_F.LOC_CURR_CODE, 
W_AP_XACT_F.DATASOURCE_NUM_ID, 
W_GL_ACCOUNT_D.GL_ACCOUNT_CAT_CODE, 
W_GL_ACCOUNT_D.FIN_STMT_ITEM_CODE, 
W_GL_ACCOUNT_D.CHART_OF_ACCOUNTS,
W_GL_ACCOUNT_D.GROUP_ACCOUNT_NUM, 
W_GL_ACCOUNT_D.ACCOUNT_GROUP_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER1_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER2_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER3_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER4_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER5_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER6_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER7_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER8_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER9_CODE, 
W_GL_ACCOUNT_D.ACCOUNT_HIER10_CODE, 
W_STATUS_D.W_STATUS_CODE,
W_STATUS_D.W_SUBSTATUS_CODE,
W_AP_XACT_F.DOC_TYPE_WID, 
W_AP_XACT_F.DOC_STATUS_WID,
W_AP_XACT_F.CLEARED_ON_DT_WID,
W_AP_XACT_F.PAYMENT_DUE_DT_WID,
W_AP_XACT_F.TENANT_ID,
D1.JULIAN_DAY_NUM,
D2.JULIAN_DAY_NUM,
D3.JULIAN_DAY_NUM
What is missing here is the predicate to filter out the soft-deleted records, which is to be placed as the last condition in the WHERE clause:
...
AND W_AP_XACT_F.DELETE_FLG='N'
...
Apparently this is a known Oracle BI Applications bug (Bug ID 9078998) which also affects the AP Invoices (Article ID 987190.1, 1299954.1) and is fixed in the Oracle BI Applications 7.9.6.2. Once the custom SQL code for the PLP_APXacts_GroupAccount_A_Stage.SQ_IA_AP_XACTS source qualifier is updated, the PLP_APXacts_GroupAccount_A_Stage and PLP_APXacts_GroupAccount_A_Stage_Full workflows should also be update (update mapping for the PLP_APXacts_GroupAccount_A_Stage and PLP_APXacts_GroupAccount_A_Stage_Full tasks). The last step here is to set the refresh dates to NULL and perform a full load of the following AP transaction aggregate tables:
  • W_AP_XACT_GRPACCT_DAY_A
  • W_AP_XACT_GRPACCT_FSCLPRD_A
  • W_AP_XACT_GRPACCT_FSCLQTR_A
  • W_AP_XACT_GRPACCT_FSCLYR_A

The last important aspect of the handling of the soft-deleted records in OBIEE. The AP transaction aggregate tables already contain the correct data after the full load, and my focus here is the W_AP_XACT_F table and the related OBIEE repository objects. As it can be seen in the OBIEE repository, the W_AP_XACT_F table has seven related logical table sources:
  • "Core"."Dim - AP Transaction Details"."Fact_W_AP_XACT_F"
  • "Core"."Dim - AP Transaction Details"."Fact_W_AP_XACT_F_GLJournalID"
  • "Core"."Dim - Purchase Order Attributes"."Fact_W_AP_XACT_F"
  • "Core"."Fact - Fins - (Internal) AP Balance Delta"."Fact_W_AP_XACT_F_GLAccount_Day_Balance Delta"
  • "Core"."Fact - Fins - AP Posted Transaction"."Fact_W_AP_XACT_F_Posted Transaction"
  • "Core"."Fact - Fins - AP Transaction"."Fact_W_AP_XACT_F"
  • "Core"."Fact - Fins - AP Transaction"."Fact_W_AP_XACT_F_Payment_Due_Date"
Further check on the fact-related related logical table sources shows that all of them include the DELETE_FLG='N' filtering condition which essentially mean that the soft-deleted records are not selected while running reports in OBIEE:

1 comment: