1 package body csz_servicerequest_util_pvt as
2 /* $Header: cszvutlb.pls 120.12.12020000.6 2013/05/21 14:48:22 lkullamb ship $ */
3 --
4 -- -------------------------------------------------------------------------
5 -- Start of comments
6 -- UTIL Name : GET_USER_NAME
7 -- Type : Private
8 -- Description : Given a USER_ID the function will return the username/partyname.
9 -- Function is used to display the CREATED_BY UserName
10 -- Parameters :
11 -- IN : p_user_id NUMBER Required
12 -- OUT: Returns UserName VARCHAR2(360)
13 -- End of comments
14 -- -------------------------------------------------------------------------
15 FUNCTION GET_USER_NAME
16 ( p_user_id IN NUMBER ) RETURN VARCHAR2 IS
17 CURSOR c_user
18 (p_user_id IN NUMBER
19 )IS SELECT user_name
20 FROM fnd_user
21 WHERE user_id = p_user_id;
22
23 CURSOR c_resource_name
24 (p_user_id IN NUMBER
25 )IS SELECT resource_name
26 FROM jtf_rs_resource_extns_vl
27 WHERE user_id = p_user_id;
28
29 l_user_name VARCHAR2(360);
30 l_name VARCHAR2(240);
31
32 BEGIN
33 IF c_resource_name%ISOPEN
34 THEN
35 CLOSE c_resource_name;
36 END IF;
37
38 OPEN c_resource_name(p_user_id);
39 FETCH c_resource_name INTO l_name;
40
41 IF c_resource_name%ISOPEN
42 THEN
43 CLOSE c_resource_name;
44 END IF;
45
46
47 IF l_name IS NULL
48 THEN
49
50
51 /*****************************************************************************
52 ** Get the UserName from FND_USER
53 *****************************************************************************/
54 IF c_user%ISOPEN
55 THEN
56 CLOSE c_user;
57 END IF;
58
59 OPEN c_user(p_user_id);
60 FETCH c_user INTO l_user_name;
61
62 IF c_user%ISOPEN
63 THEN
64 CLOSE c_user;
65 END IF;
66
67 RETURN l_user_name;
68 ELSE
69 RETURN l_name;
70 END IF;
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 IF c_resource_name%ISOPEN
75 THEN
76 CLOSE c_resource_name;
77 END IF;
78
79 IF c_user%ISOPEN
80 THEN
81 CLOSE c_user;
82 END IF;
83 RETURN 'Not Found';
84 END GET_USER_NAME;
85 --
86 -- -------------------------------------------------------------------------
87 -- Start of comments
88 -- UTIL Name : get_contact_name
89 -- Type : Private
90 -- Description : To get the ContactName based on the ContactPartyId and ContactType
91 -- Parameters :
92 -- IN: p_contact_type IN VARCHAR2 Required
93 -- IN : p_contact_party_id IN NUMBER Required
94 -- IN : p_party_id IN NUMBER Required
95
96 -- Returnvalue:
97 -- l_contact_name VARCHAR2(360)
98 -- End of comments
99 -- -------------------------------------------------------------------------
100 FUNCTION get_contact_name
101 ( p_contact_type IN VARCHAR2
102 ,p_contact_party_id IN NUMBER
103 , p_party_id IN NUMBER
104 ) RETURN VARCHAR2 as
105 --
106 l_contact_name varchar2(360) DEFAULT NULL;
107 l_employee_name varchar2(360) DEFAULT NULL;
108 l_effective_start_date DATE DEFAULT NULL;
109 l_effective_end_date DATE DEFAULT NULL;
110 --
111 cursor c1(param_person_id NUMBER) is
112 select full_name,effective_start_date,effective_end_date
113 from per_all_people_f
114 where person_id = param_person_id
115 order by effective_start_date desc;
116 --
117 begin
118 --
119 if p_contact_type = 'PERSON' then
120 select hz.party_name
121 into l_contact_name
122 from hz_parties hz
123 where hz.party_id = p_party_id;
124 elsif p_contact_type = 'PARTY_RELATIONSHIP' then
125 select hz.party_name
126 into l_contact_name
127 from hz_parties hz, hz_relationships rel
128 where rel.party_id = p_contact_party_id
129 and rel.object_id = p_party_id
130 and rel.subject_id = hz.party_id
131 and rel.subject_type = 'PERSON';
132 elsif p_contact_type = 'EMPLOYEE' then
133 OPEN c1(p_contact_party_id);
134 LOOP
135 FETCH c1 into
136 l_employee_name,l_effective_start_date,l_effective_end_date;
137 EXIT WHEN c1%NOTFOUND;
138 if (l_effective_start_date is not null and l_employee_name is not
139 null) then
140 l_contact_name := l_employee_name;
141 EXIT;
142 end if;
143 END LOOP;
144 CLOSE c1;
145 else
146 return null;
147 end if;
148 return l_contact_name;
149 end;
150 --
151 -- -------------------------------------------------------------------------
152 -- Start of comments
153 -- UTIL Name : GET_SR_JEOPARDY
154 -- Type : Private
155 -- Description : Returns if Service Request is in Jeopardy or not
156 -- Parameters :
157 -- IN : p_incident_id IN NUMBER Required
158 -- IN : p_exp_response_date IN DATE Required
159 -- IN : p_exp_resolution_date IN DATE Required
160 -- IN : p_actual_response_date IN DATE Required
161 -- IN : p_actual_resolution_date IN DATE Required)
162
163 --- Returnvalue:
164 -- l_sr_jeopardy VARCHAR2(10)
165 -- End of comments
166 -- --------------------------------------------------------------------------------
167
168 Function GET_SR_JEOPARDY
169 ( p_incident_id IN NUMBER,
170 p_exp_response_date IN DATE,
171 p_exp_resolution_date IN DATE,
172 p_actual_response_date IN DATE,
173 p_actual_resolution_date IN DATE)
174 RETURN VARCHAR2 IS
175
176 l_sr_jeopardy VARCHAR2(10) := 'NO';
177 l_source_id NUMBER DEFAULT NULL;
178 l_exp_resp_date_buffer NUMBER DEFAULT NULL;
179 l_exp_resoln_date_buffer NUMBER DEFAULT NULL;
180
181 BEGIN
182
183 -- Get the Service:Jeopardy: Expected Response Date Buffer from the Profiles
184 l_exp_resp_date_buffer := fnd_profile.value('CS_CSY_JPARDY_REACT_DATE_BUFFER');
185 -- Get the Service: Jeopardy: Expected Resolution Date Buffer from the Profiles
186 l_exp_resoln_date_buffer := fnd_profile.value('CS_CSY_JPARDY_RESL_DATE_BUFFER');
187
188 if(
189 ((l_exp_resp_date_buffer is not null) and (p_actual_response_date is null) and (p_exp_response_date is not null) and (p_actual_resolution_date is null) and ((p_exp_response_date - sysdate) <= l_exp_resp_date_buffer))
190 or
191 ((l_exp_resoln_date_buffer is not null) and (p_actual_resolution_date is null) and (p_exp_resolution_date is not null) and ((p_exp_resolution_date - sysdate) <= l_exp_resoln_date_buffer))
192 ) then
193
194 l_sr_jeopardy := 'YES';
195 end if;
196
197
198
199
200 RETURN l_sr_jeopardy;
201
202 EXCEPTION
203 WHEN others THEN
204 return 'ERROR IN GET_SR_JEOPARDY' ;
205 END; -- Function GET_SR_JEOPARDY
206 --
207 -- --------------------------------------------------------------------------------
208 -- Start of comments
209 -- UTIL Name : GET_CALCULATED_TIME
210 -- Type : Private
211 -- Description : Returns the Time in Days
212 -- Parameters :
213 -- IN : p_time IN NUMBER Required
214 -- IN : p_UOM IN VARCHAR2 Required
215 -- ReturnValue:
216 -- l_calculated_time NUMBER
217 -- --------------------------------------------------------------------------------
218 Function GET_CALCULATED_TIME
219 ( p_time IN NUMBER,
220 p_UOM IN VARCHAR2)
221 RETURN NUMBER IS
222
223 l_calculated_time NUMBER :=0 ;
224
225 BEGIN
226
227 if (p_time is not null) then
228 if (p_UOM = 'DAY') then
229 l_calculated_time := p_time;
230 elsif(p_UOM = 'HR') then
231 l_calculated_time := (p_time)/24;
232 elsif(p_UOM = 'MIN') then
233 l_calculated_time := (p_time )/(24*60);
234 elsif(p_UOM = 'WK') then
235 l_calculated_time := (p_time * 7);
236 else
237 l_calculated_time := p_time;
238 end if;
239 else
240 l_calculated_time :=0;
241 end if;
242
243 RETURN l_calculated_time;
244 EXCEPTION
245 WHEN OTHERS THEN
246 RETURN 'ERROR IN GET_CALCULATED_TIME' ;
247 END; -- Function GET_CALCULATED_TIME
248 --
249 -- --------------------------------------------------------------------------------
250 -- Start of comments
251 -- UTIL Name : GET_DEFAULT_SITE
252 -- Type : Private
253 -- Description : Returns the primary billto/shipto address associated with this party
254 -- Parameters :
255 -- IN : partyId IN NUMBER Required
256 -- IN : siteUse IN VARCHAR2 Required
257 -- ReturnValue: primary billto/shipto address for this party
258 -- l_default_site VARCHAR2
259 -- ----------------------------------------------------------------------
260 Function get_default_site
261 ( partyId in NUMBER
262 , site_use in VARCHAR2
263 ) return VARCHAR2 is
264 --
265 l_address VARCHAR2(4000);
266
267 CURSOR default_address (param_party_id NUMBER , param_site_usage VARCHAR2) is
268 select l.address1 || decode (l.address2,null,null,',' || l.address2) ||
269 decode(l.address3,null,null,', '|| l.address3) || decode(l.address4,null,null,', '|| l.address4) ||
270 decode (l.city, null, null, ', '|| l.city) || decode(l.state,null,null,', ' || l.state) ||
271 decode(l.province,null,null,', ' || l.province) || decode(l.postal_code,null,null,' '
272 || l.postal_code) || decode(l.country,null,null,' ' || l.country)
273 from hz_party_sites s, hz_locations l, hz_party_site_uses u
274 where s.party_id=param_party_id and s.status='A' and s.location_id=l.location_id and
275 s.party_site_id=u.party_site_id and u.site_use_type=param_site_usage
276 and u.primary_per_type='Y' and u.status='A';
277 --
278 begin
279 --
280 open default_address (partyId, site_use);
281 fetch default_address into l_address;
282 close default_address;
283
284 return l_address;
285 exception
286 when others then
287 IF default_address%ISOPEN THEN
288 CLOSE default_address;
289 END IF;
290 return null;
291 end get_default_site;
292 --
293 -- --------------------------------------------------------------------------------
294 -- Start of comments
295 -- UTIL Name : GET_DEFAULT_SITE_ID
296 -- Type : Private
297 -- Description : Returns the site Id for primary billto/shipto address
298 -- Parameters :
299 -- IN : partyId IN NUMBER Required
300 -- IN : site_use IN VARCHAR2 Required
301 -- ReturnValue:
302 -- l_default_site NUMBER
303 -- --------------------------------------------------------------------------
304 Function get_default_site_id
305 ( partyId in NUMBER
306 , site_use in VARCHAR2
307 ) return NUMBER is
308 --
309 l_site_id NUMBER;
310
311 CURSOR default_site(param_party_id NUMBER, param_site_usage VARCHAR2) is
312 select s.party_site_id from hz_party_sites s,
313 hz_party_site_uses u where s.party_id=param_party_id and s.status='A' and
314 s.party_site_id=u.party_site_id and u.site_use_type=param_site_usage
315 and u.primary_per_type='Y' and u.status='A';
316 --
317 begin
318 --
319
320 open default_site (partyId, site_use);
321 fetch default_site into l_site_id;
322 close default_site;
323
324 return l_site_id;
325
326 exception
327 when others then
328 IF default_site%ISOPEN THEN
329 CLOSE default_site;
330 END IF;
331
332 return null;
333 end get_default_site_id;
334 --
335 -- --------------------------------------------------------------------------
336 -- -------------------------------------------------------------------------
337 -- Start of comments
338 -- UTIL Name : task_group_template_mismatch
339 -- Type : Private
340 -- Description : Checks if there is a mismatch in task group template when item,
341 -- item category, type, problem code change on updating a SR
342 -- -------------------------------------------------------------------------
343 procedure task_group_template_mismatch
344 ( p_init_msg_list in varchar2 default fnd_api.g_false
345 , p_old_inv_category in number
346 , p_new_inv_category in number
347 , p_old_inv_item in number
348 , p_new_inv_item in number
349 , p_old_inc_type in number
350 , p_new_inc_type in number
351 , p_inv_org_id in number
352 , p_incident_id in number
353 , p_old_prob_code in varchar2
354 , p_new_prob_code in varchar2
355 , x_msg_count out nocopy number
356 , x_return_status out nocopy varchar2
357 , x_msg_data out nocopy varchar2
358 ) is
359 --
360 l_new_task_group_template_id number;
361 l_old_task_group_template_id number;
362 l_task_temp_grp_name varchar2(80);
363 l_task_temp_grp_names varchar2(2000);
364 l_found_flag varchar2(1) := 'N';
365 --
366 p_task_template_search_rec cs_autogen_task_pvt.task_template_search_rec_type;
367 l_task_template_group_tbl cs_autogen_task_pvt.task_template_group_tbl_type;
368 --
369 cursor c_task_template_id is
370 select a.template_group_id
371 from jtf_tasks_b a, jtf_task_statuses_vl b
372 where a.task_status_id = b.task_status_id and
373 (nvl(b.closed_flag,'N') = 'N') and (nvl(b.completed_flag,'N') = 'N') and
374 a.source_object_type_code = 'SR' and
375 a.source_object_id = p_incident_id;
376 --
377 cursor c_task_temp_grp_name(l_templ_grp_id in number) is
378 select template_group_name
379 from jtf_task_temp_groups_vl
380 where task_template_group_id = l_templ_grp_id;
381 --
382 begin
383 --
384 if fnd_api.to_boolean( p_init_msg_list ) then
385 fnd_msg_pub.initialize;
386 end if;
387 --
388 x_return_status := fnd_api.g_ret_sts_success;
389 --
390 if (p_old_inv_category <> p_new_inv_category) or
391 (p_old_inv_item <> p_new_inv_item) or
392 (p_old_inc_type <> p_new_inc_type) or
393 (p_old_prob_code <> p_new_prob_code) then
394 p_task_template_search_rec.incident_type_id := p_new_inc_type;
395 p_task_template_search_rec.organization_id := p_inv_org_id;
396 p_task_template_search_rec.inventory_item_id := p_new_inv_item;
397 p_task_template_search_rec.category_id := p_new_inv_category;
398 p_task_template_search_rec.problem_code := p_new_prob_code;
399 --
400 cs_autogen_task_pvt.get_task_template_group( p_api_version => 1.0
401 , p_init_msg_list => 'T'
402 , p_commit => fnd_api.g_false
403 , p_validation_level => fnd_api.g_valid_level_full
404 , p_task_template_search_rec => p_task_template_search_rec
405 , x_task_template_group_tbl => l_task_template_group_tbl
406 , x_return_status => x_return_status
407 , x_msg_count => x_msg_count
408 , x_msg_data => x_msg_data
409 );
410 --
411 if (x_return_status = fnd_api.g_ret_sts_error) then
412 raise fnd_api.g_exc_error;
413 elsif (x_return_status = fnd_api.g_ret_sts_unexp_error) then
414 raise fnd_api.g_exc_unexpected_error;
415 elsif (x_return_status = fnd_api.g_ret_sts_success) then
416 --
417 if l_task_template_group_tbl.count > 1 then
418 --
419 for i in l_task_template_group_tbl.first..l_task_template_group_tbl.last loop
420 open c_task_temp_grp_name(l_task_template_group_tbl(i).task_template_group_id);
421 fetch c_task_temp_grp_name into l_task_temp_grp_name;
422 close c_task_temp_grp_name;
423 --
424 if l_task_temp_grp_names is not null then
425 l_task_temp_grp_names := l_task_temp_grp_names||', '||l_task_temp_grp_name;
426 else
427 l_task_temp_grp_names := l_task_temp_grp_name;
428 end if;
429 --
430 end loop;
431 --
432 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_success ) then
433 fnd_message.set_name('CS','CS_SR_MULTIPLE_TASK_TEMP_GRP');
434 fnd_message.set_token('TGT_NAMES', l_task_temp_grp_names);
435 fnd_msg_pub.add_detail(p_message_type => fnd_msg_pub.G_WARNING_MSG);
436 end if;
437 elsif l_task_template_group_tbl.count = 1 then
438 l_new_task_group_template_id := l_task_template_group_tbl(0).task_template_group_id;
439 for i in c_task_template_id
440 --
441 loop
442 fetch c_task_template_id into l_old_task_group_template_id;
443 --
444 if(l_old_task_group_template_id = l_new_task_group_template_id) then
445 l_found_flag := 'Y';
446 exit;
447 end if;
448 --
449 end loop;
450 --
451 if (l_found_flag = 'N') then
452 open c_task_temp_grp_name(l_new_task_group_template_id);
453 fetch c_task_temp_grp_name into l_task_temp_grp_name;
454 close c_task_temp_grp_name;
455 --
456 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_success ) then
457 fnd_message.set_name('CS', 'CS_SR_TASK_TEMP_GRP_MISMATCH');
458 fnd_message.set_token('TGT_NAME', l_task_temp_grp_name);
459 fnd_msg_pub.add_detail(p_message_type => fnd_msg_pub.G_WARNING_MSG);
460 end if;
461 --
462 end if;
463 end if;
464 --
465 end if;
466 --
467 end if;
468
469 fnd_msg_pub.count_and_get
470 ( p_count => x_msg_count
471 , p_data => x_msg_data
472 );
473
474 --
475 exception
476 --
477 when fnd_api.g_exc_error then
478 --
479 x_return_status := fnd_api.g_ret_sts_success;
480 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_success ) then
481 fnd_message.set_name('CS', 'CS_SR_TASK_TEMP_GRP_API_ERROR');
482 fnd_msg_pub.add_detail(p_message_type => fnd_msg_pub.G_WARNING_MSG);
483 end if;
484 --
485 fnd_msg_pub.count_and_get
486 ( p_count => x_msg_count
487 , p_data => x_msg_data
488 );
489 when fnd_api.g_exc_unexpected_error then
490 --
491 x_return_status := fnd_api.g_ret_sts_success;
492 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_success ) then
493 fnd_message.set_name('CS', 'CS_SR_TASK_TEMP_GRP_API_ERROR');
494 fnd_msg_pub.add_detail(p_message_type => fnd_msg_pub.G_WARNING_MSG);
495 end if;
496 --
497 fnd_msg_pub.count_and_get
498 ( p_count => x_msg_count
499 , p_data => x_msg_data
500 );
501 when others then
502 --
503 if c_task_template_id%isopen then
504 close c_task_template_id;
505 end if;
506 --
507 if c_task_temp_grp_name%isopen then
508 close c_task_temp_grp_name;
509 end if;
510 --
511 x_return_status := fnd_api.g_ret_sts_success;
512 if fnd_msg_pub.check_msg_level( fnd_msg_pub.g_msg_lvl_success ) then
513 fnd_message.set_name('CS', 'CS_SR_TASK_TEMP_GRP_API_ERROR');
514 fnd_msg_pub.add_detail(p_message_type => fnd_msg_pub.G_WARNING_MSG);
515 end if;
516 --
517 fnd_msg_pub.count_and_get
518 ( p_count => x_msg_count
519 , p_data => x_msg_data
520 );
521 --
522 end task_group_template_mismatch;
523 --
524 -- -------------------------------------------------------------------------
525 -- Start of comments
526 -- UTIL Name : get_instance_details
527 -- Type : Private
528 -- Description : gets the contact and contract given an instance, primarily
529 -- used for defaulting when instance is selected
530 -- -------------------------------------------------------------------------
531 procedure get_instance_details
532 ( p_instance_id in number
533 , p_inc_type_id in number default fnd_profile.value('INC_DEFAULT_INCIDENT_TYPE')
534 , p_severity_id in number default fnd_profile.value('INC_DEFAULT_INCIDENT_SEVERITY')
535 , p_request_date in date default sysdate
536 , p_timezone_id in number default fnd_profile.value('SERVER_TIMEZONE_ID')
537 , p_get_contact in varchar2 default fnd_api.g_false
538 , x_contact_id out nocopy number
539 , x_contact_type out nocopy varchar2
540 , x_contract_id out nocopy number
541 , x_contract_number out nocopy varchar2
542 , x_service_line_id out nocopy number
543 , x_coverage_term out nocopy varchar2
544 , x_warranty_flag out nocopy varchar2
545 , x_reaction_time out nocopy date
546 , x_resolution_time out nocopy date
547 , x_service_desc out nocopy varchar2
548 ) is
549 --
550 l_biz_process_id number;
551 l_inp_rec OKS_ENTITLEMENTS_PUB.GET_CONTIN_REC;
552 --
553 l_party_id number;
554 x_ent_contracts OKS_ENTITLEMENTS_PUB.GET_CONTOP_TBL;
555 x_return_status varchar2(2000);
556 x_msg_count number;
557 x_msg_data varchar2(2000);
558 --
559 cursor c_biz_process is
560 select
561 business_process_id
562 from
563 cs_incident_types
564 where
565 incident_type_id = p_inc_type_id;
566 --
567 cursor c_contact is
568 select
569 decode(contact.party_source_table, 'HZ_PARTIES', 'PARTY_RELATIONSHIP', 'EMPLOYEE') contact_type,
570 contact.party_id contact_id,
571 owner.party_id
572 from
573 csi_i_parties contact,
574 csi_i_parties owner
575 where
576 contact.instance_id = p_instance_id
577 and contact.contact_flag = 'Y'
578 and contact.primary_flag = 'Y'
579 and contact.contact_ip_id = owner.instance_party_id
580 and owner.contact_flag = 'N'
581 and owner.relationship_type_code = 'OWNER';
582 --
583 cursor c_party_relation(l_object_id in number, l_subject_id in number) is
584 select
585 party_id
586 from
587 hz_relationships
588 where
589 object_id = l_object_id
590 and subject_id = l_subject_id;
591 --
592 begin
593 --
594 if p_get_contact = fnd_api.g_true then
595 --
596 for r_contact in c_contact
597 --
598 loop
599 --
600 x_contact_id := r_contact.contact_id;
601 x_contact_type := r_contact.contact_type;
602 l_party_id := r_contact.party_id;
603 exit when c_contact%rowcount > 1;
604 --
605 end loop;
606 --
607 if x_contact_type = 'PARTY_RELATIONSHIP' then
608 --
609 for r_party_relation in c_party_relation(l_party_id, x_contact_id)
610 --
611 loop
612 --
613 x_contact_id := r_party_relation.party_id;
614 exit when c_party_relation%rowcount > 1;
615 --
616 end loop;
617 --
618 end if;
619 --
620 end if;
621 --
622 for r_biz_process in c_biz_process
623 --
624 loop
625 --
626 l_biz_process_id := r_biz_process.business_process_id;
627 exit when c_biz_process%rowcount > 1;
628 --
629 end loop;
630 --
631 l_inp_rec.request_date := p_request_date;
632 l_inp_rec.business_process_id := l_biz_process_id;
633 l_inp_rec.severity_id := p_severity_id;
634 l_inp_rec.time_zone_id := p_timezone_id;
635 l_inp_rec.product_id := p_instance_id;
636 l_inp_rec.calc_resptime_flag := 'Y';
637 l_inp_rec.validate_flag := 'Y';
638 l_inp_rec.sort_key := '';
639 --
640 oks_entitlements_pub.get_contracts
641 ( 1.0
642 , 'T'
643 , l_inp_rec
644 , x_return_status
645 , x_msg_count
646 , x_msg_data
647 , x_ent_contracts
648 );
649 --
650 if x_return_status = fnd_api.g_ret_sts_success then
651 --
652 if x_ent_contracts.count > 0 then
653 x_contract_id := x_ent_contracts(1).contract_id;
654 x_contract_number := x_ent_contracts(1).contract_number;
655 x_service_line_id := x_ent_contracts(1).service_line_id;
656 x_coverage_term := x_ent_contracts(1).coverage_term_name;
657 x_warranty_flag := x_ent_contracts(1).warranty_flag;
658 x_reaction_time := x_ent_contracts(1).exp_reaction_time;
659 x_resolution_time := x_ent_contracts(1).exp_resolution_time;
660 --
661 if x_ent_contracts(1).contract_number_modifier is not null then
662 x_service_desc := x_ent_contracts(1).contract_number||' - '||
663 x_ent_contracts(1).contract_number_modifier||' : '||
664 x_ent_contracts(1).service_name;
665 else
666 x_service_desc := x_ent_contracts(1).contract_number||' : '||
667 x_ent_contracts(1).service_name;
668 end if;
669 --
670 end if;
671 --
672 else
673 raise fnd_api.g_exc_error;
674 end if;
675 --
676 --
677 exception
678 --
679 when fnd_api.g_exc_error then
680 null;
681 --
682 when others then
683 --
684 if c_biz_process%isopen then
685 close c_biz_process;
686 end if;
687 --
688 if c_contact%isopen then
689 close c_contact;
690 end if;
691 --
692 if c_party_relation%isopen then
693 close c_party_relation;
694 end if;
695 --
696 end get_instance_details;
697 --
698 -- -------------------------------------------------------------------------
699 --
700 -- -------------------------------------------------------------------------
701 -- Start of comments
702 -- UTIL Name : get_linked_srs
703 -- Type : Private
704 -- Description : get linked srs linked as duplicateof, causedby, refersto
705 -- (used to display linked srs in KM Unified sr search)
706 -- -------------------------------------------------------------------------
707 FUNCTION get_linked_srs (p_incident_id IN NUMBER) RETURN VARCHAR2
708 AS
709 CURSOR l_linked_cursor (l_incident_id in NUMBER) IS
710 select SrLnkEO.link_id link_id
711 , SrLnkEO.object_type object_type
712 , SrLnkEO.object_id object_id
713 , SrLnkEO.object_number object_number
714 , SrLnkEO.subject_id subject_id
715 , SrLnkEO.subject_type subject_type
716 , SrLnkEO.link_type_id link_type_id
717 , ltype.name link_type_name
718 from cs_incident_links SrLnkEO,
719 cs_incidents_all_b sr,
720 cs_sr_link_types_vl ltype
721 where SrLnkEO.object_id = sr.incident_id
722 and SrLnkEO.subject_type = 'SR'
723 and SrLnkEO.object_type = 'SR'
724 and sysdate between
725 nvl(SrLnkEO.start_date_active,sysdate)
726 and nvl(SrLnkEO.end_date_active,sysdate)
727 and SrLnkEO.link_type_id = ltype.link_type_id
728 and SrLnkEO.subject_id = l_incident_id
729 and ltype.link_type_id in (2, 3, 6);
730 linked_sr_rec l_linked_cursor%ROWTYPE;
731 l_linked_sr_str VARCHAR2(1000) := '';
732 BEGIN
733 for l_linked_rec in l_linked_cursor (p_incident_id) loop
734 l_linked_sr_str := l_linked_sr_str ||' ' || l_linked_rec.link_type_name || ' ' || l_linked_rec.object_number;
735 end loop;
736 return l_linked_sr_str;
737 EXCEPTION
738 WHEN OTHERS THEN
739 if l_linked_cursor%isopen
740 then
741 close l_linked_cursor;
742 end if;
743 END;
744
745 -- -------------------------------------------------------------------------
746 -- Start of comments
747 -- UTIL Name : get_contact_info
748 -- Type : Private
749 -- Description : To get the Contact info based on the Incident id, ContactPartyId,PartyId and ContactType, primarycontact
750 -- Parameters :
751 -- IN: p_incident_id IN VARCHAR2 Required
752 -- IN: p_contact_type IN VARCHAR2
753 -- IN : p_contact_party_id IN NUMBER
754 -- IN : p_party_id IN NUMBER Required
755 -- IN : p_primary_contact IN NUMBER Required
756 -- Returnvalue:
757 -- l_contact_name VARCHAR2(360)
758 -- End of comments
759 -- -------------------------------------------------------------------------
760 FUNCTION get_contact_info
761 ( p_incident_id IN NUMBER
762 ,p_contact_type IN VARCHAR2
763 ,p_contact_party_id IN NUMBER
764 ,p_party_id IN NUMBER
765 ,p_primary_contact IN VARCHAR2
766 ) RETURN VARCHAR2 AS
767 l_contact_party_id NUMBER;
768 l_contact_type VARCHAR2(400) default null;
769 l_contact_name VARCHAR2(4000) default null;
770 cursor cont(param_incident_id NUMBER,param_primary_flag VARCHAR2) is
771 select contact_type, party_id
772 from cs_hz_sr_contact_points
773 where incident_id = param_incident_id and primary_flag = param_primary_flag and
774 rownum < 2 order by party_id;
775 begin
776 -- If contact partyid is absent then find the contact based on incident info
777 if (p_contact_party_id is null) then
778 OPEN cont(p_incident_id,p_primary_contact);
779 fetch cont into l_contact_type, l_contact_party_id;
780 if cont%notfound then
781 l_contact_type := '';
782 l_contact_party_id := 0;
783 end if;
784 close cont;
785 else
786 -- Else find the contact based on provided contact information
787 l_contact_type := p_contact_type;
788 l_contact_party_id := p_contact_party_id;
789 end if;
790 return get_contact_name (l_contact_type, l_contact_party_id, p_party_id);
791 end;
792 --
793 -- -------------------------------------------------------------------------
794 -- -------------------------------------------------------------------------
795 -- Start of comments
796 -- UTIL Name : GET_TIMEZONE_FOR_CONTACT
797 -- Type : Private
798 -- Description : Returns the Timezone Id for a Contact/ContactPoint/Location.
799 -- Logic : In case if the contact type is Employee return Null;
800 -- If contact point Id is passed, query the timezone for the contact point.
801 -- If either the contact point Id is null or if the timezone associated with the
802 -- Contact point is null , then query the Primary location for the contact and get
803 -- the timezone associated with the primary location from HZ_CONTACTS
804 -- Parameters :
805 -- IN : p_contact_type IN VARCHAR2 Optional. If not passed assumed not an Employee
806 -- IN : p_contact_point_id IN Number Optional
807 -- IN : p_contact_id IN Number Optional
808 -- ReturnValue:
809 -- l_timezone_id NUMBER
810
811 -- Control comes here only when contact type != EMPLOYEE
812 -- if the contact_type is PARTY_RELATIONSHIP OR PERSON
813 -- --------------------------------------------------------------------------------
814
815 Function GET_TIMEZONE_FOR_LOCATION
816 (p_contact_id IN NUMBER)
817 RETURN NUMBER
818 AS
819 l_timezone_id NUMBER DEFAULT NULL;
820 BEGIN
821 if p_contact_id is not null and p_contact_id <> -1 then
822 SELECT timezone_id
823 INTO l_timezone_id
824 FROM hz_locations
825 WHERE location_id = ( SELECT location_id
826 FROM hz_party_sites
827 WHERE party_id = p_contact_id
828 AND identifying_address_flag = 'Y'
829 AND status = 'A' ) ;
830 end if;
831 return l_timezone_id;
832 EXCEPTION
833 when others then
834 return null;
835 END;
836
837
838 FUNCTION GET_TIMEZONE_FOR_CONTACT
839 ( p_contact_type IN VARCHAR2,
840 p_contact_id IN NUMBER,
841 p_contact_point_id IN NUMBER
842 ) RETURN VARCHAR2
843 AS
844 l_timezone_id NUMBER DEFAULT NULL;
845 l_timezone_name VARCHAR2(80) DEFAULT NULL;
846 l_timezone_code VARCHAR2(50) DEFAULT NULL;
847 BEGIN
848
849 if p_contact_type = 'EMPLOYEE' then
850 return null;
851 end if;
852
853 if p_contact_point_id is not null THEN
854
855 l_timezone_id := GET_TIMEZONE_FOR_LOCATION(p_contact_id);
856
857 IF l_timezone_id IS NULL THEN
858 SELECT timezone_id
859 INTO l_timezone_id
860 FROM hz_contact_points
861 WHERE contact_point_type = 'PHONE'
862 AND primary_flag = 'Y'
863 AND contact_point_id = p_contact_point_id;
864 END IF;
865 if l_timezone_id is not null then
866 SELECT name ,
867 timezone_code
868 INTO l_timezone_name,
869 l_timezone_code
870 FROM FND_TIMEZONES_VL
871 WHERE UPGRADE_TZ_ID = l_timezone_id
872 AND ENABLED_FLAG = 'Y';
873
874 return l_timezone_id || '::' || l_timezone_name || '::' || l_timezone_code;
875 ELSE
876 --In case timezone id from both location and contact point returns null, return null value
877 --Fix for bug 16841211
878 RETURN NULL;
879 end if;
880
881 ELSE
882 RETURN NULL;
883 END IF;
884 EXCEPTION
885 when others then
886 return null;
887 END;
888
889 --
890 -- --------------------------------------------------------------------------------
891
892 -- Start of comments
893 -- UTIL Name : GET_FIRST_NOTE
894 -- Type : Private
895 -- Description : Given a INCIDENT_ID this function will return the recently
896 --- added note to the service request number.
897 -- Parameters :
898 -- IN : p_incident_id NUMBER Required
899 -- OUT: Returns l_first_note VARCHAR2(2000)
900 -- End of comments
901 -- -------------------------------------------------------------------------
902 Function GET_FIRST_NOTE
903 ( p_incident_id IN NUMBER)
904 RETURN VARCHAR2 IS
905 l_first_note varchar2(2000) DEFAULT NULL;
906 CURSOR c_first_note(param_incident_id NUMBER) is
907 select notes
908 from jtf_notes_vl
909 where source_object_code = 'SR' and source_object_id =param_incident_id
910 order by creation_date;
911 begin
912 open c_first_note (p_incident_id);
913 fetch c_first_note into l_first_note;
914 close c_first_note;
915 RETURN l_first_note ;
916
917 EXCEPTION
918 WHEN OTHERS THEN
919 IF c_first_note%ISOPEN THEN
920 CLOSE c_first_note;
921 END IF;
922 return null;
923 end GET_FIRST_NOTE;
924 -- -------------------------------------------------------------------------
925 -- Start of comments
926 -- UTIL Name : CHECK_IF_NEXT_WORK_ENABLED
927 -- Type : Private
928 -- Description : Calls the
929 -- IEU_WR_PUB. CHECK_WS_ACTIVATION_STATUS
930 -- to verify if the activation flag is turned on
931 -- for the worksource code
932 -- Parameters :
933 -- IN : p_ws_code VARCHAR2 Required
934 -- OUT : x_enable_next_work NUMBER
935 -- OUT : x_msg_count NUMBER
936 -- OUT : x_return_status VARCHAR2
937 -- OUT : x_msg_data VARCHAR2
938 -- End of comments
939 -- ------------------------------------------------------------------------------------
940 PROCEDURE CHECK_IF_NEXT_WORK_ENABLED
941 ( p_ws_code IN VARCHAR2,
942 x_enable_next_work OUT nocopy VARCHAR2,
943 x_msg_count OUT nocopy NUMBER,
944 x_return_status OUT nocopy VARCHAR2,
945 x_msg_data OUT nocopy VARCHAR2) IS
946
947 BEGIN
948
949 BEGIN
950 -- Invoke the IEU API to get the Activation status of work source
951
952 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS
953 ( p_api_version => 1,
954 p_ws_code =>p_ws_code,
955 x_ws_activation_status =>x_enable_next_work,
956 x_msg_count => x_msg_count,
957 x_msg_data => x_msg_data,
958 x_return_status => x_return_status);
959
960 EXCEPTION
961 WHEN OTHERS THEN
962 fnd_msg_pub.Count_and_Get
963 (
964 p_count => x_msg_count,
965 p_data => x_msg_data
966 );
967 RAISE fnd_api.g_exc_error;
968 END;
969
970
971 Exception
972
973 WHEN fnd_api.g_exc_error THEN
974 x_return_status := 'E';
975 fnd_msg_pub.Count_and_Get
976 (
977 p_count => x_msg_count,
978 p_data => x_msg_data
979 );
980
981 WHEN fnd_api.g_exc_unexpected_error THEN
982 x_return_status := 'U';
983 fnd_msg_pub.Count_and_Get
984 (
985 p_count => x_msg_count,
986 p_data => x_msg_data
987 );
988
989 END CHECK_IF_NEXT_WORK_ENABLED;
990 -- ------------------------------------------------------------------------------------
991 -- Start of comments
992 -- UTIL Name : GET_NEXT_SR_TO_WORK
993 -- Type : Private
994 -- Description : Calls IEU_WR_PUB.GET_NEXT_WORK_FOR_APPS
995 -- and returns the incident_id retreived from the above call.
996 -- Parameters :
997 -- IN : p_ws_code VARCHAR2 Required
998 -- IN : p_resource_id NUMBER Required
999 -- OUT : x_incident_id NUMBER
1000 -- OUT : x_msg_count NUMBER
1001 -- OUT : x_return_status VARCHAR2
1002 -- OUT : x_msg_data VARCHAR2
1003 -- End of comments
1004
1005 -- ------------------------------------------------------------------------------------
1006 PROCEDURE GET_NEXT_SR_TO_WORK
1007 ( p_ws_code IN VARCHAR2,
1008 p_resource_id IN NUMBER,
1009 x_incident_id OUT nocopy NUMBER,
1010 x_msg_count OUT nocopy NUMBER,
1011 x_return_status OUT nocopy VARCHAR2,
1012 x_msg_data OUT nocopy VARCHAR2,
1013 x_object_type OUT nocopy VARCHAR2) IS
1014
1015 --declare variables
1016 l_ws_det_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WS_DETAILS_LIST;
1017 l_uwqm_workitem_data IEU_FRM_PVT.T_IEU_MEDIA_DATA;
1018 l_language VARCHAR2(10);
1019 l_source_lang VARCHAR2(10);
1020 j number;
1021 x_enable_next_work VARCHAR2(10);
1022 x_distribute_sr_task VARCHAR2(10);
1023 BEGIN
1024 l_ws_det_list(1).ws_code := p_ws_code;
1025 l_language := userenv('lang');
1026 l_source_lang := 'US';
1027 j :=0;
1028
1029 BEGIN
1030 -- Check if SR-TASK worksource is enabled
1031 CHECK_IF_NEXT_WORK_ENABLED
1032 ( p_ws_code => 'SR_TASKS',
1033 x_enable_next_work =>x_enable_next_work ,
1034 x_msg_count => x_msg_count ,
1035 x_return_status => x_return_status ,
1036 x_msg_data=> x_msg_data);
1037
1038 -- Check if IEU: Distribute: Service Request Task: Work Source
1039
1040
1041
1042
1043 -- profile is enabled
1044 fnd_profile.get('IEU_WR_DIS_SR_TASKS_WS', x_distribute_sr_task);
1045
1046 -- If SR_TASK is enabled, pass SR-TASK also as object code
1047 If( (x_enable_next_work = 'Y') AND (x_distribute_sr_task = 'Y')) THEN
1048 l_ws_det_list(2).ws_code := 'SR_TASKS';
1049 END IF;
1050
1051 -- Invoke the IEU API to get the incident Id
1052 IEU_WR_PUB.GET_NEXT_WORK_FOR_APPS
1053 ( p_api_version => 1,
1054 p_resource_id => p_resource_id,
1055 p_language => l_language,
1056 p_source_lang => l_source_lang,
1057 p_ws_det_list => l_ws_det_list,
1058 x_uwqm_workitem_data => l_uwqm_workitem_data,
1059 x_msg_count => x_msg_count,
1060 x_msg_data => x_msg_data,
1061 x_return_status => x_return_status);
1062
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 fnd_msg_pub.Count_and_Get
1066 (
1067 p_count => x_msg_count,
1068 p_data => x_msg_data
1069 );
1070 RAISE fnd_api.g_exc_error;
1071 END;
1072
1073 -- If the return status is Success or if the OUT param l_uwqm_workitem_data
1074 -- has values. Then retrieve the first row and get the incident_id
1075 -- which in the WORKITEM_PK_ID
1076
1077 if (x_return_status = 'S') OR (l_uwqm_workitem_data.count >= 1)
1078 then
1079
1080 FOR j in l_uwqm_workitem_data.first .. l_uwqm_workitem_data.last
1081 LOOP
1082
1083 if (l_uwqm_workitem_data(j).param_name = 'WORKITEM_PK_ID')
1084 then
1085 x_incident_id := l_uwqm_workitem_data(j).param_value;
1086 end if;
1087 if ( l_uwqm_workitem_data(j).param_name = 'WORKITEM_OBJ_CODE')
1088 then
1089 x_object_type := l_uwqm_workitem_data(j).param_value;
1090 end if;
1091
1092 if( x_incident_id <> NULL) AND (x_object_type <> NULL) then
1093 exit;
1094 end if;
1095 end loop;
1096 end if;
1097
1098 Exception
1099
1100 WHEN fnd_api.g_exc_error THEN
1101 x_return_status := 'E';
1102 fnd_msg_pub.Count_and_Get
1103 (
1104 p_count => x_msg_count,
1105 p_data => x_msg_data
1106 );
1107
1108 WHEN fnd_api.g_exc_unexpected_error THEN
1109 x_return_status := 'U';
1110 fnd_msg_pub.Count_and_Get
1111 (
1112 p_count => x_msg_count,
1113 p_data => x_msg_data
1114 );
1115
1116 END GET_NEXT_SR_TO_WORK;
1117
1118 -- -------------------------------------------------------------------------
1119 -- Start of comments
1120 -- UTIL Name : GET_SR_ESCALATED
1121 -- Type : Private
1122 -- Description : Returns Y if Service Request is escalated, else returns N
1123 -- Parameters :
1124 -- IN : p_incident_id IN NUMBER Required
1125 --
1126 --- Returnvalue:
1127 -- l_sr_escalated VARCHAR2(1)
1128 -- End of comments
1129 -- --------------------------------------------------------------------------
1130 Function GET_SR_ESCALATED
1131 ( p_incident_id IN NUMBER
1132 )
1133 RETURN VARCHAR2 IS
1134 l_object_id NUMBER;
1135 cursor c_escal_id(param_incident_id NUMBER) is
1136 select trf.object_id
1137 from jtf_tasks_b tsk,
1138 jtf_task_references_b trf
1139 where tsk.task_id = trf.task_id
1140 and tsk.task_type_id = 22
1141 and tsk.escalation_level not in ('DE', 'NE')
1142 and nvl(tsk.open_flag, 'Y') = 'Y'
1143 and trf.reference_code = 'ESC'
1144 and trf.object_type_code = 'SR'
1145 and trf.object_id = param_incident_id;
1146 BEGIN
1147 open c_escal_id (p_incident_id);
1148 fetch c_escal_id into l_object_id;
1149 close c_escal_id;
1150
1151 if l_object_id is not null then
1152 return 'Y'; --if found escalated
1153 end if;
1154 return 'N'; --if not found
1155 EXCEPTION
1156 WHEN OTHERS THEN
1157 IF c_escal_id%ISOPEN THEN
1158 CLOSE c_escal_id;
1159 END IF;
1160 END GET_SR_ESCALATED;
1161 -- -------------------------------------------------------------------------
1162 -- -------------------------------------------------------------------------
1163 -- Start of comments
1164 -- UTIL Name : GET_CONTACT_NAME
1165 -- Type : Private
1166 -- Description : Returns contact name
1167 -- Parameters :
1168 -- IN : p_incident_id IN NUMBER
1169 -- p_customer_id IN NUMBER
1170 -- Returnvalue: contact name
1171 --
1172 -- (If we make this api get_contact_info, it can return
1173 -- <contact number>-separator<contact name>separator<phone>separator<email>)
1174 -- End of comments
1175 -- -------------------------------------------------------------------------
1176
1177 Function GET_CONTACT_NAME
1178 ( p_incident_id IN NUMBER,
1179 p_customer_id IN NUMBER
1180 --,p_separator IN VARCHAR --needed for get_contact_info api
1181 )
1182 RETURN VARCHAR2 IS
1183 l_return_val VARCHAR2(255);
1184 l_contact_party_id number;
1185 l_contact_type varchar2(30);
1186 l_effective_start_date DATE DEFAULT NULL;
1187 l_effective_end_date DATE DEFAULT NULL;
1188
1189 --l_contact_num varchar2(30);
1190 l_contact_name varchar2(360); --emp.fullname 240,party_name 360
1191 --l_contact_phone varchar2(83); --emp 60, party phone and space 83
1192 --l_contact_email varchar2(2000); --emp 240, party 2000
1193
1194
1195 cursor c_sr_cont is
1196 select sr_cont.party_id,
1197 sr_cont.contact_type
1198 from cs_hz_sr_contact_points sr_cont
1199 where sr_cont.incident_id = p_incident_id
1200 and sr_cont.primary_flag = 'Y';
1201
1202 cursor c_emp(param_party_contact_id NUMBER) is
1203 select --employee_number,
1204 full_name,
1205 --, work_telephone, email_address,
1206 effective_start_date,effective_end_date
1207 from per_all_people_f
1208 where person_id = param_party_contact_id
1209 order by effective_start_date desc;
1210
1211 cursor c_party is
1212 select --hz.party_number,
1213 hz.party_name
1214 --,hz.primary_phone_country_code||' '||
1215 --hz.primary_phone_area_code||' '||
1216 --hz.primary_phone_number||' '||
1217 --hz.primary_phone_extension,
1218 --hz.email_address
1219 from hz_parties hz
1220 where hz.party_id = p_customer_id;
1221
1222 cursor c_rel(param_contact_party_id NUMBER) is
1223 select --hz.party_number,
1224 hz.party_name
1225 --,hz.primary_phone_country_code||' '||
1226 --hz.primary_phone_area_code||' '||
1227 --hz.primary_phone_number||' '||
1228 --hz.primary_phone_extension,
1229 --hz.email_address
1230 from hz_parties hz, hz_relationships rel
1231 where rel.party_id = param_contact_party_id
1232 and rel.object_id = p_customer_id
1233 and rel.subject_id = hz.party_id
1234 and rel.subject_type = 'PERSON';
1235 BEGIN
1236
1237 open c_sr_cont;
1238 fetch c_sr_cont into l_contact_party_id, l_contact_type;
1239 close c_sr_cont;
1240
1241 if l_contact_type = 'PERSON' then
1242 open c_party;
1243 fetch c_party into l_contact_name;
1244 --l_contact_num, l_contact_name, l_contact_phone, l_contact_email;
1245 close c_party;
1246
1247 elsif l_contact_type = 'PARTY_RELATIONSHIP' then
1248 open c_rel(l_contact_party_id);
1249 fetch c_rel into l_contact_name;
1250 --l_contact_num, l_contact_name, l_contact_phone, l_contact_email;
1251 close c_rel;
1252
1253 elsif l_contact_type = 'EMPLOYEE' then
1254 OPEN c_emp(l_contact_party_id);
1255 FETCH c_emp into l_contact_name,
1256 --l_contact_num, l_contact_name, l_contact_phone, l_contact_email
1257 l_effective_start_date,l_effective_end_date;
1258 CLOSE c_emp;
1259 else
1260 return null;
1261 end if;
1262
1263 /*if l_contact_name is not null then
1264 return l_contact_num||p_separator||
1265 l_contact_name||p_separator||
1266 l_contact_phone||p_separator||
1267 l_contact_email;
1268 end if;*/
1269
1270 return l_contact_name;
1271
1272
1273 EXCEPTION
1274 WHEN OTHERS THEN
1275 IF c_sr_cont%ISOPEN THEN
1276 CLOSE c_sr_cont;
1277 END IF;
1278 IF c_emp%ISOPEN THEN
1279 CLOSE c_emp;
1280 END IF;
1281 IF c_party%ISOPEN THEN
1282 CLOSE c_party;
1283 END IF;
1284 IF c_rel%ISOPEN THEN
1285 CLOSE c_rel;
1286 END IF;
1287 END GET_CONTACT_NAME;
1288
1289 -- -------------------------------------------------------------------------
1290 FUNCTION GET_REL_OBJ_DETAILS
1291 ( p_object_type IN VARCHAR2,
1292 p_object_id IN NUMBER
1293 )
1294 RETURN VARCHAR2
1295 IS
1296 CURSOR c_jtf_object
1297 (p_object_type IN VARCHAR2
1298 )IS SELECT select_id,
1299 select_details ,
1300 from_table ,
1301 where_clause
1302 FROM jtf_objects_b
1303 WHERE select_id is not null
1304 and select_details is not null
1305 and from_table is not null
1306 AND object_code = p_object_type;
1307
1308 l_select_id jtf_objects_b.select_id%TYPE;
1309 l_select_details jtf_objects_b.select_details%TYPE;
1310 l_from_table jtf_objects_b.from_table%TYPE;
1311 l_where_clause jtf_objects_b.where_clause%TYPE;
1312 l_sql_statement VARCHAR2(3000);
1313 l_details VARCHAR2(5000);
1314 l_select_id_alias jtf_objects_b.select_id%TYPE;
1315 l_select_id_value NUMBER;
1316 position NUMBER;
1317
1318 type details_type is REF CURSOR;
1319 details_cursor details_type;
1320
1321 BEGIN
1322 IF c_jtf_object%ISOPEN
1323 THEN
1324 CLOSE c_jtf_object;
1325 END IF;
1326
1327 OPEN c_jtf_object(p_object_type);
1328 FETCH c_jtf_object INTO l_select_id,
1329 l_select_details,
1330 l_from_table,
1331 l_where_clause;
1332 IF c_jtf_object%ROWCOUNT = 0 THEN
1333 RETURN NULL;
1334 END IF;
1335 CLOSE c_jtf_object;
1336
1337 --contruct the sql statement for this object type
1338 l_sql_statement := 'SELECT ' || l_select_details || ', ' || l_select_id
1339 || ' FROM ' || l_from_table;
1340 if l_where_clause is not null then
1341 l_sql_statement := l_sql_statement || ' WHERE '|| l_where_clause;
1342 end if;
1343
1344 position := instr(l_select_id, ' ', -1, 1);
1345 l_select_id_alias := substr(l_select_id, position+1);
1346
1347 if position <> 0 then
1348 l_sql_statement := 'SELECT * FROM (' ||
1349 l_sql_statement ||
1350 ' ) ' ||
1351 ' WHERE ' ||
1352 l_select_id_alias || ' = :select_id';
1353
1354 else
1355 if l_where_clause is null then
1356 l_sql_statement := l_sql_statement
1357 || ' WHERE '
1358 || l_select_id || ' = :select_id';
1359 else
1360 l_sql_statement := l_sql_statement
1361 || ' AND '
1362 || l_select_id || ' = :select_id';
1363 end if;
1364 end if;
1365
1366 open details_cursor for l_sql_statement using p_object_id;
1367 fetch details_cursor into l_details, l_select_id_value;
1368 if details_cursor%NOTFOUND then
1369 null; -- Hardcode
1370 end if;
1371 close details_cursor;
1372
1373 return l_details;
1374 END GET_REL_OBJ_DETAILS;
1375
1376 -- -------------------------------------------------------------------------
1377 -- Start of comments
1378 -- UTIL Name : get_assc_party_name
1379 -- Type : Private
1380 -- Description : To get the AsscPartyname based on AsscPartyId,AsscPartyType
1381 -- Parameters :
1382 -- IN: p_assc_party_type IN VARCHAR2
1383 -- IN : p_assc_party_id IN NUMBER
1384 -- Returnvalue:
1385 -- l_assc_party_name VARCHAR2(360)
1386 -- End of comments
1387 -- -------------------------------------------------------------------------
1388 FUNCTION get_assc_party_name
1389 ( p_assc_party_type IN VARCHAR2
1390 ,p_assc_party_id IN NUMBER
1391 ) RETURN VARCHAR2 as
1392 --
1393 l_assc_party_name varchar2(360) DEFAULT NULL;
1394 l_employee_name varchar2(360) DEFAULT NULL;
1395 l_effective_start_date DATE DEFAULT NULL;
1396 l_effective_end_date DATE DEFAULT NULL;
1397 --
1398 cursor c1(param_person_id NUMBER) is
1399 select full_name,effective_start_date,effective_end_date
1400 from per_all_people_f
1401 where person_id = param_person_id
1402 order by effective_start_date desc;
1403 --
1404 begin
1405 --
1406 if p_assc_party_type = 'PERSON' or p_assc_party_type ='PARTY_RELATIONSHIP' OR
1407 p_assc_party_type ='ORGANIZATION' then
1408 select hz.party_name
1409 into l_assc_party_name
1410 from hz_parties hz
1411 where hz.party_id = p_assc_party_id;
1412 elsif p_assc_party_type = 'EMPLOYEE' then
1413 OPEN c1(p_assc_party_id);
1414 LOOP
1415 FETCH c1 into
1416 l_employee_name,l_effective_start_date,l_effective_end_date;
1417 EXIT WHEN c1%NOTFOUND;
1418 if (l_effective_start_date is not null and l_employee_name is not
1419 null) then
1420 l_assc_party_name := l_employee_name;
1421 EXIT;
1422 end if;
1423 END LOOP;
1424 CLOSE c1;
1425 else
1426 return null;
1427 end if;
1428 return l_assc_party_name;
1429 end;
1430 -- -------------------------------------------------------------------------
1431 -- Start of comments
1432 -- UTIL Name : get_concat_associated_role
1433 -- Type : Private
1434 -- Description : To get associated party roles for incident_id
1435 -- Parameters :
1436 -- IN : p_incident_id IN NUMBER
1437 -- IN : p_party_id IN NUMBER
1438 -- IN : p_party_type IN VARCHAR2
1439 -- Returnvalue:
1440 -- l_assc_party_role_names VARCHAR2(360)
1441 -- End of comments
1442 FUNCTION get_concat_associated_role(
1443 p_incident_id IN NUMBER
1444 ,p_party_id IN NUMBER
1445 ,p_party_type IN VARCHAR2)
1446 RETURN VARCHAR2 as
1447 l_part_role_name varchar2(360) DEFAULT NULL;
1448 l_concat_party_role_names varchar2(360) DEFAULT NULL;
1449 -- Person cursor
1450 cursor personCursor(p_incident_id NUMBER, p_party_id NUMBER) is
1451 select distinct partyrole.name
1452 from cs_hz_sr_contact_points cp, CS_PARTY_ROLES_VL partyrole
1453 where cp.party_role_code = partyrole.party_role_code
1454 and cp.incident_id = p_incident_id
1455 and
1456 (
1457 (cp.contact_type = 'PERSON'
1458 and cp.party_id = p_party_id)
1459 or
1460 (cp.contact_type = 'PARTY_RELATIONSHIP'
1461 and cp.party_id in ( select party_id
1462 from hz_relationships
1463 where subject_id = p_party_id
1464 and subject_type = 'PERSON')
1465 )
1466 );
1467
1468 -- Organization cursor
1469 cursor OrgEmpRelationshipCursor(p_incident_id NUMBER,
1470 p_party_id NUMBER,
1471 p_party_type VARCHAR2) is
1472 select distinct partyrole.name
1473 from cs_hz_sr_contact_points cp, CS_PARTY_ROLES_VL partyrole
1474 where cp.party_role_code = partyrole.party_role_code
1475 and cp.incident_id = p_incident_id
1476 and
1477 (
1478 cp.contact_type = p_party_type
1479 and cp.party_id = p_party_id
1480 );
1481 begin
1482 -- For a particular case Id write a cursor to get all the roles for the party
1483 -- Get the roles from the cursor and concatenated to the varchar and return back the final string.
1484 if p_party_type = 'PERSON' then
1485 OPEN PersonCursor(p_incident_id, p_party_id);
1486 LOOP
1487 FETCH PersonCursor into l_part_role_name;
1488 EXIT WHEN PersonCursor%NOTFOUND;
1489 if ( l_part_role_name is not null) then
1490 if (l_concat_party_role_names is not null) then
1491 l_concat_party_role_names := l_concat_party_role_names || ',' || l_part_role_name ;
1492 else
1493 l_concat_party_role_names := l_part_role_name ;
1494 end if;
1495 end if;
1496 END LOOP;
1497 CLOSE PersonCursor;
1498 else
1499 OPEN OrgEmpRelationshipCursor(p_incident_id, p_party_id, p_party_type);
1500 LOOP
1501 FETCH OrgEmpRelationshipCursor into l_part_role_name;
1502 EXIT WHEN OrgEmpRelationshipCursor%NOTFOUND;
1503 if ( l_part_role_name is not null) then
1504 if (l_concat_party_role_names is not null) then
1505 l_concat_party_role_names := l_concat_party_role_names || ',' || l_part_role_name ;
1506 else
1507 l_concat_party_role_names := l_part_role_name ;
1508 end if;
1509 end if;
1510 END LOOP;
1511 CLOSE OrgEmpRelationshipCursor;
1512 end if;
1513 return l_concat_party_role_names;
1514 end;
1515 -- -------------------------------------------------------------------------
1516 -- Start of comments
1517 -- UTIL Name : get_emp_contact_name
1518 -- Type : Private
1519 -- Description : To get the emp contact name based on the party id
1520 -- Parameters :
1521 -- IN : p_person_id IN NUMBER
1522 -- Returnvalue:
1523 -- l_emp_contact_name VARCHAR2(360)
1524 -- End of comments
1525 -- -------------------------------------------------------------------------
1526 FUNCTION get_emp_contact_name
1527 (p_person_id IN NUMBER
1528 ) RETURN VARCHAR2 as
1529
1530 cursor get_emp_name is
1531 SELECT first_name || ' ' ||last_name
1532 FROM per_workforce_x
1533 WHERE person_id = p_person_id;
1534
1535 l_emp_contact_name varchar2(360);
1536
1537 begin
1538 l_emp_contact_name := null;
1539 open get_emp_name;
1540 fetch get_emp_name into l_emp_contact_name;
1541 close get_emp_name;
1542
1543 return l_emp_contact_name;
1544 end;
1545 -- -------------------------------------------------------------------------
1546 -- Start of comments
1547 -- UTIL Name : get_emp_contact_email
1548 -- Type : Private
1549 -- Description : To get the emp contact email based on the person id
1550 -- Parameters :
1551 -- IN : p_person_id IN NUMBER
1552 -- Returnvalue:
1553 -- l_emp_contact_email VARCHAR2(250)
1554 -- End of comments
1555 -- -------------------------------------------------------------------------
1556 FUNCTION get_emp_contact_email
1557 (p_person_id IN NUMBER
1558 ) RETURN VARCHAR2 as
1559
1560 cursor get_emp_email is
1561 SELECT email_address
1562 FROM per_workforce_x
1563 WHERE person_id = p_person_id;
1564
1565 l_emp_contact_email varchar2(250);
1566
1567 begin
1568 l_emp_contact_email := null;
1569 open get_emp_email;
1570 fetch get_emp_email into l_emp_contact_email;
1571 close get_emp_email;
1572
1573 return l_emp_contact_email;
1574 end;
1575
1576
1577 -- -------------------------------------------------------------------------
1578 -- Start of comments
1579 -- UTIL Name : GET_SR_ESCALATION_LEVEL
1580 -- Type : Private
1581 -- Description : Returns the value of Service Request escalation if present,
1582 -- else returns null
1583 -- Parameters :
1584 -- IN : p_incident_id IN NUMBER Required
1585 --
1586 --- Returnvalue:
1587 -- l_sr_escalation_level VARCHAR2
1588 -- End of comments
1589 -- --------------------------------------------------------------------------
1590 Function GET_SR_ESCALATION_LEVEL
1591 ( p_incident_id IN NUMBER
1592 ) RETURN VARCHAR2 as
1593 l_esc_level VARCHAR2(80) DEFAULT NULL;
1594 cursor c_escal_level(param_incident_id NUMBER) is
1595 select esc.esc_level_name
1596 from jtf_escalation_task_v esc
1597 where esc.task_id = (
1598 select max(jtf.task_id)
1599 from jtf_escalation_task_v jtf,
1600 jtf_task_references_b trf
1601 where jtf.task_id = trf.task_id
1602 and trf.object_type_code = 'SR'
1603 and trf.object_id = param_incident_id);
1604
1605 BEGIN
1606 open c_escal_level (p_incident_id);
1607 fetch c_escal_level into l_esc_level;
1608 close c_escal_level;
1609
1610 RETURN l_esc_level;
1611
1612 EXCEPTION
1613 WHEN OTHERS THEN
1614 IF c_escal_level%ISOPEN THEN
1615 CLOSE c_escal_level;
1616 END IF;
1617 END GET_SR_ESCALATION_LEVEL;
1618
1619 FUNCTION GET_STATUS_GROUP_ID
1620 (P_INCIDENT_TYPE_ID IN NUMBER,
1621 P_RESP_ID IN NUMBER) RETURN VARCHAR2
1622 AS
1623 L_EXECUTE VARCHAR2(10) := 1; --default value is query1
1624 L_STATUS_GROUP_ID NUMBER := -1;
1625 L_DEFAULT_INCIDENT_STATUS_ID NUMBER := -1;
1626 l_ret_val VARCHAR2(300);
1627 BEGIN
1628
1629 SELECT DECODE(TRANSITION_IND,'Y',2,'N',3) TO_EXECUTE,
1630 STATUS_GROUP_ID ,
1631 DEFAULT_INCIDENT_STATUS_ID
1632 INTO l_execute,
1633 l_status_group_id,
1634 l_default_incident_status_id
1635 from
1636 ( SELECT DECODE(TRANSITION_IND, NULL, 'N', TRANSITION_IND) TRANSITION_IND,
1637 ST.STATUS_GROUP_ID,
1638 DEFAULT_INCIDENT_STATUS_ID
1639 FROM
1640 --if the value is -1 then query1 ottherwisse get transition id nad stuff
1641 (select DECODE((select status_group_id
1642 FROM CS_SR_TYPE_MAPPING
1643 WHERE INCIDENT_TYPE_ID = P_INCIDENT_TYPE_ID
1644 and responsibility_id = P_RESP_ID
1645 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
1646 AND TRUNC(NVL(END_DATE,SYSDATE))),
1647 NULL,DECODE((select status_group_id
1648 FROM CS_INCIDENT_TYPES_B
1649 where incident_type_id = P_INCIDENT_TYPE_ID
1650 and incident_subtype = 'INC'
1651 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE))
1652 AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))),NULL,null,(select status_group_id
1653 FROM CS_INCIDENT_TYPES_B
1654 where incident_type_id = P_INCIDENT_TYPE_ID
1655 and incident_subtype = 'INC'
1656 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE))
1657 AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))) ),
1658 (select status_group_id
1659 FROM CS_SR_TYPE_MAPPING
1660 WHERE INCIDENT_TYPE_ID = P_INCIDENT_TYPE_ID
1661 and responsibility_id =P_RESP_ID
1662 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
1663 AND TRUNC(NVL(END_DATE,SYSDATE))) ) STATUS_GROUP_ID
1664 FROM DUAL) ST,
1665 CS_SR_STATUS_GROUPS_B B
1666 WHERE b.STATUS_GROUP_ID =st.status_group_id
1667 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE)) AND TRUNC(NVL(END_DATE,SYSDATE))
1668 ) FT;
1669
1670 L_RET_VAL := L_EXECUTE||','|| L_STATUS_GROUP_ID||','|| L_DEFAULT_INCIDENT_STATUS_ID;
1671 return l_ret_val;
1672 EXCEPTION
1673 WHEN NO_DATA_FOUND THEN
1674 L_RET_VAL := L_EXECUTE||','|| L_STATUS_GROUP_ID||','|| L_DEFAULT_INCIDENT_STATUS_ID;
1675 RETURN L_RET_VAL;
1676 END GET_STATUS_GROUP_ID;
1677
1678 -- -------------------------------------------------------------------------
1679 -- Start of comments
1680 -- UTIL Name : GET_INCIDENT_TIMEZONE_ID
1681 -- Type : Private
1682 -- Description : Returns the value of incident timezone if present,
1683 -- else returns null
1684 -- Parameters :
1685 -- IN : P_LOCATION_ID IN NUMBER Required
1686 -- P_LOCATION_TYPE IN VARCHAR Required
1687 --- Returnvalue:
1688 -- l_incident_time_zone_id NUMBER
1689 -- End of comments
1690 -- --------------------------------------------------------------------------
1691 FUNCTION GET_INCIDENT_TIMEZONE_ID
1692 ( P_INCIDENT_LOCATION_ID IN NUMBER,
1693 P_INCIDENT_LOCATION_TYPE IN VARCHAR2 )
1694 RETURN NUMBER as
1695 l_INCIDENT_TIMEZONE_ID NUMBER DEFAULT NULL;
1696
1697 CURSOR Incident_party_site_timezone IS
1698 SELECT b.timezone_id
1699 FROM hz_party_sites a, hz_locations b
1700 WHERE a.location_id = b.location_id
1701 AND a.party_site_id = p_incident_location_id ;
1702
1703 -- Cursor to derive time zone for location
1704 CURSOR Incident_location_timezone IS
1705 SELECT timezone_id
1706 FROM hz_locations
1707 WHERE location_id = p_incident_location_id ;
1708 BEGIN
1709
1710 IF (p_incident_location_id is not null) THEN
1711
1712 IF (p_incident_location_type = 'HZ_PARTY_SITE') THEN
1713 OPEN Incident_party_site_timezone ;
1714 FETCH Incident_party_site_timezone into l_incident_timezone_id ;
1715 IF (Incident_party_site_timezone%notfound) THEN
1716 null ;
1717 END IF ;
1718 CLOSE Incident_party_site_timezone ;
1719 END IF ;
1720
1721 IF (p_incident_location_type = 'HZ_LOCATION') THEN
1722 OPEN Incident_location_timezone ;
1723 FETCH Incident_location_timezone into l_incident_timezone_id ;
1724 IF (Incident_location_timezone%notfound) THEN
1725 null ;
1726 END IF ;
1727 CLOSE Incident_location_timezone ;
1728 END IF ;
1729 END IF;
1730
1731 RETURN l_incident_timezone_id;
1732 END GET_INCIDENT_TIMEZONE_ID;
1733
1734 -- -------------------------------------------------------------------------
1735 -- Start of comments
1736 -- UTIL Name : GET_CONTACT_TIMEZONE_ID
1737 -- Type : Private
1738 -- Description : Returns the value of contact timezone if present,
1739 -- else returns null
1740 -- Parameters :
1741 -- IN : P_CONTACT_PATRY_ID IN NUMBER Required
1742 -- P_CONTACT_PREF_TIMEZONE_ID IN NUMBER Required
1743 --- Returnvalue:
1744 -- l_contact_time_zone_id NUMBER
1745 -- End of comments
1746 -- --------------------------------------------------------------------------
1747 FUNCTION GET_CONTACT_TIMEZONE_ID
1748 ( P_CONTACT_PARTY_ID IN NUMBER,
1749 P_CONTACT_PREF_TIMEZONE_ID IN NUMBER
1750 )
1751 RETURN NUMBER as
1752 L_contact_timezone_id NUMBER DEFAULT NULL;
1753 l_contact_point_timezone_id NUMBER DEFAULT NULL;
1754
1755 -- Cursor to derive time zone for Primary Contact address
1756 CURSOR Contact_timezone IS
1757 SELECT b.timezone_id
1758 FROM hz_party_sites a, hz_locations b
1759 WHERE a.location_id = b.location_id
1760 AND a.identifying_address_flag = 'Y'
1761 AND a.party_id = p_contact_party_id ;
1762
1763 -- Cursor to derive time zone for Primary Contact phone
1764 CURSOR Contact_cont_point_tz IS
1765 SELECT timezone_id
1766 FROM hz_contact_points
1767 WHERE contact_point_type = 'PHONE'
1768 AND primary_flag = 'Y'
1769 AND owner_table_id = p_contact_party_id ;
1770
1771 BEGIN
1772 IF (p_contact_pref_timezone_id is not null) THEN
1773 RETURN p_contact_pref_timezone_id;
1774 END IF;
1775
1776 IF (p_contact_party_id is not null) THEN
1777 OPEN Contact_timezone ;
1778 FETCH Contact_timezone into l_contact_timezone_id ;
1779 IF (Contact_timezone%notfound) THEN
1780 null ;
1781 END IF ;
1782 CLOSE Contact_timezone ;
1783 IF (l_contact_timezone_id is not null) THEN
1784 RETURN l_contact_timezone_id;
1785 END IF ;
1786
1787 OPEN Contact_cont_point_tz ;
1788 FETCH Contact_cont_point_tz into l_contact_point_timezone_id ;
1789 IF (Contact_cont_point_tz%notfound) THEN
1790 null ;
1791 END IF ;
1792 CLOSE Contact_cont_point_tz ;
1793 IF (l_contact_point_timezone_id is not null) THEN
1794 RETURN l_contact_point_timezone_id;
1795 END IF;
1796 RETURN l_contact_point_timezone_id;
1797 END IF ;
1798 -- If none of the conditions are satisfied, return null,default value of L_contact_timezone_id
1799 RETURN L_contact_timezone_id;
1800 END GET_CONTACT_TIMEZONE_ID;
1801
1802 -- -------------------------------------------------------------------------
1803 -- Start of comments
1804 -- UTIL Name : GET_CUSTOMER_TIMEZONE_ID
1805 -- Type : Private
1806 -- Description : Returns the value of customer timezone if present for a task,
1807 -- else returns null
1808 -- Parameters :
1809 -- IN : P_CUSTOMER_ID IN NUMBER Required
1810 --- Returnvalue:
1811 -- l_customer_time_zone_id NUMBER
1812 -- End of comments
1813 -- --------------------------------------------------------------------------
1814 FUNCTION GET_CUSTOMER_TIMEZONE_ID
1815 ( P_CUSTOMER_ID IN NUMBER
1816 )
1817 RETURN NUMBER as
1818 l_customer_timezone_id NUMBER DEFAULT NULL;
1819
1820 CURSOR Cur_Customer_Timezone IS
1821 SELECT b.timezone_id
1822 FROM hz_party_sites a, hz_locations b
1823 WHERE a.location_id = b.location_id
1824 AND a.identifying_address_flag = 'Y'
1825 AND a.party_id = p_customer_id ;
1826
1827 -- Cursor to derive time zone for Customer phone
1828 CURSOR Cur_Customer_Cont_Point_Tz IS
1829 SELECT timezone_id
1830 FROM hz_contact_points
1831 WHERE contact_point_type = 'PHONE'
1832 AND primary_flag = 'Y'
1833 AND owner_table_id = p_customer_id ;
1834 BEGIN
1835 OPEN Cur_Customer_Timezone ;
1836 FETCH Cur_Customer_Timezone into l_customer_timezone_id ;
1837 IF (l_customer_timezone_id is not null) THEN
1838 RETURN l_customer_timezone_id;
1839 END IF ;
1840 CLOSE Cur_Customer_Timezone ;
1841
1842 OPEN Cur_Customer_Cont_Point_Tz ;
1843 FETCH Cur_Customer_Cont_Point_Tz into l_customer_timezone_id ;
1844 IF (l_customer_timezone_id is not null) THEN
1845 RETURN l_customer_timezone_id;
1846 END IF ;
1847 CLOSE Cur_Customer_Cont_Point_Tz ;
1848
1849 RETURN l_customer_timezone_id;
1850 END GET_CUSTOMER_TIMEZONE_ID;
1851
1852 end csz_servicerequest_util_pvt;
[Home] [Help]