Oracle BI Apps - Sales Backlog

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 1^st 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 2^nd 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 (1^st jan) will be deleted. Next, all the Current ETL run date (2^nd Jan) records from Backlog line table will be inserted.

Similarly if next ETL is run on 10^th Jan then, all records inserted during ETL run in the current month (2^nd Jan) will be deleted and Current ETL run records (10^th 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 2^nd 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 1^st 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 5^th 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	28-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 5^th 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
05-FEB-2011	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).