Sales Backlog Logic implemented in Oracle BI Apps
What is a Backlog? What is the ETL logic implemented in Oracle BI Apps? These questions will be answered in this blog.
What is a Backlog?
In Simple terms any Sales Order Line that has not been Processed/ Closed within the expected Time is called as a Backlog Sales Order Line.
Description from Oracle BAW DMR 796
For every order line item, there could be a maximum of 2 backlog line items.
Financial: Occurs between the time order is created and time it is invoiced
Operational: Occur between the time order is created and time it is picked.
If picked quantity and invoiced quantity are greater than scheduled quantity, then the item is not backlogged. The above two backlogs could be further classified as scheduled or unscheduled. A backlog is scheduled if Ship date has been set, It is unscheduled if Ship date has not been scheduled. It is delinquent if Order is delinquent in relation to customer’s requested ship date, it is blocked if some detail in order process prevents order from being shipped or invoiced.
What is the ETL implementation logic in Oracle BI Apps?There are 2 fact tables that store the backlog information.
W_SALES_BACKLOG_LINE_F – This stores backlog line items on a particular date (last ETL run date).
ETL Logic - This table is truncated every time, before a data load is done into the table. The latest backlogs for the current date are fetched from Oracle E biz and stored into the table. The column BACKLOG_PERIOD_DK stores the last date of a calendar month. BACKLOG_DK is the date when the ETL was last run to refresh the backlog tables.
Example- If the last ETL was run on 1st Jan 2011 and Backlog period is Monthly. Then BACKLOG_DK = 01-JAN-2011 and BACKLOG_PERIOD_DK =31-JAN-2011.
W_SALES_BACKLOG_HISTORY_F- This fact stores the historical monthly snapshots of backlog lines and the snapshot of the current month till date (last ETL run date).
ETL Logic – The Backlog History Fact is loaded from the Backlog Line table and thus Backlog lines fact should be loaded before loading History table. Before the load starts, the BACKLOG_PERIOD_DK value from Backlog lines is fetched and all records that match this value in the history fact are removed. In the next step all records from backlog lines fact are appended into the history fact.
Example – If current ETL run date is 2nd Jan 2011, then in Backlog Line table BACKLOG_DK = 02-JAN-2011 and BACKLOG_PERIOD_DK =31-JAN-2011. Before loading the Backlog History Fact, all records with BACKLOG_PERIOD_DK=31-JAN-2011 will be deleted, i.e records inserted during any ETL run in the same month (1st jan) will be deleted. Next, all the Current ETL run date (2nd Jan) records from Backlog line table will be inserted.
Similarly if next ETL is run on 10th Jan then, all records inserted during ETL run in the current month (2nd Jan) will be deleted and Current ETL run records (10th Jan) will be inserted.
This goes on until the next period/ next month in this case, when the value of BACKLOG_PERIOD_DK changes. Example – If the next ETL run is on 2nd Feb 2011 then in Backlog Line table BACKLOG_DK = 01-FEB-2011 and BACKLOG_PERIOD_DK =28-FEB-2011. In history fact all records in current period (28-FEB-2011) will be deleted. Since this is the first ETL run in the current month, no records will be deleted. All 1st Feb records from backlog lines fact will be loaded into backlog history fact.
Case study
Let’s assume the ETL runs have occurred on the below dates
| ETL Run Number | ETL Run Date |
| 1 | 01-JAN-2011 |
| 2 | 02-JAN-2011 |
| 3 | 25-JAN-2011 |
| 4 | 01-FEB-2011 |
| 5 | 05-FEB-2011 |
The sample data in W_SALES_BACKLOG_LINE_F and W_SALES_BACKLOG_HISTORY_F are shown below.
W_SALES_BACKLOG_LINE_F
| BACKLOG_DK | BACKLOG_PERIOD_DK | ORDER_LINE_NUMBER |
| 05-FEB-2011 | 28-FEB-2011 | 100025 |
| 05-FEB-2011 | 28-FEB-2011 | 100030 |
2nd Column - Period End Date 1st Column shows Latest ETL Run Date
W_SALES_BACKLOG_HISTORY_F (Before ETL Load on 5th Feb)
| BACKLOG_DK | BACKLOG_PERIOD_DK | ORDER_LINE_NUMBER |
| 25-JAN-2011 | 31-JAN-2011 | 100001 |
| 5-JAN-2011 | 31-JAN-2011 | 100005 |
| 25-JAN-2011 | 31-JAN-2011 | 100006 |
| 01-FEB-2011 |
| 100001 |
| 01-FEB-2011 | 28-FEB-2011 | 100008 |
Backlogs in the current period will be deleted (4th and 5th rows) Monthly Snapshot (1st, 2nd & 3rd rows) – Backlogs as on last ETL run date for every month is captured. New backlogs for current period are inserted
W_SALES_BACKLOG_HISTORY_F (After ETL Load on 5th Feb)
| BACKLOG_DK | BACKLOG_PERIOD_DK | ORDER_LINE_NUMBER |
| 10-JAN-2011 | 31-JAN-2011 | 100001 |
| 10-JAN-2011 | 31-JAN-2011 | 100005 |
| 10-JAN-2011 | 31-JAN-2011 | 100006 |
|
| 28-FEB-2011 | 100025 |
| 05-FEB-2011 | 28-FEB-2011 | 100030 |
Latest backlogs from backlog lines fact are inserted (4th and 5th rows)
By observing the above diagrams we can infer that Backlog History fact stores the historical monthly snapshots of backlog lines and the snapshot of the current month till date (last ETL run date).
