[Home] [Help]
PACKAGE BODY: APPS.CS_ASSIGN_RESOURCE_PKG
Source
1 PACKAGE BODY CS_ASSIGN_RESOURCE_PKG as
2 /* $Header: csvasrsb.pls 120.15.12020000.3 2013/03/21 11:55:06 nic ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_ASSIGN_RESOURCE_PKG';
4
5 -- This procedure filters the Usage as Support for
6 -- all the Groups returned from JTF AM API and returns
7 -- only the first Support usage Group.
8 PROCEDURE Get_Sup_Usage_Group(
9 p_assign_resources_tbl IN JTF_ASSIGN_PUB.AssignResources_tbl_type,
10 x_resource_id OUT NOCOPY NUMBER,
11 x_territory_id OUT NOCOPY NUMBER) IS
12
13 CURSOR c_usage_check(p_group_id IN NUMBER) IS
14 SELECT 'Y'
15 FROM jtf_rs_group_usages
16 WHERE group_id = p_group_id
17 AND usage = 'SUPPORT';
18
19 l_sup_usage VARCHAR2(1) := NULL;
20 i NUMBER := 0;
21
22 BEGIN
23 IF p_assign_resources_tbl.COUNT > 0 THEN
24 i := p_assign_resources_tbl.FIRST;
25 WHILE (i <= p_assign_resources_tbl.LAST)
26 LOOP
27 OPEN c_usage_check(p_assign_resources_tbl(i).resource_id);
28 FETCH c_usage_check INTO l_sup_usage;
29 IF (l_sup_usage = 'Y') THEN
30 x_resource_id := p_assign_resources_tbl(i).resource_id;
31 x_territory_id := p_assign_resources_tbl(i).terr_id;
32 EXIT;
33 END IF;
34 i := p_assign_resources_tbl.NEXT(i);
35 END LOOP;
36 END IF;
37
38 EXCEPTION
39 WHEN OTHERS THEN
40 x_resource_id := NULL;
41 x_territory_id := NULL;
42 END;
43
44 -- Assign_ServiceRequest_Main is the Proc which calls Assign_Resources.
45 -- This will in turn call Assign_Group for getting the Group and
46 -- once Success calls the Assign_Owner for assigning the proper
47 -- resources. Added x_owner_group_id for ER# 2616902
48 PROCEDURE Assign_ServiceRequest_Main
49 (p_api_name IN VARCHAR2,
50 p_api_version IN NUMBER,
51 p_init_msg_list IN VARCHAR2,
52 p_commit IN VARCHAR2,
53 p_incident_id IN NUMBER,
54 p_object_version_number IN NUMBER,
55 p_last_updated_by IN VARCHAR2,
56 p_service_request_rec IN CS_ServiceRequest_pvt.service_request_rec_type,
57 x_owner_group_id OUT NOCOPY NUMBER,
58 x_owner_id OUT NOCOPY NUMBER,
59 x_owner_type OUT NOCOPY VARCHAR2,
60 x_territory_id OUT NOCOPY NUMBER,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2
64 ) IS
65
66 -- Define Local Variables
67 l_api_name CONSTANT VARCHAR2(30) := 'Assign_ServiceRequest_Main';
68 l_api_version CONSTANT NUMBER := 1.0;
69 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
70 l_sr_rec CS_ServiceRequest_pvt.service_request_rec_type DEFAULT p_service_request_rec;
71 l_return_status VARCHAR2(1) := null;
72
73 BEGIN
74 -- Initialize API return status to success
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 -- Standard call to check for call compatibility
78 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
79 G_PKG_NAME) THEN
80 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
81 END IF;
82
83 -- Initialize message list if p_init_msg_list is set to TRUE
84 IF FND_API.To_Boolean(p_init_msg_list) THEN
85 FND_MSG_PUB.Initialize;
86 END IF;
87
88 CS_ASSIGN_RESOURCE_PKG.Assign_Resources
89 ( p_init_msg_list => p_init_msg_list,
90 p_commit => p_commit,
91 p_incident_id => p_incident_id,
92 p_object_version_number => p_object_version_number,
93 p_last_updated_by => p_last_updated_by,
94 p_service_request_rec => l_sr_rec,
95 x_owner_group_id => x_owner_group_id,
96 x_owner_type => x_owner_type,
97 x_owner_id => x_owner_id,
98 x_territory_id => x_territory_id,
99 x_return_status => l_return_status,
100 x_msg_count => x_msg_count,
101 x_msg_data => x_msg_data
102 );
103 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
104 RAISE FND_API.G_EXC_ERROR;
105 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END IF;
108
109 EXCEPTION
110 WHEN FND_API.G_EXC_ERROR THEN
111 x_return_status := FND_API.G_RET_STS_ERROR;
112 FND_MSG_PUB.Count_And_Get
113 ( p_count => x_msg_count,
114 p_data => x_msg_data
115 );
116 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
118 FND_MSG_PUB.Count_And_Get
119 ( p_count => x_msg_count,
120 p_data => x_msg_data
121 );
122 WHEN OTHERS THEN
123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
124 FND_MSG_PUB.Count_And_Get
125 ( p_count => x_msg_count,
126 p_data => x_msg_data
127 );
128
129 END Assign_ServiceRequest_Main;
130
131 -- The Proc Assign_Resources calls Assign_Group and Assign_Owner
132 -- Added x_owner_group_id for ER# 2616902.
133 PROCEDURE Assign_Resources
134 ( p_init_msg_list IN VARCHAR2,
135 p_commit IN VARCHAR2,
136 p_incident_id IN NUMBER,
137 p_object_version_number IN NUMBER,
138 p_last_updated_by IN VARCHAR2,
139 p_service_request_rec IN CS_ServiceRequest_pvt.service_request_rec_type,
140 x_owner_group_id OUT NOCOPY NUMBER,
141 x_owner_type OUT NOCOPY VARCHAR2,
142 x_owner_id OUT NOCOPY NUMBER,
143 x_territory_id OUT NOCOPY NUMBER,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2
147 ) IS
148
149 -- Define Local Variables
150 l_api_name CONSTANT VARCHAR2(30) := 'Assign_ServiceRequest_Main';
151 l_api_version CONSTANT NUMBER := 1.0;
152 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
153 l_sr_rec CS_ServiceRequest_pvt.service_request_rec_type DEFAULT p_service_request_rec;
154 l_service_request_rec CS_ServiceRequest_pvt.service_request_rec_type;
155 l_notes_table CS_ServiceRequest_pvt.notes_table;
156 l_contacts_table CS_ServiceRequest_pvt.contacts_table;
157 -- Return Status from Group, Owner and Resources Proc
158 l_grp_return_status VARCHAR2(1) := NULL;
159 l_own_return_status VARCHAR2(1) := NULL;
160 l_main_return_status VARCHAR2(1) ;
161 l_return_status VARCHAR2(1);
162 l_ib_inv_comp_id NUMBER := NULL;
163 l_ib_inv_subcomp_id NUMBER := NULL;
164 l_default_group_type VARCHAR2(30);
165 l_group_id NUMBER;
166 l_owner_id NUMBER;
167 l_territory_id NUMBER;
168 l_resource_type VARCHAR2(30);
169 l_param_resource_type VARCHAR2(30);
170 l_owner VARCHAR2(360);
171 l_group_owner VARCHAR2(60);
172 -- For Messages
173 l_msg_count NUMBER;
174 l_msg_data VARCHAR2(2000);
175 -- For Updating SR
176 l_update_grp_flag VARCHAR2(1) := 'N';
177 l_update_own_flag VARCHAR2(1) := 'N';
178 l_object_version_number NUMBER := p_object_version_number;
179 l_interaction_id NUMBER;
180 l_workflow_process_id NUMBER;
181
182 -- List of Cursors used
183 CURSOR c_inv_comp_id(p_component_id NUMBER) IS
184 SELECT inventory_item_id
185 FROM csi_item_instances
186 WHERE instance_id = p_component_id;
187
188 CURSOR c_inv_subcomp_id(p_subcomponent_id NUMBER) IS
189 SELECT inventory_item_id
190 FROM csi_item_instances
191 WHERE instance_id = p_subcomponent_id;
192
193 BEGIN
194
195 -- Initialize message list if p_init_msg_list is set to TRUE
196 IF FND_API.To_Boolean(p_init_msg_list) THEN
197 FND_MSG_PUB.Initialize;
198 END IF;
199
200 -- Initialize API return status to success
201 x_return_status := FND_API.G_RET_STS_SUCCESS;
202 l_grp_return_status := FND_API.G_RET_STS_SUCCESS;
203 l_main_return_status := FND_API.G_RET_STS_SUCCESS;
204
205 -- Set group_type as RS_GROUP always, if default_group_type profile is null
206 IF (l_sr_rec.group_type IS NULL) THEN
207 FND_PROFILE.Get('CS_SR_DEFAULT_GROUP_TYPE', l_default_group_type);
208 IF (l_default_group_type IS NULL) THEN
209 l_default_group_type := 'RS_GROUP';
210 END IF;
211 ELSE
212 l_default_group_type := l_sr_rec.group_type;
213 END IF;
214
215 IF (l_sr_rec.customer_product_id IS NOT NULL) THEN
216 OPEN c_inv_comp_id(l_sr_rec.cp_component_id);
217 FETCH c_inv_comp_id INTO l_ib_inv_comp_id;
218 CLOSE c_inv_comp_id;
219
220 OPEN c_inv_subcomp_id(l_sr_rec.cp_subcomponent_id);
221 FETCH c_inv_subcomp_id INTO l_ib_inv_subcomp_id;
222 CLOSE c_inv_subcomp_id;
223
224 l_sr_rec.cp_component_id := l_ib_inv_comp_id;
225 l_sr_rec.cp_subcomponent_id := l_ib_inv_subcomp_id;
226 END IF;
227 -- If l_default_group_type is not null then
228 IF (l_sr_rec.owner_group_id IS NULL) THEN
229 l_group_id := NULL;
230 l_update_grp_flag := 'N';
231 CS_ASSIGN_RESOURCE_PKG.Assign_Group
232 ( p_init_msg_list => p_init_msg_list,
233 p_commit => p_commit,
234 p_incident_id => p_incident_id,
235 p_group_type => l_default_group_type,
236 p_service_request_rec => l_sr_rec,
237 x_return_status => l_grp_return_status,
238 x_resource_id => l_group_id,
239 x_territory_id => l_territory_id,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data
242 );
243 IF (l_grp_return_status = FND_API.G_RET_STS_SUCCESS) THEN
244 IF (FND_PROFILE.VALUE('CS_SR_OWNER_AUTO_ASSIGN_LEVEL') = 'GROUP') THEN
245 x_owner_group_id := l_group_id;
246 x_territory_id := l_territory_id;
247 RETURN;
248 ELSE
249 IF (l_group_id IS NOT NULL ) THEN
250 l_update_grp_flag := 'Y';
251 IF (l_default_group_type <> 'RS_TEAM') THEN
252 l_param_resource_type := 'RS_INDIVIDUAL';
253 l_update_own_flag := 'N';
254
255 -- Initialize API return status to success
256 x_return_status := FND_API.G_RET_STS_SUCCESS;
257 l_own_return_status := FND_API.G_RET_STS_SUCCESS;
258
259 CS_ASSIGN_RESOURCE_PKG.Assign_Owner
260 ( p_init_msg_list => p_init_msg_list,
261 p_commit => p_commit,
262 p_incident_id => p_incident_id,
263 p_param_resource_type => l_param_resource_type,
264 p_group_id => l_group_id,
265 p_service_request_rec => l_sr_rec,
266 x_return_status => l_own_return_status,
267 x_resource_id => l_owner_id,
268 x_resource_type => l_resource_type,
269 x_territory_id => l_territory_id,
270 x_msg_count => x_msg_count,
271 x_msg_data => x_msg_data
272 );
273 x_return_status := l_own_return_status ;
274 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
275 IF (l_owner_id IS NULL) THEN
276 FND_MSG_PUB.Initialize;
277 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_OWNER');
278 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
279 FND_MSG_PUB.Add;
280 l_main_return_status := FND_API.G_RET_STS_SUCCESS;
281 ELSE
282 l_update_own_flag := 'Y';
283 END IF;
284 ELSE
285 -- Check for Expected and Unexpected Error
286 -- For Expected Error the message stack is initialized.
287 -- For Unexpected Error only all the messages are shown
288 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
289 FND_MSG_PUB.Initialize; -- Uncommented for Bug 13696291
290 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_OWNER');
291 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
292 FND_MSG_PUB.Add;
293 l_main_return_status := FND_API.G_RET_STS_ERROR;
294 ELSE
295 --FND_MSG_PUB.Initialize;
296 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_OWNER');
297 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
298 FND_MSG_PUB.Add;
299 l_main_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300 END IF;
301 END IF;
302 END IF; /* group_type <> RS_TEAM */
303 ELSE /* l_group_id is not null */
304 -- Print all the error messages for group_id is null
305 FND_MSG_PUB.Initialize; -- Uncommented for Bug 13696291
306 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_GROUP');
307 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
308 FND_MSG_PUB.Add;
309 l_main_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 END IF;
311 END IF; /* CS_SR_OWNER_AUTO_ASSIGN_LEVEL */
312 ELSE /* l_grp_return_status is not success */
313 -- Check for Expected and Unexpected Error
314 -- For Expected Error the message stack is initialized.
315 -- For Unexpected Error only all the messages are shown
316 IF (l_grp_return_status = FND_API.G_RET_STS_ERROR) THEN
317 FND_MSG_PUB.Initialize; -- Uncommented for Bug 13696291
318 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_GROUP');
319 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
320 FND_MSG_PUB.Add;
321 l_main_return_status := FND_API.G_RET_STS_ERROR;
322 ELSE
323 --FND_MSG_PUB.Initialize;
324 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_GROUP');
325 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
326 FND_MSG_PUB.Add;
327 l_main_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
328 END IF;
329 END IF; /* l_grp_return_status is not success */
330
331 ELSE /* owner_group_id is not null, group has been assigned */
332 IF (l_default_group_type <> 'RS_TEAM') THEN
333 l_param_resource_type := 'RS_INDIVIDUAL';
334 l_update_own_flag := 'N';
335 -- Initialize API return status to success
336 x_return_status := FND_API.G_RET_STS_SUCCESS;
337 l_own_return_status := FND_API.G_RET_STS_SUCCESS;
338 l_group_id := p_service_request_rec.owner_group_id ;
339
340 IF (FND_PROFILE.VALUE('CS_SR_OWNER_AUTO_ASSIGN_LEVEL') = 'GROUP') THEN
341 RETURN;
342 ELSE
343 CS_ASSIGN_RESOURCE_PKG.Assign_Owner
344 ( p_init_msg_list => p_init_msg_list,
345 p_commit => p_commit,
346 p_incident_id => p_incident_id,
347 p_param_resource_type => l_param_resource_type,
348 p_group_id => l_group_id,
349 p_service_request_rec => l_sr_rec,
350 x_return_status => l_own_return_status,
351 x_resource_id => l_owner_id,
352 x_resource_type => l_resource_type,
353 x_territory_id => l_territory_id,
354 x_msg_count => x_msg_count,
355 x_msg_data => x_msg_data
356 );
357
358 x_return_status := l_own_return_status ;
359 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
360 IF (l_owner_id IS NULL) THEN
361 FND_MSG_PUB.Initialize;
362 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_OWNER');
363 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
364 FND_MSG_PUB.Add;
365 l_main_return_status := FND_API.G_RET_STS_SUCCESS;
366 ELSE
367 l_update_own_flag := 'Y';
368 END IF;
369 ELSE
370 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
371 FND_MSG_PUB.Initialize; -- Uncommented for Bug 13696291
372 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_OWNER');
373 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
374 FND_MSG_PUB.Add;
375 l_main_return_status := FND_API.G_RET_STS_ERROR;
376 ELSE
377 --FND_MSG_PUB.Initialize;
378 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_OWNER');
379 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
380 FND_MSG_PUB.Add;
381 l_main_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 END IF;
383 END IF;
384 END IF; /* CS_SR_OWNER_AUTO_ASSIGN_LEVEL */
385 END IF; /* group_type <> RS_TEAM */
386 END IF; /* owner_group_id is not null, group has been assigned */
387
388 IF ((l_update_grp_flag = 'Y') OR ( l_update_own_flag = 'Y')) THEN
389 -- The following updates are made because when the CreateSR API is
390 -- called with Auto Assign, then the UpdateSR Business Event will be
391 -- kicked off before the CreateSR Bus.Events which from User POV will
392 -- logically be wrong.
393 BEGIN
394 l_service_request_rec.group_type := l_default_group_type;
395 IF (l_sr_rec.owner_group_id IS NULL) THEN
396 l_service_request_rec.owner_group_id := l_group_id;
397 END IF;
398
399 IF (l_update_own_flag = 'Y') THEN
400 IF (l_sr_rec.owner_id IS NULL) THEN
401 l_service_request_rec.owner_id := l_owner_id;
402 l_service_request_rec.resource_type := l_resource_type;
403 END IF;
404 END IF;
405 END;
406
407 END IF; /* l_update_grp_flag OR l_update_own_flag IS 'Y' */
408
409 -- x_owner_group_id is added for ER# 2616902
410 IF (l_main_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
411 x_owner_id := NULL;
412 x_owner_type := NULL;
413 x_owner_group_id := l_group_id;
414 x_territory_id := l_territory_id;
415 x_return_status := l_main_return_status;
416 ELSE
417 x_owner_id := l_owner_id;
418 x_owner_type := l_resource_type;
419 x_owner_group_id := l_group_id;
420 x_territory_id := l_territory_id;
421 x_return_status := l_main_return_status;
422 END IF;
423
424 EXCEPTION
425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427 FND_MSG_PUB.Count_And_Get
428 ( p_count => x_msg_count,
429 p_data => x_msg_data
430 );
431
432 WHEN OTHERS THEN
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434 FND_MSG_PUB.Count_And_Get
435 ( p_count => x_msg_count,
436 p_data => x_msg_data
437 );
438
439 END Assign_Resources;
440
441 -- This Procedure returns the Group if not passed.
442 PROCEDURE Assign_Group
443 ( p_init_msg_list IN varchar2 := fnd_api.g_false,
444 p_commit IN varchar2 := fnd_api.g_false,
445 p_incident_id IN number,
446 p_group_type IN varchar2,
447 p_service_request_rec IN CS_ServiceRequest_pvt.service_request_rec_type,
448 x_return_status OUT NOCOPY VARCHAR2,
449 x_resource_id OUT NOCOPY NUMBER,
450 x_territory_id OUT NOCOPY NUMBER,
451 x_msg_count OUT NOCOPY NUMBER,
452 x_msg_data OUT NOCOPY VARCHAR2
453 ) IS
454
455 -- Define Local Variables
456 l_category_set_id NUMBER;
457 l_platform_catg_set_id NUMBER;
458 -- Profiles variables
459 l_web_availability_check VARCHAR2(1);
460 n NUMBER;
461 -- Input and output data structures
462 l_Assign_Groups_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type;
463 l_sr_am_rec JTF_ASSIGN_PUB.JTF_Serv_req_rec_type;
464 l_sr_rec CS_ServiceRequest_pvt.service_request_rec_type DEFAULT p_service_request_rec;
465
466 -- Qualifier values
467 l_incident_id NUMBER := p_incident_id;
468 l_contract_service_id NUMBER := p_service_request_rec.contract_service_id;
469 l_inv_item_id NUMBER := NULL;
470 l_inv_org_id NUMBER := NULL;
471 l_inv_category_id NUMBER := NULL;
472 l_no_of_employees NUMBER := NULL;
473 l_party_id NUMBER := p_service_request_rec.customer_id;
474 l_class_code VARCHAR2(30) := NULL;
475 l_cust_category VARCHAR2(30) := NULL;
476 l_country VARCHAR2(60) := NULL;
477 l_province VARCHAR2(60) := NULL;
478 l_postal_code VARCHAR2(60) := NULL;
479 l_city VARCHAR2(60) := NULL;
480 l_state VARCHAR2(60) := NULL;
481 l_area_code VARCHAR2(60) := NULL;
482 l_county VARCHAR2(60) := NULL;
483 l_party_name VARCHAR2(360) := NULL;
484 -- Changed the party_site to Incident Location for the 11.5.9 ER# 2527850
485 l_location_id NUMBER := p_service_request_rec.incident_location_id;
486
487 l_day_week VARCHAR2(10) ;
488 l_time_day VARCHAR2(10) ;
489
490 --parameters
491 l_am_calling_doc_type VARCHAR2(2) := 'SR';
492 l_am_calling_doc_id NUMBER := NULL;
493 l_resource_type VARCHAR2(30) := p_group_type;
494 l_web_availability_flag VARCHAR2(1) := NULL;
495 l_no_of_resources NUMBER := NULL;
496 l_cust_prod_id NUMBER := p_service_request_rec.customer_product_id;
497 l_contract_res_flag VARCHAR2(3);
498 l_ib_resource_flag VARCHAR2(3);
499 l_business_process_id NUMBER;
500 l_system_id NUMBER ;
501 l_customer_product_id NUMBER ;
502
503 l_cs_sr_chk_res_cal_avl VARCHAR2(1) ; --gasankar Calendar check feature added
504
505 l_start_date Date ;
506 l_end_date Date ;
507 l_territory_flag VARCHAR2(3); -- added by nic for forward port bug 15890801
508
509
510 c_customer_phone_id NUMBER := p_service_request_rec.customer_phone_id;
511 -- List of Cursors used
512 CURSOR c_inc_address(p_incident_location_id NUMBER) IS
513 SELECT country,province,state,city,postal_code,county
514 FROM hz_locations
515 WHERE location_id = p_incident_location_id;
516
517 CURSOR c_inc_party_site_address(p_party_site_id NUMBER) IS
518 SELECT location_id FROM hz_party_sites
519 WHERE party_site_id = p_party_site_id;
520
521 CURSOR C_CONTRACT(l_contract_service_id number) IS
522 SELECT to_number(object1_id1), to_number(object1_id2)
523 FROM okc_k_items
524 WHERE cle_id = l_contract_service_id;
525
526 /* Waiting for JTA patch for their sql change so comment out for now
527 -- VIP Customer Code
528 CURSOR C_CLASS_CODE(l_party_id number,l_cust_category varchar2) is
529 SELECT class_code
530 FROM hz_code_assignments
531 WHERE owner_table_name = 'HZ_PARTIES'
532 AND owner_table_id = l_party_id
533 AND class_category = l_cust_category;
534 */
535 --Bug 5255184 Modified the c_area_code query
536 CURSOR c_area_code IS
537 SELECT hzp.phone_area_code
538 FROM hz_contact_points hzp
539 WHERE hzp.contact_point_id = c_customer_phone_id;
540
541 CURSOR c_cust_det(p_customer_id NUMBER) IS
542 SELECT employees_total, party_name
543 FROM hz_parties
544 WHERE party_id = p_customer_id;
545
546
547
548 BEGIN
549 -- Initialize API return status to success
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551
552 -- Assign the incident_id to a local variable
553 l_incident_id := p_incident_id;
554 -- Proceed only if incident_id is not null
555 -- Group type must have a value - default is RS_GROUP, passed by caller
556 --IF (l_incident_id IS NOT NULL) AND
557 IF (p_group_type IS NOT NULL) THEN
558 l_resource_type := p_group_type;
559
560 l_incident_id := p_incident_id;
561 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_AUTO_ASSIGN') = 'YES') THEN
562 SELECT business_process_id INTO l_business_process_id
563 FROM cs_incident_types
564 WHERE incident_type_id = l_sr_rec.type_id;
565 END IF;
566
567 -- 12.1.2 Enhancement
568 Begin
569 SELECT to_char(sysdate, 'd'), to_char(sysdate, 'hh24:mi')
570 INTO l_day_week, l_time_day
571 FROM cs_incidents_all_b
572 WHERE incident_id = l_incident_id ;
573 Exception
574 When Others then
575 l_time_day := null ;
576 l_day_week := null ;
577 End ;
578
579 l_sr_am_rec.DAY_OF_WEEK := l_day_week ;
580 l_sr_am_rec.TIME_OF_DAY := l_time_day ;
581
582 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
583 -- Assigning component and subcomponent id directly to the am rec
584
585 IF (l_sr_rec.customer_product_id IS NOT NULL) THEN
586 l_sr_am_rec.item_component := l_sr_rec.cp_component_id;
587 l_sr_am_rec.item_subcomponent := l_sr_rec.cp_subcomponent_id;
588 ELSE
589 l_sr_am_rec.item_component := l_sr_rec.inv_component_id;
590 l_sr_am_rec.item_subcomponent := l_sr_rec.inv_subcomponent_id;
591 END IF;
592
593 IF (p_service_request_rec.incident_location_id IS NOT NULL) THEN
594 IF (p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE') THEN
595 OPEN c_inc_party_site_address(p_service_request_rec.incident_location_id);
596 FETCH c_inc_party_site_address INTO l_location_id;
597 IF (c_inc_party_site_address%NOTFOUND) THEN
598 l_location_id := NULL;
599 END IF;
600 CLOSE c_inc_party_site_address;
601 END IF;
602 OPEN c_inc_address(l_location_id);
603 FETCH c_inc_address INTO l_country, l_province, l_state, l_city,
604 l_postal_code, l_county;
605 IF (c_inc_address%NOTFOUND) THEN
606 NULL;
607 END IF;
608 l_sr_am_rec.country := l_country;
609 l_sr_am_rec.city := l_city;
610 l_sr_am_rec.postal_code := l_postal_code;
611 l_sr_am_rec.state := l_state;
612 l_sr_am_rec.province := l_province;
613 l_sr_am_rec.county := l_county;
614 CLOSE c_inc_address;
615 ELSE
616 l_sr_am_rec.country := p_service_request_rec.incident_country;
617 l_sr_am_rec.city := p_service_request_rec.incident_city;
618 l_sr_am_rec.postal_code := p_service_request_rec.incident_postal_code;
619 l_sr_am_rec.state := p_service_request_rec.incident_state;
620 l_sr_am_rec.province := p_service_request_rec.incident_province;
621 l_sr_am_rec.county := p_service_request_rec.incident_county;
622 END IF;
623 --Bug 5255184 Modified the c_area_code
624 OPEN c_area_code;
625 FETCH c_area_code INTO l_area_code;
626 IF (c_area_code%NOTFOUND) THEN
627 l_area_code := NULL;
628 END IF;
629 CLOSE c_area_code;
630
631 OPEN c_cust_det(l_sr_rec.customer_id);
632 FETCH c_cust_det INTO l_no_of_employees, l_party_name;
633 IF (c_cust_det%NOTFOUND) THEN
634 l_no_of_employees := NULL;
635 l_party_name := NULL;
636 END IF;
637 CLOSE c_cust_det;
638
639 -- Assign the values to the AM Record Type
640 l_sr_am_rec.service_request_id := l_incident_id;
641 l_sr_am_rec.party_id := l_sr_rec.customer_id;
642 l_sr_am_rec.incident_type_id := l_sr_rec.type_id;
643 l_sr_am_rec.incident_severity_id := l_sr_rec.severity_id;
644 l_sr_am_rec.incident_urgency_id := l_sr_rec.urgency_id;
645 l_sr_am_rec.problem_code := l_sr_rec.problem_code;
646 l_sr_am_rec.incident_status_id := l_sr_rec.status_id;
647 l_sr_am_rec.platform_id := l_sr_rec.platform_id;
648 l_sr_am_rec.sr_creation_channel := l_sr_rec.sr_creation_channel;
649 l_sr_am_rec.inventory_item_id := l_sr_rec.inventory_item_id;
650 l_sr_am_rec.area_code := l_area_code;
651 l_sr_am_rec.squal_char12 := l_sr_rec.problem_code;
652 l_sr_am_rec.squal_char13 := l_sr_rec.comm_pref_code;
653 l_sr_am_rec.squal_num12 := l_sr_rec.platform_id;
654 l_sr_am_rec.squal_num13 := l_sr_rec.inv_platform_org_id;
655 l_sr_am_rec.squal_num14 := l_sr_rec.category_id;
656 l_sr_am_rec.squal_num15 := l_sr_rec.inventory_item_id;
657 l_sr_am_rec.squal_num16 := l_sr_rec.inventory_org_id;
658 l_sr_am_rec.squal_num17 := NULL;
659 l_sr_am_rec.squal_num30 := l_sr_rec.language_id;
660 l_sr_am_rec.squal_char20 := l_sr_rec.cust_pref_lang_code;
661 l_sr_am_rec.squal_char21 := l_sr_rec.coverage_type;
662 l_sr_am_rec.num_of_employees := l_no_of_employees;
663 l_sr_am_rec.comp_name_range := l_party_name;
664
665 --gasankar sun
666 If p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE' Then
667 l_sr_am_rec.customer_site_id := p_service_request_rec.incident_location_id ;
668 l_sr_am_rec.party_site_id := p_service_request_rec.site_number ; -- added by nic for forward port bug 15890801
669 Else
670 l_sr_am_rec.customer_site_id := null ;
671 l_sr_am_rec.party_site_id := null;
672 End If ;
673
674 l_customer_product_id := p_service_request_rec.customer_product_id ;
675 l_system_id := p_service_request_rec.system_id ;
676 If l_system_id is Null then
677
678 Begin
679 Select system_id into l_system_id
680 from csi_item_instances
681 Where instance_id = l_customer_product_id;
682 Exception
683 When Others Then
684 l_system_id := Null ;
685 End ;
686 End If ;
687 l_sr_am_rec.SQUAL_NUM60 := l_system_id ;
688
689 --gasankar sun
690
691
692 -- Commented below for implementation will be done only from 11.5.10
693 /*l_sr_am_rec.party_site_id := l_sr_rec.customer_site_id;
694 l_sr_am_rec.customer_site_id := l_sr_rec.customer_site_id;
695 l_sr_am_rec.support_site_id := l_sr_rec.site_id;*/
696
697 -- Contract Item and Org dtls
698 IF (l_contract_service_id IS NOT NULL) THEN
699 OPEN c_contract(l_contract_service_id);
700 FETCH c_contract INTO l_inv_item_id,l_inv_org_id;
701 IF (c_contract%NOTFOUND) THEN
702 NULL;
703 END IF;
704 CLOSE c_contract;
705 END IF;
706 -- Assign the values to the qualifiers
707 l_sr_am_rec.squal_num18 := l_inv_item_id;
708 l_sr_am_rec.squal_num19 := l_inv_org_id;
709
710 /* Waiting for JTA patch for their sql change so comment out for now
711 -- VIP Customer Code
712 IF (l_party_id IS NOT NULL) THEN
713 OPEN c_class_code(l_party_id,l_cust_category);
714 FETCH c_class_code INTO l_class_code;
715 IF (c_class_code%NOTFOUND) THEN
716 NULL;
717 END IF;
718 CLOSE c_class_code;
719 END IF;
720 l_sr_am_rec.squal_char11 := l_class_code;
721 */
722
723 -- Populate the form parameters
724 l_am_calling_doc_id := l_incident_id;
725
726 -- Passing the auto_select_flag as 'N' bcoz if it is null the JTF API
727 -- assigns it as 'Y' and always returns the first record. No Load Balancing
728 -- is done.
729 -- If customer product id is not null, then set ib_preferred_resource_flag
730 -- to 'Y'.If contract line id is not null, then set
731 -- contract_preferred_resource flag to 'Y'.
732
733 l_cust_prod_id := l_sr_rec.customer_product_id;
734 IF (l_contract_service_id IS NOT NULL) THEN
735 IF (FND_PROFILE.VALUE('CS_SR_CONTRACT_GROUP')= 'Y') THEN --Added By Nic
736 l_contract_res_flag := 'Y';
737 ELSE
738 l_contract_res_flag := 'N';
739 END IF;
740 END IF;
741 IF (l_cust_prod_id IS NOT NULL) THEN
742 l_ib_resource_flag := 'Y';
743 ELSE
744 l_ib_resource_flag := 'N';
745 END IF;
746 IF (FND_PROFILE.VALUE('CS_SR_TERRITORY_GROUP') = 'Y') THEN -- Added by nic
747 l_territory_flag :='Y';
748 ELSE
749 l_territory_flag :='N';
750 END IF;
751
752 FND_PROFILE.Get('CS_SR_CHK_RES_CAL_AVL', l_cs_sr_chk_res_cal_avl); --gasankar Calendar check feature added
753
754 If nvl(l_cs_sr_chk_res_cal_avl, 'N') <> 'N' Then
755 l_start_date := sysdate ;
756 l_end_date := sysdate ;
757 End If ;
758
759 JTF_ASSIGN_PUB.GET_Assign_Resources
760 ( p_api_version => 1.0,
761 p_init_msg_list => FND_API.G_FALSE,
762 p_commit => 'F',
763 p_resource_id => NULL,
764 p_resource_type => l_resource_type,
765 p_role => NULL,
766 p_no_of_resources => l_no_of_resources,
767 p_auto_select_flag => 'N',
768 p_contracts_preferred_engineer => nvl(l_contract_res_flag,'N'),
769 p_ib_preferred_engineer => nvl(l_ib_resource_flag,'N'),
770 p_contract_id => l_contract_service_id,
771 p_customer_product_id => l_cust_prod_id,
772 p_effort_duration => NULL,
773 p_effort_uom => NULL,
774 p_start_date => l_start_date,
775 p_end_date => l_end_date,
776 p_territory_flag => nvl(l_territory_flag,'N'),
777 p_calendar_flag => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
778 p_calendar_check => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
779 p_web_availability_flag => 'Y',
780 p_filter_excluded_resource => 'Y',
781 p_category_id => NULL,
782 p_inventory_item_id => NULL,
783 p_inventory_org_id => NULL,
784 p_column_list => NULL,
785 p_calling_doc_id => NULL,
786 p_calling_doc_type => 'SR',
787 p_sr_rec => l_sr_am_rec,
788 p_sr_task_rec => NULL,
789 p_defect_rec => NULL,
790 p_business_process_id => l_business_process_id,
791 p_business_process_date => l_sr_rec.request_date,
792 x_Assign_Resources_tbl => l_Assign_Groups_tbl,
793 x_return_status => x_return_status,
794 x_msg_count => x_msg_count,
795 x_msg_data => x_msg_data
796 );
797
798 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
799 l_Assign_Groups_tbl.COUNT > 0) THEN
800 Get_Sup_Usage_Group(l_Assign_Groups_tbl, x_resource_id, x_territory_id);
801 END IF;
802
803 END IF; /* l_incident_id and p_group_type is not null */
804 END Assign_Group;
805
806 /*==========================================================================================+
807 ==
808 == Procedure name : Assign_Owner
809 == Parameters :
810 == IN : event VARCHAR2
811 == OUT : none.
812 ==
813 == Description : This Procedure returns the individual Owner from the Group
814 == returned by the Assign_Group Procedure.
815 == Modification History:
816 ==
817 == Date Name Desc
818 == ---------- --------- ---------------------------------------------
819 == 08/02/2007 GASANKAR Bug 6241796 Fixed
820 == Initializing p_res_load_table(l_tbl_index).resource_type ,
821 == resource_id if the resource is not belonging to a group.
822 == 07/09/2007 GASANKAR Bug 639126 Fixed
823 == First record of the p_res_load_table is not been left blank, so
824 == that contract preferred resource will work properly.
825 ===========================================================================================*/
826
827 PROCEDURE Assign_Owner
828 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
829 p_commit IN VARCHAR2 := FND_API.G_FALSE,
830 p_incident_id IN NUMBER,
831 p_param_resource_type IN VARCHAR2,
832 p_group_id IN NUMBER,
833 p_service_request_rec IN CS_ServiceRequest_pvt.service_request_rec_type,
834 x_return_status OUT NOCOPY VARCHAR2,
835 x_resource_id OUT NOCOPY NUMBER,
836 x_resource_type OUT NOCOPY VARCHAR2,
837 x_territory_id OUT NOCOPY NUMBER,
838 x_msg_count OUT NOCOPY NUMBER,
839 x_msg_data OUT NOCOPY VARCHAR2
840 ) IS
841
842 -- Profiles variables
843 l_web_availability_check VARCHAR2(1);
844 l_category_set_id NUMBER;
845 l_platform_catg_set_id NUMBER;
846 -- Input and output data structures
847 l_Assign_Owner_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type ;
848 l_sr_am_rec JTF_ASSIGN_PUB.JTF_Serv_req_rec_type;
849 l_resource_load_tbl CS_ASSIGN_RESOURCE_PKG.LoadBalance_tbl_type;
850 l_sr_rec CS_ServiceRequest_pvt.service_request_rec_type DEFAULT p_service_request_rec;
851 l_index BINARY_INTEGER;
852 l_count NUMBER;
853 p NUMBER;
854 l NUMBER;
855 l_cal_load_return_sts VARCHAR2(1) := NULL;
856 -- Qualifier values
857 l_incident_id NUMBER := p_incident_id;
858 l_contract_service_id NUMBER := p_service_request_rec.contract_service_id;
859 l_cust_prod_id NUMBER := p_service_request_rec.customer_product_id;
860 l_inv_item_id NUMBER := NULL;
861 l_inv_org_id NUMBER := NULL;
862 l_inv_category_id NUMBER := NULL;
863 l_ib_inv_comp_id NUMBER := NULL;
864 l_ib_inv_subcomp_id NUMBER := NULL;
865 l_group_id NUMBER := p_group_id;
866 l_party_id NUMBER := p_service_request_rec.customer_id;
867 l_location_id NUMBER := p_service_request_rec.incident_location_id;
868 l_class_code VARCHAR2(30) := NULL;
869 l_cust_category VARCHAR2(30) := NULL;
870 l_country VARCHAR2(60) := NULL;
871 l_province VARCHAR2(60) := NULL;
872 l_postal_code VARCHAR2(60) := NULL;
873 l_city VARCHAR2(60) := NULL;
874 l_state VARCHAR2(60) := NULL;
875 l_county VARCHAR2(60) := NULL;
876 l_party_name VARCHAR2(360) := NULL;
877 -- Passing parameters
878 l_ismember VARCHAR2(1) := 'N';
879 l_am_calling_doc_type VARCHAR2(2) := 'SR';
880 l_param_resource_type VARCHAR2(30) := p_param_resource_type;
881 l_web_availability_flag VARCHAR2(1) := NULL;
882 l_am_calling_doc_id NUMBER := NULL;
883 l_no_of_resources NUMBER := NULL;
884 l_no_of_employees NUMBER := NULL;
885 l_product_skill_level NUMBER;
886 l_counter NUMBER;
887 l_cat_wt NUMBER;
888 l_prod_wt NUMBER;
889 l_prob_wt NUMBER;
890 l_business_process_id NUMBER;
891 l_area_code VARCHAR2(50);
892 l_contract_res_flag VARCHAR2(3);
893 l_ib_resource_flag VARCHAR2(3);
894 l_prod_skill_check VARCHAR2(3);
895 l_day_week VARCHAR2(10) ;
896 l_time_day VARCHAR2(10) ;
897 l_system_id NUMBER ;
898 l_customer_product_id NUMBER ;
899
900 --Bug 7168029
901 l_resource_set VARCHAR2(1):='N';
902 l_load_balance VARCHAR2(1):='Y';
903 l_incident_type_id NUMBER;
904 l_incident_severity_id NUMBER;
905 l_wt_prd_skill NUMBER;
906 l_wt_plt_skill NUMBER;
907 l_wt_pbm_skill NUMBER;
908 l_wt_cat_skill NUMBER;
909 l_wt_time_last_login NUMBER;
910 l_wt_backlog_sev1 NUMBER;
911 l_wt_backlog_sev2 NUMBER;
912 l_wt_backlog_sev3 NUMBER;
913 l_wt_backlog_sev4 NUMBER;
914 l_wt_time_zone_lag NUMBER;
915 l_territory_flag VARCHAR2(3); -- Added By nic
916
917
918 CURSOR c_load_wt(l_incident_type_id NUMBER,l_incident_severity_id NUMBER) IS
919 SELECT product_skill_wt,platform_skill_wt,prob_code_skill_wt,category_skill_wt,
920 last_login_time_wt,severity1_count_wt,severity2_count_wt,
921 severity3_count_wt,severity4_count_wt,time_zone_diff_wt
922 FROM cs_sr_load_balance_wt
923 WHERE incident_type_id = l_incident_type_id
924 AND incident_severity_id = l_incident_severity_id;
925
926
927 l_cs_sr_chk_res_cal_avl VARCHAR2(1) ; --gasankar Calendar check feature added
928
929 l_start_date Date ;
930 l_end_date Date ;
931
932 c_customer_phone_id NUMBER := p_service_request_rec.customer_phone_id;
933
934 CURSOR c_inc_address(p_incident_location_id NUMBER) IS
935 SELECT country,province,state,city,postal_code,county
936 FROM hz_locations
937 WHERE location_id = p_incident_location_id;
938
939 CURSOR c_inc_party_site_address(p_party_site_id NUMBER) IS
940 SELECT location_id FROM hz_party_sites
941 WHERE party_site_id = p_party_site_id;
942
943 CURSOR c_contract(l_contract_service_id NUMBER)IS
944 SELECT TO_NUMBER(object1_id1), TO_NUMBER(object1_id2)
945 FROM okc_k_items
946 WHERE cle_id = l_contract_service_id;
947
948 /* Waiting for JTA patch for their sql change so comment out for now
949 -- VIP Customer Code
950 CURSOR c_class_code(l_party_id NUMBER,l_cust_category VARCHAR2) IS
951 SELECT class_code
952 FROM hz_code_assignments
953 WHERE owner_table_name = 'HZ_PARTIES'
954 AND owner_table_id = l_party_id
955 AND class_category = l_cust_category;
956 */
957 --Bug 5255184 Modified the c_area_code query
958 CURSOR c_area_code IS
959 SELECT hzp.phone_area_code
960 FROM hz_contact_points hzp
961 WHERE hzp.contact_point_id = c_customer_phone_id;
962
963 CURSOR c_check_grp_res(p_group_id NUMBER, p_resource_id NUMBER) IS
964 SELECT 'Y'
965 FROM jtf_rs_group_members
966 WHERE group_id = p_group_id
967 AND resource_id = p_resource_id
968 AND NVL(delete_flag, 'N') <> 'Y';
969
970 CURSOR c_cust_det(p_customer_id NUMBER) IS
971 SELECT employees_total, party_name
972 FROM hz_parties
973 WHERE party_id = p_customer_id;
974
975 BEGIN
976
977 -- Initialize API return status to success
978 x_return_status := FND_API.G_RET_STS_SUCCESS;
979 -- Assign the incident_id to a local variable
980 l_incident_id := p_incident_id;
981
982 -- Proceed only if incident_id is not null
983 -- Group type must have a value
984 --IF ((l_incident_id IS NOT NULL) AND (p_group_id IS NOT NULL )) THEN
985 IF p_group_id IS NOT NULL THEN
986 l_group_id := p_group_id ;
987
988 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_AUTO_ASSIGN') = 'YES') THEN
989 SELECT business_process_id INTO l_business_process_id
990 FROM cs_incident_types
991 WHERE incident_type_id = l_sr_rec.type_id;
992 END IF;
993
994 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
995 -- Assigning component and subcomponent id directly to the am rec
996
997 IF (l_sr_rec.customer_product_id IS NOT NULL) THEN
998 l_sr_am_rec.item_component := l_sr_rec.cp_component_id;
999 l_sr_am_rec.item_subcomponent := l_sr_rec.cp_subcomponent_id;
1000 ELSE
1001 l_sr_am_rec.item_component := l_sr_rec.inv_component_id;
1002 l_sr_am_rec.item_subcomponent := l_sr_rec.inv_subcomponent_id;
1003 END IF;
1004
1005
1006 IF (p_service_request_rec.incident_location_id IS NOT NULL) THEN
1007 IF (p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE') THEN
1008 OPEN c_inc_party_site_address(p_service_request_rec.incident_location_id);
1009 FETCH c_inc_party_site_address INTO l_location_id;
1010 IF (c_inc_party_site_address%NOTFOUND) THEN
1011 l_location_id := NULL;
1012 END IF;
1013 CLOSE c_inc_party_site_address;
1014 END IF;
1015 OPEN c_inc_address(l_location_id);
1016 FETCH c_inc_address INTO l_country,l_province,l_state,l_city,
1017 l_postal_code, l_county;
1018 IF (c_inc_address%NOTFOUND) THEN
1019 NULL;
1020 END IF;
1021 l_sr_am_rec.country := l_country;
1022 l_sr_am_rec.city := l_city;
1023 l_sr_am_rec.postal_code := l_postal_code;
1024 l_sr_am_rec.state := l_state;
1025 l_sr_am_rec.province := l_province;
1026 l_sr_am_rec.county := l_county;
1027 CLOSE c_inc_address;
1028 ELSE
1029 l_sr_am_rec.country := p_service_request_rec.incident_country;
1030 l_sr_am_rec.city := p_service_request_rec.incident_city;
1031 l_sr_am_rec.postal_code := p_service_request_rec.incident_postal_code;
1032 l_sr_am_rec.state := p_service_request_rec.incident_state;
1033 l_sr_am_rec.province := p_service_request_rec.incident_province;
1034 l_sr_am_rec.county := p_service_request_rec.incident_county;
1035 END IF;
1036 --Bug 5255184 Modified the c_area_code
1037 OPEN c_area_code;
1038 FETCH c_area_code INTO l_area_code;
1039 IF (c_area_code%NOTFOUND) THEN
1040 l_area_code := NULL;
1041 END IF;
1042 CLOSE c_area_code;
1043
1044 OPEN c_cust_det(l_sr_rec.customer_id);
1045 FETCH c_cust_det INTO l_no_of_employees, l_party_name;
1046 IF (c_cust_det%NOTFOUND) THEN
1047 l_no_of_employees := NULL;
1048 l_party_name := NULL;
1049 END IF;
1050 CLOSE c_cust_det;
1051
1052 -- 12.1.2 Enhancement
1053 Begin
1054 SELECT to_char(sysdate, 'd'), to_char(sysdate, 'hh24:mi')
1055 INTO l_day_week, l_time_day
1056 FROM cs_incidents_all_b
1057 WHERE incident_id = l_incident_id ;
1058 Exception
1059 When Others then
1060 l_time_day := null ;
1061 l_day_week := null ;
1062 End ;
1063
1064 l_sr_am_rec.DAY_OF_WEEK := l_day_week ;
1065 l_sr_am_rec.TIME_OF_DAY := l_time_day ;
1066
1067
1068 l_sr_am_rec.service_request_id := l_incident_id;
1069 l_sr_am_rec.party_id := l_sr_rec.customer_id;
1070 l_sr_am_rec.incident_type_id := l_sr_rec.type_id;
1071 l_sr_am_rec.incident_severity_id := l_sr_rec.severity_id;
1072 l_sr_am_rec.incident_urgency_id := l_sr_rec.urgency_id;
1073 l_sr_am_rec.problem_code := l_sr_rec.problem_code;
1074 l_sr_am_rec.incident_status_id := l_sr_rec.status_id;
1075 l_sr_am_rec.platform_id := l_sr_rec.platform_id;
1076 l_sr_am_rec.sr_creation_channel := l_sr_rec.sr_creation_channel;
1077 l_sr_am_rec.inventory_item_id := l_sr_rec.inventory_item_id;
1078 l_sr_am_rec.area_code := l_area_code;
1079 l_sr_am_rec.squal_char12 := l_sr_rec.problem_code;
1080 l_sr_am_rec.squal_char13 := l_sr_rec.comm_pref_code;
1081 l_sr_am_rec.squal_char20 := l_sr_rec.cust_pref_lang_code ;
1082 l_sr_am_rec.squal_char21 := l_sr_rec.coverage_type;
1083 l_sr_am_rec.squal_num12 := l_sr_rec.platform_id;
1084 l_sr_am_rec.squal_num13 := l_sr_rec.inv_platform_org_id;
1085 l_sr_am_rec.squal_num14 := l_sr_rec.category_id;
1086 l_sr_am_rec.squal_num15 := l_sr_rec.inventory_item_id;
1087 l_sr_am_rec.squal_num16 := l_sr_rec.inventory_org_id;
1088 l_sr_am_rec.squal_num17 := l_group_id;
1089 l_sr_am_rec.squal_num30 := l_sr_rec.language_id;
1090 l_sr_am_rec.num_of_employees := l_no_of_employees;
1091 l_sr_am_rec.comp_name_range := l_party_name;
1092
1093
1094 --gasankar sun
1095 If p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE' Then
1096 l_sr_am_rec.customer_site_id := p_service_request_rec.incident_location_id ;
1097 l_sr_am_rec.party_site_id := p_service_request_rec.site_number ; -- Added By nic
1098 Else
1099 l_sr_am_rec.customer_site_id := null ;
1100 l_sr_am_rec.party_site_id := null ; --Added by nic
1101 End If ;
1102
1103 l_customer_product_id := p_service_request_rec.customer_product_id ;
1104 l_system_id := p_service_request_rec.system_id ;
1105 If l_system_id is Null then
1106
1107 Begin
1108 Select system_id into l_system_id
1109 from csi_item_instances
1110 Where instance_id = l_customer_product_id;
1111 Exception
1112 When Others Then
1113 l_system_id := Null ;
1114 End ;
1115 End If ;
1116 l_sr_am_rec.SQUAL_NUM60 := l_system_id ;
1117
1118 --gasankar sun
1119
1120 --Contract Item and Org dtls
1121 IF (l_contract_service_id IS NOT NULL) THEN
1122 OPEN c_contract(l_contract_service_id);
1123 FETCH c_contract INTO l_inv_item_id,l_inv_org_id;
1124 IF c_contract%NOTFOUND THEN
1125 NULL;
1126 END IF;
1127 CLOSE c_contract;
1128 END IF;
1129 -- Assign it to the AM record type For contracts
1130 l_sr_am_rec.squal_num18 := l_inv_item_id;
1131 l_sr_am_rec.squal_num19 := l_inv_org_id;
1132 l_sr_am_rec.squal_char11 := null;
1133
1134 -- populate the AM parameters
1135 l_am_calling_doc_id := l_incident_id;
1136 l_param_resource_type := p_param_resource_type;
1137
1138 -- If customer product id is not null, then set ib_preferred_resource_flag
1139 -- to 'Y'.If contract line id is not null, then set
1140 -- contract_preferred_resource flag to 'Y'.
1141 l_cust_prod_id := l_sr_rec.customer_product_id;
1142 IF (l_contract_service_id IS NOT NULL) THEN
1143 IF (FND_PROFILE.VALUE('CS_SR_CONTRACT_INDIVIDUAL')= 'Y') THEN --Added By Nic
1144 l_contract_res_flag := 'Y';
1145 ELSE
1146 l_contract_res_flag := 'N';
1147 END IF;
1148 END IF;
1149 IF (l_cust_prod_id IS NOT NULL) THEN
1150 IF (FND_PROFILE.VALUE ('CS_SR_IB_INDIVIDUAL') ='Y' ) THEN -- Added by Nic
1151 l_ib_resource_flag := 'Y';
1152 ELSE
1153 l_ib_resource_flag := 'N';
1154 END IF;
1155 END IF;
1156 IF (FND_PROFILE.VALUE('CS_SR_TERRITORY_INDIVIDUAL') = 'Y') THEN -- Added by nic
1157 l_territory_flag :='Y';
1158 ELSE
1159 l_territory_flag :='N';
1160 END IF;
1161
1162 FND_PROFILE.Get('CS_SR_CHK_RES_CAL_AVL', l_cs_sr_chk_res_cal_avl); --gasankar Calendar check feature added
1163
1164 If nvl(l_cs_sr_chk_res_cal_avl, 'N') <> 'N' Then
1165 l_start_date := sysdate ;
1166 l_end_date := sysdate ;
1167 End If ;
1168
1169 l_param_resource_type := 'RS_INDIVIDUAL';
1170 -- Passing the auto_select_flag as 'N' bcoz if it is null the JTF API
1171 -- assigns it as 'Y' and always returns the first record. No Load Balancing
1172 -- is done. Made contracts_preferred_engineer as 'Y' for 11.5.9 according
1173 -- to whether contract_service_id is not null.
1174 -- From 11.5.9+, the contract_id, inventory_item_id and inventory_org_id
1175 -- are always passed as Null and the Load Balancing will be done for all
1176 -- the resources with or without skills.
1177 JTF_ASSIGN_PUB.Get_Assign_Resources
1178 ( p_api_version => 1.0,
1179 p_init_msg_list => FND_API.G_FALSE,
1180 p_commit => 'F',
1181 p_resource_id => l_group_id,
1182 p_resource_type => l_param_resource_type,
1183 p_role => NULL,
1184 p_no_of_resources => l_no_of_resources,
1185 p_auto_select_flag => 'N',
1186 p_ib_preferred_engineer => nvl(l_ib_resource_flag,'N'),
1187 p_contracts_preferred_engineer => nvl(l_contract_res_flag,'N'),
1188 p_contract_id => l_contract_service_id,
1189 p_customer_product_id => l_cust_prod_id,
1190 p_effort_duration => NULL,
1191 p_effort_uom => NULL,
1192 p_start_date => l_start_date,
1193 p_end_date => l_end_date,
1194 p_territory_flag => nvl(l_territory_flag,'N'),
1195 p_calendar_flag => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
1196 p_calendar_check => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
1197 p_web_availability_flag => 'Y',
1198 p_filter_excluded_resource => 'Y',
1199 p_category_id => NULL,
1200 p_inventory_item_id => NULL,
1201 p_inventory_org_id => NULL,
1202 p_column_list => NULL,
1203 p_calling_doc_id => l_am_calling_doc_id,
1204 p_calling_doc_type => l_am_calling_doc_type,
1205 p_sr_rec => l_sr_am_rec,
1206 p_sr_task_rec => NULL,
1207 p_defect_rec => NULL,
1208 p_business_process_id => l_business_process_id,
1209 p_business_process_date => l_sr_rec.request_date,
1210 x_Assign_Resources_tbl => l_Assign_Owner_tbl,
1211 x_return_status => x_return_status,
1212 x_msg_count => x_msg_count,
1213 x_msg_data => x_msg_data
1214 );
1215
1216 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1217 p := l_Assign_Owner_tbl.FIRST ;
1218 IF (l_Assign_Owner_tbl.COUNT = 1 AND
1219 l_Assign_Owner_tbl(p).web_availability_flag = 'Y') THEN
1220 OPEN c_check_grp_res(l_group_id, l_Assign_Owner_tbl(p).resource_id);
1221 FETCH c_check_grp_res INTO l_ismember;
1222 CLOSE c_check_grp_res;
1223 IF (NVL(l_ismember, 'N') = 'Y') THEN
1224 x_resource_id := l_Assign_Owner_tbl(p).resource_id ;
1225 x_resource_type := l_Assign_Owner_tbl(p).resource_type ;
1226 x_territory_id := l_Assign_Owner_tbl(p).terr_id;
1227 END IF;
1228 END IF;
1229
1230 --Bug 7168029
1231
1232 l_incident_type_id := p_service_request_rec.type_id;
1233 l_incident_severity_id := p_service_request_rec.severity_id;
1234
1235 OPEN c_load_wt(l_incident_type_id,l_incident_severity_id);
1236 FETCH c_load_wt INTO
1237 l_wt_prd_skill, l_wt_plt_skill, l_wt_pbm_skill, l_wt_cat_skill,
1238 l_wt_time_last_login, l_wt_backlog_sev1, l_wt_backlog_sev2 ,
1239 l_wt_backlog_sev3, l_wt_backlog_sev4, l_wt_time_zone_lag;
1240 IF (c_load_wt%NOTFOUND) THEN
1241 l_load_balance:='N';
1242 Elsif (c_load_wt%FOUND) THEN
1243 If ( l_wt_prd_skill = 0 and
1244 l_wt_plt_skill = 0 and
1245 l_wt_pbm_skill = 0 and
1246 l_wt_cat_skill = 0 and
1247 l_wt_time_last_login = 0 and
1248 l_wt_backlog_sev1 = 0 and
1249 l_wt_backlog_sev2 = 0 and
1250 l_wt_backlog_sev3 = 0 and
1251 l_wt_backlog_sev4 = 0 and
1252 l_wt_time_zone_lag = 0 ) then
1253 l_load_balance:='N';
1254 End If;
1255 --End If;
1256 END IF;
1257 CLOSE c_load_wt;
1258
1259 IF (l_Assign_Owner_tbl.COUNT > 1) THEN
1260 l_count := l_Assign_Owner_tbl.COUNT;
1261 l_index := l_Assign_Owner_tbl.FIRST;
1262 l_counter := l_Assign_Owner_tbl.FIRST ;
1263 WHILE l_index <= l_count
1264 LOOP
1265 l_ismember := 'N';
1266 IF (l_Assign_Owner_tbl(l_index).web_availability_flag = 'Y') THEN
1267 OPEN c_check_grp_res(l_group_id, l_Assign_Owner_tbl(l_index).resource_id);
1268 FETCH c_check_grp_res INTO l_ismember;
1269 CLOSE c_check_grp_res;
1270 IF (NVL(l_ismember, 'N') = 'Y') THEN
1271 l_resource_load_tbl(l_counter).resource_id :=
1272 l_Assign_Owner_tbl(l_index).resource_id;
1273 l_resource_load_tbl(l_counter).resource_type :=
1274 l_Assign_Owner_tbl(l_index).resource_type;
1275 l_resource_load_tbl(l_counter).support_site_id :=
1276 l_Assign_Owner_tbl(l_index).support_site_id;
1277 l_resource_load_tbl(l_counter).territory_id :=
1278 l_Assign_Owner_tbl(l_index).terr_id;
1279 -- Bug 7168029
1280 -- If the Primary COntact Flag of a resource is 'Y' and there is No Load balancing setup done,
1281 -- then assign that resource to the out record
1282 if l_Assign_Owner_tbl(l_index).primary_contact_flag ='Y'
1283 and l_load_balance='N' then
1284 x_resource_id := l_Assign_Owner_tbl(l_index).resource_id;
1285 x_resource_type := l_Assign_Owner_tbl(l_index).resource_type;
1286 x_territory_id := l_Assign_Owner_tbl(l_index).terr_id;
1287 l_resource_set:='Y';
1288 return;
1289 end if;
1290 ELSE /* Start Bug : 6241796 */
1291 l_resource_load_tbl(l_counter).resource_id :=
1292 Null;
1293 l_resource_load_tbl(l_counter).resource_type :=
1294 Null ;
1295 l_resource_load_tbl(l_counter).support_site_id :=
1296 Null ;
1297 l_resource_load_tbl(l_counter).territory_id :=
1298 Null ; /* End Bug : 6241796 */
1299 END IF;
1300 /* Start Bug : 6391261 */
1301 IF ( l_Counter = l_Assign_Owner_tbl.FIRST AND nvl(l_ismember, 'N') = 'N' ) Then
1302 null ;
1303 ELSE
1304 l_counter := l_counter + 1;
1305 END IF ;
1306 /* End Bug : 6391261 */
1307 END IF;
1308 l_index := l_index + 1;
1309 END LOOP;
1310
1311 IF (l_resource_load_tbl.COUNT > 1) THEN
1312 CS_ASSIGN_RESOURCE_PKG.Calculate_Load
1313 ( p_init_msg_list => p_init_msg_list,
1314 p_incident_id => p_incident_id,
1315 p_incident_type_id => p_service_request_rec.type_id,
1316 p_incident_severity_id => p_service_request_rec.severity_id,
1317 p_inv_item_id => p_service_request_rec.inventory_item_id,
1318 p_inv_org_id => p_service_request_rec.inventory_org_id,
1319 p_platform_org_id => p_service_request_rec.inv_platform_org_id,
1320 p_inv_cat_id => p_service_request_rec.category_id,
1321 p_platform_id => p_service_request_rec.platform_id,
1322 p_problem_code => p_service_request_rec.problem_code,
1323 p_contact_timezone_id => p_service_request_rec.time_zone_id,
1324 p_res_load_table => l_resource_load_tbl,
1325 x_return_status => l_cal_load_return_sts,
1326 x_resource_id => x_resource_id,
1327 x_resource_type => x_resource_type,
1328 x_msg_count => x_msg_count,
1329 x_msg_data => x_msg_data,
1330 x_territory_id => x_territory_id
1331 );
1332
1333 IF (l_cal_load_return_sts <> FND_API.G_RET_STS_SUCCESS) THEN
1334 /* due to TZ API error, but continue if resource is returned */
1335 IF (x_resource_id IS NOT NULL) THEN
1336 x_return_status := FND_API.G_RET_STS_SUCCESS;
1337 l_cal_load_return_sts := FND_API.G_RET_STS_SUCCESS;
1338 END IF;
1339 END IF;
1340
1341 IF(l_cal_load_return_sts <> FND_API.G_RET_STS_SUCCESS) THEN
1342 x_return_status := FND_API.G_RET_STS_ERROR ;
1343 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_OWNER');
1344 FND_MSG_PUB.Add;
1345 END IF;
1346 ELSE
1347 IF (l_resource_load_tbl.COUNT = 1) THEN
1348 l := l_resource_load_tbl.FIRST;
1349 x_resource_id := l_resource_load_tbl(l).resource_id;
1350 x_resource_type := l_resource_load_tbl(l).resource_type;
1351 x_territory_id := l_resource_load_tbl(l).territory_id;
1352 END IF;
1353 END IF; -- l_resource_load_tbl.COUNT >1
1354 -- x_territory_id := l_Assign_Owner_tbl(l).terr_id;
1355 END IF; -- l_Assign_Owner_tbl.COUNT > 1
1356 END IF ; -- Return status S
1357 END IF; -- l_incident_id and p_group_id is not null
1358 END Assign_Owner;
1359
1360 PROCEDURE Calculate_Load
1361 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1362 p_incident_id IN NUMBER,
1363 p_incident_type_id IN NUMBER,
1364 p_incident_severity_id IN NUMBER,
1365 p_inv_item_id IN NUMBER,
1366 p_inv_org_id IN NUMBER,
1367 p_inv_cat_id IN NUMBER,
1368 p_platform_org_id IN NUMBER,
1369 p_platform_id IN NUMBER,
1370 p_problem_code IN VARCHAR2,
1371 p_contact_timezone_id IN NUMBER,
1372 p_res_load_table IN OUT NOCOPY CS_ASSIGN_RESOURCE_PKG.LoadBalance_tbl_type,
1373 x_return_status OUT NOCOPY VARCHAR2,
1374 x_resource_id OUT NOCOPY NUMBER,
1375 x_resource_type OUT NOCOPY VARCHAR2,
1376 x_msg_count OUT NOCOPY NUMBER,
1377 x_msg_data OUT NOCOPY VARCHAR2,
1378 x_territory_id OUT NOCOPY NUMBER
1379
1380 ) IS
1381
1382 -- Define Local Variables
1383 l_resource_id NUMBER;
1384 l_resource_type VARCHAR2(30);
1385 l_support_site_id NUMBER;
1386 l_return_status VARCHAR2(1);
1387 l_incident_type_id NUMBER;
1388 l_incident_severity_id NUMBER;
1389 l_wt_prd_skill NUMBER;
1390 l_wt_plt_skill NUMBER;
1391 l_wt_pbm_skill NUMBER;
1392 l_wt_cat_skill NUMBER;
1393 l_wt_time_last_login NUMBER;
1394 l_wt_backlog_sev1 NUMBER;
1395 l_wt_backlog_sev2 NUMBER;
1396 l_wt_backlog_sev3 NUMBER;
1397 l_wt_backlog_sev4 NUMBER;
1398 l_wt_time_zone_lag NUMBER;
1399 l_res_load NUMBER;
1400 l_max_total_load NUMBER;
1401 l_tbl_index BINARY_INTEGER;
1402 i BINARY_INTEGER;
1403 l_count NUMBER;
1404 l_max_record_index BINARY_INTEGER;
1405 l_supp_timezone_id NUMBER;
1406 l_contact_timezone_id NUMBER := p_contact_timezone_id;
1407 l_time_lag NUMBER := NULL;
1408 l_time_lag_score NUMBER := 0;
1409 l_problem_code VARCHAR2(50) := p_problem_code;
1410 l_prod_skill NUMBER := NULL;
1411 l_plat_skill NUMBER := NULL;
1412 l_prob_skill NUMBER := NULL;
1413 l_cat_skill NUMBER := NULL;
1414 l_time_last_login NUMBER := NULL;
1415 l_backlog_sev1 NUMBER := NULL;
1416 l_backlog_sev2 NUMBER := NULL;
1417 l_backlog_sev3 NUMBER := NULL;
1418 l_backlog_sev4 NUMBER := NULL;
1419 l_imp_level NUMBER := NULL;
1420
1421 CURSOR c_load_wt(l_incident_type_id NUMBER,l_incident_severity_id NUMBER) IS
1422 SELECT product_skill_wt,platform_skill_wt,prob_code_skill_wt,category_skill_wt,
1423 last_login_time_wt,severity1_count_wt,severity2_count_wt,
1424 severity3_count_wt,severity4_count_wt,time_zone_diff_wt
1425 FROM cs_sr_load_balance_wt
1426 WHERE incident_type_id = l_incident_type_id
1427 AND incident_severity_id = l_incident_severity_id;
1428
1429 -- Added nvl(rs.category_id,0) = nvl(l_cat_id,0) by pnkalari on 06/70/2002.
1430 -- to filter correct resources when product category is null or is not null.
1431 -- Removed the Category Filter as Category is another qualifier.
1432 CURSOR c_prod_skill(l_prod_id NUMBER,l_prod_org_id NUMBER,l_resource_id NUMBER
1433 ) IS
1434 SELECT s.skill_level
1435 FROM jtf_rs_skill_levels_vl s,
1436 jtf_rs_resource_skills rs
1437 WHERE rs.resource_id = l_resource_id
1438 AND rs.product_id = l_prod_id
1439 AND rs.product_org_id = l_prod_org_id
1440 --AND NVL(rs.category_id,0) = NVL(l_cat_id,0)
1441 AND rs.skill_level_id = s.skill_level_id;
1442
1443 CURSOR c_plat_skill(l_platform_id NUMBER,l_platform_org_id NUMBER,
1444 l_resource_id NUMBER) IS
1445 SELECT s.skill_level
1446 FROM jtf_rs_skill_levels_vl s,
1447 jtf_rs_resource_skills rs
1448 WHERE rs.resource_id = l_resource_id
1449 AND rs.platform_id = l_platform_id
1450 AND rs.platform_org_id = l_platform_org_id
1451 AND rs.skill_level_id = s.skill_level_id;
1452
1453 CURSOR c_prob_skill(l_problem_code VARCHAR2,l_resource_id NUMBER) IS
1454 SELECT s.skill_level
1455 FROM jtf_rs_skill_levels_vl s,
1456 jtf_rs_resource_skills rs
1457 WHERE rs.resource_id = l_resource_id
1458 AND rs.problem_code = l_problem_code
1459 AND rs.skill_level_id = s.skill_level_id;
1460
1461 CURSOR c_cat_skill(l_category_id NUMBER, l_resource_id NUMBER) IS
1462 SELECT s.skill_level
1463 FROM jtf_rs_skill_levels_vl s,
1464 jtf_rs_resource_skills rs
1465 WHERE rs.resource_id = l_resource_id
1466 AND rs.category_id = l_category_id
1467 AND rs.skill_level_id = s.skill_level_id;
1468
1469
1470 CURSOR c_time_last_login(l_resource_id NUMBER) is
1471 SELECT ROUND(((SYSDATE - nvl( max(owner_assigned_time),to_date('1990-01-01','yyyy-mm-dd'))) *24 * 60),2)
1472 FROM cs_incidents_all_b
1473 WHERE incident_owner_id = l_resource_id;
1474
1475 CURSOR c_imp_level(p_inc_severity_id NUMBER) IS
1476 SELECT importance_level
1477 FROM cs_incident_severities_vl
1478 WHERE incident_subtype = 'INC'
1479 AND incident_severity_id = p_inc_severity_id;
1480
1481 CURSOR c_sev1_cnt(l_sev1_id NUMBER,l_resource_id NUMBER) IS
1482 SELECT COUNT(*)
1483 FROM cs_incidents_all_b
1484 WHERE incident_severity_id = l_sev1_id
1485 AND incident_owner_id = l_resource_id
1486 AND incident_status_id NOT IN (
1487 SELECT incident_status_id
1488 FROM cs_incident_statuses_vl
1489 WHERE incident_subtype = 'INC'
1490 AND close_flag = 'Y');
1491
1492 CURSOR c_sev2_cnt(l_sev2_id NUMBER ,l_resource_id NUMBER) IS
1493 SELECT COUNT(*)
1494 FROM cs_incidents_all_b
1495 WHERE incident_severity_id = l_sev2_id
1496 AND incident_owner_id = l_resource_id
1497 AND incident_status_id NOT IN (
1498 SELECT incident_status_id
1499 FROM cs_incident_statuses_vl
1500 WHERE incident_subtype = 'INC'
1501 AND close_flag = 'Y');
1502
1503 CURSOR c_sev3_cnt(l_sev3_id NUMBER,l_resource_id NUMBER) IS
1504 SELECT COUNT(*)
1505 FROM cs_incidents_all_b
1506 WHERE incident_severity_id = l_sev3_id
1507 AND incident_owner_id = l_resource_id
1508 AND incident_status_id NOT IN (
1509 select incident_status_id
1510 FROM cs_incident_statuses_vl
1511 WHERE incident_subtype = 'INC'
1512 AND close_flag = 'Y');
1513
1514 CURSOR c_sev4_cnt(l_sev4_id NUMBER,l_resource_id NUMBER) IS
1515 SELECT COUNT(*)
1516 FROM cs_incidents_all_b
1517 WHERE incident_severity_id = l_sev4_id
1518 AND incident_owner_id = l_resource_id
1519 AND incident_status_id NOT IN (
1520 SELECT incident_status_id
1521 FROM cs_incident_statuses_vl
1522 WHERE incident_subtype = 'INC'
1523 AND close_flag = 'Y');
1524
1525 CURSOR c_res_time_zone(p_resource_id NUMBER) IS
1526 SELECT time_zone
1527 FROM jtf_rs_resource_extns
1528 WHERE resource_id = p_resource_id;
1529
1530 BEGIN
1531
1532 -- Initialize API return status to success
1533 x_return_status := FND_API.G_RET_STS_SUCCESS;
1534
1535 -- Assigning type_id and severity_id to local variables so as to
1536 -- find the LB weights.
1537 l_incident_type_id := p_incident_type_id;
1538 l_incident_severity_id := p_incident_severity_id;
1539 OPEN c_load_wt(l_incident_type_id,l_incident_severity_id);
1540 FETCH c_load_wt INTO
1541 l_wt_prd_skill, l_wt_plt_skill, l_wt_pbm_skill, l_wt_cat_skill,
1542 l_wt_time_last_login, l_wt_backlog_sev1, l_wt_backlog_sev2 ,
1543 l_wt_backlog_sev3, l_wt_backlog_sev4, l_wt_time_zone_lag;
1544 IF (c_load_wt%NOTFOUND) THEN
1545 l_wt_prd_skill := 0;
1546 l_wt_plt_skill := 0;
1547 l_wt_pbm_skill := 0;
1548 l_wt_cat_skill := 0;
1549 l_wt_time_last_login := 0;
1550 l_wt_backlog_sev1 := 0;
1551 l_wt_backlog_sev2 := 0;
1552 l_wt_backlog_sev3 := 0;
1553 l_wt_backlog_sev4 := 0;
1554 l_wt_time_zone_lag := 0;
1555 END IF;
1556 CLOSE c_load_wt;
1557
1558 l_tbl_index := p_res_load_table.FIRST;
1559 l_count := p_res_load_table.COUNT;
1560 WHILE l_tbl_index <= l_count
1561 LOOP
1562 l_resource_id := p_res_load_table(l_tbl_index).resource_id;
1563 l_resource_type := p_res_load_table(l_tbl_index).resource_type;
1564 l_support_site_id := p_res_load_table(l_tbl_index).support_site_id;
1565 l_supp_timezone_id := NULL;
1566 l_time_lag := NULL;
1567 l_time_lag_score := NULL;
1568 l_res_load := NULL;
1569
1570 IF (p_inv_item_id IS NOT NULL AND p_inv_org_id IS NOT NULL AND
1571 l_resource_id IS NOT NULL AND NVL(l_wt_prd_skill,0) >0 ) THEN
1572 OPEN c_prod_skill(p_inv_item_id,p_inv_org_id,l_resource_id);
1573 FETCH c_prod_skill INTO l_prod_skill;
1574 IF (c_prod_skill%NOTFOUND) THEN
1575 l_prod_skill := NULL;
1576 END IF;
1577 CLOSE c_prod_skill;
1578 END IF;
1579
1580 IF (p_platform_id IS NOT NULL AND p_platform_org_id IS NOT NULL AND
1581 l_resource_id IS NOT NULL AND NVL(l_wt_plt_skill,0)>0) THEN
1582 OPEN c_plat_skill(p_platform_id,p_platform_org_id,l_resource_id);
1583 FETCH c_plat_skill INTO l_plat_skill;
1584 IF (c_plat_skill%NOTFOUND) THEN
1585 l_plat_skill := NULL;
1586 END IF;
1587 CLOSE c_plat_skill;
1588 END IF;
1589
1590 IF (l_problem_code IS NOT NULL AND
1591 l_resource_id IS NOT NULL AND NVL(l_wt_pbm_skill,0)>0) THEN
1592 OPEN c_prob_skill(l_problem_code,l_resource_id);
1593 FETCH c_prob_skill INTO l_prob_skill;
1594 IF (c_prob_skill%NOTFOUND) THEN
1595 l_prob_skill := NULL;
1596 END IF;
1597 CLOSE c_prob_skill;
1598 END IF;
1599
1600 IF (p_inv_cat_id IS NOT NULL AND
1601 l_resource_id IS NOT NULL AND NVL(l_wt_cat_skill,0) >0) THEN
1602 OPEN c_cat_skill(p_inv_cat_id, l_resource_id);
1603 FETCH c_cat_skill INTO l_cat_skill;
1604 IF (c_cat_skill%NOTFOUND) THEN
1605 l_cat_skill := NULL;
1606 END IF;
1607 CLOSE c_cat_skill;
1608 END IF;
1609
1610 -- Changed the if condition to calculate the count of SRs if
1611 -- l_resource_id is not null 11.5.9
1612 IF (l_resource_id IS NOT NULL) THEN
1613 IF (NVL(l_wt_time_last_login,0)<>0) THEN
1614 -- for every resource get the backlog of severity 1,2,3,4 SR's
1615 OPEN c_time_last_login(l_resource_id);
1616 FETCH c_time_last_login INTO l_time_last_login;
1617 IF (c_time_last_login%NOTFOUND) THEN
1618 l_time_last_login := NULL;
1619 END IF;
1620 CLOSE c_time_last_login;
1621 END IF;
1622
1623 OPEN c_imp_level(l_incident_severity_id);
1624 FETCH c_imp_level INTO l_imp_level;
1625 IF (c_imp_level%NOTFOUND) THEN
1626 l_imp_level := 0;
1627 END IF;
1628 CLOSE c_imp_level;
1629
1630 IF (l_imp_level = 1 AND NVL(l_wt_backlog_sev1,0) <> 0) THEN
1631 OPEN c_sev1_cnt(l_incident_severity_id,l_resource_id);
1632 FETCH c_sev1_cnt INTO l_backlog_sev1;
1633 IF (c_sev1_cnt%NOTFOUND) THEN
1634 l_backlog_sev1 := NULL;
1635 END IF;
1636 CLOSE c_sev1_cnt;
1637 ELSIF (l_imp_level = 2 AND NVL(l_wt_backlog_sev2,0) <> 0) THEN
1638 OPEN c_sev2_cnt(l_incident_severity_id,l_resource_id);
1639 FETCH c_sev2_cnt INTO l_backlog_sev2;
1640 IF (c_sev2_cnt%NOTFOUND) THEN
1641 l_backlog_sev2 := NULL;
1642 END IF;
1643 CLOSE c_sev2_cnt;
1644 ELSIF (l_imp_level = 3 AND NVL(l_wt_backlog_sev3,0) <> 0) THEN
1645 OPEN c_sev3_cnt(l_incident_severity_id,l_resource_id);
1646 FETCH c_sev3_cnt INTO l_backlog_sev3;
1647 IF (c_sev3_cnt%NOTFOUND) THEn
1648 l_backlog_sev3 := NULL;
1649 END IF;
1650 CLOSE c_sev3_cnt;
1651 ELSIF (l_imp_level = 4 AND NVL(l_wt_backlog_sev4,0) <> 0) THEN
1652 OPEN c_sev4_cnt(l_incident_severity_id,l_resource_id);
1653 FETCH c_sev4_cnt INTO l_backlog_sev4;
1654 IF (c_sev4_cnt%NOTFOUND) THEN
1655 l_backlog_sev4 := NULL;
1656 END IF;
1657 CLOSE c_sev4_cnt;
1658 ELSE
1659 l_backlog_sev1 := NULL;
1660 l_backlog_sev2 := NULL;
1661 l_backlog_sev3 := NULL;
1662 l_backlog_sev4 := NULL;
1663 END IF;
1664
1665 END IF; -- l_resource_id is not null
1666
1667 IF (l_support_site_id IS NOT NULL AND NVL(l_wt_time_zone_lag,0)<>0) THEN
1668 OPEN c_res_time_zone(l_resource_id);
1669 FETCH c_res_time_zone INTO l_supp_timezone_id;
1670 IF (c_res_time_zone%NOTFOUND) THEN
1671 l_supp_timezone_id := NULL;
1672 END IF;
1673 CLOSE c_res_time_zone;
1674 END IF;
1675
1676 IF (l_contact_timezone_id IS NOT NULL AND
1677 l_supp_timezone_id IS NOT NULL ) THEN
1678 IF (l_contact_timezone_id <> l_supp_timezone_id) THEN
1679 CS_TZ_GET_DETAILS_PVT.GET_LEADTIME
1680 (1.0,
1681 'T',
1682 l_supp_timezone_id,
1683 l_contact_timezone_id,
1684 l_time_lag,
1685 x_return_status,
1686 x_msg_count,
1687 x_msg_data);
1688 ELSE
1689 l_time_lag := 0;
1690 END IF;
1691
1692 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1693 FND_MESSAGE.Set_Name('CS', 'CS_TZ_API_ERR');
1694 FND_MSG_PUB.Add;
1695 EXIT ;
1696 END IF;
1697 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1698 l_time_lag := abs(l_time_lag);
1699 ELSE
1700 l_time_lag := 0;
1701 END IF;
1702 ELSE /* l_contact_timezone_id or l_supp_timezone_id is missing */
1703 l_time_lag := 0;
1704 END IF; /* l_contact_timezone_id or l_supp_timezone_id is missing */
1705
1706 -- New formula for time lag for OSS , as given in enhancement 2093850
1707 -- Added weight multiplication and support timezone id check for Bug# 3526252
1708 IF (l_supp_timezone_id IS NULL) THEN
1709 l_time_lag_score := 0;
1710 ELSE
1711 l_time_lag_score := ROUND(2.77 - (l_time_lag/4)) * NVL(l_wt_time_zone_lag,0);
1712 END IF;
1713
1714 -- calculate total load for each
1715 -- Added nvl for all the weights 11.5.9
1716 l_res_load :=
1717 ((NVL(l_prod_skill,0) * NVL(l_wt_prd_skill,0)) +
1718 (NVL(l_plat_skill,0) * NVL(l_wt_plt_skill,0)) +
1719 (NVL(l_prob_skill,0) * NVL(l_wt_pbm_skill,0)) +
1720 (NVL(l_cat_skill,0) * NVL(l_wt_cat_skill,0)) +
1721 (NVL(l_time_last_login,0) * NVL(l_wt_time_last_login,0)) +
1722 (NVL(l_backlog_sev1,0) * NVL(l_wt_backlog_sev1,0)) +
1723 (NVL(l_backlog_sev2,0) * NVL(l_wt_backlog_sev2,0)) +
1724 (NVL(l_backlog_sev3,0) * NVL(l_wt_backlog_sev3,0)) +
1725 (NVL(l_backlog_sev4,0) * NVL(l_wt_backlog_sev4,0)) +
1726 (NVL(l_time_lag_score,0) * NVL(l_wt_time_zone_lag,0)));
1727 -- copy values into table
1728 p_res_load_table(l_tbl_index).product_skill_level := l_prod_skill;
1729 p_res_load_table(l_tbl_index).platform_skill_level := l_plat_skill;
1730 p_res_load_table(l_tbl_index).pbm_code_skill_level := l_prob_skill;
1731 p_res_load_table(l_tbl_index).category_skill_level := l_cat_skill;
1732 p_res_load_table(l_tbl_index).time_since_last_login := l_time_last_login;
1733 p_res_load_table(l_tbl_index).backlog_sev1 := l_backlog_sev1;
1734 p_res_load_table(l_tbl_index).backlog_sev2 := l_backlog_sev2;
1735 p_res_load_table(l_tbl_index).backlog_sev3 := l_backlog_sev3;
1736 p_res_load_table(l_tbl_index).backlog_sev4 := l_backlog_sev4;
1737 p_res_load_table(l_tbl_index).time_zone_lag := l_time_lag;
1738 p_res_load_table(l_tbl_index).total_load := l_res_load;
1739
1740 l_tbl_index := l_tbl_index + 1;
1741
1742 END LOOP; /* l_tbl_index <= l_count loop */
1743
1744 -- After the load for all resources are calculated find the
1745 -- resource with the max load.This is the winning resource to
1746 -- be returned
1747 -- If timezone API does not give error then proceed
1748 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1749 -- Changed index from i=0 to i=1 by pnkalari on 06/11/2002.
1750 --i := 0;
1751 -- Added this because if all the resource loads are 0,
1752 -- l_max_record_index is always the first resource.
1753 l_max_record_index := p_res_load_table.FIRST;
1754
1755 -- Bug 4907196 . The value of l_max_total_load is changed to -9999999 from 0.
1756 -- Total load can have negative values hence the initial value shouldn't be 0
1757 l_max_total_load := -999999999;
1758
1759 /* Commented out for Bug# 4017138
1760 FOR i IN 1..p_res_load_table.COUNT
1761 LOOP
1762 IF (i = 1 ) THEN
1763 l_max_total_load := p_res_load_table(i).total_load ;
1764 l_max_record_index := i ;
1765 ELSE
1766 IF (p_res_load_table(i).total_load > l_max_total_load) THEN
1767 l_max_total_load := p_res_load_table(i).total_load;
1768 l_max_record_index := i;
1769 END IF;
1770 END IF;
1771 END LOOP ;
1772 */
1773 IF (p_res_load_table.COUNT > 0) THEN
1774 FOR i IN p_res_load_table.FIRST..p_res_load_table.LAST LOOP
1775 IF ( p_res_load_table.COUNT = 1 ) THEN
1776 l_max_total_load := p_res_load_table(i).total_load ;
1777 l_max_record_index := i ;
1778 ELSE
1779 IF (p_res_load_table(i).total_load > l_max_total_load) THEN
1780 l_max_total_load := p_res_load_table(i).total_load;
1781 l_max_record_index := i;
1782 END IF;
1783 END IF;
1784 END LOOP ;
1785 END IF;
1786
1787 x_resource_id := p_res_load_table(l_max_record_index).resource_id;
1788 x_resource_type := p_res_load_table(l_max_record_index).resource_type;
1789 x_territory_id := p_res_load_table(l_max_record_index).territory_id;
1790
1791 IF (x_resource_id IS NOT NULL) THEN
1792 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1793 ELSIF (x_resource_id IS NULL) THEN
1794 x_return_status := FND_API.G_RET_STS_ERROR;
1795 END IF;
1796
1797 ELSE /* x_return_status = FND_API.G_RET_STS_SUCCESS */
1798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1799 END IF; /* x_return_status = FND_API.G_RET_STS_SUCCESS */
1800
1801 END Calculate_Load;
1802
1803 END CS_ASSIGN_RESOURCE_PKG;