[Home] [Help]
PACKAGE BODY: APPS.MSD_TRANSLATE_TIME_DATA
Source
1 PACKAGE BODY MSD_TRANSLATE_TIME_DATA AS
2 /* $Header: msdttimb.pls 120.0.12020000.2 2012/09/14 10:47:57 rissingh ship $ */
3
4 /* Private Global Variables **/
5 g_seq_num NUMBER := 0 ;
6
7
8 -- Public Procedures
9
10 procedure translate_time_data(
11 errbuf OUT NOCOPY VARCHAR2,
12 retcode OUT NOCOPY VARCHAR2,
13 p_source_table IN VARCHAR2,
14 p_dest_table IN VARCHAR2,
15 p_instance_id IN NUMBER,
16 p_calendar_type_id IN NUMBER,
17 p_calendar_code IN VARCHAR2,
18 p_from_date IN DATE,
19 p_to_date IN DATE) IS
20 v_instance_id varchar2(40);
21 v_retcode number;
22 v_sql_stmt varchar2(4000);
23 x_dblink varchar2(128);
24 TYPE Fiscal_Month_Cursor IS REF CURSOR;
25 TYPE Update_Cursor IS REF CURSOR;
26 Fiscal_Month_Cur Fiscal_Month_Cursor ;
27 Update_Cur Update_Cursor ;
28 x_calendar_code varchar2(15);
29 x_SEQ_NUM NUMBER;
30 x_YEAR VARCHAR2(15);
31 x_YEAR_DESCRIPTION VARCHAR2(15);
32 x_YEAR_START_DATE DATE;
33 x_YEAR_END_DATE DATE;
34 x_QUARTER VARCHAR2(15);
35 x_QUARTER_DESCRIPTION VARCHAR2(15);
36 x_QUARTER_START_DATE DATE;
37 x_QUARTER_END_DATE DATE;
38 x_MONTH VARCHAR2(15);
39 x_MONTH_DESCRIPTION VARCHAR2(15);
40 x_MONTH_START_DATE DATE;
41 x_MONTH_END_DATE DATE;
42 x_max_days DATE ;
43
44 l_calendar_code VARCHAR2(20);
45 v_day_range_stmt VARCHAR2(4000);
46 v_month_range_stmt VARCHAR2(4000);
47
48 Begin
49
50
51 /**************************************************
52 - 1. Get the instance id from MSC_APP_INSTANCE
53 - 2. Get the Profile Value for MSD_DIRECT_LOAD
54 - to identify whether we need to insert the
55 - data into the staging tables or the
56 - fact tables.
57 - 3. Check for the Data Duplication, we should
58 - use the forecast_designator for this fact.
59 - 4. Insert the Data accordingly into the
60 - Staging or the Fact table based on the
61 - MSD_SR_MFG_FCST_V
62 - 5. Commit
63 ****************************************************/
64
65
66 retcode :=0;
67 Savepoint Before_Delete ;
68
69
70 IF (p_calendar_type_id <> MSD_COMMON_UTILITIES.GREGORIAN_CALENDAR ) THEN
71 msd_common_utilities.get_db_link(p_instance_id, x_dblink, retcode);
72 if (retcode = -1) then
73 retcode :=-1;
74 return;
75 end if;
76 END IF;
77
78
79 /* TEST DWK */
80 IF (p_calendar_type_id = MSD_COMMON_UTILITIES.GREGORIAN_CALENDAR ) THEN
81 l_calendar_code := 'GREGORIAN';
82 ELSE
83 l_calendar_code := p_calendar_code;
84 END IF;
85
86 /* DWK Delete existing data from MSD_TIME before collection */
87
88 if (l_calendar_code is null) then
89 delete from msd_time
90 where calendar_type = p_calendar_type_id;
91 else
92 delete from msd_time
93 where calendar_code = l_calendar_code
94 and calendar_type = p_calendar_type_id;
95 end if;
96
97 v_day_range_stmt := ' and day between :p_from_date AND :p_to_date ';
98 v_month_range_stmt := ' and month_end_date between :p_from_date AND :p_to_date ';
99
100
101 /* You need to generate all the Gregorian Hierarchy for this
102 Date range mentioned below */
103 /* DWK outer IF 1 */
104 IF (p_calendar_type_id = MSD_COMMON_UTILITIES.GREGORIAN_CALENDAR ) then
105
106 Generate_Gregorian( errbuf,
107 retcode,
108 l_calendar_code,
109 p_from_date,
110 p_to_date ) ;
111
112 /* In this section the Hierarchy is already exploded to the Day Level */
113 /* ELSIF for outer IF 1 */
114 ELSIF (p_calendar_type_id = MSD_COMMON_UTILITIES.MANUFACTURING_CALENDAR) or
115 ( ( (p_calendar_type_id = MSD_COMMON_UTILITIES.FISCAL_CALENDAR)
116 or
117 (p_calendar_type_id = MSD_COMMON_UTILITIES.COMPOSITE_CALENDAR)
118
119 )
120 and
121 (p_source_table = MSD_COMMON_UTILITIES.TIME_STAGING_TABLE) ) then
122
123 v_sql_stmt := 'insert into ' || p_dest_table || ' ( ' ||
124 'instance, ' ||
125 'calendar_type, ' ||
126 'calendar_code, ' ||
127 'seq_num, ' ||
128 'YEAR, ' ||
129 'YEAR_DESCRIPTION, ' ||
130 'YEAR_START_DATE, ' ||
131 'YEAR_END_DATE, ' ||
132 'QUARTER, ' ||
133 'QUARTER_DESCRIPTION, ' ||
134 'QUARTER_START_DATE, ' ||
135 'QUARTER_END_DATE, ' ||
136 'MONTH, ' ||
137 'MONTH_DESCRIPTION, ' ||
138 'MONTH_START_DATE, ' ||
139 'MONTH_END_DATE, ' ||
140 'WEEK, ' ||
141 'WEEK_DESCRIPTION, ' ||
142 'WEEK_START_DATE, ' ||
143 'WEEK_END_DATE, ' ||
144 'DAY, ' ||
145 'DAY_DESCRIPTION, ' ||
146 'LAST_UPDATE_DATE, ' ||
147 'last_updated_by, ' ||
148 'creation_date, ' ||
149 'created_by, ' ||
150 'LAST_UPDATE_LOGIN ) ' ||
151 'select ''' ||
152 p_instance_id ||''', ' ||
153 p_calendar_type_id ||
154 ', calendar_code, ' ||
155 'seq_num, ' ||
156 'YEAR, ' ||
157 'YEAR_DESCRIPTION, ' ||
158 'YEAR_START_DATE, ' ||
159 'YEAR_END_DATE, ' ||
160 'QUARTER, ' ||
161 'QUARTER_DESCRIPTION, ' ||
162 'QUARTER_START_DATE, ' ||
163 'QUARTER_END_DATE, ' ||
164 'MONTH, ' ||
165 'MONTH_DESCRIPTION, ' ||
166 'MONTH_START_DATE, ' ||
167 'MONTH_END_DATE, ' ||
168 'WEEK, ' ||
169 'WEEK_DESCRIPTION, ' ||
170 'WEEK_START_DATE, ' ||
171 'WEEK_END_DATE, ' ||
172 'DAY, ' ||
173 'DAY_DESCRIPTION, ' ||
174 'sysdate, ' ||
175 FND_GLOBAL.USER_ID || ', ' ||
176 'sysdate, ' ||
177 FND_GLOBAL.USER_ID || ', ' ||
178 FND_GLOBAL.USER_ID || ' ' ||
179 'from ' ||
180 p_source_table ||
181 ' where calendar_code = NVL(:l_calendar_code, calendar_code)' ;
182
183 -- If it is from the staging area then we need to add the
184 -- filter for unique instance id
185 if (p_source_table = MSD_COMMON_UTILITIES.TIME_STAGING_TABLE) then
186 v_sql_stmt := v_sql_stmt || ' and instance = :p_instance_id ';
187 end if ;
188
189 if ((p_calendar_type_id = MSD_COMMON_UTILITIES.MANUFACTURING_CALENDAR)
190 and
191 (p_source_table <> MSD_COMMON_UTILITIES.TIME_STAGING_TABLE)
192 and (l_calendar_code is null)) then
193 v_sql_stmt := v_sql_stmt || ' and calendar_code in (SELECT distinct mod.calendar_code FROM mtl_parameters';
194 v_sql_stmt := v_sql_stmt || x_dblink || ' mod' || ', msd_app_instance_orgs' || x_dblink || ' maio' || ' WHERE mod.organization_id = maio.organization_id) ';
195 end if;
196
197
198 /* DWK Populate dates which falls within day range */
199 v_sql_stmt := v_sql_stmt || v_day_range_stmt;
200
201
202 if (p_source_table = MSD_COMMON_UTILITIES.TIME_STAGING_TABLE) then
203 EXECUTE IMMEDIATE v_sql_stmt
204 using l_calendar_code,
205 p_instance_id,
206 nvl(p_from_date, to_date('01-01-0001', 'DD-MM-RRRR')),
207 nvl(p_to_date, to_date('01-01-9999', 'DD-MM-RRRR'));
208 else
209
210 EXECUTE IMMEDIATE v_sql_stmt
211 using l_calendar_code,
212 nvl(p_from_date, to_date('01-01-0001', 'DD-MM-RRRR')),
213 nvl(p_to_date, to_date('01-01-9999', 'DD-MM-RRRR'));
214 end if;
215
216 /* In this range we need to get the information for the Days */
217 /* for the Fiscal Calendar from the source views */
218 /* DWK ELSE for outer IF 1 */
219 ELSE
220 v_sql_stmt := 'select ' ||
221 ' calendar_code, ' ||
222 ' YEAR, ' ||
223 ' YEAR_DESCRIPTION, ' ||
224 ' YEAR_START_DATE, ' ||
225 ' YEAR_END_DATE, ' ||
226 ' QUARTER, ' ||
227 ' QUARTER_DESCRIPTION, ' ||
228 ' QUARTER_START_DATE, ' ||
229 ' QUARTER_END_DATE, ' ||
230 ' MONTH, ' ||
231 ' MONTH_DESCRIPTION, ' ||
232 ' MONTH_START_DATE, ' ||
233 ' MONTH_END_DATE ' ||
234 ' from ' ||
235 p_source_table ||
236 ' where calendar_code = NVL( :p_calendar_code ' ||
237 ', calendar_code) ' || v_month_range_stmt;
238
239
240 OPEN Fiscal_Month_Cur FOR v_sql_stmt
241 using p_calendar_code,
242 nvl(p_from_date, to_date('01-01-0001', 'DD-MM-RRRR')),
243 nvl(p_to_date, to_date('01-01-9999', 'DD-MM-RRRR'));
244
245 LOOP
246 FETCH Fiscal_Month_Cur
247 INTO x_calendar_code,
248 x_YEAR,
249 x_YEAR_DESCRIPTION,
250 x_YEAR_START_DATE,
251 x_YEAR_END_DATE,
252 x_QUARTER,
253 x_QUARTER_DESCRIPTION,
254 x_QUARTER_START_DATE,
255 x_QUARTER_END_DATE,
256 x_MONTH,
257 x_MONTH_DESCRIPTION,
258 x_MONTH_START_DATE,
259 x_MONTH_END_DATE;
260
261 EXIT WHEN Fiscal_Month_Cur%NOTFOUND;
262
263 Explode_Fiscal_Dates(
264 errbuf => errbuf,
265 retcode => retcode,
266 p_dest_table => p_dest_table,
267 p_instance_id => p_instance_id,
268 p_calendar_type_id => p_calendar_type_id,
269 p_calendar_code => x_calendar_code,
270 p_seq_num => null,
271 p_year => x_year,
272 p_year_description => x_year_description,
273 p_year_start_date => x_year_start_date,
274 p_year_end_date => x_year_end_date,
275 p_quarter => x_quarter,
276 p_quarter_description => x_quarter_description,
277 p_quarter_start_date => x_quarter_start_date,
278 p_quarter_end_date => x_quarter_end_date,
279 p_month => x_month,
280 p_month_description => x_month_description,
281 p_month_start_date => x_month_start_date,
282 p_month_end_date => x_month_end_date,
283 p_from_date => p_from_date,
284 p_to_date => p_to_date );
285
286 END LOOP;
287 CLOSE Fiscal_Month_Cur;
288
289 END IF; /* End of outer IF 1 */
290
291 -- fill in missing dates for manufacturing calendar on the fact
292 if (p_calendar_type_id = MSD_COMMON_UTILITIES.MANUFACTURING_CALENDAR) and
293 (p_dest_table = MSD_COMMON_UTILITIES.TIME_FACT_TABLE) then
294 fix_manufacturing(errbuf, retcode, p_calendar_code);
295 end if;
296
297 COMMIT;
298
299
300 EXCEPTION
301
302 when others then
303
304 errbuf := substr(SQLERRM,1,150);
305 fnd_file.put_line(fnd_file.log, substr(SQLERRM,1,1000));
306 retcode := -1 ;
307 rollback to Savepoint Before_Delete ;
308
309
310 END translate_time_data ;
311
312
313 /* Even though the Start and End dates are passed we do not use
314 them as we do not use them for fiscal calendar
315 */
316 procedure Explode_Fiscal_Dates(
317 errbuf OUT NOCOPY VARCHAR2,
318 retcode OUT NOCOPY VARCHAR2,
319 p_dest_table IN VARCHAR2,
320 p_instance_id IN NUMBER,
321 p_calendar_type_id IN NUMBER,
322 p_calendar_code IN VARCHAR2,
323 p_seq_num IN NUMBER,
324 p_year IN VARCHAR2,
325 p_year_description IN VARCHAR2,
326 p_year_start_date IN DATE,
327 p_year_end_date IN DATE,
328 p_quarter IN VARCHAR2,
329 p_quarter_description IN VARCHAR2,
330 p_quarter_start_date IN DATE,
331 p_quarter_end_date IN DATE,
332 p_month IN VARCHAR2,
333 p_month_description IN VARCHAR2,
334 p_month_start_date IN DATE,
335 p_month_end_date IN DATE,
336 p_from_date IN DATE,
337 p_to_date IN DATE) IS
338
339 v_num_of_days NUMBER;
340 v_current_date DATE ;
341 x_count NUMBER;
342 Begin
343
344 x_count := p_month_end_date - p_month_start_date ;
345
346 if (p_dest_table = MSD_COMMON_UTILITIES.TIME_FACT_TABLE) then
347
348 For v_num_of_days in 0..(p_month_end_date - p_month_start_date) LOOP
349
350 g_seq_num := g_seq_num + 1 ;
351
352 insert into msd_time (
353 instance,
354 calendar_type,
355 calendar_code,
356 seq_num,
357 YEAR,
358 YEAR_DESCRIPTION,
359 YEAR_START_DATE,
360 YEAR_END_DATE,
361 QUARTER,
362 QUARTER_DESCRIPTION,
363 QUARTER_START_DATE,
364 QUARTER_END_DATE,
365 MONTH,
366 MONTH_DESCRIPTION,
367 MONTH_START_DATE,
368 MONTH_END_DATE,
369 DAY,
370 DAY_DESCRIPTION,
371 LAST_UPDATE_DATE,
372 last_updated_by,
373 creation_date,
374 created_by,
375 LAST_UPDATE_LOGIN )
376 values(
377 p_instance_id,
378 p_calendar_type_id,
379 p_calendar_code,
380 g_seq_num,
381 p_year,
382 p_year_description,
383 p_year_start_date,
384 p_year_end_date,
385 p_quarter,
386 p_quarter_description,
387 p_quarter_start_date,
388 p_quarter_end_date,
389 p_month,
390 p_month_description,
391 p_month_start_date,
395 sysdate,
392 p_month_end_date,
393 p_month_start_date+v_num_of_days,
394 p_month_start_date+v_num_of_days,
396 FND_GLOBAL.USER_ID ,
397 sysdate,
398 FND_GLOBAL.USER_ID ,
399 FND_GLOBAL.USER_ID
400 ) ;
401
402
403 End Loop ;
404
405 elsif (p_dest_table = MSD_COMMON_UTILITIES.TIME_STAGING_TABLE) then
406
407 For v_num_of_days in 0..(p_month_end_date - p_month_start_date) LOOP
408
409 g_seq_num := g_seq_num + 1 ;
410
411 insert into msd_st_time (
412 instance,
413 calendar_type,
414 calendar_code,
415 seq_num,
416 YEAR,
417 YEAR_DESCRIPTION,
418 YEAR_START_DATE,
419 YEAR_END_DATE,
420 QUARTER,
421 QUARTER_DESCRIPTION,
422 QUARTER_START_DATE,
423 QUARTER_END_DATE,
424 MONTH,
425 MONTH_DESCRIPTION,
426 MONTH_START_DATE,
427 MONTH_END_DATE,
428 DAY,
429 DAY_DESCRIPTION,
430 LAST_UPDATE_DATE,
431 last_updated_by,
432 creation_date,
433 created_by,
434 LAST_UPDATE_LOGIN )
435 values(
436 p_instance_id,
437 p_calendar_type_id,
438 p_calendar_code,
439 g_seq_num,
440 p_year,
441 p_year_description,
442 p_year_start_date,
443 p_year_end_date,
444 p_quarter,
445 p_quarter_description,
446 p_quarter_start_date,
447 p_quarter_end_date,
448 p_month,
449 p_month_description,
450 p_month_start_date,
451 p_month_end_date,
452 p_month_start_date+v_num_of_days,
453 p_month_start_date+v_num_of_days,
454 sysdate,
455 FND_GLOBAL.USER_ID ,
456 sysdate,
457 FND_GLOBAL.USER_ID ,
458 FND_GLOBAL.USER_ID
459 ) ;
460
461 End Loop ;
462
463 End if ;
464
465
466 exception
467
468 when others then
469 fnd_file.put_line(fnd_file.log, substr(SQLERRM,1,1000));
470 errbuf := substr(SQLERRM,1,150);
471 retcode := -1 ;
472
473
474 End Explode_Fiscal_Dates ;
475
476
477 procedure Generate_Gregorian(
478 errbuf OUT NOCOPY VARCHAR2,
479 retcode OUT NOCOPY VARCHAR2,
480 p_calendar_code IN VARCHAR2,
481 p_from_date IN DATE,
482 p_to_date IN DATE ) IS
483 v_instance_id varchar2(40);
484 v_retcode number;
485 v_sql_stmt varchar2(4000);
486 v_num_of_days number ;
487 v_seq number ;
488 x_count number ;
489 Begin
490
491 x_count := p_to_date - p_from_date ;
492
493 For v_num_of_days in 0..x_count LOOP
494
495 v_seq := v_num_of_days + 1 ;
496
497 insert into msd_time (
498 instance,
499 calendar_type,
500 calendar_code,
501 seq_num,
502 YEAR,
503 YEAR_DESCRIPTION,
504 YEAR_START_DATE,
505 YEAR_END_DATE,
506 QUARTER,
507 QUARTER_DESCRIPTION,
508 QUARTER_START_DATE,
509 QUARTER_END_DATE,
510 MONTH,
511 MONTH_DESCRIPTION,
512 MONTH_START_DATE,
513 MONTH_END_DATE,
514 DAY,
515 DAY_DESCRIPTION,
516 LAST_UPDATE_DATE,
517 last_updated_by,
518 creation_date,
519 created_by,
520 LAST_UPDATE_LOGIN )
521 values (
522 -1,
523 1,
524 p_calendar_code,
525 v_seq,
526 to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
527 to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
528 fnd_date.string_to_date('01-JAN-'||to_char(p_from_date+v_num_of_days,'YYYY'),
529 'DD-MON-YYYY'),
530 fnd_date.string_to_date('31-DEC-'||to_char(p_from_date+v_num_of_days,'YYYY'),
531 'DD-MON-YYYY'),
532 decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
533 '01', 'Qtr 1',
534 '02', 'Qtr 1',
535 '03', 'Qtr 1',
536 '04', 'Qtr 2',
537 '05', 'Qtr 2',
538 '06', 'Qtr 2',
539 '07', 'Qtr 3',
540 '08', 'Qtr 3',
541 '09', 'Qtr 3',
542 '10', 'Qtr 4',
543 '11', 'Qtr 4',
547 '02', 'Qtr 1',
544 '12', 'Qtr 4') || ' ' || to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
545 decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
546 '01', 'Qtr 1',
548 '03', 'Qtr 1',
549 '04', 'Qtr 2',
550 '05', 'Qtr 2',
551 '06', 'Qtr 2',
552 '07', 'Qtr 3',
553 '08', 'Qtr 3',
554 '09', 'Qtr 3',
555 '10', 'Qtr 4',
556 '11', 'Qtr 4',
557 '12', 'Qtr 4') || ' ' || to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
558 fnd_date.string_to_date(decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
559 '01', '01-JAN-',
560 '02', '01-JAN-',
561 '03', '01-JAN-',
562 '04', '01-APR-',
563 '05', '01-APR-',
564 '06', '01-APR-',
565 '07', '01-JUL-',
566 '08', '01-JUL-',
567 '09', '01-JUL-',
568 '10', '01-OCT-',
569 '11', '01-OCT-',
570 '12', '01-OCT-')||to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
571 'DD-MON-YYYY'),
572 fnd_date.string_to_date(decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
573 '01', '31-MAR-',
574 '02', '31-MAR-',
575 '03', '31-MAR-',
576 '04', '30-JUN-',
577 '05', '30-JUN-',
578 '06', '30-JUN-',
579 '07', '30-SEP-',
580 '08', '30-SEP-',
581 '09', '30-SEP-',
582 '10', '31-DEC-',
583 '11', '31-DEC-',
584 '12', '31-DEC-')||to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
585 'DD-MON-YYYY'),
586 to_char(p_from_date+v_num_of_days,'MON','nls_date_language = AMERICAN')||' '||
587 to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
588 to_char(p_from_date+v_num_of_days,'MON','nls_date_language = AMERICAN')||' '||
589 to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
590 fnd_date.string_to_date(decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
591 '01', '01-JAN-',
592 '02', '01-FEB-',
593 '03', '01-MAR-',
594 '04', '01-APR-',
595 '05', '01-MAY-',
596 '06', '01-JUN-',
597 '07', '01-JUL-',
598 '08', '01-AUG-',
599 '09', '01-SEP-',
600 '10', '01-OCT-',
601 '11', '01-NOV-',
602 '12', '01-DEC-')||to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
603 'DD-MON-YYYY'),
604 fnd_date.string_to_date(decode(to_char(p_from_date+v_num_of_days,'MM','nls_date_language = AMERICAN'),
605 '01', '31-JAN-',
606 '02', decode(mod(to_number(
607 to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN')),4),
608 0, '29-FEB-',
609 '28-FEB-'),
610 '03', '31-MAR-',
611 '04', '30-APR-',
612 '05', '31-MAY-',
613 '06', '30-JUN-',
614 '07', '31-JUL-',
615 '08', '31-AUG-',
616 '09', '30-SEP-',
617 '10', '31-OCT-',
618 '11', '30-NOV-',
619 '12', '31-DEC-')||to_char(p_from_date+v_num_of_days,'YYYY','nls_date_language = AMERICAN'),
620 'DD-MON-YYYY'),
621 ((p_from_date)+(v_num_of_days)),
622 to_char((p_from_date)+(v_num_of_days), 'DD-MON-YYYY','nls_date_language = AMERICAN'),
623 sysdate,
624 FND_GLOBAL.USER_ID ,
625 sysdate,
626 FND_GLOBAL.USER_ID ,
627 FND_GLOBAL.USER_ID
628 ) ;
629
630 End Loop ;
631
632 return ;
633
634 exception
635
636 when others then
637 fnd_file.put_line(fnd_file.log, substr(SQLERRM,1,1000));
638 errbuf := substr(SQLERRM,1,150);
639 retcode := -1 ;
640
641 End Generate_Gregorian ;
642
643
644 procedure fix_manufacturing(errbuf out nocopy varchar2,
645 retcode out nocopy varchar2,
646 p_cal_code in varchar2) is
647
648 -- distinct weeks by calendar
649 cursor weeks is
650 select distinct
651 week_start_date sd,
652 week_end_date ed,
653 week,
654 week_description,
655 month,
656 month_description,
657 calendar_code,
658 month_start_date,
659 month_end_date,
660 instance
661 from msd_time
662 where calendar_type = 2
663 and calendar_code = nvl(p_cal_code, calendar_code);
664
665 begin
666
667 retcode := 0;
668
669 for week in weeks loop
670
671 -- insert missing days in this week
672 insert into msd_time(INSTANCE, CALENDAR_TYPE, CALENDAR_CODE, SEQ_NUM,
673 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
674 CREATED_BY, LAST_UPDATE_LOGIN,
675 MONTH, MONTH_DESCRIPTION,
676 MONTH_START_DATE, MONTH_END_DATE,
677 WEEK, WEEK_DESCRIPTION,
678 WEEK_START_DATE, WEEK_END_DATE,
679 DAY, DAY_DESCRIPTION,
680 WORKING_DAY)
681 select week.instance, 2, week.calendar_code, -1,
682 sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.user_id,
683 week.month, week.month_description,
684 week.month_start_date, week.month_end_date,
685 week.week, week.week_description,
686 week.sd, week.ed,
687 day, to_char(day),
688 'NO'
689 from
690 (
691 select week.sd+rownum-1 day
692 from msd_time
693 where rownum < week.ed-week.sd+2
694 MINUS
695 select day
696 from msd_time
697 where calendar_type = 2
698 and calendar_code = week.calendar_code
699 and week_start_date = week.sd
700 and week_end_date = week.ed
701 );
702 end loop;
703
704 exception
705 when others then
706 fnd_file.put_line(fnd_file.log, substr(SQLERRM,1,1000));
707 errbuf := substr(SQLERRM,1,150);
708 retcode := -1;
709
710 end fix_manufacturing;
711
712
713
714 END MSD_TRANSLATE_TIME_DATA ;