eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.MSC_DEMANTRA_PKG dependencies on MSC_PHUB_UTIL

Line 10: g_demantra_schema varchar2(30) := msc_phub_util.demantra_schema;

6: g_dimension_columns constant varchar2(200) := '''DKEY_ITEM'', ''DKEY_SITE'', ''LEVEL1'', ''LEVEL2'', ''LEVEL3'', ''LEVEL4'', ''RECORD_TYPE'', ''SDATE''';
7: g_msg_num_prefix1 constant varchar2(80) := 'CN_Advanced_Planning_-_Item_Custom_Level_';
8: g_msg_num_prefix2 constant varchar2(80) := 'CN_Advanced_Planning_-_Forecasting_';
9:
10: g_demantra_schema varchar2(30) := msc_phub_util.demantra_schema;
11: g_msc_schema varchar2(30) := msc_phub_util.msc_schema;
12:
13: procedure log_dbex(p_message varchar2, p_caller varchar2)
14: is

Line 11: g_msc_schema varchar2(30) := msc_phub_util.msc_schema;

7: g_msg_num_prefix1 constant varchar2(80) := 'CN_Advanced_Planning_-_Item_Custom_Level_';
8: g_msg_num_prefix2 constant varchar2(80) := 'CN_Advanced_Planning_-_Forecasting_';
9:
10: g_demantra_schema varchar2(30) := msc_phub_util.demantra_schema;
11: g_msc_schema varchar2(30) := msc_phub_util.msc_schema;
12:
13: procedure log_dbex(p_message varchar2, p_caller varchar2)
14: is
15: begin

Line 19: msc_phub_util.log('msc_demantra_pkg.log_dbex: '||sqlerrm);

15: begin
16: execute immediate 'begin '||g_demantra_schema||'.dbex(:1, :2); end;' using p_message, p_caller;
17: exception
18: when others then
19: msc_phub_util.log('msc_demantra_pkg.log_dbex: '||sqlerrm);
20: end log_dbex;
21:
22: procedure truncate_ods_f
23: is

Line 30: msc_phub_util.log('msc_demantra_pkg.truncate_ods_f');

26: dummy1 varchar2(50);
27: dummy2 varchar2(50);
28: e_truncate_ods_f exception;
29: begin
30: msc_phub_util.log('msc_demantra_pkg.truncate_ods_f');
31: if (fnd_installation.get_app_info('FND', dummy1, dummy2, l_applsys_schema) = false) then
32: msc_phub_util.log(fnd_message.get_string('MSC', 'MSC_PART_UNDEFINED_SCHEMA'));
33: raise e_truncate_ods_f;
34: end if;

Line 32: msc_phub_util.log(fnd_message.get_string('MSC', 'MSC_PART_UNDEFINED_SCHEMA'));

28: e_truncate_ods_f exception;
29: begin
30: msc_phub_util.log('msc_demantra_pkg.truncate_ods_f');
31: if (fnd_installation.get_app_info('FND', dummy1, dummy2, l_applsys_schema) = false) then
32: msc_phub_util.log(fnd_message.get_string('MSC', 'MSC_PART_UNDEFINED_SCHEMA'));
33: raise e_truncate_ods_f;
34: end if;
35:
36: l_ddl := 'truncate table msc_demantra_ods_f';

Line 42: msc_phub_util.log('msc_demantra_pkg.truncate_ods_f: '||sqlerrm);

38: commit;
39:
40: exception
41: when others then
42: msc_phub_util.log('msc_demantra_pkg.truncate_ods_f: '||sqlerrm);
43: raise;
44: end truncate_ods_f;
45:
46: procedure create_item_hierarchy_view

Line 49: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;

45:
46: procedure create_item_hierarchy_view
47: is
48: l_sql varchar2(1024);
49: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
50: begin
51: msc_phub_util.log('msc_demantra_pkg.create_item_hierarchy_view');
52: if (g_demantra_schema is null) then
53: l_sql :=

Line 51: msc_phub_util.log('msc_demantra_pkg.create_item_hierarchy_view');

47: is
48: l_sql varchar2(1024);
49: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
50: begin
51: msc_phub_util.log('msc_demantra_pkg.create_item_hierarchy_view');
52: if (g_demantra_schema is null) then
53: l_sql :=
54: ' create or replace view '||l_apps_schema||'.msc_dmtra_item_hies_v as'||
55: ' select cast(null as varchar2(100)) level_name from dual where 1=2';

Line 76: msc_phub_util.log('msc_demantra_pkg.create_item_hierarchy_view: '||sqlerrm);

72: end if;
73: execute immediate l_sql;
74: exception
75: when others then
76: msc_phub_util.log('msc_demantra_pkg.create_item_hierarchy_view: '||sqlerrm);
77: end create_item_hierarchy_view;
78:
79:
80: function list_result(p_sql varchar2, p_delim varchar2)

Line 150: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy');

146: l_sql varchar2(16384);
147: l_sql2 varchar2(16384);
148: l_n number;
149: begin
150: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy');
151: if (g_demantra_schema is null) then
152: msc_phub_util.log('(g_demantra_schema is null)');
153: return;
154: end if;

