Lot wise Inventory Query

select 'Inventory' col_Type,
        p.* , l.creation_date ,l.ORIGINATION_DATE,
 (select min(creation_Date) from mtl_lot_numbers where lot_number=l.lot_number
and organization_id in (select organization_id from org_organization_definitions where operating_unit=:P_ORG_ID)) rec_date,
NVL(round(to_date(:P_TO_DATE)-l.creation_DATE),0) Days_on_hand,
trunc(l.EXPIRATION_DATE) expiry_date,
trunc((l.EXPIRATION_DATE) -180 ) use_by_date
from
(select a.ORGANIZATION_ID,
c.ORGANIZATION_CODE,
c.ORGANIZATION_NAME,
a.INVENTORY_ITEM_ID,
sum(a.PRIMARY_QUANTITY) qty,
a.LOT_NUMBER,
--mmt.TRANSACTION_UOM,
b.PRIMARY_UOM_CODE TRANSACTION_UOM,
--a.ORIGINATION_DATE,
b.segment1 || '-' || b.segment2 ITEM,
b.description,
mmt.subinventory_code
from
MTL_TRANSACTION_LOT_VAL_V a,
MTL_system_items b, ORG_ORganization_definitions c,
mtl_material_transactions mmt
--where a.Organization_ID in (145, 146, 154, 147, 148, 149, 150, 151, 155, 153, 152, 182, 222, 242, 342, 362, 402, 422, 442, 462, 662, 831, 832) --DKCLLC
--where a.Organization_ID in (157, 158, 159, 160, 161, 162, 163, 164, 165, 202, 223, 322, 323) -- DKSCLLC
--where a.Organization_ID in (284, 291, 285, 289, 286, 290, 288, 302, 303, 382, 287, 542, 582, 602, 622, 642, 682, 702, 791) -- DKSG
--where a.Organization_ID in (506, 505, 507, 522, 562, 772) -- DKMY
where  TRUNC(a.Transaction_date) <= :P_TO_DATE
and a.inventory_item_id = b.Inventory_item_id
and a.ORGANIZATION_ID = b.ORGANIZATION_ID
and a.ORGANIZATION_ID = c.ORGANIZATION_ID
and a.transaction_id = mmt.transaction_id
   AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND c.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, c.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, c.ORGANIZATION_CODE )
   AND c.operating_unit = :P_ORG_ID    
group by
a.ORGANIZATION_ID,
c.ORGANIZATION_CODE,
c.ORGANIZATION_NAME,
a.INVENTORY_ITEM_ID,
a.LOT_NUMBER,
b.PRIMARY_UOM_CODE,
b.segment1,
mmt.subinventory_code,
b.segment2,
b.description
order by
a.ORGANIZATION_ID,
a.INVENTORY_ITEM_ID,
a.LOT_NUMBER
--a.ORIGINATION_DATE
asc) p,
mtl_lot_numbers l
where l.lot_number=p.lot_number
--l.inventory_item_id = p.Inventory_item_id
and p.ORGANIZATION_ID = l.ORGANIZATION_ID
--and l.LOT_NUMBER=p.LOT_NUMBER
and l.creation_date = (select min(creation_Date) from mtl_lot_numbers where lot_number=l.lot_number and organization_id=l.ORGANIZATION_ID)
and p.qty <>0
union all
select 'Inventory' col_Type , p.* ,
null creation_date,null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
null expire_date,
null use_by_date
from
(select
mmt.ORGANIZATION_ID,
c.ORGANIZATION_CODE,
c.ORGANIZATION_NAME,
mmt.INVENTORY_ITEM_ID,
sum(mmt.PRIMARY_QUANTITY) qty,
a.LOT_NUMBER,
--mmt.TRANSACTION_UOM,
b.PRIMARY_UOM_CODE TRANSACTION_UOM,
--a.ORIGINATION_DATE,
b.segment1 || '-' || b.segment2 ITEM,
b.description ,
null subinventory_code
from
MTL_TRANSACTION_LOT_VAL_V a,
MTL_system_items b, ORG_ORganization_definitions c,
mtl_material_transactions mmt
--where a.Organization_ID in (145, 146, 154, 147, 148, 149, 150, 151, 155, 153, 152, 182, 222, 242, 342, 362, 402, 422, 442, 462, 662, 831, 832) --DKCLLC
--where a.Organization_ID in (157, 158, 159, 160, 161, 162, 163, 164, 165, 202, 223, 322, 323) -- DKSCLLC
--where a.Organization_ID in (284, 291, 285, 289, 286, 290, 288, 302, 303, 382, 287, 542, 582, 602, 622, 642, 682, 702, 791) -- DKSG
--where a.Organization_ID in (506, 505, 507, 522, 562, 772) -- DKMY
where  TRUNC(mmt.Transaction_date) <= :P_TO_DATE
and mmt.inventory_item_id = b.Inventory_item_id
and mmt.ORGANIZATION_ID = b.ORGANIZATION_ID
and mmt.ORGANIZATION_ID = c.ORGANIZATION_ID
and mmt.transaction_id = a.transaction_id(+)
--and mmt.organization_id =702
and mmt.transaction_type_id not in ( 10008,76,100002,59,60,65)
   AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND c.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, c.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, c.ORGANIZATION_CODE )
   AND c.operating_unit in (283,482)-- :P_ORG_ID
