APPS.PAY_KR_FF_FUNCTIONS_PKG SQL Statements

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

Line: 39

		select	legislative_parameters
		from	pay_payroll_actions
		where	payroll_action_id = p_payroll_action_id;

Line: 47

		g_legislative_parameter_tbl.delete;

Line: 193

					select	effective_date
					into	g_effective_date
					from	fnd_sessions
					where	session_id = userenv('sessionid');

Line: 312

		select
			hr_ni_chk_pkg.chk_nat_id_format(per.national_identifier, 'DDDDDD-DDDDDDD') NI
		from	per_people_f		per,
			per_assignments_f	asg
		where	asg.assignment_id	=	p_assignment_id
		and	per.person_id		=	asg.person_id
		and	p_effective_date between per.effective_start_date and per.effective_end_date
		and	p_effective_date between asg.effective_start_date and asg.effective_end_date;

Line: 557

        	select	nvl(dis_information3, 'Y')
        	from	per_disabilities_f
		where	person_id = p_person_id
	       	and	dis_information_category = 'KR'
		and	p_effective_date between effective_start_date and effective_end_date
		order by dis_information3 ;

Line: 611

  SELECT lookup_code
  FROM hr_leg_lookups
  WHERE lookup_type = 'CONTACT'
    AND meaning LIKE '%Child%'
    AND lookup_code = p_contact_type;

Line: 663

	select pei.pei_information1
	  from per_people_f           pap
	       ,per_people_extra_info pei
	       ,per_assignments_f     paa
	 where paa.assignment_id = p_assignment_id
	   and paa.business_group_id = p_business_group_id
	   and paa.person_id = pap.person_id
	   and pap.person_id = pei.person_id
	   and pei.information_type = 'PER_KR_NP_EXCEPTIONS'
	   and p_date_earned between paa.effective_start_date and paa.effective_end_date
	   and p_date_earned between pap.effective_start_date and pap.effective_end_date
	   and p_date_earned between fnd_date.canonical_to_date(pei.pei_information2)
	                         and fnd_date.canonical_to_date(pei.pei_information3);

Line: 701

  SELECT lookup_code
  FROM hr_leg_lookups
  WHERE lookup_type = 'CONTACT'
    AND meaning LIKE '%Child%'
    AND lookup_code = p_contact_type;

Line: 957

		select
			nvl(cei.cei_information13, 'N')
		from	per_people_f		        per,
			per_contact_relationships	ctr,
			per_assignments_f		asg,
			per_contact_extra_info_f	cei
		where	asg.assignment_id = p_assignment_id
		and	p_date_earned
			between asg.effective_start_date and asg.effective_end_date
		and	ctr.person_id = asg.person_id
		and	ctr.cont_information_category = 'KR'
		and	ctr.cont_information1 = 'Y'
		and    ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
		         or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
		        )
		and	per.person_id = ctr.contact_person_id
		and	cei.contact_relationship_id = ctr.contact_relationship_id
		and	cei.information_type = 'KR_DPNT_EXPENSE_INFO'
		and	to_char(cei.effective_start_date, 'YYYY') = to_char(p_date_earned,'YYYY')
		and	(
				(
					p_date_earned
					between per.effective_start_date and per.effective_end_date
				)
				or
				(
					per.start_date = per.effective_start_date
				and	not exists(
					    select  null
					    from    per_people_f per2
					    where   per2.person_id = per.person_id
					    and     p_date_earned
						    between per2.effective_start_date and per2.effective_end_date)
				)
			);

Line: 1113

		select
			decode(ctr.cont_information2, 'Y', dpnt_spouse_flag(ctr.contact_type, ctr.cont_information11), 'N') DPNT_SPOUSE_FLAG,
			decode(ctr.cont_information2, 'Y', aged_dpnt_flag(ctr.contact_type,ctr.cont_information11, per.national_identifier, p_date_earned), 'N') AGED_DPNT_FLAG,
			decode(ctr.cont_information2, 'Y', adult_dpnt_flag(ctr.contact_type, ctr.cont_information11, per.national_identifier, p_date_earned,nvl(ctr.cont_information4, 'N'),nvl(ctr.cont_information8, 'N')), 'N') ADULT_DPNT_FLAG,
			decode(ctr.cont_information2, 'Y', underaged_dpnt_flag(ctr.contact_type, ctr.cont_information11, per.national_identifier, p_date_earned), 'N')	 UNDERAGED_DPNT_FLAG,
			decode(ctr.cont_information3, 'Y', aged_flag(per.national_identifier, p_date_earned), 'N') AGED_FLAG,
			-- Bug 3172960
			decode(ctr.cont_information3, 'Y', super_aged_flag(per.national_identifier, p_date_earned), 'N') SUPER_AGED_FLAG,
			nvl(ctr.cont_information4, 'N')	DISABLED_FLAG,
			decode(ctr.cont_information7, 'Y', child_flag(ctr.cont_information11,ctr.contact_type,per.national_identifier, p_date_earned), 'N') CHILD_FLAG,
			-- Bug 6784288; Bug 6825145; Bug 7615517

