[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*select CONV_RATE
into l_currency_rate
from MSC_CURRENCY_CONVERSIONS
where FROM_CURRENCY = p_func_currency
and TO_CURRENCY = l_reporting_currency
and SR_INSTANCE_ID = p_sr_instance_id
and CONV_DATE = p_date;*/
/*select currency_code
into l_func_currency
from msc_trading_partners
where sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and partner_type = 3;*/
select meaning
from mfg_lookups
where lookup_type = 'MSC_EXCEPTION_GROUP'
and lookup_code = p_exception_group_id;
select meaning
from mfg_lookups
where lookup_type = 'MSC_EXCEPTION_GROUP'
and lookup_code = p_exception_group_id;
select nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100))
into l_list_price
from msc_system_items msi
where
msi.plan_id =p_plan_id
and msi.sr_instance_id = p_inst_id
and msi.organization_id = p_org_id
and msi.inventory_item_id = p_item_id;
select count(1) into l_plan_constrained
from
msc_plans mp
where
mp.plan_id = p_plan_id
and( nvl(mp.daily_resource_constraints,0 ) = 1
or nvl(mp.weekly_resource_constraints,0) = 1
or nvl(mp.period_resource_constraints,0) = 1
or nvl(mp.daily_material_constraints,0 ) = 1
or nvl(mp.weekly_material_constraints,0) = 1
or nvl(mp.period_material_constraints,0) = 1);
select CURR_PLAN_TYPE
into l_plan_type
from
msc_plans
where
plan_id = p_plan_id;
select distinct u.user_name
into l_user_name
from fnd_user u, fnd_user_resp_groups g
where u.user_id=g.user_id
and g.responsibility_application_id=724
and sysdate between u.start_date and nvl(u.end_date, sysdate)
and u.user_id=p_user_id;
select furg.user_id, furg.responsibility_id, responsibility_application_id
from fnd_user_resp_groups furg,
fnd_user fu,
fnd_responsibility fr
where furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and furg.responsibility_application_id = fr.application_id
--and fu.user_name = 'APCC_ADMIN'
and fu.user_name = ll_user
and fr.application_id = 724;
select furg.user_id, furg.responsibility_id, responsibility_application_id
from fnd_user_resp_groups furg,
fnd_user fu,
fnd_responsibility fr
where furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and furg.responsibility_application_id = fr.application_id
--and fu.user_name = 'APCC_ADMIN'
and fu.user_name = ll_user
and fr.responsibility_key = 'APS_SCN_PLN';
' select dir'||
' from'||
' (select trim(p.value) dir'||
' from v$parameter2 p, dba_directories d'||
' where p.name=''utl_file_dir'''||
' and d.directory_name(+)=''ECX_UTL_LOG_DIR_OBJ'''||
' and d.owner(+)=''SYS'''||
' and trim(p.value)=d.directory_path(+)'||
' order by d.directory_name'||
' )'||
' where rownum=1';
procedure delete_log is
begin
utl_file.fremove(g_log_dir, g_log_file);
end delete_log;
select systimestamp into t from dual;
' update '||p_staging_table||' set error_code=:error_code'||
' where st_transaction_id=:p_st_transaction_id and error_code is null';
' select distinct '||l_merged_columns||' from '||p_staging_table||
' where st_transaction_id=:p_st_transaction_id and error_code=:error_code';
' update '||p_staging_table||' f set (error_code, '||p_sr_instance_id_col||', '||p_organization_id_col||') ='||
' (select 0, d.sr_instance_id, d.sr_tp_id from '||
' (select mtp.sr_instance_id, mtp.sr_tp_id,'||
' mtp.organization_code, mai.instance_code'||
' from msc_trading_partners mtp, msc_apps_instances mai'||
' where mtp.sr_instance_id=mai.instance_id'||
' and mtp.partner_type=3) d'||
' where f.'||p_organization_code_col||'=d.organization_code'||
' and (instr(f.'||p_organization_code_col||', '':'')>0 '||
' or nvl('''||p_def_instance_code||''', d.instance_code)=d.instance_code)'||
' and rownum=1)'||
' where f.st_transaction_id=:p_st_transaction_id'||
' and f.error_code = 0'||
' and f.'||p_organization_code_col||' is not null';
' update '||p_staging_table||' f set (error_code, '||p_item_id_col||') ='||
' (select 0, d.inventory_item_id from msc_items d'||
' where d.item_name=f.'||p_item_name_col||')'||
' where f.st_transaction_id=:p_st_transaction_id'||
' and f.error_code = 0'||
' and f.'||p_item_name_col||' is not null';
' update '||p_staging_table||' f set (error_code, '||p_sr_instance_id_col||') ='||
' (select 0, d.instance_id from msc_apps_instances d'||
' where d.instance_code=f.'||p_category_instance_code_col||')'||
' where f.st_transaction_id=:p_st_transaction_id'||
' and f.error_code = 0'||
' and f.'||p_category_instance_code_col||' is not null';
' update '||p_staging_table||' f set (error_code, '||p_item_id_col||') ='||
' (select 0, -sr_category_id from msc_phub_categories_mv d'||
' where d.category_name=f.'||p_category_name_col||' and d.sr_instance_id=f.'||p_sr_instance_id_col||')'||
' where f.st_transaction_id=:p_st_transaction_id'||
' and f.error_code = 0'||
' and f.'||p_item_name_col||' is null and f.'||p_category_name_col||' is not null';
' update '||p_staging_table||' f set (error_code, category_set_id, category_instance_id) ='||
' (select 0, '||l_category_set_id||', d.instance_id from msc_apps_instances d'||
' where d.instance_code=f.category_instance_code)'||
' where f.st_transaction_id=:p_st_transaction_id'||
' and f.error_code = 0'||
' and f.category_instance_code is not null';
' update '||p_staging_table||' f set (error_code, sr_category_id) ='||
' (select 0, d.sr_category_id from msc_phub_categories_mv d'||
' where d.category_set_id=f.category_set_id'||
' and d.sr_instance_id=f.category_instance_id'||
' and d.category_name=f.category_name)'||
' where f.st_transaction_id=:p_st_transaction_id'||
' and f.error_code = 0'||
' and f.category_name is not null';
' update '||p_staging_table||' f set (error_code, '||substr(l_result_cols,3)||') ='||
' (select 0, '||substr(l_source_cols,3)||
' from msc_phub_customers_mv d'||
' where (('||l_where1||') or ('||l_where2||') or ('||l_where3||'))'||
' and rownum=1)'||
' where f.st_transaction_id=:p_st_transaction_id and f.error_code=0';
' update '||p_staging_table||' f set (error_code, '||p_supplier_id_col||', '||p_supplier_site_id_col||') ='||
' (select 0, d.supplier_id, d.supplier_site_id'||
' from msc_phub_suppliers_mv d'||
' where nvl(d.supplier_name,'''||l_unassigned||''')=nvl(f.'||p_supplier_name_col||','''||l_unassigned||''')'||
' and (f.'||p_supplier_site_code_col||' is null and d.supplier_site_id=-23453'||
' or d.supplier_site_code=f.'||p_supplier_site_code_col||')'||
' and rownum=1)'||
' where f.st_transaction_id=:p_st_transaction_id'||
' and f.error_code = 0';
' update '||p_staging_table||' f set (error_code, department_id, resource_id) ='||
' (select 0, d.department_id, d.resource_id'||
' from msc_department_resources d'||
' where d.plan_id=-1'||
' and nvl(d.department_code,0)=nvl(f.department_code,0)'||
' and nvl(d.department_class,0)=nvl(f.department_class,0)'||
' and nvl(d.resource_code,0)=nvl(f.resource_code,0)'||
' and nvl(d.resource_group_name,0)=nvl(f.resource_group_name,0)'||
' and d.sr_instance_id=f.sr_instance_id'||
' and d.organization_id=f.organization_id)'||
' where f.st_transaction_id=:p_st_transaction_id'||
' and f.error_code = 0'||
' and f.resource_code is not null';
' update '||p_staging_table||' f set (error_code, project_id, task_id) ='||
' (select 0, d.project_id, d.task_id'||
' from msc_phub_projects_mv d'||
' where d.project_number=f.project_number'||
' and d.task_number=f.task_number'||
' and d.sr_instance_id=f.sr_instance_id'||
' and d.organization_id=f.organization_id)'||
' where f.st_transaction_id=:p_st_transaction_id'||
' and f.error_code = 0'||
' and f.task_number is not null';
' update '||p_staging_table||
' set error_code=:error_code'||
' where st_transaction_id=:p_st_transaction_id'||
' and ('||date_col||'nvl(:p_plan_cutoff_date,'||date_col||'))';
' update '||p_staging_table||
' set error_code=:error_code'||
' where st_transaction_id=:p_st_transaction_id'||
' and '||date_col||'<=:p_overwrite_after_date';
l_sql := 'delete from '||p_fact_table||
' where '||date_col||'>:p_overwrite_after_date';
l_sql := ' delete from '||p_fact_table;
select oracle_username
into l_apps_schema
from fnd_oracle_userid
where read_only_flag = 'U';
select msc_hub_query_s.nextval into l_qid from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1,
number2,
number3,
number4,
number5
)
-- values
select distinct l_qid, sysdate, -1, sysdate, -1, -1,
mrr.plan_id,
mrr.sr_instance_id,
mrr.organization_id,
mrr.department_id,
mrr.resource_id
from msc_resource_requirements mrr, msc_plan_runs mpr
where mpr.plan_id = p_plan_id
and mpr.plan_run_id = p_plan_run_id
and mrr.plan_id = p_plan_id
and mrr.sr_instance_id = mpr.sr_instance_id
and trunc(nvl(mrr.end_date,mrr.start_date)) between mpr.plan_start_date and mpr.plan_cutoff_date
and mrr.refresh_number > mpr.lcid
union all
select distinct l_qid, sysdate, -1, sysdate, -1, -1,
mra.plan_id,
mra.sr_instance_id,
mra.organization_id,
mra.department_id,
mra.resource_id
from msc_net_resource_avail mra, msc_plan_runs mpr
where mpr.plan_id = p_plan_id
and mpr.plan_run_id = p_plan_run_id
and mra.plan_id = p_plan_id
and mra.sr_instance_id = mpr.sr_instance_id
and mra.simulation_set is null
and trunc(trunc(mra.shift_date)) between mpr.plan_start_date and mpr.plan_cutoff_date
and mra.refresh_number > mpr.lcid;
select msc_hub_query_s.nextval into l_qid from dual;
insert into msc_hub_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1,
number2,
number3,
number4
)
-- values
select distinct l_qid, sysdate, -1, sysdate, -1, -1,
ms.plan_id,
ms.sr_instance_id,
ms.organization_id,
ms.inventory_item_id
from msc_supplies ms, msc_plan_runs mpr
where mpr.plan_id = p_plan_id
and mpr.plan_run_id = p_plan_run_id
and ms.plan_id = p_plan_id
and ms.sr_instance_id = mpr.sr_instance_id
and trunc(nvl(ms.firm_date,ms.new_schedule_date)) between mpr.plan_start_date and mpr.plan_cutoff_date
and ms.refresh_number > mpr.lcid
union all
select distinct l_qid, sysdate, -1, sysdate, -1, -1,
md.plan_id,
md.sr_instance_id,
md.organization_id,
md.inventory_item_id
from msc_demands md, msc_plan_runs mpr
where mpr.plan_id = p_plan_id
and mpr.plan_run_id = p_plan_run_id
and md.plan_id = p_plan_id
and md.sr_instance_id = mpr.sr_instance_id
and trunc(nvl(md.firm_date,md.using_assembly_demand_date)) between mpr.plan_start_date and mpr.plan_cutoff_date
and md.refresh_number > mpr.lcid;
select nvl(o.currency_code, 'XXX')
into l_owning_currency_code
from msc_trading_partners o, msc_plan_runs r
where o.sr_instance_id(+)=r.sr_instance_id
and o.sr_tp_id(+)=r.organization_id
and o.partner_type(+)=3
and r.plan_run_id=p_plan_run_id;
select msc_hub_query_s.nextval into l_qid_last_date from dual;
insert into msc_hub_query (
query_id, date1, date2,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login)
select
l_qid_last_date,
nvl(lag(calendar_date) over(order by calendar_date)+1, p_plan_start_date-1),
calendar_date,
fnd_global.user_id, sysdate,
sysdate, fnd_global.user_id, fnd_global.login_id
from msc_phub_dates_mv
where calendar_date between p_plan_start_date and p_plan_cutoff_date
and calendar_date in (mfg_week_end_date, fis_period_end_date, month_end_date, p_plan_cutoff_date);
select owner, index_name
from all_indexes
where table_owner=p_table_owner and table_name=p_table;