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

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

Line: 12


   SELECT ptpt.number_per_fiscal_year
   FROM   per_all_assignments_f paaf, pay_all_payrolls_f pap, per_time_period_types ptpt, fnd_sessions fs
   WHERE  fs.session_id = userenv('sessionid')
   AND    paaf.assignment_id = p_assignment_id
   AND    fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_Date
   AND    pap.payroll_id = paaf.payroll_id
   AND    fs.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_Date
   AND    pap.period_type = ptpt.period_type;

Line: 39


   SELECT nvl(min(peev.screen_entry_value), 'NP')
   FROM   fnd_sessions fs,
          pay_element_types_f pet,
          pay_input_values_f piv,
          pay_element_entries_f peef,
          pay_element_entry_values_f peev
   WHERE  fs.session_id = userenv('sessionid')
   AND    pet.element_name = 'NI'
   AND    pet.business_group_id IS NULL
   AND    pet.legislation_code = 'GB'
   AND    fs.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
   AND    pet.element_type_id = piv.element_type_id
   AND    piv.name = 'Process Type'
   AND    piv.business_group_id IS NULL
   AND    piv.legislation_code = 'GB'
   AND    fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
   AND    peef.assignment_id = p_assignment_id
   AND    fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
   AND    peef.element_entry_id = peev.element_entry_id
   AND    peev.input_value_id = piv.input_value_id
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date;

Line: 76


   SELECT scl.segment1
   FROM   hr_soft_coding_keyflex scl,
          fnd_sessions fs,
          pay_payrolls_f ppf,
          per_all_assignments_f paaf
   WHERE  paaf.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
   AND    ppf.payroll_id = paaf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;

Line: 91


   SELECT piv.input_value_id
   FROM   fnd_sessions fs,
          pay_element_types_f pet,
          pay_input_values_f piv
   WHERE  fs.session_id = userenv('sessionid')
   AND    pet.element_name = p_ele_name
   AND    pet.business_group_id IS NULL
   AND    pet.legislation_code = 'GB'
   AND    fs.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
   AND    pet.element_type_id = piv.element_type_id
   AND    piv.name = 'Main Entry'
   AND    piv.business_group_id IS NULL
   AND    piv.legislation_code = 'GB'
   AND    fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;

Line: 107


   SELECT count(*) cnt
   FROM   fnd_sessions fs,
          per_all_assignments_f paaf1,
          per_all_assignments_f paaf2,
          pay_all_payrolls_f ppf,
          hr_soft_coding_keyflex scl,
          pay_element_entries_f peef,
          pay_element_entry_values_f peev
   WHERE  paaf1.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
   AND    paaf1.person_id = paaf2.person_id
   AND    fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
   AND    paaf2.payroll_id = ppf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
   AND    scl.segment1 = p_asg_tax_ref
   AND    paaf2.assignment_id = peef.assignment_id
   AND    fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
   AND    peef.element_entry_id = peev.element_entry_id
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
   AND    peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
   AND    nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y';

Line: 134


   SELECT 1 cnt
   FROM fnd_sessions fs,
          per_all_assignments_f paaf1,
          per_all_assignments_f paaf2,
          pay_all_payrolls_f ppf,
          hr_soft_coding_keyflex scl,
          pay_element_entries_f peef,
          pay_element_entry_values_f peev
   WHERE  paaf1.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
   AND    paaf1.person_id = paaf2.person_id
   AND    nvl(paaf2.primary_flag, 'N') = 'Y'
   AND    fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
   AND    paaf2.payroll_id = ppf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
   AND    scl.segment1 = p_asg_tax_ref
   AND    paaf2.assignment_id = peef.assignment_id
   AND    fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
   AND    peef.element_entry_id = peev.element_entry_id
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
   AND    peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id);

Line: 201


   SELECT scl.segment1, ppf.payroll_id
   FROM   hr_soft_coding_keyflex scl,
          fnd_sessions fs,
          pay_payrolls_f ppf,
          per_all_assignments_f paaf
   WHERE  paaf.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
   AND    ppf.payroll_id = paaf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;

Line: 218


   SELECT ptp.start_date
   FROM   per_time_periods ptp, fnd_sessions fs
   WHERE  fs.session_id = userenv('sessionid')
   AND    ptp.payroll_id = l_asg_payroll_id
   AND    fs.effective_date = ptp.regular_payment_date;

Line: 225


   SELECT ppf.payroll_id
   FROM   fnd_sessions fs,
          per_all_assignments_f paaf1,
          per_all_assignments_f paaf2,
          pay_all_payrolls_f ppf,
          hr_soft_coding_keyflex scl,
          pay_element_entries_f peef,
          pay_element_entry_values_f peev
   WHERE  paaf1.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
   AND    paaf1.person_id = paaf2.person_id
   AND    fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
   AND    paaf2.payroll_id = ppf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
   AND    scl.segment1 = l_asg_tax_ref
   AND    paaf2.assignment_id = peef.assignment_id
   AND    fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
   AND    peef.element_entry_id = peev.element_entry_id
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
   AND    peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
   AND    nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y';

Line: 250


   SELECT ppf.payroll_id
   FROM fnd_sessions fs,
          per_all_assignments_f paaf1,
          per_all_assignments_f paaf2,
          pay_all_payrolls_f ppf,
          hr_soft_coding_keyflex scl,
          pay_element_entries_f peef,
          pay_element_entry_values_f peev
   WHERE  paaf1.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
   AND    paaf1.person_id = paaf2.person_id
   AND    nvl(paaf2.primary_flag, 'N') = 'Y'
   AND    fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
   AND    paaf2.payroll_id = ppf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
   AND    scl.segment1 = l_asg_tax_ref
   AND    paaf2.assignment_id = peef.assignment_id
   AND    fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
   AND    peef.element_entry_id = peev.element_entry_id
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
   AND    peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id);

