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

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

Line: 28


    select pbg.security_group_id,
           pbg.legislation_code
      from per_business_groups_perf pbg
         , pqh_fr_stat_situations sts
     where
      sts.statutory_situation_id = p_statutory_situation_id
      and pbg.business_group_id = sts.business_group_id;

Line: 100


    select pbg.legislation_code
      from per_business_groups_perf     pbg
         , pqh_fr_emp_stat_situations psu
         , pqh_fr_stat_situations sts
     where psu.emp_stat_situation_id = p_emp_stat_situation_id
       and psu.statutory_situation_id = sts.statutory_situation_id
       and pbg.business_group_id = sts.business_group_id;

Line: 367


Procedure chk_non_updateable_args
  (p_effective_date               in date
  ,p_rec in pqh_psu_shd.g_rec_type
  ) IS
--
  l_proc     varchar2(72) := g_package || 'chk_non_updateable_args';

Line: 410


End chk_non_updateable_args;

Line: 417


 SELECT 'X'
 FROM   per_all_people_f
 WHERE  person_id = p_person_id
 AND    TRUNC(SYSDATE) between effective_start_date and effective_end_date
 AND    PER_INFORMATION15 = '01'
 AND    current_employee_flag = 'Y';

Line: 442


  SELECT 'X'
  FROM   pqh_fr_stat_situations
  WHERE  statutory_situation_id = p_statutory_situation_id
  AND    TRUNC(SYSDATE) BETWEEN date_from and NVL(date_to,TRUNC(SYSDATE));

Line: 469


    SELECT frequency,
           NVL(renewable_allowed,'N'),
           NVL(max_no_of_renewals,0),
           NVL(max_duration_per_renewal,0),
           NVL(max_tot_continuous_duration,0)
      FROM pqh_fr_stat_situations
     WHERE statutory_situation_id = p_rec.statutory_situation_id;

Line: 479


    SELECT NVL(actual_start_date,provisional_start_date),
           NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date) days
      FROM pqh_fr_emp_stat_situations
     WHERE person_id              = p_rec.person_id
       AND statutory_situation_id = p_rec.statutory_situation_id
       AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
       AND((NVL(renewal_flag,'N') = 'N' AND emp_stat_situation_id   = p_rec.renew_stat_situation_id)
        OR (NVL(renewal_flag,'N') = 'Y' AND renew_stat_situation_id = p_rec.renew_stat_situation_id))
       AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date);

Line: 580


    SELECT frequency,
           NVL(renewable_allowed,'N'),
           max_no_of_renewals,
           max_duration_per_renewal,
           max_tot_continuous_duration
      FROM pqh_fr_stat_situations
     WHERE statutory_situation_id = p_rec.statutory_situation_id;

Line: 590


    SELECT NVL(actual_start_date,provisional_start_date),
           (NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date)+1) days
      FROM pqh_fr_emp_stat_situations
     WHERE person_id              = p_rec.person_id
       AND statutory_situation_id = p_rec.statutory_situation_id
       AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
       AND((NVL(renewal_flag,'N') = 'N' AND emp_stat_situation_id   = p_rec.renew_stat_situation_id)
        OR (NVL(renewal_flag,'N') = 'Y' AND renew_stat_situation_id = p_rec.renew_stat_situation_id))
       AND TRUNC(NVL(actual_end_date,provisional_end_date)) = TRUNC(p_date);

Line: 729


Select original_date_of_hire
from per_all_people_f
where person_id = p_person_id
and trunc(sysdate) between effective_start_date and effective_end_date;

Line: 735


Select nvl(date_to,hr_general.end_of_time)
from pqh_fr_stat_situations
where statutory_situation_id = p_statutory_situation_id;

Line: 775


    SELECT 'x' FROM DUAL
     WHERE EXISTS(SELECT statutory_situation_id
                    FROM pqh_fr_stat_situations
                   WHERE statutory_situation_id = p_rec.statutory_situation_id
                     AND situation_type         = 'IA'
                     AND sub_type               = 'IA_N'
                     AND business_group_id      = HR_GENERAL.get_business_group_id
                     AND default_flag           = 'Y'
                     AND TRUNC(SYSDATE)   BETWEEN date_from AND NVL(date_to,HR_GENERAL.end_of_time));

