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

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

Line: 12


  select global_value
  from ff_globals_f
  Where global_name=p_name
  and legislation_code='IE'
  and p_effective_date between effective_start_date and effective_end_date;

Line: 44


select min(TPERIOD.start_date)
 from  pay_payroll_actions                      PACTION
,       per_time_periods                         TPERIOD
,       per_time_period_types                    TPTYPE
where   PACTION.payroll_action_id              = l_payroll_action_id
and     TPERIOD.payroll_id                 = PACTION.payroll_id
and     TPTYPE.period_type                 = TPERIOD.period_type
and to_char(PACTION.DATE_EARNED,'YYYY')=to_char(TPERIOD.END_DATE,'YYYY');

Line: 86


	select ptp.end_date
	from pay_payroll_actions ppa
	    , per_time_periods ptp
	WHERE ppa.payroll_action_id=p_payroll_action_id
	AND   ptp.payroll_id=ppa.payroll_id
	AND   ppa.DATE_EARNED between ptp.start_date and ptp.end_date;

Line: 121


select ptp.end_date end_date
from
  pay_payroll_actions ppa
, per_time_periods ptp
, per_time_period_types tptype
WHERE ppa.payroll_action_id=p_payroll_action_id
AND   ptp.payroll_id=ppa.payroll_id
AND   ptp.period_type = tptype.period_type
AND   c_minimum_end_date between PTP.start_date and PTP.end_date;

Line: 134


select count(ptp.period_num) pay_periods
from
  pay_payroll_actions ppa
, per_time_periods ptp
, per_time_period_types tptype
WHERE ppa.payroll_action_id=p_payroll_action_id
AND   ptp.payroll_id=ppa.payroll_id
AND   ptp.period_type = tptype.period_type
AND   ptp.end_date between
c_maximum_start_date  and c_end_date;

Line: 147


select count(ptp.period_num) pay_periods
from
  pay_payroll_actions ppa
, per_time_periods ptp
, per_time_period_types tptype
WHERE ppa.payroll_action_id=p_payroll_action_id
AND   ptp.payroll_id=ppa.payroll_id
AND   ptp.period_type = tptype.period_type
AND   ptp.end_date between
c_maximum_start_date and pay_ie_bik.get_least_date(p_payroll_action_id,
c_end_date);

Line: 220


select pay_balance_pkg.get_value(pdb.defined_balance_id, p_assignment_action_id,
null, null,null, null,null, p_source_id,p_date_earned) Cumulative_Taxable_Value
from
   pay_balance_dimensions pbd,
   pay_balance_types pbt,
   pay_defined_balances pdb
where
   pbt.balance_type_id = pdb.balance_type_id
   and pbd.balance_dimension_id = pdb.balance_dimension_id
   and pbd.legislation_code = pbt.legislation_code
   and pbt.balance_name= p_balance_name
   and pbd.dimension_name='_ELEMENT_YTD'
   and pbd.legislation_code='IE';

Line: 270


	open l_address_refcur for select address_line1
	from per_addresses
	where address_id=l_address_id;

Line: 274


	open l_address_refcur for select address_line2
	from per_addresses
	where address_id=l_address_id;

Line: 278


	open l_address_refcur for select address_line3
	from per_addresses
	where address_id=l_address_id;

Line: 282


	open l_address_refcur for select town_or_city
	from per_addresses
	where address_id=l_address_id;

Line: 286


	open l_address_refcur for select region_1
	from per_addresses
	where address_id=l_address_id;

Line: 290


	open l_address_refcur for select postal_code
	from per_addresses
	where address_id=l_address_id;

Line: 294


	open l_address_refcur for select country
	from per_addresses
	where address_id=l_address_id;

Line: 329


       open l_landlord_refcur for select hl.ADDRESS_LINE_1
       from hr_all_organization_units hou,
            hr_locations hl
       where hou.organization_id=l_address_id
       and hou.location_id=hl.location_id;

