[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 paa.date_start start_date
,NVL(paa.date_end,to_date('31-12-4712','dd-mm-yyyy')) end_date
,paar.name Reason
,paat.absence_category Reason_Category
,paa.abs_information1
,paa.abs_information2
,paa.abs_information3
,paa.abs_information4
,paa.abs_information5
,paa.abs_information6
,paa.abs_information7
,paa.abs_information8
FROM per_abs_attendance_reasons paar
,per_absence_attendances paa
,per_absence_attendance_types paat
WHERE paa.absence_attendance_id = p_absence_attendance_id
AND paa.absence_attendance_type_id = paat.absence_attendance_type_id
AND paa.abs_attendance_reason_id = paar.abs_attendance_reason_id (+);
SELECT papf.sex
FROM per_all_people_f papf
,per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND papf.person_id = paaf.person_id
AND p_date_earned BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_date_earned BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT (to_date('01-01-'||to_char(p_date_earned,'yyyy'),'dd-mm-yyyy')
-to_date('01-01-'||to_char(to_number(to_char(p_date_earned,'yyyy'))-1),'dd-mm-yyyy'))
INTO l_Days
FROM dual;
SELECT paa.date_start start_date
,paa.date_end end_date
,paa.abs_information1 linked_absence
FROM per_absence_attendances paa
WHERE paa.absence_attendance_id = l_absence_attendance_id
AND paa.date_start > ADD_MONTHS(p_disability_start_date,-6);
SELECT COUNT(pcr.contact_type)
FROM per_contact_relationships pcr
,per_all_assignments_f paaf
WHERE paaf.assignment_id = passignment_id
AND pcr.person_id = paaf.person_id
AND pcr.rltd_per_rsds_w_dsgntr_flag = 'Y'
AND pcr.cont_information_category = 'ES'
AND (pcr.cont_information1 = 'Y'
AND pcr.contact_type IN ('C','A'))
AND peffective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND peffective_date BETWEEN nvl(pcr.date_start,START_OF_TIME)
AND nvl(pcr.date_end,END_OF_TIME);
SELECT NVL(PAA1.date_end,to_date('31-12-4712','dd-mm-yyyy'))-PAA1.date_start+1
,PAAT1.absence_category Reason_Category_prev
,PAAT2.absence_category Reason_Category
FROM per_absence_attendances PAA1
,per_absence_attendances PAA2
,per_absence_attendance_types PAAT1
,per_absence_attendance_types PAAT2
WHERE PAA2.absence_attendance_id = p_absence_attendance_id
AND PAA2.date_start = PAA1.date_end + 1
AND PAA1.person_id = PAA2.person_id
AND PAA1.absence_attendance_type_id = PAAT1.absence_attendance_type_id
AND PAA2.absence_attendance_type_id = PAAT2.absence_attendance_type_id;
SELECT nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy')) end_date
,0 PU_perc
,fnd_date.canonical_to_date(hoi.org_information1) start_date
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_work_center
AND hoi.org_information_context IN ('ES_WC_NATURAL_DISASTER','ES_WC_SHUTDOWN')
AND p_date_between BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
AND nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy'));
SELECT nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy')) end_date
,fnd_number.canonical_to_number(hoi.org_information3) PU_perc
,fnd_date.canonical_to_date(hoi.org_information1) start_date
,hoi.org_information5 Cal_metod
,hoi.org_information6 Rate_formula
,hoi.org_information7 Duration_Formula
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_work_center
AND hoi.org_information_context IN ('ES_WC_PARTIAL_UNEMPLOYMENT')
AND p_date_between BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
AND nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy'));
SELECT nvl(fnd_date.canonical_to_date(hoi.org_information2),to_date('31-12-4712','dd-mm-yyyy')) end_date
,fnd_number.canonical_to_number(hoi.org_information3) PU_perc
,fnd_date.canonical_to_date(hoi.org_information1) start_date
,hoi.org_information5 Cal_metod
,hoi.org_information6 Rate_formula
,hoi.org_information7 Duration_Formula
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_work_center
AND hoi.org_information_context IN ('ES_WC_PARTIAL_UNEMPLOYMENT')
AND fnd_date.canonical_to_date(hoi.org_information1) BETWEEN p_period_start_date
AND p_period_end_date;
SELECT org_information2 l_bu_calc_method
,org_information3 l_bu_daily_rate_ff
,org_information4 l_bu_duration_ff
,GREATEST(fnd_date.canonical_to_date(org_information5), p_start_date) l_bu_start_date
,LEAST(nvl(fnd_date.canonical_to_date(org_information6),to_date('31-12-4712','dd-mm-yyyy')),p_end_date) l_bu_end_date
FROM hr_organization_information
WHERE organization_id = l_legal_emp_id
AND org_information_context = 'ES_BENEFIT_UPLIFT'
AND org_information1 = p_abs_cat
AND ((p_start_date BETWEEN fnd_date.canonical_to_date(ORG_INFORMATION5)
AND NVL(fnd_date.canonical_to_date(ORG_INFORMATION6),to_date('31-12-4712','DD-MM-YYYY')))
OR (fnd_date.canonical_to_date(ORG_INFORMATION5) BETWEEN p_start_date
AND p_end_date))
ORDER BY org_information5 ;
SELECT hoi.organization_id le_id
FROM hr_organization_information hoi
WHERE hoi.org_information1 = p_wc_organization_id
AND hoi.org_information_context = 'ES_WORK_CENTER_REF';
SELECT ptp.start_date start_date
,ptp.end_date end_date
,ppa.action_type action_type
,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_period_types ptpt
,per_time_periods ptp
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 ppa.payroll_action_id = paa2.payroll_action_id
AND ppa.business_group_id = paaf1.business_group_id
AND paa2.source_action_id IS NULL
AND ptp.period_type = ptpt.period_type
AND ptp.start_date < c_period_start_date
AND ptp.payroll_id = ppa.payroll_id
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
ORDER BY ptp.start_date DESC,paa2.assignment_action_id DESC;
/* SELECT ptp.start_date start_date
,ptp.end_date end_date
,paa2.assignment_action_id assignment_action_id
,ppa.action_type action_type
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.start_date BETWEEN paaf1.effective_start_date
AND paaf1.effective_end_date
AND ptp.start_date BETWEEN paaf2.effective_start_date
AND paaf2.effective_end_date
ORDER BY ptp.start_date DESC,paa2.assignment_action_id DESC;