[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'MSC_APCC_ITEM_D' bulk collect into l_tables from dual;
select upper('msc_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type not in (4);
select upper('msc_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type in (1,2,3,7,8,9,11,15,16);
select upper('msc_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type in (5);
select upper('msc_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type not in (4,5);
select upper('msc_st_'||entity_name||'_f') bulk collect into r from table(meta_info);
select upper('msc_st_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type in (4) and p_include_ods=sys_yes;
select upper('msc_st_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type in (2,7,9,10,11,12,14);
select plan_run_id bulk collect into r
from msc_plan_runs
where (plan_name=p_plan_name and plan_run_id=nvl(p_plan_run_id,plan_run_id))
and archive_flag=1
union
select p_plan_run_id from dual where p_plan_run_id is not null;
select meaning into l_meaning
from mfg_lookups
where lookup_type='MSC_SCN_PLAN_TYPES'
and lookup_code=p_plan_type;
select count(*)
into l_n
from all_tab_partitions
where table_owner=msc_phub_util.msc_schema
and table_name=p_tables(i)
and partition_name=l_partition_name;
select
das.scenario_id plan_id,
substr(das.scenario_name, 1, 50) plan_name,
substr(das.scenario_name, 1, 100) plan_description,
to_number(10) plan_type,
decode(das.sr_instance_id, -23453, o.sr_instance_id, das.sr_instance_id) sr_instance_id,
decode(das.organization_id, -23453, o.sr_tp_id, das.organization_id) organization_id,
tq.from_date plan_start_date,
tq.until_date plan_cutoff_date,
nvl(dsr.last_update_date, sysdate) plan_completion_date,
null publisher
into l_pi
from
msd_dp_ascp_scenarios_v das,
msd_dp_scenario_revisions dsr,
msd_dem_transfer_query tq,
(select mtp.sr_instance_id, mtp.sr_tp_id
from msc_trading_partners mtp, msd_dem_app_instance_orgs daio
where daio.organization_id=nvl(fnd_profile.value('MSD_DEM_MASTER_ORG'), -23453)
and mtp.organization_code=daio.organization_code
and mtp.partner_type=3
union all select to_number(-23453), to_number(-23453) from dual) o
where das.demand_plan_name=substr(tq.query_name(+), 1, 30)
and das.scenario_id=p_plan_id
and das.scenario_id=dsr.scenario_id(+)
and das.last_revision=dsr.revision(+)
and rownum=1;
select
p.plan_id,
decode(nvl(copy_plan_id,-1), -1, compile_designator,
(select compile_designator from msc_plans where plan_id=p.copy_plan_id)) plan_name,
p.description plan_description,
(case when p.curr_plan_type in (101,102,103,105) then 101
when p.plan_type in (1,2,3) then 1
else nvl(p.plan_type, -1) end) plan_type,
p.sr_instance_id,
p.organization_id,
trunc(nvl(b.bkt_start_date, p.curr_start_date)) plan_start_date,
trunc(nvl(b.bkt_end_date, p.curr_cutoff_date)) plan_cutoff_date,
nvl(p.plan_completion_date, sysdate) plan_completion_date,
null publisher
into l_pi
from msc_plans p,
(select plan_id, min(bkt_start_date) bkt_start_date, max(bkt_end_date) bkt_end_date
from msc_plan_buckets
where curr_flag=1
group by plan_id
) b
where p.plan_id=p_plan_id
and p.plan_id=b.plan_id(+);
select plan_name into l_plan_name from msc_plan_runs where plan_run_id=p_plan_run_id;
select max(plan_run_id)
into l_prev_plan_run_id
from msc_plan_runs
where plan_name=l_plan_name
and plan_run_id
execute immediate 'select sysdate from dual'||l_suffix;
' select user_id into l_user_id'||
' from '||l_apps_schema||'.fnd_user'||l_suffix||
' where user_name=fnd_global.user_name;'||
' select responsibility_id into l_responsibility_id'||
' from '||l_apps_schema||'.fnd_responsibility'||l_suffix||
' where responsibility_key=('||
' select responsibility_key from fnd_responsibility'||
' where responsibility_id=fnd_global.resp_id);'||
' select application_id into l_application_id'||
' from '||l_apps_schema||'.fnd_application'||l_suffix||
' where application_short_name=fnd_global.application_short_name;'||
select value into l_db_name from v$parameter where name='db_name';
select
plan_id,
plan_name,
plan_description,
plan_type,
sr_instance_id,
organization_id,
plan_start_date,
plan_cutoff_date,
plan_completion_date,
publisher
into l_pi
from msc_plan_runs
where plan_id=p_plan_id and plan_run_id=p_plan_run_id;
select nvl(display_kpi,1)
into l_display_kpi
from msc_plans
where plan_id=l_pi.plan_id;
select count(1) into l_n
from msc_plan_runs
where plan_name=l_pi.plan_name
and archive_flag=1
and local_archive_flag=2;
select plan_type into l_plan_type
from msc_plan_runs
where plan_name=l_pi.plan_name
and archive_flag=1
and plan_type<>l_pi.plan_type
and rownum=1;
update msc_plan_runs set
archive_flag=decode(archive_flag, 3, 2, archive_flag),
last_update_date = sysdate
where plan_run_id=l_plan_run_id;
cursor c is select column_value plan_run_id from table(l_purge_list);
update msc_plan_runs set
planning_hub_flag = sys_no,
last_run_flag = sys_no,
archive_flag = sys_no,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_id = fnd_global.conc_program_id,
program_login_id = fnd_global.conc_login_id,
program_application_id = fnd_global.prog_appl_id,
request_id = fnd_global.conc_request_id
where plan_run_id = r.plan_run_id;
select plan_id, plan_name
into l_plan_id, l_plan_name
from msc_plan_runs
where plan_run_id in (select column_value from table(l_purge_list))
and rownum=1;
select max(plan_run_id)
into l_last_plan_run_id
from msc_plan_runs
where plan_name=l_plan_name
and archive_flag=1;
update msc_plan_runs set
last_run_flag = 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_id = fnd_global.conc_program_id,
program_login_id = fnd_global.conc_login_id,
program_application_id = fnd_global.prog_appl_id,
request_id = fnd_global.conc_request_id
where plan_run_id=l_last_plan_run_id;
delete from msc_apcc_config;
insert into msc_apcc_config (
cal_code,
period_set_name,
reporting_currency,
category_set_id1,
category_set_id2,
category_set_id3,
pg_category_set_id,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
fnd_profile.value('MSC_HUB_CAL_CODE') cal_code,
fnd_profile.value('MSC_HUB_PERIOD_SET_NAME') period_set_name,
fnd_profile.value('MSC_HUB_CUR_CODE_RPT') reporting_currency,
fnd_profile.value('MSC_HUB_CAT_SET_ID_1') category_set_id1,
fnd_profile.value('MSC_HUB_CAT_SET_ID_2') category_set_id2,
fnd_profile.value('MSC_HUB_CAT_SET_ID_3') category_set_id3,
fnd_profile.value('MSC_APCC_PEGGING_CAT_SET') pg_category_set_id,
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 dual;
select plan_name, sr_instance_id, plan_type, temp_transfer_id
into l_plan_name, l_sr_instance_id, l_plan_type, l_transfer_id
from msc_plan_runs
where plan_run_id=p_plan_run_id;
update msc_plan_runs set
last_run_flag=sys_no,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where plan_name = l_plan_name
and plan_run_id < p_plan_run_id
and (p_plan_id<>-1 or sr_instance_id=l_sr_instance_id);
update msc_plan_runs set
last_run_flag=sys_yes,
planning_hub_flag=sys_yes,
end_date=sysdate,
archive_flag=p_archive_flag
where plan_id=p_plan_id and plan_run_id=p_plan_run_id;
update msc_plan_runs set end_date=sysdate
where plan_id=p_plan_id and plan_run_id=p_plan_run_id;
select msc_apcc_upload_s.nextval into l_transfer_id from dual;
select msc_plan_runs_s.nextval into l_plan_run_id from dual;
select 1 into l_error
from msc_plan_runs
where plan_name=p_pi.plan_name
and nvl(local_archive_flag,1) <> nvl(p_local_archive_flag,1)
and rownum=1;
select nvl(max(plan_run_version), -1) + 1 into l_plan_version from msc_plan_runs where plan_name=p_pi.plan_name;
insert into msc_plan_runs (
plan_id, plan_run_id, plan_run_name, sr_instance_id, organization_id,
plan_name, plan_type, plan_description, plan_run_version, scenario_name,
plan_start_date, plan_cutoff_date, plan_completion_date, publisher,
start_date, end_date, last_run_flag,
planning_hub_flag, archive_flag, local_archive_flag, temp_transfer_id,
created_by, creation_date, last_update_date, last_updated_by, last_update_login,
program_id, program_login_id, program_application_id, request_id
)
values (
p_pi.plan_id, l_plan_run_id, l_plan_run_name,
p_pi.sr_instance_id, p_pi.organization_id,
p_pi.plan_name, p_pi.plan_type, p_pi.plan_description,
l_plan_version, p_scenario_name,
p_pi.plan_start_date, p_pi.plan_cutoff_date,
p_pi.plan_completion_date, p_pi.publisher,
sysdate, null, sys_no, sys_no, 0, p_local_archive_flag, l_transfer_id,
fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.user_id,
fnd_global.conc_program_id, fnd_global.conc_login_id, fnd_global.prog_appl_id, fnd_global.conc_request_id
);
select plan_type into l_plan_type
from msc_plan_runs
where plan_id=p_plan_id and rownum=1;
insert into msc_apcc_item_d (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
sr_category_id1,
sr_category_id2,
sr_category_id3,
pegging_sr_category_id,
latest_item_id,
vmi_flag,
item_name,
description,
abc_class,
product_family_id,
average_daily_demand,
average_discount,
base_item_id,
build_in_wip_flag,
buyer_name,
fixed_lead_time,
list_price,
max_minmax_quantity,
min_minmax_quantity,
minimum_order_quantity,
mrp_planning_code,
planner_code,
planning_make_buy_code,
postprocessing_lead_time,
preprocessing_lead_time,
purchasing_enabled_flag,
repetitive_type,
safety_stock_code,
standard_cost,
unit_volume,
unit_weight,
uom_code,
variable_lead_time,
volume_uom,
weight_uom,
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,
b.sr_instance_id,
to_number(-23453) organization_id,
-b.sr_category_id inventory_item_id,
nvl(c1.sr_category_id1, -23453) sr_category_id1,
nvl(c2.sr_category_id2, -23453) sr_category_id2,
nvl(c3.sr_category_id3, -23453) sr_category_id3,
nvl(c4.pegging_sr_category_id, -23453) pegging_sr_category_id,
to_number(-23453) latest_item_id,
null vmi_flag,
null item_name,
null description,
to_number(-23453) abc_class,
to_number(-23453) product_family_id,
null average_daily_demand,
null average_discount,
null base_item_id,
null build_in_wip_flag,
null buyer_name,
null fixed_lead_time,
null list_price,
null max_minmax_quantity,
null min_minmax_quantity,
null minimum_order_quantity,
null mrp_planning_code,
fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED') planner_code,
null planning_make_buy_code,
null postprocessing_lead_time,
null preprocessing_lead_time,
null purchasing_enabled_flag,
null repetitive_type,
null safety_stock_code,
null standard_cost,
null unit_volume,
null unit_weight,
null uom_code,
null variable_lead_time,
null volume_uom,
null weight_uom,
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
(select distinct sr_instance_id, sr_category_id1 sr_category_id
from msc_apcc_item_d
where plan_id=p_plan_id and sr_category_id1>=0
union
select distinct sr_instance_id, sr_category_id2 sr_category_id
from msc_apcc_item_d
where plan_id=p_plan_id and sr_category_id2>=0
union
select distinct sr_instance_id, sr_category_id3 sr_category_id
from msc_apcc_item_d
where plan_id=p_plan_id and sr_category_id3>=0
union
select distinct sr_instance_id, pegging_sr_category_id sr_category_id
from msc_apcc_item_d
where plan_id=p_plan_id and pegging_sr_category_id>=0
) b,
(select distinct sr_instance_id, sr_category_id1
from msc_apcc_item_d
where plan_id=p_plan_id
) c1,
(select distinct sr_instance_id, sr_category_id2
from msc_apcc_item_d
where plan_id=p_plan_id
) c2,
(select distinct sr_instance_id, sr_category_id3
from msc_apcc_item_d
where plan_id=p_plan_id
) c3,
(select distinct sr_instance_id, pegging_sr_category_id
from msc_apcc_item_d
where plan_id=p_plan_id
) c4,
msc_plans p
where b.sr_category_id=c1.sr_category_id1(+)
and b.sr_category_id=c2.sr_category_id2(+)
and b.sr_category_id=c3.sr_category_id3(+)
and b.sr_category_id=c4.pegging_sr_category_id(+)
and b.sr_instance_id=c1.sr_instance_id(+)
and b.sr_instance_id=c2.sr_instance_id(+)
and b.sr_instance_id=c3.sr_instance_id(+)
and b.sr_instance_id=c4.sr_instance_id(+)
and p.plan_id=p_plan_id
and l_plan_type in (4)
union all
select
p_plan_id,
to_number(-23453) sr_instance_id,
to_number(-23453) organization_id,
to_number(-23453) inventory_item_id,
to_number(-23453) sr_category_id1,
to_number(-23453) sr_category_id2,
to_number(-23453) sr_category_id3,
to_number(-23453) pegging_sr_category_id,
to_number(-23453) latest_item_id,
null vmi_flag,
null item_name,
null description,
to_number(-23453) abc_class,
to_number(-23453) product_family_id,
null average_daily_demand,
null average_discount,
null base_item_id,
null build_in_wip_flag,
null buyer_name,
null fixed_lead_time,
null list_price,
null max_minmax_quantity,
null min_minmax_quantity,
null minimum_order_quantity,
null mrp_planning_code,
fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED') planner_code,
null planning_make_buy_code,
null postprocessing_lead_time,
null preprocessing_lead_time,
null purchasing_enabled_flag,
null repetitive_type,
null safety_stock_code,
null standard_cost,
null unit_volume,
null unit_weight,
null uom_code,
null variable_lead_time,
null volume_uom,
null weight_uom,
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 dual;
msc_phub_util.log('msc_phub_pkg.build_null_items: insert='||sql%rowcount);
insert into msc_apcc_item_d (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
sr_category_id1,
sr_category_id2,
sr_category_id3,
pegging_sr_category_id,
latest_item_id,
vmi_flag,
item_name,
description,
abc_class,
product_family_id,
average_daily_demand,
average_discount,
base_item_id,
build_in_wip_flag,
buyer_name,
fixed_lead_time,
list_price,
max_minmax_quantity,
min_minmax_quantity,
minimum_order_quantity,
mrp_planning_code,
planner_code,
planning_make_buy_code,
postprocessing_lead_time,
preprocessing_lead_time,
purchasing_enabled_flag,
repetitive_type,
safety_stock_code,
standard_cost,
unit_volume,
unit_weight,
uom_code,
variable_lead_time,
volume_uom,
weight_uom,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id
)
select
i.plan_id,
i.sr_instance_id,
i.organization_id,
i.inventory_item_id,
nvl(ic1.sr_category_id, -23453) sr_category_id1,
nvl(ic2.sr_category_id, -23453) sr_category_id2,
nvl(ic3.sr_category_id, -23453) sr_category_id3,
nvl(ick.sr_category_id, -23453) pegging_sr_category_id,
nvl(s.highest_item_id, i.inventory_item_id) latest_item_id,
mis.vmi_flag,
i.item_name,
i.description,
i.abc_class,
i.product_family_id,
i.average_daily_demand,
i.average_discount,
i.base_item_id,
i.build_in_wip_flag,
i.buyer_name,
i.fixed_lead_time,
i.list_price,
i.max_minmax_quantity,
i.min_minmax_quantity,
i.minimum_order_quantity,
i.mrp_planning_code,
/* bug 9919442 - if no planner code set to 'Unassigned' */
/* i.planner_code, */
nvl(i.planner_code, fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED')) planner_code,
i.planning_make_buy_code,
i.postprocessing_lead_time,
i.preprocessing_lead_time,
i.purchasing_enabled_flag,
i.repetitive_type,
i.safety_stock_code,
i.standard_cost,
i.unit_volume,
i.unit_weight,
i.uom_code,
i.variable_lead_time,
i.volume_uom,
i.weight_uom,
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_system_items i,
(select sr_instance_id, organization_id, inventory_item_id, sr_category_id
from msc_phub_item_categories_mv
where category_set_id=fnd_profile.value('MSC_HUB_CAT_SET_ID_1')) ic1,
(select sr_instance_id, organization_id, inventory_item_id, sr_category_id
from msc_phub_item_categories_mv
where category_set_id=fnd_profile.value('MSC_HUB_CAT_SET_ID_2')) ic2,
(select sr_instance_id, organization_id, inventory_item_id, sr_category_id
from msc_phub_item_categories_mv
where category_set_id=fnd_profile.value('MSC_HUB_CAT_SET_ID_3')) ic3,
(select sr_instance_id, organization_id, inventory_item_id, sr_category_id
from msc_phub_item_categories_mv
where nvl(fnd_profile.value('MSC_APCC_END_ITEM_ENABLED'), 2)<2
and category_set_id=fnd_profile.value('MSC_APCC_PEGGING_CAT_SET')) ick,
(select distinct lower_item_id,
first_value(highest_item_id) over(partition by lower_item_id) highest_item_id
from
(select lower_item_id, highest_item_id
from msc_item_substitutes
where plan_id=p_plan_id
and relationship_type=8
and inferred_flag=2
and forward_rule=1
and sysdate between effective_date and nvl(disable_date, sysdate)
union
select highest_item_id, highest_item_id
from msc_item_substitutes
where plan_id=p_plan_id
and relationship_type=8
and inferred_flag=2
and forward_rule=1
and sysdate between effective_date and nvl(disable_date, sysdate)
)
) s,
(select distinct sr_instance_id, organization_id, inventory_item_id, to_number(1) vmi_flag
from msc_item_suppliers
where plan_id=p_plan_id
and vmi_flag=1
) mis
where i.plan_id=p_plan_id
and i.organization_id>0
and i.sr_instance_id=ic1.sr_instance_id(+)
and i.organization_id=ic1.organization_id(+)
and i.inventory_item_id=ic1.inventory_item_id(+)
and i.sr_instance_id=ic2.sr_instance_id(+)
and i.organization_id=ic2.organization_id(+)
and i.inventory_item_id=ic2.inventory_item_id(+)
and i.sr_instance_id=ic3.sr_instance_id(+)
and i.organization_id=ic3.organization_id(+)
and i.inventory_item_id=ic3.inventory_item_id(+)
and i.sr_instance_id=ick.sr_instance_id(+)
and i.organization_id=ick.organization_id(+)
and i.inventory_item_id=ick.inventory_item_id(+)
and i.inventory_item_id=s.lower_item_id(+)
and nvl(i.new_plan_id,-1)=-1 -- quick workaround to avoid ORA-00001, still doesn't fix RP
and i.sr_instance_id = mis.sr_instance_id(+)
and i.organization_id = mis.organization_id(+)
and i.inventory_item_id = mis.inventory_item_id(+);
msc_phub_util.log('msc_phub_pkg.build_items_from_pds: insert='||sql%rowcount);
' insert into msc_hub_query ('||
' query_id, number3, number4, number5,'||
' created_by, creation_date, last_updated_by, last_update_date'||
' )'||
' select distinct'||
' :p_query_id, '||l_item_columns||', inventory_item_id,'||
' fnd_global.user_id, sysdate, fnd_global.user_id, sysdate'||
' from '||l_table;
cursor c is select fact_type, item_dim from table(meta_info) where item_dim in (1,2) and fact_type<>4;
select msc_hub_query_s.nextval into l_qid_plan_item from dual;
delete from msc_apcc_item_d d
where d.plan_id=p_plan_id
and exists (
select 1 from msc_hub_query q
where q.query_id=l_qid_plan_item
and q.number3=d.sr_instance_id
and q.number4=d.organization_id
and q.number5=d.inventory_item_id);
msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: delete='||sql%rowcount);
delete from msc_apcc_item_d d
where d.plan_id=p_plan_id
and d.inventory_item_id<0;
msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: delete='||sql%rowcount);
insert into msc_apcc_item_d (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
sr_category_id1,
sr_category_id2,
sr_category_id3,
pegging_sr_category_id,
latest_item_id,
vmi_flag,
item_name,
description,
abc_class,
product_family_id,
average_daily_demand,
average_discount,
base_item_id,
build_in_wip_flag,
buyer_name,
fixed_lead_time,
list_price,
max_minmax_quantity,
min_minmax_quantity,
minimum_order_quantity,
mrp_planning_code,
planner_code,
planning_make_buy_code,
postprocessing_lead_time,
preprocessing_lead_time,
purchasing_enabled_flag,
repetitive_type,
safety_stock_code,
standard_cost,
unit_volume,
unit_weight,
uom_code,
variable_lead_time,
volume_uom,
weight_uom,
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,
d.sr_instance_id,
d.organization_id,
d.inventory_item_id,
d.sr_category_id1,
d.sr_category_id2,
d.sr_category_id3,
d.pegging_sr_category_id,
to_number(-23453) latest_item_id,
d.vmi_flag,
d.item_name,
d.description,
d.abc_class,
d.product_family_id,
d.average_daily_demand,
d.average_discount,
d.base_item_id,
d.build_in_wip_flag,
d.buyer_name,
d.fixed_lead_time,
d.list_price,
d.max_minmax_quantity,
d.min_minmax_quantity,
d.minimum_order_quantity,
d.mrp_planning_code,
/* bug 9919442 - if no planner code set to 'Unassigned' */
/* d.planner_code, */
nvl(d.planner_code, fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED')) planner_code,
d.planning_make_buy_code,
d.postprocessing_lead_time,
d.preprocessing_lead_time,
d.purchasing_enabled_flag,
d.repetitive_type,
d.safety_stock_code,
d.standard_cost,
d.unit_volume,
d.unit_weight,
d.uom_code,
d.variable_lead_time,
d.volume_uom,
d.weight_uom,
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_apcc_item_d d,
(select distinct
number3 sr_instance_id,
number4 organization_id,
number5 inventory_item_id
from msc_hub_query where query_id=l_qid_plan_item
and not (number3=-23453 and number4=-23453 and number5=-23453)) q
where d.sr_instance_id=q.sr_instance_id
and d.organization_id=q.organization_id
and d.inventory_item_id=q.inventory_item_id
and d.plan_id=-1;
msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: insert='||sql%rowcount);
select compile_designator, description
into l_pi.plan_name, l_pi.plan_description
from msc_plans
where plan_id=-1;
select organization_id
into l_pi.organization_id
from msc_instance_orgs
where rownum=1 and sr_instance_id=p_sr_instance_id;
select lcid
into l_lcid
from msc_apps_instances
where instance_id=p_sr_instance_id;
delete from msc_plan_buckets
where plan_id=-1;
insert into msc_plan_buckets (
plan_id,
organization_id,
sr_instance_id,
bucket_index,
curr_flag,
bkt_start_date,
bkt_end_date,
days_in_bkt,
bucket_type,
last_update_date,
last_updated_by,
creation_date,
created_by
)
values (
con_ods_plan_id,
l_pi.organization_id,
p_sr_instance_id,
v_counter,
1, --curr_flag
l_pi.plan_start_date + v_counter - 1,
l_pi.plan_start_date + v_counter - 1/86400,
1, --days_in_bkt
1, --bucket_type
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id
);
delete from msc_plan_organizations
where plan_id=-1;
insert into msc_plan_organizations(
plan_id,
organization_id,
sr_instance_id,
organization_code,
net_wip,
net_reservations,
net_purchasing,
plan_safety_stock,
plan_level,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
select con_ods_plan_id,
mio.organization_id,
mio.sr_instance_id,
mtp.organization_code,
2, --net_wip
2, --net_reservations
2, --net_purchasing
2, --plan_safety_stock
2, --plan_level
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
from msc_instance_orgs mio,
msc_trading_partners mtp
where mio.sr_instance_id = p_sr_instance_id
and mio.sr_instance_id = mtp.sr_instance_id
and mio.organization_id = mtp.sr_tp_id
and mtp.partner_type = 3;
select plan_run_id, plan_run_name
into l_plan_run_id, l_plan_run_name
from msc_plan_runs
where plan_id = con_ods_plan_id
and sr_instance_id=p_sr_instance_id
and archive_flag=1;
update msc_plan_runs set
refresh_mode = p_refresh_mode,
plan_type = l_pi.plan_type,
sr_instance_id = p_sr_instance_id,
organization_id = l_pi.organization_id,
plan_start_date = l_pi.plan_start_date,
plan_cutoff_date = l_pi.plan_cutoff_date,
temp_transfer_id = l_transfer_id,
plan_completion_date = sysdate,
plan_run_name = plan_name||
to_char(plan_completion_date, ' MM/DD')||
'('||plan_run_version||')'||
(case when scenario_name is not null then ' ['||scenario_name||']' end),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
program_id = fnd_global.conc_program_id,
program_login_id = fnd_global.conc_login_id,
program_application_id = fnd_global.prog_appl_id,
request_id = fnd_global.conc_request_id
where plan_run_id = l_plan_run_id;
select instance_code into l_instance_code
from msc_apps_instances where instance_id=p_sr_instance_id;
update msc_plan_runs set lcid = l_lcid
where plan_run_id=l_plan_run_id;
select count(*) into l_n from msc_demands_f where customer_id>0 and region_id>0 and rownum=1;
msc_phub_util.log('update msc_demands_f set region_id=-23453 where customer_id>0');
update msc_demands_f set region_id=-23453 where customer_id>0;
select count(*) into l_n from msc_demands_cum_f where customer_id>0 and region_id>0 and rownum=1;
msc_phub_util.log('update msc_demands_cum_f set region_id=-23453 where customer_id>0');
update msc_demands_cum_f set region_id=-23453 where customer_id>0;
select count(*) into l_n from msc_demantra_f where customer_id>0 and region_id>0 and rownum=1;
msc_phub_util.log('update msc_demantra_f set region_id=-23453 where customer_id>0');
update msc_demantra_f set region_id=-23453 where customer_id>0;
select count(*) into l_n from msc_demantra_ods_f where customer_id>0 and region_id>0 and rownum=1;
msc_phub_util.log('update msc_demantra_ods_f set region_id=-23453 where customer_id>0');
update msc_demantra_ods_f set region_id=-23453 where customer_id>0;
select count(*) into l_n from msc_costs_f where customer_id>0 and customer_region_id>0 and rownum=1;
msc_phub_util.log('update msc_costs_f set customer_region_id=-23453 where customer_id>0');
update msc_costs_f set customer_region_id=-23453 where customer_id>0;
select count(*) into l_n from msc_exceptions_f where customer_id>0 and customer_region_id>0 and rownum=1;
msc_phub_util.log('update msc_exceptions_f set customer_region_id=-23453 where customer_id>0');
update msc_exceptions_f set customer_region_id=-23453 where customer_id>0;
select table_name, partition_name,
to_number(substr(partition_name, length(p_table)-2)) plan_run_id
from all_tab_partitions
where table_owner=l_msc_schema
and table_name=p_table
and to_number(substr(partition_name, length(p_table)-2)) in
(select plan_run_id from msc_plan_runs where archive_flag=2)
order by 1;
' select distinct plan_id'||
' from msc_apcc_item_d_bak'||
' where plan_id>0'||
' order by 1';
' insert into msc_apcc_item_d ('||
' plan_id,'||
' sr_instance_id,'||
' organization_id,'||
' inventory_item_id,'||
' sr_category_id1,'||
' sr_category_id2,'||
' sr_category_id3,'||
' pegging_sr_category_id,'||
' latest_item_id,'||
' vmi_flag,'||
' item_name,'||
' description,'||
' abc_class,'||
' product_family_id,'||
' average_daily_demand,'||
' average_discount,'||
' base_item_id,'||
' build_in_wip_flag,'||
' buyer_name,'||
' fixed_lead_time,'||
' list_price,'||
' max_minmax_quantity,'||
' min_minmax_quantity,'||
' minimum_order_quantity,'||
' mrp_planning_code,'||
' planner_code,'||
' planning_make_buy_code,'||
' postprocessing_lead_time,'||
' preprocessing_lead_time,'||
' purchasing_enabled_flag,'||
' repetitive_type,'||
' safety_stock_code,'||
' standard_cost,'||
' unit_volume,'||
' unit_weight,'||
' uom_code,'||
' variable_lead_time,'||
' volume_uom,'||
' weight_uom,'||
' created_by, creation_date,'||
' last_update_date, last_updated_by, last_update_login,'||
' program_id, program_login_id,'||
' program_application_id, request_id'||
' )'||
' select'||
' plan_id,'||
' sr_instance_id,'||
' organization_id,'||
' inventory_item_id,'||
' sr_category_id1,'||
' sr_category_id2,'||
' sr_category_id3,'||
' pegging_sr_category_id,'||
' latest_item_id,'||
' vmi_flag,'||
' item_name,'||
' description,'||
' abc_class,'||
' product_family_id,'||
' average_daily_demand,'||
' average_discount,'||
' base_item_id,'||
' build_in_wip_flag,'||
' buyer_name,'||
' fixed_lead_time,'||
' list_price,'||
' max_minmax_quantity,'||
' min_minmax_quantity,'||
' minimum_order_quantity,'||
' mrp_planning_code,'||
' planner_code,'||
' planning_make_buy_code,'||
' postprocessing_lead_time,'||
' preprocessing_lead_time,'||
' purchasing_enabled_flag,'||
' repetitive_type,'||
' safety_stock_code,'||
' standard_cost,'||
' unit_volume,'||
' unit_weight,'||
' uom_code,'||
' variable_lead_time,'||
' volume_uom,'||
' weight_uom,'||
' created_by, creation_date,'||
' last_update_date, last_updated_by, last_update_login,'||
' program_id, program_login_id,'||
' program_application_id, request_id'||
' from msc_apcc_item_d_bak';
msc_phub_util.log('insert into msc_apcc_item_d: '||sql%rowcount);
update msc_plan_runs set scenario_name = regexp_substr(plan_run_name, '\[.*\]', 1, 1);
update msc_plan_runs set scenario_name = substr(scenario_name, 2, length(scenario_name)-2);
update msc_plan_runs set last_run_flag=2
where plan_run_id in
(select plan_run_id from
(select plan_name, plan_run_id,
last_value(plan_run_id) over(partition by plan_name order by plan_run_id
rows between unbounded preceding and unbounded following) last_plan_run_id
from msc_plan_runs
where plan_name in
(select plan_name
from msc_plan_runs
where last_run_flag=1
group by plan_name
having count(*)>1
)
)
where plan_run_id <> last_plan_run_id
);
msc_phub_util.log('delete from msc_plan_runs where plan_id=0 and plan_run_id=-1');
delete from msc_plan_runs where plan_id=0 and plan_run_id=-1;
select count(*) into l_need_migrate
from msc_plan_runs
where plan_id=0 and plan_run_id=-1;