PO Savings Analysis Report(by Category)

SELECT   distinct null                               C_FLEX_CAT
,        poh.segment1                             	PO_number
,        ppf.full_name                                     	Buyer
,         decode(psp1.manual_po_num_type, 'NUMERIC',
                to_number(poh.segment1), null)  Ordering_PO_Num
,        decode(psp1.manual_po_num_type, 'NUMERIC',
                null, poh.segment1) 
,        T.type_name                                    PO_type
,        decode(nvl(poh.user_hold_flag,'N'), 
                     'Y', :yes, 
                          :no )                            	On_hold
,        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
,         '1'                 			Dummy
,       pol.item_id			Item_id_qpos
,       pll.ship_to_organization_id	Ship_to_org
,       pll.ship_to_location_id		Ship_to_loc
,       poh.creation_date		Creation_date
,       poh.rate			Rate
,       pol.quantity			Line_quantity
,       pol.unit_price			Line_price
,       pol.market_price		Market_price
,       pol.list_price_per_unit		List
,       plt.order_type_lookup_code	Order_type
,       pll.shipment_num		Shipment_num
,       pll.quantity			Shipment_quantity, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_FLEX_CAT_DISP, 
	PO_POXKIAGN_XMLP_PKG.get_quantity(pll.quantity, pol.quantity) C_QUANTITY, 
	PO_POXKIAGN_XMLP_PKG.return_list(plt.order_type_lookup_code, :C_MIN_QUOTE, pol.market_price, pol.list_price_per_unit, poh.rate) C_LIST_PRICE, 
	PO_POXKIAGN_XMLP_PKG.return_amt_list(:C_MIN_QUOTE, pol.market_price, pol.list_price_per_unit, PO_POXKIAGN_XMLP_PKG.get_quantity(pll.quantity, pol.quantity), poh.rate) C_AMOUNT_LIST, 
	--PO_POXKIAGN_XMLP_PKG.return_amt_act(:C_QUANTITY, pol.unit_price, poh.rate) C_AMOUNT_ACTUAL, 
	PO_POXKIAGN_XMLP_PKG.return_amt_act(PO_POXKIAGN_XMLP_PKG.get_quantity(pll.quantity, pol.quantity), pol.unit_price, poh.rate) C_AMOUNT_ACTUAL, 
	--PO_POXKIAGN_XMLP_PKG.return_amt_saved(:C_AMOUNT_LIST, :C_AMOUNT_ACTUAL) C_AMOUNT_SAVED, 
	PO_POXKIAGN_XMLP_PKG.return_amt_saved(PO_POXKIAGN_XMLP_PKG.return_amt_list(:C_MIN_QUOTE, pol.market_price, pol.list_price_per_unit, PO_POXKIAGN_XMLP_PKG.get_quantity(pll.quantity, pol.quantity), poh.rate), PO_POXKIAGN_XMLP_PKG.return_amt_act(PO_POXKIAGN_XMLP_PKG.get_quantity(pll.quantity, pol.quantity), pol.unit_price, poh.rate)) C_AMOUNT_SAVED, 
	--PO_POXKIAGN_XMLP_PKG.return_discount(:C_AMOUNT_LIST, :C_AMOUNT_ACTUAL) C_DISCOUNT, 
	PO_POXKIAGN_XMLP_PKG.return_discount(PO_POXKIAGN_XMLP_PKG.return_amt_list(:C_MIN_QUOTE, pol.market_price, pol.list_price_per_unit, PO_POXKIAGN_XMLP_PKG.get_quantity(pll.quantity, pol.quantity), poh.rate), PO_POXKIAGN_XMLP_PKG.return_amt_act(PO_POXKIAGN_XMLP_PKG.get_quantity(pll.quantity, pol.quantity), pol.unit_price, poh.rate)) C_DISCOUNT, 
	PO_POXKIAGN_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
