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_GB_PAYE_CALC SQL Statements

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

Line: 256


   select pur.row_low_range_or_name,
   	  pur.row_high_range,
   	  puci.value
   from pay_user_tables put,
   	pay_user_rows_f pur,
   	pay_user_columns puc,
   	pay_user_column_instances_f puci
   where put.user_table_id = puc.user_table_id
   and pur.user_table_id = puc.user_table_id
   and puci.user_row_id = pur.user_row_id
   and puci.user_column_id = puc.user_column_id
   and upper(puc.user_column_name) = upper('paye_percentage')
   and put.legislation_code = 'GB'
   and upper(put.user_table_name) = upper('PAYE')
   and p_session_date between puci.effective_start_date and puci.effective_end_date
   and p_session_date between pur.effective_start_date and pur.effective_end_date
   order by pur.row_low_range_or_name;

Line: 276


   select pur.row_low_range_or_name,
   	  pur.row_high_range,
   	  puci.value
   from pay_user_tables put,
   	pay_user_rows_f pur,
   	pay_user_columns puc,
   	pay_user_column_instances_f puci
   where put.user_table_id = puc.user_table_id
   and pur.user_table_id = puc.user_table_id
   and puci.user_row_id = pur.user_row_id
   and puci.user_column_id = puc.user_column_id
   and upper(puc.user_column_name) = upper('paye_percentage_svr')
   and put.legislation_code = 'GB'
   and upper(put.user_table_name) = upper('PAYE')
   and p_session_date between puci.effective_start_date and puci.effective_end_date
   and p_session_date between pur.effective_start_date and pur.effective_end_date
   order by pur.row_low_range_or_name;

Line: 637


		select min(effective_start_date) effective_start_date
		from   per_all_assignments_f
		where  assignment_id = p_assignment_id
	;

Line: 644


	    select   ppa.payroll_id old_payroll_id
		from   pay_payroll_actions ppa,
	      	       pay_assignment_actions paa
		where  ppa.payroll_action_id = paa.payroll_action_id
	        and    paa.assignment_id     = p_assignment_id
	        and    ppa.payroll_action_id <> p_payroll_action_id
		and    ppa.action_type       in  ('Q', 'R', 'B', 'I' , 'V')
		and    ppa.effective_date    >= l_date_soy
		and    ppa.effective_date    <= l_date_eoy ;

Line: 658


		select count(*)     l_exist           -- if this cursor fetches '1', that means new payroll is valid.
		from   pay_all_payrolls_f           pap
	      	      ,hr_soft_coding_keyflex       scl
		where  pap.payroll_id               = p_new_payroll_id
		and    pap.soft_coding_keyflex_id   = scl.soft_coding_keyflex_id
		and    scl.segment1                 in
	        (
	        	select distinct scl.segment1
			from   pay_all_payrolls_f           pap
		      	  ,hr_soft_coding_keyflex       scl

			where
			      pap.payroll_id             = p_old_payroll_id
			and   pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
            and   pap.effective_start_date   <= l_effective_date
			and   pap.effective_end_date     >= l_effective_date
		)
	;

Line: 678


  select count (distinct nvl(per_information10,'N') ) l_count
  from per_all_people_f papf , per_all_assignments_f paaf
    where paaf.assignment_id=p_assignment_id
    and papf.person_id=paaf.person_id
    and papf.effective_start_date > l_date_soy
    and papf.effective_start_date < l_date_eoy  ;

Line: 689


   select hsck.segment1
  from pay_all_payrolls_f papf,
       hr_soft_coding_keyflex hsck
  where
       papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
       and papf.payroll_id=c_payroll_id;

Line: 699


select count(*)
from
(
 select
     distinct
     ppev.INPUT_VALUE_ID1,
     ppev.TAX_CODE,
     ppev.INPUT_VALUE_ID2,
     ppev.D_TAX_BASIS,
     ppev.INPUT_VALUE_ID4,
     ppev.D_PAY_PREVIOUS,
     ppev.INPUT_VALUE_ID5,
     ppev.D_TAX_PREVIOUS,
     ppev.INPUT_VALUE_ID3,
     ppev.D_REFUNDABLE,
     ppev.INPUT_VALUE_ID6,
     ppev.D_AUTHORITY,
     ppev.entry_information1,
     ppev.entry_information2

     from
(
SELECT ele.rowid ROW_ID, ele.element_entry_id, min(decode(inv.name, 'Tax Code', eev.input_value_id, null)) INPUT_VALUE_ID1,
min(decode(inv.name, 'Tax Code', eev.screen_entry_value, null)) Tax_Code,
min(decode(inv.name, 'Tax Basis', eev.input_value_id, null)) INPUT_VALUE_ID2,
min(decode(inv.name, 'Tax Basis', substr(HR_GENERAL.DECODE_LOOKUP('GB_TAX_BASIS',eev.screen_entry_value),1,80),null)) D_Tax_Basis,
min(decode(inv.name, 'Tax Basis', eev.screen_entry_value, null)) Tax_Basis, min(decode(inv.name, 'Refundable',
eev.input_value_id, null)) INPUT_VALUE_ID3,
min(decode(inv.name, 'Refundable', substr(HR_GENERAL.DECODE_LOOKUP('GB_REFUNDABLE',eev.screen_entry_value),1,80),null)) D_Refundable,
min(decode(inv.name, 'Refundable', eev.screen_entry_value, null)) Refundable,
min(decode(inv.name, 'Pay Previous', eev.input_value_id, null)) INPUT_VALUE_ID4,
hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Pay_Previous,
min(decode(inv.name, 'Pay Previous', eev.screen_entry_value, null)) Pay_Previous,
min(decode(inv.name, 'Tax Previous', eev.input_value_id, null)) INPUT_VALUE_ID5,
hr_chkfmt.changeformat(nvl(min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)), 0), 'M', 'GBP') D_Tax_Previous,
min(decode(inv.name, 'Tax Previous', eev.screen_entry_value, null)) Tax_Previous,
min(decode(inv.name, 'Authority', eev.input_value_id, null)) INPUT_VALUE_ID6,
min(decode(inv.name, 'Authority', substr(HR_GENERAL.DECODE_LOOKUP('GB_AUTHORITY',eev.screen_entry_value),1,80),null)) D_AUTHORITY,
min(decode(inv.name, 'Authority', eev.screen_entry_value, null)) Authority,
ele.assignment_id,
ele.effective_start_date,
ele.effective_end_date,
ele.entry_information_category,
ele.entry_information1,
ele.entry_information2
from
pay_element_entries_f ele,
pay_element_entry_values_f eev,
pay_input_values_f inv,
pay_element_links_f lnk,
pay_element_types_f elt,

