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.HR_SUIT_MATCH_UTIL_SS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 13


     SELECT count(competence_id)
     FROM per_suitmatch_comp smtmp
     where smtmp.mandatory = (p_mandatory);

Line: 19


     select /*+ leading(smtmp) index(pce, PER_COMPETENCE_ELEMENTS_FK7) index(r1, PER_RATING_LEVELS_PK) */
          count(pce.competence_id)
     FROM per_competence_elements pce,
          per_suitmatch_comp smtmp,
          per_rating_levels r1
     where pce.competence_id = smtmp.competence_id
     AND nvl(r1.step_value,-1) >= nvl(smtmp.min_step_value, -1)
     AND pce.type = 'PERSONAL'
     AND trunc(sysdate) BETWEEN pce.effective_date_from AND
        NVL(pce.effective_date_to, trunc(sysdate))
     AND pce.proficiency_level_id = r1.rating_level_id(+)
     AND pce.person_id = p_person_id
     AND smtmp.mandatory = p_mandatory;

Line: 42


SELECT count(pcep.competence_id)
FROM   per_competence_elements pce,
       per_competence_elements pcep,
       per_rating_levels r1,
       per_rating_levels r2
WHERE  pce.type = 'REQUIREMENT'
AND    trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
       and nvl(pce.effective_date_to, trunc(sysdate))
AND    pce.proficiency_level_id = r1.rating_level_id(+)
AND    (pce.enterprise_id = p_enterprise_id or pce.organization_id = p_organization_id or
        pce.job_id = p_job_id or pce.position_id = p_position_id)
AND    pcep.person_id = p_person_id
AND    pce.competence_id = pcep.competence_id
AND    pcep.type = 'PERSONAL'
AND    trunc(sysdate) BETWEEN pcep.effective_date_from AND
          NVL(pcep.effective_date_to, trunc(sysdate))
AND    pcep.proficiency_level_id = r2.rating_level_id(+)
AND    nvl(r2.step_value, -1) >= nvl(r1.step_value, -1)
AND    pce.mandatory = p_mandatory;

Line: 71


SELECT count(pce.competence_id)
FROM   per_competence_elements pce
WHERE  pce.type = 'REQUIREMENT'
AND    trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
       and nvl(pce.effective_date_to, trunc(sysdate))
AND    (pce.enterprise_id = p_enterprise_id or pce.organization_id = p_organization_id or
        pce.job_id = p_job_id or pce.position_id = p_position_id)
AND    pce.mandatory = p_mandatory;

Line: 87


SELECT /*+ leading(smtmp) index(pcep, PER_COMPETENCE_ELEMENTS_FK7) index(r2, PER_RATING_LEVELS_PK) */
       count(pcep.competence_id)
FROM   per_suitmatch_comp smtmp,
       per_competence_elements pcep,
       per_rating_levels r2
WHERE  pcep.person_id = p_person_id
AND    smtmp.competence_id = pcep.competence_id
AND    pcep.type = 'PERSONAL'
AND    trunc(sysdate) BETWEEN pcep.effective_date_from AND
          NVL(pcep.effective_date_to, trunc(sysdate))
AND    pcep.proficiency_level_id = r2.rating_level_id(+)
AND    nvl(r2.step_value, -1) >= nvl(smtmp.min_step_value, -1)
AND    smtmp.mandatory = p_mandatory;

Line: 111


SELECT /*+ leading(ptmp) index(pce, PER_COMPETENCE_ELEMENTS_N1) index(r1, PER_RATING_LEVELS_PK) */
       count(ptmp.competence_id)
FROM   per_competence_elements pce,
       per_suitmatch_person ptmp,
       per_rating_levels r1
WHERE  pce.type = 'REQUIREMENT'
AND    trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
       and nvl(pce.effective_date_to, trunc(sysdate))
AND    pce.proficiency_level_id = r1.rating_level_id(+)
AND    (pce.enterprise_id = p_enterprise_id or pce.organization_id = p_organization_id or
        pce.job_id = p_job_id or pce.position_id = p_position_id)
AND    pce.competence_id = ptmp.competence_id
AND    nvl(ptmp.min_step_value, -1) >= nvl(r1.step_value, -1)
AND    pce.mandatory = p_mandatory;

Line: 131


SELECT count(ptmp.competence_id)
FROM   per_suitmatch_comp smtmp,
       per_suitmatch_person ptmp
WHERE  smtmp.competence_id = ptmp.competence_id
AND    nvl(ptmp.min_step_value, -1) >= nvl(smtmp.min_step_value, -1)
AND    smtmp.mandatory = p_mandatory;

Line: 141


SELECT hpf.position_id,
       hpf.organization_id,
       hpf.job_id
FROM hr_all_positions_f hpf
WHERE TRUNC(sysdate) BETWEEN hpf.effective_start_date
AND hpf.effective_end_date
AND TRUNC(sysdate) BETWEEN hpf.date_effective
AND NVL(hpf.date_end, TRUNC(sysdate))
AND (hpf.status is null OR hpf.status <> 'INVALID')
AND hpf.position_id = p_position_id;

Line: 154


SELECT pv.vacancy_id,
       pv.organization_id,
       pv.job_id,
       pv.position_id
FROM per_all_vacancies pv
WHERE TRUNC(sysdate) BETWEEN pv.date_from
AND NVL(pv.date_to, TRUNC(sysdate))
AND pv.vacancy_id = p_vacancy_id;

Line: 222


SELECT pce.competence_id,
       r1.step_value low_step_value,
       r2.step_value high_step_value,
       pce.mandatory,
       'BUS' lookup_code
FROM   per_competence_elements pce,
       per_rating_levels r1,
       per_rating_levels r2
WHERE  pce.type = 'REQUIREMENT'
AND    trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
       and nvl(pce.effective_date_to, trunc(sysdate))
AND    pce.proficiency_level_id = r1.rating_level_id(+)
AND    pce.high_proficiency_level_id = r2.rating_level_id(+)
AND    pce.enterprise_id = p_enterprise_id
UNION ALL
SELECT pce.competence_id,
       r1.step_value low_step_value,
       r2.step_value high_step_value,
       pce.mandatory,
       'ORG' lookup_code
FROM   per_competence_elements pce,
       per_rating_levels r1,
       per_rating_levels r2
WHERE  pce.type = 'REQUIREMENT'
AND    trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
       and nvl(pce.effective_date_to, trunc(sysdate))
AND    pce.proficiency_level_id = r1.rating_level_id(+)
AND    pce.high_proficiency_level_id = r2.rating_level_id(+)
AND    pce.organization_id = p_organization_id
UNION ALL
SELECT pce.competence_id,
       r1.step_value low_step_value,
       r2.step_value high_step_value,
       pce.mandatory,
       'JOB' lookup_code
FROM   per_competence_elements pce,
       per_rating_levels r1,
       per_rating_levels r2
WHERE  pce.type = 'REQUIREMENT'
AND    trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
       and nvl(pce.effective_date_to, trunc(sysdate))
AND    pce.proficiency_level_id = r1.rating_level_id(+)
AND    pce.high_proficiency_level_id = r2.rating_level_id(+)
AND    pce.job_id = p_job_id
UNION ALL
SELECT pce.competence_id,
       r1.step_value low_step_value,
       r2.step_value high_step_value,
       pce.mandatory,
       'POS' lookup_code
FROM   per_competence_elements pce,
       per_rating_levels r1,
       per_rating_levels r2
WHERE  pce.type = 'REQUIREMENT'
AND    trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
       and nvl(pce.effective_date_to, trunc(sysdate))
AND    pce.proficiency_level_id = r1.rating_level_id(+)
AND    pce.high_proficiency_level_id = r2.rating_level_id(+)
AND    pce.position_id = p_position_id;

Line: 376


    DELETE FROM per_suitmatch_comp;

Line: 379


	  insert into per_suitmatch_comp (competence_id,
          mandatory, min_step_value)
	  values (to_number(l_mat_comp_table(L).competence_id)
		 ,l_mat_comp_table(L).mandatory
	         ,to_number(l_mat_comp_table(L).low_step_value));

Line: 589


    DELETE FROM per_suitmatch_comp;

Line: 591


      insert into per_suitmatch_comp (competence_id,
      mandatory, min_step_value)
      values (to_number(p_temp_tab(I).competence_id)
             ,p_temp_tab(I).mandatory
	     ,to_number(p_temp_tab(I).min_step_value));

Line: 606


SELECT /*+ leading(pcp) index(pce, PER_COMPETENCE_ELEMENTS_N1) */
       decode(pce.enterprise_id, null,
       (decode(pce.organization_id, null,
        (decode(pce.job_id, null,
         (decode(pce.position_id,null,
            -1, pce.position_id)),pce.job_id)),
            pce.organization_id)),pce.enterprise_id) workopp_id,
       decode(pce.enterprise_id, null,
       (decode(pce.organization_id, null,
        (decode(pce.job_id, null,
         (decode(pce.position_id, null,
           'SM','POS')),'JOB')),'ORG')),'BUS') type,
       ppf.person_id
FROM   per_competence_elements pce,
       per_suitmatch_person pcp,
       per_all_people_f ppf
WHERE  pce.type = 'REQUIREMENT'
AND    trunc(sysdate) between nvl(pce.effective_date_from, trunc(sysdate))
       and nvl(pce.effective_date_to, trunc(sysdate))
AND    pce.business_group_id = decode(hr_general.get_xbg_profile,
                               'Y', pce.business_group_id, ppf.business_group_id)
AND    pce.competence_id = pcp.competence_id
AND    pcp.mandatory = p_person_id
AND    ppf.person_id = pcp.mandatory
AND    trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
AND    pce.object_name is null
GROUP BY pce.enterprise_id, pce.organization_id, pce.job_id, pce.position_id, ppf.person_id
ORDER BY type;

Line: 649


    DELETE FROM per_suitmatch_person;

Line: 652


      insert into per_suitmatch_person (competence_id,
      mandatory, min_step_value)
      values (to_number(p_temp_tab(I).competence_id)
             ,p_temp_tab(I).mandatory
	     ,to_number(p_temp_tab(I).min_step_value));

Line: 669


   DELETE FROM per_suitmatch_workopps;

Line: 713


      insert into per_suitmatch_workopps (workopp_id,
      type, ess_match, des_match, meets)
      values (to_number(l_csr_workopp_tab(I).workopp_id)
             ,l_csr_workopp_tab(I).type, l_ess_match, l_des_match, l_meets);

Line: 735


    DELETE FROM per_suitmatch_workopps;

Line: 775


      insert into per_suitmatch_workopps (workopp_id,
      type, ess_match, des_match, meets)
      values (to_number(p_temp_tab(I).competence_id)
             ,p_temp_tab(I).mandatory, l_ess_match, l_des_match, l_meets);

Line: 783


PROCEDURE insert_workopp_temp_table (
    p_temp_tab IN SSHR_SM_COMP_DETAILS_TAB_TYP
)
IS
BEGIN
    DELETE FROM per_suitmatch_workopps;

Line: 790


      insert into per_suitmatch_workopps (workopp_id,
                  type)
      values (to_number(p_temp_tab(I).competence_id)
             ,p_temp_tab(I).mandatory);

Line: 797


END insert_workopp_temp_table;

Line: 1114


    SELECT name
    FROM hr_all_organization_units_tl
    WHERE organization_id = p_bg_id
    AND language = userenv('LANG');

Line: 1137


    SELECT date_received
    FROM per_applications
    WHERE person_id(+) = p_person_id
      AND (date_end is null);

Line: 1161


    SELECT date_start
    FROM per_periods_of_service
    WHERE period_of_service_id(+) = p_period_of_service_id;

Line: 1183


    SELECT date_start
    FROM per_periods_of_placement
    WHERE actual_termination_date is null OR
         (trunc(sysdate) < actual_termination_date);