CAC WIP Material Usage with Configuration and Lot Variances

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Report your material usage variances for your open and closed WIP jobs. If the job is open the Report Type column displays "Valuation" as this WIP job and potential material usage variance is still in your WIP inventory balances. If the job has been closed during the reporting period, the Report Type column displays "Variance" as this WIP job was written off on a WIP Job Close Variance transacti ...  Report your material usage variances for your open and closed WIP jobs. If the job is open the Report Type column displays "Valuation" as this WIP job and potential material usage variance is still in your WIP inventory balances. If the job has been closed during the reporting period, the Report Type column displays "Variance" as this WIP job was written off on a WIP Job Close Variance transaction. You can report prior periods and this report will automatically adjust the assembly completion quantities and component issue quantities to reflect the quantities for the specified accounting period, as well as report only jobs which were open or closed during that prior period.

Closed, Pending Close, Cancelled, Complete and Complete No Charges WIP job statuses use the completion quantities. All other WIP jobs use the parameter "Use Completion Quantities". And if you use Standard Costing, for standard discrete jobs this report also shows your configuration variances; the difference between your WIP BOM and your primary or standard BOM. Non-standard jobs usually do not have configuration variances, as they are "non-standard" without standard BOM requirements.

Parameters:
==========
Report Option: Open jobs, Closed jobs or All jobs. Use this to limit the size of the report. (mandatory)
Period Name: the accounting period you wish to report. (mandatory)
Cost Type: defaults to your Costing Method; if the cost type is missing component costs the report will find any missing item costs from your Costing Method cost type. (optional)
Include Scrap Quantities: for calculating your completion quantities and component quantity requirements, include or exclude any scrapped assembly quantities. (mandatory)
Include Unreleased Jobs: include jobs which have not been released and are not started. (mandatory)
Include Bulk Supply Items: include Bulk items to match the results from the Oracle Discrete Job Value Report; exclude knowing that Bulk items are usually not issued to the WIP job. (mandatory)
Use Completion Qtys: for jobs in a released status, use the completion quantities for the material usage and configuration variance calculations. Useful if you backflush your materials based on your completion quantities. Complete, Complete - No Charges, Cancelled, Closed, Pending Close or Failed Close alway use the completion quantities in the variance calculations. (mandatory)
Config/Lot Variances for Non-Std: calculate configuration and lot variances for non-standard jobs.
Include Unimplemented ECOs: include future BOMs changes. (mandatory)
Alternate BOM Designator: if you save your BOMs during your Cost Rollups (based on your Cost Type step ups), use this parameter to get the correct BOMs for the configuration variance calculations. If you leave this field blank the report uses the latest BOM component effectivity date up to the period close date. (optional)
Category Set 1: any item category you wish (optional).
Category Set 2: any item category you wish (optional).
Class Code: specific type of WIP class to report (optional).
Job Status: specific WIP job status (optional).
WIP Job: specific WIP job (optional).
Assembly Number: specific assembly number you wish to report (optional)
Component Number: specific component item you wish to report (optional)
Organization Code: specific inventory organization, defaults to your session's inventory organization (optional).

-- | Copyright 2009 - 2022 Douglas Volz Consulting, Inc.
-- | All rights reserved.
-- | Version Modified on Modified by Description
-- | ======= =========== =============== =========================================
-- | 1.0 12 Oct 2020 Douglas Volz Initial Coding Based on ICP WIP Component Var and ICP WIP
-- | Component Valuation
-- | 1.31 12 Oct 2022 Douglas Volz Fix divide by zero error with the start quantity.
-- | ======= =========== =============== =========================================   more 
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,
  -- Revision for version 1.22
  trunc(wdj.date_closed) date_closed,
  wdj.last_update_date,
  wdj.primary_item_id,
  msiv.concatenated_segments assembly_number,
  msiv.description assy_description,
  msiv.item_type assy_item_type,
  msiv.inventory_item_status_code assy_item_status_code,
  msiv.primary_uom_code assy_uom_code,
  msiv.planning_make_buy_code,
  msiv.std_lot_size,
  wdj.lot_number,
  wdj.status_type,
  wdj.start_quantity,
  wdj.net_quantity,
  wdj.project_id,
  wdj.material_account,
  wdj.quantity_completed,
  wdj.quantity_scrapped,
  oap.period_start_date,
  oap.schedule_close_date,
  oap.period_name,
  -- Revision for version 1.12
  (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.12
  -- 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,
  mtl_system_items_vl msiv
  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
  and msiv.organization_id            = wdj.organization_id
  and msiv.inventory_item_id          = wdj.primary_item_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.assembly_number,
  wdjsum.assy_description,
  wdjsum.assy_item_type,
  wdjsum.assy_item_status_code,
  wdjsum.assy_uom_code,
  wdjsum.planning_make_buy_code,
  wdjsum.std_lot_size,
  wdjsum.lot_number,
  wdjsum.status_type,
  wdjsum.start_quantity,
  wdjsum.net_quantity,
  wdjsum.project_id,
  wdjsum.material_account,
  wdjsum.period_start_date,
  wdjsum.schedule_close_date,
  wdjsum.period_name,
  -- Revision for version 1.12
  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.assembly_number,
   wdj0.assy_description,
   wdj0.assy_item_type,
   wdj0.assy_item_status_code,
   wdj0.assy_uom_code,
   wdj0.planning_make_buy_code,
   wdj0.std_lot_size,
   wdj0.lot_number,
   wdj0.status_type,
   wdj0.start_quantity,
   wdj0.net_quantity,
   wdj0.project_id,
   wdj0.material_account,
   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.12
   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
   -- Revision for version 1.23
   and mmt.transaction_type_id in (17, 44, 90, 91)
   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.assembly_number,
  wdjsum.assy_description,
  wdjsum.assy_item_type,
  wdjsum.assy_item_status_code,
  wdjsum.assy_uom_code,
  wdjsum.planning_make_buy_code,
  wdjsum.std_lot_size,
  wdjsum.lot_number,
  wdjsum.status_type,
  wdjsum.start_quantity,
  wdjsum.net_quantity,
  wdjsum.project_id,
  wdjsum.material_account,
  wdjsum.period_start_date,
  wdjsum.schedule_close_date,
  wdjsum.period_name,
  -- Revision for version 1.12
  wdjsum.report_type,
  -- Revision for version 1.10
  wdjsum.acct_period_id,
  wdjsum.primary_cost_method,
  wdjsum.organization_code,
  wdjsum.class_type
 ),
wdj_assys as
 (select distinct wdj.primary_item_id,
  wdj.organization_id,
  wdj.primary_cost_method,
  wdj.assembly_number,
  wdj.assy_description,
  wdj.assy_item_type,
  wdj.assy_item_status_code,
  wdj.assy_uom_code,
  wdj.planning_make_buy_code,
  wdj.std_lot_size,
  -- Revision for version 1.20
  wdj.schedule_close_date
  from wdj),
-- Revision for version 1.22
-- Assembly cost type and lot information
cic_assys as
 (select cic.organization_id,
  cic.inventory_item_id,
  cct.cost_type,
  cic.cost_type_id,
  nvl(cic.lot_size,1) 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
  and cct.cost_type                = decode(:p_cost_type,                                                -- 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,
  nvl(cic.lot_size,1)
  union all
  select cic.organization_id,
  cic.inventory_item_id,
  cct.cost_type,
  cic.cost_type_id,
  nvl(cic.lot_size,1) 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
  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
  and cct.cost_type                = decode(:p_cost_type,                                                -- 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,
  nvl(cic.lot_size,1)
 ),
-- Get the Component Cost Basis Type and Item Costs
cic_comp as
 (select cic.inventory_item_id,
  cic.organization_id,
  cic.last_update_date,
  cct.cost_type_id,
  cct.cost_type,
  sum(case
   when cicd.level_type = 1 and cicd.cost_element_id = 1 and cicd.basis_type = 2 then 1 -- material lot basis type
   when cicd.level_type = 1 and cicd.cost_element_id = 2 and cicd.basis_type = 2 then 1 -- moh lot basis type
   when cicd.level_type = 2 and cicd.basis_type = 2 then 1 -- previous level lot basis type
   else 0
      end) lot_basis_type,
  sum(case
   when cicd.level_type = 1 and cicd.cost_element_id = 1 and cicd.basis_type = 2 then cicd.item_cost -- material lot basis cost
   when cicd.level_type = 1 and cicd.cost_element_id = 2 and cicd.basis_type = 2 then cicd.item_cost -- moh lot basis cost
   when cicd.level_type = 2 and cicd.basis_type = 2 then cicd.item_cost -- previous level lot basis cost
   else 0
      end) lot_basis_cost,
  -- Revision for version 1.28
  -- sum(case
  --  when cicd.level_type = 1 and cicd.cost_element_id = 1 and cicd.basis_type = 2 then 0 -- material item basis type
  --  when cicd.level_type = 1 and cicd.cost_element_id = 2 and cicd.basis_type = 2 then 0 -- moh item basis type
  --  when cicd.level_type = 2 and cicd.basis_type = 2 then 0 -- previous level item basis type
  --  else 1
  --     end) item_basis_type,
  -- End revision for version 1.28
  sum(case
   when cicd.level_type = 1 and cicd.cost_element_id = 1 and cicd.basis_type = 2 then 0 -- material item basis cost
   when cicd.level_type = 1 and cicd.cost_element_id = 2 and cicd.basis_type = 2 then 0 -- moh item basis cost
   when cicd.level_type = 2 and cicd.basis_type = 2 then 0 -- previous level item basis cost
   else cicd.item_cost
      end) item_basis_cost,
  -- Revision for version 1.28
  nvl(cic.lot_size,1) lot_size,
  nvl(cic.item_cost,0) item_cost
  from cst_item_cost_details cicd,
  cst_item_costs cic,
  cst_cost_types cct,
  mtl_parameters mp
  where mp.organization_id           = cic.organization_id
  and cic.cost_type_id             = cct.cost_type_id
  and 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 cct.cost_type                = decode(:p_cost_type,                                                -- p_cost_type
       null, (select cct.cost_type 
              from   dual 
              where  cct.cost_type_id = mp.primary_cost_method
             ), 
       :p_cost_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
  group by
  cic.inventory_item_id,
  cic.organization_id,
  cic.last_update_date,
  cct.cost_type_id,
  cct.cost_type,
  -- Revision for version 1.28
  nvl(cic.lot_size,1),
  nvl(cic.item_cost,0)
  union all
  select cic.inventory_item_id,
  cic.organization_id,
  cic.last_update_date,
  cct.cost_type_id,
  cct.cost_type,
  sum(case
   when cicd.level_type = 1 and cicd.cost_element_id = 1 and cicd.basis_type = 2 then 1 -- material lot basis type
   when cicd.level_type = 1 and cicd.cost_element_id = 2 and cicd.basis_type = 2 then 1 -- moh lot basis type
   when cicd.level_type = 2 and cicd.basis_type = 2 then 1 -- previous level lot basis type
   else 0
      end) lot_basis_type,
  sum(case
   when cicd.level_type = 1 and cicd.cost_element_id = 1 and cicd.basis_type = 2 then cicd.item_cost -- material lot basis cost
   when cicd.level_type = 1 and cicd.cost_element_id = 2 and cicd.basis_type = 2 then cicd.item_cost -- moh lot basis cost
   when cicd.level_type = 2 and cicd.basis_type = 2 then cicd.item_cost -- previous level lot basis cost
   else 0
      end) lot_basis_cost,
  -- Revision for version 1.28
  -- sum(case
  --  when cicd.level_type = 1 and cicd.cost_element_id = 1 and cicd.basis_type = 2 then 0 -- material item basis type
  --  when cicd.level_type = 1 and cicd.cost_element_id = 2 and cicd.basis_type = 2 then 0 -- moh item basis type
  --  when cicd.level_type = 2 and cicd.basis_type = 2 then 0 -- previous level item basis type
  --  else 1
  --     end) item_basis_type,
  -- End revision for version 1.28
  sum(case
   when cicd.level_type = 1 and cicd.cost_element_id = 1 and cicd.basis_type = 2 then 0 -- material item basis cost
   when cicd.level_type = 1 and cicd.cost_element_id = 2 and cicd.basis_type = 2 then 0 -- moh item basis cost
   when cicd.level_type = 2 and cicd.basis_type = 2 then 0 -- previous level item basis cost
   else cicd.item_cost
      end) item_basis_cost,
  -- Revision for version 1.28
  nvl(cic.lot_size,1) lot_size,
  nvl(cic.item_cost,0) item_cost
  from cst_item_cost_details cicd,
  cst_item_costs cic,
  cst_cost_types cct,
  mtl_parameters mp
  where mp.organization_id           = cic.organization_id
  and cic.cost_type_id             = mp.primary_cost_method  -- this gets the Frozen Costs
  and 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 cct.cost_type_id            <> mp.primary_cost_method  -- this avoids getting the Frozen costs twice
  and cct.cost_type                = decode(:p_cost_type,                                                -- p_cost_type
       null, (select cct.cost_type 
              from   dual 
              where  cct.cost_type_id = mp.primary_cost_method
             ), 
       :p_cost_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
  -- ====================================
  -- 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.inventory_item_id,
  cic.organization_id,
  cic.last_update_date,
  cct.cost_type_id,
  cct.cost_type,
  -- Revision for version 1.28
  nvl(cic.lot_size,1),
  nvl(cic.item_cost,0)
 ),
-- Revision for version 1.30
-- Get the list of primary and/or alternate BOMs, to enable joining to phantom subassemblies
bom_list as
  -- Get the primary BOM if the alternate BOM is not null
 (select bsb1.bill_sequence_id,
  bsb1.assembly_item_id,
  bsb1.organization_id,
  bsb1.alternate_bom_designator
  from bom_structures_b bsb1,
  mtl_parameters mp
  where bsb1.organization_id            = mp.organization_id
  and bsb1.assembly_type              = 1   -- Manufacturing
  and bsb1.common_assembly_item_id is null
  and bsb1.alternate_bom_designator   = '&p_alt_bom_designator'
  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 mp.organization_code            = '&p_org_code'
  and '&p_alt_bom_designator' is not null
  union all
  -- Get the primary BOM if the alternate BOM does not exist
  select bsb2.bill_sequence_id,
  bsb2.assembly_item_id,
  bsb2.organization_id,
  bsb2.alternate_bom_designator
  from bom_structures_b bsb2,
  mtl_parameters mp
  where bsb2.alternate_bom_designator is null -- get the primary BOM
  and '&p_alt_bom_designator' is not null
  and bsb2.organization_id            = mp.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 mp.organization_code            = '&p_org_code'
  -- Check to see if the BOM structures exist as an alternate BOM
  and not exists
  (
   select 'x'
   from bom_structures_b bsb
   where bsb.assembly_item_id            = bsb2.assembly_item_id
   and bsb.organization_id             = mp.organization_id
   and bsb.alternate_bom_designator    = '&p_alt_bom_designator'
  )
  union all
  -- Get the primary BOM if the alternate BOM is null
  select bsb3.bill_sequence_id,
  bsb3.assembly_item_id,
  bsb3.organization_id,
  bsb3.alternate_bom_designator
  from bom_structures_b bsb3,
  mtl_parameters mp
  where bsb3.organization_id            = mp.organization_id
  and bsb3.alternate_bom_designator is null
  and '&p_alt_bom_designator' is null
  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 mp.organization_code            = '&p_org_code'
 )
-- End revision for version 1.30
 
----------------main query starts here--------------
 
select mtl_sum.report_type Report_Type,
 nvl(gl.short_name, gl.name) Ledger,
 haou2.name Operating_Unit,
 mtl_sum.organization_code Org_Code,
 mtl_sum.period_name Period_Name,
 &segment_columns
 mtl_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 = mtl_sum.project_id) Project_Number,
 ml2.meaning Job_Status,
 mtl_sum.creation_date Creation_Date,
 -- Revision for version 1.5
 mtl_sum.scheduled_start_date Scheduled_Start_Date,
 mtl_sum.date_released Date_Released,
 mtl_sum.date_completed Date_Completed,
 mtl_sum.date_closed Date_Closed,
 mtl_sum.last_update_date Last_Update_Date,
 muomv.uom_code UOM_Code,
 mtl_sum.std_lot_size Item_Std_Lot_Size,
 -- Revision for version 1.12
 -- mtl_sum.primary_cost_type  Lot_Size_Cost_Type,
 cic_assys.cost_type  Lot_Size_Cost_Type,
 cic_assys.lot_size Assembly_Cost_Lot_Size,
 mtl_sum.start_quantity Start_Quantity,
 mtl_sum.quantity_completed Assembly_Quantity_Completed,
 mtl_sum.quantity_scrapped Assembly_Quantity_Scrapped,
 mtl_sum.fg_total_qty Total_Assembly_Quantity,
 -- Revision for version 1.22
 mtl_sum.assembly_number Assembly,
 mtl_sum.assy_description Assembly_Description,
 -- End revision for version 1.22
 fcl1.meaning Item_Type,
 misv.inventory_item_status_code Item_Status,
 ml3.meaning Make_Buy_Code,
&category_columns
 -- Revision for version 1.7
 mtl_sum.lot_number Lot_Number,
 mtl_sum.operation_seq_num Operation_Seq_Number,
 -- Revision for version 1.27
 mtl_sum.item_num Item_Operation_Seq,
 bd.department_code Department,
 -- Revision for version 1.22
 -- msiv2.concatenated_segments Component,
 -- msiv2.description Component_Description,
 mtl_sum.Component_Number,
 mtl_sum.Component_Description,
 -- End revision for version 1.22
 -- Revision for version 1.6 and 1.20
 -- fl1.meaning Phantom_Parent,
 fcl2.meaning Component_Item_Type,
 misv2.inventory_item_status_code Component_Status_Code,
 ml4.meaning Component_Make_Buy_Code,
 ml5.meaning WIP_Supply_Type,
 ml6.meaning Component_Basis_Type,
 -- Revision for version 1.30
 ml7.meaning Include_in_Rollup,
 mtl_sum.cost_type Cost_Type,
 gl.currency_code Currency_Code,
 mtl_sum.item_cost Component_Item_Cost,
 -- Revision for version 1.28
 mtl_sum.lot_basis_cost Lot_Basis_Cost,
 mtl_sum.comp_lot_size Component_Cost_Lot_Size,
 -- End revision for version 1.28
 muomv2.uom_code UOM_Code, 
 mtl_sum.quantity_per_assembly Quantity_Per_Assembly,
 round(mtl_sum.total_req_quantity,3) Total_Required_Quantity,
 -- Revision for version 1.18
 mtl_sum.last_txn_date Last_Transaction_Date,
 round(mtl_sum.quantity_issued,3) Quantity_Issued,
 -- =============================
 -- Quantity_Left_in_WIP = Quantity_Issued minus the Quantity Required 
 -- =============================
 round(mtl_sum.quantity_issued - mtl_sum.total_req_quantity,3) Quantity_Left_in_WIP,
 mtl_sum.wip_std_component_value WIP_Standard_Component_Value,
 round(mtl_sum.applied_component_value,2) Applied_Component_Value,
 -- Revision for version 1.8 and 1.9
 -- To match the Oracle Discrete Job Value Report, for cancelled wip jobs, turn off 
 -- material usage variances when there are no completions and no applied or charged quantities.
 -- round(mtl_sum.applied_component_value - mtl_sum.wip_std_component_value,2) Material_Usage_Variance,
 case
    when mtl_sum.fg_total_qty = 0 and round(mtl_sum.applied_component_value,2) = 0 then 0
    -- End revision for version 1.9
    else round(mtl_sum.applied_component_value - mtl_sum.wip_std_component_value,2)
 end Material_Usage_Variance,
 -- End revision for version 1.8
 round(mtl_sum.std_quantity_per_assembly,3) Std_Quantity_Per_Assembly,
 round(mtl_sum.std_total_req_quantity,3) Total_Std_Required_Quantity,
 -- =============================
 -- Configuration Qty Variance
 -- =============================
 case
    when nvl(mtl_sum.item_basis_type, 'N') = 'N' then 0
    when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
    when cic_assys.rolled_up = 'N' then 0
    when (nvl(mtl_sum.std_total_req_quantity,0) + nvl(mtl_sum.total_req_quantity,0)) = 0 then 0
    when nvl(mtl_sum.std_total_req_quantity,0) - nvl(mtl_sum.total_req_quantity,0) = 0 then 0
    -- Revision for version 1.8 and 1.9
    -- For all jobs, turn off configuration variances when there are no completions and no activity.
    when mtl_sum.fg_total_qty = 0 and round(mtl_sum.applied_component_value,2) = 0 then 0
    -- End revision for version 1.8 and 1.9
    else round(nvl(mtl_sum.total_req_quantity,0) - nvl(mtl_sum.std_total_req_quantity,0),3)
 end  Configuration_Quantity_Var,
 -- =============================
 -- Configuration Variance
 -- =============================
 case
    when nvl(mtl_sum.item_basis_type, 'N') = 'N' then 0
    when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
    when cic_assys.rolled_up = 'N' then 0
    when (nvl(mtl_sum.std_total_req_quantity,0) + nvl(mtl_sum.total_req_quantity,0)) = 0 then 0
    when nvl(mtl_sum.std_total_req_quantity,0) - nvl(mtl_sum.total_req_quantity,0) = 0 then 0
    -- Revision for version 1.6
    -- Only have configuration variances with Standard Costing (but still want to show qty configuration variances)
    when mtl_sum.primary_cost_method <> 1 then 0
    -- Revision for version 1.9
    -- When no completions, no applied value and no activity there are no configuration variances
    when mtl_sum.fg_total_qty = 0 and round(mtl_sum.applied_component_value,2) = 0 then 0
    -- End revision for version 1.9
    else round((nvl(mtl_sum.total_req_quantity,0) - nvl(mtl_sum.std_total_req_quantity,0)) * mtl_sum.item_cost,2)
    -- End revision for version 1.8
 end  Configuration_Variance,
 -- =============================
 -- WIP Lot Charges Per Unit
 -- =============================
 --   when primary_cost_method is not Frozen (1) then zero
 --   when the item is not lot-based then zero
 --   when the item basis is null then zero
 nvl(case
    -- Revision for version 1.28
    -- when nvl(mtl_sum.lot_basis_type, 'N') = 'N' then 0
    when nvl(mtl_sum.item_basis_type, 'Y') = 'Y' then 0
    -- End revision for version 1.28
    when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
    when cic_assys.rolled_up = 'N' then 0
    when mtl_sum.quantity_per_assembly = 0 then 0
    -- Revision for version 1.10, replace decodes with mtl_sum.status_type in (4,5,7,12,14,15) 
    -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
    when mtl_sum.status_type in (4,5,7,12,14,15)
  then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost /
     (decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))),5)
    when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'Y' -- Released and use completion quantities
  then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost /
     (decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))),5)
    when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'N' -- Released but do not use completion quantities
  -- Revision for version 1.31
  -- then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / mtl_sum.start_quantity,5)
    -- else round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / mtl_sum.start_quantity,5)
  then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / decode(mtl_sum.start_quantity, 0, 1, mtl_sum.start_quantity),5)
    else round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / decode(mtl_sum.start_quantity, 0, 1, mtl_sum.start_quantity),5)
    -- End revision for version 1.31
    -- End revision for version 1.8
 end,0) WIP_Lot_Charges_Per_Unit,
 -- =============================
 -- Standard Lot Charges Per Unit
 -- =============================
 nvl(case
    when nvl(mtl_sum.lot_basis_type, 'N') = 'N' then 0
    when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
    when cic_assys.rolled_up = 'N' then 0
    when mtl_sum.std_quantity_per_assembly = 0 then 0
    -- Revision for version 1.28, use the component lot size
    else round(mtl_sum.std_quantity_per_assembly * mtl_sum.lot_basis_cost / mtl_sum.comp_lot_size,5)
 end,0) Std_Lot_Charges_Per_Unit,
 -- =============================
 -- Lot Size Variance
 -- =============================
 -- (WIP Lot Charges Per Unit - Standard Lot Charges Per Unit) X Quantity
 --   when primary_cost_method is not Frozen (1) then zero
 --   when standard lot size is null then zero
 --   when the item is not lot-based then zero
 --   when the item basis is null then zero
 -- WIP Setup Charges Per Unit
 round((nvl(case
     -- Revision for version 1.28
     -- when nvl(mtl_sum.lot_basis_type, 'N') = 'N' then 0
     when nvl(mtl_sum.item_basis_type, 'Y') = 'Y' then 0
     -- End revision for version 1.28
     when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
     when cic_assys.rolled_up = 'N' then 0
     when mtl_sum.quantity_per_assembly = 0 then 0
     -- Revision for version 1.10, replace decodes with mtl_sum.status_type in (4,5,7,12,14,15) 
     -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
     when mtl_sum.status_type in (4,5,7,12,14,15)
   then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost /
      (decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))),5)
     when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'Y' -- Released and use completion quantities
   then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost /
      (decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))),5)
     when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'N' -- Released do not use completion quantities 
   -- Revision for version 1.31
   -- then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / mtl_sum.start_quantity,5)
     -- else round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / mtl_sum.start_quantity,5)
   then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / decode(mtl_sum.start_quantity, 0, 1, mtl_sum.start_quantity),5)
     else round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / decode(mtl_sum.start_quantity, 0, 1, mtl_sum.start_quantity),5)
     -- End revision for version 1.31
      end,0) - 
 -- Standard Setup Charges Per Unit
        nvl(case
     when nvl(mtl_sum.lot_basis_type, 'N') = 'N' then 0
     when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
     when cic_assys.rolled_up = 'N' then 0
     when mtl_sum.std_quantity_per_assembly = 0 then 0
     else round(mtl_sum.std_quantity_per_assembly * mtl_sum.lot_basis_cost / mtl_sum.comp_lot_size,5)
      end,0)) *  
 -- Quantity Completed or Quantity Planned
       nvl(case
     -- Revision for version 1.28
     -- when nvl(mtl_sum.lot_basis_type, 'N') = 'N' then 0
     when nvl(mtl_sum.item_basis_type, 'Y') = 'Y' then 0
     -- End revision for version 1.28
     when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
     when cic_assys.rolled_up = 'N' then 0
     when mtl_sum.quantity_per_assembly = 0 then 0
     -- Revision for version 1.10, replace decodes with mtl_sum.status_type in (4,5,7,12,14,15) 
     -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
     when mtl_sum.status_type in (4,5,7,12,14,15)
   then decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))
     when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'Y' -- Released and use completion quantities
   then decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))
     when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'N' -- Released do not use completion quantities 
   then mtl_sum.start_quantity
     else mtl_sum.start_quantity
     end,0)
    ,2) Lot_Size_Variance,
 -- =============================
 -- Total Material Usage Variance
 -- =============================
 -- Total Matl Usage Variance = Material Usage Variance + Configuration_Variance + Lot Size Variance
 -- Material Usage Variance
 -- Revision for version 1.10
 -- To match the Oracle Discrete Job Value Report, for all jobs, turn off the material usage 
 -- variances when there are no completions and no applied or charged quantities.
 -- round(mtl_sum.applied_component_value - mtl_sum.wip_std_component_value,2) +
 case
    when mtl_sum.fg_total_qty = 0 and round(mtl_sum.applied_component_value,2) = 0 then 0
    else round(mtl_sum.applied_component_value - mtl_sum.wip_std_component_value,2)
 end +
 -- End revision for version 1.10
 -- Configuration_Variance
 case
    when nvl(mtl_sum.item_basis_type, 'N') = 'N' then 0
    when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
    when cic_assys.rolled_up = 'N' then 0
    when (nvl(mtl_sum.std_total_req_quantity,0) + nvl(mtl_sum.total_req_quantity,0)) = 0 then 0
    when nvl(mtl_sum.std_total_req_quantity,0) - nvl(mtl_sum.total_req_quantity,0) = 0 then 0
    -- Revision for version 1.6
    -- Only have configuration variances with Standard Costing (but still want to show qty configuration variances)
    when mtl_sum.primary_cost_method <> 1 then 0
    -- Revision for version 1.9, for all jobs, turn off configuration var when there are no completions and no activity.
    -- when mtl_sum.status_type = 7 and mtl_sum.fg_total_qty = 0 and round(mtl_sum.applied_component_value,2) = 0 then 0
    when mtl_sum.fg_total_qty = 0 and round(mtl_sum.applied_component_value,2) = 0 then 0
    -- End revision for version 1.9
    else round((nvl(mtl_sum.total_req_quantity,0) - nvl(mtl_sum.std_total_req_quantity,0)) * mtl_sum.item_cost,2)
 end +
 -- Lot Size Variance
 -- (WIP Setup Charges Per Unit - Standard Setup Charges Per Unit) X Quantity
 -- WIP Setup Charges Per Unit
 round((nvl(case
     -- Revision for version 1.28
     -- when nvl(mtl_sum.lot_basis_type, 'N') = 'N' then 0
     when nvl(mtl_sum.item_basis_type, 'Y') = 'Y' then 0
     -- End revision for version 1.28
     when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
     when cic_assys.rolled_up = 'N' then 0
     when mtl_sum.quantity_per_assembly = 0 then 0
     -- Revision for version 1.10, replace decodes with mtl_sum.status_type in (4,5,7,12,14,15) 
     -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
     when mtl_sum.status_type in (4,5,7,12,14,15)
   then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost /
      (decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))),5)
     when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'Y' -- Released and use completion quantities
   then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost /
      (decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))),5)
     when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'N' -- Released do not use completion quantities
   -- Revision for version 1.31 
   -- then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / mtl_sum.start_quantity,5)
     -- else round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / mtl_sum.start_quantity,5)
   then round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / decode(mtl_sum.start_quantity, 0, 1, mtl_sum.start_quantity),5)
     else round(mtl_sum.quantity_per_assembly * mtl_sum.lot_basis_cost / decode(mtl_sum.start_quantity, 0, 1, mtl_sum.start_quantity),5)
     -- End revision for version 1.31
      end,0) - 
 -- Standard Setup Charges Per Unit
        nvl(case
     when nvl(mtl_sum.lot_basis_type, 'N') = 'N' then 0
     when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
     when cic_assys.rolled_up = 'N' then 0
     when mtl_sum.std_quantity_per_assembly = 0 then 0
     else round(mtl_sum.std_quantity_per_assembly * mtl_sum.lot_basis_cost / mtl_sum.comp_lot_size,5)
      end,0)) *  
 -- Quantity Completed or Quantity Planned
       nvl(case
     -- Revision for version 1.28
     -- when nvl(mtl_sum.lot_basis_type, 'N') = 'N' then 0
     when nvl(mtl_sum.item_basis_type, 'Y') = 'Y' then 0
     -- End revision for version 1.28
     when mtl_sum.class_type = 3 and :p_nsj_config_lot_var = 'N' then 0
     when cic_assys.rolled_up = 'N' then 0
     when mtl_sum.quantity_per_assembly = 0 then 0
     -- Revision for version 1.10, replace decodes with mtl_sum.status_type in (4,5,7,12,14,15) 
     -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
     when mtl_sum.status_type in (4,5,7,12,14,15)
   then decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))
     when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'Y' -- Released and use completion quantities
   then decode(mtl_sum.quantity_completed, 0, 1, mtl_sum.quantity_completed) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(mtl_sum.quantity_scrapped, 0))
     when mtl_sum.quantity_completed <> 0 and :p_use_completion_qtys = 'N' -- Released do not use completion quantities 
   then mtl_sum.start_quantity
     else mtl_sum.start_quantity
     end,0)
    ,2) Total_Material_Variance,
 -- Revision for version 1.8
 fl2.meaning Rolled_Up,
 cic_assys.last_rollup_date Last_Cost_Rollup,
 -- End revision for version 1.8
 -- Revision for version 1.27
 mtl_sum.alternate_designator_code Alternate_BOM
