[Home] [Help]
PACKAGE BODY: APPS.IGW_SPONSOR_ACTION_TBH
Source
1 PACKAGE BODY IGW_SPONSOR_ACTION_TBH AS
2 /* $Header: igwtspab.pls 115.4 2003/02/19 00:35:21 ashkumar noship $ */
3
4 ---------------------------------------------------------------------------
5
6 G_PKG_NAME VARCHAR2(30) := 'IGW_SPONSOR_ACTION_TBH';
7
8 ---------------------------------------------------------------------------
9
10 PROCEDURE Update_Prop_Sponsor_Action(p_proposal_id NUMBER) IS
11
12 CURSOR cur_latest_action IS
13 SELECT sponsor_action_code,
14 sponsor_action_date,
15 comments
16 FROM igw_prop_comments
17 WHERE proposal_id = p_proposal_id
18 AND sponsor_action_code IS NOT NULL
19 ORDER BY sponsor_action_date desc, creation_date desc;
20
21 l_sponsor_action_code Igw_Proposals_All.sponsor_action_code%type;
22 l_sponsor_action_date Igw_Proposals_All.sponsor_action_date%type;
23 l_comments Igw_Proposals_All.sponsor_action_comments%type;
24
25 BEGIN
26
27 OPEN cur_latest_action;
28 FETCH cur_latest_action INTO l_sponsor_action_code, l_sponsor_action_date, l_comments;
29
30 IF cur_latest_action%FOUND THEN
31
32 UPDATE igw_proposals_all
33 SET sponsor_action_code = l_sponsor_action_code,
34 sponsor_action_date = l_sponsor_action_date,
35 sponsor_action_comments = l_comments
36 WHERE proposal_id = p_proposal_id;
37
38 END IF;
39
40 CLOSE cur_latest_action;
41
42 END;
43
44 ---------------------------------------------------------------------------
45
46 PROCEDURE Insert_Row
47 (
48 x_rowid OUT NOCOPY VARCHAR2,
49 p_proposal_id IN NUMBER,
50 p_comments IN VARCHAR2,
51 p_sponsor_action_code in varchar2,
52 p_sponsor_action_date in date,
53 x_return_status OUT NOCOPY VARCHAR2,
54 p_mode IN VARCHAR2 default 'R'
55 ) IS
56
57 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
58
59 l_last_update_date DATE := SYSDATE;
60 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
61 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
62
63 CURSOR c IS
64 SELECT rowid
65 FROM igw_prop_comments
66 WHERE proposal_id = p_proposal_id AND
67 trunc(last_update_date) = trunc(SYSDATE);
68
69 BEGIN
70
71 x_return_status := Fnd_Api.G_Ret_Sts_Success;
72
73 IF p_mode = 'I' THEN
74
75 l_last_updated_by := 1;
76 l_last_update_login := 0;
77
78 ELSIF p_mode <> 'R' THEN
79
80 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
81 App_Exception.Raise_Exception;
82
83 END IF;
84
85 INSERT INTO igw_prop_comments
86 (
87 proposal_id,
88 comment_id,
89 comments,
90 sponsor_action_code,
91 sponsor_action_date,
92 record_version_number,
93 creation_date,
94 created_by,
95 last_update_date,
96 last_updated_by,
97 last_update_login
98 )
99 VALUES
100 (
101 p_proposal_id, /* proposal_id */
102 igw_prop_comments_s.nextval, /* comment_id */
103 p_comments, /* comments */
104 p_sponsor_action_code,
105 p_sponsor_action_date,
106 1, /* record_version_number */
107 l_last_update_date, /* creation_date */
108 l_last_updated_by, /* created_by */
109 l_last_update_date, /* last_update_date */
110 l_last_updated_by, /* last_updated_by */
111 l_last_update_login /* last_update_login */
112 );
113
114 OPEN c;
115 FETCH c INTO x_rowid;
116
117 IF c%NotFound THEN
118
119 CLOSE c;
120 RAISE no_data_found;
121
122 END IF;
123
124 CLOSE c;
125
126 Update_Prop_Sponsor_Action(p_proposal_id);
127
128 EXCEPTION
129
130 WHEN others THEN
131
132 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
133
134 Fnd_Msg_Pub.Add_Exc_Msg
135 (
136 p_pkg_name => G_PKG_NAME,
137 p_procedure_name => l_api_name
138 );
139
140 RAISE Fnd_Api.G_Exc_Unexpected_Error;
141
142 END Insert_Row;
143
144 ---------------------------------------------------------------------------
145
146 PROCEDURE Update_Row
147 (
148 p_rowid IN VARCHAR2,
149 p_proposal_id IN NUMBER,
150 p_comment_id IN NUMBER,
151 p_comments IN VARCHAR2,
152 p_sponsor_action_code in varchar2,
153 p_sponsor_action_date in date,
154 p_record_version_number IN NUMBER,
155 x_return_status OUT NOCOPY VARCHAR2,
156 p_mode IN VARCHAR2 default 'R'
157 ) IS
158
159 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
160
161 l_last_update_date DATE := SYSDATE;
162 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
163 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
164
165 BEGIN
166
167 x_return_status := Fnd_Api.G_Ret_Sts_Success;
168
169 IF p_mode = 'I' THEN
170
171 l_last_updated_by := 1;
172 l_last_update_login := 0;
173
174 ELSIF p_mode <> 'R' THEN
175
176 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
177 Fnd_Msg_Pub.Add;
178 App_Exception.Raise_Exception;
179
180 END IF;
181
182 UPDATE igw_prop_comments
183 SET sponsor_action_code = p_sponsor_action_code,
184 sponsor_action_date = p_sponsor_action_date,
185 comments = p_comments,
186 record_version_number = record_version_number + 1,
187 last_update_date = l_last_update_date,
188 last_updated_by = l_last_updated_by,
189 last_update_login = l_last_update_login
190 WHERE ((rowid = p_rowid) OR
191 (proposal_id = p_proposal_id AND comment_id = p_comment_id))
192 AND record_version_number = p_record_version_number;
193
194 IF SQL%NotFound THEN
195
196 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
197 Fnd_Msg_Pub.Add;
198 App_Exception.Raise_Exception;
199
200 END IF;
201
202 Update_Prop_Sponsor_Action(p_proposal_id);
203
204 EXCEPTION
205
206 WHEN others THEN
207
208 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
209
210 Fnd_Msg_Pub.Add_Exc_Msg
211 (
212 p_pkg_name => G_PKG_NAME,
213 p_procedure_name => l_api_name
214 );
215
216 RAISE Fnd_Api.G_Exc_Unexpected_Error;
217
218 END Update_Row;
219
220 ---------------------------------------------------------------------------
221
222 PROCEDURE Delete_Row
223 (
224 p_rowid IN VARCHAR2,
225 p_proposal_id IN NUMBER,
226 p_record_version_number IN NUMBER,
227 x_return_status OUT NOCOPY VARCHAR2
228 ) IS
229
230 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
231
232 BEGIN
233
234 x_return_status := Fnd_Api.G_Ret_Sts_Success;
235
236 DELETE igw_prop_comments
237 WHERE rowid = p_rowid
238 AND record_version_number = p_record_version_number;
239
240 IF SQL%NotFound THEN
241
242 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
243 Fnd_Msg_Pub.Add;
244 App_Exception.Raise_Exception;
245
246 END IF;
247
248 Update_Prop_Sponsor_Action(p_proposal_id);
249
250 EXCEPTION
251
252 WHEN others THEN
253
254 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
255
256 Fnd_Msg_Pub.Add_Exc_Msg
257 (
258 p_pkg_name => G_PKG_NAME,
259 p_procedure_name => l_api_name
260 );
261
262 RAISE Fnd_Api.G_Exc_Unexpected_Error;
263
264 END Delete_Row;
265
266 ---------------------------------------------------------------------------
267
268 END IGW_SPONSOR_ACTION_TBH;