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_FR_EMP_STAT_SITUATION_API SQL Statements

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

Line: 10


  PROCEDURE update_assignments(p_person_id              IN NUMBER
                              ,p_emp_stat_situation_id  IN NUMBER DEFAULT NULL
                              ,p_statutory_situation_id IN NUMBER
                              ,p_start_date             IN DATE
                              ,p_end_date               IN DATE   DEFAULT NULL)
  IS
  --
  --Cursor Declaration
  --Cursor to fetch Statutory Situation Details.
    CURSOR csr_situation_details(p_statutory_situation_id IN NUMBER) IS
    SELECT situation_type,
           sub_type,
           NVL(reserve_position,'N')             reserve_position,
           NVL(remuneration_paid,'N')            remuneration_paid,
           NVL(renewable_allowed,'N')            renewable_allowed,
           NVL(default_flag,'N')                 default_flag,
           NVL(allow_progression_flag,'N')       allow_progression_flag,
           NVL(extend_probation_period_flag,'N') extend_probation_period_flag,
           NVL(remunerate_assign_status_id,-1)   remunerate_assign_status_id
      FROM pqh_fr_stat_situations
     WHERE statutory_situation_id = p_statutory_situation_id;

Line: 34


    SELECT asg.assignment_id,
           asg.assignment_type,
           asg.effective_start_date,
           asg.effective_end_date,
           asg.assignment_status_type_id,
           ast.per_system_status,
           ast.pay_system_status,
           asg.object_version_number
      FROM per_all_assignments_f       asg,
           per_assignment_status_types ast
     WHERE asg.person_id                 = p_person_id
       AND p_eff_date              BETWEEN asg.effective_start_date AND asg.effective_end_date
       AND asg.primary_flag              = 'Y'
       AND asg.assignment_status_type_id = ast.assignment_status_type_id;

Line: 51


    SELECT assignment_id,
           effective_start_date,
           effective_end_date,
           assignment_type,
           asg.object_version_number
      FROM per_all_assignments_f       asg,
           hr_soft_coding_keyflex      scl,
           per_assignment_status_types ast
     WHERE asg.soft_coding_keyflex_id     = scl.soft_coding_keyflex_id
       AND scl.segment26                  = p_career_id
       AND asg.assignment_status_type_id  = ast.assignment_status_type_id
       AND ast.per_system_status          = p_per_status
       AND(p_aff_st_date            BETWEEN asg.effective_start_date AND asg.effective_end_date
        OR asg.effective_start_date BETWEEN p_aff_st_date AND NVL(p_aff_end_date,p_aff_st_date))
     ORDER BY effective_start_date;

Line: 70


    SELECT 'Y'
      FROM per_all_assignments_f       asg,
           per_assignment_status_types ast
     where asg.assignment_id             =  p_affect_asg_id
       and asg.effective_end_date        >= p_term_st_dt
       and ast.assignment_status_type_id =  asg.assignment_status_type_id
       and ast.per_system_status         =  'TERM_ASSIGN';

Line: 94


    l_proc                 VARCHAR2(72) := g_package||'update_assignments';

Line: 151


                                                ,p_datetrack_update_mode => l_dt_mode
                                                ,p_assignment_id         => l_affect_asg_id
                                                ,p_object_version_number => l_affect_ovn
                                                ,p_effective_start_date  => l_affect_esd
                                                ,p_effective_end_date    => l_affect_eed);

Line: 160


                                                ,p_datetrack_update_mode => l_dt_mode
                                                ,p_assignment_id         => l_affect_asg_id
                                                ,p_object_version_number => l_affect_ovn
                                                ,p_effective_start_date  => l_affect_esd
                                                ,p_effective_end_date    => l_affect_eed);

Line: 178


                                            ,p_datetrack_update_mode => l_dt_mode
                                            ,p_assignment_id         => lr_career_rec.assignment_id
                                            ,p_object_version_number => l_career_ovn
                                            ,p_effective_start_date  => l_career_esd
                                            ,p_effective_end_date    => l_career_eed);

