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)
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
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 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"
really nice post...
ReplyDeleteparesh