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'.
