[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
get_parameter(legislative_parameters,'NAME_SP') name_sp,
TO_NUMBER ( GET_PARAMETER(legislative_parameters,'LE_ID') ) Legal
,get_parameter(legislative_parameters,'VERSION') VERSION
,GET_PARAMETER(legislative_parameters,'ARCHIVE') ARCHIVE_OR_NOT
,fnd_date.canonical_to_date(GET_PARAMETER(legislative_parameters,'DATE'))
,business_group_id BG_ID 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
,org.location_id
,hoi1.org_information1
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 (+) = 'NO_LEGAL_EMPLOYER_DETAILS';
SELECT hoi_le.org_information1 local_unit_id,
hou_lu.NAME local_unit_name,
hoi_lu.org_information1,
hoi_lu.org_information3,
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 = csr_v_legal_employer_id
AND hoi_le.org_information_context = 'NO_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 = 'NO_LOCAL_UNIT_DETAILS';
SELECT org.organization_id
,org.name
,org.location_id
,hoi1.org_information1 sp_org_number
FROM hr_all_organization_units org
,hr_organization_information hoi1
WHERE org.organization_id = p_sp_org_id
AND hoi1.organization_id (+) = org.organization_id
AND hoi1.org_information_context (+) = 'NO_STATEMENT_PROVIDER_DETAILS';
SELECT rpad(Address_line_1 || ', ' || Address_line_2 ||', ' ||Address_line_3,30,' ') address, postal_code
FROM hr_locations
WHERE location_id = p_location_id;
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 pap.person_id,
pap.full_name,
substr(pap.national_identifier,0,6) || substr(pap.national_identifier,8,5) national_identifier,
paa.assignment_number employee_number,
to_char(pap.original_date_of_hire,'YYYYMMDD') seniority,
hsc.segment2 local_unit,
hsc.segment3 Position_code,
hsc.segment4 work_title,
hsc.segment5 job_status,
hsc.segment6 cond_of_emp,
hsc.segment7 full_part_time,
hsc.segment8 shift_work,
hsc.segment9 payroll_period,
hsc.segment10 agreed_working_hrs,
decode(nvl(decode(hsc.segment23,'N',null,hsc.segment23),paa.hourly_salaried_code),'S',1,'H',2,3) hourly_salaried_code,
hsc.segment20 spl_info_1,
hsc.segment21 spl_info_2,
hsc.segment22 spl_info_3,
paa.assignment_id,
paa.payroll_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_date
AND pap.effective_end_date >= trunc(p_report_date,'Y')
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_date
AND paa.effective_end_date >= trunc(p_report_date,'Y')
AND hsc.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND hsc.segment2 = to_char(p_local_unit_id);
select org_information1 job_status,
org_information2 cond_of_emp,
org_information3 full_part_time,
org_information4 shift_work,
org_information5 payroll_period,
org_information6 agreed_working_hrs
FROM hr_organization_information hoi,
hr_organization_units hou
WHERE hou.organization_id=g_legal_employer_id
AND hoi.organization_id = hou.organization_id
AND hoi.org_information_context='NO_EMPLOYMENT_DEFAULTS';
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 = g_legal_employer_id
AND hoi_le.org_information_context = 'NO_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 = 'NO_LOCAL_UNIT_DETAILS';
SELECT sum(prrv.result_value) result
FROM
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f pet,
pay_element_type_extra_info pxi
WHERE
paa.assignment_id =p_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ptp.time_period_id = ppa.time_period_id
and p_report_date between ptp.start_date and ptp.end_date
and ppa.effective_date between ptp.start_date and ptp.end_date
and paa.assignment_action_id = prr.assignment_action_id
and prr.element_type_id = pet.element_type_id
and pet.element_type_id = pxi.element_type_id
and pxi.eei_information_category = 'NO_SSB_CODES'
and pxi.eei_information3 = 'ADD_INFO'
and pxi.eei_information2 = p_ssb_code
and to_char(prrv.input_value_id) = pxi.eei_information1
and prrv.run_result_id = prr.run_result_id
order by pxi.eei_information2;
SELECT sum(prrv.result_value) result
FROM
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f pet,
pay_element_type_extra_info pxi
WHERE
paa.assignment_id =p_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.effective_date between trunc(p_report_date,'Y') and p_report_date
and paa.assignment_action_id = prr.assignment_action_id
and prr.element_type_id = pet.element_type_id
and pet.element_type_id = pxi.element_type_id
and pxi.eei_information_category = 'NO_SSB_CODES'
and pxi.eei_information3 = 'ADD_INFO'
and pxi.eei_information2 = p_ssb_code
and to_char(prrv.input_value_id) = pxi.eei_information1
and prrv.run_result_id = prr.run_result_id
order by pxi.eei_information2;
SELECT end_date,
start_date
from per_time_periods
where payroll_id = p_payroll_id
and p_report_date BETWEEN start_date and end_date;
SELECT max(end_date) prev_end_date
from per_time_periods
where payroll_id = p_payroll_id
and end_date < p_report_date;
SELECT pay_assignment_actions_s.nextval INTO l_assact_id FROM dual;
SELECT fnd_date.canonical_to_date(action_information5) report_date,
action_information6 version,
nvl(action_information8,action_information4) name_sp
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 DETAILS'
AND pai.action_information1='PYNOSSTA';
SELECT
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8
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='PYNOSSTA'
AND pai.action_information2='LE';
SELECT
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8
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='PYNOSSTA'
AND pai.action_information2='LU';
SELECT
pai.action_information6 national_identifier,
pai.action_information7 employee_number,
pai.action_information8 seniority,
pai.action_information9 position_code,
pai.action_information10 work_title,
pai.action_information11 job_status,
pai.action_information12 cond_of_emp,
pai.action_information13 full_part_time,
pai.action_information14 shift_work,
pai.action_information15 agreed_working_hrs,
pai.action_information16 spl_info_1,
pai.action_information17 spl_info_2,
pai.action_information18 spl_info_3,
pai.action_information19 hourly_salaried,
pai.action_information20 payroll_period,
pai_sal.action_information6 SSB_CODE_0010_PTD,
pai_sal.action_information7 SSB_CODE_0010_YTD,
pai_sal.action_information8 SSB_CODE_0020_PTD,
pai_sal.action_information9 SSB_CODE_0020_YTD,
pai_sal.action_information10 SSB_CODE_0010_HRS_PTD,
pai_sal.action_information11 SSB_CODE_0010_HRS_YTD,
pai_sal.action_information12 SSB_CODE_0030_YTD,
pai_sal.action_information13 SSB_CODE_0035_YTD,
pai_sal.action_information14 SSB_CODE_0035_HRS_YTD,
pai_sal.action_information15 SSB_CODE_0037_YTD,
pai_sal.action_information16 SSB_CODE_0038_YTD,
pai_sal.action_information17 SSB_CODE_0038_PYQTD,
pai_sal.action_information18 SSB_CODE_0040_YTD,
pai_sal.action_information19 SSB_CODE_0041_YTD
FROM
pay_payroll_actions paa,
pay_assignment_actions assg,
pay_action_information pai,
pay_action_information pai_sal
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='PYNOSSTA'
AND pai.action_information2='ASG'
AND pai.action_information3=p_local_unit_id
AND pai_sal.action_context_id= assg.assignment_action_id
AND pai_sal.action_context_type='AAP'
AND pai_sal.action_information_category='EMEA REPORT INFORMATION'
AND pai_sal.action_information1='PYNOSSTA'
AND pai_sal.action_information2='ASG SAL'
AND pai_sal.action_information3=p_local_unit_id order by national_identifier;
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;