PO Savings Analysis Report(by Buyer)

SELECT   distinct pol.line_num                 	Line
,        plt.line_type                                     	Line_type
,        pol.item_description                         	Item_description
,        pol.unit_meas_lookup_code            	Unit
,        plc1.displayed_field	 	List_code
,        plc2.displayed_field		Market_code
,        plc3.displayed_field		Quote_code
,        to_number( nvl(pol.unit_price,0) * nvl(poh.rate,1))       Actual_price
,        decode(poh.creation_date,NULL,decode((pll.promised_date),NULL,decode( (pll.need_by_date),NULL,sysdate,pll.need_by_date),pll.promised_date),
poh.creation_date)			Quote_date
,        pol.po_header_id                             	PO_header_id1
,        pol.market_price                	Market_price
,        pol.unit_price                      	Line_price
,        pol.list_price_per_unit       	List
,        poh.rate                                	Rate
,        pol.quantity                           	Line_quantity
,        poh.creation_date		Creation_date
,        pol.item_id			Item_id
,        pll.ship_to_organization_id  	Ship_to_org
,        pll.ship_to_location_id		Ship_to_loc
,        plt.order_type_lookup_code	Order_type
,        pll.shipment_num		Shipment_num
,        pll.quantity			Shipment_quantity
,        pll.quantity_cancelled                       Shipment_quantity_cancelled, 
/*	PO_POXKISUM_XMLP_PKG.return_discount(:C_AMOUNT_LIST1, :C_AMOUNT_ACTUAL1) C_DISCOUNT, 
	PO_POXKISUM_XMLP_PKG.return_list(:C_MIN_QUOTE, pol.market_price, pol.list_price_per_unit, poh.rate) C_LIST_PRICE, 
	PO_POXKISUM_XMLP_PKG.return_type(:C_MIN_QUOTE, plc3.displayed_field, pol.market_price, plc2.displayed_field, pol.list_price_per_unit, plc1.displayed_field) C_PRICE_TYPE, 
	PO_POXKISUM_XMLP_PKG.round_amount_list1(:C_AMOUNT_LIST1, :C_CURR_PRECISION) C_AMOUNT_LIST1_ROUND, */
	PO_POXKISUM_XMLP_PKG.get_quantity(pll.quantity, pll.quantity_cancelled, pol.quantity) C_QUANTITY, 
/*	PO_POXKISUM_XMLP_PKG.return_amt_list(:C_MIN_QUOTE, pol.market_price, pol.list_price_per_unit, :C_QUANTITY, poh.rate) C_AMOUNT_LIST1, 
*/	
	PO_POXKISUM_XMLP_PKG.return_amt_act(PO_POXKISUM_XMLP_PKG.get_quantity(pll.quantity, pll.quantity_cancelled, pol.quantity), pol.unit_price, poh.rate) C_AMOUNT_ACTUAL1 
/*	,PO_POXKISUM_XMLP_PKG.round_amount_actual1(:C_AMOUNT_ACTUAL1, :C_CURR_PRECISION) C_AMOUNT_ACTUAL1_ROUND, 
	PO_POXKISUM_XMLP_PKG.return_amt_saved(:C_AMOUNT_LIST1, :C_AMOUNT_ACTUAL1) C_AMOUNT_SAVED, 
	PO_POXKISUM_XMLP_PKG.round_amount_saved(:C_AMOUNT_SAVED, :C_CURR_PRECISION) C_AMOUNT_SAVED_ROUND*/
FROM     hr_locations_no_join            	hrl
,        po_line_types           	plt
,        po_headers              	poh
,        po_line_locations       	pll
,        po_lines                	pol
,        po_lookup_codes         	plc1
,        po_lookup_codes         	plc2
,        po_lookup_codes         	plc3
WHERE    pol.po_line_id          	= pll.po_line_id (+)
AND      pll.ship_to_location_id = hrl.location_id (+)
AND      pol.po_header_id     	= poh.po_header_id
AND      plt.line_type_id        	= pol.line_type_id
AND      plt.order_type_lookup_code != 'AMOUNT'
AND      plc1.lookup_type        	= 'LINE PRICE TYPE'
AND      plc1.lookup_code        	= 'LIST'
AND      plc2.lookup_type        	= 'LINE PRICE TYPE'                       
AND      plc2.lookup_code        	= 'MARKET'
AND      plc3.lookup_type        	= 'LINE PRICE TYPE'                       
AND      plc3.lookup_code        	= 'QUOTE' 
 and pol.po_header_id=:PO_header_id
ORDER BY pol.line_num, pll.shipment_num