Line: 187


                                            ,p_datetrack_update_mode => l_dt_mode
                                            ,p_assignment_id         => lr_career_rec.assignment_id
                                            ,p_object_version_number => l_career_ovn
                                            ,p_effective_start_date  => l_career_esd
                                            ,p_effective_end_date    => l_career_eed);

Line: 221


                                                  ,p_datetrack_update_mode => l_dt_mode
                                                  ,p_assignment_id         => l_affect_asg_id
                                                  ,p_object_version_number => l_affect_ovn
                                                  ,p_effective_start_date  => l_affect_esd
                                                  ,p_effective_end_date    => l_affect_eed);

Line: 262


                                                  ,p_datetrack_update_mode => l_dt_mode
                                                  ,p_assignment_id         => l_affect_asg_id
                                                  ,p_object_version_number => l_affect_ovn
                                                  ,p_effective_start_date  => l_affect_esd
                                                  ,p_effective_end_date    => l_affect_eed);

Line: 305


                              ,p_datetrack_update_mode     => l_dt_mode
                              ,p_assignment_id             => lr_career_rec.assignment_id
                              ,p_object_version_number     => l_career_ovn
                              ,p_assignment_status_type_id => lr_sit_dtls.remunerate_assign_status_id
                              ,p_effective_start_date      => l_career_esd
                              ,p_effective_end_date        => l_career_eed);

Line: 316


                              ,p_datetrack_update_mode     => l_dt_mode
                              ,p_assignment_id             => lr_career_rec.assignment_id
                              ,p_object_version_number     => l_career_ovn
                              ,p_assignment_status_type_id => lr_sit_dtls.remunerate_assign_status_id
                              ,p_effective_start_date      => l_career_esd
                              ,p_effective_end_date        => l_career_eed);

Line: 331


                              ,p_datetrack_update_mode     => l_dt_mode
                              ,p_assignment_id             => lr_career_rec.assignment_id
                              ,p_object_version_number     => l_career_ovn
                              ,p_effective_start_date      => l_career_esd
                              ,p_effective_end_date        => l_career_eed);

Line: 341


                              ,p_datetrack_update_mode     => l_dt_mode
                              ,p_assignment_id             => lr_career_rec.assignment_id
                              ,p_object_version_number     => l_career_ovn
                              ,p_effective_start_date      => l_career_esd
                              ,p_effective_end_date        => l_career_eed);

Line: 353


  END update_assignments;

Line: 483


     update_assignments(p_person_id => p_person_id
                       ,p_statutory_situation_id => p_statutory_situation_id
                       ,p_start_date => NVL(p_actual_start_date,p_provisional_start_date)
                       ,p_end_date => NVL(p_actual_end_date,p_provisional_end_date) );

Line: 513


procedure UPDATE_EMP_STAT_SITUATION
  (p_validate                      IN     boolean  default false
  ,p_effective_date                IN     date
  ,P_EMP_STAT_SITUATION_ID         IN     NUMBER
  ,P_STATUTORY_SITUATION_ID        IN     NUMBER   default hr_api.g_number
  ,P_PERSON_ID                     IN     NUMBER   default hr_api.g_number
  ,P_PROVISIONAL_START_DATE        IN     DATE     default hr_api.g_date
  ,P_PROVISIONAL_END_DATE          IN     DATE     default hr_api.g_date
  ,P_ACTUAL_START_DATE             IN     DATE     default hr_api.g_date
  ,P_ACTUAL_END_DATE               IN     DATE     default hr_api.g_date
  ,P_APPROVAL_FLAG                 IN     VARCHAR2 default hr_api.g_varchar2
  ,P_COMMENTS                      IN     VARCHAR2 default hr_api.g_varchar2
  ,P_CONTACT_PERSON_ID             IN     NUMBER   default hr_api.g_number
  ,P_CONTACT_RELATIONSHIP          IN     VARCHAR2 default hr_api.g_varchar2
  ,P_EXTERNAL_ORGANIZATION_ID      IN     NUMBER   default hr_api.g_number
  ,P_RENEWAL_FLAG                  IN     VARCHAR2 default hr_api.g_varchar2
  ,P_RENEW_STAT_SITUATION_ID       IN     NUMBER   default hr_api.g_number
  ,P_SECONDED_CAREER_ID            IN     NUMBER   default hr_api.g_number
  ,P_ATTRIBUTE_CATEGORY            IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE1                    IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE2                    IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE3                    IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE4                    IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE5                    IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE6                    IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE7                    IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE8                    IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE9                    IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE10                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE11                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE12                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE13                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE14                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE15                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE16                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE17                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE18                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE19                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE20                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE21                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE22                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE23                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE24                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE25                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE26                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE27                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE28                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE29                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_ATTRIBUTE30                   IN     VARCHAR2 default hr_api.g_varchar2
  ,P_OBJECT_VERSION_NUMBER         IN OUT NOCOPY NUMBER
  ) IS
  l_emp_stat_situation_id  NUMBER(15);

