[Home] [Help]
PACKAGE BODY: APPS.EDR_ERES_EVENT_PUB
Source
1 PACKAGE BODY EDR_ERES_EVENT_PUB AS
2 /* $Header: EDRPEVTB.pls 120.3.12000000.1 2007/01/18 05:54:30 appldev ship $*/
3
4 -- Private Utility Functions and/or procedures --
5
6 /** Validate if the event name exists in the WF_EVENTS table **/
7
8 FUNCTION VALID_EVENT_NAME (p_event_name IN varchar2)
9 return BOOLEAN
10 is
11 cursor l_count_csr is
12 select count(*)
13 from wf_events_vl
14 where name = p_event_name;
15
16 l_count NUMBER;
17
18 l_return_value boolean := FALSE;
19 begin
20 open l_count_csr;
21
22 fetch l_count_csr into l_count;
23
24 if (l_count > 0) THEN
25 l_return_value := TRUE;
26 END IF;
27
28 close l_count_csr;
29
30 return l_return_value;
31
32 end VALID_EVENT_NAME;
33
34 /** Read the individual statuses of the eres events in a table and
35 provide an overall status
36 **/
37
38 FUNCTION GET_OVERALL_STATUS
39 ( p_events IN ERES_EVENT_TBL_TYPE)
40 RETURN VARCHAR2
41
42 IS
43 l_total_events pls_integer;
44 l_status varchar2(20) ;
45 l_count pls_integer := 0;
46 l_overall_status VARCHAR2(20);
47 i pls_integer;
48 BEGIN
49 l_status := EDR_CONSTANTS_GRP.g_indetermined_status;
50 --read the status of each of the events and maintain a count
51 l_total_events := p_events.COUNT;
52
53 if (l_total_events > 0) then
54 i := p_events.FIRST;
55
56 --keep on adding to the count till there is no
57 --break on the status value
58 while i is not null loop
59 if (p_events(i).event_status = l_status
60 OR l_status = EDR_CONSTANTS_GRP.g_indetermined_status)
61 then
62 l_status := p_events(i).event_status;
63 l_count := l_count + 1;
64 else
65 EXIT;
66 end if;
67 i := p_events.NEXT(i);
68 end loop;
69
70 --if the status was not all the same the counts would
71 --differ and overall status would be INDETERMINED
72
73 if (l_count = l_total_events) then
74 l_overall_status := l_status;
75 else
76 l_overall_status
77 := EDR_CONSTANTS_GRP.g_indetermined_status;
78 end if;
79
80 end if;
81
82 if l_overall_status = EDR_CONSTANTS_GRP.g_no_action_status
83 then
84 l_overall_status := EDR_CONSTANTS_GRP.g_complete_status;
85 end if;
86
87 return l_overall_status;
88
89 END GET_OVERALL_STATUS;
90
91 /** Validate that the parent-child relationship defined in the subscription
92 ** parameters of an event is valid or not
93 ** IN: p_event_name IN VARCHAR2(80)
94 ** child event name
95 ** p_parent_event_name IN VARCHAR2(80)
96 ** parent event name
97 ** OUT: Boolean TRUE or FALSE
98 **/
99
100 FUNCTION VALIDATE_RELATIONSHIP
101 ( p_event_name IN VARCHAR2 ,
102 p_parent_event_name IN VARCHAR2
103 )
104 RETURN BOOLEAN
105 AS
106 l_return_value BOOLEAN := TRUE ;
107 l_sub_guid RAW(16) ;
108 l_relationship VARCHAR2(200) ;
109 BEGIN
110 --get the guid of the parent event
111 l_sub_guid := EDR_ERES_EVENT_PVT.GET_SUBSCRIPTION_GUID
112 (p_event_name => p_parent_event_name);
113
114 l_relationship :=
115 EDR_INDEXED_XML_UTIL.GET_WF_PARAMS
116 ( p_param_name => p_event_name,
117 p_event_guid => l_sub_guid
118 );
119
120 l_relationship := upper(l_relationship);
121
122 --l_relationship can have three possible values
123 --EVALUATE_NORMAL, IGNORE_SIGNATURE, ERECORD_ONLY
124
125 if (l_relationship is null OR
126 (l_relationship <> EDR_CONSTANTS_GRP.g_evaluate_normal AND
127 l_relationship <> EDR_CONSTANTS_GRP.g_erecord_only AND
128 l_relationship <> EDR_CONSTANTS_GRP.g_ignore_signature)
129 )
130 then
131 l_return_value := FALSE;
132 end if;
133
134 RETURN l_return_value;
135
136 END VALIDATE_RELATIONSHIP;
137
138 -- Public APIs --
139
140 PROCEDURE VALIDATE_ERECORD
141 ( p_api_version IN NUMBER ,
142 p_init_msg_list IN VARCHAR2 ,
143 x_return_status OUT NOCOPY VARCHAR2 ,
144 x_msg_count OUT NOCOPY NUMBER ,
145 x_msg_data OUT NOCOPY VARCHAR2 ,
146 p_erecord_id IN NUMBER
147 )
148 AS
149 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_ERECORD';
150 l_api_version CONSTANT NUMBER := 1.0;
151
152 l_count NUMBER;
153 l_mesg_text VARCHAR2(2000);
154
155 cursor l_count_csr is
156 SELECT COUNT(DOCUMENT_ID)
157 FROM EDR_PSIG_DOCUMENTS
158 WHERE DOCUMENT_ID = p_erecord_id;
159
160 BEGIN
161 -- Standard call to check for call compatibility.
162 IF NOT FND_API.Compatible_API_Call (l_api_version ,
163 p_api_version ,
164 l_api_name ,
165 G_PKG_NAME )
166 THEN
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
168 END IF;
169
170 -- Initialize message list if p_init_msg_list is set to TRUE.
171 IF FND_API.to_Boolean( p_init_msg_list ) THEN
172 FND_MSG_PUB.initialize;
173 END IF;
174
175 -- Initialize API return status to success
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177
178 -- API Body
179 -- BEGIN Bug : 3834375. Added Secure context to allow query of all rows
180 edr_ctx_pkg.set_secure_attr;
181 -- END Bug : 3834375
182 open l_count_csr;
183 fetch l_count_csr into l_count;
184 -- BEGIN Bug : 3834375. remove security context
185 edr_ctx_pkg.unset_secure_attr;
186 -- END Bug : 3834375
187 if (l_count = 0) then
188 x_return_status := FND_API.G_RET_STS_ERROR;
189 end if;
190
191 close l_count_csr;
192
193 -- End of API Body
194
195 -- Standard call to get message count and if count is 1, get
196 --message info.
197 FND_MSG_PUB.Count_And_Get
198 ( p_count => x_msg_count ,
199 p_data => x_msg_data
200 );
201
202 EXCEPTION
203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
205
206 FND_MSG_PUB.Count_And_Get
207 ( p_count => x_msg_count ,
208 p_data => x_msg_data
209 );
210
211 WHEN OTHERS THEN
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
213
214 IF FND_MSG_PUB.Check_Msg_Level
215 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
216 THEN
217 FND_MSG_PUB.Add_Exc_Msg
218 ( G_PKG_NAME ,
219 l_api_name
220 );
221 END IF;
222
223 FND_MSG_PUB.Count_And_Get
224 ( p_count => x_msg_count ,
225 p_data => x_msg_data
226 );
227
228 END VALIDATE_ERECORD;
229
230
231 /*** Determine if the payload to an ERES event is valid or not ***/
232
233 PROCEDURE VALIDATE_PAYLOAD
234 ( p_api_version IN NUMBER ,
235 p_init_msg_list IN VARCHAR2 ,
236 x_return_status OUT NOCOPY VARCHAR2 ,
237 x_msg_count OUT NOCOPY NUMBER ,
238 x_msg_data OUT NOCOPY VARCHAR2 ,
239 p_event_name IN VARCHAR2 ,
240 p_event_key IN VARCHAR2 ,
241 p_payload IN fnd_wf_event.param_table ,
242 p_mode IN VARCHAR2
243 )
244 AS
245 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_PAYLOAD';
246 l_api_version CONSTANT NUMBER := 1.0;
247
248 l_param_name varchar2(30);
249 l_param_value varchar2(2000);
250 l_error_param_name varchar2(30);
251 l_error_param_value varchar2(2000);
252
253 l_count pls_integer := 0;
254 l_inter_event_count pls_integer := 0;
255 l_source varchar2(30);
256 l_deferred varchar2(15);
257 l_parent_event_name varchar2(240);
258 l_parent_event_key varchar2(2000);
259 l_parent_erecord_id number;
260 i pls_integer;
261
262 l_mesg_text VARCHAR2(2000);
263 l_return_status VARCHAR2(1);
264 l_msg_count NUMBER;
265 l_msg_data VARCHAR2(2000);
266
267 l_event_name VARCHAR2(80);
268 l_event_key VARCHAR2(240);
269 l_error_event varchar2(240);
270
271 l_no_enabled_eres_sub NUMBER;
272
273 l_valid_relationship BOOLEAN := TRUE;
274
275 INVALID_SOURCE_ERROR EXCEPTION;
276 INVALID_PAYLOAD_ERROR EXCEPTION;
277 DEFERRED_DB_ERROR EXCEPTION;
278 INTEREVENT_PARAM_ERROR EXCEPTION;
279 INTEREVENT_DB_PARAM_ERROR EXCEPTION;
280 PARENT_ERECORD_ID_ERROR EXCEPTION;
281 PARENT_EVENT_ERROR EXCEPTION;
282 DEFERRED_PARAM_ERROR EXCEPTION;
283 INVALID_PARAM_ERROR EXCEPTION;
284 BAD_RELATIONSHIP_ERROR EXCEPTION;
285
286 BEGIN
287 -- Standard call to check for call compatibility.
288 IF NOT FND_API.Compatible_API_Call (l_api_version ,
289 p_api_version ,
290 l_api_name ,
291 G_PKG_NAME )
292 THEN
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 END IF;
295 -- Initialize message list if p_init_msg_list is set to TRUE.
296 IF FND_API.to_Boolean( p_init_msg_list ) THEN
297 FND_MSG_PUB.initialize;
298 END IF;
299
300 -- Initialize API return status to success
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302
303 -- API Body
304
305 -- Bug Start: 5087502
306 -- Verify if an active ERES subscription is available for current event.
307 select count(*) INTO l_no_enabled_eres_sub
308 from wf_events a, wf_event_subscriptions b
309 where a.GUID = b.EVENT_FILTER_GUID
310 and a.name = p_event_name
311 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
312 and b.STATUS = 'ENABLED'
313 and b.source_type = 'LOCAL'
314 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
315
316 IF l_no_enabled_eres_sub = 0 THEN
317 --Diagnostics Start
318 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
319 FND_MESSAGE.SET_NAME('EDR','EDR_VAL_NO_ERES_SUBSCRIPTIONS');
320 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event_name);
321 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event_key);
322 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
323 'edr.plsql.EDR_ERES_EVENT_PUB.VALIDATE_PAYLOAD',
324 FALSE
325 );
326 end if;
327 --Diagnostics End
328 END IF;
329
330 IF l_no_enabled_eres_sub > 0 THEN
331 -- read each of the payload parameters and validate
332 i := p_payload.FIRST;
333 while i is not null loop
334 l_param_name := p_payload(i).Param_Name;
335 l_param_value := p_payload(i).Param_Value;
336
337 if (l_param_name = EDR_CONSTANTS_GRP.g_source_param) then
338 l_source := l_param_value;
339
340 if (l_source = EDR_CONSTANTS_GRP.g_db_mode
341 OR l_source = EDR_CONSTANTS_GRP.g_forms_mode
342 OR l_source = EDR_CONSTANTS_GRP.g_oaf_mode)
343 then
344 l_count := l_count + 1;
345 else
346 RAISE INVALID_SOURCE_ERROR;
347 end if;
348 end if;
349
350 if (l_param_name = EDR_CONSTANTS_GRP.g_deferred_param) then
351 l_deferred := l_param_value;
352
353 if (l_deferred = 'Y' OR l_deferred = 'N') then
354 l_count := l_count + 1;
355 else
356 RAISE DEFERRED_PARAM_ERROR;
357 end if;
358 end if;
359
360 if (l_param_name = EDR_CONSTANTS_GRP.g_postop_param) then
361 l_count := l_count + 1;
362 end if;
363
364 if (l_param_name = EDR_CONSTANTS_GRP.g_user_label_param) then
365 l_count := l_count + 1;
366 end if;
367
368 if (l_param_name = EDR_CONSTANTS_GRP.g_user_value_param) then
369 l_count := l_count + 1;
370 end if;
371
372 if (l_param_name = EDR_CONSTANTS_GRP.g_audit_param) then
373 l_count := l_count + 1;
374 end if;
375
376 if (l_param_name = EDR_CONSTANTS_GRP.g_requester_param) then
377 l_count := l_count + 1;
378 end if;
379
380 -- additional parameters for inter event processing --
381 if (l_param_name = EDR_CONSTANTS_GRP.g_parent_event_name)
382 then
383 l_parent_event_name := l_param_value;
384 IF NOT valid_event_name(l_parent_event_name) then
385 l_error_param_name := l_param_name;
386 l_error_param_value := l_param_value;
387 RAISE INVALID_PARAM_ERROR;
388 END IF;
389
390 -- validate the relationship defined in the
391 -- parent event
392 l_valid_relationship :=
393 VALIDATE_RELATIONSHIP
394 (p_event_name => p_event_name,
395 p_parent_event_name => l_parent_event_name
396 );
397
398 if not l_valid_relationship then
399 RAISE BAD_RELATIONSHIP_ERROR;
400 end if;
401
402 l_inter_event_count := l_inter_event_count + 1;
403 end if;
404
405 if (l_param_name = EDR_CONSTANTS_GRP.g_parent_event_key) then
406 l_parent_event_key := l_param_value;
407
408 IF l_parent_event_key is null then
409 l_error_param_name := l_param_name;
410 l_error_param_value := l_param_value;
411 RAISE INVALID_PARAM_ERROR;
412 END IF;
413
414 l_inter_event_count := l_inter_event_count + 1;
415 end if;
416
417 if (l_param_name = EDR_CONSTANTS_GRP.g_parent_erecord_id) then
418 --SKARIMIS
419 /*As we are comparing to -1 we need to treate NULL also as -1 */
420 l_parent_erecord_id := NVL(l_param_value,-1);
421
422 if (l_parent_erecord_id <>
423 EDR_CONSTANTS_GRP.g_default_num_param_value)
424 then
425
426 VALIDATE_ERECORD
427 ( p_api_version => 1.0,
428 x_return_status => l_return_status,
429 x_msg_count => l_msg_count,
430 x_msg_data => l_msg_data,
431 p_erecord_id => l_parent_erecord_id
432 );
433
434 -- If any errors happen abort API.
435 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
438 RAISE PARENT_ERECORD_ID_ERROR;
439 END IF;
440 end if;
441
442 l_inter_event_count := l_inter_event_count + 1;
443
444 end if;
445
446 i := p_payload.NEXT(i);
447
448 end LOOP;
449
450
451
452 if (l_count <> EDR_CONSTANTS_GRP.g_param_count)
453 then
454 RAISE INVALID_PAYLOAD_ERROR;
455 elsif (p_mode = EDR_CONSTANTS_GRP.g_db_mode AND l_source <> EDR_CONSTANTS_GRP.g_db_mode)
456 then
457 RAISE DEFERRED_DB_ERROR;
458 elsif (l_source = EDR_CONSTANTS_GRP.g_db_mode AND l_deferred <> 'Y')
459 then
460 RAISE DEFERRED_DB_ERROR;
461 end if;
462
463 END IF;
464 -- Bug End: 5087502
465
466 -- find out if right values are passed in the case of inter event
467
468
469 if (l_inter_event_count > 0) then
470
471 /* SKARIMIS bug fix 3135118 */
472 if (p_event_name = l_parent_event_name) and (p_event_key = l_parent_event_key) then
473 RAISE INTEREVENT_PARAM_ERROR;
474 end if;
475 /* End of Bug Fix */
476 if (l_inter_event_count <> EDR_CONSTANTS_GRP.g_inter_event_param_count) then
477 RAISE INTEREVENT_PARAM_ERROR;
478
479 /* We will not perform this validating as we agreed on not bothering about parent erecord is being -1
480 elsif (p_mode = EDR_CONSTANTS_GRP.g_strict_mode
481 AND l_parent_erecord_id = EDR_CONSTANTS_GRP.g_default_num_param_value)
482 then
483 RAISE INTEREVENT_DB_PARAM_ERROR;
484 */
485 elsif (l_inter_event_count = EDR_CONSTANTS_GRP.g_inter_event_param_count
486 AND l_parent_event_key = EDR_CONSTANTS_GRP.g_default_char_param_value)
487 then
488 l_error_param_name := EDR_CONSTANTS_GRP.g_parent_event_key;
489 l_error_param_value := l_parent_event_key;
490 RAISE INVALID_PARAM_ERROR;
491
492 else
493 /* SKARIMIS . Addef id condition to get event details only for a valid erecord */
494 if l_parent_erecord_id <> EDR_CONSTANTS_GRP.g_default_num_param_value then
495 EDR_ERES_EVENT_PUB.GET_EVENT_DETAILS
496 ( p_api_version => 1.0 ,
497 x_return_status => l_return_status ,
498 x_msg_count => l_msg_count ,
499 x_msg_data => l_msg_data ,
500 p_erecord_id => l_parent_erecord_id ,
501 x_event_name => l_event_name ,
502 x_event_key => l_event_key
503 );
504
505 if ( l_event_name <> l_parent_event_name
506 OR l_event_key <> l_parent_event_key) then
507 RAISE PARENT_EVENT_ERROR;
508 end if;
509 end if;
510 end if;
511 end if;
512
513 -- End of API Body
514
515 -- Standard call to get message count and if count is 1,
516 --get message info.
517 FND_MSG_PUB.Count_And_Get
518 ( p_count => x_msg_count ,
519 p_data => x_msg_data
520 );
521
522 EXCEPTION
523 WHEN INVALID_SOURCE_ERROR THEN
524 x_return_status := FND_API.G_RET_STS_ERROR ;
525
526 fnd_message.set_name('EDR','EDR_VAL_INVALID_SOURCE');
527 fnd_message.set_token('EVENT_NAME', p_event_name);
528 fnd_message.set_token('EVENT_KEY', p_event_key);
529 l_mesg_text := fnd_message.get();
530
531 FND_MSG_PUB.Add_Exc_Msg
532 ( G_PKG_NAME ,
533 l_api_name ,
534 l_mesg_text
535 );
536 FND_MSG_PUB.Count_And_Get
537 ( p_count => x_msg_count ,
538 p_data => x_msg_data
539 );
540
541 WHEN DEFERRED_PARAM_ERROR THEN
542 x_return_status := FND_API.G_RET_STS_ERROR ;
543
544 fnd_message.set_name('EDR','EDR_VAL_INVALID_DEFERRED');
545 fnd_message.set_token('EVENT_NAME', p_event_name);
546 fnd_message.set_token('EVENT_KEY', p_event_key);
547 l_mesg_text := fnd_message.get();
548
549
550 FND_MSG_PUB.Add_Exc_Msg
551 ( G_PKG_NAME ,
552 l_api_name ,
553 l_mesg_text
554 );
555 FND_MSG_PUB.Count_And_Get
556 ( p_count => x_msg_count ,
557 p_data => x_msg_data
558 );
559
560 WHEN INVALID_PAYLOAD_ERROR THEN
561 x_return_status := FND_API.G_RET_STS_ERROR ;
562
563 fnd_message.set_name('EDR','EDR_VAL_INVALID_PAYLOAD');
564 fnd_message.set_token('EVENT_NAME', p_event_name);
565 fnd_message.set_token('EVENT_KEY', p_event_key);
566 l_mesg_text := fnd_message.get();
567
568 FND_MSG_PUB.Add_Exc_Msg
569 ( G_PKG_NAME ,
570 l_api_name ,
571 l_mesg_text
572 );
573
574 FND_MSG_PUB.Count_And_Get
575 ( p_count => x_msg_count ,
576 p_data => x_msg_data
577 );
578
579 WHEN DEFERRED_DB_ERROR THEN
580 x_return_status := FND_API.G_RET_STS_ERROR ;
581
582 fnd_message.set_name('EDR','EDR_VAL_INVALID_DEFERRED');
583 fnd_message.set_token('EVENT_NAME', p_event_name);
584 fnd_message.set_token('EVENT_KEY', p_event_key);
585 l_mesg_text := fnd_message.get();
586
587 FND_MSG_PUB.Add_Exc_Msg
588 ( G_PKG_NAME ,
589 l_api_name ,
590 l_mesg_text
591 );
592
593 FND_MSG_PUB.Count_And_Get
594 ( p_count => x_msg_count ,
595 p_data => x_msg_data
596 );
597
598 WHEN INTEREVENT_PARAM_ERROR THEN
599 x_return_status := FND_API.G_RET_STS_ERROR ;
600
601 fnd_message.set_name('EDR','EDR_VAL_INVALID_INTER_EVENT');
602 fnd_message.set_token('EVENT_NAME', p_event_name);
603 fnd_message.set_token('EVENT_KEY', p_event_key);
604 l_mesg_text := fnd_message.get();
605
606 FND_MSG_PUB.Add_Exc_Msg
607 ( G_PKG_NAME ,
608 l_api_name ,
609 l_mesg_text
610 );
611
612 FND_MSG_PUB.Count_And_Get
613 ( p_count => x_msg_count ,
614 p_data => x_msg_data
615 );
616
617 WHEN INTEREVENT_DB_PARAM_ERROR THEN
618 x_return_status := FND_API.G_RET_STS_ERROR ;
619
620 fnd_message.set_name('EDR','EDR_VAL_INTER_EVENT_DB');
621 fnd_message.set_token('EVENT_NAME', p_event_name);
622 fnd_message.set_token('EVENT_KEY', p_event_key);
623 l_mesg_text := fnd_message.get();
624
625 FND_MSG_PUB.Add_Exc_Msg
626 ( G_PKG_NAME ,
627 l_api_name ,
628 l_mesg_text
629 );
630
631 FND_MSG_PUB.Count_And_Get
632 ( p_count => x_msg_count ,
633 p_data => x_msg_data
634 );
635
636 WHEN PARENT_ERECORD_ID_ERROR THEN
637 x_return_status := FND_API.G_RET_STS_ERROR ;
638
639 fnd_message.set_name('EDR','EDR_VAL_INVALID_PARENT_ID');
640 fnd_message.set_token('ERECORD_ID', l_parent_erecord_id);
641 fnd_message.set_token('EVENT_NAME', p_event_name);
642 fnd_message.set_token('EVENT_KEY', p_event_key);
643 l_mesg_text := fnd_message.get();
644
645 FND_MSG_PUB.Add_Exc_Msg
646 ( G_PKG_NAME ,
647 l_api_name ,
648 l_mesg_text
649 );
650
651 FND_MSG_PUB.Count_And_Get
652 ( p_count => x_msg_count ,
653 p_data => x_msg_data
654 );
655
656
657 WHEN PARENT_EVENT_ERROR THEN
658 x_return_status := FND_API.G_RET_STS_ERROR ;
659
660 fnd_message.set_name('EDR','EDR_VAL_INVALID_PARENT_EVENT');
661 fnd_message.set_token('EVENT_NAME', p_event_name);
662 fnd_message.set_token('EVENT_KEY', p_event_key);
663 fnd_message.set_token('CHILD_NAME', l_event_name);
664 fnd_message.set_token('CHILD_KEY', l_event_key);
665
666 l_mesg_text := fnd_message.get();
667
668 FND_MSG_PUB.Add_Exc_Msg
669 ( G_PKG_NAME ,
670 l_api_name ,
671 l_mesg_text
672 );
673
674 FND_MSG_PUB.Count_And_Get
675 ( p_count => x_msg_count ,
676 p_data => x_msg_data
677 );
678
679 WHEN INVALID_PARAM_ERROR THEN
680 x_return_status := FND_API.G_RET_STS_ERROR ;
681
682 fnd_message.set_name('EDR','EDR_VAL_INVALID_PARAM');
683 fnd_message.set_token('EVENT_NAME', p_event_name);
684 fnd_message.set_token('EVENT_KEY', p_event_key);
685 fnd_message.set_token('PARAM_NAME', l_error_param_name);
686 fnd_message.set_token('PARAM_VALUE', l_error_param_value);
687 l_mesg_text := fnd_message.get();
688
689 FND_MSG_PUB.Add_Exc_Msg
690 ( G_PKG_NAME ,
691 l_api_name ,
692 l_mesg_text
693 );
694
695 FND_MSG_PUB.Count_And_Get
696 ( p_count => x_msg_count ,
697 p_data => x_msg_data
698 );
699
700 WHEN BAD_RELATIONSHIP_ERROR THEN
701 x_return_status := FND_API.G_RET_STS_ERROR ;
702
703 FND_MESSAGE.SET_NAME('EDR','EDR_VAL_INVALID_RELATIONSHIP');
704 FND_MESSAGE.SET_TOKEN('PARENT_EVENT', l_parent_event_name);
705 FND_MESSAGE.SET_TOKEN('EVENT_NAME', p_event_name);
706 fnd_message.set_token('EVENT_KEY', p_event_key);
707 l_mesg_text := fnd_message.get();
708
709 FND_MSG_PUB.Add_Exc_Msg
710 ( G_PKG_NAME ,
711 l_api_name ,
712 l_mesg_text
713 );
714
715 FND_MSG_PUB.Count_And_Get
716 ( p_count => x_msg_count ,
717 p_data => x_msg_data
718 );
719
720 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
722
723 FND_MSG_PUB.Count_And_Get
724 ( p_count => x_msg_count ,
725 p_data => x_msg_data
726 );
727
728 WHEN OTHERS THEN
729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
730
731 IF FND_MSG_PUB.Check_Msg_Level
732 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
733 THEN
734 FND_MSG_PUB.Add_Exc_Msg
735 ( G_PKG_NAME ,
736 l_api_name
737 );
738 END IF;
739
740 FND_MSG_PUB.Count_And_Get
741 (p_count => x_msg_count ,
742 p_data => x_msg_data
743 );
744
745 END VALIDATE_PAYLOAD;
746
747 /* Validate payload for call from forms and raise exceptions */
748
749 FUNCTION VALIDATE_PAYLOAD_FORMS
750 ( p_event_name IN VARCHAR2 ,
751 p_event_key IN VARCHAR2 ,
752 p_payload IN fnd_wf_event.param_table
753 )
754 RETURN BOOLEAN
755 AS
756 L_RETURN_STATUS VARCHAR2(1);
757 L_MSG_COUNT NUMBER;
758 L_MSG_index NUMBER;
759 L_MSG_data VARCHAR2(2000);
760
761 l_return_value boolean := false;
762 BEGIN
763 EDR_ERES_EVENT_PUB.VALIDATE_PAYLOAD
764 ( p_api_version => 1.0 ,
765 p_init_msg_list => FND_API.G_TRUE ,
766 x_return_status => l_return_status ,
767 x_msg_count => l_msg_count ,
768 x_msg_data => l_msg_data ,
769 p_event_name => p_event_name ,
770 p_event_key => p_event_key ,
771 p_payload => p_payload ,
772 p_mode => null
773 );
774
775 if (l_msg_count > 1) then
776 fnd_msg_pub.get
777 ( p_data => l_msg_data ,
778 p_msg_index_out => l_msg_index
779 );
780 end if;
781
782 -- If any errors happen abort API.
783 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
784 fnd_message.set_encoded(l_msg_data);
785 APP_EXCEPTION.RAISE_EXCEPTION;
786
787 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
788 fnd_message.set_encoded(l_msg_data);
789 APP_EXCEPTION.RAISE_EXCEPTION;
790
791 END IF;
792
793 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
794 l_return_value := true;
795 end if;
796
797 RETURN l_return_value;
798
799 END VALIDATE_PAYLOAD_FORMS;
800
801
802 /*** Get the event name and event key for an erecord id from evidence store ***/
803
804 PROCEDURE GET_EVENT_DETAILS
805 ( p_api_version IN NUMBER ,
806 p_init_msg_list IN VARCHAR2 ,
807 x_return_status OUT NOCOPY VARCHAR2 ,
808 x_msg_count OUT NOCOPY NUMBER ,
809 x_msg_data OUT NOCOPY VARCHAR2 ,
810 p_erecord_id IN NUMBER ,
811 x_event_name OUT NOCOPY VARCHAR2 ,
812 x_event_key OUT NOCOPY VARCHAR2
813 )
814 AS
815 l_api_name CONSTANT VARCHAR2(30) := 'GET_EVENT_DETAILS';
816 l_api_version CONSTANT NUMBER := 1.0;
817
818 l_mesg_text VARCHAR2(2000);
819
820 cursor l_event_csr is
821 SELECT EVENT_NAME, EVENT_KEY
822 FROM EDR_PSIG_DOCUMENTS
823 WHERE DOCUMENT_ID = p_erecord_id;
824
825 NO_DATA_ERROR EXCEPTION;
826 BEGIN
827 -- Standard call to check for call compatibility.
828 IF NOT FND_API.Compatible_API_Call (l_api_version ,
829 p_api_version ,
830 l_api_name ,
831 G_PKG_NAME )
832 THEN
833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
834 END IF;
835
836 -- Initialize message list if p_init_msg_list is set to TRUE.
837 IF FND_API.to_Boolean( p_init_msg_list ) THEN
838 FND_MSG_PUB.initialize;
839 END IF;
840
841 -- Initialize API return status to success
842 x_return_status := FND_API.G_RET_STS_SUCCESS;
843
844 -- API Body
845 open l_event_csr;
846
847 fetch l_event_csr
848 into x_event_name, x_event_key;
849
850 close l_event_csr;
851
852 if (x_event_name is null) then
853 RAISE NO_DATA_ERROR;
854 end if;
855
856 -- End of API Body
857
858 -- Standard call to get message count and if count is 1, get
859 --message info.
860 FND_MSG_PUB.Count_And_Get
861 ( p_count => x_msg_count ,
862 p_data => x_msg_data
863 );
864
865 EXCEPTION
866 WHEN NO_DATA_ERROR then
867 x_return_status := FND_API.G_RET_STS_ERROR;
868
869 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
871
872 FND_MSG_PUB.Count_And_Get
873 ( p_count => x_msg_count ,
874 p_data => x_msg_data
875 );
876
877 WHEN OTHERS THEN
878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
879
880 IF FND_MSG_PUB.Check_Msg_Level
881 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
882 THEN
883 FND_MSG_PUB.Add_Exc_Msg
884 ( G_PKG_NAME ,
885 l_api_name
886 );
887 END IF;
888
889 FND_MSG_PUB.Count_And_Get
890 ( p_count => x_msg_count ,
891 p_data => x_msg_data
892 );
893
894 END GET_EVENT_DETAILS;
895
896 /*** Raise an ERES event ***/
897
898 PROCEDURE RAISE_ERES_EVENT
899 ( p_api_version IN NUMBER,
900 p_init_msg_list IN VARCHAR2,
901 p_validation_level IN NUMBER,
902 x_return_status OUT NOCOPY VARCHAR2,
903 x_msg_count OUT NOCOPY NUMBER,
904 x_msg_data OUT NOCOPY VARCHAR2,
905 p_child_erecords IN ERECORD_ID_TBL_TYPE,
906 x_event IN OUT NOCOPY ERES_EVENT_REC_TYPE
907
908 )
909 AS
910 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_ERES_EVENT';
911 l_api_version CONSTANT NUMBER := 1.0;
912
913 i pls_integer;
914
915 l_return_status VARCHAR2(1);
916 l_msg_count NUMBER;
917 l_msg_data VARCHAR2(2000);
918
919 l_child BOOLEAN := FALSE;
920
921 l_parent_event_name varchar2(80);
922 l_parent_event_key varchar2(240);
923 l_parent_erecord_id number;
924
925 l_child_event_name varchar2(80);
926 l_child_event_key varchar2(240);
927 l_child_erecord_id number;
928
929 l_relationship_id NUMBER;
930
931 l_parameter_list FND_WF_EVENT.PARAM_TABLE;
932 l_param_name varchar2(30);
933 l_param_value varchar2(2000);
934
935 --Bug 4122622: Start
936 l_child_erecord_ids VARCHAR2(4000);
937 j pls_integer;
938 CHILD_ERECORD_ID_ERROR EXCEPTION;
939 l_wrong_child_id NUMBER;
940 l_mesg_text VARCHAR2(2000);
941 --Bug 4122622: End
942
943 BEGIN
944 -- Standard call to check for call compatibility.
945 IF NOT FND_API.Compatible_API_Call (l_api_version ,
946 p_api_version ,
947 l_api_name ,
948 G_PKG_NAME )
949 THEN
950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
951 END IF;
952
953 -- Initialize message list if p_init_msg_list is set to TRUE.
954 IF FND_API.to_Boolean( p_init_msg_list ) THEN
955 FND_MSG_PUB.initialize;
956 END IF;
957
958 -- Initialize API return status to success
959 x_return_status := FND_API.G_RET_STS_SUCCESS;
960
961 -- API Body
962
963 --Bug 4122622: Start
964 --if the child e-records are set then validate them against the evidence store.
965 if p_child_erecords.count > 0 then
966
967 i := p_child_erecords.FIRST;
968
969 VALIDATE_ERECORD
970 ( p_api_version => 1.0,
971 x_return_status => l_return_status,
972 x_msg_count => l_msg_count,
973 x_msg_data => l_msg_data,
974 p_erecord_id => p_child_erecords(i)
975 );
976 -- If any errors happen abort API.
977 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
979 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
980 l_wrong_child_id := p_child_erecords(i);
981 RAISE CHILD_ERECORD_ID_ERROR;
982 END IF;
983
984 l_child_erecord_ids := to_char(p_child_erecords(i));
985 i := p_child_erecords.NEXT(i);
986 while i is not null loop
987 VALIDATE_ERECORD
988 ( p_api_version => 1.0,
989 x_return_status => l_return_status,
990 x_msg_count => l_msg_count,
991 x_msg_data => l_msg_data,
992 p_erecord_id => p_child_erecords(i)
993 );
994 -- If any errors happen abort API.
995 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
997 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
998 l_wrong_child_id := p_child_erecords(i);
999 RAISE CHILD_ERECORD_ID_ERROR;
1000 END IF;
1001
1002 --Create a comma separated string of child e-record IDs
1003 l_child_erecord_ids := l_child_erecord_ids||','||to_char(p_child_erecords(i));
1004
1005 i := p_child_erecords.NEXT(i);
1006
1007 end loop;
1008 end if;
1009
1010 --If the child e-record IDs were set then create a payload parameter list.
1011 if l_child_erecord_ids is not null then
1012 EDR_ERES_EVENT_PVT.CREATE_PAYLOAD
1013 ( p_event => x_event,
1014 p_starting_position => 4,
1015 x_payload => l_parameter_list
1016 );
1017
1018 --Iterate to the end of the parameter list.
1019 j := null;
1020 i := l_parameter_list.FIRST;
1021 while i is not null loop
1022 j := i;
1023 i := l_parameter_list.NEXT(i);
1024 end loop;
1025
1026 --Set the comma separated child e-record ID string on the payload.
1027 l_parameter_list(j+1).param_name := EDR_CONSTANTS_GRP.G_CHILD_ERECORD_IDS;
1028 l_parameter_list(j+1).param_value := l_child_erecord_ids;
1029
1030 --Call raise event with the parameter list as the argument.
1031 EDR_ERES_EVENT_PVT.RAISE_EVENT
1032 ( p_api_version => 1.0,
1033 p_init_msg_list => FND_API.G_FALSE,
1034 p_validation_level => p_validation_level,
1035 x_return_status => l_return_status,
1036 x_msg_count => l_msg_count,
1037 x_msg_data => l_msg_data,
1038 x_event => x_event,
1039 p_mode => EDR_CONSTANTS_GRP.g_strict_mode,
1040 x_is_child_event => l_child,
1041 p_parameter_list => l_parameter_list
1042 );
1043 else
1044
1045 -- raise the event
1046 EDR_ERES_EVENT_PVT.RAISE_EVENT
1047 ( p_api_version => 1.0,
1048 p_init_msg_list => FND_API.G_FALSE,
1049 p_validation_level => p_validation_level,
1050 x_return_status => l_return_status,
1051 x_msg_count => l_msg_count,
1052 x_msg_data => l_msg_data,
1053 x_event => x_event,
1054 p_mode => EDR_CONSTANTS_GRP.g_strict_mode,
1055 x_is_child_event => l_child
1056 );
1057 end if;
1058 --Bug 4122622: End
1059
1060 -- If any errors happen abort API.
1061 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1063 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1064 RAISE FND_API.G_EXC_ERROR;
1065 END IF;
1066
1067 -- inter event: proceed only if the event has been raised
1068 -- successfully and erecord created
1069
1070 if (x_event.erecord_id is not null) then
1071
1072 -- child event: now do processing in case this is a child
1073 -- event in an inter event context
1074 if (l_child = TRUE) then
1075
1076 --Bug 3136403: Start
1077 --Copy the individual parameters to a structure
1078 --of type fnd_wf_event.param_table
1079
1080 EDR_ERES_EVENT_PVT.CREATE_PAYLOAD
1081 ( p_event => x_event ,
1082 p_starting_position => 1 ,
1083 x_payload => l_parameter_list
1084 );
1085
1086 i := l_parameter_list.FIRST;
1087
1088 while i is not null loop
1089 l_param_name := l_parameter_list(i).Param_Name;
1090 l_param_value := l_parameter_list(i).Param_Value;
1091
1092 if (l_param_name = EDR_CONSTANTS_GRP.g_parent_event_name)
1093 then
1094 l_parent_event_name := l_param_value;
1095 end if;
1096
1097 if (l_param_name = EDR_CONSTANTS_GRP.g_parent_event_key)
1098 then
1099 l_parent_event_key := l_param_value;
1100 end if;
1101
1102 if (l_param_name = EDR_CONSTANTS_GRP.g_parent_erecord_id)
1103 then
1104 l_parent_erecord_id := l_param_value;
1105 end if;
1106
1107 i := l_parameter_list.NEXT(i);
1108 end loop;
1109
1110 --Bug 3136403: End
1111
1112 -- now that we know its an inter event mode and we
1113 -- have all the information to create the
1114 -- relationship, insert a row in the relationship
1115 -- table
1116
1117 --no need to do validation in the called proc as we
1118 --have already done validation
1119 /* SKARIMIS . Added condition to post relation only if l_parent_erecord_id in > 0 */
1120 IF nvl(l_parent_erecord_id,-1) > 0 THEN
1121 EDR_EVENT_RELATIONSHIP_PUB.CREATE_RELATIONSHIP
1122 ( p_api_version => 1.0 ,
1123 p_init_msg_list => FND_API.G_FALSE ,
1124 p_commit => FND_API.G_FALSE ,
1125 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
1126 x_return_status => l_return_status ,
1127 x_msg_count => l_msg_count ,
1128 x_msg_data => l_msg_data ,
1129 P_PARENT_ERECORD_ID => l_parent_erecord_id ,
1130 P_PARENT_EVENT_NAME => l_parent_event_name ,
1131 P_PARENT_EVENT_KEY => l_parent_event_key ,
1132 P_CHILD_ERECORD_ID => x_event.erecord_id ,
1133 P_CHILD_EVENT_NAME => x_event.event_name ,
1134 P_CHILD_EVENT_KEY => x_event.event_key ,
1135 X_RELATIONSHIP_ID => l_relationship_id
1136 );
1137
1138 -- If any errors happen abort API.
1139 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1141 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1142 RAISE FND_API.G_EXC_ERROR;
1143 END IF;
1144 END IF;
1145 end if; --end of the if for child event
1146
1147 -- parent event:do processing in case this is a parent event
1148 -- in an inter event context
1149
1150 if (p_child_erecords.COUNT > 0) then
1151
1152 i := p_child_erecords.FIRST;
1153
1154 while i is not null loop
1155
1156 GET_EVENT_DETAILS
1157 ( p_api_version => 1.0,
1158 p_init_msg_list => FND_API.G_FALSE,
1159 x_return_status => l_return_status,
1160 x_msg_count => l_msg_count,
1161 x_msg_data => l_msg_data,
1162 p_erecord_id => p_child_erecords(i),
1163 x_event_name => l_child_event_name,
1164 x_event_key => l_child_event_key
1165 );
1166
1167 -- If any errors happen abort API.
1168 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1170 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1171 RAISE FND_API.G_EXC_ERROR;
1172 END IF;
1173
1174 -- now that we know its an inter event mode and we
1175 -- have all the information to create the relationship
1176 -- insert a row in the relationship table
1177
1178 -- no need to do validation as the above call to get the child
1179 -- event details would have done that.
1180 -- no need for validation of parent as its anyhow valid
1181
1182 EDR_EVENT_RELATIONSHIP_PUB.CREATE_RELATIONSHIP
1183 ( p_api_version => 1.0 ,
1184 p_init_msg_list => FND_API.G_FALSE ,
1185 p_commit => FND_API.G_FALSE ,
1186 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
1187 x_return_status => l_return_status ,
1188 x_msg_count => l_msg_count ,
1189 x_msg_data => l_msg_data ,
1190 P_PARENT_ERECORD_ID => x_event.erecord_id ,
1191 P_PARENT_EVENT_NAME => x_event.event_name ,
1192 P_PARENT_EVENT_KEY => x_event.event_key ,
1193 P_CHILD_ERECORD_ID => p_child_erecords(i) ,
1194 P_CHILD_EVENT_NAME => l_child_event_name ,
1195 P_CHILD_EVENT_KEY => l_child_event_key ,
1196 X_RELATIONSHIP_ID => l_relationship_id
1197 );
1198
1199 -- If any errors happen abort API.
1200 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1202 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1203 RAISE FND_API.G_EXC_ERROR;
1204 END IF;
1205
1206 i := p_child_erecords.NEXT(i);
1207 end loop;
1208
1209 end if; --end of the if for parent event
1210
1211 end if; --end of the if for inter event
1212
1213 -- End of API Body
1214
1215 -- Standard call to get message count and if count is 1,
1216 --get message info.
1217 FND_MSG_PUB.Count_And_Get
1218 ( p_count => x_msg_count ,
1219 p_data => x_msg_data
1220 );
1221
1222 EXCEPTION
1223 WHEN FND_API.G_EXC_ERROR THEN
1224 --no need to set the event status its been set
1225 --in the called API
1226 x_return_status := FND_API.G_RET_STS_ERROR ;
1227
1228 FND_MSG_PUB.Count_And_Get
1229 ( p_count => x_msg_count ,
1230 p_data => x_msg_data
1231 );
1232
1233
1234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1235 -- in case of unexpected errors. no need to manipulate the
1236 -- return status. the product teams should do an unqualified
1237 -- rollback
1238
1239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1240
1241 FND_MSG_PUB.Count_And_Get
1242 ( p_count => x_msg_count ,
1243 p_data => x_msg_data
1244 );
1245
1246 --Bug 4122622: Start
1247 WHEN CHILD_ERECORD_ID_ERROR THEN
1248 x_return_status := FND_API.G_RET_STS_ERROR;
1249 fnd_message.set_name('EDR','EDR_VAL_INVALID_CHILD_ID');
1250 fnd_message.set_token('ERECORD_ID', l_wrong_child_id);
1251 l_mesg_text := fnd_message.get();
1252 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
1253 l_api_name,
1254 l_mesg_text);
1255
1256 FND_MSG_PUB.Count_And_Get
1257 ( p_count => x_msg_count ,
1258 p_data => x_msg_data
1259 );
1260 --Bug 4122622: End
1261 WHEN OTHERS THEN
1262 -- in case of unexpected errors. no need to manipulate the
1263 -- return status. the product teams should do an unqualified
1264 -- rollback
1265
1266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1267
1268 IF FND_MSG_PUB.Check_Msg_Level
1269 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1270 THEN
1271 FND_MSG_PUB.Add_Exc_Msg
1272 ( G_PKG_NAME ,
1273 l_api_name
1274 );
1275 END IF;
1276
1277 FND_MSG_PUB.Count_And_Get
1278 (p_count => x_msg_count ,
1279 p_data => x_msg_data
1280 );
1281
1282 END RAISE_ERES_EVENT;
1283
1284
1285 --Bug 4122622: Start
1286 --This method sets the parent/child e-record details in the current event, if they
1287 --were raised earlier in the transaction.
1288 PROCEDURE SET_INTER_EVENT_DETAILS(P_EVENTS IN ERES_EVENT_TBL_TYPE,
1289 P_INDEX IN NUMBER,
1290 X_PARAMETER_LIST IN OUT NOCOPY FND_WF_EVENT.PARAM_TABLE)
1291
1292 IS
1293
1294 l_parent_erecord_index pls_integer;
1295 l_parent_name_set boolean;
1296 l_parent_key_set boolean;
1297 l_parent_erecord_set boolean;
1298
1299 l_parent_event_name varchar2(240);
1300 l_parent_event_key varchar2(240);
1301 l_parent_erecord_id varchar2(128);
1302 l_parent_erecord NUMBER;
1303
1304 l_return_status VARCHAR2(1);
1305 l_msg_count NUMBER;
1306 l_msg_data VARCHAR2(2000);
1307
1308 i pls_integer;
1309
1310 j pls_integer;
1311
1312 k pls_integer;
1313
1314 l_child_erecord_ids VARCHAR2(4000);
1315
1316 l_parameter_list FND_WF_EVENT.PARAM_TABLE;
1317
1318 begin
1319 --Initialize the boolean variables to false.
1320 l_parent_name_set := false;
1321 l_parent_key_set := false;
1322 l_parent_erecord_set := false;
1323
1324
1325 i := X_PARAMETER_LIST.FIRST;
1326
1327 --Iterate through the parameter list of the current event.
1328 --Obtain the parent event details if they are set.
1329 WHILE i is not null loop
1330 if x_parameter_list(i).param_name = EDR_CONSTANTS_GRP.g_parent_event_name then
1331 l_parent_event_name := x_parameter_list(i).param_value;
1332 l_parent_name_set := true;
1333 elsif x_parameter_list(i).param_name = EDR_CONSTANTS_GRP.g_parent_event_key then
1334 l_parent_event_key := x_parameter_list(i).param_value;
1335 l_parent_key_set := true;
1336 elsif x_parameter_list(i).param_name = EDR_CONSTANTS_GRP.g_parent_erecord_id then
1337 l_parent_erecord_id := x_parameter_list(i).param_value;
1338 l_parent_erecord_set := true;
1339 k := i;
1340 end if;
1341
1342 --If the parent event details are set then exit.
1343 if l_parent_name_set and l_parent_key_set and l_parent_erecord_set then
1344 exit;
1345 end if;
1346
1347 i := x_parameter_list.NEXT(i);
1348
1349 end loop;
1350
1351 if l_parent_name_set and l_parent_key_set and l_parent_erecord_set then
1352 --If the parent event details are set and the parent e-record ID is set to -1,
1353 --then fetch the corresponding e-record ID of the parent.
1354
1355 if l_parent_erecord_id is not null and l_parent_erecord_id = EDR_CONSTANTS_GRP.G_DEFAULT_CHAR_ID_VALUE then
1356 GET_ERECORD_ID
1357 ( p_api_version => 1.0,
1358 p_init_msg_list => FND_API.G_FALSE,
1359 x_return_status => l_return_status,
1360 x_msg_count => l_msg_count,
1361 x_msg_data => l_msg_data,
1362 p_events => p_events,
1363 p_event_name => l_parent_event_name,
1364 p_event_key => l_parent_event_key,
1365 x_erecord_id => l_parent_erecord
1366 );
1367
1368 -- If any errors happen abort API.
1369 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1371 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1372 RAISE FND_API.G_EXC_ERROR;
1373 END IF;
1374
1375
1376 if l_parent_erecord is not null and l_parent_erecord <> EDR_CONSTANTS_GRP.G_DEFAULT_NUM_PARAM_VALUE
1377 then
1378 l_parent_erecord_id := to_char(l_parent_erecord);
1379 x_parameter_list(k).param_value := l_parent_erecord_id;
1380 end if;
1381 end if;
1382 end if;
1383
1384
1385 --The parent details have been set
1386 --Now we need to obtain the child details.
1387 j := p_events.FIRST;
1388
1389 --Iterate through each event in the list raised prior to the current event.
1390 WHILE j is not null and j < p_index loop
1391 if p_events(j).erecord_id is not null and p_events(j).erecord_id > 0 then
1392 --Create the payload parameter list for each event
1393 EDR_ERES_EVENT_PVT.CREATE_PAYLOAD
1394 ( p_event => p_events(j),
1395 p_starting_position => 1,
1396 x_payload => l_parameter_list
1397 );
1398 i := L_PARAMETER_LIST.FIRST;
1399
1400 --Iterate through the parameter list. Obtain the parent event details if
1401 --they exist.
1402 WHILE i is not null loop
1403 if l_parameter_list(i).param_name = EDR_CONSTANTS_GRP.g_parent_event_name then
1404 l_parent_event_name := l_parameter_list(i).param_value;
1405 l_parent_name_set := true;
1406 elsif l_parameter_list(i).param_name = EDR_CONSTANTS_GRP.g_parent_event_key then
1407 l_parent_event_key := l_parameter_list(i).param_value;
1408 l_parent_key_set := true;
1409 elsif l_parameter_list(i).param_name = EDR_CONSTANTS_GRP.g_parent_erecord_id then
1410 l_parent_erecord_id := l_parameter_list(i).param_value;
1411 l_parent_erecord_set := true;
1412 end if;
1413
1414
1415
1416 --if the parent event details were found and the parent e-record ID is set to -1 then
1417 --append it the event's e-record ID to a comma separated string. Primarily this means
1418 --that the event under scrutiny is a child of the event being processed in this procedure.
1419 if l_parent_name_set and l_parent_key_set and l_parent_erecord_set then
1420
1421 --Reset the boolean values to false.
1422 l_parent_name_set := false;
1423 l_parent_key_set := false;
1424 l_parent_erecord_set := false;
1425
1426 if l_parent_event_name = p_events(p_index).event_name and l_parent_event_key = p_events(p_index).event_key
1427 and l_parent_erecord_id = EDR_CONSTANTS_GRP.G_DEFAULT_CHAR_ID_VALUE then
1428
1429 if l_child_erecord_ids is null then
1430 l_child_erecord_ids := to_char(p_events(j).erecord_id);
1431 else
1432 l_child_erecord_ids := l_child_erecord_ids ||','||to_char(p_events(j).erecord_id);
1433 end if;
1434 end if;
1435 --As the parent event details have been found, exit the loop.
1436 exit;
1437 end if;
1438 i := l_parameter_list.NEXT(i);
1439 end loop;
1440 end if;
1441 j := p_events.NEXT(j);
1442 end loop;
1443
1444 --If the child e-record ID string was set then set it on the event payload
1445 --through the parameter list.
1446 if l_child_erecord_ids is not null then
1447 j := null;
1448 i := x_parameter_list.FIRST;
1449 while i is not null loop
1450 j := i;
1451 i := x_parameter_list.NEXT(i);
1452 end loop;
1453 if j is not null then
1454 x_parameter_list(j+1).param_name := EDR_CONSTANTS_GRP.G_CHILD_ERECORD_IDS;
1455 x_parameter_list(j+1).param_value := l_child_erecord_ids;
1456 end if;
1457 end if;
1458 end SET_INTER_EVENT_DETAILS;
1459 --Bug 4122622: End
1460
1461
1462 /** Raise a set of ERES events in Inter-Event mode **/
1463
1464 PROCEDURE RAISE_INTER_EVENT
1465 ( p_api_version IN NUMBER ,
1466 p_init_msg_list IN VARCHAR2 ,
1467 p_validation_level IN NUMBER ,
1468 x_return_status OUT NOCOPY VARCHAR2 ,
1469 x_msg_count OUT NOCOPY NUMBER ,
1470 x_msg_data OUT NOCOPY VARCHAR2 ,
1471 x_events IN OUT NOCOPY ERES_EVENT_TBL_TYPE ,
1472 x_overall_status OUT NOCOPY VARCHAR2
1473 )
1474 AS
1475 l_return_status VARCHAR2(1);
1476 l_msg_count NUMBER;
1477 l_msg_data VARCHAR2(2000);
1478
1479 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_INTER_EVENT';
1480 l_api_version CONSTANT NUMBER := 1.0;
1481
1482 l_child BOOLEAN := FALSE;
1483 l_inter_event_tbl
1484 EDR_EVENT_RELATIONSHIP_PUB.INTER_EVENT_TBL_TYPE;
1485 rel_number pls_integer := 1;
1486
1487 l_relationship_id NUMBER;
1488
1489 l_parameter_list FND_WF_EVENT.PARAM_TABLE;
1490 l_param_name varchar2(30);
1491 l_param_value varchar2(2000);
1492
1493 l_parent_event_name varchar2(240);
1494 l_parent_event_key varchar2(2000);
1495 l_parent_erecord_id number;
1496
1497 i pls_integer;
1498 j pls_integer;
1499
1500 BEGIN
1501 -- Standard call to check for call compatibility.
1502 IF NOT FND_API.Compatible_API_Call (l_api_version ,
1503 p_api_version ,
1504 l_api_name ,
1505 G_PKG_NAME )
1506 THEN
1507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1508 END IF;
1509
1510 -- Initialize message list if p_init_msg_list is set to TRUE.
1511 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1512 FND_MSG_PUB.initialize;
1513 END IF;
1514
1515 -- Initialize API return status to success
1516 x_return_status := FND_API.G_RET_STS_SUCCESS;
1517
1518 -- API Body
1519
1520 --valiadate all the payloads only then start another loop
1521 --to raise the events and so forth
1522
1523 if (p_validation_level > FND_API.G_VALID_LEVEL_NONE) then
1524 i := x_events.FIRST;
1525 while i is not null loop
1526
1527 --Bug 3136403: Start
1528 EDR_ERES_EVENT_PVT.CREATE_PAYLOAD
1529 ( p_event => x_events(i) ,
1530 p_starting_position => 1 ,
1531 x_payload => l_parameter_list
1532 );
1533
1534 VALIDATE_PAYLOAD
1535 ( p_api_version => 1.0 ,
1536 p_init_msg_list => FND_API.G_FALSE,
1537 x_return_status => l_return_status ,
1538 x_msg_count => l_msg_count ,
1539 x_msg_data => l_msg_data ,
1540 p_event_name => x_events(i).event_name,
1541 p_event_key => x_events(i).event_key,
1542 p_payload => l_parameter_list ,
1543 p_mode => EDR_CONSTANTS_GRP.g_db_mode
1544 );
1545
1546 /*
1547 VALIDATE_PAYLOAD
1548 ( p_api_version => 1.0 ,
1549 p_init_msg_list => FND_API.G_FALSE,
1550 x_return_status => l_return_status ,
1551 x_msg_count => l_msg_count ,
1552 x_msg_data => l_msg_data ,
1553 p_event_name => x_events(i).event_name,
1554 p_event_key => x_events(i).event_key,
1555 p_payload => x_events(i).payload ,
1556 p_mode => EDR_CONSTANTS_GRP.g_db_mode
1557 );
1558 */
1559 --Bug 3136403: End
1560
1561 -- If any errors happen abort API.
1562 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1563 THEN
1564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1565 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1566 THEN
1567 RAISE FND_API.G_EXC_ERROR;
1568 END IF;
1569
1570 i := x_events.NEXT(i);
1571 end loop;
1572
1573 end if;
1574
1575 --raise eres events for each event in the event list
1576 i := x_events.FIRST;
1577 while i is not null loop
1578
1579 --Bug 4122622: Start
1580 --Create the event payload parameter list.
1581 EDR_ERES_EVENT_PVT.CREATE_PAYLOAD
1582 ( p_event => x_events(i),
1583 p_starting_position => 4,
1584 x_payload => l_parameter_list
1585 );
1586 --Set the parent/child event details if required.
1587 SET_INTER_EVENT_DETAILS
1588 ( P_EVENTS => X_EVENTS,
1589 P_INDEX => i,
1590 x_parameter_list => l_parameter_list
1591 );
1592 --Bug 4122622: End
1593 -- raise the event
1594 --since we have already done the validation set
1595 --the validation level to none
1596
1597 EDR_ERES_EVENT_PVT.RAISE_EVENT
1598 ( p_api_version => 1.0 ,
1599 p_init_msg_list => FND_API.G_FALSE ,
1600 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
1601 x_return_status => l_return_status ,
1602 x_msg_count => l_msg_count ,
1603 x_msg_data => l_msg_data ,
1604 x_event => x_events(i) ,
1605 p_mode => null ,
1606 x_is_child_event => l_child,
1607 --Bug 4122622: Start
1608 --Pass the parameter list variable while raising the event.
1609 p_parameter_list => l_parameter_list
1610 --Bug 4122622: End
1611 );
1612
1613 -- If any errors happen abort API.
1614 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1616 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1617 RAISE FND_API.G_EXC_ERROR;
1618 END IF;
1619
1620 --child event: now do processing in case this is a child event
1621 --in an inter event context
1622
1623 if(l_child = TRUE AND x_events(i).erecord_id is not null) then
1624
1625 --Bug 3136403: Start
1626 EDR_ERES_EVENT_PVT.CREATE_PAYLOAD
1627 ( p_event => x_events(i) ,
1628 p_starting_position => 1 ,
1629 x_payload => l_parameter_list
1630 );
1631 j := l_parameter_list.FIRST;
1632 --j := x_events(i).payload.FIRST;
1633
1634 while j is not null loop
1635
1636 l_param_name := l_parameter_list(j).Param_Name;
1637 l_param_value := l_parameter_list(j).Param_Value;
1638
1639 --l_param_name := x_events(i).payload(j).Param_Name;
1640 --l_param_value := x_events(i).payload(j).Param_Value;
1641
1642 if (l_param_name = EDR_CONSTANTS_GRP.g_parent_event_name)
1643 then
1644 l_inter_event_tbl(rel_number).parent_event_name
1645 := l_param_value;
1646 end if;
1647
1648 if (l_param_name = EDR_CONSTANTS_GRP.g_parent_event_key)
1649 then
1650 l_inter_event_tbl(rel_number).parent_event_key
1651 := l_param_value;
1652 end if;
1653
1654 if (l_param_name = EDR_CONSTANTS_GRP.g_parent_erecord_id)
1655 then
1656 l_inter_event_tbl(rel_number).parent_erecord_id
1657 := l_param_value;
1658 end if;
1659
1660 l_inter_event_tbl(rel_number).child_event_name
1661 := x_events(i).event_name;
1662 l_inter_event_tbl(rel_number).child_event_key
1663 := x_events(i).event_key;
1664 l_inter_event_tbl(rel_number).child_erecord_id
1665 := x_events(i).erecord_id;
1666
1667 j := l_parameter_list.NEXT(j);
1668 --j := x_events(i).payload.NEXT(j);
1669 --Bug 3136403: End
1670
1671 end loop;
1672
1673 --increment the counter for the relationship table type
1674 rel_number := rel_number + 1;
1675
1676 end if; --end of the if for child event
1677
1678 i := x_events.NEXT(i);
1679 end loop;
1680
1681 -- find out the overall status
1682 -- this status is just for the events. if there are any problems
1683 -- after this point this status would not be affected
1684
1685 x_overall_status := GET_OVERALL_STATUS(x_events);
1686
1687 --relationship: if there are rows in the relationship table
1688 --then post them to DB
1689
1690 if (l_inter_event_tbl.COUNT > 0) then
1691 for i IN l_inter_event_tbl.FIRST..l_inter_event_tbl.LAST
1692 loop
1693 if (l_inter_event_tbl(i).parent_erecord_id =
1694 EDR_CONSTANTS_GRP.g_default_num_param_value)
1695 then
1696
1697 GET_ERECORD_ID
1698 ( p_api_version => 1.0,
1699 p_init_msg_list => FND_API.G_FALSE ,
1700 x_return_status => l_return_status ,
1701 x_msg_count => l_msg_count ,
1702 x_msg_data => l_msg_data ,
1703 p_events => x_events ,
1704 p_event_name => l_inter_event_tbl(i).parent_event_name,
1705 p_event_key => l_inter_event_tbl(i).parent_event_key ,
1706 x_erecord_id => l_inter_event_tbl(i).parent_erecord_id
1707 );
1708
1709 end if;
1710
1711 -- If any errors happen abort API.
1712 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1714 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1715 RAISE FND_API.G_EXC_ERROR;
1716 END IF;
1717
1718 -- insert a row in the event relationship table
1719 -- we still need to do the validation
1720
1721 -- Call create relationship only when both parent and child eRecords are valid
1722 -- add if condition to resolve bug 3129588
1723 IF NVL(l_inter_event_tbl(i).child_erecord_id, -1) <> -1 AND
1724 NVL(l_inter_event_tbl(i).parent_erecord_id, -1) <> -1 THEN
1725 EDR_EVENT_RELATIONSHIP_PUB.CREATE_RELATIONSHIP
1726 (p_api_version => 1.0 ,
1727 p_init_msg_list => FND_API.G_FALSE ,
1728 p_commit => FND_API.G_FALSE ,
1729 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
1730 x_return_status => l_return_status ,
1731 x_msg_count => l_msg_count ,
1732 x_msg_data => l_msg_data ,
1733 P_PARENT_ERECORD_ID => l_inter_event_tbl(i).parent_erecord_id ,
1734 P_PARENT_EVENT_NAME => l_inter_event_tbl(i).parent_event_name ,
1735 P_PARENT_EVENT_KEY => l_inter_event_tbl(i).parent_event_key ,
1736 P_CHILD_ERECORD_ID => l_inter_event_tbl(i).child_erecord_id ,
1737 P_CHILD_EVENT_NAME => l_inter_event_tbl(i).child_event_name ,
1738 P_CHILD_EVENT_KEY => l_inter_event_tbl(i).child_event_key ,
1739 X_RELATIONSHIP_ID => l_relationship_id
1740 );
1741 END IF;
1742
1743 -- If any errors happen abort API.
1744 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1746 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1747 RAISE FND_API.G_EXC_ERROR;
1748 END IF;
1749
1750 end loop;
1751
1752 end if; --end of if for relationship
1753
1754
1755 -- End of API Body
1756
1757 -- Standard call to get message count and if count is 1,
1758 --get message info.
1759 FND_MSG_PUB.Count_And_Get
1760 ( p_count => x_msg_count ,
1761 p_data => x_msg_data
1762 );
1763
1764 EXCEPTION
1765 WHEN FND_API.G_EXC_ERROR THEN
1766 x_overall_status := EDR_CONSTANTS_GRP.g_error_status;
1767 x_return_status := FND_API.G_RET_STS_ERROR ;
1768
1769 FND_MSG_PUB.Count_And_Get
1770 ( p_count => x_msg_count ,
1771 p_data => x_msg_data
1772 );
1773
1774 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1775 -- no need to manipulate the overall status. product teams should
1776 -- do an unqualified rollback on getting this exception
1777
1778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1779
1780 FND_MSG_PUB.Count_And_Get
1781 ( p_count => x_msg_count ,
1782 p_data => x_msg_data
1783 );
1784
1785 WHEN OTHERS THEN
1786 -- no need to manipulate the overall status. product teams should
1787 -- do an unqualified rollback on getting this exception
1788
1789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1790
1791 IF FND_MSG_PUB.Check_Msg_Level
1792 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1793 THEN
1794 FND_MSG_PUB.Add_Exc_Msg
1795 ( G_PKG_NAME ,
1796 l_api_name
1797 );
1798 END IF;
1799
1800 FND_MSG_PUB.Count_And_Get
1801 (p_count => x_msg_count ,
1802 p_data => x_msg_data
1803 );
1804
1805
1806 END RAISE_INTER_EVENT;
1807
1808 /** Get the erecord id for an event from a table of eres events **/
1809 PROCEDURE GET_ERECORD_ID
1810 ( p_api_version IN NUMBER ,
1811 p_init_msg_list IN VARCHAR2 ,
1812 x_return_status OUT NOCOPY VARCHAR2 ,
1813 x_msg_count OUT NOCOPY NUMBER ,
1814 x_msg_data OUT NOCOPY VARCHAR2 ,
1815 p_events IN ERES_EVENT_TBL_TYPE ,
1816 p_event_name IN VARCHAR2 ,
1817 p_event_key IN VARCHAR2 ,
1818 x_erecord_id OUT NOCOPY NUMBER
1819 )
1820 AS
1821 l_api_name CONSTANT VARCHAR2(30) := 'GET_ERECORD_ID';
1822 l_api_version CONSTANT NUMBER := 1.0;
1823
1824 i pls_integer;
1825 BEGIN
1826 -- Standard call to check for call compatibility.
1827 IF NOT FND_API.Compatible_API_Call (l_api_version ,
1828 p_api_version ,
1829 l_api_name ,
1830 G_PKG_NAME )
1831 THEN
1832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1833 END IF;
1834
1835 -- Initialize message list if p_init_msg_list is set to TRUE.
1836 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1837 FND_MSG_PUB.initialize;
1838 END IF;
1839
1840 -- Initialize API return status to success
1841 x_return_status := FND_API.G_RET_STS_SUCCESS;
1842
1843 -- API Body
1844
1845 i := p_events.FIRST;
1846 while i is not null loop
1847 if (p_events(i).event_name = p_event_name
1848 AND p_events(i).event_key = p_event_key)
1849 then
1850 x_erecord_id := p_events(i).erecord_id;
1851 exit;
1852 end if;
1853 i := p_events.NEXT(i);
1854 end loop;
1855 -- Standard call to get message count and if count is 1,
1856 --get message info.
1857 FND_MSG_PUB.Count_And_Get
1858 ( p_count => x_msg_count ,
1859 p_data => x_msg_data
1860 );
1861
1862 EXCEPTION
1863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1865
1866 FND_MSG_PUB.Count_And_Get
1867 ( p_count => x_msg_count ,
1868 p_data => x_msg_data
1869 );
1870
1871 WHEN OTHERS THEN
1872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1873
1874 IF FND_MSG_PUB.Check_Msg_Level
1875 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1876 THEN
1877 FND_MSG_PUB.Add_Exc_Msg
1878 ( G_PKG_NAME ,
1879 l_api_name
1880 );
1881 END IF;
1882
1883 FND_MSG_PUB.Count_And_Get
1884 (p_count => x_msg_count ,
1885 p_data => x_msg_data
1886 );
1887
1888
1889 END GET_ERECORD_ID;
1890
1891 end EDR_ERES_EVENT_PUB;