[Home] [Help]
PACKAGE BODY: APPS.CS_ASSIGN_RESOURCE_PKG
Source
1 PACKAGE BODY CS_ASSIGN_RESOURCE_PKG as
2 /* $Header: csvasrsb.pls 120.7.12000000.4 2007/09/07 05:27:03 amganapa 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;
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;
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;
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);
367 l_update_own_flag := 'Y';
364 FND_MSG_PUB.Add;
365 l_main_return_status := FND_API.G_RET_STS_SUCCESS;
366 ELSE
368 END IF;
369 ELSE
370 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
371 --FND_MSG_PUB.Initialize;
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 --parameters
488 l_am_calling_doc_type VARCHAR2(2) := 'SR';
489 l_am_calling_doc_id NUMBER := NULL;
490 l_resource_type VARCHAR2(30) := p_group_type;
491 l_web_availability_flag VARCHAR2(1) := NULL;
495 l_ib_resource_flag VARCHAR2(3);
492 l_no_of_resources NUMBER := NULL;
493 l_cust_prod_id NUMBER := p_service_request_rec.customer_product_id;
494 l_contract_res_flag VARCHAR2(3);
496 l_business_process_id NUMBER;
497
498 c_customer_phone_id NUMBER := p_service_request_rec.customer_phone_id;
499 -- List of Cursors used
500 CURSOR c_inc_address(p_incident_location_id NUMBER) IS
501 SELECT country,province,state,city,postal_code,county
502 FROM hz_locations
503 WHERE location_id = p_incident_location_id;
504
505 CURSOR c_inc_party_site_address(p_party_site_id NUMBER) IS
506 SELECT location_id FROM hz_party_sites
507 WHERE party_site_id = p_party_site_id;
508
509 CURSOR C_CONTRACT(l_contract_service_id number) IS
510 SELECT to_number(object1_id1), to_number(object1_id2)
511 FROM okc_k_items
512 WHERE cle_id = l_contract_service_id;
513
514 /* Waiting for JTA patch for their sql change so comment out for now
515 -- VIP Customer Code
516 CURSOR C_CLASS_CODE(l_party_id number,l_cust_category varchar2) is
517 SELECT class_code
518 FROM hz_code_assignments
519 WHERE owner_table_name = 'HZ_PARTIES'
520 AND owner_table_id = l_party_id
521 AND class_category = l_cust_category;
522 */
523 --Bug 5255184 Modified the c_area_code query
524 CURSOR c_area_code IS
525 SELECT hzp.phone_area_code
526 FROM hz_contact_points hzp
527 WHERE hzp.contact_point_id = c_customer_phone_id;
528
529 CURSOR c_cust_det(p_customer_id NUMBER) IS
530 SELECT employees_total, party_name
531 FROM hz_parties
532 WHERE party_id = p_customer_id;
533
534 BEGIN
535 -- Initialize API return status to success
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 -- Assign the incident_id to a local variable
539 l_incident_id := p_incident_id;
540 -- Proceed only if incident_id is not null
541 -- Group type must have a value - default is RS_GROUP, passed by caller
542 --IF (l_incident_id IS NOT NULL) AND
543 IF (p_group_type IS NOT NULL) THEN
544 l_resource_type := p_group_type;
545
546 l_incident_id := p_incident_id;
547 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_AUTO_ASSIGN') = 'YES') THEN
548 SELECT business_process_id INTO l_business_process_id
549 FROM cs_incident_types
550 WHERE incident_type_id = l_sr_rec.type_id;
551 END IF;
552
553
554 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
555 -- Assigning component and subcomponent id directly to the am rec
556
557 IF (l_sr_rec.customer_product_id IS NOT NULL) THEN
558 l_sr_am_rec.item_component := l_sr_rec.cp_component_id;
559 l_sr_am_rec.item_subcomponent := l_sr_rec.cp_subcomponent_id;
560 ELSE
561 l_sr_am_rec.item_component := l_sr_rec.inv_component_id;
562 l_sr_am_rec.item_subcomponent := l_sr_rec.inv_subcomponent_id;
563 END IF;
564
565 IF (p_service_request_rec.incident_location_id IS NOT NULL) THEN
566 IF (p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE') THEN
567 OPEN c_inc_party_site_address(p_service_request_rec.incident_location_id);
568 FETCH c_inc_party_site_address INTO l_location_id;
569 IF (c_inc_party_site_address%NOTFOUND) THEN
570 l_location_id := NULL;
571 END IF;
572 CLOSE c_inc_party_site_address;
573 END IF;
574 OPEN c_inc_address(l_location_id);
575 FETCH c_inc_address INTO l_country, l_province, l_state, l_city,
576 l_postal_code, l_county;
577 IF (c_inc_address%NOTFOUND) THEN
578 NULL;
579 END IF;
580 l_sr_am_rec.country := l_country;
581 l_sr_am_rec.city := l_city;
582 l_sr_am_rec.postal_code := l_postal_code;
583 l_sr_am_rec.state := l_state;
584 l_sr_am_rec.province := l_province;
585 l_sr_am_rec.county := l_county;
586 CLOSE c_inc_address;
587 ELSE
588 l_sr_am_rec.country := p_service_request_rec.incident_country;
589 l_sr_am_rec.city := p_service_request_rec.incident_city;
590 l_sr_am_rec.postal_code := p_service_request_rec.incident_postal_code;
591 l_sr_am_rec.state := p_service_request_rec.incident_state;
592 l_sr_am_rec.province := p_service_request_rec.incident_province;
593 l_sr_am_rec.county := p_service_request_rec.incident_county;
594 END IF;
595 --Bug 5255184 Modified the c_area_code
596 OPEN c_area_code;
597 FETCH c_area_code INTO l_area_code;
598 IF (c_area_code%NOTFOUND) THEN
599 l_area_code := NULL;
600 END IF;
601 CLOSE c_area_code;
602
603 OPEN c_cust_det(l_sr_rec.customer_id);
604 FETCH c_cust_det INTO l_no_of_employees, l_party_name;
605 IF (c_cust_det%NOTFOUND) THEN
606 l_no_of_employees := NULL;
607 l_party_name := NULL;
608 END IF;
609 CLOSE c_cust_det;
610
611 -- Assign the values to the AM Record Type
612 l_sr_am_rec.service_request_id := l_incident_id;
613 l_sr_am_rec.party_id := l_sr_rec.customer_id;
614 l_sr_am_rec.incident_type_id := l_sr_rec.type_id;
615 l_sr_am_rec.incident_severity_id := l_sr_rec.severity_id;
616 l_sr_am_rec.incident_urgency_id := l_sr_rec.urgency_id;
617 l_sr_am_rec.problem_code := l_sr_rec.problem_code;
618 l_sr_am_rec.incident_status_id := l_sr_rec.status_id;
622 l_sr_am_rec.area_code := l_area_code;
619 l_sr_am_rec.platform_id := l_sr_rec.platform_id;
620 l_sr_am_rec.sr_creation_channel := l_sr_rec.sr_creation_channel;
621 l_sr_am_rec.inventory_item_id := l_sr_rec.inventory_item_id;
623 l_sr_am_rec.squal_char12 := l_sr_rec.problem_code;
624 l_sr_am_rec.squal_char13 := l_sr_rec.comm_pref_code;
625 l_sr_am_rec.squal_num12 := l_sr_rec.platform_id;
626 l_sr_am_rec.squal_num13 := l_sr_rec.inv_platform_org_id;
627 l_sr_am_rec.squal_num14 := l_sr_rec.category_id;
628 l_sr_am_rec.squal_num15 := l_sr_rec.inventory_item_id;
629 l_sr_am_rec.squal_num16 := l_sr_rec.inventory_org_id;
630 l_sr_am_rec.squal_num17 := NULL;
631 l_sr_am_rec.squal_num30 := l_sr_rec.language_id;
632 l_sr_am_rec.squal_char20 := l_sr_rec.cust_pref_lang_code;
633 l_sr_am_rec.squal_char21 := l_sr_rec.coverage_type;
634 l_sr_am_rec.num_of_employees := l_no_of_employees;
635 l_sr_am_rec.comp_name_range := l_party_name;
636
637 -- Commented below for implementation will be done only from 11.5.10
638 /*l_sr_am_rec.party_site_id := l_sr_rec.customer_site_id;
639 l_sr_am_rec.customer_site_id := l_sr_rec.customer_site_id;
640 l_sr_am_rec.support_site_id := l_sr_rec.site_id;*/
641
642 -- Contract Item and Org dtls
643 IF (l_contract_service_id IS NOT NULL) THEN
644 OPEN c_contract(l_contract_service_id);
645 FETCH c_contract INTO l_inv_item_id,l_inv_org_id;
646 IF (c_contract%NOTFOUND) THEN
647 NULL;
648 END IF;
649 CLOSE c_contract;
650 END IF;
651 -- Assign the values to the qualifiers
652 l_sr_am_rec.squal_num18 := l_inv_item_id;
653 l_sr_am_rec.squal_num19 := l_inv_org_id;
654
655 /* Waiting for JTA patch for their sql change so comment out for now
656 -- VIP Customer Code
657 IF (l_party_id IS NOT NULL) THEN
658 OPEN c_class_code(l_party_id,l_cust_category);
659 FETCH c_class_code INTO l_class_code;
660 IF (c_class_code%NOTFOUND) THEN
661 NULL;
662 END IF;
663 CLOSE c_class_code;
664 END IF;
665 l_sr_am_rec.squal_char11 := l_class_code;
666 */
667
668 -- Populate the form parameters
669 l_am_calling_doc_id := l_incident_id;
670
671 -- Passing the auto_select_flag as 'N' bcoz if it is null the JTF API
672 -- assigns it as 'Y' and always returns the first record. No Load Balancing
673 -- is done.
674 -- If customer product id is not null, then set ib_preferred_resource_flag
675 -- to 'Y'.If contract line id is not null, then set
676 -- contract_preferred_resource flag to 'Y'.
677 l_cust_prod_id := l_sr_rec.customer_product_id;
678 IF (l_contract_service_id IS NOT NULL) THEN
679 l_contract_res_flag := 'Y';
680 ELSE
681 l_contract_res_flag := 'N';
682 END IF;
683 IF (l_cust_prod_id IS NOT NULL) THEN
684 l_ib_resource_flag := 'Y';
685 ELSE
686 l_ib_resource_flag := 'N';
687 END IF;
688
689 JTF_ASSIGN_PUB.GET_Assign_Resources
690 ( p_api_version => 1.0,
691 p_init_msg_list => FND_API.G_FALSE,
692 p_commit => 'F',
693 p_resource_id => NULL,
694 p_resource_type => l_resource_type,
695 p_role => NULL,
696 p_no_of_resources => l_no_of_resources,
697 p_auto_select_flag => 'N',
698 p_contracts_preferred_engineer => l_contract_res_flag,
699 p_ib_preferred_engineer => l_ib_resource_flag,
700 p_contract_id => l_contract_service_id,
701 p_customer_product_id => l_cust_prod_id,
702 p_effort_duration => NULL,
703 p_effort_uom => NULL,
704 p_start_date => NULL,
705 p_end_date => NULL,
706 p_territory_flag => 'Y',
707 p_calendar_flag => 'N',
708 p_web_availability_flag => 'Y',
709 p_filter_excluded_resource => 'Y',
710 p_category_id => NULL,
711 p_inventory_item_id => NULL,
712 p_inventory_org_id => NULL,
713 p_column_list => NULL,
714 p_calling_doc_id => NULL,
715 p_calling_doc_type => 'SR',
716 p_sr_rec => l_sr_am_rec,
717 p_sr_task_rec => NULL,
718 p_defect_rec => NULL,
719 p_business_process_id => l_business_process_id,
720 p_business_process_date => l_sr_rec.request_date,
721 x_Assign_Resources_tbl => l_Assign_Groups_tbl,
722 x_return_status => x_return_status,
723 x_msg_count => x_msg_count,
724 x_msg_data => x_msg_data
725 );
726
727 IF (x_return_status = FND_API.G_RET_STS_SUCCESS AND
728 l_Assign_Groups_tbl.COUNT > 0) THEN
729 Get_Sup_Usage_Group(l_Assign_Groups_tbl, x_resource_id, x_territory_id);
730 END IF;
731
732 END IF; /* l_incident_id and p_group_type is not null */
733 END Assign_Group;
734
738 == Parameters :
735 /*==========================================================================================+
736 ==
737 == Procedure name : Assign_Owner
739 == IN : event VARCHAR2
740 == OUT : none.
741 ==
742 == Description : This Procedure returns the individual Owner from the Group
743 == returned by the Assign_Group Procedure.
744 == Modification History:
745 ==
746 == Date Name Desc
747 == ---------- --------- ---------------------------------------------
748 == 08/02/2007 GASANKAR Bug 6241796 Fixed
749 == Initializing p_res_load_table(l_tbl_index).resource_type ,
750 == resource_id if the resource is not belonging to a group.
751 == 07/09/2007 GASANKAR Bug 639126 Fixed
752 == First record of the p_res_load_table is not been left blank, so
753 == that contract preferred resource will work properly.
754 ===========================================================================================*/
755
756 PROCEDURE Assign_Owner
757 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
758 p_commit IN VARCHAR2 := FND_API.G_FALSE,
759 p_incident_id IN NUMBER,
760 p_param_resource_type IN VARCHAR2,
761 p_group_id IN NUMBER,
762 p_service_request_rec IN CS_ServiceRequest_pvt.service_request_rec_type,
763 x_return_status OUT NOCOPY VARCHAR2,
764 x_resource_id OUT NOCOPY NUMBER,
765 x_resource_type OUT NOCOPY VARCHAR2,
766 x_territory_id OUT NOCOPY NUMBER,
767 x_msg_count OUT NOCOPY NUMBER,
768 x_msg_data OUT NOCOPY VARCHAR2
769 ) IS
770
771 -- Profiles variables
772 l_web_availability_check VARCHAR2(1);
773 l_category_set_id NUMBER;
774 l_platform_catg_set_id NUMBER;
775 -- Input and output data structures
776 l_Assign_Owner_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type ;
777 l_sr_am_rec JTF_ASSIGN_PUB.JTF_Serv_req_rec_type;
778 l_resource_load_tbl CS_ASSIGN_RESOURCE_PKG.LoadBalance_tbl_type;
779 l_sr_rec CS_ServiceRequest_pvt.service_request_rec_type DEFAULT p_service_request_rec;
780 l_index BINARY_INTEGER;
781 l_count NUMBER;
782 p NUMBER;
783 l NUMBER;
784 l_cal_load_return_sts VARCHAR2(1) := NULL;
785 -- Qualifier values
786 l_incident_id NUMBER := p_incident_id;
787 l_contract_service_id NUMBER := p_service_request_rec.contract_service_id;
788 l_cust_prod_id NUMBER := p_service_request_rec.customer_product_id;
789 l_inv_item_id NUMBER := NULL;
790 l_inv_org_id NUMBER := NULL;
791 l_inv_category_id NUMBER := NULL;
792 l_ib_inv_comp_id NUMBER := NULL;
793 l_ib_inv_subcomp_id NUMBER := NULL;
794 l_group_id NUMBER := p_group_id;
795 l_party_id NUMBER := p_service_request_rec.customer_id;
796 l_location_id NUMBER := p_service_request_rec.incident_location_id;
797 l_class_code VARCHAR2(30) := NULL;
798 l_cust_category VARCHAR2(30) := NULL;
799 l_country VARCHAR2(60) := NULL;
800 l_province VARCHAR2(60) := NULL;
801 l_postal_code VARCHAR2(60) := NULL;
802 l_city VARCHAR2(60) := NULL;
803 l_state VARCHAR2(60) := NULL;
804 l_county VARCHAR2(60) := NULL;
805 l_party_name VARCHAR2(360) := NULL;
806 -- Passing parameters
807 l_ismember VARCHAR2(1) := 'N';
808 l_am_calling_doc_type VARCHAR2(2) := 'SR';
809 l_param_resource_type VARCHAR2(30) := p_param_resource_type;
810 l_web_availability_flag VARCHAR2(1) := NULL;
811 l_am_calling_doc_id NUMBER := NULL;
812 l_no_of_resources NUMBER := NULL;
813 l_no_of_employees NUMBER := NULL;
814 l_product_skill_level NUMBER;
815 l_counter NUMBER;
816 l_cat_wt NUMBER;
817 l_prod_wt NUMBER;
818 l_prob_wt NUMBER;
819 l_business_process_id NUMBER;
820 l_area_code VARCHAR2(50);
821 l_contract_res_flag VARCHAR2(3);
822 l_ib_resource_flag VARCHAR2(3);
823 l_prod_skill_check VARCHAR2(3);
824
825 c_customer_phone_id NUMBER := p_service_request_rec.customer_phone_id;
826
827 CURSOR c_inc_address(p_incident_location_id NUMBER) IS
828 SELECT country,province,state,city,postal_code,county
829 FROM hz_locations
830 WHERE location_id = p_incident_location_id;
831
832 CURSOR c_inc_party_site_address(p_party_site_id NUMBER) IS
833 SELECT location_id FROM hz_party_sites
834 WHERE party_site_id = p_party_site_id;
835
836 CURSOR c_contract(l_contract_service_id NUMBER)IS
837 SELECT TO_NUMBER(object1_id1), TO_NUMBER(object1_id2)
838 FROM okc_k_items
839 WHERE cle_id = l_contract_service_id;
840
841 /* Waiting for JTA patch for their sql change so comment out for now
842 -- VIP Customer Code
843 CURSOR c_class_code(l_party_id NUMBER,l_cust_category VARCHAR2) IS
844 SELECT class_code
845 FROM hz_code_assignments
846 WHERE owner_table_name = 'HZ_PARTIES'
847 AND owner_table_id = l_party_id
848 AND class_category = l_cust_category;
849 */
850 --Bug 5255184 Modified the c_area_code query
854 WHERE hzp.contact_point_id = c_customer_phone_id;
851 CURSOR c_area_code IS
852 SELECT hzp.phone_area_code
853 FROM hz_contact_points hzp
855
856 CURSOR c_check_grp_res(p_group_id NUMBER, p_resource_id NUMBER) IS
857 SELECT 'Y'
858 FROM jtf_rs_group_members
859 WHERE group_id = p_group_id
860 AND resource_id = p_resource_id
861 AND NVL(delete_flag, 'N') <> 'Y';
862
863 CURSOR c_cust_det(p_customer_id NUMBER) IS
864 SELECT employees_total, party_name
865 FROM hz_parties
866 WHERE party_id = p_customer_id;
867
868 BEGIN
869
870 -- Initialize API return status to success
871 x_return_status := FND_API.G_RET_STS_SUCCESS;
872 -- Assign the incident_id to a local variable
873 l_incident_id := p_incident_id;
874
875 -- Proceed only if incident_id is not null
876 -- Group type must have a value
877 --IF ((l_incident_id IS NOT NULL) AND (p_group_id IS NOT NULL )) THEN
878 IF p_group_id IS NOT NULL THEN
879 l_group_id := p_group_id ;
880
881 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_AUTO_ASSIGN') = 'YES') THEN
882 SELECT business_process_id INTO l_business_process_id
883 FROM cs_incident_types
884 WHERE incident_type_id = l_sr_rec.type_id;
885 END IF;
886
887 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
888 -- Assigning component and subcomponent id directly to the am rec
889
890 IF (l_sr_rec.customer_product_id IS NOT NULL) THEN
891 l_sr_am_rec.item_component := l_sr_rec.cp_component_id;
892 l_sr_am_rec.item_subcomponent := l_sr_rec.cp_subcomponent_id;
893 ELSE
894 l_sr_am_rec.item_component := l_sr_rec.inv_component_id;
895 l_sr_am_rec.item_subcomponent := l_sr_rec.inv_subcomponent_id;
896 END IF;
897
898
899 IF (p_service_request_rec.incident_location_id IS NOT NULL) THEN
900 IF (p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE') THEN
901 OPEN c_inc_party_site_address(p_service_request_rec.incident_location_id);
902 FETCH c_inc_party_site_address INTO l_location_id;
903 IF (c_inc_party_site_address%NOTFOUND) THEN
904 l_location_id := NULL;
905 END IF;
906 CLOSE c_inc_party_site_address;
907 END IF;
908 OPEN c_inc_address(l_location_id);
909 FETCH c_inc_address INTO l_country,l_province,l_state,l_city,
910 l_postal_code, l_county;
911 IF (c_inc_address%NOTFOUND) THEN
912 NULL;
913 END IF;
914 l_sr_am_rec.country := l_country;
915 l_sr_am_rec.city := l_city;
916 l_sr_am_rec.postal_code := l_postal_code;
917 l_sr_am_rec.state := l_state;
918 l_sr_am_rec.province := l_province;
919 l_sr_am_rec.county := l_county;
920 CLOSE c_inc_address;
921 ELSE
922 l_sr_am_rec.country := p_service_request_rec.incident_country;
923 l_sr_am_rec.city := p_service_request_rec.incident_city;
924 l_sr_am_rec.postal_code := p_service_request_rec.incident_postal_code;
925 l_sr_am_rec.state := p_service_request_rec.incident_state;
926 l_sr_am_rec.province := p_service_request_rec.incident_province;
927 l_sr_am_rec.county := p_service_request_rec.incident_county;
928 END IF;
929 --Bug 5255184 Modified the c_area_code
930 OPEN c_area_code;
931 FETCH c_area_code INTO l_area_code;
932 IF (c_area_code%NOTFOUND) THEN
933 l_area_code := NULL;
934 END IF;
935 CLOSE c_area_code;
936
937 OPEN c_cust_det(l_sr_rec.customer_id);
938 FETCH c_cust_det INTO l_no_of_employees, l_party_name;
939 IF (c_cust_det%NOTFOUND) THEN
940 l_no_of_employees := NULL;
941 l_party_name := NULL;
942 END IF;
943 CLOSE c_cust_det;
944
945 l_sr_am_rec.service_request_id := l_incident_id;
946 l_sr_am_rec.party_id := l_sr_rec.customer_id;
947 l_sr_am_rec.incident_type_id := l_sr_rec.type_id;
948 l_sr_am_rec.incident_severity_id := l_sr_rec.severity_id;
949 l_sr_am_rec.incident_urgency_id := l_sr_rec.urgency_id;
950 l_sr_am_rec.problem_code := l_sr_rec.problem_code;
951 l_sr_am_rec.incident_status_id := l_sr_rec.status_id;
952 l_sr_am_rec.platform_id := l_sr_rec.platform_id;
953 l_sr_am_rec.sr_creation_channel := l_sr_rec.sr_creation_channel;
954 l_sr_am_rec.inventory_item_id := l_sr_rec.inventory_item_id;
955 l_sr_am_rec.area_code := l_area_code;
956 l_sr_am_rec.squal_char12 := l_sr_rec.problem_code;
957 l_sr_am_rec.squal_char13 := l_sr_rec.comm_pref_code;
958 l_sr_am_rec.squal_char20 := l_sr_rec.cust_pref_lang_code ;
959 l_sr_am_rec.squal_char21 := l_sr_rec.coverage_type;
960 l_sr_am_rec.squal_num12 := l_sr_rec.platform_id;
961 l_sr_am_rec.squal_num13 := l_sr_rec.inv_platform_org_id;
962 l_sr_am_rec.squal_num14 := l_sr_rec.category_id;
963 l_sr_am_rec.squal_num15 := l_sr_rec.inventory_item_id;
964 l_sr_am_rec.squal_num16 := l_sr_rec.inventory_org_id;
965 l_sr_am_rec.squal_num17 := l_group_id;
966 l_sr_am_rec.squal_num30 := l_sr_rec.language_id;
967 l_sr_am_rec.num_of_employees := l_no_of_employees;
968 l_sr_am_rec.comp_name_range := l_party_name;
969
970 --Contract Item and Org dtls
974 IF c_contract%NOTFOUND THEN
971 IF (l_contract_service_id IS NOT NULL) THEN
972 OPEN c_contract(l_contract_service_id);
973 FETCH c_contract INTO l_inv_item_id,l_inv_org_id;
975 NULL;
976 END IF;
977 CLOSE c_contract;
978 END IF;
979 -- Assign it to the AM record type For contracts
980 l_sr_am_rec.squal_num18 := l_inv_item_id;
981 l_sr_am_rec.squal_num19 := l_inv_org_id;
982 l_sr_am_rec.squal_char11 := null;
983
984 -- populate the AM parameters
985 l_am_calling_doc_id := l_incident_id;
986 l_param_resource_type := p_param_resource_type;
987
988 -- If customer product id is not null, then set ib_preferred_resource_flag
989 -- to 'Y'.If contract line id is not null, then set
990 -- contract_preferred_resource flag to 'Y'.
991 l_cust_prod_id := l_sr_rec.customer_product_id;
992 IF (l_contract_service_id IS NOT NULL) THEN
993 l_contract_res_flag := 'Y';
994 ELSE
995 l_contract_res_flag := 'N';
996 END IF;
997 IF (l_cust_prod_id IS NOT NULL) THEN
998 l_ib_resource_flag := 'Y';
999 ELSE
1000 l_ib_resource_flag := 'N';
1001 END IF;
1002
1003 l_param_resource_type := 'RS_INDIVIDUAL';
1004 -- Passing the auto_select_flag as 'N' bcoz if it is null the JTF API
1005 -- assigns it as 'Y' and always returns the first record. No Load Balancing
1006 -- is done. Made contracts_preferred_engineer as 'Y' for 11.5.9 according
1007 -- to whether contract_service_id is not null.
1008 -- From 11.5.9+, the contract_id, inventory_item_id and inventory_org_id
1009 -- are always passed as Null and the Load Balancing will be done for all
1010 -- the resources with or without skills.
1011 JTF_ASSIGN_PUB.Get_Assign_Resources
1012 ( p_api_version => 1.0,
1013 p_init_msg_list => FND_API.G_FALSE,
1014 p_commit => 'F',
1015 p_resource_id => l_group_id,
1016 p_resource_type => l_param_resource_type,
1017 p_role => NULL,
1018 p_no_of_resources => l_no_of_resources,
1019 p_auto_select_flag => 'N',
1020 p_ib_preferred_engineer => l_ib_resource_flag,
1021 p_contracts_preferred_engineer => l_contract_res_flag,
1022 p_contract_id => l_contract_service_id,
1023 p_customer_product_id => l_cust_prod_id,
1024 p_effort_duration => NULL,
1025 p_effort_uom => NULL,
1026 p_start_date => NULL,
1027 p_end_date => NULL,
1028 p_territory_flag => 'Y',
1029 p_calendar_flag => 'N',
1030 p_web_availability_flag => 'Y',
1031 p_filter_excluded_resource => 'Y',
1032 p_category_id => NULL,
1033 p_inventory_item_id => NULL,
1034 p_inventory_org_id => NULL,
1035 p_column_list => NULL,
1036 p_calling_doc_id => l_am_calling_doc_id,
1037 p_calling_doc_type => l_am_calling_doc_type,
1038 p_sr_rec => l_sr_am_rec,
1039 p_sr_task_rec => NULL,
1040 p_defect_rec => NULL,
1041 p_business_process_id => l_business_process_id,
1042 p_business_process_date => l_sr_rec.request_date,
1043 x_Assign_Resources_tbl => l_Assign_Owner_tbl,
1044 x_return_status => x_return_status,
1045 x_msg_count => x_msg_count,
1046 x_msg_data => x_msg_data
1047 );
1048
1049 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1050 p := l_Assign_Owner_tbl.FIRST ;
1051 IF (l_Assign_Owner_tbl.COUNT = 1 AND
1052 l_Assign_Owner_tbl(p).web_availability_flag = 'Y') THEN
1053 OPEN c_check_grp_res(l_group_id, l_Assign_Owner_tbl(p).resource_id);
1054 FETCH c_check_grp_res INTO l_ismember;
1055 CLOSE c_check_grp_res;
1056 IF (NVL(l_ismember, 'N') = 'Y') THEN
1057 x_resource_id := l_Assign_Owner_tbl(p).resource_id ;
1058 x_resource_type := l_Assign_Owner_tbl(p).resource_type ;
1059 x_territory_id := l_Assign_Owner_tbl(p).terr_id;
1060 END IF;
1061 END IF;
1062
1063 IF (l_Assign_Owner_tbl.COUNT > 1) THEN
1064 l_count := l_Assign_Owner_tbl.COUNT;
1065 l_index := l_Assign_Owner_tbl.FIRST;
1066 l_counter := l_Assign_Owner_tbl.FIRST ;
1067 WHILE l_index <= l_count
1068 LOOP
1069 l_ismember := 'N';
1070 IF (l_Assign_Owner_tbl(l_index).web_availability_flag = 'Y') THEN
1071 OPEN c_check_grp_res(l_group_id, l_Assign_Owner_tbl(l_index).resource_id);
1072 FETCH c_check_grp_res INTO l_ismember;
1073 CLOSE c_check_grp_res;
1074 IF (NVL(l_ismember, 'N') = 'Y') THEN
1075 l_resource_load_tbl(l_counter).resource_id :=
1076 l_Assign_Owner_tbl(l_index).resource_id;
1077 l_resource_load_tbl(l_counter).resource_type :=
1078 l_Assign_Owner_tbl(l_index).resource_type;
1079 l_resource_load_tbl(l_counter).support_site_id :=
1080 l_Assign_Owner_tbl(l_index).support_site_id;
1081 l_resource_load_tbl(l_counter).territory_id :=
1085 Null;
1082 l_Assign_Owner_tbl(l_index).terr_id;
1083 ELSE /* Start Bug : 6241796 */
1084 l_resource_load_tbl(l_counter).resource_id :=
1086 l_resource_load_tbl(l_counter).resource_type :=
1087 Null ;
1088 l_resource_load_tbl(l_counter).support_site_id :=
1089 Null ;
1090 l_resource_load_tbl(l_counter).territory_id :=
1091 Null ; /* End Bug : 6241796 */
1092 END IF;
1093 /* Start Bug : 6391261 */
1094 IF ( l_Counter = l_Assign_Owner_tbl.FIRST AND nvl(l_ismember, 'N') = 'N' ) Then
1095 null ;
1096 ELSE
1097 l_counter := l_counter + 1;
1098 END IF ;
1099 /* End Bug : 6391261 */
1100 END IF;
1101 l_index := l_index + 1;
1102 END LOOP;
1103
1104 IF (l_resource_load_tbl.COUNT > 1) THEN
1105 CS_ASSIGN_RESOURCE_PKG.Calculate_Load
1106 ( p_init_msg_list => p_init_msg_list,
1107 p_incident_id => p_incident_id,
1108 p_incident_type_id => p_service_request_rec.type_id,
1109 p_incident_severity_id => p_service_request_rec.severity_id,
1110 p_inv_item_id => p_service_request_rec.inventory_item_id,
1111 p_inv_org_id => p_service_request_rec.inventory_org_id,
1112 p_platform_org_id => p_service_request_rec.inv_platform_org_id,
1113 p_inv_cat_id => p_service_request_rec.category_id,
1114 p_platform_id => p_service_request_rec.platform_id,
1115 p_problem_code => p_service_request_rec.problem_code,
1116 p_contact_timezone_id => p_service_request_rec.time_zone_id,
1117 p_res_load_table => l_resource_load_tbl,
1118 x_return_status => l_cal_load_return_sts,
1119 x_resource_id => x_resource_id,
1120 x_resource_type => x_resource_type,
1121 x_msg_count => x_msg_count,
1122 x_msg_data => x_msg_data,
1123 x_territory_id => x_territory_id
1124 );
1125
1126 IF (l_cal_load_return_sts <> FND_API.G_RET_STS_SUCCESS) THEN
1127 /* due to TZ API error, but continue if resource is returned */
1128 IF (x_resource_id IS NOT NULL) THEN
1129 x_return_status := FND_API.G_RET_STS_SUCCESS;
1130 l_cal_load_return_sts := FND_API.G_RET_STS_SUCCESS;
1131 END IF;
1132 END IF;
1133
1134 IF(l_cal_load_return_sts <> FND_API.G_RET_STS_SUCCESS) THEN
1135 x_return_status := FND_API.G_RET_STS_ERROR ;
1136 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_OWNER');
1137 FND_MSG_PUB.Add;
1138 END IF;
1139 ELSE
1140 IF (l_resource_load_tbl.COUNT = 1) THEN
1141 l := l_resource_load_tbl.FIRST;
1142 x_resource_id := l_resource_load_tbl(l).resource_id;
1143 x_resource_type := l_resource_load_tbl(l).resource_type;
1144 x_territory_id := l_resource_load_tbl(l).territory_id;
1145 END IF;
1146 END IF; -- l_resource_load_tbl.COUNT >1
1147 -- x_territory_id := l_Assign_Owner_tbl(l).terr_id;
1148 END IF; -- l_Assign_Owner_tbl.COUNT > 1
1149 END IF ; -- Return status S
1150 END IF; -- l_incident_id and p_group_id is not null
1151 END Assign_Owner;
1152
1153 PROCEDURE Calculate_Load
1154 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1155 p_incident_id IN NUMBER,
1156 p_incident_type_id IN NUMBER,
1157 p_incident_severity_id IN NUMBER,
1158 p_inv_item_id IN NUMBER,
1159 p_inv_org_id IN NUMBER,
1160 p_inv_cat_id IN NUMBER,
1161 p_platform_org_id IN NUMBER,
1162 p_platform_id IN NUMBER,
1163 p_problem_code IN VARCHAR2,
1164 p_contact_timezone_id IN NUMBER,
1165 p_res_load_table IN OUT NOCOPY CS_ASSIGN_RESOURCE_PKG.LoadBalance_tbl_type,
1166 x_return_status OUT NOCOPY VARCHAR2,
1167 x_resource_id OUT NOCOPY NUMBER,
1168 x_resource_type OUT NOCOPY VARCHAR2,
1169 x_msg_count OUT NOCOPY NUMBER,
1170 x_msg_data OUT NOCOPY VARCHAR2,
1171 x_territory_id OUT NOCOPY NUMBER
1172
1173 ) IS
1174
1175 -- Define Local Variables
1176 l_resource_id NUMBER;
1177 l_resource_type VARCHAR2(30);
1178 l_support_site_id NUMBER;
1179 l_return_status VARCHAR2(1);
1180 l_incident_type_id NUMBER;
1181 l_incident_severity_id NUMBER;
1182 l_wt_prd_skill NUMBER;
1183 l_wt_plt_skill NUMBER;
1184 l_wt_pbm_skill NUMBER;
1185 l_wt_cat_skill NUMBER;
1186 l_wt_time_last_login NUMBER;
1187 l_wt_backlog_sev1 NUMBER;
1188 l_wt_backlog_sev2 NUMBER;
1189 l_wt_backlog_sev3 NUMBER;
1190 l_wt_backlog_sev4 NUMBER;
1191 l_wt_time_zone_lag NUMBER;
1192 l_res_load NUMBER;
1193 l_max_total_load NUMBER;
1194 l_tbl_index BINARY_INTEGER;
1195 i BINARY_INTEGER;
1196 l_count NUMBER;
1197 l_max_record_index BINARY_INTEGER;
1198 l_supp_timezone_id NUMBER;
1202 l_problem_code VARCHAR2(50) := p_problem_code;
1199 l_contact_timezone_id NUMBER := p_contact_timezone_id;
1200 l_time_lag NUMBER := NULL;
1201 l_time_lag_score NUMBER := 0;
1203 l_prod_skill NUMBER := NULL;
1204 l_plat_skill NUMBER := NULL;
1205 l_prob_skill NUMBER := NULL;
1206 l_cat_skill NUMBER := NULL;
1207 l_time_last_login NUMBER := NULL;
1208 l_backlog_sev1 NUMBER := NULL;
1209 l_backlog_sev2 NUMBER := NULL;
1210 l_backlog_sev3 NUMBER := NULL;
1211 l_backlog_sev4 NUMBER := NULL;
1212 l_imp_level NUMBER := NULL;
1213
1214 CURSOR c_load_wt(l_incident_type_id NUMBER,l_incident_severity_id NUMBER) IS
1215 SELECT product_skill_wt,platform_skill_wt,prob_code_skill_wt,category_skill_wt,
1216 last_login_time_wt,severity1_count_wt,severity2_count_wt,
1217 severity3_count_wt,severity4_count_wt,time_zone_diff_wt
1218 FROM cs_sr_load_balance_wt
1219 WHERE incident_type_id = l_incident_type_id
1220 AND incident_severity_id = l_incident_severity_id;
1221
1222 -- Added nvl(rs.category_id,0) = nvl(l_cat_id,0) by pnkalari on 06/70/2002.
1223 -- to filter correct resources when product category is null or is not null.
1224 -- Removed the Category Filter as Category is another qualifier.
1225 CURSOR c_prod_skill(l_prod_id NUMBER,l_prod_org_id NUMBER,l_resource_id NUMBER
1226 ) IS
1227 SELECT s.skill_level
1228 FROM jtf_rs_skill_levels_vl s,
1229 jtf_rs_resource_skills rs
1230 WHERE rs.resource_id = l_resource_id
1231 AND rs.product_id = l_prod_id
1232 AND rs.product_org_id = l_prod_org_id
1233 --AND NVL(rs.category_id,0) = NVL(l_cat_id,0)
1234 AND rs.skill_level_id = s.skill_level_id;
1235
1236 CURSOR c_plat_skill(l_platform_id NUMBER,l_platform_org_id NUMBER,
1237 l_resource_id NUMBER) IS
1238 SELECT s.skill_level
1239 FROM jtf_rs_skill_levels_vl s,
1240 jtf_rs_resource_skills rs
1241 WHERE rs.resource_id = l_resource_id
1242 AND rs.platform_id = l_platform_id
1243 AND rs.platform_org_id = l_platform_org_id
1244 AND rs.skill_level_id = s.skill_level_id;
1245
1246 CURSOR c_prob_skill(l_problem_code VARCHAR2,l_resource_id NUMBER) IS
1247 SELECT s.skill_level
1248 FROM jtf_rs_skill_levels_vl s,
1249 jtf_rs_resource_skills rs
1250 WHERE rs.resource_id = l_resource_id
1251 AND rs.problem_code = l_problem_code
1252 AND rs.skill_level_id = s.skill_level_id;
1253
1254 CURSOR c_cat_skill(l_category_id NUMBER, l_resource_id NUMBER) IS
1255 SELECT s.skill_level
1256 FROM jtf_rs_skill_levels_vl s,
1257 jtf_rs_resource_skills rs
1258 WHERE rs.resource_id = l_resource_id
1259 AND rs.category_id = l_category_id
1260 AND rs.skill_level_id = s.skill_level_id;
1261
1262
1263 CURSOR c_time_last_login(l_resource_id NUMBER) is
1264 SELECT ROUND(((SYSDATE - nvl( max(owner_assigned_time),to_date('1990-01-01','yyyy-mm-dd'))) *24 * 60),2)
1265 FROM cs_incidents_all_b
1266 WHERE incident_owner_id = l_resource_id;
1267
1268 CURSOR c_imp_level(p_inc_severity_id NUMBER) IS
1269 SELECT importance_level
1270 FROM cs_incident_severities_vl
1271 WHERE incident_subtype = 'INC'
1272 AND incident_severity_id = p_inc_severity_id;
1273
1274 CURSOR c_sev1_cnt(l_sev1_id NUMBER,l_resource_id NUMBER) IS
1275 SELECT COUNT(*)
1276 FROM cs_incidents_all_b
1277 WHERE incident_severity_id = l_sev1_id
1278 AND incident_owner_id = l_resource_id
1279 AND incident_status_id NOT IN (
1280 SELECT incident_status_id
1281 FROM cs_incident_statuses_vl
1282 WHERE incident_subtype = 'INC'
1283 AND close_flag = 'Y');
1284
1285 CURSOR c_sev2_cnt(l_sev2_id NUMBER ,l_resource_id NUMBER) IS
1286 SELECT COUNT(*)
1287 FROM cs_incidents_all_b
1288 WHERE incident_severity_id = l_sev2_id
1289 AND incident_owner_id = l_resource_id
1290 AND incident_status_id NOT IN (
1291 SELECT incident_status_id
1292 FROM cs_incident_statuses_vl
1293 WHERE incident_subtype = 'INC'
1294 AND close_flag = 'Y');
1295
1296 CURSOR c_sev3_cnt(l_sev3_id NUMBER,l_resource_id NUMBER) IS
1297 SELECT COUNT(*)
1298 FROM cs_incidents_all_b
1299 WHERE incident_severity_id = l_sev3_id
1300 AND incident_owner_id = l_resource_id
1301 AND incident_status_id NOT IN (
1302 select incident_status_id
1303 FROM cs_incident_statuses_vl
1304 WHERE incident_subtype = 'INC'
1305 AND close_flag = 'Y');
1306
1307 CURSOR c_sev4_cnt(l_sev4_id NUMBER,l_resource_id NUMBER) IS
1308 SELECT COUNT(*)
1309 FROM cs_incidents_all_b
1310 WHERE incident_severity_id = l_sev4_id
1311 AND incident_owner_id = l_resource_id
1312 AND incident_status_id NOT IN (
1313 SELECT incident_status_id
1314 FROM cs_incident_statuses_vl
1315 WHERE incident_subtype = 'INC'
1316 AND close_flag = 'Y');
1317
1318 CURSOR c_res_time_zone(p_resource_id NUMBER) IS
1319 SELECT time_zone
1320 FROM jtf_rs_resource_extns
1321 WHERE resource_id = p_resource_id;
1322
1323 BEGIN
1324
1325 -- Initialize API return status to success
1326 x_return_status := FND_API.G_RET_STS_SUCCESS;
1327
1328 -- Assigning type_id and severity_id to local variables so as to
1329 -- find the LB weights.
1333 FETCH c_load_wt INTO
1330 l_incident_type_id := p_incident_type_id;
1331 l_incident_severity_id := p_incident_severity_id;
1332 OPEN c_load_wt(l_incident_type_id,l_incident_severity_id);
1334 l_wt_prd_skill, l_wt_plt_skill, l_wt_pbm_skill, l_wt_cat_skill,
1335 l_wt_time_last_login, l_wt_backlog_sev1, l_wt_backlog_sev2 ,
1336 l_wt_backlog_sev3, l_wt_backlog_sev4, l_wt_time_zone_lag;
1337 IF (c_load_wt%NOTFOUND) THEN
1338 l_wt_prd_skill := 0;
1339 l_wt_plt_skill := 0;
1340 l_wt_pbm_skill := 0;
1341 l_wt_cat_skill := 0;
1342 l_wt_time_last_login := 0;
1343 l_wt_backlog_sev1 := 0;
1344 l_wt_backlog_sev2 := 0;
1345 l_wt_backlog_sev3 := 0;
1346 l_wt_backlog_sev4 := 0;
1347 l_wt_time_zone_lag := 0;
1348 END IF;
1349 CLOSE c_load_wt;
1350
1351 l_tbl_index := p_res_load_table.FIRST;
1352 l_count := p_res_load_table.COUNT;
1353 WHILE l_tbl_index <= l_count
1354 LOOP
1355 l_resource_id := p_res_load_table(l_tbl_index).resource_id;
1356 l_resource_type := p_res_load_table(l_tbl_index).resource_type;
1357 l_support_site_id := p_res_load_table(l_tbl_index).support_site_id;
1358 l_supp_timezone_id := NULL;
1359 l_time_lag := NULL;
1360 l_time_lag_score := NULL;
1361 l_res_load := NULL;
1362
1363 IF (p_inv_item_id IS NOT NULL AND p_inv_org_id IS NOT NULL AND
1364 l_resource_id IS NOT NULL AND NVL(l_wt_prd_skill,0) >0 ) THEN
1365 OPEN c_prod_skill(p_inv_item_id,p_inv_org_id,l_resource_id);
1366 FETCH c_prod_skill INTO l_prod_skill;
1367 IF (c_prod_skill%NOTFOUND) THEN
1368 l_prod_skill := NULL;
1369 END IF;
1370 CLOSE c_prod_skill;
1371 END IF;
1372
1373 IF (p_platform_id IS NOT NULL AND p_platform_org_id IS NOT NULL AND
1374 l_resource_id IS NOT NULL AND NVL(l_wt_plt_skill,0)>0) THEN
1375 OPEN c_plat_skill(p_platform_id,p_platform_org_id,l_resource_id);
1376 FETCH c_plat_skill INTO l_plat_skill;
1377 IF (c_plat_skill%NOTFOUND) THEN
1378 l_plat_skill := NULL;
1379 END IF;
1380 CLOSE c_plat_skill;
1381 END IF;
1382
1383 IF (l_problem_code IS NOT NULL AND
1384 l_resource_id IS NOT NULL AND NVL(l_wt_pbm_skill,0)>0) THEN
1385 OPEN c_prob_skill(l_problem_code,l_resource_id);
1386 FETCH c_prob_skill INTO l_prob_skill;
1387 IF (c_prob_skill%NOTFOUND) THEN
1388 l_prob_skill := NULL;
1389 END IF;
1390 CLOSE c_prob_skill;
1391 END IF;
1392
1393 IF (p_inv_cat_id IS NOT NULL AND
1394 l_resource_id IS NOT NULL AND NVL(l_wt_cat_skill,0) >0) THEN
1395 OPEN c_cat_skill(p_inv_cat_id, l_resource_id);
1396 FETCH c_cat_skill INTO l_cat_skill;
1397 IF (c_cat_skill%NOTFOUND) THEN
1398 l_cat_skill := NULL;
1399 END IF;
1400 CLOSE c_cat_skill;
1401 END IF;
1402
1403 -- Changed the if condition to calculate the count of SRs if
1404 -- l_resource_id is not null 11.5.9
1405 IF (l_resource_id IS NOT NULL) THEN
1406 IF (NVL(l_wt_time_last_login,0)<>0) THEN
1407 -- for every resource get the backlog of severity 1,2,3,4 SR's
1408 OPEN c_time_last_login(l_resource_id);
1409 FETCH c_time_last_login INTO l_time_last_login;
1410 IF (c_time_last_login%NOTFOUND) THEN
1411 l_time_last_login := NULL;
1412 END IF;
1413 CLOSE c_time_last_login;
1414 END IF;
1415
1416 OPEN c_imp_level(l_incident_severity_id);
1417 FETCH c_imp_level INTO l_imp_level;
1418 IF (c_imp_level%NOTFOUND) THEN
1419 l_imp_level := 0;
1420 END IF;
1421 CLOSE c_imp_level;
1422
1423 IF (l_imp_level = 1 AND NVL(l_wt_backlog_sev1,0) <> 0) THEN
1424 OPEN c_sev1_cnt(l_incident_severity_id,l_resource_id);
1425 FETCH c_sev1_cnt INTO l_backlog_sev1;
1426 IF (c_sev1_cnt%NOTFOUND) THEN
1427 l_backlog_sev1 := NULL;
1428 END IF;
1429 CLOSE c_sev1_cnt;
1430 ELSIF (l_imp_level = 2 AND NVL(l_wt_backlog_sev2,0) <> 0) THEN
1431 OPEN c_sev2_cnt(l_incident_severity_id,l_resource_id);
1432 FETCH c_sev2_cnt INTO l_backlog_sev2;
1433 IF (c_sev2_cnt%NOTFOUND) THEN
1434 l_backlog_sev2 := NULL;
1435 END IF;
1436 CLOSE c_sev2_cnt;
1437 ELSIF (l_imp_level = 3 AND NVL(l_wt_backlog_sev3,0) <> 0) THEN
1438 OPEN c_sev3_cnt(l_incident_severity_id,l_resource_id);
1439 FETCH c_sev3_cnt INTO l_backlog_sev3;
1440 IF (c_sev3_cnt%NOTFOUND) THEn
1441 l_backlog_sev3 := NULL;
1442 END IF;
1443 CLOSE c_sev3_cnt;
1444 ELSIF (l_imp_level = 4 AND NVL(l_wt_backlog_sev4,0) <> 0) THEN
1445 OPEN c_sev4_cnt(l_incident_severity_id,l_resource_id);
1446 FETCH c_sev4_cnt INTO l_backlog_sev4;
1447 IF (c_sev4_cnt%NOTFOUND) THEN
1448 l_backlog_sev4 := NULL;
1449 END IF;
1450 CLOSE c_sev4_cnt;
1451 ELSE
1452 l_backlog_sev1 := NULL;
1453 l_backlog_sev2 := NULL;
1454 l_backlog_sev3 := NULL;
1455 l_backlog_sev4 := NULL;
1456 END IF;
1457
1461 OPEN c_res_time_zone(l_resource_id);
1458 END IF; -- l_resource_id is not null
1459
1460 IF (l_support_site_id IS NOT NULL AND NVL(l_wt_time_zone_lag,0)<>0) THEN
1462 FETCH c_res_time_zone INTO l_supp_timezone_id;
1463 IF (c_res_time_zone%NOTFOUND) THEN
1464 l_supp_timezone_id := NULL;
1465 END IF;
1466 CLOSE c_res_time_zone;
1467 END IF;
1468
1469 IF (l_contact_timezone_id IS NOT NULL AND
1470 l_supp_timezone_id IS NOT NULL ) THEN
1471 IF (l_contact_timezone_id <> l_supp_timezone_id) THEN
1472 CS_TZ_GET_DETAILS_PVT.GET_LEADTIME
1473 (1.0,
1474 'T',
1475 l_supp_timezone_id,
1476 l_contact_timezone_id,
1477 l_time_lag,
1478 x_return_status,
1479 x_msg_count,
1480 x_msg_data);
1481 ELSE
1482 l_time_lag := 0;
1483 END IF;
1484
1485 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1486 FND_MESSAGE.Set_Name('CS', 'CS_TZ_API_ERR');
1487 FND_MSG_PUB.Add;
1488 EXIT ;
1489 END IF;
1490 IF ( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1491 l_time_lag := abs(l_time_lag);
1492 ELSE
1493 l_time_lag := 0;
1494 END IF;
1495 ELSE /* l_contact_timezone_id or l_supp_timezone_id is missing */
1496 l_time_lag := 0;
1497 END IF; /* l_contact_timezone_id or l_supp_timezone_id is missing */
1498
1499 -- New formula for time lag for OSS , as given in enhancement 2093850
1500 -- Added weight multiplication and support timezone id check for Bug# 3526252
1501 IF (l_supp_timezone_id IS NULL) THEN
1502 l_time_lag_score := 0;
1503 ELSE
1504 l_time_lag_score := ROUND(2.77 - (l_time_lag/4)) * NVL(l_wt_time_zone_lag,0);
1505 END IF;
1506
1507 -- calculate total load for each
1508 -- Added nvl for all the weights 11.5.9
1509 l_res_load :=
1510 ((NVL(l_prod_skill,0) * NVL(l_wt_prd_skill,0)) +
1511 (NVL(l_plat_skill,0) * NVL(l_wt_plt_skill,0)) +
1512 (NVL(l_prob_skill,0) * NVL(l_wt_pbm_skill,0)) +
1513 (NVL(l_cat_skill,0) * NVL(l_wt_cat_skill,0)) +
1514 (NVL(l_time_last_login,0) * NVL(l_wt_time_last_login,0)) +
1515 (NVL(l_backlog_sev1,0) * NVL(l_wt_backlog_sev1,0)) +
1516 (NVL(l_backlog_sev2,0) * NVL(l_wt_backlog_sev2,0)) +
1517 (NVL(l_backlog_sev3,0) * NVL(l_wt_backlog_sev3,0)) +
1518 (NVL(l_backlog_sev4,0) * NVL(l_wt_backlog_sev4,0)) +
1519 (NVL(l_time_lag_score,0) * NVL(l_wt_time_zone_lag,0)));
1520 -- copy values into table
1521 p_res_load_table(l_tbl_index).product_skill_level := l_prod_skill;
1522 p_res_load_table(l_tbl_index).platform_skill_level := l_plat_skill;
1523 p_res_load_table(l_tbl_index).pbm_code_skill_level := l_prob_skill;
1524 p_res_load_table(l_tbl_index).category_skill_level := l_cat_skill;
1525 p_res_load_table(l_tbl_index).time_since_last_login := l_time_last_login;
1526 p_res_load_table(l_tbl_index).backlog_sev1 := l_backlog_sev1;
1527 p_res_load_table(l_tbl_index).backlog_sev2 := l_backlog_sev2;
1528 p_res_load_table(l_tbl_index).backlog_sev3 := l_backlog_sev3;
1529 p_res_load_table(l_tbl_index).backlog_sev4 := l_backlog_sev4;
1530 p_res_load_table(l_tbl_index).time_zone_lag := l_time_lag;
1531 p_res_load_table(l_tbl_index).total_load := l_res_load;
1532
1533 l_tbl_index := l_tbl_index + 1;
1534
1535 END LOOP; /* l_tbl_index <= l_count loop */
1536
1537 -- After the load for all resources are calculated find the
1538 -- resource with the max load.This is the winning resource to
1539 -- be returned
1540 -- If timezone API does not give error then proceed
1541 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1542 -- Changed index from i=0 to i=1 by pnkalari on 06/11/2002.
1543 --i := 0;
1544 -- Added this because if all the resource loads are 0,
1545 -- l_max_record_index is always the first resource.
1546 l_max_record_index := p_res_load_table.FIRST;
1547
1548 -- Bug 4907196 . The value of l_max_total_load is changed to -9999999 from 0.
1549 -- Total load can have negative values hence the initial value shouldn't be 0
1550 l_max_total_load := -999999999;
1551
1552 /* Commented out for Bug# 4017138
1553 FOR i IN 1..p_res_load_table.COUNT
1554 LOOP
1555 IF (i = 1 ) THEN
1556 l_max_total_load := p_res_load_table(i).total_load ;
1557 l_max_record_index := i ;
1558 ELSE
1559 IF (p_res_load_table(i).total_load > l_max_total_load) THEN
1560 l_max_total_load := p_res_load_table(i).total_load;
1561 l_max_record_index := i;
1562 END IF;
1563 END IF;
1564 END LOOP ;
1565 */
1566 IF (p_res_load_table.COUNT > 0) THEN
1567 FOR i IN p_res_load_table.FIRST..p_res_load_table.LAST LOOP
1568 IF ( p_res_load_table.COUNT = 1 ) THEN
1569 l_max_total_load := p_res_load_table(i).total_load ;
1570 l_max_record_index := i ;
1571 ELSE
1572 IF (p_res_load_table(i).total_load > l_max_total_load) THEN
1573 l_max_total_load := p_res_load_table(i).total_load;
1574 l_max_record_index := i;
1575 END IF;
1576 END IF;
1577 END LOOP ;
1578 END IF;
1579
1580 x_resource_id := p_res_load_table(l_max_record_index).resource_id;
1581 x_resource_type := p_res_load_table(l_max_record_index).resource_type;
1582 x_territory_id := p_res_load_table(l_max_record_index).territory_id;
1583
1584 IF (x_resource_id IS NOT NULL) THEN
1585 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1586 ELSIF (x_resource_id IS NULL) THEN
1587 x_return_status := FND_API.G_RET_STS_ERROR;
1588 END IF;
1589
1590 ELSE /* x_return_status = FND_API.G_RET_STS_SUCCESS */
1591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1592 END IF; /* x_return_status = FND_API.G_RET_STS_SUCCESS */
1593
1594 END Calculate_Load;
1595
1596 END CS_ASSIGN_RESOURCE_PKG;