Line: 1148

					    select  null
					    from    per_people_f per2
					    where   per2.person_id = per.person_id
					    and     p_date_earned
						    between per2.effective_start_date and per2.effective_end_date)
				)
			);

Line: 1156

		select
			per.national_identifier,
			per.marital_status,
			ni_sex(per.national_identifier)				NI_SEX,
			aged_flag(per.national_identifier, p_date_earned)	AGED_FLAG,
			disabled_flag(per.person_id, p_date_earned)		DISABLED_FLAG,
			child_flag('0','E',per.national_identifier, p_date_earned)	CHILD_FLAG,
			nvl(pei.pei_information3,'N')                           FEMALE_EMP_DOC,
			super_aged_flag(per.national_identifier, p_date_earned)	SUPER_AGED_FLAG		-- Bug 4124430
		from	per_people_f      per,
			per_assignments_f asg,
			per_people_extra_info pei
		where	asg.assignment_id = p_assignment_id
		and	p_date_earned
			between asg.effective_start_date and asg.effective_end_date
		and	per.person_id = asg.person_id
		and	p_date_earned
			between per.effective_start_date and per.effective_end_date
		and     per.person_id = pei.person_id(+)
		and     pei.information_type(+) = 'PER_KR_RELATED_YEA_INFORMATION';

Line: 1329

select pei_information8   loss_code
      ,pei_information9   loss_date
from per_people_extra_info  pei
    ,per_people_f           pp
    ,per_assignments_f      paa
    ,per_time_periods       ptp
where paa.assignment_id     = p_assignment_id
  and paa.business_group_id = p_business_group_id
  and pp.person_id          = paa.person_id
  and pei.person_id         = pp.person_id
  and pei.information_type  = 'PER_KR_EMPLOYMENT_INS_INFO'
  and ptp.payroll_id        = paa.payroll_id
  and p_date_earned between paa.effective_start_date and paa.effective_end_date
  and p_date_earned between pp.effective_start_date  and pp.effective_end_date
  and p_date_earned between ptp.start_date and ptp.end_date
  and ptp.end_date >= fnd_date.canonical_to_date(pei_information9);

Line: 1349

select pei_information1       ei_exception_code
      ,pei_information4       ei_exception_type
from per_people_extra_info  pei
    ,per_assignments_f      paa
    ,per_time_periods       ptp
where paa.assignment_id      = p_assignment_id
  and paa.business_group_id  = p_business_group_id
  and pei.person_id          = paa.person_id
  and pei.information_type   ='PER_KR_EI_EXCEPTIONS'
  and p_date_earned between paa.effective_start_date and paa.effective_end_date
  and ptp.payroll_id         = paa.payroll_id
  and p_date_earned between ptp.start_date and ptp.end_date
  and fnd_date.canonical_to_date(pei.pei_information2) <= ptp.end_date
  and fnd_date.canonical_to_date(pei.pei_information3) >= ptp.start_date
order by pei.pei_information2 desc,pei.pei_information3 desc;

Line: 1444

      select nvl(cei_information1,0) cei_information1,
             nvl(cei_information2,0) cei_information2,
             nvl(cei_information3,0) cei_information3,
             nvl(cei_information4,0) cei_information4,
             nvl(cei_information5,0) cei_information5,
             nvl(cei_information6,0) cei_information6,
             nvl(cei_information7,0) cei_information7,
             nvl(cei_information8,0) cei_information8,
             nvl(cei_information9,0) cei_information9,
             nvl(cei_information10,0) cei_information10,
             nvl(cei_information11,0) cei_information11,
             nvl(cei_information16,0) cei_information16,
	nvl(cei_information17,0) cei_information17,
             nvl(cei_information18,0) cei_information18, -- Bug 14219478
             nvl(cei_information19,0) cei_information19, -- Bug 14219478
             nvl(cei_information20,0) cei_information20, -- Bug 14219478
             nvl(cei_information21,0) cei_information21,  -- Bug 14219478
             nvl(cei_information22,0) cei_information22, -- Bug 14219478
             nvl(cei_information23,0) cei_information23, -- Bug 14219478
             nvl(cei_information24,0) cei_information24  -- Bug 14219478
             --
       from  per_contact_extra_info_f
             --
      where  contact_Extra_info_id = p_cont_extra_info_id;

