[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_SUBTEAM_PARTIES_PVT
Source
1 PACKAGE BODY pa_project_subteam_parties_pvt AS
2 --$Header: PARTSPVB.pls 120.1 2005/08/19 17:01:44 mwasowic noship $
3
4 PROCEDURE Create_Subteam_Party
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
10 p_validate_only IN VARCHAR2 := FND_API.g_true,
11
12 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
13
14 p_calling_module IN VARCHAR2
15 := 'SELF_SERVICE',
16
17 p_debug_mode IN VARCHAR2 := 'N',
18
19 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
20
21 p_project_subteam_id IN pa_project_subteams.Project_subteam_id%TYPE := FND_API.g_miss_num,
22
23 p_object_type IN varchar2,
24
25 p_object_id IN NUMBER := fnd_api.g_miss_num,
26
27 p_primary_subteam_flag IN VARCHAR2 := 'Y',
28
29 x_project_subteam_party_row_id OUT NOCOPY ROWID, --File.Sql.39 bug 4440895
30 x_project_subteam_party_id OUT NOCOPY pa_project_subteam_parties.project_subteam_party_id%TYPE, --File.Sql.39 bug 4440895
31 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
32 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
33 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
34 )
35 IS
36
37 l_name_count NUMBER;
38 l_msg_index_out NUMBER;
39 l_row_id ROWID;
40
41 CURSOR check_primary_flag is
42 select rowid
43 from pa_project_subteam_parties
44 where primary_subteam_flag = 'Y' and object_id = p_object_id
45 and object_type = p_object_type;
46
47 BEGIN
48
49 -- Initialize the Error Stack
50 PA_DEBUG.init_err_stack('PA_SUBTEAM_PARTIES_PVT.Create_Subteam_Party');
51
52 -- Initialize the error flag
53 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_FALSE;
54
55 -- Issue API savepoint if the transaction is to be committed
56 IF p_commit = FND_API.G_TRUE THEN
57 SAVEPOINT STP_PVT_CREATE_STP;
58 END IF;
59
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61
62 --
63 --
64 -- Check that mandatory project subteam id exists
65 --
66 IF p_project_subteam_id IS NULL
67 OR p_project_subteam_id = FND_API.G_MISS_NUM THEN
68 PA_UTILS.Add_Message( p_app_short_name => 'PA'
69 ,p_msg_name => 'PA_SBP_SBT_INV');
70 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
71 --ELSE
72 -- SELECT COUNT(*)
73 -- INTO l_name_count
74 -- FROM pa_project_subteams
75 -- WHERE project_subteam_id = p_project_subteam_id;
76
77 -- IF l_name_count < 1 then
78 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
79 -- ,p_msg_name => 'PA_SBP_ID_INV');
80 -- PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
81 -- END IF;
82 END IF;
83
84 --
85 -- Check that mandatory object_type is passed in
86 --
87 IF p_object_type IS NULL OR
88 p_object_type = FND_API.G_MISS_CHAR OR
89 (p_object_type <> 'PA_PROJECT_ASSIGNMENTS' and p_object_type <> 'PA_PROJECT_PARTIES') THEN
90
91 PA_UTILS.Add_Message( p_app_short_name => 'PA'
92 ,p_msg_name => 'PA_SBP_OBJTYPE_INV');
93 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
94 ELSE
95 --
96 -- Check that mandatory object_id is passed in
97 --
98 IF p_object_id IS NULL OR
99 p_object_id = FND_API.G_MISS_NUM THEN
100 PA_UTILS.Add_Message( p_app_short_name => 'PA'
101 ,p_msg_name => 'PA_SBP_OBJID_INV');
102 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
103 --ELSE
104 --if p_object_type = 'PA_PROJECT_ASSIGNMENTS' then
105 --select count(*)
106 --into l_name_count
107 --from PA_PROJECT_ASSIGNMENTS
108 --where assignment_id = p_object_id;
109
110 --if l_name_count < 1 then
111 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
112 -- ,p_msg_name => 'PA_SBP_OBJID_INV');
113 -- PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
114 --end if;
115 --elsif p_object_type = 'PA_PROJECT_PARTIES' then
116 --select count(*)
117 --into l_name_count
118 --from PA_PROJECT_PARTIES
119 --where project_party_id = p_object_id;
120
121 --if l_name_count < 1 then
122 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
123 -- ,p_msg_name => 'PA_SBP_OBJID_INV');
124 -- PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
125 --end if;
126 --end if;
127
128 END IF;
129 END IF;
130
131 -- Check that for a given object_type and object_id,
132 -- there is only one primary subteam
133 if PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists <> FND_API.G_TRUE
134 and p_primary_subteam_flag = 'Y' then
135
136 OPEN check_primary_flag;
137 FETCH check_primary_flag INTO l_row_id;
138
139 IF check_primary_flag%found THEN
140 PA_UTILS.Add_Message( p_app_short_name => 'PA'
141 ,p_msg_name => 'PA_SBP_PRIMARY_FLAG_INV');
142 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
143 end if;
144 CLOSE check_primary_flag;
145
146 end if;
147
148
149 -- Create the record if there is no error
150
151 IF (p_validate_only <> FND_API.G_TRUE AND PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists <> FND_API.G_TRUE) THEN
152 PA_PROJECT_SUBTEAM_PARTIES_PKG.Insert_Row
153 (
154 p_project_subteam_id => p_project_subteam_id
155 ,p_object_type => p_object_type
156 ,p_object_id => p_object_id
157 ,p_primary_subteam_flag => p_primary_subteam_flag
158 ,x_project_subteam_party_row_id => x_project_subteam_party_row_id
159 ,x_project_subteam_party_id => x_project_subteam_party_id
160 ,x_return_status => x_return_status
161 ,x_msg_count => x_msg_count
162 ,x_msg_data => x_msg_data
163 );
164
165 END IF;
166 -- Commit if the flag is set and there is no error
167 IF p_commit = FND_API.G_TRUE AND PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists <> FND_API.G_TRUE THEN
168 COMMIT;
169 END IF;
170
171 x_msg_count := FND_MSG_PUB.Count_Msg;
172 IF x_msg_count = 1 THEN
173 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
174 ,p_msg_index => 1
175 ,p_data => x_msg_data
176 ,p_msg_index_out => l_msg_index_out
177 );
178 END IF;
179
180 -- Reset the error stack when returning to the calling program
181 PA_DEBUG.Reset_Err_Stack;
182
183 -- If g_error_exists is TRUE then set the x_return_status to 'E'
184
185 IF PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists = FND_API.G_TRUE THEN
186
187 x_return_status := FND_API.G_RET_STS_ERROR;
188
189 END IF;
190
191
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 IF p_commit = FND_API.G_TRUE THEN
196 ROLLBACK TO STP_PVT_CREATE_STP;
197 END IF;
198 --
199 -- Set the excetption Message and the stack
200 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SUBTEAM_PARTIES_PVT.Create_Subteam_Party'
201 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
202 --
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
204 RAISE; -- This is optional depending on the needs
205
206 END Create_Subteam_Party;
207
208
209 PROCEDURE Update_Subteam_Party
210 (
211 p_api_version IN NUMBER := 1.0,
212 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
213 p_commit IN VARCHAR2 := FND_API.g_false,
214
215 p_validate_only IN VARCHAR2 := FND_API.g_true,
216
217 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
218
219 p_calling_module IN VARCHAR2
220 := 'SELF_SERVICE',
221
222 p_debug_mode IN VARCHAR2 := 'N',
223
224 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
225
226 p_project_subteam_party_row_id IN ROWID := null,
227
228 p_project_subteam_party_id IN pa_project_subteam_parties.project_subteam_party_id%TYPE := FND_API.g_miss_num,
229
230 p_project_subteam_id IN pa_project_subteam_parties.project_subteam_id%TYPE := FND_API.g_miss_num,
231
232 p_object_type IN varchar2,
233
234 p_object_id IN NUMBER := fnd_api.g_miss_num,
235
236 p_primary_subteam_flag IN VARCHAR2 := fnd_api.g_miss_char,
237
238 p_record_version_number IN pa_project_subteams.record_version_number%TYPE := fnd_api.g_miss_num,
239
240 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
241 x_record_version_number OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
242 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
243 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
244 ) IS
245
246 l_count number;
247 l_old_subteam_name pa_project_subteams.name%TYPE := FND_API.g_miss_char;
248 l_name_count NUMBER;
249 l_msg_index_out NUMBER;
250 l_delete_flag NUMBER := 0;
251 l_record_version_number NUMBER;
252 l_row_id ROWID;
253
254 CURSOR check_primary_key IS
255 select rowid
256 from pa_project_subteam_parties
257 where primary_subteam_flag = 'Y' and object_id = p_object_id
258 and object_type = p_object_type AND
259 project_subteam_party_id <> p_project_subteam_party_id;
260
261
262 BEGIN
263
264 -- Initialize the Error Stack
265 PA_DEBUG.init_err_stack('PA_PROJECT_SUBTEAM_PARTIES_PVT.Update_Subteam_Party');
266
267
268 -- Initialize the error flag
269 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_FALSE;
270
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272
273 -- Issue API savepoint if the transaction is to be committed
274 IF p_commit = FND_API.G_TRUE THEN
275 SAVEPOINT STP_PVT_UPDATE_STP;
276 END IF;
277
278 -- Check project_subteam_party_id IS NOT NULL
279 IF p_project_subteam_party_id IS NULL OR p_project_subteam_party_id = fnd_api.g_miss_num THEN
280 PA_UTILS.Add_Message( p_app_short_name => 'PA'
281 ,p_msg_name => 'PA_SBP_ID_INV');
282 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
283 END IF;
284
285 -- Check project_subteam_id IS NOT NULL and exists
286 IF p_project_subteam_id IS NULL OR p_project_subteam_id = fnd_api.g_miss_num THEN
287 -- We need to call delete_subteam here
288 l_delete_flag := 1;
289 --ELSE
290 --SELECT COUNT(*)
291 --INTO l_name_count
292 --FROM pa_project_subteams
293 --WHERE project_subteam_id =p_project_subteam_id;
294
295 --IF l_name_count < 1 then
296 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
297 -- ,p_msg_name => 'PA_SBP_SBT_INV');
298 --PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
299 --END IF;
300 END IF;
301
302 --
303 -- Check that mandatory object_type is passed in
304 --
305 IF p_object_type IS NULL OR
306 p_object_type = FND_API.G_MISS_CHAR OR
307 (p_object_type <> 'PA_PROJECT_ASSIGNMENTS' and p_object_type <> 'PA_PROJECT_PARTIES') THEN
308
309 PA_UTILS.Add_Message( p_app_short_name => 'PA'
310 ,p_msg_name => 'PA_SBP_OBJTYPE_INV');
311 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
312 ELSE
313 --
314 -- Check that mandatory object_id is passed in
315 --
316 IF p_object_id IS NULL OR
317 p_object_id = FND_API.G_MISS_NUM THEN
318 PA_UTILS.Add_Message( p_app_short_name => 'PA'
319 ,p_msg_name => 'PA_SBP_OBJID_INV');
320 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
321 --ELSE
322 --if p_object_type = 'PA_PROJECT_ASSIGNMENTS' then
323 -- select count(*)
324 -- into l_name_count
325 -- from PA_PROJECT_ASSIGNMENTS
326 -- where assignment_id = p_object_id;
327
328 -- if l_name_count < 1 then
329 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
330 -- ,p_msg_name => 'PA_SBP_OBJID_INV');
331 -- PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
332 -- end if;
333 --elsif p_object_type = 'PA_PROJECT_PARTIES' then
334 -- select count(*)
335 -- into l_name_count
336 -- from PA_PROJECT_PARTIES
337 -- where project_party_id = p_object_id;
338
339 -- if l_name_count < 1 then
340 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
341 -- ,p_msg_name => 'PA_SBP_OBJID_INV');
342 -- PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
343 -- end if;
344 --end if;
345
346 END IF;
347 END IF;
348
349 -- Check that for a given object_type and object_id,
350 -- there is only one primary subteam
351 if PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists <> FND_API.G_TRUE
352 and p_primary_subteam_flag = 'Y' then
353
354 OPEN check_primary_key;
355 FETCH check_primary_key INTO l_row_id;
356
357 IF check_primary_key%found THEN
358
359 PA_UTILS.Add_Message( p_app_short_name => 'PA'
360 ,p_msg_name => 'PA_SBP_PRIMARY_FLAG_INV');
361 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
362 end if;
363 CLOSE check_primary_key;
364
365 end if;
366
367
368 if p_record_version_number = FND_API.G_MISS_NUM then
369 l_record_version_number := NULL;
370 else
371 l_record_version_number := p_record_version_number;
372 end if;
373
374 IF (p_validate_only <> FND_API.G_TRUE AND l_delete_flag = 1) THEN
375 PA_PROJECT_SUBTEAM_PARTIES_PKG.Delete_Row
376 (
377 p_project_subteam_party_row_id => p_project_subteam_party_row_id
378 ,p_project_subteam_party_id => p_project_subteam_party_id
379 ,p_record_version_number => l_record_version_number
380 ,x_return_status => x_return_status
381 ,x_msg_count => l_name_count
382 ,x_msg_data => x_msg_data
383 );
384 ELSIF (p_validate_only <> FND_API.G_TRUE AND PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists <> FND_API.G_TRUE) THEN
385
386 PA_PROJECT_SUBTEAM_PARTIES_PKG.Update_Row
387 (
388 p_project_subteam_party_row_id => p_project_subteam_party_row_id
389 ,p_project_subteam_party_id => p_project_subteam_party_id
390 ,p_project_subteam_id => p_project_subteam_id
391 ,p_primary_subteam_flag => p_primary_subteam_flag
392 ,p_record_version_number => l_record_version_number
393 ,x_return_status => x_return_status
394 ,x_msg_count => l_name_count
395 ,x_msg_data => x_msg_data
396 );
397 END IF;
398 -- Commit if the flag is set and there is no error
399 IF p_commit = FND_API.G_TRUE AND PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists <> FND_API.G_TRUE THEN
400 COMMIT;
401 END IF;
402
403 x_msg_count := FND_MSG_PUB.Count_Msg;
404 IF x_msg_count = 1 THEN
405 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
406 ,p_msg_index => 1
407 ,p_data => x_msg_data
408 ,p_msg_index_out => l_msg_index_out
409 );
410 END IF;
411
412 -- Reset the error stack when returning to the calling program
413 PA_DEBUG.Reset_Err_Stack;
414
415 -- If g_error_exists is TRUE then set the x_return_status to 'E'
416
417 IF PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists = FND_API.G_TRUE THEN
418 x_return_status := FND_API.G_RET_STS_ERROR;
419
420 SELECT record_version_number
421 into x_record_version_number
422 FROM pa_project_subteam_parties
423 WHERE project_subteam_party_id = p_project_subteam_party_id;
424
425 --
426 IF (SQL%NOTFOUND) THEN
427 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
428 END IF;
429
430 ELSE
431 SELECT record_version_number
432 into x_record_version_number
433 FROM pa_project_subteam_parties
434 WHERE project_subteam_party_id = p_project_subteam_party_id;
435
436 END IF;
437
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 IF p_commit = FND_API.G_TRUE THEN
442 ROLLBACK TO STP_PVT_UPDATE_STP;
443 END IF;
444 --
445 -- Set the excetption Message and the stack
446 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_SUBTEAM_PARTIES_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_Party;
453
454 PROCEDURE Update_SPT_Assgn
455 (
456 p_api_version IN NUMBER := 1.0,
457 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
458 p_commit IN VARCHAR2 := FND_API.g_false,
459
460 p_validate_only IN VARCHAR2 := FND_API.g_true,
461
462 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
463
464 p_calling_module IN VARCHAR2
465 := 'SELF_SERVICE',
466
467 p_debug_mode IN VARCHAR2 := 'N',
468
469 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
470
471 p_project_subteam_party_row_id IN ROWID := null,
472
473 p_project_subteam_party_id IN pa_project_subteam_parties.project_subteam_party_id%TYPE := NULL,
474
475 p_project_subteam_id IN pa_project_subteam_parties.project_subteam_id%TYPE := NULL,
476
477 p_object_type IN varchar2,
478
479 p_object_id IN NUMBER := fnd_api.g_miss_num,
480
481 p_primary_subteam_flag IN VARCHAR2 := fnd_api.g_miss_char,
482
483 p_record_version_number IN pa_project_subteams.record_version_number%TYPE := FND_API.G_MISS_NUM,
484
485 p_get_subteam_party_id_flag IN VARCHAR2 := 'N',
486
487 x_project_subteam_party_id OUT NOCOPY pa_project_subteam_parties.project_subteam_party_id%TYPE, --File.Sql.39 bug 4440895
488 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
489 x_record_version_number OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
490 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
491 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
492 ) IS
493 l_project_subteam_party_row_id ROWID;
494 l_record_version_number NUMBER;
495 --l_project_subteam_party_id pa_project_subteam_parties.project_subteam_party_id%TYPE;
496 --l_project_subteam_id pa_project_subteam_parties.project_subteam_id%TYPE;
497
498 l_project_subteam_party_id number;
499 l_project_subteam_id number;
500
501 CURSOR get_subteam_party_id IS
502 SELECT project_subteam_party_id
503 From pa_project_subteam_parties
504 WHERE object_id = p_object_id
505 AND object_type = 'PA_PROJECT_ASSIGNMENTS'
506 AND primary_subteam_flag = 'Y';
507
508 BEGIN
509
510 if p_record_version_number = FND_API.G_MISS_NUM then
511 l_record_version_number := NULL;
512 else
513 l_record_version_number := p_record_version_number;
514 end if;
515
516 IF p_project_subteam_party_id = fnd_api.g_miss_num then
517 l_project_subteam_party_id := NULL;
518 ELSE
519 l_project_subteam_party_id := p_project_subteam_party_id;
520 END IF;
521
522
523 IF p_project_subteam_id = fnd_api.g_miss_num then
524 l_project_subteam_id := NULL;
525 ELSE
526 l_project_subteam_id := p_project_subteam_id;
527 END IF;
528
529
530 IF (p_get_subteam_party_id_flag = 'Y') THEN
531 -- select the subteam_party_id from the subteam_party table
532 OPEN get_subteam_party_id;
533 FETCH get_subteam_party_id INTO l_project_subteam_party_id;
534 CLOSE get_subteam_party_id ;
535 END IF;
536
537
538 IF (l_project_subteam_party_id IS NULL AND
539 l_project_subteam_id IS NOT NULL) then
540 -- insert subteam_party
541
542 pa_project_subteam_parties_pvt.create_subteam_party(
543
544 p_api_version => p_api_version,
545
546 p_init_msg_list => p_init_msg_list,
547
548 p_commit =>p_commit,
549
550
551 p_validate_only => p_validate_only,
552
553
554 p_validation_level => p_validation_level,
555
556 p_calling_module => p_calling_module,
557
558 p_debug_mode => p_debug_mode,
559
560 p_max_msg_count => p_max_msg_count,
561
562 p_project_subteam_id => l_project_subteam_id,
563
564 p_object_type => p_object_type,
565 p_object_id => p_object_id,
566
567 p_primary_subteam_flag => p_primary_subteam_flag,
568
569 x_project_subteam_party_row_id => l_project_subteam_party_row_id,
570
571 x_project_subteam_party_id => x_project_subteam_party_id,
572 x_return_status => x_return_status,
573 x_msg_count => x_msg_count,
574 x_msg_data => x_msg_data );
575 ELSIF (l_project_subteam_party_id IS NOT NULL AND
576 l_project_subteam_id IS NOT NULL) then
577 -- update subteam_party
578 pa_project_subteam_parties_pvt.update_subteam_party(
579
580 p_api_version => p_api_version,
581
582 p_init_msg_list => p_init_msg_list,
583
584 p_commit =>p_commit,
585
586 p_validate_only => p_validate_only,
587
588 p_validation_level => p_validation_level,
589
590 p_calling_module => p_calling_module,
591
592 p_debug_mode => p_debug_mode,
593
594 p_max_msg_count => p_max_msg_count,
595
596 p_project_subteam_party_row_id => p_project_subteam_party_row_id,
597
598 p_project_subteam_party_id => l_project_subteam_party_id,
599
600 p_project_subteam_id => l_project_subteam_id,
601
602 p_object_type => p_object_type,
603
604 p_object_id => p_object_id,
605
606 p_primary_subteam_flag => p_primary_subteam_flag,
607
608 p_record_version_number => l_record_version_number,
609
610 x_return_status => x_return_status,
611 x_record_version_number => x_record_version_number,
612
613 x_msg_count => x_msg_count,
614 x_msg_data => x_msg_data );
615 ELSIF (l_project_subteam_party_id IS NOT NULL AND
616 l_project_subteam_id IS NULL) then
617 -- delete subteam_party
618 pa_project_subteam_parties_pvt.delete_subteam_party(
619
620 p_api_version => p_api_version,
621
622 p_init_msg_list => p_init_msg_list,
623
624 p_commit =>p_commit,
625
626
627 p_validate_only => p_validate_only,
628
629
630 p_validation_level => p_validation_level,
631
632 p_calling_module => p_calling_module,
633
634 p_debug_mode => p_debug_mode,
635
636 p_max_msg_count => p_max_msg_count,
637
638 p_project_subteam_party_row_id => p_project_subteam_party_row_id,
639
640 p_project_subteam_party_id => l_project_subteam_party_id,
641
642 p_record_version_number => l_record_version_number,
643
644 x_return_status => x_return_status,
645 x_msg_count => x_msg_count,
646 x_msg_data => x_msg_data );
647 END IF;
648
649 END Update_SPT_Assgn;
650
651
652 PROCEDURE Delete_Subteam_Party
653 (
654 p_api_version IN NUMBER := 1.0,
655 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
656 p_commit IN VARCHAR2 := FND_API.g_false,
657
658 p_validate_only IN VARCHAR2 := FND_API.g_true,
659
660 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
661
662 p_calling_module IN VARCHAR2
663 := 'SELF_SERVICE',
664
665 p_debug_mode IN VARCHAR2 := 'N',
666
667 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
668
669 p_project_subteam_party_row_id IN ROWID := NULL,
670
671 p_project_subteam_party_id IN pa_project_subteams.project_subteam_id%TYPE := fnd_api.g_miss_num,
672
673 p_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
674
675 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
676 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
677 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
678
679 ) IS
680
681 l_count NUMBER;
682 l_return_status VARCHAR2(1);
683 l_msg_count NUMBER;
684 l_msg_data VARCHAR2(2000);
685 l_record_version_number NUMBER;
686 l_msg_index_out NUMBER;
687
688 BEGIN
689 -- Initialize the Error Stack
690 PA_DEBUG.init_err_stack('PA_PROJECT_SUBTEAM_PARTIES_PVT.Delete_Subteam_Party');
691
692 -- Initialize the error flag
693 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_FALSE;
694
695 x_return_status := FND_API.G_RET_STS_SUCCESS;
696
697 -- Issue API savepoint if the transaction is to be committed
698 IF p_commit = FND_API.G_TRUE THEN
699 SAVEPOINT STP_PVT_DELETE_STP;
700 END IF;
701
702 IF (p_validate_only <> FND_API.G_TRUE AND PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists <> FND_API.G_TRUE) THEN
703
704 if p_record_version_number = FND_API.G_MISS_NUM then
705 l_record_version_number := NULL;
706 else
707 l_record_version_number := p_record_version_number;
708 end if;
709
710
711 -- Delete the master record
712 PA_PROJECT_SUBTEAM_PARTIES_PKG.Delete_Row
713 ( p_project_subteam_party_row_id => p_project_subteam_party_row_id
714 ,p_project_subteam_party_id => p_project_subteam_party_id
715 ,p_record_version_number => l_record_version_number
716 ,x_return_status => x_return_status
717 ,x_msg_count => x_msg_count
718 ,x_msg_data => x_msg_data
719 );
720
721 -- Commit if the flag is set and there is no error
722 IF p_commit = FND_API.G_TRUE AND PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists <> FND_API.G_TRUE THEN
723 COMMIT;
724 END IF;
725
726
727 END IF;
728
729 x_msg_count := FND_MSG_PUB.Count_Msg;
730 IF x_msg_count = 1 THEN
731 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
732 ,p_msg_index => 1
733 ,p_data => x_msg_data
734 ,p_msg_index_out => l_msg_index_out
735 );
736 END IF;
737
738 -- Reset the error stack when returning to the calling program
739 PA_DEBUG.Reset_Err_Stack;
740
741 -- If g_error_exists is TRUE then set the x_return_status to 'E'
742
743 IF PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists = FND_API.G_TRUE THEN
744
745 x_return_status := FND_API.G_RET_STS_ERROR;
746
747 END IF;
748
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 IF p_commit = FND_API.G_TRUE THEN
753 ROLLBACK TO STP_PVT_DELETE_STP;
754 END IF;
755 --
756 -- Set the exception Message and the stack
757 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SUBTEAM_PARTIES_PVT.Delete_Subteam_Party'
758 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
759 --
760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
761 RAISE; -- This is optional depending on the needs
762
763 END Delete_Subteam_Party;
764
765
766 PROCEDURE Delete_SubteamParty_By_Obj
767 (
768 p_api_version IN NUMBER := 1.0,
769 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
770 p_commit IN VARCHAR2 := FND_API.g_false,
771
772 p_validate_only IN VARCHAR2 := FND_API.g_true,
773
774 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
775
776 p_calling_module IN VARCHAR2
777 := NULL,
778
779 p_debug_mode IN VARCHAR2 := 'N',
780
781 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
782
783 p_object_type IN varchar2,
784
785 p_object_id IN NUMBER := fnd_api.g_miss_num,
786
787
788 -- p_project_subteam_party_row_id IN ROWID := NULL,
789
790 -- p_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
791
792 -- p_project_subteam_party_id IN pa_project_subteams.project_subteam_id%TYPE := fnd_api.g_miss_num,
793
794 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
795 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
796 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
797
798 ) IS
799
800
801 CURSOR get_id IS
802 SELECT project_subteam_party_id
803 FROM pa_project_subteam_parties
804 WHERE object_id = p_object_id
805 AND object_type = p_object_type;
806
807 --l_subteam_party_id pa_project_subteams.project_subteam_id%TYPE := fnd_api.g_miss_num;
808
809 l_subteam_party_id NUMBER := fnd_api.g_miss_num;
810
811 l_row_id ROWID;
812 l_number NUMBER;
813 l_msg_index_out NUMBER;
814
815 BEGIN
816
817 -- Initialize the Error Stack
818 PA_DEBUG.init_err_stack('PA_PROJECT_SUBTEAM_PARTIES_PVT.Delete_Subteam_Party');
819
820 -- Initialize the error flag
821 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_FALSE;
822
823 x_return_status := FND_API.G_RET_STS_SUCCESS;
824
825 -- Issue API savepoint if the transaction is to be committed
826 IF p_commit = FND_API.G_TRUE THEN
827 SAVEPOINT STP_PVT_DELETE_STP;
828 END IF;
829
830 IF (p_validate_only <> FND_API.G_TRUE ) THEN
831
832 FOR subteam_p_id IN get_id LOOP
833
834 PA_PROJECT_SUBTEAM_PARTIES_PKG.Delete_Row
835 ( p_project_subteam_party_row_id => l_row_id
836 ,p_project_subteam_party_id => subteam_p_id.project_subteam_party_id
837 ,p_record_version_number => NULL
838 ,x_return_status => x_return_status
839 ,x_msg_count => x_msg_count
840 ,x_msg_data => x_msg_data
841 );
842 END LOOP;
843
844 END IF;
845
846 -- Commit if the flag is set and there is no error
847 IF p_commit = FND_API.G_TRUE AND PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists <> FND_API.G_TRUE THEN
848 COMMIT;
849 END IF;
850
851 x_msg_count := FND_MSG_PUB.Count_Msg;
852 IF x_msg_count = 1 THEN
853 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
854 ,p_msg_index => 1
855 ,p_data => x_msg_data
856 ,p_msg_index_out => l_msg_index_out
857 );
858 END IF;
859
860 -- Reset the error stack when returning to the calling program
861 PA_DEBUG.Reset_Err_Stack;
862
863 -- If g_error_exists is TRUE then set the x_return_status to 'E'
864
865 IF PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists = FND_API.G_TRUE THEN
866
867 x_return_status := FND_API.G_RET_STS_ERROR;
868
869 END IF;
870
871
872 EXCEPTION
873 WHEN OTHERS THEN
874 IF p_commit = FND_API.G_TRUE THEN
875 ROLLBACK TO STP_PVT_DELETE_STP;
876 END IF;
877 --
878 -- Set the exception Message and the stack
879 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROJECT_SUBTEAM_PARTIES_PVT.Delete_Subteam_Party'
880 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
881 --
882 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
883 RAISE; -- This is optional depending on the needs
884
885 END;
886
887 --
888 --
889 END PA_PROJECT_SUBTEAM_PARTIES_PVT;