--- In Oracle apps Ebs R12, Factory Short Item List Report ---
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
,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
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.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
)
)
)
GROUP BY
ITEM
,ITEM_ID
,ITEM_DESC
,ITEM_TYPE
,UOM
)
ORDER BY ITEM