Line: 1687

SELECT paa.assignment_action_id ass_act_id
from pay_payroll_actions ppa,
 pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and     to_char(ppa.effective_date, 'YYYY') = p_effective_year
and ppa.report_type = 'YEA'
and ppa.report_qualifier = 'KR'
and ppa.action_type in ('B','X')
order by paa.action_sequence desc ;

Line: 1731

  select
	ctr.cont_information2 itax_law,
	nvl(ctr.cont_information11, '0') kr_cont_type,
	decode(ctr.contact_type,   'P',   '1',   'S',   '3',   'A',   '4',   'C',   '4',   'R',   '4', 'T',   '4',  '6') cont_type
    from
	per_contact_relationships	ctr
   where
	 ctr.person_id = p_person_id
     and ctr.cont_information_category = 'KR'
     and ctr.cont_information1 = 'Y'
     and ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
		         or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
		        )
     and     ctr.contact_person_id = p_contact_person_id
     and     ctr.contact_relationship_id = p_contact_relationship_id;

Line: 1773

      SELECT   	upper(nvl(prrv.result_value, 'N'))
	FROM    pay_input_values_f       piv
	       ,pay_run_result_values    prrv
	       ,pay_run_results          prr
	       ,pay_payroll_actions      ppa
	       ,pay_assignment_actions   paa
	       ,pay_element_types_f      pet
	WHERE   paa.assignment_action_id = l_assignment_action_id
	and     ppa.payroll_action_id 	= paa.payroll_action_id
	and     prr.assignment_action_id = paa.assignment_action_id
	and     prr.status 		in ('P', 'PA')
	and     prr.element_type_id 	= pet.element_type_id
	and     pet.element_name 	= 'LTCI_PREM'
        and     piv.legislation_code 	= 'KR'
        and     pet.legislation_code 	= 'KR'
	and     prrv.run_result_id 	= prr.run_result_id
	and     piv.input_value_id 	= prrv.input_value_id
	and	piv.name 		= l_input_value_name
	and     ppa.effective_date
		between piv.effective_start_date and piv.effective_end_date;

Line: 1851

  select
        ctr.cont_information2 itax_law,
	nvl(ctr.cont_information11, '0') kr_cont_type,
	decode(ctr.contact_type,   'P',   '1',   'S',   '3',   'A',   '4',   'C',   '4',   'R',   '4',  'T',   '4',  '6') cont_type
    from
	per_contact_relationships	ctr
   where
	 ctr.person_id = p_person_id
     and ctr.cont_information_category = 'KR'
     and ctr.cont_information1 = 'Y'
     and ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
		         or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
		        )
     and     ctr.contact_person_id = p_contact_person_id
     and     ctr.contact_relationship_id = p_contact_relationship_id;

Line: 1926

          select to_number(glb.global_value,'99999999999999999999.99999')
          from   ff_globals_f glb
          where glb.global_name = p_glbvar
          and   p_process_date between glb.effective_start_date and glb.effective_end_date;

Line: 1957

      SELECT   	upper(nvl(prrv.result_value, 'N'))
	FROM    pay_input_values_f       piv
	       ,pay_run_result_values    prrv
	       ,pay_run_results          prr
	       ,pay_payroll_actions      ppa
	       ,pay_assignment_actions   paa
	       ,pay_element_types_f      pet
	WHERE   paa.assignment_action_id = l_assignment_action_id
	and     ppa.payroll_action_id 	= paa.payroll_action_id
	and     prr.assignment_action_id = paa.assignment_action_id
	and     prr.status 		in ('P', 'PA')
	and     prr.element_type_id 	= pet.element_type_id
	and     pet.element_name 	= l_element_name
        and     piv.legislation_code 	= 'KR'
        and     pet.legislation_code 	= 'KR'
	and     prrv.run_result_id 	= prr.run_result_id
	and     piv.input_value_id 	= prrv.input_value_id
	and	piv.name 		= l_input_value_name
	and     ppa.effective_date
		between piv.effective_start_date and piv.effective_end_date;

