APPS.PER_SPP_BUS SQL Statements

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

Line: 67

   select assignment_id paa
     from per_all_assignments_f paa
    where paa.assignment_id = p_assignment_id
      and p_effective_date between
          effective_start_date and effective_end_date;

Line: 74

    select placement_id
    from per_spinal_point_placements_f
    where assignment_id = p_assignment_id
      and p_effective_date between
          effective_start_date and effective_end_date;

Line: 120

    if(p_datetrack_mode = 'INSERT') then
     open csr_placement_exists;

Line: 188

  select special_ceiling_step_id,
         grade_id
  from   per_all_assignments_f
  where  assignment_id = p_assignment_id
  and    p_effective_date between effective_start_date
			                           and effective_end_date;

Line: 196

  select pgs.ceiling_step_id
    from per_grade_spines_f pgs
   where pgs.parent_spine_id = p_parent_spine_id
     and pgs.grade_id        = l_grade_id
     and p_effective_date between pgs.effective_start_date
                              and pgs.effective_end_date;

Line: 265

  select sps.sequence
  into max_ceiling_number
  from per_spinal_point_steps_f sps,
       per_grade_spines_f pgs
  where sps.step_id = l_ceiling_to_use
  and   sps.grade_spine_id = pgs.grade_spine_id
  and   pgs.parent_spine_id = p_parent_spine_id
  and   p_effective_date between sps.effective_start_date
			     and sps.effective_end_date
  and   p_effective_date between pgs.effective_start_date
			     and pgs.effective_end_date;

Line: 280

  select sps.sequence
  into l_step_number
  from per_spinal_point_steps_f sps
  where sps.step_id = p_step_id
  and p_effective_date between sps.effective_start_date
		  	   and sps.effective_end_date;

Line: 407

    select object_version_number spp
    from per_spinal_point_placements_f spp
    where spp.placement_id = p_placement_id
    and p_effective_date between spp.effective_start_date
          		   and spp.effective_end_date
    and object_version_number = p_object_version_number;

Line: 598

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

Line: 620

    select placement_id,
           business_group_id,
	   assignment_id,
	   step_id,
	   auto_increment_flag,
	   parent_spine_id,
 	   reason
     from per_spinal_point_placements_f
    where placement_id = p_rec.placement_id
    and p_effective_date between effective_start_date
                                and effective_end_date;

Line: 669

end chk_reason_only_update;

Line: 740

    select gs.parent_spine_id
    from   per_grade_spines_f gs
    where  gs.parent_spine_id = p_parent_spine_id
    and    p_effective_start_date between gs.effective_start_date
                                                   and gs.effective_end_date;

Line: 749

    select gs.parent_spine_id,
           gs.grade_spine_id
    from per_grade_spines_f gs,
	 per_spinal_point_steps_f sps
    where gs.parent_spine_id = p_parent_spine_id
    and   gs.grade_spine_id = sps.grade_spine_id
    and   sps.step_id = p_step_id
   and   p_effective_start_date between gs.effective_start_date
                                                   and gs.effective_end_date
    and   p_effective_start_date between sps.effective_start_date
                                                   and sps.effective_end_date;

Line: 911

     select paf.grade_id , paf.assignment_id
     from per_all_assignments_f paf, per_spinal_point_placements_f psf
     where paf.assignment_id = psf.assignment_id
     and psf.placement_id = p_placement_id
     and p_effective_date between psf.effective_start_Date and psf.effective_end_date
     and p_effective_date between paf.effective_start_Date and paf.effective_end_date;

Line: 922

     select pspf.effective_end_date
     from per_spinal_point_placements_f pspf
     where pspf.placement_id = p_placement_id
     and pspf.effective_start_date > p_effective_Date
     order by pspf.effective_end_date  ;

Line: 932

     select 'X'
     from per_all_assignments_f paf
     where paf.assignment_id = l_asg_id
     and paf.grade_id <> p_grade_id
     and paf.effective_end_date between p_effective_date and p_end_date;

