CAC WIP Jobs With Complete Status But Not Ready for Close
Description
Categories: Enginatics
Repository: Github
Repository: Github
Report WIP jobs which have a status of "Complete" but exceed variance tolerances, have uncompleted assemblies, open material requirements, unearned OSP (outside processing) charges or stuck transactions in interfaces. When you include scrap quantities, any scrapped assemblies are counted with the completed units. Note that for material requirements, expense items are ignored.
/* +======== ... more
/* +======== ... more
Run
CAC WIP Jobs With Complete Status But Not Ready for Close 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, -- Revision for version 1.6 -- 'p_date_completed' For_Completion_Date, wac.class_code WIP_Class, ml1.meaning Class_Type, we.wip_entity_name WIP_Job, ml2.meaning Job_Status, -- Revision for version 1.14 wdj.date_released Date_Released, wdj.date_completed Date_Completed, wdj.last_update_date Last_Update_Date, wdj.completion_subinventory Completion_Subinventory, msiv.concatenated_segments Assembly_Number, msiv.description Assembly_Description, -- Revision for version 1.14 fcl.meaning Item_Type, misv.inventory_item_status_code Item_Status, ml3.meaning Make_Buy_Code, -- End revision for version 1.14 -- Revision for version 1.9 &category_columns -- End revision for version 1.9 -- Revision for version 1.10 muomv.uom_code UOM_Code, nvl(wdj.start_quantity, 0) Start_Quantity, nvl(wdj.quantity_completed, 0) Quantity_Completed, nvl(wdj.quantity_scrapped, 0) Quantity_Scrapped, nvl(wdj.quantity_completed, 0) + nvl(wdj.quantity_scrapped, 0) Total_Quantity, -- Check for completion quantities -- Revision for version 1.10, check if WIP scrap is financially recorded case when (wdj.quantity_completed + decode(:p_include_scrap,'N',0, wdj.quantity_scrapped)) = 0 then (select fl.meaning from fnd_lookups fl where fl.lookup_type = 'YES_NO_ALL' and fl.lookup_code = 'N') -- No completion quantities when (wdj.start_quantity - (wdj.quantity_completed + decode(:p_include_scrap,'N',0, wdj.quantity_scrapped))) = 0 then (select fl.meaning from fnd_lookups fl where fl.lookup_type = 'YES_NO_ALL' and fl.lookup_code = 'A') -- All quantities completed when (wdj.start_quantity - (wdj.quantity_completed + decode(:p_include_scrap,'N',0, wdj.quantity_scrapped))) > 0 then (select ml.meaning from mfg_lookups ml where ml.lookup_type = 'SYS_RANGE' and ml.lookup_code = 2) -- Partial quantities completed else (select fl.meaning from fnd_lookups fl where fl.lookup_type = 'YES_NO_ALL' and fl.lookup_code = 'A') -- All quantities completed end Quantities_Completed, -- Revision for version 1.10, check for WIP with material quantities not issued (select max(fl.meaning) from wip_requirement_operations wro, mtl_system_items_b msi, wip_parameters wp, fnd_lookups fl where wro.wip_entity_id = wdj.wip_entity_id and wro.organization_id = wdj.organization_id and msi.organization_id = wro.organization_id and msi.inventory_item_id = wro.inventory_item_id -- Only want to check valued items, not expense items and msi.inventory_asset_flag = 'Y' and wp.organization_id = wdj.organization_id and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' -- Revision for version 1.12 and 2=2 -- Include WIP bulk supply types -- Revision for version 1.13 and wro.wip_supply_type <> 6 -- Phantom -- Calculate the quantity required based on the completion quantities -- Use the completion quantities with scrap quantities unless scrap is not financially recorded -- Basis of 2 indicates the component is issued per lot not per assembly and the component yield factor is ignored having round(sum(decode(wro.basis_type, null, nvl(wro.quantity_per_assembly, 0) * 1 / nvl(wro.component_yield_factor, 1) * (nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped, 0))), 1, nvl(wro.quantity_per_assembly, wdj.start_quantity) * 1 / nvl(wro.component_yield_factor, 1) * (nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped, 0))), 2, nvl(wro.required_quantity,1), nvl(wro.quantity_per_assembly, 0) * 1 / nvl(wro.component_yield_factor, 1) * (nvl(wdj.quantity_completed, 0) + decode(:p_include_scrap,'N',0,nvl(wdj.quantity_scrapped, 0))) ) ) ,3) - round(sum(nvl(wro.quantity_issued, 0)),3) > 0 ) Open_Material_Quantities, -- Quantity_Left_in_WIP -- End of revision for version 1.10 -- Check for WIP Operation Resources with no earned OSP -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from wip_operation_resources wor, fnd_lookups fl where wor.wip_entity_id = wdj.wip_entity_id and wor.organization_id = wdj.organization_id and wor.autocharge_type in ( 3, 4 ) -- 3 = 'PO receipt' and 4 = 'PO move' and nvl(wor.applied_resource_units,0) = 0 and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) No_Earned_OSP, gl.currency_code Currency_Code, round( sum(nvl(wpb.pl_material_in,0)- nvl(wpb.tl_material_out,0)- nvl(wpb.pl_material_out,0)- nvl(wpb.tl_material_var,0)- nvl(wpb.pl_material_var,0) + -- Material Overhead Balance nvl(wpb.pl_material_overhead_in,0)- nvl(wpb.tl_material_overhead_out,0)- nvl(wpb.pl_material_overhead_out,0)- nvl(wpb.tl_material_overhead_var,0)- nvl(wpb.pl_material_overhead_var,0) + -- Resource Balance nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)- nvl(wpb.tl_resource_out,0)- nvl(wpb.pl_resource_out,0)- nvl(wpb.tl_resource_var,0)- nvl(wpb.pl_resource_var,0)+ -- Outside Processing Balance nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)- nvl(wpb.tl_outside_processing_out,0)- nvl(wpb.pl_outside_processing_out,0)- nvl(wpb.tl_outside_processing_var,0)- nvl(wpb.pl_outside_processing_var,0) + -- Overhead Balance nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)- nvl(wpb.tl_overhead_out,0)- nvl(wpb.pl_overhead_out,0)- nvl(wpb.tl_overhead_var,0)- nvl(wpb.pl_overhead_var,0) + -- Estimated Scrap Balances nvl(wpb.tl_scrap_in,0)- nvl(wpb.tl_scrap_out,0)- nvl(wpb.tl_scrap_var,0) ),2) WIP_Value, -- Revision for version 1.15 round( sum(nvl(wpb.pl_material_in,0)+ nvl(wpb.pl_material_overhead_in,0)+ nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)+ nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+ nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)+ nvl(wpb.tl_scrap_in,0) ) ) WIP_Costs_In, -- WIP variance percentage = WIP Net Value / WIP Costs In -- WIP Net Value round( sum(nvl(wpb.pl_material_in,0)- nvl(wpb.tl_material_out,0)- nvl(wpb.pl_material_out,0)- nvl(wpb.tl_material_var,0)- nvl(wpb.pl_material_var,0) + -- Material Overhead Balance nvl(wpb.pl_material_overhead_in,0)- nvl(wpb.tl_material_overhead_out,0)- nvl(wpb.pl_material_overhead_out,0)- nvl(wpb.tl_material_overhead_var,0)- nvl(wpb.pl_material_overhead_var,0) + -- Resource Balance nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)- nvl(wpb.tl_resource_out,0)- nvl(wpb.pl_resource_out,0)- nvl(wpb.tl_resource_var,0)- nvl(wpb.pl_resource_var,0)+ -- Outside Processing Balance nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)- nvl(wpb.tl_outside_processing_out,0)- nvl(wpb.pl_outside_processing_out,0)- nvl(wpb.tl_outside_processing_var,0)- nvl(wpb.pl_outside_processing_var,0) + -- Overhead Balance nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)- nvl(wpb.tl_overhead_out,0)- nvl(wpb.pl_overhead_out,0)- nvl(wpb.tl_overhead_var,0)- nvl(wpb.pl_overhead_var,0) + -- Estimated Scrap Balances nvl(wpb.tl_scrap_in,0)- nvl(wpb.tl_scrap_out,0)- nvl(wpb.tl_scrap_var,0) ) / decode( sum(nvl(wpb.pl_material_in,0)+ nvl(wpb.pl_material_overhead_in,0)+ nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)+ nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+ nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)+ nvl(wpb.tl_scrap_in,0) ), 0, 1, sum(nvl(wpb.pl_material_in,0)+ nvl(wpb.pl_material_overhead_in,0)+ nvl(wpb.tl_resource_in,0)+ nvl(wpb.pl_resource_in,0)+ nvl(wpb.tl_outside_processing_in,0)+ nvl(wpb.pl_outside_processing_in,0)+ nvl(wpb.tl_overhead_in,0)+ nvl(wpb.pl_overhead_in,0)+ nvl(wpb.tl_scrap_in,0) ) ) ,3) * 100 WIP_Variance_Percent, -- Check for Unprocessed Material -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from mtl_material_transactions_temp mmtt, fnd_lookups fl where mmtt.organization_id = wdj.organization_id and mmtt.transaction_source_type_id = 5 and mmtt.transaction_source_id = wdj.wip_entity_id and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Unprocessed_Material, -- Check for Uncosted Material -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from mtl_material_transactions mmt1, fnd_lookups fl where mmt1.transaction_source_id = wdj.wip_entity_id and mmt1.organization_id = wdj.organization_id and mmt1.transaction_source_type_id = 5 and mmt1.costed_flag is not null and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Uncosted_Material, -- Check for Pending WIP Costing -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from wip_cost_txn_interface wcti, fnd_lookups fl where wcti.wip_entity_id = wdj.wip_entity_id and wcti.organization_id = wdj.organization_id and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_WIP_Costing, -- Check for Pending Receiving -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from rcv_transactions_interface rti, fnd_lookups fl where rti.wip_entity_id = wdj.wip_entity_id and rti.to_organization_id = wdj.organization_id and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_Receiving, -- Revision for version 1.3 -- Check for Pending Purchase Requisitions (select max(fl.meaning) from po_requisitions_interface pri, wip_operation_resources wor, fnd_lookups fl where pri.wip_entity_id = wdj.wip_entity_id and pri.destination_organization_id = wdj.organization_id and wor.wip_entity_id = wdj.wip_entity_id and wor.organization_id = wdj.organization_id and wor.autocharge_type in ( 3, 4 ) -- 3 = 'PO receipt' and 4 = 'PO move' -- Only for jobs with no applied resource units, to avoid -- selecting duplicate purchase requisition interface entries and nvl(wor.applied_resource_units,0) = 0 and fl.lookup_type = 'YES_NO' and fl.lookup_code = 'Y' ) Pending_Requisitions, -- Check for Pending Material -- Revision for version 1.3 -- nvl((select 'Yes' (select max(fl.meaning) from mtl_transactions_interface mti, wip_discrete_jobs wdj, fnd_lookups fl where mti.transaction_source_id = wdj.wip_entity_id and mti.organization_id = wdj.organization_id and mti.transaction_source_type_ |