Bill of Entry (BOE) query from PO

SELECT DISTINCT

       boe_hdr.boe_id,

       boe_hdr.import_sr_num boe_number,

       boe_hdr.boe_date,

       rsh.creation_date receipt_creation_date,

       boe_hdr.boe_type,

       boe_hdr.boe_amount,

       boe_hdr.invoice_num,

       boe_hdr.port_name,

       boe_hdr.country_origin,

       boe_match.transaction_id receive_transaction_id,

       NVL(rsh.receipt_num,'Receipt pending or not applicable') receipt_number,

       pha.segment1 po_number,

       msi.segment1 item_code,

       msi.description item_description,

       mln.lot_number,

       mln.primary_quantity lot_quantity

FROM   ja.jai_cmn_boe_hdrs_all boe_hdr,

       ja.jai_cmn_boe_matchings boe_match,

       rcv_transactions rt_receive,

       rcv_transactions rt_accept,

       rcv_transactions rt_deliver,

       rcv_shipment_headers rsh,

       rcv_shipment_lines rsl,

       po_headers_all pha,

       mtl_material_transactions mmt,

       mtl_transaction_lot_numbers mln,

       mtl_system_items_b msi

WHERE  boe_hdr.boe_id = boe_match.boe_id(+)

AND    boe_match.transaction_id = rt_receive.transaction_id(+)

AND    rt_accept.parent_transaction_id(+) = rt_receive.transaction_id

AND    rt_accept.transaction_type(+) = 'ACCEPT'

AND    rt_deliver.parent_transaction_id(+) = rt_accept.transaction_id

AND    rt_deliver.transaction_type(+) = 'DELIVER'

AND    mmt.rcv_transaction_id(+) = rt_deliver.transaction_id

AND    mln.transaction_id(+) = mmt.transaction_id

AND    rt_receive.shipment_header_id = rsh.shipment_header_id(+)

AND    rt_receive.shipment_line_id = rsl.shipment_line_id(+)

AND    rt_receive.po_header_id = pha.po_header_id(+)

AND    rsl.item_id = msi.inventory_item_id(+)

AND    rt_receive.organization_id = msi.organization_id(+)

AND    boe_hdr.org_id = &ORG_ID

--and rsh.receipt_num =&RECEIPT_NUM

ORDER BY rsh.creation_date DESC,

         receipt_number,

         mln.lot_number;