Hi,
I have two tables:
1)Table of recieved items
create table receipts
(item_id number(5),
recieved_date date,
recieved_quantity number,
receipt_id number(5));
insert into receipts(item_id, recieved_date, recieved_quantity, receipt_id)
values(668, '29/12/10', 400, 441);
insert into receipts(item_id, recieved_date, recieved_quantity, receipt_id)
values(668, '29/12/10', 100, 442);
insert into receipts(item_id, recieved_date, recieved_quantity, receipt_id)
values(668, '03/02/11', 150, 444);
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, '01/02/11', 10, 1301);
insert into deliveries(item_id, delivered_date, delivered_quantity, delivery_id)
values(668, '02/02/11', 450, 1303);
insert into deliveries(item_id, delivered_date, delivered_quantity, delivery_id)
values(668, '08/02/11', 15, 1305);
insert into deliveries(item_id, delivered_date, delivered_quantity, delivery_id)
values(668, '09/02/11', 90, 1306);
I need to track down from which batch goods are taken (for each delivery) using FIFO method.
FIFO priorities are RECIEVED_DATE, RECEIPT_ID for INs and DELIVERED_DATE, DELIVERY_ID for OUTs.
Desired output should be something like this:
{noformat}
ITEM_ID DELIVERED_DATE DELIVERED_QUANTITY DELIVERY_ID FROM_RECEIPT STOCK_QUANTITY(*)
------------- --------------- -------------------- -------------- -------------- -----------------
668 01/02/11 10 1301 441 390
668 02/02/11 390(**) 1303 441 0
668 02/02/11 60(**) 1303 442 40
668 08/02/11 15 1305 442 25
668 09/02/11 25(**) 1306 442 0
668 09/02/11 65(**) 1306 443 85
{noformat}
(*)Where STOCK_QUANTITY is the remaining quantity from that batch at the moment after according delivery amount deduction.
(**)If the delivery gets the goods from different batches, the row should be split to display the amount taken from each batch.
How can I make pl/sql query/procedure to track down this?
Any help is very appreciated.
DB version is: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Thank you,
Nikita