[Home] [Help]
PACKAGE BODY: APPS.OZF_TASK_TEMPLATE_PVT
Source
1 PACKAGE BODY OZF_TASK_TEMPLATE_PVT AS
2 /* $Header: ozfvtteb.pls 115.2 2003/11/19 08:24:38 upoluri noship $ */
3 --
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_TASK_TEMPLATE_PVT';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvtteb.pls';
7
8
9 ---------------------------------------------------------------------
10 -- PROCEDURE
11 -- Create_TaskTemplate
12 --
13 -- PURPOSE
14 -- Create a task template.
15 --
16 -- PARAMETERS
17 -- p_insert_reason : the new record to be inserted
18 -- x_reason_code_id : return the reason_code_id of the new reason code
19 --
20 -- NOTES
21 -- 1. object_version_number will be set to 1.
22 ---------------------------------------------------------------------
23 PROCEDURE Create_TaskTemplate (
24 p_api_version IN NUMBER
25 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
26 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
27 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
28
29 ,x_return_status OUT NOCOPY VARCHAR2
30 ,x_msg_data OUT NOCOPY VARCHAR2
31 ,x_msg_count OUT NOCOPY NUMBER
32
33 ,p_task_template IN ozf_task_template_tbl_type
34 ,x_task_template_id OUT NOCOPY ozf_number_tbl_type
35 )
36 IS
37 l_api_name CONSTANT VARCHAR2(30) := 'Create_TaskTemplate';
38 l_api_version CONSTANT NUMBER := 1.0;
39 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
40 --
41 l_resource_id number;
42 l_user_id number;
43 l_login_user_id number;
44 l_login_user_status varchar2(30);
45 l_Error_Msg varchar2(2000);
46 l_Error_Token varchar2(80);
47 l_object_version_number number := 1;
48 --
49 BEGIN
50 -- Standard begin of API savepoint
51 SAVEPOINT Create_TaskTemplate_PVT;
52 -- Standard call to check for call compatibility.
53 IF NOT FND_API.Compatible_API_Call (
54 l_api_version,
55 p_api_version,
56 l_api_name,
57 G_PKG_NAME)
58 THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61 -- Debug Message
62 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
63 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
64 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
65 FND_MSG_PUB.Add;
66 END IF;
67 --Initialize message list if p_init_msg_list is TRUE.
68 IF FND_API.To_Boolean (p_init_msg_list) THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71 -- Initialize API return status to sucess
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 x_task_template_id := ozf_number_tbl_type();
75 BEGIN
76 FOR i in 1..p_task_template.count LOOP
77 x_task_template_id.extend;
78 JTF_TASK_TEMPLATES_PUB.CREATE_TASK
79 (
80 P_API_VERSION => p_api_version,
81 P_INIT_MSG_LIST => FND_API.G_FALSE,
82 P_COMMIT => FND_API.G_FALSE,
83 P_TASK_GROUP_ID => p_task_template(i).reason_code_id,
84 P_TASK_NAME => p_task_template(i).task_name,
85 P_TASK_TYPE_ID => p_task_template(i).task_type_id,
86 P_DESCRIPTION => p_task_template(i).description,
87 P_TASK_STATUS_ID => p_task_template(i).task_status_id,
88 P_TASK_PRIORITY_ID => p_task_template(i).task_priority_id,
89 P_DURATION => p_task_template(i).duration,
90 P_DURATION_UOM => p_task_template(i).duration_uom,
91 X_RETURN_STATUS => x_return_status,
92 X_MSG_COUNT => x_msg_count,
93 X_MSG_DATA => x_msg_data,
94 X_TASK_ID => x_task_template_id(i)
95 );
96 -- CHECK to see if we require this flag
97 --p_multi_booked_flag IN VARCHAR2 DEFAULT NULL,
98 END LOOP;
99 EXCEPTION
100 WHEN OTHERS THEN
101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
102 END;
103
104 --Standard check of commit
105 IF FND_API.To_Boolean ( p_commit ) THEN
106 COMMIT WORK;
107 END IF;
108 -- Debug Message
109 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
110 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
111 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
112 FND_MSG_PUB.Add;
113 END IF;
114 --Standard call to get message count and if count=1, get the message
115 FND_MSG_PUB.Count_And_Get (
116 p_encoded => FND_API.G_FALSE,
117 p_count => x_msg_count,
118 p_data => x_msg_data
119 );
120 EXCEPTION
121 WHEN FND_API.G_EXC_ERROR THEN
122 ROLLBACK TO Create_TaskTemplate_PVT;
123 x_return_status := FND_API.G_RET_STS_ERROR;
124 -- Standard call to get message count and if count=1, get the message
125 FND_MSG_PUB.Count_And_Get (
126 p_encoded => FND_API.G_FALSE,
127 p_count => x_msg_count,
128 p_data => x_msg_data
129 );
130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
131 ROLLBACK TO Create_TaskTemplate_PVT;
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133 -- Standard call to get message count and if count=1, get the message
134 FND_MSG_PUB.Count_And_Get (
135 p_encoded => FND_API.G_FALSE,
136 p_count => x_msg_count,
137 p_data => x_msg_data
138 );
139 WHEN OTHERS THEN
140 ROLLBACK TO Create_TaskTemplate_PVT;
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
143 THEN
144 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
145 END IF;
146 -- Standard call to get message count and if count=1, get the message
147 FND_MSG_PUB.Count_And_Get (
148 p_encoded => FND_API.G_FALSE,
149 p_count => x_msg_count,
150 p_data => x_msg_data
151 );
152 --
153 END Create_TaskTemplate;
154 ---------------------------------------------------------------------
155 -- PROCEDURE
156 -- Update_TaskTemplate
157 --
158 -- PURPOSE
159 -- Update task template.
160 --
161 -- PARAMETERS
162 -- p_task_template : the record with new items.
163 --
164 -- NOTES
165 -- 1. Raise exception if the object_version_number doesn't match.
166 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
167 -- that column won't be updated.
168 ----------------------------------------------------------------------
169 PROCEDURE Update_TaskTemplate (
170 p_api_version IN NUMBER
171 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
172 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
173 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
174
175 ,x_return_status OUT NOCOPY VARCHAR2
176 ,x_msg_data OUT NOCOPY VARCHAR2
177 ,x_msg_count OUT NOCOPY NUMBER
178
179 ,p_task_template IN ozf_task_template_tbl_type
180 ,x_object_version_number OUT NOCOPY ozf_number_tbl_type
181 )
182 IS
183 l_api_name CONSTANT VARCHAR2(30) := 'Update_TaskTemplate';
184 l_api_version CONSTANT NUMBER := 1.0;
185 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
186 --
187 l_resource_id number;
188 l_user_id number;
189 l_login_user_id number;
190 l_login_user_status varchar2(30);
191 l_Error_Msg varchar2(2000);
192 l_Error_Token varchar2(80);
193 l_object_version_number number := 1;
194 --
195 BEGIN
196 -- Standard begin of API savepoint
197 SAVEPOINT Update_TaskTemplate_PVT;
198 -- Standard call to check for call compatibility.
199 IF NOT FND_API.Compatible_API_Call (
200 l_api_version,
201 p_api_version,
202 l_api_name,
203 G_PKG_NAME)
204 THEN
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206 END IF;
207 -- Debug Message
208 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
209 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
210 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
211 FND_MSG_PUB.Add;
212 END IF;
213 --Initialize message list if p_init_msg_list is TRUE.
214 IF FND_API.To_Boolean (p_init_msg_list) THEN
215 FND_MSG_PUB.initialize;
216 END IF;
217 -- Initialize API return status to sucess
218 x_return_status := FND_API.G_RET_STS_SUCCESS;
219
220 BEGIN
221 x_object_version_number := ozf_number_tbl_type();
222 FOR i in 1..p_task_template.count LOOP
223 x_object_version_number.extend;
224 x_object_version_number(i) := p_task_template(i).object_version_number;
225 JTF_TASK_TEMPLATES_PUB.UPDATE_TASK
226 (
227 P_API_VERSION => p_api_version,
228 P_INIT_MSG_LIST => FND_API.G_FALSE,
229 P_COMMIT => FND_API.G_FALSE,
230 P_OBJECT_VERSION_NUMBER => x_object_version_number(i),
231 P_TASK_ID => p_task_template(i).task_template_id,
232 P_TASK_NUMBER => p_task_template(i).task_number,
233 P_TASK_GROUP_ID => p_task_template(i).reason_code_id,
234 P_TASK_NAME => p_task_template(i).task_name,
235 P_TASK_TYPE_ID => p_task_template(i).task_type_id,
236 P_DESCRIPTION => p_task_template(i).description,
237 P_TASK_STATUS_ID => p_task_template(i).task_status_id,
238 P_TASK_PRIORITY_ID => p_task_template(i).task_priority_id,
239 P_DURATION => p_task_template(i).duration,
240 P_DURATION_UOM => p_task_template(i).duration_uom,
241 X_RETURN_STATUS => x_return_status,
242 X_MSG_COUNT => x_msg_count,
243 X_MSG_DATA => x_msg_data
244 );
245 END LOOP;
246 EXCEPTION
247 WHEN OTHERS THEN
248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
249 END;
250
251 --Standard check of commit
252 IF FND_API.To_Boolean ( p_commit ) THEN
253 COMMIT WORK;
254 END IF;
255 -- Debug Message
256 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
257 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
258 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
259 FND_MSG_PUB.Add;
260 END IF;
261 --Standard call to get message count and if count=1, get the message
262 FND_MSG_PUB.Count_And_Get (
263 p_encoded => FND_API.G_FALSE,
264 p_count => x_msg_count,
265 p_data => x_msg_data
266 );
267 EXCEPTION
268 WHEN FND_API.G_EXC_ERROR THEN
269 ROLLBACK TO Update_TaskTemplate_PVT;
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 -- Standard call to get message count and if count=1, get the message
272 FND_MSG_PUB.Count_And_Get (
273 p_encoded => FND_API.G_FALSE,
274 p_count => x_msg_count,
275 p_data => x_msg_data
276 );
277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
278 ROLLBACK TO Update_TaskTemplate_PVT;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 -- Standard call to get message count and if count=1, get the message
281 FND_MSG_PUB.Count_And_Get (
282 p_encoded => FND_API.G_FALSE,
283 p_count => x_msg_count,
284 p_data => x_msg_data
285 );
286 WHEN OTHERS THEN
287 ROLLBACK TO Update_TaskTemplate_PVT;
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
290 THEN
291 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
292 END IF;
293 -- Standard call to get message count and if count=1, get the message
294 FND_MSG_PUB.Count_And_Get (
295 p_encoded => FND_API.G_FALSE,
296 p_count => x_msg_count,
297 p_data => x_msg_data
298 );
299 --
300 END Update_TaskTemplate;
301 ---------------------------------------------------------------------
302 -- PROCEDURE
303 -- Delete_TaskTemplate
304 --
305 -- PURPOSE
306 -- Delete a task template.
307 --
308 -- PARAMETERS
309 -- p_task_template_id : template to be deleted
310 -- p_object_version_number : object version number
311 --
312 -- NOTES
313 -- 1. Raise exception if the object_version_number doesn't match.
314 ----------------------------------------------------------------------
315 PROCEDURE Delete_TaskTemplate (
316 p_api_version IN NUMBER
317 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
318 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
319 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
320
321 ,x_return_status OUT NOCOPY VARCHAR2
322 ,x_msg_data OUT NOCOPY VARCHAR2
323 ,x_msg_count OUT NOCOPY NUMBER
324
325 ,p_task_template_id IN ozf_number_tbl_type
326 ,p_object_version_number IN ozf_number_tbl_type
327 )
328 IS
329 l_api_name CONSTANT VARCHAR2(30) := 'Delete_TaskTemplate';
330 l_api_version CONSTANT NUMBER := 1.0;
331 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
332 --
333 l_resource_id number;
334 l_user_id number;
335 l_login_user_id number;
336 l_login_user_status varchar2(30);
337 l_Error_Msg varchar2(2000);
338 l_Error_Token varchar2(80);
339 l_object_version_number number := 1;
340 --
341 BEGIN
342 -- Standard begin of API savepoint
343 SAVEPOINT Delete_TaskTemplate_PVT;
344 -- Standard call to check for call compatibility.
345 IF NOT FND_API.Compatible_API_Call (
346 l_api_version,
347 p_api_version,
348 l_api_name,
349 G_PKG_NAME)
350 THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353 -- Debug Message
354 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
355 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
356 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
357 FND_MSG_PUB.Add;
358 END IF;
359 --Initialize message list if p_init_msg_list is TRUE.
360 IF FND_API.To_Boolean (p_init_msg_list) THEN
361 FND_MSG_PUB.initialize;
362 END IF;
363 -- Initialize API return status to sucess
364 x_return_status := FND_API.G_RET_STS_SUCCESS;
365
366 IF p_task_template_id.count <> p_object_version_number.count THEN
367 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
368 FND_MESSAGE.Set_Name('OZF','OZF_CLAIM_INCONSISTANT_TASK_RECORDS');
369 FND_MSG_PUB.Add;
370 END IF;
371 END IF;
372
373 BEGIN
374 FOR i in 1..p_task_template_id.count LOOP
375 JTF_TASK_TEMPLATES_PUB.DELETE_TASK
376 (
377 P_API_VERSION => p_api_version,
378 P_INIT_MSG_LIST => FND_API.G_FALSE,
379 P_COMMIT => FND_API.G_FALSE,
380 P_OBJECT_VERSION_NUMBER => p_object_version_number(i),
381 P_TASK_ID => p_task_template_id(i),
382 X_RETURN_STATUS => x_return_status,
383 X_MSG_COUNT => x_msg_count,
384 X_MSG_DATA => x_msg_data
385 );
386 --P_TASK_NUMBER => p_task_number,
387 END LOOP;
388 EXCEPTION
389 WHEN OTHERS THEN
390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
391 END;
392
393 --Standard check of commit
394 IF FND_API.To_Boolean ( p_commit ) THEN
395 COMMIT WORK;
396 END IF;
397 -- Debug Message
398 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
399 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
400 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
401 FND_MSG_PUB.Add;
402 END IF;
403 --Standard call to get message count and if count=1, get the message
404 FND_MSG_PUB.Count_And_Get (
405 p_encoded => FND_API.G_FALSE,
406 p_count => x_msg_count,
407 p_data => x_msg_data
408 );
409 EXCEPTION
410 WHEN FND_API.G_EXC_ERROR THEN
411 ROLLBACK TO Delete_TaskTemplate_PVT;
412 x_return_status := FND_API.G_RET_STS_ERROR;
413 -- Standard call to get message count and if count=1, get the message
414 FND_MSG_PUB.Count_And_Get (
415 p_encoded => FND_API.G_FALSE,
416 p_count => x_msg_count,
417 p_data => x_msg_data
418 );
419 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
420 ROLLBACK TO Delete_TaskTemplate_PVT;
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 -- Standard call to get message count and if count=1, get the message
423 FND_MSG_PUB.Count_And_Get (
424 p_encoded => FND_API.G_FALSE,
425 p_count => x_msg_count,
426 p_data => x_msg_data
427 );
428 WHEN OTHERS THEN
429 ROLLBACK TO Delete_TaskTemplate_PVT;
430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
431 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
432 THEN
433 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
434 END IF;
435 -- Standard call to get message count and if count=1, get the message
436 FND_MSG_PUB.Count_And_Get (
437 p_encoded => FND_API.G_FALSE,
438 p_count => x_msg_count,
439 p_data => x_msg_data
440 );
441 --
442 END Delete_TaskTemplate;
443 ---------------------------------------------------------------------
444 -- PROCEDURE
445 -- Get_TaskTemplate
446 --
447 -- PURPOSE
448 -- Get task template.
449 --
450 -- PARAMETERS
451 -- p_task_group_id : template to be deleted
452 --
453 -- NOTES
454 -- 1. Raise exception if the task group id doesn't exist.
455 ----------------------------------------------------------------------
456 PROCEDURE Get_TaskTemplate (
457 p_api_version IN NUMBER
458 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
459 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
460 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
461
462 ,x_return_status OUT NOCOPY VARCHAR2
463 ,x_msg_data OUT NOCOPY VARCHAR2
464 ,x_msg_count OUT NOCOPY NUMBER
465
466 ,p_reason_code_id IN NUMBER
467 ,x_task_template OUT NOCOPY ozf_task_template_tbl_type
468 )
469 IS
470 l_api_name CONSTANT VARCHAR2(30) := 'Get_TaskTemplate';
471 l_api_version CONSTANT NUMBER := 1.0;
472 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
473 --
474 l_resource_id number;
475 l_user_id number;
476 l_login_user_id number;
477 l_login_user_status varchar2(30);
478 l_Error_Msg varchar2(2000);
479 l_Error_Token varchar2(80);
480 l_object_version_number number := 1;
481 --
482 l_rec_count number := 1;
483
484 CURSOR c_tasks (cv_task_group_id NUMBER) IS
485 SELECT JTT.TASK_TEMPLATE_ID
486 , JTT.TASK_NAME
487 , JTT.DESCRIPTION
488 , JTT.TASK_GROUP_ID
489 , JTG.TEMPLATE_GROUP_NAME
490 , JTT.TASK_NUMBER
491 , JTT.TASK_TYPE_ID
492 , JTY.NAME
493 , JTT.TASK_STATUS_ID
494 , JTS.NAME
495 , JTT.TASK_PRIORITY_ID
496 , JTP.NAME
497 , JTT.DURATION
498 , JTT.DURATION_UOM
499 , JTT.OBJECT_VERSION_NUMBER
500 , JTT.ATTRIBUTE_CATEGORY
501 , JTT.ATTRIBUTE1
502 , JTT.ATTRIBUTE2
503 , JTT.ATTRIBUTE3
504 , JTT.ATTRIBUTE4
505 , JTT.ATTRIBUTE5
506 , JTT.ATTRIBUTE6
507 , JTT.ATTRIBUTE7
508 , JTT.ATTRIBUTE8
509 , JTT.ATTRIBUTE9
510 , JTT.ATTRIBUTE10
511 , JTT.ATTRIBUTE11
512 , JTT.ATTRIBUTE12
513 , JTT.ATTRIBUTE13
514 , JTT.ATTRIBUTE14
515 , JTT.ATTRIBUTE15
516 FROM JTF_TASK_TEMPLATES_VL JTT
517 , JTF_TASK_TEMP_GROUPS_VL JTG
518 , JTF_TASK_TYPES_VL JTY
519 , JTF_TASK_STATUSES_VL JTS
520 , JTF_TASK_PRIORITIES_VL JTP
521 WHERE JTT.TASK_GROUP_ID = cv_task_group_id
522 AND JTT.TASK_GROUP_ID = JTG.TASK_TEMPLATE_GROUP_ID
523 AND JTT.TASK_TYPE_ID = JTY.TASK_TYPE_ID
524 AND JTT.TASK_STATUS_ID = JTS.TASK_STATUS_ID
525 AND JTT.TASK_PRIORITY_ID = JTP.TASK_PRIORITY_ID;
526
527 BEGIN
528 -- Standard begin of API savepoint
529 SAVEPOINT Get_TaskTemplate_PVT;
530 -- Standard call to check for call compatibility.
531 IF NOT FND_API.Compatible_API_Call (
532 l_api_version,
533 p_api_version,
534 l_api_name,
535 G_PKG_NAME)
536 THEN
537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
538 END IF;
539 -- Debug Message
540 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
541 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
542 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
543 FND_MSG_PUB.Add;
544 END IF;
545 --Initialize message list if p_init_msg_list is TRUE.
546 IF FND_API.To_Boolean (p_init_msg_list) THEN
547 FND_MSG_PUB.initialize;
548 END IF;
549 -- Initialize API return status to sucess
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551
552 x_task_template := ozf_task_template_tbl_type();
553 OPEN c_tasks(p_reason_code_id);
554 LOOP
555 x_task_template.extend;
556 FETCH c_tasks INTO x_task_template(l_rec_count);
557 EXIT WHEN c_tasks%NOTFOUND;
558 l_rec_count := l_rec_count + 1;
559 END LOOP;
560 CLOSE c_tasks;
561
562 -- Debug Message
563 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
564 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
565 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
566 FND_MSG_PUB.Add;
567 END IF;
568 --Standard call to get message count and if count=1, get the message
569 FND_MSG_PUB.Count_And_Get (
570 p_encoded => FND_API.G_FALSE,
571 p_count => x_msg_count,
572 p_data => x_msg_data
573 );
574 EXCEPTION
575 WHEN FND_API.G_EXC_ERROR THEN
576 ROLLBACK TO Get_TaskTemplate_PVT;
577 x_return_status := FND_API.G_RET_STS_ERROR;
578 -- Standard call to get message count and if count=1, get the message
579 FND_MSG_PUB.Count_And_Get (
580 p_encoded => FND_API.G_FALSE,
581 p_count => x_msg_count,
582 p_data => x_msg_data
583 );
584 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
585 ROLLBACK TO Get_TaskTemplate_PVT;
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
587 -- Standard call to get message count and if count=1, get the message
588 FND_MSG_PUB.Count_And_Get (
589 p_encoded => FND_API.G_FALSE,
590 p_count => x_msg_count,
591 p_data => x_msg_data
592 );
593 WHEN OTHERS THEN
594 ROLLBACK TO Get_TaskTemplate_PVT;
595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
596 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
597 THEN
598 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
599 END IF;
600 -- Standard call to get message count and if count=1, get the message
601 FND_MSG_PUB.Count_And_Get (
602 p_encoded => FND_API.G_FALSE,
603 p_count => x_msg_count,
604 p_data => x_msg_data
605 );
606 --
607 END Get_TaskTemplate;
608 ---------------------------------------------------------------------
609 -- PROCEDURE
610 -- Validate_TaskTemplate
611 --
612 -- PURPOSE
613 -- Validate a reason code record.
614 --
615 -- PARAMETERS
616 -- p_validate_reason : the reason code record to be validated
617 --
618 -- NOTES
619 --
620 ----------------------------------------------------------------------
621 PROCEDURE Validate_TaskTemplate (
622 p_api_version IN NUMBER
623 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
624 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
625 ,x_return_status OUT NOCOPY VARCHAR2
626 ,x_msg_count OUT NOCOPY NUMBER
627 ,x_msg_data OUT NOCOPY VARCHAR2
628 ,p_task_template IN ozf_task_template_rec_type
629 )
630 IS
631 l_api_name CONSTANT VARCHAR2(30) := 'Validate_TaskTemplate';
632 l_api_version CONSTANT NUMBER := 1.0;
633 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| l_api_name;
634 --
635 l_resource_id number;
636 l_user_id number;
637 l_login_user_id number;
638 l_login_user_status varchar2(30);
639 l_Error_Msg varchar2(2000);
640 l_Error_Token varchar2(80);
641 l_object_version_number number := 1;
642 l_return_status VARCHAR2(1);
643 --
644 l_task_template ozf_task_template_rec_type := p_task_template;
645 BEGIN
646 -- Standard begin of API savepoint
647 SAVEPOINT Validate_TaskTemplate_PVT;
648 -- Standard call to check for call compatibility.
649 IF NOT FND_API.Compatible_API_Call (
650 l_api_version,
651 p_api_version,
652 l_api_name,
653 G_PKG_NAME)
654 THEN
655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
656 END IF;
657 -- Debug Message
658 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
659 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
660 FND_MESSAGE.Set_Token('TEXT',l_full_name||': Start');
661 FND_MSG_PUB.Add;
662 END IF;
663 --Initialize message list if p_init_msg_list is TRUE.
664 IF FND_API.To_Boolean (p_init_msg_list) THEN
665 FND_MSG_PUB.initialize;
666 END IF;
667 -- Initialize API return status to sucess
668 x_return_status := FND_API.G_RET_STS_SUCCESS;
669
670 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
671 Check_TaskTemplate_Items(
672 p_task_template_rec => p_task_template,
673 p_validation_mode => JTF_PLSQL_API.g_update,
674 x_return_status => l_return_status
675 );
676
677 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
678 RAISE FND_API.G_EXC_ERROR;
679 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
681 END IF;
682 END IF;
683
684 Complete_TaskTemplate_Rec(
685 p_task_template_rec => p_task_template,
686 x_complete_rec => l_task_template
687 );
688
689 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
690 Check_TaskTemplate_Record(
691 p_task_template_rec => p_task_template,
692 p_complete_rec => l_task_template,
693 x_return_status => l_return_status
694 );
695
696 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
697 RAISE FND_API.G_EXC_ERROR;
698 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
700 END IF;
701 END IF;
702
703 -- Debug Message
704 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
705 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
706 FND_MESSAGE.Set_Token('TEXT',l_full_name||': End');
707 FND_MSG_PUB.Add;
708 END IF;
709 --Standard call to get message count and if count=1, get the message
710 FND_MSG_PUB.Count_And_Get (
711 p_encoded => FND_API.G_FALSE,
712 p_count => x_msg_count,
713 p_data => x_msg_data
714 );
715 EXCEPTION
716 WHEN FND_API.G_EXC_ERROR THEN
717 ROLLBACK TO Validate_TaskTemplate_PVT;
718 x_return_status := FND_API.G_RET_STS_ERROR;
719 -- Standard call to get message count and if count=1, get the message
720 FND_MSG_PUB.Count_And_Get (
721 p_encoded => FND_API.G_FALSE,
722 p_count => x_msg_count,
723 p_data => x_msg_data
724 );
725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
726 ROLLBACK TO Validate_TaskTemplate_PVT;
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
728 -- Standard call to get message count and if count=1, get the message
729 FND_MSG_PUB.Count_And_Get (
730 p_encoded => FND_API.G_FALSE,
731 p_count => x_msg_count,
732 p_data => x_msg_data
733 );
734 WHEN OTHERS THEN
735 ROLLBACK TO Validate_TaskTemplate_PVT;
736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
737 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
738 THEN
739 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
740 END IF;
741 -- Standard call to get message count and if count=1, get the message
742 FND_MSG_PUB.Count_And_Get (
743 p_encoded => FND_API.G_FALSE,
744 p_count => x_msg_count,
745 p_data => x_msg_data
746 );
747 --
748 END Validate_TaskTemplate;
749 ---------------------------------------------------------------------
750 -- PROCEDURE
751 -- Check_TaskTemplate_Items
752 --
753 -- PURPOSE
754 -- Perform the item level checking including unique keys,
755 -- required columns, foreign keys, , flag items, domain constraints.
756 --
757 -- PARAMETERS
758 -- p_task_template_rec : the record to be validated
759 ---------------------------------------------------------------------
760 PROCEDURE Check_TaskTemplate_Items(
761 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
762 ,x_return_status OUT NOCOPY VARCHAR2
763 ,p_task_template_rec IN ozf_task_template_rec_type
764 )
765 IS
766 BEGIN
767 x_return_status := FND_API.G_RET_STS_SUCCESS;
768 END;
769 ---------------------------------------------------------------------
770 -- PROCEDURE
771 -- Check_TaskTemplate_Record
772 --
773 -- PURPOSE
774 -- Check the task template level business rules.
775 --
776 -- PARAMETERS
777 -- p_task_template_rec : the record to be validated; may contain attributes
778 -- as FND_API.g_miss_char/num/date
779 -- p_complete_rec: the complete record after all "g_miss" items have
780 -- been replaced by current database values
781 ---------------------------------------------------------------------
782 PROCEDURE Check_TaskTemplate_Record(
783 p_task_template_rec IN ozf_task_template_rec_type
784 ,p_complete_rec IN ozf_task_template_rec_type := NULL
785 ,x_return_status OUT NOCOPY VARCHAR2
786 )
787 IS
788 BEGIN
789 x_return_status := FND_API.G_RET_STS_SUCCESS;
790 END;
791 ---------------------------------------------------------------------
792 -- PROCEDURE
793 -- Init_TaskTemplate_Rec
794 --
795 -- PURPOSE
796 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
797 ---------------------------------------------------------------------
798 PROCEDURE Init_Reason_Rec (
799 x_task_template_rec OUT NOCOPY ozf_task_template_rec_type
800 )
801 IS
802 BEGIN
803 --
804 RETURN;
805 END;
806 ---------------------------------------------------------------------
807 -- PROCEDURE
808 -- Complete_TaskTemplate_Rec
809 --
810 -- PURPOSE
811 -- For Update_Reason, some attributes may be passed in as
812 -- FND_API.g_miss_char/num/date if the user doesn't want to
813 -- update those attributes. This procedure will replace the
814 -- "g_miss" attributes with current database values.
815 --
816 -- PARAMETERS
817 -- p_task_template_rec : the record which may contain attributes as
818 -- FND_API.g_miss_char/num/date
819 -- x_complete_rec: the complete record after all "g_miss" items
820 -- have been replaced by current database values
821 ---------------------------------------------------------------------
822 PROCEDURE Complete_TaskTemplate_Rec (
823 p_task_template_rec IN ozf_task_template_rec_type
824 ,x_complete_rec OUT NOCOPY ozf_task_template_rec_type
825 )
826 IS
827
828 l_task_template_rec ozf_task_template_rec_type;
829
830 CURSOR c_task_template (cv_task_template_id NUMBER) IS
831 SELECT JTT.TASK_TEMPLATE_ID
832 , JTT.TASK_NAME
833 , JTT.DESCRIPTION
834 , JTT.TASK_GROUP_ID
835 , JTG.TEMPLATE_GROUP_NAME
836 , JTT.TASK_NUMBER
837 , JTT.TASK_TYPE_ID
838 , JTY.NAME
839 , JTT.TASK_STATUS_ID
840 , JTS.NAME
841 , JTT.TASK_PRIORITY_ID
842 , JTP.NAME
843 , JTT.DURATION
844 , JTT.DURATION_UOM
845 , JTT.OBJECT_VERSION_NUMBER
846 , JTT.ATTRIBUTE_CATEGORY
847 , JTT.ATTRIBUTE1
848 , JTT.ATTRIBUTE2
849 , JTT.ATTRIBUTE3
850 , JTT.ATTRIBUTE4
851 , JTT.ATTRIBUTE5
852 , JTT.ATTRIBUTE6
853 , JTT.ATTRIBUTE7
854 , JTT.ATTRIBUTE8
855 , JTT.ATTRIBUTE9
856 , JTT.ATTRIBUTE10
857 , JTT.ATTRIBUTE11
858 , JTT.ATTRIBUTE12
859 , JTT.ATTRIBUTE13
860 , JTT.ATTRIBUTE14
861 , JTT.ATTRIBUTE15
862 FROM JTF_TASK_TEMPLATES_VL JTT
863 , JTF_TASK_TEMP_GROUPS_VL JTG
864 , JTF_TASK_TYPES_VL JTY
865 , JTF_TASK_STATUSES_VL JTS
866 , JTF_TASK_PRIORITIES_VL JTP
867 WHERE JTT.TASK_TEMPLATE_ID = cv_task_template_id
868 AND JTT.TASK_GROUP_ID = JTG.TASK_TEMPLATE_GROUP_ID
869 AND JTT.TASK_TYPE_ID = JTY.TASK_TYPE_ID
870 AND JTT.TASK_STATUS_ID = JTS.TASK_STATUS_ID
871 AND JTT.TASK_PRIORITY_ID = JTP.TASK_PRIORITY_ID;
872
873 BEGIN
874
875 x_complete_rec := p_task_template_rec;
876
877 OPEN c_task_template(p_task_template_rec.task_template_id);
878 FETCH c_task_template INTO l_task_template_rec;
879 IF c_task_template%NOTFOUND THEN
880 CLOSE c_task_template;
881 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
882 FND_MESSAGE.set_name('OZF','OZF_API_RECORD_NOT_FOUND');
883 FND_MSG_PUB.add;
884 END IF;
885 RAISE FND_API.g_exc_error;
886 END IF;
887 CLOSE c_task_template;
888
889 IF p_task_template_rec.task_name = FND_API.G_MISS_CHAR THEN
890 x_complete_rec.task_name := NULL;
891 END IF;
892 IF p_task_template_rec.task_name IS NULL THEN
893 x_complete_rec.task_name := l_task_template_rec.task_name;
894 END IF;
895 IF p_task_template_rec.description = FND_API.G_MISS_CHAR THEN
896 x_complete_rec.description := NULL;
897 END IF;
898 IF p_task_template_rec.description IS NULL THEN
899 x_complete_rec.description := l_task_template_rec.description;
900 END IF;
901 IF p_task_template_rec.reason_code_id = FND_API.G_MISS_NUM THEN
902 x_complete_rec.reason_code_id := NULL;
903 END IF;
904 IF p_task_template_rec.reason_code_id IS NULL THEN
905 x_complete_rec.reason_code_id := l_task_template_rec.reason_code_id;
906 END IF;
907 IF p_task_template_rec.reason_code = FND_API.G_MISS_CHAR THEN
908 x_complete_rec.reason_code := NULL;
909 END IF;
910 IF p_task_template_rec.reason_code IS NULL THEN
911 x_complete_rec.reason_code := l_task_template_rec.reason_code;
912 END IF;
913 IF p_task_template_rec.task_number = FND_API.G_MISS_CHAR THEN
914 x_complete_rec.task_number := NULL;
915 END IF;
916 IF p_task_template_rec.task_number IS NULL THEN
917 x_complete_rec.task_number := l_task_template_rec.task_number;
918 END IF;
919 IF p_task_template_rec.task_type_id = FND_API.G_MISS_NUM THEN
920 x_complete_rec.task_type_id := NULL;
921 END IF;
922 IF p_task_template_rec.task_type_id IS NULL THEN
923 x_complete_rec.task_type_id := l_task_template_rec.task_type_id;
924 END IF;
925 IF p_task_template_rec.task_type_name = FND_API.G_MISS_CHAR THEN
926 x_complete_rec.task_type_name := NULL;
927 END IF;
928 IF p_task_template_rec.task_type_name IS NULL THEN
929 x_complete_rec.task_type_name := l_task_template_rec.task_type_name;
930 END IF;
931 IF p_task_template_rec.task_status_id = FND_API.G_MISS_NUM THEN
932 x_complete_rec.task_status_id := NULL;
933 END IF;
934 IF p_task_template_rec.task_status_id IS NULL THEN
935 x_complete_rec.task_status_id := l_task_template_rec.task_status_id;
936 END IF;
937 IF p_task_template_rec.task_status_name = FND_API.G_MISS_CHAR THEN
938 x_complete_rec.task_status_name := NULL;
939 END IF;
940 IF p_task_template_rec.task_status_name IS NULL THEN
941 x_complete_rec.task_status_name := l_task_template_rec.task_status_name;
942 END IF;
943 IF p_task_template_rec.task_priority_id = FND_API.G_MISS_NUM THEN
944 x_complete_rec.task_priority_id := NULL;
945 END IF;
946 IF p_task_template_rec.task_priority_id IS NULL THEN
947 x_complete_rec.task_priority_id := l_task_template_rec.task_priority_id;
948 END IF;
949 IF p_task_template_rec.task_priority_name = FND_API.G_MISS_CHAR THEN
950 x_complete_rec.task_priority_name := NULL;
951 END IF;
952 IF p_task_template_rec.task_priority_name IS NULL THEN
953 x_complete_rec.task_priority_name := l_task_template_rec.task_priority_name;
954 END IF;
955 IF p_task_template_rec.object_version_number = FND_API.G_MISS_NUM THEN
956 x_complete_rec.object_version_number := NULL;
957 END IF;
958 IF p_task_template_rec.object_version_number IS NULL THEN
959 x_complete_rec.object_version_number := l_task_template_rec.object_version_number;
960 END IF;
961 IF p_task_template_rec.attribute_category = FND_API.G_MISS_CHAR THEN
962 x_complete_rec.attribute_category := NULL;
963 END IF;
964 IF p_task_template_rec.attribute_category IS NULL THEN
965 x_complete_rec.attribute_category := l_task_template_rec.attribute_category;
966 END IF;
967 IF p_task_template_rec.attribute1 = FND_API.G_MISS_CHAR THEN
968 x_complete_rec.attribute1 := NULL;
969 END IF;
970 IF p_task_template_rec.attribute1 IS NULL THEN
971 x_complete_rec.attribute1 := l_task_template_rec.attribute1;
972 END IF;
973 IF p_task_template_rec.attribute2 = FND_API.G_MISS_CHAR THEN
974 x_complete_rec.attribute2 := NULL;
975 END IF;
976 IF p_task_template_rec.attribute2 IS NULL THEN
977 x_complete_rec.attribute2 := l_task_template_rec.attribute2;
978 END IF;
979 IF p_task_template_rec.attribute3 = FND_API.G_MISS_CHAR THEN
980 x_complete_rec.attribute3 := NULL;
981 END IF;
982 IF p_task_template_rec.attribute3 IS NULL THEN
983 x_complete_rec.attribute3 := l_task_template_rec.attribute3;
984 END IF;
985 IF p_task_template_rec.attribute4 = FND_API.G_MISS_CHAR THEN
986 x_complete_rec.attribute4 := NULL;
987 END IF;
988 IF p_task_template_rec.attribute4 IS NULL THEN
989 x_complete_rec.attribute4 := l_task_template_rec.attribute4;
990 END IF;
991 IF p_task_template_rec.attribute5 = FND_API.G_MISS_CHAR THEN
992 x_complete_rec.attribute5 := NULL;
993 END IF;
994 IF p_task_template_rec.attribute5 IS NULL THEN
995 x_complete_rec.attribute5 := l_task_template_rec.attribute5;
996 END IF;
997 IF p_task_template_rec.attribute6 = FND_API.G_MISS_CHAR THEN
998 x_complete_rec.attribute6 := NULL;
999 END IF;
1000 IF p_task_template_rec.attribute6 IS NULL THEN
1001 x_complete_rec.attribute6 := l_task_template_rec.attribute6;
1002 END IF;
1003 IF p_task_template_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1004 x_complete_rec.attribute7 := NULL;
1005 END IF;
1006 IF p_task_template_rec.attribute7 IS NULL THEN
1007 x_complete_rec.attribute7 := l_task_template_rec.attribute7;
1008 END IF;
1009 IF p_task_template_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1010 x_complete_rec.attribute8 := NULL;
1011 END IF;
1012 IF p_task_template_rec.attribute8 IS NULL THEN
1013 x_complete_rec.attribute8 := l_task_template_rec.attribute8;
1014 END IF;
1015 IF p_task_template_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1016 x_complete_rec.attribute9 := NULL;
1017 END IF;
1018 IF p_task_template_rec.attribute9 IS NULL THEN
1019 x_complete_rec.attribute9 := l_task_template_rec.attribute9;
1020 END IF;
1021 IF p_task_template_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1022 x_complete_rec.attribute10 := NULL;
1023 END IF;
1024 IF p_task_template_rec.attribute10 IS NULL THEN
1025 x_complete_rec.attribute10 := l_task_template_rec.attribute10;
1026 END IF;
1027 IF p_task_template_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1028 x_complete_rec.attribute11 := NULL;
1029 END IF;
1030 IF p_task_template_rec.attribute11 IS NULL THEN
1031 x_complete_rec.attribute11 := l_task_template_rec.attribute11;
1032 END IF;
1033 IF p_task_template_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1034 x_complete_rec.attribute12 := NULL;
1035 END IF;
1036 IF p_task_template_rec.attribute12 IS NULL THEN
1037 x_complete_rec.attribute12 := l_task_template_rec.attribute12;
1038 END IF;
1039 IF p_task_template_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1040 x_complete_rec.attribute13 := NULL;
1041 END IF;
1042 IF p_task_template_rec.attribute13 IS NULL THEN
1043 x_complete_rec.attribute13 := l_task_template_rec.attribute13;
1044 END IF;
1045 IF p_task_template_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1046 x_complete_rec.attribute14 := NULL;
1047 END IF;
1048 IF p_task_template_rec.attribute14 IS NULL THEN
1049 x_complete_rec.attribute14 := l_task_template_rec.attribute14;
1050 END IF;
1051 IF p_task_template_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1052 x_complete_rec.attribute15 := NULL;
1053 END IF;
1054 IF p_task_template_rec.attribute15 IS NULL THEN
1055 x_complete_rec.attribute15 := l_task_template_rec.attribute15;
1056 END IF;
1057 --
1058 END Complete_TaskTemplate_Rec;
1059
1060
1061 END OZF_TASK_TEMPLATE_PVT;
1062