Lot Controlled Material (Link between po_headers_all, pa_projects_all, mtl_system_items_b, mtl_material_transactions,mtl_transaction_lot_val_v)

select pha.segment1 ENVS_PO_NUM,
pla.line_num,
msi.segment1 ENVS_ITEM,
msi.description ENVS_ITEM_DESC,
msi.primary_uom_code ENVS_UOM,
msi.item_type BULK_ISSUE,
rsh.receipt_num,
rsh.creation_date ENVS_RECEIPT_DATE,
to_char(sysdate,'MON-RR') ENVS_ROTATION_DATE,
--rsh.shipment_header_id,
--rsl.shipment_line_id,
--rt.transaction_id,
--mmt.transaction_id,
mtlv.lot_number ENVS_LOT_NUM,
mtlv.transaction_quantity ENVS_Quantity,
mtlv.primary_quantity,
ppa.segment1 ENVS_PROJECT,
pt.task_name ENVS_TASK,
pv.vendor_name ENVS_SUPPLIER_NAME,
mir.revision ENVS_REVISION
from po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda,
pa_projects_all ppa,
pa_tasks pt,
ap_suppliers pv,
mtl_system_items_b msi,
mtl_item_revisions_vl mir,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
rcv_transactions rt,
mtl_material_transactions mmt,
mtl_transaction_lot_val_v mtlv,
fnd_user fu
where pha.po_header_id =pla.po_header_id
and pha.org_id =pla.org_id
and pha.po_header_id =plla.po_header_id
and pla.po_line_id =plla.po_line_id
and pha.org_id =plla.org_id
and pla.po_line_id = pda.po_line_id
and pha.po_header_id = pda.po_header_id
and pda.project_id = ppa.project_id(+)
and pda.task_id = pt.task_id (+)
and pha.vendor_id = pv.vendor_id
and pla.item_id = msi.inventory_item_id
and pha.po_header_id = rsl.po_header_id
and msi.organization_id = rsh.ship_to_org_id
and msi.organization_id = ship_to_organization_id
and pla.po_line_id = rsl.po_line_id
and rsl.shipment_header_id = rsh.shipment_header_id
and rt.po_header_id= rsl.po_header_id
and rt.po_line_id = rsl.po_line_id
and rt.shipment_header_id= rsh.shipment_header_id
and rt.shipment_line_id =rsl.shipment_line_id
and rt.organization_id = rsh.ship_to_org_id
and rt.transaction_id = mmt.rcv_transaction_id
and mmt.transaction_id  = mtlv.transaction_id
and msi.inventory_item_id = mtlv.inventory_item_id
and msi.organization_id = mtlv.organization_id
and msi.inventory_item_id = mir.inventory_item_id
and msi.organization_id = mir.organization_id
&lp_recpt_num
--and ppa.segment1 is not null
--and pha.segment1 ='700034534'
--and rsh.receipt_num in ('1010835471','1010833388')
and msi.SERIAL_NUMBER_CONTROL_CODE =1
--and mmt.transaction_id=17867826
and rsh.created_by = fu.user_id
and mir.IMPLEMENTATION_DATE  = (select max(mirv.IMPLEMENTATION_DATE) from  mtl_item_revisions_vl mirv where mirv.INVENTORY_ITEM_ID = mir.INVENTORY_ITEM_ID
                                and  mir.organization_id =mirv.organization_id )
order by rsh.receipt_num ,mtlv.lot_number;