PO Vendor Price Performance Analysis

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Vendor Price Performance Analysis Report (XML)
Short Name: POXPRIPR_XML
DB package: PO_POXPRIPR_XMLP_PKG
select a.*,(C_amount_fix/Q_purchased_Fix) average_purchase_fix ,
PO_POXPRIPR_XMLP_PKG.average_invoiceformula(Q_Invoiced_fix, Total_amount_invoiced_fix) Average_Invoice_fix
from 	(SELECT 
       pov.vendor_name       Vendor
,      pol.item_id
,      poh.agent_id
,      pol.category_id         category_id1
,      pll.unit_meas_lookup_code   Unit_Of_measure
,      sum ( ( nvl ( pll.price_override , pol.unit_price )  *  nvl ( poh.rate,1 ) ) *  ( nvl ( pod.quantity_ordered , 0 ) - nvl ( pod.quantity_cancelled, 0 ) ) )  Amount_purchased
,      sum ( ( nvl ( pll.price_override , pol.unit_price )  *  nvl ( poh.rate,1 ) ) *  ( nvl ( pod.quantity_ordered , 0 ) - nvl ( pod.quantity_cancelled, 0 ) ) ) C_amount_fix
,      sum ( aid1.Invoice_Amt )  Amount_Invoiced
,      sum ( aid1.Invoice_Amt )  Total_amount_invoiced_fix
,      sum ( nvl ( pod.quantity_ordered  , 0 ) - nvl ( pod.quantity_cancelled, 0 ) )  PO_Quantity
,      sum ( aid1.qty_invoiced )  AP_Quantity, 
	PO_POXPRIPR_XMLP_PKG.c_amount_roundformula(:C_amount, :C_FND_PRECISION) C_AMOUNT_ROUND, 
	PO_POXPRIPR_XMLP_PKG.average_purchase_roundformula(:Average_Purchase, :C_FND_PRECISION) AVERAGE_PURCHASE_ROUND, 
	PO_POXPRIPR_XMLP_PKG.average_invoice_roundformula(:Average_Invoice, :C_FND_PRECISION) AVERAGE_INVOICE_ROUND, 
--	PO_POXPRIPR_XMLP_PKG.average_invoiceformula(:Q_Invoiced, :Total_amount_invoiced) Average_Invoice, 
	PO_POXPRIPR_XMLP_PKG.average_purchaseformula(:C_amount, :Q_Purchased) Average_Purchase, 
	PO_POXPRIPR_XMLP_PKG.ipvformula(:Average_Purchase, :Average_Invoice) IPV, 
	PO_POXPRIPR_XMLP_PKG.po_primary_qtyformula(sum ( nvl ( pod.quantity_ordered , 0 ) - nvl ( pod.quantity_cancelled , 0 ) ), PO_POXPRIPR_XMLP_PKG.rateformula(pll.unit_meas_lookup_code, :UNIT, pol.item_id)) PO_Primary_qty, 
	sum(PO_POXPRIPR_XMLP_PKG.po_primary_qtyformula(sum ( nvl ( pod.quantity_ordered , 0 ) - nvl ( pod.quantity_cancelled , 0 ) ), PO_POXPRIPR_XMLP_PKG.rateformula(pll.unit_meas_lookup_code, :UNIT, pol.item_id))) over() Q_purchased_Fix,
	PO_POXPRIPR_XMLP_PKG.ap_primary_qtyformula(sum ( aid1.qty_invoiced ), PO_POXPRIPR_XMLP_PKG.rateformula(pll.unit_meas_lookup_code, :UNIT, pol.item_id)) AP_Primary_qty	,
	sum(PO_POXPRIPR_XMLP_PKG.ap_primary_qtyformula(sum ( aid1.qty_invoiced ), PO_POXPRIPR_XMLP_PKG.rateformula(pll.unit_meas_lookup_code, :UNIT, pol.item_id))) over() q_invoiced_fix ,
	PO_POXPRIPR_XMLP_PKG.rateformula(pll.unit_meas_lookup_code, :UNIT, pol.item_id) Conv_Rate
FROM    ( Select  sum ( nvl ( aid.quantity_invoiced , 0 ) * aid.unit_price * nvl ( aia.exchange_rate,1 ) ) Invoice_Amt,
                             sum ( nvl ( aid.quantity_invoiced ,0 ) ) Qty_invoiced,
                             aid.po_distribution_id po_distribution_id
                 from ap_invoice_distributions aid
                 ,ap_invoices_all aia
                where nvl ( aid.reversal_flag, 'N' )  =  'N'  
                 AND aia.invoice_id=aid.invoice_id
                 AND &period_where
                group by aid.po_distribution_id )      aid1
,            po_distributions        pod
,            po_line_locations       pll
,            po_lines                pol
,            po_headers              poh
,            po_vendors              pov
WHERE  poh.vendor_id                 =           pov.vendor_id
AND        poh.po_header_id          =           pol.po_header_id
AND        pol.po_line_id            =           pll.po_line_id
AND        pll.line_location_id      =           pod.line_location_id
AND        pod.po_distribution_id    =           aid1.po_distribution_id
AND         (poh.type_lookup_code    =          'STANDARD'
                 OR poh.type_lookup_code =          'PLANNED'
                 OR poh.type_lookup_code =          'BLANKET')
AND        nvl ( poh.approved_flag, 'N' )  = 'Y'
AND        pll.shipment_type in ( 'STANDARD' , 'BLANKET' , 'SCHEDULED' ) 
AND    pov.vendor_name        
            BETWEEN nvl(:P_vendor_from,pov.vendor_name)
            AND     nvl(:P_vendor_to,pov.vendor_name)
AND    trunc(poh.creation_date)
            BETWEEN  nvl( trunc ( :P_creation_date_from ), trunc ( poh.creation_date - 1) )
           AND            nvl( trunc ( :P_creation_date_to ), trunc ( poh.creation_date + 1 ) ) 
 and pol.item_id=:inventory_item_id 
 and poh.agent_id=:employee_id 
 and pol.category_id=:category_id
GROUP BY  pov.vendor_name
,        pol.item_id 
,        pol.category_id
,        poh.agent_id
,        pll.unit_meas_lookup_code
ORDER BY 1 ASC,3 ASC,2 ASC,4 ASC , avg ( nvl ( pll.price_override , pol.unit_price ) * nvl ( poh.rate , 1 ) ) ASC , pov.vendor_name) a
Parameter Name SQL text Validation
P_STRUCT_NUM
 
Number
Dynamic Precision Option
 
LOV Oracle
Sort By
 
LOV Oracle
To
 
Items From
 
To
 
Categories From
 
To
 
Date
Creation Date From
 
Date
To
 
LOV Oracle
Period From
 
LOV Oracle
To
 
LOV Oracle
Vendors From
 
LOV Oracle
Title