1 package body ALR_ACTIONS_PKG as
2 /* $Header: ALRACTNB.pls 120.4.12010000.1 2008/07/27 06:58:09 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_APPLICATION_ID in NUMBER,
6 X_ACTION_ID in NUMBER,
7 X_END_DATE_ACTIVE in DATE,
8 X_NAME in VARCHAR2,
9 X_ALERT_ID in NUMBER,
10 X_ACTION_TYPE in VARCHAR2,
11 X_ENABLED_FLAG in VARCHAR2,
12 X_DESCRIPTION in VARCHAR2,
13 X_ACTION_LEVEL_TYPE in VARCHAR2,
14 X_DATE_LAST_EXECUTED in DATE,
15 X_FILE_NAME in VARCHAR2,
16 X_ARGUMENT_STRING in VARCHAR2,
17 X_PROGRAM_APPLICATION_ID in NUMBER,
18 X_CONCURRENT_PROGRAM_ID in NUMBER,
19 X_LIST_APPLICATION_ID in NUMBER,
20 X_LIST_ID in NUMBER,
21 X_TO_RECIPIENTS in VARCHAR2,
22 X_CC_RECIPIENTS in VARCHAR2,
23 X_BCC_RECIPIENTS in VARCHAR2,
24 X_PRINT_RECIPIENTS in VARCHAR2,
25 X_PRINTER in VARCHAR2,
26 X_SUBJECT in VARCHAR2,
27 X_REPLY_TO in VARCHAR2,
28 X_RESPONSE_SET_ID in NUMBER,
29 X_FOLLOW_UP_AFTER_DAYS in NUMBER,
30 X_COLUMN_WRAP_FLAG in VARCHAR2,
31 X_MAXIMUM_SUMMARY_MESSAGE in NUMBER,
32 X_BODY in VARCHAR2,
33 X_VERSION_NUMBER in NUMBER,
34 X_CREATION_DATE in DATE,
35 X_CREATED_BY in NUMBER,
36 X_LAST_UPDATE_DATE in DATE,
37 X_LAST_UPDATED_BY in NUMBER,
38 X_LAST_UPDATE_LOGIN in NUMBER
39 ) is
40 cursor C is select ROWID from ALR_ACTIONS
41 where APPLICATION_ID = X_APPLICATION_ID
42 and ACTION_ID = X_ACTION_ID
43 and (((END_DATE_ACTIVE is null)
44 and (X_END_DATE_ACTIVE is null))
45 or ((END_DATE_ACTIVE is not null)
46 and (END_DATE_ACTIVE = X_END_DATE_ACTIVE)))
47 ;
48 begin
49 insert into ALR_ACTIONS (
50 APPLICATION_ID,
51 ACTION_ID,
52 NAME,
53 ALERT_ID,
54 ACTION_TYPE,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 CREATION_DATE,
58 CREATED_BY,
59 LAST_UPDATE_LOGIN,
60 END_DATE_ACTIVE,
61 ENABLED_FLAG,
62 DESCRIPTION,
63 ACTION_LEVEL_TYPE,
64 DATE_LAST_EXECUTED,
65 FILE_NAME,
66 ARGUMENT_STRING,
67 PROGRAM_APPLICATION_ID,
68 CONCURRENT_PROGRAM_ID,
69 LIST_APPLICATION_ID,
70 LIST_ID,
71 TO_RECIPIENTS,
72 CC_RECIPIENTS,
73 BCC_RECIPIENTS,
74 PRINT_RECIPIENTS,
75 PRINTER,
76 SUBJECT,
77 REPLY_TO,
78 RESPONSE_SET_ID,
79 FOLLOW_UP_AFTER_DAYS,
80 COLUMN_WRAP_FLAG,
81 MAXIMUM_SUMMARY_MESSAGE_WIDTH,
82 BODY,
83 VERSION_NUMBER
84 ) values (
85 X_APPLICATION_ID,
86 X_ACTION_ID,
87 X_NAME,
88 X_ALERT_ID,
89 X_ACTION_TYPE,
90 X_LAST_UPDATE_DATE,
91 X_LAST_UPDATED_BY,
92 X_CREATION_DATE,
93 X_CREATED_BY,
94 X_LAST_UPDATE_LOGIN,
95 X_END_DATE_ACTIVE,
96 X_ENABLED_FLAG,
97 X_DESCRIPTION,
98 X_ACTION_LEVEL_TYPE,
99 X_DATE_LAST_EXECUTED,
100 X_FILE_NAME,
101 X_ARGUMENT_STRING,
102 X_PROGRAM_APPLICATION_ID,
103 X_CONCURRENT_PROGRAM_ID,
104 X_LIST_APPLICATION_ID,
105 X_LIST_ID,
106 X_TO_RECIPIENTS,
107 X_CC_RECIPIENTS,
108 X_BCC_RECIPIENTS,
109 X_PRINT_RECIPIENTS,
110 X_PRINTER,
111 X_SUBJECT,
112 X_REPLY_TO,
113 X_RESPONSE_SET_ID,
114 X_FOLLOW_UP_AFTER_DAYS,
115 X_COLUMN_WRAP_FLAG,
116 X_MAXIMUM_SUMMARY_MESSAGE,
117 X_BODY,
118 X_VERSION_NUMBER);
119
120 open c;
121 fetch c into X_ROWID;
122 if (c%notfound) then
123 close c;
124 raise no_data_found;
125 end if;
126 close c;
127
128 end INSERT_ROW;
129
130 procedure LOAD_ROW (
131 X_APPLICATION_SHORT_NAME in VARCHAR2,
132 X_ALERT_NAME in VARCHAR2,
133 X_ACTION_NAME in VARCHAR2,
134 X_ACTION_END_DATE_ACTIVE in VARCHAR2,
135 X_OWNER in VARCHAR2,
136 X_ACTION_TYPE in VARCHAR2,
137 X_ENABLED_FLAG in VARCHAR2,
138 X_DESCRIPTION in VARCHAR2,
139 X_ACTION_LEVEL_TYPE in VARCHAR2,
140 X_DATE_LAST_EXECUTED in VARCHAR2,
141 X_FILE_NAME in VARCHAR2,
142 X_ARGUMENT_STRING in VARCHAR2,
143 X_PROGRAM_APPLICATION_NAME in VARCHAR2,
144 X_CONCURRENT_PROGRAM_NAME in VARCHAR2,
145 X_LIST_APPLICATION_NAME in VARCHAR2,
146 X_LIST_NAME in VARCHAR2,
147 X_TO_RECIPIENTS in VARCHAR2,
148 X_CC_RECIPIENTS in VARCHAR2,
149 X_BCC_RECIPIENTS in VARCHAR2,
150 X_PRINT_RECIPIENTS in VARCHAR2,
151 X_PRINTER in VARCHAR2,
152 X_SUBJECT in VARCHAR2,
153 X_REPLY_TO in VARCHAR2,
154 X_RESPONSE_SET_NAME in VARCHAR2,
155 X_FOLLOW_UP_AFTER_DAYS in VARCHAR2,
156 X_COLUMN_WRAP_FLAG in VARCHAR2,
157 X_MAXIMUM_SUMMARY_MESSAGE in VARCHAR2,
158 X_BODY in VARCHAR2,
159 X_VERSION_NUMBER in VARCHAR2,
160 X_LAST_UPDATE_DATE in VARCHAR2,
161 X_CUSTOM_MODE in VARCHAR2
162 ) is
163
164 l_user_id number := 0;
165 l_app_id number := 0;
166 l_alert_id number := 0;
167 l_action_id number := 0;
168 l_program_application_id number := null;
169 l_concurrent_program_id number := null;
170 l_list_app_id number := null;
171 l_list_id number := null;
172 l_resp_set_id number := null;
173 l_row_id varchar2(64);
174
175 f_luby number; -- entity owner in file
176 f_ludate date; -- entity update date in file
177 db_luby number; -- entity owner in db
178 db_ludate date; -- entity update date in db
179
180 begin
181 --DBMS_SESSION.SET_SQL_TRACE(TRUE);
182
183 -- Translate owner to file_last_updated_by
184 f_luby := fnd_load_util.owner_id(X_OWNER);
185
186 -- Translate char last_update_date to date
187 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
188
189 select APPLICATION_ID into l_app_id
190 from FND_APPLICATION
191 where APPLICATION_SHORT_NAME = X_APPLICATION_SHORT_NAME;
192
193 select ALERT_ID into l_alert_id
194 from ALR_ALERTS
195 where APPLICATION_ID = l_app_id
196 and ALERT_NAME = X_ALERT_NAME;
197
198 if (X_PROGRAM_APPLICATION_NAME is not null) then
199 select APPLICATION_ID into l_program_application_id
200 from FND_APPLICATION
201 where APPLICATION_SHORT_NAME = X_PROGRAM_APPLICATION_NAME;
202 end if;
203
204 if (X_CONCURRENT_PROGRAM_NAME is not null) then
205 select CONCURRENT_PROGRAM_ID into l_concurrent_program_id
206 from FND_CONCURRENT_PROGRAMS
207 where APPLICATION_ID = l_program_application_id
208 and CONCURRENT_PROGRAM_NAME = X_CONCURRENT_PROGRAM_NAME;
209 end if;
210
211 if (X_LIST_APPLICATION_NAME is not null) then
212 select APPLICATION_ID into l_list_app_id
213 from FND_APPLICATION
214 where APPLICATION_SHORT_NAME = X_LIST_APPLICATION_NAME;
215 end if;
216
217 if (X_LIST_NAME is not null) then
218 select LIST_ID into l_list_id
219 from ALR_DISTRIBUTION_LISTS
220 where APPLICATION_ID = l_list_app_id
221 and NAME = X_LIST_NAME
222 and (((END_DATE_ACTIVE is null)
223 and (X_ACTION_END_DATE_ACTIVE is null))
224 or ((END_DATE_ACTIVE is not null)
225 and (END_DATE_ACTIVE =
226 to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));
227 end if;
228
229 if (X_RESPONSE_SET_NAME is not null) then
230 select RESPONSE_SET_ID into l_resp_set_id
231 from ALR_RESPONSE_SETS
232 where APPLICATION_ID = l_app_id
233 and ALERT_ID = l_alert_id
234 and NAME = X_RESPONSE_SET_NAME;
235 end if;
236
237 select distinct ACTION_ID into l_action_id
238 from ALR_ACTIONS
239 where APPLICATION_ID = l_app_id
240 and ALERT_ID = l_alert_id
241 and NAME = X_ACTION_NAME
242 and (((END_DATE_ACTIVE is null)
243 and (X_ACTION_END_DATE_ACTIVE is null))
244 or ((END_DATE_ACTIVE is not null)
245 and (END_DATE_ACTIVE =
246 to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));
247
248 select last_updated_by, last_update_date
249 into db_luby, db_ludate
250 from ALR_ACTIONS
251 where application_id = l_app_id
252 and action_id = l_action_id
253 and (((END_DATE_ACTIVE is null)
254 and (X_ACTION_END_DATE_ACTIVE is null))
255 or ((END_DATE_ACTIVE is not null)
256 and (end_date_active =
257 to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'))));
258
259 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,db_ludate,
260 x_custom_mode)) then
261
262
263 ALR_ACTIONS_PKG.UPDATE_ROW(
264 X_APPLICATION_ID => l_app_id,
265 X_ACTION_ID => l_action_id,
266 X_END_DATE_ACTIVE =>
267 to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
268 X_NAME => X_ACTION_NAME,
269 X_ALERT_ID => l_alert_id,
270 X_ACTION_TYPE => X_ACTION_TYPE,
271 X_ENABLED_FLAG => X_ENABLED_FLAG,
272 X_DESCRIPTION => X_DESCRIPTION,
273 X_ACTION_LEVEL_TYPE => X_ACTION_LEVEL_TYPE,
274 X_DATE_LAST_EXECUTED =>
275 to_date(X_DATE_LAST_EXECUTED,'YYYY/MM/DD HH24:MI:SS'),
276 X_FILE_NAME => X_FILE_NAME,
277 X_ARGUMENT_STRING => X_ARGUMENT_STRING,
278 X_PROGRAM_APPLICATION_ID => l_program_application_id,
279 X_CONCURRENT_PROGRAM_ID => l_concurrent_program_id,
280 X_LIST_APPLICATION_ID => l_list_app_id,
281 X_LIST_ID => l_list_id,
282 X_TO_RECIPIENTS => X_TO_RECIPIENTS,
283 X_CC_RECIPIENTS => X_CC_RECIPIENTS,
284 X_BCC_RECIPIENTS => X_BCC_RECIPIENTS,
285 X_PRINT_RECIPIENTS => X_PRINT_RECIPIENTS,
286 X_PRINTER => X_PRINTER,
287 X_SUBJECT => X_SUBJECT,
288 X_REPLY_TO => X_REPLY_TO,
289 X_RESPONSE_SET_ID => l_resp_set_id,
290 X_FOLLOW_UP_AFTER_DAYS => X_FOLLOW_UP_AFTER_DAYS,
291 X_COLUMN_WRAP_FLAG => X_COLUMN_WRAP_FLAG,
292 X_MAXIMUM_SUMMARY_MESSAGE =>
293 to_number(X_MAXIMUM_SUMMARY_MESSAGE),
294 X_BODY => X_BODY,
295 X_VERSION_NUMBER => X_VERSION_NUMBER,
296 X_LAST_UPDATE_DATE => f_ludate,
297 X_LAST_UPDATED_BY => f_luby,
298 X_LAST_UPDATE_LOGIN => 0 );
299
300 end if;
301
302 exception
303
304 when NO_DATA_FOUND then
305
306 select ALR_ACTIONS_S.nextval into l_action_id from dual;
307
308 ALR_ACTIONS_PKG.INSERT_ROW(
309 X_ROWID => l_row_id,
310 X_APPLICATION_ID => l_app_id,
311 X_ACTION_ID => l_action_id,
312 X_END_DATE_ACTIVE =>
313 to_date(X_ACTION_END_DATE_ACTIVE,'YYYY/MM/DD HH24:MI:SS'),
314 X_NAME => X_ACTION_NAME,
315 X_ALERT_ID => l_alert_id,
316 X_ACTION_TYPE => X_ACTION_TYPE,
317 X_ENABLED_FLAG => X_ENABLED_FLAG,
318 X_DESCRIPTION => X_DESCRIPTION,
319 X_ACTION_LEVEL_TYPE => X_ACTION_LEVEL_TYPE,
320 X_DATE_LAST_EXECUTED =>
321 to_date(X_DATE_LAST_EXECUTED,'YYYY/MM/DD HH24:MI:SS'),
322 X_FILE_NAME => X_FILE_NAME,
323 X_ARGUMENT_STRING => X_ARGUMENT_STRING,
324 X_PROGRAM_APPLICATION_ID => l_program_application_id,
325 X_CONCURRENT_PROGRAM_ID => l_concurrent_program_id,
326 X_LIST_APPLICATION_ID => l_list_app_id,
327 X_LIST_ID => l_list_id,
328 X_TO_RECIPIENTS => X_TO_RECIPIENTS,
329 X_CC_RECIPIENTS => X_CC_RECIPIENTS,
330 X_BCC_RECIPIENTS => X_BCC_RECIPIENTS,
331 X_PRINT_RECIPIENTS => X_PRINT_RECIPIENTS,
332 X_PRINTER => X_PRINTER,
333 X_SUBJECT => X_SUBJECT,
334 X_REPLY_TO => X_REPLY_TO,
335 X_RESPONSE_SET_ID => l_resp_set_id,
336 X_FOLLOW_UP_AFTER_DAYS => X_FOLLOW_UP_AFTER_DAYS,
337 X_COLUMN_WRAP_FLAG => X_COLUMN_WRAP_FLAG,
338 X_MAXIMUM_SUMMARY_MESSAGE =>
339 to_number(X_MAXIMUM_SUMMARY_MESSAGE),
340 X_BODY => X_BODY,
341 X_VERSION_NUMBER => X_VERSION_NUMBER,
342 X_CREATION_DATE => f_ludate,
343 X_CREATED_BY => f_luby,
344 X_LAST_UPDATE_DATE => f_ludate,
345 X_LAST_UPDATED_BY => f_luby,
346 X_LAST_UPDATE_LOGIN => 0 );
347
348
349 end LOAD_ROW;
350
351 procedure LOCK_ROW (
352 X_APPLICATION_ID in NUMBER,
353 X_ACTION_ID in NUMBER,
354 X_END_DATE_ACTIVE in DATE,
355 X_NAME in VARCHAR2,
356 X_ALERT_ID in NUMBER,
357 X_ACTION_TYPE in VARCHAR2,
358 X_ENABLED_FLAG in VARCHAR2,
359 X_DESCRIPTION in VARCHAR2,
360 X_ACTION_LEVEL_TYPE in VARCHAR2,
361 X_DATE_LAST_EXECUTED in DATE,
362 X_FILE_NAME in VARCHAR2,
363 X_ARGUMENT_STRING in VARCHAR2,
364 X_PROGRAM_APPLICATION_ID in NUMBER,
365 X_CONCURRENT_PROGRAM_ID in NUMBER,
366 X_LIST_APPLICATION_ID in NUMBER,
367 X_LIST_ID in NUMBER,
368 X_TO_RECIPIENTS in VARCHAR2,
369 X_CC_RECIPIENTS in VARCHAR2,
370 X_BCC_RECIPIENTS in VARCHAR2,
371 X_PRINT_RECIPIENTS in VARCHAR2,
372 X_PRINTER in VARCHAR2,
373 X_SUBJECT in VARCHAR2,
374 X_REPLY_TO in VARCHAR2,
375 X_RESPONSE_SET_ID in NUMBER,
376 X_FOLLOW_UP_AFTER_DAYS in NUMBER,
377 X_COLUMN_WRAP_FLAG in VARCHAR2,
378 X_MAXIMUM_SUMMARY_MESSAGE in NUMBER,
379 X_BODY in VARCHAR2,
380 X_VERSION_NUMBER in NUMBER
381 ) is
382 cursor c1 is select
383 NAME,
384 ALERT_ID,
385 ACTION_TYPE,
386 ENABLED_FLAG,
387 DESCRIPTION,
388 ACTION_LEVEL_TYPE,
389 DATE_LAST_EXECUTED,
390 FILE_NAME,
391 ARGUMENT_STRING,
392 PROGRAM_APPLICATION_ID,
393 CONCURRENT_PROGRAM_ID,
394 LIST_APPLICATION_ID,
395 LIST_ID,
396 TO_RECIPIENTS,
397 CC_RECIPIENTS,
398 BCC_RECIPIENTS,
399 PRINT_RECIPIENTS,
400 PRINTER,
401 SUBJECT,
402 REPLY_TO,
403 RESPONSE_SET_ID,
404 FOLLOW_UP_AFTER_DAYS,
405 COLUMN_WRAP_FLAG,
406 MAXIMUM_SUMMARY_MESSAGE_WIDTH,
407 BODY,
408 VERSION_NUMBER,
409 APPLICATION_ID,
410 ACTION_ID,
411 END_DATE_ACTIVE
412 from ALR_ACTIONS
413 where APPLICATION_ID = X_APPLICATION_ID
414 and ACTION_ID = X_ACTION_ID
415 and (((END_DATE_ACTIVE is null)
416 and (X_END_DATE_ACTIVE is null))
417 or ((END_DATE_ACTIVE is not null)
418 and (END_DATE_ACTIVE = X_END_DATE_ACTIVE)))
419 for update of APPLICATION_ID nowait;
420 begin
421 for recinfo in c1 loop
422 if ( (recinfo.APPLICATION_ID = X_APPLICATION_ID)
423 AND (recinfo.ACTION_ID = X_ACTION_ID)
424 AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
425 OR ((recinfo.END_DATE_ACTIVE is null)
426 AND (X_END_DATE_ACTIVE is null)))
427 AND (recinfo.NAME = X_NAME)
428 AND (recinfo.ALERT_ID = X_ALERT_ID)
429 AND (recinfo.ACTION_TYPE = X_ACTION_TYPE)
430 AND ((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
431 OR ((recinfo.ENABLED_FLAG is null)
432 AND (X_ENABLED_FLAG is null)))
433 AND ((recinfo.DESCRIPTION = X_DESCRIPTION)
434 OR ((recinfo.DESCRIPTION is null)
435 AND (X_DESCRIPTION is null)))
436 AND ((recinfo.ACTION_LEVEL_TYPE = X_ACTION_LEVEL_TYPE)
437 OR ((recinfo.ACTION_LEVEL_TYPE is null)
438 AND (X_ACTION_LEVEL_TYPE is null)))
439 AND ((recinfo.DATE_LAST_EXECUTED = X_DATE_LAST_EXECUTED)
440 OR ((recinfo.DATE_LAST_EXECUTED is null)
441 AND (X_DATE_LAST_EXECUTED is null)))
442 AND ((recinfo.FILE_NAME = X_FILE_NAME)
443 OR ((recinfo.FILE_NAME is null) AND (X_FILE_NAME is null)))
444 AND ((recinfo.ARGUMENT_STRING = X_ARGUMENT_STRING)
445 OR ((recinfo.ARGUMENT_STRING is null)
446 AND (X_ARGUMENT_STRING is null)))
447 AND ((recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
451 OR ((recinfo.CONCURRENT_PROGRAM_ID is null)
448 OR ((recinfo.PROGRAM_APPLICATION_ID is null)
449 AND (X_PROGRAM_APPLICATION_ID is null)))
450 AND ((recinfo.CONCURRENT_PROGRAM_ID = X_CONCURRENT_PROGRAM_ID)
452 AND (X_CONCURRENT_PROGRAM_ID is null)))
453 AND ((recinfo.LIST_APPLICATION_ID = X_LIST_APPLICATION_ID)
454 OR ((recinfo.LIST_APPLICATION_ID is null)
455 AND (X_LIST_APPLICATION_ID is null)))
456 AND ((recinfo.LIST_ID = X_LIST_ID)
457 OR ((recinfo.LIST_ID is null) AND (X_LIST_ID is null)))
458 AND ((recinfo.TO_RECIPIENTS = X_TO_RECIPIENTS)
459 OR ((recinfo.TO_RECIPIENTS is null)
460 AND (X_TO_RECIPIENTS is null)))
461 AND ((recinfo.CC_RECIPIENTS = X_CC_RECIPIENTS)
462 OR ((recinfo.CC_RECIPIENTS is null)
463 AND (X_CC_RECIPIENTS is null)))
464 AND ((recinfo.BCC_RECIPIENTS = X_BCC_RECIPIENTS)
465 OR ((recinfo.BCC_RECIPIENTS is null)
466 AND (X_BCC_RECIPIENTS is null)))
467 AND ((recinfo.PRINT_RECIPIENTS = X_PRINT_RECIPIENTS)
468 OR ((recinfo.PRINT_RECIPIENTS is null)
469 AND (X_PRINT_RECIPIENTS is null)))
470 AND ((recinfo.PRINTER = X_PRINTER)
471 OR ((recinfo.PRINTER is null) AND (X_PRINTER is null)))
472 AND ((recinfo.SUBJECT = X_SUBJECT)
473 OR ((recinfo.SUBJECT is null) AND (X_SUBJECT is null)))
474 AND ((recinfo.REPLY_TO = X_REPLY_TO)
475 OR ((recinfo.REPLY_TO is null) AND (X_REPLY_TO is null)))
476 AND ((recinfo.RESPONSE_SET_ID = X_RESPONSE_SET_ID)
477 OR ((recinfo.RESPONSE_SET_ID is null)
478 AND (X_RESPONSE_SET_ID is null)))
479 AND ((recinfo.FOLLOW_UP_AFTER_DAYS = X_FOLLOW_UP_AFTER_DAYS)
480 OR ((recinfo.FOLLOW_UP_AFTER_DAYS is null)
481 AND (X_FOLLOW_UP_AFTER_DAYS is null)))
482 AND ((recinfo.COLUMN_WRAP_FLAG = X_COLUMN_WRAP_FLAG)
483 OR ((recinfo.COLUMN_WRAP_FLAG is null)
484 AND (X_COLUMN_WRAP_FLAG is null)))
485 AND ((recinfo.MAXIMUM_SUMMARY_MESSAGE_WIDTH =
486 X_MAXIMUM_SUMMARY_MESSAGE)
487 OR ((recinfo.MAXIMUM_SUMMARY_MESSAGE_WIDTH is null)
488 AND (X_MAXIMUM_SUMMARY_MESSAGE is null)))
489 AND ((recinfo.BODY = X_BODY)
490 OR ((recinfo.BODY is null) AND (X_BODY is null)))
491 AND (recinfo.VERSION_NUMBER = X_VERSION_NUMBER)
492 ) then
493 null;
494 else
495 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
496 app_exception.raise_exception;
497 end if;
498 end loop;
499 return;
500 end LOCK_ROW;
501
502 procedure UPDATE_ROW (
503 X_APPLICATION_ID in NUMBER,
504 X_ACTION_ID in NUMBER,
505 X_END_DATE_ACTIVE in DATE,
506 X_NAME in VARCHAR2,
507 X_ALERT_ID in NUMBER,
508 X_ACTION_TYPE in VARCHAR2,
509 X_ENABLED_FLAG in VARCHAR2,
510 X_DESCRIPTION in VARCHAR2,
511 X_ACTION_LEVEL_TYPE in VARCHAR2,
512 X_DATE_LAST_EXECUTED in DATE,
513 X_FILE_NAME in VARCHAR2,
514 X_ARGUMENT_STRING in VARCHAR2,
515 X_PROGRAM_APPLICATION_ID in NUMBER,
516 X_CONCURRENT_PROGRAM_ID in NUMBER,
517 X_LIST_APPLICATION_ID in NUMBER,
518 X_LIST_ID in NUMBER,
519 X_TO_RECIPIENTS in VARCHAR2,
520 X_CC_RECIPIENTS in VARCHAR2,
521 X_BCC_RECIPIENTS in VARCHAR2,
522 X_PRINT_RECIPIENTS in VARCHAR2,
523 X_PRINTER in VARCHAR2,
524 X_SUBJECT in VARCHAR2,
525 X_REPLY_TO in VARCHAR2,
526 X_RESPONSE_SET_ID in NUMBER,
527 X_FOLLOW_UP_AFTER_DAYS in NUMBER,
528 X_COLUMN_WRAP_FLAG in VARCHAR2,
529 X_MAXIMUM_SUMMARY_MESSAGE in NUMBER,
530 X_BODY in VARCHAR2,
531 X_VERSION_NUMBER in NUMBER,
532 X_LAST_UPDATE_DATE in DATE,
533 X_LAST_UPDATED_BY in NUMBER,
534 X_LAST_UPDATE_LOGIN in NUMBER
535 ) is
536 begin
537
538
539 update ALR_ACTIONS set
540 NAME = X_NAME,
541 ALERT_ID = X_ALERT_ID,
542 ACTION_TYPE = X_ACTION_TYPE,
543 ENABLED_FLAG = X_ENABLED_FLAG,
544 DESCRIPTION = X_DESCRIPTION,
545 ACTION_LEVEL_TYPE = X_ACTION_LEVEL_TYPE,
546 DATE_LAST_EXECUTED = X_DATE_LAST_EXECUTED,
547 FILE_NAME = X_FILE_NAME,
548 ARGUMENT_STRING = X_ARGUMENT_STRING,
549 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
550 CONCURRENT_PROGRAM_ID = X_CONCURRENT_PROGRAM_ID,
551 LIST_APPLICATION_ID = X_LIST_APPLICATION_ID,
552 LIST_ID = X_LIST_ID,
553 TO_RECIPIENTS = X_TO_RECIPIENTS,
554 CC_RECIPIENTS = X_CC_RECIPIENTS,
555 BCC_RECIPIENTS = X_BCC_RECIPIENTS,
556 PRINT_RECIPIENTS = X_PRINT_RECIPIENTS,
557 PRINTER = X_PRINTER,
558 SUBJECT = X_SUBJECT,
559 REPLY_TO = X_REPLY_TO,
560 RESPONSE_SET_ID = X_RESPONSE_SET_ID,
561 FOLLOW_UP_AFTER_DAYS = X_FOLLOW_UP_AFTER_DAYS,
562 COLUMN_WRAP_FLAG = X_COLUMN_WRAP_FLAG,
563 MAXIMUM_SUMMARY_MESSAGE_WIDTH = X_MAXIMUM_SUMMARY_MESSAGE,
564 BODY = X_BODY,
565 VERSION_NUMBER = X_VERSION_NUMBER,
566 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
567 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
568 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
569 where APPLICATION_ID = X_APPLICATION_ID
570 and ACTION_ID = X_ACTION_ID
571 and (((END_DATE_ACTIVE is null)
572 and (X_END_DATE_ACTIVE is null))
573 or ((END_DATE_ACTIVE is not null)
574 and (END_DATE_ACTIVE = X_END_DATE_ACTIVE)));
575
576 if (sql%notfound) then
577 raise no_data_found;
578 end if;
579 end UPDATE_ROW;
580
581 procedure DELETE_ROW (
582 X_APPLICATION_ID in NUMBER,
583 X_ACTION_ID in NUMBER,
584 X_END_DATE_ACTIVE in DATE
585 ) is
586 begin
587 delete from ALR_ACTIONS
588 where APPLICATION_ID = X_APPLICATION_ID
589 and ACTION_ID = X_ACTION_ID
590 and (((END_DATE_ACTIVE is null)
591 and (X_END_DATE_ACTIVE is null))
592 or ((END_DATE_ACTIVE is not null)
593 and (END_DATE_ACTIVE = X_END_DATE_ACTIVE)));
594
595 if (sql%notfound) then
596 raise no_data_found;
597 end if;
598
599 end DELETE_ROW;
600
601
602 end ALR_ACTIONS_PKG;
[Home] [Help]