PO Purchase Price Variance

➜ Back to Library
Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Purchase Price Variance Report (XML)
Short Name: POXRCPPV_XML
DB package: PO_POXRCPPV_XMLP_PKG 
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), 
								   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	 
 ,        gmf_lc_adj_transactions    glat
 ,        mtl_parameters             mp	 
 ,        rcv_shipment_headers       rsh	 
 ,        rcv_transactions           rct 	 
 ,        po_vendors                 pov	 
 ,        mtl_system_items           msi	 
 ,        mtl_categories             mca	 
 ,        per_all_people_f             papf	 
 WHERE   
          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      msi.organization_id      = :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	 
 AND   	glat.transaction_date between  :P_Trans_date_from and :P_Trans_date_to 
 AND      &P_WHERE_CAT   	 
 AND    ((rct.organization_id = :P_org_id AND :P_org_id is not null)  OR :P_org_id is null )
Parameter Name SQL text Validation
P_STRUCT_NUM 
 
 Number
Dynamic Precision Option 
 
 LOV Oracle
Sort By 
 
 LOV Oracle
Organization Name 
 
 LOV Oracle
To 
 
 LOV Oracle
Vendors From 
 
 LOV Oracle
To 
 
 Date
Transaction Dates From 
 
 Date
To 
 
Categories From 
 
Title 
 
Download
Related Blitz Reports
PO Headers and Lines
MRP Pegging
PO Receiving Transactions Register
CST Shop Floor Invoice Variance
CST Simulate Transfer Shop Floor Invoice Variance
PO Receiving Value
PO Receipt Adjustments
PO Substitute Receipts
INV Material Transactions
Blitz Report In Action