[Home] [Help]
PACKAGE BODY: APPS.PA_TEAM_TEMPLATES_PUB
Source
1 PACKAGE BODY pa_team_templates_pub AS
2 /*$Header: PARTPUBB.pls 120.2 2005/08/23 04:31:40 sunkalya noship $*/
3 --
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5 PROCEDURE Execute_Apply_Team_Template
6 (p_team_template_id IN pa_team_templates.team_template_id%TYPE
7 ,p_project_id IN pa_projects_all.project_id%TYPE
8 ,p_project_start_date IN pa_projects_all.start_date%TYPE
9 ,p_team_start_date IN pa_team_templates.team_start_date%TYPE := FND_API.G_MISS_DATE
10 ,p_use_project_location IN VARCHAR2 := 'N'
11 ,p_project_location_id IN pa_projects_all.location_id%TYPE := NULL
12 ,p_use_project_calendar IN VARCHAR2 := 'N'
13 ,p_project_calendar_id IN pa_projects_all.calendar_id%TYPE := NULL
14 ,p_apply IN VARCHAR2 := 'Y'
15 ,p_api_version IN NUMBER := 1.0
16 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
17 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
18 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
19 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
20 ,x_msg_count OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
22 )
23 IS
24
25 l_count NUMBER;
26
27 BEGIN
28
29 -- Initialize the Error Stack
30 PA_DEBUG.init_err_stack('PA_TEAM_TEMPLATES_PUB.Execute_Apply_Team_Template');
31
32 -- Initialize the return status to success
33 x_return_status := FND_API.G_RET_STS_SUCCESS;
34
35 --Clear the global PL/SQL message table
36 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
37 FND_MSG_PUB.initialize;
38 END IF;
39
40 -- Issue API savepoint if the transaction is to be committed
41 IF p_commit = FND_API.G_TRUE THEN
42 SAVEPOINT ASG_PUB_EXEC_APPLY_TEAM_TEMP;
43 END IF;
44
45 --Log Message
46 IF (P_DEBUG_MODE = 'Y') THEN
47 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PUB.execute_apply_team_template'
48 ,x_msg => 'Beginning of Execute_Apply_Team_Template'
49 ,x_log_level => 5);
50 END IF;
51
52 --assign team template ids to be applied to the global pl/sql table.
53 l_count := g_team_template_id_tbl.COUNT;
54
55 g_team_template_id_tbl(l_count+1).team_template_id := p_team_template_id;
56
57 --If p_apply = Y then all of the team_template_ids to be copied have been
58 --loaded into the pl/sql table, so call Apply_Team_Template
59
60 IF p_apply = 'Y' THEN
61
62 --Log Message
63 IF (P_DEBUG_MODE = 'Y') THEN
64 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PUB.execute_apply_team_template'
65 ,x_msg => 'Calling Apply_Team_Template'
66 ,x_log_level => 5);
67 END IF;
68
69 PA_TEAM_TEMPLATES_PUB.Apply_Team_Template(
70 p_team_template_id_tbl => g_team_template_id_tbl
71 ,p_project_id => p_project_id
72 ,p_project_start_date => p_project_start_date
73 ,p_team_start_date => p_team_start_date
74 ,p_use_project_location => p_use_project_location
75 ,p_project_location_id => p_project_location_id
76 ,p_use_project_calendar => p_use_project_calendar
77 ,p_project_calendar_id => p_project_calendar_id
78 ,x_return_status => x_return_status
79 ,x_msg_count => x_msg_count
80 ,x_msg_data => x_msg_data);
81
82 --clear global pl/sql table
83 g_team_template_id_tbl.DELETE;
84
85 END IF; --p_apply='Y'
86
87 -- Reset the error stack when returning to the calling program
88 PA_DEBUG.Reset_Err_Stack;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92
93 --clear global pl/sql table
94 g_team_template_id_tbl.DELETE;
95
96 IF p_commit = FND_API.G_TRUE THEN
97 ROLLBACK TO ASG_PUB_EXEC_APPLY_TEAM_TEMP;
98 END IF;
99
100 -- Set the excetption Message and the stack
101 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATES_PUB.Execute_Apply_Team_Template'
102 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
103 --
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
105 RAISE;
106
107 END Execute_Apply_Team_Template;
108
109 PROCEDURE Apply_Team_Template
110 (p_team_template_id_tbl IN team_template_id_tbl
111 ,p_project_id IN pa_projects_all.project_id%TYPE
112 ,p_project_start_date IN pa_projects_all.start_date%TYPE
113 ,p_team_start_date IN pa_team_templates.team_start_date%TYPE := FND_API.G_MISS_DATE
114 ,p_use_project_location IN VARCHAR2 := 'N'
115 ,p_project_location_id IN pa_projects_all.location_id%TYPE := NULL
116 ,p_use_project_calendar IN VARCHAR2 := 'N'
117 ,p_project_calendar_id IN pa_projects_all.calendar_id%TYPE := NULL
118 ,p_api_version IN NUMBER := 1.0
119 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
120 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
121 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
122 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
123 ,x_msg_count OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
124 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
125 )
126 IS
127
128 l_msg_index_out NUMBER;
129
130 l_project_calendar_id pa_projects_all.calendar_id%TYPE;
131 l_project_location_id pa_projects_all.location_id%TYPE;
132 l_unassigned_time_proj VARCHAR2(1);
133 l_admin_proj VARCHAR2(1);
134
135 -- cursor to get location and calendar for validation
136 CURSOR get_project_location_and_cal IS
137 SELECT calendar_id, location_id
138 FROM pa_projects_all
139 WHERE project_id = p_project_id;
140
141 BEGIN
142
143 -- Initialize the Error Stack
144 PA_DEBUG.init_err_stack('PA_TEAM_TEMPLATES_PUB.Apply_Team_Template');
145
146 -- Initialize the return status to success
147 x_return_status := FND_API.G_RET_STS_SUCCESS;
148
149 --Clear the global PL/SQL message table
150 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
151 FND_MSG_PUB.initialize;
152 END IF;
153
154 -- Issue API savepoint if the transaction is to be committed
155 IF p_commit = FND_API.G_TRUE THEN
156 SAVEPOINT ASG_PUB_APPLY_TEAM_TEMPLATE;
157 END IF;
158
159 --clear globals used to put the team template name / role name in error messages.
160 pa_assignment_utils.g_team_template_id := NULL;
161 pa_assignment_utils.g_team_template_name_token := NULL;
162 pa_assignment_utils.g_team_role_name_token := NULL;
163
164 --Log Message
165 IF (P_DEBUG_MODE = 'Y') THEN
166 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PUB.apply_team_template'
167 ,x_msg => 'Beginning of Apply_Team_Template'
168 ,x_log_level => 5);
169 END IF;
170
171 --call private API to apply the team template
172 IF (P_DEBUG_MODE = 'Y') THEN
173 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PUB.apply_team_template'
174 ,x_msg => 'calling PA_TEAM_TEMPLATES_PVT.Apply_Team_Template'
175 ,x_log_level => 5);
176 END IF;
177
178 --validate that the project is not an unassigned time project.
179 --assignments are not allowed on unassigned time projects
180 l_unassigned_time_proj := PA_PROJECT_UTILS.is_unassigned_time_project(p_project_id);
181 IF l_unassigned_time_proj = 'Y' THEN
182 PA_UTILS.Add_Message( p_app_short_name => 'PA'
183 ,p_msg_name => 'PA_NO_TEMPLATE_UNASGN_TIME_PRJ');
184 END IF;
185
186 --validate that the project is not an admin project.
187 --only admin assigments are allowed on admin projects
188 l_admin_proj := PA_PROJECT_UTILS.Is_Admin_Project(p_project_id);
189 IF l_admin_proj = 'Y' THEN
190 PA_UTILS.Add_Message( p_app_short_name => 'PA'
191 ,p_msg_name => 'PA_NO_TEMPLATE_ADMIN_PRJ');
192 END IF;
193
194 -- validate project location id and calendar id
195 l_project_calendar_id := p_project_calendar_id;
196 l_project_location_id := p_project_location_id;
197
198 --if p_use_project_location = Y or p_use_project_calendar='Y' and the values are not
199 --passed in to the API then get the project calendar/location.
200 IF (p_use_project_location ='Y' AND p_project_location_id IS NULL) OR
201 (p_use_project_calendar='Y' AND p_project_calendar_id IS NULL) THEN
202
203 OPEN get_project_location_and_cal;
204 FETCH get_project_location_and_cal INTO l_project_calendar_id, l_project_location_id;
205 CLOSE get_project_location_and_cal;
206
207 END IF;
208
209 --validate that a project calendar is defined if p_use_project_calendar='Y'
210 IF p_use_project_calendar ='Y' AND l_project_calendar_id IS NULL THEN
211
212 PA_UTILS.Add_Message( p_app_short_name => 'PA'
213 ,p_msg_name => 'PA_PROJ_CAL_NOT_DEFINED');
214
215 END IF;
216
217 --validate that a project location is defined if p_use_project_location='Y'
218 IF p_use_project_location ='Y' AND l_project_location_id IS NULL THEN
219
220 PA_UTILS.Add_Message( p_app_short_name => 'PA'
221 ,p_msg_name => 'PA_PROJ_LOC_NOT_DEFINED');
222
223 END IF;
224
225 x_msg_count := FND_MSG_PUB.Count_Msg;
226
227 IF x_msg_count = 0 THEN
228
229 PA_TEAM_TEMPLATES_PVT.Start_Apply_Team_Template_WF(p_team_template_id_tbl => p_team_template_id_tbl
230 ,p_project_id => p_project_id
231 ,p_project_start_date => p_project_start_date
232 ,p_team_start_date => p_team_start_date
233 ,p_use_project_location => p_use_project_location
234 ,p_project_location_id => p_project_location_id
235 ,p_use_project_calendar => p_use_project_calendar
236 ,p_project_calendar_id => p_project_calendar_id
237 ,x_return_status => x_return_status);
238
239 END IF;
240
241 x_msg_count := FND_MSG_PUB.Count_Msg;
242
243 IF x_msg_count = 1 THEN
244 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
245 ,p_msg_index => 1
246 ,p_data => x_msg_data
247 ,p_msg_index_out => l_msg_index_out
248 );
249 END IF;
250
251 -- Reset the error stack when returning to the calling program
252
253 PA_DEBUG.Reset_Err_Stack;
254
255 -- If errors exist then set the x_return_status to 'E'
256
257 IF x_msg_count >0 THEN
258
259 x_return_status := FND_API.G_RET_STS_ERROR;
260
261 END IF;
262
263 -- Commit if the flag is set and there is no error
264 IF p_commit = FND_API.G_TRUE AND x_msg_count = 0 THEN
265 COMMIT;
266 END IF;
267
268 --clear globals used to put the team template name / role name in error messages.
269 pa_assignment_utils.g_team_template_id := NULL;
270 pa_assignment_utils.g_team_template_name_token := NULL;
271 pa_assignment_utils.g_team_role_name_token := NULL;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275
276 --clear globals used to put the team template name / role name in error messages.
277 pa_assignment_utils.g_team_template_id := NULL;
278 pa_assignment_utils.g_team_template_name_token := NULL;
279 pa_assignment_utils.g_team_role_name_token := NULL;
280
281 IF p_commit = FND_API.G_TRUE THEN
282 ROLLBACK TO ASG_PUB_APPLY_TEAM_TEMPLATE;
283 END IF;
284
285 -- Set the excetption Message and the stack
286 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATES_PUB.Apply_Team_Template'
287 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
288 --
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
290 RAISE;
291
292 END Apply_Team_Template;
293
294
295 PROCEDURE Execute_Create_Team_Template
296 (p_team_template_name IN pa_team_templates.team_template_name%TYPE
297 ,p_description IN pa_team_templates.description%TYPE := FND_API.G_MISS_CHAR
298 ,p_start_date_active IN pa_team_templates.start_date_active%TYPE
299 ,p_end_date_active IN pa_team_templates.end_date_active%TYPE := FND_API.G_MISS_DATE
300 ,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
301 ,p_calendar_id IN pa_team_templates.calendar_id%TYPE := FND_API.G_MISS_NUM
302 ,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
303 ,p_work_type_id IN pa_team_templates.work_type_id%TYPE := FND_API.G_MISS_NUM
304 ,p_role_list_name IN pa_role_lists.name%TYPE := FND_API.G_MISS_CHAR
305 ,p_role_list_id IN pa_team_templates.role_list_id%TYPE := FND_API.G_MISS_NUM
306 ,p_team_start_date IN pa_team_templates.team_start_date%TYPE
307 ,p_attribute_category IN pa_team_templates.attribute_category%TYPE := FND_API.G_MISS_CHAR
308 ,p_attribute1 IN pa_team_templates.attribute1%TYPE := FND_API.G_MISS_CHAR
309 ,p_attribute2 IN pa_team_templates.attribute2%TYPE := FND_API.G_MISS_CHAR
310 ,p_attribute3 IN pa_team_templates.attribute3%TYPE := FND_API.G_MISS_CHAR
311 ,p_attribute4 IN pa_team_templates.attribute4%TYPE := FND_API.G_MISS_CHAR
312 ,p_attribute5 IN pa_team_templates.attribute5%TYPE := FND_API.G_MISS_CHAR
313 ,p_attribute6 IN pa_team_templates.attribute6%TYPE := FND_API.G_MISS_CHAR
314 ,p_attribute7 IN pa_team_templates.attribute7%TYPE := FND_API.G_MISS_CHAR
315 ,p_attribute8 IN pa_team_templates.attribute8%TYPE := FND_API.G_MISS_CHAR
316 ,p_attribute9 IN pa_team_templates.attribute9%TYPE := FND_API.G_MISS_CHAR
317 ,p_attribute10 IN pa_team_templates.attribute10%TYPE := FND_API.G_MISS_CHAR
318 ,p_attribute11 IN pa_team_templates.attribute11%TYPE := FND_API.G_MISS_CHAR
319 ,p_attribute12 IN pa_team_templates.attribute12%TYPE := FND_API.G_MISS_CHAR
320 ,p_attribute13 IN pa_team_templates.attribute13%TYPE := FND_API.G_MISS_CHAR
321 ,p_attribute14 IN pa_team_templates.attribute14%TYPE := FND_API.G_MISS_CHAR
322 ,p_attribute15 IN pa_team_templates.attribute15%TYPE := FND_API.G_MISS_CHAR
323 ,p_api_version IN NUMBER := 1.0
324 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
325 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
326 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
327 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
328 ,x_team_template_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
329 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
330 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
331 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
332 )
333 IS
334
335 l_team_template_rec team_template_rec;
336
337 BEGIN
338
339 -- Initialize the Error Stack
340 PA_DEBUG.init_err_stack('PA_TEAM_TEMPLATES_PUB.Execute_Create_Team_Template');
341
342 -- Initialize the return status to success
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344
345 --Clear the global PL/SQL message table
346 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
347 FND_MSG_PUB.initialize;
348 END IF;
349
350 -- Issue API savepoint if the transaction is to be committed
351 IF p_commit = FND_API.G_TRUE THEN
352 SAVEPOINT ASG_PUB_EXEC_CREATE_TEAM_TEMP;
353 END IF;
354
355 --Log Message
356 IF (P_DEBUG_MODE = 'Y') THEN
357 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PUB.execute_create_team_template.begin'
358 ,x_msg => 'Beginning of Execute_Create_Team_Template'
359 ,x_log_level => 5);
360 END IF;
361
362
363 --Assign scalar parameters to the team template record.
364
365 l_team_template_rec.team_template_name := p_team_template_name;
366 l_team_template_rec.description := p_description;
367 l_team_template_rec.start_date_active := p_start_date_active;
368 l_team_template_rec.end_date_active := p_end_date_active;
369 l_team_template_rec.calendar_id := p_calendar_id;
370 l_team_template_rec.work_type_id := p_work_type_id;
371 l_team_template_rec.role_list_id := p_role_list_id;
372 l_team_template_rec.team_start_date := p_team_start_date;
373 l_team_template_rec.attribute_category := p_attribute_category;
374 l_team_template_rec.attribute1 := p_attribute1;
375 l_team_template_rec.attribute2 := p_attribute2;
376 l_team_template_rec.attribute3 := p_attribute3;
377 l_team_template_rec.attribute4 := p_attribute4;
378 l_team_template_rec.attribute5 := p_attribute5;
379 l_team_template_rec.attribute6 := p_attribute6;
380 l_team_template_rec.attribute7 := p_attribute7;
381 l_team_template_rec.attribute8 := p_attribute8;
382 l_team_template_rec.attribute9 := p_attribute9;
383 l_team_template_rec.attribute10 := p_attribute10;
384 l_team_template_rec.attribute11 := p_attribute11;
385 l_team_template_rec.attribute12 := p_attribute12;
386 l_team_template_rec.attribute13 := p_attribute13;
387 l_team_template_rec.attribute14 := p_attribute14;
388 l_team_template_rec.attribute15 := p_attribute15;
389
390 --Log Message
391 IF (P_DEBUG_MODE = 'Y') THEN
392 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PUB.execute_create_team_template.begin'
393 ,x_msg => 'Calling Create_Team_Template'
394 ,x_log_level => 5);
395 END IF;
396
397
398 PA_TEAM_TEMPLATES_PUB.Create_Team_Template
399 (p_team_template_rec => l_team_template_rec
400 ,p_calendar_name => p_calendar_name
401 ,p_work_type_name => p_work_type_name
402 ,p_role_list_name => p_role_list_name
403 ,p_api_version => p_api_version
404 ,p_init_msg_list => p_init_msg_list
405 ,p_commit => p_commit
406 ,p_max_msg_count => p_max_msg_count
407 ,x_team_template_id => x_team_template_id
408 ,x_return_status => x_return_status
409 ,x_msg_count => x_msg_count
410 ,x_msg_data => x_msg_data);
411
412
413 -- Reset the error stack when returning to the calling program
414 PA_DEBUG.Reset_Err_Stack;
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 IF p_commit = FND_API.G_TRUE THEN
419 ROLLBACK TO ASG_PUB_EXEC_CREATE_TEAM_TEMP;
420 END IF;
421
422 -- Set the excetption Message and the stack
423 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATES_PUB.Execute_Create_Team_Template'
424 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
425 --
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
427 RAISE;
428
429 END Execute_Create_Team_Template;
430
431
432 PROCEDURE Create_Team_Template
433 ( p_team_template_rec IN team_template_rec
434 ,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
435 ,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
436 ,p_role_list_name IN pa_role_lists.name%TYPE := FND_API.G_MISS_CHAR
437 ,p_api_version IN NUMBER := 1.0
438 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
439 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
440 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
441 ,p_validate_only IN VARCHAR2 := FND_API.G_FAlSE
442 ,x_team_template_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
443 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
444 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
445 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
446 IS
447
448 l_team_template_rec team_template_rec;
449 -- added for bug: 4537865
450 l_new_calendar_id pa_team_templates.calendar_id%TYPE;
451 l_new_work_type_id pa_team_templates.work_type_id%TYPE;
452 l_new_role_list_id pa_team_templates.role_list_id%TYPE;
453 -- added for bug: 4537865
454 l_return_status VARCHAR2(1);
455 l_error_message_code fnd_new_messages.message_name%TYPE;
456 l_msg_index_out NUMBER;
457
458 BEGIN
459
460 -- Initialize the Error Stack
461 PA_DEBUG.init_err_stack('PA_TEAM_TEMPLATES_PUB.Create_Team_Template');
462
463 -- Initialize the return status to success
464 x_return_status := FND_API.G_RET_STS_SUCCESS;
465
466 --Issue API savepoint if the transaction is to be committed
467 IF p_commit = FND_API.G_TRUE THEN
468 SAVEPOINT ASG_PUB_CREATE_TEAM_TEMPLATE;
469 END IF;
470
471 --Clear the global PL/SQL message table
472 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
473 FND_MSG_PUB.initialize;
474 END IF;
475
476 --Log Message
477 IF (P_DEBUG_MODE = 'Y') THEN
478 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PUB.Create_Team_Template.begin'
479 ,x_msg => 'Beginning of Create_Team_Template'
480 ,x_log_level => 5);
481 END IF;
482
483 -- Assign the record to the local variable
484 l_team_template_rec := p_team_template_rec;
485
486 --validate calendar
487 IF (l_team_template_rec.calendar_id IS NOT NULL AND l_team_template_rec.calendar_id <> FND_API.G_MISS_NUM) OR (p_calendar_name IS NOT NULL and p_calendar_name <> FND_API.G_MISS_CHAR) THEN
488
489 PA_CALENDAR_UTILS.Check_Calendar_Name_Or_Id( p_calendar_id => l_team_template_rec.calendar_id
490 ,p_calendar_name => p_calendar_name
491 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
492 --,x_calendar_id => l_team_template_rec.calendar_id * Bug: 4537865
493 ,x_calendar_id => l_new_calendar_id -- Bug: 4537865
494 ,x_return_status => l_return_status
495 ,x_error_message_code => l_error_message_code );
496 -- added for Bug fix: 4537865
497 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
498 l_team_template_rec.calendar_id := l_new_calendar_id;
499 END IF;
500 -- added for Bug fix: 4537865
501 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
502 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
503 ,p_msg_name => l_error_message_code );
504 END IF;
505
506 END IF; -- validate calendar
507
508
509
510 --validate work type
511 IF (l_team_template_rec.work_type_id IS NOT NULL AND l_team_template_rec.work_type_id <> FND_API.G_MISS_NUM) OR (p_work_type_name IS NOT NULL AND p_work_type_name <> FND_API.G_MISS_CHAR) THEN
512
513 PA_WORK_TYPE_UTILS.Check_Work_Type_Name_Or_Id( p_work_type_id => l_team_template_rec.work_type_id
514 ,p_name => p_work_type_name
515 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
516 --,x_work_type_id => l_team_template_rec.work_type_id * Bug: 4537865
517 ,x_work_type_id => l_new_work_type_id -- Bug: 4537865
518 ,x_return_status => l_return_status
519 ,x_error_message_code => l_error_message_code );
520 -- added for Bug: 4537865
521 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
522 l_team_template_rec.work_type_id := l_new_work_type_id;
523 END IF;
524 -- added for Bug: 4537865
525
526 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
527 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
528 ,p_msg_name => l_error_message_code );
529 END IF;
530
531 END IF; --validate work type
532
533 --validate role list
534 IF (l_team_template_rec.role_list_id IS NOT NULL AND l_team_template_rec.role_list_id <> FND_API.G_MISS_NUM) OR (p_role_list_name IS NOT NULL AND p_role_list_name <> FND_API.G_MISS_CHAR) THEN
535
536 PA_ROLE_LIST_UTILS.Check_Role_List_Name_Or_Id( p_role_list_id => l_team_template_rec.role_list_id
537 ,p_role_list_name => p_role_list_name
538 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
539 --,x_role_list_id => l_team_template_rec.role_list_id * Bug: 4537865
540 ,x_role_list_id => l_new_role_list_id --Bug: 4537865
541 ,x_return_status => l_return_status
542 ,x_error_message_code => l_error_message_code );
543 -- added for Bug: 4537865
544 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
545 l_team_template_rec.role_list_id := l_new_role_list_id;
546 END IF;
547 -- added for Bug: 4537865
548 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
549 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
550 ,p_msg_name => l_error_message_code );
551 END IF;
552
553 END IF; --validate role list
554
555 PA_TEAM_TEMPLATES_PVT.Create_Team_Template( p_team_template_rec => l_team_template_rec
556 ,p_commit => p_commit
557 ,p_validate_only => p_validate_only
558 ,x_team_template_id => x_team_template_id
559 ,x_return_status => x_return_status);
560
561 x_msg_count := FND_MSG_PUB.Count_Msg;
562
563 IF x_msg_count = 1 THEN
564 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
565 ,p_msg_index => 1
566 ,p_data => x_msg_data
567 ,p_msg_index_out => l_msg_index_out
568 );
569 END IF;
570
571 -- Reset the error stack when returning to the calling program
572
573 PA_DEBUG.Reset_Err_Stack;
574
575 -- If errors exist then set the x_return_status to 'E'
576
577 IF x_msg_count >0 THEN
578
579 x_return_status := FND_API.G_RET_STS_ERROR;
580
581 END IF;
582
583 -- Commit if the flag is set and there is no error
584 IF p_commit = FND_API.G_TRUE AND x_msg_count = 0 THEN
585 COMMIT;
586 END IF;
587
588
589 -- Put any message text from message stack into the Message ARRAY
590 --
591 EXCEPTION
592 WHEN OTHERS THEN
593 IF p_commit = FND_API.G_TRUE THEN
594 ROLLBACK TO ASG_PUB_CREATE_TEAM_TEMPLATE;
595 END IF;
596 -- Set the excetption Message and the stack
597 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_TEAM_TEMPLATES_PUB.Create_Team_Template'
598 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
599 --
600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
601 RAISE;
602 --
603 END Create_Team_Template;
604
605
606
607 PROCEDURE Execute_Update_Team_Template
608 ( p_team_template_id IN pa_team_templates.team_template_id%TYPE
609 ,p_record_version_number IN pa_team_templates.record_version_number%TYPE
610 ,p_team_template_name IN pa_team_templates.team_template_name%TYPE := FND_API.G_MISS_CHAR
611 ,p_description IN pa_team_templates.description%TYPE := FND_API.G_MISS_CHAR
612 ,p_start_date_active IN pa_team_templates.start_date_active%TYPE := FND_API.G_MISS_DATE
613 ,p_end_date_active IN pa_team_templates.end_date_active%TYPE := FND_API.G_MISS_DATE
614 ,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
615 ,p_calendar_id IN pa_team_templates.calendar_id%TYPE := FND_API.G_MISS_NUM
616 ,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
617 ,p_work_type_id IN pa_team_templates.work_type_id%TYPE := FND_API.G_MISS_NUM
618 ,p_role_list_name IN pa_role_lists.name%TYPE := FND_API.G_MISS_CHAR
619 ,p_role_list_id IN pa_team_templates.role_list_id%TYPE := FND_API.G_MISS_NUM
620 ,p_team_start_date IN pa_team_templates.team_start_date%TYPE := FND_API.G_MISS_DATE
621 ,p_workflow_in_progress_flag IN pa_team_templates.workflow_in_progress_flag%TYPE := FND_API.G_MISS_CHAR
622 ,p_attribute_category IN pa_team_templates.attribute_category%TYPE := FND_API.G_MISS_CHAR
623 ,p_attribute1 IN pa_team_templates.attribute1%TYPE := FND_API.G_MISS_CHAR
624 ,p_attribute2 IN pa_team_templates.attribute2%TYPE := FND_API.G_MISS_CHAR
625 ,p_attribute3 IN pa_team_templates.attribute3%TYPE := FND_API.G_MISS_CHAR
626 ,p_attribute4 IN pa_team_templates.attribute4%TYPE := FND_API.G_MISS_CHAR
627 ,p_attribute5 IN pa_team_templates.attribute5%TYPE := FND_API.G_MISS_CHAR
628 ,p_attribute6 IN pa_team_templates.attribute6%TYPE := FND_API.G_MISS_CHAR
629 ,p_attribute7 IN pa_team_templates.attribute7%TYPE := FND_API.G_MISS_CHAR
630 ,p_attribute8 IN pa_team_templates.attribute8%TYPE := FND_API.G_MISS_CHAR
631 ,p_attribute9 IN pa_team_templates.attribute9%TYPE := FND_API.G_MISS_CHAR
632 ,p_attribute10 IN pa_team_templates.attribute10%TYPE := FND_API.G_MISS_CHAR
633 ,p_attribute11 IN pa_team_templates.attribute11%TYPE := FND_API.G_MISS_CHAR
634 ,p_attribute12 IN pa_team_templates.attribute12%TYPE := FND_API.G_MISS_CHAR
635 ,p_attribute13 IN pa_team_templates.attribute13%TYPE := FND_API.G_MISS_CHAR
636 ,p_attribute14 IN pa_team_templates.attribute14%TYPE := FND_API.G_MISS_CHAR
637 ,p_attribute15 IN pa_team_templates.attribute15%TYPE := FND_API.G_MISS_CHAR
638 ,p_api_version IN NUMBER := 1.0
639 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
640 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
641 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
642 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
643 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
644 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
645 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
646 )
647
648 IS
649
650 l_team_template_rec team_template_rec;
651
652 BEGIN
653
654 -- Initialize the Error Stack
655 PA_DEBUG.init_err_stack('PA_TEAM_TEMPLATES_PUB.Execute_Update_Team_Template');
656
657 -- Initialize the return status to success
658 x_return_status := FND_API.G_RET_STS_SUCCESS;
659
660 --Clear the global PL/SQL message table
661 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
662 FND_MSG_PUB.initialize;
663 END IF;
664
665 -- Issue API savepoint if the transaction is to be committed
666 IF p_commit = FND_API.G_TRUE THEN
667 SAVEPOINT ASG_PUB_EXEC_UPDATE_TEAM_TEMP;
668 END IF;
669
670 --Log Message
671 IF (P_DEBUG_MODE = 'Y') THEN
672 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PUB.execute_update_team_template.begin'
673 ,x_msg => 'Beginning of Execute_Update_Team_Template'
674 ,x_log_level => 5);
675 END IF;
676
677
678 --Assign scalar parameters to the team template record.
679
680 l_team_template_rec.team_template_id := p_team_template_id;
681 l_team_template_rec.record_version_number := p_record_version_number;
682 l_team_template_rec.team_template_name := p_team_template_name;
683 l_team_template_rec.description := p_description;
684 l_team_template_rec.start_date_active := p_start_date_active;
685 l_team_template_rec.end_date_active := p_end_date_active;
686 l_team_template_rec.calendar_id := p_calendar_id;
687 l_team_template_rec.work_type_id := p_work_type_id;
688 l_team_template_rec.role_list_id := p_role_list_id;
689 l_team_template_rec.team_start_date := p_team_start_date;
690 l_team_template_rec.workflow_in_progress_flag := p_workflow_in_progress_flag;
691 l_team_template_rec.attribute_category := p_attribute_category;
692 l_team_template_rec.attribute1 := p_attribute1;
693 l_team_template_rec.attribute2 := p_attribute2;
694 l_team_template_rec.attribute3 := p_attribute3;
695 l_team_template_rec.attribute4 := p_attribute4;
696 l_team_template_rec.attribute5 := p_attribute5;
697 l_team_template_rec.attribute6 := p_attribute6;
698 l_team_template_rec.attribute7 := p_attribute7;
699 l_team_template_rec.attribute8 := p_attribute8;
700 l_team_template_rec.attribute9 := p_attribute9;
701 l_team_template_rec.attribute10 := p_attribute10;
702 l_team_template_rec.attribute11 := p_attribute11;
703 l_team_template_rec.attribute12 := p_attribute12;
704 l_team_template_rec.attribute13 := p_attribute13;
705 l_team_template_rec.attribute14 := p_attribute14;
706 l_team_template_rec.attribute15 := p_attribute15;
707
708 --Log Message
709 IF (P_DEBUG_MODE = 'Y') THEN
710 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_TEAM_TEMPLATES_PUB.execute_create_team_template.before_calling_update_team_template'
711 ,x_msg => 'Calling Update_Team_Template'
712 ,x_log_level => 5);
713 END IF;
714
715
716 PA_TEAM_TEMPLATES_PUB.Update_Team_Template
717 (p_team_template_rec => l_team_template_rec
718 ,p_calendar_name => p_calendar_name
719 ,p_work_type_name => p_work_type_name
720 ,p_role_list_name => p_role_list_name
721 ,p_api_version => p_api_version
722 ,p_init_msg_list => p_init_msg_list
723 ,p_commit => p_commit
724 ,p_max_msg_count => p_max_msg_count
725 ,x_return_status => x_return_status
726 ,x_msg_count => x_msg_count
727 ,x_msg_data => x_msg_data);
728
729
730 -- Reset the error stack when returning to the calling program
731 PA_DEBUG.Reset_Err_Stack;
732
733 EXCEPTION
734 WHEN OTHERS THEN
735 IF p_commit = FND_API.G_TRUE THEN
736 ROLLBACK TO ASG_PUB_EXEC_UPDATE_TEAM_TEMP;
737 END IF;
738
739 -- Set the excetption Message and the stack
740 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_TEAM_TEMPLATES_PUB.Execute_Update_Team_Template'
741 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
742 --
743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
744 RAISE;
745
746 END Execute_Update_Team_Template;
747
748
749 PROCEDURE Update_Team_Template
750 ( p_team_template_rec IN team_template_rec
751 ,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
752 ,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
753 ,p_role_list_name IN pa_role_lists.name%TYPE := FND_API.G_MISS_CHAR
754 ,p_api_version IN NUMBER := 1.0
755 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
756 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
757 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
758 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
759 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
760 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
761 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
762 )
763 IS
764
765 l_team_template_rec team_template_rec;
766 -- added for Bug: 4537865
767 l_new_calendar_id pa_team_templates.calendar_id%TYPE;
768 l_new_work_type_id pa_team_templates.work_type_id%TYPE;
769 l_new_role_list_id pa_team_templates.role_list_id%TYPE;
770 -- added for Bug: 4537865
771 l_return_status VARCHAR2(1);
772 l_error_message_code fnd_new_messages.message_name%TYPE;
773 l_msg_index_out NUMBER;
774 l_workflow_in_progress_flag pa_team_templates.workflow_in_progress_flag%TYPE;
775
776
777 CURSOR check_record_version_and_wf IS
778 SELECT workflow_in_progress_flag
779 FROM pa_team_templates
780 WHERE team_template_id = p_team_template_rec.team_template_id
781 AND record_version_number = p_team_template_rec.record_version_number;
782
783 BEGIN
784
785 -- Initialize the Error Stack
786 PA_DEBUG.init_err_stack('PA_TEAM_TEMPLATES_PUB.Update_Team_Templates');
787
788 -- Initialize the return status to success
789 x_return_status := FND_API.G_RET_STS_SUCCESS;
790
791 --Issue API savepoint if the transaction is to be committed
792 IF p_commit = FND_API.G_TRUE THEN
793 SAVEPOINT ASG_PUB_UPDATE_TEAM_TEMPLATE;
794 END IF;
795
796 --Clear the global PL/SQL message table
797 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
798 FND_MSG_PUB.initialize;
799 END IF;
800
801 --Log Message
802 IF (P_DEBUG_MODE = 'Y') THEN
803 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_Team_Teamplates_PUB.Update_Team_Template.begin'
804 ,x_msg => 'Beginning of Update_Team_Template'
805 ,x_log_level => 5);
806 END IF;
807
808 -- Assign the record to the local variable
809 l_team_template_rec := p_team_template_rec;
810
811 --check the record version number and workflow in progress flag
812
813 OPEN check_record_version_and_wf;
814
815 FETCH check_record_version_and_wf INTO l_workflow_in_progress_flag;
816
817 IF check_record_version_and_wf%NOTFOUND THEN
818
819 PA_UTILS.Add_Message( p_app_short_name => 'PA'
820 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
821
822 ELSIF l_workflow_in_progress_flag = 'Y' THEN
823
824 PA_UTILS.Add_Message( p_app_short_name => 'PA'
825 ,p_msg_name => 'PA_TEAM_TEMPLATE_WORKFLOW');
826
827 ELSE
828
829 --validate calendar
830 IF (l_team_template_rec.calendar_id IS NOT NULL AND l_team_template_rec.calendar_id <> FND_API.G_MISS_NUM) OR (p_calendar_name IS NOT NULL and p_calendar_name <> FND_API.G_MISS_CHAR) THEN
831
832 PA_CALENDAR_UTILS.Check_Calendar_Name_Or_Id( p_calendar_id => l_team_template_rec.calendar_id
833 ,p_calendar_name => p_calendar_name
834 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
835 --,x_calendar_id => l_team_template_rec.calendar_id * Bug: 4537865
836 ,x_calendar_id => l_new_calendar_id --Bug: 4537865
837 ,x_return_status => l_return_status
838 ,x_error_message_code => l_error_message_code );
839 -- added for Bug Fix: 4537865
840 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
841 l_team_template_rec.calendar_id := l_new_calendar_id;
842 END IF;
843 -- added for Bug Fix: 4537865
844
845 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
846 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
847 ,p_msg_name => l_error_message_code );
848 END IF;
849
850 END IF; -- validate calendar
851
852 --validate work type
853 IF (l_team_template_rec.work_type_id IS NOT NULL AND l_team_template_rec.work_type_id <> FND_API.G_MISS_NUM) OR (p_work_type_name IS NOT NULL AND p_work_type_name <> FND_API.G_MISS_CHAR) THEN
854
855 PA_WORK_TYPE_UTILS.Check_Work_Type_Name_Or_Id( p_work_type_id => l_team_template_rec.work_type_id
856 ,p_name => p_work_type_name
857 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
858 --,x_work_type_id => l_team_template_rec.work_type_id * Bug: 4537865
859 ,x_work_type_id => l_new_work_type_id --Bug: 4537865
860 ,x_return_status => l_return_status
861 ,x_error_message_code => l_error_message_code );
862
863 -- added for bug: 4537865
864 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
865 l_team_template_rec.work_type_id := l_new_work_type_id;
866 END IF;
867 -- added for bug: 4537865
868 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
869 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
870 ,p_msg_name => l_error_message_code );
871 END IF;
872
873 END IF; --validate work type
874
875 --validate role list
876 IF (l_team_template_rec.role_list_id IS NOT NULL AND l_team_template_rec.role_list_id <> FND_API.G_MISS_NUM) OR (p_role_list_name IS NOT NULL AND p_role_list_name <> FND_API.G_MISS_CHAR) THEN
877
878 PA_ROLE_LIST_UTILS.Check_Role_List_Name_Or_Id( p_role_list_id => l_team_template_rec.role_list_id
879 ,p_role_list_name => p_role_list_name
880 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
881 --,x_role_list_id => l_team_template_rec.role_list_id * Bug: 4537865
882 ,x_role_list_id => l_new_role_list_id --Bug: 4537865
883 ,x_return_status => l_return_status
884 ,x_error_message_code => l_error_message_code );
885 -- added for Bug:4537865
886 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
887 l_team_template_rec.role_list_id := l_new_role_list_id;
888 END IF;
889 -- added for Bug:4537865
890 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
891 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
892 ,p_msg_name => l_error_message_code );
893 END IF;
894
895 END IF; --validate role list
896
897 END IF; --record version number and workflow flag check
898
899 CLOSE check_record_version_and_wf;
900
901 PA_TEAM_TEMPLATES_PVT.Update_Team_Template(p_team_template_rec => l_team_template_rec
902 ,p_commit => p_commit
903 ,p_validate_only => p_validate_only
904 ,x_return_status => x_return_status);
905
906
907 x_msg_count := FND_MSG_PUB.Count_Msg;
908
909 IF x_msg_count = 1 THEN
910 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
911 ,p_msg_index => 1
912 ,p_data => x_msg_data
913 ,p_msg_index_out => l_msg_index_out
914 );
915 END IF;
916
917 -- Reset the error stack when returning to the calling program
918
919 PA_DEBUG.Reset_Err_Stack;
920
921 -- If errors exist then set the x_return_status to 'E'
922
923 IF x_msg_count >0 THEN
924
925 x_return_status := FND_API.G_RET_STS_ERROR;
926
927 END IF;
928
929 -- Commit if the flag is set and there is no error
930 IF p_commit = FND_API.G_TRUE AND x_msg_count = 0 THEN
931 COMMIT;
932 END IF;
933
934 -- Put any message text from message stack into the Message ARRAY
935 --
936 EXCEPTION
937 WHEN OTHERS THEN
938 IF p_commit = FND_API.G_TRUE THEN
939 ROLLBACK TO ASG_PUB_UPDATE_TEAM_TEMPLATE;
940 END IF;
941 -- Set the excetption Message and the stack
942 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_TEAM_TEMPLATES_PUB.Update_Team_Template'
943 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
944 --
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
946 RAISE;
947 --
948 END Update_Team_Template;
949
950
951 PROCEDURE Delete_Team_Template
952 ( p_team_template_id IN pa_team_templates.team_template_id%TYPE
953 ,p_record_version_number IN NUMBER
954 ,p_api_version IN NUMBER := 1
955 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
956 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
957 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
958 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
959 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
960 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
961 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
962 )
963 IS
964
965 l_msg_index_out NUMBER;
966 l_workflow_in_progress_flag VARCHAR2(1);
967 l_check_team_template_in_use VARCHAR2(1);
968
969 CURSOR check_record_version_and_wf IS
970 SELECT workflow_in_progress_flag
971 FROM pa_team_templates
972 WHERE team_template_id = p_team_template_id
973 AND record_version_number = p_record_version_number;
974
975 BEGIN
976
977 -- Initialize the Error Stack
978 PA_DEBUG.init_err_stack('PA_TEAM_TEMPLATES_PUB.Delete_Team_Template');
979
980 -- Initialize the return status to success
981 x_return_status := FND_API.G_RET_STS_SUCCESS;
982
983 --Issue API savepoint if the transaction is to be committed
984 IF p_commit = FND_API.G_TRUE THEN
985 SAVEPOINT ASG_PUB_DELETE_TEAM_TEMPLATE;
986 END IF;
987
988 --Clear the global PL/SQL message table
989 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
990 FND_MSG_PUB.initialize;
991 END IF;
992
993 --Log Message
994 IF (P_DEBUG_MODE = 'Y') THEN
995 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_Team_Teamplates_PUB.Delete_Team_Template.begin'
996 ,x_msg => 'Beginning of Delete_Team_Template'
997 ,x_log_level => 5);
998 END IF;
999
1000 --check the record version number and workflow in progress flag
1001
1002 OPEN check_record_version_and_wf;
1003
1004 FETCH check_record_version_and_wf INTO l_workflow_in_progress_flag;
1005
1006 IF check_record_version_and_wf%NOTFOUND THEN
1007
1008 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1009 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
1010
1011 ELSIF l_workflow_in_progress_flag = 'Y' THEN
1012
1013 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1014 ,p_msg_name => 'PA_TEAM_TEMPLATE_WORKFLOW');
1015
1016 ELSE
1017
1018 --Log Message
1019 IF (P_DEBUG_MODE = 'Y') THEN
1020 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_Team_Teamplates_PUB.Delete_Team_Template.calling_pvt'
1021 ,x_msg => 'Calling delete team template pvt'
1022 ,x_log_level => 5);
1023 END IF;
1024
1025 PA_TEAM_TEMPLATES_PVT.Delete_Team_Template
1026 (p_team_template_id => p_team_template_id
1027 ,p_record_version_number => p_record_version_number
1028 ,x_return_status => x_return_status);
1029
1030 END IF;
1031
1032 CLOSE check_record_version_and_wf;
1033
1034 --
1035 -- IF the number of messages is 1 then fetch the message code from the stack and return its text
1036 --
1037 x_msg_count := FND_MSG_PUB.Count_Msg;
1038 IF x_msg_count = 1 THEN
1039 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1040 ,p_msg_index => 1
1041 ,p_data => x_msg_data
1042 ,p_msg_index_out => l_msg_index_out
1043 );
1044 END IF;
1045
1046 -- Reset the error stack when returning to the calling program
1047 PA_DEBUG.Reset_Err_Stack;
1048
1049 -- If any errors exist then set the x_return_status to 'E'
1050
1051 IF x_msg_count > 0 THEN
1052
1053 x_return_status := FND_API.G_RET_STS_ERROR;
1054
1055 END IF;
1056
1057 -- Commit if the flag is set and there is no error
1058 IF p_commit = FND_API.G_TRUE AND x_msg_count = 0 THEN
1059 COMMIT;
1060 END IF;
1061
1062 -- Put any message text from message stack into the Message ARRAY
1063 --
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 IF p_commit = FND_API.G_TRUE THEN
1067 ROLLBACK TO ASG_PUB_DELETE_TEAM_TEMPLATE;
1068 END IF;
1069 -- Set the excetption Message and the stack
1070 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_TEAM_TEMPLATES_PUB.Delete_Assignment'
1071 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1072 --
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1074 RAISE;
1075 --
1076 END Delete_Team_Template;
1077
1078 END pa_team_templates_pub;