CAC Open Purchase Orders
Description
Categories: Enginatics
Repository: Github
Repository: Github
Report to show open purchase orders and related information. This report will convert any foreign currency purchases into the currency of the general ledger. The currency defaults from the inventory organization for this session. Use the To and From Transaction Date parameters to create an average receipt cost and use the Cost Type parameter to show a comparison amounts from another cost type.< ...
more
Run
CAC Open Purchase Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |