SuiteQL - Get Work Order Usage By Day

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.quantity gives 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

divided.gif

(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:

final.gif

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.