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

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

Line: 34


l_datetrack_update_mode        varchar2(30);

Line: 43


l_spp_delete_warning           boolean;

Line: 69


select b.duty_station_code   old_duty_station_code
from hr_location_extra_info a,
     ghr_duty_stations_f    b
where information_type = 'GHR_US_LOC_INFORMATION'
and   a.lei_information3 = b.duty_station_id
and   b.duty_station_code in
('040355019', '060920071', '181788003', '181789003', '195549095',
 '204891103', '211257115', '211758081', '211758187', '213397003',
 '220376047', '222431059', '240414031', '240931047', '241371003',
 '265260085', '296675179', '330043017', '343478025', '398961099',
 '421172125', '424275109', '424676109', '471348157',
 '484208013', '484209153', '485936303', '511566069', '530171061',
 '530533025', '541475079', '542325035', '542334035', '542857045',
 'UV0000000', 'CF0000000', 'CG0000000', 'PS0000000', 'TC0000000',
 'TC1000000', 'TC1030000', 'TC1040000', 'TC1050000', 'TC1200000',
 'TC1300000', 'TC1500000', 'WS0000000', '422760045')
and   a.location_id = p_old_location_id;

Line: 88


select b.duty_station_code       new_duty_station_code
from hr_location_extra_info a,
     ghr_duty_stations_f    b
where information_type = 'GHR_US_LOC_INFORMATION'
and   a.lei_information3 = b.duty_station_id
and   b.duty_station_code in
('040335019', '062922071', '181850003', '181850003', '195548095', '204840131',
 '211256115', '211757081', '211757187', '210019003', '221920121', '221130059',
 '240411031', '240110047', '241366003', '265260075', '296654179', '330029017',
 '343475025', '399230099', '421170125', '420000109', '420000109', '471338157',
 '480000013', '482400153', '484140303', '511565041', '530170061', '530529025',
 '541474079', '541348035', '541348035', '541541045',
 'UV0000000', 'CF0000000', 'CG0000000', 'PS0000000', 'AE0000000',
 'AE1000000', 'AE1030000', 'AE1040000', 'AE1050000', 'AE1200000',
 'AE1300000', 'AE1500000', 'WS0000000')
and  a.location_id = p_new_location_id;

Line: 106


select nvl(locality_pay_area_id,0) locality_pay_area_id
from   ghr_duty_stations_f
where  duty_station_code = l_old_duty_station_code
and    nvl((l_effective_date - 1),sysdate)
       between effective_start_date and effective_end_date;

Line: 113


select nvl(locality_pay_area_id,0) locality_pay_area_id
from   ghr_duty_stations_f
where  duty_station_code = l_new_duty_station_code
and    nvl(l_effective_date,sysdate)
       between effective_start_date and effective_end_date;

Line: 120


select paf.person_id             person_id,
       paf.effective_start_date  effective_start_date,
       paf.assignment_id         assignment_id,
       paf.object_version_number object_version_number
from   per_assignments_f   paf,
       per_assignment_status_types ast
where  l_effective_date between paf.effective_start_date
                        and     paf.effective_end_date
and    paf.location_id           = p_old_location_id
and    ast.assignment_status_type_id = paf.assignment_status_type_id
and    ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN')
union
select paf1.person_id             person_id,
       paf1.effective_start_date  effective_start_date,
       paf1.assignment_id         assignment_id,
       paf1.object_version_number object_version_number
from   per_assignments_f   paf1,
       per_assignment_status_types ast1
where  l_effective_date <= paf1.effective_start_date
and    paf1.location_id           = p_old_location_id
and    ast1.assignment_status_type_id = paf1.assignment_status_type_id
and    ast1.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN','TERM_ASSIGN')
order by 3,2;

Line: 146


   select ppf.full_name
   from   per_people_f ppf
   where  person_id = l_person_id
   and    l_effective_date
   between ppf.effective_start_date and ppf.effective_end_date;

Line: 152


 cursor     c_update_mode_a is
    select   asg.effective_start_date ,
             asg.effective_end_date
    from     per_assignments_f asg
    where    asg.assignment_id = l_assignment_id
    and      l_assign_effective_date
    between  asg.effective_start_date
    and      asg.effective_end_date;

Line: 161


   cursor     c_update_mode_a1 is
    select   asg.effective_start_date ,
             asg.effective_end_date
    from     per_assignments_f asg
    where    asg.assignment_id = l_assignment_id
    and      l_assign_effective_date  <  asg.effective_start_date;

