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 |