[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_HOURLY_RATE_DETAILS'
AND HOI4.org_information1 IS NOT NULL;
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_HOURLY_RATE_DETAILS'
AND HOI4.org_information2 IS NOT NULL;
SELECT PER_INFORMATION6 AS Hourly_Rate --Hourly/Salaried
FROM PER_ALL_PEOPLE_F PER
WHERE PER.PERSON_ID = l_person_id
AND P_EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE;
SELECT PER_INFORMATION7 AS Salary_Rate --Hourly/Salaried
FROM PER_ALL_PEOPLE_F PER
WHERE PER.PERSON_ID = l_person_id
AND P_EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE;
SELECT PAA.ABS_INFORMATION1 AS Hourly_Rate --Daily Rate Calculation
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID =l_abs_attendance_id;
SELECT PAA.ABS_INFORMATION2 AS Salary_Rate --Daily Rate Calculation
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID =l_abs_attendance_id;
SELECT eev1.screen_entry_value screen_entry_value
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 = l_asg_id
AND l_eff_dt BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND l_eff_dt 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 asg1.assignment_id=asg2.assignment_id
AND et.element_name = 'Sickness 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 = l_inp_val_name
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.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_start_dt
AND ee.effective_end_date <= l_end_dt
AND eev1.effective_start_date <= l_start_dt
AND eev1.effective_end_date >= l_end_dt
AND ROWNUM < 2 ;
SELECT papf.person_id
,papf.business_group_id
,segment9
,normal_hours
,frequency
,hourly_salaried_code
,employee_category
INTO l_person_id
,l_business_group_id
,l_working_percentage
,l_normal_hours
,l_frequency
,l_hour_sal
,l_employee_category
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 paa.absence_attendance_id
,paa.date_start
,nvl(paa.date_end,p_abs_end_date) date_end
,paa.time_start
,paa.time_end
,DECODE(paa.date_start, paa.date_end, 1, (paa.date_end-paa.date_start)+1) AS days_diff
FROM per_absence_attendances paa,
per_absence_attendance_types pat
WHERE paa.person_id = l_person_id
AND ((paa.date_start between l_st_date and p_abs_end_date)
OR (paa.date_end between l_st_date and p_abs_end_date))
AND paa.date_start IS NOT NULL /*AND paa.date_end IS NOT NULL*/
/*AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND pat.absence_category IN ('S')
ORDER BY paa.date_end desc;*/
SELECT paa.absence_attendance_id
,paa.date_start
--,nvl(paa.date_end,'31-jan-2000') date_end
,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
,paa.time_start
,paa.time_end
,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
(least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
FROM per_absence_attendances paa,
per_absence_attendance_types pat
WHERE paa.person_id = l_person_id
/*AND paa.date_start >=p_abs_start_date
AND nvl(paa.date_end,p_abs_end_date)<=p_abs_end_date*/
AND ((paa.date_start between l_st_date and p_abs_end_date)
OR (paa.date_end between l_st_date and p_abs_end_date))
/*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND pat.absence_category IN ('S')
AND paa.absence_attendance_id IN(
-- ORDER BY paa.date_end ;
SELECT eev1.screen_entry_value screen_entry_value
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 --34040 --l_asg_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 asg1.assignment_id=asg2.assignment_id
AND et.element_name = 'Sickness 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 = 'CREATOR_ID'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.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_abs_start_date
AND ee.effective_end_date <= p_abs_end_date
AND eev1.effective_start_date >= p_abs_start_date
AND eev1.effective_end_date <= p_abs_end_date*/
) ORDER BY paa.date_end desc ;
SELECT papf.person_id ,
hourly_salaried_code
INTO l_person_id ,
p_asg_hour_sal
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 paa.absence_attendance_id
,paa.date_start
,nvl(paa.date_end,p_abs_end_date) date_end
,paa.time_start
,paa.time_end
,DECODE(paa.date_start, paa.date_end, 1, (paa.date_end-paa.date_start)+1) AS days_diff
FROM per_absence_attendances paa,
per_absence_attendance_types pat
WHERE paa.person_id = l_person_id
AND paa.date_start >=p_abs_start_date
AND nvl(paa.date_end,p_abs_end_date)<=p_abs_end_date
AND paa.date_start IS NOT NULL /*AND paa.date_end IS NOT NULL*/
/*AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND pat.absence_category IN ('S')
ORDER BY paa.date_end ;*/
SELECT paa.absence_attendance_id
,paa.date_start
--,nvl(paa.date_end,'31-jan-2000') date_end
,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)) date_end
,paa.time_start
,paa.time_end
,DECODE(paa.date_start,least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date)), 1,
(least(nvl(paa.date_end,p_abs_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=l_person_id),p_abs_end_date))-paa.date_start)+1) AS days_diff
FROM per_absence_attendances paa,
per_absence_attendance_types pat
WHERE paa.person_id = l_person_id
AND paa.date_start >=p_abs_start_date
AND paa.date_start<=p_abs_end_date
AND least(nvl(paa.date_end,p_abs_end_date),p_abs_end_date)<=p_abs_end_date
/*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND pat.absence_category IN ('S')
/* Fix for bug 5383707 */
AND paa.absence_attendance_id IN(
-- ORDER BY paa.date_end ;
SELECT eev1.screen_entry_value screen_entry_value
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 --34040 --l_asg_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 asg1.assignment_id=asg2.assignment_id
AND et.element_name = 'Sickness 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 = 'CREATOR_ID'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.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_abs_start_date
AND ee.effective_end_date <= p_abs_end_date
AND eev1.effective_start_date >= p_abs_start_date
AND eev1.effective_end_date <= p_abs_end_date*/
) ORDER BY paa.date_end /*desc*/ ; /*Bug Fix 5649509*/
SELECT eev1.screen_entry_value screen_entry_value
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 = l_asg_id
AND l_eff_dt BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND l_eff_dt 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 = 'Sickness 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 = l_inp_val_name
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.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_start_dt
AND ee.effective_end_date = l_end_dt
AND eev1.effective_start_date = l_start_dt
AND eev1.effective_end_date = l_end_dt ;
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 SUM(RESULT_VALUE)--prrv1.* ,paa.assignment_id
/* prrv2.result_value fourteenth_date,
prrv3.result_value end_date*/
/*FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv1,
pay_input_values_f pivf,
pay_element_types_f petf
/* pay_run_result_values prrv2,
pay_run_result_values prrv3*/
/*WHERE ppa.effective_date BETWEEN p_group_start_date --'01-jun-1999' --p_report_start_date
/* AND p_group_end_date /*'01-jun-2000' */--p_report_end_date
/* AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id =p_assignment_id --21035 --p_assignment_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = petf.element_type_id --62358 -- p_element_type_id
AND petf.element_name=p_element_name --'Sick Pay 1 to 14 days'
AND petf.element_type_id=pivf.element_type_id
AND pivf.element_type_id=prr.element_type_id
AND prr.run_result_id = prrv1.run_result_id
AND prrv1.input_value_id =pivf.input_value_id --139722 --p_input_value_id;
SELECT SUM(prrv2.RESULT_VALUE) /*fnd_date.canonical_to_date(prrv1.RESULT_VALUE),prrv2.RESULT_VALUE,
ADD_MONTHS('15-nov-2000',-12), '15-nov-2000' --*, prrv1.result_value*/
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv1,
pay_run_result_values prrv2--,
-- pay_run_result_values prrv3
WHERE ppa.effective_date BETWEEN p_start_date --ADD_MONTHS('15-nov-2000',-12)
AND p_end_date --'15-nov-2000'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id --22145 -- p_assignment_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = p_element_type_id--62358 --p_element_type_id
AND prr.run_result_id = prrv1.run_result_id
AND prrv1.input_value_id = p_start_date_iv --139718 --p_start_date_iv
AND prr.run_result_id = prrv2.run_result_id
AND prrv2.input_value_id = p_waiting_day_iv --139722 --p_full_day_iv
AND fnd_date.canonical_to_date(prrv1.result_value) BETWEEN p_start_date --ADD_MONTHS('15-nov-2000',-12)
AND p_end_date ; --'15-nov-2000'
SELECT element_type_id FROM
pay_element_types_f
WHERE element_name=p_element_name
AND legislation_code='SE';
SELECT input_value_id FROM
pay_input_values_f
WHERE name=p_input_value
AND element_type_id=p_element_type_id;
SELECT nvl(SUM(prrv1.RESULT_VALUE),0)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv1--,
--pay_run_result_values prrv2
WHERE ppa.effective_date BETWEEN csr_v_start_date --ADD_MONTHS('15-nov-2000',-12)
AND csr_v_end_date --'15-nov-2000'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = csr_v_assignment_id --22145 -- p_assignment_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = csr_v_element_type_id--62358 --p_element_type_id
AND prr.run_result_id = prrv1.run_result_id
AND prrv1.input_value_id = csr_v_input_type_id1; --139718 --p_start_date_iv
SELECT nvl(global_value,0) FROM ff_globals_f WHERE --ROWNUM<3
legislation_code='SE'
AND GLOBAL_NAME=csr_v_global_name --'SE_BASIC_AMOUNT'
AND csr_v_effective_date /*'28-feb-2007'*/ BETWEEN effective_start_date
AND effective_end_date;
SELECT greatest(fnd_date.canonical_to_date(peevf1.SCREEN_ENTRY_VALUE),csr_v_start_date) date_start,
least(fnd_date.canonical_to_date(peevf2.SCREEN_ENTRY_VALUE),csr_v_end_date) date_end
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 = csr_v_assignment_id --38399 --p_assignment_id
AND csr_v_end_date /*p_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.effective_start_date <= csr_v_end_date--p_payroll_start_date
AND ee.effective_end_date >= csr_v_start_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 = 'Start Date'
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 = 'End Date'
AND peevf2.input_value_id = pivf2.input_value_id
AND csr_v_end_date /*p_payroll_start_date*/ BETWEEN et.effective_start_date AND et.effective_end_date
AND csr_v_end_date /*p_payroll_end_date*/ BETWEEN et.effective_start_date AND et.effective_end_date;
SELECT papf.person_id
,papf.business_group_id
,hourly_salaried_code
,segment9
,employee_category
,normal_hours
,time_normal_start
,time_normal_finish
,sex
INTO l_person_id
,l_business_group_id
,l_asg_hour_sal
,l_working_percentage
,l_employee_category
,l_normal_hours
,l_normal_time_start
,l_normal_time_end
,p_sex
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 CSSB.SCHEDULE_ID
from
CAC_SR_SCHDL_OBJECTS CSSO,
CAC_SR_SCHEDULES_B CSSB
where
CSSO.OBJECT_TYPE = b_object_type
AND CSSO.OBJECT_ID = b_object_id
AND CSSO.START_DATE_ACTIVE <= b_end_dt
AND CSSO.END_DATE_ACTIVE >= b_start_dt
AND CSSO.SCHEDULE_ID = CSSB.SCHEDULE_ID
AND CSSB.DELETED_DATE IS NULL
AND (CSSB.SCHEDULE_CATEGORY = b_schdl_cat
OR CSSB.SCHEDULE_ID IN (SELECT SCHEDULE_ID
FROM CAC_SR_PUBLISH_SCHEDULES
WHERE OBJECT_TYPE = b_object_type
AND OBJECT_ID = b_object_id
AND b_schdl_cat IS NULL
));
select CSTB.Template_Id,CSTB.TEMPLATE_LENGTH_DAYS from CAC_SR_SCHEDULES_B CSSB,
CAC_SR_TEMPLATES_B CSTB
where
CSSB.Template_Id=CSTB.Template_Id
and CSSB.Schedule_id=b_schedule_id; --10206
select CSRB.DURATION from
CAC_SR_TEMPLATES_B CSTB,
CAC_SR_TMPL_DETAILS CSTD,
CAC_SR_PERIODS_B CSRB
where
CSTB.Template_id =CSTD.Template_Id
and CSTD.Child_Period_Id=CSRB.Period_ID
and CSTB.Template_Id=b_template_id; --10284
SELECT nvl(global_value,0) FROM ff_globals_f WHERE --ROWNUM<3
legislation_code='SE'
AND GLOBAL_NAME=csr_v_global_name --'SE_BASIC_AMOUNT'
AND csr_v_effective_date /*'28-feb-2007'*/ BETWEEN effective_start_date
AND effective_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 hourly_salaried_code
INTO
l_asg_hour_sal
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 paa.absence_attendance_id
,greatest(paa.date_start,csr_v_fourteenth_date,csr_v_payroll_start_date) date_start
,least(nvl(paa.date_end,csr_v_payroll_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_payroll_end_date)) date_end
,paa.time_start
,paa.time_end
--,(date_end-date_start) date_diff
FROM per_absence_attendances paa,
per_absence_attendance_types pat
WHERE paa.person_id = csr_v_person_id --37732
AND paa.date_start <= csr_v_payroll_end_date--'31-mar-2000' --p_abs_end_date
AND least(nvl(paa.date_end,csr_v_payroll_end_date),csr_v_payroll_end_date)>=greatest(paa.date_start,csr_v_fourteenth_date,csr_v_payroll_start_date)--'01-mar-2000' --p_abs_end_date
/*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND pat.absence_category IN ('S')
/*Fix for Bug No. 5383707*/
AND paa.absence_attendance_id IN(
-- ORDER BY paa.date_end ;
SELECT eev1.screen_entry_value screen_entry_value
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 = csr_v_assignment_id --34040 --l_asg_id
AND p_payroll_end BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_payroll_end 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 asg1.assignment_id=asg2.assignment_id
AND et.element_name = 'Sickness 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 = 'CREATOR_ID'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.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_abs_start_date
AND ee.effective_end_date <= p_abs_end_date
AND eev1.effective_start_date >= p_abs_start_date
AND eev1.effective_end_date <= p_abs_end_date*/
) ORDER BY paa.date_end desc ;
SELECT paa.absence_attendance_id
,greatest(paa.date_start,csr_v_start_date) date_start
,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)) date_end
,paa.time_start
,paa.time_end
--,(date_end-date_start) date_diff
FROM per_absence_attendances paa,
per_absence_attendance_types pat
WHERE paa.person_id = csr_v_person_id --37732
AND paa.date_start <= csr_v_end_date--'31-mar-2000' --p_abs_end_date
AND least(nvl(paa.date_end,csr_v_end_date),csr_v_end_date)>=greatest(paa.date_start,csr_v_start_date)--'01-mar-2000' --p_abs_end_date
/*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND pat.absence_category IN ('S')
/*Fix for Bug No. 5383707*/
AND paa.absence_attendance_id IN(
-- ORDER BY paa.date_end ;
SELECT eev1.screen_entry_value screen_entry_value
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 --34040 --l_asg_id
AND p_pay_end_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
AND p_pay_end_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 asg1.assignment_id=asg2.assignment_id
AND et.element_name = 'Sickness 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 = 'CREATOR_ID'
AND el.business_group_id = per.business_group_id
AND el.element_type_id = et.element_type_id
AND ee.assignment_id = asg2.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_abs_start_date
AND ee.effective_end_date <= p_abs_end_date
AND eev1.effective_start_date >= p_abs_start_date
AND eev1.effective_end_date <= p_abs_end_date*/
) ORDER BY paa.date_end desc ;
/*SELECT paa.absence_attendance_id
,paa.date_start
--,nvl(paa.date_end,'31-jan-2000') date_end
,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)) date_end
,paa.time_start
,paa.time_end
,DECODE(paa.date_start,least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date)), 1,
(least(nvl(paa.date_end,csr_v_end_date),nvl((select actual_termination_date from per_periods_of_service where person_id=csr_v_person_id),csr_v_end_date))-paa.date_start)+1) AS days_diff
FROM per_absence_attendances paa,
per_absence_attendance_types pat
WHERE paa.person_id = csr_v_person_id
AND paa.date_start >=csr_v_start_date
AND paa.date_start<=csr_v_end_date
AND least(nvl(paa.date_end,csr_v_end_date),csr_v_end_date)<=csr_v_end_date
/*AND paa.date_start IS NOT NULL AND paa.date_end IS NOT NULL*/
/*AND paa.absence_attendance_type_id = pat.absence_attendance_type_id
AND pat.absence_category IN ('S')
ORDER BY paa.date_end ;*/