Line: 279


   SELECT nvl(regular_payment_date, to_date('01-01-0001', 'DD-MM-YYYY'))
   FROM   per_time_periods ptp
   WHERE  l_asg_period_start_date BETWEEN ptp.start_date AND ptp.end_Date
   AND    ptp.payroll_id = l_payroll_id;

Line: 355


   SELECT scl.segment1
   FROM   hr_soft_coding_keyflex scl,
          fnd_sessions fs,
          pay_payrolls_f ppf,
          per_all_assignments_f paaf
   WHERE  paaf.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
   AND    ppf.payroll_id = paaf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;

Line: 370


   SELECT ppf.payroll_id
   FROM   fnd_sessions fs,
          per_all_assignments_f paaf1,
          per_all_assignments_f paaf2,
          pay_all_payrolls_f ppf,
          hr_soft_coding_keyflex scl,
          pay_element_entries_f peef,
          pay_element_entry_values_f peev
   WHERE  paaf1.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
   AND    paaf1.person_id = paaf2.person_id
   AND    fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
   AND    paaf2.payroll_id = ppf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
   AND    scl.segment1 = l_asg_tax_ref
   AND    paaf2.assignment_id = peef.assignment_id
   AND    fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
   AND    peef.element_entry_id = peev.element_entry_id
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
   AND    peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
   AND    nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y';

Line: 395


   SELECT ppf.payroll_id
   FROM fnd_sessions fs,
          per_all_assignments_f paaf1,
          per_all_assignments_f paaf2,
          pay_all_payrolls_f ppf,
          hr_soft_coding_keyflex scl,
          pay_element_entries_f peef,
          pay_element_entry_values_f peev
   WHERE  paaf1.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
   AND    paaf1.person_id = paaf2.person_id
   AND    nvl(paaf2.primary_flag, 'N') = 'Y'
   AND    fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
   AND    paaf2.payroll_id = ppf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
   AND    scl.segment1 = l_asg_tax_ref
   AND    paaf2.assignment_id = peef.assignment_id
   AND    fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
   AND    peef.element_entry_id = peev.element_entry_id
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
   AND    peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id);

Line: 424


   SELECT number_per_fiscal_year
   FROM   per_time_periods ptp, per_time_period_types ptpt, fnd_sessions fs
   WHERE  fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN ptp.start_date AND ptp.end_Date
   AND    ptp.payroll_id = l_payroll_id
   AND    ptp.period_type = ptpt.period_type;

Line: 500


   SELECT scl.segment1
   FROM   hr_soft_coding_keyflex scl,
          fnd_sessions fs,
          pay_payrolls_f ppf,
          per_all_assignments_f paaf
   WHERE  paaf.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
   AND    ppf.payroll_id = paaf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_Date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id;

Line: 515


   SELECT piv.input_value_id
   FROM   fnd_sessions fs,
          pay_element_types_f pet,
          pay_input_values_f piv
   WHERE  fs.session_id = userenv('sessionid')
   AND    pet.element_name = p_ele_name
   AND    pet.business_group_id IS NULL
   AND    pet.legislation_code = 'GB'
   AND    fs.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
   AND    pet.element_type_id = piv.element_type_id
   AND    piv.name = p_iv_name
   AND    piv.business_group_id IS NULL
   AND    piv.legislation_code = 'GB'
   AND    fs.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;

Line: 534


   SELECT peef.element_entry_id
   FROM   fnd_sessions fs,
          per_all_assignments_f paaf1,
          per_all_assignments_f paaf2,
          pay_all_payrolls_f ppf,
          hr_soft_coding_keyflex scl,
          pay_element_entries_f peef,
          pay_element_entry_values_f peev
   WHERE  paaf1.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
   AND    paaf1.person_id = paaf2.person_id
   AND    fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
   AND    paaf2.payroll_id = ppf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
   AND    scl.segment1 = l_asg_tax_ref
   AND    paaf2.assignment_id = peef.assignment_id
   AND    fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
   AND    peef.element_entry_id = peev.element_entry_id
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
   AND    peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
   AND    nvl(SCREEN_ENTRY_VALUE, 'N') = 'Y'
   AND    peef.target_entry_id IS NULL;

Line: 560


   SELECT peef.element_entry_id
   FROM fnd_sessions fs,
          per_all_assignments_f paaf1,
          per_all_assignments_f paaf2,
          pay_all_payrolls_f ppf,
          hr_soft_coding_keyflex scl,
          pay_element_entries_f peef,
          pay_element_entry_values_f peev
   WHERE  paaf1.assignment_id = p_assignment_id
   AND    fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
   AND    paaf1.person_id = paaf2.person_id
   AND    nvl(paaf2.primary_flag, 'N') = 'Y'
   AND    fs.effective_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
   AND    paaf2.payroll_id = ppf.payroll_id
   AND    fs.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND    ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
   AND    scl.segment1 = l_asg_tax_ref
   AND    paaf2.assignment_id = peef.assignment_id
   AND    fs.effective_date BETWEEN peef.effective_start_date AND peef.effective_end_date
   AND    peef.element_entry_id = peev.element_entry_id
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
   AND    peev.input_value_id IN (g_eas_main_iv_id, g_eas_ntpp_main_iv_id)
   AND    peef.target_entry_id IS NULL;

Line: 588


   SELECT peev.screen_entry_value
   FROM   fnd_sessions fs, pay_element_entry_values_f peev
   WHERE  fs.session_id = userenv('sessionid')
   AND    fs.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
   AND    peev.element_entry_id = l_entry_id
   AND    peev.input_value_id IN (l_eas_iv_id, l_eas_ntpp_iv_id);