with wdj00 as
( select /*+ materialize */ wdj.wip_entity_id,min(mmt.transaction_date) transaction_date
from wip_discrete_jobs wdj,
org_acct_periods oap,
mtl_parameters mp,
wip_accounting_classes wac,
mtl_material_transactions mmt
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
mmt.transaction_source_id=wdj.wip_entity_id and
mmt.transaction_source_type_id=5 and
mmt.transaction_date >= oap.period_start_date and mmt.transaction_date < oap.schedule_close_date + 1 and
mmt.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 and -- p_org_code
3=3 and -- p_assembly_number
4=4 -- p_period_name, p_wip_job, wip_status, p_wip_class_code
group by wdj.wip_entity_id
),
wdj0 as
( select /*+ materialize */ 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 and wdj.date_closed < oap.schedule_close_date + 1 then 'Variance'
-- the job is open
when wdj.date_closed is null and((wdj.creation_date < oap.schedule_close_date + 1 and not exists (select null from mtl_material_transactions mmt where mmt.transaction_source_id=wdj.wip_entity_id and mmt.transaction_source_type_id=5 and mmt.organization_id=mp.organization_id))
or(wdj00.transaction_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,
wdj00
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
and wdj.wip_entity_id=wdj00.wip_entity_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 not exists (select null from mtl_material_transactions mmt where mmt.transaction_source_id=wdj.wip_entity_id and mmt.transaction_source_type_id=5 and mmt.organization_id=mp.organization_id))
or(wdj00.transaction_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((wdj.creation_date < oap.schedule_close_date + 1 and not exists (select null from mtl_material_transactions mmt where mmt.transaction_source_id=wdj.wip_entity_id and mmt.transaction_source_type_id=5 and mmt.organization_id=mp.organization_id))
or(wdj00.transaction_date<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 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,
mcacd.cost_element_id,
null lot_basis_type,
null lot_basis_cost,
null item_basis_type,
null item_basis_cost,
wdj.wip_entity_id,
max(nvl(mcacd.actual_cost,0)) item_cost
from cst_item_costs cic,
cst_cost_types cct,
mtl_parameters mp,
wdj wdj,
mtl_material_transactions mmt,
mtl_cst_actual_cost_details mcacd,
org_acct_periods oap
where 4=4 and
2=2 and -- p_org_code
mp.organization_id=cic.organization_id and
cic.cost_type_id=cct.cost_type_id and
mcacd.transaction_id = mmt.transaction_id and
mmt.inventory_item_id=mcacd.inventory_item_id and
mmt.organization_id = mcacd.organization_id and
mmt.transaction_source_id=wdj.wip_entity_id and
mmt.transaction_source_type_id=5 and
oap.organization_id=mp.organization_id and
mmt.transaction_date >= oap.period_start_date and mmt.transaction_date < oap.schedule_close_date + 1 and
cic.inventory_item_id= mcacd.inventory_item_id and
cic.organization_id= mcacd.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
)
group by
cic.inventory_item_id,
cic.organization_id,
cic.last_update_date,
cct.cost_type_id,
cct.cost_type,
mcacd.cost_element_id,
mcacd.level_type,
wdj.wip_entity_id
union all
select cic.inventory_item_id,
cic.organization_id,
cic.last_update_date,
cct.cost_type_id,
cct.cost_type,
cicd.cost_element_id,
max(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,
max(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,
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,
wdj.wip_entity_id,
nvl(cicd.item_cost,0) item_cost
from cst_item_cost_details cicd,
cst_item_costs cic,
wdj wdj,
wip_requirement_operations wro,
cst_cost_types cct,
mtl_parameters mp
where 2=2 and -- p_org_code
mp.organization_id=cic.organization_id and
cic.cost_type_id=cct.cost_type_id and
wdj.wip_entity_id=wro.wip_entity_id and
mp.organization_id=wro.organization_id and
wro.wip_supply_type<>6 and
cic.inventory_item_id=wro.inventory_item_id and
cic.cost_type_id=cicd.cost_type_id (+) and
not exists(
select null
from
mtl_cst_actual_cost_details mcacd_new,
mtl_material_transactions mmt_new,
org_acct_periods oap
where 4=4 and
oap.organization_id=mp.organization_id and
mcacd_new.transaction_id = mmt_new.transaction_id and
mmt_new.transaction_date >= oap.period_start_date and mmt_new.transaction_date < oap.schedule_close_date + 1 and
mmt_new.transaction_source_id=wdj.wip_entity_id and
mmt_new.transaction_source_type_id=5 and
mmt_new.inventory_item_id=cic.inventory_item_id and
mmt_new.organization_id = cic.organization_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
)
group by
cic.inventory_item_id,
cic.organization_id,
cic.last_update_date,
cct.cost_type_id,
cct.cost_type,
cicd.cost_element_id,
wdj.wip_entity_id,
cicd.level_type,
nvl(cicd.item_cost,0)
union all
select cic.inventory_item_id,
cic.organization_id,
cic.last_update_date,
cct.cost_type_id,
cct.cost_type,
cicd.cost_element_id,
max(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,
max(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,
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,
wdj.wip_entity_id,
nvl(cicd.item_cost,0) item_cost
from cst_item_cost_details cicd,
cst_item_costs cic,
cst_cost_types cct,
mtl_parameters mp,
wdj wdj,
wip_requirement_operations wro
where 2=2 and
mp.organization_id=cic.organization_id and
cic.cost_type_id=mp.primary_cost_method and -- this gets the Frozen Costs
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
wdj.wip_entity_id=wro.wip_entity_id and
wro.wip_supply_type<>6 and
mp.organization_id=wro.organization_id and
cic.inventory_item_id=wro.inventory_item_id and
cct.cost_type_id<>mp.primary_cost_method and -- this avoids getting the Frozen costs twice
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
-- ====================================
-- Find all the Frozen costs not in the
-- Pending or unimplemented cost type
-- ====================================
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,
cicd.level_type,
cct.cost_type,
cicd.cost_element_id,
nvl(cicd.item_cost,0),
wdj.wip_entity_id
)
----------------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 Costing_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,
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,
mtl_sum.cost_type Cost_Type,
gl.currency_code Currency_Code,
nvl(xxen_util.meaning(mtl_sum.cost_element_id ,'CST_COST_CODE_TYPE',700),'No Cost') cost_element_type,
mtl_sum.item_cost Item_Cost,
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
case when mtl_sum.last_txn_date is null then 'No WIP Issue' else to_char(mtl_sum.last_txn_date,'DD-Mon-RRRR HH24:MI:SS') end Last_Transaction_Date,
nvl(mtl_sum.last_txn_type,'No WIP Issue') Last_Transaction_Type,
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,
round(mtl_sum.wip_std_component_value,fc.precision) WIP_Standard_Component_Value,
round(mtl_sum.applied_component_value,fc.precision) 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 mtl_sum.applied_component_value = 0 then 0
-- End revision for version 1.9
else mtl_sum.applied_component_value - mtl_sum.wip_std_component_value
end Material_Usage_Variance,
-- End revision for version 1.8
-- 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.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.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,
fnd_currencies fc,
-- 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,
-- Revision for version 1.12
-- mtl.primary_cost_type,
mtl.account,
mtl.class_code,
mtl.class_type,
mtl.wip_entity_id,
mtl.project_id,
mtl.status_type,
mtl.primary_item_id,
-- Revision for version 1.22
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,
-- Revision for version 1.12 and 1.14
-- mtl.level_num,
mtl.operation_seq_num,
mtl.wip_supply_type,
-- Revision for version 1.6 and 1.22
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,
-- End revision for version 1.22
-- Revision for version 1.21
-- case
-- when sum(mtl.phantom_parent) > 0 then 'Y'
-- else 'N'
-- end phantom_parent,
-- Revision for version 1.8
mtl.basis_type,
mtl.lot_basis_type,
mtl.lot_basis_cost,
mtl.item_basis_type,
mtl.item_basis_cost,
-- End revision for version 1.8
mtl.cost_type,
mtl.item_cost,
mtl.cost_element_id,
sum(mtl.quantity_per_assembly) quantity_per_assembly,
sum(mtl.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 = mtl.inventory_item_id
and mmt.organization_id = mtl.organization_id
and mmt.transaction_source_id = mtl.wip_entity_id
and mmt.transaction_source_type_id = 5
and mmt.transaction_date < mtl.schedule_close_date + 1) last_txn_date,
(select mtt.transaction_type_name
from
(select mtt.transaction_type_name,row_number() over (order by mmt.transaction_date desc) row_num
from
mtl_material_transactions mmt,
mtl_transaction_types mtt
where mmt.inventory_item_id=mtl.inventory_item_id and
mmt.organization_id=mtl.organization_id and
mmt.transaction_source_id=mtl.wip_entity_id and
mmt.transaction_source_type_id=5 and
mtt.transaction_type_id=mmt.transaction_type_id and
mmt.transaction_date<mtl.schedule_close_date + 1
)mtt
where mtt.row_num=1) last_txn_type,
-- End revision for version 1.18
sum(mtl.quantity_issued) quantity_issued,
sum(mtl.wip_std_component_value) wip_std_component_value,
sum(mtl.applied_component_value) applied_component_value
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,
-- Revision for deriving account as per cost element
--wro.account,
decode(cic_comp.cost_element_id,
1, wdj_accounts.material_account,
2, wdj_accounts.material_overhead_account,
3, wdj_accounts.resource_account,
4, wdj_accounts.outside_processing_account,
5, wdj_accounts.overhead_account,
wdj_accounts.material_account
)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.24
-- wo.operation_seq_num,
wro.operation_seq_num,
-- Revision for version 1.14
wro.component_sequence_id,
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,
nvl(wro.basis_type, 1) basis_type,
decode(cic_comp.lot_basis_type, 0, 'N', 'Y') lot_basis_type,
-- Revision for version 1.12
nvl(cic_comp.lot_basis_cost,0) lot_basis_cost,
decode(nvl(wro.basis_type,1),
1, 'Y',
2, 'N',
decode(cic_comp.item_basis_type, 0, 'N', 'Y')
) item_basis_type,
-- 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,
cic_comp.cost_element_id,
-- 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,
-- If the job status is 'Complete' or 'Complete - No Charges' then use the
-- completions plus scrap quantities else use the planned required quantities;
-- and use the completions plus scrap quantities unless for lot-based jobs
-- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
round(decode(:p_use_completion_qtys,
'Y',
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),
2, nvl(wro.quantity_per_assembly,0), -- Lot
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
2, nvl(wro.quantity_per_assembly,0), -- Lot
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,
wro.required_quantity-decode(:p_include_scrap, 'Y', 0, nvl(wro.quantity_per_assembly,0)*nvl(wro.quantity_scrapped, 0))
)
,6) total_req_quantity,
nvl(wro.quantity_issued,0) quantity_issued,
-- If the job status is 'Complete' or 'Complete - No Charges' then use the
-- completions plus scrap quantities else use the planned required quantities;
-- and use the completions plus scrap quantities unless for lot-based jobs
-- For 'Complete', 'Complete - No Charges', 'Cancelled', 'Closed', 'Pending Close' and 'Failed Close'
round(decode(:p_use_completion_qtys,
'Y',
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),
2, nvl(wro.quantity_per_assembly,0), -- Lot
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
2, nvl(wro.quantity_per_assembly,0), -- Lot
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,
wro.required_quantity-decode(:p_include_scrap, 'Y', 0, nvl(wro.quantity_per_assembly,0)*nvl(wro.quantity_scrapped, 0))
)
,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
from wip_discrete_jobs wdj_accounts,
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,
wrosum.quantity_per_assembly,
max(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,
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
and 6=6 -- p_include_bulk_items
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,
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
-- Revision for version 1.3
and 6=6 -- p_include_bulk_items
) 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,
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 wdj_accounts.wip_entity_id=wro.wip_entity_id
and wdj_accounts.organization_id=wro.organization_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 (+)
and wro.wip_entity_id = cic_comp.wip_entity_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
) mtl
group by
mtl.report_type,
mtl.period_name,
mtl.organization_code,
mtl.organization_id,
mtl.primary_cost_method,
-- Revision for version 1.12
-- mtl.primary_cost_type,
mtl.account,
mtl.class_code,
mtl.class_type,
mtl.wip_entity_id,
mtl.project_id,
mtl.status_type,
mtl.primary_item_id,
-- Revision for version 1.22
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,
-- Revision for version 1.18
mtl.schedule_close_date,
mtl.last_update_date,
mtl.start_quantity,
mtl.quantity_completed,
mtl.quantity_scrapped,
mtl.fg_total_qty,
mtl.inventory_item_id,
-- Revision for version 1.14
mtl.department_id,
-- Revision for version 1.19
mtl.inventory_item_id,
-- Revision for version 1.12 and 1.14
-- mtl.level_num,
-- Revision for version 1.14
mtl.operation_seq_num,
mtl.wip_supply_type,
-- End revision for version 1.14
-- Revision for version 1.6 and 1.22
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,
-- End revision for version 1.22
-- Revision for version 1.8
mtl.basis_type,
mtl.lot_basis_type,
mtl.lot_basis_cost,
mtl.item_basis_type,
mtl.item_basis_cost,
-- End revision for version 1.8
mtl.cost_type,
mtl.item_cost,
mtl.cost_element_id
) 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) + 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'
and ml6.lookup_code = mtl_sum.basis_type
-- 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'
and fl2.lookup_code = cic_assys.rolled_up
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.currency_code = fc.currency_code
and 1=1 -- p_component_number, p_operating_unit, p_ledger
and mtl_sum.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)
-- order by Report_Type, Ledger, Operating_Unit, Org_Code, Period_Name, Accounts, WIP_Class, WIP_Job, Component 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,
mtl_sum.component_number,
bd.department_code |