Line: 2007

      SELECT   	nvl(prrv.result_value, 0)
	FROM    pay_input_values_f       piv
	       ,pay_run_result_values    prrv
	       ,pay_run_results          prr
	       ,pay_payroll_actions      ppa
	       ,pay_assignment_actions   paa
	       ,pay_element_types_f      pet
	WHERE   paa.assignment_action_id = l_assignment_action_id
	and     ppa.payroll_action_id 	= paa.payroll_action_id
	and     prr.assignment_action_id = paa.assignment_action_id
	and     prr.status 		in ('P', 'PA')
	and     prr.element_type_id 	= pet.element_type_id
	and     pet.element_name 	= l_element_name
        and     piv.legislation_code 	= 'KR'
        and     pet.legislation_code 	= 'KR'
	and     prrv.run_result_id 	= prr.run_result_id
	and     piv.input_value_id 	= prrv.input_value_id
	and	piv.name 		= l_input_value_name
	and     ppa.effective_date
		between piv.effective_start_date and piv.effective_end_date;

Line: 2059

      SELECT   	upper(nvl(prrv.result_value, 'Y'))
	FROM    pay_input_values_f       piv
	       ,pay_run_result_values    prrv
	       ,pay_run_results          prr
	       ,pay_payroll_actions      ppa
	       ,pay_assignment_actions   paa
	       ,pay_element_types_f      pet
	WHERE   paa.assignment_action_id = l_assignment_action_id
	and     ppa.payroll_action_id 	= paa.payroll_action_id
	and     prr.assignment_action_id = paa.assignment_action_id
	and     prr.status 		in ('P', 'PA')
	and     prr.element_type_id 	= pet.element_type_id
	and     pet.element_name 	= l_element_name
        and     piv.legislation_code 	= 'KR'
        and     pet.legislation_code 	= 'KR'
	and     prrv.run_result_id 	= prr.run_result_id
	and     piv.input_value_id 	= prrv.input_value_id
	and	piv.name 		= l_input_value_name
	and     ppa.effective_date
		between piv.effective_start_date and piv.effective_end_date;

Line: 2109

      SELECT   	nvl(fnd_date.canonical_to_date(prrv.result_value), to_date('01-01-1900','dd-mm-yyyy'))
	FROM    pay_input_values_f       piv
	       ,pay_run_result_values    prrv
	       ,pay_run_results          prr
	       ,pay_payroll_actions      ppa
	       ,pay_assignment_actions   paa
	       ,pay_element_types_f      pet
	WHERE   paa.assignment_action_id = l_assignment_action_id
	and     ppa.payroll_action_id 	= paa.payroll_action_id
	and     prr.assignment_action_id = paa.assignment_action_id
	and     prr.status 		in ('P', 'PA')
	and     prr.element_type_id 	= pet.element_type_id
	and     pet.element_name 	= l_element_name
        and     piv.legislation_code 	= 'KR'
        and     pet.legislation_code 	= 'KR'
	and     prrv.run_result_id 	= prr.run_result_id
	and     piv.input_value_id 	= prrv.input_value_id
	and	piv.name 		= l_input_value_name
	and     ppa.effective_date
		between piv.effective_start_date and piv.effective_end_date;

Line: 2188

SELECT peevf.element_entry_id	 	 element_entry_id,
       sum(peevf.screen_entry_value)	 prev_earnings
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_ER_INFO'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('SEP_INS',    'SEP_PAY',    'SP_SEP_ALW')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
group by peevf.element_entry_id;

Line: 2211

SELECT peevf.screen_entry_value 	prev_earnings
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_ER_INFO'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name = 'SP_SEP_ALW'
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;

Line: 2232

SELECT peevf.element_entry_id 		element_entry_id
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_ER_INFO'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('ELIGIBLE_POST_TAX_DEDUC_FLAG')
and nvl(peevf.screen_entry_value,'N') = 'Y'
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;

Line: 2254

SELECT fnd_date.canonical_to_date(peevf.screen_entry_value) dt_value
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_ER_INFO'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('H_DATE','L_DATE','FINAL_INT_DATE')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
order by pivf.name;

Line: 2277

SELECT peevf.element_entry_id	 	 element_entry_id
 FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_SEP_PENS_DTLS'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('BP_NUMBER')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
and peevf.screen_entry_value in (SELECT   peevf.screen_entry_value 	 bus_reg_num
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_ER_INFO'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('BP_NUMBER')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
group by peevf.screen_entry_value)

group by peevf.element_entry_id;

Line: 2318

