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