APPS.PAY_GB_WNU_RULES SQL Statements

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

Line: 3

Procedure wnu_update
  (p_assignment_id                in    number,
   p_effective_date               in    date,
   p_assignment_number            in    varchar2 default null,
   p_assignment_number_old        in    varchar2 default null,
   p_not_included_in_wnu          in    varchar2 default null,
   p_object_version_number        in out NOCOPY number,
   p_assignment_extra_info_id     out NOCOPY  number
  ) is
--
l_ass_extra_info_id      number(9):= null;

Line: 26

       select upper(apf.current_employee_flag)
       from   per_all_people_f apf,
	      per_all_assignments_f aaf
       where  aaf.person_id = apf.person_id
       and    aaf.assignment_id = p_assignment_id
       and    p_effective_date between
	      apf.effective_start_date and apf.effective_end_date;

Line: 35

       select aei.assignment_extra_info_id ,
              object_version_number,
              aei_information2
       from   per_assignment_extra_info aei
       where  aei.assignment_id = p_assignment_id
       and    information_type = 'GB_WNU';

Line: 43

       select pbg.legislation_code
       from   per_business_groups pbg,
	      per_all_assignments_f aaf
       where  aaf.assignment_id = p_assignment_id
       and    aaf.business_group_id = pbg.business_group_id
       and    p_effective_date between
	      aaf.effective_start_date and aaf.effective_end_date;

Line: 136

             hr_assignment_extra_info_api.update_assignment_extra_info
            (p_validate                       => false,
	     p_object_version_number          => l_ovn,
             p_assignment_extra_info_id       => l_ass_extra_info_id,
             p_aei_information_category       => 'GB_WNU',
             p_aei_information1               => l_ass_number_old,
             p_aei_information2               => l_not_included_in_wnu
            );

Line: 161

Procedure wnu_update
  (p_person_id                    in number,
   p_effective_date               in    date,
   p_aggregated_assignment        in    varchar2 default null,
   p_ni_number_update             in    varchar2 default null,
   p_not_included_in_wnu          in    varchar2 default null,
   p_object_version_number        in out NOCOPY number,
   p_assignment_extra_info_id     out NOCOPY  number
  ) is
--
l_ass_extra_info_id      number(9):= null;

Line: 182

l_ni_number_update       varchar2(30):= null;

Line: 185

       select upper(apf.current_employee_flag)
       from   per_all_people_f apf
       where  apf.person_id = p_person_id
       and    p_effective_date between
	      apf.effective_start_date and apf.effective_end_date;

Line: 192

       select aei.assignment_extra_info_id ,
              object_version_number,
              aei_information1,
              aei_information2,
              aei_information3
       from   per_assignment_extra_info aei
       where  aei.assignment_id = c_assignment_id
       and    information_type = 'GB_WNU';

Line: 202

       select aaf.assignment_id assignment_id
       from  per_all_assignments_f aaf
       where aaf.person_id = p_person_id
       and   p_effective_date between
	     aaf.effective_start_date and aaf.effective_end_date;

Line: 209

       select pbg.legislation_code
       from   per_business_groups pbg,
	      per_all_assignments_f aaf
       where  aaf.assignment_id = c_assignment_id
       and    aaf.business_group_id = pbg.business_group_id
       and    p_effective_date between
	      aaf.effective_start_date and aaf.effective_end_date;

Line: 218

       select min(aaf.assignment_id) assignment_id
       from  per_all_assignments_f aaf,
             hr_soft_coding_keyflex hsck,
             pay_all_payrolls_f papf,
             per_assignment_status_types past
       where aaf.person_id = p_person_id
       AND   p_effective_date between
	     aaf.effective_start_date and aaf.effective_end_date
       AND   hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
       AND   papf.payroll_id =aaf.payroll_id
       AND   past.assignment_status_type_id = aaf.assignment_status_type_id
       AND   aaf.person_id = p_person_id
       AND   past.per_system_status='ACTIVE_ASSIGN'
       AND   p_effective_date BETWEEN aaf.effective_start_date AND aaf.effective_end_date
       AND   p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
       AND   hsck.segment1 in ( SELECT distinct(hsck.segment1)
                               FROM hr_soft_coding_keyflex hsck2,
                                    pay_all_payrolls_f papf2,
                                    per_all_assignments_f paaf,
                                    per_assignment_status_types past2
                               WHERE hsck2.soft_coding_keyflex_id = papf2.soft_coding_keyflex_id
                               AND papf2.payroll_id =paaf.payroll_id
                               AND past2.assignment_status_type_id = paaf.assignment_status_type_id
                               AND paaf.person_id = p_person_id
                               AND past2.per_system_status='ACTIVE_ASSIGN'
                               AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
                               AND p_effective_date BETWEEN papf2.effective_start_date AND papf2.effective_end_date)
       GROUP BY hsck.segment1;

Line: 288

           fetch csr_extra_info into l_ass_extra_info_id, l_ovn,l_ass_number_old, l_not_included_in_wnu,l_ni_number_update;

Line: 308

               p_aei_information3               => p_ni_number_update,
  	       p_object_version_number          => l_ovn_out,
               p_assignment_extra_info_id       => l_ass_extra_info_id_out
              );

Line: 326

               if nvl(l_ni_number_update,'N') <> 'Y' then
               hr_assignment_extra_info_api.update_assignment_extra_info
              (p_validate                       => false,
  	       p_object_version_number          => l_ovn,
               p_assignment_extra_info_id       => l_ass_extra_info_id,
               p_aei_information_category       => 'GB_WNU',
               p_aei_information1               => l_ass_number_old,
               p_aei_information2               => l_not_included_in_wnu,
               p_aei_information3               => p_ni_number_update
              );

Line: 372

         fetch csr_extra_info into l_ass_extra_info_id, l_ovn,l_ass_number_old, l_not_included_in_wnu,l_ni_number_update;

Line: 394

             p_aei_information3               => p_ni_number_update
            );

Line: 410

             if nvl(l_ni_number_update,'N') <> 'Y' then
             hr_assignment_extra_info_api.update_assignment_extra_info
            (p_validate                       => false,
	     p_object_version_number          => l_ovn,
             p_assignment_extra_info_id       => l_ass_extra_info_id,
             p_aei_information_category       => 'GB_WNU',
             p_aei_information1               => l_ass_number_old,
             p_aei_information2               => l_not_included_in_wnu,
             p_aei_information3               => p_ni_number_update
             );