CAC WIP Account Value

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to show WIP values and all accounts for discrete manufacturing, in summary by inventory, organization, with WIP class, job status, name and other details. This report uses the valuation accounts from each discrete job and reports both jobs which were open during the accounting period as well as jobs closed during the accounting period. You can also run this report for earlier accounting p ...  Report to show WIP values and all accounts for discrete manufacturing, in summary by inventory, organization, with WIP class, job status, name and other details. This report uses the valuation accounts from each discrete job and reports both jobs which were open during the accounting period as well as jobs closed during the accounting period. You can also run this report for earlier accounting periods and still get the correct amounts and the jobs that were open at that time.

/* +=============================================================================+
-- | Copyright 2009 - 2022 Douglas Volz Consulting, Inc. |
-- | All rights reserved. |
-- | Permission to use this code is granted provided the original author is |
-- | acknowledged. No warranties, express or otherwise is included in this |
-- | permission. |
-- +=============================================================================+
-- |
-- | Original Author: Douglas Volz ([email protected])
-- |
-- | Program Name: xxx_wip_acct_value_rept.sql
-- |
-- | Parameters:
-- | 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)
-- | p_period_name -- Enter the Period Name you wish to report for WIP Period
-- | balances (mandatory)
-- | p_include_expense_wip -- Enter Y to include Expense WIP jobs. Defaults to N (no).
-- | p_category_set1 -- The first item category set to report, typically the
-- | Cost or Product Line Category Set
-- | p_category_set2 -- The second item category set to report, typically the
-- | Inventory Category Set
-- |
-- | Description:
-- | Report to show WIP values and all accounts for discrete manufacturing, in
-- | summary by inventory, organization, with WIP class, job status, name and other details.
-- | This report uses the valuation accounts from each discrete job and reports both jobs
-- | which were open during the accounting period as well as jobs closed during the accounting
-- | period. You can also run this report for earlier accounting periods and still get the
-- | correct amounts and the jobs that were open at that time.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 29 Oct 2009 Douglas Volz Based on XXX_WIP_VALUE_REPT.sql
-- | 1.17 19 Jun 2019 Douglas Volz Changed to G/L short name, for brevity, added
-- | inventory category. Added Date Released column.
-- | 1.18 24 Oct 2019 Douglas Volz Added aging dates, creation date and date released columns
-- | 1.19 06 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters.
-- | 1.20 24 Apr 2020 Douglas Volz Changed to multi-language views for the item
-- | master, item categories and operating units.
-- | And put the WIP Costs In, WIP Costs Out, WIP
-- | Relief and WIP Value as the last report columns.
-- | Add Project Number and Project Name columns.
-- | 1.21 17 Aug 2020 Douglas Volz Change categories to use category_concat_segs not segment1
-- | 1.22 09 Oct 2020 Douglas Volz Added unit of measure column
-- | 1.23 13 Mar 2022 Douglas Volz Added WIP job description column.
+=============================================================================+*/


   more
Run CAC WIP Account Value 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,
 wip_value.period_name Period_Name,
 &segment_columns
 wip_value.class_code WIP_Class,
 ml2.meaning Class_Type,
 we.wip_entity_name WIP_Job,
 -- Revision for version 1.23
 regexp_replace(we.description,'[^[:alnum:]'' '']', null) Job_Description,
 wip_value.status Job_Status,
 -- Revision for version 1.18
 we.creation_date Creation_Date,
 -- Revision for version 1.8
 wip_value.scheduled_start_date Scheduled_Start_Date,
 -- Revision for version 1.17
 wip_value.date_released Date_Released,
 wip_value.date_completed Date_Completed,
 wip_value.date_closed Date_Closed,
 -- Revision for version 1.18
 case 
  when (wip_value.schedule_close_date - we.creation_date) < 31  then '30 days'
  when (wip_value.schedule_close_date - we.creation_date) < 61  then '60 days'
  when (wip_value.schedule_close_date - we.creation_date) < 91  then '90 days'
  when (wip_value.schedule_close_date - we.creation_date) < 121 then '120 days'
  when (wip_value.schedule_close_date - we.creation_date) < 151 then '150 days'
  when (wip_value.schedule_close_date - we.creation_date) < 181 then '180 days'
  else 'Over 180 days'
 end Aged_Creation_Date,
 case 
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - nvl(wip_value.date_released,(nvl(wip_value.date_completed,sysdate)))) < 31  then '30 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - nvl(wip_value.date_released,(nvl(wip_value.date_completed,sysdate)))) < 61  then '60 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - nvl(wip_value.date_released,(nvl(wip_value.date_completed,sysdate)))) < 91  then '90 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - nvl(wip_value.date_released,(nvl(wip_value.date_completed,sysdate)))) < 121 then '120 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - nvl(wip_value.date_released,(nvl(wip_value.date_completed,sysdate)))) < 151 then '150 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - nvl(wip_value.date_released,(nvl(wip_value.date_completed,sysdate)))) < 181 then '180 days'
  else 'Over 180 days'
 end Aged_Compln_vs_Release_Date,
 case 
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - we.creation_date) < 31  then '30 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - we.creation_date) < 61  then '60 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - we.creation_date) < 91  then '90 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - we.creation_date) < 121 then '120 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - we.creation_date) < 151 then '150 days'
  when (nvl(wip_value.date_completed,wip_value.schedule_close_date) - we.creation_date) < 181 then '180 days'
  else 'Over 180 days'
 end Aged_Compln_vs_Creation_Date,
 -- End revision for version 1.18
 -- Revision for version 1.8 and 1.22
 muomv.uom_code UOM_Code,
 wip_value.start_quantity Start_Quantity,
 wip_value.quantity_completed Quantity_Completed,
 wip_value.quantity_scrapped Quantity_Scrapped,
 wip_value.quantity_completed + quantity_scrapped Total_Quantity,
 wip_value.completion_subinventory Completion_Subinventory,
 -- Revision for version 1.12
 msub.description Subinventory_Description,
 msiv.concatenated_segments  Item_Number,
 msiv.description Item_Description,
 xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
 -- Revision for version 1.13
