[Home] [Help]
PACKAGE BODY: APPS.PA_ADW_COLLECT_DIMENSIONS
Source
1 PACKAGE BODY PA_ADW_COLLECT_DIMENSIONS AS
2 /* $Header: PAADWCDB.pls 115.2 99/07/16 13:21:36 porting shi $ */
3
4 FUNCTION Initialize RETURN NUMBER IS
5 BEGIN
6 NULL;
7 END Initialize;
8
9 -- Procedure to get dimensions statuses
10
11 PROCEDURE get_dim_status
12 ( x_dimension_code IN VARCHAR2,
13 x_dimension_status IN OUT VARCHAR2,
14 x_err_stage IN OUT VARCHAR2,
15 x_err_stack IN OUT VARCHAR2,
16 x_err_code IN OUT NUMBER)
17 IS
18 x_old_err_stack VARCHAR2(1024);
19 BEGIN
20 x_err_code := 0;
21 x_err_stage := 'Collecting Dimension Status for Dimension Code ' || x_dimension_code;
22 x_old_err_stack := x_err_stack;
23 x_err_stack := x_err_stack || '-> get_dim_status';
24
25 pa_debug.debug(x_err_stage);
26
27 SELECT
28 status_code
29 INTO
30 x_dimension_status
31 FROM
32 pa_adw_dimension_status
33 WHERE
34 dimension_code = x_dimension_code;
35
36 x_err_stack := x_old_err_stack;
37 pa_debug.debug('Completed ' || x_err_stage);
38
39 EXCEPTION
40 WHEN OTHERS THEN
41 x_err_code := SQLCODE;
42 RAISE;
43 END get_dim_status;
44
45 -- Procedure to collect lowest/top level tasks dimension
46 -- We have one procedure for lowest level tasks and top level tasks
47 -- because a task may be top level tasks as well as lowest level task
48
49
50 PROCEDURE get_dim_tasks
51 ( x_err_stage IN OUT VARCHAR2,
52 x_err_stack IN OUT VARCHAR2,
53 x_err_code IN OUT NUMBER)
54 IS
55
56 -- Define Cursor for selecting lowest level tasks
57
58 CURSOR sel_lowest_tasks IS
59 SELECT
60 TASK_ID,
61 TOP_TASK_ID,
62 TASK_NUMBER,
63 TASK_NAME,
64 DESCRIPTION,
65 CARRYING_OUT_ORGANIZATION_ID,
66 SERVICE_TYPE_CODE,
67 USER_COL1,
68 USER_COL2,
69 USER_COL3,
70 USER_COL4,
71 USER_COL5,
72 USER_COL6,
73 USER_COL7,
74 USER_COL8,
75 USER_COL9,
76 USER_COL10,
77 ADW_NOTIFY_FLAG
78 FROM
79 PA_ADW_LOWEST_TASKS_V
80 WHERE
81 ADW_NOTIFY_FLAG = 'Y';
82
83 -- Define Cursor for selecting Top level tasks
84
85 CURSOR sel_top_tasks IS
86 SELECT
87 TOP_TASK_ID,
88 PROJECT_ID,
89 TASK_NUMBER,
90 TASK_NAME,
91 DESCRIPTION,
92 CARRYING_OUT_ORGANIZATION_ID,
93 SERVICE_TYPE_CODE,
94 USER_COL1,
95 USER_COL2,
96 USER_COL3,
97 USER_COL4,
98 USER_COL5,
99 USER_COL6,
100 USER_COL7,
101 USER_COL8,
102 USER_COL9,
103 USER_COL10,
104 ADW_NOTIFY_FLAG
105 FROM
106 PA_ADW_TOP_TASKS_V
107 WHERE
108 ADW_NOTIFY_FLAG IN ('Y','S');
109
110 -- define procedure variables
111
112 lowest_tasks_r sel_lowest_tasks%ROWTYPE;
113 top_tasks_r sel_top_tasks%ROWTYPE;
114 x_old_err_stack VARCHAR2(1024);
115
116 BEGIN
117 x_err_code := 0;
118 x_err_stage := 'Collecting Lowest Level Task Dimension Table';
119 x_old_err_stack := x_err_stack;
120 x_err_stack := x_err_stack || '-> get_dim_tasks';
121
122 pa_debug.debug(x_err_stage);
123
124 -- Check the profile option value for collecting lowest tasks
125 IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y') THEN
126
127 -- Process all lowest level tasks first
128
129 FOR lowest_tasks_r IN sel_lowest_tasks LOOP
130
131 -- First Try to Update the Row in the Interface Table
132 UPDATE
133 PA_LOWEST_TASKS_IT
134 SET
135 TOP_TASK_ID = LOWEST_TASKS_R.TOP_TASK_ID,
136 TASK_NUMBER = LOWEST_TASKS_R.TASK_NUMBER,
137 TASK_NAME = LOWEST_TASKS_R.TASK_NAME,
138 CARRYING_OUT_ORGANIZATION_ID = LOWEST_TASKS_R.CARRYING_OUT_ORGANIZATION_ID,
139 LAST_UPDATE_DATE = TRUNC(SYSDATE),
140 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
141 CREATION_DATE = TRUNC(SYSDATE),
142 CREATED_BY = X_CREATED_BY,
143 SERVICE_TYPE_CODE = LOWEST_TASKS_R.SERVICE_TYPE_CODE,
144 DESCRIPTION = LOWEST_TASKS_R.DESCRIPTION,
145 USER_COL1 = LOWEST_TASKS_R.USER_COL1,
146 USER_COL2 = LOWEST_TASKS_R.USER_COL2,
147 USER_COL3 = LOWEST_TASKS_R.USER_COL3,
148 USER_COL4 = LOWEST_TASKS_R.USER_COL4,
149 USER_COL5 = LOWEST_TASKS_R.USER_COL5,
150 USER_COL6 = LOWEST_TASKS_R.USER_COL6,
151 USER_COL7 = LOWEST_TASKS_R.USER_COL7,
152 USER_COL8 = LOWEST_TASKS_R.USER_COL8,
153 USER_COL9 = LOWEST_TASKS_R.USER_COL9,
154 USER_COL10 = LOWEST_TASKS_R.USER_COL10,
155 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
156 REQUEST_ID = X_REQUEST_ID,
157 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
158 PROGRAM_ID = X_PROGRAM_ID,
159 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
160 STATUS_CODE = 'P'
161 WHERE
162 TASK_ID = LOWEST_TASKS_R.TASK_ID;
163
164 -- Check If Any row was updated
165
166 IF (SQL%ROWCOUNT = 0) THEN
167 -- No row was updated, So Insert a new row into the interface table
168 INSERT INTO PA_LOWEST_TASKS_IT
169 (
170 TASK_ID,
171 TOP_TASK_ID,
172 TASK_NUMBER,
173 TASK_NAME,
174 CARRYING_OUT_ORGANIZATION_ID,
175 LAST_UPDATE_DATE,
176 LAST_UPDATED_BY,
177 CREATION_DATE,
178 CREATED_BY,
179 SERVICE_TYPE_CODE,
180 DESCRIPTION,
181 USER_COL1,
182 USER_COL2,
183 USER_COL3,
184 USER_COL4,
185 USER_COL5,
186 USER_COL6,
187 USER_COL7,
188 USER_COL8,
189 USER_COL9,
190 USER_COL10,
191 LAST_UPDATE_LOGIN,
192 REQUEST_ID,
193 PROGRAM_APPLICATION_ID,
194 PROGRAM_ID,
195 PROGRAM_UPDATE_DATE,
196 STATUS_CODE
197 )
198 VALUES
199 (
200 LOWEST_TASKS_R.TASK_ID,
201 LOWEST_TASKS_R.TOP_TASK_ID,
202 LOWEST_TASKS_R.TASK_NUMBER,
203 LOWEST_TASKS_R.TASK_NAME,
204 LOWEST_TASKS_R.CARRYING_OUT_ORGANIZATION_ID,
205 TRUNC(SYSDATE),
206 X_LAST_UPDATED_BY,
207 TRUNC(SYSDATE),
208 X_CREATED_BY,
209 LOWEST_TASKS_R.SERVICE_TYPE_CODE,
210 LOWEST_TASKS_R.DESCRIPTION,
211 LOWEST_TASKS_R.USER_COL1,
212 LOWEST_TASKS_R.USER_COL2,
213 LOWEST_TASKS_R.USER_COL3,
214 LOWEST_TASKS_R.USER_COL4,
215 LOWEST_TASKS_R.USER_COL5,
216 LOWEST_TASKS_R.USER_COL6,
217 LOWEST_TASKS_R.USER_COL7,
218 LOWEST_TASKS_R.USER_COL8,
219 LOWEST_TASKS_R.USER_COL9,
220 LOWEST_TASKS_R.USER_COL10,
221 X_LAST_UPDATE_LOGIN,
222 X_REQUEST_ID,
223 X_PROGRAM_APPLICATION_ID,
224 X_PROGRAM_ID,
225 TRUNC(SYSDATE),
226 'P'
227 );
228
229 END IF; -- IF ( SQL%ROWCOUNT = 0 )
230
231 -- Mark the Task as being transferred to the Interface table
232 -- We are marking these tasks as 'S', since some of these tasks
233 -- may be top level tasks too
234
235 UPDATE
236 PA_TASKS
237 SET
238 ADW_NOTIFY_FLAG = 'S'
239 WHERE
240 TASK_ID = LOWEST_TASKS_R.TASK_ID;
241
242 END LOOP; -- FOR lowest_tasks_r IN sel_lowest_tasks
243
244 END IF; -- IF ( pa_adw_collect_main.collect_lowest_tasks_flag = 'Y')
245
246 x_err_stage := 'Collecting Top Level Task Dimension Table';
247
248 pa_debug.debug(x_err_stage);
249
250 -- Check the profile option value for collecting top tasks
251 IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y') THEN
252
253 -- Now process the top level tasks
254
255 FOR top_tasks_r IN sel_top_tasks LOOP
256
257 -- First Try to Update the Row in the Interface Table
258 UPDATE
259 PA_TOP_TASKS_IT
260 SET
261 PROJECT_ID = TOP_TASKS_R.PROJECT_ID,
262 TASK_NUMBER = TOP_TASKS_R.TASK_NUMBER,
263 TASK_NAME = TOP_TASKS_R.TASK_NAME,
264 CARRYING_OUT_ORGANIZATION_ID = TOP_TASKS_R.CARRYING_OUT_ORGANIZATION_ID,
265 LAST_UPDATE_DATE = TRUNC(SYSDATE),
266 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
267 CREATION_DATE = TRUNC(SYSDATE),
268 CREATED_BY = X_CREATED_BY,
269 SERVICE_TYPE_CODE = TOP_TASKS_R.SERVICE_TYPE_CODE,
270 DESCRIPTION = TOP_TASKS_R.DESCRIPTION,
271 USER_COL1 = TOP_TASKS_R.USER_COL1,
272 USER_COL2 = TOP_TASKS_R.USER_COL2,
273 USER_COL3 = TOP_TASKS_R.USER_COL3,
274 USER_COL4 = TOP_TASKS_R.USER_COL4,
275 USER_COL5 = TOP_TASKS_R.USER_COL5,
276 USER_COL6 = TOP_TASKS_R.USER_COL6,
277 USER_COL7 = TOP_TASKS_R.USER_COL7,
278 USER_COL8 = TOP_TASKS_R.USER_COL8,
279 USER_COL9 = TOP_TASKS_R.USER_COL9,
280 USER_COL10 = TOP_TASKS_R.USER_COL10,
281 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
282 REQUEST_ID = X_REQUEST_ID,
283 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
284 PROGRAM_ID = X_PROGRAM_ID,
285 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
286 STATUS_CODE = 'P'
287 WHERE
288 TOP_TASK_ID = TOP_TASKS_R.TOP_TASK_ID;
289
290 -- Check If Any row was updated
291
292 IF (SQL%ROWCOUNT = 0) THEN
293 -- No row was updated, So Insert a new row into the interface table
294 INSERT INTO PA_TOP_TASKS_IT
295 (
296 TOP_TASK_ID,
297 PROJECT_ID,
298 TASK_NUMBER,
299 TASK_NAME,
300 CARRYING_OUT_ORGANIZATION_ID,
301 LAST_UPDATE_DATE,
302 LAST_UPDATED_BY,
303 CREATION_DATE,
304 CREATED_BY,
305 SERVICE_TYPE_CODE,
306 DESCRIPTION,
307 USER_COL1,
308 USER_COL2,
309 USER_COL3,
310 USER_COL4,
311 USER_COL5,
312 USER_COL6,
313 USER_COL7,
314 USER_COL8,
315 USER_COL9,
316 USER_COL10,
317 LAST_UPDATE_LOGIN,
318 REQUEST_ID,
319 PROGRAM_APPLICATION_ID,
320 PROGRAM_ID,
321 PROGRAM_UPDATE_DATE,
322 STATUS_CODE
323 )
324 VALUES
325 (
326 TOP_TASKS_R.TOP_TASK_ID,
327 TOP_TASKS_R.PROJECT_ID,
328 TOP_TASKS_R.TASK_NUMBER,
329 TOP_TASKS_R.TASK_NAME,
330 TOP_TASKS_R.CARRYING_OUT_ORGANIZATION_ID,
331 TRUNC(SYSDATE),
332 X_LAST_UPDATED_BY,
333 TRUNC(SYSDATE),
334 X_CREATED_BY,
335 TOP_TASKS_R.SERVICE_TYPE_CODE,
336 TOP_TASKS_R.DESCRIPTION,
337 TOP_TASKS_R.USER_COL1,
338 TOP_TASKS_R.USER_COL2,
339 TOP_TASKS_R.USER_COL3,
340 TOP_TASKS_R.USER_COL4,
341 TOP_TASKS_R.USER_COL5,
342 TOP_TASKS_R.USER_COL6,
343 TOP_TASKS_R.USER_COL7,
344 TOP_TASKS_R.USER_COL8,
345 TOP_TASKS_R.USER_COL9,
346 TOP_TASKS_R.USER_COL10,
347 X_LAST_UPDATE_LOGIN,
348 X_REQUEST_ID,
349 X_PROGRAM_APPLICATION_ID,
350 X_PROGRAM_ID,
351 TRUNC(SYSDATE),
352 'P'
353 );
354
355 END IF; -- IF ( SQL%ROWCOUNT = 0 )
356
357 -- Mark the Task as transferred to Interface table
358
359 UPDATE
360 PA_TASKS
361 SET
362 ADW_NOTIFY_FLAG = 'N'
363 WHERE
364 TOP_TASK_ID = TOP_TASKS_R.TOP_TASK_ID;
365
366 END LOOP; -- FOR top_tasks_r IN sel_top_tasks
367
368 END IF; -- IF ( pa_adw_collect_main.collect_top_tasks_flag = 'Y')
369
370 -- Now update all remaining Low Level tasks as transferred to the
371 -- interface table
372
373 UPDATE
374 PA_TASKS
375 SET
376 ADW_NOTIFY_FLAG = 'N'
377 WHERE
378 ADW_NOTIFY_FLAG = 'S';
379
380 x_err_stack := x_old_err_stack;
381
382 pa_debug.debug('Completed ' || x_err_stage);
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 x_err_code := SQLCODE;
387 RAISE;
388 END get_dim_tasks;
389
390 -- Procedure to collect projects dimension
391
392 PROCEDURE get_dim_projects
393 ( x_err_stage IN OUT VARCHAR2,
394 x_err_stack IN OUT VARCHAR2,
395 x_err_code IN OUT NUMBER)
396 IS
397
398 -- Define Cursor for selecting projects
399
400 CURSOR sel_projects IS
401 SELECT
402 PROJECT_ID,
403 PROJECT_TYPE,
404 NAME,
405 SEGMENT1,
406 CARRYING_OUT_ORGANIZATION_ID,
407 DESCRIPTION,
408 USER_COL1,
409 USER_COL2,
410 USER_COL3,
411 USER_COL4,
412 USER_COL5,
413 USER_COL6,
414 USER_COL7,
415 USER_COL8,
416 USER_COL9,
417 USER_COL10,
418 ADW_NOTIFY_FLAG
419 FROM
420 PA_ADW_PROJECTS_V
421 WHERE
422 ADW_NOTIFY_FLAG = 'Y';
423
424 -- define procedure variables
425
426 projects_r sel_projects%ROWTYPE;
427
428 x_old_err_stack VARCHAR2(1024);
429
430 BEGIN
431 x_err_code := 0;
432 x_err_stage := 'Collecting Projects Dimension Table';
433 x_old_err_stack := x_err_stack;
434 x_err_stack := x_err_stack || '-> get_dim_projects';
435
436 pa_debug.debug(x_err_stage);
437
438 -- Process all projects
439
440 FOR projects_r IN sel_projects LOOP
441
442 -- First Try to Update the Row in the Interface Table
443
444 UPDATE
445 PA_PROJECTS_IT
446 SET
447 PROJECT_TYPE = PROJECTS_R.PROJECT_TYPE,
448 SEGMENT1 = PROJECTS_R.SEGMENT1,
449 NAME = PROJECTS_R.NAME,
450 CARRYING_OUT_ORGANIZATION_ID = PROJECTS_R.CARRYING_OUT_ORGANIZATION_ID,
451 LAST_UPDATE_DATE = TRUNC(SYSDATE),
452 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
453 CREATION_DATE = TRUNC(SYSDATE),
454 CREATED_BY = X_CREATED_BY,
455 DESCRIPTION = PROJECTS_R.DESCRIPTION,
456 USER_COL1 = PROJECTS_R.USER_COL1,
457 USER_COL2 = PROJECTS_R.USER_COL2,
458 USER_COL3 = PROJECTS_R.USER_COL3,
459 USER_COL4 = PROJECTS_R.USER_COL4,
460 USER_COL5 = PROJECTS_R.USER_COL5,
461 USER_COL6 = PROJECTS_R.USER_COL6,
462 USER_COL7 = PROJECTS_R.USER_COL7,
463 USER_COL8 = PROJECTS_R.USER_COL8,
464 USER_COL9 = PROJECTS_R.USER_COL9,
465 USER_COL10 = PROJECTS_R.USER_COL10,
466 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
467 REQUEST_ID = X_REQUEST_ID,
468 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
469 PROGRAM_ID = X_PROGRAM_ID,
470 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
471 STATUS_CODE = 'P'
472 WHERE
473 PROJECT_ID = PROJECTS_R.PROJECT_ID;
474
475 -- Check If Any row was updated
476
477 IF (SQL%ROWCOUNT = 0) THEN
478 -- No row was updated, So Insert a new row into the interface table
479 INSERT INTO PA_PROJECTS_IT
480 (
481 PROJECT_ID,
482 PROJECT_TYPE,
483 SEGMENT1,
484 NAME,
485 CARRYING_OUT_ORGANIZATION_ID,
486 LAST_UPDATE_DATE,
487 LAST_UPDATED_BY,
488 CREATION_DATE,
489 CREATED_BY,
490 DESCRIPTION,
491 USER_COL1,
492 USER_COL2,
493 USER_COL3,
494 USER_COL4,
495 USER_COL5,
496 USER_COL6,
497 USER_COL7,
498 USER_COL8,
499 USER_COL9,
500 USER_COL10,
501 LAST_UPDATE_LOGIN,
502 REQUEST_ID,
503 PROGRAM_APPLICATION_ID,
504 PROGRAM_ID,
505 PROGRAM_UPDATE_DATE,
506 STATUS_CODE
507 )
508 VALUES
509 (
510 PROJECTS_R.PROJECT_ID,
511 PROJECTS_R.PROJECT_TYPE,
512 PROJECTS_R.SEGMENT1,
513 PROJECTS_R.NAME,
514 PROJECTS_R.CARRYING_OUT_ORGANIZATION_ID,
515 TRUNC(SYSDATE),
516 X_LAST_UPDATED_BY,
517 TRUNC(SYSDATE),
518 X_CREATED_BY,
519 PROJECTS_R.DESCRIPTION,
520 PROJECTS_R.USER_COL1,
521 PROJECTS_R.USER_COL2,
522 PROJECTS_R.USER_COL3,
523 PROJECTS_R.USER_COL4,
524 PROJECTS_R.USER_COL5,
525 PROJECTS_R.USER_COL6,
526 PROJECTS_R.USER_COL7,
527 PROJECTS_R.USER_COL8,
528 PROJECTS_R.USER_COL9,
529 PROJECTS_R.USER_COL10,
530 X_LAST_UPDATE_LOGIN,
531 X_REQUEST_ID,
532 X_PROGRAM_APPLICATION_ID,
533 X_PROGRAM_ID,
534 TRUNC(SYSDATE),
535 'P'
536 );
537
538 END IF; -- IF ( SQL%ROWCOUNT = 0 )
539
540 -- Mark the Projects as transferred to Interface table
541
542 UPDATE
543 PA_PROJECTS
544 SET
545 ADW_NOTIFY_FLAG = 'N'
546 WHERE
547 PROJECT_ID = PROJECTS_R.PROJECT_ID;
548
549 END LOOP; -- FOR projects_r IN sel_projects
550
551 x_err_stack := x_old_err_stack;
552
553 pa_debug.debug('Completed ' || x_err_stage);
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 x_err_code := SQLCODE;
558 RAISE;
559 END get_dim_projects;
560
561 -- Procedure to collect project types dimension
562
563 PROCEDURE get_dim_project_types
564 ( x_err_stage IN OUT VARCHAR2,
565 x_err_stack IN OUT VARCHAR2,
566 x_err_code IN OUT NUMBER)
567 IS
568
569 -- Define Cursor for selecting project types
570
571 CURSOR sel_project_types IS
572 SELECT
573 PROJECT_TYPE,
574 DESCRIPTION,
575 ALL_PROJECT_TYPES,
576 USER_COL1,
577 USER_COL2,
578 USER_COL3,
579 USER_COL4,
580 USER_COL5,
581 USER_COL6,
582 USER_COL7,
583 USER_COL8,
584 USER_COL9,
585 USER_COL10,
586 ADW_NOTIFY_FLAG
587 FROM
588 PA_ADW_PRJ_TYPES_V
589 WHERE
590 ADW_NOTIFY_FLAG = 'Y';
591
592 -- define procedure variables
593
594 project_types_r sel_project_types%ROWTYPE;
595
596 x_old_err_stack VARCHAR2(1024);
597 x_count number;
598
599 BEGIN
600 x_err_code := 0;
601 x_err_stage := 'Collecting Project Types Dimension Table';
602 x_old_err_stack := x_err_stack;
603 x_err_stack := x_err_stack || '-> get_dim_project_types';
604
605 pa_debug.debug(x_err_stage);
606
607 -- Process all project types
608
609 FOR project_types_r IN sel_project_types LOOP
610
611 -- First Try to Update the Row in the Interface Table
612
613 UPDATE
614 PA_PRJ_TYPES_IT
615 SET
616 ALL_PROJECT_TYPES = PROJECT_TYPES_R.ALL_PROJECT_TYPES,
617 LAST_UPDATE_DATE = TRUNC(SYSDATE),
618 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
619 CREATION_DATE = TRUNC(SYSDATE),
620 CREATED_BY = X_CREATED_BY,
621 DESCRIPTION = PROJECT_TYPES_R.DESCRIPTION,
622 USER_COL1 = PROJECT_TYPES_R.USER_COL1,
623 USER_COL2 = PROJECT_TYPES_R.USER_COL2,
624 USER_COL3 = PROJECT_TYPES_R.USER_COL3,
625 USER_COL4 = PROJECT_TYPES_R.USER_COL4,
626 USER_COL5 = PROJECT_TYPES_R.USER_COL5,
627 USER_COL6 = PROJECT_TYPES_R.USER_COL6,
628 USER_COL7 = PROJECT_TYPES_R.USER_COL7,
629 USER_COL8 = PROJECT_TYPES_R.USER_COL8,
630 USER_COL9 = PROJECT_TYPES_R.USER_COL9,
631 USER_COL10 = PROJECT_TYPES_R.USER_COL10,
632 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
633 REQUEST_ID = X_REQUEST_ID,
634 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
635 PROGRAM_ID = X_PROGRAM_ID,
636 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
637 STATUS_CODE = 'P'
638 WHERE
639 PROJECT_TYPE = PROJECT_TYPES_R.PROJECT_TYPE;
640
641 -- Check If Any row was updated
642
643 IF (SQL%ROWCOUNT = 0) THEN
644 -- No row was updated, So Insert a new row into the interface table
645 INSERT INTO PA_PRJ_TYPES_IT
646 (
647 PROJECT_TYPE,
648 LAST_UPDATE_DATE,
649 LAST_UPDATED_BY,
650 CREATION_DATE,
651 CREATED_BY,
652 DESCRIPTION,
653 ALL_PROJECT_TYPES,
654 USER_COL1,
655 USER_COL2,
656 USER_COL3,
657 USER_COL4,
658 USER_COL5,
659 USER_COL6,
660 USER_COL7,
661 USER_COL8,
662 USER_COL9,
663 USER_COL10,
664 LAST_UPDATE_LOGIN,
665 REQUEST_ID,
666 PROGRAM_APPLICATION_ID,
667 PROGRAM_ID,
668 PROGRAM_UPDATE_DATE,
669 STATUS_CODE
670 )
671 VALUES
672 (
673 PROJECT_TYPES_R.PROJECT_TYPE,
674 TRUNC(SYSDATE),
675 X_LAST_UPDATED_BY,
676 TRUNC(SYSDATE),
677 X_CREATED_BY,
678 PROJECT_TYPES_R.DESCRIPTION,
679 PROJECT_TYPES_R.ALL_PROJECT_TYPES,
680 PROJECT_TYPES_R.USER_COL1,
681 PROJECT_TYPES_R.USER_COL2,
682 PROJECT_TYPES_R.USER_COL3,
683 PROJECT_TYPES_R.USER_COL4,
684 PROJECT_TYPES_R.USER_COL5,
685 PROJECT_TYPES_R.USER_COL6,
686 PROJECT_TYPES_R.USER_COL7,
687 PROJECT_TYPES_R.USER_COL8,
688 PROJECT_TYPES_R.USER_COL9,
689 PROJECT_TYPES_R.USER_COL10,
690 X_LAST_UPDATE_LOGIN,
691 X_REQUEST_ID,
692 X_PROGRAM_APPLICATION_ID,
693 X_PROGRAM_ID,
694 TRUNC(SYSDATE),
695 'P'
696 );
697
698 END IF; -- IF ( SQL%ROWCOUNT = 0 )
699
700 -- Mark the project types as transferred to Interface table
701
702 UPDATE
703 PA_PROJECT_TYPES
704 SET
705 ADW_NOTIFY_FLAG = 'N'
706 WHERE
707 PROJECT_TYPE = PROJECT_TYPES_R.PROJECT_TYPE;
708
709 END LOOP; -- FOR project_types_r IN sel_project_types
710
711 SELECT COUNT(*)
712 INTO x_count
713 FROM PA_ALL_PRJ_TYPES_IT;
714
715 IF x_count = 0
716 THEN
717 INSERT INTO PA_ALL_PRJ_TYPES_IT
718 (
719 ALL_PROJECT_TYPES,
720 LAST_UPDATE_DATE,
721 LAST_UPDATED_BY,
722 CREATION_DATE,
723 CREATED_BY,
724 LAST_UPDATE_LOGIN,
725 REQUEST_ID,
726 PROGRAM_APPLICATION_ID,
727 PROGRAM_ID,
728 PROGRAM_UPDATE_DATE,
729 STATUS_CODE
730 )
731 SELECT DISTINCT
732 ALL_PROJECT_TYPES,
733 TRUNC(SYSDATE),
734 X_LAST_UPDATED_BY,
735 TRUNC(SYSDATE),
736 X_CREATED_BY,
737 X_LAST_UPDATE_LOGIN,
738 X_REQUEST_ID,
739 X_PROGRAM_APPLICATION_ID,
740 X_PROGRAM_ID,
741 TRUNC(SYSDATE),
742 'P'
743 FROM PA_ADW_PRJ_TYPES_V;
744
745 END IF;
746
747 x_err_stack := x_old_err_stack;
748
749 pa_debug.debug('Completed ' || x_err_stage);
750
751 EXCEPTION
752 WHEN OTHERS THEN
753 x_err_code := SQLCODE;
754 RAISE;
755 END get_dim_project_types;
756
757 -- Procedure to collect expenditure types dimension
758
759 PROCEDURE get_dim_expenditure_types
760 ( x_err_stage IN OUT VARCHAR2,
761 x_err_stack IN OUT VARCHAR2,
762 x_err_code IN OUT NUMBER)
763 IS
764
765 -- Define Cursor for selecting expenditure types
766
767 CURSOR sel_expenditure_types IS
768 SELECT
769 EXPENDITURE_TYPE,
770 ALL_EXPENDITURE_TYPES,
771 EXPENDITURE_CATEGORY,
772 REVENUE_CATEGORY_CODE,
773 UNIT_OF_MEASURE,
774 DESCRIPTION,
775 USER_COL1,
776 USER_COL2,
777 USER_COL3,
778 USER_COL4,
779 USER_COL5,
780 USER_COL6,
781 USER_COL7,
782 USER_COL8,
783 USER_COL9,
784 USER_COL10,
785 ADW_NOTIFY_FLAG
786 FROM
787 PA_ADW_EXP_TYPES_V
788 WHERE
789 ADW_NOTIFY_FLAG = 'Y';
790
791 -- define procedure variables
792
793 expenditure_types_r sel_expenditure_types%ROWTYPE;
794
795 x_old_err_stack VARCHAR2(1024);
796 x_count number;
797
798 BEGIN
799 x_err_code := 0;
800 x_err_stage := 'Collecting Expenditure Types Dimension Table';
801 x_old_err_stack := x_err_stack;
802 x_err_stack := x_err_stack || '-> get_dim_expenditure_types';
803
804 pa_debug.debug(x_err_stage);
805
806 -- Process all expenditure types
807
808 FOR expenditure_types_r IN sel_expenditure_types LOOP
809
810 -- First Try to Update the Row in the Interface Table
811
812 UPDATE
813 PA_EXP_TYPES_IT
814 SET
815 ALL_EXPENDITURE_TYPES = EXPENDITURE_TYPES_R.ALL_EXPENDITURE_TYPES,
816 EXPENDITURE_CATEGORY = EXPENDITURE_TYPES_R.EXPENDITURE_CATEGORY,
817 REVENUE_CATEGORY_CODE = EXPENDITURE_TYPES_R.REVENUE_CATEGORY_CODE,
818 UNIT_OF_MEASURE = EXPENDITURE_TYPES_R.UNIT_OF_MEASURE,
819 LAST_UPDATE_DATE = TRUNC(SYSDATE),
820 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
821 CREATION_DATE = TRUNC(SYSDATE),
822 CREATED_BY = X_CREATED_BY,
823 DESCRIPTION = EXPENDITURE_TYPES_R.DESCRIPTION,
824 USER_COL1 = EXPENDITURE_TYPES_R.USER_COL1,
825 USER_COL2 = EXPENDITURE_TYPES_R.USER_COL2,
826 USER_COL3 = EXPENDITURE_TYPES_R.USER_COL3,
827 USER_COL4 = EXPENDITURE_TYPES_R.USER_COL4,
828 USER_COL5 = EXPENDITURE_TYPES_R.USER_COL5,
829 USER_COL6 = EXPENDITURE_TYPES_R.USER_COL6,
830 USER_COL7 = EXPENDITURE_TYPES_R.USER_COL7,
831 USER_COL8 = EXPENDITURE_TYPES_R.USER_COL8,
832 USER_COL9 = EXPENDITURE_TYPES_R.USER_COL9,
833 USER_COL10 = EXPENDITURE_TYPES_R.USER_COL10,
834 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
835 REQUEST_ID = X_REQUEST_ID,
836 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
837 PROGRAM_ID = X_PROGRAM_ID,
838 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
839 STATUS_CODE = 'P'
840 WHERE
841 EXPENDITURE_TYPE = EXPENDITURE_TYPES_R.EXPENDITURE_TYPE;
842
843 -- Check If Any row was updated
844
845 IF (SQL%ROWCOUNT = 0) THEN
846 -- No row was updated, So Insert a new row into the interface table
847 INSERT INTO PA_EXP_TYPES_IT
848 (
849 EXPENDITURE_TYPE,
850 ALL_EXPENDITURE_TYPES,
851 EXPENDITURE_CATEGORY,
852 REVENUE_CATEGORY_CODE,
853 UNIT_OF_MEASURE,
854 LAST_UPDATE_DATE,
855 LAST_UPDATED_BY,
856 CREATION_DATE,
857 CREATED_BY,
858 DESCRIPTION,
859 USER_COL1,
860 USER_COL2,
861 USER_COL3,
862 USER_COL4,
863 USER_COL5,
864 USER_COL6,
865 USER_COL7,
866 USER_COL8,
867 USER_COL9,
868 USER_COL10,
869 LAST_UPDATE_LOGIN,
870 REQUEST_ID,
871 PROGRAM_APPLICATION_ID,
872 PROGRAM_ID,
873 PROGRAM_UPDATE_DATE,
874 STATUS_CODE
875 )
876 VALUES
877 (
878 EXPENDITURE_TYPES_R.EXPENDITURE_TYPE,
879 EXPENDITURE_TYPES_R.ALL_EXPENDITURE_TYPES,
880 EXPENDITURE_TYPES_R.EXPENDITURE_CATEGORY,
881 EXPENDITURE_TYPES_R.REVENUE_CATEGORY_CODE,
882 EXPENDITURE_TYPES_R.UNIT_OF_MEASURE,
883 TRUNC(SYSDATE),
884 X_LAST_UPDATED_BY,
885 TRUNC(SYSDATE),
886 X_CREATED_BY,
887 EXPENDITURE_TYPES_R.DESCRIPTION,
888 EXPENDITURE_TYPES_R.USER_COL1,
889 EXPENDITURE_TYPES_R.USER_COL2,
890 EXPENDITURE_TYPES_R.USER_COL3,
891 EXPENDITURE_TYPES_R.USER_COL4,
892 EXPENDITURE_TYPES_R.USER_COL5,
893 EXPENDITURE_TYPES_R.USER_COL6,
894 EXPENDITURE_TYPES_R.USER_COL7,
895 EXPENDITURE_TYPES_R.USER_COL8,
896 EXPENDITURE_TYPES_R.USER_COL9,
897 EXPENDITURE_TYPES_R.USER_COL10,
898 X_LAST_UPDATE_LOGIN,
899 X_REQUEST_ID,
900 X_PROGRAM_APPLICATION_ID,
901 X_PROGRAM_ID,
902 TRUNC(SYSDATE),
903 'P'
904 );
905
906 END IF; -- IF ( SQL%ROWCOUNT = 0 )
907
908 -- Mark the expenditure types as transferred to Interface table
909
910 UPDATE
911 PA_EXPENDITURE_TYPES
912 SET
913 ADW_NOTIFY_FLAG = 'N'
914 WHERE
915 EXPENDITURE_TYPE = EXPENDITURE_TYPES_R.EXPENDITURE_TYPE;
916
917 END LOOP; -- FOR expenditure_types_r IN sel_expenditure_types
918
919 SELECT COUNT(*)
920 INTO x_count
921 From PA_ALL_EXP_TYPES_IT;
922
923 IF x_count = 0
924 Then
925 INSERT INTO PA_ALL_EXP_TYPES_IT
926 (
927 ALL_EXPENDITURE_TYPES,
928 LAST_UPDATE_DATE,
929 LAST_UPDATED_BY,
930 CREATION_DATE,
931 CREATED_BY,
932 LAST_UPDATE_LOGIN,
933 REQUEST_ID,
934 PROGRAM_APPLICATION_ID,
935 PROGRAM_ID,
936 PROGRAM_UPDATE_DATE,
937 STATUS_CODE
938 )
939 SELECT DISTINCT
940 ALL_EXPENDITURE_TYPES,
941 TRUNC(SYSDATE),
942 X_LAST_UPDATED_BY,
943 TRUNC(SYSDATE),
944 X_CREATED_BY,
945 X_LAST_UPDATE_LOGIN,
946 X_REQUEST_ID,
947 X_PROGRAM_APPLICATION_ID,
948 X_PROGRAM_ID,
949 TRUNC(SYSDATE),
950 'P'
951 FROM PA_ADW_EXP_TYPES_V;
952 END IF;
953
954 x_err_stack := x_old_err_stack;
955
956 pa_debug.debug('Completed ' || x_err_stage);
957
958 EXCEPTION
959 WHEN OTHERS THEN
960 x_err_code := SQLCODE;
961 RAISE;
962 END get_dim_expenditure_types;
963
964 -- Procedure to collect project classes
965
966 PROCEDURE get_dim_project_classes
967 ( x_err_stage IN OUT VARCHAR2,
968 x_err_stack IN OUT VARCHAR2,
969 x_err_code IN OUT NUMBER)
970 IS
971
972 -- Define Cursor for selecting project classes
973
974 CURSOR sel_project_classes IS
975 SELECT
976 PROJECT_ID,
977 CLASS_CATEGORY,
978 CLASS_CODE,
979 USER_COL1,
980 USER_COL2,
981 USER_COL3,
982 USER_COL4,
983 USER_COL5,
984 USER_COL6,
985 USER_COL7,
986 USER_COL8,
987 USER_COL9,
988 USER_COL10,
989 ADW_NOTIFY_FLAG
990 FROM
991 PA_ADW_PRJ_CLASSES_V
992 WHERE
993 ADW_NOTIFY_FLAG = 'Y';
994
995 -- define procedure variables
996
997 project_classes_r sel_project_classes%ROWTYPE;
998
999 x_old_err_stack VARCHAR2(1024);
1000
1001 BEGIN
1002 x_err_code := 0;
1003 x_err_stage := 'Collecting Projects Classes Dimension Table';
1004 x_old_err_stack := x_err_stack;
1005 x_err_stack := x_err_stack || '-> get_dim_project_classes';
1006
1007 pa_debug.debug(x_err_stage);
1008
1009 -- Process all project classes
1010
1011 FOR project_classes_r IN sel_project_classes LOOP
1012
1013 -- First Try to Update the Row in the Interface Table
1014
1015 UPDATE
1016 PA_PRJ_CLASSES_IT
1017 SET
1018 LAST_UPDATE_DATE = TRUNC(SYSDATE),
1019 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1020 CREATION_DATE = TRUNC(SYSDATE),
1021 CREATED_BY = X_CREATED_BY,
1022 USER_COL1 = PROJECT_CLASSES_R.USER_COL1,
1023 USER_COL2 = PROJECT_CLASSES_R.USER_COL2,
1024 USER_COL3 = PROJECT_CLASSES_R.USER_COL3,
1025 USER_COL4 = PROJECT_CLASSES_R.USER_COL4,
1026 USER_COL5 = PROJECT_CLASSES_R.USER_COL5,
1027 USER_COL6 = PROJECT_CLASSES_R.USER_COL6,
1028 USER_COL7 = PROJECT_CLASSES_R.USER_COL7,
1029 USER_COL8 = PROJECT_CLASSES_R.USER_COL8,
1030 USER_COL9 = PROJECT_CLASSES_R.USER_COL9,
1031 USER_COL10 = PROJECT_CLASSES_R.USER_COL10,
1032 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1033 REQUEST_ID = X_REQUEST_ID,
1034 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1035 PROGRAM_ID = X_PROGRAM_ID,
1036 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
1037 STATUS_CODE = 'P'
1038 WHERE
1039 PROJECT_ID = PROJECT_CLASSES_R.PROJECT_ID
1040 AND CLASS_CATEGORY = PROJECT_CLASSES_R.CLASS_CATEGORY
1041 AND CLASS_CODE = PROJECT_CLASSES_R.CLASS_CODE;
1042
1043 -- Check If Any row was updated
1044
1045 IF (SQL%ROWCOUNT = 0) THEN
1046 -- No row was updated, So Insert a new row into the interface table
1047 INSERT INTO PA_PRJ_CLASSES_IT
1048 (
1049 PROJECT_ID,
1050 CLASS_CATEGORY,
1051 CLASS_CODE,
1052 LAST_UPDATE_DATE,
1053 LAST_UPDATED_BY,
1054 CREATION_DATE,
1055 CREATED_BY,
1056 USER_COL1,
1057 USER_COL2,
1058 USER_COL3,
1059 USER_COL4,
1060 USER_COL5,
1061 USER_COL6,
1062 USER_COL7,
1063 USER_COL8,
1064 USER_COL9,
1065 USER_COL10,
1066 LAST_UPDATE_LOGIN,
1067 REQUEST_ID,
1068 PROGRAM_APPLICATION_ID,
1069 PROGRAM_ID,
1070 PROGRAM_UPDATE_DATE,
1071 STATUS_CODE
1072 )
1073 VALUES
1074 (
1075 PROJECT_CLASSES_R.PROJECT_ID,
1076 PROJECT_CLASSES_R.CLASS_CATEGORY,
1077 PROJECT_CLASSES_R.CLASS_CODE,
1078 TRUNC(SYSDATE),
1079 X_LAST_UPDATED_BY,
1080 TRUNC(SYSDATE),
1081 X_CREATED_BY,
1082 PROJECT_CLASSES_R.USER_COL1,
1083 PROJECT_CLASSES_R.USER_COL2,
1084 PROJECT_CLASSES_R.USER_COL3,
1085 PROJECT_CLASSES_R.USER_COL4,
1086 PROJECT_CLASSES_R.USER_COL5,
1087 PROJECT_CLASSES_R.USER_COL6,
1088 PROJECT_CLASSES_R.USER_COL7,
1089 PROJECT_CLASSES_R.USER_COL8,
1090 PROJECT_CLASSES_R.USER_COL9,
1091 PROJECT_CLASSES_R.USER_COL10,
1092 X_LAST_UPDATE_LOGIN,
1093 X_REQUEST_ID,
1094 X_PROGRAM_APPLICATION_ID,
1095 X_PROGRAM_ID,
1096 TRUNC(SYSDATE),
1097 'P'
1098 );
1099
1100 END IF; -- IF ( SQL%ROWCOUNT = 0 )
1101
1102 -- Mark the project classes as transferred to Interface table
1103
1104 UPDATE
1105 PA_PROJECT_CLASSES
1106 SET
1107 ADW_NOTIFY_FLAG = 'N'
1108 WHERE
1109 PROJECT_ID = PROJECT_CLASSES_R.PROJECT_ID
1110 AND CLASS_CATEGORY = PROJECT_CLASSES_R.CLASS_CATEGORY
1111 AND CLASS_CODE = PROJECT_CLASSES_R.CLASS_CODE;
1112
1113 END LOOP; -- FOR project_classes_r IN sel_project_classes
1114
1115 x_err_stack := x_old_err_stack;
1116
1117 pa_debug.debug('Completed ' || x_err_stage);
1118
1119 EXCEPTION
1120 WHEN OTHERS THEN
1121 x_err_code := SQLCODE;
1122 RAISE;
1123 END get_dim_project_classes;
1124
1125 -- Procedure to collect class categories dimension
1126
1127 PROCEDURE get_dim_class_categories
1128 ( x_err_stage IN OUT VARCHAR2,
1129 x_err_stack IN OUT VARCHAR2,
1130 x_err_code IN OUT NUMBER)
1131 IS
1132
1133 -- Define Cursor for selecting class categories
1134
1135 CURSOR sel_class_categories IS
1136 SELECT
1137 CLASS_CATEGORY,
1138 DESCRIPTION,
1139 USER_COL1,
1140 USER_COL2,
1141 USER_COL3,
1142 USER_COL4,
1143 USER_COL5,
1144 USER_COL6,
1145 USER_COL7,
1146 USER_COL8,
1147 USER_COL9,
1148 USER_COL10,
1149 ADW_NOTIFY_FLAG
1150 FROM
1151 PA_ADW_CLASS_CATGS_V
1152 WHERE
1153 ADW_NOTIFY_FLAG = 'Y';
1154
1155 -- define procedure variables
1156
1157 class_categories_r sel_class_categories%ROWTYPE;
1158
1159 x_old_err_stack VARCHAR2(1024);
1160
1161 BEGIN
1162 x_err_code := 0;
1163 x_err_stage := 'Collecting Class Categories Dimension Table';
1164 x_old_err_stack := x_err_stack;
1165 x_err_stack := x_err_stack || '-> get_dim_class_categories';
1166
1167 pa_debug.debug(x_err_stage);
1168
1169 -- Process all class categories
1170
1171 FOR class_categories_r IN sel_class_categories LOOP
1172
1173 -- First Try to Update the Row in the Interface Table
1174
1175 UPDATE
1176 PA_CLASS_CATGS_IT
1177 SET
1178 DESCRIPTION = CLASS_CATEGORIES_R.DESCRIPTION,
1179 LAST_UPDATE_DATE = TRUNC(SYSDATE),
1180 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1181 CREATION_DATE = TRUNC(SYSDATE),
1182 CREATED_BY = X_CREATED_BY,
1183 USER_COL1 = CLASS_CATEGORIES_R.USER_COL1,
1184 USER_COL2 = CLASS_CATEGORIES_R.USER_COL2,
1185 USER_COL3 = CLASS_CATEGORIES_R.USER_COL3,
1186 USER_COL4 = CLASS_CATEGORIES_R.USER_COL4,
1187 USER_COL5 = CLASS_CATEGORIES_R.USER_COL5,
1188 USER_COL6 = CLASS_CATEGORIES_R.USER_COL6,
1189 USER_COL7 = CLASS_CATEGORIES_R.USER_COL7,
1190 USER_COL8 = CLASS_CATEGORIES_R.USER_COL8,
1191 USER_COL9 = CLASS_CATEGORIES_R.USER_COL9,
1192 USER_COL10 = CLASS_CATEGORIES_R.USER_COL10,
1193 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1194 REQUEST_ID = X_REQUEST_ID,
1195 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1196 PROGRAM_ID = X_PROGRAM_ID,
1197 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
1198 STATUS_CODE = 'P'
1199 WHERE
1200 CLASS_CATEGORY = CLASS_CATEGORIES_R.CLASS_CATEGORY;
1201
1202 -- Check If Any row was updated
1203
1204 IF (SQL%ROWCOUNT = 0) THEN
1205 -- No row was updated, So Insert a new row into the interface table
1206 INSERT INTO PA_CLASS_CATGS_IT
1207 (
1208 CLASS_CATEGORY,
1209 DESCRIPTION,
1210 LAST_UPDATE_DATE,
1211 LAST_UPDATED_BY,
1212 CREATION_DATE,
1213 CREATED_BY,
1214 USER_COL1,
1215 USER_COL2,
1216 USER_COL3,
1217 USER_COL4,
1218 USER_COL5,
1219 USER_COL6,
1220 USER_COL7,
1221 USER_COL8,
1222 USER_COL9,
1223 USER_COL10,
1224 LAST_UPDATE_LOGIN,
1225 REQUEST_ID,
1226 PROGRAM_APPLICATION_ID,
1227 PROGRAM_ID,
1228 PROGRAM_UPDATE_DATE,
1229 STATUS_CODE
1230 )
1231 VALUES
1232 (
1233 CLASS_CATEGORIES_R.CLASS_CATEGORY,
1234 CLASS_CATEGORIES_R.DESCRIPTION,
1235 TRUNC(SYSDATE),
1236 X_LAST_UPDATED_BY,
1237 TRUNC(SYSDATE),
1238 X_CREATED_BY,
1239 CLASS_CATEGORIES_R.USER_COL1,
1240 CLASS_CATEGORIES_R.USER_COL2,
1241 CLASS_CATEGORIES_R.USER_COL3,
1242 CLASS_CATEGORIES_R.USER_COL4,
1243 CLASS_CATEGORIES_R.USER_COL5,
1244 CLASS_CATEGORIES_R.USER_COL6,
1245 CLASS_CATEGORIES_R.USER_COL7,
1246 CLASS_CATEGORIES_R.USER_COL8,
1247 CLASS_CATEGORIES_R.USER_COL9,
1248 CLASS_CATEGORIES_R.USER_COL10,
1249 X_LAST_UPDATE_LOGIN,
1250 X_REQUEST_ID,
1251 X_PROGRAM_APPLICATION_ID,
1252 X_PROGRAM_ID,
1253 TRUNC(SYSDATE),
1254 'P'
1255 );
1256
1257 END IF; -- IF ( SQL%ROWCOUNT = 0 )
1258
1259 -- Mark the class categories as transferred to Interface table
1260
1261 UPDATE
1262 PA_CLASS_CATEGORIES
1263 SET
1264 ADW_NOTIFY_FLAG = 'N'
1265 WHERE
1266 CLASS_CATEGORY = CLASS_CATEGORIES_R.CLASS_CATEGORY;
1267
1268 END LOOP; -- FOR class_categories_r IN sel_class_categories
1269
1270 x_err_stack := x_old_err_stack;
1271
1272 pa_debug.debug('Completed ' || x_err_stage);
1273
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 x_err_code := SQLCODE;
1277 RAISE;
1278 END get_dim_class_categories;
1279
1280 -- Procedure to collect class codes dimension
1281
1282 PROCEDURE get_dim_class_codes
1283 ( x_err_stage IN OUT VARCHAR2,
1284 x_err_stack IN OUT VARCHAR2,
1285 x_err_code IN OUT NUMBER)
1286 IS
1287
1288 -- Define Cursor for selecting class codes
1289
1290 CURSOR sel_class_codes IS
1291 SELECT
1292 CLASS_CATEGORY,
1293 CLASS_CODE,
1294 DESCRIPTION,
1295 USER_COL1,
1296 USER_COL2,
1297 USER_COL3,
1298 USER_COL4,
1299 USER_COL5,
1300 USER_COL6,
1301 USER_COL7,
1302 USER_COL8,
1303 USER_COL9,
1304 USER_COL10,
1305 ADW_NOTIFY_FLAG
1306 FROM
1307 PA_ADW_CLASS_CODES_V
1308 WHERE
1309 ADW_NOTIFY_FLAG = 'Y';
1310
1311 -- define procedure variables
1312
1313 class_codes_r sel_class_codes%ROWTYPE;
1314
1315 x_old_err_stack VARCHAR2(1024);
1316
1317 BEGIN
1318 x_err_code := 0;
1319 x_err_stage := 'Collecting Class Codes Dimension Table';
1320 x_old_err_stack := x_err_stack;
1321 x_err_stack := x_err_stack || '-> get_dim_class_codes';
1322
1323 pa_debug.debug(x_err_stage);
1324
1325 -- Process all classes codes
1326
1327 FOR class_codes_r IN sel_class_codes LOOP
1328
1329 -- First Try to Update the Row in the Interface Table
1330
1331 UPDATE
1332 PA_CLASS_CODES_IT
1333 SET
1334 DESCRIPTION = CLASS_CODES_R.DESCRIPTION,
1335 LAST_UPDATE_DATE = TRUNC(SYSDATE),
1336 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1337 CREATION_DATE = TRUNC(SYSDATE),
1338 CREATED_BY = X_CREATED_BY,
1339 USER_COL1 = CLASS_CODES_R.USER_COL1,
1340 USER_COL2 = CLASS_CODES_R.USER_COL2,
1341 USER_COL3 = CLASS_CODES_R.USER_COL3,
1342 USER_COL4 = CLASS_CODES_R.USER_COL4,
1343 USER_COL5 = CLASS_CODES_R.USER_COL5,
1344 USER_COL6 = CLASS_CODES_R.USER_COL6,
1345 USER_COL7 = CLASS_CODES_R.USER_COL7,
1346 USER_COL8 = CLASS_CODES_R.USER_COL8,
1347 USER_COL9 = CLASS_CODES_R.USER_COL9,
1348 USER_COL10 = CLASS_CODES_R.USER_COL10,
1349 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1350 REQUEST_ID = X_REQUEST_ID,
1351 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1352 PROGRAM_ID = X_PROGRAM_ID,
1353 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
1354 STATUS_CODE = 'P'
1355 WHERE
1356 CLASS_CATEGORY = CLASS_CODES_R.CLASS_CATEGORY
1357 AND CLASS_CODE = CLASS_CODES_R.CLASS_CODE;
1358
1359 -- Check If Any row was updated
1360
1361 IF (SQL%ROWCOUNT = 0) THEN
1362 -- No row was updated, So Insert a new row into the interface table
1363 INSERT INTO PA_CLASS_CODES_IT
1364 (
1365 CLASS_CATEGORY,
1366 CLASS_CODE,
1367 DESCRIPTION,
1368 LAST_UPDATE_DATE,
1369 LAST_UPDATED_BY,
1370 CREATION_DATE,
1371 CREATED_BY,
1372 USER_COL1,
1373 USER_COL2,
1374 USER_COL3,
1375 USER_COL4,
1376 USER_COL5,
1377 USER_COL6,
1378 USER_COL7,
1379 USER_COL8,
1380 USER_COL9,
1381 USER_COL10,
1382 LAST_UPDATE_LOGIN,
1383 REQUEST_ID,
1384 PROGRAM_APPLICATION_ID,
1385 PROGRAM_ID,
1386 PROGRAM_UPDATE_DATE,
1387 STATUS_CODE
1388 )
1389 VALUES
1390 (
1391 CLASS_CODES_R.CLASS_CATEGORY,
1392 CLASS_CODES_R.CLASS_CODE,
1393 CLASS_CODES_R.DESCRIPTION,
1394 TRUNC(SYSDATE),
1395 X_LAST_UPDATED_BY,
1396 TRUNC(SYSDATE),
1397 X_CREATED_BY,
1398 CLASS_CODES_R.USER_COL1,
1399 CLASS_CODES_R.USER_COL2,
1400 CLASS_CODES_R.USER_COL3,
1401 CLASS_CODES_R.USER_COL4,
1402 CLASS_CODES_R.USER_COL5,
1403 CLASS_CODES_R.USER_COL6,
1404 CLASS_CODES_R.USER_COL7,
1405 CLASS_CODES_R.USER_COL8,
1406 CLASS_CODES_R.USER_COL9,
1407 CLASS_CODES_R.USER_COL10,
1408 X_LAST_UPDATE_LOGIN,
1409 X_REQUEST_ID,
1410 X_PROGRAM_APPLICATION_ID,
1411 X_PROGRAM_ID,
1412 TRUNC(SYSDATE),
1413 'P'
1414 );
1415
1416 END IF; -- IF ( SQL%ROWCOUNT = 0 )
1417
1418 -- Mark the class categories as transferred to Interface table
1419
1420 UPDATE
1421 PA_CLASS_CODES
1422 SET
1423 ADW_NOTIFY_FLAG = 'N'
1424 WHERE
1425 CLASS_CATEGORY = CLASS_CODES_R.CLASS_CATEGORY
1426 AND CLASS_CODE = CLASS_CODES_R.CLASS_CODE;
1427
1428 END LOOP; -- FOR class_codes_r IN sel_class_codes
1429
1430 x_err_stack := x_old_err_stack;
1431
1432 pa_debug.debug('Completed ' || x_err_stage);
1433
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 x_err_code := SQLCODE;
1437 RAISE;
1438 END get_dim_class_codes;
1439
1440 -- Procedure to collect lowest/top level resource dimension
1441 -- We have one procedure for lowest level resource and top level resource
1442 -- because a resource may be top level resource as well as lowest level resource
1443
1444 PROCEDURE get_dim_resources
1445 ( x_err_stage IN OUT VARCHAR2,
1446 x_err_stack IN OUT VARCHAR2,
1447 x_err_code IN OUT NUMBER)
1448 IS
1449
1450 -- Define Cursor for selecting lowest level resources
1451
1452 CURSOR sel_lowest_res_members IS
1453 SELECT
1454 RESOURCE_LIST_MEMBER_ID,
1455 PARENT_MEMBER_ID,
1456 NAME,
1457 ALIAS,
1458 USER_COL1,
1459 USER_COL2,
1460 USER_COL3,
1461 USER_COL4,
1462 USER_COL5,
1463 USER_COL6,
1464 USER_COL7,
1465 USER_COL8,
1466 USER_COL9,
1467 USER_COL10,
1468 ADW_NOTIFY_FLAG
1469 FROM
1470 PA_ADW_LOWEST_RLMEM_V
1471 WHERE
1472 ADW_NOTIFY_FLAG = 'Y';
1473
1474 -- Define Cursor for selecting top level resources
1475
1476 CURSOR sel_top_res_members IS
1477 SELECT
1478 RESOURCE_LIST_MEMBER_ID,
1479 RESOURCE_LIST_ID,
1480 NAME,
1481 ALIAS,
1482 USER_COL1,
1483 USER_COL2,
1484 USER_COL3,
1485 USER_COL4,
1486 USER_COL5,
1487 USER_COL6,
1488 USER_COL7,
1489 USER_COL8,
1490 USER_COL9,
1491 USER_COL10,
1492 ADW_NOTIFY_FLAG
1493 FROM
1494 PA_ADW_TOP_RLMEM_V
1495 WHERE
1496 ADW_NOTIFY_FLAG IN ('Y','S');
1497
1498 -- define procedure variables
1499
1500 lowest_res_members_r sel_lowest_res_members%ROWTYPE;
1501 top_res_members_r sel_top_res_members%ROWTYPE;
1502
1503 x_old_err_stack VARCHAR2(1024);
1504
1505 BEGIN
1506 x_err_code := 0;
1507 x_err_stage := 'Collecting Lowest Level Resource Dimension Table';
1508 x_old_err_stack := x_err_stack;
1509 x_err_stack := x_err_stack || '-> get_dim_resources';
1510
1511 pa_debug.debug(x_err_stage);
1512
1513 -- Process all lowest level resource first
1514
1515 FOR lowest_res_members_r IN sel_lowest_res_members LOOP
1516
1517 -- First Try to Update the Row in the Interface Table
1518 UPDATE
1519 PA_LOWEST_RLMEM_IT
1520 SET
1521 PARENT_MEMBER_ID = LOWEST_RES_MEMBERS_R.PARENT_MEMBER_ID,
1522 NAME = LOWEST_RES_MEMBERS_R.NAME,
1523 ALIAS = LOWEST_RES_MEMBERS_R.ALIAS,
1524 LAST_UPDATE_DATE = TRUNC(SYSDATE),
1525 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1526 CREATION_DATE = TRUNC(SYSDATE),
1527 CREATED_BY = X_CREATED_BY,
1528 USER_COL1 = LOWEST_RES_MEMBERS_R.USER_COL1,
1529 USER_COL2 = LOWEST_RES_MEMBERS_R.USER_COL2,
1530 USER_COL3 = LOWEST_RES_MEMBERS_R.USER_COL3,
1531 USER_COL4 = LOWEST_RES_MEMBERS_R.USER_COL4,
1532 USER_COL5 = LOWEST_RES_MEMBERS_R.USER_COL5,
1533 USER_COL6 = LOWEST_RES_MEMBERS_R.USER_COL6,
1534 USER_COL7 = LOWEST_RES_MEMBERS_R.USER_COL7,
1535 USER_COL8 = LOWEST_RES_MEMBERS_R.USER_COL8,
1536 USER_COL9 = LOWEST_RES_MEMBERS_R.USER_COL9,
1537 USER_COL10 = LOWEST_RES_MEMBERS_R.USER_COL10,
1538 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1539 REQUEST_ID = X_REQUEST_ID,
1540 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1541 PROGRAM_ID = X_PROGRAM_ID,
1542 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
1543 STATUS_CODE = 'P'
1544 WHERE
1545 RESOURCE_LIST_MEMBER_ID = LOWEST_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID;
1546
1547 -- Check If Any row was updated
1548
1549 IF (SQL%ROWCOUNT = 0) THEN
1550 -- No row was updated, So Insert a new row into the interface table
1551 INSERT INTO PA_LOWEST_RLMEM_IT
1552 (
1553 RESOURCE_LIST_MEMBER_ID,
1554 PARENT_MEMBER_ID,
1555 NAME,
1556 ALIAS,
1557 LAST_UPDATE_DATE,
1558 LAST_UPDATED_BY,
1559 CREATION_DATE,
1560 CREATED_BY,
1561 USER_COL1,
1562 USER_COL2,
1563 USER_COL3,
1564 USER_COL4,
1565 USER_COL5,
1566 USER_COL6,
1567 USER_COL7,
1568 USER_COL8,
1569 USER_COL9,
1570 USER_COL10,
1571 LAST_UPDATE_LOGIN,
1572 REQUEST_ID,
1573 PROGRAM_APPLICATION_ID,
1574 PROGRAM_ID,
1575 PROGRAM_UPDATE_DATE,
1576 STATUS_CODE
1577 )
1578 VALUES
1579 (
1580 LOWEST_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID,
1581 LOWEST_RES_MEMBERS_R.PARENT_MEMBER_ID,
1582 LOWEST_RES_MEMBERS_R.NAME,
1583 LOWEST_RES_MEMBERS_R.ALIAS,
1584 TRUNC(SYSDATE),
1585 X_LAST_UPDATED_BY,
1586 TRUNC(SYSDATE),
1587 X_CREATED_BY,
1588 LOWEST_RES_MEMBERS_R.USER_COL1,
1589 LOWEST_RES_MEMBERS_R.USER_COL2,
1590 LOWEST_RES_MEMBERS_R.USER_COL3,
1591 LOWEST_RES_MEMBERS_R.USER_COL4,
1592 LOWEST_RES_MEMBERS_R.USER_COL5,
1593 LOWEST_RES_MEMBERS_R.USER_COL6,
1594 LOWEST_RES_MEMBERS_R.USER_COL7,
1595 LOWEST_RES_MEMBERS_R.USER_COL8,
1596 LOWEST_RES_MEMBERS_R.USER_COL9,
1597 LOWEST_RES_MEMBERS_R.USER_COL10,
1598 X_LAST_UPDATE_LOGIN,
1599 X_REQUEST_ID,
1600 X_PROGRAM_APPLICATION_ID,
1601 X_PROGRAM_ID,
1602 TRUNC(SYSDATE),
1603 'P'
1604 );
1605
1606 END IF; -- IF ( SQL%ROWCOUNT = 0 )
1607
1608 -- Mark the Resource as being transferred to the Interface table
1609 -- We are marking these Resource as 'S', since some of these Resource
1610 -- may be top level Resource too
1611
1612 -- PLEASE NOTE THAT WE ARE UPDATING THE BASE TABLE SINCE THE
1613 -- PA_ADW_LOWEST_RLMEM_V IS DEFINED ON MULTIPLE TABLES
1614
1615 UPDATE
1616 PA_RESOURCE_LIST_MEMBERS
1617 SET
1618 ADW_NOTIFY_FLAG = 'S'
1619 WHERE
1620 RESOURCE_LIST_MEMBER_ID = LOWEST_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID;
1621
1622 END LOOP; -- FOR lowest_res_members_r IN sel_lowest_res_members
1623
1624 x_err_stage := 'Collecting Top Level Resource Dimension Table';
1625
1626 -- Now process the top level Resource
1627
1628 FOR top_res_members_r IN sel_top_res_members LOOP
1629
1630 -- First Try to Update the Row in the Interface Table
1631 UPDATE
1632 PA_TOP_RLMEM_IT
1633 SET
1634 RESOURCE_LIST_ID = TOP_RES_MEMBERS_R.RESOURCE_LIST_ID,
1635 NAME = TOP_RES_MEMBERS_R.NAME,
1636 ALIAS = TOP_RES_MEMBERS_R.ALIAS,
1637 LAST_UPDATE_DATE = TRUNC(SYSDATE),
1638 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1639 CREATION_DATE = TRUNC(SYSDATE),
1640 CREATED_BY = X_CREATED_BY,
1641 USER_COL1 = TOP_RES_MEMBERS_R.USER_COL1,
1642 USER_COL2 = TOP_RES_MEMBERS_R.USER_COL2,
1643 USER_COL3 = TOP_RES_MEMBERS_R.USER_COL3,
1644 USER_COL4 = TOP_RES_MEMBERS_R.USER_COL4,
1645 USER_COL5 = TOP_RES_MEMBERS_R.USER_COL5,
1646 USER_COL6 = TOP_RES_MEMBERS_R.USER_COL6,
1647 USER_COL7 = TOP_RES_MEMBERS_R.USER_COL7,
1648 USER_COL8 = TOP_RES_MEMBERS_R.USER_COL8,
1649 USER_COL9 = TOP_RES_MEMBERS_R.USER_COL9,
1650 USER_COL10 = TOP_RES_MEMBERS_R.USER_COL10,
1651 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1652 REQUEST_ID = X_REQUEST_ID,
1653 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1654 PROGRAM_ID = X_PROGRAM_ID,
1655 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
1656 STATUS_CODE = 'P'
1657 WHERE
1658 RESOURCE_LIST_MEMBER_ID = TOP_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID;
1659
1660 -- Check If Any row was updated
1661
1662 IF (SQL%ROWCOUNT = 0) THEN
1663 -- No row was updated, So Insert a new row into the interface table
1664 INSERT INTO PA_TOP_RLMEM_IT
1665 (
1666 RESOURCE_LIST_MEMBER_ID,
1667 RESOURCE_LIST_ID,
1668 NAME,
1669 ALIAS,
1670 LAST_UPDATE_DATE,
1671 LAST_UPDATED_BY,
1672 CREATION_DATE,
1673 CREATED_BY,
1674 USER_COL1,
1675 USER_COL2,
1676 USER_COL3,
1677 USER_COL4,
1678 USER_COL5,
1679 USER_COL6,
1680 USER_COL7,
1681 USER_COL8,
1682 USER_COL9,
1683 USER_COL10,
1684 LAST_UPDATE_LOGIN,
1685 REQUEST_ID,
1686 PROGRAM_APPLICATION_ID,
1687 PROGRAM_ID,
1688 PROGRAM_UPDATE_DATE,
1689 STATUS_CODE
1690 )
1691 VALUES
1692 (
1693 TOP_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID,
1694 TOP_RES_MEMBERS_R.RESOURCE_LIST_ID,
1695 TOP_RES_MEMBERS_R.NAME,
1696 TOP_RES_MEMBERS_R.ALIAS,
1697 TRUNC(SYSDATE),
1698 X_LAST_UPDATED_BY,
1699 TRUNC(SYSDATE),
1700 X_CREATED_BY,
1701 TOP_RES_MEMBERS_R.USER_COL1,
1702 TOP_RES_MEMBERS_R.USER_COL2,
1703 TOP_RES_MEMBERS_R.USER_COL3,
1704 TOP_RES_MEMBERS_R.USER_COL4,
1705 TOP_RES_MEMBERS_R.USER_COL5,
1706 TOP_RES_MEMBERS_R.USER_COL6,
1707 TOP_RES_MEMBERS_R.USER_COL7,
1708 TOP_RES_MEMBERS_R.USER_COL8,
1709 TOP_RES_MEMBERS_R.USER_COL9,
1710 TOP_RES_MEMBERS_R.USER_COL10,
1711 X_LAST_UPDATE_LOGIN,
1712 X_REQUEST_ID,
1713 X_PROGRAM_APPLICATION_ID,
1714 X_PROGRAM_ID,
1715 TRUNC(SYSDATE),
1716 'P'
1717 );
1718
1719 END IF; -- IF ( SQL%ROWCOUNT = 0 )
1720
1721 -- Mark the resources as transferred to Interface table
1722
1723 -- PLEASE NOTE THAT WE ARE UPDATING THE BASE TABLE SINCE THE
1724 -- PA_ADW_TOP_RLMEM_V IS DEFINED ON MULTIPLE TABLES
1725
1726 UPDATE
1727 PA_RESOURCE_LIST_MEMBERS
1728 SET
1729 ADW_NOTIFY_FLAG = 'N'
1730 WHERE
1731 RESOURCE_LIST_MEMBER_ID = TOP_RES_MEMBERS_R.RESOURCE_LIST_MEMBER_ID;
1732
1733 END LOOP; -- FOR top_res_members_r IN sel_top_res_members
1734
1735 -- Now update all remaining Low Level resources as transferred to the
1736 -- interface table
1737
1738 UPDATE
1739 PA_RESOURCE_LIST_MEMBERS
1740 SET
1741 ADW_NOTIFY_FLAG = 'N'
1742 WHERE
1743 ADW_NOTIFY_FLAG = 'S';
1744
1745 x_err_stack := x_old_err_stack;
1746
1747 pa_debug.debug('Completed ' || x_err_stage);
1748
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 x_err_code := SQLCODE;
1752 RAISE;
1753 END get_dim_resources;
1754
1755 -- Procedure to collect resource list dimension
1756
1757 PROCEDURE get_dim_resource_lists
1758 ( x_err_stage IN OUT VARCHAR2,
1759 x_err_stack IN OUT VARCHAR2,
1760 x_err_code IN OUT NUMBER)
1761 IS
1762
1763 -- Define Cursor for selecting resource lists
1764
1765 CURSOR sel_resource_lists IS
1766 SELECT
1767 RESOURCE_LIST_ID,
1768 NAME,
1769 DESCRIPTION,
1770 USER_COL1,
1771 USER_COL2,
1772 USER_COL3,
1773 USER_COL4,
1774 USER_COL5,
1775 USER_COL6,
1776 USER_COL7,
1777 USER_COL8,
1778 USER_COL9,
1779 USER_COL10,
1780 ADW_NOTIFY_FLAG
1781 FROM
1782 PA_ADW_RES_LISTS_V
1783 WHERE
1784 ADW_NOTIFY_FLAG = 'Y';
1785
1786 -- define procedure variables
1787
1788 resource_lists_r sel_resource_lists%ROWTYPE;
1789
1790 x_old_err_stack VARCHAR2(1024);
1791
1792 BEGIN
1793 x_err_code := 0;
1794 x_err_stage := 'Collecting Resource List Dimension Table';
1795 x_old_err_stack := x_err_stack;
1796 x_err_stack := x_err_stack || '-> get_dim_resource_lists';
1797
1798 pa_debug.debug(x_err_stage);
1799
1800 -- Process all resource lists
1801
1802 FOR resource_lists_r IN sel_resource_lists LOOP
1803
1804 -- First Try to Update the Row in the Interface Table
1805
1806 UPDATE
1807 PA_RES_LISTS_IT
1808 SET
1809 NAME = RESOURCE_LISTS_R.NAME,
1810 LAST_UPDATE_DATE = TRUNC(SYSDATE),
1811 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1812 CREATION_DATE = TRUNC(SYSDATE),
1813 CREATED_BY = X_CREATED_BY,
1814 DESCRIPTION = RESOURCE_LISTS_R.DESCRIPTION,
1815 USER_COL1 = RESOURCE_LISTS_R.USER_COL1,
1816 USER_COL2 = RESOURCE_LISTS_R.USER_COL2,
1817 USER_COL3 = RESOURCE_LISTS_R.USER_COL3,
1818 USER_COL4 = RESOURCE_LISTS_R.USER_COL4,
1819 USER_COL5 = RESOURCE_LISTS_R.USER_COL5,
1820 USER_COL6 = RESOURCE_LISTS_R.USER_COL6,
1821 USER_COL7 = RESOURCE_LISTS_R.USER_COL7,
1822 USER_COL8 = RESOURCE_LISTS_R.USER_COL8,
1823 USER_COL9 = RESOURCE_LISTS_R.USER_COL9,
1824 USER_COL10 = RESOURCE_LISTS_R.USER_COL10,
1825 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1826 REQUEST_ID = X_REQUEST_ID,
1827 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1828 PROGRAM_ID = X_PROGRAM_ID,
1829 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
1830 STATUS_CODE = 'P'
1831 WHERE
1832 RESOURCE_LIST_ID = RESOURCE_LISTS_R.RESOURCE_LIST_ID;
1833
1834 -- Check If Any row was updated
1835
1836 IF (SQL%ROWCOUNT = 0) THEN
1837 -- No row was updated, So Insert a new row into the interface table
1838 INSERT INTO PA_RES_LISTS_IT
1839 (
1840 RESOURCE_LIST_ID,
1841 LAST_UPDATE_DATE,
1842 LAST_UPDATED_BY,
1843 CREATION_DATE,
1844 CREATED_BY,
1845 NAME,
1846 DESCRIPTION,
1847 USER_COL1,
1848 USER_COL2,
1849 USER_COL3,
1850 USER_COL4,
1851 USER_COL5,
1852 USER_COL6,
1853 USER_COL7,
1854 USER_COL8,
1855 USER_COL9,
1856 USER_COL10,
1857 LAST_UPDATE_LOGIN,
1858 REQUEST_ID,
1859 PROGRAM_APPLICATION_ID,
1860 PROGRAM_ID,
1861 PROGRAM_UPDATE_DATE,
1862 STATUS_CODE,
1863 BUSINESS_GROUP_ID
1864 )
1865 VALUES
1866 (
1867 RESOURCE_LISTS_R.RESOURCE_LIST_ID,
1868 TRUNC(SYSDATE),
1869 X_LAST_UPDATED_BY,
1870 TRUNC(SYSDATE),
1871 X_CREATED_BY,
1872 RESOURCE_LISTS_R.NAME,
1873 RESOURCE_LISTS_R.DESCRIPTION,
1874 RESOURCE_LISTS_R.USER_COL1,
1875 RESOURCE_LISTS_R.USER_COL2,
1876 RESOURCE_LISTS_R.USER_COL3,
1877 RESOURCE_LISTS_R.USER_COL4,
1878 RESOURCE_LISTS_R.USER_COL5,
1879 RESOURCE_LISTS_R.USER_COL6,
1880 RESOURCE_LISTS_R.USER_COL7,
1881 RESOURCE_LISTS_R.USER_COL8,
1882 RESOURCE_LISTS_R.USER_COL9,
1883 RESOURCE_LISTS_R.USER_COL10,
1884 X_LAST_UPDATE_LOGIN,
1885 X_REQUEST_ID,
1886 X_PROGRAM_APPLICATION_ID,
1887 X_PROGRAM_ID,
1888 TRUNC(SYSDATE),
1889 'P',
1890 PA_UTILS.BUSINESS_GROUP_ID
1891 );
1892
1893 END IF; -- IF ( SQL%ROWCOUNT = 0 )
1894
1895 -- Mark the project types as transferred to Interface table
1896
1897 UPDATE
1898 PA_RESOURCE_LISTS
1899 SET
1900 ADW_NOTIFY_FLAG = 'N'
1901 WHERE
1902 RESOURCE_LIST_ID = RESOURCE_LISTS_R.RESOURCE_LIST_ID;
1903
1904 END LOOP; -- FOR resource_lists_r IN sel_resource_lists
1905
1906 x_err_stack := x_old_err_stack;
1907
1908 pa_debug.debug('Completed ' || x_err_stage);
1909
1910 EXCEPTION
1911 WHEN OTHERS THEN
1912 x_err_code := SQLCODE;
1913 RAISE;
1914 END get_dim_resource_lists;
1915
1916 -- prcoedure to collect budget type dimension
1917
1918 PROCEDURE get_dim_budget_types
1919 ( x_err_stage IN OUT VARCHAR2,
1920 x_err_stack IN OUT VARCHAR2,
1921 x_err_code IN OUT NUMBER)
1922 IS
1923
1924 -- Define Cursor for selecting budget types
1925
1926 CURSOR sel_budget_types IS
1927 SELECT
1928 BUDGET_TYPE_CODE,
1929 BUDGET_TYPE,
1930 USER_COL1,
1931 USER_COL2,
1932 USER_COL3,
1933 USER_COL4,
1934 USER_COL5,
1935 USER_COL6,
1936 USER_COL7,
1937 USER_COL8,
1938 USER_COL9,
1939 USER_COL10,
1940 ADW_NOTIFY_FLAG
1941 FROM
1942 PA_ADW_BGT_TYPES_V
1943 WHERE
1944 ADW_NOTIFY_FLAG = 'Y';
1945
1946 -- define procedure variables
1947
1948 budget_types_r sel_budget_types%ROWTYPE;
1949
1950 x_old_err_stack VARCHAR2(1024);
1951
1952 BEGIN
1953 x_err_code := 0;
1954 x_err_stage := 'Collecting Budget Types Dimension Table';
1955 x_old_err_stack := x_err_stack;
1956 x_err_stack := x_err_stack || '-> get_dim_budget_types';
1957
1958 pa_debug.debug(x_err_stage);
1959
1960 -- Process all budget types
1961
1962 FOR budget_types_r IN sel_budget_types LOOP
1963
1964 -- First Try to Update the Row in the Interface Table
1965
1966 UPDATE
1967 PA_BGT_TYPES_IT
1968 SET
1969 LAST_UPDATE_DATE = TRUNC(SYSDATE),
1970 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1971 CREATION_DATE = TRUNC(SYSDATE),
1972 CREATED_BY = X_CREATED_BY,
1973 BUDGET_TYPE = BUDGET_TYPES_R.BUDGET_TYPE,
1974 USER_COL1 = BUDGET_TYPES_R.USER_COL1,
1975 USER_COL2 = BUDGET_TYPES_R.USER_COL2,
1976 USER_COL3 = BUDGET_TYPES_R.USER_COL3,
1977 USER_COL4 = BUDGET_TYPES_R.USER_COL4,
1978 USER_COL5 = BUDGET_TYPES_R.USER_COL5,
1979 USER_COL6 = BUDGET_TYPES_R.USER_COL6,
1980 USER_COL7 = BUDGET_TYPES_R.USER_COL7,
1981 USER_COL8 = BUDGET_TYPES_R.USER_COL8,
1982 USER_COL9 = BUDGET_TYPES_R.USER_COL9,
1983 USER_COL10 = BUDGET_TYPES_R.USER_COL10,
1984 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1985 REQUEST_ID = X_REQUEST_ID,
1986 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1987 PROGRAM_ID = X_PROGRAM_ID,
1988 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
1989 STATUS_CODE = 'P'
1990 WHERE
1991 BUDGET_TYPE_CODE = BUDGET_TYPES_R.BUDGET_TYPE_CODE;
1992
1993 -- Check If Any row was updated
1994
1995 IF (SQL%ROWCOUNT = 0) THEN
1996 -- No row was updated, So Insert a new row into the interface table
1997 INSERT INTO PA_BGT_TYPES_IT
1998 (
1999 BUDGET_TYPE_CODE,
2000 LAST_UPDATE_DATE,
2001 LAST_UPDATED_BY,
2002 CREATION_DATE,
2003 CREATED_BY,
2004 BUDGET_TYPE,
2005 USER_COL1,
2006 USER_COL2,
2007 USER_COL3,
2008 USER_COL4,
2009 USER_COL5,
2010 USER_COL6,
2011 USER_COL7,
2012 USER_COL8,
2013 USER_COL9,
2014 USER_COL10,
2015 LAST_UPDATE_LOGIN,
2016 REQUEST_ID,
2017 PROGRAM_APPLICATION_ID,
2018 PROGRAM_ID,
2019 PROGRAM_UPDATE_DATE,
2020 STATUS_CODE
2021 )
2022 VALUES
2023 (
2024 BUDGET_TYPES_R.BUDGET_TYPE_CODE,
2025 TRUNC(SYSDATE),
2026 X_LAST_UPDATED_BY,
2027 TRUNC(SYSDATE),
2028 X_CREATED_BY,
2029 BUDGET_TYPES_R.BUDGET_TYPE,
2030 BUDGET_TYPES_R.USER_COL1,
2031 BUDGET_TYPES_R.USER_COL2,
2032 BUDGET_TYPES_R.USER_COL3,
2033 BUDGET_TYPES_R.USER_COL4,
2034 BUDGET_TYPES_R.USER_COL5,
2035 BUDGET_TYPES_R.USER_COL6,
2036 BUDGET_TYPES_R.USER_COL7,
2037 BUDGET_TYPES_R.USER_COL8,
2038 BUDGET_TYPES_R.USER_COL9,
2039 BUDGET_TYPES_R.USER_COL10,
2040 X_LAST_UPDATE_LOGIN,
2041 X_REQUEST_ID,
2042 X_PROGRAM_APPLICATION_ID,
2043 X_PROGRAM_ID,
2044 TRUNC(SYSDATE),
2045 'P'
2046 );
2047
2048 END IF; -- IF ( SQL%ROWCOUNT = 0 )
2049
2050 -- Mark the project types as transferred to Interface table
2051
2052 UPDATE
2053 PA_BUDGET_TYPES
2054 SET
2055 ADW_NOTIFY_FLAG = 'N'
2056 WHERE
2057 BUDGET_TYPE_CODE = BUDGET_TYPES_R.BUDGET_TYPE_CODE;
2058
2059 END LOOP; -- FOR budget_types_r IN sel_budget_types
2060
2061 x_err_stack := x_old_err_stack;
2062
2063 pa_debug.debug('Completed ' || x_err_stage);
2064
2065 EXCEPTION
2066 WHEN OTHERS THEN
2067 x_err_code := SQLCODE;
2068 RAISE;
2069 END get_dim_budget_types;
2070
2071 -- Procedure to collect pa periods dimension
2072
2073 PROCEDURE get_dim_periods
2074 ( x_err_stage IN OUT VARCHAR2,
2075 x_err_stack IN OUT VARCHAR2,
2076 x_err_code IN OUT NUMBER)
2077 IS
2078
2079 -- Define Cursor for selecting periods
2080
2081 CURSOR sel_periods IS
2082 SELECT
2083 PA_PERIOD_KEY,
2084 PA_PERIOD,
2085 PA_PERIOD_START_DATE,
2086 PA_PERIOD_END_DATE,
2087 PA_PERIOD_END_DATE - PA_PERIOD_START_DATE + 1 TIMESPAN,
2088 GL_PERIOD,
2089 FINANCIAL_QUARTER,
2090 FINANCIAL_YEAR,
2091 ALL_FINANCIAL_YEARS,
2092 USER_COL1,
2093 USER_COL2,
2094 USER_COL3,
2095 USER_COL4,
2096 USER_COL5,
2097 USER_COL6,
2098 USER_COL7,
2099 USER_COL8,
2100 USER_COL9,
2101 USER_COL10
2102 FROM
2103 PA_ADW_PERIODS_V;
2104
2105 -- Cursor for selecting GL Periods
2106
2107 CURSOR gl_periods IS
2108 SELECT DISTINCT
2109 GL_PERIOD,
2110 GL_PERIOD_START_DATE,
2111 GL_PERIOD_END_DATE,
2112 GL_PERIOD_END_DATE - GL_PERIOD_START_DATE + 1 TIMESPAN,
2113 FINANCIAL_QUARTER
2114 FROM
2115 PA_ADW_PERIODS_V;
2116
2117 -- Cursor for selecting Financial Quarters
2118
2119 CURSOR fin_qtr IS
2120 SELECT DISTINCT
2121 FINANCIAL_QUARTER,
2122 FINANCIAL_YEAR
2123 FROM
2124 PA_ADW_PERIODS_V;
2125
2126 -- Cursor for selecting Financial Years
2127
2128 CURSOR fin_year IS
2129 SELECT DISTINCT
2130 FINANCIAL_YEAR,
2131 ALL_FINANCIAL_YEARS
2132 FROM
2133 PA_ADW_PERIODS_V;
2134
2135 -- define procedure variables
2136
2137 periods_r sel_periods%ROWTYPE;
2138 periods_gl gl_periods%ROWTYPE;
2139 fin_qtr_r fin_qtr%ROWTYPE;
2140 fin_year_r fin_year%ROWTYPE;
2141 x_count number;
2142 x_start_date date;
2143 x_end_date date;
2144 x_timespan number;
2145
2146 x_old_err_stack VARCHAR2(1024);
2147
2148 BEGIN
2149 x_err_code := 0;
2150 x_err_stage := 'Collecting PA period Dimension Table';
2151 x_old_err_stack := x_err_stack;
2152 x_err_stack := x_err_stack || '-> get_dim_periods';
2153
2154 pa_debug.debug(x_err_stage);
2155
2156 -- Process all PA periods
2157
2158 FOR periods_r IN sel_periods LOOP
2159
2160 -- First Try to Update the Row in the Interface Table
2161
2162 UPDATE
2163 PA_PERIODS_IT
2164 SET
2165 PA_PERIOD = PERIODS_R.PA_PERIOD,
2166 START_DATE = PERIODS_R.PA_PERIOD_START_DATE,
2167 END_DATE = PERIODS_R.PA_PERIOD_END_DATE,
2168 TIMESPAN = PERIODS_R.TIMESPAN,
2169 GL_PERIOD = PERIODS_R.GL_PERIOD,
2170 FINANCIAL_QUARTER = PERIODS_R.FINANCIAL_QUARTER,
2171 FINANCIAL_YEAR = PERIODS_R.FINANCIAL_YEAR,
2172 ALL_FINANCIAL_YEARS = PERIODS_R.ALL_FINANCIAL_YEARS,
2173 LAST_UPDATE_DATE = TRUNC(SYSDATE),
2174 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2175 CREATION_DATE = TRUNC(SYSDATE),
2176 CREATED_BY = X_CREATED_BY,
2177 USER_COL1 = PERIODS_R.USER_COL1,
2178 USER_COL2 = PERIODS_R.USER_COL2,
2179 USER_COL3 = PERIODS_R.USER_COL3,
2180 USER_COL4 = PERIODS_R.USER_COL4,
2181 USER_COL5 = PERIODS_R.USER_COL5,
2182 USER_COL6 = PERIODS_R.USER_COL6,
2183 USER_COL7 = PERIODS_R.USER_COL7,
2184 USER_COL8 = PERIODS_R.USER_COL8,
2185 USER_COL9 = PERIODS_R.USER_COL9,
2186 USER_COL10 = PERIODS_R.USER_COL10,
2187 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2188 REQUEST_ID = X_REQUEST_ID,
2189 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2190 PROGRAM_ID = X_PROGRAM_ID,
2191 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
2192 STATUS_CODE = 'P'
2193 WHERE
2194 PA_PERIOD_KEY = PERIODS_R.PA_PERIOD_KEY;
2195
2196 -- Check If Any row was updated
2197
2198 IF (SQL%ROWCOUNT = 0) THEN
2199 -- No row was updated, So Insert a new row into the interface table
2200 INSERT INTO PA_PERIODS_IT
2201 (
2202 PA_PERIOD_KEY,
2203 PA_PERIOD,
2204 START_DATE,
2205 END_DATE,
2206 TIMESPAN,
2207 GL_PERIOD,
2208 FINANCIAL_QUARTER,
2209 FINANCIAL_YEAR,
2210 ALL_FINANCIAL_YEARS,
2211 LAST_UPDATE_DATE,
2212 LAST_UPDATED_BY,
2213 CREATION_DATE,
2214 CREATED_BY,
2215 USER_COL1,
2216 USER_COL2,
2217 USER_COL3,
2218 USER_COL4,
2219 USER_COL5,
2220 USER_COL6,
2221 USER_COL7,
2222 USER_COL8,
2223 USER_COL9,
2224 USER_COL10,
2225 LAST_UPDATE_LOGIN,
2226 REQUEST_ID,
2227 PROGRAM_APPLICATION_ID,
2228 PROGRAM_ID,
2229 PROGRAM_UPDATE_DATE,
2230 STATUS_CODE
2231 )
2232 VALUES
2233 (
2234 PERIODS_R.PA_PERIOD_KEY,
2235 PERIODS_R.PA_PERIOD,
2236 PERIODS_R.PA_PERIOD_START_DATE,
2237 PERIODS_R.PA_PERIOD_END_DATE,
2238 PERIODS_R.TIMESPAN,
2239 PERIODS_R.GL_PERIOD,
2240 PERIODS_R.FINANCIAL_QUARTER,
2241 PERIODS_R.FINANCIAL_YEAR,
2242 PERIODS_R.ALL_FINANCIAL_YEARS,
2243 TRUNC(SYSDATE),
2244 X_LAST_UPDATED_BY,
2245 TRUNC(SYSDATE),
2246 X_CREATED_BY,
2247 PERIODS_R.USER_COL1,
2248 PERIODS_R.USER_COL2,
2249 PERIODS_R.USER_COL3,
2250 PERIODS_R.USER_COL4,
2251 PERIODS_R.USER_COL5,
2252 PERIODS_R.USER_COL6,
2253 PERIODS_R.USER_COL7,
2254 PERIODS_R.USER_COL8,
2255 PERIODS_R.USER_COL9,
2256 PERIODS_R.USER_COL10,
2257 X_LAST_UPDATE_LOGIN,
2258 X_REQUEST_ID,
2259 X_PROGRAM_APPLICATION_ID,
2260 X_PROGRAM_ID,
2261 TRUNC(SYSDATE),
2262 'P'
2263 );
2264
2265 END IF; -- IF ( SQL%ROWCOUNT = 0 )
2266
2267 END LOOP; -- FOR periods_r IN sel_periods
2268
2269 FOR periods_gl IN gl_periods LOOP
2270 UPDATE
2271 PA_GL_PERIODS_IT
2272 SET
2273 START_DATE = PERIODS_GL.GL_PERIOD_START_DATE,
2274 END_DATE = PERIODS_GL.GL_PERIOD_END_DATE,
2275 TIMESPAN = PERIODS_GL.TIMESPAN,
2276 FINANCIAL_QUARTER = PERIODS_GL.FINANCIAL_QUARTER,
2277 LAST_UPDATE_DATE = TRUNC(SYSDATE),
2278 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2279 CREATION_DATE = TRUNC(SYSDATE),
2280 CREATED_BY = X_CREATED_BY,
2281 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2282 REQUEST_ID = X_REQUEST_ID,
2283 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2284 PROGRAM_ID = X_PROGRAM_ID,
2285 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
2286 STATUS_CODE = 'P'
2287 WHERE
2288 GL_PERIOD = PERIODS_GL.GL_PERIOD;
2289
2290 IF (SQL%ROWCOUNT = 0) THEN
2291 -- No row was updated, So Insert a new row into the interface table
2292 INSERT INTO PA_GL_PERIODS_IT
2293 (
2294 GL_PERIOD,
2295 START_DATE,
2296 END_DATE,
2297 TIMESPAN,
2298 FINANCIAL_QUARTER,
2299 LAST_UPDATE_DATE,
2300 LAST_UPDATED_BY,
2301 CREATION_DATE,
2302 CREATED_BY,
2303 LAST_UPDATE_LOGIN,
2304 REQUEST_ID,
2305 PROGRAM_APPLICATION_ID,
2306 PROGRAM_ID,
2307 PROGRAM_UPDATE_DATE,
2308 STATUS_CODE
2309 )
2310 VALUES
2311 (
2312 PERIODS_GL.GL_PERIOD,
2313 PERIODS_GL.GL_PERIOD_START_DATE,
2314 PERIODS_GL.GL_PERIOD_END_DATE,
2315 PERIODS_GL.TIMESPAN,
2316 PERIODS_GL.FINANCIAL_QUARTER,
2317 TRUNC(SYSDATE),
2318 X_LAST_UPDATED_BY,
2319 TRUNC(SYSDATE),
2320 X_CREATED_BY,
2321 X_LAST_UPDATE_LOGIN,
2322 X_REQUEST_ID,
2323 X_PROGRAM_APPLICATION_ID,
2324 X_PROGRAM_ID,
2325 TRUNC(SYSDATE),
2326 'P'
2327 );
2328 END IF ;
2329 END LOOP; -- For periods_gl in gl_periods
2330
2331 FOR fin_qtr_r IN fin_qtr LOOP
2332
2333 SELECT
2334 MIN(GL_PERIOD_START_DATE),
2335 MAX(GL_PERIOD_END_DATE)
2336 INTO
2337 x_start_date,
2338 x_end_date
2339 FROM
2340 PA_ADW_PERIODS_V
2341 WHERE
2342 FINANCIAL_QUARTER = fin_qtr_r.FINANCIAL_QUARTER ;
2343
2344 x_timespan := x_end_date - x_start_date + 1 ;
2345
2346 UPDATE
2347 PA_FINANCIAL_QTRS_IT
2348 SET
2349 START_DATE = x_start_date,
2350 END_DATE = x_end_date ,
2351 TIMESPAN = x_timespan,
2352 FINANCIAL_YEAR = fin_qtr_r.FINANCIAL_YEAR,
2353 LAST_UPDATE_DATE = TRUNC(SYSDATE),
2354 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2355 CREATION_DATE = TRUNC(SYSDATE),
2356 CREATED_BY = X_CREATED_BY,
2357 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2358 REQUEST_ID = X_REQUEST_ID,
2359 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2360 PROGRAM_ID = X_PROGRAM_ID,
2361 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
2362 STATUS_CODE = 'P'
2363 WHERE
2364 FINANCIAL_QUARTER = fin_qtr_r.FINANCIAL_QUARTER;
2365
2366 IF (SQL%ROWCOUNT = 0) THEN
2367 -- No row was updated, So Insert a new row into the interface table
2368 Insert Into PA_FINANCIAL_QTRS_IT
2369 (
2370 FINANCIAL_QUARTER,
2371 START_DATE,
2372 END_DATE,
2373 TIMESPAN,
2374 FINANCIAL_YEAR,
2375 LAST_UPDATE_DATE,
2376 LAST_UPDATED_BY,
2377 CREATION_DATE,
2378 CREATED_BY,
2379 LAST_UPDATE_LOGIN,
2380 REQUEST_ID,
2381 PROGRAM_APPLICATION_ID,
2382 PROGRAM_ID,
2383 PROGRAM_UPDATE_DATE,
2384 STATUS_CODE
2385 )
2386 VALUES
2387 (
2388 fin_qtr_r.FINANCIAL_QUARTER,
2389 x_start_date,
2390 x_end_date,
2391 x_timespan,
2392 fin_qtr_r.FINANCIAL_YEAR,
2393 TRUNC(SYSDATE),
2394 X_LAST_UPDATED_BY,
2395 TRUNC(SYSDATE),
2396 X_CREATED_BY,
2397 X_LAST_UPDATE_LOGIN,
2398 X_REQUEST_ID,
2399 X_PROGRAM_APPLICATION_ID,
2400 X_PROGRAM_ID,
2401 TRUNC(SYSDATE),
2402 'P'
2403 );
2404 END IF;
2405 End LOOP; -- For fin_qtr_r in fin_qtr
2406
2407 FOR fin_year_r IN fin_year LOOP
2408
2409
2410 SELECT
2411 MIN(GL_PERIOD_START_DATE),
2412 MAX(GL_PERIOD_END_DATE)
2413 INTO
2414 x_start_date,
2415 x_end_date
2416 FROM
2417 PA_ADW_PERIODS_V
2418 WHERE
2419 FINANCIAL_YEAR = fin_year_r.FINANCIAL_YEAR ;
2420
2421 x_timespan := x_end_date - x_start_date + 1 ;
2422
2423 UPDATE
2424 PA_FINANCIAL_YRS_IT
2425 SET
2426 START_DATE = x_start_date,
2427 END_DATE = x_end_date ,
2428 TIMESPAN = x_timespan,
2429 ALL_FINANCIAL_YEARS = fin_year_r.ALL_FINANCIAL_YEARS,
2430 LAST_UPDATE_DATE = TRUNC(SYSDATE),
2431 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2432 CREATION_DATE = TRUNC(SYSDATE),
2433 CREATED_BY = X_CREATED_BY,
2434 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2435 REQUEST_ID = X_REQUEST_ID,
2436 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2437 PROGRAM_ID = X_PROGRAM_ID,
2438 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
2439 STATUS_CODE = 'P'
2440 WHERE
2441 FINANCIAL_YEAR = fin_year_r.FINANCIAL_YEAR;
2442
2443 IF (SQL%ROWCOUNT = 0) THEN
2444 -- No row was updated, So Insert a new row into the interface table
2445 Insert Into PA_FINANCIAL_YRS_IT
2446 (
2447 FINANCIAL_YEAR,
2448 START_DATE,
2449 END_DATE,
2450 TIMESPAN,
2451 ALL_FINANCIAL_YEARS,
2452 LAST_UPDATE_DATE,
2453 LAST_UPDATED_BY,
2454 CREATION_DATE,
2455 CREATED_BY,
2456 LAST_UPDATE_LOGIN,
2457 REQUEST_ID,
2458 PROGRAM_APPLICATION_ID,
2459 PROGRAM_ID,
2460 PROGRAM_UPDATE_DATE,
2461 STATUS_CODE
2462 )
2463 Values
2464 (
2465 fin_year_r.FINANCIAL_YEAR,
2466 x_start_date,
2467 x_end_date,
2468 x_timespan,
2469 fin_year_r.ALL_FINANCIAL_YEARS,
2470 TRUNC(SYSDATE),
2471 X_LAST_UPDATED_BY,
2472 TRUNC(SYSDATE),
2473 X_CREATED_BY,
2474 X_LAST_UPDATE_LOGIN,
2475 X_REQUEST_ID,
2476 X_PROGRAM_APPLICATION_ID,
2477 X_PROGRAM_ID,
2478 TRUNC(SYSDATE),
2479 'P'
2480 );
2481 END IF;
2482 END LOOP; -- For fin_year_r in fin_year
2483
2484 SELECT
2485 COUNT(*)
2486 INTO x_count
2487 FROM
2488 PA_ALL_FINANCIAL_YRS_IT;
2489
2490 IF x_count = 0
2491 THEN
2492 INSERT INTO PA_ALL_FINANCIAL_YRS_IT
2493 (
2494 ALL_FINANCIAL_YEARS,
2495 LAST_UPDATE_DATE,
2496 LAST_UPDATED_BY,
2497 CREATION_DATE,
2498 CREATED_BY,
2499 LAST_UPDATE_LOGIN,
2500 REQUEST_ID,
2501 PROGRAM_APPLICATION_ID,
2502 PROGRAM_ID,
2503 PROGRAM_UPDATE_DATE,
2504 STATUS_CODE
2505 )
2506 SELECT DISTINCT
2507 ALL_FINANCIAL_YEARS,
2508 TRUNC(SYSDATE),
2509 X_LAST_UPDATED_BY,
2510 TRUNC(SYSDATE),
2511 X_CREATED_BY,
2512 X_LAST_UPDATE_LOGIN,
2513 X_REQUEST_ID,
2514 X_PROGRAM_APPLICATION_ID,
2515 X_PROGRAM_ID,
2516 TRUNC(SYSDATE),
2517 'P'
2518 FROM
2519 PA_ADW_PERIODS_V;
2520 END IF;
2521
2522 x_err_stack := x_old_err_stack;
2523
2524 pa_debug.debug('Completed ' || x_err_stage);
2525
2526 EXCEPTION
2527 WHEN OTHERS THEN
2528 x_err_code := SQLCODE;
2529 RAISE;
2530 END get_dim_periods;
2531
2532 -- Procedure to collect service types dimension
2533 -- We are not maintaining any flag on service types, so this dimension
2534 -- is always refreshed
2535
2536 PROCEDURE get_dim_service_types
2537 ( x_err_stage IN OUT VARCHAR2,
2538 x_err_stack IN OUT VARCHAR2,
2539 x_err_code IN OUT NUMBER)
2540 IS
2541
2542 -- Define Cursor for selecting service types
2543
2544 CURSOR sel_service_types IS
2545 SELECT
2546 SERVICE_TYPE_CODE,
2547 SERVICE_TYPE,
2548 ALL_SERVICE_TYPES,
2549 USER_COL1,
2550 USER_COL2,
2551 USER_COL3,
2552 USER_COL4,
2553 USER_COL5,
2554 USER_COL6,
2555 USER_COL7,
2556 USER_COL8,
2557 USER_COL9,
2558 USER_COL10
2559 FROM
2560 PA_ADW_SRVC_TYPES_V;
2561
2562 -- define procedure variables
2563
2564 service_types_r sel_service_types%ROWTYPE;
2565
2566 x_old_err_stack VARCHAR2(1024);
2567 x_count number;
2568
2569 BEGIN
2570 x_err_code := 0;
2571 x_err_stage := 'Collecting Service Types Dimension Table';
2572 x_old_err_stack := x_err_stack;
2573 x_err_stack := x_err_stack || '-> get_dim_service_types';
2574
2575 pa_debug.debug(x_err_stage);
2576
2577 -- Process all service types
2578
2579 FOR service_types_r IN sel_service_types LOOP
2580
2581 -- First Try to Update the Row in the Interface Table
2582
2583 UPDATE
2584 PA_SRVC_TYPES_IT
2585 SET
2586 ALL_SERVICE_TYPES = SERVICE_TYPES_R.ALL_SERVICE_TYPES,
2587 LAST_UPDATE_DATE = TRUNC(SYSDATE),
2588 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2589 CREATION_DATE = TRUNC(SYSDATE),
2590 CREATED_BY = X_CREATED_BY,
2591 SERVICE_TYPE = SERVICE_TYPES_R.SERVICE_TYPE,
2592 USER_COL1 = SERVICE_TYPES_R.USER_COL1,
2593 USER_COL2 = SERVICE_TYPES_R.USER_COL2,
2594 USER_COL3 = SERVICE_TYPES_R.USER_COL3,
2595 USER_COL4 = SERVICE_TYPES_R.USER_COL4,
2596 USER_COL5 = SERVICE_TYPES_R.USER_COL5,
2597 USER_COL6 = SERVICE_TYPES_R.USER_COL6,
2598 USER_COL7 = SERVICE_TYPES_R.USER_COL7,
2599 USER_COL8 = SERVICE_TYPES_R.USER_COL8,
2600 USER_COL9 = SERVICE_TYPES_R.USER_COL9,
2601 USER_COL10 = SERVICE_TYPES_R.USER_COL10,
2602 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2603 REQUEST_ID = X_REQUEST_ID,
2604 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2605 PROGRAM_ID = X_PROGRAM_ID,
2606 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
2607 STATUS_CODE = 'P'
2608 WHERE
2609 SERVICE_TYPE_CODE = SERVICE_TYPES_R.SERVICE_TYPE_CODE;
2610
2611 -- Check If Any row was updated
2612
2613 IF (SQL%ROWCOUNT = 0) THEN
2614 -- No row was updated, So Insert a new row into the interface table
2615 INSERT INTO PA_SRVC_TYPES_IT
2616 (
2617 SERVICE_TYPE_CODE,
2618 LAST_UPDATE_DATE,
2619 LAST_UPDATED_BY,
2620 CREATION_DATE,
2621 CREATED_BY,
2622 SERVICE_TYPE,
2623 ALL_SERVICE_TYPES,
2624 USER_COL1,
2625 USER_COL2,
2626 USER_COL3,
2627 USER_COL4,
2628 USER_COL5,
2629 USER_COL6,
2630 USER_COL7,
2631 USER_COL8,
2632 USER_COL9,
2633 USER_COL10,
2634 LAST_UPDATE_LOGIN,
2635 REQUEST_ID,
2636 PROGRAM_APPLICATION_ID,
2637 PROGRAM_ID,
2638 PROGRAM_UPDATE_DATE,
2639 STATUS_CODE
2640 )
2641 VALUES
2642 (
2643 SERVICE_TYPES_R.SERVICE_TYPE_CODE,
2644 TRUNC(SYSDATE),
2645 X_LAST_UPDATED_BY,
2646 TRUNC(SYSDATE),
2647 X_CREATED_BY,
2648 SERVICE_TYPES_R.SERVICE_TYPE,
2649 SERVICE_TYPES_R.ALL_SERVICE_TYPES,
2650 SERVICE_TYPES_R.USER_COL1,
2651 SERVICE_TYPES_R.USER_COL2,
2652 SERVICE_TYPES_R.USER_COL3,
2653 SERVICE_TYPES_R.USER_COL4,
2654 SERVICE_TYPES_R.USER_COL5,
2655 SERVICE_TYPES_R.USER_COL6,
2656 SERVICE_TYPES_R.USER_COL7,
2657 SERVICE_TYPES_R.USER_COL8,
2658 SERVICE_TYPES_R.USER_COL9,
2659 SERVICE_TYPES_R.USER_COL10,
2660 X_LAST_UPDATE_LOGIN,
2661 X_REQUEST_ID,
2662 X_PROGRAM_APPLICATION_ID,
2663 X_PROGRAM_ID,
2664 TRUNC(SYSDATE),
2665 'P'
2666 );
2667
2668 END IF; -- IF ( SQL%ROWCOUNT = 0 )
2669
2670 END LOOP; -- FOR service_types_r IN sel_service_types
2671
2672 SELECT count(*)
2673 into x_count
2674 FROM PA_ALL_SRVC_TYPES_IT;
2675
2676 IF x_count = 0
2677 THEN
2678 INSERT INTO PA_ALL_SRVC_TYPES_IT
2679 (
2680 ALL_SERVICE_TYPES,
2681 LAST_UPDATE_DATE,
2682 LAST_UPDATED_BY,
2683 CREATION_DATE,
2684 CREATED_BY,
2685 LAST_UPDATE_LOGIN,
2686 REQUEST_ID,
2687 PROGRAM_APPLICATION_ID,
2688 PROGRAM_ID,
2689 PROGRAM_UPDATE_DATE,
2690 STATUS_CODE
2691 )
2692 SELECT DISTINCT
2693 ALL_SERVICE_TYPES,
2694 TRUNC(SYSDATE),
2695 X_LAST_UPDATED_BY,
2696 TRUNC(SYSDATE),
2697 X_CREATED_BY,
2698 X_LAST_UPDATE_LOGIN,
2699 X_REQUEST_ID,
2700 X_PROGRAM_APPLICATION_ID,
2701 X_PROGRAM_ID,
2702 TRUNC(SYSDATE),
2703 'P'
2704 FROM PA_ADW_SRVC_TYPES_V;
2705 END IF;
2706
2707 x_err_stack := x_old_err_stack;
2708
2709 pa_debug.debug('Completed ' || x_err_stage);
2710
2711 EXCEPTION
2712 WHEN OTHERS THEN
2713 x_err_code := SQLCODE;
2714 RAISE;
2715 END get_dim_service_types;
2716
2717 -- Procedure to collect organization dimension
2718 -- We are not maintaining any flag on organizations, so this dimension
2719 -- is always refreshed
2720
2721 PROCEDURE get_dim_organizations
2722 ( x_err_stage IN OUT VARCHAR2,
2723 x_err_stack IN OUT VARCHAR2,
2724 x_err_code IN OUT NUMBER)
2725 IS
2726
2727 -- Define Cursor for selecting organizations
2728
2729 CURSOR sel_organizations IS
2730 SELECT
2731 ORGANIZATION_ID,
2732 ORGANIZATION,
2733 BUSINESS_GROUP,
2734 USER_COL1,
2735 USER_COL2,
2736 USER_COL3,
2737 USER_COL4,
2738 USER_COL5,
2739 USER_COL6,
2740 USER_COL7,
2741 USER_COL8,
2742 USER_COL9,
2743 USER_COL10
2744 FROM
2745 PA_ADW_ORGS_V;
2746
2747 CURSOR project_bsns_grp IS
2748 SELECT distinct
2749 BUSINESS_GROUP
2750 FROM
2751 PA_ADW_ORGS_V;
2752
2753 CURSOR exp_bsns_grp IS
2754 SELECT distinct
2755 BUSINESS_GROUP
2756 FROM
2757 PA_ADW_ORGS_V;
2758
2759 CURSOR project_org IS
2760 SELECT
2761 ORGANIZATION_ID,
2762 ORGANIZATION,
2763 BUSINESS_GROUP,
2764 USER_COL1,
2765 USER_COL2,
2766 USER_COL3,
2767 USER_COL4,
2768 USER_COL5,
2769 USER_COL6,
2770 USER_COL7,
2771 USER_COL8,
2772 USER_COL9,
2773 USER_COL10
2774 FROM
2775 PA_ADW_ORGS_V;
2776
2777 CURSOR exp_org IS
2778 SELECT
2779 ORGANIZATION_ID,
2780 ORGANIZATION,
2781 BUSINESS_GROUP,
2782 USER_COL1,
2783 USER_COL2,
2784 USER_COL3,
2785 USER_COL4,
2786 USER_COL5,
2787 USER_COL6,
2788 USER_COL7,
2789 USER_COL8,
2790 USER_COL9,
2791 USER_COL10
2792 FROM
2793 PA_ADW_ORGS_V;
2794
2795 CURSOR sel_operating_units IS
2796 SELECT
2797 ORGANIZATION_ID,
2798 ORGANIZATION,
2799 LEGAL_ENTITY,
2800 SET_OF_BOOK,
2801 USER_COL1,
2802 USER_COL2,
2803 USER_COL3,
2804 USER_COL4,
2805 USER_COL5,
2806 USER_COL6,
2807 USER_COL7,
2808 USER_COL8,
2809 USER_COL9,
2810 USER_COL10
2811 FROM
2812 PA_ADW_OPER_UNITS_V;
2813
2814 CURSOR legal_entity IS
2815 SELECT distinct
2816 LEGAL_ENTITY,
2817 SET_OF_BOOK
2818 FROM
2819 PA_ADW_OPER_UNITS_V;
2820
2821 CURSOR set_of_book IS
2822 SELECT distinct
2823 SET_OF_BOOK
2824 FROM
2825 PA_ADW_OPER_UNITS_V;
2826
2827 -- define procedure variables
2828
2829 organizations_r sel_organizations%ROWTYPE;
2830 operating_units_r sel_operating_units%ROWTYPE;
2831 set_of_book_r set_of_book%ROWTYPE;
2832 legal_entity_r legal_entity%ROWTYPE;
2833 project_org_r project_org%ROWTYPE;
2834 exp_org_r exp_org%ROWTYPE;
2835 project_bsns_grp_r project_bsns_grp%ROWTYPE;
2836 exp_bsns_grp_r exp_bsns_grp%ROWTYPE;
2837
2838 x_old_err_stack VARCHAR2(1024);
2839
2840 BEGIN
2841 x_err_code := 0;
2842 x_err_stage := 'Collecting Organization/Operating Units Dimension Table';
2843 x_old_err_stack := x_err_stack;
2844 x_err_stack := x_err_stack || '-> get_dim_organizations';
2845
2846 pa_debug.debug(x_err_stage);
2847
2848 FOR project_org_r IN project_org LOOP
2849
2850 -- First Try to Update the Row in the Interface Table
2851
2852 UPDATE
2853 PA_PRJ_ORGS_IT
2854 SET
2855 LAST_UPDATE_DATE = TRUNC(SYSDATE),
2856 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2857 CREATION_DATE = TRUNC(SYSDATE),
2858 CREATED_BY = X_CREATED_BY,
2859 ORGANIZATION = project_org_r.ORGANIZATION,
2860 BUSINESS_GROUP = project_org_r.BUSINESS_GROUP,
2861 USER_COL1 = project_org_r.USER_COL1,
2862 USER_COL2 = project_org_r.USER_COL2,
2863 USER_COL3 = project_org_r.USER_COL3,
2864 USER_COL4 = project_org_r.USER_COL4,
2865 USER_COL5 = project_org_r.USER_COL5,
2866 USER_COL6 = project_org_r.USER_COL6,
2867 USER_COL7 = project_org_r.USER_COL7,
2868 USER_COL8 = project_org_r.USER_COL8,
2869 USER_COL9 = project_org_r.USER_COL9,
2870 USER_COL10 = project_org_r.USER_COL10,
2871 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2872 REQUEST_ID = X_REQUEST_ID,
2873 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2874 PROGRAM_ID = X_PROGRAM_ID,
2875 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
2876 STATUS_CODE = 'P'
2877 WHERE
2878 ORGANIZATION_ID = project_org_r.ORGANIZATION_ID;
2879
2880 -- Check If Any row was updated
2881
2882 IF (SQL%ROWCOUNT = 0) THEN
2883 -- No row was updated, So Insert a new row into the interface table
2884 INSERT INTO PA_PRJ_ORGS_IT
2885 (
2886 ORGANIZATION_ID,
2887 LAST_UPDATE_DATE,
2888 LAST_UPDATED_BY,
2889 CREATION_DATE,
2890 CREATED_BY,
2891 ORGANIZATION,
2892 BUSINESS_GROUP,
2893 USER_COL1,
2894 USER_COL2,
2895 USER_COL3,
2896 USER_COL4,
2897 USER_COL5,
2898 USER_COL6,
2899 USER_COL7,
2900 USER_COL8,
2901 USER_COL9,
2902 USER_COL10,
2903 LAST_UPDATE_LOGIN,
2904 REQUEST_ID,
2905 PROGRAM_APPLICATION_ID,
2906 PROGRAM_ID,
2907 PROGRAM_UPDATE_DATE,
2908 STATUS_CODE
2909 )
2910 VALUES
2911 (
2912 project_org_r.ORGANIZATION_ID,
2913 TRUNC(SYSDATE),
2914 X_LAST_UPDATED_BY,
2915 TRUNC(SYSDATE),
2916 X_CREATED_BY,
2917 project_org_r.ORGANIZATION,
2918 project_org_r.BUSINESS_GROUP,
2919 project_org_r.USER_COL1,
2920 project_org_r.USER_COL2,
2921 project_org_r.USER_COL3,
2922 project_org_r.USER_COL4,
2923 project_org_r.USER_COL5,
2924 project_org_r.USER_COL6,
2925 project_org_r.USER_COL7,
2926 project_org_r.USER_COL8,
2927 project_org_r.USER_COL9,
2928 project_org_r.USER_COL10,
2929 X_LAST_UPDATE_LOGIN,
2930 X_REQUEST_ID,
2931 X_PROGRAM_APPLICATION_ID,
2932 X_PROGRAM_ID,
2933 TRUNC(SYSDATE),
2934 'P'
2935 );
2936
2937 END IF; -- IF ( SQL%ROWCOUNT = 0 )
2938
2939 END LOOP; -- FOR project_org_r IN project_org
2940
2941 FOR project_bsns_grp_r IN project_bsns_grp LOOP
2942
2943 -- First Try to Update the Row in the Interface Table
2944
2945 UPDATE
2946 PA_PRJ_BUSINESS_GRPS_IT
2947 SET
2948 LAST_UPDATE_DATE = TRUNC(SYSDATE),
2949 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
2950 CREATION_DATE = TRUNC(SYSDATE),
2951 CREATED_BY = X_CREATED_BY,
2952 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
2953 REQUEST_ID = X_REQUEST_ID,
2954 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
2955 PROGRAM_ID = X_PROGRAM_ID,
2956 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
2957 STATUS_CODE = 'P'
2958 WHERE
2959 BUSINESS_GROUP = project_bsns_grp_r.BUSINESS_GROUP;
2960
2961 IF (SQL%ROWCOUNT = 0) THEN
2962 -- No row was updated, So Insert a new row into the interface table
2963 INSERT INTO PA_PRJ_BUSINESS_GRPS_IT
2964 (
2965 business_group,
2966 last_update_date,
2967 last_updated_by,
2968 creation_date,
2969 created_by,
2970 LAST_UPDATE_LOGIN,
2971 REQUEST_ID,
2972 PROGRAM_APPLICATION_ID,
2973 PROGRAM_ID,
2974 PROGRAM_UPDATE_DATE,
2975 STATUS_CODE
2976 )
2977 VALUES
2978 (
2979 project_bsns_grp_r.business_group,
2980 TRUNC(SYSDATE),
2981 X_LAST_UPDATED_BY,
2982 TRUNC(SYSDATE),
2983 X_CREATED_BY,
2984 X_LAST_UPDATE_LOGIN,
2985 X_REQUEST_ID,
2986 X_PROGRAM_APPLICATION_ID,
2987 X_PROGRAM_ID,
2988 TRUNC(SYSDATE),
2989 'P'
2990 );
2991 END IF;
2992 END LOOP;
2993
2994 -- Process all organizations
2995
2996 FOR organizations_r IN sel_organizations LOOP
2997
2998 -- First Try to Update the Row in the Interface Table
2999
3000 UPDATE
3001 PA_ORGS_IT
3002 SET
3003 LAST_UPDATE_DATE = TRUNC(SYSDATE),
3004 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
3005 CREATION_DATE = TRUNC(SYSDATE),
3006 CREATED_BY = X_CREATED_BY,
3007 ORGANIZATION = ORGANIZATIONS_R.ORGANIZATION,
3008 BUSINESS_GROUP = ORGANIZATIONS_R.BUSINESS_GROUP,
3009 USER_COL1 = ORGANIZATIONS_R.USER_COL1,
3010 USER_COL2 = ORGANIZATIONS_R.USER_COL2,
3011 USER_COL3 = ORGANIZATIONS_R.USER_COL3,
3012 USER_COL4 = ORGANIZATIONS_R.USER_COL4,
3013 USER_COL5 = ORGANIZATIONS_R.USER_COL5,
3014 USER_COL6 = ORGANIZATIONS_R.USER_COL6,
3015 USER_COL7 = ORGANIZATIONS_R.USER_COL7,
3016 USER_COL8 = ORGANIZATIONS_R.USER_COL8,
3017 USER_COL9 = ORGANIZATIONS_R.USER_COL9,
3018 USER_COL10 = ORGANIZATIONS_R.USER_COL10,
3019 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
3020 REQUEST_ID = X_REQUEST_ID,
3021 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
3022 PROGRAM_ID = X_PROGRAM_ID,
3023 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
3024 STATUS_CODE = 'P'
3025 WHERE
3026 ORGANIZATION_ID = ORGANIZATIONS_R.ORGANIZATION_ID;
3027
3028 -- Check If Any row was updated
3029
3030 IF (SQL%ROWCOUNT = 0) THEN
3031 -- No row was updated, So Insert a new row into the interface table
3032 INSERT INTO PA_ORGS_IT
3033 (
3034 ORGANIZATION_ID,
3035 LAST_UPDATE_DATE,
3036 LAST_UPDATED_BY,
3037 CREATION_DATE,
3038 CREATED_BY,
3039 ORGANIZATION,
3040 BUSINESS_GROUP,
3041 USER_COL1,
3042 USER_COL2,
3043 USER_COL3,
3044 USER_COL4,
3045 USER_COL5,
3046 USER_COL6,
3047 USER_COL7,
3048 USER_COL8,
3049 USER_COL9,
3050 USER_COL10,
3051 LAST_UPDATE_LOGIN,
3052 REQUEST_ID,
3053 PROGRAM_APPLICATION_ID,
3054 PROGRAM_ID,
3055 PROGRAM_UPDATE_DATE,
3056 STATUS_CODE
3057 )
3058 VALUES
3059 (
3060 ORGANIZATIONS_R.ORGANIZATION_ID,
3061 TRUNC(SYSDATE),
3062 X_LAST_UPDATED_BY,
3063 TRUNC(SYSDATE),
3064 X_CREATED_BY,
3065 ORGANIZATIONS_R.ORGANIZATION,
3066 ORGANIZATIONS_R.BUSINESS_GROUP,
3067 ORGANIZATIONS_R.USER_COL1,
3068 ORGANIZATIONS_R.USER_COL2,
3069 ORGANIZATIONS_R.USER_COL3,
3070 ORGANIZATIONS_R.USER_COL4,
3071 ORGANIZATIONS_R.USER_COL5,
3072 ORGANIZATIONS_R.USER_COL6,
3073 ORGANIZATIONS_R.USER_COL7,
3074 ORGANIZATIONS_R.USER_COL8,
3075 ORGANIZATIONS_R.USER_COL9,
3076 ORGANIZATIONS_R.USER_COL10,
3077 X_LAST_UPDATE_LOGIN,
3078 X_REQUEST_ID,
3079 X_PROGRAM_APPLICATION_ID,
3080 X_PROGRAM_ID,
3081 TRUNC(SYSDATE),
3082 'P'
3083 );
3084
3085 END IF; -- IF ( SQL%ROWCOUNT = 0 )
3086
3087 END LOOP; -- FOR organizations_r IN sel_organizations
3088
3089 FOR exp_org_r IN exp_org LOOP
3090
3091 -- First Try to Update the Row in the Interface Table
3092
3093 UPDATE
3094 PA_EXP_ORGS_IT
3095 SET
3096 LAST_UPDATE_DATE = TRUNC(SYSDATE),
3097 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
3098 CREATION_DATE = TRUNC(SYSDATE),
3099 CREATED_BY = X_CREATED_BY,
3100 ORGANIZATION = exp_org_r.ORGANIZATION,
3101 BUSINESS_GROUP = exp_org_r.BUSINESS_GROUP,
3102 USER_COL1 = exp_org_r.USER_COL1,
3103 USER_COL2 = exp_org_r.USER_COL2,
3104 USER_COL3 = exp_org_r.USER_COL3,
3105 USER_COL4 = exp_org_r.USER_COL4,
3106 USER_COL5 = exp_org_r.USER_COL5,
3107 USER_COL6 = exp_org_r.USER_COL6,
3108 USER_COL7 = exp_org_r.USER_COL7,
3109 USER_COL8 = exp_org_r.USER_COL8,
3110 USER_COL9 = exp_org_r.USER_COL9,
3111 USER_COL10 = exp_org_r.USER_COL10,
3112 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
3113 REQUEST_ID = X_REQUEST_ID,
3114 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
3115 PROGRAM_ID = X_PROGRAM_ID,
3116 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
3117 STATUS_CODE = 'P'
3118 WHERE
3119 ORGANIZATION_ID = exp_org_r.ORGANIZATION_ID;
3120
3121 -- Check If Any row was updated
3122
3123 IF (SQL%ROWCOUNT = 0) THEN
3124 -- No row was updated, So Insert a new row into the interface table
3125 INSERT INTO PA_EXP_ORGS_IT
3126 (
3127 ORGANIZATION_ID,
3128 LAST_UPDATE_DATE,
3129 LAST_UPDATED_BY,
3130 CREATION_DATE,
3131 CREATED_BY,
3132 ORGANIZATION,
3133 BUSINESS_GROUP,
3134 USER_COL1,
3135 USER_COL2,
3136 USER_COL3,
3137 USER_COL4,
3138 USER_COL5,
3139 USER_COL6,
3140 USER_COL7,
3141 USER_COL8,
3142 USER_COL9,
3143 USER_COL10,
3144 LAST_UPDATE_LOGIN,
3145 REQUEST_ID,
3146 PROGRAM_APPLICATION_ID,
3147 PROGRAM_ID,
3148 PROGRAM_UPDATE_DATE,
3149 STATUS_CODE
3150 )
3151 VALUES
3152 (
3153 exp_org_r.ORGANIZATION_ID,
3154 TRUNC(SYSDATE),
3155 X_LAST_UPDATED_BY,
3156 TRUNC(SYSDATE),
3157 X_CREATED_BY,
3158 exp_org_r.ORGANIZATION,
3159 exp_org_r.BUSINESS_GROUP,
3160 exp_org_r.USER_COL1,
3161 exp_org_r.USER_COL2,
3162 exp_org_r.USER_COL3,
3163 exp_org_r.USER_COL4,
3164 exp_org_r.USER_COL5,
3165 exp_org_r.USER_COL6,
3166 exp_org_r.USER_COL7,
3167 exp_org_r.USER_COL8,
3168 exp_org_r.USER_COL9,
3169 exp_org_r.USER_COL10,
3170 X_LAST_UPDATE_LOGIN,
3171 X_REQUEST_ID,
3172 X_PROGRAM_APPLICATION_ID,
3173 X_PROGRAM_ID,
3174 TRUNC(SYSDATE),
3175 'P'
3176 );
3177
3178 END IF; -- IF ( SQL%ROWCOUNT = 0 )
3179
3180 END LOOP; -- FOR exp_org_r IN exp_org
3181
3182 FOR exp_bsns_grp_r IN exp_bsns_grp LOOP
3183
3184 -- First Try to Update the Row in the Interface Table
3185
3186 UPDATE
3187 PA_EXP_BUSINESS_GRPS_IT
3188 SET
3189 LAST_UPDATE_DATE = TRUNC(SYSDATE),
3190 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
3191 CREATION_DATE = TRUNC(SYSDATE),
3192 CREATED_BY = X_CREATED_BY,
3193 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
3194 REQUEST_ID = X_REQUEST_ID,
3195 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
3196 PROGRAM_ID = X_PROGRAM_ID,
3197 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
3198 STATUS_CODE = 'P'
3199 WHERE
3200 BUSINESS_GROUP = exp_bsns_grp_r.BUSINESS_GROUP;
3201
3202 IF (SQL%ROWCOUNT = 0) THEN
3203 -- No row was updated, So Insert a new row into the interface table
3204 INSERT INTO PA_EXP_BUSINESS_GRPS_IT
3205 (
3206 BUSINESS_GROUP,
3207 LAST_UPDATE_DATE,
3208 LAST_UPDATED_BY,
3209 CREATION_DATE,
3210 CREATED_BY,
3211 LAST_UPDATE_LOGIN,
3212 REQUEST_ID,
3213 PROGRAM_APPLICATION_ID,
3214 PROGRAM_ID,
3215 PROGRAM_UPDATE_DATE,
3216 STATUS_CODE
3217 )
3218 VALUES
3219 (
3220 exp_bsns_grp_r.business_group,
3221 TRUNC(SYSDATE),
3222 X_LAST_UPDATED_BY,
3223 TRUNC(SYSDATE),
3224 X_CREATED_BY,
3225 X_LAST_UPDATE_LOGIN,
3226 X_REQUEST_ID,
3227 X_PROGRAM_APPLICATION_ID,
3228 X_PROGRAM_ID,
3229 TRUNC(SYSDATE),
3230 'P'
3231 );
3232 END IF;
3233 END LOOP;
3234
3235 -- Process all operating units
3236
3237 FOR operating_units_r IN sel_operating_units LOOP
3238
3239 -- First Try to Update the Row in the Interface Table
3240
3241 UPDATE
3242 PA_OPER_UNITS_IT
3243 SET
3244 LAST_UPDATE_DATE = TRUNC(SYSDATE),
3245 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
3246 CREATION_DATE = TRUNC(SYSDATE),
3247 CREATED_BY = X_CREATED_BY,
3248 ORGANIZATION = OPERATING_UNITS_R.ORGANIZATION,
3249 LEGAL_ENTITY = OPERATING_UNITS_R.LEGAL_ENTITY,
3250 SET_OF_BOOK = OPERATING_UNITS_R.SET_OF_BOOK,
3251 USER_COL1 = OPERATING_UNITS_R.USER_COL1,
3252 USER_COL2 = OPERATING_UNITS_R.USER_COL2,
3253 USER_COL3 = OPERATING_UNITS_R.USER_COL3,
3254 USER_COL4 = OPERATING_UNITS_R.USER_COL4,
3255 USER_COL5 = OPERATING_UNITS_R.USER_COL5,
3256 USER_COL6 = OPERATING_UNITS_R.USER_COL6,
3257 USER_COL7 = OPERATING_UNITS_R.USER_COL7,
3258 USER_COL8 = OPERATING_UNITS_R.USER_COL8,
3259 USER_COL9 = OPERATING_UNITS_R.USER_COL9,
3260 USER_COL10 = OPERATING_UNITS_R.USER_COL10,
3261 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
3262 REQUEST_ID = X_REQUEST_ID,
3263 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
3264 PROGRAM_ID = X_PROGRAM_ID,
3265 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
3266 STATUS_CODE = 'P'
3267 WHERE
3268 ORGANIZATION_ID = OPERATING_UNITS_R.ORGANIZATION_ID;
3269
3270 -- Check If Any row was updated
3271
3272 IF (SQL%ROWCOUNT = 0) THEN
3273 -- No row was updated, So Insert a new row into the interface table
3274 INSERT INTO PA_OPER_UNITS_IT
3275 (
3276 ORGANIZATION_ID,
3277 LAST_UPDATE_DATE,
3278 LAST_UPDATED_BY,
3279 CREATION_DATE,
3280 CREATED_BY,
3281 ORGANIZATION,
3282 LEGAL_ENTITY,
3283 SET_OF_BOOK,
3284 USER_COL1,
3285 USER_COL2,
3286 USER_COL3,
3287 USER_COL4,
3288 USER_COL5,
3289 USER_COL6,
3290 USER_COL7,
3291 USER_COL8,
3292 USER_COL9,
3293 USER_COL10,
3294 LAST_UPDATE_LOGIN,
3295 REQUEST_ID,
3296 PROGRAM_APPLICATION_ID,
3297 PROGRAM_ID,
3298 PROGRAM_UPDATE_DATE,
3299 STATUS_CODE
3300 )
3301 VALUES
3302 (
3303 OPERATING_UNITS_R.ORGANIZATION_ID,
3304 TRUNC(SYSDATE),
3305 X_LAST_UPDATED_BY,
3306 TRUNC(SYSDATE),
3307 X_CREATED_BY,
3308 OPERATING_UNITS_R.ORGANIZATION,
3309 OPERATING_UNITS_R.LEGAL_ENTITY,
3310 OPERATING_UNITS_R.SET_OF_BOOK,
3311 OPERATING_UNITS_R.USER_COL1,
3312 OPERATING_UNITS_R.USER_COL2,
3313 OPERATING_UNITS_R.USER_COL3,
3314 OPERATING_UNITS_R.USER_COL4,
3315 OPERATING_UNITS_R.USER_COL5,
3316 OPERATING_UNITS_R.USER_COL6,
3317 OPERATING_UNITS_R.USER_COL7,
3318 OPERATING_UNITS_R.USER_COL8,
3319 OPERATING_UNITS_R.USER_COL9,
3320 OPERATING_UNITS_R.USER_COL10,
3321 X_LAST_UPDATE_LOGIN,
3322 X_REQUEST_ID,
3323 X_PROGRAM_APPLICATION_ID,
3324 X_PROGRAM_ID,
3325 TRUNC(SYSDATE),
3326 'P'
3327 );
3328
3329 END IF; -- IF ( SQL%ROWCOUNT = 0 )
3330
3331 END LOOP; -- FOR operating_units_r IN sel_operating_units
3332
3333 FOR set_of_book_r IN set_of_book LOOP
3334
3335 -- First Try to Update the Row in the Interface Table
3336
3337 UPDATE
3338 PA_SET_OF_BOOKS_IT
3339 SET
3340 LAST_UPDATE_DATE = TRUNC(SYSDATE),
3341 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
3342 CREATION_DATE = TRUNC(SYSDATE),
3343 CREATED_BY = X_CREATED_BY,
3344 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
3345 REQUEST_ID = X_REQUEST_ID,
3346 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
3347 PROGRAM_ID = X_PROGRAM_ID,
3348 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
3349 STATUS_CODE = 'P'
3350 WHERE
3351 SET_OF_BOOK = set_of_book_r.SET_OF_BOOK ;
3352
3353
3354 IF (SQL%ROWCOUNT = 0) THEN
3355 -- No row was updated, So Insert a new row into the interface table
3356 INSERT INTO PA_SET_OF_BOOKS_IT
3357 (
3358 SET_OF_BOOK,
3359 LAST_UPDATE_DATE,
3360 LAST_UPDATED_BY,
3361 CREATION_DATE,
3362 CREATED_BY,
3363 LAST_UPDATE_LOGIN,
3364 REQUEST_ID,
3365 PROGRAM_APPLICATION_ID,
3366 PROGRAM_ID,
3367 PROGRAM_UPDATE_DATE,
3368 STATUS_CODE
3369 )
3370 VALUES
3371 (
3372 SET_OF_BOOK_R.SET_OF_BOOK,
3373 TRUNC(SYSDATE),
3374 X_LAST_UPDATED_BY,
3375 TRUNC(SYSDATE),
3376 X_CREATED_BY,
3377 X_LAST_UPDATE_LOGIN,
3378 X_REQUEST_ID,
3379 X_PROGRAM_APPLICATION_ID,
3380 X_PROGRAM_ID,
3381 TRUNC(SYSDATE),
3382 'P'
3383 );
3384 END IF;
3385 END LOOP;
3386
3387 FOR legal_entity_r IN legal_entity LOOP
3388
3389 -- First Try to Update the Row in the Interface Table
3390
3391 UPDATE
3392 PA_LEGAL_ENTITY_IT
3393 SET
3394 SET_OF_BOOK = legal_entity_r.SET_OF_BOOK,
3395 LAST_UPDATE_DATE = TRUNC(SYSDATE),
3396 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
3397 CREATION_DATE = TRUNC(SYSDATE),
3398 CREATED_BY = X_CREATED_BY,
3399 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
3400 REQUEST_ID = X_REQUEST_ID,
3401 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
3402 PROGRAM_ID = X_PROGRAM_ID,
3403 PROGRAM_UPDATE_DATE = TRUNC(SYSDATE),
3404 STATUS_CODE = 'P'
3405 WHERE
3406 LEGAL_ENTITY = legal_entity_r.LEGAL_ENTITY ;
3407
3408
3409 IF (SQL%ROWCOUNT = 0) THEN
3410 -- No row was updated, So Insert a new row into the interface table
3411 INSERT INTO PA_LEGAL_ENTITY_IT
3412 (
3413 LEGAL_ENTITY,
3414 SET_OF_BOOK,
3415 LAST_UPDATE_DATE,
3416 LAST_UPDATED_BY,
3417 CREATION_DATE,
3418 CREATED_BY,
3419 LAST_UPDATE_LOGIN,
3420 REQUEST_ID,
3421 PROGRAM_APPLICATION_ID,
3422 PROGRAM_ID,
3423 PROGRAM_UPDATE_DATE,
3424 STATUS_CODE
3425 )
3426 VALUES
3427 (
3428 LEGAL_ENTITY_R.LEGAL_ENTITY,
3429 LEGAL_ENTITY_R.SET_OF_BOOK,
3430 TRUNC(SYSDATE),
3431 X_LAST_UPDATED_BY,
3432 TRUNC(SYSDATE),
3433 X_CREATED_BY,
3434 X_LAST_UPDATE_LOGIN,
3435 X_REQUEST_ID,
3436 X_PROGRAM_APPLICATION_ID,
3437 X_PROGRAM_ID,
3438 TRUNC(SYSDATE),
3439 'P'
3440 );
3441 END IF;
3442 END LOOP;
3443
3444 x_err_stack := x_old_err_stack;
3445
3446 pa_debug.debug('Completed ' || x_err_stage);
3447
3448 EXCEPTION
3449 WHEN OTHERS THEN
3450 x_err_code := SQLCODE;
3451 RAISE;
3452 END get_dim_organizations;
3453
3454 END PA_ADW_COLLECT_DIMENSIONS;