[Home] [Help]
PACKAGE BODY: APPS.CN_REASONS_PUB
Source
1 PACKAGE BODY CN_REASONS_PUB AS
2 -- $Header: cnpresnb.pls 115.3 2003/07/04 01:36:09 jjhuang 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_pub
11 -- Purpose
12 -- Package Body to display the analyst comments for a payment worksheet.
13 -- History
14 -- 04/02/02 Rao.Chenna Created
15 -- 06/16/03 Julia Huang Added show_last_analyst_note for 11.5.10
16 --
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_REASONS_PUB';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnpresnb.pls';
19 --
20 /*
21 PROCEDURE my_debug(
22 i_value IN VARCHAR2) IS
23 l_error VARCHAR2(1000);
24 BEGIN
25 INSERT INTO cn_process_audit_lines(
26 process_audit_id,process_audit_line_id,
27 message_type_code,message_text)
28 VALUES(
29 cn_process_audits_s.nextval,cn_process_audit_lines_s1.NEXTVAL,
30 'cnnotes',i_value);
31 COMMIT;
32 EXCEPTION
33 WHEN OTHERS THEN
34 l_error := SQLERRM;
35 INSERT INTO cn_process_audit_lines(
36 process_audit_id,process_audit_line_id,
37 message_type_code,message_text)
38 VALUES(
39 cn_process_audits_s.nextval,cn_process_audit_lines_s1.NEXTVAL,
40 'cnnotes',l_error);
41 COMMIT;
42 END; */
43 --
44 PROCEDURE show_analyst_notes(
45 p_api_version IN NUMBER,
46 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
47 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
48 p_commit IN VARCHAR2 := CN_API.G_FALSE,
49 p_first IN NUMBER,
50 p_last IN NUMBER,
51 p_payment_worksheet_id IN NUMBER,
52 p_table_name IN VARCHAR2,
53 p_lookup_type IN VARCHAR2,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 x_loading_status OUT NOCOPY VARCHAR2,
58 x_worksheet_rec OUT NOCOPY cn_reasons_pub.worksheet_rec,
59 x_notes_tbl OUT NOCOPY cn_reasons_pub.notes_tbl,
60 x_notes_count OUT NOCOPY NUMBER) IS
61 --
62 CURSOR c1 IS
63 SELECT w.payment_worksheet_id,w.role_id,w.worksheet_status,
64 p.name,p.payrun_id,p.pay_period_id,p.status,
65 ps.period_name,s.salesrep_id,s.resource_id,s.name,
66 s.employee_number,pg.pay_group_id,pg.name
67 FROM cn_payruns p,
68 cn_payment_worksheets w,
69 cn_salesreps s,
70 cn_period_statuses ps,
71 cn_pay_groups pg
72 WHERE p.payrun_id = w.payrun_id
73 AND w.salesrep_id = s.salesrep_id
74 AND p.pay_period_id = ps.period_id
75 AND pg.pay_group_id = p.pay_group_id
76 AND w.payment_worksheet_id = p_payment_worksheet_id;
77 --
78 CURSOR c2 IS
79 SELECT r.*, u.user_name,l.meaning
80 FROM cn_reasons r,fnd_user u,cn_lookups l
81 WHERE upd_table_id = p_payment_worksheet_id
82 AND r.lookup_type = p_lookup_type
83 AND updated_table = p_table_name
84 AND r.last_updated_by = u.user_id
85 AND r.reason_code = l.lookup_code
86 AND l.lookup_type = p_lookup_type
87 ORDER BY r.last_update_date;
88 --
89 l_api_name CONSTANT VARCHAR2(30) := 'show_analyst_notes';
90 l_api_version CONSTANT NUMBER := 1.0;
91 l_worksheet_rec cn_reasons_pub.worksheet_rec;
92 l_tbl_count NUMBER := 0;
93 l_total_rows NUMBER := 0;
94 l_clob_loc CLOB;
95 l_clob_length NUMBER;
96 --
97 BEGIN
98 -- Standard Start of API savepoint
99 SAVEPOINT show_analyst_notes;
100 -- Standard call to check for call compatibility.
101 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
102 p_api_version ,
103 l_api_name,
104 G_PKG_NAME ) THEN
105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
106 END IF;
107 -- Initialize message list if p_init_msg_list is set to TRUE.
108 IF FND_API.to_Boolean( p_init_msg_list ) THEN
109 FND_MSG_PUB.initialize;
110 END IF;
111 -- Initialize API return status to success
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113 x_loading_status := 'CN_INSERTED';
114 -- API body
115 -- Populate the worksheet information using this cursor and pass it to
116 -- JSP
117 OPEN c1;
118 FETCH c1 INTO x_worksheet_rec;
119 CLOSE c1;
120 --
121 -- Populate analyst notes.
122 FOR rec IN c2
123 LOOP
124 l_total_rows := l_total_rows + 1;
125 IF (l_total_rows BETWEEN p_first AND p_last) THEN
126 l_tbl_count := l_tbl_count + 1;
127 x_notes_tbl(l_tbl_count).reason_id := rec.reason_id;
128 x_notes_tbl(l_tbl_count).updated_table := rec.updated_table;
129 x_notes_tbl(l_tbl_count).upd_table_id := rec.upd_table_id;
130 x_notes_tbl(l_tbl_count).reason_code := rec.reason_code;
131 x_notes_tbl(l_tbl_count).reason_meaning := rec.meaning;
132 x_notes_tbl(l_tbl_count).lookup_type := rec.lookup_type;
133 x_notes_tbl(l_tbl_count).update_flag := rec.update_flag;
134 x_notes_tbl(l_tbl_count).attribute_category := rec.attribute_category;
135 x_notes_tbl(l_tbl_count).attribute1 := rec.attribute1;
136 x_notes_tbl(l_tbl_count).attribute2 := rec.attribute2;
137 x_notes_tbl(l_tbl_count).attribute3 := rec.attribute3;
138 x_notes_tbl(l_tbl_count).attribute4 := rec.attribute4;
139 x_notes_tbl(l_tbl_count).attribute5 := rec.attribute5;
140 x_notes_tbl(l_tbl_count).attribute6 := rec.attribute6;
141 x_notes_tbl(l_tbl_count).attribute7 := rec.attribute7;
142 x_notes_tbl(l_tbl_count).attribute8 := rec.attribute8;
143 x_notes_tbl(l_tbl_count).attribute9 := rec.attribute9;
144 x_notes_tbl(l_tbl_count).attribute10 := rec.attribute10;
145 x_notes_tbl(l_tbl_count).attribute11 := rec.attribute11;
146 x_notes_tbl(l_tbl_count).attribute12 := rec.attribute12;
147 x_notes_tbl(l_tbl_count).attribute13 := rec.attribute13;
148 x_notes_tbl(l_tbl_count).attribute14 := rec.attribute14;
149 x_notes_tbl(l_tbl_count).attribute15 := rec.attribute15;
150 x_notes_tbl(l_tbl_count).last_update_date := rec.last_update_date;
151 x_notes_tbl(l_tbl_count).last_updated_by := rec.last_updated_by;
152 x_notes_tbl(l_tbl_count).last_updated_username := rec.user_name;
153 x_notes_tbl(l_tbl_count).object_version_number := rec.object_version_number;
154 -- Processing CLOB
155 l_clob_loc := rec.reason;
156 l_clob_length := dbms_lob.getlength(l_clob_loc);
157 dbms_lob.read(l_clob_loc,l_clob_length,1,x_notes_tbl(l_tbl_count).reason);
158 -- End of CLOB processing
159 END IF;
160 END LOOP;
161 x_notes_count := l_total_rows;
162 EXCEPTION
163 WHEN FND_API.G_EXC_ERROR THEN
164 ROLLBACK TO show_analyst_notes;
165 x_return_status := FND_API.G_RET_STS_ERROR ;
166 FND_MSG_PUB.Count_And_Get(
167 p_count => x_msg_count ,
168 p_data => x_msg_data ,
169 p_encoded => FND_API.G_FALSE);
170 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
171 ROLLBACK TO show_analyst_notes;
172 x_loading_status := 'UNEXPECTED_ERR';
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
174 FND_MSG_PUB.Count_And_Get(
175 p_count => x_msg_count ,
176 p_data => x_msg_data ,
177 p_encoded => FND_API.G_FALSE);
178 WHEN OTHERS THEN
179 ROLLBACK TO show_analyst_notes;
180 x_loading_status := 'UNEXPECTED_ERR';
181 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
182 IF FND_MSG_PUB.Check_Msg_Level(
183 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
184 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
185 END IF;
186 FND_MSG_PUB.Count_And_Get(
187 p_count => x_msg_count ,
188 p_data => x_msg_data ,
189 p_encoded => FND_API.G_FALSE);
190 END;
191 --
192 PROCEDURE manage_analyst_notes(
193 p_api_version IN NUMBER,
194 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
195 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
196 p_commit IN VARCHAR2 := CN_API.G_FALSE,
197 p_notes_tbl IN cn_reasons_pub.notes_tbl,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2,
201 x_loading_status OUT NOCOPY VARCHAR2) IS
202 --
203 l_api_name CONSTANT VARCHAR2(30) := 'manage_analyst_notes';
204 l_api_version CONSTANT NUMBER := 1.0;
205 l_reasons_all_rec CN_REASONS_PKG.REASONS_ALL_REC_TYPE;
206 --
207 BEGIN
208 -- Standard Start of API savepoint
209 SAVEPOINT manage_analyst_notes;
210 -- Standard call to check for call compatibility.
211 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
212 p_api_version ,
213 l_api_name,
214 G_PKG_NAME ) THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217 -- Initialize message list if p_init_msg_list is set to TRUE.
218 IF FND_API.to_Boolean( p_init_msg_list ) THEN
219 FND_MSG_PUB.initialize;
220 END IF;
221 -- Initialize API return status to success
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223 x_loading_status := 'CN_INSERTED';
224 -- API body
225 IF (p_notes_tbl.COUNT > 0) THEN
226 FOR i IN p_notes_tbl.FIRST..p_notes_tbl.LAST
227 LOOP
228 --IF (p_notes_tbl(i).dml_flag = 'INSERT') THEN
229 IF ((p_notes_tbl(i).reason_code = 'SYSTEM_GENERATED') AND
230 (p_notes_tbl(i).lookup_type = 'ANALYST_NOTE_REASON')) THEN
231 l_reasons_all_rec.update_flag := 'N';
232 ELSE
233 l_reasons_all_rec.update_flag := 'Y';
234 END IF;
235 l_reasons_all_rec.reason_id := p_notes_tbl(i).reason_id;
236 l_reasons_all_rec.updated_table := p_notes_tbl(i).updated_table;
237 l_reasons_all_rec.upd_table_id := p_notes_tbl(i).upd_table_id;
238 l_reasons_all_rec.reason_code := p_notes_tbl(i).reason_code;
239 l_reasons_all_rec.reason := p_notes_tbl(i).reason;
240 l_reasons_all_rec.lookup_type := p_notes_tbl(i).lookup_type;
241 l_reasons_all_rec.attribute_category:= p_notes_tbl(i).attribute_category;
242 l_reasons_all_rec.attribute1 := p_notes_tbl(i).attribute1;
243 l_reasons_all_rec.attribute2 := p_notes_tbl(i).attribute2;
244 l_reasons_all_rec.attribute3 := p_notes_tbl(i).attribute3;
245 l_reasons_all_rec.attribute4 := p_notes_tbl(i).attribute4;
246 l_reasons_all_rec.attribute5 := p_notes_tbl(i).attribute5;
247 l_reasons_all_rec.attribute6 := p_notes_tbl(i).attribute6;
248 l_reasons_all_rec.attribute7 := p_notes_tbl(i).attribute7;
249 l_reasons_all_rec.attribute8 := p_notes_tbl(i).attribute8;
250 l_reasons_all_rec.attribute9 := p_notes_tbl(i).attribute9;
251 l_reasons_all_rec.attribute10 := p_notes_tbl(i).attribute10;
252 l_reasons_all_rec.attribute11 := p_notes_tbl(i).attribute11;
253 l_reasons_all_rec.attribute12 := p_notes_tbl(i).attribute12;
254 l_reasons_all_rec.attribute13 := p_notes_tbl(i).attribute13;
255 l_reasons_all_rec.attribute14 := p_notes_tbl(i).attribute14;
256 l_reasons_all_rec.attribute15 := p_notes_tbl(i).attribute15;
257 l_reasons_all_rec.object_version_number:= p_notes_tbl(i).object_version_number;
258 --
259 IF (p_notes_tbl(i).dml_flag = 'INSERT') THEN
260 cn_reasons_pvt.insert_row(
261 p_api_version => l_api_version,
262 p_init_msg_list => p_init_msg_list,
263 p_validation_level => p_validation_level,
264 p_commit => p_commit,
265 p_reasons_all_rec => l_reasons_all_rec,
266 x_return_status => x_return_status,
267 x_msg_count => x_msg_count,
268 x_msg_data => x_msg_data,
269 x_loading_status => x_loading_status);
270 ELSIF (p_notes_tbl(i).dml_flag = 'UPDATE') THEN
271 cn_reasons_pvt.update_row(
272 p_api_version => l_api_version,
273 p_init_msg_list => p_init_msg_list,
274 p_validation_level => p_validation_level,
275 p_commit => p_commit,
276 p_reasons_all_rec => l_reasons_all_rec,
277 x_return_status => x_return_status,
278 x_msg_count => x_msg_count,
279 x_msg_data => x_msg_data,
280 x_loading_status => x_loading_status);
281 END IF;
282 --
283 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
284 RAISE FND_API.G_EXC_ERROR;
285 END IF;
286 --
287 --END IF;
288 END LOOP;
289 END IF;
290 EXCEPTION
291 WHEN FND_API.G_EXC_ERROR THEN
292 ROLLBACK TO manage_analyst_notes;
293 x_return_status := FND_API.G_RET_STS_ERROR ;
294 FND_MSG_PUB.Count_And_Get(
295 p_count => x_msg_count ,
296 p_data => x_msg_data ,
297 p_encoded => FND_API.G_FALSE);
298 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
299 ROLLBACK TO manage_analyst_notes;
300 x_loading_status := 'UNEXPECTED_ERR';
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
302 FND_MSG_PUB.Count_And_Get(
303 p_count => x_msg_count ,
304 p_data => x_msg_data ,
305 p_encoded => FND_API.G_FALSE);
306 WHEN OTHERS THEN
307 ROLLBACK TO manage_analyst_notes;
308 x_loading_status := 'UNEXPECTED_ERR';
309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
310 IF FND_MSG_PUB.Check_Msg_Level(
311 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
312 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
313 END IF;
314 FND_MSG_PUB.Count_And_Get(
315 p_count => x_msg_count ,
316 p_data => x_msg_data ,
317 p_encoded => FND_API.G_FALSE);
318 END;
319 --
320 PROCEDURE remove_analyst_notes(
321 p_api_version IN NUMBER,
322 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
323 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
324 p_commit IN VARCHAR2 := CN_API.G_FALSE,
325 p_payment_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
326 p_reason_id IN NUMBER := FND_API.G_MISS_NUM,
327 x_return_status OUT NOCOPY VARCHAR2,
328 x_msg_count OUT NOCOPY NUMBER,
329 x_msg_data OUT NOCOPY VARCHAR2,
330 x_loading_status OUT NOCOPY VARCHAR2) IS
331
332 --
333 l_api_name CONSTANT VARCHAR2(30) := 'remove_analyst_notes';
334 l_api_version CONSTANT NUMBER := 1.0;
335 --
336 BEGIN
337 -- Standard Start of API savepoint
338 SAVEPOINT remove_analyst_notes;
339 -- Standard call to check for call compatibility.
340 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
341 p_api_version ,
342 l_api_name,
343 G_PKG_NAME ) THEN
344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
345 END IF;
346 -- Initialize message list if p_init_msg_list is set to TRUE.
347 IF FND_API.to_Boolean( p_init_msg_list ) THEN
348 FND_MSG_PUB.initialize;
349 END IF;
350 -- Initialize API return status to success
351 x_return_status := FND_API.G_RET_STS_SUCCESS;
352 x_loading_status := 'CN_INSERTED';
353 -- API body
354 cn_reasons_pvt.delete_notes(
355 p_api_version => l_api_version,
356 p_init_msg_list => p_init_msg_list,
357 p_validation_level => p_validation_level,
361 x_msg_count => x_msg_count,
358 p_commit => p_commit,
359 p_reason_id => p_reason_id,
360 x_return_status => x_return_status,
362 x_msg_data => x_msg_data,
363 x_loading_status => x_loading_status);
364 --
365 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368 --
369 EXCEPTION
370 WHEN FND_API.G_EXC_ERROR THEN
371 ROLLBACK TO remove_analyst_notes;
372 x_return_status := FND_API.G_RET_STS_ERROR ;
373 FND_MSG_PUB.Count_And_Get(
374 p_count => x_msg_count ,
375 p_data => x_msg_data ,
376 p_encoded => FND_API.G_FALSE);
377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
378 ROLLBACK TO remove_analyst_notes;
379 x_loading_status := 'UNEXPECTED_ERR';
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
381 FND_MSG_PUB.Count_And_Get(
382 p_count => x_msg_count ,
383 p_data => x_msg_data ,
384 p_encoded => FND_API.G_FALSE);
385 WHEN OTHERS THEN
386 ROLLBACK TO remove_analyst_notes;
387 x_loading_status := 'UNEXPECTED_ERR';
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
389 IF FND_MSG_PUB.Check_Msg_Level(
390 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
391 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
392 END IF;
393 FND_MSG_PUB.Count_And_Get(
394 p_count => x_msg_count ,
395 p_data => x_msg_data ,
396 p_encoded => FND_API.G_FALSE);
397 END;
398 --
399 --=====================================================================
400 --Procedure Name:show_last_analyst_note
401 --Description: Used to get the last analyst note and total number of notes.
402 --11.5.10
403 --=====================================================================
404 PROCEDURE show_last_analyst_note(
405 p_api_version IN NUMBER,
406 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
407 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
408 p_commit IN VARCHAR2 := CN_API.G_FALSE,
409 p_payment_worksheet_id IN NUMBER,
410 p_table_name IN VARCHAR2,
411 p_lookup_type IN VARCHAR2,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2,
415 x_loading_status OUT NOCOPY VARCHAR2,
416 x_notes_tbl OUT NOCOPY cn_reasons_pub.notes_tbl,
417 x_notes_count OUT NOCOPY NUMBER)
418 IS
419 CURSOR get_notes_count(p_payment_worksheet_id cn_payment_worksheets.payment_worksheet_id%TYPE,
420 p_lookup_type cn_reasons.lookup_type%TYPE,
424 FROM cn_reasons r
421 p_table_name cn_reasons.updated_table%TYPE)
422 IS
423 SELECT COUNT(1) cnt
425 WHERE r.upd_table_id = p_payment_worksheet_id
426 AND r.lookup_type = p_lookup_type
427 AND r.updated_table = p_table_name;
428
429 CURSOR get_notes_info(p_payment_worksheet_id cn_payment_worksheets.payment_worksheet_id%TYPE,
430 p_lookup_type cn_reasons.lookup_type%TYPE,
431 p_table_name cn_reasons.updated_table%TYPE)
432 IS
433 SELECT v.*
434 FROM
435 (
436 SELECT r.*, u.user_name,l.meaning
437 FROM cn_reasons r,fnd_user u,cn_lookups l
438 WHERE upd_table_id = p_payment_worksheet_id
439 AND r.lookup_type = p_lookup_type
440 AND updated_table = p_table_name
441 AND r.last_updated_by = u.user_id
442 AND r.reason_code = l.lookup_code
443 AND l.lookup_type = p_lookup_type
444 ORDER BY r.last_update_date desc
445 ) v
446 WHERE ROWNUM < 2;
447 --
448 l_api_name CONSTANT VARCHAR2(30) := 'show_last_analyst_note';
449 l_api_version CONSTANT NUMBER := 1.0;
450
451 l_clob_loc CLOB;
452 l_clob_length NUMBER;
453
454 l_tbl_count NUMBER := 0;
455 --
456 BEGIN
457 -- Standard Start of API savepoint
458 SAVEPOINT show_last_analyst_note;
459 -- Standard call to check for call compatibility.
460 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
461 p_api_version ,
462 l_api_name,
463 G_PKG_NAME ) THEN
464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
465 END IF;
466 -- Initialize message list if p_init_msg_list is set to TRUE.
467 IF FND_API.to_Boolean( p_init_msg_list ) THEN
468 FND_MSG_PUB.initialize;
469 END IF;
470 -- Initialize API return status to success
471 x_return_status := FND_API.G_RET_STS_SUCCESS;
472 x_loading_status := 'CN_INSERTED';
473 -- API body
474
475 --
476 -- Populate the number of notes.
477 FOR i IN get_notes_count(p_payment_worksheet_id, p_lookup_type, p_table_name)
478 LOOP
479 x_notes_count := i.cnt;
480 END LOOP;
481
482 --
483 -- Populate the last analyst note.
484 FOR rec IN get_notes_info(p_payment_worksheet_id, p_lookup_type, p_table_name)
485 LOOP
486 x_notes_tbl(l_tbl_count).reason_id := rec.reason_id;
487 x_notes_tbl(l_tbl_count).updated_table := rec.updated_table;
488 x_notes_tbl(l_tbl_count).upd_table_id := rec.upd_table_id;
489 x_notes_tbl(l_tbl_count).reason_code := rec.reason_code;
490 x_notes_tbl(l_tbl_count).reason_meaning := rec.meaning;
491 x_notes_tbl(l_tbl_count).lookup_type := rec.lookup_type;
492 x_notes_tbl(l_tbl_count).update_flag := rec.update_flag;
493 x_notes_tbl(l_tbl_count).attribute_category := rec.attribute_category;
494 x_notes_tbl(l_tbl_count).attribute1 := rec.attribute1;
495 x_notes_tbl(l_tbl_count).attribute2 := rec.attribute2;
496 x_notes_tbl(l_tbl_count).attribute3 := rec.attribute3;
497 x_notes_tbl(l_tbl_count).attribute4 := rec.attribute4;
498 x_notes_tbl(l_tbl_count).attribute5 := rec.attribute5;
499 x_notes_tbl(l_tbl_count).attribute6 := rec.attribute6;
500 x_notes_tbl(l_tbl_count).attribute7 := rec.attribute7;
501 x_notes_tbl(l_tbl_count).attribute8 := rec.attribute8;
502 x_notes_tbl(l_tbl_count).attribute9 := rec.attribute9;
503 x_notes_tbl(l_tbl_count).attribute10 := rec.attribute10;
504 x_notes_tbl(l_tbl_count).attribute11 := rec.attribute11;
505 x_notes_tbl(l_tbl_count).attribute12 := rec.attribute12;
506 x_notes_tbl(l_tbl_count).attribute13 := rec.attribute13;
507 x_notes_tbl(l_tbl_count).attribute14 := rec.attribute14;
508 x_notes_tbl(l_tbl_count).attribute15 := rec.attribute15;
509 x_notes_tbl(l_tbl_count).last_update_date := rec.last_update_date;
510 x_notes_tbl(l_tbl_count).last_updated_by := rec.last_updated_by;
511 x_notes_tbl(l_tbl_count).last_updated_username := rec.user_name;
512 x_notes_tbl(l_tbl_count).object_version_number := rec.object_version_number;
513 -- Processing CLOB
514 l_clob_loc := rec.reason;
515 l_clob_length := dbms_lob.getlength(l_clob_loc);
516 dbms_lob.read(l_clob_loc,l_clob_length,1,x_notes_tbl(l_tbl_count).reason);
517 -- End of CLOB processing
518
519 l_tbl_count := l_tbl_count + 1;
520 END LOOP;
521
522 EXCEPTION
523 WHEN FND_API.G_EXC_ERROR THEN
524 ROLLBACK TO show_last_analyst_note;
525 x_return_status := FND_API.G_RET_STS_ERROR ;
526 FND_MSG_PUB.Count_And_Get(
527 p_count => x_msg_count ,
528 p_data => x_msg_data ,
529 p_encoded => FND_API.G_FALSE);
530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
531 ROLLBACK TO show_last_analyst_note;
532 x_loading_status := 'UNEXPECTED_ERR';
533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
534 FND_MSG_PUB.Count_And_Get(
535 p_count => x_msg_count ,
536 p_data => x_msg_data ,
537 p_encoded => FND_API.G_FALSE);
538 WHEN OTHERS THEN
539 ROLLBACK TO show_last_analyst_note;
540 x_loading_status := 'UNEXPECTED_ERR';
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
542 IF FND_MSG_PUB.Check_Msg_Level(
543 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
544 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
545 END IF;
546 FND_MSG_PUB.Count_And_Get(
547 p_count => x_msg_count ,
548 p_data => x_msg_data ,
549 p_encoded => FND_API.G_FALSE);
550
551 END show_last_analyst_note;
552
553 END;