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

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

Line: 17


select distinct
 ppa.payroll_action_id
,paa.assignment_id
,paa.assignment_action_id
,paf.location_id
from pay_payroll_actions ppa
,pay_assignment_actions paa
,per_assignments_f paf
,per_people_f ppf
,hr_lookups hl
where ppa.action_type = 'X'
and  ppa.action_status = 'C'
and  ppa.report_type = hl.meaning
and  hl.lookup_type = 'PAYSLIP_REPORT_TYPES'
and  hl.lookup_code = 'US'
and  ppa.payroll_action_id = paa.payroll_action_id
and  paa.assignment_id = paf.assignment_id
and  paf.person_id = ppf.person_id
and  ppf.person_id = l_person_id
and  ppa.effective_date = (select max(ppa1.effective_date)
from pay_payroll_actions ppa1
,pay_assignment_actions paa1
,hr_lookups hl1
where ppa1.effective_date <= l_eff_date
and ppa1.action_type = 'X'
and ppa1.action_status = 'C'
and ppa1.report_type = hl1.meaning
and hl1.lookup_type = 'PAYSLIP_REPORT_TYPES'
and hl1.lookup_code = 'US'
and ppa1.payroll_action_id = paa1.payroll_action_id
and paa1.assignment_id = paa.assignment_id
and ppa1.business_group_id = ppa.business_group_id)
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date;

Line: 54


select region_2,location_code,
(select state_name from pay_us_states where state_abbrev = region_2)
from hr_locations_all
where location_id = p_location_id;

Line: 62


select to_char(org_information9) from
hr_organization_information where organization_id = p_bus_grp_id
and org_information_context = 'Business Group Information';

Line: 69


select prt.run_type_name
  from pay_assignment_actions paa_xfr
      ,pay_assignment_actions paa_prepay
      ,pay_payroll_actions ppa_prepay
      ,pay_run_types_f prt
 where paa_xfr.assignment_action_id = p_assignment_action_id
   and (INSTR(paa_xfr.serial_number, 'PY') <> 0
       or INSTR(paa_xfr.serial_number, 'UY') <> 0)
   and paa_xfr.source_action_id is not null
   and fnd_number.canonical_to_number(SUBSTR(paa_xfr.serial_number, 3)) = paa_prepay.assignment_action_id
   and paa_prepay.payroll_action_id = ppa_prepay.payroll_action_id
   and ppa_prepay.action_type in ('R', 'Q')
   and paa_prepay.run_type_id = prt.run_type_id
   and prt.legislation_code = 'US';

Line: 88


SELECT
organization_name
,job
,to_char(payment_date,'YYYY-MM-DD')
,Period_type
,location_name
,employee_address1 || employee_address2 || employee_address3 || ' ' || employee_city || ' ' || employee_state || ' ' || employee_zip_code
,payroll_name
,'USD'
,to_char(ending_date,'YYYY-MM-DD')
,'Federal'
,(select status from pay_us_emp_w4dtl_action_info_v
where action_context_id = p_asg_action_id
and   tax_jurisdiction = 'Federal'
 and   trunc(effective_date) <= l_eff_date) STATUS

,(select exemptions from pay_us_emp_w4dtl_action_info_v
 where action_context_id = p_asg_action_id
 and   assignment_id = p_asg_id
 and   trunc(effective_date) <= l_eff_date
and   tax_jurisdiction = 'Federal')  EXEMPTIONS

,(select additional_tax_amount from pay_us_emp_w4dtl_action_info_v
 where action_context_id = p_asg_action_id
 and   assignment_id = p_asg_id
 and  trunc(effective_date) <= l_eff_date
 and   tax_jurisdiction = 'Federal')  ADDNL_TAX_AMOUNT

,(select override_tax_amount from pay_us_emp_w4dtl_action_info_v
 where action_context_id = p_asg_action_id
 and   assignment_id = p_asg_id
 and   trunc(effective_date) <= l_eff_date
 and   tax_jurisdiction = 'Federal')  OVERRIDE_TAX_AMOUNT

 ,(select override_tax_percentage from pay_us_emp_w4dtl_action_info_v
 where action_context_id = p_asg_action_id
 and   assignment_id = p_asg_id
 and   trunc(effective_date) <= l_eff_date
 and   tax_jurisdiction = 'Federal')  OVERRIDE_TAX_PERCENTAGE
 ,p_state_code

 ,(select exemptions from pay_us_emp_w4dtl_action_info_v
 where action_context_id = p_asg_action_id
 and   assignment_id = p_asg_id
 and   trunc(effective_date) <= l_eff_date
 and   tax_jurisdiction = p_state_desc)  STEXEMPTIONS

,(select additional_tax_amount from pay_us_emp_w4dtl_action_info_v
 where action_context_id = p_asg_action_id
 and   assignment_id = p_asg_id
 and   trunc(effective_date) <= l_eff_date
 and   tax_jurisdiction = p_state_desc)  STADDNL_TAX_AMOUNT

,(select override_tax_amount from pay_us_emp_w4dtl_action_info_v
 where action_context_id = p_asg_action_id
 and   assignment_id = p_asg_id
 and   trunc(effective_date) <= l_eff_date
 and   tax_jurisdiction = p_state_desc)  STOVERRIDE_TAX_AMOUNT

 ,(select override_tax_percentage from pay_us_emp_w4dtl_action_info_v
 where action_context_id = p_asg_action_id
 and   assignment_id = p_asg_id
 and   trunc(effective_date) <= l_eff_date
 and   tax_jurisdiction = p_state_desc)  STOVERRIDE_TAX_PERCENTAGE

,(select gross_earnings
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY CURRENT')  TOTAL_EARNINGS_CV

 ,(select (nvl(gross_earnings, 0) - nvl(pretax_deductions, 0))
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY CURRENT')  TAXABLE_GROSS_CV

 ,(select taxes
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY CURRENT')  TOTAL_TAXES_CV

 ,(select (nvl(pretax_deductions, 0) + nvl(after_tax_deductions, 0))
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY CURRENT')  TOTAL_DEDUCTIONS_CV

 ,(select net_pay
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY CURRENT')  NET_PAY_CV

 ,(select gross_earnings
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY YTD')  TOTAL_EARNINGS_YTD

 ,(select (nvl(gross_earnings, 0) - nvl(pretax_deductions, 0))
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY YTD')  TAXABLE_GROSS_YTD

 ,(select taxes
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY YTD')  TOTAL_TAXES_YTD

 ,(select (nvl(pretax_deductions, 0) + nvl(after_tax_deductions, 0))
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY YTD')  TOTAL_DEDUCTIONS_YTD

 ,(select net_pay
 from pay_ac_emp_sum_action_info_v
 where action_context_id = p_asg_action_id
 and   action_information_category = 'AC SUMMARY YTD')  NET_PAY_YTD

from pay_employee_action_info_v peai
where action_context_id = p_asg_action_id
and   assignment_id = p_asg_id
and   trunc(effective_date) <= l_eff_date
and   location_name = p_loc_name;