Fetching Invoice Details in Oracle Apps

Invoice Details

SELECT hou.name Operating_Unit,
       aps.vendor_name,
       assa.vendor_site_code,
       aia.invoice_id,
       aia.vendor_id,
       aia.vendor_site_id,
       aia.invoice_date,
       aia.invoice_num,
       aia.invoice_amount,
       fct.name invoice_currency,
       aia.gl_date,
       at.name terms,
       aia.payment_status_flag payment,
       aia.invoice_currency_code,
       aia.payment_method_code
  /*DECODE(APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(aia.invoice_id, aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code),
          'NEVER APPROVED', 'Never Validated',
          'NEEDS REAPPROVAL', 'Needs Revalidation',
          'CANCELLED', 'Cancelled',
          'Validated') INVOICE_STATUS,  decode(AP_INVOICES_PKG.GET_POSTING_STATUS(invoice_id),'S','Selected','P','Partial','N','Not Posted','Y','Posted') Accounted,
  AP_INVOICES_PKG.GET_APPROVAL_STATUS(
        aia.INVOICE_ID
       ,aia.INVOICE_AMOUNT
       ,aia.PAYMENT_STATUS_FLAG
       ,aia.INVOICE_TYPE_LOOKUP_CODE
       ) Approval_Status*/
  FROM ap_invoices_all aia,
       hr_operating_units hou,
       ap_suppliers aps,
       ap_supplier_sites_all assa,
       fnd_currencies_tl fct,
       ap_terms at
 WHERE     hou.organization_id = aia.org_id
       AND aps.vendor_id = assa.vendor_id
       AND aps.vendor_id = aia.vendor_id
       AND at.term_id = aia.terms_id
       AND assa.vendor_site_id = aia.vendor_site_id
       AND fct.currency_code = aia.invoice_currency_code
       AND fct.language = USERENV ('LANG')
       AND invoice_num = NVL (:p_invoice_num, invoice_num)
       AND payment_method_code = NVL (:p_payment_method, payment_method_code)
       AND hou.name = NVL (:p_ou_name, hou.name)
       AND aia.invoice_date BETWEEN NVL (:p_inv_from_date, invoice_date)
                                AND NVL (:p_inv_to_date, invoice_date)
       AND aia.gl_date BETWEEN NVL (:p_gl_from_date, gl_date)
                           AND NVL (:p_gl_to_date, gl_date)
       AND AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.INVOICE_ID,
                                                aia.INVOICE_AMOUNT,
                                                aia.PAYMENT_STATUS_FLAG,
                                                aia.INVOICE_TYPE_LOOKUP_CODE) =
              NVL (:p_approval_status,
                   AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                      aia.INVOICE_ID,
                      aia.INVOICE_AMOUNT,
                      aia.PAYMENT_STATUS_FLAG,
                      aia.INVOICE_TYPE_LOOKUP_CODE))
       AND AP_INVOICES_PKG.GET_APPROVAL_STATUS (aia.invoice_id,
                                                aia.invoice_amount,
                                                aia.payment_status_flag,
                                                aia.invoice_type_lookup_code) =
              NVL (
                 DECODE (:p_invoice_status,
                         'Never Validated', 'NEVER APPROVED',
                         'Needs Revalidation', 'NEEDS REAPPROVAL',
                         'Cancelled', 'CANCELLED',
                         'Validated', 'APPROVED'),
                 AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                    aia.invoice_id,
                    aia.invoice_amount,
                    aia.payment_status_flag,
                    aia.invoice_type_lookup_code))
       AND AP_INVOICES_PKG.GET_POSTING_STATUS (invoice_id) =
              NVL (
                 DECODE (:p_accounted,
                         'Selected', 'S',
                         'Partial', 'P',
                         'Not Posted', 'N',
                         'Posted', 'P',
                         'Yes', 'P',
                         'No', 'N'),
                 AP_INVOICES_PKG.GET_POSTING_STATUS (invoice_id))

--//To find the Errors in AP Invoice Interface

select count(*),REJECT_LOOKUP_CODE
from ap_interface_rejections
where parent_id in
(select a.invoice_id
from ap_invoices_interface a
where  status = 'REJECTED'
and request_id = 38833490)
group by REJECT_LOOKUP_CODE;

--// Payment Method Lookup Code

 SELECT   ieppm.payment_method_code
       FROM ap_supplier_sites_all assa,
            ap_suppliers sup,
            iby_external_payees_all iepa,
            iby_ext_party_pmt_mthds ieppm
      WHERE sup.vendor_id = assa.vendor_id
        AND assa.pay_site_flag = 'Y'
        AND assa.vendor_site_id = iepa.supplier_site_id
        AND iepa.ext_payee_id = ieppm.ext_pmt_party_id
        AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)
            )
        AND assa.vendor_site_id = :p_vendor_site_id
        AND ieppm.primary_flag = 'Y'
   ORDER BY sup.vendor_name, assa.vendor_site_code