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

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

Line: 57


Procedure dt_insert_dml
  (p_rec                     in out nocopy per_spp_shd.g_rec_type
  ,p_effective_date          in date
  ,p_datetrack_mode          in varchar2
  ,p_validation_start_date   in date
  ,p_validation_end_date     in date
  ) is
-- Cursor to select 'old' created AOL who column values
--
  Cursor C_Sel1 Is
    select t.created_by,
           t.creation_date
    from   per_spinal_point_placements_f t
    where  t.placement_id       = p_rec.placement_id
    and    t.effective_start_date =
             per_spp_shd.g_old_rec.effective_start_date
    and    t.effective_end_date   = (p_validation_start_date - 1);

Line: 75


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

Line: 78


  l_last_update_date   	per_spinal_point_placements_f.last_update_date%TYPE;

Line: 79


  l_last_updated_by     per_spinal_point_placements_f.last_updated_by%TYPE;

Line: 80


  l_last_update_login   per_spinal_point_placements_f.last_update_login%TYPE;

Line: 103


  If (p_datetrack_mode <> hr_api.g_insert) then
    hr_utility.set_location(l_proc, 10);

Line: 125


    l_last_update_date   := sysdate;

Line: 126


    l_last_updated_by    := fnd_global.user_id;

Line: 127


    l_last_update_login  := fnd_global.login_id;

Line: 134


  insert into per_spinal_point_placements_f
      (placement_id
      ,effective_start_date
      ,effective_end_date
      ,business_group_id
      ,assignment_id
      ,step_id
      ,auto_increment_flag
      ,parent_spine_id
      ,reason
      ,request_id
      ,program_application_id
      ,program_id
      ,program_update_date
      ,increment_number
      ,object_version_number
      ,information1
      ,information2
      ,information3
      ,information4
      ,information5
      ,information6
      ,information7
      ,information8
      ,information9
      ,information10
      ,information11
      ,information12
      ,information13
      ,information14
      ,information15
      ,information16
      ,information17
      ,information18
      ,information19
      ,information20
      ,information21
      ,information22
      ,information23
      ,information24
      ,information25
      ,information26
      ,information27
      ,information28
      ,information29
      ,information30
      ,information_category
      ,created_by
      ,creation_date
      ,last_update_date
      ,last_updated_by
      ,last_update_login
      )
  Values
    (p_rec.placement_id
    ,p_rec.effective_start_date
    ,p_rec.effective_end_date
    ,p_rec.business_group_id
    ,p_rec.assignment_id
    ,p_rec.step_id
    ,p_rec.auto_increment_flag
    ,p_rec.parent_spine_id
    ,p_rec.reason
    ,p_rec.request_id
    ,p_rec.program_application_id
    ,p_rec.program_id
    ,p_rec.program_update_date
    ,p_rec.increment_number
    ,p_rec.object_version_number
    ,p_rec.information1
    ,p_rec.information2
    ,p_rec.information3
    ,p_rec.information4
    ,p_rec.information5
    ,p_rec.information6
    ,p_rec.information7
    ,p_rec.information8
    ,p_rec.information9
    ,p_rec.information10
    ,p_rec.information11
    ,p_rec.information12
    ,p_rec.information13
    ,p_rec.information14
    ,p_rec.information15
    ,p_rec.information16
    ,p_rec.information17
    ,p_rec.information18
    ,p_rec.information19
    ,p_rec.information20
    ,p_rec.information21
    ,p_rec.information22
    ,p_rec.information23
    ,p_rec.information24
    ,p_rec.information25
    ,p_rec.information26
    ,p_rec.information27
    ,p_rec.information28
    ,p_rec.information29
    ,p_rec.information30
    ,p_rec.information_category
    ,l_created_by
    ,l_creation_date
    ,l_last_update_date
    ,l_last_updated_by
    ,l_last_update_login
    );

Line: 258


End dt_insert_dml;

Line: 263


Procedure insert_dml
  (p_rec                   in out nocopy per_spp_shd.g_rec_type
  ,p_effective_date        in date
  ,p_datetrack_mode        in varchar2
  ,p_validation_start_date in date
  ,p_validation_end_date   in date
  ) is
--
  l_proc	varchar2(72) := g_package||'insert_dml';

Line: 276


  per_spp_ins.dt_insert_dml
    (p_rec                   => p_rec
    ,p_effective_date        => p_effective_date
    ,p_datetrack_mode        => p_datetrack_mode
    ,p_validation_start_date => p_validation_start_date
    ,p_validation_end_date   => p_validation_end_date
    );

Line: 285


End insert_dml;

Line: 326


Procedure pre_insert
  (p_rec                   in out nocopy per_spp_shd.g_rec_type
  ,p_effective_date        in date
  ,p_datetrack_mode        in varchar2
  ,p_validation_start_date in date
  ,p_validation_end_date   in date
  ) is
