[Home] [Help]
PACKAGE BODY: APPS.AMS_TRIGGER_PVT_OA
Source
1 package body ams_trigger_pvt_oa as
2 /* $Header: amsatgrb.pls 120.0 2005/08/30 08:39:11 kbasavar noship $ */
3 procedure create_trigger(p_api_version NUMBER
4 , p_init_msg_list VARCHAR2
5 , p_commit VARCHAR2
6 , p_validation_level NUMBER
7 , x_return_status out nocopy VARCHAR2
8 , x_msg_count out nocopy NUMBER
9 , x_msg_data out nocopy VARCHAR2
10 , p7_a0 NUMBER
11 , p7_a1 DATE
12 , p7_a2 NUMBER
13 , p7_a3 DATE
14 , p7_a4 NUMBER
15 , p7_a5 NUMBER
16 , p7_a6 NUMBER
17 , p7_a7 NUMBER
18 , p7_a8 NUMBER
19 , p7_a9 VARCHAR2
20 , p7_a10 VARCHAR2
21 , p7_a11 NUMBER
22 , p7_a12 NUMBER
23 , p7_a13 DATE
24 , p7_a14 DATE
25 , p7_a15 DATE
26 , p7_a16 DATE
27 , p7_a17 DATE
28 , p7_a18 DATE
29 , p7_a19 DATE
30 , p7_a20 DATE
31 , p7_a21 DATE
32 , p7_a22 DATE
33 , p7_a23 VARCHAR2
34 , p7_a24 NUMBER
35 , p7_a25 DATE
36 , p7_a26 DATE
37 , p7_a27 VARCHAR2
38 , p7_a28 VARCHAR2
39 , p7_a29 VARCHAR2
40 , p7_a30 VARCHAR2
41 , p7_a31 VARCHAR2
42 , p7_a32 VARCHAR2
43 , p7_a33 VARCHAR2
44 , p8_a0 NUMBER
45 , p8_a1 DATE
46 , p8_a2 NUMBER
47 , p8_a3 DATE
48 , p8_a4 NUMBER
49 , p8_a5 NUMBER
50 , p8_a6 NUMBER
51 , p8_a7 NUMBER
52 , p8_a8 NUMBER
53 , p8_a9 VARCHAR2
54 , p8_a10 VARCHAR2
55 , p8_a11 NUMBER
56 , p8_a12 VARCHAR2
57 , p8_a13 NUMBER
58 , p8_a14 VARCHAR2
59 , p8_a15 NUMBER
60 , p8_a16 VARCHAR2
61 , p8_a17 VARCHAR2
62 , p8_a18 VARCHAR2
63 , p8_a19 NUMBER
64 , p8_a20 NUMBER
65 , p8_a21 NUMBER
66 , p8_a22 VARCHAR2
67 , p8_a23 VARCHAR2
68 , p8_a24 VARCHAR2
69 , p8_a25 VARCHAR2
70 , p8_a26 NUMBER
71 , p8_a27 NUMBER
72 , p8_a28 VARCHAR2
73 , p8_a29 VARCHAR2
74 , p8_a30 VARCHAR2
75 , p8_a31 VARCHAR2
76 , p9_a0 NUMBER
77 , p9_a1 DATE
78 , p9_a2 NUMBER
79 , p9_a3 DATE
80 , p9_a4 NUMBER
81 , p9_a5 NUMBER
82 , p9_a6 NUMBER
83 , p9_a7 NUMBER
84 , p9_a8 NUMBER
85 , p9_a9 NUMBER
86 , p9_a10 VARCHAR2
87 , p9_a11 VARCHAR2
88 , p9_a12 VARCHAR2
89 , p9_a13 NUMBER
90 , p9_a14 VARCHAR2
91 , p9_a15 NUMBER
92 , p9_a16 NUMBER
93 , p9_a17 VARCHAR2
94 , p9_a18 NUMBER
95 , p9_a19 NUMBER
96 , p9_a20 VARCHAR2
97 , p9_a21 VARCHAR2
98 , p9_a22 NUMBER
99 , p9_a23 VARCHAR2
100 , p9_a24 VARCHAR2
101 , p9_a25 VARCHAR2
102 , p9_a26 NUMBER
103 , x_trigger_check_id out nocopy NUMBER
104 , x_trigger_action_id out nocopy NUMBER
105 , x_trigger_id out nocopy NUMBER
106 )
107
108 as
109 ddp_trig_rec ams_trig_pvt.trig_rec_type;
110 ddp_thldchk_rec ams_thldchk_pvt.thldchk_rec_type;
111 ddp_thldact_rec ams_thldact_pvt.thldact_rec_type;
112 ddindx binary_integer; indx binary_integer;
113 begin
114
115 -- copy data to the local IN or IN-OUT args, if any
116
117
118
119
120
121
122
123 ddp_trig_rec.trigger_id := p7_a0;
124 ddp_trig_rec.last_update_date := p7_a1;
125 ddp_trig_rec.last_updated_by := p7_a2;
126 ddp_trig_rec.creation_date := p7_a3;
127 ddp_trig_rec.created_by := p7_a4;
128 ddp_trig_rec.last_update_login := p7_a5;
129 ddp_trig_rec.object_version_number := p7_a6;
130 ddp_trig_rec.process_id := p7_a7;
131 ddp_trig_rec.trigger_created_for_id := p7_a8;
132 ddp_trig_rec.arc_trigger_created_for := p7_a9;
133 ddp_trig_rec.triggering_type := p7_a10;
134 ddp_trig_rec.view_application_id := p7_a11;
135 ddp_trig_rec.timezone_id := p7_a12;
136 ddp_trig_rec.user_start_date_time := p7_a13;
137 ddp_trig_rec.start_date_time := p7_a14;
138 ddp_trig_rec.user_last_run_date_time := p7_a15;
139 ddp_trig_rec.last_run_date_time := p7_a16;
140 ddp_trig_rec.user_next_run_date_time := p7_a17;
141 ddp_trig_rec.next_run_date_time := p7_a18;
142 ddp_trig_rec.user_repeat_daily_start_time := p7_a19;
143 ddp_trig_rec.repeat_daily_start_time := p7_a20;
144 ddp_trig_rec.user_repeat_daily_end_time := p7_a21;
145 ddp_trig_rec.repeat_daily_end_time := p7_a22;
146 ddp_trig_rec.repeat_frequency_type := p7_a23;
147 ddp_trig_rec.repeat_every_x_frequency := p7_a24;
148 ddp_trig_rec.user_repeat_stop_date_time := p7_a25;
149 ddp_trig_rec.repeat_stop_date_time := p7_a26;
150 ddp_trig_rec.metrics_refresh_type := p7_a27;
151 ddp_trig_rec.trigger_name := p7_a28;
152 ddp_trig_rec.description := p7_a29;
153 ddp_trig_rec.notify_flag := p7_a30;
154 ddp_trig_rec.execute_schedule_flag := p7_a31;
155 ddp_trig_rec.triggered_status := p7_a32;
156 ddp_trig_rec.usage := p7_a33;
157
158 ddp_thldchk_rec.trigger_check_id := p8_a0;
159 ddp_thldchk_rec.last_update_date := p8_a1;
160 ddp_thldchk_rec.last_updated_by := p8_a2;
161 ddp_thldchk_rec.creation_date := p8_a3;
162 ddp_thldchk_rec.created_by := p8_a4;
163 ddp_thldchk_rec.last_update_login := p8_a5;
164 ddp_thldchk_rec.object_version_number := p8_a6;
165 ddp_thldchk_rec.trigger_id := p8_a7;
166 ddp_thldchk_rec.order_number := p8_a8;
167 ddp_thldchk_rec.chk1_type := p8_a9;
168 ddp_thldchk_rec.chk1_arc_source_code_from := p8_a10;
169 ddp_thldchk_rec.chk1_act_object_id := p8_a11;
170 ddp_thldchk_rec.chk1_source_code := p8_a12;
171 ddp_thldchk_rec.chk1_source_code_metric_id := p8_a13;
172 ddp_thldchk_rec.chk1_source_code_metric_type := p8_a14;
173 ddp_thldchk_rec.chk1_workbook_owner := p8_a15;
174 ddp_thldchk_rec.chk1_workbook_name := p8_a16;
175 ddp_thldchk_rec.chk1_to_chk2_operator_type := p8_a17;
176 ddp_thldchk_rec.chk2_type := p8_a18;
177 ddp_thldchk_rec.chk2_value := p8_a19;
178 ddp_thldchk_rec.chk2_low_value := p8_a20;
179 ddp_thldchk_rec.chk2_high_value := p8_a21;
180 ddp_thldchk_rec.chk2_uom_code := p8_a22;
181 ddp_thldchk_rec.chk2_currency_code := p8_a23;
182 ddp_thldchk_rec.chk2_source_code := p8_a24;
183 ddp_thldchk_rec.chk2_arc_source_code_from := p8_a25;
184 ddp_thldchk_rec.chk2_act_object_id := p8_a26;
185 ddp_thldchk_rec.chk2_source_code_metric_id := p8_a27;
186 ddp_thldchk_rec.chk2_source_code_metric_type := p8_a28;
187 ddp_thldchk_rec.chk2_workbook_name := p8_a29;
188 ddp_thldchk_rec.chk2_workbook_owner := p8_a30;
189 ddp_thldchk_rec.chk2_worksheet_name := p8_a31;
190
191 ddp_thldact_rec.trigger_action_id := p9_a0;
192 ddp_thldact_rec.last_update_date := p9_a1;
193 ddp_thldact_rec.last_updated_by := p9_a2;
194 ddp_thldact_rec.creation_date := p9_a3;
195 ddp_thldact_rec.created_by := p9_a4;
196 ddp_thldact_rec.last_update_login := p9_a5;
197 ddp_thldact_rec.object_version_number := p9_a6;
198 ddp_thldact_rec.process_id := p9_a7;
199 ddp_thldact_rec.trigger_id := p9_a8;
200 ddp_thldact_rec.order_number := p9_a9;
201 ddp_thldact_rec.notify_flag := p9_a10;
202 ddp_thldact_rec.generate_list_flag := p9_a11;
203 ddp_thldact_rec.action_need_approval_flag := p9_a12;
204 ddp_thldact_rec.action_approver_user_id := p9_a13;
205 ddp_thldact_rec.execute_action_type := p9_a14;
206 ddp_thldact_rec.list_header_id := p9_a15;
207 ddp_thldact_rec.list_connected_to_id := p9_a16;
208 ddp_thldact_rec.arc_list_connected_to := p9_a17;
209 ddp_thldact_rec.deliverable_id := p9_a18;
210 ddp_thldact_rec.activity_offer_id := p9_a19;
211 ddp_thldact_rec.dscript_name := p9_a20;
212 ddp_thldact_rec.program_to_call := p9_a21;
213 ddp_thldact_rec.cover_letter_id := p9_a22;
214 ddp_thldact_rec.mail_subject := p9_a23;
215 ddp_thldact_rec.mail_sender_name := p9_a24;
216 ddp_thldact_rec.from_fax_no := p9_a25;
217 ddp_thldact_rec.action_for_id := p9_a26;
218
219
220
221
222 -- here's the delegated call to the old PL/SQL routine
223 ams_trigger_pvt.create_trigger(p_api_version,
224 p_init_msg_list,
225 p_commit,
226 p_validation_level,
227 x_return_status,
228 x_msg_count,
229 x_msg_data,
230 ddp_trig_rec,
231 ddp_thldchk_rec,
232 ddp_thldact_rec,
233 x_trigger_check_id,
234 x_trigger_action_id,
235 x_trigger_id);
236
237 -- copy data back from the local variables to OUT or IN-OUT args, if any
238
239
240
241
242
243
244
245
246
247
248
249
250 end;
251
252 procedure update_trigger(p_api_version NUMBER
253 , p_init_msg_list VARCHAR2
254 , p_commit VARCHAR2
255 , p_validation_level NUMBER
256 , x_return_status out nocopy VARCHAR2
257 , x_msg_count out nocopy NUMBER
258 , x_msg_data out nocopy VARCHAR2
259 , p7_a0 NUMBER
260 , p7_a1 DATE
261 , p7_a2 NUMBER
262 , p7_a3 DATE
263 , p7_a4 NUMBER
264 , p7_a5 NUMBER
265 , p7_a6 NUMBER
266 , p7_a7 NUMBER
267 , p7_a8 NUMBER
268 , p7_a9 VARCHAR2
269 , p7_a10 VARCHAR2
270 , p7_a11 NUMBER
271 , p7_a12 NUMBER
272 , p7_a13 DATE
273 , p7_a14 DATE
274 , p7_a15 DATE
275 , p7_a16 DATE
276 , p7_a17 DATE
277 , p7_a18 DATE
278 , p7_a19 DATE
279 , p7_a20 DATE
280 , p7_a21 DATE
281 , p7_a22 DATE
282 , p7_a23 VARCHAR2
283 , p7_a24 NUMBER
284 , p7_a25 DATE
285 , p7_a26 DATE
286 , p7_a27 VARCHAR2
287 , p7_a28 VARCHAR2
288 , p7_a29 VARCHAR2
289 , p7_a30 VARCHAR2
290 , p7_a31 VARCHAR2
291 , p7_a32 VARCHAR2
292 , p7_a33 VARCHAR2
293 , p8_a0 NUMBER
294 , p8_a1 DATE
295 , p8_a2 NUMBER
296 , p8_a3 DATE
297 , p8_a4 NUMBER
298 , p8_a5 NUMBER
299 , p8_a6 NUMBER
300 , p8_a7 NUMBER
301 , p8_a8 NUMBER
302 , p8_a9 VARCHAR2
303 , p8_a10 VARCHAR2
304 , p8_a11 NUMBER
305 , p8_a12 VARCHAR2
306 , p8_a13 NUMBER
307 , p8_a14 VARCHAR2
308 , p8_a15 NUMBER
309 , p8_a16 VARCHAR2
310 , p8_a17 VARCHAR2
311 , p8_a18 VARCHAR2
312 , p8_a19 NUMBER
313 , p8_a20 NUMBER
314 , p8_a21 NUMBER
315 , p8_a22 VARCHAR2
316 , p8_a23 VARCHAR2
317 , p8_a24 VARCHAR2
318 , p8_a25 VARCHAR2
319 , p8_a26 NUMBER
320 , p8_a27 NUMBER
321 , p8_a28 VARCHAR2
322 , p8_a29 VARCHAR2
323 , p8_a30 VARCHAR2
324 , p8_a31 VARCHAR2
325 , p9_a0 NUMBER
326 , p9_a1 DATE
327 , p9_a2 NUMBER
328 , p9_a3 DATE
329 , p9_a4 NUMBER
330 , p9_a5 NUMBER
331 , p9_a6 NUMBER
332 , p9_a7 NUMBER
333 , p9_a8 NUMBER
334 , p9_a9 NUMBER
335 , p9_a10 VARCHAR2
336 , p9_a11 VARCHAR2
337 , p9_a12 VARCHAR2
338 , p9_a13 NUMBER
339 , p9_a14 VARCHAR2
340 , p9_a15 NUMBER
341 , p9_a16 NUMBER
342 , p9_a17 VARCHAR2
343 , p9_a18 NUMBER
344 , p9_a19 NUMBER
345 , p9_a20 VARCHAR2
346 , p9_a21 VARCHAR2
347 , p9_a22 NUMBER
348 , p9_a23 VARCHAR2
349 , p9_a24 VARCHAR2
350 , p9_a25 VARCHAR2
351 , p9_a26 NUMBER
352 )
353
354 as
355 ddp_trig_rec ams_trig_pvt.trig_rec_type;
356 ddp_thldchk_rec ams_thldchk_pvt.thldchk_rec_type;
357 ddp_thldact_rec ams_thldact_pvt.thldact_rec_type;
358 ddindx binary_integer; indx binary_integer;
359 begin
360
361 -- copy data to the local IN or IN-OUT args, if any
362
363
364
365
366
367
368
369 ddp_trig_rec.trigger_id := p7_a0;
370 ddp_trig_rec.last_update_date := p7_a1;
371 ddp_trig_rec.last_updated_by := p7_a2;
372 ddp_trig_rec.creation_date := p7_a3;
373 ddp_trig_rec.created_by := p7_a4;
374 ddp_trig_rec.last_update_login := p7_a5;
375 ddp_trig_rec.object_version_number := p7_a6;
376 ddp_trig_rec.process_id := p7_a7;
377 ddp_trig_rec.trigger_created_for_id := p7_a8;
378 ddp_trig_rec.arc_trigger_created_for := p7_a9;
379 ddp_trig_rec.triggering_type := p7_a10;
380 ddp_trig_rec.view_application_id := p7_a11;
381 ddp_trig_rec.timezone_id := p7_a12;
382 ddp_trig_rec.user_start_date_time := p7_a13;
383 ddp_trig_rec.start_date_time := p7_a14;
384 ddp_trig_rec.user_last_run_date_time := p7_a15;
385 ddp_trig_rec.last_run_date_time := p7_a16;
386 ddp_trig_rec.user_next_run_date_time := p7_a17;
387 ddp_trig_rec.next_run_date_time := p7_a18;
388 ddp_trig_rec.user_repeat_daily_start_time := p7_a19;
389 ddp_trig_rec.repeat_daily_start_time := p7_a20;
390 ddp_trig_rec.user_repeat_daily_end_time := p7_a21;
391 ddp_trig_rec.repeat_daily_end_time := p7_a22;
392 ddp_trig_rec.repeat_frequency_type := p7_a23;
393 ddp_trig_rec.repeat_every_x_frequency := p7_a24;
394 ddp_trig_rec.user_repeat_stop_date_time := p7_a25;
395 ddp_trig_rec.repeat_stop_date_time := p7_a26;
396 ddp_trig_rec.metrics_refresh_type := p7_a27;
397 ddp_trig_rec.trigger_name := p7_a28;
398 ddp_trig_rec.description := p7_a29;
399 ddp_trig_rec.notify_flag := p7_a30;
400 ddp_trig_rec.execute_schedule_flag := p7_a31;
401 ddp_trig_rec.triggered_status := p7_a32;
402 ddp_trig_rec.usage := p7_a33;
403
404 ddp_thldchk_rec.trigger_check_id := p8_a0;
405 ddp_thldchk_rec.last_update_date := p8_a1;
406 ddp_thldchk_rec.last_updated_by := p8_a2;
407 ddp_thldchk_rec.creation_date := p8_a3;
408 ddp_thldchk_rec.created_by := p8_a4;
409 ddp_thldchk_rec.last_update_login := p8_a5;
410 ddp_thldchk_rec.object_version_number := p8_a6;
411 ddp_thldchk_rec.trigger_id := p8_a7;
412 ddp_thldchk_rec.order_number := p8_a8;
413 ddp_thldchk_rec.chk1_type := p8_a9;
414 ddp_thldchk_rec.chk1_arc_source_code_from := p8_a10;
415 ddp_thldchk_rec.chk1_act_object_id := p8_a11;
416 ddp_thldchk_rec.chk1_source_code := p8_a12;
417 ddp_thldchk_rec.chk1_source_code_metric_id := p8_a13;
418 ddp_thldchk_rec.chk1_source_code_metric_type := p8_a14;
419 ddp_thldchk_rec.chk1_workbook_owner := p8_a15;
420 ddp_thldchk_rec.chk1_workbook_name := p8_a16;
421 ddp_thldchk_rec.chk1_to_chk2_operator_type := p8_a17;
422 ddp_thldchk_rec.chk2_type := p8_a18;
423 ddp_thldchk_rec.chk2_value := p8_a19;
424 ddp_thldchk_rec.chk2_low_value := p8_a20;
425 ddp_thldchk_rec.chk2_high_value := p8_a21;
426 ddp_thldchk_rec.chk2_uom_code := p8_a22;
427 ddp_thldchk_rec.chk2_currency_code := p8_a23;
428 ddp_thldchk_rec.chk2_source_code := p8_a24;
429 ddp_thldchk_rec.chk2_arc_source_code_from := p8_a25;
430 ddp_thldchk_rec.chk2_act_object_id := p8_a26;
431 ddp_thldchk_rec.chk2_source_code_metric_id := p8_a27;
432 ddp_thldchk_rec.chk2_source_code_metric_type := p8_a28;
433 ddp_thldchk_rec.chk2_workbook_name := p8_a29;
434 ddp_thldchk_rec.chk2_workbook_owner := p8_a30;
435 ddp_thldchk_rec.chk2_worksheet_name := p8_a31;
436
437 ddp_thldact_rec.trigger_action_id := p9_a0;
438 ddp_thldact_rec.last_update_date := p9_a1;
439 ddp_thldact_rec.last_updated_by := p9_a2;
440 ddp_thldact_rec.creation_date := p9_a3;
441 ddp_thldact_rec.created_by := p9_a4;
442 ddp_thldact_rec.last_update_login := p9_a5;
443 ddp_thldact_rec.object_version_number := p9_a6;
444 ddp_thldact_rec.process_id := p9_a7;
445 ddp_thldact_rec.trigger_id := p9_a8;
446 ddp_thldact_rec.order_number := p9_a9;
447 ddp_thldact_rec.notify_flag := p9_a10;
448 ddp_thldact_rec.generate_list_flag := p9_a11;
449 ddp_thldact_rec.action_need_approval_flag := p9_a12;
450 ddp_thldact_rec.action_approver_user_id := p9_a13;
451 ddp_thldact_rec.execute_action_type := p9_a14;
452 ddp_thldact_rec.list_header_id := p9_a15;
453 ddp_thldact_rec.list_connected_to_id := p9_a16;
454 ddp_thldact_rec.arc_list_connected_to := p9_a17;
455 ddp_thldact_rec.deliverable_id := p9_a18;
456 ddp_thldact_rec.activity_offer_id := p9_a19;
457 ddp_thldact_rec.dscript_name := p9_a20;
458 ddp_thldact_rec.program_to_call := p9_a21;
459 ddp_thldact_rec.cover_letter_id := p9_a22;
460 ddp_thldact_rec.mail_subject := p9_a23;
461 ddp_thldact_rec.mail_sender_name := p9_a24;
462 ddp_thldact_rec.from_fax_no := p9_a25;
463 ddp_thldact_rec.action_for_id := p9_a26;
464
465 -- here's the delegated call to the old PL/SQL routine
466 ams_trigger_pvt.update_trigger(p_api_version,
467 p_init_msg_list,
468 p_commit,
469 p_validation_level,
470 x_return_status,
471 x_msg_count,
472 x_msg_data,
473 ddp_trig_rec,
474 ddp_thldchk_rec,
475 ddp_thldact_rec);
476
477 -- copy data back from the local variables to OUT or IN-OUT args, if any
478
479
480
481
482
483
484
485
486
487 end;
488
489 FUNCTION get_ams_monitor_disable_delete(p_triggerId in NUMBER,p_start_date IN DATE)
490 RETURN VARCHAR2
491 IS
492 l_delete VARCHAR2(1):='Y';
493
494 CURSOR c_get_csch_count(p_trigId IN NUMBER) IS
495 SELECT count(1) from ams_campaign_schedules_b
496 WHERE trigger_id = p_trigId
497 and triggerable_flag = 'Y';
498 l_csch_count NUMBER := 0;
499 BEGIN
500
501 open c_get_csch_count(p_triggerId);
502 fetch c_get_csch_count into l_csch_count;
503 close c_get_csch_count;
504
505 IF p_start_date < SYSDATE OR l_csch_count >0 THEN
506 l_delete := 'N';
507 END IF;
508
509 RETURN l_delete;
510 EXCEPTION
511 WHEN NO_DATA_FOUND THEN
512 RETURN 'Y';
513 WHEN TOO_MANY_ROWS THEN
514 RETURN 'N';
515 WHEN OTHERS THEN
516 RETURN 'Y';
517 END get_ams_monitor_disable_delete;
518
519 end ams_trigger_pvt_oa;