SQL Query To Get Invoice Validation And Accounting Status For The Given Check Number (Doc ID 2975480.1)

SELECT aca.check_number

      ,aia.invoice_id

  ,aia.invoice_num

  ,aia.invoice_amount

  ,distribution_line_number

  ,aida.accrual_posted_flag

  ,decode(aida.match_status_flag,

              'A', 'Validated',

              'N', 'Never Validated',

              'T', 'Needs Revalidation',

              'S', 'Stopped') "Invoice Validation Status"

  ,decode(ap_invoices_pkg.get_posting_status(aia.invoice_id),

              'S', 'Selected for Accounting',

              'P', 'Partial',

              'N', 'Unaccounted',

              'Y', 'Accounted',

              'Unaccounted') "Invoice Accounting Status",

      aida.invoice_distribution_id,

      aida.accounting_event_id,

      apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,

                                               aia.invoice_amount,

                                               aia.payment_status_flag,

                                               aia.invoice_type_lookup_code)"Invoice Approval Status",

      decode(aida.posted_flag,

              'Y', 'Posted',

              'N', 'Not Posted',

              'Not Posted') "Invoice Posting Status"   

FROM ap_checks_all aca

    ,ap_invoice_payments_all aipa

,ap_invoices_all aia

,ap_suppliers asa

,ap_invoice_distributions_all aida

WHERE aca.check_id = aipa.check_id

AND aipa.invoice_id = aia.invoice_id

AND aia.vendor_id = asa.vendor_id

AND aida.invoice_id = aia.invoice_id

AND aca.check_number = <pass check number>