--
  l_proc	varchar2(72) := g_package||'pre_insert';

Line: 336


  Cursor C_Sel1 is select per_spinal_point_placements_s.nextval from sys.dual;

Line: 350


End pre_insert;

Line: 358


PROCEDURE delete_future_spps(p_assignment_id  number
                            ,p_effective_date date ) IS

--
  --
  -- Fetch future SPP_Records
  --
  CURSOR csr_future_spp_records(p_assignment_id number, p_effective_date date) IS
  SELECT spp.placement_id,
         spp.object_version_number,
         spp.effective_start_date
  FROM   per_spinal_point_placements_f spp
  WHERE  spp.assignment_id = p_assignment_id
  AND    spp.effective_start_date > p_effective_date;

Line: 379


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

Line: 406


      hr_sp_placement_api.delete_spp
        (p_effective_date        => c_future_spp.effective_start_date
        ,p_datetrack_mode	 => hr_api.g_zap
        ,p_placement_id		 => c_future_spp.placement_id
        ,p_object_version_number => l_object_version_number
        ,p_effective_start_date	 => l_effective_start_date
        ,p_effective_end_date	 => l_effective_end_date);

Line: 420


END delete_future_spps;

Line: 436


  select spp.placement_id
      --  ,spp.effective_start_date
      --  ,spp.effective_end_date
      --  ,spp.step_id
  from   per_spinal_point_placements_f spp
  where  spp.assignment_id = p_assignment_id
  and    spp.effective_start_date > p_effective_date;

Line: 447


  select min(spp.effective_start_date-1)
  from   per_spinal_point_placements_f spp
  where  spp.assignment_id = p_assignment_id
  and    spp.effective_start_date > p_effective_date;

Line: 454


  Cursor Asg_updates(p_assignment_id number, p_start_date date, p_end_date date) IS
  select paa.assignment_id
        ,paa.effective_start_date
        ,paa.effective_end_date
        ,paa.grade_id
  from   per_all_assignments_f paa
  where  paa.assignment_id = p_assignment_id
  and    paa.effective_start_date between p_start_date and p_end_date
  order by paa.effective_start_date;

Line: 466


  Cursor change_grade_update(p_assignment_id number, p_asg_eff_start_date date,
         p_grade_id number) IS
  select 'Y'
  from   per_all_assignments_f paa
  where  paa.assignment_id = p_assignment_id
  and    paa.effective_end_date = (p_asg_eff_start_date-1)
  and    paa.grade_id <> p_grade_id
  and    p_grade_id is not null
  and    paa.grade_id is not null;

Line: 480


  Cursor same_grade_update(p_assignment_id number, p_asg_eff_start_date date,
         p_grade_id number) IS
  select 'Y'
  from   per_all_assignments_f paa
  where  paa.assignment_id = p_assignment_id
  and    paa.effective_end_date = (p_asg_eff_start_date-1)
  and    paa.grade_id = p_grade_id;

Line: 494


  select 'Y'
  from   per_all_assignments_f paa
  where  paa.assignment_id = p_assignment_id
  and    paa.effective_end_date = (p_asg_eff_start_date-1)
  and    paa.grade_id is not null
  and    p_grade_id is null;

Line: 504


  select spp.placement_id
        ,spp.effective_start_date
        ,spp.effective_end_date
        ,spp.object_version_number
        ,spp.step_id
  from   per_spinal_point_placements_f spp
  where  spp.assignment_id = p_assignment_id
  and    p_effective_date between spp.effective_start_date and spp.effective_end_date;

Line: 522


  l_spp_delete_warning BOOLEAN;

Line: 557


        hr_sp_placement_api.delete_spp
                (p_validate		  => false
                ,p_effective_date	  => l_end_date
                ,p_datetrack_mode	  => 'DELETE'
                ,p_placement_id		  => l_placement_id
                ,p_object_version_number  => l_object_version_number
                ,p_effective_start_date	  => l_effective_start_date
                ,p_effective_end_date	  => l_effective_end_date
                );

Line: 584


  FOR asg_rec in asg_updates(p_assignment_id, l_validation_start_date, l_validation_end_date)  LOOP
  --
    l_asg_eff_start_date := asg_rec.effective_start_date;

Line: 600


   open same_grade_update(l_assignment_id, l_asg_eff_start_date, l_grade_id);

Line: 601


   fetch same_grade_update into l_dummy;

Line: 602


   if same_grade_update%found and l_dummy ='Y' then
      hr_utility.set_location('Assignment update is not a Grade Update', 50);

