[Home] [Help]
PACKAGE BODY: APPS.GMS_NOTIFICATION_PKG
Source
1 PACKAGE BODY GMS_NOTIFICATION_PKG AS
2 --$Header: gmsawnob.pls 115.13 2003/03/06 05:34:41 gnema ship $
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Award_Id IN NUMBER,
6 X_Event_type IN VARCHAR2,
7 X_User_id IN NUMBER) IS
8
9 CURSOR C IS SELECT rowid FROM GMS_NOTIFICATIONS
10 WHERE Award_id = X_Award_id and
11 Event_type = X_Event_type and
12 User_id = X_User_id;
13
14 BEGIN
15 INSERT into GMS_NOTIFICATIONS(award_id,
16 event_type,
17 user_id)
18 values (X_award_id,
19 X_event_type,
20 X_user_id);
21
22 Open c;
23 Fetch c into X_rowid;
24 if (c%NOTFOUND) then
25 close c;
26 RAISE NO_DATA_FOUND;
27 END if;
28 CLOSE c;
29 END Insert_row;
30
31
32 PROCEDURE Lock_Row(X_Rowid IN VARCHAR2, --bug 2813856, removed OUT NOCOPY
33 X_Award_Id IN NUMBER,
34 X_Event_type IN VARCHAR2,
35 X_User_id IN NUMBER) IS
36
37 cursor c is select * from gms_notifications
38 where rowid = X_rowid
39 for update of Award_id, event_type, user_id nowait;
40 Recinfo c%rowtype;
41 BEGIN
42 open c;
43 fetch c into Recinfo;
44 if (c%NOTFOUND) then
45 CLOSE c;
46 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
47 APP_EXCEPTION.Raise_Exception;
48 END if;
49 CLOSE c;
50 if ( recinfo.award_id = X_award_id
51 and recinfo.event_type = X_event_type
52 and recinfo.user_id = X_user_id ) then
53 return;
54 else
55 FND_MESSAGE.set_name('FND','FORM_RECORD_CHANGED');
56 APP_EXCEPTION.Raise_Exception;
57 END if;
58
59 -- Added Exception for Bug:2662848
60 EXCEPTION
61 when OTHERS then
62 RAISE;
63 END Lock_row;
64
65
66 PROCEDURE Delete_Row(X_Rowid VARCHAR2) is
67 BEGIN
68 delete gms_notifications
69 where rowid = X_Rowid;
70 END Delete_row;
71
72
73 PROCEDURE Crt_default_person_events( x_err_code in out NOCOPY NUMBER,
74 x_err_stage in out NOCOPY VARCHAR2,
75 p_award_id INTEGER,
76 p_person_id INTEGER) IS
77 cursor report_event_cursor is
78 select distinct to_char(report_template_id)
79 from gms_default_reports_v
80 where award_id = p_award_id;
81
82 cursor fnd_user_cursor is
83 select user_id
84 from fnd_user
85 where employee_id = p_person_id;
86
87 l_row_id rowid;
88 l_user_id integer;
89 l_report_template_id varchar2(30);
90 user_not_yet_created exception;
91
92 BEGIN
93
94 x_err_code := 0;
95
96 open fnd_user_cursor;
97 fetch fnd_user_cursor into l_user_id;
98
99 if fnd_user_cursor%NOTFOUND then
100 close fnd_user_cursor;
101 raise user_not_yet_created;
102 END if;
103
104 close fnd_user_cursor;
105
106 -- Bug 1969587 : Added for Installment closeout notification
107
108 BEGIN
109
110 INSERT into GMS_NOTIFICATIONS(award_id,
111 event_type,
112 user_id)
113 values (p_award_id,
114 'INSTALLMENT_CLOSEOUT',
115 l_user_id);
116
117 EXCEPTION
118 when DUP_VAL_ON_INDEX then
119 null;
120 END;
121
122 BEGIN
123
124 INSERT into GMS_NOTIFICATIONS(award_id,
125 event_type,
126 user_id)
127 values (p_award_id,
128 'BUDGET_BASELINE',
129 l_user_id);
130
131 EXCEPTION
132 when DUP_VAL_ON_INDEX then
133 null;
134 END;
135
136 BEGIN
137
138 INSERT into GMS_NOTIFICATIONS(award_id,
139 event_type,
140 user_id)
141 values (p_award_id,
142 'INSTALLMENT_ACTIVE',
143 l_user_id);
144
145 EXCEPTION
146 when DUP_VAL_ON_INDEX then
147 null;
148 END;
149
150
151
152 open report_event_cursor;
153
154 LOOP
155 fetch report_event_cursor into l_report_template_id;
156 exit when report_event_cursor%notfound;
157 BEGIN
158
159 INSERT into GMS_NOTIFICATIONS(award_id,
160 event_type,
161 user_id)
162 values (p_award_id,
163 'REPORT_'||l_report_template_id,
164 l_user_id);
165
166 EXCEPTION
167 when DUP_VAL_ON_INDEX then
168 null;
169 END;
170
171 END LOOP;
172 close report_event_cursor;
173
174 EXCEPTION
175 when user_not_yet_created then
176 x_err_code := 2;
177 x_err_stage := 'GMS_FND_USER_NOT_CREATED';
178
179 -- fnd_message.set_name('GMS','GMS_FND_USER_NOT_CREATED');
180 -- fnd_message.set_token('PERSON_ID',to_char(p_person_id));
181 -- APP_EXCEPTION.raise_exception;
182
183 when others then
184 if report_event_cursor%isopen then
185 close report_Event_cursor;
186 end if;
187 if fnd_user_cursor%isopen then
188 close fnd_user_cursor;
189 end if;
190
191 gms_error_pkg.gms_message(x_err_name => 'GMS_UNEXPECTED_ERROR',
192 x_token_name1 => 'SQLCODE',
193 x_token_val1 => sqlcode,
194 x_token_name2 => 'SQLERRM',
195 x_token_val2 => sqlerrm,
196 x_err_code => x_err_code,
197 x_err_buff => x_err_stage);
198
199 APP_EXCEPTION.RAISE_EXCEPTION;
200
201 -- fnd_message.set_name('GMS','GMS_UNEXPECTED_ERROR');
202 -- fnd_message.set_token('PROGRAM_NAME','GMS_NOTIFICATIONS_PKG.CRT_DEFAULT_PERSON_EVENTS');
203 -- fnd_message.set_token('OERRNO',to_char(sqlcode));
204 -- fnd_message.set_token('OERRM',sqlerrm);
205 -- APP_EXCEPTION.raise_exception;
206 END crt_default_person_events;
207
208
209 PROCEDURE crt_default_report_events(x_err_code in out NOCOPY NUMBER,
210 x_err_stage in out NOCOPY VARCHAR2,
211 p_award_id integer,
212 p_report_template_id integer) is
213
214
215 l_report_template_id varchar2(20);
216 l_person_id integer;
217 l_user_id integer;
218
219 cursor fnd_user_cursor is
220 select user_id
221 from fnd_user
222 where employee_id = l_person_id;
223
224
225 cursor award_persons is
226 select person_id
227 from gms_personnel
228 where award_id = p_award_id and
229 trunc(sysdate) between start_date_active and nvl(end_date_active,to_date('01/01/4000','DD/MM/YYYY'));
230
231 user_not_yet_created exception;
232
233 BEGIN
234
235 x_err_code := 0;
236
237 l_report_template_id := to_char(p_report_template_id);
238
239 open award_persons;
240 LOOP
241 fetch award_persons into l_person_id;
242 exit when award_persons%NOTFOUND;
243
244 open fnd_user_cursor;
245 fetch fnd_user_cursor into l_user_id;
246 if fnd_user_cursor%NOTFOUND then
247 close fnd_user_cursor;
248 raise user_not_yet_created;
249 end if;
250 close fnd_user_cursor;
251 BEGIN
252 INSERT into GMS_NOTIFICATIONS(award_id,
253 event_type,
254 user_id)
255 values (p_award_id,
256 'REPORT_'||l_report_template_id,
257 l_user_id);
258
259 EXCEPTION
260 when DUP_VAL_ON_INDEX then
261 null;
262 END;
263 END LOOP;
264 EXCEPTION
265 when user_not_yet_created then
266 x_err_code := 2;
267 x_err_stage := 'GMS_FND_USER_NOT_CREATED';
268
269 -- fnd_message.set_name('GMS','GMS_FND_USER_NOT_CREATED');
270 -- fnd_message.set_token('PERSON_ID',to_char(l_person_id));
271 -- APP_EXCEPTION.raise_exception;
272 when others then
273 if award_persons%isopen then
274 close award_persons;
275 end if;
276 if fnd_user_cursor%isopen then
277 close fnd_user_cursor;
278 end if;
279
280 gms_error_pkg.gms_message(x_err_name => 'GMS_UNEXPECTED_ERROR',
281 x_token_name1 => 'SQLCODE',
282 x_token_val1 => sqlcode,
283 x_token_name2 => 'SQLERRM',
284 x_token_val2 => sqlerrm,
285 x_err_code => x_err_code,
286 x_err_buff => x_err_stage);
287
288 APP_EXCEPTION.RAISE_EXCEPTION;
289
290 -- fnd_message.set_name('GMS','GMS_UNEXPECTED_ERROR');
291 -- fnd_message.set_token('PROGRAM_NAME','GMS_NOTIFICATIONS_PKG.CRT_DEFAULT_REPORT_EVENTS');
292 -- fnd_message.set_token('OERRNO',to_char(sqlcode));
293 -- fnd_message.set_token('OERRM',sqlerrm);
294 -- app_exception.raise_exception;
295 END crt_default_report_events;
296
297
298 PROCEDURE Del_default_person_events(x_err_code in out NOCOPY NUMBER,
299 x_err_stage in out NOCOPY VARCHAR2,
300 p_award_id INTEGER,
301 p_person_id INTEGER) is
302
303 cursor fnd_user_cursor is
304 select user_id
305 from fnd_user
306 where employee_id = p_person_id;
307
308 l_user_id integer;
309 user_not_yet_created exception;
310 BEGIN
311
312 x_err_code := 0;
313
314 open fnd_user_cursor;
315 fetch fnd_user_cursor into l_user_id;
316 if fnd_user_cursor%NOTFOUND then
317 close fnd_user_cursor;
318 raise user_not_yet_created ;
319 end if;
320 close fnd_user_cursor;
321 delete gms_notifications
322 where user_id = l_user_id and
323 award_id = p_award_id and
324
325 -- Bug 1969587 : Installment closeout Notification
326 -- added in order not to delete the person if he still exists in gms_personnel with additional
327 -- award role.
328
329 not exists ( select 1
330 from gms_personnel
331 where award_id = p_award_id and
332 person_id = p_person_id
333 );
334
335
336
337
338 EXCEPTION
339 when user_not_yet_created then
340 x_err_code := 2;
341 x_err_stage := 'GMS_FND_USER_NOT_CREATED';
342
343 -- fnd_message.set_name('GMS','GMS_FND_USER_NOT_CREATED');
344 -- fnd_message.set_token('PERSON_ID',to_char(l_user_id));
345 -- APP_EXCEPTION.raise_exception;
346 when others then
347 gms_error_pkg.gms_message(x_err_name => 'GMS_UNEXPECTED_ERROR',
348 x_token_name1 => 'SQLCODE',
349 x_token_val1 => sqlcode,
350 x_token_name2 => 'SQLERRM',
351 x_token_val2 => sqlerrm,
352 x_err_code => x_err_code,
353 x_err_buff => x_err_stage);
354
355 APP_EXCEPTION.RAISE_EXCEPTION;
356
357 -- fnd_message.set_name('GMS','GMS_UNEXPECTED_ERROR');
358 -- fnd_message.set_token('PROGRAM_NAME','GMS_NOTIFICATIONS_PKG.DEL_DEFAULT_PERSON_EVENTS');
359 -- fnd_message.set_token('OERRNO',to_char(sqlcode));
360 -- fnd_message.set_token('OERRM',sqlerrm);
361 -- app_exception.raise_exception;
362 END;
363
364
365 PROCEDURE Del_default_report_events(x_err_code in out NOCOPY NUMBER,
366 x_err_stage in out NOCOPY VARCHAR2,
367 p_award_id INTEGER,
368 p_report_template_id INTEGER) is
369 BEGIN
370
371 x_err_code := 0;
372
373 delete gms_notifications
374 where award_id = p_award_id and
375 event_type = 'REPORT_'||to_char(p_report_template_id);
376
377 EXCEPTION
378 when others then
379 gms_error_pkg.gms_message(x_err_name => 'GMS_UNEXPECTED_ERROR',
380 x_token_name1 => 'SQLCODE',
381 x_token_val1 => sqlcode,
382 x_token_name2 => 'SQLERRM',
383 x_token_val2 => sqlerrm,
384 x_err_code => x_err_code,
385 x_err_buff => x_err_stage);
386
387 APP_EXCEPTION.RAISE_EXCEPTION;
388
389 -- fnd_message.set_name('GMS','GMS_UNEXPECTED_ERROR');
390 -- fnd_message.set_token('PROGRAM_NAME','GMS_NOTIFICATIONS_PKG.DEL_DEFAULT_REPORT_EVENTS');
391 -- fnd_message.set_token('OERRNO',to_char(sqlcode));
392 -- fnd_message.set_token('OERRM',sqlerrm);
393 -- app_exception.raise_exception;
394 END;
395
396
397 END GMS_NOTIFICATION_PKG;