&category_columns
 -- Revision for version 1.20
 pp.segment1 Project_Number,
 pp.name Project_Name,
 wip_value.lot_number Lot_Number,
 gl.currency_code Currency_Code,
 sum(wip_value.wip_matl_value) WIP_Material_Value,
 sum(wip_value.wip_moh_value) WIP_Material_Overhead_Value,
 sum(wip_value.wip_res_value) WIP_Resource_Value,
 sum(wip_value.wip_osp_value) WIP_Outside_Processing_Value,
 sum(wip_value.wip_ovhd_value) WIP_Overhead_Value,
 sum(wip_value.wip_scrap_value) WIP_Scrap_Value,
 sum(wip_value.wip_costs_in) WIP_Costs_In,
 sum(wip_value.wip_costs_out) WIP_Costs_Out,
 sum(wip_value.wip_relief) WIP_Relief,
 sum(wip_value.wip_value) WIP_Value
from gl_ledgers gl,
 gl_code_combinations gcc,
 hr_organization_information hoi,
 hr_all_organization_units_vl haou,  -- inv_organization_id
 hr_all_organization_units_vl haou2, -- operating unit
 mtl_system_items_vl msiv,
 -- Revision for version 1.22
 mtl_units_of_measure_vl muomv,
 -- Revision for version 1.20
 pa_projects_all pp,
 mtl_parameters mp,
 wip_accounting_classes wac,
 wip_entities we,
 -- Revision for version 1.12
 mtl_secondary_inventories msub,
 mfg_lookups ml2,
 -- ===========================================
 -- Inline table select for WIP Period Balances
 -- ===========================================
 -- =====================================================
 -- First get the material value for the WIP_Jobs
 -- =====================================================
 (select oap.period_name period_name,
  -- Revision for version 1.18
  oap.schedule_close_date,
  wpb.acct_period_id acct_period_id,
  wpb.organization_id organization_id,
  wdj.material_account code_combination_id,
  wdj.class_code class_code,
  wdj.wip_entity_id wip_entity_id,
  ml.meaning status,
  -- Revision for version 1.8
  wdj.scheduled_start_date,
  -- Revision for version 1.17
  wdj.date_released,
  wdj.date_completed date_completed,
  wdj.date_closed date_closed,
  -- Revision for version 1.14
  decode(wdj.attribute13, null, 'No', 'Yes') converted_job,
  wdj.start_quantity start_quantity,
  wdj.quantity_completed quantity_completed,
  wdj.quantity_scrapped quantity_scrapped,
  -- Revision for version 1.9
  wdj.completion_subinventory completion_subinventory,
  wdj.primary_item_id inventory_item_id,
  -- Revision for version 1.20
  wdj.project_id,
  -- Revision for version 1.8
  wdj.lot_number,
  sum(nvl(wpb.pl_material_in,0)) wip_costs_in,
  sum(nvl(wpb.tl_material_out,0) +
      nvl(wpb.pl_material_out,0)) wip_costs_out,
  sum(nvl(wpb.tl_material_var,0)+
      nvl(wpb.pl_material_var,0)) wip_relief,
  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)) wip_value,
  -- Revision for version 1.9
  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)) wip_matl_value,
  sum(0) wip_moh_value,
  sum(0) wip_res_value,
  sum(0) wip_osp_value,
  sum(0) wip_ovhd_value,
  sum(0) wip_scrap_value
  -- End revision for version 1.9
  from wip_period_balances wpb,
  wip_discrete_jobs wdj,
  org_acct_periods oap,
  mfg_lookups ml
  -- ===========================================
  -- WIP_Job Entity and accounting period joins
  -- ===========================================
  where wpb.wip_entity_id         = wdj.wip_entity_id
  -- bug fix for version 1.1
  -- and wpb.acct_period_id        = oap.acct_period_id
  and wpb.acct_period_id       <= oap.acct_period_id
  and wpb.organization_id       = oap.organization_id 
  -- end fix for version 1.1
  and 4=4                      -- p_period_name
  -- ===========================================
  -- Inventory accounting period joins to limit
  -- to wip activity within the accounting period.
  -- ===========================================
  -- Limit to jobs closed after the period start date
  and nvl(trunc(wdj.date_closed), oap.period_start_date) >= oap.period_start_date
  -- ===========================================
  -- Lookup Code joins
  -- ===========================================
  and ml.lookup_type              = 'WIP_JOB_STATUS'
  and ml.lookup_code              = wdj.status_type 
  group by
  oap.period_name,
  -- Revision for version 1.18
  oap.schedule_close_date,
  wpb.acct_period_id,
  wpb.organization_id,
  wdj.material_account,
  wdj.class_code,
  wdj.wip_entity_id,
  ml.meaning,
  -- Revision for version 1.8
  wdj.scheduled_start_date,
  -- Revision for version 1.17
  wdj.date_released,
  wdj.date_completed,
  wdj.date_closed,
  -- Revision for version 1.14
  decode(wdj.attribute13, null, 'No', 'Yes'),
  wdj.start_quantity,
  wdj.quantity_completed,
  wdj.quantity_scrapped,
  -- Revision for version 1.9
  wdj.completion_subinventory,
  wdj.primary_item_id,
  -- Revision for version 1.20
  wdj.project_id,
  -- Revision for version 1.8
  wdj.lot_number
  -- =====================================================
  -- Now get the material overhead Value for the WIP_Jobs
  -- =====================================================
  union all
  select oap.period_name period_name,
  -- Revision for version 1.18
  oap.schedule_close_date,
  wpb.acct_period_id acct_period_id,
  wpb.organization_id organization_id,
  wdj.material_overhead_account code_combination_id,
  wdj.class_code class_code,
  wdj.wip_entity_id wip_entity_id,
  ml.meaning status,
  -- Revision for version 1.8
  wdj.scheduled_start_date,
  -- Revision for version 1.17
  wdj.date_released,
  wdj.date_completed date_completed,
  wdj.date_closed date_closed,
  -- Revision for version 1.14
  decode(wdj.attribute13, null, 'No', 'Yes') converted_job,
  wdj.start_quantity start_quantity,
  wdj.quantity_completed quantity_completed,
  wdj.quantity_scrapped quantity_scrapped,
  -- Revision for version 1.9
  wdj.completion_subinventory,
  wdj.primary_item_id inventory_item_id,
  -- Revision for version 1.20
  wdj.project_id,
  -- Revision for version 1.8
  wdj.lot_number,
  sum(nvl(wpb.pl_material_overhead_in,0))  wip_costs_in,
  sum(nvl(wpb.tl_material_overhead_out,0)+
      nvl(wpb.pl_material_overhead_out,0)) wip_costs_out,
  sum(nvl(wpb.tl_material_overhead_var,0)+
      nvl(wpb.pl_material_overhead_var,0)) wip_relief,
   sum(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)) wip_value,
  -- Revision for version 1.9
  sum(0) wip_matl_value,
   sum(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)) wip_moh_value,
  sum(0) wip_res_value,
  sum(0) wip_osp_value,
  sum(0) wip_ovhd_value,
  sum(0) wip_scrap_value
  -- End revision for version 1.9
  from wip_period_balances wpb,
  wip_discrete_jobs wdj,
  org_acct_periods oap,
  mfg_lookups ml
  -- ===========================================
  -- WIP_Job Entity and accounting period joins
  -- ===========================================
  where wpb.wip_entity_id         = wdj.wip_entity_id
  -- bug fix for version 1.1
  -- and wpb.acct_period_id        = oap.acct_period_id
  and wpb.acct_period_id       <= oap.acct_period_id
  and wpb.organization_id       = oap.organization_id 
  -- end fix for version 1.1
  and 4=4                      -- p_period_name
  -- ===========================================
  -- Inventory accounting period joins to limit
  -- to wip activity within the accounting period.
  -- ===========================================
  -- Limit to jobs closed after the period start date
  and nvl(trunc(wdj.date_closed), oap.period_start_date) >= oap.period_start_date
  -- ===========================================
  -- Lookup Code joins
  -- ===========================================
  and ml.lookup_type              = 'WIP_JOB_STATUS'
  and ml.lookup_code              = wdj.status_type 
  group by
  oap.period_name,
  -- Revision for version 1.18
  oap.schedule_close_date,
  wpb.acct_period_id,
  wpb.organization_id,
  wdj.material_overhead_account,
  wdj.class_code,
  wdj.wip_entity_id,
  ml.meaning,
  -- Revision for version 1.8
  wdj.scheduled_start_date,
  -- Revision for version 1.17
  wdj.date_released,
  wdj.date_completed,
  wdj.date_closed,
  -- Revision for version 1.14
  decode(wdj.attribute13, null, 'No', 'Yes'),
  wdj.start_quantity,
  wdj.quantity_completed,
  wdj.quantity_scrapped,
  -- Revision for version 1.9
  wdj.completion_subinventory,
  wdj.primary_item_id,
  -- Revision for version 1.20
  wdj.project_id,
  -- Revision for version 1.8
  wdj.lot_number
  -- =====================================================
  -- Now get the resource Value for the WIP_Jobs
  -- =====================================================
  union all
  select oap.period_name period_name,
  -- Revision for version 1.18
  oap.schedule_close_date,
  wpb.acct_period_id acct_period_id,
  wpb.organization_id organization_id,
  wdj.resource_account code_combination_id,
  wdj.class_code class_code,
  wdj.wip_entity_id wip_entity_id,
  ml.meaning status,
  -- Revision for version 1.8
  wdj.scheduled_start_date,
  -- Revision for version 1.17
  wdj.date_released,
  wdj.date_completed date_completed,
  wdj.date_closed date_closed,
  -- Revision for version 1.14
  decode(wdj.attribute13, null, 'No', 'Yes') converted_job,
  wdj.start_quantity start_quantity,
  wdj.quantity_completed quantity_completed,
  wdj.quantity_scrapped quantity_scrapped,
  -- Revision for version 1.9
  wdj.completion_subinventory,
  wdj.primary_item_id inventory_item_id,
  -- Revision for version 1.20
  wdj.project_id,
  -- Revision for version 1.8
  wdj.lot_number,
  sum(nvl(wpb.tl_resource_in,0)+
      nvl(wpb.pl_resource_in,0))  wip_costs_in,
  sum(nvl(wpb.tl_resource_out,0)+
      nvl(wpb.pl_resource_out,0)) wip_costs_out,
  sum(nvl(wpb.tl_resource_var,0)+
      nvl(wpb.pl_resource_var,0)) wip_relief,
   sum(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)) wip_value,
  -- Revision for version 1.9
  sum(0) wip_matl_value,
  sum(0) wip_moh_value,
  sum(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)) wip_res_value,
  sum(0) wip_osp_value,
  sum(0) wip_ovhd_value,
  sum(0) wip_scrap_value
  -- End revision for version 1.9
  from  wip_period_balances wpb,
  wip_discrete_jobs wdj,
  org_acct_periods oap,
  mfg_lookups ml
  -- ===========================================
  -- WIP_Job Entity and accounting period joins
  -- ===========================================
  where wpb.wip_entity_id         = wdj.wip_entity_id
  -- bug fix for version 1.1
  -- and wpb.acct_period_id        = oap.acct_period_id
  and    wpb.acct_period_id       <= oap.acct_period_id
  and wpb.organization_id       = oap.organization_id 
  -- end fix for version 1.1
  and 4=4                      -- p_period_name
  -- ===========================================
  -- Inventory accounting period joins to limit
  -- to wip activity within the accounting period.
  -- ===========================================
  -- Limit to jobs closed after the period start date
  and nvl(trunc(wdj.date_closed), oap.period_start_date) >= oap.period_start_date
  -- ===========================================
  -- Lookup Code joins
  -- ===========================================
  and ml.lookup_type              = 'WIP_JOB_STATUS'
  and ml.lookup_code              = wdj.status_type 
  group by
  oap.period_name,
  -- Revision for version 1.18
  oap.schedule_close_date,
  wpb.acct_period_id,
  wpb.organization_id,
  wdj.resource_account,
  wdj.class_code,
  wdj.wip_entity_id,
  ml.meaning,
  -- Revision for version 1.8
  wdj.scheduled_start_date,
  -- Revision for version 1.17
  wdj.date_released,
  wdj.date_completed,
  wdj.date_closed,
  -- Revision for version 1.14
  decode(wdj.attribute13, null, 'No', 'Yes'),
  wdj.start_quantity,
  wdj.quantity_completed,
  wdj.quantity_scrapped,
  -- Revision for version 1.9
  wdj.completion_subinventory,
  wdj.primary_item_id,
  -- Revision for version 1.20
  wdj.project_id,
  -- Revision for version 1.8
  wdj.lot_number
  -- ================