[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT get_parameter(legislative_parameters, 'PAYROLL_ID')
,fnd_date.canonical_to_date(get_parameter(legislative_parameters, 'DATE'))
,start_date
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = c_payroll_action_id;
SELECT hoi.organization_id organization_id
,addr.postal_code c_postal_code
,addr.town_or_city c_town
,nvl(hr_general.decode_lookup('HU_COUNTY', addr.region_2),addr.region_2) c_county
,addr.address_line_1 c_location_name
,nvl(hr_general.decode_lookup('HU_LOCATION_TYPES', addr.address_line_2),addr.address_line_2) c_location_type
,addr.address_line_3 c_street_number
,addr.loc_information14 c_building
,addr.loc_information15 c_stairway
,addr.loc_information16 c_floor
,addr.loc_information17 c_door
FROM hr_organization_information hoi
,hr_all_organization_units hou
,hr_locations_all addr
,hr_organization_information hoi1
WHERE hou.organization_id = c_business_group_id
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'HU_COMPANY_INFORMATION_DETAILS'
AND hoi1.organization_id = hou.organization_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'HU_COMPANY_INFORMATION'
AND hoi1.org_information2 = 'Y'
AND hou.location_id = addr.location_id (+)
ORDER BY hoi.organization_id ;
sqlstr := 'select distinct person_id '||
'from per_people_f ppf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :payroll_action_id '||
'and ppa.business_group_id = ppf.business_group_id '||
'order by ppf.person_id';
sqlstr := 'select 1 '||
'/* ERROR - Employer Details Fetch failed with: '||
sqlerrm(sqlcode)||' */ '||
'from dual where to_char(:payroll_action_id) = dummy';
SELECT assignment_id
FROM per_all_assignments_f asl
,per_periods_of_service ppos
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = c_pact_id
AND asl.person_id BETWEEN c_stperson AND c_endperson
AND asl.primary_flag = 'Y'
AND ppos.period_of_service_id = asl.period_of_service_id
AND ppos.actual_termination_date BETWEEN asl.effective_end_date
AND asl.effective_end_date
AND asl.business_group_id = ppa.business_group_id
AND nvl(asl.payroll_id,0) = nvl(c_payroll_id,nvl(asl.payroll_id,0))
AND ppos.actual_termination_date BETWEEN ppa.start_date
AND ppa.effective_date
AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
FROM pay_payroll_actions appa
,pay_assignment_actions act -- Bug Fix 4369797 Changed table order
,pay_action_information pai
WHERE appa.action_status = 'C'
AND appa.report_type = 'HU_EMP_CERT'
AND appa.report_category = 'ARCHIVE'
AND appa.report_qualifier = 'HU' -- Bug Fix 4369797
AND appa.action_type = 'X' -- Added
AND act.action_status = 'C' -- some
AND pai.action_context_type = 'AAP' -- new conditions
AND pai.action_information_category = 'HU_EMP_CERTIFICATION'
AND act.assignment_id = asl.assignment_id
AND act.payroll_action_id = appa.payroll_action_id
AND pai.action_context_id = act.assignment_action_id
)
ORDER BY assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT addr.postal_code
,addr.town_or_city town
,addr.address_line1 location_name
,nvl(hr_general.decode_lookup('HU_LOCATION_TYPES', addr.address_line2),addr.address_line2) location_type
,addr.address_line3 street_number
,addr.add_information14 building
,addr.add_information15 stairway
,addr.add_information16 floor
,addr.add_information17 door
FROM per_addresses addr
WHERE addr.person_id = p_person_id
AND addr.primary_flag = 'Y'
AND p_termination_date between addr.date_from and
nvl(addr.date_to,fnd_date.canonical_to_date('4712/12/31'));
SELECT pap.person_id
,pap.full_name
,pap.previous_last_name Maiden_name
,pap.national_identifier social_security_code
,pap.town_of_birth place_of_birth
,fnd_date.date_to_chardate(pap.date_of_birth) date_of_birth
,pap.per_information1 mother_maiden_name
,fnd_date.date_to_chardate(ppf.date_start) hire_date
,fnd_date.date_to_chardate(ppf.actual_termination_date) termination_date
,ppf.actual_termination_date actual_termination_date
,paa.assignment_id
,decode(hr.segment2,'Y','Yes','No') railway_benefit
,hoi.organization_id organization_id
,hoi.org_information1 company_name
,paas.payroll_action_id
FROM per_all_people_F pap
,per_periods_of_service ppf
,per_all_assignments_f paa
,pay_assignment_actions paas
,hr_soft_coding_keyflex hr
,hr_organization_information hoi
WHERE paas.assignment_action_id = p_assactid
AND paas.assignment_id = paa.assignment_id
AND pap.person_id = ppf.person_id
AND ppf.business_group_id = paa.business_group_id
AND pap.person_id = paa.person_id
AND paa.period_of_service_id = ppf.period_of_service_id
AND hoi.organization_id = paa.business_group_id
AND hoi.org_information_context = 'HU_COMPANY_INFORMATION_DETAILS'
AND paa.soft_coding_keyflex_id = hr.soft_coding_keyflex_id (+)
AND ppf.actual_termination_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND ppf.actual_termination_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT eev.screen_entry_value start_date
,pee.element_entry_id ppf_element_entry_id
,pei.eei_information1 pf_scheme_name
,hou.name provider_name
,hou.organization_id provider_code
,decode(addr.town_or_city,NULL,NULL,addr.town_or_city||',')
||decode(addr.postal_code,NULL,NULL,' '||addr.postal_code)
||decode(addr.address_line_1, NULL, NULL, ' '||addr.address_line_1)
||decode(addr.address_line_2, NULL, NULL
, ' '||nvl(hr_general.decode_lookup('HU_LOCATION_TYPES', addr.address_line_2),addr.address_line_2))
||decode(addr.address_line_3, NULL, NULL, ' '||addr.address_line_3||'. ')
||decode(addr.loc_information14, NULL, NULL, addr.loc_information14||'.')
||decode(addr.loc_information15, NULL, NULL, addr.loc_information15||'.')
||decode(addr.loc_information16, NULL, NULL, addr.loc_information16||'.')
||decode(addr.loc_information17, NULL, NULL, addr.loc_information17||'.') address
,pee.personal_payment_method_id payment_method_id
FROM pay_element_entries_f pee
,pay_element_entry_values_f eev
,pay_input_values_f piv
,pay_element_types_f pet
,pay_element_type_extra_info pei
,hr_organization_units hou
,hr_locations_all addr
WHERE pee.element_entry_id = eev.element_entry_id
AND eev.input_value_id + 0 = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pee.assignment_id = c_assignment_id
AND piv.name = 'Override Start Date'
AND pet.element_type_id = pei.element_type_id
AND pei.eei_information_category = 'HU_PENSION_SCHEME_INFO'
AND pei.eei_information4 = c_type
AND pei.eei_information2 = hou.organization_id
AND hou.location_id = addr.location_id (+)
AND c_effective_date BETWEEN eev.effective_start_date
AND eev.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date;
SELECT pea.segment2 bank_ac_no
FROM pay_personal_payment_methods_f ppp
,pay_external_accounts pea
WHERE ppp.personal_payment_method_id = c_payment_method_id
AND ppp.external_account_id = pea.external_account_id
AND c_effective_date BETWEEN ppp.effective_start_date
AND ppp.effective_end_date;
SELECT min(pee.effective_start_date)
FROM pay_element_entries_f pee
WHERE pee.element_entry_id = p_element_entry_id;
SELECT SUM(paat.absence_days)
FROM per_absence_attendance_types pat
,per_absence_attendances paat
WHERE pat.absence_attendance_type_id = paat.absence_attendance_type_id
AND pat.absence_category = 'S'
AND paat.person_id = c_person_id
AND to_char(paat.date_end,'YYYY') = c_termination_year;
SELECT sum(decode(sign(c_termination_date - paat.date_end),-1, c_termination_date, paat.date_end)
- decode(to_char(paat.date_start,'yyyy'),c_termination_year
,paat.date_start, to_date('01-01-'||c_termination_year,'dd-mm-YYYY'))
+ 1
) sickness_leave_taken
FROM per_absence_attendance_types pat
,per_absence_attendances paat
WHERE pat.ABSENCE_ATTENDANCE_TYPE_ID = paat.ABSENCE_ATTENDANCE_TYPE_ID
AND pat.ABSENCE_CATEGORY = 'S'
AND paat.person_id = c_person_id
AND to_char(paat.date_end,'YYYY') = c_termination_year;
SELECT pem_information1
FROM per_previous_employers
WHERE business_group_id = c_business_group_id
AND person_id = c_person_id
AND to_char(end_date,'YYYY') = c_termination_year
ORDER BY end_date DESC;
SELECT hrl.lookup_code
FROM hr_lookups hrl
WHERE hrl.lookup_type = 'HU_PENSION_PROVIDERS'
AND hrl.meaning = c_meaning
AND hrl.enabled_flag = 'Y' ;