[Home] [Help]
PACKAGE BODY: APPS.DDR_TIME_TRANSFORM_PKG
Source
1 PACKAGE BODY ddr_time_transform_pkg AS
2 /* $Header: ddrttfmb.pls 120.2 2008/03/24 11:06:40 sdaga noship $ */
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
132 insert into DDR_R_DAY_TRANS (
133 MFG_ORG_CD,
134 DAY_TRANS_ID,
135 DAY_CD,
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,
290 null,
291 null,
292 PQTR.WK_ID,
293 PQTR.WK_CD,
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)
348 and NYR.ORG_CD(+) = WK.ORG_CD
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)
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
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
446 and NYR.MNTH_CD(+) = add_month(MNTH.MNTH_CD,12)
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,
509 DDR_TIME_BSNS_QTR_V PQTR,
506 g_last_update_login
507 from
508 DDR_TIME_BSNS_QTR_V QTR,
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,
607 CLNDR_CD,
608 CLNDR_TYP,
609 YR_DAY_CD,
610 SRC_SYS_IDNT,
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 )
683 YTD.WK_ID,
680 select
681 YTD.MFG_ORG_CD,
682 DDR_R_WK_TODATE_TRANS_SEQ.NEXTVAL,
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,
798 SRC_SYS_IDNT,
799 SRC_SYS_DT,
800 CRTD_BY_DSR ,
801 LAST_UPDT_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 END ddr_time_transform_pkg;