pay_all_payrolls_f papf,
per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck

where  ele.element_entry_id = eev.element_entry_id
AND c_pay_run_date between ele.effective_start_date and ele.effective_end_date
AND eev.input_value_id + 0 = inv.input_value_id
AND c_pay_run_date between eev.effective_start_date and eev.effective_end_date
AND inv.element_type_id = elt.element_type_id
AND c_pay_run_date between inv.effective_start_date and inv.effective_end_date
AND ele.element_link_id = lnk.element_link_id
AND elt.element_type_id = lnk.element_type_id
AND c_pay_run_date between lnk.effective_start_date and lnk.effective_end_date
AND elt.element_name = 'PAYE Details'
AND c_pay_run_date between elt.effective_start_date and elt.effective_end_date

AND c_pay_run_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND c_pay_run_date BETWEEN papf.effective_start_date AND papf.effective_end_date

AND ele.assignment_id=paaf.assignment_id

AND papf.payroll_id=paaf.payroll_id
AND papf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND hsck.segment1=c_tax_reference
AND paaf.person_id = (select distinct pap.person_id
                      from per_all_people_f pap,
                           per_all_assignments_f paaf1
                      where paaf1.person_id=pap.person_id
                      and   paaf1.assignment_id=c_assignment_id)
AND pay_gb_eoy_archive.get_agg_active_start (paaf.assignment_id, c_tax_reference,c_pay_run_date)
  = pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_reference,c_pay_run_date)
AND pay_gb_eoy_archive.get_agg_active_end(paaf.assignment_id, c_tax_reference,c_pay_run_date)
  = pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_reference,c_pay_run_date)
/*Bug 7389532 - Added the distinct and null condition */
AND paaf.period_of_service_id = (select distinct period_of_service_id
                                        from per_all_assignments_f paaf2
					where paaf2.assignment_id=c_assignment_id
					and paaf.person_id =paaf2.person_id
					and period_of_service_id is not null)

group by ele.rowid, ele.element_entry_id, ele.assignment_id, ele.entry_information_category,
ele.entry_information1, ele.entry_information2, ele.effective_start_date, ele.effective_end_date
) ppev
);

Line: 815


select regular_payment_date into l_pay_run_date
 from per_time_periods ptp ,
      pay_payroll_actions ppa
 where ptp.time_period_id=ppa.time_period_id
 and ppa.payroll_action_id=p_payroll_action_id;

Line: 826


   select nvl(PER_INFORMATION10,'N') into l_aggregated_asg
       from per_all_people_f
       where person_id = (select distinct papf.person_id
                          from per_all_people_f papf, per_all_assignments_f paaf1
                          where  papf.person_id=paaf1.person_id
			   AND paaf1.assignment_id= p_assignment_id
			)
       and l_pay_run_date  between effective_start_date and effective_end_date;

Line: 847


select effective_date into l_effective_date
 from pay_payroll_actions ppa
 where ppa.payroll_action_id=p_payroll_action_id;

Line: 929


  select PEOPLE.DATE_OF_DEATH
  from   per_all_assignments_f           ASSIGN
        ,per_all_people_f               PEOPLE
        ,fnd_sessions                   SES
  where   SES.effective_date BETWEEN ASSIGN.effective_start_date
                            AND ASSIGN.effective_end_date
  and 	  SES.effective_date BETWEEN PEOPLE.effective_start_date
                            AND PEOPLE.effective_end_date
  and     ASSIGN.assignment_id           = p_assignmnet_id
  and     PEOPLE.person_id               = ASSIGN.person_id
  and     PEOPLE.per_information4        ='Y'
  and     PEOPLE.DATE_OF_DEATH is not null
  and     SES.session_id                 = USERENV('sessionid') ;