PO Purchase Price Variance
Description
Categories: BI Publisher, Enginatics
Repository: Github
Repository: Github
Imported Oracle standard Purchase Price Variance report
Source: Purchase Price Variance Report (XML)
Short Name: POXRCPPV_XML
DB package: PO_POXRCPPV_XMLP_PKG
Source: Purchase Price Variance Report (XML)
Short Name: POXRCPPV_XML
DB package: PO_POXRCPPV_XMLP_PKG
Run
PO Purchase Price Variance and other Oracle EBS reports with Blitz Report™ on our demo environment
select 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') category , fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') item , msi.description description , pov.vendor_name vendor , decode(poh.type_lookup_code, 'BLANKET', poh.segment1||' - '||por.release_num, 'PLANNED', poh.segment1||' - '||por.release_num, poh.segment1) po_number_release , poh.currency_code currency , papf.full_name buyer , pol.line_num line , rsh.shipment_num shipment , rct.transaction_date receipt_date , rsh.receipt_num receipt_number , round(mmt.primary_quantity,:p_qty_precision) quantity_received , rct.primary_unit_of_measure unit , round((nvl(mmt.transaction_cost,0)/nvl(mmt.currency_conversion_rate,1)),pol.extended_precision) unit_price , round(nvl(mmt.transaction_cost,0) , pol.extended_precision) po_functional_price , round(nvl(mmt.actual_cost,0),pol.extended_precision) std_unit_cost , round(decode(mta.accounting_line_type, 3, nvl(mcacd.actual_cost,0), 0) , pol.extended_precision) moh_absorbed_per_unit , mp.process_enabled_flag , mp.organization_code , round(nvl(mmt.actual_cost,0),pol.extended_precision) std_unit_cost_f , po_poxrcppv_xmlp_pkg.c_price_varianceformula( round ( nvl ( mmt.transaction_cost , 0 ) , pol.extended_precision ), round ( nvl ( mmt.actual_cost , 0 ) , pol.extended_precision ), round ( decode ( mta.accounting_line_type , 3 , nvl ( mcacd.actual_cost , 0 ) , 0 ) , pol.extended_precision ), round ( mmt.primary_quantity , :p_qty_precision ), pol.precision) c_price_variance ,pol.ledger ,pol.operating_unit , pol.po_header_id||' - '||pol.po_line_id group_by_lineid , rct.transaction_id rct_id , pol.item_id from po_distributions_all pod , po_line_locations_all pll ,( select pla.*, (select fspa.inventory_organization_id from financials_system_params_all fspa where hou.set_of_books_id=fspa.set_of_books_id and pla.org_id=fspa.org_id) inventory_organization_id, gl.name ledger, hou.name operating_unit, fc.precision, nvl(fc.extended_precision,fc.precision) extended_precision from po_lines_all pla, hr_operating_units hou, gl_ledgers gl, fnd_currencies fc where 2=2 and pla.org_id=hou.organization_id and hou.set_of_books_id=gl.ledger_id and gl.currency_code=fc.currency_code ) pol , po_headers_all poh , po_releases_all por , mtl_material_transactions mmt , mtl_transaction_accounts mta , mtl_cst_actual_cost_details mcacd , mtl_parameters mp , rcv_shipment_headers rsh , rcv_transactions rct , ap_suppliers pov , mtl_system_items msi , mtl_categories mca , hr_locations_no_join hrl , per_all_people_f papf where 3=3 and mmt.rcv_transaction_id = rct.transaction_id and mmt.organization_id = rct.organization_id and mmt.transaction_id = mta.transaction_id (+) and mta.accounting_line_type (+) = 3 and mcacd.transaction_id (+) = mmt.transaction_id and mcacd.organization_id (+) = mmt.organization_id and mcacd.layer_id (+) = -1 and mcacd.cost_element_id (+) = 2 and mcacd.level_type (+) = 1 and mcacd.transaction_action_id (+) = mmt.transaction_action_id and mp.organization_id = rct.organization_id and mp.process_enabled_flag = 'N' and rct.shipment_header_id = rsh.shipment_header_id and rct.po_line_id = pol.po_line_id and rct.po_header_id = poh.po_header_id and rct.po_line_location_id = pll.line_location_id and rct.po_distribution_id = pod.po_distribution_id and pod.line_location_id = pll.line_location_id and pod.destination_type_code = 'INVENTORY' and pll.po_release_id = por.po_release_id(+) and pol.item_id = msi.inventory_item_id(+) and pol.inventory_organization_id = msi.organization_id(+) and pol.category_id = mca.category_id and rsh.vendor_id = poh.vendor_id and poh.vendor_id = pov.vendor_id and papf.person_id = poh.agent_id and (papf.employee_number is not null or papf.npw_number is not null) and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id, papf.employee_number,papf.applicant_number )) = 'TRUE' and decode(hr_general.get_xbg_profile,'Y', papf.business_group_id , hr_general.get_business_group_id) = papf.business_group_id and pod.deliver_to_location_id = hrl.location_id(+) &p_vendor_name_where and exists (select 1 from mtl_transaction_accounts mta1 where mta1.transaction_id = mmt.transaction_id and mta1.accounting_line_type = 6) union select 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') category , fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') item , msi.description description , pov.vendor_name vendor , decode(poh.type_lookup_code, 'BLANKET', poh.segment1||' - '||por.release_num, 'PLANNED', poh.segment1||' - '||por.release_num, poh.segment1) po_number_release , poh.currency_code currency , papf.full_name buyer , pol.line_num line , rsh.shipment_num shipment , rct.transaction_date receipt_date , rsh.receipt_num receipt_number , round(decode(rct.transaction_type, 'RETURN TO RECEIVING', rct.primary_quantity * -1, rct.primary_quantity), :p_qty_precision) quantity_received , rct.primary_unit_of_measure unit , rct.po_unit_price * (rct.source_doc_quantity / rct.primary_quantity ) + ((nvl(pod.nonrecoverable_tax,0)/ decode (pod.quantity_ordered,0,1,pod.quantity_ordered) )*(rct.source_doc_quantity/rct.primary_quantity))unit_price , round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1, pod.quantity_ordered) ) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision) po_functional_price , round ( &p_select_wip, pol.extended_precision ) std_unit_cost , 0 moh_absorbed_per_unit , mp.process_enabled_flag , mp.organization_code , round ( &p_select_wip, pol.extended_precision ) std_unit_cost_f , po_poxrcppv_xmlp_pkg.c_price_varianceformula(round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1,pod.quantity_ordered)) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision), round ( &p_select_wip, pol.extended_precision ), 0, round(decode(rct.transaction_type,'RETURN TO RECEIVING', rct.primary_quantity * -1,rct.primary_quantity), :p_qty_precision), pol.precision) c_price_variance ,pol.ledger ,pol.operating_unit , pol.po_header_id||' - '||pol.po_line_id group_by_lineid , rct.transaction_id rct_id , pol.item_id from po_distributions_all pod , po_line_locations_all pll ,( select pla.*, (select fspa.inventory_organization_id from financials_system_params_all fspa where hou.set_of_books_id=fspa.set_of_books_id and pla.org_id=fspa.org_id) inventory_organization_id, gl.name ledger, hou.name operating_unit, fc.precision, nvl(fc.extended_precision,fc.precision) extended_precision from po_lines_all pla, hr_operating_units hou, gl_ledgers gl, fnd_currencies fc where 2=2 and pla.org_id=hou.organization_id and hou.set_of_books_id=gl.ledger_id and gl.currency_code=fc.currency_code ) pol , po_headers_all poh , po_releases_all por , rcv_transactions rct , rcv_shipment_headers rsh , ap_suppliers pov , mtl_system_items msi , mtl_categories mca , hr_locations_no_join hrl , per_all_people_f papf , mtl_parameters mp &p_from_wip where 3=3 and rct.shipment_header_id = rsh.shipment_header_id and rct.po_line_id = pol.po_line_id and rct.po_header_id = poh.po_header_id and rct.po_line_location_id = pll.line_location_id and pod.line_location_id = pll.line_location_id and pod.po_distribution_id = rct.po_distribution_id and pod.destination_type_code = 'SHOP FLOOR' and pll.po_release_id = por.po_release_id(+) and pol.item_id = msi.inventory_item_id(+) and pol.inventory_organization_id = msi.organization_id(+) and pol.category_id = mca.category_id and rsh.vendor_id = poh.vendor_id and poh.vendor_id = pov.vendor_id and papf.person_id = poh.agent_id and (papf.employee_number is not null or papf.npw_number is not null) and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id, papf.employee_number,papf.applicant_number )) = 'TRUE' and decode(hr_general.get_xbg_profile,'Y', papf.business_group_id , hr_general.get_business_group_id) = papf.business_group_id and pod.deliver_to_location_id = hrl.location_id(+) &p_vendor_name_where &p_where_wip and mp.organization_id = rct.organization_id and mp.process_enabled_flag='N' union all select 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') category , fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') item , msi.description description , pov.vendor_name vendor , decode(poh.type_lookup_code, 'BLANKET', poh.segment1||' - '||por.release_num, 'PLANNED', poh.segment1||' - '||por.release_num, poh.segment1) po_number_release , poh.currency_code currency , papf.full_name buyer , pol.line_num line , rsh.shipment_num shipment , rct.transaction_date receipt_date , rsh.receipt_num receipt_number , round(decode(rct.transaction_type , 'RETURN TO RECEIVING' , rct.primary_quantity * -1 , 'RETURN TO VENDOR',rct.primary_quantity * -1, rct.primary_quantity) , :p_qty_precision) quantity_received , rct.primary_unit_of_measure unit , rct.po_unit_price * (rct.source_doc_quantity / rct.primary_quantity ) + ((nvl(pod.nonrecoverable_tax,0)/decode (pod.quantity_ordered,0,1, pod.quantity_ordered) )*(rct.source_doc_quantity/rct.primary_quantity)) unit_price , round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1, pod.quantity_ordered) ) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision) po_functional_price , 0 std_unit_cost , 0 moh_absorbed_per_unit , mp.process_enabled_flag , mp.organization_code , po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(pol.item_id, decode(mp.process_enabled_flag, 'Y', rct.organization_id, pol.inventory_organization_id), rct.transaction_date, mp.process_enabled_flag, 0 , pol.extended_precision) std_unit_cost_f , po_poxrcppv_xmlp_pkg.c_price_varianceformula( round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1,pod.quantity_ordered)) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision), po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(pol.item_id, decode(mp.process_enabled_flag, 'Y', rct.organization_id, pol.inventory_organization_id), rct.transaction_date, mp.process_enabled_flag, 0, pol.extended_precision), 0, round(decode(rct.transaction_type,'RETURN TO RECEIVING', rct.primary_quantity * -1,rct.primary_quantity), :p_qty_precision), pol.precision) c_price_variance ,pol.ledger ,pol.operating_unit , pol.po_header_id||' - '||pol.po_line_id group_by_lineid , rct.transaction_id rct_id , pol.item_id from po_distributions_all pod , po_line_locations_all pll ,( select pla.*, (select fspa.inventory_organization_id from financials_system_params_all fspa where hou.set_of_books_id=fspa.set_of_books_id and pla.org_id=fspa.org_id) inventory_organization_id, gl.name ledger, hou.name operating_unit, fc.precision, nvl(fc.extended_precision,fc.precision) extended_precision from po_lines_all pla, hr_operating_units hou, gl_ledgers gl, fnd_currencies fc where 2=2 and pla.org_id=hou.organization_id and hou.set_of_books_id=gl.ledger_id and gl.currency_code=fc.currency_code ) pol , po_headers_all poh , po_releases_all por , rcv_shipment_headers rsh , rcv_transactions rct , ap_suppliers pov , mtl_system_items msi , mtl_categories mca , hr_locations_no_join hrl , per_all_people_f papf , mtl_parameters mp where 3=3 and rct.shipment_header_id = rsh.shipment_header_id and rct.po_line_id = pol.po_line_id and rct.po_header_id = poh.po_header_id and rct.po_line_location_id = pll.line_location_id and rct.po_distribution_id = pod.po_distribution_id and pod.line_location_id = pll.line_location_id and (nvl(pll.lcm_flag,'N') = 'N' or ( nvl(pll.lcm_flag,'N') = 'Y' and rct.lcm_shipment_line_id is null ) ) and pod.destination_type_code in ('INVENTORY','SHOP FLOOR') and rct.destination_type_code <> 'RECEIVING' and pll.po_release_id = por.po_release_id(+) and pol.item_id = msi.inventory_item_id(+) and pol.inventory_organization_id = msi.organization_id(+) and pol.category_id = mca.category_id and rsh.vendor_id = poh.vendor_id and poh.vendor_id = pov.vendor_id and papf.person_id = poh.agent_id and (papf.employee_number is not null or papf.npw_number is not null) and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id, papf.employee_number,papf.applicant_number )) = 'TRUE' and decode(hr_general.get_xbg_profile,'Y', papf.business_group_id , hr_general.get_business_group_id) = papf.business_group_id and pod.deliver_to_location_id = hrl.location_id(+) &p_vendor_name_where and rct.organization_id = mp.organization_id and mp.process_enabled_flag = 'Y' union all /* lcm-opm integration added below query bug 8642337, pmarada */ select distinct 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') category , fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') item , msi.description description , pov.vendor_name vendor , decode(poh.type_lookup_code, 'BLANKET', poh.segment1||' - '||por.release_num, 'PLANNED', poh.segment1||' - '||por.release_num, poh.segment1) po_number_release , poh.currency_code currency , papf.full_name buyer , pol.line_num line , rsh.shipment_num shipment , glat.transaction_date receipt_date , rsh.receipt_num receipt_number , round(glat.primary_quantity,:p_qty_precision) quantity_received , glat.primary_uom_code unit , rct.po_unit_price * (rct.source_doc_quantity / rct.primary_quantity) + ((nvl(pod.nonrecoverable_tax,0)/pod.quantity_ordered)*(rct.source_doc_quantity/rct.primary_quantity)) unit_price , round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/pod.quantity_ordered) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision) po_functional_price , 0 std_unit_cost , 0 moh_absorbed_per_unit , mp.process_enabled_flag , mp.organization_code , po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(pol.item_id, decode(mp.process_enabled_flag, 'Y', rct.organization_id, pol.inventory_organization_id), rct.transaction_date, mp.process_enabled_flag, 0 , pol.extended_precision) std_unit_cost_f , po_poxrcppv_xmlp_pkg.c_price_varianceformula( round(nvl(rct.currency_conversion_rate,1) * nvl(rct.po_unit_price* (rct.source_doc_quantity / rct.primary_quantity),0) + (( (nvl(pod.nonrecoverable_tax,0) * nvl(rct.currency_conversion_rate,1))/decode (pod.quantity_ordered,0,1,pod.quantity_ordered)) *(rct.source_doc_quantity/rct.primary_quantity)), pol.extended_precision), po_poxrcppv_xmlp_pkg.std_unit_cost_fformula(pol.item_id, decode(mp.process_enabled_flag, 'Y', rct.organization_id, pol.inventory_organization_id), rct.transaction_date, mp.process_enabled_flag, 0, pol.extended_precision), 0, round(decode(rct.transaction_type,'RETURN TO RECEIVING', rct.primary_quantity * -1,rct.primary_quantity), :p_qty_precision), pol.precision) c_price_variance ,pol.ledger ,pol.operating_unit , pol.po_header_id||' - '||pol.po_line_id group_by_lineid , rct.transaction_id rct_id , pol.item_id from po_distributions_all pod , po_line_locations_all pll ,( select pla.*, (select fspa.inventory_organization_id from financials_system_params_all fspa where hou.set_of_books_id=fspa.set_of_books_id and pla.org_id=fspa.org_id) inventory_organization_id, gl.name ledger, hou.name operating_unit, fc.precision, nvl(fc.extended_precision,fc.precision) extended_precision from po_lines_all pla, hr_operating_units hou, gl_ledgers gl, fnd_currencies fc where 2=2 and pla.org_id=hou.organization_id and hou.set_of_books_id=gl.ledger_id and gl.currency_code=fc.currency_code ) pol , po_headers_all poh , po_releases_all por , gmf_lc_adj_transactions glat , mtl_parameters mp , rcv_shipment_headers rsh , rcv_transactions rct , ap_suppliers pov , mtl_system_items msi , mtl_categories mca , per_all_people_f papf where 3=3 and 4=4 and glat.rcv_transaction_id = rct.transaction_id and glat.event_type in (16,17) and mp.organization_id = glat.organization_id and mp.process_enabled_flag = 'Y' and rct.shipment_header_id = rsh.shipment_header_id and rct.po_line_id = pol.po_line_id and rct.po_header_id = poh.po_header_id and rct.po_line_location_id = pll.line_location_id and nvl(pll.lcm_flag,'N') = 'Y' and rct.po_distribution_id = pod.po_distribution_id and pll.po_release_id = por.po_release_id(+) and pod.destination_type_code in ('INVENTORY') and rct.destination_type_code <> 'RECEIVING' and pol.item_id = msi.inventory_item_id(+) and pol.inventory_organization_id = msi.organization_id(+) and pol.category_id = mca.category_id and rsh.vendor_id = pov.vendor_id and papf.person_id = poh.agent_id and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and decode(hr_security.view_all ,'Y' , 'TRUE', hr_security.show_record('PER_ALL_PEOPLE_F',papf.person_id, papf.person_type_id, papf.employee_number,papf.applicant_number )) = 'TRUE' and decode(hr_general.get_xbg_profile,'Y', papf.business_group_id , hr_general.get_business_group_id) = papf.business_group_id &p_vendor_name_where |