[Home] [Help]
PACKAGE BODY: APPS.QA_ALERT_PKG
Source
1 PACKAGE BODY qa_alert_pkg AS
2 /* $Header: qaalrb.pls 115.2 2002/11/27 19:12:21 jezheng ship $ */
3
4
5 --
6 -- Create an Alert Action record. Action ID will be generated from
7 -- alr_actions_s and returned in x_action_id parameter.
8 --
9 -- Output variable: x_return_status will be
10 -- fnd_api.g_ret_sts_success if successful
11 -- fnd_api.g_ret_sts_error if error
12 --
13 PROCEDURE insert_alr_actions(
14 p_application_id NUMBER,
15 p_name VARCHAR2,
16 p_alert_id NUMBER,
17 p_action_type VARCHAR2,
18 p_end_date_active DATE,
19 p_enabled_flag VARCHAR2,
20 p_description VARCHAR2,
21 p_action_level_type VARCHAR2,
22 p_date_last_executed DATE,
23 p_file_name VARCHAR2,
24 p_argument_string VARCHAR2,
25 p_concurrent_program_id NUMBER,
26 p_program_application_id NUMBER,
27 p_list_application_id NUMBER,
28 p_list_id NUMBER,
29 p_to_recipients VARCHAR2,
30 p_cc_recipients VARCHAR2,
31 p_bcc_recipients VARCHAR2,
32 p_print_recipients VARCHAR2,
33 p_printer VARCHAR2,
34 p_subject VARCHAR2,
35 p_reply_to VARCHAR2,
36 p_response_set_id NUMBER,
37 p_follow_up_after_days NUMBER,
38 p_column_wrap_flag VARCHAR2,
39 p_max_summary_message_width NUMBER,
40 p_body VARCHAR2,
41 p_version_Number NUMBER,
42 p_creation_date DATE,
43 p_created_by NUMBER,
44 p_last_update_date DATE,
45 p_last_updated_by NUMBER,
46 p_last_update_login NUMBER,
47 x_action_id OUT NOCOPY NUMBER,
48 x_return_status OUT NOCOPY VARCHAR2
49 ) IS
50
51 BEGIN
52 INSERT INTO ALR_ACTIONS(
53 application_id,
54 action_id,
55 name,
56 alert_id,
57 action_type,
58 end_date_active,
59 enabled_flag,
60 description,
61 action_level_type,
62 date_last_executed,
63 file_name,
64 argument_string,
65 concurrent_program_id,
66 program_application_id,
67 list_application_id,
68 list_id,
69 to_recipients,
70 cc_recipients,
71 bcc_recipients,
72 print_recipients,
73 printer,
74 subject,
75 reply_to,
76 response_set_id,
77 follow_up_after_days,
78 column_wrap_flag,
79 maximum_summary_message_width,
80 body,
81 version_number,
82 creation_date,
83 created_by,
84 last_update_date,
85 last_updated_by,
86 last_update_login)
87 VALUES (
88 p_application_id,
89 alr_actions_s.nextval,
90 p_name,
91 p_alert_id,
92 p_action_type,
93 p_end_date_active,
94 p_enabled_flag,
95 p_description,
96 p_action_level_type,
97 p_date_last_executed,
98 p_file_name,
99 p_argument_string,
100 p_concurrent_program_id,
101 p_program_application_id,
102 p_list_application_id,
103 p_list_id,
104 p_to_recipients,
105 p_cc_recipients,
106 p_bcc_recipients,
107 p_print_recipients,
108 p_printer,
109 p_subject,
110 p_reply_to,
111 p_response_set_id,
112 p_follow_up_after_days,
113 p_column_wrap_flag,
114 p_max_summary_message_width,
115 p_body,
116 p_version_number,
117 p_creation_date,
118 p_created_by,
119 p_last_update_date,
120 p_last_updated_by,
121 p_last_update_login)
122 RETURNING action_id INTO x_action_id;
123
124 x_return_status := fnd_api.g_ret_sts_success;
125
126 EXCEPTION WHEN OTHERS THEN
127 x_return_status := fnd_api.g_ret_sts_error;
128
129 END insert_alr_actions;
130
131
132 --
133 -- Update an Alert Action record. This function can be used to
134 -- update in two different modes. When p_rowid is given, then
135 -- it will be used as the update clause (used in Forms). When
136 -- it is null, then p_application_id + p_alert_id + p_name
137 -- will be used as key.
138 --
139 -- Output variable: x_return_status will be
140 -- fnd_api.g_ret_sts_success if successful
141 -- fnd_api.g_ret_sts_error if record is not found
142 --
143 PROCEDURE update_alr_actions(
144 p_rowid VARCHAR2,
145 p_application_id NUMBER,
146 p_action_id NUMBER,
147 p_name VARCHAR2,
148 p_alert_id NUMBER,
149 p_action_type VARCHAR2,
150 p_end_date_active DATE,
151 p_enabled_flag VARCHAR2,
152 p_description VARCHAR2,
153 p_action_level_type VARCHAR2,
154 p_date_last_executed DATE,
155 p_file_name VARCHAR2,
156 p_argument_string VARCHAR2,
157 p_concurrent_program_id NUMBER,
158 p_program_application_id NUMBER,
159 p_list_application_id NUMBER,
160 p_list_id NUMBER,
161 p_to_recipients VARCHAR2,
162 p_cc_recipients VARCHAR2,
163 p_bcc_recipients VARCHAR2,
164 p_print_recipients VARCHAR2,
165 p_printer VARCHAR2,
166 p_subject VARCHAR2,
167 p_reply_to VARCHAR2,
168 p_response_set_id NUMBER,
169 p_follow_up_after_days NUMBER,
170 p_column_wrap_flag VARCHAR2,
171 p_max_summary_message_width NUMBER,
172 p_body VARCHAR2,
173 p_version_Number NUMBER,
174 p_last_update_date DATE,
175 p_last_updated_by NUMBER,
176 p_last_update_login NUMBER,
177 x_return_status OUT NOCOPY VARCHAR2
178 ) IS
179
180 BEGIN
181
182 IF p_rowid IS NOT NULL THEN
183 --
184 -- rowid is given, use it as WHERE clause to achieve best
185 -- performance.
186 --
187 UPDATE ALR_ACTIONS
188 SET
189 application_id = p_application_id,
190 action_id = nvl(p_action_id, action_id),
191 name = p_name,
192 alert_id = p_alert_id,
193 action_type = p_action_type,
194 end_date_active = p_end_date_active,
195 enabled_flag = p_enabled_flag,
196 description = p_description,
197 action_level_type = p_action_level_type,
198 date_last_executed = p_date_last_executed,
199 file_name = p_file_name,
200 argument_string = p_argument_string,
201 concurrent_program_id = p_concurrent_program_id,
202 program_application_id = p_program_application_id,
203 list_application_id = p_list_application_id,
204 list_id = p_list_id,
205 to_recipients = p_to_recipients,
206 cc_recipients = p_cc_recipients,
207 bcc_recipients = p_bcc_recipients,
208 print_recipients = p_print_recipients,
209 printer = p_printer,
210 subject = p_subject,
211 reply_to = p_reply_to,
212 response_set_id = p_response_set_id,
213 follow_up_after_days = p_follow_up_after_days,
214 column_wrap_flag = p_column_wrap_flag,
215 maximum_summary_message_width = p_max_summary_message_width,
216 body = p_body,
217 version_number = p_version_number,
218 last_update_date = p_last_update_date,
219 last_updated_by = p_last_updated_by,
220 last_update_login = p_last_update_login
221 WHERE rowid = p_rowid;
222
223 ELSE
224 --
225 -- rowid is NULL, use application_id+alert_id+name as key.
226 --
227 -- Duplicating the SQL has better performance than to fiddle
228 -- with nvl(p_rowid) in one SQL. Better readability too.
229 --
230 UPDATE ALR_ACTIONS
231 SET
232 action_id = nvl(p_action_id, action_id),
233 name = p_name,
234 alert_id = p_alert_id,
235 action_type = p_action_type,
236 end_date_active = p_end_date_active,
237 enabled_flag = p_enabled_flag,
238 description = p_description,
239 action_level_type = p_action_level_type,
240 date_last_executed = p_date_last_executed,
241 file_name = p_file_name,
242 argument_string = p_argument_string,
243 concurrent_program_id = p_concurrent_program_id,
244 program_application_id = p_program_application_id,
245 list_application_id = p_list_application_id,
246 list_id = p_list_id,
247 to_recipients = p_to_recipients,
248 cc_recipients = p_cc_recipients,
249 bcc_recipients = p_bcc_recipients,
250 print_recipients = p_print_recipients,
251 printer = p_printer,
252 subject = p_subject,
253 reply_to = p_reply_to,
254 response_set_id = p_response_set_id,
255 follow_up_after_days = p_follow_up_after_days,
256 column_wrap_flag = p_column_wrap_flag,
257 maximum_summary_message_width = p_max_summary_message_width,
258 body = p_body,
259 version_number = p_version_number,
260 last_update_date = p_last_update_date,
261 last_updated_by = p_last_updated_by,
262 last_update_login = p_last_update_login
263 WHERE
264 application_id = p_application_id AND
265 alert_id = p_alert_id AND
266 name = p_name;
267
268 END IF; -- p_rowid
269
270 IF (sql%notfound) THEN
271 x_return_status := fnd_api.g_ret_sts_error;
272 ELSE
273 x_return_status := fnd_api.g_ret_sts_success;
274 END IF;
275
276 END update_alr_actions;
277
278
279 --
280 -- Create an alr_action_sets record.
281 -- Return the created action_set_id and sequence into
282 -- x_action_set_id and x_sequence params.
283 --
284 -- Output variable: x_return_status will be
285 -- fnd_api.g_ret_sts_success if successful
286 -- fnd_api.g_ret_sts_error if not
287 --
288 PROCEDURE insert_alr_action_sets(
289 p_application_id NUMBER,
290 p_alert_id NUMBER,
291 p_name VARCHAR2,
292 p_end_date_active DATE,
293 p_enabled_flag VARCHAR2,
294 p_recipients_view_only_flag VARCHAR2,
295 p_description VARCHAR2,
296 p_suppress_flag VARCHAR2,
297 p_suppress_days NUMBER,
298 p_creation_date DATE,
299 p_created_by NUMBER,
300 p_last_update_date DATE,
301 p_last_updated_by NUMBER,
302 p_last_update_login NUMBER,
303 x_action_set_id OUT NOCOPY NUMBER,
304 x_sequence OUT NOCOPY NUMBER,
305 x_return_status OUT NOCOPY VARCHAR2
306 ) IS
307
308 l_sequence NUMBER;
309
310 BEGIN
311 SELECT nvl(max(sequence),0)+1
312 INTO l_sequence
313 FROM alr_action_sets
314 WHERE application_id = p_application_id AND
315 alert_id = p_alert_id;
316
317 INSERT INTO alr_action_sets (
318 action_set_id,
319 application_id,
320 name,
321 alert_id,
322 end_date_active,
323 enabled_flag,
324 recipients_view_only_flag,
325 description,
326 suppress_flag,
327 suppress_days,
328 sequence,
329 creation_date,
330 created_by,
331 last_update_date,
332 last_updated_by,
333 last_update_login)
334 VALUES (
335 alr_action_sets_s.nextval,
336 p_application_id,
337 p_name,
338 p_alert_id,
339 p_end_date_active,
340 p_enabled_flag,
341 p_recipients_view_only_flag,
342 p_description,
343 p_suppress_flag,
344 p_suppress_days,
345 l_sequence,
346 p_creation_date,
347 p_created_by,
348 p_last_update_date,
349 p_last_updated_by,
350 p_last_update_login)
351 RETURNING action_set_id INTO x_action_set_id;
352
353 x_sequence := l_sequence;
354 x_return_status := fnd_api.g_ret_sts_success;
355
356 EXCEPTION WHEN OTHERS THEN
357 x_return_status := fnd_api.g_ret_sts_error;
358
359 END insert_alr_action_sets;
360
361
362
363
364 --
365 -- Create an alr_action_set_members record.
366 -- Return the created action_set_member_id and sequence into
367 -- x_action_set_member_id and x_sequence params.
368 --
369 -- Output variable: x_return_status will be
370 -- fnd_api.g_ret_sts_success if successful
371 -- fnd_api.g_ret_sts_error if not
372 --
373 PROCEDURE insert_alr_action_set_members(
374 p_application_id NUMBER,
375 p_action_set_id NUMBER,
376 p_action_id NUMBER,
377 p_action_group_id NUMBER,
378 p_alert_id NUMBER,
379 p_end_date_active DATE,
380 p_enabled_flag VARCHAR2,
381 p_summary_threshold VARCHAR2,
382 p_abort_flag VARCHAR2,
383 p_error_action_sequence NUMBER,
384 p_creation_date DATE,
385 p_created_by NUMBER,
386 p_last_update_date DATE,
387 p_last_updated_by NUMBER,
388 p_last_update_login NUMBER,
389 x_action_set_member_id OUT NOCOPY NUMBER,
390 x_sequence OUT NOCOPY NUMBER,
391 x_return_status OUT NOCOPY VARCHAR2
392 ) IS
393 l_sequence NUMBER;
394
395 BEGIN
396 SELECT nvl(max(sequence),0) + 1
397 INTO l_sequence
398 FROM alr_action_set_members
399 WHERE application_id = 250 AND
400 alert_id = 10177 AND
401 action_set_id = p_action_set_id;
402
403 INSERT INTO alr_action_set_members (
404 action_set_member_id,
405 application_id,
406 action_set_id,
407 action_id,
408 action_group_id,
409 alert_id,
410 sequence,
411 end_date_active,
412 enabled_flag,
413 summary_threshold,
414 abort_flag,
415 error_action_sequence,
416 creation_date,
417 created_by,
418 last_update_date,
419 last_updated_by,
420 last_update_login)
421 VALUES (
422 alr_action_set_members_s.nextval,
423 p_application_id,
424 p_action_set_id,
425 p_action_id,
426 p_action_group_id,
427 p_alert_id,
428 l_sequence,
429 p_end_date_active,
430 p_enabled_flag,
431 p_summary_threshold,
432 p_abort_flag,
433 p_error_action_sequence,
434 p_creation_date,
435 p_created_by,
436 p_last_update_date,
437 p_last_updated_by,
438 p_last_update_login)
439 RETURNING action_set_member_id INTO x_action_set_member_id;
440
441 x_sequence := l_sequence;
442 x_return_status := fnd_api.g_ret_sts_success;
443
444 EXCEPTION WHEN OTHERS THEN
445 x_return_status := fnd_api.g_ret_sts_error;
446
447 END insert_alr_action_set_members;
448
449
450
451
452 --
453 -- Create an alr_action_set_outputs record.
454 --
455 -- Output variable: x_return_status will be
456 -- fnd_api.g_ret_sts_success if successful
457 -- fnd_api.g_ret_sts_error if not
458 --
459 PROCEDURE insert_alr_action_set_outputs(
460 p_application_id NUMBER,
461 p_action_set_id NUMBER,
462 p_alert_id NUMBER,
463 p_creation_date DATE,
464 p_created_by NUMBER,
465 p_last_update_date DATE,
466 p_last_updated_by NUMBER,
467 p_last_update_login NUMBER,
468 x_return_status OUT NOCOPY VARCHAR2
469 ) IS
470
471 BEGIN
472 INSERT INTO alr_action_set_outputs(
473 application_id,
474 action_set_id,
475 alert_id,
476 name,
477 sequence,
478 suppress_flag,
479 creation_date,
480 created_by,
481 last_update_date,
482 last_updated_by,
483 last_update_login)
484 SELECT
485 p_application_id,
486 p_action_set_id,
487 p_alert_id,
488 a.name,
489 a.sequence,
490 a.default_suppress_flag,
491 p_creation_date,
492 p_created_by,
493 p_last_update_date,
494 p_last_updated_by,
495 p_last_update_login
496 FROM alr_alert_outputs a
497 WHERE a.application_id = p_application_id AND
498 a.alert_id = p_alert_id AND
499 a.enabled_flag = 'Y';
500
501 x_return_status := fnd_api.g_ret_sts_success;
502
503 EXCEPTION
504 WHEN NO_DATA_FOUND THEN
505 -- allow no data found
506 x_return_status := fnd_api.g_ret_sts_success;
507
508 WHEN OTHERS THEN
509 x_return_status := fnd_api.g_ret_sts_error;
510
511 END insert_alr_action_set_outputs;
512
513
514 --
515 -- Create an alr_action_set_inputs record.
516 --
517 -- Output variable: x_return_status will be
518 -- fnd_api.g_ret_sts_success if successful
519 -- fnd_api.g_ret_sts_error if not
520 --
521 PROCEDURE insert_alr_action_set_inputs(
522 p_application_id NUMBER,
523 p_action_set_id NUMBER,
524 p_alert_id NUMBER,
525 p_creation_date DATE,
526 p_created_by NUMBER,
527 p_last_update_date DATE,
528 p_last_updated_by NUMBER,
529 p_last_update_login NUMBER,
530 x_return_status OUT NOCOPY VARCHAR2
531 ) IS
532
533 BEGIN
534 INSERT INTO alr_action_set_inputs(
535 application_id,
536 action_set_id,
537 alert_id,
538 name,
539 value,
540 creation_date,
541 created_by,
542 last_update_date,
543 last_updated_by,
544 last_update_login)
545 SELECT
546 p_application_id,
547 p_action_set_id,
548 p_alert_id,
549 a.name,
550 a.default_value,
551 p_creation_date,
552 p_created_by,
553 p_last_update_date,
554 p_last_updated_by,
555 p_last_update_login
556 FROM alr_alert_inputs a
557 WHERE a.application_id = p_application_id AND
558 a.alert_id = p_alert_id AND
559 a.enabled_flag = 'Y';
560
561 x_return_status := fnd_api.g_ret_sts_success;
562
563 EXCEPTION
564 WHEN NO_DATA_FOUND THEN
565 -- allow no data found
566 x_return_status := fnd_api.g_ret_sts_success;
567
568 WHEN OTHERS THEN
569 x_return_status := fnd_api.g_ret_sts_error;
570
571 END insert_alr_action_set_inputs;
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588 END qa_alert_pkg;