1 package body csz_servicerequest_util_pvt as
2 /* $Header: cszvutlb.pls 120.12 2006/06/30 19:05:24 awwong noship $ */
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;
401 , p_init_msg_list => 'T'
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
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');
517 fnd_msg_pub.count_and_get
514 fnd_msg_pub.add_detail(p_message_type => fnd_msg_pub.G_WARNING_MSG);
515 end if;
516 --
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 --
664 x_ent_contracts(1).service_name;
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||' : '||
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 -- -------------------------------------------------------------------------
797 -- Type : Private
794 -- -------------------------------------------------------------------------
795 -- Start of comments
796 -- UTIL Name : GET_TIMEZONE_FOR_CONTACT
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 SELECT timezone_id
855 INTO l_timezone_id
856 FROM hz_contact_points
857 WHERE contact_point_id = p_contact_point_id
858 AND status = 'A' ;
859 if l_timezone_id is not null then
860 SELECT name ,
861 timezone_code
862 INTO l_timezone_name,
863 l_timezone_code
864 FROM FND_TIMEZONES_VL
865 WHERE UPGRADE_TZ_ID = l_timezone_id
866 AND ENABLED_FLAG = 'Y';
867
868 return l_timezone_id || '::' || l_timezone_name || '::' || l_timezone_code;
869 end if;
870 end if;
871
872 l_timezone_id := GET_TIMEZONE_FOR_LOCATION(p_contact_id);
873 -- Timezone name is also queried and Concatenated with a hyphen
874 if l_timezone_id is not null then
875 SELECT name ,
876 timezone_code
877 INTO l_timezone_name,
878 l_timezone_code
879 FROM FND_TIMEZONES_VL
880 WHERE UPGRADE_TZ_ID = l_timezone_id
881 AND ENABLED_FLAG = 'Y';
882 end if;
883 return l_timezone_id || '::' || l_timezone_name || '::' || l_timezone_code;
884
885 EXCEPTION
886 when others then
887 return null;
888 END;
889
890 --
891 -- --------------------------------------------------------------------------------
892
893 -- Start of comments
894 -- UTIL Name : GET_FIRST_NOTE
895 -- Type : Private
896 -- Description : Given a INCIDENT_ID this function will return the recently
897 --- added note to the service request number.
898 -- Parameters :
899 -- IN : p_incident_id NUMBER Required
900 -- OUT: Returns l_first_note VARCHAR2(2000)
901 -- End of comments
902 -- -------------------------------------------------------------------------
903 Function GET_FIRST_NOTE
904 ( p_incident_id IN NUMBER)
905 RETURN VARCHAR2 IS
906 l_first_note varchar2(2000) DEFAULT NULL;
907 CURSOR c_first_note(param_incident_id NUMBER) is
908 select notes
909 from jtf_notes_vl
910 where source_object_code = 'SR' and source_object_id =param_incident_id
911 order by creation_date;
912 begin
913 open c_first_note (p_incident_id);
914 fetch c_first_note into l_first_note;
915 close c_first_note;
916 RETURN l_first_note ;
917
918 EXCEPTION
919 WHEN OTHERS THEN
920 IF c_first_note%ISOPEN THEN
921 CLOSE c_first_note;
922 END IF;
923 return null;
924 end GET_FIRST_NOTE;
925 -- -------------------------------------------------------------------------
926 -- Start of comments
927 -- UTIL Name : CHECK_IF_NEXT_WORK_ENABLED
928 -- Type : Private
929 -- Description : Calls the
930 -- IEU_WR_PUB. CHECK_WS_ACTIVATION_STATUS
931 -- to verify if the activation flag is turned on
932 -- for the worksource code
933 -- Parameters :
934 -- IN : p_ws_code VARCHAR2 Required
935 -- OUT : x_enable_next_work NUMBER
939 -- End of comments
936 -- OUT : x_msg_count NUMBER
937 -- OUT : x_return_status VARCHAR2
938 -- OUT : x_msg_data VARCHAR2
940 -- ------------------------------------------------------------------------------------
941 PROCEDURE CHECK_IF_NEXT_WORK_ENABLED
942 ( p_ws_code IN VARCHAR2,
943 x_enable_next_work OUT nocopy VARCHAR2,
944 x_msg_count OUT nocopy NUMBER,
945 x_return_status OUT nocopy VARCHAR2,
946 x_msg_data OUT nocopy VARCHAR2) IS
947
948 BEGIN
949
950 BEGIN
951 -- Invoke the IEU API to get the Activation status of work source
952
953 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS
954 ( p_api_version => 1,
955 p_ws_code =>p_ws_code,
956 x_ws_activation_status =>x_enable_next_work,
957 x_msg_count => x_msg_count,
958 x_msg_data => x_msg_data,
959 x_return_status => x_return_status);
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 fnd_msg_pub.Count_and_Get
964 (
965 p_count => x_msg_count,
966 p_data => x_msg_data
967 );
968 RAISE fnd_api.g_exc_error;
969 END;
970
971
972 Exception
973
974 WHEN fnd_api.g_exc_error THEN
975 x_return_status := 'E';
976 fnd_msg_pub.Count_and_Get
977 (
978 p_count => x_msg_count,
979 p_data => x_msg_data
980 );
981
982 WHEN fnd_api.g_exc_unexpected_error THEN
983 x_return_status := 'U';
984 fnd_msg_pub.Count_and_Get
985 (
986 p_count => x_msg_count,
987 p_data => x_msg_data
988 );
989
990 END CHECK_IF_NEXT_WORK_ENABLED;
991 -- ------------------------------------------------------------------------------------
992 -- Start of comments
993 -- UTIL Name : GET_NEXT_SR_TO_WORK
994 -- Type : Private
995 -- Description : Calls IEU_WR_PUB.GET_NEXT_WORK_FOR_APPS
996 -- and returns the incident_id retreived from the above call.
997 -- Parameters :
998 -- IN : p_ws_code VARCHAR2 Required
999 -- IN : p_resource_id NUMBER Required
1000 -- OUT : x_incident_id NUMBER
1001 -- OUT : x_msg_count NUMBER
1002 -- OUT : x_return_status VARCHAR2
1003 -- OUT : x_msg_data VARCHAR2
1004 -- End of comments
1005
1006 -- ------------------------------------------------------------------------------------
1007 PROCEDURE GET_NEXT_SR_TO_WORK
1008 ( p_ws_code IN VARCHAR2,
1009 p_resource_id IN NUMBER,
1010 x_incident_id OUT nocopy NUMBER,
1011 x_msg_count OUT nocopy NUMBER,
1012 x_return_status OUT nocopy VARCHAR2,
1013 x_msg_data OUT nocopy VARCHAR2,
1014 x_object_type OUT nocopy VARCHAR2) IS
1015
1016 --declare variables
1017 l_ws_det_list IEU_UWQ_GET_NEXT_WORK_PVT.IEU_WS_DETAILS_LIST;
1018 l_uwqm_workitem_data IEU_FRM_PVT.T_IEU_MEDIA_DATA;
1019 l_language VARCHAR2(10);
1020 l_source_lang VARCHAR2(10);
1021 j number;
1022 x_enable_next_work VARCHAR2(10);
1023 x_distribute_sr_task VARCHAR2(10);
1024 BEGIN
1025 l_ws_det_list(1).ws_code := p_ws_code;
1026 l_language := userenv('lang');
1027 l_source_lang := 'US';
1028 j :=0;
1029
1030 BEGIN
1031 -- Check if SR-TASK worksource is enabled
1032 CHECK_IF_NEXT_WORK_ENABLED
1033 ( p_ws_code => 'SR_TASKS',
1034 x_enable_next_work =>x_enable_next_work ,
1035 x_msg_count => x_msg_count ,
1036 x_return_status => x_return_status ,
1037 x_msg_data=> x_msg_data);
1038
1039 -- Check if IEU: Distribute: Service Request Task: Work Source
1040
1041
1042
1043
1044 -- profile is enabled
1045 fnd_profile.get('IEU_WR_DIS_SR_TASKS_WS', x_distribute_sr_task);
1046
1047 -- If SR_TASK is enabled, pass SR-TASK also as object code
1048 If( (x_enable_next_work = 'Y') AND (x_distribute_sr_task = 'Y')) THEN
1049 l_ws_det_list(2).ws_code := 'SR_TASKS';
1050 END IF;
1051
1052 -- Invoke the IEU API to get the incident Id
1053 IEU_WR_PUB.GET_NEXT_WORK_FOR_APPS
1054 ( p_api_version => 1,
1055 p_resource_id => p_resource_id,
1056 p_language => l_language,
1057 p_source_lang => l_source_lang,
1058 p_ws_det_list => l_ws_det_list,
1059 x_uwqm_workitem_data => l_uwqm_workitem_data,
1060 x_msg_count => x_msg_count,
1061 x_msg_data => x_msg_data,
1062 x_return_status => x_return_status);
1063
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 fnd_msg_pub.Count_and_Get
1067 (
1068 p_count => x_msg_count,
1069 p_data => x_msg_data
1070 );
1071 RAISE fnd_api.g_exc_error;
1072 END;
1073
1074 -- If the return status is Success or if the OUT param l_uwqm_workitem_data
1078 if (x_return_status = 'S') OR (l_uwqm_workitem_data.count >= 1)
1075 -- has values. Then retrieve the first row and get the incident_id
1076 -- which in the WORKITEM_PK_ID
1077
1079 then
1080
1081 FOR j in l_uwqm_workitem_data.first .. l_uwqm_workitem_data.last
1082 LOOP
1083
1084 if (l_uwqm_workitem_data(j).param_name = 'WORKITEM_PK_ID')
1085 then
1086 x_incident_id := l_uwqm_workitem_data(j).param_value;
1087 end if;
1088 if ( l_uwqm_workitem_data(j).param_name = 'WORKITEM_OBJ_CODE')
1089 then
1090 x_object_type := l_uwqm_workitem_data(j).param_value;
1091 end if;
1092
1093 if( x_incident_id <> NULL) AND (x_object_type <> NULL) then
1094 exit;
1095 end if;
1096 end loop;
1097 end if;
1098
1099 Exception
1100
1101 WHEN fnd_api.g_exc_error THEN
1102 x_return_status := 'E';
1103 fnd_msg_pub.Count_and_Get
1104 (
1105 p_count => x_msg_count,
1106 p_data => x_msg_data
1107 );
1108
1109 WHEN fnd_api.g_exc_unexpected_error THEN
1110 x_return_status := 'U';
1111 fnd_msg_pub.Count_and_Get
1112 (
1113 p_count => x_msg_count,
1114 p_data => x_msg_data
1115 );
1116
1117 END GET_NEXT_SR_TO_WORK;
1118
1119 -- -------------------------------------------------------------------------
1120 -- Start of comments
1121 -- UTIL Name : GET_SR_ESCALATED
1122 -- Type : Private
1123 -- Description : Returns Y if Service Request is escalated, else returns N
1124 -- Parameters :
1125 -- IN : p_incident_id IN NUMBER Required
1126 --
1127 --- Returnvalue:
1128 -- l_sr_escalated VARCHAR2(1)
1129 -- End of comments
1130 -- --------------------------------------------------------------------------
1131 Function GET_SR_ESCALATED
1132 ( p_incident_id IN NUMBER
1133 )
1134 RETURN VARCHAR2 IS
1135 l_object_id NUMBER;
1136 cursor c_escal_id(param_incident_id NUMBER) is
1137 select trf.object_id
1138 from jtf_tasks_b tsk,
1139 jtf_task_references_b trf
1140 where tsk.task_id = trf.task_id
1141 and tsk.task_type_id = 22
1142 and tsk.escalation_level not in ('DE', 'NE')
1143 and nvl(tsk.open_flag, 'Y') = 'Y'
1144 and trf.reference_code = 'ESC'
1145 and trf.object_type_code = 'SR'
1146 and trf.object_id = param_incident_id;
1147 BEGIN
1148 open c_escal_id (p_incident_id);
1149 fetch c_escal_id into l_object_id;
1150 close c_escal_id;
1151
1152 if l_object_id is not null then
1153 return 'Y'; --if found escalated
1154 end if;
1155 return 'N'; --if not found
1156 EXCEPTION
1157 WHEN OTHERS THEN
1158 IF c_escal_id%ISOPEN THEN
1159 CLOSE c_escal_id;
1160 END IF;
1161 END GET_SR_ESCALATED;
1162 -- -------------------------------------------------------------------------
1163 -- -------------------------------------------------------------------------
1164 -- Start of comments
1165 -- UTIL Name : GET_CONTACT_NAME
1166 -- Type : Private
1167 -- Description : Returns contact name
1168 -- Parameters :
1169 -- IN : p_incident_id IN NUMBER
1170 -- p_customer_id IN NUMBER
1171 -- Returnvalue: contact name
1172 --
1173 -- (If we make this api get_contact_info, it can return
1174 -- <contact number>-separator<contact name>separator<phone>separator<email>)
1175 -- End of comments
1176 -- -------------------------------------------------------------------------
1177
1178 Function GET_CONTACT_NAME
1179 ( p_incident_id IN NUMBER,
1180 p_customer_id IN NUMBER
1181 --,p_separator IN VARCHAR --needed for get_contact_info api
1182 )
1183 RETURN VARCHAR2 IS
1184 l_return_val VARCHAR2(255);
1185 l_contact_party_id number;
1186 l_contact_type varchar2(30);
1187 l_effective_start_date DATE DEFAULT NULL;
1188 l_effective_end_date DATE DEFAULT NULL;
1189
1190 --l_contact_num varchar2(30);
1191 l_contact_name varchar2(360); --emp.fullname 240,party_name 360
1192 --l_contact_phone varchar2(83); --emp 60, party phone and space 83
1193 --l_contact_email varchar2(2000); --emp 240, party 2000
1194
1195
1196 cursor c_sr_cont is
1197 select sr_cont.party_id,
1198 sr_cont.contact_type
1199 from cs_hz_sr_contact_points sr_cont
1200 where sr_cont.incident_id = p_incident_id
1201 and sr_cont.primary_flag = 'Y';
1202
1203 cursor c_emp(param_party_contact_id NUMBER) is
1204 select --employee_number,
1205 full_name,
1206 --, work_telephone, email_address,
1207 effective_start_date,effective_end_date
1208 from per_all_people_f
1209 where person_id = param_party_contact_id
1210 order by effective_start_date desc;
1211
1212 cursor c_party is
1213 select --hz.party_number,
1214 hz.party_name
1215 --,hz.primary_phone_country_code||' '||
1216 --hz.primary_phone_area_code||' '||
1217 --hz.primary_phone_number||' '||
1218 --hz.primary_phone_extension,
1219 --hz.email_address
1220 from hz_parties hz
1224 select --hz.party_number,
1221 where hz.party_id = p_customer_id;
1222
1223 cursor c_rel(param_contact_party_id NUMBER) is
1225 hz.party_name
1226 --,hz.primary_phone_country_code||' '||
1227 --hz.primary_phone_area_code||' '||
1228 --hz.primary_phone_number||' '||
1229 --hz.primary_phone_extension,
1230 --hz.email_address
1231 from hz_parties hz, hz_relationships rel
1232 where rel.party_id = param_contact_party_id
1233 and rel.object_id = p_customer_id
1234 and rel.subject_id = hz.party_id
1235 and rel.subject_type = 'PERSON';
1236 BEGIN
1237
1238 open c_sr_cont;
1239 fetch c_sr_cont into l_contact_party_id, l_contact_type;
1240 close c_sr_cont;
1241
1242 if l_contact_type = 'PERSON' then
1243 open c_party;
1244 fetch c_party into l_contact_name;
1245 --l_contact_num, l_contact_name, l_contact_phone, l_contact_email;
1246 close c_party;
1247
1248 elsif l_contact_type = 'PARTY_RELATIONSHIP' then
1249 open c_rel(l_contact_party_id);
1250 fetch c_rel into l_contact_name;
1251 --l_contact_num, l_contact_name, l_contact_phone, l_contact_email;
1252 close c_rel;
1253
1254 elsif l_contact_type = 'EMPLOYEE' then
1255 OPEN c_emp(l_contact_party_id);
1256 FETCH c_emp into l_contact_name,
1257 --l_contact_num, l_contact_name, l_contact_phone, l_contact_email
1258 l_effective_start_date,l_effective_end_date;
1259 CLOSE c_emp;
1260 else
1261 return null;
1262 end if;
1263
1264 /*if l_contact_name is not null then
1265 return l_contact_num||p_separator||
1266 l_contact_name||p_separator||
1267 l_contact_phone||p_separator||
1268 l_contact_email;
1269 end if;*/
1270
1271 return l_contact_name;
1272
1273
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 IF c_sr_cont%ISOPEN THEN
1277 CLOSE c_sr_cont;
1278 END IF;
1279 IF c_emp%ISOPEN THEN
1280 CLOSE c_emp;
1281 END IF;
1282 IF c_party%ISOPEN THEN
1283 CLOSE c_party;
1284 END IF;
1285 IF c_rel%ISOPEN THEN
1286 CLOSE c_rel;
1287 END IF;
1288 END GET_CONTACT_NAME;
1289
1290 -- -------------------------------------------------------------------------
1291 FUNCTION GET_REL_OBJ_DETAILS
1292 ( p_object_type IN VARCHAR2,
1293 p_object_id IN NUMBER
1294 )
1295 RETURN VARCHAR2
1296 IS
1297 CURSOR c_jtf_object
1298 (p_object_type IN VARCHAR2
1299 )IS SELECT select_id,
1300 select_details ,
1301 from_table ,
1302 where_clause
1303 FROM jtf_objects_b
1304 WHERE select_id is not null
1305 and select_details is not null
1306 and from_table is not null
1307 AND object_code = p_object_type;
1308
1309 l_select_id jtf_objects_b.select_id%TYPE;
1310 l_select_details jtf_objects_b.select_details%TYPE;
1311 l_from_table jtf_objects_b.from_table%TYPE;
1312 l_where_clause jtf_objects_b.where_clause%TYPE;
1313 l_sql_statement VARCHAR2(3000);
1314 l_details VARCHAR2(5000);
1315 l_select_id_alias jtf_objects_b.select_id%TYPE;
1316 l_select_id_value NUMBER;
1317 position NUMBER;
1318
1319 type details_type is REF CURSOR;
1320 details_cursor details_type;
1321
1322 BEGIN
1323 IF c_jtf_object%ISOPEN
1324 THEN
1325 CLOSE c_jtf_object;
1326 END IF;
1327
1328 OPEN c_jtf_object(p_object_type);
1329 FETCH c_jtf_object INTO l_select_id,
1330 l_select_details,
1331 l_from_table,
1332 l_where_clause;
1333 IF c_jtf_object%ROWCOUNT = 0 THEN
1334 RETURN NULL;
1335 END IF;
1336 CLOSE c_jtf_object;
1337
1338 --contruct the sql statement for this object type
1339 l_sql_statement := 'SELECT ' || l_select_details || ', ' || l_select_id
1340 || ' FROM ' || l_from_table;
1341 if l_where_clause is not null then
1342 l_sql_statement := l_sql_statement || ' WHERE '|| l_where_clause;
1343 end if;
1344
1345 position := instr(l_select_id, ' ', -1, 1);
1346 l_select_id_alias := substr(l_select_id, position+1);
1347
1348 if position <> 0 then
1349 l_sql_statement := 'SELECT * FROM (' ||
1350 l_sql_statement ||
1351 ' ) ' ||
1352 ' WHERE ' ||
1353 l_select_id_alias || ' = :select_id';
1354
1355 else
1356 if l_where_clause is null then
1357 l_sql_statement := l_sql_statement
1358 || ' WHERE '
1359 || l_select_id || ' = :select_id';
1360 else
1361 l_sql_statement := l_sql_statement
1362 || ' AND '
1363 || l_select_id || ' = :select_id';
1364 end if;
1365 end if;
1366
1367 open details_cursor for l_sql_statement using p_object_id;
1368 fetch details_cursor into l_details, l_select_id_value;
1369 if details_cursor%NOTFOUND then
1370 null; -- Hardcode
1371 end if;
1372 close details_cursor;
1373
1374 return l_details;
1375 END GET_REL_OBJ_DETAILS;
1376
1377 -- -------------------------------------------------------------------------
1378 -- Start of comments
1379 -- UTIL Name : get_assc_party_name
1380 -- Type : Private
1381 -- Description : To get the AsscPartyname based on AsscPartyId,AsscPartyType
1382 -- Parameters :
1383 -- IN: p_assc_party_type IN VARCHAR2
1387 -- End of comments
1384 -- IN : p_assc_party_id IN NUMBER
1385 -- Returnvalue:
1386 -- l_assc_party_name VARCHAR2(360)
1388 -- -------------------------------------------------------------------------
1389 FUNCTION get_assc_party_name
1390 ( p_assc_party_type IN VARCHAR2
1391 ,p_assc_party_id IN NUMBER
1392 ) RETURN VARCHAR2 as
1393 --
1394 l_assc_party_name varchar2(360) DEFAULT NULL;
1395 l_employee_name varchar2(360) DEFAULT NULL;
1396 l_effective_start_date DATE DEFAULT NULL;
1397 l_effective_end_date DATE DEFAULT NULL;
1398 --
1399 cursor c1(param_person_id NUMBER) is
1400 select full_name,effective_start_date,effective_end_date
1401 from per_all_people_f
1402 where person_id = param_person_id
1403 order by effective_start_date desc;
1404 --
1405 begin
1406 --
1407 if p_assc_party_type = 'PERSON' or p_assc_party_type ='PARTY_RELATIONSHIP' OR
1408 p_assc_party_type ='ORGANIZATION' then
1409 select hz.party_name
1410 into l_assc_party_name
1411 from hz_parties hz
1412 where hz.party_id = p_assc_party_id;
1413 elsif p_assc_party_type = 'EMPLOYEE' then
1414 OPEN c1(p_assc_party_id);
1415 LOOP
1416 FETCH c1 into
1420 null) then
1417 l_employee_name,l_effective_start_date,l_effective_end_date;
1418 EXIT WHEN c1%NOTFOUND;
1419 if (l_effective_start_date is not null and l_employee_name is not
1421 l_assc_party_name := l_employee_name;
1422 EXIT;
1423 end if;
1424 END LOOP;
1425 CLOSE c1;
1426 else
1427 return null;
1428 end if;
1429 return l_assc_party_name;
1430 end;
1431 -- -------------------------------------------------------------------------
1432 -- Start of comments
1433 -- UTIL Name : get_concat_associated_role
1434 -- Type : Private
1435 -- Description : To get associated party roles for incident_id
1436 -- Parameters :
1437 -- IN : p_incident_id IN NUMBER
1438 -- IN : p_party_id IN NUMBER
1439 -- IN : p_party_type IN VARCHAR2
1440 -- Returnvalue:
1441 -- l_assc_party_role_names VARCHAR2(360)
1442 -- End of comments
1443 FUNCTION get_concat_associated_role(
1444 p_incident_id IN NUMBER
1445 ,p_party_id IN NUMBER
1446 ,p_party_type IN VARCHAR2)
1447 RETURN VARCHAR2 as
1448 l_part_role_name varchar2(360) DEFAULT NULL;
1449 l_concat_party_role_names varchar2(360) DEFAULT NULL;
1450 -- Person cursor
1451 cursor personCursor(p_incident_id NUMBER, p_party_id NUMBER) is
1452 select distinct partyrole.name
1453 from cs_hz_sr_contact_points cp, CS_PARTY_ROLES_VL partyrole
1454 where cp.party_role_code = partyrole.party_role_code
1455 and cp.incident_id = p_incident_id
1456 and
1457 (
1458 (cp.contact_type = 'PERSON'
1459 and cp.party_id = p_party_id)
1460 or
1461 (cp.contact_type = 'PARTY_RELATIONSHIP'
1462 and cp.party_id in ( select party_id
1463 from hz_relationships
1464 where subject_id = p_party_id
1465 and subject_type = 'PERSON')
1466 )
1467 );
1468
1469 -- Organization cursor
1470 cursor OrgEmpRelationshipCursor(p_incident_id NUMBER,
1471 p_party_id NUMBER,
1472 p_party_type VARCHAR2) is
1473 select distinct partyrole.name
1474 from cs_hz_sr_contact_points cp, CS_PARTY_ROLES_VL partyrole
1475 where cp.party_role_code = partyrole.party_role_code
1476 and cp.incident_id = p_incident_id
1477 and
1478 (
1479 cp.contact_type = p_party_type
1480 and cp.party_id = p_party_id
1481 );
1482 begin
1483 -- For a particular case Id write a cursor to get all the roles for the party
1484 -- Get the roles from the cursor and concatenated to the varchar and return back the final string.
1485 if p_party_type = 'PERSON' then
1486 OPEN PersonCursor(p_incident_id, p_party_id);
1487 LOOP
1488 FETCH PersonCursor into l_part_role_name;
1489 EXIT WHEN PersonCursor%NOTFOUND;
1490 if ( l_part_role_name is not null) then
1491 if (l_concat_party_role_names is not null) then
1492 l_concat_party_role_names := l_concat_party_role_names || ',' || l_part_role_name ;
1493 else
1494 l_concat_party_role_names := l_part_role_name ;
1495 end if;
1496 end if;
1497 END LOOP;
1498 CLOSE PersonCursor;
1499 else
1500 OPEN OrgEmpRelationshipCursor(p_incident_id, p_party_id, p_party_type);
1501 LOOP
1502 FETCH OrgEmpRelationshipCursor into l_part_role_name;
1503 EXIT WHEN OrgEmpRelationshipCursor%NOTFOUND;
1504 if ( l_part_role_name is not null) then
1505 if (l_concat_party_role_names is not null) then
1506 l_concat_party_role_names := l_concat_party_role_names || ',' || l_part_role_name ;
1507 else
1508 l_concat_party_role_names := l_part_role_name ;
1509 end if;
1510 end if;
1511 END LOOP;
1512 CLOSE OrgEmpRelationshipCursor;
1513 end if;
1514 return l_concat_party_role_names;
1515 end;
1516 -- -------------------------------------------------------------------------
1517 -- Start of comments
1518 -- UTIL Name : get_emp_contact_name
1519 -- Type : Private
1520 -- Description : To get the emp contact name based on the party id
1521 -- Parameters :
1522 -- IN : p_person_id IN NUMBER
1523 -- Returnvalue:
1524 -- l_emp_contact_name VARCHAR2(360)
1525 -- End of comments
1526 -- -------------------------------------------------------------------------
1527 FUNCTION get_emp_contact_name
1528 (p_person_id IN NUMBER
1529 ) RETURN VARCHAR2 as
1530
1531 cursor get_emp_name is
1532 SELECT first_name || ' ' ||last_name
1533 FROM per_workforce_x
1534 WHERE person_id = p_person_id;
1535
1536 l_emp_contact_name varchar2(360);
1537
1538 begin
1539 l_emp_contact_name := null;
1540 open get_emp_name;
1541 fetch get_emp_name into l_emp_contact_name;
1542 close get_emp_name;
1543
1544 return l_emp_contact_name;
1545 end;
1546 -- -------------------------------------------------------------------------
1547 -- Start of comments
1548 -- UTIL Name : get_emp_contact_email
1549 -- Type : Private
1550 -- Description : To get the emp contact email based on the person id
1551 -- Parameters :
1552 -- IN : p_person_id IN NUMBER
1553 -- Returnvalue:
1554 -- l_emp_contact_email VARCHAR2(250)
1555 -- End of comments
1556 -- -------------------------------------------------------------------------
1557 FUNCTION get_emp_contact_email
1558 (p_person_id IN NUMBER
1559 ) RETURN VARCHAR2 as
1560
1561 cursor get_emp_email is
1565
1562 SELECT email_address
1563 FROM per_workforce_x
1564 WHERE person_id = p_person_id;
1566 l_emp_contact_email varchar2(250);
1567
1568 begin
1569 l_emp_contact_email := null;
1570 open get_emp_email;
1571 fetch get_emp_email into l_emp_contact_email;
1572 close get_emp_email;
1573
1574 return l_emp_contact_email;
1575 end;
1576
1577 end csz_servicerequest_util_pvt;
[Home] [Help]