[Home] [Help]
PACKAGE BODY: APPS.IEX_NOTES_PVT
Source
1 PACKAGE BODY IEX_NOTES_PVT AS
2 /* $Header: iexvntsb.pls 120.4 2006/01/06 18:07:26 jypark ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEX_NOTES_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) :='iexvntsb.pls';
6 G_APPL_ID NUMBER;
7 G_LOGIN_ID NUMBER;
8 G_PROGRAM_ID NUMBER;
9 --G_USER_ID NUMBER;
10 G_REQUEST_ID NUMBER;
11
12 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
13 PG_DEBUG NUMBER;
14
15 PROCEDURE Create_Note(
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2,
18 p_commit IN VARCHAR2,
19 p_validation_level IN NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2,
23 p_source_object_id IN NUMBER,
24 p_source_object_code IN VARCHAR2,
25 p_note_type IN VARCHAR2,
26 p_notes IN VARCHAR2,
27 p_contexts_tbl IN CONTEXTS_TBL_TYPE,
28 x_note_id OUT NOCOPY NUMBER)
29 AS
30 l_api_name CONSTANT VARCHAR2(30) := 'Create_Note';
31 l_api_version CONSTANT NUMBER := 1.0;
32 l_return_status VARCHAR2(1);
33 l_msg_count NUMBER;
34 l_msg_data VARCHAR2(32767);
35 l_source_object_id NUMBER;
36 l_jtf_note_contexts_table jtf_notes_pub.jtf_note_contexts_tbl_type;
37 i number;
38 p_note_id number;
39
40 BEGIN
41 -- Standard start of API savepoint
42 SAVEPOINT Create_Note_PVT;
43
44 -- Standard call to check for call compatibility
45 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
46 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
47 END IF;
48
49 -- Initialize message list if p_init_msg_list is set to TRUE
50 IF FND_API.To_Boolean(p_init_msg_list) THEN
51 FND_MSG_PUB.initialize;
52 END IF;
53
54 -- Initialize API return status to success
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 -- START OF BODY OF API
58 -- IF PG_DEBUG < 10 THEN
59 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
60 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: Begin');
61 END IF;
62
63 -- IF PG_DEBUG < 10 THEN
64 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
65 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: G_USER_ID = ' || FND_GLOBAL.USER_ID);
66 END IF;
67
68 -- IF PG_DEBUG < 10 THEN
69 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
70 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: Going thru context table');
71 END IF;
72 FOR i IN 1..p_contexts_tbl.COUNT LOOP
73 -- IF PG_DEBUG < 10 THEN
74 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
75 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: loop = ' || i);
76 END IF;
77 l_jtf_note_contexts_table(i).note_context_type := p_contexts_tbl(i).CONTEXT_TYPE;
78 l_jtf_note_contexts_table(i).note_context_type_id := p_contexts_tbl(i).CONTEXT_ID;
79 l_jtf_note_contexts_table(i).last_update_date := sysdate;
80 l_jtf_note_contexts_table(i).creation_date := sysdate;
81 l_jtf_note_contexts_table(i).last_updated_by := FND_GLOBAL.USER_ID;
82 l_jtf_note_contexts_table(i).created_by := FND_GLOBAL.USER_ID;
83 l_jtf_note_contexts_table(i).last_update_login := FND_GLOBAL.USER_ID;
84 -- IF PG_DEBUG < 10 THEN
85 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
86 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: ' || l_jtf_note_contexts_table(i).note_context_type);
87 END IF;
88 -- IF PG_DEBUG < 10 THEN
89 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
90 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: ' || l_jtf_note_contexts_table(i).note_context_type_id);
91 END IF;
92 END LOOP;
93
94 -- IF PG_DEBUG < 10 THEN
95 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
96 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: Before call to jtf_notes_pub.create_note');
97 END IF;
98 jtf_notes_pub.create_note(
99 P_Api_Version => 1.0,
100 P_Init_Msg_List => FND_API.G_FALSE,
101 P_Commit => P_Commit,
102 p_jtf_note_id => FND_API.g_MISS_NUM,
103 p_validation_level => p_validation_level,
104 p_source_object_id => p_source_object_id,
105 p_source_object_code => p_source_object_code,
106 p_notes => p_notes,
107 p_entered_by => FND_GLOBAL.USER_ID,
108 p_entered_date => sysdate,
109 p_last_update_date => sysdate,
110 p_last_updated_by => FND_GLOBAL.USER_ID,
111 p_creation_date => sysdate,
112 p_created_by => FND_GLOBAL.USER_ID,
113 p_last_update_login => FND_GLOBAL.USER_ID,
114 x_jtf_note_id => x_note_id,
115 X_Return_Status => l_Return_Status,
116 X_Msg_Count => l_Msg_Count,
117 X_Msg_Data => l_Msg_Data,
118 p_jtf_note_contexts_tab => l_jtf_note_contexts_table,
119 p_note_type => p_note_type);
120
121 -- IF PG_DEBUG < 10 THEN
122 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
123 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: After call to jtf_notes_pub.create_note');
124 END IF;
125 -- IF PG_DEBUG < 10 THEN
126 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
127 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: l_Return_Status: ' || l_Return_Status);
128 END IF;
129
130 -- check for errors
131 IF l_return_status<>FND_API.G_RET_STS_SUCCESS THEN
132 FND_MESSAGE.SET_NAME('IEX', 'IEX_FAILED_CREATE_NOTE');
133 FND_MSG_PUB.Add;
134 RAISE FND_API.G_EXC_ERROR;
135 END IF;
136
137 -- END OF BODY OF API
138 -- IF PG_DEBUG < 10 THEN
139 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
140 iex_debug_pub.LogMessage(G_PKG_NAME || '.Create_Note: End');
141 END IF;
142
143 -- Standard check of p_commit.
144 IF FND_API.To_Boolean( p_commit ) THEN
145 COMMIT WORK;
146 END IF;
147
148 x_return_status := l_return_status;
149
150 -- Standard call to get message count and if count is 1, get message info
151 FND_MSG_PUB.Count_And_Get(
152 p_encoded => FND_API.G_FALSE,
153 p_count => x_msg_count,
154 p_data => x_msg_data);
155
156 EXCEPTION
157 WHEN FND_API.G_EXC_ERROR THEN
158 ROLLBACK TO Create_Note_PVT;
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
161
162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
163 ROLLBACK TO Create_Note_PVT;
164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
165 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
166
167 WHEN OTHERS THEN
168 ROLLBACK TO Create_Note_PVT;
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
171 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
172 END IF;
173 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
174
175 END Create_Note;
176
177 -- create by jypark for notes form's getting notes summary functionality
178 PROCEDURE Get_Notes_Summary(
179 p_api_version IN NUMBER,
180 p_init_msg_list IN VARCHAR2,
181 p_commit IN VARCHAR2,
182 p_validation_level IN NUMBER,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2,
186 p_user_id IN NUMBER,
187 p_object_code IN VARCHAR2,
188 p_object_id IN VARCHAR2,
189 p_summary_order IN VARCHAR2,
190 p_new_line_chr IN VARCHAR2,
191 x_notes_summary_tbl OUT NOCOPY NOTES_SUMMARY_TBL_TYPE) IS
192
193 l_notes VARCHAR2(32767);
194 l_created_by_name VARCHAR2(360);
195 l_entered_date DATE;
196 l_note_type_meaning VARCHAR2(80);
197 l_note_status_meaning VARCHAR2(80);
198
199 l_curr_rec_size NUMBER;
200 l_note_summary_size NUMBER;
201 l_curr_rec VARCHAR2(3000);
202 l_note_summary VARCHAR2(32000);
203
204 l_fnd_user_id NUMBER;
205 l_object_code VARCHAR2(32000);
206 l_object_id VARCHAR2(32000);
207
208 l_new_line_chr VARCHAR2(60);
209
210 TYPE refCur IS REF CURSOR;
211 C_note_details refCur;
212
213 l_source_code VARCHAR2(32000);
214 l_source_id NUMBER;
215 l_select_id VARCHAR2(200);
216 l_select_name VARCHAR2(200);
217 l_select_details VARCHAR2(2000);
218 l_from_table VARCHAR2(200);
219 l_where_clause VARCHAR2(32000);
220 l_source_name VARCHAR2(60);
221 l_notes_detail_size NUMBER;
222 l_more_text VARCHAR2(60);
223
224 l_sql_stmt VARCHAR2(32767);
225 l_max_page_size CONSTANT NUMBER := 32000;
226 l_note_summary_index BINARY_INTEGER;
227
228 l_length number;
229 l_object_code_tbl DBMS_SQL.VARCHAR2_TABLE;
230 l_object_id_tbl DBMS_SQL.VARCHAR2_TABLE;
231 l_start number;
232 l_end number;
233 l_count number;
234 l_where_cond varchar2(32767);
235 l_sqlcode number;
236 l_sqlerrm VARCHAR2(100);
237 l_proc_name CONSTANT VARCHAR2(100) := 'GET_NOTES_SUMMARY';
238
239 begin
240
241 iex_debug_pub.LogMessage(l_proc_name || ':' || 'begin');
242
243 l_fnd_user_id := p_user_id;
244 l_object_code := p_object_code;
245 l_object_id := p_object_id;
246 l_new_line_chr := p_new_line_chr;
247
248 l_start := 1;
249 l_end := 1;
250 l_count := 0;
251 l_where_cond := '1 = 2';
252 l_note_summary_index := 0;
253
254 l_length := LENGTH(l_object_code);
255 l_end := instr(l_object_code, ',', l_start, 1);
256
257 WHILE TRUE LOOP
258 l_count := l_count + 1;
259
260 if l_end <> 0 then
261 l_object_code_tbl(l_count) := substr(l_object_code, l_start, l_end - l_start);
262 else
263 l_object_code_tbl(l_count) := substr(l_object_code, l_start, l_length - l_start + 1);
264 exit;
265 end if;
266
267 l_start := l_end + 1;
268 l_end := instr(l_object_code, ',', l_start, 1);
269 END LOOP;
270
271 iex_debug_pub.LogMessage(l_proc_name || ':' || 'after populating code table:' || l_count );
272 l_start := 1;
273 l_count := 0;
274 l_length := LENGTH(l_object_id);
275 l_end := instr(l_object_id, ',', l_start, 1);
276
277 WHILE TRUE LOOP
278 l_count := l_count + 1;
279
280 if l_end <> 0 then
281 l_object_id_tbl(l_count) := substr(l_object_id, l_start, l_end - l_start);
282 else
283 l_object_id_tbl(l_count) := substr(l_object_id, l_start, l_length - l_start + 1);
284 exit;
285 end if;
286
287 l_start := l_end + 1;
288 l_end := instr(l_object_id, ',', l_start, 1);
289 END LOOP;
290
291 iex_debug_pub.LogMessage(l_proc_name || ':' || 'after populating id table:' || l_count );
292 l_curr_rec_size := 0;
293 l_note_summary_size := 0;
294 l_curr_rec := null;
295 l_note_summary := null;
296
297 l_note_summary_index := 1;
298
299
300 IF l_object_code_tbl.count = l_object_id_tbl.count THEN
301 For I in 1..l_object_id_tbl.count LOOP
302 l_count := I;
303 l_where_cond := l_where_cond || ' OR (note_context_type LIKE ''' || l_object_code_tbl(i) || ''' AND note_context_type_id = ' || l_object_id_tbl(i) || ')';
304 END LOOP;
305
306 ELSE
307
308 l_where_cond := l_where_cond || ' OR ((note_context_type LIKE ''' || l_object_code_tbl(1) || ''') AND (';
309
310 For I in 1..l_object_id_tbl.count LOOP
311 l_count := I;
312 IF I > 1 THEN
313 l_where_cond := l_where_cond || ' OR note_context_type_id = ' || l_object_id_tbl(i);
314 ELSE
315 l_where_cond := l_where_cond || 'note_context_type_id = ' || l_object_id_tbl(i);
316 END IF;
317
318 END LOOP;
319
320 l_where_cond := l_where_cond || ')) ';
321
322
323 END IF;
324
325 l_length := length(l_where_cond);
326 iex_debug_pub.LogMessage(l_proc_name || ':after create l_where_cond:' || l_count || ':' || l_length);
327
328 l_sql_stmt := 'SELECT a.notes, a.created_by_name, a.creation_date, ' ||
329 ' a.note_type_meaning, a.note_status_meaning, ' ||
330 ' a.source_object_id, a.source_object_code, ' ||
331 ' b.select_id, b.select_name, b.select_details, ' ||
332 ' b.from_table, b.where_clause, tl.name, a.notes_detail_size ' ||
333 'FROM iex_notes_bali_vl a, ' ||
334 ' jtf_objects_b b, jtf_objects_tl tl ' ||
335 'WHERE (a.note_status <> ''P'' or a.created_by = ' || l_fnd_user_id || ') ' ||
336 'AND a.source_object_code = b.object_code ' ||
337 'AND b.object_code = tl.object_code ' ||
338 'AND tl.language = userenv(''LANG'') ' ||
339 'AND a.jtf_note_id IN (SELECT jtf_note_id FROM jtf_note_contexts WHERE ' ;
340
341 l_length := length(l_sql_stmt);
342 iex_debug_pub.LogMessage(l_proc_name || ':before create l_sql_stmt:' || l_count, l_length);
343
344 l_sql_stmt := l_sql_stmt || l_where_cond || ') ' ||
345 'ORDER BY a.creation_date';
346
347 IF p_summary_order = 'D' THEN
348 l_sql_stmt := l_sql_stmt || ' DESC' ;
349 END IF;
350
351 iex_debug_pub.LogMessage(l_proc_name || ':after create l_sql_stmt:' || l_count || ':' || l_length);
352
353 -- dbms_output.put_line(length(l_sql_stmt));
354 -- dbms_output.put_line(substr(l_sql_stmt,1,200));
355 -- dbms_output.put_line(substr(l_sql_stmt,201,200));
356 -- dbms_output.put_line(substr(l_sql_stmt,401,200));
357 -- dbms_output.put_line(substr(l_sql_stmt,601));
358
359 OPEN C_note_details for l_sql_stmt;
360
361 LOOP
362 fetch C_note_details INTO
363 l_notes,
364 l_created_by_name,
365 l_entered_date,
366 l_note_type_meaning,
367 l_note_status_meaning,
368 l_source_id,
369 l_source_code,
370 l_select_id,
371 l_select_name,
372 l_select_details,
373 l_from_table,
374 l_where_clause,
375 l_source_name,
376 l_notes_detail_size;
377 if C_note_details%FOUND then
378 l_curr_rec := l_new_line_chr || to_char(l_entered_date,'DD-MON-RRRR HH:MI:SS') || ' *** ' ||
379 l_created_by_name || ' *** ' || l_note_type_meaning ||
380 ' *** ' || l_source_name || ': ' ||
381 ast_note_package.note_context_info(
382 l_select_id, l_select_name, l_select_details,
383 l_from_table, l_where_clause, l_source_id) ||
384 ' (ID:' || l_source_id || ')' ||
385 l_new_line_chr || l_notes;
386 if nvl(l_notes_detail_size,0) > 0 then
387 l_curr_rec := l_curr_rec || ' <...>';
388 end if;
389
390 l_curr_rec := l_curr_rec || l_new_line_chr || l_new_line_chr;
391
392 l_curr_rec_size := length(l_curr_rec);
393
394 if (l_note_summary_size + l_curr_rec_size) > l_max_page_size then
395 x_notes_summary_tbl(l_note_summary_index) := l_note_summary;
396 l_note_summary_index := l_note_summary_index + 1;
397 l_note_summary := l_curr_rec;
398 l_note_summary_size := l_curr_rec_size;
399 else
400 l_note_summary := l_note_summary || l_curr_rec;
401 l_note_summary_size := l_note_summary_size + l_curr_rec_size;
402 end if;
403 else
404 if l_note_summary_size <> 0 then
405 x_notes_summary_tbl(l_note_summary_index) := l_note_summary;
406 else
407 l_note_summary_index := l_note_summary_index - 1;
408 end if;
409 exit;
410 end if;
411 END LOOP;
412 close C_note_details;
413 EXCEPTION
414 WHEN OTHERS THEN
415 l_sqlcode := SQLCODE;
416 iex_debug_pub.LogMessage(l_proc_name || ':Error' || l_sqlcode);
417 iex_debug_pub.LogMessage(l_proc_name || ':l_count=' || to_char(l_count));
418 l_length := length(l_where_cond);
419 iex_debug_pub.LogMessage(l_proc_name || ':l_where_cond=' , l_length);
420 l_length := length(l_sql_stmt);
421 iex_debug_pub.LogMessage(l_proc_name || ':l_sql_stmt=' , l_length);
422 END Get_Notes_Summary;
423
424 FUNCTION GET_NOTE_HISTORY(p_jtf_note_id NUMBER)
425 RETURN VARCHAR2 IS
426 -- Begin fix bug #4930438-fix performance bug to remove MERGE JOIN CARTESIAN
427 -- CURSOR c_note_trx(x_jtf_note_id NUMBER) IS
428 -- SELECT ps.trx_number
429 -- FROM ast_notes_bali_vl notes, ar_payment_schedules ps
430 -- WHERE jtf_note_id = x_jtf_note_id
431 -- AND object_code = 'IEX_INVOICES'
432 -- AND ps.payment_schedule_id = object_id;
433 --
434 -- CURSOR c_note_party(x_jtf_note_id NUMBER) IS
435 -- SELECT p.party_name
436 -- FROM ast_notes_bali_vl notes, hz_parties p
437 -- WHERE jtf_note_id = x_jtf_note_id
438 -- AND object_code = 'PARTY'
439 -- AND object_id = p.party_id
440 -- AND p.party_type = 'ORGANIZATION';
441
442 CURSOR c_note_trx(x_jtf_note_id NUMBER) IS
443 SELECT ps.trx_number
444 FROM jtf_note_contexts notes, ar_payment_schedules ps
445 WHERE jtf_note_id = x_jtf_note_id
446 AND note_context_type = 'IEX_INVOICES'
447 AND ps.payment_schedule_id = note_context_type_id;
448
449 CURSOR c_note_party(x_jtf_note_id NUMBER) IS
450 SELECT p.party_name
451 FROM jtf_note_contexts notes, hz_parties p
452 WHERE jtf_note_id = x_jtf_note_id
453 AND note_context_type = 'PARTY'
454 AND note_context_type_id = p.party_id
455 AND p.party_type = 'ORGANIZATION';
456 -- End fix bug #4930438-fix performance bug to remove MERGE JOIN CARTESIAN
457
458 l_trx_number VARCHAR2(20);
459 l_party_name VARCHAR2(360);
460 BEGIN
461 OPEN c_note_trx(p_jtf_note_id);
462 FETCH c_note_trx INTO l_trx_number;
463 CLOSE c_note_trx;
464
465 IF l_trx_number IS NOT NULL THEN
466 RETURN l_trx_number;
467 END IF;
468
469 OPEN c_note_party(p_jtf_note_id);
470 FETCH c_note_party INTO l_party_name;
471 CLOSE c_note_party;
472
473 RETURN l_party_name;
474 EXCEPTION
475 WHEN OTHERS THEN
476 NULL;
477 END;
478 BEGIN
479 G_APPL_ID := FND_GLOBAL.Prog_Appl_Id;
480 G_LOGIN_ID := FND_GLOBAL.Conc_Login_Id;
481 G_PROGRAM_ID := FND_GLOBAL.Conc_Program_Id;
482 G_REQUEST_ID := FND_GLOBAL.Conc_Request_Id;
483
484 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
485
486 END IEX_NOTES_PVT;