[Home] [Help]
PACKAGE BODY: APPS.PER_ABS_ATTENDANCE_REASONS_PKG
Source
1 PACKAGE BODY PER_ABS_ATTENDANCE_REASONS_PKG as
2 /* $Header: peaar01t.pkb 115.0 99/07/17 18:23:02 porting ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===========================================================================*/
8 /*-----------------------------------------------------------------------------
9
10 Description
11 -----------
12
13 Date Version Author Description
14 -------- ------- --------- ---------------
15 17/02/94 80.0 JRhodes Initial Version
16 14/04/94 80.1 JRhodes Modified Lock-Row to rtrim VARCHAR2
17 04/03/97 80.2 JAlloun Changed all occurances of system.dual
18 to sys.dual for next release requirements.
19
20 -----------------------------------------------------------------------------*/
21 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
22 X_Abs_Attendance_Reason_Id IN OUT NUMBER,
23 X_Business_Group_Id NUMBER,
24 X_Absence_Attendance_Type_Id NUMBER,
25 X_Name VARCHAR2,
26 X_Last_Update_Date DATE,
27 X_Last_Updated_By NUMBER,
28 X_Last_Update_Login NUMBER,
29 X_Created_By NUMBER,
30 X_Creation_Date DATE
31 ) IS
32 --
33 CURSOR C IS
34 SELECT rowid
35 FROM PER_ABS_ATTENDANCE_REASONS
36 WHERE abs_attendance_reason_id = X_Abs_Attendance_Reason_Id;
37 --
38 CURSOR C2 IS
39 SELECT per_abs_attendance_reasons_s.nextval
40 FROM sys.dual;
41 --
42 BEGIN
43 --
44 check_unique_reason(X_Rowid
45 ,X_Name
46 ,X_Absence_Attendance_Type_Id);
47 --
48 if (X_Abs_Attendance_Reason_Id is NULL) then
49 OPEN C2;
50 FETCH C2 INTO X_Abs_Attendance_Reason_Id;
51 CLOSE C2;
52 end if;
53 INSERT INTO PER_ABS_ATTENDANCE_REASONS(
54 abs_attendance_reason_id,
55 business_group_id,
56 absence_attendance_type_id,
57 name,
58 last_update_date,
59 last_updated_by,
60 last_update_login,
61 created_by,
62 creation_date
63 ) VALUES (
64 X_Abs_Attendance_Reason_Id,
65 X_Business_Group_Id,
66 X_Absence_Attendance_Type_Id,
67 X_Name,
68 X_Last_Update_Date,
69 X_Last_Updated_By,
70 X_Last_Update_Login,
71 X_Created_By,
72 X_Creation_Date
73 );
74 --
75 OPEN C;
76 FETCH C INTO X_Rowid;
77 if (C%NOTFOUND) then
78 CLOSE C;
79 RAISE NO_DATA_FOUND;
80 end if;
81 CLOSE C;
82 END Insert_Row;
83 --
84 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
85 X_Abs_Attendance_Reason_Id NUMBER,
86 X_Business_Group_Id NUMBER,
87 X_Absence_Attendance_Type_Id NUMBER,
88 X_Name VARCHAR2
89 ) IS
90 CURSOR C IS
91 SELECT *
92 FROM PER_ABS_ATTENDANCE_REASONS
93 WHERE rowid = X_Rowid
94 FOR UPDATE of Abs_Attendance_Reason_Id NOWAIT;
95 Recinfo C%ROWTYPE;
96 BEGIN
97 OPEN C;
98 FETCH C INTO Recinfo;
99 if (C%NOTFOUND) then
100 CLOSE C;
101 RAISE NO_DATA_FOUND;
102 end if;
103 CLOSE C;
104 --
105 Recinfo.Name := rtrim(Recinfo.Name);
106 --
107 if (
108 ( (Recinfo.abs_attendance_reason_id = X_Abs_Attendance_Reason_Id)
109 OR ( (Recinfo.abs_attendance_reason_id IS NULL)
110 AND (X_Abs_Attendance_Reason_Id IS NULL)))
111 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
112 OR ( (Recinfo.business_group_id IS NULL)
113 AND (X_Business_Group_Id IS NULL)))
114 AND ( (Recinfo.absence_attendance_type_id = X_Absence_Attendance_Type_Id)
115 OR ( (Recinfo.absence_attendance_type_id IS NULL)
116 AND (X_Absence_Attendance_Type_Id IS NULL)))
117 AND ( (Recinfo.name = X_Name)
118 OR ( (Recinfo.name IS NULL)
119 AND (X_Name IS NULL)))
120 ) then
121 return;
122 else
123 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
124 APP_EXCEPTION.RAISE_EXCEPTION;
125 end if;
126 END Lock_Row;
127 --
128 PROCEDURE Update_Row(X_Rowid VARCHAR2,
129 X_Abs_Attendance_Reason_Id NUMBER,
130 X_Business_Group_Id NUMBER,
131 X_Absence_Attendance_Type_Id NUMBER,
132 X_Name VARCHAR2,
133 X_Last_Update_Date DATE,
134 X_Last_Updated_By NUMBER,
135 X_Last_Update_Login NUMBER
136 ) IS
137 BEGIN
138 --
139 check_unique_reason(X_Rowid
140 ,X_Name
141 ,X_Absence_Attendance_Type_Id);
142 --
143 UPDATE PER_ABS_ATTENDANCE_REASONS
144 SET
145 abs_attendance_reason_id = X_Abs_Attendance_Reason_Id,
146 business_group_id = X_Business_Group_Id,
147 absence_attendance_type_id = X_Absence_Attendance_Type_Id,
148 name = X_Name,
149 last_update_date = X_Last_Update_Date,
150 last_updated_by = X_Last_Updated_By,
151 last_update_login = X_Last_Update_Login
152 WHERE rowid = X_rowid;
153 --
154 if (SQL%NOTFOUND) then
155 RAISE NO_DATA_FOUND;
156 end if;
157 --
158 END Update_Row;
159 --
160 PROCEDURE Delete_Row(X_Rowid VARCHAR2
161 ,X_abs_attendance_reason_id NUMBER) IS
162 BEGIN
163 --
164 abr_del_validation(X_abs_attendance_reason_id);
165 --
166 DELETE FROM PER_ABS_ATTENDANCE_REASONS
167 WHERE rowid = X_Rowid;
168 --
169 if (SQL%NOTFOUND) then
170 RAISE NO_DATA_FOUND;
171 end if;
172 END Delete_Row;
173 --
174 PROCEDURE Get_Name(X_CODE Varchar2
175 ,X_MEANING IN OUT Varchar2) is
176 cursor c is
177 select meaning
178 from hr_lookups
179 where lookup_code = X_CODE
180 and lookup_type = 'ABSENCE_REASON';
181 --
182 begin
183 hr_utility.set_location('per_abs_attendance_reasons_pkg.Get_Name',1);
184 OPEN C;
185 FETCH C INTO X_MEANING;
186 CLOSE C;
187 end Get_Name;
188 --
189 PROCEDURE abr_del_validation(p_abs_attendance_reason_id NUMBER) is
190 --
191 cursor c is
192 select ''
193 from per_absence_attendances
194 where ABS_ATTENDANCE_REASON_ID =p_abs_attendance_reason_id;
195 abs_rec c%rowtype;
196 --
197 begin
198 hr_utility.set_location('per_abs_attendance_reasons_pkg.abr_del_validation',1);
199 OPEN C;
200 FETCH C INTO abs_rec;
201 if (C%FOUND) then
202 CLOSE C;
203 hr_utility.set_message(801,'PER_7060_EMP_ABS_DEL_REAS');
204 hr_utility.raise_error;
205 end if;
206 CLOSE C;
207 end;
208 --
209 --
210 PROCEDURE check_unique_reason(p_rowid VARCHAR2
211 ,p_name VARCHAR2
212 ,p_absence_attendance_type_id NUMBER) is
213 --
214 cursor c is
215 select ''
216 from per_abs_attendance_reasons
217 where absence_attendance_type_id = p_absence_attendance_type_id
218 and name = p_name
219 and (p_rowid is null
220 or (p_rowid is not null
221 and rowid = chartorowid(p_rowid)));
222 abr_rec c%rowtype;
223 --
224 begin
225 hr_utility.set_location('per_abs_attendance_reasons_pkg.check_unique_reason',1);
226 OPEN C;
227 FETCH C INTO abr_rec;
228 if (C%FOUND) then
229 CLOSE C;
230 hr_utility.set_message(801,'PER_7807_DEF_ABS_REASON_EXIST');
231 hr_utility.raise_error;
232 end if;
233 CLOSE C;
234 end check_unique_reason;
235 --
236 --
237 END PER_ABS_ATTENDANCE_REASONS_PKG;