[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
TO_NUMBER ( GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER') ) Legal
,fnd_date.canonical_to_date(get_parameter(legislative_parameters,'START_DATE')) start_date
,fnd_date.canonical_to_date(get_parameter(legislative_parameters,'END_DATE')) end_date
,GET_PARAMETER(legislative_parameters,'ARCHIVE') ARCHIVE_OR_NOT
,business_group_id BG_ID
,effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT business_group_id
,legislative_parameters
,get_parameter(legislative_parameters, 'LEGAL_EMPLOYER_ID') legal_employer_id
,fnd_date.canonical_to_date(get_parameter(legislative_parameters, 'DATE')) report_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT org.organization_id legal_employer_id
,org.name
,hoi1.org_information2
,org.location_id
FROM hr_all_organization_units org
,hr_organization_information hoi1
WHERE org.organization_id = p_legal_employer_id
AND hoi1.organization_id (+) = org.organization_id
AND hoi1.org_information_context (+) = 'SE_LEGAL_EMPLOYER_DETAILS';
SELECT rpad(Address_line_1 || ', ' || Address_line_2 ||', ' ||Address_line_3,30,' ') address,
hr_general.DECODE_FND_COMM_LOOKUP('SE_POSTAL_CODE',postal_code) postal_code,
hr_general.decode_territory (country) country
FROM hr_locations
WHERE location_id = p_location_id;
SELECT substr(hoi.org_information3,1,25) contact_person
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_legal_employer_id
AND hoi.org_information_context = 'SE_ORG_CONTACT_DETAILS';
p_sql := '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';
SELECT u.creator_id
FROM ff_user_entities u,
ff_database_items d
WHERE d.user_name = p_user_name
AND u.user_entity_id = d.user_entity_id
AND (u.legislation_code = 'NO' )
AND (u.business_group_id IS NULL )
AND u.creator_type = 'B';
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name=p_element_name
AND legislation_code='SE'
AND business_group_id IS NULL
AND p_report_end_date BETWEEN effective_start_date
AND effective_end_date;
SELECT input_value_id
FROm pay_input_values_f
WHERE element_type_id = p_element_type_id
AND p_report_end_date between effective_start_date
AND effective_end_date
AND name = p_input_value
AND legislation_code='SE'
AND business_group_id IS NULL;
SELECT hoi_le.org_information1 local_unit_id,
hou_lu.NAME local_unit_name,
hoi_lu.org_information1,
hou_lu.location_id
FROM hr_all_organization_units hou_le,
hr_organization_information hoi_le,
hr_all_organization_units hou_lu,
hr_organization_information hoi_lu
WHERE hoi_le.organization_id = hou_le.organization_id
AND hou_le.organization_id = p_legal_employer_id
AND hoi_le.org_information_context = 'SE_LOCAL_UNITS'
AND hou_lu.organization_id = hoi_le.org_information1
AND hou_lu.organization_id = hoi_lu.organization_id
AND hoi_lu.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
SELECT pap.person_id,
pap.full_name,
'19' || substr(pap.national_identifier,0,6) || substr(pap.national_identifier,8) national_identifier,
paa.assignment_number employee_number,
paa.assignment_id
FROM per_all_assignments_f paa,
per_all_people_f pap,
HR_SOFT_CODING_KEYFLEX hsc,
per_person_types ppt
WHERE pap.person_id BETWEEN p_start_person AND p_end_person
AND pap.effective_start_date <= p_report_end_date
AND pap.effective_end_date >= p_report_start_date
AND ppt.system_person_type like 'EMP%'
AND ppt.person_type_id= pap.person_type_id
AND pap.person_id = paa.person_id
AND paa.effective_start_date <= p_report_end_date
AND paa.effective_end_date >= p_report_start_date
AND hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND hsc.segment2 = to_char(p_local_unit_id)
ORDER by assignment_id;
SELECT pee.element_entry_id,
NVL(fnd_date.canonical_to_date(peev1.screen_entry_value), pee.effective_start_date) start_date,
NVL(fnd_date.canonical_to_date(peev2.screen_entry_value), pee.effective_end_date) end_date
FROM pay_element_entries_f pee ,
pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2
WHERE
element_type_id= p_element_type_id
AND assignment_id= p_assignment_id
AND p_report_start_date < nvl(pee.effective_end_date, p_report_start_date)
AND p_report_end_date > pee.effective_start_date
AND pee.element_entry_id = peev1.element_entry_id
AND p_report_start_date < nvl(peev1.effective_end_date, p_report_start_date)
AND p_report_end_date > peev1.effective_start_date
AND peev1.input_value_id= p_start_date_iv
AND pee.element_entry_id = peev2.element_entry_id
AND p_report_start_date < nvl(peev2.effective_end_date, p_report_start_date)
AND p_report_end_date > peev2.effective_start_date
AND peev2.input_value_id=p_end_date_iv; */
SELECT prr.source_id,
fnd_date.canonical_to_date(prrv1.result_value) start_date,
prrv2.result_value full_day,
fnd_date.canonical_to_date(prrv3.result_value) end_date,
prrv4.result_value part_day
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv1,
pay_run_result_values prrv2,
pay_run_result_values prrv3,
pay_run_result_values prrv4
WHERE ppa.effective_date BETWEEN p_report_start_date
AND p_report_end_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = p_element_type_id
AND prr.run_result_id = prrv1.run_result_id
AND prrv1.input_value_id = p_start_date_iv
AND prr.run_result_id = prrv2.run_result_id
AND prrv2.input_value_id = p_full_day_iv
AND prr.run_result_id = prrv3.run_result_id
AND prrv3.input_value_id = p_end_date_iv
AND prr.run_result_id = prrv4.run_result_id
AND prrv4.input_value_id = p_part_day_iv
ORDER BY prr.run_result_id;
SELECT max(to_number(prrv1.result_value)) full_day
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv1
WHERE ppa.effective_date BETWEEN p_report_start_date
AND p_report_end_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = p_element_type_id
AND prr.run_result_id = prrv1.run_result_id
AND prrv1.input_value_id = p_full_day_iv
AND prr.source_id = p_source_id;
SELECT fnd_date.canonical_to_date(prrv1.result_value) start_date,
fnd_date.canonical_to_date(prrv2.result_value) fourteenth_date,
fnd_date.canonical_to_date(prrv3.result_value) end_date,
prrv4.result_value employer_days
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv1,
pay_run_result_values prrv2,
pay_run_result_values prrv3,
pay_run_result_values prrv4
WHERE ppa.effective_date BETWEEN p_report_start_date
AND p_report_end_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = p_element_type_id
AND prr.run_result_id = prrv1.run_result_id
AND prrv1.input_value_id = p_start_date_iv
AND prr.run_result_id = prrv2.run_result_id
AND prrv2.input_value_id = p_14th_date_iv
AND prr.run_result_id = prrv3.run_result_id
AND prrv3.input_value_id = p_end_date_iv
AND prr.run_result_id = prrv4.run_result_id
AND prrv4.input_value_id = p_emp_days_iv;
SELECT assignment_extra_info_id,
fnd_date.canonical_to_date(AEI_INFORMATION1) start_date,
fnd_date.canonical_to_date(AEI_INFORMATION2) end_date,
AEI_INFORMATION3 full_day,
AEI_INFORMATION4 part_day
FROM PER_ASSIGNMENT_EXTRA_INFO
WHERE assignment_id=p_assignment_id
AND aei_information_category ='SE_SICKNESS_CORRECTION_DATA'
AND aei_information5='Y';
SELECT pay_assignment_actions_s.nextval INTO l_assact_id FROM dual;
SELECT action_information8,
action_information10
INTO l_check_end_date,
l_check_days
FROM pay_action_information
WHERE action_context_id = l_assact_id
AND action_information13 = l_start_end_date.source_id;
UPDATE pay_action_information
set action_information8 = fnd_date.date_to_canonical(greatest(l_sickness_end_date,fnd_date.canonical_to_date(l_check_end_date))),
action_information10 = least(l_total_sickness_days_rec.full_day,l_employer_days)
WHERE action_context_id = l_assact_id
AND action_information13 = l_start_end_date.source_id;
update PER_ASSIGNMENT_EXTRA_INFO
set aei_information5='N'
where assignment_extra_info_id = l_csr_correction_data.assignment_extra_info_id;
SELECT
action_information3 legal_employer_id,
action_information4 name,
action_information5 org_num,
action_information6 address,
action_information7 postal_code,
action_information8 country,
action_information9 contact_person,
effective_date
FROM pay_action_information pai
WHERE pai.action_context_id = p_payroll_action_id
AND pai.action_context_type='PA'
AND pai.action_information_category='EMEA REPORT INFORMATION'
AND pai.action_information1='PYSESTOA'
AND pai.action_information2='LE';
SELECT
pai.action_information4 national_identifier,
pai.action_information5 full_name,
fnd_date.canonical_to_date(pai.action_information7) start_date,
fnd_date.canonical_to_date(pai.action_information8) end_date,
pai.action_information10 full_day,
pai.action_information11 part_day,
pai.action_information12 correction
FROM
pay_payroll_actions paa,
pay_assignment_actions assg,
pay_action_information pai
WHERE
paa.payroll_action_id = p_payroll_action_id
AND assg.payroll_action_id = paa.payroll_action_id
AND pai.action_context_id= assg.assignment_action_id
AND pai.action_context_type='AAP'
AND pai.action_information_category='EMEA REPORT INFORMATION'
AND pai.action_information1='PYSESTOA'
AND pai.action_information2='ASG'
AND pai.action_information3=p_legal_employer_id;
SELECT payroll_action_id
INTO l_payroll_action_id
FROM pay_payroll_actions ppa,
fnd_conc_req_summary_v fcrs,
fnd_conc_req_summary_v fcrs1
WHERE fcrs.request_id = fnd_global.conc_request_id
AND fcrs.priority_request_id = fcrs1.priority_request_id
AND ppa.request_id between fcrs1.request_id and fcrs.request_id
AND ppa.request_id = fcrs1.request_id;