Line 152: msc_phub_util.log('(g_demantra_schema is null)');

148: l_n number;
149: begin
150: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy');
151: if (g_demantra_schema is null) then
152: msc_phub_util.log('(g_demantra_schema is null)');
153: return;
154: end if;
155:
156: delete from msc_apcc_custom_hier;

Line 157: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: delete from msc_apcc_custom_hier: '||sql%rowcount);

153: return;
154: end if;
155:
156: delete from msc_apcc_custom_hier;
157: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: delete from msc_apcc_custom_hier: '||sql%rowcount);
158: commit;
159:
160: delete from msc_translated_message where lang_id='00' and msg_num like g_msg_num_prefix1||'%';
161: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: delete from msc_translated_message: '||sql%rowcount);

Line 161: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: delete from msc_translated_message: '||sql%rowcount);

157: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: delete from msc_apcc_custom_hier: '||sql%rowcount);
158: commit;
159:
160: delete from msc_translated_message where lang_id='00' and msg_num like g_msg_num_prefix1||'%';
161: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: delete from msc_translated_message: '||sql%rowcount);
162: commit;
163:
164: l_sql :=
165: ' select rownum level_id, gtable, cust_code_field, gtable2, id_field2, table_label'||LF||

Line 172: msc_phub_util.log_sql(l_sql);

