Hi,
I'm referring to a previous thread answered "FIFO in pl/sql query or procedure"
2187145
I find the code very interesting for FIFO mgt.
However I tried with the following data and unfortunately it did not work.
I was hoping that the sql could work when we have missing quantity to start with
In this case, each reception would be used.
see below for the sql statements:
the total of received items ( 173 ) is > than the delivered ones (163)
In this case it would work by adding an item "received_quantity" in the LEAST statement but I'm not sure this will fix for all cases
Regards
Jean Marie Delhaze
SQL:
/*1)Table of received items*/
create table receipts
(item_id number(5),
received_date date,
received_quantity number,
receipt_id number(5));
insert into receipts(item_id, received_date, received_quantity, receipt_id)
values(668, '11 feb 2013', 100, 441);
insert into receipts(item_id, received_date, received_quantity, receipt_id)
values(668, '12 feb 2013', 53, 442);
insert into receipts(item_id, received_date, received_quantity, receipt_id)
values(668, '13 feb 2013', 17, 443);
/*2)Table of sold items */
create table deliveries
(item_id number(5),
delivered_date date,
delivered_quantity number,
delivery_id number(5));
insert into deliveries(item_id, delivered_date, delivered_quantity, delivery_id)
values(668, '14 apr 2013', 163, 1301);
the provided solution in the previous thread:
WITH receipts_rt AS
(
SELECT item_id
, received_date
, received_quantity
, SUM (received_quantity) OVER ( PARTITION BY item_id
ORDER BY received_date
, receipt_id
) AS received_total
, receipt_id
FROM receipts
)
, deliveries_rt AS
(
SELECT item_id
, delivered_date
, delivered_quantity
, SUM (delivered_quantity) OVER ( PARTITION BY item_id
ORDER BY delivered_date
, delivery_id
) AS delivered_total
, delivery_id
FROM deliveries
)
SELECT d.item_id
, d.delivered_date
, LEAST ( received_total + delivered_quantity - delivered_total
, delivered_total + received_quantity - received_total
, delivered_quantity
) AS delivered_quantity
, d.delivery_id
, r.receipt_id AS from_receipt
, GREATEST ( received_total - delivered_total
, 0
) AS stock_quantity
FROM deliveries_rt d
JOIN receipts_rt r ON d.item_id = r.item_id
AND d.delivered_total > r.received_total - r.received_quantity
AND r.received_total > d.delivered_total - d.delivered_quantity
ORDER BY d.item_id
, d.delivered_date
, d.delivery_id
, r.received_date
, r.receipt_id
;
ITEM_ID DELIVERED_DATE DELIVERED_QUANTITY DELIVERY_ID FROM_RECEIPT STOCK_QUANTITY
---------- -------------- ------------------ ----------- ------------ --------------
668 14-APR-13 100 1301 441 0
668 14-APR-13 63 1301 442 0
668 14-APR-13 10 1301 443 7
We should have
ITEM_ID DELIVERED_DATE DELIVERED_QUANTITY DELIVERY_ID FROM_RECEIPT STOCK_QUANTITY
---------- -------------- ------------------ ----------- ------------ --------------
668 14-APR-13 100 1301 441 0
668 14-APR-13 *53* 1301 442 0
668 14-APR-13 10 1301 443 7
A change in the LEAST statement for
, LEAST ( received_total + delivered_quantity - delivered_total
, delivered_total + received_quantity - received_total
, delivered_quantity
, received_quantity
) AS delivered_quantity
Fixes the issue for this case.
I do not know wether this will fixe for all cases
Regards,
JM