[Home] [Help]
PACKAGE BODY: APPS.DDR_TIME_TRANSFORM_PKG
Source
1 PACKAGE BODY ddr_time_transform_pkg AS
2 /* $Header: ddrttfmb.pls 120.2.12020000.2 2012/07/12 21:39:33 gglover ship $ */
3 g_src_sys_idnt VARCHAR2(40) := 'SQL-Script';
4 g_src_sys_dt DATE := sysdate;
5 g_crtd_by_DSR VARCHAR2(30) := USER;
6 g_last_updt_by_DSR VARCHAR2(30) := USER;
7 g_created_by NUMBER(15) := -1;
8 g_creation_date DATE := sysdate;
9 g_last_updated_by NUMBER(15) := -1;
10 g_last_update_date DATE := sysdate;
11 g_last_update_login NUMBER(15) := -1;
12
13 FUNCTION Add_Day (p_day_code IN VARCHAR2,p_no_of_days IN NUMBER DEFAULT 1)
14 RETURN VARCHAR2
15 AS
16 l_new_day_code VARCHAR2(10);
17 BEGIN
18 l_new_day_code := TO_CHAR(TO_DATE(p_day_code,'YYYYMMDD') + p_no_of_days,'YYYYMMDD');
19 RETURN l_new_day_code;
20 END Add_Day;
21
22 FUNCTION Add_Week (p_week_code IN VARCHAR2,p_no_of_weeks IN NUMBER DEFAULT 1)
23 RETURN VARCHAR2
24 AS
25 l_year_no NUMBER;
26 l_week_no NUMBER;
27 l_no_of_weeks NUMBER;
28 l_new_year_no NUMBER;
29 l_new_week_no NUMBER;
30 l_new_week_code VARCHAR2(10);
31 l_max_week_no NUMBER := 52;
32 BEGIN
33 IF p_no_of_weeks = 0 THEN RETURN p_week_code; END IF;
34
35 l_year_no := TO_NUMBER(SUBSTR(p_week_code,1,4));
36 l_week_no := TO_NUMBER(SUBSTR(p_week_code,5));
37 l_no_of_weeks := ABS(p_no_of_weeks);
38
39 IF p_no_of_weeks > 0
40 THEN
41 IF (l_max_week_no-l_week_no) >= l_no_of_weeks
42 THEN
43 l_new_year_no := l_year_no;
44 l_new_week_no := l_week_no + l_no_of_weeks;
45 ELSE
46 l_new_year_no := l_year_no + FLOOR((l_no_of_weeks-(l_max_week_no-l_week_no))/l_max_week_no) + 1;
47 l_new_week_no := MOD(l_no_of_weeks-(l_max_week_no-l_week_no),l_max_week_no);
48 END IF;
49 ELSE /* i.e. p_no_of_weeks < 0 */
50 IF l_week_no > l_no_of_weeks
51 THEN
52 l_new_year_no := l_year_no;
53 l_new_week_no := l_week_no - l_no_of_weeks;
54 ELSE
55 l_new_year_no := l_year_no - FLOOR((l_no_of_weeks-l_week_no)/l_max_week_no) - 1;
56 l_new_week_no := l_max_week_no - MOD(l_no_of_weeks-l_week_no,l_max_week_no);
57 END IF;
58 END IF;
59
60 l_new_week_code := TO_CHAR(l_new_year_no) || LPAD(TO_CHAR(l_new_week_no),2,'0');
61 RETURN l_new_week_code;
62 END Add_Week;
63
64 FUNCTION Add_Month (p_month_code IN VARCHAR2,p_no_of_months IN NUMBER DEFAULT 1)
65 RETURN VARCHAR2
66 AS
67 l_new_month_code VARCHAR2(10);
68 BEGIN
69 l_new_month_code := TO_CHAR(ADD_MONTHS(TO_DATE(p_month_code,'YYYYMM'),p_no_of_months),'YYYYMM');
70 RETURN l_new_month_code;
71 END Add_Month;
72
73 FUNCTION Add_Quarter (p_qtr_code IN VARCHAR2,p_no_of_qtrs IN NUMBER DEFAULT 1)
74 RETURN VARCHAR2
75 AS
76 l_year_no NUMBER;
77 l_qtr_no NUMBER;
78 l_no_of_qtrs NUMBER;
79 l_new_year_no NUMBER;
80 l_new_qtr_no NUMBER;
81 l_new_qtr_code VARCHAR2(10);
82 l_max_qtr_no NUMBER := 4;
83 BEGIN
84 IF p_no_of_qtrs = 0 THEN RETURN p_qtr_code; END IF;
85
86 l_year_no := TO_NUMBER(SUBSTR(p_qtr_code,1,4));
87 l_qtr_no := TO_NUMBER(SUBSTR(p_qtr_code,5));
88 l_no_of_qtrs := ABS(p_no_of_qtrs);
89
90 IF p_no_of_qtrs > 0
91 THEN
92 IF (l_max_qtr_no-l_qtr_no) >= l_no_of_qtrs
93 THEN
94 l_new_year_no := l_year_no;
95 l_new_qtr_no := l_qtr_no + l_no_of_qtrs;
96 ELSE
97 l_new_year_no := l_year_no + FLOOR((l_no_of_qtrs-(l_max_qtr_no-l_qtr_no))/l_max_qtr_no) + 1;
98 l_new_qtr_no := MOD(l_no_of_qtrs-(l_max_qtr_no-l_qtr_no),l_max_qtr_no);
99 END IF;
100 ELSE /* i.e. p_no_of_qtrs < 0 */
101 IF l_qtr_no > l_no_of_qtrs
102 THEN
103 l_new_year_no := l_year_no;
104 l_new_qtr_no := l_qtr_no - l_no_of_qtrs;
105 ELSE
106 l_new_year_no := l_year_no - FLOOR((l_no_of_qtrs-l_qtr_no)/l_max_qtr_no) - 1;
107 l_new_qtr_no := l_max_qtr_no - MOD(l_no_of_qtrs-l_qtr_no,l_max_qtr_no);
108 END IF;
109 END IF;
110
111 l_new_qtr_code := TO_CHAR(l_new_year_no) || TO_CHAR(l_new_qtr_no);
112 RETURN l_new_qtr_code;
113 END Add_Quarter;
114
115 PROCEDURE Populate_BSNS_Transformation (
116 p_org_cd IN VARCHAR2,
117 p_start_year IN NUMBER,
118 p_end_year IN NUMBER
119 )
120 AS
121 BEGIN
122
123 /* Populate DDR_R_DAY_TRANS table */
124 delete from DDR_R_DAY_TRANS
125 where (DAY_CD,CLNDR_CD) in (
126 select DAY_CD,CLNDR_CD
127 from DDR_TIME_BSNS_DAY_V
128 where ORG_CD = p_org_cd
129 and YR_CD between to_char(p_start_year) and to_char(p_end_year)
130 );
131
135 DAY_CD,
132 insert into DDR_R_DAY_TRANS (
133 MFG_ORG_CD,
134 DAY_TRANS_ID,
136 CLNDR_CD,
137 CLNDR_TYP,
138 LAST_DAY_THIS_YR_CD,
139 LAST_WK_THIS_DAY_CD,
140 LAST_MNTH_THIS_DAY_CD,
141 LAST_PRD_THIS_DAY_CD,
142 LAST_QTR_THIS_DAY_CD,
143 LAST_YR_THIS_DAY_CD,
144 NXT_DAY_THIS_YR_CD,
145 NXT_WK_THIS_DAY_CD,
146 NXT_MNTH_THIS_DAY_CD,
147 NXT_PRD_THIS_DAY_CD,
148 NXT_QTR_THIS_DAY_CD,
149 NXT_YR_THIS_DAY_CD,
150 SRC_SYS_IDNT,
151 SRC_SYS_DT,
152 CRTD_BY_DSR,
153 LAST_UPDT_BY_DSR,
154 CREATED_BY,
155 CREATION_DATE,
156 LAST_UPDATED_BY,
157 LAST_UPDATE_DATE,
158 LAST_UPDATE_LOGIN
159 )
160 select
161 BDAY.MFG_ORG_CD,
162 DDR_R_DAY_TRANS_SEQ.NEXTVAL,
163 BDAY.DAY_CD,
164 BDAY.CLNDR_CD,
165 'BSNS',
166 PDAY.DAY_CD,
167 PWK.DAY_CD,
168 PMNTH.DAY_CD,
169 null,
170 PQTR.DAY_CD,
171 PYR.DAY_CD,
172 NDAY.DAY_CD,
173 NWK.DAY_CD,
174 NMNTH.DAY_CD,
175 null,
176 NQTR.DAY_CD,
177 NYR.DAY_CD,
178 g_src_sys_idnt,
179 g_src_sys_dt,
180 g_crtd_by_DSR,
181 g_last_updt_by_DSR,
182 g_created_by,
183 g_creation_date,
184 g_last_updated_by,
185 g_last_update_date,
186 g_last_update_login
187 from
188 DDR_TIME_BSNS_DAY_V BDAY,
189 DDR_TIME_BSNS_DAY_V PDAY,
190 DDR_TIME_BSNS_DAY_V PWK,
191 DDR_TIME_BSNS_DAY_V PMNTH,
192 DDR_TIME_BSNS_DAY_V PQTR,
193 DDR_TIME_BSNS_DAY_V PYR,
194 DDR_TIME_BSNS_DAY_V NDAY,
195 DDR_TIME_BSNS_DAY_V NWK,
196 DDR_TIME_BSNS_DAY_V NMNTH,
197 DDR_TIME_BSNS_DAY_V NQTR,
198 DDR_TIME_BSNS_DAY_V NYR
199 where BDAY.ORG_CD = p_org_cd
200 and BDAY.YR_CD between to_char(p_start_year) and to_char(p_end_year)
201 and PDAY.ORG_CD(+) = BDAY.ORG_CD
202 and PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
203 and PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
204 and PWK.ORG_CD(+) = BDAY.ORG_CD
205 and PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
206 and PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
207 and PMNTH.ORG_CD(+) = BDAY.ORG_CD
208 and PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
209 and PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
210 and PQTR.ORG_CD(+) = BDAY.ORG_CD
211 and PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
212 and PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
213 and PYR.ORG_CD(+) = BDAY.ORG_CD
214 and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
215 and PYR.DAY_CD(+) = add_day(BDAY.DAY_CD,-364)
216 and NDAY.ORG_CD(+) = BDAY.ORG_CD
217 and NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
218 and NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
219 and NWK.ORG_CD(+) = BDAY.ORG_CD
220 and NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
221 and NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
222 and NMNTH.ORG_CD(+) = BDAY.ORG_CD
223 and NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
224 and NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
225 and NQTR.ORG_CD(+) = BDAY.ORG_CD
226 and NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
227 and NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
228 and NYR.ORG_CD(+) = BDAY.ORG_CD
229 and NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
230 and NYR.DAY_CD(+) = add_day(BDAY.DAY_CD,364)
231 ;
232
233 /* Populate DDR_R_WK_TRANS table */
234 delete from DDR_R_WK_TRANS
235 where (WK_CD,CLNDR_CD) in (
236 select WK_CD,CLNDR_CD
237 from DDR_TIME_BSNS_WK_V
238 where ORG_CD = p_org_cd
239 and YR_CD between to_char(p_start_year) and to_char(p_end_year)
240 );
241
242 insert into DDR_R_WK_TRANS (
243 MFG_ORG_CD,
244 WK_TRANS_ID,
245 WK_ID,
246 WK_CD,
247 CLNDR_CD,
248 CLNDR_TYP,
249 LAST_WK_THIS_YR_ID,
250 LAST_WK_THIS_YR_CD,
251 LAST_MNTH_THIS_WK_ID,
252 LAST_MNTH_THIS_WK_CD,
253 LAST_PRD_THIS_WK_ID,
254 LAST_PRD_THIS_WK_CD,
255 LAST_QTR_THIS_WK_ID,
256 LAST_QTR_THIS_WK_CD,
257 LAST_YR_THIS_WK_ID,
258 LAST_YR_THIS_WK_CD,
259 NXT_WK_THIS_YR_WK_ID,
260 NXT_WK_THIS_YR_WK_CD,
261 NXT_MNTH_THIS_WK_ID,
262 NXT_MNTH_THIS_WK_CD,
263 NXT_PRD_THIS_WK_ID,
264 NXT_PRD_THIS_WK_CD,
265 NXT_QTR_THIS_WK_ID,
266 NXT_QTR_THIS_WK_CD,
267 NXT_YR_THIS_WK_ID,
268 NXT_YR_THIS_WK_CD,
269 SRC_SYS_IDNT,
270 SRC_SYS_DT,
271 CRTD_BY_DSR,
272 LAST_UPDT_BY_DSR,
273 CREATED_BY,
274 CREATION_DATE,
275 LAST_UPDATED_BY,
276 LAST_UPDATE_DATE,
277 LAST_UPDATE_LOGIN
278 )
279 select
280 WK.MFG_ORG_CD,
281 DDR_R_WK_TRANS_SEQ.NEXTVAL,
282 WK.WK_ID,
283 WK.WK_CD,
284 WK.CLNDR_CD,
285 'BSNS',
286 PWK.WK_ID,
287 PWK.WK_CD,
288 PMNTH.WK_ID,
289 PMNTH.WK_CD,
293 PQTR.WK_CD,
290 null,
291 null,
292 PQTR.WK_ID,
294 PYR.WK_ID,
295 PYR.WK_CD,
296 NWK.WK_ID,
297 NWK.WK_CD,
298 NMNTH.WK_ID,
299 NMNTH.WK_CD,
300 null,
301 null,
302 NQTR.WK_ID,
303 NQTR.WK_CD,
304 NYR.WK_ID,
305 NYR.WK_CD,
306 g_src_sys_idnt,
307 g_src_sys_dt,
308 g_crtd_by_DSR,
309 g_last_updt_by_DSR,
310 g_created_by,
311 g_creation_date,
312 g_last_updated_by,
313 g_last_update_date,
314 g_last_update_login
315 from
316 DDR_TIME_BSNS_WK_V WK,
317 DDR_TIME_BSNS_WK_V PWK,
318 DDR_TIME_BSNS_WK_V PMNTH,
319 DDR_TIME_BSNS_WK_V PQTR,
320 DDR_TIME_BSNS_WK_V PYR,
321 DDR_TIME_BSNS_WK_V NWK,
322 DDR_TIME_BSNS_WK_V NMNTH,
323 DDR_TIME_BSNS_WK_V NQTR,
324 DDR_TIME_BSNS_WK_V NYR
325 where WK.ORG_CD = p_org_cd
326 and WK.YR_CD between to_char(p_start_year) and to_char(p_end_year)
327 and PWK.ORG_CD(+) = WK.ORG_CD
328 and PWK.CLNDR_CD(+) = WK.CLNDR_CD
329 and PWK.WK_CD(+) = add_week(WK.WK_CD,-1)
330 and PMNTH.ORG_CD(+) = WK.ORG_CD
331 and PMNTH.CLNDR_CD(+) = WK.CLNDR_CD
332 and PMNTH.WK_CD(+) = add_week(WK.WK_CD,-4)
333 and PQTR.ORG_CD(+) = WK.ORG_CD
334 and PQTR.CLNDR_CD(+) = WK.CLNDR_CD
335 and PQTR.WK_CD(+) = add_week(WK.WK_CD,-13)
336 and PYR.ORG_CD(+) = WK.ORG_CD
337 and PYR.CLNDR_CD(+) = WK.CLNDR_CD
338 and PYR.WK_CD(+) = add_week(WK.WK_CD,-52)
339 and NWK.ORG_CD(+) = WK.ORG_CD
340 and NWK.CLNDR_CD(+) = WK.CLNDR_CD
341 and NWK.WK_CD(+) = add_week(WK.WK_CD,1)
342 and NMNTH.ORG_CD(+) = WK.ORG_CD
343 and NMNTH.CLNDR_CD(+) = WK.CLNDR_CD
344 and NMNTH.WK_CD(+) = add_week(WK.WK_CD,4)
345 and NQTR.ORG_CD(+) = WK.ORG_CD
346 and NQTR.CLNDR_CD(+) = WK.CLNDR_CD
347 and NQTR.WK_CD(+) = add_week(WK.WK_CD,13)
348 and NYR.ORG_CD(+) = WK.ORG_CD
349 and NYR.CLNDR_CD(+) = WK.CLNDR_CD
350 and NYR.WK_CD(+) = add_week(WK.WK_CD,52)
351 ;
352
353 /* Populate DDR_R_MNTH_TRANS table */
354 delete from DDR_R_MNTH_TRANS
355 where (MNTH_CD,CLNDR_CD) in (
356 select MNTH_CD,CLNDR_CD
357 from DDR_TIME_BSNS_MNTH_V
358 where ORG_CD = p_org_cd
359 and YR_CD between to_char(p_start_year) and to_char(p_end_year)
360 );
361
362 insert into DDR_R_MNTH_TRANS (
363 MFG_ORG_CD,
364 MNTH_TRANS_ID,
365 MNTH_ID,
366 MNTH_CD,
367 CLNDR_CD,
368 CLNDR_TYP,
369 LAST_MNTH_THIS_YR_ID,
370 LAST_MNTH_THIS_YR_CD,
371 LAST_QTR_THIS_MNTH_ID,
372 LAST_QTR_THIS_MNTH_CD,
373 LAST_YR_THIS_MNTH_ID,
374 LAST_YR_THIS_MNTH_CD,
375 NXT_MNTH_THIS_YR_ID,
376 NXT_MNTH_THIS_YR_CD,
377 NXT_QTR_THIS_MNTH_ID,
378 NXT_QTR_THIS_MNTH_CD,
379 NXT_YR_THIS_MNTH_ID,
380 NXT_YR_THIS_MNTH_CD,
381 SRC_SYS_IDNT,
382 SRC_SYS_DT,
383 CRTD_BY_DSR,
384 LAST_UPDT_BY_DSR,
385 CREATED_BY,
386 CREATION_DATE,
387 LAST_UPDATED_BY,
388 LAST_UPDATE_DATE,
389 LAST_UPDATE_LOGIN
390 )
391 select
392 MNTH.MFG_ORG_CD,
393 DDR_R_MNTH_TRANS_SEQ.NEXTVAL,
394 MNTH.MNTH_ID,
395 MNTH.MNTH_CD,
396 MNTH.CLNDR_CD,
397 'BSNS',
398 PMNTH.MNTH_ID,
399 PMNTH.MNTH_CD,
400 PQTR.MNTH_ID,
401 PQTR.MNTH_CD,
402 PYR.MNTH_ID,
403 PYR.MNTH_CD,
404 NMNTH.MNTH_ID,
405 NMNTH.MNTH_CD,
406 NQTR.MNTH_ID,
407 NQTR.MNTH_CD,
408 NYR.MNTH_ID,
409 NYR.MNTH_CD,
410 g_src_sys_idnt,
411 g_src_sys_dt,
412 g_crtd_by_DSR,
413 g_last_updt_by_DSR,
414 g_created_by,
415 g_creation_date,
416 g_last_updated_by,
417 g_last_update_date,
418 g_last_update_login
419 from
420 DDR_TIME_BSNS_MNTH_V MNTH,
421 DDR_TIME_BSNS_MNTH_V PMNTH,
422 DDR_TIME_BSNS_MNTH_V PQTR,
423 DDR_TIME_BSNS_MNTH_V PYR,
424 DDR_TIME_BSNS_MNTH_V NMNTH,
425 DDR_TIME_BSNS_MNTH_V NQTR,
426 DDR_TIME_BSNS_MNTH_V NYR
427 where MNTH.ORG_CD = p_org_cd
428 and MNTH.YR_CD between to_char(p_start_year) and to_char(p_end_year)
429 and PMNTH.ORG_CD(+) = MNTH.ORG_CD
430 and PMNTH.CLNDR_CD(+) = MNTH.CLNDR_CD
431 and PMNTH.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-1)
432 and PQTR.ORG_CD(+) = MNTH.ORG_CD
433 and PQTR.CLNDR_CD(+) = MNTH.CLNDR_CD
434 and PQTR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-3)
435 and PYR.ORG_CD(+) = MNTH.ORG_CD
436 and PYR.CLNDR_CD(+) = MNTH.CLNDR_CD
437 and PYR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-12)
438 and NMNTH.ORG_CD(+) = MNTH.ORG_CD
439 and NMNTH.CLNDR_CD(+) = MNTH.CLNDR_CD
440 and NMNTH.MNTH_CD(+) = add_month(MNTH.MNTH_CD,1)
441 and NQTR.ORG_CD(+) = MNTH.ORG_CD
442 and NQTR.CLNDR_CD(+) = MNTH.CLNDR_CD
446 and NYR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,12)
443 and NQTR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,3)
444 and NYR.ORG_CD(+) = MNTH.ORG_CD
445 and NYR.CLNDR_CD(+) = MNTH.CLNDR_CD
447 ;
448
449 /* Populate DDR_R_QTR_TRANS table */
450 delete from DDR_R_QTR_TRANS
451 where (QTR_CD,CLNDR_CD) in (
452 select QTR_CD,CLNDR_CD
453 from DDR_TIME_BSNS_QTR_V
454 where ORG_CD = p_org_cd
455 and YR_CD between to_char(p_start_year) and to_char(p_end_year)
456 );
457
458 insert into DDR_R_QTR_TRANS (
459 MFG_ORG_CD,
460 QTR_TRANS_ID,
461 QTR_ID,
462 QTR_CD,
463 CLNDR_CD,
464 CLNDR_TYP,
465 LAST_QTR_THIS_YR_ID,
466 LAST_QTR_THIS_YR_CD,
467 LAST_YR_THIS_QTR_ID,
468 LAST_YR_THIS_QTR_CD,
469 NXT_QTR_THIS_YR_ID,
470 NXT_QTR_THIS_YR_CD,
471 NXT_YR_THIS_QTR_ID,
472 NXT_YR_THIS_QTR_CD,
473 SRC_SYS_IDNT,
474 SRC_SYS_DT,
475 CRTD_BY_DSR,
476 LAST_UPDT_BY_DSR,
477 CREATED_BY,
478 CREATION_DATE,
479 LAST_UPDATED_BY,
480 LAST_UPDATE_DATE,
481 LAST_UPDATE_LOGIN
482 )
483 select
484 QTR.MFG_ORG_CD,
485 DDR_R_QTR_TRANS_SEQ.NEXTVAL,
486 QTR.QTR_ID,
487 QTR.QTR_CD,
488 QTR.CLNDR_CD,
489 'BSNS',
490 PQTR.QTR_ID,
491 PQTR.QTR_CD,
492 PYR.QTR_ID,
493 PYR.QTR_CD,
494 NQTR.QTR_ID,
495 NQTR.QTR_CD,
496 NYR.QTR_ID,
497 NYR.QTR_CD,
498 g_src_sys_idnt,
499 g_src_sys_dt,
500 g_crtd_by_DSR,
501 g_last_updt_by_DSR,
502 g_created_by,
503 g_creation_date,
504 g_last_updated_by,
505 g_last_update_date,
506 g_last_update_login
507 from
508 DDR_TIME_BSNS_QTR_V QTR,
509 DDR_TIME_BSNS_QTR_V PQTR,
510 DDR_TIME_BSNS_QTR_V PYR,
511 DDR_TIME_BSNS_QTR_V NQTR,
512 DDR_TIME_BSNS_QTR_V NYR
513 where QTR.ORG_CD = p_org_cd
514 and QTR.YR_CD between to_char(p_start_year) and to_char(p_end_year)
515 and PQTR.ORG_CD(+) = QTR.ORG_CD
516 and PQTR.CLNDR_CD(+) = QTR.CLNDR_CD
517 and PQTR.QTR_CD(+) = add_quarter(QTR.QTR_CD,-1)
518 and PYR.ORG_CD(+) = QTR.ORG_CD
519 and PYR.CLNDR_CD(+) = QTR.CLNDR_CD
520 and PYR.QTR_CD(+) = add_quarter(QTR.QTR_CD,-4)
521 and NQTR.ORG_CD(+) = QTR.ORG_CD
522 and NQTR.CLNDR_CD(+) = QTR.CLNDR_CD
523 and NQTR.QTR_CD(+) = add_quarter(QTR.QTR_CD,1)
524 and NYR.ORG_CD(+) = QTR.ORG_CD
525 and NYR.CLNDR_CD(+) = QTR.CLNDR_CD
526 and NYR.QTR_CD(+) = add_quarter(QTR.QTR_CD,4)
527 ;
528
529 /* Populate DDR_R_YR_TRANS table */
530 delete from DDR_R_YR_TRANS
531 where (YR_CD,CLNDR_CD) in (
532 select YR_CD,CLNDR_CD
533 from DDR_TIME_BSNS_YR_V
534 where ORG_CD = p_org_cd
535 and YR_CD between to_char(p_start_year) and to_char(p_end_year)
536 );
537
538 insert into DDR_R_YR_TRANS (
539 MFG_ORG_CD,
540 YR_TRANS_ID,
541 YR_ID,
542 YR_CD,
543 CLNDR_CD,
544 CLNDR_TYP,
545 LAST_YR_ID,
546 LAST_YR_CD,
547 NXT_YR_ID,
548 NXT_YR_CD,
549 SRC_SYS_IDNT,
550 SRC_SYS_DT,
551 CRTD_BY_DSR,
552 LAST_UPDT_BY_DSR,
553 CREATED_BY,
554 CREATION_DATE,
555 LAST_UPDATED_BY,
556 LAST_UPDATE_DATE,
557 LAST_UPDATE_LOGIN
558 )
559 select
560 YR.MFG_ORG_CD,
561 DDR_R_YR_TRANS_SEQ.NEXTVAL,
562 YR.YR_ID,
563 YR.YR_CD,
564 YR.CLNDR_CD,
565 'BSNS',
566 PYR.YR_ID,
567 PYR.YR_CD,
568 NYR.YR_ID,
569 NYR.YR_CD,
570 g_src_sys_idnt,
571 g_src_sys_dt,
572 g_crtd_by_DSR,
573 g_last_updt_by_DSR,
574 g_created_by,
575 g_creation_date,
576 g_last_updated_by,
577 g_last_update_date,
578 g_last_update_login
579 from
580 DDR_TIME_BSNS_YR_V YR,
581 DDR_TIME_BSNS_YR_V PYR,
582 DDR_TIME_BSNS_YR_V NYR
583 where YR.ORG_CD = p_org_cd
584 and YR.YR_CD between to_char(p_start_year) and to_char(p_end_year)
585 and PYR.ORG_CD(+) = YR.ORG_CD
586 and PYR.CLNDR_CD(+) = YR.CLNDR_CD
587 and PYR.YR_CD(+) = YR.YR_CD - 1
588 and NYR.ORG_CD(+) = YR.ORG_CD
589 and NYR.CLNDR_CD(+) = YR.CLNDR_CD
590 and NYR.YR_CD(+) = YR.YR_CD + 1
591 ;
592
593 /*Starting data population for todate transformations*/
594 /* Populate DDR_R_DAY_TODATE_TRANS table */
595 delete from DDR_R_DAY_TODATE_TRANS
596 where (DAY_CD,CLNDR_CD) in (
597 select DAY_CD,CLNDR_CD
598 from DDR_TIME_BSNS_DAY_V
599 where ORG_CD = p_org_cd
600 and YR_CD between to_char(p_start_year) and to_char(p_end_year)
601 );
602
603 INSERT INTO DDR_R_DAY_TODATE_TRANS(
604 MFG_ORG_CD,
605 DAY_TODATE_TRANS_ID,
606 DAY_CD,
610 SRC_SYS_IDNT,
607 CLNDR_CD,
608 CLNDR_TYP,
609 YR_DAY_CD,
611 SRC_SYS_DT,
612 CRTD_BY_DSR ,
613 LAST_UPDT_BY_DSR ,
614 CREATED_BY ,
615 CREATION_DATE ,
616 LAST_UPDATED_BY ,
617 LAST_UPDATE_DATE ,
618 LAST_UPDATE_LOGIN
619 )
620 select
621 YTD.MFG_ORG_CD,
622 DDR_R_DAY_TODATE_TRANS_SEQ.NEXTVAL,
623 YTD.DAY_CD,
624 YTD.CLNDR_CD,
625 'BSNS',
626 YTD.YR_DAY_CD,
627 g_src_sys_idnt,
628 g_src_sys_dt,
629 g_crtd_by_DSR,
630 g_last_updt_by_DSR,
631 g_created_by,
632 g_creation_date,
633 g_last_updated_by,
634 g_last_update_date,
635 g_last_update_login
636 FROM
637 (SELECT
638 A.MFG_ORG_CD MFG_ORG_CD,
639 A.DAY_CD DAY_CD,
640 A.CLNDR_CD CLNDR_CD,
641 B.DAY_CD YR_DAY_CD
642 FROM DDR_TIME_BSNS_DAY_V A,
643 DDR_TIME_BSNS_DAY_V B
644 WHERE A.YR_CD = B.YR_CD
645 AND A.DAY_CD >= B.DAY_CD
646 AND A.ORG_CD = B.ORG_CD
647 AND A.ORG_CD = p_org_cd
648 AND A.YR_CD between to_char(p_start_year) and to_char(p_end_year)
649 ORDER BY A.DAY_CD,
650 B.DAY_CD) YTD;
651
652 /* Populate DDR_R_WK_TODATE_TRANS table */
653 delete from DDR_R_WK_TODATE_TRANS
654 where (WK_CD,CLNDR_CD) in (
655 select WK_CD,CLNDR_CD
656 from DDR_TIME_BSNS_WK_V
657 where ORG_CD = p_org_cd
658 and YR_CD between to_char(p_start_year) and to_char(p_end_year)
659 );
660
661 INSERT INTO DDR_R_WK_TODATE_TRANS(
662 MFG_ORG_CD,
663 WK_TODATE_TRANS_ID,
664 WK_ID,
665 WK_CD,
666 CLNDR_CD,
667 CLNDR_TYP,
668 YR_WK_ID,
669 YR_WK_CD,
670 SRC_SYS_IDNT,
671 SRC_SYS_DT,
672 CRTD_BY_DSR ,
673 LAST_UPDT_BY_DSR ,
674 CREATED_BY ,
675 CREATION_DATE ,
676 LAST_UPDATED_BY ,
677 LAST_UPDATE_DATE ,
678 LAST_UPDATE_LOGIN
679 )
680 select
681 YTD.MFG_ORG_CD,
682 DDR_R_WK_TODATE_TRANS_SEQ.NEXTVAL,
683 YTD.WK_ID,
684 YTD.WK_CD,
685 YTD.CLNDR_CD,
686 'BSNS',
687 YTD.YR_WK_ID,
688 YTD.YR_WK_CD,
689 g_src_sys_idnt,
690 g_src_sys_dt,
691 g_crtd_by_DSR,
692 g_last_updt_by_DSR,
693 g_created_by,
694 g_creation_date,
695 g_last_updated_by,
696 g_last_update_date,
697 g_last_update_login
698 FROM
699 (SELECT
700 A.MFG_ORG_CD MFG_ORG_CD,
701 A.WK_CD WK_CD,
702 A.WK_ID WK_ID,
703 A.CLNDR_CD CLNDR_CD,
704 B.WK_CD YR_WK_CD,
705 B.WK_ID YR_WK_ID
706 FROM DDR_TIME_BSNS_WK_V A,
707 DDR_TIME_BSNS_WK_V B
708 WHERE A.YR_CD = B.YR_CD
709 AND A.WK_CD >= B.WK_CD
710 AND A.ORG_CD = B.ORG_CD
711 AND A.ORG_CD = p_org_cd
712 AND A.YR_CD between to_char(p_start_year) and to_char(p_end_year)
713 ORDER BY A.WK_CD,
714 B.WK_CD) YTD;
715
716 /* Populate DDR_R_MNTH_TODATE_TRANS table */
717 delete from DDR_R_MNTH_TODATE_TRANS
718 where (MNTH_CD,CLNDR_CD) in (
719 select MNTH_CD,CLNDR_CD
720 from DDR_TIME_BSNS_MNTH_V
721 where ORG_CD = p_org_cd
722 and YR_CD between to_char(p_start_year) and to_char(p_end_year)
723 );
724
725 INSERT INTO DDR_R_MNTH_TODATE_TRANS(
726 MFG_ORG_CD,
727 MNTH_TODATE_TRANS_ID,
728 MNTH_ID,
729 MNTH_CD,
730 CLNDR_CD,
731 CLNDR_TYP,
732 YR_MNTH_ID,
733 YR_MNTH_CD,
734 SRC_SYS_IDNT,
735 SRC_SYS_DT,
736 CRTD_BY_DSR ,
737 LAST_UPDT_BY_DSR ,
738 CREATED_BY ,
739 CREATION_DATE ,
740 LAST_UPDATED_BY ,
741 LAST_UPDATE_DATE ,
742 LAST_UPDATE_LOGIN
743 )
744 select
745 YTD.MFG_ORG_CD,
746 DDR_R_MNTH_TODATE_TRANS_SEQ.NEXTVAL,
747 YTD.MNTH_ID,
748 YTD.MNTH_CD,
749 YTD.CLNDR_CD,
750 'BSNS',
751 YTD.YR_MNTH_ID,
752 YTD.YR_MNTH_CD,
753 g_src_sys_idnt,
754 g_src_sys_dt,
755 g_crtd_by_DSR,
756 g_last_updt_by_DSR,
757 g_created_by,
758 g_creation_date,
759 g_last_updated_by,
760 g_last_update_date,
761 g_last_update_login
762 FROM
763 (SELECT
764 A.MFG_ORG_CD MFG_ORG_CD,
765 A.MNTH_CD MNTH_CD,
766 A.MNTH_ID MNTH_ID,
767 A.CLNDR_CD CLNDR_CD,
768 B.MNTH_CD YR_MNTH_CD,
769 B.MNTH_ID YR_MNTH_ID
770 FROM DDR_TIME_BSNS_MNTH_V A,
771 DDR_TIME_BSNS_MNTH_V B
772 WHERE A.YR_CD = B.YR_CD
773 AND A.MNTH_CD >= B.MNTH_CD
774 AND A.ORG_CD = B.ORG_CD
775 AND A.ORG_CD = p_org_cd
776 AND A.YR_CD between to_char(p_start_year) and to_char(p_end_year)
777 ORDER BY A.MNTH_CD,
778 B.MNTH_CD) YTD;
779
780 /* Populate DDR_R_QTR_TODATE_TRANS table */
781 delete from DDR_R_QTR_TODATE_TRANS
782 where (QTR_CD,CLNDR_CD) in (
783 select QTR_CD,CLNDR_CD
784 from DDR_TIME_BSNS_QTR_V
785 where ORG_CD = p_org_cd
786 and YR_CD between to_char(p_start_year) and to_char(p_end_year)
787 );
788
789 INSERT INTO DDR_R_QTR_TODATE_TRANS(
790 MFG_ORG_CD,
791 QTR_TODATE_TRANS_ID,
792 QTR_ID,
793 QTR_CD,
794 CLNDR_CD,
795 CLNDR_TYP,
796 YR_QTR_ID,
797 YR_QTR_CD,
801 LAST_UPDT_BY_DSR ,
798 SRC_SYS_IDNT,
799 SRC_SYS_DT,
800 CRTD_BY_DSR ,
802 CREATED_BY ,
803 CREATION_DATE ,
804 LAST_UPDATED_BY ,
805 LAST_UPDATE_DATE ,
806 LAST_UPDATE_LOGIN
807 )
808 select
809 YTD.MFG_ORG_CD,
810 DDR_R_QTR_TODATE_TRANS_SEQ.NEXTVAL,
811 YTD.QTR_ID,
812 YTD.QTR_CD,
813 YTD.CLNDR_CD,
814 'BSNS',
815 YTD.YR_QTR_ID,
816 YTD.YR_QTR_CD,
817 g_src_sys_idnt,
818 g_src_sys_dt,
819 g_crtd_by_DSR,
820 g_last_updt_by_DSR,
821 g_created_by,
822 g_creation_date,
823 g_last_updated_by,
824 g_last_update_date,
825 g_last_update_login
826 FROM
827 (SELECT
828 A.MFG_ORG_CD MFG_ORG_CD,
829 A.QTR_CD QTR_CD,
830 A.QTR_ID QTR_ID,
831 A.CLNDR_CD CLNDR_CD,
832 B.QTR_CD YR_QTR_CD,
833 B.QTR_ID YR_QTR_ID
834 FROM DDR_TIME_BSNS_QTR_V A,
835 DDR_TIME_BSNS_QTR_V B
836 WHERE A.YR_CD = B.YR_CD
837 AND A.QTR_CD >= B.QTR_CD
838 AND A.ORG_CD = B.ORG_CD
839 AND A.ORG_CD = p_org_cd
840 AND A.YR_CD between to_char(p_start_year) and to_char(p_end_year)
841 ORDER BY A.QTR_CD,
842 B.QTR_CD) YTD;
843
844 COMMIT;
845 END Populate_BSNS_Transformation;
846
847
848 PROCEDURE Populate_CLNDR_Transformation(
849 p_start_year IN NUMBER,
850 p_end_year in number )
851 as
852 v_yr_timespn number;
853 v_start_year number;
854 BEGIN
855 /* Populate DDR_R_DAY_TRANS table */
856 v_start_year:= p_start_year;
857 DELETE
858 FROM DDR_R_DAY_TRANS
859 WHERE (DAY_CD,CLNDR_CD) IN
860 (SELECT DAY_CD,
861 CLNDR_CD
862 FROM DDR_TIME_CLNDR_DAY_V
863 WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
864 );
865 WHILE (V_START_YEAR <=P_END_YEAR)
866 LOOP
867 select YR_TIMESPN
868 into V_YR_TIMESPN
869 from DDR_R_CLNDR_YR
870 where YR_CD = V_START_YEAR;
871
872 IF (mod(v_start_year,4)=0 ) THEN
873
874 INSERT
875 INTO DDR_R_DAY_TRANS
876 (
877 MFG_ORG_CD,
878 DAY_TRANS_ID,
879 DAY_CD,
880 CLNDR_CD,
881 CLNDR_TYP,
882 LAST_DAY_THIS_YR_CD,
883 LAST_WK_THIS_DAY_CD,
884 LAST_MNTH_THIS_DAY_CD,
885 LAST_PRD_THIS_DAY_CD,
886 LAST_QTR_THIS_DAY_CD,
887 LAST_YR_THIS_DAY_CD,
888 NXT_DAY_THIS_YR_CD,
889 NXT_WK_THIS_DAY_CD,
890 NXT_MNTH_THIS_DAY_CD,
891 NXT_PRD_THIS_DAY_CD,
892 NXT_QTR_THIS_DAY_CD,
893 NXT_YR_THIS_DAY_CD,
894 SRC_SYS_IDNT,
895 SRC_SYS_DT,
896 CRTD_BY_DSR,
897 LAST_UPDT_BY_DSR,
898 CREATED_BY,
899 CREATION_DATE,
900 LAST_UPDATED_BY,
901 LAST_UPDATE_DATE,
902 LAST_UPDATE_LOGIN
903 )
904 SELECT BDAY.ORG_CD,
905 DDR_R_DAY_TRANS_SEQ.NEXTVAL,
906 BDAY.DAY_CD,
907 BDAY.CLNDR_CD,
908 BDAY.CLNDR_TYP,
909 PDAY.DAY_CD,
910 PWK.DAY_CD,
911 PMNTH.DAY_CD,
912 NULL,
913 PQTR.DAY_CD,
914 PYR.DAY_CD,
915 NDAY.DAY_CD,
916 NWK.DAY_CD,
917 NMNTH.DAY_CD,
918 NULL,
919 NQTR.DAY_CD,
920 NYR.DAY_CD,
921 g_src_sys_idnt,
922 g_src_sys_dt,
923 g_crtd_by_DSR,
924 g_last_updt_by_DSR,
925 g_created_by,
926 g_creation_date,
927 g_last_updated_by,
928 g_last_update_date,
929 g_last_update_login
930 FROM DDR_TIME_CLNDR_DAY_V BDAY,
931 DDR_TIME_CLNDR_DAY_V PDAY,
932 DDR_TIME_CLNDR_DAY_V PWK,
933 DDR_TIME_CLNDR_DAY_V PMNTH,
934 DDR_TIME_CLNDR_DAY_V PQTR,
935 DDR_TIME_CLNDR_DAY_V PYR,
936 DDR_TIME_CLNDR_DAY_V NDAY,
937 DDR_TIME_CLNDR_DAY_V NWK,
938 DDR_TIME_CLNDR_DAY_V NMNTH,
939 DDR_TIME_CLNDR_DAY_V NQTR,
940 DDR_TIME_CLNDR_DAY_V NYR
941 where BDAY.YR_CD = TO_CHAR(v_start_year)
942 AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
943 AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
944 AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
945 AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
946 AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
947 AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
948 AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
949 AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
950 and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
951 and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN-1))
952 AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
953 AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
954 AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
955 AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
956 AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
957 AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
958 AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
959 AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
960 AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
961 and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
962 and BDAY.DAY_CD < V_START_YEAR||'0229';
963
964 INSERT
965 INTO DDR_R_DAY_TRANS
966 (
967 MFG_ORG_CD,
968 DAY_TRANS_ID,
969 DAY_CD,
970 CLNDR_CD,
971 CLNDR_TYP,
972 LAST_DAY_THIS_YR_CD,
973 LAST_WK_THIS_DAY_CD,
974 LAST_MNTH_THIS_DAY_CD,
975 LAST_PRD_THIS_DAY_CD,
979 NXT_WK_THIS_DAY_CD,
976 LAST_QTR_THIS_DAY_CD,
977 LAST_YR_THIS_DAY_CD,
978 NXT_DAY_THIS_YR_CD,
980 NXT_MNTH_THIS_DAY_CD,
981 NXT_PRD_THIS_DAY_CD,
982 NXT_QTR_THIS_DAY_CD,
983 NXT_YR_THIS_DAY_CD,
984 SRC_SYS_IDNT,
985 SRC_SYS_DT,
986 CRTD_BY_DSR,
987 LAST_UPDT_BY_DSR,
988 CREATED_BY,
989 CREATION_DATE,
990 LAST_UPDATED_BY,
991 LAST_UPDATE_DATE,
992 LAST_UPDATE_LOGIN
993 )
994 SELECT BDAY.ORG_CD,
995 DDR_R_DAY_TRANS_SEQ.NEXTVAL,
996 BDAY.DAY_CD,
997 BDAY.CLNDR_CD,
998 BDAY.CLNDR_TYP,
999 PDAY.DAY_CD,
1000 PWK.DAY_CD,
1001 PMNTH.DAY_CD,
1002 NULL,
1003 PQTR.DAY_CD,
1004 NULL,
1005 NDAY.DAY_CD,
1006 NWK.DAY_CD,
1007 NMNTH.DAY_CD,
1008 NULL,
1009 NQTR.DAY_CD,
1010 NULL,
1011 g_src_sys_idnt,
1012 g_src_sys_dt,
1013 g_crtd_by_DSR,
1014 g_last_updt_by_DSR,
1015 g_created_by,
1016 g_creation_date,
1017 g_last_updated_by,
1018 g_last_update_date,
1019 g_last_update_login
1020 FROM DDR_TIME_CLNDR_DAY_V BDAY,
1021 DDR_TIME_CLNDR_DAY_V PDAY,
1022 DDR_TIME_CLNDR_DAY_V PWK,
1023 DDR_TIME_CLNDR_DAY_V PMNTH,
1024 DDR_TIME_CLNDR_DAY_V PQTR,
1025 DDR_TIME_CLNDR_DAY_V PYR,
1026 DDR_TIME_CLNDR_DAY_V NDAY,
1027 DDR_TIME_CLNDR_DAY_V NWK,
1028 DDR_TIME_CLNDR_DAY_V NMNTH,
1029 DDR_TIME_CLNDR_DAY_V NQTR,
1030 DDR_TIME_CLNDR_DAY_V NYR
1031 where BDAY.YR_CD = TO_CHAR(v_start_year)
1032 AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1033 AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
1034 AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1035 AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
1036 AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1037 AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
1038 AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1039 AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
1040 and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1041 and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN-1))
1042 AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1043 AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
1044 AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1045 AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
1046 AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1047 AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
1048 AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1049 AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
1050 AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1051 and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
1052 and BDAY.DAY_CD = V_START_YEAR||'0229';
1053
1054 INSERT
1055 INTO DDR_R_DAY_TRANS
1056 (
1057 MFG_ORG_CD,
1058 DAY_TRANS_ID,
1059 DAY_CD,
1060 CLNDR_CD,
1061 CLNDR_TYP,
1062 LAST_DAY_THIS_YR_CD,
1063 LAST_WK_THIS_DAY_CD,
1064 LAST_MNTH_THIS_DAY_CD,
1065 LAST_PRD_THIS_DAY_CD,
1066 LAST_QTR_THIS_DAY_CD,
1067 LAST_YR_THIS_DAY_CD,
1068 NXT_DAY_THIS_YR_CD,
1069 NXT_WK_THIS_DAY_CD,
1070 NXT_MNTH_THIS_DAY_CD,
1071 NXT_PRD_THIS_DAY_CD,
1072 NXT_QTR_THIS_DAY_CD,
1073 NXT_YR_THIS_DAY_CD,
1074 SRC_SYS_IDNT,
1075 SRC_SYS_DT,
1076 CRTD_BY_DSR,
1077 LAST_UPDT_BY_DSR,
1078 CREATED_BY,
1079 CREATION_DATE,
1080 LAST_UPDATED_BY,
1081 LAST_UPDATE_DATE,
1082 LAST_UPDATE_LOGIN
1083 )
1084 SELECT BDAY.ORG_CD,
1085 DDR_R_DAY_TRANS_SEQ.NEXTVAL,
1086 BDAY.DAY_CD,
1087 BDAY.CLNDR_CD,
1088 BDAY.CLNDR_TYP,
1089 PDAY.DAY_CD,
1090 PWK.DAY_CD,
1091 PMNTH.DAY_CD,
1092 NULL,
1093 PQTR.DAY_CD,
1094 PYR.DAY_CD,
1095 NDAY.DAY_CD,
1096 NWK.DAY_CD,
1097 NMNTH.DAY_CD,
1098 NULL,
1099 NQTR.DAY_CD,
1100 NYR.DAY_CD,
1101 g_src_sys_idnt,
1102 g_src_sys_dt,
1103 g_crtd_by_DSR,
1104 g_last_updt_by_DSR,
1105 g_created_by,
1106 g_creation_date,
1107 g_last_updated_by,
1108 g_last_update_date,
1109 g_last_update_login
1110 FROM DDR_TIME_CLNDR_DAY_V BDAY,
1111 DDR_TIME_CLNDR_DAY_V PDAY,
1112 DDR_TIME_CLNDR_DAY_V PWK,
1113 DDR_TIME_CLNDR_DAY_V PMNTH,
1114 DDR_TIME_CLNDR_DAY_V PQTR,
1115 DDR_TIME_CLNDR_DAY_V PYR,
1116 DDR_TIME_CLNDR_DAY_V NDAY,
1117 DDR_TIME_CLNDR_DAY_V NWK,
1118 DDR_TIME_CLNDR_DAY_V NMNTH,
1119 DDR_TIME_CLNDR_DAY_V NQTR,
1120 DDR_TIME_CLNDR_DAY_V NYR
1121 where BDAY.YR_CD = TO_CHAR(v_start_year)
1122 AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1123 AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
1124 AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1125 AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
1126 AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1127 AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
1128 AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1129 AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
1130 and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1131 and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN))
1132 AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1133 AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
1134 AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1135 AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
1136 AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1137 AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
1138 AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1139 AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
1140 AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1141 and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
1142 and BDAY.DAY_CD > V_START_YEAR||'0229';
1143
1147 (
1144 elsif (mod(v_start_year-1,4)=0) THEN
1145 INSERT
1146 INTO DDR_R_DAY_TRANS
1148 MFG_ORG_CD,
1149 DAY_TRANS_ID,
1150 DAY_CD,
1151 CLNDR_CD,
1152 CLNDR_TYP,
1153 LAST_DAY_THIS_YR_CD,
1154 LAST_WK_THIS_DAY_CD,
1155 LAST_MNTH_THIS_DAY_CD,
1156 LAST_PRD_THIS_DAY_CD,
1157 LAST_QTR_THIS_DAY_CD,
1158 LAST_YR_THIS_DAY_CD,
1159 NXT_DAY_THIS_YR_CD,
1160 NXT_WK_THIS_DAY_CD,
1161 NXT_MNTH_THIS_DAY_CD,
1162 NXT_PRD_THIS_DAY_CD,
1163 NXT_QTR_THIS_DAY_CD,
1164 NXT_YR_THIS_DAY_CD,
1165 SRC_SYS_IDNT,
1166 SRC_SYS_DT,
1167 CRTD_BY_DSR,
1168 LAST_UPDT_BY_DSR,
1169 CREATED_BY,
1170 CREATION_DATE,
1171 LAST_UPDATED_BY,
1172 LAST_UPDATE_DATE,
1173 LAST_UPDATE_LOGIN
1174 )
1175 SELECT BDAY.ORG_CD,
1176 DDR_R_DAY_TRANS_SEQ.NEXTVAL,
1177 BDAY.DAY_CD,
1178 BDAY.CLNDR_CD,
1179 BDAY.CLNDR_TYP,
1180 PDAY.DAY_CD,
1181 PWK.DAY_CD,
1182 PMNTH.DAY_CD,
1183 NULL,
1184 PQTR.DAY_CD,
1185 PYR.DAY_CD,
1186 NDAY.DAY_CD,
1187 NWK.DAY_CD,
1188 NMNTH.DAY_CD,
1189 NULL,
1190 NQTR.DAY_CD,
1191 NYR.DAY_CD,
1192 g_src_sys_idnt,
1193 g_src_sys_dt,
1194 g_crtd_by_DSR,
1195 g_last_updt_by_DSR,
1196 g_created_by,
1197 g_creation_date,
1198 g_last_updated_by,
1199 g_last_update_date,
1200 g_last_update_login
1201 FROM DDR_TIME_CLNDR_DAY_V BDAY,
1202 DDR_TIME_CLNDR_DAY_V PDAY,
1203 DDR_TIME_CLNDR_DAY_V PWK,
1204 DDR_TIME_CLNDR_DAY_V PMNTH,
1205 DDR_TIME_CLNDR_DAY_V PQTR,
1206 DDR_TIME_CLNDR_DAY_V PYR,
1207 DDR_TIME_CLNDR_DAY_V NDAY,
1208 DDR_TIME_CLNDR_DAY_V NWK,
1209 DDR_TIME_CLNDR_DAY_V NMNTH,
1210 DDR_TIME_CLNDR_DAY_V NQTR,
1211 DDR_TIME_CLNDR_DAY_V NYR
1212 where BDAY.YR_CD = TO_CHAR(v_start_year)
1213 AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1214 AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
1215 AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1216 AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
1217 AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1218 AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
1219 AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1220 AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
1221 and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1222 and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN+1))
1223 AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1224 AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
1225 AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1226 AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
1227 AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1228 AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
1229 AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1230 AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
1231 AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1232 and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
1233 and BDAY.DAY_CD <= V_START_YEAR||'0228';
1234
1235 INSERT
1236 INTO DDR_R_DAY_TRANS
1237 (
1238 MFG_ORG_CD,
1239 DAY_TRANS_ID,
1240 DAY_CD,
1241 CLNDR_CD,
1242 CLNDR_TYP,
1243 LAST_DAY_THIS_YR_CD,
1244 LAST_WK_THIS_DAY_CD,
1245 LAST_MNTH_THIS_DAY_CD,
1246 LAST_PRD_THIS_DAY_CD,
1247 LAST_QTR_THIS_DAY_CD,
1248 LAST_YR_THIS_DAY_CD,
1249 NXT_DAY_THIS_YR_CD,
1250 NXT_WK_THIS_DAY_CD,
1251 NXT_MNTH_THIS_DAY_CD,
1252 NXT_PRD_THIS_DAY_CD,
1253 NXT_QTR_THIS_DAY_CD,
1254 NXT_YR_THIS_DAY_CD,
1255 SRC_SYS_IDNT,
1256 SRC_SYS_DT,
1257 CRTD_BY_DSR,
1258 LAST_UPDT_BY_DSR,
1259 CREATED_BY,
1260 CREATION_DATE,
1261 LAST_UPDATED_BY,
1262 LAST_UPDATE_DATE,
1263 LAST_UPDATE_LOGIN
1264 )
1265 SELECT BDAY.ORG_CD,
1266 DDR_R_DAY_TRANS_SEQ.NEXTVAL,
1267 BDAY.DAY_CD,
1268 BDAY.CLNDR_CD,
1269 BDAY.CLNDR_TYP,
1270 PDAY.DAY_CD,
1271 PWK.DAY_CD,
1272 PMNTH.DAY_CD,
1273 NULL,
1274 PQTR.DAY_CD,
1275 PYR.DAY_CD,
1276 NDAY.DAY_CD,
1277 NWK.DAY_CD,
1278 NMNTH.DAY_CD,
1279 NULL,
1280 NQTR.DAY_CD,
1281 NYR.DAY_CD,
1282 g_src_sys_idnt,
1283 g_src_sys_dt,
1284 g_crtd_by_DSR,
1285 g_last_updt_by_DSR,
1286 g_created_by,
1287 g_creation_date,
1288 g_last_updated_by,
1289 g_last_update_date,
1290 g_last_update_login
1291 FROM DDR_TIME_CLNDR_DAY_V BDAY,
1292 DDR_TIME_CLNDR_DAY_V PDAY,
1293 DDR_TIME_CLNDR_DAY_V PWK,
1294 DDR_TIME_CLNDR_DAY_V PMNTH,
1295 DDR_TIME_CLNDR_DAY_V PQTR,
1296 DDR_TIME_CLNDR_DAY_V PYR,
1297 DDR_TIME_CLNDR_DAY_V NDAY,
1298 DDR_TIME_CLNDR_DAY_V NWK,
1299 DDR_TIME_CLNDR_DAY_V NMNTH,
1300 DDR_TIME_CLNDR_DAY_V NQTR,
1301 DDR_TIME_CLNDR_DAY_V NYR
1302 where BDAY.YR_CD = TO_CHAR(v_start_year)
1303 AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1304 AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
1305 AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1306 AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
1307 AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1308 AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
1309 AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1310 AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
1311 and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1312 and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN))
1313 AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1314 AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
1315 AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1316 AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
1320 AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
1317 AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1318 AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
1319 AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1321 AND NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1322 and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN)
1323 and BDAY.DAY_CD > V_START_YEAR||'0228';
1324
1325 ELSE
1326
1327 INSERT
1328 INTO DDR_R_DAY_TRANS
1329 (
1330 MFG_ORG_CD,
1331 DAY_TRANS_ID,
1332 DAY_CD,
1333 CLNDR_CD,
1334 CLNDR_TYP,
1335 LAST_DAY_THIS_YR_CD,
1336 LAST_WK_THIS_DAY_CD,
1337 LAST_MNTH_THIS_DAY_CD,
1338 LAST_PRD_THIS_DAY_CD,
1339 LAST_QTR_THIS_DAY_CD,
1340 LAST_YR_THIS_DAY_CD,
1341 NXT_DAY_THIS_YR_CD,
1342 NXT_WK_THIS_DAY_CD,
1343 NXT_MNTH_THIS_DAY_CD,
1344 NXT_PRD_THIS_DAY_CD,
1345 NXT_QTR_THIS_DAY_CD,
1346 NXT_YR_THIS_DAY_CD,
1347 SRC_SYS_IDNT,
1348 SRC_SYS_DT,
1349 CRTD_BY_DSR,
1350 LAST_UPDT_BY_DSR,
1351 CREATED_BY,
1352 CREATION_DATE,
1353 LAST_UPDATED_BY,
1354 LAST_UPDATE_DATE,
1355 LAST_UPDATE_LOGIN
1356 )
1357 SELECT BDAY.ORG_CD,
1358 DDR_R_DAY_TRANS_SEQ.NEXTVAL,
1359 BDAY.DAY_CD,
1360 BDAY.CLNDR_CD,
1361 BDAY.CLNDR_TYP,
1362 PDAY.DAY_CD,
1363 PWK.DAY_CD,
1364 PMNTH.DAY_CD,
1365 NULL,
1366 PQTR.DAY_CD,
1367 PYR.DAY_CD,
1368 NDAY.DAY_CD,
1369 NWK.DAY_CD,
1370 NMNTH.DAY_CD,
1371 NULL,
1372 NQTR.DAY_CD,
1373 NYR.DAY_CD,
1374 g_src_sys_idnt,
1375 g_src_sys_dt,
1376 g_crtd_by_DSR,
1377 g_last_updt_by_DSR,
1378 g_created_by,
1379 g_creation_date,
1380 g_last_updated_by,
1381 g_last_update_date,
1382 g_last_update_login
1383 FROM DDR_TIME_CLNDR_DAY_V BDAY,
1384 DDR_TIME_CLNDR_DAY_V PDAY,
1385 DDR_TIME_CLNDR_DAY_V PWK,
1386 DDR_TIME_CLNDR_DAY_V PMNTH,
1387 DDR_TIME_CLNDR_DAY_V PQTR,
1388 DDR_TIME_CLNDR_DAY_V PYR,
1389 DDR_TIME_CLNDR_DAY_V NDAY,
1390 DDR_TIME_CLNDR_DAY_V NWK,
1391 DDR_TIME_CLNDR_DAY_V NMNTH,
1392 DDR_TIME_CLNDR_DAY_V NQTR,
1393 DDR_TIME_CLNDR_DAY_V NYR
1394 where BDAY.YR_CD = TO_CHAR(v_start_year)
1395 AND PDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1396 AND PDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,-1)
1397 AND PWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1398 AND PWK.DAY_CD(+) = add_day(BDAY.DAY_CD,-7)
1399 AND PMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1400 AND PMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,-30)
1401 AND PQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1402 AND PQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,-91)
1403 and PYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1404 and PYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,-(V_YR_TIMESPN))
1405 AND NDAY.CLNDR_CD(+) = BDAY.CLNDR_CD
1406 AND NDAY.DAY_CD(+) = add_day(BDAY.DAY_CD,1)
1407 AND NWK.CLNDR_CD(+) = BDAY.CLNDR_CD
1408 AND NWK.DAY_CD(+) = add_day(BDAY.DAY_CD,7)
1409 AND NMNTH.CLNDR_CD(+) = BDAY.CLNDR_CD
1410 AND NMNTH.DAY_CD(+) = add_day(BDAY.DAY_CD,30)
1411 AND NQTR.CLNDR_CD(+) = BDAY.CLNDR_CD
1412 AND NQTR.DAY_CD(+) = add_day(BDAY.DAY_CD,91)
1413 and NYR.CLNDR_CD(+) = BDAY.CLNDR_CD
1414 and NYR.DAY_CD(+) = ADD_DAY(BDAY.DAY_CD,V_YR_TIMESPN) ;
1415
1416 END IF;
1417 V_START_YEAR:= V_START_YEAR+1;
1418 END LOOP;
1419
1420 /* Populate DDR_R_WK_TRANS table */
1421 DELETE
1422 FROM DDR_R_WK_TRANS
1423 WHERE (WK_CD,CLNDR_CD) IN
1424 (SELECT WK_CD,
1425 CLNDR_CD
1426 FROM DDR_TIME_CLNDR_WK_V
1427 WHERE SUBSTR(WK_CD,1,4) BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1428 );
1429 INSERT
1430 INTO DDR_R_WK_TRANS
1431 (
1432 MFG_ORG_CD,
1433 WK_TRANS_ID,
1434 WK_ID,
1435 WK_CD,
1436 CLNDR_CD,
1437 CLNDR_TYP,
1438 LAST_WK_THIS_YR_ID,
1439 LAST_WK_THIS_YR_CD,
1440 /* LAST_MNTH_THIS_WK_ID,
1441 LAST_MNTH_THIS_WK_CD,
1442 LAST_PRD_THIS_WK_ID,
1443 LAST_PRD_THIS_WK_CD,
1444 LAST_QTR_THIS_WK_ID,
1445 LAST_QTR_THIS_WK_CD,*/
1446 LAST_YR_THIS_WK_ID,
1447 LAST_YR_THIS_WK_CD,
1448 NXT_WK_THIS_YR_WK_ID,
1449 NXT_WK_THIS_YR_WK_CD,
1450 /* NXT_MNTH_THIS_WK_ID,
1451 NXT_MNTH_THIS_WK_CD,
1452 NXT_PRD_THIS_WK_ID,
1453 NXT_PRD_THIS_WK_CD,
1454 NXT_QTR_THIS_WK_ID,
1455 NXT_QTR_THIS_WK_CD, */
1456 NXT_YR_THIS_WK_ID,
1457 NXT_YR_THIS_WK_CD,
1458 SRC_SYS_IDNT,
1459 SRC_SYS_DT,
1460 CRTD_BY_DSR,
1461 LAST_UPDT_BY_DSR,
1462 CREATED_BY,
1463 CREATION_DATE,
1464 LAST_UPDATED_BY,
1465 LAST_UPDATE_DATE,
1466 LAST_UPDATE_LOGIN
1467 )
1468 SELECT WK.ORG_CD,
1469 DDR_R_WK_TRANS_SEQ.NEXTVAL,
1470 WK.CLNDR_WK_ID,
1471 WK.WK_CD,
1472 WK.CLNDR_CD,
1473 WK.CLNDR_TYP,
1474 PWK.CLNDR_WK_ID,
1475 PWK.WK_CD,
1476 /* PMNTH.CLNDR_WK_ID,
1477 PMNTH.WK_CD,
1478 null,
1479 null,
1480 PQTR.CLNDR_WK_ID,
1481 PQTR.WK_CD, */
1482 PYR.CLNDR_WK_ID,
1483 PYR.WK_CD,
1484 NWK.CLNDR_WK_ID,
1485 NWK.WK_CD,
1486 /* NMNTH.CLNDR_WK_ID,
1487 NMNTH.WK_CD,
1488 null,
1489 null,
1490 NQTR.CLNDR_WK_ID,
1491 NQTR.WK_CD, */
1492 NYR.CLNDR_WK_ID,
1493 NYR.WK_CD,
1494 g_src_sys_idnt,
1495 g_src_sys_dt,
1496 g_crtd_by_DSR,
1497 g_last_updt_by_DSR,
1498 g_created_by,
1502 g_last_update_login
1499 g_creation_date,
1500 g_last_updated_by,
1501 g_last_update_date,
1503 FROM DDR_TIME_CLNDR_WK_V WK,
1504 DDR_TIME_CLNDR_WK_V PWK,
1505 DDR_TIME_CLNDR_WK_V PMNTH,
1506 DDR_TIME_CLNDR_WK_V PQTR,
1507 DDR_TIME_CLNDR_WK_V PYR,
1508 DDR_TIME_CLNDR_WK_V NWK,
1509 DDR_TIME_CLNDR_WK_V NMNTH,
1510 DDR_TIME_CLNDR_WK_V NQTR,
1511 DDR_TIME_CLNDR_WK_V NYR
1512 WHERE PWK.CLNDR_CD(+) = WK.CLNDR_CD
1513 AND PWK.WK_CD(+) = add_week(WK.WK_CD,-1)
1514 AND PMNTH.CLNDR_CD(+) = WK.CLNDR_CD
1515 AND PMNTH.WK_CD(+) = add_week(WK.WK_CD,-4)
1516 AND PQTR.CLNDR_CD(+) = WK.CLNDR_CD
1517 AND PQTR.WK_CD(+) = add_week(WK.WK_CD,-13)
1518 AND PYR.CLNDR_CD(+) = WK.CLNDR_CD
1519 AND PYR.WK_CD(+) = add_week(WK.WK_CD,-52)
1520 AND NWK.CLNDR_CD(+) = WK.CLNDR_CD
1521 AND NWK.WK_CD(+) = add_week(WK.WK_CD,1)
1522 AND NMNTH.CLNDR_CD(+) = WK.CLNDR_CD
1523 AND NMNTH.WK_CD(+) = add_week(WK.WK_CD,4)
1524 AND NQTR.CLNDR_CD(+) = WK.CLNDR_CD
1525 AND NQTR.WK_CD(+) = add_week(WK.WK_CD,13)
1526 AND NYR.CLNDR_CD(+) = WK.CLNDR_CD
1527 AND NYR.WK_CD(+) = ADD_WEEK(WK.WK_CD,52)
1528 AND SUBSTR(WK.WK_CD,1,4) BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1529 ;
1530 /* Populate DDR_R_MNTH_TRANS table */
1531 DELETE
1532 FROM DDR_R_MNTH_TRANS
1533 WHERE (MNTH_CD,CLNDR_CD) IN
1534 (SELECT MNTH_CD,
1535 CLNDR_CD
1536 FROM DDR_TIME_CLNDR_MNTH_V
1537 WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1538 );
1539 INSERT
1540 INTO DDR_R_MNTH_TRANS
1541 (
1542 MFG_ORG_CD,
1543 MNTH_TRANS_ID,
1544 MNTH_ID,
1545 MNTH_CD,
1546 CLNDR_CD,
1547 CLNDR_TYP,
1548 LAST_MNTH_THIS_YR_ID,
1549 LAST_MNTH_THIS_YR_CD,
1550 LAST_QTR_THIS_MNTH_ID,
1551 LAST_QTR_THIS_MNTH_CD,
1552 LAST_YR_THIS_MNTH_ID,
1553 LAST_YR_THIS_MNTH_CD,
1554 NXT_MNTH_THIS_YR_ID,
1555 NXT_MNTH_THIS_YR_CD,
1556 NXT_QTR_THIS_MNTH_ID,
1557 NXT_QTR_THIS_MNTH_CD,
1558 NXT_YR_THIS_MNTH_ID,
1559 NXT_YR_THIS_MNTH_CD,
1560 SRC_SYS_IDNT,
1561 SRC_SYS_DT,
1562 CRTD_BY_DSR,
1563 LAST_UPDT_BY_DSR,
1564 CREATED_BY,
1565 CREATION_DATE,
1566 LAST_UPDATED_BY,
1567 LAST_UPDATE_DATE,
1568 LAST_UPDATE_LOGIN
1569 )
1570 SELECT MNTH.ORG_CD,
1571 DDR_R_MNTH_TRANS_SEQ.NEXTVAL,
1572 MNTH.CLNDR_MNTH_ID,
1573 MNTH.MNTH_CD,
1574 MNTH.CLNDR_CD,
1575 MNTH.CLNDR_TYP,
1576 PMNTH.CLNDR_MNTH_ID,
1577 PMNTH.MNTH_CD,
1578 PQTR.CLNDR_MNTH_ID,
1579 PQTR.MNTH_CD,
1580 PYR.CLNDR_MNTH_ID,
1581 PYR.MNTH_CD,
1582 NMNTH.CLNDR_MNTH_ID,
1583 NMNTH.MNTH_CD,
1584 NQTR.CLNDR_MNTH_ID,
1585 NQTR.MNTH_CD,
1586 NYR.CLNDR_MNTH_ID,
1587 NYR.MNTH_CD,
1588 g_src_sys_idnt,
1589 g_src_sys_dt,
1590 g_crtd_by_DSR,
1591 g_last_updt_by_DSR,
1592 g_created_by,
1593 g_creation_date,
1594 g_last_updated_by,
1595 g_last_update_date,
1596 g_last_update_login
1597 FROM DDR_TIME_CLNDR_MNTH_V MNTH,
1598 DDR_TIME_CLNDR_MNTH_V PMNTH,
1599 DDR_TIME_CLNDR_MNTH_V PQTR,
1600 DDR_TIME_CLNDR_MNTH_V PYR,
1601 DDR_TIME_CLNDR_MNTH_V NMNTH,
1602 DDR_TIME_CLNDR_MNTH_V NQTR,
1603 DDR_TIME_CLNDR_MNTH_V NYR
1604 WHERE MNTH.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1605 AND PMNTH.CLNDR_CD(+) = MNTH.CLNDR_CD
1606 AND PMNTH.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-1)
1607 AND PQTR.CLNDR_CD(+) = MNTH.CLNDR_CD
1608 AND PQTR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-3)
1609 AND PYR.CLNDR_CD(+) = MNTH.CLNDR_CD
1610 AND PYR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,-12)
1611 AND NMNTH.CLNDR_CD(+) = MNTH.CLNDR_CD
1612 AND NMNTH.MNTH_CD(+) = add_month(MNTH.MNTH_CD,1)
1613 AND NQTR.CLNDR_CD(+) = MNTH.CLNDR_CD
1614 AND NQTR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,3)
1615 AND NYR.CLNDR_CD(+) = MNTH.CLNDR_CD
1616 AND NYR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,12) ;
1617 /* Populate DDR_R_QTR_TRANS table */
1618 DELETE
1619 FROM DDR_R_QTR_TRANS
1620 WHERE (QTR_CD,CLNDR_CD) IN
1621 (SELECT QTR_CD,
1622 CLNDR_CD
1623 FROM DDR_TIME_CLNDR_QTR_V
1624 WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1625 );
1626 INSERT
1627 INTO DDR_R_QTR_TRANS
1628 (
1629 MFG_ORG_CD,
1630 QTR_TRANS_ID,
1631 QTR_ID,
1632 QTR_CD,
1633 CLNDR_CD,
1634 CLNDR_TYP,
1635 LAST_QTR_THIS_YR_ID,
1636 LAST_QTR_THIS_YR_CD,
1637 LAST_YR_THIS_QTR_ID,
1638 LAST_YR_THIS_QTR_CD,
1639 NXT_QTR_THIS_YR_ID,
1640 NXT_QTR_THIS_YR_CD,
1641 NXT_YR_THIS_QTR_ID,
1642 NXT_YR_THIS_QTR_CD,
1643 SRC_SYS_IDNT,
1644 SRC_SYS_DT,
1645 CRTD_BY_DSR,
1646 LAST_UPDT_BY_DSR,
1647 CREATED_BY,
1648 CREATION_DATE,
1649 LAST_UPDATED_BY,
1650 LAST_UPDATE_DATE,
1651 LAST_UPDATE_LOGIN
1652 )
1653 SELECT QTR.ORG_CD,
1654 DDR_R_QTR_TRANS_SEQ.NEXTVAL,
1655 QTR.CLNDR_QTR_ID,
1656 QTR.QTR_CD,
1657 QTR.CLNDR_CD,
1658 QTR.CLNDR_TYP,
1659 PQTR.CLNDR_QTR_ID,
1660 PQTR.QTR_CD,
1661 PYR.CLNDR_QTR_ID,
1662 PYR.QTR_CD,
1663 NQTR.CLNDR_QTR_ID,
1664 NQTR.QTR_CD,
1665 NYR.CLNDR_QTR_ID,
1666 NYR.QTR_CD,
1667 g_src_sys_idnt,
1668 g_src_sys_dt,
1669 g_crtd_by_DSR,
1670 g_last_updt_by_DSR,
1671 g_created_by,
1672 g_creation_date,
1673 g_last_updated_by,
1674 g_last_update_date,
1675 g_last_update_login
1676 FROM DDR_TIME_CLNDR_QTR_V QTR,
1677 DDR_TIME_CLNDR_QTR_V PQTR,
1678 DDR_TIME_CLNDR_QTR_V PYR,
1679 DDR_TIME_CLNDR_QTR_V NQTR,
1680 DDR_TIME_CLNDR_QTR_V NYR
1681 WHERE QTR.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1682 AND PQTR.ORG_CD(+) = QTR.ORG_CD
1683 AND PQTR.CLNDR_CD(+) = QTR.CLNDR_CD
1684 AND PQTR.QTR_CD(+) = add_quarter(QTR.QTR_CD,-1)
1685 AND PYR.ORG_CD(+) = QTR.ORG_CD
1686 AND PYR.CLNDR_CD(+) = QTR.CLNDR_CD
1687 AND PYR.QTR_CD(+) = add_quarter(QTR.QTR_CD,-4)
1688 AND NQTR.ORG_CD(+) = QTR.ORG_CD
1689 AND NQTR.CLNDR_CD(+) = QTR.CLNDR_CD
1690 AND NQTR.QTR_CD(+) = add_quarter(QTR.QTR_CD,1)
1691 AND NYR.ORG_CD(+) = QTR.ORG_CD
1692 AND NYR.CLNDR_CD(+) = QTR.CLNDR_CD
1693 AND NYR.QTR_CD(+) = add_quarter(QTR.QTR_CD,4) ;
1694 /* Populate DDR_R_YR_TRANS table */
1695 DELETE
1696 FROM DDR_R_YR_TRANS
1697 WHERE (YR_CD,CLNDR_CD) IN
1698 (SELECT YR_CD,
1699 CLNDR_CD
1700 FROM DDR_TIME_CLNDR_YR_V
1701 WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1702 );
1703 INSERT
1704 INTO DDR_R_YR_TRANS
1705 (
1706 MFG_ORG_CD,
1707 YR_TRANS_ID,
1708 YR_ID,
1709 YR_CD,
1710 CLNDR_CD,
1711 CLNDR_TYP,
1712 LAST_YR_ID,
1713 LAST_YR_CD,
1714 NXT_YR_ID,
1715 NXT_YR_CD,
1716 SRC_SYS_IDNT,
1717 SRC_SYS_DT,
1718 CRTD_BY_DSR,
1719 LAST_UPDT_BY_DSR,
1720 CREATED_BY,
1721 CREATION_DATE,
1722 LAST_UPDATED_BY,
1723 LAST_UPDATE_DATE,
1724 LAST_UPDATE_LOGIN
1725 )
1726 SELECT YR.ORG_CD,
1727 DDR_R_YR_TRANS_SEQ.NEXTVAL,
1728 YR.CLNDR_YR_ID,
1729 YR.YR_CD,
1730 YR.CLNDR_CD,
1731 YR.CLNDR_TYP,
1732 PYR.CLNDR_YR_ID,
1733 PYR.YR_CD,
1734 NYR.CLNDR_YR_ID,
1735 NYR.YR_CD,
1736 g_src_sys_idnt,
1737 g_src_sys_dt,
1738 g_crtd_by_DSR,
1739 g_last_updt_by_DSR,
1740 g_created_by,
1741 g_creation_date,
1742 g_last_updated_by,
1743 g_last_update_date,
1744 g_last_update_login
1745 FROM DDR_TIME_CLNDR_YR_V YR,
1746 DDR_TIME_CLNDR_YR_V PYR,
1747 DDR_TIME_CLNDR_YR_V NYR
1748 WHERE YR.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1749 AND PYR.CLNDR_CD(+) = YR.CLNDR_CD
1750 AND PYR.YR_CD(+) = YR.YR_CD - 1
1751 AND NYR.CLNDR_CD(+) = YR.CLNDR_CD
1752 AND NYR.YR_CD(+) = YR.YR_CD + 1 ;
1753 /*Starting data population for todate transformations*/
1754 /* Populate DDR_R_DAY_TODATE_TRANS table */
1755 DELETE
1756 FROM DDR_R_DAY_TODATE_TRANS
1757 WHERE (DAY_CD,CLNDR_CD) IN
1758 (SELECT DAY_CD,
1759 CLNDR_CD
1760 FROM DDR_TIME_CLNDR_DAY_V
1761 WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1762 );
1763 INSERT
1764 INTO DDR_R_DAY_TODATE_TRANS
1765 (
1766 MFG_ORG_CD,
1767 DAY_TODATE_TRANS_ID,
1768 DAY_CD,
1769 CLNDR_CD,
1770 CLNDR_TYP,
1771 YR_DAY_CD,
1772 SRC_SYS_IDNT,
1773 SRC_SYS_DT,
1774 CRTD_BY_DSR ,
1775 LAST_UPDT_BY_DSR ,
1776 CREATED_BY ,
1777 CREATION_DATE ,
1778 LAST_UPDATED_BY ,
1779 LAST_UPDATE_DATE ,
1780 LAST_UPDATE_LOGIN
1781 )
1782 SELECT YTD.ORG_CD,
1783 DDR_R_DAY_TODATE_TRANS_SEQ.NEXTVAL,
1784 YTD.DAY_CD,
1785 YTD.CLNDR_CD,
1786 YTD.CLNDR_TYP,
1787 YTD.YR_DAY_CD,
1788 g_src_sys_idnt,
1789 g_src_sys_dt,
1790 g_crtd_by_DSR,
1791 g_last_updt_by_DSR,
1792 g_created_by,
1793 g_creation_date,
1794 g_last_updated_by,
1795 g_last_update_date,
1796 g_last_update_login
1797 FROM
1798 (SELECT A.ORG_CD ORG_CD,
1799 A.DAY_CD DAY_CD,
1800 A.CLNDR_CD CLNDR_CD,
1801 A.CLNDR_TYP CLNDR_TYP,
1802 B.DAY_CD YR_DAY_CD
1803 FROM DDR_TIME_CLNDR_DAY_V A,
1804 DDR_TIME_CLNDR_DAY_V B
1805 WHERE A.YR_CD = B.YR_CD
1806 AND A.DAY_CD >= B.DAY_CD
1807 AND A.ORG_CD = B.ORG_CD
1808 AND A.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1809 ORDER BY A.DAY_CD, B.DAY_CD) YTD;
1810 /* Populate DDR_R_WK_TODATE_TRANS table */
1811 DELETE
1812 FROM DDR_R_WK_TODATE_TRANS
1813 WHERE (WK_CD,CLNDR_CD) IN
1814 (SELECT WK_CD,
1815 CLNDR_CD
1816 FROM DDR_TIME_CLNDR_WK_V
1820 INSERT
1817 WHERE SUBSTR(WK_CD,1,4) BETWEEN TO_CHAR(p_start_year) AND
1818 TO_CHAR(p_end_year)
1819 );
1821 INTO DDR_R_WK_TODATE_TRANS
1822 (
1823 MFG_ORG_CD,
1824 WK_TODATE_TRANS_ID,
1825 WK_ID,
1826 WK_CD,
1827 CLNDR_CD,
1828 CLNDR_TYP,
1829 YR_WK_ID,
1830 YR_WK_CD,
1831 SRC_SYS_IDNT,
1832 SRC_SYS_DT,
1833 CRTD_BY_DSR ,
1834 LAST_UPDT_BY_DSR ,
1835 CREATED_BY ,
1836 CREATION_DATE ,
1837 LAST_UPDATED_BY ,
1838 LAST_UPDATE_DATE ,
1839 LAST_UPDATE_LOGIN
1840 )
1841 SELECT YTD.ORG_CD,
1842 DDR_R_WK_TODATE_TRANS_SEQ.NEXTVAL,
1843 YTD.WK_ID,
1844 YTD.WK_CD,
1845 YTD.CLNDR_CD,
1846 YTD.CLNDR_TYP,
1847 YTD.YR_WK_ID,
1848 YTD.YR_WK_CD,
1849 g_src_sys_idnt,
1850 g_src_sys_dt,
1851 g_crtd_by_DSR,
1852 g_last_updt_by_DSR,
1853 g_created_by,
1854 g_creation_date,
1855 g_last_updated_by,
1856 g_last_update_date,
1857 g_last_update_login
1858 FROM
1859 (SELECT A.ORG_CD ORG_CD,
1860 A.WK_CD WK_CD,
1861 A.CLNDR_WK_ID WK_ID,
1862 A.CLNDR_CD CLNDR_CD,
1863 A.CLNDR_TYP CLNDR_TYP,
1864 B.WK_CD YR_WK_CD,
1865 B.CLNDR_WK_ID YR_WK_ID
1866 FROM DDR_TIME_CLNDR_WK_V A,
1867 DDR_TIME_CLNDR_WK_V B
1868 WHERE A.WK_CD >= B.WK_CD
1869 AND A.ORG_CD = B.ORG_CD
1870 AND SUBSTR(A.WK_CD,1,4) = SUBSTR(B.WK_CD,1,4)
1871 AND SUBSTR(A.WK_CD,1,4) BETWEEN TO_CHAR(P_START_YEAR) AND
1872 TO_CHAR(P_END_YEAR)
1873 ORDER BY A.WK_CD, B.WK_CD) YTD;
1874 /* Populate DDR_R_MNTH_TODATE_TRANS table */
1875 DELETE
1876 FROM DDR_R_MNTH_TODATE_TRANS
1877 WHERE (MNTH_CD,CLNDR_CD) IN
1878 (SELECT MNTH_CD,
1879 CLNDR_CD
1880 FROM DDR_TIME_CLNDR_MNTH_V
1881 WHERE YR_CD BETWEEN TO_CHAR(P_START_YEAR) AND TO_CHAR(P_END_YEAR)
1882 );
1883 INSERT
1884 INTO DDR_R_MNTH_TODATE_TRANS
1885 (
1886 MFG_ORG_CD,
1887 MNTH_TODATE_TRANS_ID,
1888 MNTH_ID,
1889 MNTH_CD,
1890 CLNDR_CD,
1891 CLNDR_TYP,
1892 YR_MNTH_ID,
1893 YR_MNTH_CD,
1894 SRC_SYS_IDNT,
1895 SRC_SYS_DT,
1896 CRTD_BY_DSR ,
1897 LAST_UPDT_BY_DSR ,
1898 CREATED_BY ,
1899 CREATION_DATE ,
1900 LAST_UPDATED_BY ,
1901 LAST_UPDATE_DATE ,
1902 LAST_UPDATE_LOGIN
1903 )
1904 SELECT YTD.ORG_CD,
1905 DDR_R_MNTH_TODATE_TRANS_SEQ.NEXTVAL,
1906 YTD.MNTH_ID,
1907 YTD.MNTH_CD,
1908 YTD.CLNDR_CD,
1909 YTD.CLNDR_TYP,
1910 YTD.YR_MNTH_ID,
1911 YTD.YR_MNTH_CD,
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 FROM
1922 (SELECT A.ORG_CD ORG_CD,
1923 A.MNTH_CD MNTH_CD,
1924 A.CLNDR_MNTH_ID MNTH_ID,
1925 A.CLNDR_CD CLNDR_CD,
1926 A.CLNDR_TYP CLNDR_TYP,
1927 B.MNTH_CD YR_MNTH_CD,
1928 B.CLNDR_MNTH_ID YR_MNTH_ID
1929 FROM DDR_TIME_CLNDR_MNTH_V A,
1930 DDR_TIME_CLNDR_MNTH_V B
1931 WHERE A.YR_CD = B.YR_CD
1932 AND A.MNTH_CD >= B.MNTH_CD
1933 AND A.ORG_CD = B.ORG_CD
1934 AND A.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1935 ORDER BY A.MNTH_CD, B.MNTH_CD) YTD;
1936 /* Populate DDR_R_QTR_TODATE_TRANS table */
1937 DELETE
1938 FROM DDR_R_QTR_TODATE_TRANS
1939 WHERE (QTR_CD,CLNDR_CD) IN
1940 (SELECT QTR_CD,
1941 CLNDR_CD
1942 FROM DDR_TIME_CLNDR_QTR_V
1943 WHERE YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1944 );
1945 INSERT
1946 INTO DDR_R_QTR_TODATE_TRANS
1947 (
1948 MFG_ORG_CD,
1949 QTR_TODATE_TRANS_ID,
1950 QTR_ID,
1951 QTR_CD,
1952 CLNDR_CD,
1953 CLNDR_TYP,
1954 YR_QTR_ID,
1955 YR_QTR_CD,
1956 SRC_SYS_IDNT,
1957 SRC_SYS_DT,
1958 CRTD_BY_DSR ,
1959 LAST_UPDT_BY_DSR ,
1960 CREATED_BY ,
1961 CREATION_DATE ,
1962 LAST_UPDATED_BY ,
1963 LAST_UPDATE_DATE ,
1964 LAST_UPDATE_LOGIN
1965 )
1966 SELECT YTD.ORG_CD,
1967 DDR_R_QTR_TODATE_TRANS_SEQ.NEXTVAL,
1968 YTD.QTR_ID,
1969 YTD.QTR_CD,
1970 YTD.CLNDR_CD,
1971 YTD.CLNDR_TYP,
1972 YTD.YR_QTR_ID,
1973 YTD.YR_QTR_CD,
1974 g_src_sys_idnt,
1975 g_src_sys_dt,
1976 g_crtd_by_DSR,
1977 g_last_updt_by_DSR,
1978 g_created_by,
1979 g_creation_date,
1980 g_last_updated_by,
1981 g_last_update_date,
1982 g_last_update_login
1983 FROM
1984 (SELECT A.ORG_CD ORG_CD,
1985 A.QTR_CD QTR_CD,
1986 A.CLNDR_QTR_ID QTR_ID,
1987 A.CLNDR_CD CLNDR_CD,
1988 A.CLNDR_CD CLNDR_TYP,
1989 B.QTR_CD YR_QTR_CD,
1990 B.CLNDR_QTR_ID YR_QTR_ID
1991 FROM DDR_TIME_CLNDR_QTR_V A,
1992 DDR_TIME_CLNDR_QTR_V B
1993 WHERE A.YR_CD = B.YR_CD
1994 AND A.QTR_CD >= B.QTR_CD
1995 AND A.ORG_CD = B.ORG_CD
1996 AND A.YR_CD BETWEEN TO_CHAR(p_start_year) AND TO_CHAR(p_end_year)
1997 ORDER BY A.QTR_CD, B.QTR_CD) YTD;
1998 COMMIT;
1999 END Populate_CLNDR_Transformation;
2000
2001 END ddr_time_transform_pkg;