[Home] [Help]
PACKAGE BODY: APPS.IGW_PROJECT_FUNDINGS_PVT
Source
1 PACKAGE BODY Igw_Project_Fundings_Pvt AS
2 --$Header: igwvapfb.pls 115.4 2002/11/19 23:45:25 ashkumar noship $
3
4 ---------------------------------------------------------------------------
5
6 G_PKG_NAME VARCHAR2(30) := 'IGW_PROJECT_FUNDINGS_PVT';
7
8 ---------------------------------------------------------------------------
9
10 PROCEDURE Check_Lock
11 (
12 p_rowid IN VARCHAR2,
13 p_record_version_number IN NUMBER
14 ) IS
15
16 l_api_name CONSTANT VARCHAR2(30) := 'Check_Lock';
17
18 l_locked VARCHAR2(1);
19
20 BEGIN
21
22 /*
23 ** Initialize
24 */
25
26 IF p_rowid IS NOT NULL AND p_record_version_number IS NOT NULL THEN
27
28 SELECT 'N'
29 INTO l_locked
30 FROM igw_project_fundings
31 WHERE rowid = p_rowid;
32 --AND record_version_number = p_record_version_number;
33
34 END IF;
35
36 EXCEPTION
37
38 WHEN no_data_found THEN
39
40 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
41 Fnd_Msg_Pub.Add;
42
43 WHEN others THEN
44
45 Fnd_Msg_Pub.Add_Exc_Msg
46 (
47 p_pkg_name => G_PKG_NAME,
48 p_procedure_name => l_api_name
49 );
50
51 RAISE Fnd_Api.G_Exc_Unexpected_Error;
52
53 END Check_Lock;
54
55 ---------------------------------------------------------------------------
56
57 PROCEDURE Validate_Project_Task
58 (
59 p_rowid IN VARCHAR2,
60 p_proposal_installment_id IN NUMBER,
61 p_project_number IN VARCHAR2,
62 x_project_id OUT NOCOPY NUMBER,
63 p_task_number IN VARCHAR2,
64 x_task_id OUT NOCOPY NUMBER
65 ) IS
66
67 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Project_Task';
68
69 l_count_budget_entry_method NUMBER;
70
71 BEGIN
72
73 /*
74 ** Initialize
75 */
76
77 IF p_project_number IS NULL THEN
78
79 x_project_id := NULL;
80
81 ELSE
82
83 BEGIN
84
85 SELECT p.project_id
86 INTO x_project_id
87 FROM pa_lookups lk,
88 pa_projects p,
89 gms_project_types gpt,
90 pa_project_types pt
91 WHERE p.segment1 = p_project_number
92 AND pt.project_type = p.project_type
93 AND gpt.project_type = pt.project_type
94 AND p.project_status_code not in ('CLOSED' , 'UNAPPROVED')
95 AND lk.lookup_type(+) = 'ALLOWABLE FUNDING LEVEL'
96 AND lk.lookup_code(+) = pt.allowable_funding_level_code
97 AND pt.project_type_class_code in ('INDIRECT','CAPITAL')
98 AND gpt.sponsored_flag = 'Y'
99 AND p.template_flag ='N';
100
101 EXCEPTION
102
103 WHEN no_data_found THEN
104
105 Fnd_Message.Set_Name('IGW','IGW_SS_BUD_PROJECT_INVALID');
106 Fnd_Msg_Pub.Add;
107
108 END;
109
110 END IF;
111
112 SELECT count(distinct t.cost_budget_entry_method_code)
113 INTO l_count_budget_entry_method
114 FROM pa_projects p,
115 pa_project_types t
116 WHERE ((p.project_id = x_project_id) OR
117 (p.project_id IN
118 (SELECT project_id
119 FROM igw_project_fundings
120 WHERE proposal_installment_id = p_proposal_installment_id
121 AND (p_rowid IS NULL OR rowid <> p_rowid))))
122 AND t.project_type = p.project_type;
123
124 IF l_count_budget_entry_method > 1 THEN
125
126 Fnd_Message.Set_Name('IGW','IGW_SS_MULT_BUD_ENTRY_METHODS');
127 Fnd_Msg_Pub.Add;
128 Return;
129
130 END IF;
131
132 IF p_task_number IS NULL THEN
133
134 x_task_id := NULL;
135
136 ELSE
137
138 BEGIN
139
140 SELECT task_id
141 INTO x_task_id
142 FROM pa_tasks_top_v
143 WHERE project_id = x_project_id
144 AND task_number = p_task_number;
145
146 EXCEPTION
147
148 WHEN no_data_found THEN
149
150 Fnd_Message.Set_Name('IGW','IGW_SS_BUD_TASK_INVALID');
151 Fnd_Msg_Pub.Add;
152
153 END;
154
155 END IF;
156
157 EXCEPTION
158
159 WHEN others THEN
160
161 Fnd_Msg_Pub.Add_Exc_Msg
162 (
163 p_pkg_name => G_PKG_NAME,
164 p_procedure_name => l_api_name
165 );
166
167 RAISE Fnd_Api.G_Exc_Unexpected_Error;
168
169 END Validate_Project_Task;
170
171 ---------------------------------------------------------------------------
172
173 PROCEDURE Create_Project_Funding
174 (
175 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_False,
176 p_validate_only IN VARCHAR2 := Fnd_Api.G_False,
177 p_commit IN VARCHAR2 := Fnd_Api.G_False,
178 x_rowid OUT NOCOPY VARCHAR2,
179 x_proposal_funding_id OUT NOCOPY NUMBER,
180 p_proposal_installment_id IN NUMBER,
181 p_project_number IN VARCHAR2,
182 p_project_id IN NUMBER,
183 p_task_number IN VARCHAR2,
184 p_task_id IN NUMBER,
185 p_funding_amount IN NUMBER,
186 p_date_allocated IN DATE,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2
190 ) IS
191
192 l_api_name CONSTANT VARCHAR2(30) := 'Create_Project_Funding';
193
194 l_project_id NUMBER;
195 l_task_id NUMBER;
196
197 l_count NUMBER;
198 l_return_status VARCHAR2(1);
199
200 BEGIN
201
202 /*
203 ** Establish Savepoint for Rollback
204 */
205
206 SAVEPOINT Create_Project_Funding_Pvt;
207
208
209 /*
210 ** Initialize
211 */
212
213 x_return_status := Fnd_Api.G_Ret_Sts_Success;
214
215 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
216
217 Fnd_Msg_Pub.Initialize;
218
219 END IF;
220
221
222 Validate_Project_Task
223 (
224 p_rowid => null,
225 p_proposal_installment_id => p_proposal_installment_id,
226 p_project_number => p_project_number,
227 x_project_id => l_project_id,
228 p_task_number => p_task_number,
229 x_task_id => l_task_id
230 );
231
232
233 /*
234 ** Discontinue processing if any error has been encountered during
235 ** the earlier stages
236 */
237
238 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
239
240 RAISE Fnd_Api.G_Exc_Error;
241
242 END IF;
243
244 SELECT count(1)
245 INTO l_count
246 FROM igw_project_fundings
247 WHERE proposal_installment_id = p_proposal_installment_id
248 AND project_id = l_project_id
249 AND ((task_id IS NOT NULL AND l_task_id IS NULL) OR
250 (task_id IS NULL AND l_task_id IS NOT NULL));
251
252 IF l_count > 0 THEN
253
254 Fnd_Message.Set_Name('IGW','IGW_SS_PROJECT_FUNDING_LEVEL');
255 Fnd_Msg_Pub.Add;
256 RAISE Fnd_Api.G_Exc_Error;
257
258 END IF;
259
260
261 /*
262 ** Discontinue processing if API invoked in validation mode
263 */
264
265 IF Fnd_Api.To_Boolean(p_validate_only) THEN
266
267 RETURN;
268
269 END IF;
270
271 /*
272 ** Invoke Table Handler to insert data
273 */
274
275 Igw_Project_Fundings_Tbh.Insert_Row
276 (
277 x_rowid => x_rowid,
278 x_proposal_funding_id => x_proposal_funding_id,
279 p_proposal_installment_id => p_proposal_installment_id,
280 p_project_id => l_project_id,
281 p_task_id => l_task_id,
282 p_funding_amount => p_funding_amount,
283 p_date_allocated => p_date_allocated,
284 x_return_status => l_return_status
285 );
286
287 /*
288 ** Commit data if API invoked in commit mode
289 */
290
291 IF Fnd_Api.To_Boolean(p_commit) THEN
292
293 COMMIT;
294
295 END IF;
296
297
298 EXCEPTION
299
300 WHEN Fnd_Api.G_Exc_Error THEN
301
302 ROLLBACK TO Create_Project_Funding_Pvt;
303
304 x_return_status := Fnd_Api.G_Ret_Sts_Error;
305
306 Fnd_Msg_Pub.Count_And_Get
307 (
308 p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311
312 WHEN Fnd_Api.G_Exc_Unexpected_Error THEN
313
314 ROLLBACK TO Create_Project_Funding_Pvt;
315
316 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
317
318 Fnd_Msg_Pub.Count_And_Get
319 (
320 p_count => x_msg_count,
321 p_data => x_msg_data
322 );
323
324 WHEN others THEN
325
326 ROLLBACK TO Create_Project_Funding_Pvt;
327
328 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
329
330 Fnd_Msg_Pub.Add_Exc_Msg
331 (
332 p_pkg_name => G_PKG_NAME,
333 p_procedure_name => l_api_name
334 );
335
336 Fnd_Msg_Pub.Count_And_Get
337 (
338 p_count => x_msg_count,
339 p_data => x_msg_data
340 );
341
342 END Create_Project_Funding;
343
344 ---------------------------------------------------------------------------
345
346 PROCEDURE Update_Project_Funding
347 (
348 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_False,
349 p_validate_only IN VARCHAR2 := Fnd_Api.G_False,
350 p_commit IN VARCHAR2 := Fnd_Api.G_False,
351 p_rowid IN VARCHAR2,
352 p_proposal_funding_id IN NUMBER,
353 p_record_version_number IN NUMBER,
354 p_proposal_installment_id IN NUMBER,
355 p_project_number IN VARCHAR2,
356 p_project_id IN NUMBER,
357 p_task_number IN VARCHAR2,
358 p_task_id IN NUMBER,
359 p_funding_amount IN NUMBER,
360 p_date_allocated IN DATE,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2
364 ) IS
365
366 l_api_name CONSTANT VARCHAR2(30) := 'Update_Project_Funding';
367
368 l_project_id NUMBER;
369 l_task_id NUMBER;
370
371 l_count NUMBER;
372 l_return_status VARCHAR2(1);
373
374 BEGIN
375
376 /*
377 ** Establish Savepoint for Rollback
378 */
379
380 SAVEPOINT Update_Project_Funding_Pvt;
381
382
383 /*
384 ** Initialize
385 */
386
387 x_return_status := Fnd_Api.G_Ret_Sts_Success;
388
389 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
390
391 Fnd_Msg_Pub.Initialize;
392
393 END IF;
394
395
396 Validate_Project_Task
397 (
398 p_rowid => p_rowid,
399 p_proposal_installment_id => p_proposal_installment_id,
400 p_project_number => p_project_number,
401 x_project_id => l_project_id,
402 p_task_number => p_task_number,
403 x_task_id => l_task_id
404 );
405
406 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
407
408 RAISE Fnd_Api.G_Exc_Error;
409
410 END IF;
411
412 SELECT count(1)
413 INTO l_count
414 FROM igw_project_fundings
415 WHERE proposal_installment_id = p_proposal_installment_id
416 AND project_id = l_project_id
417 AND rowid <> p_rowid
418 AND ((task_id IS NOT NULL AND l_task_id IS NULL) OR
419 (task_id IS NULL AND l_task_id IS NOT NULL));
420
421 IF l_count > 0 THEN
422
423 Fnd_Message.Set_Name('IGW','IGW_SS_PROJECT_FUNDING_LEVEL');
424 Fnd_Msg_Pub.Add;
425 RAISE Fnd_Api.G_Exc_Error;
426
427 END IF;
428
429 /*
430 ** Check Lock before proceeding
431 */
432
433 Check_Lock
434 (
435 p_rowid => p_rowid,
436 p_record_version_number => p_record_version_number
437 );
438
439
440 /*
441 ** Discontinue processing if any error has been encountered during
442 ** the earlier stages
443 */
444
445
446 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
447
448 RAISE Fnd_Api.G_Exc_Error;
449
450 END IF;
451
452
453 /*
454 ** Discontinue processing if API invoked in validation mode
455 */
456
457 IF Fnd_Api.To_Boolean(p_validate_only) THEN
458
459 RETURN;
460
461 END IF;
462
463
464 /*
465 ** Invoke Table Handler to Update data
466 */
467
468 Igw_Project_Fundings_Tbh.Update_Row
469 (
470 p_rowid => p_rowid,
471 p_proposal_funding_id => p_proposal_funding_id,
472 p_proposal_installment_id => p_proposal_installment_id,
473 p_project_id => l_project_id,
474 p_task_id => l_task_id,
475 p_funding_amount => p_funding_amount,
476 p_date_allocated => p_date_allocated,
477 x_return_status => x_return_status
478 );
479
480
481 /*
482 ** Commit data if API invoked in commit mode
483 */
484
485 IF Fnd_Api.To_Boolean(p_commit) THEN
486
487 COMMIT;
488
489 END IF;
490
491
492 EXCEPTION
493
494 WHEN Fnd_Api.G_Exc_Error THEN
495
496 ROLLBACK TO Update_Project_Funding_Pvt;
497
498 x_return_status := Fnd_Api.G_Ret_Sts_Error;
499
500 Fnd_Msg_Pub.Count_And_Get
501 (
502 p_count => x_msg_count,
503 p_data => x_msg_data
504 );
505
506 WHEN Fnd_Api.G_Exc_Unexpected_Error THEN
507
508 ROLLBACK TO Update_Project_Funding_Pvt;
509
510 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
511
512 Fnd_Msg_Pub.Count_And_Get
513 (
514 p_count => x_msg_count,
515 p_data => x_msg_data
516 );
517
518 WHEN others THEN
519
520 ROLLBACK TO Update_Project_Funding_Pvt;
521
522 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
523
524 Fnd_Msg_Pub.Add_Exc_Msg
525 (
526 p_pkg_name => G_PKG_NAME,
527 p_procedure_name => l_api_name
528 );
529
530 Fnd_Msg_Pub.Count_And_Get
531 (
532 p_count => x_msg_count,
533 p_data => x_msg_data
534 );
535
536 END Update_Project_Funding;
537
538 ---------------------------------------------------------------------------
539
540 PROCEDURE Delete_Project_Funding
541 (
542 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_False,
543 p_validate_only IN VARCHAR2 := Fnd_Api.G_False,
544 p_commit IN VARCHAR2 := Fnd_Api.G_False,
545 p_rowid IN VARCHAR2,
546 p_record_version_number IN NUMBER,
547 x_return_status OUT NOCOPY VARCHAR2,
548 x_msg_count OUT NOCOPY NUMBER,
549 x_msg_data OUT NOCOPY VARCHAR2
550 ) IS
551
552 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Project_Funding';
553
554 l_return_status VARCHAR2(1);
555
556 BEGIN
557
558 /*
559 ** Establish Savepoint for Rollback
560 */
561
562 SAVEPOINT Delete_Project_Funding_Pvt;
563
564
565 /*
566 ** Initialize
567 */
568
569 x_return_status := Fnd_Api.G_Ret_Sts_Success;
570
571 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
572
573 Fnd_Msg_Pub.Initialize;
574
575 END IF;
576
577
578 /*
579 ** Check Lock before proceeding
580 */
581
582 Check_Lock
583 (
584 p_rowid => p_rowid,
585 p_record_version_number => p_record_version_number
586 );
587
588 /*
589 ** Discontinue processing if any error has been encountered during
590 ** the earlier stages
591 */
592
593 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
594
595 RAISE Fnd_Api.G_Exc_Error;
596
597 END IF;
598
599
600 /*
601 ** Discontinue processing if API invoked in validation mode
602 */
603
604 IF Fnd_Api.To_Boolean(p_validate_only) THEN
605
606 RETURN;
607
608 END IF;
609
610
611 /*
612 ** Invoke Table Handler to Delete data
613 */
614
615 Igw_Project_Fundings_Tbh.Delete_Row
616 (
617 p_rowid => p_rowid,
618 x_return_status => x_return_status
619 );
620
621
622 /*
623 ** Commit data if API invoked in commit mode
624 */
625
626 IF Fnd_Api.To_Boolean(p_commit) THEN
627
628 COMMIT;
629
630 END IF;
631
632
633 EXCEPTION
634
635 WHEN Fnd_Api.G_Exc_Error THEN
636
637 ROLLBACK TO Delete_Project_Funding_Pvt;
638
639 x_return_status := Fnd_Api.G_Ret_Sts_Error;
640
641 Fnd_Msg_Pub.Count_And_Get
642 (
643 p_count => x_msg_count,
644 p_data => x_msg_data
645 );
646
647 WHEN Fnd_Api.G_Exc_Unexpected_Error THEN
648
649 ROLLBACK TO Delete_Project_Funding_Pvt;
650
651 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
652
653 Fnd_Msg_Pub.Count_And_Get
654 (
655 p_count => x_msg_count,
656 p_data => x_msg_data
657 );
658
659 WHEN others THEN
660
661 ROLLBACK TO Delete_Project_Funding_Pvt;
662
663 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
664
665 Fnd_Msg_Pub.Add_Exc_Msg
666 (
667 p_pkg_name => G_PKG_NAME,
668 p_procedure_name => l_api_name
669 );
670
671 Fnd_Msg_Pub.Count_And_Get
672 (
673 p_count => x_msg_count,
674 p_data => x_msg_data
675 );
676
677 END Delete_Project_Funding;
678
679 ---------------------------------------------------------------------------
680
681 END Igw_Project_Fundings_Pvt;