[Home] [Help]
PACKAGE BODY: APPS.MSC_SNAPSHOT_PK
Source
1 PACKAGE BODY msc_snapshot_pk AS
2 /* $Header: MSCPSNPB.pls 120.25.12020000.2 2012/10/23 13:47:46 ayussing ship $ */
3
4 MAKE_ITEM CONSTANT INTEGER:= 1;
5 mrdebug CONSTANT BOOLEAN := true;
6 NULL_VALUE CONSTANT NUMBER := -23453;
7
8 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
9 IS
10 BEGIN
11
12 IF fnd_global.conc_request_id > 0 THEN -- concurrent program
13
14 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
15 --dbms_output.put_line( pBUFF);
16
17 ELSE
18
19 --dbms_output.put_line( pBUFF);
20 null;
21
22 END IF;
23
24 END LOG_MESSAGE;
25
26
27 PROCEDURE insert_into_table(
28 p_plan_id NUMBER,
29 p_sr_instance_id NUMBER,
30 p_org_id NUMBER,
31 p_bucket_index NUMBER,
32 p_msc_plan_buckets IN OUT NOCOPY msc_plan_buckets_typ,
33 p_err_mesg OUT NOCOPY VARCHAR2
34 );
35
36 -- ********************** complete_task *************************
37 PROCEDURE complete_task(
38 arg_plan_id IN NUMBER,
39 arg_task IN NUMBER) IS
40 BEGIN
41
42 UPDATE msc_snapshot_tasks
43 SET completion_date = SYSDATE,
44 program_update_date = SYSDATE
45 WHERE task = arg_task
46 AND plan_id = arg_plan_id;
47
48 COMMIT;
49
50 END complete_task;
51
52 PROCEDURE get_bucket_cutoff_dates(
53 p_plan_id IN NUMBER,
54 p_org_id IN NUMBER,
55 p_instance_id IN NUMBER,
56 p_plan_start_date IN DATE,
57 p_plan_completion_date IN DATE,
58 -- used by form
59 p_min_cutoff_bucket IN number,
60 p_hour_cutoff_bucket IN number,
61 p_daily_cutoff_bucket IN number,
62 p_weekly_cutoff_bucket IN number,
63 p_period_cutoff_bucket IN number,
64 -- used by form
65 p_min_cutoff_date OUT NOCOPY DATE,
66 p_hour_cutoff_date OUT NOCOPY DATE,
67 p_daily_cutoff_date OUT NOCOPY DATE,
68 p_weekly_cutoff_date OUT NOCOPY DATE,
69 p_period_cutoff_date OUT NOCOPY DATE,
70 p_err_mesg OUT NOCOPY VARCHAR2
71 ) IS
72
73 l_min_cutoff_bucket NUMBER;
74 l_hour_cutoff_bucket NUMBER;
75 l_daily_cutoff_bucket NUMBER;
76 l_weekly_cutoff_bucket NUMBER;
77 l_period_cutoff_bucket NUMBER;
78
79 l_bucket_end_date DATE;
80 l_bucket_start_date DATE;
81 l_from_forms varchar2(1) := 'N';
82 BEGIN
83
84 p_min_cutoff_date := NULL;
85 p_hour_cutoff_date := NULL;
86 p_daily_cutoff_date := NULL;
87 p_weekly_cutoff_date := NULL;
88 p_period_cutoff_date := NULL;
89
90 IF p_min_cutoff_bucket IS NULL AND p_hour_cutoff_bucket IS NULL AND
91 p_daily_cutoff_bucket IS NULL AND p_weekly_cutoff_bucket IS NULL AND
92 p_period_cutoff_bucket IS NULL THEN
93 -- Not called from form.
94 SELECT
95 Nvl(min_cutoff_bucket,0),
96 Nvl(hour_cutoff_bucket,0),
97 Nvl(daily_cutoff_bucket,0),
98 -- +Nvl(min_cutoff_bucket,0) bug 1226108
99 -- +Nvl(hour_cutoff_bucket,0), bug 1226108
100 Nvl(weekly_cutoff_bucket,0)*7,
101 Nvl(period_cutoff_bucket,0)
102 INTO l_min_cutoff_bucket, l_hour_cutoff_bucket,
103 l_daily_cutoff_bucket, l_weekly_cutoff_bucket, l_period_cutoff_bucket
104 FROM msc_plans
105 WHERE plan_id = p_plan_id;
106 ELSE
107 l_min_cutoff_bucket := p_min_cutoff_bucket;
108 l_hour_cutoff_bucket := p_hour_cutoff_bucket;
109 l_daily_cutoff_bucket := p_daily_cutoff_bucket;
110 -- +p_min_cutoff_bucket+p_hour_cutoff_bucket; bug 1226108
111 --modification to set the number of days in weekly buckets to 7 times
112 --the number of weeks
113 l_weekly_cutoff_bucket := p_weekly_cutoff_bucket*7;
114 l_period_cutoff_bucket := p_period_cutoff_bucket;
115 l_from_forms := 'Y';
116 END IF;
117
118 -- the plan cutoff date changes to be the period_cutoff_date
119
120 IF mrdebug = TRUE THEN
121 null;
122 /*LOG_MESSAGE('Cutoff buckets : '
123 ||' min '||l_min_cutoff_bucket
124 ||' hour '||l_hour_cutoff_bucket
125 ||' daily '||l_daily_cutoff_bucket
126 ||' weekly '||l_weekly_cutoff_bucket
127 ||' period '||l_period_cutoff_bucket);
128 */
129 END IF;
130
131 IF l_min_cutoff_bucket <> 0 THEN
132 p_min_cutoff_date := Trunc(Sysdate) + (l_min_cutoff_bucket-1);
133 ELSE
134 p_min_cutoff_date := Trunc(Sysdate);
135 END IF;
136
137 IF l_hour_cutoff_bucket <> 0 THEN
138 IF l_min_cutoff_bucket <> 0 THEN
139 p_hour_cutoff_date := p_min_cutoff_date + l_hour_cutoff_bucket;
140 ELSE
141 -- If it is 0 then we can include plan_start_date for the hour bucket.
142 p_hour_cutoff_date := p_min_cutoff_date + (l_hour_cutoff_bucket-1);
143 END IF;
144 ELSE
145 p_hour_cutoff_date := p_min_cutoff_date;
146 END IF;
147
148 -- Already summed up min+hour+daily. So just start from sysdate.
149 if l_from_forms = 'Y' then
150 IF l_daily_cutoff_bucket <> 0 THEN
151 l_bucket_end_date := p_plan_start_date + (l_daily_cutoff_bucket-1);
152 ELSE
153 l_bucket_end_date := p_plan_start_date - 1;
154 END IF;
155 else
156 IF l_daily_cutoff_bucket <> 0 THEN
157 l_bucket_end_date := Trunc(p_plan_start_date) + (l_daily_cutoff_bucket-1);
158 ELSE
159 l_bucket_end_date := Trunc(p_plan_start_date) - 1;
160 END IF;
161 end if;
162
163
164
165 IF l_weekly_cutoff_bucket <> 0 THEN
166 p_daily_cutoff_date :=
167 msc_calendar.next_work_day(-1*p_org_id,
168 p_instance_id,
169 msc_calendar.type_weekly_bucket,
170 l_bucket_end_date + 1
171 );
172
173 p_daily_cutoff_date := p_daily_cutoff_date -1;
174
175
176 -- The above call gives the week start date after l_bucket_end_date
177 IF mrdebug = TRUE THEN
178 null;
179 --LOG_MESSAGE('p_daily_cutoff_date #1 = '||p_daily_cutoff_date||' org '||p_org_id||' inst '||p_instance_id||' l_bucket_end_date '||l_bucket_end_date);
180 END IF;
181 ELSIF l_period_cutoff_bucket <> 0 THEN
182 p_daily_cutoff_date :=
183 msc_calendar.next_work_day(-1*p_org_id,
184 p_instance_id,
185 msc_calendar.type_monthly_bucket,
186 l_bucket_end_date + 1
187 );
188 p_daily_cutoff_date := p_daily_cutoff_date -1;
189 -- The above call gives the period start date after l_bucket_end_date
190 IF mrdebug = TRUE THEN
191 null;
192 --LOG_MESSAGE('p_daily_cutoff_date #2 = '||p_daily_cutoff_date||' org '||p_org_id||' inst '||p_instance_id||' l_bucket_end_date '||l_bucket_end_date);
193 END IF;
194 ELSE
195 IF l_daily_cutoff_bucket <> 0 THEN
196 p_daily_cutoff_date := l_bucket_end_date;
197 ELSE
198 l_bucket_end_date := l_bucket_end_date + 1;
199 p_daily_cutoff_date := l_bucket_end_date;
200 END IF;
201 END IF; -- IF l_weekly_cutoff_bucket <> 0 THEN
202 -- END IF; -- IF l_daily_cutoff_bucket <> 0 THEN .... moved up
203
204
205 IF l_weekly_cutoff_bucket <> 0 THEN
206
207 -- IF l_daily_cutoff_bucket = 0 THEN
208 IF p_daily_cutoff_date = Trunc(p_plan_start_date) THEN
209 -- There is no daily bucket
210 l_bucket_start_date := Trunc(p_plan_start_date);
211 ELSE
212 -- Get next week start date after daily period ends
213 l_bucket_start_date := p_daily_cutoff_date + 1;
214 END IF;
215
216 l_bucket_end_date := l_bucket_start_date + (l_weekly_cutoff_bucket-1);
217
218 IF l_period_cutoff_bucket <> 0 THEN
219 -- This needs to be changed to be TYPE_PERIOD_BUCKET instead of months
220 p_weekly_cutoff_date :=
221 msc_calendar.next_work_day(-1*p_org_id,
222 p_instance_id,
223 msc_calendar.type_monthly_bucket,
224 l_bucket_end_date + 1
225 );
226
227 p_weekly_cutoff_date := p_weekly_cutoff_date -1;
228 IF mrdebug = TRUE THEN
229 null;
230 --LOG_MESSAGE('p_weekly_cutoff_date #1 = '||p_weekly_cutoff_date||' org '||p_org_id||' inst '||p_instance_id||' l_bucket_end_date '||l_bucket_end_date);
231 END IF;
232 ELSE
233 p_weekly_cutoff_date := l_bucket_end_date;
234 END IF; -- IF l_period_cutoff_bucket <> 0 THEN
235
236
237 END IF; -- IF l_weekly_cutoff_bucket <> 0 THEN
238
239 IF l_period_cutoff_bucket <> 0 THEN
240 IF p_weekly_cutoff_date IS NOT NULL THEN
241 l_bucket_end_date := p_weekly_cutoff_date + 1;
242 ELSIF p_daily_cutoff_date IS NOT NULL THEN
243 l_bucket_end_date := p_daily_cutoff_date + 1;
244 ELSE
245 l_bucket_end_date := Trunc(p_plan_start_date); -- They are planning only in periods
246 END IF;
247
248 FOR j IN 1..l_period_cutoff_bucket loop
249 p_period_cutoff_date :=
250 msc_calendar.next_work_day(-1*p_org_id,
251 p_instance_id,
252 msc_calendar.type_monthly_bucket,
253 l_bucket_end_date + 1
254 );
255 l_bucket_end_date := p_period_cutoff_date;
256 IF mrdebug = TRUE THEN
257 null;
258 --LOG_MESSAGE('p_period_cutoff_date = '||p_period_cutoff_date);
259 END IF;
260 END LOOP;
261 p_period_cutoff_date := p_period_cutoff_date -1;
262
263
264 END IF;
265
266 END get_bucket_cutoff_dates;
267
268 PROCEDURE calculate_plan_buckets(
269 p_plan_id IN NUMBER,
270 p_err_mesg OUT NOCOPY VARCHAR2,
271 p_min_cutoff_date OUT NOCOPY number,
272 p_hour_cutoff_date OUT NOCOPY number,
273 p_daily_cutoff_date OUT NOCOPY number,
274 p_weekly_cutoff_date OUT NOCOPY number,
275 p_period_cutoff_date OUT NOCOPY number,
276 p_min_cutoff_bucket OUT NOCOPY number,
277 p_hour_cutoff_bucket OUT NOCOPY number,
278 p_daily_cutoff_bucket OUT NOCOPY number,
279 p_weekly_cutoff_bucket OUT NOCOPY number,
280 p_period_cutoff_bucket OUT NOCOPY number
281 )
282 IS
283
284 CURSOR plan_orgs_cur IS
285 SELECT /*+ NOREWRITE */
286 mpl.organization_id,
287 mpl.sr_instance_id,
288 TRUNC(sysdate),
289 mpl.cutoff_date
290 FROM
291 msc_trading_partners mtp,
292 msc_plans mpl
293 WHERE
294 mpl.plan_id = p_plan_id
295 and mpl.organization_id = mtp.sr_tp_id
296 and mtp.partner_type = 3
297 and mpl.sr_instance_id = mtp.sr_instance_id;
298
299 l_calendar_code Varchar2(14);
300
301 l_min_cutoff_date DATE;
302 l_hour_cutoff_date DATE;
303 l_daily_cutoff_date DATE;
304 l_weekly_cutoff_date DATE;
305 l_period_cutoff_date DATE;
306
307 l_bkt_start_date DATE;
308 l_bkt_index Number :=0;
309
310 l_plan_start_date DATE;
311 l_plan_comp_date DATE;
312 l_plan_cutoff_date DATE;
313 l_org_id Number;
314 l_sr_instance_id Number;
315
316 l_msc_plan_buckets msc_plan_buckets_typ;
317
318 m_calendar_code VARCHAR2(14);
319 m_cal_exception_set_id Number;
320 m_sr_instance_id Number;
321
322 l_curr_start_date DATE;
323 l_weekly_buckets Number;
324
325 j NUMBER;
326
327 lv_bkt_ref_calendar varchar2(14) := fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR');
328 lv_bkt_ref_instance number;
329 BEGIN
330
331 -- IF mrdebug = TRUE THEN
332 -- dbms_output.enable(1000000);
333 -- END IF;
334
335 DELETE FROM msc_plan_buckets
336 WHERE plan_id = p_plan_id;
337
338 OPEN plan_orgs_cur;
339 FETCH plan_orgs_cur
340 INTO
341 l_org_id, l_sr_instance_id, l_plan_start_date, l_plan_comp_date;
342 CLOSE plan_orgs_cur;
343
344 SELECT decode(plan_type, 4, trunc(curr_start_date), 9,
345 trunc(curr_start_date), trunc(sysdate)),
346 weekly_cutoff_bucket
347 into l_curr_start_date, l_weekly_buckets
348 FROM msc_plans
349 WHERE plan_id = p_plan_id;
350
351
352 IF l_curr_start_date < l_plan_start_date THEN
353 IF l_weekly_buckets > 0 THEN
354 IF (lv_bkt_ref_calendar is null) then
355 select min(cal.week_start_date)
356 into l_curr_start_date
357 from msc_cal_week_start_dates cal,
358 msc_trading_partners tp,
359 msc_calendar_dates mc
360 where cal.exception_set_id = tp.calendar_exception_set_id
361 and mc.exception_set_id = tp.calendar_exception_set_id
362 and cal.calendar_code = tp.calendar_code
363 and mc.calendar_code = tp.calendar_code
364 and cal.sr_instance_id = tp.sr_instance_id
365 and mc.sr_instance_id = tp.sr_instance_id
366 and cal.week_start_date >= mc.next_date
367 and mc.calendar_date = trunc(sysdate)
368 and tp.sr_tp_id = l_org_id
369 and tp.sr_instance_id = l_sr_instance_id
370 and tp.partner_type = 3 ;
371 ELSE
372 select min(cal.week_start_date)
373 into l_curr_start_date
374 from msc_cal_week_start_dates cal,
375 msc_calendar_dates mc
376 where cal.exception_set_id = mc.exception_set_id
377 and cal.calendar_code = mc.calendar_code
378 and cal.sr_instance_id = mc.sr_instance_id
379 and cal.week_start_date >= mc.next_date
380 and mc.calendar_date = trunc(sysdate)
381 and mc.calendar_code = lv_bkt_ref_calendar;
382 END IF;
383 ELSE
384 IF (lv_bkt_ref_calendar is null) then
385 select min(cal.period_start_date)
386 into l_curr_start_date
387 from msc_period_start_dates cal,
388 msc_trading_partners tp
389 where cal.exception_set_id = tp.calendar_exception_set_id
390 and cal.calendar_code = tp.calendar_code
391 and cal.period_start_date >= trunc(sysdate)
392 and cal.sr_instance_id = tp.sr_instance_id
393 and tp.sr_tp_id = l_org_id
394 and tp.sr_instance_id = l_sr_instance_id
395 and tp.partner_type = 3;
396 ELSE
397 select min(cal.period_start_date)
398 into l_curr_start_date
399 from msc_period_start_dates cal
400 where cal.period_start_date >= trunc(sysdate)
401 and cal.calendar_code = lv_bkt_ref_calendar;
402 END IF;
403
404 END IF;
405 END IF;
406
407
408 l_plan_start_date := l_curr_start_date;
409
410 lv_bkt_ref_instance := l_sr_instance_id;
411
412 IF (lv_bkt_ref_calendar is not null) then
413 select sr_instance_id
414 into lv_bkt_ref_instance
415 from msc_calendar_dates
416 where calendar_code = lv_bkt_ref_calendar
417 and calendar_date = trunc(sysdate)
418 and exception_set_id = -1;
419
420 END IF;
421 /*
422 IF mrdebug = TRUE THEN
423 LOG_MESSAGE('owning_org = '||l_org_id||' instance '||l_sr_instance_id||' start '||l_plan_start_date||' comp '||l_plan_comp_date);
424 END IF;
425 */
426 get_bucket_cutoff_dates(
427 p_plan_id => p_plan_id,
428 p_org_id => l_org_id,
429 p_instance_id => lv_bkt_ref_instance,
430 p_plan_start_date => l_plan_start_date,
431 p_plan_completion_date => l_plan_comp_date,
432 p_min_cutoff_bucket => NULL,
433 p_hour_cutoff_bucket => NULL,
434 p_daily_cutoff_bucket => NULL,
435 p_weekly_cutoff_bucket => NULL,
436 p_period_cutoff_bucket => NULL,
437 p_min_cutoff_date => l_min_cutoff_date,
438 p_hour_cutoff_date => l_hour_cutoff_date,
439 p_daily_cutoff_date => l_daily_cutoff_date,
440 p_weekly_cutoff_date => l_weekly_cutoff_date,
441 p_period_cutoff_date => l_period_cutoff_date,
442 p_err_mesg => p_err_mesg
443 );
444
445 p_min_cutoff_date := To_char(l_min_cutoff_date,'J');
446 p_hour_cutoff_date := To_char(l_hour_cutoff_date,'J');
447 p_daily_cutoff_date := To_char(l_daily_cutoff_date,'J');
448 p_weekly_cutoff_date := To_char(Nvl(l_weekly_cutoff_date,l_daily_cutoff_date),'J');
449 p_period_cutoff_date := To_char(Nvl(l_period_cutoff_date,Nvl(l_weekly_cutoff_date,l_daily_cutoff_date)),'J');
450
451 l_plan_cutoff_date := to_date(p_period_cutoff_date,'J');
452
453 IF p_err_mesg IS NOT NULL THEN
454 RETURN;
455 END IF;
456
457 -- l_weekly_cutoff_date := NULL;
458 -- l_period_cutoff_date := NULL;
459 /*
460 IF mrdebug = TRUE THEN
461 LOG_MESSAGE(' min = '||l_min_cutoff_date);
462 LOG_MESSAGE(' hour = '||l_hour_cutoff_date);
463 LOG_MESSAGE(' daily = '||l_daily_cutoff_date);
464 LOG_MESSAGE(' weekly = '||l_weekly_cutoff_date);
465 LOG_MESSAGE(' period = '||l_period_cutoff_date);
466 END IF;
467 */
468 -- Set the cutoff_date of the plan based period, week, day buckets set
469 UPDATE msc_plans
470 SET curr_cutoff_date = l_plan_cutoff_date,
471 curr_start_date = l_curr_start_date
472 WHERE plan_id = p_plan_id;
473
474
475 SELECT Nvl(min_cutoff_bucket,0), Nvl(hour_cutoff_bucket,0)+Nvl(min_cutoff_bucket,0)
476 INTO p_min_cutoff_bucket, p_hour_cutoff_bucket
477 FROM msc_plans
478 WHERE plan_id = p_plan_id;
479
480 -- select bucket reference calendar
481 select nvl(fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'), tp.calendar_code),
482 tp.calendar_exception_set_id ,
483 decode(fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'), NULL, tp.sr_instance_id, mcd.sr_instance_id)
484 into m_calendar_code , m_cal_exception_set_id , m_sr_instance_id
485 from msc_plans mp,
486 msc_trading_partners tp,
487 msc_calendar_dates mcd
488 where mp.plan_id = p_plan_id
489 and tp.partner_type = 3
490 and tp.sr_instance_id = mp.sr_instance_id
491 and mp.organization_id = tp.sr_tp_id
492 and mcd.exception_set_id = tp.calendar_exception_set_id
493 and mcd.calendar_date = trunc(sysdate)
494 and mcd.calendar_code = nvl(fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'), tp.calendar_code);
495
496
497 IF l_daily_cutoff_date IS NOT NULL THEN
498 -- insert the daily time buckets
499 SELECT
500 rownum
501 ,To_char(cal.calendar_date, 'YYYY/MM/DD')
502 ,To_char(cal.calendar_date,'YYYY/MM/DD')
503 ,1 bucket_type
504 ,1 days_in_bucket
505 BULK COLLECT INTO
506 l_msc_plan_buckets.bucket_index,
507 l_msc_plan_buckets.bkt_start_date,
508 l_msc_plan_buckets.bkt_end_date,
509 l_msc_plan_buckets.bucket_type,
510 l_msc_plan_buckets.days_in_bkt
511 FROM
512 MSC_CALENDAR_DATES cal
513 WHERE
514 cal.sr_instance_id = m_sr_instance_id
515 AND cal.calendar_code = m_calendar_code
516 AND cal.exception_set_id = m_cal_exception_set_id
517 and trunc(cal.calendar_date) <= trunc(l_daily_cutoff_date )
518 and trunc(cal.calendar_date) >= l_curr_start_date
519 ORDER BY cal.calendar_date;
520
521
522 insert_into_table(
523 p_plan_id,
524 l_sr_instance_id,
525 l_org_id,
526 l_bkt_index,
527 l_msc_plan_buckets,
528 p_err_mesg);
529
530 IF p_err_mesg IS NOT NULL THEN
531 /* IF mrdebug = TRUE THEN
532 LOG_MESSAGE(' 101 ');
533 END IF; */
534 RETURN;
535 END IF;
536
537 p_daily_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
538 p_weekly_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
539 p_period_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
540
541 l_bkt_index := l_bkt_index + l_msc_plan_buckets.bucket_index.count;
542 l_msc_plan_buckets := NULL;
543 END IF;
544
545
546 IF l_weekly_cutoff_date IS NOT NULL THEN
547
548 SELECT
549 l_bkt_index+rownum
550 ,To_char(cal.week_start_date, 'YYYY/MM/DD')
551 ,To_char(Least(
552 Greatest(cal.next_date - 1, cal.week_start_date),
553 -- for last week both are same
554 l_weekly_cutoff_date),'YYYY/MM/DD')
555 --min of this and weekly cutoff
556 ,2 bucket_type
557 ,trunc(Least(
558 Greatest(cal.next_date - 1, cal.week_start_date),
559 l_weekly_cutoff_date)) - trunc(cal.week_start_date) + 1 days_in_bucket
560 BULK COLLECT INTO
561 l_msc_plan_buckets.bucket_index,
562 l_msc_plan_buckets.bkt_start_date,
563 l_msc_plan_buckets.bkt_end_date,
564 l_msc_plan_buckets.bucket_type,
565 l_msc_plan_buckets.days_in_bkt
566 FROM
567 MSC_CAL_WEEK_START_DATES cal
568 WHERE cal.sr_instance_id = m_sr_instance_id
569 AND cal.calendar_code = m_calendar_code
570 AND cal.exception_set_id = m_cal_exception_set_id
571 and trunc(cal.week_start_date) <= trunc(l_weekly_cutoff_date)
572 and trunc(cal.week_start_date) >= trunc(Nvl(l_daily_cutoff_date,Sysdate-1))+1
573 ORDER BY cal.week_start_date ASC;
574
575 insert_into_table(
576 p_plan_id,
577 l_sr_instance_id,
578 l_org_id,
579 l_bkt_index,
580 l_msc_plan_buckets,
581 p_err_mesg);
582
583 IF p_err_mesg IS NOT NULL THEN
584 /* IF mrdebug = TRUE THEN
585 LOG_MESSAGE(' 201 ');
586 END IF; */
587 RETURN;
588 END IF;
589
590 p_weekly_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
591 p_period_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
592
593 l_bkt_index := l_bkt_index + l_msc_plan_buckets.bucket_index.count;
594 l_msc_plan_buckets := NULL;
595
596
597 END IF;
598
599
600 IF l_period_cutoff_date IS NOT NULL THEN
601
602 SELECT
603 l_bkt_index+ROWNUM
604 ,To_char(cal.period_start_date, 'YYYY/MM/DD') bkt_start_date
605 ,To_char(Least(
606 Greatest(cal.next_date - 1,cal.period_start_date),
607 l_period_cutoff_date), 'YYYY/MM/DD') bkt_end_date
608 ,3 bucket_type
609 ,trunc(Least(
610 Greatest(cal.next_date - 1,cal.period_start_date),
611 l_period_cutoff_date)) - trunc(cal.period_start_date)
612 + 1 days_in_bucket
613 -- days between needs a + 1
614 BULK COLLECT INTO
615 l_msc_plan_buckets.bucket_index,
616 l_msc_plan_buckets.bkt_start_date,
617 l_msc_plan_buckets.bkt_end_date,
618 l_msc_plan_buckets.bucket_type,
619 l_msc_plan_buckets.days_in_bkt
620 FROM
621 msc_period_start_dates cal
622 WHERE
623 cal.sr_instance_id = m_sr_instance_id
624 AND cal.calendar_code = m_calendar_code
625 AND cal.exception_set_id = m_cal_exception_set_id
626 and trunc(cal.period_start_date) <= trunc(l_period_cutoff_date )
627 and trunc(cal.period_start_date) >=
628 trunc(Nvl(l_weekly_cutoff_date, Nvl(l_daily_cutoff_date,Sysdate-1))) + 1
629 ORDER BY cal.period_start_date;
630
631 insert_into_table(
632 p_plan_id,
633 l_sr_instance_id,
634 l_org_id,
635 l_bkt_index,
636 l_msc_plan_buckets,
637 p_err_mesg);
638 IF p_err_mesg IS NOT NULL THEN
639 /* IF mrdebug = TRUE THEN
640 LOG_MESSAGE(' 301 ');
641 END IF; */
642 RETURN;
643 END IF;
644 p_period_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
645
646 END IF;
647
648 EXCEPTION
649 WHEN OTHERS THEN
650 /* IF mrdebug = TRUE THEN
651 LOG_MESSAGE('Error in calculate_plan_buckets :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
652 END IF; */
653 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
654 END calculate_plan_buckets;
655
656 /*====================================================
657 This procedure will be used for concurrent program
658 This will call refresh_snapshot_ods_mv internally
659 with different parameters
660
661 p_source : 1 - Collections
662 2 - Others
663 ====================================================*/
664 PROCEDURE refresh_snapshot_ods_mv(
665 ERRBUF OUT NOCOPY VARCHAR2,
666 RETCODE OUT NOCOPY NUMBER,
667 p_plan_id IN NUMBER default null) IS
668 lv_plan_so NUMBER := 0;
669 lv_global_forecast NUMBER := 0;
670 lv_err_code NUMBER;
671 lv_err_mesg VARCHAR2(2000);
672 lv_launch_refresh_gf BOOLEAN := FALSE;
673 l_latest_change_date DATE;
674 last_gf_refresh_date DATE;
675 BEGIN
676 LOG_MESSAGE('Started refresh of global forecast');
677
678 IF p_plan_id IS NOT NULL THEN
679
680 BEGIN
681
682 select count(*) into lv_plan_so
683 from msc_plan_organizations_v
684 where plan_id = p_plan_id
685 and nvl(include_salesorder,SYS_NO) = SYS_YES;
686
687 EXCEPTION WHEN OTHERS THEN
688 RETCODE := G_ERROR;
689 RETURN;
690 END;
691
692 BEGIN
693
694 select count(*)
695 into lv_global_forecast
696 from msc_plan_schedules_v
697 where plan_id = p_plan_id
698 and input_organization_id = GLOBAL_ORG;
699
700 EXCEPTION WHEN OTHERS THEN
701 RETCODE := G_ERROR;
702 RETURN;
703 END;
704
705 /*================================================
706 Check if refresh global forecast needs to be
707 launched.
708 ================================================*/
709
710 IF (lv_global_forecast > 0 OR lv_plan_so >0 ) THEN
711
712 /*
713 BEGIN
714 select nvl(global_fcst_refresh_date, to_date('01-JAN-1900','dd-mon-yyyy'))
715 into last_gf_refresh_date
716 from msc_plans
717 where plan_id = p_plan_id;
718
719 EXCEPTION WHEN OTHERS THEN
720 RETCODE := G_ERROR;
721 RETURN;
722 END;
723
724 IF (lv_launch_refresh_gf = FALSE) THEN
725 l_latest_change_date := NULL;
726 BEGIN
727 select max(last_update_date) into l_latest_change_date
728 from msc_plan_organizations
729 where plan_id = p_plan_id;
730 EXCEPTION WHEN OTHERS THEN
731 RETCODE := G_ERROR;
732 RETURN;
733 END;
734
735 IF (l_latest_change_date > last_gf_refresh_date) THEN
736 lv_launch_refresh_gf := TRUE;
737 END IF;
738
739 END IF;
740
741 IF (lv_launch_refresh_gf = FALSE) THEN
742 l_latest_change_date := NULL;
743 BEGIN
744 select max(msa.last_update_date) into l_latest_change_date
745 from msc_sr_assignments msa,
746 msc_plans mp
747 where mp.plan_id = p_plan_id
748 and mp.FORECAST_ASSIGNMENT_SET_ID = msa.assignment_set_id;
749 EXCEPTION WHEN OTHERS THEN
750 RETCODE := G_ERROR;
751 RETURN;
752 END;
753
754 IF (l_latest_change_date > last_gf_refresh_date) THEN
755 lv_launch_refresh_gf := TRUE;
756 END IF;
757
758 END IF;
759
760 IF (lv_launch_refresh_gf = FALSE) THEN
761 l_latest_change_date := NULL;
762 BEGIN
763 select max(msr.last_update_date) into l_latest_change_date
764 from msc_sourcing_rules msr,
765 msc_sr_assignments msra,
766 msc_plans mp
767 where mp.plan_id = p_plan_id
768 and mp.FORECAST_ASSIGNMENT_SET_ID = msra.ASSIGNMENT_SET_ID
769 and msra.sourcing_rule_id = msr.sourcing_rule_id;
770 EXCEPTION WHEN OTHERS THEN
771 RETCODE := G_ERROR;
772 RETURN;
773 END;
774
775 IF (l_latest_change_date > last_gf_refresh_date) THEN
776 lv_launch_refresh_gf := TRUE;
777 END IF;
778
779 END IF; */
780
781 lv_launch_refresh_gf := TRUE;
782 ELSE
783 lv_launch_refresh_gf := FALSE;
784 END IF; /* IF (lv_global_forecast > 0 OR lv_plan_so >0 ) THEN */
785
786 ELSE
787 lv_launch_refresh_gf := TRUE;
788 END IF; /* p_plan_id IS NOT NULL THEN */
789
790
791 /*===========================
792 Call refresh_snapshot_ods_mv
793 ============================*/
794 IF ( lv_launch_refresh_gf) THEN
795
796 LOG_MESSAGE('Calling refresh_snp_ods_mv_pvt for refresh of global forecast');
797 refresh_snp_ods_mv_pvt(lv_err_code,
798 lv_err_mesg,
799 p_plan_id,
800 lv_global_forecast,
801 lv_plan_so);
802
803 ELSE
804 LOG_MESSAGE('This plan does not have Sales Orders and Global Forecast.');
805 LOG_MESSAGE('There is no need to refresh global forecast');
806 RETCODE := G_SUCCESS;
807 END IF;
808
809 IF lv_err_code = G_ERROR THEN
810 RETCODE := G_ERROR;
811 LOG_MESSAGE('RETCODE -'||lv_err_code);
812 LOG_MESSAGE(lv_err_mesg);
813 ELSE
814 RETCODE := G_SUCCESS;
815 LOG_MESSAGE('RETCODE -'||lv_err_code);
816 END IF;
817
818 END refresh_snapshot_ods_mv;
819
820 PROCEDURE refresh_snp_ods_mv_pvt(
821 p_err_code OUT NOCOPY NUMBER,
822 p_err_mesg OUT NOCOPY VARCHAR2,
823 p_plan_id in NUMBER,
824 p_global_forecast in number default null,
825 p_plan_so in number default null
826 ) IS
827 lv_sql_stmt VARCHAR2(32767);
828 lv_plan_id VARCHAR2(200);
829 lv_task_start_time DATE;
830 lv_retval boolean;
831 lv_dummy1 varchar2(32);
832 lv_dummy2 varchar2(32);
833 lv_msc_schema varchar2(32);
834 BEGIN
835 /* DBMS_MVIEW.REFRESH ('MSC_ITEM_SO_SR_LEVELS_MV','c');
836 DBMS_MVIEW.REFRESH ('MSC_ITEM_FCST_SR_LEVELS_MV','c');*/
837
838 IF p_plan_id is NULL THEN
839 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'MSC', lv_dummy1, lv_dummy2, lv_msc_schema);
840
841 lv_sql_stmt:= 'TRUNCATE TABLE '||lv_msc_schema||'.MSC_ITEM_SO_SR_LEVELS';
842 EXECUTE IMMEDIATE lv_sql_stmt;
843
844 lv_sql_stmt:= 'TRUNCATE TABLE '||lv_msc_schema||'.MSC_ITEM_FCST_SR_LEVELS';
845 EXECUTE IMMEDIATE lv_sql_stmt;
846
847 lv_plan_id := ' ';
848
849 ELSE
850 DELETE MSC_ITEM_SO_SR_LEVELS where plan_id = p_plan_id;
851 DELETE MSC_ITEM_FCST_SR_LEVELS where plan_id = p_plan_id;
852 lv_plan_id := ' WHERE plan_id = '||p_plan_id||' ';
853 END IF;
854
855 COMMIT;
856
857 IF (p_plan_so > 0 OR p_plan_id IS NULL) THEN
858
859 lv_sql_stmt :=
860 ' INSERT /*+ APPEND */ INTO MSC_ITEM_SO_SR_LEVELS'
861 ||' (INVENTORY_ITEM_ID ,'
862 ||' ORGANIZATION_ID ,'
863 ||' SR_INSTANCE_ID ,'
864 ||' PLAN_ID ,'
865 ||' ASSIGNMENT_TYPE ,'
866 ||' ASSIGNMENT_SET_ID ,'
867 ||' SOURCING_RULE_TYPE ,'
868 ||' SOURCE_ORGANIZATION_ID ,'
869 ||' SOURCE_ORG_INSTANCE_ID ,'
870 ||' ALLOCATION_PERCENT ,'
871 ||' RANK ,'
872 ||' EFFECTIVE_DATE ,'
873 ||' DISABLE_DATE ,'
874 ||' SOURCING_LEVEL ,'
875 ||' ASSIGNMENT_ID ,'
876 ||' SOURCING_RULE_ID ,'
877 ||' SOURCING_RULE_NAME ,'
878 ||' SOURCE_TYPE ,'
879 ||' SR_DESCRIPTION ,'
880 ||' COMPILE_DESIGNATOR ,'
881 ||' OWNING_ORG_ID ,'
882 ||' COMP_MRP_PLANNING_CODE ,'
883 ||' COMP_BOM_ITEM_TYPE ,'
884 ||' COMP_PLANNING_MAKE_BUY_CODE ,'
885 ||' COMP_PRIMARY_UOM_CODE ,'
886 ||' CUSTOMER_ID ,'
887 ||' CUSTOMER_SITE_ID ,'
888 ||' REGION_ID ,'
889 ||' COMP_DRP_PLANNED )'
890 ||' SELECT '
891 ||' INVENTORY_ITEM_ID ,'
892 ||' ORGANIZATION_ID ,'
893 ||' SR_INSTANCE_ID ,'
894 ||' PLAN_ID ,'
895 ||' ASSIGNMENT_TYPE ,'
896 ||' ASSIGNMENT_SET_ID ,'
897 ||' SOURCING_RULE_TYPE ,'
898 ||' SOURCE_ORGANIZATION_ID ,'
899 ||' SOURCE_ORG_INSTANCE_ID ,'
900 ||' ALLOCATION_PERCENT ,'
901 ||' RANK ,'
902 ||' EFFECTIVE_DATE ,'
903 ||' DISABLE_DATE ,'
904 ||' SOURCING_LEVEL ,'
905 ||' ASSIGNMENT_ID ,'
906 ||' SOURCING_RULE_ID ,'
907 ||' SOURCING_RULE_NAME ,'
908 ||' SOURCE_TYPE ,'
909 ||' SR_DESCRIPTION ,'
910 ||' COMPILE_DESIGNATOR ,'
911 ||' OWNING_ORG_ID ,'
912 ||' COMP_MRP_PLANNING_CODE ,'
913 ||' COMP_BOM_ITEM_TYPE ,'
914 ||' COMP_PLANNING_MAKE_BUY_CODE ,'
915 ||' COMP_PRIMARY_UOM_CODE ,'
916 ||' CUSTOMER_ID ,'
917 ||' CUSTOMER_SITE_ID ,'
918 ||' REGION_ID ,'
919 ||' COMP_DRP_PLANNED '
920 ||' FROM MSC_ITEM_SO_SR_LEVELS_V '
921 ||lv_plan_id;
922 EXECUTE IMMEDIATE lv_sql_stmt ;
923 COMMIT;
924
925 LOG_MESSAGE('Inserted records into MSC_ITEM_SO_SR_LEVELS');
926 END IF;
927
928
929 IF (p_global_forecast > 0 OR p_plan_id is NULL) THEN
930 lv_sql_stmt :=
931 ' INSERT /*+ APPEND */ INTO MSC_ITEM_FCST_SR_LEVELS '
932 ||' (INVENTORY_ITEM_ID ,'
933 ||' SR_INVENTORY_ITEM_ID ,'
934 ||' ORGANIZATION_ID ,'
935 ||' SR_INSTANCE_ID ,'
936 ||' PLAN_ID ,'
937 ||' ASSIGNMENT_TYPE ,'
938 ||' ASSIGNMENT_SET_ID ,'
939 ||' SOURCING_RULE_TYPE ,'
940 ||' SOURCE_ORGANIZATION_ID ,'
941 ||' SOURCE_ORG_INSTANCE_ID ,'
942 ||' VENDOR_ID ,'
943 ||' VENDOR_SITE_ID ,'
944 ||' ALLOCATION_PERCENT ,'
945 ||' RANK ,'
946 ||' EFFECTIVE_DATE ,'
947 ||' DISABLE_DATE ,'
948 ||' CATEGORY_ID ,'
949 ||' SOURCING_LEVEL ,'
950 ||' ASSIGNMENT_ID ,'
951 ||' SOURCING_RULE_ID ,'
952 ||' SOURCING_RULE_NAME ,'
953 ||' SOURCE_TYPE ,'
954 ||' SOURCE_ORG_CODE ,'
955 ||' SR_DESCRIPTION ,'
956 ||' COMPILE_DESIGNATOR ,'
957 ||' OWNING_ORG_ID ,'
958 ||' MRP_PLANNING_CODE ,'
959 ||' BOM_ITEM_TYPE ,'
960 ||' PLANNING_MAKE_BUY_CODE ,'
961 ||' PRIMARY_UOM_CODE ,'
962 ||' COMP_MRP_PLANNING_CODE ,'
963 ||' CUSTOMER_ID ,'
964 ||' CUSTOMER_SITE_ID ,'
965 ||' ZONE_ID ,'
966 ||' ASSY_DRP_PLANNED ,'
967 ||' COMP_DRP_PLANNED ) '
968 ||' SELECT '
969 ||' INVENTORY_ITEM_ID ,'
970 ||' SR_INVENTORY_ITEM_ID ,'
971 ||' ORGANIZATION_ID ,'
972 ||' SR_INSTANCE_ID ,'
973 ||' PLAN_ID ,'
974 ||' ASSIGNMENT_TYPE ,'
975 ||' ASSIGNMENT_SET_ID ,'
976 ||' SOURCING_RULE_TYPE ,'
977 ||' SOURCE_ORGANIZATION_ID ,'
978 ||' SOURCE_ORG_INSTANCE_ID ,'
979 ||' VENDOR_ID ,'
980 ||' VENDOR_SITE_ID ,'
981 ||' ALLOCATION_PERCENT ,'
982 ||' RANK ,'
983 ||' EFFECTIVE_DATE ,'
984 ||' DISABLE_DATE ,'
985 ||' CATEGORY_ID ,'
986 ||' SOURCING_LEVEL ,'
987 ||' ASSIGNMENT_ID ,'
988 ||' SOURCING_RULE_ID ,'
989 ||' SOURCING_RULE_NAME ,'
990 ||' SOURCE_TYPE ,'
991 ||' SOURCE_ORG_CODE ,'
992 ||' SR_DESCRIPTION ,'
993 ||' COMPILE_DESIGNATOR ,'
994 ||' OWNING_ORG_ID ,'
995 ||' MRP_PLANNING_CODE ,'
996 ||' BOM_ITEM_TYPE ,'
997 ||' PLANNING_MAKE_BUY_CODE ,'
998 ||' PRIMARY_UOM_CODE ,'
999 ||' COMP_MRP_PLANNING_CODE ,'
1000 ||' CUSTOMER_ID ,'
1001 ||' CUSTOMER_SITE_ID ,'
1002 ||' ZONE_ID ,'
1003 ||' ASSY_DRP_PLANNED ,'
1004 ||' COMP_DRP_PLANNED '
1005 ||' FROM MSC_ITEM_FCST_SR_LEVELS_V '
1006 ||lv_plan_id;
1007
1008 EXECUTE IMMEDIATE lv_sql_stmt ;
1009 COMMIT;
1010
1011 LOG_MESSAGE( 'Inserted records into MSC_ITEM_FCST_SR_LEVELS');
1012 END IF;
1013 p_err_code := G_SUCCESS;
1014 p_err_mesg := 'SUCCESS';
1015 EXCEPTION
1016 WHEN OTHERS THEN
1017 p_err_code := G_ERROR;
1018 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
1019 LOG_MESSAGE('Error while refresh global forecast');
1020 END refresh_snp_ods_mv_pvt;
1021
1022 PROCEDURE refresh_snapshot_pds_mv(
1023 p_err_mesg OUT NOCOPY VARCHAR2,
1024 p_plan_id in NUMBER,
1025 p_global_forecast in number default null,
1026 p_plan_so in number default null
1027 ) IS
1028 lv_p_plan_so number;
1029 lv_p_plan_type number;
1030 BEGIN
1031 /* DBMS_MVIEW.REFRESH ('MSC_ITEM_FCST_BOD_SR_LEVELS_MV','c');
1032 DBMS_MVIEW.REFRESH ('MSC_BOD_SO_SR_LEVELS_MV','c');
1033 */
1034 IF p_global_forecast = SYS_YES THEN
1035 DELETE MSC_ITEM_FCST_BOD_SR_LEVELS where plan_id = p_plan_id;
1036 COMMIT;
1037
1038 INSERT /*+APPEND*/ INTO MSC_ITEM_FCST_BOD_SR_LEVELS
1039 (
1040 INVENTORY_ITEM_ID ,
1041 SR_INVENTORY_ITEM_ID ,
1042 ORGANIZATION_ID ,
1043 SR_INSTANCE_ID ,
1044 PLAN_ID ,
1045 ASSIGNMENT_TYPE ,
1046 ASSIGNMENT_SET_ID ,
1047 SOURCING_RULE_TYPE ,
1048 SOURCE_ORGANIZATION_ID ,
1049 SOURCE_ORG_INSTANCE_ID ,
1050 VENDOR_ID ,
1051 VENDOR_SITE_ID ,
1052 ALLOCATION_PERCENT ,
1053 RANK ,
1054 SHIP_METHOD ,
1055 EFFECTIVE_DATE ,
1056 DISABLE_DATE ,
1057 CATEGORY_ID ,
1058 SOURCING_LEVEL ,
1059 ASSIGNMENT_ID ,
1060 SOURCING_RULE_ID ,
1061 SOURCING_RULE_NAME ,
1062 SOURCE_TYPE ,
1063 SOURCE_ORG_CODE ,
1064 SR_DESCRIPTION ,
1065 OWNING_ORG_ID ,
1066 CUSTOMER_ID ,
1067 CUSTOMER_SITE_ID ,
1068 ZONE_ID )
1069 SELECT
1070 INVENTORY_ITEM_ID ,
1071 SR_INVENTORY_ITEM_ID ,
1072 ORGANIZATION_ID ,
1073 SR_INSTANCE_ID ,
1074 PLAN_ID ,
1075 ASSIGNMENT_TYPE ,
1076 ASSIGNMENT_SET_ID ,
1077 SOURCING_RULE_TYPE ,
1078 SOURCE_ORGANIZATION_ID ,
1079 SOURCE_ORG_INSTANCE_ID ,
1080 VENDOR_ID ,
1081 VENDOR_SITE_ID ,
1082 ALLOCATION_PERCENT ,
1083 RANK ,
1084 SHIP_METHOD ,
1085 EFFECTIVE_DATE ,
1086 DISABLE_DATE ,
1087 CATEGORY_ID ,
1088 SOURCING_LEVEL ,
1089 ASSIGNMENT_ID ,
1090 SOURCING_RULE_ID ,
1091 SOURCING_RULE_NAME ,
1092 SOURCE_TYPE ,
1093 SOURCE_ORG_CODE ,
1094 SR_DESCRIPTION ,
1095 OWNING_ORG_ID ,
1096 CUSTOMER_ID ,
1097 CUSTOMER_SITE_ID ,
1098 ZONE_ID
1099 FROM MSC_ITEM_FCST_BOD_SR_LEVELS_V
1100 WHERE PLAN_ID = p_plan_id;
1101 COMMIT;
1102 END IF;
1103
1104
1105 IF p_plan_so > 0 THEN
1106 select decode(curr_plan_type,1,1,2,1,3,1,0)
1107 into lv_p_plan_type
1108 from msc_plans
1109 where plan_id = p_plan_id;
1110
1111 IF lv_p_plan_type = 0 THEN
1112 lv_p_plan_so := 1;
1113 ELSE
1114 select decode(DAILY_MATERIAL_CONSTRAINTS,1,1,
1115 decode(DAILY_RESOURCE_CONSTRAINTS,1,1,
1116 decode(WEEKLY_MATERIAL_CONSTRAINTS,1,1,
1117 decode(WEEKLY_RESOURCE_CONSTRAINTS,1,1,
1118 decode(PERIOD_MATERIAL_CONSTRAINTS,1,1,
1119 decode(PERIOD_RESOURCE_CONSTRAINTS,1,1,0))))))
1120 into lv_p_plan_so
1121 from msc_plans
1122 where plan_id = p_plan_id;
1123
1124 IF lv_p_plan_so = 1 THEN
1125 select decode(optimize_flag,1,1,
1126 decode(nvl(fnd_profile.value('MSO_ENABLE_DECISION_RULES'),'N'),
1127 'Y',1,'Yes',1,'YES',1,0))
1128 into lv_p_plan_so
1129 from msc_plans
1130 where plan_id = p_plan_id;
1131
1132 IF lv_p_plan_so = 1 THEN
1133 lv_p_plan_so := 1;
1134 ELSE
1135 lv_p_plan_so := 0;
1136 END IF;
1137 ELSE
1138 lv_p_plan_so := 0;
1139 END IF;
1140 END IF;
1141 ELSE
1142 lv_p_plan_so := 0;
1143 END IF;
1144
1145 IF lv_p_plan_so > 0 THEN
1146
1147 DELETE MSC_BOD_SO_SR_LEVELS where plan_id = p_plan_id;
1148 COMMIT;
1149
1150 INSERT INTO MSC_TEMP_REGION_LOCATIONS(
1151 REGION_ID ,
1152 LOCATION_ID ,
1153 LOCATION_SOURCE ,
1154 REGION_TYPE ,
1155 PARENT_REGION_FLAG ,
1156 SR_INSTANCE_ID,
1157 partner_type)
1158 SELECT REGION_ID ,
1159 LOCATION_ID ,
1160 LOCATION_SOURCE ,
1161 (10 * (10 - region_type)) REGION_TYPE ,
1162 PARENT_REGION_FLAG ,
1163 SR_INSTANCE_ID,
1164 2 partner_type
1165 FROM MSC_REGION_LOCATIONS
1166 WHERE location_source = 'HZ'
1167 and region_id is not null
1168 and region_id in ( select distinct msa.region_id
1169 from msc_sr_assignments msa,
1170 msc_plans mp
1171 where msa.assignment_type in (7,8,9)
1172 and mp.plan_id = p_plan_id
1173 and msa.assignment_set_id = mp.curr_assignment_set_id
1174 )
1175 UNION ALL
1176 select a.REGION_ID,
1177 c.LOCATION_ID,
1178 c.LOCATION_SOURCE,
1179 ((10 * (10 - a.zone_level)) + 1) REGION_TYPE,
1180 c.PARENT_REGION_FLAG,
1181 a.SR_INSTANCE_ID,
1182 2 PARTNER_TYPE
1183 FROM MSC_REGIONS a,
1184 MSC_ZONE_REGIONS b,
1185 msc_region_locations c
1186 WHERE a.region_id = b.parent_region_id
1187 AND a.region_type = 10
1188 AND a.zone_level IS NOT NULL
1189 AND a.sr_instance_id = b.sr_instance_id
1190 AND b.region_id = c.region_id
1191 and b.sr_instance_id = c.sr_instance_id
1192 and c.region_id is not null
1193 and c.location_source = 'HZ'
1194 and a.region_id in ( select distinct msa.region_id
1195 from msc_sr_assignments msa,
1196 msc_plans mp
1197 where msa.assignment_type in (7,8,9)
1198 and mp.plan_id = p_plan_id
1199 and msa.assignment_set_id = mp.curr_assignment_set_id
1200 )
1201 ;
1202
1203 DELETE MSC_SALES_ORDER_ITEMS where plan_id = p_plan_id;
1204 COMMIT;
1205
1206 INSERT /*+APPEND*/ into MSC_SALES_ORDER_ITEMS
1207 (PLAN_ID,
1208 SR_INSTANCE_ID,
1209 ORGANIZATION_ID,
1210 INVENTORY_ITEM_ID,
1211 CUSTOMER_ID,
1212 SHIP_TO_SITE_USE_ID)
1213 (
1214 SELECT DISTINCT p_plan_id PLAN_ID,
1215 so.sr_instance_id SR_INSTANCE_ID,
1216 so.organization_id ORGANIZATION_ID,
1217 so.inventory_item_id INVENTORY_ITEM_ID,
1218 so.customer_id CUSTOMER_ID,
1219 so.ship_to_site_use_id SHIP_TO_SITE_USE_ID
1220 FROM
1221 MSC_SALES_ORDERS so
1222 WHERE so.customer_id IS NOT NULL
1223 AND so.ship_to_site_use_id IS NOT NULL);
1224
1225 COMMIT;
1226
1227 INSERT /*+APPEND*/ into MSC_BOD_SO_SR_LEVELS
1228 ( INVENTORY_ITEM_ID ,
1229 ORGANIZATION_ID ,
1230 SR_INSTANCE_ID ,
1231 PLAN_ID ,
1232 ASSIGNMENT_TYPE ,
1233 ASSIGNMENT_SET_ID ,
1234 SOURCING_RULE_TYPE ,
1235 SOURCE_ORGANIZATION_ID ,
1236 SOURCE_ORG_INSTANCE_ID ,
1237 ALLOCATION_PERCENT ,
1238 RANK ,
1239 SHIP_METHOD ,
1240 EFFECTIVE_DATE ,
1241 DISABLE_DATE ,
1242 SOURCING_LEVEL ,
1243 ASSIGNMENT_ID ,
1244 SOURCING_RULE_ID ,
1245 SOURCING_RULE_NAME ,
1246 SOURCE_TYPE ,
1247 SR_DESCRIPTION ,
1248 COMPILE_DESIGNATOR ,
1249 OWNING_ORG_ID ,
1250 MRP_PLANNING_CODE ,
1251 BOM_ITEM_TYPE ,
1252 PLANNING_MAKE_BUY_CODE ,
1253 PRIMARY_UOM_CODE ,
1254 CUSTOMER_ID ,
1255 CUSTOMER_SITE_ID ,
1256 REGION_ID ,
1257 REGION_TYPE )
1258 SELECT
1259 INVENTORY_ITEM_ID ,
1260 ORGANIZATION_ID ,
1261 SR_INSTANCE_ID ,
1262 PLAN_ID ,
1263 ASSIGNMENT_TYPE ,
1264 ASSIGNMENT_SET_ID ,
1265 SOURCING_RULE_TYPE ,
1266 SOURCE_ORGANIZATION_ID ,
1267 SOURCE_ORG_INSTANCE_ID ,
1268 ALLOCATION_PERCENT ,
1269 RANK ,
1270 SHIP_METHOD ,
1271 EFFECTIVE_DATE ,
1272 DISABLE_DATE ,
1273 SOURCING_LEVEL ,
1274 ASSIGNMENT_ID ,
1275 SOURCING_RULE_ID ,
1276 SOURCING_RULE_NAME ,
1277 SOURCE_TYPE ,
1278 SR_DESCRIPTION ,
1279 COMPILE_DESIGNATOR ,
1280 OWNING_ORG_ID ,
1281 MRP_PLANNING_CODE ,
1282 BOM_ITEM_TYPE ,
1283 PLANNING_MAKE_BUY_CODE ,
1284 PRIMARY_UOM_CODE ,
1285 CUSTOMER_ID ,
1286 CUSTOMER_SITE_ID ,
1287 REGION_ID ,
1288 REGION_TYPE
1289 FROM MSC_BOD_SO_SR_LEVELS_V
1290 WHERE PLAN_ID = p_plan_id;
1291 COMMIT;
1292 END IF;
1293 EXCEPTION
1294 WHEN OTHERS THEN
1295 /* IF mrdebug = TRUE THEN
1296 LOG_MESSAGE('Error in insert_into_table :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
1297 END IF; */
1298 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
1299
1300 END refresh_snapshot_pds_mv;
1301
1302 PROCEDURE insert_into_table(
1303 p_plan_id NUMBER,
1304 p_sr_instance_id NUMBER,
1305 p_org_id NUMBER,
1306 p_bucket_index NUMBER,
1307 p_msc_plan_buckets IN OUT NOCOPY msc_plan_buckets_typ,
1308 p_err_mesg OUT NOCOPY VARCHAR2
1309 ) IS
1310 j NUMBER;
1311 k NUMBER;
1312 BEGIN
1313
1314 FOR j IN 1..p_msc_plan_buckets.bucket_index.COUNT LOOP
1315 p_msc_plan_buckets.bucket_index(j) := j+p_bucket_index;
1316 END LOOP;
1317 /*
1318 IF mrdebug = TRUE THEN
1319 NULL;
1320 FOR j IN 1..p_msc_plan_buckets.bucket_index.COUNT LOOP
1321 LOG_MESSAGE(p_msc_plan_buckets.bucket_index(j)||' '||
1322 p_msc_plan_buckets.bkt_start_date(j)||' '||
1323 p_msc_plan_buckets.bkt_end_date(j)||' '||
1324 p_msc_plan_buckets.bucket_type(j)||' '||
1325 p_msc_plan_buckets.days_in_bkt(j));
1326 END LOOP;
1327
1328 LOG_MESSAGE(p_msc_plan_buckets.bucket_index.count||' '||
1329 p_msc_plan_buckets.bkt_start_date.count||' '||
1330 p_msc_plan_buckets.bkt_end_date.count||' '||
1331 p_msc_plan_buckets.bucket_type.count||' '||
1332 p_msc_plan_buckets.days_in_bkt.count);
1333
1334 END IF;
1335 */
1336
1337 FORALL k IN 1..p_msc_plan_buckets.bucket_index.COUNT
1338 insert into msc_plan_buckets(
1339 PLAN_ID
1340 ,ORGANIZATION_ID
1341 ,SR_INSTANCE_ID
1342 ,BUCKET_INDEX
1343 ,BKT_START_DATE
1344 ,BKT_END_DATE
1345 ,BUCKET_TYPE
1346 ,DAYS_IN_BKT
1347 ,CURR_FLAG
1348 ,LAST_UPDATE_DATE
1349 ,LAST_UPDATED_BY
1350 ,CREATION_DATE
1351 ,CREATED_BY)
1352 VALUES
1353 (
1354 p_plan_id,
1355 p_org_id,
1356 p_sr_instance_id,
1357 p_msc_plan_buckets.bucket_index(k),
1358 To_date(p_msc_plan_buckets.bkt_start_date(k), 'YYYY/MM/DD'),
1359 To_date(p_msc_plan_buckets.bkt_end_date(k), 'YYYY/MM/DD')+(86399/86400),
1360 p_msc_plan_buckets.bucket_type(k),
1361 p_msc_plan_buckets.days_in_bkt(k),
1362 1,
1363 Sysdate,
1364 1,
1365 Sysdate,
1366 1);
1367
1368 EXCEPTION
1369 WHEN OTHERS THEN
1370 /* IF mrdebug = TRUE THEN
1371 LOG_MESSAGE('Error in insert_into_table :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
1372 END IF; */
1373 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
1374
1375 END insert_into_table;
1376
1377 PROCEDURE get_cutoff_dates(
1378 p_plan_id IN NUMBER,
1379 p_err_mesg OUT NOCOPY VARCHAR2,
1380 p_min_cutoff_date OUT NOCOPY number,
1381 p_hour_cutoff_date OUT NOCOPY number,
1382 p_daily_cutoff_date OUT NOCOPY number,
1383 p_weekly_cutoff_date OUT NOCOPY number,
1384 p_period_cutoff_date OUT NOCOPY number,
1385 p_min_cutoff_bucket OUT NOCOPY number,
1386 p_hour_cutoff_bucket OUT NOCOPY number,
1387 p_daily_cutoff_bucket OUT NOCOPY number,
1388 p_weekly_cutoff_bucket OUT NOCOPY number,
1389 p_period_cutoff_bucket OUT NOCOPY number
1390 ) IS
1391 l_daily_cutoff_date DATE;
1392 l_weekly_cutoff_date DATE;
1393 l_period_cutoff_date DATE;
1394 first_date DATE;
1395 BEGIN
1396
1397 SELECT Nvl(min_cutoff_bucket,0), Nvl(hour_cutoff_bucket,0)
1398 INTO p_min_cutoff_bucket,p_hour_cutoff_bucket
1399 FROM msc_plans
1400 WHERE plan_id = p_plan_id;
1401
1402 --LOG_MESSAGE(' 1 : '||p_min_cutoff_bucket||' '||p_hour_cutoff_bucket);
1403
1404 SELECT NVL(MIN(bkt_start_date), TRUNC(SYSDATE))
1405 INTO first_date
1406 FROM msc_plan_buckets
1407 WHERE plan_id = p_plan_id
1408 AND bucket_type = 1;
1409
1410 --LOG_MESSAGE(' 2 : '||first_date);
1411
1412 IF p_min_cutoff_bucket <> 0 THEN
1413 p_min_cutoff_date := To_char(first_date+(p_min_cutoff_bucket-1),'J');
1414 --LOG_MESSAGE(' 3 : '||p_min_cutoff_date);
1415 ELSE
1416 p_min_cutoff_date := To_char(first_date,'J');
1417 --LOG_MESSAGE(' 4 : '||p_min_cutoff_date);
1418 END IF;
1419
1420 IF p_hour_cutoff_bucket <> 0 THEN
1421 IF p_min_cutoff_bucket <> 0 THEN
1422 p_hour_cutoff_date := p_min_cutoff_date+p_hour_cutoff_bucket;
1423 -- changed after finding the date.
1424 p_hour_cutoff_bucket := p_min_cutoff_bucket + p_hour_cutoff_bucket;
1425 --LOG_MESSAGE(' 5 : '||p_hour_cutoff_bucket||' '||p_hour_cutoff_date);
1426 ELSE
1427 p_hour_cutoff_date := To_char(first_date+p_hour_cutoff_bucket -1,'J');
1428 --LOG_MESSAGE(' 6 : '||p_hour_cutoff_date);
1429 END IF;
1430 ELSE
1431 p_hour_cutoff_bucket := p_min_cutoff_bucket;
1432 p_hour_cutoff_date := p_min_cutoff_date;
1433 --LOG_MESSAGE(' 7 : '||p_hour_cutoff_bucket||' '||p_hour_cutoff_date);
1434 END IF;
1435
1436
1437 SELECT NVL(max(bkt_end_date), TRUNC(SYSDATE)),
1438 NVL(max(bucket_index), 0)
1439 INTO l_daily_cutoff_date, p_daily_cutoff_bucket
1440 from msc_plan_buckets
1441 where plan_id = p_plan_id
1442 AND bucket_type = 1;
1443
1444 if p_daily_cutoff_bucket = 0
1445 then
1446 select nvl(curr_start_date-1, TRUNC(SYSDATE-1))
1447 INTO l_daily_cutoff_date
1448 from msc_plans
1449 where plan_id = p_plan_id;
1450 end if;
1451
1452
1453
1454 --LOG_MESSAGE(' 8 : '||l_daily_cutoff_date||' '||p_daily_cutoff_bucket);
1455
1456 SELECT
1457 Nvl(max(bkt_end_date),l_daily_cutoff_date),
1458 Nvl(max(bucket_index), p_daily_cutoff_bucket)
1459 INTO l_weekly_cutoff_date, p_weekly_cutoff_bucket
1460 from msc_plan_buckets
1461 where plan_id = p_plan_id
1462 AND bucket_type = 2;
1463
1464 --LOG_MESSAGE(' 9 : '||l_weekly_cutoff_date||' '||p_weekly_cutoff_bucket);
1465
1466 SELECT
1467 Nvl(max(bkt_end_date),l_weekly_cutoff_date),
1468 Nvl(max(bucket_index), p_weekly_cutoff_bucket)
1469 INTO l_period_cutoff_date, p_period_cutoff_bucket
1470 from msc_plan_buckets
1471 where plan_id = p_plan_id
1472 AND bucket_type = 3;
1473
1474 --LOG_MESSAGE(' 10 : '||l_period_cutoff_date||' '||p_period_cutoff_bucket);
1475
1476 p_daily_cutoff_date := To_char(l_daily_cutoff_date,'J');
1477 p_weekly_cutoff_date := To_char(l_weekly_cutoff_date,'J');
1478 p_period_cutoff_date := To_char(l_period_cutoff_date,'J');
1479
1480 --LOG_MESSAGE(' 10 : '||p_daily_cutoff_date||' '||p_weekly_cutoff_date||' '||p_period_cutoff_date);
1481
1482 EXCEPTION
1483 WHEN OTHERS THEN
1484 -- IF mrdebug = TRUE THEN
1485 LOG_MESSAGE('Error in get_cutoff_dates :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
1486 -- END IF;
1487 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
1488
1489 END get_cutoff_dates;
1490
1491 PROCEDURE form_get_bucket_cutoff_dates
1492 (
1493 p_plan_id IN NUMBER,
1494 p_org_id IN NUMBER,
1495 p_instance_id IN NUMBER,
1496 p_min_cutoff_bucket IN number,
1497 p_hour_cutoff_bucket IN number,
1498 p_daily_cutoff_bucket IN number,
1499 p_weekly_cutoff_bucket IN number,
1500 p_period_cutoff_bucket IN number,
1501 p_plan_completion_date OUT NOCOPY DATE,
1502 p_err_mesg OUT NOCOPY VARCHAR2
1503 ) IS
1504 l_daily_cutoff_date DATE;
1505 l_weekly_cutoff_date DATE;
1506 l_period_cutoff_date DATE;
1507 BEGIN
1508
1509 get_bucket_cutoff_dates(
1510 p_plan_id =>p_plan_id,
1511 p_org_id =>p_org_id,
1512 p_instance_id =>p_instance_id,
1513 p_plan_start_date =>Sysdate,
1514 p_plan_completion_date =>NULL, -- not used
1515 p_min_cutoff_bucket =>p_daily_cutoff_bucket,
1516 p_hour_cutoff_bucket =>p_daily_cutoff_bucket,
1517 p_daily_cutoff_bucket =>p_daily_cutoff_bucket,
1518 p_weekly_cutoff_bucket =>p_weekly_cutoff_bucket,
1519 p_period_cutoff_bucket =>p_period_cutoff_bucket,
1520 p_min_cutoff_date =>l_daily_cutoff_date,
1521 p_hour_cutoff_date =>l_daily_cutoff_date,
1522 p_daily_cutoff_date =>l_daily_cutoff_date,
1523 p_weekly_cutoff_date =>l_weekly_cutoff_date,
1524 p_period_cutoff_date =>l_period_cutoff_date,
1525 p_err_mesg =>p_err_mesg
1526 );
1527
1528 IF l_period_cutoff_date IS NOT NULL THEN
1529 p_plan_completion_date := l_period_cutoff_date;
1530 ELSIF l_weekly_cutoff_date IS NOT NULL THEN
1531 p_plan_completion_date := l_weekly_cutoff_date;
1532 ELSE
1533 p_plan_completion_date := l_daily_cutoff_date;
1534 END IF;
1535
1536 RETURN;
1537
1538 END form_get_bucket_cutoff_dates;
1539
1540 FUNCTION get_validation_org_id (p_sr_instance_id in NUMBER)
1541 return NUMBER IS
1542 l_org_id NUMBER;
1543 BEGIN
1544 select nvl(validation_org_id,-1) into l_org_id
1545 from msc_apps_instances
1546 where instance_id = p_sr_instance_id;
1547 return l_org_id;
1548 EXCEPTION WHEN OTHERS THEN
1549 return -1;
1550 END get_validation_org_id;
1551
1552 FUNCTION get_column_expression (p_column_name in VARCHAR2,
1553 p_index_owner in VARCHAR2,
1554 p_table_owner in VARCHAR2,
1555 p_index_name in VARCHAR2,
1556 p_table_name in VARCHAR2,
1557 p_column_position in number)
1558 return VARCHAR2 IS
1559 l_retval VARCHAR2(2000);
1560 l_longvar long;
1561 BEGIN
1562 select column_expression into l_longvar
1563 from all_ind_expressions
1564 where table_owner = p_table_owner
1565 and index_owner = p_index_owner
1566 and table_name = p_table_name
1567 and index_name = p_index_name
1568 and column_position = p_column_position;
1569 return (substr(l_longvar, 1, 2000));
1570 EXCEPTION WHEN NO_DATA_FOUND THEN
1571 return p_column_name;
1572
1573 END get_column_expression;
1574 /*
1575 FUNCTION get_ss_date (p_calendar_code VARCHAR2,
1576 p_plan_id IN NUMBER,
1577 p_owning_org_id IN NUMBER,
1578 p_owning_instance_id IN NUMBER,
1579 p_ss_org_id IN NUMBER,
1580 p_ss_instance_id IN NUMBER,
1581 p_ss_date IN NUMBER,
1582 p_plan_type IN NUMBER)
1583 return NUMBER IS
1584 l_bucket_type NUMBER;
1585 l_bucket_index NUMBER;
1586 l_bkt_end_date NUMBER;
1587 l_bkt_end_date1 NUMBER;
1588 l_bkt_end_date3 NUMBER;
1589 l_calendar_date NUMBER;
1590
1591 BEGIN
1592 IF ( p_owning_org_id IS NULL OR
1593 p_owning_instance_id IS NULL OR
1594 p_ss_date IS NULL OR
1595 p_calendar_code IS NULL ) THEN
1596 return null;
1597 END IF;
1598
1599
1600 BEGIN
1601 IF (p_plan_type <> 4 AND p_plan_type <> 9) THEN
1602 select own_org_bkt.bucket_type,
1603 own_org_bkt.bucket_index,
1604 to_number(to_char(own_org_bkt.bkt_end_date,'J')),
1605 to_number(to_char(nvl(org_bkt.bkt_end_date,sysdate),'J'))
1606 into l_bucket_type,
1607 l_bucket_index,
1608 l_bkt_end_date,
1609 l_bkt_end_date1
1610 from msc_plan_buckets own_org_bkt,
1611 msc_plan_buckets org_bkt
1612 where own_org_bkt.plan_id = p_plan_id
1613 and own_org_bkt.organization_id = p_owning_org_id
1614 and own_org_bkt.sr_instance_id = p_owning_instance_id
1615 and own_org_bkt.curr_flag = 1
1616 and ((own_org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
1617 own_org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
1618 (own_org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
1619 own_org_bkt.bucket_index = 1))
1620 and org_bkt.plan_id(+) = own_org_bkt.plan_id
1621 and org_bkt.organization_id(+) = p_owning_org_id
1622 and org_bkt.sr_instance_id(+) = p_owning_instance_id
1623 and org_bkt.curr_flag(+) = 1
1624 and org_bkt.bucket_index(+) = own_org_bkt.bucket_index-1;
1625
1626 IF (l_bucket_type = 1 or l_bucket_index = 1) THEN
1627 l_bkt_end_date3 := l_bkt_end_date;
1628 ELSE
1629 l_bkt_end_date3 := l_bkt_end_date1;
1630 END IF;
1631
1632 select to_number(to_char(cal2.calendar_date,'J'))
1633 into l_calendar_date
1634 from msc_calendar_dates cal1,
1635 msc_calendar_dates cal2
1636 where cal1.calendar_code = p_calendar_code
1637 and cal1.calendar_date = to_date(l_bkt_end_date3,'J')
1638 and cal1.exception_set_id = -1
1639 and cal1.sr_instance_id = p_ss_instance_id
1640 and cal2.seq_num = cal1.prior_seq_num
1641 and cal2.calendar_code = cal1.calendar_code
1642 and cal2.sr_instance_id = cal1.sr_instance_id
1643 and cal2.exception_set_id = -1;
1644 ELSE
1645 select to_number(to_char(cal2.calendar_date,'J'))
1646 into l_calendar_date
1647 from msc_plan_buckets org_bkt,
1648 msc_calendar_dates cal1,
1649 msc_calendar_dates cal2
1650 where org_bkt.plan_id = p_plan_id
1651 and org_bkt.organization_id = p_owning_org_id
1652 and org_bkt.sr_instance_id = p_owning_instance_id
1653 and org_bkt.curr_flag = 1
1654 and ((org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
1655 org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
1656 (org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
1657 org_bkt.bucket_index = 1))
1658 and cal1.calendar_code = p_calendar_code
1659 and cal1.calendar_date = org_bkt.bkt_start_date
1660 and cal1.exception_set_id = -1
1661 and cal1.sr_instance_id = p_ss_instance_id
1662 and cal2.seq_num = cal1.next_seq_num
1663 and cal2.calendar_code = cal1.calendar_code
1664 and cal2.sr_instance_id = cal1.sr_instance_id
1665 and cal2.exception_set_id = -1;
1666 END IF;
1667
1668
1669 return(l_calendar_date);
1670
1671 EXCEPTION WHEN NO_DATA_FOUND THEN
1672 return null;
1673 END;
1674
1675
1676
1677 END get_ss_date;
1678 */
1679 --modified body of msc_snapshot_pk.get_ss_date for Bug 5610482
1680 FUNCTION GET_SS_DATE (p_calendar_code VARCHAR2,
1681 p_plan_id IN NUMBER,
1682 p_owning_org_id IN NUMBER,
1683 p_owning_instance_id IN NUMBER,
1684 p_ss_org_id IN NUMBER,
1685 p_ss_instance_id IN NUMBER,
1686 p_ss_date IN NUMBER,
1687 p_plan_type IN NUMBER)
1688 return NUMBER IS
1689 l_bucket_type NUMBER;
1690 l_bucket_index NUMBER;
1691 l_bkt_end_date NUMBER;
1692 l_bkt_end_date1 NUMBER;
1693 l_bkt_end_date3 NUMBER;
1694 l_calendar_date NUMBER;
1695 BEGIN
1696 IF ( p_owning_org_id IS NULL OR
1697 p_owning_instance_id IS NULL OR
1698 p_ss_date IS NULL OR
1699 p_calendar_code IS NULL ) THEN
1700 return null;
1701 END IF;
1702
1703 BEGIN
1704 IF (p_plan_type <> 4 AND p_plan_type <> 9) THEN
1705 select to_number(to_char(cal1.prior_date,'J'))
1706 into l_calendar_date
1707 from msc_plan_buckets own_org_bkt,
1708 msc_plan_buckets org_bkt,
1709 msc_calendar_dates cal1
1710 where own_org_bkt.plan_id = p_plan_id
1711 and own_org_bkt.organization_id = p_owning_org_id
1712 and own_org_bkt.sr_instance_id = p_owning_instance_id
1713 and own_org_bkt.curr_flag = 1
1714 and ((own_org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
1715 own_org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
1716 (own_org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
1717 own_org_bkt.bucket_index = 1))
1718 and org_bkt.plan_id(+) = own_org_bkt.plan_id
1719 and org_bkt.organization_id(+) = p_owning_org_id
1720 and org_bkt.sr_instance_id(+) = p_owning_instance_id
1721 and org_bkt.curr_flag(+) = 1
1722 and org_bkt.bucket_index(+) = own_org_bkt.bucket_index-1
1723 and cal1.calendar_code = p_calendar_code
1724 and cal1.calendar_date = decode(own_org_bkt.bucket_type,1,trunc(own_org_bkt.bkt_end_date),decode(own_org_bkt.bucket_index,1,trunc(own_org_bkt.bkt_end_date),nvl(trunc(org_bkt.bkt_end_date),trunc(sysdate))))
1725 and cal1.exception_set_id = -1
1726 and cal1.sr_instance_id = p_ss_instance_id ;
1727 ELSE
1728 select to_number(to_char(cal1.next_date,'J'))
1729 into l_calendar_date
1730 from msc_plan_buckets org_bkt,
1731 msc_calendar_dates cal1
1732 where org_bkt.plan_id = p_plan_id
1733 and org_bkt.organization_id = p_owning_org_id
1734 and org_bkt.sr_instance_id = p_owning_instance_id
1735 and org_bkt.curr_flag = 1
1736 and ((org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
1737 org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
1738 (org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
1739 org_bkt.bucket_index = 1))
1740 and cal1.calendar_code = p_calendar_code
1741 and cal1.calendar_date = org_bkt.bkt_start_date
1742 and cal1.exception_set_id = -1
1743 and cal1.sr_instance_id = p_ss_instance_id;
1744 END IF;
1745
1746 return(l_calendar_date);
1747
1748 EXCEPTION WHEN NO_DATA_FOUND THEN
1749 return null;
1750 END;
1751 END;
1752
1753
1754
1755 FUNCTION get_op_leadtime_percent(p_plan_id IN NUMBER,
1756 p_routing_seq_id IN NUMBER,
1757 p_sr_instance_id IN NUMBER,
1758 p_op_seq_num IN NUMBER)
1759 return NUMBER IS
1760 l_op_leadtime_percent NUMBER;
1761
1762 BEGIN
1763 BEGIN
1764 select nvl(mro.operation_lead_time_percent, 0.0)
1765 into l_op_leadtime_percent
1766 from
1767 msc_routing_operations mro
1768 where mro.plan_id = p_plan_id
1769 and mro.sr_instance_id = p_sr_instance_id
1770 and mro.routing_sequence_id = p_routing_seq_id
1771 and mro.operation_seq_num = p_op_seq_num
1772 and mro.effectivity_date <= sysdate
1773 and (mro.disable_date >= sysdate or
1774 mro.disable_date is NULL);
1775
1776 return(l_op_leadtime_percent);
1777
1778 EXCEPTION WHEN OTHERS THEN
1779 return 0.0;
1780 END;
1781
1782 END get_op_leadtime_percent;
1783
1784
1785
1786 procedure calculate_start_date(p_org_id IN NUMBER,
1787 p_sr_instance_id IN NUMBER,
1788 p_plan_start_date IN DATE,
1789 p_daily_cutoff_bucket IN NUMBER,
1790 p_weekly_cutoff_bucket IN NUMBER,
1791 p_period_cutoff_bucket IN NUMBER,
1792 p_daily_start_date OUT NOCOPY DATE,
1793 p_weekly_start_date OUT NOCOPY DATE,
1794 p_period_start_date OUT NOCOPY DATE,
1795 p_curr_cutoff_date OUT NOCOPY DATE) IS
1796 v_min_cutoff_date date;
1797 v_hour_cutoff_date date;
1798 v_daily_cutoff_date date;
1799 v_weekly_cutoff_date date;
1800 v_period_cutoff_date date;
1801 v_err_mesg varchar2(2000);
1802 v_daily_cutoff_bucket number;
1803 v_weekly_cutoff_bucket number;
1804 v_period_cutoff_bucket number;
1805
1806
1807 BEGIN
1808
1809 get_bucket_cutoff_dates(
1810 0, -- p_plan_id IN NUMBER,
1811 p_org_id, -- p_org_id IN NUMBER,
1812 p_sr_instance_id, -- IN NUMBER,
1813 p_plan_start_date, -- IN DATE,
1814 to_date(null), --p_plan_completion_date IN DATE,
1815 0, --p_min_cutoff_bucket IN number,
1816 0, --p_hour_cutoff_bucket IN number,
1817 p_daily_cutoff_bucket, -- IN number,
1818 p_weekly_cutoff_bucket, -- IN number,
1819 p_period_cutoff_bucket, -- IN number,
1820 v_min_cutoff_date, -- OUT DATE,
1821 v_hour_cutoff_date, -- OUT DATE,
1822 v_daily_cutoff_date, -- OUT DATE,
1823 v_weekly_cutoff_date, -- OUT DATE,
1824 v_period_cutoff_date, -- OUT DATE
1825 v_err_mesg) ;
1826
1827 p_daily_start_date := p_plan_start_date;
1828
1829 select min(cal.week_start_date)
1830 into p_weekly_start_date
1831 from msc_cal_week_start_dates cal,
1832 msc_trading_partners tp
1833 where cal.exception_set_id = tp.calendar_exception_set_id
1834 and cal.calendar_code = tp.calendar_code
1835 and cal.week_start_date >= trunc(v_daily_cutoff_date)
1836 and cal.sr_instance_id = tp.sr_instance_id
1837 and tp.sr_tp_id = p_org_id
1838 and tp.partner_type = 3;
1839
1840 if p_daily_cutoff_bucket = 0 and p_weekly_cutoff_bucket = 0 then
1841 p_weekly_start_date := p_daily_start_date;
1842 end if;
1843
1844 select min(cal.period_start_date)
1845 into p_period_start_date
1846 from msc_period_start_dates cal,
1847 msc_trading_partners tp
1848 where cal.exception_set_id = tp.calendar_exception_set_id
1849 and cal.calendar_code = tp.calendar_code
1850 and cal.period_start_date >= nvl(trunc(v_weekly_cutoff_date),
1851 trunc(v_daily_cutoff_date))
1852 and cal.sr_instance_id = tp.sr_instance_id
1853 and tp.sr_tp_id = p_org_id
1854 and tp.partner_type = 3;
1855
1856 if v_weekly_cutoff_bucket = 0 and v_period_cutoff_bucket = 0 then
1857 p_period_start_date := p_weekly_start_date;
1858 end if;
1859
1860 if v_period_cutoff_date is not null then
1861 p_curr_cutoff_date := v_period_cutoff_date;
1862 elsif v_weekly_cutoff_date is not null then
1863 p_curr_cutoff_date := v_weekly_cutoff_date;
1864 elsif v_daily_cutoff_date is not null then
1865 p_curr_cutoff_date := v_daily_cutoff_date;
1866 end if;
1867
1868 end calculate_start_date;
1869
1870 function calculate_start_date1(p_org_id IN NUMBER,
1871 p_sr_instance_id IN NUMBER,
1872 p_plan_start_date IN DATE,
1873 p_daily_cutoff_bucket IN NUMBER,
1874 p_weekly_cutoff_bucket IN NUMBER,
1875 p_period_cutoff_bucket IN NUMBER,
1876 P_start_date_bucket IN NUMBER) return Date is
1877 v_daily_start_date date;
1878 v_weekly_start_date date;
1879 v_period_start_date date;
1880 v_curr_cutoff_date date;
1881
1882 begin
1883 calculate_start_date(p_org_id ,
1884 p_sr_instance_id ,
1885 p_plan_start_date ,
1886 p_daily_cutoff_bucket ,
1887 p_weekly_cutoff_bucket ,
1888 p_period_cutoff_bucket ,
1889 v_daily_start_date ,
1890 v_weekly_start_date ,
1891 v_period_start_date ,
1892 v_curr_cutoff_date ) ;
1893
1894 if p_start_date_bucket = 1 then
1895 return v_daily_start_date;
1896 elsif p_start_date_bucket = 2 then
1897 return v_weekly_start_date;
1898 elsif p_start_date_bucket = 3 then
1899 return v_period_start_date;
1900 end if;
1901
1902 exception when others then
1903 return to_date(null);
1904
1905 end calculate_start_date1;
1906
1907 PROCEDURE create_xml_schema(p_plan_id IN NUMBER
1908 ) IS
1909 v_xml XMLTYPE;
1910 v_blob BLOB;
1911 v_data_length NUMBER;
1912 v_offset NUMBER DEFAULT 1;
1913 v_chunk CONSTANT NUMBER DEFAULT 4000;
1914 fh UTL_FILE.file_type;
1915 l_create_stmt VARCHAR2(8000);
1916 l_dest_dir VARCHAR2(400);
1917 BEGIN
1918 /*
1919 select 'MSLD_XML_FILE_PATH_' || p_plan_id
1920 INTO l_dest_dir
1921 from dual;
1922
1923 Select xmlelement("MBPSnapshotSchema",
1924 xmlattributes('12.2.0' "version"),
1925 xmlagg(
1926 xmlelement("FlatFile",
1927 xmlattributes(mxc1.file_name as "name"),
1928 (Select xmlagg(
1929 xmlelement("Column",
1930 xmlattributes(mxc2.column_name as "name",
1931 decode(mxc2.column_type ,
1932 1, 'NUMBER',
1933 2, 'DOUBLE',
1934 3, 'STRING',
1935 4, 'DATE',
1936 5, 'TIME',
1937 6, 'DATE-TIME',
1938 7, 'BOOLEAN',
1939 'INVALID') as "type")
1940 )order by mxc2.order_num
1941 )
1942 from msc_xml_schema mxc2
1943 WHERE mxc2.plan_id =p_plan_id
1944 and mxc2.file_name=mxc1.file_name
1945 and upper(mxc2.column_name) <> 'PLAN_ID'
1946 ) as "Columns",
1947 xmlelement("PrimaryKey",
1948 (Select xmlagg(
1949 xmlelement("Column",
1950 xmlattributes(mxc3.column_name as "name")
1951 )order by mxc3.order_num
1952 )
1953 from msc_xml_schema mxc3
1954 WHERE mxc3.plan_id = p_plan_id
1955 and mxc3.file_name=mxc1.file_name
1956 and mxc3.is_primary_key = 1
1957 ) /*as "Columns"
1958 ) /*xmlelement
1959 ) /*xmlelement
1960 ) /*xmlagg
1961 ) /*xmlelement
1962 INTO v_xml
1963 FROM (select unique file_name
1964 from msc_xml_schema
1965 where plan_id = p_plan_id
1966 order by file_name
1967 ) mxc1;
1968
1969 v_blob := v_xml.getblobval (1);
1970 v_data_length := DBMS_LOB.getlength (v_blob);
1971 fh := UTL_FILE.fopen (l_dest_dir, 'MSLD_XML_SCHEMA.xml', 'wb', v_chunk);
1972
1973 LOOP
1974 EXIT WHEN v_offset > v_data_length;
1975 UTL_FILE.put_raw (fh, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset), TRUE);
1976 v_offset := v_offset + v_chunk;
1977 END LOOP;
1978
1979 UTL_FILE.fclose (fh);
1980
1981 EXCEPTION
1982 WHEN NO_DATA_FOUND
1983 THEN
1984 NULL;
1985 */
1986 NULL;
1987
1988 END create_xml_schema;
1989
1990 -- Snapshot the forecast metrics for auto release
1991
1992 PROCEDURE auto_release_forecast_metrics
1993 ( p_plan_id in NUMBER,
1994 p_err_mesg OUT NOCOPY VARCHAR2 )
1995 IS
1996
1997 lv_share_partition VARCHAR2(1);
1998 lv_parallel_run VARCHAR2(1);
1999 lv_part_name VARCHAR2(50);
2000 lv_sql_stmt VARCHAR2(2048);
2001 lv_retval boolean;
2002 lv_dummy1 varchar2(32);
2003 lv_dummy2 varchar2(32);
2004 lv_msc_schema varchar2(32);
2005
2006 BEGIN --Begin procedure
2007
2008 LOG_MESSAGE('Begin Snapshot: Auto Release Forecast Metrics ');
2009
2010 --Fetch the profile values
2011 lv_share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
2012 lv_parallel_run := fnd_profile.value('MSC_PARALLEL_PLAN_RUN');
2013 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'MSC', lv_dummy1, lv_dummy2, lv_msc_schema);
2014
2015 -- If share partitions or parallel run, use the delete coomand
2016 -- Else use the truncate partition
2017 IF (lv_share_partition = 'Y' or lv_parallel_run = 'Y') THEN
2018
2019 lv_sql_stmt :=
2020 'DELETE FROM MSC_DMD_SCN_METRICS WHERE PLAN_ID = ' || p_plan_id;
2021
2022 ELSE
2023
2024 lv_part_name := 'DMD_SCN_METRICS_' || p_plan_id;
2025
2026 lv_sql_stmt :=
2027 'ALTER TABLE '||lv_msc_schema||'.MSC_DMD_SCN_METRICS TRUNCATE PARTITION ' || lv_part_name;
2028
2029 END IF;
2030
2031 EXECUTE IMMEDIATE lv_sql_stmt;
2032
2033 INSERT INTO MSC_DMD_SCN_METRICS
2034 (
2035 PLAN_ID,
2036 SCENARIO_ID,
2037 INVENTORY_ITEM_ID,
2038 ORGANIZATION_ID,
2039 SR_INSTANCE_ID,
2040 MAPE_IN_SAMPLE,
2041 MAPE_OUT_SAMPLE,
2042 FORECAST_VOLATILITY,
2043 AVG_DEMAND,
2044 CREATED_BY,
2045 CREATION_DATE,
2046 LAST_UPDATE_DATE,
2047 LAST_UPDATED_BY,
2048 LAST_UPDATE_LOGIN
2049 )
2050 (
2051 SELECT p_plan_id PLAN_ID,
2052 mdsm.SCENARIO_ID SCENARIO_ID,
2053 mdsm.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,
2054 mdsm.ORGANIZATION_ID ORGANIZATION_ID,
2055 mdsm.SR_INSTANCE_ID SR_INSTANCE_ID,
2056 mdsm.MAPE_IN_SAMPLE MAPE_IN_SAMPLE,
2057 mdsm.MAPE_OUT_SAMPLE MAPE_OUT_SAMPLE,
2058 mdsm.FORECAST_VOLATILITY FORCAST_VOLATILITY,
2059 mdsm.AVG_DEMAND AVG_DEMAND,
2060 mdsm.CREATED_BY CREATED_BY,
2061 mdsm.CREATION_DATE CREATION_DATE,
2062 mdsm.LAST_UPDATE_DATE LAST_UPDATE_DATE,
2063 mdsm.LAST_UPDATED_BY LAST_UPDATED_BY,
2064 mdsm.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
2065 FROM
2066 MSC_DMD_SCN_METRICS mdsm,
2067 MSC_PLAN_SCHEDULES mpsv
2068 WHERE
2069 -1 = mdsm.plan_id
2070 AND mpsv.input_schedule_id = mdsm.scenario_id
2071 AND mpsv.organization_id = mdsm.organization_id
2072 AND mpsv.sr_instance_id = mdsm.sr_instance_id
2073 AND mpsv.designator_type = 7 /*SCN_DESIGNATOR_TYPE*/
2074 AND mpsv.plan_id = p_plan_id );
2075
2076 COMMIT;
2077
2078 LOG_MESSAGE('End Snapshot: Auto Release Forecast Metrics ');
2079
2080
2081 EXCEPTION WHEN OTHERS then
2082 LOG_MESSAGE('RETCODE -'||SQLCODE);
2083 LOG_MESSAGE(SQLERRM);
2084 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
2085
2086 END auto_release_forecast_metrics; -- END Procedure
2087
2088 PROCEDURE update_category_info(
2089 p_err_mesg OUT NOCOPY VARCHAR2,
2090 p_plan_id in NUMBER
2091 ) IS
2092 lv_fetchComplete Boolean;
2093
2094 TYPE NumTblTyp IS TABLE OF NUMBER;
2095
2096 lb_inv_item_id NumTblTyp;
2097 lb_org_id NumTblTyp;
2098 lb_sr_category_id NumTblTyp;
2099 lb_sr_instance_id NumTblTyp;
2100
2101 ln_rows_to_fetch Number :=
2102 nvl(TO_NUMBER(FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
2103 lv_plan_partition_exists Number := 0;
2104
2105 CURSOR items_category_update
2106 IS
2107 select items.inventory_item_id,
2108 cat.organization_id,
2109 cat.sr_instance_id,
2110 cat.sr_category_id
2111 from msc_system_items items, msc_item_categories cat, msc_plans plans
2112 where cat.CATEGORY_SET_ID = plans.CATEGORY_SET_ID
2113 and cat.inventory_item_id = items.inventory_item_id
2114 and cat.sr_instance_id = items.sr_instance_id
2115 and cat.organization_id = items.organization_id
2116 and items.plan_id = plans.plan_id
2117 and plans.plan_id = p_plan_id;
2118
2119 BEGIN
2120
2121 SELECT count(*)
2122 INTO lv_plan_partition_exists
2123 FROM MSC_PLAN_PARTITIONS
2124 WHERE plan_id = p_plan_id;
2125
2126 IF (lv_plan_partition_exists > 0) THEN
2127
2128 LOG_MESSAGE('Analysing MSC_SYSTEM_ITEMS plan partition ');
2129
2130 msc_analyse_tables_pk.analyse_table( 'MSC_SYSTEM_ITEMS', -1, p_plan_id);
2131
2132 ELSE
2133
2134 -- LOG_MESSAGE('Analysing MSC_SYSTEM_ITEMS table ');
2135
2136 -- msc_analyse_tables_pk.analyse_table( 'MSC_SYSTEM_ITEMS' );
2137
2138 null;
2139
2140 END IF;
2141
2142 LOG_MESSAGE('Updating the Items Table ');
2143
2144 lv_fetchComplete := FALSE;
2145
2146 OPEN items_category_update;
2147 IF (items_category_update%ISOPEN) THEN
2148 LOOP
2149 IF (lv_fetchComplete) THEN
2150 EXIT;
2151 END IF;
2152
2153 FETCH items_category_update
2154 BULK COLLECT
2155 INTO lb_inv_item_id,
2156 lb_org_id,
2157 lb_sr_instance_id,
2158 lb_sr_category_id
2159 LIMIT ln_rows_to_fetch;
2160
2161 EXIT WHEN lb_inv_item_id.count = 0;
2162
2163 IF (items_category_update%NOTFOUND) THEN
2164 lv_fetchComplete := TRUE;
2165 END IF;
2166
2167 FORALL j IN lb_inv_item_id.FIRST..lb_inv_item_id.LAST
2168 UPDATE MSC_SYSTEM_ITEMS
2169 SET sr_category_id = lb_sr_category_id(j)
2170 WHERE sr_instance_id = lb_sr_instance_id(j)
2171 AND inventory_item_id = lb_inv_item_id(j)
2172 AND organization_id = lb_org_id(j)
2173 AND plan_id = p_plan_id;
2174
2175 COMMIT;
2176
2177 END LOOP;
2178
2179 END IF;
2180
2181 IF (items_category_update%ISOPEN) THEN
2182 CLOSE items_category_update;
2183 END IF;
2184
2185 COMMIT;
2186
2187 LOG_MESSAGE('Completed updating the Items Table ');
2188 EXCEPTION WHEN OTHERS then
2189 LOG_MESSAGE('RETCODE -'||SQLCODE);
2190 LOG_MESSAGE(SQLERRM);
2191 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
2192 END update_category_info;
2193
2194
2195 PROCEDURE update_items_info(
2196 p_err_mesg OUT NOCOPY VARCHAR2,
2197 p_plan_id in NUMBER
2198 ) IS
2199 lv_fetchComplete Boolean;
2200
2201 TYPE NumTblTyp IS TABLE OF NUMBER;
2202 TYPE Char1TblTyp IS TABLE OF VARCHAR2(250);
2203 TYPE Char2TblTyp IS TABLE OF VARCHAR2(240);
2204 TYPE Char3TblTyp IS TABLE OF VARCHAR2(10);
2205 TYPE Char4TblTyp IS TABLE OF VARCHAR2(3);
2206
2207 lb_inv_item_id NumTblTyp;
2208 lb_org_id NumTblTyp;
2209 lb_val_org_id NumTblTyp;
2210 lb_sr_instance_id NumTblTyp;
2211
2212 lb_item_name Char1TblTyp;
2213 lb_description Char2TblTyp;
2214 lb_buyer_name Char2TblTyp;
2215 lb_planner_code Char3TblTyp;
2216 lb_plng_excp_set Char3TblTyp;
2217 lb_revision Char4TblTyp;
2218
2219 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
2220 lv_plan_partition_exists Number := 0;
2221
2222 CURSOR items_for_update
2223 IS
2224 SELECT /*+ ORDERED USE_NL(ODS, MSC_SYSTEM_ITEMS_U1) */ ODS.INVENTORY_ITEM_ID,
2225 PDS.ORGANIZATION_ID,
2226 ODS.SR_INSTANCE_ID,
2227 ODS.ITEM_NAME,
2228 ODS.DESCRIPTION,
2229 ODS.BUYER_NAME,
2230 ODS.PLANNER_CODE,
2231 ODS.PLANNING_EXCEPTION_SET,
2232 ODS.REVISION,
2233 -1
2234 FROM MSC_SYSTEM_ITEMS PDS,
2235 MSC_SYSTEM_ITEMS_V ODS
2236 WHERE ODS.PLAN_ID = -1
2237 AND ODS.PDS_PLAN_ID = PDS.PLAN_ID
2238 AND ODS.INVENTORY_ITEM_ID = PDS.INVENTORY_ITEM_ID
2239 AND ODS.ORGANIZATION_ID = DECODE(PDS.ORGANIZATION_ID, -1,
2240 msc_snapshot_pk.get_validation_org_id(PDS.SR_INSTANCE_ID), PDS.ORGANIZATION_ID)
2241 AND ODS.SR_INSTANCE_ID = PDS.SR_INSTANCE_ID
2242 AND PDS.PLAN_ID = p_plan_id;
2243 BEGIN
2244
2245 SELECT count(*)
2246 INTO lv_plan_partition_exists
2247 FROM MSC_PLAN_PARTITIONS
2248 WHERE plan_id = p_plan_id;
2249
2250 IF (lv_plan_partition_exists > 0) THEN
2251
2252 LOG_MESSAGE('Analysing MSC_SYSTEM_ITEMS plan partition ');
2253
2254 msc_analyse_tables_pk.analyse_table( 'MSC_SYSTEM_ITEMS', -1, p_plan_id);
2255
2256 ELSE
2257
2258 -- LOG_MESSAGE('Analysing MSC_SYSTEM_ITEMS table ');
2259
2260 -- msc_analyse_tables_pk.analyse_table( 'MSC_SYSTEM_ITEMS' );
2261
2262 null;
2263
2264 END IF;
2265
2266 LOG_MESSAGE('Updating the Items Table ');
2267
2268 lv_fetchComplete := FALSE;
2269
2270 OPEN items_for_update;
2271 IF (items_for_update%ISOPEN) THEN
2272 LOOP
2273 IF (lv_fetchComplete) THEN
2274 EXIT;
2275 END IF;
2276
2277 FETCH items_for_update
2278 BULK COLLECT
2279 INTO lb_inv_item_id,
2280 lb_org_id,
2281 lb_sr_instance_id,
2282 lb_item_name,
2283 lb_description,
2284 lb_buyer_name,
2285 lb_planner_code,
2286 lb_plng_excp_set,
2287 lb_revision,
2288 lb_val_org_id
2289 LIMIT ln_rows_to_fetch;
2290
2291 EXIT WHEN lb_inv_item_id.count = 0;
2292
2293 IF (items_for_update%NOTFOUND) THEN
2294 lv_fetchComplete := TRUE;
2295 END IF;
2296
2297 FORALL j IN lb_inv_item_id.FIRST..lb_inv_item_id.LAST
2298 UPDATE /*+ index (items MSC_SYSTEM_ITEMS_U1) */
2299 MSC_SYSTEM_ITEMS items
2300 SET item_name = lb_item_name(j),
2301 description = lb_description(j),
2302 buyer_name = lb_buyer_name(j),
2303 planner_code = lb_planner_code(j),
2304 planning_exception_set = lb_plng_excp_set(j),
2305 revision = lb_revision(j)
2306 WHERE sr_instance_id = lb_sr_instance_id(j)
2307 AND inventory_item_id = lb_inv_item_id(j)
2308 --AND (organization_id = lb_org_id(j) OR
2309 -- (organization_id = -1 AND lb_val_org_id(j) = lb_org_id(j)
2310 -- ))
2311 AND organization_id = lb_org_id(j)
2312 AND plan_id = p_plan_id;
2313
2314 COMMIT;
2315
2316 END LOOP;
2317
2318 END IF;
2319
2320 IF (items_for_update%ISOPEN) THEN
2321 CLOSE items_for_update;
2322 END IF;
2323
2324 COMMIT;
2325
2326 LOG_MESSAGE('Completed updating the Items Table ');
2327 EXCEPTION WHEN OTHERS then
2328 LOG_MESSAGE('RETCODE -'||SQLCODE);
2329 LOG_MESSAGE(SQLERRM);
2330 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
2331 END update_items_info;
2332
2333 FUNCTION f_period_start_date(p_plan_id IN NUMBER,
2334 p_instance_id IN NUMBER,
2335 p_org_id IN NUMBER,
2336 p_item_id IN NUMBER) return date is
2337 l_date date;
2338 begin
2339 SELECT TRUNC(nvl(max(period_start_date),sysdate))
2340 into l_date
2341 FROM msc_safety_stocks
2342 WHERE period_start_date <= TRUNC(SYSDATE)
2343 AND inventory_item_id = p_item_id
2344 AND sr_instance_id = p_instance_id
2345 AND organization_id = p_org_id
2346 AND plan_id = p_plan_id;
2347 return l_date;
2348
2349 end;
2350
2351 FUNCTION new_f_period_start_date(p_plan_id IN NUMBER,
2352 p_instance_id IN NUMBER,
2353 p_org_id IN NUMBER,
2354 p_item_id IN NUMBER,
2355 p_project_id IN number,
2356 p_task_id IN number) return date is
2357 l_date date;
2358 begin
2359 SELECT TRUNC(nvl(max(period_start_date),sysdate))
2360 into l_date
2361 FROM msc_safety_stocks
2362 WHERE period_start_date <= TRUNC(SYSDATE)
2363 AND inventory_item_id = p_item_id
2364 AND sr_instance_id = p_instance_id
2365 AND organization_id = p_org_id
2366 AND plan_id = p_plan_id
2367 AND nvl(project_id,-23453) = nvl(p_project_id,-23453)
2368 AND nvl(task_id,-23453) = nvl(p_task_id,-23453);
2369 return l_date;
2370
2371 end;
2372
2373 END MSC_SNAPSHOT_PK; -- package