[Home] [Help]
PACKAGE BODY: APPS.CN_REASONS_PVT
Source
1 PACKAGE BODY cn_reasons_pvt AS
2 -- $Header: cnvresnb.pls 115.2 2002/11/21 21:16:49 hlchen ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- cn_reasons_pvt
11 -- Purpose
12 -- Package Body to display the analyst comments for a payment worksheet.
13 -- History
14 -- 04/02/02 Rao.Chenna Created
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_REASONS_PVT';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpresnb.pls';
17 --
18 /*
19 PROCEDURE my_debug(
20 i_value IN VARCHAR2) IS
21 l_error VARCHAR2(1000);
22 BEGIN
23 INSERT INTO cn_process_audit_lines(
24 process_audit_id,process_audit_line_id,
25 message_type_code,message_text)
26 VALUES(
27 cn_process_audits_s.nextval,cn_process_audit_lines_s1.NEXTVAL,
28 'cnnotes',i_value);
29 COMMIT;
30 EXCEPTION
31 WHEN OTHERS THEN
32 l_error := SQLERRM;
33 INSERT INTO cn_process_audit_lines(
34 process_audit_id,process_audit_line_id,
35 message_type_code,message_text)
36 VALUES(
37 cn_process_audits_s.nextval,cn_process_audit_lines_s1.NEXTVAL,
38 'cnnotes',l_error);
39 COMMIT;
40 END; */
41 --
42 PROCEDURE insert_row(
43 p_api_version IN NUMBER,
44 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
45 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
46 p_commit IN VARCHAR2 := CN_API.G_FALSE,
47 p_reasons_all_rec IN CN_REASONS_PKG.REASONS_ALL_REC_TYPE,
48 x_return_status OUT NOCOPY VARCHAR2,
49 x_msg_count OUT NOCOPY NUMBER,
50 x_msg_data OUT NOCOPY VARCHAR2,
51 x_loading_status OUT NOCOPY VARCHAR2) IS
52 --
53 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
54 l_api_version CONSTANT NUMBER := 1.0;
55 l_reasons_all_rec CN_REASONS_PKG.REASONS_ALL_REC_TYPE;
56 --
57 BEGIN
58 -- Standard Start of API savepoint
59 SAVEPOINT insert_row;
60 -- Standard call to check for call compatibility.
61 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
62 p_api_version ,
63 l_api_name,
64 G_PKG_NAME ) THEN
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END IF;
67 -- Initialize message list if p_init_msg_list is set to TRUE.
68 IF FND_API.to_Boolean( p_init_msg_list ) THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71 -- Initialize API return status to success
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73 x_loading_status := 'CN_INSERTED';
74 -- API body
75 l_reasons_all_rec := p_reasons_all_rec;
76 --
77 BEGIN
78 SELECT cn_reasons_s.NEXTVAL
79 INTO l_reasons_all_rec.reason_id
80 FROM DUAL;
81 EXCEPTION
82 WHEN OTHERS THEN
83 RAISE FND_API.G_EXC_ERROR;
84 END;
85 IF ((l_reasons_all_rec.reason_code = fnd_api.g_miss_char) OR
86 (l_reasons_all_rec.reason = fnd_api.g_miss_char) OR
87 (l_reasons_all_rec.lookup_type = fnd_api.g_miss_char) OR
88 (l_reasons_all_rec.updated_table = fnd_api.g_miss_char) OR
89 (l_reasons_all_rec.upd_table_id = fnd_api.g_miss_num)) THEN
90 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
91 FND_MESSAGE.SET_NAME ('CN' , 'CN_REQUIRED_FIELDS');
92 FND_MSG_PUB.Add;
93 END IF;
94 x_loading_status := 'CN_REQUIRED_FIELDS';
95 RAISE FND_API.G_EXC_ERROR;
96 ELSE
97 cn_reasons_pkg.insert_row(l_reasons_all_rec);
98 END IF;
99 EXCEPTION
100 WHEN FND_API.G_EXC_ERROR THEN
101 ROLLBACK TO insert_row;
102 x_return_status := FND_API.G_RET_STS_ERROR ;
103 FND_MSG_PUB.Count_And_Get(
104 p_count => x_msg_count ,
105 p_data => x_msg_data ,
106 p_encoded => FND_API.G_FALSE);
107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
108 ROLLBACK TO insert_row;
109 x_loading_status := 'UNEXPECTED_ERR';
110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
111 FND_MSG_PUB.Count_And_Get(
112 p_count => x_msg_count ,
113 p_data => x_msg_data ,
114 p_encoded => FND_API.G_FALSE);
115 WHEN OTHERS THEN
116 ROLLBACK TO insert_row;
117 x_loading_status := 'UNEXPECTED_ERR';
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
119 IF FND_MSG_PUB.Check_Msg_Level(
120 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
121 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
122 END IF;
123 FND_MSG_PUB.Count_And_Get(
124 p_count => x_msg_count ,
125 p_data => x_msg_data ,
126 p_encoded => FND_API.G_FALSE);
127 END;
128 --
129 PROCEDURE update_row(
130 p_api_version IN NUMBER,
131 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
132 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
133 p_commit IN VARCHAR2 := CN_API.G_FALSE,
134 p_reasons_all_rec IN CN_REASONS_PKG.REASONS_ALL_REC_TYPE,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2,
138 x_loading_status OUT NOCOPY VARCHAR2) IS
139 --
140 CURSOR reason_cur(l_reason_id NUMBER) IS
141 SELECT *
142 FROM cn_reasons r
143 WHERE r.reason_id = l_reason_id;
144 --
145 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
146 l_api_version CONSTANT NUMBER := 1.0;
147 l_reasons_all_rec CN_REASONS_PKG.REASONS_ALL_REC_TYPE;
148 l_history_rec cn_reason_history_pkg.reason_history_all_rec_type;
149 l_reason VARCHAR2(4000);
150 l_clob_loc CLOB;
151 l_clob_length NUMBER;
152 --
153 BEGIN
154 -- Standard Start of API savepoint
155 SAVEPOINT update_row;
156 -- Standard call to check for call compatibility.
157 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
158 p_api_version ,
159 l_api_name,
160 G_PKG_NAME ) THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 END IF;
163 -- Initialize message list if p_init_msg_list is set to TRUE.
164 IF FND_API.to_Boolean( p_init_msg_list ) THEN
165 FND_MSG_PUB.initialize;
166 END IF;
167 -- Initialize API return status to success
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169 x_loading_status := 'CN_INSERTED';
170 -- API body
171 l_reasons_all_rec := p_reasons_all_rec;
172 --
173 IF ((l_reasons_all_rec.reason_code = fnd_api.g_miss_char) OR
174 (l_reasons_all_rec.reason = fnd_api.g_miss_char) OR
175 (l_reasons_all_rec.lookup_type = fnd_api.g_miss_char) OR
176 (l_reasons_all_rec.updated_table = fnd_api.g_miss_char) OR
177 (l_reasons_all_rec.upd_table_id = fnd_api.g_miss_num)) THEN
178 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
179 FND_MESSAGE.SET_NAME ('CN' , 'CN_REQUIRED_FIELDS');
180 FND_MSG_PUB.Add;
181 END IF;
182 x_loading_status := 'CN_REQUIRED_FIELDS';
183 RAISE FND_API.G_EXC_ERROR;
184 ELSE
185 -- First original record to be moved to CN_REASON_HISTORY table.
186 FOR rec IN reason_cur(l_reasons_all_rec.reason_id)
187 LOOP
188 l_history_rec.reason_id := rec.reason_id;
189 l_history_rec.updated_table := rec.updated_table;
190 l_history_rec.upd_table_id := rec.upd_table_id;
191 l_history_rec.reason_code := rec.reason_code;
192 l_history_rec.dml_flag := 'UPDATE';
193 l_history_rec.lookup_type := rec.lookup_type;
194 l_history_rec.update_flag := rec.lookup_type;
195 l_history_rec.attribute_category := rec.attribute_category;
196 l_history_rec.attribute1 := rec.attribute1;
197 l_history_rec.attribute2 := rec.attribute2;
198 l_history_rec.attribute3 := rec.attribute3;
199 l_history_rec.attribute4 := rec.attribute4;
200 l_history_rec.attribute5 := rec.attribute5;
201 l_history_rec.attribute6 := rec.attribute6;
202 l_history_rec.attribute7 := rec.attribute7;
203 l_history_rec.attribute8 := rec.attribute8;
204 l_history_rec.attribute9 := rec.attribute9;
205 l_history_rec.attribute10 := rec.attribute10;
206 l_history_rec.attribute11 := rec.attribute11;
207 l_history_rec.attribute12 := rec.attribute12;
208 l_history_rec.attribute13 := rec.attribute13;
209 l_history_rec.attribute14 := rec.attribute14;
210 l_history_rec.attribute15 := rec.attribute15;
211 -- Processing CLOB
212 l_clob_loc := rec.reason;
213 l_clob_length := dbms_lob.getlength(l_clob_loc);
214 dbms_lob.read(l_clob_loc,l_clob_length,1,l_history_rec.reason);
215 -- End of CLOB processing
216 -- Get reason_history_id from the SEQUENCE.
217 SELECT cn_reason_history_s.NEXTVAL
218 INTO l_history_rec.reason_history_id
219 FROM dual;
220 --
221 cn_reason_history_pkg.insert_row(l_history_rec);
222 --
223 cn_reasons_pkg.lock_update_row(l_reasons_all_rec);
224 --
225 END LOOP;
226 END IF;
227 EXCEPTION
228 WHEN FND_API.G_EXC_ERROR THEN
229 ROLLBACK TO update_row;
230 x_return_status := FND_API.G_RET_STS_ERROR ;
231 FND_MSG_PUB.Count_And_Get(
232 p_count => x_msg_count ,
233 p_data => x_msg_data ,
234 p_encoded => FND_API.G_FALSE);
235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
236 ROLLBACK TO update_row;
237 x_loading_status := 'UNEXPECTED_ERR';
238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
239 FND_MSG_PUB.Count_And_Get(
240 p_count => x_msg_count ,
241 p_data => x_msg_data ,
242 p_encoded => FND_API.G_FALSE);
243 WHEN OTHERS THEN
244 ROLLBACK TO update_row;
245 x_loading_status := 'UNEXPECTED_ERR';
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
247 IF FND_MSG_PUB.Check_Msg_Level(
248 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
249 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
250 END IF;
251 FND_MSG_PUB.Count_And_Get(
252 p_count => x_msg_count ,
253 p_data => x_msg_data ,
254 p_encoded => FND_API.G_FALSE);
255 END;
256 --
257 PROCEDURE delete_notes(
258 p_api_version IN NUMBER,
259 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
260 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
261 p_commit IN VARCHAR2 := CN_API.G_FALSE,
262 p_reason_id IN NUMBER := FND_API.G_MISS_NUM,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2,
266 x_loading_status OUT NOCOPY VARCHAR2) IS
267 --
268 CURSOR reason_cur(l_reason_id NUMBER) IS
269 SELECT *
270 FROM cn_reasons r
271 WHERE r.reason_id = l_reason_id;
272 --
273 l_api_name CONSTANT VARCHAR2(30) := 'delete_notes';
274 l_api_version CONSTANT NUMBER := 1.0;
275 l_history_rec cn_reason_history_pkg.reason_history_all_rec_type;
276 l_reason VARCHAR2(4000);
277 l_clob_loc CLOB;
278 l_clob_length NUMBER;
279 --
280 BEGIN
281 -- Standard Start of API savepoint
282 SAVEPOINT delete_notes;
283 -- Standard call to check for call compatibility.
284 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
285 p_api_version ,
286 l_api_name,
287 G_PKG_NAME ) THEN
288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
289 END IF;
290 -- Initialize message list if p_init_msg_list is set to TRUE.
291 IF FND_API.to_Boolean( p_init_msg_list ) THEN
292 FND_MSG_PUB.initialize;
293 END IF;
294 -- Initialize API return status to success
295 x_return_status := FND_API.G_RET_STS_SUCCESS;
296 x_loading_status := 'CN_INSERTED';
297 -- API body
298 IF (p_reason_id = FND_API.G_MISS_NUM) THEN
299 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
300 FND_MESSAGE.SET_NAME ('CN' , 'CN_REASON_ID_ERROR');
301 FND_MSG_PUB.Add;
302 END IF;
303 x_loading_status := 'CN_REASON_ID_ERROR';
304 RAISE FND_API.G_EXC_ERROR ;
305 END IF;
306 -- First original record to be moved to CN_REASON_HISTORY table.
307 FOR rec IN reason_cur(p_reason_id)
308 LOOP
309 l_history_rec.reason_id := rec.reason_id;
310 l_history_rec.updated_table := rec.updated_table;
311 l_history_rec.upd_table_id := rec.upd_table_id;
312 l_history_rec.reason_code := rec.reason_code;
313 l_history_rec.dml_flag := 'DELETE';
314 l_history_rec.lookup_type := rec.lookup_type;
315 l_history_rec.update_flag := rec.lookup_type;
316 l_history_rec.attribute_category := rec.attribute_category;
317 l_history_rec.attribute1 := rec.attribute1;
318 l_history_rec.attribute2 := rec.attribute2;
319 l_history_rec.attribute3 := rec.attribute3;
320 l_history_rec.attribute4 := rec.attribute4;
321 l_history_rec.attribute5 := rec.attribute5;
322 l_history_rec.attribute6 := rec.attribute6;
323 l_history_rec.attribute7 := rec.attribute7;
324 l_history_rec.attribute8 := rec.attribute8;
325 l_history_rec.attribute9 := rec.attribute9;
326 l_history_rec.attribute10 := rec.attribute10;
327 l_history_rec.attribute11 := rec.attribute11;
328 l_history_rec.attribute12 := rec.attribute12;
329 l_history_rec.attribute13 := rec.attribute13;
330 l_history_rec.attribute14 := rec.attribute14;
331 l_history_rec.attribute15 := rec.attribute15;
332 -- Processing CLOB
333 l_clob_loc := rec.reason;
334 l_clob_length := dbms_lob.getlength(l_clob_loc);
335 dbms_lob.read(l_clob_loc,l_clob_length,1,l_history_rec.reason);
336 -- End of CLOB processing
337 -- Get reason_history_id from the SEQUENCE.
338 SELECT cn_reason_history_s.NEXTVAL
339 INTO l_history_rec.reason_history_id
340 FROM dual;
341 --
342 cn_reason_history_pkg.insert_row(l_history_rec);
343 --
344 cn_reasons_pkg.delete_row(p_reason_id);
345 --
346 END LOOP;
347 EXCEPTION
348 WHEN FND_API.G_EXC_ERROR THEN
349 ROLLBACK TO update_row;
350 x_return_status := FND_API.G_RET_STS_ERROR ;
351 FND_MSG_PUB.Count_And_Get(
352 p_count => x_msg_count ,
353 p_data => x_msg_data ,
354 p_encoded => FND_API.G_FALSE);
355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
356 ROLLBACK TO update_row;
357 x_loading_status := 'UNEXPECTED_ERR';
358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
359 FND_MSG_PUB.Count_And_Get(
360 p_count => x_msg_count ,
361 p_data => x_msg_data ,
362 p_encoded => FND_API.G_FALSE);
363 WHEN OTHERS THEN
364 ROLLBACK TO update_row;
365 x_loading_status := 'UNEXPECTED_ERR';
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
367 IF FND_MSG_PUB.Check_Msg_Level(
368 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
369 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
370 END IF;
371 FND_MSG_PUB.Count_And_Get(
372 p_count => x_msg_count ,
373 p_data => x_msg_data ,
374 p_encoded => FND_API.G_FALSE);
375 END;
376 --
377 PROCEDURE delete_worksheet_notes(
378 p_api_version IN NUMBER,
379 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
380 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
381 p_commit IN VARCHAR2 := CN_API.G_FALSE,
382 p_payment_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
383 x_return_status OUT NOCOPY VARCHAR2,
384 x_msg_count OUT NOCOPY NUMBER,
385 x_msg_data OUT NOCOPY VARCHAR2,
386 x_loading_status OUT NOCOPY VARCHAR2) IS
387 --
388 CURSOR reason_cur IS
389 SELECT reason_id
390 FROM cn_reasons
391 WHERE upd_table_id = p_payment_worksheet_id;
392 --
393 CURSOR history_cur IS
394 SELECT reason_history_id
395 FROM cn_reason_history
396 WHERE upd_table_id = p_payment_worksheet_id;
397 --
398 l_api_name CONSTANT VARCHAR2(30) := 'delete_worksheet_notes';
399 l_api_version CONSTANT NUMBER := 1.0;
400 --
401 BEGIN
402 -- Standard Start of API savepoint
403 SAVEPOINT delete_worksheet_notes;
404 -- Standard call to check for call compatibility.
405 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
406 p_api_version ,
407 l_api_name,
408 G_PKG_NAME ) THEN
409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
410 END IF;
411 -- Initialize message list if p_init_msg_list is set to TRUE.
412 IF FND_API.to_Boolean( p_init_msg_list ) THEN
413 FND_MSG_PUB.initialize;
414 END IF;
415 -- Initialize API return status to success
416 x_return_status := FND_API.G_RET_STS_SUCCESS;
417 x_loading_status := 'CN_INSERTED';
418 -- API body
419 IF (p_payment_worksheet_id = FND_API.G_MISS_NUM) THEN
420 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
421 FND_MESSAGE.SET_NAME ('CN' , 'CN_WORKSHEET_ID_ERROR');
422 FND_MSG_PUB.Add;
423 END IF;
424 x_loading_status := 'CN_WORKSHEET_ID_ERROR';
425 RAISE FND_API.G_EXC_ERROR ;
426 END IF;
427 FOR history_rec IN history_cur
428 LOOP
429 cn_reason_history_pkg.delete_row(history_rec.reason_history_id);
430 END LOOP;
431 --
432 FOR reason_rec IN reason_cur
433 LOOP
434 cn_reasons_pkg.delete_row(reason_rec.reason_id);
435 END LOOP;
436 --
437 EXCEPTION
438 WHEN FND_API.G_EXC_ERROR THEN
439 ROLLBACK TO delete_worksheet_notes;
440 x_return_status := FND_API.G_RET_STS_ERROR ;
441 FND_MSG_PUB.Count_And_Get(
442 p_count => x_msg_count ,
443 p_data => x_msg_data ,
444 p_encoded => FND_API.G_FALSE);
445 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
446 ROLLBACK TO delete_worksheet_notes;
447 x_loading_status := 'UNEXPECTED_ERR';
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
449 FND_MSG_PUB.Count_And_Get(
450 p_count => x_msg_count ,
451 p_data => x_msg_data ,
452 p_encoded => FND_API.G_FALSE);
453 WHEN OTHERS THEN
454 ROLLBACK TO delete_worksheet_notes;
455 x_loading_status := 'UNEXPECTED_ERR';
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
457 IF FND_MSG_PUB.Check_Msg_Level(
458 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
459 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
460 END IF;
461 FND_MSG_PUB.Count_And_Get(
462 p_count => x_msg_count ,
463 p_data => x_msg_data ,
464 p_encoded => FND_API.G_FALSE);
465 END;
466 --
467 END;