[Home] [Help]
PACKAGE BODY: APPS.ENG_ECN_APPROVERS_PKG
Source
1 PACKAGE BODY ENG_ECN_APPROVERS_PKG as
2 /* $Header: ENGAPPRB.pls 120.1 2006/03/27 06:53:58 sdarbha noship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Approval_List_Id NUMBER,
7 X_Employee_Id NUMBER,
8 X_Sequence1 NUMBER,
9 X_Sequence2 NUMBER DEFAULT NULL,
10 X_Description VARCHAR2 DEFAULT NULL,
11 X_Disable_Date DATE DEFAULT NULL,
12 X_Attribute_Category VARCHAR2 DEFAULT NULL,
13 X_Attribute1 VARCHAR2 DEFAULT NULL,
14 X_Attribute2 VARCHAR2 DEFAULT NULL,
15 X_Attribute3 VARCHAR2 DEFAULT NULL,
16 X_Attribute4 VARCHAR2 DEFAULT NULL,
17 X_Attribute5 VARCHAR2 DEFAULT NULL,
18 X_Attribute6 VARCHAR2 DEFAULT NULL,
19 X_Attribute7 VARCHAR2 DEFAULT NULL,
20 X_Attribute8 VARCHAR2 DEFAULT NULL,
21 X_Attribute9 VARCHAR2 DEFAULT NULL,
22 X_Attribute10 VARCHAR2 DEFAULT NULL,
23 X_Attribute11 VARCHAR2 DEFAULT NULL,
24 X_Attribute12 VARCHAR2 DEFAULT NULL,
25 X_Attribute13 VARCHAR2 DEFAULT NULL,
26 X_Attribute14 VARCHAR2 DEFAULT NULL,
27 X_Attribute15 VARCHAR2 DEFAULT NULL,
28 X_Creation_Date DATE,
29 X_Created_By NUMBER,
30 X_Last_Update_Login NUMBER DEFAULT NULL,
31 X_Last_Update_Date DATE,
32 X_Last_Updated_By NUMBER
33 ) IS
34 CURSOR C IS SELECT rowid FROM eng_ecn_approvers
35 WHERE approval_list_id = X_Approval_List_Id;
36 BEGIN
37 INSERT INTO eng_ecn_approvers
38 (
39 approval_list_id,
40 employee_id,
41 sequence1,
42 sequence2,
43 description,
44 disable_date,
45 attribute_category,
46 attribute1,
47 attribute2,
48 attribute3,
49 attribute4,
50 attribute5,
51 attribute6,
52 attribute7,
53 attribute8,
54 attribute9,
55 attribute10,
56 attribute11,
57 attribute12,
58 attribute13,
59 attribute14,
60 attribute15,
61 creation_date,
62 created_by,
63 last_update_login,
64 last_update_date,
65 last_updated_by
66 )
67 VALUES (
68 X_Approval_List_Id,
69 X_Employee_Id,
70 X_Sequence1,
71 X_Sequence2,
72 X_Description,
73 X_Disable_Date,
74 X_Attribute_Category,
75 X_Attribute1,
76 X_Attribute2,
77 X_Attribute3,
78 X_Attribute4,
79 X_Attribute5,
80 X_Attribute6,
81 X_Attribute7,
82 X_Attribute8,
83 X_Attribute9,
84 X_Attribute10,
85 X_Attribute11,
86 X_Attribute12,
87 X_Attribute13,
88 X_Attribute14,
89 X_Attribute15,
90 X_Creation_Date,
91 X_Created_By,
92 X_Last_Update_Login,
93 X_Last_Update_Date,
94 X_Last_Updated_By
95 );
96 OPEN C;
97 FETCH C INTO X_Rowid;
98 if (C%NOTFOUND) then
99 CLOSE C;
100 RAISE NO_DATA_FOUND;
101 end if;
102 CLOSE C;
103
104 wf_local_synch.propagate_user_role(
105 p_user_orig_system => 'PER',
106 p_user_orig_system_id => x_employee_Id,
107 p_role_orig_system => 'ENG_LIST',
108 p_role_orig_system_id => x_approval_List_Id,
109 p_start_date => null,
110 p_expiration_date => X_Disable_Date,--);
111 p_overwrite => TRUE); -- Bug 3817690
112
113 END Insert_Row;
114
115
116 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
117 X_Approval_List_Id NUMBER,
118 X_Employee_Id NUMBER,
119 X_Sequence1 NUMBER,
120 X_Sequence2 NUMBER DEFAULT NULL,
121 X_Description VARCHAR2 DEFAULT NULL ,
122 X_Disable_Date DATE DEFAULT NULL,
123 X_Attribute_Category VARCHAR2 DEFAULT NULL ,
124 X_Attribute1 VARCHAR2 DEFAULT NULL ,
125 X_Attribute2 VARCHAR2 DEFAULT NULL ,
126 X_Attribute3 VARCHAR2 DEFAULT NULL ,
127 X_Attribute4 VARCHAR2 DEFAULT NULL ,
128 X_Attribute5 VARCHAR2 DEFAULT NULL ,
129 X_Attribute6 VARCHAR2 DEFAULT NULL ,
130 X_Attribute7 VARCHAR2 DEFAULT NULL ,
131 X_Attribute8 VARCHAR2 DEFAULT NULL ,
132 X_Attribute9 VARCHAR2 DEFAULT NULL ,
133 X_Attribute10 VARCHAR2 DEFAULT NULL ,
134 X_Attribute11 VARCHAR2 DEFAULT NULL ,
135 X_Attribute12 VARCHAR2 DEFAULT NULL ,
136 X_Attribute13 VARCHAR2 DEFAULT NULL ,
137 X_Attribute14 VARCHAR2 DEFAULT NULL ,
138 X_Attribute15 VARCHAR2 DEFAULT NULL
139
140 ) IS
141 CURSOR C IS
142 SELECT *
143 FROM eng_ecn_approvers
144 WHERE rowid = X_Rowid
145 FOR UPDATE of Approval_List_Id NOWAIT;
146 Recinfo C%ROWTYPE;
147 BEGIN
148 OPEN C;
149 FETCH C INTO Recinfo;
150 if (C%NOTFOUND) then
151 CLOSE C;
152 RAISE NO_DATA_FOUND;
153 end if;
154 CLOSE C;
155 if (
156 ( (Recinfo.approval_list_id = X_Approval_List_Id)
157 OR ( (Recinfo.approval_list_id IS NULL)
158 AND (X_Approval_List_Id IS NULL)))
159 AND ( (Recinfo.employee_id = X_Employee_Id)
160 OR ( (Recinfo.employee_id IS NULL)
161 AND (X_Employee_Id IS NULL)))
162 AND ( (Recinfo.sequence1 = X_Sequence1)
163 OR ( (Recinfo.sequence1 IS NULL)
164 AND (X_Sequence1 IS NULL)))
165 AND ( (Recinfo.sequence2 = X_Sequence2)
166 OR ( (Recinfo.sequence2 IS NULL)
167 AND (X_Sequence2 IS NULL)))
168 AND ( (Recinfo.description = X_Description)
169 OR ( (Recinfo.description IS NULL)
170 AND (X_Description IS NULL)))
171 AND ( (Recinfo.disable_date = X_Disable_Date)
172 OR ( (Recinfo.disable_date IS NULL)
173 AND (X_Disable_Date IS NULL)))
174 AND ( (Recinfo.attribute_category = X_Attribute_Category)
175 OR ( (Recinfo.attribute_category IS NULL)
176 AND (X_Attribute_Category IS NULL)))
177 AND ( (Recinfo.attribute1 = X_Attribute1)
178 OR ( (Recinfo.attribute1 IS NULL)
179 AND (X_Attribute1 IS NULL)))
180 AND ( (Recinfo.attribute2 = X_Attribute2)
181 OR ( (Recinfo.attribute2 IS NULL)
182 AND (X_Attribute2 IS NULL)))
183 AND ( (Recinfo.attribute3 = X_Attribute3)
184 OR ( (Recinfo.attribute3 IS NULL)
185 AND (X_Attribute3 IS NULL)))
186 AND ( (Recinfo.attribute4 = X_Attribute4)
187 OR ( (Recinfo.attribute4 IS NULL)
188 AND (X_Attribute4 IS NULL)))
189 AND ( (Recinfo.attribute5 = X_Attribute5)
190 OR ( (Recinfo.attribute5 IS NULL)
191 AND (X_Attribute5 IS NULL)))
192 AND ( (Recinfo.attribute6 = X_Attribute6)
193 OR ( (Recinfo.attribute6 IS NULL)
194 AND (X_Attribute6 IS NULL)))
195 AND ( (Recinfo.attribute7 = X_Attribute7)
196 OR ( (Recinfo.attribute7 IS NULL)
197 AND (X_Attribute7 IS NULL)))
198 AND ( (Recinfo.attribute8 = X_Attribute8)
199 OR ( (Recinfo.attribute8 IS NULL)
200 AND (X_Attribute8 IS NULL)))
201 AND ( (Recinfo.attribute9 = X_Attribute9)
202 OR ( (Recinfo.attribute9 IS NULL)
203 AND (X_Attribute9 IS NULL)))
204 AND ( (Recinfo.attribute10 = X_Attribute10)
205 OR ( (Recinfo.attribute10 IS NULL)
206 AND (X_Attribute10 IS NULL)))
207 AND ( (Recinfo.attribute11 = X_Attribute11)
208 OR ( (Recinfo.attribute11 IS NULL)
209 AND (X_Attribute11 IS NULL)))
210 AND ( (Recinfo.attribute12 = X_Attribute12)
211 OR ( (Recinfo.attribute12 IS NULL)
212 AND (X_Attribute12 IS NULL)))
213 AND ( (Recinfo.attribute13 = X_Attribute13)
214 OR ( (Recinfo.attribute13 IS NULL)
215 AND (X_Attribute13 IS NULL)))
216 AND ( (Recinfo.attribute14 = X_Attribute14)
217 OR ( (Recinfo.attribute14 IS NULL)
218 AND (X_Attribute14 IS NULL)))
219 AND ( (Recinfo.attribute15 = X_Attribute15)
220 OR ( (Recinfo.attribute15 IS NULL)
221 AND (X_Attribute15 IS NULL)))
222 ) then
223 return;
224 else
225 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
226 APP_EXCEPTION.RAISE_EXCEPTION;
227 end if;
228 END Lock_Row;
229
230 PROCEDURE Update_Row(X_Rowid VARCHAR2,
231 X_Approval_List_Id NUMBER,
232 X_Employee_Id NUMBER,
233 X_Sequence1 NUMBER,
234 X_Sequence2 NUMBER DEFAULT NULL,
235 X_Description VARCHAR2 DEFAULT NULL ,
236 X_Disable_Date DATE DEFAULT NULL,
237 X_Attribute_Category VARCHAR2 DEFAULT NULL ,
238 X_Attribute1 VARCHAR2 DEFAULT NULL ,
239 X_Attribute2 VARCHAR2 DEFAULT NULL ,
240 X_Attribute3 VARCHAR2 DEFAULT NULL ,
241 X_Attribute4 VARCHAR2 DEFAULT NULL ,
242 X_Attribute5 VARCHAR2 DEFAULT NULL ,
243 X_Attribute6 VARCHAR2 DEFAULT NULL ,
244 X_Attribute7 VARCHAR2 DEFAULT NULL ,
245 X_Attribute8 VARCHAR2 DEFAULT NULL ,
246 X_Attribute9 VARCHAR2 DEFAULT NULL ,
247 X_Attribute10 VARCHAR2 DEFAULT NULL ,
248 X_Attribute11 VARCHAR2 DEFAULT NULL ,
249 X_Attribute12 VARCHAR2 DEFAULT NULL ,
250 X_Attribute13 VARCHAR2 DEFAULT NULL ,
251 X_Attribute14 VARCHAR2 DEFAULT NULL ,
252 X_Attribute15 VARCHAR2 DEFAULT NULL ,
253 X_Last_Update_Login NUMBER DEFAULT NULL,
254 X_Last_Update_Date DATE,
255 X_Last_Updated_By NUMBER
256 ) IS
257
258 l_old_employee_id NUMBER; -- Bug 3817690
259
260 BEGIN
261 -- Bug 3817690
262 -- Fetch the employee id for the row to be updated
263 SELECT Employee_Id
264 INTO l_old_employee_id
265 FROM eng_ecn_approvers
266 WHERE rowid = X_rowid;
267
268 UPDATE eng_ecn_approvers
269 SET
270 approval_list_id = X_Approval_List_Id,
271 employee_id = X_Employee_Id,
272 sequence1 = X_Sequence1,
273 sequence2 = X_Sequence2,
274 description = X_Description,
275 disable_date = X_Disable_Date,
276 attribute_category = X_Attribute_Category,
277 attribute1 = X_Attribute1,
278 attribute2 = X_Attribute2,
279 attribute3 = X_Attribute3,
280 attribute4 = X_Attribute4,
281 attribute5 = X_Attribute5,
282 attribute6 = X_Attribute6,
283 attribute7 = X_Attribute7,
284 attribute8 = X_Attribute8,
285 attribute9 = X_Attribute9,
286 attribute10 = X_Attribute10,
287 attribute11 = X_Attribute11,
288 attribute12 = X_Attribute12,
289 attribute13 = X_Attribute13,
290 attribute14 = X_Attribute14,
291 attribute15 = X_Attribute15,
292 last_update_login = X_Last_Update_Login,
293 last_update_date = X_Last_Update_Date,
294 last_updated_by = X_Last_Updated_By
295 WHERE rowid = X_rowid;
296 if (SQL%NOTFOUND) then
297 RAISE NO_DATA_FOUND;
298 end if;
299 -- Bug 3817690
300 -- If the employee itself is being updated, then disable the old employee
301 IF (l_old_employee_id <> X_Employee_Id)
302 THEN
303 wf_local_synch.propagate_user_role(
304 p_user_orig_system => 'PER',
305 p_user_orig_system_id => l_old_employee_id,
306 p_role_orig_system => 'ENG_LIST',
307 p_role_orig_system_id => x_approval_List_Id,
308 p_start_date => null,
309 p_expiration_date => sysdate,
310 p_overwrite => TRUE);
311 END IF;
312
313 wf_local_synch.propagate_user_role(
314 p_user_orig_system => 'PER',
315 p_user_orig_system_id => x_employee_Id,
316 p_role_orig_system => 'ENG_LIST',
317 p_role_orig_system_id => x_approval_List_Id,
318 p_start_date => null,
319 p_expiration_date => X_Disable_Date,
320 p_overwrite => TRUE);
321
322 END Update_Row;
323
324 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
325 l_employee_id number;
326 l_approval_list_id number;
327 l_username varchar2(320);-- adhachol
328 l_rolename varchar2(320);-- adhachol
329 l_origSystem varchar2(30);--adhachol
330 l_origSystemID number;--adhachol
331
332 BEGIN
333
334 select employee_id, approval_list_id
335 into l_employee_id, l_approval_list_id
336 from eng_ecn_approvers
337 where rowid = X_Rowid ;
338
339 DELETE FROM eng_ecn_approvers
340 WHERE rowid = X_Rowid;
341 if (SQL%NOTFOUND) then
342 RAISE NO_DATA_FOUND;
343 end if;
344 /*
345 -- adhachol adding Bug #3342686
346 select name into l_rolename
347 from wf_roles
348 where orig_system = 'ENG_LIST'
349 and orig_system_id = l_approval_list_id;
350
351 begin
352
353 select name into l_username
354 from wf_roles
355 where orig_system = 'PER'
356 and orig_system_id = l_employee_id;
357 l_origSystem := 'PER';
358 l_origSystemId := l_employee_id;
359 exception
360 when NO_DATA_FOUND then
361 --Check for possible PER
362 SELECT user_name, employee_id, 'PER'
363 INTO l_userName, l_origSystemID, l_origSystem
364 FROM FND_USER
365 WHERE USER_ID =l_employee_id;
366
367 end;
368
369 WF_DIRECTORY.RemoveUserRole(user_name => l_username,
370 role_name => l_rolename,
371 user_orig_system => l_origSystem,
372 user_orig_system_id => l_origSystemId,
373 role_orig_system => 'ENG_LIST',
374 role_orig_system_id => l_approval_list_id);
375 -- adding ends here Bug #3342686
376 */
377
378 wf_local_synch.propagate_user_role(
379 p_user_orig_system => 'PER',
380 p_user_orig_system_id => l_employee_id,
381 p_role_orig_system => 'ENG_LIST',
382 p_role_orig_system_id => l_approval_list_id,
383 p_start_date => null,
384 p_expiration_date => sysdate,--);
385 p_overwrite => TRUE); -- Bug 3817690
386
387 END Delete_Row;
388 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
389 X_Approval_List_Id NUMBER,
390 X_Sequence1 NUMBER) IS
391 DUMMY NUMBER;
392 BEGIN
393 SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
394 (SELECT 1 FROM ENG_ECN_APPROVERS
395 WHERE
396 (Approval_List_Id = X_Approval_List_Id)
397 AND (Sequence1 = X_Sequence1)
398 AND ((X_Rowid is NULL) or (ROWID <> X_Rowid))
399 );
400 EXCEPTION
401 WHEN NO_DATA_FOUND THEN
402 FND_MESSAGE.SET_NAME('ENG', 'ENG_SEQUENCE_ENTER');
403 APP_EXCEPTION.RAISE_EXCEPTION;
404 END Check_Unique;
405
406 END ENG_ECN_APPROVERS_PKG ;