PO Savings Analysis Report(by Category)

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Savings Analysis Report(by Category)
Application: Purchasing
Source: Savings Analysis Report(by Category) (XML)
Short Name: POXKIAGN_XML
DB package: PO_POXKIAGN_XMLP_PKG
Run PO Savings Analysis Report(by Category) and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Operating Unit 
 
 LOV
Title 
 
 Char
Buyer Name 
 
 LOV Oracle
Creation Dates From 
 
 Date
To 
 
 Date
Categories From 
 
 Char
To 2 
 
 Char
Sort By 
 
 LOV Oracle
Dynamic Precision Option 
 
 LOV Oracle
Download
Related Blitz Reports
PO Receipt Adjustments PO Receiving Exceptions PO Blanket and Planned PO Status PO Overshipments PO Purchase Summary Report By Category PO Receiving Transactions Register ECC Procurement, Agreements ECC Procurement, Requisitions ECC Procurement, Purchase Orders
Blitz Report™

Blitz Report™ provides multiple benefits: