Oracle apps technical tutorial

SQL Query to get PO matched Invoices and GRN number in Oracle Apps

SQL Query to get PO matched Invoices and GRN number in Oracle Apps

In this query, you will get the information about how to link AP module tables with PO and Receipt.

And this query is mainly to get PO matched Invoices and GRN number. If you will not put distinct in the query then data will come as distribution level.

 

select distinct aia.invoice_num,
 aia.invoice_date,
 ( SELECT concatenated_segments
 FROM gl_code_combinations_kfv
 WHERE code_combination_id = aida.dist_code_combination_id
 ) dist_gl_code,
 aps.vendor_name,
 aps.segment1 vendor_no,
 apss.vendor_site_code,
 poh.segment1 po_number,
 rsh.receipt_num GRN_Number, 
 (
 SELECT concatenated_segments
 FROM gl_code_combinations_kfv
 WHERE code_combination_id = pda.code_combination_id
 ) dist_po_code,
 aia.doc_sequence_value voucher_no,
 (
 SELECT segment1||'.'||segment2
 FROM mtl_system_items_b
 WHERE inventory_item_id = pol.item_id
 AND organization_id = pll.SHIP_TO_ORGANIZATION_ID
 ) item_code,
 aida.accounting_date
 /* ,(
 select SERVICE_TAX_REGNO
 FROM JAI_CMN_VENDOR_SITES
 WHERE vendor_id = aia.vendor_id
 AND vendor_site_id = aia.vendor_site_id
 ) st_reg_no,
 (
 SELECT pan_no
 FROM JAI_AP_TDS_VENDOR_HDRS
 where vendor_id = aia.vendor_id
 AND vendor_site_id = aia.vendor_site_id
 ) pan_no,
 -- jtl.UNROUND_TAXABLE_AMT_TRX_CURR,
 -- jtl. */
from ap_invoices_All aia,
 ap_invoice_lines_all aila,
 ap_suppliers aps,
 ap_supplier_sites_all apss,
 ap_invoice_distributions_all aida,
 po_headers_all poh,
 po_lines_all pol,
 po_line_locations_All pll,
 po_distributions_All pda,
 rcv_transactions rt,
 rcv_shipment_headers rsh
-- , jai_tax_lines jtl
where aia.invoice_id = aila.invoice_id
AND aila.org_id = aia.org_id
AND aila.po_header_id = poh.po_header_id
AND aila.po_line_id = pol.po_line_id
AND aila.invoice_id =aida.invoice_id
AND aia.vendor_id = aps.vendor_id
AND aia.vendor_site_id = apss.vendor_site_id
AND aia.vendor_id = apss.vendor_id
AND apss.org_id = aia.org_id
AND aida.invoice_line_number = aila.line_number
and aida.org_id= aila.org_id
AND poh.po_header_id = pol.po_header_id
AND pll.po_header_id = poh.po_header_id
AND pll.po_line_id = pol.po_line_id
AND pll.line_location_id = pda.line_location_id
AND pll.po_header_id = pda.po_header_id
AND pll.po_line_id = pda.po_line_id
AND pol.org_id = pda.org_id
AND poh.org_id = pll.org_id
AND aila.rcv_transaction_id = rt.transaction_id
AND rt.shipment_header_id = rsh.shipment_header_id

 

Hope you find it useful, if any query please put it into comment box.

Leave a Reply