Inventory report extra rows

[jdetaeye]

To be consistent we should have rows for:
On the plus side:

• Produced by MO
• Purchased by PO
• Inbound transfer by DO
• Total supply

On the minus side:

• Consumed by MO
• Outbound transfer by DO
• Total consumption

Also safety stock target should be added as a row, and displayed in the graph.

[hichamlahlou]

Inventory report should open if no record exists in buffer.
Add days of cover for start inventory (and end inventory ?).
Add safety stock value for the start date of the bucket.
In inventory Planning add a row "start inventory days of cover"

[jdetaeye]

First batch of comments:

• doc update - DONE
• release notes update - DONE
• graph is incorrect - DONE
• would be good to show the safety stock also in the graph - DONE
• javascript error when looking at the report for 1 buffer - DONE
• demo dataset on master branch: different DOS when switching from daily to weekly view. (suspect it's a case where the report start isn't a bucket start)

[jdetaeye]

• Performance issue when opening with MTS dataset. Stopped the query for the first page of results after 2 minutes. The cte subquery in its own is already problematic.

[jdetaeye]

• abbrevation SO, MO, DO and PO are clear for us, but we shouldn't assume people to know them

[jdetaeye]

Experimental subquery to compute days of cover. The nested query computes the cumulative consumption since a certain date. The outer query then takes the minimum date where it exceeds a certain value.

Such a value should then be used for every item+location+bucket in the selection, either as a cte that can later be joined in the big query, either a subquery in the big query.

select min(flowdate)
from (
select
item_id, location_id, flowdate, quantity, onhand,
sum(quantity)
over (order by flowdate asc rows between unbounded preceding and current row) as cumulative_consumed
from operationplanmaterial
where item_id = 'chair' and location_id = 'factory' and flowdate >= '2015-12-30 00:00:00+01' and quantity < 0
) t where cumulative_consumed < -100

[jdetaeye]

This version retrieves for all item-locations the intitial onhand for a certain bucket and the cumulative consumed.

select item_id, location_id, min(flowdate)
from (
select
item_id, location_id, flowdate, quantity, onhand,
sum(case when quantity < 0 then quantity end)
over (partition by item_id, location_id order by flowdate asc, id rows between unbounded preceding and current row) as cumulative_consumed,
first_value(onhand)
over (partition by item_id, location_id order by flowdate asc, id) - first_value(quantity)
over (partition by item_id, location_id order by flowdate asc, id) as starting_onhand
from operationplanmaterial
where flowdate >= '2018-8-30 00:00:00+01'
) t
where cumulative_consumed + starting_onhand < 0
group by item_id, location_id

[hichamlahlou]

@johan, can you give it a second try ?
I optimized the query, It displays on 16 seconds on the MTS dataset with 6 buckets to display.

[hichamlahlou]

DRP screen incorrect

[hichamlahlou]

IP screen corrected