[Home] [Help]
PACKAGE BODY: APPS.OKE_STANDARD_NOTES_PUB
Source
1 PACKAGE BODY OKE_STANDARD_NOTES_PUB AS
2 /* $Header: OKEPNOTB.pls 115.11 2002/11/20 20:46:10 who ship $ */
3 g_api_type CONSTANT VARCHAR2(4) := '_PUB';
4
5 PROCEDURE create_standard_note(
6 p_api_version IN NUMBER,
7 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2,
11 p_note_rec IN oke_note_pvt.note_rec_type,
12 x_note_rec OUT NOCOPY oke_note_pvt.note_rec_type) IS
13
14
15 l_note_rec oke_note_pvt.note_rec_type;
16
17 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_STANDARD_NOTE';
18 l_api_version CONSTANT NUMBER := 1.0;
19 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
20 l_line_number VARCHAR2(120);
21
22 BEGIN
23 -- call START_ACTIVITY to create savepoint, check compatibility
24 -- and initialize message list
25 l_return_status := OKE_API.START_ACTIVITY(
26 p_api_name => l_api_name,
27 p_pkg_name => g_pkg_name,
28 p_init_msg_list => p_init_msg_list,
29 l_api_version => l_api_version,
30 p_api_version => p_api_version,
31 p_api_type => g_api_type,
32 x_return_status => x_return_status);
33
34 -- check if activity started successfully
35 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
36 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
37 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
38 raise OKE_API.G_EXCEPTION_ERROR;
39 End If;
40
41 l_note_rec := p_note_rec;
42
43 -- call procedure in complex API
44
45 OKE_NOTE_PVT.Insert_Row(
46 p_api_version => p_api_version,
47 p_init_msg_list => p_init_msg_list,
48 x_return_status => x_return_status,
49 x_msg_count => x_msg_count,
50 x_msg_data => x_msg_data,
51 p_note_rec => l_note_rec,
52 x_note_rec => x_note_rec);
53
54
55 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
56 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
57 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
58 raise OKE_API.G_EXCEPTION_ERROR;
59 End If;
60
61 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
62 x_msg_data => x_msg_data);
63 EXCEPTION
64 when OKE_API.G_EXCEPTION_ERROR then
65 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
66 p_api_name => l_api_name,
67 p_pkg_name => g_pkg_name,
68 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
69 x_msg_count => x_msg_count,
70 x_msg_data => x_msg_data,
71 p_api_type => g_api_type);
72
73 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
74 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
75 p_api_name => l_api_name,
76 p_pkg_name => g_pkg_name,
77 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
78 x_msg_count => x_msg_count,
79 x_msg_data => x_msg_data,
80 p_api_type => g_api_type);
81
82 when OTHERS then
83 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
84 p_api_name => l_api_name,
85 p_pkg_name => g_pkg_name,
86 p_exc_name => 'OTHERS',
87 x_msg_count => x_msg_count,
88 x_msg_data => x_msg_data,
89 p_api_type => g_api_type);
90
91 END create_standard_note;
92
93
94
95 PROCEDURE create_standard_note(
96 p_api_version IN NUMBER,
97 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_msg_count OUT NOCOPY NUMBER,
100 x_msg_data OUT NOCOPY VARCHAR2,
101 p_note_tbl IN oke_note_pvt.note_tbl_type,
102 x_note_tbl OUT NOCOPY oke_note_pvt.note_tbl_type) IS
103
104 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_STANDARD_NOTE';
105 l_api_version CONSTANT NUMBER := 1.0;
106 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
107 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
108 i NUMBER;
109 l_note_tbl oke_note_pvt.note_tbl_type;
110 BEGIN
111 -- call START_ACTIVITY to create savepoint, check compatibility
112 -- and initialize message list
113 l_return_status := OKE_API.START_ACTIVITY(
114 p_api_name => l_api_name,
115 p_pkg_name => g_pkg_name,
116 p_init_msg_list => p_init_msg_list,
117 l_api_version => l_api_version,
118 p_api_version => p_api_version,
119 p_api_type => g_api_type,
120 x_return_status => x_return_status);
121
122 -- check if activity started successfully
123 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
124 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
125 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
126 raise OKE_API.G_EXCEPTION_ERROR;
127 End If;
128
129 OKE_NOTE_PVT.Insert_Row(
130 p_api_version => p_api_version,
131 p_init_msg_list => p_init_msg_list,
132 x_return_status => x_return_status,
133 x_msg_count => x_msg_count,
134 x_msg_data => x_msg_data,
135 p_note_tbl => p_note_tbl,
136 x_note_tbl => x_note_tbl);
137
138
139 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
140 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
141 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
142 raise OKE_API.G_EXCEPTION_ERROR;
143 End If;
144
145 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
146 x_msg_data => x_msg_data);
147 EXCEPTION
148 when OKE_API.G_EXCEPTION_ERROR then
149 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
150 p_api_name => l_api_name,
151 p_pkg_name => g_pkg_name,
152 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
153 x_msg_count => x_msg_count,
154 x_msg_data => x_msg_data,
155 p_api_type => g_api_type);
156
157 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
158 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
159 p_api_name => l_api_name,
160 p_pkg_name => g_pkg_name,
161 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
162 x_msg_count => x_msg_count,
163 x_msg_data => x_msg_data,
164 p_api_type => g_api_type);
165
166 when OTHERS then
167 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
168 p_api_name => l_api_name,
169 p_pkg_name => g_pkg_name,
170 p_exc_name => 'OTHERS',
171 x_msg_count => x_msg_count,
172 x_msg_data => x_msg_data,
173 p_api_type => g_api_type);
174
175 END create_standard_note;
176
177 PROCEDURE update_standard_note(
178 p_api_version IN NUMBER,
179 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
180 x_return_status OUT NOCOPY VARCHAR2,
181 x_msg_count OUT NOCOPY NUMBER,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 p_note_rec IN oke_note_pvt.note_rec_type,
184 x_note_rec OUT NOCOPY oke_note_pvt.note_rec_type) IS
185
186 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_STANDARD_NOTE';
187 l_api_version CONSTANT NUMBER := 1.0;
188 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
189 BEGIN
190 -- call START_ACTIVITY to create savepoint, check compatibility
191 -- and initialize message list
192
193 l_return_status := OKE_API.START_ACTIVITY(
194 p_api_name => l_api_name,
195 p_pkg_name => g_pkg_name,
196 p_init_msg_list => p_init_msg_list,
197 l_api_version => l_api_version,
198 p_api_version => p_api_version,
199 p_api_type => g_api_type,
200 x_return_status => x_return_status);
201
202 -- check if activity started successfully
203 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
204 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
205 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
206 raise OKE_API.G_EXCEPTION_ERROR;
207 End If;
208
209 -- call complex api
210
211 OKE_NOTE_PVT.Update_Row(
212 p_api_version => p_api_version,
213 p_init_msg_list => p_init_msg_list,
214 x_return_status => x_return_status,
215 x_msg_count => x_msg_count,
216 x_msg_data => x_msg_data,
217 p_note_rec => p_note_rec,
218 x_note_rec => x_note_rec);
219
220
221 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
222 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
223 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
224 raise OKE_API.G_EXCEPTION_ERROR;
225 End If;
226
227 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
228 x_msg_data => x_msg_data);
229
230 EXCEPTION
231 when OKE_API.G_EXCEPTION_ERROR then
232 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
233 p_api_name => l_api_name,
234 p_pkg_name => g_pkg_name,
235 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
236 x_msg_count => x_msg_count,
237 x_msg_data => x_msg_data,
238 p_api_type => g_api_type);
239
240 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
241 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
242 p_api_name => l_api_name,
243 p_pkg_name => g_pkg_name,
244 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
245 x_msg_count => x_msg_count,
246 x_msg_data => x_msg_data,
247 p_api_type => g_api_type);
248
249 when OTHERS then
250 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
251 p_api_name => l_api_name,
252 p_pkg_name => g_pkg_name,
253 p_exc_name => 'OTHERS',
254 x_msg_count => x_msg_count,
255 x_msg_data => x_msg_data,
256 p_api_type => g_api_type);
257
258 END update_standard_note;
259
260
261 PROCEDURE update_standard_note(
262 p_api_version IN NUMBER,
263 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
264 x_return_status OUT NOCOPY VARCHAR2,
265 x_msg_count OUT NOCOPY NUMBER,
266 x_msg_data OUT NOCOPY VARCHAR2,
267 p_note_tbl IN oke_note_pvt.note_tbl_type,
268 x_note_tbl OUT NOCOPY oke_note_pvt.note_tbl_type) IS
269
270 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_STANDARD_NOTE';
271 l_api_version CONSTANT NUMBER := 1.0;
272 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
273 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
274 i NUMBER;
275 BEGIN
276 -- call START_ACTIVITY to create savepoint, check compatibility
277 -- and initialize message list
278
279 l_return_status := OKE_API.START_ACTIVITY(
280 p_api_name => l_api_name,
281 p_pkg_name => g_pkg_name,
282 p_init_msg_list => p_init_msg_list,
283 l_api_version => l_api_version,
284 p_api_version => p_api_version,
285 p_api_type => g_api_type,
286 x_return_status => x_return_status);
287
288 -- check if activity started successfully
289 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
290 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
291 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
292 raise OKE_API.G_EXCEPTION_ERROR;
293
294 End If;
295
296 OKE_NOTE_PVT.Update_Row(
297 p_api_version => p_api_version,
298 p_init_msg_list => p_init_msg_list,
299 x_return_status => x_return_status,
300 x_msg_count => x_msg_count,
301 x_msg_data => x_msg_data,
302 p_note_tbl => p_note_tbl,
303 x_note_tbl => x_note_tbl);
304
305
306
307 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
308 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
309 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
310 raise OKE_API.G_EXCEPTION_ERROR;
311 End If;
312
313 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
314 x_msg_data => x_msg_data);
315
316 EXCEPTION
317 when OKE_API.G_EXCEPTION_ERROR then
318 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
319 p_api_name => l_api_name,
320 p_pkg_name => g_pkg_name,
321 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
322 x_msg_count => x_msg_count,
323 x_msg_data => x_msg_data,
324 p_api_type => g_api_type);
325
326 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
327 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
328 p_api_name => l_api_name,
329 p_pkg_name => g_pkg_name,
330 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
331 x_msg_count => x_msg_count,
332 x_msg_data => x_msg_data,
333 p_api_type => g_api_type);
334
335 when OTHERS then
336 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
337 p_api_name => l_api_name,
338 p_pkg_name => g_pkg_name,
339 p_exc_name => 'OTHERS',
340 x_msg_count => x_msg_count,
341 x_msg_data => x_msg_data,
342 p_api_type => g_api_type);
343
344 END update_standard_note;
345
346
347
348 PROCEDURE validate_standard_note(
349 p_api_version IN NUMBER,
350 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
351 x_return_status OUT NOCOPY VARCHAR2,
352 x_msg_count OUT NOCOPY NUMBER,
353 x_msg_data OUT NOCOPY VARCHAR2,
354 p_note_rec IN oke_note_pvt.note_rec_type) IS
355
356 l_note_rec oke_note_pvt.note_rec_type;
357 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_STANDARD_NOTE';
358 l_api_version CONSTANT NUMBER := 1.0;
359 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
360
361 BEGIN
362 -- call START_ACTIVITY to create savepoint, check compatibility
363 -- and initialize message list
364 l_return_status := OKE_API.START_ACTIVITY(
365 p_api_name => l_api_name,
366 p_pkg_name => g_pkg_name,
367 p_init_msg_list => p_init_msg_list,
368 l_api_version => l_api_version,
369 p_api_version => p_api_version,
370 p_api_type => g_api_type,
371 x_return_status => x_return_status);
372
373 -- check if activity started successfully
374 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
375 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
376 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
377 raise OKE_API.G_EXCEPTION_ERROR;
378 End If;
379
380 -- call BEFORE user hook
381 l_note_rec := p_note_rec;
382
383 -- call complex API
384
385 OKE_NOTE_PVT.Validate_Row(
386 p_api_version => p_api_version,
387 p_init_msg_list => p_init_msg_list,
388 x_return_status => x_return_status,
389 x_msg_count => x_msg_count,
390 x_msg_data => x_msg_data,
391 p_note_rec => p_note_rec);
392
393
394 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
395 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
396 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
397 raise OKE_API.G_EXCEPTION_ERROR;
398 End If;
399
400 -- end activity
401 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
402 x_msg_data => x_msg_data);
403 EXCEPTION
404 when OKE_API.G_EXCEPTION_ERROR then
405 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
406 p_api_name => l_api_name,
407 p_pkg_name => g_pkg_name,
408 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
409 x_msg_count => x_msg_count,
410 x_msg_data => x_msg_data,
411 p_api_type => g_api_type);
412
413 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
414 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
415 p_api_name => l_api_name,
416 p_pkg_name => g_pkg_name,
417 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
418 x_msg_count => x_msg_count,
419 x_msg_data => x_msg_data,
420 p_api_type => g_api_type);
421
422 when OTHERS then
423 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
424 p_api_name => l_api_name,
425 p_pkg_name => g_pkg_name,
426 p_exc_name => 'OTHERS',
427 x_msg_count => x_msg_count,
428 x_msg_data => x_msg_data,
429 p_api_type => g_api_type);
430
431 END validate_standard_note;
432
433 PROCEDURE validate_standard_note(
434 p_api_version IN NUMBER,
435 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
436 x_return_status OUT NOCOPY VARCHAR2,
437 x_msg_count OUT NOCOPY NUMBER,
438 x_msg_data OUT NOCOPY VARCHAR2,
439 p_note_tbl IN oke_note_pvt.note_tbl_type) IS
440
441 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_STANDARD_NOTE';
442
443 l_api_version CONSTANT NUMBER := 1.0;
444 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
445 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
446 i NUMBER;
447 l_note_tbl oke_note_pvt.note_tbl_type := p_note_tbl;
448 BEGIN
449 -- call START_ACTIVITY to create savepoint, check compatibility
450 -- and initialize message list
451 l_return_status := OKE_API.START_ACTIVITY(
452 p_api_name => l_api_name,
453 p_pkg_name => g_pkg_name,
454 p_init_msg_list => p_init_msg_list,
455 l_api_version => l_api_version,
456 p_api_version => p_api_version,
457 p_api_type => g_api_type,
458 x_return_status => x_return_status);
459
460 -- check if activity started successfully
461 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
462 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
463 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
464 raise OKE_API.G_EXCEPTION_ERROR;
465 End If;
466
467
468 OKE_NOTE_PVT.Validate_Row(
469 p_api_version => p_api_version,
470 p_init_msg_list => p_init_msg_list,
471 x_return_status => x_return_status,
472 x_msg_count => x_msg_count,
473 x_msg_data => x_msg_data,
474 p_note_tbl => p_note_tbl);
475
476
477 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
478 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
479 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
480 raise OKE_API.G_EXCEPTION_ERROR;
481 End If;
482
483 -- end activity
484 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
485 x_msg_data => x_msg_data);
486 EXCEPTION
487 when OKE_API.G_EXCEPTION_ERROR then
488 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
489 p_api_name => l_api_name,
490 p_pkg_name => g_pkg_name,
491 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
492 x_msg_count => x_msg_count,
493 x_msg_data => x_msg_data,
494 p_api_type => g_api_type);
495
496 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
497 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
498 p_api_name => l_api_name,
499 p_pkg_name => g_pkg_name,
500 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
501 x_msg_count => x_msg_count,
502 x_msg_data => x_msg_data,
503 p_api_type => g_api_type);
504
505 when OTHERS then
506 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
507 p_api_name => l_api_name,
508 p_pkg_name => g_pkg_name,
509 p_exc_name => 'OTHERS',
510 x_msg_count => x_msg_count,
511 x_msg_data => x_msg_data,
512 p_api_type => g_api_type);
513
514 END validate_standard_note;
515
516
517
518
519 PROCEDURE delete_standard_note(
520
521 p_api_version IN NUMBER,
522 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
523 x_return_status OUT NOCOPY VARCHAR2,
524 x_msg_count OUT NOCOPY NUMBER,
525 x_msg_data OUT NOCOPY VARCHAR2,
526 p_note_rec IN oke_note_pvt.note_rec_type) IS
527
528 l_note_rec oke_note_pvt.note_rec_type;
529 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_STANDARD_NOTE';
530 l_api_version CONSTANT NUMBER := 1.0;
531 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
532 BEGIN
533 -- call START_ACTIVITY to create savepoint, check compatibility
534 -- and initialize message list
535 l_return_status := OKE_API.START_ACTIVITY(
536 p_api_name => l_api_name,
537 p_pkg_name => g_pkg_name,
538 p_init_msg_list => p_init_msg_list,
539 l_api_version => l_api_version,
540 p_api_version => p_api_version,
541 p_api_type => g_api_type,
542 x_return_status => x_return_status);
543
544 -- check if activity started successfully
545 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
546 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
547 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
548 raise OKE_API.G_EXCEPTION_ERROR;
549 End If;
550
551 l_note_rec := p_note_rec;
552
553 -- call complex api
554
555 OKE_NOTE_PVT.delete_row(
556 p_api_version => p_api_version,
557 p_init_msg_list => p_init_msg_list,
558 x_return_status => x_return_status,
559 x_msg_count => x_msg_count,
560 x_msg_data => x_msg_data,
561 p_note_rec => p_note_rec);
562
563
564 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
565 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
566 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
567 raise OKE_API.G_EXCEPTION_ERROR;
568 End If;
569
570
571 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
572 x_msg_data => x_msg_data);
573 EXCEPTION
574 when OKE_API.G_EXCEPTION_ERROR then
575 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
576 p_api_name => l_api_name,
577 p_pkg_name => g_pkg_name,
578 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
579 x_msg_count => x_msg_count,
580 x_msg_data => x_msg_data,
581 p_api_type => g_api_type);
582
583 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
584 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
585 p_api_name => l_api_name,
586 p_pkg_name => g_pkg_name,
587 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
588 x_msg_count => x_msg_count,
589 x_msg_data => x_msg_data,
590 p_api_type => g_api_type);
591
592 when OTHERS then
593 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
594 p_api_name => l_api_name,
595 p_pkg_name => g_pkg_name,
596 p_exc_name => 'OTHERS',
597 x_msg_count => x_msg_count,
598 x_msg_data => x_msg_data,
599 p_api_type => g_api_type);
600
601 END delete_standard_note;
602
603 PROCEDURE delete_standard_note(
604 p_api_version IN NUMBER,
605 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
606 x_return_status OUT NOCOPY VARCHAR2,
607 x_msg_count OUT NOCOPY NUMBER,
608 x_msg_data OUT NOCOPY VARCHAR2,
609 p_note_tbl IN oke_note_pvt.note_tbl_type) IS
610
611 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_STANDARD_NOTE';
612 l_api_version CONSTANT NUMBER := 1.0;
613 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
614 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
615 i NUMBER;
616 BEGIN
617 -- call START_ACTIVITY to create savepoint, check compatibility
618 -- and initialize message list
619 l_return_status := OKE_API.START_ACTIVITY(
620 p_api_name => l_api_name,
621 p_pkg_name => g_pkg_name,
622 p_init_msg_list => p_init_msg_list,
623 l_api_version => l_api_version,
624 p_api_version => p_api_version,
625 p_api_type => g_api_type,
626 x_return_status => x_return_status);
627
628 -- check if activity started successfully
629 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
630 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
631 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
632 raise OKE_API.G_EXCEPTION_ERROR;
633 End If;
634
635
636 OKE_NOTE_PVT.Delete_Row(
637 p_api_version => p_api_version,
638 p_init_msg_list => p_init_msg_list,
639 x_return_status => x_return_status,
640 x_msg_count => x_msg_count,
641 x_msg_data => x_msg_data,
642 p_note_tbl => p_note_tbl);
643
644
645
646 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
647 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
648 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
649 raise OKE_API.G_EXCEPTION_ERROR;
650 End If;
651
652 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
653 x_msg_data => x_msg_data);
654 EXCEPTION
655 when OKE_API.G_EXCEPTION_ERROR then
656 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
657 p_api_name => l_api_name,
658 p_pkg_name => g_pkg_name,
659 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
660 x_msg_count => x_msg_count,
661 x_msg_data => x_msg_data,
662 p_api_type => g_api_type);
663
664 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
665 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
666 p_api_name => l_api_name,
667 p_pkg_name => g_pkg_name,
668 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data,
671 p_api_type => g_api_type);
672
673 when OTHERS then
674 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
675 p_api_name => l_api_name,
676 p_pkg_name => g_pkg_name,
677 p_exc_name => 'OTHERS',
678 x_msg_count => x_msg_count,
679 x_msg_data => x_msg_data,
680 p_api_type => g_api_type);
681
682 END delete_standard_note;
683
684 PROCEDURE delete_standard_note(
685
686 p_api_version IN NUMBER,
687 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
688 x_return_status OUT NOCOPY VARCHAR2,
689 x_msg_count OUT NOCOPY NUMBER,
690 x_msg_data OUT NOCOPY VARCHAR2,
691 p_note_id IN NUMBER) IS
692
693 l_note_rec oke_note_pvt.note_rec_type;
694 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_STANDARD_NOTE';
695 l_api_version CONSTANT NUMBER := 1.0;
696 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
697 l_check_num1 NUMBER;
698 l_check_num2 NUMBER;
699
700 BEGIN
701 -- call START_ACTIVITY to create savepoint, check compatibility
702 -- and initialize message list
703 l_return_status := OKE_API.START_ACTIVITY(
704 p_api_name => l_api_name,
705 p_pkg_name => g_pkg_name,
706 p_init_msg_list => p_init_msg_list,
707 l_api_version => l_api_version,
708 p_api_version => p_api_version,
709 p_api_type => g_api_type,
710 x_return_status => x_return_status);
711
712 -- check if activity started successfully
713 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
714 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
715 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
716 raise OKE_API.G_EXCEPTION_ERROR;
717 End If;
718
719 SELECT COUNT(*) INTO l_check_num1 FROM OKE_K_STANDARD_NOTES_B
720 WHERE STANDARD_NOTES_ID = p_note_id;
721
722 SELECT COUNT(*) INTO l_check_num2 FROM OKE_K_STANDARD_NOTES_TL
723 WHERE STANDARD_NOTES_ID = p_note_id;
724
725 If(l_check_num1<1)OR(l_check_num2<1) then
726 raise OKE_API.G_EXCEPTION_ERROR;
727 End If;
728
729 l_note_rec.STANDARD_NOTES_ID := p_note_id;
730
731 -- call complex api
732
733 OKE_NOTE_PVT.delete_row(
734 p_api_version => p_api_version,
735 p_init_msg_list => p_init_msg_list,
736 x_return_status => x_return_status,
737 x_msg_count => x_msg_count,
738 x_msg_data => x_msg_data,
739 p_note_rec => l_note_rec);
740
741
742 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
743 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
744 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
745 raise OKE_API.G_EXCEPTION_ERROR;
746 End If;
747
748
749 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
750 x_msg_data => x_msg_data);
751 EXCEPTION
752 when OKE_API.G_EXCEPTION_ERROR then
753 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
754 p_api_name => l_api_name,
755 p_pkg_name => g_pkg_name,
756 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
757 x_msg_count => x_msg_count,
758 x_msg_data => x_msg_data,
759 p_api_type => g_api_type);
760
761 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
762 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
763 p_api_name => l_api_name,
764 p_pkg_name => g_pkg_name,
765 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
766 x_msg_count => x_msg_count,
767 x_msg_data => x_msg_data,
768 p_api_type => g_api_type);
769
770 when OTHERS then
771 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
772 p_api_name => l_api_name,
773 p_pkg_name => g_pkg_name,
774 p_exc_name => 'OTHERS',
775 x_msg_count => x_msg_count,
776 x_msg_data => x_msg_data,
777 p_api_type => g_api_type);
778
779 END delete_standard_note;
780
781
782
783 PROCEDURE delete_standard_note(
784
785 p_api_version IN NUMBER,
786 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
787 x_return_status OUT NOCOPY VARCHAR2,
788 x_msg_count OUT NOCOPY NUMBER,
789 x_msg_data OUT NOCOPY VARCHAR2,
790 p_hdr_id IN NUMBER,
791 p_cle_id IN NUMBER,
792 p_del_id IN NUMBER ) IS
793
794 l_note_rec oke_note_pvt.note_rec_type;
795 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_STANDARD_NOTE';
796 l_api_version CONSTANT NUMBER := 1.0;
797 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
798 l_check_num1 NUMBER;
799 l_check_num2 NUMBER;
800
801 BEGIN
802 -- call START_ACTIVITY to create savepoint, check compatibility
803 -- and initialize message list
804 l_return_status := OKE_API.START_ACTIVITY(
805 p_api_name => l_api_name,
806 p_pkg_name => g_pkg_name,
807 p_init_msg_list => p_init_msg_list,
808 l_api_version => l_api_version,
809 p_api_version => p_api_version,
810 p_api_type => g_api_type,
811 x_return_status => x_return_status);
812
813 -- check if activity started successfully
814 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
815 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
816 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
817 raise OKE_API.G_EXCEPTION_ERROR;
818 End If;
819
820 If (p_del_id IS NOT NULL) Then
821 OKE_NOTE_PVT.delete_row(
822 p_api_version => p_api_version,
823 p_init_msg_list => p_init_msg_list,
824 x_return_status => x_return_status,
825 x_msg_count => x_msg_count,
826 x_msg_data => x_msg_data,
827 p_del_id => p_del_id);
828
829
830 ElsIf (p_cle_id IS NOT NULL) Then
831 OKE_NOTE_PVT.delete_row(
832 p_api_version => p_api_version,
833 p_init_msg_list => p_init_msg_list,
834 x_return_status => x_return_status,
835 x_msg_count => x_msg_count,
836 x_msg_data => x_msg_data,
837 p_cle_id => p_cle_id);
838
839
840 ElsIf (p_hdr_id IS NOT NULL) Then
841 OKE_NOTE_PVT.delete_row(
842 p_api_version => p_api_version,
843 p_init_msg_list => p_init_msg_list,
844 x_return_status => x_return_status,
845 x_msg_count => x_msg_count,
846 x_msg_data => x_msg_data,
847 p_hdr_id => p_hdr_id);
848
849 Else
850 raise OKE_API.G_EXCEPTION_ERROR;
851 End If;
852
853
854
855 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
856 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
857 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
858 raise OKE_API.G_EXCEPTION_ERROR;
859 End If;
860
861 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data);
863 EXCEPTION
864 when OKE_API.G_EXCEPTION_ERROR then
865 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
866 p_api_name => l_api_name,
867 p_pkg_name => g_pkg_name,
868 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
869 x_msg_count => x_msg_count,
870 x_msg_data => x_msg_data,
871 p_api_type => g_api_type);
872
873 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
874 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
875 p_api_name => l_api_name,
876 p_pkg_name => g_pkg_name,
877 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
878 x_msg_count => x_msg_count,
879 x_msg_data => x_msg_data,
880 p_api_type => g_api_type);
881
882 when OTHERS then
883 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
884 p_api_name => l_api_name,
885 p_pkg_name => g_pkg_name,
886 p_exc_name => 'OTHERS',
887 x_msg_count => x_msg_count,
888 x_msg_data => x_msg_data,
889 p_api_type => g_api_type);
890
891 END delete_standard_note;
892
893
894 PROCEDURE copy_standard_note(
895 p_api_version IN NUMBER,
896 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
897 x_return_status OUT NOCOPY VARCHAR2,
898 x_msg_count OUT NOCOPY NUMBER,
899 x_msg_data OUT NOCOPY VARCHAR2,
900 p_from_hdr_id IN NUMBER,
901 p_to_hdr_id IN NUMBER,
902 p_from_cle_id IN NUMBER,
903 p_to_cle_id IN NUMBER,
904 p_from_del_id IN NUMBER,
905 p_to_del_id IN NUMBER,
906 default_flag IN VARCHAR2
907 ) IS
908
909 l_note_tbl oke_note_pvt.note_tbl_type;
910 l_note_rec oke_note_pvt.note_rec_type;
911 l_api_name CONSTANT VARCHAR2(30) := 'COPY_STANDARD_NOTE';
912 l_api_version CONSTANT NUMBER := 1.0;
913 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
914 l_rec_num NUMBER:=0;
915
916 x_note_tbl oke_note_pvt.note_tbl_type;
917
918
919 CURSOR note_del_csr (p_id IN NUMBER) IS
920 SELECT unique
921 b.STANDARD_NOTES_ID ,
922 b.CREATION_DATE ,
923 b.CREATED_BY ,
924 b.LAST_UPDATE_DATE ,
925 b.LAST_UPDATED_BY ,
926 b.LAST_UPDATE_LOGIN ,
927 b.HEADER_ID ,
928
929 b.LINE_ID ,
930
931 b.DELIVERABLE_ID ,
932 b.TYPE_CODE ,
933 b.ATTRIBUTE_CATEGORY ,
934 b.ATTRIBUTE1 ,
935 b.ATTRIBUTE2 ,
936 b.ATTRIBUTE3 ,
937 b.ATTRIBUTE4 ,
938 b.ATTRIBUTE5 ,
939 b.ATTRIBUTE6 ,
940 b.ATTRIBUTE7 ,
941 b.ATTRIBUTE8 ,
942 b.ATTRIBUTE9 ,
943 b.ATTRIBUTE10 ,
944 b.ATTRIBUTE11 ,
945 b.ATTRIBUTE12 ,
946 b.ATTRIBUTE13 ,
947 b.ATTRIBUTE14 ,
948 b.ATTRIBUTE15 ,
949 b.SFWT_FLAG ,
950 b.DESCRIPTION ,
951 b.NAME ,
952 b.TEXT ,
953 b.default_flag
954 FROM OKE_K_STANDARD_NOTES_VL b
955 WHERE b.DELIVERABLE_ID = p_id
956 AND b.LINE_ID IS NULL;
957
958 CURSOR note_cle_csr (p_id IN NUMBER) IS
959 SELECT unique
960 b.STANDARD_NOTES_ID ,
961 b.CREATION_DATE ,
962 b.CREATED_BY ,
963 b.LAST_UPDATE_DATE ,
964 b.LAST_UPDATED_BY ,
965 b.LAST_UPDATE_LOGIN ,
966 b.HEADER_ID ,
967 b.LINE_ID ,
968 b.DELIVERABLE_ID ,
969 b.TYPE_CODE ,
970 b.ATTRIBUTE_CATEGORY ,
971 b.ATTRIBUTE1 ,
972 b.ATTRIBUTE2 ,
973 b.ATTRIBUTE3 ,
974 b.ATTRIBUTE4 ,
975 b.ATTRIBUTE5 ,
976 b.ATTRIBUTE6 ,
977 b.ATTRIBUTE7 ,
978 b.ATTRIBUTE8 ,
979 b.ATTRIBUTE9 ,
980 b.ATTRIBUTE10 ,
981 b.ATTRIBUTE11 ,
982 b.ATTRIBUTE12 ,
983 b.ATTRIBUTE13 ,
984 b.ATTRIBUTE14 ,
985 b.ATTRIBUTE15 ,
986 b.SFWT_FLAG ,
987 b.DESCRIPTION ,
988 b.NAME ,
989 b.TEXT ,
990 b.default_flag
991 FROM OKE_K_STANDARD_NOTES_VL B
992 WHERE b.LINE_ID = p_id
993 AND b.DELIVERABLE_ID IS NULL;
994
995 CURSOR note_hdr_csr (p_id IN NUMBER) IS
996 SELECT unique
997 b.STANDARD_NOTES_ID ,
998 b.CREATION_DATE ,
999 b.CREATED_BY ,
1000 b.LAST_UPDATE_DATE ,
1001 b.LAST_UPDATED_BY ,
1002 b.LAST_UPDATE_LOGIN ,
1003 b.HEADER_ID ,
1004 b.LINE_ID ,
1005 b.DELIVERABLE_ID ,
1006 b.TYPE_CODE ,
1007 b.ATTRIBUTE_CATEGORY ,
1008 b.ATTRIBUTE1 ,
1009 b.ATTRIBUTE2 ,
1010 b.ATTRIBUTE3 ,
1011 b.ATTRIBUTE4 ,
1012 b.ATTRIBUTE5 ,
1013 b.ATTRIBUTE6 ,
1014 b.ATTRIBUTE7 ,
1015 b.ATTRIBUTE8 ,
1016 b.ATTRIBUTE9 ,
1017 b.ATTRIBUTE10 ,
1018 b.ATTRIBUTE11 ,
1019 b.ATTRIBUTE12 ,
1020 b.ATTRIBUTE13 ,
1021 b.ATTRIBUTE14 ,
1022 b.ATTRIBUTE15 ,
1023 b.SFWT_FLAG ,
1024 b.DESCRIPTION ,
1025 b.NAME ,
1026 b.TEXT ,
1027 b.default_flag
1028 FROM OKE_K_STANDARD_NOTES_VL B
1029 WHERE b.HEADER_ID = p_id
1030 AND b.DELIVERABLE_ID IS NULL AND b.LINE_ID IS NULL;
1031
1032 BEGIN
1033
1034 -- call START_ACTIVITY to create savepoint, check compatibility
1035 -- and initialize message list
1036 l_return_status := OKE_API.START_ACTIVITY(
1037 p_api_name => l_api_name,
1038 p_pkg_name => g_pkg_name,
1039 p_init_msg_list => p_init_msg_list,
1040 l_api_version => l_api_version,
1041 p_api_version => p_api_version,
1042 p_api_type => g_api_type,
1043 x_return_status => x_return_status);
1044
1045 -- check if activity started successfully
1046 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1047 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1048 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1049 raise OKE_API.G_EXCEPTION_ERROR;
1050 End If;
1051
1052 IF(
1053 (p_from_hdr_id IS NOT NULL)AND
1054 (p_from_cle_id IS NULL)AND
1055 (p_from_del_id IS NULL) ) THEN
1056
1057
1058 OPEN note_hdr_csr (p_from_hdr_id);
1059 LOOP
1060 FETCH note_hdr_csr INTO
1061 l_note_rec.STANDARD_NOTES_ID ,
1062 l_note_rec.CREATION_DATE ,
1063 l_note_rec.CREATED_BY ,
1064 l_note_rec.LAST_UPDATE_DATE ,
1065 l_note_rec.LAST_UPDATED_BY ,
1066 l_note_rec.LAST_UPDATE_LOGIN ,
1067 l_note_rec.K_HEADER_ID ,
1068 l_note_rec.K_LINE_ID ,
1069 l_note_rec.DELIVERABLE_ID ,
1070 l_note_rec.TYPE_CODE ,
1071 l_note_rec.ATTRIBUTE_CATEGORY ,
1072 l_note_rec.ATTRIBUTE1 ,
1073 l_note_rec.ATTRIBUTE2 ,
1074 l_note_rec.ATTRIBUTE3 ,
1075 l_note_rec.ATTRIBUTE4 ,
1076 l_note_rec.ATTRIBUTE5 ,
1077 l_note_rec.ATTRIBUTE6 ,
1078 l_note_rec.ATTRIBUTE7 ,
1079 l_note_rec.ATTRIBUTE8 ,
1080 l_note_rec.ATTRIBUTE9 ,
1081 l_note_rec.ATTRIBUTE10 ,
1082 l_note_rec.ATTRIBUTE11 ,
1083 l_note_rec.ATTRIBUTE12 ,
1084 l_note_rec.ATTRIBUTE13 ,
1085 l_note_rec.ATTRIBUTE14 ,
1086 l_note_rec.ATTRIBUTE15 ,
1087 l_note_rec.SFWT_FLAG ,
1088 l_note_rec.DESCRIPTION ,
1089 l_note_rec.NAME ,
1090 l_note_rec.TEXT ,
1091 l_note_rec.default_flag ;
1092 EXIT WHEN note_hdr_csr%NOTFOUND;
1093
1094
1095 IF (default_flag='N')OR((default_flag='Y')AND(l_note_rec.default_flag='Y')) THEN
1096
1097 l_rec_num := l_rec_num+1;
1098 l_note_tbl(l_rec_num) := l_note_rec;
1099
1100 -- add a bit of logic to figure destination (10/11/2000)
1101
1102 IF p_to_hdr_id IS NOT NULL THEN
1103 l_note_tbl(l_rec_num).K_HEADER_ID := p_to_hdr_id;
1104 END IF;
1105
1106 IF p_to_cle_id IS NOT NULL THEN
1107 l_note_tbl(l_rec_num).K_LINE_ID := p_to_cle_id;
1108 END IF;
1109
1110 IF p_to_del_id IS NOT NULL THEN
1111 l_note_tbl(l_rec_num).DELIVERABLE_ID := p_to_del_id;
1112 END IF;
1113
1114 END IF;
1115
1116 END LOOP;
1117
1118 CLOSE note_hdr_csr;
1119
1120
1121 OKE_NOTE_PVT.Insert_Row(
1122 p_api_version => p_api_version,
1123 p_init_msg_list => p_init_msg_list,
1124 x_return_status => x_return_status,
1125 x_msg_count => x_msg_count,
1126 x_msg_data => x_msg_data,
1127 p_note_tbl => l_note_tbl,
1128 x_note_tbl => x_note_tbl);
1129
1130 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1131 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1132 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1133 raise OKE_API.G_EXCEPTION_ERROR;
1134 End If;
1135
1136
1137 ELSIF(
1138 ---- (p_from_hdr_id IS NULL)AND
1139 (p_from_cle_id IS NOT NULL)AND
1140 (p_from_del_id IS NULL) ) THEN
1141
1142
1143 OPEN note_cle_csr (p_from_cle_id);
1144 LOOP
1145 FETCH note_cle_csr INTO
1146 l_note_rec.STANDARD_NOTES_ID ,
1147 l_note_rec.CREATION_DATE ,
1148 l_note_rec.CREATED_BY ,
1149 l_note_rec.LAST_UPDATE_DATE ,
1150 l_note_rec.LAST_UPDATED_BY ,
1151 l_note_rec.LAST_UPDATE_LOGIN ,
1152 l_note_rec.K_HEADER_ID ,
1153 l_note_rec.K_LINE_ID ,
1154 l_note_rec.DELIVERABLE_ID ,
1155 l_note_rec.TYPE_CODE ,
1156 l_note_rec.ATTRIBUTE_CATEGORY ,
1157 l_note_rec.ATTRIBUTE1 ,
1158 l_note_rec.ATTRIBUTE2 ,
1159 l_note_rec.ATTRIBUTE3 ,
1160 l_note_rec.ATTRIBUTE4 ,
1161 l_note_rec.ATTRIBUTE5 ,
1162 l_note_rec.ATTRIBUTE6 ,
1163 l_note_rec.ATTRIBUTE7 ,
1164 l_note_rec.ATTRIBUTE8 ,
1165 l_note_rec.ATTRIBUTE9 ,
1166 l_note_rec.ATTRIBUTE10 ,
1167 l_note_rec.ATTRIBUTE11 ,
1168 l_note_rec.ATTRIBUTE12 ,
1169 l_note_rec.ATTRIBUTE13 ,
1170 l_note_rec.ATTRIBUTE14 ,
1171 l_note_rec.ATTRIBUTE15 ,
1172 l_note_rec.SFWT_FLAG ,
1173 l_note_rec.DESCRIPTION ,
1174 l_note_rec.NAME ,
1175 l_note_rec.TEXT ,
1176 l_note_rec.default_flag ;
1177 EXIT WHEN note_cle_csr%NOTFOUND;
1178
1179
1180 IF (default_flag='N')OR((default_flag='Y')AND(l_note_rec.default_flag='Y')) THEN
1181
1182 l_rec_num := l_rec_num+1;
1183 l_note_tbl(l_rec_num) := l_note_rec;
1184
1185
1186 -- add a bit of logic to figure destination (10/11/2000)
1187
1188 IF p_to_hdr_id IS NOT NULL THEN
1189 l_note_tbl(l_rec_num).K_HEADER_ID := p_to_hdr_id;
1190 END IF;
1191
1192 IF p_to_cle_id IS NOT NULL THEN
1193 l_note_tbl(l_rec_num).K_LINE_ID := p_to_cle_id;
1194 END IF;
1195
1196 IF p_to_del_id IS NOT NULL THEN
1197 l_note_tbl(l_rec_num).DELIVERABLE_ID := p_to_del_id;
1198 END IF;
1199
1200 END IF;
1201
1202 END LOOP;
1203 CLOSE note_cle_csr;
1204
1205
1206 OKE_NOTE_PVT.Insert_Row(
1207 p_api_version => p_api_version,
1208 p_init_msg_list => p_init_msg_list,
1209 x_return_status => x_return_status,
1210 x_msg_count => x_msg_count,
1211 x_msg_data => x_msg_data,
1212 p_note_tbl => l_note_tbl,
1213 x_note_tbl => x_note_tbl);
1214
1215 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1216 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1217 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1218 raise OKE_API.G_EXCEPTION_ERROR;
1219 End If;
1220
1221
1222 ELSIF(
1223 ---- (p_from_hdr_id = NULL)AND
1224 (p_from_cle_id IS NULL)AND
1225 (p_from_del_id IS NOT NULL) ) THEN
1226
1227
1228
1229 OPEN note_del_csr (p_from_del_id);
1230 LOOP
1231 FETCH note_del_csr INTO
1232 l_note_rec.STANDARD_NOTES_ID ,
1233 l_note_rec.CREATION_DATE ,
1234 l_note_rec.CREATED_BY ,
1235 l_note_rec.LAST_UPDATE_DATE ,
1236 l_note_rec.LAST_UPDATED_BY ,
1237 l_note_rec.LAST_UPDATE_LOGIN ,
1238 l_note_rec.K_HEADER_ID ,
1239 l_note_rec.K_LINE_ID ,
1240 l_note_rec.DELIVERABLE_ID ,
1241 l_note_rec.TYPE_CODE ,
1242 l_note_rec.ATTRIBUTE_CATEGORY ,
1243 l_note_rec.ATTRIBUTE1 ,
1244 l_note_rec.ATTRIBUTE2 ,
1245 l_note_rec.ATTRIBUTE3 ,
1246 l_note_rec.ATTRIBUTE4 ,
1247 l_note_rec.ATTRIBUTE5 ,
1248 l_note_rec.ATTRIBUTE6 ,
1249 l_note_rec.ATTRIBUTE7 ,
1250 l_note_rec.ATTRIBUTE8 ,
1251 l_note_rec.ATTRIBUTE9 ,
1252 l_note_rec.ATTRIBUTE10 ,
1253 l_note_rec.ATTRIBUTE11 ,
1254 l_note_rec.ATTRIBUTE12 ,
1255 l_note_rec.ATTRIBUTE13 ,
1256 l_note_rec.ATTRIBUTE14 ,
1257 l_note_rec.ATTRIBUTE15 ,
1258 l_note_rec.SFWT_FLAG ,
1259 l_note_rec.DESCRIPTION ,
1260 l_note_rec.NAME ,
1261 l_note_rec.TEXT ,
1262 l_note_rec.default_flag ;
1263 EXIT WHEN note_del_csr%NOTFOUND;
1264
1265
1266 IF (default_flag='N')OR((default_flag='Y')AND(l_note_rec.default_flag='Y')) THEN
1267
1268 l_rec_num := l_rec_num+1;
1269 l_note_tbl(l_rec_num) := l_note_rec;
1270
1271 -- add a bit of logic to figure destination (10/11/2000)
1272
1273 IF p_to_hdr_id IS NOT NULL THEN
1274 l_note_tbl(l_rec_num).K_HEADER_ID := p_to_hdr_id;
1275 END IF;
1276
1277 IF p_to_cle_id IS NOT NULL THEN
1278 l_note_tbl(l_rec_num).K_LINE_ID := p_to_cle_id;
1279 END IF;
1280
1281 IF p_to_del_id IS NOT NULL THEN
1282 l_note_tbl(l_rec_num).DELIVERABLE_ID := p_to_del_id;
1283 END IF;
1284
1285 END IF;
1286
1287 END LOOP;
1288 CLOSE note_del_csr;
1289
1290 OKE_NOTE_PVT.Insert_Row(
1291 p_api_version => p_api_version,
1292 p_init_msg_list => p_init_msg_list,
1293 x_return_status => x_return_status,
1294 x_msg_count => x_msg_count,
1295 x_msg_data => x_msg_data,
1296 p_note_tbl => l_note_tbl,
1297 x_note_tbl => x_note_tbl);
1298
1299
1300 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1301 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1302 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1303 raise OKE_API.G_EXCEPTION_ERROR;
1304 End If;
1305
1306 ELSE
1307 raise OKE_API.G_EXCEPTION_ERROR;
1308 END IF;
1309
1310 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1311 x_msg_data => x_msg_data);
1312 EXCEPTION
1313 when OKE_API.G_EXCEPTION_ERROR then
1314 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1315 p_api_name => l_api_name,
1316 p_pkg_name => g_pkg_name,
1317 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1318 x_msg_count => x_msg_count,
1319 x_msg_data => x_msg_data,
1320 p_api_type => g_api_type);
1321
1322 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1323 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1324 p_api_name => l_api_name,
1325 p_pkg_name => g_pkg_name,
1326 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1327 x_msg_count => x_msg_count,
1328 x_msg_data => x_msg_data,
1329 p_api_type => g_api_type);
1330
1331 when OTHERS then
1332 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1333 p_api_name => l_api_name,
1334 p_pkg_name => g_pkg_name,
1335 p_exc_name => 'OTHERS',
1336 x_msg_count => x_msg_count,
1337 x_msg_data => x_msg_data,
1338 p_api_type => g_api_type);
1339
1340 END copy_standard_note;
1341
1342
1343 PROCEDURE lock_standard_note(
1344 p_api_version IN NUMBER,
1345 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1346 x_return_status OUT NOCOPY VARCHAR2,
1347 x_msg_count OUT NOCOPY NUMBER,
1348 x_msg_data OUT NOCOPY VARCHAR2,
1349 p_note_rec IN OKE_NOTE_PVT.note_rec_type) IS
1350
1351
1352 l_del_rec oke_deliverable_pvt.del_rec_type;
1353 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_STANDARD_NOTE';
1354 l_api_version CONSTANT NUMBER := 1.0;
1355 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1356
1357 BEGIN
1358 l_return_status := OKE_API.START_ACTIVITY(
1359 p_api_name => l_api_name,
1360 p_pkg_name => g_pkg_name,
1361 p_init_msg_list => p_init_msg_list,
1362 l_api_version => l_api_version,
1363 p_api_version => p_api_version,
1364 p_api_type => g_api_type,
1365 x_return_status => x_return_status);
1366
1367 -- check if activity started successfully
1368 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1369 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1370 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1371 raise OKE_API.G_EXCEPTION_ERROR;
1372 End If;
1373
1374 OKE_NOTE_PVT.Lock_Row(
1375 p_api_version => p_api_version,
1376 p_init_msg_list => p_init_msg_list,
1377 x_return_status => x_return_status,
1378 x_msg_count => x_msg_count,
1379 x_msg_data => x_msg_data,
1380 p_note_rec => p_note_rec);
1381
1382 -- check return status
1383 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1384 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1385 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1386 raise OKE_API.G_EXCEPTION_ERROR;
1387 End If;
1388
1389 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1390 x_msg_data => x_msg_data);
1391 EXCEPTION
1392 when OKE_API.G_EXCEPTION_ERROR then
1393 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1394 p_api_name => l_api_name,
1395 p_pkg_name => g_pkg_name,
1396 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1397 x_msg_count => x_msg_count,
1398 x_msg_data => x_msg_data,
1399 p_api_type => g_api_type);
1400
1401 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1402 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1403 p_api_name => l_api_name,
1404 p_pkg_name => g_pkg_name,
1405 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1406 x_msg_count => x_msg_count,
1407 x_msg_data => x_msg_data,
1408 p_api_type => g_api_type);
1409
1410 when OTHERS then
1411 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1412 p_api_name => l_api_name,
1413 p_pkg_name => g_pkg_name,
1414 p_exc_name => 'OTHERS',
1415 x_msg_count => x_msg_count,
1416 x_msg_data => x_msg_data,
1417 p_api_type => g_api_type);
1418
1419 END lock_standard_note;
1420
1421 PROCEDURE lock_standard_note(
1422 p_api_version IN NUMBER,
1423 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1424 x_return_status OUT NOCOPY VARCHAR2,
1425 x_msg_count OUT NOCOPY NUMBER,
1426 x_msg_data OUT NOCOPY VARCHAR2,
1427 p_note_tbl IN oke_note_pvt.note_tbl_type) IS
1428
1429 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_STANDARD_NOTE';
1430 l_api_version CONSTANT NUMBER := 1.0;
1431 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1432 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1433 i NUMBER;
1434 BEGIN
1435 -- call START_ACTIVITY to create savepoint, check compatibility
1436 -- and initialize message list
1437 l_return_status := OKE_API.START_ACTIVITY(
1438 p_api_name => l_api_name,
1439 p_pkg_name => g_pkg_name,
1440 p_init_msg_list => p_init_msg_list,
1441 l_api_version => l_api_version,
1442 p_api_version => p_api_version,
1443 p_api_type => g_api_type,
1444 x_return_status => x_return_status);
1445
1446 -- check if activity started successfully
1447 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1448 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1449 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1450 raise OKE_API.G_EXCEPTION_ERROR;
1451 End If;
1452
1453 If (p_note_tbl.COUNT > 0) Then
1454 i := p_note_tbl.FIRST;
1455 LOOP
1456 -- call procedure in complex API
1457 OKE_NOTE_PVT.lock_row(
1458 p_api_version => p_api_version,
1459 p_init_msg_list => p_init_msg_list,
1460 x_return_status => x_return_status,
1461 x_msg_count => x_msg_count,
1462 x_msg_data => x_msg_data,
1463 p_note_rec => p_note_tbl(i));
1464
1465 -- store the highest degree of error
1466 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1467 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1468 l_overall_status := x_return_status;
1469 End If;
1470 End If;
1471 EXIT WHEN (i = p_note_tbl.LAST);
1472 i := p_note_tbl.NEXT(i);
1473 END LOOP;
1474 -- return overall status
1475 x_return_status := l_overall_status;
1476 End If;
1477
1478 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1479 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1480 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1481 raise OKE_API.G_EXCEPTION_ERROR;
1482 End If;
1483
1484 -- end activity
1485 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1486 x_msg_data => x_msg_data);
1487 EXCEPTION
1488 when OKE_API.G_EXCEPTION_ERROR then
1489 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1490 p_api_name => l_api_name,
1491 p_pkg_name => g_pkg_name,
1492 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1493 x_msg_count => x_msg_count,
1494 x_msg_data => x_msg_data,
1495 p_api_type => g_api_type);
1496
1497 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1498 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1499 p_api_name => l_api_name,
1500 p_pkg_name => g_pkg_name,
1501 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1502 x_msg_count => x_msg_count,
1503 x_msg_data => x_msg_data,
1504 p_api_type => g_api_type);
1505
1506 when OTHERS then
1507 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1508 p_api_name => l_api_name,
1509 p_pkg_name => g_pkg_name,
1510 p_exc_name => 'OTHERS',
1511 x_msg_count => x_msg_count,
1512 x_msg_data => x_msg_data,
1513 p_api_type => g_api_type);
1514
1515 END lock_standard_note;
1516
1517 END OKE_STANDARD_NOTES_PUB;
1518