Overview: This SQL query is use to get invoice’s supplier tax registration number including invoice number, invoice currency code, invoice date, tax amount data in Oracle R12 Application.
SELECT aia.invoice_num, aia.invoice_date, aia.invoice_id, aia.gl_date, aia.invoice_currency_code, ail.period_name, /* not required consider amount from tax table -- CASE -- WHEN aia.invoice_currency_code = 'AED' THEN ROUND (ail.amount, 2) -- ELSE ROUND ( (ail.amount) * aia.exchange_rate, 2) -- END */ ROUND( (zlv.unrounded_taxable_amt *NVL(zlv.currency_conversion_rate,1) ),2) amount, pv.vendor_name, pv.segment1 vendor_num, pvs.vendor_site_code, aia.description, 1 quantity, zlv.tax_rate_code, ap_invoices_pkg.get_posting_status (aia.invoice_id) posting_flag, -- (modon_vat_util.supplier_site_vat_reg_number (pv.party_id))supplier_trn, (SELECT VAT_REGISTRATION_NUM FROM AP_SUPPLIERS WHERE VENDOR_ID = pv.vendor_id)supplier_trn, CASE WHEN aia.invoice_currency_code = 'AED' THEN ROUND (zlv.tax_amt, 2) ELSE ROUND ( (zlv.tax_amt) * aia.exchange_rate, 2) END tax_amt FROM ap_invoices_all aia, ap_invoice_lines_all ail, po_vendors pv, po_vendor_sites_all pvs, zx_lines_v zlv WHERE aia.invoice_id = ail.invoice_id AND ail.line_type_lookup_code NOT IN ('TAX') AND aia.invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT' ) AND aia.vendor_id = pvs.vendor_id AND pv.vendor_id = pvs.vendor_id AND aia.vendor_site_id = pvs.vendor_site_id AND aia.invoice_id = zlv.trx_id AND zlv.TAX_RATE_CODE = 'STANDARD 5%' AND zlv.self_assessed_flag <> 'Y' AND zlv.cancel_flag = 'N' AND ap_invoices_pkg.get_posting_status (aia.invoice_id) <> 'N' AND ail.cancelled_flag = 'N' and zlv.TAX_AMT_INCLUDED_FLAG='N' and nvl(pvs.COUNTRY,'AE')='AE' -- AND NOT EXISTS -- (SELECT 'Y' -- FROM po_headers_all ph -- WHERE ph.PO_HEADER_ID = ail.PO_HEADER_ID -- AND ph.ATTRIBUTE4 = 'DPM') AND not EXISTS (SELECT 'Y' FROM AP_INVOICE_PAYMENTS_ALL aip, ap_checks_all ac WHERE aip.INVOICE_ID = aia.INVOICE_ID AND aip.check_id = ac.check_id AND (aip.BANK_ACCOUNT_NUM = '6204420749' OR ac.BANK_ACCOUNT_NAME = 'Current Account -DPM 6204420749')) AND ail.line_number = NVL (zlv.trx_line_number, ail.line_number) and aia.org_ID=:P_ORG_ID AND TRUNC (aia.gl_date) BETWEEN NVL ('01-'||:p_from_date, TRUNC (aia.gl_date)) AND NVL (LAST_DAY('01-'||:p_to_date), TRUNC (aia.gl_date)) ORDER BY aia.gl_date;