Hi @Nisha Italiya

The back end query for Top 10 Spend Supplier is given below.

Based on inv_dist_match_status and invoice_type_lookup_code Addressable Spend and Non-Addressable Spend columns are derived.

 SELECT

           invoicedistribution.amount,

           invoicedistribution.invoice_distribution_id,

           invoicedistribution.line_type_lookup_code,

           invoicedistribution.match_status_flag,

           (

               CASE invoicedistribution.match_status_flag

                   WHEN 'A' THEN

                       'APPROVED'

                   WHEN 'T' THEN

                           CASE ap_invoices_utility_pkg.get_approval_status(invoiceheader.invoice_id, invoiceheader.invoice_amount, invoiceheader.

                           payment_status_flag, invoiceheader.invoice_type_lookup_code)

                               WHEN 'APPROVED' THEN

                                   'APPROVED'

                               WHEN 'UNPAID'  THEN

                                   'APPROVED'

                               ELSE

                                   'NEEDS REAPPROVAL'

                           END

                   WHEN 'N' THEN

                       'NEEDS REAPPROVAL'

                   ELSE

                       'NEVER APPROVED'

               END

           )                                      AS inv_dist_match_status,

           invoiceline.invoice_id                 AS invoice_id1,

           invoiceline.line_number,

           invoiceline.line_type_lookup_code      AS line_type_lookup_code1,

           invoiceheader.invoice_amount,

           invoiceheader.invoice_currency_code,

           invoiceheader.invoice_id               AS invoice_id2,

           invoiceheader.invoice_type_lookup_code,

           invoiceheader.org_id                   AS org_id2,

           invoiceheader.payment_status_flag,

           invoiceheader.vendor_id,

           invoiceheader.vendor_site_id           AS vendor_site_id817,

           invoiceheader.wfapproval_status        AS wfapproval_status1,

           purchaseorderheaderdist.prc_bu_id      AS prc_bu_id4,

           invdistpredist.invoice_distribution_id AS pd_invoice_distribution_id,

           invdistpredist.distribution_line_number AS pd_distribution_line_number

       FROM

           ap_invoice_distributions_all invoicedistribution,

           ap_invoice_lines_all        invoiceline,

           ap_invoices_all             invoiceheader,

           ap_invoice_distributions_all invdistpredist,

           po_distributions_all        purchaseorderdist,

           po_headers_all              purchaseorderheaderdist

       WHERE

           ( invoicedistribution.invoice_id = invoiceline.invoice_id

             AND invoicedistribution.invoice_line_number = invoiceline.line_number

             AND invoiceline.invoice_id = invoiceheader.invoice_id

             AND invoicedistribution.prepay_distribution_id = invdistpredist.invoice_distribution_id (+)

             AND invoicedistribution.po_distribution_id = purchaseorderdist.po_distribution_id (+)

             AND purchaseorderdist.po_header_id = purchaseorderheaderdist.po_header_id (+) )

1. what is "Addressable Spend" and "Non-Addressable spend" here? Can someone please explain.
2. We are trying to explore if there is any seeded daShboard for supplier performance

Hi Abhishek, Liwen,

In the seeded analysis "Top 10 Spend by Suppler" in folder procurement/spend/transaction analysis samples/

this is a query from subject are "Procurement - Spend Real Time"

with a calculated measure "Spend"."Addressable Spend" "Spend"."Non-Addressable Spend"

on page manage sessions if you view log you can see the logic in the calculation

for example, addressable spend =

,         (CASE InvoiceDistribution.MATCH_STATUS_FLAG WHEN 'A' THEN 'APPROVED' 

WHEN 'T' THEN CASE ap_invoices_utility_pkg.get_approval_status(InvoiceHeader.INVOICE_ID,InvoiceHeader.INVOICE_AMOUNT,InvoiceHeader.PAYMENT_STATUS_FLAG,InvoiceHeader.INVOICE_TYPE_LOOKUP_CODE) 
WHEN 'APPROVED' THEN 'APPROVED' WHEN 'UNPAID' THEN 'APPROVED' ELSE 'NEEDS REAPPROVAL' END 
WHEN 'N' THEN 'NEEDS REAPPROVAL' ELSE 'NEVER APPROVED' END) 
AS INV_DIST_MATCH_STATUS

when INV_DIST_MATCH_STATUS = 'APPROVED'

and not AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE is null

and not AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE in ('AWT', 'PREPAYMENT')

and not AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE in ('AWT', 'PREPAY')

and AP_INVOICE_DISTRIBUTIONS_ALL.LINE_TYPE_LOOKUP_CODE in ('ACCRUAL', 'ERV', 'IPV', 'ITEM', 'RETAINAGE', 'RETROACCRUAL')

and AP_INVOICES_ALL.WFAPPROVAL_STATUS in ('APPROVED', 'MANUALLY APPROVED', 'NOT REQUIRED', 'WFAPPROVED')

and InvDistPreDist.DISTRIBUTION_LINE_NUMBER (AP_INVOICE_DISTRIBUTIONS_ALL.PREPAY_DISTRIBUTION_ID) is null

then nvl(AP_INVOICE_DISTRIBUTIONS_ALL.AMOUNT, 0) else 0