[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete from msc_form_query where query_id = p_query_id;
msc_phub_util.log('delete from msc_form_query: '||sql%rowcount);
select count(*)
into l_org_filter_cnt
from msc_form_query
where query_id = p_query_id and number1 = msc_hp_util.ft_organization;
select count(*)
into l_dept_filter_cnt
from msc_form_query
where query_id = p_query_id and number1 = msc_hp_util.ft_department;
select count(*)
into l_res_filter_cnt
from msc_form_query
where query_id = p_query_id and number1 = msc_hp_util.ft_resource;
select count(*)
into l_exception_filter_cnt
from msc_form_query
where query_id = p_query_id and number1 = msc_hp_util.ft_exception and number2 in (24,26);
select count(*)
into l_item_filter_cnt
from msc_form_query
where query_id = p_query_id and number1 = msc_hp_util.ft_item;
delete from msc_hp_row_dtls where query_id = p_query_id;
msc_phub_util.log('delete from msc_hp_row_dtls: '||sql%rowcount);
insert into msc_form_query(query_id, number1, number2, number3,
created_by, creation_date, last_update_date, last_updated_by, last_update_login)
select /*+ ordered */
p_query_id,
msc_hp_util.ft_end_pegging,
mfp1.sr_instance_id,
mfp1.end_pegging_id,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from
msc_full_pegging mfp1,
msc_exception_details med1,
msc_form_query q,
msc_trading_partners mtp1
where mfp1.plan_id = p_plan_id
and mfp1.plan_id = med1.plan_id
and mfp1.prev_pegging_id is null
and mfp1.plan_id = med1.plan_id
and mfp1.demand_id = med1.number1
and med1.exception_type = q.number2
and q.query_id = p_query_id
and q.number1 = msc_hp_util.ft_exception
and q.number2 in (24,26)
and mfp1.sr_instance_id = mtp1.sr_instance_id
and mfp1.organization_id = mtp1.sr_tp_id
and mtp1.partner_type = 3
and (l_org_filter_cnt = 0 or mtp1.organization_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_organization))
and (l_item_filter_cnt = 0 or mfp1.inventory_item_id in (select number2 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_item));
msc_phub_util.log('insert into msc_form_query: '||sql%rowcount);
insert into msc_hp_row_dtls(
query_id,
row_index,
plan_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
organization_code,
department_code,
resource_code,
data_flag,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
select
p_query_id,
(rank() over(order by organization_code, department_code, resource_code))-1 row_index,
plan_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
organization_code,
department_code,
resource_code,
1 data_flag,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from
(select distinct
r.plan_id,
r.sr_instance_id,
r.organization_id,
mdp.department_id,
mdp.resource_id,
mtp2.organization_code,
mdp.department_code,
mdp.resource_code
from
(select distinct
mfp2.plan_id,
mfp2.sr_instance_id,
mfp2.transaction_id,
mfp2.end_pegging_id,
mfp2.organization_id,
mrr.department_id,
mrr.resource_id
from
msc_full_pegging mfp2,
msc_resource_requirements mrr
where mfp2.plan_id = p_plan_id
and mfp2.plan_id = mrr.plan_id
and mfp2.sr_instance_id = mrr.sr_instance_id
--and mfp2.organization_id = mrr.organization_id
and mfp2.transaction_id = mrr.supply_id
and mrr.resource_id > 0
) r,
msc_exception_details med2,
msc_form_query q,
msc_trading_partners mtp2,
msc_department_resources mdp
where q.query_id = p_query_id
and q.number1 = msc_hp_util.ft_end_pegging
and r.sr_instance_id = q.number2
and r.end_pegging_id = q.number3
and r.plan_id = med2.plan_id
and r.transaction_id = med2.number1
--and med2.exception_type in (36,37,59,60,62,63,66,67)
and med2.exception_type in (36,60,63)
and r.sr_instance_id = mtp2.sr_instance_id
and r.organization_id = mtp2.sr_tp_id
and mtp2.partner_type = 3
and r.plan_id = mdp.plan_id
and r.sr_instance_id = mdp.sr_instance_id
and r.organization_id = mdp.organization_id
and r.department_id = mdp.department_id
and r.resource_id = mdp.resource_id
and (l_dept_filter_cnt = 0 or mdp.department_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_department))
and (l_res_filter_cnt = 0 or mdp.resource_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_resource))
) t;
msc_phub_util.log('insert into msc_hp_row_dtls: '||sql%rowcount);
insert into msc_hp_row_dtls(
query_id,
row_index,
plan_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
organization_code,
department_code,
resource_code,
data_flag,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
select
p_query_id,
(rank() over(order by mtp.organization_code, mdp.department_code, mdp.resource_code))-1 row_index,
mdp.plan_id,
mdp.sr_instance_id,
mdp.organization_id,
mdp.department_id,
mdp.resource_id,
mtp.organization_code,
mdp.department_code,
mdp.resource_code,
1 data_flag,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from
msc_department_resources mdp,
msc_trading_partners mtp
where mdp.plan_id = p_plan_id
and mdp.sr_instance_id = mtp.sr_instance_id
and mdp.organization_id = mtp.sr_tp_id
and mtp.partner_type = 3
and mdp.resource_id > 0
and (l_org_filter_cnt = 0 or mtp.organization_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_organization))
and (l_dept_filter_cnt = 0 or mdp.department_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_department))
and (l_res_filter_cnt = 0 or mdp.resource_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_resource));
msc_phub_util.log('insert into msc_hp_row_dtls: '||sql%rowcount);
insert into msc_hp_row_dtls(
query_id,
row_index,
plan_id,
data_flag,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
values (p_query_id, -1, p_plan_id, 1,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id);
msc_phub_util.log('insert into msc_hp_row_dtls: '||sql%rowcount);
select h.plan_id, b.bkt_start_date
into l_plan_id, l_past_date
from msc_hp_row_dtls h, msc_hp_col_dtls b
where h.query_id = p_query_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
and h.plan_id = b.plan_id
and b.bucket_type = 0
and rownum = 1;
delete from msc_res_plan_data
where (query_id, row_index) in
(select
h.query_id,
decode(p_summary, 1, -1, h.row_index) row_index
from msc_hp_row_dtls h
where h.query_id = p_query_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3));
msc_phub_util.log('delete from msc_res_plan_data: '||sql%rowcount);
insert into msc_res_plan_data(
query_id,
row_index,
analysis_date,
required_hours,
available_hours,
non_standard_jobs,
planned_order,
work_order,
total_resource_cost,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
select
p_query_id,
row_index,
analysis_date,
sum(required_hours) required_hours,
sum(available_hours) available_hours,
sum(non_standard_jobs) non_standard_jobs,
sum(planned_order) planned_order,
sum(work_order) work_order,
sum(total_resource_cost) total_resource_cost,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from
(
-- requirements, non-batchable
select
decode(p_summary, 1, -1, h.row_index) row_index,
b.bkt_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,
to_number(null) available_hours,
to_number(null) non_standard_jobs,
to_number(null) planned_order,
to_number(null) work_order,
to_number(null) total_resource_cost
from
msc_resource_requirements mrr,
msc_department_resources mdr,
msc_hp_col_dtls b,
msc_hp_row_dtls h
where h.query_id = p_query_id
and mrr.plan_id = h.plan_id
and mrr.sr_instance_id = h.sr_instance_id
and mrr.organization_id = h.organization_id
and mrr.department_id = h.department_id
and mrr.resource_id = h.resource_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
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 mrr.batch_number is null
and mrr.plan_id = b.plan_id
and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
between b.bkt_start_date and b.bkt_end_date
and b.bucket_index>0
and nvl(mrr.parent_id, l_constrained_plan) = l_constrained_plan
group by
decode(p_summary, 1, -1, h.row_index),
b.bkt_start_date
union all
-- requirements, batchable
select
decode(p_summary, 1, -1, h.row_index) row_index,
b.bkt_start_date analysis_date,
avg(mrr.resource_hours) required_hours,
to_number(null) available_hours,
to_number(null) non_standard_jobs,
to_number(null) planned_order,
to_number(null) work_order,
to_number(null) total_resource_cost
from
msc_resource_requirements mrr,
msc_department_resources mdr,
msc_hp_col_dtls b,
msc_hp_row_dtls h
where h.query_id = p_query_id
and mrr.plan_id = h.plan_id
and mrr.sr_instance_id = h.sr_instance_id
and mrr.organization_id = h.organization_id
and mrr.department_id = h.department_id
and mrr.resource_id = h.resource_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
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 mrr.batch_number is not null
and mrr.supply_type in (3,4,5,7,27,60)
and mrr.plan_id = b.plan_id
and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
between b.bkt_start_date and b.bkt_end_date
and b.bucket_index>0
and nvl(mrr.parent_id, l_constrained_plan) = l_constrained_plan
group by
decode(p_summary, 1, -1, h.row_index),
b.bkt_start_date
union all
-- availability, PDS
select
decode(p_summary, 1, -1, h.row_index) row_index,
b.bkt_start_date analysis_date,
to_number(null) required_hours,
sum(mra.capacity_units * decode(mra.from_time, null, 0,
(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) non_standard_jobs,
to_number(null) planned_order,
to_number(null) work_order,
to_number(null) total_resource_cost
from
msc_net_resource_avail mra,
msc_hp_col_dtls b,
msc_hp_row_dtls h
where h.query_id = p_query_id
and mra.plan_id = h.plan_id
and mra.sr_instance_id = h.sr_instance_id
and mra.organization_id = h.organization_id
and mra.department_id = h.department_id
and mra.resource_id = h.resource_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
and mra.resource_id > 0
and mra.capacity_units >= 0
and mra.plan_id = b.plan_id
and trunc(mra.shift_date) between b.bkt_start_date and b.bkt_end_date
and b.bucket_index>0
and nvl(mra.parent_id,0) <> -1
and not exists (
select 1
from msc_res_plan_updates a2
where mra.plan_id = a2.plan_id
and mra.sr_instance_id = a2.sr_instance_id
and mra.organization_id = a2.organization_id
and mra.department_id = a2.department_id
and mra.resource_id = a2.resource_id
and mra.shift_date = a2.shift_date
and a2.query_id = h.query_id
)
group by
decode(p_summary, 1, -1, h.row_index),
b.bkt_start_date
union all
-- availability, updates
select
decode(p_summary, 1, -1, h.row_index) row_index,
b.bkt_start_date analysis_date,
to_number(null) required_hours,
sum(a2.capacity_units * decode(a2.from_time, null, 0,
(decode(sign(a2.to_time-a2.from_time),
-1, a2.to_time+86400, a2.to_time) - a2.from_time)/3600)) available_hours,
to_number(null) non_standard_jobs,
to_number(null) planned_order,
to_number(null) work_order,
to_number(null) total_resource_cost
from
msc_res_plan_updates a2,
msc_hp_col_dtls b,
msc_hp_row_dtls h
where h.query_id = p_query_id
and a2.query_id = h.query_id
and a2.plan_id = h.plan_id
and a2.sr_instance_id = h.sr_instance_id
and a2.organization_id = h.organization_id
and a2.department_id = h.department_id
and a2.resource_id = h.resource_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
and a2.resource_id > 0
and a2.capacity_units >= 0
and a2.plan_id = b.plan_id
and trunc(a2.shift_date) between b.bkt_start_date and b.bkt_end_date
and b.bucket_index>0
and nvl(a2.parent_id,0) <> -1
group by
decode(p_summary, 1, -1, h.row_index),
b.bkt_start_date
union all
-- orders
select
decode(p_summary, 1, -1, h.row_index) row_index,
b.bkt_start_date analysis_date,
to_number(null) required_hours,
to_number(null) available_hours,
sum(decode(ms.order_type, 7, decode(mrr.batch_number, null,
(decode(mdr.line_flag, 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
mdr.max_rate * mrr.daily_resource_hours)),
(case when mrr.supply_type in (3,4,5,7,27,60) then mrr.resource_hours end)))) non_standard_jobs,
sum(decode(ms.order_type, 5, decode(mrr.batch_number, null,
(decode(mdr.line_flag, 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
mdr.max_rate * mrr.daily_resource_hours)),
(case when mrr.supply_type in (3,4,5,7,27,60) then mrr.resource_hours end)))) planned_order,
sum(decode(ms.order_type, 3, decode(mrr.batch_number, null,
(decode(mdr.line_flag, 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
mdr.max_rate * mrr.daily_resource_hours)),
(case when mrr.supply_type in (3,4,5,7,27,60) then mrr.resource_hours end)))) work_order,
sum(case when ms.order_type in (3,4,5,7,27,60,70,74,79,92) then
decode(mrr.batch_number, null,
(decode(mdr.line_flag, 2, decode(mrr.end_date, null, mrr.resource_hours, mrr.daily_resource_hours),
mdr.max_rate * mrr.daily_resource_hours)),
(case when mrr.supply_type in (3,4,5,7,27,60) then mrr.resource_hours end)) *
mdr.resource_cost * (1 + nvl(mdr2.dept_overhead_cost,0)) end)
total_resource_cost
from
msc_resource_requirements mrr,
msc_department_resources mdr,
msc_department_resources mdr2,
msc_supplies ms,
msc_hp_col_dtls b,
msc_hp_row_dtls h
where h.query_id = p_query_id
and mrr.plan_id = h.plan_id
and mrr.sr_instance_id = h.sr_instance_id
and mrr.organization_id = h.organization_id
and mrr.department_id = h.department_id
and mrr.resource_id = h.resource_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
and nvl(mrr.parent_id, l_constrained_plan) = l_constrained_plan
and nvl(mrr.schedule_flag,2) = 1
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 mrr.plan_id = b.plan_id
and trunc(decode(l_constrained_plan,1,mrr.start_date,nvl(mrr.end_date,mrr.start_date)))
between b.bkt_start_date and b.bkt_end_date
and b.bucket_index>0
and mrr.plan_id = mdr.plan_id
and mrr.sr_instance_id = mdr.sr_instance_id
and mrr.organization_id = mdr.organization_id
and mrr.department_id = mdr.department_id
and mrr.resource_id = mdr.resource_id
and mdr2.plan_id = -1
and mrr.sr_instance_id = mdr2.sr_instance_id
and mrr.organization_id = mdr2.organization_id
and mrr.department_id = mdr2.department_id
and mrr.resource_id = mdr2.resource_id
and mdr2.line_flag<>1
group by
decode(p_summary, 1, -1, h.row_index),
b.bkt_start_date
)
group by
row_index,
analysis_date;
msc_phub_util.log('insert into msc_res_plan_data: '||sql%rowcount);
update msc_hp_row_dtls
set data_flag = 2
where query_id = p_query_id and data_flag = 3;
msc_phub_util.log('update msc_hp_row_dtls: '||sql%rowcount);
update msc_hp_row_dtls
set data_flag = 3
where query_id = p_query_id
and row_index = -1
and data_flag = 1;
delete from msc_res_plan_data where query_id = p_query_id;
select count(*) into l_n
from msc_hp_row_dtls
where query_id = p_query_id
and row_index between p_row_index and p_row_index+g_page_size-1
and data_flag = 1;
update msc_hp_row_dtls
set data_flag = 3
where query_id = p_query_id
and row_index between p_row_index and p_row_index+g_fetch_size-1
and data_flag = 1;
select
h.row_index,
h.plan_id,
h.sr_instance_id,
h.organization_id,
h.department_id,
h.resource_id,
u.analysis_date,
u.time_level,
decode(u.time_level, 2, b.week_start_date, 3, period_start_date, bkt_start_date) start_date,
decode(u.time_level, 2, b.week_end_date, 3, period_end_date, bkt_end_date) end_date,
decode(u.time_level, 2, b.week_last_work_date, 3, period_last_work_date, bkt_end_date) last_work_date,
u.new_value
from
msc_hp_updates u,
msc_hp_row_dtls h,
msc_hp_col_dtls b
where h.query_id = p_query_id
and h.query_id = u.query_id
and h.row_index = u.row_index
and h.plan_id = b.plan_id
and u.analysis_date = b.bkt_start_date
and u.hp_type_code = 'MSC_ASCP_RES_PLAN_TYPE'
and u.column_name = 'available_hours'
and u.new_value >= 0
and nvl(u.process_status, 1) = 1;
select
query_id,
plan_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
shift_num,
shift_date,
from_time,
to_time,
from_time2,
to_time2,
min(from_time2) over(partition by shift_date order by shift_date, from_time) first_from_time2,
max(to_time2) over(partition by shift_date order by shift_date, from_time) last_to_time2,
lead(from_time2) over(order by shift_date, from_time) next_from_time2,
lag(to_time2) over(order by shift_date, from_time) prev_to_time2,
capacity_units,
parent_id,
decode(from_time, null, 0,
(decode(sign(to_time-from_time),
-1, to_time+86400, to_time) - from_time)/3600) shift_capacity
from msc_res_plan_updates
where query_id = p_query_id
and plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and department_id = p_department_id
and resource_id = p_resource_id
and shift_date between p_start_date and p_end_date
order by shift_date, from_time
for update;
delete from msc_res_plan_updates
where query_id = p_query_id
and sr_instance_id = ru.sr_instance_id
and organization_id = ru.organization_id
and department_id = ru.department_id
and resource_id = ru.resource_id
and shift_date between ru.start_date and ru.end_date;
msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
insert into msc_res_plan_updates (
query_id,
plan_id,
transaction_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
shift_num,
shift_date,
from_time,
to_time,
from_time2,
to_time2,
parent_id,
capacity_units,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
select
p_query_id,
ru.plan_id plan_id,
nvl(mra.transaction_id, -1) transaction_id,
ru.sr_instance_id sr_instance_id,
ru.organization_id organization_id,
ru.department_id department_id,
ru.resource_id resource_id,
mra.shift_num,
md.calendar_date shift_date,
mra.from_time,
mra.to_time,
mra.shift_date+(mra.from_time/86400) from_time2,
mra.shift_date+(decode(sign(mra.to_time-mra.from_time), -1, mra.to_time+86400, mra.to_time))/86400 to_time2,
mra.parent_id,
mra.capacity_units,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from msc_net_resource_avail mra, msc_calendar_dates md
where mra.plan_id(+) = ru.plan_id
and mra.sr_instance_id(+) = ru.sr_instance_id
and mra.organization_id(+) = ru.organization_id
and mra.department_id(+) = ru.department_id
and mra.resource_id(+) = ru.resource_id
and trunc(mra.shift_date(+)) = md.calendar_date
and md.calendar_date between ru.start_date and ru.end_date
and md.calendar_code = l_calendar_code
and md.exception_set_id = -1
and nvl(mra.parent_id(+),0) <> -1;
msc_phub_util.log('insert into msc_res_plan_updates: '||sql%rowcount);
select
sum(nvl(decode(from_time, null, 0,
(decode(sign(to_time-from_time),
-1, to_time+86400, to_time) - from_time)/3600) * capacity_units, 0))
total_capacity
into l_total_capacity
from msc_res_plan_updates
where query_id = p_query_id
and plan_id = ru.plan_id
and sr_instance_id = ru.sr_instance_id
and organization_id = ru.organization_id
and department_id = ru.department_id
and resource_id = ru.resource_id
and shift_date between ru.start_date and ru.end_date;
' from msc_res_plan_updates'||
' where query_id = '||p_query_id||
' and plan_id = '||ru.plan_id||
' and sr_instance_id = '||ru.sr_instance_id||
' and organization_id = '||ru.organization_id||
' and department_id = '||ru.department_id||
' and resource_id = '||ru.resource_id||
' and trunc(shift_date) between to_date('''||ru.start_date||''') and to_date('''||ru.end_date||''')');
update msc_res_plan_updates
set from_time = r.from_time,
from_time2 = r.from_time2,
to_time = r.to_time,
to_time2 = r.to_time2,
change_flag = 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where current of c;
update msc_res_plan_updates
set to_time = r.to_time,
to_time2 = r.to_time2,
change_flag = 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where current of c;
select max(to_time2)
into r.prev_to_time2
from msc_res_plan_updates
where query_id = p_query_id
and sr_instance_id = r.sr_instance_id
and organization_id = r.organization_id
and department_id = r.department_id
and resource_id = r.resource_id
and shift_date = r.shift_date - 1;
select max(shift_date+(decode(sign(to_time-from_time), -1, to_time+86400, to_time))/86400)
into r.prev_to_time2
from msc_net_resource_avail
where plan_id = r.plan_id
and sr_instance_id = r.sr_instance_id
and organization_id = r.organization_id
and department_id = r.department_id
and resource_id = r.resource_id
and shift_date = r.shift_date - 1;
select min(from_time2)
into r.next_from_time2
from msc_res_plan_updates
where query_id = p_query_id
and sr_instance_id = r.sr_instance_id
and organization_id = r.organization_id
and department_id = r.department_id
and resource_id = r.resource_id
and shift_date = r.shift_date + 1;
select min(shift_date+(from_time/86400))
into r.next_from_time2
from msc_net_resource_avail
where plan_id = r.plan_id
and sr_instance_id = r.sr_instance_id
and organization_id = r.organization_id
and department_id = r.department_id
and resource_id = r.resource_id
and shift_date = r.shift_date + 1;
update msc_res_plan_updates
set from_time = r.from_time,
from_time2 = r.from_time2,
to_time = r.to_time,
to_time2 = r.to_time2,
change_flag = 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where current of c;
update msc_res_plan_updates
set from_time = r.from_time,
from_time2 = r.from_time2,
to_time = r.to_time,
to_time2 = r.to_time2,
shift_num = r.shift_num,
capacity_units = r.capacity_units,
change_flag = 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where current of c;
update msc_res_plan_updates
set from_time = r.from_time,
from_time2 = r.from_time2,
to_time = r.to_time,
to_time2 = r.to_time2,
capacity_units = r.capacity_units,
change_flag = 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where current of c;
update msc_hp_row_dtls
set data_flag = 3
where query_id = p_query_id
and row_index = ru.row_index;
msc_phub_util.log('update msc_hp_row_dtls: '||sql%rowcount);
update msc_hp_updates set
process_status = 2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where query_id = p_query_id and nvl(process_status, 1) = 1;
msc_phub_util.log('update msc_hp_updates: '||sql%rowcount);
delete from msc_hp_row_dtls where query_id = p_query_id;
msc_phub_util.log('delete from msc_hp_row_dtls: '||sql%rowcount);
delete from msc_res_plan_data where query_id = p_query_id;
msc_phub_util.log('delete from msc_res_plan_data: '||sql%rowcount);
delete from msc_hp_updates where query_id = p_query_id;
msc_phub_util.log('delete from msc_hp_updates: '||sql%rowcount);
delete from msc_res_plan_updates where query_id = p_query_id;
msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
select
query_id,
plan_id,
transaction_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
shift_num,
shift_date,
from_time,
to_time,
from_time2,
to_time2,
parent_id,
change_flag,
capacity_units
from msc_res_plan_updates
where query_id = p_query_id
and change_flag = 1
for update nowait;
select
from_time,
to_time,
shift_num,
capacity_units,
last_update_date,
last_updated_by,
last_update_login
from msc_net_resource_avail
where plan_id = p_plan_id
and transaction_id = p_transaction_id
and sr_instance_id = p_sr_instance_id
for update nowait;
select
capacity_units,
last_update_date,
last_updated_by,
last_update_login
from msc_net_resource_avail
where plan_id = p_plan_id
and transaction_id = p_parent_id
and parent_id = -1
and sr_instance_id = p_sr_instance_id
for update nowait;
select transaction_id into r3.parent_id
from msc_net_resource_avail
where plan_id = r3.plan_id
and sr_instance_id = r3.sr_instance_id
and organization_id = r3.organization_id
and department_id = r3.department_id
and resource_id = r3.resource_id
and shift_date = r3.shift_date
and parent_id = -1;
select msc_net_resource_avail_s.nextval into r3.parent_id from dual;
insert into msc_net_resource_avail (
plan_id,
transaction_id,
parent_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
shift_date,
capacity_units,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
values (
r3.plan_id,
r3.parent_id,
-1,
r3.sr_instance_id,
r3.organization_id,
r3.department_id,
r3.resource_id,
r3.shift_date,
r3.capacity_units * decode(r3.from_time, null, 0,
(decode(sign(r3.to_time-r3.from_time),
-1, r3.to_time+86400, r3.to_time) - r3.from_time)/3600),
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id);
msc_phub_util.log('insert into msc_net_resource_avail (summary): '||sql%rowcount||', '||r3.parent_id);
select msc_net_resource_avail_s.nextval into r3.transaction_id from dual;
insert into msc_net_resource_avail (
plan_id,
transaction_id,
parent_id,
sr_instance_id,
organization_id,
department_id,
resource_id,
shift_num,
shift_date,
from_time,
to_time,
capacity_units,
status,
applied,
updated,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
values (
r3.plan_id,
r3.transaction_id,
r3.parent_id,
r3.sr_instance_id,
r3.organization_id,
r3.department_id,
r3.resource_id,
r3.shift_num,
r3.shift_date,
r3.from_time,
r3.to_time,
r3.capacity_units,
0,
2,
2,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id);
msc_phub_util.log('insert into msc_net_resource_avail (detail): '||sql%rowcount||', '||r3.transaction_id);
update msc_res_plan_updates
set transaction_id=r3.transaction_id
where current of c3;
msc_phub_util.log('update msc_res_plan_updates: '||sql%rowcount);
update msc_net_resource_avail
set from_time = r3.from_time,
to_time = r3.to_time,
shift_num = r3.shift_num,
capacity_units = r3.capacity_units,
status = 0,
applied = 2,
updated = 2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where current of c4;
msc_phub_util.log('update msc_net_resource_avail (detail): '||sql%rowcount||', '||r3.transaction_id);
update msc_net_resource_avail
set capacity_units = r3.capacity_units * decode(r3.from_time, null, 0,
(decode(sign(r3.to_time-r3.from_time),
-1, r3.to_time+86400, r3.to_time) - r3.from_time)/3600)
where current of c5;
msc_phub_util.log('update msc_net_resource_avail (summary): '||sql%rowcount||', '||r3.parent_id);
update msc_res_plan_updates
set change_flag = 2
where query_id = p_query_id
and change_flag = 1;
msc_phub_util.log('update msc_res_plan_updates: '||sql%rowcount);
delete from msc_res_plan_updates
where query_id = p_query_id;
msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);