Line: 568


    SELECT NVL(approval_flag, 'N')
    FROM   pqh_fr_emp_stat_situations
    WHERE  emp_stat_situation_id = p_emp_stat_situation_id;

Line: 572


  l_proc                varchar2(72) := g_package||'update_emp_stat_situation';

Line: 579


  savepoint update_emp_stat_situation;

Line: 645


     update_assignments(p_person_id => p_person_id
                       ,p_statutory_situation_id => p_statutory_situation_id
                       ,p_start_date => NVL(p_actual_start_date,p_provisional_start_date)
                       ,p_end_date => NVL(p_actual_end_date,p_provisional_end_date) );

Line: 666


     ROLLBACK TO update_emp_stat_situation;

Line: 669


     Rollback to update_emp_stat_situation;

Line: 673


 END UPDATE_EMP_STAT_SITUATION;

Line: 674


Procedure DELETE_EMP_STAT_SITUATION
( P_VALIDATE   IN BOOLEAN DEFAULT FALSE
 ,P_EMP_STAT_SITUATION_ID IN NUMBER
 ,P_OBJECT_VERSION_NUMBER IN NUMBER) IS
  l_proc                varchar2(72) := g_package||'delete_emp_stat_situation';

Line: 681


  savepoint delete_emp_stat_situation;

Line: 695


     ROLLBACK TO delete_emp_stat_situation;

Line: 697


      Rollback to delete_emp_stat_situation;

Line: 700


 END DELETE_EMP_STAT_SITUATION;

Line: 714


    SELECT *
    FROM   pqh_fr_emp_stat_situations
    WHERE  emp_stat_situation_id = p_emp_stat_situation_id;

Line: 742


  pqh_fr_emp_stat_situation_api.update_emp_stat_situation(p_emp_stat_situation_id => p_emp_stat_situation_id
                                                         ,p_effective_date        => lr_currec.provisional_end_date
                                                         ,p_object_version_number => l_upd_sit_ovn
                                                         ,p_provisional_end_date  => l_new_prov_end_date);

Line: 787


   SELECT *
   FROM   pqh_fr_emp_stat_situations
   WHERE  emp_stat_situation_id = p_emp_stat_situation_id;

Line: 792


   SELECT max(NVL(actual_start_date,provisional_start_date))
   FROM   pqh_fr_emp_stat_situations
   WHERE  emp_stat_situation_id <> p_emp_stat_situation_id
   AND    person_Id  = p_person_id
   AND    p_eff_date BETWEEN provisional_start_date and provisional_end_date;

Line: 820


  pqh_fr_emp_stat_situation_api.update_emp_stat_situation(p_emp_stat_situation_id => p_emp_stat_situation_id
                                                         ,p_effective_date        => trunc(p_reinstate_date)-1
                                                         ,p_object_version_number => l_upd_sit_ovn
                                                         ,p_actual_end_date       => trunc(p_reinstate_date)-1);