eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_CPR_UTILITY dependencies on PER_COMPETENCE_ELEMENTS

Line 8: p_comp_id in per_competence_elements.competence_id%type,

4: g_package varchar2(33) := ' ota_cpr_utility.'; -- Global package name
5:
6: FUNCTION is_competency_acheived
7: (p_person_id in per_all_people_f.person_id%type,
8: p_comp_id in per_competence_elements.competence_id%type,
9: p_level_id in per_competence_elements.proficiency_level_id%type
10: ) RETURN varchar2 is
11:
12: Cursor CompRecordWithLevel is

Line 9: p_level_id in per_competence_elements.proficiency_level_id%type

5:
6: FUNCTION is_competency_acheived
7: (p_person_id in per_all_people_f.person_id%type,
8: p_comp_id in per_competence_elements.competence_id%type,
9: p_level_id in per_competence_elements.proficiency_level_id%type
10: ) RETURN varchar2 is
11:
12: Cursor CompRecordWithLevel is
13: Select nvl(pce.proficiency_level_id, -100)

Line 14: from per_competence_elements pce, per_rating_levels prl

10: ) RETURN varchar2 is
11:
12: Cursor CompRecordWithLevel is
13: Select nvl(pce.proficiency_level_id, -100)
14: from per_competence_elements pce, per_rating_levels prl
15: where pce.competence_id = p_comp_id
16: and pce.person_id = p_person_id
17: and prl.rating_level_id(+) = pce.proficiency_level_id
18: and prl.step_value >= (select step_value from per_rating_levels where rating_level_id = p_level_id)

Line 23: from per_competence_elements pce

19: and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)));
20:
21: Cursor CompRecordWithNullLevel is
22: Select nvl(pce.proficiency_level_id, -100)
23: from per_competence_elements pce
24: where pce.competence_id = p_comp_id
25: and pce.person_id = p_person_id
26: and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)))
27: and pce.proficiency_level_id is null;

Line 31: from per_competence_elements pce

27: and pce.proficiency_level_id is null;
28:
29: Cursor CompRecordWithoutLevel is
30: Select nvl(pce.proficiency_level_id, -100)
31: from per_competence_elements pce
32: where pce.competence_id = p_comp_id
33: and pce.person_id = p_person_id
34: and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)));
35:

Line 36: l_level_id per_competence_elements.proficiency_level_id%type;

32: where pce.competence_id = p_comp_id
33: and pce.person_id = p_person_id
34: and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)));
35:
36: l_level_id per_competence_elements.proficiency_level_id%type;
37:
38: Begin
39: --Note: l_level_id = -100 means Data found but level is null
40:

Line 79: p_comp_id in per_competence_elements.competence_id%type,

75:
76:
77: FUNCTION check_learner_comp_step_values
78: (p_act_ver_id in ota_activity_versions.activity_version_id%type,
79: p_comp_id in per_competence_elements.competence_id%type,
80: p_level_id in per_competence_elements.proficiency_level_id%type
81: ) RETURN varchar2 is
82:
83: Cursor GetLrnCompMaxStepValue is

Line 80: p_level_id in per_competence_elements.proficiency_level_id%type

76:
77: FUNCTION check_learner_comp_step_values
78: (p_act_ver_id in ota_activity_versions.activity_version_id%type,
79: p_comp_id in per_competence_elements.competence_id%type,
80: p_level_id in per_competence_elements.proficiency_level_id%type
81: ) RETURN varchar2 is
82:
83: Cursor GetLrnCompMaxStepValue is
84: SELECT

Line 89: PER_COMPETENCE_ELEMENTS PCE

85: Decode(PRL.STEP_VALUE, null, -1, PRL.STEP_VALUE),
86: PCE.COMPETENCE_ELEMENT_ID
87: FROM
88: PER_RATING_LEVELS PRL,
89: PER_COMPETENCE_ELEMENTS PCE
90: WHERE
91: PCE.PROFICIENCY_LEVEL_ID = PRL.RATING_LEVEL_ID (+)
92: and (trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate)) and nvl(pce.effective_date_to, trunc(sysdate)))
93: AND PCE.type = 'DELIVERY'

Line 135: l_lrn_comp_el_id per_competence_elements.competence_element_id%type;