-- Revision for version 1.22
-- from mtl_system_items_vl msiv2,
from mtl_units_of_measure_vl muomv,
 mtl_units_of_measure_vl muomv2,
 mtl_item_status_vl misv,
 mtl_item_status_vl misv2,
 bom_departments bd,
 wip_entities we,
 mfg_lookups ml1, -- WIP_Class
 mfg_lookups ml2, -- WIP Status
 mfg_lookups ml3, -- Assy Planning Make Buy
 mfg_lookups ml4, -- Component Planning Make Buy
 mfg_lookups ml5, -- WIP_Supply_Type
 mfg_lookups ml6, -- Component Basis Type
 -- Revision for version 1.30
 mfg_lookups ml7, -- Include in Rollup
 -- Revision for version 1.20, comment out Phantom Parent
 -- Revision for version 1.6
 -- fnd_lookups fl1,  -- Phantom Parent
 -- Revision for version 1.8
 fnd_lookups fl2,  -- Rolled Up
 fnd_common_lookups fcl1, -- Assy Item Type
 fnd_common_lookups fcl2, -- Component 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.8
 -- cst_item_costs cic,
 -- Revision for version 1.22
 cic_assys,
 -- ========================================================
 -- Get the WIP Component Information in a multi-part union
 -- which is then condensed into a summary data set
 -- ========================================================
 -- ========================================================
 -- Section I  Condense into a summary data set.
 -- ========================================================
 (select mtl.report_type,
  mtl.period_name,
  mtl.organization_code,
  mtl.organization_id,
  mtl.primary_cost_method,
  mtl.account,
  mtl.class_code,
  mtl.class_type,
  mtl.wip_entity_id,
  mtl.project_id,
  mtl.status_type,
  mtl.primary_item_id,
  mtl.assembly_number,
  mtl.assy_description,
  mtl.assy_item_type,
  mtl.assy_item_status_code,
  mtl.assy_uom_code,
  mtl.planning_make_buy_code,
  mtl.std_lot_size,
  mtl.lot_number,
  mtl.creation_date,
  mtl.scheduled_start_date,
  mtl.date_released,
  mtl.date_completed,
  mtl.date_closed,
  mtl.last_update_date,
  mtl.start_quantity,
  mtl.quantity_completed,
  mtl.quantity_scrapped,
  mtl.fg_total_qty,
  mtl.inventory_item_id,
  mtl.department_id,
  mtl.operation_seq_num,
  mtl.item_num,
  mtl.wip_supply_type,
  mtl.component_number,
  mtl.component_description,
  mtl.component_item_type,
  mtl.comp_planning_make_buy_code,
  mtl.component_item_status_code,
  mtl.component_uom_code,
  -- Revision for version 1.28
  -- Condense to a common value to get only one row
  case
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 0 then 1
    when mtl.wip_basis_type = 1 then 1
    when mtl.wip_basis_type = 2 then 2
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 1 then 1
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 2 then 2
    else 1
  end component_basis_type,
  -- End revision for version 1.28
  -- Revision for version 1.30
  mtl.include_in_cost_rollup,
  mtl.lot_basis_type,
  mtl.comp_lot_size,
  mtl.lot_basis_cost,
  -- Revision for version 1.28
  -- Condense to a common value to get only one row
  case
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 0 then 'Y'
    when mtl.wip_basis_type = 1 then 'Y'
    when mtl.wip_basis_type = 2 then 'N'
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 1 then 'Y'
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 2 then 'N'
    else 'Y'
  end item_basis_type,
  -- End revision for version 1.28
  mtl.item_basis_cost,
  mtl.cost_type,
  mtl.item_cost,
  mtl.quantity_per_assembly,
  mtl.total_req_quantity,
  mtl.last_txn_date,
  mtl.quantity_issued,
  mtl.wip_std_component_value,
  mtl.applied_component_value,
  mtl.std_quantity_per_assembly,
  mtl.std_total_req_quantity,
  mtl.alternate_designator_code
  from (select mtl2.report_type,
   mtl2.period_name,
   mtl2.organization_code,
   mtl2.organization_id,
   mtl2.primary_cost_method,
   -- Revision for version 1.12
   -- mtl2.primary_cost_type,
   mtl2.account,
   mtl2.class_code,
   mtl2.class_type,
   mtl2.wip_entity_id,
   mtl2.project_id,
   mtl2.status_type,
   mtl2.primary_item_id,
   -- Revision for version 1.22
   mtl2.assembly_number,
   mtl2.assy_description,
   mtl2.assy_item_type,
   mtl2.assy_item_status_code,
   mtl2.assy_uom_code,
   mtl2.planning_make_buy_code,
   mtl2.std_lot_size,
   -- End revision for version 1.22
   -- Revision for version 1.7
   mtl2.lot_number,
   mtl2.creation_date,
   -- Revision for version 1.5
   mtl2.scheduled_start_date,
   mtl2.date_released,
   mtl2.date_completed,
   mtl2.date_closed,
   mtl2.last_update_date,
   mtl2.start_quantity,
   mtl2.quantity_completed,
   mtl2.quantity_scrapped,
   mtl2.fg_total_qty,
   mtl2.inventory_item_id,
   -- Revision for version 1.19
   -- Redo department, operation sequence number and wip supply type, as the WIP
   -- component_sequence_id may be different from the BOM component_sequence_id.
   -- mtl2.department_id,
   -- Revision for version 1.30
   min(case
      when mtl2.department_id is null then
    -- If the component is not on the WIP BOM then the department will still be null
    -- as there is no department information on the standard BOM.
    (select nvl(wro.department_id, wo.department_id)
     from wip_requirement_operations wro,
     wip_operations wo
     where wo.wip_entity_id          = wro.wip_entity_id
     and wo.organization_id        = wro.organization_id
     and wo.operation_seq_num      = wro.operation_seq_num
     and wro.inventory_item_id     = mtl2.inventory_item_id
     and wro.wip_entity_id         = mtl2.wip_entity_id
     -- Revision for version 1.27
     -- Prevent single-row subquery returns more than one row error
     and rownum                    = 1
     and wro.organization_id       = mtl2.organization_id)
      when mtl2.department_id is not null then mtl2.department_id
      else mtl2.department_id
   end) department_id,
   -- End revision for version 1.19 and 1.30
   -- Revision for version 1.12 and 1.14
   -- mtl2.level_num,
   -- Revision for version 1.19
   -- mtl2.operation_seq_num,
   -- Revision for version 1.30
   min(case
      when mtl2.operation_seq_num is null then
    -- Get the operation_seq_num from the WIP BOM, but if the component
    -- is not on the WIP BOM, then get it from the standard BOM.
    (select nvl(wro.operation_seq_num,
     (select comp.operation_seq_num
      from bom_components_b comp,
      bom_list bom
      where bom.bill_sequence_id       = comp.bill_sequence_id
      and comp.component_item_id     = mtl2.inventory_item_id
      and bom.assembly_item_id       = mtl2.primary_item_id
      and bom.organization_id        = mtl2.organization_id))
     from wip_requirement_operations wro
     where wro.inventory_item_id     = mtl2.inventory_item_id
     and wro.wip_entity_id         = mtl2.wip_entity_id
     and wro.organization_id       = mtl2.organization_id
     -- Revision for version 1.27
     -- Prevent single-row subquery returns more than one row error
     and rownum                    = 1
     group by wro.operation_seq_num)
      when mtl2.operation_seq_num is not null then mtl2.operation_seq_num
      else mtl2.operation_seq_num
   end) operation_seq_num,
   -- End revision for version 1.19 and 1.30
   -- Revision for version 1.27
   mtl2.item_num,
   -- mtl2.wip_supply_type,
   -- Revision for version 1.30
   min(case
      when mtl2.wip_supply_type is null then
    -- Get the wip_supply_type from the WIP BOM, but if the component
    -- is not on the WIP BOM, then get it from the standard BOM or the item master.
    (select nvl(wro.wip_supply_type,
     (select nvl(comp.wip_supply_type, msi_comp.wip_supply_type)
      from bom_components_b comp,
      bom_list bom,
      mtl_system_items_b msi_comp
      where bom.bill_sequence_id       = comp.bill_sequence_id
      and comp.component_item_id     = mtl2.inventory_item_id
      and bom.assembly_item_id       = mtl2.primary_item_id
      and bom.organization_id        = mtl2.organization_id
      and msi_comp.organization_id   = mtl2.organization_id
      and msi_comp.inventory_item_id = mtl2.inventory_item_id))
     from wip_requirement_operations wro
     where wro.inventory_item_id      = mtl2.inventory_item_id
     and wro.wip_entity_id          = mtl2.wip_entity_id
     and wro.organization_id        = mtl2.organization_id
     -- Prevent single-row subquery returns more than one row error
     and rownum                     = 1
     group by wro.wip_supply_type)
      when mtl2.wip_supply_type is not null then mtl2.wip_supply_type
      else mtl2.wip_supply_type
   end) wip_supply_type,
   -- End revision for version 1.19 and 1.30
   -- Revision for version 1.6 and 1.22
   mtl2.component_number,
   mtl2.component_description,
   mtl2.component_item_type,
   mtl2.comp_planning_make_buy_code,
   mtl2.component_item_status_code,
   mtl2.component_uom_code,
   -- End revision for version 1.22
   -- Revision for version 1.21
   -- case
   --    when sum(mtl2.phantom_parent) > 0 then 'Y'
   --    else 'N'
   -- end phantom_parent,
   -- Revision for version 1.28
   -- Revision for version 1.8
   -- mtl2.basis_type,
   -- Condense to a common value to get only one row
   sum(mtl2.wip_basis_type) wip_basis_type,
   -- End revision for version 1.28
   mtl2.lot_basis_type,
   -- Revision for version 1.28
   mtl2.comp_lot_size,
   mtl2.lot_basis_cost,
   -- Revision for version 1.28
   -- mtl2.item_basis_type,
   -- Condense to a common value to get only one row
   sum(mtl2.comp_basis_type) comp_basis_type,
   -- End revision for version 1.28
   -- Revision for version 1.30
   sum(mtl2.include_in_cost_rollup) include_in_cost_rollup,
   mtl2.item_basis_cost,
   -- End revision for version 1.8
   mtl2.cost_type,
   mtl2.item_cost,
   sum(mtl2.quantity_per_assembly) quantity_per_assembly,
   sum(mtl2.total_req_quantity) total_req_quantity,
   -- Revision for version 1.18
   (select max(mmt.transaction_date)
    from mtl_material_transactions mmt
    where mmt.inventory_item_id          = mtl2.inventory_item_id
    and mmt.organization_id            = mtl2.organization_id
    and mmt.transaction_source_id      = mtl2.wip_entity_id
    and mmt.transaction_source_type_id = 5
    and mmt.transaction_date           < mtl2.schedule_close_date + 1) last_txn_date,
   -- End revision for version 1.18
   sum(mtl2.quantity_issued) quantity_issued,
   sum(mtl2.wip_std_component_value) wip_std_component_value,
   sum(mtl2.applied_component_value) applied_component_value,
   sum(mtl2.std_quantity_per_assembly) std_quantity_per_assembly,
   sum(mtl2.std_total_req_quantity) std_total_req_quantity,
   -- Revision for version 1.27
   (select badv.display_name
    from bom_structures_b bom,
    bom_alternate_designators_vl badv 
    where bom.assembly_item_id           = mtl2.primary_item_id
    and bom.organization_id            = mtl2.organization_id 
    and badv.alternate_designator_code = bom.alternate_bom_designator
    -- Revision for version 1.28
    and bom.alternate_bom_designator   = '&p_alt_bom_designator'                 -- p_alt_bom_designator
    and rownum                         = 1 
   ) alternate_designator_code
   -- End revision for version 1.27
  from -- =======================================================
   -- Section II.A. WIP and WIP Material Components
   -- =======================================================
   -- Revision for version 1.12
   (select 'II.A' section,
    -- Revision for version 1.22
    wro.report_type,
    wro.period_name,
    wro.organization_code,
    wro.organization_id,
    wro.primary_cost_method,
    -- Revision for version 1.12
    -- cct.cost_type primary_cost_type,
    wro.account,
    wro.class_code,
    wro.class_type,
    wro.wip_entity_id,
    wro.project_id,
    wro.status_type,
    wro.primary_item_id,
    -- Revision for version 1.22
    wro.assembly_number,
    wro.assy_description,
    wro.assy_item_type,
    wro.assy_item_status_code,
    wro.assy_uom_code,
    wro.planning_make_buy_code,
    wro.std_lot_size,
    -- End revision for version 1.22
    -- Revision for version 1.7
    wro.lot_number,
    wro.creation_date,
    -- Revision for version 1.5
    wro.scheduled_start_date,
    wro.date_released,
    wro.date_completed,
    wro.date_closed,
    -- Revision for version 1.18
    wro.schedule_close_date,
    wro.last_update_date,
    wro.start_quantity,
    wro.quantity_completed,
    wro.quantity_scrapped,
    wro.fg_total_qty,
    -- End revision for version 1.22
    wro.inventory_item_id,
    -- Revision for version 1.14
    -- nvl(wo.department_id,0) department_id,
    wo.department_id department_id,
    -- Revision for version 1.6
    wro.level_num,
    -- Revision for version 1.25
    -- wo.operation_seq_num,
    wro.operation_seq_num,
    -- Revision for version 1.14
    wro.component_sequence_id,
    -- Revision for version 1.27
    wro.item_num,
    wro.wip_supply_type,
    -- Revision for version 1.6 and 1.22
    wro.component_number,
    wro.component_description,
    wro.component_item_type,
    wro.comp_planning_make_buy_code,
    wro.component_item_status_code,
    wro.component_uom_code,
    -- End revision for version 1.22
    -- Revision for version 1.21
    -- wro.phantom_parent,
    -- End revision for version 1.6
    -- Revision for version 1.8
    -- coalesce(wro.basis_type, cic_comp.basis_type, 1) basis_type,
    -- Revision for version 1.28
    -- nvl(wro.basis_type, 1) basis_type,
    nvl(wro.basis_type, 1) wip_basis_type,
    0 comp_basis_type,
    -- End revision for version 1.28
    -- Revision for version 1.30
    0 include_in_cost_rollup,
    decode(cic_comp.lot_basis_type, 0, 'N', 'Y') lot_basis_type,
    nvl(cic_comp.lot_size,1) comp_lot_size,
    -- Revision for version 1.12
    nvl(cic_comp.lot_basis_cost,0) lot_basis_cost,
    -- Revision for version 1.28
    -- decode(nvl(wro.basis_type,1),
    --     1, 'Y',
    --     2, 'N',
    --     decode(cic_comp.item_basis_type, 0, 'N', 'Y')
    --      ) item_basis_type,
    -- End revision for version 1.28
    -- Revision for version 1.12
    nvl(cic_comp.item_basis_cost,0) item_basis_cost,
    cic_comp.cost_type cost_type,
    nvl(cic_comp.item_cost,0) item_cost,
    -- a basis of 2 indicates the component is issued per lot not per assembly and the component yield factor is ignored
    decode(nvl(wro.basis_type,1), 
     1, nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1),                    -- Item basis
     2, nvl(wro.required_quantity,1),                                                              -- Lot
        nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1)                     -- Any other basis
       ) quantity_per_assembly,
    -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
    -- then use completions plus scrap quantities unless for lot-based jobs.
    round(case when wro.status_type in (4,5,7,12,14,15) then
     -- use the completions plus scrap quantities unless for lot-based jobs
     decode(nvl(wro.basis_type, 1),
       -- Revision for version 1.29
       -- 2, nvl(wro.quantity_per_assembly,0),                                        -- Lot
       2, nvl(wro.quantity_per_assembly,0) *                                          -- Lot
        case
           when nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0)) = 0 then 0
           when nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0)) > 0 then 1
           else 0
        end,
       -- End revision for version 1.29
       nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1)         -- Any other basis
       * decode(wro.class_type,
         5, nvl(wro.quantity_completed, 0),
         nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0))
        )
        ) else
     -- Else use the start quantity times the usage rate or amount
     decode(:p_use_completion_qtys,
      'Y', decode(nvl(wro.basis_type, 1),
        -- use the completions plus scrap quantities unless for lot-based jobs
        -- Revision for version 1.29
        -- 2, nvl(wro.quantity_per_assembly,0),                                -- Lot
        2, nvl(wro.quantity_per_assembly,0) *                                  -- Lot
         case
            when nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0)) = 0 then 0
            when nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0)) > 0 then 1
            else 0
         end,
        -- End revision for version 1.29
           nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1) -- Any other basis
         * decode(wro.class_type,
           5, nvl(wro.quantity_completed, 0),
           nvl(wro.quantity_completed, 1) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0))
          )
          ),
      'N', decode(nvl(wro.basis_type, 1),
        2, nvl(wro.quantity_per_assembly,0),                                                           -- Lot
           nvl(wro.quantity_per_assembly,0) * wro.start_quantity * 1/nvl(wro.component_yield_factor,1) -- Any other basis
          )
        ) end
       ,6) total_req_quantity,
    nvl(wro.quantity_issued,0) quantity_issued,
    -- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
    -- then use completions plus scrap quantities unless for lot-based jobs.
    round(case when wro.status_type in (4,5,7,12,14,15) then
     -- use the completions plus scrap quantities unless for lot-based jobs
     decode(nvl(wro.basis_type,1),
       -- Revision for version 1.29
       -- 2, nvl(wro.quantity_per_assembly,0),                                        -- Lot
       2, nvl(wro.quantity_per_assembly,0) *                                          -- Lot
        case
           when nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0)) = 0 then 0
           when nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0)) > 0 then 1
           else 0
        end,
       -- End revision for version 1.29
          nvl(wro.quantity_per_assembly,1) * 1/nvl(wro.component_yield_factor,1)                -- Any other basis
       * decode(wro.class_type,
         5, nvl(wro.quantity_completed, 0),
         nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0))
        )
        ) else
     -- else use the start quantity times the usage rate or amount
     decode(:p_use_completion_qtys,
      'Y', decode(nvl(wro.basis_type,1),
        -- use the completions plus scrap quantities unless for lot-based jobs
        -- Revision for version 1.29
        -- 2, nvl(wro.quantity_per_assembly,0),                                -- Lot
        2, nvl(wro.quantity_per_assembly,0) *                                  -- Lot
         case
            when nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0)) = 0 then 0
            when nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0)) > 0 then 1
            else 0
         end,
        -- End revision for version 1.29
           nvl(wro.quantity_per_assembly,0) * 1/nvl(wro.component_yield_factor,1)         -- Any other basis
         * decode(wro.class_type,
           5, nvl(wro.quantity_completed, 0),
           nvl(wro.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(wro.quantity_scrapped, 0))
          )
          ),
      'N', decode(nvl(wro.basis_type,1),
        2, nvl(wro.quantity_per_assembly,0),                                                           -- Lot
           nvl(wro.quantity_per_assembly,0) * wro.start_quantity * 1/nvl(wro.component_yield_factor,1) -- Any other basis
          )
        ) end
       ,6) -- total_req_quantity
    -- And multiply by the Cost_Type or Costing_Method costs
    * nvl(cic_comp.item_cost,0) wip_std_component_value,
    nvl(wro.quantity_issued,0)
    -- And multiply by the Cost_Type or Costing_Method costs
    * nvl(cic_comp.item_cost,0) applied_component_value,
    0 std_quantity_per_assembly,
    0 std_total_req_quantity
    from wip_operations wo,
    -- Revision for version 1.12
    -- cst_cost_types cct,
    -- Revision for version 1.22
    -- mtl_system_items_vl msiv,
    -- Revision for version 1.22
    cic_comp, -- Get the Cost Basis Type and Component Item Costs
    -- wdj, -- get the corrected wip qty completed and qty scrapped
    -- End revision for version 1.22
    -- get the corrected wip component issue quantities
    -- Revision for version 1.6
    (select wrosum.level_num,
     -- Revision for version 1.22
     wrosum.report_type,
     wrosum.period_name,
     wrosum.organization_code,
     wrosum.organization_id,
     wrosum.primary_cost_method,
     wrosum.account,
     wrosum.class_code,
     wrosum.class_type,
     wrosum.wip_entity_id,
     wrosum.project_id,
     wrosum.status_type,
     wrosum.primary_item_id,
     wrosum.assembly_number,
     wrosum.assy_description,
     wrosum.assy_item_type,
     wrosum.assy_item_status_code,
     wrosum.assy_uom_code,
     wrosum.planning_make_buy_code,
     wrosum.std_lot_size,
     wrosum.lot_number,
     wrosum.creation_date,
     wrosum.scheduled_start_date,
     wrosum.date_released,
     wrosum.date_completed,
     wrosum.date_closed,
     wrosum.schedule_close_date,
     wrosum.last_update_date,
     wrosum.start_quantity,
     wrosum.quantity_completed,
     wrosum.quantity_scrapped,
     wrosum.quantity_completed + wrosum.quantity_scrapped fg_total_qty,
     -- wrosum.wip_entity_id,
     -- wrosum.organization_id,
     -- End revision for version 1.22
     wrosum.inventory_item_id,
     wrosum.operation_seq_num,
     wrosum.component_sequence_id,
     -- Revision for version 1.27
     wrosum.item_num,
     wrosum.quantity_per_assembly,
     sum(wrosum.required_quantity) required_quantity,
     wrosum.component_yield_factor,
     sum(wrosum.quantity_issued) quantity_issued,
     wrosum.basis_type basis_type,
     wrosum.wip_supply_type,
     -- Revision for version 1.22
     msiv_comp.concatenated_segments component_number,
     msiv_comp.description component_description,
     msiv_comp.item_type component_item_type,
     msiv_comp.planning_make_buy_code comp_planning_make_buy_code,
     msiv_comp.inventory_item_status_code component_item_status_code,
     msiv_comp.primary_uom_code component_uom_code,
     -- End revision for version 1.22
     -- Revision for version 1.6 and 1.21
     -- sum(wrosum.phantom_parent) phantom_parent,
     -- Revision for version 1.2
     wrosum.comments
     -- Revision for version 1.6 and 1.14
     -- Get the WIP material requirements
     -- Revision for version 1.22
     -- from (select 1 level_num,
     from mtl_system_items_vl msiv_comp,
     (select 1 level_num,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      wdj.last_update_date,
      wdj.start_quantity,
      wdj.quantity_completed,
      wdj.quantity_scrapped,
      -- wdj.primary_item_id level_1_parent_assy_id,
      -- End revision for version 1.22
      0 level_2_parent_assy_id,
      0 level_3_parent_assy_id,
      0 level_4_parent_assy_id,
      -- Revision for version 1.21
      -- 0 level_1_from_phantom_assy,
      -- 0 level_2_from_phantom_assy,
      -- 0 level_3_from_phantom_assy,
      -- 0 level_4_from_phantom_assy,
      -- End revision for version 1.21
      0 level_1_comp_is_phantom,
      0 level_2_comp_is_phantom,
      0 level_3_comp_is_phantom,
      0 level_4_comp_is_phantom,
      -- Revision for version 1.6 and 1.22
      -- wro.wip_entity_id,
      -- wro.organization_id,
      -- End revision for version 1.22
      wro.inventory_item_id,
      wro.operation_seq_num,
      wro.component_sequence_id,
      -- Revision for version 1.27
      -- based on the standard BOM, get the BOM component item_num
      nvl((select min(comp.item_num)
        from bom_components_b comp
        where wro.inventory_item_id     = comp.component_item_id
        and wro.wip_entity_id         = wdj.wip_entity_id
        and wro.component_sequence_id = comp.component_sequence_id
        and wro.organization_id       = wdj.organization_id), '') item_num,
      -- End revision for version 1.27
      wro.quantity_per_assembly,
      wro.required_quantity,
      wro.component_yield_factor,
      wro.quantity_issued,
      wro.basis_type,
      wro.wip_supply_type,
      -- Revision for version 1.6 and 1.21
      -- 0 phantom_parent, -- 0 is no
      -- Revision for version 1.2
      regexp_replace(wro.comments,'[^[:alnum:]'' '']', null) comments
      from wip_requirement_operations wro,
      wdj
      where wdj.wip_entity_id               = wro.wip_entity_id
      and wdj.organization_id             = wro.organization_id
      -- Revision for version 1.14
      -- Do not select phantom WIP supply types, not issued to WIP
      and wro.wip_supply_type            <> 6 -- Phantom
      union all
      -- Subtract away the transactions which happened after the reported period
      -- Revision for version 1.6
      select 1 level_num,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      wdj.last_update_date,
      wdj.start_quantity,
      wdj.quantity_completed,
      wdj.quantity_scrapped,
      -- wdj.primary_item_id level_1_parent_assy_id,
      -- End revision for version 1.22
      0 level_2_parent_assy_id,
      0 level_3_parent_assy_id,
      0 level_4_parent_assy_id,
      -- Revision for version 1.21
      -- 0 level_1_from_phantom_assy,
      -- 0 level_2_from_phantom_assy,
      -- 0 level_3_from_phantom_assy,
      -- 0 level_4_from_phantom_assy,
      -- End revision for version 1.21
      0 level_1_comp_is_phantom,
      0 level_2_comp_is_phantom,
      0 level_3_comp_is_phantom,
      0 level_4_comp_is_phantom,
      -- Revision for version 1.6
      -- Revision for version 1.22
      -- mmt.transaction_source_id,
      -- wro.organization_id,
      -- End revision for version 1.22
      mmt.inventory_item_id,
      mmt.operation_seq_num,
      wro.component_sequence_id,
      -- Revision for version 1.27
      nvl((select min(comp.item_num)
        from bom_components_b comp
        where wro.inventory_item_id     = comp.component_item_id
        and wro.wip_entity_id         = wdj.wip_entity_id
        and wro.component_sequence_id = comp.component_sequence_id
        and wro.organization_id       = wdj.organization_id), '') item_num,
      -- End revision for version 1.27
      wro.quantity_per_assembly,
      wro.required_quantity,
      wro.component_yield_factor,
      decode(mmt.transaction_type_id,
       35, mmt.primary_quantity,     -- wip component issue
       43, -1 * mmt.primary_quantity -- wip component return
         ) quantity_issued,
      wro.basis_type,
      wro.wip_supply_type,
      -- Revision for version 1.6 and 1.21
      -- 0 phantom_parent, -- 0 is no
      -- Revision for version 1.2
      regexp_replace(wro.comments,'[^[:alnum:]'' '']', null) comments
      from mtl_material_transactions mmt,
      wdj,
      -- Revision for version 1.10
      -- oap.org_acct_periods oap,
      wip_requirement_operations wro
      -- Revision for version 1.23
      where mmt.transaction_source_type_id  = 5 -- WIP
      and mmt.transaction_source_id       = wro.wip_entity_id
      and mmt.organization_id             = wro.organization_id
      and mmt.operation_seq_num           = wro.operation_seq_num
      and mmt.inventory_item_id           = wro.inventory_item_id
      and wro.wip_entity_id               = wdj.wip_entity_id
      and wro.organization_id             = wdj.organization_id
      -- and wdj.acct_period_id              = mmt.acct_period_id
      -- and wdj.organization_id             = mmt.organization_id
      -- Revision for version 1.10
      -- and oap.acct_period_id              = mmt.acct_period_id
      -- and wdj.organization_id             = oap.organization_id
      -- and mmt.transaction_date           >= oap.schedule_close_date + 1
      and mmt.transaction_date           >= wdj.schedule_close_date + 1
      -- End revision for version 1.10
      -- End revision for version 1.23
      union all
      -- Revision for version 1.6
      -- Get components from the WIP BOM where the Supply Type is not "Phantom" (6) but
      -- the standard BOM or item master has the component as a phantom.  By doing so 
      -- you can compare the standard BOM with the WIP BOM and eliminate these 
      -- "phantom components" as a configuration variance.
      select 2 level_num,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      wdj.last_update_date,
      wdj.start_quantity,
      wdj.quantity_completed,
      wdj.quantity_scrapped,
      -- wdj.primary_item_id level_1_parent_assy_id,
      -- End revision for version 1.22
      wro.inventory_item_id level_2_parent_assy_id,
      0 level_3_parent_assy_id,
      0 level_4_parent_assy_id,
      -- Revision for version 1.21
      -- 1 level_1_from_phantom_assy,
      -- 0 level_2_from_phantom_assy,
      -- 0 level_3_from_phantom_assy,
      -- 0 level_4_from_phantom_assy,
      -- End revision for version 1.21
      1 level_1_comp_is_phantom,
      0 level_2_comp_is_phantom,
      0 level_3_comp_is_phantom,
      0 level_4_comp_is_phantom,
      -- Revision for version 1.22
      -- wro.wip_entity_id,
      -- wro.organization_id,
      -- End revision for version 1.22
      comp.component_item_id inventory_item_id,
      wro.operation_seq_num,
      wro.component_sequence_id,
      -- Revision for version 1.27
      comp.item_num,
      -- Revision for version 1.8
      -- Multiply the comp.component_quantity by the parent phantom sub-assembly quantity, wro.quantity_per_assembly
      -- a basis of 2 indicates the component is issued per lot not per assembly and the component yield factor is ignored
      decode(nvl(comp.basis_type, 1), 
       1, nvl(comp.component_quantity,0) * wro.quantity_per_assembly * 1/nvl(comp.component_yield_factor,1),     -- Item basis
       2, nvl(comp.component_quantity,1) * wro.quantity_per_assembly,                                            -- Lot
          nvl(comp.component_quantity,0) * wro.quantity_per_assembly * 1/nvl(comp.component_yield_factor,1)      -- Any other basis
         ) quantity_per_assembly,
      round(case when wdj.status_type in (4,5,7,12,14,15) then
        decode(nvl(comp.basis_type, 1),
        -- use the completions plus scrap quantities unless for lot-based jobs
        2, nvl(comp.component_quantity,0) * wro.quantity_per_assembly,                                       -- Lot
           nvl(comp.component_quantity,0) * wro.quantity_per_assembly * 1/nvl(comp.component_yield_factor,1) -- 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
       -- Revision for version 1.5
       decode(:p_use_completion_qtys,
        'Y', decode(nvl(comp.basis_type, 1),
          -- use the completions plus scrap quantities unless for lot-based jobs
          2, nvl(comp.component_quantity,0) * wro.quantity_per_assembly,          -- Lot
             nvl(comp.component_quantity,0) * wro.quantity_per_assembly * 1/nvl(comp.component_yield_factor,1) -- 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 use the start quantity times the usage rate or amount
        'N', decode(nvl(comp.basis_type, 1),
          2, nvl(comp.component_quantity,0) * wro.quantity_per_assembly,                                                            -- Lot
             nvl(comp.component_quantity,0) * wro.quantity_per_assembly * wdj.start_quantity * 1/nvl(comp.component_yield_factor,1) -- Any other basis
            )
          ) end
          ,6) required_quantity,
       -- End revision for version 1.8, multiply the comp.component_quantity by wro.quantity_per_assembly
       comp.component_yield_factor,
       -- Issued Quantity = Quantity Per Assembly X Quantity Issued for the Parent Phantom
       -- a basis of 2 indicates the component is issued per lot not per assembly and the component yield factor is ignored
       round(decode(nvl(comp.basis_type, 1), 
         1, nvl(comp.component_quantity,0) * 1/nvl(comp.component_yield_factor,1),                   -- Item basis
         2, nvl(comp.component_quantity,1),                                                          -- Lot
            nvl(comp.component_quantity,0) * 1/nvl(comp.component_yield_factor,1)                    -- Any other basis
        ) * wro.quantity_issued
         ,6) quantity_issued,    
      nvl(comp.basis_type,1) basis_type,
      -- Revision for version 1.17
      -- nvl(comp.wip_supply_type, wro.wip_supply_type) wip_supply_type,
      coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, wro.wip_supply_type) wip_supply_type,
      -- Revision for version 1.6 and 1.21
      -- 1 phantom_parent, -- 1 is yes
      -- Revision for version 1.2
      regexp_replace(wro.comments,'[^[:alnum:]'' '']', null) comments
      from wip_requirement_operations wro, -- Level 1 components
      mtl_system_items_vl msiv_comp,  -- Level 1 components
      bom_structures_b bom,           -- Get the assemblies based on WIP, at level 1
      bom_components_b comp,          -- Level 2 components
      wdj                             -- List of WIP Jobs
      -- ======================================================
      -- Get WIP components which are phantoms (level 1)
      -- ======================================================
      where wdj.wip_entity_id               = wro.wip_entity_id
      and wdj.organization_id             = wro.organization_id
      -- The WIP supply type is not "phantom" but the item type is.
      and wro.wip_supply_type            <> 6 -- Phantom
      -- Revision for version 1.17
      -- and nvl(msiv_comp.item_type,'X')    = 'PH'
      and coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0) = 6
      and msiv_comp.inventory_item_id     = wro.inventory_item_id
      and msiv_comp.organization_id       = wro.organization_id
      -- ======================================================
      -- Get BOM components which report to phantoms (level 2)
      -- ======================================================
      and bom.organization_id             = wdj.organization_id
      and bom.assembly_item_id            = wro.inventory_item_id
      and bom.bill_sequence_id            = comp.bill_sequence_id
      -- Revision for version 1.16
      -- and comp.effectivity_date          <= sysdate
      -- and nvl(comp.disable_date, sysdate+1) >  sysdate
      and comp.effectivity_date          <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp.disable_date, sysdate+1) >  
      case
         when comp.disable_date is null then sysdate
         when comp.disable_date >= sysdate then sysdate
         when comp.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      and bom.common_assembly_item_id is null
      and bom.assembly_type               = 1   -- Manufacturing
      and comp.component_quantity        <> 0
      and nvl(comp.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp.implementation_date,sysdate+1),
        'Y', nvl(comp.implementation_date,sysdate))
      -- End revision for version 1.6
     ) wrosum
     where msiv_comp.organization_id   = wrosum.organization_id
     and msiv_comp.inventory_item_id = wrosum.inventory_item_id
     group by
     -- Revision for version 1.6
     wrosum.level_num,
     -- Revision for version 1.22
     wrosum.report_type,
     wrosum.period_name,
     wrosum.organization_code,
     wrosum.organization_id,
     wrosum.primary_cost_method,
     wrosum.account,
     wrosum.class_code,
     wrosum.class_type,
     wrosum.wip_entity_id,
     wrosum.project_id,
     wrosum.status_type,
     wrosum.primary_item_id,
     wrosum.assembly_number,
     wrosum.assy_description,
     wrosum.assy_item_type,
     wrosum.assy_item_status_code,
     wrosum.assy_uom_code,
     wrosum.planning_make_buy_code,
     wrosum.std_lot_size,
     wrosum.lot_number,
     wrosum.creation_date,
     wrosum.scheduled_start_date,
     wrosum.date_released,
     wrosum.date_completed,
     wrosum.date_closed,
     wrosum.schedule_close_date,
     wrosum.last_update_date,
     wrosum.start_quantity,
     wrosum.quantity_completed,
     wrosum.quantity_scrapped,
     wrosum.quantity_completed + wrosum.quantity_scrapped, -- wrosum.fg_total_qty
     -- wrosum.wip_entity_id,
     -- wrosum.organization_id,
     -- End revision for version 1.22
     wrosum.inventory_item_id,
     wrosum.operation_seq_num,
     wrosum.component_sequence_id,
     -- Revision for version 1.27
     wrosum.item_num,
     wrosum.quantity_per_assembly,
     wrosum.component_yield_factor,
     wrosum.basis_type,
     wrosum.wip_supply_type,
     -- Revision for version 1.22
     msiv_comp.concatenated_segments, -- component_number
     msiv_comp.description, -- component_description
     msiv_comp.item_type, -- component_item_type
     msiv_comp.planning_make_buy_code, -- comp_planning_make_buy_code
     msiv_comp.inventory_item_status_code, --  component_item_status_code
     msiv_comp.primary_uom_code, --  component_uom_code
     -- End revision for version 1.22
     -- Revision for version 1.2
     wrosum.comments
    ) wro
    -- ===========================================
    -- WIP_Job Entity, Class and Period joins
    -- ===========================================
    -- Revision for version 1.22
    -- where wro.wip_entity_id         = wdj.wip_entity_id
    -- and wro.organization_id       = wdj.organization_id
    -- and wo.operation_seq_num (+)  = wro.operation_seq_num
    -- End revision for version 1.22
    where wo.operation_seq_num (+)  = wro.operation_seq_num
    and wo.wip_entity_id (+)      = wro.wip_entity_id
    and wo.organization_id (+)    = wro.organization_id    
   -- Revision for version 1.12
   -- and cct.cost_type_id          = wdj.primary_cost_method
    and wro.organization_id       = cic_comp.organization_id (+)
    and wro.inventory_item_id     = cic_comp.inventory_item_id (+)
    -- Revision for version 1.22
    -- and msiv.organization_id      = wro.organization_id
    -- and msiv.inventory_item_id    = wro.inventory_item_id
    -- End revision for version 1.22
    union all
    -- =======================================================
    -- Section II.B. Get the Bill of Material
    -- Get BOM information for configuration variances.
    -- =======================================================
    -- Revision for version 1.12
    select 'II.B' section,
    -- Revision for version 1.22
    comp.report_type,
    comp.period_name,
    comp.organization_code,
    comp.organization_id,
    comp.primary_cost_method,
    -- Revision for version 1.12
    -- cct.cost_type primary_cost_type,
    comp.account,
    comp.class_code,
    comp.class_type,
    comp.wip_entity_id,
    comp.project_id,
    comp.status_type,
    comp.primary_item_id,
    -- Revision for version 1.22
    comp.assembly_number,
    comp.assy_description,
    comp.assy_item_type,
    comp.assy_item_status_code,
    comp.assy_uom_code,
    comp.planning_make_buy_code,
    comp.std_lot_size,
    -- End revision for version 1.22
    -- Revision for version 1.7
    comp.lot_number,
    comp.creation_date,
    -- Revision for version 1.5
    comp.scheduled_start_date,
    comp.date_released,
    comp.date_completed,
    comp.date_closed,
    -- Revision for version 1.18
    comp.schedule_close_date,
    comp.last_update_date,
    comp.start_quantity,
    comp.quantity_completed,
    comp.quantity_scrapped,
    comp.fg_total_qty,
    comp.component_item_id inventory_item_id,
    -- Revision for version 1.14 and 1.19
    -- 0 department_id,
    -- If the component is only on the primary BOM the department_id does not exist
    -- If the component is on the WIP BOM but the wro.department_id is null, get it from wo.department_id
    nvl((select min(nvl(wro.department_id, wo.department_id))
      from wip_requirement_operations wro,
      wip_operations wo
      where wro.inventory_item_id     = comp.component_item_id
      and wro.component_sequence_id = comp.component_sequence_id
      and wro.wip_entity_id         = comp.wip_entity_id
      and wro.organization_id       = comp.organization_id
      and wo.wip_entity_id          = wro.wip_entity_id
      and wo.organization_id        = wro.organization_id
      and wo.operation_seq_num      = wro.operation_seq_num), '') department_id,
      -- Revision for version 1.22
      -- and bom.organization_id       = comp.organization_id
      -- and bom.assembly_item_id      = comp.primary_item_id), '') department_id,
      -- End revision for version 1.22
    -- End revision for version 1.14
    -- Revision for version 1.6
    comp.level_num,
    -- Revision for version 1.5, 1.14, 1.19 and 1.22
    -- The primary BOM operation_seq_num may be null or different from the WIP BOM
    -- abs(comp.operation_seq_num) operation_seq_num,
    nvl((select min(wro.operation_seq_num)
      from wip_requirement_operations wro
      where wro.inventory_item_id     = comp.component_item_id
      and wro.component_sequence_id = comp.component_sequence_id
      and wro.wip_entity_id         = comp.wip_entity_id
      and wro.organization_id       = comp.organization_id), '') operation_seq_num,
      -- Revision for version 1.22
      -- and bom.organization_id       = comp.organization_id
      -- and bom.assembly_item_id      = comp.primary_item_id), '') operation_seq_num,
      -- End revision for version 1.5, 1.14, 1.19 and 1.22
    -- End revision for version 1.5, 1.14 and 1.19
    -- Revision for version 1.14
    comp.component_sequence_id,
    -- Revision for version 1.27
    comp.item_num,
    -- Revision for version 1.14, 1.19 and 1.22
    -- comp.wip_supply_type,
    -- The primary BOM wip_supply_type may be null or different from the WIP BOM
    nvl((select min(wro.wip_supply_type)
      from wip_requirement_operations wro
      where wro.inventory_item_id     = comp.component_item_id
      and wro.component_sequence_id = comp.component_sequence_id
      and wro.wip_entity_id         = comp.wip_entity_id
      and wro.organization_id       = comp.organization_id), '') wip_supply_type,
      -- Revision for version 1.22
      -- and bom.organization_id       = comp.organization_id
      -- and bom.assembly_item_id      = comp.primary_item_id), '') wip_supply_type,
    -- End revision for version 1.14, 1.19 and 1.22
    -- End revision for version 1.14 and 1.19
    -- Revision for version 1.6 and 1.22
    comp.component_number,
    comp.component_description,
    comp.component_item_type,
    comp.comp_planning_make_buy_code,
    comp.component_item_status_code,
    comp.component_uom_code,
    -- End revision for version 1.22
    -- Revision for version 1.21
    -- comp.phantom_parent,
    -- End revision for version 1.6
    -- Revision for version 1.28
    -- nvl(comp.basis_type, 1) basis_type,
    0 wip_basis_type,
    nvl(comp.basis_type, 1) comp_basis_type,
    -- End revision for version 1.28
    -- Revision for version 1.30
    comp.include_in_cost_rollup,
    decode(cic_comp.lot_basis_type, 0, 'N', 'Y') lot_basis_type,
    nvl(cic_comp.lot_size,1) comp_lot_size,
    -- Revision for version 1.12
    nvl(cic_comp.lot_basis_cost,0) lot_basis_cost,
    -- Revision for version 1.28
    -- decode(nvl(comp.basis_type,1),
    --  1, 'Y',
    --  2, 'N',
    --  decode(cic_comp.item_basis_type, 0, 'N', 'Y')
    --      ) item_basis_type,
    -- End revision for version 1.28
    -- Revision for version 1.12
    nvl(cic_comp.item_basis_cost,0) item_basis_cost, 
    cic_comp.cost_type cost_type,
    nvl(cic_comp.item_cost,0) item_cost,
    0 quantity_per_assembly,
    0 total_req_quantity,
    0 quantity_issued,
    0 wip_std_component_value,
    0 applied_component_value,
    -- Revision for version 1.12, restructure the code
    -- a basis of 2 indicates the component is issued per lot not per assembly and the component yield factor is ignored
    decode(nvl(comp.basis_type,1), 
     1,    nvl(comp.component_quantity,0) * 1/nvl(comp.component_yield_factor,1),                   -- Item basis
     2,    nvl(comp.component_quantity,1),                                                          -- Lot
        nvl(comp.component_quantity,0) * 1/nvl(comp.component_yield_factor,1)                    -- Any other basis
       ) std_quantity_per_assembly,    
    round(case when comp.status_type in (4,5,7,12,14,15) then
     -- use the completions plus scrap quantities unless for lot-based jobs
     decode(nvl(comp.basis_type, 1),
      -- Revision for version 1.29
      -- 2, nvl(comp.component_quantity,0),                                                  -- Lot
      2, nvl(comp.component_quantity,0) *                                                    -- Lot
       case
          when nvl(comp.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(comp.quantity_scrapped, 0)) = 0 then 0
          when nvl(comp.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(comp.quantity_scrapped, 0)) > 0 then 1
          else 0
       end,
      -- End revision for version 1.29
         nvl(comp.component_quantity,0) * 1/nvl(comp.component_yield_factor,1)               -- Any other basis
      * decode(comp.class_type,
        5, nvl(comp.quantity_completed, 0),
        nvl(comp.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(comp.quantity_scrapped, 0))
       )
        ) else
     -- else use the start quantity times the usage rate or amount
     -- Revision for version 1.5
     decode(:p_use_completion_qtys,
      'Y', decode(nvl(comp.basis_type,1),
        -- use the completions plus scrap quantities unless for lot-based jobs
        -- Revision for version 1.29
        -- 2, nvl(comp.component_quantity,0),                                  -- Lot
        2, nvl(comp.component_quantity,0) *                                    -- Lot
         case
            when nvl(comp.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(comp.quantity_scrapped, 0)) = 0 then 0
            when nvl(comp.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(comp.quantity_scrapped, 0)) > 0 then 1
            else 0
         end,
        -- End revision for version 1.29
           nvl(comp.component_quantity,0) * 1/nvl(comp.component_yield_factor,1)         -- Any other basis
        * decode(comp.class_type,
          5, nvl(comp.quantity_completed, 0),
          nvl(comp.quantity_completed, 0) + decode(:p_include_scrap, 'N', 0, null, 0, nvl(comp.quantity_scrapped, 0))
         )
          ),
      -- else use the start quantity times the usage rate or amount
      'N', decode(nvl(comp.basis_type,1),
        2, nvl(comp.component_quantity,0),                                                            -- Lot
           nvl(comp.component_quantity,0) * comp.start_quantity * 1/nvl(comp.component_yield_factor,1) -- Any other basis
          )
        ) end
    -- End revision for version 1.5
       ,6) std_total_req_quantity
   -- Revision for version 1.22
   -- from bom_structures_b bom,
   from cic_comp, -- Get the Cost Basis Type and Item Costs
    -- Revision for version 1.12
    -- cst_cost_types cct,
    -- Revision for version 1.5    
    -- Get the BOM Components and latest component effectivity date
    -- Revision for version 1.6
    -- Condense component requirements and screen for phantom-sourced component
    -- requirements which are already on the WIP bill of material.
    (select comp2.bill_sequence_id,
     -- Revision for version 1.22
     comp2.report_type,
     comp2.period_name,
     comp2.organization_code,
     comp2.organization_id,
     comp2.primary_cost_method,
     comp2.account,
     comp2.class_code,
     comp2.class_type,
     comp2.wip_entity_id,
     comp2.project_id,
     comp2.status_type,
     comp2.primary_item_id,
     comp2.assembly_number,
     comp2.assy_description,
     comp2.assy_item_type,
     comp2.assy_item_status_code,
     comp2.assy_uom_code,
     comp2.planning_make_buy_code,
     comp2.std_lot_size,
     comp2.lot_number,
     comp2.creation_date,
     comp2.scheduled_start_date,
     comp2.date_released,
     comp2.date_completed,
     comp2.date_closed,
     comp2.schedule_close_date,
     comp2.last_update_date,
     comp2.start_quantity,
     comp2.quantity_completed,
     comp2.quantity_scrapped,
     comp2.fg_total_qty,
     -- End revision for version 1.22
     comp2.level_num,
     -- Revision for version 1.16
     -- Revision for version 1.22
     -- comp2.wip_entity_id,
     comp2.operation_seq_num,
     -- Revision for version 1.14
     comp2.component_sequence_id,
     -- Revision for version 1.27
     comp2.item_num,
     -- Revision for version 1.22
     -- comp2.organization_id,
     comp2.component_item_id,
     comp2.component_quantity,
     comp2.effectivity_date,
     -- Revision for version 1.22
     -- comp2.last_update_date,
     comp2.disable_date,
     comp2.planning_factor,
     comp2.component_yield_factor,
     comp2.include_in_cost_rollup,
     comp2.basis_type,
     comp2.wip_supply_type,
     -- Revision for version 1.22
     comp2.component_number,
     comp2.component_description,
     comp2.item_type component_item_type,
     comp2.comp_planning_make_buy_code,
     comp2.component_item_status_code,
     comp2.component_uom_code,
     -- End revision for version 1.22
     -- Revision for version 1.21
     -- comp2.phantom_parent,
     comp2.supply_subinventory,
     comp2.supply_locator_id
       from -- First BOM Explosion
      -- =================================================
      -- Get the primary (non-alternate) bills of material
      -- =================================================
     -- Get the non-phantom components (level 1) from the BOM
     -- Revision for version 1.10, add hint
     (select /*+ leading(wdj) */ comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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,
      -- End revision for version 1.22
      1 level_num,
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- bom.assembly_item_id level_1_parent_assy_id,
      -- End revision for version 1.22
      -999 level_2_parent_assy_id,
      -999 level_3_parent_assy_id,
      -999 level_4_parent_assy_id,
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) level_1_from_phantom_assy,
      -- 0 level_2_from_phantom_assy, -- 0 is no
      -- 0 level_3_from_phantom_assy, -- 0 is no
      -- 0 level_4_from_phantom_assy, -- 0 is no
      -- End revision for version 1.21
      0 level_1_comp_is_phantom, -- 0 is no
      0 level_2_comp_is_phantom, -- 0 is no
      0 level_3_comp_is_phantom, -- 0 is no
      0 level_4_comp_is_phantom, -- 0 is no
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments level_1_parent_assy,
      -- wdj.assembly_number level_1_parent_assy,
      -- End revision for version 1.22
      msiv_comp.concatenated_segments level_1_component,
      null level_2_component,
      null level_3_component,
      null level_4_component,
      comp.operation_seq_num,
      -- Revision for version 1.14
      comp.component_sequence_id,
      -- Revision for version 1.27
      comp.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp.component_item_id,
      comp.component_quantity,
      max(comp.effectivity_date) effectivity_date,
      -- Revision for version 1.22
      -- comp.last_update_date,
      comp.disable_date,
      comp.planning_factor,
      comp.component_yield_factor,
      comp.include_in_cost_rollup,
      comp.basis_type,
      -- Revision for version 1.17
      -- comp.wip_supply_type,
      coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0) wip_supply_type,
      -- Revision for version 1.22
      msiv_comp.concatenated_segments component_number,
      msiv_comp.description component_description,
      nvl(msiv_comp.item_type,'X') item_type,
      msiv_comp.planning_make_buy_code comp_planning_make_buy_code,
      msiv_comp.inventory_item_status_code component_item_status_code,
      msiv_comp.primary_uom_code component_uom_code,
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) phantom_parent,
      comp.supply_subinventory,
      comp.supply_locator_id
      from bom_structures_b bom,          -- Get the assemblies based on WIP, at level 0
      -- Revision for version 1.22
      -- mtl_system_items_vl msiv_parent,
      bom_components_b comp,         -- Get the components on the assemblies, at level 1
      mtl_system_items_vl msiv_comp, -- Only select components which are not phantoms
      -- Revision for version 1.16
      -- wdj_assys -- Limit to assemblies on WIP jobs
      wdj                            -- List of WIP Jobs
      -- ======================================================
      -- Get assemblies and components based on WIP jobs
      -- ======================================================
      -- Revision for version 1.22, outer join BOMs to wdj
      where bom.assembly_item_id            = wdj.primary_item_id (+)
      and bom.organization_id             = wdj.organization_id (+)
      and bom.bill_sequence_id            = comp.bill_sequence_id
      and msiv_comp.inventory_item_id     = comp.component_item_id
      and msiv_comp.organization_id       = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp.item_type,'X')   <> 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0) <> 6 -- Not Phantom
      -- Revision for version 1.22
      -- and msiv_parent.inventory_item_id(+)= bom.assembly_item_id
      -- and msiv_parent.organization_id(+)  = bom.organization_id
      -- End revision for version 1.22
      -- Revision for version 1.16
      -- and comp.effectivity_date          <= sysdate
      -- and nvl(comp.disable_date, sysdate+1) >  sysdate
      and comp.effectivity_date          <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp.disable_date, sysdate+1) >  
      case
         when comp.disable_date is null then sysdate
         when comp.disable_date >= sysdate then sysdate
         when comp.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom.assembly_item_id
        and bom2.organization_id          = bom.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom.common_assembly_item_id is null
      and bom.assembly_type               = 1   -- Manufacturing
      and comp.component_quantity        <> 0
      and nvl(comp.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp.implementation_date,sysdate+1),
        'Y', nvl(comp.implementation_date,sysdate))
      group by
      comp.bill_sequence_id,
      1, -- level_num
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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
      -- End revision for version 1.22
      1, -- level_num
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id, -- level_1_parent_assy_id
      -- End revision for version 1.22
      -999, -- level_2_parent_assy_id
      -999, -- level_3_parent_assy_id
      -999, -- level_4_parent_assy_id
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- level_1_from_phantom_assy
      -- 0, -- level_2_from_phantom_assy
      -- 0, -- level_3_from_phantom_assy
      -- 0, -- level_4_from_phantom_assy
      -- Revision for version 1.21
      0, -- level_1_comp_is_phantom
      0, -- level_2_comp_is_phantom
      0, -- level_3_comp_is_phantom
      0, -- level_4_comp_is_phantom
      -- Revision for version 1.21
      -- msiv_parent.concatenated_segments, -- level_1_parent_assy
      msiv_comp.concatenated_segments, -- level_1_component
      null, -- level_2_component
      null, -- level_3_component
      null, -- level_4_component
      -- End revision for version 1.6
      comp.operation_seq_num,
      -- Revision for version 1.14
      comp.component_sequence_id,
      -- Revision for version 1.27
      comp.item_num,
      -- Revision for version 1.21
      -- wdj.organization_id,
      comp.component_item_id,
      comp.component_quantity,
      -- Revision for version 1.22
      -- comp.last_update_date,
      comp.disable_date,
      comp.planning_factor,
      comp.component_yield_factor,
      comp.include_in_cost_rollup,
      comp.basis_type,
      -- Revision for version 1.17
      -- comp.wip_supply_type,
      coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0), -- wip_supply_type
      -- Revision for version 1.6 and 1.22
      msiv_comp.concatenated_segments, -- component_number
      msiv_comp.description, -- component_description
      nvl(msiv_comp.item_type,'X'), -- item_type
      msiv_comp.planning_make_buy_code, -- comp_planning_make_buy_code
      msiv_comp.inventory_item_status_code, -- component_item_status_code
      msiv_comp.primary_uom_code, -- component_uom_code
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- phantom_parent
      -- End revision for version 1.6
      comp.supply_subinventory,
      comp.supply_locator_id
      union all
      -- Second BOM Explosion
      -- Get the components (level 2) from the phantoms from level 1 on the BOM
      -- Revision for version 1.10, add hint
      select /*+ leading(wdj) */ comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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,
      -- End revision for version 1.22
      2 level_num,
      -- Revision for version 1.16
      -- Revision for version 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id level_1_parent_assy_id,
      -- End revision for version 1.22
      bom_phtm.assembly_item_id level_2_parent_assy_id,
      -999 level_3_parent_assy_id,
      -999 level_4_parent_assy_id,
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) level_1_from_phantom_assy,
      -- 1 level_2_from_phantom_assy,
      -- 0 level_3_from_phantom_assy, -- 0 is no
      -- 0 level_4_from_phantom_assy, -- 0 is no
      1 level_1_comp_is_phantom,
      0 level_2_comp_is_phantom, -- 0 is no
      0 level_3_comp_is_phantom, -- 0 is no
      0 level_4_comp_is_phantom, -- 0 is no
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments level_1_parent_assy,
      msiv_comp.concatenated_segments level_1_component,
      msiv_comp2.concatenated_segments level_2_component,
      null level_3_component,
      null level_4_component,
      comp_phtm.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm.component_quantity,
      comp_phtm.component_quantity * comp.component_quantity component_quantity,
      -- End revision for version 1.8
      max(comp_phtm.effectivity_date) effectivity_date,
      -- Revision for version 1.22
      -- comp_phtm.last_update_date,
      comp_phtm.disable_date,
      comp_phtm.planning_factor,
      comp_phtm.component_yield_factor,
      comp_phtm.include_in_cost_rollup,
      comp_phtm.basis_type,
      -- Revision for version 1.17
      -- comp_phtm.wip_supply_type,
      coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0) wip_supply_type,
      -- Revision for version 1.22
      msiv_comp2.concatenated_segments component_number,
      msiv_comp2.description component_description,
      nvl(msiv_comp2.item_type,'X') item_type,
      msiv_comp2.planning_make_buy_code comp_planning_make_buy_code,
      msiv_comp2.inventory_item_status_code component_item_status_code,
      msiv_comp2.primary_uom_code component_uom_code,
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1 phantom_parent, -- 1 is yes
      comp_phtm.supply_subinventory,
      comp_phtm.supply_locator_id
      from bom_structures_b bom,                        -- Get the assemblies based on WIP
      -- Revision for version 1.22
      -- mtl_system_items_vl msiv_parent,
      bom_components_b comp,          -- Get the components on the assemblies, at level 1
      mtl_system_items_vl msiv_comp,  -- Restrict to components which are phantoms, at level 1
      bom_structures_b bom_phtm,                   -- Get the boms for the phantoms, at level 1
      bom_components_b comp_phtm,     -- Get the components on phantom assemblies at level 2
      mtl_system_items_vl msiv_comp2, -- Only select components which are not phantoms, at level 2
      -- Revision for version 1.16
      -- wdj_assys -- Limit to assemblies on WIP jobs
      wdj                             -- List of WIP Jobs
      -- ======================================================
      -- Get assemblies and components based on WIP jobs
      -- ======================================================
      where bom.assembly_item_id            = wdj.primary_item_id
      and bom.organization_id             = wdj.organization_id
      and bom.bill_sequence_id            = comp.bill_sequence_id
      and msiv_comp.inventory_item_id     = comp.component_item_id
      and msiv_comp.organization_id       = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp.item_type,'X')    = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.22
      -- and msiv_parent.inventory_item_id   = bom.assembly_item_id
      -- and msiv_parent.organization_id     = wdj.organization_id
      -- End revision for version 1.22
      -- Revision for version 1.16
      -- and comp.effectivity_date          <= sysdate
      -- and nvl(comp.disable_date, sysdate+1) >  sysdate
      and comp.effectivity_date          <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp.disable_date, sysdate+1) >  
      case
         when comp.disable_date is null then sysdate
         when comp.disable_date >= sysdate then sysdate
         when comp.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom.assembly_item_id
        and bom2.organization_id          = bom.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom.common_assembly_item_id is null
      and bom.assembly_type               = 1   -- Manufacturing
      and comp.component_quantity        <> 0
      and nvl(comp.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp.implementation_date,sysdate+1),
        'Y', nvl(comp.implementation_date,sysdate))
      -- ======================================================
      -- Get phantom assemblies and their components
      -- ======================================================
      and bom_phtm.assembly_item_id       = comp.component_item_id
      and bom_phtm.organization_id        = wdj.organization_id
      and comp_phtm.bill_sequence_id      = bom_phtm.bill_sequence_id
      and msiv_comp2.inventory_item_id    = comp_phtm.component_item_id
      and msiv_comp2.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp2.item_type,'X')  <> 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0) <> 6 -- Not Phantom
      -- Revision for version 1.16
      -- and comp_phtm.effectivity_date          <= sysdate
      -- and nvl(comp_phtm.disable_date, sysdate+1) >  sysdate
      and comp_phtm.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm.disable_date, sysdate+1) >  
      case
         when comp_phtm.disable_date is null then sysdate
         when comp_phtm.disable_date >= sysdate then sysdate
         when comp_phtm.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom_phtm.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom_phtm.assembly_item_id
        and bom2.organization_id          = bom_phtm.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom_phtm.common_assembly_item_id is null
      and bom_phtm.assembly_type          = 1   -- Manufacturing
      and comp_phtm.component_quantity   <> 0
      and nvl(comp_phtm.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm.implementation_date,sysdate))
      -- Revision for version 1.30
      -- Only include components included in the cost rollup
      and comp.include_in_cost_rollup     = 1
      group by
      comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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
      -- End revision for version 1.22
      2, -- level_num
      -- Revision for version 1.16
      -- Revision for version 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id, -- level_1_parent_assy_id
      -- End revision for version 1.22
      bom_phtm.assembly_item_id, -- level_2_parent_assy_id
      -999, -- level_3_parent_assy_id
      -999, -- level_4_parent_assy_id
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- level_1_from_phantom_assy
      -- 1, -- level_2_from_phantom_assy
      -- 0, -- level_3_from_phantom_assy
      -- 0, -- level_4_from_phantom_assy
      -- End revision for version 1.21
      1, -- level_1_comp_is_phantom
      0, -- level_2_comp_is_phantom
      0, -- level_3_comp_is_phantom
      0, -- level_4_comp_is_phantom
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments, -- level_1_parent_assy
      msiv_comp.concatenated_segments, -- level_1_component
      msiv_comp2.concatenated_segments, -- level_2_component
      null, -- level_3_component
      null, -- level_4_component
      -- End revision for version 1.6
      comp_phtm.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm.component_quantity,
      comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      -- Revision for version 1.22
      -- comp_phtm.last_update_date,
      comp_phtm.disable_date,
      comp_phtm.planning_factor,
      comp_phtm.component_yield_factor,
      comp_phtm.include_in_cost_rollup,
      comp_phtm.basis_type,
      -- Revision for version 1.17
      -- comp_phtm.wip_supply_type,
      coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0), -- wip_supply_type
      -- Revision for version 1.6 and 1.22
      msiv_comp2.concatenated_segments, -- component_number
      msiv_comp2.description, -- component_description
      nvl(msiv_comp2.item_type,'X'), -- item_type
      msiv_comp2.planning_make_buy_code, -- comp_planning_make_buy_code
      msiv_comp2.inventory_item_status_code, -- component_item_status_code
      msiv_comp2.primary_uom_code, -- component_uom_code
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1, -- phantom_parent
      -- End revision for version 1.6
      comp_phtm.supply_subinventory,
      comp_phtm.supply_locator_id
      union all
      -- Third BOM Explosion
      -- Get the components (level 3) from the phantoms which report to phantoms
      -- Revision for version 1.10, add hint
      select /*+ leading(wdj) */ comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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,
      -- End revision for version 1.22
      3 level_num,
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- bom.assembly_item_id level_1_parent_assy_id,
      -- End for revision for version 1.22
      bom_phtm.assembly_item_id level_2_parent_assy_id,
      bom_phtm2.assembly_item_id level_3_parent_assy_id,
      -999 level_4_parent_assy_id,
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) level_1_from_phantom_assy,
      -- 1 level_2_from_phantom_assy,
      -- 1 level_3_from_phantom_assy,
      -- 0 level_4_from_phantom_assy, -- 0 is no
      -- End revision for version 1.21
      1 level_1_comp_is_phantom,
      1 level_2_comp_is_phantom,
      0 level_3_comp_is_phantom, -- 0 is no
      0 level_4_comp_is_phantom, -- 0 is no
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments level_1_parent_assy,
      msiv_comp.concatenated_segments level_1_component,
      msiv_comp2.concatenated_segments level_2_component,
      msiv_comp3.concatenated_segments level_3_component,
      null level_4_component,
      -- End revision for version 1.6
      comp_phtm2.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm2.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm2.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm2.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm2.component_quantity,
      comp_phtm2.component_quantity * comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      max(comp_phtm2.effectivity_date) effectivity_date,
      -- Revision for version 1.22
      -- comp_phtm2.last_update_date,
      comp_phtm2.disable_date,
      comp_phtm2.planning_factor,
      comp_phtm2.component_yield_factor,
      comp_phtm2.include_in_cost_rollup,
      comp_phtm2.basis_type,
      -- Revision for version 1.17
      -- comp_phtm2.wip_supply_type,
      coalesce(comp_phtm2.wip_supply_type, msiv_comp3.wip_supply_type, 0) wip_supply_type,
      -- Revision for version 1.22
      msiv_comp3.concatenated_segments component_number,
      msiv_comp3.description component_description,
      nvl(msiv_comp3.item_type,'X') item_type,
      msiv_comp3.planning_make_buy_code comp_planning_make_buy_code,
      msiv_comp3.inventory_item_status_code component_item_status_code,
      msiv_comp3.primary_uom_code component_uom_code,
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1 phantom_parent, -- 1 is yes
      comp_phtm2.supply_subinventory,
      comp_phtm2.supply_locator_id
      from bom_structures_b bom,                        -- Get the assemblies based on WIP, at level 1
      -- Revision for version 1.22
      -- mtl_system_items_vl msiv_parent,
      bom_components_b comp,          -- Get the components on the assemblies, at level 1
      mtl_system_items_vl msiv_comp,  -- Restrict to components which are phantoms, at level 1
      bom_structures_b bom_phtm,                   -- Get the boms for the phantoms, at level 1
      bom_components_b comp_phtm,     -- Get the components on phantom assemblies, at level 2
      mtl_system_items_vl msiv_comp2, -- Restrict to components which are phantoms, at level 2
      bom_structures_b bom_phtm2,                  -- Get the boms for the phantom assembles, at level 2
      bom_components_b comp_phtm2,    -- Get the components on phantom assemblies, at level 3
      mtl_system_items_vl msiv_comp3, -- Only select components which are not phantoms, at level 3
      -- Revision for version 1.16
      -- wdj_assys -- Limit to assemblies on WIP jobs
      wdj                             -- List of WIP Jobs
      -- ======================================================
      -- Get the assemblies and components based on WIP jobs
      -- ======================================================
      where bom.assembly_item_id            = wdj.primary_item_id
      and bom.organization_id             = wdj.organization_id
      and comp.bill_sequence_id           = bom.bill_sequence_id
      and msiv_comp.inventory_item_id     = comp.component_item_id
      and msiv_comp.organization_id       = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp.item_type,'X')    = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 1) = 6 -- Phantom
      -- Revision for version 1.22
      -- and msiv_parent.inventory_item_id   = bom.assembly_item_id
      -- and msiv_parent.organization_id     = wdj.organization_id
      -- End revision for version 1.22
      -- Revision for version 1.16
      -- and comp.effectivity_date          <= sysdate
      -- and nvl(comp.disable_date, sysdate+1) >  sysdate
      and comp.effectivity_date          <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp.disable_date, sysdate+1) >  
      case
         when comp.disable_date is null then sysdate
         when comp.disable_date >= sysdate then sysdate
         when comp.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom.assembly_item_id
        and bom2.organization_id          = bom.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom.common_assembly_item_id is null
      and bom.assembly_type               = 1   -- Manufacturing
      and comp.component_quantity        <> 0
      and nvl(comp.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp.implementation_date,sysdate+1),
        'Y', nvl(comp.implementation_date,sysdate))
      -- ======================================================
      -- Get phantom assemblies and their components at level 2
      -- ======================================================
      and bom_phtm.assembly_item_id       = comp.component_item_id
      and bom_phtm.organization_id        = wdj.organization_id
      and comp_phtm.bill_sequence_id      = bom_phtm.bill_sequence_id
      and msiv_comp2.inventory_item_id    = comp_phtm.component_item_id
      and msiv_comp2.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp2.item_type,'X')   = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.16
      -- and comp_phtm.effectivity_date          <= sysdate
      -- and nvl(comp_phtm.disable_date, sysdate+1) >  sysdate
      and comp_phtm.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm.disable_date, sysdate+1) >  
      case
         when comp_phtm.disable_date is null then sysdate
         when comp_phtm.disable_date >= sysdate then sysdate
         when comp_phtm.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom_phtm.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom_phtm.assembly_item_id
        and bom2.organization_id          = bom_phtm.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom_phtm.common_assembly_item_id is null
      and bom_phtm.assembly_type          = 1   -- Manufacturing
      and comp_phtm.component_quantity   <> 0
      and nvl(comp_phtm.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm.implementation_date,sysdate))
      -- ======================================================
      -- Get the phantom assemblies and their components at level 3
      -- ======================================================
      and bom_phtm2.assembly_item_id      = comp_phtm.component_item_id
      and bom_phtm2.organization_id       = wdj.organization_id
      and comp_phtm2.bill_sequence_id     = bom_phtm2.bill_sequence_id
      and msiv_comp3.inventory_item_id    = comp_phtm2.component_item_id
      and msiv_comp3.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp3.item_type,'X')  <> 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm2.wip_supply_type, msiv_comp3.wip_supply_type, 0) <> 6 -- Not Phantom
      -- Revision for version 1.16
      -- and comp_phtm2.effectivity_date          <= sysdate
      -- and nvl(comp_phtm2.disable_date, sysdate+1) >  sysdate
      and comp_phtm2.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm2.disable_date, sysdate+1) >  
      case
         when comp_phtm2.disable_date is null then sysdate
         when comp_phtm2.disable_date >= sysdate then sysdate
         when comp_phtm2.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom_phtm2.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom_phtm2.assembly_item_id
        and bom2.organization_id          = bom_phtm2.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom_phtm2.common_assembly_item_id is null
      and bom_phtm2.assembly_type         = 1   -- Manufacturing
      and comp_phtm2.component_quantity  <> 0
      and nvl(comp_phtm2.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm2.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm2.implementation_date,sysdate))
      -- Revision for version 1.30
      -- Only include components included in the cost rollup
      and comp.include_in_cost_rollup     = 1
      group by
      comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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
      -- End revision for version 1.22
      3, -- level_num
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id, -- level_1_parent_assy_id
      -- End revision for version 1.22
      bom_phtm.assembly_item_id, -- level_2_parent_assy_id
      bom_phtm2.assembly_item_id, -- level_3_parent_assy_id
      -999, -- level_4_parent_assy_id
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- level_1_from_phantom_assy
      -- 1, -- level_2_from_phantom_assy
      -- 1, -- level_3_from_phantom_assy
      -- 0, -- level_4_from_phantom_assy
      -- End revision for version 1.22
      1, -- level_1_comp_is_phantom
      1, -- level_2_comp_is_phantom
      0, -- level_3_comp_is_phantom
      0, -- level_4_comp_is_phantom
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments, -- level_1_parent_assy
      msiv_comp.concatenated_segments, -- level_1_component
      msiv_comp2.concatenated_segments, -- level_2_component
      msiv_comp3.concatenated_segments, -- level_3_component
      null, -- level_4_component
      -- Revision for version 1.6
      comp_phtm2.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm2.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm2.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm2.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm2.component_quantity,
      comp_phtm2.component_quantity * comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      -- Revision for version 1.22
      -- comp_phtm2.last_update_date,
      comp_phtm2.disable_date,
      comp_phtm2.planning_factor,
      comp_phtm2.component_yield_factor,
      comp_phtm2.include_in_cost_rollup,
      comp_phtm2.basis_type,
      -- Revision for version 1.17
      -- comp_phtm2.wip_supply_type,
      coalesce(comp_phtm2.wip_supply_type, msiv_comp3.wip_supply_type, 0), -- wip_supply_type
      -- Revision for version 1.6 and 1.22
      msiv_comp3.concatenated_segments, -- component_number
      msiv_comp3.description, -- component_description
      nvl(msiv_comp3.item_type,'X'), -- item_type
      msiv_comp3.planning_make_buy_code, -- comp_planning_make_buy_code
      msiv_comp3.inventory_item_status_code, -- component_item_status_code
      msiv_comp3.primary_uom_code, -- component_uom_code
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1, -- phantom_parent
      -- End revision for version 1.6
      comp_phtm2.supply_subinventory,
      comp_phtm2.supply_locator_id
      union all
      -- Fourth BOM Explosion
      -- Get the components (level 4) from the phantoms which report to phantoms which report to phantoms
      -- Revision for version 1.10, add hint
      select /*+ leading(wdj) */ comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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,
      -- End revision for version 1.22
      4 level_num,
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- bom.assembly_item_id level_1_parent_assy_id,
      -- End revision for version 1.22
      bom_phtm.assembly_item_id level_2_parent_assy_id,
      bom_phtm2.assembly_item_id level_3_parent_assy_id,
      bom_phtm3.assembly_item_id level_4_parent_assy_id,
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) level_1_from_phantom_assy,
      -- 1 level_2_from_phantom_assy,
      -- 1 level_3_from_phantom_assy,
      -- 0 level_4_from_phantom_assy,
      -- End revision for version 1.21
      1 level_1_comp_is_phantom,
      1 level_2_comp_is_phantom,
      1 level_3_comp_is_phantom,
      0 level_4_comp_is_phantom, -- 0 is no
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments level_1_parent_assy,
      msiv_comp.concatenated_segments level_1_component,
      msiv_comp2.concatenated_segments level_2_component,
      msiv_comp3.concatenated_segments level_3_component,
      msiv_comp4.concatenated_segments level_4_component,
      comp_phtm3.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm3.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm3.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm3.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm3.component_quantity,
      comp_phtm3.component_quantity * comp_phtm2.component_quantity * comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      max(comp_phtm3.effectivity_date) effectivity_date,
      -- Revision for version 1.22
      -- comp_phtm3.last_update_date,
      comp_phtm3.disable_date,
      comp_phtm3.planning_factor,
      comp_phtm3.component_yield_factor,
      comp_phtm3.include_in_cost_rollup,
      comp_phtm3.basis_type,
      -- Revision for version 1.17
      -- comp_phtm3.wip_supply_type,
      coalesce(comp_phtm3.wip_supply_type, msiv_comp4.wip_supply_type, 0) wip_supply_type,
      -- Revision for version 1.22
      msiv_comp4.concatenated_segments component_number,
      msiv_comp4.description component_description,
      nvl(msiv_comp4.item_type,'X') item_type,
      msiv_comp4.planning_make_buy_code comp_planning_make_buy_code,
      msiv_comp4.inventory_item_status_code component_item_status_code,
      msiv_comp4.primary_uom_code component_uom_code,
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1 phantom_parent, -- 1 is yes
      comp_phtm3.supply_subinventory,
      comp_phtm3.supply_locator_id
      from bom_structures_b bom,                        -- Get the assemblies based on WIP, at level 1
      -- Revision for version 1.22
      -- mtl_system_items_vl msiv_parent,
      bom_components_b comp,          -- Get the components on the assemblies, at level 1
      mtl_system_items_vl msiv_comp,  -- Restrict to components which are phantoms, at level 1
      bom_structures_b bom_phtm,                   -- Get the boms for the phantoms, at level 1
      bom_components_b comp_phtm,     -- Get the components on phantom assemblies, at level 2
      mtl_system_items_vl msiv_comp2, -- Restrict to components which are phantoms, at level 2
      bom_structures_b bom_phtm2,                  -- Get the boms for the phantom assembles, at level 2
      bom_components_b comp_phtm2,    -- Get the components on phantom assemblies, at level 3
      mtl_system_items_vl msiv_comp3, -- Restrict to components which are phantoms, at level 3
      bom_structures_b bom_phtm3,                  -- Get the boms for the phantom assembles, at level 3
      bom_components_b comp_phtm3,    -- Get the components on phantom assemblies, at level 4
      mtl_system_items_vl msiv_comp4, -- Only select components which are not phantoms, at level 4
      -- Revision for version 1.16
      -- wdj_assys -- Limit to assemblies on WIP jobs
      wdj                             -- List of WIP Jobs
      -- ======================================================
      -- Get the assemblies and components based on WIP jobs
      -- ======================================================
      where bom.assembly_item_id            = wdj.primary_item_id
      and bom.organization_id             = wdj.organization_id
      and comp.bill_sequence_id           = bom.bill_sequence_id
      and msiv_comp.inventory_item_id     = comp.component_item_id
      and msiv_comp.organization_id       = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp.item_type,'X')    = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.22
      -- and msiv_parent.inventory_item_id   = bom.assembly_item_id
      -- and msiv_parent.organization_id     = wdj.organization_id
      -- End revision for version 1.22
      -- Revision for version 1.16
      -- and comp.effectivity_date          <= sysdate
      -- and nvl(comp.disable_date, sysdate+1) >  sysdate
      and comp.effectivity_date          <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp.disable_date, sysdate+1) >  
      case
         when comp.disable_date is null then sysdate
         when comp.disable_date >= sysdate then sysdate
         when comp.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom.assembly_item_id
        and bom2.organization_id          = bom.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom.common_assembly_item_id is null
      and bom.assembly_type               = 1   -- Manufacturing
      and comp.component_quantity        <> 0
      and nvl(comp.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp.implementation_date,sysdate+1),
        'Y', nvl(comp.implementation_date,sysdate))
      -- ======================================================
      -- Get phantom assemblies and their components at level 2
      -- ======================================================
      and bom_phtm.assembly_item_id       = comp.component_item_id
      and bom_phtm.organization_id        = wdj.organization_id
      and comp_phtm.bill_sequence_id      = bom_phtm.bill_sequence_id
      and msiv_comp2.inventory_item_id    = comp_phtm.component_item_id
      and msiv_comp2.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp2.item_type,'X')   = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.16
      -- and comp_phtm.effectivity_date          <= sysdate
      -- and nvl(comp_phtm.disable_date, sysdate+1) >  sysdate
      and comp_phtm.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm.disable_date, sysdate+1) >  
      case
         when comp_phtm.disable_date is null then sysdate
         when comp_phtm.disable_date >= sysdate then sysdate
         when comp_phtm.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom_phtm.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom_phtm.assembly_item_id
        and bom2.organization_id          = bom_phtm.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom_phtm.common_assembly_item_id is null
      and bom_phtm.assembly_type          = 1   -- Manufacturing
      and comp_phtm.component_quantity   <> 0
      and nvl(comp_phtm.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm.implementation_date,sysdate))
      -- ======================================================
      -- Get the phantom assemblies and their components at level 3
      -- ======================================================
      and bom_phtm2.assembly_item_id      = comp_phtm.component_item_id
      and bom_phtm2.organization_id       = wdj.organization_id
      and comp_phtm2.bill_sequence_id     = bom_phtm2.bill_sequence_id
      and msiv_comp3.inventory_item_id    = comp_phtm2.component_item_id
      and msiv_comp3.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp3.item_type,'X')   = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm2.wip_supply_type, msiv_comp3.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.16
      -- and comp_phtm2.effectivity_date          <= sysdate
      -- and nvl(comp_phtm2.disable_date, sysdate+1) >  sysdate
      and comp_phtm2.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm2.disable_date, sysdate+1) >  
      case
         when comp_phtm2.disable_date is null then sysdate
         when comp_phtm2.disable_date >= sysdate then sysdate
         when comp_phtm2.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom_phtm2.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom_phtm2.assembly_item_id
        and bom2.organization_id          = bom_phtm2.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom_phtm2.common_assembly_item_id is null
      and bom_phtm2.assembly_type         = 1   -- Manufacturing
      and comp_phtm2.component_quantity  <> 0
      and nvl(comp_phtm2.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm2.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm2.implementation_date,sysdate))
      -- ======================================================
      -- Get the phantom assemblies and their components at level 4
      -- ======================================================
      and bom_phtm3.assembly_item_id      = comp_phtm2.component_item_id
      and bom_phtm3.organization_id       = wdj.organization_id
      and comp_phtm3.bill_sequence_id     = bom_phtm3.bill_sequence_id
      and msiv_comp4.inventory_item_id    = comp_phtm3.component_item_id
      and msiv_comp4.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- nvl(msiv_comp4.item_type,'X')  <> 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm3.wip_supply_type, msiv_comp4.wip_supply_type, 0) <> 6 -- Not Phantom
      -- Revision for version 1.16
      -- and comp_phtm3.effectivity_date          <= sysdate
      -- and nvl(comp_phtm3.disable_date, sysdate+1) >  sysdate
      and comp_phtm3.effectivity_date    <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm3.disable_date, sysdate+1) >  
      case
         when comp_phtm3.disable_date is null then sysdate
         when comp_phtm3.disable_date >= sysdate then sysdate
         when comp_phtm3.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27 and 1.30
      and bom_phtm3.alternate_bom_designator is null
      and not exists
       (select 'x'
        from bom_structures_b bom2
        where bom2.assembly_item_id         = bom_phtm3.assembly_item_id
        and bom2.organization_id          = bom_phtm3.organization_id
        and bom2.alternate_bom_designator = '&p_alt_bom_designator'
        and '&p_alt_bom_designator' is not null
       )
      -- End revision for version 1.27 and 1.30
      and bom_phtm3.common_assembly_item_id is null
      and bom_phtm3.assembly_type         = 1   -- Manufacturing
      and comp_phtm3.component_quantity  <> 0
      and nvl(comp_phtm3.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm3.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm3.implementation_date,sysdate))
      -- Revision for version 1.30
      -- Only include components included in the cost rollup
      and comp.include_in_cost_rollup     = 1
      group by
      comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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
      -- End revision for version 1.22
      4, -- level_num
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id, -- level_1_parent_assy_id
      -- End revision for version 1.22
      bom_phtm.assembly_item_id, -- level_2_parent_assy_id
      bom_phtm2.assembly_item_id, -- level_3_parent_assy_id
      bom_phtm3.assembly_item_id, -- level_4_parent_assy_id
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- level_1_from_phantom_assy
      -- 1, -- level_2_from_phantom_assy
      -- 1, -- level_3_from_phantom_assy
      -- 1, -- level_4_from_phantom_assy
      -- End revision for version 1.21
      1, -- level_1_comp_is_phantom
      1, -- level_2_comp_is_phantom
      1, -- level_3_comp_is_phantom
      0, -- level_4_comp_is_phantom
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments, -- level_1_parent_assy
      msiv_comp.concatenated_segments, -- level_1_component
      msiv_comp2.concatenated_segments, -- level_2_component
      msiv_comp3.concatenated_segments, -- level_3_component
      msiv_comp4.concatenated_segments, -- level_4_component
      -- End revision for version 1.6
      comp_phtm3.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm3.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm3.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm3.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm3.component_quantity,
      comp_phtm3.component_quantity * comp_phtm2.component_quantity * comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      -- Revision for version 1.22
      -- comp_phtm3.last_update_date,
      comp_phtm3.disable_date,
      comp_phtm3.planning_factor,
      comp_phtm3.component_yield_factor,
      comp_phtm3.include_in_cost_rollup,
      comp_phtm3.basis_type,
      -- Revision for version 1.17
      -- comp_phtm3.wip_supply_type,
      coalesce(comp_phtm3.wip_supply_type, msiv_comp4.wip_supply_type, 0), -- wip_supply_type
      -- Revision for version 1.6 and 1.22
      msiv_comp4.concatenated_segments, -- component_number
      msiv_comp4.description, -- component_description
      nvl(msiv_comp4.item_type,'X'), -- item_type
      msiv_comp4.planning_make_buy_code, -- comp_planning_make_buy_code
      msiv_comp4.inventory_item_status_code, -- component_item_status_code
      msiv_comp4.primary_uom_code, -- component_uom_code
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1, -- phantom_parent
      -- End revision for version 1.6
      comp_phtm3.supply_subinventory,
      comp_phtm3.supply_locator_id
      union all
      -- ======================================
      -- Get the alternate bills of material
      -- ======================================
      -- Get the non-phantom components (level 1) from the BOM
      -- Revision for version 1.10, add hint
      select /*+ leading(wdj) */ comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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,
      -- End revision for version 1.22
      1 level_num,
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- bom.assembly_item_id level_1_parent_assy_id,
      -- End revision for version 1.22
      -999 level_2_parent_assy_id,
      -999 level_3_parent_assy_id,
      -999 level_4_parent_assy_id,
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) level_1_from_phantom_assy,
      -- 0 level_2_from_phantom_assy, -- 0 is no
      -- 0 level_3_from_phantom_assy, -- 0 is no
      -- 0 level_4_from_phantom_assy, -- 0 is no
      -- End revision for version 1.21
      0 level_1_comp_is_phantom, -- 0 is no
      0 level_2_comp_is_phantom, -- 0 is no
      0 level_3_comp_is_phantom, -- 0 is no
      0 level_4_comp_is_phantom, -- 0 is no
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments level_1_parent_assy,
      -- wdj.assembly_number level_1_parent_assy,
      -- End revision for version 1.22
      msiv_comp.concatenated_segments level_1_component,
      null level_2_component,
      null level_3_component,
      null level_4_component,
      comp.operation_seq_num,
      -- Revision for version 1.14
      comp.component_sequence_id,
      -- Revision for version 1.27
      comp.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp.component_item_id,
      comp.component_quantity,
      max(comp.effectivity_date) effectivity_date,
      -- Revision for version 1.22
      -- comp.last_update_date,
      comp.disable_date,
      comp.planning_factor,
      comp.component_yield_factor,
      comp.include_in_cost_rollup,
      comp.basis_type,
      -- Revision for version 1.17
      -- comp.wip_supply_type,
      coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0) wip_supply_type,
      -- Revision for version 1.22
      msiv_comp.concatenated_segments component_number,
      msiv_comp.description component_description,
      nvl(msiv_comp.item_type,'X') item_type,
      msiv_comp.planning_make_buy_code comp_planning_make_buy_code,
      msiv_comp.inventory_item_status_code component_item_status_code,
      msiv_comp.primary_uom_code component_uom_code,
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) phantom_parent,
      comp.supply_subinventory,
      comp.supply_locator_id
      from bom_structures_b bom,          -- Get the assemblies based on WIP, at level 0
      -- Revision for version 1.22
      -- mtl_system_items_vl msiv_parent,
      bom_components_b comp,         -- Get the components on the assemblies, at level 1
      mtl_system_items_vl msiv_comp, -- Only select components which are not phantoms
      -- Revision for version 1.16
      -- wdj_assys -- Limit to assemblies on WIP jobs
      wdj                            -- List of WIP Jobs
      -- ======================================================
      -- Get assemblies and components based on WIP jobs
      -- ======================================================
      -- Revision for version 1.22, outer join BOMs to wdj
      where bom.assembly_item_id            = wdj.primary_item_id (+)
      and bom.organization_id             = wdj.organization_id (+)
      and bom.bill_sequence_id            = comp.bill_sequence_id
      and msiv_comp.inventory_item_id     = comp.component_item_id
      and msiv_comp.organization_id       = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp.item_type,'X')   <> 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0) <> 6 -- Not Phantom
      -- Revision for version 1.22
      -- and msiv_parent.inventory_item_id(+)= bom.assembly_item_id
      -- and msiv_parent.organization_id(+)  = bom.organization_id
      -- End revision for version 1.22
      -- Revision for version 1.16
      -- and comp.effectivity_date          <= sysdate
      -- and nvl(comp.disable_date, sysdate+1) >  sysdate
      and comp.effectivity_date          <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp.disable_date, sysdate+1) >  
      case
         when comp.disable_date is null then sysdate
         when comp.disable_date >= sysdate then sysdate
         when comp.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27
      and bom.alternate_bom_designator    = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom.common_assembly_item_id is null
      and bom.assembly_type               = 1   -- Manufacturing
      and comp.component_quantity        <> 0
      and nvl(comp.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp.implementation_date,sysdate+1),
        'Y', nvl(comp.implementation_date,sysdate))
      -- Revision for version 1.30
      -- Only include components included in the cost rollup
      and comp.include_in_cost_rollup     = 1
      group by
      comp.bill_sequence_id,
      1, -- level_num
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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
      -- End revision for version 1.22
      1, -- level_num
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id, -- level_1_parent_assy_id
      -- End revision for version 1.22
      -999, -- level_2_parent_assy_id
      -999, -- level_3_parent_assy_id
      -999, -- level_4_parent_assy_id
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- level_1_from_phantom_assy
      -- 0, -- level_2_from_phantom_assy
      -- 0, -- level_3_from_phantom_assy
      -- 0, -- level_4_from_phantom_assy
      -- Revision for version 1.21
      0, -- level_1_comp_is_phantom
      0, -- level_2_comp_is_phantom
      0, -- level_3_comp_is_phantom
      0, -- level_4_comp_is_phantom
      -- Revision for version 1.21
      -- msiv_parent.concatenated_segments, -- level_1_parent_assy
      msiv_comp.concatenated_segments, -- level_1_component
      null, -- level_2_component
      null, -- level_3_component
      null, -- level_4_component
      -- End revision for version 1.6
      comp.operation_seq_num,
      -- Revision for version 1.14
      comp.component_sequence_id,
      -- Revision for version 1.27
      comp.item_num,
      -- Revision for version 1.21
      -- wdj.organization_id,
      comp.component_item_id,
      comp.component_quantity,
      -- Revision for version 1.22
      -- comp.last_update_date,
      comp.disable_date,
      comp.planning_factor,
      comp.component_yield_factor,
      comp.include_in_cost_rollup,
      comp.basis_type,
      -- Revision for version 1.17
      -- comp.wip_supply_type,
      coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0), -- wip_supply_type
      -- Revision for version 1.6 and 1.22
      msiv_comp.concatenated_segments, -- component_number
      msiv_comp.description, -- component_description
      nvl(msiv_comp.item_type,'X'), -- item_type
      msiv_comp.planning_make_buy_code, -- comp_planning_make_buy_code
      msiv_comp.inventory_item_status_code, -- component_item_status_code
      msiv_comp.primary_uom_code, -- component_uom_code
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- phantom_parent
      -- End revision for version 1.6
      comp.supply_subinventory,
      comp.supply_locator_id
      union all
      -- Second BOM Explosion
      -- Get the components (level 2) from the phantoms from level 1 on the BOM
      -- Revision for version 1.10, add hint
      select /*+ leading(wdj) */ comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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,
      -- End revision for version 1.22
      2 level_num,
      -- Revision for version 1.16
      -- Revision for version 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id level_1_parent_assy_id,
      -- End revision for version 1.22
      bom_phtm.assembly_item_id level_2_parent_assy_id,
      -999 level_3_parent_assy_id,
      -999 level_4_parent_assy_id,
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) level_1_from_phantom_assy,
      -- 1 level_2_from_phantom_assy,
      -- 0 level_3_from_phantom_assy, -- 0 is no
      -- 0 level_4_from_phantom_assy, -- 0 is no
      1 level_1_comp_is_phantom,
      0 level_2_comp_is_phantom, -- 0 is no
      0 level_3_comp_is_phantom, -- 0 is no
      0 level_4_comp_is_phantom, -- 0 is no
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments level_1_parent_assy,
      msiv_comp.concatenated_segments level_1_component,
      msiv_comp2.concatenated_segments level_2_component,
      null level_3_component,
      null level_4_component,
      comp_phtm.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm.component_quantity,
      comp_phtm.component_quantity * comp.component_quantity component_quantity,
      -- End revision for version 1.8
      max(comp_phtm.effectivity_date) effectivity_date,
      -- Revision for version 1.22
      -- comp_phtm.last_update_date,
      comp_phtm.disable_date,
      comp_phtm.planning_factor,
      comp_phtm.component_yield_factor,
      comp_phtm.include_in_cost_rollup,
      comp_phtm.basis_type,
      -- Revision for version 1.17
      -- comp_phtm.wip_supply_type,
      coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0) wip_supply_type,
      -- Revision for version 1.22
      msiv_comp2.concatenated_segments component_number,
      msiv_comp2.description component_description,
      nvl(msiv_comp2.item_type,'X') item_type,
      msiv_comp2.planning_make_buy_code comp_planning_make_buy_code,
      msiv_comp2.inventory_item_status_code component_item_status_code,
      msiv_comp2.primary_uom_code component_uom_code,
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1 phantom_parent, -- 1 is yes
      comp_phtm.supply_subinventory,
      comp_phtm.supply_locator_id
      from bom_structures_b bom,                        -- Get the assemblies based on WIP
      -- Revision for version 1.22
      -- mtl_system_items_vl msiv_parent,
      bom_components_b comp,          -- Get the components on the assemblies, at level 1
      mtl_system_items_vl msiv_comp,  -- Restrict to components which are phantoms, at level 1
      bom_structures_b bom_phtm,                   -- Get the boms for the phantoms, at level 1
      bom_components_b comp_phtm,     -- Get the components on phantom assemblies at level 2
      mtl_system_items_vl msiv_comp2, -- Only select components which are not phantoms, at level 2
      -- Revision for version 1.16
      -- wdj_assys -- Limit to assemblies on WIP jobs
      wdj                             -- List of WIP Jobs
      -- ======================================================
      -- Get assemblies and components based on WIP jobs
      -- ======================================================
      where bom.assembly_item_id            = wdj.primary_item_id
      and bom.organization_id             = wdj.organization_id
      and bom.bill_sequence_id            = comp.bill_sequence_id
      and msiv_comp.inventory_item_id     = comp.component_item_id
      and msiv_comp.organization_id       = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp.item_type,'X')    = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.22
      -- and msiv_parent.inventory_item_id   = bom.assembly_item_id
      -- and msiv_parent.organization_id     = wdj.organization_id
      -- End revision for version 1.22
      -- Revision for version 1.16
      -- and comp.effectivity_date          <= sysdate
      -- and nvl(comp.disable_date, sysdate+1) >  sysdate
      and comp.effectivity_date          <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp.disable_date, sysdate+1) >  
      case
         when comp.disable_date is null then sysdate
         when comp.disable_date >= sysdate then sysdate
         when comp.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27
      and bom.alternate_bom_designator    = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom.common_assembly_item_id is null
      and bom.assembly_type               = 1   -- Manufacturing
      and comp.component_quantity        <> 0
      and nvl(comp.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp.implementation_date,sysdate+1),
        'Y', nvl(comp.implementation_date,sysdate))
      -- ======================================================
      -- Get phantom assemblies and their components
      -- ======================================================
      and bom_phtm.assembly_item_id       = comp.component_item_id
      and bom_phtm.organization_id        = wdj.organization_id
      and comp_phtm.bill_sequence_id      = bom_phtm.bill_sequence_id
      and msiv_comp2.inventory_item_id    = comp_phtm.component_item_id
      and msiv_comp2.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp2.item_type,'X')  <> 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0) <> 6 -- Not Phantom
      -- Revision for version 1.16
      -- and comp_phtm.effectivity_date          <= sysdate
      -- and nvl(comp_phtm.disable_date, sysdate+1) >  sysdate
      and comp_phtm.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm.disable_date, sysdate+1) >  
      case
         when comp_phtm.disable_date is null then sysdate
         when comp_phtm.disable_date >= sysdate then sysdate
         when comp_phtm.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
      -- End revision for version 1.16
      -- Revision for version 1.27
      and bom_phtm.alternate_bom_designator = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom_phtm.common_assembly_item_id is null
      and bom_phtm.assembly_type          = 1   -- Manufacturing
      and comp_phtm.component_quantity   <> 0
      and nvl(comp_phtm.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm.implementation_date,sysdate))
      -- Revision for version 1.30
      -- Only include components included in the cost rollup
      and comp.include_in_cost_rollup     = 1
      group by
      comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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
      -- End revision for version 1.22
      2, -- level_num
      -- Revision for version 1.16
      -- Revision for version 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id, -- level_1_parent_assy_id
      -- End revision for version 1.22
      bom_phtm.assembly_item_id, -- level_2_parent_assy_id
      -999, -- level_3_parent_assy_id
      -999, -- level_4_parent_assy_id
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- level_1_from_phantom_assy
      -- 1, -- level_2_from_phantom_assy
      -- 0, -- level_3_from_phantom_assy
      -- 0, -- level_4_from_phantom_assy
      -- End revision for version 1.21
      1, -- level_1_comp_is_phantom
      0, -- level_2_comp_is_phantom
      0, -- level_3_comp_is_phantom
      0, -- level_4_comp_is_phantom
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments, -- level_1_parent_assy
      msiv_comp.concatenated_segments, -- level_1_component
      msiv_comp2.concatenated_segments, -- level_2_component
      null, -- level_3_component
      null, -- level_4_component
      -- End revision for version 1.6
      comp_phtm.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm.component_quantity,
      comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      -- Revision for version 1.22
      -- comp_phtm.last_update_date,
      comp_phtm.disable_date,
      comp_phtm.planning_factor,
      comp_phtm.component_yield_factor,
      comp_phtm.include_in_cost_rollup,
      comp_phtm.basis_type,
      -- Revision for version 1.17
      -- comp_phtm.wip_supply_type,
      coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0), -- wip_supply_type
      -- Revision for version 1.6 and 1.22
      msiv_comp2.concatenated_segments, -- component_number
      msiv_comp2.description, -- component_description
      nvl(msiv_comp2.item_type,'X'), -- item_type
      msiv_comp2.planning_make_buy_code, -- comp_planning_make_buy_code
      msiv_comp2.inventory_item_status_code, -- component_item_status_code
      msiv_comp2.primary_uom_code, -- component_uom_code
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1, -- phantom_parent
      -- End revision for version 1.6
      comp_phtm.supply_subinventory,
      comp_phtm.supply_locator_id
      union all
      -- Third BOM Explosion
      -- Get the components (level 3) from the phantoms which report to phantoms
      -- Revision for version 1.10, add hint
      select /*+ leading(wdj) */ comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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,
      -- End revision for version 1.22
      3 level_num,
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- bom.assembly_item_id level_1_parent_assy_id,
      -- End for revision for version 1.22
      bom_phtm.assembly_item_id level_2_parent_assy_id,
      bom_phtm2.assembly_item_id level_3_parent_assy_id,
      -999 level_4_parent_assy_id,
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) level_1_from_phantom_assy,
      -- 1 level_2_from_phantom_assy,
      -- 1 level_3_from_phantom_assy,
      -- 0 level_4_from_phantom_assy, -- 0 is no
      -- End revision for version 1.21
      1 level_1_comp_is_phantom,
      1 level_2_comp_is_phantom,
      0 level_3_comp_is_phantom, -- 0 is no
      0 level_4_comp_is_phantom, -- 0 is no
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments level_1_parent_assy,
      msiv_comp.concatenated_segments level_1_component,
      msiv_comp2.concatenated_segments level_2_component,
      msiv_comp3.concatenated_segments level_3_component,
      null level_4_component,
      -- End revision for version 1.6
      comp_phtm2.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm2.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm2.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm2.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm2.component_quantity,
      comp_phtm2.component_quantity * comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      max(comp_phtm2.effectivity_date) effectivity_date,
      -- Revision for version 1.22
      -- comp_phtm2.last_update_date,
      comp_phtm2.disable_date,
      comp_phtm2.planning_factor,
      comp_phtm2.component_yield_factor,
      comp_phtm2.include_in_cost_rollup,
      comp_phtm2.basis_type,
      -- Revision for version 1.17
      -- comp_phtm2.wip_supply_type,
      coalesce(comp_phtm2.wip_supply_type, msiv_comp3.wip_supply_type, 0) wip_supply_type,
      -- Revision for version 1.22
      msiv_comp3.concatenated_segments component_number,
      msiv_comp3.description component_description,
      nvl(msiv_comp3.item_type,'X') item_type,
      msiv_comp3.planning_make_buy_code comp_planning_make_buy_code,
      msiv_comp3.inventory_item_status_code component_item_status_code,
      msiv_comp3.primary_uom_code component_uom_code,
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1 phantom_parent, -- 1 is yes
      comp_phtm2.supply_subinventory,
      comp_phtm2.supply_locator_id
      from bom_structures_b bom,                        -- Get the assemblies based on WIP, at level 1
      -- Revision for version 1.22
      -- mtl_system_items_vl msiv_parent,
      bom_components_b comp,          -- Get the components on the assemblies, at level 1
      mtl_system_items_vl msiv_comp,  -- Restrict to components which are phantoms, at level 1
      bom_structures_b bom_phtm,                   -- Get the boms for the phantoms, at level 1
      bom_components_b comp_phtm,     -- Get the components on phantom assemblies, at level 2
      mtl_system_items_vl msiv_comp2, -- Restrict to components which are phantoms, at level 2
      bom_structures_b bom_phtm2,                  -- Get the boms for the phantom assembles, at level 2
      bom_components_b comp_phtm2,    -- Get the components on phantom assemblies, at level 3
      mtl_system_items_vl msiv_comp3, -- Only select components which are not phantoms, at level 3
      -- Revision for version 1.16
      -- wdj_assys -- Limit to assemblies on WIP jobs
      wdj                             -- List of WIP Jobs
      -- ======================================================
      -- Get the assemblies and components based on WIP jobs
      -- ======================================================
      where bom.assembly_item_id            = wdj.primary_item_id
      and bom.organization_id             = wdj.organization_id
      and comp.bill_sequence_id           = bom.bill_sequence_id
      and msiv_comp.inventory_item_id     = comp.component_item_id
      and msiv_comp.organization_id       = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp.item_type,'X')    = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 1) = 6 -- Phantom
      -- Revision for version 1.22
      -- and msiv_parent.inventory_item_id   = bom.assembly_item_id
      -- and msiv_parent.organization_id     = wdj.organization_id
      -- End revision for version 1.22
      -- Revision for version 1.16
      -- and comp.effectivity_date          <= sysdate
      -- and nvl(comp.disable_date, sysdate+1) >  sysdate
      and comp.effectivity_date          <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp.disable_date, sysdate+1) >  
      case
         when comp.disable_date is null then sysdate
         when comp.disable_date >= sysdate then sysdate
         when comp.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27
      and bom.alternate_bom_designator    = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom.common_assembly_item_id is null
      and bom.assembly_type               = 1   -- Manufacturing
      and comp.component_quantity        <> 0
      and nvl(comp.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp.implementation_date,sysdate+1),
        'Y', nvl(comp.implementation_date,sysdate))
      -- ======================================================
      -- Get phantom assemblies and their components at level 2
      -- ======================================================
      and bom_phtm.assembly_item_id       = comp.component_item_id
      and bom_phtm.organization_id        = wdj.organization_id
      and comp_phtm.bill_sequence_id      = bom_phtm.bill_sequence_id
      and msiv_comp2.inventory_item_id    = comp_phtm.component_item_id
      and msiv_comp2.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp2.item_type,'X')   = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.16
      -- and comp_phtm.effectivity_date          <= sysdate
      -- and nvl(comp_phtm.disable_date, sysdate+1) >  sysdate
      and comp_phtm.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm.disable_date, sysdate+1) >  
      case
         when comp_phtm.disable_date is null then sysdate
         when comp_phtm.disable_date >= sysdate then sysdate
         when comp_phtm.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27
      and bom_phtm.alternate_bom_designator = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom_phtm.common_assembly_item_id is null
      and bom_phtm.assembly_type          = 1   -- Manufacturing
      and comp_phtm.component_quantity   <> 0
      and nvl(comp_phtm.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm.implementation_date,sysdate))
      -- ======================================================
      -- Get the phantom assemblies and their components at level 3
      -- ======================================================
      and bom_phtm2.assembly_item_id      = comp_phtm.component_item_id
      and bom_phtm2.organization_id       = wdj.organization_id
      and comp_phtm2.bill_sequence_id     = bom_phtm2.bill_sequence_id
      and msiv_comp3.inventory_item_id    = comp_phtm2.component_item_id
      and msiv_comp3.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp3.item_type,'X')  <> 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm2.wip_supply_type, msiv_comp3.wip_supply_type, 0) <> 6 -- Not Phantom
      -- Revision for version 1.16
      -- and comp_phtm2.effectivity_date          <= sysdate
      -- and nvl(comp_phtm2.disable_date, sysdate+1) >  sysdate
      and comp_phtm2.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm2.disable_date, sysdate+1) >  
      case
         when comp_phtm2.disable_date is null then sysdate
         when comp_phtm2.disable_date >= sysdate then sysdate
         when comp_phtm2.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27
      and bom_phtm2.alternate_bom_designator = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom_phtm2.common_assembly_item_id is null
      and bom_phtm2.assembly_type         = 1   -- Manufacturing
      and comp_phtm2.component_quantity  <> 0
      and nvl(comp_phtm2.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm2.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm2.implementation_date,sysdate))
      -- Revision for version 1.30
      -- Only include components included in the cost rollup
      and comp.include_in_cost_rollup     = 1
      group by
      comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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
      -- End revision for version 1.22
      3, -- level_num
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id, -- level_1_parent_assy_id
      -- End revision for version 1.22
      bom_phtm.assembly_item_id, -- level_2_parent_assy_id
      bom_phtm2.assembly_item_id, -- level_3_parent_assy_id
      -999, -- level_4_parent_assy_id
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- level_1_from_phantom_assy
      -- 1, -- level_2_from_phantom_assy
      -- 1, -- level_3_from_phantom_assy
      -- 0, -- level_4_from_phantom_assy
      -- End revision for version 1.22
      1, -- level_1_comp_is_phantom
      1, -- level_2_comp_is_phantom
      0, -- level_3_comp_is_phantom
      0, -- level_4_comp_is_phantom
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments, -- level_1_parent_assy
      msiv_comp.concatenated_segments, -- level_1_component
      msiv_comp2.concatenated_segments, -- level_2_component
      msiv_comp3.concatenated_segments, -- level_3_component
      null, -- level_4_component
      -- Revision for version 1.6
      comp_phtm2.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm2.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm2.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm2.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm2.component_quantity,
      comp_phtm2.component_quantity * comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      -- Revision for version 1.22
      -- comp_phtm2.last_update_date,
      comp_phtm2.disable_date,
      comp_phtm2.planning_factor,
      comp_phtm2.component_yield_factor,
      comp_phtm2.include_in_cost_rollup,
      comp_phtm2.basis_type,
      -- Revision for version 1.17
      -- comp_phtm2.wip_supply_type,
      coalesce(comp_phtm2.wip_supply_type, msiv_comp3.wip_supply_type, 0), -- wip_supply_type
      -- Revision for version 1.6 and 1.22
      msiv_comp3.concatenated_segments, -- component_number
      msiv_comp3.description, -- component_description
      nvl(msiv_comp3.item_type,'X'), -- item_type
      msiv_comp3.planning_make_buy_code, -- comp_planning_make_buy_code
      msiv_comp3.inventory_item_status_code, -- component_item_status_code
      msiv_comp3.primary_uom_code, -- component_uom_code
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1, -- phantom_parent
      -- End revision for version 1.6
      comp_phtm2.supply_subinventory,
      comp_phtm2.supply_locator_id
      union all
      -- Fourth BOM Explosion
      -- Get the components (level 4) from the phantoms which report to phantoms which report to phantoms
      -- Revision for version 1.10, add hint
      select /*+ leading(wdj) */ comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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,
      -- End revision for version 1.22
      4 level_num,
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- bom.assembly_item_id level_1_parent_assy_id,
      -- End revision for version 1.22
      bom_phtm.assembly_item_id level_2_parent_assy_id,
      bom_phtm2.assembly_item_id level_3_parent_assy_id,
      bom_phtm3.assembly_item_id level_4_parent_assy_id,
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0) level_1_from_phantom_assy,
      -- 1 level_2_from_phantom_assy,
      -- 1 level_3_from_phantom_assy,
      -- 0 level_4_from_phantom_assy,
      -- End revision for version 1.21
      1 level_1_comp_is_phantom,
      1 level_2_comp_is_phantom,
      1 level_3_comp_is_phantom,
      0 level_4_comp_is_phantom, -- 0 is no
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments level_1_parent_assy,
      msiv_comp.concatenated_segments level_1_component,
      msiv_comp2.concatenated_segments level_2_component,
      msiv_comp3.concatenated_segments level_3_component,
      msiv_comp4.concatenated_segments level_4_component,
      comp_phtm3.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm3.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm3.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm3.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm3.component_quantity,
      comp_phtm3.component_quantity * comp_phtm2.component_quantity * comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      max(comp_phtm3.effectivity_date) effectivity_date,
      -- Revision for version 1.22
      -- comp_phtm3.last_update_date,
      comp_phtm3.disable_date,
      comp_phtm3.planning_factor,
      comp_phtm3.component_yield_factor,
      comp_phtm3.include_in_cost_rollup,
      comp_phtm3.basis_type,
      -- Revision for version 1.17
      -- comp_phtm3.wip_supply_type,
      coalesce(comp_phtm3.wip_supply_type, msiv_comp4.wip_supply_type, 0) wip_supply_type,
      -- Revision for version 1.22
      msiv_comp4.concatenated_segments component_number,
      msiv_comp4.description component_description,
      nvl(msiv_comp4.item_type,'X') item_type,
      msiv_comp4.planning_make_buy_code comp_planning_make_buy_code,
      msiv_comp4.inventory_item_status_code component_item_status_code,
      msiv_comp4.primary_uom_code component_uom_code,
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1 phantom_parent, -- 1 is yes
      comp_phtm3.supply_subinventory,
      comp_phtm3.supply_locator_id
      from bom_structures_b bom,                        -- Get the assemblies based on WIP, at level 1
      -- Revision for version 1.22
      -- mtl_system_items_vl msiv_parent,
      bom_components_b comp,          -- Get the components on the assemblies, at level 1
      mtl_system_items_vl msiv_comp,  -- Restrict to components which are phantoms, at level 1
      bom_structures_b bom_phtm,                   -- Get the boms for the phantoms, at level 1
      bom_components_b comp_phtm,     -- Get the components on phantom assemblies, at level 2
      mtl_system_items_vl msiv_comp2, -- Restrict to components which are phantoms, at level 2
      bom_structures_b bom_phtm2,                  -- Get the boms for the phantom assembles, at level 2
      bom_components_b comp_phtm2,    -- Get the components on phantom assemblies, at level 3
      mtl_system_items_vl msiv_comp3, -- Restrict to components which are phantoms, at level 3
      bom_structures_b bom_phtm3,                  -- Get the boms for the phantom assembles, at level 3
      bom_components_b comp_phtm3,    -- Get the components on phantom assemblies, at level 4
      mtl_system_items_vl msiv_comp4, -- Only select components which are not phantoms, at level 4
      -- Revision for version 1.16
      -- wdj_assys -- Limit to assemblies on WIP jobs
      wdj                             -- List of WIP Jobs
      -- ======================================================
      -- Get the assemblies and components based on WIP jobs
      -- ======================================================
      where bom.assembly_item_id            = wdj.primary_item_id
      and bom.organization_id             = wdj.organization_id
      and comp.bill_sequence_id           = bom.bill_sequence_id
      and msiv_comp.inventory_item_id     = comp.component_item_id
      and msiv_comp.organization_id       = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp.item_type,'X')    = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp.wip_supply_type, msiv_comp.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.22
      -- and msiv_parent.inventory_item_id   = bom.assembly_item_id
      -- and msiv_parent.organization_id     = wdj.organization_id
      -- End revision for version 1.22
      -- Revision for version 1.16
      -- and comp.effectivity_date          <= sysdate
      -- and nvl(comp.disable_date, sysdate+1) >  sysdate
      and comp.effectivity_date          <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp.disable_date, sysdate+1) >  
      case
         when comp.disable_date is null then sysdate
         when comp.disable_date >= sysdate then sysdate
         when comp.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27
      and bom.alternate_bom_designator    = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom.common_assembly_item_id is null
      and bom.assembly_type               = 1   -- Manufacturing
      and comp.component_quantity        <> 0
      and nvl(comp.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp.implementation_date,sysdate+1),
        'Y', nvl(comp.implementation_date,sysdate))
      -- ======================================================
      -- Get phantom assemblies and their components at level 2
      -- ======================================================
      and bom_phtm.assembly_item_id       = comp.component_item_id
      and bom_phtm.organization_id        = wdj.organization_id
      and comp_phtm.bill_sequence_id      = bom_phtm.bill_sequence_id
      and msiv_comp2.inventory_item_id    = comp_phtm.component_item_id
      and msiv_comp2.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp2.item_type,'X')   = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm.wip_supply_type, msiv_comp2.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.16
      -- and comp_phtm.effectivity_date          <= sysdate
      -- and nvl(comp_phtm.disable_date, sysdate+1) >  sysdate
      and comp_phtm.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm.disable_date, sysdate+1) >  
      case
         when comp_phtm.disable_date is null then sysdate
         when comp_phtm.disable_date >= sysdate then sysdate
         when comp_phtm.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27
      and bom_phtm.alternate_bom_designator = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom_phtm.common_assembly_item_id is null
      and bom_phtm.assembly_type          = 1   -- Manufacturing
      and comp_phtm.component_quantity   <> 0
      and nvl(comp_phtm.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm.implementation_date,sysdate))
      -- ======================================================
      -- Get the phantom assemblies and their components at level 3
      -- ======================================================
      and bom_phtm2.assembly_item_id      = comp_phtm.component_item_id
      and bom_phtm2.organization_id       = wdj.organization_id
      and comp_phtm2.bill_sequence_id     = bom_phtm2.bill_sequence_id
      and msiv_comp3.inventory_item_id    = comp_phtm2.component_item_id
      and msiv_comp3.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- and nvl(msiv_comp3.item_type,'X')   = 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm2.wip_supply_type, msiv_comp3.wip_supply_type, 0) = 6 -- Phantom
      -- Revision for version 1.16
      -- and comp_phtm2.effectivity_date          <= sysdate
      -- and nvl(comp_phtm2.disable_date, sysdate+1) >  sysdate
      and comp_phtm2.effectivity_date     <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm2.disable_date, sysdate+1) >  
      case
         when comp_phtm2.disable_date is null then sysdate
         when comp_phtm2.disable_date >= sysdate then sysdate
         when comp_phtm2.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27
      and bom_phtm2.alternate_bom_designator = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom_phtm2.common_assembly_item_id is null
      and bom_phtm2.assembly_type         = 1   -- Manufacturing
      and comp_phtm2.component_quantity  <> 0
      and nvl(comp_phtm2.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm2.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm2.implementation_date,sysdate))
      -- ======================================================
      -- Get the phantom assemblies and their components at level 4
      -- ======================================================
      and bom_phtm3.assembly_item_id      = comp_phtm2.component_item_id
      and bom_phtm3.organization_id       = wdj.organization_id
      and comp_phtm3.bill_sequence_id     = bom_phtm3.bill_sequence_id
      and msiv_comp4.inventory_item_id    = comp_phtm3.component_item_id
      and msiv_comp4.organization_id      = wdj.organization_id
      -- Revision for version 1.13 and 1.17
      -- nvl(msiv_comp4.item_type,'X')  <> 'PH' -- phantom
      -- Revision for version 1.17
      and coalesce(comp_phtm3.wip_supply_type, msiv_comp4.wip_supply_type, 0) <> 6 -- Not Phantom
      -- Revision for version 1.16
      -- and comp_phtm3.effectivity_date          <= sysdate
      -- and nvl(comp_phtm3.disable_date, sysdate+1) >  sysdate
      and comp_phtm3.effectivity_date    <
      case
         -- Revision for version 1.21 and 1.24
         -- when wdj.schedule_close_date <= sysdate then wdj.schedule_close_date
         -- when wdj.date_closed <= sysdate then wdj.schedule_close_date
         when nvl(wdj.date_closed,sysdate) < wdj.schedule_close_date then nvl(wdj.date_closed,sysdate) + 1
         when nvl(wdj.date_closed,sysdate) >= wdj.schedule_close_date then wdj.schedule_close_date + 1
         -- End revision for version 1.21 and 1.24
         else sysdate
      end
      and nvl(comp_phtm3.disable_date, sysdate+1) >  
      case
         when comp_phtm3.disable_date is null then sysdate
         when comp_phtm3.disable_date >= sysdate then sysdate
         when comp_phtm3.disable_date < sysdate then wdj.date_closed
         else sysdate
      end
     -- End revision for version 1.16
      -- Revision for version 1.27
      and bom_phtm3.alternate_bom_designator = '&p_alt_bom_designator'
      and '&p_alt_bom_designator' is not null
      -- End revision for version 1.27
      and bom_phtm3.common_assembly_item_id is null
      and bom_phtm3.assembly_type         = 1   -- Manufacturing
      and comp_phtm3.component_quantity  <> 0
      and nvl(comp_phtm3.implementation_date,sysdate) =
       decode(:p_include_unimplemented_ECOs,                                           -- p_include_unimplemented_ECOs
        'N', nvl(comp_phtm3.implementation_date,sysdate+1),
        'Y', nvl(comp_phtm3.implementation_date,sysdate))
      -- Revision for version 1.30
      -- Only include components included in the cost rollup
      and comp.include_in_cost_rollup     = 1
      group by
      comp.bill_sequence_id,
      -- Revision for version 1.22
      wdj.report_type,
      wdj.period_name,
      wdj.organization_code,
      wdj.organization_id,
      wdj.primary_cost_method,
      wdj.material_account,
      wdj.class_code,
      wdj.class_type,
      wdj.wip_entity_id,
      wdj.project_id,
      wdj.status_type,
      wdj.primary_item_id,
      wdj.assembly_number,
      wdj.assy_description,
      wdj.assy_item_type,
      wdj.assy_item_status_code,
      wdj.assy_uom_code,
      wdj.planning_make_buy_code,
      wdj.std_lot_size,
      wdj.lot_number,
      wdj.creation_date,
      wdj.scheduled_start_date,
      wdj.date_released,
      wdj.date_completed,
      wdj.date_closed,
      wdj.schedule_close_date,
      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
      -- End revision for version 1.22
      4, -- level_num
      -- Revision for version 1.16 and 1.22
      -- wdj.wip_entity_id,
      -- Revision for version 1.6
      -- bom.assembly_item_id, -- level_1_parent_assy_id
      -- End revision for version 1.22
      bom_phtm.assembly_item_id, -- level_2_parent_assy_id
      bom_phtm2.assembly_item_id, -- level_3_parent_assy_id
      bom_phtm3.assembly_item_id, -- level_4_parent_assy_id
      -- Revision for version 1.21
      -- decode(nvl(msiv_parent.item_type,'X'), 'PH', 1, 0), -- level_1_from_phantom_assy
      -- 1, -- level_2_from_phantom_assy
      -- 1, -- level_3_from_phantom_assy
      -- 1, -- level_4_from_phantom_assy
      -- End revision for version 1.21
      1, -- level_1_comp_is_phantom
      1, -- level_2_comp_is_phantom
      1, -- level_3_comp_is_phantom
      0, -- level_4_comp_is_phantom
      -- Revision for version 1.22
      -- msiv_parent.concatenated_segments, -- level_1_parent_assy
      msiv_comp.concatenated_segments, -- level_1_component
      msiv_comp2.concatenated_segments, -- level_2_component
      msiv_comp3.concatenated_segments, -- level_3_component
      msiv_comp4.concatenated_segments, -- level_4_component
      -- End revision for version 1.6
      comp_phtm3.operation_seq_num,
      -- Revision for version 1.14
      comp_phtm3.component_sequence_id,
      -- Revision for version 1.27
      comp_phtm3.item_num,
      -- Revision for version 1.22
      -- wdj.organization_id,
      comp_phtm3.component_item_id,
      -- Revision for version 1.8
      -- The parent sub-assembly may have a quantity required greater than one
      -- comp_phtm3.component_quantity,
      comp_phtm3.component_quantity * comp_phtm2.component_quantity * comp_phtm.component_quantity * comp.component_quantity,
      -- End revision for version 1.8
      -- Revision for version 1.22
      -- comp_phtm3.last_update_date,
      comp_phtm3.disable_date,
      comp_phtm3.planning_factor,
      comp_phtm3.component_yield_factor,
      comp_phtm3.include_in_cost_rollup,
      comp_phtm3.basis_type,
      -- Revision for version 1.17
      -- comp_phtm3.wip_supply_type,
      coalesce(comp_phtm3.wip_supply_type, msiv_comp4.wip_supply_type, 0), -- wip_supply_type
      -- Revision for version 1.6 and 1.22
      msiv_comp4.concatenated_segments, -- component_number
      msiv_comp4.description, -- component_description
      nvl(msiv_comp4.item_type,'X'), -- item_type
      msiv_comp4.planning_make_buy_code, -- comp_planning_make_buy_code
      msiv_comp4.inventory_item_status_code, -- component_item_status_code
      msiv_comp4.primary_uom_code, -- component_uom_code
      -- End revision for version 1.22
      -- Revision for version 1.21
      -- 1, -- phantom_parent
      -- End revision for version 1.6
      comp_phtm3.supply_subinventory,
      comp_phtm3.supply_locator_id
     ) comp2
    ) comp
    -- End of revision for version 1.5
    -- Revision for version 1.22
    -- wdj -- get the corrected wip qty completed and qty scrapped
    -- ===========================================
    -- WIP_Job Entity and Class joins
    -- ===========================================
    -- Revision for version 1.22
    -- where bom.organization_id       = wdj.organization_id
    -- and bom.assembly_item_id      = wdj.primary_item_id
    -- and bom.bill_sequence_id      = comp.bill_sequence_id
    -- Revision for version 1.16
    -- and wdj.wip_entity_id         = comp.wip_entity_id
    -- and bom.assembly_type         = 1   -- Manufacturing
    -- and bom.common_assembly_item_id is null
    -- Revision for version 1.16
    -- and nvl(comp.effectivity_date, comp.last_update_date) < wdj.schedule_close_date + 1
    -- and nvl(comp.disable_date, sysdate+1) >  sysdate
    -- End revision for version 1.16
    -- Revision for version 1.12
    -- and cct.cost_type_id          = wdj.primary_cost_method
    where cic_comp.organization_id (+)   = comp.organization_id
    and cic_comp.inventory_item_id (+) = comp.component_item_id
    -- End revision for version 1.22
   ) mtl2
   group by
   mtl2.report_type,
   mtl2.period_name,
   mtl2.organization_code,
   mtl2.organization_id,
   mtl2.primary_cost_method,
   -- Revision for version 1.12
   -- mtl2.primary_cost_type,
   mtl2.account,
   mtl2.class_code,
   mtl2.class_type,
   mtl2.wip_entity_id,
   mtl2.project_id,
   mtl2.status_type,
   mtl2.primary_item_id,
   -- Revision for version 1.22
   mtl2.assembly_number,
   mtl2.assy_description,
   mtl2.assy_item_type,
   mtl2.assy_item_status_code,
   mtl2.assy_uom_code,
   mtl2.planning_make_buy_code,
   mtl2.std_lot_size,
   -- End revision for version 1.22
   -- Revision for version 1.7
   mtl2.lot_number,
   mtl2.creation_date,
   -- Revision for version 1.5
   mtl2.scheduled_start_date,
   mtl2.date_released,
   mtl2.date_completed,
   mtl2.date_closed,
   -- Revision for version 1.18
   mtl2.schedule_close_date,
   mtl2.last_update_date,
   mtl2.start_quantity,
   mtl2.quantity_completed,
   mtl2.quantity_scrapped,
   mtl2.fg_total_qty,
   mtl2.inventory_item_id,
   -- Revision for version 1.14 and 1.30
   -- mtl2.department_id,
   -- Revision for version 1.19
   mtl2.inventory_item_id,
   -- Revision for version 1.12 and 1.14
   -- mtl2.level_num,
   -- Revision for version 1.14 and 1.30
   -- mtl2.operation_seq_num,
   -- Revision for version 1.27
   mtl2.item_num, -- item_op_seq 
   -- Revision for version 1.30
   -- mtl2.wip_supply_type,
   -- End revision for version 1.14
   -- Revision for version 1.6 and 1.22
   mtl2.component_number,
   mtl2.component_description,
   mtl2.component_item_type,
   mtl2.comp_planning_make_buy_code,
   mtl2.component_item_status_code,
   mtl2.component_uom_code,
   -- End revision for version 1.22
   -- Revision for version 1.28
   -- Revision for version 1.8
   -- mtl2.basis_type,
   mtl2.lot_basis_type,
   -- Revision for version 1.28
   mtl2.comp_lot_size,
   mtl2.lot_basis_cost,
   -- Revision for version 1.28
   -- mtl2.item_basis_type,
   mtl2.item_basis_cost,
   -- End revision for version 1.8
   mtl2.cost_type,
   mtl2.item_cost
  ) mtl
  group by
  mtl.report_type,
  mtl.period_name,
  mtl.organization_code,
  mtl.organization_id,
  mtl.primary_cost_method,
  mtl.account,
  mtl.class_code,
  mtl.class_type,
  mtl.wip_entity_id,
  mtl.project_id,
  mtl.status_type,
  mtl.primary_item_id,
  mtl.assembly_number,
  mtl.assy_description,
  mtl.assy_item_type,
  mtl.assy_item_status_code,
  mtl.assy_uom_code,
  mtl.planning_make_buy_code,
  mtl.std_lot_size,
  -- End revision for version 1.22
  -- Revision for version 1.7
  mtl.lot_number,
  mtl.creation_date,
  -- Revision for version 1.5
  mtl.scheduled_start_date,
  mtl.date_released,
  mtl.date_completed,
  mtl.date_closed,
  mtl.last_update_date,
  mtl.start_quantity,
  mtl.quantity_completed,
  mtl.quantity_scrapped,
  mtl.fg_total_qty,
  mtl.inventory_item_id,
  mtl.department_id,
  mtl.operation_seq_num,
  mtl.item_num,
  mtl.wip_supply_type,
  mtl.component_number,
  mtl.component_description,
  mtl.component_item_type,
  mtl.comp_planning_make_buy_code,
  mtl.component_item_status_code,
  mtl.component_uom_code,
  -- Revision for version 1.28
  -- Condense to a common value to get only one row
  case
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 0 then 1
    when mtl.wip_basis_type = 1 then 1
    when mtl.wip_basis_type = 2 then 2
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 1 then 1
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 2 then 2
    else 1
  end, -- component_basis_type
  -- End revision for version 1.28
  -- Revision for version 1.30
  mtl.include_in_cost_rollup,
  mtl.lot_basis_type,
  mtl.comp_lot_size,
  mtl.lot_basis_cost,
  -- Revision for version 1.28
  -- Condense to a common value to get only one row
  case
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 0 then 'Y'
    when mtl.wip_basis_type = 1 then 'Y'
    when mtl.wip_basis_type = 2 then 'N'
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 1 then 'Y'
    when mtl.wip_basis_type = 0 and mtl.comp_basis_type = 2 then 'N'
    else 'Y'
  end, -- item_basis_type
  -- End revision for version 1.28
  mtl.item_basis_cost,
  mtl.cost_type,
  mtl.item_cost,
  mtl.quantity_per_assembly,
  mtl.total_req_quantity,
  mtl.last_txn_date,
  mtl.quantity_issued,
  mtl.wip_std_component_value,
  mtl.applied_component_value,
  mtl.std_quantity_per_assembly,
  mtl.std_total_req_quantity,
  mtl.alternate_designator_code
 ) mtl_sum
