AP Invoice Queries

/* Invoice Details Query */

SELECT hou.name                                                              Operating_Unit_Name,

       aps.vendor_name                                                       Supplier_Name,

       assa.vendor_site_code                                                 Supplier_Site,

       --aia.invoice_id,

       --aia.vendor_id,

       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,

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

/* XLA Queries */

  SELECT DISTINCT

         api.invoice_num,

         api.invoice_id,

         apl.line_number--,xal.ae_line_num

                        ,

         xal.accounting_class_code,

         xal.ACCOUNTED_DR,

         xal.ACCOUNTED_CR,

         xah.period_name,

         (SELECT CONCATENATED_SEGMENTS

            FROM gl_code_combinations_kfv gcck

           WHERE code_combination_id = xal.code_combination_id)    account,

         --                gjl.entered_cr,

         --                gjl.entered_dr,

         --                gjl.accounted_cr,

         --                gjl.accounted_dr,

         --gjh.name              journal_name,

         --gjh.creation_date     journal_creation_date,

         gcc.segment1                              --,xdl.accounting_line_code

    FROM gl_import_references        gir,

         xla_ae_lines                xal,

         xla_ae_headers              xah,

         xla_distribution_links      xdl,

         ap_invoices_all             api,

         ap_invoice_lines_all        apl,

         ap_invoice_distributions_all apd,

         gl_code_combinations        gcc

   --xla_events xe

   --xla_transaction_entities xte

   WHERE     api.invoice_id = apl.invoice_id

         AND apl.invoice_id = apd.invoice_id

         -- and xte.application_id = 200

         --and xte.entity_code      = 'AP_INVOICES'

         --and xte.source_id_int_1  = api.invoice_id

         AND apl.line_number = apd.invoice_line_number

         AND gir.gl_sl_link_table = xal.gl_sl_link_table

         AND gir.gl_sl_link_id = xal.gl_sl_link_id

         AND xal.ae_header_id = xah.ae_header_id

         AND xdl.ae_header_id = xah.ae_header_id

         AND xdl.event_id = apd.accounting_event_id

         --and xe.event_id = xah.event_id

         AND xdl.source_distribution_id_num_1 = apd.invoice_distribution_id

         AND xdl.source_distribution_type = 'AP_INV_DIST'

         -- AND xah.je_category_name = 'Purchase Invoices'

         AND gcc.code_combination_id = xal.code_combination_id

         AND gcc.segment1 = '11550'

         AND NVL (apl.discarded_flag, 'N') = 'N'

         AND gcc.segment2 = '200035'

         AND api.invoice_num = 'Y13/WWD/JS/611'

         AND api.org_id IN (SELECT organization_id

                              FROM hr_operating_units

                             WHERE short_code LIKE 'IN%')

ORDER BY line_number