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