[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_NARRATIVES_PVT
Source
1 PACKAGE BODY IGW_PROP_NARRATIVES_PVT as
2 /* $Header: igwvprnb.pls 115.6 2002/11/15 00:38:17 ashkumar ship $*/
3 PROCEDURE create_prop_narrative (
4 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5 p_commit IN VARCHAR2 := FND_API.G_FALSE,
6 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
7 X_ROWID out NOCOPY VARCHAR2,
8 P_PROPOSAL_ID in NUMBER,
9 P_MODULE_TITLE in VARCHAR2,
10 P_MODULE_STATUS in VARCHAR2,
11 P_CONTACT_NAME in VARCHAR2,
12 P_PHONE_NUMBER in VARCHAR2,
13 P_EMAIL_ADDRESS in VARCHAR2,
14 P_COMMENTS in VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2)
18
19 is
20
21 STATUS_OF_NARRATIVES VARCHAR2(1);
22
23 l_return_status VARCHAR2(1);
24 l_error_msg_code VARCHAR2(250);
25 l_msg_count NUMBER;
26 l_msg_data VARCHAR2(250);
27 l_data VARCHAR2(250);
28 l_msg_index_out NUMBER;
29
30
31
32 BEGIN
33 -- create savepoint if p_commit is true
34 IF p_commit = FND_API.G_TRUE THEN
35 SAVEPOINT create_prop_narrative;
36 END IF;
37
38 -- initialize message list if p_init_msg_list is set to true
39 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
40 fnd_msg_pub.initialize;
41 end if;
42
43 -- initialize return status to success
44 x_return_status := fnd_api.g_ret_sts_success;
45
46 -- first validate user rights
47
48 VALIDATE_LOGGED_USER_RIGHTS
49 (p_proposal_id => p_proposal_id
50 ,x_return_status => x_return_status);
51
52 check_errors;
53
54 ------------------------------------- value_id conversion ------------------------------------
55
56 -------------------------------------------- validations -----------------------------------------------------
57
58 -- call table handler
59 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
60
61 igw_prop_narratives_tbh.insert_row(
62 x_rowid => x_rowid
63 ,P_PROPOSAL_ID => P_PROPOSAL_ID
64 ,P_MODULE_TITLE => P_MODULE_TITLE
65 ,P_MODULE_STATUS => P_MODULE_STATUS
66 ,P_CONTACT_NAME => P_CONTACT_NAME
67 ,P_PHONE_NUMBER => P_PHONE_NUMBER
68 ,P_EMAIL_ADDRESS => P_EMAIL_ADDRESS
69 ,P_COMMENTS => P_COMMENTS
70 ,p_mode => 'R'
71 ,x_return_status => x_return_status);
72 STATUS_OF_NARRATIVES := IGW_PROP.GET_NARRATIVE_STATUS (P_PROPOSAL_ID);
73 IGW_PROP.SET_COMPONENT_STATUS ('NARRATIVE', P_PROPOSAL_ID, STATUS_OF_NARRATIVES);
74
75 end if;
76
77 check_errors;
78
79 -- standard check of p_commit
80 if fnd_api.to_boolean(p_commit) then
81 commit work;
82 end if;
83
84
85 -- standard call to get message count and if count is 1, get message info
86 fnd_msg_pub.count_and_get(p_count => x_msg_count,
87 p_data => x_msg_data);
88
89
90 EXCEPTION
91 WHEN FND_API.G_EXC_ERROR THEN
92 IF p_commit = FND_API.G_TRUE THEN
93 ROLLBACK TO create_prop_narrative;
94 END IF;
95
96 x_return_status := FND_API.G_RET_STS_ERROR;
97
98 fnd_msg_pub.count_and_get(p_count => x_msg_count,
99 p_data => x_msg_data);
100
101 WHEN OTHERS THEN
102 IF p_commit = FND_API.G_TRUE THEN
103 ROLLBACK TO create_prop_narrative;
104 END IF;
105
106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
107
108 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_NARRATIVES_PVT',
109 p_procedure_name => 'CREATE_PROP_NARRATIVE',
110 p_error_text => SUBSTRB(SQLERRM,1,240));
111
112 fnd_msg_pub.count_and_get(p_count => x_msg_count,
113 p_data => x_msg_data);
114
115
116 END create_prop_narrative;
117
118 --------------------------------------------------------------------------------------------------------------
119
120 Procedure update_prop_narrative (
121 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
122 p_commit IN VARCHAR2 := FND_API.G_FALSE,
123 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
124 x_rowid IN VARCHAR2,
125 P_PROPOSAL_ID in NUMBER,
126 P_MODULE_ID in NUMBER,
127 P_MODULE_TITLE in VARCHAR2,
128 P_MODULE_STATUS in VARCHAR2,
129 P_CONTACT_NAME in VARCHAR2,
130 P_PHONE_NUMBER in VARCHAR2,
131 P_EMAIL_ADDRESS in VARCHAR2,
132 P_COMMENTS in VARCHAR2,
133 p_record_version_number IN NUMBER,
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER,
136 x_msg_data OUT NOCOPY VARCHAR2) is
137
138 STATUS_OF_NARRATIVES VARCHAR2(1);
139
140 l_return_status VARCHAR2(1);
141 l_error_msg_code VARCHAR2(250);
142 l_msg_count NUMBER;
143 l_data VARCHAR2(250);
144 l_msg_data VARCHAR2(250);
145 l_msg_index_out NUMBER;
146
147 BEGIN
148 -- create savepoint if p_commit is true
149 IF p_commit = FND_API.G_TRUE THEN
150 SAVEPOINT update_prop_narrative;
151 END IF;
152
153 -- initialize message list if p_init_msg_list is true
154 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
155 fnd_msg_pub.initialize;
156 end if;
157
158 -- initialize return_status to success
159 x_return_status := fnd_api.g_ret_sts_success;
160
161
162 -- first validate user rights
163
164 VALIDATE_LOGGED_USER_RIGHTS
165 (p_proposal_id => p_proposal_id
166 ,x_return_status => x_return_status);
167
168 check_errors;
169
170 -- and also check locking.
171 CHECK_LOCK
172 (x_rowid => x_rowid
173 ,p_record_version_number => p_record_version_number
174 ,x_return_status => x_return_status);
175
176 check_errors;
177
178 ------------------------------------- value_id conversion ---------------------------------
179
180 -------------------------------------------- validations -----------------------------------------------------
181
182 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
183 igw_prop_narratives_tbh.update_row (
184 x_rowid => x_rowid
185 ,P_PROPOSAL_ID => P_PROPOSAL_ID
186 ,P_MODULE_ID => P_MODULE_ID
187 ,P_MODULE_TITLE => P_MODULE_TITLE
188 ,P_MODULE_STATUS => P_MODULE_STATUS
189 ,P_CONTACT_NAME => P_CONTACT_NAME
190 ,P_PHONE_NUMBER => P_PHONE_NUMBER
191 ,P_EMAIL_ADDRESS => P_EMAIL_ADDRESS
192 ,P_COMMENTS => P_COMMENTS
193 ,p_mode => 'R'
194 ,p_record_version_number => p_record_version_number
195 ,x_return_status => x_return_status);
196 STATUS_OF_NARRATIVES := IGW_PROP.GET_NARRATIVE_STATUS (P_PROPOSAL_ID);
197 IGW_PROP.SET_COMPONENT_STATUS ('NARRATIVE', P_PROPOSAL_ID, STATUS_OF_NARRATIVES);
198
199 end if;
200
201 check_errors;
202
203 -- standard check of p_commit
204 if fnd_api.to_boolean(p_commit) then
205 commit work;
206 end if;
207
208
209 -- standard call to get message count and if count is 1, get message info
210 fnd_msg_pub.count_and_get(p_count => x_msg_count,
211 p_data => x_msg_data);
212
213
214 EXCEPTION
215 WHEN FND_API.G_EXC_ERROR THEN
216 IF p_commit = FND_API.G_TRUE THEN
217 ROLLBACK TO update_prop_narrative;
218 END IF;
219
220 x_return_status := FND_API.G_RET_STS_ERROR;
221
222 fnd_msg_pub.count_and_get(p_count => x_msg_count,
223 p_data => x_msg_data);
224
225
226 WHEN OTHERS THEN
227 IF p_commit = FND_API.G_TRUE THEN
228 ROLLBACK TO update_prop_narrative;
229 END IF;
230
231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
232
233 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_NARRATIVES_PVT',
234 p_procedure_name => 'UPDATE_PROP_NARRATIVE',
235 p_error_text => SUBSTRB(SQLERRM,1,240));
236
237 fnd_msg_pub.count_and_get(p_count => x_msg_count,
238 p_data => x_msg_data);
239
240
241 END update_prop_narrative;
242 --------------------------------------------------------------------------------------------------------
243
244 Procedure delete_prop_narrative (
245 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
246 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
247 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
248 ,x_rowid IN VARCHAR2
249 ,p_proposal_id IN NUMBER
250 ,p_record_version_number IN NUMBER
251 ,x_return_status OUT NOCOPY VARCHAR2
252 ,x_msg_count OUT NOCOPY NUMBER
253 ,x_msg_data OUT NOCOPY VARCHAR2) is
254
255 STATUS_OF_NARRATIVES VARCHAR2(1);
256 l_return_status VARCHAR2(1);
257 l_error_msg_code VARCHAR2(250);
258 l_msg_count NUMBER;
259 l_data VARCHAR2(250);
260 l_performing_org_id NUMBER;
261 l_msg_data VARCHAR2(250);
262 l_msg_index_out NUMBER;
263 l_module_id NUMBER;
264
265 BEGIN
266 -- create savepoint
267 IF p_commit = FND_API.G_TRUE THEN
268 SAVEPOINT delete_prop_narrative;
269 END IF;
270
271 -- initialize message list if p_init_msg_list is set to true
272 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
273 fnd_msg_pub.initialize;
274 end if;
275
276 -- initialize return_status to sucess
277 x_return_status := fnd_api.g_ret_sts_success;
278
279 -- first validate user rights
280
281 VALIDATE_LOGGED_USER_RIGHTS
282 (p_proposal_id => p_proposal_id
283 ,x_return_status => x_return_status);
284
285 check_errors;
286
287 -- check locking
288 CHECK_LOCK
289 (x_rowid => x_rowid
290 ,p_record_version_number => p_record_version_number
291 ,x_return_status => x_return_status);
292
293 check_errors;
294
295 -------------------------------------------- validations -----------------------------------------------------
296
297
298 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
299
300 select module_id into l_module_id from igw_prop_narratives where rowid = x_rowid;
301
302 igw_prop_narratives_tbh.delete_row(
303 x_rowid => x_rowid,
304 p_record_version_number => p_record_version_number,
305 x_return_status => x_return_status);
306
307
308 FND_ATTACHED_DOCUMENTS2_PKG.DELETE_ATTACHMENTS('IGW_PROP_NARRATIVES',
309 p_proposal_id, l_module_id);
310
311 STATUS_OF_NARRATIVES := IGW_PROP.GET_NARRATIVE_STATUS (P_PROPOSAL_ID);
312 IGW_PROP.SET_COMPONENT_STATUS ('NARRATIVE', P_PROPOSAL_ID, STATUS_OF_NARRATIVES);
313 end if;
314
315
316 check_errors;
317
318 -- standard check of p_commit
319 if fnd_api.to_boolean(p_commit) then
320 commit work;
321 end if;
322
323
324 -- standard call to get message count and if count is 1, get message info
325 fnd_msg_pub.count_and_get(p_count => x_msg_count,
326 p_data => x_msg_data);
327
328 EXCEPTION
329 WHEN FND_API.G_EXC_ERROR THEN
330 IF p_commit = FND_API.G_TRUE THEN
331 ROLLBACK TO delete_prop_narrative;
332 END IF;
333
334 x_return_status := FND_API.G_RET_STS_ERROR;
335
336 fnd_msg_pub.count_and_get(p_count => x_msg_count,
337 p_data => x_msg_data);
338
339
340 WHEN OTHERS THEN
341 IF p_commit = FND_API.G_TRUE THEN
342 ROLLBACK TO delete_prop_narrative;
343 END IF;
344
345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
346
347 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_NARRATIVES_PVT',
348 p_procedure_name => 'DELETE_PROP_NARRATIVE',
349 p_error_text => SUBSTRB(SQLERRM,1,240));
350
351 fnd_msg_pub.count_and_get(p_count => x_msg_count,
352 p_data => x_msg_data);
353
354 END delete_prop_narrative;
355
356 ------------------------------------------------------------------------------------------
357 Procedure update_narrative_type_code (
358 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
359 p_commit IN VARCHAR2 := FND_API.G_FALSE,
360 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
361 P_PROPOSAL_ID in NUMBER,
362 P_NARRATIVE_TYPE_CODE in VARCHAR2,
363 P_NARRATIVE_SUBMISSION_CODE in VARCHAR2,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_count OUT NOCOPY NUMBER,
366 x_msg_data OUT NOCOPY VARCHAR2) is
367
368 l_return_status VARCHAR2(1);
369 l_error_msg_code VARCHAR2(250);
370 l_msg_count NUMBER;
371 l_data VARCHAR2(250);
372 l_msg_data VARCHAR2(250);
373 l_msg_index_out NUMBER;
374
375 BEGIN
376 -- create savepoint if p_commit is true
377 IF p_commit = FND_API.G_TRUE THEN
378 SAVEPOINT update_narrative_type_code;
379 END IF;
380
381 -- initialize message list if p_init_msg_list is true
382 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
383 fnd_msg_pub.initialize;
384 end if;
385
386 -- initialize return_status to success
387 x_return_status := fnd_api.g_ret_sts_success;
388
389
390 -- first validate user rights
391
392 VALIDATE_LOGGED_USER_RIGHTS
393 (p_proposal_id => p_proposal_id
394 ,x_return_status => x_return_status);
395
396 check_errors;
397
398 ------------------------------------- value_id conversion ---------------------------------
399
400 -------------------------------------------- validations -----------------------------------------------------
401
402 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
403
404 UPDATE IGW_PROPOSALS_ALL
405 SET NARRATIVE_TYPE_CODE = P_NARRATIVE_TYPE_CODE,
406 NARRATIVE_SUBMISSION_CODE = P_NARRATIVE_SUBMISSION_CODE
407 WHERE PROPOSAL_ID = P_PROPOSAL_ID;
408
409
410 end if;
411
412 check_errors;
413
414 -- standard check of p_commit
415 if fnd_api.to_boolean(p_commit) then
416 commit work;
417 end if;
418
419
420 -- standard call to get message count and if count is 1, get message info
421 fnd_msg_pub.count_and_get(p_count => x_msg_count,
422 p_data => x_msg_data);
423
424
425 EXCEPTION
426 WHEN FND_API.G_EXC_ERROR THEN
427 IF p_commit = FND_API.G_TRUE THEN
428 ROLLBACK TO update_narrative_type_code;
429 END IF;
430
431 x_return_status := FND_API.G_RET_STS_ERROR;
432
433 fnd_msg_pub.count_and_get(p_count => x_msg_count,
434 p_data => x_msg_data);
435
436
437 WHEN OTHERS THEN
438 IF p_commit = FND_API.G_TRUE THEN
439 ROLLBACK TO update_narrative_type_code;
440 END IF;
441
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443
444 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_NARRATIVES_PVT',
445 p_procedure_name => 'UPDATE_PROP_NARRATIVE',
446 p_error_text => SUBSTRB(SQLERRM,1,240));
447
448 fnd_msg_pub.count_and_get(p_count => x_msg_count,
449 p_data => x_msg_data);
450
451
452 END update_narrative_type_code;
453 --------------------------------------------------------------------------------------------------------
454 PROCEDURE CHECK_LOCK
455 (x_rowid IN VARCHAR2
456 ,p_record_version_number IN NUMBER
457 ,x_return_status OUT NOCOPY VARCHAR2) is
458
459 l_proposal_id number;
460 BEGIN
461 select proposal_id
462 into l_proposal_id
463 from igw_prop_narratives
464 where rowid = x_rowid
465 and record_version_number = p_record_version_number;
466
467 EXCEPTION
468 WHEN NO_DATA_FOUND THEN
469 x_return_status := FND_API.G_RET_STS_ERROR;
470 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
471 FND_MSG_PUB.Add;
472 raise fnd_api.g_exc_error;
473
474 WHEN OTHERS THEN
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_NARRATIVES_PVT',
477 p_procedure_name => 'CHECK_LOCK',
478 p_error_text => SUBSTRB(SQLERRM,1,240));
479 raise fnd_api.g_exc_unexpected_error;
480
481 END CHECK_LOCK;
482
483 -------------------------------------------------------------------------------------------------------
484 PROCEDURE CHECK_ERRORS is
485 l_msg_count NUMBER;
486 BEGIN
487 l_msg_count := fnd_msg_pub.count_msg;
488 IF (l_msg_count > 0) THEN
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491
492 END CHECK_ERRORS;
493
494 -------------------------------------------------------------------------------------------------
495
496 PROCEDURE VALIDATE_LOGGED_USER_RIGHTS
497 (p_proposal_id IN NUMBER
498 ,x_return_status OUT NOCOPY VARCHAR2) is
499
500 x VARCHAR2(1);
501 y VARCHAR2(1);
502
503 BEGIN
504 x_return_status:= FND_API.G_RET_STS_SUCCESS;
505
506 IF (IGW_SECURITY.ALLOW_MODIFY ('NARRATIVE', P_PROPOSAL_ID, FND_GLOBAL.USER_ID) = 'N') THEN
507 x_return_status:= FND_API.G_RET_STS_ERROR;
508 fnd_message.set_name('IGW', 'IGW_NO_RIGHTS');
509 fnd_msg_pub.add;
510 END IF;
511
512 EXCEPTION
513
514 WHEN OTHERS THEN
515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
516 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_NARRATIVES_PVT',
517 p_procedure_name => 'VALIDATE_LOGGED_USER_RIGHTS',
518 p_error_text => SUBSTRB(SQLERRM,1,240));
519 raise fnd_api.g_exc_unexpected_error;
520 END VALIDATE_LOGGED_USER_RIGHTS;
521
522
523 END IGW_PROP_NARRATIVES_PVT;