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

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

Line: 69


   select min(papf.effective_start_date)
     from per_all_people_f  papf,
     per_person_types ppt
    where papf.person_type_id = ppt.person_type_id
	and system_person_type in (l_emp,l_emp_apl)
        and papf.person_id          =  p_per_or_asg_id
        and papf.business_group_id  =  p_business_group_id;

Line: 78


   select min(effective_start_date)
	from per_all_assignments_f paaf,
		 hr_soft_coding_keyflex scl,
		 per_assignment_status_types past
	where paaf.ASSIGNMENT_STATUS_TYPE_ID = past.ASSIGNMENT_STATUS_TYPE_ID
	and paaf.SOFT_CODING_KEYFLEX_ID = scl.SOFT_CODING_KEYFLEX_ID
	and scl.segment3 = p_contract_category
	and paaf.assignment_id = p_per_or_asg_id
	and paaf.business_group_id = p_business_group_id
	and past.per_system_status in (l_active_assign,l_susp_assign);

Line: 354


    select bus.business_group_id
         , bus.legislation_code
      from per_all_assignments_f    paf
         , per_business_groups_perf bus
     where paf.person_id         = p_assignment_id
     and   l_effective_date      between paf.effective_start_date
                                 and     paf.effective_end_date
     and   bus.business_group_id = paf.business_group_id;

Line: 455


    select bus.business_group_id
         , bus.legislation_code
      from per_all_assignments_f    paf
         , per_business_groups_perf bus
     where paf.person_id         = p_assignment_id
     and   l_effective_date      between paf.effective_start_date
                                 and     paf.effective_end_date
     and   bus.business_group_id = paf.business_group_id;

Line: 556


    select bus.business_group_id
         , bus.legislation_code
      from per_all_assignments_f    paf
         , per_business_groups_perf bus
     where paf.person_id         = p_assignment_id
     and   l_effective_date      between paf.effective_start_date
                                 and     paf.effective_end_date
     and   bus.business_group_id = paf.business_group_id;

Line: 658


    select bus.business_group_id
         , bus.legislation_code
      from per_all_people_f papf
         , per_business_groups_perf bus
     where papf.person_id        = p_person_id
     and   l_effective_date      between papf.effective_start_date
                                 and     papf.effective_end_date
     and   bus.business_group_id = papf.business_group_id;

Line: 727


procedure update_pl_sii_details
  (p_validate                      in     boolean  default false
  ,p_effective_date                in     date
  ,p_datetrack_update_mode         in     varchar2
  ,p_sii_details_id                in     number
  ,p_object_version_number         in out nocopy   number
  ,p_emp_social_security_info      in     varchar2 default hr_api.g_varchar2
  ,p_old_age_contribution          in     varchar2 default hr_api.g_varchar2
  ,p_pension_contribution          in     varchar2 default hr_api.g_varchar2
  ,p_sickness_contribution         in     varchar2 default hr_api.g_varchar2
  ,p_work_injury_contribution      in     varchar2 default hr_api.g_varchar2
  ,p_labor_contribution            in     varchar2 default hr_api.g_varchar2
  ,p_health_contribution           in     varchar2 default hr_api.g_varchar2
  ,p_unemployment_contribution     in     varchar2 default hr_api.g_varchar2
  ,p_old_age_cont_end_reason       in     varchar2 default hr_api.g_varchar2
  ,p_pension_cont_end_reason       in     varchar2 default hr_api.g_varchar2
  ,p_sickness_cont_end_reason      in     varchar2 default hr_api.g_varchar2
  ,p_work_injury_cont_end_reason   in     varchar2 default hr_api.g_varchar2
  ,p_labor_fund_cont_end_reason    in     varchar2 default hr_api.g_varchar2
  ,p_health_cont_end_reason        in     varchar2 default hr_api.g_varchar2
  ,p_unemployment_cont_end_reason  in     varchar2 default hr_api.g_varchar2
  ,p_effective_start_date          out nocopy   date
  ,p_effective_end_date            out nocopy   date
  )
   is
  --
  -- Declare cursors and local variables
  --
  l_effective_date         date;

Line: 756


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

