[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT to_number(NVL(ucf.value,0)) DefaultValue
FROM pay_user_column_instances_f ucf
,pay_user_columns uc
,pay_user_rows_f ur
,pay_user_tables ut
WHERE ut.legislation_code = 'NO'
AND ut.user_table_name = 'Self Certification Rules'
AND ur.row_low_range_or_name = c_rule
AND ur.legislation_code = 'NO'
AND ur.user_table_id = ut.user_table_id
AND c_date BETWEEN ur.effective_start_date
AND ur.effective_end_date
AND uc.user_column_name='Default'
AND uc.legislation_code = 'NO'
AND uc.user_table_id = ut.user_table_id
AND ur.row_low_range_or_name = p_rule
AND ucf.legislation_code = 'NO'
AND ucf.user_row_id = ur.user_row_id
AND ucf.user_column_id = uc.user_column_id
AND c_date BETWEEN ucf.effective_start_date
AND ucf.effective_end_date;
SELECT max (decode (uc.user_column_name
,'Default'
,ucf.value
,0)) DefaultValue
,max (decode (uc.user_column_name
,'Minimum'
,ucf.value
,0)) MinValue
,max (decode (uc.user_column_name
,'Maximum'
,ucf.value
,0)) MaxValue
FROM pay_user_column_instances_f ucf
,pay_user_columns uc
,pay_user_rows_f ur
,pay_user_tables ut
WHERE ut.legislation_code = 'NO'
AND ut.user_table_name = 'Self Certification Rules'
AND ur.row_low_range_or_name = c_rule
AND ur.legislation_code = 'NO'
AND ur.user_table_id = ut.user_table_id
AND c_date BETWEEN ur.effective_start_date
AND ur.effective_end_date
AND uc.legislation_code = 'NO'
AND uc.user_table_id = ut.user_table_id
AND ucf.legislation_code = 'NO'
AND ucf.user_row_id = ur.user_row_id
AND ucf.user_column_id = uc.user_column_id
AND c_date BETWEEN ucf.effective_start_date
AND ucf.effective_end_date
GROUP BY ur.row_low_range_or_name;
SELECT hoi.organization_id org_id
FROM per_all_assignments_f paaf
,per_all_people_f papf
,hr_soft_coding_keyflex hsc
,hr_organization_information hoi
WHERE papf.person_id = c_person_id
AND paaf.person_id = papf.person_id
AND paaf.primary_flag = 'Y'
AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
AND hoi.org_information1 = hsc.segment2
AND hoi.org_information_context = 'NO_LOCAL_UNITS';
SELECT decode (c_rule
,'CONSECUTIVE_DAYS' ,org_information1
,'YEARLY_DAYS' ,org_information2
,'YEARLY_COUNT' ,org_information3
,'EMPLOYER_PERIOD_DAYS' ,org_information4
,0) override
FROM hr_organization_information
WHERE organization_id = c_org_id
AND org_information_context = 'NO_SC_OVERRIDE';
SELECT p_conn_start_date - prev_abs.date_end duration
,prev_abs.date_start start_date
,prev_abs.date_end end_date
FROM per_absence_attendances prev_abs
,per_absence_attendance_types pat
WHERE prev_abs.absence_attendance_type_id = pat.absence_attendance_type_id
AND prev_abs.date_end < p_conn_start_date
AND pat.absence_category IN ('S','PTS')
AND (((p_conn_start_date - prev_abs.date_end) <= p_abs_link_period)
OR (decode (trim (to_char (prev_abs.date_end,'DAY'))
,'FRIDAY',next_day (prev_abs.date_end,'SUNDAY')
,'SATURDAY',next_day (prev_abs.date_end,'SUNDAY')
,prev_abs.date_end) BETWEEN decode (trim (to_char (p_conn_start_date,'DAY'))
,'MONDAY',p_conn_start_date - 2
,'SUNDAY',p_conn_start_date - 1
,p_conn_start_date) - (p_abs_link_period+1)
AND decode (trim (to_char (p_conn_start_date,'DAY'))
,'MONDAY',p_conn_start_date - 2
,'SUNDAY',p_conn_start_date - 1
,p_conn_start_date)-1))
AND prev_abs.date_start IS NOT NULL
AND prev_abs.date_end IS NOT NULL
AND prev_abs.person_id = p_person_id
ORDER BY duration;
SELECT 'Y'
FROM per_absence_attendances abs
WHERE abs.person_id = p_person_id
AND abs.date_start is not null
AND p_date_start is not null
AND (p_absence_attendance_id is null or
p_absence_attendance_id <> abs.absence_attendance_id)
AND (( abs.date_start
BETWEEN p_date_start AND NVL(p_date_end,hr_api.g_eot)
)
OR
( p_date_start
BETWEEN abs.date_start AND NVL(abs.date_end,hr_api.g_eot)
)
);
SELECT pap.date_of_birth
,ROUND(MONTHS_BETWEEN( p_eff_date, pap.date_of_birth ) / 12, 2) AS AGE
,pcr.contact_type
,pcr.cont_information1
,pcr.cont_information2
,nvl(pcr.date_end,to_date('31-12-4712','DD-MM-YYYY')) date_end
FROM per_all_people_f pap
,per_contact_relationships pcr
WHERE pap.person_id = pcr.contact_person_id
AND pcr.person_id = p_person_id
AND pcr.contact_type = p_contact_type
AND (pcr.date_start is null or pcr.date_start <= p_eff_date)
AND (pcr.date_end is null or pcr.date_end >= p_eff_date );
SELECT abs.date_start
,abs.date_end
,abs.absence_days
FROM per_absence_attendances abs
,per_absence_attendance_types paat
WHERE abs.person_id = p_person_id
AND abs.absence_attendance_type_id = paat.absence_attendance_type_id
AND paat.absence_category = 'CMS'
AND abs.absence_attendance_id <> NVL(l_glb_absence_attendance_id,-1)
AND abs.date_start IS NOT NULL
AND (
(abs.date_start BETWEEN p_start_date AND p_end_date)
OR
(abs.date_end BETWEEN p_start_date AND p_end_date)
);
SELECT pap.date_of_birth
,ROUND(MONTHS_BETWEEN( abs_stdt, pap.date_of_birth ) / 12, 2) AS AGE
,pcr.contact_type
,pcr.cont_information1
,pcr.cont_information2
,NVL(pcr.date_end,TO_DATE('31-12-4712','DD-MM-YYYY')) date_end
,pcr.contact_relationship_id
FROM per_all_people_f pap
,per_contact_relationships pcr
WHERE pap.person_id = pcr.contact_person_id
AND pcr.person_id = personid
AND pcr.contact_type = contacttype
AND (pcr.date_start IS NULL OR pcr.date_start <= abs_stdt)
AND (pcr.date_end IS NULL OR pcr.date_end >= abs_stdt );
SELECT pceif.effective_start_date
,pceif.effective_end_date
,fnd_date.canonical_to_date(pceif.cei_information1) start_date
,fnd_date.canonical_to_date(pceif.cei_information2) end_date
FROM per_contact_extra_info_f pceif
WHERE pceif.contact_relationship_id = p_con_relationship_id
AND pceif.information_type = 'NO_CMS_DIS_DATE';
SELECT fnd_number.canonical_to_number(global_value)
FROM ff_globals_f
WHERE global_name = p_global_name
AND legislation_code = 'NO'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT sex FROM per_all_people_f
WHERE person_id = l_person_id
AND l_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT fnd_number.canonical_to_number(global_value)
FROM ff_globals_f
WHERE global_name = p_global_name
AND legislation_code = 'NO'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT start_date,PER_INFORMATION7 FROM per_all_people_f where
person_id = p_person_id
AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE;
SELECT (PAA.DATE_END - PAA.DATE_START) + 1 AS DAYS,PAA.DATE_START, PAA.DATE_END
FROM PER_ABSENCE_ATTENDANCES PAA, PER_ABSENCE_ATTENDANCE_TYPES PAT
WHERE PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAT.ABSENCE_ATTENDANCE_TYPE_ID
AND PAT.ABSENCE_CATEGORY = 'UN'
AND PAA.DATE_START IS NOT NULL
AND PAA.DATE_END IS NOT NULL
AND PAA.PERSON_ID = personid
ORDER BY DATE_START ;
select name from PER_ABS_ATTENDANCE_REASONS par
WHERE PAR.ABS_ATTENDANCE_REASON_ID = attn_reason_id ;
SELECT DATE_START
,DATE_END
FROM PER_ABSENCE_ATTENDANCES
WHERE PERSON_ID = personid
AND DATE_END BETWEEN (abs_start_date - abs_link_period) AND (abs_start_date -1)
AND ABS_INFORMATION1 = 'SC'
AND DATE_START IS NOT NULL
AND DATE_END IS NOT NULL;
SELECT count (paa.absence_attendance_id) counts
,sum ((paa.date_end - paa.date_start) + 1) days
FROM per_absence_attendances paa
WHERE paa.person_id = c_person_id
AND paa.date_end BETWEEN add_months (c_start_date,- 12)
AND (c_start_date - 1)
AND paa.abs_information1 = 'SC'
AND paa.date_start IS NOT NULL
AND paa.date_end IS NOT NULL;
SELECT count(1)
INTO l_abs_count
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.PERSON_ID = l_person_id
AND PAA.DATE_END BETWEEN add_months(l_abs_start_date, -12) AND (l_abs_start_date-1)
AND PAA.ABS_INFORMATION1 = 'SC'
AND PAA.DATE_START IS NOT NULL
AND PAA.DATE_END IS NOT NULL;
procedure UPDATE_ABS_VALIDATION (P_ABS_INFORMATION_CATEGORY in varchar2
,P_ABSENCE_ATTENDANCE_ID in NUMBER
,P_EFFECTIVE_DATE in DATE
,P_ABS_INFORMATION1 in VARCHAR2
,P_ABS_INFORMATION2 in VARCHAR2
,P_ABS_INFORMATION3 in VARCHAR2
,P_ABS_INFORMATION5 in VARCHAR2
,P_ABS_INFORMATION6 in VARCHAR2
,P_ABS_INFORMATION9 in VARCHAR2 --#8510300
,P_ABS_INFORMATION15 in VARCHAR2
,P_ABS_INFORMATION16 in VARCHAR2
,P_DATE_START in DATE
,P_DATE_END in DATE
,P_DATE_PROJECTED_START in DATE
,P_DATE_PROJECTED_END in DATE
,P_ABSENCE_DAYS in NUMBER --#8510300
,P_ABS_ATTENDANCE_REASON_ID in NUMBER) is
-- Get the person Id
CURSOR get_person_id (p_abs_attendance_id in NUMBER) is
SELECT person_id
FROM PER_ABSENCE_ATTENDANCES
WHERE ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
END UPDATE_ABS_VALIDATION;