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:
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;
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;
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;
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:
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;
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
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;
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
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',
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',
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:
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
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;
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;
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;
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
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)
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;
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
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',
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',
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:
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 ;
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;
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;
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
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:
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;
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',