131: OR pce.competence_id = prl.competence_id)
132: AND pce.competence_id = p_comp_id;
133:
134:
135: l_lrn_comp_el_id per_competence_elements.competence_element_id%type;
136: l_lrn_step_value per_rating_levels.step_value%type;
137:
138: l_prereq_step_value per_rating_levels.step_value%type;
139: l_comp_max_step_value per_rating_levels.step_value%type;

Line 290: FROM per_competence_elements cpe

286:
287: Cursor c_get_mandatory_competencies is
288: SELECT cpe.competence_id,
289: cpe.proficiency_level_id
290: FROM per_competence_elements cpe
291: WHERE cpe.object_id = p_act_ver_id
292: and cpe.mandatory = 'Y'
293: and cpe.type = 'OTA_COMP_PREREQ';
294:

Line 350: FROM per_competence_elements cpe

346:
347: Cursor c_get_advisory_competencies is
348: SELECT cpe.competence_id,
349: cpe.proficiency_level_id
350: FROM per_competence_elements cpe
351: WHERE cpe.object_id = p_act_ver_id
352: and ( cpe.mandatory = 'N' or cpe.mandatory is null )
353: and cpe.type = 'OTA_COMP_PREREQ';
354:

Line 535: FROM per_competence_elements cpe

531: IS
532: Cursor c_get_mandatory_competencies is
533: SELECT cpe.competence_id,
534: cpe.proficiency_level_id
535: FROM per_competence_elements cpe
536: WHERE cpe.object_id = p_act_ver_id
537: and cpe.mandatory = 'Y'
538: and cpe.type = 'OTA_COMP_PREREQ';
539:

Line 599: ,p_comp_id in per_competence_elements.competence_id%type

595:
596: Function Get_comp_prereq_count
597: (p_prereq_met varchar2
598: ,p_event_id in ota_events.event_id%type
599: ,p_comp_id in per_competence_elements.competence_id%type
600: ,p_level_id in per_competence_elements.proficiency_level_id%type
601: ) Return varchar2 is
602: --
603: --

Line 600: ,p_level_id in per_competence_elements.proficiency_level_id%type

596: Function Get_comp_prereq_count
597: (p_prereq_met varchar2
598: ,p_event_id in ota_events.event_id%type
599: ,p_comp_id in per_competence_elements.competence_id%type
600: ,p_level_id in per_competence_elements.proficiency_level_id%type
601: ) Return varchar2 is
602: --
603: --
604: Cursor c_comp_prereq_status is

Line 638: p_comp_id in per_competence_elements.competence_id%type,

634:
635: FUNCTION get_prereq_met_count
636: (p_event_id in ota_events.event_id%type,
637: p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
638: p_comp_id in per_competence_elements.competence_id%type,
639: p_level_id in per_competence_elements.proficiency_level_id%type
640: ) RETURN varchar2 is
641: --
642: l_met_count Varchar2(15) := '0';

Line 639: p_level_id in per_competence_elements.proficiency_level_id%type

635: FUNCTION get_prereq_met_count
636: (p_event_id in ota_events.event_id%type,
637: p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
638: p_comp_id in per_competence_elements.competence_id%type,
639: p_level_id in per_competence_elements.proficiency_level_id%type
640: ) RETURN varchar2 is
641: --
642: l_met_count Varchar2(15) := '0';
643: --

Line 674: p_comp_id in per_competence_elements.competence_id%type,

670:
671: FUNCTION get_prereq_not_met_count
672: (p_event_id in ota_events.event_id%type,
673: p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
674: p_comp_id in per_competence_elements.competence_id%type,
675: p_level_id in per_competence_elements.proficiency_level_id%type
676: ) RETURN varchar2 is
677:
678: l_not_met_cnt Number := 0;

Line 675: p_level_id in per_competence_elements.proficiency_level_id%type

671: FUNCTION get_prereq_not_met_count
672: (p_event_id in ota_events.event_id%type,
673: p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
674: p_comp_id in per_competence_elements.competence_id%type,
675: p_level_id in per_competence_elements.proficiency_level_id%type
676: ) RETURN varchar2 is
677:
678: l_not_met_cnt Number := 0;
679: --