[Home] [Help]
3:
4: -- declare global cursor for total essential/desired count
5: CURSOR csr_total_ess_des_count(p_mandatory in varchar2) IS
6: SELECT count(competence_id)
7: FROM per_suitmatch_comp smtmp
8: where smtmp.mandatory = (p_mandatory);
9:
10: -- declare global cursor for essential/desired match count
11: CURSOR csr_ess_des_match_count(p_person_id in number, p_mandatory in varchar2)IS
11: CURSOR csr_ess_des_match_count(p_person_id in number, p_mandatory in varchar2)IS
12: select /*+ leading(smtmp) index(pce, PER_COMPETENCE_ELEMENTS_FK7) index(r1, PER_RATING_LEVELS_PK) */
13: count(pce.competence_id)
14: FROM per_competence_elements pce,
15: per_suitmatch_comp smtmp,
16: per_rating_levels r1
17: where pce.competence_id = smtmp.competence_id
18: AND nvl(r1.step_value,-1) >= nvl(smtmp.min_step_value, -1)
19: AND pce.type = 'PERSONAL'
78: p_person_id in number
79: ,p_mandatory in varchar2) IS
80: SELECT /*+ leading(smtmp) index(pcep, PER_COMPETENCE_ELEMENTS_FK7) index(r2, PER_RATING_LEVELS_PK) */
81: count(pcep.competence_id)
82: FROM per_suitmatch_comp smtmp,
83: per_competence_elements pcep,
84: per_rating_levels r2
85: WHERE pcep.person_id = p_person_id
86: AND smtmp.competence_id = pcep.competence_id
121: -- with competencies for person and work opp coming from temp table
122: CURSOR csr_tmp_ed_match_count(
123: p_mandatory in varchar2) IS
124: SELECT count(ptmp.competence_id)
125: FROM per_suitmatch_comp smtmp,
126: per_suitmatch_person ptmp
127: WHERE smtmp.competence_id = ptmp.competence_id
128: AND nvl(ptmp.min_step_value, -1) >= nvl(smtmp.min_step_value, -1)
129: AND smtmp.mandatory = p_mandatory;
366: END LOOP;
367:
368: -- Now insert the competencies into temp table.
369: --truncate the table before inserting
370: execute immediate 'truncate table '||getTableSchema||'.per_suitmatch_comp';
371: FOR L IN 1 ..l_mat_comp_table.count LOOP
372: IF (l_mat_comp_table(L).mandatory <> 'I') THEN
373: insert into per_suitmatch_comp (competence_id,
374: mandatory, min_step_value)
369: --truncate the table before inserting
370: execute immediate 'truncate table '||getTableSchema||'.per_suitmatch_comp';
371: FOR L IN 1 ..l_mat_comp_table.count LOOP
372: IF (l_mat_comp_table(L).mandatory <> 'I') THEN
373: insert into per_suitmatch_comp (competence_id,
374: mandatory, min_step_value)
375: values (to_number(l_mat_comp_table(L).competence_id)
376: ,l_mat_comp_table(L).mandatory
377: ,to_number(l_mat_comp_table(L).low_step_value));
579: )
580: IS
581: BEGIN
582: --truncate the table before inserting
583: execute immediate 'truncate table '||getTableSchema||'.per_suitmatch_comp';
584: FOR I IN 1 ..p_temp_tab.count LOOP
585: insert into per_suitmatch_comp (competence_id,
586: mandatory, min_step_value)
587: values (to_number(p_temp_tab(I).competence_id)
581: BEGIN
582: --truncate the table before inserting
583: execute immediate 'truncate table '||getTableSchema||'.per_suitmatch_comp';
584: FOR I IN 1 ..p_temp_tab.count LOOP
585: insert into per_suitmatch_comp (competence_id,
586: mandatory, min_step_value)
587: values (to_number(p_temp_tab(I).competence_id)
588: ,p_temp_tab(I).mandatory
589: ,to_number(p_temp_tab(I).min_step_value));