[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_SPECIAL_REVIEWS_PVT
Source
1 PACKAGE BODY Igw_Prop_Special_Reviews_Pvt AS
2 --$Header: igwvrevb.pls 115.6 2002/11/15 00:45:41 ashkumar ship $
3
4 ---------------------------------------------------------------------------
5
6 G_PKG_NAME VARCHAR2(30) := 'IGW_PROP_SPECIAL_REVIEWS_PVT';
7
8 ---------------------------------------------------------------------------
9
10 PROCEDURE Check_Ins_Upd_Dep_Data
11 (
12 p_approval_type_code IN VARCHAR2,
13 p_protocol_number IN VARCHAR2,
14 p_approval_date IN DATE,
15 p_application_date IN DATE,
16 x_return_status OUT NOCOPY VARCHAR2
17 ) IS
18
19 l_api_name CONSTANT VARCHAR2(30) := 'Check_Update_Dependent_Data';
20
21 BEGIN
22
23 /*
24 ** Initialize
25 */
26
27 x_return_status := Fnd_Api.G_Ret_Sts_Success;
28 /*
29
30 IF p_rowid IS NULL THEN
31
32 RETURN;
33
34 END IF;
35 */
36
37 IF p_approval_type_code='2' then
38 IF (p_approval_date IS NULL) then
39 x_return_status:= FND_API.G_RET_STS_ERROR;
40 FND_MESSAGE.SET_NAME('IGW','IGW_PROP_REV_APPDATE_REQ');
41 ELSE
42 IF (p_protocol_number IS NULL) then
43 x_return_status:=FND_API.G_RET_STS_ERROR;
44 FND_MESSAGE.SET_NAME('IGW','IGW_PROP_REV_PROTNO_REQ');
45 ELSE
46 return;
47 END IF;
48 END IF;
49
50 ELSIF p_approval_type_code='1' then
51 IF (p_application_date IS NULL) then
52 x_return_status:= FND_API.G_RET_STS_ERROR;
53 FND_MESSAGE.SET_NAME('IGW','IGW_PROP_REV_APPLDATE_REQ');
54 ELSE
55 return;
56 END IF;
57 ELSE
58 return;
59 END IF;
60
61 EXCEPTION
62
63 WHEN others THEN
64
65 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
66
67 Fnd_Msg_Pub.Add_Exc_Msg
68 (
69 p_pkg_name => G_PKG_NAME,
70 p_procedure_name => l_api_name
71 );
72
73 RAISE Fnd_Api.G_Exc_Unexpected_Error;
74
75 END Check_Ins_Upd_Dep_Data;
76
77 ---------------------------------------------------------------------------
78
79
80 PROCEDURE Check_Lock
81 (
82 p_rowid IN VARCHAR2,
83 p_record_version_number IN NUMBER,
84 x_return_status OUT NOCOPY VARCHAR2
85 ) IS
86
87 l_api_name CONSTANT VARCHAR2(30) := 'Check_Lock';
88
89 l_locked VARCHAR2(1);
90
91 BEGIN
92
93 /*
94 ** Initialize
95 */
96
97 x_return_status := Fnd_Api.G_Ret_Sts_Success;
98
99 IF p_rowid IS NOT NULL AND p_record_version_number IS NOT NULL THEN
100
101 SELECT 'N'
102 INTO l_locked
103 FROM igw_prop_special_reviews
104 WHERE rowid = p_rowid
105 AND record_version_number = p_record_version_number;
106
107 END IF;
108
109 EXCEPTION
110
111 WHEN no_data_found THEN
112
113 x_return_status := Fnd_Api.G_Ret_Sts_Error;
114 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
115 Fnd_Msg_Pub.Add;
116
117 WHEN others THEN
118
119 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
120
121 Fnd_Msg_Pub.Add_Exc_Msg
122 (
123 p_pkg_name => G_PKG_NAME,
124 p_procedure_name => l_api_name
125 );
126
127 RAISE Fnd_Api.G_Exc_Unexpected_Error;
128
129 END Check_Lock;
130
131 ---------------------------------------------------------------------------
132
133 PROCEDURE Create_Prop_Special_Reviews
134 (
135 p_init_msg_list IN VARCHAR2,
136 p_validate_only IN VARCHAR2,
137 p_commit IN VARCHAR2,
138 x_rowid OUT NOCOPY VARCHAR2,
139 p_proposal_id IN NUMBER,
140 p_proposal_number IN VARCHAR2,
141 p_special_review_code IN VARCHAR2,
142 p_special_review_desc IN VARCHAR2,
143 p_special_review_type IN VARCHAR2,
144 p_special_review_type_desc IN VARCHAR2,
145 p_approval_type_code IN VARCHAR2,
146 p_approval_type_desc IN VARCHAR2,
147 p_protocol_number IN VARCHAR2,
148 p_application_date IN DATE,
149 p_approval_date IN DATE,
150 p_comments IN VARCHAR2,
151 x_return_status OUT NOCOPY VARCHAR2,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2
154 ) IS
155
156 l_api_name CONSTANT VARCHAR2(30) := 'Create_Prop_Special_Reviews';
157
158 l_proposal_id NUMBER := p_proposal_id;
159 l_special_review_code VARCHAR2(30) := p_special_review_code;
160 l_special_review_type VARCHAR2(30) := p_special_review_type;
161 l_approval_type_code VARCHAR2(30) := p_approval_type_code;
162 l_return_status VARCHAR2(1);
163
164 BEGIN
165
166 /*
167 ** Establish Savepoint for Rollback
168 */
169
170 SAVEPOINT Create_Prop_Spl_Review_Pvt;
171
172
173 /*
174 ** Initialize
175 */
176
177 x_return_status := Fnd_Api.G_Ret_Sts_Success;
178
179 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
180
181 Fnd_Msg_Pub.Initialize;
182
183 END IF;
184
185
186 /*
187 ** Get Ids from Values if Ids not passed. Check Mandatory columns
188 */
189
190 /*
191 IF p_proposal_id IS NULL THEN
192
193 Igw_Utils.Get_Proposal_Id
194 (
195 p_context_field => 'PROPOSAL_ID',
196 p_proposal_number => p_proposal_number,
197 x_proposal_id => l_proposal_id,
198 x_return_status => l_return_status
199 );
200
201 END IF;
202
203 */
204 /*
205 IF Igw_Security.Allow_Modify
206 (
207 p_function_name => 'PROPOSAL',
208 p_proposal_id => l_proposal_id,
209 p_user_id => Fnd_Global.User_Id
210 )
211 = 'N' THEN
212
213 x_return_status := Fnd_Api.G_Ret_Sts_Error;
214 Fnd_Message.Set_Name('IGW','IGW_SS_SEC_NO_MODIFY_RIGHTS');
215 Fnd_Msg_Pub.Add;
216 RAISE Fnd_Api.G_Exc_Error;
217
218 END IF;
219
220
221 */
222
223 /*
224 IF p_special_review_code IS NULL THEN
225
226 Igw_Utils.Get_Lookup_Code
227 (
228 p_context_field => 'SPECIAL_REVIEW_CODE',
229 p_lookup_type => 'IGW_SPECIAL_REVIEWS',
230 p_lookup_meaning => p_special_review_desc,
231 x_lookup_code => l_special_review_code,
232 x_return_status => l_return_status
233 );
234
235 END IF;
236
237
238 IF p_special_review_type IS NULL THEN
239
240 Igw_Utils.Get_Lookup_Code
241 (
242 p_context_field => 'SPECIAL_REVIEW_TYPE',
243 p_lookup_type => 'IGW_SPECIAL_REVIEW_TYPES',
244 p_lookup_meaning => p_special_review_type_desc,
245 x_lookup_code => l_special_review_type,
246 x_return_status => l_return_status
247 );
248
249 END IF;
250
251
252 IF p_approval_type_code IS NULL THEN
253
254 Igw_Utils.Get_Lookup_Code
255 (
256 p_context_field => 'APPROVAL_TYPE_CODE',
257 p_lookup_type => 'IGW_REVIEW_APPROVAL_TYPES',
258 p_lookup_meaning => p_approval_type_desc,
259 x_lookup_code => l_approval_type_code,
260 x_return_status => l_return_status
261 );
262 END IF;
263
264 */
265 /*
266 ** Discontinue processing if any error has been encountered during
267 ** the earlier stages
268 */
269
270 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
271
272 RAISE Fnd_Api.G_Exc_Error;
273
274 END IF;
275
276 Check_Ins_Upd_Dep_Data
277 (
278 p_approval_type_code => l_approval_type_code,
279 p_protocol_number => p_protocol_number,
280 p_approval_date => p_approval_date,
281 p_application_date => p_application_date,
282 x_return_status =>l_return_status
283 );
284
285 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
286
287 RAISE Fnd_Api.G_Exc_Error;
288
289 END IF;
290
291
292 /*
293 ** Discontinue processing if API invoked in validation mode
294 */
295
296 IF Fnd_Api.To_Boolean(p_validate_only) THEN
297
298 RETURN;
299
300 END IF;
301
302
303 /*
304 ** Invoke Table Handler to insert data
305 */
306
307
308 Igw_Prop_Special_Reviews_Tbh.Insert_Row
309 (
310 x_rowid => x_rowid,
311 p_proposal_id => l_proposal_id,
312 p_special_review_code => l_special_review_code,
313 p_special_review_type => l_special_review_type,
314 p_approval_type_code => l_approval_type_code,
315 p_protocol_number => p_protocol_number,
316 p_application_date => p_application_date,
317 p_approval_date => p_approval_date,
318 p_comments => p_comments,
319 x_return_status => l_return_status
320 );
321
322
323 /*
324 ** Commit data if API invoked in commit mode
325 */
326
327 IF Fnd_Api.To_Boolean(p_commit) THEN
328
329 COMMIT;
330
331 END IF;
332
333
334 EXCEPTION
335
336 WHEN Fnd_Api.G_Exc_Error THEN
337
338 ROLLBACK TO Create_Prop_Spl_Review_Pvt;
339
340 x_return_status := Fnd_Api.G_Ret_Sts_Error;
341
342 Fnd_Msg_Pub.Count_And_Get
343 (
344 p_count => x_msg_count,
345 p_data => x_msg_data
346 );
347
348 WHEN Fnd_Api.G_Exc_Unexpected_Error THEN
349
350 ROLLBACK TO Create_Prop_Spl_Review_Pvt;
351
352 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
353
354 Fnd_Msg_Pub.Count_And_Get
355 (
356 p_count => x_msg_count,
357 p_data => x_msg_data
358 );
359
360 WHEN others THEN
361
362 ROLLBACK TO Create_Prop_Spl_Review_Pvt;
363
364 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
365
366 Fnd_Msg_Pub.Add_Exc_Msg
367 (
368 p_pkg_name => G_PKG_NAME,
369 p_procedure_name => l_api_name
370 );
371
372 Fnd_Msg_Pub.Count_And_Get
373 (
374 p_count => x_msg_count,
375 p_data => x_msg_data
376 );
377
378 END Create_Prop_Special_Reviews;
379
380 ---------------------------------------------------------------------------
381
382 PROCEDURE Update_Prop_Special_Reviews
383 (
384 p_init_msg_list IN VARCHAR2,
385 p_validate_only IN VARCHAR2,
386 p_commit IN VARCHAR2,
387 p_rowid IN VARCHAR2,
388 p_proposal_id IN NUMBER,
389 p_proposal_number IN VARCHAR2,
390 p_special_review_code IN VARCHAR2,
391 p_special_review_desc IN VARCHAR2,
392 p_special_review_type IN VARCHAR2,
393 p_special_review_type_desc IN VARCHAR2,
394 p_approval_type_code IN VARCHAR2,
395 p_approval_type_desc IN VARCHAR2,
396 p_protocol_number IN VARCHAR2,
397 p_application_date IN DATE,
398 p_approval_date IN DATE,
399 p_comments IN VARCHAR2,
400 p_record_version_number IN NUMBER,
401 x_return_status OUT NOCOPY VARCHAR2,
402 x_msg_count OUT NOCOPY NUMBER,
403 x_msg_data OUT NOCOPY VARCHAR2
404 ) IS
405
406 l_api_name CONSTANT VARCHAR2(30) := 'Update_Prop_Special_Reviews';
407
408 l_proposal_id NUMBER := p_proposal_id;
409 l_special_review_code VARCHAR2(30) := p_special_review_code;
410 l_special_review_type VARCHAR2(30) := p_special_review_type;
411 l_approval_type_code VARCHAR2(30) := p_approval_type_code;
412
413 l_return_status VARCHAR2(1);
414
415 BEGIN
416
417 /*
418 ** Establish Savepoint for Rollback
419 */
420
421 SAVEPOINT Update_Prop_Spl_Reviews_Pvt;
422
423
424 /*
425 ** Initialize
426 */
427
428 x_return_status := Fnd_Api.G_Ret_Sts_Success;
429
430 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
431
432 Fnd_Msg_Pub.Initialize;
433
434 END IF;
435
436
437 /*
438 ** Get Ids from Values if Ids not passed
439 */
440 /*
441 IF p_proposal_id IS NULL THEN
442
443 Igw_Utils.Get_Proposal_Id
444 (
445 p_context_field => 'PROPOSAL_ID',
446 p_proposal_number => p_proposal_number,
447 x_proposal_id => l_proposal_id,
448 x_return_status => l_return_status
449 );
450
451 END IF;
452
453
454 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
455
456 RAISE Fnd_Api.G_Exc_Error;
457
458 END IF;
459 */
460
461 /*
462 ** Check Modify Rights
463 */
464 /*
465
466 IF Igw_Security.Allow_Modify
467 (
468 p_function_name => 'PROPOSAL',
469 p_proposal_id => l_proposal_id,
470 p_user_id => Fnd_Global.User_Id
471 )
472 = 'N' THEN
473
474 x_return_status := Fnd_Api.G_Ret_Sts_Error;
475 Fnd_Message.Set_Name('IGW','IGW_SS_SEC_NO_MODIFY_RIGHTS');
476 Fnd_Msg_Pub.Add;
477 RAISE Fnd_Api.G_Exc_Error;
478
479 END IF;
480
481 */
482
483
484 /*
485 ** Check Lock before proceeding
486 */
487
488 Check_Lock
489 (
490 p_rowid => p_rowid,
491 p_record_version_number => p_record_version_number,
492 x_return_status => l_return_status
493 );
494
495
496 /*
497 ** Discontinue processing if any error has been encountered during
498 ** the earlier stages
499 */
500
501
502 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
503
504 RAISE Fnd_Api.G_Exc_Error;
505
506 END IF;
507
508 /*
509 IF p_special_review_code IS NULL THEN
510
511 Igw_Utils.Get_Lookup_Code
512 (
513 p_context_field => 'SPECIAL_REVIEW_CODE',
514 p_lookup_type => 'IGW_SPECIAL_REVIEWS',
515 p_lookup_meaning => p_special_review_desc,
516 x_lookup_code => l_special_review_code,
517 x_return_status => l_return_status
518 );
519
520 END IF;
521
522
523 IF p_special_review_type IS NULL THEN
524
525 Igw_Utils.Get_Lookup_Code
526 (
527 p_context_field => 'SPECIAL_REVIEW_TYPE',
528 p_lookup_type => 'IGW_SPECIAL_REVIEW_TYPES',
529 p_lookup_meaning => p_special_review_type_desc,
530 x_lookup_code => l_special_review_type,
531 x_return_status => l_return_status
532 );
533
534 END IF;
535
536
537 IF p_approval_type_code IS NULL THEN
538
539 Igw_Utils.Get_Lookup_Code
540 (
541 p_context_field => 'APPROVAL_TYPE_CODE',
542 p_lookup_type => 'IGW_REVIEW_APPROVAL_TYPES',
543 p_lookup_meaning => p_approval_type_desc,
544 x_lookup_code => l_approval_type_code,
545 x_return_status => l_return_status
546 );
547 END IF;
548 */
549
550
551 /*
552 ** Discontinue processing if any error has been encountered during
553 ** the earlier stages
554 */
555
556 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
557
558 RAISE Fnd_Api.G_Exc_Error;
559
560 END IF;
561
562 Check_Ins_Upd_Dep_Data
563 (
564 p_approval_type_code => l_approval_type_code,
565 p_protocol_number => p_protocol_number,
566 p_approval_date => p_approval_date,
567 p_application_date => p_application_date,
568 x_return_status =>l_return_status
569 );
570
571 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
572
573 RAISE Fnd_Api.G_Exc_Error;
574
575 END IF;
576
577
578 /*
579 ** Discontinue processing if API invoked in validation mode
580 */
581
582 IF Fnd_Api.To_Boolean(p_validate_only) THEN
583
584 RETURN;
585
586 END IF;
587
588
589
590 /*
591 ** Invoke Table Handler to Update data
592 */
593
594 Igw_Prop_Special_Reviews_Tbh.Update_Row
595 (
596 p_rowid => p_rowid,
597 p_proposal_id => l_proposal_id,
598 p_special_review_code => l_special_review_code,
599 p_special_review_type => l_special_review_type,
600 p_approval_type_code => l_approval_type_code,
601 p_protocol_number => p_protocol_number,
602 p_application_date => p_application_date,
603 p_approval_date => p_approval_date,
604 p_comments => p_comments,
605 p_record_version_number => p_record_version_number,
606 x_return_status => x_return_status
607 );
608
609
610 /*
611 ** Commit data if API invoked in commit mode
612 */
613
614 IF Fnd_Api.To_Boolean(p_commit) THEN
615
616 COMMIT;
617
618 END IF;
619
620
621 EXCEPTION
622
623 WHEN Fnd_Api.G_Exc_Error THEN
624
625 ROLLBACK TO Update_Prop_Spl_Reviews_Pvt;
626
627 x_return_status := Fnd_Api.G_Ret_Sts_Error;
628
629 Fnd_Msg_Pub.Count_And_Get
630 (
631 p_count => x_msg_count,
632 p_data => x_msg_data
633 );
634
635 WHEN Fnd_Api.G_Exc_Unexpected_Error THEN
636
637 ROLLBACK TO Update_Prop_Spl_Reviews_Pvt;
638
639 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_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 others THEN
648
649 ROLLBACK TO Update_Prop_Spl_Reviews_Pvt;
650
651 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
652
653 Fnd_Msg_Pub.Add_Exc_Msg
654 (
655 p_pkg_name => G_PKG_NAME,
656 p_procedure_name => l_api_name
657 );
658
659 Fnd_Msg_Pub.Count_And_Get
660 (
661 p_count => x_msg_count,
662 p_data => x_msg_data
663 );
664
665 END Update_Prop_Special_Reviews;
666
667 ---------------------------------------------------------------------------
668
669 PROCEDURE Delete_Prop_Special_Reviews
670 (
671 p_init_msg_list IN VARCHAR2,
672 p_validate_only IN VARCHAR2,
673 p_commit IN VARCHAR2,
674 p_rowid IN VARCHAR2,
675 p_proposal_id IN NUMBER,
676 p_record_version_number IN NUMBER,
677 x_return_status OUT NOCOPY VARCHAR2,
678 x_msg_count OUT NOCOPY NUMBER,
679 x_msg_data OUT NOCOPY VARCHAR2
680 ) IS
681
682 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Prop_Person';
683
684 l_return_status VARCHAR2(1);
685
686 BEGIN
687
688 /*
689 ** Establish Savepoint for Rollback
690 */
691
692 SAVEPOINT Delete_Prop_Spl_Reviews_Pvt;
693
694
695 /*
696 ** Initialize
697 */
698
699 x_return_status := Fnd_Api.G_Ret_Sts_Success;
700
701 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
702
703 Fnd_Msg_Pub.Initialize;
704
705 END IF;
706
707 /*
708 ** Check Modify Rights
709 */
710
711
712 /*
713 IF Igw_Security.Allow_Modify
714 (
715 p_function_name => 'PROPOSAL',
716 p_proposal_id => p_proposal_id,
717 p_user_id => Fnd_Global.User_Id
718 )
719 = 'N' THEN
720
721 x_return_status := Fnd_Api.G_Ret_Sts_Error;
722 Fnd_Message.Set_Name('IGW','IGW_SS_SEC_NO_MODIFY_RIGHTS');
723 Fnd_Msg_Pub.Add;
724 RAISE Fnd_Api.G_Exc_Error;
725
726 END IF;
727 */
728
729 /*
730 ** Check Lock before processing
731 */
732
733 Check_Lock
734 (
735 p_rowid => p_rowid,
736 p_record_version_number => p_record_version_number,
737 x_return_status => l_return_status
738 );
739
740 /*
741 ** Discontinue processing if any error has been encountered during
742 ** the earlier stages
743 */
744
745 IF Fnd_Msg_Pub.Count_Msg > 0 THEN
746
747 RAISE Fnd_Api.G_Exc_Error;
748
749 END IF;
750
751
752 /*
753 ** Discontinue processing if API invoked in validation mode
754 */
755
756 IF Fnd_Api.To_Boolean(p_validate_only) THEN
757
758 RETURN;
759
760 END IF;
761
762 /*
763 ** Invoke Table Handler to Delete data
764 */
765
766 Igw_Prop_Special_Reviews_Tbh.Delete_Row
767 (
768 p_rowid => p_rowid,
769 p_record_version_number => p_record_version_number,
770 x_return_status => x_return_status
771 );
772
773
774 /*
775 ** Commit data if API invoked in commit mode
776 */
777
778 IF Fnd_Api.To_Boolean(p_commit) THEN
779
780 COMMIT;
781
782 END IF;
783
784
785 EXCEPTION
786
787 WHEN Fnd_Api.G_Exc_Error THEN
788
789 ROLLBACK TO Delete_Prop_Spl_Reviews_Pvt;
790
791 x_return_status := Fnd_Api.G_Ret_Sts_Error;
792
793 Fnd_Msg_Pub.Count_And_Get
794 (
795 p_count => x_msg_count,
796 p_data => x_msg_data
797 );
798
799 WHEN Fnd_Api.G_Exc_Unexpected_Error THEN
800
801 ROLLBACK TO Delete_Prop_Spl_Reviews_Pvt;
802
803 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
804
805 Fnd_Msg_Pub.Count_And_Get
806 (
807 p_count => x_msg_count,
808 p_data => x_msg_data
809 );
810
811 WHEN others THEN
812
813 ROLLBACK TO Delete_Prop_Spl_Reviews_Pvt;
814
815 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
816
817 Fnd_Msg_Pub.Add_Exc_Msg
818 (
819 p_pkg_name => G_PKG_NAME,
820 p_procedure_name => l_api_name
821 );
822
823 Fnd_Msg_Pub.Count_And_Get
824 (
825 p_count => x_msg_count,
826 p_data => x_msg_data
827 );
828
829 END Delete_Prop_Special_Reviews;
830
831 ---------------------------------------------------------------------------
832
833 END Igw_Prop_Special_Reviews_Pvt;