PO Purchase Price Variance

SELECT 
	   null                               C_FLEX_CAT
,        null                              C_FLEX_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
,        pol.po_header_id||' - '||pol.po_line_id          group_by_lineid
,        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)),:c_ext_precision) Unit_Price
,        rct.transaction_id                        RCT_ID
,        round(nvl(mmt.transaction_cost,0) , :c_ext_precision)       PO_Functional_Price
,        round(nvl(mmt.actual_cost,0),:c_ext_precision)    STD_Unit_Cost
,        round(decode(mta.accounting_line_type, 3, nvl(mcacd.actual_cost,0), 0) , :c_ext_precision) moh_absorbed_per_unit
,        mtp.process_enabled_flag 
,        rct.organization_id 
,        msi.inventory_item_id 
, 	   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
,	   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') C_FLEX_ITEM_DISP
,        round(nvl(mmt.actual_cost,0),:c_ext_precision)    STD_UNIT_COST_F
, 	   PO_POXRCPPV_XMLP_PKG.c_price_varianceformula( round ( nvl ( mmt.transaction_cost , 0 ) , :c_ext_precision ), 
									 round ( nvl ( mmt.actual_cost , 0 ) , :c_ext_precision ), 
									 round ( decode ( mta.accounting_line_type , 3 , nvl ( mcacd.actual_cost , 0 ) , 0 ) , :c_ext_precision ), 
									 round ( mmt.primary_quantity , :P_qty_precision ), 
									 :C_PRECISION) C_Price_Variance 
/*	PO_POXRCPPV_XMLP_PKG.std_unit_cost_fformula(msi.inventory_item_id, :organization_id, rct.transaction_date, mtp.process_enabled_flag, round ( nvl ( mmt.actual_cost , 0 ) , :c_ext_precision ), :C_EXT_PRECISION) STD_UNIT_COST_F, 
	PO_POXRCPPV_XMLP_PKG.c_price_varianceformula(round ( nvl ( mmt.transaction_cost , 0 ) , :c_ext_precision ), round ( nvl ( mmt.actual_cost , 0 ) , :c_ext_precision ), round ( decode ( mta.accounting_line_type , 3 , nvl ( mcacd.actual_cost , 0 ) , 0 ) , :c_ext_precision ), round ( mmt.primary_quantity , :P_qty_precision ), :C_PRECISION) C_Price_Variance */
FROM     po_distributions           pod
,        po_line_locations          pll
,        po_lines                   pol
,        po_headers                 poh
,        po_releases                por
,        mtl_material_transactions  mmt
,        mtl_transaction_accounts   mta
,        mtl_cst_actual_cost_details mcacd
,        mtl_parameters               mtp
,        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 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      mtp.organization_id = rct.organization_id
AND      mtp.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      msi.organization_id  = :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_TX_DATE_WHERE
AND      &P_WHERE_CAT   
AND    ((rct.organization_id = :P_org_id AND :P_org_id is not null)  OR :P_org_id is null )
AND  exists (select 1 from mtl_transaction_accounts mta1 where mta1.transaction_id = mmt.transaction_id
                           and mta1.accounting_line_type = 6)
UNION
SELECT   null                               C_FLEX_CAT
,        null                              C_FLEX_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
,        pol.po_header_id||' - '||pol.po_line_id          group_by_lineid
,        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
,        rct.transaction_id                        RCT_ID
,       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)), :c_ext_precision) PO_Functional_Price
,        round ( &P_select_wip, :c_ext_precision ) STD_UNIT_COST
,        0 moh_absorbed_per_unit
,        mtp.process_enabled_flag 
,        rct.organization_id 
,       msi.inventory_item_id
,  	  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 
,	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') C_FLEX_ITEM_DISP
,      round ( &P_select_wip, :c_ext_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)), :c_ext_precision),
		  						   round ( &P_select_wip, :c_ext_precision ),
		  						   0, 
						 		   round(decode(rct.transaction_type,'RETURN TO RECEIVING', rct.primary_quantity * -1,rct.primary_quantity), :P_qty_precision), 
								   :C_PRECISION) C_Price_Variance 
