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 287: FROM per_competence_elements cpe

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

Line 344: FROM per_competence_elements cpe

340:
341: Cursor c_get_advisory_competencies is
342: SELECT cpe.competence_id,
343: cpe.proficiency_level_id
344: FROM per_competence_elements cpe
345: WHERE cpe.object_id = p_act_ver_id
346: and ( cpe.mandatory = 'N' or cpe.mandatory is null )
347: and cpe.type = 'OTA_COMP_PREREQ';
348:

Line 529: FROM per_competence_elements cpe

525: IS
526: Cursor c_get_mandatory_competencies is
527: SELECT cpe.competence_id,
528: cpe.proficiency_level_id
529: FROM per_competence_elements cpe
530: WHERE cpe.object_id = p_act_ver_id
531: and cpe.mandatory = 'Y'
532: and cpe.type = 'OTA_COMP_PREREQ';
533:

Line 593: ,p_comp_id in per_competence_elements.competence_id%type

589:
590: Function Get_comp_prereq_count
591: (p_prereq_met varchar2
592: ,p_event_id in ota_events.event_id%type
593: ,p_comp_id in per_competence_elements.competence_id%type
594: ,p_level_id in per_competence_elements.proficiency_level_id%type
595: ) Return varchar2 is
596: --
597: --

Line 594: ,p_level_id in per_competence_elements.proficiency_level_id%type

590: Function Get_comp_prereq_count
591: (p_prereq_met varchar2
592: ,p_event_id in ota_events.event_id%type
593: ,p_comp_id in per_competence_elements.competence_id%type
594: ,p_level_id in per_competence_elements.proficiency_level_id%type
595: ) Return varchar2 is
596: --
597: --
598: Cursor c_comp_prereq_status is

Line 632: p_comp_id in per_competence_elements.competence_id%type,

628:
629: FUNCTION get_prereq_met_count
630: (p_event_id in ota_events.event_id%type,
631: p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
632: p_comp_id in per_competence_elements.competence_id%type,
633: p_level_id in per_competence_elements.proficiency_level_id%type
634: ) RETURN varchar2 is
635: --
636: l_met_count Varchar2(15) := '0';

Line 633: p_level_id in per_competence_elements.proficiency_level_id%type

629: FUNCTION get_prereq_met_count
630: (p_event_id in ota_events.event_id%type,
631: p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
632: p_comp_id in per_competence_elements.competence_id%type,
633: p_level_id in per_competence_elements.proficiency_level_id%type
634: ) RETURN varchar2 is
635: --
636: l_met_count Varchar2(15) := '0';
637: --

Line 668: p_comp_id in per_competence_elements.competence_id%type,

664:
665: FUNCTION get_prereq_not_met_count
666: (p_event_id in ota_events.event_id%type,
667: p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
668: p_comp_id in per_competence_elements.competence_id%type,
669: p_level_id in per_competence_elements.proficiency_level_id%type
670: ) RETURN varchar2 is
671:
672: l_not_met_cnt Number := 0;

Line 669: p_level_id in per_competence_elements.proficiency_level_id%type

665: FUNCTION get_prereq_not_met_count
666: (p_event_id in ota_events.event_id%type,
667: p_prerequisite_course_id ota_activity_versions.activity_version_id%type,
668: p_comp_id in per_competence_elements.competence_id%type,
669: p_level_id in per_competence_elements.proficiency_level_id%type
670: ) RETURN varchar2 is
671:
672: l_not_met_cnt Number := 0;
673: --