[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select upper('msc_st_'||entity_name||'_f') into l_entity_name from table(msc_phub_pkg.meta_info) where fact_type=p_fact_type;
update msc_apcc_upload_detail set
last_updated_by=fnd_global.user_id,
last_update_date=sysdate,
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 transfer_id=p_transfer_id and fact_type=p_fact_type;
select source_plan_run_id, source_dblink, nvl(source_version, msc_phub_util.g_version)
into l_plan_run_id, l_source_dblink, l_source_version
from msc_apcc_upload
where transfer_id=p_transfer_id;
select count(*) into n
from msc_hub_query
where query_id=p_query_id and number1=1;
select count(*) into n
from msc_hub_query
where query_id=p_query_id and number1=2 and blob1 is not null;
insert into msc_apcc_upload (
transfer_id,
import_level,
upload_mode,
directory,
plan_name,
plan_type,
plan_description,
sr_instance_id,
organization_id,
plan_start_date,
plan_cutoff_date,
plan_completion_date,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
select
l_transfer_id,
number3 import_level,
number4 upload_mode,
char3 directory,
char1 plan_name,
number2 plan_type,
char2 plan_description,
number5 sr_instance_id,
-23453 organization_id,
date1 plan_start_date,
date2 plan_cutoff_date,
sysdate plan_completion_date,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
from msc_hub_query
where query_id=p_query_id
and number1=1;
insert into msc_apcc_upload_detail (
transfer_id,
fact_type,
file_name,
file_data,
overwrite_after_date,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
select
l_transfer_id,
number2 fact_type,
char3 file_name,
blob1 file_data,
date1 overwrite_after_date,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
from msc_hub_query
where query_id=p_query_id
and number1=2
and blob1 is not null;
select instance_id
into l_sr_instance_id
from msc_apps_instances
where instance_code=p_instance_code;
select sr_instance_id, sr_tp_id
into l_sr_instance_id, l_organization_id
from msc_trading_partners
where partner_type=3
and organization_code=p_organization_code
and sr_instance_id=nvl(l_sr_instance_id, sr_instance_id)
and rownum=1;
insert into msc_apcc_upload (
transfer_id,
export_level,
import_level,
upload_mode,
directory,
source_plan_run_id,
source_dblink,
source_version,
transfer_status,
plan_name,
plan_type,
plan_description,
sr_instance_id,
organization_id,
plan_start_date,
plan_cutoff_date,
plan_completion_date,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
values (
l_transfer_id,
p_export_level,
p_import_level,
p_upload_mode,
p_directory,
p_source_plan_run_id,
p_source_dblink,
nvl(p_source_version, msc_phub_util.g_version),
status_transfering,
p_plan_name,
p_plan_type,
p_plan_description,
l_sr_instance_id,
l_organization_id,
p_plan_start_date,
p_plan_cutoff_date,
p_plan_completion_date,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
insert into msc_apcc_upload_detail (
transfer_id, fact_type, file_name,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
select
l_transfer_id, fact_type, upper('msc_st_'||entity_name||'_f')||'.csv',
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id
from
(select rownum fact_type, entity_name, initial_verion from table(msc_phub_pkg.meta_info))
where decode(fact_type,4,p_include_ods,p_include_pds)=1
and initial_verion<=nvl(p_source_version, msc_phub_util.g_version);
update msc_apcc_upload_detail
set overwrite_after_date=p_overwrite_after_date
where transfer_id=p_transfer_id and fact_type=p_fact_type;
update msc_apcc_upload set
last_updated_by=fnd_global.user_id,
last_update_date=sysdate,
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 transfer_id=p_transfer_id;
select source_plan_run_id
into l_plan_run_id
from msc_apcc_upload
where transfer_id=p_transfer_id;
select fact_type into l_fact_type
from msc_apcc_upload_detail
where transfer_id=p_transfer_id;
update msc_apcc_upload
set transfer_status=status_transfered
where transfer_id=p_transfer_id;
update msc_apcc_upload set
last_updated_by=fnd_global.user_id,
last_update_date=sysdate,
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 transfer_id=p_transfer_id;
select upload_mode, plan_id, plan_run_id
into l_upload_mode, l_plan_id, l_plan_run_id
from msc_apcc_upload
where transfer_id=p_transfer_id;
update msc_apcc_upload
set transfer_status=status_transfered
where transfer_id=p_transfer_id;
select
plan_id,
plan_name,
plan_description,
plan_type,
sr_instance_id,
organization_id,
plan_start_date,
plan_cutoff_date,
plan_completion_date,
null
into l_pi
from msc_apcc_upload
where transfer_id=p_transfer_id;
select import_level, upload_mode
into l_import_level, l_upload_mode
from msc_apcc_upload
where transfer_id=p_transfer_id;
select count(*) into n
from msc_apcc_upload_detail
where transfer_id=p_transfer_id
and fact_type=4
and (file_data is not null or l_import_level <> 3);
select r.plan_id, r.plan_run_id, r.plan_type, r.local_archive_flag
into l_pi.plan_id, l_plan_run_id, l_plan_type2, l_local_archive_flag
from msc_plan_runs r,
(select plan_id, max(plan_run_id) last_plan_run_id
from msc_plan_runs
where planning_hub_flag=1
group by plan_id) t
where r.plan_id=t.plan_id and r.plan_run_id=t.last_plan_run_id
and r.plan_name=l_pi.plan_name;
update msc_plan_runs
set plan_description = nvl(l_pi.plan_description, plan_description),
sr_instance_id = nvl(l_pi.sr_instance_id, sr_instance_id),
organization_id = nvl(l_pi.organization_id, organization_id),
plan_start_date = nvl(l_pi.plan_start_date, plan_start_date),
plan_cutoff_date = nvl(l_pi.plan_cutoff_date, plan_cutoff_date),
plan_completion_date = nvl(l_pi.plan_completion_date, plan_completion_date)
where plan_run_id=l_plan_run_id;
update msc_apcc_upload
set plan_id=l_pi.plan_id, plan_run_id=l_plan_run_id
where transfer_id=p_transfer_id;
select msc_plans_s.nextval into l_pi.plan_id from dual;
select plan_id, plan_type
into l_pi.plan_id, l_plan_type2
from
(select plan_id, compile_designator, plan_type
from msc_plans
union
select das.scenario_id, substr(das.scenario_name, 1, 50), 10
from msd_dp_ascp_scenarios_v das, msd_dem_transfer_query tq
where das.demand_plan_id=5555555
and das.demand_plan_name = substr(tq.query_name, 1, 30))
where compile_designator=l_pi.plan_name
and rownum=1;
select count(*) into n
from msc_plans p, msc_designators d
where p.plan_id=l_pi.plan_id
and p.sr_instance_id=d.sr_instance_id
and p.compile_designator=d.designator
and p.organization_id=d.organization_id;
update msc_apcc_upload
set plan_id=l_pi.plan_id, plan_run_id=l_plan_run_id
where transfer_id=p_transfer_id;
update msc_apcc_upload_detail set
last_updated_by=fnd_global.user_id,
last_update_date=sysdate,
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 transfer_id=p_transfer_id and fact_type=p_fact_type;
select plan_id, plan_run_id, plan_type, sr_instance_id,
plan_start_date, plan_cutoff_date, upload_mode
into l_plan_id, l_plan_run_id, l_plan_type, l_def_instance_id,
l_plan_start_date, l_plan_cutoff_date, l_upload_mode
from msc_apcc_upload
where transfer_id=p_transfer_id;
select overwrite_after_date
into l_overwrite_after_date
from msc_apcc_upload_detail
where transfer_id=p_transfer_id
and fact_type=p_fact_type;
select instance_code
into l_def_instance_code
from msc_apps_instances
where instance_id=l_def_instance_id;
select plan_name into l_plan_name from msc_apcc_upload where transfer_id=p_transfer_id;
select msc_plans_s.nextval into l_plan_id from dual;
insert into msc_plans (
plan_id,
compile_designator,
description,
plan_type,
sr_instance_id,
organization_id,
curr_start_date,
curr_cutoff_date,
plan_completion_date,
curr_append_planned_orders,
curr_demand_time_fence_flag,
curr_operation_schedule_type,
curr_overwrite_option,
curr_planning_time_fence_flag,
curr_plan_type,
daily_cutoff_bucket,
daily_item_aggregation_level,
daily_material_constraints,
daily_resource_constraints,
daily_res_aggregation_level,
weekly_cutoff_bucket,
weekly_item_aggregation_level,
weekly_material_constraints,
weekly_resource_constraints,
weekly_res_aggregation_level,
optimize_flag,
schedule_flag,
curr_enforce_dem_due_dates,
curr_planned_resources,
daily_rtg_aggregation_level,
weekly_rtg_aggregation_level,
period_cutoff_bucket,
period_material_constraints,
period_resource_constraints,
period_item_aggregation_level,
period_res_aggregation_level,
display_kpi,
last_updated_by, last_update_date, created_by, creation_date
)
select
l_plan_id,
l_plan_name,
plan_description,
plan_type,
nvl(sr_instance_id, -23453),
nvl(organization_id, -23453),
nvl(plan_start_date, sysdate),
nvl(plan_cutoff_date, sysdate),
nvl(plan_completion_date, sysdate),
0, 0, 0, 0, 0, plan_type,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
from msc_apcc_upload
where transfer_id=p_transfer_id;
update msc_apcc_upload_detail set file_data=null where transfer_id=p_transfer_id;
select transfer_id
from msc_apcc_upload
where transfer_status=status_purging;
update msc_apcc_upload
set transfer_status=status_purging
where ((export_level>0 and source_dblink is null
and source_plan_run_id in (select column_value from table(msc_phub_pkg.list_plan_runs(p_plan_name, p_plan_run_id))))
or (import_level>0
and plan_run_id in (select column_value from table(msc_phub_pkg.list_plan_runs(p_plan_name, p_plan_run_id)))));
delete from msc_apcc_upload_detail where transfer_id in (
select distinct transfer_id from msc_apcc_upload
where transfer_status=status_purging);
delete from msc_apcc_upload where transfer_status=status_purging;