CAC WIP Jobs With Complete Status Which Are Ready for Close

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report WIP jobs which have a status of "Complete", do not exceed variance tolerances, have completed or exceeded the WIP start quantity, with no open material requirements, no unearned OSP (outside processing) charges and no stuck transactions in interfaces. When you include scrap quantities, any scrapped assemblies are counted with the completed units. Note that for material requirements, expen ...  Report WIP jobs which have a status of "Complete", do not exceed variance tolerances, have completed or exceeded the WIP start quantity, with no open material requirements, no unearned OSP (outside processing) charges and no 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.
-- | Copyright 2017 - 2022 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Original Author: Douglas A. Volz
-- |
-- | Program Name: xxx_wip_jobs_status_eligible.sql
-- |
-- | Parameters:
-- | p_wip_var_threshold -- Maximum WIP variance or current job balance that is allowed
-- | for jobs you wish to close
-- | p_wip_var_percent -- Maximum WIP variance percentage that is allowed for jobs you
-- | wish to close. Based on WIP Job Balance / WIP Costs In.
-- | p_include_scrap -- Include scrap for quantity requirements
-- | p_include_bulk_items -- Include bulk WIP supply types in the component requirements
-- | p_assembly_number -- Enter the specific assembly number you wish to report (optional)
-- | p_wip_job -- Specific WIP job (optional)
-- | p_job_status -- Specific WIP job status (optional)
-- | p_wip_class_code -- Specific WIP class code (optional)
-- | p_org_code -- Specific inventory organization you wish to report (optional)
-- | p_operating_unit -- Operating_Unit you wish to report, leave blank for all
-- | operating units (optional)
-- | p_ledger -- general ledger you wish to report, leave blank for all
-- | ledgers (optional)
-- |
-- | Description:
-- | Report to determine which Jobs are at the status "Complete" and are eligible
-- | for moving to the status "Complete - No Charges".
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============= =========================================
-- | 1.0 16 MAR 2017 Douglas Volz Initial Coding
-- | 1.3 27 MAR 2017 Douglas Volz Fix "return more than one row" error for
-- | correlated sub-query on OSP and add in
-- | check for purchase requisitions
-- | 1.4 27 APR 2017 Douglas Volz Fix for cross-joining results
-- | 1.5 25 May 2017 Douglas Volz Added business code, product family,
-- | product line and package code item categories
-- | 1.6 25 Oct 2017 Douglas Volz Remove p_date_completed parameter, not needed
-- | 1.7 25 Jul 2018 Douglas Volz Removed all categories except Inventory
-- | 1.8 25 Jul 2018 Douglas Volz Removed all category values
-- | 1.9 11 Dec 2020 Douglas Volz Now for Standard, Lot Based Standard and Non-
-- | Standard Asset Jobs. Added another category.
-- | 1.10 26 Jan 2021 Douglas Volz Check for unissued materials and WIP scrap controls
-- | and now use multi-language tables or views.
-- | 1.11 11 Feb 2021 Douglas Volz Added parameter to include scrap for requirements
-- | 1.12 05 Mar 2021 Douglas Volz Added parameter to include bulk items for requirements.
-- | 1.13 12 Mar 2021 Douglas Volz Add logic to ignore Phantom WIP Supply Types as
-- | these requirements are never issued.
-- | 1.14 15 Apr 2021 Douglas Volz Added Date Released
-- | 1.15 10 Jul 2022 Douglas Volz Added WIP Variance Percentage parameter.
-- +=============================================================================+*/   more
Run CAC WIP Jobs With Complete Status Which Are 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 Item_Number,
 msiv.description Item_Description,
 -- 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,