Line: 619


		hr_sp_placement_api.update_spp
	   	              (p_effective_date        => l_asg_eff_start_date
			      ,p_datetrack_mode        => 'UPDATE'
			      ,p_placement_id          => l_placement_id
			      ,p_object_version_number => l_object_version_number
			      ,p_step_id               => l_step_id
			      ,p_effective_start_date  => l_effective_start_date
			      ,p_effective_end_date    => l_effective_end_date);

Line: 639


   close same_grade_update;

Line: 664


        hr_sp_placement_api.delete_spp
                (p_validate		  => false
                ,p_effective_date	  => l_asg_eff_start_date-1
                ,p_datetrack_mode	  => 'DELETE'
                ,p_placement_id		  => l_placement_id
                ,p_object_version_number  => l_object_version_number
                ,p_effective_start_date	  => l_effective_start_date
                ,p_effective_end_date	  => l_effective_end_date
                );

Line: 694


   open change_grade_update(l_assignment_id, l_asg_eff_start_date, l_grade_id);

Line: 695


   fetch change_grade_update into l_dummy;

Line: 696


   if change_grade_update%found and l_dummy = 'Y' then

        hr_utility.set_location('Updation on Assignment was Grade1 to Grade2 ', 130);

Line: 699


        hr_utility.set_location('So if any SPP found then update with lowest step_id', 130);

Line: 716


                             ,p_datetrack_mode        => 'UPDATE'
                             ,p_validation_start_date => l_asg_eff_start_date
                             ,p_validation_end_date   => l_asg_eff_end_date
                             ,p_grade_id	      => l_grade_id
                             ,p_spp_delete_warning    => l_spp_delete_warning);

Line: 730


   close change_grade_update;

Line: 774


Procedure post_insert
  (p_rec                   in per_spp_shd.g_rec_type
  ,p_effective_date        in date
  ,p_datetrack_mode        in varchar2
  ,p_validation_start_date in date
  ,p_validation_end_date   in date
  ,p_replace_future_spp    in boolean  -- Added for bug 2977842.
  ) is
--
  l_proc	varchar2(72) := g_package||'post_insert';

Line: 788


    select max(effective_end_date)
    from per_spinal_point_steps_f
    where step_id = p_rec.step_id;

Line: 805


    update per_spinal_point_placements_f
    set effective_end_date = l_step_end_date
    where placement_id = p_rec.placement_id
    and effective_start_date = p_rec.effective_start_date;

Line: 817


    per_spp_rki.after_insert
      (p_effective_date          => p_effective_date
      ,p_validation_start_date   => p_validation_start_date
      ,p_validation_end_date     => p_validation_end_date
      ,p_placement_id            => p_rec.placement_id
      ,p_effective_start_date    => p_rec.effective_start_date
      ,p_effective_end_date      => p_rec.effective_end_date
      ,p_business_group_id       => p_rec.business_group_id
      ,p_assignment_id           => p_rec.assignment_id
      ,p_step_id                 => p_rec.step_id
      ,p_auto_increment_flag     => p_rec.auto_increment_flag
      ,p_parent_spine_id         => p_rec.parent_spine_id
      ,p_reason                  => p_rec.reason
      ,p_request_id              => p_rec.request_id
      ,p_program_application_id  => p_rec.program_application_id
      ,p_program_id              => p_rec.program_id
      ,p_program_update_date     => p_rec.program_update_date
      ,p_increment_number        => p_rec.increment_number
      ,p_object_version_number   => p_rec.object_version_number
      ,p_information1            => p_rec.information1
      ,p_information2            => p_rec.information2
      ,p_information3            => p_rec.information3
      ,p_information4            => p_rec.information4
      ,p_information5            => p_rec.information5
      ,p_information6            => p_rec.information6
      ,p_information7            => p_rec.information7
      ,p_information8            => p_rec.information8
      ,p_information9            => p_rec.information9
      ,p_information10           => p_rec.information10
      ,p_information11           => p_rec.information11
      ,p_information12           => p_rec.information12
      ,p_information13           => p_rec.information13
      ,p_information14           => p_rec.information14
      ,p_information15           => p_rec.information15
      ,p_information16           => p_rec.information16
      ,p_information17           => p_rec.information17
      ,p_information18           => p_rec.information18
      ,p_information19           => p_rec.information19
      ,p_information20           => p_rec.information20
      ,p_information21           => p_rec.information21
      ,p_information22           => p_rec.information22
      ,p_information23           => p_rec.information23
      ,p_information24           => p_rec.information24
      ,p_information25           => p_rec.information25
      ,p_information26           => p_rec.information26
      ,p_information27           => p_rec.information27
      ,p_information28           => p_rec.information28
      ,p_information29           => p_rec.information29
      ,p_information30           => p_rec.information30
      ,p_information_category    => p_rec.information_category
      );

