[Home] [Help]
PACKAGE BODY: APPS.SR_UWQ_INTEG
Source
1 PACKAGE BODY SR_UWQ_INTEG AS
2 /* $Header: cssruwqb.pls 120.3 2008/03/21 05:03:20 bkanimoz 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
254 sr_uwq_integ.validate_ivr_parameter(
255 l_sr_uwq_parameter,
256 l_sr_uwq_param_value,
257 l_sr_uwq_param_message,
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,
385 l_node_label varchar2(100);
382 p_source_lang in varchar2,
383 p_sel_enum_id in number) as
384
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;
521 -- This means that the node is a child node and its parent
518 l_bind_list(1).bind_var_data_type :='NUMBER';
519
520 if (l_parent_id < 1000 and l_parent_id > 0) then
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
636 open cur_seed_cursor;
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
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
753 , l_res_cat_enum_flag
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
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
890 using out n_count, in p_resource_id;
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
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,
1004 creation_date,
1001 cursor_sql,
1002 cursor_key_col,
1003 enabled_flag,
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,
1162 and oe.order_category_code in ('RETURN','MIXED')
1159 cs_estimate_details chg,
1160 cs_incidents_all_b inc
1161 where oe.order_number = p_parameter_value
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
1196 --Validate the Account Number.
1197 cursor account_cursor is
1198 select acc.cust_account_id,acc.party_id,
1199 party.party_type
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;
1247 x_parameter_flag := 'VALID';
1244 if inc_cursor%NOTFOUND then
1245 l_parameter_id := -1;
1246 else
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
1346 /* For all other incoming calls with Phone Number or ANI we shall do
1347 a regular Call to Inquiry scenario */
1348
1349 open phone_cursor;
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
1384
1381 end if;
1382
1383 close rma_cursor;
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
1546 INTO n_user_id
1543 -- will be set to N.
1544
1545 SELECT 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) is
1629
1630 v_true varchar2(5);
1631 v_false varchar2(5);
1632 v_ret_sts_failure varchar2(1);
1633 p_media_rec JTF_IH_PUB.media_rec_type;
1634
1635 begin
1636
1637 v_true := cs_core_util.get_g_true;
1638 v_false := cs_core_util.get_g_false;
1639 v_ret_sts_failure := 'E';
1640
1641 p_media_rec.media_id := NULL;
1642 p_media_rec.media_item_type := 'TELEPHONE';
1643 p_media_rec.start_date_time := sysdate;
1644 p_media_rec.direction := 'OUTBOUND';
1645
1646 jtf_ih_pub.open_mediaitem( p_api_version => 1.0,
1647 p_init_msg_list => v_true,
1648 p_commit => v_true,
1649 p_resp_appl_id => p_resp_appl_id,
1650 p_resp_id => p_resp_id,
1651 p_user_id => p_user_id,
1652 p_login_id => p_login_id,
1653 x_return_status => x_return_status,
1654 x_msg_count => x_msg_count,
1655 x_msg_data => x_msg_data,
1656 p_media_rec => p_media_rec,
1657 x_media_id => x_media_id);
1658
1659 if x_media_id is null then
1660 x_return_status := v_ret_sts_failure;
1661 end if;
1662
1663 end start_media_item;
1664
1665
1666
1667 -- ===========================================================
1668 -- PRAYADUR 04/28/2004
1669 -- Added the procedure SR_UWQ_NONMEDIA_ACTIONS for Bug 3357706.
1670 -- This is a Non media Action function used to pass the
1674 -- ===========================================================
1671 -- Default_Tab Parameter to SR form. This Function is mapped
1672 -- to the Action object Code 'SR' and it invokes the SR form
1673 -- with Default Tab as Tasks.
1675
1676 PROCEDURE SR_UWQ_NONMEDIA_ACTIONS(p_ieu_action_data IN SYSTEM.IEU_UWQ_MEDIA_DATA_NST,
1677 x_action_type OUT NOCOPY NUMBER,
1678 x_action_name OUT NOCOPY varchar2,
1679 x_action_param OUT NOCOPY varchar2,
1680 x_msg_name OUT NOCOPY varchar2,
1681 x_msg_param OUT NOCOPY varchar2,
1682 x_dialog_style OUT NOCOPY number,
1683 x_msg_appl_short_name OUT NOCOPY varchar2) IS
1684
1685
1686 l_Req_Number Varchar2(64);
1687 l_Task_id NUMBER;
1688
1689 CURSOR SR_Cur is
1690 Select Inc.Incident_number
1691 from Jtf_tasks_b Tsk, CS_incidents_all_b Inc
1692 where Tsk.Task_id=l_Task_id
1693 and Inc.Incident_Id=Tsk.source_object_id;
1694
1695
1696 BEGIN
1697
1698 FOR i IN p_ieu_action_data.first.. p_ieu_action_data.last
1699
1700 LOOP
1701
1702 IF ( upper(p_ieu_action_data(i).param_name) = 'TASK_ID' ) then
1703
1704 l_task_id := p_ieu_action_data(i).param_value;
1705
1706 END IF;
1707
1708 END LOOP;
1709
1710 IF l_task_id IS NOT NULL THEN
1711
1712 OPEN SR_cur;
1713
1714 FETCH SR_Cur INTO l_Req_Number;
1715
1716 IF SR_cur%NOTFOUND THEN
1717
1718 NULL;
1719
1720 ELSE
1721
1722 x_action_param := 'REQUEST_NUMBER="' || l_Req_Number||'"' ;
1723
1724 x_action_param :=x_action_param ||'DEFAULT_TAB="TASKS"';
1725 -- For Bug 6901209
1726 x_action_param :=x_action_param ||'REQUEST_TASK_ID="' || l_task_id||'"';
1727
1728 END IF;
1729
1730 CLOSE SR_cur;
1731
1732 END IF;
1733
1734 x_action_name := 'CSXSRISR' ;
1735 x_action_type := 1;
1736 x_msg_name := 'NULL' ;
1737 x_msg_param := 'NULL' ;
1738 x_dialog_style := 1;
1739 x_msg_appl_short_name := 'NULL' ;
1740
1741
1742 EXCEPTION
1743 WHEN OTHERS THEN
1744 NULL;
1745
1746 END SR_UWQ_NONMEDIA_ACTIONS ;
1747
1748
1749 procedure create_service_request(
1750 p_api_version IN NUMBER,
1751 p_init_msg_list IN VARCHAR2,
1752 p_commit IN VARCHAR2,
1753 x_return_status OUT NOCOPY VARCHAR2,
1754 x_msg_count OUT NOCOPY NUMBER,
1755 x_msg_data OUT NOCOPY VARCHAR2,
1756 p_resp_appl_id IN NUMBER,
1757 p_resp_id IN NUMBER,
1758 p_user_id IN NUMBER,
1759 p_login_id IN NUMBER,
1760 p_org_id IN NUMBER,
1761 p_request_id IN NUMBER,
1762 p_request_number IN VARCHAR2,
1763 sr_type IN VARCHAR2,
1764 summary IN VARCHAR2,
1765 severity_id IN VARCHAR2,
1766 urgency_id IN VARCHAR2,
1767 customer_id IN VARCHAR2,
1768 customer_type IN VARCHAR2,
1769 account_id IN VARCHAR2,
1770 note_type IN VARCHAR2,
1771 note IN VARCHAR2,
1772 -- contact_id IN VARCHAR2,
1773 -- contact_point_id IN VARCHAR2,
1774 -- primary_flag IN VARCHAR2,
1775 -- contact_point_type IN VARCHAR2,
1776 -- contact_type IN VARCHAR2,
1777 p_auto_assign IN VARCHAR2,
1778 p_auto_generate_tasks IN VARCHAR2,
1779 x_service_request_number OUT NOCOPY NUMBER,
1780 p_default_contract_sla_ind IN VARCHAR2,
1781 p_default_coverage_template_id IN NUMBER) is
1782
1783 l_auto_generate_tasks VARCHAR2(1) := 'N';
1784 x_msg_index_out NUMBER;
1785
1786 subtype r_service_request_rec_type is CS_SERVICEREQUEST_PUB.service_request_rec_type;
1787 r_service_request_rec r_service_request_rec_type;
1788
1789 subtype t_notes_table_type is CS_ServiceRequest_PUB.notes_table;
1790 t_notes_table t_notes_table_type;
1791
1792 subtype t_contacts_table_type is CS_ServiceRequest_PUB.contacts_table;
1793 t_contacts_table t_contacts_table_type;
1794
1795 subtype o_sr_create_out_rec_type is CS_SERVICEREQUEST_PUB.SR_CREATE_OUT_REC_TYPE;
1796 o_sr_create_out_rec o_sr_create_out_rec_type;
1797
1798 BEGIN
1799 CS_SERVICEREQUEST_PUB.initialize_rec(r_service_request_rec);
1800
1801 r_service_request_rec.status_id := '1';
1802 r_service_request_rec.customer_id := customer_id;
1803 r_service_request_rec.caller_type := customer_type;
1804 r_service_request_rec.account_id := account_id;
1805 r_service_request_rec.request_date := sysdate;
1806 r_service_request_rec.type_id := sr_type;
1807 r_service_request_rec.summary := summary;
1808 r_service_request_rec.severity_id := severity_id;
1809 r_service_request_rec.urgency_id := urgency_id;
1810 t_notes_table(0).note := note;
1811 t_notes_table(0).note_type := note_type;
1812 -- t_contacts_table(0).party_id := contact_id;
1813 -- t_contacts_table(0).contact_point_id := contact_point_id;
1814 -- t_contacts_table(0).primary_flag := primary_flag;
1815 -- t_contacts_table(0).contact_point_type := contact_point_type;
1816 -- t_contacts_table(0).contact_type := contact_type;
1817
1818 if (fnd_profile.value('CS_SR_AUTO_TASK_CREATE') = 'TASK_TMPL') then
1819 l_auto_generate_tasks := 'Y';
1820 else
1821 l_auto_generate_tasks := 'N';
1822 end if;
1823
1824 CS_ServiceRequest_PUB.Create_ServiceRequest(
1825 p_api_version => p_api_version,
1826 p_init_msg_list => p_init_msg_list,
1827 p_commit => p_commit,
1828 x_return_status => x_return_status,
1829 x_msg_count => x_msg_count,
1830 x_msg_data => x_msg_data,
1831 p_resp_appl_id => p_resp_appl_id,
1832 p_resp_id => p_resp_id,
1833 p_user_id => p_user_id,
1834 p_login_id => p_login_id,
1835 p_org_id => p_org_id,
1836 p_request_id => p_request_id,
1837 p_request_number => p_request_number,
1838 p_service_request_rec => r_service_request_rec,
1839 p_notes => t_notes_table,
1840 p_contacts => t_contacts_table,
1841 p_auto_assign => nvl(fnd_profile.value('CS_AUTO_ASSIGN_OWNER_FORMS'),'N'),
1842 p_auto_generate_tasks => l_auto_generate_tasks,
1843 x_sr_create_out_rec => o_sr_create_out_rec,
1844 p_default_contract_sla_ind => p_default_contract_sla_ind,
1845 p_default_coverage_template_id => p_default_coverage_template_id);
1846
1847 IF ( x_return_status ) = 'S' THEN
1848 x_service_request_number := o_sr_create_out_rec.request_number;
1849 END IF;
1850
1851 end create_service_request;
1852
1853
1854 PROCEDURE Build_Solution_Text_Query(
1855 p_raw_text in varchar2,
1856 p_solution_type_id_tbl in varchar2,
1857 p_search_option in number,
1858 x_solution_text out NOCOPY varchar2)
1859 is
1863
1860 begin
1861 x_solution_text := CS_KNOWLEDGE_PVT.Build_Solution_Text_Query(p_raw_text, NULL, NULL, NULL, p_search_option);
1862 end Build_Solution_Text_Query;
1864
1865 FUNCTION Get_KM_Params_Str(
1866 solution_num in varchar2)
1867 return varchar2
1868 is
1869 begin
1870 return CS_KB_INTEG_CONSTANTS_PKG.getParameterName('SOLUTION_NUM')||'='|| solution_num ||'&'||
1871 CS_KB_INTEG_CONSTANTS_PKG.getParameterName('TASK_PAGE_FUNC')||'=CSZ_TASK_TEMPLATE_CR_FN'||'&'||
1872 'OAPB=CS_KB_SR_BRAND';
1873 end Get_KM_Params_Str;
1874
1875
1876 END SR_UWQ_INTEG; -- End of Package