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?
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
WHEN 'UNPAID' THEN
ELSE
'NEEDS REAPPROVAL'
END
WHEN 'N' THEN
'NEVER APPROVED'
) 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 (+) )
Thanks ! @Sahithi Kolasani-Oracle
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
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
Thank you @Nathan CCC , was able to retrieve the 3 columns from the view log's SQL as below now, thanks much:
Is there a log that can be checked on our side that might help with the issue. I've submitted an SR, however it is currently being bounced around between different areas. Could not establish connection. oracle.xdo.delivery.ssh2.SSHMessageException: Error in decrypting message Current thought is that this is an issue with…
Hello, I'm trying my hand at creating some KPI's and Dashboard Visualizations for the Recruiting Activity Center (RAC) per the following documentation: Flexible Reporting in Redwood Dashboards Redwood Experience: Recruiting Dashboard When I add a session variable to my OTBI reports, I correctly see a value of 0 in my…
'Facility Name' exists in the Recruiting - Recruiting Real Time multiple times with varying usage. Job Requistion -> Job Requisition - Additional Details -> Facility Name Job Requistion -> Location -> Facility Name Is there any reason why these two Facility Names would not exist in the Recruiting - Real Time Events subject…
We are encountering issues with our OTBI dashboards where there is no option to export to Excel. This seems to be a new issue after the latest update to 26B. I made a change to the report layout (moved one analysis into its own section), and now I cannot export the page. As you can see in my attached photos, the export…
we have a requirement to fetch Worker Historic information like Current Job Title Previous Job Title Previous Department New Department Previous Manager New Manager Previous Location New Location which subject area stores this information in the OTBI?
