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