[Home] [Help]
PACKAGE BODY: APPS.ENG_ECN_APPROVAL_LISTS_PKG
Source
1 PACKAGE BODY ENG_ECN_APPROVAL_LISTS_PKG as
2 /*$Header: ENGAPPLB.pls 115.7 2004/05/11 13:26:01 amalviya ship $ */
3
4
5 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
6 X_Approval_List_Id IN OUT NOCOPY NUMBER,
7 X_Approval_List_Name VARCHAR2,
8 X_Disable_Date DATE DEFAULT NULL,
9 X_Description VARCHAR2 DEFAULT NULL,
10 X_Attribute_Category VARCHAR2 DEFAULT NULL,
11 X_Attribute1 VARCHAR2 DEFAULT NULL,
12 X_Attribute2 VARCHAR2 DEFAULT NULL,
13 X_Attribute3 VARCHAR2 DEFAULT NULL,
14 X_Attribute4 VARCHAR2 DEFAULT NULL,
15 X_Attribute5 VARCHAR2 DEFAULT NULL,
16 X_Attribute6 VARCHAR2 DEFAULT NULL,
17 X_Attribute7 VARCHAR2 DEFAULT NULL,
18 X_Attribute8 VARCHAR2 DEFAULT NULL,
19 X_Attribute9 VARCHAR2 DEFAULT NULL,
20 X_Attribute10 VARCHAR2 DEFAULT NULL,
21 X_Attribute11 VARCHAR2 DEFAULT NULL,
22 X_Attribute12 VARCHAR2 DEFAULT NULL,
23 X_Attribute13 VARCHAR2 DEFAULT NULL,
24 X_Attribute14 VARCHAR2 DEFAULT NULL,
25 X_Attribute15 VARCHAR2 DEFAULT NULL,
26 X_Creation_Date DATE,
27 X_Created_By NUMBER,
28 X_Last_Update_Login NUMBER DEFAULT NULL,
29 X_Last_Update_Date DATE,
30 X_Last_Updated_By NUMBER
31 ) IS
32 CURSOR C IS SELECT rowid FROM eng_ecn_approval_lists
33 WHERE approval_list_id = X_Approval_List_Id;
34 CURSOR C2 IS SELECT eng_ecn_approval_lists_s.nextval FROM dual;
35
36 myList wf_parameter_list_t;
37 l_username varchar2(320);
38 BEGIN
39 if (X_Approval_List_Id is NULL) then
40 OPEN C2;
41 FETCH C2 INTO X_Approval_List_Id;
42 CLOSE C2;
43 end if;
44 INSERT INTO eng_ecn_approval_lists(
45 approval_list_id,
46 approval_list_name,
47 disable_date,
48 description,
49 attribute_category,
50 attribute1,
51 attribute2,
52 attribute3,
53 attribute4,
54 attribute5,
55 attribute6,
56 attribute7,
57 attribute8,
58 attribute9,
59 attribute10,
60 attribute11,
61 attribute12,
62 attribute13,
63 attribute14,
64 attribute15,
65 creation_date,
66 created_by,
67 last_update_login,
68 last_update_date,
69 last_updated_by
70 ) VALUES (
71 X_Approval_List_Id,
72 X_Approval_List_Name,
73 X_Disable_Date,
74 X_Description,
75 X_Attribute_Category,
76 X_Attribute1,
77 X_Attribute2,
78 X_Attribute3,
79 X_Attribute4,
80 X_Attribute5,
81 X_Attribute6,
82 X_Attribute7,
83 X_Attribute8,
84 X_Attribute9,
85 X_Attribute10,
86 X_Attribute11,
87 X_Attribute12,
88 X_Attribute13,
89 X_Attribute14,
90 X_Attribute15,
91 X_Creation_Date,
92 X_Created_By,
93 X_Last_Update_Login,
94 X_Last_Update_Date,
95 X_Last_Updated_By
96 );
97 OPEN C;
98 FETCH C INTO X_Rowid;
99 if (C%NOTFOUND) then
100 CLOSE C;
101 RAISE NO_DATA_FOUND;
102 end if;
103 CLOSE C;
104
105 -- construct attribute list --
106
107 l_username := 'ENG_LIST:'||to_char(X_Approval_List_Id);
108 wf_event.AddParameterToList('DISPLAYNAME', X_Approval_List_Name, mylist);
109 wf_event.AddParameterToList('DESCRIPTION', X_Description, mylist);
110 wf_event.AddParameterToList('USER_NAME', l_username, mylist);
111
112
113 -- added for synchronize the work flow
114 wf_local_synch.propagate_role(p_orig_system=>'ENG_LIST' ,
115 p_orig_system_id =>X_Approval_List_Id,
116 p_attributes => mylist,
117 p_start_date =>null,
118 p_expiration_date =>X_Disable_Date);
119
120 END Insert_Row;
121 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
122 X_Approval_List_Id NUMBER,
123 X_Approval_List_Name VARCHAR2,
124 X_Disable_Date DATE DEFAULT NULL,
125 X_Description VARCHAR2 DEFAULT NULL ,
126 X_Attribute_Category VARCHAR2 DEFAULT NULL ,
127 X_Attribute1 VARCHAR2 DEFAULT NULL ,
128 X_Attribute2 VARCHAR2 DEFAULT NULL ,
129 X_Attribute3 VARCHAR2 DEFAULT NULL ,
130 X_Attribute4 VARCHAR2 DEFAULT NULL ,
131 X_Attribute5 VARCHAR2 DEFAULT NULL ,
132 X_Attribute6 VARCHAR2 DEFAULT NULL ,
133 X_Attribute7 VARCHAR2 DEFAULT NULL ,
134 X_Attribute8 VARCHAR2 DEFAULT NULL ,
135 X_Attribute9 VARCHAR2 DEFAULT NULL ,
136 X_Attribute10 VARCHAR2 DEFAULT NULL ,
137 X_Attribute11 VARCHAR2 DEFAULT NULL ,
138 X_Attribute12 VARCHAR2 DEFAULT NULL ,
139 X_Attribute13 VARCHAR2 DEFAULT NULL ,
140 X_Attribute14 VARCHAR2 DEFAULT NULL ,
141 X_Attribute15 VARCHAR2 DEFAULT NULL
142 ) IS
143 CURSOR C IS
144 SELECT *
145 FROM eng_ecn_approval_lists
146 WHERE rowid = X_Rowid
147 FOR UPDATE of Approval_List_Id NOWAIT;
148 Recinfo C%ROWTYPE;
149 BEGIN
150 OPEN C;
151 FETCH C INTO Recinfo;
152 if (C%NOTFOUND) then
153 CLOSE C;
154 RAISE NO_DATA_FOUND;
155 end if;
156 CLOSE C;
157 if (
158 ( (Recinfo.approval_list_id = X_Approval_List_Id)
159 OR ( (Recinfo.approval_list_id IS NULL)
160 AND (X_Approval_List_Id IS NULL)))
161 AND ( (Recinfo.approval_list_name = X_Approval_List_Name)
162 OR ( (Recinfo.approval_list_name IS NULL)
163 AND (X_Approval_List_Name IS NULL)))
164 AND ( (Recinfo.disable_date = X_Disable_Date)
165 OR ( (Recinfo.disable_date IS NULL)
166 AND (X_Disable_Date IS NULL)))
167 AND ( (Recinfo.description = X_Description)
168 OR ( (Recinfo.description IS NULL)
169 AND (X_Description IS NULL)))
170 AND ( (Recinfo.attribute_category = X_Attribute_Category)
171 OR ( (Recinfo.attribute_category IS NULL)
172 AND (X_Attribute_Category IS NULL)))
173 AND ( (Recinfo.attribute1 = X_Attribute1)
174 OR ( (Recinfo.attribute1 IS NULL)
175 AND (X_Attribute1 IS NULL)))
176 AND ( (Recinfo.attribute2 = X_Attribute2)
177 OR ( (Recinfo.attribute2 IS NULL)
178 AND (X_Attribute2 IS NULL)))
179 AND ( (Recinfo.attribute3 = X_Attribute3)
180 OR ( (Recinfo.attribute3 IS NULL)
181 AND (X_Attribute3 IS NULL)))
182 AND ( (Recinfo.attribute4 = X_Attribute4)
183 OR ( (Recinfo.attribute4 IS NULL)
184 AND (X_Attribute4 IS NULL)))
185 AND ( (Recinfo.attribute5 = X_Attribute5)
186 OR ( (Recinfo.attribute5 IS NULL)
187 AND (X_Attribute5 IS NULL)))
188 AND ( (Recinfo.attribute6 = X_Attribute6)
189 OR ( (Recinfo.attribute6 IS NULL)
190 AND (X_Attribute6 IS NULL)))
191 AND ( (Recinfo.attribute7 = X_Attribute7)
192 OR ( (Recinfo.attribute7 IS NULL)
193 AND (X_Attribute7 IS NULL)))
194 AND ( (Recinfo.attribute8 = X_Attribute8)
195 OR ( (Recinfo.attribute8 IS NULL)
196 AND (X_Attribute8 IS NULL)))
197 AND ( (Recinfo.attribute9 = X_Attribute9)
198 OR ( (Recinfo.attribute9 IS NULL)
199 AND (X_Attribute9 IS NULL)))
200 AND ( (Recinfo.attribute10 = X_Attribute10)
201 OR ( (Recinfo.attribute10 IS NULL)
202 AND (X_Attribute10 IS NULL)))
203 AND ( (Recinfo.attribute11 = X_Attribute11)
204 OR ( (Recinfo.attribute11 IS NULL)
205 AND (X_Attribute11 IS NULL)))
206 AND ( (Recinfo.attribute12 = X_Attribute12)
207 OR ( (Recinfo.attribute12 IS NULL)
208 AND (X_Attribute12 IS NULL)))
209 AND ( (Recinfo.attribute13 = X_Attribute13)
210 OR ( (Recinfo.attribute13 IS NULL)
211 AND (X_Attribute13 IS NULL)))
212 AND ( (Recinfo.attribute14 = X_Attribute14)
213 OR ( (Recinfo.attribute14 IS NULL)
214 AND (X_Attribute14 IS NULL)))
215 AND ( (Recinfo.attribute15 = X_Attribute15)
216 OR ( (Recinfo.attribute15 IS NULL)
217 AND (X_Attribute15 IS NULL)))
218 ) then
219 return;
220 else
221 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
222 APP_EXCEPTION.RAISE_EXCEPTION;
223 end if;
224 END Lock_Row;
225 PROCEDURE Update_Row(X_Rowid VARCHAR2,
226 X_Approval_List_Id NUMBER,
227 X_Approval_List_Name VARCHAR2,
228 X_Disable_Date DATE DEFAULT NULL,
229 X_Description VARCHAR2 DEFAULT NULL ,
230 X_Attribute_Category VARCHAR2 DEFAULT NULL ,
231 X_Attribute1 VARCHAR2 DEFAULT NULL ,
232 X_Attribute2 VARCHAR2 DEFAULT NULL ,
233 X_Attribute3 VARCHAR2 DEFAULT NULL ,
234 X_Attribute4 VARCHAR2 DEFAULT NULL ,
235 X_Attribute5 VARCHAR2 DEFAULT NULL ,
236 X_Attribute6 VARCHAR2 DEFAULT NULL ,
237 X_Attribute7 VARCHAR2 DEFAULT NULL ,
238 X_Attribute8 VARCHAR2 DEFAULT NULL ,
239 X_Attribute9 VARCHAR2 DEFAULT NULL ,
240 X_Attribute10 VARCHAR2 DEFAULT NULL ,
241 X_Attribute11 VARCHAR2 DEFAULT NULL ,
242 X_Attribute12 VARCHAR2 DEFAULT NULL ,
243 X_Attribute13 VARCHAR2 DEFAULT NULL ,
244 X_Attribute14 VARCHAR2 DEFAULT NULL ,
245 X_Attribute15 VARCHAR2 DEFAULT NULL ,
246 X_Last_Update_Login NUMBER DEFAULT NULL,
247 X_Last_Update_Date DATE,
248 X_Last_Updated_By NUMBER
249 ) IS
250
251 /* Added for Bug 3511260 */
252
253 Cursor user_roles is
254 select START_DATE,
255 USER_ORIG_SYSTEM,USER_ORIG_SYSTEM_ID
256 from WF_LOCAL_USER_ROLES
257 where role_orig_system= 'ENG_LIST' and
258 role_orig_system_id = X_Approval_List_Id;
259 myList wf_parameter_list_t;
260 l_username varchar2(320);
261 lemail WF_LOCAL_ROLES.EMAIL_ADDRESS%type;
262 lfax WF_LOCAL_ROLES.FAX%type;
263 BEGIN
264 UPDATE eng_ecn_approval_lists
265 SET
266 approval_list_id = X_Approval_List_Id,
267 approval_list_name = X_Approval_List_Name,
268 disable_date = X_Disable_Date,
269 description = X_Description,
270 attribute_category = X_Attribute_Category,
271 attribute1 = X_Attribute1,
272 attribute2 = X_Attribute2,
273 attribute3 = X_Attribute3,
274 attribute4 = X_Attribute4,
275 attribute5 = X_Attribute5,
276 attribute6 = X_Attribute6,
277 attribute7 = X_Attribute7,
278 attribute8 = X_Attribute8,
279 attribute9 = X_Attribute9,
280 attribute10 = X_Attribute10,
281 attribute11 = X_Attribute11,
282 attribute12 = X_Attribute12,
283 attribute13 = X_Attribute13,
284 attribute14 = X_Attribute14,
285 attribute15 = X_Attribute15,
286 last_update_login = X_Last_Update_Login,
287 last_update_date = X_Last_Update_Date,
288 last_updated_by = X_Last_Updated_By
289 WHERE rowid = X_rowid;
290 if (SQL%NOTFOUND) then
291 RAISE NO_DATA_FOUND;
292 end if;
293
294 l_username := 'ENG_LIST:'||to_char(X_Approval_List_Id);
295 /* added for bug 3511260 */
296
297 select EMAIL_ADDRESS,FAX
298 into lemail,lfax
299 from WF_LOCAL_ROLES
300 where NAME = l_username and
301 ORIG_SYSTEM = 'ENG_LIST' and
302 ORIG_SYSTEM_ID = X_Approval_List_Id;
303
304
305 -- construct attribute list --
306 wf_event.AddParameterToList('DISPLAYNAME', X_Approval_List_Name, mylist);
307 wf_event.AddParameterToList('DESCRIPTION', X_Description, mylist);
308 wf_event.AddParameterToList('USER_NAME', l_username, mylist);
309 wf_event.AddParameterToList('MAIL', lemail, mylist);
310 wf_event.AddParameterToList('FACSIMILETELEPHONENUMBER', lfax, mylist);
311 wf_event.AddParameterToList('WFSYNCH_OVERWRITE','TRUE', mylist);
312
313
314 -- added for synchronize the work flow
315 wf_local_synch.propagate_role(p_orig_system=>'ENG_LIST' ,
316 p_orig_system_id =>X_Approval_List_Id,
317 p_attributes => mylist,
318 p_start_date =>null,
319 p_expiration_date =>X_Disable_Date);
320
321 /* Added for Bug 3511260
322 When approval list is reactivated all the approvers in the list retain
323 the original expirtaion date. So Notifications are not sent to users even
324 though the Role is re acttivated. Below code is added to update the User
325 role relation in the table*/
326
327 for c in user_roles loop
328
329 wf_local_synch.propagate_user_role(
330
331 p_user_orig_system => c.USER_ORIG_SYSTEM,
332 p_user_orig_system_id => c.USER_ORIG_SYSTEM_ID,
333 p_role_orig_system => 'ENG_LIST',
334 p_role_orig_system_id => x_approval_List_Id,
335 p_start_date => c.START_DATE,
336 p_overwrite => TRUE,
337 p_expiration_date => X_Disable_Date);
338
339 end loop;
340
341 END Update_Row;
342
343 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
344 x_approval_list_id number;
345 myList wf_parameter_list_t;
346
347 BEGIN
348
349 select approval_list_id into x_approval_list_id
350 from eng_ecn_approval_lists
351 where rowid = X_Rowid;
352
353 DELETE FROM eng_ecn_approval_lists
354 WHERE rowid = X_Rowid;
355 if (SQL%NOTFOUND) then
356 RAISE NO_DATA_FOUND;
357 end if;
358
359 -- construct attribute list --
360 wf_event.AddParameterToList('DELETE','TRUE', mylist);
361
362 -- added for synchronize the work flow
363 wf_local_synch.propagate_role(p_orig_system=> 'ENG_LIST' ,
364 p_orig_system_id => X_Approval_List_Id,
365 p_attributes => mylist,
366 p_start_date => null,
367 p_expiration_date => sysdate);
368
369
370 END Delete_Row;
371
372 PROCEDURE Check_References(X_Approval_List_Id NUMBER) IS
373 DUMMY NUMBER;
374 BEGIN
375 SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
376 (SELECT 1 FROM ENG_ENGINEERING_CHANGES
377 WHERE Approval_List_Id = X_Approval_List_Id
378 );
379 EXCEPTION
380 WHEN NO_DATA_FOUND THEN
381 FND_MESSAGE.SET_NAME('ENG', 'LIST IN USE');
382 APP_EXCEPTION.RAISE_EXCEPTION;
383 END CHECK_REFERENCES;
384
385 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
386 X_Approval_List_Name VARCHAR2) IS
387 DUMMY NUMBER;
388 BEGIN
389 SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
390 (SELECT 1 FROM ENG_ECN_APPROVAL_LISTS
391 WHERE (Approval_List_Name = X_Approval_List_Name)
392 AND ((X_Rowid is NULL) or (ROWID <> X_Rowid))
393 );
394 EXCEPTION
395 WHEN NO_DATA_FOUND THEN
396 -- FND_MESSAGE.SET_NAME('ENG', 'Approval List Name Already Used');
397 APP_EXCEPTION.RAISE_EXCEPTION;
398 END Check_Unique;
399
400 END ENG_ECN_APPROVAL_LISTS_PKG ;