[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OPEN l_ref_cur FOR SELECT xle.name,
cct.cost_type,
cpp.PERIOD_NAME,
ccg.cost_group,
p_class_type className,
p_from_job from_job,
p_to_job to_job,
p_from_assembly from_aasembly,
p_to_assembly to_aasembly,
p_exchange_rate_char exchange_rate_char,
p_currency_code currency_code,
m1.meaning rep_type
FROM xle_entity_profiles xle,
cst_pac_periods cpp,
cst_cost_types cct,
cst_le_cost_types clct,
cst_cost_groups ccg,
mfg_lookups m1
WHERE xle.legal_entity_id = p_legal_entity_id
AND clct.legal_entity = xle.legal_entity_id
AND clct.cost_type_id = cct.cost_type_id
AND cct.cost_type_id = p_cost_type_id
AND cpp.legal_entity = clct.legal_entity
AND cpp.cost_type_id = cct.cost_type_id
AND cpp.pac_period_id = p_pac_period_id
AND ccg.cost_group_id = p_cost_group_id
AND ccg.legal_entity = clct.legal_entity
AND m1.lookup_type = 'WIP_REP_VAL_TYPE'
AND m1.lookup_code = p_report_type;
SELECT wip_entity_id,
organization_id,
line_id,
/* All In */
sum( nvl(pl_material_in, 0)) material_in,
sum( nvl(pl_material_overhead_in, 0)) material_ovhd_in ,
sum( nvl(tl_resource_in ,0) + nvl( pl_resource_in,0)) Resource_in,
sum( nvl(tl_overhead_in ,0) + nvl( pl_overhead_in, 0)) overhead_in,
sum( nvl(tl_outside_processing_in ,0) + nvl( pl_outside_processing_in,0)) osp_in,
/* All out */
sum( nvl(pl_material_out, 0) ) material_out,
sum( nvl(pl_material_overhead_out , 0)) material_ovhd_out,
sum( nvl(tl_resource_out ,0) + nvl( pl_resource_out, 0)) resource_out,
sum( nvl(tl_outside_processing_out ,0) + nvl( pl_outside_processing_out,0)) osp_out,
sum( nvl(tl_overhead_out ,0) + nvl( pl_overhead_out, 0)) overhead_out,
/* All var */
sum( nvl(pl_material_var, 0) ) material_var,
sum( nvl(pl_material_overhead_var , 0)) material_ovhd_var,
sum( nvl(tl_resource_var ,0) + nvl(pl_resource_var, 0)) resource_var,
sum( nvl(tl_outside_processing_var ,0) + nvl(pl_outside_processing_var,0)) osp_var,
sum( nvl(tl_overhead_var,0) + nvl(pl_overhead_var, 0)) overhead_var
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = p_pac_period_id
AND wppb.cost_type_id = p_cost_type_id
AND wppb.cost_group_id = p_cost_group_id
GROUP BY wppb.wip_entity_id,
wppb.organization_id,
wppb.line_id;
SELECT nvl(max(cpp.pac_period_id), -1)
INTO l_prev_period_id
FROM cst_pac_process_phases cppp,
cst_pac_periods cpp
WHERE cppp.pac_period_id = cpp.pac_period_id
AND cppp.cost_group_id = p_cost_group_id
AND cpp.cost_type_id = p_cost_type_id
AND cpp.legal_entity = p_legal_entity_id
AND cpp.pac_period_id < p_pac_period_id;
SELECT
SUM( nvl(pl_material_in, 0) - nvl(pl_material_out, 0) - nvl(pl_material_var, 0) ),
SUM( nvl(pl_material_overhead_in, 0) - nvl(pl_material_overhead_out,0) - nvl(pl_material_overhead_var,0)),
SUM( nvl(tl_resource_in ,0) + nvl( pl_resource_in,0)
- nvl(tl_resource_out ,0) - nvl( pl_resource_out, 0)
- nvl(tl_resource_var ,0) - nvl( pl_resource_var, 0) ),
SUM( nvl(tl_overhead_in ,0) + nvl( pl_overhead_in, 0)
- nvl(tl_overhead_out ,0) - nvl( pl_overhead_out, 0)
- nvl(tl_overhead_var,0) - nvl( pl_overhead_var, 0) ) ,
SUM( nvl(tl_outside_processing_in ,0) + nvl( pl_outside_processing_in,0)
- nvl(tl_outside_processing_out ,0) - nvl( pl_outside_processing_out,0)
- nvl(tl_outside_processing_var ,0) - nvl( pl_outside_processing_var,0) ),
/* Total incurred value till previous period */
SUM ( nvl(pl_material_in, 0) ) ,
SUM( nvl(pl_material_overhead_in, 0)) ,
SUM( nvl(tl_resource_in ,0) + nvl( pl_resource_in,0) ) ,
SUM( nvl(tl_overhead_in ,0) + nvl( pl_overhead_in, 0) ) ,
SUM( nvl(tl_outside_processing_in ,0) + nvl(pl_outside_processing_in,0)),
/* Total relieved value till prior period */
SUM( nvl(pl_material_out, 0) ) ,
SUM( nvl(pl_material_overhead_out, 0)) ,
SUM( nvl(tl_resource_out ,0) + nvl( pl_resource_out,0) ) ,
SUM( nvl(tl_overhead_out ,0) + nvl( pl_overhead_out, 0) ) ,
SUM( nvl(tl_outside_processing_out ,0) + nvl(pl_outside_processing_out,0) ),
/* Total variance till prior period */
sum( nvl(pl_material_var, 0) ) material_var,
sum( nvl(pl_material_overhead_var , 0)) material_ovhd_var,
sum( nvl(tl_resource_var ,0) + nvl(pl_resource_var, 0)) resource_var,
sum( nvl(tl_outside_processing_var ,0) + nvl(pl_outside_processing_var,0)) osp_var,
sum( nvl(tl_overhead_var,0) + nvl(pl_overhead_var, 0)) overhead_var
INTO l_begining_material,
l_begining_material_ovhd,
l_begining_resource,
l_begining_overhead,
l_begining_osp,
l_prev_material_in,
l_prev_material_ovhd_in,
l_prev_resource_in,
l_prev_overhead_in,
l_prev_osp_in,
l_prev_material_out,
l_prev_material_ovhd_out,
l_prev_resource_out,
l_prev_overhead_out,
l_prev_osp_out,
l_prev_material_var,
l_prev_material_ovhd_var,
l_prev_resource_var,
l_prev_osp_var,
l_prev_overhead_var
FROM wip_pac_period_balances wppb
WHERE wppb.pac_period_id = l_prev_period_id
AND wppb.wip_entity_id = temp_rec.wip_entity_id
AND nvl(wppb.line_id,-99) = nvl(temp_rec.line_id,-99)
AND wppb.cost_type_id = p_cost_type_id;
INSERT INTO cst_wip_pac_period_bal_tmp (cost_group_id,
pac_period_id,
cost_type_id,
wip_entity_id,
organization_id,
line_id,
cost_element_id,
begining_balance,
costs_incurred,
costs_relieved,
ending_balance,
variance_amount)
VALUES (p_cost_group_id,
p_pac_period_id,
p_cost_type_id,
temp_rec.wip_entity_id,
temp_rec.organization_id,
temp_rec.line_id,
i, -- Cost Element
DECODE(i,1,DECODE(p_report_type,1,nvl(l_begining_material,0),0),
2,DECODE(p_report_type,1,nvl(l_begining_material_ovhd,0),0),
3,DECODE(p_report_type,1,nvl(l_begining_Resource,0),0),
4,DECODE(p_report_type,1,nvl(l_begining_osp,0),0),
5,DECODE(p_report_type,1,nvl(l_begining_overhead,0),0)
),
DECODE(i,1,temp_rec.material_in - DECODE(p_report_type,1,nvl(l_prev_material_in,0),0),
2,temp_rec.material_ovhd_in - DECODE(p_report_type,1,nvl(l_prev_material_ovhd_in,0),0),
3,temp_rec.Resource_in - DECODE(p_report_type,1,nvl(l_prev_Resource_in,0),0),
4,temp_rec.osp_in - DECODE(p_report_type,1,nvl(l_prev_osp_in,0),0),
5,temp_rec.overhead_in - DECODE(p_report_type,1,nvl(l_prev_overhead_in,0),0)
),
DECODE(i,1,temp_rec.material_out - DECODE(p_report_type,1,nvl(l_prev_material_out,0),0),
2,temp_rec.material_ovhd_out - DECODE(p_report_type,1,nvl(l_prev_material_ovhd_out,0),0),
3,temp_rec.Resource_out - DECODE(p_report_type,1,nvl(l_prev_Resource_out,0),0) ,
4,temp_rec.osp_out - DECODE(p_report_type,1,nvl(l_prev_osp_out,0),0),
5,temp_rec.overhead_out - DECODE(p_report_type,1,nvl(l_prev_overhead_out,0),0)
),
DECODE(i,1,(temp_rec.material_in - temp_rec.material_out - temp_rec.material_var ),
2,(temp_rec.material_ovhd_in - temp_rec.material_ovhd_out - temp_rec.material_ovhd_var),
3,(temp_rec.Resource_in - temp_rec.Resource_out - temp_rec.Resource_var),
4,(temp_rec.osp_in - temp_rec.osp_out - temp_rec.osp_var),
5,(temp_rec.overhead_in - temp_rec.overhead_out - temp_rec.overhead_var)
),
DECODE(i,1,temp_rec.material_var - DECODE(p_report_type,1,nvl(l_prev_material_var,0),0),
2,temp_rec.material_ovhd_var - DECODE(p_report_type,1,nvl(l_prev_material_ovhd_var,0),0),
3,temp_rec.Resource_var - DECODE(p_report_type,1,nvl(l_prev_Resource_var,0),0) ,
4,temp_rec.osp_var - DECODE(p_report_type,1,nvl(l_prev_osp_var,0),0) ,
5,temp_rec.overhead_var - DECODE(p_report_type,1,nvl(l_prev_overhead_var,0),0)
)
);
'Inserted '||l_ctr|| ' rows into temp table.' ||
'Exiting CST_PAC_WIP_Value_Report_PVT.Periodic_WIP_Value_Rpt_Details >> ');
SELECT fc.precision
INTO l_precision
FROM fnd_currencies fc
WHERE fc.currency_code = p_currency_code;
SELECT cpp.period_end_date
INTO l_period_end_date
FROM cst_pac_periods cpp
WHERE cpp.pac_period_id = P_PAC_PERIOD_ID;
select
min(ml.meaning) Class_type,
we.wip_entity_name,
mp.organization_code,
msik.concatenated_segments Assembly,
min(ml2.meaning) job_status,
wdj.scheduled_start_date scheduled_start_date,
cce.cost_element,
sum(round( wppb.BEGINING_BALANCE * l_exchange_rate, l_precision )) BeginingBalance,
sum(round( wppb.COSTS_INCURRED * l_exchange_rate, l_precision )) CostsIncurred,
sum(round( wppb.costs_relieved * l_exchange_rate, l_precision )) CostsRelieved,
sum(round( wppb.ENDING_BALANCE * l_exchange_rate, l_precision )) EndingBalance,
sum(round( wppb.VARIANCE_AMOUNT * l_exchange_rate, l_precision )) VarianceAmount
from
wip_entities we,
mfg_lookups ml,
cst_wip_pac_period_bal_tmp wppb,
wip_discrete_jobs wdj,
wip_accounting_classes wac,
mfg_lookups ml2,
mtl_parameters mp,
cst_cost_elements cce,
mtl_system_items_kfv msik
where
wppb.cost_group_id = P_COST_GROUP_ID
and wppb.pac_period_id = P_PAC_PERIOD_ID
and wdj.wip_entity_id = wppb.wip_entity_id
and wdj.organization_id = wppb.organization_id
and we.wip_entity_id = wdj.wip_entity_id
and wac.class_code = wdj.class_code
and wac.organization_id = wdj.organization_id
and ml.lookup_type = 'WIP_CLASS_TYPE_CAP'
and ml.lookup_code = wac.class_type
and msik.organization_id = wdj.organization_id
and msik.inventory_item_id = wdj.primary_item_id
and ml2.lookup_type = 'WIP_JOB_STATUS'
and ml2.lookup_code = wdj.status_type
and mp.organization_id = wppb.organization_id
and cce.cost_element_id = WPPB.cost_element_id
and ( p_class_type is null or wac.class_type = p_class_type )
and ( p_from_job is null or WE.WIP_ENTITY_NAME >= P_FROM_JOB )
and ( p_to_job is null or WE.WIP_ENTITY_NAME <= P_TO_JOB )
and ( p_from_assembly is null or msik.concatenated_segments >= p_from_assembly )
and ( p_to_assembly is null or msik.concatenated_segments <= p_to_assembly )
GROUP BY
wac.class_type,
wppb.wip_entity_id,
we.wip_entity_name,
mp.organization_code,
wdj.scheduled_start_date,
wdj.primary_item_id,
msik.concatenated_segments,
wdj.status_type,
wppb.pac_period_id,
wppb.cost_element_id,
cce.cost_element
UNION ALL
select
min(ml.meaning) Class_type,
wl.line_code wip_entity_name,
mp.organization_code,
msik.concatenated_segments Assembly,
decode( sign( l_period_end_date - NVL(min(NVL(wl.disable_date,l_period_end_date + 1)), l_period_end_date + 1 )),
1, 'Line Disabled', 'Line Open') job_status ,
to_date(NULL) scheduled_start_date,
cce.cost_element ,
sum(round( wppb.BEGINING_BALANCE * l_exchange_rate, l_precision )) BeginingBalance,
sum(round( wppb.COSTS_INCURRED * l_exchange_rate, l_precision )) CostsIncurred,
sum(round( wppb.costs_relieved * l_exchange_rate, l_precision )) CostsRelieved,
sum(round( wppb.ENDING_BALANCE * l_exchange_rate, l_precision )) EndingBalance,
sum(round( wppb.VARIANCE_AMOUNT * l_exchange_rate, l_precision )) VarianceAmount
FROM
wip_lines wl ,
mfg_lookups ml ,
cst_wip_pac_period_bal_tmp wppb ,
wip_repetitive_items wri ,
mtl_system_items_kfv msik ,
wip_accounting_classes wac ,
wip_entities we ,
mtl_parameters mp ,
cst_cost_elements cce
WHERE
wppb.cost_group_id = P_COST_GROUP_ID
and wppb.pac_period_id = P_PAC_PERIOD_ID
and wl.line_id = wppb.line_id
and wri.wip_entity_id = wppb.wip_entity_id
and wri.line_id = wppb.line_id
and we.wip_entity_id = wppb.wip_entity_id
and wac.class_code = wri.class_code
and wac.organization_id = wppb.organization_id
and ml.lookup_type = 'WIP_CLASS_TYPE_CAP'
and ml.lookup_code = wac.class_type
and msik.organization_id = wppb.organization_id
and msik.inventory_item_id = wri.primary_item_id
and mp.organization_id = wppb.organization_id
and cce.cost_element_id = WPPB.cost_element_id
and ( p_class_type is null or wac.class_type = p_class_type )
and ( p_from_job is null or WE.WIP_ENTITY_NAME >= P_FROM_JOB )
and ( p_to_job is null or WE.WIP_ENTITY_NAME <= P_TO_JOB )
and ( p_from_assembly is null or msik.concatenated_segments >= p_from_assembly )
and ( p_to_assembly is null or msik.concatenated_segments <= p_to_assembly )
GROUP BY
wac.class_type,
WPPB.wip_entity_id,
wl.line_code,
mp.organization_code,
wri.primary_item_id,
msik.concatenated_segments,
wppb.pac_period_id,
wppb.cost_element_id,
cce.cost_element
ORDER BY
1,2,3,4,5,6,7;
open l_ref_cur FOR select l_total_rows_processed row_count from DUAL;