Line: 791


    SELECT 'x'
      FROM DUAL
     WHERE EXISTS(SELECT 'x'
                    FROM pqh_fr_emp_stat_situations
                   WHERE person_id                        = p_person_id
                     AND emp_stat_situation_id           <> NVL(p_emp_stat_situation_id,-1)
--                     AND NVL(renew_stat_situation_id,-2) <> NVL(p_emp_stat_situation_id,-1)    --commented by deenath
                     AND statutory_situation_id          <>
                        (SELECT statutory_situation_id
                           FROM pqh_fr_stat_situations_v sit,
                                per_shared_types_vl sh
                          WHERE sh.shared_type_id     = type_of_ps
                            AND sh.system_type_cd     = NVL(PQH_FR_UTILITY.get_bg_type_of_ps,sh.system_type_cd)
                            AND sit.business_group_id = HR_GENERAL.get_business_group_id
                            AND sit.default_flag      = 'Y'
                            AND sit.situation_type    = 'IA'
                            AND sit.sub_type          = 'IA_N'
                            AND TRUNC(SYSDATE) BETWEEN date_from AND NVL(date_to,HR_GENERAL.end_of_time))
       AND(NVL(p_actual_start,p_provisional_start) BETWEEN NVL(actual_start_date,provisional_start_date)
                                                       AND NVL(actual_end_date,NVL(provisional_end_date,hr_general.end_of_time))
        OR NVL(p_actual_end,p_provisional_end)     BETWEEN NVL(actual_end_date,provisional_end_date)
                                                       AND NVL(actual_end_date,NVL(provisional_end_date,hr_general.end_of_time))));

Line: 828


          RETURN; --Return because we dont want to check overlaps if Sit being created/updated is Default In Activity Normal Situation

Line: 860


   SELECT date_start,date_end
   FROM   per_contact_relationships
   WHERE  contact_person_id = p_contact_person_id
   AND    person_id = p_person_id
   AND    contact_type = p_contact_relationship;

Line: 897


    SELECT 'X' x
      FROM pqh_fr_emp_stat_situations
     WHERE person_id              = p_rec.person_id
       AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
       AND statutory_situation_id = p_rec.statutory_situation_id;

Line: 905


    SELECT frequency,
           first_period_max_duration,
           min_duration_per_request,
           max_duration_per_request,
           max_duration_whole_career
      FROM pqh_fr_stat_situations
     WHERE statutory_situation_id = p_rec.statutory_situation_id;

Line: 915


    SELECT SUM(days) total_duration_days
      FROM(SELECT NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date) days
             FROM pqh_fr_emp_stat_situations
            WHERE person_id              = p_rec.person_id
              AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
              AND statutory_situation_id = p_rec.statutory_situation_id);

Line: 1015


    SELECT 'Y'
      FROM pqh_fr_emp_stat_situations
     WHERE person_id              = p_rec.person_id
       AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
       AND statutory_situation_id = p_rec.statutory_situation_id;

Line: 1023


    SELECT situation_type,
           sub_type,
           frequency,
           first_period_max_duration,
           max_duration_whole_career,
           max_duration_per_request,
           min_duration_per_request
      FROM pqh_fr_stat_situations
     WHERE statutory_situation_id = p_rec.statutory_situation_id;

Line: 1035


    SELECT NVL(SUM(days),0) total_duration_days
      FROM(SELECT (NVL(actual_end_date,provisional_end_date)-NVL(actual_start_date,provisional_start_date)+1) days
             FROM pqh_fr_emp_stat_situations
            WHERE person_id              = p_rec.person_id
              AND emp_stat_situation_id <> NVL(p_rec.emp_stat_situation_id,-1)
              AND statutory_situation_id = p_rec.statutory_situation_id);

Line: 1219


  PROCEDURE insert_validate(p_effective_date IN DATE
                           ,p_rec            IN pqh_psu_shd.g_rec_type)
  IS
  --
    l_proc  varchar2(72) := g_package||'insert_validate';

Line: 1246


  END insert_validate;

Line: 1251


  PROCEDURE update_validate(p_effective_date IN DATE
                           ,p_rec            IN pqh_psu_shd.g_rec_type)
  IS
  --
    l_proc  varchar2(72) := g_package||'update_validate';

Line: 1274


    chk_non_updateable_args(p_effective_date => p_effective_date
                           ,p_rec            => p_rec);

Line: 1281


  END update_validate;

Line: 1286


  PROCEDURE delete_validate(p_rec IN pqh_psu_shd.g_rec_type)
  IS
  --
    l_proc  varchar2(72) := g_package||'delete_validate';

Line: 1298


  END delete_validate;