[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mtp.calendar_code
into l_calendar_code
from msc_trading_partners mtp, msc_plans mp
where mtp.sr_tp_id=mp.organization_id
and mtp.sr_instance_id=mp.sr_instance_id
and mp.plan_id=p_plan_id
and mtp.partner_type=3;
delete from msc_hp_col_dtls where plan_id=p_plan_id;
msc_phub_util.log('delete from msc_hp_col_dtls: '||sql%rowcount);
select count(*) into l_n
from msc_hp_col_dtls
where plan_id=p_plan_id;
insert into msc_hp_col_dtls(
plan_id,
bkt_start_date,
bkt_end_date,
seq_num,
bucket_type,
bucket_index,
week_start_date,
week_end_date,
week_last_work_date,
period_start_date,
period_end_date,
period_last_work_date,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
with t as (
select
b.bkt_start_date,
b.bkt_end_date,
b.seq_num,
b.bucket_type,
b.bucket_index,
w.week_start_date week_start_date,
w.week_end_date,
w.week_last_work_date,
p.period_start_date period_start_date,
p.period_end_date,
p.period_last_work_date
from
(select
b.bkt_start_date,
b.bkt_end_date,
md.seq_num,
b.bucket_type,
b.bucket_index
from
msc_plan_buckets b,
msc_calendar_dates md
where b.plan_id=p_plan_id
and b.curr_flag=1
and md.calendar_code=l_calendar_code
and md.exception_set_id=-1
and b.bkt_start_date=md.calendar_date
) b,
(select
mw.week_start_date,
mw.next_date-1 week_end_date,
max(decode(md.seq_num, null, null, md.calendar_date)) week_last_work_date
from
msc_plan_buckets b,
msc_calendar_dates md,
msc_cal_week_start_dates mw
where b.plan_id=p_plan_id
and b.curr_flag=1
and md.calendar_date between b.bkt_start_date and b.bkt_end_date
and md.calendar_code=l_calendar_code
and md.exception_set_id=-1
and md.calendar_code=mw.calendar_code
and md.sr_instance_id=mw.sr_instance_id
and md.exception_set_id=mw.exception_set_id
and md.calendar_date between mw.week_start_date and mw.next_date-1
--and md.seq_num is not null
group by
mw.week_start_date,
mw.next_date
) w,
(select
mp.period_start_date,
mp.next_date-1 period_end_date,
max(decode(md.seq_num, null, null, md.calendar_date)) period_last_work_date
from
msc_plan_buckets b,
msc_calendar_dates md,
msc_period_start_dates mp
where b.plan_id=p_plan_id
and b.curr_flag=1
and md.calendar_date between b.bkt_start_date and b.bkt_end_date
and md.calendar_code=l_calendar_code
and md.exception_set_id=-1
and md.calendar_code=mp.calendar_code
and md.sr_instance_id=mp.sr_instance_id
and md.exception_set_id=mp.exception_set_id
and md.calendar_date between mp.period_start_date and mp.next_date-1
--and md.seq_num is not null
group by
mp.period_start_date,
mp.next_date
) p
where b.bkt_start_date between w.week_start_date and w.week_end_date
and w.week_start_date between p.period_start_date and p.period_end_date
),
m as (
select bkt_start_date plan_start_date
from msc_plan_buckets
where plan_id=p_plan_id
and curr_flag=1
and bucket_index=1
)
select
p_plan_id,
t.bkt_start_date,
t.bkt_end_date,
t.seq_num,
t.bucket_type,
t.bucket_index,
decode(tw.week_index, 1, m.plan_start_date, t.week_start_date) week_start_date,
t.week_end_date,
greatest(decode(tw.week_index, 1, m.plan_start_date, t.week_start_date), t.week_last_work_date) week_last_work_date,
decode(tp.period_index, 1, m.plan_start_date, t.period_start_date) period_start_date,
t.period_end_date,
greatest(decode(tp.period_index, 1, m.plan_start_date, t.period_start_date), t.period_last_work_date) period_last_work_date,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from t, m,
(select rownum week_index, week_start_date from (select distinct week_start_date from t order by 1)) tw,
(select rownum period_index, period_start_date from (select distinct period_start_date from t order by 1)) tp
where t.week_start_date=tw.week_start_date
and t.period_start_date=tp.period_start_date
union all
select
p_plan_id,
m.plan_start_date - 1 bkt_start_date,
m.plan_start_date - 1 bkt_end_date,
0 seq_num,
0 bucket_type,
0 bucket_index,
m.plan_start_date - 1 week_start_date,
m.plan_start_date - 1 week_end_date,
m.plan_start_date - 1 week_last_work_date,
m.plan_start_date - 1 period_start_date,
m.plan_start_date - 1 period_end_date,
m.plan_start_date - 1 period_last_work_date,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from m
order by bucket_index;
msc_phub_util.log('insert into msc_hp_col_dtls: '||sql%rowcount);
insert into msc_form_query(query_id, number1, char1,
created_by, creation_date, last_update_date, last_updated_by, last_update_login)
values (p_query_id, p_filter_type, trim(substr(s, 1, i - 1)),
fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.login_id);
insert into msc_form_query(query_id, number1, number2,
created_by, creation_date, last_update_date, last_updated_by, last_update_login)
values (p_query_id, p_filter_type, trim(substr(s, 1, i - 1)),
fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.login_id);
select msc_hp_query_s.nextval into l_query_id from dual;
select plan_id into l_plan_id
from msc_hp_row_dtls
where query_id=p_query_id and rownum=1;
delete from msc_matl_plan_data where query_id in (select query_id from msc_hp_row_dtls where plan_id=p_plan_id);
msc_phub_util.log('delete from msc_matl_plan_data: '||sql%rowcount);
delete from msc_res_plan_data where query_id in (select query_id from msc_hp_row_dtls where plan_id=p_plan_id);
msc_phub_util.log('delete from msc_res_plan_data: '||sql%rowcount);
delete from msc_hp_updates where query_id in (select query_id from msc_hp_row_dtls where plan_id=p_plan_id);
msc_phub_util.log('delete from msc_hp_updates: '||sql%rowcount);
delete from msc_res_plan_updates where query_id in (select query_id from msc_hp_row_dtls where plan_id=p_plan_id);
msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
delete from msc_hp_row_dtls where plan_id=p_plan_id;
msc_phub_util.log('delete from msc_hp_row_dtls: '||sql%rowcount);
delete from msc_hp_col_dtls where plan_id=p_plan_id;
msc_phub_util.log('delete from msc_hp_col_dtls: '||sql%rowcount);