CAC Open Purchase Orders

select  nvl(gl.short_name, gl.name) Ledger,
	haou2.name Operating_Unit,
	mp.organization_code Org_Code,
	pov.vendor_name Supplier,
	hr.full_name Buyer,
	msiv.concatenated_segments Item_Number,
	msiv.description Item_Description,
	fcl.meaning Item_Type,
	-- Revision for version 1.6
	misv.inventory_item_status_code Item_Status,
&category_columns
	pl1.displayed_field PO_Destination_Type,
	--we.wip_entity_name WIP_Job,
	cic.resource_code OSP_Resource,
	pl.vendor_product_num Supplier_Item,
	round(msiv.list_price_per_unit,5) Target_or_List_Price,
	ph.segment1 PO_Number,
        to_char(pl.line_num)  PO_Line,
	nvl(pl4.displayed_field,nvl(pl3.displayed_field, pl2.displayed_field))  PO_Line_Status,
	pp.segment1 Project_Number,
	pp.name Project_Name,
	pll.creation_date Creation_Date,
	-- Revision for version 1.2
	pll.promised_date Promise_Date,
	pll.need_by_date Need_by_Date,
	to_char(pr.release_num) PO_Release,
	pr.release_date Release_Date,
	(select	max(ms.expected_delivery_date)
	 from	mtl_supply ms
	 where	ms.supply_type_code in ('PO','RECEIVING','SHIPMENT')
	 and	ms.item_id                      = msiv.inventory_item_id
	 and	ms.to_organization_id           = msiv.organization_id
	 and	ms.destination_type_code        in ('INVENTORY','SHOP FLOOR')
	 and	ms.po_distribution_id           = pod.po_distribution_id
	) Expected_Receipt_Date,
	-- Revision for version 1.1
	fcl.meaning Inspection_Required,
	muomv_po.uom_code PO_UOM,
	nvl(ph.currency_code, gl.currency_code) PO_Currency_Code,
	nvl(pll.price_override, pl.unit_price) PO_Unit_Price,
	decode(pll.match_option, 
		'P', trunc(nvl(pod.rate_date, pll.creation_date)),
		'R', trunc(nvl(gdr_po.conversion_date, sysdate)),
		trunc(nvl(gdr_po.conversion_date, sysdate))) Currency_Rate_Date,
	round(decode(pll.match_option, 
		'P', nvl(pod.rate,1),
		'R', gdr_po.conversion_rate,
		gdr_po.conversion_rate),6) PO_Exchange_Rate,
	gl.currency_code GL_Currency_Code,
	round(decode(pll.match_option, 
		'P', nvl(pod.rate,1),
		'R', gdr_po.conversion_rate,
		gdr_po.conversion_rate) * nvl(pll.price_override, pl.unit_price),6) Converted_PO_Unit_Price,
	ucr.conversion_rate PO_UOM_Conversion_Rate,
	round(decode(pll.match_option, 
		'P', nvl(pod.rate,1),
		'R', gdr_po.conversion_rate,
		gdr_po.conversion_rate) * nvl(pll.price_override, pl.unit_price) * ucr.conversion_rate,6) Converted_PO_at_Primary_UOM,
	-- Revision for version 1.4
	muomv_msi.uom_code UOM_Code,
	round(nvl(cic.unburdened_cost,0),5) Unburdened_Unit_Cost,
	-- Revision for version 1.20
	-- PO Price - Unburdened Cost = Unit_Cost_Difference
	round((decode(pll.match_option, 
		'P', nvl(pod.rate,1),
		'R', gdr_po.conversion_rate,
		gdr_po.conversion_rate) * 
		nvl(pll.price_override, pl.unit_price)) - nvl(cic.unburdened_cost,0),5) Unit_Cost_Difference,
	-- Revision for version 1.20
	-- (PO Price - Unburdened Cost) * nvl(pll.quantity,0) = Extended_Cost_Difference
	round(((decode(pll.match_option, 
		'P', nvl(pod.rate,1),
		'R', gdr_po.conversion_rate,
		gdr_po.conversion_rate) * 
		nvl(pll.price_override, pl.unit_price)) - nvl(cic.unburdened_cost,0)) * nvl(pll.quantity,0),2) Extended_Cost_Difference,
	-- Calculate the Percent Difference
	--   when difference = 0 then 0
	--   when item cost = 0 then 100%
	--   when PO unit price = 0 then -100%
	--   else PO Price - item cost / item cost
	case
	   when	round((decode(pll.match_option, 
		'P', nvl(pod.rate,1),
		'R', gdr_po.conversion_rate,
		gdr_po.conversion_rate) * 
		nvl(pll.price_override, pl.unit_price)) - nvl(cic.unburdened_cost,0),5) = 0
		then 0
	   when round(nvl(cic.unburdened_cost,0),5) = 0
		then 100
	   when round(decode(pll.match_option, 
			'P', nvl(pod.rate,1), 
			'R', gdr_po.conversion_rate,
			gdr_po.conversion_rate
			    ) * nvl(pll.price_override, pl.unit_price),5) = 0
		then -100
	   else 
		round(((decode(pll.match_option, 
			'P', nvl(pod.rate,1),
			'R', gdr_po.conversion_rate,
			gdr_po.conversion_rate) * 
			nvl(pll.price_override, pl.unit_price)) - nvl(cic.unburdened_cost,0)) /
		nvl(cic.unburdened_cost,0) * 100,2)
	end Percent_Difference,
	-- End revision for version 1.20
	cct_curr.cost_type Current_Cost_Type,
	-- Revision for version 1.5
	round(cic.material_cost,5)           Material_Cost,
	round(cic.material_overhead_cost,5)  Material_Overhead_Cost,
	round(cic.resource_cost,5)           Resource_Cost,
	round(cic.outside_processing_cost,5) Outside_Processing_Cost,
	round(cic.overhead_cost,5)           Overhead_Cost,
	round(cic.item_cost,5)               Current_Item_Cost,
	-- Revision for version 1.5
	(select	round(cic.material_cost,5) - round(cic.tl_material_overhead,5)
	 from	cst_item_costs cic,
		cst_cost_types cct,
		mtl_parameters mp		
	 where	cic.inventory_item_id = msiv.inventory_item_id
	 and	cic.organization_id   = msiv.organization_id
	 and	cic.cost_type_id      = cct.cost_type_id
	 and	mp.organization_id    = cic.organization_id
	 and 6=6                   -- p_org_code
	 and 10=10                 -- p_cost_type
	) Comparison_Material_Cost,
	receipts.receipt_qty Average_Receipt_Quantity,
	receipts.receipt_amount Average_Receipt_Amount,
	receipts.avg_receipt_cost Average_Receipt_Cost,
	-- End revision for version 1.5
	pll.quantity Quantity_Ordered,
	pll.quantity_received Quantity_Received,
	pll.quantity_billed Quantity_Invoiced,
	round(nvl(pod.rate,1) * pll.price_override * (pll.quantity * ucr.conversion_rate),2) Total_PO_Amount,
	-- Revision for version 1.1
	&segment_columns
	pl.creation_date PO_Line_Creation_Date
