[Home] [Help]
PACKAGE BODY: APPS.CS_SR_TASK_AUTOASSIGN_PKG
Source
1 PACKAGE BODY CS_SR_TASK_AUTOASSIGN_PKG as
2 /* $Header: csxasrtb.pls 120.15.12020000.3 2013/03/21 11:58:29 nic ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_SR_TASK_AUTOASSIGN_PKG';
4
5 /***********************
6 Define Local Procedures
7 ***********************/
8 PROCEDURE Assign_Group
9 (p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
10 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
11 p_incident_id IN NUMBER,
12 p_total_emp IN NUMBER DEFAULT NULL,
13 p_party_name IN VARCHAR2 DEFAULT NULL,
14 p_service_request_rec IN CS_ServiceRequest_PUB.service_request_rec_type,
15 p_task_attribute_rec IN SR_Task_rec_type,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_group_id OUT NOCOPY NUMBER,
18 x_group_type OUT NOCOPY VARCHAR2,
19 x_territory_id OUT NOCOPY NUMBER,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2
22 );
23
24 PROCEDURE Assign_Owner
25 (p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
26 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
27 p_incident_id IN NUMBER,
28 p_total_emp IN NUMBER DEFAULT NULL,
29 p_party_name IN VARCHAR2 DEFAULT NULL,
30 p_param_resource_type IN VARCHAR2,
31 p_service_request_rec IN CS_ServiceRequest_PUB.service_request_rec_type,
32 p_task_attribute_rec IN SR_Task_rec_type,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_resource_id OUT NOCOPY NUMBER,
35 x_resource_type OUT NOCOPY VARCHAR2,
36 x_territory_id OUT NOCOPY NUMBER,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2
39 );
40
41 /*************************************************
42 -- This is the Main Procedure which gets the Group
43 -- and Resources back to the calling Program.
44 **************************************************/
45 PROCEDURE Assign_Task_Resource
46 (p_api_version IN NUMBER,
47 p_init_msg_list IN VARCHAR2,
48 p_commit IN VARCHAR2,
49 p_incident_id IN NUMBER,
50 p_service_request_rec IN CS_ServiceRequest_PUB.service_request_rec_type,
51 p_task_attribute_rec IN SR_Task_rec_type,
52 x_owner_group_id OUT NOCOPY NUMBER,
53 x_group_type OUT NOCOPY VARCHAR2,
54 x_owner_type OUT NOCOPY VARCHAR2,
55 x_owner_id OUT NOCOPY NUMBER,
56 x_territory_id OUT NOCOPY NUMBER,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2
60 ) IS
61
62 -- Define Local Variables
63 l_api_name CONSTANT VARCHAR2(30) := 'Assign_Task_Resource';
64 l_api_version CONSTANT NUMBER := 1.0;
65 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
66 l_location_id NUMBER := p_service_request_rec.incident_location_id;
67 l_ib_inv_comp_id NUMBER;
68 l_ib_inv_subcomp_id NUMBER;
69 l_no_of_employees NUMBER := NULL;
70 l_country VARCHAR2(60) := NULL;
71 l_province VARCHAR2(60) := NULL;
72 l_postal_code VARCHAR2(60) := NULL;
73 l_city VARCHAR2(60) := NULL;
74 l_state VARCHAR2(60) := NULL;
75 l_county VARCHAR2(60) := NULL;
76 l_party_name VARCHAR2(360) := NULL;
77 -- Return Status from Group, Owner and Resources Proc
78 l_grp_return_status VARCHAR2(1) := NULL;
79 l_own_return_status VARCHAR2(1) := NULL;
80 l_main_return_status VARCHAR2(1) ;
81 l_return_status VARCHAR2(1);
82 l_default_group_type VARCHAR2(30);
83 l_group_type VARCHAR2(30);
84 l_group_id NUMBER;
85 l_owner_id NUMBER;
86 l_resource_type VARCHAR2(30);
87 l_param_resource_type VARCHAR2(30);
88 l_owner VARCHAR2(360);
89 l_group_owner VARCHAR2(60);
90 l_update_own_flag VARCHAR2(1) := 'N';
91 l_update_grp_flag VARCHAR2(1) := 'N';
92 l_territory_id NUMBER;
93 -- For Messages
94 l_msg_count NUMBER;
95 l_msg_data VARCHAR2(2000);
96
97 CURSOR c_inc_rec IS
98 SELECT incident_id,
99 incident_number,
100 incident_status_id,
101 incident_type_id,
102 incident_urgency_id,
103 incident_severity_id,
104 incident_owner_id,
105 resource_type,
106 inventory_item_id,
107 customer_id,
108 account_id,
109 bill_to_site_use_id,
110 purchase_order_num,
111 ship_to_site_use_id,
112 problem_code,
113 expected_resolution_date,
114 actual_resolution_date,
115 customer_product_id,
116 install_site_use_id,
117 bill_to_contact_id,
118 ship_to_contact_id,
119 current_serial_number,
120 product_revision,
121 component_version,
122 subcomponent_version,
123 resolution_code,
124 org_id,
125 original_order_number,
126 workflow_process_id,
127 close_date,
128 contract_service_id,
129 contract_id,
130 contract_number,
131 project_number,
132 owner_group_id,
133 obligation_date,
134 caller_type,
135 platform_id,
136 platform_version,
137 db_version,
138 cp_revision_id,
139 inv_component_version,
140 language_id,
141 territory_id,
142 inv_organization_id,
143 cust_pref_lang_code,
144 comm_pref_code,
145 incident_address,
146 incident_city,
147 incident_state,
148 incident_country,
149 incident_province,
150 incident_postal_code,
151 incident_county,
152 sr_creation_channel,
153 coverage_type,
154 customer_site_id,
155 site_id,
156 incident_date,
157 category_id,
158 inv_platform_org_id,
159 incident_location_id,
160 incident_location_type,
161 -- Added for 11.5.10+
162 cp_component_id,
163 cp_subcomponent_id,
164 inv_component_id,
165 inv_subcomponent_id
166 FROM cs_incidents_all_b
167 WHERE incident_id = p_incident_id;
168
169 -- For 11.5.10+ Need to get the location_id from party sites
170 -- for location_type, HZ_PARTY_SITES
171 CURSOR c_inc_address(p_incident_location_id NUMBER) IS
172 SELECT country,province,state,city,postal_code,county
173 FROM hz_locations
174 WHERE location_id = p_incident_location_id;
175
176 CURSOR c_inc_party_site_address(p_party_site_id NUMBER) IS
177 SELECT location_id FROM hz_party_sites
178 WHERE party_site_id = p_party_site_id;
179
180 -- Added the following cursors for ER# 3811871.
181 CURSOR c_inv_comp_id(p_component_id NUMBER) IS
182 SELECT inventory_item_id
183 FROM csi_item_instances
184 WHERE instance_id = p_component_id;
185
186 CURSOR c_inv_subcomp_id(p_subcomponent_id NUMBER) IS
187 SELECT inventory_item_id
188 FROM csi_item_instances
189 WHERE instance_id = p_subcomponent_id;
190
191 -- Added cursor to fetch the number of Employees for a
192 -- customer, for ER# 4107660.
193 CURSOR c_cust_det(p_customer_id NUMBER) IS
194 SELECT employees_total, party_name
195 FROM hz_parties
196 WHERE party_id = p_customer_id;
197
198 l_inc_rec c_inc_rec%ROWTYPE;
199 l_service_request_rec CS_ServiceRequest_PUB.service_request_rec_type ;
200 l_service_req_rec CS_ServiceRequest_PUB.service_request_rec_type default p_service_request_rec;
201
202 BEGIN
203 -- Initialize message list if p_init_msg_list is set to TRUE
204 IF FND_API.To_Boolean(p_init_msg_list) THEN
205 FND_MSG_PUB.Initialize;
206 END IF;
207
208 -- Initialize API return status to success
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210 l_grp_return_status := FND_API.G_RET_STS_SUCCESS;
211 l_main_return_status := FND_API.G_RET_STS_SUCCESS;
212 -- Raise Error when both incident_id and the service request record is not
213 -- passed. The service request record is checked for null based on the
214 -- incident_type_id. If only incident_id is passed then fetch all the
215 -- territory attributes from cs_incidents_all_b
216 IF (p_incident_id IS NULL and p_service_request_rec.type_id IS NULL) THEN
217 RAISE FND_API.G_EXC_ERROR;
218 ELSE
219 IF (p_service_request_rec.type_id IS NULL) THEN
220 OPEN c_inc_rec;
221 FETCH c_inc_rec INTO l_inc_rec;
222 l_service_request_rec.customer_id := l_inc_rec.customer_id;
223 l_service_request_rec.type_id := l_inc_rec.incident_type_id;
224 l_service_request_rec.severity_id := l_inc_rec.incident_severity_id;
225 l_service_request_rec.urgency_id := l_inc_rec.incident_urgency_id;
226 l_service_request_rec.status_id := l_inc_rec.incident_status_id;
227 l_service_request_rec.problem_code := l_inc_rec.problem_code;
228 l_service_request_rec.sr_creation_channel := l_inc_rec.sr_creation_channel;
229 l_service_request_rec.inventory_item_id := l_inc_rec.inventory_item_id;
230 l_service_request_rec.inventory_org_id := l_inc_rec.inv_organization_id;
231 l_service_request_rec.comm_pref_code := l_inc_rec.comm_pref_code;
232 l_service_request_rec.platform_id := l_inc_rec.platform_id;
233 l_service_request_rec.inv_platform_org_id := l_inc_rec.inv_platform_org_id;
234 l_service_request_rec.category_id := l_inc_rec.category_id;
235 l_service_request_rec.cust_pref_lang_code := l_inc_rec.cust_pref_lang_code;
236 l_service_request_rec.coverage_type := l_inc_rec.coverage_type;
237 l_service_request_rec.customer_site_id := l_inc_rec.customer_site_id;
238 l_service_request_rec.site_id := l_inc_rec.site_id;
239 l_service_request_rec.request_date := l_inc_rec.incident_date;
240 l_service_request_rec.incident_country := l_inc_rec.incident_country;
241 l_service_request_rec.incident_city := l_inc_rec.incident_city;
242 l_service_request_rec.incident_state := l_inc_rec.incident_state;
243 l_service_request_rec.incident_province := l_inc_rec.incident_province;
244 l_service_request_rec.incident_postal_code := l_inc_rec.incident_postal_code;
245 l_service_request_rec.incident_county := l_inc_rec.incident_county;
246 l_service_request_rec.cp_component_id := l_inc_rec.cp_component_id;
247 l_service_request_rec.cp_subcomponent_id := l_inc_rec.cp_subcomponent_id;
248 l_service_request_rec.inv_component_id := l_inc_rec.inv_component_id;
249 l_service_request_rec.inv_subcomponent_id := l_inc_rec.inv_subcomponent_id;
250 l_service_request_rec.incident_location_id := l_inc_rec.incident_location_id;
251 l_service_request_rec.incident_location_type := l_inc_rec.incident_location_type;
252 l_service_request_rec.owner_group_id := l_inc_rec.owner_group_id;
253 l_service_request_rec.customer_product_id := l_inc_rec.customer_product_id;
254 l_service_request_rec.contract_service_id := l_inc_rec.contract_service_id;
255 l_service_request_rec.language_id := l_inc_rec.language_id;
256 CLOSE c_inc_rec;
257 l_service_req_rec := l_service_request_rec;
258 END IF;
259
260 -- Added the following for 11.5.10+
261 IF (l_service_req_rec.incident_location_id is not null) THEN
262 IF (l_service_req_rec.incident_location_type = 'HZ_PARTY_SITE') THEN
263 OPEN c_inc_party_site_address(l_service_req_rec.incident_location_id);
264 FETCH c_inc_party_site_address INTO l_location_id;
265 IF (c_inc_party_site_address%NOTFOUND) THEN
266 l_location_id := NULL;
267 END IF;
268 CLOSE c_inc_party_site_address;
269 -- Added for bug 5228561
270 ELSE
271 IF (l_service_req_rec.incident_location_type = 'HZ_LOCATION') THEN
272 l_location_id := l_service_req_rec.incident_location_id;
273 END IF;
274 END IF;
275 OPEN c_inc_address(l_location_id);
276 FETCH c_inc_address INTO l_country,l_province,l_state,l_city,l_postal_code,
277 l_county;
278 IF (c_inc_address%NOTFOUND) THEN
279 NULL;
280 END IF;
281 CLOSE c_inc_address;
282 l_service_req_rec.incident_country := l_country;
283 l_service_req_rec.incident_city := l_city;
284 l_service_req_rec.incident_postal_code := l_postal_code;
285 l_service_req_rec.incident_state := l_state;
286 l_service_req_rec.incident_province := l_province;
287 l_service_req_rec.incident_county := l_county;
288 END IF;
289
290 -- Added for 11.5.10+ ER# 3811871
291 IF (l_service_req_rec.customer_product_id IS NOT NULL) THEN
292 OPEN c_inv_comp_id(l_service_req_rec.cp_component_id);
293 FETCH c_inv_comp_id INTO l_ib_inv_comp_id;
294 CLOSE c_inv_comp_id;
295
296 OPEN c_inv_subcomp_id(l_service_req_rec.cp_subcomponent_id);
297 FETCH c_inv_subcomp_id INTO l_ib_inv_subcomp_id;
298 CLOSE c_inv_subcomp_id;
299
300 l_service_req_rec.cp_component_id := l_ib_inv_comp_id;
301 l_service_req_rec.cp_subcomponent_id := l_ib_inv_subcomp_id;
302 END IF;
303
304 OPEN c_cust_det(l_service_req_rec.customer_id);
305 FETCH c_cust_det INTO l_no_of_employees, l_party_name;
306 IF (c_cust_det%NOTFOUND) THEN
307 l_no_of_employees := NULL;
308 l_party_name := NULL;
309 END IF;
310 CLOSE c_cust_det;
311
312 IF (NVL(FND_PROFILE.VALUE('CS_SR_TASK_OWNER_AUTO_ASSIGN_LEVEL'),'INDIVIDUAL') = 'INDIVIDUAL') THEN
313 Assign_Owner
314 ( p_init_msg_list => p_init_msg_list,
315 p_commit => p_commit,
316 p_incident_id => p_incident_id,
317 p_total_emp => l_no_of_employees,
318 p_party_name => l_party_name,
319 p_param_resource_type => 'RS_INDIVIDUAL',
320 p_service_request_rec => l_service_req_rec,
321 p_task_attribute_rec => p_task_attribute_rec,
322 x_return_status => x_return_status,
323 x_resource_id => l_owner_id,
324 x_resource_type => l_resource_type,
325 x_territory_id => l_territory_id,
326 x_msg_count => x_msg_count,
327 x_msg_data => x_msg_data
328 );
329 END IF; -- Profile value is INDIVIDUAL
330 IF (l_owner_id IS NULL OR
331 FND_PROFILE.VALUE('CS_SR_TASK_OWNER_AUTO_ASSIGN_LEVEL') = 'GROUP') THEN
332 -- Call the Assign Group Procedure to return the Group and Group Type
333 Assign_Group
334 ( p_init_msg_list => p_init_msg_list,
335 p_commit => p_commit,
336 p_incident_id => p_incident_id,
337 p_total_emp => l_no_of_employees,
338 p_party_name => l_party_name,
339 p_service_request_rec => l_service_req_rec,
340 p_task_attribute_rec => p_task_attribute_rec,
341 x_return_status => x_return_status,
342 x_group_id => l_group_id,
343 x_group_type => l_group_type,
344 x_territory_id => l_territory_id,
345 x_msg_count => x_msg_count,
346 x_msg_data => x_msg_data
347 );
348 END IF; -- l_owner_id IS NULL
349 END IF; -- p_incident_id IS NULL
350
351 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
352 x_owner_id := NULL;
353 x_owner_type := NULL;
354 x_owner_group_id := NULL;
355 x_group_type := NULL;
356 FND_MSG_PUB.Initialize;
357 FND_MESSAGE.Set_Name('CS', 'CS_SR_TASK_NO_OWNER');
358 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
359 FND_MSG_PUB.Add;
360 RAISE FND_API.G_EXC_ERROR;
361 ELSE
362 IF (l_owner_id IS NULL AND l_group_id IS NULL) THEN
363 -- FND_MSG_PUB.Initialize;
364 FND_MESSAGE.Set_Name('CS', 'CS_SR_TASK_NO_OWNER');
365 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full||l_service_req_rec.platform_id);
366 FND_MSG_PUB.Add;
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
368 ELSE
369 x_owner_id := l_owner_id;
370 x_owner_type := l_resource_type;
371 x_owner_group_id := l_group_id;
372 x_group_type := l_group_type;
373 x_territory_id := l_territory_id;
374 x_return_status := x_return_status;
375 END IF;
376 END IF;
377
378 EXCEPTION
379 WHEN FND_API.G_EXC_ERROR THEN
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 FND_MSG_PUB.Count_And_Get
382 ( p_count => x_msg_count,
383 p_data => x_msg_data
384 );
385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 FND_MSG_PUB.Count_And_Get
388 ( p_count => x_msg_count,
389 p_data => x_msg_data
390 );
391
392 END Assign_Task_Resource;
393
394 /***************************************************
395 -- This Procedure returns the Group if not passed.
396 ***************************************************/
397 PROCEDURE Assign_Group
398 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
399 p_commit IN VARCHAR2 := FND_API.G_FALSE,
400 p_incident_id IN NUMBER,
401 p_total_emp IN NUMBER,
402 p_party_name IN VARCHAR2,
403 p_service_request_rec IN CS_Servicerequest_PUB.service_request_rec_type,
404 p_task_attribute_rec IN SR_Task_rec_type,
405 x_return_status OUT NOCOPY VARCHAR2,
406 x_group_id OUT NOCOPY NUMBER,
407 x_group_type OUT NOCOPY VARCHAR2,
408 x_territory_id OUT NOCOPY NUMBER,
409 x_msg_count OUT NOCOPY NUMBER,
410 x_msg_data OUT NOCOPY VARCHAR2
411 ) IS
412
413 -- Define Local Variables
414 n NUMBER;
415 -- Input and output data structures
416 l_Assign_Groups_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type;
417 l_task_am_rec JTF_ASSIGN_PUB.JTF_Srv_Task_rec_type ;
418 -- Qualifier values
419 l_incident_id NUMBER := p_incident_id;
420 l_inv_item_id NUMBER := NULL;
421 l_inv_org_id NUMBER := NULL;
422 l_party_id NUMBER := p_service_request_rec.customer_id;
423 l_cust_category VARCHAR2(30) := NULL;
424 l_area_code VARCHAR2(60) := NULL;
425 l_contract_service_id NUMBER := p_service_request_rec.contract_service_id;
426 l_cust_prod_id NUMBER := p_service_request_rec.customer_product_id;
427 l_contract_res_flag VARCHAR2(3);
428 l_ib_res_flag VARCHAR2(3);
429 --parameters
430 l_no_of_resources NUMBER := 1;
431 l_business_process_id NUMBER;
432 l_day_week VARCHAR2(10) ;
433 l_time_day VARCHAR2(10) ;
434
435 l_cs_sr_tsk_chk_res_cal_avl VARCHAR2(1) ; --gasankar Calendar check feature added
436 l_start_date Date ;
437 l_end_date Date ;
438 l_territory_flag VARCHAR2(3); -- Added By NIC
439
440
441 c_customer_phone_id NUMBER := p_service_request_rec.customer_phone_id;
442
443 -- List of Cursors used
444 CURSOR C_CONTRACT(l_contract_service_id number) IS
445 SELECT to_number(object1_id1), to_number(object1_id2)
446 FROM okc_k_items
447 WHERE cle_id = l_contract_service_id;
448
449 /*--Bug 5255184 Modified the c_area_code query
450 CURSOR c_area_code IS
451 SELECT hzp.phone_area_code
452 FROM hz_contact_points hzp
453 WHERE hzp.contact_point_id = c_customer_phone_id;*/
454
455 -- bug 14156822
456 CURSOR c_area_code(c_incident_id NUMBER) IS
457 SELECT hzp.phone_area_code
458 FROM hz_contact_points hzp,
459 cs_incidents_all_b csi
460 WHERE csi.incident_id = c_incident_id
461 AND csi.customer_phone_id = hzp.contact_point_id
462 AND csi.customer_phone_id IS NOT NULL;
463
464
465 BEGIN
466
467 -- Initialize API return status to success
468 x_return_status := FND_API.G_RET_STS_SUCCESS;
469
470 -- Proceed even if the group_type is null
471 l_incident_id := p_incident_id;
472
473 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_TASK_AUTO_ASSIGN') = 'YES') THEN
474 SELECT business_process_id INTO l_business_process_id
475 FROM cs_incident_types
476 WHERE incident_type_id = p_service_request_rec.type_id;
477 END IF;
478
479 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
480 -- Assigning component and subcomponent id directly to the am rec
481
482 IF (l_cust_prod_id IS NOT NULL) THEN
483 l_task_am_rec.item_component := p_service_request_rec.cp_component_id ;
484 l_task_am_rec.item_subcomponent := p_service_request_rec.cp_subcomponent_id ;
485 ELSE
486 l_task_am_rec.item_component := p_service_request_rec.inv_component_id ;
487 l_task_am_rec.item_subcomponent := p_service_request_rec.inv_subcomponent_id ;
488 END IF;
489
490 --Bug 5255184 Modified the c_area_code
491 OPEN c_area_code(l_incident_id);
492 FETCH c_area_code INTO l_area_code;
493 IF (c_area_code%NOTFOUND) THEN
494 l_area_code := NULL;
495 -- fnd_log_repository.string_unchecked_internal(1,'3-5712811001', 'area code not foiund in c_area_code for incident_id '||l_incident_id);
496
497 END IF;
498 CLOSE c_area_code;
499
500
501 -- Assign the values to the AM Record Type
502 -- Assign the Task Related Information
503 l_task_am_rec.task_type_id := p_task_attribute_rec.task_type_id;
504 l_task_am_rec.task_status_id := p_task_attribute_rec.task_status_id;
505 l_task_am_rec.task_priority_id := p_task_attribute_rec.task_priority_id;
506 l_task_am_rec.num_of_employees := p_total_emp;
507 -- Assign the Service Request Related Information
508 l_task_am_rec.service_request_id := p_incident_id;
509 l_task_am_rec.party_id := p_service_request_rec.customer_id;
510 l_task_am_rec.incident_type_id := p_service_request_rec.type_id;
511 l_task_am_rec.incident_severity_id := p_service_request_rec.severity_id;
512 l_task_am_rec.incident_urgency_id := p_service_request_rec.urgency_id;
513 l_task_am_rec.incident_status_id := p_service_request_rec.status_id;
514 l_task_am_rec.problem_code := p_service_request_rec.problem_code;
515 l_task_am_rec.platform_id := p_service_request_rec.platform_id;
516 l_task_am_rec.sr_creation_channel := p_service_request_rec.sr_creation_channel;
517 l_task_am_rec.inventory_item_id := p_service_request_rec.inventory_item_id;
518 l_task_am_rec.squal_char12 := p_service_request_rec.problem_code;
519 l_task_am_rec.squal_char13 := p_service_request_rec.comm_pref_code;
520 l_task_am_rec.squal_num12 := p_service_request_rec.platform_id;
521 l_task_am_rec.squal_num13 := p_service_request_rec.inv_platform_org_id;
522 l_task_am_rec.squal_num14 := p_service_request_rec.category_id;
523 l_task_am_rec.squal_num15 := p_service_request_rec.inventory_item_id;
524 l_task_am_rec.squal_num16 := p_service_request_rec.inventory_org_id;
525 -- Passing SR Group Owner for Bug# 3564691
526 l_task_am_rec.squal_num17 := p_service_request_rec.owner_group_id;
527 l_task_am_rec.squal_num30 := p_service_request_rec.language_id;
528 l_task_am_rec.squal_char20 := p_service_request_rec.cust_pref_lang_code;
529 l_task_am_rec.squal_char21 := p_service_request_rec.coverage_type;
530 l_task_am_rec.area_code := l_area_code;
531 l_task_am_rec.party_site_id := p_service_request_rec.customer_site_id;
532 l_task_am_rec.customer_site_id := p_task_attribute_rec.customer_site_id; --p_service_request_rec.customer_site_id; --gasankar sun
533 l_task_am_rec.support_site_id := p_service_request_rec.site_id;
534 l_task_am_rec.country := p_service_request_rec.incident_country;
535 l_task_am_rec.city := p_service_request_rec.incident_city;
536 l_task_am_rec.postal_code := p_service_request_rec.incident_postal_code;
537 l_task_am_rec.state := p_service_request_rec.incident_state;
538 l_task_am_rec.province := p_service_request_rec.incident_province;
539 l_task_am_rec.county := p_service_request_rec.incident_county;
540 l_task_am_rec.comp_name_range := p_party_name;
541 l_task_am_rec.SQUAL_NUM60 := p_task_attribute_rec.SQUAL_NUM60 ; -- gasankar sun
542
543 -- 12.1.2 Enhancement
544 Begin
545 SELECT to_char(sysdate, 'd'), to_char(sysdate, 'hh24:mi')
546 INTO l_day_week, l_time_day
547 FROM cs_incidents_all_b
548 WHERE incident_id = l_incident_id ;
549 Exception
550 When Others then
551 l_time_day := null ;
552 l_day_week := null ;
553 End ;
554
555 l_task_am_rec.DAY_OF_WEEK := l_day_week ;
556 l_task_am_rec.TIME_OF_DAY := l_time_day ;
557
558 -- Contract Item and Org dtls
559 IF (l_contract_service_id IS NOT NULL) THEN
560 OPEN c_contract(l_contract_service_id);
561 FETCH c_contract INTO l_inv_item_id,l_inv_org_id;
562 IF (c_contract%NOTFOUND) THEN
563 NULL;
564 END IF;
565 CLOSE c_contract;
566 END IF;
567 -- Assign the values to the qualifiers
568 l_task_am_rec.squal_num18 := l_inv_item_id;
569 l_task_am_rec.squal_num19 := l_inv_org_id;
570
571 -- If customer product id is not null, then set ib_preferred_resource_flag
572 -- to 'Y'.If contract line id is not null, then set
573 -- contract_preferred_resource flag to 'Y'.
574 IF (l_contract_service_id IS NOT NULL) THEN
575 IF (FND_PROFILE.VALUE('CS_TASK_CONTRACT_OWNER')= 'Y') THEN --Added By Nic
576 l_contract_res_flag := 'Y';
577 ELSE
578 l_contract_res_flag := 'N';
579 END IF;
580 END IF;
581 IF (l_cust_prod_id IS NOT NULL) THEN
582 IF (FND_PROFILE.VALUE('CS_TASK_IB_OWNER')= 'Y') THEN --Added By Nic
583 l_ib_res_flag := 'Y';
584 ELSE
585 l_ib_res_flag := 'N';
586 END IF;
587 END IF;
588 IF (FND_PROFILE.VALUE('CS_TASK_TERRITORY_OWNER')= 'Y') THEN --Added By Nic
589 l_territory_flag :='Y';
590 ELSE
591 l_territory_flag :='N';
592 END IF;
593
594 FND_PROFILE.Get('CS_SR_TSK_CHK_RES_CAL_AVL', l_cs_sr_tsk_chk_res_cal_avl); --gasankar Calendar check feature added
595
596 If nvl(l_cs_sr_tsk_chk_res_cal_avl, 'N') <> 'N' Then
597 l_start_date := sysdate ;
598 l_end_date := sysdate ;
599 End If ;
600
601 JTF_ASSIGN_PUB.GET_Assign_Resources
602 ( p_api_version => 1.0,
603 p_init_msg_list => 'T',
604 p_commit => 'F',
605 p_resource_id => NULL,
606 p_resource_type => 'RS_GROUP',
607 p_role => NULL,
608 p_no_of_resources => l_no_of_resources,
609 p_auto_select_flag => 'N',
610 p_contracts_preferred_engineer => nvl(l_contract_res_flag,'N'),
611 p_ib_preferred_engineer => nvl(l_ib_res_flag,'N'),
612 p_contract_id => l_contract_service_id,
613 p_customer_product_id => l_cust_prod_id,
614 p_effort_duration => NULL,
615 p_effort_uom => NULL,
616 p_start_date => l_start_date,
617 p_end_date => l_end_date,
618 p_territory_flag => nvl(l_territory_flag,'N'),
619 p_calendar_flag => nvl(l_cs_sr_tsk_chk_res_cal_avl, 'N') ,
620 p_calendar_check => nvl(l_cs_sr_tsk_chk_res_cal_avl, 'N') ,
621 p_web_availability_flag => 'Y',
622 p_filter_excluded_resource => 'Y',
623 p_category_id => NULL,
624 p_inventory_item_id => NULL,
625 p_inventory_org_id => NULL,
626 p_column_list => NULL,
627 p_calling_doc_id => NULL,
628 p_calling_doc_type => 'SR',
629 p_sr_rec => NULL,
630 p_sr_task_rec => l_task_am_rec,
631 p_defect_rec => NULL,
632 p_business_process_id => l_business_process_id,
633 p_business_process_date => p_service_request_rec.request_date,
634 x_Assign_Resources_tbl => l_Assign_Groups_tbl,
635 x_return_status => x_return_status,
636 x_msg_count => x_msg_count,
637 x_msg_data => x_msg_data
638 );
639
640 n := l_Assign_Groups_tbl.FIRST;
641
642 IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
643 IF (l_Assign_Groups_tbl.exists(n)) THEN
644 x_group_id := l_Assign_Groups_tbl(n).resource_id;
645 x_group_type := l_Assign_Groups_tbl(n).resource_type;
646 x_territory_id := l_Assign_Groups_tbl(n).terr_id ;
647 ELSE
648 x_group_id := NULL;
649 x_group_type := NULL;
650 END IF;
651 END IF;
652
653 END Assign_Group;
654
655 /**************************************************************
656 -- This Procedure returns the individual Owner from the Group
657 -- returned by the Assign_Group Procedure.
658 **************************************************************/
659 PROCEDURE Assign_Owner
660 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
661 p_commit IN VARCHAR2 := FND_API.G_FALSE,
662 p_incident_id IN NUMBER,
663 p_total_emp IN NUMBER,
664 p_party_name IN VARCHAR2,
665 p_param_resource_type IN VARCHAR2,
666 p_service_request_rec IN CS_ServiceRequest_PUB.service_request_rec_type,
667 p_task_attribute_rec IN SR_Task_rec_type,
668 x_return_status OUT NOCOPY VARCHAR2,
669 x_resource_id OUT NOCOPY NUMBER,
670 x_resource_type OUT NOCOPY VARCHAR2,
671 x_territory_id OUT NOCOPY NUMBER,
672 x_msg_count OUT NOCOPY NUMBER,
673 x_msg_data OUT NOCOPY VARCHAR2
674 ) IS
675
676 -- Input and output data structures
677 l_Assign_Owner_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type ;
678 l_task_am_rec JTF_ASSIGN_PUB.JTF_Srv_Task_rec_type ;
679 -- Message Variables
680 l_index BINARY_INTEGER;
681 l_count NUMBER;
682 l_counter NUMBER;
683 p NUMBER;
684 -- Qualifier values
685 l_incident_id NUMBER := p_incident_id;
686 l_contract_service_id NUMBER := p_service_request_rec.contract_service_id;
687 l_cust_prod_id NUMBER := p_service_request_rec.customer_product_id;
688 l_contract_res_flag VARCHAR2(3);
689 l_ib_res_flag VARCHAR2(3);
690 l_inv_item_id NUMBER := NULL;
691 l_inv_org_id NUMBER := NULL;
692 l_inv_category_id NUMBER := NULL ;
693 l_party_id NUMBER := p_service_request_rec.customer_id;
694 l_class_code VARCHAR2(30) := NULL;
695 -- Passing parameters
696 l_no_of_resources NUMBER := NULL;
697 l_area_code VARCHAR2(50) ;
698 l_business_process_id NUMBER;
699 l_day_week VARCHAR2(10) ;
700 l_time_day VARCHAR2(10) ;
701
702 l_cs_sr_tsk_chk_res_cal_avl VARCHAR2(1) ; --gasankar Calendar check feature added
703 l_start_date Date ;
704 l_end_date Date ;
705
706 l_territory_flag VARCHAR2(3); -- Added By NIC
707 l_task_id NUMBER := p_task_attribute_rec.task_id;
708 l_address_id VARCHAR2(50) ;
709
710 -- List of Cursors
711 CURSOR c_contract(l_contract_service_id NUMBER)IS
712 SELECT TO_NUMBER(object1_id1), TO_NUMBER(object1_id2)
713 FROM okc_k_items
714 WHERE cle_id = l_contract_service_id;
715
716 CURSOR c_area_code(c_incident_id NUMBER) IS
717 SELECT hzp.phone_area_code
718 FROM hz_contact_points hzp,
719 cs_incidents_all_b csi
720 WHERE csi.incident_id = c_incident_id
721 AND csi.customer_phone_id = hzp.contact_point_id
722 AND csi.customer_phone_id IS NOT NULL;
723
724 cursor c_address(c_task_id number) IS --added by nic
725 select address_id
726 FROM jtf_tasks_b
727 where task_id=c_task_id;
728
729
730 BEGIN
731 -- Initialize API return status to success
732 x_return_status := FND_API.G_RET_STS_SUCCESS;
733
734 -- Proceed even if the group_id is null
735 -- l_group_id := p_group_id ;
736
737 --
738 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_TASK_AUTO_ASSIGN') = 'YES') THEN
739 SELECT business_process_id INTO l_business_process_id
740 FROM cs_incident_types
741 WHERE incident_type_id = p_service_request_rec.type_id;
742 END IF;
743
744 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
745 -- Assigning component and subcomponent id directly to the am rec
746
747 IF (l_cust_prod_id IS NOT NULL) THEN
748 l_task_am_rec.item_component := p_service_request_rec.cp_component_id ;
749 l_task_am_rec.item_subcomponent := p_service_request_rec.cp_subcomponent_id ;
750 ELSE
751 l_task_am_rec.item_component := p_service_request_rec.inv_component_id ;
752 l_task_am_rec.item_subcomponent := p_service_request_rec.inv_subcomponent_id ;
753 END IF;
754
755 OPEN c_area_code(l_incident_id);
756 FETCH c_area_code INTO l_area_code;
757 IF (c_area_code%NOTFOUND) THEN
758 l_area_code := NULL;
759 END IF;
760 CLOSE c_area_code;
761
762 OPEN c_address(l_task_id); -- Added by nic
763 FETCH c_address INTO l_address_id;
764 IF (c_address%notfound) THEN
765 l_address_id :=NULL;
766 END IF;
767 close c_address;
768
769 -- Set the Task Related Information
770 l_task_am_rec.task_type_id := p_task_attribute_rec.task_type_id;
771 l_task_am_rec.task_status_id := p_task_attribute_rec.task_status_id;
772 l_task_am_rec.task_priority_id := p_task_attribute_rec.task_priority_id;
773 l_task_am_rec.num_of_employees := p_total_emp;
774 -- Set the Service Request Related Information
775 l_task_am_rec.service_request_id := p_incident_id;
776 l_task_am_rec.party_id := p_service_request_rec.customer_id;
777 l_task_am_rec.incident_type_id := p_service_request_rec.type_id;
778 l_task_am_rec.incident_severity_id := p_service_request_rec.severity_id;
779 l_task_am_rec.incident_urgency_id := p_service_request_rec.urgency_id;
780 l_task_am_rec.incident_status_id := p_service_request_rec.status_id;
781 l_task_am_rec.problem_code := p_service_request_rec.problem_code;
782 l_task_am_rec.platform_id := p_service_request_rec.platform_id;
783 l_task_am_rec.sr_creation_channel := p_service_request_rec.sr_creation_channel;
784 l_task_am_rec.inventory_item_id := p_service_request_rec.inventory_item_id;
785 l_task_am_rec.squal_char12 := p_service_request_rec.problem_code;
786 l_task_am_rec.squal_char13 := p_service_request_rec.comm_pref_code;
787 l_task_am_rec.squal_char20 := p_service_request_rec.cust_pref_lang_code ;
788 l_task_am_rec.squal_char21 := p_service_request_rec.coverage_type;
789 l_task_am_rec.squal_num12 := p_service_request_rec.platform_id;
790 l_task_am_rec.squal_num13 := p_service_request_rec.inv_platform_org_id;
791 l_task_am_rec.squal_num14 := p_service_request_rec.category_id;
792 l_task_am_rec.squal_num15 := p_service_request_rec.inventory_item_id;
793 l_task_am_rec.squal_num16 := p_service_request_rec.inventory_org_id;
794 -- Passing SR Group Owner for Bug# 3564691
795 l_task_am_rec.squal_num17 := p_service_request_rec.owner_group_id;
796 l_task_am_rec.squal_num30 := p_service_request_rec.language_id;
797 l_task_am_rec.area_code := l_area_code;
798 -- l_task_am_rec.party_site_id := p_service_request_rec.customer_site_id;
799 l_task_am_rec.party_site_id := l_address_id; -- added by nic
800 l_task_am_rec.customer_site_id := p_task_attribute_rec.customer_site_id; --p_service_request_rec.customer_site_id; --gasankar sun
801 l_task_am_rec.support_site_id := p_service_request_rec.site_id;
802 l_task_am_rec.country := p_service_request_rec.incident_country;
803 l_task_am_rec.city := p_service_request_rec.incident_city;
804 l_task_am_rec.postal_code := p_service_request_rec.incident_postal_code;
805 l_task_am_rec.state := p_service_request_rec.incident_state;
806 l_task_am_rec.province := p_service_request_rec.incident_province;
807 l_task_am_rec.county := p_service_request_rec.incident_county;
808 l_task_am_rec.comp_name_range := p_party_name;
809 l_task_am_rec.SQUAL_NUM60 := p_task_attribute_rec.SQUAL_NUM60 ; -- gasankar sun
810
811
812 -- 12.1.2 Enhancement
813 Begin
814 SELECT to_char(sysdate, 'd'), to_char(sysdate, 'hh24:mi')
815 INTO l_day_week, l_time_day
816 FROM cs_incidents_all_b
817 WHERE incident_id = l_incident_id ;
818 Exception
819 When Others then
820 l_time_day := null ;
821 l_day_week := null ;
822 End ;
823
824 l_task_am_rec.DAY_OF_WEEK := l_day_week ;
825 l_task_am_rec.TIME_OF_DAY := l_time_day ;
826
827 --Contract Item and Org dtls
828 IF (l_contract_service_id IS NOT NULL) THEN
829 OPEN c_contract(l_contract_service_id);
830 FETCH c_contract INTO l_inv_item_id,l_inv_org_id;
831 IF c_contract%NOTFOUND THEN
832 NULL;
833 END IF;
834 CLOSE c_contract;
835 END IF;
836 -- Assign it to the AM record type For contracts
837 l_task_am_rec.squal_num18 := l_inv_item_id;
838 l_task_am_rec.squal_num19 := l_inv_org_id;
839 l_task_am_rec.squal_char11 := null;
840
841 -- If customer product id is not null, then set ib_preferred_resource_flag
842 -- to 'Y'.If contract line id is not null, then set
843 -- contract_preferred_resource flag to 'Y'.
844 IF (l_contract_service_id IS NOT NULL) THEN
845 IF (FND_PROFILE.VALUE('CS_TASK_CONTRACT_OWNER')= 'Y') THEN --Added By Nic
846 l_contract_res_flag := 'Y';
847 ELSE
848 l_contract_res_flag := 'N';
849 END IF;
850 END IF;
851 IF (l_cust_prod_id IS NOT NULL) THEN
852 IF (FND_PROFILE.VALUE('CS_TASK_IB_OWNER')= 'Y') THEN --Added By Nic
853 l_ib_res_flag := 'Y';
854 ELSE
855 l_ib_res_flag := 'N';
856 END IF;
857 END IF;
858 IF (FND_PROFILE.VALUE('CS_TASK_TERRITORY_OWNER')= 'Y') THEN --Added By Nic
859 l_territory_flag :='Y';
860 ELSE
861 l_territory_flag :='N';
862 END IF;
863
864 FND_PROFILE.Get('CS_SR_TSK_CHK_RES_CAL_AVL', l_cs_sr_tsk_chk_res_cal_avl); --gasankar Calendar check feature added
865
866 If nvl(l_cs_sr_tsk_chk_res_cal_avl, 'N') <> 'N' Then
867 l_start_date := sysdate ;
868 l_end_date := sysdate ;
869 End If ;
870
871 JTF_ASSIGN_PUB.GET_Assign_Resources
872 ( p_api_version => 1.0,
873 p_init_msg_list => null,
874 p_commit => 'F',
875 --p_resource_id => l_group_id,
876 p_resource_type => 'RS_INDIVIDUAL',
877 p_role => NULL,
878 p_no_of_resources => l_no_of_resources,
879 p_auto_select_flag => 'N',
880 p_ib_preferred_engineer => nvl(l_ib_res_flag,'N'),
881 p_contracts_preferred_engineer => nvl(l_contract_res_flag,'N'),
882 p_contract_id => l_contract_service_id,
883 p_customer_product_id => l_cust_prod_id,
884 p_effort_duration => NULL,
885 p_effort_uom => NULL,
886 p_start_date => l_start_date,
887 p_end_date => l_end_date,
888 p_territory_flag => nvl(l_territory_flag,'N'),
889 p_calendar_flag => nvl(l_cs_sr_tsk_chk_res_cal_avl, 'N') ,
890 p_calendar_check => nvl(l_cs_sr_tsk_chk_res_cal_avl, 'N') ,
891 --p_web_availability_flag => 'Y',
892 p_filter_excluded_resource => 'Y',
893 p_category_id => NULL,
894 p_inventory_item_id => NULL,
895 p_inventory_org_id => NULL,
896 p_column_list => NULL,
897 p_calling_doc_id => NULL,
898 p_calling_doc_type => 'SR',
899 p_sr_rec => NULL,
900 p_sr_task_rec => l_task_am_rec,
901 p_defect_rec => NULL,
902 p_business_process_id => l_business_process_id,
903 p_business_process_date => p_service_request_rec.request_date,
904 x_Assign_Resources_tbl => l_Assign_Owner_tbl,
905 x_return_status => x_return_status,
906 x_msg_count => x_msg_count,
907 x_msg_data => x_msg_data
908 );
909
910 --Bug 7168029
911 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
912 p := l_Assign_Owner_tbl.FIRST ;
913
914 IF (l_Assign_Owner_tbl.COUNT > 1) THEN
915 l_count := l_Assign_Owner_tbl.COUNT;
916 l_index := l_Assign_Owner_tbl.FIRST;
917 l_counter := l_Assign_Owner_tbl.FIRST ;
918
919 x_resource_id := l_Assign_Owner_tbl(l_index).resource_id;
920 x_resource_type := l_Assign_Owner_tbl(l_index).resource_type;
921 x_territory_id := l_Assign_Owner_tbl(l_index).terr_id;
922
923 WHILE l_index <= l_count
924 LOOP
925 if l_Assign_Owner_tbl(l_index).primary_contact_flag ='Y' then
926 x_resource_id := l_Assign_Owner_tbl(l_index).resource_id;
927 x_resource_type := l_Assign_Owner_tbl(l_index).resource_type;
928 x_territory_id := l_Assign_Owner_tbl(l_index).terr_id;
929 --l_resource_set:='Y';
930 return;
931 end if;
932 l_index := l_index + 1;
933 END LOOP;
934 ELSIF (l_Assign_Owner_tbl.COUNT = 1) THEN
935 x_resource_id := l_Assign_Owner_tbl(p).resource_id ;
936 x_resource_type := l_Assign_Owner_tbl(p).resource_type ;
937 x_territory_id := l_Assign_Owner_tbl(p).terr_id ; --Bug fix : 5622725 - Added by bkanimoz
938
939 END IF;
940 END IF ; -- Return status S
941 END Assign_Owner;
942
943 END CS_SR_TASK_AUTOASSIGN_PKG;