with j as (
select /*+ materialize*/
nvl(gl.short_name,gl.name) ledger,
haouv_ou.name operating_unit,
mp.organization_code,
gl.currency_code,
oap.period_name,
oap.period_start_date,
oap.schedule_close_date,
case when wdj.date_closed>=oap.period_start_date and wdj.date_closed<oap.schedule_close_date+1 then 'Variance' else 'Valuation' end report_type,
wdj.organization_id,
wdj.wip_entity_id,
wdj.class_code,
wac.class_type,
wdj.status_type,
wdj.project_id,
wdj.primary_item_id,
wdj.lot_number,
wdj.creation_date,
wdj.scheduled_start_date,
wdj.date_released,
wdj.date_completed,
trunc(wdj.date_closed) date_closed,
wdj.last_update_date,
wdj.start_quantity,
wdj.material_account,
wdj.quantity_completed-nvl((select sum(mmt.primary_quantity) from mtl_material_transactions mmt where wdj.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wdj.organization_id=mmt.organization_id and mmt.transaction_type_id in (17,44) and mmt.transaction_date>=oap.schedule_close_date+1),0) quantity_completed,
wdj.quantity_scrapped-nvl((select sum(mmt.primary_quantity) from mtl_material_transactions mmt where wdj.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wdj.organization_id=mmt.organization_id and mmt.transaction_type_id in (90,91) and mmt.transaction_date>=oap.schedule_close_date+1),0) quantity_scrapped,
cct.cost_type_id,
cct.cost_type,
mp.primary_cost_method
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units haou,
hr_all_organization_units_vl haouv_ou,
gl_ledgers gl,
cst_cost_types cct,
org_acct_periods oap,
wip_discrete_jobs wdj,
wip_accounting_classes wac
where
1=1 and
2=2 and
3=3 and
4=4 and
mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and
mp.organization_id=hoi.organization_id and
hoi.org_information_context='Accounting Information' and
hoi.organization_id=haou.organization_id and
to_number(hoi.org_information3)=haouv_ou.organization_id and
to_number(hoi.org_information1)=gl.ledger_id and
cct.cost_type_id=nvl((select cct2.cost_type_id from cst_cost_types cct2 where cct2.cost_type=:p_cost_type),mp.primary_cost_method) and
mp.organization_id=oap.organization_id and
mp.organization_id=wdj.organization_id and
wdj.class_code=wac.class_code and
wdj.organization_id=wac.organization_id and
wac.class_type in (1,3,5) and
(
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') or
(wdj.date_closed is null or wdj.date_closed>=oap.schedule_close_date+1) and wdj.creation_date<oap.schedule_close_date+1 and :p_report_option in ('Open jobs','All jobs')
)
),
c as (
select /*+ materialize*/
j.wip_entity_id,
wro.operation_seq_num,
wro.inventory_item_id,
wro.wip_supply_type,
nvl(wro.basis_type,1) basis_type,
decode(nvl(wro.basis_type,1),2,nvl(wro.required_quantity,1),nvl(wro.quantity_per_assembly,0)/nvl(wro.component_yield_factor,1)) quantity_per_assembly,
round(case
when j.status_type in (4,5,7,12,14,15) or :p_use_completion_qtys='Y' then decode(nvl(wro.basis_type,1),2,nvl(wro.quantity_per_assembly,0)*case when nvl(j.quantity_completed,0)+decode(:p_include_scrap,'Y',nvl(j.quantity_scrapped,0),0)>0 then 1 else 0 end,nvl(wro.quantity_per_assembly,0)/nvl(wro.component_yield_factor,1)*decode(j.class_type,5,nvl(j.quantity_completed,0),nvl(j.quantity_completed,0)+decode(:p_include_scrap,'Y',nvl(j.quantity_scrapped,0),0)))
else decode(nvl(wro.basis_type,1),2,nvl(wro.quantity_per_assembly,0),nvl(wro.quantity_per_assembly,0)*j.start_quantity/nvl(wro.component_yield_factor,1))
end,6) total_req_quantity,
nvl(wro.quantity_issued,0)+nvl((select sum(mmt.primary_quantity) from mtl_material_transactions mmt where wro.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wro.organization_id=mmt.organization_id and wro.operation_seq_num=mmt.operation_seq_num and wro.inventory_item_id=mmt.inventory_item_id and mmt.transaction_type_id in (35,43) and mmt.transaction_date>=j.schedule_close_date+1),0) quantity_issued,
(select max(mmt.transaction_date) from mtl_material_transactions mmt where wro.wip_entity_id=mmt.transaction_source_id and mmt.transaction_source_type_id=5 and wro.organization_id=mmt.organization_id and wro.inventory_item_id=mmt.inventory_item_id and mmt.transaction_date<j.schedule_close_date+1) last_transaction_date,
(select wo.department_id from wip_operations wo where wro.wip_entity_id=wo.wip_entity_id and wro.organization_id=wo.organization_id and wro.operation_seq_num=wo.operation_seq_num) department_id,
nvl((select /*+ no_unnest*/ max(cic.item_cost) keep (dense_rank first order by decode(cic.cost_type_id,j.cost_type_id,1,2)) from cst_item_costs cic where wro.inventory_item_id=cic.inventory_item_id and wro.organization_id=cic.organization_id and cic.cost_type_id in (j.cost_type_id,j.primary_cost_method)),0) item_cost,
(select /*+ no_unnest*/ sum(cicd.item_cost) from cst_item_cost_details cicd, cst_cost_types cct, bom_resources br where wro.inventory_item_id=cicd.inventory_item_id and wro.organization_id=cicd.organization_id and cicd.cost_type_id=cct.cost_type_id and cicd.resource_id=br.resource_id and 7=7) pii_item_cost
from
j,
wip_requirement_operations wro
where
6=6 and
j.wip_entity_id=wro.wip_entity_id and
j.organization_id=wro.organization_id and
wro.wip_supply_type<>6 and
wro.operation_seq_num>0
)
select
x.report_type,
x.ledger,
x.operating_unit,
x.organization_code org_code,
x.period_name,
&segment_columns
x.class_code wip_class,
xxen_util.meaning(x.class_type,'WIP_CLASS_TYPE',700) class_type,
we.wip_entity_name wip_job,
(select ppa.segment1 from pa_projects_all ppa where x.project_id=ppa.project_id) project_number,
xxen_util.meaning(x.status_type,'WIP_JOB_STATUS',700) job_status,
x.creation_date,
x.scheduled_start_date,
x.date_released,
x.date_completed,
x.date_closed,
x.last_update_date,
msiv.primary_uom_code uom_code,
msiv.std_lot_size item_std_lot_size,
nvl2(x.costing_lot_size,x.cost_type,null) lot_size_cost_type,
x.costing_lot_size,
x.start_quantity,
x.quantity_completed assembly_quantity_completed,
x.quantity_scrapped assembly_quantity_scrapped,
x.quantity_completed+x.quantity_scrapped total_assembly_quantity,
msiv.concatenated_segments assembly,
msiv.description assembly_description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type,
msiv.inventory_item_status_code item_status,
xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy_code,
&category_columns
x.lot_number,
x.operation_seq_num operation_seq_number,
bd.department_code department,
msiv_comp.concatenated_segments component_number,
msiv_comp.description component_description,
xxen_util.meaning(msiv_comp.item_type,'ITEM_TYPE',3) component_item_type,
msiv_comp.inventory_item_status_code component_status_code,
xxen_util.meaning(msiv_comp.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) component_make_buy_code,
xxen_util.meaning(x.wip_supply_type,'WIP_SUPPLY',700) wip_supply_type,
xxen_util.meaning(x.basis_type,'CST_BASIS',700) component_basis_type,
x.cost_type,
nvl2(x.pii_item_cost,:p_pii_cost_type,null) pii_cost_type,
x.currency_code,
x.item_cost gross_item_cost,
nvl(x.pii_item_cost,0) pii_item_cost,
msiv_comp.primary_uom_code uom_code,
x.quantity_per_assembly,
round(x.total_req_quantity,3) total_required_quantity,
x.last_transaction_date,
round(x.quantity_issued,3) quantity_issued,
round(x.quantity_issued-x.total_req_quantity,3) quantity_left_in_wip,
x.total_req_quantity*x.item_cost wip_standard_component_value,
round(x.quantity_issued*x.item_cost,2) applied_component_value,
case when x.quantity_completed+x.quantity_scrapped=0 and round(x.quantity_issued*x.item_cost,2)=0 then 0 else round(x.quantity_issued*x.item_cost-x.total_req_quantity*x.item_cost,2) end material_usage_variance,
round(x.quantity_issued-x.total_req_quantity,3)*decode(x.report_type,'Variance',1,decode(x.quantity_issued,0,0,1)) adjusted_quantity_in_wip,
round(round(x.quantity_issued-x.total_req_quantity,3)*decode(x.report_type,'Variance',1,decode(x.quantity_issued,0,0,1))*nvl(x.pii_item_cost,0),2) pii_in_wip,
xxen_util.yes(nvl2(x.last_rollup_date,'Y',null)) rolled_up,
x.last_rollup_date last_cost_rollup
from
(
select
j.*,
c.operation_seq_num,
c.inventory_item_id,
c.wip_supply_type,
c.basis_type,
c.quantity_per_assembly,
c.total_req_quantity,
c.quantity_issued,
c.last_transaction_date,
c.department_id,
c.item_cost,
c.pii_item_cost,
a.lot_size costing_lot_size,
a.last_rollup_date
from
j,
c,
(
select
z.organization_id,
z.inventory_item_id,
max(z.lot_size) keep (dense_rank first order by z.rnk) lot_size,
max(z.last_rollup_date) keep (dense_rank first order by z.rnk) last_rollup_date
from
(
select
cic.organization_id,
cic.inventory_item_id,
decode(cic.cost_type_id,j.cost_type_id,1,2) rnk,
nvl(cic.lot_size,1) lot_size,
decode(cic.based_on_rollup_flag,1,(select max(cicd.creation_date) from cst_item_cost_details cicd 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 cicd.rollup_source_type=3 and cicd.attribute15 is null)) last_rollup_date
from
(select distinct j.organization_id, j.primary_item_id, j.cost_type_id, j.primary_cost_method from j) j,
cst_item_costs cic
where
j.organization_id=cic.organization_id and
j.primary_item_id=cic.inventory_item_id and
cic.cost_type_id in (j.cost_type_id,j.primary_cost_method)
) z
group by
z.organization_id,
z.inventory_item_id
) a
where
j.wip_entity_id=c.wip_entity_id and
j.organization_id=a.organization_id(+) and
j.primary_item_id=a.inventory_item_id(+)
) x,
wip_entities we,
mtl_system_items_vl msiv,
mtl_system_items_vl msiv_comp,
bom_departments bd,
gl_code_combinations gcc
where
5=5 and
x.quantity_per_assembly+round(x.quantity_issued,3)<>0 and
x.wip_entity_id=we.wip_entity_id and
x.organization_id=msiv.organization_id and
x.primary_item_id=msiv.inventory_item_id and
x.organization_id=msiv_comp.organization_id and
x.inventory_item_id=msiv_comp.inventory_item_id and
x.department_id=bd.department_id(+) and
x.material_account=gcc.code_combination_id(+)
order by
x.report_type,
x.ledger,
x.operating_unit,
x.organization_code,
&segment_columns_grp
x.class_code,
we.wip_entity_name,
x.operation_seq_num,
component_number,
department |