[Home] [Help]
PACKAGE BODY: APPS.MSC_SNAPSHOT_PK
Source
1 PACKAGE BODY msc_snapshot_pk AS
2 /* $Header: MSCPSNPB.pls 120.11.12010000.2 2008/11/11 19:32:20 srpartha 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
419 END IF;
420 /*
421 IF mrdebug = TRUE THEN
422 LOG_MESSAGE('owning_org = '||l_org_id||' instance '||l_sr_instance_id||' start '||l_plan_start_date||' comp '||l_plan_comp_date);
423 END IF;
424 */
425 get_bucket_cutoff_dates(
426 p_plan_id => p_plan_id,
427 p_org_id => l_org_id,
428 p_instance_id => lv_bkt_ref_instance,
429 p_plan_start_date => l_plan_start_date,
430 p_plan_completion_date => l_plan_comp_date,
431 p_min_cutoff_bucket => NULL,
432 p_hour_cutoff_bucket => NULL,
433 p_daily_cutoff_bucket => NULL,
434 p_weekly_cutoff_bucket => NULL,
435 p_period_cutoff_bucket => NULL,
436 p_min_cutoff_date => l_min_cutoff_date,
437 p_hour_cutoff_date => l_hour_cutoff_date,
438 p_daily_cutoff_date => l_daily_cutoff_date,
439 p_weekly_cutoff_date => l_weekly_cutoff_date,
440 p_period_cutoff_date => l_period_cutoff_date,
441 p_err_mesg => p_err_mesg
442 );
443
444 p_min_cutoff_date := To_char(l_min_cutoff_date,'J');
445 p_hour_cutoff_date := To_char(l_hour_cutoff_date,'J');
446 p_daily_cutoff_date := To_char(l_daily_cutoff_date,'J');
447 p_weekly_cutoff_date := To_char(Nvl(l_weekly_cutoff_date,l_daily_cutoff_date),'J');
448 p_period_cutoff_date := To_char(Nvl(l_period_cutoff_date,Nvl(l_weekly_cutoff_date,l_daily_cutoff_date)),'J');
449
450 l_plan_cutoff_date := to_date(p_period_cutoff_date,'J');
451
452 IF p_err_mesg IS NOT NULL THEN
453 RETURN;
454 END IF;
455
456 -- l_weekly_cutoff_date := NULL;
457 -- l_period_cutoff_date := NULL;
458 /*
459 IF mrdebug = TRUE THEN
460 LOG_MESSAGE(' min = '||l_min_cutoff_date);
461 LOG_MESSAGE(' hour = '||l_hour_cutoff_date);
462 LOG_MESSAGE(' daily = '||l_daily_cutoff_date);
463 LOG_MESSAGE(' weekly = '||l_weekly_cutoff_date);
464 LOG_MESSAGE(' period = '||l_period_cutoff_date);
465 END IF;
466 */
467 -- Set the cutoff_date of the plan based period, week, day buckets set
468 UPDATE msc_plans
469 SET curr_cutoff_date = l_plan_cutoff_date,
470 curr_start_date = l_curr_start_date
471 WHERE plan_id = p_plan_id;
472
473
474 SELECT Nvl(min_cutoff_bucket,0), Nvl(hour_cutoff_bucket,0)+Nvl(min_cutoff_bucket,0)
475 INTO p_min_cutoff_bucket, p_hour_cutoff_bucket
476 FROM msc_plans
477 WHERE plan_id = p_plan_id;
478
479 -- select bucket reference calendar
480 select nvl(fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'), tp.calendar_code),
481 tp.calendar_exception_set_id ,
482 decode(fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'), NULL, tp.sr_instance_id, mcd.sr_instance_id)
483 into m_calendar_code , m_cal_exception_set_id , m_sr_instance_id
484 from msc_plans mp,
485 msc_trading_partners tp,
486 msc_calendar_dates mcd
487 where mp.plan_id = p_plan_id
488 and tp.partner_type = 3
489 and tp.sr_instance_id = mp.sr_instance_id
490 and mp.organization_id = tp.sr_tp_id
491 and mcd.calendar_date = trunc(sysdate)
492 and mcd.calendar_code = nvl(fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'), tp.calendar_code);
493
494
495 IF l_daily_cutoff_date IS NOT NULL THEN
496 -- insert the daily time buckets
497 SELECT
498 rownum
499 ,To_char(cal.calendar_date, 'YYYY/MM/DD')
500 ,To_char(cal.calendar_date,'YYYY/MM/DD')
501 ,1 bucket_type
502 ,1 days_in_bucket
503 BULK COLLECT INTO
504 l_msc_plan_buckets.bucket_index,
505 l_msc_plan_buckets.bkt_start_date,
506 l_msc_plan_buckets.bkt_end_date,
507 l_msc_plan_buckets.bucket_type,
508 l_msc_plan_buckets.days_in_bkt
509 FROM
510 MSC_CALENDAR_DATES cal
511 WHERE
512 cal.sr_instance_id = m_sr_instance_id
513 AND cal.calendar_code = m_calendar_code
514 AND cal.exception_set_id = m_cal_exception_set_id
515 and trunc(cal.calendar_date) <= trunc(l_daily_cutoff_date )
516 and trunc(cal.calendar_date) >= l_curr_start_date
517 ORDER BY cal.calendar_date;
518
519
520 insert_into_table(
521 p_plan_id,
522 l_sr_instance_id,
523 l_org_id,
524 l_bkt_index,
525 l_msc_plan_buckets,
526 p_err_mesg);
527
528 IF p_err_mesg IS NOT NULL THEN
529 /* IF mrdebug = TRUE THEN
530 LOG_MESSAGE(' 101 ');
531 END IF; */
532 RETURN;
533 END IF;
534
535 p_daily_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
536 p_weekly_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
537 p_period_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
538
539 l_bkt_index := l_bkt_index + l_msc_plan_buckets.bucket_index.count;
540 l_msc_plan_buckets := NULL;
541 END IF;
542
543
544 IF l_weekly_cutoff_date IS NOT NULL THEN
545
546 SELECT
547 l_bkt_index+rownum
548 ,To_char(cal.week_start_date, 'YYYY/MM/DD')
549 ,To_char(Least(
550 Greatest(cal.next_date - 1, cal.week_start_date),
551 -- for last week both are same
552 l_weekly_cutoff_date),'YYYY/MM/DD')
553 --min of this and weekly cutoff
554 ,2 bucket_type
555 ,trunc(Least(
556 Greatest(cal.next_date - 1, cal.week_start_date),
557 l_weekly_cutoff_date)) - trunc(cal.week_start_date) + 1 days_in_bucket
558 BULK COLLECT INTO
559 l_msc_plan_buckets.bucket_index,
560 l_msc_plan_buckets.bkt_start_date,
561 l_msc_plan_buckets.bkt_end_date,
562 l_msc_plan_buckets.bucket_type,
563 l_msc_plan_buckets.days_in_bkt
564 FROM
565 MSC_CAL_WEEK_START_DATES cal
566 WHERE cal.sr_instance_id = m_sr_instance_id
567 AND cal.calendar_code = m_calendar_code
568 AND cal.exception_set_id = m_cal_exception_set_id
569 and trunc(cal.week_start_date) <= trunc(l_weekly_cutoff_date)
570 and trunc(cal.week_start_date) >= trunc(Nvl(l_daily_cutoff_date,Sysdate-1))+1
571 ORDER BY cal.week_start_date ASC;
572
573 insert_into_table(
574 p_plan_id,
575 l_sr_instance_id,
576 l_org_id,
577 l_bkt_index,
578 l_msc_plan_buckets,
579 p_err_mesg);
580
581 IF p_err_mesg IS NOT NULL THEN
582 /* IF mrdebug = TRUE THEN
583 LOG_MESSAGE(' 201 ');
584 END IF; */
585 RETURN;
586 END IF;
587
588 p_weekly_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
589 p_period_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
590
591 l_bkt_index := l_bkt_index + l_msc_plan_buckets.bucket_index.count;
592 l_msc_plan_buckets := NULL;
593
594
595 END IF;
596
597
598 IF l_period_cutoff_date IS NOT NULL THEN
599
600 SELECT
601 l_bkt_index+ROWNUM
602 ,To_char(cal.period_start_date, 'YYYY/MM/DD') bkt_start_date
603 ,To_char(Least(
604 Greatest(cal.next_date - 1,cal.period_start_date),
605 l_period_cutoff_date), 'YYYY/MM/DD') bkt_end_date
606 ,3 bucket_type
607 ,trunc(Least(
608 Greatest(cal.next_date - 1,cal.period_start_date),
609 l_period_cutoff_date)) - trunc(cal.period_start_date)
610 + 1 days_in_bucket
611 -- days between needs a + 1
612 BULK COLLECT INTO
613 l_msc_plan_buckets.bucket_index,
614 l_msc_plan_buckets.bkt_start_date,
615 l_msc_plan_buckets.bkt_end_date,
616 l_msc_plan_buckets.bucket_type,
617 l_msc_plan_buckets.days_in_bkt
618 FROM
619 msc_period_start_dates cal
620 WHERE
621 cal.sr_instance_id = m_sr_instance_id
622 AND cal.calendar_code = m_calendar_code
623 AND cal.exception_set_id = m_cal_exception_set_id
624 and trunc(cal.period_start_date) <= trunc(l_period_cutoff_date )
625 and trunc(cal.period_start_date) >=
626 trunc(Nvl(l_weekly_cutoff_date, Nvl(l_daily_cutoff_date,Sysdate-1))) + 1
627 ORDER BY cal.period_start_date;
628
629 insert_into_table(
630 p_plan_id,
631 l_sr_instance_id,
632 l_org_id,
633 l_bkt_index,
634 l_msc_plan_buckets,
635 p_err_mesg);
636 IF p_err_mesg IS NOT NULL THEN
637 /* IF mrdebug = TRUE THEN
638 LOG_MESSAGE(' 301 ');
639 END IF; */
640 RETURN;
641 END IF;
642 p_period_cutoff_bucket := l_bkt_index + l_msc_plan_buckets.bucket_index.COUNT;
643
644 END IF;
645
646 EXCEPTION
647 WHEN OTHERS THEN
648 /* IF mrdebug = TRUE THEN
649 LOG_MESSAGE('Error in calculate_plan_buckets :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
650 END IF; */
651 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
652 END calculate_plan_buckets;
653
654 /*====================================================
655 This procedure will be used for concurrent program
656 This will call refresh_snapshot_ods_mv internally
657 with different parameters
658
659 p_source : 1 - Collections
660 2 - Others
661 ====================================================*/
662 PROCEDURE refresh_snapshot_ods_mv(
663 ERRBUF OUT NOCOPY VARCHAR2,
664 RETCODE OUT NOCOPY NUMBER,
665 p_plan_id IN NUMBER default null) IS
666 lv_plan_so NUMBER := 0;
667 lv_global_forecast NUMBER := 0;
668 lv_err_code NUMBER;
669 lv_err_mesg VARCHAR2(2000);
670 lv_launch_refresh_gf BOOLEAN := FALSE;
671 l_latest_change_date DATE;
672 last_gf_refresh_date DATE;
673 BEGIN
674 LOG_MESSAGE('Started refresh of global forecast');
675
676 IF p_plan_id IS NOT NULL THEN
677
678 BEGIN
679
680 select count(*) into lv_plan_so
681 from msc_plan_organizations_v
682 where plan_id = p_plan_id
683 and nvl(include_salesorder,SYS_NO) = SYS_YES;
684
685 EXCEPTION WHEN OTHERS THEN
686 RETCODE := G_ERROR;
687 RETURN;
688 END;
689
690 BEGIN
691
692 select count(*)
693 into lv_global_forecast
694 from msc_plan_schedules_v
695 where plan_id = p_plan_id
696 and input_organization_id = GLOBAL_ORG;
697
698 EXCEPTION WHEN OTHERS THEN
699 RETCODE := G_ERROR;
700 RETURN;
701 END;
702
703 /*================================================
704 Check if refresh global forecast needs to be
705 launched.
706 ================================================*/
707
708 IF (lv_global_forecast > 0 OR lv_plan_so >0 ) THEN
709
710 /*
711 BEGIN
712 select nvl(global_fcst_refresh_date, to_date('01-JAN-1900','dd-mon-yyyy'))
713 into last_gf_refresh_date
714 from msc_plans
715 where plan_id = p_plan_id;
716
717 EXCEPTION WHEN OTHERS THEN
718 RETCODE := G_ERROR;
719 RETURN;
720 END;
721
722 IF (lv_launch_refresh_gf = FALSE) THEN
723 l_latest_change_date := NULL;
724 BEGIN
725 select max(last_update_date) into l_latest_change_date
726 from msc_plan_organizations
727 where plan_id = p_plan_id;
728 EXCEPTION WHEN OTHERS THEN
729 RETCODE := G_ERROR;
730 RETURN;
731 END;
732
733 IF (l_latest_change_date > last_gf_refresh_date) THEN
734 lv_launch_refresh_gf := TRUE;
735 END IF;
736
737 END IF;
738
739 IF (lv_launch_refresh_gf = FALSE) THEN
740 l_latest_change_date := NULL;
741 BEGIN
742 select max(msa.last_update_date) into l_latest_change_date
743 from msc_sr_assignments msa,
744 msc_plans mp
745 where mp.plan_id = p_plan_id
746 and mp.FORECAST_ASSIGNMENT_SET_ID = msa.assignment_set_id;
747 EXCEPTION WHEN OTHERS THEN
748 RETCODE := G_ERROR;
749 RETURN;
750 END;
751
752 IF (l_latest_change_date > last_gf_refresh_date) THEN
753 lv_launch_refresh_gf := TRUE;
754 END IF;
755
756 END IF;
757
758 IF (lv_launch_refresh_gf = FALSE) THEN
759 l_latest_change_date := NULL;
760 BEGIN
761 select max(msr.last_update_date) into l_latest_change_date
762 from msc_sourcing_rules msr,
763 msc_sr_assignments msra,
764 msc_plans mp
765 where mp.plan_id = p_plan_id
766 and mp.FORECAST_ASSIGNMENT_SET_ID = msra.ASSIGNMENT_SET_ID
767 and msra.sourcing_rule_id = msr.sourcing_rule_id;
768 EXCEPTION WHEN OTHERS THEN
769 RETCODE := G_ERROR;
770 RETURN;
771 END;
772
773 IF (l_latest_change_date > last_gf_refresh_date) THEN
774 lv_launch_refresh_gf := TRUE;
775 END IF;
776
777 END IF; */
778
779 lv_launch_refresh_gf := TRUE;
780 ELSE
781 lv_launch_refresh_gf := FALSE;
782 END IF; /* IF (lv_global_forecast > 0 OR lv_plan_so >0 ) THEN */
783
784 ELSE
785 lv_launch_refresh_gf := TRUE;
786 END IF; /* p_plan_id IS NOT NULL THEN */
787
788
789 /*===========================
790 Call refresh_snapshot_ods_mv
791 ============================*/
792 IF ( lv_launch_refresh_gf) THEN
793
794 LOG_MESSAGE('Calling refresh_snp_ods_mv_pvt for refresh of global forecast');
795 refresh_snp_ods_mv_pvt(lv_err_code,
796 lv_err_mesg,
797 p_plan_id,
798 lv_global_forecast,
799 lv_plan_so);
800
801 ELSE
802 LOG_MESSAGE('This plan does not have Sales Orders and Global Forecast.');
803 LOG_MESSAGE('There is no need to refresh global forecast');
804 RETCODE := G_SUCCESS;
805 END IF;
806
807 IF lv_err_code = G_ERROR THEN
808 RETCODE := G_ERROR;
809 LOG_MESSAGE('RETCODE -'||lv_err_code);
810 LOG_MESSAGE(lv_err_mesg);
811 ELSE
812 RETCODE := G_SUCCESS;
813 LOG_MESSAGE('RETCODE -'||lv_err_code);
814 END IF;
815
816 END refresh_snapshot_ods_mv;
817
818 PROCEDURE refresh_snp_ods_mv_pvt(
819 p_err_code OUT NOCOPY NUMBER,
820 p_err_mesg OUT NOCOPY VARCHAR2,
821 p_plan_id in NUMBER,
822 p_global_forecast in number default null,
823 p_plan_so in number default null
824 ) IS
825 lv_sql_stmt VARCHAR2(32767);
826 lv_plan_id VARCHAR2(200);
827 lv_task_start_time DATE;
828 lv_retval boolean;
829 lv_dummy1 varchar2(32);
830 lv_dummy2 varchar2(32);
831 lv_msc_schema varchar2(32);
832 BEGIN
833 /* DBMS_MVIEW.REFRESH ('MSC_ITEM_SO_SR_LEVELS_MV','c');
834 DBMS_MVIEW.REFRESH ('MSC_ITEM_FCST_SR_LEVELS_MV','c');*/
835
836 IF p_plan_id is NULL THEN
837 lv_retval := FND_INSTALLATION.GET_APP_INFO ( 'MSC', lv_dummy1, lv_dummy2, lv_msc_schema);
838
839 lv_sql_stmt:= 'TRUNCATE TABLE '||lv_msc_schema||'.MSC_ITEM_SO_SR_LEVELS';
840 EXECUTE IMMEDIATE lv_sql_stmt;
841
842 lv_sql_stmt:= 'TRUNCATE TABLE '||lv_msc_schema||'.MSC_ITEM_FCST_SR_LEVELS';
843 EXECUTE IMMEDIATE lv_sql_stmt;
844
845 lv_plan_id := ' ';
846
847 ELSE
848 DELETE MSC_ITEM_SO_SR_LEVELS where plan_id = p_plan_id;
849 DELETE MSC_ITEM_FCST_SR_LEVELS where plan_id = p_plan_id;
850 lv_plan_id := ' WHERE plan_id = '||p_plan_id||' ';
851 END IF;
852
853 COMMIT;
854
855 IF (p_plan_so > 0 OR p_plan_id IS NULL) THEN
856
857 lv_sql_stmt :=
858 ' INSERT /*+ APPEND */ INTO MSC_ITEM_SO_SR_LEVELS'
859 ||' (INVENTORY_ITEM_ID ,'
860 ||' ORGANIZATION_ID ,'
861 ||' SR_INSTANCE_ID ,'
862 ||' PLAN_ID ,'
863 ||' ASSIGNMENT_TYPE ,'
864 ||' ASSIGNMENT_SET_ID ,'
865 ||' SOURCING_RULE_TYPE ,'
866 ||' SOURCE_ORGANIZATION_ID ,'
867 ||' SOURCE_ORG_INSTANCE_ID ,'
868 ||' ALLOCATION_PERCENT ,'
869 ||' RANK ,'
870 ||' EFFECTIVE_DATE ,'
871 ||' DISABLE_DATE ,'
872 ||' SOURCING_LEVEL ,'
873 ||' ASSIGNMENT_ID ,'
874 ||' SOURCING_RULE_ID ,'
875 ||' SOURCING_RULE_NAME ,'
876 ||' SOURCE_TYPE ,'
877 ||' SR_DESCRIPTION ,'
878 ||' COMPILE_DESIGNATOR ,'
879 ||' OWNING_ORG_ID ,'
880 ||' COMP_MRP_PLANNING_CODE ,'
881 ||' COMP_BOM_ITEM_TYPE ,'
882 ||' COMP_PLANNING_MAKE_BUY_CODE ,'
883 ||' COMP_PRIMARY_UOM_CODE ,'
884 ||' CUSTOMER_ID ,'
885 ||' CUSTOMER_SITE_ID ,'
886 ||' REGION_ID ,'
887 ||' COMP_DRP_PLANNED )'
888 ||' SELECT '
889 ||' INVENTORY_ITEM_ID ,'
890 ||' ORGANIZATION_ID ,'
891 ||' SR_INSTANCE_ID ,'
892 ||' PLAN_ID ,'
893 ||' ASSIGNMENT_TYPE ,'
894 ||' ASSIGNMENT_SET_ID ,'
895 ||' SOURCING_RULE_TYPE ,'
896 ||' SOURCE_ORGANIZATION_ID ,'
897 ||' SOURCE_ORG_INSTANCE_ID ,'
898 ||' ALLOCATION_PERCENT ,'
899 ||' RANK ,'
900 ||' EFFECTIVE_DATE ,'
901 ||' DISABLE_DATE ,'
902 ||' SOURCING_LEVEL ,'
903 ||' ASSIGNMENT_ID ,'
904 ||' SOURCING_RULE_ID ,'
905 ||' SOURCING_RULE_NAME ,'
906 ||' SOURCE_TYPE ,'
907 ||' SR_DESCRIPTION ,'
908 ||' COMPILE_DESIGNATOR ,'
909 ||' OWNING_ORG_ID ,'
910 ||' COMP_MRP_PLANNING_CODE ,'
911 ||' COMP_BOM_ITEM_TYPE ,'
912 ||' COMP_PLANNING_MAKE_BUY_CODE ,'
913 ||' COMP_PRIMARY_UOM_CODE ,'
914 ||' CUSTOMER_ID ,'
915 ||' CUSTOMER_SITE_ID ,'
916 ||' REGION_ID ,'
917 ||' COMP_DRP_PLANNED '
918 ||' FROM MSC_ITEM_SO_SR_LEVELS_V '
919 ||lv_plan_id;
920 EXECUTE IMMEDIATE lv_sql_stmt ;
921 COMMIT;
922
923 LOG_MESSAGE('Inserted records into MSC_ITEM_SO_SR_LEVELS');
924 END IF;
925
926
927 IF (p_global_forecast > 0 OR p_plan_id is NULL) THEN
928 lv_sql_stmt :=
929 ' INSERT /*+ APPEND */ INTO MSC_ITEM_FCST_SR_LEVELS '
930 ||' (INVENTORY_ITEM_ID ,'
931 ||' SR_INVENTORY_ITEM_ID ,'
932 ||' ORGANIZATION_ID ,'
933 ||' SR_INSTANCE_ID ,'
934 ||' PLAN_ID ,'
935 ||' ASSIGNMENT_TYPE ,'
936 ||' ASSIGNMENT_SET_ID ,'
937 ||' SOURCING_RULE_TYPE ,'
938 ||' SOURCE_ORGANIZATION_ID ,'
939 ||' SOURCE_ORG_INSTANCE_ID ,'
940 ||' VENDOR_ID ,'
941 ||' VENDOR_SITE_ID ,'
942 ||' ALLOCATION_PERCENT ,'
943 ||' RANK ,'
944 ||' EFFECTIVE_DATE ,'
945 ||' DISABLE_DATE ,'
946 ||' CATEGORY_ID ,'
947 ||' SOURCING_LEVEL ,'
948 ||' ASSIGNMENT_ID ,'
949 ||' SOURCING_RULE_ID ,'
950 ||' SOURCING_RULE_NAME ,'
951 ||' SOURCE_TYPE ,'
952 ||' SOURCE_ORG_CODE ,'
953 ||' SR_DESCRIPTION ,'
954 ||' COMPILE_DESIGNATOR ,'
955 ||' OWNING_ORG_ID ,'
956 ||' MRP_PLANNING_CODE ,'
957 ||' BOM_ITEM_TYPE ,'
958 ||' PLANNING_MAKE_BUY_CODE ,'
959 ||' PRIMARY_UOM_CODE ,'
960 ||' COMP_MRP_PLANNING_CODE ,'
961 ||' CUSTOMER_ID ,'
962 ||' CUSTOMER_SITE_ID ,'
963 ||' ZONE_ID ,'
964 ||' ASSY_DRP_PLANNED ,'
965 ||' COMP_DRP_PLANNED ) '
966 ||' SELECT '
967 ||' INVENTORY_ITEM_ID ,'
968 ||' SR_INVENTORY_ITEM_ID ,'
969 ||' ORGANIZATION_ID ,'
970 ||' SR_INSTANCE_ID ,'
971 ||' PLAN_ID ,'
972 ||' ASSIGNMENT_TYPE ,'
973 ||' ASSIGNMENT_SET_ID ,'
974 ||' SOURCING_RULE_TYPE ,'
975 ||' SOURCE_ORGANIZATION_ID ,'
976 ||' SOURCE_ORG_INSTANCE_ID ,'
977 ||' VENDOR_ID ,'
978 ||' VENDOR_SITE_ID ,'
979 ||' ALLOCATION_PERCENT ,'
980 ||' RANK ,'
981 ||' EFFECTIVE_DATE ,'
982 ||' DISABLE_DATE ,'
983 ||' CATEGORY_ID ,'
984 ||' SOURCING_LEVEL ,'
985 ||' ASSIGNMENT_ID ,'
986 ||' SOURCING_RULE_ID ,'
987 ||' SOURCING_RULE_NAME ,'
988 ||' SOURCE_TYPE ,'
989 ||' SOURCE_ORG_CODE ,'
990 ||' SR_DESCRIPTION ,'
991 ||' COMPILE_DESIGNATOR ,'
992 ||' OWNING_ORG_ID ,'
993 ||' MRP_PLANNING_CODE ,'
994 ||' BOM_ITEM_TYPE ,'
995 ||' PLANNING_MAKE_BUY_CODE ,'
996 ||' PRIMARY_UOM_CODE ,'
997 ||' COMP_MRP_PLANNING_CODE ,'
998 ||' CUSTOMER_ID ,'
999 ||' CUSTOMER_SITE_ID ,'
1000 ||' ZONE_ID ,'
1001 ||' ASSY_DRP_PLANNED ,'
1002 ||' COMP_DRP_PLANNED '
1003 ||' FROM MSC_ITEM_FCST_SR_LEVELS_V '
1004 ||lv_plan_id;
1005
1006 EXECUTE IMMEDIATE lv_sql_stmt ;
1007 COMMIT;
1008
1009 LOG_MESSAGE( 'Inserted records into MSC_ITEM_FCST_SR_LEVELS');
1010 END IF;
1011 p_err_code := G_SUCCESS;
1012 p_err_mesg := 'SUCCESS';
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015 p_err_code := G_ERROR;
1016 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
1017 LOG_MESSAGE('Error while refresh global forecast');
1018 END refresh_snp_ods_mv_pvt;
1019
1020 PROCEDURE refresh_snapshot_pds_mv(
1021 p_err_mesg OUT NOCOPY VARCHAR2,
1022 p_plan_id in NUMBER,
1023 p_global_forecast in number default null,
1024 p_plan_so in number default null
1025 ) IS
1026 lv_p_plan_so number;
1027 lv_p_plan_type number;
1028 BEGIN
1029 /* DBMS_MVIEW.REFRESH ('MSC_ITEM_FCST_BOD_SR_LEVELS_MV','c');
1030 DBMS_MVIEW.REFRESH ('MSC_BOD_SO_SR_LEVELS_MV','c');
1031 */
1032 IF p_global_forecast = SYS_YES THEN
1033 DELETE MSC_ITEM_FCST_BOD_SR_LEVELS where plan_id = p_plan_id;
1034 COMMIT;
1035
1036 INSERT /*+APPEND*/ INTO MSC_ITEM_FCST_BOD_SR_LEVELS
1037 (
1038 INVENTORY_ITEM_ID ,
1039 SR_INVENTORY_ITEM_ID ,
1040 ORGANIZATION_ID ,
1041 SR_INSTANCE_ID ,
1042 PLAN_ID ,
1043 ASSIGNMENT_TYPE ,
1044 ASSIGNMENT_SET_ID ,
1045 SOURCING_RULE_TYPE ,
1046 SOURCE_ORGANIZATION_ID ,
1047 SOURCE_ORG_INSTANCE_ID ,
1048 VENDOR_ID ,
1049 VENDOR_SITE_ID ,
1050 ALLOCATION_PERCENT ,
1051 RANK ,
1052 SHIP_METHOD ,
1053 EFFECTIVE_DATE ,
1054 DISABLE_DATE ,
1055 CATEGORY_ID ,
1056 SOURCING_LEVEL ,
1057 ASSIGNMENT_ID ,
1058 SOURCING_RULE_ID ,
1059 SOURCING_RULE_NAME ,
1060 SOURCE_TYPE ,
1061 SOURCE_ORG_CODE ,
1062 SR_DESCRIPTION ,
1063 OWNING_ORG_ID ,
1064 CUSTOMER_ID ,
1065 CUSTOMER_SITE_ID ,
1066 ZONE_ID )
1067 SELECT
1068 INVENTORY_ITEM_ID ,
1069 SR_INVENTORY_ITEM_ID ,
1070 ORGANIZATION_ID ,
1071 SR_INSTANCE_ID ,
1072 PLAN_ID ,
1073 ASSIGNMENT_TYPE ,
1074 ASSIGNMENT_SET_ID ,
1075 SOURCING_RULE_TYPE ,
1076 SOURCE_ORGANIZATION_ID ,
1077 SOURCE_ORG_INSTANCE_ID ,
1078 VENDOR_ID ,
1079 VENDOR_SITE_ID ,
1080 ALLOCATION_PERCENT ,
1081 RANK ,
1082 SHIP_METHOD ,
1083 EFFECTIVE_DATE ,
1084 DISABLE_DATE ,
1085 CATEGORY_ID ,
1086 SOURCING_LEVEL ,
1087 ASSIGNMENT_ID ,
1088 SOURCING_RULE_ID ,
1089 SOURCING_RULE_NAME ,
1090 SOURCE_TYPE ,
1091 SOURCE_ORG_CODE ,
1092 SR_DESCRIPTION ,
1093 OWNING_ORG_ID ,
1094 CUSTOMER_ID ,
1095 CUSTOMER_SITE_ID ,
1096 ZONE_ID
1097 FROM MSC_ITEM_FCST_BOD_SR_LEVELS_V
1098 WHERE PLAN_ID = p_plan_id;
1099 COMMIT;
1100 END IF;
1101
1102
1103 IF p_plan_so > 0 THEN
1104 select decode(curr_plan_type,1,1,2,1,3,1,0)
1105 into lv_p_plan_type
1106 from msc_plans
1107 where plan_id = p_plan_id;
1108
1109 IF lv_p_plan_type = 0 THEN
1110 lv_p_plan_so := 1;
1111 ELSE
1112 select decode(DAILY_MATERIAL_CONSTRAINTS,1,1,
1113 decode(DAILY_RESOURCE_CONSTRAINTS,1,1,
1114 decode(WEEKLY_MATERIAL_CONSTRAINTS,1,1,
1115 decode(WEEKLY_RESOURCE_CONSTRAINTS,1,1,
1116 decode(PERIOD_MATERIAL_CONSTRAINTS,1,1,
1117 decode(PERIOD_RESOURCE_CONSTRAINTS,1,1,0))))))
1118 into lv_p_plan_so
1119 from msc_plans
1120 where plan_id = p_plan_id;
1121
1122 IF lv_p_plan_so = 1 THEN
1123 select decode(optimize_flag,1,1,
1124 decode(nvl(fnd_profile.value('MSO_ENABLE_DECISION_RULES'),'N'),
1125 'Y',1,'Yes',1,'YES',1,0))
1126 into lv_p_plan_so
1127 from msc_plans
1128 where plan_id = p_plan_id;
1129
1130 IF lv_p_plan_so = 1 THEN
1131 lv_p_plan_so := 1;
1132 ELSE
1133 lv_p_plan_so := 0;
1134 END IF;
1135 ELSE
1136 lv_p_plan_so := 0;
1137 END IF;
1138 END IF;
1139 ELSE
1140 lv_p_plan_so := 0;
1141 END IF;
1142
1143 IF lv_p_plan_so > 0 THEN
1144
1145 DELETE MSC_BOD_SO_SR_LEVELS where plan_id = p_plan_id;
1146 COMMIT;
1147
1148 INSERT INTO MSC_TEMP_REGION_LOCATIONS(
1149 REGION_ID ,
1150 LOCATION_ID ,
1151 LOCATION_SOURCE ,
1152 REGION_TYPE ,
1153 PARENT_REGION_FLAG ,
1154 SR_INSTANCE_ID,
1155 partner_type)
1156 SELECT REGION_ID ,
1157 LOCATION_ID ,
1158 LOCATION_SOURCE ,
1159 (10 * (10 - region_type)) REGION_TYPE ,
1160 PARENT_REGION_FLAG ,
1161 SR_INSTANCE_ID,
1162 2 partner_type
1163 FROM MSC_REGION_LOCATIONS
1164 WHERE location_source = 'HZ'
1165 and region_id is not null
1166 and region_id in ( select distinct msa.region_id
1167 from msc_sr_assignments msa,
1168 msc_plans mp
1169 where msa.assignment_type in (7,8,9)
1170 and mp.plan_id = p_plan_id
1171 and msa.assignment_set_id = mp.curr_assignment_set_id
1172 )
1173 UNION ALL
1174 select a.REGION_ID,
1175 c.LOCATION_ID,
1176 c.LOCATION_SOURCE,
1177 ((10 * (10 - a.zone_level)) + 1) REGION_TYPE,
1178 c.PARENT_REGION_FLAG,
1179 a.SR_INSTANCE_ID,
1180 2 PARTNER_TYPE
1181 FROM MSC_REGIONS a,
1182 MSC_ZONE_REGIONS b,
1183 msc_region_locations c
1184 WHERE a.region_id = b.parent_region_id
1185 AND a.region_type = 10
1186 AND a.zone_level IS NOT NULL
1187 AND a.sr_instance_id = b.sr_instance_id
1188 AND b.region_id = c.region_id
1189 and b.sr_instance_id = c.sr_instance_id
1190 and c.region_id is not null
1191 and c.location_source = 'HZ'
1192 and a.region_id in ( select distinct msa.region_id
1193 from msc_sr_assignments msa,
1194 msc_plans mp
1195 where msa.assignment_type in (7,8,9)
1196 and mp.plan_id = p_plan_id
1197 and msa.assignment_set_id = mp.curr_assignment_set_id
1198 )
1199 ;
1200
1201 INSERT /*+APPEND*/ into MSC_BOD_SO_SR_LEVELS
1202 ( INVENTORY_ITEM_ID ,
1203 ORGANIZATION_ID ,
1204 SR_INSTANCE_ID ,
1205 PLAN_ID ,
1206 ASSIGNMENT_TYPE ,
1207 ASSIGNMENT_SET_ID ,
1208 SOURCING_RULE_TYPE ,
1209 SOURCE_ORGANIZATION_ID ,
1210 SOURCE_ORG_INSTANCE_ID ,
1211 ALLOCATION_PERCENT ,
1212 RANK ,
1213 SHIP_METHOD ,
1214 EFFECTIVE_DATE ,
1215 DISABLE_DATE ,
1216 SOURCING_LEVEL ,
1217 ASSIGNMENT_ID ,
1218 SOURCING_RULE_ID ,
1219 SOURCING_RULE_NAME ,
1220 SOURCE_TYPE ,
1221 SR_DESCRIPTION ,
1222 COMPILE_DESIGNATOR ,
1223 OWNING_ORG_ID ,
1224 MRP_PLANNING_CODE ,
1225 BOM_ITEM_TYPE ,
1226 PLANNING_MAKE_BUY_CODE ,
1227 PRIMARY_UOM_CODE ,
1228 CUSTOMER_ID ,
1229 CUSTOMER_SITE_ID ,
1230 REGION_ID ,
1231 REGION_TYPE )
1232 SELECT
1233 INVENTORY_ITEM_ID ,
1234 ORGANIZATION_ID ,
1235 SR_INSTANCE_ID ,
1236 PLAN_ID ,
1237 ASSIGNMENT_TYPE ,
1238 ASSIGNMENT_SET_ID ,
1239 SOURCING_RULE_TYPE ,
1240 SOURCE_ORGANIZATION_ID ,
1241 SOURCE_ORG_INSTANCE_ID ,
1242 ALLOCATION_PERCENT ,
1243 RANK ,
1244 SHIP_METHOD ,
1245 EFFECTIVE_DATE ,
1246 DISABLE_DATE ,
1247 SOURCING_LEVEL ,
1248 ASSIGNMENT_ID ,
1249 SOURCING_RULE_ID ,
1250 SOURCING_RULE_NAME ,
1251 SOURCE_TYPE ,
1252 SR_DESCRIPTION ,
1253 COMPILE_DESIGNATOR ,
1254 OWNING_ORG_ID ,
1255 MRP_PLANNING_CODE ,
1256 BOM_ITEM_TYPE ,
1257 PLANNING_MAKE_BUY_CODE ,
1258 PRIMARY_UOM_CODE ,
1259 CUSTOMER_ID ,
1260 CUSTOMER_SITE_ID ,
1261 REGION_ID ,
1262 REGION_TYPE
1263 FROM MSC_BOD_SO_SR_LEVELS_V
1264 WHERE PLAN_ID = p_plan_id;
1265 COMMIT;
1266 END IF;
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 /* IF mrdebug = TRUE THEN
1270 LOG_MESSAGE('Error in insert_into_table :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
1271 END IF; */
1272 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
1273
1274 END refresh_snapshot_pds_mv;
1275
1276 PROCEDURE insert_into_table(
1277 p_plan_id NUMBER,
1278 p_sr_instance_id NUMBER,
1279 p_org_id NUMBER,
1280 p_bucket_index NUMBER,
1281 p_msc_plan_buckets IN OUT NOCOPY msc_plan_buckets_typ,
1282 p_err_mesg OUT NOCOPY VARCHAR2
1283 ) IS
1284 j NUMBER;
1285 k NUMBER;
1286 BEGIN
1287
1288 FOR j IN 1..p_msc_plan_buckets.bucket_index.COUNT LOOP
1289 p_msc_plan_buckets.bucket_index(j) := j+p_bucket_index;
1290 END LOOP;
1291 /*
1292 IF mrdebug = TRUE THEN
1293 NULL;
1294 FOR j IN 1..p_msc_plan_buckets.bucket_index.COUNT LOOP
1295 LOG_MESSAGE(p_msc_plan_buckets.bucket_index(j)||' '||
1296 p_msc_plan_buckets.bkt_start_date(j)||' '||
1297 p_msc_plan_buckets.bkt_end_date(j)||' '||
1298 p_msc_plan_buckets.bucket_type(j)||' '||
1299 p_msc_plan_buckets.days_in_bkt(j));
1300 END LOOP;
1301
1302 LOG_MESSAGE(p_msc_plan_buckets.bucket_index.count||' '||
1303 p_msc_plan_buckets.bkt_start_date.count||' '||
1304 p_msc_plan_buckets.bkt_end_date.count||' '||
1305 p_msc_plan_buckets.bucket_type.count||' '||
1306 p_msc_plan_buckets.days_in_bkt.count);
1307
1308 END IF;
1309 */
1310
1311 FORALL k IN 1..p_msc_plan_buckets.bucket_index.COUNT
1312 insert into msc_plan_buckets(
1313 PLAN_ID
1314 ,ORGANIZATION_ID
1315 ,SR_INSTANCE_ID
1316 ,BUCKET_INDEX
1317 ,BKT_START_DATE
1318 ,BKT_END_DATE
1319 ,BUCKET_TYPE
1320 ,DAYS_IN_BKT
1321 ,CURR_FLAG
1322 ,LAST_UPDATE_DATE
1323 ,LAST_UPDATED_BY
1324 ,CREATION_DATE
1325 ,CREATED_BY)
1326 VALUES
1327 (
1328 p_plan_id,
1329 p_org_id,
1330 p_sr_instance_id,
1331 p_msc_plan_buckets.bucket_index(k),
1332 To_date(p_msc_plan_buckets.bkt_start_date(k), 'YYYY/MM/DD'),
1333 To_date(p_msc_plan_buckets.bkt_end_date(k), 'YYYY/MM/DD')+(86399/86400),
1334 p_msc_plan_buckets.bucket_type(k),
1335 p_msc_plan_buckets.days_in_bkt(k),
1336 1,
1337 Sysdate,
1338 1,
1339 Sysdate,
1340 1);
1341
1342 EXCEPTION
1343 WHEN OTHERS THEN
1344 /* IF mrdebug = TRUE THEN
1345 LOG_MESSAGE('Error in insert_into_table :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
1346 END IF; */
1347 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
1348
1349 END insert_into_table;
1350
1351 PROCEDURE get_cutoff_dates(
1352 p_plan_id IN NUMBER,
1353 p_err_mesg OUT NOCOPY VARCHAR2,
1354 p_min_cutoff_date OUT NOCOPY number,
1355 p_hour_cutoff_date OUT NOCOPY number,
1356 p_daily_cutoff_date OUT NOCOPY number,
1357 p_weekly_cutoff_date OUT NOCOPY number,
1358 p_period_cutoff_date OUT NOCOPY number,
1359 p_min_cutoff_bucket OUT NOCOPY number,
1360 p_hour_cutoff_bucket OUT NOCOPY number,
1361 p_daily_cutoff_bucket OUT NOCOPY number,
1362 p_weekly_cutoff_bucket OUT NOCOPY number,
1363 p_period_cutoff_bucket OUT NOCOPY number
1364 ) IS
1365 l_daily_cutoff_date DATE;
1366 l_weekly_cutoff_date DATE;
1367 l_period_cutoff_date DATE;
1368 first_date DATE;
1369 BEGIN
1370
1371 SELECT Nvl(min_cutoff_bucket,0), Nvl(hour_cutoff_bucket,0)
1372 INTO p_min_cutoff_bucket,p_hour_cutoff_bucket
1373 FROM msc_plans
1374 WHERE plan_id = p_plan_id;
1375
1376 --LOG_MESSAGE(' 1 : '||p_min_cutoff_bucket||' '||p_hour_cutoff_bucket);
1377
1378 SELECT NVL(MIN(bkt_start_date), TRUNC(SYSDATE))
1379 INTO first_date
1380 FROM msc_plan_buckets
1381 WHERE plan_id = p_plan_id
1382 AND bucket_type = 1;
1383
1384 --LOG_MESSAGE(' 2 : '||first_date);
1385
1386 IF p_min_cutoff_bucket <> 0 THEN
1387 p_min_cutoff_date := To_char(first_date+(p_min_cutoff_bucket-1),'J');
1388 --LOG_MESSAGE(' 3 : '||p_min_cutoff_date);
1389 ELSE
1390 p_min_cutoff_date := To_char(first_date,'J');
1391 --LOG_MESSAGE(' 4 : '||p_min_cutoff_date);
1392 END IF;
1393
1394 IF p_hour_cutoff_bucket <> 0 THEN
1395 IF p_min_cutoff_bucket <> 0 THEN
1396 p_hour_cutoff_date := p_min_cutoff_date+p_hour_cutoff_bucket;
1397 -- changed after finding the date.
1398 p_hour_cutoff_bucket := p_min_cutoff_bucket + p_hour_cutoff_bucket;
1399 --LOG_MESSAGE(' 5 : '||p_hour_cutoff_bucket||' '||p_hour_cutoff_date);
1400 ELSE
1401 p_hour_cutoff_date := To_char(first_date+p_hour_cutoff_bucket -1,'J');
1402 --LOG_MESSAGE(' 6 : '||p_hour_cutoff_date);
1403 END IF;
1404 ELSE
1405 p_hour_cutoff_bucket := p_min_cutoff_bucket;
1406 p_hour_cutoff_date := p_min_cutoff_date;
1407 --LOG_MESSAGE(' 7 : '||p_hour_cutoff_bucket||' '||p_hour_cutoff_date);
1408 END IF;
1409
1410
1411 SELECT NVL(max(bkt_end_date), TRUNC(SYSDATE)),
1412 NVL(max(bucket_index), 0)
1413 INTO l_daily_cutoff_date, p_daily_cutoff_bucket
1414 from msc_plan_buckets
1415 where plan_id = p_plan_id
1416 AND bucket_type = 1;
1417
1418 if p_daily_cutoff_bucket = 0
1419 then
1420 select nvl(curr_start_date-1, TRUNC(SYSDATE-1))
1421 INTO l_daily_cutoff_date
1422 from msc_plans
1423 where plan_id = p_plan_id;
1424 end if;
1425
1426
1427
1428 --LOG_MESSAGE(' 8 : '||l_daily_cutoff_date||' '||p_daily_cutoff_bucket);
1429
1430 SELECT
1431 Nvl(max(bkt_end_date),l_daily_cutoff_date),
1432 Nvl(max(bucket_index), p_daily_cutoff_bucket)
1433 INTO l_weekly_cutoff_date, p_weekly_cutoff_bucket
1434 from msc_plan_buckets
1435 where plan_id = p_plan_id
1436 AND bucket_type = 2;
1437
1438 --LOG_MESSAGE(' 9 : '||l_weekly_cutoff_date||' '||p_weekly_cutoff_bucket);
1439
1440 SELECT
1441 Nvl(max(bkt_end_date),l_weekly_cutoff_date),
1442 Nvl(max(bucket_index), p_weekly_cutoff_bucket)
1443 INTO l_period_cutoff_date, p_period_cutoff_bucket
1444 from msc_plan_buckets
1445 where plan_id = p_plan_id
1446 AND bucket_type = 3;
1447
1448 --LOG_MESSAGE(' 10 : '||l_period_cutoff_date||' '||p_period_cutoff_bucket);
1449
1450 p_daily_cutoff_date := To_char(l_daily_cutoff_date,'J');
1451 p_weekly_cutoff_date := To_char(l_weekly_cutoff_date,'J');
1452 p_period_cutoff_date := To_char(l_period_cutoff_date,'J');
1453
1454 --LOG_MESSAGE(' 10 : '||p_daily_cutoff_date||' '||p_weekly_cutoff_date||' '||p_period_cutoff_date);
1455
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 -- IF mrdebug = TRUE THEN
1459 LOG_MESSAGE('Error in get_cutoff_dates :'|| to_char(sqlcode) || substr(sqlerrm,1,60));
1460 -- END IF;
1461 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
1462
1463 END get_cutoff_dates;
1464
1465 PROCEDURE form_get_bucket_cutoff_dates
1466 (
1467 p_plan_id IN NUMBER,
1468 p_org_id IN NUMBER,
1469 p_instance_id IN NUMBER,
1470 p_min_cutoff_bucket IN number,
1471 p_hour_cutoff_bucket IN number,
1472 p_daily_cutoff_bucket IN number,
1473 p_weekly_cutoff_bucket IN number,
1474 p_period_cutoff_bucket IN number,
1475 p_plan_completion_date OUT NOCOPY DATE,
1476 p_err_mesg OUT NOCOPY VARCHAR2
1477 ) IS
1478 l_daily_cutoff_date DATE;
1479 l_weekly_cutoff_date DATE;
1480 l_period_cutoff_date DATE;
1481 BEGIN
1482
1483 get_bucket_cutoff_dates(
1484 p_plan_id =>p_plan_id,
1485 p_org_id =>p_org_id,
1486 p_instance_id =>p_instance_id,
1487 p_plan_start_date =>Sysdate,
1488 p_plan_completion_date =>NULL, -- not used
1489 p_min_cutoff_bucket =>p_daily_cutoff_bucket,
1490 p_hour_cutoff_bucket =>p_daily_cutoff_bucket,
1491 p_daily_cutoff_bucket =>p_daily_cutoff_bucket,
1492 p_weekly_cutoff_bucket =>p_weekly_cutoff_bucket,
1493 p_period_cutoff_bucket =>p_period_cutoff_bucket,
1494 p_min_cutoff_date =>l_daily_cutoff_date,
1495 p_hour_cutoff_date =>l_daily_cutoff_date,
1496 p_daily_cutoff_date =>l_daily_cutoff_date,
1497 p_weekly_cutoff_date =>l_weekly_cutoff_date,
1498 p_period_cutoff_date =>l_period_cutoff_date,
1499 p_err_mesg =>p_err_mesg
1500 );
1501
1502 IF l_period_cutoff_date IS NOT NULL THEN
1503 p_plan_completion_date := l_period_cutoff_date;
1504 ELSIF l_weekly_cutoff_date IS NOT NULL THEN
1505 p_plan_completion_date := l_weekly_cutoff_date;
1506 ELSE
1507 p_plan_completion_date := l_daily_cutoff_date;
1508 END IF;
1509
1510 RETURN;
1511
1512 END form_get_bucket_cutoff_dates;
1513
1514 FUNCTION get_validation_org_id (p_sr_instance_id in NUMBER)
1515 return NUMBER IS
1516 l_org_id NUMBER;
1517 BEGIN
1518 select nvl(validation_org_id,-1) into l_org_id
1519 from msc_apps_instances
1520 where instance_id = p_sr_instance_id;
1521 return l_org_id;
1522 EXCEPTION WHEN OTHERS THEN
1523 return -1;
1524 END get_validation_org_id;
1525
1526 FUNCTION get_column_expression (p_column_name in VARCHAR2,
1527 p_index_owner in VARCHAR2,
1528 p_table_owner in VARCHAR2,
1529 p_index_name in VARCHAR2,
1530 p_table_name in VARCHAR2,
1531 p_column_position in number)
1532 return VARCHAR2 IS
1533 l_retval VARCHAR2(2000);
1534 l_longvar long;
1535 BEGIN
1536 select column_expression into l_longvar
1537 from all_ind_expressions
1538 where table_owner = p_table_owner
1539 and index_owner = p_index_owner
1540 and table_name = p_table_name
1541 and index_name = p_index_name
1542 and column_position = p_column_position;
1543 return (substr(l_longvar, 1, 2000));
1544 EXCEPTION WHEN NO_DATA_FOUND THEN
1545 return p_column_name;
1546
1547 END get_column_expression;
1548 /*
1549 FUNCTION get_ss_date (p_calendar_code VARCHAR2,
1550 p_plan_id IN NUMBER,
1551 p_owning_org_id IN NUMBER,
1552 p_owning_instance_id IN NUMBER,
1553 p_ss_org_id IN NUMBER,
1554 p_ss_instance_id IN NUMBER,
1555 p_ss_date IN NUMBER,
1556 p_plan_type IN NUMBER)
1557 return NUMBER IS
1558 l_bucket_type NUMBER;
1559 l_bucket_index NUMBER;
1560 l_bkt_end_date NUMBER;
1561 l_bkt_end_date1 NUMBER;
1562 l_bkt_end_date3 NUMBER;
1563 l_calendar_date NUMBER;
1564
1565 BEGIN
1566 IF ( p_owning_org_id IS NULL OR
1567 p_owning_instance_id IS NULL OR
1568 p_ss_date IS NULL OR
1569 p_calendar_code IS NULL ) THEN
1570 return null;
1571 END IF;
1572
1573
1574 BEGIN
1575 IF (p_plan_type <> 4 AND p_plan_type <> 9) THEN
1576 select own_org_bkt.bucket_type,
1577 own_org_bkt.bucket_index,
1578 to_number(to_char(own_org_bkt.bkt_end_date,'J')),
1579 to_number(to_char(nvl(org_bkt.bkt_end_date,sysdate),'J'))
1580 into l_bucket_type,
1581 l_bucket_index,
1582 l_bkt_end_date,
1583 l_bkt_end_date1
1584 from msc_plan_buckets own_org_bkt,
1585 msc_plan_buckets org_bkt
1586 where own_org_bkt.plan_id = p_plan_id
1587 and own_org_bkt.organization_id = p_owning_org_id
1588 and own_org_bkt.sr_instance_id = p_owning_instance_id
1589 and own_org_bkt.curr_flag = 1
1590 and ((own_org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
1591 own_org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
1592 (own_org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
1593 own_org_bkt.bucket_index = 1))
1594 and org_bkt.plan_id(+) = own_org_bkt.plan_id
1595 and org_bkt.organization_id(+) = p_owning_org_id
1596 and org_bkt.sr_instance_id(+) = p_owning_instance_id
1597 and org_bkt.curr_flag(+) = 1
1598 and org_bkt.bucket_index(+) = own_org_bkt.bucket_index-1;
1599
1600 IF (l_bucket_type = 1 or l_bucket_index = 1) THEN
1601 l_bkt_end_date3 := l_bkt_end_date;
1602 ELSE
1603 l_bkt_end_date3 := l_bkt_end_date1;
1604 END IF;
1605
1606 select to_number(to_char(cal2.calendar_date,'J'))
1607 into l_calendar_date
1608 from msc_calendar_dates cal1,
1609 msc_calendar_dates cal2
1610 where cal1.calendar_code = p_calendar_code
1611 and cal1.calendar_date = to_date(l_bkt_end_date3,'J')
1612 and cal1.exception_set_id = -1
1613 and cal1.sr_instance_id = p_ss_instance_id
1614 and cal2.seq_num = cal1.prior_seq_num
1615 and cal2.calendar_code = cal1.calendar_code
1616 and cal2.sr_instance_id = cal1.sr_instance_id
1617 and cal2.exception_set_id = -1;
1618 ELSE
1619 select to_number(to_char(cal2.calendar_date,'J'))
1620 into l_calendar_date
1621 from msc_plan_buckets org_bkt,
1622 msc_calendar_dates cal1,
1623 msc_calendar_dates cal2
1624 where org_bkt.plan_id = p_plan_id
1625 and org_bkt.organization_id = p_owning_org_id
1626 and org_bkt.sr_instance_id = p_owning_instance_id
1627 and org_bkt.curr_flag = 1
1628 and ((org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
1629 org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
1630 (org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
1631 org_bkt.bucket_index = 1))
1632 and cal1.calendar_code = p_calendar_code
1633 and cal1.calendar_date = org_bkt.bkt_start_date
1634 and cal1.exception_set_id = -1
1635 and cal1.sr_instance_id = p_ss_instance_id
1636 and cal2.seq_num = cal1.next_seq_num
1637 and cal2.calendar_code = cal1.calendar_code
1638 and cal2.sr_instance_id = cal1.sr_instance_id
1639 and cal2.exception_set_id = -1;
1640 END IF;
1641
1642
1643 return(l_calendar_date);
1644
1645 EXCEPTION WHEN NO_DATA_FOUND THEN
1646 return null;
1647 END;
1648
1649
1650
1651 END get_ss_date;
1652 */
1653 --modified body of msc_snapshot_pk.get_ss_date for Bug 5610482
1654 FUNCTION GET_SS_DATE (p_calendar_code VARCHAR2,
1655 p_plan_id IN NUMBER,
1656 p_owning_org_id IN NUMBER,
1657 p_owning_instance_id IN NUMBER,
1658 p_ss_org_id IN NUMBER,
1659 p_ss_instance_id IN NUMBER,
1660 p_ss_date IN NUMBER,
1661 p_plan_type IN NUMBER)
1662 return NUMBER IS
1663 l_bucket_type NUMBER;
1664 l_bucket_index NUMBER;
1665 l_bkt_end_date NUMBER;
1666 l_bkt_end_date1 NUMBER;
1667 l_bkt_end_date3 NUMBER;
1668 l_calendar_date NUMBER;
1669 BEGIN
1670 IF ( p_owning_org_id IS NULL OR
1671 p_owning_instance_id IS NULL OR
1672 p_ss_date IS NULL OR
1673 p_calendar_code IS NULL ) THEN
1674 return null;
1675 END IF;
1676
1677 BEGIN
1678 IF (p_plan_type <> 4 AND p_plan_type <> 9) THEN
1679 select to_number(to_char(cal1.prior_date,'J'))
1680 into l_calendar_date
1681 from msc_plan_buckets own_org_bkt,
1682 msc_plan_buckets org_bkt,
1683 msc_calendar_dates cal1
1684 where own_org_bkt.plan_id = p_plan_id
1685 and own_org_bkt.organization_id = p_owning_org_id
1686 and own_org_bkt.sr_instance_id = p_owning_instance_id
1687 and own_org_bkt.curr_flag = 1
1688 and ((own_org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
1689 own_org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
1690 (own_org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
1691 own_org_bkt.bucket_index = 1))
1692 and org_bkt.plan_id(+) = own_org_bkt.plan_id
1693 and org_bkt.organization_id(+) = p_owning_org_id
1694 and org_bkt.sr_instance_id(+) = p_owning_instance_id
1695 and org_bkt.curr_flag(+) = 1
1696 and org_bkt.bucket_index(+) = own_org_bkt.bucket_index-1
1697 and cal1.calendar_code = p_calendar_code
1698 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))))
1699 and cal1.exception_set_id = -1
1700 and cal1.sr_instance_id = p_ss_instance_id ;
1701 ELSE
1702 select to_number(to_char(cal1.next_date,'J'))
1703 into l_calendar_date
1704 from msc_plan_buckets org_bkt,
1705 msc_calendar_dates cal1
1706 where org_bkt.plan_id = p_plan_id
1707 and org_bkt.organization_id = p_owning_org_id
1708 and org_bkt.sr_instance_id = p_owning_instance_id
1709 and org_bkt.curr_flag = 1
1710 and ((org_bkt.bkt_start_date <= to_date(p_ss_date,'J') and
1711 org_bkt.bkt_end_date >= to_date(p_ss_date,'J')) OR
1712 (org_bkt.bkt_start_date > to_date(p_ss_date,'J') and
1713 org_bkt.bucket_index = 1))
1714 and cal1.calendar_code = p_calendar_code
1715 and cal1.calendar_date = org_bkt.bkt_start_date
1716 and cal1.exception_set_id = -1
1717 and cal1.sr_instance_id = p_ss_instance_id;
1718 END IF;
1719
1720 return(l_calendar_date);
1721
1722 EXCEPTION WHEN NO_DATA_FOUND THEN
1723 return null;
1724 END;
1725 END;
1726
1727
1728
1729 FUNCTION get_op_leadtime_percent(p_plan_id IN NUMBER,
1730 p_routing_seq_id IN NUMBER,
1731 p_sr_instance_id IN NUMBER,
1732 p_op_seq_num IN NUMBER)
1733 return NUMBER IS
1734 l_op_leadtime_percent NUMBER;
1735
1736 BEGIN
1737 BEGIN
1738 select nvl(mro.operation_lead_time_percent, 0.0)
1739 into l_op_leadtime_percent
1740 from
1741 msc_routing_operations mro
1742 where mro.plan_id = p_plan_id
1743 and mro.sr_instance_id = p_sr_instance_id
1744 and mro.routing_sequence_id = p_routing_seq_id
1745 and mro.operation_seq_num = p_op_seq_num
1746 and mro.effectivity_date <= sysdate
1747 and (mro.disable_date >= sysdate or
1748 mro.disable_date is NULL);
1749
1750 return(l_op_leadtime_percent);
1751
1752 EXCEPTION WHEN OTHERS THEN
1753 return 0.0;
1754 END;
1755
1756 END get_op_leadtime_percent;
1757
1758
1759
1760 procedure calculate_start_date(p_org_id IN NUMBER,
1761 p_sr_instance_id IN NUMBER,
1762 p_plan_start_date IN DATE,
1763 p_daily_cutoff_bucket IN NUMBER,
1764 p_weekly_cutoff_bucket IN NUMBER,
1765 p_period_cutoff_bucket IN NUMBER,
1766 p_daily_start_date OUT NOCOPY DATE,
1767 p_weekly_start_date OUT NOCOPY DATE,
1768 p_period_start_date OUT NOCOPY DATE,
1769 p_curr_cutoff_date OUT NOCOPY DATE) IS
1770 v_min_cutoff_date date;
1771 v_hour_cutoff_date date;
1772 v_daily_cutoff_date date;
1773 v_weekly_cutoff_date date;
1774 v_period_cutoff_date date;
1775 v_err_mesg varchar2(2000);
1776 v_daily_cutoff_bucket number;
1777 v_weekly_cutoff_bucket number;
1778 v_period_cutoff_bucket number;
1779
1780
1781 BEGIN
1782
1783 get_bucket_cutoff_dates(
1784 0, -- p_plan_id IN NUMBER,
1785 p_org_id, -- p_org_id IN NUMBER,
1786 p_sr_instance_id, -- IN NUMBER,
1787 p_plan_start_date, -- IN DATE,
1788 to_date(null), --p_plan_completion_date IN DATE,
1789 0, --p_min_cutoff_bucket IN number,
1790 0, --p_hour_cutoff_bucket IN number,
1791 p_daily_cutoff_bucket, -- IN number,
1792 p_weekly_cutoff_bucket, -- IN number,
1793 p_period_cutoff_bucket, -- IN number,
1794 v_min_cutoff_date, -- OUT DATE,
1795 v_hour_cutoff_date, -- OUT DATE,
1796 v_daily_cutoff_date, -- OUT DATE,
1797 v_weekly_cutoff_date, -- OUT DATE,
1798 v_period_cutoff_date, -- OUT DATE
1799 v_err_mesg) ;
1800
1801 p_daily_start_date := p_plan_start_date;
1802
1803 select min(cal.week_start_date)
1804 into p_weekly_start_date
1805 from msc_cal_week_start_dates cal,
1806 msc_trading_partners tp
1807 where cal.exception_set_id = tp.calendar_exception_set_id
1808 and cal.calendar_code = tp.calendar_code
1809 and cal.week_start_date >= trunc(v_daily_cutoff_date)
1810 and cal.sr_instance_id = tp.sr_instance_id
1811 and tp.sr_tp_id = p_org_id
1812 and tp.partner_type = 3;
1813
1814 if p_daily_cutoff_bucket = 0 and p_weekly_cutoff_bucket = 0 then
1815 p_weekly_start_date := p_daily_start_date;
1816 end if;
1817
1818 select min(cal.period_start_date)
1819 into p_period_start_date
1820 from msc_period_start_dates cal,
1821 msc_trading_partners tp
1822 where cal.exception_set_id = tp.calendar_exception_set_id
1823 and cal.calendar_code = tp.calendar_code
1824 and cal.period_start_date >= nvl(trunc(v_weekly_cutoff_date),
1825 trunc(v_daily_cutoff_date))
1826 and cal.sr_instance_id = tp.sr_instance_id
1827 and tp.sr_tp_id = p_org_id
1828 and tp.partner_type = 3;
1829
1830 if v_weekly_cutoff_bucket = 0 and v_period_cutoff_bucket = 0 then
1831 p_period_start_date := p_weekly_start_date;
1832 end if;
1833
1834 if v_period_cutoff_date is not null then
1835 p_curr_cutoff_date := v_period_cutoff_date;
1836 elsif v_weekly_cutoff_date is not null then
1837 p_curr_cutoff_date := v_weekly_cutoff_date;
1838 elsif v_daily_cutoff_date is not null then
1839 p_curr_cutoff_date := v_daily_cutoff_date;
1840 end if;
1841
1842 end calculate_start_date;
1843
1844 function calculate_start_date1(p_org_id IN NUMBER,
1845 p_sr_instance_id IN NUMBER,
1846 p_plan_start_date IN DATE,
1847 p_daily_cutoff_bucket IN NUMBER,
1848 p_weekly_cutoff_bucket IN NUMBER,
1849 p_period_cutoff_bucket IN NUMBER,
1850 P_start_date_bucket IN NUMBER) return Date is
1851 v_daily_start_date date;
1852 v_weekly_start_date date;
1853 v_period_start_date date;
1854 v_curr_cutoff_date date;
1855
1856 begin
1857 calculate_start_date(p_org_id ,
1858 p_sr_instance_id ,
1859 p_plan_start_date ,
1860 p_daily_cutoff_bucket ,
1861 p_weekly_cutoff_bucket ,
1862 p_period_cutoff_bucket ,
1863 v_daily_start_date ,
1864 v_weekly_start_date ,
1865 v_period_start_date ,
1866 v_curr_cutoff_date ) ;
1867
1868 if p_start_date_bucket = 1 then
1869 return v_daily_start_date;
1870 elsif p_start_date_bucket = 2 then
1871 return v_weekly_start_date;
1872 elsif p_start_date_bucket = 3 then
1873 return v_period_start_date;
1874 end if;
1875
1876 exception when others then
1877 return to_date(null);
1878
1879 end calculate_start_date1;
1880
1881 PROCEDURE update_items_info(
1882 p_err_mesg OUT NOCOPY VARCHAR2,
1883 p_plan_id in NUMBER
1884 ) IS
1885 lv_fetchComplete Boolean;
1886
1887 TYPE NumTblTyp IS TABLE OF NUMBER;
1888 TYPE Char1TblTyp IS TABLE OF VARCHAR2(250);
1889 TYPE Char2TblTyp IS TABLE OF VARCHAR2(240);
1890 TYPE Char3TblTyp IS TABLE OF VARCHAR2(10);
1891 TYPE Char4TblTyp IS TABLE OF VARCHAR2(3);
1892
1893 lb_inv_item_id NumTblTyp;
1894 lb_org_id NumTblTyp;
1895 lb_val_org_id NumTblTyp;
1896 lb_sr_instance_id NumTblTyp;
1897
1898 lb_item_name Char1TblTyp;
1899 lb_description Char2TblTyp;
1900 lb_buyer_name Char2TblTyp;
1901 lb_planner_code Char3TblTyp;
1902 lb_plng_excp_set Char3TblTyp;
1903 lb_revision Char4TblTyp;
1904
1905 ln_rows_to_fetch Number := nvl(TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE')),75000);
1906 lv_plan_partition_exists Number := 0;
1907
1908 CURSOR items_for_update
1909 IS
1910 SELECT /*+ ORDERED USE_NL(ODS, MSC_SYSTEM_ITEMS_U1) */ ODS.INVENTORY_ITEM_ID,
1911 PDS.ORGANIZATION_ID,
1912 ODS.SR_INSTANCE_ID,
1913 ODS.ITEM_NAME,
1914 ODS.DESCRIPTION,
1915 ODS.BUYER_NAME,
1916 ODS.PLANNER_CODE,
1917 ODS.PLANNING_EXCEPTION_SET,
1918 ODS.REVISION,
1919 -1
1920 FROM MSC_SYSTEM_ITEMS PDS,
1921 MSC_SYSTEM_ITEMS ODS
1922 WHERE ODS.PLAN_ID = -1
1923 AND ODS.INVENTORY_ITEM_ID = PDS.INVENTORY_ITEM_ID
1924 AND ODS.ORGANIZATION_ID = DECODE(PDS.ORGANIZATION_ID, -1,
1925 msc_snapshot_pk.get_validation_org_id(PDS.SR_INSTANCE_ID), PDS.ORGANIZATION_ID)
1926 AND ODS.SR_INSTANCE_ID = PDS.SR_INSTANCE_ID
1927 AND PDS.PLAN_ID = p_plan_id;
1928 BEGIN
1929
1930 SELECT count(*)
1931 INTO lv_plan_partition_exists
1932 FROM MSC_PLAN_PARTITIONS
1933 WHERE plan_id = p_plan_id;
1934
1935 IF (lv_plan_partition_exists > 0) THEN
1936
1937 LOG_MESSAGE('Analysing MSC_SYSTEM_ITEMS plan partition ');
1938
1939 msc_analyse_tables_pk.analyse_table( 'MSC_SYSTEM_ITEMS', -1, p_plan_id);
1940
1941 ELSE
1942
1943 -- LOG_MESSAGE('Analysing MSC_SYSTEM_ITEMS table ');
1944
1945 -- msc_analyse_tables_pk.analyse_table( 'MSC_SYSTEM_ITEMS' );
1946
1947 null;
1948
1949 END IF;
1950
1951 LOG_MESSAGE('Updating the Items Table ');
1952
1953 lv_fetchComplete := FALSE;
1954
1955 OPEN items_for_update;
1956 IF (items_for_update%ISOPEN) THEN
1957 LOOP
1958 IF (lv_fetchComplete) THEN
1959 EXIT;
1960 END IF;
1961
1962 FETCH items_for_update
1963 BULK COLLECT
1964 INTO lb_inv_item_id,
1965 lb_org_id,
1966 lb_sr_instance_id,
1967 lb_item_name,
1968 lb_description,
1969 lb_buyer_name,
1970 lb_planner_code,
1971 lb_plng_excp_set,
1972 lb_revision,
1973 lb_val_org_id
1974 LIMIT ln_rows_to_fetch;
1975
1976 EXIT WHEN lb_inv_item_id.count = 0;
1977
1978 IF (items_for_update%NOTFOUND) THEN
1979 lv_fetchComplete := TRUE;
1980 END IF;
1981
1982 FORALL j IN lb_inv_item_id.FIRST..lb_inv_item_id.LAST
1983 UPDATE MSC_SYSTEM_ITEMS
1984 SET item_name = lb_item_name(j),
1985 description = lb_description(j),
1986 buyer_name = lb_buyer_name(j),
1987 planner_code = lb_planner_code(j),
1988 planning_exception_set = lb_plng_excp_set(j),
1989 revision = lb_revision(j)
1990 WHERE sr_instance_id = lb_sr_instance_id(j)
1991 AND inventory_item_id = lb_inv_item_id(j)
1992 --AND (organization_id = lb_org_id(j) OR
1993 -- (organization_id = -1 AND lb_val_org_id(j) = lb_org_id(j)
1994 -- ))
1995 AND organization_id = lb_org_id(j)
1996 AND plan_id = p_plan_id;
1997
1998 COMMIT;
1999
2000 END LOOP;
2001
2002 END IF;
2003
2004 IF (items_for_update%ISOPEN) THEN
2005 CLOSE items_for_update;
2006 END IF;
2007
2008 COMMIT;
2009
2010 LOG_MESSAGE('Completed updating the Items Table ');
2011 EXCEPTION WHEN OTHERS then
2012 LOG_MESSAGE('RETCODE -'||SQLCODE);
2013 LOG_MESSAGE(SQLERRM);
2014 p_err_mesg := to_char(sqlcode)||substr(sqlerrm,1,60);
2015 END update_items_info;
2016
2017 FUNCTION f_period_start_date(p_plan_id IN NUMBER,
2018 p_instance_id IN NUMBER,
2019 p_org_id IN NUMBER,
2020 p_item_id IN NUMBER) return date is
2021 l_date date;
2022 begin
2023 SELECT TRUNC(nvl(max(period_start_date),sysdate))
2024 into l_date
2025 FROM msc_safety_stocks
2026 WHERE period_start_date <= TRUNC(SYSDATE)
2027 AND inventory_item_id = p_item_id
2028 AND sr_instance_id = p_instance_id
2029 AND organization_id = p_org_id
2030 AND plan_id = p_plan_id;
2031 return l_date;
2032
2033 end;
2034
2035 END MSC_SNAPSHOT_PK; -- package