Monday, June 20, 2016

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';


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect