[Home] [Help]
PACKAGE BODY: APPS.CS_ASSIGN_RESOURCE_CON_PKG
Source
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_ASSIGN_RESOURCE_CON_PKG';
1 PACKAGE BODY CS_ASSIGN_RESOURCE_CON_PKG as
2 /* $Header: csvconab.pls 120.11.12020000.6 2013/03/22 05:05:27 nic ship $ */
3
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')
104 OR p_group3_id IS NOT NULL
101 AND NVL(owner_assigned_flag,'N') <> 'Y'
102 AND (((p_group1_id IS NOT NULL
103 OR p_group2_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
212 p_commit => fnd_api.g_false,
209 ( p_api_name => 'Assign_ServiceRequest_Main',
210 p_api_version => 1.0 ,
211 p_init_msg_list => 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;
341 l_sr_update_out_rec CS_ServiceRequest_pvt.sr_update_out_rec_type;
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;
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
425 (p_init_msg_list => p_init_msg_list,
426 p_commit => p_commit,
427 p_incident_id => p_incident_id,
428 p_param_resource_type => l_param_resource_type,
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);
468 END IF; /* group_type <> RS_TEAM */
465 l_main_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466 END IF;
467 END IF;
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);
531 l_main_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532 ELSE
533 l_update_own_flag := 'Y';
534 END IF;
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',
588 x_msg_data => l_msg_data,
585 p_validation_level => fnd_api.g_valid_level_none,
586 x_return_status => l_return_status,
587 x_msg_count => l_msg_count,
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;
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;
653 l_inv_org_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
678 l_cs_sr_chk_res_cal_avl VARCHAR2(1) ; --gasankar Calendar check feature added
679
680 l_start_date Date ;
681 l_end_date Date ;
682
683
684 l_day_week VARCHAR2(10) ;
685 l_time_day VARCHAR2(10) ;
686 l_territory_flag VARCHAR2(3); -- added by nic
687
688 -- List of Cursors
689 CURSOR c_inc_address(p_incident_location_id NUMBER) is
690 SELECT country,province,state,city,postal_code,county
691 FROM hz_locations
692 WHERE location_id = p_incident_location_id;
693
694 -- Added for 11.5.10 Incident Location Type ER
695 CURSOR c_inc_party_site_address(p_party_site_id NUMBER) IS
696 SELECT location_id FROM hz_party_sites
697 WHERE party_site_id = p_party_site_id;
698
699 CURSOR C_CONTRACT(l_contract_service_id NUMBER)is
700 SELECT TO_NUMBER(object1_id1), TO_NUMBER(object1_id2)
701 FROM okc_k_items
702 WHERE cle_id = l_contract_service_id;
703
704 CURSOR C_CLASS_CODE(l_party_id NUMBER,l_cust_category VARCHAR2) IS
705 SELECT class_code
706 FROM hz_code_assignments
707 WHERE owner_table_name = 'HZ_PARTIES'
708 AND owner_table_id = l_party_id
709 AND class_category = l_cust_category;
710
711 --Bug 5255184 Modified the c_area_code query
712 CURSOR c_area_code IS
713 SELECT hzp.phone_area_code
714 FROM hz_contact_points hzp
715 WHERE hzp.contact_point_id = c_customer_phone_id;
716
720 WHERE party_id = p_customer_id;
717 CURSOR c_cust_det(p_customer_id NUMBER) IS
718 SELECT employees_total, party_name
719 FROM hz_parties
721
722 BEGIN
723 -- Initialize API return status to success
724 x_return_status := FND_API.G_RET_STS_SUCCESS;
725
726 l_incident_id := p_incident_id;
727 l_cust_prod_id := l_sr_rec.customer_product_id;
728
729 -- if incident_id is not null proceed, else exit API
730 -- group type must have a value - default is RS_GROUP, passed by caller
731 IF (l_incident_id IS NOT NULL) AND (p_group_type IS NOT NULL) THEN
732 l_resource_type := p_group_type;
733
734 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_AUTO_ASSIGN') = 'YES') THEN
735 SELECT business_process_id INTO l_business_process_id
736 FROM cs_incident_types
737 WHERE incident_type_id = l_sr_rec.type_id;
738 END IF;
739
740 -- 12.1.2 Enhancement
741 Begin
742 SELECT to_char(sysdate, 'd'), to_char(sysdate, 'hh24:mi')
743 INTO l_day_week, l_time_day
744 FROM cs_incidents_all_b
745 WHERE incident_id = l_incident_id ;
746 Exception
747 When Others then
748 l_time_day := null ;
749 l_day_week := null ;
750 End ;
751
752 l_sr_am_rec.DAY_OF_WEEK := l_day_week ;
753 l_sr_am_rec.TIME_OF_DAY := l_time_day ;
754 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
755 -- Assigning component and subcomponent id directly to the am rec
756
757 IF (l_cust_prod_id IS NOT NULL) THEN
758 l_sr_am_rec.item_component := l_sr_rec.cp_component_id;
759 l_sr_am_rec.item_subcomponent := l_sr_rec.cp_subcomponent_id;
760 ELSE
761 l_sr_am_rec.item_component := l_sr_rec.inv_component_id;
762 l_sr_am_rec.item_subcomponent := l_sr_rec.inv_subcomponent_id;
763 END IF;
764
765
766 IF (p_service_request_rec.incident_location_id IS NOT NULL) THEN
767 -- Added in 11.5.9 ER# 2527850
768 IF (p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE') THEN
769 OPEN c_inc_party_site_address(p_service_request_rec.incident_location_id);
770 FETCH c_inc_party_site_address INTO l_location_id;
771 IF (c_inc_party_site_address%NOTFOUND) THEN
772 l_location_id := NULL;
773 END IF;
774 CLOSE c_inc_party_site_address;
775 END IF;
776 OPEN c_inc_address(l_location_id);
777 FETCH c_inc_address INTO l_country,l_province,l_state,l_city,
778 l_postal_code, l_county;
779 IF (c_inc_address%NOTFOUND) THEN
780 NULL;
781 END IF;
782 l_sr_am_rec.country := l_country;
783 l_sr_am_rec.city := l_city;
784 l_sr_am_rec.postal_code := l_postal_code;
785 l_sr_am_rec.state := l_state;
786 l_sr_am_rec.province := l_province;
787 l_sr_am_rec.county := l_county;
788 CLOSE c_inc_address;
789 ELSE
790 l_sr_am_rec.country := p_service_request_rec.incident_country;
791 l_sr_am_rec.city := p_service_request_rec.incident_city;
792 l_sr_am_rec.postal_code := p_service_request_rec.incident_postal_code;
793 l_sr_am_rec.state := p_service_request_rec.incident_state;
794 l_sr_am_rec.province := p_service_request_rec.incident_province;
795 l_sr_am_rec.county := p_service_request_rec.incident_county;
796 END IF;
797
798 --Bug 5255184 Modified the c_area_code
799 OPEN c_area_code;
800 FETCH c_area_code INTO l_area_code;
801 IF (c_area_code%NOTFOUND) THEN
802 l_area_code := NULL;
803 END IF;
804 CLOSE c_area_code;
805
806 OPEN c_cust_det(l_sr_rec.customer_id);
807 FETCH c_cust_det INTO l_no_of_employees, l_party_name;
808 IF (c_cust_det%NOTFOUND) THEN
809 l_no_of_employees := NULL;
810 END IF;
811 CLOSE c_cust_det;
812
813 l_sr_am_rec.service_request_id := l_incident_id;
814 l_sr_am_rec.party_id := l_sr_rec.customer_id;
815 l_sr_am_rec.incident_type_id := l_sr_rec.type_id;
816 l_sr_am_rec.incident_severity_id := l_sr_rec.severity_id;
817 l_sr_am_rec.incident_urgency_id := l_sr_rec.urgency_id;
818 l_sr_am_rec.problem_code := l_sr_rec.problem_code;
819 l_sr_am_rec.incident_status_id := l_sr_rec.status_id;
820 l_sr_am_rec.platform_id := l_sr_rec.platform_id;
821 l_sr_am_rec.inventory_item_id := l_sr_rec.inventory_item_id;
822 l_sr_am_rec.sr_creation_channel := l_sr_rec.sr_creation_channel;
823 l_sr_am_rec.area_code := l_area_code;
824 --l_sr_am_rec.customer_site_id := l_sr_rec.customer_site_id;
825 --l_sr_am_rec.support_site_id := l_sr_rec.site_id;
826 l_sr_am_rec.squal_char12 := l_sr_rec.problem_code;
827 l_sr_am_rec.squal_char13 := l_sr_rec.comm_pref_code;
828 l_sr_am_rec.squal_char20 := l_sr_rec.cust_pref_lang_code;
829 l_sr_am_rec.squal_num12 := l_sr_rec.platform_id;
830 l_sr_am_rec.squal_num13 := l_sr_rec.inv_platform_org_id;
831 l_sr_am_rec.squal_num14 := l_sr_rec.category_id;
832 l_sr_am_rec.squal_num15 := l_sr_rec.inventory_item_id;
833 l_sr_am_rec.squal_num16 := l_sr_rec.inventory_org_id;
834 l_sr_am_rec.squal_num17 := NULL;
835 l_sr_am_rec.squal_num30 := l_sr_rec.language_id;
836 l_sr_am_rec.squal_char21 := l_sr_rec.coverage_type;
837 l_sr_am_rec.num_of_employees := l_no_of_employees;
838 l_sr_am_rec.comp_name_range := l_party_name;
839
840 IF (p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE') THEN -- Added by nic
841 l_sr_am_rec.party_site_id := p_service_request_rec.site_number;
842 ELSE
843 L_SR_AM_REC.PARTY_SITE_ID := NULL ;
844 End If ;
845
849 FETCH C_CONTRACT INTO l_inv_item_id,l_inv_org_id;
846 --Contract Item and Org dtls
847 IF (l_contract_service_id is not null) then
848 OPEN C_CONTRACT(l_contract_service_id);
850 IF (C_CONTRACT%NOTFOUND) THEN
851 NULL;
852 END IF;
853 CLOSE C_CONTRACT;
854 END IF;
855 l_sr_am_rec.squal_num18 := l_inv_item_id;
856 l_sr_am_rec.squal_num19 := l_inv_org_id;
857
858 /* Waiting for JTA patch for their sql change so comment out for now
859 --VIP Customer Code
860 IF (l_party_id IS NOT NULL) THEN
861 OPEN C_CLASS_CODE(l_party_id,l_cust_category);
862 FETCH C_CLASS_CODE INTO l_class_code;
863 IF (C_CLASS_CODE%NOTFOUND) THEN
864 NULL;
865 END IF;
866 CLOSE C_CLASS_CODE;
867 END IF;
868 l_sr_am_rec.squal_char11 := l_class_code;
869 */
870 l_class_code := null;
871
872 -- populate the form parameters
873 l_am_calling_doc_id := l_incident_id;
874
875 -- Passing the auto_select_flag as 'N' bcoz if it is null the JTF API
876 -- assigns it as 'Y' and always returns the first record. No Load Balancing
877 -- is done.
878 -- If customer product id is not null, then set ib_preferred_resource_flag
879 -- to 'Y'.If contract line id is not null, then set
880 -- contract_preferred_resource flag to 'Y'.
881 IF (l_contract_service_id IS NOT NULL) THEN
882 IF (FND_PROFILE.VALUE('CS_SR_CONTRACT_GROUP')= 'Y') THEN --Added By Nic
883 l_contract_res_flag := 'Y';
884 ELSE
885 l_contract_res_flag := 'N';
886 END IF;
887 END IF;
888 IF (l_cust_prod_id IS NOT NULL) THEN
889 l_ib_resource_flag := 'Y';
890 ELSE
891 l_ib_resource_flag := 'N';
892 END IF;
893 IF (FND_PROFILE.VALUE('CS_SR_TERRITORY_GROUP') = 'Y') THEN -- Added by nic
894 l_territory_flag :='Y';
895 ELSE
896 L_TERRITORY_FLAG :='N';
897 END IF;
898
899
900
901 FND_PROFILE.Get('CS_SR_CHK_RES_CAL_AVL', l_cs_sr_chk_res_cal_avl); --gasankar Calendar check feature added
902 If nvl(l_cs_sr_chk_res_cal_avl, 'N') <> 'N' Then
903 l_start_date := sysdate ;
904 l_end_date := sysdate ;
905 End If ;
906
907 JTF_ASSIGN_PUB.GET_Assign_Resources
908 ( p_api_version => 1.0,
909 p_init_msg_list => 'T',
910 p_commit => 'F',
911 p_resource_id => NULL,
912 p_resource_type => l_resource_type,
913 p_role => NULL,
914 p_no_of_resources => l_no_of_resources,
915 p_auto_select_flag => NULL,
916 p_contracts_preferred_engineer => nvl(l_contract_res_flag,'N'),
917 p_ib_preferred_engineer => nvl(l_ib_resource_flag,'N'),
918 p_effort_duration => NULL,
919 p_effort_uom => NULL,
920 p_start_date => l_start_date,
921 p_end_date => l_end_date,
922 p_territory_flag => nvl(l_territory_flag,'N'),
923 p_calendar_flag => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
924 p_calendar_check => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
925 p_web_availability_flag => 'Y',
926 p_filter_excluded_resource => 'Y',
927 p_category_id => NULL,
928 p_inventory_item_id => NULL,
929 p_inventory_org_id => NULL,
930 p_column_list => NULL,
931 p_calling_doc_id => NULL,
932 p_calling_doc_type => 'SR',
933 p_sr_rec => l_sr_am_rec,
934 p_sr_task_rec => NULL,
935 p_defect_rec => NULL,
936 p_business_process_id => l_business_process_id,
937 p_business_process_date => l_sr_rec.request_date,
938 x_Assign_Resources_tbl => l_Assign_Groups_tbl,
939 x_return_status => x_return_status,
940 x_msg_count => x_msg_count,
941 x_msg_data => x_msg_data
942 );
943
944 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
945 IF (l_Assign_Groups_tbl.COUNT > 0) THEN
946 CS_ASSIGN_RESOURCE_PKG.Get_Sup_Usage_Group(
947 l_Assign_Groups_tbl, x_resource_id, x_territory_id);
948 END IF;
949 END IF;
950
951 END IF; /* l_incident_id is not null */
952 END Assign_Group;
953 /*==========================================================================================+
954 ==
955 == Procedure name : Assign_Owner
956 == Parameters :
957 == IN : event VARCHAR2
958 == OUT : none.
959 ==
960 == Description : This Procedure returns the individual Owner from the Group
961 == returned by the Assign_Group Procedure.
962 == Modification History:
963 ==
964 == Date Name Desc
965 == ---------- --------- ---------------------------------------------
966 == 08/02/2007 GASANKAR Bug 6241796 Fixed
967 == Initializing p_res_load_table(l_tbl_index).resource_type ,
968 == resource_id if the resource is not belonging to a group.
969 == 07/09/2007 GASANKAR Bug 6391261 Fixed
970 == First record of the p_res_load_table is not been left blank, so
971 == that contract preferred resource will work properly.
972 ===========================================================================================*/
973 PROCEDURE Assign_Owner
974 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
975 p_commit IN VARCHAR2 := fnd_api.g_false,
976 p_incident_id IN NUMBER,
980 x_return_status OUT NOCOPY VARCHAR2,
977 p_param_resource_type IN VARCHAR2,
978 p_group_id IN NUMBER,
979 p_service_request_rec IN CS_ServiceRequest_pvt.service_request_rec_type,
981 x_resource_id OUT NOCOPY NUMBER,
982 x_resource_type OUT NOCOPY VARCHAR2,
983 x_territory_id OUT NOCOPY NUMBER,
984 x_msg_count OUT NOCOPY NUMBER,
985 x_msg_data OUT NOCOPY VARCHAR2
986 ) IS
987
988 --profiles variables
989 l_web_availability_check VARCHAR2(1);
990 l_category_set_id NUMBER;
991 l_platform_catg_set_id NUMBER;
992 l_api_name CONSTANT VARCHAR2(30) := 'Assign_ServiceRequest_Main';
993 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
994 l_msg VARCHAR2(2000) ;
995 -- input and output data structures
996 l_Assign_Owner_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type ;
997 l_sr_am_rec JTF_ASSIGN_PUB.JTF_Serv_req_rec_type;
998 l_resource_load_tbl CS_ASSIGN_RESOURCE_CON_PKG.LoadBalance_tbl_type;
999 l_index BINARY_INTEGER;
1000 l_count NUMBER;
1001 p NUMBER;
1002 l NUMBER;
1003 l_cal_load_return_sts VARCHAR2(1) := null;
1004 l_sr_rec CS_ServiceRequest_pvt.service_request_rec_type DEFAULT p_service_request_rec;
1005 --qualifier values
1006 l_incident_id NUMBER := p_incident_id;
1007 l_contract_service_id NUMBER := p_service_request_rec.contract_service_id;
1008 l_inv_item_id NUMBER := NULL;
1009 l_inv_org_id NUMBER := NULL;
1010 l_inv_category_id NUMBER := NULL ;
1011 l_party_id NUMBER := p_service_request_rec.customer_id;
1012 l_group_id NUMBER := p_group_id;
1013 l_cust_prod_id NUMBER := p_service_request_rec.customer_product_id;
1014 l_location_id NUMBER := p_service_request_rec.incident_location_id;
1015 l_class_code VARCHAR2(30) := NULL;
1016 l_cust_category VARCHAR2(30) := NULL;
1017 l_country VARCHAR2(60) := NULL;
1018 l_province VARCHAR2(60) := NULL;
1019 l_postal_code VARCHAR2(60) := NULL;
1020 l_city VARCHAR2(60) := NULL;
1021 l_state VARCHAR2(60) := NULL;
1022 l_county VARCHAR2(60) := NULL;
1023 l_party_name VARCHAR2(360) := NULL;
1024 l_web_availability_flag VARCHAR2(1) := NULL;
1025 l_am_calling_doc_type VARCHAR2(2) := 'SR';
1026 l_ismember VARCHAR2(1) := 'N';
1027 l_param_resource_type VARCHAR2(30) := p_param_resource_type;
1028 l_am_calling_doc_id NUMBER := NULL;
1029 l_no_of_resources NUMBER := NULL;
1030 l_no_of_employees NUMBER := NULL;
1031 l_product_skill_level NUMBER;
1032 l_counter NUMBER;
1033 l_cat_wt NUMBER;
1034 l_prod_wt NUMBER;
1035 l_prob_wt NUMBER;
1036 l_business_process_id NUMBER;
1037 l_area_code VARCHAR2(60);
1038 l_contract_res_flag VARCHAR2(3);
1039 l_ib_resource_flag VARCHAR2(3);
1040 l_prod_skill_check VARCHAR2(3);
1041
1042 l_day_week VARCHAR2(10) ;
1043 l_time_day VARCHAR2(10) ;
1044
1045 l_cs_sr_chk_res_cal_avl VARCHAR2(1) ; --gasankar Calendar check feature added
1046
1047 l_start_date Date ;
1048 l_end_date Date ;
1049 l_territory_flag VARCHAR2(3);-- Added by nic
1050
1051 c_customer_phone_id NUMBER := p_service_request_rec.customer_phone_id;
1052 -- List of Cursors
1053 CURSOR c_inc_address(p_incident_location_id NUMBER) IS
1054 SELECT country,province,state,city,postal_code,county
1055 FROM hz_locations
1056 WHERE location_id = p_incident_location_id;
1057
1058 -- Added for 11.5.10 Incident Location Type ER
1059 CURSOR c_inc_party_site_address(p_party_site_id NUMBER) IS
1060 SELECT location_id FROM hz_party_sites
1061 WHERE party_site_id = p_party_site_id;
1062
1063 CURSOR c_contract(l_contract_service_id NUMBER) IS
1064 SELECT TO_NUMBER(object1_id1), TO_NUMBER(object1_id2)
1065 FROM okc_k_items
1066 WHERE cle_id = l_contract_service_id;
1067
1068 CURSOR c_class_code(l_party_id NUMBER,l_cust_category VARCHAR2) is
1069 SELECT class_code
1070 FROM hz_code_assignments
1071 WHERE owner_table_name = 'HZ_PARTIES'
1072 AND owner_table_id = l_party_id
1073 AND class_category = l_cust_category;
1074
1075 --Added in 11.5.9
1076 CURSOR c_load_wt(l_incident_type_id NUMBER,l_incident_severity_id NUMBER) IS
1077 select product_skill_wt,prob_code_skill_wt, category_skill_wt
1078 from cs_sr_load_balance_wt
1079 where incident_type_id = l_incident_type_id
1080 and incident_severity_id = l_incident_severity_id;
1081
1082 -- Added in 11.5.9. Fetch phone area code for the customer.
1083 --Bug 5255184 Modified the c_area_code query
1084 CURSOR c_area_code IS
1085 SELECT hzp.phone_area_code
1086 FROM hz_contact_points hzp
1087 WHERE hzp.contact_point_id = c_customer_phone_id;
1088
1089 -- Added for member filtering for IB/Contracts Preferred Resources
1090 -- From 11.5.10 onwards
1091 CURSOR c_check_grp_res(p_group_id NUMBER, p_resource_id NUMBER) IS
1092 SELECT 'Y'
1093 FROM jtf_rs_group_members
1094 WHERE group_id = p_group_id
1095 AND resource_id = p_resource_id
1096 AND NVL(delete_flag, 'N') <> 'Y';
1097
1098 CURSOR c_cust_det(p_customer_id NUMBER) IS
1099 SELECT employees_total, party_name
1100 FROM hz_parties
1101 WHERE party_id = p_customer_id;
1102
1103 BEGIN
1104 -- Initialize API return status to success
1105 x_return_status := FND_API.G_RET_STS_SUCCESS;
1106
1107 l_incident_id := p_incident_id;
1108 l_cust_prod_id := l_sr_rec.customer_product_id;
1109 -- if incident_id is not null proceed, otherwise exit API
1113 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_AUTO_ASSIGN') = 'YES') THEN
1110 IF ((l_incident_id IS NOT NULL) AND (p_group_id IS NOT NULL )) THEN
1111 l_group_id := p_group_id ;
1112
1114 SELECT business_process_id INTO l_business_process_id
1115 FROM cs_incident_types
1116 WHERE incident_type_id = l_sr_rec.type_id;
1117 END IF;
1118
1119 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
1120 -- Assigning component and subcomponent id directly to the am rec
1121
1122 IF (l_cust_prod_id IS NOT NULL) THEN
1123 l_sr_am_rec.item_component := l_sr_rec.cp_component_id;
1124 l_sr_am_rec.item_subcomponent := l_sr_rec.cp_subcomponent_id;
1125 ELSE
1126 l_sr_am_rec.item_component := l_sr_rec.inv_component_id;
1127 l_sr_am_rec.item_subcomponent := l_sr_rec.inv_subcomponent_id;
1128 END IF;
1129
1130 IF (p_service_request_rec.incident_location_id IS NOT NULL) THEN
1131 -- Added for 11.5.9 ER# 2527850
1132 IF (p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE') THEN
1133 OPEN c_inc_party_site_address(p_service_request_rec.incident_location_id);
1134 FETCH c_inc_party_site_address INTO l_location_id;
1135 IF (c_inc_party_site_address%NOTFOUND) THEN
1136 l_location_id := NULL;
1137 END IF;
1138 CLOSE c_inc_party_site_address;
1139 END IF;
1140 OPEN c_inc_address(l_location_id);
1141 FETCH c_inc_address INTO l_country,l_province,l_state,l_city,
1142 l_postal_code, l_county;
1143 IF (c_inc_address%NOTFOUND) THEN
1144 NULL;
1145 END IF;
1146 l_sr_am_rec.country := l_country;
1147 l_sr_am_rec.city := l_city;
1148 l_sr_am_rec.postal_code := l_postal_code;
1149 l_sr_am_rec.state := l_state;
1150 l_sr_am_rec.province := l_province;
1151 l_sr_am_rec.county := l_county;
1152 CLOSE c_inc_address;
1153 ELSE
1154 l_sr_am_rec.country := p_service_request_rec.incident_country;
1155 l_sr_am_rec.city := p_service_request_rec.incident_city;
1156 l_sr_am_rec.postal_code := p_service_request_rec.incident_postal_code;
1157 l_sr_am_rec.state := p_service_request_rec.incident_state;
1158 l_sr_am_rec.province := p_service_request_rec.incident_province;
1159 l_sr_am_rec.county := p_service_request_rec.incident_county;
1160 END IF;
1161
1162 OPEN c_area_code;
1163 FETCH c_area_code INTO l_area_code;
1164 IF (c_area_code%NOTFOUND) THEN
1165 l_area_code := NULL;
1166 END IF;
1167 CLOSE c_area_code;
1168
1169 OPEN c_cust_det(l_sr_rec.customer_id);
1170 FETCH c_cust_det INTO l_no_of_employees, l_party_name;
1171 IF (c_cust_det%NOTFOUND) THEN
1172 l_no_of_employees := NULL;
1173 l_party_name := NULL;
1174 END IF;
1175 CLOSE c_cust_det;
1176 -- 12.1.2 Enhancement
1177 Begin
1178 SELECT to_char(sysdate, 'd'), to_char(sysdate, 'hh24:mi')
1179 INTO l_day_week, l_time_day
1180 FROM cs_incidents_all_b
1181 WHERE incident_id = l_incident_id ;
1182 Exception
1183 When Others then
1184 l_time_day := null ;
1185 l_day_week := null ;
1186 End ;
1187
1188 l_sr_am_rec.DAY_OF_WEEK := l_day_week ;
1189 l_sr_am_rec.TIME_OF_DAY := l_time_day ;
1190 l_sr_am_rec.service_request_id := l_incident_id;
1191 l_sr_am_rec.party_id := l_sr_rec.customer_id;
1192 l_sr_am_rec.party_id := l_sr_rec.customer_id;
1193 l_sr_am_rec.incident_type_id := l_sr_rec.type_id;
1194 l_sr_am_rec.incident_severity_id := l_sr_rec.severity_id;
1195 l_sr_am_rec.incident_urgency_id := l_sr_rec.urgency_id;
1196 l_sr_am_rec.problem_code := l_sr_rec.problem_code;
1197 l_sr_am_rec.incident_status_id := l_sr_rec.status_id;
1198 l_sr_am_rec.platform_id := l_sr_rec.platform_id;
1199 l_sr_am_rec.inventory_item_id := l_sr_rec.inventory_item_id;
1200 l_sr_am_rec.sr_creation_channel := l_sr_rec.sr_creation_channel;
1201 l_sr_am_rec.area_code := l_area_code;
1202 --l_sr_am_rec.customer_site_id := l_sr_rec.customer_site_id;
1203 --l_sr_am_rec.support_site_id := l_sr_rec.site_id;
1204 l_sr_am_rec.squal_char12 := l_sr_rec.problem_code;
1205 l_sr_am_rec.squal_char13 := l_sr_rec.comm_pref_code;
1206 l_sr_am_rec.squal_char20 := l_sr_rec.cust_pref_lang_code ;
1207 l_sr_am_rec.squal_char21 := l_sr_rec.coverage_type;
1208 l_sr_am_rec.squal_num12 := l_sr_rec.platform_id;
1209 l_sr_am_rec.squal_num13 := l_sr_rec.inv_platform_org_id;
1210 l_sr_am_rec.squal_num14 := l_sr_rec.category_id;
1211 l_sr_am_rec.squal_num15 := l_sr_rec.inventory_item_id;
1212 l_sr_am_rec.squal_num16 := l_sr_rec.inventory_org_id;
1213 l_sr_am_rec.squal_num17 := l_group_id;
1214 l_sr_am_rec.squal_num30 := l_sr_rec.language_id;
1215 l_sr_am_rec.num_of_employees := l_no_of_employees;
1216 l_sr_am_rec.comp_name_range := l_party_name;
1217
1218
1219 If p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE' Then -- Added by NIC
1220 l_sr_am_rec.party_site_id := p_service_request_rec.site_number ;
1221 ELSE
1222 l_sr_am_rec.party_site_id := null ;
1223 END IF ;
1224
1225 --Contract Item and Org dtls
1226 IF (l_contract_service_id IS NOT NULL) THEN
1227 OPEN c_contract(l_contract_service_id);
1228 FETCH c_contract INTO l_inv_item_id,l_inv_org_id;
1229 IF c_contract%NOTFOUND THEN
1230 NULL;
1231 END IF;
1232 CLOSE c_contract;
1233 END IF;
1234 -- For contracts
1235 l_sr_am_rec.squal_num18 := l_inv_item_id;
1236 l_sr_am_rec.squal_num19 := l_inv_org_id;
1240 l_am_calling_doc_id := l_incident_id;
1237 l_sr_am_rec.squal_char11 := NULL;
1238
1239 -- populate the AM parameters
1241 l_param_resource_type := p_param_resource_type;
1242
1243 -- If customer product id is not null, then set ib_preferred_resource_flag
1244 -- to 'Y'.If contract line id is not null, then set
1245 -- contract_preferred_resource flag to 'Y'.
1246 IF (l_contract_service_id IS NOT NULL) THEN
1247 IF (FND_PROFILE.VALUE('CS_SR_CONTRACT_INDIVIDUAL')= 'Y') THEN --Added By Nic
1248 l_contract_res_flag := 'Y';
1249 ELSE
1250 l_contract_res_flag := 'N';
1251 END IF;
1252 END IF;
1253 IF (L_CUST_PROD_ID IS NOT NULL) THEN
1254 IF (FND_PROFILE.VALUE ('CS_SR_IB_INDIVIDUAL') ='Y' ) THEN -- Added by Nic
1255 l_ib_resource_flag := 'Y';
1256 ELSE
1257 l_ib_resource_flag := 'N';
1258 END IF;
1259 END IF;
1260 IF (FND_PROFILE.VALUE('CS_SR_TERRITORY_INDIVIDUAL') = 'Y') THEN -- Added by nic
1261 l_territory_flag :='Y';
1262 ELSE
1263 l_territory_flag :='N';
1264 END IF;
1265
1266 FND_PROFILE.Get('CS_SR_CHK_RES_CAL_AVL', l_cs_sr_chk_res_cal_avl); --gasankar Calendar check feature added
1267
1268 If nvl(l_cs_sr_chk_res_cal_avl, 'N') <> 'N' Then
1269 l_start_date := sysdate ;
1270 l_end_date := sysdate ;
1271 End If ;
1272
1273 l_param_resource_type := 'RS_INDIVIDUAL';
1274
1275 -- Passing the auto_select_flag as 'N' bcoz if it is null the JTF API
1276 -- assigns it as 'Y' and always returns the first record. No Load Balancing
1277 -- is done. Made contracts_preferred_engineer as 'Y' for 11.5.9 according
1278 -- to whether contract_service_id is not null.
1279 -- From 11.5.9+, the contract_id, inventory_item_id and inventory_org_id
1280 -- are always passed as Null and the Load Balancing will be done for all
1281 -- the resources with or without skills.
1282 JTF_ASSIGN_PUB.GET_Assign_Resources
1283 ( p_api_version => 1.0,
1284 p_init_msg_list => 'T',
1285 p_commit => 'F',
1286 p_resource_id => l_group_id,
1287 p_resource_type => l_param_resource_type,
1288 p_role => null,
1289 p_no_of_resources => l_no_of_resources,
1290 p_auto_select_flag => 'N',
1291 p_ib_preferred_engineer => nvl(l_ib_resource_flag,'N'),
1292 p_contracts_preferred_engineer => nvl(l_contract_res_flag,'N'),
1293 p_contract_id => l_contract_service_id,
1294 p_customer_product_id => l_cust_prod_id,
1295 p_effort_duration => null,
1296 p_effort_uom => null,
1297 p_start_date => l_start_date,
1298 p_end_date => l_end_date,
1299 p_territory_flag => nvl(l_territory_flag,'N'),
1300 p_calendar_flag => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
1301 p_calendar_check => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
1302 p_web_availability_flag => 'Y',
1303 p_filter_excluded_resource => 'Y',
1304 p_category_id => NULL,
1305 p_inventory_item_id => NULL,
1306 p_inventory_org_id => NULL,
1307 p_column_list => null,
1308 p_calling_doc_id => l_am_calling_doc_id,
1309 p_calling_doc_type => l_am_calling_doc_type,
1310 p_sr_rec => l_sr_am_rec,
1311 p_sr_task_rec => null,
1312 p_defect_rec => null,
1313 p_business_process_id => l_business_process_id,
1314 p_business_process_date => l_sr_rec.request_date,
1315 x_Assign_Resources_tbl => l_Assign_Owner_tbl,
1316 x_return_status => x_return_status,
1317 x_msg_count => x_msg_count,
1318 x_msg_data => x_msg_data
1319 );
1320
1321 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1322 p := l_Assign_Owner_tbl.FIRST ;
1323 if (l_Assign_Owner_tbl.count = 1 AND
1324 l_Assign_Owner_tbl(p).web_availability_flag = 'Y') then
1325 OPEN c_check_grp_res(l_group_id, l_Assign_Owner_tbl(p).resource_id);
1326 FETCH c_check_grp_res INTO l_ismember;
1327 CLOSE c_check_grp_res;
1328 IF (NVL(l_ismember, 'N') = 'Y') THEN
1329 x_resource_id := l_Assign_Owner_tbl(p).resource_id ;
1330 x_resource_type := l_Assign_Owner_tbl(p).resource_type ;
1331 x_territory_id := l_Assign_Owner_tbl(p).terr_id;
1332 END IF;
1333 END IF;
1334
1335 IF (l_Assign_Owner_tbl.COUNT > 1) THEN
1336 l_count := l_Assign_Owner_tbl.COUNT;
1337 l_index := l_Assign_Owner_tbl.FIRST;
1338 l_counter := l_Assign_Owner_tbl.FIRST ;
1339 WHILE l_index <= l_count
1340 LOOP
1341 l_ismember := 'N';
1342 IF (l_Assign_Owner_tbl(l_index).web_availability_flag = 'Y') THEN
1343 OPEN c_check_grp_res(l_group_id, l_Assign_Owner_tbl(l_index).resource_id);
1344 FETCH c_check_grp_res INTO l_ismember;
1345 CLOSE c_check_grp_res;
1346 IF (NVL(l_ismember, 'N') = 'Y') THEN
1347 l_resource_load_tbl(l_counter).resource_id :=
1348 l_Assign_Owner_tbl(l_index).resource_id;
1349 l_resource_load_tbl(l_counter).resource_type :=
1350 l_Assign_Owner_tbl(l_index).resource_type;
1351 l_resource_load_tbl(l_counter).support_site_id :=
1352 l_Assign_Owner_tbl(l_index).support_site_id;
1353 l_resource_load_tbl(l_counter).territory_id :=
1357 l_resource_load_tbl(l_counter).resource_id :=
1354 l_Assign_Owner_tbl(l_index).terr_id;
1355 ELSE
1356 /* Start Bug : 6241796 */
1358 Null;
1359 l_resource_load_tbl(l_counter).resource_type :=
1360 Null ;
1361 l_resource_load_tbl(l_counter).support_site_id :=
1362 Null ;
1363 l_resource_load_tbl(l_counter).territory_id :=
1364 Null ; /* End Bug : 6241796 */
1365 END IF;
1366
1367 /* Start Bug : 6391261 */
1368 IF ( l_Counter = l_Assign_Owner_tbl.FIRST AND nvl(l_ismember, 'N') = 'N' ) Then
1369 null ;
1370 ELSE
1371 l_counter := l_counter + 1;
1372 END IF ;
1373 /* End Bug : 6391261 */
1374 END IF;
1375 l_index := l_index + 1;
1376 END LOOP;
1377
1378 IF (l_resource_load_tbl.COUNT > 1) THEN
1379 CS_ASSIGN_RESOURCE_CON_PKG.Calculate_Load
1380 ( p_init_msg_list => p_init_msg_list,
1381 p_incident_id => p_incident_id,
1382 p_incident_type_id => p_service_request_rec.type_id,
1383 p_incident_severity_id => p_service_request_rec.severity_id,
1384 p_inv_item_id => p_service_request_rec.inventory_item_id,
1385 p_inv_org_id => p_service_request_rec.inventory_org_id,
1386 p_platform_org_id => p_service_request_rec.inv_platform_org_id,
1387 p_inv_cat_id => p_service_request_rec.category_id,
1388 p_platform_id => p_service_request_rec.platform_id,
1389 p_problem_code => p_service_request_rec.problem_code,
1390 p_contact_timezone_id => p_service_request_rec.time_zone_id,
1391 p_res_load_table => l_resource_load_tbl,
1392 x_return_status => l_cal_load_return_sts,
1393 x_resource_id => x_resource_id,
1394 x_resource_type => x_resource_type,
1395 x_msg_count => x_msg_count,
1396 x_msg_data => x_msg_data,
1397 x_territory_id => x_territory_id
1398 );
1399
1400 IF (l_cal_load_return_sts <> FND_API.G_RET_STS_SUCCESS) THEN
1401 /* due to TZ API error, but continue if resource is returned */
1402 IF (x_resource_id IS NOT NULL) THEN
1403 x_return_status := FND_API.G_RET_STS_SUCCESS;
1404 l_cal_load_return_sts := FND_API.G_RET_STS_SUCCESS;
1405 END IF;
1406 END IF;
1407
1408 IF (l_cal_load_return_sts <> FND_API.G_RET_STS_SUCCESS) THEN
1409 FND_MESSAGE.Set_Name('CS', 'CS_API_NO_OWNER');
1410 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
1411 l_msg := fnd_message.get ;
1412 fnd_file.put_line(fnd_file.log,' ' || l_msg);
1413 x_return_status := FND_API.G_RET_STS_ERROR ;
1414 END IF;
1415 ELSE
1416 IF (l_resource_load_tbl.COUNT = 1) THEN
1417 l := l_resource_load_tbl.FIRST;
1418 x_resource_id := l_resource_load_tbl(l).resource_id;
1419 x_resource_type := l_resource_load_tbl(l).resource_type;
1420 x_territory_id := l_resource_load_tbl(l).territory_id;
1421 END IF;
1422 END IF; -- l_resource_load_tbl.Count >1
1423 -- x_territory_id := l_Assign_Owner_tbl(l).terr_id;
1424 END IF; -- l_Assign_Owner_tbl.Count > 1
1425 END IF ; --Return status S
1426 END IF; -- l_incident_id is not null
1427 END Assign_Owner;
1428
1429 -- Added parameter p_inv_cat_id by pnkalari on 06/11/2002.
1430 PROCEDURE Calculate_Load
1431 (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1432 p_incident_id IN NUMBER,
1433 p_incident_type_id IN NUMBER,
1434 p_incident_severity_id IN NUMBER,
1435 p_inv_item_id IN NUMBER,
1436 p_inv_org_id IN NUMBER,
1437 p_inv_cat_id IN NUMBER,
1438 p_platform_org_id IN NUMBER,
1439 p_platform_id IN NUMBER,
1440 p_problem_code IN VARCHAR2,
1441 p_contact_timezone_id IN NUMBER,
1442 p_res_load_table IN OUT NOCOPY CS_ASSIGN_RESOURCE_CON_PKG.LoadBalance_tbl_type,
1443 x_return_status OUT NOCOPY VARCHAR2,
1444 x_resource_id OUT NOCOPY NUMBER,
1445 x_resource_type OUT NOCOPY VARCHAR2,
1446 x_msg_count OUT NOCOPY NUMBER,
1447 x_msg_data OUT NOCOPY VARCHAR2,
1448 x_territory_id OUT NOCOPY NUMBER
1449 ) IS
1450
1451 -- Define Local Variables
1452 l_api_name CONSTANT VARCHAR2(30) := 'Assign_ServiceRequest_Main';
1453 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1454 l_msg VARCHAR2(2000) ;
1455 l_resource_id NUMBER;
1456 l_resource_type VARCHAR2(30);
1457 l_support_site_id NUMBER;
1458 l_return_status VARCHAR2(1);
1459 l_incident_type_id NUMBER;
1460 l_incident_severity_id NUMBER;
1461 l_wt_prd_skill NUMBER;
1462 l_wt_plt_skill NUMBER;
1463 l_wt_pbm_skill NUMBER;
1464 l_wt_cat_skill NUMBER;
1465 l_wt_time_last_login NUMBER;
1466 l_wt_backlog_sev1 NUMBER;
1467 l_wt_backlog_sev2 NUMBER;
1468 l_wt_backlog_sev3 NUMBER;
1469 l_wt_backlog_sev4 NUMBER;
1470 l_wt_time_zone_lag NUMBER;
1471 l_res_load NUMBER;
1472 l_max_total_load NUMBER;
1473 l_tbl_index BINARY_INTEGER;
1474 i BINARY_INTEGER;
1475 l_count NUMBER;
1476 l_max_record_index BINARY_INTEGER;
1477 l_supp_timezone_id NUMBER;
1478 l_contact_timezone_id NUMBER := p_contact_timezone_id;
1479 l_time_lag NUMBER;
1480 l_time_lag_score NUMBER := 0;
1484 l_prob_skill NUMBER := NULL;
1481 l_problem_code VARCHAR2(50) := p_problem_code;
1482 l_prod_skill NUMBER := NULL;
1483 l_plat_skill NUMBER := NULL;
1485 l_cat_skill NUMBER := NULL;
1486 l_time_last_login NUMBER := NULL;
1487 l_backlog_sev1 NUMBER := NULL;
1488 l_backlog_sev2 NUMBER := NULL;
1489 l_backlog_sev3 NUMBER := NULL;
1490 l_backlog_sev4 NUMBER := NULL;
1491 l_imp_level NUMBER := NULL;
1492 -- List of Cursors
1493 CURSOR c_load_wt(l_incident_type_id NUMBER,l_incident_severity_id NUMBER) IS
1494 SELECT product_skill_wt,platform_skill_wt,prob_code_skill_wt, category_skill_wt,
1495 last_login_time_wt,severity1_count_wt,severity2_count_wt,
1496 severity3_count_wt,severity4_count_wt,time_zone_diff_wt
1497 FROM cs_sr_load_balance_wt
1498 WHERE incident_type_id = l_incident_type_id
1499 AND incident_severity_id = l_incident_severity_id;
1500
1501 CURSOR C_PROD_SKILL(l_prod_id NUMBER,l_prod_org_id NUMBER,l_resource_id NUMBER) IS
1502 SELECT s.skill_level
1503 FROM jtf_rs_skill_levels_vl s,
1504 jtf_rs_resource_skills rs
1505 WHERE rs.resource_id = l_resource_id
1506 AND rs.product_id = l_prod_id
1507 AND rs.product_org_id = l_prod_org_id
1508 AND rs.skill_level_id = s.skill_level_id;
1509 -- and nvl(rs.category_id,0) = nvl(l_cat_id,0)
1510
1511 CURSOR C_PLAT_SKILL(l_platform_id NUMBER,l_platform_org_id NUMBER,l_resource_id NUMBER) IS
1512 SELECT s.skill_level
1513 FROM jtf_rs_skill_levels_vl s,
1514 jtf_rs_resource_skills rs
1515 WHERE rs.resource_id = l_resource_id
1516 AND rs.platform_id = l_platform_id
1517 AND rs.platform_org_id = l_platform_org_id
1518 AND rs.skill_level_id = s.skill_level_id;
1519
1520 CURSOR C_PROB_SKILL(l_problem_code VARCHAR2,l_resource_id NUMBER) IS
1521 SELECT s.skill_level
1522 FROM jtf_rs_skill_levels_vl s,
1523 jtf_rs_resource_skills rs
1524 WHERE rs.resource_id = l_resource_id
1525 AND rs.problem_code = l_problem_code
1526 AND rs.skill_level_id = s.skill_level_id;
1527
1528 -- Added cursor to fetch the Category Skill for ER# 2527963.
1529 CURSOR c_cat_skill(l_category_id NUMBER, l_resource_id NUMBER) IS
1530 SELECT s.skill_level
1531 FROM jtf_rs_skill_levels_vl s,
1532 jtf_rs_resource_skills rs
1533 WHERE rs.resource_id = l_resource_id
1534 AND rs.category_id = l_category_id
1535 AND rs.skill_level_id = s.skill_level_id;
1536
1537 CURSOR c_time_last_login(l_resource_id NUMBER) IS
1538 SELECT ROUND(((SYSDATE - nvl( max(owner_assigned_time),to_date('1990-01-01','yyyy-mm-dd'))) *24 * 60),2)
1539 FROM cs_incidents_all_b
1540 WHERE incident_owner_id = l_resource_id;
1541
1542 -- Commented above and added for Bug# 3562642
1543 CURSOR c_imp_level(p_inc_severity_id NUMBER) IS
1544 SELECT importance_level
1545 FROM cs_incident_severities_vl
1546 WHERE incident_subtype = 'INC'
1547 AND incident_severity_id = p_inc_severity_id;
1548
1549 CURSOR c_sev1_cnt(l_sev1_id NUMBER,l_resource_id NUMBER) IS
1550 SELECT COUNT(*)
1551 FROM cs_incidents_all_b
1552 WHERE incident_severity_id = l_sev1_id
1553 AND incident_owner_id = l_resource_id
1554 AND incident_status_id NOT IN (
1555 SELECT incident_status_id
1556 FROM cs_incident_statuses_vl
1557 WHERE incident_subtype = 'INC'
1558 AND close_flag = 'Y');
1559
1560 CURSOR c_sev2_cnt(l_sev2_id NUMBER ,l_resource_id NUMBER) IS
1561 SELECT COUNT(*)
1562 FROM cs_incidents_all_b
1563 WHERE incident_severity_id = l_sev2_id
1564 AND incident_owner_id = l_resource_id
1565 AND incident_status_id NOT IN (
1566 SELECT incident_status_id
1567 FROM cs_incident_statuses_vl
1568 WHERE incident_subtype = 'INC'
1569 AND close_flag = 'Y');
1570
1571 CURSOR c_sev3_cnt(l_sev3_id NUMBER,l_resource_id NUMBER) IS
1572 SELECT COUNT(*)
1573 FROM cs_incidents_all_b
1574 WHERE incident_severity_id = l_sev3_id
1575 AND incident_owner_id = l_resource_id
1576 AND incident_status_id NOT IN (
1577 SELECT incident_status_id
1578 FROM cs_incident_statuses_vl
1579 WHERE incident_subtype = 'INC'
1580 AND close_flag = 'Y');
1581
1582 CURSOR c_sev4_cnt(l_sev4_id NUMBER,l_resource_id NUMBER) IS
1583 SELECT COUNT(*)
1584 FROM cs_incidents_all_b
1585 WHERE incident_severity_id = l_sev4_id
1586 AND incident_owner_id = l_resource_id
1587 AND incident_status_id NOT IN (
1588 SELECT incident_status_id
1589 FROM cs_incident_statuses_vl
1590 WHERE incident_subtype = 'INC'
1591 AND close_flag = 'Y');
1592
1593 CURSOR c_res_time_zone(p_resource_id NUMBER) IS
1594 SELECT time_zone
1595 FROM jtf_rs_resource_extns
1596 WHERE resource_id = p_resource_id;
1597
1598 BEGIN
1599 -- Initialize API return status to success
1600 x_return_status := FND_API.G_RET_STS_SUCCESS;
1601
1602 l_incident_type_id := p_incident_type_id;
1603 l_incident_severity_id := p_incident_severity_id;
1604
1605 OPEN c_load_wt(l_incident_type_id,l_incident_severity_id);
1606 FETCH c_load_wt INTO
1607 l_wt_prd_skill, l_wt_plt_skill, l_wt_pbm_skill, l_wt_cat_skill,
1608 l_wt_time_last_login, l_wt_backlog_sev1, l_wt_backlog_sev2 ,
1609 l_wt_backlog_sev3, l_wt_backlog_sev4, l_wt_time_zone_lag;
1610 IF (c_load_wt%NOTFOUND) THEN
1611 l_wt_prd_skill := 0;
1612 l_wt_plt_skill := 0;
1613 l_wt_pbm_skill := 0;
1614 l_wt_cat_skill := 0;
1615 l_wt_time_last_login := 0;
1616 l_wt_backlog_sev1 := 0;
1617 l_wt_backlog_sev2 := 0;
1618 l_wt_backlog_sev3 := 0;
1619 l_wt_backlog_sev4 := 0;
1623
1620 l_wt_time_zone_lag := 0;
1621 END IF;
1622 CLOSE c_load_wt;
1624 l_tbl_index := p_res_load_table.FIRST;
1625 l_count := p_res_load_table.COUNT;
1626 WHILE l_tbl_index <= l_count
1627 LOOP
1628 l_resource_id := p_res_load_table(l_tbl_index).resource_id;
1629 l_resource_type := p_res_load_table(l_tbl_index).resource_type;
1630 l_support_site_id := p_res_load_table(l_tbl_index).support_site_id;
1631 l_supp_timezone_id := NULL;
1632 l_time_lag := NULL;
1633 l_time_lag_score := NULL;
1634 l_res_load := NULL;
1635
1636 IF (p_inv_item_id IS NOT NULL AND
1637 p_inv_org_id IS NOT NULL AND
1638 l_resource_id IS NOT NULL AND NVL(l_wt_prd_skill,0) >0) THEN
1639 OPEN c_prod_skill(p_inv_item_id,p_inv_org_id,l_resource_id);
1640 FETCH c_prod_skill INTO l_prod_skill;
1641 IF (c_prod_skill%NOTFOUND) THEN
1642 l_prod_skill := NULL;
1643 END IF;
1644 CLOSE c_prod_skill;
1645 END IF;
1646
1647 IF (p_platform_id IS NOT NULL AND
1648 p_platform_org_id IS NOT NULL AND
1649 l_resource_id IS NOT NULL AND NVL(l_wt_plt_skill,0)>0) THEN
1650 OPEN c_plat_skill(p_platform_id,p_platform_org_id,l_resource_id);
1651 FETCH c_plat_skill INTO l_plat_skill;
1652 IF (c_plat_skill%NOTFOUND) THEN
1653 l_plat_skill := NULL;
1654 END IF;
1655 CLOSE c_plat_skill;
1656 END IF;
1657
1658 IF (l_problem_code IS NOT NULL AND
1659 l_resource_id IS NOT NULL AND NVL(l_wt_pbm_skill,0)>0) THEN
1660 OPEN c_prob_skill(l_problem_code,l_resource_id);
1661 FETCH c_prob_skill INTO l_prob_skill;
1662 IF (c_prob_skill%NOTFOUND) THEN
1663 l_prob_skill := NULL;
1664 END IF;
1665 CLOSE c_prob_skill;
1666 END IF;
1667
1668 IF (p_inv_cat_id IS NOT NULL AND
1669 l_resource_id IS NOT NULL AND NVL(l_wt_cat_skill,0) >0) THEN
1670 OPEN c_cat_skill(p_inv_cat_id, l_resource_id);
1671 FETCH c_cat_skill INTO l_cat_skill;
1672 IF (c_cat_skill%NOTFOUND) THEN
1673 l_cat_skill := NULL;
1674 END IF;
1675 CLOSE c_cat_skill;
1676 END IF;
1677
1678 -- Changed the if condition to calculate the count of SRs if
1679 -- l_resource_id is not null 11.5.9
1680 IF (l_resource_id IS NOT NULL) THEN
1681 IF (NVL(l_wt_time_last_login,0)<>0) THEN
1682 --For every resource get the backlog of severity 1,2 3, 4 SR's
1683 OPEN c_time_last_login(l_resource_id);
1684 FETCH c_time_last_login INTO l_time_last_login;
1685 IF (c_time_last_login%NOTFOUND) THEN
1686 l_time_last_login := NULL;
1687 END IF;
1688 CLOSE c_time_last_login;
1689 END IF;
1690
1691 OPEN c_imp_level(l_incident_severity_id);
1692 FETCH c_imp_level INTO l_imp_level;
1693 IF (c_imp_level%NOTFOUND) THEN
1694 l_imp_level := 0;
1695 END IF;
1696 CLOSE c_imp_level;
1697
1698 IF (l_imp_level = 1 AND NVL(l_wt_backlog_sev1,0) <> 0) THEN
1699 OPEN c_sev1_cnt(l_incident_severity_id,l_resource_id);
1700 FETCH c_sev1_cnt INTO l_backlog_sev1;
1701 IF (c_sev1_cnt%NOTFOUND) THEN
1702 l_backlog_sev1 := NULL;
1703 END IF;
1704 CLOSE c_sev1_cnt;
1705 ELSIF (l_imp_level = 2 AND NVL(l_wt_backlog_sev2,0) <> 0) THEN
1706 OPEN c_sev2_cnt(l_incident_severity_id,l_resource_id);
1707 FETCH c_sev2_cnt INTO l_backlog_sev2;
1708 IF (c_sev2_cnt%NOTFOUND) THEN
1709 l_backlog_sev2 := NULL;
1710 END IF;
1711 CLOSE c_sev2_cnt;
1712 ELSIF (l_imp_level = 3 AND NVL(l_wt_backlog_sev3,0) <> 0) THEN
1713 OPEN c_sev3_cnt(l_incident_severity_id,l_resource_id);
1714 FETCH c_sev3_cnt INTO l_backlog_sev3;
1715 IF (c_sev3_cnt%NOTFOUND) THEn
1716 l_backlog_sev3 := NULL;
1717 END IF;
1718 CLOSE c_sev3_cnt;
1719 ELSIF (l_imp_level = 4 AND NVL(l_wt_backlog_sev4,0) <> 0) THEN
1720 OPEN c_sev4_cnt(l_incident_severity_id,l_resource_id);
1721 FETCH c_sev4_cnt INTO l_backlog_sev4;
1722 IF (c_sev4_cnt%NOTFOUND) THEN
1723 l_backlog_sev4 := NULL;
1724 END IF;
1725 CLOSE c_sev4_cnt;
1726 ELSE
1727 l_backlog_sev1 := NULL;
1728 l_backlog_sev2 := NULL;
1729 l_backlog_sev3 := NULL;
1730 l_backlog_sev4 := NULL;
1731 END IF;
1732
1733 END IF; -- l_resource_id is not null
1734
1735 IF (l_support_site_id IS NOT NULL AND NVL(l_wt_time_zone_lag,0)<>0) THEN
1736 OPEN c_res_time_zone(l_resource_id);
1737 FETCH c_res_time_zone INTO l_supp_timezone_id;
1738 IF (c_res_time_zone%NOTFOUND) THEN
1739 l_supp_timezone_id := NULL;
1740 END IF;
1741 CLOSE c_res_time_zone;
1742 END IF;
1743
1744 IF (l_contact_timezone_id IS NOT NULL AND
1745 l_supp_timezone_id IS NOT NULL) THEN
1746 IF (l_contact_timezone_id <> l_supp_timezone_id) THEN
1747 CS_TZ_GET_DETAILS_PVT.GET_LEADTIME
1748 (1.0,
1749 'T',
1750 l_supp_timezone_id,
1751 l_contact_timezone_id,
1752 l_time_lag,
1753 x_return_status,
1754 x_msg_count,
1755 x_msg_data
1756 );
1757 ELSE
1758 l_time_lag := 0;
1759 END IF;
1760
1761 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1762 FND_MESSAGE.Set_Name('CS', 'CS_TZ_API_ERR');
1763 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
1767 END IF;
1764 l_msg := fnd_message.get ;
1765 fnd_file.put_line(fnd_file.log,' ' || l_msg);
1766 EXIT ;
1768
1769 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1770 l_time_lag := abs(l_time_lag);
1771 ELSE
1772 l_time_lag := 0;
1773 END IF;
1774 ELSE /* l_contact_timezone_id or l_supp_timezone_id is not null*/
1775 l_time_lag := 0;
1776 END IF; /* l_contact_timezone_id or l_supp_timezone_id is missing */
1777
1778 -- New formula for time lag for OSS ,as given in enhancement 2093850
1779 -- Added weight multiplication and support timezone id check for Bug# 3526252
1780 IF (l_supp_timezone_id IS NULL) THEN
1781 l_time_lag_score := 0;
1782 ELSE
1783 l_time_lag_score := ROUND(2.77 - (l_time_lag/4)) * NVL(l_wt_time_zone_lag,0);
1784 END IF;
1785
1786 -- calculate total load for each
1787 -- Added nvl for all the weights 11.5.9
1788 l_res_load :=
1789 ((NVL(l_prod_skill,0) * NVL(l_wt_prd_skill,0)) +
1790 (NVL(l_plat_skill,0) * NVL(l_wt_plt_skill,0)) +
1791 (NVL(l_prob_skill,0) * NVL(l_wt_pbm_skill,0)) +
1792 (NVL(l_cat_skill,0) * NVL(l_wt_cat_skill,0)) +
1793 (NVL(l_time_last_login,0) * NVL(l_wt_time_last_login,0)) +
1794 (NVL(l_backlog_sev1,0) * NVL(l_wt_backlog_sev1,0)) +
1795 (NVL(l_backlog_sev2,0) * NVL(l_wt_backlog_sev2,0)) +
1796 (NVL(l_backlog_sev3,0) * NVL(l_wt_backlog_sev3,0)) +
1797 (NVL(l_backlog_sev4,0) * NVL(l_wt_backlog_sev4,0)) +
1798 (NVL(l_time_lag_score,0) * NVL(l_wt_time_zone_lag,0)));
1799
1800 -- copy values into table
1801 p_res_load_table(l_tbl_index).product_skill_level := l_prod_skill;
1802 p_res_load_table(l_tbl_index).platform_skill_level := l_plat_skill;
1803 p_res_load_table(l_tbl_index).pbm_code_skill_level := l_prob_skill;
1804 p_res_load_table(l_tbl_index).category_skill_level := l_cat_skill;
1805 p_res_load_table(l_tbl_index).time_since_last_login := l_time_last_login;
1806 p_res_load_table(l_tbl_index).backlog_sev1 := l_backlog_sev1;
1807 p_res_load_table(l_tbl_index).backlog_sev2 := l_backlog_sev2;
1808 p_res_load_table(l_tbl_index).backlog_sev3 := l_backlog_sev3;
1809 p_res_load_table(l_tbl_index).backlog_sev4 := l_backlog_sev4;
1810 p_res_load_table(l_tbl_index).time_zone_lag := l_time_lag;
1811 p_res_load_table(l_tbl_index).total_load := l_res_load;
1812
1813 l_tbl_index := l_tbl_index + 1;
1814 END LOOP;
1815 -- After the load for all resources are calculated find the
1816 -- resource with the max load.This is the winning resource to
1817 -- be returned
1818 -- If timezone API does not give error then proceed
1819
1820 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1821 --i := 0;
1822 -- Added this because if all the resource loads are 0,
1823 -- l_max_record_index is always the first resource.
1824 l_max_record_index := p_res_load_table.FIRST;
1825
1826 -- Bug 4907196 . The value of l_max_total_load is changed to -9999999 from 0.
1827 -- Total load can have negative values hence the initial value shouldn't be 0
1828 l_max_total_load := -999999999;
1829
1830 /* Commented out for Bug# 4017138
1831 FOR i IN 1..p_res_load_table.COUNT LOOP
1832 IF (i = 1 ) THEN
1833 l_max_total_load := p_res_load_table(i).total_load ;
1834 l_max_record_index := i ;
1835 ELSE
1836 IF (p_res_load_table(i).total_load > l_max_total_load) THEN
1837 l_max_total_load := p_res_load_table(i).total_load;
1838 l_max_record_index := i;
1839 END IF;
1840 END IF;
1841 END LOOP ;
1842 */
1843 IF (p_res_load_table.COUNT > 0) THEN
1844 FOR i IN p_res_load_table.FIRST..p_res_load_table.LAST LOOP
1845 IF ( p_res_load_table.COUNT = 1 ) THEN
1846 l_max_total_load := p_res_load_table(i).total_load ;
1847 l_max_record_index := i ;
1848 ELSE
1849 IF (p_res_load_table(i).total_load > l_max_total_load) THEN
1850 l_max_total_load := p_res_load_table(i).total_load;
1851 l_max_record_index := i;
1852 END IF;
1853 END IF;
1854 END LOOP ;
1855 END IF;
1856 x_resource_id := p_res_load_table(l_max_record_index).resource_id;
1857 x_resource_type := p_res_load_table(l_max_record_index).resource_type;
1858 x_territory_id := p_res_load_table(l_max_record_index).territory_id;
1859 IF (x_resource_id IS NOT NULL) THEN
1860 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1861 ELSIF(x_resource_id IS NULL) THEN
1862 x_return_status := FND_API.G_RET_STS_ERROR;
1863 END IF;
1864 ELSE -- x_return_status is success loop
1865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1866 END IF;
1867
1868 END Calculate_Load;
1869
1870 END CS_ASSIGN_RESOURCE_CON_PKG;