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