[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select plan_type, sr_instance_id, plan_start_date, plan_cutoff_date, temp_transfer_id
into l_plan_type, l_sr_instance_id, l_plan_start_date, l_plan_cutoff_date, l_transfer_id
from msc_plan_runs
where plan_id=p_plan_id
and plan_run_id=p_plan_run_id;
select refresh_mode into l_refresh_mode
from msc_plan_runs
where plan_run_id = p_plan_run_id;
delete from msc_resources_f
where plan_id = p_plan_id
and plan_run_id = p_plan_run_id
and (p_plan_id, sr_instance_id, organization_id, department_id, resource_id) in
(select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid);
msc_phub_util.log('msc_resources_f, delete='||sql%rowcount||', l_rowcount1='||l_rowcount1);
delete from msc_resources_cum_f
where plan_id = p_plan_id
and plan_run_id = p_plan_run_id
and (p_plan_id, sr_instance_id, organization_id, department_id, resource_id) in
(select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid);
msc_phub_util.log('msc_resources_cum_f, delete='||sql%rowcount||', l_rowcount2='||l_rowcount2);
insert /*+ append nologging */ into msc_st_resources_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
department_id,
resource_id,
inventory_item_id,
analysis_date,
required_hours,
setup_time_hrs,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
mrr.sr_instance_id,
mrr.organization_id,
mrr.department_id,
mrr.resource_id,
to_number(-23453) inventory_item_id, -- do not use nvl(mrr.assembly_item_id, -23453), res-item granularity is too much
trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date))) analysis_date,
sum(decode(mdr.line_flag,
2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
mdr.max_rate * mrr.daily_resource_hours)) required_hours,
sum(decode(nvl(mrr.schedule_flag,1), 1, 0,
decode(mdr.line_flag,
2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
mdr.max_rate * mrr.daily_resource_hours))) setup_time_hrs,
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_resource_requirements mrr,
msc_department_resources mdr
where mrr.plan_id = p_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 nvl(mdr.batchable_flag,2) =2
and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
between l_plan_start_date and l_plan_cutoff_date
and nvl(mrr.parent_id, l_constrained_plan) = l_constrained_plan
and (p_plan_id <> -1
or (p_plan_id = -1
and mdr.sr_instance_id = l_sr_instance_id
and (l_refresh_mode = 1
or (l_refresh_mode = 2 and (p_plan_id, mdr.sr_instance_id, mdr.organization_id, mdr.department_id, mdr.resource_id) in
(select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid)))
)
)
group by
mrr.sr_instance_id,
mrr.organization_id,
mrr.department_id,
mrr.resource_id,
trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)));
msc_phub_util.log('insert into msc_st_resources_f:requirements: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_resources_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
department_id,
resource_id,
inventory_item_id,
analysis_date,
available_hours,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
mra.sr_instance_id,
mra.organization_id,
mra.department_id,
mra.resource_id,
to_number(-23453) inventory_item_id,
trunc(mra.shift_date) analysis_date,
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,
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_net_resource_avail mra
where mra.plan_id = p_plan_id
and mra.resource_id > 0
and mra.capacity_units >= 0 -- bug 10010498
and mra.sr_instance_id=decode(p_plan_id, -1, l_sr_instance_id, mra.sr_instance_id) -- bug 9599539
and trunc(mra.shift_date) between l_plan_start_date and l_plan_cutoff_date
and ((p_plan_id <> -1
and nvl(mra.parent_id,0) <> -1)
or (p_plan_id = -1
and mra.simulation_set is null
and (l_refresh_mode = 1
or (l_refresh_mode = 2 and (p_plan_id, mra.sr_instance_id, mra.organization_id, mra.department_id, mra.resource_id) in
(select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid) ) )
)
)
group by
mra.sr_instance_id,
mra.organization_id,
mra.department_id,
mra.resource_id,
trunc(mra.shift_date);
msc_phub_util.log('insert into msc_st_resources_f:availability: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_resources_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
department_id,
resource_id,
inventory_item_id,
analysis_date,
order_quantity,
resource_hours,
no_of_orders,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
l_transfer_id,
to_number(0),
mrr.sr_instance_id,
mrr.organization_id,
mrr.department_id,
mrr.resource_id,
ms.inventory_item_id,
trunc(nvl(mrr.end_date,mrr.start_date)) analysis_date,
sum(nvl(mrr.cummulative_quantity, ms.new_order_quantity)) order_quantity,
sum(mrr.resource_hours) resource_hours,
1 no_of_orders,
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_resource_requirements mrr,
msc_supplies ms,
msc_department_resources mdr
where mrr.plan_id = p_plan_id
and nvl(mrr.parent_id, l_constrained_plan) = 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 l_plan_start_date and l_plan_cutoff_date
and p_plan_id <> -1
group by
mrr.sr_instance_id,
mrr.organization_id,
mrr.department_id,
mrr.resource_id,
ms.inventory_item_id,
ms.transaction_id,
trunc(nvl(mrr.end_date,mrr.start_date));
msc_phub_util.log('insert into msc_st_resources_f:orders: '||sql%rowcount);
insert /*+ append nologging */ into msc_st_resources_f (
st_transaction_id,
error_code,
sr_instance_id,
organization_id,
department_id,
resource_id,
inventory_item_id,
analysis_date,
required_hours,
available_hours,
setup_time_hrs,
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)
select
l_transfer_id,
to_number(0),
t1.sr_instance_id,
t1.organization_id,
t1.department_id,
t1.resource_id,
t1.inventory_item_id,
t1.resource_date analysis_date,
t1.required_hours,
t1.available_hours,
t1.setup_hours setup_time_hrs,
t1.resource_cost,
t1.resource_cost * decode(decode(l_plan_type, 6, l_owning_currency_code, t1.currency_code),
fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate, 0)) 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
/* For SNO plan don't filter detail_level.
*/
(select /*+ ordered */
mdrs.sr_instance_id,
mdrs.organization_id,
mtp.currency_code,
to_number(-23453) inventory_item_id,
mdrs.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_trading_partners mtp
where mdrs.plan_id = p_plan_id
and l_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
union all
select /*+ ordered */
mbid.sr_instance_id,
mbid.organization_id,
mtp.currency_code,
mbid.inventory_item_id,
to_number(-23453) department_id,
to_number(-23453) resource_id,
trunc(mbid.detail_date) resource_date,
to_number(null) required_hours,
to_number(null) available_hours,
to_number(null) setup_hours,
mbid.production_cost
from
msc_bis_inv_detail mbid,
msc_trading_partners mtp
where mbid.plan_id = p_plan_id
and nvl(mbid.detail_level, 0) = 1
and nvl(mbid.period_type, 0) = 1
and l_plan_type in (1,101,102,103,105)
and mbid.sr_instance_id = mtp.sr_instance_id(+)
and mbid.organization_id = mtp.sr_tp_id(+)
and mtp.partner_type(+) = 3
and mbid.production_cost>0
) t1,
msc_currency_conv_mv mcc
where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.from_currency(+) = decode(l_plan_type, 6, l_owning_currency_code, t1.currency_code)
and mcc.calendar_date(+) = t1.resource_date
and p_plan_id <> -1;
msc_phub_util.log('insert into msc_st_resources_f:resource_cost: '||sql%rowcount);
insert into msc_resources_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
inventory_item_id,
analysis_date,
aggr_type,
resource_group,
available_hours,
required_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)
select
p_plan_id,
p_plan_run_id,
f.sr_instance_id,
f.organization_id,
f.department_id,
f.resource_id,
f.inventory_item_id,
f.analysis_date,
to_number(0) aggr_type,
'-23453' resource_group,
sum(f.available_hours),
sum(f.required_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_st_resources_f f
where f.st_transaction_id=l_transfer_id
group by
sr_instance_id,
organization_id,
department_id,
resource_id,
inventory_item_id,
analysis_date;
msc_phub_util.log('insert into msc_st_resources_f:final: '||sql%rowcount);
insert into msc_resources_cum_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
inventory_item_id,
analysis_date,
aggr_type,
resource_group,
cum_net_resource_avail,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
f.plan_id,
f.plan_run_id,
f.sr_instance_id,
f.organization_id,
f.department_id,
f.resource_id,
f.inventory_item_id,
d.calendar_date analysis_date,
to_number(0) aggr_type,
'-23453' resource_group,
sum(nvl(f.available_hours, 0) - nvl(f.required_hours, 0)) cum_net_resource_avail,
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_dates_mv d
where f.plan_id=p_plan_id
and f.plan_run_id=p_plan_run_id
and f.aggr_type=0
and d.calendar_date between l_plan_start_date and l_plan_cutoff_date
and d.calendar_date in (d.mfg_week_end_date, d.fis_period_end_date, d.month_end_date, l_plan_cutoff_date)
and d.calendar_date >= f.analysis_date
and (f.available_hours > 0 or f.required_hours > 0)
and ((f.plan_id <> -1)
or (f.plan_id = -1
and f.sr_instance_id = l_sr_instance_id
and (l_refresh_mode = 1
or (l_refresh_mode = 2 and (p_plan_id, f.sr_instance_id, f.organization_id, f.department_id, f.resource_id) in
(select number1, number2, number3, number4, number5 from msc_hub_query q where q.query_id = l_res_rn_qid)))
)
)
group by
f.plan_id,
f.plan_run_id,
f.sr_instance_id,
f.organization_id,
f.department_id,
f.resource_id,
f.inventory_item_id,
d.calendar_date;
msc_phub_util.log('msc_resources_cum_f, insert='||sql%rowcount||', l_rowcount2='||l_rowcount2);
delete from msc_resources_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
msc_phub_util.log('msc_resource_pkg.summarize_resources_f, delete='||sql%rowcount);
insert into msc_resources_f (
plan_id, plan_run_id,
sr_instance_id, organization_id,
department_id, resource_id,
inventory_item_id, analysis_date,
aggr_type, 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,
to_number(-23453) resource_id,
f.inventory_item_id, f.analysis_date,
to_number(81) aggr_type,
'-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.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) resource_id,
f.inventory_item_id, f.analysis_date,
to_number(82) aggr_type,
nvl(r.resource_group_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,
msc_phub_resources_mv r
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type=0
and r.sr_instance_id(+) = f.sr_instance_id
and r.organization_id(+) = f.organization_id
and r.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,
nvl(r.resource_group_id, '-23453');
insert into msc_resources_f (
plan_id, plan_run_id,
sr_instance_id, organization_id,
department_id, resource_id,
inventory_item_id, analysis_date,
aggr_type, 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.resource_id,
f.inventory_item_id, d.mfg_period_start_date,
to_number(1038) aggr_type,
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_dates_mv d
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 = d.calendar_date
and d.mfg_period_start_date is not null
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.department_id, f.resource_id,
f.inventory_item_id, d.mfg_period_start_date,
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.resource_id,
f.inventory_item_id, d.fis_period_start_date,
to_number(1039) aggr_type,
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_dates_mv d
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 = d.calendar_date
and d.fis_period_start_date is not null
group by
f.plan_id, f.plan_run_id,
f.sr_instance_id, f.organization_id,
f.department_id, f.resource_id,
f.inventory_item_id, d.fis_period_start_date,
f.resource_group;
delete from msc_resources_cum_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
msc_phub_util.log('msc_resource_pkg.summarize_resources_cum_f, delete='||sql%rowcount);
insert into msc_resources_cum_f (
plan_id, plan_run_id,
sr_instance_id, organization_id,
department_id, resource_id,
inventory_item_id, analysis_date,
aggr_type, resource_group,
cum_net_resource_avail,
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,
to_number(-23453) resource_id,
f.inventory_item_id, f.analysis_date,
to_number(81) aggr_type,
'-23453' resource_group,
sum(f.cum_net_resource_avail),
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_cum_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.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) resource_id,
f.inventory_item_id, f.analysis_date,
to_number(82) aggr_type,
nvl(r.resource_group_id, '-23453') resource_group,
sum(f.cum_net_resource_avail),
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_cum_f f,
msc_phub_resources_mv r
where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
and f.aggr_type=0
and r.sr_instance_id(+) = f.sr_instance_id
and r.organization_id(+) = f.organization_id
and r.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,
nvl(r.resource_group_id, '-23453');
delete from msc_st_resources_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_resources_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' department_id,'||
' resource_id,'||
' inventory_item_id,'||
' organization_code,'||
' department_code,'||
' department_class,'||
' resource_code,'||
' resource_group_name,'||
' item_name,'||
' analysis_date,'||
' available_hours,'||
' required_hours,'||
' setup_time_hrs,'||
' order_quantity,'||
' resource_hours,'||
' no_of_orders,'||
' resource_cost,'||
' resource_cost2,'||
' created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.department_id,'||
' f.resource_id,'||
' f.inventory_item_id,'||
' mtp.organization_code,'||
' mdr.department_code,'||
' mdr.department_class,'||
' mdr.resource_code,'||
' mdr.resource_group_name,'||
' mi.item_name,'||
' f.analysis_date,'||
' f.available_hours,'||
' f.required_hours,'||
' f.setup_time_hrs,'||
' f.order_quantity,'||
' f.resource_hours,'||
' f.no_of_orders,'||
' f.resource_cost,'||
' f.resource_cost2,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' '||l_apps_schema||'.msc_resources_f'||l_suffix||' f,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
' '||l_apps_schema||'.msc_department_resources'||l_suffix||' mdr'||
' where f.plan_run_id=:p_plan_run_id'||
' and f.aggr_type=0'||
' and mtp.partner_type(+)=3'||
' and mtp.sr_instance_id(+)=f.sr_instance_id'||
' and mtp.sr_tp_id(+)=f.organization_id'||
' and mi.inventory_item_id(+)=f.inventory_item_id'||
' and mdr.plan_id(+)=-1'||
' and mdr.department_id(+)=f.department_id'||
' and mdr.resource_id(+)=f.resource_id'||
' and mdr.sr_instance_id(+)=f.sr_instance_id'||
' and mdr.organization_id(+)=f.organization_id';
delete from msc_st_resources_cum_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_resources_cum_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' department_id,'||
' resource_id,'||
' inventory_item_id,'||
' organization_code,'||
' department_code,'||
' department_class,'||
' resource_code,'||
' resource_group_name,'||
' item_name,'||
' analysis_date,';
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.department_id,'||
' f.resource_id,'||
' f.inventory_item_id,'||
' mtp.organization_code,'||
' mdr.department_code,'||
' mdr.department_class,'||
' mdr.resource_code,'||
' mdr.resource_group_name,'||
' mi.item_name,'||
' f.analysis_date,';
msc_phub_util.log('msc_resource_pkg.import_resources_f: insert into msc_resources_f');
insert into msc_resources_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
inventory_item_id,
analysis_date,
available_hours,
required_hours,
setup_time_hrs,
order_quantity,
resource_hours,
no_of_orders,
resource_cost,
resource_cost2,
aggr_type, resource_group,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login
)
select
p_plan_id,
p_plan_run_id,
nvl(sr_instance_id, -23453),
nvl(organization_id, -23453),
nvl(department_id, -23453),
nvl(resource_id, -23453),
nvl(inventory_item_id, -23453),
analysis_date,
available_hours,
required_hours,
setup_time_hrs,
order_quantity,
resource_hours,
no_of_orders,
resource_cost,
resource_cost2,
0, '-23453',
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_resources_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_resource_pkg.import_resources_f: inserted='||sql%rowcount);
msc_phub_util.log('msc_resource_pkg.import_resources_cum_f: insert into msc_resources_cum_f');
insert into msc_resources_cum_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
inventory_item_id,
analysis_date,
cum_net_resource_avail,
aggr_type, resource_group,
created_by, creation_date,
last_updated_by, last_update_date, last_update_login
)
select
p_plan_id,
p_plan_run_id,
nvl(sr_instance_id, -23453),
nvl(organization_id, -23453),
nvl(department_id, -23453),
nvl(resource_id, -23453),
nvl(inventory_item_id, -23453),
analysis_date,
cum_net_resource_avail,
0, '-23453',
fnd_global.user_id, sysdate,
fnd_global.user_id, sysdate, fnd_global.login_id
from msc_st_resources_cum_f
where st_transaction_id=p_st_transaction_id and error_code=0;
msc_phub_util.log('msc_resource_pkg.import_resources_cum_f: inserted='||sql%rowcount);