PO Purchase Price Variance

Description
Categories: BI Publisher, Enginatics
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
select
        &c_flex_cat_disp category
,       &c_flex_item_disp 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 
,        po_vendors                 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      &p_where_cat   
and  exists (select 1 from mtl_transaction_accounts mta1 where mta1.transaction_id = mmt.transaction_id
                           and mta1.accounting_line_type = 6)
union
select
        &c_flex_cat_disp category
,       &c_flex_item_disp 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
,        po_vendors                 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      r