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

Thursday 15 November 2012

Extending Oracle BI Applications Calendars

The end of fiscal year is near, and it is a good time to extend Oracle BI Applications (OBIA) calendars.

The system that I'm going to describe in this post is an OBIA which is integrated with Oracle EBS 12.1.1 source system. The current 2013 fiscal year ends on 03/02/2013. The fiscal calendars (both source and generated) should be extended till the end of 2016 fiscal year which will be on 31/01/2016. The Gregorian calendar should be extended till the 31/12/2016 accordingly.

The OBIA supports Enterprise or Global calendar (cross-functional reporting calendar, can be fiscal or Gregorian), Fiscal calendar (can be accounting or financial), Gregorian calendar, 4-4-5 and 13 period calendars. The Time Dimension that represents those calendars uses the following tables from Oracle Business Analytics Warehouse (OBAW):

Gregorian Calendar
  • W_DAY_D
  • W_WEEK_D
  • W_QTR_D
  • W_YEAR_D
Multiple Fiscal Calendars (both source and generated)
Enterprise Calendar