In Seeded OTBI report "Top 10 Spend Supplier"

Nisha Italiya

Summary: In the Seeded OTBI report "Top 10 Spend Supplier" for the column "Addressable Spend" and "Non-Addressable Spend" where the data and calculations are coming from?

Is it PO level or Ap invoice level?

Sahithi Kolasani-Oracle

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 (+) )

Nisha Italiya

Thanks ! @Sahithi Kolasani-Oracle

Abhishek Gattu

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