Line: 949

  IF p_datetrack_mode = 'DELETE_NEXT_CHANGE' then
    open c_next_spp_eed;

Line: 1016

    select pbg.security_group_id
      from per_business_groups pbg
         , per_spinal_point_placements_f spp
     where spp.placement_id = p_placement_id
       and pbg.business_group_id = spp.business_group_id;

Line: 1076

    select pbg.legislation_code
      from per_business_groups pbg
         , per_spinal_point_placements_f spp
     where spp.placement_id = p_placement_id
       and pbg.business_group_id = spp.business_group_id;

Line: 1164

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

Line: 1201

End chk_non_updateable_args;

Line: 1232

Procedure dt_update_validate
  (p_step_id                       in number
  ,p_assignment_id                 in number
  ,p_datetrack_mode                in varchar2
  ,p_validation_start_date         in date
  ,p_validation_end_date           in date
  ) Is
--
  l_proc  varchar2(72) := g_package||'dt_update_validate';

Line: 1316

End dt_update_validate;

Line: 1353

Procedure dt_delete_validate
  (p_placement_id                     in number
  ,p_datetrack_mode                   in varchar2
  ,p_validation_start_date            in date
  ,p_validation_end_date              in date
  ) Is
--
  l_proc	varchar2(72) 	:= g_package||'dt_delete_validate';

Line: 1377

  If (p_datetrack_mode = hr_api.g_delete or
      p_datetrack_mode = hr_api.g_zap) then
    --
    --
    -- Ensure the arguments are not null
    --
    hr_api.mandatory_arg_error
      (p_api_name       => l_proc
      ,p_argument       => 'validation_start_date'
      ,p_argument_value => p_validation_start_date
      );

Line: 1424

End dt_delete_validate;

Line: 1429

Procedure insert_validate
  (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
  ) is
--
  l_proc	varchar2(72) := g_package||'insert_validate';

Line: 1497

End insert_validate;

Line: 1502

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

Line: 1517

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

Line: 1601

     (p_datetrack_mode = 'UPDATE_OVERRIDE') then
     --
     chk_future_asg_changes
            (p_placement_id   => p_rec.placement_id
            ,p_effective_date => p_effective_date
            ,p_datetrack_mode => p_datetrack_mode
            );

Line: 1613

  hr_utility.set_location('Entering: chk_reason_only_update'||l_proc, 20);

Line: 1619

  chk_reason_only_update
  (p_rec                        => p_rec
  ,p_datetrack_mode             => p_datetrack_mode
  ,p_effective_date             => p_effective_date
  ,p_validation_start_date      => p_validation_start_date
  ,p_validation_end_date        => p_validation_end_date
  );

Line: 1628

  hr_utility.set_location(' Entering: dt_update_validate'||l_proc, 30);

Line: 1632

  dt_update_validate
    (p_step_id                        => p_rec.step_id
    ,p_assignment_id                  => p_rec.assignment_id
    ,p_datetrack_mode                 => p_datetrack_mode
    ,p_validation_start_date          => p_validation_start_date
    ,p_validation_end_date            => p_validation_end_date
    );

Line: 1640

  hr_utility.set_location(' Entering: chk_non_updateable_args'||l_proc, 35);

Line: 1641

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

Line: 1648

End update_validate;

Line: 1653

Procedure delete_validate
  (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
  ) is
--
  l_proc	varchar2(72) := g_package||'delete_validate';

Line: 1677

     (p_datetrack_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE'))then
     --
     hr_utility.set_location( l_proc ||'calling chk_fututre_asg_chng', 40);

Line: 1691

  hr_utility.set_location('Entering: dt_delete_validate'||l_proc,8);

Line: 1693

  dt_delete_validate
    (p_datetrack_mode                   => p_datetrack_mode
    ,p_validation_start_date            => p_validation_start_date
    ,p_validation_end_date              => p_validation_end_date
    ,p_placement_id                     => p_rec.placement_id
    );

Line: 1701

End delete_validate;