SELECT       nvl(peevf.screen_entry_value,0)	 entry_value
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_SEP_PENS_DTLS'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('PRINCIPAL_INTRST','PERS_CONTRIBUTION','PENS_EXEM','AMT_EXP_STAT_SEP','AMT_EXP_NONSTAT_SEP','TOTAL_RECEIVED')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
order by pivf.name;

Line: 2338

/* Bug 8601387: 2009 Non Statutory Separation Pay Tax Receipt Layout Updates */
cursor csr_prev_lsa(l_element_entry_id in number,l_assignment_action_id in number) is
select nvl(result_value,0) result_value
from pay_element_types_f petf,
     pay_run_results prr,
     pay_run_result_values prrv,
     pay_element_entries_f peef
where petf.element_name = 'PREV_EMP_SEP_LUMP_SUM_AMOUNT'
and petf.element_type_id = prr.element_type_id
and prr.assignment_action_id = l_assignment_action_id
and prrv.run_result_id = prr.run_result_id
and prr.source_id = l_element_entry_id
and petf.legislation_code = 'KR';

Line: 3904

SELECT peevf.element_entry_id	 	 element_entry_id,
       peevf.screen_entry_value          interim_date
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_ER_INFO'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('FINAL_INT_DATE')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;

Line: 3924

SELECT sum(nvl(peevf.screen_entry_value,0)) non_stat_earnings
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peevf.element_entry_id = l_element_entry_id
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_ER_INFO'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name in ('SP_SEP_ALW')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date;

Line: 3944

SELECT peevf.element_entry_id	 	 element_entry_id
 FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_SEP_PENS_DTLS'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('BP_NUMBER')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
and peevf.screen_entry_value in (SELECT   peevf.screen_entry_value 	 bus_reg_num
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_ER_INFO'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('BP_NUMBER')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
group by peevf.screen_entry_value);

Line: 3982

SELECT       nvl(peevf.screen_entry_value,0)	 entry_value
FROM pay_element_entry_values_f peevf,
     pay_element_entries_f peef,
     pay_element_types_f petf,
     pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
and peevf.element_entry_id = nvl(l_element_entry_id,peevf.element_entry_id)
and peef.element_type_id = petf.element_type_id
and petf.element_name LIKE 'PREV_SEP_PENS_DTLS'
and petf.legislation_code = 'KR'
and peef.assignment_id = l_assignment_id
and peevf.input_value_id = pivf.input_value_id
and pivf.element_type_id = petf.element_type_id
and pivf.name IN ('PRINCIPAL_INTRST','PERS_CONTRIBUTION','RECEIVED_AMOUNT_NONSTAT','DEFERRED_AMOUNT_NONSTAT','PENS_EXEM','AMT_EXP_NONSTAT_SEP','TOTAL_RECEIVED','LUMP_SUM_AMT_NST')
and l_effective_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date
and l_effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
and l_effective_date BETWEEN petf.effective_start_date AND petf.effective_end_date
and l_effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
order by pivf.name;

Line: 4159

	SELECT  'Y'
	from	pay_payroll_actions ppa,
		pay_assignment_actions paa
	where	paa.assignment_id = p_assignment_id
	and	paa.action_status = 'C'
	and	ppa.payroll_action_id = paa.payroll_action_id
	and	ppa.report_type = 'YEA'
	and	ppa.report_qualifier = 'KR'
	and 	to_char(ppa.effective_date, 'YYYY') = to_char(p_effective_date, 'YYYY')
	and	ppa.action_type in ('B','X');

Line: 4241

  select
decode(ROAD_NAME, null, rtrim(CITY_PROVINCE||' '||DISTRICT||' '||TOWN_VILLAGE||' '||HOUSE_NUMBER),
RTRIM(CITY_PROVINCE||' '||DISTRICT||' '||TOWN_VILLAGE||' '||ROAD_NAME||' '||HOUSE_NUMBER||' '||
decode((LEGISLATIVE_DISTRICT || PUBLIC_HOUSING_NAME) , null, ' ', '('||LEGISLATIVE_DISTRICT ||
decode(LEGISLATIVE_DISTRICT, null, PUBLIC_HOUSING_NAME,decode(PUBLIC_HOUSING_NAME, null, null,', '||PUBLIC_HOUSING_NAME)) || ')' )))
  from   per_kr_addresses
  where  postal_code_id = l_postal_code_id;

Line: 4275

 select fnd_date.canonical_to_date(aei.AEI_INFORMATION1),
	fnd_date.canonical_to_date(aei.AEI_INFORMATION2)
 from per_assignment_extra_info aei
 where aei.information_type = p_information_type
   and aei.assignment_id = p_assignment_id;