[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT get_parameters(c_payroll_action_id, 'Legal_Employer')
,start_date
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = c_payroll_action_id;
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token_name)+(LENGTH(p_token_name)+1),
INSTR(legislative_parameters,' ',
INSTR(legislative_parameters,p_token_name)))
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT hoi1.organization_id organization_id
,hoi2.org_information1 company_name
,hoi2.org_information3 representative_title
,hoi2.org_information8 cac
,hoi2.org_information2 person_id
FROM hr_organization_information hoi1
,hr_All_organization_units hou
,hr_organization_information hoi2
WHERE hou.business_group_id = c_business_group_id
AND hoi1.organization_id = hou.organization_id
AND hoi2.organization_id = hou.organization_id
AND hou.organization_id = NVL(c_legal_employer,hou.organization_id)
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi2.org_information_context = 'ES_STATUTORY_INFO'
ORDER BY hoi1.organization_id ;
SELECT pap.full_name representative_name
,decode(pap.per_information2, 'DNI', pap.per_information2, 'PASSPORT',pap.per_information3,NULL) dni_passport
FROM per_all_people_f pap
WHERE pap.person_id = c_person_id
AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
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
,hr_soft_coding_keyflex hr
,hr_organization_information hoi
WHERE asl.person_id BETWEEN stperson AND endperson
AND asl.primary_flag = 'Y'
AND ppos.period_of_service_id = asl.period_of_service_id
AND ppos.actual_termination_date BETWEEN c_start_date AND c_end_date
AND asl.effective_end_date = ppos.actual_termination_date
AND asl.business_group_id = c_business_group_id
AND hr.soft_coding_keyflex_id = asl.soft_coding_keyflex_id
AND hr.segment2 = hoi.org_information1
AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
AND hoi.organization_id = decode(c_legal_employer,NULL,hoi.organization_id,c_legal_employer)
AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
FROM pay_payroll_actions appa
,pay_assignment_actions act
,pay_action_information pai
WHERE act.assignment_id = asl.assignment_id
AND act.payroll_action_id = appa.payroll_action_id
AND appa.report_category = 'ARCHIVE'
AND appa.action_status = 'C'
AND appa.report_qualifier = 'ES'
AND appa.report_type = 'ES_COMP_CERT'
AND pai.action_context_id = act.assignment_action_id
AND pai.action_information_category = 'ES_CC_REP_EMPLOYEE'
AND pai.action_information21 = 'T');
SELECT assignment_id
FROM per_all_assignments_f asl
,hr_soft_coding_keyflex hr
,hr_organization_information hoi
,per_absence_attendance_types pat
,per_absence_attendances paa
,pay_payroll_actions ppa
where ppa.payroll_action_id = c_p_actid
AND asl.person_id BETWEEN stperson AND endperson
AND asl.primary_flag = 'Y'
AND asl.business_group_id = ppa.business_group_id
AND paa.person_id = asl.person_id
AND pat.absence_attendance_type_id = paa.absence_attendance_type_id
AND pat.absence_category = 'TD'
AND pat.business_group_id = ppa.business_group_id
AND paa.business_group_id = ppa.business_group_id
AND paa.date_start between c_start_date AND c_end_date
AND hr.soft_coding_keyflex_id = asl.soft_coding_keyflex_id
AND hr.segment2 = hoi.org_information1
AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
AND hoi.organization_id =NVL(c_legal_employer,hoi.organization_id)
AND c_end_date between asl.effective_start_date and asl.effective_end_date
AND NOT EXISTS (SELECT NULL
FROM pay_payroll_actions appa
,pay_assignment_actions act
,pay_action_information pai
WHERE act.assignment_id = asl.assignment_id
AND act.payroll_action_id = appa.payroll_action_id
AND appa.report_category = 'ARCHIVE'
AND appa.action_status = 'C'
AND appa.report_type = 'ES_COMP_CERT'
AND appa.report_qualifier = 'ES'
AND pai.action_context_id = act.assignment_action_id
AND pai.action_information_category = 'ES_CC_REP_EMPLOYEE'
AND pai.action_information21 = 'S'
AND pai.action_information22 = to_char(paa.absence_attendance_id))
AND NOT EXISTS (SELECT NULL
FROM pay_payroll_actions appa
,pay_assignment_actions act
WHERE appa.payroll_action_id = c_p_actid
AND act.payroll_action_id = appa.payroll_action_id
AND act.assignment_id = asl.assignment_id);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT addr.address_line1 address_line1
,addr.address_line2 address_line2
,addr.address_line3 address_line3
,addr.town_or_city town_or_city
,hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
,addr.postal_code postal_code
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 addr.address_line_1||' - '||hr_general.decode_lookup('HR_ES_LOCATION_TYPES',addr.address_line_1) address_line1
,addr.address_line_3 address_line3
,addr.town_or_city town_or_city
,hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
,addr.postal_code postal_code
,addr.telephone_number_1 telephone_number
FROM hr_organization_units hou,
hr_locations_all addr
WHERE hou.organization_id = c_organization_id
AND hou.location_id = addr.location_id;
SELECT pap.person_id person_id
,paa.assignment_id assignment_id
,paa.business_group_id organization_id
,pap.full_name emp_name
,decode(pap.per_information2, 'NIE', NULL,pap.per_information3) dni_passport
,paa.job_id job_id
,pps.date_start start_date
,pps.actual_termination_date end_date
,pps.leaving_reason leaving_reason
,hoi.organization_id legal_employer
,hr.segment2 work_center_id
,hr.segment5 cont_group
,hr_general.decode_lookup('ES_PROFESSIONAL_CAT'
,paa.employee_category) prof_catg
,paa.soft_coding_keyflex_id sc_key_id
,'T' type
, 0 abs_attn_id
,to_date('01-01-0001','dd-mm-yyyy') sickness_start_date
,to_date('31-12-4712','dd-mm-yyyy') sickness_end_date
,pps.pds_information5 accrued_vacation
,pps.pds_information6 vacation_accrued
,pps.pds_information7 vacation_taken
,pps.pds_information8 vacation_reamining
,fnd_date.canonical_to_date(pps.pds_information9) vacation_from
,fnd_date.canonical_to_date(pps.pds_information10) vacation_to
FROM per_all_people_f pap
,per_all_assignments_f paa
,per_periods_of_service pps
,pay_assignment_actions paas
,pay_payroll_actions ppa
,hr_soft_coding_keyflex hr
,hr_organization_information hoi
WHERE paas.assignment_action_id = p_assactid
AND paas.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paas.assignment_id
AND pap.person_id = paa.person_id
AND pap.person_id = pps.person_id
AND pps.period_of_service_id = paa.period_of_service_id
AND hr.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND hr.segment2 = hoi.org_information1
AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
AND pps.actual_termination_date BETWEEN ppa.start_date
AND ppa.effective_date
AND pps.actual_termination_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND pps.actual_termination_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
UNION
SELECT pap.person_id person_id
,paa.assignment_id assignment_id
,paa.business_group_id organization_id
,pap.full_name emp_name
,decode(pap.per_information2, 'NIE', NULL,pap.per_information3) dni_passport
,paa.job_id job_id
,pps.date_start start_date
,pps.actual_termination_date end_date
,pps.leaving_reason leaving_reason
,hoi.organization_id legal_employer
,hr.segment2 work_center_id
,hr.segment5 cont_group
,hr_general.decode_lookup('ES_PROFESSIONAL_CAT'
,paa.employee_category) prof_catg
,paa.soft_coding_keyflex_id sc_key_id
,'S' Type
,paat.absence_attendance_id abs_attn_id
,paat.date_start sickness_start_date
,paat.date_end sickness_end_date
,pps.pds_information5 accrued_vacation
,pps.pds_information6 vacation_accrued
,pps.pds_information7 vacation_taken
,pps.pds_information8 vacation_reamining
,fnd_date.canonical_to_date(pps.pds_information9) vacation_from
,fnd_date.canonical_to_date(pps.pds_information10) vacation_to
FROM per_all_people_f pap
,per_all_assignments_f paa
,pay_assignment_actions paas
,pay_payroll_actions ppa
,per_periods_of_service pps
,hr_soft_coding_keyflex hr
,hr_organization_information hoi
,per_absence_attendance_types pat
,per_absence_attendances paat
WHERE paas.assignment_action_id = p_assactid
AND paas.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paas.assignment_id
AND pps.period_of_service_id (+)= paa.period_of_service_id
AND pap.person_id = paa.person_id
AND pap.person_id = pps.person_id
AND pap.effective_start_date = (select max(papf.effective_start_date)
from per_all_people_f papf
where papf.person_id = pap.person_id
AND papf.effective_start_date <= ppa.effective_date)
AND paa.effective_start_date = (select max(paaf.effective_start_date)
from per_all_assignments_f paaf
where paaf.assignment_id = paa.Assignment_id
AND paaf.effective_start_date <= ppa.effective_date)
AND hr.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
AND hr.segment2 = hoi.org_information1
AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
AND pat.ABSENCE_ATTENDANCE_TYPE_ID = paat.ABSENCE_ATTENDANCE_TYPE_ID
AND pat.ABSENCE_CATEGORY = 'TD'
AND paat.person_id = pap.person_id
AND paat.date_start between ppa.start_date AND ppa.effective_date
order by Type desc;
SELECT pcf.contract_id contract_id
,hr_general.decode_lookup('CONTRACT_TYPE',pcf.type) contract_type
,hr_contract_api.get_active_end_date (pcf.contract_id
,p_effective_date,pcf.status) contract_end_date
FROM per_contracts_f pcf
where pcf.person_id = c_person_id
AND c_effective_date BETWEEN pcf.effective_start_date
AND pcf.effective_end_date;
SELECT screen_entry_value ss_id
FROM pay_element_entries_f peef
,pay_element_entry_values_f peev
,pay_input_values_f piv
,pay_element_types_f pet
WHERE pet.element_name = 'Social Security Details'
AND piv.element_type_id = pet.element_type_id
AND pet.legislation_code = 'ES'
AND piv.name ='Social Security Identifier'
AND peef.element_type_id = pet.element_type_id
AND peef.assignment_id = c_assignment_id
AND peev.element_entry_id = peef.element_entry_id
AND peev.input_value_id = piv.input_value_id
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_effective_date BETWEEN peef.effective_start_date
AND peef.effective_end_date
AND c_effective_date BETWEEN peev.effective_start_date
AND peev.effective_end_date
AND c_effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT hr_general.decode_lookup('STAT_TERM_REASONS',information1) prov
FROM per_shared_types
WHERE lookup_type ='LEAV_REAS'
AND system_type_cd = l_employee_data.leaving_reason
AND business_group_id = c_business_group_id;
SELECT hr_general.decode_lookup('STAT_TERM_REASONS',information1) prov
FROM per_shared_types
WHERE lookup_type ='LEAV_REAS'
AND system_type_cd = l_employee_data.leaving_reason
AND business_group_id IS NULL;
SELECT jbt.name
FROM per_jobs_tl jbt
WHERE jbt.language = userenv('LANG')
AND jbt.job_id = c_job_id;
SELECT hr_general.decode_lookup('ES_PROVINCE_CODES',addr.region_2) prov
FROM hr_organization_units hou,
hr_locations_all addr
WHERE hou.organization_id = c_wc_id
AND hou.location_id = addr.location_id;
SELECT pee.rowid row_id
,pee.element_entry_id
,min(decode(piv.name, 'Year', eev.screen_entry_value, null)) year
,min(decode(piv.name, 'Month', hr_general.decode_lookup('ES_MONTH_NAMES',eev.screen_entry_value), null)) month
,min(decode(piv.name, 'Contribution Days', eev.screen_entry_value, null)) contribution_days
,min(decode(piv.name, 'Regular Situation Base', eev.screen_entry_value, null)) rs_cont_base
,min(decode(piv.name, 'IA ID Contribution', eev.screen_entry_value, null)) ia_id_contribution
,min(decode(piv.name, 'Note', eev.screen_entry_value, null)) note
,min(decode(p_type,'S',decode(piv.name, 'Last TD Report Paid', eev.screen_entry_value, null),null)) last_TD_date
FROM pay_element_entries_f pee
,pay_element_entry_values_f eev
,pay_input_values_f piv
,pay_element_types_f pet
WHERE pee.element_entry_id = eev.element_entry_id
AND c_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND eev.input_value_id + 0 = piv.input_value_id
AND c_effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
AND piv.element_type_id = pet.element_type_id
AND c_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND pee.assignment_id = c_assignment_id
AND pet.element_name = decode(p_type,'T','Employee Termination Contribution Bases','Employee Temporary Disability Contribution Bases')
AND pet.legislation_code = 'ES'
AND c_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
GROUP BY pee.rowid
,pee.element_entry_id;
SELECT userenv('LANGUAGE') INTO g_nls_db_char FROM dual;
SELECT file_data INTO p_pdf_blob
FROM fnd_lobs
WHERE file_id = (SELECT MAX(file_id) FROM per_gb_xdo_templates
WHERE file_name like '%ES_company_cert.pdf%');
SELECT substr(pai1.action_information4,1,40) company_name
,substr(pai1.action_information5,1,15) CAC
,substr(pai1.action_information6,1,40) representative_name
,pai1.action_information7 representative_DNI
,substr(pai1.action_information8,1,45) representative_Position
,substr(pai2.action_information5,1,40) cloc_type
,substr(pai2.action_information7,1,10) cloc_no
,substr(pai2.action_information8,1,16) ccity
,substr(pai2.action_information10,1,24) cprov_name
,substr(pai2.action_information12,1,7) cpostal_code
,substr(pai2.action_information26,1,16) ctel_no
,substr(pai3.action_information3,1,34) emp_name
,substr(pai3.action_information4,1,15) dni_passport
,pai3.action_information5 social_security_identifier
,pai3.action_information6 cont_grp
,substr(pai3.action_information7,1,15) prof_catg
,substr(pai3.action_information8,1,25) emp_occupation
,pai3.action_information9 start_date
,pai3.action_information10 end_date
,pai3.action_information11 contract_end_date
,substr(pai3.action_information12,1,30) leaving_reason
,pai3.action_information13 accured_vac
,pai3.action_information14 no_vac_days
,pai3.action_information15 vac_from
,pai3.action_information16 vac_till
,pai3.action_information17 sick_leave_start_date
,pai3.action_information18 number1
,pai3.action_information19 date1
,substr(pai3.action_information20,1,14) contract_type
,substr(pai3.action_information23,1,10) wc_prov
,substr(pai4.action_information5,1,15) eloc_type
,substr(pai4.action_information6,1,10) eloc_name
,substr(pai4.action_information7,1,8) eloc_no
,substr(pai4.action_information8,1,15) ecity
,substr(pai4.action_information10,1,13) eprov_name
,substr(pai4.action_information12,1,6) epostal_code
,paa.assignment_action_id
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_information pai1 --Employer rec
,pay_action_information pai2 --Employer Address
,pay_action_information pai3 --Employee rec
,pay_action_information pai4 --Employee address
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND pai1.action_context_id = ppa.payroll_action_id
AND pai2.action_context_id (+)= pai1.action_context_id
AND pai1.action_context_type = 'PA'
AND pai2.action_context_type (+)= 'PA'
AND pai1.action_information_category = 'ES_CC_REP_EMPLOYER'
AND pai2.action_information_category(+)= 'ADDRESS DETAILS'
AND pai1.action_information1 = pai2.action_information1(+)
AND pai3.action_context_type = 'AAP'
AND pai3.action_context_id = paa.assignment_action_id
AND pai4.action_context_id (+)= pai3.action_context_id
AND pai4.action_context_type (+)= 'AAP'
AND pai3.action_information_category = 'ES_CC_REP_EMPLOYEE'
AND pai4.action_information_category(+)= 'ADDRESS DETAILS'
AND pai3.action_information1 = pai4.action_information1(+)
AND pai3.action_information2 = pai1.action_information1
AND pai1.action_information1 = NVL(p_legal_employer,pai1.action_information1)
AND pai3.action_information1 = NVL(p_person_id,pai3.action_information1);
SELECT pai1.action_information5 Type
,pai1.action_information6 Year
,substr(pai1.action_information7,1,12) Month
,pai1.action_information8 contribution_days
,pai1.action_information9 contribution_base
,pai1.action_information10 ia_id_cont
,substr(pai1.action_information11,1,20) note
FROM pay_action_information pai1
WHERE pai1.action_context_id = c_assignment_action_id
AND pai1.action_context_type = 'AAP'
AND pai1.action_information_category = 'ES_CC_REP_ELEMENT_INFO'
AND pai1.action_information5 = 'T';
SELECT pai1.action_information5 Type
,pai1.action_information6 Year
,substr(pai1.action_information7,1,12) Month
,pai1.action_information8 contribution_days
,pai1.action_information9 contribution_base
,pai1.action_information10 ia_id_cont
,substr(pai1.action_information11,1,20) note
,pai1.action_information12 last_td_date
FROM pay_action_information pai1
WHERE pai1.action_context_id = c_assignment_action_id
AND pai1.action_context_type = 'AAP'
AND pai1.action_information_category = 'ES_CC_REP_ELEMENT_INFO'
AND pai1.action_information5 = 'S';
vXMLTable.DELETE;