Invoice Details Report

Invoice Details Report

SELECT hou.name                    Operating_Unit_Name,

       aps.vendor_name             Supplier_Name,

       assa.vendor_site_code       Supplier_Site,

       aia.vendor_site_id,

       aia.invoice_date,

       aia.invoice_num             Invoice_Number,

       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

  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))