[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT substr(hoi4.ORG_INFORMATION1,4,2),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 = p_assignment_id
AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
AND p_effective_date 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_HOLIDAY_YEAR_DEFN' 'SE_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;*/
SELECT aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
SELECT hoi4.org_information1
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_effective_date 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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT DISTINCT eev1.screen_entry_value attendance_type_id
FROM per_all_assignments_f asg1
--,per_all_assignments_f asg2
-- ,per_all_people_f per
, pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date
AND asg1.effective_end_date
--AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
-- AND per.person_id = asg1.person_id
-- AND asg2.person_id = per.person_id
-- AND asg2.primary_flag = 'Y'
AND et.element_name = 'Absence Details'
AND et.legislation_code = 'SE'
--OR et.business_group_id=3261 ) --checking for the business group, it should be removed
AND iv1.element_type_id = et.element_type_id
AND iv1.NAME = 'Absence Category' --l_inp_val_name
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND ee.effective_start_date >= l_earning_start_date
AND ee.effective_end_date <= l_earning_end_date
AND eev1.effective_start_date >= l_earning_start_date
AND eev1.effective_end_date <= l_earning_end_date;
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 information2 generate
,information3 max_days
FROM per_absence_attendance_types
WHERE absence_attendance_type_id = csr_v_absence_type_id;
SELECT SUBSTR (hoi4.org_information1, 4, 2)
,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 = p_assignment_id
AND asg.soft_coding_keyflex_id =
scl.soft_coding_keyflex_id
AND p_effective_date 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_HOLIDAY_YEAR_DEFN'
AND hoi4.org_information1 IS NOT NULL;
SELECT min(EFFECTIVE_START_DATE) FROM
per_all_assignments_f
WHERE assignment_id=p_assignment_id;*/
SELECT papf.business_group_id
,papf.person_id
INTO l_business_group_id
,l_person_id
FROM per_all_assignments_f paaf
,per_all_people_f papf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT SUBSTR (hoi4.org_information1, 4, 2)
,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 = p_assignment_id
AND asg.soft_coding_keyflex_id =
scl.soft_coding_keyflex_id
AND p_effective_date 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_HOLIDAY_YEAR_DEFN'
AND hoi4.org_information1 IS NOT NULL;
SELECT MIN (effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT papf.business_group_id
INTO l_business_group_id
FROM per_all_assignments_f paaf
,per_all_people_f papf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT substr(hoi4.ORG_INFORMATION1,4,2),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 = p_assignment_id
AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
AND p_effective_date 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_HOLIDAY_YEAR_DEFN' 'SE_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;*/
SELECT aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
SELECT hoi4.org_information1
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_effective_date 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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT DISTINCT eev1.screen_entry_value attendance_category_id
FROM per_all_assignments_f asg1
--,per_all_assignments_f asg2
-- ,per_all_people_f per
, pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date
AND asg1.effective_end_date
--AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
-- AND per.person_id = asg1.person_id
-- AND asg2.person_id = per.person_id
-- AND asg2.primary_flag = 'Y'
--AND et.element_name = 'Absence Details'
AND et.legislation_code = 'SE'
--OR et.business_group_id=3261 ) --checking for the business group, it should be removed
AND iv1.element_type_id = et.element_type_id
AND iv1.NAME = 'Absence Category' --l_inp_val_name
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND ee.effective_start_date <= p_earning_end_date
AND ee.effective_end_date >= p_earning_start_date
AND eev1.effective_start_date <= p_earning_end_date
AND eev1.effective_end_date >= p_earning_start_date
AND et.element_name NOT IN
('Advance Holiday Details', 'Advance Holiday Pay');
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 INFORMATION2 Generate
,INFORMATION3 Max_Days
FROM PER_ABSENCE_ATTENDANCE_TYPES
WHERE ABSENCE_ATTENDANCE_TYPE_ID=csr_v_absence_type_id;*/
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_effective_date 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 = l_attendance_category_id;
SELECT MIN (effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT papf.business_group_id
,papf.person_id
,segment9
INTO l_business_group_id
,l_person_id
,l_working_perc
FROM per_all_assignments_f paaf
,per_all_people_f papf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id --15381
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT NVL (SUM (peevf2.screen_entry_value), 0)
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_input_values_f pivf1
,pay_input_values_f pivf2
WHERE paaf.assignment_id = p_assignment_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND et.element_name = 'Absence Details'
AND et.legislation_code = 'SE'
AND ee.assignment_id = paaf.assignment_id
AND ee.element_type_id = et.element_type_id
AND ee.effective_start_date >= p_payroll_start_date
AND ee.effective_end_date <= p_payroll_end_date
AND ee.element_entry_id = peevf1.element_entry_id
AND pivf1.element_type_id = et.element_type_id
AND pivf1.NAME = 'Absence Category'
AND peevf1.input_value_id = pivf1.input_value_id
AND peevf1.screen_entry_value = 'V'
AND ee.element_entry_id = peevf2.element_entry_id
AND pivf2.element_type_id = et.element_type_id
AND pivf2.NAME = 'Days'
AND peevf2.input_value_id = pivf2.input_value_id
AND p_payroll_start_date BETWEEN et.effective_start_date
AND et.effective_end_date
AND p_payroll_end_date BETWEEN et.effective_start_date
AND et.effective_end_date
--AND peevf1.effective_start_date >= to_date('01-jan-2000')
--AND peevf1.effective_end_date <= to_date('31-jan-2000')
--AND peevf2.effective_start_date >= to_date('01-jan-2000')
--AND peevf2.effective_end_date <= to_date('31-jan-2000')
;
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 aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
SELECT hoi4.org_information1
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_effective_date 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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT papf.business_group_id
,papf.person_id
INTO l_business_group_id
,l_person_id
FROM per_all_assignments_f paaf
,per_all_people_f papf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id --15381
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT 1
FROM per_all_assignments_f asg
,pay_element_links_f el
,pay_element_types_f et
,pay_element_entries_f ee
WHERE asg.assignment_id = p_assignment_id
AND et.element_name = p_element_name
AND et.legislation_code = 'SE'
AND el.business_group_id = asg.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg.assignment_id
AND ee.element_link_id = el.element_link_id
AND p_effective_date BETWEEN ee.effective_start_date
AND ee.effective_end_date
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND p_effective_date BETWEEN et.effective_start_date
AND et.effective_end_date
AND p_effective_date BETWEEN el.effective_start_date
AND el.effective_end_date;
SELECT prd_information1
,prd_information3
,prd_information4
,prd_information6
,prd_information8
,prd_information9
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND p_date_earned BETWEEN start_date AND end_date;
SELECT aei_information3
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
SELECT pei_information3
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
SELECT 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_effective_date 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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
SELECT hoi4.org_information1
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_effective_date 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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT papf.business_group_id
,papf.person_id
INTO l_business_group_id
,l_person_id
FROM per_all_assignments_f paaf
,per_all_people_f papf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id --15381
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT SUBSTR (hoi2.org_information1, 4, 2)
,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 --3134
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 prd_information4
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND p_effective_date BETWEEN start_date AND end_date;
SELECT SUBSTR (hoi2.org_information1, 4, 2)
,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 --3134
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 NVL (hourly_salaried_code, '##') hsc
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id_id
AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
SELECT prd_information1
,prd_information3
,prd_information4
,prd_information5
,prd_information7
,prd_information8
FROM per_time_periods
WHERE payroll_id = p_payroll_id
AND p_date_earned BETWEEN start_date AND end_date;
FUNCTION update_entitlement_ran (p_tax_unit_id IN NUMBER)
RETURN NUMBER
IS
BEGIN
UPDATE hr_organization_information
SET org_information5 = 'Y'
WHERE org_information_id =
(SELECT hoi2.org_information_id
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 --3134
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);
END update_entitlement_ran;
SELECT SUBSTR (hoi2.org_information1, 4, 2)
,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 MIN (effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT paaf.effective_start_date effective_start_date
FROM per_all_assignments_f paaf
WHERE paaf.business_group_id = p_business_group_id
AND paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = 3;
SELECT DISTINCT eev1.screen_entry_value attendance_category_id
FROM per_all_assignments_f asg1
--,per_all_assignments_f asg2
-- ,per_all_people_f per
, pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_date_earned BETWEEN asg1.effective_start_date
AND asg1.effective_end_date
--AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
-- AND per.person_id = asg1.person_id
-- AND asg2.person_id = per.person_id
-- AND asg2.primary_flag = 'Y'
--AND et.element_name = 'Absence Details'
AND et.legislation_code = 'SE'
--OR et.business_group_id=3261 ) --checking for the business group, it should be removed
AND iv1.element_type_id = et.element_type_id
AND iv1.NAME = 'Absence Category' --l_inp_val_name
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND ee.effective_start_date > p_earn_end_date
AND ee.effective_end_date <= p_termination_date
AND eev1.effective_start_date > p_earn_end_date
AND eev1.effective_end_date <= p_termination_date;
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 = l_attendance_category_id;
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 aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
SELECT hoi4.org_information1
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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT papf.business_group_id
,papf.person_id
,segment9
INTO l_business_group_id
,l_person_id
,l_working_perc
FROM per_all_assignments_f paaf
,per_all_people_f papf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id --15381
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_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 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 = 'S';
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 aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
SELECT hoi4.org_information1
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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT global_value
FROM ff_globals_f fgf
WHERE csr_v_effective_date BETWEEN effective_start_date
AND effective_end_date
AND GLOBAL_NAME = 'SE_ADVANCE_HOLIDAY_YEAR_LIMIT';
SELECT MIN (effective_start_date)
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = csr_v_assignment_id;
SELECT SUBSTR (hoi4.org_information1, 4, 2)
,SUBSTR (hoi4.org_information2, 4, 2)
FROM hr_organization_units o1
,hr_organization_information hoi3
,hr_organization_information hoi4
WHERE o1.business_group_id = l_business_group_id
AND o1.organization_id = hoi3.organization_id
AND hoi3.organization_id = p_tax_unit_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;
SELECT MIN (effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT payroll_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT MIN (start_date)
FROM per_time_periods
WHERE payroll_id = v_payroll_id AND start_date >= v_date;
SELECT aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
SELECT hoi4.org_information1
FROM hr_organization_units o1
,hr_organization_information hoi3
,hr_organization_information hoi4
WHERE o1.business_group_id = l_business_group_id
AND o1.organization_id = hoi3.organization_id
AND hoi3.organization_id = p_tax_unit_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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT papf.business_group_id
,papf.person_id
FROM per_all_assignments_f paaf
,per_all_people_f papf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id --15381
--AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT aei_information1
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND information_type = 'SE_ASSIGN_HOLIDAY_PAY_DETAILS';
SELECT pei_information1
FROM per_people_extra_info
WHERE person_id = l_person_id
AND information_type = 'SE_PERSON_HOLIDAY_PAY_DETAILS';
SELECT hoi4.org_information1
FROM hr_organization_units o1
,hr_organization_information hoi3
,hr_organization_information hoi4
WHERE o1.business_group_id = l_business_group_id
AND o1.organization_id = hoi3.organization_id
AND hoi3.organization_id = p_tax_unit_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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT DISTINCT eev1.screen_entry_value attendance_category_id
FROM per_all_assignments_f asg1
,pay_element_links_f el
,pay_element_types_f et
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE asg1.assignment_id = p_assignment_id
AND p_effective_date BETWEEN asg1.effective_start_date
AND asg1.effective_end_date
AND et.legislation_code = 'SE'
AND iv1.element_type_id = et.element_type_id
AND iv1.NAME = 'Absence Category' --l_inp_val_name
AND el.business_group_id = asg1.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg1.assignment_id
AND ee.element_link_id = el.element_link_id
AND eev1.element_entry_id = ee.element_entry_id
AND eev1.input_value_id = iv1.input_value_id
AND ee.effective_start_date <= p_cy_end_date
AND ee.effective_end_date >= p_cy_start_date
AND eev1.effective_start_date <= p_cy_end_date
AND eev1.effective_end_date >= p_cy_start_date
AND et.element_name NOT IN
('Advance Holiday Details', 'Advance Holiday Pay');
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 hoi4.org_information2
,hoi4.org_information3
FROM hr_organization_units o1
,hr_organization_information hoi3
,hr_organization_information hoi4
WHERE o1.business_group_id = l_business_group_id
AND hoi3.organization_id = o1.organization_id
AND hoi3.organization_id = p_tax_unit_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 = l_attendance_category_id;
SELECT MIN (effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT papf.business_group_id
,papf.person_id
,segment9
INTO l_business_group_id
,l_person_id
,l_working_perc
FROM per_all_assignments_f paaf
,per_all_people_f papf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT SUBSTR (hoi2.org_information1, 4, 2)
,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 --3134
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 employee_category
FROM per_all_assignments_f
WHERE assignment_id = p_asg_id
AND csr_v_effective_date BETWEEN effective_start_date
AND effective_end_date;
SELECT hoi4.org_information6
FROM hr_organization_units o1
,hr_organization_information hoi3
,hr_organization_information hoi4
WHERE o1.business_group_id = p_business_group_id
AND o1.organization_id = hoi3.organization_id
AND hoi3.organization_id = p_tax_unit_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;
SELECT MIN (effective_start_date)
INTO l_return
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT nvl(TRUNC
(fnd_number.canonical_to_number (segment13)),l_full_time)
/* change this to the field which we are going to add for part time employee */
FROM per_all_assignments_f paaf
,
-- per_all_people_f papf,
hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = csr_v_assignment_id --15381
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
--AND papf.person_id=paaf.person_id
AND csr_v_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT segment12
FROM per_all_assignments_f paaf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = csr_v_assignment_id
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND csr_v_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT r.row_low_range_or_name
FROM pay_user_rows_f r
,pay_user_tables t
WHERE r.legislation_code IS NULL
AND t.legislation_code = 'SE'
AND UPPER (t.user_table_name) = UPPER ('SE_HOLIDAY_PAY_AGREEMENT')
AND t.user_table_id = r.user_table_id
AND r.business_group_id = p_business_group_id
AND r.user_row_id = csr_v_row_id
AND csr_v_effective_date BETWEEN r.effective_start_date
AND r.effective_end_date;
SELECT SUBSTR (hoi4.org_information1, 4, 2)
,SUBSTR (hoi4.org_information2, 4, 2)
FROM hr_organization_units o1
,hr_organization_information hoi3
,hr_organization_information hoi4
WHERE o1.business_group_id = l_business_group_id
AND o1.organization_id = hoi3.organization_id
AND hoi3.organization_id = p_tax_unit_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;
SELECT paaf.business_group_id,
paaf.person_id,
segment9
FROM per_all_assignments_f paaf,
hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id --15381
AND paaf.soft_coding_keyflex_id=hsck.soft_coding_keyflex_id
AND paaf.effective_start_date <= l_ey_end_date
AND paaf.effective_end_date >= l_ey_start_date;
SELECT ROUND (SUM (perc * days) / sum(days)
,2
)
FROM (SELECT
-- paaf.business_group_id,
-- paaf.person_id,
-- greatest(paaf.effective_start_date,'01-Apr-2000'),
-- least(paaf.effective_end_date,'31-Mar-2001'),
fnd_number.canonical_to_number (segment9) perc --segment9 perc --Existing bug fixed as part of 9747212
, LEAST (paaf.effective_end_date, csr_v_ey_end)
- GREATEST (paaf.effective_start_date
,csr_v_ey_start)
+ 1 "DAYS"
FROM per_all_assignments_f paaf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id =
hsck.soft_coding_keyflex_id
AND paaf.effective_start_date <= csr_v_ey_end
AND paaf.effective_end_date >= csr_v_ey_start);
SELECT MIN (effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT TRUNC (MONTHS_BETWEEN (p_effective_date, papf.date_of_birth))
FROM per_all_assignments_f paaf
,per_all_people_f papf
WHERE paaf.assignment_id = p_assignment_id
AND papf.person_id = paaf.person_id
AND p_effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND p_effective_date BETWEEN papf.effective_start_date
AND papf.effective_end_date;
SELECT SUM (end_date - start_date)
FROM per_previous_job_usages
WHERE assignment_id = p_assignment_id;
SELECT hoi4.org_information2
FROM hr_organization_units o1
,hr_organization_information hoi3
,hr_organization_information hoi4
WHERE o1.business_group_id = p_business_group_id
AND o1.organization_id = hoi3.organization_id
AND hoi3.organization_id = p_tax_unit_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_LE_HOLIDAY_PAY_DETAILS'
AND hoi4.org_information1 IS NOT NULL;
SELECT FLOOR (csr_end_date - csr_start_date) + 1
FROM DUAL;
SELECT REPLACE (TRIM (l_time), ':', '.')
FROM DUAL;
SELECT ROUND (SUM (perc * in_hours) / 100, 2)
FROM (SELECT segment9 perc
, LEAST (paaf.effective_end_date, csr_v_ey_end)
- GREATEST (paaf.effective_start_date, csr_v_ey_start)
+ 1 "DAYS"
,normal_hours
,frequency
,segment13 days_in_week
,CASE
WHEN frequency = 'D'
THEN ( LEAST (paaf.effective_end_date
,csr_v_ey_end
)
- GREATEST (paaf.effective_start_date
,csr_v_ey_start
)
+ 1
)
* normal_hours
WHEN frequency = 'W'
THEN ( LEAST (paaf.effective_end_date
,csr_v_ey_end
)
- GREATEST (paaf.effective_start_date
,csr_v_ey_start
)
+ 1
)
* (normal_hours / segment13)
WHEN frequency = 'M'
THEN ( LEAST (paaf.effective_end_date
,csr_v_ey_end
)
- GREATEST (paaf.effective_start_date
,csr_v_ey_start
)
+ 1
)
* ( (normal_hours * 12)
/ ( ( (csr_v_ey_end)
- (csr_v_ey_start)
)
+ 1
)
)
END "IN_HOURS"
FROM per_all_assignments_f paaf
,hr_soft_coding_keyflex hsck
WHERE paaf.assignment_id = p_assignment_id
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND paaf.effective_start_date <= csr_v_ey_end
AND paaf.effective_end_date >= csr_v_ey_start);
SELECT SUBSTR (hoi2.org_information1, 4, 2)
,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 --3134
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 MIN (effective_start_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;