-- ===========================================
-- Account, cost and department joins
-- ===========================================
where we.wip_entity_id                = mtl_sum.wip_entity_id
-- and msiv2.organization_id           = mtl_sum.organization_id
-- and msiv2.inventory_item_id         = mtl_sum.inventory_item_id  -- Component item
-- and muomv.uom_code                  = mtl_sum.primary_uom_code
and muomv.uom_code                  = mtl_sum.assy_uom_code
-- End revision for version 1.22
and misv.inventory_item_status_code = mtl_sum.assy_item_status_code
and muomv2.uom_code                 = mtl_sum.component_uom_code
and misv2.inventory_item_status_code = mtl_sum.component_item_status_code
and bd.department_id (+)            = mtl_sum.department_id
-- Revision for version 1.6
-- Screen out phantoms from the WIP BOM as these are never issued from stock
-- Phantoms on the WIP BOM have a negative operation_seq_num
and nvl(mtl_sum.operation_seq_num,0) > 0
-- These joins get the Item Lot_Size
and cic_assys.organization_id (+)   = mtl_sum.organization_id
and cic_assys.inventory_item_id (+) = mtl_sum.primary_item_id
and gcc.code_combination_id (+)     = mtl_sum.account
-- Revision for version 1.5
-- Remove noise from the report, if no quantities required do not report the component
-- and nvl(mtl_sum.quantity_per_assembly,0) + nvl(mtl_sum.std_quantity_per_assembly,0) <> 0
and nvl(mtl_sum.quantity_per_assembly,0) + nvl(mtl_sum.std_quantity_per_assembly,0) + round(mtl_sum.quantity_issued,3) <> 0
-- ===========================================
-- Lookup Codes
-- ===========================================
and ml1.lookup_type                 = 'WIP_CLASS_TYPE'
and ml1.lookup_code                 = mtl_sum.class_type
and ml2.lookup_type                 = 'WIP_JOB_STATUS'
and ml2.lookup_code                 = mtl_sum.status_type
and ml3.lookup_type                 = 'MTL_PLANNING_MAKE_BUY'
and ml3.lookup_code                 = mtl_sum.planning_make_buy_code
and ml4.lookup_type                 = 'MTL_PLANNING_MAKE_BUY'
and ml4.lookup_code                 = mtl_sum.comp_planning_make_buy_code
and ml5.lookup_type (+)             = 'WIP_SUPPLY'
and ml5.lookup_code (+)             = mtl_sum.wip_supply_type
and ml6.lookup_type                 = 'CST_BASIS'
-- Revision for version 1.28
-- and ml6.lookup_code                 = mtl_sum.basis_type
and ml6.lookup_code                 = mtl_sum.component_basis_type
-- Revision for version 1.30
and ml7.lookup_type                 = 'SYS_YES_NO'
and ml7.lookup_code                 = to_char(decode(mtl_sum.include_in_cost_rollup,0,2,1,1,2,2,2))
-- Revision for version 1.20, comment out Phantom Parent
-- Revision for version 1.6
-- and fl1.lookup_type                 = 'YES_NO'
-- and fl1.lookup_code                 = mtl_sum.phantom_parent
-- Revision for version 1.8
and fl2.lookup_type                 = 'YES_NO'
-- Revision for version 1.27
-- and fl2.lookup_code                 = cic_assys.rolled_up
and fl2.lookup_code                 = nvl(cic_assys.rolled_up,'N')
and fcl1.lookup_type (+)            = 'ITEM_TYPE'
and fcl1.lookup_code (+)            = mtl_sum.assy_item_type
and fcl2.lookup_type (+)            = 'ITEM_TYPE'
and fcl2.lookup_code (+)            = mtl_sum.component_item_type
-- ===========================================
-- Organization joins to the HR org model
-- ===========================================
and hoi.org_information_context     = 'Accounting Information'
and hoi.organization_id             = mtl_sum.organization_id
and hoi.organization_id             = haou.organization_id   -- this gets the organization name
and haou2.organization_id           = to_number(hoi.org_information3) -- this gets the operating unit id
and gl.ledger_id                    = to_number(hoi.org_information1) -- get the ledger_id
and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
and haou2.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
and 1=1                            -- p_component_number, p_include_bulk_items, p_operating_unit, p_ledger
-- order by Report_Type, Ledger, Operating_Unit, Org_Code, Period_Name, Accounts, WIP_Class, WIP_Job, Component, Item Num and Operation
order by
 mtl_sum.report_type,
 nvl(gl.short_name, gl.name),
 haou2.name, --  Operating_Unit
 mtl_sum.organization_code,
 &segment_columns_grp
 mtl_sum.class_code,
 we.wip_entity_name,
 -- Revision for version 1.14
 -- Revision for version 1.22
 -- msiv2.concatenated_segments,
 mtl_sum.operation_seq_num,
 -- Revision for 1.27
 mtl_sum.item_num,
 mtl_sum.component_number,
 bd.department_code
