Query to Find the Validated Invoices in AP

SELECT

  v.vendor_id,

  segment1 vendor_num,

  s.vendor_name,

  ss.vendor_site_code site_name,

  CAST (invoice_num AS VARCHAR (20)) doc_number,

  invoice_type_lookup_code,

  DECODE (apps.ap_invoices_pkg.get_approval_status (invoice_id, invoice_amount,payment_status_flag, invoice_type_lookup_code ),

          'FULL', 'Fully Applied'

          ,'UNAPPROVED', 'Unvalidated'

          ,'NEEDS REAPPROVAL', 'Needs Revalidation'

          ,'APPROVED', 'Validated'

          ,'NEVER APPROVED', 'Never Validated'

          ,'CANCELLED','Cancelled'

          ,'UNPAID', 'Unpaid'

          ,'AVAILABLE', 'Available'

         ) invoice_check_status,

  invoice_date doc_date,

  v.invoice_currency_code doc_currency,

  exchange_rate,

  exchange_date,

  gl_date,

  payment_status_flag,

  ap_invoices_pkg.get_amount_withheld (invoice_id) awt_amount

FROM

  ap_invoices_all v,

  ap_suppliers s,

  ap_supplier_sites_all ss

WHERE 1=1

  --AND s.segment1 = :supplier_number

  --AND v.invoice_currency_code = :mycurrency

AND v.vendor_id = ss.vendor_id

AND v.vendor_id = s.vendor_id

  --AND v.org_id = :org_id

AND v.vendor_site_id = ss.vendor_site_id

SELECT APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS

            (

             I.INVOICE_ID

            ,I.INVOICE_AMOUNT

            ,I.PAYMENT_STATUS_FLAG

            ,I.INVOICE_TYPE_LOOKUP_CODE

            ) Approval_Status

      ,invoice_num

FROM   AP_INVOICES I

WHERE  invoice_num = 'INV_TEST_001';