Query to get Invoice Supplier Tax Registration Number in Oracle Apps R12 - Orapoint Orapoint.com

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;