SELECT ITEM ITEM
,ITEM_DESC ITEM_DESC
,ITEM_TYPE ITEM_TYPE
,UOM UOM
,REQ_QTY REQ_QTY
,ON_HAND ON_HAND
,INTRANSIT INTRANSIT
,IN_RECEIVING IN_RECEIVING
,NVL((ON_HAND + INTRANSIT + IN_RECEIVING),0) TOTAL_STOCK
,case when req_qty > NVL((ON_HAND + INTRANSIT + IN_RECEIVING),0)
then req_qty - NVL((ON_HAND + INTRANSIT + IN_RECEIVING),0)
else 0 end SHORT_QTY
,case when req_qty > NVL((ON_HAND + INTRANSIT + IN_RECEIVING),0)
then '*'
else ' ' end HIGHLIGHT
FROM(
SELECT ITEM
,ITEM_ID
,ITEM_DESC
,ITEM_TYPE
,UOM
,(SELECT ABS(ROUND(NVL(sum( CASE WHEN --order_type=5 AND
organization_id= :P_ORG
AND TRUNC(new_due_date) BETWEEN NVL(fnd_date.canonical_to_date(:P_DATE_FROM),SYSDATE)
AND NVL(fnd_date.canonical_to_date(:P_DATE_TO),SYSDATE) THEN QUANTITY ELSE 0 END),0),3))
FROM msc_orders_v mov3
WHERE 1=1
AND mov3.source_table='MSC_DEMANDS'
AND mov3.compile_designator = AA.compile_designator --:P_PLAN
AND mov3.inventory_item_id = AA.inventory_item_id -- 1012
AND mov3.category_set_id = AA.category_set_id -- 1001
AND TRUNC(mov3.new_due_date) BETWEEN NVL(fnd_date.canonical_to_date(:P_DATE_FROM),SYSDATE)
AND NVL(fnd_date.canonical_to_date(:P_DATE_TO),SYSDATE) ) REQ_QTY
-- ,ROUND(NVL(sum( CASE WHEN order_type=5 AND organization_id= :P_ORG
-- AND TRUNC(new_due_date) BETWEEN NVL(fnd_date.canonical_to_date(:P_DATE_FROM),SYSDATE)
-- AND NVL(fnd_date.canonical_to_date(:P_DATE_TO),SYSDATE) THEN ALLOCATED_QUANTITY ELSE 0 END),0),3)
-- REQ_QTY
,ROUND(NVL(SUM(CASE WHEN ORDER_TYPE=18 THEN (ALLOCATED_QUANTITY) ELSE 0 END ),0),3)
ON_HAND
,ROUND(NVL(sum( CASE WHEN order_type IN (1,2)
AND TRUNC(new_due_date) BETWEEN NVL(fnd_date.canonical_to_date(:P_DATE_FROM),SYSDATE)
AND NVL(fnd_date.canonical_to_date(:P_DATE_TO),SYSDATE) THEN ALLOCATED_QUANTITY ELSE 0 END),0),3)
INTRANSIT
,ROUND(NVL(sum( CASE WHEN order_type IN (8,12)
AND TRUNC(new_due_date) BETWEEN NVL(fnd_date.canonical_to_date(:P_DATE_FROM),SYSDATE)
AND NVL(fnd_date.canonical_to_date(:P_DATE_TO),SYSDATE) THEN ALLOCATED_QUANTITY ELSE 0 END),0),3)
IN_RECEIVING
FROM(
SELECT msi.item_name ITEM
,msi.inventory_item_id ITEM_ID
,msi.description ITEM_DESC
,(SELECT SUBSTR(CATEGORY_NAME,instr(CATEGORY_NAME,'.',1,1)+1,instr(CATEGORY_NAME,'.',1,2)-instr(CATEGORY_NAME,'.',1,1)-1)
FROM msc_item_categories mic
WHERE mic.organization_id=mov.organization_id
AND mic.inventory_item_id=mov.inventory_item_id
and category_set_id!=1001) ITEM_TYPE
,mov.uom_code UOM
,mfp.allocated_quantity ALLOCATED_QUANTITY
,mov.order_type ORDER_TYPE
,mov.new_due_date NEW_DUE_DATE
,mov.using_assembly_item_id
,mov.organization_id organization_id
,mov.compile_designator
,mov.inventory_item_id
,mov.category_set_id
FROM msc_orders_v mov,
msc_full_pegging mfp,
msc_system_items msi
WHERE mov.transaction_id=mfp.transaction_id
AND mov.plan_id=mfp.plan_id
AND mov.inventory_item_id=mfp.inventory_item_id
AND mov.organization_id=mfp.organization_id
AND MOV.sr_instance_id=mfp.sr_instance_id
AND mov.inventory_item_id=msi.inventory_item_id
AND mov.organization_id=msi.organization_id
AND mov.sr_instance_id = msi.sr_instance_id
AND mov.plan_id=msi.plan_id
AND MOV.planning_make_buy_code=2
AND mov.compile_designator = :P_PLAN
AND mov.category_set_id=1001
-- AND mov.INVENTORY_ITEM_ID = 1012
-- AND mov.organization_id=:P_ORG
AND mov.source_table='MSC_SUPPLIES'
AND mov.inventory_item_id IN(
SELECT distinct inventory_item_id
FROM msc_orders_v mov1
WHERE 1=1
and mov1.using_assembly_item_id is not null
AND planning_make_buy_code =2
AND mov1.compile_designator = :P_PLAN
AND mov1.category_set_id=1001
AND EXISTS(
SELECT 1
FROM msc_orders_v mov2
WHERE mov2.inventory_item_id=mov1.using_assembly_item_id
AND mov2.organization_id=mov1.organization_id
AND mov2.planning_make_buy_code=1
AND mov2.plan_id=mov1.plan_id
AND mov2.compile_designator = :P_PLAN
AND mov2.organization_id= :P_ORG
AND MOV2.using_assembly_item_id IS NOT NULL
)
)
)AA
GROUP BY
ITEM
,ITEM_ID
,ITEM_DESC
,ITEM_TYPE
,UOM
,compile_designator
,inventory_item_id
,category_set_id
)
ORDER BY ITEM
