Oracle R12 : Query for Trading Invoice link to Purchase Order

Match Receipts - Trading:

There is a case where the company would involve in trading of its finished goods.That is, it may purchase the item from another supplier/manufacturer and sell it to customer. This can be done in Oracle Apps in India Local Order Management > Shipping > Match Receipts - Trading

SELECT rt.primary_quantity, pol.unit_price, rt.transaction_id,
       jrt.excise_invoice_no, msi.description, jrlt.tax_type, jrlt.tax_amount,
       aps.vendor_name, apss.address_line1, apss.address_line2,
       apss.address_line3, apss.city || ' - ' || apss.zip city, apss.state,
       poll.unit_meas_lookup_code uom, poh.segment1 po_number,
       TO_CHAR (poh.creation_date, 'DD-MON-RRRR') po_date, poh.po_header_id,
       (rt.primary_quantity * pol.unit_price) po_amount,
       apt.NAME payment_term, jcvs.excise_duty_zone range_add,
       jcvs.excise_duty_reg_no ce_reg_no, jcvs.excise_duty_range RANGE,
       jcvs.excise_duty_division division,
       jcvs.excise_duty_comm commisionerate, msi.inventory_item_id,
       msi.organization_id
  FROM jai_cmn_rg_23d_trxs jcrt,
       jai_cmn_match_receipts jcmr,

       rcv_transactions rt,
       po_line_locations_all poll,
       po_lines_all pol,
       po_headers_all poh,
       jai_rcv_transactions jrt,
       mtl_system_items_b msi,
       jai_rcv_line_taxes jrlt,
       ap_suppliers aps,
       ap_supplier_sites_all apss,
       jai_cmn_vendor_sites jcvs,
       ap_terms apt
 WHERE jcrt.register_id = jcmr.receipt_id
   AND jcrt.reference_line_id = rt.shipment_line_id
   AND rt.transaction_id = jcrt.receipt_ref
   AND rt.po_line_location_id = poll.line_location_id
   AND poll.po_line_id = pol.po_line_id
   AND pol.po_header_id = poh.po_header_id
   AND jrt.transaction_id = rt.transaction_id
   AND jrt.inventory_item_id = msi.inventory_item_id
   AND jrt.organization_id = msi.organization_id
   AND jrlt.transaction_id = rt.transaction_id
   AND rt.vendor_id = aps.vendor_id
   AND rt.vendor_site_id = apss.vendor_site_id
   AND jcvs.vendor_site_id(+) = apss.vendor_site_id
   AND poh.terms_id = apt.term_id(+)
   AND jcmr.ref_line_id = :delivery_detail_id

The jai_cmn_match_receipts table is key, as it contains the link between the delivery and purchase receipt.