[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_cat_filter_cnt
from msc_form_query
where query_id = p_query_id and number1 = msc_hp_util.ft_category;
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_hp_row_dtls(
query_id,
row_index,
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
base_item_id,
category_set_id,
sr_category_id,
organization_code,
category_name,
item_name,
data_flag,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
select
p_query_id,
(rank() over(order by o.organization_code, ic.category_name, i.item_name))-1 row_index,
i.plan_id,
i.sr_instance_id,
i.organization_id,
i.inventory_item_id,
i.base_item_id,
p_category_set_id,
ic.sr_category_id,
o.organization_code,
ic.category_name,
i.item_name,
1 data_flag,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from
msc_system_items i,
msc_item_categories ic,
msc_trading_partners o
where i.plan_id = p_plan_id
and i.sr_instance_id = o.sr_instance_id
and i.organization_id = o.sr_tp_id
and o.partner_type = 3
and i.sr_instance_id = ic.sr_instance_id(+)
and i.organization_id = ic.organization_id(+)
and i.inventory_item_id = ic.inventory_item_id(+)
and ic.category_set_id(+) = p_category_set_id
and (l_org_filter_cnt = 0 or o.organization_code in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_organization))
and (l_cat_filter_cnt = 0 or ic.category_name in (select char1 from msc_form_query where query_id = p_query_id and number1 = msc_hp_util.ft_category))
and (l_item_filter_cnt = 0 or i.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_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;
select min(b.bkt_start_date), max(b.bkt_end_date)
into l_pre_start_date, l_plan_cutoff_date
from msc_hp_row_dtls h, msc_hp_col_dtls b
where h.query_id = p_query_id
and h.plan_id = b.plan_id;
delete from msc_matl_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_matl_plan_data: '||sql%rowcount);
insert into msc_matl_plan_data(
query_id,
row_index,
analysis_date,
total_supply,
on_hand,
scheduled_receipts,
planned_order,
purchase_order,
requisition,
work_order,
total_demand,
forecast,
forecast_mds,
dependent_demand,
manual_demand,
sales_order,
safety_stock,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
select
p_query_id,
row_index,
analysis_date,
sum(nvl(on_hand,0) + nvl(planned_order,0) + nvl(other_supply,0)) total_supply,
sum(on_hand) on_hand,
sum(scheduled_receipts) scheduled_receipts,
sum(planned_order) planned_order,
sum(purchase_order) purchase_order,
sum(requisition) requisition,
sum(work_order) work_order,
sum(nvl(forecast,0) + nvl(forecast_mds,0) + nvl(dependent_demand,0) + nvl(manual_demand,0) +
nvl(sales_order,0) + nvl(other_demand,0)) total_demand,
sum(forecast) forecast,
sum(forecast_mds) forecast_mds,
sum(dependent_demand) dependent_demand,
sum(manual_demand) manual_demand,
sum(sales_order) sales_order,
sum(safety_stock) safety_stock,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from
(
-- supplies:scheduled_receipts
select
decode(p_summary, 1, -1, h.row_index) row_index,
b.bkt_start_date analysis_date,
to_number(null) on_hand,
sum(decode(ms.last_unit_completion_date, null,
nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate))
scheduled_receipts,
to_number(null) planned_order,
to_number(null) purchase_order,
to_number(null) requisition,
to_number(null) work_order,
to_number(null) other_supply,
to_number(null) forecast,
to_number(null) forecast_mds,
to_number(null) dependent_demand,
to_number(null) manual_demand,
to_number(null) sales_order,
to_number(null) other_demand,
to_number(null) safety_stock
from
msc_supplies ms,
msc_hp_col_dtls b,
msc_hp_row_dtls h
where h.query_id = p_query_id
and ms.plan_id = h.plan_id
and ms.sr_instance_id = h.sr_instance_id
and ms.organization_id = h.organization_id
and ms.inventory_item_id = h.inventory_item_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
and (h.base_item_id is not null or nvl(ms.disposition_status_type, 1) <> 2)
and ms.plan_id = b.plan_id
--and b.curr_flag = 1
and greatest(l_pre_start_date,
trunc(nvl(ms.firm_date, nvl(ms.old_schedule_date, ms.new_schedule_date))))
between b.bkt_start_date and b.bkt_end_date
and ms.order_type in (1,2,3,8,11,12,14,27,49,53,80)
group by
decode(p_summary, 1, -1, h.row_index),
b.bkt_start_date
union all
-- supplies: new_schedule_date
select
decode(p_summary, 1, -1, h.row_index) row_index,
b.bkt_start_date analysis_date,
sum(case when nvl(ms.item_type_value,1) = 1 and ms.order_type in (18) then
decode(ms.last_unit_completion_date, null,
nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
end) on_hand, -- apcc does not check part condistion
to_number(null) scheduled_receipts,
sum(case when ms.order_type in (5) then
decode(ms.last_unit_completion_date, null,
nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
end) planned_order,
sum(case when ms.order_type in (1) then
decode(ms.last_unit_completion_date, null,
nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
end) purchase_order,
sum(case when ms.order_type in (2) then
decode(ms.last_unit_completion_date, null,
nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
end) requisition,
sum(case when ms.order_type in (3) then
decode(ms.last_unit_completion_date, null,
nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
end) work_order,
sum(case when ms.order_type in (1,2,3,8,11,12,14,27,49,53,80) then
decode(ms.last_unit_completion_date, null,
nvl(ms.firm_quantity, ms.new_order_quantity), ms.daily_rate)
end) other_supply,
to_number(null) forecast,
to_number(null) forecast_mds,
to_number(null) dependent_demand,
to_number(null) manual_demand,
to_number(null) sales_order,
to_number(null) other_demand,
to_number(null) safety_stock
from
msc_supplies ms,
msc_hp_col_dtls b,
msc_hp_row_dtls h
where h.query_id = p_query_id
and ms.plan_id = h.plan_id
and ms.sr_instance_id = h.sr_instance_id
and ms.organization_id = h.organization_id
and ms.inventory_item_id = h.inventory_item_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
and (h.base_item_id is not null or nvl(ms.disposition_status_type, 1) <> 2)
and ms.plan_id = b.plan_id
--and b.curr_flag = 1
and trunc(nvl(ms.firm_date, ms.new_schedule_date)) between b.bkt_start_date and b.bkt_end_date
group by
decode(p_summary, 1, -1, h.row_index),
b.bkt_start_date
union all
-- demands
select
decode(p_summary, 1, -1, h.row_index) row_index,
b.bkt_start_date analysis_date,
to_number(null) on_hand,
to_number(null) scheduled_receipts,
to_number(null) planned_order,
to_number(null) purchase_order,
to_number(null) requisition,
to_number(null) work_order,
to_number(null) other_supply,
sum(case when origination_type in (29) then
decode(md.assembly_demand_comp_date, null,
nvl(md.probability,1) * nvl(md.firm_quantity, md.using_requirement_quantity),
nvl(md.probability,1) * md.daily_demand_rate) end)
/ decode(nvl(least(sum(nvl(md.probability,0)), 1), 1),
0, 1, nvl(least(sum(nvl(md.probability,0)), 1), 1))
forecast,
sum(case when origination_type in (7) then
decode(md.assembly_demand_comp_date, null,
nvl(md.probability,1) * nvl(md.firm_quantity, md.using_requirement_quantity),
nvl(md.probability,1) * md.daily_demand_rate) end)
/ decode(nvl(least(sum(nvl(md.probability,0)), 1), 1),
0, 1, nvl(least(sum(nvl(md.probability,0)), 1), 1))
forecast_mds,
sum(case when origination_type in (1,2,3,4,24,25) then
decode(md.assembly_demand_comp_date, null,
nvl(md.firm_quantity, md.using_requirement_quantity), md.daily_demand_rate)
end) dependent_demand,
sum(case when origination_type in (8) then
decode(md.assembly_demand_comp_date, null,
nvl(md.firm_quantity, md.using_requirement_quantity), md.daily_demand_rate)
end) manual_demand,
sum(case when origination_type in (6,30) then
decode(md.assembly_demand_comp_date, null,
nvl(md.firm_quantity, md.using_requirement_quantity), md.daily_demand_rate)
end) sales_order,
sum(case when origination_type not in (1,2,3,4,6,7,8,24,25,29,30) then
decode(md.assembly_demand_comp_date, null,
nvl(md.firm_quantity, md.using_requirement_quantity), md.daily_demand_rate)
end) other_demand,
to_number(null) safety_stock
from
msc_demands md,
msc_hp_col_dtls b,
msc_hp_row_dtls h
where h.query_id = p_query_id
and md.plan_id = h.plan_id
and md.sr_instance_id = h.sr_instance_id
and md.organization_id = h.organization_id
and md.inventory_item_id = h.inventory_item_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
and md.plan_id = b.plan_id
--and b.curr_flag = 1
and trunc(nvl(md.firm_date, md.using_assembly_demand_date)) between b.bkt_start_date and b.bkt_end_date
group by
decode(p_summary, 1, -1, h.row_index),
b.bkt_start_date
union all
-- safety_stock
select
f.row_index,
min(b.bkt_start_date) analysis_date,
to_number(null) on_hand,
to_number(null) scheduled_receipts,
to_number(null) planned_order,
to_number(null) purchase_order,
to_number(null) requisition,
to_number(null) work_order,
to_number(null) other_supply,
to_number(null) forecast,
to_number(null) forecast_mds,
to_number(null) dependent_demand,
to_number(null) manual_demand,
to_number(null) sales_order,
to_number(null) other_demand,
f.safety_stock_quantity
from
(select
row_index,
plan_id,
period_start_date effective_date,
nvl(lead(period_start_date-1) over(partition by row_index order by period_start_date), l_plan_cutoff_date) expiry_date,
safety_stock_quantity
from
(select
decode(p_summary, 1, -1, h.row_index) row_index,
ss.plan_id,
ss.period_start_date,
sum(ss.safety_stock_quantity) safety_stock_quantity
from
msc_safety_stocks ss,
msc_hp_row_dtls h
where h.query_id = p_query_id
and ss.plan_id = h.plan_id
and ss.sr_instance_id = h.sr_instance_id
and ss.organization_id = h.organization_id
and ss.inventory_item_id = h.inventory_item_id
and h.data_flag = decode(p_summary, 1, h.data_flag, 3)
group by
decode(p_summary, 1, -1, h.row_index),
ss.plan_id,
ss.period_start_date
)
) f,
msc_hp_col_dtls b
where f.plan_id = b.plan_id
and b.bkt_start_date between f.effective_date and f.expiry_date
group by
f.row_index,
f.effective_date,
f.expiry_date,
f.safety_stock_quantity
)
group by
row_index,
analysis_date;
msc_phub_util.log('insert into msc_matl_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_matl_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_page_size-1
and data_flag = 1;
select
h.row_index,
h.plan_id,
h.sr_instance_id,
h.organization_id,
h.inventory_item_id,
decode(u.column_name, 'planned_order', 5) supply_type,
decode(u.column_name, 'forecast', 29, 'forecast_mds', 7, 'manual_demand', 8) demand_type,
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,
b.bucket_type,
u.column_name,
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_MATL_PLAN_TYPE'
and u.new_value >= 0
and nvl(u.process_status, 1) = 1;
select
firm_planned_type,
firm_date,
firm_quantity,
status,
applied,
last_update_date,
last_updated_by,
last_update_login
from msc_supplies
where plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and trunc(nvl(firm_date, new_schedule_date)) between p_start_date and p_end_date
and order_type = p_supply_type
and last_unit_completion_date is null
for update nowait;
select
firm_date,
firm_quantity,
status,
applied,
last_update_date,
last_updated_by,
last_update_login
from msc_demands
where plan_id = p_plan_id
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and trunc(nvl(firm_date, using_assembly_demand_date)) between p_start_date and p_end_date
and origination_type = p_demand_type
and assembly_demand_comp_date is null
for update nowait;
select
nvl(sum(decode(last_unit_completion_date, null,
nvl(firm_quantity, new_order_quantity), null)), 0) old_value,
nvl(sum(decode(last_unit_completion_date, null, null, daily_rate)), 0) fixed,
nvl(sum(decode(last_unit_completion_date, null, 1, 0)), 0) cnt
into l_old_value, l_fixed, l_cnt
from msc_supplies
where plan_id = ru.plan_id
and sr_instance_id = ru.sr_instance_id
and organization_id = ru.organization_id
and inventory_item_id = ru.inventory_item_id
and trunc(nvl(firm_date, new_schedule_date)) between ru.start_date and ru.end_date
and order_type = ru.supply_type;
update msc_supplies set
firm_planned_type = 1,
firm_date = nvl(firm_date, new_schedule_date),
firm_quantity = decode(l_old_value, 0, (ru.new_value-l_fixed)/l_cnt,
nvl(firm_quantity, new_order_quantity)*(ru.new_value-l_fixed)/l_old_value),
status = 0,
applied = 2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where current of c1;
msc_phub_util.log('update msc_supplies: '||l_n);
insert into msc_supplies(
plan_id,
transaction_id,
sr_instance_id,
organization_id,
inventory_item_id,
order_type,
new_schedule_date,
firm_planned_type,
firm_date,
firm_quantity,
status,
applied,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
values(
ru.plan_id,
msc_supplies_s.nextval,
ru.sr_instance_id,
ru.organization_id,
ru.inventory_item_id,
ru.supply_type,
ru.last_work_date,
1,
ru.last_work_date,
(ru.new_value-l_fixed),
0,
2,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id);
msc_phub_util.log('insert into msc_supplies: '||sql%rowcount);
select
nvl(sum(decode(assembly_demand_comp_date, null,
nvl(firm_quantity, using_requirement_quantity), null)), 0) old_value,
nvl(sum(decode(assembly_demand_comp_date, null, null, daily_demand_rate)), 0) fixed,
nvl(sum(decode(assembly_demand_comp_date, null, 1, 0)), 0) cnt
into l_old_value, l_fixed, l_cnt
from msc_demands
where plan_id = ru.plan_id
and sr_instance_id = ru.sr_instance_id
and organization_id = ru.organization_id
and inventory_item_id = ru.inventory_item_id
and trunc(nvl(firm_date, using_assembly_demand_date)) between ru.start_date and ru.end_date
and origination_type = ru.demand_type;
update msc_demands set
firm_date = nvl(firm_date, using_assembly_demand_date),
firm_quantity = decode(l_old_value, 0, (ru.new_value-l_fixed)/l_cnt,
nvl(firm_quantity, using_requirement_quantity)*(ru.new_value-l_fixed)/l_old_value),
org_firm_flag = 1,
status = 0,
applied = 2,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where current of c2;
msc_phub_util.log('update msc_demands: '||l_n);
insert into msc_demands(
plan_id,
demand_id,
sr_instance_id,
organization_id,
inventory_item_id,
origination_type,
using_requirement_quantity,
demand_type,
using_assembly_item_id,
using_assembly_demand_date,
firm_date,
firm_quantity,
demand_priority,
org_firm_flag,
status,
applied,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
values(
ru.plan_id,
msc_demands_s.nextval,
ru.sr_instance_id,
ru.organization_id,
ru.inventory_item_id,
ru.demand_type,
0,
1,
ru.inventory_item_id,
ru.last_work_date,
ru.last_work_date,
(ru.new_value-l_fixed),
1,
1,
0,
2,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id);
msc_phub_util.log('insert into msc_demands: '||sql%rowcount);
update msc_hp_row_dtls
set data_flag = 3
where query_id = p_query_id
and row_index in (select row_index from msc_hp_updates where query_id = p_query_id);
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_matl_plan_data where query_id = p_query_id;
msc_phub_util.log('delete from msc_matl_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);