[Home] [Help]
PACKAGE BODY: APPS.DDR_POP_CALENDAR_PKG
Source
1 PACKAGE BODY ddr_pop_calendar_pkg AS
2 /* $Header: ddrcldrb.pls 120.6 2008/04/15 12:35:04 bjayaram noship $ */
3
4 /* SURROGATE KEY format for each level */
5 g_YR_ID_format VARCHAR2(10) := 'YYYYMMDD'; -- Start day of Year
6 g_QTR_ID_format VARCHAR2(10) := 'YYYYMMDD'; -- Start day of Quarter
7 g_MNTH_ID_format VARCHAR2(10) := 'YYYYMMDD'; -- Start day of Month
8 g_PRD_ID_format VARCHAR2(10) := 'YYYYMMDD'; -- Start day of Period
9 g_WK_ID_format VARCHAR2(10) := 'YYYYMMDD'; -- Start day of Week
10 g_DAY_ID_format VARCHAR2(10) := 'YYYYMMDD'; -- Each Day
11
12 g_src_sys_idnt VARCHAR2(40) := 'SQL-Script';
13 g_src_sys_dt DATE := sysdate;
14 g_crtd_by_DSR VARCHAR2(30) := USER;
15 g_last_updt_by_DSR VARCHAR2(30) := USER;
16 g_created_by NUMBER(15) := -1;
17 g_creation_date DATE := sysdate;
18 g_last_updated_by NUMBER(15) := -1;
19 g_last_update_date DATE := sysdate;
20 g_last_update_login NUMBER(15) := -1;
21
22 PROCEDURE Raise_Error (p_error_text IN VARCHAR2)
23 IS
24 l_error_text VARCHAR2(240);
25 BEGIN
26 l_error_text := p_error_text;
27 Raise_Application_Error(-20001,l_error_text);
28 END;
29
30 PROCEDURE Get_Last_Year_Details (
31 p_clndr_type IN VARCHAR2,
32 p_org_code IN VARCHAR2,
33 p_last_year OUT NOCOPY NUMBER,
34 p_last_year_start_dt OUT NOCOPY DATE,
35 p_last_year_end_dt OUT NOCOPY DATE
36 )
37 IS
38 l_SQL_str VARCHAR2(500) := null;
39 l_table_name VARCHAR2(30);
40 BEGIN
41 IF p_clndr_type = 'CLNDR' THEN l_table_name := 'DDR_R_CLNDR_YR';
42 ELSIF p_clndr_type = 'BSNS' THEN l_table_name := 'DDR_R_BSNS_YR';
43 ELSIF p_clndr_type = 'FSCL' THEN l_table_name := 'DDR_R_FSCL_YR';
44 ELSIF p_clndr_type = 'ADVR' THEN l_table_name := 'DDR_R_ADVR_YR';
45 ELSIF p_clndr_type = 'PLNG' THEN l_table_name := 'DDR_R_PLNG_YR';
46 END IF;
47
48 /*
49 SELECT yr_strt_dt, yr_end_dt
50 FROM DDR_R_BSNS_YR
51 WHERE yr_nbr = (
52 SELECT MAX(yr_nbr)
53 FROM DDR_R_BSNS_YR
54 WHERE clndr_cd = (
55 SELECT clndr_cd
56 FROM DDR_R_CLNDR
57 WHERE org_cd = p_org_code
58 AND clndr_typ = 'BSNS'
59 )
60 )
61 -- Bug# 6866605 change start
62 AND clndr_cd = (
63 SELECT clndr_cd
64 FROM DDR_R_CLNDR
65 WHERE org_cd = p_org_code
66 AND clndr_typ = 'BSNS'
67 )
68 -- Bug# 6866605 change end
69 */
70 l_SQL_str := 'select YR_NBR, YR_STRT_DT, YR_END_DT from ' || l_table_name || ' where YR_NBR =';
71 l_SQL_str := l_SQL_str || ' (select max(YR_NBR) from ' || l_table_name;
72 IF p_clndr_type IN ('BSNS','FSCL','ADVR','PLNG')
73 THEN
74 l_SQL_str := l_SQL_str || ' where CLNDR_CD = (select CLNDR_CD from DDR_R_CLNDR';
75 l_SQL_str := l_SQL_str || ' where ORG_CD = ''' || p_org_code || ''' and CLNDR_TYP = ''' || p_clndr_type ||''')';
76 -- Bug# 6866605 change start
77 l_SQL_str := l_SQL_str || ')';
78 l_SQL_str := l_SQL_str || ' AND clndr_cd = (SELECT clndr_cd FROM DDR_R_CLNDR WHERE org_cd = ''' || p_org_code || ''' AND clndr_typ = ''' || p_clndr_type || ''')';
79 ELSE
80 l_SQL_str := l_SQL_str || ')';
81 END IF;
82 -- Bug# 6866605 change end
83 BEGIN
84 EXECUTE IMMEDIATE l_SQL_str INTO p_last_year, p_last_year_start_dt, p_last_year_end_dt;
85 EXCEPTION
86 WHEN NO_DATA_FOUND
87 THEN
88 p_last_year := null;
89 p_last_year_start_dt := null;
90 p_last_year_end_dt := null;
91 END;
92 END Get_Last_Year_Details;
93
94 PROCEDURE Get_Last_Calendar_Week_Details (
95 p_last_week OUT NOCOPY NUMBER,
96 p_last_week_start_dt OUT NOCOPY DATE,
97 p_last_week_end_dt OUT NOCOPY DATE
98 )
99 IS
100 cursor cur_week is
101 select WK_NBR, WK_STRT_DT, WK_END_DT
102 from DDR_R_CLNDR_WK
103 where WK_NBR = (
104 select max(WK_NBR)
105 from DDR_R_CLNDR_WK
106 );
107 BEGIN
108 OPEN cur_week;
109 FETCH cur_week INTO p_last_week,p_last_week_start_dt,p_last_week_end_dt;
110 IF cur_week%NOTFOUND
111 THEN
112 p_last_week := null;
113 p_last_week_start_dt := null;
114 p_last_week_end_dt := null;
115 END IF;
116 CLOSE cur_week;
117 END Get_Last_Calendar_Week_Details;
118
119 PROCEDURE Get_Calendar_Week (
120 p_date IN DATE,
121 p_week_id OUT NOCOPY NUMBER,
122 p_week_code OUT NOCOPY VARCHAR2
123 )
124 IS
125 cursor cur_week is
126 select CLNDR_WK_ID, WK_CD
127 from DDR_R_CLNDR_WK
128 where p_date between TRUNC(WK_STRT_DT)
129 and TRUNC(WK_END_DT) + .99999;
130 BEGIN
131 OPEN cur_week;
132 FETCH cur_week INTO p_week_id,p_week_code;
133 IF cur_week%NOTFOUND
134 THEN
135 p_week_id := null;
136 p_week_code := null;
137 END IF;
138 CLOSE cur_week;
139 END Get_Calendar_Week;
140
141 FUNCTION Get_Organization_Type (p_org_code IN VARCHAR2)
142 RETURN VARCHAR2
143 IS
144 cursor cur_org is
145 select ORG_TYP
146 from DDR_R_ORG
147 where ORG_CD = p_org_code;
148
149 l_org_type DDR_R_ORG.ORG_TYP%TYPE;
150 BEGIN
151 OPEN cur_org;
152 FETCH cur_org INTO l_org_type;
153 IF cur_org%NOTFOUND
154 THEN
155 l_org_type := null;
156 END IF;
157 CLOSE cur_org;
158 RETURN l_org_type;
159 END Get_Organization_Type;
160
161 FUNCTION Get_Manufacturer
162 RETURN VARCHAR2
163 IS
164 cursor cur_mfg is
165 select ORG_CD
166 from DDR_R_ORG
167 where ORG_TYP = 'MFG';
168
169 l_mfg_code DDR_R_ORG.ORG_CD%TYPE;
170 BEGIN
171 OPEN cur_mfg;
172 FETCH cur_mfg INTO l_mfg_code;
173 IF cur_mfg%NOTFOUND
174 THEN
175 l_mfg_code := null;
176 END IF;
177 CLOSE cur_mfg;
178 RETURN l_mfg_code;
179 END Get_Manufacturer;
180
181 -- Bug# 6965786 change start
182 FUNCTION Get_Spcl_Prd_Qtr(
183 P_extra_week_period IN VARCHAR2,
184 P_qtr_array Number_Tab,
185 P_period_array Number_Tab
186 )
187 RETURN VARCHAR2
188 IS
189 l_period_no NUMBER:=0;
190 l_period_idx_name VARCHAR2(30);
191 l_tot_no_of_weeks_period NUMBER:=0;
192 l_qtr_no NUMBER:=0;
193 l_qtr_idx_name VARCHAR2(30);
194 l_tot_no_of_weeks_qtr NUMBER:=0;
195 BEGIN
196 LOOP
197 l_period_no := l_period_no + 1;
198 l_period_idx_name := 'P' || to_char(l_period_no);
199 l_tot_no_of_weeks_period := l_tot_no_of_weeks_period + P_period_array(l_period_idx_name);
200 EXIT WHEN P_extra_week_period = l_period_idx_name;
201 END LOOP;
202 LOOP
203 l_qtr_no := l_qtr_no + 1;
204 l_qtr_idx_name := 'Q' || to_char(l_qtr_no);
205 l_tot_no_of_weeks_qtr := l_tot_no_of_weeks_qtr + P_qtr_array(l_qtr_idx_name);
206 EXIT WHEN l_tot_no_of_weeks_period <= l_tot_no_of_weeks_qtr;
207 END LOOP;
208 RETURN l_qtr_idx_name;
209 END Get_Spcl_Prd_Qtr;
210 -- Bug# 6965786 change end
211
212 PROCEDURE Create_WKDAY_Records
213 IS
214 cursor cur_wkday is
215 select 1
216 from DDR_R_WKDAY;
217
218 l_dummy NUMBER(1);
219 l_sunday_date DATE;
220 BEGIN
221 OPEN cur_wkday;
222 FETCH cur_wkday INTO l_dummy;
223 IF cur_wkday%NOTFOUND
224 THEN
225 l_sunday_date := TRUNC(sysdate,'DAY');
226 FOR day_idx IN 1 .. 7
227 LOOP
228 insert into DDR_R_WKDAY (
229 WKDAY_ID,
230 WKDAY_CD,
231 WKDAY_DESC,
232 SRC_SYS_IDNT,
233 SRC_SYS_DT,
234 CRTD_BY_DSR,
235 LAST_UPDT_BY_DSR,
236 CREATED_BY,
237 CREATION_DATE,
238 LAST_UPDATED_BY,
239 LAST_UPDATE_DATE,
240 LAST_UPDATE_LOGIN
241 )
242 values (
243 day_idx,
244 day_idx,
245 TO_CHAR(l_sunday_date+day_idx-1,'DAY'),
246 g_src_sys_idnt,
247 g_src_sys_dt,
248 g_crtd_by_DSR,
249 g_last_updt_by_DSR,
250 g_created_by,
251 g_creation_date,
252 g_last_updated_by,
253 g_last_update_date,
254 g_last_update_login
255 );
256 END LOOP;
257 END IF;
258 CLOSE cur_wkday;
259 END Create_WKDAY_Records;
260
261 FUNCTION Get_Calendar (
262 p_clndr_type IN VARCHAR2,
263 p_org_code IN VARCHAR2,
264 p_mfg_org_code IN VARCHAR2
265 )
266 RETURN VARCHAR2
267 IS
268 cursor cur_clndr is
269 select CLNDR_CD
270 from DDR_R_CLNDR
271 where CLNDR_TYP = p_clndr_type
272 and ORG_CD = p_org_code
273 and MFG_ORG_CD = p_mfg_org_code;
274
275 l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
276 BEGIN
277 OPEN cur_clndr;
278 FETCH cur_clndr INTO l_clndr_cd;
279 IF cur_clndr%NOTFOUND
280 THEN
281 l_clndr_cd := p_org_code || '-' || p_clndr_type;
282 insert into DDR_R_CLNDR (
283 CLNDR_ID,
284 MFG_ORG_CD,
285 CLNDR_CD,
286 ORG_CD,
287 CLNDR_TYP,
288 CLNDR_DESC,
289 SRC_SYS_IDNT,
290 SRC_SYS_DT,
291 CRTD_BY_DSR,
292 LAST_UPDT_BY_DSR,
293 CREATED_BY,
294 CREATION_DATE,
295 LAST_UPDATED_BY,
296 LAST_UPDATE_DATE,
297 LAST_UPDATE_LOGIN
298 )
299 values (
300 DDR_R_CLNDR_SEQ.NEXTVAL,
301 p_mfg_org_code,
302 l_clndr_cd,
303 p_org_code,
304 p_clndr_type,
305 p_org_code || ' - ' ||
306 decode(p_clndr_type,
307 'BSNS','Business',
308 'FSCL','Fiscal',
309 'ADVR','Advertising',
310 'PLNG','Planning'
311 ),
312 g_src_sys_idnt,
313 g_src_sys_dt,
314 g_crtd_by_DSR,
315 g_last_updt_by_DSR,
316 g_created_by,
317 g_creation_date,
318 g_last_updated_by,
319 g_last_update_date,
320 g_last_update_login
321 );
322 END IF;
323 CLOSE cur_clndr;
324 RETURN l_clndr_cd;
325 END Get_Calendar;
326
327 FUNCTION Check_Calendar_Year_Exists (
328 p_clndr_type IN VARCHAR2,
329 p_start_date IN DATE,
330 p_no_of_years IN NUMBER,
331 p_spl_year_count IN NUMBER
332 )
333 RETURN VARCHAR2
334 IS
335 cursor cur_clndr_yr (p_year IN NUMBER) is
336 select 1
337 from DDR_R_CLNDR_YR
338 where YR_NBR = p_year;
339
340 l_start_year NUMBER;
341 l_end_year NUMBER;
342 l_end_date DATE;
343 l_year NUMBER;
344 l_dummy NUMBER;
345 l_missing_year VARCHAR2(100);
346 BEGIN
347 l_start_year := TO_NUMBER(TO_CHAR(p_start_date,'YYYY'));
348
349 IF p_clndr_type IN ('BSNS','ADVR','PLNG')
350 THEN
351 l_end_date := p_start_date + p_no_of_years*52*7 + nvl(p_spl_year_count,0)*7 - 1;
352 ELSIF p_clndr_type = 'FSCL'
353 THEN
354 l_end_date := ADD_MONTHS(p_start_date,p_no_of_years*12);
355 END IF;
356
357 l_end_year := TO_NUMBER(TO_CHAR(l_end_date,'YYYY'));
358
359 l_missing_year := null;
360 FOR year_idx IN l_start_year .. l_end_year
361 LOOP
362 OPEN cur_clndr_yr (year_idx);
363 FETCH cur_clndr_yr INTO l_dummy;
364 IF cur_clndr_yr%NOTFOUND
365 THEN
366 IF l_missing_year IS NOT NULL
367 THEN
368 l_missing_year := l_missing_year || ',';
369 END IF;
370 l_missing_year := l_missing_year || TO_CHAR(year_idx);
371 END IF;
372 CLOSE cur_clndr_yr;
373 END LOOP;
374
375 RETURN l_missing_year;
376 END Check_Calendar_Year_Exists;
377
378 PROCEDURE Populate_Month_Arrays (
379 p_month_array IN OUT NOCOPY Number_Tab,
380 p_qtr_array IN OUT NOCOPY Number_Tab,
381 p_spl_year_array IN OUT NOCOPY Number_Tab,
382 p_five_week_month_list IN VARCHAR2,
383 p_special_year_list IN VARCHAR2,
384 p_extra_week_month IN VARCHAR2,
385 p_no_of_years IN NUMBER,
386 p_start_date IN DATE
387 )
388 IS
389 l_month_count NUMBER;
390 l_five_week_month_list VARCHAR2(100);
391 l_token VARCHAR2(30);
392 l_month_idx_name VARCHAR2(30);
393 l_qtr_idx_name VARCHAR2(30);
394 l_special_year_list VARCHAR2(100);
395 l_special_year_count NUMBER;
396 l_extra_week_month VARCHAR2(30);
397 l_extra_week_count NUMBER;
398 l_start_year NUMBER;
399 l_end_year NUMBER;
400 BEGIN
401 IF p_five_week_month_list IS NULL
402 THEN
403 Raise_Error('List of Months (of 5-Week) must be specified');
404 END IF;
405
406 /* Perform five-week-month related validation */
407 l_five_week_month_list := REPLACE(UPPER(p_five_week_month_list),' ','');
408 l_month_count := LENGTH(l_five_week_month_list) - LENGTH(REPLACE(l_five_week_month_list,',','')) + 1;
409
410 IF l_month_count <> 4
411 THEN
412 Raise_Error('List of Months (of 5-Week) must have exactly four months specified');
413 END IF;
414
415 FOR idx IN 0 .. (l_month_count-1)
419 IF INSTR(l_five_week_month_list,',',1,1) <> 0
416 LOOP
417 IF (idx=0)
418 THEN
420 THEN
421 l_token := SUBSTR(l_five_week_month_list,1,INSTR(l_five_week_month_list,',',1,1)-1);
422 ELSE
423 l_token := l_five_week_month_list;
424 END IF;
425 ELSE
426 IF INSTR(l_five_week_month_list,',',1,idx+1) <> 0
427 THEN
428 l_token := SUBSTR(l_five_week_month_list,INSTR(l_five_week_month_list,',',1,idx)+1,
429 INSTR(l_five_week_month_list,',',1,idx+1)-INSTR(l_five_week_month_list,',',1,idx)-1);
430 ELSE
431 l_token := SUBSTR(l_five_week_month_list,INSTR(l_five_week_month_list,',',1,idx)+1);
432 END IF;
433 END IF;
434
435 IF l_token NOT IN ('M1','M2','M3','M4','M5','M6','M7','M8','M9','M10','M11','M12')
436 THEN
437 Raise_Error('Invalid Month specification in List of Months (of 5-Week) => ' || l_token);
438 END IF;
439
440 p_month_array(l_token) := 5;
441 END LOOP;
442
443 FOR idx IN 1 .. 12
444 LOOP
445 l_month_idx_name := 'M' || to_char(idx);
446 IF NOT p_month_array.EXISTS(l_month_idx_name)
447 THEN
448 p_month_array(l_month_idx_name) := 4;
449 END IF;
450 END LOOP;
451
452 p_qtr_array('Q1') := p_month_array('M1') + p_month_array('M2') + p_month_array('M3');
453 p_qtr_array('Q2') := p_month_array('M4') + p_month_array('M5') + p_month_array('M6');
454 p_qtr_array('Q3') := p_month_array('M7') + p_month_array('M8') + p_month_array('M9');
455 p_qtr_array('Q4') := p_month_array('M10') + p_month_array('M11') + p_month_array('M12');
456
457 FOR idx IN 1 .. 4
458 LOOP
459 l_qtr_idx_name := 'Q' || to_char(idx);
460 IF p_qtr_array(l_qtr_idx_name) <> 13
461 THEN
462 Raise_Error('In List of Months (of 5-Week), exactly one month to be specified for Quarter => ' || l_qtr_idx_name);
463 END IF;
464 END LOOP;
465
466 IF p_special_year_list IS NOT NULL
467 THEN
468 IF p_extra_week_month IS NULL
469 THEN
470 Raise_Error('Month (with extra Week for Special Year) must be specified');
471 END IF;
472
473 /* Perform Validation for p_extra_week_month */
474 l_extra_week_month := REPLACE(UPPER(p_extra_week_month),' ','');
475 l_extra_week_count := LENGTH(l_extra_week_month) - LENGTH(REPLACE(l_extra_week_month,',','')) + 1;
476 IF l_extra_week_count <> 1
477 THEN
478 Raise_Error('Month (with extra Week for Special Year) must have exactly one month specified');
479 END IF;
480
481 IF l_extra_week_month NOT IN ('M1','M2','M3','M4','M5','M6','M7','M8','M9','M10','M11','M12')
482 THEN
483 Raise_Error('Invalid Month specification in Month (with extra Week for Special Year) => ' || l_extra_week_month);
484 END IF;
485
486 IF p_month_array(l_extra_week_month) = 5
487 THEN
488 Raise_Error('Month (with extra Week for Special Year) is already a five-week month');
489 END IF;
490
491 /* Build the special year array and Perform the corresponding validation */
492 l_special_year_list := REPLACE(UPPER(p_special_year_list),' ','');
493 l_special_year_count := LENGTH(l_special_year_list) - LENGTH(REPLACE(l_special_year_list,',','')) + 1;
494
495 IF l_special_year_count > p_no_of_years
496 THEN
497 Raise_Error('Number of Special Years can''t be more than number of Years to be populated');
498 END IF;
499
500 l_start_year := TO_CHAR(p_start_date,'YYYY');
501 l_end_year := l_start_year + p_no_of_years - 1;
502
503 FOR idx IN 0 .. (l_special_year_count-1)
504 LOOP
505 IF (idx=0)
506 THEN
507 IF INSTR(l_special_year_list,',',1,1) <> 0
508 THEN
509 l_token := SUBSTR(l_special_year_list,1,INSTR(l_special_year_list,',',1,1)-1);
510 ELSE
511 l_token := l_special_year_list;
512 END IF;
513 ELSE
514 IF INSTR(l_special_year_list,',',1,idx+1) <> 0
515 THEN
516 l_token := SUBSTR(l_special_year_list,INSTR(l_special_year_list,',',1,idx)+1,
517 INSTR(l_special_year_list,',',1,idx+1)-INSTR(l_special_year_list,',',1,idx)-1);
518 ELSE
519 l_token := SUBSTR(l_special_year_list,INSTR(l_special_year_list,',',1,idx)+1);
520 END IF;
521 END IF;
522
523 p_spl_year_array(l_token) := to_number(l_token);
524 IF to_number(l_token) NOT BETWEEN l_start_year AND l_end_year
525 THEN
526 Raise_Error('Special Year not within the range of Years to be populated => ' || l_token);
527 END IF;
528 END LOOP;
529 END IF;
530
531 END Populate_Month_Arrays;
532
533 PROCEDURE Populate_Period_Arrays (
534 p_period_array IN OUT NOCOPY Number_Tab,
535 p_qtr_array IN OUT NOCOPY Number_Tab,
536 p_spl_year_array IN OUT NOCOPY Number_Tab,
537 p_period_dist_list IN VARCHAR2,
538 p_week_dist_list IN VARCHAR2,
539 p_special_year_list IN VARCHAR2,
540 p_extra_week_period IN VARCHAR2,
541 p_no_of_years IN NUMBER,
542 p_start_date IN DATE
543 )
544 IS
545 l_qtr_count NUMBER;
546 l_period_count NUMBER;
547 l_period_count_qtr NUMBER;
548 l_period_dist_list VARCHAR2(100);
549 l_week_dist_list VARCHAR2(500);
550 l_token VARCHAR2(30);
551 l_period_idx_name VARCHAR2(30);
552 l_qtr_idx_name VARCHAR2(30);
553 l_special_year_list VARCHAR2(100);
554 l_special_year_count NUMBER;
555 l_extra_week_period VARCHAR2(30);
556 l_extra_week_count NUMBER;
557 l_start_year NUMBER;
558 l_end_year NUMBER;
559 l_token_name VARCHAR2(30);
560 l_token_value VARCHAR2(30);
561 l_period_number VARCHAR2(10);
562 l_week_count NUMBER;
563 l_period_idx NUMBER;
564 BEGIN
565 IF p_period_dist_list IS NULL
566 THEN
567 Raise_Error('Period Distribution list over Quarters must be specified');
568 END IF;
569
570 IF p_week_dist_list IS NULL
571 THEN
572 Raise_Error('Week Distribution list over Periods must be specified');
573 END IF;
574
575 /* Perform Period Distribution and Week Distribution related validation */
576 l_period_dist_list := REPLACE(UPPER(p_period_dist_list),' ','');
577 l_qtr_count := LENGTH(l_period_dist_list) - LENGTH(REPLACE(l_period_dist_list,',','')) + 1;
578
579 IF l_qtr_count <> 4
580 THEN
581 Raise_Error('Period Distribution List must have exactly four Quarters specified');
582 END IF;
583
584 l_period_count_qtr := 0;
585 FOR idx IN 0 .. (l_qtr_count-1)
586 LOOP
587 IF (idx=0)
588 THEN
589 IF INSTR(l_period_dist_list,',',1,1) <> 0
590 THEN
591 l_token := SUBSTR(l_period_dist_list,1,INSTR(l_period_dist_list,',',1,1)-1);
592 ELSE
593 l_token := l_period_dist_list;
594 END IF;
595 ELSE
596 IF INSTR(l_period_dist_list,',',1,idx+1) <> 0
597 THEN
598 l_token := SUBSTR(l_period_dist_list,INSTR(l_period_dist_list,',',1,idx)+1,
599 INSTR(l_period_dist_list,',',1,idx+1)-INSTR(l_period_dist_list,',',1,idx)-1);
600 ELSE
604
601 l_token := SUBSTR(l_period_dist_list,INSTR(l_period_dist_list,',',1,idx)+1);
602 END IF;
603 END IF;
605 l_token_name := SUBSTR(l_token,1,INSTR(l_token,'=')-1);
606 l_token_value := SUBSTR(l_token,INSTR(l_token,'=')+1);
607
608 IF l_token_name NOT IN ('Q1','Q2','Q3','Q4')
609 THEN
610 Raise_Error('Invalid Quarter specification in Period Distribution List => ' || l_token_name);
611 END IF;
612
613 l_period_count_qtr := l_period_count_qtr + to_number(l_token_value);
614 p_qtr_array(l_token_name) := to_number(l_token_value);
615 END LOOP;
616
617 l_week_dist_list := REPLACE(UPPER(p_week_dist_list),' ','');
618 l_period_count := LENGTH(l_week_dist_list) - LENGTH(REPLACE(l_week_dist_list,',','')) + 1;
619
620 IF l_period_count <> l_period_count_qtr
621 THEN
622 Raise_Error('Number of Periods mismatch between Period and Week Distribution List');
623 END IF;
624
625 l_week_count := 0;
626 FOR idx IN 0 .. (l_period_count-1)
627 LOOP
628 IF (idx=0)
629 THEN
630 IF INSTR(l_week_dist_list,',',1,1) <> 0
631 THEN
632 l_token := SUBSTR(l_week_dist_list,1,INSTR(l_week_dist_list,',',1,1)-1);
633 ELSE
634 l_token := l_week_dist_list;
635 END IF;
636 ELSE
637 IF INSTR(l_week_dist_list,',',1,idx+1) <> 0
638 THEN
639 l_token := SUBSTR(l_week_dist_list,INSTR(l_week_dist_list,',',1,idx)+1,
640 INSTR(l_week_dist_list,',',1,idx+1)-INSTR(l_week_dist_list,',',1,idx)-1);
641 ELSE
642 l_token := SUBSTR(l_week_dist_list,INSTR(l_week_dist_list,',',1,idx)+1);
643 END IF;
644 END IF;
645
646 l_token_name := SUBSTR(l_token,1,INSTR(l_token,'=')-1);
647 l_token_value := SUBSTR(l_token,INSTR(l_token,'=')+1);
648
649 /* Validate Period Name */
650 IF SUBSTR(l_token_name,1,1) <> 'P'
651 THEN
652 Raise_Error('Invalid Period specification in Week Distribution List => ' || l_token_name);
653 END IF;
654
655 l_period_number := SUBSTR(l_token_name,2);
656 IF l_period_number <> to_char(idx+1)
657 THEN
658 Raise_Error('Period is not in order in Week Distribution List => ' || l_token_name);
659 END IF;
660
661 p_period_array(l_token_name) := to_number(l_token_value);
662 l_week_count := l_week_count + to_number(l_token_value);
663 END LOOP;
664
665 IF l_week_count <> 52
666 THEN
667 Raise_Error('Total number of weeks over all Periods must be 52');
668 END IF;
669
670 /* Populate the Quarter array */
671 l_period_idx := 0;
672 FOR idx_outer IN 1 .. 4
673 LOOP
674 l_qtr_idx_name := 'Q' || to_char(idx_outer);
675 l_period_count_qtr := p_qtr_array(l_qtr_idx_name);
676 p_qtr_array(l_qtr_idx_name) := 0;
677 FOR idx IN 1 .. l_period_count_qtr
678 LOOP
679 l_period_idx := l_period_idx + 1;
680 l_period_idx_name := 'P' || to_char(l_period_idx);
681 p_qtr_array(l_qtr_idx_name) := p_qtr_array(l_qtr_idx_name) + p_period_array(l_period_idx_name);
682 END LOOP;
683
684 /*
685 IF p_qtr_array(l_qtr_idx_name) <> 13
686 THEN
687 Raise_Error('Each Quarter must have exactly 13 Weeks. Quarter ' || l_qtr_idx_name || ' has ' || to_char(p_qtr_array(l_qtr_idx_name)) || ' weeks');
688 END IF;
689 */
690 END LOOP;
691
692 IF p_special_year_list IS NOT NULL
693 THEN
694 IF p_extra_week_period IS NULL
695 THEN
696 Raise_Error('Period (with extra Week for Special Year) must be specified');
697 END IF;
698
699 /* Perform Validation for p_extra_week_period */
700 l_extra_week_period := REPLACE(UPPER(p_extra_week_period),' ','');
701 l_extra_week_count := LENGTH(l_extra_week_period) - LENGTH(REPLACE(l_extra_week_period,',','')) + 1;
702 IF l_extra_week_count <> 1
703 THEN
704 Raise_Error('Period (with extra Week for Special Year) must have exactly one period specified');
705 END IF;
706
707 IF SUBSTR(l_extra_week_period,1,1) <> 'P'
708 THEN
709 Raise_Error('Invalid Period specification in Period (with extra Week for Special Year) => ' || l_extra_week_period);
710 END IF;
711
712 l_period_number := SUBSTR(l_extra_week_period,2);
713 IF to_number(l_period_number) NOT BETWEEN 1 AND l_period_count
714 THEN
715 Raise_Error('Invalid Period specification in Period (with extra Week for Special Year) => ' || l_extra_week_period);
716 END IF;
717
718 /* Build the special year array and Perform the corresponding validation */
719 l_special_year_list := REPLACE(UPPER(p_special_year_list),' ','');
720 l_special_year_count := LENGTH(l_special_year_list) - LENGTH(REPLACE(l_special_year_list,',','')) + 1;
721
722 IF l_special_year_count > p_no_of_years
723 THEN
724 Raise_Error('Number of Special Years can''t be more than number of Years to be populated');
725 END IF;
726
727 l_start_year := TO_CHAR(p_start_date,'YYYY');
728 l_end_year := l_start_year + p_no_of_years - 1;
729
730 FOR idx IN 0 .. (l_special_year_count-1)
731 LOOP
735 THEN
732 IF (idx=0)
733 THEN
734 IF INSTR(l_special_year_list,',',1,1) <> 0
736 l_token := SUBSTR(l_special_year_list,1,INSTR(l_special_year_list,',',1,1)-1);
737 ELSE
738 l_token := l_special_year_list;
739 END IF;
740 ELSE
741 IF INSTR(l_special_year_list,',',1,idx+1) <> 0
742 THEN
743 l_token := SUBSTR(l_special_year_list,INSTR(l_special_year_list,',',1,idx)+1,
744 INSTR(l_special_year_list,',',1,idx+1)-INSTR(l_special_year_list,',',1,idx)-1);
745 ELSE
746 l_token := SUBSTR(l_special_year_list,INSTR(l_special_year_list,',',1,idx)+1);
747 END IF;
748 END IF;
749
750 p_spl_year_array(l_token) := to_number(l_token);
751 IF to_number(l_token) NOT BETWEEN l_start_year AND l_end_year
752 THEN
753 Raise_Error('Special Year not within the range of Years to be populated => ' || l_token);
754 END IF;
755 END LOOP;
756 END IF;
757
758 END Populate_Period_Arrays;
759
760 PROCEDURE Populate_STND_Calendar (
761 p_no_of_years IN NUMBER,
762 p_start_year IN NUMBER DEFAULT NULL
763 )
764 AS
765 l_last_year NUMBER;
766 l_last_year_start_date DATE;
767 l_last_year_end_date DATE;
768 l_last_week NUMBER;
769 l_last_week_start_date DATE;
770 l_last_week_end_date DATE;
771
772 l_no_of_years NUMBER;
773 l_no_year_days NUMBER;
774 l_curr_year_id NUMBER;
775 l_curr_year NUMBER;
776 l_curr_year_start_date DATE;
777 l_curr_year_end_date DATE;
778 l_curr_year_desc VARCHAR2(40);
779 l_curr_qtr_id NUMBER;
780 l_curr_qtr NUMBER;
781 l_curr_qtr_start_date DATE;
782 l_curr_qtr_end_date DATE;
783 l_curr_month_id NUMBER;
784 l_curr_month NUMBER;
785 l_curr_month_start_date DATE;
786 l_curr_month_end_date DATE;
787 l_curr_week_id NUMBER;
788 l_curr_week NUMBER;
789 l_curr_week_start_date DATE;
790 l_curr_week_end_date DATE;
791 l_curr_day_id NUMBER;
792 l_curr_date DATE;
793 l_wkday_id NUMBER;
794 l_qtr_no NUMBER;
795 l_month_no NUMBER;
796 l_week_no NUMBER;
797
798 BEGIN
799 /* Check existence of record in DDR_R_CLNDR_YR and get last year details */
800 Get_Last_Year_Details('CLNDR',null,l_last_year,l_last_year_start_date,l_last_year_end_date);
801 IF l_last_year IS NULL /* Last year record does not exist */
802 THEN
803 IF p_start_year IS NULL
804 THEN
805 Raise_Error('Year must be specified');
806 END IF;
807 l_last_year := p_start_year-1;
808 l_last_year_start_date := TO_DATE(TO_CHAR(l_last_year) || '01','YYYYMM');
809 l_last_year_end_date := ADD_MONTHS(l_last_year_start_date,12)-1;
810 -- Bug# 6863276 change start
811 ELSE
812 IF p_start_year IS NOT NULL
813 THEN
814 Raise_Error('Year must be NULL');
815 END IF;
816 -- Bug# 6863276 change end
817 END IF;
818
819 /* Check existence of record in DDR_R_CLNDR_WK and get last week details */
820 Get_Last_Calendar_Week_Details(l_last_week,l_last_week_start_date,l_last_week_end_date);
821 IF l_last_week IS NULL /* Last week record does not exist */
822 THEN
823 l_last_week_start_date := TRUNC(l_last_year_end_date,'DAY');
824 l_last_week_end_date := l_last_week_start_date + 7 - 1;
825 END IF;
826
827 l_no_of_years := nvl(p_no_of_years,1);
828
829 /* Check existance of record in DDR_R_WKDAY. Create records if not alreday exists */
830 Create_WKDAY_Records;
831
832 /* Initialize Year and Week Variables for loop operation */
833 l_curr_year := l_last_year;
834 l_curr_year_start_date := l_last_year_start_date;
835 l_curr_year_end_date := l_last_year_end_date;
836 l_curr_week_start_date := l_last_week_start_date;
837 l_curr_week_end_date := l_last_week_end_date;
838
839 /* Create records in various Calendar tables */
840 FOR year_idx IN 1 .. l_no_of_years
841 LOOP
842 l_curr_year := l_curr_year +1;
843 l_curr_year_start_date := l_curr_year_end_date+1;
844 l_curr_year_end_date := ADD_MONTHS(l_curr_year_start_date,12)-1;
845 l_no_year_days := l_curr_year_end_date - l_curr_year_start_date + 1;
846 -- l_curr_year_id := TO_NUMBER(TO_CHAR(l_curr_year_start_date,g_YR_ID_format));
847 SELECT DDR_R_CLNDR_YR_SEQ.NEXTVAL
848 INTO l_curr_year_id
849 FROM DUAL;
850 l_curr_year_desc := 'CY ' || TO_CHAR(l_curr_year);
851
852 insert into DDR_R_CLNDR_YR (
853 CLNDR_YR_ID,
854 YR_CD,
855 YR_NBR,
856 YR_DESC,
857 YR_STRT_DT,
858 YR_END_DT,
859 YR_TIMESPN,
860 SRC_SYS_IDNT,
861 SRC_SYS_DT,
862 CRTD_BY_DSR,
863 LAST_UPDT_BY_DSR,
864 CREATED_BY,
865 CREATION_DATE,
866 LAST_UPDATED_BY,
870 values (
867 LAST_UPDATE_DATE,
868 LAST_UPDATE_LOGIN
869 )
871 l_curr_year_id,
872 l_curr_year,
873 l_curr_year,
874 l_curr_year_desc,
875 l_curr_year_start_date,
876 l_curr_year_end_date,
877 l_no_year_days,
878 g_src_sys_idnt,
879 g_src_sys_dt,
880 g_crtd_by_DSR,
881 g_last_updt_by_DSR,
882 g_created_by,
883 g_creation_date,
884 g_last_updated_by,
885 g_last_update_date,
886 g_last_update_login
887 );
888
889 /* Initialize Variables for loop operation */
890 l_curr_qtr_end_date := l_curr_year_start_date - 1;
891 l_curr_month_end_date := l_curr_year_start_date - 1;
892 l_curr_date := l_curr_year_start_date - 1;
893 l_qtr_no := 0;
894 l_month_no := 0;
895 l_week_no := 0;
896
897 FOR day_idx IN 1 .. l_no_year_days
898 LOOP
899 l_curr_date := l_curr_date + 1;
900
901 IF (l_curr_date > l_curr_qtr_end_date) /* New Quarter */
902 THEN
903 l_qtr_no := l_qtr_no + 1;
904 l_curr_qtr_start_date := l_curr_date;
905 l_curr_qtr_end_date := ADD_MONTHS(l_curr_qtr_start_date,3)-1;
906 -- l_curr_qtr_id := TO_NUMBER(TO_CHAR(l_curr_qtr_start_date,g_QTR_ID_format));
907 SELECT DDR_R_CLNDR_QTR_SEQ.NEXTVAL
908 INTO l_curr_qtr_id
909 FROM DUAL;
910 l_curr_qtr := TO_NUMBER(TO_CHAR(l_curr_year) || TO_CHAR(l_qtr_no));
911
912 insert into DDR_R_CLNDR_QTR (
913 CLNDR_QTR_ID,
914 QTR_CD,
915 QTR_NBR,
916 QTR_DESC,
917 QTR_STRT_DT,
918 QTR_END_DT,
919 QTR_TIMESPN,
920 CLNDR_YR_ID,
921 YR_CD,
922 SRC_SYS_IDNT,
923 SRC_SYS_DT,
924 CRTD_BY_DSR,
925 LAST_UPDT_BY_DSR,
926 CREATED_BY,
927 CREATION_DATE,
928 LAST_UPDATED_BY,
929 LAST_UPDATE_DATE,
930 LAST_UPDATE_LOGIN
931 )
932 values (
933 l_curr_qtr_id,
934 l_curr_qtr,
935 l_curr_qtr,
936 l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
937 l_curr_qtr_start_date,
938 l_curr_qtr_end_date,
939 l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
940 l_curr_year_id,
941 l_curr_year,
942 g_src_sys_idnt,
943 g_src_sys_dt,
944 g_crtd_by_DSR,
945 g_last_updt_by_DSR,
946 g_created_by,
947 g_creation_date,
948 g_last_updated_by,
949 g_last_update_date,
950 g_last_update_login
951 );
952 END IF;
953
954 IF (l_curr_date > l_curr_month_end_date) /* New Month */
955 THEN
956 l_month_no := l_month_no + 1;
957 l_curr_month_start_date := l_curr_date;
958 l_curr_month_end_date := ADD_MONTHS(l_curr_month_start_date,1)-1;
959 -- l_curr_month_id := TO_NUMBER(TO_CHAR(l_curr_month_start_date,g_MNTH_ID_format));
960 SELECT DDR_R_CLNDR_MNTH_SEQ.NEXTVAL
961 INTO l_curr_month_id
962 FROM DUAL;
963 l_curr_month := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_month_no),2,'0'));
964
965 insert into DDR_R_CLNDR_MNTH (
966 CLNDR_MNTH_ID,
967 MNTH_CD,
968 MNTH_NBR,
969 MNTH_DESC,
970 MNTH_STRT_DT,
971 MNTH_END_DT,
972 MNTH_TIMESPN,
973 CLNDR_QTR_ID,
974 QTR_CD,
975 SRC_SYS_IDNT,
976 SRC_SYS_DT,
977 CRTD_BY_DSR,
978 LAST_UPDT_BY_DSR,
979 CREATED_BY,
980 CREATION_DATE,
981 LAST_UPDATED_BY,
982 LAST_UPDATE_DATE,
983 LAST_UPDATE_LOGIN
984 )
985 values (
986 l_curr_month_id,
987 l_curr_month,
988 l_curr_month,
989 l_curr_year_desc || ' M' || TO_CHAR(l_month_no),
990 l_curr_month_start_date,
991 l_curr_month_end_date,
992 l_curr_month_end_date - l_curr_month_start_date + 1,
993 l_curr_qtr_id,
994 l_curr_qtr,
995 g_src_sys_idnt,
996 g_src_sys_dt,
997 g_crtd_by_DSR,
998 g_last_updt_by_DSR,
999 g_created_by,
1000 g_creation_date,
1001 g_last_updated_by,
1002 g_last_update_date,
1006
1003 g_last_update_login
1004 );
1005 END IF;
1007 IF (l_curr_date > l_curr_week_end_date) /* New Week */
1008 THEN
1009 l_week_no := l_week_no + 1;
1010 l_curr_week_start_date := l_curr_date;
1011 l_curr_week_end_date := l_curr_week_start_date + 7 - 1;
1012 -- l_curr_week_id := TO_NUMBER(TO_CHAR(l_curr_week_start_date,g_WK_ID_format));
1013 SELECT DDR_R_CLNDR_WK_SEQ.NEXTVAL
1014 INTO l_curr_week_id
1015 FROM DUAL;
1016 l_curr_week := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_week_no),2,'0'));
1017
1018 insert into DDR_R_CLNDR_WK (
1019 CLNDR_WK_ID,
1020 WK_CD,
1021 WK_NBR,
1022 WK_DESC,
1023 WK_STRT_DT,
1024 WK_END_DT,
1025 WK_TIMESPN,
1026 SRC_SYS_IDNT,
1027 SRC_SYS_DT,
1028 CRTD_BY_DSR,
1029 LAST_UPDT_BY_DSR,
1030 CREATED_BY,
1031 CREATION_DATE,
1032 LAST_UPDATED_BY,
1033 LAST_UPDATE_DATE,
1034 LAST_UPDATE_LOGIN
1035 )
1036 values (
1037 l_curr_week_id,
1038 l_curr_week,
1039 l_curr_week,
1040 l_curr_year_desc || ' W' || TO_CHAR(l_week_no),
1041 l_curr_week_start_date,
1042 l_curr_week_end_date,
1043 l_curr_week_end_date - l_curr_week_start_date + 1,
1044 g_src_sys_idnt,
1045 g_src_sys_dt,
1046 g_crtd_by_DSR,
1047 g_last_updt_by_DSR,
1048 g_created_by,
1049 g_creation_date,
1050 g_last_updated_by,
1051 g_last_update_date,
1052 g_last_update_login
1053 );
1054 END IF;
1055
1056 /* Insert Day Record */
1057 l_curr_day_id := TO_NUMBER(TO_CHAR(l_curr_date,g_DAY_ID_format));
1058 l_wkday_id := TRUNC(l_curr_date) - TRUNC(l_curr_date,'DAY') + 1;
1059
1060 IF l_curr_week IS NULL
1061 THEN
1062 Get_Calendar_Week (l_curr_date,l_curr_week_id,l_curr_week);
1063 END IF;
1064
1065 insert into DDR_R_DAY (
1066 DAY_CD,
1067 CLNDR_DT,
1068 CLNDR_DT_DESC,
1069 JULIAN_DAY,
1070 WKDAY_ID,
1071 WK_DAY,
1072 CLNDR_WK_ID,
1073 WK_CD,
1074 CLNDR_MNTH_ID,
1075 MNTH_CD,
1076 DAY_OF_YR,
1077 SRC_SYS_IDNT,
1078 SRC_SYS_DT,
1079 CRTD_BY_DSR,
1080 LAST_UPDT_BY_DSR,
1081 CREATED_BY,
1082 CREATION_DATE,
1083 LAST_UPDATED_BY,
1084 LAST_UPDATE_DATE,
1085 LAST_UPDATE_LOGIN
1086 )
1087 values (
1088 l_curr_day_id,
1089 l_curr_date,
1090 l_curr_year_desc || ' ' || TO_CHAR(l_curr_date),
1091 TO_CHAR(l_curr_date,'J'),
1092 l_wkday_id,
1093 l_wkday_id,
1094 l_curr_week_id,
1095 l_curr_week,
1096 l_curr_month_id,
1097 l_curr_month,
1098 day_idx,
1099 g_src_sys_idnt,
1100 g_src_sys_dt,
1101 g_crtd_by_DSR,
1102 g_last_updt_by_DSR,
1103 g_created_by,
1104 g_creation_date,
1105 g_last_updated_by,
1106 g_last_update_date,
1107 g_last_update_login
1108 );
1109 END LOOP; /* end of day loop */
1110 END LOOP; /* end of year loop */
1111
1112 COMMIT;
1113 END Populate_STND_Calendar;
1114
1115 PROCEDURE Populate_BSNS_Calendar (
1116 p_org_cd IN VARCHAR2,
1117 p_no_of_years IN NUMBER,
1118 p_start_date IN DATE DEFAULT NULL,
1119 p_five_week_month_list IN VARCHAR2,
1120 p_special_year_list IN VARCHAR2,
1121 p_extra_week_month IN VARCHAR2
1122 )
1123 IS
1124 l_qtr_array Number_Tab;
1125 l_month_array Number_Tab;
1126 l_spl_year_array Number_Tab;
1127 l_extra_week_month VARCHAR2(30);
1128
1129 -- Bug# 6932509 change start
1130 l_cut_extra_mnth number;
1131 l_qtr_start_mnth number;
1132 l_qtr_end_mnth number;
1133 -- Bug# 6932509 change end
1134
1135 l_last_year NUMBER;
1136 l_last_year_start_date DATE;
1137 l_last_year_end_date DATE;
1138
1139 l_org_type DDR_R_ORG.ORG_TYP%TYPE;
1140 l_mfg_org_cd DDR_R_ORG.ORG_CD%TYPE;
1141 l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
1142 l_missing_year VARCHAR2(100);
1143
1144 l_spl_year_flag BOOLEAN := FALSE;
1145 l_no_of_years NUMBER;
1146 l_no_of_weeks NUMBER;
1150 l_curr_year_start_date DATE;
1147 l_no_year_days NUMBER;
1148 l_curr_year_id NUMBER;
1149 l_curr_year NUMBER;
1151 l_curr_year_end_date DATE;
1152 l_curr_year_desc VARCHAR2(40);
1153 l_curr_qtr_id NUMBER;
1154 l_curr_qtr NUMBER;
1155 l_curr_qtr_start_date DATE;
1156 l_curr_qtr_end_date DATE;
1157 l_curr_month_id NUMBER;
1158 l_curr_month NUMBER;
1159 l_curr_month_start_date DATE;
1160 l_curr_month_end_date DATE;
1161 l_curr_week_id NUMBER;
1162 l_curr_week NUMBER;
1163 l_curr_week_start_date DATE;
1164 l_curr_week_end_date DATE;
1165 l_curr_day_id NUMBER;
1166 l_curr_date DATE;
1167 l_qtr_no NUMBER;
1168 l_month_no NUMBER;
1169 l_week_no NUMBER;
1170 l_no_of_weeks_qtr NUMBER;
1171 l_no_of_weeks_month NUMBER;
1172 l_day_no NUMBER;
1173 l_qtr_idx_name VARCHAR2(30);
1174 l_month_idx_name VARCHAR2(30);
1175 BEGIN
1176 /*
1177 Validate that p_org_cd is not null and also it is a valid organization as per DDR_R_ORG table
1178 Check that Organization Type in ('MFG','RTL')
1179 */
1180 IF p_org_cd IS NULL
1181 THEN
1182 Raise_Error('Organization Code must be specified');
1183 END IF;
1184
1185 l_org_type := Get_Organization_Type(p_org_cd);
1186 IF l_org_type IS NULL
1187 THEN
1188 Raise_Error('Invalid Organization');
1189 END IF;
1190
1191 IF l_org_type NOT IN ('MFG','RTL')
1192 THEN
1193 Raise_Error('Business Calendar can be defined for Manufacturer and Retailer only');
1194 END IF;
1195
1196 /* Validate that Manufacturing Organization exists in DDR_R_ORG */
1197 l_mfg_org_cd := Get_Manufacturer;
1198 IF (l_org_type <> 'MFG') AND (l_mfg_org_cd IS NULL)
1199 THEN
1200 Raise_Error('Manufacturer Organization not yet defined');
1201 END IF;
1202
1203 /* Check existence of record in DDR_R_BSNS_YR and get last year details */
1204 Get_Last_Year_Details('BSNS',p_org_cd,l_last_year,l_last_year_start_date,l_last_year_end_date);
1205 IF l_last_year IS NULL /* Last year record does not exist */
1206 THEN
1207 IF p_start_date IS NULL
1208 THEN
1209 Raise_Error('Start Date must be specified');
1210 END IF;
1211 l_last_year := TO_NUMBER(TO_CHAR(p_start_date,'YYYY'))-1;
1212 l_last_year_end_date := p_start_date - 1;
1213 -- Bug# 6863276 change start
1214 ELSE
1215 IF p_start_date IS NOT NULL
1216 THEN
1217 Raise_Error('Start Date must be NULL');
1218 END IF;
1219 -- Bug# 6863276 change end
1220 END IF;
1221
1222 l_no_of_years := nvl(p_no_of_years,1);
1223
1224 /* Populate Quarter and Month array */
1225 Populate_Month_Arrays(l_month_array,l_qtr_array,l_spl_year_array,p_five_week_month_list,p_special_year_list,p_extra_week_month,l_no_of_years,l_last_year_end_date+1);
1226 l_extra_week_month := REPLACE(UPPER(p_extra_week_month),' ','');
1227
1228 /* Check existance of record in DDR_R_CLNDR_YR for all relevant years */
1229 l_missing_year := Check_Calendar_Year_Exists('BSNS',l_last_year_end_date+1,l_no_of_years,l_spl_year_array.COUNT);
1230 IF l_missing_year IS NOT NULL
1231 THEN
1232 Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
1233 END IF;
1234
1235 /*
1236 Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='BSNS' and ORG_CD=p_org_cd;
1237 If no such record exists
1238 then
1239 Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='BSNS';
1240 end if;
1241 */
1242 l_clndr_cd := Get_Calendar('BSNS',p_org_cd,l_mfg_org_cd);
1243
1244 /* Initialize Year Variables for loop operation */
1245 l_curr_year := l_last_year;
1246 l_curr_year_start_date := l_last_year_start_date;
1247 l_curr_year_end_date := l_last_year_end_date;
1248
1249 /* Create records in various Business Calendar tables */
1250 FOR year_idx IN 1 .. l_no_of_years
1251 LOOP
1252 l_curr_year := l_curr_year + 1;
1253
1254 IF l_spl_year_array.EXISTS(to_char(l_curr_year))
1255 THEN
1256 l_spl_year_flag := TRUE;
1257 l_no_of_weeks := 53;
1258 ELSE
1259 l_spl_year_flag := FALSE;
1260 l_no_of_weeks := 52;
1261 END IF;
1262
1263 l_curr_year_start_date := l_curr_year_end_date+1;
1264 l_curr_year_end_date := l_curr_year_start_date + (7*l_no_of_weeks) - 1;
1265 l_no_year_days := l_curr_year_end_date - l_curr_year_start_date + 1;
1266 -- l_curr_year_id := TO_NUMBER(TO_CHAR(l_curr_year_start_date,g_YR_ID_format));
1267 SELECT DDR_R_BSNS_YR_SEQ.NEXTVAL
1268 INTO l_curr_year_id
1269 FROM DUAL;
1270 l_curr_year_desc := 'BY ' || TO_CHAR(l_curr_year);
1271
1272 insert into DDR_R_BSNS_YR (
1273 BSNS_YR_ID,
1274 MFG_ORG_CD,
1275 CLNDR_CD,
1276 YR_CD,
1277 YR_NBR,
1278 YR_DESC,
1279 YR_STRT_DT,
1280 YR_END_DT,
1281 YR_TIMESPN,
1282 SRC_SYS_IDNT,
1283 SRC_SYS_DT,
1284 CRTD_BY_DSR,
1285 LAST_UPDT_BY_DSR,
1289 LAST_UPDATE_DATE,
1286 CREATED_BY,
1287 CREATION_DATE,
1288 LAST_UPDATED_BY,
1290 LAST_UPDATE_LOGIN
1291 )
1292 values (
1293 l_curr_year_id,
1294 l_mfg_org_cd,
1295 l_clndr_cd,
1296 l_curr_year,
1297 l_curr_year,
1298 l_curr_year_desc,
1299 l_curr_year_start_date,
1300 l_curr_year_end_date,
1301 l_no_year_days,
1302 g_src_sys_idnt,
1303 g_src_sys_dt,
1304 g_crtd_by_DSR,
1305 g_last_updt_by_DSR,
1306 g_created_by,
1307 g_creation_date,
1308 g_last_updated_by,
1309 g_last_update_date,
1310 g_last_update_login
1311 );
1312
1313 /* Initialize Variables for loop operation */
1314 l_curr_qtr_end_date := l_curr_year_start_date - 1;
1315 l_curr_month_end_date := l_curr_year_start_date - 1;
1316 l_curr_date := l_curr_year_start_date - 1;
1317 l_qtr_no := 0;
1318 l_month_no := 0;
1319 l_week_no := 0;
1320 l_day_no := 0;
1321
1322 FOR week_idx IN 1 .. l_no_of_weeks
1323 LOOP
1324 l_curr_date := l_curr_year_start_date + 7 * (week_idx-1);
1325
1326 IF (l_curr_date > l_curr_qtr_end_date) /* New Quarter */
1327 THEN
1328 l_qtr_no := l_qtr_no + 1;
1329 l_curr_qtr_start_date := l_curr_date;
1330 l_qtr_idx_name := 'Q' || to_char(l_qtr_no);
1331
1332 IF (l_spl_year_flag = TRUE)
1333 THEN
1334 --Bug# 6932509 change start
1335 -- l_no_of_weeks_qtr := l_qtr_array(l_qtr_idx_name) + 1;
1336 l_cut_extra_mnth := to_number(substr(p_extra_week_month,2));
1337 l_qtr_start_mnth := (l_qtr_no - 1) * 3 + 1;
1338 l_qtr_end_mnth := l_qtr_no * 3;
1339 IF l_cut_extra_mnth between l_qtr_start_mnth and l_qtr_end_mnth
1340 THEN
1341 l_no_of_weeks_qtr := l_qtr_array(l_qtr_idx_name) + 1;
1342 ELSE
1343 l_no_of_weeks_qtr := l_qtr_array(l_qtr_idx_name);
1344 END IF;
1345 --Bug# 6932509 change end
1346 ELSE
1347 l_no_of_weeks_qtr := l_qtr_array(l_qtr_idx_name);
1348 END IF;
1349
1350 l_curr_qtr_end_date := l_curr_qtr_start_date + 7*l_no_of_weeks_qtr - 1;
1351 -- l_curr_qtr_id := TO_NUMBER(TO_CHAR(l_curr_qtr_start_date,g_QTR_ID_format));
1352 SELECT DDR_R_BSNS_QTR_SEQ.NEXTVAL
1353 INTO l_curr_qtr_id
1354 FROM DUAL;
1355 l_curr_qtr := TO_NUMBER(TO_CHAR(l_curr_year) || TO_CHAR(l_qtr_no));
1356
1357 insert into DDR_R_BSNS_QTR (
1358 BSNS_QTR_ID,
1359 MFG_ORG_CD,
1360 CLNDR_CD,
1361 QTR_CD,
1362 QTR_NBR,
1363 QTR_DESC,
1364 QTR_STRT_DT,
1365 QTR_END_DT,
1366 QTR_TIMESPN,
1367 BSNS_YR_ID,
1368 YR_CD,
1369 SRC_SYS_IDNT,
1370 SRC_SYS_DT,
1371 CRTD_BY_DSR,
1372 LAST_UPDT_BY_DSR,
1373 CREATED_BY,
1374 CREATION_DATE,
1375 LAST_UPDATED_BY,
1376 LAST_UPDATE_DATE,
1377 LAST_UPDATE_LOGIN
1378 )
1379 values (
1380 l_curr_qtr_id,
1381 l_mfg_org_cd,
1382 l_clndr_cd,
1383 l_curr_qtr,
1384 l_curr_qtr,
1385 l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
1386 l_curr_qtr_start_date,
1387 l_curr_qtr_end_date,
1388 l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
1389 l_curr_year_id,
1390 l_curr_year,
1391 g_src_sys_idnt,
1392 g_src_sys_dt,
1393 g_crtd_by_DSR,
1394 g_last_updt_by_DSR,
1395 g_created_by,
1396 g_creation_date,
1397 g_last_updated_by,
1398 g_last_update_date,
1399 g_last_update_login
1400 );
1401 END IF;
1402
1403 IF (l_curr_date > l_curr_month_end_date) /* New Month */
1404 THEN
1405 l_month_no := l_month_no + 1;
1406 l_curr_month_start_date := l_curr_date;
1407 l_month_idx_name := 'M' || to_char(l_month_no);
1408
1409 IF ( (l_spl_year_flag = TRUE) AND (l_extra_week_month = l_month_idx_name) )
1410 THEN
1411 l_no_of_weeks_month := l_month_array(l_month_idx_name) + 1;
1412 ELSE
1413 l_no_of_weeks_month := l_month_array(l_month_idx_name);
1414 END IF;
1415
1416 l_curr_month_end_date := l_curr_month_start_date + 7*l_no_of_weeks_month - 1;
1417 -- l_curr_month_id := TO_NUMBER(TO_CHAR(l_curr_month_start_date,g_MNTH_ID_format));
1418 SELECT DDR_R_BSNS_MNTH_SEQ.NEXTVAL
1422
1419 INTO l_curr_month_id
1420 FROM DUAL;
1421 l_curr_month := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_month_no),2,'0'));
1423 insert into DDR_R_BSNS_MNTH (
1424 BSNS_MNTH_ID,
1425 MFG_ORG_CD,
1426 CLNDR_CD,
1427 MNTH_CD,
1428 MNTH_NBR,
1429 MNTH_DESC,
1430 MNTH_STRT_DT,
1431 MNTH_END_DT,
1432 MNTH_TIMESPN,
1433 BSNS_QTR_ID,
1434 QTR_CD,
1435 SRC_SYS_IDNT,
1436 SRC_SYS_DT,
1437 CRTD_BY_DSR,
1438 LAST_UPDT_BY_DSR,
1439 CREATED_BY,
1440 CREATION_DATE,
1441 LAST_UPDATED_BY,
1442 LAST_UPDATE_DATE,
1443 LAST_UPDATE_LOGIN
1444 )
1445 values (
1446 l_curr_month_id,
1447 l_mfg_org_cd,
1448 l_clndr_cd,
1449 l_curr_month,
1450 l_curr_month,
1451 l_curr_year_desc || ' M' || TO_CHAR(l_month_no),
1452 l_curr_month_start_date,
1453 l_curr_month_end_date,
1454 l_curr_month_end_date - l_curr_month_start_date + 1,
1455 l_curr_qtr_id,
1456 l_curr_qtr,
1457 g_src_sys_idnt,
1458 g_src_sys_dt,
1459 g_crtd_by_DSR,
1460 g_last_updt_by_DSR,
1461 g_created_by,
1462 g_creation_date,
1463 g_last_updated_by,
1464 g_last_update_date,
1465 g_last_update_login
1466 );
1467 END IF;
1468
1469 /* Insert Record into Week table */
1470 l_week_no := l_week_no + 1;
1471 l_curr_week_start_date := l_curr_date;
1472 l_curr_week_end_date := l_curr_week_start_date + 7 - 1;
1473 -- l_curr_week_id := TO_NUMBER(TO_CHAR(l_curr_week_start_date,g_WK_ID_format));
1474 SELECT DDR_R_BSNS_WK_SEQ.NEXTVAL
1475 INTO l_curr_week_id
1476 FROM DUAL;
1477 l_curr_week := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_week_no),2,'0'));
1478
1479 insert into DDR_R_BSNS_WK (
1480 BSNS_WK_ID,
1481 MFG_ORG_CD,
1482 CLNDR_CD,
1483 WK_CD,
1484 WK_NBR,
1485 WK_DESC,
1486 WK_STRT_DT,
1487 WK_END_DT,
1488 WK_TIMESPN,
1489 BSNS_MNTH_ID,
1490 MNTH_CD,
1491 SRC_SYS_IDNT,
1492 SRC_SYS_DT,
1493 CRTD_BY_DSR,
1494 LAST_UPDT_BY_DSR,
1495 CREATED_BY,
1496 CREATION_DATE,
1497 LAST_UPDATED_BY,
1498 LAST_UPDATE_DATE,
1499 LAST_UPDATE_LOGIN
1500 )
1501 values (
1502 l_curr_week_id,
1503 l_mfg_org_cd,
1504 l_clndr_cd,
1505 l_curr_week,
1506 l_curr_week,
1507 l_curr_year_desc || ' W' || TO_CHAR(l_week_no),
1508 l_curr_week_start_date,
1509 l_curr_week_end_date,
1510 l_curr_week_end_date - l_curr_week_start_date + 1,
1511 l_curr_month_id,
1512 l_curr_month,
1513 g_src_sys_idnt,
1514 g_src_sys_dt,
1515 g_crtd_by_DSR,
1516 g_last_updt_by_DSR,
1517 g_created_by,
1518 g_creation_date,
1519 g_last_updated_by,
1520 g_last_update_date,
1521 g_last_update_login
1522 );
1523
1524 /* Insert Records into Base Day table */
1525 l_curr_date := l_curr_date - 1;
1526 FOR day_idx IN 1 .. 7
1527 LOOP
1528 l_curr_date := l_curr_date + 1;
1529 l_day_no := l_day_no + 1;
1530 l_curr_day_id := TO_NUMBER(TO_CHAR(l_curr_date,g_DAY_ID_format));
1531
1532 insert into DDR_R_BASE_DAY (
1533 BASE_DAY_ID,
1534 MFG_ORG_CD,
1535 CLNDR_CD,
1536 DAY_CD,
1537 CLNDR_TYP,
1538 WK_ID,
1539 WK_CD,
1540 MNTH_ID,
1541 MNTH_CD,
1542 DAY_OF_YR,
1543 WKEND_IND,
1544 CLNDR_STRT_DT,
1545 CLNDR_END_DT,
1546 SRC_SYS_IDNT,
1547 SRC_SYS_DT,
1548 CRTD_BY_DSR,
1549 LAST_UPDT_BY_DSR,
1550 CREATED_BY,
1551 CREATION_DATE,
1552 LAST_UPDATED_BY,
1553 LAST_UPDATE_DATE,
1554 LAST_UPDATE_LOGIN
1555 )
1556 values (
1557 DDR_R_BASE_DAY_SEQ.NEXTVAL,
1558 l_mfg_org_cd,
1559 l_clndr_cd,
1563 l_curr_week,
1560 l_curr_day_id,
1561 'BSNS',
1562 l_curr_week_id,
1564 l_curr_month_id,
1565 l_curr_month,
1566 l_day_no,
1567 decode(TRIM(TO_CHAR(l_curr_date,'DAY')),'SATURDAY','Y','SUNDAY','Y','N'),
1568 l_curr_date,
1569 l_curr_date,
1570 g_src_sys_idnt,
1571 g_src_sys_dt,
1572 g_crtd_by_DSR,
1573 g_last_updt_by_DSR,
1574 g_created_by,
1575 g_creation_date,
1576 g_last_updated_by,
1577 g_last_update_date,
1578 g_last_update_login
1579 );
1580 END LOOP;
1581
1582 END LOOP; /* end of week loop */
1583
1584 END LOOP; /* end of year loop */
1585
1586 COMMIT;
1587 END Populate_BSNS_Calendar;
1588
1589 PROCEDURE Populate_FSCL_Calendar (
1590 p_org_cd IN VARCHAR2,
1591 p_no_of_years IN NUMBER,
1592 p_start_year_month IN NUMBER DEFAULT NULL
1593 )
1594 AS
1595 l_last_year NUMBER;
1596 l_last_year_start_date DATE;
1597 l_last_year_end_date DATE;
1598
1599 l_org_type DDR_R_ORG.ORG_TYP%TYPE;
1600 l_mfg_org_cd DDR_R_ORG.ORG_CD%TYPE;
1601 l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
1602 l_missing_year VARCHAR2(100);
1603
1604 l_no_of_years NUMBER;
1605 l_no_year_days NUMBER;
1606 l_curr_year_id NUMBER;
1607 l_curr_year NUMBER;
1608 l_curr_year_start_date DATE;
1609 l_curr_year_end_date DATE;
1610 l_curr_year_desc VARCHAR2(40);
1611 l_curr_qtr_id NUMBER;
1612 l_curr_qtr NUMBER;
1613 l_curr_qtr_start_date DATE;
1614 l_curr_qtr_end_date DATE;
1615 l_curr_month_id NUMBER;
1616 l_curr_month NUMBER;
1617 l_curr_month_start_date DATE;
1618 l_curr_month_end_date DATE;
1619 l_curr_day_id NUMBER;
1620 l_curr_date DATE;
1621 l_qtr_no NUMBER;
1622 l_month_no NUMBER;
1623 l_day_no NUMBER;
1624 BEGIN
1625 /*
1626 Validate that p_org_cd is not null and also it is a valid organization as per DDR_R_ORG table
1627 Check that Organization Type in ('MFG')
1628 */
1629 IF p_org_cd IS NULL
1630 THEN
1631 Raise_Error('Organization Code must be specified');
1632 END IF;
1633
1634 l_org_type := Get_Organization_Type(p_org_cd);
1635 IF l_org_type IS NULL
1636 THEN
1637 Raise_Error('Invalid Organization');
1638 END IF;
1639
1640 IF l_org_type NOT IN ('MFG')
1641 THEN
1642 Raise_Error('Fiscal Calendar can be defined for Manufacturer only');
1643 END IF;
1644
1645 /* Set the Manufacturer code with the given organization code */
1646 l_mfg_org_cd := p_org_cd;
1647
1648 /* Check existence of record in DDR_R_FSCL_YR and get last year details */
1649 Get_Last_Year_Details('FSCL',p_org_cd,l_last_year,l_last_year_start_date,l_last_year_end_date);
1650 IF l_last_year IS NULL /* Last year record does not exist */
1651 THEN
1652 IF p_start_year_month IS NULL
1653 THEN
1654 Raise_Error('Start Year-Month (YYYYMM) must be specified');
1655 END IF;
1656 l_last_year := TO_NUMBER(SUBSTR(p_start_year_month,1,4))-1;
1657 l_last_year_end_date := TO_DATE(p_start_year_month,'YYYYMM')-1; /* Fiscal Year starts on 1st day of the month */
1658 -- Bug# 6863276 change start
1659 ELSE
1660 IF p_start_year_month IS NOT NULL
1661 THEN
1662 Raise_Error('Start Year-Month (YYYYMM) must be NULL');
1663 END IF;
1664 -- Bug# 6863276 change end
1665 END IF;
1666
1667 l_no_of_years := nvl(p_no_of_years,1);
1668
1669 /* Check existance of record in DDR_R_CLNDR_YR for all relevant years */
1670 l_missing_year := Check_Calendar_Year_Exists('FSCL',l_last_year_end_date+1,l_no_of_years,null);
1671 IF l_missing_year IS NOT NULL
1672 THEN
1673 Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
1674 END IF;
1675
1676 /*
1677 Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='FSCL' and ORG_CD=p_org_cd;
1678 If no such record exists
1679 then
1680 Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='FSCL';
1681 end if;
1682 */
1683 l_clndr_cd := Get_Calendar('FSCL',p_org_cd,l_mfg_org_cd);
1684
1685 /* Initialize Year Variables for loop operation */
1686 l_curr_year := l_last_year;
1687 l_curr_year_start_date := l_last_year_start_date;
1688 l_curr_year_end_date := l_last_year_end_date;
1689
1690 /* Create records in various Fiscal Calendar tables */
1691 FOR year_idx IN 1 .. l_no_of_years
1692 LOOP
1693 l_curr_year := l_curr_year +1;
1694 l_curr_year_start_date := l_curr_year_end_date+1;
1695 l_curr_year_end_date := ADD_MONTHS(l_curr_year_start_date,12)-1;
1696 l_no_year_days := l_curr_year_end_date - l_curr_year_start_date + 1;
1697 -- l_curr_year_id := TO_NUMBER(TO_CHAR(l_curr_year_start_date,g_YR_ID_format));
1698 SELECT DDR_R_FSCL_YR_SEQ.NEXTVAL
1702
1699 INTO l_curr_year_id
1700 FROM DUAL;
1701 l_curr_year_desc := 'FY ' || TO_CHAR(l_curr_year);
1703 insert into DDR_R_FSCL_YR (
1704 FSCL_YR_ID,
1705 MFG_ORG_CD,
1706 CLNDR_CD,
1707 YR_CD,
1708 YR_NBR,
1709 YR_DESC,
1710 YR_STRT_DT,
1711 YR_END_DT,
1712 YR_TIMESPN,
1713 SRC_SYS_IDNT,
1714 SRC_SYS_DT,
1715 CRTD_BY_DSR,
1716 LAST_UPDT_BY_DSR,
1717 CREATED_BY,
1718 CREATION_DATE,
1719 LAST_UPDATED_BY,
1720 LAST_UPDATE_DATE,
1721 LAST_UPDATE_LOGIN
1722 )
1723 values (
1724 l_curr_year_id,
1725 l_mfg_org_cd,
1726 l_clndr_cd,
1727 l_curr_year,
1728 l_curr_year,
1729 l_curr_year_desc,
1730 l_curr_year_start_date,
1731 l_curr_year_end_date,
1732 l_no_year_days,
1733 g_src_sys_idnt,
1734 g_src_sys_dt,
1735 g_crtd_by_DSR,
1736 g_last_updt_by_DSR,
1737 g_created_by,
1738 g_creation_date,
1739 g_last_updated_by,
1740 g_last_update_date,
1741 g_last_update_login
1742 );
1743
1744 /* Initialize Variables for loop operation */
1745 l_curr_qtr_end_date := l_curr_year_start_date - 1;
1746 l_curr_month_end_date := l_curr_year_start_date - 1;
1747 l_curr_date := l_curr_year_start_date - 1;
1748 l_qtr_no := 0;
1749 l_month_no := 0;
1750 l_day_no := 0;
1751
1752 FOR day_idx IN 1 .. l_no_year_days
1753 LOOP
1754 l_curr_date := l_curr_date + 1;
1755
1756 IF (l_curr_date > l_curr_qtr_end_date) /* New Quarter */
1757 THEN
1758 l_qtr_no := l_qtr_no + 1;
1759 l_curr_qtr_start_date := l_curr_date;
1760 l_curr_qtr_end_date := ADD_MONTHS(l_curr_qtr_start_date,3)-1;
1761 -- l_curr_qtr_id := TO_NUMBER(TO_CHAR(l_curr_qtr_start_date,g_QTR_ID_format));
1762 SELECT DDR_R_FSCL_QTR_SEQ.NEXTVAL
1763 INTO l_curr_qtr_id
1764 FROM DUAL;
1765 l_curr_qtr := TO_NUMBER(TO_CHAR(l_curr_year) || TO_CHAR(l_qtr_no));
1766
1767 insert into DDR_R_FSCL_QTR (
1768 FSCL_QTR_ID,
1769 MFG_ORG_CD,
1770 CLNDR_CD,
1771 QTR_CD,
1772 QTR_NBR,
1773 QTR_DESC,
1774 QTR_STRT_DT,
1775 QTR_END_DT,
1776 QTR_TIMESPN,
1777 FSCL_YR_ID,
1778 YR_CD,
1779 SRC_SYS_IDNT,
1780 SRC_SYS_DT,
1781 CRTD_BY_DSR,
1782 LAST_UPDT_BY_DSR,
1783 CREATED_BY,
1784 CREATION_DATE,
1785 LAST_UPDATED_BY,
1786 LAST_UPDATE_DATE,
1787 LAST_UPDATE_LOGIN
1788 )
1789 values (
1790 l_curr_qtr_id,
1791 l_mfg_org_cd,
1792 l_clndr_cd,
1793 l_curr_qtr,
1794 l_curr_qtr,
1795 l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
1796 l_curr_qtr_start_date,
1797 l_curr_qtr_end_date,
1798 l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
1799 l_curr_year_id,
1800 l_curr_year,
1801 g_src_sys_idnt,
1802 g_src_sys_dt,
1803 g_crtd_by_DSR,
1804 g_last_updt_by_DSR,
1805 g_created_by,
1806 g_creation_date,
1807 g_last_updated_by,
1808 g_last_update_date,
1809 g_last_update_login
1810 );
1811 END IF;
1812
1813 IF (l_curr_date > l_curr_month_end_date) /* New Month */
1814 THEN
1815 l_month_no := l_month_no + 1;
1816 l_curr_month_start_date := l_curr_date;
1817 l_curr_month_end_date := ADD_MONTHS(l_curr_month_start_date,1)-1;
1818 -- l_curr_month_id := TO_NUMBER(TO_CHAR(l_curr_month_start_date,g_MNTH_ID_format));
1819 SELECT DDR_R_FSCL_MNTH_SEQ.NEXTVAL
1820 INTO l_curr_month_id
1821 FROM DUAL;
1822 l_curr_month := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_month_no),2,'0'));
1823
1824 insert into DDR_R_FSCL_MNTH (
1825 FSCL_MNTH_ID,
1826 MFG_ORG_CD,
1827 CLNDR_CD,
1828 MNTH_CD,
1829 MNTH_NBR,
1830 MNTH_DESC,
1831 MNTH_STRT_DT,
1832 MNTH_END_DT,
1833 MNTH_TIMESPN,
1834 FSCL_QTR_ID,
1835 QTR_CD,
1836 SRC_SYS_IDNT,
1837 SRC_SYS_DT,
1838 CRTD_BY_DSR,
1839 LAST_UPDT_BY_DSR,
1840 CREATED_BY,
1841 CREATION_DATE,
1845 )
1842 LAST_UPDATED_BY,
1843 LAST_UPDATE_DATE,
1844 LAST_UPDATE_LOGIN
1846 values (
1847 l_curr_month_id,
1848 l_mfg_org_cd,
1849 l_clndr_cd,
1850 l_curr_month,
1851 l_curr_month,
1852 l_curr_year_desc || ' M' || TO_CHAR(l_month_no),
1853 l_curr_month_start_date,
1854 l_curr_month_end_date,
1855 l_curr_month_end_date - l_curr_month_start_date + 1,
1856 l_curr_qtr_id,
1857 l_curr_qtr,
1858 g_src_sys_idnt,
1859 g_src_sys_dt,
1860 g_crtd_by_DSR,
1861 g_last_updt_by_DSR,
1862 g_created_by,
1863 g_creation_date,
1864 g_last_updated_by,
1865 g_last_update_date,
1866 g_last_update_login
1867 );
1868 END IF;
1869
1870 /* Insert Day Record */
1871 l_day_no := l_day_no + 1;
1872 l_curr_day_id := TO_NUMBER(TO_CHAR(l_curr_date,g_DAY_ID_format));
1873
1874 insert into DDR_R_BASE_DAY (
1875 BASE_DAY_ID,
1876 MFG_ORG_CD,
1877 CLNDR_CD,
1878 DAY_CD,
1879 CLNDR_TYP,
1880 WK_ID,
1881 WK_CD,
1882 MNTH_ID,
1883 MNTH_CD,
1884 DAY_OF_YR,
1885 WKEND_IND,
1886 CLNDR_STRT_DT,
1887 CLNDR_END_DT,
1888 SRC_SYS_IDNT,
1889 SRC_SYS_DT,
1890 CRTD_BY_DSR,
1891 LAST_UPDT_BY_DSR,
1892 CREATED_BY,
1893 CREATION_DATE,
1894 LAST_UPDATED_BY,
1895 LAST_UPDATE_DATE,
1896 LAST_UPDATE_LOGIN
1897 )
1898 values (
1899 DDR_R_BASE_DAY_SEQ.NEXTVAL,
1900 l_mfg_org_cd,
1901 l_clndr_cd,
1902 l_curr_day_id,
1903 'FSCL',
1904 null,
1905 null,
1906 l_curr_month_id,
1907 l_curr_month,
1908 l_day_no,
1909 decode(TRIM(TO_CHAR(l_curr_date,'DAY')),'SATURDAY','Y','SUNDAY','Y','N'),
1910 l_curr_date,
1911 l_curr_date,
1912 g_src_sys_idnt,
1913 g_src_sys_dt,
1914 g_crtd_by_DSR,
1915 g_last_updt_by_DSR,
1916 g_created_by,
1917 g_creation_date,
1918 g_last_updated_by,
1919 g_last_update_date,
1920 g_last_update_login
1921 );
1922
1923 END LOOP; /* end of day loop */
1924 END LOOP; /* end of year loop */
1925
1926 COMMIT;
1927 END Populate_FSCL_Calendar;
1928
1929 PROCEDURE Populate_ADVR_Calendar (
1930 p_org_cd IN VARCHAR2,
1931 p_no_of_years IN NUMBER,
1932 p_start_date IN DATE DEFAULT NULL,
1933 p_period_dist_list IN VARCHAR2,
1934 p_week_dist_list IN VARCHAR2,
1935 p_special_year_list IN VARCHAR2,
1936 p_extra_week_period IN VARCHAR2
1937 )
1938 AS
1939 l_qtr_array Number_Tab;
1940 l_period_array Number_Tab;
1941 l_spl_year_array Number_Tab;
1942 l_extra_week_period VARCHAR2(30);
1943
1944 l_last_year NUMBER;
1945 l_last_year_start_date DATE;
1946 l_last_year_end_date DATE;
1947
1948 l_org_type DDR_R_ORG.ORG_TYP%TYPE;
1949 l_mfg_org_cd DDR_R_ORG.ORG_CD%TYPE;
1950 l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
1951 l_missing_year VARCHAR2(100);
1952
1953 l_spl_year_flag BOOLEAN := FALSE;
1954 l_no_of_years NUMBER;
1955 l_no_of_weeks NUMBER;
1956 l_no_year_days NUMBER;
1957 l_curr_year_id NUMBER;
1958 l_curr_year NUMBER;
1959 l_curr_year_start_date DATE;
1960 l_curr_year_end_date DATE;
1961 l_curr_year_desc VARCHAR2(40);
1962 l_curr_qtr_id NUMBER;
1963 l_curr_qtr NUMBER;
1964 l_curr_qtr_start_date DATE;
1965 l_curr_qtr_end_date DATE;
1966 l_curr_period_id NUMBER;
1967 l_curr_period NUMBER;
1968 l_curr_period_start_date DATE;
1969 l_curr_period_end_date DATE;
1970 l_curr_week_id NUMBER;
1971 l_curr_week NUMBER;
1972 l_curr_week_start_date DATE;
1973 l_curr_week_end_date DATE;
1974 l_curr_day_id NUMBER;
1975 l_curr_date DATE;
1976 l_qtr_no NUMBER;
1977 l_period_no NUMBER;
1978 l_week_no NUMBER;
1979 l_no_of_weeks_qtr NUMBER;
1980 l_no_of_weeks_period NUMBER;
1981 l_day_no NUMBER;
1982 l_qtr_idx_name VARCHAR2(30);
1983 l_period_idx_name VARCHAR2(30);
1984 -- Bug# 6965786 change start
1985 l_spcl_qtr VARCHAR2(30);
1989 Validate that p_org_cd is not null and also it is a valid organization as per DDR_R_ORG table
1986 -- Bug# 6965786 change end
1987 BEGIN
1988 /*
1990 Check that Organization Type in ('MFG','RTL')
1991 */
1992 IF p_org_cd IS NULL
1993 THEN
1994 Raise_Error('Organization Code must be specified');
1995 END IF;
1996
1997 l_org_type := Get_Organization_Type(p_org_cd);
1998 IF l_org_type IS NULL
1999 THEN
2000 Raise_Error('Invalid Organization');
2001 END IF;
2002
2003 IF l_org_type NOT IN ('MFG')
2004 THEN
2005 Raise_Error('Advertising Calendar can be defined for Manufacturer only');
2006 END IF;
2007
2008 /* Set the Manufacturer code with the given organization code */
2009 l_mfg_org_cd := p_org_cd;
2010
2011 /* Check existence of record in DDR_R_ADVR_YR and get last year details */
2012 Get_Last_Year_Details('ADVR',p_org_cd,l_last_year,l_last_year_start_date,l_last_year_end_date);
2013 IF l_last_year IS NULL /* Last year record does not exist */
2014 THEN
2015 IF p_start_date IS NULL
2016 THEN
2017 Raise_Error('Start Date must be specified');
2018 END IF;
2019 l_last_year := TO_NUMBER(TO_CHAR(p_start_date,'YYYY'))-1;
2020 l_last_year_end_date := p_start_date - 1;
2021 -- Bug# 6863276 change start
2022 ELSE
2023 IF p_start_date IS NOT NULL
2024 THEN
2025 Raise_Error('Start Date must be NULL');
2026 END IF;
2027 -- Bug# 6863276 change end
2028 END IF;
2029
2030 l_no_of_years := nvl(p_no_of_years,1);
2031
2032 /* Populate ADVR different arrays like Quarter, Period, Special Year etc. */
2033 Populate_Period_Arrays(l_period_array,l_qtr_array,l_spl_year_array,p_period_dist_list,p_week_dist_list,p_special_year_list,p_extra_week_period,l_no_of_years,l_last_year_end_date+1);
2034 l_extra_week_period := REPLACE(UPPER(p_extra_week_period),' ','');
2035
2036 /* Check existance of record in DDR_R_CLNDR_YR for all relevant years */
2037 l_missing_year := Check_Calendar_Year_Exists('ADVR',l_last_year_end_date+1,l_no_of_years,l_spl_year_array.COUNT);
2038 IF l_missing_year IS NOT NULL
2039 THEN
2040 Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
2041 END IF;
2042
2043 /*
2044 Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='ADVR' and ORG_CD=p_org_cd;
2045 If no such record exists
2046 then
2047 Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='ADVR';
2048 end if;
2049 */
2050 l_clndr_cd := Get_Calendar('ADVR',p_org_cd,l_mfg_org_cd);
2051
2052 /* Initialize Year Variables for loop operation */
2053 l_curr_year := l_last_year;
2054 l_curr_year_start_date := l_last_year_start_date;
2055 l_curr_year_end_date := l_last_year_end_date;
2056
2057 -- Bug# 6965786 change start
2058 /* Calculating the special periode belongs in which quater */
2059 l_spcl_qtr := Get_Spcl_Prd_Qtr(l_extra_week_period,l_qtr_array,l_period_array);
2060 -- Bug# 6965786 change end
2061
2062 /* Create records in various Advertising Calendar tables */
2063 FOR year_idx IN 1 .. l_no_of_years
2064 LOOP
2065 l_curr_year := l_curr_year + 1;
2066
2067 IF l_spl_year_array.EXISTS(to_char(l_curr_year))
2068 THEN
2069 l_spl_year_flag := TRUE;
2070 l_no_of_weeks := 53;
2071 ELSE
2072 l_spl_year_flag := FALSE;
2073 l_no_of_weeks := 52;
2074 END IF;
2075
2076 l_curr_year_start_date := l_curr_year_end_date+1;
2077 l_curr_year_end_date := l_curr_year_start_date + (7*l_no_of_weeks) - 1;
2078 l_no_year_days := l_curr_year_end_date - l_curr_year_start_date + 1;
2079 -- l_curr_year_id := TO_NUMBER(TO_CHAR(l_curr_year_start_date,g_YR_ID_format));
2080 SELECT DDR_R_ADVR_YR_SEQ.NEXTVAL
2081 INTO l_curr_year_id
2082 FROM DUAL;
2083 l_curr_year_desc := 'AY ' || TO_CHAR(l_curr_year);
2084
2085 insert into DDR_R_ADVR_YR (
2086 ADVR_YR_ID,
2087 MFG_ORG_CD,
2088 CLNDR_CD,
2089 YR_CD,
2090 YR_NBR,
2091 YR_DESC,
2092 YR_STRT_DT,
2093 YR_END_DT,
2094 YR_TIMESPN,
2095 SRC_SYS_IDNT,
2096 SRC_SYS_DT,
2097 CRTD_BY_DSR,
2098 LAST_UPDT_BY_DSR,
2099 CREATED_BY,
2100 CREATION_DATE,
2101 LAST_UPDATED_BY,
2102 LAST_UPDATE_DATE,
2103 LAST_UPDATE_LOGIN
2104 )
2105 values (
2106 l_curr_year_id,
2107 l_mfg_org_cd,
2108 l_clndr_cd,
2109 l_curr_year,
2110 l_curr_year,
2111 l_curr_year_desc,
2112 l_curr_year_start_date,
2113 l_curr_year_end_date,
2114 l_no_year_days,
2115 g_src_sys_idnt,
2116 g_src_sys_dt,
2117 g_crtd_by_DSR,
2118 g_last_updt_by_DSR,
2119 g_created_by,
2120 g_creation_date,
2121 g_last_updated_by,
2122 g_last_update_date,
2123 g_last_update_login
2124 );
2125
2126 /* Initialize Variables for loop operation */
2127 l_curr_qtr_end_date := l_curr_year_start_date - 1;
2128 l_curr_period_end_date := l_curr_year_start_date - 1;
2132 l_week_no := 0;
2129 l_curr_date := l_curr_year_start_date - 1;
2130 l_qtr_no := 0;
2131 l_period_no := 0;
2133 l_day_no := 0;
2134
2135 FOR week_idx IN 1 .. l_no_of_weeks
2136 LOOP
2137 l_curr_date := l_curr_year_start_date + 7 * (week_idx-1);
2138
2139 IF (l_curr_date > l_curr_qtr_end_date) /* New Quarter */
2140 THEN
2141 l_qtr_no := l_qtr_no + 1;
2142 l_curr_qtr_start_date := l_curr_date;
2143 l_qtr_idx_name := 'Q' || to_char(l_qtr_no);
2144 -- Bug# 6965786 change start
2145 -- IF (l_spl_year_flag = TRUE)
2146 IF (l_spl_year_flag = TRUE) AND l_spcl_qtr = l_qtr_idx_name
2147 -- Bug# 6965786 change end
2148 THEN
2149 l_no_of_weeks_qtr := l_qtr_array(l_qtr_idx_name) + 1;
2150 ELSE
2151 l_no_of_weeks_qtr := l_qtr_array(l_qtr_idx_name);
2152 END IF;
2153
2154 l_curr_qtr_end_date := l_curr_qtr_start_date + 7*l_no_of_weeks_qtr - 1;
2155 -- l_curr_qtr_id := TO_NUMBER(TO_CHAR(l_curr_qtr_start_date,g_QTR_ID_format));
2156 SELECT DDR_R_ADVR_QTR_SEQ.NEXTVAL
2157 INTO l_curr_qtr_id
2158 FROM DUAL;
2159 l_curr_qtr := TO_NUMBER(TO_CHAR(l_curr_year) || TO_CHAR(l_qtr_no));
2160
2161 insert into DDR_R_ADVR_QTR (
2162 ADVR_QTR_ID,
2163 MFG_ORG_CD,
2164 CLNDR_CD,
2165 QTR_CD,
2166 QTR_NBR,
2167 QTR_DESC,
2168 QTR_STRT_DT,
2169 QTR_END_DT,
2170 QTR_TIMESPN,
2171 ADVR_YR_ID,
2172 YR_CD,
2173 SRC_SYS_IDNT,
2174 SRC_SYS_DT,
2175 CRTD_BY_DSR,
2176 LAST_UPDT_BY_DSR,
2177 CREATED_BY,
2178 CREATION_DATE,
2179 LAST_UPDATED_BY,
2180 LAST_UPDATE_DATE,
2181 LAST_UPDATE_LOGIN
2182 )
2183 values (
2184 l_curr_qtr_id,
2185 l_mfg_org_cd,
2186 l_clndr_cd,
2187 l_curr_qtr,
2188 l_curr_qtr,
2189 l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
2190 l_curr_qtr_start_date,
2191 l_curr_qtr_end_date,
2192 l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
2193 l_curr_year_id,
2194 l_curr_year,
2195 g_src_sys_idnt,
2196 g_src_sys_dt,
2197 g_crtd_by_DSR,
2198 g_last_updt_by_DSR,
2199 g_created_by,
2200 g_creation_date,
2201 g_last_updated_by,
2202 g_last_update_date,
2203 g_last_update_login
2204 );
2205 END IF;
2206
2207 IF (l_curr_date > l_curr_period_end_date) /* New Period */
2208 THEN
2209 l_period_no := l_period_no + 1;
2210 l_curr_period_start_date := l_curr_date;
2211 l_period_idx_name := 'P' || to_char(l_period_no);
2212
2213 IF ( (l_spl_year_flag = TRUE) AND (l_extra_week_period = l_period_idx_name) )
2214 THEN
2215 l_no_of_weeks_period := l_period_array(l_period_idx_name) + 1;
2216 ELSE
2217 l_no_of_weeks_period := l_period_array(l_period_idx_name);
2218 END IF;
2219
2220 l_curr_period_end_date := l_curr_period_start_date + 7*l_no_of_weeks_period - 1;
2221 -- l_curr_period_id := TO_NUMBER(TO_CHAR(l_curr_period_start_date,g_PRD_ID_format));
2222 SELECT DDR_R_ADVR_PRD_SEQ.NEXTVAL
2223 INTO l_curr_period_id
2224 FROM DUAL;
2225 l_curr_period := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_period_no),2,'0'));
2226
2227 insert into DDR_R_ADVR_PRD (
2228 ADVR_PRD_ID,
2229 MFG_ORG_CD,
2230 CLNDR_CD,
2231 PRD_CD,
2232 PRD_NBR,
2233 PRD_DESC,
2234 PRD_STRT_DT,
2235 PRD_END_DT,
2236 PRD_TIMESPN,
2237 ADVR_QTR_ID,
2238 QTR_CD,
2239 SRC_SYS_IDNT,
2240 SRC_SYS_DT,
2241 CRTD_BY_DSR,
2242 LAST_UPDT_BY_DSR,
2243 CREATED_BY,
2244 CREATION_DATE,
2245 LAST_UPDATED_BY,
2246 LAST_UPDATE_DATE,
2247 LAST_UPDATE_LOGIN
2248 )
2249 values (
2250 l_curr_period_id,
2251 l_mfg_org_cd,
2252 l_clndr_cd,
2253 l_curr_period,
2254 l_curr_period,
2255 l_curr_year_desc || ' P' || TO_CHAR(l_period_no),
2256 l_curr_period_start_date,
2257 l_curr_period_end_date,
2258 l_curr_period_end_date - l_curr_period_start_date + 1,
2259 l_curr_qtr_id,
2263 g_crtd_by_DSR,
2260 l_curr_qtr,
2261 g_src_sys_idnt,
2262 g_src_sys_dt,
2264 g_last_updt_by_DSR,
2265 g_created_by,
2266 g_creation_date,
2267 g_last_updated_by,
2268 g_last_update_date,
2269 g_last_update_login
2270 );
2271 END IF;
2272
2273 /* Insert Record into Week table */
2274 l_week_no := l_week_no + 1;
2275 l_curr_week_start_date := l_curr_date;
2276 l_curr_week_end_date := l_curr_week_start_date + 7 - 1;
2277 -- l_curr_week_id := TO_NUMBER(TO_CHAR(l_curr_week_start_date,g_WK_ID_format));
2278 SELECT DDR_R_ADVR_WK_SEQ.NEXTVAL
2279 INTO l_curr_week_id
2280 FROM DUAL;
2281 l_curr_week := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_week_no),2,'0'));
2282
2283 insert into DDR_R_ADVR_WK (
2284 ADVR_WK_ID,
2285 MFG_ORG_CD,
2286 CLNDR_CD,
2287 WK_CD,
2288 WK_NBR,
2289 WK_DESC,
2290 WK_STRT_DT,
2291 WK_END_DT,
2292 WK_TIMESPN,
2293 ADVR_PRD_ID,
2294 PRD_CD,
2295 SRC_SYS_IDNT,
2296 SRC_SYS_DT,
2297 CRTD_BY_DSR,
2298 LAST_UPDT_BY_DSR,
2299 CREATED_BY,
2300 CREATION_DATE,
2301 LAST_UPDATED_BY,
2302 LAST_UPDATE_DATE,
2303 LAST_UPDATE_LOGIN
2304 )
2305 values (
2306 l_curr_week_id,
2307 l_mfg_org_cd,
2308 l_clndr_cd,
2309 l_curr_week,
2310 l_curr_week,
2311 l_curr_year_desc || ' W' || TO_CHAR(l_week_no),
2312 l_curr_week_start_date,
2313 l_curr_week_end_date,
2314 l_curr_week_end_date - l_curr_week_start_date + 1,
2315 l_curr_period_id,
2316 l_curr_period,
2317 g_src_sys_idnt,
2318 g_src_sys_dt,
2319 g_crtd_by_DSR,
2320 g_last_updt_by_DSR,
2321 g_created_by,
2322 g_creation_date,
2323 g_last_updated_by,
2324 g_last_update_date,
2325 g_last_update_login
2326 );
2327
2328 /* Insert Records into Base Day table */
2329 l_curr_date := l_curr_date - 1;
2330 FOR day_idx IN 1 .. 7
2331 LOOP
2332 l_curr_date := l_curr_date + 1;
2333 l_day_no := l_day_no + 1;
2334 l_curr_day_id := TO_NUMBER(TO_CHAR(l_curr_date,g_DAY_ID_format));
2335
2336 insert into DDR_R_BASE_DAY (
2337 BASE_DAY_ID,
2338 MFG_ORG_CD,
2339 CLNDR_CD,
2340 DAY_CD,
2341 CLNDR_TYP,
2342 WK_ID,
2343 WK_CD,
2344 MNTH_ID,
2345 MNTH_CD,
2346 DAY_OF_YR,
2347 WKEND_IND,
2348 CLNDR_STRT_DT,
2349 CLNDR_END_DT,
2350 SRC_SYS_IDNT,
2351 SRC_SYS_DT,
2352 CRTD_BY_DSR,
2353 LAST_UPDT_BY_DSR,
2354 CREATED_BY,
2355 CREATION_DATE,
2356 LAST_UPDATED_BY,
2357 LAST_UPDATE_DATE,
2358 LAST_UPDATE_LOGIN
2359 )
2360 values (
2361 DDR_R_BASE_DAY_SEQ.NEXTVAL,
2362 l_mfg_org_cd,
2363 l_clndr_cd,
2364 l_curr_day_id,
2365 'ADVR',
2366 l_curr_week_id,
2367 l_curr_week,
2368 null,
2369 null,
2370 l_day_no,
2371 decode(TRIM(TO_CHAR(l_curr_date,'DAY')),'SATURDAY','Y','SUNDAY','Y','N'),
2372 l_curr_date,
2373 l_curr_date,
2374 g_src_sys_idnt,
2375 g_src_sys_dt,
2376 g_crtd_by_DSR,
2377 g_last_updt_by_DSR,
2378 g_created_by,
2379 g_creation_date,
2380 g_last_updated_by,
2381 g_last_update_date,
2382 g_last_update_login
2383 );
2384 END LOOP;
2385
2386 END LOOP; /* end of week loop */
2387
2388 END LOOP; /* end of year loop */
2389
2390 COMMIT;
2391 END Populate_ADVR_Calendar;
2392
2393 PROCEDURE Populate_PLNG_Calendar (
2394 p_org_cd IN VARCHAR2,
2395 p_no_of_years IN NUMBER,
2396 p_start_date IN DATE DEFAULT NULL,
2397 p_period_dist_list IN VARCHAR2,
2398 p_week_dist_list IN VARCHAR2,
2399 p_special_year_list IN VARCHAR2,
2400 p_extra_week_period IN VARCHAR2
2401 )
2402 AS
2403 l_qtr_array Number_Tab;
2404 l_period_array Number_Tab;
2408 l_last_year NUMBER;
2405 l_spl_year_array Number_Tab;
2406 l_extra_week_period VARCHAR2(30);
2407
2409 l_last_year_start_date DATE;
2410 l_last_year_end_date DATE;
2411
2412 l_org_type DDR_R_ORG.ORG_TYP%TYPE;
2413 l_mfg_org_cd DDR_R_ORG.ORG_CD%TYPE;
2414 l_clndr_cd DDR_R_CLNDR.CLNDR_CD%TYPE;
2415 l_missing_year VARCHAR2(100);
2416
2417 l_spl_year_flag BOOLEAN := FALSE;
2418 l_no_of_years NUMBER;
2419 l_no_of_weeks NUMBER;
2420 l_no_year_days NUMBER;
2421 l_curr_year_id NUMBER;
2422 l_curr_year NUMBER;
2423 l_curr_year_start_date DATE;
2424 l_curr_year_end_date DATE;
2425 l_curr_year_desc VARCHAR2(40);
2426 l_curr_qtr_id NUMBER;
2427 l_curr_qtr NUMBER;
2428 l_curr_qtr_start_date DATE;
2429 l_curr_qtr_end_date DATE;
2430 l_curr_period_id NUMBER;
2431 l_curr_period NUMBER;
2432 l_curr_period_start_date DATE;
2433 l_curr_period_end_date DATE;
2434 l_curr_week_id NUMBER;
2435 l_curr_week NUMBER;
2436 l_curr_week_start_date DATE;
2437 l_curr_week_end_date DATE;
2438 l_curr_day_id NUMBER;
2439 l_curr_date DATE;
2440 l_qtr_no NUMBER;
2441 l_period_no NUMBER;
2442 l_week_no NUMBER;
2443 l_no_of_weeks_qtr NUMBER;
2444 l_no_of_weeks_period NUMBER;
2445 l_day_no NUMBER;
2446 l_qtr_idx_name VARCHAR2(30);
2447 l_period_idx_name VARCHAR2(30);
2448 -- Bug# 6965786 change start
2449 l_spcl_qtr VARCHAR2(30);
2450 -- Bug# 6965786 change end
2451 BEGIN
2452 /*
2453 Validate that p_org_cd is not null and also it is a valid organization as per DDR_R_ORG table
2454 Check that Organization Type in ('MFG','RTL')
2455 */
2456 IF p_org_cd IS NULL
2457 THEN
2458 Raise_Error('Organization Code must be specified');
2459 END IF;
2460
2461 l_org_type := Get_Organization_Type(p_org_cd);
2462 IF l_org_type IS NULL
2463 THEN
2464 Raise_Error('Invalid Organization');
2465 END IF;
2466
2467 IF l_org_type NOT IN ('MFG')
2468 THEN
2469 Raise_Error('Planning Calendar can be defined for Manufacturer only');
2470 END IF;
2471
2472 /* Set the Manufacturer code with the given organization code */
2473 l_mfg_org_cd := p_org_cd;
2474
2475 /* Check existence of record in DDR_R_ADVR_YR and get last year details */
2476 Get_Last_Year_Details('PLNG',p_org_cd,l_last_year,l_last_year_start_date,l_last_year_end_date);
2477 IF l_last_year IS NULL /* Last year record does not exist */
2478 THEN
2479 IF p_start_date IS NULL
2480 THEN
2481 Raise_Error('Start Date must be specified');
2482 END IF;
2483 l_last_year := TO_NUMBER(TO_CHAR(p_start_date,'YYYY'))-1;
2484 l_last_year_end_date := p_start_date - 1;
2485 -- Bug# 6863276 change start
2486 ELSE
2487 IF p_start_date IS NOT NULL
2488 THEN
2489 Raise_Error('Start Date must be NULL');
2490 END IF;
2491 -- Bug# 6863276 change end
2492 END IF;
2493
2494 l_no_of_years := nvl(p_no_of_years,1);
2495
2496 /* Populate PLNG different arrays like Quarter, Period, Special Year etc. */
2497 Populate_Period_Arrays(l_period_array,l_qtr_array,l_spl_year_array,p_period_dist_list,p_week_dist_list,p_special_year_list,p_extra_week_period,l_no_of_years,l_last_year_end_date+1);
2498 l_extra_week_period := REPLACE(UPPER(p_extra_week_period),' ','');
2499
2500 /* Check existance of record in DDR_R_CLNDR_YR for all relevant years */
2501 l_missing_year := Check_Calendar_Year_Exists('PLNG',l_last_year_end_date+1,l_no_of_years,l_spl_year_array.COUNT);
2502 IF l_missing_year IS NOT NULL
2503 THEN
2504 Raise_Error('Standard Calendar to be populated for years (' || l_missing_year || ')');
2505 END IF;
2506
2507 /*
2508 Check whether record exists in DDR_R_CLNDR for CLNDR_TYP='PLNG' and ORG_CD=p_org_cd;
2509 If no such record exists
2510 then
2511 Insert a record into DDR_R_CLNDR for the organization p_org_cd with CLNDR_TYP='PLNG';
2512 end if;
2513 */
2514 l_clndr_cd := Get_Calendar('PLNG',p_org_cd,l_mfg_org_cd);
2515
2516 /* Initialize Year Variables for loop operation */
2517 l_curr_year := l_last_year;
2518 l_curr_year_start_date := l_last_year_start_date;
2519 l_curr_year_end_date := l_last_year_end_date;
2520
2521 -- Bug# 6965786 change start
2522 /* Calculating the special periode belongs in which quater */
2523 l_spcl_qtr := Get_Spcl_Prd_Qtr(l_extra_week_period,l_qtr_array,l_period_array);
2524 -- Bug# 6965786 change end
2525
2526 /* Create records in various Planning Calendar tables */
2527 FOR year_idx IN 1 .. l_no_of_years
2528 LOOP
2529 l_curr_year := l_curr_year + 1;
2530
2531 IF l_spl_year_array.EXISTS(to_char(l_curr_year))
2532 THEN
2533 l_spl_year_flag := TRUE;
2534 l_no_of_weeks := 53;
2535 ELSE
2536 l_spl_year_flag := FALSE;
2537 l_no_of_weeks := 52;
2538 END IF;
2539
2540 l_curr_year_start_date := l_curr_year_end_date+1;
2544 SELECT DDR_R_PLNG_YR_SEQ.NEXTVAL
2541 l_curr_year_end_date := l_curr_year_start_date + (7*l_no_of_weeks) - 1;
2542 l_no_year_days := l_curr_year_end_date - l_curr_year_start_date + 1;
2543 -- l_curr_year_id := TO_NUMBER(TO_CHAR(l_curr_year_start_date,g_YR_ID_format));
2545 INTO l_curr_year_id
2546 FROM DUAL;
2547 l_curr_year_desc := 'PY ' || TO_CHAR(l_curr_year);
2548
2549 insert into DDR_R_PLNG_YR (
2550 PLNG_YR_ID,
2551 MFG_ORG_CD,
2552 CLNDR_CD,
2553 YR_CD,
2554 YR_NBR,
2555 YR_DESC,
2556 YR_STRT_DT,
2557 YR_END_DT,
2558 YR_TIMESPN,
2559 SRC_SYS_IDNT,
2560 SRC_SYS_DT,
2561 CRTD_BY_DSR,
2562 LAST_UPDT_BY_DSR,
2563 CREATED_BY,
2564 CREATION_DATE,
2565 LAST_UPDATED_BY,
2566 LAST_UPDATE_DATE,
2567 LAST_UPDATE_LOGIN
2568 )
2569 values (
2570 l_curr_year_id,
2571 l_mfg_org_cd,
2572 l_clndr_cd,
2573 l_curr_year,
2574 l_curr_year,
2575 l_curr_year_desc,
2576 l_curr_year_start_date,
2577 l_curr_year_end_date,
2578 l_no_year_days,
2579 g_src_sys_idnt,
2580 g_src_sys_dt,
2581 g_crtd_by_DSR,
2582 g_last_updt_by_DSR,
2583 g_created_by,
2584 g_creation_date,
2585 g_last_updated_by,
2586 g_last_update_date,
2587 g_last_update_login
2588 );
2589
2590 /* Initialize Variables for loop operation */
2591 l_curr_qtr_end_date := l_curr_year_start_date - 1;
2592 l_curr_period_end_date := l_curr_year_start_date - 1;
2593 l_curr_date := l_curr_year_start_date - 1;
2594 l_qtr_no := 0;
2595 l_period_no := 0;
2596 l_week_no := 0;
2597 l_day_no := 0;
2598
2599 FOR week_idx IN 1 .. l_no_of_weeks
2600 LOOP
2601 l_curr_date := l_curr_year_start_date + 7 * (week_idx-1);
2602
2603 IF (l_curr_date > l_curr_qtr_end_date) /* New Quarter */
2604 THEN
2605 l_qtr_no := l_qtr_no + 1;
2606 l_curr_qtr_start_date := l_curr_date;
2607 l_qtr_idx_name := 'Q' || to_char(l_qtr_no);
2608
2609 -- Bug# 6965786 change start
2610 -- IF (l_spl_year_flag = TRUE)
2611 IF (l_spl_year_flag = TRUE) AND l_spcl_qtr = l_qtr_idx_name
2612 -- Bug# 6965786 change end
2613 THEN
2614 l_no_of_weeks_qtr := l_qtr_array(l_qtr_idx_name) + 1;
2615 ELSE
2616 l_no_of_weeks_qtr := l_qtr_array(l_qtr_idx_name);
2617 END IF;
2618
2619 l_curr_qtr_end_date := l_curr_qtr_start_date + 7*l_no_of_weeks_qtr - 1;
2620 -- l_curr_qtr_id := TO_NUMBER(TO_CHAR(l_curr_qtr_start_date,g_QTR_ID_format));
2621 SELECT DDR_R_PLNG_QTR_SEQ.NEXTVAL
2622 INTO l_curr_qtr_id
2623 FROM DUAL;
2624 l_curr_qtr := TO_NUMBER(TO_CHAR(l_curr_year) || TO_CHAR(l_qtr_no));
2625
2626 insert into DDR_R_PLNG_QTR (
2627 PLNG_QTR_ID,
2628 MFG_ORG_CD,
2629 CLNDR_CD,
2630 QTR_CD,
2631 QTR_NBR,
2632 QTR_DESC,
2633 QTR_STRT_DT,
2634 QTR_END_DT,
2635 QTR_TIMESPN,
2636 PLNG_YR_ID,
2637 YR_CD,
2638 SRC_SYS_IDNT,
2639 SRC_SYS_DT,
2640 CRTD_BY_DSR,
2641 LAST_UPDT_BY_DSR,
2642 CREATED_BY,
2643 CREATION_DATE,
2644 LAST_UPDATED_BY,
2645 LAST_UPDATE_DATE,
2646 LAST_UPDATE_LOGIN
2647 )
2648 values (
2649 l_curr_qtr_id,
2650 l_mfg_org_cd,
2651 l_clndr_cd,
2652 l_curr_qtr,
2653 l_curr_qtr,
2654 l_curr_year_desc || ' Q' || TO_CHAR(l_qtr_no),
2655 l_curr_qtr_start_date,
2656 l_curr_qtr_end_date,
2657 l_curr_qtr_end_date - l_curr_qtr_start_date + 1,
2658 l_curr_year_id,
2659 l_curr_year,
2660 g_src_sys_idnt,
2661 g_src_sys_dt,
2662 g_crtd_by_DSR,
2663 g_last_updt_by_DSR,
2664 g_created_by,
2665 g_creation_date,
2666 g_last_updated_by,
2667 g_last_update_date,
2668 g_last_update_login
2669 );
2670 END IF;
2671
2672 IF (l_curr_date > l_curr_period_end_date) /* New Period */
2673 THEN
2674 l_period_no := l_period_no + 1;
2675 l_curr_period_start_date := l_curr_date;
2676 l_period_idx_name := 'P' || to_char(l_period_no);
2677
2678 IF ( (l_spl_year_flag = TRUE) AND (l_extra_week_period = l_period_idx_name) )
2679 THEN
2680 l_no_of_weeks_period := l_period_array(l_period_idx_name) + 1;
2684
2681 ELSE
2682 l_no_of_weeks_period := l_period_array(l_period_idx_name);
2683 END IF;
2685 l_curr_period_end_date := l_curr_period_start_date + 7*l_no_of_weeks_period - 1;
2686 -- l_curr_period_id := TO_NUMBER(TO_CHAR(l_curr_period_start_date,g_PRD_ID_format));
2687 SELECT DDR_R_PLNG_PRD_SEQ.NEXTVAL
2688 INTO l_curr_period_id
2689 FROM DUAL;
2690 l_curr_period := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_period_no),2,'0'));
2691
2692 insert into DDR_R_PLNG_PRD (
2693 PLNG_PRD_ID,
2694 MFG_ORG_CD,
2695 CLNDR_CD,
2696 PRD_CD,
2697 PRD_NBR,
2698 PRD_DESC,
2699 PRD_STRT_DT,
2700 PRD_END_DT,
2701 PRD_TIMESPN,
2702 PLNG_QTR_ID,
2703 QTR_CD,
2704 SRC_SYS_IDNT,
2705 SRC_SYS_DT,
2706 CRTD_BY_DSR,
2707 LAST_UPDT_BY_DSR,
2708 CREATED_BY,
2709 CREATION_DATE,
2710 LAST_UPDATED_BY,
2711 LAST_UPDATE_DATE,
2712 LAST_UPDATE_LOGIN
2713 )
2714 values (
2715 l_curr_period_id,
2716 l_mfg_org_cd,
2717 l_clndr_cd,
2718 l_curr_period,
2719 l_curr_period,
2720 l_curr_year_desc || ' P' || TO_CHAR(l_period_no),
2721 l_curr_period_start_date,
2722 l_curr_period_end_date,
2723 l_curr_period_end_date - l_curr_period_start_date + 1,
2724 l_curr_qtr_id,
2725 l_curr_qtr,
2726 g_src_sys_idnt,
2727 g_src_sys_dt,
2728 g_crtd_by_DSR,
2729 g_last_updt_by_DSR,
2730 g_created_by,
2731 g_creation_date,
2732 g_last_updated_by,
2733 g_last_update_date,
2734 g_last_update_login
2735 );
2736 END IF;
2737
2738 /* Insert Record into Week table */
2739 l_week_no := l_week_no + 1;
2740 l_curr_week_start_date := l_curr_date;
2741 l_curr_week_end_date := l_curr_week_start_date + 7 - 1;
2742 -- l_curr_week_id := TO_NUMBER(TO_CHAR(l_curr_week_start_date,g_WK_ID_format));
2743 SELECT DDR_R_PLNG_WK_SEQ.NEXTVAL
2744 INTO l_curr_week_id
2745 FROM DUAL;
2746 l_curr_week := TO_NUMBER(TO_CHAR(l_curr_year) || LPAD(TO_CHAR(l_week_no),2,'0'));
2747
2748 insert into DDR_R_PLNG_WK (
2749 PLNG_WK_ID,
2750 MFG_ORG_CD,
2751 CLNDR_CD,
2752 WK_CD,
2753 WK_NBR,
2754 WK_DESC,
2755 WK_STRT_DT,
2756 WK_END_DT,
2757 WK_TIMESPN,
2758 PLNG_PRD_ID,
2759 PRD_CD,
2760 SRC_SYS_IDNT,
2761 SRC_SYS_DT,
2762 CRTD_BY_DSR,
2763 LAST_UPDT_BY_DSR,
2764 CREATED_BY,
2765 CREATION_DATE,
2766 LAST_UPDATED_BY,
2767 LAST_UPDATE_DATE,
2768 LAST_UPDATE_LOGIN
2769 )
2770 values (
2771 l_curr_week_id,
2772 l_mfg_org_cd,
2773 l_clndr_cd,
2774 l_curr_week,
2775 l_curr_week,
2776 l_curr_year_desc || ' W' || TO_CHAR(l_week_no),
2777 l_curr_week_start_date,
2778 l_curr_week_end_date,
2779 l_curr_week_end_date - l_curr_week_start_date + 1,
2780 l_curr_period_id,
2781 l_curr_period,
2782 g_src_sys_idnt,
2783 g_src_sys_dt,
2784 g_crtd_by_DSR,
2785 g_last_updt_by_DSR,
2786 g_created_by,
2787 g_creation_date,
2788 g_last_updated_by,
2789 g_last_update_date,
2790 g_last_update_login
2791 );
2792
2793 /* Insert Records into Base Day table */
2794 l_curr_date := l_curr_date - 1;
2795 FOR day_idx IN 1 .. 7
2796 LOOP
2797 l_curr_date := l_curr_date + 1;
2798 l_day_no := l_day_no + 1;
2799 l_curr_day_id := TO_NUMBER(TO_CHAR(l_curr_date,g_DAY_ID_format));
2800
2801 insert into DDR_R_BASE_DAY (
2802 BASE_DAY_ID,
2803 MFG_ORG_CD,
2804 CLNDR_CD,
2805 DAY_CD,
2806 CLNDR_TYP,
2807 WK_ID,
2808 WK_CD,
2809 MNTH_ID,
2810 MNTH_CD,
2811 DAY_OF_YR,
2812 WKEND_IND,
2813 CLNDR_STRT_DT,
2814 CLNDR_END_DT,
2815 SRC_SYS_IDNT,
2816 SRC_SYS_DT,
2817 CRTD_BY_DSR,
2818 LAST_UPDT_BY_DSR,
2819 CREATED_BY,
2820 CREATION_DATE,
2821 LAST_UPDATED_BY,
2822 LAST_UPDATE_DATE,
2823 LAST_UPDATE_LOGIN
2824 )
2825 values (
2826 DDR_R_BASE_DAY_SEQ.NEXTVAL,
2827 l_mfg_org_cd,
2828 l_clndr_cd,
2829 l_curr_day_id,
2830 'PLNG',
2831 l_curr_week_id,
2832 l_curr_week,
2833 null,
2834 null,
2835 l_day_no,
2836 decode(TRIM(TO_CHAR(l_curr_date,'DAY')),'SATURDAY','Y','SUNDAY','Y','N'),
2837 l_curr_date,
2838 l_curr_date,
2839 g_src_sys_idnt,
2840 g_src_sys_dt,
2841 g_crtd_by_DSR,
2842 g_last_updt_by_DSR,
2843 g_created_by,
2844 g_creation_date,
2845 g_last_updated_by,
2846 g_last_update_date,
2847 g_last_update_login
2848 );
2849 END LOOP;
2850
2851 END LOOP; /* end of week loop */
2852
2853 END LOOP; /* end of year loop */
2854
2855 COMMIT;
2856 END Populate_PLNG_Calendar;
2857
2858 END ddr_pop_calendar_pkg;