APPS.PER_SUCC_MGMT_MIGRATION_PKG SQL Statements

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

Line: 5

    SELECT 'Y'
    FROM    dual
    WHERE EXISTS (SELECT 'x'
                  FROM   per_people_extra_info pei,
                         per_all_people_f ppf
                  WHERE  ppf.business_group_id = p_bg_id
                  AND    ppf.person_id = pei.person_id
                  AND    pei.information_type = 'PER_SUCCESSION_PLANNING');

Line: 33

    SELECT rpad(lookup_code,30) LOOKUP_CODE, meaning
    FROM   hr_lookups h
    WHERE  lookup_type = 'PER_SUCC_PLAN_POTENTIAL'
    AND    lookup_code IN (SELECT distinct pei_information1
                           FROM   per_people_extra_info pei,
                                  per_all_people_f ppf
                           WHERE  pei.information_type = 'PER_SUCCESSION_PLANNING'
                           AND    pei.person_id = ppf.person_id
                           AND    trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
                           AND    ppf.business_group_id = p_bg_id)
    AND    lookup_code NOT IN (SELECT lookup_code
                               FROM    hr_lookups
                               WHERE  lookup_type = 'READINESS_LEVEL'
                               AND    enabled_flag = 'Y'
                               AND    trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND NVL(end_date_active,trunc(sysdate)) );

Line: 49

    SELECT rpad(lookup_code,30) LOOKUP_CODE, meaning
    FROM   hr_lookups h
    WHERE  lookup_type = 'PER_SUCC_PLAN_RISK_LEVEL'
    AND    lookup_code IN (SELECT distinct pei_information2
                           FROM   per_people_extra_info pei,
                                  per_all_people_f ppf
                           WHERE  pei.information_type = 'PER_SUCCESSION_PLANNING'
                           AND    pei.person_id = ppf.person_id
                           AND    trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
                           AND    ppf.business_group_id = p_bg_id)
    AND    lookup_code NOT IN (SELECT lookup_code
                               FROM    hr_lookups
                               WHERE  lookup_type = 'PER_RETENTION_POTENTIAL'
                               AND    enabled_flag = 'Y'
                               AND    trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND NVL(end_date_active,trunc(sysdate)) );

Line: 96

  SELECT pei.person_id
        ,pei.person_extra_info_id
        ,pei_information3 --- key person
        ,pei_information1 --- potential
        ,pei_information2 --- risk of loss
  FROM  per_people_extra_info pei
       ,per_all_people_f ppf
  WHERE ppf.business_group_id = p_bg_id
  AND    trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
  AND   ppf.person_id = pei.person_id
  AND   pei.information_type = 'PER_SUCCESSION_PLANNING'
  AND   NOT EXISTS (SELECT 'x'
                    FROM   per_people_extra_info new
                    WHERE  new.information_type = 'PER_SUCCESSION_MGMT_INFO'
                    AND    new.person_id = pei.person_id
                    AND    new.pei_information7 = (-1*pei.person_extra_info_id));