[Home] [Help]
PACKAGE BODY: APPS.SR_UWQ_INTEG
Source
1 PACKAGE BODY SR_UWQ_INTEG AS
2 /* $Header: cssruwqb.pls 120.5.12020000.6 2013/04/22 06:54:30 spamujul ship $ */
3
4 ------------------------------------------------------------------------------
5 -- Parameters :
6 -- p_ieu_media_data IN SYSTEM.IEW_UWQ_MEDIA_DATA_NST Required
7 -- p_action_type OUT NUMBER
8 -- p_action_name OUT VARCHAR2
9 -- p_action_param OUT VARCHAR2
10 --
11 ------------------------------------------------------------------------------
12
13 procedure sr_uwq_foo_func
14 ( p_ieu_media_data IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
15 p_action_type OUT NOCOPY NUMBER,
16 p_action_name OUT NOCOPY VARCHAR2,
17 p_action_param OUT NOCOPY VARCHAR2) IS
18
19 n_ctn number;
20 l_name varchar2(500);
21 l_value varchar2(1996);
22 l_type varchar2(500);
23 l_incident_id number;
24
25 l_sr_uwq_call varchar2(50);
26 l_sr_uwq_parameter varchar2(50);
27 l_sr_uwq_param_value varchar2(50);
28 l_sr_uwq_param_id number;
29 l_sr_uwq_param_message varchar2(150);
30 l_sr_uwq_action varchar2(50);
31 l_sr_uwq_action_value varchar2(30);
32 l_sr_uwq_exp_action varchar2(30);
33 l_sr_uwq_call_type varchar2(50);
34 l_topmost_tab_page varchar2(50);
35
36 -- These parameters will default the customer info on
37 -- the Call to Issue scenario.
38 l_customer_id number;
39 l_customer varchar2(360);
40 l_customer_type varchar2(30);
41
42 -- The variables below are for call transfer
43 n_party_id number;
44 n_cust_party_id number;
45 n_rel_party_id number;
46 n_per_party_id number;
47 n_cust_phone_id number;
48 n_rel_phone_id number;
49 n_cust_account_id number;
50 n_interaction_id number;
51 n_action_id number;
52 n_uwq_ani number;
53 v_service_key_name varchar2(40);
54 v_service_key_value varchar2(2000);
55 v_call_reason varchar2(40);
56
57
58 x_return_status varchar2(10);
59 x_parameter_flag varchar2(20);
60 v_parameter_char varchar2(80);
61
62 BEGIN
63
64 -- p_action_param := 'SR_UWQ_CALL="YES"';
65 -- The below code intializes the parameters that will be passed to
66 -- the Service Request form. These will be decoded and used to control
67 -- the flow of the form.
68
69 n_ctn := 1;
70 l_sr_uwq_call := 'SR_UWQ_CALL="YES"';
71 l_sr_uwq_parameter := 'NO_DATA';
72 l_sr_uwq_param_value := 'NO_DATA';
73 l_sr_uwq_param_message := 'NO_DATA';
74 l_sr_uwq_action := 'NO_DATA';
75 l_sr_uwq_action_value := 'QUERY_SR';
76 l_sr_uwq_exp_action := 'NO_DATA';
77 l_sr_uwq_call_type := 'SR_UWQ_CALL_TYPE="SR_REGULAR"';
78 l_topmost_tab_page := 'NO_DATA';
79
80 n_action_id := 0;
81
82 for i IN 1..p_ieu_media_data.COUNT
83 loop
84 l_name := p_ieu_media_data(i).param_name;
85 l_value := p_ieu_media_data(i).param_value;
86 l_type := p_ieu_media_data(i).param_type;
87
88 -- Process the IVR table entries
89 /*
90 insert into uwq_temp values(l_name, l_value, l_type,n_ctn,sysdate);
91 commit;
92 n_ctn := n_ctn + 1;
93 */
94 IF l_name = 'occtEventName' THEN
95 -- p_action_param := p_action_param||'uwq_event="'||l_value||'"';
96 null;
97 -- Simba
98
99 ELSIF l_name = 'occtAgentID' THEN
100 p_action_param := p_action_param||'uwq_agent="'||l_value||'"';
101
102 ELSIF l_name = 'occtANI' THEN
103 p_action_param := p_action_param||'uwq_ani="'||l_value||'"';
104 n_uwq_ani := l_value;
105
106 ELSIF l_name = 'occtDNIS' THEN
107 p_action_param := p_action_param||'uwq_dnis="'||l_value||'"';
108
109 ELSIF l_name = 'occtMediaItemID' THEN
110 p_action_param := p_action_param||'uwq_media_item_id="'||l_value||'"';
111
112 ELSIF l_name = 'workItemID' THEN
113 p_action_param := p_action_param||'uwq_work_item_id="'||l_value||'"';
114
115 ELSIF l_name = 'occtMediaType' THEN
116 p_action_param := p_action_param||'uwq_media_type="'||l_value||'"';
117
118 ELSIF l_name = 'occtCallID' THEN
119 p_action_param := p_action_param||'uwq_call_id="'||l_value||'"';
120
121 ELSIF l_name = 'occtScreenPopAction' THEN
122 if l_value = 'CreateSR' then
123 l_value := 'CREATE_SR';
124 elsif l_value = 'InquireSR' then
125 l_value := 'QUERY_SR';
126 else
127 l_value := 'QUERY_SR';
128 end if;
129 l_sr_uwq_action := 'SR_UWQ_ACTION="'||l_value||'"';
130 l_sr_uwq_action_value := l_value;
131
132 ELSIF l_name = 'ServiceRequestNum' THEN
133 l_sr_uwq_parameter := 'SR_NUMBER';
134 l_sr_uwq_param_value := l_value;
135
136 ELSIF l_name = 'AccountCode' THEN
137 l_sr_uwq_parameter := 'SR_ACC_NUMBER';
138 l_sr_uwq_param_value := l_value;
139
140 ELSIF l_name = 'SerialNum' THEN
141 l_sr_uwq_parameter := 'SR_SERIAL_NUMBER';
142 l_sr_uwq_param_value := l_value;
143
144 ELSIF l_name = 'TagNumber' THEN
145 l_sr_uwq_parameter := 'SR_TAG_NUMBER';
146 l_sr_uwq_param_value := l_value;
147
148 ELSIF l_name = 'CustomerNum' THEN
149 l_sr_uwq_parameter := 'SR_PARTY_NUMBER';
150 l_sr_uwq_param_value := l_value;
151
152 ELSIF l_name = 'PhoneNumber' THEN
153 l_sr_uwq_parameter := 'SR_PHONE_NUMBER';
154 l_sr_uwq_param_value := l_value;
155
156 ELSIF l_name = 'RMANum' THEN
157 l_sr_uwq_parameter := 'SR_RMA_NUMBER';
158 l_sr_uwq_param_value := l_value;
159
160 ELSIF l_name = 'ContractNum' THEN
161 l_sr_uwq_parameter := 'SR_CONTRACT_NUMBER';
162 l_sr_uwq_param_value := l_value;
163
164 -- The code below are used for transfer between applications
165
166 ELSIF l_name = 'CUST_PARTY_ID' THEN
167 n_cust_party_id := l_value;
168
169 ELSIF l_name = 'REL_PARTY_ID' THEN
170 n_rel_party_id := l_value;
171
172 ELSIF l_name = 'PER_PARTY_ID' THEN
173 n_per_party_id := l_value;
174
175 ELSIF l_name = 'CUST_PHONE_ID' THEN
176 n_cust_phone_id := l_value;
177
178 ELSIF l_name = 'REL_PHONE_ID' THEN
179 n_rel_phone_id := l_value;
180
181 ELSIF l_name = 'CUST_ACCOUNT_ID' THEN
182 n_cust_account_id := l_value;
183
184 ELSIF l_name = 'INTERACTION_ID' THEN
185 n_interaction_id := l_value;
186
187 ELSIF l_name = 'ACTION_ID' THEN
188 n_action_id := l_value;
189
190 ELSIF l_name = 'SERVICE_KEY_NAME' THEN
191 v_service_key_name := l_value;
192
193 ELSIF l_name = 'SERVICE_KEY_VALUE' THEN
194 v_service_key_value := l_value;
195
196 ELSIF l_name = 'CALL_REASON' THEN
197 v_call_reason := l_value;
198
199 end if;
200
201 end loop; -- End of loop of searching through parameters.
202
203 -- The code below handles the transfer and Conf. of calls.
204 -- 63 is Transfer and 64 is Conference.
205
206 if n_action_id in (63,64) then
207
208 if nvl(n_cust_party_id,0) <> 0 then
209 n_party_id := n_cust_party_id;
210 elsif nvl(n_per_party_id,0) <> 0 then
211 n_party_id := n_per_party_id;
212 else
213 n_party_id := -1;
214 end if;
215
216 sr_uwq_integ.interpret_service_keys(v_service_key_name,
217 v_service_key_value,
218 n_party_id,
219 n_cust_account_id,
220 n_cust_phone_id,
221 l_sr_uwq_parameter,
222 x_return_status);
223
224 l_sr_uwq_param_value := v_service_key_value;
225 l_sr_uwq_action_value := 'QUERY_SR';
226 l_sr_uwq_action := 'SR_UWQ_ACTION="'||l_sr_uwq_action_value||'"';
227
228 if n_action_id = 63 then
229 l_sr_uwq_call_type := 'SR_UWQ_CALL_TYPE="SR_TRANSFER"';
230 elsif n_action_id = 64 then
231 l_sr_uwq_call_type := 'SR_UWQ_CALL_TYPE="SR_CONF"';
232 end if;
233
234 end if; /* End of if for n_action_id = 63,64 */
235
236 /* This is a case where the incoming call is from the UWQ and
237 the call did not have any other parameter and we have to use the
238 ANI to get more data. */
239
240 if l_sr_uwq_parameter = 'NO_DATA' then
241 l_sr_uwq_parameter := 'SR_ANI';
242 l_sr_uwq_param_value := n_uwq_ani;
243
244 elsif l_sr_uwq_parameter is not null
245 and ltrim(rtrim(l_sr_uwq_param_value)) is null then
246 l_sr_uwq_parameter := 'SR_ANI';
247 l_sr_uwq_param_value := n_uwq_ani;
248
249 end if;
250
251 -- The parameter SR_UWQ_CALL is set to YES here.
252 p_action_param := p_action_param||l_sr_uwq_call;
253
257 l_sr_uwq_param_message,
254 sr_uwq_integ.validate_ivr_parameter(
255 l_sr_uwq_parameter,
256 l_sr_uwq_param_value,
258 l_sr_uwq_action_value,
259 l_sr_uwq_param_id,
260 x_parameter_flag,
261 l_customer_id,
262 l_customer_type,
263 x_return_status);
264
265 if l_sr_uwq_parameter = 'SR_NUMBER' and l_sr_uwq_param_id <> -1 then
266 p_action_param := p_action_param||'REQUEST_NUMBER="'||l_sr_uwq_param_value||'"';
267 p_action_param := p_action_param||'SR_UWQ_PARAM_VALUE="'||l_sr_uwq_param_value||'"';
268 else
269 p_action_param := p_action_param||'SR_UWQ_PARAM_VALUE="'||l_sr_uwq_param_value||'"';
270 end if;
271
272 p_action_type := 1; -- This means app_navigate
273
274 -- This section of the code decides on what form to call. Depending on the
275 -- Action type different forms are popped up. SR_UWQ_ACTION
276 -- Call To Issue ----> CSXSRISR, Call to Inquiry ---> CSXSRISR, and CSXSRISV.
277
278 if nvl(l_sr_uwq_action_value,'QUERY_SR') = 'QUERY_SR' then
279 if l_sr_uwq_parameter = 'SR_NUMBER' and l_sr_uwq_param_id <> -1 then
280 p_action_name := 'CSXSRISR';
281 else
282 p_action_name := 'CSXSRISV';
283 end if;
284
285 elsif l_sr_uwq_action_value = 'CREATE_SR' then
286 p_action_name := 'CSXSRISR';
287
288 else
289 p_action_name := 'CSXSRISR';
290 end if;
291
292 -- The parameter SR_UWQ_ACTION is set here. This will be used by the
293 -- Lib CSSRUWQ.pll to navigate and default values.
294
295 if l_sr_uwq_action = 'NO_DATA' then
296 l_sr_uwq_action := 'SR_UWQ_ACTION="'||l_sr_uwq_action_value||'"';
297 end if;
298 p_action_param := p_action_param||l_sr_uwq_action;
299
300
301 -- This section of the code appends the IVR Value and the ID of the value
302 -- to the parameter list for the form to process. Output of the function to validate
303 -- the IVR parameter.
304
305 if x_return_status = 'S' then
306 p_action_param := p_action_param||'SR_UWQ_PARAMETER="'||l_sr_uwq_parameter||'"';
307 p_action_param := p_action_param||'SR_UWQ_PARAM_ID="'||l_sr_uwq_param_id||'"';
308 end if;
309
310 -- This section of code adds the Customer related info to the parameter
311 -- string. This would happen only when the customer info is retriveable for
312 -- the passed IVR parameter.
313 if x_parameter_flag = 'VALID_CUSTOMER' then
314 if l_customer_id is not null then
315 p_action_param := p_action_param||'SR_UWQ_CUST_ID="'||l_customer_id||'"';
316 end if;
317
318 if l_customer_type is not null then
319 p_action_param := p_action_param||'SR_UWQ_CUST_TYPE="'||l_customer_type||'"';
320 end if;
321 else
322 p_action_param := p_action_param||'SR_UWQ_CUST_ID="-1"';
323
324 end if;
325
326 -- The parameter SR_UWQ_CALL_TYPE is set here. This will be used to identify if
327 -- it is a Transfer, or Conference, or Regular Queue call
328 -- For outbound calls it is set to SR_OUTBOUND
329 p_action_param := p_action_param||l_sr_uwq_call_type;
330
331 -- The parameter SR_UWQ_PARAM_MESSAGE is set here. This will be used to pass any extra
332 -- info from the foo function to the form.
333 if l_sr_uwq_param_message <> 'NO_DATA' then
334 p_action_param := p_action_param||'SR_UWQ_PARAM_MESSAGE="'||l_sr_uwq_param_message||'"';
335 end if;
336
337 -- This parameter will make override the Cancel, Save, Discard message and force the
338 -- the SR UWQ Alert pop up window to come up. This is used in the Restart scenario.
339 p_action_param := p_action_param||'FIRE_CLEAR_FORM="N"';
340
341 end sr_uwq_foo_func;
342
343 procedure connect_form_to_foo
344 ( p_ieu_media_data in IEU_FRM_PVT.t_ieu_media_data,
345 p_action_type out NOCOPY number,
346 p_action_name out NOCOPY varchar2,
347 p_action_param out NOCOPY varchar2) is
348
349 api_ivr_param_list system.IEU_UWQ_MEDIA_DATA_NST;
350
351 begin
352
353 api_ivr_param_list := system.IEU_UWQ_MEDIA_DATA_NST();
354
355 for i IN p_ieu_media_data.first..p_ieu_media_data.last
356 loop
357 api_ivr_param_list.extend;
358 api_ivr_param_list(api_ivr_param_list.LAST) := SYSTEM.IEU_UWQ_MEDIA_DATA_OBJ(p_ieu_media_data(i).param_name,
359 p_ieu_media_data(i).param_value,
360 p_ieu_media_data(i).param_type);
361
362 end loop;
363
364 sr_uwq_integ.sr_uwq_foo_func(api_ivr_param_list,
365 p_action_type, p_action_name, p_action_param);
366
367 end connect_form_to_foo;
368
369 /*======================================================================+
370 ==
371 == Procedure name :enumerate_sr_nodes
372 == Modification History:
373 ==
374 == Date Name Desc
375 == ---------- --------- ---------------------------------------------
376 == 07-dec-2004 VARNARAY Made changes for Bug 3818940.
377 == 29-SEP-2005 PRAYADUR Fix for Bug 4434093 added.
378 ========================================================================*/
379 procedure enumerate_sr_nodes
380 (p_resource_id in number,
381 p_language in varchar2,
382 p_source_lang in varchar2,
383 p_sel_enum_id in number) as
384
385 l_node_label varchar2(100);
386 l_sr_list IEU_PUB.EnumeratorDataRecordList;
387 l_bind_list IEU_PUB.BindVariableRecordList;
388 l_node_counter number;
389
390 l_team_id number;
391 l_team_name varchar2(30);
392 l_group_id number;
393 l_group_name varchar2(60);
394 l_where_clause varchar2(500);
395 l_parent_where_clause varchar2(500);
396 l_sr_name varchar2(30);
397 return_value varchar2(2000);
398
399 l_view_name varchar2(50);
400 l_data_source varchar2(50);
401 n_where_clause varchar2(500);
402 l_cursor_sql varchar2(1500);
403 l_node_id number;
404 l_id_of_value number;
405 l_parent_id number;
406 l_where_value varchar2(500);
407 l_cursor_key_col varchar2(30);
408 l_value_flag varchar2(10);
409 l_node_query varchar2(10);
410
411 cursor team_cursor is select distinct team_mem.team_id,team_tl.team_name
412 from jtf_rs_team_members team_mem, jtf_rs_teams_tl team_tl
413 where team_resource_id = p_resource_id
414 and team_mem.team_id = team_tl.team_id
415 and team_tl.language = userenv('LANG');
416
417 cursor group_cursor is select distinct group_mem.group_id,group_tl.group_name
418 from jtf_rs_group_members group_mem, jtf_rs_groups_tl group_tl
419 where group_mem.resource_id = p_resource_id
420 and group_mem.group_id = group_tl.group_id
421 and group_tl.language = userenv('LANG');
422
423 l_lookup_code varchar2(30);
424 l_meaning varchar2(360);--5579863
425 l_level number;
426 l_res_cat_enum_flag varchar2(1);
427
428 cursor all_cursor is select node_label,node_view,cursor_key_col,cursor_sql,
429 data_source
430 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
431 where uwq_b.node_id = uwq_tl.node_id
432 and uwq_tl.language = userenv('LANG')
433 and parent_id = l_node_id
434 and node_query='CURSOR' and enabled_flag='Y';
435
436 cursor node_cursor is select 'Node ',
437 node_view,where_clause,data_source,level,node_query,cursor_sql,
438 uwq_b.node_id,res_cat_enum_flag
439 from cs_sr_uwq_nodes_b uwq_b
440 where node_id > 9999 and node_query='SINGLE' and enabled_flag='Y'
441 and ( parent_id is null or parent_id > 9999 )
442 start with uwq_b.parent_id is null
443 connect by prior uwq_b.node_id = uwq_b.parent_id;
444
445 cursor seed_cursor is select 'Node',
446 node_view,where_clause,data_source,node_query,cursor_sql,
447 uwq_b.node_id,res_cat_enum_flag,level,nvl(parent_id,-1)
448 from cs_sr_uwq_nodes_b uwq_b
449 where enabled_flag='Y'
450 --where node_id < 1000 and enabled_flag='Y'
451 and node_query = 'SINGLE'
452 start with uwq_b.parent_id is null
453 connect by prior uwq_b.node_id = uwq_b.parent_id;
454
455 cursor cur_seed_cursor is select node_label,
456 node_view,where_clause,data_source,node_query,cursor_sql,
457 cursor_key_col,node_id,res_cat_enum_flag
458 from cs_sr_uwq_nodes_vl
459 where node_id < 1000 and enabled_flag='Y'
460 and node_query = 'CURSOR';
461
462 v_cursorid number;
463 v_dummy number;
464
465 begin
466
467 begin
468 select name into l_sr_name from jtf_objects_vl
469 where object_code='SR';
470 exception
471 when OTHERS then
472 l_sr_name := 'SR Error';
473 end;
474
475 l_node_counter := 0;
476 savepoint start_cs_enum;
477
478 l_sr_list(l_node_counter).node_label := l_sr_name;
479 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_LABEL_V';
480 l_sr_list(l_node_counter).data_source := 'CS_SR_UWQ_LABEL_DS';
481 l_sr_list(l_node_counter).media_type_id := '';
482 l_sr_list(l_node_counter).where_clause := ' incident_id = -1 ';
483 l_sr_list(l_node_counter).node_type := 0;
484 l_sr_list(l_node_counter).hide_if_empty := '';
485 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
486 l_sr_list(l_node_counter).node_depth := 1;
487
488 /* The setting of res_cat_enum_flag='N' makes sure that the default
489 where condition in UWQ is not fired. Instead the where condition
490 defined by l_where_clause is fired */
491
492 -- Creation of My Node. All SR assigned directly to the Resource.
493
494 open seed_cursor;
495
496 loop
497 fetch seed_cursor into l_node_label,l_view_name,l_where_clause,l_data_source,
498 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag,l_level,l_parent_id;
499
500 exit when seed_cursor%NOTFOUND;
501
502 if l_node_id < 1000 or (l_parent_id < 1000 and l_parent_id > 0) then
503 -- This means that the node is a seeded node or a child node of
504 -- another seeded node.
505
506 l_node_counter := l_node_counter + 1;
507
508 begin
509 select node_label into l_node_label from cs_sr_uwq_nodes_tl
510 where node_id=l_node_id and language=userenv('LANG');
511 exception
512 when NO_DATA_FOUND then
513 l_node_label:='Error: No Label ';
514 end;
515
516 l_bind_list(1).bind_var_name := ':owner_id';
517 l_bind_list(1).bind_var_value := p_resource_id;
518 l_bind_list(1).bind_var_data_type :='NUMBER';
519
520 if (l_parent_id < 1000 and l_parent_id > 0) then
521 -- This means that the node is a child node and its parent
522 -- is a seeded node. So the child node will inherit the where
523 -- condition of the parent.
524
525 begin
526 l_parent_where_clause := null;
527 select where_clause into l_parent_where_clause from cs_sr_uwq_nodes_b
528 where node_id=l_parent_id ;
529 exception
530 when NO_DATA_FOUND then
531 l_parent_where_clause :=' incident_id = -1 ';
532 end;
533 if l_where_clause is not null then
534 l_where_clause := l_parent_where_clause ||' AND '||l_where_clause;
535 else
536 l_where_clause := l_parent_where_clause ;
537 end if;
538
539 end if; -- End of if at l_parent_id
540
541 l_sr_list(l_node_counter).node_label := l_node_label;
542 l_sr_list(l_node_counter).view_name := l_view_name;
543 l_sr_list(l_node_counter).data_source := l_data_source;
544 l_sr_list(l_node_counter).media_type_id := '';
545 l_sr_list(l_node_counter).where_clause := l_where_clause;
546 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
547 l_sr_list(l_node_counter).node_type := 10;
548 l_sr_list(l_node_counter).hide_if_empty := '';
549 l_sr_list(l_node_counter).node_depth := l_level+1;
550 return_value := ieu_pub.set_bind_var_data(l_bind_list);
551 l_sr_list(l_node_counter).bind_vars := return_value;
552
553 begin
554 -- For Each of the SINGLE defined nodes first check if there
555 -- exist any CURSOR defined child nodes. If so execute the
556 -- SQL and build the child nodes.
557
558 select node_label,node_view,cursor_key_col,data_source,
559 node_query,cursor_sql,uwq_b.node_id,res_cat_enum_flag
560 into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
561 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag
562 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
563 where uwq_b.node_id = uwq_tl.node_id
564 and uwq_tl.language = userenv('LANG')
565 and parent_id = l_node_id and enabled_flag='Y'
566 and node_query = 'CURSOR' ;
567
568 if l_cursor_sql is not null then
569
570 l_node_counter := l_node_counter + 1;
571 l_sr_list(l_node_counter).node_label := l_node_label;
572 l_sr_list(l_node_counter).view_name := l_view_name;
573 l_sr_list(l_node_counter).data_source := l_data_source;
574 l_sr_list(l_node_counter).media_type_id := '';
575 l_sr_list(l_node_counter).where_clause := ' incident_id = -101 ';
576 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
577 l_sr_list(l_node_counter).node_type := 10;
578 l_sr_list(l_node_counter).hide_if_empty := '';
579 l_sr_list(l_node_counter).node_depth := l_level+2;
580
581 v_cursorid := dbms_sql.open_cursor;
582 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
583 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
584 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
585 v_dummy := dbms_sql.execute(v_cursorid);
586
587 loop
588 if dbms_sql.fetch_rows(v_cursorid) = 0 then
589 exit;
590 end if;
591
592 dbms_sql.column_value(v_cursorid, 1, l_meaning);
593 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
594 l_where_clause := l_cursor_key_col||' = :seedsubbindvalue';
595 l_node_counter := l_node_counter + 1;
596
597 l_bind_list(1).bind_var_name := ':seedsubbindvalue';
598 l_bind_list(1).bind_var_value := l_id_of_value;
599 l_bind_list(1).bind_var_data_type :='NUMBER';
600
601 l_sr_list(l_node_counter).node_label := l_meaning;
602 l_sr_list(l_node_counter).view_name := l_view_name;
603 l_sr_list(l_node_counter).data_source := l_data_source;
604 l_sr_list(l_node_counter).media_type_id := '';
605 l_sr_list(l_node_counter).where_clause := l_where_clause;
606 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
607
608 l_sr_list(l_node_counter).node_type := 12;
609
610 l_sr_list(l_node_counter).hide_if_empty := '';
611 l_sr_list(l_node_counter).node_depth := l_level+3;
612
613 return_value := ieu_pub.set_bind_var_data(l_bind_list);
614 l_sr_list(l_node_counter).bind_vars := return_value;
615
616 end loop;
617
618 dbms_sql.close_cursor(v_cursorid);
619
620 end if;
621
622 exception
623 when NO_DATA_FOUND then
624 null;
625 end; -- End of Begin at CURSOR based sub Nodes.
626
627 end if; --- End of check for node id and parent id.
628
629 end loop; --- End of main loop for seed cursor
630
631 close seed_cursor;
632
633 -- Creation of Team Node. All SR assigned directly to the Team(s) of the Resource.
634 -- The first node in the Team Node is a dummy
635
636 open cur_seed_cursor;
637
638 loop
639 fetch cur_seed_cursor into l_node_label,l_view_name,
640 l_where_clause,l_data_source,
641 l_node_query,l_cursor_sql,l_cursor_key_col,
642 l_node_id,l_res_cat_enum_flag;
643
644 exit when cur_seed_cursor%NOTFOUND;
645
646 if l_cursor_sql is not null then
647
648 l_bind_list(1).bind_var_name := ':owner_id';
649 l_bind_list(1).bind_var_value := p_resource_id;
650 l_bind_list(1).bind_var_data_type :='NUMBER';
651
652 l_node_counter := l_node_counter + 1;
653 l_sr_list(l_node_counter).node_label := l_node_label;
654 l_sr_list(l_node_counter).view_name := l_view_name;
655 l_sr_list(l_node_counter).data_source := l_data_source;
656 l_sr_list(l_node_counter).media_type_id := '';
657 l_sr_list(l_node_counter).where_clause := l_where_clause;
658 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
659 l_sr_list(l_node_counter).node_type := 10;
660 l_sr_list(l_node_counter).hide_if_empty := '';
661 l_sr_list(l_node_counter).node_depth := 2;
662 return_value := ieu_pub.set_bind_var_data(l_bind_list);
663 l_sr_list(l_node_counter).bind_vars := return_value;
664
665 v_cursorid := dbms_sql.open_cursor;
666 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
667 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
668 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
669 dbms_sql.bind_variable(v_cursorid, ':OWNER_ID', p_resource_id);
670 v_dummy := dbms_sql.execute(v_cursorid);
671
672 loop
673 if dbms_sql.fetch_rows(v_cursorid) = 0 then
674 exit;
675 end if;
676 dbms_sql.column_value(v_cursorid, 1, l_meaning);
677 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
678 l_where_clause := l_cursor_key_col||' = :bindvalue';
679 l_node_counter := l_node_counter + 1;
680
681 l_bind_list(1).bind_var_name := ':bindvalue';
682 l_bind_list(1).bind_var_value := l_id_of_value;
683 l_bind_list(1).bind_var_data_type :='NUMBER';
684
685 l_sr_list(l_node_counter).node_label := l_meaning;
686 l_sr_list(l_node_counter).view_name := l_view_name;
687 l_sr_list(l_node_counter).data_source := l_data_source;
688 l_sr_list(l_node_counter).media_type_id := '';
689 l_sr_list(l_node_counter).where_clause := l_where_clause;
690 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
691 l_sr_list(l_node_counter).node_type := 12;
692 l_sr_list(l_node_counter).hide_if_empty := '';
693 l_sr_list(l_node_counter).node_depth := 3;
694
695 return_value := ieu_pub.set_bind_var_data(l_bind_list);
696 l_sr_list(l_node_counter).bind_vars := return_value;
697 end loop;
698
699 dbms_sql.close_cursor(v_cursorid);
700
701 end if; -- End of if at cursor_sql is not null
702 end loop; -- End of loop for cur_seed_cursor
703
704 --- Start of personalized single,cursor nodes.
705 -- This section of code queries the table cs_sr_uwq_nodes_b / tl for all
706 -- personalized nodes.
707
708 open node_cursor;
709 loop
710
711 fetch node_cursor into l_node_label,
712 l_view_name,n_where_clause,l_data_source,l_level,
713 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag;
714 exit when node_cursor%notfound;
715
716 l_node_counter := l_node_counter + 1;
717
718 begin
719 select node_label into l_node_label from cs_sr_uwq_nodes_tl
720 where node_id=l_node_id and language=userenv('LANG');
721 exception
722 when NO_DATA_FOUND then
723 l_node_label:='Error: No Label ';
724 end;
725
726 l_sr_list(l_node_counter).node_label := l_node_label;
727 l_sr_list(l_node_counter).view_name := l_view_name;
728 l_sr_list(l_node_counter).data_source := l_data_source;
729 l_sr_list(l_node_counter).media_type_id := '';
730 l_sr_list(l_node_counter).where_clause := n_where_clause;
731 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
732 l_sr_list(l_node_counter).node_type := 10;
733 l_sr_list(l_node_counter).hide_if_empty := '';
734 l_sr_list(l_node_counter).node_depth := l_level+1;
735
736 if instr(lower(n_where_clause), ':owner_id') <> 0 then
737 l_bind_list(1).bind_var_name := ':owner_id';
738 l_bind_list(1).bind_var_value := p_resource_id;
739 l_bind_list(1).bind_var_data_type :='NUMBER';
740 return_value := ieu_pub.set_bind_var_data(l_bind_list);
741 l_sr_list(l_node_counter).bind_vars := return_value;
742 end if;
743
744 begin
745 -- For Each of the SINGLE defined nodes first check if there
746 -- exist any CURSOR defined child nodes. If so execute the
747 -- SQL and build the child nodes.
748 select node_label,node_view,cursor_key_col,data_source,
749 node_query,cursor_sql,uwq_b.node_id
750 , res_cat_enum_flag
751 into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
752 l_node_query,l_cursor_sql,l_node_id
753 , l_res_cat_enum_flag
754 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
755 where uwq_b.node_id = uwq_tl.node_id
756 and uwq_tl.language = userenv('LANG')
757 and parent_id = l_node_id and enabled_flag='Y'
758 and node_query = 'CURSOR' ;
759
760 if l_cursor_sql is not null then
761
762 l_node_counter := l_node_counter + 1;
763 l_sr_list(l_node_counter).node_label := l_node_label;
764 l_sr_list(l_node_counter).view_name := l_view_name;
765 l_sr_list(l_node_counter).data_source := l_data_source;
766 l_sr_list(l_node_counter).media_type_id := '';
767 l_sr_list(l_node_counter).where_clause := ' incident_id = -1 ';
768 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
769 l_sr_list(l_node_counter).node_type := 10;
770 l_sr_list(l_node_counter).hide_if_empty := '';
771 l_sr_list(l_node_counter).node_depth := l_level+2;
772
773 v_cursorid := dbms_sql.open_cursor;
774 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
775 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
776 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
777 v_dummy := dbms_sql.execute(v_cursorid);
778
779 loop
780 if dbms_sql.fetch_rows(v_cursorid) = 0 then
781 exit;
782 end if;
783
784 dbms_sql.column_value(v_cursorid, 1, l_meaning);
785 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
786 l_where_clause := l_cursor_key_col||' = :customsubbindvalue';
787 l_node_counter := l_node_counter + 1;
788
789 l_bind_list(1).bind_var_name := ':customsubbindvalue';
790 l_bind_list(1).bind_var_value := l_id_of_value;
791 l_bind_list(1).bind_var_data_type :='NUMBER';
792
793 l_sr_list(l_node_counter).node_label := l_meaning;
794 l_sr_list(l_node_counter).view_name := l_view_name;
795 l_sr_list(l_node_counter).data_source := l_data_source;
796 l_sr_list(l_node_counter).media_type_id := '';
797 l_sr_list(l_node_counter).where_clause := l_where_clause;
798 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
799 l_sr_list(l_node_counter).node_type := 12;
800 l_sr_list(l_node_counter).hide_if_empty := '';
801 l_sr_list(l_node_counter).node_depth := l_level+3;
802
803 return_value := ieu_pub.set_bind_var_data(l_bind_list);
804 l_sr_list(l_node_counter).bind_vars := return_value;
805 end loop;
806
807 dbms_sql.close_cursor(v_cursorid);
808
809 end if;
810
811 exception
812 when NO_DATA_FOUND then
813 null;
814 end; -- End of Begin at CURSOR based sub Nodes.
815
816 end loop;
817 close node_cursor;
818
819 ieu_pub.add_uwq_node_data
820 (p_resource_id,
821 p_sel_enum_id,
822 l_sr_list );
823
824 exception
825 when OTHERS then
826
827 --prayadur 29-Sep-05 Commented the code below and added
828 --the following 2 lines for Bug 4434093.
829 -- l_where_clause := sqlerrm;
830 ROLLBACK TO start_cs_enum;
831 RAISE;
832
833 end enumerate_sr_nodes;
834
835 procedure refresh_sr_nodes
836 (p_resource_id in number,
837 p_node_id in number,
838 p_count out NOCOPY number) is
839
840 sr_count number;
841 n_count number;
842 l_node_type number;
843 l_node_count_view varchar2(50);
844
845 l_node_detail_record IEU_PUB.NodeDetailRecord;
846 s_sql_statement varchar2(4000);
847 s_unbound_stat varchar2(4000);
848
849 begin
850
851 /* Count refresh is done W.R.T. the type of the Node. The root node
852 is of the type '0'. All seeded nodes are of type '10'. All run time
853 generated nodes are of type '12'.
854
855 Service Request Type 0
856 |
857 ---My Service Request Type 10
858 |
859 ---My Groups Type 10
860 |
861 ----Group 1 Type 12
862 |
863 ----Group 2 Type 12
864 |
865 ---My Teams Type 10
866 |
867 ----Team 1 Type 12
868 |
869 ----Team 2 Type 12
870 |
871 ---Group Owned Type 10
872 |
873 ---Team Owned Type 10
874
875 For the Refresh function logic all Seeded and Root nodes will use the
876 new Count views. Since the runtime nodes can be set to different
877 where clauses and may use the columns from the regular view we
878 cannot use the count view as they are just a subset.
879 */
880
881 sr_count := 0;
882
883 IEU_PUB.GET_UWQ_NODE_DETAILS(p_resource_id, p_node_id, l_node_detail_record);
884
885 if l_node_detail_record.node_type = 0 then
886
887 s_sql_statement := ' begin select count(1) into :n_count from cs_sr_uwq_emp_count_v where resource_id = :owner_id; end; ';
888
889 execute immediate s_sql_statement
890 using out n_count, in p_resource_id;
891
892 sr_count := sr_count + n_count;
893
894 select count(1) into n_count
895 from cs_sr_uwq_group_count_v
896 where resource_id in ( select distinct group_id from jtf_rs_group_members a
897 where a.resource_id = p_resource_id
898 and a.resource_id = p_resource_id
899 and a.resource_id is not null
900 and nvl(a.delete_flag,'N') <> 'Y')
901 and resource_type='RS_GROUP'
902 and (owner_id is null or owner_id <> p_resource_id);
903
904 sr_count := sr_count + n_count;
905
906 select count(1) into n_count
907 from cs_sr_uwq_team_count_v
908 where resource_id in ( select distinct team_id from jtf_rs_team_members a
909 where a.team_resource_id = p_resource_id
910 and a.team_resource_id = p_resource_id
911 and a.team_resource_id is not null
912 and nvl(a.delete_flag,'N') <> 'Y')
913 and resource_type='RS_TEAM'
914 and (owner_id is null or owner_id <> p_resource_id);
915
916 sr_count := sr_count + n_count;
917
918 elsif l_node_detail_record.node_type = 10 then
919
920 if l_node_detail_record.view_name = 'CS_SR_UWQ_EMPLOYEE_V' then
921 l_node_count_view := 'CS_SR_UWQ_EMP_COUNT_V';
922 elsif l_node_detail_record.view_name = 'CS_SR_UWQ_GROUP_V' then
923 l_node_count_view := 'CS_SR_UWQ_GROUP_COUNT_V';
924 elsif l_node_detail_record.view_name = 'CS_SR_UWQ_TEAM_V' then
925 l_node_count_view := 'CS_SR_UWQ_TEAM_COUNT_V';
926 else
927 l_node_count_view := l_node_detail_record.view_name;
928 end if;
929
930 s_sql_statement := ' begin select count(1) into :n_count from '||l_node_count_view||' where '||l_node_detail_record.complete_where_clause||' ; end;';
931 select replace(s_sql_statement, to_char(p_resource_id),':OWNER_ID') into s_unbound_stat from dual;
932
933 execute immediate s_unbound_stat
934 using out n_count, in p_resource_id;
935 sr_count := n_count;
936
937 elsif l_node_detail_record.node_type = 12 then
938
939 s_sql_statement := ' begin select count(1) into :n_count from '||l_node_detail_record.view_name||' where '||l_node_detail_record.complete_where_clause||' ; end;';
940
941 execute immediate s_sql_statement
942 using out n_count;
943 sr_count := n_count;
944 else
945 sr_count := -1;
946
947 end if; /* end of if at node_type */
948
949 p_count := sr_count;
950 end refresh_sr_nodes;
951
952 procedure insert_row(
953 p_node_id in number,
954 p_node_view in varchar2,
955 p_node_label in varchar2,
956 p_data_source in varchar2,
957 p_media_type_id in number,
958 p_where_clause in varchar2,
959 p_res_cat_enum_flag in varchar2,
960 p_node_type in varchar2,
961 p_hide_if_empty in varchar2,
962 p_node_depth in number,
963 p_parent_id in number,
964 p_node_query in varchar2,
965 p_cursor_sql in varchar2,
966 p_cursor_key_col in varchar2,
967 p_enabled_flag in varchar2,
968 p_creation_date in date,
969 p_created_by in number,
970 p_last_update_date in date,
971 p_last_updated_by in number,
972 p_last_update_login in number,
973 x_node_id out NOCOPY number,
974 x_return_status out NOCOPY varchar2) is
975
976 l_node_id number;
977 l_return_status varchar2(10);
978
979 begin
980
981 l_node_id :=0 ;
982 l_return_status := 'S';
983 if p_node_id is null OR p_node_id = -1 then
984 select cs_sr_uwq_nodes_s.nextval into l_node_id from dual;
985 else
986 l_node_id := p_node_id;
987 end if;
988
989 insert into cs_sr_uwq_nodes_b
990 (node_id,
991 node_view,
992 data_source,
993 media_type_id,
994 where_clause,
995 res_cat_enum_flag,
996 node_type,
997 hide_if_empty,
998 node_depth,
999 parent_id,
1000 node_query,
1001 cursor_sql,
1002 cursor_key_col,
1003 enabled_flag,
1004 creation_date,
1005 created_by,
1006 last_update_date,
1007 last_updated_by,
1008 last_update_login,
1009 object_version_number)
1010 values
1011 (l_node_id,
1012 p_node_view,
1013 p_data_source,
1014 p_media_type_id,
1015 p_where_clause,
1016 p_res_cat_enum_flag,
1017 p_node_type,
1018 p_hide_if_empty,
1019 p_node_depth,
1020 p_parent_id,
1021 p_node_query,
1022 p_cursor_sql,
1023 p_cursor_key_col,
1024 p_enabled_flag,
1025 p_creation_date,
1026 p_created_by,
1027 p_last_update_date,
1028 p_last_updated_by,
1029 p_last_update_login,
1030 1);
1031
1032 insert into cs_sr_uwq_nodes_tl
1033 (node_id,
1034 node_label,
1035 creation_date,
1036 created_by,
1037 last_update_date,
1038 last_updated_by,
1039 last_update_login,
1040 language,
1041 source_lang)
1042 select
1043 l_node_id,
1044 p_node_label,
1045 p_creation_date,
1046 p_created_by,
1047 p_last_update_date,
1048 p_last_updated_by,
1049 p_last_update_login,
1050 l.language_code,
1051 userenv('LANG')
1052 from fnd_languages l
1053 where l.installed_flag in ('I','B');
1054
1055 x_node_id := l_node_id;
1056 x_return_status := l_return_status;
1057
1058 end insert_row;
1059
1060 procedure update_row(
1061 p_node_id in number,
1062 p_object_version_number in number,
1063 p_node_view in varchar2,
1064 p_node_label in varchar2,
1065 p_data_source in varchar2,
1066 p_media_type_id in number,
1067 p_where_clause in varchar2,
1068 p_res_cat_enum_flag in varchar2,
1069 p_node_type in varchar2,
1070 p_hide_if_empty in varchar2,
1071 p_node_depth in number,
1072 p_parent_id in number,
1073 p_node_query in varchar2,
1074 p_cursor_sql in varchar2,
1075 p_cursor_key_col in varchar2,
1076 p_enabled_flag in varchar2,
1077 p_creation_date in date,
1078 p_created_by in number,
1079 p_last_update_date in date,
1080 p_last_updated_by in number,
1081 p_last_update_login in number,
1082 x_return_status out NOCOPY varchar2) is
1083
1084 l_object_version_number number :=0 ;
1085
1086 begin
1087 l_object_version_number := p_object_version_number;
1088
1089 select object_version_number into l_object_version_number
1090 from cs_sr_uwq_nodes_b where node_id = p_node_id;
1091
1092 if l_object_version_number = p_object_version_number then
1093
1094 update cs_sr_uwq_nodes_b set
1095 node_view = p_node_view,
1096 data_source = p_data_source,
1097 media_type_id = p_media_type_id,
1098 where_clause = p_where_clause,
1099 res_cat_enum_flag = p_res_cat_enum_flag,
1100 node_type = p_node_type,
1101 hide_if_empty = p_hide_if_empty,
1102 node_depth = p_node_depth,
1103 parent_id = p_parent_id,
1104 node_query = p_node_query,
1105 cursor_sql = p_cursor_sql,
1106 cursor_key_col = p_cursor_key_col,
1107 enabled_flag = p_enabled_flag,
1108 creation_date = p_creation_date,
1109 created_by = p_created_by,
1110 last_update_date = p_last_update_date,
1111 last_updated_by = p_last_updated_by,
1112 last_update_login = p_last_update_login,
1113 object_version_number = p_object_version_number + 1
1114 where node_id = p_node_id;
1115
1116 update cs_sr_uwq_nodes_tl set
1117 node_label = p_node_label,
1118 creation_date = p_creation_date,
1119 created_by = p_created_by,
1120 last_update_date = p_last_update_date,
1121 last_updated_by = p_last_updated_by,
1122 last_update_login = p_last_update_login
1123 where node_id = p_node_id
1124 and userenv('LANG') in (language, source_lang);
1125
1126 end if;
1127
1128 end update_row;
1129
1130 procedure validate_ivr_parameter(
1131 p_parameter_code in out NOCOPY varchar2,
1132 p_parameter_value in out NOCOPY varchar2,
1133 p_parameter_mesg in out NOCOPY varchar2,
1134 p_param_action_val in out NOCOPY varchar2,
1135 x_parameter_id out NOCOPY number,
1136 x_parameter_flag out NOCOPY varchar2,
1137 x_customer_id out NOCOPY number,
1138 x_customer_type out NOCOPY varchar2,
1139 x_return_status out NOCOPY varchar2) is
1140
1141 l_parameter_id number;
1142 v_transposed_phone_number varchar2(60);
1143 v_phone_number varchar2(60);
1144 v_sql_statement varchar2(500);
1145 v_parameter_value_temp varchar2(100);
1146 n_rec_count number;
1147 v_cust_number_temp varchar2(60);
1148
1149 -- Validate the Incident number.
1150 cursor inc_cursor is select incident_id from
1151 cs_incidents_all_b where incident_number = p_parameter_value;
1152
1153 -- Validate the RMA Number
1154 -- This cursor will get the Inc. Number associated to
1155 -- the RMA Number. If not found it just passes the RMA Number.
1156 cursor rma_cursor is
1157 select inc.incident_id,inc.incident_number
1158 from oe_order_headers_all oe,
1159 cs_estimate_details chg,
1160 cs_incidents_all_b inc
1161 where oe.order_number = p_parameter_value
1162 and oe.order_category_code in ('RETURN','MIXED')
1163 and oe.header_id = chg.order_header_id
1164 and chg.incident_id = inc.incident_id;
1165
1166 -- Validate the Tag Number
1167 -- The validation is done against CSI Schema only. The
1168 -- non-validated Tag number in CS_INCIDENTS_ALL_B cannot
1169 -- be passed as parameter.
1170 cursor tag_cursor is
1171 select item.instance_id, item.owner_party_id,
1172 hzp.party_type
1173 from csi_item_instances item, hz_parties hzp
1174 where item.external_reference = p_parameter_value
1175 and hzp.party_id = item.owner_party_id;
1176
1177 -- Validate the Serial Number
1178 cursor serial_cursor is
1179 select item.instance_id, item.owner_party_id,
1180 hzp.party_type
1181 from csi_item_instances item, hz_parties hzp
1182 where item.serial_number = p_parameter_value
1183 and hzp.party_id = item.owner_party_id;
1184
1185 -- Validate the Contract number.
1186 -- When a Contract number is passed as an IVR it is converted
1187 -- to the Party Id. All Open SR for that party is queried.
1188 cursor contract_cursor is
1189 select oks.contract_id, oks.party_id,hzp.party_type
1190 from oks_ent_hdr_summary_v oks, hz_parties hzp
1191 where oks.contract_number = p_parameter_value
1192 and oks.party_id = hzp.party_id
1193 and oks.start_date_active <= sysdate
1194 order by oks.start_date_active DESC;
1195
1199 party.party_type
1196 --Validate the Account Number.
1197 cursor account_cursor is
1198 select acc.cust_account_id,acc.party_id,
1200 from hz_cust_accounts acc, hz_parties party
1201 where acc.account_number = p_parameter_value
1202 and acc.status = 'A'
1203 and party.party_id = acc.party_id;
1204
1205 --Validate the Party Number.
1206 cursor party_cursor is
1207 select party.party_id,party.party_id,
1208 party.party_type
1209 from hz_parties party
1210 where party_number = p_parameter_value;
1211
1212 --Validate the Phone Number
1213
1214 cursor phone_cursor is
1215 select cont.contact_point_id,cont.phone,
1216 party.party_type
1217 from cs_sr_hz_cust_cont_v party, cs_sr_hz_cont_pts_p_phones_v cont
1218 where cont.transposed_phone_number = v_transposed_phone_number
1219 and cont.owner_table_id = party.party_id
1220 and cont.phone is not null;
1221
1222 --Validate the Phone Number on a CREATE_SR scenario
1223
1224 cursor phone_cursor_create_sr is
1225 select hzc.owner_table_id,hzp.party_number,hzp.party_type
1226 from hz_contact_points hzc, hz_parties hzp
1227 where hzc.transposed_phone_number = v_transposed_phone_number
1228 and hzc.owner_table_id = hzp.party_id
1229 and hzc.owner_table_name = 'HZ_PARTIES';
1230
1231 begin
1232 -- This procedure validates the IVR data using sqls.
1233 -- It returns the Id value of the parameter if found.
1234 -- There may not be a case where a parameter was found
1235 -- valid but the id was not available.
1236
1237 l_parameter_id := -1;
1238 x_parameter_flag := 'INVALID';
1239
1240 if p_parameter_code = 'SR_NUMBER' then
1241 open inc_cursor;
1242
1243 fetch inc_cursor into l_parameter_id;
1244 if inc_cursor%NOTFOUND then
1245 l_parameter_id := -1;
1246 else
1247 x_parameter_flag := 'VALID';
1248 end if;
1249
1250 close inc_cursor;
1251
1252 elsif p_parameter_code = 'SR_ACC_NUMBER' then
1253 open account_cursor;
1254
1255 fetch account_cursor into l_parameter_id,x_customer_id,
1256 x_customer_type;
1257 if account_cursor%NOTFOUND then
1258 l_parameter_id := -1;
1259 else
1260 x_parameter_flag := 'VALID_CUSTOMER';
1261 end if;
1262
1263 close account_cursor;
1264
1265 elsif p_parameter_code = 'SR_SERIAL_NUMBER' then
1266 open serial_cursor;
1267
1268 fetch serial_cursor into l_parameter_id,x_customer_id,
1269 x_customer_type;
1270 if serial_cursor%NOTFOUND then
1271 l_parameter_id := -1;
1272 else
1273 x_parameter_flag := 'VALID_CUSTOMER';
1274 end if;
1275
1276 close serial_cursor;
1277
1278 elsif p_parameter_code = 'SR_TAG_NUMBER' then
1279 open tag_cursor;
1280
1281 fetch tag_cursor into l_parameter_id,x_customer_id,
1282 x_customer_type;
1283 if tag_cursor%NOTFOUND then
1284 l_parameter_id := -1;
1285 else
1286 x_parameter_flag := 'VALID_CUSTOMER';
1287 end if;
1288
1289 close tag_cursor;
1290
1291 elsif p_parameter_code = 'SR_PARTY_NUMBER' then
1292 open party_cursor;
1293
1294 fetch party_cursor into l_parameter_id,x_customer_id,
1295 x_customer_type;
1296 if party_cursor%NOTFOUND then
1297 l_parameter_id := -1;
1298 else
1299 x_parameter_flag := 'VALID_CUSTOMER';
1300 end if;
1301
1302 close party_cursor;
1303
1304 elsif p_parameter_code in ('SR_PHONE_NUMBER','SR_ANI') then
1305
1306 if p_parameter_value = 'NO_DATA' then
1307 p_parameter_value := 0;
1308 end if;
1309 p_parameter_value := nvl(p_parameter_value,0);
1310
1311 v_sql_statement := ' begin select reverse(to_char('||p_parameter_value||')) into :v_transposed_phone_number from dual; end; ';
1312 execute immediate v_sql_statement
1313 using out v_transposed_phone_number;
1314
1315 if p_param_action_val in ('CREATE_SR') then
1316 /* When the incoming call is to create a new SR, we check if
1317 there exists just one customer with that phone. If so we
1318 shall default the customers details on the SR form. Else
1319 we shall just open a Blank SR form */
1320 open phone_cursor_create_sr;
1321 n_rec_count := 0;
1322
1323 loop
1324 fetch phone_cursor_create_sr into x_customer_id,v_cust_number_temp,
1325 x_customer_type;
1326 exit when phone_cursor_create_sr%NOTFOUND;
1327 n_rec_count := n_rec_count + 1;
1328
1329 end loop;
1330
1331 close phone_cursor_create_sr;
1332
1333 if n_rec_count = 0 then
1334 l_parameter_id := -1;
1335 elsif n_rec_count > 1 then
1336 p_parameter_mesg := p_parameter_code||'-'||p_parameter_value||'-MULT-'||n_rec_count;
1337 elsif n_rec_count = 1 then
1338 p_parameter_mesg := p_parameter_code||'-'||p_parameter_value;
1339 p_parameter_value := v_cust_number_temp;
1340 p_parameter_code := 'SR_PARTY_NUMBER';
1341 l_parameter_id := x_customer_id;
1342 x_parameter_flag := 'VALID_CUSTOMER';
1343 end if;
1344
1345 else
1349 open phone_cursor;
1346 /* For all other incoming calls with Phone Number or ANI we shall do
1347 a regular Call to Inquiry scenario */
1348
1350
1351 fetch phone_cursor into l_parameter_id,v_phone_number,
1352 x_customer_type;
1353 if phone_cursor%NOTFOUND then
1354 l_parameter_id := -1;
1355 else
1356 p_parameter_mesg := p_parameter_code||'-'||p_parameter_value;
1357 p_parameter_value := v_phone_number;
1358 x_parameter_flag := 'VALID_CUSTOMER';
1359 end if;
1360
1361 close phone_cursor;
1362
1363 end if; /* End of if at p_param_action_val */
1364
1365 elsif p_parameter_code = 'SR_RMA_NUMBER' then
1366 open rma_cursor;
1367
1368 fetch rma_cursor into l_parameter_id,v_parameter_value_temp;
1369 if rma_cursor%NOTFOUND then
1370 l_parameter_id := -1;
1371 v_parameter_value_temp := null;
1372 else
1373 x_parameter_flag := 'VALID_RMA';
1374 p_parameter_mesg := p_parameter_code||' '||p_parameter_value;
1375 p_parameter_code := 'SR_NUMBER';
1376 p_parameter_value:= v_parameter_value_temp;
1377 /* This code above converts the RMA Parameter into the corresponding SR Number.
1378 This opens the main SR form automatically. The Original values of
1379 RMA number and the code are put into the message parameter */
1380
1381 end if;
1382
1383 close rma_cursor;
1384
1385 elsif p_parameter_code = 'SR_CONTRACT_NUMBER' then
1386 open contract_cursor;
1387
1388 fetch contract_cursor into l_parameter_id,x_customer_id,
1389 x_customer_type;
1390 if contract_cursor%NOTFOUND then
1391 l_parameter_id := -1;
1392 else
1393 x_parameter_flag := 'VALID_CUSTOMER';
1394 end if;
1395
1396 close contract_cursor;
1397
1398 end if;
1399
1400 x_parameter_id := l_parameter_id;
1401 x_return_status := 'S';
1402
1403 exception
1404 when OTHERS then
1405 x_return_status := 'U';
1406 x_parameter_id := -1;
1407 x_parameter_flag := 'INVALID';
1408
1409 end;
1410
1411 procedure interpret_service_keys(
1412 v_service_key in varchar2,
1413 v_service_key_value in out NOCOPY varchar2,
1414 p_cust_id in number,
1415 p_cust_account_id in number,
1416 p_phone_id in number,
1417 x_parameter_code out NOCOPY varchar2,
1418 x_return_status out NOCOPY varchar2) is
1419
1420 --Retrieve the Account Number.
1421 cursor account_cursor is
1422 select acc.account_number
1423 from hz_cust_accounts acc
1424 where acc.cust_account_id = p_cust_account_id
1425 and acc.status = 'A';
1426
1427 --Retrieve the Party Number.
1428 cursor party_cursor is
1429 select party_number
1430 from hz_parties
1431 where party_id = p_cust_id;
1432
1433 begin
1434 x_return_status := 'S';
1435
1436 if v_service_key = 'SERVICE_REQUEST_NUMBER' then
1437 x_parameter_code := 'SR_NUMBER';
1438
1439 elsif v_service_key = 'CONTRACT_NUMBER' then
1440 x_parameter_code := 'SR_CONTRACT_NUMBER';
1441
1442 elsif v_service_key = 'SERIAL_NUMBER' then
1443 x_parameter_code := 'SR_SERIAL_NUMBER';
1444
1445 elsif v_service_key = 'EXTERNAL_REFERENCE' then
1446 x_parameter_code := 'SR_TAG_NUMBER';
1447
1448 elsif v_service_key = 'RMA_NUMBER' then
1449 x_parameter_code := 'SR_RMA_NUMBER';
1450
1451 else
1452 if nvl(p_cust_account_id,0) <>0 then
1453 x_parameter_code := 'SR_ACC_NUMBER';
1454 open account_cursor;
1455
1456 fetch account_cursor into v_service_key_value;
1457 if account_cursor%NOTFOUND then
1458 v_service_key_value := '-1';
1459 end if;
1460
1461 close account_cursor;
1462
1463 elsif nvl(p_cust_id,0) <>0 then
1464 x_parameter_code := 'SR_PARTY_NUMBER';
1465 open party_cursor;
1466
1467 fetch party_cursor into v_service_key_value;
1468 if party_cursor%NOTFOUND then
1469 v_service_key_value := '-1';
1470 end if;
1471
1472 close party_cursor;
1473
1474 elsif nvl(p_phone_id,0) <>0 then
1475 x_parameter_code := 'SR_PHONE_NUMBER';
1476
1477 else
1478 x_parameter_code := 'NO_DATA';
1479
1480 end if; -- End of if Acc, Cust, Phone.
1481 end if; -- End of if v_service_key.
1482
1483 end ;
1484
1485 procedure validate_security(
1486 p_ivr_data_key in varchar2,
1487 p_ivr_data_value in varchar2,
1488 p_table_of_agents in out NOCOPY system.CCT_AGENT_RESP_APP_ID_NST,
1489 x_return_status out NOCOPY varchar2) is
1490
1491 lx_msg_count number;
1492 lx_msg_data varchar2(2000);
1493 lx_return_status varchar2(1);
1494
1495 n_agent_id number;
1496 n_resp_id number;
1497 n_user_id number;
1498 n_old_resp_id number;
1499 n_app_id number;
1500 n_old_app_id number;
1501 n_incident_id number;
1502 v_security_flag varchar2(1);
1503 v_resource_err varchar2(1);
1504 v_process_flag varchar2(1) ;
1505 v_sec_setting varchar2(30);
1506
1507 cursor sec_value is select sr_agent_security
1508 from cs_system_options where rownum = 1;
1509
1510 cursor sr_cursor is select incident_id from
1511 cs_incidents_all_b where incident_number = p_ivr_data_value;
1512
1513 cursor sr_type_sec_chk is select 'Y' from
1514 cs_sr_type_mapping where incident_type_id = n_incident_id
1515 and responsibility_id= n_resp_id
1516 and sysdate between
1517 nvl(start_date,sysdate) and nvl(end_date,sysdate);
1518
1519 begin
1520 x_return_status := 'S';
1521 n_old_resp_id := -1;
1522 n_resp_id := -1;
1523 n_old_app_id := -1;
1524 n_app_id := -1;
1525 v_process_flag := 'Y';
1526
1527 open sr_cursor;
1528 fetch sr_cursor into n_incident_id;
1529
1530 if sr_cursor%NOTFOUND then
1531 v_process_flag := 'N';
1532 end if;
1533 close sr_cursor;
1534
1535 if v_process_flag = 'Y' then
1536 for i IN 1..p_table_of_agents.count
1537 loop
1538 n_agent_id := p_table_of_agents(i).agent_id;
1539 BEGIN
1540 -- Deriving the user id for resource. If there is no user id associated
1541 -- with the resource the security_yn_flag will be set to N. If the
1542 -- resource is associated with more that one user then security_yn_flag
1543 -- will be set to N.
1544
1545 SELECT user_id
1546 INTO n_user_id
1547 FROM jtf_rs_resource_extns
1548 WHERE resource_id = n_agent_id;
1549
1550 v_resource_err := 'N';
1551 EXCEPTION
1552 -- Setting the v_security_flag to N because it may have the value of
1553 -- previous record.
1554 WHEN NO_DATA_FOUND THEN
1555 v_resource_err := 'Y';
1556 v_security_flag := 'N';
1557 WHEN TOO_MANY_ROWS THEN
1558 v_resource_err := 'Y';
1559 v_security_flag := 'N';
1560 WHEN OTHERS THEN
1561 v_resource_err := 'Y';
1562 v_security_flag := 'N';
1563 END;
1564
1565 n_resp_id := p_table_of_agents(i).responsibility_id;
1566 n_app_id := p_table_of_agents(i).application_id;
1567
1568 if (n_old_resp_id <> n_resp_id OR n_old_app_id <> n_app_id) then
1569
1570 -- When the responsibility id changes the security function
1571 -- is called to check if the responsibilty has access to
1572 -- to the SR.
1573 -- Set the CS application context for the responsibility.
1574 -- cs_sr_security_context.set_sr_security_context('SRTYPE_ID', n_incident_id);
1575
1576 -- Call Sec function(n_app_id, p_ivr_data_value);
1577
1578 v_security_flag := 'N';
1579
1580 IF nvl(v_resource_err,'N') = 'N' THEN
1581 fnd_global.apps_initialize(
1582 user_id => n_user_id,
1583 resp_id => n_resp_id,
1584 resp_appl_id => n_app_id
1585 );
1586 cs_sr_security_context.set_sr_security_context('RESP_ID', n_resp_id);
1587 cs_sr_security_context.set_sr_security_context('APPL_ID', n_app_id);
1588
1589 cs_sr_security_grp.validate_user_responsibility
1590 ( p_api_version => NULL,
1591 p_init_msg_list => fnd_api.g_true,
1592 p_commit => fnd_api.g_true,
1593 p_incident_id => n_incident_id,
1594 x_resp_access_status => v_security_flag,
1595 x_return_status => lx_return_status,
1596 x_msg_count => lx_msg_count,
1597 x_msg_data => lx_msg_data);
1598
1599 if (lx_return_status <> fnd_api.g_ret_sts_success) then
1600 v_security_flag := 'N';
1601 end if;
1602
1603 n_old_resp_id := n_resp_id;
1604 n_old_app_id := n_app_id;
1605 end if; -- Resource error check
1606
1607 end if; -- Check if resp or appl id changed
1608
1609 p_table_of_agents(i).security_yn_flag := v_security_flag;
1610 v_resource_err := null;
1611 end loop;
1612
1613 elsif v_process_flag = 'N' then
1614 for i IN 1..p_table_of_agents.count loop
1615 p_table_of_agents(i).security_yn_flag := 'Y';
1616 end loop;
1617 end if; -- End of if at v_process_flag
1618
1619 end;
1620
1621 procedure start_media_item( p_resp_appl_id in number,
1622 p_resp_id in number,
1623 p_user_id in number,
1624 p_login_id in number,
1625 x_return_status out nocopy varchar2,
1626 x_msg_count out nocopy number,
1627 x_msg_data out nocopy varchar2,
1628 x_media_id out nocopy number,
1629 p_outbound_dnis in varchar2 DEFAULT NULL, -- Added by vpremach for Bug 9499153
1630 p_outbound_ani in varchar2 DEFAULT NULL -- Added by vpremach for Bug 9499153
1631 ) is
1632
1633 v_true varchar2(5);
1634 v_false varchar2(5);
1635 v_ret_sts_failure varchar2(1);
1636 p_media_rec JTF_IH_PUB.media_rec_type;
1637
1638 begin
1639
1640 v_true := cs_core_util.get_g_true;
1641 v_false := cs_core_util.get_g_false;
1642 v_ret_sts_failure := 'E';
1643
1644 p_media_rec.media_id := NULL;
1645 p_media_rec.media_item_type := 'TELEPHONE';
1646 p_media_rec.start_date_time := sysdate;
1647 p_media_rec.direction := 'OUTBOUND';
1648 p_media_rec.ani := p_outbound_ani ; -- Added by vpremach for Bug 9499153
1649 p_media_rec.dnis := p_outbound_dnis; -- Added by vpremach for Bug 9499153
1650
1651
1652 jtf_ih_pub.open_mediaitem( p_api_version => 1.0,
1653 p_init_msg_list => v_true,
1654 p_commit => v_true,
1655 p_resp_appl_id => p_resp_appl_id,
1656 p_resp_id => p_resp_id,
1657 p_user_id => p_user_id,
1658 p_login_id => p_login_id,
1659 x_return_status => x_return_status,
1660 x_msg_count => x_msg_count,
1661 x_msg_data => x_msg_data,
1662 p_media_rec => p_media_rec,
1663 x_media_id => x_media_id);
1664
1665 if x_media_id is null then
1666 x_return_status := v_ret_sts_failure;
1667 end if;
1668
1669 end start_media_item;
1670
1671
1672
1673 -- ===========================================================
1674 -- PRAYADUR 04/28/2004
1675 -- Added the procedure SR_UWQ_NONMEDIA_ACTIONS for Bug 3357706.
1676 -- This is a Non media Action function used to pass the
1677 -- Default_Tab Parameter to SR form. This Function is mapped
1678 -- to the Action object Code 'SR' and it invokes the SR form
1679 -- with Default Tab as Tasks.
1680 -- ===========================================================
1681
1682 PROCEDURE SR_UWQ_NONMEDIA_ACTIONS(p_ieu_action_data IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
1683 x_action_type OUT NOCOPY NUMBER,
1684 x_action_name OUT NOCOPY varchar2,
1685 x_action_param OUT NOCOPY varchar2,
1686 x_msg_name OUT NOCOPY varchar2,
1687 x_msg_param OUT NOCOPY varchar2,
1688 x_dialog_style OUT NOCOPY number,
1689 x_msg_appl_short_name OUT NOCOPY varchar2) IS
1690
1691
1692 l_Req_Number Varchar2(64);
1693 l_Req_id number;
1694 l_Task_id NUMBER;
1695 l_cs_html VARCHAR2(1000);
1696
1697 CURSOR SR_Cur is
1698 Select Inc.Incident_number,Inc.Incident_id
1699 from Jtf_tasks_b Tsk, CS_incidents_all_b Inc
1700 where Tsk.Task_id=l_Task_id
1701 and Inc.Incident_Id=Tsk.source_object_id;
1702
1703
1704 BEGIN
1705
1706 FOR i IN p_ieu_action_data.first.. p_ieu_action_data.last
1707
1708 LOOP
1709
1710 IF ( upper(p_ieu_action_data(i).param_name) = 'TASK_ID' ) then
1711
1712 l_task_id := p_ieu_action_data(i).param_value;
1713
1714 END IF;
1715 IF ( upper(p_ieu_action_data(i).param_name) = 'UWQ_HTML_NAVIGATION' ) then
1716 l_cs_html := p_ieu_action_data(i).param_value;
1717 END IF;
1718 END LOOP;
1719
1720 IF l_task_id IS NOT NULL THEN
1721
1722 OPEN SR_cur;
1723 FETCH SR_Cur INTO l_Req_Number,l_Req_id;
1724 IF SR_cur%NOTFOUND THEN
1725 NULL;
1726 ELSE
1727 IF l_cs_html IS NOT NULL AND l_cs_html ='CS_HTML' THEN
1728 x_action_param := 'cszIncidentId='||l_Req_id ;
1729 x_action_name := 'CSZ_SR_UP_FN';
1730 ELSE
1731 x_action_param := 'REQUEST_NUMBER="' || l_Req_Number||'"' ;
1732 x_action_param :=x_action_param ||'DEFAULT_TAB="TASKS"';
1733 x_action_param :=x_action_param ||'REQUEST_TASK_ID="' || l_task_id||'"';
1734 x_action_name := 'CSXSRISR' ;
1735 END IF;
1736 END IF;
1737 CLOSE SR_cur;
1738 END IF;
1739 x_action_type := 1;
1740 x_msg_name := 'NULL' ;
1741 x_msg_param := 'NULL' ;
1742 x_dialog_style := 1;
1743 x_msg_appl_short_name := 'NULL' ;
1744 EXCEPTION
1745 WHEN OTHERS THEN
1746 NULL;
1747
1748 END SR_UWQ_NONMEDIA_ACTIONS ;
1749
1750
1751 procedure create_service_request(
1752 p_api_version IN NUMBER,
1753 p_init_msg_list IN VARCHAR2,
1754 p_commit IN VARCHAR2,
1755 x_return_status OUT NOCOPY VARCHAR2,
1756 x_msg_count OUT NOCOPY NUMBER,
1757 x_msg_data OUT NOCOPY VARCHAR2,
1758 p_resp_appl_id IN NUMBER,
1759 p_resp_id IN NUMBER,
1760 p_user_id IN NUMBER,
1761 p_login_id IN NUMBER,
1762 p_org_id IN NUMBER,
1763 p_request_id IN NUMBER,
1764 p_request_number IN VARCHAR2,
1765 sr_type IN VARCHAR2,
1766 summary IN VARCHAR2,
1767 severity_id IN VARCHAR2,
1768 urgency_id IN VARCHAR2,
1769 customer_id IN VARCHAR2,
1770 customer_type IN VARCHAR2,
1771 account_id IN VARCHAR2,
1772 note_type IN VARCHAR2,
1773 note IN VARCHAR2,
1774 -- contact_id IN VARCHAR2,
1775 -- contact_point_id IN VARCHAR2,
1776 -- primary_flag IN VARCHAR2,
1777 -- contact_point_type IN VARCHAR2,
1778 -- contact_type IN VARCHAR2,
1779 p_auto_assign IN VARCHAR2,
1780 p_auto_generate_tasks IN VARCHAR2,
1781 x_service_request_number OUT NOCOPY NUMBER,
1782 p_default_contract_sla_ind IN VARCHAR2,
1783 p_default_coverage_template_id IN NUMBER) is
1784
1785 l_auto_generate_tasks VARCHAR2(1) := 'N';
1786 x_msg_index_out NUMBER;
1787
1788 subtype r_service_request_rec_type is CS_SERVICEREQUEST_PUB.service_request_rec_type;
1789 r_service_request_rec r_service_request_rec_type;
1790
1791 subtype t_notes_table_type is CS_ServiceRequest_PUB.notes_table;
1792 t_notes_table t_notes_table_type;
1793
1794 subtype t_contacts_table_type is CS_ServiceRequest_PUB.contacts_table;
1795 t_contacts_table t_contacts_table_type;
1796
1797 subtype o_sr_create_out_rec_type is CS_SERVICEREQUEST_PUB.SR_CREATE_OUT_REC_TYPE;
1798 o_sr_create_out_rec o_sr_create_out_rec_type;
1799
1800 BEGIN
1801 CS_SERVICEREQUEST_PUB.initialize_rec(r_service_request_rec);
1802
1803 r_service_request_rec.status_id := '1';
1804 r_service_request_rec.customer_id := customer_id;
1805 r_service_request_rec.caller_type := customer_type;
1806 r_service_request_rec.account_id := account_id;
1807 r_service_request_rec.request_date := sysdate;
1808 r_service_request_rec.type_id := sr_type;
1809 r_service_request_rec.summary := summary;
1810 r_service_request_rec.severity_id := severity_id;
1811 r_service_request_rec.urgency_id := urgency_id;
1812 t_notes_table(0).note := note;
1813 t_notes_table(0).note_type := note_type;
1814 -- t_contacts_table(0).party_id := contact_id;
1815 -- t_contacts_table(0).contact_point_id := contact_point_id;
1816 -- t_contacts_table(0).primary_flag := primary_flag;
1817 -- t_contacts_table(0).contact_point_type := contact_point_type;
1818 -- t_contacts_table(0).contact_type := contact_type;
1819
1820 if (fnd_profile.value('CS_SR_AUTO_TASK_CREATE') = 'TASK_TMPL') then
1821 l_auto_generate_tasks := 'Y';
1822 else
1823 l_auto_generate_tasks := 'N';
1824 end if;
1825
1826 CS_ServiceRequest_PUB.Create_ServiceRequest(
1827 p_api_version => p_api_version,
1828 p_init_msg_list => p_init_msg_list,
1829 p_commit => p_commit,
1830 x_return_status => x_return_status,
1831 x_msg_count => x_msg_count,
1832 x_msg_data => x_msg_data,
1833 p_resp_appl_id => p_resp_appl_id,
1834 p_resp_id => p_resp_id,
1835 p_user_id => p_user_id,
1836 p_login_id => p_login_id,
1837 p_org_id => p_org_id,
1838 p_request_id => p_request_id,
1839 p_request_number => p_request_number,
1840 p_service_request_rec => r_service_request_rec,
1841 p_notes => t_notes_table,
1842 p_contacts => t_contacts_table,
1843 p_auto_assign => nvl(fnd_profile.value('CS_AUTO_ASSIGN_OWNER_FORMS'),'N'),
1844 p_auto_generate_tasks => l_auto_generate_tasks,
1845 x_sr_create_out_rec => o_sr_create_out_rec,
1846 p_default_contract_sla_ind => p_default_contract_sla_ind,
1850 x_service_request_number := o_sr_create_out_rec.request_number;
1847 p_default_coverage_template_id => p_default_coverage_template_id);
1848
1849 IF ( x_return_status ) = 'S' THEN
1851 END IF;
1852
1853 end create_service_request;
1854
1855
1856 PROCEDURE Build_Solution_Text_Query(
1857 p_raw_text in varchar2,
1858 p_solution_type_id_tbl in varchar2,
1859 p_search_option in number,
1860 x_solution_text out NOCOPY varchar2)
1861 is
1862 begin
1863 x_solution_text := CS_KNOWLEDGE_PVT.Build_Solution_Text_Query(p_raw_text, NULL, NULL, NULL, p_search_option);
1864 end Build_Solution_Text_Query;
1865
1866
1867 FUNCTION Get_KM_Params_Str(
1868 solution_num in varchar2)
1869 return varchar2
1870 is
1871 begin
1872 return CS_KB_INTEG_CONSTANTS_PKG.getParameterName('SOLUTION_NUM')||'='|| solution_num ||'&'||
1873 CS_KB_INTEG_CONSTANTS_PKG.getParameterName('TASK_PAGE_FUNC')||'=CSZ_TASK_TEMPLATE_CR_FN'||'&'||
1874 'OAPB=CS_KB_SR_BRAND';
1875 end Get_KM_Params_Str;
1876
1877 FUNCTION Get_CCT_SSO_ACTIVE_FLAG RETURN VARCHAR2 IS
1878 BEGIN
1879 RETURN CCT_SSO_ACTIVE_FLAG;
1880 END;
1881 PROCEDURE SET_CCT_SSO_ACTIVE_FLAG(v_flag VARCHAR2) IS
1882 BEGIN
1883 CCT_SSO_ACTIVE_FLAG := v_flag;
1884 END;
1885
1889 *== Modification History:
1886 /*======================================================================+
1887 *==
1888 *== Procedure name :enumerate_sr_nodes
1890 *==
1891 *== Date Name Desc
1892 *== ---------- ---------
1893 *---------------------------------------------
1894 *== 07-dec-2004 VARNARAY Made changes for Bug 3818940.
1895 *== 29-SEP-2005 PRAYADUR Fix for Bug 4434093 added.
1896 *========================================================================*/
1897 procedure ENUMERATE_CASE_HTML_NODES
1898 (p_resource_id in number,
1899 p_language in varchar2,
1900 p_source_lang in varchar2,
1901 p_sel_enum_id in number) as
1902
1903 l_node_label varchar2(100);
1904 l_sr_list IEU_PUB.EnumeratorDataRecordList;
1905 l_bind_list IEU_PUB.BindVariableRecordList;
1906 l_node_counter number;
1907
1908 l_team_id number;
1909 l_team_name varchar2(30);
1910 l_group_id number;
1911 l_group_name varchar2(60);
1912 l_where_clause varchar2(500);
1913 l_parent_where_clause varchar2(500);
1914 l_sr_name varchar2(30);
1915 return_value varchar2(2000);
1916
1917 l_view_name varchar2(50);
1918 l_data_source varchar2(50);
1919 n_where_clause varchar2(500);
1920 l_cursor_sql varchar2(1500);
1921 l_node_id number;
1922 l_id_of_value number;
1923 l_parent_id number;
1924 l_where_value varchar2(500);
1925 l_cursor_key_col varchar2(30);
1926 l_value_flag varchar2(10);
1927 l_node_query varchar2(10);
1928
1929 cursor team_cursor is select distinct team_mem.team_id,team_tl.team_name
1930 from jtf_rs_team_members team_mem, jtf_rs_teams_tl team_tl
1931 where team_resource_id = p_resource_id
1932 and team_mem.team_id = team_tl.team_id
1933 and team_tl.language = userenv('LANG');
1934
1935 cursor group_cursor is select distinct group_mem.group_id,group_tl.group_name
1936 from jtf_rs_group_members group_mem, jtf_rs_groups_tl group_tl
1937 where group_mem.resource_id = p_resource_id
1938 and group_mem.group_id = group_tl.group_id
1939 and group_tl.language = userenv('LANG');
1940
1941 l_lookup_code varchar2(30);
1942 l_meaning varchar2(360);--5579863
1943 l_level number;
1944 l_res_cat_enum_flag varchar2(1);
1945
1946 cursor all_cursor is select node_label,node_view,cursor_key_col,cursor_sql,
1947 data_source
1948 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
1949 where uwq_b.node_id = uwq_tl.node_id
1950 and uwq_tl.language = userenv('LANG')
1951 and parent_id = l_node_id
1952 and node_query='CURSOR' and enabled_flag='Y';
1953
1954 cursor node_cursor is select 'Node ',
1955 node_view,where_clause,data_source,level,node_query,cursor_sql,
1956 uwq_b.node_id,res_cat_enum_flag
1957 from cs_sr_uwq_nodes_b uwq_b
1958 where node_id > 9999 and node_query='SINGLE' and enabled_flag='Y'
1959 and ( parent_id is null or parent_id > 9999 )
1960 start with uwq_b.parent_id is null
1961 connect by prior uwq_b.node_id = uwq_b.parent_id;
1962
1963 cursor seed_cursor is select 'Node',
1964 node_view,where_clause,data_source,node_query,cursor_sql,
1965 uwq_b.node_id,res_cat_enum_flag,level,nvl(parent_id,-1)
1966 from cs_sr_uwq_nodes_b uwq_b
1967 where enabled_flag='Y'
1968 --where node_id < 1000 and enabled_flag='Y'
1969 and node_query = 'SINGLE'
1970 start with uwq_b.parent_id is null
1971 connect by prior uwq_b.node_id = uwq_b.parent_id;
1972
1973 cursor cur_seed_cursor is select node_label,
1974 node_view,where_clause,data_source,node_query,cursor_sql,
1975 cursor_key_col,node_id,res_cat_enum_flag
1976 from cs_sr_uwq_nodes_vl
1977 where node_id < 1000 and enabled_flag='Y'
1978 and node_query = 'CURSOR';
1979
1980 v_cursorid number;
1981 v_dummy number;
1982
1983 begin
1984
1985 begin
1986 select name into l_sr_name from jtf_objects_vl
1987 where object_code='SR';
1988 exception
1989 when OTHERS then
1990 l_sr_name := 'SR Error';
1991 end;
1992
1993 l_node_counter := 0;
1994 savepoint start_cs_enum;
1995 l_sr_name := fnd_message.get_string('CS','CSZ_CASE_MESSAGE');
1996 l_sr_list(l_node_counter).node_label := l_sr_name;
1997 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_LABEL_V';
1998 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_LABEL_DS';
1999 l_sr_list(l_node_counter).media_type_id := '';
2000 l_sr_list(l_node_counter).where_clause := ' incident_id = -1 ';
2001 l_sr_list(l_node_counter).node_type := 0;
2002 l_sr_list(l_node_counter).hide_if_empty := '';
2003 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
2004 l_sr_list(l_node_counter).node_depth := 1;
2005
2006 /* The setting of res_cat_enum_flag='N' makes sure that the default
2007 * where condition in UWQ is not fired. Instead the where condition
2008 * defined by l_where_clause is fired */
2009
2010 -- Creation of My Node. All SR assigned directly to the Resource.
2011
2012 open seed_cursor;
2013
2014 loop
2015 fetch seed_cursor into
2016 l_node_label,l_view_name,l_where_clause,l_data_source,
2017 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag,l_level,l_parent_id;
2018
2022 -- This means that the node is a seeded node or a child node of
2019 exit when seed_cursor%NOTFOUND;
2020
2021 if l_node_id < 1000 or (l_parent_id < 1000 and l_parent_id > 0) then
2026
2023 -- another seeded node.
2024
2025 l_node_counter := l_node_counter + 1;
2027 begin
2028 select node_label into l_node_label from cs_sr_uwq_nodes_tl
2029 where node_id=l_node_id and language=userenv('LANG');
2030 exception
2031 when NO_DATA_FOUND then
2032 l_node_label:='Error: No Label ';
2033 end;
2034
2035 l_bind_list(1).bind_var_name := ':owner_id';
2036 l_bind_list(1).bind_var_value := p_resource_id;
2037 l_bind_list(1).bind_var_data_type :='NUMBER';
2038
2039 if (l_parent_id < 1000 and l_parent_id > 0) then
2040 -- This means that the node is a child node and its parent
2041 -- is a seeded node. So the child node will inherit the where
2042 -- condition of the parent.
2043
2044 begin
2045 l_parent_where_clause := null;
2046 select where_clause into l_parent_where_clause from
2047 cs_sr_uwq_nodes_b
2048 where node_id=l_parent_id ;
2049 exception
2050 when NO_DATA_FOUND then
2051 l_parent_where_clause :=' incident_id = -1 ';
2052 end;
2053 if l_where_clause is not null then
2054 l_where_clause := l_parent_where_clause ||' AND '||l_where_clause;
2055 else
2056 l_where_clause := l_parent_where_clause ;
2057 end if;
2058
2059 end if; -- End of if at l_parent_id
2060
2061 If (l_node_label='My Service Request') THEN
2062 l_node_label := fnd_message.get_string('CS','CS_SR_UWQ_MY_CASE_NODE');
2063 end if;
2064 l_sr_list(l_node_counter).node_label := l_node_label;
2065 l_sr_list(l_node_counter).view_name := l_view_name;
2066 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2067 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2068 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_EMPLOYEE_DS';
2069 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2070 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2071 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_GROUP_DS';
2072 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2073 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2074 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_TEAM_DS';
2075 END IF;
2076 l_data_source := l_sr_list(l_node_counter).data_source;
2077 l_view_name :=l_sr_list(l_node_counter).view_name;
2078 l_sr_list(l_node_counter).media_type_id := '';
2079 l_sr_list(l_node_counter).where_clause := l_where_clause;
2080 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
2081 l_sr_list(l_node_counter).node_type := 10;
2082 l_sr_list(l_node_counter).hide_if_empty := '';
2083 l_sr_list(l_node_counter).node_depth := l_level+1;
2084 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2085 l_sr_list(l_node_counter).bind_vars := return_value;
2086
2087 begin
2088 -- For Each of the SINGLE defined nodes first check if there
2089 -- exist any CURSOR defined child nodes. If so execute the
2090 -- SQL and build the child nodes.
2091
2092 select node_label,node_view,cursor_key_col,data_source,
2093 node_query,cursor_sql,uwq_b.node_id,res_cat_enum_flag
2094 into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
2095 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag
2096 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
2097 where uwq_b.node_id = uwq_tl.node_id
2098 and uwq_tl.language = userenv('LANG')
2099 and parent_id = l_node_id and enabled_flag='Y'
2100 and node_query = 'CURSOR' ;
2101
2102 if l_cursor_sql is not null then
2103
2104 l_node_counter := l_node_counter + 1;
2105 l_sr_list(l_node_counter).node_label := l_node_label;
2106 l_sr_list(l_node_counter).view_name := l_view_name;
2107 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2108 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_EMPLOYEE_DS';
2109 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2110 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2111 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_GROUP_DS';
2112 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2113 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2114 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_TEAM_DS';
2115 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2116 END IF;
2117 l_view_name :=l_sr_list(l_node_counter).view_name;
2118 l_data_source := l_sr_list(l_node_counter).data_source;
2119 l_sr_list(l_node_counter).media_type_id := '';
2120 l_sr_list(l_node_counter).where_clause := ' incident_id = -101 ';
2121 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
2122 l_sr_list(l_node_counter).node_type := 10;
2123 l_sr_list(l_node_counter).hide_if_empty := '';
2124 l_sr_list(l_node_counter).node_depth := l_level+2;
2125
2126 v_cursorid := dbms_sql.open_cursor;
2127 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
2128 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
2129 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
2130 v_dummy := dbms_sql.execute(v_cursorid);
2131
2132 loop
2133 if dbms_sql.fetch_rows(v_cursorid) = 0 then
2134 exit;
2135 end if;
2136
2137 dbms_sql.column_value(v_cursorid, 1, l_meaning);
2138 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
2139 l_where_clause := l_cursor_key_col||' = :seedsubbindvalue';
2140 l_node_counter := l_node_counter + 1;
2141
2142 l_bind_list(1).bind_var_name := ':seedsubbindvalue';
2143 l_bind_list(1).bind_var_value := l_id_of_value;
2144 l_bind_list(1).bind_var_data_type :='NUMBER';
2145
2146 l_sr_list(l_node_counter).node_label := l_meaning;
2147 l_sr_list(l_node_counter).view_name := l_view_name;
2148 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2149 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_EMPLOYEE_DS';
2150 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2151 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2152 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_GROUP_DS';
2153 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2154 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2155 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_TEAM_DS';
2156 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2157 END IF;
2158 l_data_source := l_sr_list(l_node_counter).data_source;
2159 l_view_name :=l_sr_list(l_node_counter).view_name;
2160 l_sr_list(l_node_counter).media_type_id := '';
2161 l_sr_list(l_node_counter).where_clause := l_where_clause;
2162 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
2163
2164 l_sr_list(l_node_counter).node_type := 12;
2165
2166 l_sr_list(l_node_counter).hide_if_empty := '';
2167 l_sr_list(l_node_counter).node_depth := l_level+3;
2168
2169 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2170 l_sr_list(l_node_counter).bind_vars := return_value;
2171
2172 end loop;
2173
2174 dbms_sql.close_cursor(v_cursorid);
2175
2176 end if;
2177
2178 exception
2179 when NO_DATA_FOUND then
2180 null;
2181 end; -- End of Begin at CURSOR based sub Nodes.
2182
2183 end if; --- End of check for node id and parent id.
2184
2185 end loop; --- End of main loop for seed cursor
2186
2187 close seed_cursor;
2188
2189 -- Creation of Team Node. All SR assigned directly to the Team(s) of the Resource.
2190 -- The first node in the Team Node is a dummy
2191
2192 open cur_seed_cursor;
2193
2194 loop
2195 fetch cur_seed_cursor into l_node_label,l_view_name,
2196 l_where_clause,l_data_source,
2197 l_node_query,l_cursor_sql,l_cursor_key_col,
2198 l_node_id,l_res_cat_enum_flag;
2199
2200 exit when cur_seed_cursor%NOTFOUND;
2201
2202 if l_cursor_sql is not null then
2203
2204 l_bind_list(1).bind_var_name := ':owner_id';
2205 l_bind_list(1).bind_var_value := p_resource_id;
2206 l_bind_list(1).bind_var_data_type :='NUMBER';
2207
2208 l_node_counter := l_node_counter + 1;
2209 l_sr_list(l_node_counter).node_label := l_node_label;
2210 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2211 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_EMPLOYEE_DS';
2212 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2213 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2214 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_GROUP_DS';
2215 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2216 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2217 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_TEAM_DS';
2218 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2219 END IF;
2220 l_data_source := l_sr_list(l_node_counter).data_source;
2221 l_view_name :=l_sr_list(l_node_counter).view_name;
2222 l_sr_list(l_node_counter).media_type_id := '';
2223 l_sr_list(l_node_counter).where_clause := l_where_clause;
2224 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
2225 l_sr_list(l_node_counter).node_type := 10;
2226 l_sr_list(l_node_counter).hide_if_empty := '';
2227 l_sr_list(l_node_counter).node_depth := 2;
2228 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2229 l_sr_list(l_node_counter).bind_vars := return_value;
2230
2231 v_cursorid := dbms_sql.open_cursor;
2232 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
2233 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
2234 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
2235 dbms_sql.bind_variable(v_cursorid, ':OWNER_ID', p_resource_id);
2236 v_dummy := dbms_sql.execute(v_cursorid);
2237
2238 loop
2239 if dbms_sql.fetch_rows(v_cursorid) = 0 then
2240 exit;
2241 end if;
2242 dbms_sql.column_value(v_cursorid, 1, l_meaning);
2243 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
2244 l_where_clause := l_cursor_key_col||' = :bindvalue';
2245 l_node_counter := l_node_counter + 1;
2246
2247 l_bind_list(1).bind_var_name := ':bindvalue';
2248 l_bind_list(1).bind_var_value := l_id_of_value;
2249 l_bind_list(1).bind_var_data_type :='NUMBER';
2250
2251 l_sr_list(l_node_counter).node_label := l_meaning;
2252 l_sr_list(l_node_counter).view_name := l_view_name;
2253 l_sr_list(l_node_counter).data_source := l_data_source;
2254 l_sr_list(l_node_counter).media_type_id := '';
2255 l_sr_list(l_node_counter).where_clause := l_where_clause;
2256 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
2257 l_sr_list(l_node_counter).node_type := 12;
2258 l_sr_list(l_node_counter).hide_if_empty := '';
2259 l_sr_list(l_node_counter).node_depth := 3;
2260
2261 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2262 l_sr_list(l_node_counter).bind_vars := return_value;
2263 end loop;
2264
2265 dbms_sql.close_cursor(v_cursorid);
2266
2267 end if; -- End of if at cursor_sql is not null
2268 end loop; -- End of loop for cur_seed_cursor
2269
2270 --- Start of personalized single,cursor nodes.
2271 -- This section of code queries the table cs_sr_uwq_nodes_b / tl for all
2272 -- personalized nodes.
2273
2274 open node_cursor;
2275 loop
2276
2277 fetch node_cursor into l_node_label,
2278 l_view_name,n_where_clause,l_data_source,l_level,
2279 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag;
2280 exit when node_cursor%notfound;
2281
2282 l_node_counter := l_node_counter + 1;
2283
2284 begin
2285 select node_label into l_node_label from cs_sr_uwq_nodes_tl
2286 where node_id=l_node_id and language=userenv('LANG');
2287 exception
2288 when NO_DATA_FOUND then
2289 l_node_label:='Error: No Label ';
2290 end;
2291
2292 l_sr_list(l_node_counter).node_label := l_node_label;
2293 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2294 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_EMPLOYEE_DS';
2295 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2296 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2297 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_GROUP_DS';
2298 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2299 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2300 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_TEAM_DS';
2301 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2302 END IF;
2303 l_data_source := l_sr_list(l_node_counter).data_source;
2304 l_view_name :=l_sr_list(l_node_counter).view_name;
2305 l_sr_list(l_node_counter).media_type_id := '';
2306 l_sr_list(l_node_counter).where_clause := n_where_clause;
2307 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
2308 l_sr_list(l_node_counter).node_type := 10;
2309 l_sr_list(l_node_counter).hide_if_empty := '';
2310 l_sr_list(l_node_counter).node_depth := l_level+1;
2311
2312 if instr(lower(n_where_clause), ':owner_id') <> 0 then
2313 l_bind_list(1).bind_var_name := ':owner_id';
2314 l_bind_list(1).bind_var_value := p_resource_id;
2315 l_bind_list(1).bind_var_data_type :='NUMBER';
2316 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2317 l_sr_list(l_node_counter).bind_vars := return_value;
2318 end if;
2319
2320 begin
2321 -- For Each of the SINGLE defined nodes first check if there
2322 -- exist any CURSOR defined child nodes. If so execute the
2323 -- SQL and build the child nodes.
2324 select node_label,node_view,cursor_key_col,data_source,
2325 node_query,cursor_sql,uwq_b.node_id
2326 , res_cat_enum_flag
2327 into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
2328 l_node_query,l_cursor_sql,l_node_id
2329 , l_res_cat_enum_flag
2330 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
2331 where uwq_b.node_id = uwq_tl.node_id
2332 and uwq_tl.language = userenv('LANG')
2333 and parent_id = l_node_id and enabled_flag='Y'
2334 and node_query = 'CURSOR' ;
2335
2336 if l_cursor_sql is not null then
2337
2338 l_node_counter := l_node_counter + 1;
2339 l_sr_list(l_node_counter).node_label := l_node_label;
2340 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2341 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_EMPLOYEE_DS';
2342 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2343 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2344 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_GROUP_DS';
2345 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2346 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2347 l_sr_list(l_node_counter).data_source := 'CS_CASE_UWQ_TEAM_DS';
2348 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2349 END IF;
2350 l_data_source := l_sr_list(l_node_counter).data_source;
2351 l_view_name :=l_sr_list(l_node_counter).view_name;
2352 l_sr_list(l_node_counter).media_type_id := '';
2353 l_sr_list(l_node_counter).where_clause := ' incident_id = -1 ';
2354 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
2355 l_sr_list(l_node_counter).node_type := 10;
2356 l_sr_list(l_node_counter).hide_if_empty := '';
2357 l_sr_list(l_node_counter).node_depth := l_level+2;
2358
2359 v_cursorid := dbms_sql.open_cursor;
2360 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
2361 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
2362 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
2363 v_dummy := dbms_sql.execute(v_cursorid);
2364
2365 loop
2366 if dbms_sql.fetch_rows(v_cursorid) = 0 then
2367 exit;
2368 end if;
2369
2370 dbms_sql.column_value(v_cursorid, 1, l_meaning);
2371 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
2372 l_where_clause := l_cursor_key_col||' = :customsubbindvalue';
2373 l_node_counter := l_node_counter + 1;
2374
2375 l_bind_list(1).bind_var_name := ':customsubbindvalue';
2376 l_bind_list(1).bind_var_value := l_id_of_value;
2377 l_bind_list(1).bind_var_data_type :='NUMBER';
2378
2379 l_sr_list(l_node_counter).node_label := l_meaning;
2380 l_sr_list(l_node_counter).view_name := l_view_name;
2381 l_sr_list(l_node_counter).data_source := l_data_source;
2382 l_sr_list(l_node_counter).media_type_id := '';
2383 l_sr_list(l_node_counter).where_clause := l_where_clause;
2384 l_sr_list(l_node_counter).res_cat_enum_flag :=
2385 l_res_cat_enum_flag;
2386 l_sr_list(l_node_counter).node_type := 12;
2387 l_sr_list(l_node_counter).hide_if_empty := '';
2388 l_sr_list(l_node_counter).node_depth := l_level+3;
2389
2390 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2391 l_sr_list(l_node_counter).bind_vars := return_value;
2392 end loop;
2393
2394 dbms_sql.close_cursor(v_cursorid);
2395
2396 end if;
2397
2398 exception
2399 when NO_DATA_FOUND then
2400 null;
2401 end; -- End of Begin at CURSOR based sub Nodes.
2402
2403 end loop;
2404 close node_cursor;
2405
2406 ieu_pub.add_uwq_node_data
2407 (p_resource_id,
2408 p_sel_enum_id,
2409 l_sr_list );
2410
2411 exception
2412 when OTHERS then
2413
2414 --prayadur 29-Sep-05 Commented the code below and added
2415 --the following 2 lines for Bug 4434093.
2416 -- l_where_clause := sqlerrm;
2417 ROLLBACK TO start_cs_enum;
2418 RAISE;
2419
2420 end ENUMERATE_CASE_HTML_NODES;
2421
2422 procedure ENUMERATE_SR_HTML_NODES
2423 (p_resource_id in number,
2424 p_language in varchar2,
2425 p_source_lang in varchar2,
2426 p_sel_enum_id in number) as
2427
2428 l_node_label varchar2(100);
2429 l_sr_list IEU_PUB.EnumeratorDataRecordList;
2430 l_bind_list IEU_PUB.BindVariableRecordList;
2431 l_node_counter number;
2432
2433 l_team_id number;
2434 l_team_name varchar2(30);
2435 l_group_id number;
2436 l_group_name varchar2(60);
2437 l_where_clause varchar2(500);
2438 l_parent_where_clause varchar2(500);
2439 l_sr_name varchar2(30);
2440 return_value varchar2(2000);
2441
2442 l_view_name varchar2(50);
2443 l_data_source varchar2(50);
2444 n_where_clause varchar2(500);
2445 l_cursor_sql varchar2(1500);
2446 l_node_id number;
2447 l_id_of_value number;
2448 l_parent_id number;
2449 l_where_value varchar2(500);
2450 l_cursor_key_col varchar2(30);
2451 l_value_flag varchar2(10);
2452 l_node_query varchar2(10);
2453
2454 cursor team_cursor is select distinct team_mem.team_id,team_tl.team_name
2455 from jtf_rs_team_members team_mem, jtf_rs_teams_tl team_tl
2456 where team_resource_id = p_resource_id
2457 and team_mem.team_id = team_tl.team_id
2458 and team_tl.language = userenv('LANG');
2459
2460 cursor group_cursor is select distinct group_mem.group_id,group_tl.group_name
2461 from jtf_rs_group_members group_mem, jtf_rs_groups_tl group_tl
2462 where group_mem.resource_id = p_resource_id
2463 and group_mem.group_id = group_tl.group_id
2464 and group_tl.language = userenv('LANG');
2465
2466 l_lookup_code varchar2(30);
2467 l_meaning varchar2(360);--5579863
2468 l_level number;
2469 l_res_cat_enum_flag varchar2(1);
2470
2471 cursor all_cursor is select node_label,node_view,cursor_key_col,cursor_sql,
2472 data_source
2473 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
2474 where uwq_b.node_id = uwq_tl.node_id
2475 and uwq_tl.language = userenv('LANG')
2476 and parent_id = l_node_id
2477 and node_query='CURSOR' and enabled_flag='Y';
2478
2479 cursor node_cursor is select 'Node ',
2480 node_view,where_clause,data_source,level,node_query,cursor_sql,
2481 uwq_b.node_id,res_cat_enum_flag
2482 from cs_sr_uwq_nodes_b uwq_b
2483 where node_id > 9999 and node_query='SINGLE' and enabled_flag='Y'
2484 and ( parent_id is null or parent_id > 9999 )
2485 start with uwq_b.parent_id is null
2486 connect by prior uwq_b.node_id = uwq_b.parent_id;
2487
2488 cursor seed_cursor is select 'Node',
2489 node_view,where_clause,data_source,node_query,cursor_sql,
2490 uwq_b.node_id,res_cat_enum_flag,level,nvl(parent_id,-1)
2491 from cs_sr_uwq_nodes_b uwq_b
2492 where enabled_flag='Y'
2493 --where node_id < 1000 and enabled_flag='Y'
2494 and node_query = 'SINGLE'
2495 start with uwq_b.parent_id is null
2496 connect by prior uwq_b.node_id = uwq_b.parent_id;
2497
2498 cursor cur_seed_cursor is select node_label,
2499 node_view,where_clause,data_source,node_query,cursor_sql,
2500 cursor_key_col,node_id,res_cat_enum_flag
2501 from cs_sr_uwq_nodes_vl
2502 where node_id < 1000 and enabled_flag='Y'
2503 and node_query = 'CURSOR';
2504
2505 v_cursorid number;
2506 v_dummy number;
2507
2508 begin
2509
2510 begin
2511 select name into l_sr_name from jtf_objects_vl
2512 where object_code='SR_HTML';
2513 exception
2514 when OTHERS then
2515 l_sr_name := 'SR Error';
2516 end;
2517
2518 l_node_counter := 0;
2519 savepoint start_cs_enum;
2520 l_sr_list(l_node_counter).node_label := fnd_message.get_string('CS','CSZ_CS_MESSAGE');
2521 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_LABEL_V';
2522 l_sr_list(l_node_counter).data_source := 'CS_SR_UWQ_LABEL_DS';
2523 l_sr_list(l_node_counter).media_type_id := '';
2524 l_sr_list(l_node_counter).where_clause := ' incident_id = -1 ';
2525 l_sr_list(l_node_counter).node_type := 0;
2526 l_sr_list(l_node_counter).hide_if_empty := '';
2527 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
2528 l_sr_list(l_node_counter).node_depth := 1;
2529
2530 /* The setting of res_cat_enum_flag='N' makes sure that the default
2531 * * where condition in UWQ is not fired. Instead the where condition
2532 * * defined by l_where_clause is fired */
2533
2534 -- Creation of My Node. All SR assigned directly to the Resource.
2535
2536 open seed_cursor;
2537
2538 loop
2539 fetch seed_cursor into
2540 l_node_label,l_view_name,l_where_clause,l_data_source,
2541 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag,l_level,l_parent_id;
2542
2543 exit when seed_cursor%NOTFOUND;
2544
2545 if l_node_id < 1000 or (l_parent_id < 1000 and l_parent_id > 0) then
2546 -- This means that the node is a seeded node or a child node of
2547 -- another seeded node.
2548
2549 l_node_counter := l_node_counter + 1;
2550
2551 begin
2552 select node_label into l_node_label from cs_sr_uwq_nodes_tl
2553 where node_id=l_node_id and language=userenv('LANG');
2554 exception
2555 when NO_DATA_FOUND then
2556 l_node_label:='Error: No Label ';
2557 end;
2558
2559 l_bind_list(1).bind_var_name := ':owner_id';
2560 l_bind_list(1).bind_var_value := p_resource_id;
2561 l_bind_list(1).bind_var_data_type :='NUMBER';
2562
2563 if (l_parent_id < 1000 and l_parent_id > 0) then
2564 -- This means that the node is a child node and its parent
2565 -- is a seeded node. So the child node will inherit the where
2566 -- condition of the parent.
2567
2568 begin
2569 l_parent_where_clause := null;
2570 select where_clause into l_parent_where_clause from
2571 cs_sr_uwq_nodes_b
2572 where node_id=l_parent_id ;
2573 exception
2574 when NO_DATA_FOUND then
2575 l_parent_where_clause :=' incident_id = -1 ';
2576 end;
2577 if l_where_clause is not null then
2578 l_where_clause := l_parent_where_clause ||' AND '||l_where_clause;
2579 else
2580 l_where_clause := l_parent_where_clause ;
2581 end if;
2582
2583 end if; -- End of if at l_parent_id
2584
2585 l_sr_list(l_node_counter).node_label := l_node_label;
2586 l_sr_list(l_node_counter).data_source := l_data_source;
2587 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2588 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2589 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_EMPLOYEE_DS';
2590 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2591 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2592 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_GROUP_DS';
2593 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2594 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2595 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_TEAM_DS';
2596 END IF;
2597 l_view_name :=l_sr_list(l_node_counter).view_name;
2598 l_data_source := l_sr_list(l_node_counter).data_source;
2599 l_sr_list(l_node_counter).media_type_id := '';
2600 l_sr_list(l_node_counter).where_clause := l_where_clause;
2601 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
2602 l_sr_list(l_node_counter).node_type := 10;
2603 l_sr_list(l_node_counter).hide_if_empty := '';
2604 l_sr_list(l_node_counter).node_depth := l_level+1;
2605 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2606 l_sr_list(l_node_counter).bind_vars := return_value;
2607
2608 begin
2609 -- For Each of the SINGLE defined nodes first check if there
2610 -- exist any CURSOR defined child nodes. If so execute the
2611 -- SQL and build the child nodes.
2612
2613 select node_label,node_view,cursor_key_col,data_source,
2614 node_query,cursor_sql,uwq_b.node_id,res_cat_enum_flag
2615 into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
2616 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag
2617 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
2618 where uwq_b.node_id = uwq_tl.node_id
2619 and uwq_tl.language = userenv('LANG')
2620 and parent_id = l_node_id and enabled_flag='Y'
2621 and node_query = 'CURSOR' ;
2622
2623 if l_cursor_sql is not null then
2624
2625 l_node_counter := l_node_counter + 1;
2626 l_sr_list(l_node_counter).node_label := l_node_label;
2627 l_sr_list(l_node_counter).data_source := l_data_source;
2628 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2629 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2630 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_EMPLOYEE_DS';
2631 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2632 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2633 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_GROUP_DS';
2634 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2635 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2636 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_TEAM_DS';
2637 END IF;
2638 l_view_name :=l_sr_list(l_node_counter).view_name;
2639 l_data_source := l_sr_list(l_node_counter).data_source;
2640 l_sr_list(l_node_counter).media_type_id := '';
2641 l_sr_list(l_node_counter).where_clause := ' incident_id = -101 ';
2642 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
2643 l_sr_list(l_node_counter).node_type := 10;
2644 l_sr_list(l_node_counter).hide_if_empty := '';
2645 l_sr_list(l_node_counter).node_depth := l_level+2;
2646
2650 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
2647 v_cursorid := dbms_sql.open_cursor;
2648 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
2649 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
2651 v_dummy := dbms_sql.execute(v_cursorid);
2652
2653 loop
2654 if dbms_sql.fetch_rows(v_cursorid) = 0 then
2655 exit;
2656 end if;
2657
2658 dbms_sql.column_value(v_cursorid, 1, l_meaning);
2659 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
2660 l_where_clause := l_cursor_key_col||' = :seedsubbindvalue';
2661 l_node_counter := l_node_counter + 1;
2662
2663 l_bind_list(1).bind_var_name := ':seedsubbindvalue';
2664 l_bind_list(1).bind_var_value := l_id_of_value;
2665 l_bind_list(1).bind_var_data_type :='NUMBER';
2666
2667 l_sr_list(l_node_counter).node_label := l_meaning;
2668 l_sr_list(l_node_counter).data_source := l_data_source;
2669 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2670 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_EMPLOYEE_DS';
2671 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2672 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2673 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_GROUP_DS';
2674 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2675 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2676 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_TEAM_DS';
2677 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2678 END IF;
2679 l_view_name :=l_sr_list(l_node_counter).view_name;
2680 l_data_source := l_sr_list(l_node_counter).data_source;
2681 l_sr_list(l_node_counter).media_type_id := '';
2682 l_sr_list(l_node_counter).where_clause := l_where_clause;
2683 l_sr_list(l_node_counter).res_cat_enum_flag :=
2684 l_res_cat_enum_flag;
2685
2686 l_sr_list(l_node_counter).node_type := 12;
2687
2688 l_sr_list(l_node_counter).hide_if_empty := '';
2689 l_sr_list(l_node_counter).node_depth := l_level+3;
2690
2691 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2692 l_sr_list(l_node_counter).bind_vars := return_value;
2693
2694 end loop;
2695
2696 dbms_sql.close_cursor(v_cursorid);
2697
2698 end if;
2699
2700 exception
2701 when NO_DATA_FOUND then
2702 null;
2703 end; -- End of Begin at CURSOR based sub Nodes.
2704
2705 end if; --- End of check for node id and parent id.
2706
2707 end loop; --- End of main loop for seed cursor
2708
2709 close seed_cursor;
2710
2711 -- Creation of Team Node. All SR assigned directly to the Team(s) of the Resource.
2712 -- The first node in the Team Node is a dummy
2713
2714 open cur_seed_cursor;
2715
2716 loop
2717 fetch cur_seed_cursor into l_node_label,l_view_name,
2718 l_where_clause,l_data_source,
2719 l_node_query,l_cursor_sql,l_cursor_key_col,
2720 l_node_id,l_res_cat_enum_flag;
2721
2722 exit when cur_seed_cursor%NOTFOUND;
2723
2724 if l_cursor_sql is not null then
2725
2726 l_bind_list(1).bind_var_name := ':owner_id';
2727 l_bind_list(1).bind_var_value := p_resource_id;
2728 l_bind_list(1).bind_var_data_type :='NUMBER';
2729
2730 l_node_counter := l_node_counter + 1;
2731 l_sr_list(l_node_counter).node_label := l_node_label;
2732 l_sr_list(l_node_counter).data_source := l_data_source;
2733 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2734 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_EMPLOYEE_DS';
2735 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2736 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2737 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_GROUP_DS';
2738 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2739 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2740 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_TEAM_DS';
2741 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2742 END IF;
2743 l_view_name :=l_sr_list(l_node_counter).view_name;
2744 l_data_source := l_sr_list(l_node_counter).data_source;
2745 l_sr_list(l_node_counter).media_type_id := '';
2746 l_sr_list(l_node_counter).where_clause := l_where_clause;
2747 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
2748 l_sr_list(l_node_counter).node_type := 10;
2749 l_sr_list(l_node_counter).hide_if_empty := '';
2750 l_sr_list(l_node_counter).node_depth := 2;
2751 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2752 l_sr_list(l_node_counter).bind_vars := return_value;
2753
2754 v_cursorid := dbms_sql.open_cursor;
2755 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
2756 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
2757 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
2758 dbms_sql.bind_variable(v_cursorid, ':OWNER_ID', p_resource_id);
2759 v_dummy := dbms_sql.execute(v_cursorid);
2760
2761 loop
2762 if dbms_sql.fetch_rows(v_cursorid) = 0 then
2763 exit;
2764 end if;
2765 dbms_sql.column_value(v_cursorid, 1, l_meaning);
2766 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
2767 l_where_clause := l_cursor_key_col||' = :bindvalue';
2768 l_node_counter := l_node_counter + 1;
2769
2770 l_bind_list(1).bind_var_name := ':bindvalue';
2771 l_bind_list(1).bind_var_value := l_id_of_value;
2772 l_bind_list(1).bind_var_data_type :='NUMBER';
2773
2774 l_sr_list(l_node_counter).node_label := l_meaning;
2775 l_sr_list(l_node_counter).view_name := l_view_name;
2776 l_sr_list(l_node_counter).data_source := l_data_source;
2777 l_sr_list(l_node_counter).media_type_id := '';
2778 l_sr_list(l_node_counter).where_clause := l_where_clause;
2779 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
2780 l_sr_list(l_node_counter).node_type := 12;
2781 l_sr_list(l_node_counter).hide_if_empty := '';
2782 l_sr_list(l_node_counter).node_depth := 3;
2783
2784 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2785 l_sr_list(l_node_counter).bind_vars := return_value;
2786 end loop;
2787
2788 dbms_sql.close_cursor(v_cursorid);
2789
2790 end if; -- End of if at cursor_sql is not null
2791 end loop; -- End of loop for cur_seed_cursor
2792
2793 --- Start of personalized single,cursor nodes.
2794 -- This section of code queries the table cs_sr_uwq_nodes_b / tl for all
2795 -- personalized nodes.
2796
2797 open node_cursor;
2798 loop
2799
2800 fetch node_cursor into l_node_label,
2801 l_view_name,n_where_clause,l_data_source,l_level,
2802 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag;
2803 exit when node_cursor%notfound;
2804
2805 l_node_counter := l_node_counter + 1;
2806
2807 begin
2808 select node_label into l_node_label from cs_sr_uwq_nodes_tl
2809 where node_id=l_node_id and language=userenv('LANG');
2810 exception
2811 when NO_DATA_FOUND then
2812 l_node_label:='Error: No Label ';
2813 end;
2814
2815 l_sr_list(l_node_counter).node_label := l_node_label;
2816 l_sr_list(l_node_counter).data_source := l_data_source;
2817 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2818 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2819 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_EMPLOYEE_DS';
2820 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2821 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2822 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_GROUP_DS';
2823 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2824 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2825 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_TEAM_DS';
2826 END IF;
2827 l_view_name :=l_sr_list(l_node_counter).view_name;
2828 l_data_source := l_sr_list(l_node_counter).data_source;
2829 l_sr_list(l_node_counter).media_type_id := '';
2830 l_sr_list(l_node_counter).where_clause := n_where_clause;
2831 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
2832 l_sr_list(l_node_counter).node_type := 10;
2833 l_sr_list(l_node_counter).hide_if_empty := '';
2834 l_sr_list(l_node_counter).node_depth := l_level+1;
2835
2836 if instr(lower(n_where_clause), ':owner_id') <> 0 then
2837 l_bind_list(1).bind_var_name := ':owner_id';
2838 l_bind_list(1).bind_var_value := p_resource_id;
2839 l_bind_list(1).bind_var_data_type :='NUMBER';
2840 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2841 l_sr_list(l_node_counter).bind_vars := return_value;
2842 end if;
2843
2844 begin
2845 -- For Each of the SINGLE defined nodes first check if there
2846 -- exist any CURSOR defined child nodes. If so execute the
2847 -- SQL and build the child nodes.
2848 select node_label,node_view,cursor_key_col,data_source,
2849 node_query,cursor_sql,uwq_b.node_id
2850 , res_cat_enum_flag
2851 into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
2852 l_node_query,l_cursor_sql,l_node_id
2853 , l_res_cat_enum_flag
2854 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
2855 where uwq_b.node_id = uwq_tl.node_id
2856 and uwq_tl.language = userenv('LANG')
2857 and parent_id = l_node_id and enabled_flag='Y'
2858 and node_query = 'CURSOR' ;
2859
2860 if l_cursor_sql is not null then
2861
2862 l_node_counter := l_node_counter + 1;
2863 l_sr_list(l_node_counter).node_label := l_node_label;
2864 l_sr_list(l_node_counter).data_source := l_data_source;
2865 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
2866 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
2867 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_EMPLOYEE_DS';
2868 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
2869 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
2870 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_GROUP_DS';
2871 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
2872 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
2873 l_sr_list(l_node_counter).data_source := 'CS_SR_HTML_UWQ_TEAM_DS';
2874 END IF;
2875 l_view_name :=l_sr_list(l_node_counter).view_name;
2876 l_data_source := l_sr_list(l_node_counter).data_source;
2877 l_sr_list(l_node_counter).media_type_id := '';
2878 l_sr_list(l_node_counter).where_clause := ' incident_id = -1 ';
2879 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
2880 l_sr_list(l_node_counter).node_type := 10;
2881 l_sr_list(l_node_counter).hide_if_empty := '';
2882 l_sr_list(l_node_counter).node_depth := l_level+2;
2883
2884 v_cursorid := dbms_sql.open_cursor;
2885 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
2886 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
2887 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
2888 v_dummy := dbms_sql.execute(v_cursorid);
2889
2890 loop
2891 if dbms_sql.fetch_rows(v_cursorid) = 0 then
2892 exit;
2893 end if;
2894
2895 dbms_sql.column_value(v_cursorid, 1, l_meaning);
2896 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
2897 l_where_clause := l_cursor_key_col||' = :customsubbindvalue';
2898 l_node_counter := l_node_counter + 1;
2899
2900 l_bind_list(1).bind_var_name := ':customsubbindvalue';
2901 l_bind_list(1).bind_var_value := l_id_of_value;
2902 l_bind_list(1).bind_var_data_type :='NUMBER';
2903
2904 l_sr_list(l_node_counter).node_label := l_meaning;
2905 l_sr_list(l_node_counter).view_name := l_view_name;
2906 l_sr_list(l_node_counter).data_source := l_data_source;
2907 l_sr_list(l_node_counter).media_type_id := '';
2908 l_sr_list(l_node_counter).where_clause := l_where_clause;
2909 l_sr_list(l_node_counter).res_cat_enum_flag :=
2910 l_res_cat_enum_flag;
2911 l_sr_list(l_node_counter).node_type := 12;
2912 l_sr_list(l_node_counter).hide_if_empty := '';
2913 l_sr_list(l_node_counter).node_depth := l_level+3;
2914
2915 return_value := ieu_pub.set_bind_var_data(l_bind_list);
2916 l_sr_list(l_node_counter).bind_vars := return_value;
2917 end loop;
2918
2919 dbms_sql.close_cursor(v_cursorid);
2920
2921 end if;
2922
2923 exception
2924 when NO_DATA_FOUND then
2925 null;
2926 end; -- End of Begin at CURSOR based sub Nodes.
2927
2928 end loop;
2929 close node_cursor;
2930
2931 ieu_pub.add_uwq_node_data
2932 (p_resource_id,
2933 p_sel_enum_id,
2934 l_sr_list );
2935
2936 exception
2937 when OTHERS then
2938
2939 --prayadur 29-Sep-05 Commented the code below and added
2940 --the following 2 lines for Bug 4434093.
2941 -- l_where_clause := sqlerrm;
2942 ROLLBACK TO start_cs_enum;
2943 RAISE;
2944
2945 end ENUMERATE_SR_HTML_NODES;
2946
2947 procedure ENUMERATE_TICKET_HTML_NODES
2948 (p_resource_id in number,
2949 p_language in varchar2,
2950 p_source_lang in varchar2,
2951 p_sel_enum_id in number) as
2952
2953 l_node_label varchar2(100);
2954 l_sr_list IEU_PUB.EnumeratorDataRecordList;
2955 l_bind_list IEU_PUB.BindVariableRecordList;
2956 l_node_counter number;
2957
2958 l_team_id number;
2959 l_team_name varchar2(30);
2960 l_group_id number;
2961 l_group_name varchar2(60);
2962 l_where_clause varchar2(500);
2963 l_parent_where_clause varchar2(500);
2964 l_sr_name varchar2(30);
2965 return_value varchar2(2000);
2966
2967 l_view_name varchar2(50);
2968 l_data_source varchar2(50);
2969 n_where_clause varchar2(500);
2970 l_cursor_sql varchar2(1500);
2971 l_node_id number;
2972 l_id_of_value number;
2973 l_parent_id number;
2974 l_where_value varchar2(500);
2975 l_cursor_key_col varchar2(30);
2976 l_value_flag varchar2(10);
2977 l_node_query varchar2(10);
2978
2979 cursor team_cursor is select distinct team_mem.team_id,team_tl.team_name
2980 from jtf_rs_team_members team_mem, jtf_rs_teams_tl team_tl
2981 where team_resource_id = p_resource_id
2982 and team_mem.team_id = team_tl.team_id
2983 and team_tl.language = userenv('LANG');
2984
2985 cursor group_cursor is select distinct group_mem.group_id,group_tl.group_name
2986 from jtf_rs_group_members group_mem, jtf_rs_groups_tl group_tl
2987 where group_mem.resource_id = p_resource_id
2988 and group_mem.group_id = group_tl.group_id
2989 and group_tl.language = userenv('LANG');
2990
2991 l_lookup_code varchar2(30);
2992 l_meaning varchar2(360);--5579863
2993 l_level number;
2994 l_res_cat_enum_flag varchar2(1);
2995
2996 cursor all_cursor is select node_label,node_view,cursor_key_col,cursor_sql,
2997 data_source
2998 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
2999 where uwq_b.node_id = uwq_tl.node_id
3000 and uwq_tl.language = userenv('LANG')
3001 and parent_id = l_node_id
3002 and node_query='CURSOR' and enabled_flag='Y';
3003
3004 cursor node_cursor is select 'Node ',
3005 node_view,where_clause,data_source,level,node_query,cursor_sql,
3006 uwq_b.node_id,res_cat_enum_flag
3007 from cs_sr_uwq_nodes_b uwq_b
3008 where node_id > 9999 and node_query='SINGLE' and enabled_flag='Y'
3009 and ( parent_id is null or parent_id > 9999 )
3010 start with uwq_b.parent_id is null
3011 connect by prior uwq_b.node_id = uwq_b.parent_id;
3012
3013 cursor seed_cursor is select 'Node',
3014 node_view,where_clause,data_source,node_query,cursor_sql,
3015 uwq_b.node_id,res_cat_enum_flag,level,nvl(parent_id,-1)
3016 from cs_sr_uwq_nodes_b uwq_b
3017 where enabled_flag='Y'
3018 --where node_id < 1000 and enabled_flag='Y'
3019 and node_query = 'SINGLE'
3020 start with uwq_b.parent_id is null
3021 connect by prior uwq_b.node_id = uwq_b.parent_id;
3022
3023 cursor cur_seed_cursor is select node_label,
3024 node_view,where_clause,data_source,node_query,cursor_sql,
3025 cursor_key_col,node_id,res_cat_enum_flag
3026 from cs_sr_uwq_nodes_vl
3027 where node_id < 1000 and enabled_flag='Y'
3028 and node_query = 'CURSOR';
3029
3030 v_cursorid number;
3031 v_dummy number;
3032
3033 begin
3034
3035 begin
3036 select name into l_sr_name from jtf_objects_vl
3037 where object_code='SR';
3038 exception
3039 when OTHERS then
3040 l_sr_name := 'SR Error';
3041 end;
3042
3043 l_node_counter := 0;
3044 savepoint start_cs_enum;
3045 l_sr_name :=fnd_message.get_string('CS','CSZ_ITIL_MESSAGE');
3046 l_sr_list(l_node_counter).node_label := l_sr_name;
3047 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_LABEL_V';
3048 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_LABEL_DS';
3049 l_sr_list(l_node_counter).media_type_id := '';
3050 l_sr_list(l_node_counter).where_clause := ' incident_id = -1 ';
3051 l_sr_list(l_node_counter).node_type := 0;
3052 l_sr_list(l_node_counter).hide_if_empty := '';
3053 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
3054 l_sr_list(l_node_counter).node_depth := 1;
3055
3056 /* The setting of res_cat_enum_flag='N' makes sure that the default
3057 * * where condition in UWQ is not fired. Instead the where condition
3058 * * defined by l_where_clause is fired */
3059
3060 -- Creation of My Node. All SR assigned directly to the Resource.
3061
3062 open seed_cursor;
3063
3064 loop
3065 fetch seed_cursor into
3066 l_node_label,l_view_name,l_where_clause,l_data_source,
3067 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag,l_level,l_parent_id;
3068
3069 exit when seed_cursor%NOTFOUND;
3070
3071 if l_node_id < 1000 or (l_parent_id < 1000 and l_parent_id > 0) then
3072 -- This means that the node is a seeded node or a child node of
3073 -- another seeded node.
3074
3075 l_node_counter := l_node_counter + 1;
3076
3077 begin
3078 select node_label into l_node_label from cs_sr_uwq_nodes_tl
3079 where node_id=l_node_id and language=userenv('LANG');
3080 exception
3081 when NO_DATA_FOUND then
3082 l_node_label:='Error: No Label ';
3083 end;
3084
3085 l_bind_list(1).bind_var_name := ':owner_id';
3086 l_bind_list(1).bind_var_value := p_resource_id;
3087 l_bind_list(1).bind_var_data_type :='NUMBER';
3088
3089 if (l_parent_id < 1000 and l_parent_id > 0) then
3090 -- This means that the node is a child node and its parent
3091 -- is a seeded node. So the child node will inherit the where
3092 -- condition of the parent.
3093
3094 begin
3095 l_parent_where_clause := null;
3096 select where_clause into l_parent_where_clause from
3097 cs_sr_uwq_nodes_b
3098 where node_id=l_parent_id ;
3099 exception
3100 when NO_DATA_FOUND then
3101 l_parent_where_clause :=' incident_id = -1 ';
3102 end;
3103 if l_where_clause is not null then
3104 l_where_clause := l_parent_where_clause ||' AND '||l_where_clause;
3105 else
3106 l_where_clause := l_parent_where_clause ;
3107 end if;
3108
3109 end if; -- End of if at l_parent_id
3110
3111 If (l_node_label='My Service Request') THEN
3112 l_node_label :=fnd_message.get_string('CS','CS_SR_UWQ_MY_ITIL_NODE');
3113 end if;
3114 l_sr_list(l_node_counter).node_label := l_node_label;
3115 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
3116 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
3117 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_EMPLOYEE_DS';
3118 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
3119 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
3120 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_GROUP_DS';
3121 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
3122 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
3123 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_TEAM_DS';
3124 END IF;
3125 l_data_source := l_sr_list(l_node_counter).data_source;
3126 l_view_name :=l_sr_list(l_node_counter).view_name;
3127 l_sr_list(l_node_counter).media_type_id := '';
3128 l_sr_list(l_node_counter).where_clause := l_where_clause;
3129 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
3130 l_sr_list(l_node_counter).node_type := 10;
3131 l_sr_list(l_node_counter).hide_if_empty := '';
3132 l_sr_list(l_node_counter).node_depth := l_level+1;
3133 return_value := ieu_pub.set_bind_var_data(l_bind_list);
3134 l_sr_list(l_node_counter).bind_vars := return_value;
3135
3136 begin
3137 -- For Each of the SINGLE defined nodes first check if there
3138 -- exist any CURSOR defined child nodes. If so execute the
3139 -- SQL and build the child nodes.
3140
3141 select node_label,node_view,cursor_key_col,data_source,
3142 node_query,cursor_sql,uwq_b.node_id,res_cat_enum_flag
3143 into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
3144 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag
3145 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
3146 where uwq_b.node_id = uwq_tl.node_id
3147 and uwq_tl.language = userenv('LANG')
3148 and parent_id = l_node_id and enabled_flag='Y'
3149 and node_query = 'CURSOR' ;
3150
3151 if l_cursor_sql is not null then
3152
3153 l_node_counter := l_node_counter + 1;
3154 l_sr_list(l_node_counter).node_label := l_node_label;
3155 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
3156 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_EMPLOYEE_DS';
3157 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
3158 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
3159 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_GROUP_DS';
3160 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
3161 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
3162 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_TEAM_DS';
3163 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
3164 END IF;
3165 l_data_source := l_sr_list(l_node_counter).data_source;
3166 l_view_name :=l_sr_list(l_node_counter).view_name;
3167 l_sr_list(l_node_counter).media_type_id := '';
3168 l_sr_list(l_node_counter).where_clause := ' incident_id = -101 ';
3169 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
3170 l_sr_list(l_node_counter).node_type := 10;
3171 l_sr_list(l_node_counter).hide_if_empty := '';
3172 l_sr_list(l_node_counter).node_depth := l_level+2;
3173
3174 v_cursorid := dbms_sql.open_cursor;
3175 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
3176 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
3177 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
3178 v_dummy := dbms_sql.execute(v_cursorid);
3179
3180 loop
3181 if dbms_sql.fetch_rows(v_cursorid) = 0 then
3182 exit;
3183 end if;
3184
3185 dbms_sql.column_value(v_cursorid, 1, l_meaning);
3186 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
3187 l_where_clause := l_cursor_key_col||' = :seedsubbindvalue';
3188 l_node_counter := l_node_counter + 1;
3189
3190 l_bind_list(1).bind_var_name := ':seedsubbindvalue';
3191 l_bind_list(1).bind_var_value := l_id_of_value;
3192 l_bind_list(1).bind_var_data_type :='NUMBER';
3193
3194 l_sr_list(l_node_counter).node_label := l_meaning;
3195 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
3196 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_EMPLOYEE_DS';
3197 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
3198 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
3199 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_GROUP_DS';
3200 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
3201 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
3202 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_TEAM_DS';
3203 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
3204 END IF;
3205 l_data_source := l_sr_list(l_node_counter).data_source;
3206 l_view_name :=l_sr_list(l_node_counter).view_name;
3207 l_sr_list(l_node_counter).media_type_id := '';
3208 l_sr_list(l_node_counter).where_clause := l_where_clause;
3209 l_sr_list(l_node_counter).res_cat_enum_flag :=
3210 l_res_cat_enum_flag;
3211
3212 l_sr_list(l_node_counter).node_type := 12;
3213
3214 l_sr_list(l_node_counter).hide_if_empty := '';
3215 l_sr_list(l_node_counter).node_depth := l_level+3;
3216
3217 return_value := ieu_pub.set_bind_var_data(l_bind_list);
3218 l_sr_list(l_node_counter).bind_vars := return_value;
3219
3220 end loop;
3221
3222 dbms_sql.close_cursor(v_cursorid);
3223
3224 end if;
3225
3226 exception
3227 when NO_DATA_FOUND then
3228 null;
3229 end; -- End of Begin at CURSOR based sub Nodes.
3230
3231 end if; --- End of check for node id and parent id.
3232
3233 end loop; --- End of main loop for seed cursor
3234
3235 close seed_cursor;
3236
3237 -- Creation of Team Node. All SR assigned directly to the Team(s) of the Resource.
3238 -- The first node in the Team Node is a dummy
3239
3240 open cur_seed_cursor;
3241
3242 loop
3243 fetch cur_seed_cursor into l_node_label,l_view_name,
3244 l_where_clause,l_data_source,
3245 l_node_query,l_cursor_sql,l_cursor_key_col,
3246 l_node_id,l_res_cat_enum_flag;
3247
3248 exit when cur_seed_cursor%NOTFOUND;
3249
3250 if l_cursor_sql is not null then
3251
3252 l_bind_list(1).bind_var_name := ':owner_id';
3253 l_bind_list(1).bind_var_value := p_resource_id;
3254 l_bind_list(1).bind_var_data_type :='NUMBER';
3255
3256 l_node_counter := l_node_counter + 1;
3257 l_sr_list(l_node_counter).node_label := l_node_label;
3258 l_sr_list(l_node_counter).view_name := l_view_name;
3259 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
3260 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_EMPLOYEE_DS';
3261 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
3262 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
3263 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_GROUP_DS';
3264 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
3265 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
3266 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_TEAM_DS';
3267 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
3268 END IF;
3269 l_data_source := l_sr_list(l_node_counter).data_source;
3270 l_view_name :=l_sr_list(l_node_counter).view_name;
3271 l_sr_list(l_node_counter).media_type_id := '';
3272 l_sr_list(l_node_counter).where_clause := l_where_clause;
3273 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
3274 l_sr_list(l_node_counter).node_type := 10;
3275 l_sr_list(l_node_counter).hide_if_empty := '';
3276 l_sr_list(l_node_counter).node_depth := 2;
3277 return_value := ieu_pub.set_bind_var_data(l_bind_list);
3278 l_sr_list(l_node_counter).bind_vars := return_value;
3279
3280 v_cursorid := dbms_sql.open_cursor;
3281 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
3282 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
3283 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
3284 dbms_sql.bind_variable(v_cursorid, ':OWNER_ID', p_resource_id);
3285 v_dummy := dbms_sql.execute(v_cursorid);
3286
3287 loop
3288 if dbms_sql.fetch_rows(v_cursorid) = 0 then
3289 exit;
3290 end if;
3291 dbms_sql.column_value(v_cursorid, 1, l_meaning);
3292 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
3293 l_where_clause := l_cursor_key_col||' = :bindvalue';
3294 l_node_counter := l_node_counter + 1;
3295
3296 l_bind_list(1).bind_var_name := ':bindvalue';
3297 l_bind_list(1).bind_var_value := l_id_of_value;
3298 l_bind_list(1).bind_var_data_type :='NUMBER';
3299
3300 l_sr_list(l_node_counter).node_label := l_meaning;
3301 l_sr_list(l_node_counter).view_name := l_view_name;
3302 l_sr_list(l_node_counter).data_source := l_data_source;
3303 l_sr_list(l_node_counter).media_type_id := '';
3304 l_sr_list(l_node_counter).where_clause := l_where_clause;
3305 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
3306 l_sr_list(l_node_counter).node_type := 12;
3307 l_sr_list(l_node_counter).hide_if_empty := '';
3308 l_sr_list(l_node_counter).node_depth := 3;
3309
3310 return_value := ieu_pub.set_bind_var_data(l_bind_list);
3311 l_sr_list(l_node_counter).bind_vars := return_value;
3312 end loop;
3313
3314 dbms_sql.close_cursor(v_cursorid);
3315
3316 end if; -- End of if at cursor_sql is not null
3317 end loop; -- End of loop for cur_seed_cursor
3318
3319 --- Start of personalized single,cursor nodes.
3320 -- This section of code queries the table cs_sr_uwq_nodes_b / tl for all
3321 -- personalized nodes.
3322
3323 open node_cursor;
3324 loop
3325
3326 fetch node_cursor into l_node_label,
3327 l_view_name,n_where_clause,l_data_source,l_level,
3328 l_node_query,l_cursor_sql,l_node_id,l_res_cat_enum_flag;
3329 exit when node_cursor%notfound;
3330
3331 l_node_counter := l_node_counter + 1;
3332
3333 begin
3334 select node_label into l_node_label from cs_sr_uwq_nodes_tl
3335 where node_id=l_node_id and language=userenv('LANG');
3336 exception
3337 when NO_DATA_FOUND then
3338 l_node_label:='Error: No Label ';
3339 end;
3340
3341 l_sr_list(l_node_counter).node_label := l_node_label;
3342 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
3343 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_EMPLOYEE_DS';
3344 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
3345 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
3346 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_GROUP_DS';
3347 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
3348 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
3349 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_TEAM_DS';
3350 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
3351 END IF;
3352 l_data_source := l_sr_list(l_node_counter).data_source;
3353 l_view_name :=l_sr_list(l_node_counter).view_name;
3354 l_sr_list(l_node_counter).media_type_id := '';
3355 l_sr_list(l_node_counter).where_clause := n_where_clause;
3356 l_sr_list(l_node_counter).res_cat_enum_flag := l_res_cat_enum_flag;
3357 l_sr_list(l_node_counter).node_type := 10;
3358 l_sr_list(l_node_counter).hide_if_empty := '';
3359 l_sr_list(l_node_counter).node_depth := l_level+1;
3360
3361 if instr(lower(n_where_clause), ':owner_id') <> 0 then
3362 l_bind_list(1).bind_var_name := ':owner_id';
3363 l_bind_list(1).bind_var_value := p_resource_id;
3364 l_bind_list(1).bind_var_data_type :='NUMBER';
3365 return_value := ieu_pub.set_bind_var_data(l_bind_list);
3366 l_sr_list(l_node_counter).bind_vars := return_value;
3367 end if;
3368
3369 begin
3370 -- For Each of the SINGLE defined nodes first check if there
3371 -- exist any CURSOR defined child nodes. If so execute the
3372 -- SQL and build the child nodes.
3373 select node_label,node_view,cursor_key_col,data_source,
3374 node_query,cursor_sql,uwq_b.node_id
3375 , res_cat_enum_flag
3376 into l_node_label,l_view_name,l_cursor_key_col,l_data_source,
3377 l_node_query,l_cursor_sql,l_node_id
3378 , l_res_cat_enum_flag
3379 from cs_sr_uwq_nodes_b uwq_b, cs_sr_uwq_nodes_tl uwq_tl
3380 where uwq_b.node_id = uwq_tl.node_id
3381 and uwq_tl.language = userenv('LANG')
3382 and parent_id = l_node_id and enabled_flag='Y'
3383 and node_query = 'CURSOR' ;
3384
3385 if l_cursor_sql is not null then
3386
3387 l_node_counter := l_node_counter + 1;
3388 l_sr_list(l_node_counter).node_label := l_node_label;
3389 IF l_data_source = 'CS_SR_UWQ_EMPLOYEE_DS' THEN
3390 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_EMPLOYEE_DS';
3391 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_EMPLOYEE_V';
3392 ELSIF l_data_source = 'CS_SR_UWQ_GROUP_DS' THEN
3393 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_GROUP_DS';
3394 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_GROUP_V';
3395 ELSIF l_data_source = 'CS_SR_UWQ_TEAM_DS' THEN
3396 l_sr_list(l_node_counter).data_source := 'CS_TICKET_UWQ_TEAM_DS';
3397 l_sr_list(l_node_counter).view_name := 'CS_SR_UWQ_HTML_TEAM_V';
3398 END IF;
3399 l_data_source := l_sr_list(l_node_counter).data_source;
3400 l_view_name :=l_sr_list(l_node_counter).view_name;
3401 l_sr_list(l_node_counter).media_type_id := '';
3402 l_sr_list(l_node_counter).where_clause := ' incident_id = -1 ';
3403 l_sr_list(l_node_counter).res_cat_enum_flag := 'N';
3404 l_sr_list(l_node_counter).node_type := 10;
3405 l_sr_list(l_node_counter).hide_if_empty := '';
3406 l_sr_list(l_node_counter).node_depth := l_level+2;
3407
3408 v_cursorid := dbms_sql.open_cursor;
3409 dbms_sql.parse(v_cursorid, l_cursor_sql, DBMS_SQL.V7);
3410 dbms_sql.define_column(v_cursorid, 1, l_meaning, 360);--5579863
3411 dbms_sql.define_column(v_cursorid, 2, l_id_of_value);
3412 v_dummy := dbms_sql.execute(v_cursorid);
3413
3414 loop
3415 if dbms_sql.fetch_rows(v_cursorid) = 0 then
3416 exit;
3417 end if;
3418
3419 dbms_sql.column_value(v_cursorid, 1, l_meaning);
3420 dbms_sql.column_value(v_cursorid, 2, l_id_of_value);
3421 l_where_clause := l_cursor_key_col||' = :customsubbindvalue';
3422 l_node_counter := l_node_counter + 1;
3423
3424 l_bind_list(1).bind_var_name := ':customsubbindvalue';
3425 l_bind_list(1).bind_var_value := l_id_of_value;
3426 l_bind_list(1).bind_var_data_type :='NUMBER';
3427
3428 l_sr_list(l_node_counter).node_label := l_meaning;
3429 l_sr_list(l_node_counter).view_name := l_view_name;
3430 l_sr_list(l_node_counter).data_source := l_data_source;
3431 l_sr_list(l_node_counter).media_type_id := '';
3432 l_sr_list(l_node_counter).where_clause := l_where_clause;
3433 l_sr_list(l_node_counter).res_cat_enum_flag :=
3434 l_res_cat_enum_flag;
3435 l_sr_list(l_node_counter).node_type := 12;
3436 l_sr_list(l_node_counter).hide_if_empty := '';
3437 l_sr_list(l_node_counter).node_depth := l_level+3;
3438
3439 return_value := ieu_pub.set_bind_var_data(l_bind_list);
3440 l_sr_list(l_node_counter).bind_vars := return_value;
3441 end loop;
3442
3443 dbms_sql.close_cursor(v_cursorid);
3444
3445 end if;
3446
3447 exception
3448 when NO_DATA_FOUND then
3449 null;
3450 end; -- End of Begin at CURSOR based sub Nodes.
3451
3452 end loop;
3453 close node_cursor;
3454
3455 ieu_pub.add_uwq_node_data
3456 (p_resource_id,
3457 p_sel_enum_id,
3458 l_sr_list );
3459
3460 exception
3461 when OTHERS then
3462
3463 --prayadur 29-Sep-05 Commented the code below and added
3464 --the following 2 lines for Bug 4434093.
3465 -- l_where_clause := sqlerrm;
3466 ROLLBACK TO start_cs_enum;
3467 RAISE;
3468
3469 end ENUMERATE_TICKET_HTML_NODES;
3470 PROCEDURE SR_HTML_UWQ_NONMEDIA_ACTIONS(p_ieu_action_data IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
3471 x_action_type OUT NOCOPY NUMBER,
3472 x_action_name OUT NOCOPY varchar2,
3473 x_action_param OUT NOCOPY varchar2,
3474 x_msg_name OUT NOCOPY varchar2,
3475 x_msg_param OUT NOCOPY varchar2,
3476 x_dialog_style OUT NOCOPY number,
3477 x_msg_appl_short_name OUT NOCOPY varchar2) IS
3478
3479 l_Req_id number;
3480 l_Task_id NUMBER;
3481 CURSOR SR_Cur is
3482 Select Inc.Incident_id
3483 from Jtf_tasks_b Tsk, CS_incidents_all_b Inc
3484 where Tsk.Task_id=l_Task_id
3485 and Inc.Incident_Id=Tsk.source_object_id;
3486 BEGIN
3487 FOR i IN p_ieu_action_data.first.. p_ieu_action_data.last
3488 LOOP
3489 IF ( upper(p_ieu_action_data(i).param_name) = 'TASK_ID' ) then
3490 l_task_id := p_ieu_action_data(i).param_value;
3491 END IF;
3492 END LOOP;
3493 IF l_task_id IS NOT NULL THEN
3494 OPEN SR_cur;
3495 FETCH SR_Cur INTO l_Req_id;
3496 IF SR_cur%NOTFOUND THEN
3497 NULL;
3498 ELSE
3499 x_action_param := 'cszIncidentId='||l_Req_id ;
3500 END IF;
3501 CLOSE SR_cur;
3502 END IF;
3503 x_action_name := 'CSZ_SR_UP_FN';
3504 x_action_type := 1;
3505 x_msg_name := 'NULL' ;
3506 x_msg_param := 'NULL' ;
3507 x_dialog_style := 1;
3508 x_msg_appl_short_name := 'NULL' ;
3509 EXCEPTION
3510 WHEN OTHERS THEN
3511 NULL;
3512 END SR_HTML_UWQ_NONMEDIA_ACTIONS;
3513
3514
3515 END SR_UWQ_INTEG; -- End of Package