PO Savings Analysis Report(by Category)
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
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 |