AND c.operating_unit = :p_org_id
   group by
mmt.ORGANIZATION_ID,
c.ORGANIZATION_CODE,
c.ORGANIZATION_NAME,
mmt.INVENTORY_ITEM_ID,
a.LOT_NUMBER,
trunc (a.EXPIRATION_DATE),
b.PRIMARY_UOM_CODE,
b.segment1,
b.segment2,
b.description ) p
  where p.qty<>0
   and p.lot_number is null  
union all
Select 'Intransit' col_Type, org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
null subinventory_code,
null creation_date,
null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
trunc (lot.EXPIRATION_DATE) expire_date,
trunc((lot.EXPIRATION_DATE) -180 ) use_by_date
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where  ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id (+) =rsl.shipment_line_id
and rcv.shipment_header_id (+) =rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is  null
and trunc(ve.shipped_date) <= :P_TO_DATE
  AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID  
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
trunc (lot.EXPIRATION_DATE) ,
trunc((lot.EXPIRATION_DATE) -180 ),
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description
union all
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
null subinventory_code,
null creation_date,
null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
trunc (lot.EXPIRATION_DATE) expire_date,
trunc((lot.EXPIRATION_DATE) -180 ) use_by_date
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and rcv.shipment_line_id=rsl.shipment_line_id
and rcv.shipment_header_id=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
and rcv.TRANSACTION_TYPE='DELIVER' and trunc(rcv.TRANSACTION_DATE) > :P_TO_DATE and trunc(ve.shipped_date) <= :P_TO_DATE
 AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
trunc (lot.EXPIRATION_DATE) ,
trunc((lot.EXPIRATION_DATE) -180 ),
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description
UNION  ALL
Select 'Intransit' col_Type , org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
null subinventory_code,
null creation_date,
null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
trunc (lot.EXPIRATION_DATE) expire_date,
trunc((lot.EXPIRATION_DATE) -180 ) use_by_date
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS NULL
AND trunc(ve.shipped_date) <= :P_TO_DATE
 AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
trunc (lot.EXPIRATION_DATE) ,
trunc((lot.EXPIRATION_DATE) -180 ),
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description
union all
Select 'Intransit' col_Type, org.organization_id,org.organization_code,org.organization_name,
b.inventory_item_id,sum(rsl.QUANTITY_SHIPPED) qty,lot.LOT_NUMBER,'KG' Transaction_UOM, b.segment1||'-'||b.segment2 Item,b.description,
null subinventory_code,
null creation_date,
null ORIGINATION_DATE,
null rec_date,
0 days_on_hand,
trunc (lot.EXPIRATION_DATE) expire_date,
trunc((lot.EXPIRATION_DATE) -180 ) use_by_date
from rcv_msh_v ve,
rcv_shipment_lines  rsl ,
mtl_system_items b,
rcv_transactions rcv,
mtl_transaction_lot_val_v lot,
org_organization_definitions org
where ve.shipment_headeR_id=rsl.shipment_headeR_id
and org.organization_id=ve.SHIP_TO_ORG_ID
and rsl.mmt_transaction_id=lot.transaction_id
and b.inventory_item_id=rsl.item_id
and rcv.shipment_line_id (+)=rsl.shipment_line_id
and rcv.shipment_header_id(+)=rsl.shipment_header_id
and rsl.FROM_ORGANIZATION_ID=b.organization_id
and RECEIPT_NUM is not null
AND rcv.TRANSACTION_DATE IS not NULL
and rcv.shipment_line_id NOT IN (
                                    SELECT shipment_line_id
                                      FROM rcv_transactions rcv1, rcv_shipment_headers rsh1
                                     WHERE rcv1.shipment_header_id = rsh1.shipment_header_id
                                       AND rcv1.transaction_type IN ('ACCEPT', 'DELIVER','RETURN TO VENDOR'))
AND trunc(ve.shipped_date) <= :P_TO_DATE
 AND b.segment1||'-'||b.segment2 BETWEEN NVL (:p_from_item_num, b.segment1||'-'||b.segment2 )
                    AND NVL (:p_to_item_num, b.segment1||'-'||b.segment2 )
   AND ORG.ORGANIZATION_CODE BETWEEN NVL (:p_from_inv_org, ORG.ORGANIZATION_CODE)
                             AND NVL (:p_to_inv_org, ORG.ORGANIZATION_CODE )
   AND ORG.operating_unit = :P_ORG_ID
group by org.organization_id,org.organization_code,
org.organization_name,
b.inventory_item_id,
trunc (lot.EXPIRATION_DATE),
trunc((lot.EXPIRATION_DATE) -180 ),
lot.LOT_NUMBER, b.segment1||'-'||b.segment2,
b.description
order by 3,9,7