CAC WIP Period Balances to Accounting Activity Reconciliation

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report to compare the monthly WIP Period Balances with the pre-Create Accounting WIP accounting entries for material, resource, overhead, outside processing, job close variance and standard cost update transactions. With WIP class, job status, name and other details. This report shows both WIP jobs which were open during the accounting period as well as jobs closed during the accounting period.  ...  Report to compare the monthly WIP Period Balances with the pre-Create Accounting WIP accounting entries for material, resource, overhead, outside processing, job close variance and standard cost update transactions. With WIP class, job status, name and other details. This report shows both WIP jobs which were open during the accounting period as well as jobs closed during the accounting period. If the stored WIP period balances agree to the period WIP accounting activity, the "Difference" columns have a zero amount.

//* +=============================================================================+
-- | Copyright 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_reconcile_wip_balances.sql
-- |
-- | Parameters:
-- | p_period_name -- The desired accounting period you wish to report
-- | 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 compare the monthly WIP transactions against the WIP period balances.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0 07 Apr 2021 Douglas Volz Initial Coding based on item_cost_history.sql
-- | 1.1 10 Jul 2022 Douglas Volz Add Ledger and Operating Unit columns
-- | 1.2 19 Oct 2022 Douglas Volz Bug fix for missing organization join
-- +=============================================================================+*/


   more
Run CAC WIP Period Balances to Accounting Activity Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
select wipsum.period_name Period_Name,
 -- Revision for version 1.1
 nvl(gl.short_name, gl.name) Ledger,
 haou2.name Operating_Unit,
 -- End revision for version 1.1
 wip.organization_code Org_Code,
 wip.organization_id Org_Id,
 msiv.concatenated_segments Assembly_Number,
 msiv.description Description,
 fcl.meaning Item_Type,
 misv.inventory_item_status_code Item_Status,
 ml1.meaning Make_Buy_Code,
 wac.class_code WIP_Class,
 ml2.meaning Class_Type,
 ml3.meaning WIP_Type,
 wip.wip_entity_name WIP_Job,
 wip.date_closed Date_Closed,
 wip.status Job_Status,
 wip.date_released Date_Released,
 wip.date_completed Date_Completed,
 muomv.uom_code UOM_Code,
 wip.start_quantity Start_Quantity,
 wip.quantity_completed Quantity_Completed,
 wip.quantity_scrapped Quantity_Scrapped,
 wipsum.wip_costs_in WIP_Costs_In,
 wipsum.wip_costs_out WIP_Costs_Out,
 wipsum.wip_close_var WIP_Relief,
 wipsum.wip_value WIP_Value,
 wipsum.wip_acct_in WIP_Accounted_Costs_In,
 wipsum.wip_acct_out WIP_Accounted_Costs_Out,
 wipsum.wip_acct_var WIP_Accounted_Relief,
 wipsum.wip_acct_value WIP_Accounted_Value,
 wipsum.wip_costs_in - wipsum.wip_acct_in WIP_Costs_In_Difference,
 wipsum.wip_costs_out - wipsum.wip_acct_out WIP_Costs_Out_Difference,
 wipsum.wip_close_var  - wipsum.wip_acct_var WIP_Relief_Difference,
 wipsum.wip_value - wipsum.wip_acct_value WIP_Value_Difference
