In Oracle apps Ebs R12, Factory Short Item List Report

 --- 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