https://inventorymanagementinv.blogspot.com/2018/07/plsql-query-for-shipment-details-oracle.html
PLSQL Query for Shipment Details - Oracle EBS R12
PLSQL Query for Shipment Details - Oracle EBS R12
Select (select vb.ORGANIZATION_NAME from apps.org_organization_definitions2 vb where vb.organization_id = tb.organization_id and rownum = 1) Inventory,
Item, transaction_type_name,
case
when transaction_type_name='Intransit Receipt' then 'Received at Inventory'
when transaction_type_name='Intransit Shipment' then 'Inventory Intransit'
when transaction_type_name='Miscellaneous issue' then 'Miscellaneous issue'
when transaction_type_name='Miscellaneous receipt' then 'Miscellaneous receipt'
when transaction_type_name='PO Receipt' then 'Receiving at PO'
when transaction_type_name='Subinventory Transfer' then 'Bin to Bin Transfer'
when transaction_type_name='WOW Miscellaneous Issue' then 'Miscellaneous issue'
when transaction_type_name='WOW Miscellaneous Receipt' then 'Miscellaneous receipt'
when transaction_type_name='WIP Issue' then 'Issuance at Work Order'
else 'not_found'
end Transaction_Description,
transaction_date,
case
when transaction_type_name='Intransit Shipment' then shipment_number
when transaction_type_name='PO Receipt' then (select po.segment1 from apps.po_headers_all po where po.po_header_id = transaction_source_id)
when transaction_type_name='WIP Issue' then (select wo.wip_entity_name from apps.wip_eam_work_orders_v wo where wo.wip_entity_id = transaction_source_id)
End Document_Number,
case
when transaction_type_name='WIP Issue' then (select wo.asset_number from apps.wip_eam_work_orders_v wo where wo.wip_entity_id = transaction_source_id)
End Vehicle_Number,
case
when transaction_type_name='WIP Issue' then
(select item.description from apps.wip_eam_work_orders_v wo, (select * from apps.mtl_system_items_b where organization_id = 82) item where
wo.wip_entity_id = transaction_source_id and wo.primary_item_id = item.inventory_item_id)
End Activity,
Quantity, Unit_Value, AVG_UNIT_COST
from (
--Reviced
select msi.description Item,
mtt.transaction_type_name,
mmt.transaction_date,
ltrim(ood.organization_name, 'WOW') Transfer_Org,
sum(mmt.transaction_quantity) Quantity,
sum(nvl(mmt.transaction_quantity, 0) * nvl(mmt.actual_cost, 0)) Unit_Value,
case when sum(mmt.transaction_quantity) = 0 then 0
else (sum(nvl(mmt.transaction_quantity,0)*nvl(mmt.actual_cost,0))/sum(mmt.transaction_quantity)) End AVG_UNIT_COST,
mmt.organization_id,
mmt.shipment_number,
mmt.transaction_source_id
from apps.mtl_material_transactions mmt,
apps.mtl_system_items_b msi,
apps.mtl_transaction_types mtt,
apps.org_organization_definitions ood
where
--mmt.organization_id = :V_Org_Id
--and
mmt.transfer_organization_id = ood.organization_id(+)
and mmt.inventory_item_id = msi.inventory_item_id
and mmt.organization_id = msi.organization_id
and mmt.transaction_quantity > 0
--and trunc(mmt.transaction_date) between '01-Jun-2018' and '23-SEP-2018'-- '01-OCT-2014' and '31-OCT-2014'
--and msi.segment1 = 'SCM-10000143' --'SCM-10000128'
and msi.segment1 <> 'WOW-Veh'
and msi.description not like '%SIM%'
--and msi.inventory_item_id in (select * from WOW_units)
and mmt.transaction_type_id = mtt.transaction_type_id
and mmt.transaction_type_id = mtt.transaction_type_id
and mtt.transaction_type_name = 'PO Receipt'
group by msi.description,
mtt.transaction_type_name,
ood.organization_name,mmt.organization_id,
mmt.shipment_number,
mmt.transaction_source_id,mmt.transaction_date
--order by mtt.transaction_type_name
) TB
Where (select vb.ORGANIZATION_NAME from apps.org_organization_definitions2 vb where vb.organization_id = tb.organization_id and rownum = 1) Like 'WOW Supply Chain%'
