PO Vendor Price Performance Analysis

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