1 PACKAGE BODY EDR_ERES_EVENT_PVT AS
2 /* $Header: EDRVEVTB.pls 120.2.12000000.1 2007/01/18 05:56:10 appldev ship $*/
3
4 -- Private Utility Functions --
5
6 /** Gets the guid of the ERES subscription for a business event **/
7
8 /** following get subscription GUI function is totaly rewritten to resolve the bug
9 3355468
10 **/
11 FUNCTION GET_SUBSCRIPTION_GUID
12 ( p_event_name IN VARCHAR2)
13 RETURN RAW
14 IS
15
16 l_guid RAW(16);
17 l_no_enabled_eres_sub NUMBER;
18 l_no_of_eres_sub NUMBER;
19
20 cursor enabled_subscription_csr is
21 select b.guid
22 from wf_events_vl a, wf_event_subscriptions b
23 where a.guid=b.EVENT_FILTER_GUID
24 and a.name = p_event_name
25 and UPPER(b.rule_function) = EDR_CONSTANTS_GRP.g_rule_function
26 and b.status = 'ENABLED'
27 --Bug No 4912782- Start
28 and b.source_type = 'LOCAL'
29 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
30 --Bug No 4912782- End
31
32 cursor single_subscription_csr is
33 select b.guid
34 from wf_events_vl a, wf_event_subscriptions b
35 where a.guid=b.EVENT_FILTER_GUID
36 and a.name = p_event_name
37 and UPPER(b.rule_function) = EDR_CONSTANTS_GRP.g_rule_function
38 --Bug No 4912782- Start
39 and b.source_type = 'LOCAL'
40 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
41 --Bug No 4912782- End
42
43 BEGIN
44 /*
45 ** added following comments as part of bug fix 3355468
46 ** This function returns valid Subscription GUID for following cases
47 ** 1. only one ERES subscription present
48 ** 2. Only one ERES subscription is enabled when multiple
49 ** ERES subscriptions are present for the event
50 **
51 ** in all other cases it will return "Null" */
52
53 --
54 -- find out how many ERES subscriptions are
55 -- present for the event
56 --
57
58 select count(*) INTO l_no_of_eres_sub
59 from
60 wf_events a, wf_event_subscriptions b
61 where a.GUID = b.EVENT_FILTER_GUID
62 and a.name = p_event_name
63 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
64 --Bug No 4912782- Start
65 and b.source_type = 'LOCAL'
66 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
67 --Bug No 4912782- End
68
69 IF l_no_of_eres_sub > 1 then
70
71 --
72 --
73 -- Verify is more than one active ERES subscriptions are present
74 -- for the event. then return subscription guid as null.
75 -- return null when no subscription is enabled
76 -- return valid Subscription GUID when only one
77 -- subscription is enabled
78 --
79 --
80 select count(*) INTO l_no_enabled_eres_sub
81 from
82 wf_events a, wf_event_subscriptions b
83 where a.GUID = b.EVENT_FILTER_GUID
84 and a.name = p_event_name
85 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
86 and b.STATUS = 'ENABLED'
87 --Bug No 4912782- Start
88 and b.source_type = 'LOCAL'
89 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
90 --Bug No 4912782- End
91 IF l_no_enabled_eres_sub > 1 THEN
92 l_guid := null;
93 ELSIF l_no_enabled_eres_sub = 0 THEN
94 l_guid := null;
95 ELSIF l_no_enabled_eres_sub = 1 THEN
96 open enabled_subscription_csr;
97 fetch enabled_subscription_csr into l_guid;
98 close enabled_subscription_csr ;
99 END IF;
100 ELSIF l_no_of_eres_sub = 0 THEN
101 l_guid := null;
102 ELSIF l_no_of_eres_sub = 1 THEN
103 --
104 -- if only one ERES subscription is present
105 -- then ignore status and return valid subscription GUID
106 --
107 open single_subscription_csr;
108 fetch single_subscription_csr into l_guid;
109 close single_subscription_csr ;
110 END IF;
111
112 return l_guid;
113
114 EXCEPTION WHEN NO_DATA_FOUND then
115 return(null);
116
117 END GET_SUBSCRIPTION_GUID;
118
119 -- Private APIs --
120
121 PROCEDURE RAISE_EVENT
122 ( p_api_version IN NUMBER,
123 p_init_msg_list IN VARCHAR2,
124 p_validation_level IN NUMBER,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2,
128 p_mode IN VARCHAR2,
129 x_event IN OUT NOCOPY EDR_ERES_EVENT_PUB.ERES_EVENT_REC_TYPE,
130 x_is_child_event OUT NOCOPY BOOLEAN,
131 --Bug 4122622: Start
132 p_parameter_list IN FND_WF_EVENT.PARAM_TABLE
133 --Bug 4122622: End
134 )
135 AS
136 l_api_name CONSTANT VARCHAR2(30) := 'RAISE_EVENT';
137 l_api_version CONSTANT NUMBER := 1.0;
138
139 l_parameter_list fnd_wf_event.param_table;
140 l_param_name varchar2(30);
141 l_param_value varchar2(2000);
142 l_param_number number;
143 i pls_integer;
144
145 l_return_status VARCHAR2(1);
146 l_msg_count NUMBER;
147 l_msg_data VARCHAR2(2000);
148 l_mesg_text VARCHAR2(2000);
149
150
151 PAYLOAD_VALIDATION_ERROR EXCEPTION;
152 EVENT_RAISE_ERROR EXCEPTION;
153
154 --Bug 4122622: Start
155 PARENT_ERECORD_ID_ERROR EXCEPTION;
156 l_parent_erecord_id VARCHAR2(128);
157 --Bug 4122622: End
158
159 BEGIN
160 -- Standard call to check for call compatibility.
161 IF NOT FND_API.Compatible_API_Call (l_api_version ,
162 p_api_version ,
163 l_api_name ,
164 G_PKG_NAME )
165 THEN
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167 END IF;
168
169 -- Initialize message list if p_init_msg_list is set to TRUE.
170 IF FND_API.to_Boolean( p_init_msg_list ) THEN
171 FND_MSG_PUB.initialize;
172 END IF;
173
174 -- Initialize API return status to success
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176
177 -- API Body
178
179 -- CHANGE_SOURCE_TYPE(x_event.payload, EDR_CONSTANTS_GRP.g_db_mode);
180 --Bug 3136403: Start
181 --Copy the individual parameters to a structure
182 --of type fnd_wf_event.param_table
183 --start with 4 because in event of the payload with valid
184 --the first three parameters are set to specific values below
185 -- SKARIMIS Moved the code logic outside of follwing IF statment. Payload should
186 -- be populated without checking validation
187
188 --Bug 4122622: Start
189 if(P_PARAMETER_LIST.COUNT = 0) then
190 CREATE_PAYLOAD
191 ( p_event => x_event ,
192 p_starting_position => 4 ,
193 x_payload => l_parameter_list
194 );
195 else
196 l_parameter_list := p_parameter_list;
197 end if;
198 --Bug 4122622: End
199
200 --validate that the payload passed is valid
201 IF p_validation_level > FND_API.G_VALID_LEVEL_NONE THEN
202
203 EDR_ERES_EVENT_PUB.VALIDATE_PAYLOAD
204 ( p_api_version => 1.0 ,
205 p_init_msg_list => FND_API.G_FALSE ,
206 x_return_status => l_return_status ,
207 x_msg_count => l_msg_count ,
208 x_msg_data => l_msg_data ,
209 p_event_name => x_event.event_name ,
210 p_event_key => x_event.event_key ,
211 p_payload => l_parameter_list ,
212 p_mode => p_mode
213 );
214
215
216 --Bug 3136403: End
217
218 -- If any errors happen abort API.
219 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
222 RAISE PAYLOAD_VALIDATION_ERROR;
223 END IF;
224
225 END IF;
226 --Bug 3136403: Start
227 -- SKARIMIS Introduced a check of child record
228 i := 4;
229 while i is not null loop
230 --Bug 4122622: Start
231 --We need to obtain the parent e-record id.
232 if (l_parameter_list(i).PARAM_NAME = EDR_CONSTANTS_GRP.g_parent_erecord_id) then
233 l_parent_erecord_id := l_parameter_list(i).param_value;
234 x_is_child_event := TRUE;
235 exit;
236 end if;
237 --Bug 4122622: End
238 i := l_parameter_list.NEXT(i);
239 end loop;
240
241 --Bug 4122622: Start
242 --Validate the parent erecord if they are set and only if they were'nt validated
243 --earlier.
244 --They would'nt be validated if the Validation level was set to NONE.
245 if x_is_child_event and l_parent_erecord_id is not null and
246 l_parent_erecord_id <> '-1' and p_validation_level = FND_API.G_VALID_LEVEL_NONE
247 then
248 EDR_ERES_EVENT_PUB.VALIDATE_ERECORD
249 ( p_api_version => 1.0,
250 x_return_status => l_return_status,
251 x_msg_count => l_msg_count,
252 x_msg_data => l_msg_data,
253 p_erecord_id => to_number(l_parent_erecord_id,'999999999999.999999')
254 );
255 -- If any errors happen abort API.
256 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
257 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
258 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
259 RAISE PARENT_ERECORD_ID_ERROR;
260 END IF;
261 END IF;
262 --Bug 4122622: End;
263
264 --Bug 3136403: End
265 --if valid then extend the payload to have additional parameters
266 --this is done by copying the payload to another table of same type
267 --this would help in putting additional parameters at specific locations
268 --and also do some additional payload inspection to figure out if
269 --this is inter event mode
270
271 l_parameter_list(1).param_name := EDR_CONSTANTS_GRP.g_wf_pageflow_itemtype_attr;
272 l_parameter_list(1).param_value := null;
273 l_parameter_list(2).param_name := EDR_CONSTANTS_GRP.g_wf_pageflow_itemkey_attr;
274 l_parameter_list(2).param_value := null;
275
276 --the third parameter would be the #ERECORD_ID that would contain the erecord id
277 l_parameter_list(3).param_name := EDR_CONSTANTS_GRP.g_erecord_id_attr;
278 l_parameter_list(3).param_value := null;
279
280 --raise the event
281 begin
282 --Bug 3136403: Start
283 --Get the value of the number of parameters in the parameter list
284 --and pass it
285 l_param_number := l_parameter_list.COUNT;
286
287 --Bug 3207385: Start
288 RAISE_TABLE
289 ( x_event.event_name,
290 x_event.event_key,
291 --Bug 3893101: Start
292 --Pass the event xml payload while raising the event.
293 x_event.event_xml,
294 --Bug 3893101: End
295 l_parameter_list,
296 l_param_number,
297 NULL
298 );
299 --Bug 3207385: End
300 --Bug 3136403: End
301
302 exception WHEN OTHERS then
303 l_parameter_list(1).param_value := 'WF_ERROR';
304 l_parameter_list(2).param_value := '-999';
305
306 --this would get the messages on the error stack set by
307 --the rule function and add to the api error stack
308
309 l_mesg_text := fnd_message.get();
310
311 FND_MSG_PUB.Add_Exc_Msg
312 ( G_PKG_NAME ,
313 l_api_name ,
314 l_mesg_text
315 );
316 end;
317
318 IF l_parameter_list(1).param_value = 'WF_ERROR'
319 AND l_parameter_list(2).param_value = '-999'
320 THEN
321 RAISE EVENT_RAISE_ERROR;
322
323 ELSIF l_parameter_list(1).param_value is NULL
324 AND l_parameter_list(2).param_value is NULL
325 THEN
326 -- this means that no signature was required
327 -- No WF, mark as success
328 x_event.event_status := EDR_CONSTANTS_GRP.g_no_action_status;
329
330 -- an eRecord may or may not have been required anyhow
331 -- get the erecord id
332 x_event.erecord_id := l_parameter_list(3).param_value;
333
334 ELSE
335 -- this means that signature was required and offline notification
336 -- has been sent out
337 x_event.event_status := EDR_CONSTANTS_GRP.g_pending_status;
338
339 --get the erecord id
340 x_event.erecord_id := l_parameter_list(3).param_value;
341 END IF;
342
343 -- Standard call to get message count and if count is 1,
344 --get message info.
345 FND_MSG_PUB.Count_And_Get
346 ( p_count => x_msg_count ,
347 p_data => x_msg_data
348 );
349
350 EXCEPTION
351 WHEN PAYLOAD_VALIDATION_ERROR THEN
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 x_event.event_status := EDR_CONSTANTS_GRP.g_error_status;
354
355 -- this would pass on the validation errors to the calling
356 -- routine
357
358 FND_MSG_PUB.Count_And_Get
359 ( p_count => x_msg_count ,
360 p_data => x_msg_data
361 );
362
363 WHEN EVENT_RAISE_ERROR THEN
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 x_event.event_status := EDR_CONSTANTS_GRP.g_error_status;
366 x_event.erecord_id := null;
367
368 l_mesg_text := fnd_message.get_string('EDR','EDR_EVENT_RAISE_ERROR');
369
370 FND_MSG_PUB.Add_Exc_Msg
371 ( G_PKG_NAME ,
372 l_api_name ,
373 l_mesg_text
374 );
375
376 FND_MSG_PUB.Count_And_Get
377 ( p_count => x_msg_count ,
378 p_data => x_msg_data
379 );
380
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
383 x_event.event_status := EDR_CONSTANTS_GRP.g_error_status;
384 x_event.erecord_id := null;
385
386 FND_MSG_PUB.Count_And_Get
387 ( p_count => x_msg_count ,
388 p_data => x_msg_data
389 );
390
391
392 --Bug 4122622: Start
393 --This exception would be thrown when the parent e-record ID is invalid.
394 WHEN PARENT_ERECORD_ID_ERROR THEN
395 x_return_status := FND_API.G_RET_STS_ERROR ;
396
397 fnd_message.set_name('EDR','EDR_VAL_INVALID_PARENT_ID');
398 fnd_message.set_token('ERECORD_ID', l_parent_erecord_id);
399 fnd_message.set_token('EVENT_NAME', x_event.event_name);
400 fnd_message.set_token('EVENT_KEY', x_event.event_key);
401 l_mesg_text := fnd_message.get();
402 FND_MSG_PUB.Add_Exc_Msg
403 (G_PKG_NAME,
404 l_api_name,
405 l_mesg_text
406 );
407 FND_MSG_PUB.Count_And_Get
408 (p_count => x_msg_count,
409 p_data => x_msg_data
410 );
411 --Bug 4122622: End
412
413 WHEN OTHERS THEN
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
415 x_event.event_status := EDR_CONSTANTS_GRP.g_error_status;
416 x_event.erecord_id := null;
417
418 IF FND_MSG_PUB.Check_Msg_Level
419 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
420 THEN
421 FND_MSG_PUB.Add_Exc_Msg
422 ( G_PKG_NAME ,
423 l_api_name
424 );
425 END IF;
426
427 FND_MSG_PUB.Count_And_Get
428 ( p_count => x_msg_count ,
429 p_data => x_msg_data
430 );
431
432 END RAISE_EVENT;
433
434 PROCEDURE CREATE_PAYLOAD
435 ( p_event IN EDR_ERES_EVENT_PUB.ERES_EVENT_REC_TYPE ,
436 p_starting_position IN NUMBER ,
437 x_payload OUT NOCOPY FND_WF_EVENT.PARAM_TABLE
438 )
439 IS
440 l_position number;
441 BEGIN
442
443 --Bug 4074173 : GSCC Warning
444 l_position := p_starting_position;
445
446 /* SKARIMIS. Cahged the way payload is populated */
447 IF p_event.param_name_1 is NOT NULL THEN
448 x_payload(l_position).param_name := p_event.param_name_1;
449 x_payload(l_position).param_value := p_event.param_value_1;
450 l_position:=l_position+1;
451 END IF;
452 IF p_event.param_name_2 is NOT NULL THEN
453 x_payload(l_position).param_name := p_event.param_name_2;
454 x_payload(l_position).param_value := p_event.param_value_2;
455 l_position:=l_position+1;
456 END IF;
457 IF p_event.param_name_3 is NOT NULL THEN
458 x_payload(l_position).param_name := p_event.param_name_3;
459 x_payload(l_position).param_value := p_event.param_value_3;
460 l_position:=l_position+1;
461 END IF;
462 IF p_event.param_name_4 is NOT NULL THEN
463 x_payload(l_position).param_name := p_event.param_name_4;
464 x_payload(l_position).param_value := p_event.param_value_4;
465 l_position:=l_position+1;
466 END IF;
467
468 IF p_event.param_name_5 is NOT NULL THEN
469 x_payload(l_position).param_name := p_event.param_name_5;
470 x_payload(l_position).param_value := p_event.param_value_5;
471 l_position:=l_position+1;
472 END IF;
473 IF p_event.param_name_6 is NOT NULL THEN
474 x_payload(l_position).param_name := p_event.param_name_6;
475 x_payload(l_position).param_value := p_event.param_value_6;
476 l_position:=l_position+1;
477 END IF;
478
479 IF p_event.param_name_7 is NOT NULL THEN
480 x_payload(l_position).param_name := p_event.param_name_7;
481 x_payload(l_position).param_value := p_event.param_value_7;
482 l_position:=l_position+1;
483 END IF;
484 IF p_event.param_name_8 is NOT NULL THEN
485 x_payload(l_position).param_name := p_event.param_name_8;
486 x_payload(l_position).param_value := p_event.param_value_8;
487 l_position:=l_position+1;
488 END IF;
489
490 IF p_event.param_name_9 is NOT NULL THEN
491 /* SKARIMIS . There was a bug here name is beign populated for both name and value */
492 x_payload(l_position).param_name := p_event.param_name_9;
493 x_payload(l_position).param_value := p_event.param_value_9;
494 l_position:=l_position+1;
495 END IF;
496 IF p_event.param_name_10 is NOT NULL THEN
497 x_payload(l_position).param_name := p_event.param_name_10;
498 x_payload(l_position).param_value := p_event.param_value_10;
499 l_position:=l_position+1;
500 END IF;
501
502 IF p_event.param_name_11 is NOT NULL THEN
503 x_payload(l_position).param_name := p_event.param_name_11;
504 x_payload(l_position).param_value := p_event.param_value_11;
505 l_position:=l_position+1;
506 END IF;
507 IF p_event.param_name_12 is NOT NULL THEN
508 x_payload(l_position).param_name := p_event.param_name_12;
509 x_payload(l_position).param_value := p_event.param_value_12;
510 l_position:=l_position+1;
511 END IF;
512 IF p_event.param_name_13 is NOT NULL THEN
513 x_payload(l_position).param_name := p_event.param_name_13;
514 x_payload(l_position).param_value := p_event.param_value_13;
515 l_position:=l_position+1;
516 END IF;
517 IF p_event.param_name_14 is NOT NULL THEN
518 x_payload(l_position).param_name := p_event.param_name_14;
519 x_payload(l_position).param_value := p_event.param_value_14;
520 l_position:=l_position+1;
521 END IF;
522
523 IF p_event.param_name_15 is NOT NULL THEN
524 x_payload(l_position).param_name := p_event.param_name_15;
525 x_payload(l_position).param_value := p_event.param_value_15;
526 l_position:=l_position+1;
527 END IF;
528 IF p_event.param_name_16 is NOT NULL THEN
529 x_payload(l_position).param_name := p_event.param_name_16;
530 x_payload(l_position).param_value := p_event.param_value_16;
531 l_position:=l_position+1;
532 END IF;
533
534 IF p_event.param_name_17 is NOT NULL THEN
535 x_payload(l_position).param_name := p_event.param_name_17;
536 x_payload(l_position).param_value := p_event.param_value_17;
537 l_position:=l_position+1;
538 END IF;
539 IF p_event.param_name_18 is NOT NULL THEN
540 x_payload(l_position).param_name := p_event.param_name_18;
541 x_payload(l_position).param_value := p_event.param_value_18;
542 l_position:=l_position+1;
543 END IF;
544
545 IF p_event.param_name_19 is NOT NULL THEN
546 x_payload(l_position).param_name := p_event.param_name_19;
547 x_payload(l_position).param_value := p_event.param_value_19;
548 l_position:=l_position+1;
549 END IF;
550 IF p_event.param_name_20 is NOT NULL THEN
551 x_payload(l_position).param_name := p_event.param_name_20;
552 x_payload(l_position).param_value := p_event.param_value_20;
553 l_position:=l_position+1;
554 END IF;
555
556 END CREATE_PAYLOAD;
557
558 PROCEDURE GET_EVENT_APPROVERS
559 ( p_api_version IN NUMBER ,
560 p_init_msg_list IN VARCHAR2 ,
561 x_return_status OUT NOCOPY VARCHAR2 ,
562 x_msg_count OUT NOCOPY NUMBER ,
563 x_msg_data OUT NOCOPY VARCHAR2 ,
564 p_event_name IN VARCHAR2 ,
565 p_event_key IN VARCHAR2 ,
566 x_approver_count OUT NOCOPY NUMBER ,
567 x_approvers_name OUT NOCOPY FND_TABLE_OF_VARCHAR2_255 ,
568 x_approvers_role_name OUT NOCOPY FND_TABLE_OF_VARCHAR2_255 ,
569 x_overriding_details OUT NOCOPY FND_TABLE_OF_VARCHAR2_255 ,
570 x_approvers_sequence OUT NOCOPY FND_TABLE_OF_VARCHAR2_255
571 )
572 AS
573 l_api_name CONSTANT VARCHAR2(30) := 'GET_EVENT_APPROVERS';
574 l_api_version CONSTANT NUMBER := 1.0;
575
576 --Bug 2674799 : start
577 l_approver_list EDR_UTILITIES.approvers_Table;
578
579 -- ame approver api call variables
580 l_ruleids edr_utilities.id_List;
581 l_rulenames edr_utilities.string_List;
582
583 --Bug 2674799 : end
584
585 l_fnd_user varchar2(100);
586 l_application_id NUMBER;
587 i NUMBER := 1;
588 l_ame_txn_type VARCHAR2(1000);
589 l_new_user VARCHAR2(100);
590 l_comments VARCHAR2(1000);
591 l_sub_count NUMBER;
592 l_user_id NUMBER;
593 l_cur_user_id NUMBER;
594 l_err_code varchar2(100);
595 l_err_mesg varchar2(1000);
596 l_guid raw(16);
597 INVALID_EVENT_NAME_ERROR EXCEPTION;
598 INVALID_USER_NAME_ERROR EXCEPTION;
599 MULTIPLE_ERES_SUBSCRIPTIONS EXCEPTION;
600 CURSOR CUR_EVENT is
601 SELECT application_id
602 FROM FND_APPLICATION A, WF_EVENTS B
603 WHERE A.APPLICATION_SHORT_NAME = B.OWNER_TAG
604 AND B.NAME=P_EVENT_NAME;
605
606 CURSOR CUR_SUB is
607 select count(*)
608 from
609 wf_events a, wf_event_subscriptions b
610 where a.GUID = b.EVENT_FILTER_GUID
611 and a.name = p_event_name
612 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
613 and b.STATUS = 'ENABLED'
614 --Bug No 4912782- Start
615 and b.source_type = 'LOCAL'
616 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
617 --Bug No 4912782- End
618
619 CURSOR CUR_USER_NAME(l_cur_user_id number) is
620 select user_name
621 from FND_USER
622 where USER_ID = l_cur_user_id;
623
624 BEGIN
625 --by default return 0 as the approver count
626 x_approver_count := 0;
627
628 -- Standard call to check for call compatibility.
629 IF NOT FND_API.Compatible_API_Call (l_api_version ,
630 p_api_version ,
631 l_api_name ,
632 G_PKG_NAME )
633 THEN
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635 END IF;
636
637 -- Initialize message list if p_init_msg_list is set to TRUE.
638 IF FND_API.to_Boolean( p_init_msg_list ) THEN
639 FND_MSG_PUB.initialize;
640 END IF;
641
642 -- Initialize API return status to success
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644
645
646 -- API Body
647 --validate the event name and get the application id
648 OPEN CUR_EVENT;
649 FETCH CUR_EVENT into l_application_id;
650 IF CUR_EVENT%NOTFOUND THEN
651 CLOSE CUR_EVENT;
652 RAISE INVALID_EVENT_NAME_ERROR;
653 END IF;
654 CLOSE CUR_EVENT;
655 -- Validate Subscription
656 OPEN CUR_SUB;
657 FETCH CUR_SUB into l_SUB_COUNT;
658 IF l_SUB_COUNT > 1 THEN
659 CLOSE CUR_SUB;
660 RAISE MULTIPLE_ERES_SUBSCRIPTIONS;
661 END IF;
662 CLOSE CUR_SUB;
663 --get the ame transaction type of the event
664 BEGIN
665
666 --Bug 2674799: start
667 --Fixing this as a part of AME patch.
668 --If there are > 1 subscriptions, and one is enabled query returns >1
669 -- rows. Hence adding two more conditions in where clause
670
671 SELECT EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_AME_TRANSACTION_TYPE',b.guid) into l_ame_txn_type
672 from wf_events_vl a,
673 wf_event_subscriptions b
674 WHERE a.guid=b.EVENT_FILTER_GUID
675 and a.name = p_event_name
676 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
677 and b.STATUS = 'ENABLED'
678 --Bug No 4912782- Start
679 and b.source_type = 'LOCAL'
680 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
681 --Bug No 4912782- End
682 --Bug 2674799: end
683
684 EXCEPTION
685 when OTHERS THEN
686 raise FND_API.G_EXC_UNEXPECTED_ERROR;
687 END;
688
689 l_ame_txn_type := nvl(l_ame_txn_type, p_event_name);
690
691
692 --Bug 2674799: start
693
694 EDR_UTILITIES.GET_APPROVERS
695 (p_APPLICATION_ID => l_application_Id,
696 p_TRANSACTION_ID => p_event_key,
697 p_TRANSACTION_TYPE => l_ame_txn_type,
698 X_APPROVERS => l_approver_List,
699 X_RULE_IDS => l_ruleids,
700 X_RULE_DESCRIPTIONS => l_rulenames
701 );
702
703 --Bug 2674799: end
704
705
706 --initialize the return tables
707 x_approvers_name := fnd_table_of_varchar2_255('');
708 x_approvers_role_name := fnd_table_of_varchar2_255('');
709 x_overriding_details := fnd_table_of_varchar2_255('');
710 x_approvers_sequence := fnd_table_of_varchar2_255('');
711
712 --for each user id returned by ame get the user_name from fnd schema
713 --and the role name from the wf directory services
714 while (i <= l_approver_list.count) loop
715
716 if (i > 1) then
717
718 x_approvers_name.extend;
719 x_approvers_role_name.extend;
720 x_overriding_details.extend;
721 x_approvers_sequence.extend;
722
723 end if;
724
725 --Bug 2674799 : start
726 l_fnd_user := l_approver_list(i).name;
727 --Bug 2674799 : end
728
729 --find out if any overriding approver is defined in the workflow
730 --system for this user currently
731 edr_standard.FIND_WF_NTF_RECIPIENT
732 (P_ORIGINAL_RECIPIENT => l_fnd_user,
733 P_MESSAGE_TYPE => null,
734 P_MESSAGE_NAME => null,
735 P_RECIPIENT => l_new_user,
736 P_NTF_ROUTING_COMMENTS => l_comments,
737 P_ERR_CODE => l_err_code,
738 P_ERR_MSG => l_err_mesg
739 );
740
741 if (l_err_code = '0') then
742 x_approvers_name(i) := l_new_user;
743 x_approvers_role_name(i) := wf_directory.getroledisplayname(l_new_user);
744 x_overriding_details(i) := l_comments;
745 else
746 x_approvers_name(i) := l_fnd_user;
747 x_approvers_role_name(i) := wf_directory.getroledisplayname(l_fnd_user);
748 x_overriding_details(i) := null;
749 end if;
750
751 x_approvers_sequence(i) := l_approver_list(i).approver_order_number;
752
753 i := i+1;
754 end loop;
755
756 x_approver_count := i-1;
757
758 -- Standard call to get message count and if count is 1,
759 --get message info.
760 FND_MSG_PUB.Count_And_Get
761 ( p_count => x_msg_count ,
762 p_data => x_msg_data
763 );
764
765 EXCEPTION
766 WHEN INVALID_EVENT_NAME_ERROR THEN
767 x_return_status := FND_API.G_RET_STS_ERROR;
768
769 -- this would pass on the validation errors to the calling
770 -- routine
771 FND_MSG_PUB.Count_And_Get
772 ( p_count => x_msg_count ,
773 p_data => x_msg_data
774 );
775 WHEN INVALID_USER_NAME_ERROR THEN
776 x_return_status := FND_API.G_RET_STS_ERROR;
777
778 -- this would pass on the validation errors to the calling
779 -- routine
780 FND_MSG_PUB.Count_And_Get
781 ( p_count => x_msg_count ,
782 p_data => x_msg_data
783 );
784 WHEN MULTIPLE_ERES_SUBSCRIPTIONS THEN
785 x_return_status := FND_API.G_RET_STS_ERROR;
786 FND_MESSAGE.SET_NAME('EDR','EDR_MULTI_ERES_SUBSCRP_ERR');
787 fnd_message.set_token( 'EVENT', p_event_NAME);
788 fnd_msg_pub.Add;
789 -- this would pass on the validation errors to the calling
790 -- routine
791 FND_MSG_PUB.Count_And_Get
792 ( p_count => x_msg_count ,
793 p_data => x_msg_data
794 );
795
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
797 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
798
799 FND_MSG_PUB.Count_And_Get
800 ( p_count => x_msg_count ,
801 p_data => x_msg_data
802 );
803
804 WHEN OTHERS THEN
805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
806
807 IF FND_MSG_PUB.Check_Msg_Level
808 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
809 THEN
810 FND_MSG_PUB.Add_Exc_Msg
811 ( G_PKG_NAME ,
812 l_api_name
813 );
814 END IF;
815
816 FND_MSG_PUB.Count_And_Get
817 ( p_count => x_msg_count ,
818 p_data => x_msg_data
819 );
820 END GET_EVENT_APPROVERS;
821
822
823 --Bug 3667036: Start
824 PROCEDURE CREATE_MANAGER_PROCESS(P_RETURN_URL IN VARCHAR2,
825 P_RETURN_FUNCTION IN VARCHAR2,
826 P_OVERALL_STATUS IN VARCHAR2,
827 P_CREATION_DATE IN DATE,
828 P_CREATED_BY IN NUMBER,
829 P_LAST_UPDATE_DATE IN DATE,
830 P_LAST_UPDATED_BY IN NUMBER,
831 P_LAST_UPDATE_LOGIN IN NUMBER,
832 X_ERES_PROCESS_ID OUT NOCOPY NUMBER)
833 IS
834 BEGIN
835 --get the next pk value from sequence
836 select EDR_ERESMANAGER_T_S.nextval into X_ERES_PROCESS_ID from dual;
837
838 --insert all the values in db
839 insert into EDR_ERESMANAGER_T(ERES_PROCESS_ID,
840 RETURN_URL,
841 RETURN_FUNCTION,
842 OVERALL_STATUS,
843 CREATED_BY,
844 CREATION_DATE,
845 LAST_UPDATE_DATE,
846 LAST_UPDATED_BY,
847 LAST_UPDATE_LOGIN
848 )
849 values( X_ERES_PROCESS_ID,
850 P_RETURN_URL,
851 P_RETURN_FUNCTION,
852 P_OVERALL_STATUS,
853 P_CREATED_BY,
854 P_CREATION_DATE,
855 P_LAST_UPDATE_DATE,
856 P_LAST_UPDATED_BY,
857 P_LAST_UPDATE_LOGIN);
858
859 --no exception handling is done here because only an unexpected exception can occur
860 --here which is supposed to be handled in the calling code
861
862 END CREATE_MANAGER_PROCESS;
863
864
865 PROCEDURE DELETE_ERECORDS(P_ERES_PROCESS_ID IN NUMBER)
866 IS
867 L_TEMP_DATA_LIFE VARCHAR2(128);
868 L_TEMP_DATE DATE;
869 L_TEMP_DATA_LIFE_NUM NUMBER;
870 BEGIN
871
872 --If eres_process_id is not null then delete from edr_process_erecords_t and
873 --edr_eres_manager_t tables
874 if P_ERES_PROCESS_ID is not null then
875
876 --Bug 3893101: Start
877 --Delete from the EDR parameters table as well.
878
879 delete from EDR_ERESPARAMETERS_T params
880 where params.PARENT_ID in (select ERECORD_SEQUENCE_ID
881 from EDR_PROCESS_ERECORDS_T records
882 where records.ERES_PROCESS_ID = P_ERES_PROCESS_ID
883 ) and params.parent_type = 'ERECORD';
884
885 delete from EDR_ERESPARAMETERS_T where parent_id = p_eres_process_id
886 and parent_type = 'ERESMANAGER';
887 --Bug 3893101: End
888 delete from EDR_PROCESS_ERECORDS_T where ERES_PROCESS_ID = P_ERES_PROCESS_ID;
889 delete from EDR_ERESMANAGER_T where ERES_PROCESS_ID = P_ERES_PROCESS_ID;
890
891 else
892 --If eres_process_id is null then delete temp data based on the profile values
893 L_TEMP_DATE := sysdate;
894 L_TEMP_DATA_LIFE := FND_PROFILE.VALUE('EDR_TEMP_DATA_LIFE');
895
896 --Perform delete operation only if profile value is not null
897 if L_TEMP_DATA_LIFE is not null then
898
899 --Convert varchar2 to number. Ensure MLS compliancy
900 L_TEMP_DATA_LIFE_NUM := TO_NUMBER(L_TEMP_DATA_LIFE,'999999999999');
901
902 --verify -ve value.
903 if L_TEMP_DATA_LIFE_NUM <= 0 then
904 raise VALUE_ERROR;
905 end if;
906
907 L_TEMP_DATE := L_TEMP_DATE - L_TEMP_DATA_LIFE_NUM;
908
909 --Bug 3893101: Start
910 --Delete the EDR parameters table as well
911 delete from EDR_ERESPARAMETERS_T params
912 where params.PARENT_ID in (select records.ERECORD_SEQUENCE_ID
913 from EDR_PROCESS_ERECORDS_T records
914 where records.CREATION_DATE <= L_TEMP_DATE
915 ) and params.parent_type = 'ERECORD';
916
917 delete from EDR_ERESPARAMETERS_T params
918 where params.PARENT_ID in (select manager.ERES_PROCESS_ID
919 from EDR_ERESMANAGER_T manager
920 where manager.CREATION_DATE <= L_TEMP_DATE
921 ) and params.parent_type = 'ERESMANAGER';
922 --Bug 3893101: End
923
924 delete from EDR_ERESMANAGER_T where CREATION_DATE <= L_TEMP_DATE;
925 fnd_message.set_name('EDR', 'EDR_TEMP_ERESMANAGER_CLEANUP');
926 fnd_message.set_token( 'CLN_ERESMANAGER', SQL%ROWCOUNT);
927 fnd_file.put_line(fnd_file.output, fnd_message.get);
928
929 delete from EDR_PROCESS_ERECORDS_T where CREATION_DATE <= L_TEMP_DATE;
930 fnd_message.set_name('EDR', 'EDR_TEMP_ERECORDS_CLEANUP');
931 fnd_message.set_token( 'CLN_ERECORDS', SQL%ROWCOUNT);
932 fnd_file.put_line(fnd_file.output, fnd_message.get);
933
934 --Bug 3621309 : Start
935 delete from EDR_RAW_XML_T where CREATION_DATE <= L_TEMP_DATE;
936 fnd_message.set_name('EDR', 'EDR_VALIDATE_TEMP_DATA_CLEANUP');
937 fnd_message.set_token( 'CLN_ERECORDS', SQL%ROWCOUNT);
938 fnd_file.put_line(fnd_file.output, fnd_message.get);
939 --Bug 3621309 : End
940
941 end if;
942 end if;
943
944 exception
945
946 when VALUE_ERROR then
947 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_INVALID_PROFILE_VALUE'));
948
949 END DELETE_ERECORDS;
950
951 --Bug 3667036: End
952
953
954 --Bug 3207385: Start
955
956 --This method would raise the file approval completion event.
957 --We need to perform the a commit operation after raising the event.
958 --Hence this new API will perform an autonomous commit.
959
960 PROCEDURE RAISE_COMPLETION_EVENT(P_ORIG_EVENT_NAME IN VARCHAR2,
961 P_ORIG_EVENT_KEY IN VARCHAR2,
962 P_ORIG_PARAM_LIST IN FND_WF_EVENT.PARAM_TABLE,
963 P_SEND_DATE IN DATE)
964 IS
965
966 PRAGMA AUTONOMOUS_TRANSACTION;
967
968 i NUMBER;
969
970 l_wfitemtype_set boolean;
971 l_wfitemkey_set boolean;
972
973 l_temp_string VARCHAR2(4000);
974 l_erecord_id VARCHAR2(128);
975
976 l_event_name VARCHAR2(240);
977 l_event_key VARCHAR2(240);
978 l_param_list WF_PARAMETER_LIST_T;
979
980 BEGIN
981
982 l_erecord_id := NULL;
983
984 l_temp_string := NULL;
985
986 l_wfitemtype_set := false;
987
988 l_wfitemkey_set := false;
989
990 --Check if approval is required for the event.
991 --This is done by checking the values of wfitemtype,wfitemkey attributes set in
992 --the parameter list.
993 FOR i IN 1..p_orig_param_list.count loop
994 IF p_orig_param_list(i).param_name = '#WF_PAGEFLOW_ITEMTYPE'
995 AND length(p_orig_param_list(i).param_value) > 0 THEN
996
997 l_temp_string := trim(' ' FROM p_orig_param_list(i).param_value);
998
999 IF length(l_temp_string) > 0 then
1000 l_wfitemtype_set := true;
1001 END IF;
1002
1003 ELSIF p_orig_param_list(i).param_name = '#WF_PAGEFLOW_ITEMKEY'
1004 AND length(p_orig_param_list(i).param_value) > 0 THEN
1005
1006 l_temp_string := trim(' ' FROM p_orig_param_list(i).param_value);
1007
1008 IF length(l_temp_string) > 0 then
1009 l_wfitemkey_set := true;
1010 END IF;
1011
1012 ELSIF p_orig_param_list(i).param_name = '#ERECORD_ID'
1013 AND length(p_orig_param_list(i).param_value) > 0 THEN
1014
1015 l_erecord_id := trim(' ' FROM p_orig_param_list(i).param_value);
1016
1017 END IF;
1018
1019 END LOOP;
1020
1021 --Signature is not required if either of these parameters is not set.
1022 IF NOT l_wfitemtype_set or NOT l_wfitemkey_set then
1023
1024 --Hence the approval is complete.
1025 --Raise the approval completetion event with same event key as a combination of the event name and event key.
1026 l_event_name := EDR_CONSTANTS_GRP.G_APPROVAL_COMPLETION_EVT;
1027
1028 IF length(l_erecord_id) > 0 then
1029 l_event_key := l_erecord_id;
1030 else
1031 l_event_key := '-1';
1032 END IF;
1033
1034 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_ORIGINAL_EVENT_NAME,p_orig_event_name,l_param_list);
1035
1036 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_ORIGINAL_EVENT_KEY,p_orig_event_key,l_param_list);
1037
1038 if length(l_erecord_id) > 0 THEN
1039
1040 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_ERECORD_ID,l_erecord_id,l_param_list);
1041
1042 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_EVENT_STATUS,EDR_CONSTANTS_GRP.G_COMPLETE_STATUS,l_param_list);
1043
1044 ELSE
1045 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_EVENT_STATUS,EDR_CONSTANTS_GRP.G_NO_ERES_STATUS,l_param_list);
1046 END IF;
1047
1048 --Raise the approval completion event.
1049 WF_EVENT.RAISE3(L_EVENT_NAME,
1050 L_EVENT_KEY,
1051 null,
1052 L_PARAM_LIST,
1053 P_SEND_DATE);
1054
1055 --Perform a commit after raising the event.
1056 COMMIT;
1057 END IF;
1058
1059 END RAISE_COMPLETION_EVENT;
1060
1061
1062 --This method will be a wrapper over FND_WF_EVENT.RAISE_EVENT
1063 PROCEDURE RAISE_TABLE(P_EVENT_NAME IN VARCHAR2,
1064 P_EVENT_KEY IN VARCHAR2,
1065 P_EVENT_DATA IN CLOB DEFAULT NULL,
1066 P_PARAM_TABLE IN OUT NOCOPY FND_WF_EVENT.PARAM_TABLE,
1067 P_NUMBER_PARAMS IN NUMBER,
1068 P_SEND_DATE IN DATE DEFAULT NULL)
1069 IS
1070
1071 BEGIN
1072
1073 --Call the workflow API to raise the event.
1074 FND_WF_EVENT.RAISE_TABLE(P_EVENT_NAME,
1075 P_EVENT_KEY,
1076 P_EVENT_DATA,
1077 P_PARAM_TABLE,
1078 P_NUMBER_PARAMS,
1079 P_SEND_DATE);
1080
1081 --Call the API to raise the approval completion if required.
1082 RAISE_COMPLETION_EVENT(P_ORIG_EVENT_NAME => p_event_name,
1083 P_ORIG_EVENT_KEY => p_event_key,
1084 P_ORIG_PARAM_LIST => p_param_table,
1085 P_SEND_DATE => p_send_date);
1086
1087
1088 END RAISE_TABLE;
1089
1090 --Bug 3207385: End
1091
1092 --Bug 4122622: Start
1093 --This procedure would fetch the event name and event key for the specified e-record ID.
1094 --This method is strictly private.
1095 --It should be used for a valid e-record ID only.
1096 PROCEDURE GET_EVENT_DETAILS(P_ERECORD_ID IN NUMBER,
1097 X_EVENT_NAME OUT NOCOPY VARCHAR2,
1098 X_EVENT_KEY OUT NOCOPY VARCHAR2)
1099 IS
1100
1101 --Define a cursor on edr_psig_documents.
1102 cursor l_event_csr is
1103 SELECT EVENT_NAME, EVENT_KEY
1104 FROM EDR_PSIG_DOCUMENTS
1105 WHERE DOCUMENT_ID = p_erecord_id;
1106 BEGIN
1107
1108 --Set the secure context.
1109 edr_ctx_pkg.set_secure_attr;
1110
1111 --Open the cursor and fetch the event details.
1112 open l_event_csr;
1113
1114 fetch l_event_csr into x_event_name,x_event_key;
1115
1116 close l_event_csr;
1117
1118 edr_ctx_pkg.unset_secure_attr;
1119
1120 END GET_EVENT_DETAILS;
1121 --Bug 4122622: End
1122
1123 end EDR_ERES_EVENT_PVT;
[Home] [Help]