APPS.GHR_SF52_POS_UPDATE SQL Statements

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

Line: 51

	select oi.org_information1,
	       oi.org_information2,
	       oi.org_information3,
	       oi.org_information4,
	       oi.org_information5
	  from hr_organization_information oi
	  where oi.organization_id = org_id
	  and oi.org_information_context = 'GHR_US_ORG_INFORMATION';

Line: 113

procedure update_position_info
(p_pos_data_rec    in position_data_rec_type
)
IS

l_proc              varchar2(25) := 'update_position_info';

Line: 121

procedure  update_position_kff
IS
l_proc              varchar2(25) := 'update_position_kff';

Line: 141

	SELECT name,position_definition_id,
               organization_id,object_version_number,business_group_id
        FROM   hr_all_positions_f  pos
        WHERE  position_id = c_position_id -- Venkat -- Position DT
        and    p_pos_data_rec.effective_date between
           pos.effective_start_date and pos.effective_end_date;

Line: 152

	SELECT segment1,
               segment2,
	       segment3,
	       segment4,
	       segment5,
	       segment6,
	       segment7,
	       segment8,
	       segment9,
	       segment10,
	       segment11,
	       segment12,
	       segment13,
	       segment14,
	       segment15,
	       segment16,
	       segment17,
	       segment18,
	       segment19,
               segment20,
	       segment21,
	       segment22,
	       segment23,
	       segment24,
	       segment25,
	       segment26,
	       segment27,
	       segment28,
	       segment29,
	       segment30
     FROM      per_position_definitions
     WHERE     position_definition_id = c_position_definition_id;

Line: 250

    l_datetrack_mode := pos_return_update_mode
                 (p_position_id        => p_pos_data_rec.position_id,
                  p_effective_date     => p_pos_data_rec.effective_date);

Line: 253

     hr_utility.set_location('UPDATE_MODE Position  :   ' || l_datetrack_mode,25);

Line: 254

     hr_position_api.update_position(
 	       p_position_id              => p_pos_data_rec.position_id,
               p_object_version_number    => l_object_version_number ,
               p_segment1                 => l_segment_tab(1),
               p_segment2                 => l_segment_tab(2),
	       p_segment3                 => l_segment_tab(3),
	       p_segment4                 => l_segment_tab(4),
	       p_segment5                 => l_segment_tab(5),
	       p_segment6                 => l_segment_tab(6),
	       p_segment7                 => l_segment_tab(7),
	       p_segment8                 => l_segment_tab(8),
	       p_segment9                 => l_segment_tab(9),
	       p_segment10                 => l_segment_tab(10),
	       p_segment11                 => l_segment_tab(11),
	       p_segment12                 => l_segment_tab(12),
	       p_segment13                 => l_segment_tab(13),
	       p_segment14                 => l_segment_tab(14),
	       p_segment15                 => l_segment_tab(15),
	       p_segment16                 => l_segment_tab(16),
	       p_segment17                 => l_segment_tab(17),
	       p_segment18                 => l_segment_tab(18),
	       p_segment19                 => l_segment_tab(19),
	       p_segment20                 => l_segment_tab(20),
	       p_segment21                 => l_segment_tab(21),
	       p_segment22                 => l_segment_tab(22),
	       p_segment23                 => l_segment_tab(23),
	       p_segment24                 => l_segment_tab(24),
	       p_segment25                 => l_segment_tab(25),
	       p_segment26                 => l_segment_tab(26),
	       p_segment27                 => l_segment_tab(27),
	       p_segment28                 => l_segment_tab(28),
	       p_segment29                 => l_segment_tab(29),
	       p_segment30                 => l_segment_tab(30),
               p_position_definition_id    => l_position_definition_id,
               p_name                      => l_position_name,
               p_valid_grades_changed_warning => l_valid_grades_changed_warning,
               p_effective_start_date     => l_effective_start_date,
               p_effective_end_date       => l_effective_end_date,
               p_effective_date           => p_pos_data_rec.effective_date,
               p_datetrack_mode           => l_datetrack_mode);

Line: 295

hr_utility.set_location('after update_position_api ' || l_proc ,25);

Line: 299

procedure update_pos_organization
IS

l_object_version_number  number;