Line: 774


    select per_or_asg_id from pay_pl_sii_details_f
     where sii_details_id  = p_sii_details_id and
           p_effective_date between effective_start_date and effective_end_date and
           contract_category = p_contract_catg;

Line: 780


   select soft1.segment4 contract_type1, soft2.segment4 contract_type2
     from hr_soft_coding_keyflex soft1,
          per_all_assignments_f paf1,
          per_all_people_f pap,
          per_assignment_status_types pst,
          hr_soft_coding_keyflex soft2,
          per_all_assignments_f paf2
    where pap.person_id = (select per_or_asg_id from pay_pl_sii_details_f
                           where sii_details_id = p_sii_details_id and
                           p_effective_date between effective_start_date
                                                  and effective_end_date)
      and pap.person_id = paf1.person_id
      and p_effective_date between pap.effective_start_date and pap.effective_end_date
      and p_effective_date between paf1.effective_start_date and paf1.effective_end_date
      and paf1.soft_coding_keyflex_id = soft1.soft_coding_keyflex_id
      and soft1.segment3 = l_norm_active
      and paf1.assignment_status_type_id = pst.assignment_status_type_id
      and pst.per_system_status in (l_assg_type1,l_assg_type2)
      and pap.person_id = paf2.person_id
      and (p_effective_date-1) between paf2.effective_start_date and paf2.effective_end_date
      and  paf2.soft_coding_keyflex_id = soft2.soft_coding_keyflex_id
      and soft2.segment3 = l_norm_active
      and paf2.assignment_status_type_id = pst.assignment_status_type_id
  -- This join ensures that the Contract types are for the same assignment
      and paf2.assignment_id = paf1.assignment_id;

Line: 817


  savepoint update_pl_sii_details;

Line: 833


      if csr_term_catg%FOUND and p_datetrack_update_mode <> 'CORRECTION' then
        -- Raise an error message as the record can only be Corrected for a Normal Terminated
        -- Assignment
           hr_utility.set_message(801,'PAY_375859_INVALID_TERM_MODE');

Line: 843


      if csr_term_catg%FOUND and p_datetrack_update_mode <> 'CORRECTION' then
        --
          close csr_term_catg;

Line: 870


    PAY_PL_SII_BK2.update_pl_sii_details_b
      (p_effective_date                => p_effective_date
      ,p_sii_details_id                => p_sii_details_id
      ,p_datetrack_update_mode         => p_datetrack_update_mode
      ,p_emp_social_security_info      => p_emp_social_security_info
      ,p_old_age_contribution          => p_old_age_contribution
      ,p_pension_contribution          => p_pension_contribution
      ,p_sickness_contribution         => p_sickness_contribution
      ,p_work_injury_contribution      => p_work_injury_contribution
      ,p_labor_contribution            => p_labor_contribution
      ,p_health_contribution           => p_health_contribution
      ,p_unemployment_contribution     => p_unemployment_contribution
      ,p_old_age_cont_end_reason       => p_old_age_cont_end_reason
      ,p_pension_cont_end_reason       => p_pension_cont_end_reason
      ,p_sickness_cont_end_reason      => p_sickness_cont_end_reason
      ,p_work_injury_cont_end_reason   => p_work_injury_cont_end_reason
      ,p_labor_fund_cont_end_reason    => p_labor_fund_cont_end_reason
      ,p_health_cont_end_reason        => p_health_cont_end_reason
      ,p_unemployment_cont_end_reason  => p_unemployment_cont_end_reason
      ,p_object_version_number         => l_object_version_number
       );

Line: 894


        (p_module_name => 'update_pl_sii_details'
        ,p_hook_type   => 'BP'
        );

