[Home] [Help]
PACKAGE BODY: APPS.EDR_PSIG_RULE
Source
1 PACKAGE BODY EDR_PSIG_RULE AS
2 /* $Header: EDRRULEB.pls 120.20.12000000.3 2007/02/20 11:40:03 rvsingh 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
2249 --ER # 2966866 start
2250 if (l_inter_event_mode = 'Y' and l_relationship = EDR_CONSTANTS_GRP.g_erecord_only) then
2251 l_trans_value_only := 'Y';
2252 -- SRPURI : Bug fix 3168963 Added following code
2253 -- Deletes data in Rule Id and rulename arrays as these are not used if relationship between
2254 -- events is 'ERECORD_ONLY' and initialize array with dummay values assumin -1 never be a valid
2255 -- row in EDR_AMERULE_INPUT_VAR and null for rule name since we need to pass rulename to
2256 -- get AME input variable values call
2257 -- following statements are valid only when relationship between events is 'ERECORD_ONLY'
2258 -- if you are planning to use this if you are planning to overload this if condition for
2259 -- some other purpose make sure it is not breaking ERECORD_ONLY Functionality
2260 l_ruleids.delete;
2261 l_rulenames.delete;
2262 l_ruleids(1) := -1;
2263 l_rulenames(1) := Null;
2264 --
2265 -- when event 2 is raised in the context of event 1 and subscription parameter is
2266 -- event1=ERECORD_ONLY then we need to capture eRecord unconditionaly
2267 -- by setting l_erecord_required = 'Y' we will achieve this.
2268 --
2269 l_erecord_required := 'Y';
2270 wf_log_pkg.string(3, 'EDR_PSIG_rule.psig_rule','Get Only the input variables at txn level');
2271 end if;
2272 --ER # 2966866 end
2273
2274 for i in 1..l_ruleids.count loop
2275
2276 --ER # 2966866 start
2277 if (l_trans_value_only = 'Y') then
2278 l_rule_id := -1;
2279 else
2280 l_rule_id := l_ruleids(i);
2281 end if;
2282
2283 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Rule_id: '||l_ruleids(i)||' Rule '||l_rulenames(i));
2284
2285 -- Bug 3214495 : Start
2286
2287 EDR_STANDARD.GET_AMERULE_INPUT_VARIABLES( transactiontypeid =>NVL(l_ame_transaction_type,P_EVENT.getEventName( )),
2288 ameruleid =>l_ruleids(i),
2289 amerulename=>l_rulenames(i),
2290 ameruleinputvalues=>l_rulevalues);
2291
2292 -- Bug 3214495 : End
2293
2294 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','Total Input Values '||l_rulevalues.count );
2295 if l_rulevalues.count > 0 then
2296 for i in 1..l_rulevalues.count loop
2297 if l_rulevalues(i).input_name = 'ESIG_REQUIRED' then
2298 l_rule_esign_required:=upper(l_rulevalues(i).input_value);
2299 if ( upper(l_esign_required)='N' and l_rule_esign_required ='Y') then
2300 l_esign_required:= l_rule_esign_required;
2301 end if;
2302 elsif l_rulevalues(i).input_name = 'EREC_REQUIRED' then
2303 l_rule_erecord_required:=upper(l_rulevalues(i).input_value);
2304 if ( upper(l_erecord_required)='N' and l_rule_erecord_required ='Y') then
2305 l_erecord_required:= l_rule_erecord_required;
2306 end if;
2307 elsif l_rulevalues(i).input_name = 'EREC_STYLE_SHEET' then
2308 l_temp_style_sheet:= l_rulevalues(i).input_value;
2309 -- Bug 3761813 : start
2310 elsif l_rulevalues(i).input_name = 'REDLINE_REQUIRED' then
2311 l_redline_required := upper(l_rulevalues(i).input_value);
2312 -- Bug 3761813 : end
2313 elsif l_rulevalues(i).input_name = 'EREC_STYLE_SHEET_VER' then
2314 l_temp_style_sheet_ver:= l_rulevalues(i).input_value;
2315 -- Bug 3161859 : Start
2316 elsif l_rulevalues(i).input_name = 'CHANGE_SIGNERS' then
2317 l_change_signer_defined := 'Y';
2318 if ( upper(l_change_signer)='ADHOC' and upper(l_rulevalues(i).input_value) ='NONE') then
2319 l_change_signer:= l_rulevalues(i).input_value;
2320 elsif ( upper(l_change_signer)='ALL' and upper(l_rulevalues(i).input_value) ='NONE') then
2321 l_change_signer:= l_rulevalues(i).input_value;
2322 elsif ( upper(l_change_signer)='ALL' and upper(l_rulevalues(i).input_value) ='ADHOC') then
2323 l_change_signer:= l_rulevalues(i).input_value;
2324 end if;
2325 -- Bug 3161859 : End
2326
2327 --Bug 4160412: Start
2328 --Get the signature mode parameter if found.
2329 elsif l_rulevalues(i).input_name = EDR_CONSTANTS_GRP.G_SIGNATURE_MODE then
2330 l_temp_signature_mode := l_rulevalues(i).input_value;
2331 --Bug 4160412: End
2332 end if;
2333 end loop;
2334 end if;
2335 -- Begin Bug 3334148
2336 -- Determine if sytlesheet belongs to most deterministic Rule
2337 -- Bug 3456530 : Start
2338 if (l_rule_erecord_required ='Y' and l_esign_required ='N') then
2339 -- Bug 3456530 : End
2340 l_style_sheet:=l_temp_style_sheet;
2341 l_style_sheet_ver:=l_temp_style_sheet_ver;
2342 end if;
2343 if l_rule_esign_required ='Y' then
2344 l_style_sheet:=l_temp_style_sheet;
2345 l_style_sheet_ver:=l_temp_style_sheet_ver;
2346
2347 --Bug 4160412: Start
2348 l_signature_mode := l_temp_signature_mode;
2349 --Bug 4160412: End
2350 end if;
2351 --End Bug 3334148
2352 --Start Bug 5158772
2353 IF l_force_erecord = 'Y' then
2354 l_style_sheet := l_temp_style_sheet;
2355 l_style_sheet_ver := l_temp_style_sheet_ver;
2356 wf_log_pkg.string(3, 'EDR_PSIG_rule.psig_rule','Force Ereocrd setting style sheet and version');
2357 end if;
2358 --End Bug 5158772
2359 end loop;
2360
2361 -- Bug 3161859 : Start
2362 if (l_change_signer_defined = 'N') then
2363 l_change_signer:= 'NONE';
2364 end if;
2365 -- Bug 3161859 : End
2366
2367 --ER # 2966866 start
2368 if (l_inter_event_mode = 'Y' and(l_relationship = EDR_CONSTANTS_GRP.g_erecord_only OR
2369 l_relationship = EDR_CONSTANTS_GRP.g_ignore_signature )) then
2370
2371 l_esign_required := 'N';
2372 -- Bug : 3499326 - start
2373 l_style_sheet := l_temp_style_sheet;
2374 l_style_sheet_ver := l_temp_style_sheet_ver;
2375 -- Bug : 3499326 - end
2376
2377 wf_log_pkg.string(3, 'EDR_PSIG_rule.psig_rule','Inter Event Mode and No eSignature');
2378 end if;
2379 --ER # 2966866 end
2380
2381
2382 -- Bug 3170251 : Start Get the File Extension of Template Associated with this event
2383 -- Bug 3456399 : Start - get the last three characters from file name
2384 L_STYLE_SHEET_TYPE := UPPER(SUBSTR(L_STYLE_SHEET,LENGTH(L_STYLE_SHEET)-2,LENGTH(L_STYLE_SHEET)));
2385 -- Bug 3456399 : End
2386 --Bug: 3170251 : End
2387
2388 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Signature Required :'||l_esign_required);
2389 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','eRecord Required :'||l_erecord_required);
2390 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Style Sheet :'||l_style_sheet);
2391 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','StyleSheet Version :'||l_style_sheet_ver);
2392 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','StyleSheet repository :'||l_style_sheet_repository);
2393
2394 --Diagnostics Start
2395 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2396 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_PARAM_LIST_EVT');
2397 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2398 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2399 FND_MESSAGE.SET_TOKEN('ESIG_VALUE',l_esign_required);
2400 FND_MESSAGE.SET_TOKEN('EREC_VALUE',l_erecord_required);
2401 FND_MESSAGE.SET_TOKEN('STYLE_SHEET',l_style_sheet);
2402 FND_MESSAGE.SET_TOKEN('STYLE_VER',l_style_sheet_ver);
2403 FND_MESSAGE.SET_TOKEN('STYLE_REP',l_style_sheet_repository);
2404 FND_MESSAGE.SET_TOKEN('XML_MAP_CODE',l_xml_map_code);
2405 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2406 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2407 FALSE
2408 );
2409 end if;
2410 --Diagnostics End
2411
2412
2413 --Bug 4150616: Start
2414 --We want to create the e-record for any the following conditions being true.
2415 --1. ESIG_REQUIRED = Y
2416 --2. EREC_REQUIRED = Y
2417 --3. Subscription parameter FORCE_ERECORD = Y
2418 IF (upper(l_esign_required)='Y' OR upper(l_erecord_required)='Y' or l_force_erecord = 'Y') THEN
2419 /* Read the Require Parameter */
2420
2421 --If this condition is true then it means that the e-record is being created
2422 --because FORCE_ERECORD is set to Y and either the profile option is switched off
2423 --or (ESIG_REQUIRED=N and EREC_REQUIRED = N).
2424 --Hence we would add an addittional parameter to the event payload which in
2425 --turn is added to the doc params which indicates that the e-record is being
2426 --created because FORCE_ERECORD is set to Y.
2427 --This parameter is named "FORCE_ERECORD_USED".
2428 if l_eres_profile_value = 'N' OR (upper(l_esign_required) = 'N' and upper(l_erecord_required) = 'N') then
2429 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_FORCE_ERECORD_USED,'Y',p_event.Parameter_List);
2430 end if;
2431 --Bug 4150616: End
2432
2433 l_audit_group := NVL(wf_event.getValueForParameter('EDR_AUDIT_GROUP',p_event.Parameter_List),
2434 P_EVENT.getEventName( ));
2435
2436 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','XML Map Code :'||l_xml_map_code );
2437 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Audit Group :'||l_audit_group );
2438 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','Setting ECX parameter');
2439
2440 /* Set Additional Parameters required by Esignature */
2441 /* wf_event.AddParameterToList('SUB_GUID', p_subscription_guid,p_event.Parameter_List); Subscription ID */
2442 wf_event.AddParameterToList('ECX_MAP_CODE', l_xml_map_code,p_event.Parameter_List); /* XML Map Code*/
2443 wf_event.AddParameterToList('ECX_DOCUMENT_ID', P_EVENT.getEventKey( ),p_event.Parameter_List); /* XML Document ID*/
2444 wf_event.AddParameterToList('TEXT_XSLNAME', l_style_sheet,p_event.Parameter_List); /* Style Sheet*/
2445 wf_event.AddParameterToList('TEXT_XSLVERSION', l_style_sheet_ver,p_event.Parameter_List); /* Style Sheet*/
2446 wf_event.AddParameterToList('HTML_XSLNAME', NULL,p_event.Parameter_List); /* Style Sheet*/
2447 wf_event.AddParameterToList('HTML_XSLVERSION', NULL,p_event.Parameter_List); /* Style Sheet*/
2448 wf_event.AddParameterToList('APPLICATION_CODE', lower(l_application_code),p_event.Parameter_List); /* Style Sheet*/
2449 wf_event.AddParameterToList('EDR_SIGN_REQUIRED', l_esign_required,p_event.Parameter_List); /* Signature Required*/
2450 wf_event.AddParameterToList('EDR_EREC_REQUIRED', l_erecord_required,p_event.Parameter_List); /* eRecord Required*/
2451 --Bug 3761813 : start
2452 wf_event.AddParameterToList('REDLINE_REQUIRED', nvl(l_redline_required,'N'),p_event.Parameter_List); /* redline Required*/
2453 --Bug 3761813 : END
2454
2455 -- Bug 3161859 : Start
2456 -- this parameter would be used to show whether or not to display
2457 -- the Update Signer button wouuld be enabled or disabled
2458 wf_event.AddParameterToList('EDR_CHANGE_SIGNERS', l_change_signer,p_event.Parameter_List); /* Change Signer Required*/
2459 -- Bug 3161859 : End
2460
2461 -- Bug 3170251 : Start - Store EREC_TEMPLATE_TYPE in the event parameter list so that its available throughout
2462 -- the rule function and pageflow till the workflow is alive for this event
2463 wf_event.AddParameterToList('EREC_TEMPLATE_TYPE', l_style_sheet_type,p_event.Parameter_List); /* Template Type */
2464 -- Bug 3170251 : End
2465
2466 -- Bug 5158510 :start
2467 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_SIGNATURE_MODE, l_signature_mode,p_event.Parameter_List);
2468 -- Bug 5158510 :end
2469 wf_log_pkg.string(1, 'EDR_PSIG_RULE.psig_rule','ECX Parameter set have been set' );
2470
2471 /* Generate XML Document */
2472 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','Calling ECX Standard package to generate XML' );
2473 -- Bug 3960236 : Start
2474 -- Fetch the payload parameter
2475 L_XML_GENERATION_API := wf_event.getValueForParameter('XML_GENERATION_API',p_event.Parameter_List);
2476
2477 -- Bug 3960236 : end
2478 --Bug 3667036 : Start
2479 --if event data is not null then don't call XML gateway again
2480 if dbms_lob.getlength(l_event_data) > 0 then
2481 L_XML_DOCUMENT := l_erecord_data;
2482 -- Bug 3960236 : Start
2483 elsif L_XML_GENERATION_API is NOT NULL then
2484 -- Check if call back function has been passed to generate XML document by product team
2485 begin
2486 --bug 4730592: start
2487 --changed the way the callback api is called dynamically
2488 --L_SQL_STR:= 'select '|| l_xml_generation_api || ' from dual';
2489 --execute immediate L_SQL_STR into L_XML_DOCUMENT;
2490 execute immediate 'begin :1:= '||l_xml_generation_api ||'; end;'
2491 using out l_xml_document;
2492 --bug 4730592: end
2493 if l_XML_DOCUMENT is NULL then
2494 raise EMPTY_XML_DOCUMENT;
2495 end if;
2496 exception
2497 when others then
2498 raise API_EXECUTION_ERROR;
2499 end;
2500
2501 --Bug 4529417: Start
2502 --We need to obtain the next sequence value from EDR_ERECORDS_S.
2503
2504 --insert the erecord id into the temp table
2505 --This would be picked up by the procedures called by the XML Map for attacments
2506 --Delete the global temp table before inserting a row
2507 delete edr_erecord_id_temp;
2508
2509 --select Unique event Id to be posted in temp tables
2510 SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL;
2511
2512 INSERT into edr_erecord_id_temp (document_id) values (l_edr_Event_id);
2513
2514 --Obtain the attachment string parameter.
2515 l_attachment_string_details := wf_event.getValueForParameter('EDR_PSIG_ATTACHMENT',p_event.Parameter_List);
2516
2517 --If the attachment string exists then call the attachment API to create the attachments.
2518 if l_attachment_string_details is not null then
2519 EDR_ATTACHMENTS_GRP.ADD_ERP_ATTACH(p_attachment_string => l_attachment_string_details);
2520 end if;
2521 --Bug 4529417: End
2522
2523
2524
2525
2526 -- Bug 3960236 : end
2527 else
2528 --otherwise call XML gateway
2529 --insert the erecord id into the temp table
2530 --This would be picked up by the procedures called by the XML Map for attacments
2531 --Delete the global temp table before inserting a row
2532 delete edr_erecord_id_temp;
2533
2534 --select Unique event Id to be posted in temp tables
2535 SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL;
2536
2537
2538 --Bug 3761813 : start
2539 --Bug 4306292: Start
2540 --Commenting changes made for red lining.
2541
2542 if ( nvl(l_redline_required,'N') = 'Y') then
2543 l_snapstatus:= PRE_TX_SNAPSHOT(l_edr_Event_id,l_xml_map_code,P_EVENT.getEventKey());
2544 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','After Calling Snapshot to generate XML'||P_EVENT.getEventKey( ));
2545 END IF;
2546 --Bug 4306292: End
2547 -- Bug 3761813 : end
2548
2549 INSERT into edr_erecord_id_temp (document_id) values (l_edr_Event_id);
2550 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','eRecord id: '||l_edr_Event_id||' has been sent to ECX');
2551
2552 L_XML_DOCUMENT:=ECX_STANDARD.GENERATE(p_event_name => P_EVENT.getEventName(),
2553 p_event_key => P_EVENT.getEventKey(),
2554 p_parameter_list => p_event.Parameter_List);
2555
2556 --Diagnostics Start
2557 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2558 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_ECX_GEN_EVT');
2559 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2560 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2561 FND_MESSAGE.SET_TOKEN('XML_MAP_CODE',l_xml_map_code);
2562 FND_MESSAGE.SET_TOKEN('TRANS','DB TO XML');
2563 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2564 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2565 FALSE
2566 );
2567 end if;
2568 --Diagnostics End
2569
2570 end if;
2571 --Bug 3667036 : End
2572
2573 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','XML Document Generated' );
2574
2575 --Bug 5724159 :Start
2576 --When the number of approvers is 0,Adhoc signer capability is ALL or ADHOC and the
2577 --Signature mode is SHORT,Error message needs to displayed. E-Record will not be captured in this scenario.
2578 IF(approverlist.count=0 and l_change_signer_defined ='Y' AND l_change_signer <>'NONE' and l_signature_mode='SHORT') THEN
2579 RAISE NO_SIGNERS_DEF_FOR_LITE_MODE;
2580 END IF;
2581 --Bug 5724159 :End
2582
2583 --Bug 3437422: Start
2584 --call an api that would find out of the xml contains the user_data
2585 --placeholder and replace it with the actual contents of the user_data
2586 --node.
2587 edr_utilities.replace_user_data_token(l_xml_document);
2588 --Bug 3437422: End
2589 --To make eRecord generation storing autonomous
2590 --Moved all of the following code in this IF clause
2591 --to STROE_ERECORD Local Procedure
2592 --
2593 -- Bug Fix 3143107
2594 -- Removed elsif l_erecord_required = Y clause as we already know that
2595 -- we need to generate eRecord irrespective of l_erecord_required value
2596 -- we will use l_esign_required required in STORE_ERECORD to move the eRecord
2597 -- status to 'COMPLETE'(l_esign_required = 'N') or 'ERROR' (l_esign_required ='Y')
2598
2599 STORE_ERECORD(p_XML_DOCUMENT => l_XML_DOCUMENT,
2600 p_style_sheet_repository => l_style_sheet_repository,
2601 p_style_sheet => l_style_sheet,
2602 p_style_sheet_ver => l_style_sheet_ver,
2603 p_application_code => l_application_code,
2604 p_edr_event_id => l_edr_event_id ,
2605 p_esign_required => l_esign_required,
2606 p_subscription_guid => p_subscription_guid,
2607 x_event => p_event);
2608 --Bug 4590037
2609 --Set the XML document to CLOB datatype of the event
2610 P_EVENT.setEventData(L_XML_DOCUMENT);
2611 --Bug 4590037
2612
2613 --Bug 4150616: Start
2614 --Start the workflow process only if the profile option is switched on.
2615 IF upper(l_esign_required)='Y' and l_eres_profile_value = 'Y' THEN
2616 --Bug 4150616: End
2617
2618 --Bug 3761813 : start
2619 --Bug 4306292: Start
2620 --Commenting changes made for red lining.
2621 if (l_style_sheet_type = 'XSL' and nvl(l_redline_required,'N') = 'Y' ) then
2622 select DIFF_XML into L_XML_DOCUMENT from EDR_REDLINE_TRANS_DATA where EVENT_ID = l_edr_event_id ;
2623 -- Bug 5167207 : start
2624 --Set the XML document to CLOB datatype of the event
2625 P_EVENT.setEventData(L_XML_DOCUMENT);
2626 -- Bug 5167207 : end
2627 end if;
2628 --Bug 4306292: End
2629 --Bug 3761813 : end
2630
2631 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','XML Document Set to event Data' );
2632
2633 /* GET Approver List.
2634 ame_api.getAllApprovers(applicationIdIn=>l_application_Id,
2635 transactionIdIn=>P_EVENT.getEventKey( ),
2636 transactionTypeIn=>NVL(l_ame_transaction_type,P_EVENT.getEventName( )),
2637 approversOut=>approverList); */
2638
2639 --Diagnostics Start
2640 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2641 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_APPROVER_COUNT_EVT');
2642 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2643 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2644 FND_MESSAGE.SET_TOKEN('COUNT',approverList.count);
2645 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2646 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2647 FALSE
2648 );
2649 end if;
2650 --Diagnostics End
2651 --Bug 5724159 : Start
2652 /*check whether number of approvers is zero,Change signer property is defined and its value is
2653 NONE, List Of signers should not be launched in this scenario, return success*/
2654
2655 IF (approverList.count=0 AND l_change_signer_defined ='Y' AND l_change_signer ='NONE') THEN
2656 return 'SUCCESS' ;
2657 END IF;
2658 --Bug 5724159 : End
2659
2660 -- BUG 3567868 Start
2661 -- IF No Approver but Change Signer is 'ADHOC' or 'ALL'
2662 IF ( (approverList.count > 0) OR (upper(l_change_signer_defined) = 'Y') or l_lite_mode) THEN
2663 -- BUG 3567868 END
2664
2665
2666 --Bug 4577122: Start
2667 --validate the voting regime and then proceed
2668 validate_voting_regime
2669 (
2670 p_approver_list => approverList,
2671 x_valid_regime => l_valid_regime,
2672 x_voting_regime => l_voting_regime
2673 );
2674
2675 if (l_valid_regime = 'N') then
2676 raise INVALID_FIRST_VOTER_WINS_SETUP;
2677 else
2678 if (l_voting_regime <> ame_util.firstApproverVoting ) then
2679 l_voting_regime := 'X';
2680 end if;
2681
2682 --set the voting regime on the workflow attribute
2683 wf_event.AddParameterToList
2684 ('AME_VOTING_REGIME', l_voting_regime,p_event.Parameter_List);
2685
2686 end if;
2687 --Bug 4577122: End
2688
2689 --Bug 4160412: Start
2690 --Check the value of signature mode.
2691 --If it is set to lite then, fetch the value of the attributes APPROVING_USERS,
2692 --APPROVER_COUNT and APPROVING RESPONSIBILITY.
2693 --Bug 5891879 :Start
2694 --Defaults Signature mode to SHORT when the src application type is KIOSK
2695 IF l_source_application_type = EDR_CONSTANTS_PUB.g_kiosk_mode then
2696 l_signature_mode := EDR_CONSTANTS_GRP.G_ERES_LITE;
2697 end if;
2698 --Bug 5891879 :End
2699
2700 --Bug 4543216: Start
2701 if l_signature_mode = EDR_CONSTANTS_GRP.G_ERES_LITE then
2702 --Bug 4543216: Start
2703
2704 --Diagnostics Start
2705 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2706 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_SIGN_MODE_EVT');
2707 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2708 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2709 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2710 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2711 FALSE
2712 );
2713 end if;
2714 --Diagnostics End
2715
2716 l_einitials_defer_mode := 'Y';
2717 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_SIGNATURE_MODE, l_signature_mode,p_event.Parameter_List);
2718 l_approver_count := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_APPROVER_COUNT,p_event.Parameter_List);
2719 l_approver_list := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_APPROVER_LIST,p_event.Parameter_List);
2720
2721 --If approver_count and approving users are set, then raise an exception.
2722 if l_approver_count is not null and l_approver_list is not null then
2723 RAISE APPROVING_USERS_PARAMS_ERR;
2724 end if;
2725
2726 --If approving users are set, then fetch the approver list from the comma separated string.
2727 if l_approver_list is not null then
2728
2729 --Diagnostics Start
2730 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2731 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_APPR_LIST_EVT');
2732 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2733 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2734 FND_MESSAGE.SET_TOKEN('APPROVER_LIST',l_approver_list);
2735 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2736 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2737 FALSE
2738 );
2739 end if;
2740 --Diagnostics End
2741
2742 EDR_UTILITIES.GET_APPROVER_LIST(P_APPROVING_USERS => L_APPROVER_LIST,
2743 X_APPROVER_LIST => L_CUSTOM_APPROVER_LIST,
2744 X_ARE_APPROVERS_VALID => L_ARE_APPROVERS_VALID,
2745 X_INVALID_APPROVERS => L_INVALID_APPROVERS);
2746
2747 l_repeating_approvers := EDR_UTILITIES.ARE_APPROVERS_REPEATING(P_APPROVER_LIST => L_CUSTOM_APPROVER_LIST);
2748
2749 --If there are repeating approvers then raise an exception
2750 if l_repeating_approvers then
2751 raise REPEATING_APPROVERS_ERR;
2752 end if;
2753
2754 --If the approvers are invalid then raise an exception.
2755 if l_are_approvers_valid = 'N' then
2756 RAISE INVALID_APPROVING_USERS;
2757 end if;
2758 approverlist := L_CUSTOM_APPROVER_LIST;
2759
2760 for l_list_count in 1..approverlist.count loop
2761 if instr(approverlist(l_list_count).name,'#') = 1 then
2762 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_DO_RESPS_EXIST,'Y',p_event.Parameter_List);
2763 l_einitials_defer_mode := 'N';
2764 exit;
2765 end if;
2766 end loop;
2767 end if;
2768
2769 if l_approver_count is not null then
2770
2771 --Diagnostics Start
2772 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2773 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_APPR_COUNT_EVT');
2774 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2775 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2776 FND_MESSAGE.SET_TOKEN('APPROVER_COUNT',l_approver_count);
2777 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2778 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2779 FALSE
2780 );
2781 end if;
2782 --Diagnostics End
2783
2784 --Approver Count has been specified
2785 --Convert it into a number type.
2786 BEGIN
2787 l_approver_count_value := to_number(l_approver_count,'999999999999');
2788 --If approver count is less then 0 then raise an error.
2789 if l_approver_count_value < 0 then
2790 raise INVALID_APPR_COUNT_VALUE_ERR;
2791 end if;
2792 EXCEPTION
2793 --Approver Count attribute was a not a valid number. Hence raise an exception.
2794 WHEN VALUE_ERROR THEN
2795 RAISE INVALID_APPR_COUNT_ERR;
2796 END;
2797
2798
2799 --Make null entries in the approver list table type.
2800 for l_list_count in 1..l_approver_count_value loop
2801 L_CUSTOM_APPROVER_LIST(l_list_count).name := null;
2802 L_CUSTOM_APPROVER_LIST(l_list_count).approver_order_number := l_list_count;
2803 approverList := L_CUSTOM_APPROVER_LIST;
2804 end loop;
2805
2806 l_einitials_defer_mode := 'N';
2807
2808 end if;
2809
2810 --If approver count is zero, then do not start the pageflow process.
2811 --This is because adhoc functionality is not supported in EINITIALS.
2812 if approverList.count = 0 then
2813 return 'SUCCESS';
2814 end if;
2815
2816 --Evaluate the einitials deferred mode variable.
2817 if l_einitials_defer_mode = 'Y' then
2818 l_eres_defer_mode := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_DEFERRED_PARAM,p_event.Parameter_List);
2819 if l_eres_defer_mode is null or l_eres_defer_mode <> 'Y' then
2820 l_einitials_defer_mode := 'N';
2821 end if;
2822 END IF;
2823
2824 --Diagnostics Start
2825 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2826 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_DEFER_MODE_EVT');
2827 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2828 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2829 FND_MESSAGE.SET_TOKEN('DEFER_MODE',l_einitials_defer_mode);
2830 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2831 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2832 FALSE
2833 );
2834 end if;
2835 --Diagnostics End
2836
2837 --Add the e-initials deferred mode variable to the event.
2838 wf_event.AddParameterToList(EDR_CONSTANTS_GRP.G_EINITIALS_DEFER_MODE,l_einitials_defer_mode,p_event.Parameter_List);
2839 END IF;
2840 --Bug 4160412: End
2841
2842
2843 /* select Unique event Id to be posted in temp tables */
2844 /* 24-FEB-2003: CJ: commented one line below as the erecord id is already available
2845 in the local variable and added a line to delete the erecord id from temp
2846 table */
2847 /* SELECT EDR_ERECORDS_S.NEXTVAL into l_edr_Event_id from DUAL; */
2848 /* 24-FEB-2003: CJ : end */
2849 /* Set this value to correlation_id to be used by WF_RULE.DEFAULT_RULE funciton */
2850 P_EVENT.SETCORRELATIONID(l_edr_event_id);
2851 /* Select the workflow process for the subscription */
2852 select wf_process_type,wf_process_name
2853 into l_wftype, l_wfprocess
2854 from wf_event_subscriptions
2855 where guid = p_subscription_guid;
2856
2857 wf_event.AddParameterToList('#WF_PAGEFLOW_ITEMTYPE', l_wftype,p_event.Parameter_List); /* Page Flow Workflow type*/
2858 wf_event.AddParameterToList('#WF_PAGEFLOW_ITEMPROCESS', l_wfprocess,p_event.Parameter_List); /* Page Flow Workflow process*/
2859 wf_event.AddParameterToList('#WF_PAGEFLOW_ITEMKEY', l_edr_event_id,p_event.Parameter_List); /* Item Key*/
2860
2861 /* CAll Page Flow Creation API */
2862 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','Calling Page Flow Creation Procedure');
2863
2864 --Diagnostics Start
2865 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2866 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_WF_PARAMS_EVT');
2867 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
2868 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
2869 FND_MESSAGE.SET_TOKEN('ITEM_TYPE',l_wftype);
2870 FND_MESSAGE.SET_TOKEN('ITEM_KEY',l_edr_event_id);
2871 FND_MESSAGE.SET_TOKEN('PROCESS',l_wfprocess);
2872 FND_LOG.MESSAGE(FND_LOG.LEVEL_EVENT,
2873 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2874 FALSE
2875 );
2876 end if;
2877 --Diagnostics End
2878
2879 --Bug 4122622: Start
2880 --Obtain the value of the comma separated child e-record ID string.
2881 l_child_erecord_ids := wf_event.getValueForParameter(EDR_CONSTANTS_GRP.G_CHILD_ERECORD_IDS,p_event.Parameter_List);
2882
2883 --Set the approriate flags based on this value.
2884 if l_child_erecord_ids is not null then
2885 if instr(l_child_erecord_ids,',') > 0 then
2886 l_child_erecord_ids_set := true;
2887 else
2888 l_child_erecord_id_set := true;
2889 end if;
2890 end if;
2891
2892 --Set the approriate flags based on the parent e-record id.
2893 if(l_parent_erecord_id is not null and l_parent_erecord_id <> EDR_CONSTANTS_GRP.g_default_num_param_value) then
2894 l_parent_erecord_id_set := true;
2895 end if;
2896
2897 --Set the related e-record IDs URLs based on the flags.
2898 if l_parent_erecord_id_set then
2899 if l_child_erecord_id_set then
2900
2901 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_RELATED_ERECORDS_VIEW&retainAM=Y&parentERecordId='||
2902 l_parent_erecord_id||'&childERecordIds='||l_child_erecord_ids||'&addBreadCrumb=Y';
2903
2904 elsif l_child_erecord_ids_set then
2905 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_RELATED_ERECORDS_VIEW&edrNotifId=-&#NID-&retainAM=Y'||
2906 '&parentERecordId='||l_parent_erecord_id||'&addBreadCrumb=Y';
2907 else
2908
2909 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_ERECORD_DETAILS_VIEW&retainAM=Y' ||
2910 '&relatedERecordType=PARENT&fromNotifPG=Y&eRecordId='||l_parent_erecord_id;
2911 end if;
2912
2913 elsif l_child_erecord_ids_set then
2914
2915 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_RELATED_ERECORDS_VIEW&edrNotifId=-&#NID-&retainAM=Y&addBreadCrumb=Y';
2916
2917 elsif l_child_erecord_id_set then
2918
2919 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=EDR_ERECORD_DETAILS_VIEW&retainAM=Y' ||
2920 '&relatedERecordType=CHILD&fromNotifPG=Y&eRecordId='||l_child_erecord_ids;
2921
2922 end if;
2923
2924 wf_event.AddParameterToList('RELATED_ERECORDS',l_url,p_event.Parameter_List);
2925
2926 --Bug 4122622: End;
2927
2928 l_return_status:= CREATE_PAGEFLOW(p_event_id => l_edr_event_id,
2929 p_map_code => l_xml_map_code,
2930 p_ame_transaction_type => l_ame_transaction_type,
2931 p_audit_group => l_audit_group,
2932 p_eventP => p_event,
2933 p_subscription_guid => p_subscription_guid,
2934 P_approverlist => approverList,
2935 P_ERR_CODE => l_error,
2936 P_ERR_MSG => l_error_msg);
2937 wf_log_pkg.string(3, 'EDR_PSIG_RULE.psig_rule','Page Flow Returned '||l_return_status);
2938
2939 IF l_return_status = 'ERROR' OR L_ERROR is not null then
2940 raise PAGE_FLOW_FAILED;
2941 ELSE
2942 RETURN l_return_status;
2943 END IF;
2944
2945 ELSE
2946 /* No approver Required, Signature Not REquired */
2947 RETURN 'SUCCESS';
2948 END IF;
2949 END IF;
2950 end if;
2951 end if;
2952
2953 RETURN 'SUCCESS';
2954
2955 exception
2956 --Bug 4577122: Start
2957 when INVALID_FIRST_VOTER_WINS_SETUP then
2958 FND_MESSAGE.SET_NAME('EDR','EDR_BAD_FRST_VOTER_SETUP');
2959 fnd_message.set_token( 'EVENT', l_error_event);
2960 APP_EXCEPTION.RAISE_EXCEPTION;
2961 return EDR_CONSTANTS_GRP.g_error_status;
2962 --Bug 4577122: End
2963
2964 when NO_ENABLED_ERES_SUBSCRIPTION THEN
2965 FND_MESSAGE.SET_NAME('EDR','EDR_ERES_SUBSCRIPTION_DISABLED');
2966 fnd_message.set_token( 'EVENT', l_error_event);
2967 --Diagnostics Start
2968 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2969 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2970 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2971 FALSE
2972 );
2973 end if;
2974 --Diagnostics End
2975 APP_EXCEPTION.RAISE_EXCEPTION;
2976 return EDR_CONSTANTS_GRP.g_error_status;
2977
2978 when NO_ERES_SUBSCRIPTION THEN
2979 FND_MESSAGE.SET_NAME('EDR','EDR_NO_ERES_SUBSCRIPTION');
2980 fnd_message.set_token( 'EVENT', l_error_event);
2981
2982 --Diagnostics Start
2983 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2984 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
2985 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
2986 FALSE
2987 );
2988 end if;
2989 --Diagnostics End
2990
2991 APP_EXCEPTION.RAISE_EXCEPTION;
2992 return EDR_CONSTANTS_GRP.g_error_status;
2993
2994 WHEN MULTIPLE_ERES_SUBSCRIPTIONS THEN
2995 FND_MESSAGE.SET_NAME('EDR','EDR_MULTI_ERES_SUBSCRIPTIONS');
2996 fnd_message.set_token( 'EVENT',l_error_event);
2997 --Diagnostics Start
2998 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2999 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3000 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3001 FALSE
3002 );
3003 end if;
3004 --Diagnostics End
3005
3006 APP_EXCEPTION.RAISE_EXCEPTION;
3007 return EDR_CONSTANTS_GRP.g_error_status;
3008
3009 when PAGE_FLOW_FAILED then
3010 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', p_event.getEventName(), p_subscription_guid);
3011 wf_event.setErrorInfo(p_event,SQLERRM || 'ERROR');
3012 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
3013 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
3014 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
3015 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PSIG_RULE');
3016 --Diagnostics Start
3017 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3018 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3019 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3020 FALSE
3021 );
3022 end if;
3023 --Diagnostics End
3024 APP_EXCEPTION.RAISE_EXCEPTION;
3025 return EDR_CONSTANTS_GRP.g_error_status;
3026
3027 --Bug 3667036: Start
3028 --Catch the XML to XML transformation error.
3029 when ECX_XML_TO_XML_ERROR then
3030 FND_MESSAGE.SET_NAME('EDR','EDR_FWKRULE_XML_TO_XML_ERR');
3031 FND_MESSAGE.SET_TOKEN('EXCEPTION_MESSAGE',l_errbuf);
3032 FND_MESSAGE.SET_TOKEN('LOG',l_log_file);
3033 --Diagnostics Start
3034 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3035 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3036 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3037 FALSE
3038 );
3039 end if;
3040 --Diagnostics End
3041 APP_EXCEPTION.RAISE_EXCEPTION;
3042 return EDR_CONSTANTS_GRP.g_error_status;
3043 --Bug 3667036: End
3044
3045 --Bug 3893101: Start
3046 WHEN TRANSFORM_XML_VAR_ERROR then
3047 FND_MESSAGE.SET_NAME('EDR','EDR_FWK_TRANSFORM_XML_VAR_ERR');
3048 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3049 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3050 --Diagnostics Start
3051 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3052 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3053 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3054 FALSE
3055 );
3056 end if;
3057 --Diagnostics End
3058 APP_EXCEPTION.RAISE_EXCEPTION;
3059 return EDR_CONSTANTS_GRP.g_error_status;
3060 --Bug 3893101: End
3061
3062 --Bug 4160412: Start
3063 WHEN APPROVING_USERS_PARAMS_ERR THEN
3064 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_USERS_PARAMS_ERR');
3065 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3066 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3067 --Diagnostics Start
3068 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3069 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3070 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3071 FALSE
3072 );
3073 end if;
3074 --Diagnostics End
3075 APP_EXCEPTION.RAISE_EXCEPTION;
3076 return EDR_CONSTANTS_GRP.g_error_status;
3077
3078 WHEN INVALID_APPROVING_USERS THEN
3079 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_INVALID_USERS');
3080 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3081 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3082 FND_MESSAGE.SET_TOKEN('APPROVER_NAMES',l_invalid_approvers);
3083 --Diagnostics Start
3084 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3085 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3086 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3087 FALSE
3088 );
3089 end if;
3090 --Diagnostics End
3091 APP_EXCEPTION.RAISE_EXCEPTION;
3092 return EDR_CONSTANTS_GRP.g_error_status;
3093
3094 WHEN REPEATING_APPROVERS_ERR THEN
3095 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_REPEATING_APPRS');
3096 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3097 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3098 FND_MESSAGE.SET_TOKEN('STRING',l_approver_list);
3099 --Diagnostics Start
3100 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3101 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3102 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3103 FALSE
3104 );
3105 end if;
3106 --Diagnostics End
3107 APP_EXCEPTION.RAISE_EXCEPTION;
3108 return EDR_CONSTANTS_GRP.g_error_status;
3109
3110 WHEN INVALID_APPR_COUNT_ERR THEN
3111 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_INVALID_APPR_CNT');
3112 FND_MESSAGE.SET_TOKEN('APPROVER_COUNT',l_approver_count);
3113 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3114 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3115
3116 --Diagnostics Start
3117 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3118 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3119 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3120 FALSE
3121 );
3122 end if;
3123 --Diagnostics End
3124 APP_EXCEPTION.RAISE_EXCEPTION;
3125 return EDR_CONSTANTS_GRP.g_error_status;
3126
3127 WHEN INVALID_APPR_COUNT_VALUE_ERR THEN
3128 FND_MESSAGE.SET_NAME('EDR','EDR_EINITIALS_APPR_CNT_ERR');
3129 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3130 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3131
3132 --Diagnostics Start
3133 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3134 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3135 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3136 FALSE
3137 );
3138 end if;
3139 --Diagnostics End
3140 APP_EXCEPTION.RAISE_EXCEPTION;
3141 return EDR_CONSTANTS_GRP.g_error_status;
3142 --Bug 4160412: End
3143
3144 --Bug 3960236: Start
3145 WHEN API_EXECUTION_ERROR THEN
3146 FND_MESSAGE.SET_NAME('EDR','EDR_XMLCALLBACK_API_ERR');
3147 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3148 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3149 FND_MESSAGE.SET_TOKEN('APINAME',SQLERRM||': '||l_xml_generation_api);
3150 --Diagnostics Start
3151 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3152 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3153 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3154 FALSE
3155 );
3156 end if;
3157 --Diagnostics End
3158 APP_EXCEPTION.RAISE_EXCEPTION;
3159 return EDR_CONSTANTS_GRP.g_error_status;
3160 -- Bug 3960236 : end
3161
3162
3163 WHEN EMPTY_XML_DOCUMENT THEN
3164 FND_MESSAGE.SET_NAME('EDR','EDR_XMLCALLBACK_EMPTY_ERR');
3165 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3166 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3167 FND_MESSAGE.SET_TOKEN('APINAME',SQLERRM);
3168 --Diagnostics Start
3169 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3170 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3171 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3172 FALSE
3173 );
3174 end if;
3175 --Diagnostics End
3176 APP_EXCEPTION.RAISE_EXCEPTION;
3177 return EDR_CONSTANTS_GRP.g_error_status;
3178 -- Bug 3960236 : end
3179
3180 --Bug 5724159 :start
3181 --define a new exception to throw error message when there are no approvers, Signature mode is SHORT
3182 --and the Adhoc Signer capability is ALL or ADHOC.
3183 WHEN NO_SIGNERS_DEF_FOR_LITE_MODE THEN
3184 FND_MESSAGE.SET_NAME('EDR','EDR_NO_SIGNER_DEF_ERR');
3185 FND_MESSAGE.SET_TOKEN('EVENT_NAME',p_event.getEventName());
3186 FND_MESSAGE.SET_TOKEN('EVENT_KEY',p_event.getEventKey());
3187 --Diagnostics Start
3188 if (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3189 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3190 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3191 FALSE
3192 );
3193 end if;
3194 --Diagnostics End
3195 APP_EXCEPTION.RAISE_EXCEPTION;
3196 return EDR_CONSTANTS_GRP.g_error_status;
3197 --Bug 5724159 :End
3198
3199 when others then
3200 Wf_Core.Context('EDR_PSIG_RULE', 'PSIG_RULE', p_event.getEventName(), p_subscription_guid);
3201 wf_event.setErrorInfo(p_event,SQLERRM || 'ERROR');
3202 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
3203 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
3204 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_RULE');
3205 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PSIG_RULE');
3206 --Diagnostics Start
3207 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
3208 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3209 'edr.plsql.EDR_PSIG_RULE.PSIG_RULE',
3210 FALSE
3211 );
3212 end if;
3213 --Diagnostics End
3214
3215 APP_EXCEPTION.RAISE_EXCEPTION;
3216 return EDR_CONSTANTS_GRP.g_error_status;
3217
3218 end PSIG_RULE;
3219
3220 end EDR_PSIG_RULE;