Inventory to PO , SO(Sales Order) , WO(Work Order) Drill Down Query With Lot Numbers (Item Transaction Report)

 select 

TAB.RECEIPT_DATE TRANSACTION_DATE, 

TAB.SUPPLY_TYPE_CODE DOCUMENT_TYPE,  

TAB.DOCUMENT,

TAB.CustomerorSupplier,

TAB.TO_ORG_PRIMARY_QUANTITY QUANTITY, 

TAB.TO_ORG_PRIMARY_UOM_CODE UOM, 

TAB.onhand_qty, 

TAB.lot_number,

TAB.item,

TAB.SECONDARY_TRANSACTION_QUANTITY SECONDARY_QUANTITY,

TAB.TRANSFER_SUBINVENTORY TRANSFER_TANK,

TAB.SHIPMENT_NUMBER,

TAB.transfer_org,

TAB.RECEIPT_NUM RECEIPT

from 

(

select 

to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

poh.segment1 document,

(select hp.party_name

FROM poz_suppliers ps,

hz_parties hp

WHERE 1=1

and hp.party_id = ps.party_id

and ps.vendor_id=poh.vendor_id)CustomerorSupplier,

imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

RSH.RECEIPT_NUM

from INV_MATERIAL_TXNS imt,

     INV_TRANSACTION_LOT_NUMBERS itln,

egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

PO_LINES_ALL pla,

PO_HEADERS_ALL poh

,RCV_SHIPMENT_LINES RSL,

     RCV_SHIPMENT_HEADERS RSH,

rcv_transactions RV

where 1=1

and imt.ORGANIZATION_id=itln.ORGANIZATION_id

and imt.TRANSACTION_ID=itln.TRANSACTION_ID

and imt.INVENTORY_ITEM_ID=esi.INVENTORY_ITEM_ID

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and itst.LANGUAGE='US'

AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

 AND RSL.PO_HEADER_ID = POH.PO_HEADER_ID

 AND RSL.PO_LINE_ID = PLA.PO_LINE_ID

 AND RV.TRANSACTION_ID = IMT.RCV_TRANSACTION_ID

 AND RV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

 --AND RV.TRANSACTION_TYPE = 'DELIVER'

 AND RV.DESTINATION_TYPE_CODE = 'INVENTORY'

 and itln.TRANSACTION_SOURCE_TYPE_ID in (1)

--and poh.segment1 = '31'

and pla.ITEM_ID=imt.inventory_item_id

and pla.PO_HEADER_ID=poh.PO_HEADER_ID

and esi.ORGANIZATION_id=imt.ORGANIZATION_id

AND poh.po_header_id = imt.transaction_source_id

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               --100000004025858

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  --300000003803116

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select  to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

iso.SALES_ORDER_NUMBER document,

(select hp.party_name

         FROM HZ_PARTIES hp

         where 1=1

         AND hp.PARTY_ID=iso.SOLD_TO_PARTY_ID)CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

NULL RECEIPT_NUM

from INV_SALES_ORDERS iso,

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln

WHERE 1=1

AND iso.SALES_ORDER_ID  = imt.transaction_source_id

--and iso.SALES_ORDER_NUMBER  = '150'

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID = 2

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select  to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

wwo.WORK_ORDER_NUMBER document,

NULL CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

  esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

NULL RECEIPT_NUM

from WIE_WORK_ORDERS_B wwo,

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln

WHERE 1=1

AND wwo.WORK_ORDER_ID  = imt.TRANSACTION_SOURCE_ID

--and iso.SALES_ORDER_NUMBER  = '150'

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID = 5

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

IGD.SEGMENT1 document,

NULL CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

NULL RECEIPT_NUM

from INV_GENERIC_DISPOSITIONS  IGD,

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln

WHERE 1=1

AND IGD.DISPOSITION_ID = IMT.TRANSACTION_SOURCE_ID

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID = 6

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select  to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

NULL document,

NULL CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

NULL RECEIPT_NUM

from 

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln

WHERE 1=1

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID IN (5,13)

--and itln.TRANSACTION_SOURCE_TYPE_ID IN 13

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

UNION ALL

select  to_char(imt.TRANSACTION_DATE, 'mm/dd/yyyy') RECEIPT_DATE,

    itst.TRANSACTION_SOURCE_TYPE_NAME SUPPLY_TYPE_CODE,

( select RA_DOCUMENT_NUMBER 

           from rcv_shipment_headers rsh 

           where rt.CUSTOMER_ID = rsh.CUSTOMER_ID

           and rt.RECEIPT_ADVICE_HEADER_ID = rsh.SHIPMENT_HEADER_ID

           and rt.DESTINATION_TYPE_CODE = 'INVENTORY' 

           ) DOC_NUMBER,

   NULL CustomerorSupplier,

       imt.TRANSACTION_QUANTITY TO_ORG_PRIMARY_QUANTITY,

   imt.TRANSACTION_UOM TO_ORG_PRIMARY_UOM_CODE,

   itln.lot_number,

(select nvl(sum(a.primary_TRANSACTION_QUANTITY),0) from inv_onhand_quantities_detail a

                    where a.INVENTORY_ITEM_ID = esi.inventory_item_id

                    and a.ORGANIZATION_ID =esi.organization_id) onhand_qty,

esi.ITEM_NUMBER  item,

imt.SECONDARY_TRANSACTION_QUANTITY,

imt.TRANSFER_SUBINVENTORY,

imt.SHIPMENT_NUMBER,

(select hou.name from hr_organization_units hou where hou.ORGANIZATION_ID=imt.TRANSFER_ORGANIZATION_ID) transfer_org,

(select RECEIPT_NUM from rcv_shipment_headers where SHIPMENT_HEADER_ID = rt.SHIPMENT_HEADER_ID ) RCV_SHIP_RECEIPT

from 

     INV_MATERIAL_TXNS imt,

     egp_system_items esi,

INV_TXN_SOURCE_TYPES_TL itst,

INV_TRANSACTION_LOT_NUMBERS itln,

  rcv_transactions rt

WHERE 1=1

AND imt.inventory_item_id = esi.inventory_item_id

and imt.ORGANIZATION_ID = esi.organization_id

and itst.LANGUAGE='US'

and itln.inventory_item_id = esi.inventory_item_id

and itln.ORGANIZATION_ID = esi.ORGANIZATION_ID

and itln.TRANSACTION_SOURCE_TYPE_ID IN (12)

and imt.TRANSACTION_SOURCE_TYPE_ID=itst.TRANSACTION_SOURCE_TYPE_ID

and imt.TRANSACTION_ID = itln.TRANSACTION_ID

and rt.transaction_id  = imt.RCV_TRANSACTION_ID

AND (itst.TRANSACTION_SOURCE_TYPE_NAME IN(:P_TRX_SOURCE_TYPE) OR LEAST(:P_TRX_SOURCE_TYPE)IS NULL)

AND (imt.SUBINVENTORY_CODE IN(:P_SUBINVENTORY_CODE) OR LEAST(:P_SUBINVENTORY_CODE)IS NULL)

AND (esi.item_number IN(:p_item) OR LEAST(:p_item)IS NULL)               

and imt.ORGANIZATION_ID in (select organization_id from hr_organization_units where 1=1 and (name IN(:p_orgId) OR LEAST(:p_orgId)IS NULL))  

AND imt.TRANSACTION_DATE BETWEEN NVL(:P_FromDate,to_date('01/01/1970', 'mm/dd/yyyy')) AND NVL(:P_ToDate,to_date('01/01/4710', 'mm/dd/yyyy'))

) TAB

order by 1