1 PACKAGE BODY IEU_PVT AS
2 /* $Header: IEU_VB.pls 120.4.12010000.4 2008/10/24 09:57:47 majha ship $ */
3
4 IEU_UWQ_SEL_RT_NODES_LIST IEU_UWQ_SEL_RT_NODES_TAB;
5 IEU_UWQ_RTNODE_BIND_VALS_LIST IEU_UWQ_RTNODE_BIND_VALS_TAB;
6
7 SEL_RT_NODE_ID_LIST NUMBER_TAB;
8 CREATED_BY_LIST NUMBER_TAB;
9 CREATION_DATE_LIST DATE_TAB;
10 LAST_UPDATED_BY_LIST NUMBER_TAB;
11 LAST_UPDATE_DATE_LIST DATE_TAB;
12 LAST_UPDATE_LOGIN_LIST NUMBER_TAB;
13 RESOURCE_ID_LIST NUMBER_TAB;
14 SEL_ENUM_ID_LIST NUMBER_TAB;
15 NODE_ID_LIST NUMBER_TAB;
16 NODE_TYPE_LIST NUMBER_TAB;
17 NODE_LABEL_LIST NODE_LABEL_TAB;
18 COUNT_LIST NUMBER_TAB;
19 DATA_SOURCE_LIST DATA_SOURCE_TAB;
20 VIEW_NAME_LIST VIEW_NAME_TAB;
21 MEDIA_TYPE_ID_LIST NUMBER_TAB;
22 SEL_ENUM_PID_LIST NUMBER_TAB;
23 NODE_PID_LIST NUMBER_TAB;
24 NODE_WEIGHT_LIST NUMBER_TAB;
25 WHERE_CLAUSE_LIST WHERE_CLAUSE_TAB;
26 HIDE_IF_EMPTY_LIST HIDE_IF_EMPTY_TAB;
27 NOT_VALID_LIST NOT_VALID_TAB;
28 SECURITY_GROUP_ID_LIST NUMBER_TAB;
29 OBJECT_VERSION_NUMBER_LIST NUMBER_TAB;
30 REFRESH_VIEW_NAME_LIST REFRESH_VIEW_NAME_TAB;
31 RES_CAT_ENUM_FLAG_LIST RES_CAT_ENUM_FLAG_TAB;
32 REFRESH_VIEW_SUM_COL_LIST REFRESH_VIEW_SUM_COL_TAB;
33 NODE_DEPTH_LIST NUMBER_TAB;
34
35 BIND_OBJ_VERSION_NUMBER_LIST NUMBER_TAB;
36 BIND_CREATED_BY_LIST NUMBER_TAB;
37 BIND_CREATION_DATE_LIST DATE_TAB;
38 BIND_LAST_UPDATED_BY_LIST NUMBER_TAB;
39 BIND_LAST_UPDATE_DATE_LIST DATE_TAB;
40 BIND_LAST_UPDATE_LOGIN_LIST NUMBER_TAB;
41 BIND_SECURITY_GROUP_ID_LIST NUMBER_TAB;
42 BIND_SEL_RT_NODE_ID_LIST NUMBER_TAB;
43 BIND_RESOURCE_ID_LIST NUMBER_TAB;
44 BIND_NODE_ID_LIST NUMBER_TAB;
45 BIND_VAR_NAME_LIST BIND_VAR_NAME_TAB;
46 BIND_VAR_VALUE_LIST BIND_VAR_VALUE_TAB;
47 BIND_VAR_DATATYPE_LIST BIND_VAR_DATATYPE_TAB;
48 NOT_VALID_FLAG_LIST NOT_VALID_FLAG_TAB;
49
50 SEL_RT_NODE_ID_REF_LIST NUMBER_TAB;
51 REF_COUNT_LIST NUMBER_TAB;
52
53 L_IND_LIST_ITR NUMBER;
54 L_RT_NODES_ITR NUMBER;
55 L_BIND_VALS_ITR NUMBER;
56 L_SEL_REF_COUNTER NUMBER;
57
58 array_dml_errors EXCEPTION;
59 PRAGMA exception_init(array_dml_errors, -24381);
60
61 -- Sub-Program Units
62
63
64 /* Used to determine what style of WB is set for an agent */
65 FUNCTION DETERMINE_WB_STYLE ( RESOURCE_ID IN NUMBER ) RETURN VARCHAR2
66 AS
67
68 l_wb_style VARCHAR2(1000);
69
70 BEGIN
71
72 -- Work Blending Styles are as follows:
73 --
74 -- 'N' - Not Blended
75 -- 'O' - Optional Blended
76 -- 'F' - Force Blended
77
78 BEGIN
79 FND_PROFILE.GET( 'IEU_BLENDING_STYLE', l_wb_style );
80 EXCEPTION
81 WHEN OTHERS THEN
82 l_wb_style := 'N';
83 END;
84
85 --
86 -- Putting some protective logic in here... if we get an invalid style
87 -- then we'll force it to 'N' not-blended.
88 --
89 -- This is because we don't have time to implement the LOV logic for the
90 -- profile SQL statement, and I'm worried that someone can enter crap for
91 -- the blending style.
92 --
93 if ( (l_wb_style <> 'F') and (l_wb_style <> 'SF')
94 and (l_wb_style <> 'O') and (l_wb_style <> 'SO') )
95 then
96 l_wb_style := 'N';
97 end if;
98
99 return l_wb_style;
100
101 END DETERMINE_WB_STYLE;
102
103
104 /* Used to determine classes to load by the client plugin loader */
105 PROCEDURE DETERMINE_CLI_PLUGINS
106 (P_RESOURCE_ID IN NUMBER
107 ,X_CLASSES OUT NOCOPY ClientClasses
108 )
109 AS
110
111 l_media_types EligibleMediaList;
112 l_class IEU_UWQ_CLI_MED_PLUGINS.CLI_PLUGIN_CLASS%TYPE;
113
114 j NUMBER := 0;
115
116 BEGIN
117
118 --
119 -- We detect what the agent can work on, and relate this to what plugins
120 -- need to be loaded (based on media type id).
121 --
122 DETERMINE_ELIGIBLE_MEDIA_TYPES(
123 P_RESOURCE_ID,
124 l_media_types );
125
126 IF (l_media_types is not null and l_media_types.COUNT > 0) THEN
127
128 -- x_classes := ClientClasses();
129
130 FOR i IN l_media_types.FIRST..l_media_types.LAST LOOP
131
132 BEGIN
133
134 SELECT DISTINCT
135 ptable.CLI_PLUGIN_CLASS
136 INTO
137 l_class
138 FROM
139 IEU_UWQ_CLI_MED_PLUGINS ptable
140 WHERE
141 (ptable.MEDIA_TYPE_ID = l_media_types(i).media_type_id);
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 l_class := '';
146
147 END;
148
149 IF (l_class is not null) THEN
150 j := j+1;
151 X_CLASSES(j) := l_class;
152 END IF;
153
154 END LOOP;
155
156 END IF;
157
158 /*
159 IF (FND_PROFILE.VALUE('IEU_MDEN_TELEPHONY') = 'Y')
160 THEN
161 --
162 -- assume that the plugin for INBOUND or OUTBOUND will do...
163 -- picking by who's been around the longest...
164 --
165 BEGIN
166 SELECT
167 DISTINCT
168 ptable.CLI_PLUGIN_CLASS
169 INTO
170 l_class
171 FROM
172 IEU_UWQ_CLI_MED_PLUGINS ptable
173 WHERE
174 ( (ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_INBOUND_TELEPHONY)
175 OR
176 (ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_OUTBOUND_TELEPHONY)
177 OR
178 (ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_ADV_OUTB_TELEPHONY))
179 AND
180 (ROWNUM <= 1)
181 ORDER BY
182 ptable.CLI_PLUGIN_ID;
183 EXCEPTION
184 WHEN OTHERS THEN
185 l_class := '';
186 END;
187
188 --
189 -- make sure the class gets loaded at run-time...
190 --
191 IF (l_class is not null) THEN
192 j := j+1;
193 X_CLASSES(j) := l_class;
194 END IF;
195
196 END IF;
197
198
199 IF (FND_PROFILE.VALUE('IEU_MDEN_EMAIL') = 'Y')
200 THEN
201
202 --
203 -- assume that the plugin for one of the email enablers will do...
204 -- picking by who's been around the longest...
205 --
206 BEGIN
207 SELECT
208 DISTINCT
209 ptable.CLI_PLUGIN_CLASS
210 INTO
211 l_class
212 FROM
213 IEU_UWQ_CLI_MED_PLUGINS ptable
214 WHERE
215 ( (ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_INBOUND_EMAIL) OR
216 (ptable.MEDIA_TYPE_ID = IEU_CONSTS_PUB.G_MTID_DIRECT_EMAIL) ) AND
217 (ROWNUM <= 1)
218 ORDER BY
219 ptable.CLI_PLUGIN_ID;
220 EXCEPTION
221 WHEN OTHERS THEN
222 l_class := '';
223 END;
224
225 --
226 -- make sure the class gets loaded at run-time...
227 --
228 IF (l_class is not null) THEN
229 j := j+1;
230 X_CLASSES(j) := l_class;
231 END IF;
232
233 END IF;
234 */
235
236 END DETERMINE_CLI_PLUGINS;
237
238 PROCEDURE DETERMINE_ALL_MEDIA_TYPES_EXTN
239 (P_RESOURCE_ID IN NUMBER,
240 X_ALL_MEDIA_LIST OUT NOCOPY EligibleAllMediaList,
241 X_EXTN_FLAG OUT NOCOPY VARCHAR2)
242 AS
243
244 BEGIN
245 DETERMINE_ALL_MEDIA_TYPES(P_RESOURCE_ID, X_ALL_MEDIA_LIST);
246 X_EXTN_FLAG := IS_TEL_EXTN_REQUIRED (X_ALL_MEDIA_LIST);
247 END DETERMINE_ALL_MEDIA_TYPES_EXTN;
248
249 PROCEDURE DETERMINE_ALL_MEDIA_TYPES
250
251 (P_RESOURCE_ID IN NUMBER,
252 X_ALL_MEDIA_LIST OUT NOCOPY EligibleAllMediaList
253 ) AS
254
255 l_media_type_uuid varchar2(38);
256 l_ctr pls_integer;
257 l_elg_media_list EligibleMediaList;
258 l_all_media_list EligibleAllMediaList;
259 l_uuid_string_list varchar2(4000);
260
261 l_tel_reqd_flag varchar2(1);
262 l_svr_connect_rule varchar2(255);
263 l_tel_media_type varchar2(255);
264
265 str varchar2(255);
266 text varchar2(255);
267 pos number;
268 len number;
269 l_media_type_uuid_1 varchar2(38);
270 l_media_type_id_1 number;
271
272 BEGIN
273
274 DETERMINE_ELIGIBLE_MEDIA_TYPES(P_RESOURCE_ID, l_elg_media_list);
275
276 l_ctr := l_elg_media_list.count;
277
278 FOR i IN 1..l_elg_media_list.COUNT LOOP
279
280 l_media_type_uuid := l_elg_media_list(i).MEDIA_TYPE_UUID;
281
282 begin
283 select a.tel_reqd_flag, decode(b.login_rule_type, 'FUNC', b.login_rule, null),
284 decode(b.login_rule_type, 'MUUID', b.login_rule, null)
285 into l_tel_reqd_flag, l_svr_connect_rule, l_tel_media_type
286 from ieu_uwq_media_types_b a, ieu_uwq_login_rules_b b
287 where a.media_type_uuid = l_media_type_uuid
288 and a.svr_login_rule_id = b.svr_login_rule_id(+);
289 exception
290 when no_data_found then null;
291 end;
292
293 X_ALL_MEDIA_LIST(i).MEDIA_TYPE_ID := l_elg_media_list(i).MEDIA_TYPE_ID;
294 X_ALL_MEDIA_LIST(i).MEDIA_TYPE_UUID := l_elg_media_list(i).MEDIA_TYPE_UUID;
295 X_ALL_MEDIA_LIST(i).tel_reqd_flag := l_tel_reqd_flag;
296 X_ALL_MEDIA_LIST(i).svr_connect_rule := l_svr_connect_rule;
297 X_ALL_MEDIA_LIST(i).tel_media_type := l_tel_media_type;
298 X_ALL_MEDIA_LIST(i).ORIGIN_FLAG := 'E';
299
300 if l_svr_connect_rule is not null then
301 execute immediate
302 'begin '|| l_svr_connect_rule || '(' || ':p_resource_id,' || ':p_media_type_uuid,' || ':l_uuid_string_list);end;'
303 using in p_resource_id, l_media_type_uuid, out l_uuid_string_list;
304 elsif l_tel_media_type is not null then
305 l_uuid_string_list := l_tel_media_type;
306 end if;
307
308 l_svr_connect_rule := null;
309 l_tel_media_type := null;
310
311 ------------- Parse string into separate UUID's ------------
312
313 l_ctr := i + 1;
314
315 str := l_uuid_string_list;
316 len := length(str);
317 select instr(str, '|') into pos from dual;
318 while len > 0 loop
319 select instr(str, '|') into pos from dual;
320 if pos = 0 then
321 text := str;
322 str := null;
323 else
324 text := substr(str,1, pos -1);
325 str := substr(str, pos + 1, len);
326 end if;
327 len := length(str);
328
329 l_media_type_uuid_1 := text;
330
331 begin
332
333 select a.media_type_id, a.media_type_uuid, a.tel_reqd_flag, decode(b.login_rule_type, 'FUNC', b.login_rule, null),
334 decode(b.login_rule_type, 'MUUID', b.login_rule, null)
335 into l_media_type_id_1, l_media_type_uuid_1, l_tel_reqd_flag, l_svr_connect_rule, l_tel_media_type
336 from ieu_uwq_media_types_b a, ieu_uwq_login_rules_b b
337 where a.media_type_uuid = l_media_type_uuid_1
338 and a.svr_login_rule_id = b.svr_login_rule_id(+);
339
340 X_ALL_MEDIA_LIST(l_ctr).MEDIA_TYPE_ID := l_media_type_id_1;
341 X_ALL_MEDIA_LIST(l_ctr).MEDIA_TYPE_UUID := l_media_type_uuid_1;
342 X_ALL_MEDIA_LIST(l_ctr).tel_reqd_flag := l_tel_reqd_flag;
343 X_ALL_MEDIA_LIST(l_ctr).svr_connect_rule := l_svr_connect_rule;
344 X_ALL_MEDIA_LIST(l_ctr).tel_media_type := l_tel_media_type;
345 X_ALL_MEDIA_LIST(l_ctr).ORIGIN_FLAG := 'R';
346
347 l_ctr := l_ctr + 1;
348 exception
349 when no_data_found then null;
350 end;
351 end loop;
352 END LOOP;
353 END DETERMINE_ALL_MEDIA_TYPES;
354
355
356 FUNCTION IS_TEL_EXTN_REQUIRED (p_eligibleallmedialist IN EligibleAllMediaList ) RETURN VARCHAR2
357 AS
358 BEGIN
359 IF p_eligibleAllMediaList is not null or p_eligibleAllMediaList.count > 0 then
360 for i in 1..p_eligibleAllMediaList.count loop
361 if p_eligibleAllMediaList(i).tel_reqd_flag = 'Y'
362 and p_eligibleAllMediaList(i).origin_flag = 'E'
363 and p_eligibleAllMediaList(i).svr_connect_rule is null
364 and p_eligibleAllMediaList(i).tel_media_type is null then
365 RETURN 'Y';
366 end if;
367 if p_eligibleAllMediaList(i).origin_flag = 'R'
368 and p_eligibleAllMediaList(i).tel_reqd_flag = 'Y' then
369 RETURN 'Y';
370 end if;
371 end loop;
372 END IF;
373 RETURN 'N';
374 END;
375
376 /* Used to determine the eligible media types the resource can work on */
377 PROCEDURE DETERMINE_ELIGIBLE_MEDIA_TYPES
378 (P_RESOURCE_ID IN NUMBER
379 ,X_PLUGINS OUT NOCOPY EligibleMediaList
380 )
381 AS
382
383 CURSOR c_types IS
384 SELECT
385 DISTINCT
386 mttab.MEDIA_TYPE_ID,
387 mttab.MEDIA_TYPE_UUID
388 FROM
389 -- sjm got rid of server side checks altogether for Client Provider enh.
390 -- this is simpler and more flexible for 3rd party providers
391 -- now just check for all media types that have media provider plugins defined
392 /* JTF_RS_RESOURCE_EXTNS restab,
393 IEO_SVR_SERVERS svrtab,
394 IEO_SVR_SERVERS svrtab2,
395 IEU_UWQ_SVR_MPS_MMAPS mmptab,
396 IEU_UWQ_MEDIA_TYPES_B mttab
397 WHERE
398 (restab.RESOURCE_ID = p_resource_id) AND
399 (restab.SERVER_GROUP_ID IS NOT NULL) AND
400 ( (svrtab.MEMBER_SVR_GROUP_ID IS NOT NULL) AND
401 (
402 (restab.SERVER_GROUP_ID = svrtab.MEMBER_SVR_GROUP_ID) OR
403 (
404 (svrtab.USING_SVR_GROUP_ID IS NOT NULL) AND
405 (svrtab.SERVER_ID = svrtab2.SERVER_ID) AND
406 (svrtab.USING_SVR_GROUP_ID = svrtab2.MEMBER_SVR_GROUP_ID) AND
407 (svrtab2.MEMBER_SVR_GROUP_ID = restab.SERVER_GROUP_ID)
408 )
409 )
410 ) AND
411 (svrtab.TYPE_ID = mmptab.SVR_TYPE_ID) AND
412 (mmptab.MEDIA_TYPE_ID = mttab.MEDIA_TYPE_ID);
413 */
414 IEU_UWQ_MEDIA_TYPES_B mttab
415 WHERE
416 mttab.MEDIA_TYPE_ID in
417 (SELECT MEDIA_TYPE_ID
418 FROM IEU_UWQ_SVR_MPS_MMAPS
419 UNION -- 10/5/04 changed to union #3926849
420 SELECT subclimap.MEDIA_TYPE_ID
421 FROM IEU_CLI_PROV_PLUGIN_MED_MAPS subclimap,
422 IEU_CLI_PROV_PLUGINS cliplugins
423 WHERE
424 subclimap.PLUGIN_ID = cliplugins.PLUGIN_ID
425 AND (cliplugins.IS_ACTIVE_FLAG is NULL
426 OR upper(cliplugins.IS_ACTIVE_FLAG) = 'Y')
427
428 );
429
430 i number := 0;
431
432 l_valid boolean;
433 l_doCheck boolean;
434 L_WORK_Q_ENABLE_PROFILE_OPTION IEU_UWQ_SEL_ENUMERATORS.WORK_Q_ENABLE_PROFILE_OPTION%TYPE;
435 l_profile_id NUMBER;
436
437 BEGIN
438
439 --
440 -- We will determine the eligible media types based on the RESOURCE_ID.
441 --
442
443
444 --
445 -- Don't have any admin to this currently, so will select based on the
446 -- servers in the server group the agent is assigned to. Another way to
447 -- implement this is to have more param/values associated with the
448 -- resource that are y/n flags (i.e., Inbound Telephony Enabled, etc).
449 --
450
451 -- x_plugins := EligibleMediaList();
452
453 FOR c_rec IN c_types LOOP
454
455 l_valid := TRUE;
456 l_doCheck := TRUE;
457
458 BEGIN
459 SELECT WORK_Q_ENABLE_PROFILE_OPTION
460 INTO L_WORK_Q_ENABLE_PROFILE_OPTION
461 FROM IEU_UWQ_SEL_ENUMERATORS
462 WHERE media_type_id = c_rec.MEDIA_TYPE_ID
463 AND NVL(not_valid_flag, 'N') = 'N';
464 EXCEPTION
465 WHEN NO_DATA_FOUND THEN
466 -- 07/24/02: NOW Assume there must be a valid profile option defined!
467 l_doCheck := FALSE;
468 l_valid := FALSE;
469 END;
470
471 -- 07/24/02: Now assume we must have a valid profile option
472 IF (l_doCheck = TRUE)
473 THEN
474 -- TODO: find a better way to determine a valid profile option:
475 BEGIN
476 SELECT
477 PROFILE_OPTION_ID
478 INTO
479 l_profile_id
480 FROM
481 FND_PROFILE_OPTIONS
482 WHERE
483 PROFILE_OPTION_NAME = L_WORK_Q_ENABLE_PROFILE_OPTION;
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN
486 l_doCheck := FALSE;
487 l_valid := FALSE;
488 END;
489
490 -- Assume a profile value of Y or NULL is valid!
491 IF (FND_PROFILE.VALUE(L_WORK_Q_ENABLE_PROFILE_OPTION) = 'N'
492 AND l_doCheck = TRUE)
493 THEN
494 l_valid := FALSE;
495 END IF;
496 END IF;
497
498 IF (l_valid = TRUE)
499 THEN
500 -- x_plugins.EXTEND;
501 -- x_plugins(x_plugins.LAST).MEDIA_TYPE_ID := c_rec.MEDIA_TYPE_ID;
502 -- x_plugins.EXTEND;
503 -- x_plugins(x_plugins.LAST).MEDIA_TYPE_UUID := c_rec.MEDIA_TYPE_UUID;
504
505 i := i + 1;
506
507 x_plugins(i).MEDIA_TYPE_ID := c_rec.MEDIA_TYPE_ID;
508 x_plugins(i).MEDIA_TYPE_UUID := c_rec.MEDIA_TYPE_UUID;
509
510 END IF;
511
512 END LOOP;
513
514
515 EXCEPTION
516 WHEN OTHERS THEN
517 NULL;
518
519 END DETERMINE_ELIGIBLE_MEDIA_TYPES;
520
521 /* Used to determine if a particular media is eligible */
522 FUNCTION IS_MEDIA_TYPE_ELIGIBLE
523 (P_RESOURCE_ID IN NUMBER
524 ,P_MEDIA_TYPE_UUID IN VARCHAR2
525 ) RETURN VARCHAR2
526 AS
527
528 l_media_types EligibleMediaList;
529
530 BEGIN
531
532
533 -- NOTE: This implementation really bothers me because it's slow... I
534 -- originally had this doing the specific select required.
535 -- However, with the addition of the User Profile options to turn
536 -- off specific Queues, I don't want to spread that logic out.
537 -- Therefore, I'm just using the DETERMINE, and looking for the
538 -- one we're interested in. Slower, but a bit more maintainable.
539
540
541 DETERMINE_ELIGIBLE_MEDIA_TYPES(
542 P_RESOURCE_ID,
543 l_media_types );
544
545
546 IF (l_media_types is null OR l_media_types.COUNT <= 0) THEN
547 RETURN 'N';
548 END IF;
549
550
551 FOR i IN l_media_types.FIRST..l_media_types.LAST LOOP
552
553 IF (l_media_types(i).media_type_uuid = P_MEDIA_TYPE_UUID) THEN
554 RETURN 'Y';
555 END IF;
556
557 END LOOP;
558
559
560 RETURN 'N';
561
562
563 EXCEPTION
564 WHEN NO_DATA_FOUND THEN
565 return 'N';
566
567 WHEN OTHERS THEN
568 return 'N';
569
570 END IS_MEDIA_TYPE_ELIGIBLE;
571
572 /* Used to determine if a particular media is eligible
573 FUNCTION IS_MEDIA_TYPE_ELIGIBLE
574 (P_RESOURCE_ID IN NUMBER
575 ,P_MEDIA_TYPE_UUID IN VARCHAR2
576 ) RETURN BOOLEAN
577 AS
578
579 l_media_types EligibleMediaList;
580
581 BEGIN
582
583
584 -- NOTE: This implementation really bothers me because it's slow... I
585 -- originally had this doing the specific select required.
586 -- However, with the addition of the User Profile options to turn
587 -- off specific Queues, I don't want to spread that logic out.
588 -- Therefore, I'm just using the DETERMINE, and looking for the
589 -- one we're interested in. Slower, but a bit more maintainable.
590
591
592 DETERMINE_ELIGIBLE_MEDIA_TYPES(
593 P_RESOURCE_ID,
594 l_media_types );
595
596
597 IF (l_media_types is null OR l_media_types.COUNT <= 0) THEN
598 RETURN FALSE;
599 END IF;
600
601
602 FOR i IN l_media_types.FIRST..l_media_types.LAST LOOP
603
604 IF (l_media_types(i).media_type_uuid = P_MEDIA_TYPE_UUID) THEN
605 RETURN TRUE;
606 END IF;
607
608 END LOOP;
609
610
611 RETURN FALSE;
612
613
614 EXCEPTION
615 WHEN NO_DATA_FOUND THEN
616 return FALSE;
617
618 WHEN OTHERS THEN
619 return FALSE;
620
621
622 END IS_MEDIA_TYPE_ELIGIBLE;
623 */
624
625 /* Used to determine if a particular media is eligible */
626 FUNCTION IS_MEDIA_TYPE_ELIGIBLE
627 (P_RESOURCE_ID IN NUMBER
628 ,P_MEDIA_TYPE_ID IN NUMBER
629 ) RETURN BOOLEAN
630 AS
631
632 l_media_types EligibleMediaList;
633
634 BEGIN
635
636
637 -- NOTE: This implementation really bothers me because it's slow... I
638 -- originally had this doing the specific select required.
639 -- However, with the addition of the User Profile options to turn
640 -- off specific Queues, I don't want to spread that logic out.
641 -- Therefore, I'm just using the DETERMINE, and looking for the
642 -- one we're interested in. Slower, but a bit more maintainable.
643
644
645 DETERMINE_ELIGIBLE_MEDIA_TYPES(
646 P_RESOURCE_ID,
647 l_media_types );
648
649
650 IF (l_media_types is null OR l_media_types.COUNT <= 0) THEN
651 RETURN FALSE;
652 END IF;
653
654
655 FOR i IN l_media_types.FIRST..l_media_types.LAST LOOP
656
657 -- dbms_output.put_line('l_media_types(i).media_type_id '||l_media_types(i).media_type_id||' P_MEDIA_TYPE_ID: '||P_MEDIA_TYPE_ID);
658 IF (l_media_types(i).media_type_id = P_MEDIA_TYPE_ID) THEN
659 RETURN TRUE;
660 END IF;
661
662 END LOOP;
663
664
665 RETURN FALSE;
666
667
668 EXCEPTION
669 WHEN NO_DATA_FOUND THEN
670 return FALSE;
671
672 WHEN OTHERS THEN
673 return FALSE;
674
675 END IS_MEDIA_TYPE_ELIGIBLE;
676
677
678 /* Used to build nodes table for Forms tree view. */
679 PROCEDURE ENUMERATE_WORK_NODES
680 (P_RESOURCE_ID IN NUMBER
681 ,P_LANGUAGE IN VARCHAR2
682 ,P_SOURCE_LANG IN VARCHAR2
683 )
684 AS
685
686 l_savepoint_valid NUMBER(1):=0;
687
688 l_media_count PLS_INTEGER;
689 l_node_label VARCHAR2(80);
690 l_wb_style VARCHAR2(2);
691 l_media_eligible VARCHAR2(5) := null;
692
693 CURSOR c_enum IS
694 SELECT
695 e.SEL_ENUM_ID sel_enum_id,
696 e.ENUM_PROC enum_proc,
697 nvl(IEU_UWQ_UTIL_PUB.to_number_noerr(fnd_profile.value(e.work_q_order_profile_option)), e.work_q_order_system_default) display_order,
698 e.work_q_register_type,
699 e.media_type_id
700 FROM
701 IEU_UWQ_SEL_ENUMERATORS e
702 WHERE EXISTS (select 'x' from FND_PROFILE_OPTIONS b
703 where b.PROFILE_OPTION_NAME = upper(e.work_q_enable_profile_option)
704 and (b.end_date_active is null -- Niraj, bug 4738501, Added
705 or trunc(b.end_date_active) > trunc(sysdate))) -- Niraj, Bug 5031721, Added
706 AND ((e.NOT_VALID_FLAG is NULL) OR (e.NOT_VALID_FLAG = 'N')) AND
707 (nvl(fnd_profile.value(e.work_q_enable_profile_option),'Y') = 'Y')
708 ORDER BY
709 display_order;
710
711 BEGIN
712
713 UPDATE IEU_UWQ_SEL_RT_NODES
714 SET not_valid = 'Y'
715 WHERE resource_id = P_RESOURCE_ID;
716
717 UPDATE IEU_UWQ_RTNODE_BIND_VALS
718 SET not_valid_flag = 'Y'
719 WHERE resource_id = P_RESOURCE_ID;
720
721 --
722 -- We simply call the enumeration procedures for each media type and pass
723 -- the resource id and media type uuid. The repetition of the uuid is in
724 -- case we want to have some procedures that are capable of doing multiple
725 -- media types. The procedure can simply check the UUID if needed.
726 --
727
728 l_wb_style := ieu_pvt.determine_wb_style( p_resource_id );
729
730 FOR cur_rec IN c_enum LOOP
731
732 BEGIN
733
734 l_media_eligible := null;
735
736 if ( ( (l_wb_style = 'F') or (l_wb_style = 'SF') )
737 and (cur_rec.work_q_register_type = 'M') ) -- Full/Simple Forced Blending
738 then
739
740 IEU_DEFAULT_MEDIA_ENUMS_PVT.create_blended_node( p_resource_id, p_language, p_source_lang );
741
742 else
743
744 if ( ( (l_wb_style = 'O') or (l_wb_style = 'SO') )
745 and (cur_rec.work_q_register_type = 'M') ) -- Full/Simple Optional Blending
746 then
747
748 IEU_DEFAULT_MEDIA_ENUMS_PVT.create_blended_node( p_resource_id, p_language, p_source_lang );
749
750 end if;
751 --
752 -- Note that P_RESOURCE_ID is not escaped because it is a number, whereas
753 -- the MEDIA_TYPE_UUID is a string, so must be in single quotes.
754 --
755
756 -- Here we are excluding Inbound and Acquired email as these will not have servers now.
757 IF ( not( (cur_rec.media_type_id = 10001) or (cur_rec.media_type_id = 10008) ))
758 THEN
759
760 IF ((cur_rec.work_q_register_type = 'M') and (cur_rec.media_type_id is not NULL))
761 THEN
762
763 IF (IEU_PVT.IS_MEDIA_TYPE_ELIGIBLE
764 (P_RESOURCE_ID ,cur_rec.media_type_id) = FALSE)
765 THEN
766 l_media_eligible := 'FALSE';
767 ELSE
768 l_media_eligible := 'TRUE';
769 END IF;
770
771 END IF;
772 END IF;
773
774 -- dbms_output.put_line('l_media_eligible : '||l_media_eligible||' enum proc : '||cur_rec.enum_proc);
775 IF ( (l_media_eligible is null) or (l_media_eligible = 'TRUE') )
776 THEN
777
778 EXECUTE IMMEDIATE
779 'begin ' || cur_rec.ENUM_PROC ||
780 '( ' ||
781 'p_resource_id => :1, ' ||
782 'p_language => :2, ' ||
783 'p_source_lang => :3, ' ||
784 'p_sel_enum_id => :4 ' ||
785 '); end;'
786 USING
787 IN P_RESOURCE_ID,
788 IN P_LANGUAGE,
789 IN P_SOURCE_LANG,
790 IN cur_rec.SEL_ENUM_ID;
791
792 -- dbms_output.put_line('l_media_eligible : '||l_media_eligible||' enum proc : '||cur_rec.enum_proc);
793
794 -- if we don't commit every time, the entire transaction will be rolled
795 -- back if one of the enumerators does a rollback... including any
796 -- previous enumerators progress!!
797 -- Ray Cardillo 06-24-2000
798 -- sjm 09/01/00 for efficiency don't do a commit on ea. iteration
799 --COMMIT;
800 -- Instead we'll roll back to the last successful upon exception
801 SAVEPOINT last_enum_success;
802
803 -- if we got here, the savepoint has been executed
804 l_savepoint_valid := 1;
805
806 END IF;
807
808 end if;
809
810 EXCEPTION
811 WHEN OTHERS THEN
812 -- Adding this condition will prevent an error if the exception
813 -- was caused by the first record
814
815 if (l_savepoint_valid = 1) then
816 ROLLBACK TO last_enum_success;
817 end if;
818 END;
819
820
821 END LOOP;
822
823
824 -- Removed call to CREATE_MYWORK_NODE because we're now putting that
825 -- node in as an enumerated entity of it's own.
826 --
827 -- R.Cardillo 02/07/01
828
829
830 --
831 -- After all nodes have enumerated, we'll see if we need to add the
832 -- special "Media" node or not. This directly corresponds to the new
833 -- logic in ADD_UWQ_NODE_DATA that forces the root node to "Media" if
834 -- MEDIA_TYPE_ID is valid. (Ray Cardillo / 05-22-01)
835 --
836 begin
837 select
838 rownum
839 into
840 l_media_count
841 from
842 IEU_UWQ_SEL_RT_NODES
843 where
844 (resource_id = p_resource_id) and
845 (not_valid = 'N') and
846 (media_type_id IS NOT NULL) and
847 (rownum = 1);
848 exception
849 when others then
850 l_media_count := 0;
851 end;
852
853
854 if (l_media_count >= 1)
855 then
856
857 Select
858 meaning
859 into
860 l_node_label
861 from
862 fnd_lookup_values_vl
863 where
864 (lookup_type = 'IEU_NODE_LABELS') and
865 (view_application_id = 696) and
866 (lookup_code = 'IEU_MEDIA_LBL');
867
868 IEU_UWQ_SEL_RT_NODES_PKG.LOAD_ROW (
869 X_RESOURCE_ID => p_resource_id,
870 X_SEL_ENUM_ID => 0,
871 X_NODE_ID => IEU_CONSTS_PUB.G_SNID_MEDIA,
872 X_NODE_TYPE => 0,
873 X_NODE_PID => 0,
874 X_NODE_WEIGHT => nvl(IEU_UWQ_UTIL_PUB.to_number_noerr(fnd_profile.value('IEU_QOR_MEDIA')) , IEU_CONSTS_PUB.G_SNID_MEDIA),
875 X_NODE_DEPTH => 1,
876 X_SEL_ENUM_PID => 0,
877 X_MEDIA_TYPE_ID => NULL,
878 X_COUNT => 0,
879 X_DATA_SOURCE => 'IEU_UWQ_MEDIA_DS',
880 X_VIEW_NAME => 'IEU_UWQ_MEDIA_V',
881 X_WHERE_CLAUSE => '',
882 X_HIDE_IF_EMPTY => NULL,
883 X_NOT_VALID => 'N',
884 X_NODE_LABEL => l_node_label,
885 X_REFRESH_VIEW_NAME => 'IEU_UWQ_MEDIA_V',
886 X_RES_CAT_ENUM_FLAG => NULL,
887 X_REFRESH_VIEW_SUM_COL => 'QUEUE_COUNT'
888 );
889
890 end if;
891
892
893 COMMIT;
894
895 EXCEPTION
896 WHEN OTHERS THEN
897 -- Adding this condition will prevent an error if the exception
898 -- was caused in the update statements
899 ROLLBACK WORK;
900
901 END ENUMERATE_WORK_NODES;
902
903
904 /* Used to refresh nodes table for Forms tree view. */
905 PROCEDURE REFRESH_WORK_NODE_COUNTS( P_RESOURCE_ID IN NUMBER )
906 AS
907
908 l_count NUMBER;
909 l_where_clause VARCHAR2(30000);
910 l_refresh_view_name varchar2(200);
911 l_refresh_view_sum_col varchar2(200);
912 l_sel_rt_node_id number;
913 l_node_id number(10);
914 l_node_pid number(10);
915 l_sel_enum_id number(15);
916 l_res_cat_enum_flag varchar2(1);
917 l_view_name varchar2(512);
918 l_media_type_id number;
919
920 l_tsk_count NUMBER;
921 l_tsk_where_clause VARCHAR2(30000);
922 l_tsk_refresh_view_name varchar2(200);
923 l_tsk_refresh_view_sum_col varchar2(200);
924 l_tsk_sel_rt_node_id number;
925 l_tsk_node_id number(10);
926 l_tsk_node_pid number(10);
927 l_tsk_sel_enum_id number(15);
928 l_tsk_res_cat_enum_flag varchar2(1);
929 l_tsk_view_name varchar2(512);
930 l_tsk_media_type_id number;
931 l_bindvallist BindValList;
932 i number := 1;
933
934 j NUMBER;
935 l_bulk_count NUMBER;
936
937 CURSOR c_nodes IS
938 SELECT
939 rt_nodes.sel_rt_node_id,
940 rt_nodes.node_id,
941 rt_nodes.node_pid,
942 rt_nodes.view_name,
943 rt_nodes.where_clause,
944 rt_nodes.media_type_id,
945 rt_nodes.sel_enum_id,
946 rt_nodes.refresh_view_name,
947 rt_nodes.refresh_view_sum_col,
948 rt_nodes.res_cat_enum_flag,
949 rt_nodes.node_depth
950 FROM
951
952 ieu_uwq_sel_rt_nodes rt_nodes
953 WHERE
954 (rt_nodes.resource_id = p_resource_id) AND
955 (rt_nodes.node_id > 0) AND
956 /* (rt_nodes.node_id <> IEU_CONSTS_PUB.G_SNID_MEDIA) and */
957 (rt_nodes.not_valid = 'N');
958
959 /*
960 CURSOR c_media_nodes IS
961 SELECT
962 rt_nodes.sel_rt_node_id,
963 rt_nodes.node_id,
964 rt_nodes.node_pid,
965 rt_nodes.where_clause,
966 rt_nodes.sel_enum_id,
967 rt_nodes.refresh_view_name,
968 rt_nodes.refresh_view_sum_col,
969 rt_nodes.res_cat_enum_flag,
970 rt_nodes.view_name
971 FROM
972 ieu_uwq_sel_rt_nodes rt_nodes
973 WHERE
974 (rt_nodes.resource_id = p_resource_id) AND
975 (rt_nodes.node_id = IEU_CONSTS_PUB.G_SNID_MEDIA) and
976 (rt_nodes.not_valid = 'N');
977 */
978
979 CURSOR c_bindVal IS
980 SELECT
981 rt_nodes_bind_val.SEL_RT_NODE_ID,
982 rt_nodes_bind_val.node_id,
983 rt_nodes_bind_val.BIND_VAR_NAME,
984 rt_nodes_bind_val.bind_var_value
985 FROM
986 ieu_uwq_rtnode_bind_vals rt_nodes_bind_val
987 WHERE
988 (rt_nodes_bind_val.resource_id = p_resource_id) AND
989 (rt_nodes_bind_val.node_id > 0) AND
990 (rt_nodes_bind_val.not_valid_flag = 'N');
991
992
993 BEGIN
994
995 j := 0;
996 IF IEU_PVT.SEL_RT_NODE_ID_REF_LIST.FIRST IS NOT NULL THEN
997 IEU_PVT.SEL_RT_NODE_ID_REF_LIST.DELETE;
998 IEU_PVT.REF_COUNT_LIST.DELETE;
999 END IF;
1000
1001 For b in c_bindVal
1002 loop
1003 l_bindvallist(i).sel_rt_node_id := b.sel_rt_node_id;
1004 l_bindvallist(i).node_id := b.node_id;
1005 l_bindvallist(i).bind_var_name := b.bind_var_name;
1006 l_bindvallist(i).bind_var_value := b.bind_var_value;
1007
1008 i := i + 1;
1009
1010 end loop;
1011
1012 begin
1013 FOR node in c_nodes
1014 LOOP
1015
1016 l_count := 0;
1017
1018 if (node.node_id = IEU_CONSTS_PUB.G_SNID_MEDIA)
1019 then
1020
1021 l_sel_rt_node_id := node.sel_rt_node_id;
1022 l_node_id := node.node_id;
1023 l_node_pid := node.node_pid;
1024 l_view_name := node.view_name;
1025 l_where_clause := node.where_clause;
1026 l_media_type_id := node.media_type_id;
1027 l_sel_enum_id := node.sel_enum_id;
1028 l_refresh_view_name := node.refresh_view_name;
1029 l_refresh_view_sum_col := node.refresh_view_sum_col;
1030 l_res_cat_enum_flag := node.res_cat_enum_flag;
1031
1032 elsif (node.sel_enum_id = 10054 and node.node_depth = 1)
1033 then
1034
1035 l_tsk_sel_rt_node_id := node.sel_rt_node_id;
1036 l_tsk_node_id := node.node_id;
1037 l_tsk_node_pid := node.node_pid;
1038 l_tsk_view_name := node.view_name;
1039 l_tsk_where_clause := node.where_clause;
1040 l_tsk_media_type_id := node.media_type_id;
1041 l_tsk_sel_enum_id := node.sel_enum_id;
1042 l_tsk_refresh_view_name := node.refresh_view_name;
1043 l_tsk_refresh_view_sum_col := node.refresh_view_sum_col;
1044 l_tsk_res_cat_enum_flag := node.res_cat_enum_flag;
1045
1046 else
1047 l_bulk_count := '';
1048 refresh_node(node.node_id, node.node_pid, node.sel_enum_id, node.where_clause,
1049 node.res_cat_enum_flag, node.refresh_view_name, node.refresh_view_sum_col,
1050 node.sel_rt_node_id, l_count, p_resource_id, node.view_name,l_bindvallist, l_bulk_count );
1051
1052 IEU_PVT.SEL_RT_NODE_ID_REF_LIST(j) := node.sel_rt_node_id;
1053 IEU_PVT.REF_COUNT_LIST(j) := l_bulk_count;
1054 j := j + 1;
1055 end if;
1056
1057 END LOOP;
1058
1059 BEGIN
1060 IF IEU_PVT.SEL_RT_NODE_ID_REF_LIST.FIRST IS NOT NULL THEN
1061 FORALL x IN IEU_PVT.SEL_RT_NODE_ID_REF_LIST.FIRST..IEU_PVT.SEL_RT_NODE_ID_REF_LIST.LAST SAVE EXCEPTIONS
1062 UPDATE IEU_UWQ_SEL_RT_NODES
1063 SET COUNT = IEU_PVT.REF_COUNT_LIST(x)
1064 WHERE SEL_RT_NODE_ID = IEU_PVT.SEL_RT_NODE_ID_REF_LIST(x)
1065 AND RESOURCE_ID = P_RESOURCE_ID;
1066 COMMIT;
1067
1068 IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
1069 IEU_PVT.REF_COUNT_LIST.delete;
1070 END IF;
1071
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
1075 IEU_PVT.REF_COUNT_LIST.delete;
1076 END;
1077
1078 end;
1079
1080 if (l_node_id is not null) and (l_node_id = IEU_CONSTS_PUB.G_SNID_MEDIA)
1081 then
1082 l_count := 0;
1083 l_bulk_count := '';
1084
1085 refresh_node(l_node_id, l_node_pid, l_sel_enum_id, l_where_clause,
1086 l_res_cat_enum_flag, l_refresh_view_name, l_refresh_view_sum_col,
1087 l_sel_rt_node_id, l_count, p_resource_id,l_view_name, l_bindvallist, l_bulk_count);
1088
1089 BEGIN
1090 UPDATE IEU_UWQ_SEL_RT_NODES
1091 SET COUNT = l_bulk_count
1092 WHERE SEL_RT_NODE_ID = l_sel_rt_node_id
1093 AND RESOURCE_ID = P_RESOURCE_ID;
1094 COMMIT;
1095
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 NULL;
1099 END;
1100 end if;
1101
1102 if (l_tsk_node_id is not null)
1103 then
1104 l_tsk_count := 0;
1105 l_bulk_count := '';
1106
1107 refresh_node(l_tsk_node_id, l_tsk_node_pid, l_tsk_sel_enum_id, l_tsk_where_clause,
1108 l_tsk_res_cat_enum_flag, l_tsk_refresh_view_name, l_tsk_refresh_view_sum_col,
1109 l_tsk_sel_rt_node_id, l_tsk_count, p_resource_id,l_tsk_view_name, l_bindvallist, l_bulk_count);
1110
1111 BEGIN
1112 UPDATE IEU_UWQ_SEL_RT_NODES
1113 SET COUNT = l_bulk_count
1114 WHERE SEL_RT_NODE_ID = l_tsk_sel_rt_node_id
1115 AND RESOURCE_ID = P_RESOURCE_ID;
1116 COMMIT;
1117
1118 EXCEPTION
1119 WHEN OTHERS THEN
1120 NULL;
1121 END;
1122 end if;
1123
1124
1125 /*
1126 begin
1127 open c_media_nodes;
1128
1129 fetch c_media_nodes
1130 into l_sel_rt_node_id,l_node_id, l_node_pid, l_where_clause, l_sel_enum_id,
1131 l_refresh_view_name,l_refresh_view_sum_col, l_res_cat_enum_flag, l_view_name;
1132
1133 if c_media_nodes%NOTFOUND then
1134 null;
1135 else
1136 l_count := 0;
1137
1138 refresh_node(l_node_id, l_node_pid, l_sel_enum_id, l_where_clause,
1139 l_res_cat_enum_flag, l_refresh_view_name, l_refresh_view_sum_col,
1140 l_sel_rt_node_id, l_count, p_resource_id,l_view_name);
1141
1142 end if;
1143 END;
1144 */
1145 commit;
1146
1147 END REFRESH_WORK_NODE_COUNTS;
1148
1149 PROCEDURE REFRESH_NODE(
1150 p_node_id in number,
1151 p_node_pid in number,
1152 p_sel_enum_id in number,
1153 p_where_clause in varchar2,
1154 p_res_cat_enum_flag in varchar2,
1155 p_refresh_view_name in varchar2,
1156 p_refresh_view_sum_col in varchar2,
1157 p_sel_rt_node_id in number,
1158 p_count in number,
1159 p_resource_id in number,
1160 p_view_name in varchar2,
1161 p_bindvallist in BindValList,
1162 x_count out NOCOPY number) AS
1163
1164 l_count NUMBER;
1165 l_refresh_proc VARCHAR2(100);
1166 l_where_clause VARCHAR2(30000);
1167 l_res_cat_where_clause VARCHAR2(30000);
1168 l_sql_stmt VARCHAR2(30000);
1169 l_cursor_name INTEGER;
1170 l_rows_processed INTEGER;
1171 l_rtnode_bind_var_flag Varchar2(50);
1172 l_enum_bind_var_flag Varchar2(50);
1173 l_resource_id_flag Varchar2(10);
1174 l_node_count number;
1175 l_param_pk_value varchar2(500);
1176 l_media_sql_stmt varchar2(10000);
1177
1178 BEGIN
1179
1180 l_rtnode_bind_var_flag := 'T';
1181 l_enum_bind_var_flag := '';
1182 l_resource_id_flag := '';
1183 --
1184 -- I don't really like the way this turned out... we need some more
1185 -- indicator columns so this logic can truly be a "blind loop" doing
1186 -- the same processing for every node. Unfortunately, we can't meet
1187 -- our requirements if we do that right now, and the final code pull
1188 -- is upon us... so this will have to do for now...
1189 --
1190 -- (Ray Cardillo / 05-08-00)
1191 --
1192
1193
1194 if ( (p_node_id = IEU_CONSTS_PUB.G_SNID_MEDIA) or
1195 (p_node_id = IEU_CONSTS_PUB.G_SNID_BLENDED) )
1196 then
1197 begin
1198 select
1199 where_clause
1200 into
1201 l_res_cat_where_clause
1202 from
1203 ieu_uwq_res_cats_b
1204 where
1205 res_cat_id = 10001;
1206
1207 exception
1208 when no_data_found then
1209 null;
1210 end;
1211 else
1212 l_res_cat_where_clause := ieu_pub.get_enum_res_cat(p_sel_enum_id);
1213 end if;
1214
1215 if (p_where_clause is NULL)
1216 then
1217 l_where_clause := l_res_cat_where_clause;
1218 l_rtnode_bind_var_flag := 'F';
1219 else
1220
1221 if (p_res_cat_enum_flag = 'Y') OR (p_res_cat_enum_flag is NULL)
1222 then
1223 if (l_res_cat_where_clause) is not null
1224 then
1225 l_where_clause :=
1226 l_res_cat_where_clause || ' and ' || p_where_clause;
1227 --l_rtnode_bind_var_flag := 'F';
1228 l_rtnode_bind_var_flag := 'T';
1229 end if;
1230 else
1231 l_where_clause := p_where_clause;
1232 l_rtnode_bind_var_flag := 'T';
1233 end if;
1234 end if;
1235
1236
1237 if (l_res_cat_where_clause is not null)
1238 then
1239 select
1240 decode(
1241 (instr(l_res_cat_where_clause, ':resource_id', 1, 1)), 0, 'F','T' )
1242 into
1243 l_enum_bind_var_flag
1244 from
1245 dual;
1246 else
1247 l_enum_bind_var_flag := 'F';
1248 end if;
1249
1250
1251 BEGIN
1252
1253
1254 -- Use sel_enum_id to find which proc to call
1255 BEGIN
1256 select
1257 refresh_proc
1258 into
1259 l_refresh_proc
1260 from
1261 ieu_uwq_sel_enumerators
1262 where
1263 sel_enum_id = p_sel_enum_id;
1264 EXCEPTION
1265 WHEN NO_DATA_FOUND THEN
1266 NULL;
1267
1268 END;
1269
1270 --
1271 -- If Refresh Proc is present then get the count from refresh proc
1272 -- otherwise, get count from the refresh view, or default query.
1273 --
1274 IF (l_refresh_proc IS not NULL)
1275 THEN
1276
1277 -- If any refresh proc produces an error, then ignore it.
1278 -- (Ray Cardillo / 05-22-01)
1279
1280 BEGIN
1281
1282 execute immediate
1283 'begin '|| l_refresh_proc || '(' || ':P_RESOURCE_ID' ||
1284 ', '|| ':p_node_id ' || ',:l_count);end;'
1285 using
1286 in p_resource_id, in p_node_id, out l_count;
1287 EXCEPTION
1288 when others then
1289 null;
1290 END;
1291
1292 ELSE
1293
1294 --
1295
1296 -- if we have a refresh view then get the count from the refresh
1297 -- view else from the base view .
1298 --
1299 IF (p_refresh_view_name IS NOT NULL)
1300 THEN
1301
1302 --
1303 -- If there is a SUM column specified for the node, then use that
1304 -- to perform a sum on the specified column in refresh view.
1305 --
1306 IF (p_REFRESH_VIEW_SUM_COL IS NOT NULL)
1307 THEN
1308
1309
1310 --
1311 -- special processing for SUM count logic (i.e., Media nodes)
1312 --
1313 l_sql_stmt :=
1314 'Select sum(' || p_REFRESH_VIEW_SUM_COL || ') from ' ||
1315 p_REFRESH_view_name || ' where ' || l_where_clause ||
1316 ' and ieu_param_pk_value is not null';
1317
1318 /* this following codes added because in passive mode there is no classification but 'ANY'
1319 so, the above select would not work for 'ANY' */
1320
1321 if p_node_pid = 4000 then
1322 l_media_sql_stmt :=
1323 'begin select count(*) into :l_node_count from '||
1324 p_refresh_view_name||' where resource_id = '||':p_resource_id'||'; end;';
1325
1326 EXECUTE IMMEDIATE l_media_sql_stmt
1327 USING out l_node_count, in p_resource_id;
1328
1329 if l_node_count = 1 then
1330 l_media_sql_stmt :=
1331 'begin select ieu_param_pk_value into :l_param_pk_value from '||
1332 p_refresh_view_name||' where resource_id = '||':p_resource_id'||'; end;';
1333
1334 EXECUTE IMMEDIATE l_media_sql_stmt
1335 USING out l_param_pk_value, in p_resource_id;
1336
1337 if l_param_pk_value is null then
1338 l_sql_stmt :=
1339 'Select sum(' || p_REFRESH_VIEW_SUM_COL || ') from ' ||
1340 p_REFRESH_view_name || ' where ' || l_where_clause;
1341 end if;
1342 end if;
1343
1344 end if;
1345
1346 ELSE
1347 -- Begin fix by spamujul for Bug 7024226
1348 If p_refresh_view_name = 'IEU_UWQ_TASK_GA_REF_V v' then
1349 l_sql_stmt := 'select /*+ index(v.tasks JTF_TASKS_B_U1) */ count(resource_id) from ' || p_refresh_view_name || ' where ' || l_where_clause;
1350 else
1351 -- End fix by spamujul for Bug 7024226
1352 l_sql_stmt :=
1353 'select count(resource_id) from ' || p_refresh_view_name ||
1354 ' where ' || l_where_clause;
1355 end if ;-- Added by spamujul for Bug 7024226
1356
1357
1358 END IF;
1359
1360 ELSE
1361
1362 --
1363 -- we'll have to collect the count on our own... usually slower...
1364 --
1365 l_sql_stmt :=
1366 'select count(resource_id) from ' || p_view_name ||
1367 ' where ' || l_where_clause;
1368
1369 END IF;
1370
1371 --
1372 -- Execute the sql_stmt to get the count
1373 --
1374
1375 BEGIN
1376 l_cursor_name := dbms_sql.open_cursor;
1377 DBMS_SQL.PARSE(l_cursor_name,l_sql_stmt , dbms_sql.native);
1378
1379 If (l_rtnode_bind_var_flag = 'T')
1380 then
1381
1382 -- Check if resource_id is present.
1383 if (l_where_clause is not null)
1384 then
1385 select
1386 decode((instr(l_where_clause, ':resource_id', 1, 1)), 0, 'F','T' )
1387 into
1388 l_resource_id_flag
1389 from
1390 dual;
1391 else
1392 l_resource_id_flag := 'F';
1393 end if;
1394
1395 if (l_resource_id_flag = 'T')
1396 then
1397 DBMS_SQL.BIND_VARIABLE (
1398 l_cursor_name,
1399 ':resource_id',
1400 p_resource_id );
1401 end if;
1402
1403 for i in p_bindvallist.first..p_bindvallist.last
1404 loop
1405
1406 if ( (p_bindvallist(i).sel_rt_node_id = p_sel_rt_node_id) and
1407 (p_bindvallist(i).node_id = p_node_id) )
1408 then
1409
1410 -- Ignore bind Var :resource_id here.
1411 If (p_bindvallist(i).bind_var_name <> ':resource_id')
1412 then
1413
1414 DBMS_SQL.BIND_VARIABLE (
1415 l_cursor_name,
1416 p_bindvallist(i).bind_var_name,
1417 p_bindvallist(i).bind_var_value );
1418 end if;
1419
1420 end if;
1421
1422 end loop;
1423
1424
1425 else
1426
1427 if (l_enum_bind_var_flag = 'T')
1428 then
1429 DBMS_SQL.BIND_VARIABLE (
1430 l_cursor_name,
1431 ':resource_id',
1432 p_resource_id );
1433 end if;
1434
1435 end if;
1436
1437
1438 --DBMS_SQL.BIND_VARIABLE(l_cursor_name, ':resource_id', 3807);
1439 DBMS_SQL.DEFINE_COLUMN(l_cursor_name, 1, l_count);
1440 l_rows_processed := dbms_sql.execute(l_cursor_name);
1441
1442 IF (DBMS_SQL.FETCH_ROWS(l_cursor_name) > 0)
1443 THEN
1444 -- get column values of the row
1445 DBMS_SQL.COLUMN_VALUE(l_cursor_name, 1, l_count);
1446 END IF;
1447
1448 DBMS_SQL.close_cursor(l_cursor_name);
1449
1450 EXCEPTION
1451
1452 WHEN OTHERS THEN
1453 DBMS_SQL.CLOSE_CURSOR(l_cursor_name);
1454
1455 END;
1456
1457 END IF;
1458
1459 exception
1460 WHEN OTHERS THEN
1461 l_count := 0;
1462 --dbms_output.put_line(SQLCODE);
1463 --dbms_output.put_line(SQLERRM);
1464
1465
1466 end;
1467
1468 IF (l_count IS NULL)
1469 THEN
1470 l_count := 0;
1471 END IF;
1472
1473 --
1474 -- now update the count for the row
1475 --
1476 /* UPDATE
1477 IEU_UWQ_SEL_RT_NODES nodes
1478 SET
1479
1480 nodes.count = l_count
1481 WHERE
1482 (nodes.sel_rt_node_id = p_sel_rt_node_id) AND
1483 (nodes.resource_id = p_resource_id);
1484 */
1485 x_count := l_count;
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 -- nothing we can really do if this fails...
1489 NULL;
1490 --dbms_output.put_line('exception : '||substr(sqlerrm, 1, 50));
1491
1492 END refresh_node;
1493
1494
1495 /* Returns information needed to connect UWQ Client to a UWQ Server. */
1496 PROCEDURE UWQ_CLIENT_LOCATE_UWQ_SERVER
1497 (P_RESOURCE_ID IN NUMBER
1498 ,P_WIRE_PROTOCOL IN VARCHAR2
1499 ,P_COMP_DEF_NAME IN VARCHAR2
1500 ,P_COMP_DEF_VERSION IN NUMBER
1501 ,P_COMP_DEF_IMPL IN VARCHAR2
1502 ,P_COMP_NAME IN VARCHAR2
1503 ,X_COMP_NAME OUT NOCOPY VARCHAR2
1504 ,X_SVR_USER_ADDRESS OUT NOCOPY VARCHAR2
1505 ,X_SVR_IP_ADDRESS OUT NOCOPY VARCHAR2
1506 ,X_SVR_DNS_NAME OUT NOCOPY VARCHAR2
1507 ,X_SVR_PORT OUT NOCOPY NUMBER
1508 ,X_USE_PROXY OUT NOCOPY VARCHAR2
1509 ,X_SESSION_TIMEOUT OUT NOCOPY NUMBER
1510 ,X_SYNC_TIMEOUT OUT NOCOPY NUMBER
1511 ,X_RESPONSE_TIMEOUT OUT NOCOPY NUMBER
1512 ,X_RECONNECT_RETRY_DELAY OUT NOCOPY NUMBER
1513 ,X_HEART_RATE OUT NOCOPY NUMBER
1514 )
1515 AS
1516
1517 l_server_id IEO_SVR_SERVERS.SERVER_ID%TYPE;
1518 l_server_group_id IEO_SVR_GROUPS.SERVER_GROUP_ID%TYPE;
1519
1520 l_last_update_secs PLS_INTEGER;
1521 l_curr_time_secs PLS_INTEGER;
1522 l_threshold_secs PLS_INTEGER;
1523 l_use_refresh_time VARCHAR2(10);
1524
1525 BEGIN
1526
1527
1528 IF ( (P_RESOURCE_ID IS NULL) OR
1529 (P_WIRE_PROTOCOL IS NULL) OR
1530 (P_COMP_DEF_NAME IS NULL) OR
1531 (P_COMP_DEF_VERSION IS NULL) OR
1532 (P_COMP_DEF_IMPL IS NULL) )
1533 THEN
1534 raise_application_error
1535 (-20000
1536 ,'A required parameter is null' ||
1537 '. (P_RESOURCE_ID = ' || P_RESOURCE_ID ||
1538 ') (P_WIRE_PROTOCOL = ' || P_WIRE_PROTOCOL ||
1539 ') (P_COMP_DEF_NAME = ' || P_COMP_DEF_NAME ||
1540 ') (P_COMP_DEF_VERSION = ' || P_COMP_DEF_VERSION ||
1541 ') (P_COMP_DEF_IMPL = ' || P_COMP_DEF_IMPL ||
1542 ')'
1543 ,TRUE );
1544 END IF;
1545
1546
1547 --
1548 -- Here are the steps we need to perform:
1549 --
1550 -- 1. Find the server group this agent belongs to, based on RESOURCE_ID.
1551 --
1552 -- 2. See if a cached server connection is present.
1553 --
1554 -- 3. Locate least loaded UWQ server by group, if not cached.
1555 --
1556 -- 4. Select additional information, and return info to the client.
1557 --
1558
1559
1560 --
1561 -- 1. Given the RESOURCE_ID, find the server group that this agent belongs to.
1562 --
1563
1564 SELECT
1565 DISTINCT
1566 SERVER_GROUP_ID
1567 INTO
1568 l_server_group_id
1569 FROM
1570 JTF_RS_RESOURCE_EXTNS
1571 WHERE
1572 (RESOURCE_ID = P_RESOURCE_ID) AND
1573 (ROWNUM <= 1);
1574
1575 --
1576 -- 2. See if a cached server connection is present.
1577 --
1578
1579 BEGIN
1580
1581 --
1582 -- see if there is a cached entry
1583 --
1584 SELECT
1585 binds.SERVER_ID
1586 INTO
1587 l_server_id
1588 FROM
1589 IEU_UWQ_AGENT_BINDINGS binds,
1590 IEO_SVR_SERVERS srvrs,
1591 IEO_SVR_RT_INFO rti
1592 WHERE
1593 (binds.RESOURCE_ID = P_RESOURCE_ID) AND
1594 (binds.SERVER_ID = srvrs.SERVER_ID) AND
1595 (nvl(binds.NOT_VALID,'N') = 'N') AND
1596 (srvrs.TYPE_ID = IEU_CONSTS_PUB.G_STID_UWQ) AND
1597 (srvrs.SERVER_ID = rti.SERVER_ID) AND
1598 (nvl(rti.STATUS,0) > 0) AND
1599 (ROWNUM <= 1);
1600
1601 IF (l_server_id is null)
1602 THEN
1603 RAISE NO_DATA_FOUND;
1604 END IF;
1605
1606 --
1607 -- See what the last update time was for this server
1608 --
1609 SELECT
1610 DISTINCT
1611 to_number(to_char(rti.LAST_UPDATE_DATE,'SSSSS'))
1612 INTO
1613 l_last_update_secs
1614 FROM
1615 IEO_SVR_RT_INFO rti
1616 WHERE
1617 (rti.SERVER_ID = l_server_id) AND
1618 (ROWNUM <= 1);
1619
1620 --
1621 -- calculate the interval in seconds + a buffer
1622 --
1623 SELECT
1624 DISTINCT
1625 ((stype.RT_REFRESH_RATE * 60) + 60)
1626 INTO
1627 l_threshold_secs
1628 FROM
1629 IEO_SVR_SERVERS srvrs,
1630 IEO_SVR_TYPES_B stype
1631 WHERE
1632 (srvrs.SERVER_ID = l_server_id) AND
1633 (srvrs.TYPE_ID = stype.TYPE_ID) AND
1634 (ROWNUM <= 1);
1635
1636 l_curr_time_secs := to_number(to_char(SYSDATE,'SSSSS'));
1637
1638 --
1639 -- See if this server is "dead".
1640 --
1641 IF ( ABS(l_curr_time_secs - l_last_update_secs) > l_threshold_secs )
1642 THEN
1643 l_server_id := null;
1644 END IF;
1645
1646 EXCEPTION
1647 WHEN OTHERS THEN
1648 l_server_id := null;
1649 END;
1650
1651 --
1652 -- 3. Locate least loaded UWQ server by group.
1653 --
1654
1655 IF (l_server_id is null)
1656 THEN
1657
1658 BEGIN
1659 IEO_SVR_UTIL_PVT.LOCATE_LLS_AND_INFO_BY_GROUP
1660 (
1661 l_server_group_id, -- server group id
1662 'E59Bf3F0B7DF11D3A05000C04F53FBA6', -- server type uuid
1663 P_WIRE_PROTOCOL, -- wire protocol
1664 P_COMP_DEF_NAME, -- compenent definition name
1665 P_COMP_DEF_VERSION, -- version of component definition
1666 P_COMP_DEF_IMPL, -- implementation
1667 P_COMP_NAME, -- component name, or NULL
1668 l_server_id, -- server id found
1669 X_SVR_USER_ADDRESS, -- user connect address
1670 X_SVR_DNS_NAME, -- dns name
1671 X_SVR_IP_ADDRESS, -- ip address
1672 X_SVR_PORT, -- port to connect to
1673 X_COMP_NAME, -- component name
1674 60 -- rt timeout tolerance
1675 );
1676
1677 EXCEPTION
1678 WHEN OTHERS THEN
1679 IEO_SVR_UTIL_PVT.LOCATE_LLS_AND_INFO_BY_GROUP
1680 (
1681 l_server_group_id, -- server group id
1682 'E59Bf3F0B7DF11D3A05000C04F53FBA6', -- server type uuid
1683 P_WIRE_PROTOCOL, -- wire protocol
1684 P_COMP_DEF_NAME, -- compenent definition name
1685 P_COMP_DEF_VERSION, -- version of component definition
1686 P_COMP_DEF_IMPL, -- implementation
1687 P_COMP_NAME, -- component name, or NULL
1688 l_server_id, -- server id found
1689 X_SVR_USER_ADDRESS, -- user connect address
1690 X_SVR_DNS_NAME, -- dns name
1691 X_SVR_IP_ADDRESS, -- ip address
1692 X_SVR_PORT, -- port to connect to
1693 X_COMP_NAME, -- component name
1694 -1 -- rt timeout tolerance
1695 );
1696 END;
1697
1698 ELSE
1699
1700 --
1701 -- just slammed this code in from IEO for now... really need another
1702 -- helper in IEO... but don't have the time to validate right now.
1703 --
1704 -- Ray Cardillo (05-11-00)
1705 --
1706 IF (P_COMP_NAME IS NULL) THEN
1707
1708 SELECT
1709 DISTINCT
1710 comp_table.COMP_NAME
1711 INTO
1712 X_COMP_NAME
1713 FROM
1714 IEO_SVR_SERVERS svr_table,
1715 IEO_SVR_COMP_DEFS cdef_table,
1716 IEO_SVR_COMPS comp_table,
1717 IEO_SVR_PROTOCOL_MAP prot_table
1718 WHERE
1719 (svr_table.SERVER_ID = comp_table.SERVER_ID) AND
1720 (svr_table.SERVER_ID = l_server_id) AND
1721 (comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
1722 (prot_table.COMP_ID = comp_table.COMP_ID) AND
1723 (prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
1724 (cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
1725 (cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
1726 (cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
1727 (ROWNUM <= 1);
1728
1729 ELSE
1730
1731 X_COMP_NAME := P_COMP_NAME;
1732
1733 END IF;
1734
1735 SELECT
1736 DISTINCT
1737 svr_table.USER_ADDRESS,
1738 svr_table.DNS_NAME,
1739 svr_table.IP_ADDRESS,
1740 prot_table.PORT
1741 INTO
1742 X_SVR_USER_ADDRESS,
1743 X_SVR_DNS_NAME,
1744 X_SVR_IP_ADDRESS,
1745 X_SVR_PORT
1746 FROM
1747 IEO_SVR_SERVERS svr_table,
1748 IEO_SVR_COMP_DEFS cdef_table,
1749 IEO_SVR_COMPS comp_table,
1750 IEO_SVR_PROTOCOL_MAP prot_table
1751 WHERE
1752 (svr_table.SERVER_ID = l_server_id) AND
1753 (svr_table.SERVER_ID = comp_table.SERVER_ID) AND
1754 (comp_table.COMP_DEF_ID = cdef_table.COMP_DEF_ID) AND
1755 (prot_table.COMP_ID = comp_table.COMP_ID) AND
1756 (prot_table.WIRE_PROTOCOL = P_WIRE_PROTOCOL) AND
1757 (cdef_table.COMP_DEF_NAME = P_COMP_DEF_NAME) AND
1758 (cdef_table.COMP_DEF_VERSION = P_COMP_DEF_VERSION) AND
1759 (cdef_table.IMPLEMENTATION = P_COMP_DEF_IMPL) AND
1760 (comp_table.COMP_NAME = X_COMP_NAME) AND
1761 (ROWNUM <= 1);
1762
1763 END IF;
1764
1765
1766 --
1767 -- 4. Select additional information, and return info to the client.
1768 --
1769
1770 --
1771 -- For now, just hard-coding the return values... we'll have to make sure
1772 -- this is seeded in the server params table first, and then associate
1773 -- values, defaulting whatever is not found.
1774 --
1775 -- Note: l_server_id was collected above so we can use it to get
1776 -- variables that may be associated with the server.
1777 --
1778
1779 FND_PROFILE.GET( 'IEU_CLI_NET_USE_PROXY', X_USE_PROXY );
1780 IF (X_USE_PROXY <> 'Y')
1781 THEN
1782 X_USE_PROXY := '';
1783 END IF;
1784
1785 X_SESSION_TIMEOUT := 180;
1786 X_SYNC_TIMEOUT := 360000;
1787 X_RESPONSE_TIMEOUT := 360000;
1788 X_RECONNECT_RETRY_DELAY := 4;
1789 X_HEART_RATE := 9;
1790
1791
1792 EXCEPTION
1793 WHEN OTHERS THEN
1794 NULL; -- may need to do something in the future
1795 RAISE;
1796
1797
1798 END UWQ_CLIENT_LOCATE_UWQ_SERVER;
1799
1800
1801 /* Used by UWQ Server to set an agent binding to a server. */
1802 PROCEDURE BIND_AGENT( P_RESOURCE_ID IN NUMBER, P_SERVER_ID IN NUMBER ) AS
1803
1804 CURSOR c_binds(svr_type_id NUMBER) IS
1805 SELECT
1806 ab.SERVER_ID
1807 FROM
1808 IEU_UWQ_AGENT_BINDINGS ab,
1809 IEO_SVR_SERVERS srvrs
1810 WHERE
1811 ab.RESOURCE_ID = P_RESOURCE_ID AND
1812 ab.SERVER_ID = srvrs.SERVER_ID AND
1813 srvrs.TYPE_ID = svr_type_id;
1814
1815 l_svr_type_id NUMBER(15,0);
1816
1817 BEGIN
1818
1819 IF ((P_SERVER_ID IS NULL) OR (P_RESOURCE_ID IS NULL)) THEN
1820 raise_application_error
1821 (-20000
1822 ,'P_RESOURCE_ID and P_SERVER_ID cannot be NULL. (P_RESOURCE_ID = ' ||
1823 P_RESOURCE_ID || ') (P_SERVER_ID = ' || P_SERVER_ID || ')'
1824 ,TRUE
1825 );
1826 END IF;
1827
1828
1829 SAVEPOINT start_bind;
1830
1831
1832 SELECT
1833 TYPE_ID
1834 INTO
1835 l_svr_type_id
1836 FROM
1837 IEO_SVR_SERVERS
1838 WHERE
1839 SERVER_ID = P_SERVER_ID;
1840
1841
1842 --
1843 -- first invalidate all other entries of the same server type...
1844 --
1845 FOR cur in c_binds( l_svr_type_id )
1846 LOOP
1847
1848 UPDATE
1849 IEU_UWQ_AGENT_BINDINGS ab
1850 SET
1851 ab.LAST_UPDATE_DATE = SYSDATE,
1852 ab.NOT_VALID = 'Y'
1853 WHERE
1854 ab.RESOURCE_ID = P_RESOURCE_ID AND
1855 ab.SERVER_ID = cur.SERVER_ID;
1856
1857 END LOOP;
1858
1859
1860 UPDATE IEU_UWQ_AGENT_BINDINGS ab
1861 SET
1862 ab.LAST_UPDATE_DATE = SYSDATE,
1863 ab.NOT_VALID = NULL
1864 WHERE
1865 ab.RESOURCE_ID = P_RESOURCE_ID AND
1866 ab.SERVER_ID = P_SERVER_ID;
1867
1868
1869 IF (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) THEN
1870
1871 INSERT INTO IEU_UWQ_AGENT_BINDINGS
1872 ( RESOURCE_ID,
1873 SERVER_ID,
1874 LAST_UPDATE_DATE,
1875 NOT_VALID )
1876 VALUES (
1877 P_RESOURCE_ID,
1878 P_SERVER_ID,
1879 SYSDATE,
1880 NULL );
1881
1882 END IF;
1883
1884
1885 EXCEPTION
1886 WHEN OTHERS THEN
1887 ROLLBACK TO start_bind;
1888 RAISE;
1889
1890 END BIND_AGENT;
1891
1892
1893 /* Used by UWQ Server to unset an agent binding to a server. */
1894 PROCEDURE UNBIND_AGENT( P_RESOURCE_ID IN NUMBER, P_SERVER_ID IN NUMBER ) AS
1895 BEGIN
1896
1897 IF ((P_SERVER_ID IS NULL) OR (P_RESOURCE_ID IS NULL)) THEN
1898 raise_application_error
1899 (-20000
1900 ,'P_RESOURCE_ID and P_SERVER_ID cannot be NULL. (P_RESOURCE_ID = ' ||
1901 P_RESOURCE_ID || ') (P_SERVER_ID = ' || P_SERVER_ID || ')'
1902 ,TRUE
1903 );
1904 END IF;
1905
1906
1907 SAVEPOINT start_unbind;
1908
1909
1910 UPDATE IEU_UWQ_AGENT_BINDINGS ab
1911 SET
1912 ab.LAST_UPDATE_DATE = SYSDATE,
1913 ab.NOT_VALID = 'Y'
1914 WHERE
1915 ab.RESOURCE_ID = P_RESOURCE_ID AND
1916 ab.SERVER_ID = P_SERVER_ID;
1917
1918
1919 IF (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) THEN
1920 -- this would mean we tried to unbind, but it wasn't found... I can't think of
1921 -- any harm this would cause because we're trying to unbind anyway. (rcardill)
1922 NULL;
1923 END IF;
1924
1925
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928 ROLLBACK TO start_unbind;
1929 RAISE;
1930
1931 END UNBIND_AGENT;
1932
1933
1934 /* Used by UWQ Server to unset an agent binding to a server. */
1935 PROCEDURE CLEAR_ALL_AGENT_BINDINGS( P_RESOURCE_ID IN NUMBER
1936 ,P_SERVER_ID IN NUMBER
1937 ,P_MAJOR_LOAD_FACTOR IN NUMBER
1938 ,P_MINOR_LOAD_FACTOR IN NUMBER )
1939 AS
1940 BEGIN
1941
1942 IF (P_RESOURCE_ID IS NULL) THEN
1943 raise_application_error
1944 (-20000
1945 ,'P_RESOURCE_ID cannot be NULL. (P_RESOURCE_ID = ' || P_RESOURCE_ID || ')'
1946 ,TRUE );
1947 END IF;
1948
1949
1950 SAVEPOINT start_unbind;
1951
1952
1953 UPDATE IEU_UWQ_AGENT_BINDINGS ab
1954 SET
1955 ab.LAST_UPDATE_DATE = SYSDATE,
1956 ab.NOT_VALID = 'Y'
1957 WHERE
1958 ab.RESOURCE_ID = P_RESOURCE_ID;
1959
1960 IF (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) THEN
1961 NULL;
1962 END IF;
1963
1964 /* update rt info */
1965 /*server status is always 4 when agent logs in*/
1966 IEO_SVR_UTIL_PVT.UPDATE_RT_INFO( P_SERVER_ID, 4,
1967 P_MAJOR_LOAD_FACTOR,
1968 P_MINOR_LOAD_FACTOR, ' ' );
1969
1970 EXCEPTION
1971 WHEN OTHERS THEN
1972 ROLLBACK TO start_unbind;
1973 RAISE;
1974
1975 END CLEAR_ALL_AGENT_BINDINGS;
1976
1977 PROCEDURE UPDATE_SERVER_STARTUP_INFO( P_SERVER_ID IN NUMBER
1978 ,P_IP_ADDRESS IN VARCHAR2
1979 ,P_DNS_NAME IN VARCHAR2
1980 ,P_USER_ADDRESS IN VARCHAR2 )
1981 AS
1982
1983 BEGIN
1984
1985 /*clear all old server bindings*/
1986 IEO_SVR_UTIL_PVT.CLEAR_SERVER_BINDINGS( P_SERVER_ID );
1987
1988 /*update server information*/
1989 UPDATE IEO_SVR_SERVERS
1990 SET DNS_NAME = P_DNS_NAME,
1991 IP_ADDRESS = P_IP_ADDRESS,
1992 USER_ADDRESS = P_USER_ADDRESS
1993 WHERE SERVER_ID = P_SERVER_ID;
1994
1995 EXCEPTION
1996 WHEN OTHERS THEN
1997 RAISE;
1998
1999 END UPDATE_SERVER_STARTUP_INFO;
2000
2001 PROCEDURE BIND_AGENT_AND_UPDATE_LOAD( P_RESOURCE_ID IN NUMBER
2002 ,P_SERVER_ID IN NUMBER
2003 ,P_MAJOR_LOAD_FACTOR IN NUMBER
2004 ,P_MINOR_LOAD_FACTOR IN NUMBER
2005 ,X_EXISTING_BINDINGS OUT NOCOPY BINDING_CURSOR )
2006 AS
2007 l_binding_cursor BINDING_CURSOR;
2008 l_binding_statement VARCHAR2(350);
2009 BEGIN
2010 IF ((P_SERVER_ID IS NULL) OR (P_RESOURCE_ID IS NULL)) THEN
2011 raise_application_error
2012 (-20000
2013 ,'P_RESOURCE_ID and P_SERVER_ID cannot be NULL. (P_RESOURCE_ID = ' ||
2014 P_RESOURCE_ID || ') (P_SERVER_ID = ' || P_SERVER_ID || ')'
2015 ,TRUE
2016 );
2017 END IF;
2018
2019 /*create uwq binding*/
2020 BIND_AGENT( P_RESOURCE_ID, P_SERVER_ID );
2021
2022 /* update rt info */
2023 /*server status is always 4 when agent logs in*/
2024 IEO_SVR_UTIL_PVT.UPDATE_RT_INFO( P_SERVER_ID, 4,
2025 P_MAJOR_LOAD_FACTOR,
2026 P_MINOR_LOAD_FACTOR, ' ' );
2027
2028 /*
2029 this will have problems if multiple servers with the same type have valid bindings
2030 with the agent. But in this case we rely on the fact that every time a valid
2031 binding is created all other valid bindings for the same server type are reset
2032 (see BIND_AGENT proc.) so this case should never happen and we should get just
2033 one server. - ssk
2034 */
2035 l_binding_statement := 'SELECT bindings.server_id, svr_types.type_id FROM ' ||
2036 ' ieu_uwq_agent_bindings bindings, ' ||
2037 ' ieo_svr_types_b svr_types, ' ||
2038 ' ieo_svr_servers svrs ' ||
2039 ' WHERE bindings.resource_id = :1 ' ||
2040 ' AND bindings.server_id=svrs.server_id ' ||
2041 ' AND svr_types.type_id = svrs.type_id ' ||
2042 ' AND bindings.NOT_VALID IS NULL';
2043
2044 OPEN l_binding_cursor for l_binding_statement using P_RESOURCE_ID;
2045
2046 X_EXISTING_BINDINGS := l_binding_cursor;
2047
2048 return;
2049 EXCEPTION
2050 WHEN OTHERS THEN
2051 RAISE;
2052
2053 END BIND_AGENT_AND_UPDATE_LOAD;
2054
2055
2056 /* Used by UWQ Server to communicate Queue information to client. */
2057 PROCEDURE FORCE_UPDATE_MRT_DATA
2058 (P_RESOURCE_ID IN IEU.IEU_UWQ_SEL_MRT_DATA.RESOURCE_ID%TYPE
2059 ,P_SERVER_TYPE_ID IN IEU.IEU_UWQ_SEL_MRT_DATA.SVR_TYPE_ID%TYPE
2060 ,P_MEDIA_TYPE_ID IN IEU.IEU_UWQ_SEL_MRT_DATA.MEDIA_TYPE_ID%TYPE
2061 ,P_QUEUE_LIST IN SYSTEM.IEU_UWQ_SEL_MRT_QUEUES_NST
2062 )
2063 AS
2064 BEGIN
2065
2066 IF ( (P_RESOURCE_ID IS NULL) OR
2067 (P_SERVER_TYPE_ID IS NULL) OR
2068 (P_MEDIA_TYPE_ID IS NULL) )
2069 THEN
2070 raise_application_error
2071 (-20000
2072 ,'P_RESOURCE_ID, P_SERVER_TYPE_ID, and P_MEDIA_TYPE_ID cannot be NULL.' ||
2073 '(P_RESOURCE_ID = ' || P_RESOURCE_ID ||
2074 ') (P_SERVER_TYPE_ID = ' || P_SERVER_TYPE_ID ||
2075 ') (P_MEDIA_TYPE_ID = ' || P_MEDIA_TYPE_ID || ')'
2076 ,TRUE
2077 );
2078 END IF;
2079
2080
2081 SAVEPOINT start_update;
2082
2083
2084 --
2085 -- The technique used here is to always clear all entries for this
2086 -- combination of (resource_id + server_type + media_type), and then
2087 -- reset them to the values passed in on this UPDATE. The reason for the
2088 -- combination is that the same table can be used by multiple providers,
2089 -- or even the same provider, with different media types (i.e., MCM).
2090 -- UWQ plugins using this technique can simply publish their current state
2091 -- periodically.
2092 --
2093 -- NOTE: This procedure is called FORCE_XXX because we may want to come up
2094 -- with an incremental update sometime in the future for providers
2095 -- that have large amounts of data, with only small changes.
2096 --
2097 -- Ray Cardillo (01/24/2000)
2098 --
2099
2100 UPDATE IEU_UWQ_SEL_MRT_DATA
2101 SET
2102 NOT_VALID = 'Y',
2103 LAST_UPDATE_DATE = SYSDATE
2104 WHERE
2105 (RESOURCE_ID = P_RESOURCE_ID) AND
2106 (SVR_TYPE_ID = P_SERVER_TYPE_ID) AND
2107 (MEDIA_TYPE_ID = P_MEDIA_TYPE_ID);
2108
2109
2110 --
2111 -- Note: It's completely valid to send us a NULL queue list... it just means
2112 -- that there are no more entries that are valid.
2113 --
2114
2115 IF (P_QUEUE_LIST IS NOT NULL) THEN
2116
2117 FOR i IN P_QUEUE_LIST.FIRST..P_QUEUE_LIST.LAST LOOP
2118
2119 IF ((P_QUEUE_LIST(i) IS NOT NULL) AND
2120 (P_QUEUE_LIST(i).QUEUE_COUNT IS NOT NULL)) THEN
2121
2122
2123 --
2124 -- NOTE: Updated (ROWNUM <= 1) because we saw an environment
2125 -- that had duplicated rows (prob. from a DB copy) and
2126 -- both rows remained forever after that point. By only
2127 -- validating one per criteria, we will avoid this problem.
2128 --
2129
2130 -- Added condition for NULL QUEUE_NAME in the where clause because NULL
2131 -- names could not be compared and the update clause always ended up
2132 -- adding new rows to the table when the queue name was NULL
2133 UPDATE IEU_UWQ_SEL_MRT_DATA
2134 SET
2135 NOT_VALID = NULL,
2136 LAST_UPDATE_DATE = SYSDATE,
2137 QUEUE_COUNT = P_QUEUE_LIST(i).QUEUE_COUNT,
2138 PROVIDER_REF = p_QUEUE_LIST(i).PROVIDER_REF
2139 WHERE
2140 (RESOURCE_ID = P_RESOURCE_ID) AND
2141 (SVR_TYPE_ID = P_SERVER_TYPE_ID) AND
2142 (MEDIA_TYPE_ID = P_MEDIA_TYPE_ID) AND
2143 ( ( (QUEUE_NAME IS NULL) AND
2144 (P_QUEUE_LIST(i).QUEUE_NAME IS NULL ) ) OR
2145 (QUEUE_NAME = P_QUEUE_LIST(i).QUEUE_NAME) ) AND
2146 (ROWNUM <= 1);
2147
2148
2149 --
2150 -- If the update failed, we need to insert a new row...
2151 --
2152
2153 IF (SQL%NOTFOUND OR (SQL%ROWCOUNT <= 0)) THEN
2154
2155 INSERT INTO IEU_UWQ_SEL_MRT_DATA
2156 ( SEL_MRT_ID,
2157 RESOURCE_ID,
2158 SVR_TYPE_ID,
2159 MEDIA_TYPE_ID,
2160 LAST_UPDATE_DATE,
2161 NOT_VALID,
2162 QUEUE_NAME,
2163 QUEUE_COUNT,
2164 PROVIDER_REF )
2165 VALUES (
2166 IEU_UWQ_SEL_MRT_DATA_S1.NEXTVAL,
2167 P_RESOURCE_ID,
2168 P_SERVER_TYPE_ID,
2169 P_MEDIA_TYPE_ID,
2170 SYSDATE,
2171 NULL,
2172 P_QUEUE_LIST(i).QUEUE_NAME,
2173 P_QUEUE_LIST(i).QUEUE_COUNT,
2174 P_QUEUE_LIST(i).PROVIDER_REF );
2175
2176 END IF;
2177
2178 ELSE
2179 -- Somebody passed us crap...
2180 NULL;
2181 END IF;
2182
2183 END LOOP;
2184
2185 END IF;
2186
2187
2188 EXCEPTION
2189 WHEN OTHERS THEN
2190 ROLLBACK TO start_update;
2191 RAISE;
2192
2193 END FORCE_UPDATE_MRT_DATA;
2194
2195
2196 /* Used to determine if agent is eligible for ANY media */
2197 FUNCTION IS_AGENT_ELIGIBLE_FOR_MEDIA( P_RESOURCE_ID IN NUMBER )
2198 RETURN BOOLEAN
2199 AS
2200
2201 l_media_types EligibleMediaList;
2202
2203 BEGIN
2204
2205 DETERMINE_ELIGIBLE_MEDIA_TYPES(
2206 P_RESOURCE_ID,
2207 l_media_types );
2208
2209 IF (l_media_types is not null and l_media_types.COUNT > 0) THEN
2210 RETURN TRUE;
2211 END IF;
2212
2213 RETURN FALSE;
2214
2215 END IS_AGENT_ELIGIBLE_FOR_MEDIA;
2216
2217
2218 /* Used to determine if a connection to the UWQ server is required */
2219 FUNCTION IS_UWQ_SERVER_REQUIRED( P_RESOURCE_ID IN NUMBER )
2220 RETURN BOOLEAN
2221 AS
2222
2223 BEGIN
2224
2225 -- for now, these are the same...
2226 -- maybe they will be different some day
2227
2228 RETURN IS_AGENT_ELIGIBLE_FOR_MEDIA( P_RESOURCE_ID );
2229
2230 END IS_UWQ_SERVER_REQUIRED;
2231
2232
2233 /* Used to enumerate while setting FND_GLOBAL session variables */
2234 PROCEDURE ENUMERATE_WORK_NODES_FOR_SVR
2235 (P_RESOURCE_ID IN NUMBER
2236 ,P_USER_ID IN NUMBER
2237 ,P_RESP_ID IN NUMBER
2238 ,P_RESP_APPL_ID IN NUMBER
2239 ,P_LANGUAGE IN VARCHAR2
2240 ,P_SOURCE_LANG IN VARCHAR2
2241 )
2242 AS
2243
2244 l_old_lang varchar2(100);
2245 l_new_lang varchar2(100);
2246 l_lang varchar2(100);
2247
2248 BEGIN
2249
2250
2251 -- Changed on 02/21/01. Pseudo-translation was not maintained when view was
2252 -- refreshed.
2253 -- So we alter the session to set the NLS language to P_LANGUAGE which corresponds to the
2254 -- language set in the ICX Profile.
2255
2256 -- set NLS_LANGUAGE
2257 l_lang := 'alter session set nls_language = '|| ''''||
2258 substr(p_language, 1, (instr(p_language,'_',1,1) - 1) ) ||'''';
2259 execute immediate l_lang;
2260
2261 -- Set NLS_TERRITORY
2262
2263 l_lang := 'alter session set nls_territory = '|| ''''||
2264 substr(p_language, ( instr(p_language,'_',1,1) + 1 ), ( instr(p_language,'.',1,1) -
2265 instr(p_language,'_',1,1) - 1) ) || '''';
2266 execute immediate l_lang;
2267
2268 FND_GLOBAL.APPS_INITIALIZE( p_user_id, p_resp_id, p_resp_appl_id );
2269
2270 ENUMERATE_WORK_NODES( P_RESOURCE_ID, '', '' );
2271
2272 END ENUMERATE_WORK_NODES_FOR_SVR;
2273
2274
2275 /* Used to refresh while setting FND_GLOBAL session variables */
2276 PROCEDURE REFRESH_WORK_NODE_FOR_SVR
2277 (P_RESOURCE_ID IN NUMBER
2278 ,P_USER_ID IN NUMBER
2279 ,P_RESP_ID IN NUMBER
2280 ,P_RESP_APPL_ID IN NUMBER
2281
2282 )
2283 AS
2284
2285
2286 BEGIN
2287
2288 FND_GLOBAL.APPS_INITIALIZE( p_user_id, p_resp_id, p_resp_appl_id );
2289
2290 REFRESH_WORK_NODE_COUNTS( P_RESOURCE_ID );
2291
2292
2293
2294 END REFRESH_WORK_NODE_FOR_SVR;
2295
2296
2297 /* Used to add data to the UWQ node table */
2298 PROCEDURE ADD_UWQ_NODE_DATA
2299 (P_RESOURCE_ID IN NUMBER,
2300 P_SEL_ENUM_ID IN NUMBER,
2301 P_ENUMERATOR_DATAREC_LIST IN IEU_PUB.EnumeratorDataRecordList
2302 )
2303 AS
2304
2305 temp_err_msg VARCHAR2(4000);
2306 x_iterator NUMBER;
2307 x_new_node_id NUMBER;
2308 l_prnt_node_id NUMBER;
2309 l_curr_node_id NUMBER;
2310 l_node_weight NUMBER;
2311
2312 BEGIN
2313
2314
2315 --
2316 -- Make sure there is something to add!
2317 --
2318 BEGIN
2319 IF ( (P_ENUMERATOR_DATAREC_LIST IS NULL) OR
2320 (P_ENUMERATOR_DATAREC_LIST.LAST < 0) )
2321 THEN
2322 return;
2323 END IF;
2324 EXCEPTION
2325 WHEN OTHERS THEN
2326 return;
2327 END;
2328
2329
2330 --
2331 -- Get the next node ID
2332 --
2333 begin
2334
2335 select
2336 (max(node_id)+1)
2337 into
2338 l_curr_node_id
2339 from
2340 ieu_uwq_sel_rt_nodes
2341 where
2342 (resource_id = p_resource_id) and
2343 (not_valid = 'N');
2344
2345 exception
2346 when no_data_found then
2347 l_curr_node_id := 10010;
2348 end;
2349
2350
2351 if ((l_curr_node_id is null) or (l_curr_node_id < 10010)) then
2352 l_curr_node_id := 10010;
2353 end if;
2354
2355
2356 --
2357 -- check top level nodes to see if associated with Media. if associated
2358 -- with media, then it belongs under the reserved "Media" node area.
2359 --
2360 -- NOTE: purposely avoiding doing this in recursive WALK_TREE_ADD
2361 -- to avoid doing this check every time when it's only really needed
2362 -- for the top node. (Ray Cardillo / 05-22-01)
2363 --
2364 if ( (P_ENUMERATOR_DATAREC_LIST(0).MEDIA_TYPE_ID IS NOT NULL) )
2365 then
2366 l_prnt_node_id := IEU_CONSTS_PUB.G_SNID_MEDIA;
2367 else
2368 l_prnt_node_id := 0;
2369 end if;
2370
2371 IEU_PVT.L_IND_LIST_ITR := 0;
2372 IEU_PVT.L_RT_NODES_ITR := 0;
2373 IEU_PVT.L_BIND_VALS_ITR := 0;
2374
2375 -- Call the recursive procedure to insert or update the nodes
2376 IEU_PVT.WALK_TREE_ADD (
2377 P_ENUMERATOR_DATAREC_LIST,
2378 l_prnt_node_id,
2379 l_curr_node_id,
2380 0,
2381 p_sel_enum_id,
2382 p_resource_id,
2383 x_iterator,
2384 x_new_node_id
2385 );
2386
2387 BEGIN
2388 IF IEU_PVT.NODE_ID_LIST.FIRST IS NOT NULL THEN
2389 FORALL i IN IEU_PVT.NODE_ID_LIST.FIRST..IEU_PVT.NODE_ID_LIST.LAST SAVE EXCEPTIONS
2390 UPDATE IEU_UWQ_SEL_RT_NODES SET
2391 SEL_ENUM_ID = IEU_PVT.SEL_ENUM_ID_LIST(i),
2392 NODE_TYPE = IEU_PVT.NODE_TYPE_LIST(i),
2393 NODE_PID = IEU_PVT.NODE_PID_LIST(i),
2394 NODE_WEIGHT = IEU_PVT.NODE_WEIGHT_LIST(i),
2395 NODE_DEPTH = IEU_PVT.NODE_DEPTH_LIST(i),
2396 SEL_ENUM_PID = IEU_PVT.SEL_ENUM_PID_LIST(i),
2397 MEDIA_TYPE_ID = IEU_PVT.MEDIA_TYPE_ID_LIST(i),
2398 COUNT = IEU_PVT.COUNT_LIST(i),
2399 DATA_SOURCE = IEU_PVT.DATA_SOURCE_LIST(i),
2400 VIEW_NAME = IEU_PVT.VIEW_NAME_LIST(i),
2401 WHERE_CLAUSE = IEU_PVT.WHERE_CLAUSE_LIST(i),
2402 HIDE_IF_EMPTY = IEU_PVT.HIDE_IF_EMPTY_LIST(i),
2403 REFRESH_VIEW_NAME = IEU_PVT.REFRESH_VIEW_NAME_LIST(i),
2404 REFRESH_VIEW_SUM_COL = IEU_PVT.REFRESH_VIEW_SUM_COL_LIST(i),
2405 RES_CAT_ENUM_FLAG = IEU_PVT.RES_CAT_ENUM_FLAG_LIST(i),
2406 NOT_VALID = IEU_PVT.NOT_VALID_LIST(i),
2407 NODE_LABEL = IEU_PVT.NODE_LABEL_LIST(i),
2408 LAST_UPDATE_DATE = IEU_PVT.LAST_UPDATE_DATE_LIST(i),
2409 LAST_UPDATED_BY = IEU_PVT.LAST_UPDATED_BY_LIST(i),
2410 LAST_UPDATE_LOGIN = IEU_PVT.LAST_UPDATE_LOGIN_LIST(i)
2411 WHERE RESOURCE_ID = IEU_PVT.RESOURCE_ID_LIST(i)
2412 AND NODE_ID = IEU_PVT.NODE_ID_LIST(i);
2413
2414 IEU_PVT.LAST_UPDATED_BY_LIST.delete;
2415 IEU_PVT.LAST_UPDATE_DATE_LIST.delete;
2416 IEU_PVT.LAST_UPDATE_LOGIN_LIST.delete;
2417 IEU_PVT.RESOURCE_ID_LIST.delete;
2418 IEU_PVT.SEL_ENUM_ID_LIST.delete;
2419 IEU_PVT.NODE_ID_LIST.delete;
2420 IEU_PVT.NODE_TYPE_LIST.delete;
2421 IEU_PVT.NODE_LABEL_LIST.delete;
2422 IEU_PVT.COUNT_LIST.delete;
2423 IEU_PVT.DATA_SOURCE_LIST.delete;
2424 IEU_PVT.VIEW_NAME_LIST.delete;
2425 IEU_PVT.MEDIA_TYPE_ID_LIST.delete;
2426 IEU_PVT.SEL_ENUM_PID_LIST.delete;
2427 IEU_PVT.NODE_PID_LIST.delete;
2428 IEU_PVT.NODE_WEIGHT_LIST.delete;
2429 IEU_PVT.WHERE_CLAUSE_LIST.delete;
2430 IEU_PVT.HIDE_IF_EMPTY_LIST.delete;
2431 IEU_PVT.NOT_VALID_LIST.delete;
2432 IEU_PVT.REFRESH_VIEW_NAME_LIST.delete;
2433 IEU_PVT.RES_CAT_ENUM_FLAG_LIST.delete;
2434 IEU_PVT.REFRESH_VIEW_SUM_COL_LIST.delete;
2435 IEU_PVT.NODE_DEPTH_LIST.delete;
2436 END IF;
2437
2438 IF IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST.FIRST IS NOT NULL THEN
2439 FORALL i IN IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST.FIRST..IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST.LAST SAVE EXCEPTIONS
2440 insert into IEU_UWQ_SEL_RT_NODES values IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(i);
2441
2442 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST.delete;
2443 END IF;
2444
2445 IF IEU_PVT.BIND_VAR_NAME_LIST.FIRST IS NOT NULL THEN
2446 FORALL i IN IEU_PVT.BIND_VAR_NAME_LIST.FIRST..IEU_PVT.BIND_VAR_NAME_LIST.LAST SAVE EXCEPTIONS
2447 UPDATE IEU_UWQ_RTNODE_BIND_VALS SET
2448 LAST_UPDATED_BY = IEU_PVT.BIND_LAST_UPDATED_BY_LIST(i),
2449 LAST_UPDATE_DATE = IEU_PVT.BIND_LAST_UPDATE_DATE_LIST(i),
2450 LAST_UPDATE_LOGIN = IEU_PVT.BIND_LAST_UPDATE_LOGIN_LIST(i),
2451 BIND_VAR_VALUE = IEU_PVT.BIND_VAR_VALUE_LIST(i),
2452 BIND_VAR_DATATYPE = IEU_PVT.BIND_VAR_DATATYPE_LIST(i),
2453 NOT_VALID_FLAG = IEU_PVT.NOT_VALID_FLAG_LIST(i),
2454 SEL_RT_NODE_ID = IEU_PVT.BIND_SEL_RT_NODE_ID_LIST(i),
2455 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
2456 WHERE RESOURCE_ID = IEU_PVT.BIND_RESOURCE_ID_LIST(i)
2457 AND NODE_ID = IEU_PVT.BIND_NODE_ID_LIST(i)
2458 AND BIND_VAR_NAME = IEU_PVT.BIND_VAR_NAME_LIST(i);
2459
2460 IEU_PVT.BIND_LAST_UPDATED_BY_LIST.delete;
2461 IEU_PVT.BIND_LAST_UPDATE_DATE_LIST.delete;
2462 IEU_PVT.BIND_LAST_UPDATE_LOGIN_LIST.delete;
2463 IEU_PVT.BIND_SEL_RT_NODE_ID_LIST.delete;
2464 IEU_PVT.BIND_RESOURCE_ID_LIST.delete;
2465 IEU_PVT.BIND_NODE_ID_LIST.delete;
2466 IEU_PVT.BIND_VAR_NAME_LIST.delete;
2467 IEU_PVT.BIND_VAR_VALUE_LIST.delete;
2468 IEU_PVT.BIND_VAR_DATATYPE_LIST.delete;
2469 IEU_PVT.NOT_VALID_FLAG_LIST.delete;
2470 END IF;
2471
2472 IF IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST.FIRST IS NOT NULL THEN
2473 FORALL i IN IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST.FIRST..IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST.LAST SAVE EXCEPTIONS
2474 insert into IEU_UWQ_RTNODE_BIND_VALS values IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(i);
2475
2476 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST.delete;
2477 END IF;
2478 -- COMMIT;
2479
2480 EXCEPTION
2481 WHEN OTHERS THEN
2482 IEU_PVT.LAST_UPDATED_BY_LIST.delete;
2483 IEU_PVT.LAST_UPDATE_DATE_LIST.delete;
2484 IEU_PVT.LAST_UPDATE_LOGIN_LIST.delete;
2485 IEU_PVT.RESOURCE_ID_LIST.delete;
2486 IEU_PVT.SEL_ENUM_ID_LIST.delete;
2487 IEU_PVT.NODE_ID_LIST.delete;
2488 IEU_PVT.NODE_TYPE_LIST.delete;
2489 IEU_PVT.NODE_LABEL_LIST.delete;
2490 IEU_PVT.COUNT_LIST.delete;
2491 IEU_PVT.DATA_SOURCE_LIST.delete;
2492 IEU_PVT.VIEW_NAME_LIST.delete;
2493 IEU_PVT.MEDIA_TYPE_ID_LIST.delete;
2494 IEU_PVT.SEL_ENUM_PID_LIST.delete;
2495 IEU_PVT.NODE_PID_LIST.delete;
2496 IEU_PVT.NODE_WEIGHT_LIST.delete;
2497 IEU_PVT.WHERE_CLAUSE_LIST.delete;
2498 IEU_PVT.HIDE_IF_EMPTY_LIST.delete;
2499 IEU_PVT.NOT_VALID_LIST.delete;
2500 IEU_PVT.REFRESH_VIEW_NAME_LIST.delete;
2501 IEU_PVT.RES_CAT_ENUM_FLAG_LIST.delete;
2502 IEU_PVT.REFRESH_VIEW_SUM_COL_LIST.delete;
2503 IEU_PVT.NODE_DEPTH_LIST.delete;
2504
2505 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST.delete;
2506
2507 IEU_PVT.BIND_LAST_UPDATED_BY_LIST.delete;
2508 IEU_PVT.BIND_LAST_UPDATE_DATE_LIST.delete;
2509 IEU_PVT.BIND_LAST_UPDATE_LOGIN_LIST.delete;
2510 IEU_PVT.BIND_SEL_RT_NODE_ID_LIST.delete;
2511 IEU_PVT.BIND_RESOURCE_ID_LIST.delete;
2512 IEU_PVT.BIND_NODE_ID_LIST.delete;
2513 IEU_PVT.BIND_VAR_NAME_LIST.delete;
2514 IEU_PVT.BIND_VAR_VALUE_LIST.delete;
2515 IEU_PVT.BIND_VAR_DATATYPE_LIST.delete;
2516 IEU_PVT.NOT_VALID_FLAG_LIST.delete;
2517
2518 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST.delete;
2519
2520 RAISE;
2521 END;
2522
2523 -- we're going to make sure the proper node weight is put into the
2524 -- table, we also have to make sure that the UI is honoring the
2525 -- display order as well. (Ray Cardillo / 12-21-2000)
2526 select
2527 nvl (
2528 IEU_UWQ_UTIL_PUB.to_number_noerr (
2529 fnd_profile.value(e.work_q_order_profile_option) ) ,
2530 e.work_q_order_system_default
2531 )
2532 into
2533 l_node_weight
2534 from
2535 ieu_uwq_sel_enumerators e
2536 where
2537 e.sel_enum_id = p_sel_enum_id;
2538
2539 update
2540 ieu_uwq_sel_rt_nodes
2541 set
2542 node_weight = l_node_weight
2543 where
2544 resource_id = p_resource_id and
2545 node_id = l_curr_node_id;
2546
2547 END ADD_UWQ_NODE_DATA;
2548
2549 PROCEDURE WALK_TREE_ADD(
2550 P_ENUM_REC_LIST IN IEU_PUB.EnumeratorDataRecordList,
2551 P_PID IN PLS_INTEGER,
2552 P_CURR_NODE_ID IN PLS_INTEGER,
2553 P_REC_LIST_ITERATOR IN PLS_INTEGER,
2554 P_S_ENUM_ID IN NUMBER,
2555 P_RESOURCE_ID IN NUMBER,
2556 X_NEW_REC_LIST_ITERATOR IN OUT NOCOPY PLS_INTEGER,
2557 X_NEW_CURR_NODE_ID IN OUT NOCOPY PLS_INTEGER) AS
2558
2559 l_curr_node_id PLS_INTEGER;
2560 i PLS_INTEGER;
2561 l_sel_rt_node_id ieu_uwq_sel_rt_nodes.sel_rt_node_id%type;
2562 l_bind_var_name IEU_UWQ_RTNODE_BIND_VALS.BIND_VAR_NAME%TYPE;
2563 L_BIND_VAR_VALUE IEU_UWQ_RTNODE_BIND_VALS.BIND_VAR_VALUE%TYPE;
2564 L_BIND_VAR_DATA_TYPE IEU_UWQ_RTNODE_BIND_VALS.BIND_VAR_DATATYPE%TYPE;
2565 tempString varchar2(2000);
2566 l_counter number;
2567 j NUMBER;
2568 k NUMBER;
2569
2570 l_not_valid_flag VARCHAR2(1);
2571 l_object_version_number NUMBER;
2572 L_RTNODE_INSERT_FLAG VARCHAR2(1);
2573 L_BINDVALS_INSERT_FLAG VARCHAR2(1);
2574 L_RTNODE_BIND_VAR_ID NUMBER;
2575
2576 BEGIN
2577
2578 l_not_valid_flag := 'N';
2579 l_object_version_number := 1;
2580
2581 i := p_rec_list_iterator;
2582 l_curr_node_id := p_curr_node_id;
2583
2584 loop
2585 /*
2586 -- insert or update IEU_UWQ_RTNODE_BIND_VALS
2587 IEU_UWQ_SEL_RT_NODES_PKG.LOAD_ROW (
2588 X_RESOURCE_ID => p_resource_id,
2589 X_SEL_ENUM_ID => p_s_enum_id,
2590 X_NODE_ID => l_curr_node_id,
2591 X_NODE_TYPE => P_ENUM_REC_LIST(i).NODE_TYPE ,
2592 X_NODE_PID => p_pid,
2593 X_NODE_WEIGHT => l_curr_node_id,
2594 X_NODE_DEPTH => P_ENUM_REC_LIST(I).NODE_DEPTH,
2595 X_SEL_ENUM_PID => null,
2596 X_MEDIA_TYPE_ID => P_ENUM_REC_LIST(i).MEDIA_TYPE_ID,
2597 X_COUNT => NULL,
2598 X_DATA_SOURCE => P_ENUM_REC_LIST(i).DATA_SOURCE,
2599 X_VIEW_NAME => P_ENUM_REC_LIST(i).VIEW_NAME,
2600 X_WHERE_CLAUSE => P_ENUM_REC_LIST(i).WHERE_CLAUSE,
2601 X_HIDE_IF_EMPTY => P_ENUM_REC_LIST(i).HIDE_IF_EMPTY,
2602 X_NOT_VALID => 'N',
2603 X_NODE_LABEL => P_ENUM_REC_LIST(i).NODE_LABEL,
2604 X_REFRESH_VIEW_NAME => P_ENUM_REC_LIST(i).REFRESH_VIEW_NAME,
2605 X_RES_CAT_ENUM_FLAG => P_ENUM_REC_LIST(i).RES_CAT_ENUM_FLAG,
2606 X_REFRESH_VIEW_SUM_COL => P_ENUM_REC_LIST(i).REFRESH_VIEW_SUM_COL
2607 );
2608 */
2609
2610 L_RTNODE_INSERT_FLAG := '';
2611 L_SEL_RT_NODE_ID := '';
2612 BEGIN
2613 select sel_rt_node_id
2614 into l_sel_rt_node_id
2615 from ieu_uwq_sel_rt_nodes
2616 where RESOURCE_ID = P_RESOURCE_ID
2617 and NODE_ID = l_curr_node_id;
2618
2619 L_RTNODE_INSERT_FLAG := 'N';
2620 EXCEPTION
2621 WHEN NO_DATA_FOUND THEN
2622 SELECT IEU_UWQ_SEL_RT_NODES_S1.NEXTVAL INTO L_SEL_RT_NODE_ID FROM DUAL;
2623 L_RTNODE_INSERT_FLAG := 'Y';
2624 END;
2625 IF NVL(L_RTNODE_INSERT_FLAG, 'X') = 'N' THEN
2626 IEU_PVT.LAST_UPDATED_BY_LIST(i) := FND_GLOBAL.USER_ID;
2627 IEU_PVT.LAST_UPDATE_DATE_LIST(i) := sysdate;
2628 IEU_PVT.LAST_UPDATE_LOGIN_LIST(i) := FND_GLOBAL.LOGIN_ID;
2629 IEU_PVT.RESOURCE_ID_LIST(i) := P_RESOURCE_ID;
2630 IEU_PVT.SEL_ENUM_ID_LIST(i) := P_S_ENUM_ID;
2631 IEU_PVT.NODE_ID_LIST(i) := l_curr_node_id;
2632 IEU_PVT.NODE_TYPE_LIST(i) := P_ENUM_REC_LIST(i).NODE_TYPE;
2633 IEU_PVT.NODE_LABEL_LIST(i) := P_ENUM_REC_LIST(i).NODE_LABEL;
2634 IEU_PVT.COUNT_LIST(i) := null;
2635 IEU_PVT.DATA_SOURCE_LIST(i) := P_ENUM_REC_LIST(i).DATA_SOURCE;
2636 IEU_PVT.VIEW_NAME_LIST(i) := P_ENUM_REC_LIST(i).VIEW_NAME;
2637 IEU_PVT.MEDIA_TYPE_ID_LIST(i) := P_ENUM_REC_LIST(i).MEDIA_TYPE_ID;
2638 IEU_PVT.SEL_ENUM_PID_LIST(i) := null;
2639 IEU_PVT.NODE_PID_LIST(i) := p_pid;
2640 IEU_PVT.NODE_WEIGHT_LIST(i) := l_curr_node_id;
2641 IEU_PVT.WHERE_CLAUSE_LIST(i) := P_ENUM_REC_LIST(i).WHERE_CLAUSE;
2642 IEU_PVT.HIDE_IF_EMPTY_LIST(i) := P_ENUM_REC_LIST(i).HIDE_IF_EMPTY;
2643 IEU_PVT.NOT_VALID_LIST(i) := L_NOT_VALID_FLAG;
2644 IEU_PVT.REFRESH_VIEW_NAME_LIST(i) := P_ENUM_REC_LIST(i).REFRESH_VIEW_NAME;
2645 IEU_PVT.RES_CAT_ENUM_FLAG_LIST(i) := P_ENUM_REC_LIST(i).RES_CAT_ENUM_FLAG;
2646 IEU_PVT.REFRESH_VIEW_SUM_COL_LIST(i) := P_ENUM_REC_LIST(i).REFRESH_VIEW_SUM_COL;
2647 IEU_PVT.NODE_DEPTH_LIST(i) := P_ENUM_REC_LIST(i).NODE_DEPTH;
2648 ELSE
2649 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).SEL_RT_NODE_ID := L_SEL_RT_NODE_ID;
2650 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).CREATED_BY := FND_GLOBAL.USER_ID;
2651 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).CREATION_DATE := sysdate;
2652 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2653 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).LAST_UPDATE_DATE := sysdate;
2654 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2655 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).RESOURCE_ID := P_RESOURCE_ID;
2656 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).SEL_ENUM_ID := P_S_ENUM_ID;
2657 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).NODE_ID := l_curr_node_id;
2658 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).NODE_TYPE := P_ENUM_REC_LIST(i).NODE_TYPE;
2659 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).NODE_LABEL := P_ENUM_REC_LIST(i).NODE_LABEL;
2660 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).COUNT := null;
2661 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).DATA_SOURCE := P_ENUM_REC_LIST(i).DATA_SOURCE;
2662 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).VIEW_NAME := P_ENUM_REC_LIST(i).VIEW_NAME;
2663 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).MEDIA_TYPE_ID := P_ENUM_REC_LIST(i).MEDIA_TYPE_ID;
2664 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).SEL_ENUM_PID := null;
2665 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).NODE_PID := p_pid;
2666 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).NODE_WEIGHT := l_curr_node_id;
2667 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).WHERE_CLAUSE := P_ENUM_REC_LIST(i).WHERE_CLAUSE;
2668 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).HIDE_IF_EMPTY := P_ENUM_REC_LIST(i).HIDE_IF_EMPTY;
2669 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).NOT_VALID := L_NOT_VALID_FLAG;
2670 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).SECURITY_GROUP_ID := null;
2671 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).OBJECT_VERSION_NUMBER := L_OBJECT_VERSION_NUMBER;
2672 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).REFRESH_VIEW_NAME := P_ENUM_REC_LIST(i).REFRESH_VIEW_NAME;
2673 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).RES_CAT_ENUM_FLAG := P_ENUM_REC_LIST(i).RES_CAT_ENUM_FLAG;
2674 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).REFRESH_VIEW_SUM_COL := P_ENUM_REC_LIST(i).REFRESH_VIEW_SUM_COL;
2675 IEU_PVT.IEU_UWQ_SEL_RT_NODES_LIST(IEU_PVT.L_RT_NODES_ITR).NODE_DEPTH := P_ENUM_REC_LIST(i).NODE_DEPTH;
2676 IEU_PVT.L_RT_NODES_ITR := IEU_PVT.L_RT_NODES_ITR + 1;
2677 END IF;
2678
2679 -- Bind Variables are inserted into IEU_UWQ_RTNODE_BIND_VALS
2680 -- based on sel_rt_node_id. So select sel_node_id from
2681 -- ieu_uwq_sel_rt_nodes based on resource_id and node_id.
2682 /*
2683 select
2684 sel_rt_node_id
2685 into
2686 l_sel_rt_node_id
2687 from
2688 ieu_uwq_sel_rt_nodes
2689 where
2690 RESOURCE_ID = P_RESOURCE_ID and
2691 NODE_ID = l_curr_node_id;
2692 */
2693
2694 -- Parse the bind_var list and insert the bind names, values and types
2695 -- into IEU_UWQ_RTNODE_BIND_VALS
2696 -- Bind_var list is passed as <name1|val1|datatype1><name2|val2|datatype2>
2697
2698 j:= 1;
2699 l_counter := 1;
2700 k := 1;
2701
2702
2703 If (LENGTH(P_ENUM_REC_LIST(i).BIND_VARS) is not NULL)
2704 then
2705
2706 While (l_counter < LENGTH(P_ENUM_REC_LIST(i).BIND_VARS) )
2707 loop
2708
2709 tempString :=
2710 substr (
2711 P_ENUM_REC_LIST(i).BIND_VARS,
2712 instr(P_ENUM_REC_LIST(i).BIND_VARS, '<',1,j),
2713 ( instr(P_ENUM_REC_LIST(i).BIND_VARS, '>',1,j) -
2714 instr(P_ENUM_REC_LIST(i).BIND_VARS, '<',1,j)+1 )
2715 );
2716
2717 L_BIND_VAR_NAME :=
2718 substr (
2719 tempString,
2720 2,
2721 instr(tempString, '|',1,k) - 2
2722 );
2723 L_BIND_VAR_VALUE :=
2724 substr (
2725 tempString,
2726 instr(tempString, '|',1,k) + 1,
2727 ( instr(tempString,'|',1,k+1) -
2728 instr(tempString, '|',1,k) - 1)
2729 );
2730 L_BIND_VAR_DATA_TYPE :=
2731 substr (
2732 tempString,
2733 instr(tempString, '|',1,k+1) + 1,
2734 length(tempstring) - instr(tempString, '|',1,k+1) -1
2735 );
2736
2737 l_counter := instr(P_ENUM_REC_LIST(i).BIND_VARS, '>',1,j);
2738 j := j+1;
2739
2740 /*
2741 -- insert or update IEU_UWQ_RTNODE_BIND_VALS
2742 IEU_UWQ_RTNODE_BIND_VAL_PKG.LOAD_ROW (
2743 P_RESOURCE_ID => P_RESOURCE_ID,
2744 P_NODE_ID => L_CURR_NODE_ID,
2745 P_SEL_RT_NODE_ID => L_SEL_RT_NODE_ID,
2746 P_BIND_VAR_NAME => L_BIND_VAR_NAME,
2747 P_BIND_VAR_VALUE => L_BIND_VAR_VALUE,
2748 P_BIND_VAR_DATA_TYPE => L_BIND_VAR_DATA_TYPE
2749 );
2750 */
2751
2752 L_BINDVALS_INSERT_FLAG := '';
2753 L_RTNODE_BIND_VAR_ID := '';
2754 BEGIN
2755 select rtnode_bind_var_id
2756 into l_rtnode_bind_var_id
2757 from ieu_uwq_rtnode_bind_vals
2758 where RESOURCE_ID = P_RESOURCE_ID
2759 and NODE_ID = l_curr_node_id
2760 and BIND_VAR_NAME = L_BIND_VAR_NAME;
2761
2762 L_BINDVALS_INSERT_FLAG := 'N';
2763 EXCEPTION
2764 WHEN NO_DATA_FOUND THEN
2765 SELECT IEU_UWQ_RTNODE_BIND_VALS_S1.NEXTVAL INTO L_RTNODE_BIND_VAR_ID FROM DUAL;
2766 L_BINDVALS_INSERT_FLAG := 'Y';
2767 END;
2768 IF NVL(L_BINDVALS_INSERT_FLAG, 'X') = 'N' THEN
2769 IEU_PVT.BIND_LAST_UPDATED_BY_LIST(IEU_PVT.L_IND_LIST_ITR) := FND_GLOBAL.USER_ID;
2770 IEU_PVT.BIND_LAST_UPDATE_DATE_LIST(IEU_PVT.L_IND_LIST_ITR) := SYSDATE;
2771 IEU_PVT.BIND_LAST_UPDATE_LOGIN_LIST(IEU_PVT.L_IND_LIST_ITR) := FND_GLOBAL.LOGIN_ID;
2772 IEU_PVT.BIND_SEL_RT_NODE_ID_LIST(IEU_PVT.L_IND_LIST_ITR) := L_SEL_RT_NODE_ID;
2773 IEU_PVT.BIND_RESOURCE_ID_LIST(IEU_PVT.L_IND_LIST_ITR) := P_RESOURCE_ID ;
2774 IEU_PVT.BIND_NODE_ID_LIST(IEU_PVT.L_IND_LIST_ITR) := L_CURR_NODE_ID ;
2775 IEU_PVT.BIND_VAR_NAME_LIST(IEU_PVT.L_IND_LIST_ITR) := L_BIND_VAR_NAME ;
2776 IEU_PVT.BIND_VAR_VALUE_LIST(IEU_PVT.L_IND_LIST_ITR) := L_BIND_VAR_VALUE;
2777 IEU_PVT.BIND_VAR_DATATYPE_LIST(IEU_PVT.L_IND_LIST_ITR) := L_BIND_VAR_DATA_TYPE;
2778 IEU_PVT.NOT_VALID_FLAG_LIST(IEU_PVT.L_IND_LIST_ITR) := L_NOT_VALID_FLAG;
2779 IEU_PVT.L_IND_LIST_ITR := IEU_PVT.L_IND_LIST_ITR + 1;
2780 ELSE
2781 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).RTNODE_BIND_VAR_ID := L_RTNODE_BIND_VAR_ID;
2782 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).OBJECT_VERSION_NUMBER := L_OBJECT_VERSION_NUMBER;
2783 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).CREATED_BY := FND_GLOBAL.USER_ID;
2784 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).CREATION_DATE := sysdate;
2785 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2786 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).LAST_UPDATE_DATE := sysdate;
2787 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2788 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).SECURITY_GROUP_ID := null;
2789 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).SEL_RT_NODE_ID := L_SEL_RT_NODE_ID;
2790 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).RESOURCE_ID := P_RESOURCE_ID;
2791 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).NODE_ID := L_CURR_NODE_ID;
2792 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).BIND_VAR_NAME := L_BIND_VAR_NAME;
2793 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).BIND_VAR_VALUE := L_BIND_VAR_VALUE;
2794 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).BIND_VAR_DATATYPE := L_BIND_VAR_DATA_TYPE;
2795 IEU_PVT.IEU_UWQ_RTNODE_BIND_VALS_LIST(IEU_PVT.L_BIND_VALS_ITR).NOT_VALID_FLAG := L_NOT_VALID_FLAG;
2796 IEU_PVT.L_BIND_VALS_ITR := IEU_PVT.L_BIND_VALS_ITR + 1;
2797 END IF;
2798 END LOOP;
2799 END IF;
2800
2801
2802 if ( (i = p_enum_rec_list.last)
2803 or
2804 (p_enum_rec_list(i).node_depth > p_enum_rec_list(i+1).node_depth)
2805 or
2806 (p_enum_rec_list(P_REC_LIST_ITERATOR).node_depth
2807 > p_enum_rec_list(i).node_depth))
2808 then
2809
2810 -- exit out of the loop as this is either the end of the list or the
2811 -- node depth of the next node is greater then the prev node
2812
2813 if ( (i < p_enum_rec_list.last )
2814 and
2815 (p_enum_rec_list(i).node_depth > p_enum_rec_list(i+1).node_depth ) )
2816 then
2817
2818 -- increment the node id and i if it is not the last element
2819 -- and if the node depth of the current node is greater than
2820 -- node depth of the next node.
2821
2822 l_curr_node_id := l_curr_node_id + 1;
2823 i := i + 1;
2824
2825 end if;
2826 exit;
2827
2828 elsif (p_enum_rec_list(i).node_depth < p_enum_rec_list(i+1).node_depth )
2829 then
2830
2831 -- call the recursive algorithm for all the sub nodes
2832 walk_tree_add (
2833 p_enum_rec_list, -- complete enum record list
2834 l_curr_node_id, -- node p_id
2835 l_curr_node_id + 1, -- current node id
2836 i + 1, -- list iterator
2837 p_s_enum_id, -- enum id
2838 p_resource_id, -- resource id
2839 i, -- new list iterator
2840 l_curr_node_id -- new current node id
2841 );
2842
2843 elsif (( (i < p_enum_rec_list.last )
2844 and
2845 (p_enum_rec_list(i).node_depth = p_enum_rec_list(i+1).node_depth )) )
2846 then
2847
2848 -- if node depths are equal just increment the node id and set the pid
2849
2850 l_curr_node_id := l_curr_node_id + 1;
2851 i := i + 1;
2852
2853 end if;
2854
2855 exit when (i >= p_enum_rec_list.count );
2856
2857 end loop;
2858
2859 -- i should not be incremented until the node depth of the i is equal
2860 -- to the node depth of the current node
2861
2862 if ((i = p_enum_rec_list.last )
2863 and
2864 (p_enum_rec_list(P_REC_LIST_ITERATOR).node_depth
2865 = p_enum_rec_list(i).node_depth ) )
2866 then
2867
2868 l_curr_node_id := l_curr_node_id + 1;
2869 i := i + 1;
2870
2871 end if;
2872
2873 x_new_curr_node_id := l_curr_node_id;
2874 X_NEW_REC_LIST_ITERATOR := i;
2875
2876
2877 END WALK_TREE_ADD;
2878
2879 PROCEDURE CHECK_AO_MANUAL_MODE(l_resource_id IN NUMBER,
2880 l_ret_val OUT NOCOPY BOOLEAN)
2881 AS
2882
2883 l_sql_stmt VARCHAR2(1000);
2884 l_count NUMBER;
2885 l_count1 NUMBER; --Added for bug7149127
2886 l_obj_code_flag VARCHAR2(1);
2887 l_select_id JTF_OBJECTS_B.SELECT_ID%TYPE;
2888 l_select_name JTF_OBJECTS_B.SELECT_NAME%TYPE;
2889 l_select_details JTF_OBJECTS_B.SELECT_DETAILS%TYPE;
2890 l_from_table JTF_OBJECTS_B.FROM_TABLE%TYPE;
2891 l_where_Clause JTF_OBJECTS_B.WHERE_CLAUSE%TYPE;
2892
2893 BEGIN
2894
2895 l_obj_code_flag := 'T';
2896 BEGIN
2897 SELECT select_id, select_name, select_details, from_table,where_clause
2898 INTO l_select_id,l_select_name, l_select_details, l_from_table, l_where_clause
2899 FROM jtf_objects_b
2900 WHERE object_code = 'AOMANMODE';
2901 EXCEPTION
2902 WHEN OTHERS THEN
2903 l_count := 0;
2904 END;
2905
2906 /* Start of fix for bug7149127 */
2907
2908 BEGIN
2909 select count(*) into l_count1 from ast_grp_campaigns a, IEC_G_EXECUTING_LISTS_V b, -- for bug 6982201
2910 JTF_RS_GROUP_MEMBERS c, JTF_RS_GROUPS_DENORM d
2911 where c.group_id = d.group_id
2912 and a.group_id = d.parent_group_id
2913 and c.resource_id = l_resource_id
2914 and a.campaign_id = b.schedule_id
2915 and b.DIALING_METHOD <> 'MAN';
2916 EXCEPTION
2917 WHEN OTHERS THEN
2918 l_count1 := 0;
2919 END;
2920
2921 /* End of fix for bug7149127 */
2922
2923 l_sql_stmt := 'BEGIN SELECT '|| l_select_id || ' INTO :l_count ';
2924 l_sql_stmt := l_sql_stmt || ' FROM '|| l_from_table ||
2925 ' WHERE ' ||l_where_clause || ';END; ';
2926
2927
2928 IF (l_obj_code_flag = 'T')
2929 THEN
2930
2931 EXECUTE IMMEDIATE l_sql_stmt
2932 USING out l_count, in l_resource_id;
2933
2934 END IF;
2935
2936 /*Start of fix for bug7149127 */
2937
2938 /* IF (l_count IS NULL)
2939 THEN
2940 l_count := 0;
2941 END IF;
2942
2943 IF (l_count = 0)
2944 THEN
2945 l_ret_val := True; -- AO Manual Mode
2946 ELSE
2947 l_ret_val := False; -- Ao Normal Mode
2948 END IF; */
2949
2950 IF (l_count IS NULL and l_count1 IS NULL)
2951 THEN
2952 l_count := 0;
2953 l_count1 := 0;
2954 END IF;
2955
2956 IF (l_count = 0 and l_count1 = 0)
2957 THEN
2958 l_ret_val := True; -- AO Manual Mode
2959 ELSE
2960 l_ret_val := False; -- Ao Normal Mode
2961 END IF;
2962
2963 /* End of fix for bug7149127 */
2964
2965 END CHECK_AO_MANUAL_MODE;
2966
2967 -- Niraj, 26-May-2005, Added for Bug 4389449
2968 PROCEDURE REFRESH_CUR_NODE_CNTS_FOR_SVR (
2969 P_RESOURCE_ID IN NUMBER
2970 ,P_USER_ID IN NUMBER
2971 ,P_RESP_ID IN NUMBER
2972 ,P_RESP_APPL_ID IN NUMBER
2973 ,p_node_id IN NUMBER
2974 ,x_node_id_list OUT NOCOPY varchar2) AS
2975 BEGIN
2976 FND_GLOBAL.APPS_INITIALIZE( p_user_id, p_resp_id, p_resp_appl_id);
2977 REFRESH_CUR_NODE_COUNTS(p_resource_id, p_node_id, x_node_id_list);
2978 END REFRESH_CUR_NODE_CNTS_FOR_SVR;
2979
2980
2981 PROCEDURE REFRESH_CUR_NODE_COUNTS(p_resource_id in number, p_node_id in number, x_node_id_list OUT NOCOPY varchar2) AS
2982
2983 l_where_clause VARCHAR2(30000);
2984 l_refresh_view_name varchar2(200);
2985 l_refresh_view_sum_col varchar2(200);
2986 l_sel_rt_node_id number;
2987 l_node_id number(10);
2988 l_node_pid number(10);
2989 l_node_depth number;
2990 l_sel_enum_id number(15);
2991 l_res_cat_enum_flag varchar2(1);
2992 l_view_name varchar2(512);
2993 l_bindvallist BindValList;
2994 j number := 1;
2995
2996
2997 i integer := 0;
2998 curr_node_id number(10);
2999 curr_node_depth number;
3000
3001 l_ref_flag varchar2(1);
3002
3003 cursor c_nodes(p_sel_enum_id in number, p_node_depth in number) is
3004 SELECT
3005 sel_rt_node_id,
3006 node_id,
3007 node_pid,
3008 node_depth,
3009 view_name,
3010 where_clause,
3011 sel_enum_id,
3012 refresh_view_name,
3013 refresh_view_sum_col,
3014 res_cat_enum_flag
3015 from
3016 ( SELECT
3017 rt_nodes.sel_rt_node_id,
3018 rt_nodes.node_id,
3019 rt_nodes.node_pid,
3020 rt_nodes.node_depth,
3021 rt_nodes.view_name,
3022 rt_nodes.where_clause,
3023 rt_nodes.sel_enum_id,
3024 rt_nodes.refresh_view_name,
3025 rt_nodes.refresh_view_sum_col,
3026 rt_nodes.res_cat_enum_flag
3027 FROM
3028 ieu_uwq_sel_rt_nodes rt_nodes
3029 WHERE
3030 (rt_nodes.resource_id = p_resource_id) AND
3031 (rt_nodes.sel_enum_id = p_sel_enum_id) AND
3032 (rt_nodes.not_valid = 'N')
3033 ) ieu_uwq_sel_enumerators
3034 -- connect by node_pid = node_id; -- Niraj: Bug 4352211, 06-May-2005: Commented this and added below 2 statements,
3035 start with node_depth = p_node_depth -- Niraj: Bug 4389449, 24-May-2005: Added p_node_depth instead of hardcoding to 1
3036 connect by prior node_id = node_pid;
3037
3038 /*
3039 cursor c_nodes(p_sel_enum_id in number) is
3040 SELECT
3041 rt_nodes.sel_rt_node_id,
3042 rt_nodes.node_id,
3043 rt_nodes.node_pid,
3044 rt_nodes.node_depth,
3045 rt_nodes.view_name,
3046 rt_nodes.where_clause,
3047 rt_nodes.sel_enum_id,
3048 rt_nodes.refresh_view_name,
3049 rt_nodes.refresh_view_sum_col,
3050 rt_nodes.res_cat_enum_flag
3051 FROM
3052 ieu_uwq_sel_rt_nodes rt_nodes
3053 WHERE
3054 (rt_nodes.resource_id = p_resource_id) AND
3055 (rt_nodes.sel_enum_id = p_sel_enum_id) AND
3056 (rt_nodes.not_valid = 'N');
3057 */
3058
3059 cursor c_media_nodes is
3060 SELECT
3061 rt_nodes.sel_enum_id, rt_nodes.node_depth
3062 FROM
3063 ieu_uwq_sel_rt_nodes rt_nodes
3064 WHERE
3065 (rt_nodes.resource_id = p_resource_id) AND
3066 (rt_nodes.node_pid = 4000) AND
3067 (rt_nodes.not_valid = 'N');
3068
3069 CURSOR c_bindVal IS
3070 SELECT
3071 rt_nodes_bind_val.SEL_RT_NODE_ID,
3072 rt_nodes_bind_val.node_id,
3073 rt_nodes_bind_val.BIND_VAR_NAME,
3074 rt_nodes_bind_val.bind_var_value
3075 FROM
3076 ieu_uwq_rtnode_bind_vals rt_nodes_bind_val
3077 WHERE
3078 (rt_nodes_bind_val.resource_id = p_resource_id) AND
3079 (rt_nodes_bind_val.node_id > 0) AND
3080 (rt_nodes_bind_val.not_valid_flag = 'N');
3081
3082 l_count number;
3083 l_iterator number;
3084
3085 BEGIN
3086
3087 l_iterator := 0;
3088
3089 IF IEU_PVT.SEL_RT_NODE_ID_REF_LIST.FIRST IS NOT NULL THEN
3090 IEU_PVT.SEL_RT_NODE_ID_REF_LIST.DELETE;
3091 IEU_PVT.REF_COUNT_LIST.DELETE;
3092 END IF;
3093
3094 l_ref_flag := '';
3095 For b in c_bindVal
3096 loop
3097 -- j := 1;
3098 l_bindvallist(j).sel_rt_node_id := b.sel_rt_node_id;
3099 l_bindvallist(j).node_id := b.node_id;
3100 l_bindvallist(j).bind_var_name := b.bind_var_name;
3101 l_bindvallist(j).bind_var_value := b.bind_var_value;
3102
3103 j := j + 1;
3104 end loop;
3105
3106 begin
3107 SELECT
3108 rt_nodes.sel_rt_node_id,
3109 rt_nodes.node_id,
3110 rt_nodes.node_pid,
3111 rt_nodes.node_depth,
3112 rt_nodes.view_name,
3113 rt_nodes.where_clause,
3114 rt_nodes.sel_enum_id,
3115 rt_nodes.refresh_view_name,
3116 rt_nodes.refresh_view_sum_col,
3117 rt_nodes.res_cat_enum_flag
3118 INTO
3119 l_sel_rt_node_id,
3120 l_node_id,
3121 l_node_pid,
3122 l_node_depth,
3123 l_view_name,
3124 l_where_clause,
3125 l_sel_enum_id,
3126 l_refresh_view_name,
3127 l_refresh_view_sum_col,
3128 l_res_cat_enum_flag
3129 FROM
3130 ieu_uwq_sel_rt_nodes rt_nodes
3131 WHERE
3132 (rt_nodes.resource_id = p_resource_id) AND
3133 (rt_nodes.node_id = p_node_id) AND
3134 (rt_nodes.not_valid = 'N');
3135 exception when others then null;
3136 end;
3137
3138 curr_node_id := l_node_id;
3139 curr_node_depth := l_node_depth;
3140
3141 if (curr_node_id <> 4000) then
3142
3143 for node in c_nodes(l_sel_enum_id, l_node_depth)
3144 loop
3145
3146 if (node.node_id = l_node_id) then
3147 l_ref_flag := 'T';
3148 end if;
3149
3150 curr_node_depth := node.node_depth;
3151 -- curr_node_id := node.node_pid;
3152 i := i +1;
3153
3154 if (curr_node_depth > l_node_depth) and (nvl(l_ref_flag, 'F') = 'T') then
3155
3156 -- if (curr_node_depth > l_node_depth)
3157 -- and (curr_node_id = node.node_pid) then
3158
3159 l_count := '';
3160 l_iterator := l_iterator + 1;
3161 IEU_PVT.REFRESH_NODE(p_node_id => node.node_id,
3162 p_node_pid => node.node_pid,
3163 p_sel_enum_id => node.sel_enum_id,
3164 p_where_clause => node.where_clause,
3165 p_res_cat_enum_flag => node.res_cat_enum_flag,
3166 p_refresh_view_name => node.refresh_view_name,
3167 p_refresh_view_sum_col => node.refresh_view_sum_col,
3168 p_sel_rt_node_id => node.sel_rt_node_id,
3169 p_count => 0,
3170 p_resource_id => p_resource_id,
3171 p_view_name => node.view_name,
3172 p_bindvallist => l_bindvallist,
3173 x_count => l_count);
3174 x_node_id_list := x_node_id_list||node.node_id||fnd_global.local_chr(20);
3175
3176 IEU_PVT.SEL_RT_NODE_ID_REF_LIST(l_iterator) := node.sel_rt_node_id;
3177 IEU_PVT.REF_COUNT_LIST(l_iterator) := l_count;
3178
3179 elsif (node.node_depth <= l_node_depth) and (nvl(l_ref_flag, 'F') = 'T') and (node.node_id <> l_node_id) then
3180 l_ref_flag := '';
3181 exit;
3182 end if;
3183 end loop;
3184
3185 BEGIN
3186 IF IEU_PVT.SEL_RT_NODE_ID_REF_LIST.FIRST IS NOT NULL THEN
3187 FORALL x IN IEU_PVT.SEL_RT_NODE_ID_REF_LIST.FIRST..IEU_PVT.SEL_RT_NODE_ID_REF_LIST.LAST SAVE EXCEPTIONS
3188 UPDATE IEU_UWQ_SEL_RT_NODES
3189 SET COUNT = IEU_PVT.REF_COUNT_LIST(x)
3190 WHERE SEL_RT_NODE_ID = IEU_PVT.SEL_RT_NODE_ID_REF_LIST(x)
3191 AND RESOURCE_ID = P_RESOURCE_ID;
3192 COMMIT;
3193
3194 IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
3195 IEU_PVT.REF_COUNT_LIST.delete;
3196 END IF;
3197
3198 EXCEPTION
3199 WHEN OTHERS THEN
3200 IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
3201 IEU_PVT.REF_COUNT_LIST.delete;
3202 END;
3203
3204 i := i + 1;
3205 x_node_id_list := x_node_id_list||l_node_id||fnd_global.local_chr(20);
3206
3207 l_count := '';
3208 IEU_PVT.REFRESH_NODE(p_node_id => l_node_id,
3209 p_node_pid => l_node_pid,
3210 p_sel_enum_id => l_sel_enum_id,
3211 p_where_clause => l_where_clause,
3212 p_res_cat_enum_flag => l_res_cat_enum_flag,
3213 p_refresh_view_name => l_refresh_view_name,
3214 p_refresh_view_sum_col => l_refresh_view_sum_col,
3215 p_sel_rt_node_id => l_sel_rt_node_id,
3216 p_count => 0,
3217 p_resource_id => p_resource_id,
3218 p_view_name => l_view_name,
3219 p_bindvallist => l_bindvallist,
3220 x_count => l_count);
3221
3222 BEGIN
3223 UPDATE IEU_UWQ_SEL_RT_NODES
3224 SET COUNT = l_count
3225 WHERE SEL_RT_NODE_ID = l_sel_rt_node_id
3226 AND RESOURCE_ID = P_RESOURCE_ID;
3227 COMMIT;
3228
3229 EXCEPTION
3230 WHEN OTHERS THEN
3231 NULL;
3232 END;
3233
3234 elsif (l_node_id = 4000) then
3235 x_node_id_list := x_node_id_list||l_node_id||fnd_global.local_chr(20);
3236
3237 for media_nodes in c_media_nodes
3238 loop
3239 for node in c_nodes(media_nodes.sel_enum_id, media_nodes.node_depth)
3240 loop
3241 i := i + 1;
3242
3243 l_count := '';
3244 l_iterator := l_iterator + 1;
3245 IEU_PVT.REFRESH_NODE(p_node_id => node.node_id,
3246 p_node_pid => node.node_pid,
3247 p_sel_enum_id => node.sel_enum_id,
3248 p_where_clause => node.where_clause,
3249 p_res_cat_enum_flag => node.res_cat_enum_flag,
3250 p_refresh_view_name => node.refresh_view_name,
3251 p_refresh_view_sum_col => node.refresh_view_sum_col,
3252 p_sel_rt_node_id => node.sel_rt_node_id,
3253 p_count => 0,
3254 p_resource_id => p_resource_id,
3255 p_view_name => node.view_name,
3256 p_bindvallist => l_bindvallist,
3257 x_count => l_count);
3258
3259 x_node_id_list := x_node_id_list||node.node_id||fnd_global.local_chr(20);
3260
3261 IEU_PVT.SEL_RT_NODE_ID_REF_LIST(l_iterator) := node.sel_rt_node_id;
3262 IEU_PVT.REF_COUNT_LIST(l_iterator) := l_count;
3263
3264 end loop;
3265 end loop;
3266
3267 BEGIN
3268 IF IEU_PVT.SEL_RT_NODE_ID_REF_LIST.FIRST IS NOT NULL THEN
3269 FORALL x IN IEU_PVT.SEL_RT_NODE_ID_REF_LIST.FIRST..IEU_PVT.SEL_RT_NODE_ID_REF_LIST.LAST SAVE EXCEPTIONS
3270 UPDATE IEU_UWQ_SEL_RT_NODES
3271 SET COUNT = IEU_PVT.REF_COUNT_LIST(x)
3272 WHERE SEL_RT_NODE_ID = IEU_PVT.SEL_RT_NODE_ID_REF_LIST(x)
3273 AND RESOURCE_ID = P_RESOURCE_ID;
3274 COMMIT;
3275
3276 IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
3277 IEU_PVT.REF_COUNT_LIST.delete;
3278 END IF;
3279
3280 EXCEPTION
3281 WHEN OTHERS THEN
3282 IEU_PVT.SEL_RT_NODE_ID_REF_LIST.delete;
3283 IEU_PVT.REF_COUNT_LIST.delete;
3284 END;
3285
3286 l_count := '';
3287 IEU_PVT.REFRESH_NODE(p_node_id => l_node_id,
3288 p_node_pid => l_node_pid,
3289 p_sel_enum_id => l_sel_enum_id,
3290 p_where_clause => l_where_clause,
3291 p_res_cat_enum_flag => l_res_cat_enum_flag,
3292 p_refresh_view_name => l_refresh_view_name,
3293 p_refresh_view_sum_col => l_refresh_view_sum_col,
3294 p_sel_rt_node_id => l_sel_rt_node_id,
3295 p_count => 0,
3296 p_resource_id => p_resource_id,
3297 p_view_name => l_view_name,
3298 p_bindvallist => l_bindvallist,
3299 x_count => l_count);
3300
3301 BEGIN
3302 UPDATE IEU_UWQ_SEL_RT_NODES
3303 SET COUNT = l_count
3304 WHERE SEL_RT_NODE_ID = l_sel_rt_node_id
3305 AND RESOURCE_ID = P_RESOURCE_ID;
3306 COMMIT;
3307
3308 EXCEPTION
3309 WHEN OTHERS THEN
3310 NULL;
3311 END;
3312
3313 end if;
3314
3315 end;
3316
3317 PROCEDURE NEW_AO_TEL_CONNECT_RULE(
3318 p_resource_id IN NUMBER,
3319 p_elig_media_uuid IN VARCHAR2,
3320 x_login_media_uuid OUT NOCOPY varchar2) AS
3321
3322 l_media_UUID_OUT VARCHAR2(38);
3323 l_ao_man_mode BOOLEAN := False;
3324
3325 BEGIN
3326
3327 ieu_pvt.check_ao_manual_mode(p_resource_id, l_ao_man_mode);
3328
3329 IF (NOT l_ao_man_mode)
3330 THEN
3331 l_media_UUID_OUT := '50BFCF20B6F511D3A05000C04F53FBA6'; -- AO Normal Mode
3332 ELSE
3333 l_media_UUID_OUT := null; -- AO Manual Mode
3334 END IF;
3335
3336 -- IEU_PUB.SET_MEDIA_UUID_FOR_LOGIN(l_media_UUID_OUT, x_media_uuid);
3337 x_login_media_uuid := l_media_UUID_OUT;
3338
3339 END;
3340
3341 -- Niraj, 26-May-2005, Added for Bug 4389449
3342 PROCEDURE REFRESH_SEL_NODE_CNTS_FOR_SVR (
3343 P_RESOURCE_ID IN NUMBER
3344 ,P_USER_ID IN NUMBER
3345 ,P_RESP_ID IN NUMBER
3346 ,P_RESP_APPL_ID IN NUMBER
3347 ,p_node_id_string in varchar2
3348 ,x_node_id_list OUT NOCOPY varchar2)
3349 AS
3350 BEGIN
3351 FND_GLOBAL.APPS_INITIALIZE( p_user_id, p_resp_id, p_resp_appl_id );
3352 REFRESH_SELECTIVE_NODE_COUNTS(p_resource_id, p_node_id_string, x_node_id_list);
3353 END REFRESH_SEL_NODE_CNTS_FOR_SVR;
3354
3355
3356 PROCEDURE REFRESH_SELECTIVE_NODE_COUNTS(p_resource_id in number, p_node_id_string in varchar2, x_node_id_list OUT NOCOPY varchar2 )
3357
3358 IS
3359
3360 current_node_id number;
3361 j number := 1;
3362 l_counter number := 1;
3363 temp number;
3364 i integer := 0;
3365 p_node_id_list varchar2(4000);
3366 BEGIN
3367
3368 if (length(p_node_id_string)) is not null then
3369
3370 temp := (instr(p_node_id_string, fnd_global.local_chr(20), 1, j));
3371
3372 if temp = 0 then
3373
3374 current_node_id := to_number(p_node_id_string);
3375 IEU_PVT.REFRESH_CUR_NODE_COUNTS(p_resource_id, current_node_id, p_node_id_list);
3376
3377 x_node_id_list := p_node_id_list;
3378
3379 else
3380
3381 while (l_counter < length(p_node_id_string))
3382 loop
3383 i := i + 1;
3384
3385 temp := (instr(p_node_id_string, fnd_global.local_chr(20), 1, j)) - l_counter;
3386
3387 current_node_id := to_number(substr(p_node_id_string, l_counter, temp));
3388
3389 IEU_PVT.REFRESH_CUR_NODE_COUNTS(p_resource_id, current_node_id, p_node_id_list);
3390
3391 x_node_id_list := x_node_id_list||p_node_id_list;
3392
3393 l_counter := (instr(p_node_id_string, fnd_global.local_chr(20),1,j))+1;
3394
3395 j := j +1;
3396 end loop;
3397 end if;
3398
3399 end if;
3400 END;
3401
3402 --just a wrapper to enable calling DETERMINE_ELIGIBLE_MEDIA_TYPES from
3403 --java because jdbc does not allow passing of records
3404 PROCEDURE GET_WB_MEDIA_LOGIN_MEDIA_TYPES(
3405 P_RESOURCE_ID IN NUMBER
3406 ,P_USER_ID IN NUMBER
3407 ,P_RESP_ID IN NUMBER
3408 ,P_RESP_APPL_ID IN NUMBER
3409 ,X_MEDIA_TYPE_NST OUT NOCOPY SYSTEM.IEU_UWQ_MEDIA_TYPE_NST
3410 )
3411 AS
3412 l_media_types EligibleMediaList;
3413 BEGIN
3414
3415 FND_GLOBAL.APPS_INITIALIZE( p_user_id, p_resp_id, p_resp_appl_id );
3416
3417 DETERMINE_ELIGIBLE_MEDIA_TYPES(
3418 P_RESOURCE_ID,
3419 l_media_types );
3420
3421 IF (l_media_types is not null and l_media_types.COUNT > 0)
3422 THEN
3423
3424 X_MEDIA_TYPE_NST := SYSTEM.IEU_UWQ_MEDIA_TYPE_NST();
3425 FOR i IN l_media_types.FIRST..l_media_types.LAST
3426 LOOP
3427 X_MEDIA_TYPE_NST.extend(1);
3428 X_MEDIA_TYPE_NST( X_MEDIA_TYPE_NST.LAST ) :=
3429 SYSTEM.IEU_UWQ_MEDIA_TYPE_OBJ( l_media_types(i).media_type_id,
3430 l_media_types(i).media_type_uuid );
3431 END LOOP;
3432 END IF;
3433
3434 END;
3435
3436 -- PL/SQL Block
3437 END IEU_PVT;
[Home] [Help]