[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_SUBTEAMS_PVT
Source
1 PACKAGE BODY PA_PROJECT_SUBTEAMS_PVT AS
2 /*$Header: PARTSTVB.pls 120.2 2005/08/19 17:02:09 mwasowic ship $*/
3
4 PROCEDURE Create_Subteam
5 (
6 p_api_version IN NUMBER := 1.0,
7 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
8 p_commit IN VARCHAR2 := FND_API.g_false,
9 p_validate_only IN VARCHAR2 := FND_API.g_true,
10 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
11 p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
12 p_debug_mode IN VARCHAR2 := 'N',
13 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
14 p_subteam_name IN pa_project_subteams.name%TYPE := FND_API.g_miss_char,
15 p_object_type IN pa_project_subteams.object_type%TYPE := FND_API.g_miss_char,
16 p_object_id IN pa_project_subteams.object_id%TYPE := FND_API.g_miss_num,
17 p_description IN pa_project_subteams.description%TYPE := FND_API.g_miss_char,
18 p_record_version_number IN pa_project_subteams.record_version_number%TYPE := FND_API.g_miss_num,
19 p_attribute_category IN pa_project_subteams.attribute_category%TYPE := FND_API.g_miss_char,
20 p_attribute1 IN pa_project_subteams.attribute1%TYPE := FND_API.G_MISS_CHAR,
21 p_attribute2 IN pa_project_subteams.attribute2%TYPE := FND_API.G_MISS_CHAR,
22 p_attribute3 IN pa_project_subteams.attribute3%TYPE := FND_API.G_MISS_CHAR,
23 p_attribute4 IN pa_project_subteams.attribute4%TYPE := FND_API.G_MISS_CHAR,
24 p_attribute5 IN pa_project_subteams.attribute5%TYPE := FND_API.G_MISS_CHAR,
25 p_attribute6 IN pa_project_subteams.attribute6%TYPE := FND_API.G_MISS_CHAR,
26 p_attribute7 IN pa_project_subteams.attribute7%TYPE := FND_API.G_MISS_CHAR,
27 p_attribute8 IN pa_project_subteams.attribute8%TYPE := FND_API.G_MISS_CHAR,
28 p_attribute9 IN pa_project_subteams.attribute9%TYPE := FND_API.G_MISS_CHAR,
29 p_attribute10 IN pa_project_subteams.attribute10%TYPE := FND_API.G_MISS_CHAR,
30 p_attribute11 IN pa_project_subteams.attribute11%TYPE := FND_API.G_MISS_CHAR,
31 p_attribute12 IN pa_project_subteams.attribute12%TYPE := FND_API.G_MISS_CHAR,
32 p_attribute13 IN pa_project_subteams.attribute13%TYPE := FND_API.G_MISS_CHAR,
33 p_attribute14 IN pa_project_subteams.attribute14%TYPE := FND_API.G_MISS_CHAR,
34 p_attribute15 IN pa_project_subteams.attribute15%TYPE := FND_API.G_MISS_CHAR,
35 x_subteam_row_id OUT NOCOPY ROWID, --File.Sql.39 bug 4440895
36 x_new_subteam_id OUT NOCOPY pa_project_subteams.project_subteam_id%TYPE, --File.Sql.39 bug 4440895
37 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
38 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
39 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
40 )
41 IS
42
43 --l_name_count NUMBER;
44
45 l_rowid ROWID;
46
47 CURSOR get_project is
48 SELECT rowid
49 FROM pa_projects_all
50 WHERE project_id =p_object_id;
51
52 CURSOR get_project_subteam is
53 SELECT rowid
54 FROM pa_project_subteams
55 WHERE name =p_subteam_name
56 AND object_type = p_object_type
57 AND object_id = p_object_id;
58
59 BEGIN
60
61 -- Initialize the Error Stack
62 PA_DEBUG.init_err_stack('PA_PROJECT_SUBTEAMS_PVT.Create_Subteam');
63
64 -- Initialize the error flag
65 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_FALSE;
66
67 -- Issue API savepoint if the transaction is to be committed
68 IF p_commit = FND_API.G_TRUE THEN
69 SAVEPOINT SBT_PVT_CREATE_SBT;
70 END IF;
71
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 --
75 -- Project name to id is already done in the public API
76 --
77
78 --
79 --
80 -- Check that mandatory project id exists
81 --
82 IF ( (p_object_type is null OR p_object_type = FND_API.G_MISS_CHAR)
83 OR (p_object_id IS NULL OR p_object_id = FND_API.G_MISS_NUM)) THEN
84 PA_UTILS.Add_Message( p_app_short_name => 'PA'
85 ,p_msg_name => 'PA_SBT_PRJID_INV');
86 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
87 END IF;
88
89 If(p_object_type = 'PA_PROJECTS') then
90
91 OPEN get_project;
92 FETCH get_project INTO l_rowid;
93 IF get_project%notfound THEN
94 PA_UTILS.Add_Message( p_app_short_name => 'PA'
95 ,p_msg_name => 'PA_SBT_PRJID_INV');
96 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
97 END IF;
98
99 CLOSE get_project;
100
101 --SELECT COUNT(*)
102 -- INTO l_name_count
103 -- FROM pa_projects_all
104 -- WHERE project_id =p_object_id;
105
106 end if;
107 --IF l_name_count < 1 then
108 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
109 -- ,p_msg_name => 'PA_SBT_PRJID_INV');
110 -- PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
111 --END IF;
112
113
114 --
115 -- Check that mandatory subteam name is passed in
116 --
117 IF p_subteam_name IS NULL OR
118 p_subteam_name = FND_API.G_MISS_CHAR THEN
119 PA_UTILS.Add_Message( p_app_short_name => 'PA'
120 ,p_msg_name => 'PA_SBT_NAME_INV');
121 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
122 END IF;
123
124 --
125 -- Check that subteam name is not used by existing record with the same
126 -- project ID
127 --
128
129 OPEN get_project_subteam;
130 FETCH get_project_subteam INTO l_rowid;
131
132 IF get_project_subteam%found THEN
133 PA_UTILS.Add_Message( p_app_short_name => 'PA'
134 ,p_msg_name => 'PA_SBT_NAME_INV');
135 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
136 END IF;
137 CLOSE get_project_subteam;
138
139
140 --SELECT COUNT(*)
141 --INTO l_name_count
142 --FROM pa_project_subteams
143 --WHERE name =p_subteam_name
144 --AND object_type = 'PA_PROJECTS'
145 --AND object_id = p_object_id;
146
147 --IF l_name_count > 0 THEN
148 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
149 -- ,p_msg_name => 'PA_SBT_NAME_INV');
150 -- PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
151 --END IF;
152
153
154 --dbms_output.put_line(l_assignment_rec.project_id);
155 --dbms_output.put_line('proj party return status is '||x_return_status);
156
157
158 -- Create the record if there is no error
159
160 IF (p_validate_only <> FND_API.G_TRUE AND PA_PROJECT_SUBTEAMS_PUB.g_error_exists <> FND_API.G_TRUE) THEN
161 PA_PROJECT_SUBTEAMS_PKG.Insert_Row
162 (p_subteam_name => p_subteam_name
163 ,p_object_type => p_object_type
164 ,p_object_id => p_object_id
165 ,p_description => p_description
166 ,p_attribute_category => p_attribute_category
167 ,p_attribute1 => p_attribute1
168 ,p_attribute2 => p_attribute2
169 ,p_attribute3 => p_attribute3
170 ,p_attribute4 => p_attribute4
171 ,p_attribute5 => p_attribute5
172 ,p_attribute6 => p_attribute6
173 ,p_attribute7 => p_attribute7
174 ,p_attribute8 => p_attribute8
175 ,p_attribute9 => p_attribute9
176 ,p_attribute10 => p_attribute10
177 ,p_attribute11 => p_attribute11
178 ,p_attribute12 => p_attribute12
179 ,p_attribute13 => p_attribute13
180 ,p_attribute14 => p_attribute14
181 ,p_attribute15 => p_attribute15
182 ,x_subteam_row_id => x_subteam_row_id
183 ,x_new_subteam_id => x_new_subteam_id
184 ,x_return_status => x_return_status
185 ,x_msg_count => x_msg_count
186 ,x_msg_data => x_msg_data
187 );
188
189 END IF;
190 -- Commit if the flag is set and there is no error
191 IF p_commit = FND_API.G_TRUE AND PA_PROJECT_SUBTEAMS_PUB.g_error_exists <> FND_API.G_TRUE THEN
192 COMMIT;
193 END IF;
194
195 -- Reset the error stack when returning to the calling program
196 PA_DEBUG.Reset_Err_Stack;
197
198 -- If g_error_exists is TRUE then set the x_return_status to 'E'
199
200 IF PA_PROJECT_SUBTEAMS_PUB.g_error_exists = FND_API.G_TRUE THEN
201
202 x_return_status := FND_API.G_RET_STS_ERROR;
203
204 END IF;
205
206
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 IF p_commit = FND_API.G_TRUE THEN
211 ROLLBACK TO SBT_PVT_CREATE_SBT;
212 END IF;
213 --
214 -- Set the excetption Message and the stack
215 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SUBTEAMS_PVT.Create_Subteam'
216 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
217 --
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
219 RAISE; -- This is optional depending on the needs
220
221 END Create_Subteam;
222
223
224 PROCEDURE Update_Subteam
225 (
226 p_api_version IN NUMBER := 1.0,
227 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
228 p_commit IN VARCHAR2 := FND_API.g_false,
229 p_validate_only IN VARCHAR2 := FND_API.g_true,
230 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
231 p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
232 p_debug_mode IN VARCHAR2 := 'N',
233 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
234 p_subteam_row_id IN ROWID := null,
235 p_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.g_miss_num,
236 p_subteam_name IN pa_project_subteams.name%TYPE := FND_API.g_miss_char,
237 p_object_type IN pa_project_subteams.object_type%TYPE := FND_API.g_miss_char,
238 p_object_id IN pa_project_subteams.object_id%TYPE := FND_API.g_miss_num,
239 p_description IN pa_project_subteams.description%TYPE := FND_API.g_miss_char,
240 p_record_version_number IN pa_project_subteams.record_version_number%TYPE := FND_API.g_miss_num,
241 p_attribute_category IN pa_project_subteams.attribute_category%TYPE := FND_API.g_miss_char,
242 p_attribute1 IN pa_project_subteams.attribute1%TYPE := FND_API.G_MISS_CHAR,
243 p_attribute2 IN pa_project_subteams.attribute2%TYPE := FND_API.G_MISS_CHAR,
244 p_attribute3 IN pa_project_subteams.attribute3%TYPE := FND_API.G_MISS_CHAR,
245 p_attribute4 IN pa_project_subteams.attribute4%TYPE := FND_API.G_MISS_CHAR,
246 p_attribute5 IN pa_project_subteams.attribute5%TYPE := FND_API.G_MISS_CHAR,
247 p_attribute6 IN pa_project_subteams.attribute6%TYPE := FND_API.G_MISS_CHAR,
248 p_attribute7 IN pa_project_subteams.attribute7%TYPE := FND_API.G_MISS_CHAR,
249 p_attribute8 IN pa_project_subteams.attribute8%TYPE := FND_API.G_MISS_CHAR,
250 p_attribute9 IN pa_project_subteams.attribute9%TYPE := FND_API.G_MISS_CHAR,
251 p_attribute10 IN pa_project_subteams.attribute10%TYPE := FND_API.G_MISS_CHAR,
252 p_attribute11 IN pa_project_subteams.attribute11%TYPE := FND_API.G_MISS_CHAR,
253 p_attribute12 IN pa_project_subteams.attribute12%TYPE := FND_API.G_MISS_CHAR,
254 p_attribute13 IN pa_project_subteams.attribute13%TYPE := FND_API.G_MISS_CHAR,
255 p_attribute14 IN pa_project_subteams.attribute14%TYPE := FND_API.G_MISS_CHAR,
256 p_attribute15 IN pa_project_subteams.attribute15%TYPE := FND_API.G_MISS_CHAR,
257 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
258 --x_record_version_number OUT NUMBER ,
259 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
260 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
261 ) IS
262
263 l_count number;
264 l_old_subteam_name pa_project_subteams.name%TYPE := FND_API.g_miss_char;
265 --l_name_count NUMBER;
266 l_rowid ROWID;
267
268 CURSOR get_project is
269 SELECT rowid
270 FROM pa_projects_all
271 WHERE project_id =p_object_id;
272
273 CURSOR get_project_subteam IS
274 SELECT rowid
275 FROM pa_project_subteams
276 WHERE name = p_subteam_name
277 AND object_type = p_object_type
278 AND object_id = p_object_id
279 AND project_subteam_id <> p_subteam_id;
280
281 BEGIN
282
283 -- Initialize the Error Stack
284 PA_DEBUG.init_err_stack('PA_PROJECT_SUBTEAMS_PVT.Update_Subteam');
285
286
287 -- Initialize the error flag
288 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_FALSE;
289
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 -- Issue API savepoint if the transaction is to be committed
293 IF p_commit = FND_API.G_TRUE THEN
294 SAVEPOINT SBT_PVT_UPDATE_SBT;
295 END IF;
296
297
298 -- Check project_subteam_id IS NOT NULL
299 IF p_subteam_id IS NULL THEN
300 PA_UTILS.Add_Message( p_app_short_name => 'PA'
301 ,p_msg_name => 'PA_SBT_ID_INV');
302 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
303 END IF;
304
305 --
306 -- Check that mandatory subteam name is not null
307 --
308 IF p_subteam_name IS NULL THEN
309 PA_UTILS.Add_Message( p_app_short_name => 'PA'
310 ,p_msg_name => 'PA_SBT_NAME_INV');
311 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
312 END IF;
313
314 --
315 -- Check that mandatory project id exists
316 --
317 IF ((p_object_type is null OR p_object_type=FND_API.g_miss_char)
318 OR (p_object_id IS NULL OR p_object_id=FND_API.g_miss_num)) THEN
319 PA_UTILS.Add_Message( p_app_short_name => 'PA'
320 ,p_msg_name => 'PA_SBT_PRJID_INV');
321 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.g_true;
322 ELSE
323 IF (p_object_type='PA_PROJECTS'
324 AND p_object_id is not null
325 AND p_object_id <> FND_API.g_miss_num )THEN
326
327 OPEN get_project;
328 FETCH get_project INTO l_rowid;
329 IF get_project%notfound THEN
330 PA_UTILS.Add_Message( p_app_short_name => 'PA'
331 ,p_msg_name => 'PA_SBT_PRJID_INV');
332 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
333 END IF;
334
335 CLOSE get_project;
336 --SELECT COUNT(*)
337 --INTO l_name_count
338 --FROM pa_projects_all
339 --WHERE project_id =p_object_id;
340
341
342 -- IF l_name_count < 1 then
343 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
344 -- ,p_msg_name => 'PA_SBT_PRJID_INV');
345 -- PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
346 --END IF;
347 END IF;
348 END IF;
349
350 --
351 -- Check that the subteam name is not duplicated for the same object ID
352 --
353 -- SELECT name
354 -- INTO l_old_subteam_name
355 -- FROM pa_project_subteams
356 -- WHERE project_subteam_id = p_subteam_id;
357
358 --IF l_old_subteam_name <> p_subteam_name THEN
359
360 -- p_subteam_name is a new name to be associated with subteam ID
361
362
363 OPEN get_project_subteam;
364 FETCH get_project_subteam INTO l_rowid;
365
366 IF get_project_subteam%found THEN
367 -- if the name is already taken by another subteam
368 PA_UTILS.Add_Message( p_app_short_name => 'PA'
369 ,p_msg_name => 'PA_SBT_NAME_INV');
370 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
371 END IF;
372
373 CLOSE get_project_subteam;
374
375 --SELECT COUNT(*)
376 --INTO l_count
377 --FROM pa_project_subteams
378 --WHERE name = p_subteam_name
379 --AND object_type = p_object_type
380 --AND object_id = p_object_id
381 --AND project_subteam_id <> p_subteam_id;
382
383 -- IF l_count > 0 THEN
384 -- if the name is already taken by another subteam
385 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
386 -- ,p_msg_name => 'PA_SBT_NAME_INV');
387 -- PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
388 --END IF;
389 --END IF;
390
391 IF (p_validate_only = FND_API.G_FALSE AND PA_PROJECT_SUBTEAMS_PUB.g_error_exists <> FND_API.G_TRUE) THEN
392
393 --dbms_output.put_line('Call table handler');
394
395 PA_PROJECT_SUBTEAMS_PKG.Update_Row
396 (p_subteam_row_id => p_subteam_row_id
397 ,p_subteam_id => p_subteam_id
398 ,p_record_version_number => p_record_version_number
399 ,p_subteam_name => p_subteam_name
400 ,p_object_type => p_object_type
401 ,p_object_id => p_object_id
402 ,p_description => p_description
403 ,p_attribute_category => p_attribute_category
404 ,p_attribute1 => p_attribute1
405 ,p_attribute2 => p_attribute2
406 ,p_attribute3 => p_attribute3
407 ,p_attribute4 => p_attribute4
408 ,p_attribute5 => p_attribute5
409 ,p_attribute6 => p_attribute6
410 ,p_attribute7 => p_attribute7
411 ,p_attribute8 => p_attribute8
412 ,p_attribute9 => p_attribute9
413 ,p_attribute10 => p_attribute10
414 ,p_attribute11 => p_attribute11
415 ,p_attribute12 => p_attribute12
416 ,p_attribute13 => p_attribute13
417 ,p_attribute14 => p_attribute14
418 ,p_attribute15 => p_attribute15
419 ,x_return_status => x_return_status
420 ,x_msg_count => x_msg_count
421 ,x_msg_data => x_msg_data
422 );
423 END IF;
424 -- Commit if the flag is set and there is no error
425 IF p_commit = FND_API.G_TRUE AND PA_PROJECT_SUBTEAMS_PUB.g_error_exists <> FND_API.G_TRUE THEN
426 COMMIT;
427 END IF;
428
429 -- Reset the error stack when returning to the calling program
430 PA_DEBUG.Reset_Err_Stack;
431
432 -- If g_error_exists is TRUE then set the x_return_status to 'E'
433
434 IF PA_PROJECT_SUBTEAMS_PUB.g_error_exists = FND_API.G_TRUE THEN
435 x_return_status := FND_API.G_RET_STS_ERROR;
436 END IF;
437
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 IF p_commit = FND_API.G_TRUE THEN
442 ROLLBACK TO SBT_PVT_UPDATE_SBT;
443 END IF;
444 --
445 -- Set the excetption Message and the stack
446 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SUBTEAMS_PVT.Update_Subteam'
447 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
448 --
449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
450 RAISE; -- This is optional depending on the needs
451
452 END Update_Subteam;
453
454
455 PROCEDURE Delete_Subteam
456 (
457 p_api_version IN NUMBER := 1.0,
458 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
459 p_commit IN VARCHAR2 := FND_API.g_false,
460
461 p_validate_only IN VARCHAR2 := FND_API.g_true,
462
463 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
464
465 p_calling_module IN VARCHAR2
466 := 'SELF_SERVICE',
467
468 p_debug_mode IN VARCHAR2 := 'N',
469
470 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
471
472 p_subteam_row_id IN ROWID := NULL,
473
474 p_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := fnd_api.g_miss_num,
475
476 p_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
477
478 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
479 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
480 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
481
482 ) IS
483
484 l_count NUMBER;
485 l_return_status VARCHAR2(1);
486 l_msg_count NUMBER;
487 l_msg_data VARCHAR2(2000);
488 l_record_version_number NUMBER;
489
490 l_rowid ROWID;
491
492 CURSOR get_project_subteam IS
493 SELECT ROWID
494 FROM pa_project_subteam_parties
495 WHERE project_subteam_id = p_subteam_id ;
496
497 BEGIN
498 -- Initialize the Error Stack
499 PA_DEBUG.init_err_stack('PA_PROJECT_SUBTEAMS_PVT.Delete_Subteam');
500
501 -- Initialize the error flag
502 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_FALSE;
503
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505
506 -- Issue API savepoint if the transaction is to be committed
507 IF p_commit = FND_API.G_TRUE THEN
508 SAVEPOINT SBT_PVT_DELETE_SBT;
509 END IF;
510
511 -- If the subteam belongs to any subteam_party table, we can not delete it
512 l_count := 0;
513
514 OPEN get_project_subteam;
515 FETCH get_project_subteam INTO l_rowid;
516 IF get_project_subteam%found THEN
517 PA_UTILS.Add_Message( p_app_short_name => 'PA'
518 ,p_msg_name => 'PA_SBT_ID_INUSE');
519 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.g_true;
520 END IF;
521
522 CLOSE get_project_subteam;
523
524
525 --BEGIN
526 --SELECT 1
527 --INTO l_count
528 --FROM dual
529 --WHERE exists(
530 --SELECT project_subteam_id
531 --FROM pa_project_subteam_parties
532 --WHERE project_subteam_id = p_subteam_id );
533 --exception when no_data_found then
534 -- null;
535 --END;
536
537 --IF l_count >0 THEN
538 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
539 -- ,p_msg_name => 'PA_SBT_ID_INUSE');
540 -- PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
541 --END IF;
542
543
544 IF (p_validate_only = FND_API.G_FALSE AND PA_PROJECT_SUBTEAMS_PUB.g_error_exists <> FND_API.G_TRUE) THEN
545
546 if p_record_version_number = FND_API.G_MISS_NUM then
547 l_record_version_number := NULL;
548 else
549 l_record_version_number := p_record_version_number;
550 end if;
551
552 -- Delete the master record
553 PA_PROJECT_SUBTEAMS_PKG.Delete_Row
554 ( p_subteam_row_id => p_subteam_row_id
555 ,p_subteam_id => p_subteam_id
556 ,p_record_version_number => l_record_version_number
557 ,x_return_status => x_return_status
558 ,x_msg_count => x_msg_count
559 ,x_msg_data => x_msg_data
560 );
561
562 -- Commit if the flag is set and there is no error
563 IF p_commit = FND_API.G_TRUE AND PA_PROJECT_SUBTEAMS_PUB.g_error_exists <> FND_API.G_TRUE THEN
564 COMMIT;
565 END IF;
566
567
568 END IF;
569
570 -- Reset the error stack when returning to the calling program
571 PA_DEBUG.Reset_Err_Stack;
572
573 -- If g_error_exists is TRUE then set the x_return_status to 'E'
574
575 IF PA_PROJECT_SUBTEAMS_PUB.g_error_exists = FND_API.G_TRUE THEN
576
577 x_return_status := FND_API.G_RET_STS_ERROR;
578
579 END IF;
580
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 IF p_commit = FND_API.G_TRUE THEN
585 ROLLBACK TO SBT_PVT_DELETE_SBT;
586 END IF;
587 --
588 -- Set the exception Message and the stack
589 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SUBTEAMS_PVT.Delete_Subteam'
590 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
591 --
592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
593 RAISE; -- This is optional depending on the needs
594
595 END Delete_Subteam;
596
597
598 PROCEDURE Get_Subteam_Id
599 (
600 p_subteam_name IN pa_project_subteams.name%TYPE := fnd_api.g_miss_char,
601 p_object_type IN pa_project_subteams.object_type%TYPE := fnd_api.g_miss_char,
602 p_object_id IN pa_project_subteams.object_id%TYPE := fnd_api.g_miss_num,
603 x_subteam_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
604 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
605 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
606 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
607
608 ) IS
609
610 l_subteam_id NUMBER := NULL;
611 BEGIN
612
613 x_return_status := FND_API.G_RET_STS_SUCCESS;
614
615 SELECT project_subteam_id
616 INTO l_subteam_id
617 FROM pa_project_subteams
618 WHERE name = p_subteam_name
619 AND object_type=p_object_type
620 AND object_id = p_object_id;
621
622 x_subteam_id := l_subteam_id;
623
624 EXCEPTION
625 WHEN OTHERS THEN
626 --
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
628 RAISE; -- This is optional depending on the needs
629
630 END Get_Subteam_Id;
631
632 --
633 --
634 END PA_PROJECT_SUBTEAMS_PVT;