I'm trying to link PART_BATCHES, IC_LOT_STATUS &
SALES_ORDER_LINE_DETAIL_QTYS to look at inventory that's about to expire
(say, in 20 days) and then grab entered Sales Orders in the system.
I'm using:
Select A.Part_code, A.Ic_Lot_Number, a.batch_date,
a.ic_last_movement_date, a.creation_date,
A.Ic_Quantity, A.Ic_qty_In_Despatch,
B.Order_Quantity
FROM ( SELECT a.part_code, a.ic_lot_number, a.batch_date,
a.ic_last_movement_date,
a.creation_date,
Sum(b.ic_quantity) as Ic_quantity,
Sum(b.ic_qty_in_despatch) as Ic_Qty_In_Despatch
FROM fin_prod.part_batches a,
fin_prod.ic_lot_status b
WHERE a.COMPANY_CODE = '1' and b.COMPANY_CODE = '1'
and a.COMPANY_CODE = b .COMPANY_CODE
and a.WAREHOUSE = 'S1'
and b.WAREHOUSE = 'S1'
and a.part_code = b.part_code
and a.ic_lot_number = b.ic_lot_number
and b.ic_quantity <> 0
and a.batch_date BETWEEN SYSDATE AND SYSDATE+20
group by a.part_code, a.ic_lot_number, a.batch_date,
a.ic_last_movement_date,
a.creation_date ) A,
(SELECT part_code, required_date, sum(order_quantity)
Order_Quantity
FROM fin_prod.sales_order_line_detail_qtys
where company_code = '1' and division = '1'
and required_date > = SYSDATE
and warehouse = 'S1'
group by part_code, required_date ) (+) B WHERE
a.part_code = b.part_code (+)
and a.batch_date > b.required_date (+)
Testing with a single item (19201) I get:
19201 19201S04-14-05 08-FEB-05 21-JAN-05 11-DEC-04
1 0 5
19201 19201S04-14-05 08-FEB-05 21-JAN-05 11-DEC-04
1 0 33
19201 19201S04-14-05 08-FEB-05 21-JAN-05 11-DEC-04
1 0 1
19201 19201S04-21-05 15-FEB-05 31-JAN-05 17-DEC-04
551 21 5
19201 19201S04-21-05 15-FEB-05 31-JAN-05 17-DEC-04
551 21 33
19201 19201S04-21-05 15-FEB-05 31-JAN-05 17-DEC-04
551 21 1
When I expected:
19201 19201S04-14-05 08-FEB-05 21-JAN-05 11-DEC-04
1 0 39
19201 19201S04-21-05 15-FEB-05 31-JAN-05 17-DEC-04
551 21
Row details from sales_order_line_detail_qtys (39) =
ORDER_NU ORDER_QUANTITY WA PART_CODE REQUIRED_
-------- -------------- -- --------------- ---------
619610 5 S1 19201 03-FEB-05
619601 13 S1 19201 04-FEB-05
619668 20 S1 19201 04-FEB-05
619703 1 S1 19201 07-FEB-05
These should all rollup on the first line since require_date <
batch_date of 2/8.
???
Any suggestions where I need something tweeked?
Oracle LazyDBA home page