In Seeded OTBI report "Top 10 Spend Supplier"

Question

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?

Liwen Chiu · Answer

Thank you @Nathan CCC , was able to retrieve the 3 columns from the view log's SQL as below now, thanks much:

Nathan CCC · Answer

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

Liwen Chiu · Answer

Hi all,

Would also like to know what does it mean with the "Non-Addressable Spend" as well as the "Addressable Spend" as well, please advise, thank you.

Liwen