[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT abs.date_start
,abs.date_end
,abs.ABS_ATTENDANCE_REASON_ID
-- ,abs.TIME_START
-- ,abs.TIME_END
,abs.absence_Days
,abs.absence_Hours
INTO l_start_date
,l_end_date
,l_absent_reason
-- ,l_start_time
-- ,l_end_time
,l_days
,l_hours
FROM per_absence_attendances abs
WHERE abs.absence_attendance_id = p_absence_attendance_id;
-- To select absence category meaning by passing code
BEGIN
SELECT MEANING
INTO l_abs_cat_meaning
FROM hr_lookups
WHERE LOOKUP_TYPE = 'ABSENCE_CATEGORY'
AND ENABLED_FLAG = 'Y'
AND LOOKUP_CODE = p_absence_category;
SELECT fnd_number.canonical_to_number(PAA.ABS_INFORMATION3) INTO
l_absence_percentage
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID =p_absence_attendance_id;
select floor(csr_end_date-csr_start_date) from dual;
SELECT replace(trim(l_time),':','.') FROM dual;
CURSOR CSR_GET_ABSENCE is SELECT PAAT.NAME,-- PAAT.ABSENCE_ATTENDANCE_TYPE_ID,
PAAT.ABSENCE_CATEGORY,
PAA.date_start,
PAA.date_end,
PAA.absence_Days,
PAA.absence_Hours,
PAA.TIME_START,
PAA.TIME_END,
PAA.ABS_INFORMATION3
FROM 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 ;
CURSOR CSR_GET_ABCAT is SELECT MEANING
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'ABSENCE_CATEGORY'
AND ENABLED_FLAG = 'Y'
AND LOOKUP_CODE = p_absence_category;
-- To select absence category meaning by passing code
OPEN CSR_GET_ABCAT;
SELECT hoi2.org_information2,hoi2.org_information3
FROM HR_ORGANIZATION_UNITS hou
,HR_ORGANIZATION_INFORMATION hoi1
,HR_ORGANIZATION_INFORMATION hoi2
WHERE hou.organization_id =p_tax_unit_id
AND hoi1.organization_id = hou.organization_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.ORG_INFORMATION1='HR_LEGAL_EMPLOYER'
AND hoi2.ORG_INFORMATION_CONTEXT='SE_ABSENCE_CATEGORY_LIMIT'
AND hoi1.organization_id = hoi2.organization_id
AND hoi2.org_information1 IS NOT NULL
AND hoi2.org_information1=p_category_code;
/* SELECT hoi4.ORG_INFORMATION2,hoi4.ORG_INFORMATION3
FROM HR_ORGANIZATION_UNITS o1
,HR_ORGANIZATION_INFORMATION hoi1
,HR_ORGANIZATION_INFORMATION hoi2
,HR_ORGANIZATION_INFORMATION hoi3
,HR_ORGANIZATION_INFORMATION hoi4
,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
FROM PER_ALL_ASSIGNMENTS_F ASG
,HR_SOFT_CODING_KEYFLEX SCL
WHERE ASG.ASSIGNMENT_ID = p_assignment_id
AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
AND p_date_earned BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
WHERE o1.business_group_id = l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = X.ORG_ID
--AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi3.organization_id = hoi4.organization_id
AND hoi4.ORG_INFORMATION_CONTEXT='SE_ABSENCE_CATEGORY_LIMIT'
AND hoi4.org_information1 IS NOT NULL
AND hoi4.org_information1=p_category_code;
SELECT substr(hoi2.ORG_INFORMATION2,4,2)
FROM HR_ORGANIZATION_UNITS o1
,HR_ORGANIZATION_INFORMATION hoi1
,HR_ORGANIZATION_INFORMATION hoi2
WHERE
hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = p_tax_unit_id
AND hoi1.ORG_INFORMATION_CONTEXT='CLASS'
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.organization_id = hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='SE_HOLIDAY_YEAR_DEFN'
AND hoi2.org_information1 IS NOT NULL;
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'SE'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
SELECT fnd_date.canonical_to_date(peevf1.SCREEN_ENTRY_VALUE) start_date
,fnd_date.canonical_to_date(peevf2.SCREEN_ENTRY_VALUE) end_date
,fnd_number.canonical_to_number(peevf4.SCREEN_ENTRY_VALUE) Absence_Percentage
FROM per_all_assignments_f paaf
,pay_element_types_f et
,pay_element_entries_f ee
,pay_element_entry_values_f peevf1
,pay_element_entry_values_f peevf2
,pay_element_entry_values_f peevf3
,pay_element_entry_values_f peevf4
,pay_input_values_f pivf1
,pay_input_values_f pivf2
,pay_input_values_f pivf3
,pay_input_values_f pivf4
WHERE paaf.assignment_id=csr_v_assignment_id
AND csr_v_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND et.element_name= 'Sickness Details'
AND et.legislation_code= 'SE'
AND ee.assignment_id= paaf.assignment_id
AND ee.ELEMENT_TYPE_ID=et.ELEMENT_TYPE_ID
AND ee.ELEMENT_ENTRY_ID=peevf1.ELEMENT_ENTRY_ID
AND pivf1.element_type_id= et.element_type_id
AND pivf1.name='Start Date'
AND peevf1.input_value_id=pivf1.input_value_id
and ee.ELEMENT_ENTRY_ID=peevf2.ELEMENT_ENTRY_ID
AND pivf2.element_type_id=et.element_type_id
AND pivf2.name='End Date'
AND peevf2.input_value_id=pivf2.input_value_id
AND ee.ELEMENT_ENTRY_ID =peevf3.ELEMENT_ENTRY_ID
AND pivf3.element_type_id=et.element_type_id
AND pivf3.name='Absence Category'
AND peevf3.input_value_id=pivf3.input_value_id
AND peevf3.SCREEN_ENTRY_VALUE=csr_v_category_code
AND ee.ELEMENT_ENTRY_ID=peevf4.ELEMENT_ENTRY_ID
AND pivf4.element_type_id=et.element_type_id
AND pivf4.name='Absence Percentage'
AND peevf4.input_value_id=pivf4.input_value_id
AND ee.effective_start_date<=csr_v_effective_date
AND ee.effective_end_date>=csr_v_earn_start_date
AND peevf1.effective_start_date<=csr_v_effective_date
AND peevf1.effective_end_date>=csr_v_earn_start_date
AND peevf2.effective_start_date<=csr_v_effective_date
AND peevf2.effective_end_date>=csr_v_earn_start_date
AND peevf3.effective_start_date<=csr_v_effective_date
AND peevf3.effective_end_date>=csr_v_earn_start_date
AND peevf4.effective_start_date<=csr_v_effective_date
AND peevf4.effective_end_date>=csr_v_earn_start_date
ORDER BY fnd_date.canonical_to_date(peevf1.SCREEN_ENTRY_VALUE);
SELECT substr(hoi4.ORG_INFORMATION2,4,2)
FROM HR_ORGANIZATION_UNITS o1
,HR_ORGANIZATION_INFORMATION hoi1
,HR_ORGANIZATION_INFORMATION hoi2
,HR_ORGANIZATION_INFORMATION hoi3
,HR_ORGANIZATION_INFORMATION hoi4
,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
FROM PER_ALL_ASSIGNMENTS_F ASG
,HR_SOFT_CODING_KEYFLEX SCL
WHERE ASG.ASSIGNMENT_ID = csr_v_assignment_id
AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
AND csr_v_effective_date BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
WHERE o1.business_group_id = csr_v_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = X.ORG_ID
AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
AND hoi2.organization_id = hoi3.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi3.organization_id = hoi4.organization_id
AND hoi4.ORG_INFORMATION_CONTEXT='SE_HOLIDAY_YEAR_DEFN'
AND hoi4.org_information1 IS NOT NULL;