[Home] [Help]
PACKAGE BODY: APPS.MSC_PHUB_PKG
Source
1 PACKAGE BODY msc_phub_pkg AS
2 /* $Header: MSCHBPBB.pls 120.48.12010000.5 2008/09/12 23:24:50 pabram ship $ */
3
4 TYPE TabListTblTyp IS TABLE OF VARCHAR2(50);
5
6 sys_yes constant number := 1;
7 sys_no constant number := 2;
8
9 g_plan_id number;
10 g_plan_type number;
11 g_plan_name varchar2(80);
12 g_owning_inst_id number;
13 g_owning_org_id number;
14 g_plan_start_date date;
15 g_plan_end_date date;
16 g_plan_comp_date date;
17 g_display_kpi number;
18 g_scenario_name varchar2(40) := null;
19
20 cursor c_plan_info_cur is
21 select
22 compile_designator,
23 sr_instance_id,
24 organization_id,
25 decode(plan_id, -1, sysdate, trunc(curr_start_date)) curr_start_date,
26 decode(plan_id, -1, sysdate+365, trunc(curr_cutoff_date)) curr_cutoff_date,
27 curr_plan_type,
28 nvl(plan_completion_date, sysdate),
29 nvl(display_kpi,1)
30 from msc_plans
31 where plan_id = g_plan_id;
32
33 --plan archival
34 cursor c_prior_plan_run is
35 select plan_run_id
36 from msc_plan_runs
37 where plan_id = g_plan_id
38 and nvl(archive_flag,sys_no) = sys_yes
39 order by plan_run_id desc;
40
41 procedure println(p_msg varchar2) is
42 begin
43 --insert into msc_temp_xx (msg) values (p_msg); commit;
44 --dbms_output.put_line(p_msg);
45 if ( g_log_flag= 0 ) then
46 return;
47 elsif ( g_log_flag = 1 ) then
48 g_log_row := g_log_row + 1;
49 if (g_log_file_name is null) then
50 select ltrim(rtrim(value))
51 into g_log_file_dir
52 from (select value from v$parameter2 where name='utl_file_dir'
53 order by rownum desc)
54 where rownum <2;
55 g_log_file_name := 'msc-phub.txt';
56 g_log_file_handle := utl_file.fopen(g_log_file_dir, g_log_file_name, 'w');
57 end if;
58
59 if (utl_file.is_open(g_log_file_handle)) then
60 utl_file.put_line(g_log_file_handle, p_msg);
61 utl_file.fflush(g_log_file_handle);
62 utl_file.fclose(g_log_file_handle);
63 else
64 g_log_file_handle := utl_file.fopen(g_log_file_dir, g_log_file_name, 'a');
65 utl_file.put_line(g_log_file_handle, p_msg);
66 utl_file.fflush(g_log_file_handle);
67 utl_file.fclose(g_log_file_handle);
68 end if;
69 elsif ( g_log_flag = 2 ) then
70 fnd_file.put_line(fnd_file.log, p_msg);
71 end if;
72 --dbms_output.put_line(p_msg);
73 exception
74 when others then
75 return;
76 end println;
77
78 function check_apcc_setup return number is
79 l_category_set_id1 varchar2(200) := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
80 l_category_set_id2 varchar2(200) := fnd_profile.value('MSC_HUB_CAT_SET_ID_2');
81 l_category_set_id3 varchar2(200) := fnd_profile.value('MSC_HUB_CAT_SET_ID_3');
82 l_hub_cal_code varchar2(200) := fnd_profile.value('MSC_HUB_CAL_CODE');
83 l_hub_pr_set varchar2(200) := fnd_profile.value('MSC_HUB_PERIOD_SET_NAME');
84 l_hub_cur_code varchar2(200) := fnd_profile.value('MSC_HUB_CUR_CODE_RPT');
85 l_hur_reg_instance varchar2(200) := fnd_profile.value('MSC_HUB_REGION_INSTANCE');
86 l_top_bottom_n varchar2(200) := fnd_profile.value('MSC_HUB_TOP_BOTTOM_N_VALUE');
87 begin
88 if l_category_set_id1 is null then
89 return 2;
90 end if;
91 return 1;
92 end check_apcc_setup;
93
94
95 procedure populate_greg_calendar(p_start_date date, p_end_date date,
96 p_return_status out nocopy number, p_error_message out nocopy varchar2) is
97 l_year_start_date date;
98 l_year_end_date date;
99 l_year_name varchar2(80);
100 l_month_start_date date;
101 l_month_end_date date;
102 l_month_name varchar2(80);
103 l_month_seq number;
104 l_detail_date date;
105 l_start_date date;
106 l_end_date date;
107
108 begin
109 p_return_status := 1;
110 --validation
111 if (p_start_date is null or p_end_date is null) then
112 p_return_status := -1;
113 p_error_message := 'ERROR :: Start date or End date is null for populating greg calendar';
114 end if;
115 if (p_start_date > p_end_date) then
116 p_return_status := -2;
117 p_error_message := 'ERROR :: Start date is greater than End date';
118 end if;
119
120 l_start_date := p_start_date;
121 l_end_date := p_end_date;
122 --println('starts start-date end-date '|| to_char(l_start_date,'dd-mon-yyyy') ||' - '|| to_char(l_end_date,'dd-mon-yyyy') );
123
124 l_detail_date := trunc(l_start_date);
125 loop
126 --println('starts l_detail_date end-date '|| to_char(l_detail_date,'dd-mon-yyyy') ||' - '|| to_char(l_end_date,'dd-mon-yyyy') ||' - '||l_cnt);
127 if (l_detail_date > l_end_date) then
128 exit;
129 end if;
130
131 l_year_start_date := to_date('01/01/'||to_char(l_detail_date,'YYYY'), 'mm/dd/yyyy');
132 l_year_end_date := to_date('01/01/'|| to_number(to_char(l_detail_date,'YYYY')+1), 'mm/dd/yyyy') - 1;
133 l_year_name := to_char(l_detail_date,'YYYY');
134
135 l_month_start_date := to_date(to_char(l_detail_date,'MM') || '/01' || to_char(l_detail_date,'YYYY'), 'mm/dd/yyyy');
136 l_month_end_date := ADD_MONTHS(to_date(to_char(l_detail_date,'MM') || '/01' || to_char(l_detail_date,'YYYY'), 'mm/dd/yyyy'), 1) - 1;
137 l_month_name := to_char(l_detail_date,'MON');
138 l_month_seq := to_char(l_detail_date,'MM');
139
140 insert into msc_calendar_dtl (year_start_date, year_end_date, year_name,
141 month_start_date, month_end_date, month_name, month_seq, detail_date,
142 created_by, creation_date, last_update_date, last_updated_by, last_update_login)
143 values (l_year_start_date, l_year_end_date, l_year_name,
144 l_month_start_date, l_month_end_date, l_month_name, l_month_seq, l_detail_date,
145 fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.user_id);
146
147 l_detail_date := l_detail_date + 1; --increment the date
148 end loop;
149 println('ends');
150 end populate_greg_calendar;
151
152 procedure flush_greg_calendar(p_return_status out nocopy number, p_error_message out nocopy varchar2) is
153 cursor c_get_minmax_dates is
154 select min(detail_date) min_date,
155 max(detail_date) max_date
156 from msc_calendar_dtl;
157 l_min_date date;
158 l_max_date date;
159
160 cursor c_get_mfg_minmax_dates is
161 select min(calendar_date) mfg_min_date, max(calendar_date) mfg_max_date
162 from msc_calendars mc,
163 msc_calendar_dates mcd
164 where mc.calendar_code = fnd_profile.value('MSC_HUB_CAL_CODE')
165 and mc.calendar_code = mcd.calendar_code
166 and mc.sr_instance_id = mcd.sr_instance_id;
167 l_mfg_min_date date;
168 l_mfg_max_date date;
169
170 cursor c_get_bis_minmax_dates is
171 select min(mbp.start_date) bis_min_date, max(mbp.end_date) bis_max_date
172 from msc_bis_periods mbp,
173 msc_trading_partners mtp
174 where mbp.sr_instance_id = mtp.sr_instance_id
175 and mbp.organization_id = mtp.sr_tp_id
176 and mtp.partner_type = 3
177 and mtp.organization_code||':'||period_set_name = fnd_profile.value('MSC_HUB_PERIOD_SET_NAME');
178 l_bis_min_date date;
179 l_bis_max_date date;
180
181 l_cal_min_date date;
182 l_cal_max_date date;
183
184 begin
185 println('populating msc_calendar_dtl starts');
186 p_return_status := 1;
187
188 open c_get_minmax_dates;
189 fetch c_get_minmax_dates into l_min_date, l_max_date;
190 close c_get_minmax_dates;
191 println('l_min_date, l_max_date '||l_min_date||' - '||l_max_date);
192
193 open c_get_mfg_minmax_dates;
194 fetch c_get_mfg_minmax_dates into l_mfg_min_date, l_mfg_max_date;
195 close c_get_mfg_minmax_dates;
196 println('l_mfg_min_date, l_mfg_max_date '||l_mfg_min_date||' - '||l_mfg_max_date);
197
198 open c_get_bis_minmax_dates;
199 fetch c_get_bis_minmax_dates into l_bis_min_date, l_bis_max_date;
200 close c_get_bis_minmax_dates;
201 println('l_bis_min_date, l_bis_max_date '||l_bis_min_date ||' - '||l_bis_max_date);
202
203 if (l_mfg_min_date is null and l_bis_min_date is null) then
204 return;
205 end if;
206
207 if (l_mfg_min_date is null or l_bis_min_date is null) then
208 if (l_mfg_min_date is null) then
209 l_cal_min_date := l_bis_min_date;
210 else
211 l_cal_min_date := l_mfg_min_date;
212 end if;
213 else
214 if ( l_mfg_min_date < l_bis_min_date) then
215 l_cal_min_date := l_mfg_min_date;
216 else
217 l_cal_min_date := l_bis_min_date;
218 end if;
219 end if;
220
221 if (l_mfg_max_date is null or l_bis_max_date is null) then
222 if (l_mfg_max_date is null) then
223 l_cal_max_date := l_bis_max_date;
224 else
225 l_cal_max_date := l_mfg_max_date;
226 end if;
227 else
228 if ( l_mfg_max_date > l_bis_max_date) then
229 l_cal_max_date := l_mfg_max_date;
230 else
231 l_cal_max_date := l_bis_max_date;
232 end if;
233 end if;
234 println('l_cal_min_date, l_cal_max_date '||l_cal_min_date ||' - '||l_cal_max_date);
235
236 if (l_min_date is null or l_max_date is null) then
237 populate_greg_calendar(l_cal_min_date, l_cal_max_date, p_return_status, p_error_message);
238 if (p_return_status <> 1) then
239 return;
240 end if;
241 else
242 if (l_cal_min_date < l_min_date) then
243 populate_greg_calendar(l_cal_min_date, l_min_date-1, p_return_status, p_error_message);
244 if (p_return_status <> 1) then
245 return;
246 end if;
247 end if;
248
249 if (l_cal_max_date > l_max_date) then
250 populate_greg_calendar(l_max_date+1, l_cal_max_date, p_return_status, p_error_message);
251 if (p_return_status <> 1) then
252 return;
253 end if;
254 end if;
255 end if;
256 commit;
257 println('populating msc_calendar_dtl ends');
258 exception
259 when others then
260 p_return_status := -3;
261 fnd_message.set_name('MSC', 'MSC_HUB_GREG_CAL_ERROR');
262 println(fnd_message.get||' - '||SQLERRM);
263 end flush_greg_calendar;
264
265 function submit_exceptions_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
266 p_plan_id number, p_plan_run_id number) return number is
267 l_req_id number := 0;
268 begin
269 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_STARTS_1');
270 fnd_message.set_token('TABLE_NAME', 'Exceptions');
271 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
272 --msc_exception_pkg.populate_details(errbuf, retcode, p_plan_id, p_plan_run_id);
273 l_req_id := fnd_request.submit_request('MSC','MSCHUB1',NULL,
274 null, false, p_plan_id, p_plan_run_id);
275 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS_1');
276 fnd_message.set_token('TABLE_NAME', 'Exceptions');
277 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
278 return l_req_id;
279 end submit_exceptions_f;
280
281 function submit_resources_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
282 p_plan_id number, p_plan_run_id number) return number is
283 l_req_id number := 0;
284 begin
285 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_STARTS_1');
286 fnd_message.set_token('TABLE_NAME', 'Resources');
287 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
288 --msc_resource_pkg.populate_details(errbuf, retcode, p_plan_id, p_plan_run_id);
289 l_req_id := fnd_request.submit_request('MSC','MSCHUB2',NULL,
290 null, false, p_plan_id, p_plan_run_id);
291 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS_1');
292 fnd_message.set_token('TABLE_NAME', 'Resources');
293 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
294 return l_req_id;
295 end submit_resources_f;
296
297 function submit_suppliers_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
298 p_plan_id number, p_plan_run_id number) return number is
299 l_req_id number := 0;
300 begin
301 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_STARTS_1');
302 fnd_message.set_token('TABLE_NAME', 'Suppliers');
303 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
304 --msc_supplier_pkg.populate_details(errbuf, retcode, p_plan_id,p_plan_run_id);
305 l_req_id := fnd_request.submit_request('MSC','MSCHUB3',NULL,
306 null, false, p_plan_id, p_plan_run_id);
307 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS_1');
308 fnd_message.set_token('TABLE_NAME', 'Suppliers');
309 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
310 return l_req_id;
311 end submit_suppliers_f;
312
313 function submit_supplies_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
314 p_plan_id number, p_plan_run_id number) return number is
315 l_req_id number := 0;
316 begin
317 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS_1');
318 fnd_message.set_token('TABLE_NAME', 'Supplies');
319 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
320 --msc_supply_pkg.populate_details(errbuf, retcode, p_plan_id, p_plan_run_id);
321 l_req_id := fnd_request.submit_request('MSC','MSCHUB4',NULL,
322 null, false, p_plan_id, p_plan_run_id);
323 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS_1');
324 fnd_message.set_token('TABLE_NAME', 'Supplies');
325 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
326 return l_req_id;
327 end submit_supplies_f;
328
329 function submit_demands_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
330 p_plan_id number, p_plan_run_id number) return number is
331 l_req_id number := 0;
332 begin
333 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_STARTS_1');
334 fnd_message.set_token('TABLE_NAME', 'Demands');
335 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
336 --msc_demand_pkg.populate_details(errbuf, retcode, p_plan_id, p_plan_run_id);
337 l_req_id := fnd_request.submit_request('MSC','MSCHUB5',NULL,
338 null, false, p_plan_id, p_plan_run_id);
339 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS_1');
340 fnd_message.set_token('TABLE_NAME', 'Demands');
341 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
342 return l_req_id;
343 end submit_demands_f;
344
345 function submit_items_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
346 p_plan_id number, p_plan_run_id number) return number is
347 l_req_id number := 0;
348 begin
349 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_STARTS_1');
350 fnd_message.set_token('TABLE_NAME', 'Item');
351 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
352 --msc_item_pkg.populate_details(errbuf, retcode, p_plan_id, p_plan_run_id);
353 l_req_id := fnd_request.submit_request('MSC','MSCHUB6',NULL,
354 null, false, p_plan_id, p_plan_run_id);
355 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS_1');
356 fnd_message.set_token('TABLE_NAME', 'Item');
357 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
358 return l_req_id;
359 end submit_items_f;
360
361 function submit_demantra_f(errbuf out nocopy varchar2, retcode out nocopy varchar2,
362 p_plan_id number, p_plan_run_id number) return number is
363 l_req_id number := 0;
364 begin
365 println('submit_demantra_f('||p_plan_id||', '||p_plan_run_id||')');
366 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_STARTS_1');
367 fnd_message.set_token('TABLE_NAME', 'Demantra Plan');
368 println(fnd_message.get);
369 msc_demantra_pkg.populate_details(errbuf, retcode, p_plan_id, p_plan_run_id);
370 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS_1');
371 fnd_message.set_token('TABLE_NAME', 'Demantra Plan');
372 println(fnd_message.get);
373 return l_req_id;
374 end submit_demantra_f;
375
376 procedure msc_wait_for_request(p_request_id in number,
377 p_timeout in number, o_retcode out nocopy number) is
378
379 l_refreshed_flag NUMBER;
380 l_pending_timeout_flag NUMBER;
381 l_start_time DATE;
382
383 l_call_status boolean;
384 l_phase varchar2(80);
385 l_status varchar2(80);
386 l_dev_phase varchar2(80);
387 l_dev_status varchar2(80);
388 l_message varchar2(240);
389 l_request_id number;
390
391 l_ctr number := 0;
392 begin
393 l_request_id := p_request_id;
394 l_start_time := SYSDATE;
395 LOOP
396 << begin_loop >>
397 dbms_lock.sleep(10);
398 l_pending_timeout_flag := sign( sysdate - l_start_time - p_timeout/1440.0);
399 l_call_status:= fnd_concurrent.wait_for_request(l_request_id,
400 10, 10, l_phase, l_status, l_dev_phase, l_dev_status, l_message);
401 println(' msc_wait_for_request request_id complete status '|| l_request_id ||' - '|| l_dev_phase ||' - '|| l_dev_status);
402 exit when l_call_status=FALSE;
403 if l_dev_phase='PENDING' then
404 exit when l_pending_timeout_flag= 1;
405 elsif l_dev_phase='RUNNING' then
406 GOTO begin_loop;
407 elsif l_dev_phase='COMPLETE' then
408 if l_dev_status = 'NORMAL' then
409 println(' msc_wait_for_request request_id complete status '|| l_request_id ||' - '|| l_dev_phase ||' - '|| l_dev_status);
410 o_retcode:= sys_yes;
411 return;
412 end if;
413 exit;
414 elsif l_dev_phase='INACTIVE' THEN
415 exit when l_pending_timeout_flag= 1;
416 end if;
417 --dbms_lock.sleep(10);
418 end loop;
419
420 o_retcode:= sys_no;
421 return;
422 end msc_wait_for_request;
423
424 function populate_facts(errbuf out nocopy varchar2, retcode out nocopy varchar2,
425 p_plan_id number, p_plan_run_id number) return number is
426 l_exceptions_req_id number;
427 l_resource_req_id number;
428 l_suppliers_req_id number;
429 l_supplies_req_id number;
430 l_demands_req_id number;
431 l_items_req_id number;
432
433 l_max_wait_time number := 999999;
434 l_out_status varchar2(2000);
435 begin
436 l_exceptions_req_id := submit_exceptions_f(errbuf, retcode, p_plan_id, p_plan_run_id); commit;
437 println('exceptons req id '|| l_exceptions_req_id);
438
439 l_resource_req_id := submit_resources_f(errbuf, retcode, p_plan_id, p_plan_run_id); commit;
440 println('resource req id '|| l_resource_req_id);
441
442 l_suppliers_req_id := submit_suppliers_f(errbuf, retcode, p_plan_id, p_plan_run_id); commit;
443 println('suppliers req id '|| l_suppliers_req_id);
444
445 l_supplies_req_id := submit_supplies_f(errbuf, retcode, p_plan_id, p_plan_run_id); commit;
446 println('supplies req id '|| l_supplies_req_id);
447
448 l_demands_req_id := submit_demands_f(errbuf, retcode, p_plan_id, p_plan_run_id); commit;
449 println('demands req id '|| l_demands_req_id);
450
451 msc_wait_for_request(l_exceptions_req_id, l_max_wait_time, l_out_status);
452 if ( l_out_status = sys_no ) then
453 return sys_no;
454 end if;
455
456 msc_wait_for_request(l_resource_req_id, l_max_wait_time, l_out_status);
457 if ( l_out_status = sys_no ) then
458 return sys_no;
459 end if;
460
461 msc_wait_for_request(l_suppliers_req_id, l_max_wait_time, l_out_status);
462 if ( l_out_status = sys_no ) then
463 return sys_no;
464 end if;
465
466 msc_wait_for_request(l_supplies_req_id, l_max_wait_time, l_out_status);
467 if ( l_out_status = sys_no ) then
468 return sys_no;
469 end if;
470
471 msc_wait_for_request(l_demands_req_id, l_max_wait_time, l_out_status);
472 if ( l_out_status = sys_no ) then
473 return sys_no;
474 end if;
475
476 l_items_req_id := submit_items_f(errbuf, retcode, p_plan_id, p_plan_run_id); commit;
477 println('items req id '|| l_items_req_id);
478 msc_wait_for_request(l_items_req_id, l_max_wait_time, l_out_status);
479 if ( l_out_status = sys_no ) then
480 return sys_no;
481 end if;
482 return sys_yes;
483 end populate_facts;
484
485 function manage_partitions(p_plan_run_id number, p_part_mode number) return number is
486 l_partitioned_tables TabListTblTyp:= TabListTblTyp(
487 'MSC_DEMANDS_F',
488 'MSC_DEMANDS_CUM_F',
489 'MSC_DEMANTRA_F',
490 'MSC_EXCEPTIONS_F',
491 'MSC_ITEM_INVENTORY_F',
492 'MSC_ITEM_ORDERS_F',
493 'MSC_RESOURCES_F',
494 'MSC_SUPPLIERS_F',
495 'MSC_SUPPLIES_F',
496 'MSC_ITEM_WIPS_F'
497 );
498
499 l_partition_name varchar2(300);
500 sql_stmt varchar2(300);
501 dummy1 varchar2(50);
502 dummy2 varchar2(50);
503 l_applsys_schema varchar2(100);
504 errbuf varchar2(2000);
505 retcode number := 0;
506
507 begin
508 if (FND_INSTALLATION.GET_APP_INFO('FND',dummy1,dummy2,l_applsys_schema) = FALSE) then
509 retcode := FND_API.G_RET_STS_ERROR;
510 fnd_message.set_name('MSC','MSC_PART_UNDEFINED_SCHEMA');
511 errbuf := fnd_message.get;
512 println(errbuf);
513 return retcode;
514 end if;
515
516 for i in 1..l_partitioned_tables.count loop
517 l_partition_name := substr(l_partitioned_tables(i),5)||'_'||to_char(p_plan_run_id);
518
519 if (p_part_mode = 1) then
520 -- create partitions
521 sql_stmt := 'alter table ' || l_partitioned_tables(i) || ' add partition '
522 || l_partition_name || ' values less than ('|| to_char(p_plan_run_id+1) || ')';
523 elsif (p_part_mode = 2) then
524 --drop partitions
525 sql_stmt := 'alter table ' || l_partitioned_tables(i) || ' drop partition '|| l_partition_name ;
526 end if;
527 println(sql_stmt);
528 ad_ddl.do_ddl(l_applsys_schema,'MSC', ad_ddl.alter_table,sql_stmt,l_partitioned_tables(i));
529 end loop;
530 return retcode;
531 exception
532 when others then
533 retcode := 1;
534 errbuf := 'Error in manage_partitions api '||SQLCODE||' -ERROR- '||SQLERRM;
535 println(errbuf);
536 return retcode;
537 end manage_partitions;
538
539 --lookup values for plan types old and new
540 --MSC_PLAN_TYPE
541 --1 Manufacturing Plan
542 --2 Production Plan
543 --3 Master Plan
544 --4 Inventory Plan
545 --5 Distribution Plan
546 --6 Maintenance Schedule
547 --7 Manufacturing Schedule
548 --8 Service Plan
549 --9 Service Inventory Plan
550
551 --MSC_SCN_PLAN_TYPES
552 --1 Advanced Supply Chain Planning
553 --4 Inventory Optimization
554 --5 Distribution Planning
555 --6 Strategic Network Optimization
556 --8 Service Planning
557 --10 Demand Management
558
559 function populate_plan_run_info(p_plan_id number, p_plan_name varchar2, p_plan_type number, p_plan_comp_date date) return number is
560 cursor c_nextval is
561 select msc_plan_runs_s.nextval
562 from dual;
563
564 cursor c_plan_version is
565 select count(*)
566 from msc_plan_runs
567 where plan_id = p_plan_id;
568
569 l_plan_run_id number;
570 l_plan_version number;
571 l_plan_run_name varchar2(100);
572 l_scenario_suffix varchar2(40) := null;
573
574 begin
575 open c_nextval;
576 fetch c_nextval into l_plan_run_id;
577 close c_nextval;
578
579 open c_plan_version;
580 fetch c_plan_version into l_plan_version;
581 close c_plan_version;
582
583 if g_scenario_name is not null then
584 l_scenario_suffix := ' ['||g_scenario_name||']';
585 end if;
586
587 if (p_plan_type in (1,2,3,4,5,6,7,8)) then
588 insert into msc_plan_runs
589 (plan_id, plan_run_id, plan_run_name, plan_completion_date, last_run_flag,
590 created_by, creation_date, last_update_date, last_updated_by, last_update_login,
591 start_date, end_date, archive_flag, planning_hub_flag)
592 values (p_plan_id, l_plan_run_id,
593 p_plan_name||' '||to_char(p_plan_comp_date,'mm/dd')||'('||l_plan_version||')'||l_scenario_suffix,
594 p_plan_comp_date, 2,
595 fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.user_id,
596 sysdate, null, sys_no, sys_no);
597 commit;
598 elsif (p_plan_type in (10)) then
599 insert into msc_plan_runs
600 (plan_id, plan_run_id, plan_run_name, plan_completion_date, last_run_flag,
601 created_by, creation_date, last_update_date, last_updated_by, last_update_login,
602 start_date, end_date, archive_flag, planning_hub_flag)
603 select
604 das.scenario_id plan_id,
605 l_plan_run_id plan_run_id,
606 das.scenario_name||' '||to_char(sysdate,'mm/dd')||'('||l_plan_version||')'||l_scenario_suffix plan_run_name,
607 sysdate plan_completion_date,
608 sys_yes last_run_flag,
609 fnd_global.user_id created_by,
610 sysdate creation_date,
611 sysdate last_update_date,
612 fnd_global.user_id last_updated_by,
613 fnd_global.user_id last_update_login,
614 tq.from_date start_date,
615 tq.until_date end_date,
616 sys_yes archive_flag,
617 sys_no planning_hub_flag
618 from msd_dp_ascp_scenarios_v das,
619 msd_dem_transfer_query tq
620 where das.demand_plan_id=5555555
621 and das.demand_plan_name = substr(tq.query_name, 1, 30)
622 and das.scenario_id = p_plan_id;
623
624 commit;
625 end if;
626
627 select plan_run_name into l_plan_run_name from msc_plan_runs
628 where plan_id=p_plan_id and plan_run_id=l_plan_run_id;
629 println('msc_phub_pkg.populate_plan_run_info('||p_plan_id||', '||l_plan_run_id||
630 ', '''||l_plan_run_name||''')');
631
632 return l_plan_run_id;
633 end populate_plan_run_info;
634
635 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
636 p_plan_id number, p_plan_run_id number default null, p_archive_flag number default -1) is
637
638 l_plan_run_id number;
639 l_plan_version number;
640 l_return_status number;
641 l_error_message varchar2(100);
642
643 p_return_status number := sys_no;
644 p_error_message varchar2(2000);
645
646 p_archive_prior_plan number;
647 p_planning_hub_flag number;
648
649 l_purge_errbuf varchar2(2000);
650 l_purge_retcode varchar2(2000);
651
652 l_part_retval number;
653
654 l_purge_plan_run_id number;
655 l_purge_req_id number;
656
657 l_max_wait_time number := 999999;
658 l_out_status varchar2(2000);
659
660 l_display_kpi number;
661 begin
662 retcode := 0;
663 errbuf := NULL;
664
665 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_STARTS');
666 println(fnd_message.get);
667
668 g_plan_id := p_plan_id;
669
670 if ( p_archive_flag in (-1,1) ) then
671 -- archive the plan
672 null;
673 else
674 --dont archive the plan, purge the prior run...
675 open c_prior_plan_run;
676 fetch c_prior_plan_run into l_purge_plan_run_id;
677 close c_prior_plan_run;
678 --purge_details(l_purge_errbuf, l_purge_retcode, g_plan_id, l_purge_plan_run_id);
679 l_purge_req_id := fnd_request.submit_request('MSC','MSCHUBP',NULL, NULL, FALSE, g_plan_id, l_purge_plan_run_id);
680 commit;
681
682 msc_wait_for_request(l_purge_req_id, l_max_wait_time, l_out_status);
683 if ( l_out_status = sys_no ) then
684 println('Purging the prior plan run has failed.');
685 --return sys_no;
686 end if;
687 end if;
688
689 --get plan info
690 open c_plan_info_cur;
691 fetch c_plan_info_cur into g_plan_name, g_owning_inst_id, g_owning_org_id, g_plan_start_date, g_plan_end_date,
692 g_plan_type, g_plan_comp_date, g_display_kpi;
693 close c_plan_info_cur;
694
695 if (p_plan_run_id is null) then
696 l_plan_run_id := populate_plan_run_info(g_plan_id, g_plan_name, g_plan_type, g_plan_comp_date);
697
698 else
699 l_plan_run_id := p_plan_run_id;
700 end if;
701
702 --check_apcc_setup
703 l_return_status := check_apcc_setup;
704 if (l_return_status = 2) then
705 retcode := 1;
706 errbuf := 'APCC Profiles are not set correctly. Please set the profiles correctly.';
707 println(errbuf);
708 return;
709 end if;
710
711
712 if (g_display_kpi = 1 or g_plan_type = 6) then
713 l_display_kpi := sys_yes;
714 else
715 l_display_kpi := sys_no;
716 end if;
717
718 if (l_display_kpi = sys_yes) then --(
719 --managing partitions
720 l_part_retval := manage_partitions(l_plan_run_id, 1);
721 if (l_part_retval <> 0) then
722 retcode := 1;
723 errbuf := 'Error while creating partitions ';
724 println(errbuf);
725 return;
726 end if;
727
728 -- this will refresh all mvs for now, but we need to place this call in collections conc program also
729 --pabram need to move collections mv refresh later, need to move this code after populate_facts
730 -- bug 6836759 , comment out refresh_mvs(3) for now
731 --bug 6665805, collections is calling this api after collections run, commenting this 051308
732 --refresh_mvs(3);
733
734 --populate fact/summary tables
735 p_return_status := populate_facts(errbuf, retcode, g_plan_id, l_plan_run_id);
736 if (p_return_status = sys_no) then
737 retcode := 1;
738 errbuf := 'Error while populating the fact tables. purging this plan summary. ';
739 println(errbuf);
740
741 --purge_details(l_purge_errbuf, l_purge_retcode, g_plan_id, l_plan_run_id);
742 l_purge_req_id := fnd_request.submit_request('MSC','MSCHUBP',NULL, NULL, FALSE, g_plan_id, l_plan_run_id);
743 commit;
744 end if;
745 end if; --}
746
747 --updating last_run_flag to 2 for earlier versions
748 update msc_plan_runs
749 set last_run_flag = decode(p_return_status, sys_yes, sys_no, sys_yes)
750 where plan_id = g_plan_id
751 and plan_run_id < l_plan_run_id;
752
753 update msc_plan_runs
754 set last_run_flag = decode(p_return_status, sys_yes, sys_yes, sys_no),
755 end_date = sysdate,
756 planning_hub_flag = decode(p_return_status, sys_yes, sys_yes, sys_no),
757 archive_flag = l_display_kpi
758 where plan_id = g_plan_id
759 and plan_run_id = l_plan_run_id;
760
761 commit;
762
763 --if (retcode = 0 and l_purge_retcode <> 0) then
764 --errbuf := l_purge_errbuf;
765 --retcode := l_purge_retcode;
766 --end if;
767
768 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS');
769 println(fnd_message.get);
770 end populate_details;
771
772 procedure purge_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
773 p_plan_id number, p_plan_run_id number) is
774 l_part_retval number;
775
776 cursor c_plan_runs is
777 select plan_run_id
778 from msc_plan_runs
779 where plan_id = g_plan_id
780 and plan_run_id = nvl(p_plan_run_id, plan_run_id)
781 and nvl(archive_flag,sys_no) = sys_yes;
782 begin
783 retcode := 0;
784 errbuf := NULL;
785 g_plan_id := p_plan_id;
786
787 fnd_message.set_name('MSC', 'MSC_HUB_PURGE_STARTS');
788 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
789
790 for r_plan_runs in c_plan_runs
791 loop
792 l_part_retval := manage_partitions(r_plan_runs.plan_run_id, 2);
793
794 update msc_plan_runs
795 set
796 planning_hub_flag = sys_no,
797 last_run_flag = sys_no,
798 archive_flag = sys_no
799 where plan_run_id = r_plan_runs.plan_run_id;
800 commit;
801
802 if (l_part_retval <> 0) then
803 retcode := 1;
804 errbuf := 'Error while dropping partitions ';
805 println(errbuf);
806 end if;
807 end loop;
808
809 /*
810 --purge fact/summary tables
811 msc_exception_pkg.purge_details(errbuf, retcode, p_plan_id, p_plan_run_id);
812 msc_resource_pkg.purge_details(errbuf, retcode, p_plan_id, p_plan_run_id);
813 msc_supplier_pkg.purge_details(errbuf, retcode, p_plan_id, p_plan_run_id);
814
815 msc_supply_pkg.purge_details(errbuf, retcode, p_plan_id, p_plan_run_id);
816 msc_demand_pkg.purge_details(errbuf, retcode, p_plan_id, p_plan_run_id);
817 msc_item_pkg.purge_details(errbuf, retcode, p_plan_id, p_plan_run_id);
818 commit;
819 */
820
821 fnd_message.set_name('MSC', 'MSC_HUB_PURGE_ENDS');
822 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || fnd_message.get);
823
824 --MSC_HUB_PURGE_ERROR
825 end purge_details;
826
827 procedure refresh_one_mv(p_name varchar2) is
828 begin
829 dbms_mview.refresh(p_name);
830 end refresh_one_mv;
831
832 procedure refresh_ods_mvs(errbuf out nocopy varchar2, retcode out nocopy varchar2) is
833 l_ods_table_list TabListTblTyp := TabListTblTyp(
834 'MSC_PHUB_FISCAL_YEARS_MV',
835 'MSC_PHUB_FISCAL_QUARTERS_MV',
836 'MSC_PHUB_FISCAL_PERIODS_MV',
837 'MSC_PHUB_MFG_CAL_PERIODS_MV',
838 'MSC_PHUB_MFG_CAL_WEEKS_MV',
839 'MSC_PHUB_MFG_CAL_DATES_MV',
840 'MSC_PHUB_CATEGORIES_MV',
841 'MSC_PHUB_ITEM_CATEGORIES_MV',
842 'MSC_PHUB_CUSTOMERS_MV',
843 'MSC_PHUB_SUPPLIERS_MV',
844 'MSC_CURRENCY_CONV_MV'
845 );
846 l_name varchar2(50);
847
848 p_return_status number;
849 p_error_message varchar2(2000);
850 begin
851 --flush gregorian calendar
852 flush_greg_calendar(p_return_status, p_error_message);
853
854 for i in 1..l_ods_table_list.count loop
855 l_name := l_ods_table_list(i);
856 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || 'Refreshing MV : '||l_name||' starts');
857 refresh_one_mv(l_name);
858 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || 'Refreshing MV : '||l_name||' ends');
859 end loop;
860
861 exception
862 when others then
863 retcode := 1;
864 errbuf := 'Error while Refreshing MV : '||l_name;
865 println(errbuf);
866 end refresh_ods_mvs;
867
868 procedure refresh_pds_mvs(errbuf out nocopy varchar2, retcode out nocopy varchar2) is
869 l_pds_table_list TabListTblTyp := TabListTblTyp(
870 -- 'MSC_DEMANDS_F_MV'
871 );
872 l_name varchar2(50);
873 begin
874 for i in 1..l_pds_table_list.count loop
875 l_name := l_pds_table_list(i);
876 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || 'Refreshing MV : '||l_name||' starts');
877 refresh_one_mv(l_name);
878 println(to_char(sysdate,'YYYY/MM/DD:HH24:MI:SS') || ' ' || 'Refreshing MV : '||l_name||' ends');
879 end loop;
880
881 exception
882 when others then
883 retcode := 1;
884 errbuf := 'Error while Refreshing MV : '||l_name;
885 println(errbuf);
886 end refresh_pds_mvs;
887
888 procedure refresh_mvs(p_refresh_mode varchar2) is
889 errbuf varchar2(2000);
890 retcode varchar2(2000);
891 begin
892 retcode := 0;
893 errbuf := NULL;
894 --1 ods, 2 pds, 3 both
895 if (p_refresh_mode = 1) then
896 refresh_ods_mvs(errbuf, retcode);
897 elsif (p_refresh_mode = 2) then
898 refresh_pds_mvs(errbuf, retcode);
899 elsif (p_refresh_mode = 3) then
900 refresh_ods_mvs(errbuf, retcode);
901 refresh_pds_mvs(errbuf, retcode);
902 end if;
903 end refresh_mvs;
904
905 procedure populate_demantra_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
906 p_scenario_id number, p_plan_run_id number, p_archive_flag number default -1) is
907
908 cursor c_nextval is
909 select msc_plan_runs_s.nextval
910 from dual;
911
912 cursor c_plan_version(p_scenario_id number) is
913 select count(*)
914 from msc_plan_runs
915 where plan_id = p_scenario_id;
916
917 l_plan_run_id number;
918 l_plan_version number;
919 l_req_id number;
920 l_purge_plan_run_id number;
921 l_purge_req_id number;
922 l_part_retval number;
923 l_max_wait_time number := 999999;
924 l_out_status varchar2(2000);
925 begin
926 retcode := 0;
927 errbuf := null;
928 g_plan_id := p_scenario_id;
929
930 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_STARTS');
931 println(fnd_message.get);
932
933 if ( p_archive_flag in (-1,1) ) then
934 -- archive the plan
935 null;
936 else
937 --dont archive the plan, purge the prior run...
938 open c_prior_plan_run;
939 fetch c_prior_plan_run into l_purge_plan_run_id;
940 close c_prior_plan_run;
941
942 --purge_details(l_purge_errbuf, l_purge_retcode, g_plan_id, l_purge_plan_run_id);
943 l_purge_req_id := fnd_request.submit_request('MSC','MSCHUBP',NULL, NULL, FALSE, g_plan_id, l_purge_plan_run_id);
944 commit;
945
946 msc_wait_for_request(l_purge_req_id, l_max_wait_time, l_out_status);
947 if ( l_out_status = sys_no ) then
948 println('Purging the prior plan run has failed.');
949 --return sys_no;
950 end if;
951 end if;
952
953 if (p_plan_run_id is null) then
954 l_plan_run_id := populate_plan_run_info(p_scenario_id, null, 10, to_date(null));
955 else
956 l_plan_run_id := p_plan_run_id;
957 end if;
958
959 --bugfix 6810755, need to create partitions, since archive scnario will
960 --call populate_plan_run_info first and then this procedure
961 l_part_retval := manage_partitions(l_plan_run_id, 1);
962 if (l_part_retval <> 0) then
963 retcode := 1;
964 errbuf := 'Error while creating partitions ';
965 println(errbuf);
966 return;
967 end if;
968
969
970 l_req_id := submit_demantra_f(errbuf, retcode, p_scenario_id, l_plan_run_id);
971 if (retcode <> 0) then
972 println(errbuf);
973 return;
974 end if;
975
976 update msc_plan_runs
977 set last_run_flag = sys_no
978 where plan_id = p_scenario_id
979 and plan_run_id < l_plan_run_id;
980
981 update msc_plan_runs
982 set last_run_flag = sys_yes,
983 end_date = sysdate,
984 planning_hub_flag = sys_yes
985 where plan_id = p_scenario_id
986 and plan_run_id = l_plan_run_id;
987
988 commit;
989
990 fnd_message.set_name('MSC', 'MSC_HUB_POPULATE_ENDS');
991 println(fnd_message.get);
992 end populate_demantra_details;
993
994 procedure purge_demantra_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
995 p_scenario_id number, p_plan_run_id number) is
996 begin
997 retcode := 0;
998 errbuf := null;
999
1000 fnd_message.set_name('MSC', 'MSC_HUB_PURGE_STARTS');
1001 println(fnd_message.get);
1002
1003 msc_phub_pkg.purge_details(errbuf, retcode, p_scenario_id, p_plan_run_id);
1004 -- msc_demantra_pkg.purge_details(errbuf, retcode, p_scenario_id, p_plan_run_id);
1005
1006 fnd_message.set_name('MSC', 'MSC_HUB_PURGE_ENDS');
1007 println(fnd_message.get);
1008
1009 end purge_demantra_details;
1010
1011 procedure populate_sno_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
1012 p_plan_id number, p_plan_run_id number default null,
1013 p_archive_flag number default -1,
1014 p_scenario_name in varchar2 default null) is
1015 begin
1016 g_scenario_name := substr(p_scenario_name, 1, 30);
1017 populate_details(errbuf, retcode, p_plan_id, p_plan_run_id, p_archive_flag);
1018 end;
1019
1020
1021
1022 END msc_phub_pkg;