1 PACKAGE BODY AP_NOTES_PUB AS
2 /* $Header: apwnotpb.pls 120.5 2011/09/20 12:35:09 rveliche ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='AP_NOTES_PUB';
6 G_MODULE_PREFIX CONSTANT VARCHAR2(30):='ap.plsql.AP_NOTES_PUB.';
7 G_ENTER CONSTANT VARCHAR2(30):='ENTER';
8 G_EXIT CONSTANT VARCHAR2(30):='EXIT';
9
10
11 --
12 PROCEDURE writeDatatoLob
13 ------------------------------------------------------------------------------
14 -- Procedure : writeDatatoLob
15 ------------------------------------------------------------------------------
16 ( p_note_id IN NUMBER ,
17 p_buffer IN VARCHAR2
18 )
19 IS
20
21 Position INTEGER := 1;
22
23 CURSOR c1
24 IS SELECT notes_detail
25 FROM ap_notes
26 WHERE note_id = p_note_id
27 FOR UPDATE;
28
29 BEGIN
30 FOR i IN c1
31 LOOP
32 DBMS_LOB.WRITE(i.notes_detail,LENGTH(p_buffer),position,p_buffer);
33 END LOOP;
34 END WriteDataToLob;
35 --
36
37 /*===========================================================================*/
38 -- Start of comments
39 --
40 -- API NAME : Create_Note
41 -- TYPE : Public
42 -- PURPOSE : This creates a note
43 -- PRE_REQS : None
44 --
45 -- PARAMETERS :
46 -- IN -
47 -- p_api_version NUMBER Required
48 -- p_init_msg_list VARCHAR2 Optional default = FND_API.G_FALSE
49 -- p_commit VARCHAR2 Optional default = FND_API.G_FALSE
50 -- p_source_object_code VARCHAR2 Required
51 -- p_source_object_id NUMBER Required
52 -- p_note_type VARCHAR2 Required
53 -- p_notes_detail VARCHAR2 Required
54 -- p_entered_by NUMBER Optional default = fnd_global.user_id
55 -- p_entered_date DATE Optional default = sysdate
56 -- p_source_lang VARCHAR2 Optional default = userenv('LANG')
57 -- p_creation_date DATE Optional default = sysdate
58 -- p_created_by NUMBER Optional default = fnd_global.user_id
59 -- p_last_update_date DATE Optional default = sysdate
60 -- p_last_updated_by NUMBER Optional default = fnd_global.user_id
61 -- p_last_update_login NUMBER Optional default = fnd_global.login_id
62 -- OUT -
63 -- x_return_status VARCHAR2(1)
64 -- x_msg_count NUMBER
65 -- x_return_status VARCHAR2(2000)
66 -- IN OUT NO COPY -
67 --
68 -- VERSION :
69 -- Initial version 1.0
70 --
71 -- MODIFICATION HISTORY :
72 -- Date Author Description of Changes
73 -- 11-Nov-2003 V Nama Created
74 --
75 -- NOTES :
76 --
77 -- End of comments
78 /*===========================================================================*/
79 procedure Create_Note (
80 p_api_version IN NUMBER ,
81 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
82 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
83 x_return_status OUT NOCOPY VARCHAR2 ,
84 x_msg_count OUT NOCOPY NUMBER ,
85 x_msg_data OUT NOCOPY VARCHAR2 ,
86 p_source_object_code IN VARCHAR2 ,
87 p_source_object_id IN NUMBER ,
88 p_note_type IN VARCHAR2 ,
89 p_notes_detail IN VARCHAR2 ,
90 p_entered_by IN NUMBER := fnd_global.user_id ,
91 p_entered_date IN DATE := sysdate ,
92 p_source_lang IN VARCHAR2 := userenv('LANG') ,
93 p_creation_date IN DATE := sysdate ,
94 p_created_by IN NUMBER := fnd_global.user_id ,
95 p_last_update_date IN DATE := sysdate ,
96 p_last_updated_by IN NUMBER := fnd_global.user_id ,
97 p_last_update_login IN NUMBER := fnd_global.login_id
98 )
99 IS
100
101 l_api_name CONSTANT VARCHAR2(30) := 'Create_Note';
102 l_api_version CONSTANT NUMBER := 1.0;
103
104 l_note_id NUMBER;
105
106 BEGIN
107 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
108 fnd_log.string(fnd_log.level_procedure,G_MODULE_PREFIX||l_api_name,G_ENTER);
109 end if;
110
111 -- Standard Start of API savepoint
112 SAVEPOINT Create_Note_PUB;
113
114 -- Standard call to check for call compatibility.
115 IF NOT FND_API.
116 Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME)
117 THEN
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119 END IF;
120
121 -- Initialize message list if p_init_msg_list is set to TRUE.
122 IF FND_API.to_Boolean( p_init_msg_list ) THEN
123 FND_MSG_PUB.initialize;
124 END IF;
125
126 -- Initialize API return status to success
127 x_return_status := FND_API.G_RET_STS_SUCCESS;
128
129
130 -- Start of API body
131 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
132 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'Start of API body');
133 end if;
134
135 select ap_notes_s.nextval into l_note_id from dual;
136 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
137 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'l_note_id='||l_note_id);
138 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'Calling insert into AP_NOTES');
139 end if;
140 insert into AP_NOTES (
141 NOTE_ID,
142 SOURCE_OBJECT_CODE,
143 SOURCE_OBJECT_ID,
144 ENTERED_BY,
145 ENTERED_DATE,
146 NOTE_TYPE,
147 NOTES_DETAIL,
148 SOURCE_LANG,
149 CREATION_DATE,
150 CREATED_BY,
151 LAST_UPDATE_DATE,
152 LAST_UPDATED_BY,
153 LAST_UPDATE_LOGIN
154 ) values (
155 l_note_id,
156 p_source_object_code,
157 p_source_object_id,
158 nvl(p_entered_by, nvl(fnd_global.user_id, -1)),
159 p_entered_date,
160 p_note_type,
161 EMPTY_CLOB(), --p_notes_detail
162 p_source_lang,
163 p_creation_date,
164 nvl(p_entered_by,-1),--Bug#6768560
165 p_last_update_date,
166 nvl(p_entered_by,-1),--Bug#6768560
167 nvl(p_last_update_login,-1)
168 );
169
170 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
171 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'p_notes_detail='||p_notes_detail);
172 end if;
173 IF p_notes_detail is not null THEN
174 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
175 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'Calling writedatatolob()');
176 end if;
177 writeDatatoLob(l_note_id,p_notes_detail);
178 END IF;
179
180 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
181 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'End of API body');
182 end if;
183 -- End of API body.
184
185
186 -- Standard check of p_commit.
187 IF FND_API.To_Boolean(p_commit) THEN
188 COMMIT WORK;
189 END IF;
190
191 -- Standard call to get message count and if count is 1, get message info.
192 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
193 p_data => x_msg_data);
194
195 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
196 fnd_log.string(fnd_log.level_procedure,G_MODULE_PREFIX||l_api_name,G_EXIT);
197 end if;
198 EXCEPTION
199
200 WHEN FND_API.G_EXC_ERROR THEN
201 if ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
202 fnd_log.string(fnd_log.level_exception,G_MODULE_PREFIX||l_api_name,'error - FND_API.G_EXC_ERROR');
203 end if;
204 ROLLBACK TO Create_Note_PUB;
205 x_return_status := FND_API.G_RET_STS_ERROR ;
206 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
207 p_data => x_msg_data);
208
209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
210 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
211 fnd_log.string(fnd_log.level_unexpected,G_MODULE_PREFIX||l_api_name,'error - FND_API.G_EXC_UNEXPECTED_ERROR');
212 end if;
213 ROLLBACK TO Create_Note_PUB;
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
215 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
216 p_data => x_msg_data);
217
218 WHEN OTHERS THEN
219 if ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
220 fnd_log.string(fnd_log.level_exception,G_MODULE_PREFIX||l_api_name,'error - OTHERS:sqlerrm'||sqlerrm);
221 end if;
222 ROLLBACK TO Create_Note_PUB;
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
224 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
225 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
226 END IF;
227 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
228 p_data => x_msg_data);
229
230 END Create_Note;
231
232
233
234 /*===========================================================================*/
235 -- Start of comments
236 --
237 -- API NAME : Delete_Notes
238 -- PURPOSE : This deletes all notes associated to a particular
239 -- source object and note type. If the note type value
240 -- isnt passed the default action is to delete all
241 -- notes irrespective of their note types.
242 -- PRE_REQS : None
243 --
244 -- PARAMETERS :
245 -- IN -
246 -- p_api_version NUMBER Required
247 -- p_init_msg_list VARCHAR2 Optional default = FND_API.G_FALSE
248 -- p_commit VARCHAR2 Optional default = FND_API.G_FALSE
249 -- p_source_object_code VARCHAR2 Required
250 -- p_source_object_id NUMBER Required
251 -- p_note_type VARCHAR2 Optional default = G_ALL_NOTE_TYPES
252 -- OUT -
253 -- x_return_status VARCHAR2(1)
254 -- x_msg_count NUMBER
255 -- x_return_status VARCHAR2(2000)
256 -- IN OUT NO COPY -
257 --
258 -- VERSION :
259 -- Initial version 1.0
260 --
261 -- MODIFICATION HISTORY :
262 -- Date Author Description of Changes
263 -- 11-Nov-2003 V Nama Created
264 --
265 -- NOTES :
266 --
267 -- End of comments
268 /*===========================================================================*/
269 procedure Delete_Notes (
270 p_api_version IN NUMBER ,
271 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
272 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
273 x_return_status OUT NOCOPY VARCHAR2 ,
274 x_msg_count OUT NOCOPY NUMBER ,
275 x_msg_data OUT NOCOPY VARCHAR2 ,
276 p_source_object_code IN VARCHAR2 ,
277 p_source_object_id IN NUMBER ,
278 p_note_type IN VARCHAR2 := G_ALL_NOTE_TYPES
279 )
280 IS
281
282 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Notes';
283 l_api_version CONSTANT NUMBER := 1.0;
284
285 BEGIN
286 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
287 fnd_log.string(fnd_log.level_procedure,G_MODULE_PREFIX||l_api_name,G_ENTER);
288 end if;
289
290
291 -- Standard Start of API savepoint
292 SAVEPOINT Delete_Notes;
293
294 -- Standard call to check for call compatibility.
295 IF NOT FND_API.
296 Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME)
297 THEN
298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
299 END IF;
300
301 -- Initialize message list if p_init_msg_list is set to TRUE.
302 IF FND_API.to_Boolean( p_init_msg_list ) THEN
303 FND_MSG_PUB.initialize;
304 END IF;
305
306 -- Initialize API return status to success
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308
309
310 -- Start of API body
311 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
312 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'Start of API body');
313 end if;
314
315
316 IF p_note_type is null THEN
317 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
318 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'p_note_type is null');
319 end if;
320 delete from AP_NOTES
321 where SOURCE_OBJECT_CODE = p_source_object_code
322 and SOURCE_OBJECT_ID = p_source_object_id
323 and NOTE_TYPE is null;
324 ELSIF p_note_type = G_ALL_NOTE_TYPES THEN
325 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
326 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'p_note_type='||p_note_type||':deleting all notes');
327 end if;
328 delete from AP_NOTES
329 where SOURCE_OBJECT_CODE = p_source_object_code
330 and SOURCE_OBJECT_ID = p_source_object_id;
331 ELSE
332 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
333 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'p_note_type='||p_note_type||':deleting specific notes');
334 end if;
335 delete from AP_NOTES
336 where SOURCE_OBJECT_CODE = p_source_object_code
337 and SOURCE_OBJECT_ID = p_source_object_id
338 and NOTE_TYPE = p_note_type;
339 END IF;
340
341 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
342 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'End of API body');
343 end if;
344 -- End of API body.
345
346
347 -- Standard check of p_commit.
348 IF FND_API.To_Boolean(p_commit) THEN
349 COMMIT WORK;
350 END IF;
351
352 -- Standard call to get message count and if count is 1, get message info.
353 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
354 p_data => x_msg_data);
355
356 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
357 fnd_log.string(fnd_log.level_procedure,G_MODULE_PREFIX||l_api_name,G_EXIT);
358 end if;
359 EXCEPTION
360
361 WHEN FND_API.G_EXC_ERROR THEN
362 if ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
363 fnd_log.string(fnd_log.level_exception,G_MODULE_PREFIX||l_api_name,'error - FND_API.G_EXC_ERROR');
364 end if;
365 ROLLBACK TO Delete_Notes;
366 x_return_status := FND_API.G_RET_STS_ERROR ;
367 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
368 p_data => x_msg_data);
369
370 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
371 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
372 fnd_log.string(fnd_log.level_unexpected,G_MODULE_PREFIX||l_api_name,'error - FND_API.G_EXC_UNEXPECTED_ERROR');
373 end if;
374 ROLLBACK TO Delete_Notes;
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
376 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
377 p_data => x_msg_data);
378
379 WHEN OTHERS THEN
380 if ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
381 fnd_log.string(fnd_log.level_exception,G_MODULE_PREFIX||l_api_name,'error - OTHERS:sqlerrm'||sqlerrm);
382 end if;
383 ROLLBACK TO Delete_Notes;
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
386 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
387 END IF;
388 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
389 p_data => x_msg_data);
390
391 END Delete_Notes;
392
393
394
395 /*===========================================================================*/
396 -- Start of comments
397 --
398 -- API NAME : Copy_Notes
399 -- TYPE : Public
400 -- PURPOSE : This copies all notes associated to a particular
404 --
401 -- source object into new notes associated to another
402 -- source object.
403 -- PRE_REQS : None
405 -- PARAMETERS :
406 -- IN -
407 -- p_api_version NUMBER Required
408 -- p_init_msg_list VARCHAR2 Optional default = FND_API.G_FALSE
409 -- p_commit VARCHAR2 Optional default = FND_API.G_FALSE
410 -- p_old_source_object_code VARCHAR2 Required
411 -- p_old_source_object_id NUMBER Required
412 -- p_new_source_object_code VARCHAR2 Required
413 -- p_new_source_object_id NUMBER Required
414 -- OUT -
415 -- x_return_status VARCHAR2(1)
416 -- x_msg_count NUMBER
417 -- x_return_status VARCHAR2(2000)
418 -- IN OUT NO COPY -
419 --
420 -- VERSION :
421 -- Initial version 1.0
422 --
423 -- MODIFICATION HISTORY :
424 -- Date Author Description of Changes
425 -- 11-Nov-2003 V Nama Created
426 --
427 -- NOTES : If the source and destination source objects are
428 -- same, API returns after adding error message
429 -- OIE_NOTES_COPY_ON_ITSELF_ERR to the message list.
430 --
431 -- End of comments
432 /*===========================================================================*/
433 procedure Copy_Notes (
434 p_api_version IN NUMBER ,
435 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
436 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
437 x_return_status OUT NOCOPY VARCHAR2 ,
438 x_msg_count OUT NOCOPY NUMBER ,
439 x_msg_data OUT NOCOPY VARCHAR2 ,
440 p_old_source_object_code IN VARCHAR2 ,
441 p_old_source_object_id IN NUMBER ,
442 p_new_source_object_code IN VARCHAR2 ,
443 p_new_source_object_id IN NUMBER
444 )
445 IS
446
447 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Notes';
448 l_api_version CONSTANT NUMBER := 1.0;
449
450 BEGIN
451 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
452 fnd_log.string(fnd_log.level_procedure,G_MODULE_PREFIX||l_api_name,G_ENTER);
453 end if;
454
455
456 -- Standard Start of API savepoint
457 SAVEPOINT Copy_Notes;
458
459 -- Standard call to check for call compatibility.
460 IF NOT FND_API.
461 Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME)
462 THEN
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 END IF;
465
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
471 -- Initialize API return status to success
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473
474
475 -- Start of API body
476 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
477 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'Start of API body');
478 end if;
479
480 if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
481 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,
482 ' p_old_source_object_code='||p_old_source_object_code||
483 ':p_old_source_object_id='||p_old_source_object_id||
484 ':p_new_source_object_code='||p_new_source_object_code||
485 ':p_new_source_object_id='||p_new_source_object_id);
486 end if;
487
488 --verify old and new source objects are not same
489 IF p_old_source_object_code = p_new_source_object_code AND
490 p_old_source_object_id = p_new_source_object_id
491 THEN
492 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
493 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'old and new source objects are same!');
494 end if;
495 FND_MESSAGE.SET_NAME('SQLAP','OIE_NOTES_COPY_ON_ITSELF_ERR');
496 FND_MSG_PUB.Add;
497 RAISE FND_API.G_EXC_ERROR;
498 END IF;
499
500 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
501 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'Calling insert into AP_NOTES');
502 end if;
503 insert into AP_NOTES (
504 NOTE_ID,
505 SOURCE_OBJECT_CODE,
506 SOURCE_OBJECT_ID,
507 ENTERED_BY,
508 ENTERED_DATE,
509 NOTE_TYPE,
510 NOTES_DETAIL,
511 SOURCE_LANG,
512 CREATION_DATE,
513 CREATED_BY,
514 LAST_UPDATE_DATE,
515 LAST_UPDATED_BY,
516 LAST_UPDATE_LOGIN
517 )
518 select
519 AP_NOTES_S.nextval,
520 p_new_source_object_code,
521 p_new_source_object_id,
522 ENTERED_BY,
523 ENTERED_DATE,
524 NOTE_TYPE,
525 NOTES_DETAIL,
526 SOURCE_LANG,
527 CREATION_DATE,
528 CREATED_BY,
529 LAST_UPDATE_DATE,
530 LAST_UPDATED_BY,
531 LAST_UPDATE_LOGIN
532 from AP_NOTES
533 where source_object_code = p_old_source_object_code
534 and source_object_id = p_old_source_object_id;
535
536 if ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
537 fnd_log.string(fnd_log.level_statement,G_MODULE_PREFIX||l_api_name,'Start of API body');
538 end if;
539 -- End of API body.
540
541
542 -- Standard check of p_commit.
543 IF FND_API.To_Boolean(p_commit) THEN
544 COMMIT WORK;
545 END IF;
546
550
547 -- Standard call to get message count and if count is 1, get message info.
548 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
549 p_data => x_msg_data);
551 if ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
552 fnd_log.string(fnd_log.level_procedure,G_MODULE_PREFIX||l_api_name,G_EXIT);
553 end if;
554 EXCEPTION
555
556 WHEN FND_API.G_EXC_ERROR THEN
557 if ( FND_LOG.LEVEL_exception >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
558 fnd_log.string(fnd_log.level_exception,G_MODULE_PREFIX||l_api_name,'error - FND_API.G_EXC_ERROR');
559 end if;
560 ROLLBACK TO Copy_Notes;
561 x_return_status := FND_API.G_RET_STS_ERROR;
562 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
563 p_data => x_msg_data);
564
565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
566 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
567 fnd_log.string(fnd_log.level_unexpected,G_MODULE_PREFIX||l_api_name,'error - FND_API.G_EXC_UNEXPECTED_ERROR');
568 end if;
569 ROLLBACK TO Copy_Notes;
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
571 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
572 p_data => x_msg_data);
573
574 WHEN OTHERS THEN
575 if ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
576 fnd_log.string(fnd_log.level_exception,G_MODULE_PREFIX||l_api_name,'error - OTHERS:sqlerrm'||sqlerrm);
577 end if;
578 ROLLBACK TO Copy_Notes;
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
580 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
581 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
582 END IF;
583 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
584 p_data => x_msg_data);
585
586 END Copy_Notes;
587
588
589
590 END AP_NOTES_PUB;