Line: 337


	open l_landlord_refcur for select hl.ADDRESS_LINE_2
        from hr_all_organization_units hou,
	     hr_locations hl
        where hou.organization_id=l_address_id
        and hou.location_id=hl.location_id;

Line: 345


        open l_landlord_refcur for select hl.ADDRESS_LINE_3
        from hr_all_organization_units hou,hr_locations hl
        where hou.organization_id=l_address_id
        and hou.location_id=hl.location_id;

Line: 352


         open l_landlord_refcur for select hl.town_or_city
         from hr_all_organization_units hou,hr_locations hl
         where hou.organization_id=l_address_id
         and hou.location_id=hl.location_id;

Line: 359


         open l_landlord_refcur for select hl.region_1
         from hr_all_organization_units hou,hr_locations hl
         where hou.organization_id=l_address_id
         and hou.location_id=hl.location_id;

Line: 366


         open l_landlord_refcur for select hl.postal_code
         from hr_all_organization_units hou,hr_locations hl
         where hou.organization_id=l_address_id
         and hou.location_id=hl.location_id;

Line: 373


         open l_landlord_refcur for select hl.country
         from hr_all_organization_units hou,hr_locations hl
         where hou.organization_id=l_address_id
         and hou.location_id=hl.location_id;

Line: 399


select nvl(sum(to_number(prrv.result_value)),0)
from pay_element_types_f pet,
       pay_input_values_f piv,
       pay_element_entries_f pee,
       pay_payroll_actions ppa,
       pay_assignment_actions paas,
       pay_run_results prr,
       pay_run_result_values prrv
where pee.element_entry_id=p_element_entry_id
      AND prr.source_id = pee.element_entry_id
      AND prr.element_type_id=pet.element_type_id
      AND (pet.element_name='IE BIK Company Vehicle Details'
      OR pet.element_name='IE BIK Company Vehicle 2009 Details') --8236523
      AND prr.run_result_id=prrv.run_result_id
      AND piv.name='Days Unavailable in Period'
      AND prrv.input_value_id = piv.input_value_id
      AND paas.assignment_action_id=prr.assignment_action_id
      AND paas.payroll_action_id=ppa.payroll_action_id
      AND paas.assignment_id=pee.assignment_id
      AND ppa.date_earned>( select min(ppa1.date_earned)   -- query is now based on date earned (5070091)
                                from pay_payroll_actions ppa1,
				     pay_assignment_actions paas1,
				     pay_run_results prr1,
                                     pay_element_entries_f pee1
				where ppa1.date_earned>=p_vehicle_alloc_end_date -- query is now based on date earned (5070091)
				  AND pee1.element_entry_id=p_element_entry_id
				  AND prr1.element_entry_id = pee1.element_entry_id
				  AND paas1.assignment_action_id=prr1.assignment_action_id
                          AND paas1.payroll_action_id=ppa1.payroll_action_id
				  AND prr1.source_id= pee1.element_entry_id) -- Added for bug 4771780
-- query is now based on date earned (5070091)
      AND ppa.date_earned<=p_curr_period_end_date
      AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
      AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
      AND ppa.effective_date between piv.effective_start_date and piv.effective_end_date;

Line: 451


select nvl(sum(to_number(prrv.result_value)),0)
from pay_element_types_f pet,
       pay_input_values_f piv,
       pay_element_entries_f pee,
       pay_payroll_actions ppa,
       pay_assignment_actions paas,
       pay_run_results prr,
       pay_run_result_values prrv
