APPS.PA_SEARCH_GLOB SQL Statements

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

Line: 18

   SELECT nvl(sum(decode(mandatory,'Y',1,0)),0), nvl(sum(decode(mandatory,'N',1,0)),0) INTO x_mandatory_count, x_optional_count
     FROM per_competence_elements pce
    WHERE pce.object_name = 'OPEN_ASSIGNMENT'
      AND pce.object_id = p_requirement_id;

Line: 28

      SELECT nvl(sum(decode(pce.mandatory,'Y',1,0)),0), nvl(sum(decode(pce.mandatory,'N',1,0)),0) INTO x_mandatory_match, x_optional_match
        FROM per_competence_elements pce,
             per_competence_elements pce2,
             per_rating_levels prl,
             per_rating_levels prl2
       WHERE pce.object_id = p_requirement_id
         AND pce.object_name = 'OPEN_ASSIGNMENT'
         AND pce.proficiency_level_id = prl.rating_level_id(+)
         AND pce.competence_id = pce2.competence_id
         AND pce2.person_id = p_person_id
         AND pce2.proficiency_level_id = prl2.rating_level_id(+)
         AND decode(prl2.step_value, NULL, decode(prl.step_value, NULL, -999,  PA_SEARCH_GLOB.get_min_prof_level(pce2.competence_id)), prl2.step_value) >= nvl(prl.step_value, nvl(prl2.step_value , -999));

Line: 76

  SELECT res.person_id, TRUNC(SUM(DECODE(SIGN(
         (nvl(res.capacity_quantity, 0) - nvl(res.confirmed_qty, 0))/
          asgmt.item_quantity-1),1, 1,
         greatest((nvl(res.capacity_quantity, 0) -
                    nvl(res.confirmed_qty, 0)), 0)/ asgmt.item_quantity))/
          l_assignment_days * 100)
    FROM PA_FORECAST_ITEMS res,
         PA_FORECAST_ITEMS asgmt
   WHERE res.resource_id = p_resource_id
     AND res.forecast_item_type = 'U'
     AND res.delete_flag = 'N'
     AND res.item_date = asgmt.item_date
     AND asgmt.assignment_id = p_assignment_id
     AND asgmt.delete_flag = 'N'
     AND asgmt.error_flag IN ('Y','N')
     AND asgmt.item_date >= trunc(SYSDATE)
     AND asgmt.item_quantity > 0
GROUP BY res.person_id;

Line: 100

   SELECT count(*) INTO l_assignment_days
     FROM pa_forecast_items
    WHERE assignment_id = p_assignment_id
      AND delete_flag = 'N'
      AND error_flag IN ('Y','N')
      AND item_date >= trunc(SYSDATE)
      AND item_quantity > 0;

Line: 152

       SELECT MIN(step_value) into l_min_rating_level
         FROM per_competence_levels_v
        WHERE competence_id = l_competence_id;