Line: 313

	SELECT name,position_definition_id,
               organization_id,object_version_number,business_group_id
	       ,location_id --  Bug 3219207 added by Ashley
        FROM   hr_all_positions_f  pos
        WHERE  position_id = c_position_id -- Venkat -- Position DT
        and    p_pos_data_rec.effective_date between
           pos.effective_start_date and pos.effective_end_date;

Line: 326

    hr_utility.set_location('update_pos_organization   ' ,25);

Line: 334

    l_datetrack_mode := pos_return_update_mode
                 (p_position_id        => p_pos_data_rec.position_id,
                  p_effective_date     => p_pos_data_rec.effective_date);

Line: 338

    hr_utility.set_location('UPDATE_MODE Position  :   ' || l_datetrack_mode,25);

Line: 343

       hr_position_api.update_position(
 	       p_position_id              => p_pos_data_rec.position_id,
               p_object_version_number    => l_object_version_number ,
               p_location_id              => p_pos_data_rec.location_id,
               p_position_definition_id   => l_position_definition_id,
               p_name                     => l_pos_name,
               p_valid_grades_changed_warning => l_valid_grade_warning,
               p_effective_start_date     => l_effective_start_date,
               p_effective_end_date       => l_effective_end_date,
               p_effective_date           => p_pos_data_rec.effective_date,
               p_datetrack_mode           => l_datetrack_mode);

Line: 355

      UPDATE hr_all_positions_f
      SET organization_id = p_pos_data_rec.organization_id
      WHERE position_id = p_pos_data_rec.position_id
      AND   effective_start_date = l_effective_start_date;

Line: 364

       hr_position_api.update_position(
 	       p_position_id              => p_pos_data_rec.position_id,
               p_object_version_number    => l_object_version_number ,
               p_location_id              => l_location_id,
               p_position_definition_id   => l_position_definition_id,
               p_name                     => l_pos_name,
               p_valid_grades_changed_warning => l_valid_grade_warning,
               p_effective_start_date     => l_effective_start_date,
               p_effective_end_date       => l_effective_end_date,
               p_effective_date           => p_pos_data_rec.effective_date,
               p_datetrack_mode           => l_datetrack_mode
	                            );

Line: 377

      UPDATE hr_all_positions_f
      SET organization_id = p_pos_data_rec.organization_id
      WHERE position_id = p_pos_data_rec.position_id
      AND   effective_start_date = l_effective_start_date;

Line: 388

procedure update_pos_end_date
IS

cursor c_get_position_details(c_position_id IN number, c_effective_date date)
IS
	SELECT  name,position_definition_id,
                organization_id,object_version_number,business_group_id
        FROM    hr_all_positions_f
        WHERE   position_id = c_position_id
        AND     nvl(c_effective_date,sysdate)
        between effective_start_date and effective_end_Date;

Line: 401

        SELECT shared_type_id,shared_type_name  from per_shared_types
        WHERE  system_type_cd = 'ELIMINATED';

Line: 414

  hr_utility.set_location('update_pos_end_date ', 25);

Line: 421

    l_datetrack_mode := pos_return_update_mode
                 (p_position_id        => p_pos_data_rec.position_id,
                  p_effective_date     => p_pos_data_rec.effective_date);

Line: 424

    hr_utility.set_location('UPDATE_MODE Position  :   ' || l_datetrack_mode,25);

Line: 425

    hr_position_api.update_position(
 	       p_position_id              => p_pos_data_rec.position_id,
               p_object_version_number    => c_get_position_details_rec.object_version_number,
               p_position_definition_id   => c_get_position_details_rec.position_definition_id,
               p_name                     => l_pos_name,
               --p_date_end                 => p_pos_data_rec.effective_date,
               p_availability_status_id   =>  l_shared_type_id,
               p_valid_grades_changed_warning => l_valid_grade_warning,
               p_effective_start_date     => l_effective_start_date,
               p_effective_end_date       => l_effective_end_date,
               p_effective_date           => p_pos_data_rec.effective_date,
               p_datetrack_mode           => l_datetrack_mode);

Line: 437

       hr_utility.set_location('After update of position',1);

Line: 449

procedure update_pos_job_id
IS
l_effective_start_Date date;

Line: 461

	SELECT name,position_definition_id,
               job_id,object_version_number,business_group_id
        FROM   hr_all_positions_f
        WHERE  position_id = c_position_id
        and    nvl(c_effective_date,sysdate) between
               effective_start_date and effective_end_date;

