1 PACKAGE BODY CS_AutoGen_Task_PVT AS
2 /* $Header: csvatskb.pls 120.12.12010000.7 2010/01/06 07:07:54 sshilpam ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_AutoGen_Task_PVT';
5
6 -- 12.1.2 SR Task Enhancement project
7 -- Get the Planned End date based on the Profile
8 PROCEDURE Default_Planned_End_Date(p_respond_by IN DATE,
9 p_resolve_by IN DATE,
10 p_uom_code IN varchar2 DEFAULT FND_API.G_MISS_CHAR,
11 p_planned_effort IN Number DEFAULT FND_API.G_MISS_NUM,
12 x_planned_end_date OUT NOCOPY DATE) IS
13
14 l_profile Varchar2(50);
15 l_profile_respond_by Number;
16 l_planned_effort Number;
17 l_planned_end_date Date;
18 BEGIN
19
20 l_profile := fnd_profile.value('CS_SR_TSK_DEF_PLN_END_DT');
21 l_profile_respond_by := To_number(fnd_profile.value('CS_SR_TASK_RESPOND_BY'));
22
23 if nvl(l_profile,'RESP_BY') = 'RESP_BY' Then
24
25 If p_respond_by is not null and p_respond_by >= Sysdate Then
26 l_planned_end_date:= p_respond_by;
27
28 ElsIf l_profile_respond_by is not null Then
29 select sysdate + l_profile_respond_by into l_planned_end_date from dual;
30
31 Elsif p_planned_effort is not null and p_uom_code is not null then
32 l_planned_end_date := okc_time_util_pub.get_enddate(Sysdate,p_uom_code,p_planned_effort);
33 End If;
34
35 Elsif l_profile = 'RESL_EFFORT' Then
36
37 If p_respond_by is not null and p_respond_by >= Sysdate then
38 l_planned_end_date := p_respond_by;
39
40 Elsif p_resolve_by is not null and p_resolve_by >= Sysdate then
41 IF p_planned_effort is not null and p_uom_code is not null then
42 l_planned_effort := p_planned_effort * (-1);
43 l_planned_end_date := okc_time_util_pub.get_enddate(p_resolve_by,p_uom_code,l_planned_effort);
44
45 End IF;
46 Elsif l_profile_respond_by is not null Then
47 select sysdate + l_profile_respond_by into l_planned_end_date from dual;
48
49 Elsif p_planned_effort is not null and p_uom_code is not null then
50 l_planned_end_date := okc_time_util_pub.get_enddate(Sysdate,p_uom_code,p_planned_effort);
51
52 End if;
53 Elsif l_profile = 'RESL_BY' then
54 If p_resolve_by is not null and p_resolve_by >= Sysdate then
55 l_planned_end_date := p_resolve_by;
56
57 elsif l_profile_respond_by is not null Then
58 select sysdate + l_profile_respond_by into l_planned_end_date from dual;
59
60 Elsif p_planned_effort is not null and p_uom_code is not null then
61 l_planned_end_date := okc_time_util_pub.get_enddate(Sysdate,p_uom_code,p_planned_effort);
62 End if;
63 End if;
64
65 If l_planned_end_date < sysdate or l_planned_end_date is null then
66 x_planned_end_date := SYSDATE;
67 Else
68 x_planned_end_date := l_planned_end_date;
69 End IF;
70
71 End Default_Planned_End_Date;
72 -- End of 12.1.2 Project code
73
74 PROCEDURE Create_Task_From_Template
75 (
76 P_task_template_group_owner IN NUMBER,
77 P_task_tmpl_group_owner_type IN VARCHAR2,
78 P_incident_id IN NUMBER,
79 P_service_request_rec IN Cs_ServiceRequest_PVT.Service_Request_rec_type,
80 P_task_template_group_info IN JTF_TASK_INST_TEMPLATES_PUB.task_template_group_info,
81 P_task_template_tbl IN JTF_TASK_INST_TEMPLATES_PUB.task_template_info_tbl,
82 X_field_service_task_created OUT NOCOPY BOOLEAN,
83 X_return_status OUT NOCOPY VARCHAR2,
84 X_msg_data OUT NOCOPY VARCHAR2,
85 X_msg_count OUT NOCOPY NUMBER
86 ) IS
87
88 l_level_statement VARCHAR2(240) := 'cs.plsql.cs_autogen_task_pvt.create_task_from_template';
89 l_return_status VARCHAR2(30) := 'S';
90 l_commit VARCHAR2(30) := fnd_api.g_false;
91 l_init_msg_list VARCHAR2(30) := fnd_api.g_false;
92 l_owner_group_id NUMBER ;
93 l_owner_id NUMBER ;
94 l_owner_type VARCHAR2(30) ;
95 l_msg_count NUMBER ;
96 l_msg_data VARCHAR2(240) ;
97 l_task_rule VARCHAR2(30) := null ;
98 l_template_group_name VARCHAR2(80);
99 l_field_service_task_created BOOLEAN := FALSE;
100 l_task_name VARCHAR2(240) := null;
101 l_owner_group_type VARCHAr2(240);
102
103 -- Variabkes to be passed to the JTF Create Task From emplate API
104
105 l_task_template_group_info JTF_TASK_INST_TEMPLATES_PUB.task_template_group_info := P_task_template_group_info ;
106 l_task_template_tbl JTF_TASK_INST_TEMPLATES_PUB.task_template_info_tbl := P_task_template_tbl;
107 l_task_contact_points_tbl JTF_TASK_INST_TEMPLATES_PUB.task_contact_points_tbl ;
108 l_task_details_tbl JTF_TASK_INST_TEMPLATES_PUB.task_details_tbl ;
109
110
111 -- Variabkes to be passed to the Task Auto Assignment API
112
113 l_task_attribute_rec CS_SR_TASK_AUTOASSIGN_PKG.Sr_Task_rec_type := null;
114 l_service_request_pub_rec CS_SERVICEREQUEST_PUB.Service_Request_Rec_Type ;
115
116 -- Exception declaraion
117
118 e_AutoAssignment_Exception EXCEPTION ;
119 e_party_site_exception EXCEPTION ;
120 e_Planned_effort_Exception EXCEPTION ;
121 e_CreateTask_Exception EXCEPTION ;
122
123 -- Cursor declareation
124
125 -- Cursor to check task type
126
127 CURSOR c_check_task_type (p_task_type_id IN NUMBER) IS
128 SELECT RULE
129 FROM jtf_task_types_vl
130 WHERE task_type_id = p_task_type_id ;
131
132 -- cursor to determine task template group name
133
134 CURSOR c_get_tgt_name IS
135 SELECT template_group_name
136 FROM jtf_task_temp_groups_vl
137 WHERE task_template_group_id = l_task_template_group_info.task_template_group_id;
138
139
140 -- Simplex
141 -- local variable and exception declarations for Simplex Enhancement
142 l_prof_val VARCHAR(1);
143 l_temp NUMBER(30,6);
144 l_api_name VARCHAR2(100) := 'CS_AutoGen_Task_PVT.Create_Task_From_Template';
145 l_conv_rate NUMBER(30,6);
146 l_conv_rate_day NUMBER;
147 l_planned_effort NUMBER(30,6):= 0;
148 l_text VARCHAR2(240);
149
150 e_date_pair_exception EXCEPTION ;
151 e_planned_effort_val_exception EXCEPTION ;
152 -- end of Simplex
153
154 l_owner_territory_id NUMBER;
155 l_profile_respond_by NUMBER; -- Bug 7281019
156
157 BEGIN
158
159 -- Simplex
160 -- Get the value for the profile option 'Service : Apply State Restriction on Tasks'
161 -- to decide the enabling/disabling of task state restrictions
162
163 FND_PROFILE.Get('CS_SR_ENABLE_TASK_STATE_RESTRICTIONS',l_prof_val);
164
165 -- end Simplex
166
167 -- Loop through the Task Template table
168
169 FOR i IN 1..l_task_template_tbl.COUNT
170
171 LOOP
172
173 -- Get task owner
174
175 IF (( l_task_template_tbl(i).owner_id IS NULL) OR (l_task_template_tbl(i).owner_type_code IS NULL) ) THEN
176
177 IF ((p_task_template_group_owner IS NOT NULL) AND (p_task_tmpl_group_owner_type IS NOT NULL) ) THEN -- Get owner if
178
179
180 -- Assign the passed owner to the task.
181
182 l_task_template_tbl(i).owner_type_code := p_task_tmpl_group_owner_type ;
183 l_task_template_tbl(i).owner_id := p_task_template_group_owner;
184 ELSE
185
186 -- Get owner else
187 -- Call Auto Owner Assignment API to get task owner
188
189
190 -- Initialize task rec
191
192
193 l_task_attribute_rec := NULL;
194
195 l_task_attribute_rec.TASK_ID := null ;
196 l_task_attribute_rec.SERVICE_REQUEST_ID := p_incident_id ;
197 l_task_attribute_rec.PARTY_ID := p_service_request_rec.customer_id ;
198 l_task_attribute_rec.COUNTRY := p_service_request_rec.incident_country;
199 l_task_attribute_rec.CITY := p_service_request_rec.incident_city ;
200 l_task_attribute_rec.POSTAL_CODE := p_service_request_rec.incident_postal_code ;
201 l_task_attribute_rec.STATE := p_service_request_rec.incident_state ;
202 l_task_attribute_rec.AREA_CODE := p_service_request_rec.incident_postal_code ;
203 l_task_attribute_rec.COUNTY := p_service_request_rec.incident_county ;
204 l_task_attribute_rec.PROVINCE := p_service_request_rec.incident_province ;
205 l_task_attribute_rec.TASK_TYPE_ID := l_task_template_tbl(i).task_type_id ;
206 l_task_attribute_rec.TASK_STATUS_ID := l_task_template_tbl(i).task_status_id ;
207 l_task_attribute_rec.TASK_PRIORITY_ID := l_task_template_tbl(i).task_priority_id ;
208 l_task_attribute_rec.INCIDENT_TYPE_ID := p_service_request_rec.type_id;
209 l_task_attribute_rec.INCIDENT_SEVERITY_ID := p_service_request_rec.severity_id;
210 l_task_attribute_rec.INCIDENT_URGENCY_ID := p_service_request_rec.urgency_id;
211 l_task_attribute_rec.PROBLEM_CODE := p_service_request_rec.problem_code;
212 l_task_attribute_rec.INCIDENT_STATUS_ID := p_service_request_rec.status_id;
213 l_task_attribute_rec.PLATFORM_ID := p_service_request_rec.platform_id;
214 l_task_attribute_rec.CUSTOMER_SITE_ID := p_service_request_rec.customer_site_id;
215 l_task_attribute_rec.SR_CREATION_CHANNEL := p_service_request_rec.sr_creation_channel;
216 l_task_attribute_rec.INVENTORY_ITEM_ID := p_service_request_rec.inventory_item_id;
217 l_task_attribute_rec.ATTRIBUTE1 := l_task_template_tbl(i).attribute1 ;
218 l_task_attribute_rec.ATTRIBUTE2 := l_task_template_tbl(i).attribute2 ;
219 l_task_attribute_rec.ATTRIBUTE3 := l_task_template_tbl(i).attribute3 ;
220 l_task_attribute_rec.ATTRIBUTE4 := l_task_template_tbl(i).attribute4 ;
221 l_task_attribute_rec.ATTRIBUTE5 := l_task_template_tbl(i).attribute5 ;
222 l_task_attribute_rec.ATTRIBUTE6 := l_task_template_tbl(i).attribute6 ;
223 l_task_attribute_rec.ATTRIBUTE7 := l_task_template_tbl(i).attribute7 ;
224 l_task_attribute_rec.ATTRIBUTE8 := l_task_template_tbl(i).attribute8 ;
225 l_task_attribute_rec.ATTRIBUTE9 := l_task_template_tbl(i).attribute9 ;
226 l_task_attribute_rec.ATTRIBUTE10 := l_task_template_tbl(i).attribute10 ;
227 l_task_attribute_rec.ATTRIBUTE11 := l_task_template_tbl(i).attribute11 ;
228 l_task_attribute_rec.ATTRIBUTE12 := l_task_template_tbl(i).attribute12 ;
229 l_task_attribute_rec.ATTRIBUTE13 := l_task_template_tbl(i).attribute13 ;
230 l_task_attribute_rec.ATTRIBUTE14 := l_task_template_tbl(i).attribute14 ;
231 l_task_attribute_rec.ATTRIBUTE15 := l_task_template_tbl(i).attribute15 ;
232 l_task_attribute_rec.ORGANIZATION_ID := p_service_request_rec.inventory_org_id ;
233
234 -- Following task parameters are not available
235
236 --l_task_attribute_rec.COMP_NAME_RANGE VARCHAR2 ;
237 --l_task_attribute_rec.NUM_OF_EMPLOYEES NUMBER,
238 --l_task_attribute_rec.SUPPORT_SITE_ID NUMBER;
239
240 -- l_task_attribute_rec.SQUAL_NUM12 NUMBER, --INVENTORY ITEM ID / SR PLATFORM
241 -- l_task_attribute_rec.SQUAL_NUM13 NUMBER, --ORGANIZATION ID / SR PLATFORM
242 -- l_task_attribute_rec.SQUAL_NUM14 NUMBER, --CATEGORY ID / SR PRODUCT
243 -- l_task_attribute_rec.SQUAL_NUM15 NUMBER, --INVENTORY ITEM ID / SR PRODUCT
244 -- l_task_attribute_rec.SQUAL_NUM16 NUMBER, --ORGANIZATION ID / SR PRODUCT
245 -- l_task_attribute_rec.SQUAL_NUM17 NUMBER, --SR GROUP OWNER
246 -- l_task_attribute_rec.SQUAL_NUM18 NUMBER, --INVENTORY ITEM ID / CONTRACT SUPPORT SERVICE ITEM
247 -- l_task_attribute_rec.SQUAL_NUM19 NUMBER, --ORGANIZATION ID / CONTRACT SUPPORT SERVICE ITEM
248 -- l_task_attribute_rec.SQUAL_NUM30 NUMBER, --SR LANGUAGE ... should use squal_char20 instead
249 -- l_task_attribute_rec.SQUAL_CHAR11 VARCHAR2(360), --VIP CUSTOMERS
250 -- l_task_attribute_rec.SQUAL_CHAR12 VARCHAR2(360), --SR PROBLEM CODE
251 -- l_task_attribute_rec.SQUAL_CHAR13 VARCHAR2(360), --SR CUSTOMER CONTACT PREFERENCE
252 -- l_task_attribute_rec.SQUAL_CHAR20 VARCHAR2(360), --SR LANGUAGE ID for TERR REQ
253 -- l_task_attribute_rec.SQUAL_CHAR21 VARCHAR2(360) --SR Service Contract Coverage
254
255
256 -- Initialize OUT parameters
257
258 l_owner_group_id := null ;
259 l_owner_id := null ;
260 l_owner_type := null ;
261 l_return_status := null ;
262
263
264 CS_SR_TASK_AUTOASSIGN_PKG.Assign_Task_Resource
265 (p_api_version => 1.0 ,
266 p_init_msg_list => fnd_api.g_false ,
267 p_commit => l_commit ,
268 p_incident_id => p_incident_id ,
269 p_task_attribute_rec => l_task_attribute_rec ,
270 x_owner_group_id => l_owner_group_id ,
271 x_group_type => l_owner_group_type,
272 x_owner_type => l_owner_type ,
273 x_owner_id => l_owner_id ,
274 x_return_status => l_return_status ,
275 x_territory_id => l_owner_territory_id ,
276 x_msg_count => x_msg_count ,
277 x_msg_data => x_msg_data );
278
279
280 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
281 -- 12.1.3 Task enh proj
282 IF l_owner_id is null or l_owner_type is null Then
283 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_TASK_OWNER', l_owner_id);
284 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_TASK_OWNER_TYPE', l_owner_type);
285
286 l_task_template_tbl(i).owner_type_code := l_owner_type ;
287 l_task_template_tbl(i).owner_id := l_owner_id ;
288 End If;
289 If l_owner_id is null or l_owner_type is null Then
290 -- End Task Enh proj
291 l_task_name := null;
292 l_task_name := l_task_template_tbl(i).task_name;
293 RAISE e_AutoAssignment_Exception;
294 End If;
295 ELSE
296
297 -- Assign the derived owner to the task.
298 IF l_owner_id IS NOT NULL THEN
299 l_task_template_tbl(i).owner_type_code := l_owner_type ;
300 l_task_template_tbl(i).owner_id := l_owner_id ;
301 l_task_template_group_info.owner_id := l_owner_id;
302 l_task_template_group_info.owner_type_code := l_owner_type;
303 l_task_template_group_info.owner_territory_id := l_owner_territory_id;
304 ELSIF l_owner_group_id IS NOT NULL THEN
305 l_task_template_tbl(i).owner_type_code := l_owner_group_type ;
306 l_task_template_tbl(i).owner_id := l_owner_group_id ;
307 l_task_template_group_info.owner_id := l_owner_id;
308 l_task_template_group_info.owner_type_code := l_owner_type;
309 l_task_template_group_info.owner_territory_id := l_owner_territory_id;
310
311 ELSE
312 RAISE e_AutoAssignment_Exception;
313 END IF ;
314
315 END IF ;
316 END IF ; -- Get owner end if
317 END IF ;
318
319 -- Ensure that the incident location is passed for the field service tasks
320
321 l_task_rule := null;
322
323 OPEN c_check_task_type (l_task_template_tbl(i).task_type_id) ;
324 FETCH c_check_task_type INTO l_task_rule ;
325 CLOSE c_check_task_type ;
326
327 IF NVL(l_task_rule,'XXXXX') = 'DISPATCH' THEN
328
329 IF p_service_request_rec.incident_location_id IS NULL THEN
330 RAISE e_party_site_exception ;
331 END IF ;
332
333 END IF ;
334
335 -- Determine Planned Effort
336
337 IF ((l_task_template_tbl(i).planned_effort IS NULL) AND (NVL(l_task_rule,'XXXX') = 'DISPATCH')) THEN
338 l_task_name := null;
339 l_task_name := l_task_template_tbl(i).task_name;
340
341
342 RAISE e_Planned_effort_Exception;
343 END IF ;
344
345
346 -- Determine Planned END Date and Start Date
347 -- Commented the code for 12.1.2 Project SR Task Enhancements
348 /* l_task_template_tbl(i).planned_start_date := sysdate ;
349
350 IF p_service_request_rec.obligation_date IS NULL THEN
351 -- Bug 7281019, Calculate the planned end date from the profile when respond by date is null
352 l_profile_respond_by := to_number(fnd_profile.value('CS_SR_TASK_RESPOND_BY'));
353 l_task_template_tbl(i).planned_end_date := sysdate + nvl(l_profile_respond_by,0);
354 --l_task_template_tbl(i).planned_end_date := sysdate ; -- NULL;
355 ELSIF (p_service_request_rec.obligation_date > sysdate) THEN
356 l_task_template_tbl(i).planned_end_date := sysdate +(p_service_request_rec.obligation_date -sysdate);
357 ELSE
358 l_task_template_tbl(i).planned_end_date := sysdate ;
359 END IF ;
360 */
361 -- Simplex
362 -- The below validations should be done every tasks in the task template group and
363 -- hence the validations are inside the loop
364
365 -- Enable task state restrictions depending on the profile value
366 -- 'Service : Apply State Restriction on Tasks'
367 IF ( l_prof_val = 'Y') THEN
368 l_task_template_tbl(i).p_date_selected := 'D';
369 END IF;
370
371 -- If the confirmation status is set as 'Confirmed'('C') in the task template group,
372 -- the Confirmation status should be set to 'Not Requried'('N'),
373 IF ( l_task_template_tbl(i).task_confirmation_status = 'C') THEN
374 l_task_template_tbl(i).task_confirmation_status := 'N';
375 END IF ;
376
377 -- The palnned start date and planned end date should appear in pair.
378 -- If not,exception is thrown
379 IF ( ( (l_task_template_tbl(i).planned_start_date IS NOT NULL AND
380 l_task_template_tbl(i).planned_start_date <> FND_API.G_MISS_DATE)
381 AND
382 (l_task_template_tbl(i).planned_end_date IS NULL OR
383 l_task_template_tbl(i).planned_end_date = FND_API.G_MISS_DATE)
384 )
385 OR
386 ( (l_task_template_tbl(i).planned_end_date IS NOT NULL AND
387 l_task_template_tbl(i).planned_end_date <> FND_API.G_MISS_DATE)
388 AND
389 (l_task_template_tbl(i).planned_start_date IS NULL OR
390 l_task_template_tbl(i).planned_start_date = FND_API.G_MISS_DATE)
391 )
392 )THEN
393
394 l_task_name := null;
395 l_task_name := l_task_template_tbl(i).task_name;
396
397 Raise e_date_pair_exception;
398 END IF;
399
400 -- end Simplex
401 END LOOP ; -- End loop for the task template table.
402
403
404 -- Call JTF API to create task from template.
405
406 -- Initialize OUT parameters
407
408 l_owner_group_id := null ;
409 l_owner_id := null ;
410 l_owner_type := null ;
411 l_return_status := null ;
412
413 JTF_TASK_INST_TEMPLATES_PUB.create_task_from_template
414 (p_api_version => 1.0 ,
415 p_init_msg_list => fnd_api.g_false ,
416 p_commit => l_commit ,
417 p_task_template_group_info => l_task_template_group_info,
418 p_task_templates_tbl => l_task_template_tbl,
419 p_task_contact_points_tbl => l_task_contact_points_tbl,
420 x_return_status => l_return_status ,
421 x_msg_count => x_msg_count ,
422 x_msg_data => x_msg_data ,
423 x_task_details_tbl => l_task_details_tbl);
424
425
426 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
427
428 RAISE e_CreateTask_Exception;
429 ELSE
430 x_return_status := FND_API.G_RET_STS_SUCCESS ;
431 x_field_service_task_created := l_field_service_task_created ;
432
433 END IF ;
434
435 EXCEPTION
436 WHEN e_AutoAssignment_Exception THEN
437 OPEN c_get_tgt_name;
438 FETCH c_get_tgt_name INTO l_template_group_name ;
439 CLOSE c_get_tgt_name;
440 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
441 p_data => x_msg_data );
442 FND_MESSAGE.SET_NAME('CS','CS_SR_TSK_AUTO_ASSIGNMENT_FAIL');
443 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE_GROUP',l_template_group_name);
444 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE',l_task_name);
445 FND_MSG_PUB.ADD;
446 x_return_status := FND_API.G_RET_STS_ERROR ;
447 WHEN e_party_site_exception THEN
448 OPEN c_get_tgt_name;
449 FETCH c_get_tgt_name INTO l_template_group_name ;
450 CLOSE c_get_tgt_name;
451 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
452 p_data => x_msg_data );
453 FND_MESSAGE.SET_NAME('CS','CS_SR_TSK_INVALID_PARTY_SITE');
454 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE_GROUP',l_template_group_name);
455 FND_MSG_PUB.ADD;
456 x_return_status := FND_API.G_RET_STS_ERROR ;
457 WHEN e_Planned_effort_Exception THEN
458 OPEN c_get_tgt_name;
459 FETCH c_get_tgt_name INTO l_template_group_name ;
460 CLOSE c_get_tgt_name;
461 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
462 p_data => x_msg_data );
463 FND_MESSAGE.SET_NAME('CS','CS_SR_TSK_INVALID_PLANNED_EFRT');
464 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE_GROUP',l_template_group_name);
465 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE',l_task_name);
466 FND_MSG_PUB.ADD;
467 x_return_status := FND_API.G_RET_STS_ERROR ;
468 WHEN e_CreateTask_Exception THEN
469 OPEN c_get_tgt_name;
470 FETCH c_get_tgt_name INTO l_template_group_name ;
471 CLOSE c_get_tgt_name;
472 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
473 p_data => x_msg_data );
474 FND_MESSAGE.SET_NAME('CS','CS_SR_TSK_CREATE_TASK_FAILED');
475 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE_GROUP',l_template_group_name);
476 FND_MSG_PUB.ADD;
477 l_text := sqlcode||'-'||sqlerrm ;
478 x_return_status := FND_API.G_RET_STS_ERROR ;
479 -- Simplex
480 -- The following are the exceptions thrown as a part of the validations done
481 -- for Task State Restrictions
482 WHEN e_date_pair_exception THEN
483 OPEN c_get_tgt_name;
484 FETCH c_get_tgt_name INTO l_template_group_name ;
485 CLOSE c_get_tgt_name;
486 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
487 p_data => x_msg_data );
488 FND_MESSAGE.SET_NAME('CS','CS_DATE_PAIR_ERROR');
489 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE_GROUP',l_template_group_name);
490 FND_MESSAGE.SET_TOKEN('API_NAME',l_api_name);
491 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE',l_task_name);
492 FND_MSG_PUB.ADD;
493 x_return_status := FND_API.G_RET_STS_ERROR ;
494 WHEN e_planned_effort_val_exception THEN
495 OPEN c_get_tgt_name;
496 FETCH c_get_tgt_name INTO l_template_group_name ;
497 CLOSE c_get_tgt_name;
498 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
499 p_data => x_msg_data );
500 FND_MESSAGE.SET_NAME('CS','CS_PLANNED_EFFORT_VAL_ERROR');
501 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE_GROUP',l_template_group_name);
502 FND_MESSAGE.SET_TOKEN('API_NAME',l_api_name);
503 FND_MESSAGE.SET_TOKEN('TASK_TEMPLATE',l_task_name);
504 FND_MSG_PUB.ADD;
505 x_return_status := FND_API.G_RET_STS_ERROR ;
506 -- end of simplex
507 WHEN others THEN
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
509 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
510 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_level_statement ||' - '||SQLERRM);
511 FND_MSG_PUB.ADD;
512 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
513 p_data => x_msg_data);
514 x_return_status := FND_API.G_RET_STS_ERROR ;
515
516 END Create_Task_From_Template;
517
518
519
520 PROCEDURE Get_Task_Template_Group
521 (
522 p_api_version IN NUMBER,
523 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
524 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
525 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
526 p_task_template_search_rec IN CS_AutoGen_Task_PVT.task_template_search_rec_type,
527 x_task_template_group_tbl OUT NOCOPY CS_AutoGen_Task_PVT.task_template_group_tbl_type,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_msg_count OUT NOCOPY NUMBER,
530 x_msg_data OUT NOCOPY VARCHAR2
531 )
532 IS
533
534 l_api_name CONSTANT VARCHAR2(30) := 'Get_Task_Template_Group';
535 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
536
537 l_api_version CONSTANT NUMBER := 1.0;
538
539 l_all_attrs_passed BOOLEAN;
540
541 Cursor c_search_rules
542 Is
543 Select *
544 From cs_sr_tsk_tmpl_seq_rules
545 Where active_flag = 'Y'
546 Order by search_sequence;
547
548 -- Cursor modified to check equality between database column and parameter value only if parameter value has been passed
549 --anmukher -- 08/27/03
550 /*
551 Cursor c_task_template_grp
552 (p_incident_type_id NUMBER,
553 p_organization_id NUMBER,
554 p_inventory_item_id NUMBER,
555 p_category_id NUMBER,
556 p_problem_code VARCHAR2,
557 p_null_num NUMBER,
558 p_null_char VARCHAR2,
559 p_num NUMBER,
560 p_char VARCHAR2)
561 Is
562 Select task_template_group_id
563 From cs_sr_tsk_tmpl_gp_map
564 Where decode(p_incident_type_id, p_null_num, p_num, incident_type_id) = nvl(p_incident_type_id, p_num)
565 And decode(p_organization_id, p_null_num, p_num, organization_id) = nvl(p_organization_id, p_num)
566 And decode(p_inventory_item_id, p_null_num, p_num, inventory_item_id) = nvl(p_inventory_item_id, p_num)
567 And decode(p_category_id, p_null_num, p_num, category_id) = nvl(p_category_id, p_num)
568 And decode(p_problem_code, p_null_char, p_char, problem_code) = nvl(p_problem_code, p_char);
569 */
570
571 -- Cursor re-written --anmukher --10/13/03
572 Cursor c_task_template_grp
573 (p_incident_type_id NUMBER,
574 p_organization_id NUMBER,
575 p_inventory_item_id NUMBER,
576 p_category_id NUMBER,
577 p_problem_code VARCHAR2,
578 p_num NUMBER,
579 p_char VARCHAR2)
580 Is
581 Select gp.task_template_group_id
582 From cs_sr_tsk_tmpl_gp_map gp,
583 jtf_task_temp_groups_vl jtf
584 Where nvl(gp.incident_type_id, p_num) = nvl(p_incident_type_id, p_num)
585 And nvl(gp.organization_id, p_num) = nvl(p_organization_id, p_num)
586 And nvl(gp.inventory_item_id, p_num) = nvl(p_inventory_item_id, p_num)
587 And nvl(gp.category_id, p_num) = nvl(p_category_id, p_num)
588 And nvl(gp.problem_code, p_char) = nvl(p_problem_code, p_char)
589 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(gp.start_date,sysdate)) AND TRUNC(NVL(gp.end_date,sysdate))
590 AND jtf.task_template_group_id = gp.task_template_group_id
591 AND TRUNC(sysdate) BETWEEN TRUNC(NVL(jtf.start_date_active,sysdate)) AND TRUNC(NVL(jtf.end_date_active,sysdate));
592
593 Cursor c_category
594 (p_category_set_id NUMBER)
595 Is
596 SELECT category_id
597 FROM mtl_item_categories
598 WHERE organization_id = p_task_template_search_rec.organization_id
599 AND inventory_item_id = p_task_template_search_rec.inventory_item_id
600 AND category_set_id = p_category_set_id;
601
602 l_search_rules_rec c_search_rules%rowtype;
603
604 l_task_template_grp_rec c_task_template_grp%rowtype;
605
606 l_category_id NUMBER := NULL;
607
608 l_category_set_id NUMBER := NULL;
609
610 l_tbl_index BINARY_INTEGER := 0;
611
612 /* Added for passing NULLs if attribute is not part of search rule
613 --anmukher --10/13/03
614 */
615
616 l_incident_type_id NUMBER;
617
618 l_organization_id NUMBER;
619
620 l_inventory_item_id NUMBER;
621
622 l_problem_code VARCHAR2(30);
623
624 Begin
625
626 -- Initialize message list if p_init_msg_list is set to TRUE
627 IF FND_API.To_Boolean(p_init_msg_list) THEN
628 FND_MSG_PUB.Initialize;
629 END IF;
630
631 -- Standard call to check for call compatibility
632 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
633 l_api_name, G_PKG_NAME) THEN
634 FND_MSG_PUB.Count_And_Get(
635 p_count => x_msg_count,
636 p_data => x_msg_data );
637
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 END IF;
640
641 -- Initialize API return status to success
642 x_return_status := FND_API.G_RET_STS_SUCCESS;
643
644 -- Get all active search rules
645 open c_search_rules;
646
647 fetch c_search_rules into l_search_rules_rec;
648
649 While c_search_rules%found
650 Loop
651
652 --Initialize all local variables used for fetching group templates to NULL --anmukher
653 l_category_id := NULL;
654 l_incident_type_id := NULL;
655 l_organization_id := NULL;
656 l_inventory_item_id := NULL;
657 l_problem_code := NULL;
658
659 -- Check if search rule contains product category and if product category has been passed
660 If (instr(l_search_rules_rec.search_rule_code, 'C') > 0 And p_task_template_search_rec.category_id IS NOT NULL)
661 -- Or if search rule does not contain product category
662 Or (instr(l_search_rules_rec.search_rule_code, 'C') = 0) Then
663
664 -- Check if all search attributes have been passed
665 -- First assume that all relevant attributes
666 -- (those that are part of the search rule) have been passed
667 l_all_attrs_passed := TRUE;
668
669 -- Check for SR Type
670 If (instr(l_search_rules_rec.search_rule_code, 'T') > 0
671 And p_task_template_search_rec.incident_type_id IS NULL) Then
672 l_all_attrs_passed := FALSE;
673 End If;
674
675 -- Check for Problem Code
676 If (instr(l_search_rules_rec.search_rule_code, 'P') > 0
677 And p_task_template_search_rec.problem_code IS NULL) Then
678 l_all_attrs_passed := FALSE;
679 End If;
680
681 -- Check for Product
682 If (instr(l_search_rules_rec.search_rule_code, 'I') > 0
683 And ( p_task_template_search_rec.inventory_item_id IS NULL OR p_task_template_search_rec.organization_id IS NULL) ) Then
684 l_all_attrs_passed := FALSE;
685 End If;
686
687 -- Populate the local variables if attribute is part of search rule and has been passed
688 --anmukher --10/13/03
689 IF (instr(l_search_rules_rec.search_rule_code, 'C') > 0 And p_task_template_search_rec.category_id IS NOT NULL) THEN
690 l_category_id := p_task_template_search_rec.category_id;
691 END IF;
692
693 IF (instr(l_search_rules_rec.search_rule_code, 'T') > 0 And p_task_template_search_rec.incident_type_id IS NOT NULL) THEN
694 l_incident_type_id := p_task_template_search_rec.incident_type_id;
695 END IF;
696
697 IF (instr(l_search_rules_rec.search_rule_code, 'P') > 0 And p_task_template_search_rec.problem_code IS NOT NULL) THEN
698 l_problem_code := p_task_template_search_rec.problem_code;
699 END IF;
700
701 IF (instr(l_search_rules_rec.search_rule_code, 'I') > 0 And (p_task_template_search_rec.inventory_item_id IS NOT NULL AND p_task_template_search_rec.organization_id IS NOT NULL)) THEN
702 l_inventory_item_id := p_task_template_search_rec.inventory_item_id;
703 l_organization_id := p_task_template_search_rec.organization_id;
704 END IF;
705
706 If l_all_attrs_passed = TRUE Then
707 /*
708 open c_task_template_grp
709 (p_task_template_search_rec.incident_type_id,
710 p_task_template_search_rec.organization_id,
711 p_task_template_search_rec.inventory_item_id,
712 p_task_template_search_rec.category_id,
713 p_task_template_search_rec.problem_code,
714 NULL,
715 NULL,
716 -99,
717 'AA');
718 */
719 -- anmukher --10/13/03
720 open c_task_template_grp
721 (l_incident_type_id,
722 l_organization_id,
723 l_inventory_item_id,
724 l_category_id,
725 l_problem_code,
726 -99,
727 '@@');
728
729 fetch c_task_template_grp into l_task_template_grp_rec;
730
731 while c_task_template_grp%found
732 Loop
733 x_task_template_group_tbl(l_tbl_index).task_template_group_id := l_task_template_grp_rec.task_template_group_id;
734
735 l_tbl_index := l_tbl_index + 1;
736 fetch c_task_template_grp into l_task_template_grp_rec;
737 end loop; -- c_task_template_grp%found
738
739 close c_task_template_grp;
740
741 end if; -- If l_all_attrs_passed = TRUE
742
743 -- Check if product is passed and derive product category from product
744 -- if search rule contains product category (but category has not been passed)
745 ELSIF (instr(l_search_rules_rec.search_rule_code, 'C') > 0 And p_task_template_search_rec.category_id IS NULL)
746 AND (p_task_template_search_rec.inventory_item_id IS NOT NULL)
747 AND (p_task_template_search_rec.organization_id IS NOT NULL) Then
748
749 -- Check if all search attributes have been passed
750 -- First assume that all relevant attributes
751 -- (those that are part of the search rule) have been passed
752 l_all_attrs_passed := TRUE;
753
754 -- Check for SR Type
755 If (instr(l_search_rules_rec.search_rule_code, 'T') > 0
756 And p_task_template_search_rec.incident_type_id IS NULL) Then
757 l_all_attrs_passed := FALSE;
758 End If;
759
760 -- Check for Problem Code
761 If (instr(l_search_rules_rec.search_rule_code, 'P') > 0
762 And p_task_template_search_rec.problem_code IS NULL) Then
763 l_all_attrs_passed := FALSE;
764 End If;
765
766 -- Populate the local variables if attribute is part of search rule and has been passed
767 -- No need to populate category id since it is being fetched from cursor
768 --anmukher --10/13/03
769 IF (instr(l_search_rules_rec.search_rule_code, 'T') > 0 And p_task_template_search_rec.incident_type_id IS NOT NULL) THEN
770 l_incident_type_id := p_task_template_search_rec.incident_type_id;
771 END IF;
772
773 IF (instr(l_search_rules_rec.search_rule_code, 'P') > 0 And p_task_template_search_rec.problem_code IS NOT NULL) THEN
774 l_problem_code := p_task_template_search_rec.problem_code;
775 END IF;
776
777 IF (instr(l_search_rules_rec.search_rule_code, 'I') > 0 And (p_task_template_search_rec.inventory_item_id IS NOT NULL AND p_task_template_search_rec.organization_id IS NOT NULL)) THEN
778 l_inventory_item_id := p_task_template_search_rec.inventory_item_id;
779 l_organization_id := p_task_template_search_rec.organization_id;
780 END IF;
781
782 If l_all_attrs_passed = TRUE Then
783
784 -- Get the category set from the relevant site-level profile
785 l_category_set_id := fnd_profile.value('CS_SR_DEFAULT_CATEGORY_SET');
786
787 for l_category_rec in c_category(l_category_set_id)
788 loop
789 /*
790 open c_task_template_grp
791 (p_task_template_search_rec.incident_type_id,
792 p_task_template_search_rec.organization_id,
793 p_task_template_search_rec.inventory_item_id,
794 l_category_rec.category_id,
795 p_task_template_search_rec.problem_code,
796 NULL,
797 NULL,
798 -99,
799 'AA');
800 */
801 --anmukher --10/13/03
802 open c_task_template_grp
803 (l_incident_type_id,
804 l_organization_id,
805 l_inventory_item_id,
806 l_category_rec.category_id,
807 l_problem_code,
808 -99,
809 '@@');
810
811 fetch c_task_template_grp into l_task_template_grp_rec;
812
813 while c_task_template_grp%found
814 Loop
815 x_task_template_group_tbl(l_tbl_index).task_template_group_id := l_task_template_grp_rec.task_template_group_id;
816
817 l_tbl_index := l_tbl_index + 1;
818 fetch c_task_template_grp into l_task_template_grp_rec;
819 end loop; -- c_task_template_grp%found
820
821 close c_task_template_grp;
822
823 end loop; -- for l_category_rec in c_category(l_category_set_id)
824
825 end if; -- If l_all_attrs_passed = TRUE Then
826
827 end if; -- If (instr(l_search_rules_rec.search_rule_code, 'C') > 0 And p_task_template_search_rec.category_id IS NOT NULL)
828
829 -- If task template groups have been found then exit the loop, otherwise look for the next search rule
830 If l_tbl_index > 0 Then
831 exit;
832 Else
833 fetch c_search_rules into l_search_rules_rec;
834 End If;
835
836 end loop; -- While c_search_rules%found
837
838 close c_search_rules;
839
840 -- Standard call to get message count and if count is 1, get message info
841 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
842
843 EXCEPTION
844 WHEN FND_API.G_EXC_ERROR THEN
845 x_return_status := FND_API.G_RET_STS_ERROR;
846 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
847
848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
850 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
851
852 WHEN OTHERS THEN
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854 If c_task_template_grp%IsOpen Then
855 close c_task_template_grp;
856 End If;
857 If c_search_rules%IsOpen Then
858 close c_search_rules;
859 End If;
860 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
861 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
862 FND_MSG_PUB.ADD;
863 FND_MSG_PUB.Count_And_Get(
864 p_count => x_msg_count,
865 p_data => x_msg_data);
866
867 END Get_Task_Template_Group;
868
869
870
871 PROCEDURE Auto_Generate_Tasks
872 (
873 p_api_version IN NUMBER,
874 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
875 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
876 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
877 p_incident_id IN NUMBER,
878 p_service_request_rec IN Cs_ServiceRequest_PVT.Service_Request_rec_type,
879 p_task_template_group_owner IN NUMBER,
880 p_task_tmpl_group_owner_type IN VARCHAR2,
881 p_task_template_group_rec IN JTF_TASK_INST_TEMPLATES_PUB.task_template_group_info,
882 p_task_template_table IN JTF_TASK_INST_TEMPLATES_PUB.task_template_info_tbl,
883 x_auto_task_gen_rec OUT NOCOPY Cs_AutoGen_Task_PVT.auto_task_gen_rec_type,
884 x_return_status OUT NOCOPY VARCHAR2,
885 x_msg_count OUT NOCOPY NUMBER,
886 x_msg_data OUT NOCOPY VARCHAR2
887 )
888 IS
889
890 l_api_name CONSTANT VARCHAR2(30) := 'Auto_Generate_Tasks';
891 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
892
893 l_api_version CONSTANT NUMBER := 1.0;
894
895 l_field_service_task_created BOOLEAN;
896 l_return_status VARCHAR2(30);
897
898 l_task_template_search_rec CS_AutoGen_Task_PVT.Task_Template_Search_Rec_Type;
899 l_task_template_group_tbl Cs_AutoGen_Task_PVT.Task_Template_Group_Tbl_Type;
900 l_task_template_table JTF_TASK_INST_TEMPLATES_PUB.task_template_info_tbl;
901 l_task_template_group_rec JTF_TASK_INST_TEMPLATES_PUB.task_template_group_info;
902 l_task_template_group_owner NUMBER := NULL;
903 l_task_tmpl_group_owner_type VARCHAR2(240) := NULL;
904
905 l_tbl_index BINARY_INTEGER := 0;
906 l_tbl_ind BINARY_INTEGER := 0;
907 l_find_task_tmpl BOOLEAN := FALSE;
908 l_call_create_task_api BOOLEAN := FALSE;
909
910 l_planned_uom_value varchar2(30); -- 12.1.2 SHACHOUD
911 l_planned_effort_value number; -- 12.1.2 SHACHOUD
912
913 Cursor c_get_task_templates (p_task_template_group_id NUMBER)
914 Is
915 Select *
916 From Jtf_Task_Templates_vl
917 Where Task_Group_Id = p_task_template_group_id
918 And nvl(deleted_flag,'N') = 'N'; -- Bug 6429514
919
920 Cursor c_get_task_tmpl_grp_info (p_task_template_group_id NUMBER)
921 Is
922 Select *
923 From Jtf_Task_Temp_Groups_vl
924 Where Task_Template_Group_Id = p_task_template_group_id;
925
926 l_get_task_tmpl_grp_info_rec c_get_task_tmpl_grp_info%ROWTYPE;
927
928 l_task_tmpl_groups_found VARCHAR2(500) := ' - ';
929
930 Many_Task_Tmpl_Found EXCEPTION;
931
932 Task_Creation_Failed EXCEPTION;
933 l_planned_end_date DATE; -- 12.1.2 SR Task Enhancement Project
934
935 BEGIN
936 -- Declare a savepoint to rollback to in case of errors in task creation.
937 -- API standard start of API saveponit
938
939 SAVEPOINT CS_AutoGen_Task_PVT ;
940
941 -- Initialize message list if p_init_msg_list is set to TRUE
942 IF FND_API.To_Boolean(p_init_msg_list) THEN
943 FND_MSG_PUB.Initialize;
944 END IF;
945
946 -- Standard call to check for call compatibility
947 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
948 l_api_name, G_PKG_NAME) THEN
949 FND_MSG_PUB.Count_And_Get(
950 p_count => x_msg_count,
951 p_data => x_msg_data );
952
953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954 END IF;
955
956 -- Initialize API return status to success
957 x_return_status := FND_API.G_RET_STS_SUCCESS;
958
959 l_task_template_group_owner := p_task_template_group_owner;
960 l_task_tmpl_group_owner_type := p_task_tmpl_group_owner_type;
961
962 -- Check if task template group is provided
963 If p_task_template_group_rec.task_template_group_id IS NOT NULL Then
964
965 -- Populate the local variables with the passed values
966 -- These local variables will be passed to the Create Task From Template API later
967 l_task_template_group_rec := p_task_template_group_rec;
968
969 -- Check if any task templates are passed
970 If p_task_template_table.count > 0 Then
971 l_task_template_table := p_task_template_table;
972
973 -- Set flag to Call Create Task from Template API to TRUE
974 l_call_create_task_api := TRUE;
975
976 Else
977 -- Set flag to determine task templates from task template group to TRUE
978 l_find_task_tmpl := TRUE;
979
980 End If; -- If p_task_template_table.count > 0
981
982 -- Check if the following attributes are passed. If not, populate them with their default values.
983
984 l_task_template_group_rec.source_object_id := p_incident_id ;
985 l_task_template_group_rec.source_object_name := 'Service Request'; -- this object name is used in jtf_objects_vl for object code 'SR'
986 l_task_template_group_rec.cust_account_id := nvl(p_service_request_rec.account_id,l_task_template_group_rec.cust_account_id);
987 l_task_template_group_rec.customer_id := nvl(p_service_request_rec.customer_id,l_task_template_group_rec.customer_id);
988
989 Else
990
991 -- Populate the task template search rec type to be passed to the Get Task Template Group API
992 l_task_template_search_rec.incident_type_id := p_service_request_rec.type_id;
993 l_task_template_search_rec.organization_id := p_service_request_rec.inventory_org_id;
994 l_task_template_search_rec.inventory_item_id := p_service_request_rec.inventory_item_id;
995 l_task_template_search_rec.category_id := p_service_request_rec.category_id;
996 l_task_template_search_rec.problem_code := p_service_request_rec.problem_code;
997
998 -- Determine the task template group by calling the Get Task Template Group API
999
1000
1001 CS_Autogen_Task_PVT.Get_Task_Template_Group
1002 ( p_api_version => 1.0,
1003 p_init_msg_list => fnd_api.g_false,
1004 p_commit => p_commit,
1005 p_validation_level => p_validation_level,
1006 p_task_template_search_rec => l_task_template_search_rec,
1007 x_task_template_group_tbl => l_task_template_group_tbl,
1008 x_return_status => l_return_status,
1009 x_msg_count => x_msg_count,
1010 x_msg_data => x_msg_data
1011 );
1012
1013
1014 If (l_return_status = FND_API.G_RET_STS_ERROR) Then
1015 Raise FND_API.G_EXC_ERROR;
1016 Elsif (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) Then
1017 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1018 End If;
1019
1020 -- Check the number of task template groups returned by the Get Task Template Group API
1021 -- If l_task_template_group_tbl.count > 0 Then
1022 While l_tbl_index < l_task_template_group_tbl.count
1023 Loop
1024 -- Retrieve the Task Template Group Information
1025 Open c_get_task_tmpl_grp_info (l_task_template_group_tbl(l_tbl_index).task_template_group_id);
1026 Fetch c_get_task_tmpl_grp_info Into l_get_task_tmpl_grp_info_rec;
1027 -- Populate local record type variable to be passed to Create Task From Template API with the Task Template Group Information
1028 If c_get_task_tmpl_grp_info%FOUND Then
1029 l_task_template_group_rec.task_template_group_id := l_get_task_tmpl_grp_info_rec.task_template_group_id;
1030 l_task_template_group_rec.source_object_id := p_incident_id;
1031 l_task_template_group_rec.source_object_name := 'Service Request'; -- this object name is used in jtf_objects_vl for object code 'SR'
1032 l_task_template_group_rec.cust_account_id := p_service_request_rec.account_id;
1033 l_task_template_group_rec.customer_id := p_service_request_rec.customer_id;
1034 l_task_template_group_rec.attribute1 := l_get_task_tmpl_grp_info_rec.attribute1;
1035 l_task_template_group_rec.attribute2 := l_get_task_tmpl_grp_info_rec.attribute2;
1036 l_task_template_group_rec.attribute3 := l_get_task_tmpl_grp_info_rec.attribute3;
1037 l_task_template_group_rec.attribute4 := l_get_task_tmpl_grp_info_rec.attribute4;
1038 l_task_template_group_rec.attribute5 := l_get_task_tmpl_grp_info_rec.attribute5;
1039 l_task_template_group_rec.attribute6 := l_get_task_tmpl_grp_info_rec.attribute6;
1040 l_task_template_group_rec.attribute7 := l_get_task_tmpl_grp_info_rec.attribute7;
1041 l_task_template_group_rec.attribute8 := l_get_task_tmpl_grp_info_rec.attribute8;
1042 l_task_template_group_rec.attribute9 := l_get_task_tmpl_grp_info_rec.attribute9;
1043 l_task_template_group_rec.attribute10 := l_get_task_tmpl_grp_info_rec.attribute10;
1044 l_task_template_group_rec.attribute11 := l_get_task_tmpl_grp_info_rec.attribute11;
1045 l_task_template_group_rec.attribute12 := l_get_task_tmpl_grp_info_rec.attribute12;
1046 l_task_template_group_rec.attribute13 := l_get_task_tmpl_grp_info_rec.attribute13;
1047 l_task_template_group_rec.attribute14 := l_get_task_tmpl_grp_info_rec.attribute14;
1048 l_task_template_group_rec.attribute15 := l_get_task_tmpl_grp_info_rec.attribute15;
1049 l_task_template_group_rec.attribute_category := l_get_task_tmpl_grp_info_rec.attribute_category;
1050
1051 IF p_service_request_rec.incident_location_type = 'HZ_PARTY_SITE' THEN
1052 l_task_template_group_rec.address_id := p_service_request_rec.incident_location_id;
1053 ELSIF p_service_request_rec.incident_location_type = 'HZ_LOCATION' THEN
1054 l_task_template_group_rec.customer_id := null;
1055 l_task_template_group_rec.address_id := null;
1056 l_task_template_group_rec.location_id := p_service_request_rec.incident_location_id;
1057 END IF ;
1058
1059
1060 -- Concatenate the task template group name to the local variable to be returned with error message if multiple templates are found
1061 -- Add a comma at the end of the concatenated string if there are multiple template groups
1062 If l_tbl_index > 0 Then
1063 l_task_tmpl_groups_found := l_task_tmpl_groups_found || ', ';
1064 End If; -- If l_tbl_index > 0
1065
1066 l_task_tmpl_groups_found := l_task_tmpl_groups_found || l_get_task_tmpl_grp_info_rec.template_group_name;
1067
1068 End If; -- If c_get_task_tmpl_grp_info%FOUND
1069 -- Increment the table index (which is also a counter for number of task templates found) by 1
1070 l_tbl_index := l_tbl_index + 1;
1071 -- Close the cursor
1072 Close c_get_task_tmpl_grp_info;
1073 End Loop;
1074
1075 -- End If; -- If l_task_template_group_tbl.count > 0
1076
1077 If l_tbl_index = 0 Then
1078 -- No task template groups are found
1079 x_return_status := FND_API.G_RET_STS_SUCCESS;
1080 x_auto_task_gen_rec.auto_task_gen_attempted := FALSE;
1081
1082 Elsif l_tbl_index > 1 Then
1083 -- Multiple task template groups are found
1084 -- Modified error handling logic --anmukher --08/22/03
1085 x_auto_task_gen_rec.auto_task_gen_attempted := TRUE;
1086 Raise Many_Task_Tmpl_Found;
1087
1088 Elsif l_tbl_index = 1 Then
1089 -- Only one task template group is found
1090 -- Set flag to determine task templates from task template group to TRUE
1091 l_find_task_tmpl := TRUE;
1092
1093 End If; -- If l_tbl_index = 0
1094
1095 End If; -- If p_task_template_group_rec.task_template_group_id IS NOT NULL
1096
1097 If l_find_task_tmpl = TRUE Then
1098
1099 -- Need to determine the task templates based on the task template group
1100 -- l_tbl_index := 0; -- l_tbl_index is the index for l_task_template_group_tbl, l_tbl_ind is the index for l_task_template_table
1101
1102 -- Open cursor (in For Loop) to retrieve the task templates
1103 l_tbl_ind := 1 ;
1104
1105 For l_task_tmpl_rec IN c_get_task_templates (l_task_template_group_rec.task_template_group_id)
1106 Loop
1107
1108 -- Populate the local table type variable which will be passed to the Create Task from template API
1109 l_task_template_table(l_tbl_ind).task_template_id := l_task_tmpl_rec.task_template_id;
1110 l_task_template_table(l_tbl_ind).task_name := l_task_tmpl_rec.task_name;
1111 l_task_template_table(l_tbl_ind).description := l_task_tmpl_rec.description;
1112 l_task_template_table(l_tbl_ind).task_type_id := l_task_tmpl_rec.task_type_id;
1113 l_task_template_table(l_tbl_ind).task_status_id := l_task_tmpl_rec.task_status_id;
1114 l_task_template_table(l_tbl_ind).task_priority_id := l_task_tmpl_rec.task_priority_id;
1115 l_task_template_table(l_tbl_ind).duration := l_task_tmpl_rec.duration;
1116 l_task_template_table(l_tbl_ind).duration_uom := l_task_tmpl_rec.duration_uom;
1117 l_task_template_table(l_tbl_ind).planned_effort := l_task_tmpl_rec.planned_effort;
1118 l_task_template_table(l_tbl_ind).planned_effort_uom := l_task_tmpl_rec.planned_effort_uom;
1119 l_task_template_table(l_tbl_ind).private_flag := l_task_tmpl_rec.private_flag;
1120 l_task_template_table(l_tbl_ind).restrict_closure_flag := l_task_tmpl_rec.restrict_closure_flag;
1121 l_task_template_table(l_tbl_ind).attribute1 := l_task_tmpl_rec.attribute1;
1122 l_task_template_table(l_tbl_ind).attribute2 := l_task_tmpl_rec.attribute2;
1123 l_task_template_table(l_tbl_ind).attribute3 := l_task_tmpl_rec.attribute3;
1124 l_task_template_table(l_tbl_ind).attribute4 := l_task_tmpl_rec.attribute4;
1125 l_task_template_table(l_tbl_ind).attribute5 := l_task_tmpl_rec.attribute5;
1126 l_task_template_table(l_tbl_ind).attribute6 := l_task_tmpl_rec.attribute6;
1127 l_task_template_table(l_tbl_ind).attribute7 := l_task_tmpl_rec.attribute7;
1128 l_task_template_table(l_tbl_ind).attribute8 := l_task_tmpl_rec.attribute8;
1129 l_task_template_table(l_tbl_ind).attribute9 := l_task_tmpl_rec.attribute9;
1130 l_task_template_table(l_tbl_ind).attribute10 := l_task_tmpl_rec.attribute10;
1131 l_task_template_table(l_tbl_ind).attribute11 := l_task_tmpl_rec.attribute11;
1132 l_task_template_table(l_tbl_ind).attribute12 := l_task_tmpl_rec.attribute12;
1133 l_task_template_table(l_tbl_ind).attribute13 := l_task_tmpl_rec.attribute13;
1134 l_task_template_table(l_tbl_ind).attribute14 := l_task_tmpl_rec.attribute14;
1135 l_task_template_table(l_tbl_ind).attribute15 := l_task_tmpl_rec.attribute15;
1136 l_task_template_table(l_tbl_ind).attribute_category := l_task_tmpl_rec.attribute_category;
1137 l_task_template_table(l_tbl_ind).enable_workflow := 'Y';
1138
1139 -- Simplex
1140 -- Get the value of the confirmation status for all the task types in the task template group
1141 -- This value is used for validation in create_task_from_template procedure
1142
1143 l_task_template_table(l_tbl_ind).task_confirmation_status := l_task_tmpl_rec.task_confirmation_status;
1144
1145 -- end Simplex
1146
1147 -- 12.1.2 SR Task Enhancement project
1148 -- Get the Planned End date based on the Profile
1149 l_planned_uom_value := l_task_template_table(l_tbl_ind).planned_effort_uom;
1150 l_planned_effort_value := l_task_template_table(l_tbl_ind).planned_effort;
1151
1152 if ( l_planned_uom_value = 'DAY') then
1153 l_planned_effort_value := l_planned_effort_value +1;
1154 end if;
1155
1156 CS_AutoGen_Task_PVT.Default_Planned_End_Date(p_service_request_rec.obligation_date,
1157 p_service_request_rec.exp_resolution_date,
1158 l_planned_uom_value,
1159 l_planned_effort_value,
1160 l_planned_end_date);
1161
1162 l_task_template_table(l_tbl_ind).planned_start_date := sysdate;
1163 l_task_template_table(l_tbl_ind).planned_end_date := l_planned_end_date;
1164 -- End of 12.1.2 project code
1165
1166 -- Increment the pl/sql table index by 1
1167 l_tbl_ind := l_tbl_ind + 1;
1168
1169 End Loop; -- For l_task_tmpl_rec IN c_get_task_templates (l_task_template_group_rec.task_template_group_id)
1170
1171 -- Check if any task templates have been retrieved
1172 If l_tbl_ind > 0 Then
1173 -- Set flag to Call Create Task from Template API to TRUE
1174 l_call_create_task_api := TRUE;
1175 Else
1176 -- If no task templates are retrieved, then set the task generation attempted flag to FALSE
1177 x_auto_task_gen_rec.auto_task_gen_attempted := FALSE;
1178 End If; -- If l_tbl_ind > 0
1179
1180 End If; -- If l_find_task_tmpl = TRUE
1181
1182 -- Check if call to Create Task From Template should be made
1183 If (l_call_create_task_api = TRUE) Then
1184
1185
1186 -- Call API to create task from template
1187 Create_Task_From_Template
1188 ( p_task_template_group_owner => l_task_template_group_owner,
1189 p_task_tmpl_group_owner_type => l_task_tmpl_group_owner_type,
1190 p_incident_id => p_incident_id ,
1191 p_service_request_rec => p_service_request_rec,
1192 p_task_template_group_info => l_task_template_group_rec,
1193 p_task_template_tbl => l_task_template_table,
1194 x_field_service_task_created => l_field_service_task_created,
1195 x_return_status => l_return_status,
1196 x_msg_count => x_msg_count,
1197 x_msg_data => x_msg_data
1198 );
1199
1200
1201 If (l_return_status = FND_API.G_RET_STS_SUCCESS) Then
1202 x_return_status := l_return_status;
1203 x_auto_task_gen_rec.auto_task_gen_attempted := TRUE;
1204 x_auto_task_gen_rec.field_service_task_created := l_field_service_task_created;
1205 -- Modified error handling logic --anmukher --08/22/03
1206 Elsif (l_return_status = FND_API.G_RET_STS_ERROR) OR (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) Then
1207 x_auto_task_gen_rec.auto_task_gen_attempted := TRUE;
1208 Raise Task_Creation_Failed;
1209 End If;
1210
1211 End If; -- If (l_call_create_task_api = TRUE)
1212
1213 -- Standard call to get message count and if count is 1, get message info
1214 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1215
1216 -- Standard check of p_commit
1217 IF FND_API.To_Boolean(p_commit) THEN
1218 COMMIT WORK;
1219 END IF;
1220
1221
1222 EXCEPTION
1223 WHEN FND_API.G_EXC_ERROR THEN
1224 ROLLBACK to CS_AutoGen_Task_PVT ;
1225 x_return_status := FND_API.G_RET_STS_ERROR;
1226 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1227
1228 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1229 ROLLBACK to CS_AutoGen_Task_PVT ;
1230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1231 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1232
1233 -- Added new exception handler for mutiple template group error --anmukher -- 08/22/03
1234 WHEN Many_Task_Tmpl_Found THEN
1235 ROLLBACK to CS_AutoGen_Task_PVT ;
1236 x_return_status := FND_API.G_RET_STS_ERROR;
1237 FND_MESSAGE.SET_NAME ('CS', 'CS_SR_TGT_MULTIPLE_TGT_ERROR');
1238 FND_MESSAGE.SET_TOKEN ('TASK_GROUP_TEMPLATES',l_task_tmpl_groups_found);
1239 FND_MSG_PUB.ADD;
1240 FND_MSG_PUB.Count_And_Get(
1241 p_count => x_msg_count,
1242 p_data => x_msg_data);
1243
1244 -- Added new exception handler for auto task creation failure with single template group --anmukher -- 08/22/03
1245 WHEN Task_Creation_Failed THEN
1246 ROLLBACK to CS_AutoGen_Task_PVT ;
1247 x_return_status := FND_API.G_RET_STS_ERROR;
1248 -- Message commented out since Create_Task_From_Template API is already
1249 -- stacking specific error message on failure --anmukher --10/30/2003
1250 /*
1251 FND_MESSAGE.SET_NAME ('CS', 'CS_SR_TGT_SINGLE_TGT_ERROR');
1252 FND_MESSAGE.SET_TOKEN ('TASK_TEMPLATE_GROUP',l_task_tmpl_groups_found);
1253 FND_MSG_PUB.ADD;
1254 */
1255 FND_MSG_PUB.Count_And_Get(
1256 p_count => x_msg_count,
1257 p_data => x_msg_data);
1258
1259 WHEN OTHERS THEN
1260 ROLLBACK to CS_AutoGen_Task_PVT ;
1261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1262 If c_get_task_tmpl_grp_info%IsOpen Then
1263 close c_get_task_tmpl_grp_info;
1264 End If;
1265 If c_get_task_templates%IsOpen Then
1266 close c_get_task_templates;
1267 End If;
1268 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
1269 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
1270 FND_MSG_PUB.ADD;
1271 FND_MSG_PUB.Count_And_Get(
1272 p_count => x_msg_count,
1273 p_data => x_msg_data);
1274
1275 END Auto_Generate_Tasks;
1276
1277
1278 FUNCTION Get_Task_Template_Status
1279 ( p_start_date IN DATE ,
1280 p_end_date IN DATE ) RETURN VARCHAR2 IS
1281
1282 l_status VARCHAR2(40) ;
1283
1284 BEGIN
1285 IF ((TRUNC(sysdate) >= TRUNC(NVL(p_start_date,sysdate))) AND
1286 (TRUNC(sysdate) <= TRUNC(NVL(p_end_date,sysdate)))) THEN
1287
1288 l_status := 'Active';
1289
1290 SELECT meaning
1291 INTO l_status
1292 FROM cs_lookups
1293 WHERE lookup_type = 'CS_SR_TSK_TMPL_STATUS_DISP'
1294 AND lookup_code = 'ACTIVE' ;
1295 ELSE
1296 l_status := 'Inactive';
1297
1298 SELECT meaning
1299 INTO l_status
1300 FROM cs_lookups
1301 WHERE lookup_type = 'CS_SR_TSK_TMPL_STATUS_DISP'
1302 AND lookup_code = 'INACTIVE' ;
1303 END IF;
1304
1305 RETURN l_status ;
1306
1307 EXCEPTION
1308 WHEN others THEN
1309 return l_status ;
1310 END Get_Task_Template_Status;
1311
1312
1313
1314 END CS_AutoGen_Task_PVT;
[Home] [Help]