from wip_accounting_classes wac,
 mtl_system_items_vl msiv,
 mtl_item_status_vl misv,
 mtl_units_of_measure_vl muomv,
 mfg_lookups ml1, -- planning make/buy code
 mfg_lookups ml2, -- class type
 mfg_lookups ml3, -- WIP type
 fnd_common_lookups fcl, -- item type
 hr_organization_information hoi,
 hr_all_organization_units_vl haou,  -- inv_organization_id
 hr_all_organization_units_vl haou2, -- operating unit
 gl_ledgers gl,
 -- ===========================================
 -- Get WIP_Jobs and Flow Schedule information
 -- ===========================================
 (select mp.organization_id,
  mp.organization_code,
  we.wip_entity_name,
  wdj.wip_entity_id,
  we.entity_type,
  wdj.class_code,
  wdj.date_closed,
  ml.meaning status,
  wdj.date_released,
  wdj.date_completed,
  wdj.start_quantity,
  wdj.quantity_completed,
  wdj.quantity_scrapped,
  wdj.primary_item_id
  from wip_discrete_jobs wdj,
  wip_entities we,
  org_acct_periods oap,
  mtl_parameters mp,
  mfg_lookups ml
  where we.wip_entity_id               = wdj.wip_entity_id
  and oap.organization_id            = wdj.organization_id
  -- Revision for version 1.2
  and mp.organization_id             = wdj.organization_id
  and ml.lookup_type                 = 'WIP_JOB_STATUS'
  and ml.lookup_code                 = wdj.status_type
  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_period_name
  -- 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 trunc(wdj.creation_date) <=  oap.schedule_close_date
   )
    or -- the job is closed and ...the job was closed after the accounting period 
   (wdj.date_closed is not null
    and trunc(wdj.date_closed) > oap.schedule_close_date
   )
    or -- find jobs that were closed during the report period
   (wdj.date_closed >= oap.period_start_date 
    and trunc(wdj.date_closed) < oap.schedule_close_date + 1
   )
  )
  union all
  select mp.organization_id,
  mp.organization_code,
  we.wip_entity_name,
  wfs.wip_entity_id,
  we.entity_type,
  wfs.class_code,
  wfs.date_closed,
  ml.meaning status,
  wfs.creation_date date_released,
  wfs.date_closed date_completed,
  wfs.planned_quantity start_quantity,
  wfs.quantity_completed,
  0 quantity_scrapped,
  wfs.primary_item_id
  from wip_flow_schedules wfs,
  wip_entities we,
  org_acct_periods oap,
  mtl_parameters mp,
  mfg_lookups ml
  where we.wip_entity_id               = wfs.wip_entity_id
  and we.entity_type                 = 4 -- Flow schedule
  and oap.organization_id            = wfs.organization_id
  -- Revision for version 1.2
  and mp.organization_id             = wfs.organization_id
  and ml.lookup_type                 = 'WIP_FLOW_SCHEDULE_STATUS'
  and ml.lookup_code                 = wfs.status
  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_period_name
  -- 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 ((wfs.date_closed is null -- the job is open
    and trunc(wfs.creation_date) <=  oap.schedule_close_date
   )
    or -- the job is closed and ...the job was closed after the accounting period 
   (wfs.date_closed is not null
    and trunc(wfs.date_closed) > oap.schedule_close_date
   )
    or -- find jobs that were closed during the report period
   (wfs.date_closed >= oap.period_start_date 
    and trunc(wfs.date_closed) < oap.schedule_close_date + 1
   )
  )
 ) wip,
 -- ===========================================
 -- Condense WIP Balances and Accounting Entries
 -- ===========================================
 (select wip.organization_id,
  wip.wip_entity_id,
  wip.period_name,
  wip.acct_period_id,
  sum(wip_costs_in) wip_costs_in,
  sum(wip_costs_out) wip_costs_out,
  sum(wip_close_var) wip_close_var,
  sum(wip_value) wip_value,
  sum(round(wip_acct_in,2)) wip_acct_in,
  sum(round(wip_acct_out,2)) wip_acct_out,
  sum(round(wip_acct_var,2)) wip_acct_var,
  sum(round(wip_acct_value,2)) wip_acct_value
  from (
   -- ===========================================
   -- Get the WIP Period Balances
   -- ===========================================
   select mp.organization_id,
   wpb.wip_entity_id,
   oap.period_name,
   wpb.acct_period_id,
   sum(nvl(tl_scrap_in,0)+
   nvl(pl_material_in,0)+
   nvl(pl_material_overhead_in,0)+
   nvl(tl_resource_in,0)+
   nvl(pl_resource_in,0)+
   nvl(tl_outside_processing_in,0)+
   nvl(pl_outside_processing_in,0)+
   nvl(tl_overhead_in,0)+
   nvl(pl_overhead_in,0))  wip_costs_in,
   sum(nvl(tl_material_out,0)+
   nvl(tl_scrap_out,0)+
   nvl(pl_material_out,0)+
   nvl(tl_material_overhead_out,0)+
   nvl(pl_material_overhead_out,0)+
   nvl(tl_resource_out,0)+
   nvl(pl_resource_out,0)+
   nvl(tl_outside_processing_out,0)+
   nvl(pl_outside_processing_out,0)+
   nvl(tl_overhead_out,0)+
   nvl(pl_overhead_out,0)) wip_costs_out,
   sum(nvl(tl_material_var,0)+
   nvl(tl_scrap_var,0)+
   nvl(pl_material_var,0)+
   nvl(tl_material_overhead_var,0)+
   nvl(pl_material_overhead_var,0)+
   nvl(tl_resource_var,0)+
   nvl(pl_resource_var,0)+
   nvl(tl_outside_processing_var,0)+
   nvl(pl_outside_processing_var,0)+
   nvl(tl_overhead_var,0)+
   nvl(pl_overhead_var,0)) wip_close_var,
   sum(nvl(tl_scrap_in,0)+
   nvl(pl_material_in,0)-
   nvl(tl_material_out,0)-
   nvl(tl_scrap_out,0)-
   nvl(pl_material_out,0)-
   nvl(tl_material_var,0)-
   nvl(tl_scrap_var,0)-
   nvl(pl_material_var,0)+
   nvl(pl_material_overhead_in,0)-
   nvl(tl_material_overhead_out,0)-
   nvl(pl_material_overhead_out,0)-
   nvl(tl_material_overhead_var,0)-
   nvl(pl_material_overhead_var,0)+
   nvl(tl_resource_in,0)+
   nvl(pl_resource_in,0)-
   nvl(tl_resource_out,0)-
   nvl(pl_resource_out,0)-
   nvl(tl_resource_var,0)-
   nvl(pl_resource_var,0)+
   nvl(tl_outside_processing_in,0)+
   nvl(pl_outside_processing_in,0)-
   nvl(tl_outside_processing_out,0)-
   nvl(pl_outside_processing_out,0)-
   nvl(tl_outside_processing_var,0)-
   nvl(pl_outside_processing_var,0)+
   nvl(tl_overhead_in,0)+
   nvl(pl_overhead_in,0)-
   nvl(tl_overhead_out,0)-
   nvl(pl_overhead_out,0)-
   nvl(tl_overhead_var,0)-
   nvl(pl_overhead_var,0)) wip_value,
   sum(0) wip_acct_in,
   sum(0) wip_acct_out,
   sum(0) wip_acct_var,
   sum(0) wip_acct_value
   from wip_period_balances wpb,
   org_acct_periods oap,
   mtl_parameters mp
   where mp.organization_id             = wpb.organization_id
   and oap.acct_period_id             = wpb.acct_period_id
   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_period_name
   group by
   mp.organization_id,
   wpb.wip_entity_id,
   oap.period_name,
   wpb.acct_period_id
   union all
   -- ===========================================
   -- Get the WIP Material Transactions
   -- ===========================================
   select mp.organization_id,
   mmt.transaction_source_id wip_entity_id,
   oap.period_name,
   mmt.acct_period_id,
   sum(0) wip_costs_in,
   sum(0) wip_costs_out,
   sum(0) wip_close_var,
   sum(0) wip_value,
   sum(decode(mmt.transaction_type_id,
    35, mta.base_transaction_value, -- WIP Component Issue
    38, mta.base_transaction_value, -- WIP Component Negative Issue
    43, mta.base_transaction_value, -- WIP Return
    48, mta.base_transaction_value, -- WIP Negative Return
    55, mta.base_transaction_value, -- WIP Lot Split
    56, mta.base_transaction_value, -- WIP Lot Merge
    57, mta.base_transaction_value, -- WIP Lot Bonus
     0)
      ) wip_acct_in,
   sum(decode(mmt.transaction_type_id,
    17, mta.base_transaction_value * -1, -- WIP Completion Return
    44, mta.base_transaction_value * -1, -- WIP Completion
    90, mta.base_transaction_value * -1, -- WIP Assembly Scrap
    91, mta.base_transaction_value * -1, -- WIP return from scrap
    92, mta.base_transaction_value * -1, -- WIP estimated scrap
    1002, mta.base_transaction_value * -1, -- WIP Byproduct Completion
    1003, mta.base_transaction_value * -1, -- WIP Byproduct Return
     0)
      ) wip_acct_out,
   sum(0) wip_acct_var,
   sum(decode(mmt.transaction_type_id,
    35, mta.base_transaction_value, -- WIP Component Issue
    38, mta.base_transaction_value, -- WIP Component Negative Issue
    43, mta.base_transaction_value, -- WIP Return
    48, mta.base_transaction_value, -- WIP Negative Return
    55, mta.base_transaction_value, -- WIP Lot Split
    56, mta.base_transaction_value, -- WIP Lot Merge
    57, mta.base_transaction_value, -- WIP Lot Bonus
    17, mta.base_transaction_value, -- WIP Completion Return
    44, mta.base_transaction_value, -- WIP Completion
    90, mta.base_transaction_value, -- WIP Assembly Scrap
    91, mta.base_transaction_value, -- WIP return from scrap
    92, mta.base_transaction_value, -- WIP estimated scrap
    1002, mta.base_transaction_value, -- WIP Byproduct Completion
    1003, mta.base_transaction_value, -- WIP Byproduct Return
     0)
      ) wip_acct_value
   from mtl_transaction_accounts mta,
   mtl_material_transactions mmt,
   org_acct_periods oap,
   mtl_parameters mp
   where mta.transaction_id             = mmt.transaction_id
   and mp.organization_id             = mta.organization_id
   and mta.transaction_source_type_id = 5
   and mta.accounting_line_type       = 7 -- WIP valuation
   and mmt.transaction_date          >= oap.period_start_date
   and mmt.transaction_date          <  oap.schedule_close_date + 1
   and mta.transaction_date          >= oap.period_start_date
   and mta.transaction_date          <  oap.schedule_close_date + 1
   and oap.acct_period_id             = mmt.acct_period_id
   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_period_name
   group by
   mp.organization_id,
   mmt.transaction_source_id,
   oap.period_name,
   mmt.acct_period_id
   union all
   -- ===========================================
   -- Get the WIP_Resource Transactions
   -- ===========================================
   select mp.organization_id,
   wt.wip_entity_id,
   oap.period_name,
   wt.acct_period_id,
   sum(0) wip_costs_in,
   sum(0) wip_costs_out,
   sum(0) wip_close_var,
   sum(0) wip_value,
   sum(decode(wt.transaction_type,
     1, wta.base_transaction_value, -- Resource transaction
     2, wta.base_transaction_value, -- Overhead transaction
     3, wta.base_transaction_value, -- Outside processing
    11, wta.base_transaction_value, -- WIP Lot Split
    12, wta.base_transaction_value, -- WIP Lot Merge
    13, wta.base_transaction_value, -- WIP Lot Bonus
    14, wta.base_transaction_value, -- WIP Lot Quantity Update
     0)
      ) wip_acct_in,
   sum(decode(wt.transaction_type,
    15, wta.base_transaction_value, -- Estimated Scrap Absorption
    16, wta.base_transaction_value, -- Estimated Scrap Reallocation
    17, wta.base_transaction_value, -- Direct Shopfloor Delivery
     0)
      ) wip_acct_out,
   sum(decode(wt.transaction_type,
     5, wta.base_transaction_value * -1, -- Period close variance
     6, wta.base_transaction_value * -1, -- Job close variance
     7, wta.base_transaction_value * -1, -- Final completion variance
     0)
      ) wip_acct_var,
   sum(decode(wt.transaction_type,
     1, wta.base_transaction_value, -- Resource transaction
     2, wta.base_transaction_value, -- Overhead transaction
     3, wta.base_transaction_value, -- Outside processing
    11, wta.base_transaction_value, -- WIP Lot Split
    12, wta.base_transaction_value, -- WIP Lot Merge
    13, wta.base_transaction_value, -- WIP Lot Bonus
    14, wta.base_transaction_value, -- WIP Lot Quantity Update
    15, wta.base_transaction_value, -- Estimated Scrap Absorption
    16, wta.base_transaction_value, -- Estimated Scrap Reallocation
    17, wta.base_transaction_value, -- Direct Shopfloor Delivery
     5, wta.base_transaction_value, -- Period close variance
     6, wta.base_transaction_value, -- Job close variance
     7, wta.base_transaction_value, -- Final completion variance
     0)
      ) wip_acct_value
   from wip_transaction_accounts wta,
   wip_transactions wt,
   org_acct_periods oap,
   mtl_parameters mp
   where wta.transaction_id             = wt.transaction_id
   and mp.organization_id             = wta.organization_id
   and wta.accounting_line_type       = 7 -- WIP valuation
   and wta.transaction_date          >= oap.period_start_date
   and wta.transaction_date          <  oap.schedule_close_date + 1
   and wt.transaction_date           >= oap.period_start_date
   and wt.transaction_date           <  oap.schedule_close_date + 1
   and oap.acct_period_id             = wt.acct_period_id
   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_period_name
   group by
   mp.organization_id,
   wt.wip_entity_id,
   oap.period_name,
   wt.acct_period_id
   union all
   -- ===========================================
   -- Get the WIP Cost Update Transactions
   -- ===========================================
   select mp.organization_id,
   cscav.wip_entity_id,
   oap.period_name,
   oap.acct_period_id,
   sum(0) wip_costs_in,
   sum(0) wip_costs_out,
   sum(0) wip_close_var,
   sum(0) wip_value,
   round(sum(decode(cscav.transaction_type,
     3, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- WIP component issue
     6, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Resource
     7, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Resource overhead
     8, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Item based overhead
     9, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Lot based overhead
    10, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Resource / Overhead
     0)
      ),2) wip_acct_in,
   round(sum(decode(cscav.transaction_type,
     4, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- WIP assembly completion
     5, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- WIP scrap transaction
     0)
      ),2) wip_acct_out,
   sum(0) wip_acct_var,
   round(sum(decode(cscav.transaction_type,
     3, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- WIP component issue
     6, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Resource
     7, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Resource overhead
     8, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Item based overhead
     9, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Lot based overhead
    10, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost), -- Resource / Overhead
     4, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost) * cscav.in_out_flag, -- WIP assembly completion
     5, cscav.adjustment_quantity * (new_unit_cost - old_unit_cost) * cscav.in_out_flag, -- WIP scrap transaction
     0)
      ),2) wip_acct_value
   from cst_std_cost_adj_values cscav,
   cst_cost_updates ccu,
   org_acct_periods oap,
   mtl_parameters mp
   where ccu.cost_update_id             = cscav.cost_update_id
   and mp.organization_id             = ccu.organization_id
   and ccu.update_date               >= oap.period_start_date
   and ccu.update_date               <  oap.schedule_close_date + 1
   and oap.organization_id            = ccu.organization_id
   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_period_name
   group by
   mp.organization_id,
   cscav.wip_entity_id,
   oap.period_name,
   oap.acct_period_id
  ) wip
  group by
   wip.organization_id,
  wip.wip_entity_id,
  wip.period_name,
  wip.acct_period_id
 ) wipsum