[Home] [Help]
PACKAGE BODY: APPS.OKE_NOTE_PVT
Source
1 PACKAGE BODY OKE_NOTE_PVT AS
2 /* $Header: OKEVNOTB.pls 115.17 2002/11/20 20:42:23 who ship $ */
3
4 -- validate record
5
6 FUNCTION validate_record (
7 p_note_rec IN note_rec_type
8 ) RETURN VARCHAR2 IS
9 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
10 BEGIN
11
12 RETURN(l_return_status);
13
14 END validate_record;
15
16 -- validate individual attributes
17
18 FUNCTION validate_attributes(
19 p_note_rec IN note_rec_type
20 ) RETURN VARCHAR2 IS
21
22 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
23 x_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
24
25
26
27 PROCEDURE validate_k_header_id(x_return_status OUT NOCOPY VARCHAR2,
28 p_note_rec IN note_rec_type)IS
29
30 l_dummy_val VARCHAR2(1):='?';
31 CURSOR l_csr IS
32 SELECT 'x'
33 FROM OKE_K_HEADERS
34 WHERE K_HEADER_ID = p_note_rec.K_HEADER_ID;
35
36 BEGIN
37
38 x_return_status := OKE_API.G_RET_STS_SUCCESS;
39
40
41 -- check required value - not null
42
43 IF ( p_note_rec.k_header_id = OKE_API.G_MISS_NUM
44 OR p_note_rec.k_header_id IS NULL) THEN
45 OKE_API.SET_MESSAGE(
46 p_app_name =>g_app_name,
47 p_msg_name =>g_required_value,
48 p_token1 =>g_col_name_token,
49 p_token1_value =>'K_HEADER_ID');
50
51 x_return_status := OKE_API.G_RET_STS_ERROR;
52 raise G_EXCEPTION_HALT_VALIDATION;
53 END IF;
54
55
56 OPEN l_csr;
57 FETCH l_csr INTO l_dummy_val;
58 CLOSE l_csr;
59
60 IF (l_dummy_val = '?') THEN
61 OKE_API.SET_MESSAGE(
62 p_app_name =>g_app_name,
63 p_msg_name =>g_no_parent_record,
64 p_token1 =>g_col_name_token,
65 p_token1_value =>'K_HEADER_ID',
66 p_token2 =>g_child_table_token,
67 p_token2_value =>G_VIEW,
68 p_token3 =>g_parent_table_token,
69 p_token3_value =>'OKE_K_HEADERS');
70
71 x_return_status := OKE_API.G_RET_STS_ERROR;
72 END IF;
73
74
75
76 EXCEPTION
77 WHEN G_EXCEPTION_HALT_VALIDATION THEN
78 NULL;
79 WHEN OTHERS THEN
80 -- store SQL error message on message stack
81 OKE_API.SET_MESSAGE(
82 p_app_name =>g_app_name,
83 p_msg_name =>G_UNEXPECTED_ERROR,
84 p_token1 =>G_SQLCODE_TOKEN,
85 p_token1_value =>SQLCODE,
86 p_token2 =>G_SQLERRM_TOKEN,
87 p_token2_value =>SQLERRM);
88 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
89
90 IF l_csr%ISOPEN THEN
91 CLOSE l_csr;
92 END IF;
93
94 END validate_k_header_id;
95
96
97 PROCEDURE validate_k_line_id(x_return_status OUT NOCOPY VARCHAR2,
98 p_note_rec IN note_rec_type)IS
99
100 l_dummy_val VARCHAR2(1):='?';
101 CURSOR l_csr IS
102 SELECT 'x'
103 FROM OKE_K_LINES
104 WHERE K_LINE_ID = p_note_rec.K_LINE_ID;
105
106 BEGIN
107
108 x_return_status := OKE_API.G_RET_STS_SUCCESS;
109
110 IF ( p_note_rec.k_line_id <> OKE_API.G_MISS_NUM
111 AND p_note_rec.k_line_id IS NOT NULL) THEN
112
113 OPEN l_csr;
114 FETCH l_csr INTO l_dummy_val;
115 CLOSE l_csr;
116
117 IF (l_dummy_val = '?') THEN
118 OKE_API.SET_MESSAGE(
119 p_app_name =>g_app_name,
120 p_msg_name =>g_no_parent_record,
121 p_token1 =>g_col_name_token,
122 p_token1_value =>'K_LINE_ID',
123 p_token2 =>g_child_table_token,
124 p_token2_value =>G_VIEW,
125 p_token3 =>g_parent_table_token,
126 p_token3_value =>'OKE_K_LINES');
127
128 x_return_status := OKE_API.G_RET_STS_ERROR;
129 END IF;
130 END IF;
131
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 -- store SQL error message on message stack
136 OKE_API.SET_MESSAGE(
137 p_app_name =>g_app_name,
138 p_msg_name =>G_UNEXPECTED_ERROR,
139 p_token1 =>G_SQLCODE_TOKEN,
140 p_token1_value =>SQLCODE,
141 p_token2 =>G_SQLERRM_TOKEN,
142 p_token2_value =>SQLERRM);
143 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
144
145 IF l_csr%ISOPEN THEN
146 CLOSE l_csr;
147 END IF;
148
149 END validate_k_line_id;
150
151
152 PROCEDURE validate_deliverable_id(x_return_status OUT NOCOPY VARCHAR2,
153 p_note_rec IN note_rec_type)IS
154
155 l_dummy_val VARCHAR2(1):='?';
156 CURSOR l_csr IS
157 SELECT 'x'
158 FROM OKE_K_DELIVERABLES_B
159 WHERE DELIVERABLE_ID = p_note_rec.DELIVERABLE_ID;
160
161 BEGIN
162 x_return_status := OKE_API.G_RET_STS_SUCCESS;
163 IF ( p_note_rec.deliverable_id <> OKE_API.G_MISS_NUM
164 AND p_note_rec.deliverable_id IS NOT NULL) THEN
165
166 OPEN l_csr;
167 FETCH l_csr INTO l_dummy_val;
168 CLOSE l_csr;
169
170 IF (l_dummy_val = '?') THEN
171 OKE_API.SET_MESSAGE(
172 p_app_name =>g_app_name,
173 p_msg_name =>g_no_parent_record,
174 p_token1 =>g_col_name_token,
175 p_token1_value =>'DELIVERABLE_ID',
176 p_token2 =>g_child_table_token,
177 p_token2_value =>G_VIEW,
178 p_token3 =>g_parent_table_token,
179 p_token3_value =>'OKE_K_DELIVERABLES_B');
180
181 x_return_status := OKE_API.G_RET_STS_ERROR;
182 END IF;
183 END IF;
184
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 -- store SQL error message on message stack
189 OKE_API.SET_MESSAGE(
190 p_app_name =>g_app_name,
191 p_msg_name =>G_UNEXPECTED_ERROR,
192 p_token1 =>G_SQLCODE_TOKEN,
193 p_token1_value =>SQLCODE,
194 p_token2 =>G_SQLERRM_TOKEN,
195 p_token2_value =>SQLERRM);
196 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
197
198 IF l_csr%ISOPEN THEN
199 CLOSE l_csr;
200 END IF;
201 END validate_deliverable_id;
202
203
204 BEGIN
205
206 validate_k_header_id (x_return_status => l_return_status,
207 p_note_rec => p_note_rec);
208 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
209 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
210 x_return_status := l_return_status;
211 END IF;
212 END IF;
213
214
215 validate_k_line_id (x_return_status => l_return_status,
216 p_note_rec => p_note_rec);
217 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
218 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
219 x_return_status := l_return_status;
220 END IF;
221 END IF;
222
223
224 validate_deliverable_id (x_return_status => l_return_status,
225 p_note_rec => p_note_rec);
226 IF l_return_status <> OKE_API.G_RET_STS_SUCCESS THEN
227 IF x_return_status <> OKE_API.G_RET_STS_UNEXP_ERROR THEN
228 x_return_status := l_return_status;
229 END IF;
230 END IF;
231
232
233 /* call individual validation procedure */
234 -- return status to caller
235 RETURN(x_return_status);
236
237 END Validate_Attributes;
238
239 FUNCTION null_out_defaults(
240 p_note_rec IN note_rec_type ) RETURN note_rec_type IS
241
242 l_note_rec note_rec_type := p_note_rec;
243
244 BEGIN
245
246
247 IF l_note_rec.K_HEADER_ID = OKE_API.G_MISS_NUM THEN
248 l_note_rec.K_HEADER_ID := NULL;
249 END IF;
250
251 IF l_note_rec.K_LINE_ID = OKE_API.G_MISS_NUM THEN
252 l_note_rec.K_LINE_ID := NULL;
253 END IF;
254
255 IF l_note_rec.DELIVERABLE_ID = OKE_API.G_MISS_NUM THEN
256 l_note_rec.DELIVERABLE_ID := NULL;
257 END IF;
258
259 IF l_note_rec.TYPE_CODE = OKE_API.G_MISS_CHAR THEN
260 l_note_rec.TYPE_CODE := NULL;
261 END IF;
262
263 IF l_note_rec.default_flag = OKE_API.G_MISS_CHAR THEN
264 l_note_rec.default_flag := NULL;
265 END IF;
266
267
268 IF l_note_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
269 l_note_rec.ATTRIBUTE_CATEGORY := NULL;
270 END IF;
271
272 IF l_note_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
273 l_note_rec.ATTRIBUTE1 := NULL;
274 END IF;
275
276 IF l_note_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
277 l_note_rec.ATTRIBUTE2 := NULL;
278 END IF;
279
280 IF l_note_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
281 l_note_rec.ATTRIBUTE3 := NULL;
282 END IF;
283
284 IF l_note_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
285 l_note_rec.ATTRIBUTE4 := NULL;
286 END IF;
287
288 IF l_note_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
289 l_note_rec.ATTRIBUTE5 := NULL;
290 END IF;
291
292 IF l_note_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
293 l_note_rec.ATTRIBUTE6 := NULL;
294 END IF;
295
296 IF l_note_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
297 l_note_rec.ATTRIBUTE7 := NULL;
298 END IF;
299
300 IF l_note_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
301 l_note_rec.ATTRIBUTE8 := NULL;
302 END IF;
303
304 IF l_note_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
305 l_note_rec.ATTRIBUTE9 := NULL;
306 END IF;
307
308 IF l_note_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
309 l_note_rec.ATTRIBUTE10 := NULL;
310 END IF;
311
312 IF l_note_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
313 l_note_rec.ATTRIBUTE11 := NULL;
314 END IF;
315
316 IF l_note_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
317 l_note_rec.ATTRIBUTE12 := NULL;
318 END IF;
319
320 IF l_note_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
321 l_note_rec.ATTRIBUTE13 := NULL;
322 END IF;
323
324 IF l_note_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
325 l_note_rec.ATTRIBUTE14 := NULL;
326 END IF;
327
328 IF l_note_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
329 l_note_rec.ATTRIBUTE15 := NULL;
330 END IF;
331
332 IF l_note_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
333 l_note_rec.CREATED_BY := NULL;
334 END IF;
335
336 IF l_note_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
337 l_note_rec.CREATION_DATE := NULL;
338 END IF;
339
340 IF l_note_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
341 l_note_rec.LAST_UPDATED_BY := NULL;
342 END IF;
343
344 IF l_note_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
345 l_note_rec.LAST_UPDATE_LOGIN := NULL;
346 END IF;
347
348 IF l_note_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
349 l_note_rec.LAST_UPDATE_DATE := NULL;
350 END IF;
351
352 IF l_note_rec.SFWT_FLAG = OKE_API.G_MISS_CHAR THEN
353 l_note_rec.SFWT_FLAG := NULL;
354 END IF;
355
356 IF l_note_rec.DESCRIPTION = OKE_API.G_MISS_CHAR THEN
357 l_note_rec.DESCRIPTION := NULL;
358 END IF;
359
360 IF l_note_rec.NAME = OKE_API.G_MISS_CHAR THEN
361 l_note_rec.NAME := NULL;
362 END IF;
363
364 IF l_note_rec.TEXT = OKE_API.G_MISS_CHAR THEN
365 l_note_rec.TEXT := NULL;
366 END IF;
367
368
369 RETURN(l_note_rec);
370
371 END null_out_defaults;
372
373
374 FUNCTION get_rec (
375 p_note_rec IN note_rec_type,
376 x_no_data_found OUT NOCOPY BOOLEAN
377 ) RETURN note_rec_type IS
378
379 CURSOR note_pk_csr (p_id IN NUMBER) IS
380 SELECT
381 STANDARD_NOTES_ID ,
382 CREATION_DATE ,
383 CREATED_BY ,
384 LAST_UPDATE_DATE ,
385 LAST_UPDATED_BY ,
386 LAST_UPDATE_LOGIN ,
387 K_HEADER_ID ,
388 K_LINE_ID ,
389 DELIVERABLE_ID ,
390 TYPE_CODE ,
391 ATTRIBUTE_CATEGORY ,
392 ATTRIBUTE1 ,
393 ATTRIBUTE2 ,
394 ATTRIBUTE3 ,
395 ATTRIBUTE4 ,
396 ATTRIBUTE5 ,
397 ATTRIBUTE6 ,
398 ATTRIBUTE7 ,
399 ATTRIBUTE8 ,
400 ATTRIBUTE9 ,
401 ATTRIBUTE10 ,
402 ATTRIBUTE11 ,
403 ATTRIBUTE12 ,
404 ATTRIBUTE13 ,
405 ATTRIBUTE14 ,
406 ATTRIBUTE15 ,
407 default_flag
408 FROM OKE_K_STANDARD_NOTES_B
409 WHERE OKE_K_STANDARD_NOTES_B.STANDARD_NOTES_ID = p_id;
410
411 CURSOR note_pk_csr2 (p_id IN NUMBER) IS
412 SELECT
413 SFWT_FLAG ,
414 DESCRIPTION ,
415 NAME ,
416 TEXT
417 FROM OKE_K_STANDARD_NOTES_TL
418 WHERE OKE_K_STANDARD_NOTES_TL.STANDARD_NOTES_ID = p_id;
419
420
421 l_note_pk note_pk_csr%ROWTYPE;
422 l_note_rec note_rec_type;
423
424 BEGIN
425 x_no_data_found := TRUE;
426
427 -- get current database value
428
429
430 OPEN note_pk_csr(p_note_rec.STANDARD_NOTES_ID);
431 FETCH note_pk_csr INTO
432 l_note_rec.STANDARD_NOTES_ID ,
433 l_note_rec.CREATION_DATE ,
434 l_note_rec.CREATED_BY ,
435 l_note_rec.LAST_UPDATE_DATE ,
436 l_note_rec.LAST_UPDATED_BY ,
437 l_note_rec.LAST_UPDATE_LOGIN ,
438 l_note_rec.K_HEADER_ID ,
439 l_note_rec.K_LINE_ID ,
440 l_note_rec.DELIVERABLE_ID ,
441 l_note_rec.TYPE_CODE ,
442 l_note_rec.ATTRIBUTE_CATEGORY ,
443 l_note_rec.ATTRIBUTE1 ,
444 l_note_rec.ATTRIBUTE2 ,
445 l_note_rec.ATTRIBUTE3 ,
446 l_note_rec.ATTRIBUTE4 ,
447 l_note_rec.ATTRIBUTE5 ,
448 l_note_rec.ATTRIBUTE6 ,
449 l_note_rec.ATTRIBUTE7 ,
450 l_note_rec.ATTRIBUTE8 ,
451 l_note_rec.ATTRIBUTE9 ,
452 l_note_rec.ATTRIBUTE10 ,
453 l_note_rec.ATTRIBUTE11 ,
454 l_note_rec.ATTRIBUTE12 ,
455 l_note_rec.ATTRIBUTE13 ,
456 l_note_rec.ATTRIBUTE14 ,
457 l_note_rec.ATTRIBUTE15 ,
458 l_note_rec.default_flag ;
459
460 x_no_data_found := note_pk_csr%NOTFOUND;
461
462 CLOSE note_pk_csr;
463
464 IF(x_no_data_found) THEN
465 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
466 END IF;
467
468 OPEN note_pk_csr2(p_note_rec.STANDARD_NOTES_ID);
469 FETCH note_pk_csr2 INTO
470 l_note_rec.SFWT_FLAG ,
471 l_note_rec.DESCRIPTION ,
472 l_note_rec.NAME ,
473 l_note_rec.TEXT ;
474
475 x_no_data_found := note_pk_csr2%NOTFOUND;
476
477 CLOSE note_pk_csr2;
478
479 IF(x_no_data_found) THEN
480 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
481 END IF;
482
483 RETURN(l_note_rec);
484
485 END get_rec;
486
487
488
489 -- row level insert
490
491 PROCEDURE insert_row(
492 p_api_version IN NUMBER,
493 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2,
497 p_note_rec IN note_rec_type,
498 x_note_rec OUT NOCOPY note_rec_type) IS
499
500 l_api_version CONSTANT NUMBER := 1;
501 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
502 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
503 l_note_rec note_rec_type;
504 l_def_note_rec note_rec_type;
505 lx_note_rec note_rec_type;
506 l_seq NUMBER;
507
508 -- FUNCTION fill_who_columns --
509 -------------------------------
510 FUNCTION fill_who_columns (
511 p_note_rec IN note_rec_type
512 ) RETURN note_rec_type IS
513
514 l_note_rec note_rec_type := p_note_rec;
515
516 BEGIN
517
518 l_note_rec.CREATION_DATE := SYSDATE;
519 l_note_rec.CREATED_BY := FND_GLOBAL.USER_ID;
520 l_note_rec.LAST_UPDATE_DATE := SYSDATE;
521 l_note_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
522 l_note_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
523 RETURN(l_note_rec);
524
525 END fill_who_columns;
526
527
528
529 FUNCTION Set_Attributes (
530 p_note_rec IN note_rec_type,
531 x_note_rec OUT NOCOPY note_rec_type
532 ) RETURN VARCHAR2 IS
533 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
534 BEGIN
535 x_note_rec := p_note_rec;
536 x_note_rec.SFWT_FLAG := UPPER(x_note_rec.SFWT_FLAG);
537 RETURN(l_return_status);
538
539 END Set_Attributes;
540
541
542 BEGIN -- insert
543
544 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
545 G_PKG_NAME,
546 p_init_msg_list,
547 l_api_version,
548 p_api_version,
549 '_PVT',
550 x_return_status);
551
552
553 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
554 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
555 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
556 RAISE OKE_API.G_EXCEPTION_ERROR;
557 END IF;
558
559
560
561 l_note_rec := null_out_defaults(p_note_rec);
562
563
564
565 --- Setting item attributes
566 l_return_status := Set_Attributes(
567 l_note_rec, -- IN
568 l_def_note_rec); -- OUT
569
570 --- If any errors happen abort API
571 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
572 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
573 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
574 RAISE OKE_API.G_EXCEPTION_ERROR;
575 END IF;
576
577
578
579 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
580 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
581 END IF;
582
583 l_def_note_rec := fill_who_columns(l_def_note_rec);
584
585
586
587 --- Validate all non-missing attributes (Item Level Validation)
588 l_return_status := Validate_Attributes(l_def_note_rec);
589
590
591 --- If any errors happen abort API
592 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
593 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
594
595 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
596 RAISE OKE_API.G_EXCEPTION_ERROR;
597 END IF;
598
599
600
601 l_return_status := Validate_Record(l_def_note_rec);
602
603 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
604 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
605 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
606 RAISE OKE_API.G_EXCEPTION_ERROR;
607 END IF;
608
609 SELECT OKE_K_STANDARD_NOTES_S.nextval INTO l_seq FROM dual;
610
611 INSERT INTO OKE_K_STANDARD_NOTES_B(
612
613 STANDARD_NOTES_ID ,
614 CREATION_DATE ,
615 CREATED_BY ,
616 LAST_UPDATE_DATE ,
617 LAST_UPDATED_BY ,
618 LAST_UPDATE_LOGIN ,
619 K_HEADER_ID ,
620 K_LINE_ID ,
621 DELIVERABLE_ID ,
622 TYPE_CODE ,
623 ATTRIBUTE_CATEGORY ,
624 ATTRIBUTE1 ,
625 ATTRIBUTE2 ,
626 ATTRIBUTE3 ,
627 ATTRIBUTE4 ,
628 ATTRIBUTE5 ,
629 ATTRIBUTE6 ,
630 ATTRIBUTE7 ,
631 ATTRIBUTE8 ,
632 ATTRIBUTE9 ,
633 ATTRIBUTE10 ,
634 ATTRIBUTE11 ,
635 ATTRIBUTE12 ,
636 ATTRIBUTE13 ,
637 ATTRIBUTE14 ,
638 ATTRIBUTE15 ,
639 default_flag
640 )
641 VALUES(
642
643 l_seq,
644 l_def_note_rec.CREATION_DATE ,
645 l_def_note_rec.CREATED_BY ,
646 l_def_note_rec.LAST_UPDATE_DATE ,
647 l_def_note_rec.LAST_UPDATED_BY ,
648 l_def_note_rec.LAST_UPDATE_LOGIN ,
649 l_def_note_rec.K_HEADER_ID ,
650 l_def_note_rec.K_LINE_ID ,
651 l_def_note_rec.DELIVERABLE_ID ,
652 l_def_note_rec.TYPE_CODE ,
653 l_def_note_rec.ATTRIBUTE_CATEGORY ,
654 l_def_note_rec.ATTRIBUTE1 ,
655 l_def_note_rec.ATTRIBUTE2 ,
656 l_def_note_rec.ATTRIBUTE3 ,
657 l_def_note_rec.ATTRIBUTE4 ,
658 l_def_note_rec.ATTRIBUTE5 ,
659 l_def_note_rec.ATTRIBUTE6 ,
660 l_def_note_rec.ATTRIBUTE7 ,
661 l_def_note_rec.ATTRIBUTE8 ,
662 l_def_note_rec.ATTRIBUTE9 ,
663 l_def_note_rec.ATTRIBUTE10 ,
664 l_def_note_rec.ATTRIBUTE11 ,
665 l_def_note_rec.ATTRIBUTE12 ,
666 l_def_note_rec.ATTRIBUTE13 ,
667 l_def_note_rec.ATTRIBUTE14 ,
668 l_def_note_rec.ATTRIBUTE15 ,
669 l_def_note_rec.default_flag
670 );
671
672 INSERT INTO OKE_K_STANDARD_NOTES_TL(
673 STANDARD_NOTES_ID ,
674 LANGUAGE ,
675 CREATION_DATE ,
676 CREATED_BY ,
677 LAST_UPDATE_DATE ,
678 LAST_UPDATED_BY ,
679 LAST_UPDATE_LOGIN ,
680 SOURCE_LANG ,
681 SFWT_FLAG ,
682 DESCRIPTION ,
683 NAME ,
684 TEXT
685 )
686 SELECT
687 l_seq ,
688 L.language_code ,
689 l_def_note_rec.CREATION_DATE ,
690 l_def_note_rec.CREATED_BY ,
691 l_def_note_rec.LAST_UPDATE_DATE ,
692 l_def_note_rec.LAST_UPDATED_BY ,
693 l_def_note_rec.LAST_UPDATE_LOGIN ,
694 oke_utils.get_userenv_lang ,
695 l_def_note_rec.SFWT_FLAG ,
696 l_def_note_rec.DESCRIPTION ,
697 l_def_note_rec.NAME ,
698 l_def_note_rec.TEXT
699 FROM fnd_languages L
700 WHERE L.INSTALLED_FLAG in ('I', 'B')
701 AND NOT EXISTS
702 (select NULL
703 from OKE_K_STANDARD_NOTES_TL T
704 where T.STANDARD_NOTES_ID = l_seq
705 and T.LANGUAGE = L.LANGUAGE_CODE);
706
707
708 -- Set OUT values
709 x_note_rec := l_def_note_rec;
710 x_note_rec.STANDARD_NOTES_ID := l_seq;
711 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
712
713 EXCEPTION
714 WHEN OKE_API.G_EXCEPTION_ERROR THEN
715 x_return_status := OKE_API.HANDLE_EXCEPTIONS
716 (
717 l_api_name,
718 G_PKG_NAME,
719 'OKE_API.G_RET_STS_ERROR',
720 x_msg_count,
721 x_msg_data,
722 '_PVT'
723 );
724 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
725 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
726 (
727 l_api_name,
728 G_PKG_NAME,
729 'OKE_API.G_RET_STS_UNEXP_ERROR',
730 x_msg_count,
731 x_msg_data,
732 '_PVT'
733 );
734 WHEN OTHERS THEN
735 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
736 (
737 l_api_name,
738 G_PKG_NAME,
739 'OTHERS',
740 x_msg_count,
741 x_msg_data,
742 '_PVT'
743 );
744 END insert_row; -- row level
745
746
747
748
749 -- table level insert
750
751 PROCEDURE insert_row(
752 p_api_version IN NUMBER,
753 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
754 x_return_status OUT NOCOPY VARCHAR2,
755 x_msg_count OUT NOCOPY NUMBER,
756 x_msg_data OUT NOCOPY VARCHAR2,
757 p_note_tbl IN note_tbl_type,
758 x_note_tbl OUT NOCOPY note_tbl_type) IS
759
760 l_api_version CONSTANT NUMBER := 1;
761 l_api_name CONSTANT VARCHAR2(30) := 'TBL_insert_row';
762 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
763 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
764 i NUMBER := 0;
765 BEGIN
766
767 OKE_API.init_msg_list(p_init_msg_list);
768 -- Make sure PL/SQL table has records in it before passing
769 IF (p_note_tbl.COUNT > 0) THEN
770 i := p_note_tbl.FIRST;
771 LOOP
772 insert_row (
773 p_api_version => p_api_version,
774 p_init_msg_list => OKE_API.G_FALSE,
775 x_return_status => x_return_status,
776 x_msg_count => x_msg_count,
777 x_msg_data => x_msg_data,
778
779 p_note_rec => p_note_tbl(i),
780 x_note_rec => x_note_tbl(i));
781
782 -- store the highest degree of error
783 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
784 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
785 l_overall_status := x_return_status;
786 End If;
787 End If;
788
789 EXIT WHEN (i = p_note_tbl.LAST);
790
791 i := p_note_tbl.NEXT(i);
792 END LOOP;
793 -- return overall status
794 x_return_status := l_overall_status;
795 END IF;
796
797 EXCEPTION
798 WHEN OKE_API.G_EXCEPTION_ERROR THEN
799 x_return_status := OKE_API.HANDLE_EXCEPTIONS
800 (
801 l_api_name,
802 G_PKG_NAME,
803 'OKE_API.G_RET_STS_ERROR',
804 x_msg_count,
805 x_msg_data,
806 '_PVT'
807 );
808 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
809 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
810 (
811 l_api_name,
812 G_PKG_NAME,
813 'OKE_API.G_RET_STS_UNEXP_ERROR',
814 x_msg_count,
815 x_msg_data,
816 '_PVT'
817 );
818 WHEN OTHERS THEN
819 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
820 (
821 l_api_name,
822 G_PKG_NAME,
823 'OTHERS',
824 x_msg_count,
825 x_msg_data,
826 '_PVT'
827 );
828 END insert_row; -- table level
829
830
831
832
833
834
835
836
837 PROCEDURE update_row(
838 p_api_version IN NUMBER,
839 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
840 x_return_status OUT NOCOPY VARCHAR2,
841 x_msg_count OUT NOCOPY NUMBER,
842 x_msg_data OUT NOCOPY VARCHAR2,
843 p_note_rec IN note_rec_type,
844 x_note_rec OUT NOCOPY note_rec_type) IS
845
846 l_api_version CONSTANT NUMBER := 1.0;
847 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
848 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
849 l_note_rec note_rec_type := p_note_rec;
850 l_def_note_rec note_rec_type;
851 lx_note_rec note_rec_type;
852
853 -------------------------------
854 -- FUNCTION fill_who_columns --
855 -------------------------------
856 FUNCTION fill_who_columns (
857 p_note_rec IN note_rec_type
858 ) RETURN note_rec_type IS
859
860 l_note_rec note_rec_type := p_note_rec;
861
862 BEGIN
863 l_note_rec.LAST_UPDATE_DATE := SYSDATE;
864 l_note_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
865 l_note_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
866 RETURN(l_note_rec);
867 END fill_who_columns;
868
869 ----------------------------------
870 -- FUNCTION populate_new_record --
871 ----------------------------------
872 FUNCTION populate_new_record (
873 p_note_rec IN note_rec_type,
874 x_note_rec OUT NOCOPY note_rec_type
875 ) RETURN VARCHAR2 IS
876
877 l_note_rec note_rec_type;
878 l_row_notfound BOOLEAN := TRUE;
879 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
880
881 BEGIN
882
883 x_note_rec := p_note_rec;
884
885
886 -- Get current database values
887 l_note_rec := get_rec(p_note_rec, l_row_notfound);
888
889
890 IF (l_row_notfound) THEN
891 l_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
892 END IF;
893
894
895 -- this is key
896 --????????????????????????????????????????
897 -- IF x_note_rec.STANDARD_NOTES_ID = OKE_API.G_MISS_NUM THEN
898 -- x_note_rec.STANDARD_NOTES_ID := l_note_rec.STANDARD_NOTES_ID;
899 -- END IF;
900
901 IF x_note_rec.CREATION_DATE = OKE_API.G_MISS_DATE THEN
902 x_note_rec.CREATION_DATE := l_note_rec.CREATION_DATE;
903 END IF;
904
905 IF x_note_rec.CREATED_BY = OKE_API.G_MISS_NUM THEN
906 x_note_rec.CREATED_BY := l_note_rec.CREATED_BY;
907 END IF;
908
909 IF x_note_rec.LAST_UPDATE_DATE = OKE_API.G_MISS_DATE THEN
910 x_note_rec.LAST_UPDATE_DATE := l_note_rec.LAST_UPDATE_DATE;
911 END IF;
912
913 IF x_note_rec.LAST_UPDATED_BY = OKE_API.G_MISS_NUM THEN
914 x_note_rec.LAST_UPDATED_BY := l_note_rec.LAST_UPDATED_BY ;
915 END IF;
916
917 IF x_note_rec.LAST_UPDATE_LOGIN = OKE_API.G_MISS_NUM THEN
918 x_note_rec.LAST_UPDATE_LOGIN := l_note_rec.LAST_UPDATE_LOGIN;
919 END IF;
920
921 IF x_note_rec.K_HEADER_ID = OKE_API.G_MISS_NUM THEN
922 x_note_rec.K_HEADER_ID := l_note_rec.K_HEADER_ID;
923 END IF;
924
925 IF x_note_rec.K_LINE_ID = OKE_API.G_MISS_NUM THEN
926 x_note_rec.K_LINE_ID := l_note_rec.K_LINE_ID;
927 END IF;
928
929 IF x_note_rec.DELIVERABLE_ID = OKE_API.G_MISS_NUM THEN
930 x_note_rec.DELIVERABLE_ID := l_note_rec.DELIVERABLE_ID;
931 END IF;
932
933 IF x_note_rec.TYPE_CODE = OKE_API.G_MISS_CHAR THEN
934 x_note_rec.TYPE_CODE := l_note_rec.TYPE_CODE;
935 END IF;
936
937 IF x_note_rec.default_flag = OKE_API.G_MISS_CHAR THEN
938 x_note_rec.default_flag := l_note_rec.default_flag;
939 END IF;
940
941 IF x_note_rec.ATTRIBUTE_CATEGORY = OKE_API.G_MISS_CHAR THEN
942 x_note_rec.ATTRIBUTE_CATEGORY := l_note_rec.ATTRIBUTE_CATEGORY;
943 END IF;
944
945 IF x_note_rec.ATTRIBUTE1 = OKE_API.G_MISS_CHAR THEN
946 x_note_rec.ATTRIBUTE1 := l_note_rec.ATTRIBUTE1;
947 END IF;
948
949 IF x_note_rec.ATTRIBUTE2 = OKE_API.G_MISS_CHAR THEN
950 x_note_rec.ATTRIBUTE2 := l_note_rec.ATTRIBUTE2;
951 END IF;
952
953 IF x_note_rec.ATTRIBUTE3 = OKE_API.G_MISS_CHAR THEN
954 x_note_rec.ATTRIBUTE3 := l_note_rec.ATTRIBUTE3;
955 END IF;
956
957 IF x_note_rec.ATTRIBUTE4 = OKE_API.G_MISS_CHAR THEN
958 x_note_rec.ATTRIBUTE4 := l_note_rec.ATTRIBUTE4;
959 END IF;
960
961 IF x_note_rec.ATTRIBUTE5 = OKE_API.G_MISS_CHAR THEN
962 x_note_rec.ATTRIBUTE5 := l_note_rec.ATTRIBUTE5;
963 END IF;
964
965 IF x_note_rec.ATTRIBUTE6 = OKE_API.G_MISS_CHAR THEN
966 x_note_rec.ATTRIBUTE6 := l_note_rec.ATTRIBUTE6;
967 END IF;
968
969 IF x_note_rec.ATTRIBUTE7 = OKE_API.G_MISS_CHAR THEN
970 x_note_rec.ATTRIBUTE7 := l_note_rec.ATTRIBUTE7;
971 END IF;
972
973 IF x_note_rec.ATTRIBUTE8 = OKE_API.G_MISS_CHAR THEN
974 x_note_rec.ATTRIBUTE8 := l_note_rec.ATTRIBUTE8;
975 END IF;
976
977 IF x_note_rec.ATTRIBUTE9 = OKE_API.G_MISS_CHAR THEN
978 x_note_rec.ATTRIBUTE9 := l_note_rec.ATTRIBUTE9;
979 END IF;
980
981 IF x_note_rec.ATTRIBUTE10 = OKE_API.G_MISS_CHAR THEN
982 x_note_rec.ATTRIBUTE10 := l_note_rec.ATTRIBUTE10;
983 END IF;
984
985 IF x_note_rec.ATTRIBUTE11 = OKE_API.G_MISS_CHAR THEN
986 x_note_rec.ATTRIBUTE11 := l_note_rec.ATTRIBUTE11;
987 END IF;
988
989 IF x_note_rec.ATTRIBUTE12 = OKE_API.G_MISS_CHAR THEN
990 x_note_rec.ATTRIBUTE12 := l_note_rec.ATTRIBUTE12;
991 END IF;
992
993 IF x_note_rec.ATTRIBUTE13 = OKE_API.G_MISS_CHAR THEN
994 x_note_rec.ATTRIBUTE13 := l_note_rec.ATTRIBUTE13;
995 END IF;
996
997 IF x_note_rec.ATTRIBUTE14 = OKE_API.G_MISS_CHAR THEN
998 x_note_rec.ATTRIBUTE14 := l_note_rec.ATTRIBUTE14;
999 END IF;
1000
1001 IF x_note_rec.ATTRIBUTE15 = OKE_API.G_MISS_CHAR THEN
1002 x_note_rec.ATTRIBUTE15 := l_note_rec.ATTRIBUTE15;
1003 END IF;
1004
1005
1006 RETURN(l_return_status);
1007
1008
1009
1010 END populate_new_record;
1011
1012
1013
1014
1015 FUNCTION set_attributes(
1016 p_note_rec IN note_rec_type,
1017 x_note_rec OUT NOCOPY note_rec_type
1018 ) RETURN VARCHAR2 IS
1019 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1020 BEGIN
1021
1022 x_note_rec := p_note_rec;
1023 x_note_rec.SFWT_FLAG := UPPER(x_note_rec.SFWT_FLAG);
1024 RETURN(l_return_status);
1025
1026 END Set_Attributes;
1027
1028
1029 BEGIN -- update row
1030
1031
1032 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1033 G_PKG_NAME,
1034 p_init_msg_list,
1035 l_api_version,
1036 p_api_version,
1037 '_PVT',
1038 x_return_status);
1039
1040
1041 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1042 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1043 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1044 RAISE OKE_API.G_EXCEPTION_ERROR;
1045 END IF;
1046
1047
1048
1049 l_return_status := Set_Attributes(
1050 p_note_rec, -- IN
1051 l_note_rec); -- OUT
1052
1053
1054
1055 --- If any errors happen abort API
1056 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1057 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1058 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1059 RAISE OKE_API.G_EXCEPTION_ERROR;
1060 END IF;
1061
1062
1063
1064 l_return_status := populate_new_record(l_note_rec, l_def_note_rec);
1065
1066 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1067 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1068 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1069 RAISE OKE_API.G_EXCEPTION_ERROR;
1070 END IF;
1071
1072
1073 l_def_note_rec := fill_who_columns(l_def_note_rec);
1074
1075
1076 --- Validate all non-missing attributes (Item Level Validation)
1077 l_return_status := Validate_Attributes(l_def_note_rec);
1078
1079 --- If any errors happen abort API
1080 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1081 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1082 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1083 RAISE OKE_API.G_EXCEPTION_ERROR;
1084 END IF;
1085
1086 l_return_status := Validate_Record(l_def_note_rec);
1087 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1088 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1089 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1090 RAISE OKE_API.G_EXCEPTION_ERROR;
1091 END IF;
1092
1093
1094 UPDATE OKE_K_STANDARD_NOTES_B
1095 SET
1096 CREATION_DATE = l_def_note_rec.CREATION_DATE,
1097 CREATED_BY = l_def_note_rec.CREATED_BY,
1098 LAST_UPDATE_DATE = l_def_note_rec.LAST_UPDATE_DATE,
1099 LAST_UPDATED_BY = l_def_note_rec.LAST_UPDATED_BY,
1100 LAST_UPDATE_LOGIN = l_def_note_rec.LAST_UPDATE_LOGIN,
1101 K_HEADER_ID = l_def_note_rec.K_HEADER_ID,
1102 K_LINE_ID = l_def_note_rec.K_LINE_ID,
1103 DELIVERABLE_ID = l_def_note_rec.DELIVERABLE_ID,
1104 TYPE_CODE = l_def_note_rec.TYPE_CODE,
1105 ATTRIBUTE_CATEGORY = l_def_note_rec.ATTRIBUTE_CATEGORY,
1106 ATTRIBUTE1 = l_def_note_rec.ATTRIBUTE1,
1107 ATTRIBUTE2 = l_def_note_rec.ATTRIBUTE2,
1108 ATTRIBUTE3 = l_def_note_rec.ATTRIBUTE3,
1109 ATTRIBUTE4 = l_def_note_rec.ATTRIBUTE4,
1110 ATTRIBUTE5 = l_def_note_rec.ATTRIBUTE5,
1111 ATTRIBUTE6 = l_def_note_rec.ATTRIBUTE6,
1112 ATTRIBUTE7 = l_def_note_rec.ATTRIBUTE7,
1113 ATTRIBUTE8 = l_def_note_rec.ATTRIBUTE8,
1114 ATTRIBUTE9 = l_def_note_rec.ATTRIBUTE9,
1115 ATTRIBUTE10 = l_def_note_rec.ATTRIBUTE10,
1116 ATTRIBUTE11 = l_def_note_rec.ATTRIBUTE11,
1117 ATTRIBUTE12 = l_def_note_rec.ATTRIBUTE12,
1118 ATTRIBUTE13 = l_def_note_rec.ATTRIBUTE13,
1119 ATTRIBUTE14 = l_def_note_rec.ATTRIBUTE14,
1120 ATTRIBUTE15 = l_def_note_rec.ATTRIBUTE15,
1121 default_flag= l_def_note_rec.default_flag
1122 WHERE STANDARD_NOTES_ID = l_def_note_rec.STANDARD_NOTES_ID;
1123
1124
1125
1126
1127
1128 UPDATE OKE_K_STANDARD_NOTES_TL
1129 SET
1130 LAST_UPDATE_DATE = l_def_note_rec.LAST_UPDATE_DATE,
1131 LAST_UPDATED_BY = l_def_note_rec.LAST_UPDATED_BY,
1132 LAST_UPDATE_LOGIN = l_def_note_rec.LAST_UPDATE_LOGIN,
1133 SOURCE_LANG = oke_utils.get_userenv_lang,
1134 SFWT_FLAG = l_def_note_rec.SFWT_FLAG,
1135 DESCRIPTION = l_def_note_rec.DESCRIPTION,
1136 NAME = l_def_note_rec.NAME,
1137 TEXT = l_def_note_rec.TEXT
1138 WHERE STANDARD_NOTES_ID = l_def_note_rec.STANDARD_NOTES_ID
1139 AND userenv('LANG') in (language , source_lang);
1140
1141 x_note_rec := l_def_note_rec;
1142
1143 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1144
1145 EXCEPTION
1146 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1147 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1148 (
1149 l_api_name,
1150 G_PKG_NAME,
1151 'OKE_API.G_RET_STS_ERROR',
1152 x_msg_count,
1153 x_msg_data,
1154 '_PVT'
1155 );
1156 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1157 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1158 (
1159 l_api_name,
1160 G_PKG_NAME,
1161 'OKE_API.G_RET_STS_UNEXP_ERROR',
1162 x_msg_count,
1163 x_msg_data,
1164 '_PVT'
1165 );
1166 WHEN OTHERS THEN
1167 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1168 (
1169 l_api_name,
1170 G_PKG_NAME,
1171 'OTHERS',
1172 x_msg_count,
1173 x_msg_data,
1174 '_PVT'
1175 );
1176 END update_row; -- row level update
1177
1178
1179
1180 PROCEDURE update_row(
1181 p_api_version IN NUMBER,
1182 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1183 x_return_status OUT NOCOPY VARCHAR2,
1184 x_msg_count OUT NOCOPY NUMBER,
1185 x_msg_data OUT NOCOPY VARCHAR2,
1186 p_note_tbl IN note_tbl_type,
1187 x_note_tbl OUT NOCOPY note_tbl_type) IS
1188
1189 l_api_version CONSTANT NUMBER := 1.0;
1190 l_api_name CONSTANT VARCHAR2(30) := 'TBL_update_row';
1191
1192
1193 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1194 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1195 i NUMBER := 0;
1196 BEGIN
1197
1198 OKE_API.init_msg_list(p_init_msg_list);
1199 -- Make sure PL/SQL table has records in it before passing
1200 IF (p_note_tbl.COUNT > 0) THEN
1201 i := p_note_tbl.FIRST;
1202 LOOP
1203
1204 update_row (
1205 p_api_version => p_api_version,
1206 p_init_msg_list => OKE_API.G_FALSE,
1207 x_return_status => x_return_status,
1208 x_msg_count => x_msg_count,
1209 x_msg_data => x_msg_data,
1210 p_note_rec => p_note_tbl(i),
1211 x_note_rec => x_note_tbl(i));
1212
1213 -- store the highest degree of error
1214 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1215 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1216 l_overall_status := x_return_status;
1217 End If;
1218 End If;
1219
1220 EXIT WHEN (i = p_note_tbl.LAST);
1221 i := p_note_tbl.NEXT(i);
1222 END LOOP;
1223 -- return overall status
1224 x_return_status := l_overall_status;
1225 END IF;
1226
1227 EXCEPTION
1228 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1229 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1230 (
1231 l_api_name,
1232 G_PKG_NAME,
1233 'OKE_API.G_RET_STS_ERROR',
1234 x_msg_count,
1235 x_msg_data,
1236 '_PVT'
1237 );
1238
1239 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1240 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1241 (
1242 l_api_name,
1243 G_PKG_NAME,
1244 'OKE_API.G_RET_STS_UNEXP_ERROR',
1245 x_msg_count,
1246 x_msg_data,
1247 '_PVT'
1248 );
1249 WHEN OTHERS THEN
1250 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1251 (
1252 l_api_name,
1253 G_PKG_NAME,
1254 'OTHERS',
1255 x_msg_count,
1256 x_msg_data,
1257 '_PVT'
1258 );
1259 END update_row; -- table level update
1260
1261
1262 PROCEDURE delete_row(
1263 p_api_version IN NUMBER,
1264 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1265 x_return_status OUT NOCOPY VARCHAR2,
1266 x_msg_count OUT NOCOPY NUMBER,
1267 x_msg_data OUT NOCOPY VARCHAR2,
1268 p_del_id IN NUMBER) IS
1269
1270 l_api_version CONSTANT NUMBER := 1;
1271 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1272 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1273
1274
1275 BEGIN
1276
1277 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1278 p_init_msg_list,
1279 '_PVT',
1280 x_return_status);
1281
1282
1283 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1284 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1285 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1286 RAISE OKE_API.G_EXCEPTION_ERROR;
1287 END IF;
1288
1289
1290 DELETE FROM OKE_K_STANDARD_NOTES_TL
1291 WHERE STANDARD_NOTES_ID IN (
1292 SELECT STANDARD_NOTES_ID FROM OKE_K_STANDARD_NOTES_B
1293 WHERE DELIVERABLE_ID = p_del_id);
1294
1295 DELETE FROM OKE_K_STANDARD_NOTES_B
1296 WHERE DELIVERABLE_ID = p_del_id;
1297
1298 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1299
1300 EXCEPTION
1301 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1302 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1303 (
1304 l_api_name,
1305 G_PKG_NAME,
1306 'OKE_API.G_RET_STS_ERROR',
1307 x_msg_count,
1308 x_msg_data,
1309 '_PVT'
1310 );
1311 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1312 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1313 (
1314 l_api_name,
1315 G_PKG_NAME,
1316 'OKE_API.G_RET_STS_UNEXP_ERROR',
1317 x_msg_count,
1318 x_msg_data,
1319 '_PVT'
1320 );
1321 WHEN OTHERS THEN
1322 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1323 (
1324 l_api_name,
1325 G_PKG_NAME,
1326 'OTHERS',
1327 x_msg_count,
1328 x_msg_data,
1329 '_PVT'
1330 );
1331 END delete_row;
1332
1333
1334 PROCEDURE delete_row(
1335 p_api_version IN NUMBER,
1336 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1337 x_return_status OUT NOCOPY VARCHAR2,
1338 x_msg_count OUT NOCOPY NUMBER,
1339 x_msg_data OUT NOCOPY VARCHAR2,
1340 p_cle_id IN NUMBER) IS
1341
1342 l_api_version CONSTANT NUMBER := 1;
1343 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1344 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1345
1346
1347 BEGIN
1348
1349 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1350 p_init_msg_list,
1351 '_PVT',
1352 x_return_status);
1353
1354
1355 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1356 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1357 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1358 RAISE OKE_API.G_EXCEPTION_ERROR;
1359 END IF;
1360
1361
1362 DELETE FROM OKE_K_STANDARD_NOTES_TL
1363 WHERE STANDARD_NOTES_ID IN (
1364 SELECT STANDARD_NOTES_ID FROM OKE_K_STANDARD_NOTES_B
1365 WHERE K_LINE_ID = p_cle_id);
1366
1367 DELETE FROM OKE_K_STANDARD_NOTES_B
1368 WHERE K_LINE_ID = p_cle_id;
1369
1370 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1371
1372 EXCEPTION
1373 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1374 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1375 (
1376 l_api_name,
1377 G_PKG_NAME,
1378 'OKE_API.G_RET_STS_ERROR',
1379 x_msg_count,
1380 x_msg_data,
1381 '_PVT'
1382 );
1383 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1384 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1385 (
1386 l_api_name,
1387 G_PKG_NAME,
1388 'OKE_API.G_RET_STS_UNEXP_ERROR',
1389 x_msg_count,
1390 x_msg_data,
1391 '_PVT'
1392 );
1393 WHEN OTHERS THEN
1394 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1395 (
1396 l_api_name,
1397 G_PKG_NAME,
1398 'OTHERS',
1399 x_msg_count,
1400 x_msg_data,
1401 '_PVT'
1402 );
1403 END delete_row;
1404
1405 PROCEDURE delete_row(
1406 p_api_version IN NUMBER,
1407 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1408 x_return_status OUT NOCOPY VARCHAR2,
1409 x_msg_count OUT NOCOPY NUMBER,
1410 x_msg_data OUT NOCOPY VARCHAR2,
1411 p_hdr_id IN NUMBER) IS
1412
1413 l_api_version CONSTANT NUMBER := 1;
1414 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1415 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1416
1417
1418 BEGIN
1419
1420 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1421 p_init_msg_list,
1422 '_PVT',
1423 x_return_status);
1424
1425
1426 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1427 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1428 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1429 RAISE OKE_API.G_EXCEPTION_ERROR;
1430 END IF;
1431
1432
1433 DELETE FROM OKE_K_STANDARD_NOTES_TL
1434 WHERE STANDARD_NOTES_ID IN (
1435 SELECT STANDARD_NOTES_ID FROM OKE_K_STANDARD_NOTES_B
1436 WHERE
1437 (K_HEADER_ID = p_hdr_id) AND
1438 (K_LINE_ID IS NULL) AND
1439 (DELIVERABLE_ID IS NULL));
1440
1441 DELETE FROM OKE_K_STANDARD_NOTES_B
1442 WHERE (K_HEADER_ID = p_hdr_id) AND
1443 (K_LINE_ID IS NULL) AND
1444 (DELIVERABLE_ID IS NULL);
1445
1446 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1447
1448 EXCEPTION
1449 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1450 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1451 (
1452 l_api_name,
1453 G_PKG_NAME,
1454 'OKE_API.G_RET_STS_ERROR',
1455 x_msg_count,
1456 x_msg_data,
1457 '_PVT'
1458 );
1459 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1460 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1461 (
1462 l_api_name,
1463 G_PKG_NAME,
1464 'OKE_API.G_RET_STS_UNEXP_ERROR',
1465 x_msg_count,
1466 x_msg_data,
1467 '_PVT'
1468 );
1469 WHEN OTHERS THEN
1470 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1471 (
1472 l_api_name,
1473 G_PKG_NAME,
1474 'OTHERS',
1475 x_msg_count,
1476 x_msg_data,
1477 '_PVT'
1478 );
1479 END delete_row;
1480
1481 -- row level delete
1482 PROCEDURE delete_row(
1483 p_api_version IN NUMBER,
1484 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1485 x_return_status OUT NOCOPY VARCHAR2,
1486 x_msg_count OUT NOCOPY NUMBER,
1487 x_msg_data OUT NOCOPY VARCHAR2,
1488 p_note_rec IN note_rec_type) IS
1489
1490 l_api_version CONSTANT NUMBER := 1;
1491 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
1492 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1493 l_note_rec note_rec_type := p_note_rec;
1494
1495 BEGIN
1496
1497 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1498 p_init_msg_list,
1499 '_PVT',
1500 x_return_status);
1501
1502
1503 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1504 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1505 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1506 RAISE OKE_API.G_EXCEPTION_ERROR;
1507 END IF;
1508
1509 DELETE FROM OKE_K_STANDARD_NOTES_B
1510 WHERE STANDARD_NOTES_ID = l_note_rec.STANDARD_NOTES_ID;
1511
1512 DELETE FROM OKE_K_STANDARD_NOTES_TL
1513 WHERE STANDARD_NOTES_ID = l_note_rec.STANDARD_NOTES_ID;
1514
1515 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1516
1517 EXCEPTION
1518 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1519 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1520 (
1521 l_api_name,
1522 G_PKG_NAME,
1523 'OKE_API.G_RET_STS_ERROR',
1524 x_msg_count,
1525 x_msg_data,
1526 '_PVT'
1527 );
1528 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1529 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1530 (
1531 l_api_name,
1532 G_PKG_NAME,
1533 'OKE_API.G_RET_STS_UNEXP_ERROR',
1534 x_msg_count,
1535 x_msg_data,
1536 '_PVT'
1537 );
1538 WHEN OTHERS THEN
1539 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1540 (
1541 l_api_name,
1542 G_PKG_NAME,
1543 'OTHERS',
1544 x_msg_count,
1545 x_msg_data,
1546 '_PVT'
1547 );
1548 END delete_row;
1549
1550
1551 -- table level delete
1552
1553 PROCEDURE delete_row(
1554 p_api_version IN NUMBER,
1555 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1556 x_return_status OUT NOCOPY VARCHAR2,
1557 x_msg_count OUT NOCOPY NUMBER,
1558 x_msg_data OUT NOCOPY VARCHAR2,
1559 p_note_tbl IN note_tbl_type) IS
1560
1561 l_api_version CONSTANT NUMBER := 1;
1562 l_api_name CONSTANT VARCHAR2(30) := 'TBL_delete_row';
1563 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1564 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1565 i NUMBER := 0;
1566 BEGIN
1567 OKE_API.init_msg_list(p_init_msg_list);
1568
1569 -- Make sure PL/SQL table has records in it before passing
1570 IF (p_note_tbl.COUNT > 0) THEN
1571 i := p_note_tbl.FIRST;
1572 LOOP
1573 delete_row (
1574 p_api_version => p_api_version,
1575 p_init_msg_list => OKE_API.G_FALSE,
1576 x_return_status => x_return_status,
1577 x_msg_count => x_msg_count,
1578 x_msg_data => x_msg_data,
1579 p_note_rec => p_note_tbl(i));
1580
1581
1582
1583 -- store the highest degree of error
1584 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1585 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1586 l_overall_status := x_return_status;
1587 End If;
1588 End If;
1589
1590 EXIT WHEN (i = p_note_tbl.LAST);
1591 i := p_note_tbl.NEXT(i);
1592 END LOOP;
1593
1594 -- return overall status
1595 x_return_status := l_overall_status;
1596 END IF;
1597
1598 EXCEPTION
1599 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1600 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1601 (
1602 l_api_name,
1603 G_PKG_NAME,
1604 'OKE_API.G_RET_STS_ERROR',
1605 x_msg_count,
1606 x_msg_data,
1607 '_PVT'
1608 );
1609 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1610 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1611 (
1612 l_api_name,
1613 G_PKG_NAME,
1614 'OKE_API.G_RET_STS_UNEXP_ERROR',
1615 x_msg_count,
1616 x_msg_data,
1617 '_PVT'
1618 );
1619 WHEN OTHERS THEN
1620 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1621 (
1622 l_api_name,
1623 G_PKG_NAME,
1624 'OTHERS',
1625 x_msg_count,
1626 x_msg_data,
1627 '_PVT'
1628 );
1629 END delete_row; -- table level delete
1630
1631
1632 -- validate row
1633
1634 PROCEDURE validate_row(
1635 p_api_version IN NUMBER,
1636 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1637 x_return_status OUT NOCOPY VARCHAR2,
1638 x_msg_count OUT NOCOPY NUMBER,
1639 x_msg_data OUT NOCOPY VARCHAR2,
1640 p_note_rec IN note_rec_type
1641 ) IS
1642
1643 l_api_version CONSTANT NUMBER := 1;
1644 l_api_name CONSTANT VARCHAR2(30) := 'B_validate_row';
1645 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1646 l_note_rec note_rec_type := p_note_rec;
1647
1648 BEGIN
1649 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1650 G_PKG_NAME,
1651 p_init_msg_list,
1652 l_api_version,
1653 p_api_version,
1654 '_PVT',
1655 x_return_status);
1656 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1657 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1658 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1659
1660 RAISE OKE_API.G_EXCEPTION_ERROR;
1661 END IF;
1662 --- Validate all non-missing attributes (Item Level Validation)
1663 l_return_status := Validate_Attributes(l_note_rec);
1664 --- If any errors happen abort API
1665 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1666 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1667 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1668 RAISE OKE_API.G_EXCEPTION_ERROR;
1669 END IF;
1670 l_return_status := Validate_Record(l_note_rec);
1671
1672 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1673 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1674 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1675 RAISE OKE_API.G_EXCEPTION_ERROR;
1676 END IF;
1677 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1678 EXCEPTION
1679 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1680 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1681 (
1682 l_api_name,
1683 G_PKG_NAME,
1684 'OKE_API.G_RET_STS_ERROR',
1685 x_msg_count,
1686 x_msg_data,
1687 '_PVT'
1688 );
1689 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1690 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1691 (
1692 l_api_name,
1693 G_PKG_NAME,
1694 'OKE_API.G_RET_STS_UNEXP_ERROR',
1695 x_msg_count,
1696 x_msg_data,
1697 '_PVT'
1698 );
1699 WHEN OTHERS THEN
1700 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1701 (
1702 l_api_name,
1703 G_PKG_NAME,
1704 'OTHERS',
1705 x_msg_count,
1706 x_msg_data,
1707 '_PVT'
1708 );
1709 END validate_row;
1710
1711 PROCEDURE validate_row(
1712 p_api_version IN NUMBER,
1713 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1714 x_return_status OUT NOCOPY VARCHAR2,
1715 x_msg_count OUT NOCOPY NUMBER,
1716 x_msg_data OUT NOCOPY VARCHAR2,
1717 p_note_tbl IN note_tbl_type
1718 ) IS
1719
1720 l_api_version CONSTANT NUMBER := 1;
1721 l_api_name CONSTANT VARCHAR2(30) := 'TBL_validate_row';
1722 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1723 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1724 i NUMBER := 0;
1725 BEGIN
1726 OKE_API.init_msg_list(p_init_msg_list);
1727 -- Make sure PL/SQL table has records in it before passing
1728 IF (p_note_tbl.COUNT > 0) THEN
1729 i := p_note_tbl.FIRST;
1730 LOOP
1731 validate_row (
1732 p_api_version => p_api_version,
1733 p_init_msg_list => OKE_API.G_FALSE,
1734 x_return_status => x_return_status,
1735 x_msg_count => x_msg_count,
1736 x_msg_data => x_msg_data,
1737 p_note_rec => p_note_tbl(i));
1738
1739 -- store the highest degree of error
1740 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1741 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1742 l_overall_status := x_return_status;
1743 End If;
1744 End If;
1745
1746 EXIT WHEN (i = p_note_tbl.LAST);
1747 i := p_note_tbl.NEXT(i);
1748 END LOOP;
1749 -- return overall status
1750 x_return_status := l_overall_status;
1751 END IF;
1752
1753 EXCEPTION
1754 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1755 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1756 (
1757 l_api_name,
1758 G_PKG_NAME,
1759 'OKE_API.G_RET_STS_ERROR',
1760 x_msg_count,
1761 x_msg_data,
1762 '_PVT'
1763 );
1764 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1765 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1766 (
1767 l_api_name,
1768 G_PKG_NAME,
1769 'OKE_API.G_RET_STS_UNEXP_ERROR',
1770 x_msg_count,
1771 x_msg_data,
1772 '_PVT'
1773 );
1774 WHEN OTHERS THEN
1775 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1776 (
1777 l_api_name,
1778 G_PKG_NAME,
1779 'OTHERS',
1780 x_msg_count,
1781 x_msg_data,
1782 '_PVT'
1783 );
1784 END validate_row;
1785
1786
1787
1788
1789 PROCEDURE lock_row(
1790 p_api_version IN NUMBER,
1791 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1792 x_return_status OUT NOCOPY VARCHAR2,
1793 x_msg_count OUT NOCOPY NUMBER,
1794 x_msg_data OUT NOCOPY VARCHAR2,
1795 p_note_rec IN note_rec_type) IS
1796
1797
1798 l_api_version CONSTANT NUMBER := 1;
1799 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
1800 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1801 l_row_notfound1 BOOLEAN := FALSE;
1802 l_row_notfound2 BOOLEAN := FALSE;
1803
1804 l_row_id NUMBER;
1805
1806 E_Resource_Busy EXCEPTION;
1807 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1808
1809
1810 CURSOR lock_csr1 (p_note_rec IN note_rec_type) IS
1811 SELECT standard_notes_id FROM oke_k_standard_notes_b
1812 WHERE standard_notes_id = p_note_rec.standard_notes_id
1813 FOR UPDATE NOWAIT;
1814
1815 CURSOR lock_csr2 (p_note_rec IN note_rec_type) IS
1816 SELECT standard_notes_id FROM oke_k_standard_notes_tl
1817 WHERE standard_notes_id = p_note_rec.standard_notes_id
1818 FOR UPDATE NOWAIT;
1819
1820
1821 BEGIN
1822 l_return_status := OKE_API.START_ACTIVITY(l_api_name,
1823 p_init_msg_list,
1824 '_PVT',
1825 x_return_status);
1826 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
1827 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1828 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
1829 RAISE OKE_API.G_EXCEPTION_ERROR;
1830 END IF;
1831
1832
1833 BEGIN
1834 OPEN lock_csr1(p_note_rec);
1835 FETCH lock_csr1 INTO l_row_id;
1836 l_row_notfound1 := lock_csr1%NOTFOUND;
1837 CLOSE lock_csr1;
1838
1839 OPEN lock_csr2(p_note_rec);
1840 FETCH lock_csr2 INTO l_row_id;
1841 l_row_notfound2 := lock_csr2%NOTFOUND;
1842 CLOSE lock_csr2;
1843
1844 EXCEPTION
1845 WHEN E_Resource_Busy THEN
1846 IF (lock_csr1%ISOPEN) THEN
1847 CLOSE lock_csr1;
1848 END IF;
1849 IF (lock_csr2%ISOPEN) THEN
1850 CLOSE lock_csr2;
1851 END IF;
1852 OKE_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1853 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1854 END;
1855
1856
1857 IF (l_row_notfound1)OR(l_row_notfound2) THEN
1858 OKE_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1859 RAISE OKE_API.G_EXCEPTION_ERROR;
1860 END IF;
1861
1862 OKE_API.END_ACTIVITY(x_msg_count, x_msg_data);
1863
1864
1865 EXCEPTION
1866 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1867 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1868 (
1869 l_api_name,
1870 G_PKG_NAME,
1871 'OKE_API.G_RET_STS_ERROR',
1872 x_msg_count,
1873 x_msg_data,
1874 '_PVT'
1875 );
1876 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1877 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1878 (
1879 l_api_name,
1880 G_PKG_NAME,
1881 'OKE_API.G_RET_STS_UNEXP_ERROR',
1882 x_msg_count,
1883 x_msg_data,
1884 '_PVT'
1885 );
1886 WHEN OTHERS THEN
1887 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1888 (
1889 l_api_name,
1890 G_PKG_NAME,
1891 'OTHERS',
1892 x_msg_count,
1893 x_msg_data,
1894 '_PVT'
1895 );
1896 END lock_row;
1897
1898 procedure ADD_LANGUAGE
1899 is
1900 begin
1901 --
1902 -- Regular table
1903 --
1904 delete from OKE_K_STANDARD_NOTES_TL T
1905 where not exists
1906 (select NULL
1907 from OKE_K_STANDARD_NOTES_B B
1908 where B.STANDARD_NOTES_ID = T.STANDARD_NOTES_ID
1909 );
1910
1911 update OKE_K_STANDARD_NOTES_TL T set (
1912 NAME,
1913 DESCRIPTION,
1914 TEXT
1915 ) = (select
1916 B.NAME,
1917 B.DESCRIPTION,
1918 B.TEXT
1919 from OKE_K_STANDARD_NOTES_TL B
1920 where B.STANDARD_NOTES_ID = T.STANDARD_NOTES_ID
1921 and B.LANGUAGE = T.SOURCE_LANG)
1922 where (
1923 T.STANDARD_NOTES_ID,
1924 T.LANGUAGE
1925 ) in (select
1926 SUBT.STANDARD_NOTES_ID,
1927 SUBT.LANGUAGE
1928 from OKE_K_STANDARD_NOTES_TL SUBB, OKE_K_STANDARD_NOTES_TL SUBT
1929 where SUBB.STANDARD_NOTES_ID = SUBT.STANDARD_NOTES_ID
1930 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1931 and (SUBB.NAME <> SUBT.NAME
1932 or (SUBB.NAME is null and SUBT.NAME is not null)
1933 or (SUBB.NAME is not null and SUBT.NAME is null)
1934 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
1935 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
1936 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
1937 or SUBB.TEXT <> SUBT.TEXT
1938 or (SUBB.TEXT is null and SUBT.TEXT is not null)
1939 or (SUBB.TEXT is not null and SUBT.TEXT is null)
1940 ));
1941
1942 insert into OKE_K_STANDARD_NOTES_TL (
1943 STANDARD_NOTES_ID,
1944 CREATION_DATE,
1945 CREATED_BY,
1946 LAST_UPDATE_DATE,
1947 LAST_UPDATED_BY,
1948 LAST_UPDATE_LOGIN,
1949 SFWT_FLAG,
1950 DESCRIPTION,
1951 NAME,
1952 TEXT,
1953 LANGUAGE,
1954 SOURCE_LANG
1955 ) select
1956 B.STANDARD_NOTES_ID,
1957 B.CREATION_DATE,
1958 B.CREATED_BY,
1959 B.LAST_UPDATE_DATE,
1960 B.LAST_UPDATED_BY,
1961 B.LAST_UPDATE_LOGIN,
1962 B.SFWT_FLAG,
1963 B.DESCRIPTION,
1964 B.NAME,
1965 B.TEXT,
1966 L.LANGUAGE_CODE,
1967 B.SOURCE_LANG
1968 from OKE_K_STANDARD_NOTES_TL B, FND_LANGUAGES L
1969 where L.INSTALLED_FLAG in ('I', 'B')
1970 and B.LANGUAGE = userenv('LANG')
1971 and not exists
1972 (select NULL
1973 from OKE_K_STANDARD_NOTES_TL T
1974 where T.STANDARD_NOTES_ID = B.STANDARD_NOTES_ID
1975 and T.LANGUAGE = L.LANGUAGE_CODE);
1976
1977 --
1978 -- History table
1979 --
1980 delete from OKE_K_STANDARD_NOTES_TLH T
1981 where not exists
1982 (select NULL
1983 from OKE_K_STANDARD_NOTES_BH B
1984 where B.STANDARD_NOTES_ID = T.STANDARD_NOTES_ID
1985 AND B.MAJOR_VERSION = T.MAJOR_VERSION
1986 );
1987
1988 update OKE_K_STANDARD_NOTES_TLH T set (
1989 NAME,
1990 DESCRIPTION,
1991 TEXT
1992 ) = (select
1993 B.NAME,
1994 B.DESCRIPTION,
1995 B.TEXT
1996 from OKE_K_STANDARD_NOTES_TLH B
1997 where B.STANDARD_NOTES_ID = T.STANDARD_NOTES_ID
1998 and B.MAJOR_VERSION = T.MAJOR_VERSION
1999 and B.LANGUAGE = T.SOURCE_LANG)
2000 where (
2001 T.STANDARD_NOTES_ID,
2002 T.MAJOR_VERSION,
2003 T.LANGUAGE
2004 ) in (select
2005 SUBT.STANDARD_NOTES_ID,
2006 SUBT.MAJOR_VERSION,
2007 SUBT.LANGUAGE
2008 from OKE_K_STANDARD_NOTES_TLH SUBB, OKE_K_STANDARD_NOTES_TLH SUBT
2009 where SUBB.STANDARD_NOTES_ID = SUBT.STANDARD_NOTES_ID
2010 and SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
2011 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
2012 and (SUBB.NAME <> SUBT.NAME
2013 or (SUBB.NAME is null and SUBT.NAME is not null)
2014 or (SUBB.NAME is not null and SUBT.NAME is null)
2015 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
2016 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
2017 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
2018 or SUBB.TEXT <> SUBT.TEXT
2019 or (SUBB.TEXT is null and SUBT.TEXT is not null)
2020 or (SUBB.TEXT is not null and SUBT.TEXT is null)
2021 ));
2022
2023 insert into OKE_K_STANDARD_NOTES_TLH (
2024 STANDARD_NOTES_ID,
2025 MAJOR_VERSION,
2026 CREATION_DATE,
2027 CREATED_BY,
2028 LAST_UPDATE_DATE,
2029 LAST_UPDATED_BY,
2030 LAST_UPDATE_LOGIN,
2031 SFWT_FLAG,
2032 DESCRIPTION,
2033 NAME,
2034 TEXT,
2035 LANGUAGE,
2036 SOURCE_LANG
2037 ) select
2038 B.STANDARD_NOTES_ID,
2039 B.MAJOR_VERSION,
2040 B.CREATION_DATE,
2041 B.CREATED_BY,
2042 B.LAST_UPDATE_DATE,
2043 B.LAST_UPDATED_BY,
2044 B.LAST_UPDATE_LOGIN,
2045 B.SFWT_FLAG,
2046 B.DESCRIPTION,
2047 B.NAME,
2048 B.TEXT,
2049 L.LANGUAGE_CODE,
2050 B.SOURCE_LANG
2051 from OKE_K_STANDARD_NOTES_TLH B, FND_LANGUAGES L
2052 where L.INSTALLED_FLAG in ('I', 'B')
2053 and B.LANGUAGE = userenv('LANG')
2054 and not exists
2055 (select NULL
2056 from OKE_K_STANDARD_NOTES_TLH T
2057 where T.STANDARD_NOTES_ID = B.STANDARD_NOTES_ID
2058 and T.MAJOR_VERSION = B.MAJOR_VERSION
2059 and T.LANGUAGE = L.LANGUAGE_CODE);
2060 end ADD_LANGUAGE;
2061
2062 END OKE_NOTE_PVT;