[Home] [Help]
1: package body PER_ABSENCE_ATTENDANCES_PKG as
2: /* $Header: peaba01t.pkb 120.0 2005/05/31 04:39:27 appldev noship $ */
3:
4: /* Date Name Release Description
5: ---- ---- ------- -----------
22: not be delivered until SSP delivery. These
23: commented out bits should be uncommented at that
24: time.
25: 13-OCT-95 J Thuringer Replaced 'PACKAGE_NAME' variable by
26: hardcoded 'PER_ABSENCE_ATTENDANCES_PKG';
27: removed error checking code from end of script
28: 17-OCT-95 F Assadi Added a new function get_anuual_bal to work
29: out the absence taken from the year.
30: is_emp_entitled function was added. This
119: 115.24 23-JUL-2003 2829746 ablinko amendments for SAP/SPP
120: 115.25 25-Aug-2003 3111653 skota added to_number() for explicit
121: conversion. Seems implicit conversion
122: is not taking place in 10G
123: 115.26 06-NOV-2003 3156665 kjagadee Modified per_absence_attendances_pkg.get_defaults
124: procedure to calculate the drfault hours in a day
125: as 24 hours.
126: 115.27 29-JAN-2004 3387265 smparame Modified inti_form procedure.
127: upper(formula_type_name) is changed to formula
132: 115.30 14-JUL-2004 3694165 ablinko Now calls earliest_ppp_start_date with
133: actual_birth_date instead of due_date
134: -------+------------+---------+----------+--------------------------------------
135: */
136: g_package varchar2(33) := ' per_absence_attendances_pkg.';
137: -- Global package name
138:
139: --
140: ------------------------------------------------------------------------------
148: Begin
149: --
150: hr_utility.set_location('Entering:'||l_proc, 5);
151: --
152: If (p_constraint_name = 'PER_ABSENCE_ATTENDANCES_FK4') Then
153: --
154: -- The linked_absence_id does not refer to a valid absence_attendance_id.
155: --
156: fnd_message.set_name('SSP', 'SSP_35060_SSP_ABS_FK2');
154: -- The linked_absence_id does not refer to a valid absence_attendance_id.
155: --
156: fnd_message.set_name('SSP', 'SSP_35060_SSP_ABS_FK2');
157: --
158: ElsIf (p_constraint_name = 'PER_ABSENCE_ATTENDANCES_FK5') Then
159: --
160: -- The maternity_id is not valid.
161: --
162: fnd_message.set_name('SSP', 'SSP_35061_SSP_ABS_FK1');
247: p_nos_absences in out nocopy number) is
248: --
249: cursor c2 is
250: select date_start, rowid
251: from per_absence_attendances
252: where maternity_id = p_maternity_id;
253: c2_rec c2%ROWTYPE;
254: --
255: begin
511: --
512: procedure populate_ben_absence_rec
513: (p_absence_attendance_id in number,
514: p_rec_type in varchar2,
515: p_old_rec in per_absence_attendances%rowtype,
516: p_ben_rec out nocopy ben_abs_ler.g_abs_ler_rec) is
517:
518: cursor c_current_absence is
519: select *
516: p_ben_rec out nocopy ben_abs_ler.g_abs_ler_rec) is
517:
518: cursor c_current_absence is
519: select *
520: from per_absence_attendances
521: where absence_attendance_id = p_absence_attendance_id;
522: --
523: l_absence_rec per_absence_attendances%rowtype;
524: l_proc varchar2(72) := g_package||'populate_ben_absence_rec';
519: select *
520: from per_absence_attendances
521: where absence_attendance_id = p_absence_attendance_id;
522: --
523: l_absence_rec per_absence_attendances%rowtype;
524: l_proc varchar2(72) := g_package||'populate_ben_absence_rec';
525:
526: begin
527:
703: X_Abs_Information28 VARCHAR2,
704: X_Abs_Information29 VARCHAR2,
705: X_Abs_Information30 VARCHAR2
706: ) IS
707: CURSOR C IS SELECT rowid FROM per_absence_attendances
708: WHERE absence_attendance_id = X_absence_attendance_id;
709:
710: CURSOR C2 IS SELECT per_absence_attendances_s.nextval FROM sys.dual;
711:
706: ) IS
707: CURSOR C IS SELECT rowid FROM per_absence_attendances
708: WHERE absence_attendance_id = X_absence_attendance_id;
709:
710: CURSOR C2 IS SELECT per_absence_attendances_s.nextval FROM sys.dual;
711:
712: cursor csr_later_sickness is
713: select 1
714: from per_absence_attendances
710: CURSOR C2 IS SELECT per_absence_attendances_s.nextval FROM sys.dual;
711:
712: cursor csr_later_sickness is
713: select 1
714: from per_absence_attendances
715: where person_id = X_person_id
716: and sickness_start_date is not null
717: and sickness_start_date > X_sickness_start_date;
718: --
759: OPEN C2;
760: FETCH C2 INTO X_absence_attendance_id;
761: CLOSE C2;
762: end if;
763: INSERT INTO per_absence_attendances(
764: absence_attendance_id,
765: business_group_id,
766: absence_attendance_type_id,
767: abs_attendance_reason_id,
960: --
961: Exception
962: When hr_api.check_integrity_violated Then
963: -- A check constraint has been violated
964: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
965: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
966: When hr_api.parent_integrity_violated Then
967: -- Parent integrity has been violated
968: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
964: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
965: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
966: When hr_api.parent_integrity_violated Then
967: -- Parent integrity has been violated
968: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
969: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
970: When hr_api.unique_integrity_violated Then
971: -- Unique integrity has been violated
972: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
968: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
969: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
970: When hr_api.unique_integrity_violated Then
971: -- Unique integrity has been violated
972: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
973: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
974: When Others Then
975: Raise;
976: END Insert_Row;
1061: X_Abs_Information30 VARCHAR2
1062: ) IS
1063: CURSOR C IS
1064: SELECT *
1065: FROM per_absence_attendances
1066: WHERE rowid = chartorowid(X_Rowid)
1067: FOR UPDATE of absence_attendance_id NOWAIT;
1068:
1069: Recinfo C%ROWTYPE;
1494: ) IS
1495:
1496: cursor csr_absence is
1497: select *
1498: from per_absence_attendances
1499: where absence_attendance_id = X_absence_attendance_id;
1500: --
1501: l_absence csr_absence%rowtype;
1502: --
1504: -- Start of BUGFIX for WWBUG 1408379
1505: --
1506: cursor c1 is
1507: select *
1508: from per_absence_attendances
1509: where rowid = chartorowid(X_rowid);
1510: --
1511: l_c1 c1%rowtype;
1512: l_rows_found boolean := false;
1548: close c1;
1549: --
1550: -- End of BUGFIX for WWBUG 1408379
1551: --
1552: UPDATE per_absence_attendances
1553: SET
1554: absence_attendance_id = X_Absence_Attendance_Id,
1555: business_group_id = X_Business_Group_Id,
1556: absence_attendance_type_id = X_Absence_Attendance_Type_Id,
1694: --
1695: Exception
1696: When hr_api.check_integrity_violated Then
1697: -- A check constraint has been violated
1698: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1699: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1700: When hr_api.parent_integrity_violated Then
1701: -- Parent integrity has been violated
1702: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1698: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1699: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1700: When hr_api.parent_integrity_violated Then
1701: -- Parent integrity has been violated
1702: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1703: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1704: When hr_api.unique_integrity_violated Then
1705: -- Unique integrity has been violated
1706: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1702: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1703: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1704: When hr_api.unique_integrity_violated Then
1705: -- Unique integrity has been violated
1706: PER_ABSENCE_ATTENDANCES_PKG.constraint_error
1707: (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
1708: When Others Then
1709: Raise;
1710: END Update_Row;
1714: PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
1715: --
1716: cursor csr_current_absence is
1717: select *
1718: from per_absence_attendances
1719: where rowid = X_ROWID;
1720: --
1721: l_absence csr_current_absence%rowtype;
1722: l_old ben_abs_ler.g_abs_ler_rec;
1741: p_absence_attendance_id => l_absence.absence_attendance_id);
1742: --
1743: end if;
1744: --
1745: DELETE FROM per_absence_attendances
1746: WHERE rowid = chartorowid(X_Rowid);
1747:
1748: if (SQL%NOTFOUND) then
1749: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1802: and pp.current_employee_flag = 'Y';
1803: --
1804: begin
1805: --
1806: hr_utility.set_location('per_absence_attendances_pkg.b_check_auth_de',1);
1807: --
1808: open c1;
1809: --
1810: fetch c1 into l_exists;
1848: and pp.current_employee_flag = 'Y';
1849: --
1850: begin
1851: --
1852: hr_utility.set_location('per_absence_attendances_pkg.b_check_rep_de',1);
1853: --
1854: open c3;
1855: --
1856: fetch c3 into l_exists;
1880: and p.person_id = p_rep_per;
1881: --
1882: begin
1883: --
1884: hr_utility.set_location('per_absence_attendances_pkg.b_check_for_termination',1);
1885: --
1886: open c2;
1887: --
1888: l_ret_val := c2%found;
1918: and pp.replacement_required_flag = 'Y';
1919: --
1920: begin
1921: --
1922: hr_utility.set_location('per_absence_attendances_pkg.chk_rep_req',1);
1923: --
1924: open c4;
1925: fetch c4 into l_exists;
1926: --
1953: or p_dend is null);
1954: --
1955: begin
1956: --
1957: hr_utility.set_location('per_absence_attendances_pkg.chk_type',1);
1958: --
1959: open c5;
1960: --
1961: fetch c5 into l_exists;
1987: or p_proj_end is null);
1988: --
1989: begin
1990: --
1991: hr_utility.set_location('per_absence_attendances_pkg.chk_proj',1);
1992: --
1993: open c6;
1994: --
1995: fetch c6 into l_exists;
2014: l_ret_val BOOLEAN;
2015:
2016: cursor c7 is
2017: select 1
2018: from per_absence_attendances t,
2019: per_absence_attendance_types a,
2020: per_absence_attendance_types b
2021: where (p_abat_id is null
2022: or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
2028: and t.date_end = p_dstart -1;
2029: --
2030: begin
2031: --
2032: hr_utility.set_location('per_absence_attendances_pkg.chkab1',1);
2033: --
2034: open c7;
2035: fetch c7 into l_exists;
2036: --
2057: l_exists2 VARCHAR2(1);
2058:
2059: cursor c8 is
2060: select 'x'
2061: from per_absence_attendances t,
2062: per_absence_attendance_types a,
2063: per_absence_attendance_types b
2064: where (p_abat_id is null
2065: or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
2072: and t.date_end is null;
2073: --
2074: cursor c9 is
2075: select 'x'
2076: from per_absence_attendances t,
2077: per_absence_attendance_types a,
2078: per_absence_attendance_types b
2079: where (p_abat_id is null
2080: or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
2087: or (t.date_start between p_dstart and nvl(p_dend,p_eot)));
2088: --
2089: begin
2090: --
2091: hr_utility.set_location('per_absence_attendances_pkg.chkab2',1);
2092: --
2093: open c8;
2094: --
2095: fetch c8 into l_exists;
2100: END IF;
2101: --
2102: close c8;
2103: --
2104: hr_utility.set_location('per_absence_attendances_pkg.chkab2',2);
2105: --
2106: open c9;
2107: --
2108: fetch c9 into l_exists2;
2129: l_ret_val BOOLEAN;
2130:
2131: cursor c10 is
2132: select 1
2133: from per_absence_attendances t,
2134: per_absence_attendance_types a,
2135: per_absence_attendance_types b
2136: where (p_abat_id is null
2137: or (p_abat_id is not null and p_abat_id <> t.absence_attendance_id))
2142: or (t.date_start between p_dstart and nvl(p_dend,p_eot)));
2143: --
2144: begin
2145: --
2146: hr_utility.set_location('per_absence_attendances_pkg.chkab3',1);
2147: --
2148: open c10;
2149: fetch c10 into l_exists;
2150: --
2190: and p_sdstart between t.start_date and t.end_date;
2191: --
2192: begin
2193: --
2194: hr_utility.set_location('per_absence_attendances_pkg.b_elmnt_entry_dets',1);
2195: --
2196: open c11;
2197: --
2198: fetch c11 into p_e_entry_id,
2216: and lookup_code = p_abcat;
2217: --
2218: begin
2219: --
2220: hr_utility.set_location('per_absence_attendances_pkg.b_get_category',1);
2221: --
2222: open c12;
2223: --
2224: fetch c12 into p_mean;
2248: from sys.dual;
2249: --
2250: begin
2251: --
2252: hr_utility.set_location('per_absence_attendances_pkg.get_defaults',1);
2253: --
2254: open c14;
2255: --
2256: fetch c14 into l_hrs_def,
2334: and p_sess between lu1.effective_start_date and lu1.effective_end_date));
2335: --
2336: begin
2337: --
2338: hr_utility.set_location('per_absence_attendances_pkg.get_ele_det1',1);
2339: --
2340: open c15;
2341: --
2342: fetch c15 into p_ass_id,
2367: and pettl.language = userenv('LANG');
2368: --
2369: begin
2370: --
2371: hr_utility.set_location('per_absence_attendances_pkg.get_ele_det2',1);
2372: --
2373: open c16;
2374: --
2375: fetch c16 into p_dele_name;
2395: and p_dstart between start_date and end_date;
2396: --
2397: begin
2398: --
2399: hr_utility.set_location('per_absence_attendances_pkg.get_period_dates',1);
2400: --
2401: open c18;
2402: --
2403: fetch c18 into p_prd_start,
2429: and primary_flag = 'Y';
2430: --
2431: begin
2432: --
2433: hr_utility.set_location('per_absence_attendances_pkg.get_run_tot',1);
2434: --
2435: open c19;
2436: --
2437: fetch c19 into p_db_itm;
2437: fetch c19 into p_db_itm;
2438: --
2439: close c19;
2440: --
2441: hr_utility.set_location('per_absence_attendances_pkg.get_run_tot',2);
2442: --
2443: open c20;
2444: --
2445: fetch c20 into p_ass_id;
2482: l_balance NUMBER := 0;
2483: --
2484: begin
2485: --
2486: hr_utility.set_location('per_absence_attendances_pkg.get_annual_bal',1);
2487: --
2488: open c19;
2489: --
2490: fetch c19 into l_balance;
2547:
2548: --
2549: begin
2550: --
2551: hr_utility.set_location('per_absence_attendances_pkg.check_entitlement',1);
2552: --
2553: if p_days_requested is null and p_days_requested is null then
2554: --
2555: return true;
2617: and date_start <= p_sess;
2618: --
2619: begin
2620: --
2621: hr_utility.set_location('per_absence_attendances_pkg.init_form',1);
2622: --
2623: open c21;
2624: --
2625: fetch c21 into p_form_type;
2625: fetch c21 into p_form_type;
2626: --
2627: close c21;
2628: --
2629: hr_utility.set_location('per_absence_attendances_pkg.init_form',2);
2630: --
2631: open c21b;
2632: --
2633: fetch c21b into p_dstart,
2649: and payroll_id is not null;
2650: --
2651: begin
2652: --
2653: hr_utility.set_location('per_absence_attendances_pkg.ins_ok',1);
2654: --
2655: open c22;
2656: --
2657: fetch c22 into p_test;
2667: p_occur IN OUT NOCOPY NUMBER) is
2668:
2669: cursor c23 is
2670: select nvl(max(occurrence),0) + 1
2671: from per_absence_attendances
2672: where business_group_id + 0 = p_bgroup_id
2673: and absence_attendance_type_id = p_abat_type
2674: and person_id = p_per_id;
2675: --
2674: and person_id = p_per_id;
2675: --
2676: begin
2677: --
2678: hr_utility.set_location('per_absence_attendances_pkg.get_occur',1);
2679: --
2680: open c23;
2681: --
2682: fetch c23 into p_occur;
2701: pos.date_start AND nvl(pos.actual_termination_date,hr_general.end_of_time);
2702: --
2703: begin
2704: --
2705: hr_utility.set_location('per_absence_attendances_pkg.chk_serv_period',1);
2706: --
2707: open chk_periods;
2708: --
2709: l_ret_val := chk_periods%found;
2768: where h.maternity_id = p_maternity_id;
2769: c1_rec c1%ROWTYPE;
2770: BEGIN
2771: open c1;
2772: hr_utility.set_location('per_absence_attendances_pkg.get_due_date',1);
2773: fetch c1 into c1_rec;
2774: close c1;
2775: return(c1_rec.due_date);
2776: END get_due_date;
2799: and nvl(h.leave_type,'MA') = p_leave_type;
2800:
2801:
2802: BEGIN
2803: hr_utility.set_location('per_absence_attendances_pkg.get_due_date_2',1);
2804: counter:= 0;
2805: for c1_rec in c1 LOOP
2806: p_smp_due_date := c1_rec.due_date;
2807: counter := counter + 1;
2840: --
2841: maternity csr_maternity%ROWTYPE;
2842: --
2843: BEGIN
2844: hr_utility.set_location('per_absence_attendances_pkg.check_val_abs_start',1);
2845: --
2846: -- This functionality only applies to Oracle SSP users
2847: if not ssp_ssp_pkg.ssp_is_installed then
2848: return;
2881: from ssp_maternities h
2882: where h.person_id = p_person_id;
2883: c1_rec c1%ROWTYPE;
2884: BEGIN
2885: hr_utility.set_location('per_absence_attendances_pkg.chk_related_maernity',1);
2886: open c1;
2887: fetch c1 into c1_rec;
2888: if c1%NOTFOUND then
2889: fnd_message.set_name('SSP','SSP_36073_PREG_RELATED_ILLNESS');
2912: where h.absence_attendance_id = p_absence_attendance_id
2913: and h.medical_type = p_medical_type;
2914: c1_rec c1%ROWTYPE;
2915: BEGIN
2916: hr_utility.set_location('per_absence_attendances_pkg.chk_evd_before_del',1);
2917: open c1;
2918: fetch c1 into c1_rec;
2919: if c1%FOUND then
2920: fnd_message.set_name('SSP','SSP_35033_ATTACHED_SICK_NOTE');
2942: l_element_details ssp_SMP_pkg.csr_SMP_element_details%rowtype;
2943:
2944: BEGIN
2945: hr_utility.set_location
2946: ('per_absence_attendances_pkg.late_abs_notification',1);
2947: open ssp_SMP_pkg.csr_SMP_element_details(p_effective_date,
2948: p_element_name);
2949: fetch ssp_SMP_pkg.csr_SMP_element_details into l_element_details;
2950: close ssp_SMP_pkg.csr_SMP_element_details;
2958: return(FALSE);
2959: end if;
2960: END late_abs_notification;
2961:
2962: end PER_ABSENCE_ATTENDANCES_PKG;