[Home] [Help]
PACKAGE BODY: APPS.MSC_DEMANTRA_PKG
Source
1 package body msc_demantra_pkg as
2 /* $Header: MSCHBDMB.pls 120.59.12020000.2 2012/12/21 17:00:14 wexia ship $ */
3 LF constant varchar2(1) := ''; /* use chr(10) for debug */
4 DUMMY constant varchar2(10) := 'dummy';
5
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
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
24 l_ddl varchar2(500);
25 l_applsys_schema varchar2(100);
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;
35
36 l_ddl := 'truncate table msc_demantra_ods_f';
37 ad_ddl.do_ddl(l_applsys_schema, g_msc_schema, ad_ddl.truncate_table, l_ddl, 'MSC_DEMANTRA_ODS_F');
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
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';
56 else
57 l_sql :=
58 ' create or replace view '||l_apps_schema||'.msc_dmtra_item_hies_v as'||
59 ' select table_label level_name from ('||
60 ' select group_table_id, table_label'||
61 ' from '||g_demantra_schema||'.group_tables'||
62 ' connect by father_id=prior group_table_id'||
63 ' start with table_label=''Item'''||
64 ' )'||
65 ' where group_table_id in'||
66 ' (select group_table_id'||
67 ' from '||g_demantra_schema||'.group_tables b'||
68 ' where not exists'||
69 ' (select 1 from '||g_demantra_schema||'.group_tables where father_id=b.group_table_id)'||
70 ' )'||
71 ' order by 1';
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)
81 return varchar2
82 is
83 l_s varchar2(1024);
84 l_result varchar2(16384);
85 c sys_refcursor;
86 begin
87 l_result := '';
88 open c for p_sql;
89 loop
90 fetch c into l_s;
91 exit when c%notfound;
92 l_result := l_result||l_s||p_delim;
93 end loop;
94 close c;
95
96 return l_result;
97 end list_result;
98
99 function source_table(p_publisher varchar2) return varchar2 is
100 l_source_table varchar2(30);
101 l_sql varchar2(200);
102 begin
103 l_sql := 'select view_name from '||g_demantra_schema||'.transfer_query where application_id='''||p_publisher||'''';
104 execute immediate l_sql into l_source_table;
105 return l_source_table;
106 exception
107 when others then
108 return null;
109 end source_table;
110
111 function publish_mode(p_publisher varchar2) return number is
112 l_publish_mode number;
113 l_sql varchar2(200);
114 begin
115 l_sql := 'select export_type from '||g_demantra_schema||'.transfer_query where application_id='''||p_publisher||'''';
116 execute immediate l_sql into l_publish_mode;
117 return l_publish_mode;
118 exception
119 when others then
120 return null;
121 end publish_mode;
122
123 function fact_table(p_fact_type number) return varchar2 is
124 l_entity_name varchar2(30);
125 begin
126 select upper('msc_'||entity_name||'_f') into l_entity_name from table(msc_phub_pkg.meta_info) where fact_type=p_fact_type;
127 return l_entity_name;
128 exception
129 when others then
130 return null;
131 end fact_table;
132
133 function staging_table(p_fact_type number) return varchar2 is
134 l_entity_name varchar2(30);
135 begin
136 select upper('msc_st_'||entity_name||'_f') into l_entity_name from table(msc_phub_pkg.meta_info) where fact_type=p_fact_type;
137 return l_entity_name;
138 exception
139 when others then
140 return null;
141 end staging_table;
142
143 procedure build_custom_hierarchy
144 is
145 l_item_hierarchy varchar2(100) := fnd_profile.value_specific('MSC_APCC_DMTRA_ITEM_HIERS', -1);
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;
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);
162 commit;
163
164 l_sql :=
165 ' select rownum level_id, gtable, cust_code_field, gtable2, id_field2, table_label'||LF||
166 ' from'||LF||
167 ' (select gtable, cust_code_field, prior gtable gtable2, prior id_field id_field2, table_label'||LF||
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
177 l_sql2 :=
178 ' insert into msc_apcc_custom_hier('||LF||
179 ' detail_id,'||LF||
180 ' '||list_result('select ''level''||level_id from ('||l_sql||') where gtable<>''t_ep_item''', ','||LF)||
181 ' created_by, creation_date,'||LF||
182 ' last_update_date, last_updated_by, last_update_login,'||LF||
183 ' program_id, program_login_id,'||LF||
184 ' program_application_id, request_id)'||LF||
185 ' select'||LF||
186 ' t_ep_item.ebs_item_dest_key,'||LF||
187 ' '||list_result('select ''cast(''||gtable||''.''||cust_code_field||'' as varchar2(200))'' from ('||l_sql||') where gtable<>''t_ep_item''', ','||LF)||
188 ' fnd_global.user_id, sysdate,'||LF||
189 ' sysdate, fnd_global.user_id, fnd_global.login_id,'||LF||
190 ' fnd_global.conc_program_id, fnd_global.conc_login_id,'||LF||
191 ' fnd_global.prog_appl_id, fnd_global.conc_request_id'||LF||
192 ' from'||LF||
193 ' '||list_result('select '''||g_demantra_schema||'.''||gtable from ('||l_sql||') where gtable is not null', ', ')||' dual'||LF||
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
203 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;
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
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
228 from dba_tab_columns f, table(msc_phub_pkg.meta_info) m, user_synonyms syn
229 where m.fact_type in (4,5)
230 and syn.synonym_name='MSC_'||upper(m.entity_name)||'_F'
231 and f.owner=g_msc_schema
232 and f.column_name like 'ATTRIBUTE%'
233 and f.owner=syn.table_owner
234 and f.table_name=syn.table_name) f
235 on (b.fact_type=f.fact_type and b.column_name=f.column_name)
236 when not matched then insert (
237 fact_type, column_name, column_title,
238 source_column_name, aggregation_method,
239 created_by, creation_date,
240 last_update_date, last_updated_by, last_update_login,
241 program_id, program_login_id,
242 program_application_id, request_id)
243 values (
244 f.fact_type, f.column_name, null, null, null,
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
254 (select 1
255 from dba_tab_columns f, table(msc_phub_pkg.meta_info) m, user_synonyms syn
256 where m.fact_type in (4,5)
257 and syn.synonym_name='MSC_'||upper(m.entity_name)||'_F'
258 and f.owner=g_msc_schema
259 and f.column_name like 'ATTRIBUTE%'
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
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,
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 (
285 plan_id,
286 plan_run_id,
287 sr_instance_id,
288 organization_id,
289 customer_id,
290 customer_site_id,
291 region_id,
292 inventory_item_id,
293 demand_class,
294 owning_org_id,
295 owning_inst_id,
296 start_date,
297 aggr_type, category_set_id, sr_category_id,
298 consensus_fcst,
299 consensus_fcst_value,
300 consensus_fcst_value2,
301 consensus_fcst_cum,
302 priority,
303 created_by, creation_date,
304 last_update_date, last_updated_by, last_update_login,
305 program_id, program_login_id,
306 program_application_id, request_id)
307 select
308 t.scenario_id plan_id,
309 p_plan_run_id,
310 decode(t.organization_id, -1, -23453, t.sr_instance_id) sr_instance_id, --wei: sync sr_instance_id with organization_id
311 decode(t.organization_id, -1, -23453, t.organization_id) organization_id,
312 t.customer_id,
313 t.customer_site_id,
314 t.zone_id region_id,
315 t.inventory_item_id,
316 t.demand_class,
317 t.owning_org_id,
318 t.sr_instance_id owning_inst_id,
319 t.start_date,
320 to_number(0) aggr_type,
321 to_number(-23453) category_set_id,
322 to_number(-23453) sr_category_id,
323 t.consensus_fcst,
324 t.consensus_fcst*t.price consensus_fcst_value,
325 t.consensus_fcst*t.price * decode(t.currency_code,
326 fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))
327 consensus_fcst_value2,
328 t.consensus_fcst_cum,
329 t.priority,
330 fnd_global.user_id, sysdate,
331 sysdate, fnd_global.user_id, fnd_global.login_id,
332 fnd_global.conc_program_id, fnd_global.conc_login_id,
333 fnd_global.prog_appl_id, fnd_global.conc_request_id
334 from (
335 select
336 k.scenario_id,
337 k.sr_instance_id,
338 k.organization_id,
339 k.customer_id,
340 k.customer_site_id,
341 k.zone_id,
342 k.inventory_item_id,
343 k.owning_org_id,
344 k.demand_class,
345 k.start_date,
346 nvl(f.currency_code, l_owning_currency_code) currency_code,
347 f.price,
348 f.consensus_fcst,
349 sum(nvl(f.consensus_fcst, 0)) over(partition by
350 k.scenario_id, k.sr_instance_id, k.organization_id,
351 k.customer_id, k.customer_site_id, k.zone_id,
352 k.inventory_item_id, k.demand_class
353 order by k.start_date) consensus_fcst_cum,
354 f.priority
355 from
356 (select -- distinct bug 9941114
357 k1.scenario_id,
358 k1.sr_instance_id,
359 k1.organization_id,
360 k1.customer_id,
361 k1.customer_site_id,
362 k1.zone_id,
363 k1.inventory_item_id,
364 k1.owning_org_id,
365 k1.demand_class,
366 k1.price,
367 k2.start_date
368 from
369 (select distinct
370 scenario_id,
371 sr_instance_id,
372 organization_id,
373 customer_id,
374 customer_site_id,
375 zone_id,
376 inventory_item_id,
377 price,
378 demand_class,
379 decode(organization_id,
380 -1, msc_hub_calendar.get_item_org(-1, inventory_item_id, sr_instance_id),
381 organization_id) owning_org_id
382 from msd_dem_scn_entries_v
383 where scenario_id=p_plan_id
384 ) k1,
385
386 (select distinct start_date
387 from msd_dem_scn_entries_v
388 where scenario_id=p_plan_id) k2
389 ) k,
390 msd_dem_scn_entries_v f
391 where k.scenario_id = f.scenario_id(+)
392 and k.sr_instance_id = f.sr_instance_id(+)
393 and k.organization_id = f.organization_id(+)
394 and k.customer_id = f.customer_id(+)
395 and k.customer_site_id = f.customer_site_id(+)
396 and k.zone_id = f.zone_id(+)
397 and k.inventory_item_id = f.inventory_item_id(+)
398 and k.demand_class = f.demand_class(+)
399 and k.start_date = f.start_date(+)) t,
400 msc_currency_conv_mv mcc
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
410 exception
411 when others then
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
421 c sys_refcursor;
422 l_sql varchar2(16384);
423 l_fact_type number;
424 l_publisher number;
425 l_source_column_name varchar2(30);
426 l_op varchar2(1);
427 l_column_title varchar2(1024);
428 l_aggregation_method varchar2(30);
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)');
438 return;
439 end if;
440
441 select msc_hub_query_s.nextval into l_qid_wf from dual;
442 l_sql :=
443 ' insert into msc_hub_query ('||LF||
444 ' query_id, number1, clob1,'||LF||
445 ' created_by, creation_date,'||LF||
446 ' last_update_date, last_updated_by, last_update_login)'||LF||
447 ' select'||LF||
448 ' '||l_qid_wf||', schema_id, to_lob(schema_data),'||LF||
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 :=
458 ' select'||LF||
459 ' nvl(b.fact_type, v.fact_type) fact_type,'||LF||
460 ' decode(b.source_column_name, null, ''I'', decode(v.computed_name, null, ''D'', ''U'')) op,'||LF||
461 ' nvl(b.source_column_name, v.computed_name) computed_name,'||LF||
462 ' v.computed_title, v.int_aggr_func'||LF||
463 ' from'||LF||
464 ' (select fact_type, source_column_name'||LF||
465 ' from msc_apcc_custom_measures'||LF||
466 ' where source_column_name is not null'||LF||
467 ' ) b'||LF||
468 ' full outer join'||LF||
469 ' (select /*+ ordered */ distinct m.fact_type, c.computed_name, c.computed_title, c.int_aggr_func'||LF||
470 ' from'||LF||
471 ' (select /*+ ordered */ distinct b.fact_type, q.id'||LF||
472 ' from'||LF||
473 ' (select 4 fact_type, ''apps.msc_phub_pkg.archive_demantra_ods'' proc_name from dual'||LF||
474 ' union select 5 fact_type, ''apps.msc_phub_pkg.archive_demantra_plan'' proc_name from dual'||LF||
475 ' ) b,'||LF||
476 ' (select extract(f.column_value, ''/PROCEDURE'') s'||LF||
477 ' from msc_hub_query t, xmltable(''/WORKFLOW/STOREDPROCEDURESTEP/PROCEDURE'' PASSING xmltype(t.clob1)) f'||LF||
478 ' where t.query_id='||l_qid_wf||LF||
479 ' ) f,'||LF||
480 ' '||g_demantra_schema||'.transfer_query q'||LF||
481 ' where lower(extractValue(f.s, ''/PROCEDURE/NAME''))=b.proc_name'||LF||
482 ' and trim(extractValue(f.s, ''/PROCEDURE/PARAM[1]''))=q.application_id'||LF||
483 ' ) m,'||LF||
484 ' '||g_demantra_schema||'.transfer_query_series qs,'||LF||
485 ' '||g_demantra_schema||'.computed_fields c'||LF||
486 ' where m.id=qs.id'||LF||
487 ' and qs.series_id=c.forecast_type_id'||LF||
488 ' and upper(c.computed_name) not in ('||g_dimension_columns||')'||LF||
489 ' 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||
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;
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,
506 column_title=null,
507 aggregation_method=null,
508 last_update_date=sysdate,
509 last_updated_by=fnd_global.user_id,
510 last_update_login=fnd_global.login_id,
511 program_id=fnd_global.conc_program_id,
512 program_login_id=fnd_global.conc_login_id,
513 program_application_id=fnd_global.prog_appl_id,
514 request_id=fnd_global.conc_request_id
515 where fact_type=l_fact_type and source_column_name=l_source_column_name;
516 commit;
517
518 when l_op='I' then
519 select column_name into l_column_name
520 from msc_apcc_custom_measures
521 where fact_type=l_fact_type and source_column_name is null and rownum=1
522 order by to_number(substr(column_name,10));
523
524 update msc_apcc_custom_measures
525 set source_column_name=l_source_column_name,
526 column_title=l_column_title,
527 aggregation_method=l_aggregation_method,
528 last_update_date=sysdate,
529 last_updated_by=fnd_global.user_id,
530 last_update_login=fnd_global.login_id,
531 program_id=fnd_global.conc_program_id,
532 program_login_id=fnd_global.conc_login_id,
533 program_application_id=fnd_global.prog_appl_id,
534 request_id=fnd_global.conc_request_id
535 where fact_type=l_fact_type and column_name=l_column_name;
536 commit;
537
538 when l_op='U' then
539 update msc_apcc_custom_measures
540 set last_update_date=sysdate,
541 last_updated_by=fnd_global.user_id,
542 last_update_login=fnd_global.login_id,
543 program_id=fnd_global.conc_program_id,
544 program_login_id=fnd_global.conc_login_id,
545 program_application_id=fnd_global.prog_appl_id,
546 request_id=fnd_global.conc_request_id
547 where fact_type=l_fact_type and source_column_name=l_source_column_name;
548 commit;
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
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
564 '00' lang_id,
565 g_msg_num_prefix2||upper(m.entity_name)||'_'||substr(to_char(substr(f.column_name,10),'09'),2) msg_num,
566 f.column_title msg_text
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
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
581 is
582 t msc_apcc_measure_map_table := msc_apcc_measure_map_table();
583
584 l_value varchar2(20) := ' * t.price';
585 l_rpt_cur varchar2(120) := ' * decode(t.currency_code, fnd_profile.value(''MSC_HUB_CUR_CODE_RPT''), 1, nvl(mcc.conv_rate, 0))';
586 l_value2 varchar2(120) := l_value||l_rpt_cur;
587 l_over varchar2(200) := ' over(partition by t.sr_instance_id, t.organization_id, t.owning_org_id, t.inventory_item_id, t.customer_id, t.customer_site_id, t.region_id, t.demand_class order by t.sdate)';
588 l_projected_backlog varchar2(1024) := 'sum(decode(t.future_start, 1, t.total_backlog, 0))'||l_over||' + sum(decode(t.record_type, 1, null, nvl(t.fcst_booking, 0) - nvl(t.fcst_shipment, 0)))'||l_over;
589 l_booking_history varchar2(60) := 'decode(t.record_type, 1, t.ebs_bh_book_qty_bd, null)';
590 l_shipment_history varchar2(60) := 'decode(t.record_type, 1, t.ebs_sh_ship_qty_sd, null)';
591 l_inventory_history varchar2(60) := 'decode(t.record_type, 1, t.actual_on_hand, null)';
592 l_returns_history varchar2(60) := 'decode(t.record_type, 1, t.ebs_return_history, null)';
593 l_production_history varchar2(60) := 'decode(t.record_type, 1, t.actual_prod, null)';
594
595 begin
596 t.extend(56);
597 t(1) := msc_apcc_measure_map_t(5, 'prty_demand', 'priority', 't.prty_demand', 'min');
598 t(2) := msc_apcc_measure_map_t(5, 'fcst_consensus', 'consensus_fcst', 't.fcst_consensus', 'sum');
599 t(3) := msc_apcc_measure_map_t(5, 'fcst_consensus', 'consensus_fcst_value', 't.fcst_consensus'||l_value, 'sum');
600 t(4) := msc_apcc_measure_map_t(5, 'fcst_consensus', 'consensus_fcst_value2', 't.fcst_consensus'||l_value2, 'sum');
601 t(5) := msc_apcc_measure_map_t(5, 'fcst_consensus', 'consensus_fcst_cum', 'sum(t.fcst_consensus)'||l_over, 'sum');
602 t(6) := msc_apcc_measure_map_t(5, 'fcst_hyp_annual_plan', 'annual_plan_value', 't.fcst_hyp_annual_plan', 'sum');
603 t(7) := msc_apcc_measure_map_t(5, 'fcst_hyp_annual_plan', 'annual_plan_value2', 't.fcst_hyp_annual_plan'||l_rpt_cur, 'sum');
604 t(8) := msc_apcc_measure_map_t(5, 'fcst_booking', 'booking_fcst', 't.fcst_booking', 'sum');
605 t(9) := msc_apcc_measure_map_t(5, 'fcst_booking', 'booking_fcst_value', 't.fcst_booking'||l_value, 'sum');
606 t(10) := msc_apcc_measure_map_t(5, 'fcst_booking', 'booking_fcst_value2', 't.fcst_booking'||l_value2, 'sum');
607 t(11) := msc_apcc_measure_map_t(5, 'fcst_booking', 'booking_fcst_cum', 'sum(t.fcst_booking)'||l_over, 'sum');
608 t(12) := msc_apcc_measure_map_t(5, 'budget', 'budget', 't.budget', 'sum');
609 t(13) := msc_apcc_measure_map_t(5, 'budget', 'budget2', 't.budget'||l_rpt_cur, 'sum');
610 t(14) := msc_apcc_measure_map_t(5, 'budget', 'budget_cum', 'sum(t.budget)'||l_over, 'sum');
611 t(15) := msc_apcc_measure_map_t(5, 'budget', 'budget2_cum', 'sum(t.budget'||l_rpt_cur||')'||l_over, 'sum');
612 t(16) := msc_apcc_measure_map_t(5, 'c_pred', 'final_fcst', 't.c_pred', 'sum');
613 t(17) := msc_apcc_measure_map_t(5, 'c_pred', 'final_fcst_value', 't.c_pred'||l_value, 'sum');
614 t(18) := msc_apcc_measure_map_t(5, 'c_pred', 'final_fcst_value2', 't.c_pred'||l_value2, 'sum');
615 t(19) := msc_apcc_measure_map_t(5, 'c_pred', 'final_fcst_cum', 'sum(t.c_pred)'||l_over, 'sum');
616 t(20) := msc_apcc_measure_map_t(5, 'fcst_hyp_financial', 'financial_fcst_value', 't.fcst_hyp_financial', 'sum');
617 t(21) := msc_apcc_measure_map_t(5, 'fcst_hyp_financial', 'financial_fcst_value2', 't.fcst_hyp_financial'||l_rpt_cur, 'sum');
618 t(22) := msc_apcc_measure_map_t(5, 'mktg_fcst', 'mktg_fcst', 't.mktg_fcst', 'sum');
619 t(23) := msc_apcc_measure_map_t(5, 'mktg_fcst', 'mktg_fcst_value', 't.mktg_fcst'||l_value, 'sum');
620 t(24) := msc_apcc_measure_map_t(5, 'mktg_fcst', 'mktg_fcst_value2', 't.mktg_fcst'||l_value2, 'sum');
621 t(25) := msc_apcc_measure_map_t(5, 'mktg_fcst', 'mktg_fcst_cum', 'sum(t.mktg_fcst)'||l_over, 'sum');
622 t(26) := msc_apcc_measure_map_t(5, 'sales_fcst', 'sales_fcst', 't.sales_fcst', 'sum');
623 t(27) := msc_apcc_measure_map_t(5, 'sales_fcst', 'sales_fcst_value', 't.sales_fcst'||l_value, 'sum');
624 t(28) := msc_apcc_measure_map_t(5, 'sales_fcst', 'sales_fcst_value2', 't.sales_fcst'||l_value2, 'sum');
625 t(29) := msc_apcc_measure_map_t(5, 'sales_fcst', 'sales_fcst_cum', 'sum(t.sales_fcst)'||l_over, 'sum');
626 t(30) := msc_apcc_measure_map_t(5, 'fcst_shipment', 'shipment_fcst', 't.fcst_shipment', 'sum');
627 t(31) := msc_apcc_measure_map_t(5, 'fcst_shipment', 'shipment_fcst_value', 't.fcst_shipment'||l_value, 'sum');
628 t(32) := msc_apcc_measure_map_t(5, 'fcst_shipment', 'shipment_fcst_value2', 't.fcst_shipment'||l_value2, 'sum');
629 t(33) := msc_apcc_measure_map_t(5, 'fcst_shipment', 'shipment_fcst_cum', 'sum(t.fcst_shipment)'||l_over, 'sum');
630 t(33) := msc_apcc_measure_map_t(5, 'total_backlog', 'projected_backlog', l_projected_backlog, 'sum');
631 t(35) := msc_apcc_measure_map_t(5, 'fcst_booking', 'projected_backlog', l_projected_backlog, 'sum');
632 t(36) := msc_apcc_measure_map_t(5, 'fcst_shipment', 'projected_backlog', l_projected_backlog, 'sum');
633
634 t(37) := msc_apcc_measure_map_t(4, 'week4_abs_pct_err', 'consen_fcst_accrcy_mape_4week', 't.week4_abs_pct_err', 'avg');
635 t(38) := msc_apcc_measure_map_t(4, 'week8_abs_pct_err', 'consen_fcst_accrcy_mape_8week', 't.week8_abs_pct_err', 'avg');
636 t(39) := msc_apcc_measure_map_t(4, 'week13_abs_pct_err', 'consen_fcst_accrcy_mape_13week', 't.week13_abs_pct_err', 'avg');
637 t(40) := msc_apcc_measure_map_t(4, 'actual_prod', 'production_history', l_production_history, 'sum');
638 t(41) := msc_apcc_measure_map_t(4, 'ebs_bh_book_qty_bd', 'booking_history', l_booking_history, 'sum');
639 t(42) := msc_apcc_measure_map_t(4, 'ebs_bh_book_qty_bd', 'booking_history_value', l_booking_history||l_value, 'sum');
640 t(43) := msc_apcc_measure_map_t(4, 'ebs_bh_book_qty_bd', 'booking_history_value2', l_booking_history||l_value2, 'sum');
641 t(44) := msc_apcc_measure_map_t(4, 'ebs_sh_ship_qty_sd', 'shipment_history', l_shipment_history, 'sum');
642 t(45) := msc_apcc_measure_map_t(4, 'ebs_sh_ship_qty_sd', 'shipment_history_value', l_shipment_history||l_value, 'sum');
643 t(46) := msc_apcc_measure_map_t(4, 'ebs_sh_ship_qty_sd', 'shipment_history_value2', l_shipment_history||l_value2, 'sum');
644 t(47) := msc_apcc_measure_map_t(4, 'total_backlog', 'actual_backlog', 't.total_backlog', 'sum');
645 t(48) := msc_apcc_measure_map_t(4, 'total_backlog', 'actual_backlog_value', 't.total_backlog'||l_value, 'sum');
646 t(49) := msc_apcc_measure_map_t(4, 'total_backlog', 'actual_backlog_value2', 't.total_backlog'||l_value2, 'sum');
647 t(50) := msc_apcc_measure_map_t(4, 'actual_on_hand', 'inventory_history', l_inventory_history, 'sum');
648 t(51) := msc_apcc_measure_map_t(4, 'actual_on_hand', 'inventory_history_value', l_inventory_history||l_value, 'sum');
649 t(52) := msc_apcc_measure_map_t(4, 'actual_on_hand', 'inventory_history_value2', l_inventory_history||l_value2, 'sum');
650 t(53) := msc_apcc_measure_map_t(4, 'ebs_bh_book_qty_rd', 'booking_history_rd', 't.ebs_bh_book_qty_rd', 'sum');
651 t(54) := msc_apcc_measure_map_t(4, 'ebs_bh_book_qty_rd', 'booking_history_rd_value', 't.ebs_bh_book_qty_rd'||l_value, 'sum');
652 t(55) := msc_apcc_measure_map_t(4, 'ebs_bh_book_qty_rd', 'booking_history_rd_value2', 't.ebs_bh_book_qty_rd'||l_value2, 'sum');
653 t(56) := msc_apcc_measure_map_t(4, 'ebs_return_history', 'returns_history', l_returns_history, 'sum');
654 return t;
655 end seeded_measure_mapping;
656
657 function list_measures(p_list_type number, p_prefix varchar2, p_fact_type number, p_publisher varchar2)
658 return varchar2
659 is
660 l_sql varchar2(16384);
661 l_sql2 varchar2(16384);
662 begin
663 l_sql :=
664 ' select source_column_name, aggregation_method, column_name, formula'||LF||
665 ' from'||LF||
666 ' (select'||LF||
667 ' first_value(v.column_name) over(partition by b.column_name, b.formula order by b.computed_name desc) source_column_name,'||LF||
668 ' b.aggregation_method,'||LF||
669 ' b.column_name, b.formula'||LF||
670 ' from'||LF||
671 ' (select c.computed_name, c.int_aggr_func, m.column_name, m.formula, m.aggregation_method'||LF||
672 ' from'||LF||
673 ' table(msc_demantra_pkg.seeded_measure_mapping) m,'||LF||
674 ' '||g_demantra_schema||'.computed_fields c'||LF||
675 ' where m.source_column_name=c.computed_name'||LF||
676 ' and m.fact_type='||p_fact_type||LF||
677 ' ) b,'||LF||
678 ' all_tab_columns v'||LF||
679 ' where v.table_name(+)='''||source_table(p_publisher)||''''||LF||
680 ' and v.owner(+)='''||g_demantra_schema||''''||LF||
681 ' and upper(b.computed_name)=v.column_name(+)'||LF||
682 ' union all'||LF||
683 ' select c.source_column_name, nvl(c.aggregation_method, ''sum''), c.column_name,'||LF||
684 ' decode(c.source_column_name, null, null, ''t.''||c.source_column_name) formula'||LF||
685 ' from'||LF||
686 ' msc_apcc_custom_measures c,'||LF||
687 ' all_tab_columns v'||LF||
688 ' where v.table_name='''||source_table(p_publisher)||''''||LF||
689 ' and v.owner='''||g_demantra_schema||''''||LF||
690 ' and v.column_name=upper(c.source_column_name)'||LF||
691 ' and c.fact_type='||p_fact_type||LF||
692 ' and c.column_name like ''ATTRIBUTE%'''||LF||
693 ' )';
694
695 l_sql2 :=
696 ' select distinct column_name, aggregation_method'||LF||
697 ' from table(msc_demantra_pkg.seeded_measure_mapping)'||LF||
698 ' where fact_type='||p_fact_type||LF||
699 ' union all'||LF||
700 ' select column_name, nvl(aggregation_method, ''sum'')'||LF||
701 ' from msc_apcc_custom_measures'||LF||
702 ' where fact_type='||p_fact_type;
703
704 case when p_list_type=1 then
705 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';
706 when p_list_type=2 then
707 l_sql := 'select distinct '''||p_prefix||'.''||source_column_name s from ('||l_sql||') where source_column_name is not null';
708 when p_list_type=3 then
709 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)';
710 when p_list_type=4 then
711 l_sql := 'select distinct column_name s from ('||l_sql||') where source_column_name is not null order by column_name';
712
713 when p_list_type=5 then
714 l_sql := 'select s from (select column_name, aggregation_method||''(''||'''||p_prefix||'.''||column_name||'') ''||column_name s from ('||l_sql2||') order by column_name)';
715 when p_list_type=6 then
716 l_sql := 'select column_name s from ('||l_sql2||') order by column_name';
717 else null;
718 end case;
719 return list_result(l_sql, ', '||LF);
720 end list_measures;
721
722 function sql_dimensions(p_publisher varchar2) return varchar2
723 is
724 l_sql_debug varchar2(16384);
725 l_sql varchar2(16384);
726 begin
727 l_sql_debug :=
728 ' select ''LEVEL1'' column_name, ''t_ep_item_EP_ID'' id_field from dual'||LF||
729 ' union select ''LEVEL2'', ''t_ep_ebs_demand_class_EP_ID'' from dual'||LF||
730 ' union select ''LEVEL3'', ''t_ep_organization_EP_ID'' from dual'||LF||
731 ' union select ''LEVEL4'', ''t_ep_site_EP_ID'' from dual'||LF||
732 ' union select ''DKEY_ITEM'', null from dual'||LF||
733 ' union select ''DKEY_SITE'', null from dual'||LF||
734 ' union select ''SDATE'', null from dual'||LF||
735 ' union select ''RECORD_TYPE'', null from dual';
736
737 l_sql :=
738 ' select v.column_name, d.id_field'||LF||
739 ' from'||LF||
740 ' all_tab_columns v,'||LF||
741 ' (select ''LEVEL''||rownum level_column, gtable, id_field'||LF||
742 ' from'||LF||
743 ' (select g.gtable, g.id_field'||LF||
744 ' from '||g_demantra_schema||'.transfer_query q, '||g_demantra_schema||'.transfer_query_levels l, '||g_demantra_schema||'.group_tables g'||LF||
745 ' where q.id=l.id'||LF||
746 ' and l.level_id=g.group_table_id'||LF||
747 ' and q.view_name='''||source_table(p_publisher)||''''||LF||
748 ' and g.status=''ACTIVE'''||LF||
749 ' order by l.lorder'||LF||
750 ' )'||LF||
751 ' ) d'||LF||
752 ' where v.table_name='''||source_table(p_publisher)||''''||LF||
753 ' and v.owner='''||g_demantra_schema||''''||LF||
754 ' and v.column_name in ('||g_dimension_columns||')'||LF||
755 ' and v.column_name=d.level_column(+)'||LF;
756
757 return l_sql;
758 end sql_dimensions;
759
760 function list_dimensions(p_list_type number, p_prefix varchar2, p_publisher varchar2)
761 return varchar2
762 is
763 l_sql varchar2(16384) := sql_dimensions(p_publisher);
764 begin
765 case when p_list_type=1 then
766 l_sql := 'select '''||p_prefix||'.''||column_name s from ('||l_sql||')';
767 when p_list_type=2 then
768 l_sql := 'select '''||p_prefix||'.''||column_name s from ('||l_sql||') where nvl(id_field,'''||DUMMY||''')<>''t_ep_organization_EP_ID''';
769 when p_list_type=3 then
770 l_sql := 'select '''||p_prefix||'.''||column_name s from ('||l_sql||') where column_name not in (''RECORD_TYPE'', ''SDATE'')';
771 else null;
772 end case;
773 return list_result(l_sql, ', ');
774 end list_dimensions;
775
776 procedure find_dimension_columns(p_publisher varchar2,
777 p_organization_column out nocopy varchar2,
778 p_demand_class_column out nocopy varchar2,
779 p_dkey_item_column out nocopy varchar2,
780 p_dkey_site_column out nocopy varchar2,
781 p_zone_column out nocopy varchar2)
782 is
783 l_sql varchar2(16384) := sql_dimensions(p_publisher);
784 c sys_refcursor;
785 l_column_name varchar2(30);
786 l_id_field varchar2(30);
787 begin
788 p_organization_column := DUMMY;
789 p_demand_class_column := DUMMY;
790 p_dkey_item_column := DUMMY;
791 p_dkey_site_column := DUMMY;
792 p_zone_column := DUMMY;
793
794 open c for l_sql;
795 loop
796 fetch c into l_column_name, l_id_field;
797 exit when c%notfound;
798
799 case when l_column_name='DKEY_ITEM' then p_dkey_item_column := l_column_name;
800 when l_column_name='DKEY_SITE' then p_dkey_site_column := l_column_name;
801 when l_id_field='t_ep_organization_EP_ID' then p_organization_column := l_column_name;
802 when l_id_field='t_ep_ebs_demand_class_EP_ID' then p_demand_class_column := l_column_name;
803 when l_id_field='t_ep_ebs_zone_EP_ID' then p_zone_column := l_column_name;
804 else null;
805 end case;
806 end loop;
807 close c;
808
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||
818 ',p_zone_column='||p_zone_column);
819
820 end find_dimension_columns;
821
822 procedure summarize(p_plan_id number, p_plan_run_id number, p_fact_type number)
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;
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||
840 ' plan_id, plan_run_id,'||LF||
841 ' sr_instance_id, organization_id, owning_inst_id, owning_org_id,'||LF||
842 ' inventory_item_id,'||LF||
843 ' customer_id, customer_site_id, region_id,'||LF||
844 ' demand_class, start_date,'||LF||
845 ' aggr_type, category_set_id, sr_category_id,'||LF||
846 ' '||list_measures(6, '', p_fact_type, null)||LF||
847 ' created_by, creation_date,'||LF||
848 ' last_update_date, last_updated_by, last_update_login,'||LF||
849 ' program_id, program_login_id,'||LF||
850 ' program_application_id, request_id)'||LF||
851 ' select'||LF||
852 ' f.plan_id, f.plan_run_id,'||LF||
853 ' f.sr_instance_id, f.organization_id, f.owning_inst_id, to_number(-23453) owning_org_id,'||LF||
854 ' to_number(-23453) inventory_item_id,'||LF||
855 ' f.customer_id, f.customer_site_id, f.region_id,'||LF||
856 ' f.demand_class, f.start_date,'||LF||
857 ' to_number(42) aggr_type,'||LF||
858 ' '||l_category_set_id1||' category_set_id,'||LF||
859 ' nvl(q.sr_category_id, -23453),'||LF||
860 ' '||list_measures(5, 'f', p_fact_type, null)||LF||
861 ' fnd_global.user_id, sysdate,'||LF||
862 ' sysdate, fnd_global.user_id, fnd_global.login_id,'||LF||
863 ' fnd_global.conc_program_id, fnd_global.conc_login_id,'||LF||
864 ' fnd_global.prog_appl_id, fnd_global.conc_request_id'||LF||
865 ' from'||LF||
866 ' msc_demantra_f f,'||LF||
867 ' msc_phub_item_categories_mv q'||LF||
868 ' where f.plan_id='||p_plan_id||' and f.plan_run_id='||p_plan_run_id||LF||
869 ' and f.aggr_type=0'||LF||
870 ' and f.owning_inst_id=q.sr_instance_id(+)'||LF||
871 ' and f.owning_org_id=q.organization_id(+)'||LF||
872 ' and f.inventory_item_id=q.inventory_item_id(+)'||LF||
873 ' and q.category_set_id(+)='||l_category_set_id1||LF||
874 ' group by'||LF||
875 ' f.plan_id, f.plan_run_id,'||LF||
876 ' f.sr_instance_id, f.organization_id, f.owning_inst_id,'||LF||
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
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)
893 is
894 l_sql varchar2(16384);
895 l_target_table varchar2(30) := fact_table(p_fact_type);
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, ';
905 else
906 l_plan_cols1 := 'end_date, ';
907 l_plan_cols2 := 't.end_date, ';
908 end if;
909
910 l_sql :=
911 ' insert into '||l_target_table||' ('||LF||
912 ' '||l_plan_cols1||LF||
913 ' sr_instance_id,'||LF||
914 ' organization_id,'||LF||
915 ' owning_inst_id,'||LF||
916 ' owning_org_id,'||LF||
917 ' inventory_item_id,'||LF||
918 ' customer_id,'||LF||
919 ' customer_site_id,'||LF||
920 ' region_id,'||LF||
921 ' demand_class,'||LF||
922 ' '||LF||
923 ' '||list_measures(6, '', p_fact_type, null)||LF||
924 ' '||LF||
925 ' created_by, creation_date,'||LF||
926 ' last_update_date, last_updated_by, last_update_login,'||LF||
927 ' program_id, program_login_id,'||LF||
928 ' program_application_id, request_id)'||LF||
929 ' select'||LF||
930 ' '||l_plan_cols2||LF||
931 ' nvl(t.sr_instance_id, -23453),'||LF||
932 ' nvl(t.organization_id, -23453),'||LF||
933 ' nvl(t.owning_inst_id, -23453),'||LF||
934 ' nvl(t.owning_org_id, -23453),'||LF||
935 ' nvl(t.inventory_item_id, -23453),'||LF||
936 ' nvl(t.customer_id, -23453),'||LF||
937 ' nvl(t.customer_site_id, -23453),'||LF||
938 ' nvl(t.region_id, -23453),'||LF||
939 ' nvl(t.demand_class, -''23453''),'||LF||
940 ' '||LF||
941 ' '||list_measures(6, 't', p_fact_type, null)||LF||
942 ' '||LF||
943 ' fnd_global.user_id, sysdate,'||LF||
944 ' sysdate, fnd_global.user_id, fnd_global.login_id,'||LF||
945 ' fnd_global.conc_program_id, fnd_global.conc_login_id,'||LF||
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
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)
962 is
963 l_sql varchar2(16384);
964 l_organization_column varchar2(30);
965 l_demand_class_column varchar2(30);
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);
975
976 l_plan_cols1 varchar2(200);
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;
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);
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
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
1005 truncate_ods_f;
1006 end if;
1007 else
1008 if (p_fact_type = 4) then
1009 l_target_table := staging_table(p_fact_type);
1010 else
1011 l_publish_mode := 1;
1012 end if;
1013 end if;
1014
1015 find_dimension_columns(p_publisher, l_organization_column, l_demand_class_column, l_dkey_item_column, l_dkey_site_column, l_zone_column);
1016
1017 if (p_fact_type = 5) then
1018 l_plan_cols1 := 'plan_id, plan_run_id, aggr_type, category_set_id, sr_category_id, start_date, ';
1019 l_plan_cols2 := p_plan_id||', '||p_plan_run_id||', 0, -23453, -23453, t.sdate, ';
1020 else
1021 if (l_publish_mode = 1) then
1022 l_plan_cols1 := 'end_date, ';
1023 l_plan_cols2 := 't.sdate, ';
1024 else
1025 l_plan_cols1 := 'st_transaction_id, error_code, end_date, ';
1026 l_plan_cols2 := p_transfer_id||', 0, t.sdate, ';
1027 end if;
1028 end if;
1029
1030 l_sql :=
1031 ' insert into '||l_target_table||' ('||LF||
1032 ' '||l_plan_cols1||LF||
1033 ' sr_instance_id,'||LF||
1034 ' organization_id,'||LF||
1035 ' owning_inst_id,'||LF||
1036 ' owning_org_id,'||LF||
1037 ' inventory_item_id,'||LF||
1038 ' customer_id,'||LF||
1039 ' customer_site_id,'||LF||
1040 ' region_id,'||LF||
1041 ' demand_class,'||LF||
1042 ' '||LF||
1043 ' '||list_measures(4, '', p_fact_type, p_publisher)||LF||
1044 ' '||LF||
1045 ' created_by, creation_date,'||LF||
1046 ' last_update_date, last_updated_by, last_update_login,'||LF||
1047 ' program_id, program_login_id,'||LF||
1048 ' program_application_id, request_id)'||LF||
1049 ' select'||LF||
1050 ' '||l_plan_cols2||LF||
1051 ' t.sr_instance_id,'||LF||
1052 ' t.organization_id,'||LF||
1053 ' t.owning_inst_id,'||LF||
1054 ' t.owning_org_id,'||LF||
1055 ' t.inventory_item_id,'||LF||
1056 ' t.customer_id,'||LF||
1057 ' t.customer_site_id,'||LF||
1058 ' t.region_id,'||LF||
1059 ' t.demand_class,'||LF||
1060 ' '||LF||
1061 ' '||list_measures(3, 't', p_fact_type, p_publisher)||LF||
1062 ' '||LF||
1063 ' fnd_global.user_id, sysdate,'||LF||
1064 ' sysdate, fnd_global.user_id, fnd_global.login_id,'||LF||
1065 ' fnd_global.conc_program_id, fnd_global.conc_login_id,'||LF||
1066 ' fnd_global.prog_appl_id, fnd_global.conc_request_id'||LF||
1067 ' from'||LF||
1068 ' (select'||LF||
1069 ' t.sr_instance_id,'||LF||
1070 ' t.organization_id,'||LF||
1071 ' t.currency_code,'||LF||
1072 ' t.owning_inst_id,'||LF||
1073 ' t.owning_org_id,'||LF||
1074 ' t.inventory_item_id,'||LF||
1075 ' t.price,'||LF||
1076 ' t.customer_id,'||LF||
1077 ' t.customer_site_id,'||LF||
1078 ' t.region_id,'||LF||
1079 ' t.demand_class,'||LF||
1080 ' t.record_type,'||LF||
1081 ' t.sdate,'||LF||
1082 ' t.future_start,'||LF||
1083 ' '||list_measures(1, 't', p_fact_type, p_publisher)||LF||
1084 ' t.dummy'||LF||
1085 ' from'||LF||
1086 ' (select /*+ ordered */'||LF||
1087 ' t.sr_instance_id,'||LF||
1088 ' t.organization_id,'||LF||
1089 ' t.currency_code,'||LF||
1090 ' rank() over('||LF||
1091 ' partition by t.sr_instance_id, t.organization_id,'||LF||
1092 ' msi.inventory_item_id, mpc.customer_id, mpc.customer_site_id,'||LF||
1093 ' mpc.region_id, mdc.demand_class, t.sdate'||LF||
1094 ' order by msi.organization_id) n,'||LF||
1095 ' t.sr_instance_id owning_inst_id,'||LF||
1096 ' nvl(msi.organization_id, -23453) owning_org_id,'||LF||
1097 ' nvl(msi.inventory_item_id, -23453) inventory_item_id,'||LF||
1098 ' nvl(msi.list_price,0)*(1-nvl(msi.average_discount,0)/100) price,'||LF||
1099 ' nvl(mpc.customer_id, -23453) customer_id,'||LF||
1100 ' nvl(mpc.customer_site_id, -23453) customer_site_id,'||LF||
1101 ' nvl(mpc.region_id, -23453) region_id,'||LF||
1102 ' nvl(mdc.demand_class, ''-23453'') demand_class,'||LF||
1103 ' t.record_type,'||LF||
1104 ' t.sdate,'||LF||
1105 ' t.future_start,'||LF||
1106 ' '||list_measures(2, 't', p_fact_type, p_publisher)||LF||
1107 ' t.dummy'||LF||
1108 ' from'||LF||
1109 ' (select /*+ ordered */'||LF||
1110 ' '||list_dimensions(2, 't', p_publisher)||LF||
1111 ' nvl(mtp.sr_instance_id, '||l_sr_instance_id||') sr_instance_id,'||LF||
1112 ' nvl(mtp.sr_tp_id, -23453) organization_id,'||LF||
1113 ' nvl(mtp.currency_code, '''||l_owning_currency_code||''') currency_code,'||LF||
1114 ' t.future_start,'||LF||
1115 ' '||list_measures(2, 't', p_fact_type, p_publisher)||LF||
1116 ' t.dummy'||LF||
1117 ' from'||LF||
1118 ' (select '||list_dimensions(1, 't', p_publisher)||LF||
1119 ' (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||
1120 ' '||list_measures(1, 't', p_fact_type, p_publisher)||LF||
1121 ' null dummy'||LF||
1122 ' from '||g_demantra_schema||'.'||source_table(p_publisher)||' t'||LF||
1123 ' group by '||list_dimensions(1, 't', p_publisher)||'0'||LF||
1124 ' ) t,'||LF||
1125 ' msc_trading_partners mtp'||LF||
1126 ' where t.'||l_organization_column||'=mtp.organization_code(+)'||LF||
1127 ' and mtp.partner_type(+)=3'||LF||
1128 ' ) t,'||LF||
1129 ' msc_system_items msi,'||LF||
1130 ' msc_phub_customers_mv mpc,'||LF||
1131 ' (select sr_instance_id, meaning, demand_class from msc_demand_classes'||LF||
1132 ' union all select instance_id, ''0'', ''-23453'' from msc_apps_instances'||LF||
1133 ' union all select instance_id, ''N/A'', ''-23453'' from msc_apps_instances) mdc'||LF||
1134 ' where msi.plan_id(+)=-1'||LF||
1135 ' and t.'||l_dkey_item_column||'=msi.inventory_item_id(+)'||LF;
1136
1137 if (l_dkey_site_column <> DUMMY) then l_sql := l_sql||
1138 ' and t.'||l_dkey_site_column||'=mpc.customer_site_id(+)'||LF;
1139 else l_sql := l_sql||
1140 ' and t.'||l_zone_column||'=mpc.zone(+)'||LF||
1141 ' and t.sr_instance_id=mpc.sr_instance_id(+)'||LF;
1142 end if; l_sql := l_sql||
1143 ' and nvl(t.'||l_demand_class_column||', ''-23453'')=mdc.meaning(+)'||LF||
1144 ' and t.sr_instance_id=msi.sr_instance_id(+)'||LF||
1145 ' and decode(t.organization_id, -23453, msi.organization_id(+), t.organization_id)=msi.organization_id(+)'||LF||
1146 ' and t.sr_instance_id=mdc.sr_instance_id(+)) t'||LF||
1147 ' where t.n=1'||LF||
1148 ' group by'||LF||
1149 ' t.sr_instance_id,'||LF||
1150 ' t.organization_id,'||LF||
1151 ' t.currency_code,'||LF||
1152 ' t.owning_inst_id,'||LF||
1153 ' t.owning_org_id,'||LF||
1154 ' t.inventory_item_id,'||LF||
1155 ' t.price,'||LF||
1156 ' t.customer_id,'||LF||
1157 ' t.customer_site_id,'||LF||
1158 ' t.region_id,'||LF||
1159 ' t.demand_class,'||LF||
1160 ' t.record_type,'||LF||
1161 ' t.sdate,'||LF||
1162 ' t.future_start,'||LF||
1163 ' t.dummy'||LF||
1164 ' ) t,'||LF||
1165 ' msc_currency_conv_mv mcc'||LF||
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
1175 if (p_fact_type = 4 and l_publish_mode = 2) then
1176 delete from msc_demantra_ods_f f where exists (
1177 select 1 from msc_st_demantra_ods_f
1178 where st_transaction_id=p_transfer_id and error_code=0
1179 and sr_instance_id=f.sr_instance_id
1180 and organization_id=f.organization_id
1181 and owning_inst_id=f.owning_inst_id
1182 and owning_org_id=f.owning_org_id
1183 and inventory_item_id=f.inventory_item_id
1184 and customer_id=f.customer_id
1185 and customer_site_id=f.customer_site_id
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;
1195
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);
1205 log_dbex(sqlerrm, 'msc_demantra_pkg.archive');
1206 raise;
1207 end archive;
1208
1209 procedure import_fact (
1210 errbuf out nocopy varchar2, retcode out nocopy varchar2,
1211 p_st_transaction_id number, p_plan_id number, p_plan_run_id number, p_fact_type number,
1212 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
1213 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
1214 is
1215 l_result number := 0;
1216 l_staging_table varchar2(30) := staging_table(p_fact_type);
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
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
1231 l_is_plan_data := 0;
1232 l_date_column := 'end_date';
1233 else
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
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(
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,
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);
1268 summarize(p_plan_id, p_plan_run_id, p_fact_type);
1269
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,
1279 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
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);
1289
1290 exception
1291 when others then
1292 retcode := 2;
1293 errbuf := 'msc_demantra_pkg.import_demantra_ods_f: '||sqlerrm;
1294 raise;
1295 end import_demantra_ods_f;
1296
1297 procedure import_demantra_f (
1298 errbuf out nocopy varchar2, retcode out nocopy varchar2,
1299 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
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);
1309
1310 exception
1311 when others then
1312 retcode := 2;
1313 errbuf := 'msc_demantra_pkg.import_demantra_f: '||sqlerrm;
1314 raise;
1315 end import_demantra_f;
1316
1317 procedure summarize_demantra_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
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);
1327
1328 exception
1329 when others then
1330 retcode := 2;
1331 errbuf := 'msc_demantra_pkg.summarize_demantra_f: '||sqlerrm;
1332 raise;
1333 end summarize_demantra_f;
1334
1335 procedure export_demantra_f (
1336 errbuf out nocopy varchar2, retcode out nocopy varchar2,
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
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;
1351 commit;
1352
1353 if (p_source_version > '11.5.10') then
1354 l_sql :=
1355 ' insert into msc_st_demantra_f('||
1356 ' st_transaction_id,'||
1357 ' error_code,'||
1358 ' sr_instance_id,'||
1359 ' organization_id,'||
1360 ' owning_inst_id,'||
1361 ' owning_org_id,'||
1362 ' inventory_item_id,'||
1363 ' customer_id,'||
1364 ' customer_site_id,'||
1365 ' region_id,'||
1366 ' organization_code,'||
1367 ' owning_org_code,'||
1368 ' item_name,'||
1369 ' customer_name,'||
1370 ' customer_site_code,'||
1371 ' zone,'||
1372 ' demand_class,'||
1373 ' start_date,'||
1374 ' consensus_fcst,'||
1375 ' consensus_fcst_value,'||
1376 ' consensus_fcst_value2,'||
1377 ' consensus_fcst_cum,'||
1378 ' priority,'||
1379 ' annual_plan_value,'||
1380 ' annual_plan_value2,'||
1381 ' booking_fcst,'||
1382 ' booking_fcst_value,'||
1383 ' booking_fcst_value2,'||
1384 ' booking_fcst_cum,'||
1385 ' budget,'||
1386 ' budget2,'||
1387 ' budget_cum,'||
1388 ' budget2_cum,'||
1389 ' final_fcst,'||
1390 ' final_fcst_value,'||
1391 ' final_fcst_value2,'||
1392 ' final_fcst_cum,'||
1393 ' financial_fcst_value,'||
1394 ' financial_fcst_value2,'||
1395 ' mktg_fcst,'||
1396 ' mktg_fcst_value,'||
1397 ' mktg_fcst_value2,'||
1398 ' mktg_fcst_cum,'||
1399 ' projected_backlog,'||
1400 ' projected_backlog_value,'||
1401 ' projected_backlog_value2,'||
1402 ' sales_fcst,'||
1403 ' sales_fcst_value,'||
1404 ' sales_fcst_value2,'||
1405 ' sales_fcst_cum,'||
1406 ' shipment_fcst,'||
1407 ' shipment_fcst_value,'||
1408 ' shipment_fcst_value2,'||
1409 ' shipment_fcst_cum,'||
1410 ' attribute1,'||
1411 ' attribute2,'||
1412 ' attribute3,'||
1413 ' attribute4,'||
1414 ' attribute5,'||
1415 ' attribute6,'||
1416 ' attribute7,'||
1417 ' attribute8,'||
1418 ' attribute9,'||
1419 ' attribute10,'||
1420 ' created_by, creation_date,'||
1421 ' last_updated_by, last_update_date, last_update_login'||
1422 ' )'||
1423 ' select'||
1424 ' :p_st_transaction_id,'||
1425 ' 0,'||
1426 ' f.sr_instance_id,'||
1427 ' f.organization_id,'||
1428 ' f.owning_inst_id,'||
1429 ' f.owning_org_id,'||
1430 ' f.inventory_item_id,'||
1431 ' f.customer_id,'||
1432 ' f.customer_site_id,'||
1433 ' f.region_id,'||
1434 ' mtp.organization_code,'||
1435 ' mtp2.organization_code,'||
1436 ' mi.item_name,'||
1437 ' decode(f.customer_id, -23453, null, cmv.customer_name),'||
1438 ' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
1439 ' decode(f.region_id, -23453, null, cmv.zone),'||
1440 ' f.demand_class,'||
1441 ' f.start_date,'||
1442 ' f.consensus_fcst,'||
1443 ' f.consensus_fcst_value,'||
1444 ' f.consensus_fcst_value2,'||
1445 ' f.consensus_fcst_cum,'||
1446 ' f.priority,'||
1447 ' f.annual_plan_value,'||
1448 ' f.annual_plan_value2,'||
1449 ' f.booking_fcst,'||
1450 ' f.booking_fcst_value,'||
1451 ' f.booking_fcst_value2,'||
1452 ' f.booking_fcst_cum,'||
1453 ' f.budget,'||
1454 ' f.budget2,'||
1455 ' f.budget_cum,'||
1456 ' f.budget2_cum,'||
1457 ' f.final_fcst,'||
1458 ' f.final_fcst_value,'||
1459 ' f.final_fcst_value2,'||
1460 ' f.final_fcst_cum,'||
1461 ' f.financial_fcst_value,'||
1462 ' f.financial_fcst_value2,'||
1463 ' f.mktg_fcst,'||
1464 ' f.mktg_fcst_value,'||
1465 ' f.mktg_fcst_value2,'||
1466 ' f.mktg_fcst_cum,'||
1467 ' f.projected_backlog,'||
1468 ' f.projected_backlog_value,'||
1469 ' f.projected_backlog_value2,'||
1470 ' f.sales_fcst,'||
1471 ' f.sales_fcst_value,'||
1472 ' f.sales_fcst_value2,'||
1473 ' f.sales_fcst_cum,'||
1474 ' f.shipment_fcst,'||
1475 ' f.shipment_fcst_value,'||
1476 ' f.shipment_fcst_value2,'||
1477 ' f.shipment_fcst_cum,'||
1478 ' f.attribute1,'||
1479 ' f.attribute2,'||
1480 ' f.attribute3,'||
1481 ' f.attribute4,'||
1482 ' f.attribute5,'||
1483 ' f.attribute6,'||
1484 ' f.attribute7,'||
1485 ' f.attribute8,'||
1486 ' f.attribute9,'||
1487 ' f.attribute10,'||
1488 ' fnd_global.user_id, sysdate,'||
1489 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
1490 ' from'||
1491 ' '||l_apps_schema||'.msc_demantra_f'||l_suffix||' f,'||
1492 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
1493 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
1494 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
1495 ' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv'||
1496 ' where f.plan_run_id=:p_plan_run_id'||
1497 ' and f.aggr_type=0'||
1498 ' and mtp.partner_type(+)=3'||
1499 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
1500 ' and mtp.sr_tp_id(+)=f.organization_id'||
1501 ' and mtp2.partner_type(+)=3'||
1502 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
1503 ' and mtp2.sr_tp_id(+)=f.owning_org_id'||
1504 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
1505 ' and cmv.customer_id(+)=f.customer_id'||
1506 ' and cmv.customer_site_id(+)=f.customer_site_id'||
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||
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,'||
1525 ' error_code,'||
1526 ' sr_instance_id,'||
1527 ' organization_id,'||
1528 ' owning_inst_id,'||
1529 ' owning_org_id,'||
1530 ' inventory_item_id,'||
1531 ' customer_id,'||
1532 ' customer_site_id,'||
1533 ' region_id,'||
1534 ' organization_code,'||
1535 ' owning_org_code,'||
1536 ' item_name,'||
1537 ' customer_name,'||
1538 ' customer_site_code,'||
1539 ' zone,'||
1540 ' demand_class,'||
1541 ' start_date,'||
1542 ' consensus_fcst,'||
1543 ' consensus_fcst_value,'||
1544 ' consensus_fcst_value2,'||
1545 ' consensus_fcst_cum,'||
1546 ' priority,'||
1547 ' booking_fcst,'||
1548 ' booking_fcst_cum,'||
1549 ' booking_fcst_value,'||
1550 ' booking_fcst_value2,'||
1551 ' budget,'||
1552 ' budget2,'||
1553 ' budget2_cum,'||
1554 ' budget_cum,'||
1555 ' mktg_fcst,'||
1556 ' mktg_fcst_cum,'||
1557 ' mktg_fcst_value,'||
1558 ' mktg_fcst_value2,'||
1559 ' projected_backlog,'||
1560 ' sales_fcst,'||
1561 ' sales_fcst_cum,'||
1562 ' sales_fcst_value,'||
1563 ' sales_fcst_value2,'||
1564 ' shipment_fcst,'||
1565 ' shipment_fcst_cum,'||
1566 ' shipment_fcst_value,'||
1567 ' shipment_fcst_value2,'||
1568 ' created_by, creation_date,'||
1569 ' last_updated_by, last_update_date, last_update_login)'||
1570 ' select'||
1571 ' :p_st_transaction_id,'||
1572 ' 0,'||
1573 ' f.sr_instance_id,'||
1574 ' f.organization_id,'||
1575 ' f.owning_inst_id,'||
1576 ' f.owning_org_id,'||
1577 ' f.inventory_item_id,'||
1578 ' f.customer_id,'||
1579 ' f.customer_site_id,'||
1580 ' f.region_id,'||
1581 ' mtp.organization_code,'||
1582 ' mtp2.organization_code,'||
1583 ' mi.item_name,'||
1584 ' decode(f.customer_id, -23453, null, cmv.customer_name),'||
1585 ' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
1586 ' decode(f.region_id, -23453, null, cmv.zone),'||
1587 ' f.demand_class,'||
1588 ' f.start_date,'||
1589 ' sum(f.consensus_fcst),'||
1590 ' sum(f.consensus_fcst_value),'||
1591 ' sum(f.consensus_fcst_value2),'||
1592 ' sum(f.consensus_fcst_cum),'||
1593 ' min(f.priority),'||
1594 ' sum(f.booking_fcst),'||
1595 ' sum(f.booking_fcst_cum),'||
1596 ' sum(f.booking_fcst_value),'||
1597 ' sum(f.booking_fcst_value2),'||
1598 ' sum(f.budget),'||
1599 ' sum(f.budget2),'||
1600 ' sum(f.budget2_cum),'||
1601 ' sum(f.budget_cum),'||
1602 ' sum(f.mktg_fcst),'||
1603 ' sum(f.mktg_fcst_cum),'||
1604 ' sum(f.mktg_fcst_value),'||
1605 ' sum(f.mktg_fcst_value2),'||
1606 ' sum(f.projected_backlog),'||
1607 ' sum(f.sales_fcst),'||
1608 ' sum(f.sales_fcst_cum),'||
1609 ' sum(f.sales_fcst_value),'||
1610 ' sum(f.sales_fcst_value2),'||
1611 ' sum(f.shipment_fcst),'||
1612 ' sum(f.shipment_fcst_cum),'||
1613 ' sum(f.shipment_fcst_value),'||
1614 ' sum(f.shipment_fcst_value2),'||
1615 ' fnd_global.user_id, sysdate,'||
1616 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
1617 ' from'||
1618 ' (select'||
1619 ' sr_instance_id,'||
1620 ' organization_id,'||
1621 ' owning_inst_id,'||
1622 ' owning_org_id,'||
1623 ' inventory_item_id,'||
1624 ' customer_id,'||
1625 ' customer_site_id,'||
1626 ' region_id,'||
1627 ' demand_class,'||
1628 ' start_date,'||
1629 ' consensus_fcst,'||
1630 ' consensus_fcst_value,'||
1631 ' consensus_fcst_value2,'||
1632 ' consensus_fcst_cum,'||
1633 ' priority,'||
1634 ' to_number(null) booking_fcst,'||
1635 ' to_number(null) booking_fcst_cum,'||
1636 ' to_number(null) booking_fcst_value,'||
1637 ' to_number(null) booking_fcst_value2,'||
1638 ' to_number(null) budget,'||
1639 ' to_number(null) budget2,'||
1640 ' to_number(null) budget2_cum,'||
1641 ' to_number(null) budget_cum,'||
1642 ' to_number(null) mktg_fcst,'||
1643 ' to_number(null) mktg_fcst_cum,'||
1644 ' to_number(null) mktg_fcst_value,'||
1645 ' to_number(null) mktg_fcst_value2,'||
1646 ' to_number(null) projected_backlog,'||
1647 ' to_number(null) sales_fcst,'||
1648 ' to_number(null) sales_fcst_cum,'||
1649 ' to_number(null) sales_fcst_value,'||
1650 ' to_number(null) sales_fcst_value2,'||
1651 ' to_number(null) shipment_fcst,'||
1652 ' to_number(null) shipment_fcst_cum,'||
1653 ' to_number(null) shipment_fcst_value,'||
1654 ' to_number(null) shipment_fcst_value2'||
1655 ' from '||l_apps_schema||'.msc_demantra_f'||l_suffix||
1656 ' where plan_run_id=:p_plan_run_id and aggr_type=0'||
1657 ' union all'||
1658 ' select'||
1659 ' sr_instance_id,'||
1660 ' organization_id,'||
1661 ' sr_instance_id owning_inst_id,'||
1662 ' organization_id owning_org_id,'||
1663 ' inventory_item_id,'||
1664 ' customer_id,'||
1665 ' customer_site_id,'||
1666 ' region_id,'||
1667 ' demand_class,'||
1668 ' end_date,'||
1669 ' to_number(null) consensus_fcst,'||
1670 ' to_number(null) consensus_fcst_value,'||
1671 ' to_number(null) consensus_fcst_value2,'||
1672 ' to_number(null) consensus_fcst_cum,'||
1673 ' to_number(null) priority,'||
1674 ' booking_fcst,'||
1675 ' booking_fcst_cum,'||
1676 ' booking_fcst_value,'||
1677 ' booking_fcst_value2,'||
1678 ' budget,'||
1679 ' budget2,'||
1680 ' budget2_cum,'||
1681 ' budget_cum,'||
1682 ' mktg_fcst,'||
1683 ' mktg_fcst_cum,'||
1684 ' mktg_fcst_value,'||
1685 ' mktg_fcst_value2,'||
1686 ' projected_backlog,'||
1687 ' sales_fcst,'||
1688 ' sales_fcst_cum,'||
1689 ' sales_fcst_value,'||
1690 ' sales_fcst_value2,'||
1691 ' shipment_fcst,'||
1692 ' shipment_fcst_cum,'||
1693 ' shipment_fcst_value,'||
1694 ' shipment_fcst_value2'||
1695 ' from '||l_apps_schema||'.msc_demantra_ods_f'||l_suffix||
1696 ' where end_date between :p_plan_start_date and :p_plan_cutoff_date'||
1697 ' ) f,'||
1698 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
1699 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,'||
1700 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
1701 ' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv'||
1702 ' where mtp.partner_type(+)=3'||
1703 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
1704 ' and mtp.sr_tp_id(+)=f.organization_id'||
1705 ' and mtp2.partner_type(+)=3'||
1706 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
1707 ' and mtp2.sr_tp_id(+)=f.owning_org_id'||
1708 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
1709 ' and cmv.customer_id(+)=f.customer_id'||
1710 ' and cmv.customer_site_id(+)=f.customer_site_id'||
1711 ' and cmv.region_id(+)=f.region_id'||
1712 ' group by'||
1713 ' f.sr_instance_id,'||
1714 ' f.organization_id,'||
1715 ' f.owning_inst_id,'||
1716 ' f.owning_org_id,'||
1717 ' f.inventory_item_id,'||
1718 ' f.customer_id,'||
1719 ' f.customer_site_id,'||
1720 ' f.region_id,'||
1721 ' mtp.organization_code,'||
1722 ' mtp2.organization_code,'||
1723 ' mi.item_name,'||
1724 ' decode(f.customer_id, -23453, null, cmv.customer_name),'||
1725 ' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
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;
1735 msc_phub_util.log('msc_demantra_pkg.export_demantra_f: complete, retcode='||retcode);
1736
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,
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;
1755 errbuf := null;
1756
1757 delete from msc_st_demantra_ods_f where st_transaction_id=p_st_transaction_id;
1758 commit;
1759
1760 l_sql :=
1761 ' insert into msc_st_demantra_ods_f('||
1762 ' st_transaction_id,'||
1763 ' error_code,'||
1764 ' sr_instance_id,'||
1765 ' organization_id,';
1766 if (p_source_version > '11.5.10') then l_sql := l_sql||
1767 ' owning_inst_id,'||
1768 ' owning_org_id,';
1769 end if;
1770 l_sql := l_sql||
1771 ' inventory_item_id,'||
1772 ' customer_id,'||
1773 ' customer_site_id,'||
1774 ' organization_code,';
1775 if (p_source_version > '11.5.10') then l_sql := l_sql||
1776 ' owning_org_code,';
1777 end if;
1778 l_sql := l_sql||
1779 ' item_name,'||
1780 ' customer_name,'||
1781 ' customer_site_code,'||
1782 ' demand_class,'||
1783 ' end_date,'||
1784 ' production_history,'||
1785 ' actual_backlog,'||
1786 ' shipment_history,'||
1787 ' shipment_history_value,'||
1788 ' shipment_history_value2,'||
1789 ' booking_history,'||
1790 ' booking_history_value,'||
1791 ' booking_history_value2,'||
1792 ' consen_fcst_accrcy_mape_4week,'||
1793 ' consen_fcst_accrcy_mape_8week,'||
1794 ' consen_fcst_accrcy_mape_13week,';
1795 if (p_source_version > '11.5.10') then l_sql := l_sql||
1796 ' returns_history,'||
1797 ' actual_backlog_value,'||
1798 ' actual_backlog_value2,'||
1799 ' inventory_history,'||
1800 ' inventory_history_value,'||
1801 ' inventory_history_value2,'||
1802 ' booking_history_rd,'||
1803 ' booking_history_rd_value,'||
1804 ' booking_history_rd_value2,';
1805 end if;
1806 l_sql := l_sql||
1807 ' created_by, creation_date,'||
1808 ' last_updated_by, last_update_date, last_update_login'||
1809 ' )'||
1810 ' select'||
1811 ' :p_st_transaction_id,'||
1812 ' 0,'||
1813 ' f.sr_instance_id,'||
1814 ' f.organization_id,';
1815 if (p_source_version > '11.5.10') then l_sql := l_sql||
1816 ' f.owning_inst_id,'||
1817 ' f.owning_org_id,';
1818 end if;
1819 l_sql := l_sql||
1820 ' f.inventory_item_id,'||
1821 ' f.customer_id,'||
1822 ' f.customer_site_id,'||
1823 ' mtp.organization_code,';
1824 if (p_source_version > '11.5.10') then l_sql := l_sql||
1825 ' mtp2.organization_code,';
1826 end if;
1827 l_sql := l_sql||
1828 ' mi.item_name,'||
1829 ' decode(f.customer_id, -23453, null, cmv.customer_name),'||
1830 ' decode(f.customer_site_id, -23453, null, cmv.customer_site),'||
1831 ' f.demand_class,'||
1832 ' f.end_date,'||
1833 ' f.production_history,'||
1834 ' f.actual_backlog,'||
1835 ' f.shipment_history,'||
1836 ' f.shipment_history_value,'||
1837 ' f.shipment_history_value2,'||
1838 ' f.booking_history,'||
1839 ' f.booking_history_value,'||
1840 ' f.booking_history_value2,'||
1841 ' f.consen_fcst_accrcy_mape_4week,'||
1842 ' f.consen_fcst_accrcy_mape_8week,'||
1843 ' f.consen_fcst_accrcy_mape_13week,';
1844 if (p_source_version > '11.5.10') then l_sql := l_sql||
1845 ' f.returns_history,'||
1846 ' f.actual_backlog_value,'||
1847 ' f.actual_backlog_value2,'||
1848 ' f.inventory_history,'||
1849 ' f.inventory_history_value,'||
1850 ' f.inventory_history_value2,'||
1851 ' f.booking_history_rd,'||
1852 ' f.booking_history_rd_value,'||
1853 ' f.booking_history_rd_value2,';
1854 end if;
1855 l_sql := l_sql||
1856 ' fnd_global.user_id, sysdate,'||
1857 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
1858 ' from'||
1859 ' '||l_apps_schema||'.msc_demantra_ods_f'||l_suffix||' f,'||
1860 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,';
1861 if (p_source_version > '11.5.10') then l_sql := l_sql||
1862 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp2,';
1863 end if;
1864 l_sql := l_sql||
1865 ' '||l_apps_schema||'.msc_items'||l_suffix||' mi,'||
1866 ' '||l_apps_schema||'.msc_phub_customers_mv'||l_suffix||' cmv'||
1867 ' where mtp.partner_type(+)=3'||
1868 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
1869 ' and mtp.sr_tp_id(+)=f.organization_id';
1870 if (p_source_version > '11.5.10') then l_sql := l_sql||
1871 ' and mtp2.partner_type(+)=3'||
1872 ' and mtp2.sr_instance_id(+)=f.owning_inst_id'||
1873 ' and mtp2.sr_tp_id(+)=f.owning_org_id';
1874 end if;
1875 l_sql := l_sql||
1876 ' and mi.inventory_item_id(+)=f.inventory_item_id'||
1877 ' and cmv.customer_id(+)=f.customer_id'||
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
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;