[Home] [Help]
PACKAGE BODY: APPS.MSC_PHUB_PKG
Source
1 PACKAGE BODY msc_phub_pkg AS
2 /* $Header: MSCHBPBB.pls 120.131.12020000.4 2012/12/21 16:58:27 wexia ship $ */
3
4 partition_add constant number := 1;
5 partition_drop constant number := 2;
6 partition_truncate constant number := 3;
7 partition_init constant number := 4; -- not exist: add, exist: truncate
8
9 partition_range constant number := 5;
10 partition_list constant number := 6;
11
12 function item_dim_table return object_names
13 is
14 l_tables object_names;
15 begin
16 select 'MSC_APCC_ITEM_D' bulk collect into l_tables from dual;
17 return l_tables;
18 end item_dim_table;
19
20 function meta_info return msc_apcc_fact_type_table
21 is
22 t msc_apcc_fact_type_table := msc_apcc_fact_type_table();
23 n number;
24 begin
25 t.extend(15);
26 t(1) := msc_apcc_fact_type(1, 'msc_supply_pkg', 'supplies', '11.5.10', 'supply_date', 1);
27 t(2) := msc_apcc_fact_type(2, 'msc_demand_pkg', 'demands', '11.5.10', 'order_date', 1);
28 t(3) := msc_apcc_fact_type(3, 'msc_demand_pkg', 'demands_cum', '11.5.10', 'order_date', 1);
29 t(4) := msc_apcc_fact_type(4, 'msc_demantra_pkg', 'demantra_ods', '11.5.10', 'end_date', 4);
30 t(5) := msc_apcc_fact_type(5, 'msc_demantra_pkg', 'demantra', '11.5.10', 'start_date', 3);
31 t(6) := msc_apcc_fact_type(6, 'msc_exception_pkg', 'exceptions', '11.5.10', 'analysis_date', 1);
32 t(7) := msc_apcc_fact_type(7, 'msc_item_pkg', 'item_orders', '11.5.10', 'order_date', 1);
33 t(8) := msc_apcc_fact_type(8, 'msc_supply_pkg', 'item_wips', '11.5.10', 'wip_start_date', 2);
34 t(9) := msc_apcc_fact_type(9, 'msc_resource_pkg', 'resources', '11.5.10', 'analysis_date', 2);
35 t(10) := msc_apcc_fact_type(10, 'msc_supplier_pkg', 'suppliers', '11.5.10', 'analysis_date', 1);
36 t(11) := msc_apcc_fact_type(11, 'msc_item_pkg', 'item_inventory', '11.5.10', 'order_date', 1);
37 t(12) := msc_apcc_fact_type(12, 'msc_phub_excess_pkg', 'items', '11.5.10', null, 2); -- 11.5.10 item_inventory
38 t(13) := msc_apcc_fact_type(13, 'msc_phub_budget_pkg', 'budgets', '12.1.3', null, 0);
39 t(14) := msc_apcc_fact_type(14, 'msc_phub_cost_pkg', 'costs', '11.5.10', 'detail_date', 1); -- 11.5.10 item_inventory
40 t(15) := msc_apcc_fact_type(15, 'msc_resource_pkg', 'resources_cum', '12.1.3', 'analysis_date', 2);
41 return t;
42 end meta_info;
43
44 function list_fact_tables(p_plan_type number) return object_names
45 is
46 r object_names;
47 begin
48 case when p_plan_type is null then
49 select upper('msc_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type not in (4);
50 when p_plan_type in (-1) then
51 select upper('msc_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type in (1,2,3,7,8,9,11,15,16);
52 when p_plan_type in (10) then
53 select upper('msc_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type in (5);
54 else
55 select upper('msc_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type not in (4,5);
56 end case;
57 return r;
58 end list_fact_tables;
59
60 function list_staging_tables(p_plan_type number, p_include_ods number) return object_names
61 is
62 r object_names;
63 begin
64 case when p_plan_type is null then
65 select upper('msc_st_'||entity_name||'_f') bulk collect into r from table(meta_info);
66 when p_plan_type in (10) then
67 select upper('msc_st_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type in (4) and p_include_ods=sys_yes;
68 else
69 select upper('msc_st_'||entity_name||'_f') bulk collect into r from table(meta_info) where fact_type in (2,7,9,10,11,12,14);
70 end case;
71 return r;
72 end list_staging_tables;
73
74 function list_plan_runs(p_plan_name varchar2, p_plan_run_id number) return msc_apcc_number_list
75 is
76 r msc_apcc_number_list;
77 begin
78 select plan_run_id bulk collect into r
79 from msc_plan_runs
80 where (plan_name=p_plan_name and plan_run_id=nvl(p_plan_run_id,plan_run_id))
81 and archive_flag=1
82 union
83 select p_plan_run_id from dual where p_plan_run_id is not null;
84
85 return r;
86 end list_plan_runs;
87
88 function get_plan_type_meaning(p_plan_type number) return varchar2
89 is
90 l_meaning varchar2(80);
91 begin
92 select meaning into l_meaning
93 from mfg_lookups
94 where lookup_type='MSC_SCN_PLAN_TYPES'
95 and lookup_code=p_plan_type;
96
97 return l_meaning;
98 exception
99 when others then
100 return null;
101 end get_plan_type_meaning;
102
103 function check_apcc_setup return number is
104 l_category_set_id1 varchar2(200) := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
105 l_category_set_id2 varchar2(200) := fnd_profile.value('MSC_HUB_CAT_SET_ID_2');
106 l_category_set_id3 varchar2(200) := fnd_profile.value('MSC_HUB_CAT_SET_ID_3');
107 l_hub_cal_code varchar2(200) := fnd_profile.value('MSC_HUB_CAL_CODE');
108 l_hub_pr_set varchar2(200) := fnd_profile.value('MSC_HUB_PERIOD_SET_NAME');
109 l_hub_cur_code varchar2(200) := fnd_profile.value('MSC_HUB_CUR_CODE_RPT');
110 l_hur_reg_instance varchar2(200) := fnd_profile.value('MSC_HUB_REGION_INSTANCE');
111 l_top_bottom_n varchar2(200) := fnd_profile.value('MSC_HUB_TOP_BOTTOM_N_VALUE');
112 begin
113 if l_category_set_id1 is null then
114 return 2;
115 end if;
116 return 1;
117 end check_apcc_setup;
118
119 function has_etl(p_package varchar2, p_plan_type number) return boolean is
120 begin
121 case
122 when p_package='msc_exception_pkg' then return p_plan_type not in (-1);
123 when p_package='msc_supply_pkg' then return true;
124 when p_package='msc_demand_pkg' then return true;
125 when p_package='msc_phub_cost_pkg' then return p_plan_type not in (-1);
126 when p_package='msc_resource_pkg' then return p_plan_type not in (5);
127 when p_package='msc_supplier_pkg' then return p_plan_type not in (-1,5);
128 when p_package='msc_phub_budget_pkg' then return p_plan_type in (4);
129 when p_package='msc_item_pkg' then return true;
130 when p_package='msc_phub_excess_pkg' then return p_plan_type not in (-1,5);
131 when p_package='msc_phub_custom_pkg' then return true;
132 else return false;
133 end case;
134 end has_etl;
135
136 function submit_each(p_package varchar2, p_plan_id number, p_plan_run_id number, p_plan_type number)
137 return number is
138 l_req_id number := null;
139 begin
140 if (has_etl(p_package, p_plan_type)) then
141 msc_phub_util.log('msc_phub_pkg.submit_each('||p_package||','||p_plan_id||','||p_plan_run_id||')');
142 l_req_id := fnd_request.submit_request('MSC', 'MSCHUBA2', null,
143 null, false, p_package, p_plan_id, p_plan_run_id);
144 msc_phub_util.log('msc_phub_pkg.submit_each: l_req_id='||l_req_id);
145 commit;
146 end if;
147 return l_req_id;
148 end submit_each;
149
150 procedure msc_wait_for_request(p_request_id in number)
151 is
152 l_refreshed_flag NUMBER;
153 l_pending_timeout_flag NUMBER;
154 l_start_time DATE;
155
156 l_call_status boolean;
157 l_phase varchar2(80);
158 l_status varchar2(80);
159 l_dev_phase varchar2(80);
160 l_dev_status varchar2(80);
161 l_message varchar2(240);
162
163 l_ctr number := 0;
164 l_timeout number := 9999;
165 e_msc_wait_for_request exception;
166 begin
167 if (p_request_id is null) then
168 return;
169 end if;
170
171 l_start_time := SYSDATE;
172 loop
173 << begin_loop >>
174 dbms_lock.sleep(10);
175 l_pending_timeout_flag := sign( sysdate - l_start_time - l_timeout/1440.0);
176 l_call_status:= fnd_concurrent.wait_for_request(p_request_id,
177 10, 10, l_phase, l_status, l_dev_phase, l_dev_status, l_message);
178 --msc_phub_util.log('msc_wait_for_request '||p_request_id||' complete status '||l_dev_phase||' - '||l_dev_status);
179 exit when l_call_status=FALSE;
180 if l_dev_phase='PENDING' then
181 exit when l_pending_timeout_flag= 1;
182 elsif l_dev_phase='RUNNING' then
183 GOTO begin_loop;
184 elsif l_dev_phase='COMPLETE' then
185 if l_dev_status = 'NORMAL' then
186 --msc_phub_util.log('msc_wait_for_request '||p_request_id||' complete status '||l_dev_phase||' - '||l_dev_status);
187 return;
188 end if;
189 exit;
190 elsif l_dev_phase='INACTIVE' THEN
191 exit when l_pending_timeout_flag= 1;
192 end if;
193 -- dbms_lock.sleep(10);
194 end loop;
195
196 msc_phub_util.log('msc_phub_pkg.msc_wait_for_request: failed: '||p_request_id||','||
197 l_phase||','||l_status||','||l_dev_phase||','||l_dev_status||','||l_message);
198 raise e_msc_wait_for_request;
199 end msc_wait_for_request;
200
201 function populate_facts(p_plan_id number, p_plan_run_id number, p_plan_type number) return number
202 is
203 l_exceptions_req_id number := null;
204 l_resource_req_id number := null;
205 l_suppliers_req_id number := null;
206 l_supplies_req_id number := null;
207 l_demands_req_id number := null;
208 l_items_req_id number := null;
209 l_budgets_req_id number := null;
210 l_costs_req_id number := null;
211 l_excesss_req_id number := null;
212 l_custom_req_id number := null;
213
214 l_max_wait_time number := 999999;
215 begin
216 msc_phub_util.log('msc_phub_pkg.populate_facts('||p_plan_id||','||p_plan_run_id||','||p_plan_type||')');
217 l_exceptions_req_id := submit_each('msc_exception_pkg', p_plan_id, p_plan_run_id, p_plan_type);
218 l_supplies_req_id := submit_each('msc_supply_pkg', p_plan_id, p_plan_run_id, p_plan_type);
219 l_demands_req_id := submit_each('msc_demand_pkg', p_plan_id, p_plan_run_id, p_plan_type);
220 l_costs_req_id := submit_each('msc_phub_cost_pkg', p_plan_id, p_plan_run_id, p_plan_type);
221 l_resource_req_id := submit_each('msc_resource_pkg', p_plan_id, p_plan_run_id, p_plan_type);
222 l_suppliers_req_id := submit_each('msc_supplier_pkg', p_plan_id, p_plan_run_id, p_plan_type);
223 l_budgets_req_id := submit_each('msc_phub_budget_pkg', p_plan_id, p_plan_run_id, p_plan_type);
224
225 msc_wait_for_request(l_supplies_req_id);
226 msc_wait_for_request(l_demands_req_id);
227 msc_wait_for_request(l_exceptions_req_id);
228
229 l_items_req_id := submit_each('msc_item_pkg', p_plan_id, p_plan_run_id, p_plan_type);
230 l_excesss_req_id := submit_each('msc_phub_excess_pkg', p_plan_id, p_plan_run_id, p_plan_type);
231
232 msc_wait_for_request(l_resource_req_id);
233 msc_wait_for_request(l_suppliers_req_id);
234 msc_wait_for_request(l_items_req_id);
235 msc_wait_for_request(l_budgets_req_id);
236 msc_wait_for_request(l_costs_req_id);
237 msc_wait_for_request(l_excesss_req_id);
238
239 l_custom_req_id := submit_each('msc_phub_custom_pkg', p_plan_id, p_plan_run_id, p_plan_type);
240 --msc_wait_for_request(l_custom_req_id);
241 return sys_yes;
242
243 exception
244 when others then
245 return sys_no;
246 end populate_facts;
247
248 procedure manage_partitions(p_tables object_names, p_partition_id number,
249 p_mode number, p_partition_type number)
250 is
251 l_partition_name varchar2(300);
252 l_ddl varchar2(300);
253 dummy1 varchar2(50);
254 dummy2 varchar2(50);
255 l_n number;
256 l_applsys_schema varchar2(100);
257 e_manage_partitions exception;
258 e_partition_not_exist exception;
259 e_deadlock exception;
260 retcode number := 0;
261 errbuf varchar2(2000);
262 pragma exception_init (e_partition_not_exist, -2149);
263 pragma exception_init (e_deadlock, -60);
264 begin
265 msc_phub_util.log('msc_phub_pkg.manage_partitions('||
266 p_tables.count||', '||p_partition_id||', '||
267 p_mode||', '||p_partition_type||')');
268 if (p_partition_id is null or p_tables.count=0) then
269 return;
270 end if;
271
272 if (fnd_installation.get_app_info('FND',
273 dummy1, dummy2, l_applsys_schema) = false) then
274 retcode := -1;
275 fnd_message.set_name('MSC', 'MSC_PART_UNDEFINED_SCHEMA');
276 errbuf := fnd_message.get;
277 raise e_manage_partitions;
278 end if;
279
280 for i in 1..p_tables.count loop
281 l_partition_name := msc_phub_util.get_partition_name(p_tables(i), p_partition_id);
282 l_ddl := null;
283 if (p_mode in (partition_add, partition_init)) then
284 -- create partitions
285 select count(*)
286 into l_n
287 from all_tab_partitions
288 where table_owner=msc_phub_util.msc_schema
289 and table_name=p_tables(i)
290 and partition_name=l_partition_name;
291
292 if (l_n = 0) then
293 if (p_partition_type = partition_range) then
294 l_ddl := 'alter table '||p_tables(i)||
295 ' add partition '||l_partition_name||
296 ' values less than ('||to_char(p_partition_id+1)||')';
297 else
298 l_ddl := 'alter table '||p_tables(i)||
299 ' add partition '||l_partition_name||
300 ' values ('||to_char(p_partition_id)||')';
301 end if;
302 else
303 if (p_mode = partition_init) then
304 l_ddl := 'alter table '||p_tables(i)||' truncate partition '||l_partition_name;
305 end if;
306 end if;
307 elsif (p_mode = partition_drop) then
308 --drop partitions
309 l_ddl := 'alter table '||p_tables(i)||' drop partition '||l_partition_name;
310 elsif (p_mode = partition_truncate) then
311 --truncate partitions
312 l_ddl := 'alter table '||p_tables(i)||' truncate partition '||l_partition_name;
313 end if;
314
315 if (l_ddl is not null) then
316 msc_phub_util.log(l_ddl);
317 for j in 1..1800 loop
318 begin
319 ad_ddl.do_ddl(l_applsys_schema, 'MSC', ad_ddl.alter_table, l_ddl, p_tables(i));
320 exit;
321
322 exception
323 when e_partition_not_exist then exit;
324 when e_deadlock then null;
325 when others then raise;
326 end;
327 dbms_lock.sleep(10);
328 end loop;
329 end if;
330 end loop;
331
332 msc_phub_util.log('msc_phub_pkg.manage_partitions: complete');
333
334 exception
335 when others then
336 if (retcode = 0) then
337 retcode := -1;
338 errbuf := sqlerrm;
339 end if;
340 msc_phub_util.log('msc_phub_pkg.manage_partitions.exception: '||errbuf);
341 raise;
342
343 end manage_partitions;
344
345 function get_plan_info(p_plan_id number, p_plan_type number) return plan_info
346 is
347 l_pi plan_info;
348 begin
349 if (p_plan_type = 10) then
350 select
351 das.scenario_id plan_id,
352 substr(das.scenario_name, 1, 50) plan_name,
353 substr(das.scenario_name, 1, 100) plan_description,
354 to_number(10) plan_type,
355 decode(das.sr_instance_id, -23453, o.sr_instance_id, das.sr_instance_id) sr_instance_id,
356 decode(das.organization_id, -23453, o.sr_tp_id, das.organization_id) organization_id,
357 tq.from_date plan_start_date,
358 tq.until_date plan_cutoff_date,
359 nvl(dsr.last_update_date, sysdate) plan_completion_date,
360 null publisher
361 into l_pi
362 from
363 msd_dp_ascp_scenarios_v das,
364 msd_dp_scenario_revisions dsr,
365 msd_dem_transfer_query tq,
366 (select mtp.sr_instance_id, mtp.sr_tp_id
367 from msc_trading_partners mtp, msd_dem_app_instance_orgs daio
368 where daio.organization_id=nvl(fnd_profile.value('MSD_DEM_MASTER_ORG'), -23453)
369 and mtp.organization_code=daio.organization_code
370 and mtp.partner_type=3
371 union all select to_number(-23453), to_number(-23453) from dual) o
372 where das.demand_plan_name=substr(tq.query_name(+), 1, 30)
373 and das.scenario_id=p_plan_id
374 and das.scenario_id=dsr.scenario_id(+)
375 and das.last_revision=dsr.revision(+)
376 and rownum=1;
377 else
378 select
379 p.plan_id,
380 decode(nvl(copy_plan_id,-1), -1, compile_designator,
381 (select compile_designator from msc_plans where plan_id=p.copy_plan_id)) plan_name,
382 p.description plan_description,
383 (case when p.curr_plan_type in (101,102,103,105) then 101
384 when p.plan_type in (1,2,3) then 1
385 else nvl(p.plan_type, -1) end) plan_type,
386 p.sr_instance_id,
387 p.organization_id,
388 trunc(nvl(b.bkt_start_date, p.curr_start_date)) plan_start_date,
389 trunc(nvl(b.bkt_end_date, p.curr_cutoff_date)) plan_cutoff_date,
390 nvl(p.plan_completion_date, sysdate) plan_completion_date,
391 null publisher
392 into l_pi
393 from msc_plans p,
394 (select plan_id, min(bkt_start_date) bkt_start_date, max(bkt_end_date) bkt_end_date
395 from msc_plan_buckets
396 where curr_flag=1
397 group by plan_id
398 ) b
399 where p.plan_id=p_plan_id
400 and p.plan_id=b.plan_id(+);
401 end if;
402 return l_pi;
403
404 exception
405 when others then
406 msc_phub_util.log('msc_phub_pkg.get_plan_info.exception: '||sqlerrm);
407 raise;
408 end get_plan_info;
409
410 function make_demantra_plan_info(p_plan_id number, p_publisher varchar2, p_plan_name varchar2) return plan_info
411 is
412 l_pi plan_info;
413 begin
414 l_pi.plan_id := p_plan_id;
415 l_pi.plan_name := p_plan_name;
416 l_pi.plan_description := p_plan_name;
417 l_pi.plan_type := 10;
418 l_pi.sr_instance_id := -23453;
419 l_pi.organization_id := -23453;
420 l_pi.plan_start_date := null;
421 l_pi.plan_cutoff_date := null;
422 l_pi.plan_completion_date := sysdate;
423 l_pi.publisher := p_publisher;
424 return l_pi;
425 end make_demantra_plan_info;
426
427 procedure purge_previous_plan_run(p_plan_run_id number)
428 is
429 l_prev_plan_run_id number;
430 l_max_wait_time number := 999999;
431 l_request_id number;
432 l_plan_name varchar2(50);
433 begin
434 msc_phub_util.log('msc_phub_pkg.purge_previous_plan_run('||p_plan_run_id||')');
435
436 select plan_name into l_plan_name from msc_plan_runs where plan_run_id=p_plan_run_id;
437
438 select max(plan_run_id)
439 into l_prev_plan_run_id
440 from msc_plan_runs
441 where plan_name=l_plan_name
442 and plan_run_id<p_plan_run_id
443 and nvl(archive_flag,sys_no)=sys_yes;
444
445 if (l_prev_plan_run_id is not null) then
446 l_request_id := fnd_request.submit_request('MSC','MSCHUBP',
447 null, null, false, l_plan_name, l_prev_plan_run_id);
448 commit;
449
450 msc_phub_util.log('msc_phub_pkg.purge_previous_plan_run, MSCHUBP('||
451 l_plan_name||','||l_prev_plan_run_id||'), l_request_id='||l_request_id);
452
453 if (l_request_id = 0) then
454 purge_details(l_plan_name, l_prev_plan_run_id);
455 end if;
456 else
457 msc_phub_util.log('msc_phub_pkg.purge_previous_plan_run, l_prev_plan_run_id='||l_prev_plan_run_id);
458 end if;
459
460 exception
461 when others then
462 msc_phub_util.log('msc_phub_pkg.purge_previous_plan_run.exception: '||sqlerrm);
463 raise;
464 end purge_previous_plan_run;
465
466 function remote_context_sql(p_dblink varchar2, p_sql varchar2) return varchar2
467 is
468 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
469 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
470 begin
471 execute immediate 'select sysdate from dual'||l_suffix;
472
473 return
474 ' declare'||
475 ' l_user_id number;'||
476 ' l_responsibility_id number;'||
477 ' l_application_id number;'||
478 ' begin'||
479 ' select user_id into l_user_id'||
480 ' from '||l_apps_schema||'.fnd_user'||l_suffix||
481 ' where user_name=fnd_global.user_name;'||
482 ' '||
483 ' select responsibility_id into l_responsibility_id'||
484 ' from '||l_apps_schema||'.fnd_responsibility'||l_suffix||
485 ' where responsibility_key=('||
486 ' select responsibility_key from fnd_responsibility'||
487 ' where responsibility_id=fnd_global.resp_id);'||
488 ' '||
489 ' select application_id into l_application_id'||
490 ' from '||l_apps_schema||'.fnd_application'||l_suffix||
491 ' where application_short_name=fnd_global.application_short_name;'||
492 ' '||
493 ' '||l_apps_schema||'.fnd_global.apps_initialize'||l_suffix||'('||
494 ' l_user_id, l_responsibility_id, l_application_id);'||
495 ' '||p_sql||
496 ' end;';
497
498 exception
499 when others then
500 msc_phub_util.log('msc_phub_pkg.remote_context_sql.exception: '||sqlerrm);
501 fnd_message.set_name('MSC', 'MSC_APCC_DBLINK_E01');
502 fnd_message.set_token('DBLINK', p_dblink);
503 msc_phub_util.log(fnd_message.get);
504 raise;
505 end remote_context_sql;
506
507 procedure publish_to_central_apcc(errbuf out nocopy varchar2, retcode out nocopy varchar2,
508 p_plan_id number, p_plan_run_id number,
509 p_target_plan_name varchar2, p_dblink varchar2,
510 p_include_ods number, p_archive_flag number)
511 is
512 l_db_name varchar2(30) := fnd_profile.value('MSC_APCC_BACK_TO_SELF_DBLINK');
513 l_sql varchar2(2000);
514 l_pi plan_info;
515 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
516 l_request_id number;
517 l_upload_mode number := 3;
518 begin
519 retcode := 0;
520 errbuf := null;
521 msc_phub_util.log('msc_phub_pkg.publish_to_central_apcc('||p_plan_id
522 ||','||p_plan_run_id||','||p_target_plan_name
523 ||','||p_dblink||','||p_include_ods||','||p_archive_flag||')');
524
525 if (l_db_name is null) then
526 select value into l_db_name from v$parameter where name='db_name';
527 end if;
528 msc_phub_util.log('msc_phub_pkg.publish_to_central_apcc:l_db_name='||l_db_name);
529
530 select
531 plan_id,
532 plan_name,
533 plan_description,
534 plan_type,
535 sr_instance_id,
536 organization_id,
537 plan_start_date,
538 plan_cutoff_date,
539 plan_completion_date,
540 publisher
541 into l_pi
542 from msc_plan_runs
543 where plan_id=p_plan_id and plan_run_id=p_plan_run_id;
544
545 if (p_archive_flag = 2) then
546 l_upload_mode := 4;
547 end if;
548
549 l_sql :=
550 ' :1 := fnd_request.submit_request'||l_suffix||'('||
551 ' ''MSC'', ''MSCHUBFI'', null, null, false,'||
552 ' :transfer_id, :debug_level, :source_plan_id, :source_plan_run_id,'||
553 ' :source_db_link, :source_version, :target_plan_name,'||
554 ' :plan_description, :plan_type, :directory, :upload_mode, :include_ods,'||
555 ' :instance_code, :organization_code, :plan_start_date, :plan_cutoff_date);';
556
557 execute immediate remote_context_sql(p_dblink, l_sql) using out l_request_id,
558 to_number(null), to_number(null), p_plan_id, p_plan_run_id,
559 l_db_name, msc_phub_util.g_version, nvl(p_target_plan_name, l_pi.plan_name),
560 l_pi.plan_description, l_pi.plan_type, to_char(null), l_upload_mode, p_include_ods,
561 msc_get_name.instance_code(l_pi.sr_instance_id),
562 msc_get_name.org_code(l_pi.organization_id, l_pi.sr_instance_id),
563 to_char(l_pi.plan_start_date), to_char(l_pi.plan_cutoff_date);
564
565 commit;
566
567 fnd_message.set_name('MSC', 'MSC_APCC_REMOTE_CONFIRM');
568 fnd_message.set_token('DBLINK', p_dblink);
569 fnd_message.set_token('REQ_ID', l_request_id);
570 msc_phub_util.log(fnd_message.get);
571
572 exception
573 when others then
574 if (retcode = 0) then
575 retcode := -1;
576 errbuf := 'msc_phub_pkg.publish_to_central_apcc.exception: '||sqlerrm;
577 end if;
578 msc_phub_util.log(errbuf);
579 end publish_to_central_apcc;
580
581
582 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
583 p_plan_id number default null,
584 p_plan_run_id number default null,
585 p_archive_flag number default sys_yes,
586 p_target_plan_name varchar2 default null,
587 p_dblink varchar2 default null,
588 p_include_ods number default sys_no,
589 p_plan_type number default null,
590 p_scenario_name in varchar2 default null,
591 p_publisher varchar2 default null)
592 is
593 l_plan_run_id number := null;
594 begin
595 l_plan_run_id := populate_details_fn(errbuf, retcode,
596 p_plan_id,
597 p_plan_run_id,
598 p_archive_flag,
599 p_target_plan_name,
600 p_dblink,
601 p_include_ods,
602 p_plan_type,
603 p_scenario_name,
604 p_publisher);
605 end populate_details;
606
607 function populate_details_fn(errbuf out nocopy varchar2, retcode out nocopy varchar2,
608 p_plan_id number default null,
609 p_plan_run_id number default null,
610 p_archive_flag number default sys_yes,
611 p_target_plan_name varchar2 default null,
612 p_dblink varchar2 default null,
613 p_include_ods number default sys_no,
614 p_plan_type number default null,
615 p_scenario_name in varchar2 default null,
616 p_publisher varchar2 default null)
617 return number
618 is
619 l_plan_run_id number := null;
620 l_return_status number := sys_yes;
621
622 l_plan_type number;
623 l_display_kpi number;
624 e_populate_details exception;
625
626 l_n number;
627 l_max_wait_time number := 999999;
628 l_request_id number;
629 l_demantra_flag number := sys_no;
630 l_pi plan_info := null;
631 l_ods_transfer_id number := null;
632 begin
633 msc_phub_util.init_log;
634 retcode := 0;
635 errbuf := null;
636
637 msc_phub_util.log(fnd_message.get_string('MSC','MSC_HUB_POPULATE_STARTS'));
638 execute immediate 'alter session set ddl_lock_timeout=18000';
639
640 msc_phub_util.log('msc_phub_pkg.populate_details_fn('||
641 p_plan_id||','||
642 p_plan_run_id||','||
643 p_archive_flag||','||
644 p_target_plan_name||','||
645 p_dblink||','||
646 p_include_ods||','||
647 p_plan_type||','||
648 p_scenario_name||','||
649 p_publisher||')');
650
651 -- check_apcc_setup
652 if (check_apcc_setup = 2) then
653 retcode := -1;
654 errbuf := fnd_message.get_string('MSC', 'MSC_APCC_INVALID_PROFILE');
655 msc_phub_util.log(errbuf);
656 raise e_populate_details;
657 end if;
658
659 if (p_plan_type=10 or (p_plan_type is null and p_plan_id>5555555)) then
660 l_demantra_flag := sys_yes;
661 l_plan_type := 10;
662
663 if (msc_phub_util.demantra_schema is null) then
664 msc_phub_util.log('msc_phub_pkg.populate_details_fn: (msc_phub_util.demantra_schema is null)');
665 return null;
666 end if;
667 end if;
668
669 -- get_plan_info
670 if (l_demantra_flag = sys_yes and p_publisher is not null) then
671 l_pi := make_demantra_plan_info(p_plan_id, p_publisher, p_target_plan_name);
672 else
673 l_pi := get_plan_info(p_plan_id, l_plan_type);
674 end if;
675
676 -- validate inputs
677 if (l_pi.plan_name is null and p_include_ods=sys_no) then
678 msc_phub_util.log('msc_phub_pkg.populate_details_fn: (l_pi.plan_name is null and p_include_ods=sys_no)');
679 return null;
680 end if;
681
682 -- validate inputs
683 if (l_pi.plan_type in (1,4,8)) then
684 select nvl(display_kpi,1)
685 into l_display_kpi
686 from msc_plans
687 where plan_id=l_pi.plan_id;
688
689 if (l_display_kpi=2) then
690 msc_phub_util.log('msc_phub_pkg.populate_details_fn: (l_pi.plan_type in (1,4,8) and l_display_kpi=2)');
691 return null;
692 end if;
693 end if;
694
695 -- validate inputs
696 select count(1) into l_n
697 from msc_plan_runs
698 where plan_name=l_pi.plan_name
699 and archive_flag=1
700 and local_archive_flag=2;
701
702 if (l_n > 0) then
703 fnd_message.set_name('MSC', 'MSC_APCC_PLAN_INFO_E01');
704 retcode := -1;
705 errbuf := 'msc_phub_pkg.populate_details_fn: '||fnd_message.get;
706 msc_phub_util.log(errbuf);
707 raise e_populate_details;
708 end if;
709
710 begin
711 select plan_type into l_plan_type
712 from msc_plan_runs
713 where plan_name=l_pi.plan_name
714 and archive_flag=1
715 and plan_type<>l_pi.plan_type
716 and rownum=1;
717
718 fnd_message.set_name('MSC', 'MSC_APCC_PLAN_INFO_E03');
719 fnd_message.set_token('PLAN', l_pi.plan_name);
720 fnd_message.set_token('PLAN_TYPE', get_plan_type_meaning(l_plan_type));
721 retcode := -1;
722 errbuf := 'msc_phub_pkg.populate_details_fn: '||fnd_message.get;
723 msc_phub_util.log(errbuf);
724 raise e_populate_details;
725 exception
726 when others then
727 null;
728 end;
729
730 l_plan_run_id := create_plan_run(p_scenario_name, p_include_ods, sys_yes, l_pi);
731 if (l_plan_run_id is null and l_demantra_flag = sys_yes and p_include_ods = sys_yes) then
732 l_ods_transfer_id := create_staging_partitions(l_pi.plan_type, p_include_ods);
733 end if;
734
735 -- this will refresh all mvs for now, but we need to place this call in collections conc program also
736 --pabram need to move collections mv refresh later, need to move this code after populate_facts
737 -- bug 6836759 , comment out refresh_mvs(3) for now
738 --bug 6665805, collections is calling this api after collections run, commenting this 051308
739 --refresh_mvs(3);
740
741 --populate fact/summary tables
742 if (l_demantra_flag = sys_yes) then
743 if (l_pi.plan_id > 0) then
744 msc_demantra_pkg.populate_details(errbuf, retcode, l_pi.plan_id, l_plan_run_id);
745 else
746 if (l_plan_run_id is not null) then
747 msc_demantra_pkg.archive(l_pi.plan_id, l_plan_run_id, 5, p_publisher, null);
748 end if;
749
750 if (p_include_ods = sys_yes) then
751 msc_demantra_pkg.archive(null, null, 4, p_publisher, l_ods_transfer_id);
752 end if;
753 end if;
754 else
755 -- build item dimension
756 build_items_from_pds(l_pi.plan_id);
757 if (populate_facts(l_pi.plan_id, l_plan_run_id, l_pi.plan_type) = sys_no) then
758 retcode := -1;
759 errbuf := 'Error while populating the fact tables. purging this plan summary. ';
760 msc_phub_util.log(errbuf);
761
762 l_request_id := fnd_request.submit_request('MSC', 'MSCHUBP', null, null, false, l_pi.plan_name, l_plan_run_id);
763 commit;
764
765 msc_phub_util.log('msc_phub_pkg.populate_details_fn, MSCHUBP('||
766 l_pi.plan_name||','||l_plan_run_id||'), l_request_id='||l_request_id);
767 l_return_status := sys_no;
768 end if;
769 end if;
770
771 -- finalize_plan_run
772 finalize_plan_run(l_pi.plan_id, l_plan_run_id, p_include_ods, sys_yes, l_return_status, p_archive_flag);
773 if (l_ods_transfer_id is not null) then
774 drop_staging_partitions(l_ods_transfer_id, l_pi.plan_type, p_include_ods);
775 end if;
776
777 if (l_return_status = sys_yes) then
778 /*
779 -- build item dimension
780 if (l_demantra_flag = sys_yes) then
781 build_items_from_apcc(l_pi.plan_id, p_plan_run_id);
782 end if;
783 */
784
785 -- publish_to_central_apcc
786 if (p_dblink is not null) then
787 publish_to_central_apcc(errbuf, retcode, l_pi.plan_id, l_plan_run_id,
788 p_target_plan_name, p_dblink, p_include_ods, p_archive_flag);
789 end if;
790
791 end if;
792
793 msc_phub_util.log(fnd_message.get_string('MSC','MSC_HUB_POPULATE_ENDS'));
794 return l_plan_run_id;
795
796 exception
797 when others then
798 if (l_plan_run_id > 0) then
799 update msc_plan_runs set
800 archive_flag=decode(archive_flag, 3, 2, archive_flag),
801 last_update_date = sysdate
802 where plan_run_id=l_plan_run_id;
803 commit;
804 end if;
805 retcode := -1;
806 errbuf := 'msc_phub_pkg.populate_details_fn: '||sqlerrm;
807 msc_phub_util.log(errbuf);
808 return l_plan_run_id;
809
810 end populate_details_fn;
811
812 procedure purge_details(p_plan_name varchar2, p_plan_run_id number) is
813 l_purge_list msc_apcc_number_list := list_plan_runs(p_plan_name, p_plan_run_id);
814 cursor c is select column_value plan_run_id from table(l_purge_list);
815 l_last_plan_run_id number := null;
816 l_plan_name varchar2(50);
817 l_plan_id number;
818 l_rowcount number := 0;
819 begin
820 msc_phub_util.log('msc_phub_pkg.purge_details('||p_plan_name||','||p_plan_run_id||')');
821 if (l_purge_list.count = 0) then
822 msc_phub_util.log('msc_phub_pkg.purge_details: (l_purge_list.count = 0)');
823 return;
824 end if;
825
826 for r in c loop
827 manage_partitions(list_fact_tables(null), r.plan_run_id, partition_drop, partition_range);
828
829 update msc_plan_runs set
830 planning_hub_flag = sys_no,
831 last_run_flag = sys_no,
832 archive_flag = sys_no,
833 last_update_date = sysdate,
834 last_updated_by = fnd_global.user_id,
835 last_update_login = fnd_global.login_id,
836 program_id = fnd_global.conc_program_id,
837 program_login_id = fnd_global.conc_login_id,
838 program_application_id = fnd_global.prog_appl_id,
839 request_id = fnd_global.conc_request_id
840 where plan_run_id = r.plan_run_id;
841 commit;
842
843 l_rowcount := l_rowcount + 1;
844 end loop;
845
846 select plan_id, plan_name
847 into l_plan_id, l_plan_name
848 from msc_plan_runs
849 where plan_run_id in (select column_value from table(l_purge_list))
850 and rownum=1;
851
852 select max(plan_run_id)
853 into l_last_plan_run_id
854 from msc_plan_runs
855 where plan_name=l_plan_name
856 and archive_flag=1;
857
858 if (l_last_plan_run_id > 0) then
859 update msc_plan_runs set
860 last_run_flag = 1,
861 last_update_date = sysdate,
862 last_updated_by = fnd_global.user_id,
863 last_update_login = fnd_global.login_id,
864 program_id = fnd_global.conc_program_id,
865 program_login_id = fnd_global.conc_login_id,
866 program_application_id = fnd_global.prog_appl_id,
867 request_id = fnd_global.conc_request_id
868 where plan_run_id=l_last_plan_run_id;
869 else
870 if (l_plan_id > 0) then
871 manage_partitions(item_dim_table, l_plan_id,
872 partition_drop, partition_list);
873 end if;
874 end if;
875
876 msc_phub_custom_pkg.purge_details(p_plan_name, p_plan_run_id);
877 msc_phub_util.log('msc_phub_pkg.purge_details: complete');
878 end purge_details;
879
880 procedure refresh_mvs(p_refresh_mode varchar2)
881 is
882 errbuf varchar2(1000) := '';
883 retcode number := 0;
884 begin
885 refresh_mvs(errbuf, retcode, p_refresh_mode);
886 end refresh_mvs;
887
888 procedure refresh_mvs(errbuf out nocopy varchar2, retcode out nocopy varchar2, p_refresh_mode varchar2) is
889 l_ods_table_list object_names := object_names(
890 'MSC_PHUB_DATES_MV',
891 'MSC_PHUB_CATEGORIES_MV',
892 'MSC_PHUB_ITEM_CATEGORIES_MV',
893 'MSC_PHUB_ORGS_MV',
894 'MSC_PHUB_CUSTOMERS_MV',
895 'MSC_PHUB_SUPPLIERS_MV',
896 'MSC_CURRENCY_CONV_MV',
897 'MSC_PHUB_PROJECTS_MV',
898 'MSC_PHUB_RESOURCES_MV'
899 );
900
901 l_name varchar2(50);
902 p_return_status number;
903 p_error_message varchar2(2000);
904 begin
905 msc_phub_util.log('msc_phub_pkg.refresh_mvs('||p_refresh_mode||')');
906 retcode := 0;
907 errbuf := null;
908
909 --1 ods, 2 pds, 3 both
910 if (p_refresh_mode not in (1,3)) then
911 return;
912 end if;
913
914 -- check_apcc_setup
915 if (check_apcc_setup = 2) then
916 msc_phub_util.log(fnd_message.get_string('MSC', 'MSC_APCC_INVALID_PROFILE'));
917 return;
918 end if;
919
920 check_migrate(errbuf, retcode);
921
922 delete from msc_apcc_config;
923 commit;
924
925 insert into msc_apcc_config (
926 cal_code,
927 period_set_name,
928 reporting_currency,
929 category_set_id1,
930 category_set_id2,
931 category_set_id3,
932 pg_category_set_id,
933 created_by, creation_date,
934 last_update_date, last_updated_by, last_update_login,
935 program_id, program_login_id,
936 program_application_id, request_id)
937 select
938 fnd_profile.value('MSC_HUB_CAL_CODE') cal_code,
939 fnd_profile.value('MSC_HUB_PERIOD_SET_NAME') period_set_name,
940 fnd_profile.value('MSC_HUB_CUR_CODE_RPT') reporting_currency,
941 fnd_profile.value('MSC_HUB_CAT_SET_ID_1') category_set_id1,
942 fnd_profile.value('MSC_HUB_CAT_SET_ID_2') category_set_id2,
943 fnd_profile.value('MSC_HUB_CAT_SET_ID_3') category_set_id3,
944 fnd_profile.value('MSC_APCC_PEGGING_CAT_SET') pg_category_set_id,
945 fnd_global.user_id, sysdate,
946 sysdate, fnd_global.user_id, fnd_global.login_id,
947 fnd_global.conc_program_id, fnd_global.conc_login_id,
948 fnd_global.prog_appl_id, fnd_global.conc_request_id
949 from dual;
950 commit;
951
952 for i in 1..l_ods_table_list.count loop
953 l_name := l_ods_table_list(i);
954 msc_phub_util.log('Refreshing MV : '||l_name||' starts');
955 dbms_mview.refresh(l_name, atomic_refresh=>false);
956 msc_phub_util.log('Refreshing MV : '||l_name||' ends');
957 end loop;
958 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_PHUB_ITEM_CATEGORIES_MV', null, msc_phub_util.apps_schema);
959
960 build_items_from_pds(-1);
961 msc_demantra_pkg.build_custom_hierarchy;
962
963 exception
964 when others then
965 retcode := -1;
966 errbuf := 'msc_phub_pkg.refresh_mvs.exception: '||sqlerrm;
967 msc_phub_util.log(errbuf);
968
969 end refresh_mvs;
970
971 procedure populate_demantra_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
972 p_plan_id number, p_plan_run_id number, p_archive_flag number default -1,
973 p_dblink varchar2 default null,
974 p_include_ods number default sys_no)
975 is
976 l_return_status number := sys_yes;
977 l_plan_run_id number;
978 l_req_id number;
979 begin
980 populate_details(errbuf, retcode, p_plan_id, p_plan_run_id, p_archive_flag, null, p_dblink, p_include_ods, 10);
981 end populate_demantra_details;
982
983 procedure populate_sno_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
984 p_plan_id number,
985 p_plan_run_id number default null,
986 p_archive_flag number default -1,
987 p_scenario_name in varchar2 default null) is
988 begin
989 populate_details(errbuf, retcode, p_plan_id, p_plan_run_id, p_archive_flag,
990 null, null, sys_no, 6, p_scenario_name);
991 end;
992
993 procedure finalize_plan_run(
994 p_plan_id number, p_plan_run_id number, p_include_ods number,
995 p_archive_flag number, p_success number, p_keep_previous number)
996 is
997 l_sr_instance_id number;
998 l_prev_plan_run_id number;
999 l_transfer_id number;
1000 l_plan_type number;
1001 l_plan_name varchar(50);
1002 begin
1003 msc_phub_util.log('msc_phub_pkg.finalize_plan_run('||
1004 p_plan_id||','||p_plan_run_id||','||p_include_ods||','||
1005 p_archive_flag||','||p_success||','||p_keep_previous||')');
1006
1007 if (p_plan_run_id is not null) then
1008 select plan_name, sr_instance_id, plan_type, temp_transfer_id
1009 into l_plan_name, l_sr_instance_id, l_plan_type, l_transfer_id
1010 from msc_plan_runs
1011 where plan_run_id=p_plan_run_id;
1012
1013 if (p_success = sys_yes) then
1014 update msc_plan_runs set
1015 last_run_flag=sys_no,
1016 last_update_date = sysdate,
1017 last_updated_by = fnd_global.user_id,
1018 last_update_login = fnd_global.login_id
1019 where plan_name = l_plan_name
1020 and plan_run_id < p_plan_run_id
1021 and (p_plan_id<>-1 or sr_instance_id=l_sr_instance_id);
1022
1023 update msc_plan_runs set
1024 last_run_flag=sys_yes,
1025 planning_hub_flag=sys_yes,
1026 end_date=sysdate,
1027 archive_flag=p_archive_flag
1028 where plan_id=p_plan_id and plan_run_id=p_plan_run_id;
1029 else
1030 update msc_plan_runs set end_date=sysdate
1031 where plan_id=p_plan_id and plan_run_id=p_plan_run_id;
1032 end if;
1033 commit;
1034 end if;
1035
1036 -- drop_staging_partitions
1037 if (l_transfer_id is not null) then
1038 drop_staging_partitions(l_transfer_id, l_plan_type, p_include_ods);
1039 end if;
1040
1041 -- purge_previous_plan_run
1042 if (p_keep_previous = sys_no) then
1043 purge_previous_plan_run(p_plan_run_id);
1044 end if;
1045
1046 exception
1047 when others then
1048 msc_phub_util.log('msc_phub_pkg.finalize_plan_run.exception: '||sqlerrm);
1049 raise;
1050 end finalize_plan_run;
1051
1052 function create_staging_partitions(p_plan_type number, p_include_ods number) return number
1053 is
1054 l_error number;
1055 l_lock varchar2(200);
1056 l_transfer_id number := null;
1057 e_create_staging_partitions exception;
1058 l_staging_tables object_names := list_staging_tables(p_plan_type, p_include_ods);
1059 begin
1060 msc_phub_util.log('msc_phub_pkg.create_staging_partitions('||p_plan_type||','||p_include_ods||')');
1061
1062 if (l_staging_tables.count > 0) then
1063 -- add partitions synchronuously
1064 dbms_lock.allocate_unique('msc_apcc_upload_s', l_lock);
1065 msc_phub_util.log('msc_phub_pkg.create_staging_partitions: dbms_lock.request');
1066 l_error := dbms_lock.request(l_lock);
1067 if (l_error <> 0) then
1068 msc_phub_util.log('msc_phub_pkg.create_staging_partitions: dbms_lock.request='||l_error);
1069 raise e_create_staging_partitions;
1070 end if;
1071
1072 select msc_apcc_upload_s.nextval into l_transfer_id from dual;
1073 manage_partitions(l_staging_tables, l_transfer_id, partition_add, partition_range);
1074 l_error := dbms_lock.release(l_lock);
1075 end if;
1076 return l_transfer_id;
1077
1078 exception
1079 when others then
1080 msc_phub_util.log('msc_phub_pkg.create_staging_partitions.exception: '||sqlerrm);
1081 l_error := dbms_lock.release(l_lock);
1082 msc_phub_util.log('msc_phub_pkg.create_staging_partitions.exception: dbms_lock.release='||l_error);
1083 rollback;
1084 raise;
1085 end create_staging_partitions;
1086
1087 procedure drop_staging_partitions(p_transfer_id number, p_plan_type number, p_include_ods number)
1088 is
1089 l_staging_tables object_names := list_staging_tables(p_plan_type, p_include_ods);
1090 begin
1091 msc_phub_util.log('msc_phub_pkg.drop_staging_partitions('||p_transfer_id||','||p_plan_type||','||p_include_ods||')');
1092 manage_partitions(l_staging_tables, p_transfer_id, partition_drop, partition_range);
1093 end drop_staging_partitions;
1094
1095 function create_fact_partitions(
1096 p_local_archive_flag number default sys_yes,
1097 p_plan_type number default null)
1098 return number
1099 is
1100 l_error number;
1101 l_lock varchar2(200);
1102 l_fact_tables object_names := null;
1103 l_plan_run_id number;
1104 e_create_fact_partitions exception;
1105 begin
1106 if (p_local_archive_flag = sys_yes) then
1107 l_fact_tables := list_fact_tables(p_plan_type);
1108 else
1109 l_fact_tables := list_fact_tables(null);
1110 end if;
1111
1112 if (l_fact_tables.count > 0) then
1113 -- create fact partitions synchronuously
1114 dbms_lock.allocate_unique('msc_plan_runs_s', l_lock);
1115 msc_phub_util.log('msc_phub_pkg.create_fact_partitions: dbms_lock.request');
1116 l_error := dbms_lock.request(l_lock);
1117 if (l_error <> 0) then
1118 msc_phub_util.log('msc_phub_pkg.create_fact_partitions: dbms_lock.request='||l_error);
1119 raise e_create_fact_partitions;
1120 end if;
1121
1122 select msc_plan_runs_s.nextval into l_plan_run_id from dual;
1123 manage_partitions(l_fact_tables, l_plan_run_id, partition_add, partition_range);
1124 l_error := dbms_lock.release(l_lock);
1125 end if;
1126 return l_plan_run_id;
1127
1128 exception
1129 when others then
1130 l_error := dbms_lock.release(l_lock);
1131 msc_phub_util.log('msc_phub_pkg.create_fact_partitions.exception: dbms_lock.release='||l_error);
1132 raise;
1133 end create_fact_partitions;
1134
1135 function create_plan_run(
1136 p_scenario_name varchar2, p_include_ods number,
1137 p_local_archive_flag number, p_pi plan_info)
1138 return number
1139 is
1140 l_error number;
1141 l_plan_run_id number := null;
1142 l_plan_run_name varchar2(100);
1143 l_plan_version number;
1144 l_transfer_id number := null;
1145 e_create_plan_run exception;
1146 begin
1147 msc_phub_util.log('msc_phub_pkg.create_plan_run('||
1148 p_scenario_name||','||p_include_ods||','||p_local_archive_flag||','||
1149 p_pi.plan_id||','||p_pi.plan_type||')');
1150
1151 if (p_pi.plan_name is null) then
1152 return null;
1153 end if;
1154
1155 -- check local_archive_flag
1156 begin
1157 select 1 into l_error
1158 from msc_plan_runs
1159 where plan_name=p_pi.plan_name
1160 and nvl(local_archive_flag,1) <> nvl(p_local_archive_flag,1)
1161 and rownum=1;
1162 exception
1163 when others then null;
1164 end;
1165
1166 if (l_error = 1) then
1167 msc_phub_util.log('msc_phub_pkg.create_plan_run: p_local_archive_flag mismatch');
1168 raise e_create_plan_run;
1169 end if;
1170
1171 l_plan_run_id := create_fact_partitions(p_local_archive_flag, p_pi.plan_type);
1172 if (p_local_archive_flag = sys_yes) then
1173 l_transfer_id := create_staging_partitions(p_pi.plan_type, p_include_ods);
1174 end if;
1175
1176 if (l_plan_run_id is not null) then
1177 select nvl(max(plan_run_version), -1) + 1 into l_plan_version from msc_plan_runs where plan_name=p_pi.plan_name;
1178
1179 l_plan_run_name := p_pi.plan_name||
1180 to_char(p_pi.plan_completion_date, ' MM/DD')||
1181 '('||l_plan_version||')'||
1182 (case when p_scenario_name is not null then ' ['||p_scenario_name||']' end);
1183
1184 insert into msc_plan_runs (
1185 plan_id, plan_run_id, plan_run_name, sr_instance_id, organization_id,
1186 plan_name, plan_type, plan_description, plan_run_version, scenario_name,
1187 plan_start_date, plan_cutoff_date, plan_completion_date, publisher,
1188 start_date, end_date, last_run_flag,
1189 planning_hub_flag, archive_flag, local_archive_flag, temp_transfer_id,
1190 created_by, creation_date, last_update_date, last_updated_by, last_update_login,
1191 program_id, program_login_id, program_application_id, request_id
1192 )
1193 values (
1194 p_pi.plan_id, l_plan_run_id, l_plan_run_name,
1195 p_pi.sr_instance_id, p_pi.organization_id,
1196 p_pi.plan_name, p_pi.plan_type, p_pi.plan_description,
1197 l_plan_version, p_scenario_name,
1198 p_pi.plan_start_date, p_pi.plan_cutoff_date,
1199 p_pi.plan_completion_date, p_pi.publisher,
1200 sysdate, null, sys_no, sys_no, 0, p_local_archive_flag, l_transfer_id,
1201 fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.user_id,
1202 fnd_global.conc_program_id, fnd_global.conc_login_id, fnd_global.prog_appl_id, fnd_global.conc_request_id
1203 );
1204 commit;
1205
1206 msc_phub_util.log('msc_phub_pkg.create_plan_run('||p_pi.plan_id||', '||l_plan_run_id||
1207 ', '''||l_plan_run_name||''')');
1208 end if;
1209 return l_plan_run_id;
1210
1211 exception
1212 when others then
1213 msc_phub_util.log('msc_phub_pkg.create_plan_run.exception: '||sqlerrm);
1214 rollback;
1215 raise;
1216
1217 end create_plan_run;
1218
1219 procedure populate_demantra_ods(errbuf out nocopy varchar2, retcode out nocopy varchar2)
1220 is
1221 begin
1222 populate_details(errbuf, retcode,
1223 0, null, sys_yes,
1224 null, null, sys_yes, 10, null, 'Export OBI Data');
1225 end populate_demantra_ods;
1226
1227 procedure populate_each(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1228 p_package varchar2, p_plan_id number, p_plan_run_id number)
1229 is
1230 l_sql varchar2(200);
1231 begin
1232 msc_phub_util.log('msc_phub_pkg.populate_each: ('||p_package||','||p_plan_id||','||p_plan_run_id||')');
1233 l_sql := 'begin '||p_package||'.populate_details(:errbuf, :retcode, :p_plan_id, :p_plan_run_id); end;';
1234 execute immediate l_sql using out errbuf, out retcode, p_plan_id, p_plan_run_id;
1235 exception
1236 when others then
1237 if (retcode = 0) then
1238 retcode := -1;
1239 errbuf := 'msc_phub_pkg.populate_each: '||sqlerrm;
1240 msc_phub_util.log(errbuf);
1241 end if;
1242 raise;
1243 end populate_each;
1244
1245 procedure build_null_items(p_plan_id number)
1246 is
1247 l_plan_type number := null;
1248 begin
1249 msc_phub_util.log('msc_phub_pkg.build_null_items('||p_plan_id||')');
1250
1251 begin
1252 select plan_type into l_plan_type
1253 from msc_plan_runs
1254 where plan_id=p_plan_id and rownum=1;
1255 exception
1256 when others then null;
1257 end;
1258
1259 insert into msc_apcc_item_d (
1260 plan_id,
1261 sr_instance_id,
1262 organization_id,
1263 inventory_item_id,
1264 sr_category_id1,
1265 sr_category_id2,
1266 sr_category_id3,
1267 pegging_sr_category_id,
1268 latest_item_id,
1269 vmi_flag,
1270 item_name,
1271 description,
1272 abc_class,
1273 product_family_id,
1274 average_daily_demand,
1275 average_discount,
1276 base_item_id,
1277 build_in_wip_flag,
1278 buyer_name,
1279 fixed_lead_time,
1280 list_price,
1281 max_minmax_quantity,
1282 min_minmax_quantity,
1283 minimum_order_quantity,
1284 mrp_planning_code,
1285 planner_code,
1286 planning_make_buy_code,
1287 postprocessing_lead_time,
1288 preprocessing_lead_time,
1289 purchasing_enabled_flag,
1290 repetitive_type,
1291 safety_stock_code,
1292 standard_cost,
1293 unit_volume,
1294 unit_weight,
1295 uom_code,
1296 variable_lead_time,
1297 volume_uom,
1298 weight_uom,
1299 created_by, creation_date,
1300 last_update_date, last_updated_by, last_update_login,
1301 program_id, program_login_id,
1302 program_application_id, request_id
1303 )
1304 select
1305 p.plan_id,
1306 b.sr_instance_id,
1307 to_number(-23453) organization_id,
1308 -b.sr_category_id inventory_item_id,
1309 nvl(c1.sr_category_id1, -23453) sr_category_id1,
1310 nvl(c2.sr_category_id2, -23453) sr_category_id2,
1311 nvl(c3.sr_category_id3, -23453) sr_category_id3,
1312 nvl(c4.pegging_sr_category_id, -23453) pegging_sr_category_id,
1313 to_number(-23453) latest_item_id,
1314 null vmi_flag,
1315 null item_name,
1316 null description,
1317 to_number(-23453) abc_class,
1318 to_number(-23453) product_family_id,
1319 null average_daily_demand,
1320 null average_discount,
1321 null base_item_id,
1322 null build_in_wip_flag,
1323 null buyer_name,
1324 null fixed_lead_time,
1325 null list_price,
1326 null max_minmax_quantity,
1327 null min_minmax_quantity,
1328 null minimum_order_quantity,
1329 null mrp_planning_code,
1330 fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED') planner_code,
1331 null planning_make_buy_code,
1332 null postprocessing_lead_time,
1333 null preprocessing_lead_time,
1334 null purchasing_enabled_flag,
1335 null repetitive_type,
1336 null safety_stock_code,
1337 null standard_cost,
1338 null unit_volume,
1339 null unit_weight,
1340 null uom_code,
1341 null variable_lead_time,
1342 null volume_uom,
1343 null weight_uom,
1344 fnd_global.user_id, sysdate,
1345 sysdate, fnd_global.user_id, fnd_global.login_id,
1346 fnd_global.conc_program_id, fnd_global.conc_login_id,
1347 fnd_global.prog_appl_id, fnd_global.conc_request_id
1348 from
1349 (select distinct sr_instance_id, sr_category_id1 sr_category_id
1350 from msc_apcc_item_d
1351 where plan_id=p_plan_id and sr_category_id1>=0
1352 union
1353 select distinct sr_instance_id, sr_category_id2 sr_category_id
1354 from msc_apcc_item_d
1355 where plan_id=p_plan_id and sr_category_id2>=0
1356 union
1357 select distinct sr_instance_id, sr_category_id3 sr_category_id
1358 from msc_apcc_item_d
1359 where plan_id=p_plan_id and sr_category_id3>=0
1360 union
1361 select distinct sr_instance_id, pegging_sr_category_id sr_category_id
1362 from msc_apcc_item_d
1363 where plan_id=p_plan_id and pegging_sr_category_id>=0
1364 ) b,
1365 (select distinct sr_instance_id, sr_category_id1
1366 from msc_apcc_item_d
1367 where plan_id=p_plan_id
1368 ) c1,
1369 (select distinct sr_instance_id, sr_category_id2
1370 from msc_apcc_item_d
1371 where plan_id=p_plan_id
1372 ) c2,
1373 (select distinct sr_instance_id, sr_category_id3
1374 from msc_apcc_item_d
1375 where plan_id=p_plan_id
1376 ) c3,
1377 (select distinct sr_instance_id, pegging_sr_category_id
1378 from msc_apcc_item_d
1379 where plan_id=p_plan_id
1380 ) c4,
1381 msc_plans p
1382 where b.sr_category_id=c1.sr_category_id1(+)
1383 and b.sr_category_id=c2.sr_category_id2(+)
1384 and b.sr_category_id=c3.sr_category_id3(+)
1385 and b.sr_category_id=c4.pegging_sr_category_id(+)
1386 and b.sr_instance_id=c1.sr_instance_id(+)
1387 and b.sr_instance_id=c2.sr_instance_id(+)
1388 and b.sr_instance_id=c3.sr_instance_id(+)
1389 and b.sr_instance_id=c4.sr_instance_id(+)
1390 and p.plan_id=p_plan_id
1391 and l_plan_type in (4)
1392 union all
1393 select
1394 p_plan_id,
1395 to_number(-23453) sr_instance_id,
1396 to_number(-23453) organization_id,
1397 to_number(-23453) inventory_item_id,
1398 to_number(-23453) sr_category_id1,
1399 to_number(-23453) sr_category_id2,
1400 to_number(-23453) sr_category_id3,
1401 to_number(-23453) pegging_sr_category_id,
1402 to_number(-23453) latest_item_id,
1403 null vmi_flag,
1404 null item_name,
1405 null description,
1406 to_number(-23453) abc_class,
1407 to_number(-23453) product_family_id,
1408 null average_daily_demand,
1409 null average_discount,
1410 null base_item_id,
1411 null build_in_wip_flag,
1412 null buyer_name,
1413 null fixed_lead_time,
1414 null list_price,
1415 null max_minmax_quantity,
1416 null min_minmax_quantity,
1417 null minimum_order_quantity,
1418 null mrp_planning_code,
1419 fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED') planner_code,
1420 null planning_make_buy_code,
1421 null postprocessing_lead_time,
1422 null preprocessing_lead_time,
1423 null purchasing_enabled_flag,
1424 null repetitive_type,
1425 null safety_stock_code,
1426 null standard_cost,
1427 null unit_volume,
1428 null unit_weight,
1429 null uom_code,
1430 null variable_lead_time,
1431 null volume_uom,
1432 null weight_uom,
1433 fnd_global.user_id, sysdate,
1434 sysdate, fnd_global.user_id, fnd_global.login_id,
1435 fnd_global.conc_program_id, fnd_global.conc_login_id,
1436 fnd_global.prog_appl_id, fnd_global.conc_request_id
1437 from dual;
1438
1439 msc_phub_util.log('msc_phub_pkg.build_null_items: insert='||sql%rowcount);
1440 commit;
1441
1442 exception
1443 when others then
1444 msc_phub_util.log('msc_phub_pkg.build_null_items.exception:'||sqlerrm);
1445 raise;
1446 end build_null_items;
1447
1448 procedure build_items_from_pds(p_plan_id number)
1449 is
1450 begin
1451 msc_phub_util.log('msc_phub_pkg.build_items_from_pds('||p_plan_id||')');
1452 manage_partitions(item_dim_table, p_plan_id, partition_init, partition_list);
1453 insert into msc_apcc_item_d (
1454 plan_id,
1455 sr_instance_id,
1456 organization_id,
1457 inventory_item_id,
1458 sr_category_id1,
1459 sr_category_id2,
1460 sr_category_id3,
1461 pegging_sr_category_id,
1462 latest_item_id,
1463 vmi_flag,
1464 item_name,
1465 description,
1466 abc_class,
1467 product_family_id,
1468 average_daily_demand,
1469 average_discount,
1470 base_item_id,
1471 build_in_wip_flag,
1472 buyer_name,
1473 fixed_lead_time,
1474 list_price,
1475 max_minmax_quantity,
1476 min_minmax_quantity,
1477 minimum_order_quantity,
1478 mrp_planning_code,
1479 planner_code,
1480 planning_make_buy_code,
1481 postprocessing_lead_time,
1482 preprocessing_lead_time,
1483 purchasing_enabled_flag,
1484 repetitive_type,
1485 safety_stock_code,
1486 standard_cost,
1487 unit_volume,
1488 unit_weight,
1489 uom_code,
1490 variable_lead_time,
1491 volume_uom,
1492 weight_uom,
1493 created_by, creation_date,
1494 last_update_date, last_updated_by, last_update_login,
1495 program_id, program_login_id,
1496 program_application_id, request_id
1497 )
1498 select
1499 i.plan_id,
1500 i.sr_instance_id,
1501 i.organization_id,
1502 i.inventory_item_id,
1503 nvl(ic1.sr_category_id, -23453) sr_category_id1,
1504 nvl(ic2.sr_category_id, -23453) sr_category_id2,
1505 nvl(ic3.sr_category_id, -23453) sr_category_id3,
1506 nvl(ick.sr_category_id, -23453) pegging_sr_category_id,
1507 nvl(s.highest_item_id, i.inventory_item_id) latest_item_id,
1508 mis.vmi_flag,
1509 i.item_name,
1510 i.description,
1511 i.abc_class,
1512 i.product_family_id,
1513 i.average_daily_demand,
1514 i.average_discount,
1515 i.base_item_id,
1516 i.build_in_wip_flag,
1517 i.buyer_name,
1518 i.fixed_lead_time,
1519 i.list_price,
1520 i.max_minmax_quantity,
1521 i.min_minmax_quantity,
1522 i.minimum_order_quantity,
1523 i.mrp_planning_code,
1524 /* bug 9919442 - if no planner code set to 'Unassigned' */
1525 /* i.planner_code, */
1526 nvl(i.planner_code, fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED')) planner_code,
1527 i.planning_make_buy_code,
1528 i.postprocessing_lead_time,
1529 i.preprocessing_lead_time,
1530 i.purchasing_enabled_flag,
1531 i.repetitive_type,
1532 i.safety_stock_code,
1533 i.standard_cost,
1534 i.unit_volume,
1535 i.unit_weight,
1536 i.uom_code,
1537 i.variable_lead_time,
1538 i.volume_uom,
1539 i.weight_uom,
1540 fnd_global.user_id, sysdate,
1541 sysdate, fnd_global.user_id, fnd_global.login_id,
1542 fnd_global.conc_program_id, fnd_global.conc_login_id,
1543 fnd_global.prog_appl_id, fnd_global.conc_request_id
1544 from msc_system_items i,
1545 (select sr_instance_id, organization_id, inventory_item_id, sr_category_id
1546 from msc_phub_item_categories_mv
1547 where category_set_id=fnd_profile.value('MSC_HUB_CAT_SET_ID_1')) ic1,
1548 (select sr_instance_id, organization_id, inventory_item_id, sr_category_id
1549 from msc_phub_item_categories_mv
1550 where category_set_id=fnd_profile.value('MSC_HUB_CAT_SET_ID_2')) ic2,
1551 (select sr_instance_id, organization_id, inventory_item_id, sr_category_id
1552 from msc_phub_item_categories_mv
1553 where category_set_id=fnd_profile.value('MSC_HUB_CAT_SET_ID_3')) ic3,
1554 (select sr_instance_id, organization_id, inventory_item_id, sr_category_id
1555 from msc_phub_item_categories_mv
1556 where nvl(fnd_profile.value('MSC_APCC_END_ITEM_ENABLED'), 2)<2
1557 and category_set_id=fnd_profile.value('MSC_APCC_PEGGING_CAT_SET')) ick,
1558 (select distinct lower_item_id,
1559 first_value(highest_item_id) over(partition by lower_item_id) highest_item_id
1560 from
1561 (select lower_item_id, highest_item_id
1562 from msc_item_substitutes
1563 where plan_id=p_plan_id
1564 and relationship_type=8
1565 and inferred_flag=2
1566 and forward_rule=1
1567 and sysdate between effective_date and nvl(disable_date, sysdate)
1568 union
1569 select highest_item_id, highest_item_id
1570 from msc_item_substitutes
1571 where plan_id=p_plan_id
1572 and relationship_type=8
1573 and inferred_flag=2
1574 and forward_rule=1
1575 and sysdate between effective_date and nvl(disable_date, sysdate)
1576 )
1577 ) s,
1578 (select distinct sr_instance_id, organization_id, inventory_item_id, to_number(1) vmi_flag
1579 from msc_item_suppliers
1580 where plan_id=p_plan_id
1581 and vmi_flag=1
1582 ) mis
1583 where i.plan_id=p_plan_id
1584 and i.organization_id>0
1585 and i.sr_instance_id=ic1.sr_instance_id(+)
1586 and i.organization_id=ic1.organization_id(+)
1587 and i.inventory_item_id=ic1.inventory_item_id(+)
1588 and i.sr_instance_id=ic2.sr_instance_id(+)
1589 and i.organization_id=ic2.organization_id(+)
1590 and i.inventory_item_id=ic2.inventory_item_id(+)
1591 and i.sr_instance_id=ic3.sr_instance_id(+)
1592 and i.organization_id=ic3.organization_id(+)
1593 and i.inventory_item_id=ic3.inventory_item_id(+)
1594 and i.sr_instance_id=ick.sr_instance_id(+)
1595 and i.organization_id=ick.organization_id(+)
1596 and i.inventory_item_id=ick.inventory_item_id(+)
1597 and i.inventory_item_id=s.lower_item_id(+)
1598 and nvl(i.new_plan_id,-1)=-1 -- quick workaround to avoid ORA-00001, still doesn't fix RP
1599 and i.sr_instance_id = mis.sr_instance_id(+)
1600 and i.organization_id = mis.organization_id(+)
1601 and i.inventory_item_id = mis.inventory_item_id(+);
1602
1603 msc_phub_util.log('msc_phub_pkg.build_items_from_pds: insert='||sql%rowcount);
1604 commit;
1605
1606 build_null_items(p_plan_id);
1607 msc_phub_util.gather_table_stats(msc_phub_util.stats_fact, 'MSC_APCC_ITEM_D', p_plan_id);
1608 msc_phub_util.log('msc_phub_pkg.build_items_from_pds: complete');
1609
1610 exception
1611 when others then
1612 msc_phub_util.log('msc_phub_pkg.build_items_from_pds.exception:'||sqlerrm);
1613 raise;
1614 end build_items_from_pds;
1615
1616 procedure gather_items(p_query_id number, p_fact_type number, p_plan_id number, p_plan_run_id number)
1617 is
1618 l_table varchar2(30);
1619 l_is_plan boolean := (p_fact_type <> 4);
1620 l_meta_info msc_apcc_fact_type_table := meta_info;
1621 l_item_columns varchar2(100);
1622 l_sql varchar2(1000);
1623 begin
1624 l_table := upper('msc_'||l_meta_info(p_fact_type).entity_name||'_f');
1625
1626 if (l_meta_info(p_fact_type).item_dim in (1,3)) then
1627 l_item_columns := 'owning_inst_id, owning_org_id';
1628 else
1629 l_item_columns := 'sr_instance_id, organization_id';
1630 end if;
1631
1632 l_sql :=
1633 ' insert into msc_hub_query ('||
1634 ' query_id, number3, number4, number5,'||
1635 ' created_by, creation_date, last_updated_by, last_update_date'||
1636 ' )'||
1637 ' select distinct'||
1638 ' :p_query_id, '||l_item_columns||', inventory_item_id,'||
1639 ' fnd_global.user_id, sysdate, fnd_global.user_id, sysdate'||
1640 ' from '||l_table;
1641
1642 if (l_is_plan) then
1643 l_sql := l_sql||' where plan_id='||p_plan_id;
1644 if (p_plan_run_id is not null) then
1645 l_sql := l_sql||' and plan_run_id='||p_plan_run_id;
1646 end if;
1647 l_sql := l_sql||' and aggr_type=0';
1648 end if;
1649
1650 execute immediate l_sql using p_query_id;
1651 commit;
1652
1653 exception
1654 when others then
1655 msc_phub_util.log('msc_phub_pkg.gather_items.exception:'||sqlerrm);
1656 raise;
1657 end gather_items;
1658
1659 procedure build_items_from_apcc(p_plan_id number, p_plan_run_id number)
1660 is
1661 l_qid_plan_item number;
1662 cursor c is select fact_type, item_dim from table(meta_info) where item_dim in (1,2) and fact_type<>4;
1663 begin
1664 msc_phub_util.log('msc_phub_pkg.build_items_from_apcc('||
1665 p_plan_id||','||p_plan_run_id||')');
1666
1667 if (nvl(p_plan_id, -1) <= 0) then
1668 return;
1669 end if;
1670
1671 select msc_hub_query_s.nextval into l_qid_plan_item from dual;
1672 for r in c loop
1673 gather_items(l_qid_plan_item, r.fact_type, p_plan_id, p_plan_run_id);
1674 end loop;
1675 --msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: l_qid_plan_item='||l_qid_plan_item);
1676
1677 -- prepare partition
1678 manage_partitions(item_dim_table, p_plan_id, partition_init, partition_list);
1679
1680 -- delete existing items
1681 delete from msc_apcc_item_d d
1682 where d.plan_id=p_plan_id
1683 and exists (
1684 select 1 from msc_hub_query q
1685 where q.query_id=l_qid_plan_item
1686 and q.number3=d.sr_instance_id
1687 and q.number4=d.organization_id
1688 and q.number5=d.inventory_item_id);
1689 msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: delete='||sql%rowcount);
1690 commit;
1691
1692 -- delete existing items
1693 delete from msc_apcc_item_d d
1694 where d.plan_id=p_plan_id
1695 and d.inventory_item_id<0;
1696 msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: delete='||sql%rowcount);
1697 commit;
1698
1699 -- insert items
1700 insert into msc_apcc_item_d (
1701 plan_id,
1702 sr_instance_id,
1703 organization_id,
1704 inventory_item_id,
1705 sr_category_id1,
1706 sr_category_id2,
1707 sr_category_id3,
1708 pegging_sr_category_id,
1709 latest_item_id,
1710 vmi_flag,
1711 item_name,
1712 description,
1713 abc_class,
1714 product_family_id,
1715 average_daily_demand,
1716 average_discount,
1717 base_item_id,
1718 build_in_wip_flag,
1719 buyer_name,
1720 fixed_lead_time,
1721 list_price,
1722 max_minmax_quantity,
1723 min_minmax_quantity,
1724 minimum_order_quantity,
1725 mrp_planning_code,
1726 planner_code,
1727 planning_make_buy_code,
1728 postprocessing_lead_time,
1729 preprocessing_lead_time,
1730 purchasing_enabled_flag,
1731 repetitive_type,
1732 safety_stock_code,
1733 standard_cost,
1734 unit_volume,
1735 unit_weight,
1736 uom_code,
1737 variable_lead_time,
1738 volume_uom,
1739 weight_uom,
1740 created_by, creation_date,
1741 last_update_date, last_updated_by, last_update_login,
1742 program_id, program_login_id,
1743 program_application_id, request_id
1744 )
1745 select
1746 p_plan_id,
1747 d.sr_instance_id,
1748 d.organization_id,
1749 d.inventory_item_id,
1750 d.sr_category_id1,
1751 d.sr_category_id2,
1752 d.sr_category_id3,
1753 d.pegging_sr_category_id,
1754 to_number(-23453) latest_item_id,
1755 d.vmi_flag,
1756 d.item_name,
1757 d.description,
1758 d.abc_class,
1759 d.product_family_id,
1760 d.average_daily_demand,
1761 d.average_discount,
1762 d.base_item_id,
1763 d.build_in_wip_flag,
1764 d.buyer_name,
1765 d.fixed_lead_time,
1766 d.list_price,
1767 d.max_minmax_quantity,
1768 d.min_minmax_quantity,
1769 d.minimum_order_quantity,
1770 d.mrp_planning_code,
1771 /* bug 9919442 - if no planner code set to 'Unassigned' */
1772 /* d.planner_code, */
1773 nvl(d.planner_code, fnd_message.get_string('MSC','MSC_HUB_UNASSIGNED')) planner_code,
1774 d.planning_make_buy_code,
1775 d.postprocessing_lead_time,
1776 d.preprocessing_lead_time,
1777 d.purchasing_enabled_flag,
1778 d.repetitive_type,
1779 d.safety_stock_code,
1780 d.standard_cost,
1781 d.unit_volume,
1782 d.unit_weight,
1783 d.uom_code,
1784 d.variable_lead_time,
1785 d.volume_uom,
1786 d.weight_uom,
1787 fnd_global.user_id, sysdate,
1788 sysdate, fnd_global.user_id, fnd_global.login_id,
1789 fnd_global.conc_program_id, fnd_global.conc_login_id,
1790 fnd_global.prog_appl_id, fnd_global.conc_request_id
1791 from msc_apcc_item_d d,
1792 (select distinct
1793 number3 sr_instance_id,
1794 number4 organization_id,
1795 number5 inventory_item_id
1796 from msc_hub_query where query_id=l_qid_plan_item
1797 and not (number3=-23453 and number4=-23453 and number5=-23453)) q
1798 where d.sr_instance_id=q.sr_instance_id
1799 and d.organization_id=q.organization_id
1800 and d.inventory_item_id=q.inventory_item_id
1801 and d.plan_id=-1;
1802 msc_phub_util.log('msc_phub_pkg.build_items_from_apcc: insert='||sql%rowcount);
1803 commit;
1804
1805 build_null_items(p_plan_id);
1806
1807 exception
1808 when others then
1809 msc_phub_util.log('msc_phub_pkg.build_items_from_apcc.exception:'||sqlerrm);
1810 raise;
1811 end build_items_from_apcc;
1812
1813 procedure populate_ods_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1814 p_sr_instance_id number, p_refresh_mode number, p_param1 varchar2 default null) is
1815
1816 con_ods_plan_id constant number := -1;
1817 l_plan_run_id number;
1818 l_lcid number;
1819 l_return_status number := sys_no;
1820 l_days_forward number := nvl(fnd_profile.value('MSC_APCC_COLL_HORIZON_DAYS'),30);
1821 l_days_back number := nvl(fnd_profile.value('MSC_APCC_COLL_HORIZON_DAYS_BACK'),30);
1822 l_instance_code varchar2(3);
1823 l_plan_run_name varchar2(100);
1824 l_pi plan_info;
1825 l_transfer_id number := null;
1826 begin
1827 --set the conc program ret values to success
1828 retcode := 0;
1829 errbuf := null;
1830
1831 msc_phub_util.log(fnd_message.get_string('MSC','MSC_HUB_POPULATE_STARTS'));
1832
1833 msc_phub_util.log('msc_phub_pkg.populate_ods_details('||
1834 p_sr_instance_id||','||p_refresh_mode||')');
1835
1836 -- prepare plan info
1837 select compile_designator, description
1838 into l_pi.plan_name, l_pi.plan_description
1839 from msc_plans
1840 where plan_id=-1;
1841
1842 select organization_id
1843 into l_pi.organization_id
1844 from msc_instance_orgs
1845 where rownum=1 and sr_instance_id=p_sr_instance_id;
1846
1847 l_pi.plan_id := -1;
1848 l_pi.plan_type := -1;
1849 l_pi.sr_instance_id := p_sr_instance_id;
1850 l_pi.plan_start_date := trunc(sysdate) - l_days_back;
1851 l_pi.plan_cutoff_date := trunc(sysdate) + l_days_forward;
1852
1853 --get last collection id
1854 select lcid
1855 into l_lcid
1856 from msc_apps_instances
1857 where instance_id=p_sr_instance_id;
1858
1859 --populate msc_plan_buckets one row for days between sysdate and l_horizon_date
1860 msc_phub_util.log('Populating msc_plan_buckets');
1861 delete from msc_plan_buckets
1862 where plan_id=-1;
1863 commit;
1864
1865 for v_counter in 1 .. l_days_back + l_days_forward + 1 loop
1866 insert into msc_plan_buckets (
1867 plan_id,
1868 organization_id,
1869 sr_instance_id,
1870 bucket_index,
1871 curr_flag,
1872 bkt_start_date,
1873 bkt_end_date,
1874 days_in_bkt,
1875 bucket_type,
1876 last_update_date,
1877 last_updated_by,
1878 creation_date,
1879 created_by
1880 )
1881 values (
1882 con_ods_plan_id,
1883 l_pi.organization_id,
1884 p_sr_instance_id,
1885 v_counter,
1886 1, --curr_flag
1887 l_pi.plan_start_date + v_counter - 1,
1888 l_pi.plan_start_date + v_counter - 1/86400,
1889 1, --days_in_bkt
1890 1, --bucket_type
1891 sysdate,
1892 fnd_global.user_id,
1893 sysdate,
1894 fnd_global.user_id
1895 );
1896 end loop;
1897 commit;
1898
1899 --populate msc_plan_organizations one row for each org from msc_instance_orgs where sr_instance_id = p_sr_instance_id;
1900 msc_phub_util.log('Populating msc_plan_organizations');
1901 delete from msc_plan_organizations
1902 where plan_id=-1;
1903 commit;
1904
1905 insert into msc_plan_organizations(
1906 plan_id,
1907 organization_id,
1908 sr_instance_id,
1909 organization_code,
1910 net_wip,
1911 net_reservations,
1912 net_purchasing,
1913 plan_safety_stock,
1914 plan_level,
1915 last_update_date,
1916 last_updated_by,
1917 creation_date,
1918 created_by,
1919 last_update_login)
1920 select con_ods_plan_id,
1921 mio.organization_id,
1922 mio.sr_instance_id,
1923 mtp.organization_code,
1924 2, --net_wip
1925 2, --net_reservations
1926 2, --net_purchasing
1927 2, --plan_safety_stock
1928 2, --plan_level
1929 sysdate,
1930 fnd_global.user_id,
1931 sysdate,
1932 fnd_global.user_id,
1933 fnd_global.login_id
1934 from msc_instance_orgs mio,
1935 msc_trading_partners mtp
1936 where mio.sr_instance_id = p_sr_instance_id
1937 and mio.sr_instance_id = mtp.sr_instance_id
1938 and mio.organization_id = mtp.sr_tp_id
1939 and mtp.partner_type = 3;
1940 commit;
1941
1942 --check for existing collections data
1943 begin
1944 select plan_run_id, plan_run_name
1945 into l_plan_run_id, l_plan_run_name
1946 from msc_plan_runs
1947 where plan_id = con_ods_plan_id
1948 and sr_instance_id=p_sr_instance_id
1949 and archive_flag=1;
1950
1951 msc_phub_util.log('msc_phub_pkg.populate_ods_details('||l_plan_run_id||
1952 ', '''||l_plan_run_name||''')');
1953
1954 if (p_refresh_mode = 1) then
1955 manage_partitions(list_fact_tables(-1), l_plan_run_id, partition_truncate, partition_range);
1956 end if;
1957
1958 -- prepare staging tables
1959 l_transfer_id := create_staging_partitions(l_pi.plan_type, sys_no);
1960
1961 -- update msc_plan_runs
1962 update msc_plan_runs set
1963 refresh_mode = p_refresh_mode,
1964 plan_type = l_pi.plan_type,
1965 sr_instance_id = p_sr_instance_id,
1966 organization_id = l_pi.organization_id,
1967 plan_start_date = l_pi.plan_start_date,
1968 plan_cutoff_date = l_pi.plan_cutoff_date,
1969 temp_transfer_id = l_transfer_id,
1970 plan_completion_date = sysdate,
1971 plan_run_name = plan_name||
1972 to_char(plan_completion_date, ' MM/DD')||
1973 '('||plan_run_version||')'||
1974 (case when scenario_name is not null then ' ['||scenario_name||']' end),
1975 last_update_date = sysdate,
1976 last_updated_by = fnd_global.user_id,
1977 last_update_login = fnd_global.login_id,
1978 program_id = fnd_global.conc_program_id,
1979 program_login_id = fnd_global.conc_login_id,
1980 program_application_id = fnd_global.prog_appl_id,
1981 request_id = fnd_global.conc_request_id
1982 where plan_run_id = l_plan_run_id;
1983 commit;
1984
1985 exception
1986 when no_data_found then
1987 msc_phub_util.log('msc_phub_pkg.populate_ods_details.exception: l_plan_run_id is null');
1988
1989 select instance_code into l_instance_code
1990 from msc_apps_instances where instance_id=p_sr_instance_id;
1991
1992 l_plan_run_id := create_plan_run(l_instance_code, sys_no, sys_yes, l_pi);
1993 end;
1994
1995 --call populate_facts api to populate fact tables
1996 l_return_status := populate_facts(-1, l_plan_run_id, -1);
1997 if (l_return_status = sys_no) then
1998 retcode := -1;
1999 errbuf := 'Error while populating the fact tables. purging this plan summary. ';
2000 msc_phub_util.log(errbuf);
2001 else
2002 update msc_plan_runs set lcid = l_lcid
2003 where plan_run_id=l_plan_run_id;
2004 commit;
2005 end if;
2006 finalize_plan_run(-1, l_plan_run_id, sys_no, sys_yes, l_return_status, sys_yes);
2007
2008 msc_phub_util.log(fnd_message.get_string('MSC','MSC_HUB_POPULATE_ENDS'));
2009 end populate_ods_details;
2010
2011 procedure clean_up_customer_keys
2012 is
2013 l_n number := 0;
2014 begin
2015 msc_phub_util.log('msc_phub_pkg.clean_up_customer_keys');
2016
2017 select count(*) into l_n from msc_demands_f where customer_id>0 and region_id>0 and rownum=1;
2018 if (l_n > 0) then
2019 msc_phub_util.log('update msc_demands_f set region_id=-23453 where customer_id>0');
2020 update msc_demands_f set region_id=-23453 where customer_id>0;
2021 commit;
2022 end if;
2023
2024 select count(*) into l_n from msc_demands_cum_f where customer_id>0 and region_id>0 and rownum=1;
2025 if (l_n > 0) then
2026 msc_phub_util.log('update msc_demands_cum_f set region_id=-23453 where customer_id>0');
2027 update msc_demands_cum_f set region_id=-23453 where customer_id>0;
2028 commit;
2029 end if;
2030
2031 select count(*) into l_n from msc_demantra_f where customer_id>0 and region_id>0 and rownum=1;
2032 if (l_n > 0) then
2033 msc_phub_util.log('update msc_demantra_f set region_id=-23453 where customer_id>0');
2034 update msc_demantra_f set region_id=-23453 where customer_id>0;
2035 commit;
2036 end if;
2037
2038 select count(*) into l_n from msc_demantra_ods_f where customer_id>0 and region_id>0 and rownum=1;
2039 if (l_n > 0) then
2040 msc_phub_util.log('update msc_demantra_ods_f set region_id=-23453 where customer_id>0');
2041 update msc_demantra_ods_f set region_id=-23453 where customer_id>0;
2042 commit;
2043 end if;
2044
2045 select count(*) into l_n from msc_costs_f where customer_id>0 and customer_region_id>0 and rownum=1;
2046 if (l_n > 0) then
2047 msc_phub_util.log('update msc_costs_f set customer_region_id=-23453 where customer_id>0');
2048 update msc_costs_f set customer_region_id=-23453 where customer_id>0;
2049 commit;
2050 end if;
2051
2052 select count(*) into l_n from msc_exceptions_f where customer_id>0 and customer_region_id>0 and rownum=1;
2053 if (l_n > 0) then
2054 msc_phub_util.log('update msc_exceptions_f set customer_region_id=-23453 where customer_id>0');
2055 update msc_exceptions_f set customer_region_id=-23453 where customer_id>0;
2056 commit;
2057 end if;
2058
2059 exception
2060 when others then
2061 msc_phub_util.log('msc_phub_pkg.clean_up_customer_keys.exception:'||sqlerrm);
2062 raise;
2063 end clean_up_customer_keys;
2064
2065 procedure clean_up_fact_partitions
2066 is
2067 l_tables object_names := null;
2068 l_applsys_schema varchar2(100) := null;
2069 l_msc_schema varchar2(100) := null;
2070 l_ddl varchar2(300) := null;
2071
2072 cursor c_obsolete_partitions(p_table varchar2) is
2073 select table_name, partition_name,
2074 to_number(substr(partition_name, length(p_table)-2)) plan_run_id
2075 from all_tab_partitions
2076 where table_owner=l_msc_schema
2077 and table_name=p_table
2078 and to_number(substr(partition_name, length(p_table)-2)) in
2079 (select plan_run_id from msc_plan_runs where archive_flag=2)
2080 order by 1;
2081
2082 e_partition_not_exist exception;
2083 pragma exception_init (e_partition_not_exist, -2149);
2084 begin
2085 msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions');
2086
2087 l_applsys_schema := msc_phub_util.applsys_schema;
2088 l_msc_schema := msc_phub_util.msc_schema;
2089 l_tables := list_fact_tables(null);
2090
2091 for i in 1..l_tables.count loop
2092 for r in c_obsolete_partitions(l_tables(i)) loop
2093 l_ddl := 'alter table '||r.table_name||' drop partition '||r.partition_name;
2094 begin
2095 msc_phub_util.log(l_ddl);
2096 ad_ddl.do_ddl(l_applsys_schema, l_msc_schema, ad_ddl.alter_table, l_ddl, r.table_name);
2097 exception
2098 when e_partition_not_exist then
2099 msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions.exception: '||sqlerrm);
2100 end;
2101 end loop;
2102 end loop;
2103 msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions: complete');
2104
2105 exception
2106 when others then
2107 msc_phub_util.log('msc_phub_pkg.clean_up_fact_partitions.exception: '||sqlerrm);
2108 raise;
2109 end clean_up_fact_partitions;
2110
2111 procedure migrate_item_d
2112 is
2113 l_sql varchar2(5000);
2114 l_plan_id number;
2115 c sys_refcursor;
2116 l_msc_schema varchar2(100) := msc_phub_util.msc_schema;
2117 begin
2118 msc_phub_util.log('msc_phub_pkg.migrate_item_d');
2119 open c for
2120 ' select distinct plan_id'||
2121 ' from msc_apcc_item_d_bak'||
2122 ' where plan_id>0'||
2123 ' order by 1';
2124 loop
2125 fetch c into l_plan_id;
2126 exit when c%notfound;
2127 manage_partitions(item_dim_table, l_plan_id, partition_add, partition_list);
2128 end loop;
2129 close c;
2130
2131 l_sql :=
2132 ' insert into msc_apcc_item_d ('||
2133 ' plan_id,'||
2134 ' sr_instance_id,'||
2135 ' organization_id,'||
2136 ' inventory_item_id,'||
2137 ' sr_category_id1,'||
2138 ' sr_category_id2,'||
2139 ' sr_category_id3,'||
2140 ' pegging_sr_category_id,'||
2141 ' latest_item_id,'||
2142 ' vmi_flag,'||
2143 ' item_name,'||
2144 ' description,'||
2145 ' abc_class,'||
2146 ' product_family_id,'||
2147 ' average_daily_demand,'||
2148 ' average_discount,'||
2149 ' base_item_id,'||
2150 ' build_in_wip_flag,'||
2151 ' buyer_name,'||
2152 ' fixed_lead_time,'||
2153 ' list_price,'||
2154 ' max_minmax_quantity,'||
2155 ' min_minmax_quantity,'||
2156 ' minimum_order_quantity,'||
2157 ' mrp_planning_code,'||
2158 ' planner_code,'||
2159 ' planning_make_buy_code,'||
2160 ' postprocessing_lead_time,'||
2161 ' preprocessing_lead_time,'||
2162 ' purchasing_enabled_flag,'||
2163 ' repetitive_type,'||
2164 ' safety_stock_code,'||
2165 ' standard_cost,'||
2166 ' unit_volume,'||
2167 ' unit_weight,'||
2168 ' uom_code,'||
2169 ' variable_lead_time,'||
2170 ' volume_uom,'||
2171 ' weight_uom,'||
2172 ' created_by, creation_date,'||
2173 ' last_update_date, last_updated_by, last_update_login,'||
2174 ' program_id, program_login_id,'||
2175 ' program_application_id, request_id'||
2176 ' )'||
2177 ' select'||
2178 ' plan_id,'||
2179 ' sr_instance_id,'||
2180 ' organization_id,'||
2181 ' inventory_item_id,'||
2182 ' sr_category_id1,'||
2183 ' sr_category_id2,'||
2184 ' sr_category_id3,'||
2185 ' pegging_sr_category_id,'||
2186 ' latest_item_id,'||
2187 ' vmi_flag,'||
2188 ' item_name,'||
2189 ' description,'||
2190 ' abc_class,'||
2191 ' product_family_id,'||
2192 ' average_daily_demand,'||
2193 ' average_discount,'||
2194 ' base_item_id,'||
2195 ' build_in_wip_flag,'||
2196 ' buyer_name,'||
2197 ' fixed_lead_time,'||
2198 ' list_price,'||
2199 ' max_minmax_quantity,'||
2200 ' min_minmax_quantity,'||
2201 ' minimum_order_quantity,'||
2202 ' mrp_planning_code,'||
2203 ' planner_code,'||
2204 ' planning_make_buy_code,'||
2205 ' postprocessing_lead_time,'||
2206 ' preprocessing_lead_time,'||
2207 ' purchasing_enabled_flag,'||
2208 ' repetitive_type,'||
2209 ' safety_stock_code,'||
2210 ' standard_cost,'||
2211 ' unit_volume,'||
2212 ' unit_weight,'||
2213 ' uom_code,'||
2214 ' variable_lead_time,'||
2215 ' volume_uom,'||
2216 ' weight_uom,'||
2217 ' created_by, creation_date,'||
2218 ' last_update_date, last_updated_by, last_update_login,'||
2219 ' program_id, program_login_id,'||
2220 ' program_application_id, request_id'||
2221 ' from msc_apcc_item_d_bak';
2222 execute immediate l_sql;
2223 msc_phub_util.log('insert into msc_apcc_item_d: '||sql%rowcount);
2224 commit;
2225 exception
2226 when others then
2227 msc_phub_util.log('msc_phub_pkg.migrate_item_d.exception:'||sqlerrm);
2228 end migrate_item_d;
2229
2230 procedure migrate_scenario_names is
2231 begin
2232 msc_phub_util.log('msc_phub_pkg.migrate_scenario_names');
2233 update msc_plan_runs set scenario_name = regexp_substr(plan_run_name, '\[.*\]', 1, 1);
2234 update msc_plan_runs set scenario_name = substr(scenario_name, 2, length(scenario_name)-2);
2235 commit;
2236
2237 update msc_plan_runs set last_run_flag=2
2238 where plan_run_id in
2239 (select plan_run_id from
2240 (select plan_name, plan_run_id,
2241 last_value(plan_run_id) over(partition by plan_name order by plan_run_id
2242 rows between unbounded preceding and unbounded following) last_plan_run_id
2243 from msc_plan_runs
2244 where plan_name in
2245 (select plan_name
2246 from msc_plan_runs
2247 where last_run_flag=1
2248 group by plan_name
2249 having count(*)>1
2250 )
2251 )
2252 where plan_run_id <> last_plan_run_id
2253 );
2254 commit;
2255 end;
2256
2257 procedure maintain_data_model(errbuf out nocopy varchar2, retcode out nocopy varchar2,
2258 p_cleanup_facts number default null, p_operation_code varchar2 default null)
2259 is
2260 l_n number := 0;
2261 begin
2262 msc_phub_util.log('msc_phub_pkg.maintain_data_model('||
2263 p_cleanup_facts||','||p_operation_code||')');
2264 retcode := 0;
2265 errbuf := null;
2266
2267 if (p_cleanup_facts = 1) then
2268 clean_up_fact_partitions;
2269 elsif (p_operation_code = 'MIGRATE_ITEM_D') then
2270 migrate_item_d;
2271 elsif (p_operation_code = 'MIGRATE_SCENARIO_NAMES') then
2272 migrate_scenario_names;
2273 else
2274 execute immediate 'begin msc_phub_cost_pkg.migrate; end;';
2275 clean_up_customer_keys;
2276 end if;
2277
2278 -- clear migration flag
2279 msc_phub_util.log('delete from msc_plan_runs where plan_id=0 and plan_run_id=-1');
2280 delete from msc_plan_runs where plan_id=0 and plan_run_id=-1;
2281 commit;
2282
2283 exception
2284 when others then
2285 retcode := -1;
2286 errbuf := 'msc_phub_pkg.maintain_data_model.exception:'||sqlerrm;
2287 msc_phub_util.log(errbuf);
2288 raise;
2289 end maintain_data_model;
2290
2291 procedure check_migrate(errbuf out nocopy varchar2, retcode out nocopy varchar2)
2292 is
2293 l_need_migrate number := 0;
2294 l_request_id number := 0;
2295 begin
2296 msc_phub_util.log('msc_phub_pkg.check_migrate');
2297
2298 -- check migration flag
2299 select count(*) into l_need_migrate
2300 from msc_plan_runs
2301 where plan_id=0 and plan_run_id=-1;
2302
2303 msc_phub_util.log('msc_phub_pkg.check_migrate, l_need_migrate='||l_need_migrate);
2304
2305 if (l_need_migrate > 0) then
2306 l_request_id := fnd_request.submit_request('MSC','MSCHUBM', null, null, false);
2307 msc_phub_util.log('msc_phub_pkg.check_migrate: l_request_id='||l_request_id);
2308 commit;
2309
2310 msc_wait_for_request(l_request_id);
2311 end if;
2312
2313 exception
2314 when others then
2315 retcode := -1;
2316 errbuf := 'msc_phub_pkg.check_migrate.exception:'||sqlerrm;
2317 msc_phub_util.log(errbuf);
2318 raise;
2319 end check_migrate;
2320
2321 procedure archive_demantra_plan(p_publisher varchar2, p_plan_name varchar2, p_keep_previous number)
2322 is
2323 errbuf varchar2(2000);
2324 retcode number := 0;
2325 e_archive_demantra_plan exception;
2326
2327 l_current_schema varchar2(30) := sys_context('USERENV', 'CURRENT_SCHEMA');
2328 begin
2329 execute immediate 'alter session set current_schema='||msc_phub_util.apps_schema;
2330 populate_details(errbuf, retcode,
2331 0, null, p_keep_previous,
2332 p_plan_name, null, null, 10, null, p_publisher);
2333
2334 execute immediate 'alter session set current_schema='||l_current_schema;
2335 if (retcode <> 0) then
2336 raise e_archive_demantra_plan;
2337 end if;
2338 end archive_demantra_plan;
2339
2340 procedure archive_demantra_ods(p_publisher varchar2)
2341 is
2342 errbuf varchar2(2000);
2343 retcode number := 0;
2344 e_archive_demantra_ods exception;
2345
2346 l_current_schema varchar2(30) := sys_context('USERENV', 'CURRENT_SCHEMA');
2347 begin
2348 execute immediate 'alter session set current_schema='||msc_phub_util.apps_schema;
2349 populate_details(errbuf, retcode,
2350 0, null, sys_yes,
2351 null, null, sys_yes, 10, null, p_publisher);
2352
2353 execute immediate 'alter session set current_schema='||l_current_schema;
2354 if (retcode <> 0) then
2355 raise e_archive_demantra_ods;
2356 end if;
2357 end archive_demantra_ods;
2358
2359 procedure sync_demantra(errbuf out nocopy varchar2, retcode out nocopy varchar2) is
2360 begin
2361 retcode := 0;
2362 errbuf := null;
2363 msc_demantra_pkg.create_item_hierarchy_view;
2364 msc_demantra_pkg.register_custom_measures;
2365 exception
2366 when others then
2367 retcode := -1;
2368 errbuf := sqlerrm;
2369 msc_phub_util.log('msc_phub_pkg.sync_demantra.exception: '||errbuf);
2370 end sync_demantra;
2371
2372 END msc_phub_pkg;