[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into msc_resources_f(
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
department_id,
owning_department_id,
resource_id,
inventory_item_id,
analysis_date,
aggr_type, category_set_id, sr_category_id, resource_group,
created_by,
creation_date,
last_update_date,
last_updated_by,
last_update_login,
program_id,
program_login_id,
program_application_id,
request_id,
required_hours,
available_hours,
setup_time_hrs,
order_quantity,
resource_hours,
no_of_orders,
resource_cost,
resource_cost2
)
--values
select
plan_id ,
plan_run_id ,
sr_instance_id ,
organization_id ,
department_id ,
nvl(owning_department_id,-23453),
resource_id ,
inventory_item_id ,
analysis_date ,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
'-23453' resource_group,
fnd_global.user_id created_by,
sysdate creation_date,
sysdate last_update_date,
fnd_global.user_id last_updated_by,
fnd_global.login_id last_update_login,
fnd_global.conc_program_id program_id,
fnd_global.conc_login_id program_login_id,
fnd_global.prog_appl_id program_application_id,
fnd_global.conc_request_id request_id,
sum(required_hours),
sum(available_hours) ,
sum(setup_time_hrs) ,
sum(order_quantity) ,
sum(resource_hours),
sum(no_of_orders),
sum(resource_cost) resource_cost,
sum(resource_cost2) resource_cost2
from
(
select
mrr.plan_id plan_id,
p_plan_run_id plan_run_id,
mrr.sr_instance_id sr_instance_id,
mrr.organization_id organization_id,
mrr.department_id department_id,
nvl(mdr.owning_department_id,mrr.department_id) owning_department_id,
mrr.resource_id resource_id,
-23453 inventory_item_id,
null supply_id,
trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date))) analysis_date,
sum(decode(nvl(mrr.schedule_flag,2),1,(mrr.resource_hours),0) ) required_hours,
to_number(null) available_hours,
sum(decode(nvl(mrr.schedule_flag,2),1,0,(mrr.resource_hours))) setup_time_hrs,
to_number(null) order_quantity,
to_number(null) resource_hours,
to_number(null) no_of_orders,
to_number(null) resource_cost,
to_number(null) resource_cost2
from msc_resource_requirements mrr,
msc_department_resources mdr,
msc_plans mp
where mp.plan_id = p_plan_id
and mrr.plan_id = mp.plan_id
and mdr.plan_id = mrr.plan_id
and mdr.sr_instance_id = mrr.sr_instance_id
and mdr.organization_id = mrr.organization_id
and mdr.department_id = mrr.department_id
and mdr.resource_id = mrr.resource_id
and mrr.resource_id > 0
and ((l_constrained_plan=2 and mrr.parent_id = 2) or (l_constrained_plan=1 and mrr.parent_id = 1))
and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date))) between mp.curr_start_date and mp.curr_cutoff_date
group by
mrr.plan_id,
p_plan_run_id,
mrr.sr_instance_id,
mrr.organization_id,
mrr.department_id,
nvl(mdr.owning_department_id,mrr.department_id),
mrr.resource_id,
-23453,
null,
trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
union all
select
mra.plan_id plan_id,
p_plan_run_id plan_run_id,
mra.sr_instance_id sr_instance_id,
mra.organization_id organization_id,
mra.department_id department_id,
nvl(mdr.owning_department_id,mra.department_id) owning_department_id,
mra.resource_id resource_id,
-23453 inventory_item_id,
null supply_id,
trunc(mra.shift_date) analysis_date,
to_number(null) required_hours,
sum((mra.capacity_units * decode(mra.from_time,null,1,(( decode(sign(mra.to_time-mra.from_time),
-1, mra.to_time+86400, mra.to_time) - mra.from_time)/3600))) ) available_hours,
to_number(null) setup_time_hrs,
to_number(null) order_quantity,
to_number(null) resource_hours,
to_number(null) no_of_orders,
to_number(null) resource_cost,
to_number(null) resource_cost2
from msc_net_resource_avail mra,
msc_department_resources mdr,
msc_plans mp
where mp.plan_id = p_plan_id
and mra.plan_id = mp.plan_id
and mdr.plan_id = mra.plan_id
and mdr.sr_instance_id = mra.sr_instance_id
and mdr.organization_id = mra.organization_id
and mdr.department_id = mra.department_id
and mdr.resource_id = mra.resource_id
and mra.parent_id <> -1
and mra.resource_id > 0
and trunc(mra.shift_date) between mp.curr_start_date and mp.curr_cutoff_date
group by
mra.plan_id,
p_plan_run_id,
mra.sr_instance_id,
mra.organization_id,
mra.department_id,
nvl(mdr.owning_department_id,mra.department_id),
mra.resource_id,
-23453,
null,
trunc(mra.shift_date)
union all
select
mrr.plan_id plan_id,
p_plan_run_id plan_run_id,
mrr.sr_instance_id sr_instance_id,
mrr.organization_id organization_id,
mrr.department_id department_id,
nvl(mdr.owning_department_id,mrr.department_id) owning_department_id,
mrr.resource_id resource_id,
ms.inventory_item_id inventory_item_id,
mrr.supply_id supply_id,
trunc(max(nvl(mrr.end_date,mrr.start_date))) analysis_date,
to_number(null) required_hours,
to_number(null) available_hours,
to_number(null) setup_time_hrs,
nvl(mrr.cummulative_quantity, ms.NEW_ORDER_QUANTITY) order_quantity,
sum(mrr.resource_hours) resource_hours,
1 no_of_orders,
to_number(null) resource_cost,
to_number(null) resource_cost2
from
msc_resource_requirements mrr,
msc_supplies ms ,
msc_department_resources mdr,
msc_plans mp
where mp.plan_id = p_plan_id
and mrr.plan_id = mp.plan_id
and mrr.parent_id = 2
and nvl(mrr.schedule_flag,2) = 1
and mdr.plan_id = mrr.plan_id
and mdr.sr_instance_id = mrr.sr_instance_id
and mdr.organization_id = mrr.organization_id
and mdr.department_id = mrr.department_id
and mdr.resource_id = mrr.resource_id
and mrr.plan_id = ms.plan_id
and mrr.sr_instance_id = ms.sr_instance_id
and mrr.organization_id = ms.organization_id
and mrr.supply_id = ms.transaction_id
and mrr.resource_id > 0
and trunc(nvl(mrr.end_date,mrr.start_date)) between mp.curr_start_date and mp.curr_cutoff_date
group by
mrr.plan_id ,
p_plan_run_id,
mrr.sr_instance_id ,
mrr.organization_id ,
mrr.department_id ,
nvl(mdr.owning_department_id,mrr.department_id),
mrr.resource_id ,
ms.inventory_item_id,
mrr.supply_id,
nvl(mrr.cummulative_quantity, ms.NEW_ORDER_QUANTITY),
1
union all
select
t1.plan_id,
p_plan_run_id plan_run_id,
t1.sr_instance_id,
t1.organization_id,
t1.department_id,
t1.owning_department_id,
t1.resource_id,
-23453 inventory_item_id,
null supply_id,
t1.resource_date analysis_date,
t1.required_hours,
t1.available_hours,
t1.setup_hours setup_time_hrs,
to_number(null) order_quantity,
to_number(null) resource_hours,
to_number(null) no_of_orders,
t1.resource_cost,
t1.resource_cost * decode(t1.currency_code, fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) resource_cost2
from
/* For SNO plan don't filter detail_level.
*/
(select
mdrs.plan_id,
mdrs.sr_instance_id,
mdrs.organization_id,
mtp.currency_code,
mdrs.department_id,
mdr.owning_department_id,
mdrs.resource_id,
trunc(mdrs.resource_date) resource_date,
mdrs.required_hours,
mdrs.available_hours,
mdrs.setup_hours,
mdrs.resource_cost
from
msc_bis_res_summary mdrs,
msc_department_resources mdr,
msc_trading_partners mtp,
msc_plans mp
where mdrs.plan_id = p_plan_id
and mp.plan_id = mdrs.plan_id
and mp.plan_type = 6
and nvl(mdrs.period_type, 0) = 1
and mdrs.sr_instance_id = mtp.sr_instance_id(+)
and mdrs.organization_id = mtp.sr_tp_id(+)
and mtp.partner_type(+) = 3
and mdr.plan_id = mdrs.plan_id
and mdr.sr_instance_id = mdrs.sr_instance_id
and mdr.organization_id = mdrs.organization_id
and mdr.department_id = mdrs.department_id
and mdr.resource_id = mdrs.resource_id) t1,
msc_currency_conv_mv mcc
where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.from_currency(+) = nvl(t1.currency_code, 'XXX')
and mcc.calendar_date(+) = t1.resource_date
)
group by
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
department_id,
nvl(owning_department_id,-23453),
resource_id,
inventory_item_id,
analysis_date
;
insert into msc_resources_f (
plan_id, plan_run_id,
sr_instance_id, organization_id,
department_id, owning_department_id, resource_id,
inventory_item_id, analysis_date,
aggr_type, category_set_id, sr_category_id, resource_group,
required_hours,
available_hours,
setup_time_hrs,
order_quantity,
resource_hours,
no_of_orders,
resource_cost,
resource_cost2,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- department (81)
select
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.department_id, f.owning_department_id, to_number(-23453) resource_id,
f.inventory_item_id, f.analysis_date,
to_number(81) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
'-23453' resource_group,
sum(f.required_hours),
sum(f.available_hours),
sum(f.setup_time_hrs),
sum(f.order_quantity),
sum(f.resource_hours),
sum(f.no_of_orders),
sum(f.resource_cost),
sum(f.resource_cost2),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_resources_f f
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type=0
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.department_id, f.owning_department_id,
f.inventory_item_id, f.analysis_date
union all
-- resource_group (82)
select
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
to_number(-23453) department_id,
to_number(-23453) owning_department_id,
to_number(-23453) resource_id,
f.inventory_item_id, f.analysis_date,
to_number(82) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
nvl(r.resource_group_name, '-23453') resource_group,
sum(f.required_hours),
sum(f.available_hours),
sum(f.setup_time_hrs),
sum(f.order_quantity),
sum(f.resource_hours),
sum(f.no_of_orders),
sum(f.resource_cost),
sum(f.resource_cost2),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_resources_f f,
msc_department_resources r
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type=0
and r.plan_id(+) = f.plan_id
and r.sr_instance_id(+) = f.sr_instance_id
and r.organization_id(+) = f.organization_id
and r.department_id(+) = nvl(f.owning_department_id, f.department_id)
and r.resource_id(+) = f.resource_id
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.inventory_item_id, f.analysis_date,
r.resource_group_name;
insert into msc_resources_f (
plan_id, plan_run_id,
sr_instance_id, organization_id,
department_id, owning_department_id, resource_id,
inventory_item_id, analysis_date,
aggr_type, category_set_id, sr_category_id, resource_group,
required_hours,
available_hours,
setup_time_hrs,
order_quantity,
resource_hours,
no_of_orders,
resource_cost,
resource_cost2,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
-- resource_group-mfg_period (1038)
select
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.department_id, f.owning_department_id, f.resource_id,
f.inventory_item_id, mp.period_start_date,
to_number(1038) aggr_type,
f.category_set_id, f.sr_category_id, f.resource_group,
sum(f.required_hours),
sum(f.available_hours),
sum(f.setup_time_hrs),
sum(f.order_quantity),
sum(f.resource_hours),
sum(f.no_of_orders),
sum(f.resource_cost),
sum(f.resource_cost2),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_resources_f f,
msc_phub_mfg_cal_periods_mv mp
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type = 82
and f.analysis_date between mp.period_start_date and mp.period_end_date
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.department_id, f.owning_department_id, f.resource_id,
f.inventory_item_id, mp.period_start_date,
f.category_set_id, f.sr_category_id, f.resource_group
union all
-- resource_group-fiscal_period (1039)
select
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.department_id, f.owning_department_id, f.resource_id,
f.inventory_item_id, fp.start_date,
to_number(1039) aggr_type,
f.category_set_id, f.sr_category_id, f.resource_group,
sum(f.required_hours),
sum(f.available_hours),
sum(f.setup_time_hrs),
sum(f.order_quantity),
sum(f.resource_hours),
sum(f.no_of_orders),
sum(f.resource_cost),
sum(f.resource_cost2),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id,
fnd_global.conc_program_id, fnd_global.conc_login_id,
fnd_global.prog_appl_id, fnd_global.conc_request_id
from
msc_resources_f f,
msc_phub_fiscal_periods_mv fp
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type = 82
and f.analysis_date between fp.start_date and fp.end_date
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.department_id, f.owning_department_id, f.resource_id,
f.inventory_item_id, fp.start_date,
f.category_set_id, f.sr_category_id, f.resource_group;
delete
from msc_resources_f
where plan_id = p_plan_id
and plan_run_id = nvl(p_plan_run_id,plan_run_id);