FROM     po_lines                     	pol
,        po_line_types                	plt
,        po_headers                   	poh
,        per_people_f                 	ppf
,        mtl_categories               	mca
,        po_document_types_all_tl  T
,        po_document_types_all_b   B
,        po_line_locations      	pll
,        hr_locations_no_join            	hrl
,        po_vendors	       	pov
,        po_lookup_codes 	plc
,        po_lookup_codes              plc1
,        po_lookup_codes              plc2
,        po_lookup_codes              plc3
,        po_system_parameters     psp1
WHERE    poh.type_lookup_code in ('STANDARD','BLANKET','PLANNED')
AND      pol.po_header_id     	= poh.po_header_id
AND      pol.po_line_id 	= pll.po_line_id (+) 
AND      pll.ship_to_location_id = hrl.location_id (+)
AND      trunc(sysdate)
         BETWEEN ppf.effective_start_date
         AND ppf.effective_end_date
AND      ppf.business_group_id + 0 = :business_group_id
AND      trunc(poh.creation_date)    
         BETWEEN nvl(:P_start_date_from,poh.creation_date - 1)
         AND     nvl(:P_start_date_to,poh.creation_date + 1)
AND      plc.lookup_code      	= poh.type_lookup_code
AND      plc.lookup_type      	= 'PO TYPE'
AND      mca.category_id      	= pol.category_id             
AND      ppf.full_name        	= nvl(:P_buyer,ppf.full_name)
AND      ppf.person_id        	= poh.agent_id
AND      plt.line_type_id     	= pol.line_type_id
AND      plt.order_type_lookup_code != 'AMOUNT'
AND      pov.vendor_id        	= poh.vendor_id
AND      B.document_type_code in ('PO', 'PA')
AND      B.document_subtype = poh.type_lookup_code
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      B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE 
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE 
AND B.ORG_ID = T.ORG_ID   
AND B.ORG_ID = poh.ORG_ID 
AND T.LANGUAGE = USERENV('LANG')
AND      &P_WHERE_CAT
GROUP BY &P_FLEX_CAT
,        poh.segment1
,        ppf.full_name
,        psp1.manual_po_num_type
,        poh.segment1
,        poh.segment1
,        poh.creation_date
,        T.type_name
,        poh.user_hold_flag
,        pol.line_num
,        pol.item_id
,        plt.line_type
,        pol.item_description
,        pol.unit_meas_lookup_code
,        pol.quantity
,        pol.market_price
,        pol.list_price_per_unit
,        poh.rate
,        pol.unit_price
,        plc1.displayed_field
,        plc2.displayed_field
,        plc3.displayed_field
,        plt.order_type_lookup_code
,        pll.ship_to_organization_id
,        pll.ship_to_location_id
,        pll.promised_date
,        pll.need_by_date
,        pll.shipment_num
,        pll.quantity
,   fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_cat_disp', 'INV', 'MCAT', MCA.STRUCTURE_ID, NULL, MCA.CATEGORY_ID, 'ALL', 'Y', 'VALUE') , 
	PO_POXKIAGN_XMLP_PKG.get_quantity(pll.quantity, pol.quantity), 
	PO_POXKIAGN_XMLP_PKG.return_list(plt.order_type_lookup_code, :C_MIN_QUOTE, pol.market_price, pol.list_price_per_unit, poh.rate),
	-- commented by Atul
--	PO_POXKIAGN_XMLP_PKG.return_amt_list(:C_MIN_QUOTE, pol.market_price, pol.list_price_per_unit, :C_QUANTITY, poh.rate), 
	--PO_POXKIAGN_XMLP_PKG.return_amt_act(:C_QUANTITY, pol.unit_price, poh.rate), 
	PO_POXKIAGN_XMLP_PKG.return_amt_saved(:C_AMOUNT_LIST, :C_AMOUNT_ACTUAL), 
	PO_POXKIAGN_XMLP_PKG.return_discount(:C_AMOUNT_LIST, :C_AMOUNT_ACTUAL), 
	PO_POXKIAGN_XMLP_PKG.return_type(:C_MIN_QUOTE, plc3.displayed_field, pol.market_price, plc2.displayed_field, pol.list_price_per_unit, plc1.displayed_field)
ORDER BY Dummy
,C_FLEX_CAT_DISP
,PO_number
,PO_type
,On_hold
,Buyer
,decode(psp1.manual_po_num_type, 'NUMERIC',null, poh.segment1)
,line
,Line_type
,Item_description
,Item_id
,Shipment_num
,:orderby_clause
,pol.line_num
,pll.shipment_num