A simple requirement that turned out to be a very long and complex query.
In my company we need a list of items that need to be sent to the WIP bins every day. Getting a list of items on a work order is simple enought but how can we group it by day across multiple work orders?
Let us take a look at the work orders that we will be working with:
We will use this query to start with:
SELECT
transaction.tranid,
transaction.id,
BUILTIN.DF(transactionline.item) AS Item,
transactionline.quantity * -1 / unitstypeuom.conversionrate AS Quantity,
BUILTIN.DF(item.stockunit) AS UM,
transaction.startdate,
transaction.enddate
FROM
transaction
INNER JOIN transactionline ON transaction.id = transactionline.transaction
INNER JOIN item ON item.id = transactionline.item
INNER JOIN unitstypeuom ON item.stockunit = unitstypeuom.internalid
WHERE
transaction.type = 'WorkOrd'
AND transactionline.mainline = 'F'
A few things to note here.
Since
transactionline.quantitygives me the quantity in base units I join the item table and the units table and convert it to stock units.I ignore the items the mainline as I do not want it to grab the item that we are building
I multiply it by -1 because the quantity in the lines is always a negative
Here it is running in the suiteql query tool by Tim Dietrich:
Note that for the purposes of this demonstration I am filtering it to three work orders: WO467, WO473, and WO474. I am also not including sub-assemblies. Your use case may vary.
The next step is to return the rows with the amount that would be used each day. For example suppose we use 100 cases of item 1 over 4 days. We only want the row to show 25 cases. We will get it for each day in the next step. The way to get it for each day would be to divide the quantity by transaction.endate - transaction.startdate + 1. The +1 is to account for the fact that we are including both days.
Here is the modified query. Note that I am removing the start date and end date columns
SELECT
transaction.tranid,
transaction.id,
BUILTIN.DF(transactionline.item) AS Item,
(transactionline.quantity * -1 / unitstypeuom.conversionrate)/(transactionline.enddate-transactionline.startdate + 1) AS Quantity,
BUILTIN.DF(item.stockunit) AS UM
FROM
transaction
INNER JOIN transactionline ON transaction.id = transactionline.transaction
INNER JOIN item ON item.id = transactionline.item
INNER JOIN unitstypeuom ON item.stockunit = unitstypeuom.internalid
WHERE
transaction.type = 'WorkOrd'
AND transactionline.mainline = 'F'
Here are the results:
For my use case I have rounded it to the the nearest 10-5
Now each row represents the quantity of an item that I would need for one day of that work order.
Now the fun begins.
The table that I need to generate consists of the All the dates that the work order is running.
So for example work order #WO473 is running from 8/7 through 8/10.
I need a table that looks like this:
| WO # | Day |
|---|---|
| WO473 | 8/7/2021 |
| WO473 | 8/8/2021 |
| WO473 | 8/9/2021 |
| WO473 | 8/10/2021 |
And adding WO474:
| WO # | Day |
|---|---|
| WO473 | 8/7/2021 |
| WO473 | 8/8/2021 |
| WO473 | 8/9/2021 |
| WO473 | 8/10/2021 |
| WO474 | 8/6/2021 |
| ... | ... |
| WO474 | 8/14/2021 |
Then when I have this table I can join it on the daily usage table above and have a table of rows where each row represents what we are using for each day for each item for each work order. Then we can group as necessary.
So how can we go about generating the table of days?
The trick is to use a dummy table provided by oracle called dual to generate a list of numbers using the query:
SELECT
rownum AS r
FROM
dual CONNECT BY rownum <= 100
Gives us a simple list of numbers 1-100
Why 100? Simply because there are no work orders that are more than 100 days. We can choose a larger number if needed.
The next table we need to generate is a table of the work orders with the start date and the amount of days. We can do that using transaction.enddate - transaction.startdate + 1 as mentioned above.
SELECT
transaction.tranid,
transaction.startdate,
transaction.enddate - transaction.startdate + 1 as Num_Days
FROM
transaction
Here are the results:
Now lets see what happens when we join in it on the dummy table that we created before and filter it where the dummy number is less than or equal to the days.
SELECT
transaction.tranid,
transaction.startdate,
transaction.enddate - transaction.startdate + 1 AS Num_Days,
r
FROM
transaction
INNER JOIN (
SELECT
rownum AS r
FROM
dual CONNECT BY rownum <= 100
) ON r <= (transaction.enddate - transaction.startdate + 1)
ORDER BY
transaction.tranid,
r
We now have the number of days table with an extra column numbered 1 through the amount of days:
We can now simply add the r column and the startdate column to get a list of days the work order is active. Note that we will have to actualy add r - 1 because the first day is included. We will also remove the number of days row and the start date.
SELECT
transaction.tranid,
transaction.startdate + (r-1) as day
FROM
transaction
INNER JOIN (
SELECT
rownum AS r
FROM
dual CONNECT BY rownum <= 100
) ON r <= (transaction.enddate - transaction.startdate + 1)
ORDER BY
transaction.tranid, transaction.startdate
We now finally have are dates table.
All that is left to do is join it on the item info table.
SELECT
dates_table.day, item_info.item, sum(item_info.Quantity), item_info.um
FROM
(SELECT
transaction.tranid,
transaction.startdate + (r-1) as day
FROM
transaction
INNER JOIN (
SELECT
rownum AS r
FROM
dual CONNECT BY rownum <= 100
) ON r <= (transaction.enddate - transaction.startdate + 1)
ORDER BY
transaction.tranid, transaction.startdate) as dates_table
INNER JOIN
(SELECT
transaction.tranid,
transaction.id,
BUILTIN.DF(transactionline.item) AS Item,
(transactionline.quantity * -1 / unitstypeuom.conversionrate)/(transactionline.enddate-transactionline.startdate + 1) AS Quantity,
BUILTIN.DF(item.stockunit) AS UM
FROM
transaction
INNER JOIN transactionline ON transaction.id = transactionline.transaction
INNER JOIN item ON item.id = transactionline.item
INNER JOIN unitstypeuom ON item.stockunit = unitstypeuom.internalid
WHERE
transaction.type = 'WorkOrd'
AND transactionline.mainline = 'F') as item_info on item_info.tranid = dates_table.tranid
ORDER BY
dates_table.tranid,
dates_table.day
(Embedded Gif kept breaking so here's a link)
Each row represents the quantity we need for that work order for that item for that day.
Since we don't really need it by work order, it's time to group them.
SELECT *
FROM
(SELECT
transaction.tranid,
transaction.startdate + (r-1) as day
FROM
transaction
INNER JOIN (
SELECT
rownum AS r
FROM
dual CONNECT BY rownum <= 100
) ON r <= (transaction.enddate - transaction.startdate + 1)
ORDER BY
transaction.tranid, transaction.startdate) as dates_table
INNER JOIN
(SELECT
transaction.tranid,
transaction.id,
BUILTIN.DF(transactionline.item) AS Item,
(transactionline.quantity * -1 / unitstypeuom.conversionrate)/(transactionline.enddate-transactionline.startdate + 1) AS Quantity,
BUILTIN.DF(item.stockunit) AS UM
FROM
transaction
INNER JOIN transactionline ON transaction.id = transactionline.transaction
INNER JOIN item ON item.id = transactionline.item
INNER JOIN unitstypeuom ON item.stockunit = unitstypeuom.internalid
WHERE
transaction.type = 'WorkOrd'
AND transactionline.mainline = 'F') as item_info on item_info.tranid = dates_table.tranid
GROUP BY
dates_table.day, item_info.item, item_info.um
And the results:
each line represents what the warehouse needs to transfer for that day.
A few disclaimers:
This code is fairly slow.
In reality you should use the internal ids.
I am pretty new to suiteql and sql in general so I have no idea if I'm missing the boat.
Anything you write is in you, I have no responsibility whatsoever.
Again I would like to thank Tim Dietrich for his wonderful tool, without which I would have no way of testing and debugging queries.
Top comments (0)