168: ' from '||g_demantra_schema||'.group_tables'||LF||
169: ' connect by group_table_id=prior father_id'||LF||
170: ' start with table_label='''||l_item_hierarchy||''''||LF||
171: ' order by level desc)'||LF;
172: msc_phub_util.log_sql(l_sql);
173:
174: l_sql2 := 'select count(1) from ('||l_sql||')';
175: execute immediate l_sql2 into l_n;
176: if (l_n > 0) then

Line 198: msc_phub_util.log_sql(l_sql2);

194: ' where'||LF||
195: ' '||list_result('select gtable||''.''||id_field2||''=''||gtable2||''.''||id_field2 from ('||l_sql||') where gtable2 is not null', ' and '||LF)||
196: ' t_ep_item.ebs_item_dest_key is not null';
197:
198: msc_phub_util.log_sql(l_sql2);
199: execute immediate l_sql2;
200: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: insert into msc_apcc_custom_hier: '||sql%rowcount);
201: commit;
202:

Line 200: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: insert into msc_apcc_custom_hier: '||sql%rowcount);

196: ' t_ep_item.ebs_item_dest_key is not null';
197:
198: msc_phub_util.log_sql(l_sql2);
199: execute immediate l_sql2;
200: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: insert into msc_apcc_custom_hier: '||sql%rowcount);
201: commit;
202:
203: l_sql2 :=
204: ' insert into msc_translated_message(lang_id, msg_num, msg_text)'||LF||

Line 208: msc_phub_util.log_sql(l_sql2);

204: ' insert into msc_translated_message(lang_id, msg_num, msg_text)'||LF||
205: ' select ''00'', '''||g_msg_num_prefix1||'''||substr(to_char(level_id,''09''),2), table_label'||LF||
206: ' from ('||l_sql||')';
207:
208: msc_phub_util.log_sql(l_sql2);
209: execute immediate l_sql2;
210: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: insert into msc_translated_message: '||sql%rowcount);
211: commit;
212: end if;

Line 210: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: insert into msc_translated_message: '||sql%rowcount);

206: ' from ('||l_sql||')';
207:
208: msc_phub_util.log_sql(l_sql2);
209: execute immediate l_sql2;
210: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: insert into msc_translated_message: '||sql%rowcount);
211: commit;
212: end if;
213:
214: exception

Line 216: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: '||sqlerrm);

212: end if;
213:
214: exception
215: when others then
216: msc_phub_util.log('msc_demantra_pkg.build_custom_hierarchy: '||sqlerrm);
217: raise;
218: end build_custom_hierarchy;
219:
220: procedure init_cust_measures_table

Line 223: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table');

219:
220: procedure init_cust_measures_table
221: is
222: begin
223: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table');
224:
225: merge into msc_apcc_custom_measures b
226: using
227: (select m.fact_type, f.column_name

Line 249: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table: merge into msc_apcc_custom_measures: '||sql%rowcount);

245: fnd_global.user_id, sysdate,
246: sysdate, fnd_global.user_id, fnd_global.login_id,
247: fnd_global.conc_program_id, fnd_global.conc_login_id,
248: fnd_global.prog_appl_id, fnd_global.conc_request_id);
249: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table: merge into msc_apcc_custom_measures: '||sql%rowcount);
250: commit;
251:
252: delete from msc_apcc_custom_measures b
253: where not exists

Line 264: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table: delete from msc_apcc_custom_measures: '||sql%rowcount);

260: and f.owner=syn.table_owner
261: and f.table_name=syn.table_name
262: and f.column_name=b.column_name
263: and m.fact_type=b.fact_type);
264: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table: delete from msc_apcc_custom_measures: '||sql%rowcount);
265: commit;
266:
267: msc_phub_util.log(fnd_message.get_string('MSC','MSC_APCC_DM_R01'));
268:

Line 267: msc_phub_util.log(fnd_message.get_string('MSC','MSC_APCC_DM_R01'));

263: and m.fact_type=b.fact_type);
264: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table: delete from msc_apcc_custom_measures: '||sql%rowcount);
265: commit;
266:
267: msc_phub_util.log(fnd_message.get_string('MSC','MSC_APCC_DM_R01'));
268:
269: exception
270: when others then
271: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table: '||sqlerrm);

Line 271: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table: '||sqlerrm);

267: msc_phub_util.log(fnd_message.get_string('MSC','MSC_APCC_DM_R01'));
268:
269: exception
270: when others then
271: msc_phub_util.log('msc_demantra_pkg.init_cust_measures_table: '||sqlerrm);
272: raise;
273: end init_cust_measures_table;
274:
275: procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,

Line 278: l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);

274:
275: procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
276: p_plan_id number, p_plan_run_id number)
277: is
278: l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
279: begin
280: msc_phub_util.log('msc_demantra_pkg.populate_details');
281: retcode := 0;
282: errbuf := '';

Line 280: msc_phub_util.log('msc_demantra_pkg.populate_details');

276: p_plan_id number, p_plan_run_id number)
277: is
278: l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
279: begin
280: msc_phub_util.log('msc_demantra_pkg.populate_details');
281: retcode := 0;
282: errbuf := '';
283:
284: insert into msc_demantra_f (

Line 405: msc_phub_util.log('msc_demantra_pkg.populate_details: insert='||sql%rowcount);

401: where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
402: and mcc.from_currency(+) = t.currency_code
403: and mcc.calendar_date(+) = t.start_date;
404:
405: msc_phub_util.log('msc_demantra_pkg.populate_details: insert='||sql%rowcount);
406: commit;
407:
408: summarize_demantra_f(errbuf, retcode, p_plan_id, p_plan_run_id);
409:

Line 416: msc_phub_util.log(errbuf);

412: if (retcode = 0) then
413: retcode := 2;
414: errbuf := 'msc_demantra_pkg.populate_details: '||sqlerrm;
415: end if;
416: msc_phub_util.log(errbuf);
417: end populate_details;
418:
419: procedure register_custom_measures
420: is

Line 433: msc_phub_util.log('msc_demantra_pkg.register_custom_measures');

429: l_column_name varchar2(30);
430: l_msg_num varchar2(80);
431: l_qid_wf number;
432: begin
433: msc_phub_util.log('msc_demantra_pkg.register_custom_measures');
434: init_cust_measures_table;
435:
436: if (g_demantra_schema is null) then
437: msc_phub_util.log('(g_demantra_schema is null)');

Line 437: msc_phub_util.log('(g_demantra_schema is null)');

433: msc_phub_util.log('msc_demantra_pkg.register_custom_measures');
434: init_cust_measures_table;
435:
436: if (g_demantra_schema is null) then
437: msc_phub_util.log('(g_demantra_schema is null)');
438: return;
439: end if;
440:
441: select msc_hub_query_s.nextval into l_qid_wf from dual;

Line 453: msc_phub_util.log_sql(l_sql);

449: ' fnd_global.user_id, sysdate,'||LF||
450: ' sysdate, fnd_global.user_id, fnd_global.login_id'||LF||
451: ' from '||g_demantra_schema||'.wf_schemas';
452:
453: msc_phub_util.log_sql(l_sql);
454: execute immediate l_sql;
455: commit;
456:
457: l_sql :=

Line 494: msc_phub_util.log_sql(l_sql);

490: ' ) v'||LF||
491: ' on (b.fact_type=v.fact_type and b.source_column_name=v.computed_name)'||LF||
492: ' order by 3,1';
493:
494: msc_phub_util.log_sql(l_sql);
495:
496: open c for l_sql;
497: loop
498: fetch c into l_fact_type, l_op, l_source_column_name, l_column_title, l_aggregation_method;

Line 501: msc_phub_util.log('('||l_fact_type||','||','||l_op||','||l_source_column_name||','||l_column_title||','||l_aggregation_method||')');

497: loop
498: fetch c into l_fact_type, l_op, l_source_column_name, l_column_title, l_aggregation_method;
499: exit when c%notfound;
500:
501: msc_phub_util.log('('||l_fact_type||','||','||l_op||','||l_source_column_name||','||l_column_title||','||l_aggregation_method||')');
502: begin
503: case when l_op='D' then
504: update msc_apcc_custom_measures
505: set source_column_name=null,

Line 553: msc_phub_util.log('msc_demantra_pkg.register_custom_measures: '||sqlerrm);

549:
550: end case;
551: exception
552: when others then
553: msc_phub_util.log('msc_demantra_pkg.register_custom_measures: '||sqlerrm);
554: end;
555: end loop;
556: close c;
557:

Line 559: msc_phub_util.log('msc_demantra_pkg.register_custom_measures: delete from msc_translated_message: '||sql%rowcount);

555: end loop;
556: close c;
557:
558: delete from msc_translated_message where lang_id='00' and msg_num like g_msg_num_prefix2||'%';
559: msc_phub_util.log('msc_demantra_pkg.register_custom_measures: delete from msc_translated_message: '||sql%rowcount);
560: commit;
561:
562: insert into msc_translated_message(lang_id, msg_num, msg_text)
563: select

Line 571: msc_phub_util.log('msc_demantra_pkg.register_custom_measures: insert into msc_translated_message: '||sql%rowcount);

567: from msc_apcc_custom_measures f, table(msc_phub_pkg.meta_info) m
568: where f.fact_type=m.fact_type
569: and f.source_column_name is not null
570: order by to_number(substr(f.column_name,10));
571: msc_phub_util.log('msc_demantra_pkg.register_custom_measures: insert into msc_translated_message: '||sql%rowcount);
572: commit;
573:
574: exception
575: when others then

Line 576: msc_phub_util.log('msc_demantra_pkg.register_custom_measures: '||sqlerrm);

572: commit;
573:
574: exception
575: when others then
576: msc_phub_util.log('msc_demantra_pkg.register_custom_measures: '||sqlerrm);
577: raise;
578: end register_custom_measures;
579:
580: function seeded_measure_mapping return msc_apcc_measure_map_table

Line 813: msc_phub_util.log('msc_demantra_pkg.find_dimension_columns'||

809: if (p_zone_column <> DUMMY) then
810: p_dkey_site_column := DUMMY;
811: end if;
812:
813: msc_phub_util.log('msc_demantra_pkg.find_dimension_columns'||
814: ':p_organization_column='||p_organization_column||
815: ',p_demand_class_column='||p_demand_class_column||
816: ',p_dkey_item_column='||p_dkey_item_column||
817: ',p_dkey_site_column='||p_dkey_site_column||

Line 827: msc_phub_util.log('msc_demantra_pkg.summarize');

823: is
824: l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
825: l_sql varchar2(16384);
826: begin
827: msc_phub_util.log('msc_demantra_pkg.summarize');
828:
829: if (p_fact_type <> 5) then
830: return;
831: end if;

Line 835: msc_phub_util.log('msc_demantra_pkg.summarize, delete='||sql%rowcount);

831: end if;
832:
833: delete from msc_demantra_f
834: where plan_id=p_plan_id and plan_run_id=p_plan_run_id and aggr_type>0;
835: msc_phub_util.log('msc_demantra_pkg.summarize, delete='||sql%rowcount);
836: commit;
837:
838: l_sql :=
839: ' insert into msc_demantra_f ('||LF||

Line 881: msc_phub_util.log_sql(l_sql);

877: ' f.customer_id, f.customer_site_id, f.region_id,'||LF||
878: ' f.demand_class, f.start_date,'||LF||
879: ' nvl(q.sr_category_id, -23453)';
880:
881: msc_phub_util.log_sql(l_sql);
882: execute immediate l_sql;
883: msc_phub_util.log('msc_demantra_pkg.summarize, level1='||sql%rowcount);
884: commit;
885:

Line 883: msc_phub_util.log('msc_demantra_pkg.summarize, level1='||sql%rowcount);

879: ' nvl(q.sr_category_id, -23453)';
880:
881: msc_phub_util.log_sql(l_sql);
882: execute immediate l_sql;
883: msc_phub_util.log('msc_demantra_pkg.summarize, level1='||sql%rowcount);
884: commit;
885:
886: exception
887: when others then

Line 888: msc_phub_util.log('msc_demantra_pkg.summarize: '||sqlerrm);

884: commit;
885:
886: exception
887: when others then
888: msc_phub_util.log('msc_demantra_pkg.summarize: '||sqlerrm);
889: raise;
890: end summarize;
891:
892: procedure append_from_staging(p_plan_id number, p_plan_run_id number, p_fact_type number, p_st_transaction_id number)

Line 900: msc_phub_util.log('msc_demantra_pkg.append_from_staging');

896: l_staging_table varchar2(30) := staging_table(p_fact_type);
897: l_plan_cols1 varchar2(200);
898: l_plan_cols2 varchar2(200);
899: begin
900: msc_phub_util.log('msc_demantra_pkg.append_from_staging');
901:
902: if (p_fact_type = 5) then
903: l_plan_cols1 := 'plan_id, plan_run_id, aggr_type, category_set_id, sr_category_id, start_date, ';
904: l_plan_cols2 := p_plan_id||', '||p_plan_run_id||', 0, -23453, -23453, t.start_date, ';

Line 950: msc_phub_util.log_sql(l_sql);

946: ' fnd_global.prog_appl_id, fnd_global.conc_request_id'||LF||
947: ' from '||l_staging_table||' t'||LF||
948: ' where st_transaction_id='||p_st_transaction_id||' and error_code=0';
949:
950: msc_phub_util.log_sql(l_sql);
951: execute immediate l_sql;
952: msc_phub_util.log('msc_demantra_pkg.append_from_staging: insert into '||l_target_table||': '||sql%rowcount);
953: commit;
954:

Line 952: msc_phub_util.log('msc_demantra_pkg.append_from_staging: insert into '||l_target_table||': '||sql%rowcount);

948: ' where st_transaction_id='||p_st_transaction_id||' and error_code=0';
949:
950: msc_phub_util.log_sql(l_sql);
951: execute immediate l_sql;
952: msc_phub_util.log('msc_demantra_pkg.append_from_staging: insert into '||l_target_table||': '||sql%rowcount);
953: commit;
954:
955: exception
956: when others then

Line 957: msc_phub_util.log('msc_demantra_pkg.append_from_staging: '||sqlerrm);

953: commit;
954:
955: exception
956: when others then
957: msc_phub_util.log('msc_demantra_pkg.append_from_staging: '||sqlerrm);
958: raise;
959: end append_from_staging;
960:
961: procedure archive(p_plan_id number, p_plan_run_id number, p_fact_type number, p_publisher varchar2, p_transfer_id number)

Line 970: l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(0);

966: l_dkey_item_column varchar2(30);
967: l_dkey_site_column varchar2(30);
968: l_zone_column varchar2(30);
969:
970: l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(0);
971: l_sr_instance_id number := fnd_profile.value('MSD_DEM_SR_INSTANCE_FOR_GLOBAL_FCST');
972:
973: l_target_table varchar2(30) := fact_table(p_fact_type);
974: l_publish_mode number := publish_mode(p_publisher);

Line 981: msc_phub_util.log('msc_demantra_pkg.archive('||

977: l_plan_cols2 varchar2(200);
978: e_archive exception;
979: l_start_time timestamp := systimestamp;
980: begin
981: msc_phub_util.log('msc_demantra_pkg.archive('||
982: p_plan_id||','||p_plan_run_id||','||p_fact_type||','||p_publisher||','||p_transfer_id||')');
983: if (g_demantra_schema is null) then
984: msc_phub_util.log('(g_demantra_schema is null)');
985: return;

Line 984: msc_phub_util.log('(g_demantra_schema is null)');

980: begin
981: msc_phub_util.log('msc_demantra_pkg.archive('||
982: p_plan_id||','||p_plan_run_id||','||p_fact_type||','||p_publisher||','||p_transfer_id||')');
983: if (g_demantra_schema is null) then
984: msc_phub_util.log('(g_demantra_schema is null)');
985: return;
986: end if;
987:
988: if (source_table(p_publisher) is null) then

Line 989: msc_phub_util.log('(source_table(p_publisher) is null)');

985: return;
986: end if;
987:
988: if (source_table(p_publisher) is null) then
989: msc_phub_util.log('(source_table(p_publisher) is null)');
990: raise e_archive;
991: end if;
992:
993: msc_phub_util.log('l_publish_mode='||l_publish_mode);

Line 993: msc_phub_util.log('l_publish_mode='||l_publish_mode);

989: msc_phub_util.log('(source_table(p_publisher) is null)');
990: raise e_archive;
991: end if;
992:
993: msc_phub_util.log('l_publish_mode='||l_publish_mode);
994:
995: if (l_sr_instance_id is null) then
996: select min(instance_id) into l_sr_instance_id
997: from msc_apps_instances

Line 1000: msc_phub_util.log('l_sr_instance_id='||l_sr_instance_id);

996: select min(instance_id) into l_sr_instance_id
997: from msc_apps_instances
998: where instance_type<>3 and validation_org_id is not null;
999: end if;
1000: msc_phub_util.log('l_sr_instance_id='||l_sr_instance_id);
1001:
1002:
1003: if (l_publish_mode = 1) then
1004: if (p_fact_type = 4) then

Line 1170: msc_phub_util.log_sql(l_sql);

1166: ' where mcc.to_currency(+)=fnd_profile.value(''MSC_HUB_CUR_CODE_RPT'')'||LF||
1167: ' and mcc.from_currency(+)=t.currency_code'||LF||
1168: ' and mcc.calendar_date(+)=t.sdate';
1169:
1170: msc_phub_util.log_sql(l_sql);
1171: execute immediate l_sql;
1172: msc_phub_util.log('msc_demantra_pkg.archive: insert into '||l_target_table||': '||sql%rowcount);
1173: commit;
1174:

Line 1172: msc_phub_util.log('msc_demantra_pkg.archive: insert into '||l_target_table||': '||sql%rowcount);

1168: ' and mcc.calendar_date(+)=t.sdate';
1169:
1170: msc_phub_util.log_sql(l_sql);
1171: execute immediate l_sql;
1172: msc_phub_util.log('msc_demantra_pkg.archive: insert into '||l_target_table||': '||sql%rowcount);
1173: commit;
1174:
1175: if (p_fact_type = 4 and l_publish_mode = 2) then
1176: delete from msc_demantra_ods_f f where exists (

Line 1190: msc_phub_util.log('msc_demantra_pkg.archive: delete from msc_demantra_ods_f: '||sql%rowcount);

1186: and region_id=f.region_id
1187: and demand_class=f.demand_class
1188: and end_date=f.end_date
1189: );
1190: msc_phub_util.log('msc_demantra_pkg.archive: delete from msc_demantra_ods_f: '||sql%rowcount);
1191: commit;
1192:
1193: append_from_staging(p_plan_id, p_plan_run_id, p_fact_type, p_transfer_id);
1194: end if;

Line 1200: msc_phub_util.log('msc_demantra_pkg.archive.complete, duration='||(systimestamp-l_start_time));

1196: if (p_fact_type = 5) then
1197: summarize(p_plan_id, p_plan_run_id, p_fact_type);
1198: end if;
1199:
1200: msc_phub_util.log('msc_demantra_pkg.archive.complete, duration='||(systimestamp-l_start_time));
1201:
1202: exception
1203: when others then
1204: msc_phub_util.log('msc_demantra_pkg.archive: '||sqlerrm);

Line 1204: msc_phub_util.log('msc_demantra_pkg.archive: '||sqlerrm);

1200: msc_phub_util.log('msc_demantra_pkg.archive.complete, duration='||(systimestamp-l_start_time));
1201:
1202: exception
1203: when others then
1204: msc_phub_util.log('msc_demantra_pkg.archive: '||sqlerrm);
1205: log_dbex(sqlerrm, 'msc_demantra_pkg.archive');
1206: raise;
1207: end archive;
1208:

Line 1221: msc_phub_util.log('msc_demantra_pkg.import_fact');

1217: l_fact_table varchar2(30) := fact_table(p_fact_type);
1218: l_date_column varchar2(30);
1219: l_is_plan_data number;
1220: begin
1221: msc_phub_util.log('msc_demantra_pkg.import_fact');
1222: retcode := 0;
1223: errbuf := null;
1224:
1225: if (p_fact_type not in (4,5)) then

Line 1226: msc_phub_util.log('msc_demantra_pkg.import_fact: (p_fact_type not in (4,5))');

1222: retcode := 0;
1223: errbuf := null;
1224:
1225: if (p_fact_type not in (4,5)) then
1226: msc_phub_util.log('msc_demantra_pkg.import_fact: (p_fact_type not in (4,5))');
1227: return;
1228: end if;
1229:
1230: if (p_fact_type = 4) then

Line 1238: l_result := l_result + msc_phub_util.prepare_staging_dates(

1234: l_is_plan_data := 1;
1235: l_date_column := 'start_date';
1236: end if;
1237:
1238: l_result := l_result + msc_phub_util.prepare_staging_dates(
1239: l_staging_table, l_date_column, p_st_transaction_id,
1240: p_upload_mode, p_overwrite_after_date,
1241: p_plan_start_date, p_plan_cutoff_date);
1242:

Line 1243: l_result := l_result + msc_phub_util.prepare_fact_dates(

1239: l_staging_table, l_date_column, p_st_transaction_id,
1240: p_upload_mode, p_overwrite_after_date,
1241: p_plan_start_date, p_plan_cutoff_date);
1242:
1243: l_result := l_result + msc_phub_util.prepare_fact_dates(
1244: l_fact_table, l_is_plan_data, l_date_column, p_plan_id, p_plan_run_id,
1245: p_upload_mode, p_overwrite_after_date);
1246:
1247: l_result := l_result + msc_phub_util.decode_organization_key(

Line 1247: l_result := l_result + msc_phub_util.decode_organization_key(

1243: l_result := l_result + msc_phub_util.prepare_fact_dates(
1244: l_fact_table, l_is_plan_data, l_date_column, p_plan_id, p_plan_run_id,
1245: p_upload_mode, p_overwrite_after_date);
1246:
1247: l_result := l_result + msc_phub_util.decode_organization_key(
1248: l_staging_table, p_st_transaction_id, p_def_instance_code,
1249: 'sr_instance_id', 'organization_id', 'organization_code');
1250:
1251: l_result := l_result + msc_phub_util.decode_organization_key(

Line 1251: l_result := l_result + msc_phub_util.decode_organization_key(

1247: l_result := l_result + msc_phub_util.decode_organization_key(
1248: l_staging_table, p_st_transaction_id, p_def_instance_code,
1249: 'sr_instance_id', 'organization_id', 'organization_code');
1250:
1251: l_result := l_result + msc_phub_util.decode_organization_key(
1252: l_staging_table, p_st_transaction_id, p_def_instance_code,
1253: 'owning_inst_id', 'owning_org_id', 'owning_org_code');
1254:
1255: l_result := l_result + msc_phub_util.decode_item_key(

Line 1255: l_result := l_result + msc_phub_util.decode_item_key(

1251: l_result := l_result + msc_phub_util.decode_organization_key(
1252: l_staging_table, p_st_transaction_id, p_def_instance_code,
1253: 'owning_inst_id', 'owning_org_id', 'owning_org_code');
1254:
1255: l_result := l_result + msc_phub_util.decode_item_key(
1256: l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
1257:
1258: l_result := l_result + msc_phub_util.decode_customer_key(
1259: l_staging_table, p_st_transaction_id,

Line 1258: l_result := l_result + msc_phub_util.decode_customer_key(

1254:
1255: l_result := l_result + msc_phub_util.decode_item_key(
1256: l_staging_table, p_st_transaction_id, 'inventory_item_id', 'item_name');
1257:
1258: l_result := l_result + msc_phub_util.decode_customer_key(
1259: l_staging_table, p_st_transaction_id,
1260: 'customer_id', 'customer_site_id', 'owning_inst_id', 'region_id',
1261: 'customer_name', 'customer_site_code', 'zone');
1262:

Line 1263: if (p_fact_type = 4 and p_upload_mode <> msc_phub_util.upload_append) then

1259: l_staging_table, p_st_transaction_id,
1260: 'customer_id', 'customer_site_id', 'owning_inst_id', 'region_id',
1261: 'customer_name', 'customer_site_code', 'zone');
1262:
1263: if (p_fact_type = 4 and p_upload_mode <> msc_phub_util.upload_append) then
1264: truncate_ods_f;
1265: end if;
1266:
1267: append_from_staging(p_plan_id, p_plan_run_id, p_fact_type, p_st_transaction_id);

Line 1274: msc_phub_util.log('msc_demantra_pkg.import_fact: complete, retcode='||retcode);

1270: if (l_result > 0) then
1271: retcode := -1;
1272: end if;
1273:
1274: msc_phub_util.log('msc_demantra_pkg.import_fact: complete, retcode='||retcode);
1275: end import_fact;
1276:
1277: procedure import_demantra_ods_f (
1278: errbuf out nocopy varchar2, retcode out nocopy varchar2,

Line 1284: msc_phub_util.log('msc_demantra_pkg.import_demantra_ods_f');

1280: p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
1281: p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
1282: is
1283: begin
1284: msc_phub_util.log('msc_demantra_pkg.import_demantra_ods_f');
1285: import_fact(errbuf, retcode,
1286: p_st_transaction_id, p_plan_id, p_plan_run_id, 4,
1287: p_plan_type, p_plan_start_date, p_plan_cutoff_date,
1288: p_upload_mode, p_overwrite_after_date, p_def_instance_code);

Line 1304: msc_phub_util.log('msc_demantra_pkg.import_demantra_f');

1300: p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
1301: p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
1302: is
1303: begin
1304: msc_phub_util.log('msc_demantra_pkg.import_demantra_f');
1305: import_fact(errbuf, retcode,
1306: p_st_transaction_id, p_plan_id, p_plan_run_id, 5,
1307: p_plan_type, p_plan_start_date, p_plan_cutoff_date,
1308: p_upload_mode, p_overwrite_after_date, p_def_instance_code);

Line 1322: msc_phub_util.log('msc_demantra_pkg.summarize_demantra_f');

1318: p_plan_id number, p_plan_run_id number)
1319: is
1320: l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
1321: begin
1322: msc_phub_util.log('msc_demantra_pkg.summarize_demantra_f');
1323: retcode := 0;
1324: errbuf := '';
1325:
1326: summarize(p_plan_id, p_plan_run_id, 5);

Line 1341: l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);

1337: p_st_transaction_id number, p_plan_run_id number,
1338: p_dblink varchar2, p_source_version varchar2)
1339: is
1340: l_sql varchar2(16384);
1341: l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
1342: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
1343: l_plan_start_date date := null;
1344: l_plan_cutoff_date date := null;
1345: begin

Line 1342: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;

1338: p_dblink varchar2, p_source_version varchar2)
1339: is
1340: l_sql varchar2(16384);
1341: l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
1342: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
1343: l_plan_start_date date := null;
1344: l_plan_cutoff_date date := null;
1345: begin
1346: msc_phub_util.log('msc_demantra_pkg.export_demantra_f');

Line 1346: msc_phub_util.log('msc_demantra_pkg.export_demantra_f');

1342: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
1343: l_plan_start_date date := null;
1344: l_plan_cutoff_date date := null;
1345: begin
1346: msc_phub_util.log('msc_demantra_pkg.export_demantra_f');
1347: retcode := 0;
1348: errbuf := null;
1349:
1350: delete from msc_st_demantra_f where st_transaction_id=p_st_transaction_id;

Line 1511: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: insert into msc_st_demantra_f: '||sql%rowcount);

1507: ' and cmv.region_id(+)=f.region_id'||
1508: ' and cmv.sr_instance_id(+)=decode(f.region_id,-23453,cmv.sr_instance_id(+),f.owning_inst_id)';
1509:
1510: execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
1511: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: insert into msc_st_demantra_f: '||sql%rowcount);
1512: else
1513: l_sql :=
1514: ' select min(start_date), max(start_date)'||
1515: ' from '||l_apps_schema||'.msc_demantra_f'||l_suffix||

Line 1518: msc_phub_util.log_sql(l_sql);

1514: ' select min(start_date), max(start_date)'||
1515: ' from '||l_apps_schema||'.msc_demantra_f'||l_suffix||
1516: ' where plan_run_id=:p_plan_run_id and aggr_type=0';
1517:
1518: msc_phub_util.log_sql(l_sql);
1519: execute immediate l_sql into l_plan_start_date, l_plan_cutoff_date using p_plan_run_id;
1520: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: l_plan_start_date='||l_plan_start_date||', l_plan_cutoff_date='||l_plan_cutoff_date);
1521:
1522: l_sql :=

Line 1520: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: l_plan_start_date='||l_plan_start_date||', l_plan_cutoff_date='||l_plan_cutoff_date);

1516: ' where plan_run_id=:p_plan_run_id and aggr_type=0';
1517:
1518: msc_phub_util.log_sql(l_sql);
1519: execute immediate l_sql into l_plan_start_date, l_plan_cutoff_date using p_plan_run_id;
1520: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: l_plan_start_date='||l_plan_start_date||', l_plan_cutoff_date='||l_plan_cutoff_date);
1521:
1522: l_sql :=
1523: ' insert into msc_st_demantra_f('||
1524: ' st_transaction_id,'||

Line 1730: msc_phub_util.log_sql(l_sql);

1726: ' decode(f.region_id, -23453, null, cmv.zone),'||
1727: ' f.demand_class,'||
1728: ' f.start_date';
1729:
1730: msc_phub_util.log_sql(l_sql);
1731: execute immediate l_sql using p_st_transaction_id, p_plan_run_id, l_plan_start_date, l_plan_cutoff_date;
1732: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: insert into msc_st_demantra_f: '||sql%rowcount);
1733: commit;
1734: end if;

Line 1732: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: insert into msc_st_demantra_f: '||sql%rowcount);

1728: ' f.start_date';
1729:
1730: msc_phub_util.log_sql(l_sql);
1731: execute immediate l_sql using p_st_transaction_id, p_plan_run_id, l_plan_start_date, l_plan_cutoff_date;
1732: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: insert into msc_st_demantra_f: '||sql%rowcount);
1733: commit;
1734: end if;
1735: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: complete, retcode='||retcode);
1736:

Line 1735: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: complete, retcode='||retcode);

1731: execute immediate l_sql using p_st_transaction_id, p_plan_run_id, l_plan_start_date, l_plan_cutoff_date;
1732: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: insert into msc_st_demantra_f: '||sql%rowcount);
1733: commit;
1734: end if;
1735: msc_phub_util.log('msc_demantra_pkg.export_demantra_f: complete, retcode='||retcode);
1736:
1737: exception
1738: when others then
1739: retcode := 2;

Line 1741: msc_phub_util.log(errbuf);

1737: exception
1738: when others then
1739: retcode := 2;
1740: errbuf := 'msc_demantra_pkg.export_demantra_f: '||sqlerrm;
1741: msc_phub_util.log(errbuf);
1742: end export_demantra_f;
1743:
1744: procedure export_demantra_ods_f (
1745: errbuf out nocopy varchar2, retcode out nocopy varchar2,

Line 1750: l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);

1746: p_st_transaction_id number, p_plan_run_id number,
1747: p_dblink varchar2, p_source_version varchar2)
1748: is
1749: l_sql varchar2(16384);
1750: l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
1751: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
1752: begin
1753: msc_phub_util.log('msc_demantra_pkg.export_demantra_ods_f');
1754: retcode := 0;

Line 1751: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;

1747: p_dblink varchar2, p_source_version varchar2)
1748: is
1749: l_sql varchar2(16384);
1750: l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
1751: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
1752: begin
1753: msc_phub_util.log('msc_demantra_pkg.export_demantra_ods_f');
1754: retcode := 0;
1755: errbuf := null;

Line 1753: msc_phub_util.log('msc_demantra_pkg.export_demantra_ods_f');

1749: l_sql varchar2(16384);
1750: l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
1751: l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
1752: begin
1753: msc_phub_util.log('msc_demantra_pkg.export_demantra_ods_f');
1754: retcode := 0;
1755: errbuf := null;
1756:
1757: delete from msc_st_demantra_ods_f where st_transaction_id=p_st_transaction_id;

Line 1882: msc_phub_util.log_sql(l_sql);

1878: ' and cmv.customer_site_id(+)=f.customer_site_id'||
1879: ' and cmv.region_id(+)=f.region_id'||
1880: ' and cmv.sr_instance_id(+)=decode(f.region_id,-23453,cmv.sr_instance_id(+),f.sr_instance_id)';
1881:
1882: msc_phub_util.log_sql(l_sql);
1883: execute immediate l_sql using p_st_transaction_id;
1884: commit;
1885: msc_phub_util.log('msc_demantra_pkg.export_demantra_ods_f: complete, retcode='||retcode);
1886:

Line 1885: msc_phub_util.log('msc_demantra_pkg.export_demantra_ods_f: complete, retcode='||retcode);

1881:
1882: msc_phub_util.log_sql(l_sql);
1883: execute immediate l_sql using p_st_transaction_id;
1884: commit;
1885: msc_phub_util.log('msc_demantra_pkg.export_demantra_ods_f: complete, retcode='||retcode);
1886:
1887: exception
1888: when others then
1889: retcode := 2;

Line 1891: msc_phub_util.log(errbuf);

1887: exception
1888: when others then
1889: retcode := 2;
1890: errbuf := 'msc_demantra_pkg.export_demantra_ods_f: '||sqlerrm;
1891: msc_phub_util.log(errbuf);
1892: end export_demantra_ods_f;
1893:
1894: end msc_demantra_pkg;