[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
peef.element_entry_id
FROM
pay_element_entry_values_f peevf
,pay_input_values_f pivf
,pay_element_entries_f peef
WHERE
peevf.screen_entry_value = p_creator_id
AND pivf.input_value_id = peevf.input_value_id
AND pivf.NAME = 'CREATOR_ID'
AND pivf.legislation_code = 'DK'
AND peef.element_entry_id = peevf.element_entry_id
AND peef.assignment_id = p_assignment_id;
SELECT abs.date_start
,abs.date_end
,abs.ABS_ATTENDANCE_REASON_ID
,abs.TIME_START
,abs.TIME_END
,abs.abs_information1
,abs.abs_information2
INTO l_start_date
,l_end_date
,l_absent_reason
,l_start_time
,l_end_time
,l_intial_absence
,l_initial_abs_creator_id
FROM per_absence_attendances abs
WHERE abs.absence_attendance_id = p_absence_attendance_id;
SELECT Meaning
INTO p_input_value5
FROM FND_LOOKUP_VALUES_VL LKP
,PER_ABSENCE_ATTENDANCES PAA
,PER_ABS_ATTENDANCE_REASONS PAR
WHERE PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAR.ABSENCE_ATTENDANCE_TYPE_ID
AND PAA.ABSENCE_ATTENDANCE_ID = p_absence_attendance_id
AND PAR.ABS_ATTENDANCE_REASON_ID = l_absent_reason
AND LKP.lookup_type = 'ABSENCE_REASON'
AND LOOKUP_CODE = PAR.NAME
-- Bug No 4994835. Group by clause introduced to avoid duplicate records
GROUP BY LKP.Meaning;
SELECT Meaning
INTO p_input_value5
FROM FND_LOOKUP_VALUES_VL LKP
,PER_ABSENCE_ATTENDANCES PAA
,PER_ABS_ATTENDANCE_REASONS PAR
WHERE PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAR.ABSENCE_ATTENDANCE_TYPE_ID
AND PAA.ABSENCE_ATTENDANCE_ID = p_absence_attendance_id
AND PAR.ABS_ATTENDANCE_REASON_ID = l_absent_reason
AND LKP.lookup_type = 'ABSENCE_REASON'
AND LOOKUP_CODE = PAR.NAME
-- Bug No 4994835. Group by clause introduced to avoid duplicate records
GROUP BY LKP.Meaning;
SELECT ee.element_entry_id element_entry_id
, eev1.screen_entry_value screen_entry_value
, iv1.name
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 et.element_name = 'Maternity Override'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name in ('Start Date', 'End Date','Payment During Leave'
,'Pre Birth Duration Override','Post Birth Duration Override','Part Time Hours','Part Time Hours Frequency',
'Maternity Weeks Used','Shared Maternity Weeks Used','Holiday Accrual to Supress')
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 p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.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
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
ORDER BY ee.element_entry_id;
CURSOR csr_absence IS SELECT
fnd_date.canonical_to_date(abs_information4) expected_dob,
fnd_date.canonical_to_date(abs_information5) actual_dob,
abs_information8 pre_birth_duration,
abs_information9 post_birth_duration,
abs_information10 maternity_weeks_transfer,
abs_information6 holiday_accrual
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
CURSOR csr_asg_le IS SELECT
nvl(asg.normal_hours,hoi.org_information3) hours
,nvl(asg.frequency,hoi.org_information4) frequency
FROM
hr_organization_information hoi
,HR_SOFT_CODING_KEYFLEX SCL
,PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_ID = p_assignment_id
AND hoi.organization_id = scl.segment1
AND org_information_context = 'DK_EMPLOYMENT_DEFAULTS'
AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
AND fnd_date.canonical_to_date(p_date_earned) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE;
SELECT MAX(paaf.effective_end_date) effective_end_date
FROM per_all_assignments_f paaf
WHERE paaf.business_group_id = p_business_group_id
AND paaf.assignment_id = p_assignment_id
AND paaf.assignment_status_type_id = 1;
SELECT
ABS.abs_information7
,ABS.abs_information5
FROM per_absence_attendances ABS
WHERE ABS.absence_attendance_id = p_abs_attendance_id;
SELECT ee.element_entry_id element_entry_id
, eev1.screen_entry_value screen_entry_value
, iv1.name
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 et.element_name = 'Paternity Override'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name in ('Start Date', 'End Date','Duration Override Weeks'
,'Holiday Accrual to Supress')
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 p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.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
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
ORDER BY ee.element_entry_id;
CURSOR csr_absence IS SELECT
fnd_date.canonical_to_date(abs_information4) expected_dob,
fnd_date.canonical_to_date(abs_information5) actual_dob,
abs_information8 pre_adopt_duration,
abs_information9 post_adopt_duration,
abs_information10 adoption_weeks_transfer,
abs_information11 weeks_from_mother,
abs_information6 hol_accrual_elig
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT papf.sex FROM per_all_people_f papf,per_all_assignments_f paaf
WHERE
paaf.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND papf.person_id = paaf.person_id
AND p_date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date;
SELECT ee.element_entry_id element_entry_id
, eev1.screen_entry_value screen_entry_value
, iv1.name
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 et.element_name = 'Adoption Override'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name in ('Start Date', 'End Date','Payment During Leave'
,'Pre Adopt Duration Override','Post Adopt Duration Override','Adoption Weeks Used','Shared Adoption Weeks Used','Holiday Accrual to Supress')
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 p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.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
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
ORDER BY ee.element_entry_id;
CURSOR csr_absence IS SELECT
fnd_date.canonical_to_date(abs_information4) actual_dob,
abs_information7 duration_override,
abs_information10 parental_type,
abs_information5 holiday_accrual
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT ee.element_entry_id element_entry_id
, eev1.screen_entry_value screen_entry_value
, iv1.name
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 et.element_name = 'Parental Leave Override'
AND et.legislation_code = 'DK'
AND iv1.element_type_id = et.element_type_id
AND iv1.name in ('Start Date', 'End Date','Duration Override'
,'Shared Maternity Weeks Used','Shared Adoption Weeks Used','Holiday Accrual to Supress')
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 p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
AND p_effective_date BETWEEN iv1.effective_start_date AND iv1.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
AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
ORDER BY ee.element_entry_id;
CURSOR csr_absence IS SELECT
fnd_date.canonical_to_date(abs_information4) actual_dob,
fnd_number.canonical_to_number(abs_information7) part_time_hours,
abs_information8 part_time_hrs_freq,
abs_information5 holiday_accrual
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
CURSOR csr_asg_le IS SELECT
fnd_number.canonical_to_number(nvl(asg.normal_hours,hoi.org_information3)) hours
,nvl(asg.frequency,hoi.org_information4) frequency
,segment10 work_pattern
FROM
hr_organization_information hoi
,HR_SOFT_CODING_KEYFLEX SCL
,PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_ID = p_assignment_id
AND hoi.organization_id = scl.segment1
AND org_information_context = 'DK_EMPLOYMENT_DEFAULTS'
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;