Line: 169


select pos1.position_id           position_id,
       pos1.name                  name,
       pos1.effective_start_date  effective_start_date,
       pos1.object_version_number object_version_number
from   hr_all_positions_f pos1
where  l_effective_date between pos1.effective_start_date
                        and     pos1.effective_end_date
and    pos1.location_id           = p_old_location_id
union
select pos2.position_id           position_id,
       pos2.name                  name,
       pos2.effective_start_date  effective_start_date,
       pos2.object_version_number object_version_number
from   hr_all_positions_f pos2
where  l_effective_date <= pos2.effective_start_date
and    pos2.location_id           = p_old_location_id
order by 1,3;

Line: 187


 cursor     c_pos_update_mode_a is
    select   pos.effective_start_date ,
             pos.effective_end_date
    from     hr_all_positions_f pos
    where    pos.position_id = l_position_id
    and      l_assign_effective_date
    between  pos.effective_start_date
    and      pos.effective_end_date;

Line: 196


   cursor     c_pos_update_mode_a1 is
    select   pos.effective_start_date ,
             pos.effective_end_date
    from     hr_all_positions_f pos
    where    pos.position_id = l_position_id
    and      l_assign_effective_date  <  pos.effective_start_date;

Line: 204


select organization_id,name
from   hr_organization_units
where  location_id = p_old_location_id
for update of location_id;

Line: 341


      for update_mode in c_update_mode_a loop
        l_esd := update_mode.effective_start_date;

Line: 343


        l_eed := update_mode.effective_end_date;

Line: 346


         l_datetrack_update_mode := 'CORRECTION';

Line: 349


         l_datetrack_update_mode := 'UPDATE';

Line: 352


        for update_mode1 in c_update_mode_a1 loop
          l_exists := true;

Line: 357


          l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';

Line: 360


          l_datetrack_update_mode := 'CORRECTION';

Line: 364


    hr_utility.set_location('UPDATE_MODE  :   ' || l_datetrack_update_mode,2);

Line: 380


     hr_assignment_api.update_emp_asg_criteria
       (p_effective_date               => l_assign_effective_date
       ,p_datetrack_update_mode        => l_datetrack_update_mode
       ,p_assignment_id                => l_assignment_id
       ,p_object_version_number        => l_object_version_number
       ,p_location_id                  => p_new_location_id
       ,p_special_ceiling_step_id      => l_special_ceiling_step_id
       ,p_effective_start_date         => l_start_date
       ,p_effective_end_date           => l_end_date
       ,p_people_group_id              => l_people_group_id
       ,p_group_name                   => l_group_name
       ,p_org_now_no_manager_warning   => l_org_now_no_manager_warning
       ,p_other_manager_warning        => l_other_manager_warning
       ,p_spp_delete_warning           => l_spp_delete_warning
       ,p_entries_changed_warning      => l_entries_changed_warning
       ,p_tax_district_changed_warning => l_tax_district_changed_warning
        );

Line: 405


     ghr_history_api.post_update_process;

Line: 450


      for pos_update_mode in c_pos_update_mode_a loop
        l_esd := pos_update_mode.effective_start_date;

Line: 452


        l_eed := pos_update_mode.effective_end_date;

Line: 455


         l_datetrack_update_mode := 'CORRECTION';

Line: 458


         l_datetrack_update_mode := 'UPDATE';

Line: 461


        for pos_update_mode1 in c_pos_update_mode_a1 loop
          l_exists := true;

Line: 466


          l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';

Line: 469


          l_datetrack_update_mode := 'CORRECTION';

Line: 473


    hr_utility.set_location('UPDATE_MODE Position  :   ' || l_datetrack_update_mode,2);

Line: 487


     hr_position_api.update_position
       (p_position_id                  => l_position_id
       ,p_effective_start_date         => l_out_esd
       ,p_effective_end_date           => l_out_eed
       ,p_object_version_number        => l_object_version_number
       ,p_location_id                  => p_new_location_id
       ,p_position_definition_id       => l_position_definition_id
       ,p_name                         => l_name
       ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
       ,p_effective_date               => l_assign_effective_date
       ,p_datetrack_mode               => l_datetrack_update_mode
        );

Line: 500


       ghr_history_api.post_update_process;

Line: 533


   update hr_organization_units set location_id = p_new_location_id
   where current of cur_organizations;