[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_NARRATIVES_TBH
Source
1 package body IGW_PROP_NARRATIVES_TBH as
2 /* $Header: igwtprnb.pls 115.4 2002/11/15 00:39:05 ashkumar ship $*/
3
4 PROCEDURE INSERT_ROW (
5 X_ROWID OUT NOCOPY VARCHAR2,
6 P_PROPOSAL_ID in NUMBER,
7 P_MODULE_TITLE in VARCHAR2,
8 P_MODULE_STATUS in VARCHAR2,
9 P_CONTACT_NAME in VARCHAR2,
10 P_PHONE_NUMBER in VARCHAR2,
11 P_EMAIL_ADDRESS in VARCHAR2,
12 P_COMMENTS in VARCHAR2,
13 P_MODE IN VARCHAR2,
14 X_RETURN_STATUS OUT NOCOPY VARCHAR2) is
15
16 L_MODULE_ID NUMBER;
17
18 cursor c is select ROWID from IGW_PROP_NARRATIVES
19 where proposal_id = p_proposal_id
20 and module_id = l_module_id;
21
22 l_last_update_date DATE;
23 l_last_updated_by NUMBER;
24 l_last_update_login NUMBER;
25
26
27 BEGIN
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29
30 l_last_update_date := SYSDATE;
31
32 if(p_mode = 'I') then
33 l_last_updated_by := 1;
34 l_last_update_login := 0;
35 elsif (p_mode = 'R') then
36 l_last_updated_by := FND_GLOBAL.USER_ID;
37
38 if l_last_updated_by is NULL then
39 l_last_updated_by := -1;
40 end if;
41
42 l_last_update_login := FND_GLOBAL.LOGIN_ID;
43
44 if l_last_update_login is NULL then
45 l_last_update_login := -1;
46 end if;
47 else
48 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
49 app_exception.raise_exception;
50 end if;
51
52
53 insert into igw_prop_narratives (
54 PROPOSAL_ID
55 ,MODULE_ID
56 ,MODULE_TITLE
57 ,MODULE_STATUS
58 ,CONTACT_NAME
59 ,PHONE_NUMBER
60 ,EMAIL_ADDRESS
61 ,COMMENTS
62 ,LAST_UPDATE_DATE
63 ,LAST_UPDATED_BY
64 ,CREATION_DATE
65 ,CREATED_BY
66 ,LAST_UPDATE_LOGIN
67 ,RECORD_VERSION_NUMBER
68 ) values (
69 P_PROPOSAL_ID
70 ,IGW_PROP_NARRATIVES_S.NEXTVAL
71 ,P_MODULE_TITLE
72 ,P_MODULE_STATUS
73 ,P_CONTACT_NAME
74 ,P_PHONE_NUMBER
75 ,P_EMAIL_ADDRESS
76 ,P_COMMENTS
77 ,l_last_update_date
78 ,l_last_updated_by
79 ,l_last_update_date
80 ,l_last_updated_by
81 ,l_last_update_login
82 ,1
83 )
84 RETURNING MODULE_ID INTO L_MODULE_ID;
85
86 open c;
87 fetch c into x_rowid;
88 if (c%notfound) then
89 close c;
90 raise no_data_found;
91 end if;
92 close c;
93
94 EXCEPTION
95 when others then
96 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_NARRATIVES_TBH',
97 p_procedure_name => 'INSERT_ROW',
98 p_error_text => SUBSTRB(SQLERRM, 1, 240));
99 x_return_status := fnd_api.g_ret_sts_unexp_error;
100 raise;
101
102 END INSERT_ROW;
103 ----------------------------------------------------------------------------------------------------
104
105 PROCEDURE UPDATE_ROW (
106 X_ROWID IN VARCHAR2,
107 P_PROPOSAL_ID in NUMBER,
108 P_MODULE_ID in NUMBER,
109 P_MODULE_TITLE in VARCHAR2,
110 P_MODULE_STATUS in VARCHAR2,
111 P_CONTACT_NAME in VARCHAR2,
112 P_PHONE_NUMBER in VARCHAR2,
113 P_EMAIL_ADDRESS in VARCHAR2,
114 P_COMMENTS in VARCHAR2,
115 P_MODE IN VARCHAR2,
116 P_RECORD_VERSION_NUMBER IN NUMBER,
117 X_RETURN_STATUS OUT NOCOPY VARCHAR2
118 ) is
119
120 l_last_update_date DATE;
121 l_last_updated_by NUMBER;
122 l_last_update_login NUMBER;
123
124 BEGIN
125 x_return_status := fnd_api.g_ret_sts_success;
126
127
128 l_last_update_date := SYSDATE;
129 if(p_mode = 'I') then
130 l_last_updated_by := 1;
131 l_last_update_login := 0;
132 elsif (p_mode = 'R') then
133 l_last_updated_by := FND_GLOBAL.USER_ID;
134
135 if l_last_updated_by is NULL then
136 l_last_updated_by := -1;
137 end if;
138
139 l_last_update_login := FND_GLOBAL.LOGIN_ID;
140
141 if l_last_update_login is NULL then
142 l_last_update_login := -1;
143 end if;
144 else
145 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
146 app_exception.raise_exception;
147 end if;
148
149 update IGW_PROP_NARRATIVES set
150 PROPOSAL_ID = P_PROPOSAL_ID
151 ,MODULE_ID = P_MODULE_ID
152 ,MODULE_TITLE = P_MODULE_TITLE
153 ,MODULE_STATUS = P_MODULE_STATUS
154 ,CONTACT_NAME = P_CONTACT_NAME
155 ,PHONE_NUMBER = P_PHONE_NUMBER
156 ,EMAIL_ADDRESS = P_EMAIL_ADDRESS
157 ,COMMENTS = P_COMMENTS
158 ,last_update_date = l_last_update_date
159 ,last_updated_by = l_last_updated_by
160 ,last_update_login = l_last_update_login
161 ,record_version_number = record_version_number + 1
162 where rowid = x_rowid
163 and record_version_number = p_record_version_number;
164
165 if (sql%notfound) then
166 fnd_message.set_name('IGW', 'IGW_SS_RECORD_CHANGED');
167 fnd_msg_pub.Add;
168 x_return_status := 'E';
169 end if;
170
171 EXCEPTION
172 when others then
173 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_NARRATIVES_TBH',
174 p_procedure_name => 'UPDATE_ROW',
175 p_error_text => SUBSTRB(SQLERRM, 1, 240));
176 x_return_status := fnd_api.g_ret_sts_unexp_error;
177 raise;
178
179 END UPDATE_ROW;
180
181 ----------------------------------------------------------------------------------------------------
182
183 PROCEDURE DELETE_ROW (
184 x_rowid in VARCHAR2,
185 p_record_version_number in NUMBER,
186 x_return_status out NOCOPY VARCHAR2
187 ) is
188
189 BEGIN
190 x_return_status := fnd_api.g_ret_sts_success;
191
192 delete from IGW_PROP_NARRATIVES
193 where rowid = x_rowid
194 and record_version_number = p_record_version_number;
195
196 if (sql%notfound) then
197 fnd_message.set_name('IGW', 'IGW_SS_RECORD_CHANGED');
198 fnd_msg_pub.Add;
199 x_return_status := 'E';
200 end if;
201
202
203 EXCEPTION
204 when others then
205 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_NARRATIVES_TBH',
206 p_procedure_name => 'DELETE_ROW',
207 p_error_text => SUBSTRB(SQLERRM, 1, 240));
208 x_return_status := fnd_api.g_ret_sts_unexp_error;
209 raise;
210
211 END DELETE_ROW;
212
213 ------------------------------------------------------------------------------------------------------
214
215
216 /* procedure ADD_ROW (
217 X_ROWID in out NOCOPY VARCHAR2,
218 X_RULE_ID in NUMBER,
219 X_RULE_SEQUENCE_NUMBER in NUMBER,
220 X_ORGANIZATION_ID in NUMBER,
221 X_RULE_NAME in VARCHAR2,
222 X_RULE_TYPE in VARCHAR2,
223 X_MAP_ID in NUMBER,
224 X_VALID_FLAG in VARCHAR2,
225 X_START_DATE_ACTIVE in DATE,
226 X_END_DATE_ACTIVE in DATE,
227 X_MODE in VARCHAR2
228 ) is
229 cursor c1 is select rowid from IGW_BUSINESS_RULES_ALL
230 where RULE_ID = X_RULE_ID
231 ;
232 dummy c1%rowtype;
233 begin
234 open c1;
235 fetch c1 into dummy;
236 if (c1%notfound) then
237 close c1;
238 INSERT_ROW (
239 X_ROWID,
240 X_RULE_ID,
241 X_RULE_SEQUENCE_NUMBER,
242 X_ORGANIZATION_ID,
243 X_RULE_NAME,
244 X_RULE_TYPE,
245 X_MAP_ID,
246 X_VALID_FLAG,
247 X_START_DATE_ACTIVE,
248 X_END_DATE_ACTIVE,
249 X_MODE);
250 return;
251 end if;
252 close c1;
253 UPDATE_ROW (
254 X_RULE_ID,
255 X_RULE_SEQUENCE_NUMBER,
256 X_ORGANIZATION_ID,
257 X_RULE_NAME,
258 X_RULE_TYPE,
259 X_MAP_ID,
260 X_VALID_FLAG,
261 X_START_DATE_ACTIVE,
262 X_END_DATE_ACTIVE,
263 X_MODE);
264 end ADD_ROW; */
265
266
267 /* ---------------------- WILL NOT BE USED IN SELF SERVICE -----------------------------------------
268 procedure LOCK_ROW (
269 X_ROWID in VARCHAR2,
270 X_RULE_ID in NUMBER,
271 X_RULE_SEQUENCE_NUMBER in NUMBER,
272 X_ORGANIZATION_ID in NUMBER,
273 X_RULE_NAME in VARCHAR2,
274 X_RULE_TYPE in VARCHAR2,
275 X_MAP_ID in NUMBER,
276 X_VALID_FLAG in VARCHAR2,
277 X_START_DATE_ACTIVE in DATE,
278 X_END_DATE_ACTIVE in DATE
279 ) is
280 cursor c1 is select *
281 from IGW_BUSINESS_RULES_ALL
282 where ROWID = X_ROWID
283 for update of RULE_ID nowait;
284 tlinfo c1%rowtype;
285
286 begin
287 open c1;
288 fetch c1 into tlinfo;
289 if (c1%notfound) then
290 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
291 app_exception.raise_exception;
292 close c1;
293 return;
294 end if;
295 close c1;
296
297 if (
298 (tlinfo.RULE_ID = X_RULE_ID)
299 AND ((tlinfo.RULE_SEQUENCE_NUMBER = X_RULE_SEQUENCE_NUMBER)
300 OR ((tlinfo.RULE_SEQUENCE_NUMBER is null)
301 AND (X_RULE_SEQUENCE_NUMBER is null)))
302 AND (tlinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
303 AND (tlinfo.RULE_NAME = X_RULE_NAME)
304 AND (tlinfo.RULE_TYPE = X_RULE_TYPE)
305 AND ((tlinfo.MAP_ID = X_MAP_ID)
306 OR ((tlinfo.MAP_ID is null)
307 AND (X_MAP_ID is null)))
308 AND ((tlinfo.VALID_FLAG = X_VALID_FLAG)
309 OR ((tlinfo.VALID_FLAG is null)
310 AND (X_VALID_FLAG is null)))
311 AND (tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
312 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
313 OR ((tlinfo.END_DATE_ACTIVE is null)
314 AND (X_END_DATE_ACTIVE is null)))
315 ) then
316 null;
317 else
318 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
319 app_exception.raise_exception;
320 end if;
321 return;
322 end LOCK_ROW;
323 */
324
325 END IGW_PROP_NARRATIVES_TBH;