from	po_vendors                  pov,
	wip_entities                we,
	po_headers_all              ph,
	po_lines_all                pl,
	po_line_locations_all       pll,
	po_releases_all             pr,
	po_distributions_all        pod,
	mtl_system_items_vl         msiv,
	-- Revision for version 1.5
	cst_cost_types              cct_curr,
	mtl_uom_conversions_view    ucr,
	-- Revision for version 1.4
	mtl_units_of_measure_vl     muomv_msi,
	mtl_units_of_measure_vl     muomv_po,
	pa_projects_all             pp,
	-- End revision for version 1.4
	-- Revision for version 1.6
	mtl_item_status_vl          misv,
	-- Revision for version 1.4
	(select crc.organization_id,
		msiv.inventory_item_id purchase_item_id,
		br.resource_code,
		br.resource_id,
		crc.cost_type_id,
		0 material_cost,
		0 material_overhead_cost,
		0 resource_cost,
		nvl(crc.resource_rate,0) outside_processing_cost,
		0 overhead_cost,
		nvl(crc.resource_rate,0) item_cost,
		nvl(crc.resource_rate,0) unburdened_cost
	 from	cst_resource_costs  crc,
		bom_resources       br,
		mtl_system_items_vl msiv,
		mtl_parameters      mp
	 where  crc.cost_type_id      = mp.primary_cost_method
	 and    crc.resource_id       = br.resource_id
	 and    crc.organization_id   = mp.organization_id
	 and    br.purchase_item_id   = msiv.inventory_item_id
	 and    br.organization_id    = msiv.organization_id
	 and	br.cost_element_id    = 4 -- OSP cost element
	 and	mp.organization_id    = msiv.organization_id
	 and	msiv.item_type        = 'OP'
	 and	6=6                  -- p_org_code
	 and	7=7                  -- p_item_number
	 union
	 select	cic.organization_id,
		cic.inventory_item_id purchase_item_id,
		null resource_code,
		-999 resource_id,
		cic.cost_type_id,
		cic.material_cost,
		cic.material_overhead_cost,
		cic.resource_cost,
		cic.outside_processing_cost,
		cic.overhead_cost,
		cic.item_cost,
		cic.unburdened_cost
	 from	cst_item_costs cic,
		mtl_system_items_vl msiv,
		mtl_parameters mp
	 where	cic.organization_id   = mp.organization_id
	 and	cic.organization_id   = msiv.organization_id
	 and	cic.inventory_item_id = msiv.inventory_item_id
	 and	cic.cost_type_id      = mp.primary_cost_method
	 and	6=6                  -- p_org_code
	 and	7=7                  -- p_item_number
	) cic, -- item costs
	-- End revision for version 1.4
	-- Revision for version 1.5
	-- ===========================================================================
	-- Get the average PO receipt cost over a date range
	-- ===========================================================================
	(select	mp.organization_id,
		mmt.inventory_item_id,
		sum(mmt.primary_quantity) receipt_qty,
		round(sum(nvl(mmt.transaction_cost,0) * mmt.primary_quantity),2)  receipt_amount,
		round(sum(nvl(mmt.transaction_cost,0) * mmt.primary_quantity) /
			decode(sum(mmt.primary_quantity),
					0,1,
					sum(mmt.primary_quantity)),5) avg_receipt_cost
	 from	mtl_material_transactions mmt,
		mtl_system_items_vl msiv,
		mtl_parameters mp
	 where 	mmt.inventory_item_id 	 	= msiv.inventory_item_id
	 and	mmt.organization_id	 	= msiv.organization_id
	 and	11=11                           -- p_trx_date_from, p_trx_date_to
	 and	mmt.transaction_source_type_id	= 1 -- purchase orders
	 and	6=6                  -- p_org_code
	 and	7=7                  -- p_item_number
	 and	mmt.organization_id             = mp.organization_id
	 and	nvl(mmt.transaction_cost,0)    <> 0
	 and	msiv.inventory_asset_flag       = 'Y'
	 group by
		mp.organization_id,
		mmt.inventory_item_id
	) receipts,
	-- ===========================================================================
	-- Get a set of currency rates to translate the PO Price into the G/L Currency
	-- ===========================================================================
	(select	gdr1.from_currency,
		gdr1.to_currency,
		gdct1.user_conversion_type,
		gdr1.conversion_date,
		gdr1.conversion_rate
	 from	gl_daily_rates gdr1,
		gl_daily_conversion_types gdct1
	 where	exists (
			select 'x'
			from	mtl_parameters mp,
				hr_organization_information hoi,
				hr_all_organization_units_vl haou,
				hr_all_organization_units_vl haou2,
				gl_ledgers gl
			-- =================================================
			-- Get inventory ledger and operating unit information
			-- =================================================
			where	hoi.org_information_context   = 'Accounting Information'
			and	hoi.organization_id           = mp.organization_id
			and	hoi.organization_id           = haou.organization_id            -- this gets the organization name
			and	haou2.organization_id         = to_number(hoi.org_information3) -- this gets the operating unit id
			and	gl.ledger_id                  = to_number(hoi.org_information1) -- get the ledger_id
			and	gdr1.to_currency              = gl.currency_code
			-- Do not report the master inventory organization
			and	mp.organization_id           <> mp.master_organization_id
			   )
	 and	gdr1.conversion_type       = gdct1.conversion_type
	 and	4=4                        -- p_curr_conv_date1
	 and	5=5                        -- p_curr_conv_type1
	 union all
	 select	gl.currency_code,              -- from_currency
		gl.currency_code,              -- to_currency
		gdct1.user_conversion_type,    -- user_conversion_type
		:p_curr_conv_date1,            -- conversion_date                                              -- p_curr_conv_date1
		1                              -- conversion_rate
	 from	gl_ledgers gl,
		gl_daily_conversion_types gdct1
	 where	5=5    -- user_conversion_type
	 group by
		gl.currency_code,
		gl.currency_code,
		gdct1.user_conversion_type,                                                                  -- p_curr_conv_date1
		:p_curr_conv_date1,           -- conversion_date                                             -- p_curr_conv_date1
		1
	) gdr_po, -- NEW Currency Rates
	-- End revision for version 1.5
	-- ===========================================================================
	-- Select current Currency Rates based on the currency conversion date
	-- ===========================================================================
	(select	gdr1.from_currency,
		gdr1.to_currency,
		gdct1.user_conversion_type,
		gdr1.conversion_date,
		gdr1.conversion_rate
	 from	gl_daily_rates gdr1,
		gl_daily_conversion_types gdct1
	 where	exists (
			select 'x'
			from	mtl_parameters mp,
				hr_organization_information hoi,
				hr_all_organization_units_vl haou,
				hr_all_organization_units_vl haou2,
				gl_ledgers gl
			-- =================================================
			-- Get inventory ledger and operating unit information
			-- =================================================
			where	hoi.org_information_context   = 'Accounting Information'
			and	hoi.organization_id           = mp.organization_id
			and	hoi.organization_id           = haou.organization_id            -- this gets the organization name
			and	haou2.organization_id         = to_number(hoi.org_information3) -- this gets the operating unit id
			and	gl.ledger_id                  = to_number(hoi.org_information1) -- get the ledger_id
			and	gdr1.to_currency              = gl.currency_code
			-- Do not report the master inventory organization
			and	mp.organization_id           <> mp.master_organization_id
			   )
	 and	gdr1.conversion_type       = gdct1.conversion_type
	 and	4=4                        -- p_curr_conv_date1
	 and	5=5                        -- p_curr_conv_type1
	 union all
	 select	gl.currency_code,              -- from_currency
		gl.currency_code,              -- to_currency
		gdct1.user_conversion_type,    -- user_conversion_type
		:p_curr_conv_date1,            -- conversion_date                                              -- p_curr_conv_date1
		1                              -- conversion_rate
	 from	gl_ledgers gl,
		gl_daily_conversion_types gdct1
	 where	5=5    -- user_conversion_type
	 group by
		gl.currency_code,
		gl.currency_code,
		gdct1.user_conversion_type,                                                                  -- p_curr_conv_date1
		:p_curr_conv_date1,           -- conversion_date                                             -- p_curr_conv_date1
		1
	) gdr1, -- Current Currency Rates
	-- End for revision 1.20
	mtl_parameters              mp, 
	hr_employees                hr,
	fnd_common_lookups          fcl,
	-- Revision for version 1.1
	fnd_common_lookups          fcl2,
	po_lookup_codes             pl1,  -- Destination_Type
	po_lookup_codes             pl2,  -- Header
	po_lookup_codes             pl3,  -- Line
	po_lookup_codes             pl4,  -- Line location
	hr_organization_information hoi,
	hr_all_organization_units   haou, -- inv_organization_id
	hr_all_organization_units   haou2, -- operating unit
	gl_ledgers                  gl,
	gl_code_combinations        gcc1,
	gl_code_combinations        gcc2,
	gl_code_combinations        gcc3
-- ========================================================
-- Organization, resource, item, costs and PO joins
-- ========================================================
where	msiv.inventory_item_id          = pl.item_id
and	msiv.organization_id            = pll.ship_to_organization_id
and	ucr.inventory_item_id           = msiv.inventory_item_id
and	ucr.organization_id             = msiv.organization_id
and	ucr.unit_of_measure             = pl.unit_meas_lookup_code
-- Revision for version 1.6
and	msiv.inventory_item_status_code = misv.inventory_item_status_code
-- Revision for version 1.4
and	msiv.primary_uom_code           = muomv_msi.uom_code
and	ucr.uom_code                    = muomv_po.uom_code
and	mp.organization_id              = msiv.organization_id
-- Revision for version 1.5
and	cct_curr.cost_type_id           = mp.primary_cost_method
and	nvl(cic.resource_id,-999)       = nvl(pod.bom_resource_id,-999)
and	we.wip_entity_id (+)            = pod.wip_entity_id
and</