Line: 905


       ,p_datetrack_mode               => p_datetrack_update_mode
       ,p_sii_details_id               => p_sii_details_id
       ,p_object_version_number        => l_object_version_number
       ,p_emp_social_security_info     => p_emp_social_security_info
       ,p_old_age_contribution         => p_old_age_contribution
       ,p_pension_contribution         => p_pension_contribution
       ,p_sickness_contribution        => p_sickness_contribution
       ,p_work_injury_contribution     => p_work_injury_contribution
       ,p_labor_contribution           => p_labor_contribution
       ,p_health_contribution          => p_health_contribution
       ,p_unemployment_contribution    => p_unemployment_contribution
       ,p_old_age_cont_end_reason      => p_old_age_cont_end_reason
       ,p_pension_cont_end_reason      => p_pension_cont_end_reason
       ,p_sickness_cont_end_reason     => p_sickness_cont_end_reason
       ,p_work_injury_cont_end_reason  => p_work_injury_cont_end_reason
       ,p_labor_fund_cont_end_reason   => p_labor_fund_cont_end_reason
       ,p_health_cont_end_reason       => p_health_cont_end_reason
       ,p_unemployment_cont_end_reason => p_unemployment_cont_end_reason
       ,p_program_id                   => l_program_id
       ,p_program_login_id             => l_program_login_id
       ,p_program_application_id       => l_program_application_id
       ,p_request_id                   => l_request_id
       ,p_effective_start_date         => l_effective_start_date
       ,p_effective_end_date           => l_effective_end_date
       );

Line: 934


     pay_pl_sii_bk2.update_pl_sii_details_a
       (p_effective_date                => p_effective_date
       ,p_sii_details_id                => p_sii_details_id
       ,p_datetrack_update_mode         => p_datetrack_update_mode
       ,p_emp_social_security_info      => p_emp_social_security_info
       ,p_old_age_contribution          => p_old_age_contribution
       ,p_pension_contribution          => p_pension_contribution
       ,p_sickness_contribution         => p_sickness_contribution
       ,p_work_injury_contribution      => p_work_injury_contribution
       ,p_labor_contribution            => p_labor_contribution
       ,p_health_contribution           => p_health_contribution
       ,p_unemployment_contribution     => p_unemployment_contribution
       ,p_old_age_cont_end_reason       => p_old_age_cont_end_reason
       ,p_pension_cont_end_reason       => p_pension_cont_end_reason
       ,p_sickness_cont_end_reason      => p_sickness_cont_end_reason
       ,p_work_injury_cont_end_reason   => p_work_injury_cont_end_reason
       ,p_labor_fund_cont_end_reason    => p_labor_fund_cont_end_reason
       ,p_health_cont_end_reason        => p_health_cont_end_reason
       ,p_unemployment_cont_end_reason  => p_unemployment_cont_end_reason
       ,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: 961


        (p_module_name => 'update_pl_sii_details'
        ,p_hook_type   => 'AP'
        );

Line: 986


    rollback to update_pl_sii_details;

Line: 1001


    rollback to update_pl_sii_details;

Line: 1011


end update_pl_sii_details;

Line: 1017


procedure delete_pl_sii_details
  (p_validate                      in     boolean  default false
  ,p_effective_date                in     date
  ,p_datetrack_delete_mode         in     varchar2
  ,p_sii_details_id                in     number
  ,p_object_version_number         in out nocopy   number
  ,p_effective_start_date          out nocopy   date
  ,p_effective_end_date            out nocopy   date
  )
   is
  --
  -- Declare cursors and local variables
  --
  l_effective_date         date;

Line: 1031


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

Line: 1048


  savepoint delete_pl_sii_details;

Line: 1066


    PAY_PL_SII_BK3.delete_pl_sii_details_b
      (p_effective_date          => p_effective_date
      ,p_sii_details_id          => p_sii_details_id
      ,p_datetrack_delete_mode   => p_datetrack_delete_mode
      ,p_object_version_number   => p_object_version_number
      );

Line: 1075


        (p_module_name => 'delete_pl_sii_details'
        ,p_hook_type   => 'BP'
        );

Line: 1086


     ,p_datetrack_mode         => p_datetrack_delete_mode
     ,p_sii_details_id         => p_sii_details_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: 1100


   pay_pl_sii_bk3.delete_pl_sii_details_a
     (p_effective_date        => p_effective_date
     ,p_sii_details_id        => p_sii_details_id
     ,p_datetrack_delete_mode => p_datetrack_delete_mode
     ,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: 1112


        (p_module_name => 'delete_pl_sii_details'
        ,p_hook_type   => 'AP'
        );

Line: 1138


    rollback to delete_pl_sii_details;

Line: 1163


end delete_pl_sii_details;