[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pap.per_information5
,pps.adjusted_svc_date
FROM per_all_people_f pap
,per_all_assignments_f paaf
,per_periods_of_service pps
WHERE paaf.assignment_id = c_assignment_id
AND paaf.person_id = pap.person_id
AND pap.person_id = pps.person_id
AND paaf.period_of_service_id = pps.period_of_service_id
AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date
AND c_effective_date between pap.effective_start_date and pap.effective_end_date;
SELECT segment5
,segment2
FROM per_all_assignments_f paaf
,hr_soft_coding_keyflex scl
WHERE paaf.assignment_id = c_assignment_id
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
SELECT GREATEST (paa.DATE_START,c_period_start_date) start_date
,LEAST(c_period_end_date,nvl(paa.date_end,to_date('31/12/4712','dd/mm/yyyy'))) end_date
,paa.abs_information3 ptm_perc
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,per_all_people_f pap
,per_all_assignments_f paaf
WHERE paaf.assignment_id = c_assignment_id
AND paaf.business_group_id = c_business_group_id
AND paaf.person_id = pap.person_id
AND pap.person_id = paa.person_id
AND paat.absence_category = c_leave_type
AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
AND NVL(paa.date_end,c_period_end_date) >= c_period_start_date
AND paa.date_start <= c_period_end_date
AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND c_effective_date BETWEEN pap.effective_start_date AND Pap.effective_end_date;
SELECT GREATEST(paa.date_start,c_period_start_date) start_date
,LEAST(c_period_end_date,nvl(paa.date_end,to_date('31/12/4712','dd/mm/yyyy'))) end_date
,time_start
,time_end
FROM per_absence_attendances paa
,per_absence_attendance_types paat
,per_all_people_f pap
,per_all_assignments_f paaf
WHERE paaf.assignment_id = c_assignment_id
AND paaf.business_group_id = c_business_group_id
AND paaf.person_id = pap.person_id
AND pap.person_id = paa.person_id
AND paat.absence_category = c_leave_type
AND paat.absence_attendance_type_id = paa.absence_attendance_type_id
AND NVL(paa.date_end,c_period_end_date) >= c_period_start_date
AND paa.DATE_start <= c_period_end_date
AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date
AND c_effective_date between pap.effective_start_date and pap.effective_end_date;
SELECT hoi.org_information3
,hoi.org_information4
,hoi.org_information5
,hoi.org_information6
,hoi.org_information7
,hoi.org_information8
,hoi.org_information9
,hoi.org_information10
,hoi.org_information11
,hoi.org_information12
FROM hr_organization_information hoi
WHERE hoi.organization_id = c_work_center
AND hoi.org_information_context = 'ES_WORK_CENTER_DETAILS';
SELECT hoi2.org_information1 situation
,fnd_date.canonical_to_date(hoi2.org_information2) start_date
,nvl(fnd_date.canonical_to_date(hoi2.org_information3),c_period_end_date) end_date
FROM hr_organization_information hoi
,hr_organization_information hoi1
,hr_all_organization_units hou
,hr_organization_information hoi2
WHERE hou.business_group_id = p_business_gr_id
AND hoi.org_information1 = c_work_center
AND hoi.org_information_context = 'ES_WORK_CENTER_REF'
AND hoi1.organization_id = hou.organization_id
AND hoi2.organization_id = hou.organization_id
AND hou.organization_id = hoi.organization_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi2.org_information_context = c_type
AND fnd_date.canonical_to_date(hoi2.org_information2) <= c_period_end_date
and nvl(fnd_date.canonical_to_date(hoi2.org_information3),c_period_end_date) >= c_period_start_date
ORDER BY hoi1.organization_id ;
SELECT sum(CTR_INFORMATION2) In_Class_trng_hours
,sum(CTR_INFORMATION3) Remote_trng_hours
FROM PER_CONTRACTS_f pcf
,per_all_assignments_f paaf
WHERE paaf.assignment_id = c_assignment_id
AND paaf.business_group_id = c_business_gr_id
AND paaf.contract_id = pcf.contract_id
AND pcf.ctr_information_category = 'ES'
AND pcf.ctr_information1 = 'ES_TRAINING'
AND c_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND c_effective_date BETWEEN pcf.effective_start_date
AND pcf.effective_end_date ;
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = p_bal_name
AND pbd.database_item_suffix = p_db_item_suffix;
SELECT ptp.start_date start_date
,ptp.end_date end_date
,ppa.action_type
,MAX(paa2.assignment_action_id) assignment_action_id
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,per_all_assignments_f paaf1
,per_all_assignments_f paaf2
,pay_payroll_actions ppa
,pay_payroll_actions ppa1
,per_time_periods ptp
,per_time_period_types ptpt
WHERE paa1.assignment_action_id = c_assignment_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.business_group_id = paaf1.business_group_id
AND paa1.assignment_id = paaf1.assignment_id
AND paaf1.person_id = paaf2.person_id
AND paaf2.business_group_id = paaf1.business_group_id
AND paaf2.assignment_id = paa2.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND paa2.payroll_action_id = ppa.payroll_action_id
AND paa2.source_action_id IS NULL
AND ptp.start_date < c_period_start_date
AND ppa.payroll_id = ptp.payroll_id
AND ppa.business_group_id = paaf2.business_group_id
AND ptp.period_type = ptpt.period_type
AND ppa.action_type IN ('R','Q','I','B')
AND ppa.action_status IN('C','U')
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND ptp.end_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND ptp.end_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
GROUP BY ptp.start_date, ptp.end_date, ppa.action_type
ORDER BY 1 desc;
/* SELECT ptp.start_date start_date
,ptp.end_date end_date
,ppa.action_type
,MAX(paa2.assignment_action_id) assignment_action_id
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,per_all_assignments_f paaf1
,per_all_assignments_f paaf2
,pay_payroll_actions ppa
,per_time_periods ptp
WHERE paa1.assignment_action_id = c_assignment_action_id
AND paa1.assignment_id = paaf1.assignment_id
AND paaf1.person_id = paaf2.person_id
AND paaf2.assignment_id = paa2.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND paa2.payroll_action_id = ppa.payroll_action_id
AND paa2.source_action_id IS NULL
AND ptp.start_date < c_period_start_date
AND ppa.payroll_id = ptp.payroll_id
AND ppa.time_period_id = ptp.time_period_id
AND ppa.action_type IN ('R','Q','I','B')
AND ppa.action_status IN('C','U')
AND ptp.end_date BETWEEN paaf1.effective_start_date
AND paaf1.effective_end_date
AND ptp.end_date BETWEEN paaf2.effective_start_date
AND paaf2.effective_end_date
GROUP BY ptp.start_date, ptp.end_date, ppa.action_type
ORDER BY 1 desc;
SELECT hoi.organization_id
FROM hr_organization_information hoi
WHERE hoi.org_information1 = c_work_center_id
AND hoi.org_information_context = 'ES_WORK_CENTER_REF';
SELECT pur.row_low_range_or_name row_val
,puci2.value Offset
,puci3.value Duration
FROM pay_user_columns puc1
,pay_user_columns puc2
,pay_user_columns puc3
,pay_user_rows_f pur
,pay_user_tables put
,pay_user_column_instances_f puci1
,pay_user_column_instances_f puci2
,pay_user_column_instances_f puci3
WHERE put.legislation_code = 'ES'
AND pur.user_table_id = put.user_table_id
AND puc1.user_table_id = put.user_table_id
AND puc1.user_column_name='REBATE_REDUCTION_ID'
AND puc2.user_table_id = put.user_table_id
AND puc2.user_column_name='OFFSET'
AND puc3.user_table_id = put.user_table_id
AND puc3.user_column_name='DURATION'
AND puci1.user_row_id = pur.user_row_id
AND puci1.user_column_id = puc1.user_column_id
AND puci1.value = c_reduction_id
AND puci2.user_row_id = pur.user_row_id
AND puci2.user_column_id = puc2.user_column_id
AND puci3.user_row_id = pur.user_row_id
AND puci3.user_column_id = puc3.user_column_id
AND put.user_table_name like 'ES_REBATE_OR_REDUCTION_RATES'
AND c_efective_date BETWEEN puci1.effective_start_date AND puci1.effective_end_date
AND c_efective_date BETWEEN puci2.effective_start_date AND puci2.effective_end_date
AND c_efective_date BETWEEN puci3.effective_start_date AND puci3.effective_end_date
AND c_efective_date BETWEEN pur.effective_start_date AND pur.effective_end_date
ORDER BY 1;
SELECT Sum(decode(piv2.name, c_input_value_name, nvl(peev2.screen_entry_value,0), null)) adjusted_period
,min(decode(piv2.name, 'Epigraph Code', nvl(peev2.screen_entry_value,'x'), null)) epigraph_code
,min(decode(piv2.name, 'Secondary CAC', nvl(peev2.screen_entry_value,'x'), null)) Secondary_CAC
FROM pay_element_entries_f peef1
,pay_element_entry_values_f peev1
,pay_element_entry_values_f peev2
,pay_input_values_f piv1
,pay_input_values_f piv2
,pay_element_types_f pet
WHERE pet.element_name = c_element_name
AND piv1.element_type_id = pet.element_type_id
AND piv2.element_type_id = pet.element_type_id
AND pet.legislation_code = 'ES'
AND piv1.name ='Reason'
AND peev1.screen_entry_value = c_type
AND peef1.element_type_id = pet.element_type_id
AND peef1.assignment_id = c_assignment_id
AND peev1.element_entry_id = peef1.element_entry_id
AND peev2.element_entry_id = peef1.element_entry_id
AND peev1.input_value_id = piv1.input_value_id
AND peev2.input_value_id = piv2.input_value_id
AND NVL(peef1.date_earned, c_period_end_date) BETWEEN c_rec_start_date
AND c_rec_end_date
AND c_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_effective_date BETWEEN peef1.effective_start_date
AND peef1.effective_end_date
AND c_effective_date BETWEEN peev1.effective_start_date
AND peev1.effective_end_date
AND c_effective_date BETWEEN piv1.effective_start_date
AND piv1.effective_end_date
AND c_effective_date BETWEEN peev2.effective_start_date
AND peev2.effective_end_date
AND c_effective_date BETWEEN piv2.effective_start_date
AND piv2.effective_end_date;
SELECT fnd_date.canonical_to_date(hoi.org_information1) Information_1
,nvl(fnd_date.canonical_to_date(hoi.org_information2),c_period_end_date) Information_2
,hoi.org_information3 Information_3
FROM hr_organization_information hoi
WHERE hoi.organization_id = c_work_center
AND hoi.org_information_context = c_context --'ES_WC_PARTIAL_UNEMPLOYMENT'~~'ES_WC_NATURAL_DISASTER'
ORDER BY 1;
SELECT pee.element_entry_id
,GREATEST(pee.effective_start_date, c_start_date) start_date
,LEAST(pee.effective_end_date, c_end_date) end_date
,min(decode(piv.name, 'SS Epigraph 126', nvl(peev.screen_entry_value,'x'), null)) epigraph_126
,min(decode(piv.name, 'SS Epigraph 114', nvl(peev.screen_entry_value,'x'), null)) epigraph_114
,min(decode(piv.name, 'SS Epigraph Code', nvl(peev.screen_entry_value,'x'), null)) epigraph_code
,min(decode(piv2.name, 'Work Center CAC', nvl(peev2.screen_entry_value,0), null)) work_center_cac
FROM pay_element_entries_f pee
,pay_element_entries_f pee2
,pay_element_types_f pet
,pay_element_types_f pet2
,pay_input_values_f piv
,pay_input_values_f piv2
,pay_element_entry_values_f peev
,pay_element_entry_values_f peev2
WHERE pee.assignment_id = c_assignment_id
AND pee2.assignment_id = pee.assignment_id
AND pet.element_name = 'Social Security Details'
AND pet2.element_name = 'Multiple Employment Details'
AND pet.legislation_code = 'ES'
AND pet2.legislation_code = 'ES'
AND piv.legislation_code = 'ES'
AND piv2.legislation_code = 'ES'
AND pee.element_type_id = pet.element_type_id
AND pee2.element_type_id = pet2.element_type_id
AND piv.element_type_id = pet.element_type_id
AND piv2.element_type_id = pet2.element_type_id
AND peev.input_value_id = piv.input_value_id
AND peev2.input_value_id = piv2.input_value_id
AND peev.element_entry_id = pee.element_entry_id
AND peev2.element_entry_id = pee2.element_entry_id
AND pee.effective_start_date = peev.effective_start_date
AND pee2.effective_start_date = peev2.effective_start_date
AND pee.effective_end_date = peev.effective_end_date
AND pee2.effective_end_date = peev2.effective_end_date
AND pee2.effective_start_date = pee.effective_start_date
AND pee2.effective_end_date = pee.effective_end_date
AND (pee.effective_start_date <= c_end_date
AND pee.effective_end_date >= c_start_date)
AND c_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND c_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND c_start_date BETWEEN pet2.effective_start_date AND pet2.effective_end_date
AND c_start_date BETWEEN piv2.effective_start_date AND piv2.effective_end_date
GROUP BY pee.element_entry_id
,pee.effective_start_date
,pee.effective_end_date;
SELECT segment5
,segment2
FROM per_all_assignments_f paaf
,hr_soft_coding_keyflex scl
WHERE paaf.assignment_id = c_assignment_id
AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND c_effective_date between paaf.effective_start_date and paaf.effective_end_date;
cac_epigraph_change.DELETE;
SELECT ptp.start_date start_date
,ptp.end_date end_date
,ppa.action_type
,max(paa2.assignment_action_id) assignment_action_id
FROM pay_assignment_actions paa1
,per_all_assignments_f paaf1
,per_all_assignments_f paaf2
,pay_assignment_actions paa2
,pay_payroll_actions ppa
,pay_payroll_actions ppa1
,per_time_periods ptp
,per_time_period_types ptpt
WHERE paa1.assignment_action_id = c_assignment_action_id
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND ppa1.business_group_id = paaf1.business_group_id
AND paaf1.assignment_id = paa1.assignment_id
AND paaf2.person_id = paaf1.person_id
AND paaf2.business_group_id = paaf1.business_group_id
AND paa2.assignment_id = paaf2.assignment_id
AND paa2.tax_unit_id = paa1.tax_unit_id
AND paa2.source_action_id IS NULL
AND ppa.payroll_action_id = paa2.payroll_action_id
AND ppa.action_type IN ('R','Q','I','B')
AND ppa.action_status IN ('C','U')
AND ppa.business_group_id = paaf2.business_group_id
AND ptp.payroll_id = ppa.payroll_id
AND ptp.period_type = ptpt.period_type
AND ptp.start_date < c_period_start_date
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
AND ptp.end_date BETWEEN paaf1.effective_start_date AND paaf1.effective_end_date
AND ptp.end_date BETWEEN paaf2.effective_start_date AND paaf2.effective_end_date
GROUP BY ptp.start_date, ptp.end_date, ppa.action_type
ORDER BY 1 desc;
/* SELECT ptp.start_date start_date
,ptp.end_date end_date
,ppa.action_type
,MAX(paa2.assignment_action_id) assignment_action_id
FROM pay_assignment_actions paa1
,pay_assignment_actions paa2
,per_all_assignments_f paaf1
,per_all_assignments_f paaf2
,pay_payroll_actions ppa
,per_time_periods ptp
WHERE paa1.assignment_action_id = c_assignment_action_id
AND paa1.assignment_id = paaf1.assignment_id
AND paaf1.person_id = paaf2.person_id
AND paaf2.assignment_id = paa2.assignment_id
AND paa1.tax_unit_id = paa2.tax_unit_id
AND paa2.payroll_action_id = ppa.payroll_action_id
AND paa2.source_action_id IS NULL
AND ptp.start_date < c_period_start_date
AND ppa.payroll_id = ptp.payroll_id
AND ppa.time_period_id = ptp.time_period_id
AND ppa.action_type IN ('R','Q','I','B')
AND ppa.action_status IN('C','U')
AND ptp.end_date BETWEEN paaf1.effective_start_date
AND paaf1.effective_end_date
AND ptp.end_date BETWEEN paaf2.effective_start_date
AND paaf2.effective_end_date
GROUP BY ptp.start_date, ptp.end_date, ppa.action_type
ORDER BY 1 desc;
SELECT hoi.organization_id
FROM hr_organization_information hoi
WHERE hoi.org_information1 = c_work_center_id
AND hoi.org_information_context = 'ES_WORK_CENTER_REF';