[Home] [Help]
PACKAGE BODY: APPS.EDR_PSIG_RULE
Source
1 PACKAGE BODY EDR_PSIG_RULE AS
2 /* $Header: EDRRULEB.pls 120.22.12010000.2 2008/12/09 20:18:51 srpuri ship $ */
3
4 /* Global varaibles */
5
6 G_ENTITY_NAME constant varchar2(10) := 'ERECORD';
7 G_TEMP_ENTITY_NAME constant varchar2(15) := 'TEMPERECORD';
8 G_PUBLISH_FLAG_N constant varchar2(1) := 'N';
9 G_PUBLISH_FLAG_Y constant varchar2(1) := 'Y';
10 G_SECURITY_OFF constant NUMBER := 4;
11 G_SECURITY_ON constant NUMBER := 1;
12
13 L_EVENT_NAME VARCHAR2(240);
14 L_EVENT_KEY VARCHAR2(240);
15
16 ------------------------------------------------------------------------------
17 --Bug 4577122: Start
18
19 procedure validate_voting_regime
20 (p_approver_list IN EDR_UTILITIES.approvers_Table,
21 x_valid_regime OUT NOCOPY VARCHAR2,
22 x_voting_regime OUT NOCOPY VARCHAR2)
23 as
24 G_UNVIABLE_NUMBER constant number := -1984;
25
26 l_group_id number;
27 l_action_type_id number;
28 l_action_type_voting_regime varchar2(1);
29
30 l_group_id_prev number := G_UNVIABLE_NUMBER;
31 l_action_type_id_prev number := G_UNVIABLE_NUMBER;
32 l_firstvoter_found varchar2(1) := 'N';
33
34 l_multiple_group_id varchar2(1) := 'N';
35 l_multiple_action_type_id varchar2(1) := 'N';
36 l_multiple_voting_regime varchar2(1) := 'N';
37
38 i number;
39 begin
40 for i in 1..p_approver_list.count loop
41 --get the voting regime of the action id
42 l_action_type_voting_regime :=
43 ame_engine.GETACTIONTYPEVOTINGREGIME
44 (ACTIONTYPEIDIN => p_approver_list(i).action_type_id);
45
46 l_group_id := p_approver_list(i).group_or_chain_id;
47 l_action_type_id := p_approver_list(i).action_type_id;
48 l_action_type_voting_regime := ame_engine.GETACTIONTYPEVOTINGREGIME
49 (ACTIONTYPEIDIN => l_action_type_id);
50
51 if (l_group_id_prev <> G_UNVIABLE_NUMBER and
52 l_group_id_prev <> l_group_id and
53 l_multiple_group_id <> 'Y') then
54 l_multiple_group_id := 'Y';
55 else
56 l_group_id_prev := l_group_id;
57 end if;
58
59 if (l_action_type_id_prev <> G_UNVIABLE_NUMBER and
60 l_action_type_id_prev <> l_action_type_id and
61 l_multiple_action_type_id <> 'Y') then
62 l_multiple_action_type_id := 'Y';
63 else
64 l_action_type_id_prev := l_action_type_id;
65 end if;
66
67 if (l_action_type_voting_regime = ame_util.firstApproverVoting) then
68 l_firstvoter_found := 'Y';
69 x_voting_regime := ame_util.firstApproverVoting;
70 end if;
71
72 if (l_firstvoter_found = 'Y' and
73 (l_multiple_group_id = 'Y' or l_multiple_action_type_id = 'Y')) then
74 x_valid_regime := 'N';
75 return;
76 end if;
77 end loop;
78
79 x_valid_regime := 'Y';
80 end validate_voting_regime;
81 --Bug 4577122: End
82 ------------------------------------------------------------------------------
83
84 -- Bug 3761813 : start
85 ------------------------------------------------------------------------------
86 /* PRE_TX_SNAPSHOT IN:
87 p_event_id Unique eREcord ID,
88 p_map_code Map Code passed as Subscription parameter,
89 p_document EVENT KEY
90 */
91
92 --Bug 4306292: Start
93 --Commenting changes made for red lining.
94
95 function PRE_TX_SNAPSHOT
96 ( p_event_id in number,
97 p_MAPCODE in varchar2,
98 p_document in varchar2
99 ) return boolean IS PRAGMA AUTONOMOUS_TRANSACTION;
100 L_XML_DOCUMENT CLOB;
101 retcode pls_integer;
102 errmsg varchar2(2000);
103 logfile varchar2(200);
104 begin
105 INSERT into edr_erecord_id_temp (document_id) values (p_Event_id);
106 ecx_outbound.getXML
107 (
108 i_map_code => p_MAPCODE,
109 i_document_id => P_DOCUMENT,
110 i_debug_level => 6,
111 i_xmldoc => L_XML_DOCUMENT,
112 i_ret_code => retcode,
113 i_errbuf => errmsg,
114 i_log_file => logfile
115 );
116 insert into EDR_REDLINE_TRANS_DATA (EVENT_ID,PRE_XML_DATA,POST_XML_DATA,ERR_CODE,ERR_MSG,DIFF_XML,APPENDIX_GEN_XML) values (p_event_id,L_XML_DOCUMENT,empty_clob(), retcode, errmsg, empty_clob(),empty_clob());
117 commit;
118 return true;
119 exception
120 when others then
121 rollback;
122 return false;
123 end;
124
125 --Bug 4306292: End
126
127 -- Bug 3761813 : end
128
129 ----------------------------------------------------------------------------------
130
131 /* CREATE Pageflow IN:
132 p_event_id Unique eREcord ID,
133 p_map_code Map Code passed as Subscription parameter,
134 p_ame_transaction_type AME Transaction Type passed as Subscription parameter,
135 p_audit_group AOL Audit Group passed as Subscription parameter,
136 p_eventP Event to be processes
137 p_approverlist AME approver Table
138 */
139
140 function CREATE_PAGEFLOW
141 ( p_event_id in number,
142 p_map_code in varchar2,
143 p_ame_transaction_type in varchar2,
144 p_audit_group in varchar2,
145 p_eventP in out NOCOPY wf_event_t,
146 p_subscription_guid in raw,
147 P_approverlist in EDR_UTILITIES.approvers_Table,
148 P_ERR_CODE out NOCOPY varchar2,
149 P_ERR_MSG out NOCOPY varchar2
150
151 ) return varchar2 IS PRAGMA AUTONOMOUS_TRANSACTION;
152
153 l_event_user_key_label VARCHAR2(240);
154 l_event_user_key_VALUE VARCHAR2(240);
155 l_transaction_audit_id NUMBER;
156 i integer;
157 l_edr_signature_id number;
158 l_fnd_user varchar2(100);
159 l_temp_clob CLOB;
160 l_itemtype varchar2(240);
161 l_itemkey varchar2(240);
162 aname varchar2(240);
163 avalue varchar2(4000);
164 lparam wf_parameter_list_t;
165 l_event wf_event_t;
166 l_return_status varchar2(240);
167 l_document_id number;
168 l_error number;
169 l_error_msg varchar2(4000);
170 l_error_stack varchar2(4000);
171 l_signature_id number;
172 l_requester varchar2(240);
173 l_doc_params EDR_PSIG.params_table;
174
175 /*Save Current Workflow Threshold */
176 l_cur_threshold WF_ENGINE.THRESHOLD%TYPE;
177 l_overriding_approver varchar2(80);
178 l_overriding_comments varchar2(4000);
179 BAD_RULE Exception;
180 DEFAULT_RULE_ERROR Exception;
181
182 BEGIN
183 --Bug 4074173 : start
184 l_cur_threshold := WF_ENGINE.THRESHOLD;
185 --Bug 4074173 : end
186
187 /* Update workflow threshold to default threshold */
188 WF_ENGINE.THRESHOLD := 50;
189
190 /* Get Payload Attributes required by Rule function */
191 l_event_user_key_label := wf_event.getValueForParameter('PSIG_USER_KEY_LABEL',p_eventP.Parameter_List);
192 l_event_user_key_value := wf_event.getValueForParameter('PSIG_USER_KEY_VALUE',p_eventP.Parameter_List);
193 l_transaction_audit_id := wf_event.getValueForParameter('PSIG_TRANSACTION_AUDIT_ID',p_eventP.Parameter_List);
194 l_requester := wf_event.getValueForParameter('#WF_SIGN_REQUESTER',p_eventP.Parameter_List);
195 wf_event.AddParameterToList('#FROM_ROLE', l_requester,p_eventP.Parameter_List); /* From Role */
196
197 /* Temp Table Insertion */
198 l_event_name:=P_eventP.getEventName( );
199 l_event_key:=P_eventP.getEventKey( );
200 l_temp_clob:=P_eventP.getEventData( );
201
202 --Diagnostics Start
203 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
204 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_PAGEFLOW_PROC_EVT');
205 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
206 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
207 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
208 'edr.plsql.EDR_PSIG_RULE.CREATE_PAGEFLOW',
209 FALSE
210 );
211 end if;
212 --Diagnostics End
213
214 /* Insert into Temp tables for Further Processing */
215 wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','Event Name Assigned');
216 wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','CLOB Assignment Complete and insert being executed');
217
218 INSERT INTO EDR_ERECORDS(EVENT_ID,
219 EVENT_NAME,
220 EVENT_KEY,
221 EVENT_USER_KEY_LABEL,
222 EVENT_USER_KEY_VALUE,
223 EVENT_TIMESTAMP,
224 EVENT_TIMEZONE,
225 ERECORD_XML,
226 ERECORD_SIGNATURE_STATUS,
227 XML_MAP_NAME,
228 AME_TRANSACTION_TYPE,
229 AUDIT_GROUP,
230 TRANSACTION_AUDIT_ID)
231 Values(p_event_id,
232 l_event_name,
233 l_event_key,
234 l_event_user_key_label,
235 l_event_user_key_value,
236 SYSDATE,
237 NULL, -- Still figuring out how to populate this
238 l_temp_clob,
239 'PENDING',
240 P_map_code,
241 p_ame_transaction_type,
242 p_audit_group,
243 l_transaction_audit_id);
244
245
246 wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','Insertion Complete in EDR_ERECORDS');
247 -- Bug Fix 3143107
248 -- Removed Open Docement
249 -- Post document parameters
250 -- Change status
251 -- Now this part will be handeled in PSIG_RULE for Both eRecord Only
252 -- and eSignature Required scenarios
253 --
254 l_document_id := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.g_erecord_id_attr,p_eventP.Parameter_List);
255 --Diagnostics Start
256 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
257 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_PAGEFLOW_EREC_EVT');
258 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
259 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
260 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
261 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
262 'edr.plsql.EDR_PSIG_RULE.CREATE_PAGEFLOW',
263 FALSE
264 );
265 end if;
266 --Diagnostics End
267
268
269 wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','Set the Document ID '||l_document_ID ||' as Event Parameter');
270 wf_event.AddParameterToList('PSIG_DOCUMENT_ID', l_document_id,p_eventP.Parameter_List); /* Document_ID*/
271 wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','Document ID added to event parameters ' );
272
273 /* 24-FEB-2003: CJ : add the attachment item attribute to the workflow so that the attachments
274 of the eRecord show up in the workflow notification sent out to the signer*/
275
276
277 /* Attachments_ID*/
278 wf_event.AddParameterToList('#ATTACHMENTS', 'FND:entity=ERECORD'||'&'||'pk1name=DOCUMENT_ID'||'&'||'pk1value='||l_document_id,p_eventP.Parameter_List);
279 wf_log_pkg.string(3, 'EDR_PSIG_rule.create_pageflow','Attachments added to event parameters ' );
280 /* 24-FEB-2003: CJ: end */
281
282 for i in 1 .. P_approverlist.count loop
283
284 --Bug 2674799: start
285 --New AME approvers table has the WF Directory name of user.
286 l_fnd_user := P_approverList(i).name;
287 wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','AME Approver '||P_approverlist(i).occurrence||'-'||l_fnd_user);
288 wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','Figuring out overriding approver for '||l_fnd_user);
289 --Bug 4160412: Start
290 --Find the overriding details only if a user name is provided.
291 if length(l_fnd_user) > 0 and instr(l_fnd_user,'#') = 0 then
292 EDR_STANDARD.FIND_WF_NTF_RECIPIENT(l_fnd_user,'EDRPSIGF','PSIG_EREC_MESSAGE_BLAF',
293 l_overriding_approver,l_overriding_comments,
294 l_error,l_error_msg);
295
296 IF l_error > 0 THEN
297 RAISE BAD_RULE;
298 END IF;
299
300 else
301
302 l_overriding_approver := l_fnd_user;
303
304 end if;
305
306 --Bug 4160412: End
307
308 --Diagnostics Start
309 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
310 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_PAGEFLOW_APPR_EVT');
311 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
312 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
313 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
314 FND_MESSAGE.SET_TOKEN('APPROVER_NO',i);
315 FND_MESSAGE.SET_TOKEN('APPROVER_COUNT',p_approverlist.count);
316 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
317 'edr.plsql.EDR_PSIG_RULE.CREATE_PAGEFLOW',
318 FALSE
319 );
320 end if;
321 --Diagnostics End
322
323 wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','Figured out overriding approver for '||l_fnd_user||' as '||l_overriding_approver);
324
325 /* Insert into Temp tables for Further Processing */
326 SELECT EDR_ESIGNATURES_S.NEXTVAL into l_edr_signature_id from DUAL;
327 wf_log_pkg.string(3, 'EDR_PSIG_rule.create_pageflow','Signature Sequence Fetched '||l_edr_signature_id);
328
329 INSERT into EDR_ESIGNATURES(SIGNATURE_ID,
330 EVENT_ID,
331 EVENT_NAME,
332 USER_NAME,
333 SIGNATURE_SEQUENCE,
334 SIGNATURE_STATUS,
335 ORIGINAL_RECIPIENT,
336 SIGNATURE_OVERRIDING_COMMENTS)
337
338 values(l_edr_signature_id,
339 p_event_id,
340 l_event_name,
341 l_OVERRIDING_APPROVER,
342 P_approverList(i).approver_order_number,
343 'PENDING',
344 L_FND_USER,
345 L_OVERRIDING_COMMENTS);
346 --Bug 2674799 : end
347
348 --Diagnostics Start
349 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
350 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_PAGEFLOW_ESIG_EVT');
351 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
352 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
353 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
354 FND_MESSAGE.SET_TOKEN('APPROVER',l_overriding_approver);
355 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
356 'edr.plsql.EDR_PSIG_RULE.CREATE_PAGEFLOW',
357 FALSE
358 );
359 end if;
360 --Diagnostics End
361
362
363 wf_log_pkg.string(3, 'EDR_PSIG_rule.create_pageflow','Insert Complete into EDR_ESIGNATURES');
364 wf_log_pkg.string(3, 'EDR_PSIG_rule.create_pageflow','Requesting Signatures in PSIG evidance Store');
365
366 --Bug 2674799: start
367 --Store the approver order number in EDR_PSIG_DETAILS
368 --Bug 4160412: Start
369 --Request for signature only if a user name is provided.
370 if length(l_fnd_user) > 0 and instr(l_fnd_user,'#') = 0 then
371 EDR_PSIG.REQUESTSIGNATURE(P_DOCUMENT_ID=>l_document_id,
372 P_USER_NAME=>l_overriding_approver,
373 P_ORIGINAL_RECIPIENT=>l_fnd_user,
374 P_OVERRIDING_COMMENTS=>l_overriding_comments,
375 P_SIGNATURE_ID=>l_signature_id,
376 P_ERROR=>L_ERROR,
377 P_ERROR_MSG=>l_error_msg,
378 P_SIGNATURE_SEQUENCE => P_APPROVERLIST(I).APPROVER_ORDER_NUMBER);
379 --Bug 2674799: end
380
381 --Bug 3416357: start
382 --Trap the error after requestSignature.
383 IF (nvl(l_error,0) > 0) THEN
384 RAISE BAD_RULE;
385 END IF;
386 end if;
387 --Bug 3416357: end
388 --Bug 4160412: End
389
390 --Diagnostics Start
391 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
392 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_PAGEFLOW_PSIG_EVT');
393 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
394 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
395 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
396 FND_MESSAGE.SET_TOKEN('APPROVER',l_overriding_approver);
397 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
398 'edr.plsql.EDR_PSIG_RULE.CREATE_PAGEFLOW',
399 FALSE
400 );
401 end if;
402 --Diagnostics End
403
404 wf_log_pkg.string(1, 'EDR_PSIG_rule.create_pageflow','PSIG id for' ||l_fnd_user||' is '||l_signature_id);
405
406 /* Clear the entries */
407 l_overriding_comments:=NULL;
408 l_overriding_approver:=NULL;
409 l_fnd_user:=NULL;
410
411 end loop;
412
413 /* END Approver List */
414
415 wf_log_pkg.string(3, 'EDR_PSIG_RULE.create_pageflow','AME Approver list inserted into temp table');
416 /*call Workflow dEfault Rule funciton */
417
418 l_return_status:=WF_RULE.DEFAULT_RULE(p_subscription_guid=>p_subscription_guid,p_event=>p_eventP);
419
420
421 if l_return_status = EDR_CONSTANTS_GRP.g_error_status THEN
422 ROLLBACK;
423 -- Bug Fix :3154362
424 -- Added following two lines first one retrieves error message populated by
425 -- Rule function
426 -- second line will push error message it UI as cascading exception handling
427 --
428 l_error_msg := p_eventP.GETERRORMESSAGE;
429 RAISE DEFAULT_RULE_ERROR;
430 ELSE
431 COMMIT;
432 wf_log_pkg.string(3, 'EDR_PSIG_RULE.create_pageflow','COMMIT Completed in Create pageflow');
433
434 --Diagnostics Start
435 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
436 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_PAGEFLOW_SUCCESS_EVT');
437 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
438 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
439 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
440 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
441 'edr.plsql.EDR_PSIG_RULE.CREATE_PAGEFLOW',
442 FALSE
443 );
444 end if;
445 --Diagnostics End
446
447 END IF;
448
449 /* Udate workflow threshold to saved value */
450 WF_ENGINE.THRESHOLD := l_cur_threshold;
451 RETURN l_return_status;
452
453 exception
454 when BAD_RULE then
455
456 /* Update workflow threshold to saved value */
457 WF_ENGINE.THRESHOLD := l_cur_threshold;
458 Wf_Core.Context('EDR_PSIG_RULE', 'CREATE_PAGEFLOW', p_eventP.getEventName(), p_subscription_guid);
459 wf_event.setErrorInfo(p_eventP,'ERROR');
460 ROLLBACK;
461 p_err_code:=l_error;
462 p_err_msg:=l_error_msg;
463
464 --Diagnostics Start
465 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
466 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
467 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
468 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','CREATE_PAGEFLOW');
469 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
470 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
471 'edr.plsql.EDR_PSIG_RULE.CREATE_PAGEFLOW',
472 FALSE
473 );
474 end if;
475 --Diagnostics End
476
477 return 'ERROR';
478
479 when DEFAULT_RULE_ERROR then
480 /* Update workflow threshold to saved value */
481 WF_ENGINE.THRESHOLD := l_cur_threshold;
482 Wf_Core.Context('EDR_PSIG_RULE', 'CREATE_PAGEFLOW', p_eventP.getEventName(), p_subscription_guid);
483 wf_event.setErrorInfo(p_eventP,'ERROR');
484 ROLLBACK;
485 p_err_code:=l_error;
486 p_err_msg:= l_error_msg;
487
488 --Diagnostics Start
489 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
490 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
491 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
492 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','CREATE_PAGEFLOW');
493 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
494 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
495 'edr.plsql.EDR_PSIG_RULE.CREATE_PAGEFLOW',
496 FALSE
497 );
498 end if;
499 --Diagnostics End
500
501 return 'ERROR';
502
503 when others then
504 /* Udate workflow threshold to saved value */
505 Wf_Core.get_ERROR(l_error,l_error_msg,l_error_stack);
506 wf_log_pkg.string(6,'in when others of create_pageflow'||l_error,l_error_msg||substr(l_error_stack,1,100));
507 WF_ENGINE.THRESHOLD := l_cur_threshold;
508 Wf_Core.Context('EDR_PSIG_RULE', 'CREATE_PAGEFLOW', p_eventP.getEventName(), p_subscription_guid);
509 wf_event.setErrorInfo(p_eventP,'ERROR');
510 ROLLBACK;
511 p_err_code:=l_error;
512 p_err_msg:=l_error_msg;
513 --Diagnostics Start
514 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
515 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
516 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
517 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','CREATE_PAGEFLOW');
518 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
519 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
520 'edr.plsql.EDR_PSIG_RULE.CREATE_PAGEFLOW',
521 FALSE
522 );
523 end if;
524 --Diagnostics End
525 return 'ERROR';
526 END CREATE_PAGEFLOW;
527
528 -------------------------------------------------------------------------------------------------------------------------------------
529
530 --
531 -- Start of comments
532 -- API name : STORE_ERECORD
533 -- Type : Private.
534 -- Function : Generates and stores eRecord into Evidence Store
535 -- autonomously.
536 -- Pre-reqs : None.
537 -- Parameters :
538 -- p_XML_DOCUMENT -- XML Generated in calling procedure
539 -- p_style_shee_repository -- Style Sheet repository which will be responsibile for providing the API
540 -- to be called to provide the stylesheet in blob
541 -- p_style_sheet -- Style Sheet to be applied
542 -- p_style_sheet_ver -- Version of the stlye sheet to be applied
543 -- p_approved_style_sheet_required -- Whether appoved Style Sheet is required for current transaction
544 -- p_edr_event_id -- Event Id this is used to associate with attachments
545 -- p_esign_required -- Siganture Required flag it will be either 'Y' or 'N'
546 -- p_subscription_guid -- Subscription Id
547 -- x_event -- This is in OUT using this calling code can access
548 -- updated event parameters
549
550 PROCEDURE STORE_ERECORD(p_XML_DOCUMENT CLOB,
551 p_style_sheet_repository VARCHAR2,
552 p_style_sheet VARCHAR2,
553 p_style_sheet_ver VARCHAR2,
554 p_application_code VARCHAR2,
555 p_edr_event_id NUMBER,
556 p_esign_required VARCHAR2,
557 p_subscription_guid RAW,
558 x_event in out NOCOPY wf_event_t
559 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
560 l_XML_DOCUMENT CLOB ;
561 l_EREC_OUTPUT CLOB;
562 l_EREC_OUTPUT_CH VARCHAR2(512);
563 l_erec_output_ln number;
564 L_EVENT_NAME varchar2(240);
565 L_EVENT_KEY varchar2(240);
566 l_document_id number;
567 l_error number;
568 l_error_msg varchar2(4000);
569 l_event_user_key_label VARCHAR2(240);
570 l_event_user_key_VALUE VARCHAR2(240);
571 l_params EDR_PSIG.params_table;
572 l_source_application varchar2(240);
573 l_requester varchar2(240);
574 l_user_id NUMBER;
575 l_login_id NUMBER;
576
577 l_approved BOOLEAN ;
578 l_style_sheet_type VARCHAR2(240);
579 l_output_format VARCHAR2(25);
580 ERECORD_GENERATION_ERROR EXCEPTION;
581 ERECORD_CREATION_ERROR EXCEPTION;
582 ERECORD_PARAM_ERROR EXCEPTION;
583
584 ERECORD_GEN_XSLFOMISS_ERR EXCEPTION;
585 ERECORD_GEN_FATALERR EXCEPTION;
586 ERECORD_GEN_XDOERR EXCEPTION;
587 ERECORD_GEN_UNKNWN_ERR EXCEPTION;
588 ERECORD_GEN_JSPMSNG_ERR EXCEPTION;
589 ERECORD_GEN_JSPRQFAIL_ERR EXCEPTION;
590 ERECORD_UNKWN_TMPL_TYPE EXCEPTION;
591
592 ERECORD_GEN_XSLT_ERR EXCEPTION;
593
594 --Bug 2637353: Start
595 l_source_application_type VARCHAR2(10);
596 MSCA_TMPL_ERROR EXCEPTION;
597 --Bug 2637353: End
598 -- Bug 3761813 : start
599 l_redline_required varchar2(30);
600 X_DIFF_XML CLOB ;
601 x_output CLOB ;
602 l_amount BINARY_INTEGER ;
603 l_pos PLS_INTEGER ;
604 l_clob_len PLS_INTEGER;
605 vBuffer VARCHAR2 (32767);
606 -- Bug 3761813 : END
607
608 --Bug 4150616: Start
609 l_force_erecord VARCHAR2(10);
610 l_force_erecord_used VARCHAR2(10);
611 --Bug 4150616: End
612 BEGIN
613
614 --Bug 4150616: Start
615 --Fetch the value of FORCE_ERECORD from the event payload.
616 l_force_erecord := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_FORCE_ERECORD,x_event.Parameter_List);
617
618 --Fetch the value of FORCE_ERECORD_USED from the event payload.
619 l_force_erecord_used := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_FORCE_ERECORD_USED,x_event.Parameter_List);
620 --Bug 4150616: End
621
622 --Bug 4074173 : start
623 l_approved := false;
624 l_XML_DOCUMENT := p_XML_DOCUMENT;
625 l_user_id := fnd_global.user_id;
626 l_login_id := fnd_global.login_id;
627 l_style_sheet_type := 'XSL';
628 l_output_format := null;
629 --Bug 4074173 : end
630
631 l_event_name:=x_event.getEventName( );
632 l_event_key:=x_event.getEventKey( );
633
634 --Diagnostics Start
635 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
636 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_PROC_EVT');
637 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
638 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
639 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
640 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
641 FALSE
642 );
643 end if;
644 --Diagnostics End
645
646 /* Only eRecord Required. Generate the eRecord output Output and post it in temp tables */
647 L_EREC_OUTPUT:=P_XML_DOCUMENT;
648 wf_log_pkg.string(3, 'EDR_PSIG_RULE.STORE_ERECORD','Start of store erecord');
649 l_requester := wf_event.getValueForParameter('#WF_SIGN_REQUESTER',x_event.Parameter_List);
650
651 --Bug 3170251 : Start- EREC_TEMPLATE TYPE from Workflow
652 /* Find the Style Sheet Type from Workflow Parameter EREC_TEMPLATE_TYPE */
653 l_style_sheet_type := wf_event.getValueForParameter('EREC_TEMPLATE_TYPE',x_event.Parameter_List);
654 -- Bug 3170251 : End
655
656 -- Bug 3170251 : Start- Open the PSIG document, to get the l_document_id value for use in XDOC
657 EDR_PSIG.openDocument(P_PSIG_XML => p_xml_document,
658 P_PSIG_REQUESTER => l_requester,
659 P_DOCUMENT_ID => l_document_id,
660 P_ERROR => l_error,
661 P_ERROR_MSG => l_error_msg);
662 -- Bug 3170251 : End
663
664 IF l_error is NOT NULL THEN
665 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','open document errored out with code '||l_error);
666 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','open document Error Msg '||l_error_msg);
667 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Opened Document in eRecords Repository with Document ID '||l_document_id);
668 RAISE ERECORD_CREATION_ERROR;
669 END IF;
670
671 wf_log_pkg.string(3, 'EDR_PSIG_RULE.store_erecord','committing the transaction in store erecord');
672 commit;
673
674 --Diagnostics Start
675 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
676 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_EREC_EVT');
677 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
678 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
679 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
680 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
681 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
682 FALSE
683 );
684 end if;
685 --Diagnostics End
686
687 -- Bug 3761813 : start
688 l_redline_required := wf_event.getValueForParameter('REDLINE_REQUIRED',x_event.Parameter_List);
689 wf_log_pkg.string(3, 'EDR_PSIG_RULE.store_erecord','REDLINE_REQUIRED'||l_redline_required);
690 -- Bug 3761813 : end
691
692
693 -- Bug 3170251 : Start- if EREC_TEMPLATE TYPE isRTF then call EDR_XDOC_UTIL_PKG.GENERATE_ERECORD procedure.
694 -- call xdoc utility package to geneate the pdf document if style sheet is RTF
695 IF (UPPER(l_style_sheet_type) = 'RTF') THEN
696
697 --Bug 2637353: Start
698 --Check if source application type is "MSCA".
699 l_source_application_type := wf_event.getValueForParameter('#WF_SOURCE_APPLICATION_TYPE',x_event.Parameter_List);
700 if(l_source_application_type = 'MSCA') then
701 raise MSCA_TMPL_ERROR;
702 END IF;
703 --Bug 2637353: End
704
705 --Diagnostics Start
706 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
707 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_RTF_EVT');
708 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
709 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
710 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
711 FND_MESSAGE.SET_TOKEN('TEMPLATE_NAME',p_style_sheet);
712 FND_MESSAGE.SET_TOKEN('TEMPLATE_VER',p_style_sheet_ver);
713 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
714 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
715 FALSE
716 );
717 end if;
718 --Diagnostics End
719
720 /* EDR_XDOC_UTIL_PKG.generate_ERecord(p_edr_event_id =>p_edr_event_id,
721 p_erecord_id => l_document_id,
722 p_style_sheet_repository => p_style_sheet_repository,
723 p_style_sheet => p_style_sheet,
724 p_style_sheet_ver => p_style_sheet_ver,
725 x_output_format => l_output_format,
726 x_error_code => l_error,
727 x_error_msg => l_error_msg);*/
728 --Bug 3761813 : start
729 -- new parameter p_redline_mode is added
730
731 EDR_XDOC_UTIL_PKG.generate_ERecord(p_edr_event_id => p_edr_event_id,
732 p_erecord_id => l_document_id,
733 p_style_sheet_repository => p_style_sheet_repository,
734 p_style_sheet => p_style_sheet,
735 p_style_sheet_ver => p_style_sheet_ver,
736 p_redline_mode => l_redline_required,
737 --Bug 4306292: End
738 -- Bug 3170251 : start
739 p_application_code => p_application_code,
740 -- Bug 3170251 : end
741 x_output_format => l_output_format,
742 x_error_code => l_error,
743 x_error_msg => l_error_msg);
744 --Bug 3761813 : end
745
746 --Bug 3474765 : Start
747 -- Changing to MIME Format
748 l_output_format := 'application/pdf';
749 --Bug 3474765 : End
750
751 --Raise exception if there is any error in e-Record Generation
752
753 -- Important Error Handling done to make sure UTL_HTTP based eRecord PDF generation
754 -- was successful, otherwise throw appropriate error to the user,
755
756 IF l_error = 10 THEN
757 -- This error means either the XSLFO for given template is missing i.e. due to
758 -- approval process missing or converision error OR Template is not uploaded
759 -- in the respective repository.
760 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code from PDF e-record generation process: '||l_error);
761 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg from PDF e-record generation process: '||l_error_msg);
762 RAISE ERECORD_GEN_XSLFOMISS_ERR;
763 END IF;
764
765 IF l_error = 20 THEN
766 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code from PDF e-record generation process: '||l_error);
767 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg from PDF e-record generation process: '||l_error_msg);
768 RAISE ERECORD_CREATION_ERROR;
769 END IF;
770
771 IF l_error = 30 THEN
772 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code from PDF e-record generation process: '||l_error);
773 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg from PDF e-record generation process: '||l_error_msg);
774 RAISE ERECORD_GEN_FATALERR;
775 END IF;
776
777 IF l_error = 40 THEN
778 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code from PDF e-record generation process: '||l_error);
779 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg from PDF e-record generation process: '||l_error_msg);
780 RAISE ERECORD_GEN_XDOERR;
781 END IF;
782
783 IF l_error = 100 THEN
784 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code from PDF e-record generation process: '||l_error);
785 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg from PDF e-record generation process: '||l_error_msg);
786 RAISE ERECORD_GEN_UNKNWN_ERR;
787 END IF;
788
789 IF l_error = 505 THEN
790 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code from PDF e-record generation process: '||l_error);
791 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg from PDF e-record generation process: '||l_error_msg);
792 RAISE ERECORD_GEN_JSPMSNG_ERR;
793 END IF;
794
795 IF l_error = 500 THEN
796 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code from PDF e-record generation process: '||l_error);
797 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg from PDF e-record generation process: '||l_error_msg);
798 RAISE ERECORD_GEN_JSPRQFAIL_ERR;
799 END IF;
800
801 /* Set the E-Record Notification Details to Message for PDF E-Records */
802 fnd_message.set_name('EDR','EDR_EREC_ATT_NTF_BODY');
803 fnd_message.set_token('ERECORD_ID',l_document_id);
804
805 l_erec_output_ch := fnd_message.get;
806 l_erec_output_ln := DBMS_LOB.GETLENGTH(L_EREC_OUTPUT);
807 DBMS_LOB.ERASE(L_EREC_OUTPUT,l_erec_output_ln , 1);
808 DBMS_LOB.WRITE(L_EREC_OUTPUT, LENGTH(L_EREC_OUTPUT_CH), 1, l_erec_output_ch);
809 DBMS_LOB.TRIM(L_EREC_OUTPUT, length(l_erec_output_ch));
810
811 --Diagnostics Start
812 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
813 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_PDF_SUC_EVT');
814 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
815 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
816 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
817 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
818 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
819 FALSE
820 );
821 end if;
822 --Diagnostics End
823
824 END IF;
825 --Bug: 3170251 - End
826
827 -- For normal XSL stylsheet generate HTML eRecord using ECX Transformations
828 IF(UPPER(l_style_sheet_type) = 'XSL') THEN
829
830 --Diagnostics Start
831 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
832 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_XSL_EVT');
833 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
834 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
835 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
836 FND_MESSAGE.SET_TOKEN('TEMPLATE_NAME',p_style_sheet);
837 FND_MESSAGE.SET_TOKEN('TEMPLATE_VER',p_style_sheet_ver);
838 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
839 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
840 FALSE
841 );
842 end if;
843 --Diagnostics End
844
845 -- Bug 3761813 : start
846 --Bug 4306292: Start
847 --Commenting the changes made for red lining.
848
849 --get the DIFF XML and transform the same.
850 if (nvl(l_redline_required,'N') = 'Y' ) then
851
852 X_DIFF_XML := EMPTY_CLOB;
853 x_output := EMPTY_CLOB;
854 l_amount := 32767;
855 l_pos := 1;
856
857 EDR_XDOC_UTIL_PKG.generate_ERecord(p_edr_event_id => p_edr_event_id,
858 p_erecord_id => l_document_id,
859 p_style_sheet_repository => p_style_sheet_repository,
860 p_style_sheet => p_style_sheet,
861 p_style_sheet_ver => p_style_sheet_ver,
862 p_redline_mode => l_redline_required,
863 p_application_code => p_application_code,
864 x_output_format => l_output_format,
865 x_error_code => l_error,
866 x_error_msg => l_error_msg);
867
868 wf_log_pkg.string(1, 'EDR_PSIG_RULE.store_erecord',' generate_ERecord for redline. Error '||l_error );
869
870 wf_log_pkg.string(1, 'EDR_PSIG_RULE.store_erecord',' generate_ERecord for redline. l_error_msg '||l_error_msg );
871
872 dbms_lob.createtemporary(X_DIFF_XML,TRUE);
873 dbms_lob.open( X_DIFF_XML, dbms_lob.lob_readwrite );
874
875 select DIFF_XML into x_output from EDR_REDLINE_TRANS_DATA where EVENT_ID = p_edr_event_id ;
876
877 l_clob_len := dbms_lob.getlength(x_output);
878 WHILE l_pos < l_clob_len LOOP
879 dbms_lob.read(x_output, l_amount, l_pos, vBuffer);
880 dbms_lob.writeappend(X_DIFF_XML, LENGTH(vBuffer), vBuffer);
881 l_pos := l_pos + l_amount;
882 END LOOP;
883
884 L_EREC_OUTPUT := X_DIFF_XML;
885 dbms_lob.close(X_DIFF_XML);
886 dbms_lob.freeTemporary(X_DIFF_XML );
887
888 IF nvl(l_error,0) <> 0 THEN
889 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Template Type : text ');
890 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code '||l_error);
891 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg '||l_error_msg);
892 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','eRecord generated');
893 RAISE ERECORD_GEN_XSLT_ERR;
894 END IF;
895
896 IF l_error = 10 THEN
897 -- This error means either the XSLFO for given template is missing i.e. due to
898 -- approval process missing or converision error OR Template is not uploaded
899 -- in the respective repository.
900 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code XSL '||l_error);
901 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg '||l_error_msg);
902 RAISE ERECORD_GEN_XSLFOMISS_ERR;
903 END IF;
904
905 IF l_error = 20 THEN
906 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code XSL'||l_error);
907 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg '||l_error_msg);
908 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','eRecord generated');
909 RAISE ERECORD_CREATION_ERROR;
910 END IF;
911
912 IF l_error = 30 THEN
913 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code XSL'||l_error);
914 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg '||l_error_msg);
915 RAISE ERECORD_GEN_FATALERR;
916 END IF;
917
918 IF l_error = 40 THEN
919 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error CodeXSL '||l_error);
920 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg '||l_error_msg);
921 RAISE ERECORD_GEN_XDOERR;
922 END IF;
923
924 IF l_error = 100 THEN
925 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code XSL'||l_error);
926 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg '||l_error_msg);
927 RAISE ERECORD_GEN_UNKNWN_ERR;
928 END IF;
929
930 IF l_error = 505 THEN
931 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code XSL'||l_error);
932 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg '||l_error_msg);
933 RAISE ERECORD_GEN_JSPMSNG_ERR;
934 END IF;
935
936 IF l_error = 500 THEN
937 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code XSL'||l_error);
938 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg '||l_error_msg);
939 RAISE ERECORD_GEN_JSPRQFAIL_ERR;
940 END IF;
941 END IF;
942 --Bug 4306292: End
943 --Bug 3761813 : END
944
945 ECX_STANDARD.perform_xslt_transformation (i_xml_file => L_EREC_OUTPUT,
946 i_xslt_file_name => p_style_sheet,
947 i_XSLT_FILE_VER => p_style_sheet_ver,
948 i_XSLT_APPLICATION_CODE => lower(p_application_code),
949 i_retcode => l_error,
950 i_retmsg => l_error_msg);
951 -- Bug 4862464: Start
952 -- l_output_format :=TEXT;
953 -- Changing the harcoded document format from TEXT to
954 -- text/plain
955 -- The edr_psig.openDocument api converts the document format from TEXT
956 -- to text/plain but the updatedocument api retains the document format
957 -- so for consistency we pass the text/plain document format.
958 l_output_format := WF_NOTIFICATION.doc_text;
959 -- Bug 4862464: End
960 IF nvl(l_error,0) <> 0 THEN
961 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','XSL Transformation Error Code '||l_error);
962 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','XSL Transformation Error Msg '||l_error_msg);
963 RAISE ERECORD_GEN_XSLT_ERR;
964 END IF;
965
966 --Diagnostics Start
967 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
968 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_XSL_SUC_EVT');
969 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
970 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
971 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
972 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
973 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
974 FALSE
975 );
976 end if;
977 --Diagnostics End
978
979 END IF;
980
981
982
983 -- Check if template type is one of the valid types.
984 IF ((UPPER(l_style_sheet_type) <> 'XSL') AND (UPPER(l_style_sheet_type) <> 'RTF')) THEN
985 wf_log_pkg.string(5, 'EDR_PSIG_RULE.store_erecord','Wrong Stylesheet Type ' || l_style_sheet_type );
986 RAISE ERECORD_UNKWN_TMPL_TYPE;
987 END IF;
988
989 /* Get Payload Attributes required by Rule function */
990 l_event_user_key_label := wf_event.getValueForParameter('PSIG_USER_KEY_LABEL',x_event.Parameter_List);
991 l_event_user_key_value := wf_event.getValueForParameter('PSIG_USER_KEY_VALUE',x_event.Parameter_List);
992 l_source_application := wf_event.getValueForParameter('#WF_SOURCE_APPLICATION_TYPE',x_event.Parameter_List);
993 l_requester := wf_event.getValueForParameter('#WF_SIGN_REQUESTER',x_event.Parameter_List);
994
995 /* Call Repository API's */
996 wf_log_pkg.string(3, 'EDR_PSIG_RULE.store_erecord','Posting Document in eRecords Repository');
997
998 EDR_PSIG.updateDocument(P_DOCUMENT_ID => l_document_id,
999 P_PSIG_XML => L_XML_DOCUMENT,
1000 P_PSIG_DOCUMENT => L_EREC_OUTPUT,
1001 P_PSIG_DOCUMENTFORMAT => l_output_format,
1002 P_PSIG_REQUESTER => l_requester,
1003 P_PSIG_SOURCE => l_source_application,
1004 P_EVENT_NAME => l_event_name,
1005 P_EVENT_KEY => l_event_key,
1006 P_WF_NID => NULL,
1007 P_ERROR => l_error,
1008 P_ERROR_MSG => l_error_msg);
1009 IF l_error is NOT NULL THEN
1010 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code during update document '||l_error);
1011 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg during update document '||l_error_msg);
1012 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Updated Document in eRecords Repository with Document ID '||l_document_id);
1013 RAISE ERECORD_CREATION_ERROR;
1014 END IF;
1015
1016 --Diagnostics Start
1017 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1018 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_REC_UPD_EVT');
1019 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
1020 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
1021 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
1022 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
1023 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1024 FALSE
1025 );
1026 end if;
1027 --Diagnostics End
1028
1029
1030 /* 20-OCT-2003: SRPURI : Modified attachment code to resolve the bug 3058816 */
1031
1032 /*
1033 -- Commented and added Copy Attachments and delete attachments
1034 update fnd_attached_documents
1035 set pk1_value = l_document_id
1036 where entity_name = EDR_CONSTANTS_GRP.g_erecord_entity_name
1037 and pk1_value = p_edr_event_id; */
1038 wf_log_pkg.string(1, 'EDR_PSIG_RULE.store_erecord','Before Moving attachments from Temp entity to eRecord '
1039 ||'from '||p_edr_event_id||' to '||l_document_id);
1040
1041 --Bug 4381237: Start
1042 --Change the call to EDR attachment API
1043 --fnd_attached_documents2_pkg.copy_attachments(
1044 edr_attachments_grp.copy_attachments
1045 --Bug 4381237: End
1046 (X_from_entity_name => G_TEMP_ENTITY_NAME,
1047 X_from_pk1_value => p_edr_event_id,
1048 X_from_pk2_value => null,
1049 X_from_pk3_value => null,
1050 X_from_pk4_value => null,
1051 X_from_pk5_value => null,
1052 X_to_entity_name => G_ENTITY_NAME,
1053 X_to_pk1_value => l_document_id,
1054 X_to_pk2_value => null,
1055 X_to_pk3_value => null,
1056 X_to_pk4_value => null,
1057 X_to_pk5_value => null,
1058 X_created_by => l_user_id,
1059 X_last_update_login => l_login_id,
1060 X_program_application_id => null,
1061 X_program_id => null,
1062 X_request_id => null,
1063 X_automatically_added_flag => 'N',
1064 X_from_category_id => null,
1065 X_to_category_id => null);
1066
1067 wf_log_pkg.string(3, 'EDR_PSIG_RULE.store_erecord','After Moving attachments from Temp entity to eRecord '
1068 ||'from '||p_edr_event_id||' to '||l_document_id);
1069
1070 wf_log_pkg.string(1, 'EDR_PSIG_RULE.store_erecord','Before Deleting attachments from Temp entity '
1071 ||'from '||p_edr_event_id);
1072
1073
1074 -- Bug 3863541 : Start
1075 -- Modified call to delete_attachments to support
1076 -- URL / Web page attachments
1077
1078 fnd_attached_documents2_pkg.delete_attachments
1079 (X_entity_name => G_TEMP_ENTITY_NAME,
1080 X_pk1_value => to_char(p_edr_event_id),
1081 X_pk2_value => null,
1082 X_pk3_value => null,
1083 X_pk4_value => null,
1084 X_pk5_value => null,
1085 --Bug 4381237: Start
1086 --pass this flag as Y so that the attachments to TEMPERECORD
1087 --are deleted. this is ok NOW because we are using our
1088 --own attachment api above. That would not bring the base
1089 --entities attachment into picture
1090 --X_delete_document_flag => 'N',
1091 X_delete_document_flag => 'Y',
1092 --Bug 4381237: End
1093 X_automatically_added_flag => 'N');
1094 --Bug 3863541 : End
1095
1096 wf_log_pkg.string(3, 'EDR_PSIG_RULE.store_erecord','After Deleting attachments from Temp entity '
1097 ||'from '||p_edr_event_id);
1098
1099
1100 --Diagnostics Start
1101 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1102 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_ATCH_EVT');
1103 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
1104 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
1105 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
1106 FND_MESSAGE.SET_TOKEN('ATTACHMENT_ID',p_edr_event_id);
1107 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
1108 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1109 FALSE
1110 );
1111 end if;
1112 --Diagnostics End
1113
1114
1115 /* 20-OCT-2003: srpuri : end */
1116 --ER # 2966866 start
1117 wf_event.AddParameterToList('#ERECORD_ID',
1118 l_document_id,
1119 x_event.Parameter_List);
1120 wf_log_pkg.string(1, 'EDR_PSIG_RULE.store_erecord','eRecord Id added to payload: '||l_document_id);
1121
1122 --ER # 2966866 end
1123 /* Add Document Parameters */
1124 l_params(1).param_name:='PSIG_USER_KEY_LABEL';
1125 l_params(1).param_value:=l_event_user_key_label;
1126 l_params(1).param_displayname:='Identifier label';
1127 l_params(2).param_name:='PSIG_USER_KEY_VALUE';
1128 l_params(2).param_value:=l_event_user_key_value;
1129 l_params(2).param_displayname:='Identifier value';
1130
1131 /* Style Sheet information */
1132 l_params(3).param_name:='TEXT_XSLNAME';
1133 l_params(3).param_value:=p_style_sheet;
1134 l_params(3).param_displayname:='Stylesheet';
1135 l_params(4).param_name:='TEXT_XSLVERSION';
1136 l_params(4).param_value:=p_style_sheet_ver;
1137 l_params(4).param_displayname:='Stylesheet Version';
1138
1139 --Bug 4150616: Start
1140 --Set the value of FORCE_ERECORD if it exists in the
1141 --doc params.
1142 if upper(l_force_erecord) in('Y','N') then
1143 l_params(5).param_name := EDR_CONSTANTS_GRP.G_FORCE_ERECORD;
1144 l_params(5).param_value := l_force_erecord;
1145 l_params(5).param_displayname := 'Force E-record';
1146
1147 --If FORCE_ERECORD_USED is set then set that parameter and its value
1148 --in the doc params.
1149 if l_force_erecord_used = 'Y' then
1150 l_params(6).param_name := EDR_CONSTANTS_GRP.G_FORCE_ERECORD_USED;
1151 l_params(6).param_value := l_force_erecord_used;
1152 l_params(6).param_displayname := 'E-record Creation Forced';
1153 end if;
1154 end if;
1155 --Bug 4150616: End
1156
1157 --Bug 4755255: Start
1158 l_params(5).param_name := '#ATTACHMENTS';
1159 l_params(5).param_value := 'FND:entity=ERECORD'||'&'||'pk1name=DOCUMENT_ID'||'&'||'pk1value='||l_document_id;
1160
1161 --Bug 4755255: End
1162
1163 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Posting Document Parameters');
1164
1165 EDR_PSIG.POSTDOCUMENTPARAMETER(P_DOCUMENT_ID => l_document_id,
1166 P_PARAMETERS => l_params,
1167 P_ERROR => l_error,
1168 P_ERROR_MSG => l_error_msg);
1169
1170 IF l_error is NOT NULL THEN
1171 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Code during posting doc params '||l_error);
1172 wf_log_pkg.string(4, 'EDR_PSIG_RULE.store_erecord','Error Msg during posting doc params '||l_error_msg);
1173 RAISE ERECORD_PARAM_ERROR;
1174 END IF;
1175
1176 --Diagnostics Start
1177 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1178 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_POST_EVT');
1179 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
1180 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
1181 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
1182 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
1183 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1184 FALSE
1185 );
1186 end if;
1187 --Diagnostics End
1188
1189 -- Bug Fix 3143107
1190 -- Modified unconditional Close document to Conditional Close
1191 -- and added Conditional Status Change Call where eSignature is Required
1192 --Bug 4150616: Start
1193 --If either ESIG_REQUIRED is set to 'N' or FORCE_ERECORD_USED has been set to 'Y' then close the document.
1194 IF p_esign_required = 'N' OR l_force_erecord_used = 'Y' THEN
1195 --Bug 4150616: End
1196 EDR_PSIG.closeDocument(P_DOCUMENT_ID => l_document_id,
1197 P_ERROR => l_error,
1198 P_ERROR_MSG => l_error_msg);
1199 wf_log_pkg.string(3, 'EDR_PSIG_RULE.store_erecord','Document Closed');
1200
1201 ELSIF p_esign_required = 'Y' THEN
1202 EDR_PSIG.CHANGEDOCUMENTSTATUS(P_DOCUMENT_ID => l_document_id,
1203 P_STATUS => 'ERROR',
1204 P_ERROR => L_ERROR,
1205 P_ERROR_MSG => l_error_msg);
1206 wf_log_pkg.string(3, 'EDR_PSIG_RULE.store_erecord','Changed Document Status to an Intermediate Status');
1207 END IF;
1208 -- END Bug Fix 3143107
1209
1210 -- If everything went fine, commiting the ERECORD Contents by commiting this autonomous transaction
1211 COMMIT;
1212
1213 --Diagnostics Start
1214 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1215 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_ST_ERECORD_STATUS_EVT');
1216 FND_MESSAGE.SET_TOKEN('EVENT_NAME',l_event_name);
1217 FND_MESSAGE.SET_TOKEN('EVENT_KEY',l_event_key);
1218 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
1219 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
1220 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1221 FALSE
1222 );
1223 end if;
1224 --Diagnostics End
1225
1226 wf_log_pkg.string(3, 'EDR_PSIG_RULE.store_erecord','Store_erecord completed successfully');
1227
1228 EXCEPTION
1229 when ERECORD_GENERATION_ERROR then
1230 ROLLBACK;
1231 Wf_Core.Context('EDR_PSIG_RULE', 'STORE_ERECORD', x_event.getEventName(), p_subscription_guid);
1232 wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR');
1233 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1234 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
1235 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
1236 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PSIG_RULE');
1237
1238 --Diagnostics Start
1239 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1240 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1241 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1242 FALSE
1243 );
1244 end if;
1245 --Diagnostics End
1246
1247 APP_EXCEPTION.RAISE_EXCEPTION;
1248 when ERECORD_CREATION_ERROR then
1249 ROLLBACK;
1250 Wf_Core.Context('EDR_PSIG_RULE', 'STORE_ERECORD', x_event.getEventName(), p_subscription_guid);
1251 wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR');
1252 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1253 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
1254 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
1255 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','STORE_ERECORD');
1256
1257 --Diagnostics Start
1258 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1259 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1260 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1261 FALSE
1262 );
1263 end if;
1264 --Diagnostics End
1265
1266 APP_EXCEPTION.RAISE_EXCEPTION;
1267 when ERECORD_PARAM_ERROR then
1268 ROLLBACK;
1269 Wf_Core.Context('EDR_PSIG_RULE', 'STORE_ERECORD', x_event.getEventName(), p_subscription_guid);
1270 wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR');
1271 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1272 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
1273 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
1274 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','STORE_ERECORD');
1275 --Diagnostics Start
1276 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1277 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1278 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1279 FALSE
1280 );
1281 end if;
1282 --Diagnostics End
1283
1284 APP_EXCEPTION.RAISE_EXCEPTION;
1285
1286 -- Bug 3170251 : Start - Added following new exceptions for error handling
1287 when ERECORD_GEN_XSLFOMISS_ERR then
1288 ROLLBACK;
1289 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', x_event.getEventName(), p_subscription_guid);
1290 Wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR XSLFO MISSING');
1291 FND_MESSAGE.SET_NAME('EDR','EDR_EREC_XSLFO_MISSING_ERR');
1292 fnd_message.set_token( 'TEMPLATE_NAME',p_style_sheet);
1293 fnd_message.set_token( 'TEMPLATE_VER',p_style_sheet_ver);
1294 --Diagnostics Start
1295 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1296 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1297 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1298 FALSE
1299 );
1300 end if;
1301 --Diagnostics End
1302 APP_EXCEPTION.RAISE_EXCEPTION;
1303
1304 when ERECORD_GEN_FATALERR then
1305 ROLLBACK;
1306 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', x_event.getEventName(), p_subscription_guid);
1307 wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR');
1308
1309 wf_log_pkg.string(5, 'EDR_PSIG_RULE.store_erecord','There is a fatal error during e-record creation using XDO API');
1310
1311 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1312 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
1313 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
1314 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','STORE_ERECORD');
1315
1316 --Diagnostics Start
1317 if (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1318 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
1319 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1320 FALSE
1321 );
1322 end if;
1323 --Diagnostics End
1324
1325 APP_EXCEPTION.RAISE_EXCEPTION;
1326 when ERECORD_GEN_UNKNWN_ERR then
1327 ROLLBACK;
1328 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', x_event.getEventName(), p_subscription_guid);
1329 wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR');
1330 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1331 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
1332 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
1333 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','STORE_ERECORD');
1334
1335 --Diagnostics Start
1336 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1337 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1338 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1339 FALSE
1340 );
1341 end if;
1342 --Diagnostics End
1343
1344 APP_EXCEPTION.RAISE_EXCEPTION;
1345 when ERECORD_GEN_XDOERR then
1346 ROLLBACK;
1347 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', x_event.getEventName(), p_subscription_guid);
1348 wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR IN XDO');
1349 FND_MESSAGE.SET_NAME('EDR','EDR_EREC_XDO_ERR');
1350 fnd_message.set_token( 'TEMPLATE_NAME',p_style_sheet);
1351 fnd_message.set_token( 'TEMPLATE_VER',p_style_sheet_ver);
1352
1353 --Diagnostics Start
1354 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1355 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1356 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1357 FALSE
1358 );
1359 end if;
1360 --Diagnostics End
1361
1362 APP_EXCEPTION.RAISE_EXCEPTION;
1363 when ERECORD_UNKWN_TMPL_TYPE then
1364 ROLLBACK;
1365 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', x_event.getEventName(), p_subscription_guid);
1366 wf_event.setErrorInfo(x_event,SQLERRM || 'Unknown Template Type'|| l_style_sheet_type);
1367 FND_MESSAGE.SET_NAME('EDR','EDR_EREC_TMPL_TYPE_ERR');
1368 fnd_message.set_token( 'TEMPLATE_NAME',p_style_sheet);
1369 fnd_message.set_token( 'TEMPLATE_VER',p_style_sheet_ver);
1370
1371 --Diagnostics Start
1372 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1373 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1374 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1375 FALSE
1376 );
1377 end if;
1378 --Diagnostics End
1379
1380 APP_EXCEPTION.RAISE_EXCEPTION;
1381
1382 when ERECORD_GEN_JSPMSNG_ERR then
1383 ROLLBACK;
1384 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', x_event.getEventName(), p_subscription_guid);
1385 Wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR JSP MISSING');
1386 FND_MESSAGE.SET_NAME('EDR','EDR_EREC_JSP_MISSING_ERR');
1387
1388 --Diagnostics Start
1389 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1390 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1391 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1392 FALSE
1393 );
1394 end if;
1395 --Diagnostics End
1396
1397 APP_EXCEPTION.RAISE_EXCEPTION;
1398 when ERECORD_GEN_JSPRQFAIL_ERR then
1399 ROLLBACK;
1400 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', x_event.getEventName(), p_subscription_guid);
1401 Wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR JSP UTL_HTTP REQUEST FAILED');
1402 FND_MESSAGE.SET_NAME('EDR','EDR_EREC_JSPREQFAIL_ERR');
1403
1404 --Diagnostics Start
1405 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1406 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1407 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1408 FALSE
1409 );
1410 end if;
1411 --Diagnostics End
1412
1413 APP_EXCEPTION.RAISE_EXCEPTION;
1414 -- Bug 3170251 : End
1415
1416 -- Bug 3390571 : Start
1417 when ERECORD_GEN_XSLT_ERR then
1418 ROLLBACK;
1419 wf_core.context('EDR_PSIG_RULE', 'PSIG_RULE', x_event.getEventName(), p_subscription_guid);
1420 Wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR : XSLT Transformation error');
1421 FND_MESSAGE.SET_NAME('EDR','EDR_EREC_XSLTTRANS_ERR');
1422 fnd_message.set_token( 'TEMPLATE_NAME',p_style_sheet);
1423 fnd_message.set_token( 'TEMPLATE_VER',p_style_sheet_ver);
1424 --Diagnostics Start
1425 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1426 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1427 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1428 FALSE
1429 );
1430 end if;
1431 --Diagnostics End
1432 APP_EXCEPTION.RAISE_EXCEPTION;
1433
1434 --Bug 3390571: End
1435
1436 --Bug 2637353: Start
1437 WHEN MSCA_TMPL_ERROR THEN
1438 FND_MESSAGE.SET_NAME('EDR','EDR_MSCA_TMPL_ERR');
1439 --Diagnostics Start
1440 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1441 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
1442 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1443 FALSE
1444 );
1445 end if;
1446 APP_EXCEPTION.RAISE_EXCEPTION;
1447 --Diagnostics End
1448 --Bug 2637353: End
1449
1450
1451 when OTHERS then
1452 ROLLBACK;
1453 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', x_event.getEventName(), p_subscription_guid);
1454 wf_event.setErrorInfo(x_event,SQLERRM || 'ERROR');
1455 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1456 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
1457 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
1458 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','STORE_ERECORD');
1459 --Diagnostics Start
1460 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1461 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
1462 'edr.plsql.EDR_PSIG_RULE.STORE_ERECORD',
1463 FALSE
1464 );
1465 end if;
1466 --Diagnostics End
1467
1468
1469 APP_EXCEPTION.RAISE_EXCEPTION;
1470 END STORE_ERECORD;
1471
1472 -------------------------------------------------------------------------------------------------------------------------------------
1473
1474 /* Signature Rule function
1475 IN:
1476 p_subscription_guid GUID of Subscription to be processed
1477 p_event Event to be processes
1478 */
1479
1480
1481 function psig_rule(p_subscription_guid in raw,
1482 p_event in out NOCOPY wf_event_t)
1483
1484 return varchar2
1485
1486 is
1487
1488 L_EVENT_NAME varchar2(240);
1489 L_EVENT_KEY varchar2(240);
1490 --sswa addition
1491 L_EVENT_DATA CLOB;
1492 l_erecord_data CLOB;
1493 l_application_id NUMBER;
1494 l_application_code varchar2(32);
1495
1496 -- using EDR approvers table
1497 approverList EDR_UTILITIES.approvers_Table;
1498
1499 upperLimit integer;
1500 i integer;
1501 l_esign_required varchar2(1);
1502 l_eRecord_required varchar2(1);
1503 l_rule_esign_required varchar2(1);
1504 l_rule_eRecord_required varchar2(1);
1505 l_XML_DOCUMENT CLOB;
1506 l_EREC_OUTPUT CLOB;
1507 lparamlist wf_parameter_list_t;
1508 l_STYLE_SHEET varchar2(240);
1509 l_STYLE_SHEET_VER varchar2(240);
1510 l_STYLE_SHEET_repository varchar2(240);
1511 l_STYLE_SHEET_type varchar2(240);
1512 l_temp_style_sheet varchar2(240);
1513 l_temp_style_sheet_ver varchar2(240);
1514
1515 l_POS number;
1516
1517 l_return_status varchar2(10);
1518 l_xml_map_code varchar2(240);
1519 l_audit_group varchar2(240);
1520 l_ame_transaction_type varchar2(240);
1521 l_edr_Event_id number;
1522 l_event_user_key_label VARCHAR2(240);
1523 l_event_user_key_VALUE VARCHAR2(240);
1524 l_transaction_audit_id NUMBER;
1525 l_document_id number;
1526 l_error number;
1527 l_error_msg varchar2(4000);
1528 l_params EDR_PSIG.params_table;
1529 l_source_application varchar2(240);
1530 l_requester varchar2(240);
1531 l_transaction_name varchar2(240);
1532
1533 --Using EDR idList and stringList types
1534 l_ruleids edr_utilities.id_List;
1535 l_rulenames edr_utilities.string_List;
1536
1537 l_application_name varchar2(240);
1538
1539 l_rulevalues EDR_STANDARD.ameruleinputvalues;
1540 l_wftype varchar2(240);
1541 l_wfprocess varchar2(240);
1542 l_no_enabled_eres_sub NUMBER;
1543 l_error_event varchar2(240);
1544
1545 user_data varchar2(2000);
1546 user_data_placeholder varchar2(40);
1547 placeholder_position integer;
1548
1549 MULTIPLE_ERES_SUBSCRIPTIONS EXCEPTION;
1550 NO_ENABLED_ERES_SUBSCRIPTION EXCEPTION;
1551 NO_ERES_SUBSCRIPTION EXCEPTION;
1552 --Bug 5724159:Start
1553 --Define a new Exception to be raised when the signature mode
1554 -- is set to SHORT ,adhoc signer capability is set to ALL or ADHOC
1555 --and the number of approvers is 0.
1556 NO_SIGNERS_DEF_FOR_LITE_MODE EXCEPTION;
1557 -- Bug 5724159 :End
1558
1559 --parameters for inter event
1560 l_parent_event varchar2(240) ;
1561 l_parent_key varchar2(2000) ;
1562 l_parent_erecord_id number ;
1563
1564 l_inter_event_mode varchar2(1);
1565 l_relationship varchar2(200);
1566 l_trans_value_only varchar2(1);
1567 l_parent_sub_guid raw(16);
1568 l_rule_id number;
1569
1570 --Bug 3161859 : start
1571 l_change_signer varchar2(32);
1572 l_change_signer_defined varchar2(1);
1573 --Bug 3161859 : end
1574
1575 -- Bug 4197656 : Start
1576 l_change_signer_evaluate varchar2(1);
1577 -- Bug 4197656 : End
1578
1579
1580 l_debug_level number;
1581 l_ret_code pls_integer;
1582 l_log_file varchar2(2000);
1583 l_errbuf varchar2(2000);
1584
1585 -- Exceptions
1586 PAGE_FLOW_FAILED EXCEPTION;
1587 BAD_RELATIONSHIP_ERROR EXCEPTION;
1588 NO_STYLE_SHEET_FOUND EXCEPTION;
1589 ECX_XML_TO_XML_ERROR EXCEPTION;
1590
1591 --Bug 3893101: Start
1592 l_transform_xml varchar2(1);
1593 TRANSFORM_XML_VAR_ERROR EXCEPTION;
1594 --Bug 3893101: End
1595
1596 l_attachment_string_details VARCHAR2(2000);
1597
1598 -- Bug 3567868 Start
1599 l_source_application_type VARCHAR2(10);
1600 -- Bug 3567868 End
1601 -- Bug 3567868 End
1602 -- Bug 3761813 : start
1603 l_redline_required varchar2(30);
1604 l_snapstatus boolean;
1605 -- -- Bug 3761813 : end
1606
1607 --Bug 4122622: Start
1608 --This variable would hold the comma separated string of child e-record IDs
1609 --set on the event parameters.
1610 l_child_erecord_ids VARCHAR2(4000);
1611
1612 --These variables would indicate whether the parent e-record Id and child e-record
1613 --Id have been set.
1614 l_parent_erecord_id_set boolean;
1615 l_child_erecord_id_set boolean;
1616 l_child_erecord_ids_set boolean;
1617 l_url VARCHAR2(500);
1618 --Bug 4122622: End
1619
1620 --Bug 4150616: Start
1621 --This variable would be used to hold the value of the FORCE_ERECORD subscription parameter.
1622 L_FORCE_ERECORD VARCHAR2(10);
1623 --The ERES profile option value will be stored in this variable.
1624 L_ERES_PROFILE_VALUE VARCHAR2(1);
1625 --Bug 4150616: End
1626
1627 --Bug 4160412: Start
1628 L_TEMP_SIGNATURE_MODE VARCHAR2(10);
1629 L_SIGNATURE_MODE VARCHAR2(10);
1630 L_APPROVER_COUNT VARCHAR2(128);
1631 L_APPROVER_LIST VARCHAR2(4000);
1632 L_ARE_APPROVERS_VALID VARCHAR2(1);
1633 L_INVALID_APPROVERS VARCHAR2(4000);
1634 L_CUSTOM_APPROVER_LIST EDR_UTILITIES.APPROVERS_TABLE;
1635 L_APPROVER_COUNT_VALUE NUMBER;
1636 L_LIST_COUNT NUMBER;
1637 L_TEMP_COUNT NUMBER;
1638 L_LITE_MODE BOOLEAN;
1639 L_REPEATING_APPROVERS BOOLEAN;
1640 L_EINITIALS_DEFER_MODE VARCHAR2(1);
1641 L_ERES_DEFER_MODE VARCHAR2(1);
1642 APPROVING_USERS_PARAMS_ERR EXCEPTION;
1643 INVALID_APPROVING_USERS EXCEPTION;
1644 REPEATING_APPROVERS_ERR EXCEPTION;
1645 INVALID_APPR_COUNT_ERR EXCEPTION;
1646 INVALID_APPR_COUNT_VALUE_ERR EXCEPTION;
1647 --Bug 4160412: End
1648
1649 --Bug 3960236: Start
1650 --This variable would be used to hold the value of the
1651 --call back API passed by product teams in XML_GENERATION_API parameter to generate XML
1652 L_XML_GENERATION_API VARCHAR2(240);
1653 L_SQL_STR varchar2(4000);
1654 EMPTY_XML_DOCUMENT EXCEPTION;
1655 API_EXECUTION_ERROR EXCEPTION;
1656 --Bug 3960236: End
1657
1658 --Bug 4577122: Start
1659
1660 l_valid_regime varchar2(1);
1661 l_voting_regime varchar2(1);
1662 l_temp_value VARCHAR2(4000);
1663
1664 INVALID_FIRST_VOTER_WINS_SETUP EXCEPTION;
1665 --Bug 4577122: End
1666
1667 BEGIN
1668
1669 --Bug 4122622: Start
1670 l_parent_erecord_id_set := false;
1671 l_child_erecord_id_set := false;
1672 l_child_erecord_ids_set := false;
1673 --Bug 4122622: End
1674
1675
1676 --Bug 4074173 : start
1677 l_esign_required :='N';
1678 l_eRecord_required :='N';
1679
1680 l_STYLE_SHEET_repository := 'ISIGN';
1681 user_data_placeholder := 'EDR_USER_DATA_xxYG%7#@(765';
1682
1683 l_parent_event := EDR_CONSTANTS_GRP.g_default_char_param_value;
1684 l_parent_key := EDR_CONSTANTS_GRP.g_default_char_param_value;
1685 l_parent_erecord_id := EDR_CONSTANTS_GRP.g_default_num_param_value;
1686
1687 l_change_signer :='ALL';
1688 l_change_signer_defined :='N';
1689
1690 -- Bug 4197656 : Start
1691 l_change_signer_evaluate := 'N';
1692 -- Bug 4197656 : End
1693
1694 l_debug_level :=6;
1695
1696 --Bug 4074173 : end
1697
1698 --Bug 4150616: Start
1699 --We need to fetch the subscrpition parameters irrespective of whether profile option
1700 --is switched on.
1701 -- Bug 5639849: Starts
1702 -- Instead of loading all subscription parameters, just load the following
1703 -- variables. 1. EDR_AME_TRANSACTION_TYPE 2. EDR_XML_MAP_CODE 3. FORCE_ERECORD
1704 -- All others either are custom variables or interevent speficand those are
1705 -- not required to load at this point.
1706 -- srpuri: We still need to copy parameters whatever workflow can copy.
1707 l_return_status := wf_rule.setParametersIntoParameterList(p_subscription_guid,p_event);
1708 l_temp_value := EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_AME_TRANSACTION_TYPE',p_subscription_guid);
1709 if(l_temp_value is not null) then
1710 wf_event.AddParameterToList('EDR_AME_TRANSACTION_TYPE',
1711 l_temp_value,
1712 p_event.Parameter_List);
1713 end if;
1714 l_temp_value := EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_XML_MAP_CODE',p_subscription_guid);
1715 if(l_temp_value is not null) then
1716 wf_event.AddParameterToList('EDR_XML_MAP_CODE',
1717 l_temp_value,
1718 p_event.Parameter_List);
1719 end if;
1720 l_temp_value := EDR_INDEXED_XML_UTIL.GET_WF_PARAMS(EDR_CONSTANTS_GRP.G_FORCE_ERECORD,p_subscription_guid);
1721
1722 if(l_temp_value is not null) then
1723 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_FORCE_ERECORD,
1724 l_temp_value,
1725 p_event.Parameter_List);
1726 end if;
1727 -- Bug 5639849: Ends
1728
1729 --Fetch the value of the ERES profile option.
1730 l_eres_profile_value := fnd_profile.value('EDR_ERES_ENABLED');
1731
1732 --Fetch the value of FORCE_ERECORD.
1733 l_force_erecord := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_FORCE_ERECORD,p_event.Parameter_List);
1734
1735 --Convert it to upper case for ease in verification later in the code.
1736 l_force_erecord := upper(l_force_erecord);
1737 --Bug 4150616: End
1738 --Bug 5891879: Start
1739 --Read the source application type
1740 l_source_application_type := wf_event.getValueForParameter('#WF_SOURCE_APPLICATION_TYPE',p_event.Parameter_List);
1741 --Bug 5891879 : End
1742 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','Start of the rule function');
1743
1744 --Bug 4150616: Start
1745 IF l_eres_profile_value = 'Y' OR l_force_erecord = 'Y' THEN
1746 --Bug 4150616: End
1747
1748 --Bug 4160412: Start
1749 L_LITE_MODE := false;
1750 --Bug 4160412: End
1751
1752 --IF l_return_status='SUCCESS' THEN
1753 /* Check for User Defined Parameters. We only require AME parameter for now */
1754 l_ame_transaction_type := NVL(wf_event.getValueForParameter('EDR_AME_TRANSACTION_TYPE',p_event.Parameter_List),
1755 P_EVENT.getEventName());
1756 -- END IF;
1757
1758 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','AME transaction Type :'||l_ame_transaction_type );
1759
1760 --Diagnostics Start
1761 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1762 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_START_EVT');
1763 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
1764 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
1765 FND_MESSAGE.SET_TOKEN('AME_TYPE',l_ame_transaction_type);
1766 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
1767 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
1768 FALSE
1769 );
1770 end if;
1771 --Diagnostics End
1772
1773 --ER # 2966866 start
1774
1775 --get the PARENT_EVENT_NAME parameter
1776 l_parent_event := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.g_parent_event_name ,p_event.Parameter_List);
1777 wf_log_pkg.string(1, 'EDR_PSIG_rule.psig_rule','Parent Event Name:'||l_parent_event);
1778
1779 --get the PARENT_EVENT_KEY parameter
1780 l_parent_key := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.g_parent_event_key,p_event.Parameter_List);
1781
1782 wf_log_pkg.string(1, 'EDR_PSIG_rule.psig_rule','Parent Event Key:'||l_parent_key);
1783
1784 --get the PARENT_ERECORD_ID parameter
1785 l_parent_erecord_id := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.g_parent_erecord_id,p_event.Parameter_List);
1786
1787 wf_log_pkg.string(1, 'EDR_PSIG_rule.psig_rule','Parent eRecord ID:'||l_parent_erecord_id);
1788
1789 --find out if the requisite parameters are passed in the payload so as
1790 --to make it an inter event. either the parent event name AND parent event
1791 --key have to be passed in the payload OR all three, to make this an inter
1792 --event context
1793
1794 IF (l_parent_event <> EDR_CONSTANTS_GRP.g_default_char_param_value
1795 AND l_parent_key <> EDR_CONSTANTS_GRP.g_default_char_param_value )THEN
1796
1797 l_inter_event_mode := 'Y';
1798
1799 ELSIF (l_parent_erecord_id <> EDR_CONSTANTS_GRP.g_default_num_param_value) THEN
1800
1801 l_inter_event_mode := 'Y';
1802
1803 ELSE
1804
1805 l_inter_event_mode := 'N';
1806
1807 END IF;
1808
1809 wf_log_pkg.string(1, 'EDR_PSIG_rule.psig_rule','Inter Event Mode: '||l_inter_event_mode);
1810
1811 if (l_inter_event_mode = 'Y') then
1812 --Diagnostics Start
1813 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1814 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_INTER_EVENT_EVT');
1815 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
1816 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
1817 FND_MESSAGE.SET_TOKEN('PARENT_NAME',nvl(l_parent_event,'NULL'));
1818 FND_MESSAGE.SET_TOKEN('PARENT_KEY',nvl(l_parent_key,'NULL'));
1819 FND_MESSAGE.SET_TOKEN('PARENT_EREC_ID',l_parent_erecord_id);
1820 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
1821 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
1822 FALSE
1823 );
1824 end if;
1825 --Diagnostics End
1826
1827
1828 --
1829 -- Start Bug Fix 3355468
1830 -- to resolve this issue we need to modify following code
1831 -- to do following
1832 -- 1. Verify how many ERES subscriptions are available at parent event level
1833 -- 2. If only one subscription is available then do nothing.
1834 -- 3. If more than one ERES subscriptions are enabled then raise error.
1835 -- 4. If more than one ERES subscription is present at parent level and all are disabled raise ERROR
1836 -- 5. if more than one ERES subscription is present at parent level but only one ERES subscription is
1837 -- enabled then do nothing.
1838 -- find out how many ERES subscriptions are
1839 -- present for the parent event
1840 --
1841
1842 select count(*) INTO l_no_enabled_eres_sub
1843 from wf_events a, wf_event_subscriptions b
1844 where a.GUID = b.EVENT_FILTER_GUID
1845 and a.name = l_parent_event
1846 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
1847 --Bug No 4912782- Start
1848 and b.source_type = 'LOCAL'
1849 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
1850 --Bug No 4912782- End
1851
1852 IF l_no_enabled_eres_sub > 1 then
1853
1854 --
1855 -- Start Bug Fix 3078516
1856 -- Verify is more than one active ERES subscriptions are present
1857 -- for parent event. if more than one ERES subscription is ENABLED
1858 -- Raise Multiple ERES Subscriptions error
1859 --
1860 select count(*) INTO l_no_enabled_eres_sub
1861 from wf_events a, wf_event_subscriptions b
1862 where a.GUID = b.EVENT_FILTER_GUID
1863 and a.name = l_parent_event
1864 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
1865 and b.STATUS = 'ENABLED'
1866 --Bug No 4912782- Start
1867 and b.source_type = 'LOCAL'
1868 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
1869 --Bug No 4912782- End
1870
1871 IF l_no_enabled_eres_sub > 1 THEN
1872 l_error_event := l_parent_event;
1873 RAISE MULTIPLE_ERES_SUBSCRIPTIONS;
1874 ELSIF l_no_enabled_eres_sub = 0 THEN
1875 l_error_event := l_parent_event;
1876 RAISE NO_ENABLED_ERES_SUBSCRIPTION;
1877 END IF;
1878
1879 ELSIF l_no_enabled_eres_sub = 0 THEN
1880 l_error_event := l_parent_event;
1881 RAISE NO_ERES_SUBSCRIPTION;
1882 END IF;
1883 --
1884 -- end of bug fix 3355468
1885 --
1886
1887 --get the guid of the parent event
1888 l_parent_sub_guid := EDR_ERES_EVENT_PVT.GET_SUBSCRIPTION_GUID(p_event_name => l_parent_event);
1889
1890 l_relationship := upper(EDR_INDEXED_XML_UTIL.GET_WF_PARAMS(p_event.event_name,l_parent_sub_guid));
1891
1892 --Diagnostics Start
1893 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1894 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_INTEREVENT_REL_EVT');
1895 FND_MESSAGE.SET_TOKEN('CHILD_NAME',p_event.getEventName());
1896 FND_MESSAGE.SET_TOKEN('PARENT_EVENT',l_parent_event);
1897 FND_MESSAGE.SET_TOKEN('RELATION',l_relationship);
1898 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
1899 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
1900 FALSE
1901 );
1902 end if;
1903 --Diagnostics End
1904
1905 wf_log_pkg.string(1, 'EDR_PSIG_rule.psig_rule','Relationship between parent and child:'||l_relationship);
1906
1907 --l_relationship can have three possible values
1908 --EVALUATE_NORMAL, IGNORE_SIGNATURE, ERECORD_ONLY
1909 end if;
1910 --ER # 2966866 end
1911
1912 --
1913 -- Verify is more than one active ERES subscriptions are present
1914 -- for current event. if more than one ERES subscription is ENABLED
1915 -- Raise Multiple ERES Subscriptions error
1916 --
1917 select count(*) INTO l_no_enabled_eres_sub
1918 from wf_events a, wf_event_subscriptions b
1919 where a.GUID = b.EVENT_FILTER_GUID
1920 and a.name = p_event.event_name
1921 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
1922 and b.STATUS = 'ENABLED'
1923 --Bug No 4912782- Start
1924 and b.source_type = 'LOCAL'
1925 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
1926 --Bug No 4912782- End
1927
1928 IF l_no_enabled_eres_sub > 1 THEN
1929 l_error_event := p_event.event_name;
1930 RAISE MULTIPLE_ERES_SUBSCRIPTIONS;
1931 END IF;
1932
1933 /* AME Processing */
1934 /* Select APPLICATION_ID of the Event. This is required by AME. Assumption made here
1935 is OWNER_TAG will always be set to application Short Name */
1936
1937 SELECT application_id,APPLICATION_SHORT_NAME into l_application_id,l_application_code
1938 FROM FND_APPLICATION A, WF_EVENTS B
1939 WHERE A.APPLICATION_SHORT_NAME = B.OWNER_TAG
1940 AND B.NAME=P_EVENT.getEventName( );
1941
1942 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Application_id :'||l_application_id );
1943 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','event key :'||P_EVENT.getEventKey( ));
1944 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Trans Type :'||NVL(l_ame_transaction_type,P_EVENT.getEventName( )) );
1945
1946 /* AME Enhancement Code. Determine if singature is need or not and also get approvers */
1947 -- if the inter event mode is on and the relationship is ERECORD_ONLY then
1948 -- also we need to go to AME to get the transaction type
1949 -- the transaction type would help in determining the stylesheet to be applied
1950 -- to create the erecord
1951
1952 --ER # 2966866 start
1953
1954 --Bug 3667036 : Start
1955
1956 --Obtain event details
1957 l_event_data:=p_event.getEventData();
1958 l_event_name:=p_event.getEventName();
1959 l_event_key:=p_event.getEventKey();
1960 l_xml_map_code := NVL(wf_event.getValueForParameter('EDR_XML_MAP_CODE',p_event.Parameter_List),
1961 P_EVENT.getEventName( ));
1962
1963
1964 --Insert into temp table. This is done because a handle to a CLOB created through JDBC
1965 --can be obtained only if it stored in a table and the table queried.
1966 insert into EDR_FINAL_XML_GT(event_name,event_key,event_xml)
1967 values(l_event_name,l_event_key,l_event_data);
1968
1969 select event_xml
1970 into l_event_data
1971 from EDR_FINAL_XML_GT;
1972
1973 if dbms_lob.getlength(l_event_data)>0 then
1974 --Diagnostics Start
1975 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1976 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_DATA_SOURCE_EVT');
1977 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
1978 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
1979 FND_MESSAGE.SET_TOKEN('DATA_SOURCE','VIEW OBJECT');
1980 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
1981 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
1982 FALSE
1983 );
1984 end if;
1985 --Diagnostics End
1986
1987 --insert the erecord id into the temp table
1988 --This would be picked up by the procedures called by the XML Map for attacments
1989 --Delete the global temp table before inserting a row
1990 delete edr_erecord_id_temp;
1991
1992 SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL;
1993 INSERT into edr_erecord_id_temp (document_id) values (l_edr_Event_id);
1994 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','eRecord id: '||l_edr_Event_id||' will be sent to ECX');
1995
1996 --Bug 3893101: Start
1997 --Obtain the value of transform xml parameter.
1998 l_transform_xml := NVL(wf_event.getValueForParameter('TRANSFORM_XML',p_event.Parameter_List),
1999 'Y');
2000 --If it is set to 'Y' then generate the XML as required.
2001 if l_transform_xml = 'Y' then
2002 --Generate the customised Erecord using XML Gateway
2003 ecx_inbound_trig.processXML
2004 (i_map_code => l_xml_map_code,
2005 i_payload => l_event_data,
2006 i_debug_level => l_debug_level,
2007 i_ret_code => l_ret_code,
2008 i_errbuf => l_errbuf,
2009 i_log_file => l_log_file,
2010 o_payload => l_erecord_data
2011 );
2012
2013 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','ecx inbound trig called');
2014
2015 --If it is set to 'N' then do not perform the XML to XML transformation.
2016 --Take the source XML as the target itself.
2017 elsif l_transform_xml = 'N' then
2018 --We need to recreate the event CLOB in another XML CLOB as
2019 --the CLOB obtained from the event definition cannot be modified.
2020 DBMS_LOB.CREATETEMPORARY(l_erecord_data, TRUE, DBMS_LOB.SESSION);
2021 DBMS_LOB.COPY(l_erecord_data,l_event_data,DBMS_LOB.GETLENGTH(l_event_data));
2022 l_attachment_string_details := wf_event.getValueForParameter('EDR_PSIG_ATTACHMENT',p_event.Parameter_List);
2023 if l_attachment_string_details is not null then
2024 EDR_ATTACHMENTS_GRP.ADD_ERP_ATTACH(p_attachment_string => l_attachment_string_details);
2025 end if;
2026
2027 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','no ecx transformation required');
2028 else
2029 raise TRANSFORM_XML_VAR_ERROR;
2030 end if;
2031 --Bug 3893101: End
2032
2033 --If the return code from ECX is a value other than 0 then
2034 --an error has occurred
2035 if(l_transform_xml = 'Y' and l_ret_code <> 0) then
2036 raise ECX_XML_TO_XML_ERROR;
2037 end if;
2038
2039 --Bug 3893101: Start
2040 --Check for transform xml parameter before writing into diagnostics.
2041 if l_transform_xml = 'Y' then
2042
2043 --Ensure that the ECX error handling is put here.
2044 --Diagnostics Start
2045 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2046 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_ECX_GEN_EVT');
2047 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2048 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2049 FND_MESSAGE.SET_TOKEN('XML_MAP_CODE',l_xml_map_code);
2050 FND_MESSAGE.SET_TOKEN('TRANS','XML TO XML');
2051 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2052 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2053 FALSE
2054 );
2055 end if;
2056 --Diagnostics End
2057 else
2058 --Diagnostics Start
2059 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2060 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_NO_XML_TRANS_EVT');
2061 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2062 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2063 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2064 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2065 FALSE
2066 );
2067 end if;
2068 --Diagnostics End
2069
2070 end if;
2071 --Bug 3893101: End
2072
2073 --update the global temp table with the final xml obtained from ECX
2074 --this would be the xml payload of the e-record
2075 update EDR_FINAL_XML_GT
2076 set event_xml = l_erecord_data
2077 where event_name = l_event_name
2078 and event_key = l_event_key;
2079
2080 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','updated edr_final_xml_gt with the final xml');
2081 else
2082 --Diagnostics Start
2083 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2084 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_DATA_SOURCE_EVT');
2085 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2086 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2087 FND_MESSAGE.SET_TOKEN('DATA_SOURCE','DATABASE');
2088 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2089 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2090 FALSE
2091 );
2092 end if;
2093 --Diagnostics End
2094
2095 end if;
2096 --Bug 3667036 : End
2097
2098
2099 -- Bug 4126932 : Start
2100 -- No need to call AME API in case of Inter Event and relationship is E-Record
2101
2102 -- Bug 2674799 : start
2103 if (l_inter_event_mode = 'Y' and l_relationship = EDR_CONSTANTS_GRP.g_erecord_only) then
2104 l_trans_value_only := 'Y';
2105 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','no need to find out approvers form ame as interevent relationship forces erecord_only');
2106 else
2107 EDR_UTILITIES.GET_APPROVERS(P_APPLICATION_ID => l_application_id,
2108 P_TRANSACTION_ID => p_event.getEventKey(),
2109 P_TRANSACTION_TYPE => NVL(l_ame_transaction_type,p_event.getEventName()),
2110 X_approvers => approverList,
2111 X_rule_Ids => l_ruleids,
2112 X_rule_Descriptions => l_rulenames);
2113 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','called ame to get the approvers');
2114 END IF ;
2115 -- Bug 2674799: end
2116
2117 -- Bug 4126932 : END
2118
2119 --clean up temp table
2120 delete from EDR_FINAL_XML_GT where event_name = l_event_name and event_key=l_event_key;
2121
2122 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','AME API Called. Total Approver '||approverlist.count);
2123
2124 -- get the Application Name
2125 select ltrim(rtrim(application_name)) into l_application_name
2126 from ame_Calling_Apps
2127 where FND_APPLICATION_ID=l_application_id and
2128 TRANSACTION_TYPE_ID=NVL(l_ame_transaction_type,P_EVENT.getEventName( ))
2129 --Bug 4652277: Start
2130 --and end_Date is null;
2131 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
2132 --Bug 4652277: End
2133
2134 --Bug fix 3168963 when eRecord_Only is subscription parameter we should not
2135 --look for number of approvers
2136 -- Bug 3567868 :Start
2137 --Bug 5724159 :Start
2138 --Added an extra 'AND' condtion to ensure that no processing takes place if no rules are defined.
2139 IF ((approverlist.count = 0)
2140 and (nvl(l_relationship,'EDR') <> EDR_CONSTANTS_GRP.g_erecord_only) and l_ruleids.count >0 )then
2141
2142 --Bug 5724159 :End
2143 --Diagnostics Start
2144 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2145 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_NO_APPROVERS_EVT');
2146 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2147 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2148 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2149 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2150 FALSE
2151 );
2152 end if;
2153 --Diagnostics End
2154 l_source_application_type := wf_event.getValueForParameter('#WF_SOURCE_APPLICATION_TYPE',p_event.Parameter_List);
2155 -- Bug 3214495 : Start
2156
2157 EDR_STANDARD.GET_AMERULE_INPUT_VARIABLES
2158 (transactiontypeid => NVL(l_ame_transaction_type,P_EVENT.getEventName()),
2159 ameruleid => -1,
2160 amerulename => null,
2161 ameruleinputvalues => l_rulevalues);
2162
2163 --Bug 3214495 : End
2164
2165 --Bug 4197656 : Start
2166
2167 for i in 1..l_rulevalues.count loop
2168 if l_rulevalues(i).input_name = 'ESIG_REQUIRED' then
2169
2170 l_esign_required:= upper(l_rulevalues(i).input_value);
2171
2172 elsif l_rulevalues(i).input_name = 'EREC_REQUIRED' then
2173
2174 l_erecord_required:= upper(l_rulevalues(i).input_value);
2175
2176 elsif l_rulevalues(i).input_name = 'EREC_STYLE_SHEET' then
2177
2178 l_STYLE_SHEET := l_rulevalues(i).input_value;
2179
2180 elsif l_rulevalues(i).input_name = 'EREC_STYLE_SHEET_VER' then
2181
2182 l_STYLE_SHEET_VER := l_rulevalues(i).input_value;
2183
2184 elsif l_rulevalues(i).input_name = 'CHANGE_SIGNERS' then
2185
2186 l_change_signer:= upper(l_rulevalues(i).input_value);
2187 l_change_signer_evaluate := 'Y';
2188
2189 --Bug 3761813 : start
2190
2191 elsif l_rulevalues(i).input_name = 'REDLINE_REQUIRED' then
2192
2193 l_redline_required := upper(l_rulevalues(i).input_value);
2194 --Bug 3761813: End
2195
2196 --Bug 4160412: Start
2197 --Set the signature mode parameter if it is set.
2198 elsif l_rulevalues(i).input_name = EDR_CONSTANTS_GRP.G_SIGNATURE_MODE then
2199
2200 l_signature_mode := upper(l_rulevalues(i).input_value);
2201
2202 --Bug 4160412: End
2203
2204
2205
2206 end if;
2207 end loop;
2208 -- If E- Signature is required and CHANGE_SIGNER is ALL or ADHOC
2209 -- and source application is not 'DB'' the set l_change_signer_defined
2210 -- else return the process .
2211
2212 --Bug 4150616: Start
2213 --This "IF" condition needs to modified to check if the profile option is switched on.
2214 --If the profile option has been switched off, then the force e-record option has been used.
2215 --Hence in that scenario, the if condition should fail.
2216 IF (l_esign_required ='Y' AND l_change_signer_evaluate = 'Y'
2217 AND (l_change_signer = 'ALL' OR l_change_signer = EDR_CONSTANTS_GRP.g_change_signer_adhoc)
2218 AND l_source_application_type not in (EDR_CONSTANTS_GRP.g_db_mode , EDR_CONSTANTS_GRP.g_msca_mode)
2219 AND l_eres_profile_value = 'Y') THEN
2220 l_change_signer_defined := 'Y';
2221 --Bug 4150616: End
2222
2223 --Bug 4150616: Start
2224 --If force_erecord is set to 'Y', then add the "FORCE_ERECORD_USED" parameter into the event list.
2225 --Otherwise exit the rule function with a return status of "SUCCESS". (as before).
2226
2227 --Bug 4543216: Start
2228 ELSIF (l_esign_required = 'Y' and l_signature_mode = EDR_CONSTANTS_GRP.G_ERES_LITE
2229 AND l_source_application_type not in (EDR_CONSTANTS_GRP.g_db_mode,EDR_CONSTANTS_GRP.g_msca_mode) AND l_eres_profile_value = 'Y') then
2230 --Bug 4543216: End
2231 l_lite_mode := true;
2232 ELSIF l_force_erecord = 'Y' then
2233 --Add the parameter into the event list.
2234 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_FORCE_ERECORD_USED, 'Y',p_event.Parameter_List);
2235 --Bug 4150616: End
2236 --Bug 5724159 : Start
2237 --The Control should not return back without generating E-Record
2238 /* ELSE*/
2239 /* Terminate the process . Nothing has to be done */
2240 /* return 'SUCCESS';*/
2241 --Bug 5724159 : End
2242
2243 END IF;
2244 END IF;
2245
2246 -- Bug 4197656 : End
2247 -- Bug 3567868 :END
2248 -- bug 7154935
2249 -- Added OR condition to fix eRecord Generation issue when Force ERecord
2250 -- is used but no rule is defined.
2251 --ER # 2966866 start
2252 if (l_inter_event_mode = 'Y' and l_relationship = EDR_CONSTANTS_GRP.g_erecord_only)
2253 OR (l_force_erecord = 'Y' and l_ruleids.count = 0)
2254 then
2255 l_trans_value_only := 'Y';
2256 -- SRPURI : Bug fix 3168963 Added following code
2257 -- Deletes data in Rule Id and rulename arrays as these are not used if relationship between
2258 -- events is 'ERECORD_ONLY' and initialize array with dummay values assumin -1 never be a valid
2259 -- row in EDR_AMERULE_INPUT_VAR and null for rule name since we need to pass rulename to
2260 -- get AME input variable values call
2261 -- following statements are valid only when relationship between events is 'ERECORD_ONLY'
2262 -- if you are planning to use this if you are planning to overload this if condition for
2263 -- some other purpose make sure it is not breaking ERECORD_ONLY Functionality
2264 l_ruleids.delete;
2265 l_rulenames.delete;
2266 l_ruleids(1) := -1;
2267 l_rulenames(1) := Null;
2268 --
2269 -- when event 2 is raised in the context of event 1 and subscription parameter is
2270 -- event1=ERECORD_ONLY then we need to capture eRecord unconditionaly
2271 -- by setting l_erecord_required = 'Y' we will achieve this.
2272 --
2273 l_erecord_required := 'Y';
2274 wf_log_pkg.string(3, 'EDR_PSIG_rule.psig_rule','Get Only the input variables at txn level');
2275 end if;
2276 --ER # 2966866 end
2277
2278 for i in 1..l_ruleids.count loop
2279
2280 --ER # 2966866 start
2281 if (l_trans_value_only = 'Y') then
2282 l_rule_id := -1;
2283 else
2284 l_rule_id := l_ruleids(i);
2285 end if;
2286
2287 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Rule_id: '||l_ruleids(i)||' Rule '||l_rulenames(i));
2288
2289 -- Bug 3214495 : Start
2290
2291 EDR_STANDARD.GET_AMERULE_INPUT_VARIABLES( transactiontypeid =>NVL(l_ame_transaction_type,P_EVENT.getEventName( )),
2292 ameruleid =>l_ruleids(i),
2293 amerulename=>l_rulenames(i),
2294 ameruleinputvalues=>l_rulevalues);
2295
2296 -- Bug 3214495 : End
2297
2298 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','Total Input Values '||l_rulevalues.count );
2299 if l_rulevalues.count > 0 then
2300 for i in 1..l_rulevalues.count loop
2301 if l_rulevalues(i).input_name = 'ESIG_REQUIRED' then
2302 l_rule_esign_required:=upper(l_rulevalues(i).input_value);
2303 if ( upper(l_esign_required)='N' and l_rule_esign_required ='Y') then
2304 l_esign_required:= l_rule_esign_required;
2305 end if;
2306 elsif l_rulevalues(i).input_name = 'EREC_REQUIRED' then
2307 l_rule_erecord_required:=upper(l_rulevalues(i).input_value);
2308 if ( upper(l_erecord_required)='N' and l_rule_erecord_required ='Y') then
2309 l_erecord_required:= l_rule_erecord_required;
2310 end if;
2311 elsif l_rulevalues(i).input_name = 'EREC_STYLE_SHEET' then
2312 l_temp_style_sheet:= l_rulevalues(i).input_value;
2313 -- Bug 3761813 : start
2314 elsif l_rulevalues(i).input_name = 'REDLINE_REQUIRED' then
2315 l_redline_required := upper(l_rulevalues(i).input_value);
2316 -- Bug 3761813 : end
2317 elsif l_rulevalues(i).input_name = 'EREC_STYLE_SHEET_VER' then
2318 l_temp_style_sheet_ver:= l_rulevalues(i).input_value;
2319 -- Bug 3161859 : Start
2320 elsif l_rulevalues(i).input_name = 'CHANGE_SIGNERS' then
2321 l_change_signer_defined := 'Y';
2322 if ( upper(l_change_signer)='ADHOC' and upper(l_rulevalues(i).input_value) ='NONE') then
2323 l_change_signer:= l_rulevalues(i).input_value;
2324 elsif ( upper(l_change_signer)='ALL' and upper(l_rulevalues(i).input_value) ='NONE') then
2325 l_change_signer:= l_rulevalues(i).input_value;
2326 elsif ( upper(l_change_signer)='ALL' and upper(l_rulevalues(i).input_value) ='ADHOC') then
2327 l_change_signer:= l_rulevalues(i).input_value;
2328 end if;
2329 -- Bug 3161859 : End
2330
2331 --Bug 4160412: Start
2332 --Get the signature mode parameter if found.
2333 elsif l_rulevalues(i).input_name = EDR_CONSTANTS_GRP.G_SIGNATURE_MODE then
2334 l_temp_signature_mode := l_rulevalues(i).input_value;
2335 --Bug 4160412: End
2336 end if;
2337 end loop;
2338 end if;
2339 -- Begin Bug 3334148
2340 -- Determine if sytlesheet belongs to most deterministic Rule
2341 -- Bug 3456530 : Start
2342 if (l_rule_erecord_required ='Y' and l_esign_required ='N') then
2343 -- Bug 3456530 : End
2344 l_style_sheet:=l_temp_style_sheet;
2345 l_style_sheet_ver:=l_temp_style_sheet_ver;
2346 end if;
2347 if l_rule_esign_required ='Y' then
2348 l_style_sheet:=l_temp_style_sheet;
2349 l_style_sheet_ver:=l_temp_style_sheet_ver;
2350
2351 --Bug 4160412: Start
2352 l_signature_mode := l_temp_signature_mode;
2353 --Bug 4160412: End
2354 end if;
2355 --End Bug 3334148
2356 --Start Bug 5158772
2357 IF l_force_erecord = 'Y' then
2358 l_style_sheet := l_temp_style_sheet;
2359 l_style_sheet_ver := l_temp_style_sheet_ver;
2360 wf_log_pkg.string(3, 'EDR_PSIG_rule.psig_rule','Force Ereocrd setting style sheet and version');
2361 end if;
2362 --End Bug 5158772
2363 end loop;
2364
2365 -- Bug 3161859 : Start
2366 if (l_change_signer_defined = 'N') then
2367 l_change_signer:= 'NONE';
2368 end if;
2369 -- Bug 3161859 : End
2370
2371 --ER # 2966866 start
2372 if (l_inter_event_mode = 'Y' and(l_relationship = EDR_CONSTANTS_GRP.g_erecord_only OR
2373 l_relationship = EDR_CONSTANTS_GRP.g_ignore_signature )) then
2374
2375 l_esign_required := 'N';
2376 -- Bug : 3499326 - start
2377 l_style_sheet := l_temp_style_sheet;
2378 l_style_sheet_ver := l_temp_style_sheet_ver;
2379 -- Bug : 3499326 - end
2380
2381 wf_log_pkg.string(3, 'EDR_PSIG_rule.psig_rule','Inter Event Mode and No eSignature');
2382 end if;
2383 --ER # 2966866 end
2384
2385
2386 -- Bug 3170251 : Start Get the File Extension of Template Associated with this event
2387 -- Bug 3456399 : Start - get the last three characters from file name
2388 L_STYLE_SHEET_TYPE := UPPER(SUBSTR(L_STYLE_SHEET,LENGTH(L_STYLE_SHEET)-2,LENGTH(L_STYLE_SHEET)));
2389 -- Bug 3456399 : End
2390 --Bug: 3170251 : End
2391
2392 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Signature Required :'||l_esign_required);
2393 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','eRecord Required :'||l_erecord_required);
2394 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Style Sheet :'||l_style_sheet);
2395 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','StyleSheet Version :'||l_style_sheet_ver);
2396 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','StyleSheet repository :'||l_style_sheet_repository);
2397
2398 --Diagnostics Start
2399 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2400 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_PARAM_LIST_EVT');
2401 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2402 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2403 FND_MESSAGE.SET_TOKEN('ESIG_VALUE',l_esign_required);
2404 FND_MESSAGE.SET_TOKEN('EREC_VALUE',l_erecord_required);
2405 FND_MESSAGE.SET_TOKEN('STYLE_SHEET',l_style_sheet);
2406 FND_MESSAGE.SET_TOKEN('STYLE_VER',l_style_sheet_ver);
2407 FND_MESSAGE.SET_TOKEN('STYLE_REP',l_style_sheet_repository);
2408 FND_MESSAGE.SET_TOKEN('XML_MAP_CODE',l_xml_map_code);
2409 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2410 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2411 FALSE
2412 );
2413 end if;
2414 --Diagnostics End
2415
2416
2417 --Bug 4150616: Start
2418 --We want to create the e-record for any the following conditions being true.
2419 --1. ESIG_REQUIRED = Y
2420 --2. EREC_REQUIRED = Y
2421 --3. Subscription parameter FORCE_ERECORD = Y
2422 IF (upper(l_esign_required)='Y' OR upper(l_erecord_required)='Y' or l_force_erecord = 'Y') THEN
2423 /* Read the Require Parameter */
2424
2425 --If this condition is true then it means that the e-record is being created
2426 --because FORCE_ERECORD is set to Y and either the profile option is switched off
2427 --or (ESIG_REQUIRED=N and EREC_REQUIRED = N).
2428 --Hence we would add an addittional parameter to the event payload which in
2429 --turn is added to the doc params which indicates that the e-record is being
2430 --created because FORCE_ERECORD is set to Y.
2431 --This parameter is named "FORCE_ERECORD_USED".
2432 if l_eres_profile_value = 'N' OR (upper(l_esign_required) = 'N' and upper(l_erecord_required) = 'N') then
2433 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_FORCE_ERECORD_USED,'Y',p_event.Parameter_List);
2434 end if;
2435 --Bug 4150616: End
2436
2437 l_audit_group := NVL(wf_event.getValueForParameter('EDR_AUDIT_GROUP',p_event.Parameter_List),
2438 P_EVENT.getEventName( ));
2439
2440 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','XML Map Code :'||l_xml_map_code );
2441 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Audit Group :'||l_audit_group );
2442 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Setting ECX parameter');
2443
2444 /* Set Additional Parameters required by Esignature */
2445 /* wf_event.AddParameterToList('SUB_GUID', p_subscription_guid,p_event.Parameter_List); Subscription ID */
2446 wf_event.AddParameterToList('ECX_MAP_CODE', l_xml_map_code,p_event.Parameter_List); /* XML Map Code*/
2447 wf_event.AddParameterToList('ECX_DOCUMENT_ID', P_EVENT.getEventKey( ),p_event.Parameter_List); /* XML Document ID*/
2448 wf_event.AddParameterToList('TEXT_XSLNAME', l_style_sheet,p_event.Parameter_List); /* Style Sheet*/
2449 wf_event.AddParameterToList('TEXT_XSLVERSION', l_style_sheet_ver,p_event.Parameter_List); /* Style Sheet*/
2450 wf_event.AddParameterToList('HTML_XSLNAME', NULL,p_event.Parameter_List); /* Style Sheet*/
2451 wf_event.AddParameterToList('HTML_XSLVERSION', NULL,p_event.Parameter_List); /* Style Sheet*/
2452 wf_event.AddParameterToList('APPLICATION_CODE', lower(l_application_code),p_event.Parameter_List); /* Style Sheet*/
2453 wf_event.AddParameterToList('EDR_SIGN_REQUIRED', l_esign_required,p_event.Parameter_List); /* Signature Required*/
2454 wf_event.AddParameterToList('EDR_EREC_REQUIRED', l_erecord_required,p_event.Parameter_List); /* eRecord Required*/
2455 --Bug 3761813 : start
2456 wf_event.AddParameterToList('REDLINE_REQUIRED', nvl(l_redline_required,'N'),p_event.Parameter_List); /* redline Required*/
2457 --Bug 3761813 : END
2458
2459 -- Bug 3161859 : Start
2460 -- this parameter would be used to show whether or not to display
2461 -- the Update Signer button wouuld be enabled or disabled
2462 wf_event.AddParameterToList('EDR_CHANGE_SIGNERS', l_change_signer,p_event.Parameter_List); /* Change Signer Required*/
2463 -- Bug 3161859 : End
2464
2465 -- Bug 3170251 : Start - Store EREC_TEMPLATE_TYPE in the event parameter list so that its available throughout
2466 -- the rule function and pageflow till the workflow is alive for this event
2467 wf_event.AddParameterToList('EREC_TEMPLATE_TYPE', l_style_sheet_type,p_event.Parameter_List); /* Template Type */
2468 -- Bug 3170251 : End
2469
2470 -- Bug 5158510 :start
2471 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_SIGNATURE_MODE, l_signature_mode,p_event.Parameter_List);
2472 -- Bug 5158510 :end
2473 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','ECX Parameter set have been set' );
2474
2475 /* Generate XML Document */
2476 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','Calling ECX Standard package to generate XML' );
2477 -- Bug 3960236 : Start
2478 -- Fetch the payload parameter
2479 L_XML_GENERATION_API := wf_event.getValueForParameter('XML_GENERATION_API',p_event.Parameter_List);
2480
2481 -- Bug 3960236 : end
2482 --Bug 3667036 : Start
2483 --if event data is not null then don't call XML gateway again
2484 if dbms_lob.getlength(l_event_data) > 0 then
2485 L_XML_DOCUMENT := l_erecord_data;
2486 -- Bug 3960236 : Start
2487 elsif L_XML_GENERATION_API is NOT NULL then
2488 -- Check if call back function has been passed to generate XML document by product team
2489 begin
2490 --bug 4730592: start
2491 --changed the way the callback api is called dynamically
2492 --L_SQL_STR:= 'select '|| l_xml_generation_api || ' from dual';
2493 --execute immediate L_SQL_STR into L_XML_DOCUMENT;
2494 execute immediate 'begin :1:= '||l_xml_generation_api ||'; end;'
2495 using out l_xml_document;
2496 --bug 4730592: end
2497 if l_XML_DOCUMENT is NULL then
2498 raise EMPTY_XML_DOCUMENT;
2499 end if;
2500 exception
2501 when others then
2502 raise API_EXECUTION_ERROR;
2503 end;
2504
2505 --Bug 4529417: Start
2506 --We need to obtain the next sequence value from EDR_ERECORDS_S.
2507
2508 --insert the erecord id into the temp table
2509 --This would be picked up by the procedures called by the XML Map for attacments
2510 --Delete the global temp table before inserting a row
2511 delete edr_erecord_id_temp;
2512
2513 --select Unique event Id to be posted in temp tables
2514 SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL;
2515
2516 INSERT into edr_erecord_id_temp (document_id) values (l_edr_Event_id);
2517
2518 --Obtain the attachment string parameter.
2519 l_attachment_string_details := wf_event.getValueForParameter('EDR_PSIG_ATTACHMENT',p_event.Parameter_List);
2520
2521 --If the attachment string exists then call the attachment API to create the attachments.
2522 if l_attachment_string_details is not null then
2523 EDR_ATTACHMENTS_GRP.ADD_ERP_ATTACH(p_attachment_string => l_attachment_string_details);
2524 end if;
2525 --Bug 4529417: End
2526
2527
2528
2529
2530 -- Bug 3960236 : end
2531 else
2532 --otherwise call XML gateway
2533 --insert the erecord id into the temp table
2534 --This would be picked up by the procedures called by the XML Map for attacments
2535 --Delete the global temp table before inserting a row
2536 delete edr_erecord_id_temp;
2537
2538 --select Unique event Id to be posted in temp tables
2539 SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL;
2540
2541
2542 --Bug 3761813 : start
2543 --Bug 4306292: Start
2544 --Commenting changes made for red lining.
2545
2546 if ( nvl(l_redline_required,'N') = 'Y') then
2547 l_snapstatus:= PRE_TX_SNAPSHOT(l_edr_Event_id,l_xml_map_code,P_EVENT.getEventKey());
2548 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','After Calling Snapshot to generate XML'||P_EVENT.getEventKey( ));
2549 END IF;
2550 --Bug 4306292: End
2551 -- Bug 3761813 : end
2552
2553 INSERT into edr_erecord_id_temp (document_id) values (l_edr_Event_id);
2554 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','eRecord id: '||l_edr_Event_id||' has been sent to ECX');
2555
2556 L_XML_DOCUMENT:=ECX_STANDARD.GENERATE(p_event_name => P_EVENT.getEventName(),
2557 p_event_key => P_EVENT.getEventKey(),
2558 p_parameter_list => p_event.Parameter_List);
2559
2560 --Diagnostics Start
2561 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2562 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_ECX_GEN_EVT');
2563 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2564 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2565 FND_MESSAGE.SET_TOKEN('XML_MAP_CODE',l_xml_map_code);
2566 FND_MESSAGE.SET_TOKEN('TRANS','DB TO XML');
2567 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2568 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2569 FALSE
2570 );
2571 end if;
2572 --Diagnostics End
2573
2574 end if;
2575 --Bug 3667036 : End
2576
2577 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','XML Document Generated' );
2578
2579 --Bug 5724159 :Start
2580 --When the number of approvers is 0,Adhoc signer capability is ALL or ADHOC and the
2581 --Signature mode is SHORT,Error message needs to displayed. E-Record will not be captured in this scenario.
2582 IF(approverlist.count=0 and l_change_signer_defined ='Y' AND l_change_signer <>'NONE' and l_signature_mode='SHORT') THEN
2583 RAISE NO_SIGNERS_DEF_FOR_LITE_MODE;
2584 END IF;
2585 --Bug 5724159 :End
2586
2587 --Bug 3437422: Start
2588 --call an api that would find out of the xml contains the user_data
2589 --placeholder and replace it with the actual contents of the user_data
2590 --node.
2591 edr_utilities.replace_user_data_token(l_xml_document);
2592 --Bug 3437422: End
2593 --To make eRecord generation storing autonomous
2594 --Moved all of the following code in this IF clause
2595 --to STROE_ERECORD Local Procedure
2596 --
2597 -- Bug Fix 3143107
2598 -- Removed elsif l_erecord_required = Y clause as we already know that
2599 -- we need to generate eRecord irrespective of l_erecord_required value
2600 -- we will use l_esign_required required in STORE_ERECORD to move the eRecord
2601 -- status to 'COMPLETE'(l_esign_required = 'N') or 'ERROR' (l_esign_required ='Y')
2602
2603 STORE_ERECORD(p_XML_DOCUMENT => l_XML_DOCUMENT,
2604 p_style_sheet_repository => l_style_sheet_repository,
2605 p_style_sheet => l_style_sheet,
2606 p_style_sheet_ver => l_style_sheet_ver,
2607 p_application_code => l_application_code,
2608 p_edr_event_id => l_edr_event_id ,
2609 p_esign_required => l_esign_required,
2610 p_subscription_guid => p_subscription_guid,
2611 x_event => p_event);
2612 --Bug 4590037
2613 --Set the XML document to CLOB datatype of the event
2614 P_EVENT.setEventData(L_XML_DOCUMENT);
2615 --Bug 4590037
2616
2617 --Bug 4150616: Start
2618 --Start the workflow process only if the profile option is switched on.
2619 IF upper(l_esign_required)='Y' and l_eres_profile_value = 'Y' THEN
2620 --Bug 4150616: End
2621
2622 --Bug 3761813 : start
2623 --Bug 4306292: Start
2624 --Commenting changes made for red lining.
2625 if (l_style_sheet_type = 'XSL' and nvl(l_redline_required,'N') = 'Y' ) then
2626 select DIFF_XML into L_XML_DOCUMENT from EDR_REDLINE_TRANS_DATA where EVENT_ID = l_edr_event_id ;
2627 -- Bug 5167207 : start
2628 --Set the XML document to CLOB datatype of the event
2629 P_EVENT.setEventData(L_XML_DOCUMENT);
2630 -- Bug 5167207 : end
2631 end if;
2632 --Bug 4306292: End
2633 --Bug 3761813 : end
2634
2635 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','XML Document Set to event Data' );
2636
2637 /* GET Approver List.
2638 ame_api.getAllApprovers(applicationIdIn=>l_application_Id,
2639 transactionIdIn=>P_EVENT.getEventKey( ),
2640 transactionTypeIn=>NVL(l_ame_transaction_type,P_EVENT.getEventName( )),
2641 approversOut=>approverList); */
2642
2643 --Diagnostics Start
2644 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2645 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_APPROVER_COUNT_EVT');
2646 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2647 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2648 FND_MESSAGE.SET_TOKEN('COUNT',approverList.count);
2649 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2650 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2651 FALSE
2652 );
2653 end if;
2654 --Diagnostics End
2655 --Bug 5724159 : Start
2656 /*check whether number of approvers is zero,Change signer property is defined and its value is
2657 NONE, List Of signers should not be launched in this scenario, return success*/
2658
2659 IF (approverList.count=0 AND l_change_signer_defined ='Y' AND l_change_signer ='NONE') THEN
2660 return 'SUCCESS' ;
2661 END IF;
2662 --Bug 5724159 : End
2663
2664 -- BUG 3567868 Start
2665 -- IF No Approver but Change Signer is 'ADHOC' or 'ALL'
2666 IF ( (approverList.count > 0) OR (upper(l_change_signer_defined) = 'Y') or l_lite_mode) THEN
2667 -- BUG 3567868 END
2668
2669
2670 --Bug 4577122: Start
2671 --validate the voting regime and then proceed
2672 validate_voting_regime
2673 (
2674 p_approver_list => approverList,
2675 x_valid_regime => l_valid_regime,
2676 x_voting_regime => l_voting_regime
2677 );
2678
2679 if (l_valid_regime = 'N') then
2680 raise INVALID_FIRST_VOTER_WINS_SETUP;
2681 else
2682 if (l_voting_regime <> ame_util.firstApproverVoting ) then
2683 l_voting_regime := 'X';
2684 end if;
2685
2686 --set the voting regime on the workflow attribute
2687 wf_event.AddParameterToList
2688 ('AME_VOTING_REGIME', l_voting_regime,p_event.Parameter_List);
2689
2690 end if;
2691 --Bug 4577122: End
2692
2693 --Bug 4160412: Start
2694 --Check the value of signature mode.
2695 --If it is set to lite then, fetch the value of the attributes APPROVING_USERS,
2696 --APPROVER_COUNT and APPROVING RESPONSIBILITY.
2697 --Bug 5891879 :Start
2698 --Defaults Signature mode to SHORT when the src application type is KIOSK
2699 IF l_source_application_type = EDR_CONSTANTS_PUB.g_kiosk_mode then
2700 l_signature_mode := EDR_CONSTANTS_GRP.G_ERES_LITE;
2701 end if;
2702 --Bug 5891879 :End
2703
2704 --Bug 4543216: Start
2705 if l_signature_mode = EDR_CONSTANTS_GRP.G_ERES_LITE then
2706 --Bug 4543216: Start
2707
2708 --Diagnostics Start
2709 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2710 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_SIGN_MODE_EVT');
2711 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2712 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2713 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2714 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2715 FALSE
2716 );
2717 end if;
2718 --Diagnostics End
2719
2720 l_einitials_defer_mode := 'Y';
2721 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_SIGNATURE_MODE, l_signature_mode,p_event.Parameter_List);
2722 l_approver_count := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_APPROVER_COUNT,p_event.Parameter_List);
2723 l_approver_list := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_APPROVER_LIST,p_event.Parameter_List);
2724
2725 --If approver_count and approving users are set, then raise an exception.
2726 if l_approver_count is not null and l_approver_list is not null then
2727 RAISE APPROVING_USERS_PARAMS_ERR;
2728 end if;
2729
2730 --If approving users are set, then fetch the approver list from the comma separated string.
2731 if l_approver_list is not null then
2732
2733 --Diagnostics Start
2734 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2735 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_APPR_LIST_EVT');
2736 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2737 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2738 FND_MESSAGE.SET_TOKEN('APPROVER_LIST',l_approver_list);
2739 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2740 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2741 FALSE
2742 );
2743 end if;
2744 --Diagnostics End
2745
2746 EDR_UTILITIES.GET_APPROVER_LIST(P_APPROVING_USERS => L_APPROVER_LIST,
2747 X_APPROVER_LIST => L_CUSTOM_APPROVER_LIST,
2748 X_ARE_APPROVERS_VALID => L_ARE_APPROVERS_VALID,
2749 X_INVALID_APPROVERS => L_INVALID_APPROVERS);
2750
2751 l_repeating_approvers := EDR_UTILITIES.ARE_APPROVERS_REPEATING(P_APPROVER_LIST => L_CUSTOM_APPROVER_LIST);
2752
2753 --If there are repeating approvers then raise an exception
2754 if l_repeating_approvers then
2755 raise REPEATING_APPROVERS_ERR;
2756 end if;
2757
2758 --If the approvers are invalid then raise an exception.
2759 if l_are_approvers_valid = 'N' then
2760 RAISE INVALID_APPROVING_USERS;
2761 end if;
2762 approverlist := L_CUSTOM_APPROVER_LIST;
2763
2764 for l_list_count in 1..approverlist.count loop
2765 if instr(approverlist(l_list_count).name,'#') = 1 then
2766 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_DO_RESPS_EXIST,'Y',p_event.Parameter_List);
2767 l_einitials_defer_mode := 'N';
2768 exit;
2769 end if;
2770 end loop;
2771 end if;
2772
2773 if l_approver_count is not null then
2774
2775 --Diagnostics Start
2776 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2777 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_APPR_COUNT_EVT');
2778 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2779 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2780 FND_MESSAGE.SET_TOKEN('APPROVER_COUNT',l_approver_count);
2781 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2782 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2783 FALSE
2784 );
2785 end if;
2786 --Diagnostics End
2787
2788 --Approver Count has been specified
2789 --Convert it into a number type.
2790 BEGIN
2791 l_approver_count_value := to_number(l_approver_count,'999999999999');
2792 --If approver count is less then 0 then raise an error.
2793 if l_approver_count_value < 0 then
2794 raise INVALID_APPR_COUNT_VALUE_ERR;
2795 end if;
2796 EXCEPTION
2797 --Approver Count attribute was a not a valid number. Hence raise an exception.
2798 WHEN VALUE_ERROR THEN
2799 RAISE INVALID_APPR_COUNT_ERR;
2800 END;
2801
2802
2803 --Make null entries in the approver list table type.
2804 for l_list_count in 1..l_approver_count_value loop
2805 L_CUSTOM_APPROVER_LIST(l_list_count).name := null;
2806 L_CUSTOM_APPROVER_LIST(l_list_count).approver_order_number := l_list_count;
2807 approverList := L_CUSTOM_APPROVER_LIST;
2808 end loop;
2809
2810 l_einitials_defer_mode := 'N';
2811
2812 end if;
2813
2814 --If approver count is zero, then do not start the pageflow process.
2815 --This is because adhoc functionality is not supported in EINITIALS.
2816 if approverList.count = 0 then
2817 return 'SUCCESS';
2818 end if;
2819
2820 --Evaluate the einitials deferred mode variable.
2821 if l_einitials_defer_mode = 'Y' then
2822 l_eres_defer_mode := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_DEFERRED_PARAM,p_event.Parameter_List);
2823 if l_eres_defer_mode is null or l_eres_defer_mode <> 'Y' then
2824 l_einitials_defer_mode := 'N';
2825 end if;
2826 END IF;
2827
2828 --Diagnostics Start
2829 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2830 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_DEFER_MODE_EVT');
2831 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2832 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2833 FND_MESSAGE.SET_TOKEN('DEFER_MODE',l_einitials_defer_mode);
2834 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2835 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2836 FALSE
2837 );
2838 end if;
2839 --Diagnostics End
2840
2841 --Add the e-initials deferred mode variable to the event.
2842 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_EINITIALS_DEFER_MODE,l_einitials_defer_mode,p_event.Parameter_List);
2843 END IF;
2844 --Bug 4160412: End
2845
2846
2847 /* select Unique event Id to be posted in temp tables */
2848 /* 24-FEB-2003: CJ: commented one line below as the erecord id is already available
2849 in the local variable and added a line to delete the erecord id from temp
2850 table */
2851 /* SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL; */
2852 /* 24-FEB-2003: CJ : end */
2853 /* Set this value to correlation_id to be used by WF_RULE.DEFAULT_RULE funciton */
2854 P_EVENT.SETCORRELATIONID(l_edr_event_id);
2855 /* Select the workflow process for the subscription */
2856 select wf_process_type,wf_process_name
2857 into l_wftype, l_wfprocess
2858 from wf_event_subscriptions
2859 where guid = p_subscription_guid;
2860
2861 wf_event.AddParameterToList('#WF_PAGEFLOW_ITEMTYPE', l_wftype,p_event.Parameter_List); /* Page Flow Workflow type*/
2862 wf_event.AddParameterToList('#WF_PAGEFLOW_ITEMPROCESS', l_wfprocess,p_event.Parameter_List); /* Page Flow Workflow process*/
2863 wf_event.AddParameterToList('#WF_PAGEFLOW_ITEMKEY', l_edr_event_id,p_event.Parameter_List); /* Item Key*/
2864
2865 /* CAll Page Flow Creation API */
2866 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','Calling Page Flow Creation Procedure');
2867
2868 --Diagnostics Start
2869 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2870 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_WF_PARAMS_EVT');
2871 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2872 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2873 FND_MESSAGE.SET_TOKEN('ITEM_TYPE',l_wftype);
2874 FND_MESSAGE.SET_TOKEN('ITEM_KEY',l_edr_event_id);
2875 FND_MESSAGE.SET_TOKEN('PROCESS',l_wfprocess);
2876 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2877 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2878 FALSE
2879 );
2880 end if;
2881 --Diagnostics End
2882
2883 --Bug 4122622: Start
2884 --Obtain the value of the comma separated child e-record ID string.
2885 l_child_erecord_ids := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_CHILD_ERECORD_IDS,p_event.Parameter_List);
2886
2887 --Set the approriate flags based on this value.
2888 if l_child_erecord_ids is not null then
2889 if instr(l_child_erecord_ids,',') > 0 then
2890 l_child_erecord_ids_set := true;
2891 else
2892 l_child_erecord_id_set := true;
2893 end if;
2894 end if;
2895
2896 --Set the approriate flags based on the parent e-record id.
2897 if(l_parent_erecord_id is not null and l_parent_erecord_id <> EDR_CONSTANTS_GRP.g_default_num_param_value) then
2898 l_parent_erecord_id_set := true;
2899 end if;
2900
2901 --Set the related e-record IDs URLs based on the flags.
2902 if l_parent_erecord_id_set then
2903 if l_child_erecord_id_set then
2904
2905 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_RELATED_ERECORDS_VIEW&retainAM=Y&parentERecordId='||
2906 l_parent_erecord_id||'&childERecordIds='||l_child_erecord_ids||'&addBreadCrumb=Y';
2907
2908 elsif l_child_erecord_ids_set then
2909 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_RELATED_ERECORDS_VIEW&edrNotifId=-&#NID-&retainAM=Y'||
2910 '&parentERecordId='||l_parent_erecord_id||'&addBreadCrumb=Y';
2911 else
2912
2913 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_ERECORD_DETAILS_VIEW&retainAM=Y' ||
2914 '&relatedERecordType=PARENT&fromNotifPG=Y&eRecordId='||l_parent_erecord_id;
2915 end if;
2916
2917 elsif l_child_erecord_ids_set then
2918
2919 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_RELATED_ERECORDS_VIEW&edrNotifId=-&#NID-&retainAM=Y&addBreadCrumb=Y';
2920
2921 elsif l_child_erecord_id_set then
2922
2923 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_ERECORD_DETAILS_VIEW&retainAM=Y' ||
2924 '&relatedERecordType=CHILD&fromNotifPG=Y&eRecordId='||l_child_erecord_ids;
2925
2926 end if;
2927
2928 wf_event.AddParameterToList('RELATED_ERECORDS',l_url,p_event.Parameter_List);
2929
2930 --Bug 4122622: End;
2931
2932 l_return_status:= CREATE_PAGEFLOW(p_event_id => l_edr_event_id,
2933 p_map_code => l_xml_map_code,
2934 p_ame_transaction_type => l_ame_transaction_type,
2935 p_audit_group => l_audit_group,
2936 p_eventP => p_event,
2937 p_subscription_guid => p_subscription_guid,
2938 P_approverlist => approverList,
2939 P_ERR_CODE => l_error,
2940 P_ERR_MSG => l_error_msg);
2941 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','Page Flow Returned '||l_return_status);
2942
2943 IF l_return_status = 'ERROR' OR L_ERROR is not null then
2944 raise PAGE_FLOW_FAILED;
2945 ELSE
2946 RETURN l_return_status;
2947 END IF;
2948
2949 ELSE
2950 /* No approver Required, Signature Not REquired */
2951 RETURN 'SUCCESS';
2952 END IF;
2953 END IF;
2954 end if;
2955 end if;
2956
2957 RETURN 'SUCCESS';
2958
2959 exception
2960 --Bug 4577122: Start
2961 when INVALID_FIRST_VOTER_WINS_SETUP then
2962 FND_MESSAGE.SET_NAME('EDR','EDR_BAD_FRST_VOTER_SETUP');
2963 fnd_message.set_token( 'EVENT', l_error_event);
2964 APP_EXCEPTION.RAISE_EXCEPTION;
2965 return EDR_CONSTANTS_GRP.g_error_status;
2966 --Bug 4577122: End
2967
2968 when NO_ENABLED_ERES_SUBSCRIPTION THEN
2969 FND_MESSAGE.SET_NAME('EDR','EDR_ERES_SUBSCRIPTION_DISABLED');
2970 fnd_message.set_token( 'EVENT', l_error_event);
2971 --Diagnostics Start
2972 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2973 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2974 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2975 FALSE
2976 );
2977 end if;
2978 --Diagnostics End
2979 APP_EXCEPTION.RAISE_EXCEPTION;
2980 return EDR_CONSTANTS_GRP.g_error_status;
2981
2982 when NO_ERES_SUBSCRIPTION THEN
2983 FND_MESSAGE.SET_NAME('EDR','EDR_NO_ERES_SUBSCRIPTION');
2984 fnd_message.set_token( 'EVENT', l_error_event);
2985
2986 --Diagnostics Start
2987 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2988 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2989 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2990 FALSE
2991 );
2992 end if;
2993 --Diagnostics End
2994
2995 APP_EXCEPTION.RAISE_EXCEPTION;
2996 return EDR_CONSTANTS_GRP.g_error_status;
2997
2998 WHEN MULTIPLE_ERES_SUBSCRIPTIONS THEN
2999 FND_MESSAGE.SET_NAME('EDR','EDR_MULTI_ERES_SUBSCRIPTIONS');
3000 fnd_message.set_token( 'EVENT',l_error_event);
3001 --Diagnostics Start
3002 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3003 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3004 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3005 FALSE
3006 );
3007 end if;
3008 --Diagnostics End
3009
3010 APP_EXCEPTION.RAISE_EXCEPTION;
3011 return EDR_CONSTANTS_GRP.g_error_status;
3012
3013 when PAGE_FLOW_FAILED then
3014 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', p_event.getEventName(), p_subscription_guid);
3015 wf_event.setErrorInfo(p_event,SQLERRM || 'ERROR');
3016 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
3017 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
3018 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
3019 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PSIG_RULE');
3020 --Diagnostics Start
3021 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3022 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3023 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3024 FALSE
3025 );
3026 end if;
3027 --Diagnostics End
3028 APP_EXCEPTION.RAISE_EXCEPTION;
3029 return EDR_CONSTANTS_GRP.g_error_status;
3030
3031 --Bug 3667036: Start
3032 --Catch the XML to XML transformation error.
3033 when ECX_XML_TO_XML_ERROR then
3034 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_XML_TO_XML_ERR');
3035 FND_MESSAGE.SET_TOKEN('EXCEPTION_MESSAGE',l_errbuf);
3036 FND_MESSAGE.SET_TOKEN('LOG',l_log_file);
3037 --Diagnostics Start
3038 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3039 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3040 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3041 FALSE
3042 );
3043 end if;
3044 --Diagnostics End
3045 APP_EXCEPTION.RAISE_EXCEPTION;
3046 return EDR_CONSTANTS_GRP.g_error_status;
3047 --Bug 3667036: End
3048
3049 --Bug 3893101: Start
3050 WHEN TRANSFORM_XML_VAR_ERROR then
3051 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_TRANSFORM_XML_VAR_ERR');
3052 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3053 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3054 --Diagnostics Start
3055 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3056 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3057 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3058 FALSE
3059 );
3060 end if;
3061 --Diagnostics End
3062 APP_EXCEPTION.RAISE_EXCEPTION;
3063 return EDR_CONSTANTS_GRP.g_error_status;
3064 --Bug 3893101: End
3065
3066 --Bug 4160412: Start
3067 WHEN APPROVING_USERS_PARAMS_ERR THEN
3068 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_USERS_PARAMS_ERR');
3069 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3070 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3071 --Diagnostics Start
3072 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3073 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3074 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3075 FALSE
3076 );
3077 end if;
3078 --Diagnostics End
3079 APP_EXCEPTION.RAISE_EXCEPTION;
3080 return EDR_CONSTANTS_GRP.g_error_status;
3081
3082 WHEN INVALID_APPROVING_USERS THEN
3083 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_INVALID_USERS');
3084 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3085 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3086 FND_MESSAGE.SET_TOKEN('APPROVER_NAMES',l_invalid_approvers);
3087 --Diagnostics Start
3088 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3089 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3090 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3091 FALSE
3092 );
3093 end if;
3094 --Diagnostics End
3095 APP_EXCEPTION.RAISE_EXCEPTION;
3096 return EDR_CONSTANTS_GRP.g_error_status;
3097
3098 WHEN REPEATING_APPROVERS_ERR THEN
3099 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_REPEATING_APPRS');
3100 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3101 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3102 FND_MESSAGE.SET_TOKEN('STRING',l_approver_list);
3103 --Diagnostics Start
3104 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3105 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3106 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3107 FALSE
3108 );
3109 end if;
3110 --Diagnostics End
3111 APP_EXCEPTION.RAISE_EXCEPTION;
3112 return EDR_CONSTANTS_GRP.g_error_status;
3113
3114 WHEN INVALID_APPR_COUNT_ERR THEN
3115 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_INVALID_APPR_CNT');
3116 FND_MESSAGE.SET_TOKEN('APPROVER_COUNT',l_approver_count);
3117 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3118 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3119
3120 --Diagnostics Start
3121 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3122 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3123 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3124 FALSE
3125 );
3126 end if;
3127 --Diagnostics End
3128 APP_EXCEPTION.RAISE_EXCEPTION;
3129 return EDR_CONSTANTS_GRP.g_error_status;
3130
3131 WHEN INVALID_APPR_COUNT_VALUE_ERR THEN
3132 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_APPR_CNT_ERR');
3133 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3134 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3135
3136 --Diagnostics Start
3137 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3138 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3139 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3140 FALSE
3141 );
3142 end if;
3143 --Diagnostics End
3144 APP_EXCEPTION.RAISE_EXCEPTION;
3145 return EDR_CONSTANTS_GRP.g_error_status;
3146 --Bug 4160412: End
3147
3148 --Bug 3960236: Start
3149 WHEN API_EXECUTION_ERROR THEN
3150 FND_MESSAGE.SET_NAME('EDR','EDR_XMLCALLBACK_API_ERR');
3151 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3152 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3153 FND_MESSAGE.SET_TOKEN('APINAME',SQLERRM||': '||l_xml_generation_api);
3154 --Diagnostics Start
3155 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3156 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3157 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3158 FALSE
3159 );
3160 end if;
3161 --Diagnostics End
3162 APP_EXCEPTION.RAISE_EXCEPTION;
3163 return EDR_CONSTANTS_GRP.g_error_status;
3164 -- Bug 3960236 : end
3165
3166
3167 WHEN EMPTY_XML_DOCUMENT THEN
3168 FND_MESSAGE.SET_NAME('EDR','EDR_XMLCALLBACK_EMPTY_ERR');
3169 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3170 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3171 FND_MESSAGE.SET_TOKEN('APINAME',SQLERRM);
3172 --Diagnostics Start
3173 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3174 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3175 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3176 FALSE
3177 );
3178 end if;
3179 --Diagnostics End
3180 APP_EXCEPTION.RAISE_EXCEPTION;
3181 return EDR_CONSTANTS_GRP.g_error_status;
3182 -- Bug 3960236 : end
3183
3184 --Bug 5724159 :start
3185 --define a new exception to throw error message when there are no approvers, Signature mode is SHORT
3186 --and the Adhoc Signer capability is ALL or ADHOC.
3187 WHEN NO_SIGNERS_DEF_FOR_LITE_MODE THEN
3188 FND_MESSAGE.SET_NAME('EDR','EDR_NO_SIGNER_DEF_ERR');
3189 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3190 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3191 --Diagnostics Start
3192 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3193 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3194 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3195 FALSE
3196 );
3197 end if;
3198 --Diagnostics End
3199 APP_EXCEPTION.RAISE_EXCEPTION;
3200 return EDR_CONSTANTS_GRP.g_error_status;
3201 --Bug 5724159 :End
3202
3203 when others then
3204 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', p_event.getEventName(), p_subscription_guid);
3205 wf_event.setErrorInfo(p_event,SQLERRM || 'ERROR');
3206 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
3207 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
3208 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
3209 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PSIG_RULE');
3210 --Diagnostics Start
3211 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3212 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3213 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3214 FALSE
3215 );
3216 end if;
3217 --Diagnostics End
3218
3219 APP_EXCEPTION.RAISE_EXCEPTION;
3220 return EDR_CONSTANTS_GRP.g_error_status;
3221
3222 end PSIG_RULE;
3223
3224 end EDR_PSIG_RULE;