April 25, 2013
Roughly 12 years ago I was attempting to analyze customer order changes that were received through electronic document interchange (EDI), specifically X12 830 documents that show order forecasted demand for specific part numbers. At the time, the EDI data was partially transformed and inserted into an Oracle 8.0.5 database, while that data is now inserted into an Oracle 11.2 database. I recently took another glance that this particular problem to see if there is a better method for performing the analysis.
Complications in the analysis:
- The EDI documents do not include the customer’s purchase order line numbers, just a list of outstanding due dates and quantities. That complication means that there is no easy way to see if an outstanding quantity has changed, or the date for that outstanding quantity has changed.
- It is possible to ship to the customer a partial order quantity, with the remaining quantity on the line handled as a back-order. If the customer does not alter this particular forecast line, only the quantity outstanding will be provided, along with the due date – the original quantity is not provided.
- It is possible to ship out an order line received in an 830 document up to five days before the customer’s due date.
- Shipped parts may require hours or days to reach the customer’s site, thus delaying when those shipped parts are acknowledged in the list of outstanding due dates and quantities received through EDI.
- EDI 830 documents are generally received the next weekday after the customer batch generates those documents on a nightly basis. Thus, there is an opportunity for the received forecast to be out of sync with what is known to have arrived at the customer site, and what is acknowledged in the EDI data.
- The customer may generate multiple EDI 830 documents for a particular part number/purchase order combination on any given day, and only the EDI 830 document received with the highest (sorted in ascending order) release number (which may not be a number) on a specific date is the valid release for the specific date.
- EDI 830 documents may be generated for a specific part number by the customer nearly every weekday, once a month, or any frequency in between.
I recall trying to find a solution to this particular problem 12 years ago. I thought that I could possibly pull all of the relevant data from the Oracle database into a custom application, and through some magical formula output what appeared to be a list of what the customer changed. However, what about those part quantities that are in transit to the customer? What about those part quantities that were received by the customer, but had not yet made their way through the customer’s batch processing system? What about those cases where the customer sents two or more EDI 830 releases for a specific part number, and those EDI releases are received on the same date?
At the time, I was frustrated. The quality of the output of my analysis would be dependent on the accuracy of humans (yes, and humans wrote the program that performed the analysis too). I simply had to find the first matching due date and order quantity from the previous release with the due date and order quanitity from the current release, and then rely on an end user to correct the 5% to 10% of the cases where the matching algorithm was wrong due to one or more of the “complications” outlined above.
—
While recently re-examining the problem, I noticed a data element in the EDI 830 document that is described as “ACCUM RECEIPTS AS OF THIS DATE”, and a luck would have it, that particular data element is one of those elements that was selected more than 12 years ago to be transformed into the Oracle Database tables (in the sample data included with this blog article, this column is named PREVIOUS_ACCUM_QTY). If only I recognized the meaning of the values of this column, and had access to Oracle analytical SQL functions 12 years ago, this particular analysis problem might not have had so many variables that could potentially throw off the quality of the analysis.
This particular blog article has a fairly large set of sample data – however, that sample data is a fairly small subset of the data that must be analyzed on a daily basis. The sample data may be downloaded here: analysischallenges.sql sample code (save the file as “analysischallenges.sql” – without the .doc double extension). If the file is saved in the same directory (folder) as SQL*Plus was started from, you may then execute the script in SQL*Plus to build the tables and populate the sample data by using the following command:
@analysischallenges.sql
The two sample table definitions are created with the following commands (also included in the script):
CREATE TABLE T1 ( CUSTOMER_PO VARCHAR2(15), RELEASE_ID VARCHAR2(15), PART_ID VARCHAR2(30), RELEASE_DATE DATE, PREVIOUS_ACCUM_QTY NUMBER, CREATE_DATE DATE, PRIMARY KEY (CUSTOMER_PO,RELEASE_ID)); CREATE TABLE T1_LINE ( CUSTOMER_PO VARCHAR2(12), RELEASE_ID VARCHAR2(12), QTY NUMBER, DUE_DATE DATE, PRIMARY KEY (CUSTOMER_PO,RELEASE_ID,DUE_DATE));
T1 Table Columns:
CUSTOMER_PO is the unique identifier for the customer purchase order received through the EDI 830 documents. RELEASE_ID defines the specific serialized change number for the customer’s PO – the RELEASE_ID is unique for a specific PO. PART_ID is the part number for which the customer is providing a forecast. RELEASE_DATE is the date that the forecast was batch generated in the customer’s computer system. PREVIOUS_ACCUM_QTY is the count of the number of this part number that the customer has already received for this specific customer purchase order. CREATE_DATE is the date that the forecast was received from the customer and transformed/inserted into the Oracle Database tables.
T1_LINE Table Columns:
CUSTOMER_PO and RELEASE_ID are undeclared foreign key columns that point back to table T1. QTY is the quantity of the part number that should (or is forecasted to) ship on the specfied DUE_DATE.
—–
If you are curious how I created the script to populate the tables, I executed the following in SQL*Plus, and then performed a little cleanup of the resulting spool files:
SET LINESIZE 200 SET TRIMSPOOL ON SPOOL analysischallenges_t1.txt SELECT 'INSERT INTO T1 VALUES ('||CHR(39)||CUSTOMER_PO||CHR(39)||',' ||CHR(39)||RELEASE_ID||CHR(39)||',' ||CHR(39)||PART_ID||CHR(39)||',' ||'TO_DATE('||CHR(39)||TO_CHAR(RELEASE_DATE,'DD-MON-YYYY')||CHR(39)||','||CHR(39)||'DD-MON-YYYY'||CHR(39)||')'||',' ||PREVIOUS_ACCUM_QTY||',' ||'TO_DATE('||CHR(39)||TO_CHAR(CREATE_DATE,'DD-MON-YYYY')||CHR(39)||','||CHR(39)||'DD-MON-YYYY'||CHR(39)||')'||');' D FROM T1 ORDER BY CUSTOMER_PO, RELEASE_ID; SPOOL OFF SPOOL analysischallenges_t1_line.txt SELECT 'INSERT INTO T1_LINE VALUES ('||CHR(39)||CUSTOMER_PO||CHR(39)||',' ||CHR(39)||RELEASE_ID||CHR(39)||',' ||QTY||',' ||'TO_DATE('||CHR(39)||TO_CHAR(DUE_DATE,'DD-MON-YYYY')||CHR(39)||','||CHR(39)||'DD-MON-YYYY'||CHR(39)||')'||');' D FROM T1_LINE ORDER BY CUSTOMER_PO, RELEASE_ID, DUE_DATE; SPOOL OFF
—–
We must find a starting point for performing the analysis of what changed from one day’s forecast to a prior day’s forecast. There is an issue with the EDI 830 releases that were received (identified by the CREATE_DATE column) on April 24, 2013 – two releases for the same customer purchase orders were received, and there were two purchase orders that demand the same part number. Our analysis is only concerned with the highest numbered (sorted alphabetically) release number document for each customer purchase order that was received on any particular day, so we need to find a way to eliminate the other older releases for the same customer purchase order that was received on the same day. The ROW_NUMBER analytic function is perfect for this task – we just need the output to show the same number (1) for the rows that we want to keep, and have some other number appear on the other rows. Something similar to the following may work (note that the TRUNC function, or other approach that uses a date/time range, probably should be used when working with the CREATE_DATE column – realistically, there could be a time component included with those date values):
COLUMN PART_ID FORMAT A10 SET LINESIZE 120 SET PAGESIZE 1000 SELECT T.PART_ID, T.CUSTOMER_PO, ROW_NUMBER() OVER (PARTITION BY T.CUSTOMER_PO,TRUNC(T.CREATE_DATE) ORDER BY T.RELEASE_ID DESC) REL_ON_DATE, T.RELEASE_ID, T.RELEASE_DATE, T.CREATE_DATE, T.PREVIOUS_ACCUM_QTY FROM T1 T WHERE T.CREATE_DATE=TO_DATE('24-APR-2013','DD-MON-YYYY') ORDER BY T.PART_ID, T.CUSTOMER_PO, T.RELEASE_ID; PART_ID CUSTOMER_PO REL_ON_DATE RELEASE_ID RELEASE_D CREATE_DA PREVIOUS_ACCUM_QTY ---------- --------------- ----------- --------------- --------- --------- ------------------ DS1812+ 1231234 2 20130402-001 23-APR-13 24-APR-13 24 DS1812+ 1231234 1 20130403-001 24-APR-13 24-APR-13 24 DS1812+ 1233290 2 20130402-001 23-APR-13 24-APR-13 4 DS1812+ 1233290 1 20130403-001 24-APR-13 24-APR-13 4 DS212+ 1133290 2 20130402-001 23-APR-13 24-APR-13 2 DS212+ 1133290 1 20130403-001 24-APR-13 24-APR-13 2 RS812 1231280 2 20130402-001 23-APR-13 24-APR-13 60 RS812 1231280 1 20130403-001 24-APR-13 24-APR-13 60
To remove all of the rows that have something other than 1 in the REL_ON_DATE column, we are able to slide the above SQL statement into an inline view, and then specify that the REL_ON_DATE column value must be equal to 1. Typically, there are supposed to be few/no changes made by the customer within eight weeks of the customer forecast’s release date, but that is not always the case. While eliminating the unnecessary releases, it probably makes sense to also calculate the end date of this “firm” period by adding 55 days to the date on which the forecast was created:
SELECT T.PART_ID, T.CUSTOMER_PO, T.RELEASE_ID, T.RELEASE_DATE+55 POSSIBLE_FIRM_DATE, T.PREVIOUS_ACCUM_QTY FROM (SELECT T.PART_ID, T.CUSTOMER_PO, ROW_NUMBER() OVER (PARTITION BY T.CUSTOMER_PO,TRUNC(T.CREATE_DATE) ORDER BY T.RELEASE_ID DESC) REL_ON_DATE, T.RELEASE_ID, T.RELEASE_DATE, T.CREATE_DATE, T.PREVIOUS_ACCUM_QTY FROM T1 T WHERE T.CREATE_DATE=TO_DATE('24-APR-2013','DD-MON-YYYY')) T WHERE T.REL_ON_DATE=1 ORDER BY T.PART_ID, T.CUSTOMER_PO, T.RELEASE_ID; PART_ID CUSTOMER_PO RELEASE_ID POSSIBLE_ PREVIOUS_ACCUM_QTY ---------- --------------- --------------- --------- ------------------ DS1812+ 1231234 20130403-001 18-JUN-13 24 DS1812+ 1233290 20130403-001 18-JUN-13 4 DS212+ 1133290 20130403-001 18-JUN-13 2 RS812 1231280 20130403-001 18-JUN-13 60
Next, we need to retrieve the forecasted delivery schedule (in the T1_LINES table) that are associated with the above rows. If we take the value in the PREVIOUS_ACCUM_QTY column, and find a way to generate a running SUM of the QTY column from the T1_LINE table, in theory we should be able to determine if the total quantity ordered through a specific date has changed. The analytic version of the SUM function offers the capability of producing a running SUM when an ORDER BY clause is included in the function call. To reduce the data set size, I will only consider those forecasts with a scheduled delivery date that is within 101 days of the customer’s forecast date. With those changes in place, the SQL statement is modified as follows:
SELECT T.PART_ID, T.CUSTOMER_PO, T.RELEASE_ID, T.RELEASE_DATE+55 POSSIBLE_FIRM_DATE, T.PREVIOUS_ACCUM_QTY, TL.DUE_DATE, TL.QTY, T.PREVIOUS_ACCUM_QTY + SUM(TL.QTY) OVER (PARTITION BY T.CUSTOMER_PO,T.RELEASE_ID ORDER BY TL.DUE_DATE) REL_RUNNING_SUM FROM (SELECT T.PART_ID, T.CUSTOMER_PO, ROW_NUMBER() OVER (PARTITION BY T.CUSTOMER_PO,TRUNC(T.CREATE_DATE) ORDER BY T.RELEASE_ID DESC) REL_ON_DATE, T.RELEASE_ID, T.RELEASE_DATE, T.CREATE_DATE, T.PREVIOUS_ACCUM_QTY FROM T1 T WHERE T.CREATE_DATE=TO_DATE('24-APR-2013','DD-MON-YYYY')) T, T1_LINE TL WHERE T.REL_ON_DATE=1 AND T.CUSTOMER_PO=TL.CUSTOMER_PO AND T.RELEASE_ID=TL.RELEASE_ID AND (T.RELEASE_DATE+100)>=TL.DUE_DATE ORDER BY T.PART_ID, T.CUSTOMER_PO, T.RELEASE_ID, TL.DUE_DATE; PART_ID CUSTOMER_PO RELEASE_ID POSSIBLE_ PREVIOUS_ACCUM_QTY DUE_DATE QTY REL_RUNNING_SUM ---------- --------------- --------------- --------- ------------------ --------- ---------- --------------- DS1812+ 1231234 20130403-001 18-JUN-13 24 24-APR-13 0 24 DS1812+ 1233290 20130403-001 18-JUN-13 4 24-APR-13 0 4 DS212+ 1133290 20130403-001 18-JUN-13 2 24-APR-13 0 2 RS812 1231280 20130403-001 18-JUN-13 60 27-JUN-13 1 61 RS812 1231280 20130403-001 18-JUN-13 60 03-JUL-13 1 62 RS812 1231280 20130403-001 18-JUN-13 60 24-JUL-13 2 64
As may be seen above, only customer purchase order 1231280 has a forecast, calling for a quantity of 1 on June 27, quantity of 1 on July 3, and a quantity of 2 on July 24. The previously received quantity for customer purchase order 1231280 is 60, so the additional forecast quantities will bring the total order quantities up to 61, 62, and 64 through the three dates included in the forecast.
-The Top Half of the Final SQL Statement is Above-
—
So, we now have a starting point – the data that was received on April 24, 2013. How does that forecast data compare with previously received forecasts? We need to start building the second half of the SQL statement, which looks at the historical data. To limit the dataset size, I will limit the data to only those EDI documents that were received up to 56 days prior (the eight week “firm” period) to the April 24, 2013 date that was specified earlier in this article:
SELECT T.CUSTOMER_PO, ROW_NUMBER() OVER (PARTITION BY T.CUSTOMER_PO,TRUNC(T.CREATE_DATE) ORDER BY T.RELEASE_ID DESC) REL_ON_DATE, TRUNC(T.CREATE_DATE) CREATE_DATE, T.RELEASE_ID, T.PREVIOUS_ACCUM_QTY FROM T1 T WHERE T.CREATE_DATE >= TO_DATE('24-APR-2013','DD-MON-YYYY') - 56 AND T.CREATE_DATE < TO_DATE('24-APR-2013','DD-MON-YYYY') ORDER BY T.CUSTOMER_PO, T.RELEASE_ID; CUSTOMER_PO REL_ON_DATE CREATE_DA RELEASE_ID PREVIOUS_ACCUM_QTY --------------- ----------- --------- --------------- ------------------ 1131111 1 27-FEB-13 0371 575 1131111 1 01-MAR-13 0381 577 1131111 1 06-MAR-13 0431 578 1131111 1 07-MAR-13 0441 581 1131111 1 08-MAR-13 0451 581 1131111 1 11-MAR-13 0461 581 ... 1131111 1 08-APR-13 0741 593 1131111 1 11-APR-13 0791 593 1131111 1 23-APR-13 0911 595 1131245 1 27-FEB-13 0371 2299 1131245 1 01-MAR-13 0381 2300 1131245 1 05-MAR-13 0421 2303 ... 1131745 1 19-APR-13 0871 435 1131745 1 23-APR-13 0911 435 1133290 1 27-FEB-13 20130205-001 2 1133290 1 28-FEB-13 20130206-001 2 1133290 1 01-MAR-13 20130207-001 2 ... 1133290 1 19-APR-13 20130328-001 2 1133290 1 23-APR-13 20130401-001 2 1231234 1 27-FEB-13 20130205-001 24 1231234 1 28-FEB-13 20130206-001 24 ... 1231234 1 23-APR-13 20130401-001 24 1231280 1 27-FEB-13 20130205-001 57 1231280 1 28-FEB-13 20130206-001 57 1231280 1 01-MAR-13 20130207-001 57 1231280 1 04-MAR-13 20130208-001 58 1231280 1 05-MAR-13 20130211-001 58 ... 1231280 1 04-APR-13 20130313-001 60 1231280 2 05-APR-13 20130314-002 60 ... 1231280 1 19-APR-13 20130328-001 60 1231280 1 23-APR-13 20130401-001 60 1233290 1 27-FEB-13 20130205-001 4 1233290 1 28-FEB-13 20130206-001 4 ...
As we found earlier, there could be more than one release for a customer purchase order received in a single date, and we are only concerned with the most recent release received on any particular day. We have a second issue, in that the dates may have shifted from one release to the next, so we have to artifically generate rows with the potentially missing dates, with an order quantity of 0 on those dates.
But, how to generate a list of dates? We could use a CONNECT BY LEVEL trick:
SELECT TO_DATE('24-APR-2013','DD-MON-YYYY')+(LEVEL-1) DUE_DATE FROM DUAL CONNECT BY LEVEL<=100; DUE_DATE --------- 24-APR-13 25-APR-13 26-APR-13 27-APR-13 28-APR-13 ... 15-JUN-13 16-JUN-13 17-JUN-13 18-JUN-13 ... 30-JUL-13 31-JUL-13 01-AUG-13
Now, by creating a Cartesian join between the dataset with the dates and the dataset with the EDI release header records, we will make certain that a date exists for every date that is potentially of interest (up to 100 days after April 24, 2013) when we later join the result with the T1_LINE table (the child table that lists the forecast delivery dates).
SELECT T2.CUSTOMER_PO, T2.RELEASE_ID, T2.CREATE_DATE, T2.PREVIOUS_ACCUM_QTY, D.DUE_DATE FROM (SELECT T.CUSTOMER_PO, ROW_NUMBER() OVER (PARTITION BY T.CUSTOMER_PO,TRUNC(T.CREATE_DATE) ORDER BY T.RELEASE_ID DESC) REL_ON_DATE, TRUNC(T.CREATE_DATE) CREATE_DATE, T.RELEASE_ID, T.PREVIOUS_ACCUM_QTY FROM T1 T WHERE T.CREATE_DATE >= TO_DATE('24-APR-2013','DD-MON-YYYY') - 56 AND T.CREATE_DATE < TO_DATE('24-APR-2013','DD-MON-YYYY') ORDER BY T.CUSTOMER_PO, T.RELEASE_ID) T2, (SELECT TO_DATE('24-APR-2013','DD-MON-YYYY')+(LEVEL-1) DUE_DATE FROM DUAL CONNECT BY LEVEL<=100) D WHERE T2.REL_ON_DATE=1 ORDER BY T2.CUSTOMER_PO, T2.RELEASE_ID, D.DUE_DATE; ... CUSTOMER_PO RELEASE_ID CREATE_DA PREVIOUS_ACCUM_QTY DUE_DATE --------------- --------------- --------- ------------------ --------- 1233290 20130318-001 09-APR-13 4 25-MAY-13 1233290 20130318-001 09-APR-13 4 26-MAY-13 1233290 20130318-001 09-APR-13 4 27-MAY-13 1233290 20130318-001 09-APR-13 4 28-MAY-13 ... 1233290 20130322-001 15-APR-13 4 22-JUN-13 1233290 20130322-001 15-APR-13 4 23-JUN-13 1233290 20130322-001 15-APR-13 4 24-JUN-13 1233290 20130322-001 15-APR-13 4 25-JUN-13 ... 1233290 20130401-001 23-APR-13 4 29-JUL-13 1233290 20130401-001 23-APR-13 4 30-JUL-13 1233290 20130401-001 23-APR-13 4 31-JUL-13 1233290 20130401-001 23-APR-13 4 01-AUG-13
Next, we will outer join to the T1_LINE table to bring in the actual forecasted delivery schedule from the previous dates (note that I also removed an unnecessary ORDER BY clause that was still embedded in the SQL statement – the Oracle query optimizer probably would have realized that the ORDER BY clause was meaningless to the query result, and automatically removed it – but I removed the clause just in case my assumption is incorrect):
SELECT T3.CUSTOMER_PO, T3.CREATE_DATE, T3.DUE_DATE, T3.PREVIOUS_ACCUM_QTY + SUM(NVL(TL.QTY,0)) OVER (PARTITION BY T3.CUSTOMER_PO,T3.RELEASE_ID ORDER BY T3.DUE_DATE) REL_RUNNING_SUM FROM (SELECT T2.CUSTOMER_PO, T2.RELEASE_ID, T2.CREATE_DATE, T2.PREVIOUS_ACCUM_QTY, D.DUE_DATE FROM (SELECT T.CUSTOMER_PO, ROW_NUMBER() OVER (PARTITION BY T.CUSTOMER_PO,TRUNC(T.CREATE_DATE) ORDER BY T.RELEASE_ID DESC) REL_ON_DATE, TRUNC(T.CREATE_DATE) CREATE_DATE, T.RELEASE_ID, T.PREVIOUS_ACCUM_QTY FROM T1 T WHERE T.CREATE_DATE >= TO_DATE('24-APR-2013','DD-MON-YYYY') - 56 AND T.CREATE_DATE < TO_DATE('24-APR-2013','DD-MON-YYYY')) T2, (SELECT TO_DATE('24-APR-2013','DD-MON-YYYY')+(LEVEL-1) DUE_DATE FROM DUAL CONNECT BY LEVEL<=100) D WHERE T2.REL_ON_DATE=1) T3, T1_LINE TL WHERE T3.CUSTOMER_PO=TL.CUSTOMER_PO(+) AND T3.RELEASE_ID=TL.RELEASE_ID(+) AND T3.DUE_DATE=TL.DUE_DATE(+) ORDER BY T3.CUSTOMER_PO, T3.RELEASE_ID, T3.DUE_DATE; CUSTOMER_PO CREATE_DA DUE_DATE REL_RUNNING_SUM --------------- --------- --------- --------------- 1131111 27-FEB-13 24-APR-13 577 1131111 27-FEB-13 25-APR-13 578 1131111 27-FEB-13 26-APR-13 579 1131111 27-FEB-13 27-APR-13 579 1131111 27-FEB-13 28-APR-13 579 1131111 27-FEB-13 29-APR-13 580 1131111 27-FEB-13 30-APR-13 581 ... 1131111 23-APR-13 30-JUL-13 636 1131111 23-APR-13 31-JUL-13 638 1131111 23-APR-13 01-AUG-13 639 ... 1231280 11-MAR-13 08-MAY-13 58 1231280 11-MAR-13 09-MAY-13 58 1231280 11-MAR-13 10-MAY-13 58 1231280 11-MAR-13 11-MAY-13 58 ... 1231280 23-APR-13 21-JUL-13 62 1231280 23-APR-13 22-JUL-13 62 1231280 23-APR-13 23-JUL-13 62 1231280 23-APR-13 24-JUL-13 64 1231280 23-APR-13 25-JUL-13 64 1231280 23-APR-13 26-JUL-13 64 ...
We are getting close. Now we just need to join the SQL created earlier in this article with the above SQL statement so that the current customer purchase order forecasts may be compared with the earlier received customer purchase order forecasts. The LISTAGG analytic funtion, introduced with Oracle Database 11.1, makes it easy to condense multiple rows from the resultset into a much smaller number of rows, if the resultset is grouped on at least one column. So, the final version of the analysis challenge appears as follows:
SELECT C.PART_ID, C.CUSTOMER_PO, C.POSSIBLE_FIRM_DATE, C.DUE_DATE, C.QTY, C.REL_RUNNING_SUM, LISTAGG(T4.REL_RUNNING_SUM||' ('||TO_CHAR(T4.CREATE_DATE,'MM/DD/YY')||')',', ') WITHIN GROUP (ORDER BY T4.CREATE_DATE DESC) EXCEPTIONS FROM (SELECT T.PART_ID, T.CUSTOMER_PO, T.RELEASE_ID, T.RELEASE_DATE+55 POSSIBLE_FIRM_DATE, T.PREVIOUS_ACCUM_QTY, TL.DUE_DATE, TL.QTY, T.PREVIOUS_ACCUM_QTY + SUM(TL.QTY) OVER (PARTITION BY T.CUSTOMER_PO,T.RELEASE_ID ORDER BY TL.DUE_DATE) REL_RUNNING_SUM FROM (SELECT T.PART_ID, T.CUSTOMER_PO, ROW_NUMBER() OVER (PARTITION BY T.CUSTOMER_PO,TRUNC(T.CREATE_DATE) ORDER BY T.RELEASE_ID DESC) REL_ON_DATE, T.RELEASE_ID, T.RELEASE_DATE, T.CREATE_DATE, T.PREVIOUS_ACCUM_QTY FROM T1 T WHERE T.CREATE_DATE=TO_DATE('24-APR-2013','DD-MON-YYYY')) T, T1_LINE TL WHERE T.REL_ON_DATE=1 AND T.CUSTOMER_PO=TL.CUSTOMER_PO AND T.RELEASE_ID=TL.RELEASE_ID AND (T.RELEASE_DATE+100)>=TL.DUE_DATE) C, (SELECT T3.CUSTOMER_PO, T3.CREATE_DATE, T3.DUE_DATE, T3.PREVIOUS_ACCUM_QTY + SUM(NVL(TL.QTY,0)) OVER (PARTITION BY T3.CUSTOMER_PO,T3.RELEASE_ID ORDER BY T3.DUE_DATE) REL_RUNNING_SUM FROM (SELECT T2.CUSTOMER_PO, T2.RELEASE_ID, T2.CREATE_DATE, T2.PREVIOUS_ACCUM_QTY, D.DUE_DATE FROM (SELECT T.CUSTOMER_PO, ROW_NUMBER() OVER (PARTITION BY T.CUSTOMER_PO,TRUNC(T.CREATE_DATE) ORDER BY T.RELEASE_ID DESC) REL_ON_DATE, TRUNC(T.CREATE_DATE) CREATE_DATE, T.RELEASE_ID, T.PREVIOUS_ACCUM_QTY FROM T1 T WHERE T.CREATE_DATE >= TO_DATE('24-APR-2013','DD-MON-YYYY') - 56 AND T.CREATE_DATE < TO_DATE('24-APR-2013','DD-MON-YYYY')) T2, (SELECT TO_DATE('24-APR-2013','DD-MON-YYYY')+(LEVEL-1) DUE_DATE FROM DUAL CONNECT BY LEVEL<=100) D WHERE T2.REL_ON_DATE=1) T3, T1_LINE TL WHERE T3.CUSTOMER_PO=TL.CUSTOMER_PO(+) AND T3.RELEASE_ID=TL.RELEASE_ID(+) AND T3.DUE_DATE=TL.DUE_DATE(+)) T4 WHERE C.CUSTOMER_PO=T4.CUSTOMER_PO AND C.DUE_DATE=T4.DUE_DATE AND C.REL_RUNNING_SUM<>T4.REL_RUNNING_SUM GROUP BY C.PART_ID, C.CUSTOMER_PO, C.POSSIBLE_FIRM_DATE, C.DUE_DATE, C.QTY, C.REL_RUNNING_SUM ORDER BY C.PART_ID, C.CUSTOMER_PO, C.DUE_DATE; PART_ID CUSTOMER_PO POSSIBLE_ DUE_DATE QTY REL_RUNNING_SUM ---------- --------------- --------- --------- ---------- --------------- EXCEPTIONS ----------------------------------------------------------------------------------------------------------------------- RS812 1231280 18-JUN-13 27-JUN-13 1 61 60 (03/22/13), 60 (03/21/13), 60 (03/19/13), 60 (03/18/13), 60 (03/15/13), 60 (03/14/13), 59 (03/13/13), 59 (03/12/13), 59 (03/11/13), 59 (03/08/13), 60 (03/07/13), 60 (03/06/13), 60 (03/05/13), 60 (03/04/13), 59 (03/01/13), 59 (02/28/13), 59 (02/27/13) RS812 1231280 18-JUN-13 03-JUL-13 1 62 61 (03/22/13), 61 (03/21/13), 61 (03/19/13), 61 (03/18/13), 61 (03/15/13), 61 (03/14/13), 60 (03/13/13), 60 (03/12/13), 60 (03/11/13), 60 (03/08/13), 61 (03/07/13), 61 (03/06/13), 61 (03/05/13), 61 (03/04/13), 60 (03/01/13), 60 (02/28/13), 60 (02/27/13) RS812 1231280 18-JUN-13 24-JUL-13 2 64 63 (03/26/13), 63 (03/25/13), 62 (03/22/13), 62 (03/21/13), 62 (03/19/13), 62 (03/18/13), 62 (03/15/13), 62 (03/14/13), 61 (03/13/13), 61 (03/12/13), 61 (03/11/13), 61 (03/08/13), 62 (03/07/13), 62 (03/06/13), 62 (03/05/13), 62 (03/04/13), 61 (03/01/13), 61 (02/28/13), 61 (02/27/13)
It is now easy to see that the requested total quantity due through June 27, 2013 increased by 2 since the first forecast which was received on February 27, 2013. The requested total quantity increased by 1 in the EDI release that was received on March 4, 2013, and by 1 again on the next EDI release that was received after March 22, 2013. The second and third lines of the output show that there was a little more fluctuation in the customer’s forecast, increasing in quantity, decreasing in quantity, and then increasing in quantity again.
An unfortunate problem may be present – what if the customer completely eliminates the forecast orders on their purchase order, but still sends in a header line for the forecast? A future enhancement may be required, where the CONNECT BY LEVEL trick is used in the first half of the SQL statement (the current releases), just as it is used in the second half of the SQL statement (the historical releases), so that when the top half of the final SQL statement is joined to the bottom half, rows returned by the bottom half of the SQL statement are not unnecessarily removed from the output.
The output is a little ugly. One approach to clean up the output involves spooling the output to an HTML file as explained here and in the documentation here. Another approach to clean up the output involves sending the data into an Excel spreadsheet using either a macro or the Microsoft Query Wizard, accessed from within Excel (see the articles in the Excel category of this blog for an explanation of these approaches).
—–
The final solution for this analysis challenge is so simple when built up from smaller SQL statements as shown above; I wonder why I did not consider this approach 12 years ago? 🙂
Recent Comments