1 PACKAGE BODY EDR_UTILITIES AS
2 /* $Header: EDRUTILB.pls 120.18.12000000.2 2007/02/12 09:33:47 pbhattip ship $ */
3 /* Print Procedure */
4
5 --Bug 3164491 : start
6 EDR_OVERRIDE_DETAILS_ERR Exception;
7 --Bug 3164491 : end
8
9 PROCEDURE PRINTCLOB(result IN OUT NOCOPY CLOB) Is
10 xmlstr varchar2(32767);
11 line varchar2(2000);
12 begin
13 NULL;
14 end PRINTCLOB;
15
16
17 /* Generate e Record from gateway*/
18 PROCEDURE VERIFY_SETUP (ERRBUF OUT NOCOPY VARCHAR2,
19 RETCODE OUT NOCOPY VARCHAR2,
20 P_EVENT IN VARCHAR2,
21 P_EVENT_KEY IN VARCHAR2
22 )IS
23
24 l_event_status varchar2(100);
25 l_sub_status varchar2(100);
26 l_sub_guid varchar2(4000);
27 evt wf_event_t;
28 l_application_id number;
29 l_application_code varchar2(32);
30 l_return_status varchar2(32);
31 l_application_name varchar2(240);
32 l_ame_transaction_Type varchar2(240);
33 l_xml_map_code varchar2(240);
34 l_xml_document clob;
35 l_transaction_name varchar2(240);
36
37 l_ruleids edr_utilities.id_List;
38 l_rulenames edr_utilities.string_List;
39
40 l_rulevalues EDR_STANDARD.ameruleinputvalues;
41
42 -- Bug 2674799 : start
43 approverList EDR_UTILITIES.approvers_Table;
44
45 -- ame approver api call variables
46 approvalProcessCompleteYN ame_util.charType;
47 itemClasses ame_util.stringList;
48 itemIndexes ame_util.idList;
49 itemIds ame_util.stringList;
50 itemSources ame_util.longStringList;
51 ruleIndexes ame_util.idList;
52 sourceTypes ame_util.stringList;
53
54
55 -- Bug 2674799 : end
56
57
58
59
60 l_user varchar2(240);
61 i integer;
62 CURSOR GET_EVT_SUBSCRIPTION_DETAILS IS
63 select b.guid,A.status,b.status
64 from
65 wf_events a, wf_event_subscriptions b
66 where a.GUID = b.EVENT_FILTER_GUID
67 and a.name = p_event
68 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
69 --Bug No 4912782- Start
70 and b.source_type = 'LOCAL'
71 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
72 --Bug No 4912782- End
73 l_no_enabled_eres_sub NUMBER;
74 MULTIPLE_ERES_SUBSCRIPTIONS EXCEPTION;
75 BEGIN
76 /* Check if Profile option is Defined or Not */
77
78 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_CHECK_SETUP');
79 fnd_message.set_token('EVENT', p_Event);
80 fnd_file.put_line(fnd_file.output, fnd_message.get);
81 fnd_file.new_line(fnd_file.output, 2);
82
83 if (fnd_profile.defined('EDR_ERES_ENABLED')) THEN
84 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_ERES_PROFILE_Y') );
85 else
86 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_ERES_PROFILE_N') );
87 end if;
88
89 fnd_file.new_line(fnd_file.output, 1);
90 if (fnd_timezones.GET_SERVER_TIMEZONE_CODE IS NULL) THEN
91 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_TIME_ZONE_N') );
92 else
93 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_TIME_ZONE_Y');
94 fnd_message.set_token('TMZONE', fnd_timezones.GET_SERVER_TIMEZONE_CODE);
95 fnd_file.put_line(fnd_file.output, fnd_message.get);
96 end if;
97
98 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_ERES_PROFILE_V');
99 fnd_message.set_token( 'ERESPROFILE', fnd_profile.value('EDR_ERES_ENABLED'));
100 fnd_file.put_line(fnd_file.output, fnd_message.get);
101 fnd_file.new_line(fnd_file.output, 1);
102
103
104 /*check if event and subscritptions are Enabled */
105 BEGIN
106 --
107 -- Start Bug Fix 3078516
108 -- Verify is more than one active ERES subscriptions are present
109 --
110 select count(*) INTO l_no_enabled_eres_sub
111 from
112 wf_events a, wf_event_subscriptions b
113 where a.GUID = b.EVENT_FILTER_GUID
114 and a.name = p_event
115 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
116 and b.STATUS = 'ENABLED'
117 --Bug No 4912782- Start
118 and b.source_type = 'LOCAL'
119 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
120 --Bug No 4912782- End
121 IF l_no_enabled_eres_sub > 1 THEN
122 RAISE MULTIPLE_ERES_SUBSCRIPTIONS;
123 ELSE
124 select count(*) INTO l_no_enabled_eres_sub
125 from
126 wf_events a, wf_event_subscriptions b
127 where a.GUID = b.EVENT_FILTER_GUID
128 and a.name = p_event
129 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
130 --Bug No 4912782- Start
131 and b.source_type = 'LOCAL'
132 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
133 --Bug No 4912782- End
134 IF l_no_enabled_eres_sub = 0 THEN
135 RAISE NO_DATA_FOUND;
136 ELSE
137 OPEN GET_EVT_SUBSCRIPTION_DETAILS;
138 LOOP
139 FETCH GET_EVT_SUBSCRIPTION_DETAILS INTO l_sub_guid,l_Event_status,l_sub_status;
140 EXIT WHEN GET_EVT_SUBSCRIPTION_DETAILS%NOTFOUND;
141 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_EVENT_STATUS');
142 fnd_message.set_token( 'EVENT', p_event);
143 fnd_message.set_token( 'STATUS', l_event_status);
144 fnd_file.put_line(fnd_file.output, fnd_message.get);
145
146 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_SUBSCRIBE_STATUS');
147 fnd_message.set_token( 'EVENT', p_event);
148 fnd_message.set_token( 'STATUS', l_sub_status);
149 fnd_file.put_line(fnd_file.output, fnd_message.get);
150 END LOOP;
151 CLOSE GET_EVT_SUBSCRIPTION_DETAILS;
152 END IF;
153 END IF;
154 --
155 -- End Bug Fix 3078516
156 --
157 EXCEPTION
158 WHEN MULTIPLE_ERES_SUBSCRIPTIONS THEN
159 FND_MESSAGE.SET_NAME('EDR','EDR_MULTI_ERES_SUBSCRP_ERR');
160 fnd_message.set_token( 'EVENT', p_event);
161 fnd_file.put_line(fnd_file.output, fnd_message.get);
162 RETURN;
163 WHEN NO_DATA_FOUND THEN
164 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_EVENTSUB_NOEXIST');
165 fnd_message.set_token( 'EVENT', p_event);
166 fnd_file.put_line(fnd_file.output, fnd_message.get);
167 RETURN;
168 END;
169
170 /*check if any AMe stuff is available */
171 wf_event_t.initialize(evt);
172 evt.setSendDate(sysdate);
173 evt.setEventName(p_event);
174 evt.setEventKey(p_event_key);
175 -- Bug 5639849 : Starts
176 -- No need of loading all subscription parameters, rather just read the
177 -- edr_ame_transaction_type and map code directly using edr API.
178 --l_return_status:=wf_rule.setParametersIntoParameterList(l_sub_guid,evt);
179
180 /* Check for User Defined Parameters, contains AME transactions Type
181 If Parameters are not specified, Assume Event name to be AME transaction Type */
182
183 l_ame_transaction_type := NVL(EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_AME_TRANSACTION_TYPE', l_sub_guid), evt.getEventName( ) );
184 -- Bug 5639849 : Ends
185
186 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_AME_TRANS_TYPE');
187 fnd_message.set_token( 'TRANSTYPE', l_ame_transaction_type);
188 fnd_file.put_line(fnd_file.output, fnd_message.get);
189
190
191 /* AME Processing, Select APPLICATION_ID of the Event. */
192 /* Required by AME. Assumption made here is OWNER_TAG will always be set to application Short Name*/
193 BEGIN
194 SELECT application_id,APPLICATION_SHORT_NAME into l_application_id,l_application_code
195 FROM FND_APPLICATION
196 WHERE APPLICATION_SHORT_NAME in
197 (SELECT OWNER_TAG from WF_EVENTS WHERE NAME=evt.getEventName( ));
198
199 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_EVENT_APP_CODE');
200 fnd_message.set_token( 'APPCODE', l_application_code);
201 fnd_file.put_line(fnd_file.output, fnd_message.get);
202 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_EVENT_APP_ID');
203 fnd_message.set_token( 'APPID', l_application_id);
204 fnd_file.put_line(fnd_file.output, fnd_message.get);
205
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_EVENT_APP_ID_NO') );
209 END;
210
211 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_AME_RULE_EVAL') );
212
213
214 --Bug 2674799: start
215
216 EDR_UTILITIES.GET_APPROVERS
217 (P_APPLICATION_ID => l_application_Id,
218 P_TRANSACTION_ID => evt.getEventKey( ),
219 P_TRANSACTION_TYPE => NVL(l_ame_transaction_type,evt.getEventName( )),
220 X_APPROVERS => approverList,
221 X_RULE_IDS => l_ruleids,
222 X_RULE_DESCRIPTIONS=> l_rulenames
223 );
224
225 --Bug 2674799: end
226
227 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_AME_APPROVER_NUM');
228 fnd_message.set_token( 'APPR_NUM', approverlist.count);
229 fnd_file.put_line(fnd_file.output, fnd_message.get);
230
231 select application_name into l_application_name
232 from ame_Calling_Apps
233 where FND_APPLICATION_ID=l_application_id
234 and TRANSACTION_TYPE_ID=NVL(l_ame_transaction_type,evt.getEventName( ))
235 --Bug 4652277: Start
236 --and end_Date is null;
237 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
238 --Bug 4652277: End
239
240 if approverList.count > 0 then
241 for i in 1..approverList.count loop
242
243 --Bug 2674799: start
244 l_user := approverList(i).name;
245 --Bug 2674799: end
246
247 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_AME_APPROVER_SEQ');
248 fnd_message.set_token( 'APPROVER', l_user);
249 fnd_message.set_token( 'APPR_SEQ', i);
250 fnd_file.put_line(fnd_file.output, fnd_message.get);
251
252 end loop;
253
254 for i in 1..l_ruleids.count loop
255 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_AME_RULE_APPLY');
256 fnd_message.set_token( 'RULEID', l_ruleids(i));
257 fnd_message.set_token( 'RULENAME', l_rulenames(i));
258 fnd_file.put_line(fnd_file.output, fnd_message.get);
259
260 -- Bug 3214495 : Start
261
262 EDR_STANDARD.GET_AMERULE_INPUT_VARIABLES( transactiontypeid =>NVL(l_ame_transaction_type,evt.getEventName( )),
263 ameruleid =>l_ruleids(i),
264 amerulename=>l_rulenames(i),
265 ameruleinputvalues=>l_rulevalues);
266
267 -- Bug 3214495 : End
268
269 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_INPUT_VAR_NUM');
270 fnd_message.set_token( 'NUM_INPUT', l_rulevalues.count);
271 fnd_file.put_line(fnd_file.output, fnd_message.get);
272 if l_rulevalues.count > 0 then
273 for i in 1..l_rulevalues.count loop
274 if l_rulevalues(i).input_name = 'ESIG_REQUIRED' then
275 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_INPUT_ESIG_REQ');
276 fnd_message.set_token( 'VAR_ESIGREQ', l_rulevalues(i).input_value);
277 fnd_file.put_line(fnd_file.output, fnd_message.get);
278 elsif l_rulevalues(i).input_name = 'EREC_REQUIRED' then
279 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_INPUT_EREC_REQ');
280 fnd_message.set_token( 'VAR_ERECREQ', l_rulevalues(i).input_value);
281 fnd_file.put_line(fnd_file.output, fnd_message.get);
282 elsif l_rulevalues(i).input_name = 'EREC_STYLE_SHEET' then
283 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_INPUT_EREC_XSL');
284 fnd_message.set_token( 'VAR_ERECXSL', l_rulevalues(i).input_value);
285 fnd_file.put_line(fnd_file.output, fnd_message.get);
286 elsif l_rulevalues(i).input_name = 'EREC_STYLE_SHEET_VER' then
287 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_INPUT_EREC_VER');
288 fnd_message.set_token( 'VAR_ERECVER', l_rulevalues(i).input_value);
289 fnd_file.put_line(fnd_file.output, fnd_message.get);
290 end if;
291 end loop;
292 end if;
293
294 END LOOP;
295 END IF;
296
297 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_DOC_GET_XML') );
298 -- Bug 5639849 : Starts
299 -- Read EDR_XML_MAP_CODE from subscription parameter rather than
300 -- event parameter list.
301 l_xml_map_code := NVL(EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_XML_MAP_CODE',l_sub_guid),
302 evt.getEventName( ));
303 -- Bug 5639849 : Ends
304
305 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_DOC_GET_MAP');
306 fnd_message.set_token( 'DOC_MAP', l_xml_map_code);
307 fnd_file.put_line(fnd_file.output, fnd_message.get);
308 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_DOC_GET_ID');
309 fnd_message.set_token( 'DOC_ID', evt.getEventKey());
310 fnd_file.put_line(fnd_file.output, fnd_message.get);
311
312 wf_event.AddParameterToList('ECX_MAP_CODE', l_xml_map_code,evt.Parameter_List); /* XML Map Code*/
313 wf_event.AddParameterToList('ECX_DOCUMENT_ID', evt.getEventKey( ),evt.Parameter_List); /* XML Document ID*/
314
315 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_DOC_GET_ECX') );
316
317 /* Generate XML Document */
318 L_XML_DOCUMENT:=ECX_STANDARD.GENERATE
319 ( p_event_name=>evt.getEventName( ),
320 p_event_key=>evt.getEventKey( ),
321 p_parameter_list=>evt.Parameter_List );
322
323 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_DOC_XML_GOT') );
324 -- 3186732 start: fnd_message.set_token limit 3000 char, fnd_message.get limit 2000 char
325 fnd_file.put_line(fnd_file.output, dbms_lob.SUBSTR(l_xml_document,32767) );
326 -- 3186732 end: removed fnd_message set_name/set_token/get statements, write to output directly
327
328 fnd_file.new_line(fnd_file.output, 2);
329 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_DOC_SETUP_OK') );
330
331
332 EXCEPTION
333 WHEN OTHERS THEN
334 fnd_file.put_line(fnd_file.output,SQLERRM);
335 fnd_file.new_line(fnd_file.output,2);
336 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_DOC_SETUP_NO') );
337 END VERIFY_SETUP;
338
339
340
341 PROCEDURE GENERATE_ERECORD( P_XML IN CLOB,
342 P_XSL IN CLOB,
343 P_DOC OUT NOCOPY VARCHAR2) IS
344
345 i_stylesheet xslprocessor.Stylesheet;
346 i_processor xslprocessor.Processor;
347 i_xmlDocFrag xmlDOM.DOMDocumentFragment;
348 i_domDocFrag xmlDOM.DOMDocumentFragment;
349 i_domNode xmlDOM.DOMNode;
350 i_xslt_dir varchar2(200);
351 i_fullpath varchar2(200);
352 i_string varchar2(2000);
353 l_xslt_payload clob;
354 XSL_parser xmlparser.parser;
355 XML_parser xmlparser.parser;
356 l_xsl_doc xmldom.DOMDocument;
357 l_xml_doc xmldom.DOMDocument;
358 l_processed_xsl CLOB;
359 l_text varchar2(32767);
360 -- -- -- -- -- 3056514 fix -- --
361 i_Doc xmlDOM.DOMDocument;
362 l_node_type pls_integer;
363 -- -- -- -- -- 3056514 fix -- --
364
365 begin
366 /* convert i_xml_file from CLOB to DOMNode and set in ecx_utils.g_xmldoc */
367 xml_parser := xmlparser.newParser;
368 xmlparser.parseCLOB(xml_parser, P_XML);
369 l_xml_doc := xmlparser.getDocument(xml_parser);
370 ecx_utils.g_xmldoc:=xmlDOM.makeNode(l_xml_doc);
371 xmlParser.freeParser(xml_parser);
372
373 /* convert l_xslt_paylod from clob to DOMDocument */
374 xsl_parser := xmlparser.newParser;
375 xmlparser.parseCLOB(xsl_parser, P_XSL);
376 l_xsl_doc := xmlparser.getDocument(xsl_parser);
377
378 /* get the stylesheet */
379 i_stylesheet := xslprocessor.newStyleSheet(l_xsl_doc, null);
380 i_processor := xslprocessor.newProcessor;
381
382 -- 3056514 fix, distinguish the internal node type to make different document node for xslprocessor
383 l_node_type := xmlDOM.getNodeType(ecx_utils.g_xmldoc);
384 if l_node_type = xmlDOM.DOCUMENT_NODE then
385 i_Doc := xmlDOM.makeDocument(ecx_utils.g_xmldoc);
386 i_xmlDocFrag := xslprocessor.processXSL(i_processor,i_stylesheet,i_Doc);
387 elsif l_node_type = xmlDOM.DOCUMENT_FRAGMENT_NODE then
388 i_domDocFrag := xmlDOM.makeDocumentFragment(ecx_utils.g_xmldoc);
389 i_xmlDocFrag := xslprocessor.processXSL(i_processor,i_stylesheet,i_domDocFrag);
390 end if;
391 -- i_domDocFrag := xmlDOM.makeDocumentFragment(ecx_utils.g_xmldoc);
392 -- i_xmlDocFrag := xslprocessor.processXSL(i_processor,i_stylesheet,i_domDocFrag);
393 -- -- 3056514 fix endend -- -- --
394
395 i_domNode := xmlDOM.makeNode(i_xmlDocFrag);
396 ecx_utils.g_xmldoc := i_domNode;
397 xmlDOM.writeTobuffer(ecx_utils.g_xmldoc, l_text);
398 p_doc:=l_text;
399
400 /*free all the used variables*/
401 if (xsl_parser.id <> -1)
402 then
403 xmlParser.freeParser(xsl_parser);
404 end if;
405 if (not xmldom.isNull(l_xsl_doc))
406 then
407 xmldom.freeDocument(l_xsl_doc);
408 end if;
409 exception
410 /* Put All DOM Parser Exceptions Here. */
411 WHEN OTHERS THEN
412 if (xsl_parser.id <> -1)
413 then
414 xmlParser.freeParser(xsl_parser);
415 end if;
416 if (not xmldom.isNull(l_xsl_doc))
417 then
418 xmldom.freeDocument(l_xsl_doc);
419 end if;
420 raise;
421 END GENERATE_ERECORD;
422
423 /* Generate XML */
424 PROCEDURE GENERATE_XML(P_MAP_CODE IN VARCHAR2,
425 P_DOCUMENT_ID IN VARCHAR2,
426 P_XML OUT NOCOPY CLOB,
427 P_ERROR_CODE OUT NOCOPY NUMBER,
428 P_ERROR_MSG OUT NOCOPY VARCHAR2,
429 P_LOG_FILE OUT NOCOPY VARCHAR2
430 )
431 IS
432 result CLOB;
433 retcode pls_integer;
434 errmsg varchar2(2000);
435 logfile varchar2(200);
436 begin
437 ecx_outbound.getXML(i_map_code => P_MAP_CODE,
438 i_document_id => P_DOCUMENT_ID,
439 i_debug_level => 6,
440 i_xmldoc => P_XML,
441 i_ret_code => P_ERROR_CODE,
442 i_errbuf => P_ERROR_MSG,
443 i_log_file => P_LOG_FILE);
444
445 replace_user_data_token(p_xml);
446
447 END GENERATE_XML;
448
449 PROCEDURE TEMP_DATA_CLEANUP(ERRBUF OUT NOCOPY VARCHAR2,
450 RETCODE OUT NOCOPY VARCHAR2)
451 IS
452
453 BEGIN
454
455 fnd_file.put_line(fnd_file.output, fnd_message.get_string('EDR', 'EDR_UTIL_PLS_CLEAN_TEMP') );
456
457 DELETE EDR_ERECORDS where ERECORD_SIGNATURE_STATUS not in ('PENDING');
458
459 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_CLEAN_EREC');
460 fnd_message.set_token( 'CLN_NUMREC', SQL%ROWCOUNT);
461 fnd_file.put_line(fnd_file.output, fnd_message.get);
462
463 DELETE EDR_ESIGNATURES where EVENT_ID not in (SELECT EVENT_ID from EDR_ERECORDS);
464
465 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_CLEAN_ESIG');
466 fnd_message.set_token( 'CLN_NUMSIG', SQL%ROWCOUNT);
467 fnd_file.put_line(fnd_file.output, fnd_message.get);
468
469 -- Bug 3761813 rvsingh start
470 --Bug 4306292: Start
471 --Commenting the changes made for the red lining project.
472
473 DELETE EDR_REDLINE_TRANS_DATA where EVENT_ID not in (SELECT EVENT_ID from EDR_ERECORDS);
474
475 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_CLEAN_REDLINE');
476 fnd_message.set_token( 'CLN_NUMREDLINE', SQL%ROWCOUNT);
477 fnd_file.put_line(fnd_file.output, fnd_message.get);
478
479 --Bug 4306292: End
480 -- Bug 3761813 rvsing stop
481
482 DELETE EDR_TRANS_QUERY_TEMP;
483
484 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_CLEAN_TQRY');
485 fnd_message.set_token( 'CLN_NUMQRY', SQL%ROWCOUNT);
486 fnd_file.put_line(fnd_file.output, fnd_message.get);
487
488 --Bug 5256904: Start
489 DELETE FROM EDR_ERESPARAMETERS_T
490 WHERE PARENT_TYPE = 'EDR_XDOC_PARAMS';
491 --Bug 5256904: End
492
493 --Bug 3667036: Start
494 --In addition to previous cleanup operations, as a part of the OAF project
495 --we also have to cleanup the temp data created as a part of OAF processing
496
497
498 EDR_ERES_EVENT_PVT.DELETE_ERECORDS();
499 --Bug 3667036: End
500
501 -- Bug 3776079 : Added delete statements to cleanup edr
502 -- print utility temporary data.
503
504 DELETE FROM EDR_PREPARE_DOCUMENT_TEMP WHERE REQUEST_ID IN
505 (SELECT REQUEST_ID FROM EDR_COLLATE_PRINT_TEMP
506 WHERE CREATION_DATE < (SYSDATE - 2) );
507 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_CLEAN_PREP');
508 fnd_message.set_token( 'CLN_NUMQRY', SQL%ROWCOUNT);
509 fnd_file.put_line(fnd_file.output, fnd_message.get);
510
511 DELETE FROM EDR_COLLATE_PRINT_TEMP where CREATION_DATE < (SYSDATE - 2);
512 fnd_message.set_name('EDR', 'EDR_UTIL_PLS_CLEAN_COLL');
513 fnd_message.set_token( 'CLN_NUMQRY', SQL%ROWCOUNT);
514 fnd_file.put_line(fnd_file.output, fnd_message.get);
515
516 -- Bug 3776079 : End
517
518 COMMIT;
519 END TEMP_DATA_CLEANUP;
520
521 --CJ 2/27/2003
522 --This function would take the original string and use the delimiter to return the substring
523 --from the start of the string till the delimiter. The original script along with the
524 --substring are returned to the calling function
525
526 FUNCTION GET_DELIMITED_STRING(p_original_string IN OUT NOCOPY VARCHAR2,
527 p_delimiter IN VARCHAR2)
528 return varchar2 IS
529 l_return_value VARCHAR2(1000);
530 l_location NUMBER;
531 l_length number;
532 BEGIN
533 if (p_original_string is null) then
534 l_return_value := null;
535 else
536 l_location := instr(p_original_string, p_delimiter);
537
538 if (l_location = 0) then
539 l_return_value := p_original_string;
540 p_original_string := null;
541 else
542 l_return_value := substr(p_original_string, 1,l_location-1);
543 l_length := length(p_original_string);
544 p_original_string := substr(p_original_string,l_location+1,(l_length - l_location));
545 end if;
546 end if;
547
548 return l_return_value;
549
550 END GET_DELIMITED_STRING;
551
552 PROCEDURE EDR_RAISE3( p_event_name IN varchar2,
553 p_event_key IN varchar2,
554 p_event_data IN clob default NULL,
555 p_param_list IN OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
556 p_send_date IN date default NULL,
557 p_param_value IN OUT NOCOPY FND_TABLE_OF_VARCHAR2_255 ) IS
558
559 --Bug 3207385: Start
560 --This variable would now be a simple name/value param type.
561 l_param_list fnd_wf_event.param_table;
562 --Bug 3207385: End
563 ith number := 1;
564 len number := 1;
565 BEGIN
566
567 len := p_param_list.COUNT;
568 for ith in 1..len LOOP
569 --l_param_list.extend;
570 --Bug 3207385: Start
571 --We just have to add the parameters directly into the parameter list.
572 --wf_event.AddParameterToList( p_param_list(ith), p_param_value(ith),lparam_list);
573 l_param_list(ith).param_name := p_param_list(ith);
574 l_param_list(ith).param_value := p_param_value(ith);
575 --Bug 3207385: End
576 end LOOP;
577
578 --Bug 3207385: Start
579 --wf_event.raise3(p_event_name, p_event_key, p_event_data, l_param_list, p_send_date);
580 --Call the new procedure "EDR_ERES_EVENT_PVT.RAISE_TABLE" to raise the business event.
581
582 EDR_ERES_EVENT_PVT.RAISE_TABLE(P_EVENT_NAME,
583 P_EVENT_KEY,
584 P_EVENT_DATA,
585 L_PARAM_LIST,
586 L_PARAM_LIST.COUNT,
587 P_SEND_DATE);
588
589 --Recreate the param list and param value objects.
590 p_param_list := FND_TABLE_OF_VARCHAR2_255();
591 p_param_value := FND_TABLE_OF_VARCHAR2_255();
592
593 for ith in 1..l_param_list.count LOOP
594 p_param_list.extend;
595 p_param_list(ith) := l_param_list(ith).param_name;
596
597 p_param_value.extend;
598 p_param_value(ith) := l_param_list(ith).param_value;
599 end LOOP;
600 --Bug 3207385: End
601 END EDR_RAISE3;
602
603 PROCEDURE EDR_NTF_HISTORY( document_id in varchar2,
604 display_type in varchar2,
605 document in out nocopy varchar2,
606 document_type in out nocopy varchar2) IS
607
608 --Bug: 3499311 : Start - Specified Number format in call TO_NUMBER
609 cursor NTF_HISTORY(document_id varchar2) is
610 --Bug 4160412: Start
611 --Modified query to ensure that signature details are fetched even though
612 --signing reason is null.
613 select SIGNATURE_SEQUENCE,USER_NAME,
614 B.MEANING SIGNATURE_STATUS,
615 SIGNATURE_TIMESTAMP,
616 C.MEANING SIGNATURE_TYPE,
617 (SELECT D.MEANING FROM WF_LOOKUPS D WHERE D.LOOKUP_CODE=A.SIGNATURE_REASON_CODE AND
618 D.LOOKUP_TYPE='SIGNING_REASON_CODES') SIGNATURE_REASON_CODE,
619 SIGNER_COMMENTS,
620 --Bug 4113995: Start
621 --Including overriding comments in the cursor.
622 SIGNATURE_OVERRIDING_COMMENTS
623 --Bug 4113995: End
624 from EDR_ESIGNATURES A,
625 FND_LOOKUPS B,
626 WF_LOOKUPS C
627 WHERE EVENT_ID=to_number(document_id,'999999999999.999999' ) AND
628 A.SIGNATURE_STATUS=B.LOOKUP_CODE AND
629 B.LOOKUP_TYPE='EDR_PSIG_ESIGNATURE' AND
630 C.LOOKUP_CODE=A.SIGNATURE_TYPE AND
631 C.LOOKUP_TYPE='PSIG_ESIGN_SIGNER_LOOKUP'
632 --Bug 4272262: Start
633 --Convert signature sequence to a number value before performing the
634 --order by operation.
635 order by to_number(SIGNATURE_SEQUENCE,'999999999999.999999') desc;
636 --Bug 4272262: End
637
638 --Bug 4160412: End
639 --Bug: 3499311 : End
640
641 table_direction varchar2(1);
642 table_type varchar2(1) ;
643 table_width varchar2(8);
644 table_border varchar2(2);
645 table_cellpadding varchar2(2);
646 table_l_cellspacing varchar2(2) ;
647 table_bgcolor varchar2(7);
648 th_bgcolor varchar2(7) ;
649 th_fontcolor varchar2(7) ;
650 th_fontface varchar2(80) ;
651 th_fontsize varchar2(2);
652 td_bgcolor varchar2(7) ;
653 td_fontcolor varchar2(7);
654 td_fontface varchar2(80);
655 td_fontsize varchar2(2);
656
657 l_itype varchar2(30);
658 l_ikey varchar2(240);
659 l_actid number;
660 l_result_type varchar2(30);
661 l_result_code varchar2(30);
662 l_action varchar2(80);
663 l_owner_role varchar2(320);
664 l_owner varchar2(320);
665 l_begin_date date;
666 i pls_integer;
667 j pls_integer;
668 l_delim varchar2(1) ;
669 l_cells wf_notification.tdType;
670 l_result varchar2(32767);
671 L_SIGNATURE_SEQUENCE number;
672 L_USER_NAME varchar2(240);
673 L_SIGNATURE_STATUS varchar2(240);
674 L_SIGNATURE_TIMESTAMP DATE;
675 LSIGNER_COMMENTS varchar2(4000);
676 begin
677
678 --Bug 4074173 : start
679 table_direction := 'L';
680 table_type := 'V';
681 table_width := '100%';
682 table_border := '0';
683 table_cellpadding := '3';
684 table_l_cellspacing := '1';
685 table_bgcolor := 'white';
686 th_bgcolor := '#cccc99';
687 th_fontcolor := '#336699';
688 th_fontface := 'Arial, Helvetica, Geneva, sans-serif';
689 th_fontsize := '2';
690 td_bgcolor := '#f7f7e7';
691 td_fontcolor := 'black';
692 td_fontface := 'Arial, Helvetica, Geneva, sans-serif';
693 td_fontsize := '2';
694
695 l_delim := ':';
696 --Bug 4074173 : end
697
698 -- Bug 3841676 : Start
699 -- Display EDR Signature History based on WF Document Type
700 IF display_type = WF_NOTIFICATION.doc_text THEN
701 document := NULL;
702 ELSE
703 j := 1;
704 l_cells(j) := 'S:'||FND_MESSAGE.GET_STRING('EDR','EDR_NTF_SEQUENCE');
705 j := j+1;
706 l_cells(j) := 'S:'||FND_MESSAGE.GET_STRING('EDR','EDR_NTF_NAME');
707 j := j+1;
708 l_cells(j) := 'S:'||FND_MESSAGE.GET_STRING('EDR','EDR_NTF_STATUS');
709 j := j+1;
710 l_cells(j) := 'S:'||FND_MESSAGE.GET_STRING('EDR','EDR_NTF_DATETIME');
711 j := j+1;
712 l_cells(j) := 'S:'||FND_MESSAGE.GET_STRING('EDR','EDR_NTF_SIGNATURE_TYPE');
713 j := j+1;
714 l_cells(j) := 'S:'||FND_MESSAGE.GET_STRING('EDR','EDR_NTF_REASON_CODE');
715 j := j+1;
716 l_cells(j) := 'S:'||FND_MESSAGE.GET_STRING('EDR','EDR_NTF_COMMENTS');
717 j := j+1;
718 --Bug 4113995: Start
719 --Including overriding details in the signature history table.
720 l_cells(j) := 'S:'||FND_MESSAGE.GET_STRING('EDR','EDR_NTF_OVERRIDING_DETAILS');
721 j := j+1;
722 --Bug 4113995: End
723
724 i := 0;
725 for histr in NTF_HISTORY(document_id) loop
726 l_cells(j) := histr.SIGNATURE_SEQUENCE;
727 j := j+1;
728 l_cells(j) := 'S:'||wf_directory.GetRoledisplayname(histr.USER_NAME);
729 j := j+1;
730 l_cells(j) := 'S:'||histr.signature_status;
731 j := j+1;
732 --Bug 4687718: Start
733 l_cells(j) := 'S:'||nvl(fnd_date.date_to_displayDT(histr.signature_timestamp,fnd_timezones.get_server_timezone_code),'&'||'nbsp;');
734 --Bug 4687718: End
735 j := j+1;
736 l_cells(j) := 'S:'||nvl(histr.signature_type,'&'||'nbsp;');
737 j := j+1;
738 l_cells(j) := 'S:'||nvl(histr.SIGNATURE_REASON_CODE,'&'||'nbsp;');
739 j := j+1;
740 l_cells(j) := 'S:'||nvl(histr.signer_comments,'&'||'nbsp;');
741 j := j+1;
742 --Bug 4113995: Start
743 --Including overriding details in the signature history table.
744 l_cells(j) := 'S:'||nvl(histr.SIGNATURE_OVERRIDING_COMMENTS,'&'||'nbsp;');
745 j := j+1;
746 --Bug 4113995: End
747 i := i+1;
748 end loop;
749 table_width := '100%';
750 --Bug 4113995: Start
751 --The signature history table will now have 8 columns.
752 wf_notification.NTF_Table(l_cells,8,'HL',l_result);
753 --Bug 4113995: End
754
755 -- Bug 3787607 : Start
756 -- Removed hard-coded usage of colors and used corresponding Style Sheet
757 -- classes
758
759 document:='<table><tr>'||
760 '<td width="100%"><h2 class="x3w">'||
761 FND_MESSAGE.GET_STRING('EDR','EDR_NTF_SIGNATURE_HISTORY')||
762 '</h2></td></tr></table>'||
763 '<table class="x1h" cellpadding="1" cellspacing="0" border="0" width="100%"><tr>' ||
764 '<th scope="col" class="x1r">'||
765 l_result ||'</th></tr> </table>';
766
767 -- Bug 3787607 : End
768 END IF;
769 -- Bug 3841676 : End
770
771 document_type:='text/html';
772
773 exception
774 when OTHERS then
775 wf_core.context('EDR_NTF_HISTORY', 'History',document_id);
776 raise;
777 end EDR_NTF_HISTORY;
778
779 /* Get the standard WHO columns of a table row */
780 PROCEDURE getWhoColumns(creation_date out nocopy date,
781 created_by out nocopy number,
782 last_update_date out nocopy date,
783 last_updated_by out nocopy number,
784 last_update_login out nocopy number)
785 is
786 begin
787 creation_date := sysdate;
788 created_by := fnd_global.user_id();
789 last_update_date := sysdate;
790 last_updated_by := fnd_global.user_id();
791 last_update_login := fnd_global.login_id();
792
793 end getWhoColumns;
794
795 --Bug 3164491: start
796 /* Get the userdisplayname for the fnd user */
797 function getUserDisplayName (p_username in varchar2)
798 return varchar2
799 is
800 l_displayname varchar2(400);
801 l_emailaddress varchar2(400);
802 l_notification_preference varchar2(30);
803 l_language varchar2(30);
804 l_teritory varchar2(30);
805
806 begin
807 -- we are not using the wf_directory.getroledisplayname
808 -- because we get a invalid number error when username
809 -- contains a colon
810 wf_directory.getroleinfo( ROLE => p_username,
811 DISPLAY_NAME => l_displayname,
812 EMAIL_ADDRESS => l_emailaddress,
813 NOTIFICATION_PREFERENCE => l_notification_preference,
814 LANGUAGE => l_language,
815 TERRITORY => l_teritory
816 );
817 return l_displayname;
818 end getUserDisplayName;
819
820 /* get overriding details for the username */
821 function getOverridingDetails (p_username in varchar2)
822 return varchar2
823 is
824 l_error_num number;
825 l_error_msg varchar2(4000);
826 l_overriding_approver varchar2(80);
827 l_override varchar2(4000);
828 begin
829
830 EDR_STANDARD.FIND_WF_NTF_RECIPIENT (P_ORIGINAL_RECIPIENT => p_username,
831 P_MESSAGE_TYPE => 'EDRPSIGF',
832 P_MESSAGE_NAME => 'PSIG_EREC_MESSAGE_BLAF',
833 P_RECIPIENT => l_overriding_approver,
834 P_NTF_ROUTING_COMMENTS => l_override,
835 P_ERR_CODE => l_error_num,
836 P_ERR_MSG => l_error_msg);
837 IF (l_ERROR_NUM > 0 ) THEN
838 RAISE EDR_OVERRIDE_DETAILS_ERR;
839 END IF;
840
841 return l_override;
842 end getOverridingDetails;
843
844 /* get actual recipient for the username */
845 function getActualRecipient (p_username in varchar2)
846 return varchar2
847 is
848 l_error_num number;
849 l_error_msg varchar2(4000);
850 l_overriding_approver varchar2(80);
851 l_override varchar2(4000);
852 begin
853
854 EDR_STANDARD.FIND_WF_NTF_RECIPIENT (P_ORIGINAL_RECIPIENT => p_username,
855 P_MESSAGE_TYPE => 'EDRPSIGF',
856 P_MESSAGE_NAME => 'PSIG_EREC_MESSAGE_BLAF',
857 P_RECIPIENT => l_overriding_approver,
858 P_NTF_ROUTING_COMMENTS => l_override,
859 P_ERR_CODE => l_error_num,
860 P_ERR_MSG => l_error_msg);
861 IF (l_ERROR_NUM > 0 ) THEN
862 RAISE EDR_OVERRIDE_DETAILS_ERR;
863 END IF;
864
865 return l_overriding_approver;
866
867 end getActualRecipient;
868
869 --Bug 3589701 : start
870 --Unsupport the functionality to search by group as ame has not released it.
871 --Whenever ame supports it, we will uncomment this.
872 /* Get Userid from Ame */
873 /*
874 procedure getUseridFromAme (p_groupname in varchar2,
875 x_userid out NOCOPY FND_TABLE_OF_VARCHAR2_255,
876 x_usergroupname out NOCOPY FND_TABLE_OF_VARCHAR2_255)
877 is
878 l_approval_group_id AME_APPROVAL_GROUPS.APPROVAL_GROUP_ID%TYPE;
879 l_approval_group_name AME_APPROVAL_GROUPS.NAME%TYPE;
880 l_parameter_values ame_util.longStringList;
881 l_parameter_names ame_util.stringList;
882 i integer;
883 ithelement integer := 0;
884 --get the amegroupid and groupname
885 cursor GET_AMEGROUP is select approval_group_id, name
886 from AME_APPROVAL_GROUPS
887 where upper(name) like p_groupname
888 and end_date is null;
889
890 BEGIN
891 -- Begin Bug FIX 3388988
892 x_userid :=FND_TABLE_OF_VARCHAR2_255();
893 x_usergroupname :=FND_TABLE_OF_VARCHAR2_255();
894 --End Bug Fix 3388988
895 open GET_AMEGROUP;
896 loop
897 fetch GET_AMEGROUP into l_approval_group_id, l_approval_group_name;
898 EXIT WHEN GET_AMEGROUP%NOTFOUND;
899 -- call ame for the groupid and get all the memebers
900 AME_APPROVAL_GROUP_PKG.GETGROUPMEMBERS (
901 APPROVALGROUPIDIN => l_approval_group_id,
902 MEMBERIDSOUT => l_parameter_values,
903 MEMBERTYPESOUT => l_parameter_names);
904
905 for i in 1 .. l_parameter_values.count loop
906 --if the parameter name is USER_ID use it
907 if ( (l_parameter_names(i) is not null) and
908 (upper(l_parameter_names(i)) = 'USER_ID') ) then
909 -- extend the array and add the userid to the list
910 x_userid.extend;
911 ithelement := ithelement + 1;
912 x_userid(ithelement) := l_parameter_values(i);
913 -- extend the array and add the groupname to the list
914 x_usergroupname.extend;
915 x_usergroupname(ithelement) := l_approval_group_name;
916 end if;
917 end loop;
918 end loop;
919 close GET_AMEGROUP;
920 END getUseridFromAme;
921 */
922 --Bug 3589701 : end
923 --Bug 3164491 : end
924
925 --Bug 3465204 : start
926 procedure getProfileValue (p_profile in varchar2, x_profileValue out nocopy varchar2)
927 is
928 begin
929 x_profileValue := fnd_profile.value(NAME => p_profile);
930
931 end getProfileValue;
932
933
934 procedure getProfileValueSpecific (p_profile in varchar2,
935 p_user_id in number default null,
936 p_responsibility_id in number default null,
937 p_application_id in number default null,
938 p_org_id in number default null,
939 p_server_id number default null,
940 x_profilevalue out nocopy varchar2)
941 is
942 begin
943 x_profileValue := fnd_profile.value_specific (NAME => p_profile,
944 USER_ID => p_user_id,
945 RESPONSIBILITY_ID => p_responsibility_id,
946 APPLICATION_ID => p_application_id,
947 ORG_ID => p_org_id,
948 SERVER_ID => p_server_id);
949 end getProfileValueSpecific;
950
951 --Bug 3465204 : end
952
953 --Bug 3437422: Start
954 FUNCTION GET_USER_DATA
955 RETURN VARCHAR2
956 AS
957 numeric_data_format varchar2(50);
958 default_numeric_format varchar2(20);
959 user_data varchar2(2000);
960 BEGIN
961
962 --Bug 4074173 : start
963 default_numeric_format := ',.';
964 --Bug 4074173 : end
965
966 numeric_data_format := fnd_profile.value('ICX_NUMERIC_CHARACTERS');
967 numeric_data_format := nvl(numeric_data_format, default_numeric_format);
968 user_data := '<NUMERIC_DATA><ICX_NUMERIC_CHARACTERS>'|| numeric_data_format||
969 '</ICX_NUMERIC_CHARACTERS></NUMERIC_DATA>';
970
971 return user_data;
972 END GET_USER_DATA;
973
974 PROCEDURE REPLACE_USER_DATA_TOKEN
975 (P_IN_XML IN OUT NOCOPY CLOB)
976 AS
977 user_data varchar2(2000);
978 user_data_token varchar2(40);
979 token_position integer;
980 BEGIN
981
982 --Bug 4074173 : start
983 user_data_token := '#EDR_USER_DATA';
984 --Bug 4074173 : end
985
986 token_position:= DBMS_LOB.INSTR
987 (lob_loc => p_in_xml,
988 pattern => user_data_token,
989 offset => 1,
990 nth => 1);
991
992 if (token_position> 0) then
993 user_data := get_user_data;
994 p_in_xml:= clob_replace(p_in_xml,user_data_token, user_data);
995 end if;
996
997 END REPLACE_USER_DATA_TOKEN;
998
999 FUNCTION CLOB_REPLACE
1000 (p_source IN CLOB,
1001 p_srch_str IN VARCHAR2,
1002 p_replace_str IN VARCHAR2)
1003 return CLOB
1004 AS
1005
1006 l_offset number;
1007 l_amount number;
1008 l_diff number;
1009 l_buffer VARCHAR2(32767);
1010 x_replaced_clob clob;
1011 l_temp_len number;
1012 l_temp_clob clob;
1013 l_len number;
1014 l_pos number;
1015 l_first_char VARCHAR2(1);
1016 BEGIN
1017
1018 DBMS_LOB.CREATETEMPORARY(x_replaced_clob, TRUE, DBMS_LOB.SESSION);
1019 DBMS_LOB.CREATETEMPORARY(l_temp_clob, TRUE, DBMS_LOB.SESSION);
1020
1021
1022 --Obtain the length of the source CLOB
1023 l_len:=dbms_lob.getlength(p_source);
1024
1025 --Set the amount to the 32 KB
1026 l_amount:=32767;
1027
1028 --Initial Offset is set to 1
1029 l_offset:=1;
1030
1031 --Obtain the difference
1032 l_diff:=l_len-l_offset;
1033
1034
1035 --If true then length of search is greater than that of the replacement string
1036 if length(p_srch_str)>=length(p_replace_str) then
1037
1038 --If this condition is true then size of CLOB is less than 32 KB
1039 if l_diff <= 32766 then
1040
1041 --Therefore convert the CLOB to VARCHAR2
1042 dbms_lob.read(p_source,l_len,1,l_buffer);
1043
1044 --Perform the replace operation
1045 l_buffer:=replace(l_buffer,p_srch_str,p_replace_str);
1046
1047 --Append the modified VARCHAR2 to an EMPTY CLOB.
1048 dbms_lob.writeappend(x_replaced_clob,length(l_buffer),l_buffer);
1049
1050
1051 --Size of input CLOB is greater than 32 KB
1052 else
1053
1054 --Obtain the First Character of the search string
1055 l_first_char:=substr(p_srch_str,1,1);
1056
1057 --Parse the CLOB in chunks of 32 KB
1058 while l_diff > 32766
1059
1060
1061
1062 loop
1063 --Read 32 KB of the CLOB into buffer
1064 dbms_lob.read(p_source,l_amount,l_offset,l_buffer);
1065
1066 --This statement is used to check for boundary condition
1067 --Check if the first character of the search String occurs anywhere,
1068 --at the boundary of the 32 KB buffer.
1069 l_pos:=instr(l_buffer,l_first_char,l_amount-length(p_srch_str)+2);
1070
1071 --If this condition is true then boundary condition has occured
1072 if l_pos>0
1073
1074 then
1075 --Set the amount to pos-1.
1076 l_amount:=l_pos-1;
1077
1078 --Repopulate the buffer to read only the above calculated amount
1079 l_buffer:=substr(l_buffer,1,l_amount);
1080
1081 --Perform the Replace operation for this buffer.
1082 l_buffer:=replace(l_buffer,p_srch_str,p_replace_str);
1083
1084 --Append this buffer to the target CLOB
1085 dbms_lob.writeappend(x_replaced_clob,length(l_buffer),l_buffer);
1086
1087 --Increment the offset by the amount already modified
1088 l_offset:=l_offset+l_amount;
1089
1090 --Reset Amount back to 32 KB
1091 l_amount:=32767;
1092
1093 --Boundary Condition has not occurred
1094 else
1095
1096 --Reset amount back to 32 KB
1097 l_amount:=32767;
1098
1099 --Perform the replace operation
1100 l_buffer:=replace(l_buffer,p_srch_str,p_replace_str);
1101
1102 --Append this buffer to the target CLOB
1103 dbms_lob.writeappend(x_replaced_clob,length(l_buffer),l_buffer);
1104
1105 --Increment the offset by the amount already modified
1106 l_offset:=l_offset+l_amount;
1107
1108
1109 end if;
1110
1111 --Obtain the final difference, i.e. the amount still to be parsed
1112 l_diff:=l_len-l_offset;
1113
1114 end loop;
1115
1116
1117 if l_diff>0
1118
1119 then
1120 --If difference is greater than zero, perform the replace operatiom
1121 --on this last chunk and append it to the target CLOB.
1122 l_amount:=l_diff+1;
1123 dbms_lob.read(p_source,l_amount,l_offset,l_buffer);
1124 l_buffer:=replace(l_buffer,p_srch_str,p_replace_str);
1125 dbms_lob.writeappend(x_replaced_clob,length(l_buffer),l_buffer);
1126
1127 end if;
1128
1129 end if;
1130
1131
1132 else
1133 --Length of search string is smaller than that of the replacement string
1134
1135 --Initialize offset
1136 l_offset:=1;
1137
1138 --Loop till the whole CLOB is searched
1139 while l_offset <= l_len
1140
1141 loop
1142 --Search for the required string starting from specified offset
1143 l_pos:=dbms_lob.instr(p_source,p_srch_str,l_offset);
1144
1145 if l_pos > 0 then
1146 --Search is a success
1147
1148 --Obtain the chunk of CLOB that exists before the occurence
1149 --of the search string
1150 l_amount:=l_pos-l_offset;
1151
1152 --Push this into the target CLOB
1153 l_temp_len:=dbms_lob.getlength(x_replaced_clob);
1154 if l_amount > 0 then
1155 dbms_lob.copy(x_replaced_clob,p_source,l_amount,l_temp_len+1,l_offset);
1156 end if;
1157
1158 --Push the replacement string into the target CLOB
1159 if length(p_replace_str) > 0 then
1160 dbms_lob.writeappend(x_replaced_clob,length(p_replace_str),p_replace_str);
1161 end if;
1162 l_offset:=l_pos+length(p_srch_str);
1163 else
1164 --The search String was not found from the specified offset
1165 --Append, the chunk of source CLOB from the offset to the end, into the
1166 --target CLOB
1167 l_temp_len:=dbms_lob.getlength(x_replaced_clob);
1168 dbms_lob.copy(x_replaced_clob,p_source,l_len-l_offset+1,l_temp_len+1,l_offset);
1169 l_offset:=l_len+1;
1170 end if;
1171 end loop;
1172 end if;
1173
1174 RETURN x_replaced_clob;
1175
1176 END CLOB_REPLACE;
1177 --Bug 3437422: End
1178
1179
1180 --Bug 3101047 : Start
1181 --Procedure defined to obtain the user display name along with the
1182 --originating system name and system id for a given user name
1183 PROCEDURE GETUSERROLEINFO
1184 (P_USER_NAME IN VARCHAR2,
1185 X_USER_DISPLAY_NAME OUT NOCOPY VARCHAR2,
1186 X_ORIG_SYSTEM OUT NOCOPY VARCHAR2,
1187 X_ORIG_SYSTEM_ID OUT NOCOPY NUMBER)
1188 IS
1189
1190 L_NOTIF_PREF VARCHAR2(30);
1191 L_LANGUAGE VARCHAR2(30);
1192 L_TERRITORY VARCHAR2(30);
1193 L_EMAIL_ADDRESS VARCHAR2(400);
1194 L_FLAG VARCHAR2(10);
1195
1196 BEGIN
1197 WF_DIRECTORY.GETROLEINFOMAIL (ROLE => P_USER_NAME,
1198 DISPLAY_NAME => X_USER_DISPLAY_NAME,
1199 EMAIL_ADDRESS => L_EMAIL_ADDRESS,
1200 NOTIFICATION_PREFERENCE => L_NOTIF_PREF,
1201 LANGUAGE => L_LANGUAGE,
1202 TERRITORY => L_TERRITORY,
1203 ORIG_SYSTEM => X_ORIG_SYSTEM,
1204 ORIG_SYSTEM_ID => X_ORIG_SYSTEM_ID,
1205 INSTALLED_FLAG => L_FLAG);
1206 END GETUSERROLEINFO;
1207
1208 --Procedure to obtain the event display name for a given event name.
1209 PROCEDURE GET_EVENT_DESCRIPTION(P_EVENT IN VARCHAR2,
1210 P_DESCRIPTION OUT NOCOPY VARCHAR2)
1211 IS
1212
1213 CURSOR c1 is
1214 SELECT DISPLAY_NAME
1215 from
1216 wf_events_vl
1217 WHERE NAME= P_EVENT;
1218
1219 BEGIN
1220 OPEN C1;
1221 FETCH c1 into P_DESCRIPTION;
1222 CLOSE C1;
1223
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 P_DESCRIPTION:=NULL;
1227
1228 END GET_EVENT_DESCRIPTION;
1229
1230
1231 -- Bug 3863508 : Start
1232
1233 --Procedure to obtain the user display name from EDR_PSIG_DETAILS if
1234 --the value is not null. Else it is fetched from FND tables
1235 PROCEDURE FETCH_USER_DISPLAY_NAME(P_USER_NAME IN VARCHAR2,
1236 P_SIGNATURE_ID IN NUMBER,
1237 X_USER_DISPLAY_NAME OUT NOCOPY VARCHAR2)
1238 IS
1239 l_displayname varchar2(400);
1240
1241 BEGIN
1242
1243 select user_display_name into l_displayname from edr_psig_details where
1244 signature_id=p_signature_id;
1245 x_user_display_name:=nvl(l_displayname,edr_utilities.getuserdisplayname(p_user_name));
1246
1247 --Bug 3101047 : END
1248 END FETCH_USER_DISPLAY_NAME;
1249
1250 -- Bug 3863508 : End
1251
1252 -- Bug 3630380 : Start
1253 --Procedure to obtain the user display name given the user id
1254 PROCEDURE FETCH_USER_DISPLAY_NAME(P_USER_ID IN NUMBER,
1255 X_USER_DISPLAY_NAME OUT NOCOPY VARCHAR2)
1256 AS
1257 BEGIN
1258 X_USER_DISPLAY_NAME := EDR_UTILITIES.getUserDisplayName(fnd_user_ap_pkg.get_user_name(P_USER_ID));
1259 END FETCH_USER_DISPLAY_NAME;
1260 -- Bug 3630380 : End
1261
1262
1263 --Bug 2674799 : start
1264 --changed to use new approversTable datatype.
1265
1266 --Bug 3607477 : Start
1267 -- Filters the duplicate and deleted approvers from the list of all ame approvers
1268 FUNCTION GET_UNIQUE_AME_APPROVERS(P_APPROVER_LIST IN approvers_Table)
1269 RETURN approvers_Table
1270 AS
1271
1272 l_unique_approver_list approvers_Table;
1273 --Bug 4870886 : start
1274 l_final_approver_list approvers_Table;
1275 --Bug 4870886 : end
1276 l_count NUMBER := 1;
1277 -- Bug 5167253 : start
1278 l_unique_approver_list_tmp ame_util.approverRecord2;
1279 -- Bug 5167253 : end
1280 BEGIN
1281 IF p_approver_list is null THEN
1282 return l_unique_approver_list;
1283 END IF;
1284
1285 FOR i in 1..p_approver_list.count LOOP
1286 IF(nvl(p_approver_list(i).approval_status,' ') <> 'REPEATED' AND
1287 nvl(p_approver_list(i).approval_status, ' ') <> 'SUPPRESSED') THEN
1288
1289 l_unique_approver_list(l_count) := p_approver_list(i);
1290 l_count := l_count+1;
1291 END IF;
1292 END LOOP;
1293
1294 -- Bug 5167253 : start
1295 IF (l_unique_approver_list.count) > 1 then
1296 FOR j IN REVERSE 1..(l_unique_approver_list.count-1) LOOP
1297 FOR i IN 1..j LOOP
1298 if(l_unique_approver_list(i).approver_order_number > l_unique_approver_list(i+1).approver_order_number) THEN
1299 l_unique_approver_list_tmp := l_unique_approver_list(i);
1300 l_unique_approver_list(i) := l_unique_approver_list(i+1);
1301 l_unique_approver_list(i+1) := l_unique_approver_list_tmp;
1302 END IF;
1303 END LOOP;
1304 END LOOP ;
1305 END IF;
1306
1307 -- Bug 5167253 : end
1308
1309 --Bug 4870886 : Start
1310 l_final_approver_list := l_unique_approver_list;
1311 IF l_final_approver_list.count > 0 then
1312
1313 l_final_approver_list(1).approver_order_number := 1;
1314
1315 --This logic reorders the approver sequence appropriately.
1316 FOR i IN 1..(l_unique_approver_list.count - 1) LOOP
1317 IF l_unique_approver_list(i+1).approver_order_number=l_unique_approver_list(i).approver_order_number THEN
1318 l_final_approver_list(i+1).approver_order_number:=l_final_approver_list(i).approver_order_number;
1319
1320 ELSE
1321 l_final_approver_list(i+1).approver_order_number:=l_final_approver_list(i).approver_order_number + 1;
1322 END IF;
1323
1324 END LOOP;
1325 END IF;
1326
1327 RETURN l_final_approver_list;
1328 --Bug 4870886 : End
1329
1330 END GET_UNIQUE_AME_APPROVERS;
1331 -- BUG 3607477 : End
1332 -- Bug 2674799 : end
1333
1334
1335 --Bug 3667036: Start
1336 FUNCTION GET_XML_ATTRIBUTE
1337 (
1338 P_EVENT_NAME VARCHAR2,
1339 P_EVENT_KEY VARCHAR2,
1340 p_XPATH_EXPRESSION VARCHAR2
1341 )
1342 RETURN VARCHAR2
1343 AS
1344 l_api_version CONSTANT NUMBER := 1.0;
1345 l_return_status VARCHAR2(1);
1346 l_msg_data VARCHAR2(2000);
1347 l_XPATH_VALUE VARCHAR2(2000);
1348 l_event_xml CLOB;
1349 BEGIN
1350 --read the xml from temp table
1351 select event_xml into l_event_xml
1352 from EDR_FINAL_XML_GT
1353 where event_name = p_event_name
1354 and event_key = p_event_key;
1355
1356 --call the ecx api to get the value for the xpath
1357 ECX_STANDARD.GET_VALUE_FOR_XPATH
1358 (p_api_version => l_api_version,
1359 x_return_status => l_return_status,
1360 x_msg_data => l_msg_data,
1361 p_XML_DOCUMENT => l_event_xml,
1362 p_XPATH_EXPRESSION => p_xpath_expression,
1363 x_XPATH_VALUE => l_xpath_value
1364 );
1365
1366 RETURN l_XPATH_VALUE;
1367
1368 EXCEPTION
1369 WHEN OTHERS THEN
1370 RETURN NULL;
1371
1372 END GET_XML_ATTRIBUTE;
1373
1374 --Bug 3667036: End
1375
1376 --Bug 3621309: Start
1377 --This API is defined to perform the required Database to XML
1378 --and XML to XML transformation based on the event parameters
1379 PROCEDURE GENERATE_XML_PAYLOAD(P_MAP_CODE IN VARCHAR2,
1380 P_DOCUMENT_ID IN VARCHAR2,
1381 P_RAW_XML IN CLOB,
1382 P_EVENT_NAME IN VARCHAR2,
1383 P_USE_CONTEXT IN VARCHAR2,
1384 X_XML_PAYLOAD OUT NOCOPY CLOB,
1385 X_DISPLAY_XML OUT NOCOPY CLOB
1386 )
1387 IS
1388
1389 l_error_code PLS_INTEGER;
1390 l_error_msg VARCHAR2(2000);
1391 l_log_file VARCHAR2(200);
1392 l_count NUMBER;
1393 DB_TO_XML_ERROR EXCEPTION;
1394 XML_TO_XML_ERROR EXCEPTION;
1395
1396 BEGIN
1397 --if the raw xml is not null it means that the data is existing in the database
1398 --and the map uses the database as the source
1399 if (p_raw_xml is null) then
1400
1401 ecx_outbound.getXML(i_map_code => P_MAP_CODE,
1402 i_document_id => P_DOCUMENT_ID,
1403 i_debug_level => 6,
1404 i_xmldoc => X_XML_PAYLOAD,
1405 i_ret_code => L_ERROR_CODE,
1406 i_errbuf => L_ERROR_MSG,
1407 i_log_file => L_LOG_FILE);
1408
1409 --If the return code from ECX is a value other than 0 then
1410 --an error has occurred
1411 if(l_error_code <> 0) then
1412 raise DB_TO_XML_ERROR;
1413 end if;
1414
1415 --Bug 3893101: Start
1416 --If Map Code is null then set the xml payload value to the raw XML value
1417 --itself.
1418 elsif P_MAP_CODE is null then
1419
1420 X_XML_PAYLOAD := P_RAW_XML;
1421 --Bug 3893101: End
1422
1423 else
1424
1425 --else the map needs the source xml that would be transformed using the map
1426 ecx_inbound_trig.processXML(i_map_code => P_MAP_CODE,
1427 i_payload => P_RAW_XML,
1428 i_debug_level => 6,
1429 i_ret_code => L_ERROR_CODE,
1430 i_errbuf => L_ERROR_MSG,
1431 i_log_file => L_LOG_FILE,
1432 o_payload => X_XML_PAYLOAD);
1433 --If the return code from ECX is a value other than 0 then
1434 --an error has occurred
1435 if(l_error_code <> 0) then
1436 raise XML_TO_XML_ERROR;
1437 end if;
1438
1439 end if;
1440
1441 --replace the user data section of the xml generated from ecx
1442 replace_user_data_token(X_xml_PAYLOAD);
1443
1444 if x_xml_payload is not null then
1445 --Manipulate the XML data to replace all occurrences of '<' with '<'
1446 --and all occurrences of '>' with '>'. Also prefix the CLOB with the
1447 --'<pre>' tag and append the CLOB with the '</pre>' tag. This ensures that
1448 --while rendering in a UI bean, the alignment is maintained.
1449 X_DISPLAY_XML := ADJUST_CLOB_FOR_DISPLAY(P_PAYLOAD => X_XML_PAYLOAD,
1450 P_PAYLOAD_TYPE => 'XMLPAYLOAD'
1451 );
1452 end if;
1453
1454 --if the api is used in the context of the validate setup ui page
1455 --we have to save the data in the glbal temp table in the database
1456 if (p_use_context = validate_setup_ui_ctx and p_raw_xml is not null) then
1457
1458 --Check if row already exists in the global temp table for specified event
1459 --event name and event key.
1460 select count(*) into l_count from edr_final_xml_gt
1461 where event_name = p_event_name
1462 and event_key = p_document_id;
1463 if l_count > 0 then
1464 --A row exists, hence update the row.
1465 update edr_final_xml_gt
1466 set event_xml = X_XML_PAYLOAD
1467 where event_name = p_event_name
1468 and event_key = p_document_id;
1469
1470 else
1471 --No row exists with the specified event name and event key.
1472 --Hence insert a row into the global temp table.
1473 insert into edr_final_xml_gt
1474 (event_name, event_key, event_xml)
1475 values
1476 (p_event_name, p_document_id, X_xml_PAYLOAD);
1477
1478 end if;
1479 end if;
1480
1481 EXCEPTION
1482
1483 when DB_TO_XML_ERROR then
1484 FND_MESSAGE.SET_NAME('EDR','EDR_VALIDATE_XML_GEN_ERR');
1485 FND_MESSAGE.SET_TOKEN('OPERATION','DB to XML');
1486 FND_MESSAGE.SET_TOKEN('ERROR_DETAILS',l_error_msg);
1487 FND_MESSAGE.SET_TOKEN('LOG_DETAILS',l_log_file);
1488 APP_EXCEPTION.RAISE_EXCEPTION;
1489
1490 when XML_TO_XML_ERROR then
1491 FND_MESSAGE.SET_NAME('EDR','EDR_VALIDATE_XML_GEN_ERR');
1492 FND_MESSAGE.SET_TOKEN('OPERATION','XML to XML');
1493 FND_MESSAGE.SET_TOKEN('ERROR_DETAILS',l_error_msg);
1494 FND_MESSAGE.SET_TOKEN('LOG_DETAILS',l_log_file);
1495 APP_EXCEPTION.RAISE_EXCEPTION;
1496
1497 when others then
1498 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1499 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',nvl(l_error_msg,SQLERRM));
1500 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_UTILITIES');
1501 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GENERATE_XML_PAYLOAD');
1502 APP_EXCEPTION.RAISE_EXCEPTION;
1503
1504
1505 END GENERATE_XML_PAYLOAD;
1506
1507
1508 --This method is basically used to manipulate the CLOB provided
1509 --for rendering on a OAF UI bean.
1510 FUNCTION ADJUST_CLOB_FOR_DISPLAY(P_PAYLOAD CLOB,
1511 P_PAYLOAD_TYPE VARCHAR2
1512 )
1513 RETURN CLOB
1514
1515 AS
1516
1517 L_TEMP_CLOB CLOB;
1518 X_RETURN_CLOB CLOB;
1519
1520 BEGIN
1521
1522 --Create a temporary clob
1523 DBMS_LOB.CREATETEMPORARY(x_return_clob, TRUE, DBMS_LOB.SESSION);
1524
1525 --Prefix the <pre> tag.
1526 DBMS_LOB.WRITEAPPEND(X_RETURN_CLOB,5,'<pre>');
1527
1528 if P_PAYLOAD_TYPE = 'XMLPAYLOAD' then
1529 --Payload is of type XMLPAYLOAD.
1530 --Therefore all occurrences of '<' should be replaced by '<' and
1531 --all occurrences of '>' should be replaced by '>'
1532 DBMS_LOB.CREATETEMPORARY(l_temp_clob, TRUE, DBMS_LOB.SESSION);
1533 L_TEMP_CLOB := EDR_UTILITIES.CLOB_REPLACE(P_PAYLOAD,'<','<');
1534 L_TEMP_CLOB := EDR_UTILITIES.CLOB_REPLACE(l_TEMP_CLOB,'>','>');
1535 DBMS_LOB.COPY(X_RETURN_CLOB,L_TEMP_CLOB,DBMS_LOB.GETLENGTH(L_TEMP_CLOB),6,1);
1536 else
1537 --Otherwise, just append the contents of L_TEMP_CLOB into X_RETURN_CLOB
1538 DBMS_LOB.COPY(X_RETURN_CLOB,P_PAYLOAD,DBMS_LOB.GETLENGTH(P_PAYLOAD),6,1);
1539 end if;
1540
1541 --Append the </pre> tag.
1542 DBMS_LOB.WRITEAPPEND(X_RETURN_CLOB,6,'</pre>');
1543
1544 RETURN X_RETURN_CLOB;
1545
1546 END ADJUST_CLOB_FOR_DISPLAY;
1547
1548 --This method is a wrapper over the ECX API. It is used to perform
1549 --the XSLT transformation to create the e-record at run-time.
1550 PROCEDURE XSLT_TRANSFORMATION ( p_xml_file IN CLOB,
1551 p_xslt_file_name IN VARCHAR2,
1552 p_XSLT_APPLICATION_CODE IN VARCHAR2,
1553 p_XSLT_FILE_VER IN VARCHAR2,
1554 x_output OUT NOCOPY CLOB,
1555 x_retcode OUT NOCOPY NUMBER,
1556 x_retmsg OUT NOCOPY VARCHAR2
1557 )
1558 is
1559
1560 BEGIN
1561
1562 --Create a temporary CLOB and copy the input contents from p_xml_file.
1563 --This is done because, the ECX API destroys the CLOB locator of the i/p
1564 --XML clob passed as parameter. Hence we create another CLOB and duplicate
1565 --the contents.
1566 DBMS_LOB.CREATETEMPORARY(X_OUTPUT, TRUE, DBMS_LOB.SESSION);
1567 DBMS_LOB.COPY(X_OUTPUT,p_xml_file,DBMS_LOB.GETLENGTH(p_xml_file));
1568
1569 --Call the ECX api to perform the XSLT transformation.
1570 ECX_STANDARD.perform_xslt_transformation ( i_xml_file => X_OUTPUT,
1571 i_xslt_file_name => p_xslt_file_name,
1572 i_XSLT_FILE_VER => p_xslt_file_ver,
1573 i_XSLT_APPLICATION_CODE => p_xslt_application_code,
1574 i_retcode => x_retcode,
1575 i_retmsg => x_retmsg
1576 );
1577
1578 --Adjust the CLOB data so that it can rendered properly on the UI bean in OAF.
1579 X_OUTPUT := ADJUST_CLOB_FOR_DISPLAY ( P_PAYLOAD => X_OUTPUT,
1580 P_PAYLOAD_TYPE => 'ERECORDPAYLOAD'
1581 );
1582 end XSLT_TRANSFORMATION;
1583
1584 --This method is used to save the raw xml run-time data in the table EDR_RAW_XML_T
1585 --This API is called when the developer mode is on.
1586 PROCEDURE SAVE_RAW_XML( P_TRANSACTION_NAME IN VARCHAR2,
1587 P_TRANSACTION_KEY IN VARCHAR2,
1588 P_RAW_XML CLOB)
1589 is
1590
1591 L_COUNT_VALUE NUMBER;
1592 pragma AUTONOMOUS_TRANSACTION;
1593
1594 BEGIN
1595
1596 --Verify if the row already exists with the given event name and event key
1597 select count(*) into L_count_Value
1598 from EDR_RAW_XML_T
1599 where event_name = p_transaction_name
1600 and event_key = p_transaction_key;
1601
1602
1603 if(L_count_value > 0) then
1604 --Row already exists with the specified event name and event key.
1605 --There update the row.
1606 update EDR_RAW_XML_T set RAW_XML = P_RAW_XML,
1607 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1608 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1609 LAST_UPDATE_DATE = sysdate
1610
1611 where event_name = p_transaction_name
1612 and event_key = p_transaction_key;
1613
1614 else
1615 --No row exists with the specified event name and event key.
1616 --There insert a row into the table with the required details
1617 insert into EDR_RAW_XML_T(EVENT_NAME,
1618 EVENT_KEY,
1619 RAW_XML,
1620 CREATED_BY,
1621 CREATION_DATE,
1622 LAST_UPDATED_BY,
1623 LAST_UPDATE_LOGIN,
1624 LAST_UPDATE_DATE
1625 )
1626 values ( p_transaction_name,
1627 p_transaction_key,
1628 p_raw_xml,
1629 FND_GLOBAL.USER_ID,
1630 sysdate,
1631 FND_GLOBAL.USER_ID,
1632 FND_GLOBAL.LOGIN_ID,
1633 sysdate
1634 );
1635 end if;
1636 --Commit the transaction.
1637 commit;
1638
1639 end SAVE_RAW_XML;
1640
1641 --This procedure obtains the required AME rule name,rule names, rule variables
1642 --and rule values for a given event name and event key.
1643 PROCEDURE GET_RULES_AND_VARIABLES
1644 (
1645 P_EVENT_NAME IN VARCHAR2,
1646 P_EVENT_KEY IN VARCHAR2 DEFAULT NULL,
1647 X_AME_RULE_IDS OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
1648 X_AME_RULE_DESCRIPTIONS OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
1649 X_VARIABLE_NAMES OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
1650 X_VARIABLE_VALUES OUT NOCOPY FND_TABLE_OF_VARCHAR2_255
1651 )
1652 IS
1653
1654 l_esign_required varchar2(1);
1655 l_eRecord_required varchar2(1);
1656
1657 l_rule_esign_required varchar2(1);
1658 l_rule_eRecord_required varchar2(1);
1659 l_event_status varchar2(100);
1660 l_sub_status varchar2(100);
1661 l_sub_guid varchar2(4000);
1662 evt wf_event_t;
1663 l_application_id number;
1664 l_application_code varchar2(32);
1665 l_return_status varchar2(32);
1666 l_application_name varchar2(240);
1667 l_ame_transaction_Type varchar2(240);
1668 l_transaction_name varchar2(240);
1669 l_ruleids ame_util.idList;
1670 l_rulenames ame_util.stringList;
1671 l_rulevalues EDR_STANDARD.ameruleinputvalues;
1672 l_temp_rule_names FND_TABLE_OF_VARCHAR2_255;
1673 l_temp_rule_values FND_TABLE_OF_VARCHAR2_255;
1674 l_rule_id NUMBER;
1675 l_variable_count NUMBER;
1676 -- bug 5586151 : start
1677 l_active_sub_guid varchar2(4000) := null;
1678 l_disabled_sub_guid varchar2(4000) :=null;
1679 l_disabled_sub_guid_count NUMBER :=0;
1680 NO_ERES_SUBSCRIPTIONS EXCEPTION;
1681 -- bug 5586151 : End
1682 CURSOR GET_EVT_SUBSCRIPTION_DETAILS IS
1683 select b.guid,A.status,b.status
1684 from
1685 wf_events a, wf_event_subscriptions b
1686 where a.GUID = b.EVENT_FILTER_GUID
1687 and a.name = p_event_name
1688 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
1689 -- bug 5586151 : start
1690 -- and b.STATUS = 'ENABLED'
1691 -- bug 5586151 : End
1692 --Bug No 4912782- Start
1693 and b.source_type = 'LOCAL'
1694 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
1695 --Bug No 4912782- End
1696 l_no_enabled_eres_sub NUMBER;
1697
1698 --The exception object to catch Multiple ERES subscriptions error.
1699 MULTIPLE_ERES_SUBSCRIPTIONS EXCEPTION;
1700
1701 l_change_signer varchar2(32);
1702 l_change_signer_defined varchar2(1);
1703
1704 BEGIN
1705
1706 --Bug 4074173 : start
1707 l_esign_required :='N';
1708 l_eRecord_required :='N';
1709 l_change_signer := 'ALL';
1710 l_change_signer_defined :='N';
1711 --Bug 4074173 : end
1712
1713
1714
1715 --Obtain the number of ERES subscriptions enabled for this event.
1716 select count(*) INTO l_no_enabled_eres_sub
1717 from wf_events a, wf_event_subscriptions b
1718 where a.GUID = b.EVENT_FILTER_GUID
1719 and a.name = p_event_name
1720 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
1721 and b.STATUS = 'ENABLED'
1722 --Bug No 4912782- Start
1723 and b.source_type = 'LOCAL'
1724 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
1725 --Bug No 4912782- End
1726
1727 --If the count is greater than 1 than raise exception.
1728 IF l_no_enabled_eres_sub > 1 THEN
1729 RAISE MULTIPLE_ERES_SUBSCRIPTIONS;
1730 ELSE
1731 -- bug 5586151 : start
1732 --Else fetch the subscription details for the event.
1733 -- OPEN GET_EVT_SUBSCRIPTION_DETAILS;
1734 -- FETCH GET_EVT_SUBSCRIPTION_DETAILS INTO l_sub_guid,l_Event_status,l_sub_status;
1735 -- CLOSE GET_EVT_SUBSCRIPTION_DETAILS;
1736 OPEN GET_EVT_SUBSCRIPTION_DETAILS;
1737 LOOP
1738 FETCH GET_EVT_SUBSCRIPTION_DETAILS INTO l_sub_guid,l_Event_status,l_sub_status;
1739 EXIT WHEN GET_EVT_SUBSCRIPTION_DETAILS%NOTFOUND;
1740 IF l_sub_status='ENABLED' THEN
1741 l_active_sub_guid := l_sub_guid;
1742 EXIT;
1743 ELSE
1744 l_disabled_sub_guid := l_sub_guid;
1745 l_disabled_sub_guid_count := l_disabled_sub_guid_count+1;
1746 END IF;
1747 END LOOP;
1748 CLOSE GET_EVT_SUBSCRIPTION_DETAILS;
1749
1750 IF(l_active_sub_guid IS NULL and l_disabled_sub_guid_count > 1) THEN
1751 RAISE NO_ERES_SUBSCRIPTIONS;
1752 ELSIF (l_active_sub_guid IS NULL and l_disabled_sub_guid_count = 0) THEN
1753 RAISE NO_DATA_FOUND;
1754 ELSE
1755 l_sub_guid := nvl(l_active_sub_guid,l_disabled_sub_guid);
1756 END IF;
1757 -- bug 5586151 : End
1758 END IF;
1759
1760 --If both event and subscription is enabled then
1761 --set the workflow variable parameters.
1762 -- IF l_event_status='ENABLED' and l_sub_status='ENABLED' then
1763 IF l_sub_guid IS NOT NULL then
1764 wf_event_t.initialize(evt);
1765 evt.setSendDate(sysdate);
1766 evt.setEventName(p_event_name);
1767 evt.setEventKey(p_event_key);
1768
1769 --Bug 4704598: start
1770 --no need to do this as the way to fetch the subscription param is
1771 --changed
1772 --l_return_status:=wf_rule.setParametersIntoParameterList(l_sub_guid,evt);
1773 --Bug 4704598: end
1774
1775 --If return status is success, then obtain the AME transaction name
1776 --from the subscription
1777
1778 --Bug 4704598: start
1779 --comment this if and change the way subscription params are obtained
1780 --IF l_return_status='SUCCESS' THEN
1781 -- l_ame_transaction_type := NVL(wf_event.getValueForParameter('EDR_AME_TRANSACTION_TYPE',evt.Parameter_List),
1782 -- evt.getEventName( ));
1783
1784 l_ame_transaction_type := edr_indexed_xml_util.get_wf_params('EDR_AME_TRANSACTION_TYPE',l_sub_guid);
1785 --Bug 4704598: end
1786
1787 --Obtain the application id and code.
1788 SELECT application_id,APPLICATION_SHORT_NAME into l_application_id,
1789 l_application_code
1790 FROM FND_APPLICATION
1791 WHERE APPLICATION_SHORT_NAME in
1792 (SELECT OWNER_TAG from WF_EVENTS
1793 WHERE NAME=evt.getEventName( ));
1794 --For the obtained AME transaction, fetch the rules that are are
1795 --applicable for the specified transaction id.
1796
1797 -- Bug 5167817 : start
1798 --Bug 5287504: Start
1799 --Create a save point
1800 --This is required because in previous versions of AME, all AME APIs
1801 --stored the run-time data in temp tables and put a lock in it when executed..
1802 --Since these tables are reused by other AME API's, their execution would
1803 --fail because of this.
1804 --Hence we always create a savepoint and rollback to remove this lock.
1805 --AME has fixed this issue in recent patch, but for safety reasons,
1806 --it is preferrable that this workaround is still utilized.
1807 SAVEPOINT AME_RULES_AND_VARIABLES;
1808 BEGIN
1809
1810 AME_API3.GETAPPLICABLERULES3(APPLICATIONIDIN => l_application_Id,
1811 TRANSACTIONIDIN => evt.getEventKey( ),
1812 TRANSACTIONTYPEIN => NVL(l_ame_transaction_type,evt.getEventName( )),
1813 RULEIDSOUT => l_ruleids,
1814 RULEDESCRIPTIONSOUT=> l_rulenames);
1815 ROLLBACK TO AME_RULES_AND_VARIABLES;
1816 EXCEPTION
1817 WHEN OTHERS THEN
1818 -- Following statement clears all lock aquired by this session
1819 -- This is modified as part of bug fix 2639210
1820 ROLLBACK TO AME_RULES_AND_VARIABLES;
1821 FND_MESSAGE.SET_NAME('EDR','EDR_AME_SETUP_ERR');
1822 FND_MESSAGE.SET_TOKEN('TXN_TYPE',nvl(l_ame_transaction_type,evt.getEventName()));
1823 FND_MESSAGE.SET_TOKEN('ERR_MSG',sqlerrm);
1824 APP_EXCEPTION.RAISE_EXCEPTION;
1825 END;
1826 --Bug 5287504: End
1827
1828 -- Bug 5167817 : end
1829 --Obtain the application name
1830 select application_name into l_application_name
1831 from ame_Calling_Apps
1832 where FND_APPLICATION_ID=l_application_id
1833 and TRANSACTION_TYPE_ID=NVL(l_ame_transaction_type,evt.getEventName( ))
1834 --Bug 4652277: Start
1835 --and end_Date is null;
1836 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
1837 --Bug 4652277: End
1838
1839 --If no rules were found to be applicable then set the ruleid to -1
1840 --and other variables to null.
1841 if l_ruleids.count = 0 then
1842 l_ruleids.delete;
1843 l_rulenames.delete;
1844 l_ruleids(1) := -1;
1845 l_rulenames(1) := null;
1846 end if;
1847
1848 --For each rule applicable in the transaction, obtain the rule variable
1849 --names and values.
1850 for i in 1..l_ruleids.count loop
1851 --These temporary variables are used to compute
1852 --the rule variable names and values.
1853 l_temp_rule_names := new FND_TABLE_OF_VARCHAR2_255();
1854 l_temp_rule_values := new FND_TABLE_OF_VARCHAR2_255();
1855
1856 --Initialize variable count to zero
1857 l_variable_count := 1;
1858
1859 l_rule_id := l_ruleids(i);
1860 wf_log_pkg.string(6, 'EDR_UTILITIES.GET_RULES_AND_VARIABLES','Rule_id: '||l_rule_id||' Rule '||l_rulenames(i));
1861
1862 --Obtain the rule variable name and value for the specified rule name.
1863
1864 -- Bug 3214495 : Start
1865
1866 EDR_STANDARD.GET_AMERULE_INPUT_VARIABLES(
1867 transactiontypeid =>NVL(l_ame_transaction_type,evt.getEventName( )),
1868 ameruleid => l_rule_id,
1869 amerulename => l_rulenames(i),
1870 ameruleinputvalues => l_rulevalues
1871 );
1872
1873 -- Bug 3214495 : End
1874
1875 wf_log_pkg.string(6, 'EDR_UTILITIES.GET_RULES_AND_VARIABLES','Total Input Values '||l_rulevalues.count );
1876 --If rule variables where found then compute the rules based on
1877 --the most restrictive rule.
1878 if l_rulevalues.count > 0 then
1879 for i in 1..l_rulevalues.count loop
1880 if l_rulevalues(i).input_name = 'ESIG_REQUIRED' then
1881 l_rule_esign_required:=upper(l_rulevalues(i).input_value);
1882 --For ESIGN_REQUIRED, 'Y' preceds 'N'.
1883 --The value of ESIGN_REQUIRED preceds EREC_REQUIRED.
1884 if (upper(l_esign_required)='N' and l_rule_esign_required ='Y') then
1885 l_esign_required:= l_rule_esign_required;
1886 end if;
1887 --Set the temporary variables accordingly.
1888 l_temp_rule_names.extend;
1889 l_temp_rule_names(l_variable_count) := l_rulevalues(i).input_name;
1890 l_temp_rule_values.extend;
1891 l_temp_rule_values(l_variable_count) := l_esign_required;
1892 l_variable_count := l_variable_count + 1;
1893 --Similarly set the value of EREC_REQUIRED.
1894 elsif l_rulevalues(i).input_name = 'EREC_REQUIRED' then
1895 l_rule_erecord_required:=upper(l_rulevalues(i).input_value);
1896
1897 if (upper(l_erecord_required)='N' and l_rule_erecord_required ='Y') then
1898 l_erecord_required:= l_rule_erecord_required;
1899 end if;
1900
1901 l_temp_rule_names.extend;
1902 l_temp_rule_names(l_variable_count) := l_rulevalues(i).input_name;
1903 l_temp_rule_values.extend;
1904 l_temp_rule_values(l_variable_count) := l_erecord_required;
1905 l_variable_count := l_variable_count + 1;
1906 --For the CHANGE_SIGNERS variable,
1907 --NONE preceds ADHOC,
1908 --ADGOC preceds ALL
1909 elsif l_rulevalues(i).input_name = 'CHANGE_SIGNERS' then
1910 l_change_signer_defined := 'Y';
1911
1912 if ( upper(l_change_signer)='ADHOC' and upper(l_rulevalues(i).input_value) ='NONE') then
1913 l_change_signer:= l_rulevalues(i).input_value;
1914 elsif ( upper(l_change_signer)='ALL' and upper(l_rulevalues(i).input_value) ='NONE') then
1915 l_change_signer:= l_rulevalues(i).input_value;
1916 elsif ( upper(l_change_signer)='ALL' and upper(l_rulevalues(i).input_value) ='ADHOC') then
1917 l_change_signer:= l_rulevalues(i).input_value;
1918 end if;
1919 else
1920 --If rule variable names are other than
1921 --ESIG_REQUIRED, EREC_REQUIRED,CHANGE_SIGNERS
1922 --then just obtain their values.
1923 l_temp_rule_names.extend;
1924 l_temp_rule_names(l_variable_count) := l_rulevalues(i).input_name;
1925 l_temp_rule_values.extend;
1926 l_temp_rule_values(l_variable_count) := l_rulevalues(i).input_value;
1927 l_variable_count := l_variable_count + 1;
1928 end if;
1929 --Increment the variable count value
1930 --The rationale behind using a separate counter for the temp
1931 --variables is that, the CHANGE_SIGNERS variable is set only
1932 --at the end of the procedure.
1933 end loop;
1934 end if;
1935
1936 --If EREC_REQUIRED is set to 'Y'
1937 --then set the rule variables values
1938 --to that of the current rule.
1939 if l_rule_erecord_required ='Y' and l_esign_required='N' then
1940 if X_VARIABLE_VALUES is not null then
1941 X_VARIABLE_NAMES.delete;
1942 X_VARIABLE_VALUES.delete;
1943 end if;
1944
1945 X_VARIABLE_NAMES := l_temp_rule_names;
1946 X_VARIABLE_VALUES := l_temp_rule_values;
1947
1948 end if;
1949
1950 --If ESIG_REQUIRED is set to 'Y'
1951 --then set the rule variables values
1952 --to that of the current rule.
1953 --The reason why this logic is repeated is that
1954 --ESIG_REQUIRED ='Y' takes precedence over
1955 --any value of EREC_REQUIRED.
1956 if l_rule_esign_required ='Y' then
1957 if X_VARIABLE_VALUES is not null then
1958 X_VARIABLE_NAMES.delete;
1959 X_VARIABLE_VALUES.delete;
1960 end if;
1961
1962 X_VARIABLE_NAMES := l_temp_rule_names;
1963 X_VARIABLE_VALUES := l_temp_rule_values;
1964 end if;
1965
1966 --If the no rule uptil now has set either of
1967 --ESIG_REQUIRED and EREC_REQUIRED to 'Y', then
1968 --set the rule variable values to that of the
1969 --current rule.
1970 if l_esign_required = 'N' and l_erecord_required = 'N' then
1971 if X_VARIABLE_VALUES is not null then
1972 X_VARIABLE_NAMES.delete;
1973 X_VARIABLE_VALUES.delete;
1974 end if;
1975
1976 X_VARIABLE_NAMES := l_temp_rule_names;
1977 X_VARIABLE_VALUES := l_temp_rule_values;
1978 end if;
1979
1980 --Delete the temporary variable contents if they exists.
1981 if l_temp_rule_names is not null then
1982 l_temp_rule_names.delete;
1983 end if;
1984
1985 if l_temp_rule_values is not null then
1986 l_temp_rule_values.delete;
1987 end if;
1988 end loop;
1989
1990 --If the change signer variable is defined then
1991 --set its value in the result array that has the most deterministic value
1992 --This workaround is required for change signers because, the variables
1993 --ESIGN_REQUIRED and EREC_REQUIRED have a precedence relationship between
1994 --themselves, while CHANGE_SIGNERS only has a precedence relationship
1995 --between its values not with any other variable.
1996 if l_change_signer_defined = 'Y' then
1997 if(X_VARIABLE_NAMES is null) then
1998 X_VARIABLE_NAMES := new FND_TABLE_OF_VARCHAR2_255();
1999 X_VARIABLE_VALUES := new FND_TABLE_OF_VARCHAR2_255();
2000 end if;
2001
2002 X_VARIABLE_NAMES.extend;
2003 X_VARIABLE_VALUES.extend;
2004
2005 X_VARIABLE_NAMES(X_VARIABLE_NAMES.count) := 'CHANGE_SIGNERS';
2006 X_VARIABLE_VALUES(X_VARIABLE_VALUES.count) := l_change_signer;
2007 end if;
2008
2009 --Obtain the rule list from AME
2010 --If ruleids are not null then populate the out parameter variables.
2011
2012 if l_ruleids(1) <> -1 then
2013 if l_ruleids.count > 0 then
2014 X_AME_RULE_IDS := NEW FND_TABLE_OF_VARCHAR2_255();
2015 X_AME_RULE_DESCRIPTIONS := NEW FND_TABLE_OF_VARCHAR2_255();
2016 for i in 1..l_rulenames.count loop
2017 X_AME_RULE_IDS.extend;
2018 X_AME_RULE_IDs(i) := l_ruleids(i);
2019 X_AME_RULE_DESCRIPTIONS.extend;
2020 X_AME_RULE_DESCRIPTIONS(i) := l_rulenames(i);
2021 end loop;
2022 end if;
2023 end if;
2024
2025 wf_log_pkg.string(6, 'EDR_UTILITIES.GET_RULES_AND_VARIABLES','Signature Required :'||l_esign_required);
2026 wf_log_pkg.string(6, 'EDR_UTILITIES.GET_RULES_AND_VARIABLES','eRecord Required :'||l_erecord_required);
2027 END IF;
2028
2029 --Bug 4704598: start
2030 -- END IF;
2031 --Bug 4704598: end
2032 --
2033
2034 EXCEPTION
2035 --In the event of multiple subscriptions raise an exception.
2036 WHEN MULTIPLE_ERES_SUBSCRIPTIONS THEN
2037 FND_MESSAGE.SET_NAME('EDR','EDR_VALIDATE_MULTI_ERES_ERR');
2038 fnd_message.set_token( 'EVENT', p_event_name);
2039 raise_application_error(-20002,fnd_message.get);
2040 WHEN NO_DATA_FOUND THEN
2041 -- Bug 5586151 :start
2042 fnd_message.set_name('EDR', 'EDR_VALIDATE_NO_ERES_SUBS_ERR');
2043 fnd_message.set_token( 'EVENT_NAME', p_event_name);
2044 raise_application_error(-20003,fnd_message.get);
2045 WHEN NO_ERES_SUBSCRIPTIONS THEN
2046 FND_MESSAGE.SET_NAME('EDR','EDR_VALIDATE_NO_ACT_ERES_ERR');
2047 fnd_message.set_token( 'EVENT_NAME', p_event_name);
2048 raise_application_error(-20004,fnd_message.get);
2049 -- Bug 5586151 :End
2050 WHEN OTHERS THEN
2051 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
2052 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
2053 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_UTILITIES');
2054 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GET_RULES_AND_VARIABLES');
2055 APP_EXCEPTION.RAISE_EXCEPTION;
2056
2057 END GET_RULES_AND_VARIABLES;
2058
2059 --Bug 3621309: end
2060
2061 -- Bug 3575265 :Start
2062 -- Start of comments
2063 -- API Name : GET_TEST_SCENARIO_XML
2064 -- Type : PRIVATE UTILITY
2065 -- Function : This API is called by XML Gateway while producing EDR
2066 -- ERES OAF Inter Event 4 Payload
2067 -- Pre-reqs : None
2068 -- Parameters :
2069 -- IN : P_TEST_SCENARIO_ID - TestScenarioId of the event being
2070 -- tested
2071 -- OUT : P_PARTIAL_XML - XML returned to XML gateway which will
2072 -- be part of event payload
2073 -- OUT : P_TEST_SCENARIO - XML returned to XML gateway which will
2074 -- be part of event payload
2075 -- OUT : P_TEST_SCENARIO_INSTANCE - XML returned to XML gateway which will
2076 -- be part of event payload
2077
2078 PROCEDURE GET_TEST_SCENARIO_XML
2079 (
2080 P_TEST_SCENARIO_ID IN NUMBER,
2081 P_PARTIAL_XML OUT NOCOPY VARCHAR2,
2082 P_TEST_SCENARIO OUT NOCOPY VARCHAR2,
2083 P_TEST_SCENARIO_INSTANCE OUT NOCOPY VARCHAR2
2084 ) IS
2085
2086 l_test_scenario_id number;
2087 l_test_scenario VARCHAR2(1000);
2088 l_test_scenario_instance VARCHAR2(1000);
2089 BEGIN
2090
2091 select test_scenario_id, test_scenario, test_scenario_instance
2092 into l_test_scenario_id, l_test_scenario, l_test_scenario_instance
2093 from edr_inter_event_test_scenarios
2094 where test_scenario_id = p_test_scenario_id;
2095
2096 p_partial_xml := l_test_scenario_id;
2097 p_test_scenario := l_test_scenario;
2098 p_test_scenario_instance := l_test_scenario_instance;
2099 END GET_TEST_SCENARIO_XML;
2100
2101 -- Bug 3575265 : End
2102
2103 -- Bug 3882605 : start
2104
2105 -- Start of comments
2106 -- API name : GET_PARENT_ERECORD_ID
2107 -- Type : Private Utility.
2108 -- Function : fetches Parent eRecord ID for a given erecord. P_PARENT_ERECORD_ID will be NULL if not found
2109 -- Pre-reqs : None.
2110 -- Parameters :
2111 -- IN : P_ERECORD_ID eRecord id for which parent erecord id need to be retrieved
2112 -- OUT : P_PARENT_ERECORD_ID Parent eRecord id of a given eRecord
2113
2114 -- End of comments
2115
2116 PROCEDURE GET_PARENT_ERECORD_ID(P_ERECORD_ID IN NUMBER,
2117 P_PARENT_ERECORD_ID OUT NOCOPY NUMBER)
2118 IS
2119 CURSOR c1 is
2120 SELECT PARENT_ERECORD_ID
2121 from
2122 EDR_EVENT_RELATIONSHIP
2123 WHERE CHILD_ERECORD_ID= P_ERECORD_ID;
2124 BEGIN
2125 OPEN C1;
2126 FETCH c1 into P_PARENT_ERECORD_ID;
2127 CLOSE C1;
2128 EXCEPTION
2129 WHEN OTHERS THEN
2130 P_PARENT_ERECORD_ID:=NULL;
2131 END GET_PARENT_ERECORD_ID;
2132
2133 -- Bug 3882605 : end
2134
2135 --Bug 3893101: Start
2136 PROCEDURE GENERATE_EVENT_PAYLOAD ( P_EVENT_KEY IN VARCHAR2,
2137 P_XML_CLOB_ID IN NUMBER )
2138 is
2139
2140 l_event_xml CLOB;
2141 l_ret_code PLS_INTEGER;
2142 l_error_msg VARCHAR2(2000);
2143 l_log_file VARCHAR2(200);
2144
2145 BEGIN
2146
2147 --Call the ECX API to perform the DB to XML transformation
2148 ECX_OUTBOUND.GETXML( i_map_code => 'oracle.apps.edr.IntrEvnt.Event',
2149 i_document_id => p_event_key,
2150 i_debug_level => FND_LOG.G_CURRENT_RUNTIME_LEVEL,
2151 i_xmldoc => l_event_xml,
2152 i_ret_code => l_ret_code,
2153 i_errbuf => l_error_msg,
2154 i_log_file => l_log_file
2155 );
2156 insert into EDR_PROCESS_ERECORDS_T(ERECORD_SEQUENCE_ID,
2157 PAYLOAD,
2158 CREATED_BY,
2159 CREATION_DATE,
2160 LAST_UPDATED_BY,
2161 LAST_UPDATE_DATE)
2162 values(P_XML_CLOB_ID,
2163 l_event_xml,
2164 fnd_global.user_id,
2165 sysdate,
2166 fnd_global.user_id,
2167 sysdate);
2168
2169 END GENERATE_EVENT_PAYLOAD;
2170 --Bug 3893101: End
2171
2172
2173 --Bug 2674799 : start
2174
2175 PROCEDURE GET_APPROVERS(
2176 p_APPLICATION_ID IN NUMBER,
2177 p_TRANSACTION_ID IN VARCHAR2,
2178 p_TRANSACTION_TYPE IN VARCHAR2,
2179 x_APPROVERS OUT NOCOPY APPROVERS_TABLE,
2180 x_RULE_IDS OUT NOCOPY ID_LIST,
2181 x_RULE_DESCRIPTIONS OUT NOCOPY STRING_LIST
2182 )
2183 AS
2184 approvalProcessCompleteYN ame_util.charType;
2185 itemClasses ame_util.stringList;
2186 itemIndexes ame_util.idList;
2187 itemIds ame_util.stringList;
2188 itemSources ame_util.longStringList;
2189 ruleIndexes ame_util.idList;
2190 sourceTypes ame_util.stringList;
2191
2192 approverList APPROVERS_TABLE;
2193
2194 BEGIN
2195 AME_API2.GETALLAPPROVERS6
2196 (
2197 APPLICATIONIDIN => p_application_id,
2198 TRANSACTIONIDIN => p_TRANSACTION_ID,
2199 TRANSACTIONTYPEIN => p_TRANSACTION_TYPE,
2200 approvalProcessCompleteYNOut => approvalProcessCompleteYN,
2201 APPROVERSOUT => approverList,
2202 itemIndexesOut => itemIndexes,
2203 itemClassesOut => itemClasses,
2204 itemIdsOut => itemIds,
2205 itemSourcesOut => itemSources,
2206 ruleIndexesOut => ruleIndexes,
2207 sourceTypesOut => sourceTypes,
2208 RULEIDSOUT => x_RULE_IDS,
2209 RULEDESCRIPTIONSOUT=> x_RULE_DESCRIPTIONS
2210 );
2211
2212 X_APPROVERS := GET_UNIQUE_AME_APPROVERS( approverList);
2213 --Bug 5724159 :Start
2214 IF (approverList.count=0) THEN
2215 /* This API does not return any rules when the number of approvers is 0 .
2216 so we need to call another API provided by AME to populate rule id's */
2217
2218 SAVEPOINT AME_RULES_AND_VARIABLES;
2219 BEGIN
2220
2221 AME_API3.GETAPPLICABLERULES3(APPLICATIONIDIN =>p_application_Id,
2222 TRANSACTIONIDIN =>p_TRANSACTION_ID,
2223 TRANSACTIONTYPEIN => p_TRANSACTION_TYPE,
2224 RULEIDSOUT => x_RULE_IDS,
2225 RULEDESCRIPTIONSOUT=> x_RULE_DESCRIPTIONS);
2226 ROLLBACK TO AME_RULES_AND_VARIABLES;
2227 EXCEPTION
2228 WHEN OTHERS THEN
2229 -- Following statement clears all lock aquired by this session
2230 -- This is modified as part of bug fix 2639210
2231 ROLLBACK TO AME_RULES_AND_VARIABLES;
2232 FND_MESSAGE.SET_NAME('EDR','EDR_AME_SETUP_ERR');
2233 FND_MESSAGE.SET_TOKEN('TXN_TYPE',p_TRANSACTION_TYPE);
2234 FND_MESSAGE.SET_TOKEN('ERR_MSG',sqlerrm);
2235 APP_EXCEPTION.RAISE_EXCEPTION;
2236 END;
2237 END IF;
2238 --Bug 5724159 :End
2239
2240 --Bug 5287504: Start
2241 EXCEPTION
2242 WHEN OTHERS THEN
2243 FND_MESSAGE.SET_NAME('EDR','EDR_AME_SETUP_ERR');
2244 FND_MESSAGE.SET_TOKEN('TXN_TYPE',p_TRANSACTION_TYPE);
2245 FND_MESSAGE.SET_TOKEN('ERR_MSG',sqlerrm);
2246 APP_EXCEPTION.RAISE_EXCEPTION;
2247 --Bug 5287504: End
2248 END GET_APPROVERS;
2249 -- Bug 2674799 : end
2250
2251
2252 --Bug 4122622: Start
2253 -- Start of comments
2254 -- API name : REPEATING_NUMBERS_EXIST
2255 -- Type : Private Utility.
2256 -- Function : Checks if any of the number in the method
2257 -- are repeated. If so it returns true. Else it returns
2258 -- false.
2259
2260 -- Pre-reqs : None.
2261 -- Parameters :
2262 -- IN : P_NUMBERS IN EDR_ERES_EVENT_PUB.ERECORD_ID_TBL_TYPE
2263 -- : Array of numbers.
2264 -- RETURNS : Boolean valye which indicates if any of the e-record ids
2265 -- : are repeating.
2266
2267 FUNCTION REPEATING_NUMBERS_EXIST(P_NUMBERS IN EDR_ERES_EVENT_PUB.ERECORD_ID_TBL_TYPE)
2268
2269 return boolean
2270
2271 is
2272
2273 i pls_integer;
2274
2275 j pls_integer;
2276
2277 l_repeating boolean;
2278
2279 BEGIN
2280
2281 l_repeating := false;
2282
2283
2284 i := p_numbers.FIRST;
2285
2286 while i is not null loop
2287 j := p_numbers.NEXT(i);
2288
2289 --Iterate through the e-record ID list.
2290 while j is not null loop
2291 --Set the flag and exit as soon as a repeating e-record ID is found.
2292 if(p_numbers(i) = p_numbers(j)) then
2293 l_repeating := true;
2294 exit;
2295 end if;
2296 j := p_numbers.NEXT(j);
2297 end loop;
2298
2299 if (l_repeating) then
2300 exit;
2301 end if;
2302
2303 i := p_numbers.NEXT(i);
2304 end loop;
2305
2306 return l_repeating;
2307
2308 END REPEATING_NUMBERS_EXIST;
2309
2310
2311 -- Start of comments
2312 -- API name : GET_ERECORD_IDS
2313 -- Type : Private Utility.
2314 -- PROCEDURE : The procedure takes a comma separated string of
2315 -- : child e-record IDs and converts them into table of numbers.
2316
2317 -- Pre-reqs : None.
2318 -- Parameters :
2319 -- IN : P_ERECORD_IDS_STRING IN VARCHAR2
2320 -- : Comma separated string of child e-record IDs.
2321 -- OUT : X_ERECORD_IDS_ARRAY
2322 -- : The array of e-record ID values.
2323
2324 PROCEDURE GET_ERECORD_IDS(P_ERECORD_IDS_STRING IN VARCHAR2,
2325 X_ERECORD_IDS_ARRAY OUT NOCOPY EDR_ERES_EVENT_PUB.ERECORD_ID_TBL_TYPE)
2326
2327 is
2328
2329 l_current_erecord_id NUMBER;
2330 l_current_erecord_id_string VARCHAR2(128);
2331 l_erecord_ids_string VARCHAR2(4000);
2332 l_pos NUMBER;
2333 l_count NUMBER;
2334 begin
2335
2336 l_erecord_ids_string := p_erecord_ids_string;
2337 l_count := 1;
2338
2339
2340 while true loop
2341 --Search for each occurrence of the "," character.
2342 --Extract the child e-record Id that is occurring.
2343 l_pos := instr(l_erecord_ids_string,',');
2344 if(l_pos = 0) then
2345 l_current_erecord_id_string := l_erecord_ids_string;
2346 l_erecord_ids_string := substr(l_erecord_ids_string,l_pos+1,length(l_erecord_ids_string)-length(l_current_erecord_id_string) + 1);
2347 --Convert the string e-record ID into a Number value.
2348 l_current_erecord_id := to_number(l_current_erecord_id_string,'999999999999.999999');
2349 x_erecord_ids_array(l_count) := l_current_erecord_id;
2350 exit;
2351 end if;
2352 l_current_erecord_id_string := substr(l_erecord_ids_string,1,l_pos-1);
2353 l_erecord_ids_string := substr(l_erecord_ids_string,l_pos+1,length(l_erecord_ids_string)-length(l_current_erecord_id_string) + 1);
2354 --Convert the string e-record ID into a Number value.
2355 l_current_erecord_id := to_number(l_current_erecord_id_string,'999999999999.999999');
2356 x_erecord_ids_array(l_count) := l_current_erecord_id;
2357 l_count := l_count + 1;
2358 end loop;
2359
2360 END GET_ERECORD_IDS;
2361 --Bug 4122622: End
2362
2363
2364 --Bug 4160412: Start
2365 --This method is used to obtain the a table of approvers from the comma separated string.
2366 PROCEDURE GET_APPROVER_LIST(P_APPROVING_USERS IN VARCHAR2,
2367 X_APPROVER_LIST OUT NOCOPY EDR_UTILITIES.APPROVERS_TABLE,
2368 X_ARE_APPROVERS_VALID OUT NOCOPY VARCHAR2,
2369 X_INVALID_APPROVERS OUT NOCOPY VARCHAR2)
2370 IS
2371
2372 l_list_counter NUMBER;
2373 l_count NUMBER;
2374 l_pos NUMBER;
2375 l_approving_users VARCHAR2(4000);
2376 l_current_approving_user VARCHAR2(4000);
2377
2378 BEGIN
2379
2380 l_approving_users := upper(p_approving_users);
2381
2382 l_list_counter := 1;
2383
2384
2385 X_ARE_APPROVERS_VALID := 'Y';
2386
2387 while true loop
2388
2389 --Search for each occurrence of the "," character.
2390 --Extract the user name for which it is occurring.
2391 l_pos := instr(l_approving_users,',');
2392 if(l_pos = 0) then
2393
2394 --Check if the specified user name is an FND USER.
2395 select count(*) into l_count from wf_roles
2396 where name = l_approving_users
2397 and status = 'ACTIVE'
2398 and (orig_system = 'PER' or ORIG_SYSTEM = 'FND_USR');
2399
2400
2401 --If count is zero then, then check if the specified user name is a responsibility.
2402 if(l_count = 0) then
2403
2404 --Check if the specified user name is a responsibility
2405 select count(*) into l_count from wf_roles
2406 where name = l_approving_users
2407 and status = 'ACTIVE'
2408 and orig_system = 'FND_RESP';
2409
2410 --If this count is also zero then the specified approver name is invalid.
2411 if l_count = 0 then
2412 X_ARE_APPROVERS_VALID := 'N';
2413 X_INVALID_APPROVERS := X_INVALID_APPROVERS || ',' || l_approving_users;
2414 else
2415 X_APPROVER_LIST(l_list_counter).NAME := '#'||l_approving_users;
2416 X_APPROVER_LIST(l_list_counter).APPROVER_ORDER_NUMBER := l_list_counter;
2417 end if;
2418 else
2419 X_APPROVER_LIST(l_list_counter).NAME := l_approving_users;
2420 X_APPROVER_LIST(l_list_counter).APPROVER_ORDER_NUMBER := l_list_counter;
2421 end if;
2422
2423 exit;
2424
2425 end if;
2426
2427 IF l_pos > 1 then
2428
2429 --Extract the approver from the comma separated string.
2430 l_current_approving_user := substr(l_approving_users,1,l_pos-1);
2431
2432 --Filter the source string to remove the approvers extracted in the previous step.
2433 l_approving_users := substr(l_approving_users,l_pos+1,length(l_approving_users)-length(l_current_approving_user) + 1);
2434
2435 --Check if the specified user name is an fnd user.
2436 select count(*) into l_count from wf_roles
2437 where name = l_current_approving_user
2438 and status = 'ACTIVE'
2439 and (orig_system = 'PER' or orig_system = 'FND_USR');
2440
2441 --If count is zero then, then check if the specified user name is a responsibility.
2442 if(l_count = 0) then
2443
2444 --Check if the specified user name is a responsibility
2445 select count(*) into l_count from wf_roles
2446 where name = l_current_approving_user
2447 and status = 'ACTIVE'
2448 and orig_system = 'FND_RESP';
2449
2450 --If this count is also zero then the specified approver name is invalid.
2451 if l_count = 0 then
2452 X_ARE_APPROVERS_VALID := 'N';
2453 X_INVALID_APPROVERS := X_INVALID_APPROVERS || ',' || l_current_approving_user;
2454 else
2455 X_APPROVER_LIST(l_list_counter).NAME := '#'||l_current_approving_user;
2456 X_APPROVER_LIST(l_list_counter).APPROVER_ORDER_NUMBER := l_list_counter;
2457 l_list_counter := l_list_counter + 1;
2458 end if;
2459 else
2460 X_APPROVER_LIST(l_list_counter).NAME := l_current_approving_user;
2461 X_APPROVER_LIST(l_list_counter).APPROVER_ORDER_NUMBER := l_list_counter;
2462 l_list_counter := l_list_counter + 1;
2463 end if;
2464 else
2465 if l_approving_users is null or length(l_approving_users) = 0 then
2466 exit;
2467 else
2468 --The control would come here if there were multiple commas specified in sequence.
2469 l_approving_users := substr(l_approving_users,l_pos+1,length(l_approving_users));
2470 end if;
2471 end if;
2472 end loop;
2473
2474 if length(x_invalid_approvers) > 0 then
2475 --Trim the invalid approvers string to remove the extra comma prefixed.
2476 x_invalid_approvers := ltrim(x_invalid_approvers,',');
2477 end if;
2478
2479 END GET_APPROVER_LIST;
2480
2481
2482 --This functions would return true if the approver list contains repeating approvers.
2483 --Otherwise it returns false.
2484 FUNCTION ARE_APPROVERS_REPEATING(P_APPROVER_LIST IN EDR_UTILITIES.APPROVERS_TABLE)
2485
2486 RETURN BOOLEAN
2487
2488 IS
2489
2490 i NUMBER;
2491 j NUMBER;
2492 l_repeating boolean;
2493
2494 BEGIN
2495
2496 l_repeating := false;
2497
2498 --Iterate through each approver in the list to identify the repeating approver.
2499 for i in 1..p_approver_list.count loop
2500
2501 if instr(p_approver_list(i).name,'#') <> 1 then
2502 for j in i+1..p_approver_list.count loop
2503
2504 if i <> j then
2505 if instr(p_approver_list(j).name,'#') <> 1 and upper(p_approver_list(i).name) = upper(p_approver_list(j).name) then
2506 --A repeating approver was found. Hence set the boolean variable to true.
2507 l_repeating := true;
2508 exit;
2509 end if;
2510 end if;
2511 end loop;
2512 end if;
2513
2514 if l_repeating then
2515 exit;
2516 end if;
2517
2518 end loop;
2519
2520 return l_repeating;
2521
2522 END ARE_APPROVERS_REPEATING;
2523 --Bug 4160412: End
2524 END EDR_UTILITIES;
[Home] [Help]