Line: 882


     per_spp_ins.delete_future_spps(p_assignment_id  => p_rec.assignment_id
                                  ,p_effective_date => p_effective_date);

Line: 893


End post_insert;

Line: 1041


  l_datetrack_mode              varchar2(30) := hr_api.g_insert;

Line: 1050


  per_spp_bus.insert_validate
    (p_rec                   => p_rec
    ,p_effective_date        => p_effective_date
    ,p_datetrack_mode        => l_datetrack_mode
    ,p_validation_start_date => l_validation_start_date
    ,p_validation_end_date   => l_validation_end_date
    );

Line: 1070


  per_spp_bus.insert_validate
    (p_rec                   => p_rec
    ,p_effective_date        => p_effective_date
    ,p_datetrack_mode        => l_datetrack_mode
    ,p_validation_start_date => l_validation_start_date
    ,p_validation_end_date   => l_validation_end_date
    );

Line: 1081


  per_spp_ins.pre_insert
    (p_rec                   => p_rec
    ,p_effective_date        => p_effective_date
    ,p_datetrack_mode        => l_datetrack_mode
    ,p_validation_start_date => l_validation_start_date
    ,p_validation_end_date   => l_validation_end_date
    );

Line: 1091


  per_spp_ins.insert_dml
    (p_rec                   => p_rec
    ,p_effective_date        => p_effective_date
    ,p_datetrack_mode        => l_datetrack_mode
    ,p_validation_start_date => l_validation_start_date
    ,p_validation_end_date   => l_validation_end_date
    );

Line: 1102


  per_spp_ins.post_insert
    (p_rec                   => p_rec
    ,p_effective_date        => p_effective_date
    ,p_datetrack_mode        => l_datetrack_mode
    ,p_validation_start_date => l_validation_start_date
    ,p_validation_end_date   => l_validation_end_date
    ,p_replace_future_spp    => p_replace_future_spp  --Added for bug 2977842.
    );

Line: 1129


  ,p_program_update_date            in     date
  ,p_increment_number               in     number
  ,p_information1                   in     varchar2
  ,p_information2                   in     varchar2
  ,p_information3                   in     varchar2
  ,p_information4                   in     varchar2
  ,p_information5                   in     varchar2
  ,p_information6                   in     varchar2
  ,p_information7                   in     varchar2
  ,p_information8                   in     varchar2
  ,p_information9                   in     varchar2
  ,p_information10                  in     varchar2
  ,p_information11                  in     varchar2
  ,p_information12                  in     varchar2
  ,p_information13                  in     varchar2
  ,p_information14                  in     varchar2
  ,p_information15                  in     varchar2
  ,p_information16                  in     varchar2
  ,p_information17                  in     varchar2
  ,p_information18                  in     varchar2
  ,p_information19                  in     varchar2
  ,p_information20                  in     varchar2
  ,p_information21                  in     varchar2
  ,p_information22                  in     varchar2
  ,p_information23                  in     varchar2
  ,p_information24                  in     varchar2
  ,p_information25                  in     varchar2
  ,p_information26                  in     varchar2
  ,p_information27                  in     varchar2
  ,p_information28                  in     varchar2
  ,p_information29                  in     varchar2
  ,p_information30                  in     varchar2
  ,p_information_category           in     varchar2
  ,p_placement_id                      out nocopy number
  ,p_object_version_number             out nocopy number
  ,p_effective_start_date              out nocopy date
  ,p_effective_end_date                out nocopy date
  ,p_replace_future_spp             in     boolean   -- Added bug 2977842.
  ) is
  --
  l_rec         per_spp_shd.g_rec_type;

Line: 1174


  select parent_spine_id
    from per_grade_spines_f pgs,
         per_all_assignments_f paa
   where paa.grade_id = pgs.grade_id
   and   paa.assignment_id = p_assignment_id
   and   p_effective_date between paa.effective_start_date
	                    		       and paa.effective_end_date
    and  p_effective_date between pgs.effective_start_date
			                           and pgs.effective_end_date;

Line: 1221


    ,p_program_update_date
    ,p_increment_number
    ,p_information1
    ,p_information2
    ,p_information3
    ,p_information4
    ,p_information5
    ,p_information6
    ,p_information7
    ,p_information8
    ,p_information9
    ,p_information10
    ,p_information11
    ,p_information12
    ,p_information13
    ,p_information14
    ,p_information15
    ,p_information16
    ,p_information17
    ,p_information18
    ,p_information19
    ,p_information20
    ,p_information21
    ,p_information22
    ,p_information23
    ,p_information24
    ,p_information25
    ,p_information26
    ,p_information27
    ,p_information28
    ,p_information29
    ,p_information30
    ,p_information_category
    ,null
    );