[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT paa.absence_attendance_id
,paa.date_start
,paa.date_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 paa.date_end < p_abs_start_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','PTS')
ORDER BY paa.date_end desc ;
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 = 'Absence Detail'
AND et.legislation_code = 'NO'
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 result_value
FROM pay_element_types_f pet
, pay_element_entries_f pee
, pay_run_results prr
, pay_run_result_values prrv
, pay_input_values_f piv
WHERE pet.element_type_id = pee.element_type_id
AND pet.element_type_id = prr.element_type_id
AND pee.element_entry_id = prr.element_entry_id
AND prr.run_result_id = prrv.run_result_id
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = prrv.input_value_id
AND pet.element_name LIKE 'Absence Detail'
AND piv.NAME = p_input_name
AND pee.effective_start_date = stdate
AND pee.effective_end_date = enddate ;
SELECT DATE_EARNED
FROM PER_ALL_ASSIGNMENTS_F PAA
,PAY_PAYROLL_ACTIONS PPA
,PAY_ASSIGNMENT_ACTIONS PASG
WHERE PAA.PAYROLL_ID = PPA.PAYROLL_ID
AND PAA.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PAA.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PPA.PAYROLL_ACTION_ID = PASG.PAYROLL_ACTION_ID
AND PASG.SOURCE_ACTION_ID IS NOT NULL
AND P_EFFECTIVE_DATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND DATE_EARNED BETWEEN P_ST_DT AND P_EN_DT
GROUP BY DATE_EARNED
ORDER BY DATE_EARNED ASC;
SELECT START_DATE
,END_DATE
,fnd_number.canonical_to_number(PEM_INFORMATION1) AS PEM_INFORMATION1
FROM PER_PREVIOUS_EMPLOYERS
WHERE person_id = personid
AND end_date BETWEEN TO_DATE('01/01/'|| TO_CHAR(stdate,'yyyy'), 'mm/dd/yyyy' )
AND stdate
ORDER BY end_date DESC;
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
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 ); /* 5413738 */
SELECT paa.absence_attendance_id
,paa.date_start
,paa.date_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 paa.date_end < p_abs_start_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 ('CMS')
ORDER BY paa.date_end desc ;
SELECT
paa.date_start
FROM
PER_ABSENCE_ATTENDANCES paa
WHERE
PAA.ABSENCE_ATTENDANCE_ID = p_intial_abs_attend_id;
SELECT nvl(peef2.effective_start_date,peef1.effective_start_date)
FROM pay_element_entry_values_f peevf
,pay_input_values_f pivf
,pay_element_entries_f peef1
,pay_element_entries_f peef2
WHERE peevf.screen_entry_value = p_abs_attn_id
AND pivf.input_value_id = peevf.input_value_id
AND pivf.NAME = 'CREATOR_ID'
AND pivf.legislation_code = 'NO'
AND peef1.element_entry_id = peevf.element_entry_id
AND peef2.element_entry_id(+) = peef1.original_entry_id;
SELECT ptp.start_date
FROM per_all_assignmeNts_f paaf, PER_TIME_PERIODS ptp
WHERE paaf.assignment_id = p_assignment_id
AND ptp.payroll_id = paaf.payroll_id
AND p_initial_abs_start_date between ptp.start_date and ptp.end_date;
SELECT LOOKUP_CODE
INTO l_abs_category_code
FROM HR_LOOKUPS /* Bug fix 5263714 used hr_lookups instead of fnd_lookup_values
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'ABSENCE_CATEGORY'
AND ENABLED_FLAG = 'Y'
AND MEANING = p_absence_category;
SELECT PERSON_ID
,BUSINESS_GROUP_ID
,HOURLY_SALARIED_CODE
INTO p_person_id
,p_business_group_id
,l_asg_hour_sal
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_ID = p_assignment_id
AND p_effective_date BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE;
SELECT hoi4.ORG_INFORMATION1
,hoi4.ORG_INFORMATION2
,hoi4.ORG_INFORMATION3
,hoi4.ORG_INFORMATION4
,hoi4.ORG_INFORMATION5
,hoi4.ORG_INFORMATION6
,hoi4.ORG_INFORMATION7
,hoi4.ORG_INFORMATION8
INTO l_le_reimb_ss
,l_le_restrict_dr_ss
,l_le_restrict_empr_sl
,l_le_restrict_ss_sl
,l_le_hour_sal
,l_le_entitled_sc
,l_le_exempt_empr
,l_le_daily_rate
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 = p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = X.ORG_ID
AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='NO_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='NO_ABSENCE_PAYMENT_DETAILS';
SELECT PER_INFORMATION6 AS HOURLY_SALARIED
,PER_INFORMATION7 AS ENTITLE_SC
,PER_INFORMATION8 AS EXEMPT_EMPR
,PER_INFORMATION9 AS REIMB_SS
,PER_INFORMATION10 AS RESTRICT_DR_SS
,PER_INFORMATION11 AS RESTRICT_EMPR_SL
,PER_INFORMATION12 AS RESTRICT_SS_SL
,PER_INFORMATION14 AS DAILY_RATE /* knelli changed from PER_INFORMATION13*/
,START_DATE AS DATEOFJOIN
INTO l_hourly_salaried
,l_entitled_sc
,l_exempt_empr
,l_reimb_ss
,l_restrict_dr_ss
,l_restrict_empr_sl
,l_restrict_ss_sl
,l_per_daily_rate
,l_dateofjoin
FROM PER_ALL_PEOPLE_F PER
WHERE PER.PERSON_ID = p_person_id
AND P_EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE;
SELECT PAA.ABS_INFORMATION1 AS Cert_type
,to_date(PAA.ABS_INFORMATION2,'yyyy/mm/dd hh24:mi:ss') AS Cert_stdt
,to_date(PAA.ABS_INFORMATION3,'yyyy/mm/dd hh24:mi:ss') AS Cert_endt
,to_date(PAA.ABS_INFORMATION4,'yyyy/mm/dd hh24:mi:ss') AS Follow_due
,to_date(PAA.ABS_INFORMATION5,'yyyy/mm/dd hh24:mi:ss') AS Follow_created
,PAA.ABS_INFORMATION6 AS reimb_ss
,PAA.ABS_INFORMATION7 AS dailyrate_ss
,PAA.ABS_INFORMATION8 AS employer_6g
,PAA.ABS_INFORMATION9 AS socialsec_6g
,to_date(PAA.ABS_INFORMATION10,'yyyy/mm/dd hh24:mi:ss') AS Follow_sent
,PAA.ABS_INFORMATION11 AS Daily_rate
INTO l_cert_type
,l_cert_stdt
,l_cert_endt
,l_follow_due
,l_follow_created
,l_abs_reimb_ss
,l_abs_restrict_dr_ss
,l_abs_restrict_empr_sl
,l_abs_restrict_ss_sl
,l_follow_sent
,l_abs_daily_rate
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT PAA.ABS_INFORMATION1 AS Cert_type
,to_date(PAA.ABS_INFORMATION2,'yyyy/mm/dd hh24:mi:ss') AS Cert_stdt
,to_date(PAA.ABS_INFORMATION3,'yyyy/mm/dd hh24:mi:ss') AS Cert_endt
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION4) AS PT_Percent
,to_date(PAA.ABS_INFORMATION5,'yyyy/mm/dd hh24:mi:ss') AS Follow_due
,to_date(PAA.ABS_INFORMATION6,'yyyy/mm/dd hh24:mi:ss') AS Follow_created
,PAA.ABS_INFORMATION7 AS reimb_ss
,PAA.ABS_INFORMATION8 AS dailyrate_ss
,PAA.ABS_INFORMATION9 AS employer_6g
,PAA.ABS_INFORMATION10 AS socialsec_6g
,to_date(PAA.ABS_INFORMATION11,'yyyy/mm/dd hh24:mi:ss') AS Follow_sent
,PAA.ABS_INFORMATION12 AS Daily_rate
INTO l_cert_type
,l_cert_stdt
,l_cert_endt
,l_abs_pts_percent
,l_follow_due
,l_follow_created
,l_abs_reimb_ss
,l_abs_restrict_dr_ss
,l_abs_restrict_empr_sl
,l_abs_restrict_ss_sl
,l_follow_sent
,l_abs_daily_rate
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT SUM( PAA.DATE_END - PAA.DATE_START )
INTO l_abs_count
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id
AND PAA.DATE_END BETWEEN (p_abs_start_date - (p_abs_link_period+1)) AND (p_abs_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.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id
AND PAA.DATE_END BETWEEN add_months(p_abs_start_date, -12) AND (p_abs_start_date-1)
AND PAA.ABS_INFORMATION1 = 'SC'
AND PAA.DATE_START IS NOT NULL
AND PAA.DATE_END IS NOT NULL;
SELECT LKP.Meaning
INTO l_abs_reason
FROM FND_LOOKUP_VALUES LKP
,PER_ABSENCE_ATTENDANCES PAA
,PER_ABS_ATTENDANCE_REASONS PAR
WHERE PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAR.ABSENCE_ATTENDANCE_TYPE_ID
AND PAA.ABS_ATTENDANCE_REASON_ID = PAR.ABS_ATTENDANCE_REASON_ID
AND PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id
AND LKP.lookup_type = 'ABSENCE_REASON'
AND LOOKUP_CODE = PAR.NAME
GROUP BY LKP.Meaning;
SELECT SUM ( CASE WHEN PAA.DATE_END > l_dateofjoin_28 THEN
( l_dateofjoin_28 - PAA.DATE_START)
WHEN PAA.DATE_START = PAA.DATE_END THEN
1
ELSE (PAA.DATE_END - PAA.DATE_START) END ) AS DAYS
INTO l_abs_unauthor
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 <= l_dateofjoin_28
AND PAA.DATE_END >= l_dateofjoin
AND PAA.DATE_START IS NOT NULL
AND PAA.DATE_END IS NOT NULL
AND PAA.PERSON_ID = p_person_id;/
SELECT SUM ( CASE WHEN PAA.DATE_END > (p_abs_start_date-1) THEN
( (p_abs_start_date-1) - PAA.DATE_START) + 1
WHEN PAA.DATE_START = PAA.DATE_END THEN
1
ELSE (PAA.DATE_END - PAA.DATE_START) + 1 END ) AS DAYS
INTO l_abs_unauthor
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 <= (p_abs_start_date-1)
AND PAA.DATE_END >= l_dateofjoin
AND PAA.DATE_START IS NOT NULL
AND PAA.DATE_END IS NOT NULL
AND PAA.PERSON_ID = p_person_id;
SELECT COUNT(1)
INTO l_abs_count
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 IN ( 'CMS','PA','PTP','M','PTM','IE_AL' ,'PTA')
AND PAA.DATE_END BETWEEN (p_abs_start_date - (p_abs_min_gap+1)) AND p_abs_start_date
AND ( PAA.DATE_END - PAA.DATE_START ) > p_abs_link_period
AND PAA.DATE_START IS NOT NULL
AND PAA.DATE_END IS NOT NULL
AND PAA.PERSON_ID = p_person_id;
SELECT SUM( CASE WHEN date_start < (l_initial_abs_pay_stdt - p_abs_min_gap) THEN
( date_end - (l_initial_abs_pay_stdt - p_abs_min_gap) ) +1
WHEN date_end > (l_initial_abs_pay_stdt-1) THEN
( (l_initial_abs_pay_stdt-1) - date_start )+1
WHEN date_end = date_start THEN
1
ELSE (date_end - date_start) + 1 END ) AS Days_diff
INTO l_abs_worked_days
FROM per_absence_attendances
WHERE person_id = p_person_id
AND date_start < (l_initial_abs_pay_stdt-1)
AND date_end > (l_initial_abs_pay_stdt - p_abs_min_gap)
AND date_start IS NOT NULL
AND date_end IS NOT NULL ;
SELECT PAA.ABS_INFORMATION1 AS Cert_type
,to_date(PAA.ABS_INFORMATION2,'yyyy/mm/dd hh24:mi:ss') AS Cert_stdt
,to_date(PAA.ABS_INFORMATION3,'yyyy/mm/dd hh24:mi:ss') AS Cert_endt
,PAA.ABS_INFORMATION4 AS reimb_ss
,PAA.ABS_INFORMATION5 AS dailyrate_ss
,PAA.ABS_INFORMATION6 AS employer_6g
,PAA.ABS_INFORMATION7 AS socialsec_6g
,PAA.ABS_INFORMATION8 AS Daily_rate
INTO l_cert_type
,l_cert_stdt
,l_cert_endt
,l_abs_reimb_ss
,l_abs_restrict_dr_ss
,l_abs_restrict_empr_sl
,l_abs_restrict_ss_sl
,l_abs_daily_rate
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT SUM ( CASE WHEN PAA.DATE_END > l_dateofjoin_28 THEN
( l_dateofjoin_28 - PAA.DATE_START)
WHEN PAA.DATE_START = PAA.DATE_END THEN
1
ELSE (PAA.DATE_END - PAA.DATE_START) END ) AS DAYS
INTO l_abs_unauthor
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 <= l_dateofjoin_28
AND PAA.DATE_END >= l_dateofjoin
AND PAA.DATE_START IS NOT NULL
AND PAA.DATE_END IS NOT NULL
AND PAA.PERSON_ID = p_person_id;/
SELECT SUM ( CASE WHEN PAA.DATE_END > (p_abs_start_date-1) THEN
( (p_abs_start_date-1) - PAA.DATE_START) + 1
WHEN PAA.DATE_START = PAA.DATE_END THEN
1
ELSE (PAA.DATE_END - PAA.DATE_START) + 1 END ) AS DAYS
INTO l_abs_unauthor
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 <= (p_abs_start_date-1)
AND PAA.DATE_END >= l_dateofjoin
AND PAA.DATE_START IS NOT NULL
AND PAA.DATE_END IS NOT NULL
AND PAA.PERSON_ID = p_person_id;
SELECT COUNT(1)
INTO l_abs_count
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 IN ( 'S','PTS','PA','PTP','M','PTM','IE_AL','PTA' )
AND PAA.DATE_END BETWEEN (p_abs_start_date - (p_abs_min_gap+1)) AND p_abs_start_date
AND ( PAA.DATE_END - PAA.DATE_START ) > p_abs_link_period
AND PAA.DATE_START IS NOT NULL
AND PAA.DATE_END IS NOT NULL
AND PAA.PERSON_ID = p_person_id;
SELECT SUM( CASE WHEN date_start < (l_initial_abs_pay_stdt - p_abs_min_gap) THEN
( date_end - (l_initial_abs_pay_stdt - p_abs_min_gap) ) +1
WHEN date_end > (l_initial_abs_pay_stdt-1) THEN
( (l_initial_abs_pay_stdt-1) - date_start )+1
WHEN date_end = date_start THEN
1
ELSE (date_end - date_start) + 1 END ) AS Days_diff
INTO l_abs_worked_days
FROM per_absence_attendances
WHERE person_id = p_person_id
AND date_start < (l_initial_abs_pay_stdt-1)
AND date_end > (l_initial_abs_pay_stdt - p_abs_min_gap)
AND date_start IS NOT NULL
AND date_end IS NOT NULL ;
SELECT to_date(PAA.ABS_INFORMATION1,'yyyy/mm/dd hh24:mi:ss') --AS l_p_dob
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION2) --AS l_p_compensation_rate
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION3) --AS l_p_maternity_days
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION4) --AS l_p_pt_maternity_days
,PAA.ABS_INFORMATION5 --AS l_p_reimburse_from_ss
,PAA.ABS_INFORMATION6 --AS l_p_use_ss_daily_rate
,PAA.ABS_INFORMATION7 --AS l_p_reclaimable_pay_max_6g
,PAA.ABS_INFORMATION8 --AS l_p_hol_acc_ent
,PAA.ABS_INFORMATION9 --AS l_p_daily_rate_calc
,PAA.ABS_INFORMATION15 --AS intial_absence
,PAA.ABS_INFORMATION16 -- AS intial_abs_attend_id
INTO l_p_dob
,l_p_compensation_rate
,l_p_maternity_days
,l_p_pt_maternity_days
,l_p_reimburse_from_ss
,l_p_use_ss_daily_rate
,l_p_reclaimable_pay_max_6g
,l_p_hol_acc_ent
,l_p_daily_rate_calc
,l_initial_absence
,l_initial_abs_attend_id
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT SUM(DECODE(paa.date_start, paa.date_end, 1,
get_weekdays(greatest(p_pay_start_date, paa.date_start), paa.date_end, p_work_pattern) )) AS days_diff
INTO l_paternity_sum
FROM per_absence_attendances paa, per_absence_attendance_types pat
WHERE paa.person_id = p_person_id
AND paa.date_end BETWEEN p_pay_start_date AND p_pay_end_date
AND paa.date_end < p_abs_start_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 = 'M'
ORDER BY paa.date_end DESC ;
SELECT SUM( CASE WHEN date_start < (l_initial_abs_pay_stdt - p_abs_min_gap) THEN
( date_end - (l_initial_abs_pay_stdt - p_abs_min_gap) ) +1
WHEN date_end > (l_initial_abs_pay_stdt-1) THEN
( (l_initial_abs_pay_stdt-1) - date_start )+1
WHEN date_end = date_start THEN
1
ELSE (date_end - date_start) + 1 END ) AS Days_diff
INTO l_abs_worked_days
FROM per_absence_attendances
WHERE person_id = p_person_id
AND date_start < (l_initial_abs_pay_stdt-1)
AND date_end > (l_initial_abs_pay_stdt - p_abs_min_gap)
AND date_start IS NOT NULL
AND date_end IS NOT NULL ;
SELECT to_date(PAA.ABS_INFORMATION1,'yyyy/mm/dd hh24:mi:ss') --AS l_ptp_dob
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION2) --AS l_ptp_mat_compensation_rate
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION3) --AS l_ptp_paternity_percent
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION4) --AS l_ptp_days_spouse_mat_leave
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION5) --AS l_ptp_days_pt_maternity
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION6) --AS l_ptp_no_of_babies_born
,PAA.ABS_INFORMATION7 --AS l_ptp_reimburse_from_ss
,PAA.ABS_INFORMATION8 --AS l_ptp_use_ss_daily_rate
,PAA.ABS_INFORMATION9 --AS l_ptp_reclaimable_pay_max_6g
,PAA.ABS_INFORMATION10 --AS l_ptp_hol_acc_ent
,PAA.ABS_INFORMATION11 --AS l_ptp_daily_rate_calc
,PAA.ABS_INFORMATION15 --AS intial_absence
,PAA.ABS_INFORMATION16 -- AS intial_abs_attend_id
INTO l_ptp_dob
,l_ptp_mat_compensation_rate
,l_ptp_paternity_percent
,l_ptp_days_spouse_mat_leave
,l_ptp_days_pt_maternity
,l_ptp_no_of_babies_born
,l_ptp_reimburse_from_ss
,l_ptp_use_ss_daily_rate
,l_ptp_reclaimable_pay_max_6g
,l_ptp_hol_acc_ent
,l_ptp_daily_rate_calc
,l_initial_absence
,l_initial_abs_attend_id
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT SUM(DECODE(paa.date_start, paa.date_end, 1,
get_weekdays(greatest(p_pay_start_date, paa.date_start), paa.date_end, p_work_pattern) )) AS days_diff
INTO l_pt_paternity_sum
FROM per_absence_attendances paa, per_absence_attendance_types pat
WHERE paa.person_id = p_person_id
AND paa.date_end BETWEEN p_pay_start_date AND p_pay_end_date
AND paa.date_end < p_abs_start_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 = 'PTM'
ORDER BY paa.date_end DESC ;
SELECT SUM( CASE WHEN date_start < (l_initial_abs_pay_stdt - p_abs_min_gap) THEN
( date_end - (l_initial_abs_pay_stdt - p_abs_min_gap) ) +1
WHEN date_end > (l_initial_abs_pay_stdt-1) THEN
( (l_initial_abs_pay_stdt-1) - date_start )+1
WHEN date_end = date_start THEN
1
ELSE (date_end - date_start) + 1 END ) AS Days_diff
INTO l_abs_worked_days
FROM per_absence_attendances
WHERE person_id = p_person_id
AND date_start < (l_initial_abs_pay_stdt-1)
AND date_end > (l_initial_abs_pay_stdt - p_abs_min_gap)
AND date_start IS NOT NULL
AND date_end IS NOT NULL ;
SELECT to_date(PAA.ABS_INFORMATION1,'yyyy/mm/dd hh24:mi:ss') --AS l_m_expected_dob
,to_date(PAA.ABS_INFORMATION2,'yyyy/mm/dd hh24:mi:ss') --AS l_m_dob
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION3) --AS l_m_no_of_babies_born
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION4) --AS l_m_compensation_rate
,PAA.ABS_INFORMATION5 --AS l_m_spouse
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION6) --AS l_m_paternity_days
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION7) --AS l_m_pt_paternity_days
,PAA.ABS_INFORMATION8 --AS l_m_reimurse_from_ss
,PAA.ABS_INFORMATION9 --AS l_m_use_ss_daily_rate
,PAA.ABS_INFORMATION10 --AS l_m_reclaimable_pay_max_6g
,PAA.ABS_INFORMATION11 --AS l_m_hol_acc_ent
,PAA.ABS_INFORMATION12 --AS l_m_daily_rate_calc
,to_date(PAA.ABS_INFORMATION13,'yyyy/mm/dd hh24:mi:ss') --AS l_m_date_stillborn
,PAA.ABS_INFORMATION15 --AS intial_absence
,PAA.ABS_INFORMATION16 -- AS intial_abs_attend_id
INTO l_m_expected_dob
,l_m_dob
,l_m_no_of_babies_born
,l_m_compensation_rate
,l_m_spouse
,l_m_paternity_days
,l_m_pt_paternity_days
,l_m_reimurse_from_ss
,l_m_use_ss_daily_rate
,l_m_reclaimable_pay_max_6g
,l_m_hol_acc_ent
,l_m_daily_rate_calc
,l_m_date_stillborn
,l_initial_absence
,l_initial_abs_attend_id
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT SUM(DECODE(paa.date_start, paa.date_end, 1,
get_weekdays(greatest(p_pay_start_date, paa.date_start), paa.date_end, p_work_pattern) )) AS days_diff
INTO l_maternity_sum
FROM per_absence_attendances paa, per_absence_attendance_types pat
WHERE paa.person_id = p_person_id
AND paa.date_end BETWEEN p_pay_start_date AND p_pay_end_date
AND paa.date_end < p_abs_start_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 = 'M'
ORDER BY paa.date_end DESC ;
SELECT SUM( CASE WHEN date_start < (l_initial_abs_pay_stdt - p_abs_min_gap) THEN
( date_end - (l_initial_abs_pay_stdt - p_abs_min_gap) ) +1
WHEN date_end > (l_initial_abs_pay_stdt-1) THEN
( (l_initial_abs_pay_stdt-1) - date_start )+1
WHEN date_end = date_start THEN
1
ELSE (date_end - date_start) + 1 END ) AS Days_diff
INTO l_abs_worked_days
FROM per_absence_attendances
WHERE person_id = p_person_id
AND date_start < (l_initial_abs_pay_stdt-1)
AND date_end > (l_initial_abs_pay_stdt - p_abs_min_gap)
AND date_start IS NOT NULL
AND date_end IS NOT NULL ;
SELECT to_date(PAA.ABS_INFORMATION1,'yyyy/mm/dd hh24:mi:ss') --AS l_ptm_expected_dob
,to_date(PAA.ABS_INFORMATION2,'yyyy/mm/dd hh24:mi:ss') --AS l_ptm_dob
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION3) --AS l_ptm_percentage
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION4) --AS l_ptm_compensation_rate
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION5) --AS l_ptm_paternity_days
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION6) --AS l_ptm_pt_paternity_days
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION7) --AS l_ptm_no_of_babies_born
,PAA.ABS_INFORMATION8 --AS l_ptm_reimburse_from_ss
,PAA.ABS_INFORMATION9 --AS l_ptm_use_ss_daily_rate
,PAA.ABS_INFORMATION10 --AS l_ptm_reclaim_pay_max_6g
,PAA.ABS_INFORMATION11 --AS l_ptm_hol_acc_ent
,PAA.ABS_INFORMATION12 --AS l_ptm_daily_rate_calc
,PAA.ABS_INFORMATION15 --AS intial_absence
,PAA.ABS_INFORMATION16 -- AS intial_abs_attend_id
INTO l_ptm_expected_dob
,l_ptm_dob
,l_ptm_percentage
,l_ptm_compensation_rate
,l_ptm_paternity_days
,l_ptm_pt_paternity_days
,l_ptm_no_of_babies_born
,l_ptm_reimburse_from_ss
,l_ptm_use_ss_daily_rate
,l_ptm_reclaim_pay_max_6g
,l_ptm_hol_acc_ent
,l_ptm_daily_rate_calc
,l_initial_absence
,l_initial_abs_attend_id
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT SUM(DECODE(paa.date_start, paa.date_end, 1,
get_weekdays(greatest(p_pay_start_date, paa.date_start), paa.date_end, p_work_pattern) )) AS days_diff
INTO l_pt_maternity_sum
FROM per_absence_attendances paa, per_absence_attendance_types pat
WHERE paa.person_id = p_person_id
AND paa.date_end BETWEEN p_pay_start_date AND p_pay_end_date
AND paa.date_end < p_abs_start_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 = 'M'
ORDER BY paa.date_end DESC ;
SELECT SUM( CASE WHEN date_start < (l_initial_abs_pay_stdt - p_abs_min_gap) THEN
( date_end - (l_initial_abs_pay_stdt - p_abs_min_gap) ) +1
WHEN date_end > (l_initial_abs_pay_stdt-1) THEN
( (l_initial_abs_pay_stdt-1) - date_start )+1
WHEN date_end = date_start THEN
1
ELSE (date_end - date_start) + 1 END ) AS Days_diff
INTO l_abs_worked_days
FROM per_absence_attendances
WHERE person_id = p_person_id
AND date_start < (l_initial_abs_pay_stdt-1)
AND date_end > (l_initial_abs_pay_stdt - p_abs_min_gap)
AND date_start IS NOT NULL
AND date_end IS NOT NULL ;
SELECT to_date(PAA.ABS_INFORMATION1,'yyyy/mm/dd hh24:mi:ss') AS Dateofadoption
,to_date(PAA.ABS_INFORMATION2,'yyyy/mm/dd hh24:mi:ss') AS Dateofbirth
,to_number(PAA.ABS_INFORMATION3) AS Comprate
,PAA.ABS_INFORMATION4 AS Use_ss_rate
,PAA.ABS_INFORMATION5 AS Daily_rate_option
,PAA.ABS_INFORMATION6 AS dummy
INTO l_adopt_doa
,l_adopt_dob
,l_adopt_comprate
,l_abs_restrict_dr_ss
,l_abs_daily_rate
,l_dummy
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT to_date(PAA.ABS_INFORMATION1,'yyyy/mm/dd hh24:mi:ss') AS Dateofadoption
,to_date(PAA.ABS_INFORMATION2,'yyyy/mm/dd hh24:mi:ss') AS Dateofbirth
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION3) AS Comprate
,PAA.ABS_INFORMATION4 AS reimb_ss
,PAA.ABS_INFORMATION5 AS Use_ss_rate
,PAA.ABS_INFORMATION6 AS restrict_ss_sl
,PAA.ABS_INFORMATION7 AS dummy
,PAA.ABS_INFORMATION8 AS Daily_rate_option
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION9) AS NumOfChildren
,PAA.ABS_INFORMATION15 AS intial_absence
,PAA.ABS_INFORMATION16 AS intial_abs_attend_id
INTO l_adopt_doa
,l_adopt_dob
,l_adopt_comprate
,l_abs_reimb_ss
,l_abs_restrict_dr_ss
,l_abs_restrict_ss_sl
,l_dummy
,l_abs_daily_rate
,l_no_of_children
,l_initial_absence
,l_initial_abs_attend_id
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT SUM(DECODE(paa.date_start, paa.date_end, 1,
get_weekdays(greatest(p_pay_start_date, paa.date_start), paa.date_end, p_work_pattern) )) AS days_diff
INTO l_adopt_sum
FROM per_absence_attendances paa, per_absence_attendance_types pat
WHERE paa.person_id = p_person_id
AND paa.date_end BETWEEN p_pay_start_date AND p_pay_end_date
AND paa.date_end < p_abs_start_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 = 'IE_AL'
ORDER BY paa.date_end DESC ;
SELECT SUM( CASE WHEN date_start < (l_initial_abs_pay_stdt - p_abs_min_gap) THEN
( date_end - (l_initial_abs_pay_stdt - p_abs_min_gap) ) +1
WHEN date_end > (l_initial_abs_pay_stdt-1) THEN
( (l_initial_abs_pay_stdt-1) - date_start )+1
WHEN date_end = date_start THEN
1
ELSE (date_end - date_start) + 1 END ) AS Days_diff
INTO l_abs_worked_days
FROM per_absence_attendances
WHERE person_id = p_person_id
AND date_start < (l_initial_abs_pay_stdt-1)
AND date_end > (l_initial_abs_pay_stdt - p_abs_min_gap)
AND date_start IS NOT NULL
AND date_end IS NOT NULL ;
SELECT to_date(PAA.ABS_INFORMATION1,'yyyy/mm/dd hh24:mi:ss') AS Dateofadoption
,to_date(PAA.ABS_INFORMATION2,'yyyy/mm/dd hh24:mi:ss') AS Dateofbirth
,to_number(PAA.ABS_INFORMATION3) AS PartTimePercent
,to_number(PAA.ABS_INFORMATION4) AS Comprate
,PAA.ABS_INFORMATION5 AS reimurse_ss--Use_ss_rate
,PAA.ABS_INFORMATION6 AS use_ss_rate
,PAA.ABS_INFORMATION7 AS reclaim_6g
,PAA.ABS_INFORMATION9 AS dailtrate
INTO l_adopt_doa
,l_adopt_dob
,l_adopt_ptp
,l_adopt_comprate
,l_adopt_reimb_ss
,l_adopt_restrict_dr_ss
,l_adopt_restrict_empr_sl
,l_adopt_daily_rate
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT to_date(PAA.ABS_INFORMATION1,'yyyy/mm/dd hh24:mi:ss') AS Dateofadoption
,to_date(PAA.ABS_INFORMATION2,'yyyy/mm/dd hh24:mi:ss') AS Dateofbirth
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION3) AS PartTimePercent
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION4) AS Comprate
,PAA.ABS_INFORMATION5 AS reimurse_ss--Use_ss_rate
,PAA.ABS_INFORMATION6 AS use_ss_rate
,PAA.ABS_INFORMATION7 AS reclaim_6g
,PAA.ABS_INFORMATION9 AS dailtrate
,fnd_number.canonical_to_number(PAA.ABS_INFORMATION10) AS NumOfChildren
,PAA.ABS_INFORMATION15 AS intial_absence
,PAA.ABS_INFORMATION16 AS intial_abs_attend_id
INTO l_adopt_doa
,l_adopt_dob
,l_adopt_ptp
,l_adopt_comprate
,l_adopt_reimb_ss
,l_adopt_restrict_dr_ss
,l_adopt_restrict_empr_sl
,l_adopt_daily_rate
,l_no_of_children
,l_initial_absence
,l_initial_abs_attend_id
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT SUM(DECODE(paa.date_start, paa.date_end, 1,
get_weekdays(greatest(p_pay_start_date, paa.date_start), paa.date_end, p_work_pattern) )) AS days_diff
INTO l_adopt_sum
FROM per_absence_attendances paa, per_absence_attendance_types pat
WHERE paa.person_id = p_person_id
AND paa.date_end BETWEEN p_pay_start_date AND p_pay_end_date
AND paa.date_end < p_abs_start_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 = 'PTA'
ORDER BY paa.date_end DESC ;
SELECT SUM( CASE WHEN date_start < (l_initial_abs_pay_stdt - p_abs_min_gap) THEN
( date_end - (l_initial_abs_pay_stdt - p_abs_min_gap) ) +1
WHEN date_end > (l_initial_abs_pay_stdt-1) THEN
( (l_initial_abs_pay_stdt-1) - date_start )+1
WHEN date_end = date_start THEN
1
ELSE (date_end - date_start) + 1 END ) AS Days_diff
INTO l_abs_worked_days
FROM per_absence_attendances
WHERE person_id = p_person_id
AND date_start < (l_initial_abs_pay_stdt-1)
AND date_end > (l_initial_abs_pay_stdt - p_abs_min_gap)
AND date_start IS NOT NULL
AND date_end IS NOT NULL ;
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 = 'Absence Detail Override'
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
AND iv1.name in ('Start Date', 'End Date', 'Employer Period Daily Rate', 'Social Security Daily Rate', 'Reclaimable Daily Rate', 'Employer Period Days', 'Social Security Period Days', 'Absence Category')
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;
SELECT LOOKUP_CODE
INTO l_abs_category_code
FROM HR_LOOKUPS /* Bug Fix 5263714 referred hr_lookups instead of fnd_lookup_values
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'ABSENCE_CATEGORY'
AND ENABLED_FLAG = 'Y'
AND MEANING = p_abs_category;
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 = 'Sickness Unpaid'
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
AND iv1.name IN ('Start Date', 'End Date')
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 effdt BETWEEN ee.effective_start_date AND ee.effective_end_date
AND effdt 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 effdt 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;
SELECT MAX(ee.effective_end_date)
INTO l_max_date
FROM pay_element_types_f et
,pay_element_links_f el
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE et.element_name = 'Sickness Unpaid'
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
AND iv1.NAME = 'End Date'
AND el.element_type_id = et.element_type_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_end_date <= p_effective_date
AND ee.assignment_id = p_assignment_id;
select pucf.VALUE
into l_g_rate
from pay_user_tables put
,pay_user_rows_f pur
,pay_user_columns puc
,pay_user_column_instances_f pucf
where put.USER_TABLE_NAME = 'NO_GLOBAL_CONSTANTS'
and pur.ROW_LOW_RANGE_OR_NAME = 'NATIONAL_INSURANCE_BASE_RATE'
and puc.USER_COLUMN_NAME = 'Value'
and put.legislation_code = 'NO'
and pur.legislation_code = 'NO'
and puc.legislation_code = 'NO'
and ( pucf.business_group_id = p_business_group_id OR pucf.business_group_id is NULL )
and put.user_table_id = pur.user_table_id
and put.user_table_id = puc.user_table_id
and pucf.user_row_id = pur.user_row_id
and pucf.user_column_id = puc.user_column_id
and p_effective_date between pur.effective_start_date and pur.effective_end_date
and p_effective_date between pucf.effective_start_date and pucf.effective_end_date ;
select fnd_number.canonical_to_number(GLOBAL_VALUE)
into l_g_rate
from ff_globals_f
where global_name = 'NO_NATIONAL_INSURANCE_BASE_RATE'
and LEGISLATION_CODE = 'NO'
and BUSINESS_GROUP_ID IS NULL
and p_effective_date between EFFECTIVE_START_DATE and 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 = 'Sickness Unpaid'
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
AND iv1.name = 'End Date'
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 effdt BETWEEN ee.effective_start_date AND ee.effective_end_date
AND effdt 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 effdt 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;
SELECT PPA.EFFECTIVE_DATE
,PAA.ASSIGNMENT_ID
INTO l_effective_date
,l_assignment_id
FROM PAY_PAYROLL_ACTIONS PPA
,PAY_ASSIGNMENT_ACTIONS PAA
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = P_ASSIGNMENT_ACTION_ID;
SELECT MAX(ee.effective_end_date)
INTO l_max_date
FROM pay_element_types_f et
,pay_element_links_f el
,pay_input_values_f iv1
,pay_element_entries_f ee
,pay_element_entry_values_f eev1
WHERE et.element_name = 'Sickness Unpaid'
AND et.legislation_code = 'NO'
AND iv1.element_type_id = et.element_type_id
AND iv1.NAME = 'End Date'
AND el.element_type_id = et.element_type_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_end_date <= l_effective_date
AND ee.assignment_id = l_assignment_id;
SELECT pucf.VALUE
INTO l_value
FROM pay_user_tables put
,pay_user_rows_f pur
,pay_user_columns puc
,pay_user_column_instances_f pucf
WHERE put.USER_TABLE_NAME = p_usertable_name
AND pur.ROW_LOW_RANGE_OR_NAME = p_exact_text
AND puc.USER_COLUMN_NAME = p_usertable_colname
AND put.legislation_code = 'NO'
AND pur.legislation_code = 'NO'
AND puc.legislation_code = 'NO'
AND ( pucf.business_group_id = p_business_group_id OR pucf.business_group_id IS NULL )
AND put.user_table_id = pur.user_table_id
AND put.user_table_id = puc.user_table_id
AND pucf.user_row_id = pur.user_row_id
AND pucf.user_column_id = puc.user_column_id
AND p_effective_date BETWEEN pur.effective_start_date AND pur.effective_end_date
AND p_effective_date BETWEEN pucf.effective_start_date AND pucf.effective_end_date ;
SELECT start_date FROM per_previous_employers
WHERE person_id = p_person_id
AND end_date >= p_check_start_date
AND start_date <= p_check_end_date
ORDER BY start_date;
SELECT end_date FROM per_previous_employers
WHERE person_id = p_person_id
AND end_date >= p_check_start_date
AND start_date <= p_check_end_date
ORDER BY start_date;
SELECT min(start_date) FROM per_previous_employers
WHERE person_id = p_person_id
AND end_date >= p_check_start_date
AND start_date <= p_check_end_date;
SELECT max(end_date) FROM per_previous_employers
WHERE person_id = p_person_id
AND end_date >= p_check_start_date
AND start_date <= p_check_end_date;
SELECT pap.person_id FROM
pay_assignment_actions paa, per_all_assignments_f asgmt, per_all_people_f pap
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.assignment_id = asgmt.assignment_id
AND asgmt.person_id = pap.person_id;
SELECT min(date_start) from
per_absence_attendances
where person_id = p_person_id
AND ABS_INFORMATION_CATEGORY = 'NO_M'
AND abs_information2 = p_dob;
SELECT abs_information2 from
per_absence_attendances
WHERE ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT element_type_id from
pay_element_entries_f
WHERE element_entry_id = p_element_entry_id;
SELECT input_value_id from
pay_input_values_f
WHERE element_type_id = p_element_type_id
AND NAME = 'CREATOR_ID';
SELECT screen_entry_value
FROM pay_element_entry_values_f
WHERE element_entry_id = p_element_entry_id
AND input_value_id = p_input_value_id;
SELECT pap.person_id FROM
pay_assignment_actions paa, per_all_assignments_f asgmt, per_all_people_f pap
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.assignment_id = asgmt.assignment_id
AND asgmt.person_id = pap.person_id;
SELECT min(date_start) from
per_absence_attendances
where person_id = p_person_id
AND ABS_INFORMATION_CATEGORY = 'NO_IE_AL'
AND abs_information2 = p_dob;
SELECT min(date_start) from
per_absence_attendances
where person_id = p_person_id
AND ABS_INFORMATION_CATEGORY IN ('NO_IE_AL','NO_PTA')
AND abs_information2 = p_dob;
SELECT abs_information2 from
per_absence_attendances
WHERE ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT element_type_id from
pay_element_entries_f
WHERE element_entry_id = p_element_entry_id;
SELECT input_value_id from
pay_input_values_f
WHERE element_type_id = p_element_type_id
AND NAME = 'CREATOR_ID';
SELECT screen_entry_value
FROM pay_element_entry_values_f
WHERE element_entry_id = p_element_entry_id
AND input_value_id = p_input_value_id;
SELECT peef.effective_start_date,peef.assignment_id
FROM pay_element_entries_f peef
WHERE peef.element_entry_id = p_original_entry_id;
SELECT ptp.start_date
FROM per_all_assignments_f paaf, per_time_periods ptp
WHERE paaf.assignment_id = p_assignment_id
AND ptp.payroll_id = paaf.payroll_id
AND p_initial_abs_start_date between ptp.start_date and ptp.end_date;
SELECT screen_entry_value
FROM pay_element_entries_f peef,
pay_input_values_f pivf,
pay_element_entry_values_f peevf
WHERE peef.element_entry_id = p_element_entry_id
AND pivf.element_type_id = peef.element_type_id
AND pivf.name = 'Absence Category'
AND pivf.legislation_code = 'NO'
AND peevf.input_value_id = pivf.input_value_id
AND peevf.element_entry_id = peef.element_entry_id;
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_asg_id
AND paaf.assignment_status_type_id = 1;
SELECT PERSON_ID
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_ID = p_assignment_id
AND p_effective_date BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE;
SELECT PER_INFORMATION22 AS restrict_hol_to_6G
FROM PER_ALL_PEOPLE_F PER
WHERE PER.PERSON_ID = p_person_id
AND P_EFFECTIVE_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE;
SELECT hoi4.ORG_INFORMATION10 AS restrict_hol_to_6G
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 = p_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = X.ORG_ID
AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id = hoi2.org_information1
AND hoi2.ORG_INFORMATION_CONTEXT='NO_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='NO_ABSENCE_PAYMENT_DETAILS';
SELECT paa.abs_information14 hol_days
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT paa.abs_information13 hol_days
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT paa.abs_information10 hol_days
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT paa.abs_information12 hol_days
FROM PER_ABSENCE_ATTENDANCES PAA
WHERE PAA.ABSENCE_ATTENDANCE_ID = p_abs_attendance_id;
SELECT PERSON_ID
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_ID = p_assignment_id
AND p_abs_start_date BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_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
,pcr.date_start
,pcr.date_end
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 nvl(peef2.effective_start_date,peef1.effective_start_date)
FROM pay_element_entry_values_f peevf
,pay_input_values_f pivf
,pay_element_entries_f peef1
,pay_element_entries_f peef2
WHERE peevf.screen_entry_value = p_abs_attn_id
AND pivf.input_value_id = peevf.input_value_id
AND pivf.NAME = 'CREATOR_ID'
AND pivf.legislation_code = 'NO'
AND peef1.element_entry_id = peevf.element_entry_id
AND peef2.element_entry_id(+) = peef1.original_entry_id;
SELECT date_start
FROM per_absence_attendances
WHERE absence_attendance_id = p_abs_attn_id ;