1 PACKAGE BODY EDR_EVENT_RELATIONSHIP_PVT AS
2 /* $Header: EDRVRELB.pls 120.0.12000000.1 2007/01/18 05:56:40 appldev ship $*/
3
4 -- Start of comments
5 -- API name : STORE_INTER_EVENT_AUTONOMOUS
6 -- Type : Private.
7 -- Function : Stores the inter event relationship information into
8 -- the databse in an autonomous manner. This API does a bulk
9 -- upload of a number of relationship records at one time
10 -- and does an autonomous commit.
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- IN :p_api_version IN NUMBER Required
14 -- p_init_msg_list IN VARCHAR2 Optional
15 -- Default = FND_API.G_FALSE
16 -- p_inter_event_tbl IN INTER_EVENT_TBL_TYPE Required
17 --
18 -- OUT :x_return_status OUT VARCHAR2
19 -- x_msg_count OUT NUMBER
20 -- x_msg_data OUT VARCHAR2
21 --
22 -- Version :Current version 1.0
23 -- Initial version 1.0
24 --
25 -- Notes :
26 --
27 -- End of comments
28
29
30 PROCEDURE STORE_INTER_EVENT_AUTONOMOUS
31 ( p_api_version IN NUMBER ,
32 p_init_msg_list IN VARCHAR2 ,
33 x_return_status OUT NOCOPY VARCHAR2 ,
34 x_msg_count OUT NOCOPY NUMBER ,
35 x_msg_data OUT NOCOPY VARCHAR2 ,
36 p_inter_event_tbl IN
37 EDR_EVENT_RELATIONSHIP_PUB.INTER_EVENT_TBL_TYPE
38 ) AS PRAGMA AUTONOMOUS_TRANSACTION;
39 l_api_name CONSTANT VARCHAR2(30) := 'STORE_INTER_EVENT_AUTONOMOUS';
40 l_api_version CONSTANT NUMBER := 1.0;
41 i NUMBER;
42 l_relationship_id NUMBER;
43 L_RETURN_STATUS VARCHAR2(1);
44 L_MSG_COUNT NUMBER;
45 L_MSG_index NUMBER;
46 L_MSG_data VARCHAR2(2000);
47
48 BEGIN
49 -- Standard call to check for call compatibility.
50 IF NOT FND_API.Compatible_API_Call (l_api_version ,
51 p_api_version ,
52 l_api_name ,
53 G_PKG_NAME )
54 THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
56 END IF;
57
58 -- Initialize message list if p_init_msg_list is set to TRUE.
59 IF FND_API.to_Boolean( p_init_msg_list ) THEN
60 FND_MSG_PUB.initialize;
61 END IF;
62
63 -- Initialize API return status to success
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66 -- API Body
67 -- API Body
68
69 i := p_inter_event_tbl.FIRST;
70
71 -- this loop would read each row of the input table and insert
72 -- a corresponding row in the relationship table
73 -- if any error occurs it would rollback, thereby erasing all
74 -- of the inserted rows in the relationship table tiil that point
75 -- (by this call only)
76 -- Inteer event table data is already validated by calling routine
77 -- because of this we need not validate data again
78
79 while i is not null loop
80 /* BUG Fix 3135128. SKARIMIS . Added a IF condition to eliminate the posting of orphan childs*/
81 IF (p_inter_event_tbl(i).parent_erecord_id is not NULL) THEN
82 /* Bugfix 3169361 SRPURI added new condition AND NVL(p_inter_event_tbl(i).child_erecord_id,-1) <> -1
83 to resolve issue when child is not required */
84
85 IF (p_inter_event_tbl(i).parent_erecord_id <> -1) AND
86 NVL(p_inter_event_tbl(i).child_erecord_id,-1) <> -1 THEN
87
88 edr_event_relationship_pub.CREATE_RELATIONSHIP
89 ( p_api_version => 1.0 ,
90 p_init_msg_list => FND_API.G_FALSE ,
91 p_commit => FND_API.G_FALSE ,
92 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
93 x_return_status => l_return_status ,
94 x_msg_count => l_msg_count ,
95 x_msg_data => l_msg_data ,
96 p_parent_erecord_id => p_inter_event_tbl(i).parent_erecord_id ,
97 p_parent_event_name => p_inter_event_tbl(i).parent_event_name ,
98 p_parent_event_key => p_inter_event_tbl(i).parent_event_key ,
99 p_child_erecord_id => p_inter_event_tbl(i).child_erecord_id ,
100 p_child_event_name => p_inter_event_tbl(i).child_event_name ,
101 p_child_event_key => p_inter_event_tbl(i).child_event_key ,
102 x_relationship_id => l_relationship_id
103 );
104
105 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
108 RAISE FND_API.G_EXC_ERROR;
109 END IF;
110 END IF;
111 END IF;
112 i := p_inter_event_tbl.NEXT(i);
113 end LOOP;
114
115 -- unconditional commit as this is an autonomous txn
116 COMMIT;
117
118 FND_MSG_PUB.Count_And_Get
119 (p_count => x_msg_count ,
120 p_data => x_msg_data
121 );
122
123
124 EXCEPTION
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 ROLLBACK;
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
128
129
130 FND_MSG_PUB.Count_And_Get
131 (p_count => x_msg_count ,
132 p_data => x_msg_data
133 );
134
135 --this would get the message and can be used in the case of
136 --an error
137 if (x_msg_count > 1) then
138 fnd_msg_pub.get
139 ( p_data => l_msg_data ,
140 p_msg_index_out => l_msg_index
141 );
142 end if;
143
144 fnd_message.set_encoded(l_msg_data);
145 APP_EXCEPTION.RAISE_EXCEPTION;
146
147 WHEN FND_API.G_EXC_ERROR THEN
148 ROLLBACK;
149 x_return_status := FND_API.G_RET_STS_ERROR ;
150
151 FND_MSG_PUB.Count_And_Get
152 (p_count => x_msg_count ,
153 p_data => x_msg_data
154 );
155
156 --this would get the message and can be used in the case of
157 --an error
158 if (x_msg_count > 1) then
159 fnd_msg_pub.get
160 ( p_data => l_msg_data ,
161 p_msg_index_out => l_msg_index
162 );
163 end if;
164
165 fnd_message.set_encoded(l_msg_data);
166 APP_EXCEPTION.RAISE_EXCEPTION;
167
168 WHEN OTHERS THEN
169 ROLLBACK;
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
171
172 IF FND_MSG_PUB.Check_Msg_Level
173 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
174 THEN
175 FND_MSG_PUB.Add_Exc_Msg
176 ( G_PKG_NAME ,
177 l_api_name
178 );
179 END IF;
180
181 FND_MSG_PUB.Count_And_Get
182 (p_count => x_msg_count ,
183 p_data => x_msg_data
184 );
185
186 --this would get the message and can be used in the case of
187 --an error
188 if (x_msg_count > 1) then
189 fnd_msg_pub.get
190 ( p_data => l_msg_data ,
191 p_msg_index_out => l_msg_index
192 );
193 end if;
194
195 fnd_message.set_encoded(l_msg_data);
196 APP_EXCEPTION.RAISE_EXCEPTION;
197
198 END STORE_INTER_EVENT_AUTONOMOUS;
199
200
201 PROCEDURE STORE_INTER_EVENT
202 ( p_api_version IN NUMBER ,
203 p_init_msg_list IN VARCHAR2 ,
204 x_return_status OUT NOCOPY VARCHAR2 ,
205 x_msg_count OUT NOCOPY NUMBER ,
206 x_msg_data OUT NOCOPY VARCHAR2 ,
207 p_inter_event_tbl IN
208 EDR_EVENT_RELATIONSHIP_PUB.INTER_EVENT_TBL_TYPE
209 )
210 AS
211 l_api_name CONSTANT VARCHAR2(30) := 'STORE_INTER_EVENT';
212 l_api_version CONSTANT NUMBER := 1.0;
213
214 i NUMBER;
215 l_relationship_id NUMBER;
216 L_RETURN_STATUS VARCHAR2(1);
217 L_MSG_COUNT NUMBER;
218 L_MSG_index NUMBER;
219 L_MSG_data VARCHAR2(2000);
220
221 BEGIN
222 -- Standard call to check for call compatibility.
223 IF NOT FND_API.Compatible_API_Call (l_api_version ,
224 p_api_version ,
225 l_api_name ,
226 G_PKG_NAME )
227 THEN
228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
229 END IF;
230
231 -- Initialize message list if p_init_msg_list is set to TRUE.
232 IF FND_API.to_Boolean( p_init_msg_list ) THEN
233 FND_MSG_PUB.initialize;
234 END IF;
235
236 -- Initialize API return status to success
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238
239 -- API Body
240
241 i := p_inter_event_tbl.FIRST;
242
243 -- this loop would read each row of the input table
244 -- and validates relationship data.
245 --
246 --
247
248 while i is not null loop
249
250 /* BUG Fix 3135128. SKARIMIS . Added a IF condition to eliminate the validation of orphan childs*/
251 IF (p_inter_event_tbl(i).parent_erecord_id is not NULL) THEN
252
253 /* Bugfix 3169361 SRPURI added new condition AND NVL(p_inter_event_tbl(i).child_erecord_id,-1) <> -1
254 to resolve issue when child is not required */
255 IF (p_inter_event_tbl(i).parent_erecord_id <> -1 AND
256 NVL(p_inter_event_tbl(i).child_erecord_id,-1) <> -1 ) THEN
257
258 EDR_EVENT_RELATIONSHIP_PUB.VALIDATE_RELATIONSHIP
259 ( p_api_version => 1.0,
260 x_return_status => l_return_status,
261 x_msg_count => l_msg_count,
262 x_msg_data => l_msg_data,
263 P_PARENT_ERECORD_ID => p_inter_event_tbl(i).parent_erecord_id ,
264 P_PARENT_EVENT_NAME => p_inter_event_tbl(i).parent_event_name ,
265 P_PARENT_EVENT_KEY => p_inter_event_tbl(i).parent_event_key ,
266 P_CHILD_ERECORD_ID => p_inter_event_tbl(i).child_erecord_id ,
267 P_CHILD_EVENT_NAME => p_inter_event_tbl(i).child_event_name ,
268 P_CHILD_EVENT_KEY => p_inter_event_tbl(i).child_event_key
269 );
270
271 -- If any errors happen abort API.
272 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
274 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
275 RAISE FND_API.G_EXC_ERROR;
276 END IF;
277 END IF;
278 END IF;
279
280 i := p_inter_event_tbl.NEXT(i);
281 end LOOP;
282 --
283 -- Validation is completed
284 -- Now we can commit the data autonomously
285 --
286 STORE_INTER_EVENT_AUTONOMOUS(p_api_version => 1.0,
287 p_init_msg_list => p_init_msg_list,
288 x_return_status => l_return_status,
289 x_msg_count => l_msg_count,
290 x_msg_data => l_msg_data,
291 p_inter_event_tbl => p_inter_event_tbl);
292
293 FND_MSG_PUB.Count_And_Get
294 (p_count => x_msg_count ,
295 p_data => x_msg_data
296 );
297
298
299 EXCEPTION
300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
302
303
304 FND_MSG_PUB.Count_And_Get
305 (p_count => x_msg_count ,
306 p_data => x_msg_data
307 );
308
309 --this would get the message and can be used in the case of
310 --an error
311 if (x_msg_count > 1) then
312 fnd_msg_pub.get
313 ( p_data => l_msg_data ,
314 p_msg_index_out => l_msg_index
315 );
316 end if;
317
318 fnd_message.set_encoded(l_msg_data);
319 APP_EXCEPTION.RAISE_EXCEPTION;
320
321 WHEN FND_API.G_EXC_ERROR THEN
322 x_return_status := FND_API.G_RET_STS_ERROR ;
323
324 FND_MSG_PUB.Count_And_Get
325 (p_count => x_msg_count ,
326 p_data => x_msg_data
327 );
328
329 --this would get the message and can be used in the case of
330 --an error
331 if (x_msg_count > 1) then
332 fnd_msg_pub.get
333 ( p_data => l_msg_data ,
334 p_msg_index_out => l_msg_index
335 );
336 end if;
337
338 fnd_message.set_encoded(l_msg_data);
339 APP_EXCEPTION.RAISE_EXCEPTION;
340
341 WHEN OTHERS THEN
342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
343
344 IF FND_MSG_PUB.Check_Msg_Level
345 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
346 THEN
347 FND_MSG_PUB.Add_Exc_Msg
348 ( G_PKG_NAME ,
349 l_api_name
350 );
351 END IF;
352
353 FND_MSG_PUB.Count_And_Get
354 (p_count => x_msg_count ,
355 p_data => x_msg_data
356 );
357
358 --this would get the message and can be used in the case of
359 --an error
360 if (x_msg_count > 1) then
361 fnd_msg_pub.get
362 ( p_data => l_msg_data ,
363 p_msg_index_out => l_msg_index
364 );
365 end if;
366
367 fnd_message.set_encoded(l_msg_data);
368 APP_EXCEPTION.RAISE_EXCEPTION;
369
370 END STORE_INTER_EVENT;
371
372
373 -- Bug 3667036: Start
374 PROCEDURE ESTABLISH_RELATIONSHIP
375 (PARENT_CHILD_RECORD IN PARENT_CHILD_TBL)
376 AS
377 L_RETURN_STATUS VARCHAR2(10);
378 L_MSG_COUNT NUMBER;
379 L_MSG_DATA VARCHAR2(2000);
380 L_RELATIONSHIP_ID NUMBER;
381 L_CHILD_EVENT_NAME VARCHAR2(80);
382 L_CHILD_EVENT_KEY VARCHAR2(240);
383 BEGIN
384
385 FOR I IN 1..PARENT_CHILD_RECORD.count loop
386
387 L_CHILD_EVENT_NAME := parent_child_record(i).child_event_name;
388
389 if length(l_child_event_name) = 0 then
390
391 edr_ctx_pkg.set_secure_attr;
392 select event_name,event_key into l_child_event_name,l_child_event_key
393 from edr_psig_documents
394 where document_id = parent_child_record(i).child_erecord_id;
395 edr_ctx_pkg.unset_secure_attr;
396 edr_event_relationship_pub.CREATE_RELATIONSHIP
397 (p_api_version => 1.0,
398 p_init_msg_list => FND_API.G_FALSE,
399 p_commit => FND_API.G_TRUE,
400 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
401 x_return_status => L_RETURN_STATUS,
402 x_msg_count => L_MSG_COUNT,
403 x_msg_data => L_MSG_DATA,
404 p_parent_erecord_id => PARENT_CHILD_RECORD(i).PARENT_ERECORD_ID,
405 p_parent_event_name => PARENT_CHILD_RECORD(i).PARENT_EVENT_NAME,
406 p_parent_event_key => PARENT_CHILD_RECORD(i).PARENT_EVENT_KEY,
407 p_child_erecord_id => PARENT_CHILD_RECORD(i).CHILD_ERECORD_ID,
408 p_child_event_name => L_CHILD_EVENT_NAME,
409 p_child_event_key => L_CHILD_EVENT_KEY,
410 x_relationship_id => L_RELATIONSHIP_ID);
411
412 else
413
414 --Create a relationship for each record in the parent_child_table type
415 edr_event_relationship_pub.CREATE_RELATIONSHIP
416 (p_api_version => 1.0,
417 p_init_msg_list => FND_API.G_FALSE,
418 p_commit => FND_API.G_TRUE,
419 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
420 x_return_status => L_RETURN_STATUS,
421 x_msg_count => L_MSG_COUNT,
422 x_msg_data => L_MSG_DATA,
423 p_parent_erecord_id => PARENT_CHILD_RECORD(i).PARENT_ERECORD_ID,
424 p_parent_event_name => PARENT_CHILD_RECORD(i).PARENT_EVENT_NAME,
425 p_parent_event_key => PARENT_CHILD_RECORD(i).PARENT_EVENT_KEY,
426 p_child_erecord_id => PARENT_CHILD_RECORD(i).CHILD_ERECORD_ID,
427 p_child_event_name => PARENT_CHILD_RECORD(i).CHILD_EVENT_NAME,
428 p_child_event_key => PARENT_CHILD_RECORD(i).CHILD_EVENT_KEY,
429 x_relationship_id => L_RELATIONSHIP_ID);
430
431 end if;
432
433 end loop;
434
435 END ESTABLISH_RELATIONSHIP;
436
437 --This procedure has been primarily defined to verify if the
438 --specified event exists in the evidence store.
439 PROCEDURE VALIDATE_PARENT(P_PARENT_EVENT_NAME IN VARCHAR2,
440 P_PARENT_EVENT_KEY IN VARCHAR2,
441 P_PARENT_ERECORD_ID IN NUMBER
442 )
443 IS
444
445 --Temporary count variable
446 l_count NUMBER := 0;
447
448 --This cursor is defined to query the evidence store
449 cursor l_psig_count_csr is
450 select count(*) from edr_psig_documents
451 where event_name = p_parent_event_name
452 and event_key = p_parent_event_key
453 and document_id = p_parent_erecord_id;
454
455 --This cursor is defined to query the workflow event details view.
456 cursor l_wf_count_csr is
457 select count(*) from wf_events_vl
458 where name = p_parent_event_name;
459
460 PARENT_EVENT_NOT_FOUND EXCEPTION ;
461 INVALID_PARENT EXCEPTION;
462
463 begin
464
465 --If parent e-record id is -1 then the parent event is part of the current
466 --transaction. Hence just verify if the event has been defined in workflow.
467 if p_parent_erecord_id = -1 then
468
469 --Query the workflow events view using the cursor for the specified parent
470 --event name.
471 open l_wf_count_csr;
472 fetch l_wf_count_csr into l_count;
473 close l_wf_count_csr;
474
475 --If count is zero then parent event is not defined.
476 --Hence raise an exception.
477 if l_count = 0 then
478 RAISE PARENT_EVENT_NOT_FOUND;
479
480 end if;
481
482 else
483
484 --The parent e-record ID has also been defined.
485 --Hence query evidence store to check if the parent e-record exists.
486
487 --Set the secure context to enable direct query on evidence store.
488 edr_ctx_pkg.set_secure_attr;
489
490 --Query the evidence store using the cursor for the specified parent
491 --e-record.
492 open l_psig_count_csr;
493 fetch l_psig_count_csr into l_count;
494 close l_psig_count_csr;
495 edr_ctx_pkg.unset_secure_attr;
496 --If count is zero then the specified parent e-record does not exist.
497 --Hence raise an exception.
498 if l_count = 0 then
499
500 RAISE INVALID_PARENT;
501
502 end if;
503
504 end if;
505
506 EXCEPTION
507 --Handle the exceptions by setting the error code.
508 WHEN PARENT_EVENT_NOT_FOUND THEN
509 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_PARENT_ERROR');
510 fnd_message.set_token('EVENT_NAME',P_PARENT_EVENT_NAME);
511 fnd_message.set_token('EVENT_KEY',P_PARENT_EVENT_KEY);
512 --Diagnostics Start
513 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
514 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
515 'edr.plsql.EDR_EVENT_RELATIONSHIP_PVT.VALIDATE_PARENT',
516 FALSE
517 );
518 end if;
519 --Diagnostics End
520
521 APP_EXCEPTION.RAISE_EXCEPTION;
522
523 WHEN INVALID_PARENT THEN
524 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_PARENT_CHILD_INVALID');
525 fnd_message.set_token('EVENT_NAME',P_PARENT_EVENT_NAME);
526 fnd_message.set_token('EVENT_KEY',P_PARENT_EVENT_KEY);
527 --Diagnostics Start
528 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
529 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
530 'edr.plsql.EDR_EVENT_RELATIONSHIP_PVT.VALIDATE_PARENT',
531 FALSE
532 );
533 end if;
534 --Diagnostics End
535
536 APP_EXCEPTION.RAISE_EXCEPTION;
537
538
539 END VALIDATE_PARENT;
540
541 --This API is defined to verify if the specified child e-records
542 --exist in the evidence store and represent transactions
543 --other than that of the parent transaction.
544 PROCEDURE VALIDATE_CHILDREN(P_CHILD_ERECORD_IDS IN FND_TABLE_OF_VARCHAR2_255,
545 P_PARENT_EVENT_NAME IN VARCHAR2
546 )
547 IS
548
549 --Define a cursor to query the evidence store
550 --based on the e-record id. This cursor would return
551 --the event_name for the specified e-record id.
552 cursor l_psig_details_csr(p_erecord_id NUMBER) is
553 select event_name from edr_psig_documents
554 where document_id = p_erecord_id;
555
556
557 L_COUNT NUMBER;
558 L_CHILD_ERECORD_ID NUMBER;
559
560 --This variable would hold the values of those invalid e-record IDs
561 --whose event name is the same as that of their parent.
562 L_INVALID_CHILD_ERECORD_IDS VARCHAR2(32767);
563
564 --This variable would hold the values of those invalid e-record IDs
565 --which don't exist in evidence store.
566 L_WRONG_CHILD_ERECORD_IDS VARCHAR2(32767);
567 L_COUNTER1 NUMBER;
568 L_COUNTER2 NUMBER;
569 L_TEMP_EVENT_NAME VARCHAR2(80);
570
571 INVALID_CHILD_ERECORDS EXCEPTION;
572 PARENT_CHILD_SAME_ERROR EXCEPTION;
573
574 INTER_EVENT_ERROR EXCEPTION;
575
576 BEGIN
577
578 L_WRONG_CHILD_ERECORD_IDS := '';
579 L_INVALID_CHILD_ERECORD_IDS := '';
580 L_COUNTER1 := 0;
581 L_COUNTER2 := 0;
582 L_COUNT := P_CHILD_ERECORD_IDS.count;
583 for i in 1..l_count loop
584 --Convert the VARCHAR2 e-record id value into number format.
585 L_CHILD_ERECORD_ID:=to_number(P_CHILD_ERECORD_IDS(i),'999999999999.999999');
586
587 --Set secure attribute
588 edr_ctx_pkg.set_secure_attr;
589 --Query the evidence using the cursor.
590 OPEN l_psig_details_csr(L_CHILD_ERECORD_ID);
591 FETCH L_PSIG_DETAILS_CSR into L_TEMP_EVENT_NAME;
592
593 --If no data was found, then update the error message.
594 IF L_PSIG_DETAILS_CSR%NOTFOUND THEN
595 l_counter1 := l_counter1 + 1;
596 L_WRONG_CHILD_ERECORD_IDS := L_WRONG_CHILD_ERECORD_IDS ||
597 ' '|| P_CHILD_ERECORD_IDS(i);
598 ELSE
599 --Otherwise check if the parent event name is the same as
600 --the child event name.
601 if L_TEMP_EVENT_NAME = P_PARENT_EVENT_NAME then
602 --If they are the same, keep track of the invalid e-record IDs
603 --and increment the counter.
604 l_invalid_child_erecord_ids := l_invalid_child_erecord_ids ||
605 ' ' || P_CHILD_ERECORD_IDS(i);
606
607 l_counter2 := l_counter2 + 1;
608
609 END IF;
610 END IF;
611
612 CLOSE L_PSIG_DETAILS_CSR;
613 end loop;
614
615 --unset secure attribute
616 edr_ctx_pkg.unset_secure_attr;
617
618 if l_counter1 > 0 and l_counter2 > 0 then
619 --Some of the specified child e-record IDs do not exist in evidence store
620 --and also there exists some e-record IDS whose event name is the same as
621 --that of their parent.
622 RAISE INTER_EVENT_ERROR;
623
624
625 elsif l_counter1 > 0 then
626 --Some of the specified child e-record IDs do not exist in the evidence
627 --store.
628 RAISE INVALID_CHILD_ERECORDS;
629
630 elsif l_counter2 > 0 then
631 --Some of the specified child e-record IDs have the same event name as that
632 --of their parent.
633 RAISE PARENT_CHILD_SAME_ERROR;
634
635 end if;
636
637 EXCEPTION
638 WHEN INVALID_CHILD_ERECORDS THEN
639 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_CHILD_ERROR');
640 fnd_message.set_token('EVENT_NAME',P_PARENT_EVENT_NAME);
641 fnd_message.set_token('CHILD_ERECORD_IDS',L_WRONG_CHILD_ERECORD_IDS);
642 --Diagnostics Start
643 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
644 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
645 'edr.plsql.EDR_EVENT_RELATIONSHIP_PVT.VALIDATE_CHILDREN',
646 FALSE
647 );
648 end if;
649 --Diagnostics End
650
651 APP_EXCEPTION.RAISE_EXCEPTION;
652
653 WHEN PARENT_CHILD_SAME_ERROR THEN
654 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_CHILD_PARENT_SAME_ERR');
655 fnd_message.set_token('EVENT_NAME',P_PARENT_EVENT_NAME);
656 fnd_message.set_token('CHILD_ERECORD_IDS',L_INVALID_CHILD_ERECORD_IDS);
657 --Diagnostics Start
658 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
659 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
660 'edr.plsql.EDR_EVENT_RELATIONSHIP_PVT.VALIDATE_CHILDREN',
661 FALSE
662 );
663 end if;
664 --Diagnostics End
665
666 APP_EXCEPTION.RAISE_EXCEPTION;
667
668
669 WHEN INTER_EVENT_ERROR THEN
670 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_INTER_EVENT_ERR');
671 fnd_message.set_token('EVENT_NAME',P_PARENT_EVENT_NAME);
672 fnd_message.set_token('WRONG_CHILD_ERECORD_IDS',L_WRONG_CHILD_ERECORD_IDS);
673 fnd_message.set_token('INVALID_CHILD_ERECORD_IDS',L_INVALID_CHILD_ERECORD_IDS);
674 --Diagnostics Start
675 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
676 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
677 'edr.plsql.EDR_EVENT_RELATIONSHIP_PVT.VALIDATE_CHILDREN',
678 FALSE
679 );
680 end if;
681 --Diagnostics End
682
683 APP_EXCEPTION.RAISE_EXCEPTION;
684
685 END VALIDATE_CHILDREN;
686 -- Bug 3667036: End
687
688 --Bug 4122622: Start
689 --This is just a wrapper overload on the existing validate_children.
690 PROCEDURE VALIDATE_CHILDREN(P_CHILD_ERECORD_IDS IN EDR_ERES_EVENT_PUB.ERECORD_ID_TBL_TYPE,
691 P_PARENT_EVENT_NAME IN VARCHAR2)
692 IS
693
694 --This variable would hold the array of child e-record ids in varchar2 format.
695 l_child_erecord_ids FND_TABLE_OF_VARCHAR2_255;
696 i pls_integer;
697 l_counter pls_integer;
698
699
700 BEGIN
701
702 l_counter := 1;
703
704 --Create a new object instance of child e-record IDs.
705 l_child_erecord_ids := FND_TABLE_OF_VARCHAR2_255();
706
707 --Copy the contents of e-record ID table type into the object type variable.
708 i := p_child_erecord_ids.FIRST;
709 while i is not null loop
710 l_child_erecord_ids.extend;
711 l_child_erecord_ids(l_counter) := to_char(p_child_erecord_ids(i));
712 l_counter := l_counter + 1;
713 i := p_child_erecord_ids.NEXT(i);
714 END LOOP;
715
716 --Validate the e-record IDs.
717 VALIDATE_CHILDREN(P_CHILD_ERECORD_IDS => L_CHILD_ERECORD_IDS,
718 P_PARENT_EVENT_NAME => P_PARENT_EVENT_NAME);
719 END VALIDATE_CHILDREN;
720 --Bug 4122622: End
721
722 end EDR_EVENT_RELATIONSHIP_PVT;
[Home] [Help]