06 Feb 2014 Default settings for the soft delete option in Oracle BI APPS

During one of our last OBI Apps Finance 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 and UAT environments, some metric issues appeared after going live with the Production environment.

The first issue we noticed was related to an Accounts Payable (AP) report, which contained the number of in-validated invoices; the metric shown within the Oracle BI report was greater that the real number on in-validated invoices found in the Oracle E-Business Suite database (EBS). Finally AP users confirmed that they do not always follow the EBS best practices and some in-validated invoices were manually deleted instead of setting them to the “cancel” status prior to the deletion.

The second issue appeared in the Accounts Receivable (AR) business area. In this case we identified the mismatching within the aging snapshot table; this table holds the outstanding AR balance information and it was showing outstanding amounts where the balance should be 0. In this case no manual deletions were done, AR users modified payment terms from EBS front-end and when modifying the payment term on the invoice, a new record was created and the old payment schedule record deleted.

We could see that both issues are related to the deletion of records within the EBS source system. In an out-of-the-box Oracle BI APPS implementation, records that are deleted in the source system are not removed from the Oracle Business Analytics 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 (the soft delete feature is disabled by default).

Description of the Primary Extract and Delete Informatica PowerCenter mappings:

The following graph describes how the primary extract and delete Informatica PowerCenter mappings interact with the database tables:

Primary and Delete Informatica PowerCenter mappings description:

• The “_Primary” mappings perform a full extract of the primary keys from the EBS source system and load the result into the primary extract (_F_PE) table.
• The “_IdentifyDelete” mappings identify deleted records in the source by doing brute force comparison between primary extract table (_F_PE) and the target table (_XACT_F) and load the results into a staging table (_XACT_F_DEL).
• The “_SoftDelete” mappings update the delete flag column with a value ‘Y’ on the  target table (_XACT_F) for all the records that were  identified as ‘deleted’, driving from the staging area table (_F_DEL).

How to enable Primary Extract and Delete sessions on Tasks:

In order to enable the Primary Extract and Delete Informatica PowerCenter mappings you will have to apply changes to the Data Warehouse Administration Console (DAC) application. Here is the list of steps required:

1. In DAC, go to the Design view, and select the appropriate custom container from the drop-down list.
2. Display the Tasks tab.
3. Query for all tasks containing the string ‘Delete’ or ‘Primary’.

Accounts Payable (OBA APPS 7.9.6.4)

–        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

Accounts Receivable (OBA APPS 7.9.6.4)

–        SDE_ORA_ARTransactionFact_ARSchedules_Primary

–        SDE_ORA_ARTransactionFact_Adjustments_Primary

–        SDE_ORA_ARTransactionFact_CreditMemoApplication_Primary

–        SDE_ORA_ARTransactionFact_ReceivableApplication_Primary

–        SIL_ARTransactionFact_IdentifyDelete

–        SIL_ARTransactionFact_SoftDelete.

4. Deselect the Inactive check boxes for those tasks. Find below a screenshot which shows the AP primary extract tasks, being “Inactive” by default:

5.Reassemble your subject areas and rebuild your execution plans.

Additional considerations

In order to have a smooth implementation of the soft delete option, you may have to perform some additional tasks.

1. Review the “Sql Query” attribute in the Source Qualifier transformation within the SDE Primary mappings:

• Make sure that the WHERE clause in the SQL query is correctly specified
• Double check that the SQL query matches with the customizations you may have already applied to your Oracle BI APPS environment

2. Within DAC, select the “_Primary” tasks and verify that the “Truncate Always” checkbox for the target primary extract table (_F_PE) is selected just for the first task in the execution plan.

Example for the AP business area:

• SDE_ORA_APTransactionFact_ExpenseDistribution_Primary “Truncate Always” checkbox checked
• SDE_ORA_APTransactionFact_LiabilityDistribution_Primary “Truncate Always” checkbox NOT checked
• SDE_ORA_APTransactionFact_PaymentSchedule_Primary “Truncate Always” checkbox NOT checked
• SDE_ORA_APTransactionFact_Payment_Primary “Truncate Always” checkbox NOT checked

The screenshots below show how to check and un-check the “Truncate Always” option for the target table within the AP business area:

3. Impact on aging tables:

• If the soft delete option has been enabled after the system was already in PRODCUTION, you may have some wrong information in the aging tables (it happened to us with the Accounts Receivables business area) so you will have to build a SQL DDL that deletes the related records within the aging table.
• Double check that the “Sql Query” attribute in the Source Qualifier transformation within the PLP mappings that load the aging tables contains a filter for the soft delete DELETE_FLG field (DELETE_FLG=’N’)

4. Review your RPD:

Oracle BI APPS repository by default comes with filters on the soft delete DELETE_FLG field (DELETE_FLG=’N’) however it would be good to double check.

You may also want to take advantage of this soft delete DELETE_FLG field and build reports in order to analyse them.

Find below a screenshot showing the filter on the DELETE_FLG field within the repository:

Conclusion

The soft delete feature in Oracle BI APPS is disabled by default, so the best approach would be to analyse and agree with the business users if they will require the soft delete feature to be enabled and in case they need it, apply the required changes as soon as possible (before the deployment to the PRODUCTION environment) in order to minimise the impact.