/*	PO_POXRCPPV_XMLP_PKG.std_unit_cost_fformula(msi.inventory_item_id, :organization_id, rct.transaction_date, 
								  mtp.process_enabled_flag,round ( &P_select_wip, :c_ext_precision ), :C_EXT_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)), :c_ext_precision),
		  round ( &P_select_wip, :c_ext_precision ),
		  0, 
		  round(decode(rct.transaction_type,
                           'RETURN TO RECEIVING', rct.primary_quantity * -1,rct.primary_quantity), :P_qty_precision), :C_PRECISION) C_Price_Variance */
FROM     po_distributions           pod
,        po_line_locations          pll
,        po_lines                   pol
,        po_headers                 poh
,        po_releases                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             mtp
&P_from_wip
WHERE 
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     msi.organization_id  = :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_TX_DATE_WHERE
AND      &P_WHERE_CAT
&P_where_wip
AND   ( (rct.organization_id = :P_org_id AND :P_org_id is not null)  OR :P_org_id is null )
AND mtp.organization_id = rct.organization_id
AND mtp.process_enabled_flag='N'
UNION ALL
SELECT   null                               C_FLEX_CAT
,      null                              C_FLEX_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
,        pol.po_header_id||' - '||pol.po_line_id          group_by_lineid
,        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
,        rct.transaction_id                        RCT_ID
,        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)), :c_ext_precision)               PO_Functional_Price
,       0  STD_UNIT_COST 
,        0 moh_absorbed_per_unit
,        mpa.process_enabled_flag 
,        rct.organization_id 
,       msi.inventory_item_id
,	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 
,	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') C_FLEX_ITEM_DISP
,	PO_POXRCPPV_XMLP_PKG.std_unit_cost_fformula(msi.inventory_item_id, 
								 decode(mpa.process_enabled_flag, 'Y', rct.organization_id, :organization_id), 
								  rct.transaction_date, 
								  mpa.process_enabled_flag, 0 , :C_EXT_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)), :c_ext_precision), 
								   PO_POXRCPPV_XMLP_PKG.std_unit_cost_fformula(msi.inventory_item_id, 
										decode(mpa.process_enabled_flag, 'Y', rct.organization_id, :organization_id),
										rct.transaction_date, mpa.process_enabled_flag, 0, :C_EXT_PRECISION), 
								   0, 
								   round(decode(rct.transaction_type,'RETURN TO RECEIVING', rct.primary_quantity * -1,rct.primary_quantity), :P_qty_precision), 
								   :C_PRECISION) C_Price_Variance 
FROM     po_distributions           pod
,        po_line_locations          pll
,        po_lines                   pol
,        po_headers                 poh
,        po_releases                por
,        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
,        mtl_parameters		    mpa
WHERE 
              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      msi.organization_id        = :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_TX_DATE_WHERE
AND      &P_WHERE_CAT   
AND     ( (rct.organization_id = :P_org_id AND :p_org_id is not null)  OR :P_org_id is null )
AND       rct.organization_id = mpa.organization_id
AND      mpa.process_enabled_flag = 'Y'
UNION ALL
/* LCM-OPM Integration added below query  bug 8642337, pmarada */
SELECT  distinct
         null                               C_FLEX_CAT	 
 ,        null                              C_FLEX_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      
 ,         pol.po_header_id||' - '||pol.po_line_id group_by_lineid 
 ,        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
,        rct.transaction_id                        RCT_ID
,        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)), :c_ext_precision)               PO_Functional_Price
,        0 STD_UNIT_COST 
,        0 moh_absorbed_per_unit
,        mp.process_enabled_flag
,        glat.organization_id	 
,        msi.inventory_item_id
,	    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 
,	   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') C_FLEX_ITEM_DISP	 
,       PO_POXRCPPV_XMLP_PKG.std_unit_cost_fformula(msi.inventory_item_id, 
								 decode(mp.process_enabled_flag, 'Y', rct.organization_id, :organization_id), 
								  rct.transaction_date, 
								  mp.process_enabled_flag, 0 , :C_EXT_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)), :c_ext_precision), 
								   PO_POXRCPPV_XMLP_PKG.std_unit_cost_fformula(msi.inventory_item_id, 
										decode(mp.process_enabled_flag, 'Y', rct.organization_id, :organization_id),
										rct.transaction_date, mp.process_enabled_flag, 0, :C_EXT_PRECISION),