Parameter NameSQL textValidation
Report Option
 
LOV
Period Name
oap.period_name = :p_period_name
LOV
Cost Type
 
LOV
Include Scrap Quantities
 
LOV Oracle
Include Unreleased Jobs
wdj.status_type<> 1
LOV Oracle
Include Bulk Supply Items
nvl(mtl_sum.wip_supply_type,1) <> 4
LOV Oracle
Use Completion Quantities
 
LOV Oracle
Config/Lot Variances for Non-Std
 
LOV Oracle
Include Unimplemented ECOs
 
LOV Oracle
Alternate BOM Designator
select
alternate_designator_code
from bom_alternate_designators_vl badv 
where badv.display_name    = :p_alt_bom_designator
group by alternate_designator_code
LOV
Category Set 1
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'mtl_sum', p_item_id_column=>'primary_item_id') sql_text from dual
LOV
Category Set 2
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'mtl_sum', p_item_id_column=>'primary_item_id') sql_text from dual
LOV
Category Set 3
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'mtl_sum', p_item_id_column=>'primary_item_id') sql_text from dual
LOV
Organization Code
mp.organization_code = :p_org_code
LOV
Class Code
wdj.class_code = :p_class_code
LOV
Job Status
wdj.status_type=xxen_util.lookup_code(:p_job_status,'WIP_JOB_STATUS',700)
LOV
WIP Job
(wdj.organization_id, wdj.wip_entity_id) in (select we.organization_id, we.wip_entity_id from wip_entities we where we.wip_entity_name=:p_wip_job)
LOV
Component Number
mtl_sum.component_number = :p_component_number
LOV
Assembly Number
(wdj.organization_id, wdj.primary_item_id) in (select msibk.organization_id, msibk.inventory_item_id from mtl_system_items_b_kfv msibk where msibk.concatenated_segments=:p_assembly_number)
LOV
Operating Unit
haou2.name = :p_operating_unit
LOV
Ledger
gl.name = :p_ledger
LOV
Download
Blitz Report™