with wip_detail_q as
( select
wdq.job_type,
wdq.class_type_code,
wdq.class_type,
wdq.class_code,
wdq.wip_entity,
wdq.period,
wdq.period_close_date,
wdq.scheduled_start_date,
wdq.assembly,
wdq.element,
wdq.gl_account,
-- PTD
case when wdq.acct_period_id = :p_acct_period_id then round(:p_exchange_rate * wdq.costs_incurred,:p_precision) end ptd_costs_incurred,
case when wdq.acct_period_id = :p_acct_period_id then round(:p_exchange_rate * wdq.costs_relieved,:p_precision) end ptd_costs_relieved,
case when wdq.acct_period_id = :p_acct_period_id then round(:p_exchange_rate * wdq.elemental_variances_relieved,:p_precision) end ptd_elemental_var_relieved,
case when wdq.acct_period_id = :p_acct_period_id then round(:p_exchange_rate * wdq.single_variances_relieved,:p_precision) end ptd_single_var_relieved,
case when wdq.acct_period_id = :p_acct_period_id then
round(:p_exchange_rate * nvl(wdq.costs_incurred,0),:p_precision) -
round(:p_exchange_rate * nvl(wdq.costs_relieved,0),:p_precision) -
round(:p_exchange_rate * nvl(wdq.elemental_variances_relieved,0),:p_precision)
end ptd_net_activity,
-- CTD
round(:p_exchange_rate * wdq.costs_incurred,:p_precision) ctd_costs_incurred,
round(:p_exchange_rate * wdq.costs_relieved,:p_precision) ctd_costs_relieved,
round(:p_exchange_rate * wdq.elemental_variances_relieved,:p_precision) ctd_elemental_var_relieved,
round(:p_exchange_rate * wdq.single_variances_relieved,:p_precision) ctd_single_var_relieved,
round(:p_exchange_rate * nvl(wdq.costs_incurred,0),:p_precision) -
round(:p_exchange_rate * nvl(wdq.costs_relieved,0),:p_precision) -
round(:p_exchange_rate * nvl(wdq.elemental_variances_relieved,0),:p_precision) ctd_net_activity,
--
wdq.organization_id,
wdq.wip_entity_id,
wdq.repetitive_schedule_id,
wdq.acct_period_id,
wdq.element_code
from
(
select /*+ LEADING(WDJ WPB OAP WE) USE_NL(WDJ WPB OAP WE ML2) */ -- Discrete Jobs
'Discrete' job_type,
wpb.class_type class_type_code,
ml.meaning class_type,
wdj.class_code class_code,
we.wip_entity_name wip_entity,
oap.period_name period,
oap.schedule_close_date period_close_date,
wdj.scheduled_start_date scheduled_start_date,
wdj.primary_item_id item_id,
msik.concatenated_segments assembly,
--
ml2.meaning element,
nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'VALUE'),null)
gl_account,
--
decode( ml2.lookup_code,
1 , nvl(wpb.pl_material_in,0),
2 , nvl(wpb.pl_material_overhead_in,0),
3 , nvl(wpb.tl_resource_in,0) + nvl(wpb.pl_resource_in,0),
4 , nvl(wpb.tl_outside_processing_in,0) + nvl(wpb.pl_outside_processing_in,0),
5 , nvl(wpb.tl_overhead_in,0) + nvl(wpb.pl_overhead_in,0),
12 , nvl(wpb.tl_scrap_in,0),
0) costs_incurred,
decode(ml2.lookup_code,
1 , nvl(wpb.tl_material_out,0) + nvl(wpb.pl_material_out,0),
2 , nvl(wpb.pl_material_overhead_out,0)+nvl(wpb.tl_material_overhead_out,0),
3 , nvl(wpb.tl_resource_out,0) + nvl(wpb.pl_resource_out,0),
4 , nvl(wpb.tl_outside_processing_out,0) + nvl(wpb.pl_outside_processing_out, 0),
5 , nvl(wpb.tl_overhead_out,0) + nvl(wpb.pl_overhead_out,0),
12 , nvl(wpb.tl_scrap_out,0),
0) costs_relieved,
decode(ml2.lookup_code,
1 , nvl(wpb.tl_material_var,0) + nvl(wpb.pl_material_var,0),
2 , nvl(wpb.pl_material_overhead_var,0) + nvl(wpb.tl_material_overhead_var,0),
3 , nvl(wpb.tl_resource_var,0) + nvl(wpb.pl_resource_var,0),
4 , nvl(wpb.tl_outside_processing_var,0) + nvl(wpb.pl_outside_processing_var, 0),
5 , nvl(wpb.tl_overhead_var,0) + nvl(wpb.pl_overhead_var,0),
12 , nvl(wpb.tl_scrap_var,0),
0) elemental_variances_relieved,
decode(ml2.lookup_code,
6 , nvl(wpb.tl_material_var,0) +
nvl(wpb.pl_material_var,0) +
nvl(wpb.pl_material_overhead_var,0) +
nvl(wpb.pl_resource_var,0) +
nvl(wpb.pl_overhead_var,0) +
nvl(wpb.pl_outside_processing_var,0) +
nvl(wpb.tl_material_overhead_var,0),
7 , nvl(wpb.tl_resource_var,0),
8 , nvl(wpb.tl_outside_processing_var,0),
9 , nvl(wpb.tl_overhead_var,0),
13 , nvl(wpb.tl_scrap_var,0),
0) single_variances_relieved,
--
wpb.organization_id,
wpb.wip_entity_id,
wpb.repetitive_schedule_id,
wpb.acct_period_id,
ml2.lookup_code element_code
from
wip_discrete_jobs wdj,
wip_period_balances wpb,
org_acct_periods oap,
mfg_lookups ml,
wip_entities we,
mtl_system_items_b_kfv msik,
mfg_lookups ml2,
gl_code_combinations gcc
where
wpb.wip_entity_id = wdj.wip_entity_id and
wpb.organization_id = wdj.organization_id and
oap.organization_id = wpb.organization_id and
oap.acct_period_id = wpb.acct_period_id and
ml.lookup_type = 'WIP_CLASS_TYPE' and
ml.lookup_code = wpb.class_type and
we.wip_entity_id = wdj.wip_entity_id and
we.organization_id = wdj.organization_id and
msik.inventory_item_id (+) = wdj.primary_item_id and
msik.organization_id (+) = wdj.organization_id and
ml2.lookup_type = 'WIP_ELEMENT_VAR_TYPE' and
ml2.lookup_code in (1,2,3,4,5,6,7,8,9,12,13) and
gcc.code_combination_id = decode(ml2.lookup_code,
1 , wdj.material_account,
2 , wdj.material_overhead_account,
3 , wdj.resource_account,
4 , wdj.outside_processing_account,
5 , wdj.overhead_account,
6 , wdj.material_variance_account,
7 , wdj.resource_variance_account,
8 , wdj.outside_proc_variance_account,
9 , wdj.overhead_variance_account,
12, wdj.est_scrap_account,
13, wdj.est_scrap_var_account,
0) and
wdj.organization_id = :p_organization_id and
wdj.date_released is not null and
trunc(wdj.date_released) <= :p_period_close_date and
wpb.organization_id = :p_organization_id and
wpb.acct_period_id <= :p_acct_period_id and
2=2
union
select /*+ LEADING(WRS) */ -- Repetitive Schedules
'Repetitive' job_type,
wpb.class_type class_type_code,
ml.meaning class_type,
wri.class_code class_code,
wl.line_code wip_entity,
oap.period_name period,
oap.schedule_close_date period_close_date,
wrs.first_unit_start_date scheduled_start_date,
wri.primary_item_id item_id,
msik.concatenated_segments assembly,
--
ml2.meaning element,
nvl2(gcc.code_combination_id,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', gcc.chart_of_accounts_id, NULL, gcc.code_combination_id, 'ALL', 'Y', 'VALUE'),null)
gl_account,
--
decode( ml2.lookup_code,
1 , nvl(wpb.pl_material_in,0),
2 , nvl(wpb.pl_material_overhead_in,0),
3 , nvl(wpb.tl_resource_in,0) + nvl(wpb.pl_resource_in,0),
4 , nvl(wpb.tl_outside_processing_in,0) + nvl(wpb.pl_outside_processing_in,0),
5 , nvl(wpb.tl_overhead_in,0) + nvl(wpb.pl_overhead_in,0),
0) costs_incurred,
decode(ml2.lookup_code,
1 , nvl(wpb.tl_material_out,0) + nvl(wpb.pl_material_out,0),
2 , nvl(wpb.pl_material_overhead_out,0) + nvl(wpb.tl_material_overhead_out,0),
3 , nvl(wpb.tl_resource_out,0) + nvl(wpb.pl_resource_out,0),
4 , nvl(wpb.tl_outside_processing_out,0) + nvl(wpb.pl_outside_processing_out, 0),
5 , nvl(wpb.tl_overhead_out,0) + nvl(wpb.pl_overhead_out,0),
0) costs_relieved,
decode(ml2.lookup_code,
1 , nvl(wpb.tl_material_var,0) + nvl(wpb.pl_material_var,0),
2 , nvl(wpb.pl_material_overhead_var,0) + nvl(wpb.tl_material_overhead_var,0),
3 , nvl(wpb.tl_resource_var,0) + nvl(wpb.pl_resource_var,0),
4 , nvl(wpb.tl_outside_processing_var,0) + nvl(wpb.pl_outside_processing_var, 0),
5 , nvl(wpb.tl_overhead_var,0) + nvl(wpb.pl_overhead_var,0),
0) elemental_variances_relieved,
decode(ml2.lookup_code,
6 , nvl(wpb.tl_material_var,0) +
nvl(wpb.pl_material_var,0) +
nvl(wpb.pl_material_overhead_var,0) +
nvl(wpb.pl_resource_var,0) +
nvl(wpb.pl_overhead_var,0) +
nvl(wpb.pl_outside_processing_var,0) +
nvl(wpb.tl_material_overhead_var,0),
7 , nvl(wpb.tl_resource_var,0),
8 , nvl(wpb.tl_outside_processing_var,0),
9 , nvl(wpb.tl_overhead_var,0),
0) single_variances_relieved,
--
wpb.organization_id,
wpb.wip_entity_id,
wpb.repetitive_schedule_id,
wpb.acct_period_id,
ml2.lookup_code element_code
from
wip_repetitive_schedules wrs,
wip_period_balances wpb,
org_acct_periods oap,
mfg_lookups ml,
wip_entities we,
wip_lines wl,
wip_repetitive_items wri,
mtl_system_items_b_kfv msik,
mfg_lookups ml2,
gl_code_combinations gcc
where
wpb.wip_entity_id = wrs.wip_entity_id and
wpb.organization_id = wrs.organization_id and
wpb.repetitive_schedule_id = wrs.repetitive_schedule_id and
oap.organization_id = wpb.organization_id and
oap.acct_period_id = wpb.acct_period_id and
ml.lookup_type = 'WIP_CLASS_TYPE' and
ml.lookup_code = wpb.class_type and
we.wip_entity_id = wrs.wip_entity_id and
we.organization_id = wrs.organization_id and
wl.line_id = wrs.line_id and
wl.organization_id = wrs.organization_id and
wri.wip_entity_id = wrs.wip_entity_id and
wri.line_id = wrs.line_id and
wri.organization_id = wrs.organization_id and
msik.inventory_item_id = wri.primary_item_id and
msik.organization_id = wri.organization_id and
ml2.lookup_type = 'WIP_ELEMENT_VAR_TYPE' and
ml2.lookup_code in (1,2,3,4,5,6,7,8,9) and
gcc.code_combination_id = decode(ml2.lookup_code,
1 , wrs.material_account,
2 , wrs.material_overhead_account,
3 , wrs.resource_account,
4 , wrs.outside_processing_account,
5 , wrs.overhead_account,
6 , wrs.material_variance_account,
7 , wrs.resource_variance_account,
8 , wrs.outside_proc_variance_account,
9 , wrs.overhead_variance_account,
0) and
wrs.organization_id = :p_organization_id and
wrs.date_released is not null and
trunc(wrs.date_released) <= :p_period_close_date and
( wrs.date_closed is null or
wrs.date_closed >= :p_period_start_date
) and
wpb.organization_id = :p_organization_id and
wpb.acct_period_id <= :p_acct_period_id and
3=3
) wdq
)
--
-- Main Query Starts Here
--
select
:p_organization_code organization,
wdq.job_type,
wdq.class_type,
wdq.class_code class,
wdq.wip_entity "Job/Line",
wdq.period,
wdq.period_close_date,
wdq.scheduled_start_date start_date,
wdq.assembly,
wdq.element "Element/Variance",
wdq.gl_account,
:p_currency_code currency,
:p_exchange_rate exchange_rate,
-- PTD
wdq.ptd_costs_incurred,
wdq.ptd_costs_relieved,
wdq.ptd_elemental_var_relieved ptd_variances_relieved,
wdq.ptd_single_var_relieved ptd_single_level_var_relieved,
wdq.ptd_net_activity,
-- CTD
wdq.ctd_costs_incurred,
wdq.ctd_costs_relieved,
wdq.ctd_elemental_var_relieved ctd_variances_relieved,
wdq.ctd_single_var_relieved ctd_single_level_var_relieved,
wdq.ctd_net_activity,
--
wdq.class_type_code,
wdq.class_type_code || ' ' || wdq.class_type class_type_pivot_label,
wdq.element_code,
lpad(wdq.element_code,2) || ' ' || wdq.element || ' - ' || wdq.gl_account "Element/Variance Pivot Label"
from
wip_detail_q wdq
where
1=1
order by
wdq.job_type,
wdq.class_type_code,
wdq.class_code,
wdq.wip_entity,
wdq.period_close_date,
wdq.scheduled_start_date |
