[Home] [Help]
PACKAGE BODY: APPS.EDR_STANDARD
Source
1 PACKAGE BODY edr_standard AS
2 /* $Header: EDRSTNDB.pls 120.11.12000000.1 2007/01/18 05:55:36 appldev ship $ */
3
4 PROCEDURE PSIG_STATUS
5 (
6 p_event in varchar2,
7 p_event_key in varchar2,
8 P_status out NOCOPY varchar2
9 ) IS
10 BEGIN
11
12 --Bug 3468810: start
13 edr_ctx_pkg.set_secure_attr;
14 --Bug 3468810: end
15
16 --Bug 4565450: start
17 select PSIG_STATUS into p_status
18 from edr_psig_documents
19 where EVENT_NAME=p_event
20 and EVENT_KEY=p_event_key
21 and PSIG_TIMESTAMP =
22 (select MAX(PSIG_TIMESTAMP)
23 from edr_psig_documents
24 where EVENT_NAME=p_event
25 and EVENT_KEY=p_event_key
26 and rownum < 2);
27 --Bug 4565450: end
28
29 --Bug 3468810: start
30 --unset the secure context now.
31 edr_ctx_pkg.unset_secure_attr;
32 --Bug 3468810: end
33 exception
34 when no_data_found then
35 p_status:=NULL;
36 when others then
37 p_status:='SQLERROR';
38
39 END PSIG_STATUS;
40
41 /* signature Requirement. This Procedure returns signature requireemnt for a given event.
42 The status is 'Yes' */
43
44 PROCEDURE PSIG_REQUIRED
45 (
46 p_event in varchar2,
47 p_event_key in varchar2,
48 P_status out NOCOPY boolean
49 ) IS
50 l_event_status varchar2(100);
51 l_sub_status varchar2(100);
52 l_sub_guid varchar2(4000);
53 evt wf_event_t;
54 l_application_id number;
55 l_application_code varchar2(32);
56 l_return_status varchar2(32);
57 l_application_name varchar2(240);
58 l_ame_transaction_Type varchar2(240);
59 l_transaction_name varchar2(240);
60 l_ruleids ame_util.idList;
61 l_rulenames ame_util.stringList;
62 l_rulevalues EDR_STANDARD.ameruleinputvalues;
63 -- Bug 5167817 : start
64 -- approverList ame_util.approversTable;
65 approverList EDR_UTILITIES.approvers_Table;
66 approvalProcessCompleteYN ame_util.charType;
67 itemClasses ame_util.stringList;
68 itemIndexes ame_util.idList;
69 itemIds ame_util.stringList;
70 itemSources ame_util.longStringList;
71 ruleIndexes ame_util.idList;
72 sourceTypes ame_util.stringList;
73 -- Bug 5167817 : end
74
75 l_esign_required varchar2(1);
76 l_eRecord_required varchar2(1);
77 CURSOR GET_EVT_SUBSCRIPTION_DETAILS IS
78 select b.guid,A.status,b.status
79 from
80 wf_events a, wf_event_subscriptions b
81 where a.GUID = b.EVENT_FILTER_GUID
82 and a.name = p_event
83 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
84 and b.STATUS = 'ENABLED'
85 --Bug No 4912782- Start
86 and b.source_type = 'LOCAL'
87 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
88 --Bug No 4912782- End
89 l_no_enabled_eres_sub NUMBER;
90 MULTIPLE_ERES_SUBSCRIPTIONS EXCEPTION;
91 BEGIN
92
93 --Bug 4074173 : start
94 l_esign_required :='N';
95 l_eRecord_required :='N';
96 --Bug 4074173 : end
97
98 /* Add a Savepoint to Rollback after the work is complete Bug Fix 3208296*/
99 SAVEPOINT PSIG_REQUIRED;
100 /* End of Fix Bug Fix 3208296 */
101 /* We will code this once AMe API's are in place */
102 p_status:=false;
103 /*check if Profile value is enabled */
104 if (fnd_profile.value('EDR_ERES_ENABLED') = 'Y') then
105 /*check if event and subscritptions are Enabled */
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 OPEN GET_EVT_SUBSCRIPTION_DETAILS;
125 FETCH GET_EVT_SUBSCRIPTION_DETAILS INTO l_sub_guid,l_Event_status,l_sub_status;
126 CLOSE GET_EVT_SUBSCRIPTION_DETAILS;
127 END IF;
128 --
129 -- End Bug Fix 3078516
130 --
131
132 IF l_event_status='ENABLED' and l_sub_status='ENABLED' then
133 /*check if any AMe stuff is available */
134 wf_event_t.initialize(evt);
135 evt.setSendDate(sysdate);
136 evt.setEventName(p_event);
137 evt.setEventKey(p_event_key);
138 -- Bug 5639849 : Starts
139 -- No need to load all subscription parameters, just get
140 -- ame_transaction_type using edr API.
141 -- l_return_status:=wf_rule.setParametersIntoParameterList(l_sub_guid,evt);
142
143 -- IF l_return_status='SUCCESS' THEN
144 /* Check for User Defined Parameters,
145 contains AME transactions Type
146 If Parameters are not specified, Assume Event name to be AME transaction Type
147
148 */
149 l_ame_transaction_type := NVL(EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_AME_TRANSACTION_TYPE',l_sub_guid),
150 evt.getEventName( ));
151 -- Bug 5639849 : Ends
152 /* AME Processing */
153 /* Select APPLICATION_ID of the Event. This is required by AME. Assumption made here
154 is OWNER_TAG will always be set to application Short Name*/
155
156 SELECT application_id,APPLICATION_SHORT_NAME into l_application_id,l_application_code
157 FROM FND_APPLICATION
158 WHERE APPLICATION_SHORT_NAME in (SELECT OWNER_TAG from WF_EVENTS
159 WHERE NAME=evt.getEventName( ));
160
161 /* AME Enhancement Code. Determine if singature is need or not and also get approvers */
162
163 /* This Code should be uncommented when the SSWA AME forms move to opmeres */
164 -- Bug 5167817 : start
165 /* AME_API.GETAPPROVERSANDRULES3
166 ( APPLICATIONIDIN => l_application_Id,
167 TRANSACTIONIDIN => evt.getEventKey( ),
168 TRANSACTIONTYPEIN => NVL(l_ame_transaction_type,evt.getEventName( )),
169 APPROVERSOUT => approverList,
170 RULEIDSOUT => l_ruleids,
171 RULEDESCRIPTIONSOUT=> l_rulenames
172 );
173 */
174 --Bug 5287504: Start
175 BEGIN
176 AME_API2.GETALLAPPROVERS6
177 (
178 APPLICATIONIDIN => l_application_Id,
179 TRANSACTIONIDIN => evt.getEventKey(),
180 TRANSACTIONTYPEIN => NVL(l_ame_transaction_type,evt.getEventName()),
181 approvalProcessCompleteYNOut => approvalProcessCompleteYN,
182 APPROVERSOUT => approverList,
183 itemIndexesOut => itemIndexes,
184 itemClassesOut => itemClasses,
185 itemIdsOut => itemIds,
186 itemSourcesOut => itemSources,
187 ruleIndexesOut => ruleIndexes,
188 sourceTypesOut => sourceTypes,
189 RULEIDSOUT => l_ruleids,
190 RULEDESCRIPTIONSOUT=> l_rulenames
191 );
192 EXCEPTION
193 WHEN OTHERS THEN
194 FND_MESSAGE.SET_NAME('EDR','EDR_AME_SETUP_ERR');
195 FND_MESSAGE.SET_TOKEN('TXN_TYPE',nvl(l_ame_transaction_type,evt.getEventName()));
196 FND_MESSAGE.SET_TOKEN('ERR_MSG',sqlerrm);
197 APP_EXCEPTION.RAISE_EXCEPTION;
198 END;
199 --Bug 5287504: End
200
201
202 -- Bug 5167817 : end
203 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','AME API Called. Total Approver '||approverlist.count );
204
205 select application_name into l_application_name
206 from ame_Calling_Apps
207 where
208 FND_APPLICATION_ID=l_application_id and
209 TRANSACTION_TYPE_ID=NVL(l_ame_transaction_type,evt.getEventName( ))
210 --Bug 4652277: Start
211 --and end_Date is null;
212 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
213 --Bug 4652277: End
214
215 if approverList.count > 0 then
216
217 for i in 1..l_ruleids.count loop
218 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','Rule_id: '||l_ruleids(i)||' Rule '||l_rulenames(i));
219
220 -- Bug 3214495 : Start
221
222 EDR_STANDARD.GET_AMERULE_INPUT_VARIABLES( transactiontypeid => NVL(l_ame_transaction_type,evt.getEventName( )),
223 ameruleid =>l_ruleids(i),
224 amerulename=>l_rulenames(i),
225 ameruleinputvalues=>l_rulevalues);
226 -- Bug 3214495 : End
227 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','Total Input Values '||l_rulevalues.count );
228 if l_rulevalues.count > 0 then
229 for i in 1..l_rulevalues.count loop
230 if l_rulevalues(i).input_name = 'ESIG_REQUIRED' then
231 if ( l_esign_required='N' and l_rulevalues(i).input_value ='Y') then
232 l_esign_required:= l_rulevalues(i).input_value;
233 end if;
234 elsif l_rulevalues(i).input_name = 'EREC_REQUIRED' then
235 if ( l_erecord_required='N' and l_rulevalues(i).input_value ='Y') then
236 l_erecord_required:= l_rulevalues(i).input_value;
237 end if;
238 end if;
239 end loop;
240 end if;
241 END LOOP;
242 END IF;
243 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','Signature Required :'||l_esign_required);
244 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','eRecord Required :'||l_erecord_required);
245 IF (l_esign_required='Y') THEN
246 p_status:=true;
247 END IF;
248 END IF;
249 -- Bug 5639849 : Starts
250 -- Comment the If l_return status statement
251 -- END IF;
252 -- Bug 5639849 : Ends
253 END IF;
254 --
255 -- Following statement clears all lock aquired by this session
256 -- This is modified as part of bug fix 2639210
257
258 --
259 ROLLBACK TO PSIG_REQUIRED;
260
261 EXCEPTION
262 WHEN MULTIPLE_ERES_SUBSCRIPTIONS THEN
263 p_status:=false;
264 ROLLBACK TO PSIG_REQUIRED;
265 FND_MESSAGE.SET_NAME('EDR','EDR_MULTI_ERES_SUBSCRP_ERR');
266 fnd_message.set_token( 'EVENT', p_event);
267 RAISE;
268 WHEN OTHERS THEN
269 p_status:=false;
270 ROLLBACK TO PSIG_REQUIRED;
271 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
272 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
273 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_STANDARD');
274 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','PSIG_REQUIRED');
275 RAISE;
276 END PSIG_REQUIRED;
277
278 /* eRecord Requirement. This Procedure returns signature requirement for a given event.
279 The status is 'true' or 'false' */
280
281 PROCEDURE EREC_REQUIRED
282 (
283 p_event in varchar2,
284 p_event_key in varchar2,
285 P_status out NOCOPY boolean
286 ) IS
287
288 l_event_status varchar2(100);
289 l_sub_status varchar2(100);
290 l_sub_guid varchar2(4000);
291
292 evt wf_event_t;
293 l_application_id number;
294 l_application_code varchar2(32);
295 l_return_status varchar2(32);
296 l_application_name varchar2(240);
297 l_ame_transaction_Type varchar2(240);
298 l_transaction_name varchar2(240);
299 l_ruleids ame_util.idList;
300 l_rulenames ame_util.stringList;
301 l_rulevalues EDR_STANDARD.ameruleinputvalues;
302
303 -- Bug 5167817 : start
304 -- approverList ame_util.approversTable;
305 approverList EDR_UTILITIES.approvers_Table;
306 approvalProcessCompleteYN ame_util.charType;
307 itemClasses ame_util.stringList;
308 itemIndexes ame_util.idList;
309 itemIds ame_util.stringList;
310 itemSources ame_util.longStringList;
311 ruleIndexes ame_util.idList;
312 sourceTypes ame_util.stringList;
313 -- Bug 5167817 : end
314
315 l_esign_required varchar2(1);
316 l_eRecord_required varchar2(1);
317
318 CURSOR GET_EVT_SUBSCRIPTION_DETAILS IS
319 select b.guid,A.status,b.status
320 from
321 wf_events a, wf_event_subscriptions b
322 where a.GUID = b.EVENT_FILTER_GUID
323 and a.name = p_event
324 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
325 and b.STATUS = 'ENABLED'
326 --Bug No 4912782- Start
327 and b.source_type = 'LOCAL'
328 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
329 --Bug No 4912782- End
330
331 l_no_enabled_eres_sub NUMBER;
332 MULTIPLE_ERES_SUBSCRIPTIONS EXCEPTION;
333 BEGIN
334
335 --Bug 4074173 : start
336 l_esign_required :='N';
337 l_eRecord_required :='N';
338 --Bug 4074173 : end
339
340 /* Begin of Fix Bug Fix 3208296 */
341 SAVEPOINT EREC_REQUIRED;
342 /* End of Fix Bug Fix 3208296 */
343 /* We will code this once AMe API's are in place */
344 p_status:=false;
345 /*check if Profile value is enabled */
346 if (fnd_profile.value('EDR_ERES_ENABLED') = 'Y') then
347 /*check if event and subscritptions are Enabled */
348 --
349 -- Start Bug Fix 3078516
350 -- Verify is more than one active ERES subscriptions are present
351 --
352 select count(*) into l_no_enabled_eres_sub
353 from
354 wf_events a, wf_event_subscriptions b
355 where a.GUID = b.EVENT_FILTER_GUID
356 and a.name = p_event
357 and b.RULE_FUNCTION='EDR_PSIG_RULE.PSIG_RULE'
358 and b.STATUS = 'ENABLED'
359 --Bug No 4912782- Start
360 and b.source_type = 'LOCAL'
361 and b.system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID')) ;
362 --Bug No 4912782- End
363 IF l_no_enabled_eres_sub > 1 THEN
364 RAISE MULTIPLE_ERES_SUBSCRIPTIONS;
365 ELSE
366 OPEN GET_EVT_SUBSCRIPTION_DETAILS;
367 FETCH GET_EVT_SUBSCRIPTION_DETAILS INTO l_sub_guid,l_Event_status,l_sub_status;
368 CLOSE GET_EVT_SUBSCRIPTION_DETAILS;
369 END IF;
370 --
371 -- End Bug Fix 3078516
372 --
373
374 IF l_event_status='ENABLED' and l_sub_status='ENABLED' then
375 /*check if any AMe stuff is available */
376 wf_event_t.initialize(evt);
377 evt.setSendDate(sysdate);
378 evt.setEventName(p_event);
379 evt.setEventKey(p_event_key);
380 -- Bug 5639849 : Starts
381 -- No need to loading all subscription parameters, just get
382 -- edr_ame_transaction_type using edr API.
383 --l_return_status:=wf_rule.setParametersIntoParameterList(l_sub_guid,evt);
384
385 -- IF l_return_status='SUCCESS' THEN
386 /* Check for User Defined Parameters,
387 contains AME transactions Type
388 If Parameters are not specified, Assume Event name to be AME transaction Type
389
390 */
391 l_ame_transaction_type := NVL(EDR_INDEXED_XML_UTIL.GET_WF_PARAMS('EDR_AME_TRANSACTION_TYPE',l_sub_guid),
392 evt.getEventName( ));
393 -- Bug 5639849: Ends
394 /* AME Processing */
395 /* Select APPLICATION_ID of the Event. This is required by AME. Assumption made here
396 is OWNER_TAG will always be set to application Short Name*/
397
398 SELECT application_id,APPLICATION_SHORT_NAME into l_application_id,l_application_code
399 FROM FND_APPLICATION
400 WHERE APPLICATION_SHORT_NAME in (SELECT OWNER_TAG from WF_EVENTS
401 WHERE NAME=evt.getEventName( ));
402
403 /* AME Enhancement Code. Determine if singature is need or not and also get approvers */
404
405 /* This Code should be uncommented when the SSWA AME forms move to opmeres */
406 -- Bug 5167817 : start
407 /*
408 AME_API.GETAPPROVERSANDRULES3
409 ( APPLICATIONIDIN => l_application_Id,
410 TRANSACTIONIDIN => evt.getEventKey( ),
411 TRANSACTIONTYPEIN => NVL(l_ame_transaction_type,evt.getEventName( )),
412 APPROVERSOUT => approverList,
413 RULEIDSOUT => l_ruleids,
414 RULEDESCRIPTIONSOUT=> l_rulenames
415 );
416 */
417
418 --Bug 5287504: Start
419 BEGIN
420 AME_API2.GETALLAPPROVERS6
421 (
422 APPLICATIONIDIN => l_application_Id,
423 TRANSACTIONIDIN => evt.getEventKey(),
424 TRANSACTIONTYPEIN => NVL(l_ame_transaction_type,evt.getEventName()),
425 approvalProcessCompleteYNOut => approvalProcessCompleteYN,
426 APPROVERSOUT => approverList,
427 itemIndexesOut => itemIndexes,
428 itemClassesOut => itemClasses,
429 itemIdsOut => itemIds,
430 itemSourcesOut => itemSources,
431 ruleIndexesOut => ruleIndexes,
432 sourceTypesOut => sourceTypes,
433 RULEIDSOUT => l_ruleids,
434 RULEDESCRIPTIONSOUT=> l_rulenames
435 );
436 EXCEPTION
437 WHEN OTHERS THEN
438 FND_MESSAGE.SET_NAME('EDR','EDR_AME_SETUP_ERR');
439 FND_MESSAGE.SET_TOKEN('TXN_TYPE',nvl(l_ame_transaction_type,evt.getEventName()));
440 FND_MESSAGE.SET_TOKEN('ERR_MSG',sqlerrm);
441 APP_EXCEPTION.RAISE_EXCEPTION;
442 END;
443 --bug 5287504: End
444
445 -- Bug 5167817 : end
446 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','AME API Called. Total Approver '||approverlist.count );
447 select application_name into l_application_name
448 from ame_Calling_Apps
449 where
450 FND_APPLICATION_ID=l_application_id and
451 TRANSACTION_TYPE_ID=NVL(l_ame_transaction_type,evt.getEventName( ))
452 --Bug 4652277: Start
453 --and end_Date is null;
454 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
455 --Bug 4652277: End
456
457 if approverList.count > 0 then
458
459 for i in 1..l_ruleids.count loop
460 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','Rule_id: '||l_ruleids(i)||' Rule '||l_rulenames(i));
461
462 -- Bug 3214495 : Start
463
464 EDR_STANDARD.GET_AMERULE_INPUT_VARIABLES( transactiontypeid =>NVL(l_ame_transaction_type,evt.getEventName( )),
465 ameruleid =>l_ruleids(i),
466 amerulename=>l_rulenames(i),
467 ameruleinputvalues=>l_rulevalues);
468
469 -- Bug 3214495 : End
470
471 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','Total Input Values '||l_rulevalues.count );
472 if l_rulevalues.count > 0 then
473 for i in 1..l_rulevalues.count loop
474 if l_rulevalues(i).input_name = 'ESIG_REQUIRED' then
475 if ( l_esign_required='N' and l_rulevalues(i).input_value ='Y') then
476 l_esign_required:= l_rulevalues(i).input_value;
477 end if;
478 elsif l_rulevalues(i).input_name = 'EREC_REQUIRED' then
479 if ( l_erecord_required='N' and l_rulevalues(i).input_value ='Y') then
480 l_erecord_required:= l_rulevalues(i).input_value;
481 end if;
482 end if;
483 end loop;
484 end if;
485 END LOOP;
486 END IF;
487 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','Signature Required :'||l_esign_required);
488 wf_log_pkg.string(6, 'EDR_STANDARD.psig_rule','eRecord Required :'||l_erecord_required);
489 IF (l_erecord_required='Y') THEN
490 p_status:=true;
491 END IF;
492 END IF;
493 -- Bug 5639849 : Starts
494 -- Commented the l_return_status if statement
495 --END IF;
496 -- Bug 5639849: Ends
497 END IF;
498
499 --
500 -- Following statement clears all lock aquired by this session
501 -- This is modified as part of bug fix 2936432
502
503 --
504 ROLLBACK TO EREC_REQUIRED;
505
506 EXCEPTION
507 WHEN MULTIPLE_ERES_SUBSCRIPTIONS THEN
508 p_status:=false;
509 ROLLBACK TO EREC_REQUIRED;
510 FND_MESSAGE.SET_NAME('EDR','EDR_MULTI_ERES_SUBSCRP_ERR');
511 fnd_message.set_token( 'EVENT', p_event);
512 RAISE;
513 WHEN OTHERS THEN
514 p_status:=false;
515 ROLLBACK TO EREC_REQUIRED;
516 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
517 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
518 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_STANDARD');
519 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','EREC_REQUIRED');
520 raise;
521 END EREC_REQUIRED;
522
523 FUNCTION PSIG_QUERY(p_eventQuery EDR_STANDARD.eventQuery) return number IS PRAGMA AUTONOMOUS_TRANSACTION;
524 i number;
525 l_document_id number;
526 l_query_id number:=NULL;
527 l_where_clause varchar2(4000) ;
528 l_sql varchar2(4000);
529 l_dbms_cur integer;
530 l_Rows_processed integer;
531 l_count number:=0;
532 l_prep_per VARCHAR2(1) := null;
533 l_append_per VARCHAR2(1) := null;
534 begin
535 if p_eventQuery.count > 0 THEN
536 for i in 1 ..p_eventQuery.count loop
537 -- Check do we need to prepend %
538 IF p_eventQuery(i).key_type = 'PREPEND_PER' OR
539 p_eventQuery(i).key_type = 'BOTH'
540 THEN
541 l_prep_per := '%';
542 ELSE
543 l_prep_per := null;
544 END IF;
545
546 -- Check do we need to append %
547 IF p_eventQuery(i).key_type = 'APPEND_PER' OR
548 p_eventQuery(i).key_type = 'BOTH'
549 THEN
550 l_append_per := '%';
551 ELSE
552 l_append_per := null;
553 END IF;
554 if I = 1 then
555 /* Build the where clause based on parameters
556 if key type is WHERE_CLAUSE then just and with event name
557 Other wise construct Where clause based on parameters either
558 append or prepend or set % both side of the string
559 */
560 if p_eventQuery(i).key_type = 'WHERE_CLAUSE' THEN
561 l_where_clause:= '( (event_name = ' || '''' || p_eventQuery(i).event_name || '''' || ' and event_key = '|| '''' || p_eventQuery(i).event_key || '''' || ') ';
562 ELSE
563 l_where_clause:= '((event_name = ' || '''' || p_eventQuery(i).event_name || '''' || ' and event_key like ' || '''' || l_prep_per || p_eventQuery(i).event_key || l_append_per || '''' || ') ';
564 END IF;
565
566 ELSE
567 if p_eventQuery(i).key_type = 'WHERE_CLAUSE' THEN
568 l_where_clause:= l_where_clause || ' OR (event_name = ' || '''' || p_eventQuery(i).event_name || '''' || ' and event_key = ' || '''' || p_eventQuery(i).event_key || '''' || ') ';
569 ELSE
570 l_where_clause:= l_where_clause || ' OR (event_name = ' || '''' || p_eventQuery(i).event_name || '''' || ' and event_key like '|| '''' || l_prep_per || p_eventQuery(i).event_key || l_append_per || '''' || ') ';
571 END IF;
572
573 END IF;
574
575 end Loop;
576
577 /* Construct SQL */
578 l_sql:='select document_id from edr_psig_documents where ' || l_where_clause || ' )' ;
579
580 /* Open Dynamic sql */
581 IF dbms_sql.is_open(l_dbms_cur) THEN
582 dbms_sql.close_cursor(l_dbms_cur);
583 END IF;
584
585 /* Opening the Cursor to fetch the row */
586 l_dbms_cur:=dbms_sql.open_cursor;
587 dbms_sql.parse(l_dbms_cur,l_sql,0);
588 dbms_sql.define_column(l_dbms_cur,1,l_document_id);
589
590 l_Rows_processed:=dbms_sql.execute(l_dbms_cur);
591 loop
592 IF dbms_sql.fetch_rows(l_dbms_cur) = 0 THEN
593 exit;
594 ELSE
595 if (l_count=0) then
596
597 select EDR_TRANS_QUERY_TEMP_S.nextval into l_query_id from dual;
598 end if;
599 l_count:=l_count+1;
600 dbms_sql.column_value(l_dbms_cur,1,l_document_id);
601 insert into EDR_TRANS_QUERY_TEMP(QUERY_ID,
602 DOCUMENT_ID,
603 CREATION_DATE,
604 CREATED_BY,
605 LAST_UPDATE_DATE,
606 LAST_UPDATED_BY,
607 LAST_UPDATE_LOGIN
608 )
609 VALUES
610 (l_query_id,
611 l_document_id,
612 sysdate,
613 fnd_global.user_id,
614 sysdate,
615 fnd_global.user_id,
616 fnd_global.login_id
617 );
618
619 END IF;
620 END LOOP;
621 dbms_sql.close_cursor(l_dbms_cur);
622 commit;
623
624 END IF;
625 return l_query_id;
626 END PSIG_QUERY;
627
628 -- This API Would be deprecated and replaced by an API that takes transaction id as a parameter.
629 -- Bug 3214495 : Start
630
631 PROCEDURE GET_AMERULE_INPUT_VALUES( ameapplication IN varchar2,
632 ameruleid IN NUMBER,
633 amerulename IN VARCHAR2,
634 ameruleinputvalues OUT NOCOPY EDR_STANDARD.ameruleinputvalues) is
635
636 -- 3172322 start: parameterize the cursors on a local transId for duplicated sub-query
637 -- 3172322 start: transName may be changed but 3075902 fix doesn't help here
638 CURSOR C0( the_trans_id VARCHAR2 ) is
639 SELECT INPUT_NAME, DEFAULT_VALUE from EDR_AMETRAN_INPUT_VAR
640 where AME_TRANS_ID = the_trans_id;
641 -- 3172322 end: query on trans id
642
643 -- 3172322 start: transName may be changed so need to collect all applnames for the same transId
644 CURSOR C1( the_trans_id VARCHAR2 ) is
645 SELECT INPUT_NAME, INPUT_VALUE
646 from EDR_AMERULE_INPUT_VAR
647 where AME_TRANS_NAME in
648 ( select distinct application_name from ame_calling_apps where transaction_type_id = the_trans_id)
649 AND RULE_ID=ameruleid;
650
651 CURSOR TID is
652 Select distinct transaction_type_id FROM ame_calling_apps
653 WHERE application_name = ameapplication
654 --Bug 4652277: Start
655 --AND end_date is null;
656 and sysdate between START_DATE AND NVL(END_DATE, SYSDATE);
657 --Bug 4652277: End
658
659 l_trans_id varchar2(80);
660 -- 3172322 end: select all transName matching this applName, remove query condition rule_name
661
662 l_rulevalues EDR_STANDARD.ameruleinputvalues;
663 i number:=0;
664 j number:=0;
665 l_name varchar2(240);
666 l_value varchar2(240);
667 BEGIN
668
669 -- 3172322 start: open cursor to populate value into l_trans_id
670 OPEN TID;
671 Fetch TID into l_trans_id;
672 IF TID%NOTFOUND THEN
673 Close TID;
674 RAISE NO_DATA_FOUND;
675 END IF;
676 Close TID;
677
678 -- 3172322 end: also pass parameter l_trans_id to the cursors below
679
680 /* Fetch Transaction inputs */
681 OPEN c0(l_trans_id);
682 Loop
683 fetch c0 into l_name,l_value;
684 EXIT when c0%NOTFOUND;
685 i:=i+1;
686 l_rulevalues(i).input_name:=l_name;
687 l_rulevalues(i).input_value:=l_value;
688 end loop;
689 CLOSE c0;
690
691 /* Now Check the Rule input Variable Values */
692
693 /* Open Cursor */
694 OPEN c1(l_trans_id);
695 Loop
696 fetch c1 into l_name,l_value;
697 EXIT when c1%NOTFOUND;
698 for i in 1..l_rulevalues.count
699 loop
700 if (l_rulevalues(i).input_name = l_name) then
701 l_rulevalues(i).input_value:=l_value;
702 end if;
703 end loop;
704 end loop;
705 CLOSE c1;
706 ameruleinputvalues:=l_rulevalues;
707 END GET_AMERULE_INPUT_VALUES;
708
709 -- Bug 3214495 : End
710
711
712
713
714 -- Bug 3214495 : Start
715
716 -- New API for passing Transaction Id as input
717
718 PROCEDURE GET_AMERULE_INPUT_VARIABLES( transactiontypeid IN varchar2,
719 ameruleid IN NUMBER,
720 amerulename IN VARCHAR2,
721 ameruleinputvalues OUT NOCOPY EDR_STANDARD.ameruleinputvalues) is
722
723 -- 3172322 start: parameterize the cursors on a local transId for duplicated sub-query
724 -- 3172322 start: transName may be changed but 3075902 fix doesn't help here
725 CURSOR C0( the_trans_id VARCHAR2 ) is
726 SELECT INPUT_NAME, DEFAULT_VALUE from EDR_AMETRAN_INPUT_VAR
727 where AME_TRANS_ID = the_trans_id;
728 -- 3172322 end: query on trans id
729
730 -- 3172322 start: transName may be changed so need to collect all applnames for the same transId
731 CURSOR C1( the_trans_id VARCHAR2 ) is
732 --Bug 5074583: Start
733 SELECT INPUT_NAME, INPUT_VALUE from EDR_AMERULE_INPUT_VAR where AME_TRANS_ID = the_trans_id
734 AND RULE_ID=ameruleid;
735 --Bug 5074583: End
736
737 l_trans_id varchar2(80);
738 -- 3172322 end: select all transName matching this applName, remove query condition rule_name
739
740 l_rulevalues EDR_STANDARD.ameruleinputvalues;
741 i number:=0;
742 j number:=0;
743 l_name varchar2(240);
744 l_value varchar2(240);
745 BEGIN
746
747 l_trans_id := transactiontypeid;
748
749 /* Fetch Transaction inputs */
750 OPEN c0(l_trans_id);
751 Loop
752 fetch c0 into l_name,l_value;
753 EXIT when c0%NOTFOUND;
754 i:=i+1;
755 l_rulevalues(i).input_name:=l_name;
756 l_rulevalues(i).input_value:=l_value;
757 end loop;
758 CLOSE c0;
759
760 /* Now Check the Rule input Variable Values */
761
762 /* Open Cursor */
763 OPEN c1(l_trans_id);
764 Loop
765 fetch c1 into l_name,l_value;
766 EXIT when c1%NOTFOUND;
767 for i in 1..l_rulevalues.count
768 loop
769 if (l_rulevalues(i).input_name = l_name) then
770 l_rulevalues(i).input_value:=l_value;
771 end if;
772 end loop;
773 end loop;
774 CLOSE c1;
775 ameruleinputvalues:=l_rulevalues;
776 END GET_AMERULE_INPUT_VARIABLES;
777
778 -- Bug 3214495 : End
779
780 PROCEDURE DISPLAY_DATE(P_DATE_IN in DATE , P_DATE_OUT OUT NOCOPY Varchar2) IS
781 BEGIN
782 -- BUG 322791 : Added FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE in the function call
783 P_DATE_OUT:= FND_DATE.DATE_TO_DISPLAYDT(P_DATE_IN, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE);
784 END;
785
786 -- BUG: 3075771 New Procedure added to return date only
787
788 PROCEDURE DISPLAY_DATE_ONLY(P_DATE_IN in DATE , P_DATE_OUT OUT NOCOPY Varchar2) IS
789 BEGIN
790
791 display_date(p_date_in,P_DATE_OUT );
792 P_DATE_OUT := trunc(fnd_date.displayDT_to_date(P_DATE_OUT));
793 END;
794
795 -- BUG: 3075771 New Procedure added to return time only
796
797 PROCEDURE DISPLAY_TIME_ONLY(P_DATE_IN in DATE , P_DATE_OUT OUT NOCOPY Varchar2) IS
798 BEGIN
799
800 display_date(p_date_in,P_DATE_OUT );
801 P_DATE_OUT := trim(substr(P_DATE_OUT, length(trunc(fnd_date.displayDT_to_date(P_DATE_OUT))) + 1));
802 END;
803
804 /* Audit Comparasion values */
805
806 FUNCTION COMPARE_AUDITVALUES(P_TABLE_NAME IN VARCHAR2,
807 P_COLUMN IN VARCHAR2,
808 P_PKNAME IN VARCHAR2,
809 P_PKVALUE IN VARCHAR2
810 )
811 return varchar2 IS
812 l_trigger_status varchar2(100);
813 AUDIT_DISABLED exception;
814 SQL_STMT varchar2(4000);
815 P_CURRENT_VALUE varchar2(2000);
816 P_OLD_VALUE varchar2(2000);
817 l_table_name varchar2(4000);
818
819 BEGIN
820 /* check if audit is enable on the given table, if not throw an error */
821 BEGIN
822 SELECT status into l_trigger_status
823 FROM user_triggers
824 WHERE trigger_name=P_TABLE_NAME||'_AU';
825
826 wf_log_pkg.string(6, 'TEST','Trigger Status ' ||l_trigger_status);
827
828
829 EXCEPTION
830 WHEN NO_DATA_FOUND then
831 raise AUDIT_DISABLED;
832 END;
833 IF l_trigger_status ='DISABLED' then
834 raise AUDIT_DISABLED;
835 END IF;
836 /* fetch current value */
837 sql_stmt := 'SELECT :1 FROM :2 WHERE :3 = :4';
838
839 EXECUTE IMMEDIATE 'select ' ||P_COLUMN||' from '||P_TABLE_NAME ||' where '||P_PKNAME||'='||P_PKVALUE ||' '
840 INTO p_current_value;
841
842 wf_log_pkg.string(6, 'TEST','Current Value ' ||p_current_value);
843
844
845 /* fetch old values from audit tables */
846 l_table_name:=P_TABLE_NAME||'_A';
847
848
849 l_table_name:=P_TABLE_NAME||'_A';
850 wf_log_pkg.string(6, 'TEST','Audit Table ' ||l_table_name);
851 wf_log_pkg.string(6, 'TEST','SQL '||sql_stmt);
852 BEGIN
853 EXECUTE IMMEDIATE 'select '||P_COLUMN||' from '||L_TABLE_NAME||' where '||P_PKNAME||'='||P_PKVALUE ||' and '||
854 'AUDIT_SESSION_ID=USERENV('||''''||'SESSIONID'||''''||') and '||
855 'AUDIT_TIMESTAMP in (SELECT max(AUDIT_TIMESTAMP) from '||L_TABLE_NAME||' where '||P_PKNAME||'='||P_PKVALUE ||' and '||
856 'AUDIT_SESSION_ID=USERENV('||''''||'SESSIONID'||''''||')) and '||
857 'AUDIT_TRANSACTION_TYPE='||''''||'U'||'''' INTO p_old_value;
858 wf_log_pkg.string(6, 'TEST','old Value ' ||p_old_value);
859 EXCEPTION
860 WHEN NO_DATA_FOUND then
861 /* Non of the columns changed */
862 return 'false';
863 END;
864 IF p_old_value is NULL then
865 return 'false';
866 ELSIF p_old_value <> p_current_value then
867 return 'true';
868 END IF;
869 EXCEPTION
870 WHEN AUDIT_DISABLED then
871 raise;
872 end COMPARE_AUDITVALUES;
873
874 PROCEDURE FIND_WF_NTF_RECIPIENT(P_ORIGINAL_RECIPIENT IN VARCHAR2,
875 P_MESSAGE_TYPE IN VARCHAR2,
876 P_MESSAGE_NAME IN VARCHAR2,
877 P_RECIPIENT IN OUT NOCOPY VARCHAR2,
878 P_NTF_ROUTING_COMMENTS IN OUT NOCOPY VARCHAR2,
879 P_ERR_CODE OUT NOCOPY varchar2,
880 P_ERR_MSG OUT NOCOPY varchar2)IS
881 l_original_recipient varchar2(40);
882 X_role varchar2(40);
883 /* SKARIMIS Changed the cusrosr to handle NULL values as NULL=NULL comparision does not work */
884 cursor rulecurs(x_role varchar2) is
885 select WRR.ACTION, WRR.ACTION_ARGUMENT, WRR.RULE_COMMENT
886 from WF_ROUTING_RULES WRR
887 where WRR.ROLE =L_ORIGINAL_RECIPIENT
888 and sysdate between nvl(WRR.BEGIN_DATE, sysdate-1) and
889 nvl(WRR.END_DATE, sysdate+1)
890 and nvl(WRR.MESSAGE_TYPE, nvl(P_MESSAGE_TYPE,0)) = nvl(P_MESSAGE_TYPE,0)
891 and nvl(WRR.MESSAGE_NAME, nvl(P_MESSAGE_NAME,0)) = nvl(P_MESSAGE_NAME,0)
892 order by WRR.MESSAGE_TYPE, WRR.MESSAGE_NAME;
893 l_RECIPIENT varchar2(200);
894 l_OVERRIDE_MODE varchar2(100);
895 l_comments varchar2(4000);
896 BAD_FORWARD exception;
897 forwardCount number := 0;
898 final_comments varchar2(4000);
899 BEGIN
900 l_original_recipient := p_original_recipient;
901 while true
902 LOOP
903 IF (forwardCount >= 10) then
904 Raise BAD_FORWARD;
905 ELSE
906 forwardCount:=forwardCount+1;
907 END IF;
908 OPEN ruleCurs(l_original_recipient);
909 fetch ruleCurs into l_override_mode,l_recipient,l_comments;
910 IF ruleCurs%NOTFOUND THEN
911 /* No overriding Rule */
912 exit;
913 ELSE
914 Wf_Core.Token('TO_ROLE', WF_Directory.GetRoleDisplayName(l_recipient));
915 Wf_Core.Token('FROM_ROLE', WF_Directory.GetRoleDisplayName(L_original_recipient));
916
917 IF l_override_mode = 'FORWARD' then
918 L_COMMENTS := Wf_Core.Translate('DELEGATE_AUDIT_MSG')||' ';
919 ELSIF l_override_mode = 'TRANSFER' then
920 L_COMMENTS := Wf_Core.Translate('TRANSFER_AUDIT_MSG')||' ';
921
922 --Bug 3879150: Start
923 --A NOOP means that there is no transfer of notification.
924 ELSIF l_override_mode = 'NOOP' then
925 --No delegation or transfer of ownership. Hence exit.
926 exit;
927 --Bug 3879150: End
928
929 ELSE
930 raise BAD_FORWARD;
931 END IF;
932 final_comments :=final_comments||wf_core.newline||l_comments;
933 l_original_recipient := l_recipient;
934 close ruleCurs;
935 END IF;
936 END LOOP;
937 P_RECIPIENT:=l_ORIGINAL_RECIPIENT;
938 P_NTF_ROUTING_COMMENTS:=final_COMMENTS;
939 close ruleCurs;
940 P_ERR_CODE:=0;
941 EXCEPTION
942 WHEN BAD_FORWARD then
943 P_ERR_CODE:= 20001;
944 FND_MESSAGE.SET_NAME('EDR','EDR_BAD_NTF_RULE');
945 FND_MESSAGE.SET_TOKEN('NAME',WF_Directory.GetRoleDisplayName(p_original_recipient));
946 P_ERR_MSG:= FND_MESSAGE.GET;
947
948 END FIND_WF_NTF_RECIPIENT;
949
950 /*******************************************************************************
951 ***** This procedure returns single Descriptive flex field prompt ****
952 ***** It accepts ****
953 ***** Application ID -- Descriptive Flexed Field owner Application ****
954 ***** DESC_FLEX_DEF_NAME -- Name of the Flex Definition ****
955 ***** DESC_FLEX_CONTEXT -- Flex Definition context(ATTRIBUTE_CATEGORY) ****
956 ***** COLUMN_NAME -- Attribute Column Name (ATTRIBUTE1 ...) ****
957 ***** PROMPT_TYPE -- Allowed Values LEFT OR ABOVE based on ****
958 ***** prompt type we return value from one of the ****
959 ***** following field ****
960 ***** LEFT --> FORM_LEFT_PROMPT ****
961 ***** ABOVE --> FORM_ABOVE_PROMPT ****
962 ***** COLUMN_PROMPT -- Returns Prompt for Column Name passed ****
963 ********************************************************************************/
964
965 PROCEDURE GET_DESC_FLEX_SINGLE_PROMPT(P_APPLICATION_ID IN NUMBER,
966 P_DESC_FLEX_DEF_NAME IN VARCHAR2,
967 P_DESC_FLEX_CONTEXT IN VARCHAR2,
968 P_COLUMN_NAME IN VARCHAR2,
969 P_PROMPT_TYPE IN VARCHAR2,
970 P_COLUMN_PROMPT OUT NOCOPY VARCHAR2) AS
971 CURSOR GET_DESC_FLEX_PROMPT IS
972 SELECT FORM_LEFT_PROMPT, FORM_ABOVE_PROMPT
973 FROM FND_DESCR_FLEX_COL_USAGE_VL
974 WHERE APPLICATION_ID = P_APPLICATION_ID
975 and DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEX_DEF_NAME
976 and DESCRIPTIVE_FLEX_CONTEXT_CODE = P_DESC_FLEX_CONTEXT
977 and APPLICATION_COLUMN_NAME = P_COLUMN_NAME;
978
979 l_prompt_rec GET_DESC_FLEX_PROMPT%ROWTYPE;
980 BEGIN
981 OPEN GET_DESC_FLEX_PROMPT;
982 FETCH GET_DESC_FLEX_PROMPT INTO l_prompt_rec;
983 IF P_PROMPT_TYPE = 'LEFT' THEN
984 P_COLUMN_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
985 ELSIF P_PROMPT_TYPE = 'ABOVE' THEN
986 P_COLUMN_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
987 ELSE
988 P_COLUMN_PROMPT := NULL;
989 END IF;
990 CLOSE GET_DESC_FLEX_PROMPT;
991 END GET_DESC_FLEX_SINGLE_PROMPT;
992
993 /*******************************************************************************
994 ***** This procedure returns all 30 Descriptive flex field prompt ****
995 ***** It accepts ****
996 ***** Application ID -- Descriptive Flexed Field owner Application ****
997 ***** DESC_FLEX_DEF_NAME -- Name of the Flex Definition ****
998 ***** DESC_FLEX_CONTEXT -- Flex Definition context(ATTRIBUTE_CATEGORY) ****
999 ***** PROMPT_TYPE -- Allowed Values LEFT OR ABOVE based on ****
1000 ***** prompt type we return value from one of the ****
1001 ***** following field ****
1002 ***** LEFT --> FORM_LEFT_PROMPT ****
1003 ***** ABOVE --> FORM_ABOVE_PROMPT ****
1004 ***** COLUMN1_PROMPT -- Returns Prompt for Column Name passed ****
1005 ***** ....... COLUMN30_PROMPT ****
1006 ********************************************************************************/
1007
1008 PROCEDURE GET_DESC_FLEX_ALL_PROMPTS(P_APPLICATION_ID IN NUMBER,
1009 P_DESC_FLEX_DEF_NAME IN VARCHAR2,
1010 P_DESC_FLEX_CONTEXT IN VARCHAR2,
1011 P_PROMPT_TYPE IN VARCHAR2 ,
1012 P_COLUMN1_NAME IN VARCHAR2,
1013 P_COLUMN2_NAME IN VARCHAR2,
1014 P_COLUMN3_NAME IN VARCHAR2,
1015 P_COLUMN4_NAME IN VARCHAR2,
1016 P_COLUMN5_NAME IN VARCHAR2,
1017 P_COLUMN6_NAME IN VARCHAR2,
1018 P_COLUMN7_NAME IN VARCHAR2,
1019 P_COLUMN8_NAME IN VARCHAR2,
1020 P_COLUMN9_NAME IN VARCHAR2,
1021 P_COLUMN10_NAME IN VARCHAR2,
1022 P_COLUMN11_NAME IN VARCHAR2,
1023 P_COLUMN12_NAME IN VARCHAR2,
1024 P_COLUMN13_NAME IN VARCHAR2,
1025 P_COLUMN14_NAME IN VARCHAR2,
1026 P_COLUMN15_NAME IN VARCHAR2,
1027 P_COLUMN16_NAME IN VARCHAR2,
1028 P_COLUMN17_NAME IN VARCHAR2,
1029 P_COLUMN18_NAME IN VARCHAR2,
1030 P_COLUMN19_NAME IN VARCHAR2,
1031 P_COLUMN20_NAME IN VARCHAR2,
1032 P_COLUMN21_NAME IN VARCHAR2,
1033 P_COLUMN22_NAME IN VARCHAR2,
1034 P_COLUMN23_NAME IN VARCHAR2,
1035 P_COLUMN24_NAME IN VARCHAR2,
1036 P_COLUMN25_NAME IN VARCHAR2,
1037 P_COLUMN26_NAME IN VARCHAR2,
1038 P_COLUMN27_NAME IN VARCHAR2,
1039 P_COLUMN28_NAME IN VARCHAR2,
1040 P_COLUMN29_NAME IN VARCHAR2,
1041 P_COLUMN30_NAME IN VARCHAR2,
1042 P_COLUMN1_PROMPT OUT NOCOPY VARCHAR2,
1043 P_COLUMN2_PROMPT OUT NOCOPY VARCHAR2,
1044 P_COLUMN3_PROMPT OUT NOCOPY VARCHAR2,
1045 P_COLUMN4_PROMPT OUT NOCOPY VARCHAR2,
1046 P_COLUMN5_PROMPT OUT NOCOPY VARCHAR2,
1047 P_COLUMN6_PROMPT OUT NOCOPY VARCHAR2,
1048 P_COLUMN7_PROMPT OUT NOCOPY VARCHAR2,
1049 P_COLUMN8_PROMPT OUT NOCOPY VARCHAR2,
1050 P_COLUMN9_PROMPT OUT NOCOPY VARCHAR2,
1051 P_COLUMN10_PROMPT OUT NOCOPY VARCHAR2,
1052 P_COLUMN11_PROMPT OUT NOCOPY VARCHAR2,
1053 P_COLUMN12_PROMPT OUT NOCOPY VARCHAR2,
1054 P_COLUMN13_PROMPT OUT NOCOPY VARCHAR2,
1055 P_COLUMN14_PROMPT OUT NOCOPY VARCHAR2,
1056 P_COLUMN15_PROMPT OUT NOCOPY VARCHAR2,
1057 P_COLUMN16_PROMPT OUT NOCOPY VARCHAR2,
1058 P_COLUMN17_PROMPT OUT NOCOPY VARCHAR2,
1059 P_COLUMN18_PROMPT OUT NOCOPY VARCHAR2,
1060 P_COLUMN19_PROMPT OUT NOCOPY VARCHAR2,
1061 P_COLUMN20_PROMPT OUT NOCOPY VARCHAR2,
1062 P_COLUMN21_PROMPT OUT NOCOPY VARCHAR2,
1063 P_COLUMN22_PROMPT OUT NOCOPY VARCHAR2,
1064 P_COLUMN23_PROMPT OUT NOCOPY VARCHAR2,
1065 P_COLUMN24_PROMPT OUT NOCOPY VARCHAR2,
1066 P_COLUMN25_PROMPT OUT NOCOPY VARCHAR2,
1067 P_COLUMN26_PROMPT OUT NOCOPY VARCHAR2,
1068 P_COLUMN27_PROMPT OUT NOCOPY VARCHAR2,
1069 P_COLUMN28_PROMPT OUT NOCOPY VARCHAR2,
1070 P_COLUMN29_PROMPT OUT NOCOPY VARCHAR2,
1071 P_COLUMN30_PROMPT OUT NOCOPY VARCHAR2) IS
1072
1073 CURSOR GET_DESC_FLEX_PROMPTS IS
1074 SELECT col_vl.APPLICATION_COLUMN_NAME,
1075 col_vl.FORM_LEFT_PROMPT,
1076 col_vl.FORM_ABOVE_PROMPT
1077 FROM FND_DESCR_FLEX_COL_USAGE_VL col_vl,fnd_descr_flex_contexts ctx
1078 WHERE col_vl.APPLICATION_ID = P_APPLICATION_ID
1079 and col_vl.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEX_DEF_NAME
1080 and col_vl.APPLICATION_ID = ctx.APPLICATION_ID
1081 and col_vl.DESCRIPTIVE_FLEXFIELD_NAME = ctx.DESCRIPTIVE_FLEXFIELD_NAME
1082 and col_vl.DESCRIPTIVE_FLEX_CONTEXT_CODE <> NVL(P_DESC_FLEX_CONTEXT,' ')
1083 and col_vl.DESCRIPTIVE_FLEX_CONTEXT_CODE = ctx.DESCRIPTIVE_FLEX_CONTEXT_CODE
1084 and ctx.GLOBAL_FLAG = 'Y'
1085 UNION ALL
1086 SELECT APPLICATION_COLUMN_NAME,
1087 FORM_LEFT_PROMPT,
1088 FORM_ABOVE_PROMPT
1089 FROM FND_DESCR_FLEX_COL_USAGE_VL
1090 WHERE APPLICATION_ID = P_APPLICATION_ID
1091 and DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEX_DEF_NAME
1092 and DESCRIPTIVE_FLEX_CONTEXT_CODE = NVL(P_DESC_FLEX_CONTEXT,' ');
1093
1094 l_prompt_rec GET_DESC_FLEX_PROMPTS%ROWTYPE;
1095 BEGIN
1096 OPEN GET_DESC_FLEX_PROMPTS;
1097 LOOP
1098 FETCH GET_DESC_FLEX_PROMPTS INTO l_prompt_rec;
1099 EXIT WHEN GET_DESC_FLEX_PROMPTS%NOTFOUND;
1100 IF P_PROMPT_TYPE = 'LEFT' THEN
1101 IF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN1_NAME THEN
1102 P_COLUMN1_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1103 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN2_NAME THEN
1104 P_COLUMN2_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1105 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN3_NAME THEN
1106 P_COLUMN3_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1107 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN4_NAME THEN
1108 P_COLUMN4_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1109 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN5_NAME THEN
1110 P_COLUMN5_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1111 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN6_NAME THEN
1112 P_COLUMN6_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1113 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN7_NAME THEN
1114 P_COLUMN7_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1115 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN8_NAME THEN
1116 P_COLUMN8_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1117 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN9_NAME THEN
1118 P_COLUMN9_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1119 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN10_NAME THEN
1120 P_COLUMN10_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1121 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN11_NAME THEN
1122 P_COLUMN11_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1123 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN12_NAME THEN
1124 P_COLUMN12_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1125 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN13_NAME THEN
1126 P_COLUMN13_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1127 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN14_NAME THEN
1128 P_COLUMN14_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1129 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN15_NAME THEN
1130 P_COLUMN15_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1131 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN16_NAME THEN
1132 P_COLUMN16_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1133 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN17_NAME THEN
1134 P_COLUMN17_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1135 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN18_NAME THEN
1136 P_COLUMN18_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1137 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN19_NAME THEN
1138 P_COLUMN19_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1139 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN20_NAME THEN
1140 P_COLUMN20_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1141 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN21_NAME THEN
1142 P_COLUMN21_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1143 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN22_NAME THEN
1144 P_COLUMN22_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1145 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN23_NAME THEN
1146 P_COLUMN23_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1147 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN24_NAME THEN
1148 P_COLUMN24_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1149 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN25_NAME THEN
1150 P_COLUMN25_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1151 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN26_NAME THEN
1152 P_COLUMN26_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1153 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN27_NAME THEN
1154 P_COLUMN27_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1155 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN28_NAME THEN
1156 P_COLUMN28_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1157 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN29_NAME THEN
1158 P_COLUMN29_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1159 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN30_NAME THEN
1160 P_COLUMN30_PROMPT := l_prompt_rec.FORM_LEFT_PROMPT;
1161 END IF;
1162
1163 ELSIF P_PROMPT_TYPE = 'ABOVE' THEN
1164 IF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN1_NAME THEN
1165 P_COLUMN1_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1166 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN2_NAME THEN
1167 P_COLUMN2_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1168 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN3_NAME THEN
1169 P_COLUMN3_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1170 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN4_NAME THEN
1171 P_COLUMN4_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1172 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN5_NAME THEN
1173 P_COLUMN5_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1174 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN6_NAME THEN
1175 P_COLUMN6_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1176 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN7_NAME THEN
1177 P_COLUMN7_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1178 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN8_NAME THEN
1179 P_COLUMN8_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1180 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN9_NAME THEN
1181 P_COLUMN9_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1182 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN10_NAME THEN
1183 P_COLUMN10_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1184 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN11_NAME THEN
1185 P_COLUMN11_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1186 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN12_NAME THEN
1187 P_COLUMN12_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1188 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN13_NAME THEN
1189 P_COLUMN13_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1190 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN14_NAME THEN
1191 P_COLUMN14_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1192 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN15_NAME THEN
1193 P_COLUMN15_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1194 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN16_NAME THEN
1195 P_COLUMN16_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1196 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN17_NAME THEN
1197 P_COLUMN17_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1198 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN18_NAME THEN
1199 P_COLUMN18_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1200 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN19_NAME THEN
1201 P_COLUMN19_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1202 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN20_NAME THEN
1203 P_COLUMN20_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1204 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN21_NAME THEN
1205 P_COLUMN21_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1206 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN22_NAME THEN
1207 P_COLUMN22_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1208 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN23_NAME THEN
1209 P_COLUMN23_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1210 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN24_NAME THEN
1211 P_COLUMN24_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1212 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN25_NAME THEN
1213 P_COLUMN25_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1214 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN26_NAME THEN
1215 P_COLUMN26_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1216 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN27_NAME THEN
1217 P_COLUMN27_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1218 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN28_NAME THEN
1219 P_COLUMN28_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1220 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN29_NAME THEN
1221 P_COLUMN29_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1222 ELSIF l_prompt_rec.APPLICATION_COLUMN_NAME = P_COLUMN30_NAME THEN
1223 P_COLUMN30_PROMPT := l_prompt_rec.FORM_ABOVE_PROMPT;
1224 END IF;
1225 END IF;
1226 END LOOP;
1227 CLOSE GET_DESC_FLEX_PROMPTS;
1228 END GET_DESC_FLEX_ALL_PROMPTS;
1229
1230
1231 -- Bug 4501520 :rvsingh:start
1232
1233 /*******************************************************************************
1234 ***** This procedure returns all 30 Descriptive flex field values ****
1235 ***** It accepts ****
1236 ***** Application ID -- Descriptive Flexed Field owner Application ****
1237 ***** DESC_FLEX_DEF_NAME -- Name of the Flex Definition ****
1238 ***** DESC_FLEX_CONTEXT -- Flex Definition context(ATTRIBUTE_CATEGORY) ****
1239 ***** P_COLUMN1_NAME ..P_COLUMN30_NAME -- Name of the columns ****
1240 ***** P_COLUMN1_ID_VAL ..P_COLUMN30_ID_VAL -- ID or values of columns passed*
1241 ***** if the id is passed then corresponding value****
1242 ***** else ****
1243 ***** value is returned ****
1244 ***** COLUMN1_VAL -- Returns value for Column Name passed ****
1245 ***** ....... COLUMN30_VAL ****
1246 ********************************************************************************/
1247
1248 PROCEDURE GET_DESC_FLEX_ALL_VALUES(P_APPLICATION_ID IN NUMBER,
1249 P_DESC_FLEX_DEF_NAME IN VARCHAR2,
1250 P_DESC_FLEX_CONTEXT IN VARCHAR2,
1251 P_COLUMN1_NAME IN VARCHAR2,
1252 P_COLUMN2_NAME IN VARCHAR2,
1253 P_COLUMN3_NAME IN VARCHAR2,
1254 P_COLUMN4_NAME IN VARCHAR2,
1255 P_COLUMN5_NAME IN VARCHAR2,
1256 P_COLUMN6_NAME IN VARCHAR2,
1257 P_COLUMN7_NAME IN VARCHAR2,
1258 P_COLUMN8_NAME IN VARCHAR2,
1259 P_COLUMN9_NAME IN VARCHAR2,
1260 P_COLUMN10_NAME IN VARCHAR2,
1261 P_COLUMN11_NAME IN VARCHAR2,
1262 P_COLUMN12_NAME IN VARCHAR2,
1263 P_COLUMN13_NAME IN VARCHAR2,
1264 P_COLUMN14_NAME IN VARCHAR2,
1265 P_COLUMN15_NAME IN VARCHAR2,
1266 P_COLUMN16_NAME IN VARCHAR2,
1267 P_COLUMN17_NAME IN VARCHAR2,
1268 P_COLUMN18_NAME IN VARCHAR2,
1269 P_COLUMN19_NAME IN VARCHAR2,
1270 P_COLUMN20_NAME IN VARCHAR2,
1271 P_COLUMN21_NAME IN VARCHAR2,
1272 P_COLUMN22_NAME IN VARCHAR2,
1273 P_COLUMN23_NAME IN VARCHAR2,
1274 P_COLUMN24_NAME IN VARCHAR2,
1275 P_COLUMN25_NAME IN VARCHAR2,
1276 P_COLUMN26_NAME IN VARCHAR2,
1277 P_COLUMN27_NAME IN VARCHAR2,
1278 P_COLUMN28_NAME IN VARCHAR2,
1279 P_COLUMN29_NAME IN VARCHAR2,
1280 P_COLUMN30_NAME IN VARCHAR2,
1281 P_COLUMN1_ID_VAL IN VARCHAR2,
1282 P_COLUMN2_ID_VAL IN VARCHAR2,
1283 P_COLUMN3_ID_VAL IN VARCHAR2,
1284 P_COLUMN4_ID_VAL IN VARCHAR2,
1285 P_COLUMN5_ID_VAL IN VARCHAR2,
1286 P_COLUMN6_ID_VAL IN VARCHAR2,
1287 P_COLUMN7_ID_VAL IN VARCHAR2,
1288 P_COLUMN8_ID_VAL IN VARCHAR2,
1289 P_COLUMN9_ID_VAL IN VARCHAR2,
1290 P_COLUMN10_ID_VAL IN VARCHAR2,
1291 P_COLUMN11_ID_VAL IN VARCHAR2,
1292 P_COLUMN12_ID_VAL IN VARCHAR2,
1293 P_COLUMN13_ID_VAL IN VARCHAR2,
1294 P_COLUMN14_ID_VAL IN VARCHAR2,
1295 P_COLUMN15_ID_VAL IN VARCHAR2,
1296 P_COLUMN16_ID_VAL IN VARCHAR2,
1297 P_COLUMN17_ID_VAL IN VARCHAR2,
1298 P_COLUMN18_ID_VAL IN VARCHAR2,
1299 P_COLUMN19_ID_VAL IN VARCHAR2,
1300 P_COLUMN20_ID_VAL IN VARCHAR2,
1301 P_COLUMN21_ID_VAL IN VARCHAR2,
1302 P_COLUMN22_ID_VAL IN VARCHAR2,
1303 P_COLUMN23_ID_VAL IN VARCHAR2,
1304 P_COLUMN24_ID_VAL IN VARCHAR2,
1305 P_COLUMN25_ID_VAL IN VARCHAR2,
1306 P_COLUMN26_ID_VAL IN VARCHAR2,
1307 P_COLUMN27_ID_VAL IN VARCHAR2,
1308 P_COLUMN28_ID_VAL IN VARCHAR2,
1309 P_COLUMN29_ID_VAL IN VARCHAR2,
1310 P_COLUMN30_ID_VAL IN VARCHAR2,
1311 P_COLUMN1_VAL OUT NOCOPY VARCHAR2,
1312 P_COLUMN2_VAL OUT NOCOPY VARCHAR2,
1313 P_COLUMN3_VAL OUT NOCOPY VARCHAR2,
1314 P_COLUMN4_VAL OUT NOCOPY VARCHAR2,
1315 P_COLUMN5_VAL OUT NOCOPY VARCHAR2,
1316 P_COLUMN6_VAL OUT NOCOPY VARCHAR2,
1317 P_COLUMN7_VAL OUT NOCOPY VARCHAR2,
1318 P_COLUMN8_VAL OUT NOCOPY VARCHAR2,
1319 P_COLUMN9_VAL OUT NOCOPY VARCHAR2,
1320 P_COLUMN10_VAL OUT NOCOPY VARCHAR2,
1321 P_COLUMN11_VAL OUT NOCOPY VARCHAR2,
1322 P_COLUMN12_VAL OUT NOCOPY VARCHAR2,
1323 P_COLUMN13_VAL OUT NOCOPY VARCHAR2,
1324 P_COLUMN14_VAL OUT NOCOPY VARCHAR2,
1325 P_COLUMN15_VAL OUT NOCOPY VARCHAR2,
1326 P_COLUMN16_VAL OUT NOCOPY VARCHAR2,
1327 P_COLUMN17_VAL OUT NOCOPY VARCHAR2,
1328 P_COLUMN18_VAL OUT NOCOPY VARCHAR2,
1329 P_COLUMN19_VAL OUT NOCOPY VARCHAR2,
1330 P_COLUMN20_VAL OUT NOCOPY VARCHAR2,
1331 P_COLUMN21_VAL OUT NOCOPY VARCHAR2,
1332 P_COLUMN22_VAL OUT NOCOPY VARCHAR2,
1333 P_COLUMN23_VAL OUT NOCOPY VARCHAR2,
1334 P_COLUMN24_VAL OUT NOCOPY VARCHAR2,
1335 P_COLUMN25_VAL OUT NOCOPY VARCHAR2,
1336 P_COLUMN26_VAL OUT NOCOPY VARCHAR2,
1337 P_COLUMN27_VAL OUT NOCOPY VARCHAR2,
1338 P_COLUMN28_VAL OUT NOCOPY VARCHAR2,
1339 P_COLUMN29_VAL OUT NOCOPY VARCHAR2,
1340 P_COLUMN30_VAL OUT NOCOPY VARCHAR2) IS
1341 l_appl_short_name varchar2(30) := 'EDR';
1342 l_values_or_ids varchar2(10) := 'I';
1343 l_validation_date DATE := SYSDATE;
1344 l_segcount number;
1345 l_attrrcount number;
1346 l_error_segment varchar2(30);
1347 errors_received EXCEPTION;
1348 BEGIN
1349 l_attrrcount :=0;
1350 l_segcount :=0;
1351
1352 select count(*) INTO l_attrrcount from (
1353 SELECT col_vl.APPLICATION_COLUMN_NAME
1354 FROM FND_DESCR_FLEX_COL_USAGE_VL col_vl,fnd_descr_flex_contexts ctx
1355 WHERE col_vl.APPLICATION_ID = P_APPLICATION_ID
1356 and col_vl.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEX_DEF_NAME
1357 and col_vl.APPLICATION_ID = ctx.APPLICATION_ID
1358 and col_vl.DESCRIPTIVE_FLEXFIELD_NAME = ctx.DESCRIPTIVE_FLEXFIELD_NAME
1359 and col_vl.DESCRIPTIVE_FLEX_CONTEXT_CODE <> NVL(P_DESC_FLEX_CONTEXT,' ')
1360 and col_vl.DESCRIPTIVE_FLEX_CONTEXT_CODE = ctx.DESCRIPTIVE_FLEX_CONTEXT_CODE
1361 and ctx.GLOBAL_FLAG = 'Y'
1362 UNION ALL
1363 SELECT APPLICATION_COLUMN_NAME
1364 FROM FND_DESCR_FLEX_COL_USAGE_VL
1365 WHERE APPLICATION_ID = P_APPLICATION_ID
1366 and DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEX_DEF_NAME
1367 and DESCRIPTIVE_FLEX_CONTEXT_CODE = NVL(P_DESC_FLEX_CONTEXT,' '));
1368
1369 -- check for valid context for flexfield
1370 IF(l_attrrcount > 0) THEN
1371
1372 FND_FLEX_DESCVAL.set_context_value(P_DESC_FLEX_CONTEXT);
1373 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE1',P_COLUMN1_ID_VAL);
1374 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE2',P_COLUMN2_ID_VAL);
1375 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE3',P_COLUMN3_ID_VAL);
1376 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE4',P_COLUMN4_ID_VAL);
1377 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE5',P_COLUMN5_ID_VAL);
1378 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE6',P_COLUMN6_ID_VAL);
1379 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE7',P_COLUMN7_ID_VAL);
1380 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE8',P_COLUMN8_ID_VAL);
1381 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE9',P_COLUMN9_ID_VAL);
1382 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE10',P_COLUMN10_ID_VAL);
1383 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE11',P_COLUMN11_ID_VAL);
1384 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE12',P_COLUMN12_ID_VAL);
1385 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE13',P_COLUMN13_ID_VAL);
1386 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE14',P_COLUMN14_ID_VAL);
1387 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE15',P_COLUMN15_ID_VAL);
1388 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE16',P_COLUMN16_ID_VAL);
1389 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE17',P_COLUMN17_ID_VAL);
1390 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE18',P_COLUMN18_ID_VAL);
1391 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE19',P_COLUMN19_ID_VAL);
1392 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE20',P_COLUMN20_ID_VAL);
1393 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE21',P_COLUMN21_ID_VAL);
1394 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE22',P_COLUMN22_ID_VAL);
1395 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE23',P_COLUMN23_ID_VAL);
1396 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE24',P_COLUMN24_ID_VAL);
1397 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE25',P_COLUMN25_ID_VAL);
1398 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE26',P_COLUMN26_ID_VAL);
1399 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE27',P_COLUMN27_ID_VAL);
1400 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE28',P_COLUMN28_ID_VAL);
1401 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE29',P_COLUMN29_ID_VAL);
1402 FND_FLEX_DESCVAL.set_column_value('ATTRIBUTE30',P_COLUMN30_ID_VAL);
1403
1404 IF FND_FLEX_DESCVAL.validate_desccols(l_appl_short_name,P_DESC_FLEX_DEF_NAME,l_values_or_ids,l_validation_date) THEN
1405 wf_log_pkg.string(6, 'GET_DESC_FLEX_ALL_VALUES','Function completed successfully');
1406 l_segcount := FND_FLEX_DESCVAL.segment_count;
1407 -- FND_FLEX_DESCVAL.segment_value(1) - Segment name
1408 IF(l_segcount > 1)THEN
1409 FOR i in 2..l_segcount LOOP
1410 IF FND_FLEX_DESCVAL.segment_column_name(i) = P_COLUMN1_NAME THEN
1411 P_COLUMN1_VAL := FND_FLEX_DESCVAL.segment_value(i);
1412 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN2_NAME THEN
1413 P_COLUMN2_VAL := FND_FLEX_DESCVAL.segment_value(i);
1414 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN3_NAME THEN
1415 P_COLUMN3_VAL := FND_FLEX_DESCVAL.segment_value(i);
1416 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN4_NAME THEN
1417 P_COLUMN4_VAL := FND_FLEX_DESCVAL.segment_value(i);
1418 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN5_NAME THEN
1419 P_COLUMN5_VAL := FND_FLEX_DESCVAL.segment_value(i);
1420 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN6_NAME THEN
1421 P_COLUMN6_VAL := FND_FLEX_DESCVAL.segment_value(i);
1422 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN7_NAME THEN
1423 P_COLUMN7_VAL := FND_FLEX_DESCVAL.segment_value(i);
1424 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN8_NAME THEN
1425 P_COLUMN8_VAL := FND_FLEX_DESCVAL.segment_value(i);
1426 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN9_NAME THEN
1427 P_COLUMN9_VAL := FND_FLEX_DESCVAL.segment_value(i);
1428 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN10_NAME THEN
1429 P_COLUMN10_VAL := FND_FLEX_DESCVAL.segment_value(i);
1430 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN11_NAME THEN
1431 P_COLUMN11_VAL := FND_FLEX_DESCVAL.segment_value(i);
1432 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN12_NAME THEN
1433 P_COLUMN12_VAL := FND_FLEX_DESCVAL.segment_value(i);
1434 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN13_NAME THEN
1435 P_COLUMN13_VAL := FND_FLEX_DESCVAL.segment_value(i);
1436 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN14_NAME THEN
1437 P_COLUMN14_VAL := FND_FLEX_DESCVAL.segment_value(i);
1438 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN15_NAME THEN
1439 P_COLUMN15_VAL := FND_FLEX_DESCVAL.segment_value(i);
1440 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN16_NAME THEN
1441 P_COLUMN16_VAL := FND_FLEX_DESCVAL.segment_value(i);
1442 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN17_NAME THEN
1443 P_COLUMN17_VAL := FND_FLEX_DESCVAL.segment_value(i);
1444 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN18_NAME THEN
1445 P_COLUMN18_VAL := FND_FLEX_DESCVAL.segment_value(i);
1446 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN19_NAME THEN
1447 P_COLUMN19_VAL := FND_FLEX_DESCVAL.segment_value(i);
1448 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN20_NAME THEN
1449 P_COLUMN20_VAL := FND_FLEX_DESCVAL.segment_value(i);
1450 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN21_NAME THEN
1451 P_COLUMN21_VAL := FND_FLEX_DESCVAL.segment_value(i);
1452 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN22_NAME THEN
1453 P_COLUMN22_VAL := FND_FLEX_DESCVAL.segment_value(i);
1454 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN23_NAME THEN
1455 P_COLUMN23_VAL := FND_FLEX_DESCVAL.segment_value(i);
1456 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN24_NAME THEN
1457 P_COLUMN24_VAL := FND_FLEX_DESCVAL.segment_value(i);
1458 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN25_NAME THEN
1459 P_COLUMN25_VAL := FND_FLEX_DESCVAL.segment_value(i);
1460 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN26_NAME THEN
1461 P_COLUMN26_VAL := FND_FLEX_DESCVAL.segment_value(i);
1462 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN27_NAME THEN
1463 P_COLUMN27_VAL := FND_FLEX_DESCVAL.segment_value(i);
1464 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN28_NAME THEN
1465 P_COLUMN28_VAL := FND_FLEX_DESCVAL.segment_value(i);
1466 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN29_NAME THEN
1467 P_COLUMN29_VAL := FND_FLEX_DESCVAL.segment_value(i);
1468 ELSIF FND_FLEX_DESCVAL.segment_column_name(i)= P_COLUMN30_NAME THEN
1469 P_COLUMN30_VAL := FND_FLEX_DESCVAL.segment_value(i);
1470 END IF;
1471 END LOOP;
1472 END IF;
1473 ELSE
1474 l_error_segment := FND_FLEX_DESCVAL.error_segment;
1475 RAISE errors_received;
1476 END IF;
1477 END IF;
1478
1479 EXCEPTION
1480 WHEN errors_received THEN
1481 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
1482 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',fnd_flex_descval.error_message);
1483 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_STANDARD');
1484 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GET_DESC_FLEX_ALL_VALUES');
1485 raise;
1486 END GET_DESC_FLEX_ALL_VALUES;
1487
1488 -- Bug 4501520 :rvsingh:end
1489 /*******************************************************************************
1490 ***** This procedure returns Lookup code meaning ****
1491 ***** It accepts ****
1492 ***** LOOKUP_TYPE and LOOKUP CODE as in parameter and Returns MEANING ****
1493 ***** as out parameter. This uses FND_LOOKUPS View ****
1494 ********************************************************************************/
1495
1496 PROCEDURE GET_MEANING(P_LOOKUP_TYPE IN VARCHAR2,
1497 P_LOOKUP_CODE IN VARCHAR2,
1498 P_MEANING OUT NOCOPY VARCHAR2) IS
1499 CURSOR GET_LKUP_MEANING IS
1500 SELECT MEANING
1501 FROM FND_LOOKUPS
1502 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
1503 AND LOOKUP_CODE = P_LOOKUP_CODE;
1504 BEGIN
1505 OPEN GET_LKUP_MEANING;
1506 FETCH GET_LKUP_MEANING INTO P_MEANING;
1507 CLOSE GET_LKUP_MEANING;
1508 END;
1509
1510
1511 -- bug 4865689 :start
1512 /*******************************************************************************
1513 ***** This procedure returns USER RESPONSE ****
1514 ***** It accepts ****
1515 ***** LOOKUP_TYPE and PSIG_STATUS as in parameter and Returns RESPONSE ****
1516 ***** as out parameter. This uses FND_LOOKUPS View ****
1517 ********************************************************************************/
1518
1519 PROCEDURE GET_USER_RESPONSE(P_LOOKUP_TYPE IN VARCHAR2,
1520 P_PSIG_STATUS IN VARCHAR2,
1521 P_RESPONSE OUT NOCOPY VARCHAR2) IS
1522 L_LOOKUP_CODE VARCHAR2(30);
1523 CURSOR GET_LKUP_MEANING IS
1524 SELECT MEANING
1525 FROM FND_LOOKUPS
1526 WHERE LOOKUP_TYPE = P_LOOKUP_TYPE
1527 AND LOOKUP_CODE = L_LOOKUP_CODE;
1528 BEGIN
1529 IF(P_PSIG_STATUS = 'REJECTED') THEN
1530 P_RESPONSE := null;
1531 ELSIF(P_PSIG_STATUS = 'COMPLETE') THEN
1532 P_RESPONSE := null;
1533 ELSIF (P_PSIG_STATUS = 'ERROR') THEN
1534 P_RESPONSE := null;
1535 ELSIF (P_PSIG_STATUS = 'CANCEL') THEN
1536 L_LOOKUP_CODE := 'CANCELED';
1537 OPEN GET_LKUP_MEANING;
1538 FETCH GET_LKUP_MEANING INTO P_RESPONSE;
1539 CLOSE GET_LKUP_MEANING;
1540 ELSE
1541 L_LOOKUP_CODE := 'WAITING';
1542 OPEN GET_LKUP_MEANING;
1543 FETCH GET_LKUP_MEANING INTO P_RESPONSE;
1544 CLOSE GET_LKUP_MEANING;
1545 END IF;
1546 END;
1547 -- bug 4865689 :end
1548
1549 /****+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++****
1550 ***** This procedure returns single query id for eRecords ****
1551 ***** It is a simplied version of PSIG_QUERY for Java api ****
1552 ***** It accepts strings of event name, event key and query type ****
1553 *****+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++****/
1554
1555 PROCEDURE PSIG_QUERY_ONE( p_event_name IN FND_TABLE_OF_VARCHAR2_255,
1556 p_event_key IN FND_TABLE_OF_VARCHAR2_255,
1557 o_query_id OUT NOCOPY NUMBER )
1558 IS
1559 ith NUMBER;
1560 nEvents NUMBER;
1561 l_single_array EDR_STANDARD.eventQuery;
1562 BEGIN
1563 nEvents := p_event_name.COUNT;
1564
1565 IF p_event_key.COUNT = nEvents THEN
1566
1567 FOR ith in 1..nEvents LOOP
1568 -- l_single_array.extend; -- can't extend table...
1569
1570 l_single_array(ith).event_name := p_event_name(ith); -- substr(p_event_name(ith),1,240);
1571 l_single_array(ith).event_key := p_event_key(ith); -- substr(p_event_key(ith), 1,240);
1572 l_single_array(ith).key_type := 'WHERE_CLAUSE';
1573 END LOOP;
1574 o_query_id := EDR_STANDARD.PSIG_QUERY(l_single_array);
1575
1576 ELSE
1577 o_query_id := 0;
1578 END IF;
1579
1580 END;
1581
1582
1583 end EDR_STANDARD;