[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.8.12000000.2 2007/04/23 09:33:31 gasankar 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
204 -- Initialize message list if p_init_msg_list is set to TRUE
205 IF FND_API.To_Boolean(p_init_msg_list) THEN
206 FND_MSG_PUB.Initialize;
207 END IF;
208
209 -- Initialize API return status to success
210 x_return_status := FND_API.G_RET_STS_SUCCESS;
211 l_grp_return_status := FND_API.G_RET_STS_SUCCESS;
212 l_main_return_status := FND_API.G_RET_STS_SUCCESS;
213 -- Raise Error when both incident_id and the service request record is not
214 -- passed. The service request record is checked for null based on the
215 -- incident_type_id. If only incident_id is passed then fetch all the
216 -- territory attributes from cs_incidents_all_b
217 IF (p_incident_id IS NULL and p_service_request_rec.type_id IS NULL) THEN
218 RAISE FND_API.G_EXC_ERROR;
219 ELSE
220 IF (p_service_request_rec.type_id IS NULL) THEN
221 OPEN c_inc_rec;
222 FETCH c_inc_rec INTO l_inc_rec;
223 l_service_request_rec.customer_id := l_inc_rec.customer_id;
224 l_service_request_rec.type_id := l_inc_rec.incident_type_id;
225 l_service_request_rec.severity_id := l_inc_rec.incident_severity_id;
226 l_service_request_rec.urgency_id := l_inc_rec.incident_urgency_id;
227 l_service_request_rec.status_id := l_inc_rec.incident_status_id;
228 l_service_request_rec.problem_code := l_inc_rec.problem_code;
229 l_service_request_rec.sr_creation_channel := l_inc_rec.sr_creation_channel;
230 l_service_request_rec.inventory_item_id := l_inc_rec.inventory_item_id;
231 l_service_request_rec.inventory_org_id := l_inc_rec.inv_organization_id;
232 l_service_request_rec.comm_pref_code := l_inc_rec.comm_pref_code;
233 l_service_request_rec.platform_id := l_inc_rec.platform_id;
234 l_service_request_rec.inv_platform_org_id := l_inc_rec.inv_platform_org_id;
235 l_service_request_rec.category_id := l_inc_rec.category_id;
236 l_service_request_rec.cust_pref_lang_code := l_inc_rec.cust_pref_lang_code;
237 l_service_request_rec.coverage_type := l_inc_rec.coverage_type;
238 l_service_request_rec.customer_site_id := l_inc_rec.customer_site_id;
239 l_service_request_rec.site_id := l_inc_rec.site_id;
240 l_service_request_rec.request_date := l_inc_rec.incident_date;
241 l_service_request_rec.incident_country := l_inc_rec.incident_country;
242 l_service_request_rec.incident_city := l_inc_rec.incident_city;
243 l_service_request_rec.incident_state := l_inc_rec.incident_state;
244 l_service_request_rec.incident_province := l_inc_rec.incident_province;
245 l_service_request_rec.incident_postal_code := l_inc_rec.incident_postal_code;
246 l_service_request_rec.incident_county := l_inc_rec.incident_county;
247 l_service_request_rec.cp_component_id := l_inc_rec.cp_component_id;
248 l_service_request_rec.cp_subcomponent_id := l_inc_rec.cp_subcomponent_id;
249 l_service_request_rec.inv_component_id := l_inc_rec.inv_component_id;
250 l_service_request_rec.inv_subcomponent_id := l_inc_rec.inv_subcomponent_id;
251 l_service_request_rec.incident_location_id := l_inc_rec.incident_location_id;
252 l_service_request_rec.incident_location_type := l_inc_rec.incident_location_type;
253 l_service_request_rec.owner_group_id := l_inc_rec.owner_group_id;
254 l_service_request_rec.customer_product_id := l_inc_rec.customer_product_id;
255 l_service_request_rec.contract_service_id := l_inc_rec.contract_service_id;
256 l_service_request_rec.language_id := l_inc_rec.language_id;
257 CLOSE c_inc_rec;
258 l_service_req_rec := l_service_request_rec;
259 END IF;
260
261 -- Added the following for 11.5.10+
262 IF (l_service_req_rec.incident_location_id is not null) THEN
263 IF (l_service_req_rec.incident_location_type = 'HZ_PARTY_SITE') THEN
264 OPEN c_inc_party_site_address(l_service_req_rec.incident_location_id);
265 FETCH c_inc_party_site_address INTO l_location_id;
266 IF (c_inc_party_site_address%NOTFOUND) THEN
267 l_location_id := NULL;
268 END IF;
269 CLOSE c_inc_party_site_address;
270 -- Added for bug 5228561
271 ELSE
272 IF (l_service_req_rec.incident_location_type = 'HZ_LOCATION') THEN
273 l_location_id := l_service_req_rec.incident_location_id;
274 END IF;
275 END IF;
276 OPEN c_inc_address(l_location_id);
277 FETCH c_inc_address INTO l_country,l_province,l_state,l_city,l_postal_code,
278 l_county;
279 IF (c_inc_address%NOTFOUND) THEN
280 NULL;
281 END IF;
282 CLOSE c_inc_address;
283 l_service_req_rec.incident_country := l_country;
284 l_service_req_rec.incident_city := l_city;
285 l_service_req_rec.incident_postal_code := l_postal_code;
286 l_service_req_rec.incident_state := l_state;
287 l_service_req_rec.incident_province := l_province;
288 l_service_req_rec.incident_county := l_county;
289 END IF;
290
291 -- Added for 11.5.10+ ER# 3811871
292 IF (l_service_req_rec.customer_product_id IS NOT NULL) THEN
293 OPEN c_inv_comp_id(l_service_req_rec.cp_component_id);
294 FETCH c_inv_comp_id INTO l_ib_inv_comp_id;
295 CLOSE c_inv_comp_id;
296
297 OPEN c_inv_subcomp_id(l_service_req_rec.cp_subcomponent_id);
298 FETCH c_inv_subcomp_id INTO l_ib_inv_subcomp_id;
299 CLOSE c_inv_subcomp_id;
300
301 l_service_req_rec.cp_component_id := l_ib_inv_comp_id;
302 l_service_req_rec.cp_subcomponent_id := l_ib_inv_subcomp_id;
303 END IF;
304
305 OPEN c_cust_det(l_service_req_rec.customer_id);
306 FETCH c_cust_det INTO l_no_of_employees, l_party_name;
307 IF (c_cust_det%NOTFOUND) THEN
308 l_no_of_employees := NULL;
309 l_party_name := NULL;
310 END IF;
311 CLOSE c_cust_det;
312
313 IF (NVL(FND_PROFILE.VALUE('CS_SR_TASK_OWNER_AUTO_ASSIGN_LEVEL'),'INDIVIDUAL') = 'INDIVIDUAL') THEN
314 Assign_Owner
315 ( p_init_msg_list => p_init_msg_list,
316 p_commit => p_commit,
317 p_incident_id => p_incident_id,
318 p_total_emp => l_no_of_employees,
319 p_party_name => l_party_name,
320 p_param_resource_type => 'RS_INDIVIDUAL',
321 p_service_request_rec => l_service_req_rec,
322 p_task_attribute_rec => p_task_attribute_rec,
323 x_return_status => x_return_status,
324 x_resource_id => l_owner_id,
325 x_resource_type => l_resource_type,
326 x_territory_id => l_territory_id,
327 x_msg_count => x_msg_count,
328 x_msg_data => x_msg_data
329 );
330 END IF; -- Profile value is INDIVIDUAL
331 IF (l_owner_id IS NULL OR
332 FND_PROFILE.VALUE('CS_SR_TASK_OWNER_AUTO_ASSIGN_LEVEL') = 'GROUP') THEN
333 -- Call the Assign Group Procedure to return the Group and Group Type
334 Assign_Group
335 ( p_init_msg_list => p_init_msg_list,
336 p_commit => p_commit,
337 p_incident_id => p_incident_id,
338 p_total_emp => l_no_of_employees,
339 p_party_name => l_party_name,
340 p_service_request_rec => l_service_req_rec,
341 p_task_attribute_rec => p_task_attribute_rec,
342 x_return_status => x_return_status,
343 x_group_id => l_group_id,
344 x_group_type => l_group_type,
345 x_territory_id => l_territory_id,
346 x_msg_count => x_msg_count,
347 x_msg_data => x_msg_data
348 );
349 END IF; -- l_owner_id IS NULL
350 END IF; -- p_incident_id IS NULL
351
352 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
353 x_owner_id := NULL;
354 x_owner_type := NULL;
355 x_owner_group_id := NULL;
356 x_group_type := NULL;
357 FND_MSG_PUB.Initialize;
358 FND_MESSAGE.Set_Name('CS', 'CS_SR_TASK_NO_OWNER');
359 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full);
360 FND_MSG_PUB.Add;
361 RAISE FND_API.G_EXC_ERROR;
362 ELSE
363 IF (l_owner_id IS NULL AND l_group_id IS NULL) THEN
364 -- FND_MSG_PUB.Initialize;
365 FND_MESSAGE.Set_Name('CS', 'CS_SR_TASK_NO_OWNER');
366 FND_MESSAGE.Set_Token('API_NAME',l_api_name_full||l_service_req_rec.platform_id);
367 FND_MSG_PUB.Add;
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369 ELSE
370 x_owner_id := l_owner_id;
371 x_owner_type := l_resource_type;
372 x_owner_group_id := l_group_id;
373 x_group_type := l_group_type;
374 x_territory_id := l_territory_id;
375 x_return_status := x_return_status;
376 END IF;
377 END IF;
378
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381 x_return_status := FND_API.G_RET_STS_ERROR;
382 FND_MSG_PUB.Count_And_Get
383 ( p_count => x_msg_count,
384 p_data => x_msg_data
385 );
386 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
388 FND_MSG_PUB.Count_And_Get
389 ( p_count => x_msg_count,
390 p_data => x_msg_data
391 );
392
393 END Assign_Task_Resource;
394
395 /***************************************************
396 -- This Procedure returns the Group if not passed.
397 ***************************************************/
398 PROCEDURE Assign_Group
399 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
400 p_commit IN VARCHAR2 := FND_API.G_FALSE,
401 p_incident_id IN NUMBER,
402 p_total_emp IN NUMBER,
403 p_party_name IN VARCHAR2,
404 p_service_request_rec IN CS_Servicerequest_PUB.service_request_rec_type,
405 p_task_attribute_rec IN SR_Task_rec_type,
406 x_return_status OUT NOCOPY VARCHAR2,
407 x_group_id OUT NOCOPY NUMBER,
408 x_group_type OUT NOCOPY VARCHAR2,
409 x_territory_id OUT NOCOPY NUMBER,
410 x_msg_count OUT NOCOPY NUMBER,
411 x_msg_data OUT NOCOPY VARCHAR2
412 ) IS
413
414 -- Define Local Variables
415 n NUMBER;
416 -- Input and output data structures
417 l_Assign_Groups_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type;
418 l_task_am_rec JTF_ASSIGN_PUB.JTF_Srv_Task_rec_type ;
419 -- Qualifier values
420 l_incident_id NUMBER := p_incident_id;
421 l_inv_item_id NUMBER := NULL;
422 l_inv_org_id NUMBER := NULL;
423 l_party_id NUMBER := p_service_request_rec.customer_id;
424 l_cust_category VARCHAR2(30) := NULL;
425 l_area_code VARCHAR2(60) := NULL;
426 l_contract_service_id NUMBER := p_service_request_rec.contract_service_id;
427 l_cust_prod_id NUMBER := p_service_request_rec.customer_product_id;
428 l_contract_res_flag VARCHAR2(3);
429 l_ib_res_flag VARCHAR2(3);
430 --parameters
431 l_no_of_resources NUMBER := 1;
432 l_business_process_id NUMBER;
433 c_customer_phone_id NUMBER := p_service_request_rec.customer_phone_id;
434
435 -- List of Cursors used
436 CURSOR C_CONTRACT(l_contract_service_id number) IS
437 SELECT to_number(object1_id1), to_number(object1_id2)
438 FROM okc_k_items
439 WHERE cle_id = l_contract_service_id;
440
441 --Bug 5255184 Modified the c_area_code query
442 CURSOR c_area_code IS
443 SELECT hzp.phone_area_code
444 FROM hz_contact_points hzp
445 WHERE hzp.contact_point_id = c_customer_phone_id;
446
447 BEGIN
448 -- Initialize API return status to success
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450
451 -- Proceed even if the group_type is null
452 l_incident_id := p_incident_id;
453
454 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_TASK_AUTO_ASSIGN') = 'YES') THEN
455 SELECT business_process_id INTO l_business_process_id
456 FROM cs_incident_types
457 WHERE incident_type_id = p_service_request_rec.type_id;
458 END IF;
459
460 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
461 -- Assigning component and subcomponent id directly to the am rec
462
463 IF (l_cust_prod_id IS NOT NULL) THEN
464 l_task_am_rec.item_component := p_service_request_rec.cp_component_id ;
465 l_task_am_rec.item_subcomponent := p_service_request_rec.cp_subcomponent_id ;
466 ELSE
467 l_task_am_rec.item_component := p_service_request_rec.inv_component_id ;
468 l_task_am_rec.item_subcomponent := p_service_request_rec.inv_subcomponent_id ;
469 END IF;
470
471 --Bug 5255184 Modified the c_area_code
472 OPEN c_area_code;
473 FETCH c_area_code INTO l_area_code;
474 IF (c_area_code%NOTFOUND) THEN
475 l_area_code := NULL;
476 END IF;
477 CLOSE c_area_code;
478 -- Assign the values to the AM Record Type
479 -- Assign the Task Related Information
480 l_task_am_rec.task_type_id := p_task_attribute_rec.task_type_id;
481 l_task_am_rec.task_status_id := p_task_attribute_rec.task_status_id;
482 l_task_am_rec.task_priority_id := p_task_attribute_rec.task_priority_id;
483 l_task_am_rec.num_of_employees := p_total_emp;
484 -- Assign the Service Request Related Information
485 l_task_am_rec.service_request_id := p_incident_id;
486 l_task_am_rec.party_id := p_service_request_rec.customer_id;
487 l_task_am_rec.incident_type_id := p_service_request_rec.type_id;
488 l_task_am_rec.incident_severity_id := p_service_request_rec.severity_id;
489 l_task_am_rec.incident_urgency_id := p_service_request_rec.urgency_id;
490 l_task_am_rec.incident_status_id := p_service_request_rec.status_id;
491 l_task_am_rec.problem_code := p_service_request_rec.problem_code;
492 l_task_am_rec.platform_id := p_service_request_rec.platform_id;
493 l_task_am_rec.sr_creation_channel := p_service_request_rec.sr_creation_channel;
494 l_task_am_rec.inventory_item_id := p_service_request_rec.inventory_item_id;
495 l_task_am_rec.squal_char12 := p_service_request_rec.problem_code;
496 l_task_am_rec.squal_char13 := p_service_request_rec.comm_pref_code;
497 l_task_am_rec.squal_num12 := p_service_request_rec.platform_id;
498 l_task_am_rec.squal_num13 := p_service_request_rec.inv_platform_org_id;
499 l_task_am_rec.squal_num14 := p_service_request_rec.category_id;
500 l_task_am_rec.squal_num15 := p_service_request_rec.inventory_item_id;
501 l_task_am_rec.squal_num16 := p_service_request_rec.inventory_org_id;
502 -- Passing SR Group Owner for Bug# 3564691
503 l_task_am_rec.squal_num17 := p_service_request_rec.owner_group_id;
504 l_task_am_rec.squal_num30 := p_service_request_rec.language_id;
505 l_task_am_rec.squal_char20 := p_service_request_rec.cust_pref_lang_code;
506 l_task_am_rec.squal_char21 := p_service_request_rec.coverage_type;
507 l_task_am_rec.area_code := l_area_code;
508 l_task_am_rec.party_site_id := p_service_request_rec.customer_site_id;
509 l_task_am_rec.customer_site_id := p_service_request_rec.customer_site_id;
510 l_task_am_rec.support_site_id := p_service_request_rec.site_id;
511 l_task_am_rec.country := p_service_request_rec.incident_country;
512 l_task_am_rec.city := p_service_request_rec.incident_city;
513 l_task_am_rec.postal_code := p_service_request_rec.incident_postal_code;
514 l_task_am_rec.state := p_service_request_rec.incident_state;
515 l_task_am_rec.province := p_service_request_rec.incident_province;
516 l_task_am_rec.county := p_service_request_rec.incident_county;
517 l_task_am_rec.comp_name_range := p_party_name;
518
519 -- Contract Item and Org dtls
520 IF (l_contract_service_id IS NOT NULL) THEN
521 OPEN c_contract(l_contract_service_id);
522 FETCH c_contract INTO l_inv_item_id,l_inv_org_id;
523 IF (c_contract%NOTFOUND) THEN
524 NULL;
525 END IF;
526 CLOSE c_contract;
527 END IF;
528 -- Assign the values to the qualifiers
529 l_task_am_rec.squal_num18 := l_inv_item_id;
530 l_task_am_rec.squal_num19 := l_inv_org_id;
531
532 -- If customer product id is not null, then set ib_preferred_resource_flag
533 -- to 'Y'.If contract line id is not null, then set
534 -- contract_preferred_resource flag to 'Y'.
535 IF (l_contract_service_id IS NOT NULL) THEN
536 l_contract_res_flag := 'Y';
537 ELSE
538 l_contract_res_flag := 'N';
539 END IF;
540 IF (l_cust_prod_id IS NOT NULL) THEN
541 l_ib_res_flag := 'Y';
542 ELSE
543 l_ib_res_flag := 'N';
544 END IF;
545
546 JTF_ASSIGN_PUB.GET_Assign_Resources
547 ( p_api_version => 1.0,
548 p_init_msg_list => 'T',
549 p_commit => 'F',
550 p_resource_id => NULL,
551 p_resource_type => 'RS_GROUP',
552 p_role => NULL,
553 p_no_of_resources => l_no_of_resources,
554 p_auto_select_flag => 'N',
555 p_contracts_preferred_engineer => l_contract_res_flag,
556 p_ib_preferred_engineer => l_ib_res_flag,
557 p_contract_id => l_contract_service_id,
558 p_customer_product_id => l_cust_prod_id,
559 p_effort_duration => NULL,
560 p_effort_uom => NULL,
561 p_start_date => NULL,
562 p_end_date => NULL,
563 p_territory_flag => 'Y',
564 p_calendar_flag => 'N',
565 p_web_availability_flag => 'Y',
566 p_filter_excluded_resource => 'Y',
567 p_category_id => NULL,
568 p_inventory_item_id => NULL,
569 p_inventory_org_id => NULL,
570 p_column_list => NULL,
571 p_calling_doc_id => NULL,
572 p_calling_doc_type => 'SR',
573 p_sr_rec => NULL,
574 p_sr_task_rec => l_task_am_rec,
575 p_defect_rec => NULL,
576 p_business_process_id => l_business_process_id,
577 p_business_process_date => p_service_request_rec.request_date,
578 x_Assign_Resources_tbl => l_Assign_Groups_tbl,
579 x_return_status => x_return_status,
580 x_msg_count => x_msg_count,
581 x_msg_data => x_msg_data
582 );
583
584 n := l_Assign_Groups_tbl.FIRST;
585
586 IF (x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
587 IF (l_Assign_Groups_tbl.exists(n)) THEN
588 x_group_id := l_Assign_Groups_tbl(n).resource_id;
589 x_group_type := l_Assign_Groups_tbl(n).resource_type;
590 x_territory_id := l_Assign_Groups_tbl(n).terr_id ;
591 ELSE
592 x_group_id := NULL;
593 x_group_type := NULL;
594 END IF;
595 END IF;
596
597 END Assign_Group;
598
599 /**************************************************************
600 -- This Procedure returns the individual Owner from the Group
601 -- returned by the Assign_Group Procedure.
602 **************************************************************/
603 PROCEDURE Assign_Owner
604 ( p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
605 p_commit IN VARCHAR2 := FND_API.G_FALSE,
606 p_incident_id IN NUMBER,
607 p_total_emp IN NUMBER,
608 p_party_name IN VARCHAR2,
609 p_param_resource_type IN VARCHAR2,
610 p_service_request_rec IN CS_ServiceRequest_PUB.service_request_rec_type,
611 p_task_attribute_rec IN SR_Task_rec_type,
612 x_return_status OUT NOCOPY VARCHAR2,
613 x_resource_id OUT NOCOPY NUMBER,
614 x_resource_type OUT NOCOPY VARCHAR2,
615 x_territory_id OUT NOCOPY NUMBER,
616 x_msg_count OUT NOCOPY NUMBER,
617 x_msg_data OUT NOCOPY VARCHAR2
618 ) IS
619
620 -- Input and output data structures
621 l_Assign_Owner_tbl JTF_ASSIGN_PUB.AssignResources_tbl_type ;
622 l_task_am_rec JTF_ASSIGN_PUB.JTF_Srv_Task_rec_type ;
623 -- Message Variables
624 l_index BINARY_INTEGER;
625 l_count NUMBER;
626 l_counter NUMBER;
627 p NUMBER;
628 -- Qualifier values
629 l_incident_id NUMBER := p_incident_id;
630 l_contract_service_id NUMBER := p_service_request_rec.contract_service_id;
631 l_cust_prod_id NUMBER := p_service_request_rec.customer_product_id;
632 l_contract_res_flag VARCHAR2(3);
633 l_ib_res_flag VARCHAR2(3);
634 l_inv_item_id NUMBER := NULL;
635 l_inv_org_id NUMBER := NULL;
636 l_inv_category_id NUMBER := NULL ;
637 l_party_id NUMBER := p_service_request_rec.customer_id;
638 l_class_code VARCHAR2(30) := NULL;
639 -- Passing parameters
640 l_no_of_resources NUMBER := NULL;
641 l_area_code VARCHAR2(50) ;
642 l_business_process_id NUMBER;
643
644 -- List of Cursors
645 CURSOR c_contract(l_contract_service_id NUMBER)IS
646 SELECT TO_NUMBER(object1_id1), TO_NUMBER(object1_id2)
647 FROM okc_k_items
648 WHERE cle_id = l_contract_service_id;
649
650 CURSOR c_area_code(c_incident_id NUMBER) IS
651 SELECT hzp.phone_area_code
652 FROM hz_contact_points hzp,
653 cs_incidents_all_b csi
654 WHERE csi.incident_id = c_incident_id
655 AND csi.customer_phone_id = hzp.contact_point_id
656 AND csi.customer_phone_id IS NOT NULL;
657
658 BEGIN
659
660 -- Initialize API return status to success
661 x_return_status := FND_API.G_RET_STS_SUCCESS;
662
663 -- Proceed even if the group_id is null
664 -- l_group_id := p_group_id ;
665
666 --
667 IF (FND_PROFILE.VALUE('CS_SR_USE_BUS_PROC_TASK_AUTO_ASSIGN') = 'YES') THEN
668 SELECT business_process_id INTO l_business_process_id
669 FROM cs_incident_types
670 WHERE incident_type_id = p_service_request_rec.type_id;
671 END IF;
672
673 -- 4365612 Removed the profile check "Service : Use Component Subcomponent in Assignment (Reserved)"
674 -- Assigning component and subcomponent id directly to the am rec
675
676 IF (l_cust_prod_id IS NOT NULL) THEN
677 l_task_am_rec.item_component := p_service_request_rec.cp_component_id ;
678 l_task_am_rec.item_subcomponent := p_service_request_rec.cp_subcomponent_id ;
679 ELSE
680 l_task_am_rec.item_component := p_service_request_rec.inv_component_id ;
681 l_task_am_rec.item_subcomponent := p_service_request_rec.inv_subcomponent_id ;
682 END IF;
683
684 OPEN c_area_code(l_incident_id);
685 FETCH c_area_code INTO l_area_code;
686 IF (c_area_code%NOTFOUND) THEN
687 l_area_code := NULL;
688 END IF;
689 CLOSE c_area_code;
690
691 -- Set the Task Related Information
692 l_task_am_rec.task_type_id := p_task_attribute_rec.task_type_id;
693 l_task_am_rec.task_status_id := p_task_attribute_rec.task_status_id;
694 l_task_am_rec.task_priority_id := p_task_attribute_rec.task_priority_id;
695 l_task_am_rec.num_of_employees := p_total_emp;
696 -- Set the Service Request Related Information
697 l_task_am_rec.service_request_id := p_incident_id;
698 l_task_am_rec.party_id := p_service_request_rec.customer_id;
699 l_task_am_rec.incident_type_id := p_service_request_rec.type_id;
700 l_task_am_rec.incident_severity_id := p_service_request_rec.severity_id;
701 l_task_am_rec.incident_urgency_id := p_service_request_rec.urgency_id;
702 l_task_am_rec.incident_status_id := p_service_request_rec.status_id;
703 l_task_am_rec.problem_code := p_service_request_rec.problem_code;
704 l_task_am_rec.platform_id := p_service_request_rec.platform_id;
705 l_task_am_rec.sr_creation_channel := p_service_request_rec.sr_creation_channel;
706 l_task_am_rec.inventory_item_id := p_service_request_rec.inventory_item_id;
707 l_task_am_rec.squal_char12 := p_service_request_rec.problem_code;
708 l_task_am_rec.squal_char13 := p_service_request_rec.comm_pref_code;
709 l_task_am_rec.squal_char20 := p_service_request_rec.cust_pref_lang_code ;
710 l_task_am_rec.squal_char21 := p_service_request_rec.coverage_type;
711 l_task_am_rec.squal_num12 := p_service_request_rec.platform_id;
712 l_task_am_rec.squal_num13 := p_service_request_rec.inv_platform_org_id;
713 l_task_am_rec.squal_num14 := p_service_request_rec.category_id;
714 l_task_am_rec.squal_num15 := p_service_request_rec.inventory_item_id;
715 l_task_am_rec.squal_num16 := p_service_request_rec.inventory_org_id;
716 -- Passing SR Group Owner for Bug# 3564691
717 l_task_am_rec.squal_num17 := p_service_request_rec.owner_group_id;
718 l_task_am_rec.squal_num30 := p_service_request_rec.language_id;
719 l_task_am_rec.area_code := l_area_code;
720 l_task_am_rec.party_site_id := p_service_request_rec.customer_site_id;
721 l_task_am_rec.customer_site_id := p_service_request_rec.customer_site_id;
722 l_task_am_rec.support_site_id := p_service_request_rec.site_id;
723 l_task_am_rec.country := p_service_request_rec.incident_country;
724 l_task_am_rec.city := p_service_request_rec.incident_city;
725 l_task_am_rec.postal_code := p_service_request_rec.incident_postal_code;
726 l_task_am_rec.state := p_service_request_rec.incident_state;
727 l_task_am_rec.province := p_service_request_rec.incident_province;
728 l_task_am_rec.county := p_service_request_rec.incident_county;
729 l_task_am_rec.comp_name_range := p_party_name;
730
731 --Contract Item and Org dtls
732 IF (l_contract_service_id IS NOT NULL) THEN
733 OPEN c_contract(l_contract_service_id);
734 FETCH c_contract INTO l_inv_item_id,l_inv_org_id;
735 IF c_contract%NOTFOUND THEN
736 NULL;
737 END IF;
738 CLOSE c_contract;
739 END IF;
740 -- Assign it to the AM record type For contracts
741 l_task_am_rec.squal_num18 := l_inv_item_id;
742 l_task_am_rec.squal_num19 := l_inv_org_id;
743 l_task_am_rec.squal_char11 := null;
744
745 -- If customer product id is not null, then set ib_preferred_resource_flag
746 -- to 'Y'.If contract line id is not null, then set
747 -- contract_preferred_resource flag to 'Y'.
748 IF (l_contract_service_id IS NOT NULL) THEN
749 l_contract_res_flag := 'Y';
750 ELSE
751 l_contract_res_flag := 'N';
752 END IF;
753 IF (l_cust_prod_id IS NOT NULL) THEN
754 l_ib_res_flag := 'Y';
755 ELSE
756 l_ib_res_flag := 'N';
757 END IF;
758
759 JTF_ASSIGN_PUB.GET_Assign_Resources
760 ( p_api_version => 1.0,
761 p_init_msg_list => null,
762 p_commit => 'F',
763 --p_resource_id => l_group_id,
764 p_resource_type => 'RS_INDIVIDUAL',
765 p_role => NULL,
766 p_no_of_resources => l_no_of_resources,
767 p_auto_select_flag => 'N',
768 p_ib_preferred_engineer => l_ib_res_flag,
769 p_contracts_preferred_engineer => l_contract_res_flag,
770 p_contract_id => l_contract_service_id,
771 p_customer_product_id => l_cust_prod_id,
772 p_effort_duration => NULL,
773 p_effort_uom => NULL,
774 p_start_date => NULL,
775 p_end_date => NULL,
776 p_territory_flag => 'Y',
777 p_calendar_flag => 'N',
778 --p_web_availability_flag => 'Y',
779 p_filter_excluded_resource => 'Y',
780 p_category_id => NULL,
781 p_inventory_item_id => NULL,
782 p_inventory_org_id => NULL,
783 p_column_list => NULL,
784 p_calling_doc_id => NULL,
785 p_calling_doc_type => 'SR',
786 p_sr_rec => NULL,
787 p_sr_task_rec => l_task_am_rec,
788 p_defect_rec => NULL,
789 p_business_process_id => l_business_process_id,
790 p_business_process_date => p_service_request_rec.request_date,
791 x_Assign_Resources_tbl => l_Assign_Owner_tbl,
792 x_return_status => x_return_status,
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data
795 );
796
797 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
798 p := l_Assign_Owner_tbl.FIRST ;
799 IF (l_Assign_Owner_tbl.COUNT >= 1) THEN
800 x_resource_id := l_Assign_Owner_tbl(p).resource_id ;
801 x_resource_type := l_Assign_Owner_tbl(p).resource_type ;
802 x_territory_id := l_Assign_Owner_tbl(p).terr_id ;
803 END IF;
804 END IF ; -- Return status S
805 END Assign_Owner;
806
807 END CS_SR_TASK_AUTOASSIGN_PKG;