[Home] [Help]
10: --
11: -- declare global cursor for total essential/desired count
12: CURSOR csr_total_ess_des_count(p_mandatory in varchar2) IS
13: SELECT count(competence_id)
14: FROM per_suitmatch_comp smtmp
15: where smtmp.mandatory = (p_mandatory);
16:
17: -- declare global cursor for essential/desired match count
18: CURSOR csr_ess_des_match_count(p_person_id in number, p_mandatory in varchar2)IS
18: CURSOR csr_ess_des_match_count(p_person_id in number, p_mandatory in varchar2)IS
19: select /*+ leading(smtmp) index(pce, PER_COMPETENCE_ELEMENTS_FK7) index(r1, PER_RATING_LEVELS_PK) */
20: count(pce.competence_id)
21: FROM per_competence_elements pce,
22: per_suitmatch_comp smtmp,
23: per_rating_levels r1
24: where pce.competence_id = smtmp.competence_id
25: AND nvl(r1.step_value,-1) >= nvl(smtmp.min_step_value, -1)
26: AND pce.type = 'PERSONAL'
85: p_person_id in number
86: ,p_mandatory in varchar2) IS
87: SELECT /*+ leading(smtmp) index(pcep, PER_COMPETENCE_ELEMENTS_FK7) index(r2, PER_RATING_LEVELS_PK) */
88: count(pcep.competence_id)
89: FROM per_suitmatch_comp smtmp,
90: per_competence_elements pcep,
91: per_rating_levels r2
92: WHERE pcep.person_id = p_person_id
93: AND smtmp.competence_id = pcep.competence_id
128: -- with competencies for person and work opp coming from temp table
129: CURSOR csr_tmp_ed_match_count(
130: p_mandatory in varchar2) IS
131: SELECT count(ptmp.competence_id)
132: FROM per_suitmatch_comp smtmp,
133: per_suitmatch_person ptmp
134: WHERE smtmp.competence_id = ptmp.competence_id
135: AND nvl(ptmp.min_step_value, -1) >= nvl(smtmp.min_step_value, -1)
136: AND smtmp.mandatory = p_mandatory;
372: END LOOP;
373: END LOOP;
374:
375: -- Now insert the competencies into temp table.
376: DELETE FROM per_suitmatch_comp;
377: FOR L IN 1 ..l_mat_comp_table.count LOOP
378: IF (l_mat_comp_table(L).mandatory <> 'I') THEN
379: insert into per_suitmatch_comp (competence_id,
380: mandatory, min_step_value)
375: -- Now insert the competencies into temp table.
376: DELETE FROM per_suitmatch_comp;
377: FOR L IN 1 ..l_mat_comp_table.count LOOP
378: IF (l_mat_comp_table(L).mandatory <> 'I') THEN
379: insert into per_suitmatch_comp (competence_id,
380: mandatory, min_step_value)
381: values (to_number(l_mat_comp_table(L).competence_id)
382: ,l_mat_comp_table(L).mandatory
383: ,to_number(l_mat_comp_table(L).low_step_value));
585: )
586: IS
587: BEGIN
588:
589: DELETE FROM per_suitmatch_comp;
590: FOR I IN 1 ..p_temp_tab.count LOOP
591: insert into per_suitmatch_comp (competence_id,
592: mandatory, min_step_value)
593: values (to_number(p_temp_tab(I).competence_id)
587: BEGIN
588:
589: DELETE FROM per_suitmatch_comp;
590: FOR I IN 1 ..p_temp_tab.count LOOP
591: insert into per_suitmatch_comp (competence_id,
592: mandatory, min_step_value)
593: values (to_number(p_temp_tab(I).competence_id)
594: ,p_temp_tab(I).mandatory
595: ,to_number(p_temp_tab(I).min_step_value));