Line: 470

    hr_utility.set_location('update_pos_job_id   ' ,25);

Line: 474

    l_datetrack_mode := pos_return_update_mode
                 (p_position_id        => p_pos_data_rec.position_id,
                  p_effective_date     => p_pos_data_rec.effective_date);

Line: 477

    hr_utility.set_location('UPDATE_MODE Position  :   ' || l_datetrack_mode,25);

Line: 478

    hr_position_api.update_position(
 	       p_position_id              => p_pos_data_rec.position_id,
               p_object_version_number    => l_object_version_number ,
               p_position_definition_id   => l_position_definition_id,
               p_name                     => l_pos_name,
               p_valid_grades_changed_warning => l_valid_grade_warning,
               p_effective_start_date     => l_effective_start_date,
               p_effective_end_date       => l_effective_end_date,
               p_effective_date           => p_pos_data_rec.effective_date,
               p_datetrack_mode           => l_datetrack_mode);

Line: 489

      UPDATE hr_all_positions_f
      SET job_id        = p_pos_data_rec.job_id
      WHERE position_id = p_pos_data_rec.position_id
      and effective_start_date = l_effective_start_date;

Line: 506

   update_pos_organization;

Line: 518

    update_position_kff;

Line: 530

   update_pos_end_date;

Line: 542

   update_pos_job_id;

Line: 552

FUNCTION pos_return_update_mode
  (p_position_id     IN     hr_all_positions_f.position_id%type,
   p_effective_date  IN     date)

RETURN varchar2 is

  l_proc     varchar2(72) := 'return_update_mode';

Line: 564

  cursor     c_update_mode_pos is
    select   pos.effective_start_date ,
             pos.effective_end_date
    from     hr_all_positions_f pos
    where    pos.position_id = p_position_id
    and      p_effective_date
    between  pos.effective_start_date
    and      pos.effective_end_date;

Line: 573

  cursor     c_update_mode_pos1 is
    select   pos.effective_start_date ,
             pos.effective_end_date
    from     hr_all_positions_f pos
    where    pos.position_id = p_position_id
    and      p_effective_date  <  pos.effective_start_date;

Line: 582

      for update_mode in c_update_mode_pos loop
        hr_utility.set_location(l_proc,10);

Line: 584

        l_esd := update_mode.effective_start_date;

Line: 585

        l_eed := update_mode.effective_end_date;

Line: 593

        l_mode := 'UPDATE';                           --  to end date a row and then create a new row

Line: 596

        for update_mode1 in c_update_mode_pos1 loop
          hr_utility.set_location(l_proc,35);

Line: 603

          l_mode := 'UPDATE_CHANGE_INSERT';              -- to insert a row between 2 existing rows

Line: 611

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

Line: 619

End pos_return_update_mode;

Line: 624

PROCEDURE update_positions_location
 (p_position_id        IN       hr_all_positions_f.position_id%TYPE,
  p_location_id        IN       hr_all_positions_f.location_id%TYPE,
  p_effective_date     IN       date) IS

 l_position_definition_id         hr_all_positions_f.position_definition_id%TYPE;

Line: 641

	SELECT name, position_definition_id, location_id, object_version_number
      FROM   hr_all_positions_f
      WHERE  position_id = p_position_id
      and    nvl(p_effective_date,sysdate) between
             effective_start_date and effective_end_date;

Line: 657

      l_datetrack_mode := pos_return_update_mode
          (p_position_id        => p_position_id,
          p_effective_date     => p_effective_date);

Line: 661

      hr_utility.set_location('UPDATE_MODE Position  :   ' || l_datetrack_mode,99);

Line: 663

      hr_utility.set_location('update_pos_location ', 99);

Line: 665

      hr_position_api.update_position(
        p_position_id                  => p_position_id,
        p_object_version_number        => l_object_version_number,
        p_location_id                  => p_location_id,
        p_position_definition_id       => l_position_definition_id,
        p_name                         => l_position_name,
        p_valid_grades_changed_warning => l_valid_grade_warning,
        p_effective_start_date         => l_effective_start_date,
        p_effective_end_date           => l_effective_end_date,
        p_effective_date               => p_effective_date,
        p_datetrack_mode               => l_datetrack_mode);

Line: 677

      hr_utility.set_location('After update of positions location ',99);

Line: 685

end ghr_sf52_pos_update;