Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    MShrini
    Unable to find invoice validation status through BIP sql
    Topic posted October 4, 2017 by MShriniBronze Crown: 15,000+ Points, last edited January 30, 2019, tagged Accounting Hub Cloud Service, Allocations, Assets, Budgetary control, Cash Management, Collections, Compliance, Configuration, Core HR, Expenses, Financials, General Ledger, Intercompany, Invoice Imaging, Payables, Period Close / Reconciliation, Public Sector, Receivables, Reports, Revenue Management, Sample Reports, Security, Setup / Administration, Subledger Accounting, Tip, Update 
    1311 Views, 8 Comments
    Title:
    Unable to find invoice validation status through BIP sql
    Summary:
    Unable to find invoice validation status through BIP sql
    Content:

    We are not bale find invoice validation status through BIP sql by using AP_INVOICE_ALL.  Will be appreciated for quick help.

    Comment

     

    • Jon Hayes

      Hi - The Validation Status is held at distribution level, and so it is held in AP_INVOICE_DISTRIBUTIONS_ALL.

      Please see following blog for details: http://appselangovan.blogspot.co.uk/2013/12/ap-invoice-validation-status.html

      Kind Regards,

      Jon

    • MShrini

      Thanks Jon but i am not able use 'AP_INVOICES_UTILITY_PKG' in fusion cloud application.  Kindly provide the sql query to fetch the invoice where invoice are validated and accounted as It is really helpful us.

    • Kumaran Raviprakash

      There is no field on an Oracle Payables Invoice that holds the invoice validation status.

      The invoice validation status is calculated from the data in the invoice distributions.

      Invoice Validation Status is a derived field.As the blog entry points to an E-Business Suite note would suggest you to refer to the latest Fusion Apps OER tables list and also contact product support to exactly confirm on the derived value on whether there is change in any specific logic in deriving Validation Status in Fusion Payables in comparison to E-Business Suite. 

      Regards,
      Kumaran
      • MShrini

        Thanks Kumaran, We have gone through all the documents including OER but no luck.   Planning to raise an SR for the same.

    • Becky Alvarez

      This may not be of any help but you can get the validation status from the BI Subject area Payables Invoices - Installments Real Time under Invoice Details > Reference Information.

    • manohar kondabattula

      we  can  use  this package  to approval status like this  'use  this  subquery  in  select 

      SELECT 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) "INSTATUS_SUMMARY"
              FROM   AP_INVOICES_ALL AIA
              WHERE   AIA.INVOICE_NUM = 'Z234'
              

      it  will work