[Home] [Help]
PACKAGE BODY: APPS.PA_ADW_COLLECT_MAIN
Source
1 PACKAGE BODY PA_ADW_COLLECT_MAIN AS
2 /* $Header: PAADWCMB.pls 115.5 99/07/16 13:22:01 porting ship $ */
3
4 FUNCTION Initialize RETURN NUMBER IS
5 BEGIN
6 pa_debug.debug('Getting the License Status');
7
8 -- By Default the Oracle Project Collection Pack is not Licensed
9
10 license_status := NVL(fnd_profile.value('PA_ADW_LICENSED'),'N');
11
12 pa_debug.debug('Getting the Install Status');
13
14 -- By Default the Oracle Project Collection Pack is not Installed
15
16 install_status := NVL(fnd_profile.value('PA_ADW_INSTALLED'),'N');
17
18 pa_debug.debug('Getting the Tasks Profile Option Values');
19
20 -- By Default we will not collect lowest/top level tasks
21 -- If we are collecting lowest tasks, we will always collect top tasks
22
23 pa_debug.debug('Getting the Lowest Task Profile Option Values');
24
25 collect_lowest_tasks_flag := NVL(fnd_profile.value('PA_ADW_COLLECT_LOWEST_TASKS'),'N');
26
27 IF (collect_lowest_tasks_flag = 'Y') THEN
28 -- Always collect top tasks
29 collect_top_tasks_flag := 'Y';
30 ELSE
31 pa_debug.debug('Getting the Top Task Profile Option Values');
32 collect_top_tasks_flag := NVL(fnd_profile.value('PA_ADW_COLLECT_TOP_TASKS'),'N');
33 END IF;
34
35 RETURN (0);
36
37 EXCEPTION
38 WHEN OTHERS THEN
39 RAISE;
40 END Initialize;
41 -- Procedure to get dimension statuses
42
43 PROCEDURE get_dimension_status
44 ( x_err_stage IN OUT VARCHAR2,
45 x_err_stack IN OUT VARCHAR2,
46 x_err_code IN OUT NUMBER)
47 IS
48 x_old_err_stack VARCHAR2(1024);
49 BEGIN
50 x_err_code := 0;
51 x_err_stage := 'Getting Dimension Status';
52 x_old_err_stack := x_err_stack;
53 x_err_stack := x_err_stack || '-> get_dimension_status';
54
55 pa_debug.debug(x_err_stage);
56
57 -- Call the initialize procedure
58
59 x_err_code := pa_adw_collect_main.initialize;
60
61 -- get the dimension statuses
62
63 pa_adw_collect_dimensions.get_dim_status
64 ('DM_PROJECT',
65 dim_project,
66 x_err_stage,
67 x_err_stack,
68 x_err_code);
69
70 pa_adw_collect_dimensions.get_dim_status
71 ('DM_RESOURCE',
72 dim_resource,
73 x_err_stage,
74 x_err_stack,
75 x_err_code);
76
77 pa_adw_collect_dimensions.get_dim_status
78 ('DM_PROJECT_ORG',
79 dim_project_org,
80 x_err_stage,
81 x_err_stack,
82 x_err_code);
83
84 pa_adw_collect_dimensions.get_dim_status
85 ('DM_EXP_ORG',
86 dim_exp_org,
87 x_err_stage,
88 x_err_stack,
89 x_err_code);
90
91 pa_adw_collect_dimensions.get_dim_status
92 ('DM_SRVC_TYPE',
93 dim_srvc_type,
94 x_err_stage,
95 x_err_stack,
96 x_err_code);
97
98 pa_adw_collect_dimensions.get_dim_status
99 ('DM_TIME',
100 dim_time,
101 x_err_stage,
102 x_err_stack,
103 x_err_code);
104
105 pa_adw_collect_dimensions.get_dim_status
106 ('DM_BGT_TYPE',
107 dim_bgt_type,
108 x_err_stage,
109 x_err_stack,
110 x_err_code);
111
112 pa_adw_collect_dimensions.get_dim_status
113 ('DM_EXP_TYPE',
114 dim_exp_type,
115 x_err_stage,
116 x_err_stack,
117 x_err_code);
118
119 pa_adw_collect_dimensions.get_dim_status
120 ('DM_OPERATING_UNIT',
121 dim_operating_unit,
122 x_err_stage,
123 x_err_stack,
124 x_err_code);
125
126 x_err_stack := x_old_err_stack;
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 x_err_code := SQLCODE;
131 RAISE;
132 END get_dimension_status;
133
134 -- Prepare the source data for refresh
135 -- We will mark all source table ADW_NOTIFY_FLAG = 'Y'
136
137 PROCEDURE prepare_src_table_for_refresh
138 ( x_err_stage IN OUT VARCHAR2,
139 x_err_stack IN OUT VARCHAR2,
140 x_err_code IN OUT NUMBER)
141 IS
142 x_old_err_stack VARCHAR2(1024);
143 BEGIN
144 x_err_code := 0;
145 x_err_stage := 'Preparing Source Table for Collection';
146 x_old_err_stack := x_err_stack;
147 x_err_stack := x_err_stack || '-> prepare_src_table_for_refresh';
148
149 pa_debug.debug(x_err_stage);
150
151 UPDATE PA_TASKS SET ADW_NOTIFY_FLAG='Y'
152 WHERE ADW_NOTIFY_FLAG = 'N';
153
154 UPDATE PA_PROJECTS SET ADW_NOTIFY_FLAG='Y'
155 WHERE ADW_NOTIFY_FLAG = 'N';
156
157 UPDATE PA_PROJECT_TYPES SET ADW_NOTIFY_FLAG='Y'
158 WHERE ADW_NOTIFY_FLAG = 'N';
159
160 UPDATE PA_EXPENDITURE_TYPES SET ADW_NOTIFY_FLAG='Y'
161 WHERE ADW_NOTIFY_FLAG = 'N';
162
163 UPDATE PA_CLASS_CATEGORIES SET ADW_NOTIFY_FLAG='Y'
164 WHERE ADW_NOTIFY_FLAG = 'N';
165
166 UPDATE PA_CLASS_CODES SET ADW_NOTIFY_FLAG='Y'
167 WHERE ADW_NOTIFY_FLAG = 'N';
168
169 UPDATE PA_PROJECT_CLASSES SET ADW_NOTIFY_FLAG='Y'
170 WHERE ADW_NOTIFY_FLAG = 'N';
171
172 UPDATE PA_BUDGET_TYPES SET ADW_NOTIFY_FLAG='Y'
173 WHERE ADW_NOTIFY_FLAG = 'N';
174
175 UPDATE PA_RESOURCE_LIST_MEMBERS SET ADW_NOTIFY_FLAG='Y'
176 WHERE ADW_NOTIFY_FLAG = 'N';
177
178 UPDATE PA_RESOURCE_LISTS SET ADW_NOTIFY_FLAG='Y'
179 WHERE ADW_NOTIFY_FLAG = 'N';
180
181 UPDATE PA_TXN_ACCUM SET ADW_NOTIFY_FLAG='Y'
182 WHERE ADW_NOTIFY_FLAG = 'N';
183
184 UPDATE PA_RESOURCE_ACCUM_DETAILS SET ADW_NOTIFY_FLAG='Y'
185 WHERE ADW_NOTIFY_FLAG = 'N';
186
187 UPDATE PA_BUDGET_VERSIONS SET ADW_NOTIFY_FLAG='Y'
188 WHERE ADW_NOTIFY_FLAG = 'N';
189
190 x_err_stack := x_old_err_stack;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 x_err_code := SQLCODE;
195 RAISE;
196 END prepare_src_table_for_refresh;
197
198 -- Clear interface tables
199 -- Delete all rows from interface table
200
201 PROCEDURE clear_interface_tables
202 ( x_err_stage IN OUT VARCHAR2,
203 x_err_stack IN OUT VARCHAR2,
204 x_err_code IN OUT NUMBER)
205 IS
206 x_old_err_stack VARCHAR2(1024);
207 BEGIN
208 x_err_code := 0;
209 x_err_stage := 'Clearing Interface Tables';
210 x_old_err_stack := x_err_stack;
211 x_err_stack := x_err_stack || '-> clear_interface_tables';
212
213 pa_debug.debug(x_err_stage);
214
215 DELETE FROM PA_TOP_TASKS_IT;
216 DELETE FROM PA_PROJECTS_IT;
217 DELETE FROM PA_PRJ_TYPES_IT;
218 DELETE FROM PA_EXP_TYPES_IT;
219 DELETE FROM PA_PRJ_CLASSES_IT;
220 DELETE FROM PA_CLASS_CATGS_IT;
221 DELETE FROM PA_CLASS_CODES_IT;
222 DELETE FROM PA_LOWEST_RLMEM_IT;
223 DELETE FROM PA_TOP_RLMEM_IT;
224 DELETE FROM PA_RES_LISTS_IT;
225 DELETE FROM PA_SRVC_TYPES_IT;
226 DELETE FROM PA_PERIODS_IT;
227 DELETE FROM PA_ORGS_IT;
228 DELETE FROM PA_BGT_TYPES_IT;
229 DELETE FROM PA_TSK_ACT_CMT_IT;
230 DELETE FROM PA_PRJ_ACT_CMT_IT;
231 DELETE FROM PA_TSK_BGT_LINES_IT;
232 DELETE FROM PA_PRJ_BGT_LINES_IT;
233 DELETE FROM PA_OLD_RES_ACCUM_DTLS;
234 DELETE FROM PA_OPER_UNITS_IT;
235 DELETE FROM PA_GL_PERIODS_IT;
236 DELETE FROM PA_FINANCIAL_QTRS_IT;
237 DELETE FROM PA_FINANCIAL_YRS_IT;
238 DELETE FROM PA_ALL_FINANCIAL_YRS_IT;
239 DELETE FROM PA_ALL_EXP_TYPES_IT;
240 DELETE FROM PA_ALL_SRVC_TYPES_IT;
241 DELETE FROM PA_ALL_PRJ_TYPES_IT;
242 DELETE FROM PA_PRJ_ORGS_IT;
243 DELETE FROM PA_PRJ_BUSINESS_GRPS_IT;
244 DELETE FROM PA_EXP_ORGS_IT;
245 DELETE FROM PA_EXP_BUSINESS_GRPS_IT;
246 DELETE FROM PA_SET_OF_BOOKS_IT;
247 DELETE FROM PA_LEGAL_ENTITY_IT;
248
249 x_err_stack := x_old_err_stack;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 x_err_code := SQLCODE;
254 RAISE;
255 END clear_interface_tables;
256
257 -- Purge interface table using dynamic SQL
258 -- for rows of information already integrated with OADW system
259 -- If the WH_UPDATE_DATE column has the value less than the
260 -- Date when the warehouse was updated last, then these rows
261 -- can be deleted from the interface table.
262
263 PROCEDURE purge_it_OADW
264 ( x_table_name IN VARCHAR2,
265 x_wh_update_date IN DATE)
266 IS
267 sql_command VARCHAR2(1024);
268 source_cursor integer;
269 rows_deleted integer;
270 BEGIN
271
272 pa_debug.debug('Purging Interface Tables For OADW ' || x_table_name);
273
274 -- prepare a cursor to delete from the source table
275 source_cursor := dbms_sql.open_cursor;
276 sql_command :=
277 'DELETE FROM '|| x_table_name
278 || ' WHERE WH_UPDATE_DATE <= :x_wh_update_date';
279 dbms_sql.parse(source_cursor,sql_command,dbms_sql.native);
280 dbms_sql.bind_variable(source_cursor, 'x_wh_update_date', x_wh_update_date);
281 rows_deleted := dbms_sql.execute(source_cursor);
282 pa_debug.debug('Rows deleted:= ' || to_char(rows_deleted));
283 dbms_sql.close_cursor(source_cursor);
284 EXCEPTION
285 WHEN OTHERS THEN
286 IF dbms_sql.is_open(source_cursor) THEN
287 dbms_sql.close_cursor(source_cursor);
288 END IF;
289 RAISE;
290 END purge_it_OADW;
291
292 -- Purge interface table using dynamic SQL
293 -- for rows of information already integrated with OADW system
294 -- If the WH_UPDATE_DATE column has the value less than the
295 -- Date when the warehouse was updated last, then these rows
296 -- can be deleted from the interface table.
297
298 PROCEDURE purge_interface_tables_OADW
299 ( x_err_stage IN OUT VARCHAR2,
300 x_err_stack IN OUT VARCHAR2,
301 x_err_code IN OUT NUMBER)
302 IS
303 x_old_err_stack VARCHAR2(1024);
304 oadw_wh_cursor integer;
305 earliest_collect_time Date;/*Earliest collection time from OADW Warehouse*/
306 rows_processed integer;
307 BEGIN
308 x_err_code := 0;
309 x_err_stage := 'Purging Interface Tables For OADW';
310 x_old_err_stack := x_err_stack;
311 x_err_stack := x_err_stack || '-> purge_interface_tables_OADW';
312 earliest_collect_time := NULL;
313
314 pa_debug.debug(x_err_stage);
315
316 IF (install_status = 'Y') THEN
317
318 /* Get the Warehouse Update DATE */
319 /* Get the warehouse update date using a dynamic SQL, since
320 WH_RT_VERSIONS_V2 is available in the OADW Warehouse Database */
321
322 oadw_wh_cursor := dbms_sql.open_cursor;
323 dbms_sql.parse(oadw_wh_cursor,
324 'SELECT earliest_collect_time FROM wh_rt_versions_v2@PA_TO_WH',dbms_sql.native);
325 dbms_sql.define_column(oadw_wh_cursor, 1, earliest_collect_time);
326 rows_processed := dbms_sql.execute(oadw_wh_cursor);
327
328 if ( dbms_sql.fetch_rows(oadw_wh_cursor) > 0 ) then
329 dbms_sql.column_value(oadw_wh_cursor, 1, earliest_collect_time);
330 end if;
331 dbms_sql.close_cursor(oadw_wh_cursor);
332
333 IF (earliest_collect_time IS NOT NULL) THEN
334 /* Purge Interface table */
335 /* Project Dimension */
336 IF (collect_top_tasks_flag = 'Y') THEN
337 purge_it_OADW('PA_TOP_TASKS_IT',earliest_collect_time);
338 END IF;
339 IF (collect_lowest_tasks_flag = 'Y') THEN
340 purge_it_OADW('PA_LOWEST_TASKS_IT',earliest_collect_time);
341 END IF;
342 purge_it_OADW('PA_PROJECTS_IT_ALL',earliest_collect_time);
343 purge_it_OADW('PA_PRJ_TYPES_IT_ALL',earliest_collect_time);
344 purge_it_OADW('PA_ALL_PRJ_TYPES_IT',earliest_collect_time);
345 /* Resource Dimension */
346 purge_it_OADW('PA_LOWEST_RLMEM_IT',earliest_collect_time);
347 purge_it_OADW('PA_TOP_RLMEM_IT',earliest_collect_time);
348 purge_it_OADW('PA_RES_LISTS_IT_ALL_BG',earliest_collect_time);
349 /* Budget Type Dimension */
350 purge_it_OADW('PA_BGT_TYPES_IT',earliest_collect_time);
351 /* Time Dimension */
352 purge_it_OADW('PA_PERIODS_IT',earliest_collect_time);
353 purge_it_OADW('PA_GL_PERIODS_IT',earliest_collect_time);
354 purge_it_OADW('PA_FINANCIAL_QTRS_IT',earliest_collect_time);
355 purge_it_OADW('PA_FINANCIAL_YRS_IT',earliest_collect_time);
356 purge_it_OADW('PA_ALL_FINANCIAL_YRS_IT',earliest_collect_time);
357 /* Fact Tables */
358 IF (collect_top_tasks_flag = 'Y' OR collect_lowest_tasks_flag = 'Y') THEN
359 purge_it_OADW('PA_TSK_ACT_CMT_IT_ALL',earliest_collect_time);
360 END IF;
361 purge_it_OADW('PA_PRJ_ACT_CMT_IT_ALL',earliest_collect_time);
362 IF (collect_top_tasks_flag = 'Y' OR collect_lowest_tasks_flag = 'Y') THEN
363 purge_it_OADW('PA_TSK_BGT_LINES_IT_ALL',earliest_collect_time);
364 END IF;
365 purge_it_OADW('PA_PRJ_BGT_LINES_IT_ALL',earliest_collect_time);
366 /* Expenditure Type Dimension */
367 IF (dim_exp_type = 'E') THEN
368 purge_it_OADW('PA_EXP_TYPES_IT',earliest_collect_time);
369 purge_it_OADW('PA_ALL_EXP_TYPES_IT',earliest_collect_time);
370 END IF;
371 /* Service Type Dimension */
372 IF (dim_srvc_type = 'E') THEN
373 purge_it_OADW('PA_SRVC_TYPES_IT',earliest_collect_time);
374 purge_it_OADW('PA_ALL_SRVC_TYPES_IT',earliest_collect_time);
375 END IF;
376 /* Project Organization Dimension */
377 IF (dim_project_org = 'E') THEN
378 purge_it_OADW('PA_PRJ_ORGS_IT',earliest_collect_time);
379 purge_it_OADW('PA_PRJ_BUSINESS_GRPS_IT',earliest_collect_time);
380 END IF;
381 /* Expenditure Organization Dimension */
382 IF (dim_exp_org = 'E') THEN
383 purge_it_OADW('PA_EXP_ORGS_IT',earliest_collect_time);
384 purge_it_OADW('PA_EXP_BUSINESS_GRPS_IT',earliest_collect_time);
385 END IF;
386 /* Operating Unit Dimension */
387 IF (dim_operating_unit = 'E') THEN
388 purge_it_OADW('PA_SET_OF_BOOKS_IT',earliest_collect_time);
389 purge_it_OADW('PA_LEGAL_ENTITY_IT',earliest_collect_time);
390 purge_it_OADW('PA_OPER_UNITS_IT',earliest_collect_time);
391 END IF;
392 END IF; -- IF (earliest_collect_time IS NOT NULL) THEN
393
394 END IF ; -- IF (install_status = 'Y')
395
396 x_err_stack := x_old_err_stack;
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 IF dbms_sql.is_open(oadw_wh_cursor) THEN
401 dbms_sql.close_cursor(oadw_wh_cursor);
402 END IF;
403 x_err_code := SQLCODE;
404 RAISE;
405 END purge_interface_tables_OADW;
406
407 -- Purge interface tables for information that is integrated
408 -- with external system
409 -- The STATUS_CODE for all such rows will have a value of 'T'
410
411 PROCEDURE purge_interface_tables
412 ( x_err_stage IN OUT VARCHAR2,
413 x_err_stack IN OUT VARCHAR2,
414 x_err_code IN OUT NUMBER)
415 IS
416 x_old_err_stack VARCHAR2(1024);
417 BEGIN
418 x_err_code := 0;
419 x_err_stage := 'Purging Interface Tables';
420 x_old_err_stack := x_err_stack;
421 x_err_stack := x_err_stack || '-> purge_interface_tables';
422
423 pa_debug.debug(x_err_stage);
424
425 DELETE FROM PA_TOP_TASKS_IT
426 WHERE STATUS_CODE = 'T';
427
428 DELETE FROM PA_PROJECTS_IT
429 WHERE STATUS_CODE = 'T';
430
431 DELETE FROM PA_PRJ_TYPES_IT
432 WHERE STATUS_CODE = 'T';
433
434 DELETE FROM PA_EXP_TYPES_IT
435 WHERE STATUS_CODE = 'T';
436
437 DELETE FROM PA_PRJ_CLASSES_IT
438 WHERE STATUS_CODE = 'T';
439
440 DELETE FROM PA_CLASS_CATGS_IT
441 WHERE STATUS_CODE = 'T';
442
443 DELETE FROM PA_CLASS_CODES_IT
444 WHERE STATUS_CODE = 'T';
445
446 DELETE FROM PA_LOWEST_RLMEM_IT
447 WHERE STATUS_CODE = 'T';
448
449 DELETE FROM PA_TOP_RLMEM_IT
450 WHERE STATUS_CODE = 'T';
451
452 DELETE FROM PA_RES_LISTS_IT
453 WHERE STATUS_CODE = 'T';
454
455 DELETE FROM PA_SRVC_TYPES_IT
456 WHERE STATUS_CODE = 'T';
457
458 DELETE FROM PA_PERIODS_IT
459 WHERE STATUS_CODE = 'T';
460
461 DELETE FROM PA_ORGS_IT
462 WHERE STATUS_CODE = 'T';
463
464 DELETE FROM PA_BGT_TYPES_IT
465 WHERE STATUS_CODE = 'T';
466
467 DELETE FROM PA_TSK_ACT_CMT_IT
468 WHERE STATUS_CODE = 'T';
469
470 DELETE FROM PA_PRJ_ACT_CMT_IT
471 WHERE STATUS_CODE = 'T';
472
473 DELETE FROM PA_TSK_BGT_LINES_IT
474 WHERE STATUS_CODE = 'T';
475
476 DELETE FROM PA_PRJ_BGT_LINES_IT
477 WHERE STATUS_CODE = 'T';
478
479 DELETE FROM PA_OPER_UNITS_IT
480 WHERE STATUS_CODE = 'T';
481
482 DELETE FROM PA_GL_PERIODS_IT
483 WHERE STATUS_CODE = 'T';
484
485 DELETE FROM PA_FINANCIAL_QTRS_IT
486 WHERE STATUS_CODE = 'T';
487
488 DELETE FROM PA_FINANCIAL_YRS_IT
489 WHERE STATUS_CODE = 'T';
490
491 DELETE FROM PA_ALL_FINANCIAL_YRS_IT
492 WHERE STATUS_CODE = 'T';
493
494 DELETE FROM PA_ALL_EXP_TYPES_IT
495 WHERE STATUS_CODE = 'T';
496
497 DELETE FROM PA_ALL_SRVC_TYPES_IT
498 WHERE STATUS_CODE = 'T';
499
500 DELETE FROM PA_ALL_PRJ_TYPES_IT
501 WHERE STATUS_CODE = 'T';
502
503 DELETE FROM PA_PRJ_ORGS_IT
504 WHERE STATUS_CODE = 'T';
505
506 DELETE FROM PA_PRJ_BUSINESS_GRPS_IT
507 WHERE STATUS_CODE = 'T';
508
509 DELETE FROM PA_EXP_ORGS_IT
510 WHERE STATUS_CODE = 'T';
511
512 DELETE FROM PA_EXP_BUSINESS_GRPS_IT
513 WHERE STATUS_CODE = 'T';
514
515 DELETE FROM PA_SET_OF_BOOKS_IT
516 WHERE STATUS_CODE = 'T';
517
518 DELETE FROM PA_LEGAL_ENTITY_IT
519 WHERE STATUS_CODE = 'T';
520
521 pa_adw_collect_main.purge_interface_tables_OADW
522 ( x_err_stage,
523 x_err_stack,
524 x_err_code);
525
526 x_err_stack := x_old_err_stack;
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 x_err_code := SQLCODE;
531 RAISE;
532 END purge_interface_tables;
533
534 -- Main Procedure to collect dimension and fact tables
535
536 PROCEDURE get_dim_and_fact_main
537 ( x_collect_dim_tables IN VARCHAR2,
538 x_dimension_table IN VARCHAR2,
539 x_collect_fact_tables IN VARCHAR2,
540 x_fact_table IN VARCHAR2,
541 x_project_num_from IN VARCHAR2,
542 x_project_num_to IN VARCHAR2,
543 x_err_stage IN OUT VARCHAR2,
544 x_err_stack IN OUT VARCHAR2,
545 x_err_code IN OUT NUMBER)
546 IS
547 x_old_err_stack VARCHAR2(1024);
548 BEGIN
549 x_err_code := 0;
550 x_err_stage := 'Collecting Dimensions and Fact Tables';
551 x_old_err_stack := x_err_stack;
552 x_err_stack := x_err_stack || '-> get_dim_and_fact_main';
553
554 pa_debug.debug(x_err_stage);
555
556 -- Call the initialize procedure
557
558 x_err_code := pa_adw_collect_main.initialize;
559
560 -- Check the license status
561
562 IF ( license_status <> 'Y' ) THEN
563 pa_debug.debug('Oracle Project Analysis Collection Pack Not Licensed',
564 pa_debug.DEBUG_LEVEL_EXCEPTION);
565 x_err_stage := 'Oracle Project Analysis Collection Pack Not Licensed';
566 x_err_code := 2;
567 ROLLBACK WORK;
568 return;
569 END IF; -- IF ( license_status <> 'Y' )
570
571 IF ( x_collect_dim_tables = 'Y') THEN
572
573 -- Collect dimension tables
574 -- First get the dimension statuses
575
576 pa_adw_collect_main.get_dimension_status
577 ( x_err_stage,
578 x_err_stack,
579 x_err_code);
580
581 IF ( x_dimension_table = 'TASKS'
582 OR x_dimension_table IS NULL ) THEN
583
584 IF (dim_project = 'E') THEN
585 -- Collect Tasks
586 pa_adw_collect_dimensions.get_dim_tasks (x_err_stage, x_err_stack, x_err_code);
587 END IF;
588
589 END IF;
590 IF ( x_dimension_table = 'PROJECTS'
591 OR x_dimension_table IS NULL ) THEN
592
593 IF (dim_project = 'E') THEN
594 -- Collect Projects
595 pa_adw_collect_dimensions.get_dim_projects (x_err_stage, x_err_stack, x_err_code);
596 END IF;
597
598 END IF;
599 IF ( x_dimension_table = 'PROJECT_TYPES'
600 OR x_dimension_table IS NULL ) THEN
601
602 IF (dim_project = 'E') THEN
603 -- Collect Project Types
604 pa_adw_collect_dimensions.get_dim_project_types (x_err_stage, x_err_stack, x_err_code);
605 END IF;
606
607 END IF;
608 IF ( x_dimension_table = 'EXPENDITURE_TYPES'
609 OR x_dimension_table IS NULL ) THEN
610
611 IF (dim_exp_type = 'E') THEN
612 -- Collect Project Types
613 pa_adw_collect_dimensions.get_dim_expenditure_types (x_err_stage, x_err_stack, x_err_code);
614 END IF;
615
616 END IF;
617 IF ( x_dimension_table = 'PROJECT_CLASSES'
618 OR x_dimension_table IS NULL ) THEN
619
620 IF (dim_project = 'E') THEN
621 -- Collect Project Classes
622 pa_adw_collect_dimensions.get_dim_project_classes (x_err_stage, x_err_stack, x_err_code);
623 END IF;
624
625 END IF;
626 IF ( x_dimension_table = 'CLASS_CATEGORIES'
627 OR x_dimension_table IS NULL ) THEN
628
629 IF (dim_project = 'E') THEN
630 -- Collect Class Categories
631 pa_adw_collect_dimensions.get_dim_class_categories (x_err_stage, x_err_stack, x_err_code);
632 END IF;
633
634 END IF;
635 IF ( x_dimension_table = 'CLASS_CODES'
636 OR x_dimension_table IS NULL ) THEN
637
638 IF (dim_project = 'E') THEN
639 -- Collect Class Codes
640 pa_adw_collect_dimensions.get_dim_class_codes (x_err_stage, x_err_stack, x_err_code);
641 END IF;
642
643 END IF;
644 IF ( x_dimension_table = 'RESOURCES'
645 OR x_dimension_table IS NULL ) THEN
646
647 IF (dim_resource = 'E') THEN
648 -- Collect Resources
649 pa_adw_collect_dimensions.get_dim_resources (x_err_stage, x_err_stack, x_err_code);
650 END IF;
651
652 END IF;
653 IF ( x_dimension_table = 'RESOURCE_LISTS'
654 OR x_dimension_table IS NULL ) THEN
655
656 IF (dim_resource = 'E') THEN
657 -- Collect Resource Lists
658 pa_adw_collect_dimensions.get_dim_resource_lists (x_err_stage, x_err_stack, x_err_code);
659 END IF;
660
661 END IF;
662 IF ( x_dimension_table = 'BUDGET_TYPES'
663 OR x_dimension_table IS NULL ) THEN
664
665 IF (dim_bgt_type = 'E') THEN
666 -- Collect Budget Types
667 pa_adw_collect_dimensions.get_dim_budget_types (x_err_stage, x_err_stack, x_err_code);
668 END IF;
669
670 END IF;
671 IF ( x_dimension_table = 'PERIODS'
672 OR x_dimension_table IS NULL ) THEN
673
674 IF (dim_time = 'E') THEN
675 -- Collect Periods
676 pa_adw_collect_dimensions.get_dim_periods (x_err_stage, x_err_stack, x_err_code);
677 END IF;
678
679 END IF;
680 IF ( x_dimension_table = 'SERVICE_TYPES'
681 OR x_dimension_table IS NULL ) THEN
682
683 IF (dim_srvc_type = 'E') THEN
684 -- Collect Service Types
685 pa_adw_collect_dimensions.get_dim_service_types (x_err_stage, x_err_stack, x_err_code);
686 END IF;
687
688 END IF;
689 IF ( x_dimension_table = 'ORGANIZATIONS'
690 OR x_dimension_table IS NULL ) THEN
691
692 IF (dim_project_org = 'E' OR dim_exp_org = 'E'
693 OR dim_operating_unit = 'E' ) THEN
694 -- Collect Organizations
695 pa_adw_collect_dimensions.get_dim_organizations (x_err_stage, x_err_stack, x_err_code);
696 END IF;
697
698 END IF;
699
700 -- Collect Custom Dimensions
701
702 IF ( x_dimension_table = 'CUSTOM_DIMENSIONS_TABLES'
703 OR x_dimension_table IS NULL ) THEN
704
705 pa_adw_custom_collect.get_dimension_tables
706 (x_err_stage,
707 x_err_stack,
708 x_err_code,
709 'I'); -- Incremental Collection
710 END IF;
711
712 -- Commit Collected Dimensions
713 COMMIT;
714 END IF; -- IF ( x_collect_dim_tables = 'Y')
715
716 IF ( x_collect_fact_tables = 'Y') THEN
717
718 -- Collect fact tables
719
720 IF ( x_fact_table = 'ACTUALS_COST_AND_COMMITMENTS'
721 OR x_fact_table IS NULL ) THEN
722
723 -- Collect Actuals and commitments
724 pa_adw_collect_facts.get_fact_act_cmts
725 (x_project_num_from,
726 x_project_num_to,
727 x_err_stage,
728 x_err_stack,
729 x_err_code);
730 END IF;
731
732 IF ( x_fact_table = 'BUDGETS'
733 OR x_fact_table IS NULL ) THEN
734
735 -- Collect Budgets
736 pa_adw_collect_facts.get_fact_budgets
737 (x_project_num_from,
738 x_project_num_to,
739 x_err_stage,
740 x_err_stack,
741 x_err_code);
742
743 END IF;
744
745 -- Collect Custom fact Tables
746
747 IF ( x_fact_table = 'CUSTOM_FACT_TABLES'
748 OR x_fact_table IS NULL ) THEN
749
750 -- Collect Custom Fact Tables
751 pa_adw_custom_collect.get_fact_tables
752 (x_project_num_from,
753 x_project_num_to,
754 x_err_stage,
755 x_err_stack,
756 x_err_code,
757 'I'); -- Incremental Collection
758 END IF;
759
760 -- Commit Collected Fact Tables
761 COMMIT;
762 END IF; -- IF ( x_collect_fact_tables = 'Y')
763
764 -- purge interface tables
765
766 pa_adw_collect_main.purge_interface_tables
767 ( x_err_stage,
768 x_err_stack,
769 x_err_code);
770
771 x_err_stack := x_old_err_stack;
772
773 EXCEPTION
774 WHEN OTHERS THEN
775 x_err_code := SQLCODE;
776 pa_debug.debug('Exception Generated By Oracle Error: ' || SQLERRM(SQLCODE),
777 pa_debug.DEBUG_LEVEL_EXCEPTION);
778 ROLLBACK WORK;
779 return;
780 END get_dim_and_fact_main;
781
782 -- Procedure to refresh the dimension tables/fact tables from scratch
783 -- This procedure need to followed when the user changes
784 -- dimension after initial collection.
785 -- If integrating with OADW then the entire warehouse need to be
786 -- built again.
787
788 PROCEDURE ref_dim_and_fact_main
789 ( x_err_stage IN OUT VARCHAR2,
790 x_err_stack IN OUT VARCHAR2,
791 x_err_code IN OUT NUMBER)
792 IS
793 x_old_err_stack VARCHAR2(1024);
794 BEGIN
795 x_err_code := 0;
796 x_err_stage := 'Refreshing Dimensions and Fact Tables';
797 x_old_err_stack := x_err_stack;
798 x_err_stack := x_err_stack || '-> ref_dim_and_fact_main';
799
800 pa_debug.debug(x_err_stage);
801
802 -- Call the initialize procedure
803
804 x_err_code := pa_adw_collect_main.initialize;
805
806 -- Check the license status
807
808 IF ( license_status <> 'Y' ) THEN
809 pa_debug.debug('Oracle Project Analysis Collection Pack Not Licensed',
810 pa_debug.DEBUG_LEVEL_EXCEPTION);
811 x_err_stage := 'Oracle Project Analysis Collection Pack Not Licensed';
812 x_err_code := 2;
813 ROLLBACK WORK;
814 return;
815 END IF; -- IF ( license_status <> 'Y' )
816
817 -- Prepare source table for refresh
818
819 pa_adw_collect_main.prepare_src_table_for_refresh
820 ( x_err_stage,
821 x_err_stack,
822 x_err_code);
823
824 -- Clear interface tables
825
826 pa_adw_collect_main.clear_interface_tables
827 ( x_err_stage,
828 x_err_stack,
829 x_err_code);
830
831 -- Collect dimension tables
832 -- get the dimension statuses
833
834 pa_adw_collect_main.get_dimension_status
835 ( x_err_stage,
836 x_err_stack,
837 x_err_code);
838
839 IF (dim_project = 'E') THEN
840 -- Collect Tasks
841 pa_adw_collect_dimensions.get_dim_tasks (x_err_stage, x_err_stack, x_err_code);
842 END IF;
843
844 IF (dim_project = 'E') THEN
845 -- Collect Projects
846 pa_adw_collect_dimensions.get_dim_projects (x_err_stage, x_err_stack, x_err_code);
847 END IF;
848
849 IF (dim_project = 'E') THEN
850 -- Collect Project Types
851 pa_adw_collect_dimensions.get_dim_project_types (x_err_stage, x_err_stack, x_err_code);
852 END IF;
853
854 IF (dim_exp_type = 'E') THEN
855 -- Collect Project Types
856 pa_adw_collect_dimensions.get_dim_expenditure_types (x_err_stage, x_err_stack, x_err_code);
857 END IF;
858
859 IF (dim_project = 'E') THEN
860 -- Collect Project Classes
861 pa_adw_collect_dimensions.get_dim_project_classes (x_err_stage, x_err_stack, x_err_code);
862 END IF;
863
864 IF (dim_project = 'E') THEN
865 -- Collect Class Categories
866 pa_adw_collect_dimensions.get_dim_class_categories (x_err_stage, x_err_stack, x_err_code);
867 END IF;
868
869 IF (dim_project = 'E') THEN
870 -- Collect Class Codes
871 pa_adw_collect_dimensions.get_dim_class_codes (x_err_stage, x_err_stack, x_err_code);
872 END IF;
873
874 IF (dim_resource = 'E') THEN
875 -- Collect Resources
876 pa_adw_collect_dimensions.get_dim_resources (x_err_stage, x_err_stack, x_err_code);
877 END IF;
878
879 IF (dim_resource = 'E') THEN
880 -- Collect Resource Lists
881 pa_adw_collect_dimensions.get_dim_resource_lists (x_err_stage, x_err_stack, x_err_code);
882 END IF;
883
884 IF (dim_bgt_type = 'E') THEN
885 -- Collect Budget Types
886 pa_adw_collect_dimensions.get_dim_budget_types (x_err_stage, x_err_stack, x_err_code);
887 END IF;
888
889 IF (dim_time = 'E') THEN
890 -- Collect Periods
891 pa_adw_collect_dimensions.get_dim_periods (x_err_stage, x_err_stack, x_err_code);
892 END IF;
893
894 IF (dim_srvc_type = 'E') THEN
895 -- Collect Service Types
896 pa_adw_collect_dimensions.get_dim_service_types (x_err_stage, x_err_stack, x_err_code);
897 END IF;
898
899 IF (dim_project_org = 'E' OR dim_exp_org = 'E'
900 OR dim_operating_unit = 'E' ) THEN
901 -- Collect Organizations
902 pa_adw_collect_dimensions.get_dim_organizations (x_err_stage, x_err_stack, x_err_code);
903 END IF;
904
905 -- Collect Custom Dimensions
906
907 pa_adw_custom_collect.get_dimension_tables
908 (x_err_stage,
909 x_err_stack,
910 x_err_code,
911 'R'); -- refresh process
912 -- Commit Collected Dimensions
913 COMMIT;
914
915 -- Collect fact tables
916
917 -- Collect Actuals and commitments
918 pa_adw_collect_facts.get_fact_act_cmts
919 (NULL, -- x_project_num_from
920 NULL, -- x_project_num_to
921 x_err_stage,
922 x_err_stack,
923 x_err_code);
924
925 -- Collect Budgets
926 pa_adw_collect_facts.get_fact_budgets
927 (NULL, -- x_project_num_from
928 NULL, -- x_project_num_to
929 x_err_stage,
930 x_err_stack,
931 x_err_code);
932
933 -- Collect Custom Fact Tables
934 pa_adw_custom_collect.get_fact_tables
935 (NULL, -- x_project_num_from
936 NULL, -- x_project_num_to
937 x_err_stage,
938 x_err_stack,
939 x_err_code,
940 'R'); -- refresh process
941 -- Commit Collected Fact Tables
942 COMMIT;
943
944 x_err_stack := x_old_err_stack;
945
946 EXCEPTION
947 WHEN OTHERS THEN
948 x_err_code := SQLCODE;
949 pa_debug.debug('Exception Generated By Oracle Error: ' || SQLERRM(SQLCODE),
950 pa_debug.DEBUG_LEVEL_EXCEPTION);
951 ROLLBACK WORK;
952 return;
953 END ref_dim_and_fact_main;
954
955 END PA_ADW_COLLECT_MAIN;