[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
' select cast(null as varchar2(100)) level_name from dual where 1=2';
' select table_label level_name from ('||
' select group_table_id, table_label'||
' from '||g_demantra_schema||'.group_tables'||
' connect by father_id=prior group_table_id'||
' start with table_label=''Item'''||
' )'||
' where group_table_id in'||
' (select group_table_id'||
' from '||g_demantra_schema||'.group_tables b'||
' where not exists'||
' (select 1 from '||g_demantra_schema||'.group_tables where father_id=b.group_table_id)'||
' )'||
' order by 1';
l_sql := 'select view_name from '||g_demantra_schema||'.transfer_query where application_id='''||p_publisher||'''';
l_sql := 'select export_type from '||g_demantra_schema||'.transfer_query where application_id='''||p_publisher||'''';
select upper('msc_'||entity_name||'_f') into l_entity_name from table(msc_phub_pkg.meta_info) where fact_type=p_fact_type;
select upper('msc_st_'||entity_name||'_f') into l_entity_name from table(msc_phub_pkg.meta_info) where fact_type=p_fact_type;
delete from msc_apcc_custom_hier;
msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: delete from msc_apcc_custom_hier: '||sql%rowcount);
delete from msc_translated_message where lang_id='00' and msg_num like g_msg_num_prefix1||'%';
msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: delete from msc_translated_message: '||sql%rowcount);
' select rownum level_id, gtable, cust_code_field, gtable2, id_field2, table_label'||LF||
' from'||LF||
' (select gtable, cust_code_field, prior gtable gtable2, prior id_field id_field2, table_label'||LF||
' from '||g_demantra_schema||'.group_tables'||LF||
' connect by group_table_id=prior father_id'||LF||
' start with table_label='''||l_item_hierarchy||''''||LF||
' order by level desc)'||LF;
l_sql2 := 'select count(1) from ('||l_sql||')';
' insert into msc_apcc_custom_hier('||LF||
' detail_id,'||LF||
' '||list_result('select ''level''||level_id from ('||l_sql||') where gtable<>''t_ep_item''', ','||LF)||
' created_by, creation_date,'||LF||
' last_update_date, last_updated_by, last_update_login,'||LF||
' program_id, program_login_id,'||LF||
' program_application_id, request_id)'||LF||
' select'||LF||
' t_ep_item.ebs_item_dest_key,'||LF||
' '||list_result('select ''cast(''||gtable||''.''||cust_code_field||'' as varchar2(200))'' from ('||l_sql||') where gtable<>''t_ep_item''', ','||LF)||
' fnd_global.user_id, sysdate,'||LF||
' sysdate, fnd_global.user_id, fnd_global.login_id,'||LF||
' fnd_global.conc_program_id, fnd_global.conc_login_id,'||LF||
' fnd_global.prog_appl_id, fnd_global.conc_request_id'||LF||
' from'||LF||
' '||list_result('select '''||g_demantra_schema||'.''||gtable from ('||l_sql||') where gtable is not null', ', ')||' dual'||LF||
' where'||LF||
' '||list_result('select gtable||''.''||id_field2||''=''||gtable2||''.''||id_field2 from ('||l_sql||') where gtable2 is not null', ' and '||LF)||
' t_ep_item.ebs_item_dest_key is not null';
msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: insert into msc_apcc_custom_hier: '||sql%rowcount);
' insert into msc_translated_message(lang_id, msg_num, msg_text)'||LF||
' select ''00'', '''||g_msg_num_prefix1||'''||substr(to_char(level_id,''09''),2), table_label'||LF||
' from ('||l_sql||')';
msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: insert into msc_translated_message: '||sql%rowcount);
(select m.fact_type, f.column_name
from dba_tab_columns f, table(msc_phub_pkg.meta_info) m, user_synonyms syn
where m.fact_type in (4,5)
and syn.synonym_name='MSC_'||upper(m.entity_name)||'_F'
and f.owner=g_msc_schema
and f.column_name like 'ATTRIBUTE%'
and f.owner=syn.table_owner
and f.table_name=syn.table_name) f
on (b.fact_type=f.fact_type and b.column_name=f.column_name)
when not matched then insert (
fact_type, column_name, column_title,
source_column_name, aggregation_method,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
values (
f.fact_type, f.column_name, null, null, null,
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);
delete from msc_apcc_custom_measures b
where not exists
(select 1
from dba_tab_columns f, table(msc_phub_pkg.meta_info) m, user_synonyms syn
where m.fact_type in (4,5)
and syn.synonym_name='MSC_'||upper(m.entity_name)||'_F'
and f.owner=g_msc_schema
and f.column_name like 'ATTRIBUTE%'
and f.owner=syn.table_owner
and f.table_name=syn.table_name
and f.column_name=b.column_name
and m.fact_type=b.fact_type);
msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table: delete from msc_apcc_custom_measures: '||sql%rowcount);
insert into msc_demantra_f (
plan_id,
plan_run_id,
sr_instance_id,
organization_id,
customer_id,
customer_site_id,
region_id,
inventory_item_id,
demand_class,
owning_org_id,
owning_inst_id,
start_date,
aggr_type, category_set_id, sr_category_id,
consensus_fcst,
consensus_fcst_value,
consensus_fcst_value2,
consensus_fcst_cum,
priority,
created_by, creation_date,
last_update_date, last_updated_by, last_update_login,
program_id, program_login_id,
program_application_id, request_id)
select
t.scenario_id plan_id,
p_plan_run_id,
decode(t.organization_id, -1, -23453, t.sr_instance_id) sr_instance_id, --wei: sync sr_instance_id with organization_id
decode(t.organization_id, -1, -23453, t.organization_id) organization_id,
t.customer_id,
t.customer_site_id,
t.zone_id region_id,
t.inventory_item_id,
t.demand_class,
t.owning_org_id,
t.sr_instance_id owning_inst_id,
t.start_date,
to_number(0) aggr_type,
to_number(-23453) category_set_id,
to_number(-23453) sr_category_id,
t.consensus_fcst,
t.consensus_fcst*t.price consensus_fcst_value,
t.consensus_fcst*t.price * decode(t.currency_code,
fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))
consensus_fcst_value2,
t.consensus_fcst_cum,
t.priority,
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
k.scenario_id,
k.sr_instance_id,
k.organization_id,
k.customer_id,
k.customer_site_id,
k.zone_id,
k.inventory_item_id,
k.owning_org_id,
k.demand_class,
k.start_date,
nvl(f.currency_code, l_owning_currency_code) currency_code,
f.price,
f.consensus_fcst,
sum(nvl(f.consensus_fcst, 0)) over(partition by
k.scenario_id, k.sr_instance_id, k.organization_id,
k.customer_id, k.customer_site_id, k.zone_id,
k.inventory_item_id, k.demand_class
order by k.start_date) consensus_fcst_cum,
f.priority
from
(select -- distinct bug 9941114
k1.scenario_id,
k1.sr_instance_id,
k1.organization_id,
k1.customer_id,
k1.customer_site_id,
k1.zone_id,
k1.inventory_item_id,
k1.owning_org_id,
k1.demand_class,
k1.price,
k2.start_date
from
(select distinct
scenario_id,
sr_instance_id,
organization_id,
customer_id,
customer_site_id,
zone_id,
inventory_item_id,
price,
demand_class,
decode(organization_id,
-1, msc_hub_calendar.get_item_org(-1, inventory_item_id, sr_instance_id),
organization_id) owning_org_id
from msd_dem_scn_entries_v
where scenario_id=p_plan_id
) k1,
(select distinct start_date
from msd_dem_scn_entries_v
where scenario_id=p_plan_id) k2
) k,
msd_dem_scn_entries_v f
where k.scenario_id = f.scenario_id(+)
and k.sr_instance_id = f.sr_instance_id(+)
and k.organization_id = f.organization_id(+)
and k.customer_id = f.customer_id(+)
and k.customer_site_id = f.customer_site_id(+)
and k.zone_id = f.zone_id(+)
and k.inventory_item_id = f.inventory_item_id(+)
and k.demand_class = f.demand_class(+)
and k.start_date = f.start_date(+)) t,
msc_currency_conv_mv mcc
where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
and mcc.from_currency(+) = t.currency_code
and mcc.calendar_date(+) = t.start_date;
msc_phub_util.log('msc_demantra_pkg.populate_details: insert='||sql%rowcount);
select msc_hub_query_s.nextval into l_qid_wf from dual;
' insert into msc_hub_query ('||LF||
' query_id, number1, clob1,'||LF||
' created_by, creation_date,'||LF||
' last_update_date, last_updated_by, last_update_login)'||LF||
' select'||LF||
' '||l_qid_wf||', schema_id, to_lob(schema_data),'||LF||
' fnd_global.user_id, sysdate,'||LF||
' sysdate, fnd_global.user_id, fnd_global.login_id'||LF||
' from '||g_demantra_schema||'.wf_schemas';
' select'||LF||
' nvl(b.fact_type, v.fact_type) fact_type,'||LF||
' decode(b.source_column_name, null, ''I'', decode(v.computed_name, null, ''D'', ''U'')) op,'||LF||
' nvl(b.source_column_name, v.computed_name) computed_name,'||LF||
' v.computed_title, v.int_aggr_func'||LF||
' from'||LF||
' (select fact_type, source_column_name'||LF||
' from msc_apcc_custom_measures'||LF||
' where source_column_name is not null'||LF||
' ) b'||LF||
' full outer join'||LF||
' (select /*+ ordered */ distinct m.fact_type, c.computed_name, c.computed_title, c.int_aggr_func'||LF||
' from'||LF||
' (select /*+ ordered */ distinct b.fact_type, q.id'||LF||
' from'||LF||
' (select 4 fact_type, ''apps.msc_phub_pkg.archive_demantra_ods'' proc_name from dual'||LF||
' union select 5 fact_type, ''apps.msc_phub_pkg.archive_demantra_plan'' proc_name from dual'||LF||
' ) b,'||LF||
' (select extract(f.column_value, ''/PROCEDURE'') s'||LF||
' from msc_hub_query t, xmltable(''/WORKFLOW/STOREDPROCEDURESTEP/PROCEDURE'' PASSING xmltype(t.clob1)) f'||LF||
' where t.query_id='||l_qid_wf||LF||
' ) f,'||LF||
' '||g_demantra_schema||'.transfer_query q'||LF||
' where lower(extractValue(f.s, ''/PROCEDURE/NAME''))=b.proc_name'||LF||
' and trim(extractValue(f.s, ''/PROCEDURE/PARAM[1]''))=q.application_id'||LF||
' ) m,'||LF||
' '||g_demantra_schema||'.transfer_query_series qs,'||LF||
' '||g_demantra_schema||'.computed_fields c'||LF||
' where m.id=qs.id'||LF||
' and qs.series_id=c.forecast_type_id'||LF||
' and upper(c.computed_name) not in ('||g_dimension_columns||')'||LF||
' and c.computed_name not in (select source_column_name from table(msc_demantra_pkg.seeded_measure_mapping) where fact_type=m.fact_type)'||LF||
' ) v'||LF||
' on (b.fact_type=v.fact_type and b.source_column_name=v.computed_name)'||LF||
' order by 3,1';
update msc_apcc_custom_measures
set source_column_name=null,
column_title=null,
aggregation_method=null,
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 fact_type=l_fact_type and source_column_name=l_source_column_name;
select column_name into l_column_name
from msc_apcc_custom_measures
where fact_type=l_fact_type and source_column_name is null and rownum=1
order by to_number(substr(column_name,10));
update msc_apcc_custom_measures
set source_column_name=l_source_column_name,
column_title=l_column_title,
aggregation_method=l_aggregation_method,
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 fact_type=l_fact_type and column_name=l_column_name;
update msc_apcc_custom_measures
set 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 fact_type=l_fact_type and source_column_name=l_source_column_name;
delete from msc_translated_message where lang_id='00' and msg_num like g_msg_num_prefix2||'%';
msc_phub_util.log('msc_demantra_pkg.register_custom_measures: delete from msc_translated_message: '||sql%rowcount);
insert into msc_translated_message(lang_id, msg_num, msg_text)
select
'00' lang_id,
g_msg_num_prefix2||upper(m.entity_name)||'_'||substr(to_char(substr(f.column_name,10),'09'),2) msg_num,
f.column_title msg_text
from msc_apcc_custom_measures f, table(msc_phub_pkg.meta_info) m
where f.fact_type=m.fact_type
and f.source_column_name is not null
order by to_number(substr(f.column_name,10));
msc_phub_util.log('msc_demantra_pkg.register_custom_measures: insert into msc_translated_message: '||sql%rowcount);
' select source_column_name, aggregation_method, column_name, formula'||LF||
' from'||LF||
' (select'||LF||
' first_value(v.column_name) over(partition by b.column_name, b.formula order by b.computed_name desc) source_column_name,'||LF||
' b.aggregation_method,'||LF||
' b.column_name, b.formula'||LF||
' from'||LF||
' (select c.computed_name, c.int_aggr_func, m.column_name, m.formula, m.aggregation_method'||LF||
' from'||LF||
' table(msc_demantra_pkg.seeded_measure_mapping) m,'||LF||
' '||g_demantra_schema||'.computed_fields c'||LF||
' where m.source_column_name=c.computed_name'||LF||
' and m.fact_type='||p_fact_type||LF||
' ) b,'||LF||
' all_tab_columns v'||LF||
' where v.table_name(+)='''||source_table(p_publisher)||''''||LF||
' and v.owner(+)='''||g_demantra_schema||''''||LF||
' and upper(b.computed_name)=v.column_name(+)'||LF||
' union all'||LF||
' select c.source_column_name, nvl(c.aggregation_method, ''sum''), c.column_name,'||LF||
' decode(c.source_column_name, null, null, ''t.''||c.source_column_name) formula'||LF||
' from'||LF||
' msc_apcc_custom_measures c,'||LF||
' all_tab_columns v'||LF||
' where v.table_name='''||source_table(p_publisher)||''''||LF||
' and v.owner='''||g_demantra_schema||''''||LF||
' and v.column_name=upper(c.source_column_name)'||LF||
' and c.fact_type='||p_fact_type||LF||
' and c.column_name like ''ATTRIBUTE%'''||LF||
' )';
' select distinct column_name, aggregation_method'||LF||
' from table(msc_demantra_pkg.seeded_measure_mapping)'||LF||
' where fact_type='||p_fact_type||LF||
' union all'||LF||
' select column_name, nvl(aggregation_method, ''sum'')'||LF||
' from msc_apcc_custom_measures'||LF||
' where fact_type='||p_fact_type;
l_sql := 'select distinct aggregation_method||''(''||'''||p_prefix||'.''||source_column_name||'') ''||source_column_name s from ('||l_sql||') where source_column_name is not null';
l_sql := 'select distinct '''||p_prefix||'.''||source_column_name s from ('||l_sql||') where source_column_name is not null';
l_sql := 'select s from (select distinct column_name, formula||'' ''||column_name s from ('||l_sql||') where source_column_name is not null order by column_name)';
l_sql := 'select distinct column_name s from ('||l_sql||') where source_column_name is not null order by column_name';
l_sql := 'select s from (select column_name, aggregation_method||''(''||'''||p_prefix||'.''||column_name||'') ''||column_name s from ('||l_sql2||') order by column_name)';
l_sql := 'select column_name s from ('||l_sql2||') order by column_name';
' select ''LEVEL1'' column_name, ''t_ep_item_EP_ID'' id_field from dual'||LF||
' union select ''LEVEL2'', ''t_ep_ebs_demand_class_EP_ID'' from dual'||LF||
' union select ''LEVEL3'', ''t_ep_organization_EP_ID'' from dual'||LF||
' union select ''LEVEL4'', ''t_ep_site_EP_ID'' from dual'||LF||
' union select ''DKEY_ITEM'', null from dual'||LF||
' union select ''DKEY_SITE'', null from dual'||LF||
' union select ''SDATE'', null from dual'||LF||
' union select ''RECORD_TYPE'', null from dual';
' select v.column_name, d.id_field'||LF||
' from'||LF||
' all_tab_columns v,'||LF||
' (select ''LEVEL''||rownum level_column, gtable, id_field'||LF||
' from'||LF||
' (select g.gtable, g.id_field'||LF||
' from '||g_demantra_schema||'.transfer_query q, '||g_demantra_schema||'.transfer_query_levels l, '||g_demantra_schema||'.group_tables g'||LF||
' where q.id=l.id'||LF||
' and l.level_id=g.group_table_id'||LF||
' and q.view_name='''||source_table(p_publisher)||''''||LF||
' and g.status=''ACTIVE'''||LF||
' order by l.lorder'||LF||
' )'||LF||
' ) d'||LF||
' where v.table_name='''||source_table(p_publisher)||''''||LF||
' and v.owner='''||g_demantra_schema||''''||LF||
' and v.column_name in ('||g_dimension_columns||')'||LF||
' and v.column_name=d.level_column(+)'||LF;
l_sql := 'select '''||p_prefix||'.''||column_name s from ('||l_sql||')';
l_sql := 'select '''||p_prefix||'.''||column_name s from ('||l_sql||') where nvl(id_field,'''||DUMMY||''')<>''t_ep_organization_EP_ID''';
l_sql := 'select '''||p_prefix||'.''||column_name s from ('||l_sql||') where column_name not in (''RECORD_TYPE'', ''SDATE'')';
delete from msc_demantra_f
where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
msc_phub_util.log('msc_demantra_pkg.summarize, delete='||sql%rowcount);
' insert into msc_demantra_f ('||LF||
' plan_id, plan_run_id,'||LF||
' sr_instance_id, organization_id, owning_inst_id, owning_org_id,'||LF||
' inventory_item_id,'||LF||
' customer_id, customer_site_id, region_id,'||LF||
' demand_class, start_date,'||LF||
' aggr_type, category_set_id, sr_category_id,'||LF||
' '||list_measures(6, '', p_fact_type, null)||LF||
' created_by, creation_date,'||LF||
' last_update_date, last_updated_by, last_update_login,'||LF||
' program_id, program_login_id,'||LF||
' program_application_id, request_id)'||LF||
' select'||LF||
' f.plan_id, f.plan_run_id,'||LF||
' f.sr_instance_id, f.organization_id, f.owning_inst_id, to_number(-23453) owning_org_id,'||LF||
' to_number(-23453) inventory_item_id,'||LF||
' f.customer_id, f.customer_site_id, f.region_id,'||LF||
' f.demand_class, f.start_date,'||LF||
' to_number(42) aggr_type,'||LF||
' '||l_category_set_id1||' category_set_id,'||LF||
' nvl(q.sr_category_id, -23453),'||LF||
' '||list_measures(5, 'f', p_fact_type, null)||LF||
' fnd_global.user_id, sysdate,'||LF||
' sysdate, fnd_global.user_id, fnd_global.login_id,'||LF||
' fnd_global.conc_program_id, fnd_global.conc_login_id,'||LF||
' fnd_global.prog_appl_id, fnd_global.conc_request_id'||LF||
' from'||LF||
' msc_demantra_f f,'||LF||
' msc_phub_item_categories_mv q'||LF||
' where f.plan_id='||p_plan_id||' and f.plan_run_id='||p_plan_run_id||LF||
' and f.aggr_type=0'||LF||
' and f.owning_inst_id=q.sr_instance_id(+)'||LF||
' and f.owning_org_id=q.organization_id(+)'||LF||
' and f.inventory_item_id=q.inventory_item_id(+)'||LF||
' and q.category_set_id(+)='||l_category_set_id1||LF||
' group by'||LF||
' f.plan_id, f.plan_run_id,'||LF||
' f.sr_instance_id, f.organization_id, f.owning_inst_id,'||LF||
' f.customer_id, f.customer_site_id, f.region_id,'||LF||
' f.demand_class, f.start_date,'||LF||
' nvl(q.sr_category_id, -23453)';
' insert into '||l_target_table||' ('||LF||
' '||l_plan_cols1||LF||
' sr_instance_id,'||LF||
' organization_id,'||LF||
' owning_inst_id,'||LF||
' owning_org_id,'||LF||
' inventory_item_id,'||LF||
' customer_id,'||LF||
' customer_site_id,'||LF||
' region_id,'||LF||
' demand_class,'||LF||
' '||LF||
' '||list_measures(6, '', p_fact_type, null)||LF||
' '||LF||
' created_by, creation_date,'||LF||
' last_update_date, last_updated_by, last_update_login,'||LF||
' program_id, program_login_id,'||LF||
' program_application_id, request_id)'||LF||
' select'||LF||
' '||l_plan_cols2||LF||
' nvl(t.sr_instance_id, -23453),'||LF||
' nvl(t.organization_id, -23453),'||LF||
' nvl(t.owning_inst_id, -23453),'||LF||
' nvl(t.owning_org_id, -23453),'||LF||
' nvl(t.inventory_item_id, -23453),'||LF||
' nvl(t.customer_id, -23453),'||LF||
' nvl(t.customer_site_id, -23453),'||LF||
' nvl(t.region_id, -23453),'||LF||
' nvl(t.demand_class, -''23453''),'||LF||
' '||LF||
' '||list_measures(6, 't', p_fact_type, null)||LF||
' '||LF||
' fnd_global.user_id, sysdate,'||LF||
' sysdate, fnd_global.user_id, fnd_global.login_id,'||LF||
' fnd_global.conc_program_id, fnd_global.conc_login_id,'||LF||
' fnd_global.prog_appl_id, fnd_global.conc_request_id'||LF||
' from '||l_staging_table||' t'||LF||
' where st_transaction_id='||p_st_transaction_id||' and error_code=0';
msc_phub_util.log('msc_demantra_pkg.append_from_staging: insert into '||l_target_table||': '||sql%rowcount);
select min(instance_id) into l_sr_instance_id
from msc_apps_instances
where instance_type<>3 and validation_org_id is not null;
' insert into '||l_target_table||' ('||LF||
' '||l_plan_cols1||LF||
' sr_instance_id,'||LF||
' organization_id,'||LF||
' owning_inst_id,'||LF||
' owning_org_id,'||LF||
' inventory_item_id,'||LF||
' customer_id,'||LF||
' customer_site_id,'||LF||
' region_id,'||LF||
' demand_class,'||LF||
' '||LF||
' '||list_measures(4, '', p_fact_type, p_publisher)||LF||
' '||LF||
' created_by, creation_date,'||LF||
' last_update_date, last_updated_by, last_update_login,'||LF||
' program_id, program_login_id,'||LF||
' program_application_id, request_id)'||LF||
' select'||LF||
' '||l_plan_cols2||LF||
' t.sr_instance_id,'||LF||
' t.organization_id,'||LF||
' t.owning_inst_id,'||LF||
' t.owning_org_id,'||LF||
' t.inventory_item_id,'||LF||
' t.customer_id,'||LF||
' t.customer_site_id,'||LF||
' t.region_id,'||LF||
' t.demand_class,'||LF||
' '||LF||
' '||list_measures(3, 't', p_fact_type, p_publisher)||LF||
' '||LF||
' fnd_global.user_id, sysdate,'||LF||
' sysdate, fnd_global.user_id, fnd_global.login_id,'||LF||
' fnd_global.conc_program_id, fnd_global.conc_login_id,'||LF||
' fnd_global.prog_appl_id, fnd_global.conc_request_id'||LF||
' from'||LF||
' (select'||LF||
' t.sr_instance_id,'||LF||
' t.organization_id,'||LF||
' t.currency_code,'||LF||
' t.owning_inst_id,'||LF||
' t.owning_org_id,'||LF||
' t.inventory_item_id,'||LF||
' t.price,'||LF||
' t.customer_id,'||LF||
' t.customer_site_id,'||LF||
' t.region_id,'||LF||
' t.demand_class,'||LF||
' t.record_type,'||LF||
' t.sdate,'||LF||
' t.future_start,'||LF||
' '||list_measures(1, 't', p_fact_type, p_publisher)||LF||
' t.dummy'||LF||
' from'||LF||
' (select /*+ ordered */'||LF||
' t.sr_instance_id,'||LF||
' t.organization_id,'||LF||
' t.currency_code,'||LF||
' rank() over('||LF||
' partition by t.sr_instance_id, t.organization_id,'||LF||
' msi.inventory_item_id, mpc.customer_id, mpc.customer_site_id,'||LF||
' mpc.region_id, mdc.demand_class, t.sdate'||LF||
' order by msi.organization_id) n,'||LF||
' t.sr_instance_id owning_inst_id,'||LF||
' nvl(msi.organization_id, -23453) owning_org_id,'||LF||
' nvl(msi.inventory_item_id, -23453) inventory_item_id,'||LF||
' nvl(msi.list_price,0)*(1-nvl(msi.average_discount,0)/100) price,'||LF||
' nvl(mpc.customer_id, -23453) customer_id,'||LF||
' nvl(mpc.customer_site_id, -23453) customer_site_id,'||LF||
' nvl(mpc.region_id, -23453) region_id,'||LF||
' nvl(mdc.demand_class, ''-23453'') demand_class,'||LF||
' t.record_type,'||LF||
' t.sdate,'||LF||
' t.future_start,'||LF||
' '||list_measures(2, 't', p_fact_type, p_publisher)||LF||
' t.dummy'||LF||
' from'||LF||
' (select /*+ ordered */'||LF||
' '||list_dimensions(2, 't', p_publisher)||LF||
' nvl(mtp.sr_instance_id, '||l_sr_instance_id||') sr_instance_id,'||LF||
' nvl(mtp.sr_tp_id, -23453) organization_id,'||LF||
' nvl(mtp.currency_code, '''||l_owning_currency_code||''') currency_code,'||LF||
' t.future_start,'||LF||
' '||list_measures(2, 't', p_fact_type, p_publisher)||LF||
' t.dummy'||LF||
' from'||LF||
' (select '||list_dimensions(1, 't', p_publisher)||LF||
' (case when record_type<>lag(record_type) over(partition by '||list_dimensions(3, 't', p_publisher)||' 0 order by sdate) then 1 else 0 end) future_start,'||LF||
' '||list_measures(1, 't', p_fact_type, p_publisher)||LF||
' null dummy'||LF||
' from '||g_demantra_schema||'.'||source_table(p_publisher)||' t'||LF||
' group by '||list_dimensions(1, 't', p_publisher)||'0'||LF||
' ) t,'||LF||
' msc_trading_partners mtp'||LF||
' where t.'||l_organization_column||'=mtp.organization_code(+)'||LF||
' and mtp.partner_type(+)=3'||LF||
' ) t,'||LF||
' msc_system_items msi,'||LF||
' msc_phub_customers_mv mpc,'||LF||
' (select sr_instance_id, meaning, demand_class from msc_demand_classes'||LF||
' union all select instance_id, ''0'', ''-23453'' from msc_apps_instances'||LF||
' union all select instance_id, ''N/A'', ''-23453'' from msc_apps_instances) mdc'||LF||
' where msi.plan_id(+)=-1'||LF||
' and t.'||l_dkey_item_column||'=msi.inventory_item_id(+)'||LF;
msc_phub_util.log('msc_demantra_pkg.archive: insert into '||l_target_table||': '||sql%rowcount);
delete from msc_demantra_ods_f f where exists (
select 1 from msc_st_demantra_ods_f
where st_transaction_id=p_transfer_id and error_code=0
and sr_instance_id=f.sr_instance_id
and organization_id=f.organization_id
and owning_inst_id=f.owning_inst_id
and owning_org_id=f.owning_org_id
and inventory_item_id=f.inventory_item_id
and customer_id=f.customer_id
and customer_site_id=f.customer_site_id
and region_id=f.region_id
and demand_class=f.demand_class
and end_date=f.end_date
);
msc_phub_util.log('msc_demantra_pkg.archive: delete from msc_demantra_ods_f: '||sql%rowcount);
delete from msc_st_demantra_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_demantra_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' owning_inst_id,'||
' owning_org_id,'||
' inventory_item_id,'||
' customer_id,'||
' customer_site_id,'||
' region_id,'||
' organization_code,'||
' owning_org_code,'||
' item_name,'||
' customer_name,'||
' customer_site_code,'||
' zone,'||
' demand_class,'||
' start_date,'||
' consensus_fcst,'||
' consensus_fcst_value,'||
' consensus_fcst_value2,'||
' consensus_fcst_cum,'||
' priority,'||
' annual_plan_value,'||
' annual_plan_value2,'||
' booking_fcst,'||
' booking_fcst_value,'||
' booking_fcst_value2,'||
' booking_fcst_cum,'||
' budget,'||
' budget2,'||
' budget_cum,'||
' budget2_cum,'||
' final_fcst,'||
' final_fcst_value,'||
' final_fcst_value2,'||
' final_fcst_cum,'||
' financial_fcst_value,'||
' financial_fcst_value2,'||
' mktg_fcst,'||
' mktg_fcst_value,'||
' mktg_fcst_value2,'||
' mktg_fcst_cum,'||
' projected_backlog,'||
' projected_backlog_value,'||
' projected_backlog_value2,'||
' sales_fcst,'||
' sales_fcst_value,'||
' sales_fcst_value2,'||
' sales_fcst_cum,'||
' shipment_fcst,'||
' shipment_fcst_value,'||
' shipment_fcst_value2,'||
' shipment_fcst_cum,'||
' attribute1,'||
' attribute2,'||
' attribute3,'||
' attribute4,'||
' attribute5,'||
' attribute6,'||
' attribute7,'||
' attribute8,'||
' attribute9,'||
' attribute10,'||
' created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.owning_inst_id,'||
' f.owning_org_id,'||
' f.inventory_item_id,'||
' f.customer_id,'||
' f.customer_site_id,'||
' f.region_id,'||
' mtp.organization_code,'||
' mtp2.organization_code,'||
' mi.item_name,'||
' decode(f.customer_id, -23453, null, cmv.customer_name),'||
' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
' decode(f.region_id, -23453, null, cmv.zone),'||
' f.demand_class,'||
' f.start_date,'||
' f.consensus_fcst,'||
' f.consensus_fcst_value,'||
' f.consensus_fcst_value2,'||
' f.consensus_fcst_cum,'||
' f.priority,'||
' f.annual_plan_value,'||
' f.annual_plan_value2,'||
' f.booking_fcst,'||
' f.booking_fcst_value,'||
' f.booking_fcst_value2,'||
' f.booking_fcst_cum,'||
' f.budget,'||
' f.budget2,'||
' f.budget_cum,'||
' f.budget2_cum,'||
' f.final_fcst,'||
' f.final_fcst_value,'||
' f.final_fcst_value2,'||
' f.final_fcst_cum,'||
' f.financial_fcst_value,'||
' f.financial_fcst_value2,'||
' f.mktg_fcst,'||
' f.mktg_fcst_value,'||
' f.mktg_fcst_value2,'||
' f.mktg_fcst_cum,'||
' f.projected_backlog,'||
' f.projected_backlog_value,'||
' f.projected_backlog_value2,'||
' f.sales_fcst,'||
' f.sales_fcst_value,'||
' f.sales_fcst_value2,'||
' f.sales_fcst_cum,'||
' f.shipment_fcst,'||
' f.shipment_fcst_value,'||
' f.shipment_fcst_value2,'||
' f.shipment_fcst_cum,'||
' f.attribute1,'||
' f.attribute2,'||
' f.attribute3,'||
' f.attribute4,'||
' f.attribute5,'||
' f.attribute6,'||
' f.attribute7,'||
' f.attribute8,'||
' f.attribute9,'||
' f.attribute10,'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' '||l_apps_schema||'.msc_demantra_f'||l_suffix||' f,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv'||
' where f.plan_run_id=:p_plan_run_id'||
' and f.aggr_type=0'||
' and mtp.partner_type(+)=3'||
' and mtp.sr_instance_id(+)=f.sr_instance_id'||
' and mtp.sr_tp_id(+)=f.organization_id'||
' and mtp2.partner_type(+)=3'||
' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
' and mtp2.sr_tp_id(+)=f.owning_org_id'||
' and mi.inventory_item_id(+)=f.inventory_item_id'||
' and cmv.customer_id(+)=f.customer_id'||
' and cmv.customer_site_id(+)=f.customer_site_id'||
' and cmv.region_id(+)=f.region_id'||
' and cmv.sr_instance_id(+)=decode(f.region_id,-23453,cmv.sr_instance_id(+),f.owning_inst_id)';
msc_phub_util.log('msc_demantra_pkg.export_demantra_f: insert into msc_st_demantra_f: '||sql%rowcount);
' select min(start_date), max(start_date)'||
' from '||l_apps_schema||'.msc_demantra_f'||l_suffix||
' where plan_run_id=:p_plan_run_id and aggr_type=0';
' insert into msc_st_demantra_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,'||
' owning_inst_id,'||
' owning_org_id,'||
' inventory_item_id,'||
' customer_id,'||
' customer_site_id,'||
' region_id,'||
' organization_code,'||
' owning_org_code,'||
' item_name,'||
' customer_name,'||
' customer_site_code,'||
' zone,'||
' demand_class,'||
' start_date,'||
' consensus_fcst,'||
' consensus_fcst_value,'||
' consensus_fcst_value2,'||
' consensus_fcst_cum,'||
' priority,'||
' booking_fcst,'||
' booking_fcst_cum,'||
' booking_fcst_value,'||
' booking_fcst_value2,'||
' budget,'||
' budget2,'||
' budget2_cum,'||
' budget_cum,'||
' mktg_fcst,'||
' mktg_fcst_cum,'||
' mktg_fcst_value,'||
' mktg_fcst_value2,'||
' projected_backlog,'||
' sales_fcst,'||
' sales_fcst_cum,'||
' sales_fcst_value,'||
' sales_fcst_value2,'||
' shipment_fcst,'||
' shipment_fcst_cum,'||
' shipment_fcst_value,'||
' shipment_fcst_value2,'||
' created_by, creation_date,'||
' last_updated_by, last_update_date, last_update_login)'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.owning_inst_id,'||
' f.owning_org_id,'||
' f.inventory_item_id,'||
' f.customer_id,'||
' f.customer_site_id,'||
' f.region_id,'||
' mtp.organization_code,'||
' mtp2.organization_code,'||
' mi.item_name,'||
' decode(f.customer_id, -23453, null, cmv.customer_name),'||
' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
' decode(f.region_id, -23453, null, cmv.zone),'||
' f.demand_class,'||
' f.start_date,'||
' sum(f.consensus_fcst),'||
' sum(f.consensus_fcst_value),'||
' sum(f.consensus_fcst_value2),'||
' sum(f.consensus_fcst_cum),'||
' min(f.priority),'||
' sum(f.booking_fcst),'||
' sum(f.booking_fcst_cum),'||
' sum(f.booking_fcst_value),'||
' sum(f.booking_fcst_value2),'||
' sum(f.budget),'||
' sum(f.budget2),'||
' sum(f.budget2_cum),'||
' sum(f.budget_cum),'||
' sum(f.mktg_fcst),'||
' sum(f.mktg_fcst_cum),'||
' sum(f.mktg_fcst_value),'||
' sum(f.mktg_fcst_value2),'||
' sum(f.projected_backlog),'||
' sum(f.sales_fcst),'||
' sum(f.sales_fcst_cum),'||
' sum(f.sales_fcst_value),'||
' sum(f.sales_fcst_value2),'||
' sum(f.shipment_fcst),'||
' sum(f.shipment_fcst_cum),'||
' sum(f.shipment_fcst_value),'||
' sum(f.shipment_fcst_value2),'||
' fnd_global.user_id, sysdate,'||
' fnd_global.user_id, sysdate, fnd_global.login_id'||
' from'||
' (select'||
' sr_instance_id,'||
' organization_id,'||
' owning_inst_id,'||
' owning_org_id,'||
' inventory_item_id,'||
' customer_id,'||
' customer_site_id,'||
' region_id,'||
' demand_class,'||
' start_date,'||
' consensus_fcst,'||
' consensus_fcst_value,'||
' consensus_fcst_value2,'||
' consensus_fcst_cum,'||
' priority,'||
' to_number(null) booking_fcst,'||
' to_number(null) booking_fcst_cum,'||
' to_number(null) booking_fcst_value,'||
' to_number(null) booking_fcst_value2,'||
' to_number(null) budget,'||
' to_number(null) budget2,'||
' to_number(null) budget2_cum,'||
' to_number(null) budget_cum,'||
' to_number(null) mktg_fcst,'||
' to_number(null) mktg_fcst_cum,'||
' to_number(null) mktg_fcst_value,'||
' to_number(null) mktg_fcst_value2,'||
' to_number(null) projected_backlog,'||
' to_number(null) sales_fcst,'||
' to_number(null) sales_fcst_cum,'||
' to_number(null) sales_fcst_value,'||
' to_number(null) sales_fcst_value2,'||
' to_number(null) shipment_fcst,'||
' to_number(null) shipment_fcst_cum,'||
' to_number(null) shipment_fcst_value,'||
' to_number(null) shipment_fcst_value2'||
' from '||l_apps_schema||'.msc_demantra_f'||l_suffix||
' where plan_run_id=:p_plan_run_id and aggr_type=0'||
' union all'||
' select'||
' sr_instance_id,'||
' organization_id,'||
' sr_instance_id owning_inst_id,'||
' organization_id owning_org_id,'||
' inventory_item_id,'||
' customer_id,'||
' customer_site_id,'||
' region_id,'||
' demand_class,'||
' end_date,'||
' to_number(null) consensus_fcst,'||
' to_number(null) consensus_fcst_value,'||
' to_number(null) consensus_fcst_value2,'||
' to_number(null) consensus_fcst_cum,'||
' to_number(null) priority,'||
' booking_fcst,'||
' booking_fcst_cum,'||
' booking_fcst_value,'||
' booking_fcst_value2,'||
' budget,'||
' budget2,'||
' budget2_cum,'||
' budget_cum,'||
' mktg_fcst,'||
' mktg_fcst_cum,'||
' mktg_fcst_value,'||
' mktg_fcst_value2,'||
' projected_backlog,'||
' sales_fcst,'||
' sales_fcst_cum,'||
' sales_fcst_value,'||
' sales_fcst_value2,'||
' shipment_fcst,'||
' shipment_fcst_cum,'||
' shipment_fcst_value,'||
' shipment_fcst_value2'||
' from '||l_apps_schema||'.msc_demantra_ods_f'||l_suffix||
' where end_date between :p_plan_start_date and :p_plan_cutoff_date'||
' ) f,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv'||
' where mtp.partner_type(+)=3'||
' and mtp.sr_instance_id(+)=f.sr_instance_id'||
' and mtp.sr_tp_id(+)=f.organization_id'||
' and mtp2.partner_type(+)=3'||
' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
' and mtp2.sr_tp_id(+)=f.owning_org_id'||
' and mi.inventory_item_id(+)=f.inventory_item_id'||
' and cmv.customer_id(+)=f.customer_id'||
' and cmv.customer_site_id(+)=f.customer_site_id'||
' and cmv.region_id(+)=f.region_id'||
' group by'||
' f.sr_instance_id,'||
' f.organization_id,'||
' f.owning_inst_id,'||
' f.owning_org_id,'||
' f.inventory_item_id,'||
' f.customer_id,'||
' f.customer_site_id,'||
' f.region_id,'||
' mtp.organization_code,'||
' mtp2.organization_code,'||
' mi.item_name,'||
' decode(f.customer_id, -23453, null, cmv.customer_name),'||
' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
' decode(f.region_id, -23453, null, cmv.zone),'||
' f.demand_class,'||
' f.start_date';
msc_phub_util.log('msc_demantra_pkg.export_demantra_f: insert into msc_st_demantra_f: '||sql%rowcount);
delete from msc_st_demantra_ods_f where st_transaction_id=p_st_transaction_id;
' insert into msc_st_demantra_ods_f('||
' st_transaction_id,'||
' error_code,'||
' sr_instance_id,'||
' organization_id,';
' last_updated_by, last_update_date, last_update_login'||
' )'||
' select'||
' :p_st_transaction_id,'||
' 0,'||
' f.sr_instance_id,'||
' f.organization_id,';