[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_COMMENTS_TBH
Source
1 PACKAGE BODY Igw_Prop_Comments_Tbh AS
2 /* $Header: igwtcomb.pls 115.5 2002/11/15 00:36:42 ashkumar ship $ */
3
4 ---------------------------------------------------------------------------
5
6 G_PKG_NAME VARCHAR2(30) := 'IGW_PROP_COMMENTS_TBH';
7
8 ---------------------------------------------------------------------------
9
10 PROCEDURE Insert_Row
11 (
12 x_rowid OUT NOCOPY VARCHAR2,
13 p_proposal_id IN NUMBER,
14 p_comments IN VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2,
16 p_mode IN VARCHAR2
17 ) IS
18
19 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
20
21 l_last_update_date DATE := SYSDATE;
22 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
23 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
24
25 CURSOR c IS
26 SELECT rowid
27 FROM igw_prop_comments
28 WHERE proposal_id = p_proposal_id AND
29 comments = p_comments AND
30 trunc(last_update_date) = trunc(SYSDATE);
31
32 BEGIN
33
34 x_return_status := Fnd_Api.G_Ret_Sts_Success;
35
36 IF p_mode = 'I' THEN
37
38 l_last_updated_by := 1;
39 l_last_update_login := 0;
40
41 ELSIF p_mode <> 'R' THEN
42
43 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
44 App_Exception.Raise_Exception;
45
46 END IF;
47
48 INSERT INTO igw_prop_comments
49 (
50 proposal_id,
51 comment_id,
52 comments,
53 record_version_number,
54 creation_date,
55 created_by,
56 last_update_date,
57 last_updated_by,
58 last_update_login
59 )
60 VALUES
61 (
62 p_proposal_id, /* proposal_id */
63 igw_prop_comments_s.nextval, /* comment_id */
64 p_comments, /* comments */
65 1, /* record_version_number */
66 l_last_update_date, /* creation_date */
67 l_last_updated_by, /* created_by */
68 l_last_update_date, /* last_update_date */
69 l_last_updated_by, /* last_updated_by */
70 l_last_update_login /* last_update_login */
71 );
72
73 OPEN c;
74 FETCH c INTO x_rowid;
75
76 IF c%NotFound THEN
77
78 CLOSE c;
79 RAISE no_data_found;
80
81 END IF;
82
83 CLOSE c;
84
85 EXCEPTION
86
87 WHEN others THEN
88
89 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
90
91 Fnd_Msg_Pub.Add_Exc_Msg
92 (
93 p_pkg_name => G_PKG_NAME,
94 p_procedure_name => l_api_name
95 );
96
97 RAISE Fnd_Api.G_Exc_Unexpected_Error;
98
99 END Insert_Row;
100
101 ---------------------------------------------------------------------------
102
103 PROCEDURE Update_Row
104 (
105 p_rowid IN VARCHAR2,
106 p_proposal_id IN NUMBER,
107 p_comment_id IN NUMBER,
108 p_comments IN VARCHAR2,
109 p_record_version_number IN NUMBER,
110 x_return_status OUT NOCOPY VARCHAR2,
111 p_mode IN VARCHAR2
112 ) IS
113
114 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
115
116 l_last_update_date DATE := SYSDATE;
117 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
118 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
119
120 BEGIN
121
122 x_return_status := Fnd_Api.G_Ret_Sts_Success;
123
124 IF p_mode = 'I' THEN
125
126 l_last_updated_by := 1;
127 l_last_update_login := 0;
128
129 ELSIF p_mode <> 'R' THEN
130
131 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
132 Fnd_Msg_Pub.Add;
133 App_Exception.Raise_Exception;
134
135 END IF;
136
137 UPDATE igw_prop_comments
138 SET comments = p_comments,
139 record_version_number = record_version_number + 1,
140 last_update_date = l_last_update_date,
141 last_updated_by = l_last_updated_by,
142 last_update_login = l_last_update_login
143 WHERE ((rowid = p_rowid) OR
144 (proposal_id = p_proposal_id AND comment_id = p_comment_id))
145 AND record_version_number = p_record_version_number;
146
147 IF SQL%NotFound THEN
148
149 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
150 Fnd_Msg_Pub.Add;
151 App_Exception.Raise_Exception;
152
153 END IF;
154
155 EXCEPTION
156
157 WHEN others THEN
158
159 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
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 Update_Row;
170
171 ---------------------------------------------------------------------------
172
173 PROCEDURE Delete_Row
174 (
175 p_rowid IN VARCHAR2,
176 p_record_version_number IN NUMBER,
177 x_return_status OUT NOCOPY VARCHAR2
178 ) IS
179
180 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
181
182 BEGIN
183
184 x_return_status := Fnd_Api.G_Ret_Sts_Success;
185
186 DELETE igw_prop_comments
187 WHERE rowid = p_rowid
188 AND record_version_number = p_record_version_number;
189
190 IF SQL%NotFound THEN
191
192 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
193 Fnd_Msg_Pub.Add;
194 App_Exception.Raise_Exception;
195
196 END IF;
197
198 EXCEPTION
199
200 WHEN others THEN
201
202 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
203
204 Fnd_Msg_Pub.Add_Exc_Msg
205 (
206 p_pkg_name => G_PKG_NAME,
207 p_procedure_name => l_api_name
208 );
209
210 RAISE Fnd_Api.G_Exc_Unexpected_Error;
211
212 END Delete_Row;
213
214 ---------------------------------------------------------------------------
215
216 END Igw_Prop_Comments_Tbh;