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