where pee.element_entry_id=p_element_entry_id
      AND prr.source_id = pee.element_entry_id
      AND prr.element_type_id=pet.element_type_id
      AND (pet.element_name='IE BIK Company Vehicle Details'
       OR pet.element_name='IE BIK Company Vehicle 2009 Details') --8236523
      AND prr.run_result_id=prrv.run_result_id
      AND piv.name='Total Mileage for Run'
      AND prrv.input_value_id = piv.input_value_id
      AND paas.assignment_action_id=prr.assignment_action_id
      AND paas.payroll_action_id=ppa.payroll_action_id
      AND paas.assignment_id=pee.assignment_id
      AND ppa.date_earned>( select min(ppa1.date_earned) -- query is now based on date earned (5070091)
                                from pay_payroll_actions ppa1,
						 pay_assignment_actions paas1,
						 pay_run_results prr1,
                                     pay_element_entries_f pee1
				where ppa1.date_earned>=p_vehicle_alloc_end_date -- query is now based on date earned (5070091)
				  AND pee1.element_entry_id=p_element_entry_id
				  AND prr1.element_entry_id = pee1.element_entry_id
				  AND paas1.assignment_action_id=prr1.assignment_action_id
                          AND paas1.payroll_action_id=ppa1.payroll_action_id
				  AND prr1.source_id= pee1.element_entry_id) -- Added for bug 4771780
      -- query is now based on date earned (5070091)
      AND ppa.date_earned<=p_curr_period_end_date
      AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
      AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
      AND ppa.effective_date between piv.effective_start_date and piv.effective_end_date;

Line: 503


select nvl(sum(to_number(prrv.result_value)),0)
from pay_element_types_f pet,
       pay_input_values_f piv,
       pay_element_entries_f pee,
       pay_payroll_actions ppa,
       pay_assignment_actions paas,
       pay_run_results prr,
       pay_run_result_values prrv
where pee.element_entry_id=p_element_entry_id
      AND prr.source_id = pee.element_entry_id
      AND prr.element_type_id=pet.element_type_id
      AND (pet.element_name='IE BIK Company Vehicle Details'
      OR pet.element_name='IE BIK Company Vehicle 2009 Details') --8236523
      AND prr.run_result_id=prrv.run_result_id
      AND piv.name='Business Mileage for Run'
      AND prrv.input_value_id = piv.input_value_id
      AND paas.assignment_action_id=prr.assignment_action_id
      AND paas.payroll_action_id=ppa.payroll_action_id
      AND paas.assignment_id=pee.assignment_id
      AND ppa.date_earned>( select min(ppa1.date_earned) -- query is now based on date earned (5070091)
                                from pay_payroll_actions ppa1,
						pay_assignment_actions paas1,
						pay_run_results prr1,
                                    pay_element_entries_f pee1
				where ppa1.date_earned>=p_vehicle_alloc_end_date -- query is now based on date earned (5070091)
				  AND pee1.element_entry_id=p_element_entry_id
				  AND prr1.element_entry_id = pee1.element_entry_id
				  AND paas1.assignment_action_id=prr1.assignment_action_id
                          AND paas1.payroll_action_id=ppa1.payroll_action_id
                          AND prr1.source_id= pee1.element_entry_id) -- Added for bug 4771780
      -- query is now based on date earned (5070091)
      AND ppa.date_earned<=p_curr_period_end_date
      AND ppa.date_earned between pee.effective_start_date and pee.effective_end_date
      AND ppa.effective_date between pet.effective_start_date and pet.effective_end_date
      AND ppa.effective_date between piv.effective_start_date and piv.effective_end_date;

Line: 555


        SELECT
          fiscal_ratings
        FROM
          pqp_vehicle_repository_f pvr,
          pqp_vehicle_allocations_f pva
        WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
          AND pva.vehicle_allocation_id = c_allocation_id
       --   AND to_char(pvr.EFFECTIVE_END_DATE,'DDMMYYYY')='31124712';

Line: 564


       (SELECT
          MAX(pvr.EFFECTIVE_END_DATE)
        FROM
          pqp_vehicle_repository_f pvr,
          pqp_vehicle_allocations_f pva
        WHERE pvr.vehicle_repository_id = pva.vehicle_repository_id
          AND pva.vehicle_allocation_id = c_allocation_id);