Invoice Headers and Accounted and Validated Status Info

If you can log into your database as the APPS user, or something like that, this can be useful to finding out the approval status, accounted status and things like that.

-- ##############################################################################
--      INVOICE DETAILS - EXTRA INFO
-- ############################################################################*/
 
SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.creation_date
     , aia.last_update_date
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , aia.invoice_date
     , aia.validation_request_id
     , aia.validation_worker_id
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by          
     , aia.payment_status_flag paid
     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled
     , aia.cancelled_date
     , DECODE(apps.ap_invoices_utility_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL' , 'Fully Applied', 'NEVER APPROVED' , 'Never Validated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'CANCELLED' , 'Cancelled', 'UNPAID' , 'Unpaid', 'AVAILABLE' , 'Available', 'UNAPPROVED' , 'Unvalidated', 'APPROVED' , 'Validated', 'PERMANENT' , 'Permanent Prepayment', NULL) validation_status_v1 -- http://m-burhan.blogspot.co.uk/2012/06/function-which-provide-ap-validation.html
     , DECODE(apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.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') validation_status_v2 -- https://community.oracle.com/thread/3573183
     , apps.ap_invoices_pkg.get_posting_status(aia.invoice_id) accounted
     , apps.ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) approval_status
     , apps.ap_invoices_pkg.get_amount_withheld (aia.invoice_id) amount_withheld
--     , (SELECT COUNT(*) from ap.ap_holds_all ah where ah.invoice_id = aia.invoice_id AND ah.release_lookup_code IS NULL) hold_count
  FROM ap.ap_invoices_all aia
  JOIN ap.ap_terms_tl att            ON aia.terms_id = att.term_id
  JOIN applsys.fnd_user fu           ON aia.created_by = fu.user_id
  JOIN apps.po_vendors pv            ON aia.vendor_id = pv.vendor_id
  JOIN apps.po_vendor_sites_all pvsa ON aia.vendor_site_id = pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id 
 WHERE 1 = 1
--   AND pv.vendor_name = 'Worra Lorra Cheese'
--   AND aia.invoice_id IN (252962,2088392,2165995,2500940,273058)                  -- ##    ID   ## --
--   AND aia.invoice_num IN ('69487161')              -- ## NUMBER  ## --
--   AND AIA.DOC_SEQUENCE_VALUE IN (2317661,2353095)          -- ## VOUCHER ## --
   AND DECODE(apps.ap_invoices_utility_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL' , 'Fully Applied', 'NEVER APPROVED' , 'Never Validated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'CANCELLED' , 'Cancelled', 'UNPAID' , 'Unpaid', 'AVAILABLE' , 'Available', 'UNAPPROVED' , 'Unvalidated', 'APPROVED' , 'Validated', 'PERMANENT' , 'Permanent Prepayment', NULL) = 'Needs Revalidation'
   AND 1 = 1;

Using Oracle SQL instead of ANSI SQL:

-- ##############################################################################
--      EXTRA INVOICE DETAILS
-- ############################################################################*/

SELECT aia.invoice_id id
     , aia.invoice_num num
     , aia.doc_sequence_value voucher
     , aia.creation_date
     , aia.last_update_date          
     , aia.invoice_date inv_date
     , aia.invoice_type_lookup_code inv_type
     , pv.vendor_name supplier
     , pvsa.vendor_site_code site
     , aia.invoice_amount amt
     , fu.description cr_by          
     , aia.payment_status_flag paid
     , nvl2(aia.cancelled_amount, 'Y', 'N') cancelled
--     , DECODE(ap_invoices_utility_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code), 'FULL' , 'Fully Applied', 'NEVER APPROVED' , 'Never Validated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'CANCELLED' , 'Cancelled', 'UNPAID' , 'Unpaid', 'AVAILABLE' , 'Available', 'UNAPPROVED' , 'Unvalidated', 'APPROVED' , 'Validated', 'PERMANENT' , 'Permanent Prepayment', NULL) validation_status_v1 -- http://m-burhan.blogspot.co.uk/2012/06/function-which-provide-ap-validation.html
--     , DECODE(apps.ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.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') validation_status_v2 -- https://community.oracle.com/thread/3573183
--     , ap_invoices_pkg.get_posting_status(aia.invoice_id) accounted
--     , ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) approval_status
--     , ap_invoices_pkg.get_amount_withheld (aia.invoice_id) amount_withheld
     , (SELECT COUNT(*) from ap.ap_holds_all ah where ah.invoice_id = aia.invoice_id AND ah.release_lookup_code IS NULL) hold_count
  FROM ap.ap_invoices_all aia
     , ap.ap_terms_tl att
     , applsys.fnd_user fu
     , apps.po_vendors pv
     , apps.po_vendor_sites_all pvsa
 WHERE aia.terms_id = att.term_id
   AND aia.created_by = fu.user_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
   AND pv.vendor_name = 'Worra Lorra Cheese'
--   AND aia.invoice_id IN (2109872)               -- ##    ID   ## --
--   AND aia.invoice_num IN ('69487161')              -- ## NUMBER  ## --
--   AND AIA.DOC_SEQUENCE_VALUE IN (2039086)         -- ## VOUCHER ## --
   AND 1 = 1;

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*