CAC WIP Resource Efficiency
Description
Categories: Enginatics
Repository: Github
Repository: Github
Report your resource efficiency variances for your open and closed WIP jobs. Resource efficiency measures the WIP routing requirements against the actual applied resources. This report replicates the Resource Section for the Oracle Discrete Job Value Report - Standard Costing.
If you leave the Cost Type parameter blank the report uses either your Costing Method Cost Type (Standard) or you ... more
If you leave the Cost Type parameter blank the report uses either your Costing Method Cost Type (Standard) or you ... more
Run
CAC WIP Resource Efficiency and other Oracle EBS reports with Blitz Report™ on our demo environment
with wdj0 as (select wdj.wip_entity_id, wdj.organization_id, wdj.class_code, wdj.creation_date, wdj.scheduled_start_date, wdj.date_released, wdj.date_completed, wdj.date_closed, wdj.last_update_date, wdj.primary_item_id, wdj.lot_number, wdj.status_type, wdj.start_quantity, wdj.net_quantity, wdj.project_id, wdj.material_account, -- Revision for version 1.22 wdj.resource_account, wdj.outside_processing_account, -- End revision for version 1.22 wdj.quantity_completed, wdj.quantity_scrapped, oap.period_start_date, oap.schedule_close_date, oap.period_name, -- Revision for version 1.22 (case when wdj.date_closed >= oap.period_start_date then 'Variance' -- the job is open when wdj.date_closed is null and wdj.creation_date < oap.schedule_close_date + 1 then 'Valuation' -- the job is closed and ...the job was closed after the accounting period when wdj.date_closed is not null and wdj.date_closed >= oap.schedule_close_date + 1 then 'Valuation' end ) Report_Type, -- End revision for version 1.22 -- Revision for version 1.10 oap.acct_period_id, mp.primary_cost_method, mp.organization_code, wac.class_type from wip_discrete_jobs wdj, org_acct_periods oap, mtl_parameters mp, wip_accounting_classes wac where wdj.class_code = wac.class_code and wdj.organization_id = wac.organization_id and wac.class_type in (1,3,5) and oap.organization_id = wdj.organization_id and mp.organization_id = wdj.organization_id -- find jobs that were open or closed during or after the report period -- the job is open or opened before the period close date and ( (wdj.date_closed is null -- the job is open and wdj.creation_date < oap.schedule_close_date + 1 and :p_report_option in ('Open jobs', 'All jobs') -- p_report_option ) or -- the job is closed and ...the job was closed after the accounting period (wdj.date_closed is not null and wdj.date_closed >= oap.schedule_close_date + 1 and :p_report_option in ('Open jobs', 'All jobs') -- p_report_option ) or -- find jobs that were closed during the report period (wdj.date_closed >= oap.period_start_date and wdj.date_closed < oap.schedule_close_date + 1 and :p_report_option in ('Closed jobs', 'All jobs') -- p_report_option ) ) and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and 2=2 -- p_org_code and 3=3 -- p_assembly_number and 4=4 -- p_period_name, p_wip_job, wip_status, p_wip_class_code ), wdj as (select wdjsum.wip_entity_id, wdjsum.organization_id, wdjsum.class_code, wdjsum.creation_date, wdjsum.scheduled_start_date, wdjsum.date_released, wdjsum.date_closed, wdjsum.date_completed, wdjsum.last_update_date, wdjsum.primary_item_id, wdjsum.lot_number, wdjsum.status_type, wdjsum.start_quantity, wdjsum.net_quantity, wdjsum.project_id, wdjsum.material_account, -- Revision for version 1.22 wdjsum.resource_account, wdjsum.outside_processing_account, -- End revision for version 1.22 wdjsum.period_start_date, wdjsum.schedule_close_date, wdjsum.period_name, -- Revision for version 1.22 wdjsum.report_type, -- Revision for version 1.10 wdjsum.acct_period_id, wdjsum.primary_cost_method, wdjsum.organization_code, wdjsum.class_type, sum (wdjsum.quantity_completed) quantity_completed, sum (wdjsum.quantity_scrapped) quantity_scrapped, -- Revision for version 1.1, if scrap is not financially recorded do not include in component requirements sum(decode(:p_include_scrap, 'N', 0, wdjsum.quantity_scrapped)) adj_quantity_scrapped from (select wdj0.* from wdj0 union all select wdj0.wip_entity_id, wdj0.organization_id, wdj0.class_code, wdj0.creation_date, wdj0.scheduled_start_date, wdj0.date_released, wdj0.date_completed, wdj0.date_closed, wdj0.last_update_date, wdj0.primary_item_id, wdj0.lot_number, wdj0.status_type, wdj0.start_quantity, wdj0.net_quantity, wdj0.project_id, wdj0.material_account, -- Revision for version 1.22 wdj0.resource_account, wdj0.outside_processing_account, -- End revision for version 1.22 decode(mmt.transaction_type_id, 90, 0, -- scrap assemblies from wip 91, 0, -- return assemblies scrapped from wip 44, -1 * mmt.primary_quantity, -- wip completion 17, mmt.primary_quantity -- wip completion return ) quantity_completed, decode(mmt.transaction_type_id, 90, mmt.primary_quantity, -- scrap assemblies from wip 91, -1 * mmt.primary_quantity, -- return assemblies scrapped from wip 44, 0, -- wip completion 17, 0 -- wip completion return ) quantity_scrapped, wdj0.period_start_date, wdj0.schedule_close_date, wdj0.period_name, -- Revision for version 1.22 wdj0.report_type, -- Revision for version 1.10 wdj0.acct_period_id, wdj0.primary_cost_method, wdj0.organization_code, wdj0.class_type from wdj0, mtl_material_transactions mmt where mmt.transaction_source_type_id = 5 and mmt.transaction_source_id = wdj0.wip_entity_id and mmt.transaction_date >= wdj0.schedule_close_date + 1 and wdj0.organization_id = mmt.organization_id ) wdjsum group by wdjsum.wip_entity_id, wdjsum.organization_id, wdjsum.class_code, wdjsum.creation_date, wdjsum.scheduled_start_date, wdjsum.date_released, wdjsum.date_completed, wdjsum.date_closed, wdjsum.last_update_date, wdjsum.primary_item_id, wdjsum.lot_number, wdjsum.status_type, wdjsum.start_quantity, wdjsum.net_quantity, wdjsum.project_id, wdjsum.material_account, -- Revision for version 1.22 wdjsum.resource_account, wdjsum.outside_processing_account, -- End revision for version 1.22 wdjsum.period_start_date, wdjsum.schedule_close_date, wdjsum.period_name, -- Revision for version 1.22 wdjsum.report_type, -- Revision for version 1.10 wdjsum.acct_period_id, wdjsum.primary_cost_method, wdjsum.organization_code, wdjsum.class_type ), -- Revision for version 1.22 wdj_assys as (select distinct wdj.primary_item_id, wdj.organization_id, wdj.primary_cost_method from wdj) ----------------main query starts here-------------- select res_sum.report_type Report_Type, nvl(gl.short_name, gl.name) Ledger, haou2.name Operating_Unit, res_sum.organization_code Org_Code, res_sum.period_name Period_Name, &segment_columns res_sum.class_code WIP_Class, ml1.meaning Class_Type, we.wip_entity_name WIP_Job, (select ppa.segment1 from pa_projects_all ppa where ppa.project_id = res_sum.project_id) Project_Number, ml2.meaning Job_Status, -- Revision for version 1.21 res_sum.creation_date Creation_Date, res_sum.scheduled_start_date Scheduled_Start_Date, -- End revision for version 1.21 res_sum.date_released Date_Released, res_sum.date_completed Date_Completed, res_sum.date_closed Date_Closed, res_sum.last_update_date Last_Update_Date, muomv.uom_code UOM_Code, msiv_fg.std_lot_size Item_Std_Lot_Size, -- Revision for version 1.22 cic.cost_type Lot_Size_Cost_Type, nvl(cic.lot_size, 1) Costing_Lot_Size, res_sum.start_quantity Start_Quantity, res_sum.quantity_completed Assembly_Quantity_Completed, res_sum.quantity_scrapped Assembly_Quantity_Scrapped, res_sum.fg_total_qty Total_Assembly_Quantity, msiv_fg.concatenated_segments Assembly, msiv_fg.description Assembly_Description, -- Revision for version 1.22 fl.meaning Rolled_Up, cic.last_rollup_date Last_Cost_Rollup, -- End revision for version 1.22 fcl.meaning Item_Type, misv.inventory_item_status_code Item_Status, ml4.meaning Make_Buy_Code, &category_columns -- Revision for version 1.22 res_sum.lot_number Lot_Number, msiv_osp.concatenated_segments Outside_Processing_Item, msiv_osp.description OSP_Description, (select poh.segment1 from po_headers_all poh where poh.po_header_id = res_sum.po_header_id) PO_Number, decode(res_sum.line_num, 0, null, res_sum.line_num) Line_Number, decode(res_sum.release_num, 0, null, res_sum.release_num) PO_Release, bd.department_code Department, res_sum.operation_seq_num Operation_Seq_Number, res_sum.resource_seq_num Resource_Seq_Number, res_sum.resource_code Resource_Code, -- Revision for version 1.22 res_sum.description Resource_Description, -- Revision for version 1.25 ml5.meaning Auto_Charge, ml6.meaning Std_Rate, -- End revision for version 1.25 ml3.meaning Basis_Type, -- Revision for version 1.25 res_sum.po_currency_code PO_Currency_Code, -- Revision for version 1.17 decode(res_sum.line_num, 0, null, res_sum.po_unit_price) PO_Unit_Price, res_sum.cost_type Resource_Cost_Type, -- Revision for version 1.21 gl.currency_code Currency_Code, res_sum.resource_rate Resource_Rate, res_sum.res_unit_of_measure Resource_UOM_Code, res_sum.usage_rate_or_amount Quantity_Per_Assembly, round(res_sum.total_req_quantity,3) Total_Required_Quantity, round(res_sum.applied_resource_units,3) Total_Units_Applied, -- Revision for version 1.17 -- res_sum.qty_variance Quantity_Variance, round(res_sum.applied_resource_units - res_sum.total_req_quantity,3) Quantity_Variance, res_sum.std_resource_value Standard_Resource_Value, round(res_sum.applied_resource_value,2) Applied_Resource_Value, -- res_sum.res_efficiency_variance Resource_Efficiency_Variance, round(res_sum.applied_resource_value - res_sum.std_resource_value,2) Resource_Efficiency_Variance from mtl_system_items_vl msiv_fg, mtl_system_items_vl msiv_osp, mtl_units_of_measure_vl muomv, mtl_item_status_vl misv, bom_departments bd, wip_entities we, mfg_lookups ml1, -- WIP Class mfg_lookups ml2, -- WIP Status mfg_lookups ml3, -- Basis Type mfg_lookups ml4, -- Planning Make Buy -- Revision for version 1.25 mfg_lookups ml5, -- Autocharge Type mfg_lookups ml6, -- Standard Rate -- End revision for version 1.25 -- Revision for version 1.22 fnd_lookups fl, -- Rolled Up fnd_common_lookups fcl, -- Item Type gl_code_combinations gcc, -- wip job accounts hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units haou2, gl_ledgers gl, -- Revision for version 1.22 -- cst_item_costs cic, -- Get the assembly cost type, lot size and cost rollup status (select cic.organization_id, cic.inventory_item_id, cct.cost_type, cic.lot_size, case when sum(case when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then 1 else 0 end) > 0 then 'Y' else 'N' end rolled_up, max(case when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then cicd.creation_date else null end) last_rollup_date from cst_item_costs cic, cst_item_cost_details cicd, cst_cost_types cct, -- Limit to assemblies on WIP jobs wdj_assys where cic.organization_id = cicd.organization_id (+) and cic.inventory_item_id = cicd.inventory_item_id (+) and cic.cost_type_id = cicd.cost_type_id (+) and cic.inventory_item_id = wdj_assys.primary_item_id and cic.organization_id = wdj_assys.organization_id and cct.cost_type_id = cic.cost_type_id -- Revision for version 1.22 and 1.23 and cct.cost_type = decode(:p_cost_type, null, (select cct.cost_type from dual where cct.cost_type_id = wdj_assys.primary_cost_method ), :p_cost_type ) group by cic.organization_id, cic.inventory_item_id, cct.cost_type, cic.cost_type_id, cic.lot_size union all select cic.organization_id, cic.inventory_item_id, cct.cost_type, cic.lot_size, case when sum(case when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then 1 else 0 end) > 0 then 'Y' else 'N' end rolled_up, max(case when cic.based_on_rollup_flag = 1 and cicd.rollup_source_type = 3 and cicd.attribute15 is null then cicd.creation_date else null end) last_rollup_date from cst_item_costs cic, cst_item_cost_details cicd, cst_cost_types cct, -- Limit to assemblies on WIP jobs wdj_assys where cic.cost_type_id = cicd.cost_type_id (+) and cic.inventory_item_id = cicd.inventory_item_id (+) and cic.organization_id = cicd.organization_id (+) and cic.inventory_item_id = wdj_assys.primary_item_id and cic.organization_id = wdj_assys.organization_id and cic.cost_type_id = wdj_assys.primary_cost_method -- this gets the Frozen Costs and cct.cost_type_id <> wdj_assys.primary_cost_method -- this avoids getting the Frozen costs twice -- Revision for version 1.22 and 1.23 and cct.cost_type = decode(:p_cost_type, null, (select cct.cost_type from dual where cct.cost_type_id = wdj_assys.primary_cost_method ), :p_cost_type ) -- ==================================== -- Find all the Frozen costs not in the -- Pending or unimplemented cost type -- ==================================== and not exists ( select 'x' from cst_item_costs cic2 where cic2.organization_id = cic.organization_id and cic2.inventory_item_id = cic.inventory_item_id and cic2.cost_type_id = cct.cost_type_id) group by cic.organization_id, cic.inventory_item_id, cct.cost_type, cic.cost_type_id, cic.lot_size ) cic, -- End revision for version 1.22 -- ======================================================== -- Get the WIP Resource Information in a three part union -- which is then condensed into a summary data set -- ======================================================== -- ======================================================== -- Section I Condense into a summary data set. -- ======================================================= (select res.report_type, res.period_name, res.organization_code, res.organization_id, res.primary_cost_method, res.account, res.class_code, res.class_type, res.wip_entity_id, res.project_id, res.status_type, res.primary_item_id, -- Revision for version 1.22 res.lot_number, -- Revision for version 1.21 res.creation_date, res.scheduled_start_date, -- End revision for version 1.21 res.date_released, res.date_completed, res.date_closed, res.last_update_date, res.start_quantity, res.quantity_completed, res.quantity_scrapped, res.fg_total_qty, max(res.po_header_id) po_header_id, max(res.line_num) line_num, max(res.release_num) release_num, max(res.purchase_item_id) purchase_item_id, res.department_id, -- Revision for version 1.22 1 level_num, res.operation_seq_num, -- Revision for version 1.25 res.autocharge_type, res.standard_rate_flag, res.po_currency_code, -- End revision for version 1.25 res.resource_seq_num, res.resource_code, -- Revision for version 1.22 res.description, res.basis_type, res.res_unit_of_measure, max(res.po_unit_price) po_unit_price, res.cost_type, res.resource_rate, sum(res.usage_rate_or_amount) usage_rate_or_amount, sum(res.total_req_quantity) total_req_quantity, sum(res.applied_resource_units) applied_resource_units, -- Revision for version 1.17 -- sum(res.qty_variance) qty_variance, -- Revision for version 1.17 sum(res.std_resource_value) std_resource_value, sum(res.applied_resource_value) applied_resource_value from -- ======================================================== -- Section II.A.OSP -- First get the OSP resource information with the related -- PO number and unit price information -- ======================================================= -- Revision for version 1.22 (select 'II.A' section, wdj.report_type, wdj.period_name, wdj.organization_code, wdj.organization_id, -- Revision for version 1.22 -- cct.cost_type primary_cost_type, wdj.primary_cost_method, -- End revision for version 1.22 wdj.outside_processing_account account, wdj.class_code, wdj.class_type, wdj.wip_entity_id, wdj.project_id, wdj.status_type, wdj.primary_item_id, -- Revision for version 1.22 wdj.lot_number, -- Revision for version 1.21 wdj.creation_date, wdj.scheduled_start_date, -- End revision for version 1.21 wdj.date_released, wdj.date_completed, wdj.date_closed, wdj.last_update_date, wdj.start_quantity, wdj.quantity_completed, wdj.quantity_scrapped, nvl(wdj.quantity_completed,0) + nvl(wdj.quantity_scrapped,0) fg_total_qty, poh.po_header_id, pol.line_num, pr.release_num, br.purchase_item_id, wo.department_id, wo.operation_seq_num, wor.resource_seq_num, br.resource_code, -- Revision for version 1.22 br.description, wor.basis_type, -- Revision for version 1.25 wor.autocharge_type, wor.standard_rate_flag, poh.currency_code po_currency_code, -- End revision for version 1.25 br.unit_of_measure res_unit_of_measure, nvl(pll.price_override, pol.unit_price) po_unit_price, crc.cost_type cost_type, nvl(crc.resource_rate,0) resource_rate, nvl(wor.usage_rate_or_amount,0) usage_rate_or_amount, -- Revision for version 1.22 -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close' -- use the completions plus scrap quantities unless for lot-based jobs nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis * decode(wdj.class_type, 5, nvl(wdj.quantity_completed, 0), nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0)) ) ) else -- else use the start quantity times the usage rate or amount decode(:p_use_completion_qtys, 'Y', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis * decode(wdj.class_type, 5, nvl(wdj.quantity_completed, 0), nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0)) ) ), -- Revision for version 1.24 'N', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) * nvl(wdj.start_quantity, 0) -- Any other basis ) ) end ,6),0) total_req_quantity, nvl(applied_resource_units,0) applied_resource_units, -- Revision for version 1.17 and 1.22 -- If the job status is "Complete" then use the completions plus -- scrap quantities else use the planned required quantities; and -- use the completions plus scrap quantities unless for lot-based jobs -- Get the total required quantity nvl(round(case when wdj.status_type in (4,5,7,12,14,15) then decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot nvl(wor.usage_rate_or_amount,0) -- Any other basis * decode(wdj.class_type, 5, nvl(wdj.quantity_completed, 0), nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wdj.quantity_scrapped, 0)) ) ) else -- else use the start quantity times the usage rate or amount decode(:p_use_completion_qtys, 'Y', decode(wor.basis_type, 2, nvl(wor.usage_rate_or_amount,0), -- Lot |