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