[Home] [Help]
PACKAGE BODY: APPS.NTN
Source
1 package body ntn as
2 /* $Header: PONSENDB.pls 115.1 2002/11/26 19:50:40 sbull ship $ */
3
4
5
6 procedure Send_Notification(
7 p_employee_id number,
8 p_message_name varchar2,
9 object_id number,
10 priority number default 3,
11 deletable varchar2 default 'Y',
12 from_id number default NULL,
13 p_application_id number default 0,
14 doc_type varchar2 default NULL,
15 doc_number varchar2 default NULL,
16 amount number default NULL,
17 currency varchar2 default NULL,
18 note varchar2 default NULL,
19 start_effective_date date default NULL,
20 end_effective_date date default NULL,
21 doc_creation_date date default NULL,
22 date1 date default NULL,
23 date2 date default NULL,
24 date3 date default NULL,
25 attribute_array char_array,
26 array_lb number,
27 array_ub number,
28 return_code out NOCOPY number,
29 notification_id out NOCOPY number)
30 is
31 stmt varchar2(20000);
32 ix integer;
33 new_id integer;
34 num_attrs integer;
35 num_attrs_passed integer;
36 c integer;
37 rows_processed integer;
38 status varchar2(30):='NEW';
39 user_id Number := FND_PROFILE.Value('USER_ID');
40 login_id Number := FND_PROFILE.Value('LOGIN_ID');
41 xxx rowid;
42 off_line varchar2(1);
43 on_line integer;
44
45 x_app_short_name varchar2(50); /* Bug458110. gtummala. 3/14/97.*/
46 xxx_msg varchar2(2000):=NULL; /* Added these 3 variables */
47 x_progress VARCHAR2(3) := ''; /* */
48
49 begin
50 notification_id:=NULL;
51 return_code:=1;
52 dbms_output.put_line(p_message_name);
53 -- Check for valid message name
54
55 /* Bug458110. gtummala. 3/14/97.
56 * Previously the code was:
57 * select rowid into xxx
58 * from fnd_new_messages where application_id = p_application_id
59 * and message_name=p_message_name;
60 *
61 * This causes too many rows to be returned in a translated install
62 * since we are not joining on language_code. Thus in any translated
63 * db notifications were not working.
64 * We should be selecting from the fnd message dictionary api's.
65 * So using the fnd_message.get_string api. It returns the translated
66 * message or null if the message can't be found. The null is what
67 * we need to check for.
68 */
69
70 x_progress:='010';
71 select application_short_name
72 into x_app_short_name
73 from fnd_application
74 where application_id = p_application_id;
75
76 dbms_output.put_line('Application short name found');
77
78 xxx_msg := fnd_message.get_string(x_app_short_name, p_message_name);
79 x_progress:='020';
80
81 dbms_output.put_line('xxx_msg =' || xxx_msg);
82
83 if (xxx_msg is NULL) then
84 RAISE NO_DATA_FOUND;
85 end if;
86
87 x_progress:='030';
88
89 dbms_output.put_line(to_char(p_employee_id));
90
91
92 -- Check for valid employee id
93 select rowid into xxx
94 from hr_employees where employee_id=p_employee_id;
95
96 -- Check for off-line users
97
98 /* DEBUG. gtummala. 9/16/97.
99 * person_type isn't supported in the sep10 install.
100 * Just to make this package compile I'm going to take out
101 * this whole sql stmt and hardcode on_line. We're going
102 * to drop this package anyway in a week.
103 */
104
105 /* select count(1) into on_line
106 from fnd_user where employee_id = p_employee_id
107 and person_type = 'E'
108 and sysdate < NVL(end_date, sysdate +1);
109 */
110
111 on_line :=1;
112
113
114 if (on_line = 0) then
115 off_line := 'Y';
116 else
117 off_line := 'N';
118 end if;
119
120 dbms_output.put_line(off_line);
121
122 stmt:= 'insert into fnd_notifications'
123 || '(notification_id, employee_id, message_name, priority, status, object_id, deletable, from_id, application_id, doc_type, ' ||
124 'doc_number, amount, currency, note, start_effective_date, end_effective_date, doc_creation_date, date1, date2, date3, off_line,
125 '
126 || 'attribute1, attribute2, attribute3, attribute4, attribute5,'
127 || 'attribute6, attribute7, attribute8, attribute9, attribute10,'
128 || 'attribute11, attribute12, attribute13, attribute14, attribute15,'
129 || 'attribute16, attribute17, attribute18, attribute19, attribute20,'
130 || 'attribute21, attribute22, attribute23, attribute24, attribute25,'
131 || 'attribute26, attribute27, attribute28, attribute29, attribute30,'
132 || 'attribute31, attribute32, '
133 || 'last_update_date, last_updated_by, last_update_login, '
134 || 'creation_date, created_by)'
135 || ' values '
136 || '(fnd_notifications_s.nextval, :employee_id, :message_name, :priority, :status, :object_id, :deletable, :from_id, :application_id, ' ||
137 ':doc_type, :doc_number, :amount, :currency, :note, :start_effective_date, :end_effective_date, :doc_creation_date, :date1, :date2, :date3, :off_line, '
138 || ':a1, :a2, :a3, :a4, :a5, :a6, :a7, :a8, :a9, :a10,'
139 || ':a11, :a12, :a13, :a14, :a15, :a16,'
140 || ':a17, :a18, :a19, :a20, :a21, :a22,'
141 || ':a23, :a24, :a25, :a26, :a27, :a28,'
142 || ':a29, :a30, :a31, :a32,'
143 || 'sysdate, :user_id, :login_id, sysdate, :user_id)';
144
145 -- stmt:= stmt||' '||new_id||',';
146 -- stmt:= stmt||' '||employee_id||',';
147 -- stmt:= stmt||''''||p_message_name||''',';
148 c := sys.dbms_sql.open_cursor;
149 sys.dbms_sql.parse(c, stmt, dbms_sql.native);
150 sys.dbms_sql.bind_variable(c,'employee_id', p_employee_id);
151 sys.dbms_sql.bind_variable(c,'message_name', p_message_name);
152 sys.dbms_sql.bind_variable(c,'object_id', object_id);
153 sys.dbms_sql.bind_variable(c,'deletable', deletable);
154 sys.dbms_sql.bind_variable(c,'from_id', from_id);
155 sys.dbms_sql.bind_variable(c,'application_id', p_application_id);
156 sys.dbms_sql.bind_variable(c,'doc_type', doc_type);
157 sys.dbms_sql.bind_variable(c,'doc_number', doc_number);
158 sys.dbms_sql.bind_variable(c,'amount', amount);
159 sys.dbms_sql.bind_variable(c,'currency', currency);
160 sys.dbms_sql.bind_variable(c,'note', note);
161 sys.dbms_sql.bind_variable(c,'start_effective_date', start_effective_date);
162 sys.dbms_sql.bind_variable(c,'end_effective_date', end_effective_date);
163 sys.dbms_sql.bind_variable(c,'doc_creation_date', doc_creation_date);
164 sys.dbms_sql.bind_variable(c,'date1', date1);
165 sys.dbms_sql.bind_variable(c,'date2', date2);
166 sys.dbms_sql.bind_variable(c,'date3', date3);
167 sys.dbms_sql.bind_variable(c,'off_line', off_line);
168 sys.dbms_sql.bind_variable(c,'priority', priority);
169 sys.dbms_sql.bind_variable(c,'status', status);
170 sys.dbms_sql.bind_variable(c,'user_id', user_id);
171 sys.dbms_sql.bind_variable(c,'login_id', login_id);
172
173 dbms_output.put_line('after message insert');
174 num_attrs := 32;
175 for ix in 1..num_attrs loop
176 num_attrs_passed := array_ub - array_lb +1;
177 if (ix <= num_attrs_passed) then
178 sys.dbms_sql.bind_variable(c,'a' || (ix), attribute_array(array_lb +ix -1));
179 else
180 sys.dbms_sql.bind_variable(c,'a' || (ix), '');
181 end if ;
182
183 end loop;
184 dbms_output.put_line('after attribute array');
185 -- sys.dbms_output.put_line(stmt);
186
187 rows_processed := sys.dbms_sql.execute(c);
188 sys.dbms_sql.close_cursor(c);
189 select fnd_notifications_s.currval into notification_id from dual;
190 return_code:=0;
191 dbms_output.put_line('after send notification');
192
193
194 /* Bug458110. gtummala. 3/14/97.
195 * Added this exception handler
196 */
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 dbms_output.put_line('In Exception');
201 PO_MESSAGE_S.SQL_ERROR('Send_Notification', x_progress, sqlcode);
202 RAISE;
203 end Send_Notification;
204
205
206 procedure Delete_Notification(
207 p_notification_id number,
208 return_code out NOCOPY number)
209 is
210 candelete varchar2(25) := '';
211 begin
212 select 'record_exists'
213 into candelete
214 from fnd_notifications
215 where notification_id = p_notification_id;
216 if candelete = 'record_exists' then
217 delete from fnd_notifications
218 where notification_id = p_notification_id;
219 return_code:=0;
220 else
221 return_code:=1;
222 end if;
223 end;
224
225 procedure Delete_Notif_By_ID_Type(
226 p_object_id number,
227 p_doc_type varchar2)
228 is
229 /* DEBUG. gtummala. 9/16/97.
230 * fnd_notifications_view isn't in the sep10 install.
231 * Just to make this package compile I'm going to take
232 * this whole procedure and replace with a null.
233 * We're going to drop this package
234 * next week anyway.
235 */
236
237
238 /*
239 cursor c1 is select notification_id
240 from fnd_notifications_view
241 where object_id=p_object_id
242 and p_doc_type = doc_type;
243
244 */
245
246 return_code number;
247 begin
248 /*
249 for dntn in c1 loop
250 Delete_Notification(dntn.notification_id, return_code);
251 end loop;
252 */
253
254 NULL;
255
256 end;
257
258 procedure Forward_Notification(
259 p_notification_id number,
260 p_new_recip number,
261 p_note varchar2 default NULL)
262 is
263 xxx rowid;
264 user_id Number := FND_PROFILE.Value('USER_ID');
265 login_id Number := FND_PROFILE.Value('LOGIN_ID');
266
267 begin
268 -- Check for valid employee id
269 select rowid into xxx
270 from hr_employees where employee_id=p_new_recip;
271
272 update fnd_notifications
273 set from_id = employee_id,
274 employee_id = p_new_recip,
275 note = p_note,
276 last_update_date = sysdate,
277 last_updated_by = user_id,
278 last_update_login = login_id
279 where
280 notification_id = p_notification_id;
281
282 end;
283
284 /*
285 procedure Get_Notification_Attribute(
286 p_notification_id number,
287 attribute_name varchar2,
288 attribute_value out NOCOPY varchar2)
289 is
290 stmt varchar2(2000);
291 c integer;
292 rows_processed integer;
293 attrval varchar2(100);
294 begin
295 attribute_value:= NULL;
296 stmt:= 'select '
297 || attribute_name
298 || ' from jliang_nv '
299 || ' where notification_id = :p_notification_id';
300
301
302 c := sys.dbms_sql.open_cursor;
303 sys.dbms_sql.parse(c, stmt, dbms_sql.native);
304 sys.dbms_sql.bind_variable(c,'p_notification_id', p_notification_id);
305 sys.dbms_sql.define_column(c, 1, attrval, 100);
306
307
308 rows_processed := sys.dbms_sql.execute(c);
309
310 if dbms_sql.fetch_rows(c) > 0 then
311 dbms_sql.column_value(c,1,attrval);
312 end if;
313
314 sys.dbms_sql.close_cursor(c);
315 attribute_value := attrval;
316 end;
317 */
318
319
320 /*===========================================================================
321
322 PROCEDURE NAME: notif_current
323
324 ===========================================================================*/
325
326 FUNCTION notif_current (x_notification_id NUMBER) RETURN BOOLEAN IS
327 x_progress VARCHAR2(3) := '';
328 x_data_exists NUMBER := 0;
329 BEGIN
330
331 IF x_notification_id IS NOT NULL THEN
332
333 x_progress := '010';
334
335 SELECT count(1)
336 INTO x_data_exists
337 FROM fnd_notifications
338 WHERE notification_id = x_notification_id;
339
340 ELSE
341 x_progress := '015';
342 RETURN false;
343 END IF;
344
345 x_progress := '020';
346 IF x_data_exists = 1 THEN
347 return TRUE;
348 ELSE
349 return FALSE;
350 END IF;
351
352 EXCEPTION
353 WHEN OTHERS THEN
354 dbms_output.put_line('In Exception');
355 raise_application_error(-20000, sqlerrm);
356 END;
357
358
359
360
361 procedure test_in(i number)
362 is
363 attr_a char_array;
364 new_id number;
365 return_code number;
366 t varchar2(30);
367 begin
368 attr_a(0):='at_val1';
369 attr_a(1):='at_val2';
370 attr_a(2):=NULL;
371 for ix in 1..10 loop
372 Send_Notification(p_employee_id=>5009,
373 p_message_name=>'FNDFMREG- no matching forms',
374 object_id=>ix,
375 doc_type=>'req',
376 attribute_array=>attr_a,
377 array_lb=>0,
378 array_ub=>2,
379 notification_id=>new_id,
380 return_code=>return_code);
381 end loop;
382
383
384 Delete_Notification(
385 p_notification_id => new_id,
386 return_code=>return_code);
387
388
389 Delete_Notif_By_ID_Type(
390 p_doc_type => 'req',
391 p_object_id => 8);
392
393 Forward_Notification(
394 p_notification_id => 5,
395 p_new_recip => 5010,
396 p_note => 'test');
397
398 --Get_Notification_Attribute(2, 'attr2', t);
399 end;
400
401 end ntn;