[Home] [Help]
391: /* Get the rule id from global temporary table for a given object_id */
392: CURSOR get_rule_id_tmp (l_proj_id IN NUMBER,
393: l_measure_id IN NUMBER) IS
394: SELECT rule_id
395: FROM pa_perf_temp_obj_measure
396: WHERE object_id = l_proj_id
397: AND object_type = 'PA_PROJECTS'
398: AND measure_id = l_measure_id;
399:
399:
400: /* Get the measure id from global temporary table for a given object_id */
401: CURSOR get_measure_id (l_object_id IN NUMBER) IS
402: SELECT DISTINCT measure_id
403: FROM pa_perf_temp_obj_measure
404: WHERE object_type = 'PA_PROJECTS'
405: AND object_id = l_object_id;
406:
407: /* Get the object id from global temporary table */
406:
407: /* Get the object id from global temporary table */
408: CURSOR get_object_id IS
409: SELECT DISTINCT object_id
410: FROM pa_perf_temp_obj_measure
411: WHERE object_type = 'PA_PROJECTS'
412: ;
413:
414: /* Get the list of Business Event code from temporary table */
413:
414: /* Get the list of Business Event code from temporary table */
415: CURSOR bz_ent_code IS
416: SELECT distinct bz_ent_code
417: FROM pa_perf_temp_obj_measure
418: WHERE object_type = 'PA_PROJECTS'
419: AND bz_ent_code is not null;
420:
421: BEGIN
430: pa_debug.write_file('LOG', pa_debug.g_err_stage);
431: END IF;
432:
433: -- Remove the data in temp table
434: EXECUTE IMMEDIATE ('delete from pa_perf_temp_obj_measure') ;
435:
436:
437: /*** Begin business logic to generate the Performance Transaction ***/
438:
440: IF P_PA_DEBUG_MODE = 'Y' THEN
441: PA_DEBUG.write_file('LOG', 'Number of Projects to be processed is : '||p_project_list.COUNT);
442: END IF;
443: FOR i IN p_project_list.FIRST .. p_project_list.LAST LOOP
444: -- Step 1: Populate PA_PERF_TEMP_OBJ_MEASURE (temp table)
445: -- get all the rules attached to the project
446:
447: OPEN get_rule_id (p_project_list(i));
448: FETCH get_rule_id bulk collect INTO l_rule_list;
450:
451: IF l_rule_list.COUNT <> 0 THEN
452:
453: IF P_PA_DEBUG_MODE = 'Y' THEN
454: PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_TEMP_OBJ_MEASURE temp table for Project: '||p_project_list(i));
455: PA_DEBUG.write_file('LOG', 'Number of Rule IDs : '||l_rule_list.COUNT);
456: END IF;
457:
458: FOR j IN l_rule_list.FIRST .. l_rule_list.LAST LOOP
484: WHERE measure_id = l_measure_id
485: AND rownum = 1;
486: END IF;
487:
488: INSERT INTO pa_perf_temp_obj_measure
489: ( object_type
490: ,object_id
491: ,measure_id
492: ,measure_value
510: ELSE
511:
512: l_bz_ent_code := p_business_event_code;
513:
514: INSERT INTO pa_perf_temp_obj_measure
515: ( object_type
516: ,object_id
517: ,measure_id
518: ,measure_value
558:
559: commit; --Save the work after inserting all the projects into temp table
560:
561: IF P_PA_DEBUG_MODE = 'Y' THEN
562: PA_DEBUG.write_file('LOG', 'End of Step 1 .. Inserting into PA_PERF_TEMP_OBJ_MEASURE temp table');
563: END IF;
564:
565: --- Step 2: Call Business Event API to get the value of measure_id
566: --- for a given project_id. This API will do the update on the temp
656:
657: END LOOP; --end loop for l_bz_code_list
658: ELSE
659: IF P_PA_DEBUG_MODE = 'Y' THEN -- Added for Bug 4324824
660: PA_DEBUG.write_file('LOG', 'No Business Event code selected from PA_PERF_TEMP_OBJ_MEASURE temp table.');
661: END IF;
662: END IF;
663: -- End of Step 2
664:
741: END IF;
742: -- Get the calendar_type associated with the rule_id and measure_id
743: SELECT calendar_type
744: INTO l_period_type
745: FROM pa_perf_temp_obj_measure
746: WHERE object_id = l_object_list(i)
747: AND object_type = 'PA_PROJECTS'
748: AND measure_id = l_measure_list(j)
749: AND rule_id = l_rule_list(k);
764: l_kpa_code, l_precision, l_currency_type, l_measure_format,
765: l_rule_type
766: FROM pa_perf_rules ppr,
767: pa_perf_object_rules ppor,
768: pa_perf_temp_obj_measure pptom
769: WHERE pptom.object_id = l_object_list(i)
770: AND pptom.object_id = ppor.object_id
771: AND ppor.object_type = 'PA_PROJECTS'
772: AND pptom.measure_id = l_measure_list(j)
776: AND ppor.rule_id = pptom.rule_id
777: AND rownum = 1;
778:
779: IF P_PA_DEBUG_MODE = 'Y' THEN
780: PA_DEBUG.write_file('LOG', 'After selecting values from PA_PERF_TEMP_OBJ_MEASURE temp table' );
781: END IF;
782:
783: -- If measure value is null for a given measure_id and there is associated API
784: -- then call the API to get the measure value
871: pa_debug.write_file('LOG','Period Name : '||l_period_name);
872: END IF;
873:
874: IF l_measure_value is NOT NULL THEN
875: -- Update the measure value on pa_perf_temp_obj_measure table
876: UPDATE pa_perf_temp_obj_measure
877: SET measure_value = l_measure_value,
878: period_name = l_period_name
879: WHERE object_id = l_object_list(i)
872: END IF;
873:
874: IF l_measure_value is NOT NULL THEN
875: -- Update the measure value on pa_perf_temp_obj_measure table
876: UPDATE pa_perf_temp_obj_measure
877: SET measure_value = l_measure_value,
878: period_name = l_period_name
879: WHERE object_id = l_object_list(i)
880: AND measure_id = l_measure_list(j)
880: AND measure_id = l_measure_list(j)
881: AND rule_id = l_rule_list(k)
882: AND object_type = 'PA_PROJECTS';
883: IF P_PA_DEBUG_MODE = 'Y' THEN
884: pa_debug.write_file('LOG','Updating PA_PERF_TEMP_OBJ_MEASURE () with success');
885: END IF;
886: END IF;
887: dbms_sql.close_cursor(l_cursor);
888: END IF;
899: END IF; --end if for measure values is not null
900:
901: SELECT measure_value
902: INTO l_measure_value
903: FROM pa_perf_temp_obj_measure
904: WHERE object_id = l_object_list(i)
905: AND measure_id = l_measure_list(j)
906: AND object_type = 'PA_PROJECTS'
907: AND rule_id = l_rule_list(k)
1012: END IF;
1013: -- End of Step 3 --
1014:
1015: -- Remove the data in temp table
1016: -- EXECUTE IMMEDIATE ('delete from pa_perf_temp_obj_measure') ;
1017: IF P_PA_DEBUG_MODE = 'Y' THEN
1018: pa_debug.write_file('LOG','Deleting records from PA_PERF_TEMP_OBJ_MEASURE () with success');
1019: END IF;
1020:
1014:
1015: -- Remove the data in temp table
1016: -- EXECUTE IMMEDIATE ('delete from pa_perf_temp_obj_measure') ;
1017: IF P_PA_DEBUG_MODE = 'Y' THEN
1018: pa_debug.write_file('LOG','Deleting records from PA_PERF_TEMP_OBJ_MEASURE () with success');
1019: END IF;
1020:
1021: -- Clean the Interface Table PA_PER_BZ_OBJECT --
1022: