APPS.IRC_SKILLS_MATCHING_PKG SQL Statements

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

Line: 28

    SELECT pc.name,
       pc.competence_id,
       pc.rating_scale_id,
       MAX(minpr.STEP_VALUE) highest_min_level,
       MIN(maxpr.STEP_VALUE) lowest_max_level,
       pce.MANDATORY
     FROM per_competences pc,
       per_competence_elements pce,
       per_rating_levels minpr,
       per_rating_levels maxpr
     WHERE pc.competence_id = pce.competence_id
     AND   pce.PROFICIENCY_LEVEL_ID = minpr.rating_level_id(+)
     AND   pce.HIGH_PROFICIENCY_LEVEL_ID = maxpr.rating_level_id(+)
     AND   pce.type = 'REQUIREMENT'
     AND   trunc(CP_EFF_DATE)  between trunc(pce.effective_date_from) and trunc (nvl(pce.effective_date_to,CP_EFF_DATE))
     AND (       pce.job_id            = CP_JOB
              or pce.POSITION_ID       = CP_POS
              or pce.ORGANIZATION_ID   = CP_ORG
              or pce.ENTERPRISE_ID     = CP_BGP)
     AND pc.business_group_id is null
     GROUP BY pc.competence_id,
              pc.name,
              pce.MANDATORY,
              pc.rating_scale_id;

Line: 57

      SELECT rating_level_id --, step_value, name as level_name
      FROM per_rating_levels
      WHERE rating_scale_id = CP_RATE_SCALE
      AND      ( step_value = CP_STEP_VALUE1 or step_value = CP_STEP_VALUE2)
      ORDER BY step_value;

Line: 66

      SELECT rating_level_id --, step_value, name as level_name
      FROM per_rating_levels
      WHERE competence_id = CP_COMPETENCE_ID
      AND      ( step_value = CP_STEP_VALUE1 or step_value = CP_STEP_VALUE2)
      ORDER BY step_value;

Line: 74

    SELECT organization_id,business_group_id,position_id,job_id
    FROM per_all_vacancies
    WHERE vacancy_id = CP_VACANCY
    AND    CP_DATE between nvl(date_from,trunc(SYSDATE))
                        and nvl(date_to,hr_api.g_eot);

Line: 205

  l_sql_select VARCHAR2(32000) :='';

Line: 271

     l_sql_select  :=' SELECT  count(*)';

Line: 284

         l_sql_where := l_sql_where|| ' AND  exists (select 1 from per_rating_levels  where rating_level_id=pce.proficiency_level_id'||
                 '  and step_value between ' || table_of_ids(l).min_level_num || ' and ' ||
  		      table_of_ids(l).max_level_num || ' ) )';

Line: 289

         l_sql_where := l_sql_where|| ' AND  exists (select 1 from per_rating_levels  where rating_level_id=pce.proficiency_level_id'||
                 '  and ' || '(step_value >= ' || table_of_ids(l).min_level_num  || ' ) ) )';

Line: 293

         l_sql_where := l_sql_where|| ' AND  exists (select 1 from per_rating_levels  where rating_level_id=pce.proficiency_level_id'||
                 '  and ' || '(step_value <= ' || table_of_ids(l).max_level_num || ' ) ) )';

Line: 302

     l_sql_query := l_sql_select||l_sql_from||l_sql_where;

Line: 351

    l_sql_select  :='SELECT  rl.rating_level_id,rl.step_value';

Line: 354

    l_sql_query := l_sql_select||l_sql_from||l_sql_where;

Line: 381

    l_temp_tab.delete;

Line: 417

    l_temp_tab.delete;

Line: 643

  select competence_id,
         nvl(mandatory,'N')                as mandatory,
         nvl(proficiency_level_id,-1)      as proficiency_level_id,
         nvl(high_proficiency_level_id,-1) as high_proficiency_level_id
  from per_competence_elements
  where object_name = 'VACANCY'
  and object_id = CP_VACANCY_ID;