[Home] [Help]
PACKAGE BODY: APPS.OKL_AVL_PVT
Source
1 PACKAGE BODY OKL_AVL_PVT AS
2 /* $Header: OKLSAVLB.pls 120.4 2007/01/29 10:50:38 ssdeshpa noship $ */
3
4 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
5 G_ITEM_NOT_FOUND_ERROR EXCEPTION;
6
7 ---------------------------------------------------------------------------
8 -- PROCEDURE validate_name
9 -- 04/27/2001 Inserted by Robin Edwin for not null validation
10 ---------------------------------------------------------------------------
11 PROCEDURE validate_name(
12 x_return_status OUT NOCOPY VARCHAR2,
13 p_avlv_rec IN avlv_rec_type
14 ) IS
15
16 BEGIN
17
18 x_return_status := OKC_API.G_RET_STS_SUCCESS;
19
20 IF (p_avlv_rec.name IS NULL) OR (p_avlv_rec.name = OKC_API.G_MISS_CHAR) THEN
21 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
22 p_msg_name => g_required_value,
23 p_token1 => g_col_name_token,
24 p_token1_value => 'NAME');
25
26 x_return_status := OKC_API.G_RET_STS_ERROR;
27 RAISE G_EXCEPTION_HALT_VALIDATION;
28 ELSE
29 x_return_status := OKC_API.G_RET_STS_SUCCESS;
30 END IF;
31 EXCEPTION
32 WHEN G_EXCEPTION_HALT_VALIDATION THEN
33 NULL;
34 WHEN OTHERS THEN
35 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
36 p_msg_name => g_required_value,
37 p_token1 => g_sqlcode_token,
38 p_token1_value => SQLCODE,
39 p_token2 => g_sqlerrm_token,
40 p_token2_value => SQLERRM);
41 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
42 END validate_name;
43
44 ---------------------------------------------------------------------------
45 -- PROCEDURE validate_set_of_books_id
46 -- 04/27/2001 Inserted by Robin Edwin for not null validation
47 -- 01/17/2007 Fixed Bug # 5707866 ssdeshpa start
48 -- Removed Validation Logic for set_of_books_id column
49 ---------------------------------------------------------------------------
50 /* PROCEDURE validate_set_of_books_id(
51 x_return_status OUT NOCOPY VARCHAR2,
52 p_avlv_rec IN avlv_rec_type
53 ) IS
54 BEGIN
55 x_return_status := OKC_API.G_RET_STS_SUCCESS;
56 IF (p_avlv_rec.set_of_books_id IS NULL) OR (p_avlv_rec.set_of_books_id = OKC_API.G_MISS_NUM) THEN
57 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
58 p_msg_name => g_required_value,
59 p_token1 => g_col_name_token,
60 p_token1_value => 'SET_OF_BOOKS_ID');
61 x_return_status := OKC_API.G_RET_STS_ERROR;
62 RAISE G_EXCEPTION_HALT_VALIDATION;
63 ELSE
64 x_return_status := OKC_API.G_RET_STS_SUCCESS;
65 END IF;
66 EXCEPTION
67 WHEN G_EXCEPTION_HALT_VALIDATION THEN
68 NULL;
69 WHEN OTHERS THEN
70 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
71 p_msg_name => g_required_value,
72 p_token1 => g_sqlcode_token,
73 p_token1_value => SQLCODE,
74 p_token2 => g_sqlerrm_token,
75 p_token2_value => SQLERRM);
76 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
77 END validate_set_of_books_id; */
78 --Fixed Bug # 5707866 ssdeshpa end
79 ---------------------------------------------------------------------------
80 -- PROCEDURE validate_sty_id
81 -- 04/27/2001 Inserted by Robin Edwin for not null validation
82 ---------------------------------------------------------------------------
83 PROCEDURE validate_sty_id(
84 x_return_status OUT NOCOPY VARCHAR2,
85 p_avlv_rec IN avlv_rec_type
86 ) IS
87
88 CURSOR sty_csr(v_sty_id NUMBER) IS
89 SELECT '1'
90 FROM OKL_STRM_TYPE_V
91 WHERE ID = v_sty_id;
92
93 l_dummy VARCHAR2(1);
94
95
96 BEGIN
97
98 x_return_status := OKC_API.G_RET_STS_SUCCESS;
99
100 IF (p_avlv_rec.sty_id IS NULL) OR (p_avlv_rec.sty_id = OKC_API.G_MISS_NUM) THEN
101 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
102 p_msg_name => g_required_value,
103 p_token1 => g_col_name_token,
104 p_token1_value => 'STY_ID');
105
106 x_return_status := OKC_API.G_RET_STS_ERROR;
107 RAISE G_EXCEPTION_HALT_VALIDATION;
108 ELSE
109
110 OPEN sty_csr(p_avlv_rec.sty_id);
111 FETCH sty_csr INTO l_dummy;
112 IF (sty_csr%NOTFOUND) THEN
113 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
114 p_msg_name => g_invalid_value,
115 p_token1 => g_col_name_token,
116 p_token1_value => 'STY_ID');
117
118 x_return_status := OKC_API.G_RET_STS_ERROR;
119 CLOSE sty_csr;
120 RAISE G_EXCEPTION_HALT_VALIDATION;
121 END IF;
122 CLOSE sty_csr;
123
124 END IF;
125
126
127 EXCEPTION
128 WHEN G_EXCEPTION_HALT_VALIDATION THEN
129 NULL;
130 WHEN OTHERS THEN
131 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
132 p_msg_name => g_required_value,
133 p_token1 => g_sqlcode_token,
134 p_token1_value => SQLCODE,
135 p_token2 => g_sqlerrm_token,
136 p_token2_value => SQLERRM);
137 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
138 END validate_sty_id;
139
140 ---------------------------------------------------------------------------
141 -- PROCEDURE validate_try_id
142 -- 04/27/2001 Inserted by Robin Edwin for not null validation
143 ---------------------------------------------------------------------------
144 PROCEDURE validate_try_id(
145 x_return_status OUT NOCOPY VARCHAR2,
146 p_avlv_rec IN avlv_rec_type
147 ) IS
148
149 CURSOR try_csr(v_try_id NUMBER) IS
150 SELECT '1'
151 FROM OKL_TRX_TYPES_V
152 WHERE ID = v_try_id;
153
154 l_dummy VARCHAR2(1);
155
156
157 BEGIN
158 x_return_status := OKC_API.G_RET_STS_SUCCESS;
159
160 IF (p_avlv_rec.try_id IS NULL) OR (p_avlv_rec.try_id = OKC_API.G_MISS_NUM) THEN
161
162 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
163 p_msg_name => g_required_value,
164 p_token1 => g_col_name_token,
165 p_token1_value => 'TRY_ID');
166
167 x_return_status := OKC_API.G_RET_STS_ERROR;
168 RAISE G_EXCEPTION_HALT_VALIDATION;
169
170 ELSE
171
172 OPEN try_csr(p_avlv_rec.try_id);
173 FETCH try_csr INTO l_dummy;
174
175 IF (try_csr%NOTFOUND) THEN
176 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
177 p_msg_name => g_invalid_value,
178 p_token1 => g_col_name_token,
179 p_token1_value => 'TRY_ID');
180 x_return_status := OKC_API.G_RET_STS_ERROR;
181 CLOSE try_csr;
182 RAISE G_EXCEPTION_HALT_VALIDATION;
183 END IF;
184
185 CLOSE try_csr;
186
187 END IF;
188
189 EXCEPTION
190 WHEN G_EXCEPTION_HALT_VALIDATION THEN
191 NULL;
192 WHEN OTHERS THEN
193 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
194 p_msg_name => g_required_value,
195 p_token1 => g_sqlcode_token,
196 p_token1_value => SQLCODE,
197 p_token2 => g_sqlerrm_token,
198 p_token2_value => SQLERRM);
199 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
200 END validate_try_id;
201
202 ---------------------------------------------------------------------------
203 -- PROCEDURE validate_aes_id
204 -- 04/27/2001 Inserted by Robin Edwin for not null validation
205 ---------------------------------------------------------------------------
206 PROCEDURE validate_aes_id(
207 x_return_status OUT NOCOPY VARCHAR2,
208 p_avlv_rec IN avlv_rec_type
209 ) IS
210
211 CURSOR aes_csr(v_aes_id NUMBER) IS
212 SELECT '1'
213 FROM OKL_AE_TMPT_SETS_V
214 WHERE ID = v_aes_id;
215
216 l_dummy VARCHAR2(1);
217
218 BEGIN
219
220 x_return_status := OKC_API.G_RET_STS_SUCCESS;
221
222 IF (p_avlv_rec.aes_id IS NULL) OR (p_avlv_rec.aes_id = OKC_API.G_MISS_NUM) THEN
223 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
224 p_msg_name => g_required_value,
225 p_token1 => g_col_name_token,
226 p_token1_value => 'AES_ID');
227
228 x_return_status := OKC_API.G_RET_STS_ERROR;
229 RAISE G_EXCEPTION_HALT_VALIDATION;
230 ELSE
231
232 OPEN aes_csr(p_avlv_rec.aes_id);
233 FETCH aes_csr INTO l_dummy;
234 IF (aes_csr%NOTFOUND) THEN
235
236 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
237 p_msg_name => g_invalid_value,
238 p_token1 => g_col_name_token,
239 p_token1_value => 'AES_ID');
240
241 x_return_status := OKC_API.G_RET_STS_ERROR;
242 CLOSE aes_csr;
243 RAISE G_EXCEPTION_HALT_VALIDATION;
244
245 END IF;
246
247 CLOSE aes_csr;
248
249 END IF;
250
251 EXCEPTION
252 WHEN G_EXCEPTION_HALT_VALIDATION THEN
253 NULL;
254 WHEN OTHERS THEN
255 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
256 p_msg_name => g_required_value,
257 p_token1 => g_sqlcode_token,
258 p_token1_value => SQLCODE,
259 p_token2 => g_sqlerrm_token,
260 p_token2_value => SQLERRM);
261 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
262 END validate_aes_id;
263
264
265 ---------------------------------------------------------------------------
266 -- PROCEDURE validate_syt_code
267 ---------------------------------------------------------------------------
268
269 PROCEDURE validate_syt_code(
270 x_return_status OUT NOCOPY VARCHAR2,
271 p_avlv_rec IN avlv_rec_type
272 ) IS
273 l_dummy VARCHAR2(1);
274
275 BEGIN
276
277 x_return_status := OKC_API.G_RET_STS_SUCCESS;
278
279 IF (p_avlv_rec.syt_code IS NOT NULL) AND (p_avlv_rec.syt_code <> OKC_API.G_MISS_CHAR) THEN
280
281 l_dummy :=
282 OKL_ACCOUNTING_UTIL.validate_lookup_code (p_lookup_type => 'OKL_SYNDICATION_CODE',
283 p_lookup_code => p_avlv_rec.syt_code);
284
285 IF (l_dummy = OKC_API.G_FALSE) THEN
286
287 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
288 p_msg_name => g_invalid_value,
289 p_token1 => g_col_name_token,
290 p_token1_value => 'SYT_CODE');
291
292 x_return_status := OKC_API.G_RET_STS_ERROR;
293 RAISE G_EXCEPTION_HALT_VALIDATION;
294
295 END IF;
296
297 END IF;
298
299 EXCEPTION
300 WHEN G_EXCEPTION_HALT_VALIDATION THEN
301 NULL;
302 WHEN OTHERS THEN
303 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
304 p_msg_name => g_required_value,
305 p_token1 => g_sqlcode_token,
306 p_token1_value => SQLCODE,
307 p_token2 => g_sqlerrm_token,
308 p_token2_value => SQLERRM);
309 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
310 END validate_syt_code;
311
312
313
314 ---------------------------------------------------------------------------
315 -- PROCEDURE validate_fac_code
316 ---------------------------------------------------------------------------
317
318 PROCEDURE validate_fac_code(
319 x_return_status OUT NOCOPY VARCHAR2,
320 p_avlv_rec IN avlv_rec_type
321 ) IS
322 l_dummy VARCHAR2(1);
323
324 BEGIN
325
326 x_return_status := OKC_API.G_RET_STS_SUCCESS;
327
328 IF (p_avlv_rec.fac_code IS NOT NULL) AND (p_avlv_rec.fac_code <> OKC_API.G_MISS_CHAR) THEN
329
330 l_dummy :=
331 OKL_ACCOUNTING_UTIL.validate_lookup_code (p_lookup_type => 'OKL_FACTORING_CODE',
332 p_lookup_code => p_avlv_rec.fac_code);
333
334 IF (l_dummy = OKC_API.G_FALSE) THEN
335
336 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
337 p_msg_name => g_invalid_value,
338 p_token1 => g_col_name_token,
339 p_token1_value => 'FAC_CODE');
340
341 x_return_status := OKC_API.G_RET_STS_ERROR;
342 RAISE G_EXCEPTION_HALT_VALIDATION;
343
344 END IF;
345
346 END IF;
347
348 EXCEPTION
349 WHEN G_EXCEPTION_HALT_VALIDATION THEN
350 NULL;
351 WHEN OTHERS THEN
352 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
353 p_msg_name => g_required_value,
354 p_token1 => g_sqlcode_token,
355 p_token1_value => SQLCODE,
356 p_token2 => g_sqlerrm_token,
357 p_token2_value => SQLERRM);
358 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
359 END validate_fac_code;
360
361
362 ---------------------------------------------------------------------------
363 -- PROCEDURE validate_factoring_synd_flag
364 ---------------------------------------------------------------------------
365
366 PROCEDURE validate_factoring_synd_flag(
367 x_return_status OUT NOCOPY VARCHAR2,
368 p_avlv_rec IN avlv_rec_type
369 ) IS
370 l_dummy VARCHAR2(1);
371
372 BEGIN
373
374 x_return_status := OKC_API.G_RET_STS_SUCCESS;
375
376 IF (p_avlv_rec.factoring_synd_flag IS NOT NULL) AND
377 (p_avlv_rec.factoring_synd_flag <> OKC_API.G_MISS_CHAR) THEN
378
379 l_dummy :=
380 OKL_ACCOUNTING_UTIL.validate_lookup_code (p_lookup_type => 'OKL_FACTORING_SYNDICATION',
381 p_lookup_code => p_avlv_rec.factoring_synd_flag);
382
383 IF (l_dummy = OKC_API.G_FALSE) THEN
384
385 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
386 p_msg_name => g_invalid_value,
387 p_token1 => g_col_name_token,
388 p_token1_value => 'FACTORING_SYND_FLAG');
389
390 x_return_status := OKC_API.G_RET_STS_ERROR;
391 RAISE G_EXCEPTION_HALT_VALIDATION;
392
393 END IF;
394
395 /* IF (p_avlv_rec.factoring_synd_flag = 'FACTORING') THEN
396
397 IF (p_avlv_rec.FAC_CODE IS NULL) OR (p_avlv_rec.FAC_CODE = OKL_API.G_MISS_CHAR) THEN
398
399 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
400 p_msg_name => g_required_value,
401 p_token1 => g_col_name_token,
402 p_token1_value => 'FAC_CODE');
403 x_return_status := OKC_API.G_RET_STS_ERROR;
404 RAISE G_EXCEPTION_HALT_VALIDATION;
405
406 END IF;
407
408 ELSIF (p_avlv_rec.factoring_synd_flag = 'SYNDICATION') THEN
409
410 IF (p_avlv_rec.SYT_CODE IS NULL) OR (p_avlv_rec.SYT_CODE = OKL_API.G_MISS_CHAR) THEN
411
412 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
413 p_msg_name => g_required_value,
414 p_token1 => g_col_name_token,
415 p_token1_value => 'SYT_CODE');
416 x_return_status := OKC_API.G_RET_STS_ERROR;
417 RAISE G_EXCEPTION_HALT_VALIDATION;
418
419 END IF;
420
421 ELSIF (p_avlv_rec.factoring_synd_flag = 'INVESTOR') THEN
422
423 IF (p_avlv_rec.INV_CODE IS NULL) OR (p_avlv_rec.INV_CODE = OKL_API.G_MISS_CHAR) THEN
424
425 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
426 p_msg_name => g_required_value,
427 p_token1 => g_col_name_token,
428 p_token1_value => 'INV_CODE');
429 x_return_status := OKC_API.G_RET_STS_ERROR;
430 RAISE G_EXCEPTION_HALT_VALIDATION;
431 END IF;
432
433 END IF;
434 */
435
436 END IF;
437
438 EXCEPTION
439 WHEN G_EXCEPTION_HALT_VALIDATION THEN
440 NULL;
441 WHEN OTHERS THEN
442 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
443 p_msg_name => g_required_value,
444 p_token1 => g_sqlcode_token,
445 p_token1_value => SQLCODE,
446 p_token2 => g_sqlerrm_token,
447 p_token2_value => SQLERRM);
448 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
449 END validate_factoring_synd_flag;
450
451
452 -- Santonyr 18th Jul, 2002.
453 -- Removed the validation as the field is removed from the screen
454
455 /*
456 ---------------------------------------------------------------------------
457 -- PROCEDURE validate_advance_arrears
458 ---------------------------------------------------------------------------
459 -- PROCEDURE validate_advance_arrears
460 -- 04/27/2001 Inserted by Robin Edwin for not null validation
461 ---------------------------------------------------------------------------
462 PROCEDURE validate_advance_arrears(
463 x_return_status OUT NOCOPY VARCHAR2,
464 p_avlv_rec IN avlv_rec_type
465 ) IS
466
467 l_dummy varchar2(1);
468
469 BEGIN
470 x_return_status := OKC_API.G_RET_STS_SUCCESS;
471
472 IF (p_avlv_rec.advance_arrears IS NOT NULL) AND
473 (p_avlv_rec.advance_arrears <> OKC_API.G_MISS_CHAR) THEN
474
475 l_dummy :=
476 OKL_ACCOUNTING_UTIL.validate_lookup_code (p_lookup_type => 'OKL_ADVANCE_ARREARS',
477 p_lookup_code => p_avlv_rec.advance_arrears);
478
479 IF (l_dummy = OKC_API.G_FALSE) THEN
480
481 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
482 p_msg_name => g_invalid_value,
483 p_token1 => g_col_name_token,
484 p_token1_value => 'ADVANCE_ARREARS');
485
486 x_return_status := OKC_API.G_RET_STS_ERROR;
487 RAISE G_EXCEPTION_HALT_VALIDATION;
488
489 END IF;
490
491
492 END IF;
493
494 EXCEPTION
495 WHEN G_EXCEPTION_HALT_VALIDATION THEN
496 NULL;
497 WHEN OTHERS THEN
498 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
499 p_msg_name => g_required_value,
500 p_token1 => g_sqlcode_token,
501 p_token1_value => SQLCODE,
502 p_token2 => g_sqlerrm_token,
503 p_token2_value => SQLERRM);
504 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
505 END validate_advance_arrears;
506 */
507
508 -- Santonyr 18th Jul, 2002.
509 -- Removed the validation as the field is removed from the screen
510
511 /*
512 ---------------------------------------------------------------------------
513 -- PROCEDURE validate_Accrual_yn
514 -- Validates the field ACCRUAL_YN to be Not NULL and to be Y or N
515 ---------------------------------------------------------------------------
516 PROCEDURE validate_accrual_yn(
517 x_return_status OUT NOCOPY VARCHAR2,
518 p_avlv_rec IN avlv_rec_type
519 ) IS
520
521 l_dummy VARCHAR2(1);
522 l_app_id NUMBER := 0;
523 l_view_app_id NUMBER := 0;
524
525 BEGIN
526
527 x_return_status := OKC_API.G_RET_STS_SUCCESS;
528
529 IF (p_avlv_rec.accrual_yn IS NULL) OR (p_avlv_rec.accrual_yn = OKC_API.G_MISS_CHAR) THEN
530 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
531 p_msg_name => g_required_value,
532 p_token1 => g_col_name_token,
533 p_token1_value => 'ACCRUAL_YN');
534
535 x_return_status := OKC_API.G_RET_STS_ERROR;
536 RAISE G_EXCEPTION_HALT_VALIDATION;
537 ELSE
538 l_dummy :=
539 OKL_ACCOUNTING_UTIL.validate_lookup_code (p_lookup_type => 'YES_NO',
540 p_lookup_code => p_avlv_rec.accrual_yn,
541 p_app_id => l_app_id,
542 p_view_app_id => l_view_app_id);
543 IF (l_dummy = OKC_API.G_FALSE) THEN
544
545 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
546 p_msg_name => g_invalid_value,
547 p_token1 => g_col_name_token,
548 p_token1_value => 'ACCRUAL_YN');
549
550 x_return_status := OKC_API.G_RET_STS_ERROR;
551 RAISE G_EXCEPTION_HALT_VALIDATION;
552
553 END IF;
554
555 END IF;
556 EXCEPTION
557 WHEN G_EXCEPTION_HALT_VALIDATION THEN
558 NULL;
559 WHEN OTHERS THEN
560 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
561 p_msg_name => g_required_value,
562 p_token1 => g_sqlcode_token,
563 p_token1_value => SQLCODE,
564 p_token2 => g_sqlerrm_token,
565 p_token2_value => SQLERRM);
566 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
567
568 END validate_accrual_yn;
569 */
570
571
572 ---------------------------------------------------------------------------
573 -- PROCEDURE validate_post_to_gl
574 -- 01/17/2007 Fixed Bug # 5707866 ssdeshpa
575 -- Removed Validation Logic for post_to_gl column
576 ---------------------------------------------------------------------------
577 /* PROCEDURE validate_post_to_gl(
578 x_return_status OUT NOCOPY VARCHAR2,
579 p_avlv_rec IN avlv_rec_type
580 ) IS
581
582 l_dummy VARCHAR2(1);
583 l_app_id NUMBER := 0;
584 l_view_app_id NUMBER := 0;
585
586 BEGIN
587
588 x_return_status := OKC_API.G_RET_STS_SUCCESS;
589
590 IF (p_avlv_rec.post_to_gl IS NULL) OR (p_avlv_rec.post_to_gl = OKC_API.G_MISS_CHAR) THEN
591 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
592 p_msg_name => g_required_value,
593 p_token1 => g_col_name_token,
594 p_token1_value => 'POST_TO_GL');
595
596 x_return_status := OKC_API.G_RET_STS_ERROR;
597 RAISE G_EXCEPTION_HALT_VALIDATION;
598 ELSE
599 l_dummy :=
600 OKL_ACCOUNTING_UTIL.validate_lookup_code (p_lookup_type => 'YES_NO',
601 p_lookup_code => p_avlv_rec.post_to_gl,
602 p_app_id => l_app_id,
603 p_view_app_id => l_view_app_id);
604 IF (l_dummy = OKC_API.G_FALSE) THEN
605
606 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
607 p_msg_name => g_invalid_value,
608 p_token1 => g_col_name_token,
609 p_token1_value => 'POST_TO_GL');
610
611 x_return_status := OKC_API.G_RET_STS_ERROR;
612 RAISE G_EXCEPTION_HALT_VALIDATION;
613
614 END IF;
615
616 END IF;
617 EXCEPTION
618 WHEN G_EXCEPTION_HALT_VALIDATION THEN
619 NULL;
620 WHEN OTHERS THEN
621 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
622 p_msg_name => g_required_value,
623 p_token1 => g_sqlcode_token,
624 p_token1_value => SQLCODE,
625 p_token2 => g_sqlerrm_token,
626 p_token2_value => SQLERRM);
627 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
628 END validate_post_to_gl; */
629 --Fixed Bug # 5707866 ssdeshpa end.
630 ---------------------------------------------------------------------------
631 -- PROCEDURE validate_version
632 -- 04/27/2001 Inserted by Robin Edwin for not null validation
633 -- 01/17/2007 Fixed Bug # 5707866 ssdeshpa start
634 -- Removed Validation Logic for version column
635 ---------------------------------------------------------------------------
636 /* PROCEDURE validate_version(
637 x_return_status OUT NOCOPY VARCHAR2,
638 p_avlv_rec IN avlv_rec_type
639 ) IS
640 BEGIN
641 x_return_status := OKC_API.G_RET_STS_SUCCESS;
642
643 IF (p_avlv_rec.version IS NULL) OR (p_avlv_rec.version = OKC_API.G_MISS_CHAR) THEN
644 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
645 p_msg_name => g_required_value,
646 p_token1 => g_col_name_token,
647 p_token1_value => 'VERSION');
648
649 x_return_status := OKC_API.G_RET_STS_ERROR;
650 RAISE G_EXCEPTION_HALT_VALIDATION;
651 ELSE
652 x_return_status := OKC_API.G_RET_STS_SUCCESS;
653 END IF;
654 EXCEPTION
655 WHEN G_EXCEPTION_HALT_VALIDATION THEN
656 NULL;
657 WHEN OTHERS THEN
658 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
659 p_msg_name => g_required_value,
660 p_token1 => g_sqlcode_token,
661 p_token1_value => SQLCODE,
662 p_token2 => g_sqlerrm_token,
663 p_token2_value => SQLERRM);
664 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
665 END validate_version;*/
666 --Fixed Bug # 5707866 ssdeshpa end
667 ---------------------------------------------------------------------------
668 -- PROCEDURE Validate_Unique_Avl_Record
669 ---------------------------------------------------------------------------
670 PROCEDURE Validate_Unique_Avl_Record(x_return_status OUT NOCOPY VARCHAR2
671 ,p_avlv_rec IN avlv_rec_type)
672 IS
673
674 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
675 l_unq_tbl OKC_UTIL.unq_tbl_type;
676 l_avlv_status VARCHAR2(1);
677 l_row_found BOOLEAN := FALSE;
678 l_dummy VARCHAR2(1);
679 l_start_date DATE;
680 l_end_date DATE;
681
682 CURSOR c1 IS
683 SELECT start_date,
684 end_date
685 FROM OKL_AE_TEMPLATES
686 WHERE try_id = p_avlv_rec.try_id
687 AND nvl(org_id,-99) = nvl(p_avlv_rec.org_id,-99)
688 AND sty_id = p_avlv_rec.sty_id
689 AND aes_id = p_avlv_rec.aes_id
690 AND nvl(advance_arrears,'AA') = nvl(p_avlv_rec.advance_arrears ,'AA')
691 AND memo_yn = p_avlv_rec.memo_yn
692 AND prior_year_yn = p_avlv_rec.prior_year_yn
693 AND nvl(factoring_synd_flag,'N') = nvl(p_Avlv_rec.factoring_synd_flag,'N')
694 AND id <> p_avlv_rec.ID;
695
696
697 CURSOR avl_csr(v_name OKL_AE_TEMPLATES.NAME%TYPE,
698 v_version OKL_AE_TEMPLATES.version%TYPE,
699 v_aes_id OKL_AE_TEMPLATES.aes_id%TYPE ) IS
700 SELECT '1'
701 FROM OKL_AE_TEMPLATES
702 WHERE name = v_name
703 AND version = v_version
704 AND aes_id = v_aes_id
705 AND ID <> p_avlv_rec.ID;
706
707
708
709 BEGIN
710
711 -- initialize return status
712 x_return_status := OKC_API.G_RET_STS_SUCCESS;
713
714 -- Following section commented by Kanti. This check will be enforced thru process
715 -- API.
716
717 /* OPEN c1;
718
719 FETCH c1 INTO l_start_date,
720 l_end_date;
721 l_row_found := c1%FOUND;
722 CLOSE c1;
723
724 -- If record with all the similar information exists then check that the new record
725 -- being inserted does not overlap with the existing date range
726
727 IF (l_row_found) THEN
728
729 IF (p_avlv_rec.start_date <= l_end_date) THEN
730 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
731 p_msg_name => 'OKL_TMPL_DATE_OVERLAPS');
732 x_return_status := OKC_API.G_RET_STS_ERROR;
733 RAISE G_EXCEPTION_HALT_VALIDATION;
734 END IF;
735
736 END IF;
737
738 */
739
740 OPEN avl_csr(p_avlv_rec.name,
741 p_avlv_rec.version,
742 p_avlv_rec.aes_id );
743 FETCH avl_csr INTO l_dummy;
744 IF (avl_csr%FOUND) THEN
745 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
746 p_msg_name => 'OKL_NAME_VERSION_NOT_UNIQUE');
747 x_return_status := OKC_API.G_RET_STS_ERROR;
748 CLOSE avl_csr;
749 RAISE G_EXCEPTION_HALT_VALIDATION;
750 END IF;
751 CLOSE avl_csr;
752
753 EXCEPTION
754 WHEN G_EXCEPTION_HALT_VALIDATION THEN
755 -- no processing necessary; validation can continue
756 -- with the next column
757 NULL;
758
759 WHEN OTHERS THEN
760 -- store SQL error message on message stack for caller
761 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
762 p_msg_name => g_unexpected_error,
763 p_token1 => g_sqlcode_token,
764 p_token1_value => SQLCODE,
765 p_token2 => g_sqlerrm_token,
766 p_token2_value => SQLERRM);
767
768 -- notify caller of an UNEXPECTED error
769 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
770
771 END Validate_Unique_Avl_Record;
772
773
774 ---------------------------------------------------------------------------
775 -- PROCEDURE validate_fma_id
776 -- 05/11/2001 Inserted by Robin Edwin for fk validation
777 ---------------------------------------------------------------------------
778
779 PROCEDURE validate_fma_id(x_return_status OUT NOCOPY VARCHAR2,
780 p_avlv_rec IN avlv_rec_type) IS
781
782 CURSOR okl_fma_id_csr(v_fma_id NUMBER) IS
783 SELECT '1'
784 FROM OKL_FORMULAE_V
785 WHERE id = v_fma_id;
786
787 l_fma_id NUMBER;
788 l_row_notfound BOOLEAN := TRUE;
789 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
790
791 BEGIN
792
793 x_return_status := OKC_API.G_RET_STS_SUCCESS;
794
795 IF (p_avlv_rec.fma_id IS NOT NULL) AND (p_avlv_rec.fma_id <> OKC_API.G_MISS_NUM) THEN
796 OPEN okl_fma_id_csr(p_avlv_rec.fma_id);
797 FETCH okl_fma_id_csr INTO l_fma_id;
798 l_row_notfound := okl_fma_id_csr%NOTFOUND;
799 CLOSE okl_fma_id_csr;
800
801 IF(l_row_notfound) THEN
802 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'FMA_ID');
803 x_return_status := OKC_API.G_RET_STS_ERROR;
804 RAISE G_EXCEPTION_HALT_VALIDATION;
805 END IF;
806
807 END IF;
808
809 EXCEPTION
810 WHEN G_EXCEPTION_HALT_VALIDATION THEN
811 NULL;
812 WHEN OTHERS THEN
813 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
814 p_msg_name => g_required_value,
815 p_token1 => g_sqlcode_token,
816 p_token1_value => SQLCODE,
817 p_token2 => g_sqlerrm_token,
818 p_token2_value => SQLERRM);
819 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
820
821 END validate_fma_id;
822
823
824 ---------------------------------------------------------------------------
825 -- PROCEDURE validate_memo_yn
826 -- 05/11/2001
827 ---------------------------------------------------------------------------
828 PROCEDURE validate_memo_yn(x_return_status OUT NOCOPY VARCHAR2,
829 p_avlv_rec IN avlv_rec_type) IS
830
831 l_dummy VARCHAR2(1);
832 l_app_id NUMBER := 0;
833 l_view_app_id NUMBER := 0;
834
835 BEGIN
836
837 x_return_status := OKC_API.G_RET_STS_SUCCESS;
838
839 IF (p_avlv_rec.MEMO_YN IS NULL) OR (p_avlv_rec.MEMO_YN = OKC_API.G_MISS_CHAR) THEN
840 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
841 p_msg_name => g_required_value,
842 p_token1 => g_col_name_token,
843 p_token1_value => 'MEMO_YN');
844
845 x_return_status := OKC_API.G_RET_STS_ERROR;
846 RAISE G_EXCEPTION_HALT_VALIDATION;
847 ELSE
848 l_dummy :=
849 OKL_ACCOUNTING_UTIL.validate_lookup_code(p_lookup_type => 'YES_NO',
850 p_lookup_code => p_avlv_rec.memo_yn,
851 p_app_id => l_app_id,
852 p_view_app_id => l_view_app_id);
853 IF (l_dummy = OKC_API.G_FALSE) THEN
854
855 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
856 p_msg_name => g_invalid_value,
857 p_token1 => g_col_name_token,
858 p_token1_value => 'MEMO_YN');
859
860 x_return_status := OKC_API.G_RET_STS_ERROR;
861 RAISE G_EXCEPTION_HALT_VALIDATION;
862
863 END IF;
864 END IF;
865
866 EXCEPTION
867 WHEN G_EXCEPTION_HALT_VALIDATION THEN
868 NULL;
869
870 WHEN OTHERS THEN
871 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
872 p_msg_name => g_required_value,
873 p_token1 => g_sqlcode_token,
874 p_token1_value => SQLCODE,
875 p_token2 => g_sqlerrm_token,
876 p_token2_value => SQLERRM);
877 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
878
879 END validate_memo_yn;
880
881 -- Santonyr 18th Jul, 2002.
882 -- Removed the validation as the field is removed from the screen
883
884 /*
885 ---------------------------------------------------------------------------
886 -- PROCEDURE validate_prior_year_yn
887 -- 05/11/2001
888 ---------------------------------------------------------------------------
889 PROCEDURE validate_prior_year_yn(x_return_status OUT NOCOPY VARCHAR2,
890 p_avlv_rec IN avlv_rec_type) IS
891
892 l_dummy VARCHAR2(1);
893 l_app_id NUMBER := 0;
894 l_view_app_id NUMBER := 0;
895
896 BEGIN
897
898 x_return_status := OKC_API.G_RET_STS_SUCCESS;
899
900 IF (p_avlv_rec.PRIOR_YEAR_YN IS NULL) OR (p_avlv_rec.PRIOR_YEAR_YN = OKC_API.G_MISS_CHAR) THEN
901 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
902 p_msg_name => g_required_value,
903 p_token1 => g_col_name_token,
904 p_token1_value => 'PRIOR_YEAR_YN');
905
906 x_return_status := OKC_API.G_RET_STS_ERROR;
907 RAISE G_EXCEPTION_HALT_VALIDATION;
908 ELSE
909 l_dummy :=
910 OKL_ACCOUNTING_UTIL.validate_lookup_code(p_lookup_type => 'YES_NO',
911 p_lookup_code => p_avlv_rec.prior_year_yn,
912 p_app_id => l_app_id,
913 p_view_app_id => l_view_app_id);
914 IF (l_dummy = OKC_API.G_FALSE) THEN
915
916 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
917 p_msg_name => g_invalid_value,
918 p_token1 => g_col_name_token,
919 p_token1_value => 'PRIOR_YEAR_YN');
920
921 x_return_status := OKC_API.G_RET_STS_ERROR;
922 RAISE G_EXCEPTION_HALT_VALIDATION;
923
924 END IF;
925 END IF;
926
927 EXCEPTION
928 WHEN G_EXCEPTION_HALT_VALIDATION THEN
929 NULL;
930
931 WHEN OTHERS THEN
932 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
933 p_msg_name => g_required_value,
934 p_token1 => g_sqlcode_token,
935 p_token1_value => SQLCODE,
936 p_token2 => g_sqlerrm_token,
937 p_token2_value => SQLERRM);
938 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
939
940 END validate_prior_year_yn;
941 */
942
943 -- Added by HKPATEL for securitization changes
944 ---------------------------------------------------------------------------
945 -- PROCEDURE validate_inv_code
946 ---------------------------------------------------------------------------
947
948 PROCEDURE validate_inv_code(
949 x_return_status OUT NOCOPY VARCHAR2,
950 p_avlv_rec IN avlv_rec_type
951 ) IS
952 l_dummy VARCHAR2(1);
953
954 BEGIN
955
956 x_return_status := OKC_API.G_RET_STS_SUCCESS;
957
958 IF (p_avlv_rec.inv_code IS NOT NULL) AND (p_avlv_rec.inv_code <> OKC_API.G_MISS_CHAR) THEN
959
960 l_dummy :=
961 OKL_ACCOUNTING_UTIL.validate_lookup_code (p_lookup_type => 'OKL_INVESTOR_CODE',
962 p_lookup_code => p_avlv_rec.inv_code);
963
964 IF (l_dummy = OKC_API.G_FALSE) THEN
965
966 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
967 p_msg_name => g_invalid_value,
968 p_token1 => g_col_name_token,
969 p_token1_value => 'INV_CODE');
970
971 x_return_status := OKC_API.G_RET_STS_ERROR;
972 RAISE G_EXCEPTION_HALT_VALIDATION;
973
974 END IF;
975
976 END IF;
977
978 EXCEPTION
979 WHEN G_EXCEPTION_HALT_VALIDATION THEN
980 NULL;
981 WHEN OTHERS THEN
982 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
983 p_msg_name => g_required_value,
984 p_token1 => g_sqlcode_token,
985 p_token1_value => SQLCODE,
986 p_token2 => g_sqlerrm_token,
987 p_token2_value => SQLERRM);
988 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
989 END validate_inv_code;
990
991 ---------------------------------------------------------------------------
992 -- FUNCTION get_seq_id
993 ---------------------------------------------------------------------------
994 ---------------------------------------------------------------------------
995 -- FUNCTION get_seq_id
996 ---------------------------------------------------------------------------
997 FUNCTION get_seq_id RETURN NUMBER IS
998 BEGIN
999 RETURN(okc_p_util.raw_to_number(sys_guid()));
1000 END get_seq_id;
1001
1002 ---------------------------------------------------------------------------
1003 -- PROCEDURE qc
1004 ---------------------------------------------------------------------------
1005 PROCEDURE qc IS
1006 BEGIN
1007 NULL;
1008 END qc;
1009
1010 ---------------------------------------------------------------------------
1011 -- PROCEDURE change_version
1012 ---------------------------------------------------------------------------
1013 PROCEDURE change_version IS
1014 BEGIN
1015 NULL;
1016 END change_version;
1017
1018 ---------------------------------------------------------------------------
1019 -- PROCEDURE api_copy
1020 ---------------------------------------------------------------------------
1021 PROCEDURE api_copy IS
1022 BEGIN
1023 NULL;
1024 END api_copy;
1025
1026 ---------------------------------------------------------------------------
1027 -- FUNCTION get_rec for: OKL_AE_TEMPLATES
1028 ---------------------------------------------------------------------------
1029 FUNCTION get_rec (
1030 p_avl_rec IN avl_rec_type,
1031 x_no_data_found OUT NOCOPY BOOLEAN
1032 ) RETURN avl_rec_type IS
1033 CURSOR okl_ae_templates_pk_csr (p_id IN NUMBER) IS
1034 SELECT
1035 ID,
1036 NAME,
1037 SET_OF_BOOKS_ID,
1038 STY_ID,
1039 TRY_ID,
1040 AES_ID,
1041 SYT_CODE,
1042 FAC_CODE,
1043 FMA_ID,
1044 ADVANCE_ARREARS,
1045 POST_TO_GL,
1046 VERSION,
1047 START_DATE,
1048 OBJECT_VERSION_NUMBER,
1049 MEMO_YN,
1050 PRIOR_YEAR_YN,
1051 DESCRIPTION,
1052 FACTORING_SYND_FLAG,
1053 END_DATE,
1054 ACCRUAL_YN,
1055 ORG_ID,
1056 ATTRIBUTE_CATEGORY,
1057 ATTRIBUTE1,
1058 ATTRIBUTE2,
1059 ATTRIBUTE3,
1060 ATTRIBUTE4,
1061 ATTRIBUTE5,
1062 ATTRIBUTE6,
1063 ATTRIBUTE7,
1064 ATTRIBUTE8,
1065 ATTRIBUTE9,
1066 ATTRIBUTE10,
1067 ATTRIBUTE11,
1068 ATTRIBUTE12,
1069 ATTRIBUTE13,
1070 ATTRIBUTE14,
1071 ATTRIBUTE15,
1072 CREATED_BY,
1073 CREATION_DATE,
1074 LAST_UPDATED_BY,
1075 LAST_UPDATE_DATE,
1076 LAST_UPDATE_LOGIN,
1077 -- Added by HKPATEL for securitization changes
1078 INV_CODE
1079 FROM Okl_Ae_Templates
1080 WHERE okl_ae_templates.id = p_id;
1081 l_okl_ae_templates_pk okl_ae_templates_pk_csr%ROWTYPE;
1082 l_avl_rec avl_rec_type;
1083 BEGIN
1084 x_no_data_found := TRUE;
1085 -- Get current database values
1086 OPEN okl_ae_templates_pk_csr (p_avl_rec.id);
1087 FETCH okl_ae_templates_pk_csr INTO
1088 l_avl_rec.ID,
1089 l_avl_rec.NAME,
1090 l_avl_rec.SET_OF_BOOKS_ID,
1091 l_avl_rec.STY_ID,
1092 l_avl_rec.TRY_ID,
1093 l_avl_rec.AES_ID,
1094 l_avl_rec.SYT_CODE,
1095 l_avl_rec.FAC_CODE,
1096 l_avl_rec.FMA_ID,
1097 l_avl_rec.ADVANCE_ARREARS,
1098 l_avl_rec.POST_TO_GL,
1099 l_avl_rec.VERSION,
1100 l_avl_rec.START_DATE,
1101 l_avl_rec.OBJECT_VERSION_NUMBER,
1102 l_avl_rec.MEMO_YN,
1103 l_avl_rec.PRIOR_YEAR_YN,
1104 l_avl_rec.DESCRIPTION,
1105 l_avl_rec.FACTORING_SYND_FLAG,
1106 l_avl_rec.END_DATE,
1107 l_avl_rec.ACCRUAL_YN,
1108 l_avl_rec.ORG_ID,
1109 l_avl_rec.ATTRIBUTE_CATEGORY,
1110 l_avl_rec.ATTRIBUTE1,
1111 l_avl_rec.ATTRIBUTE2,
1112 l_avl_rec.ATTRIBUTE3,
1113 l_avl_rec.ATTRIBUTE4,
1114 l_avl_rec.ATTRIBUTE5,
1115 l_avl_rec.ATTRIBUTE6,
1116 l_avl_rec.ATTRIBUTE7,
1117 l_avl_rec.ATTRIBUTE8,
1118 l_avl_rec.ATTRIBUTE9,
1119 l_avl_rec.ATTRIBUTE10,
1120 l_avl_rec.ATTRIBUTE11,
1121 l_avl_rec.ATTRIBUTE12,
1122 l_avl_rec.ATTRIBUTE13,
1123 l_avl_rec.ATTRIBUTE14,
1124 l_avl_rec.ATTRIBUTE15,
1125 l_avl_rec.CREATED_BY,
1126 l_avl_rec.CREATION_DATE,
1127 l_avl_rec.LAST_UPDATED_BY,
1128 l_avl_rec.LAST_UPDATE_DATE,
1129 l_avl_rec.LAST_UPDATE_LOGIN,
1130 -- Added by HKPATEL for securitization changes
1131 l_avl_rec.INV_CODE;
1132 x_no_data_found := okl_ae_templates_pk_csr%NOTFOUND;
1133 CLOSE okl_ae_templates_pk_csr;
1134 RETURN(l_avl_rec);
1135 END get_rec;
1136
1137 FUNCTION get_rec (
1138 p_avl_rec IN avl_rec_type
1139 ) RETURN avl_rec_type IS
1140 l_row_notfound BOOLEAN := TRUE;
1141 BEGIN
1142 RETURN(get_rec(p_avl_rec, l_row_notfound));
1143 END get_rec;
1144 ---------------------------------------------------------------------------
1145 -- FUNCTION get_rec for: OKL_AE_TEMPLATES_V
1146 ---------------------------------------------------------------------------
1147 FUNCTION get_rec (
1148 p_avlv_rec IN avlv_rec_type,
1149 x_no_data_found OUT NOCOPY BOOLEAN
1150 ) RETURN avlv_rec_type IS
1151 CURSOR okl_avlv_pk_csr (p_id IN NUMBER) IS
1152 SELECT
1153 ID,
1154 OBJECT_VERSION_NUMBER,
1155 TRY_ID,
1156 AES_ID,
1157 STY_ID,
1158 FMA_ID,
1159 SET_OF_BOOKS_ID,
1160 FAC_CODE,
1161 SYT_CODE,
1162 POST_TO_GL,
1163 ADVANCE_ARREARS,
1164 MEMO_YN,
1165 PRIOR_YEAR_YN,
1166 NAME,
1167 DESCRIPTION,
1168 VERSION,
1169 FACTORING_SYND_FLAG,
1170 START_DATE,
1171 END_DATE,
1172 ACCRUAL_YN,
1173 ATTRIBUTE_CATEGORY,
1174 ATTRIBUTE1,
1175 ATTRIBUTE2,
1176 ATTRIBUTE3,
1177 ATTRIBUTE4,
1178 ATTRIBUTE5,
1179 ATTRIBUTE6,
1180 ATTRIBUTE7,
1181 ATTRIBUTE8,
1182 ATTRIBUTE9,
1183 ATTRIBUTE10,
1184 ATTRIBUTE11,
1185 ATTRIBUTE12,
1186 ATTRIBUTE13,
1187 ATTRIBUTE14,
1188 ATTRIBUTE15,
1189 ORG_ID,
1190 CREATED_BY,
1191 CREATION_DATE,
1192 LAST_UPDATED_BY,
1193 LAST_UPDATE_DATE,
1194 LAST_UPDATE_LOGIN,
1195 -- Added by HKPATEL for securitization changes
1196 INV_CODE
1197 FROM OKL_AE_TEMPLATES
1198 WHERE OKL_AE_TEMPLATES.id = p_id;
1199 l_okl_avlv_pk okl_avlv_pk_csr%ROWTYPE;
1200 l_avlv_rec avlv_rec_type;
1201 BEGIN
1202 x_no_data_found := TRUE;
1203 -- Get current database values
1204 OPEN okl_avlv_pk_csr (p_avlv_rec.id);
1205 FETCH okl_avlv_pk_csr INTO
1206 l_avlv_rec.ID,
1207 l_avlv_rec.OBJECT_VERSION_NUMBER,
1208 l_avlv_rec.TRY_ID,
1209 l_avlv_rec.AES_ID,
1210 l_avlv_rec.STY_ID,
1211 l_avlv_rec.FMA_ID,
1212 l_avlv_rec.SET_OF_BOOKS_ID,
1213 l_avlv_rec.FAC_CODE,
1214 l_avlv_rec.SYT_CODE,
1215 l_avlv_rec.POST_TO_GL,
1216 l_avlv_rec.ADVANCE_ARREARS,
1217 l_avlv_rec.MEMO_YN,
1218 l_avlv_rec.PRIOR_YEAR_YN,
1219 l_avlv_rec.NAME,
1220 l_avlv_rec.DESCRIPTION,
1221 l_avlv_rec.VERSION,
1222 l_avlv_rec.FACTORING_SYND_FLAG,
1223 l_avlv_rec.START_DATE,
1224 l_avlv_rec.END_DATE,
1225 l_avlv_rec.ACCRUAL_YN,
1226 l_avlv_rec.ATTRIBUTE_CATEGORY,
1227 l_avlv_rec.ATTRIBUTE1,
1228 l_avlv_rec.ATTRIBUTE2,
1229 l_avlv_rec.ATTRIBUTE3,
1230 l_avlv_rec.ATTRIBUTE4,
1231 l_avlv_rec.ATTRIBUTE5,
1232 l_avlv_rec.ATTRIBUTE6,
1233 l_avlv_rec.ATTRIBUTE7,
1234 l_avlv_rec.ATTRIBUTE8,
1235 l_avlv_rec.ATTRIBUTE9,
1236 l_avlv_rec.ATTRIBUTE10,
1237 l_avlv_rec.ATTRIBUTE11,
1238 l_avlv_rec.ATTRIBUTE12,
1239 l_avlv_rec.ATTRIBUTE13,
1240 l_avlv_rec.ATTRIBUTE14,
1241 l_avlv_rec.ATTRIBUTE15,
1242 l_avlv_rec.ORG_ID,
1243 l_avlv_rec.CREATED_BY,
1244 l_avlv_rec.CREATION_DATE,
1245 l_avlv_rec.LAST_UPDATED_BY,
1246 l_avlv_rec.LAST_UPDATE_DATE,
1247 l_avlv_rec.LAST_UPDATE_LOGIN,
1248 -- Added by HKPATEL for securitization changes
1249 l_avlv_rec.INV_CODE;
1250 x_no_data_found := okl_avlv_pk_csr%NOTFOUND;
1251 CLOSE okl_avlv_pk_csr;
1252 RETURN(l_avlv_rec);
1253 END get_rec;
1254
1255 FUNCTION get_rec (
1256 p_avlv_rec IN avlv_rec_type
1257 ) RETURN avlv_rec_type IS
1258 l_row_notfound BOOLEAN := TRUE;
1259 BEGIN
1260 RETURN(get_rec(p_avlv_rec, l_row_notfound));
1261 END get_rec;
1262
1263 --------------------------------------------------------
1264 -- FUNCTION null_out_defaults for: OKL_AE_TEMPLATES_V --
1265 --------------------------------------------------------
1266 FUNCTION null_out_defaults (
1267 p_avlv_rec IN avlv_rec_type
1268 ) RETURN avlv_rec_type IS
1269 l_avlv_rec avlv_rec_type := p_avlv_rec;
1270 BEGIN
1271 IF (l_avlv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
1272 l_avlv_rec.object_version_number := NULL;
1273 END IF;
1274 IF (l_avlv_rec.try_id = OKC_API.G_MISS_NUM) THEN
1275 l_avlv_rec.try_id := NULL;
1276 END IF;
1277 IF (l_avlv_rec.aes_id = OKC_API.G_MISS_NUM) THEN
1278 l_avlv_rec.aes_id := NULL;
1279 END IF;
1280 IF (l_avlv_rec.sty_id = OKC_API.G_MISS_NUM) THEN
1281 l_avlv_rec.sty_id := NULL;
1282 END IF;
1283 IF (l_avlv_rec.fma_id = OKC_API.G_MISS_NUM) THEN
1284 l_avlv_rec.fma_id := NULL;
1285 END IF;
1286 IF (l_avlv_rec.set_of_books_id = OKC_API.G_MISS_NUM) THEN
1287 l_avlv_rec.set_of_books_id := NULL;
1288 END IF;
1289 IF (l_avlv_rec.fac_code = OKC_API.G_MISS_CHAR) THEN
1290 l_avlv_rec.fac_code := NULL;
1291 END IF;
1292 IF (l_avlv_rec.syt_code = OKC_API.G_MISS_CHAR) THEN
1293 l_avlv_rec.syt_code := NULL;
1294 END IF;
1295 IF (l_avlv_rec.post_to_gl = OKC_API.G_MISS_CHAR) THEN
1296 l_avlv_rec.post_to_gl := NULL;
1297 END IF;
1298 IF (l_avlv_rec.advance_arrears = OKC_API.G_MISS_CHAR) THEN
1299 l_avlv_rec.advance_arrears := NULL;
1300 END IF;
1301 IF (l_avlv_rec.memo_yn = OKC_API.G_MISS_CHAR) THEN
1302 l_avlv_rec.memo_yn := NULL;
1303 END IF;
1304 IF (l_avlv_rec.prior_year_yn = OKC_API.G_MISS_CHAR) THEN
1305 l_avlv_rec.prior_year_yn := NULL;
1306 END IF;
1307 IF (l_avlv_rec.name = OKC_API.G_MISS_CHAR) THEN
1308 l_avlv_rec.name := NULL;
1309 END IF;
1310 IF (l_avlv_rec.description = OKC_API.G_MISS_CHAR) THEN
1311 l_avlv_rec.description := NULL;
1312 END IF;
1313 IF (l_avlv_rec.version = OKC_API.G_MISS_CHAR) THEN
1314 l_avlv_rec.version := NULL;
1315 END IF;
1316 IF (l_avlv_rec.factoring_synd_flag = OKC_API.G_MISS_CHAR) THEN
1317 l_avlv_rec.factoring_synd_flag := NULL;
1318 END IF;
1319 IF (l_avlv_rec.start_date = OKC_API.G_MISS_DATE) THEN
1320 l_avlv_rec.start_date := NULL;
1321 END IF;
1322 IF (l_avlv_rec.end_date = OKC_API.G_MISS_DATE) THEN
1323 l_avlv_rec.end_date := NULL;
1324 END IF;
1325 IF (l_avlv_rec.accrual_yn = OKC_API.G_MISS_CHAR) THEN
1326 l_avlv_rec.accrual_yn := NULL;
1327 END IF;
1328 IF (l_avlv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
1329 l_avlv_rec.attribute_category := NULL;
1330 END IF;
1331 IF (l_avlv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
1332 l_avlv_rec.attribute1 := NULL;
1333 END IF;
1334 IF (l_avlv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
1335 l_avlv_rec.attribute2 := NULL;
1336 END IF;
1337 IF (l_avlv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
1338 l_avlv_rec.attribute3 := NULL;
1339 END IF;
1340 IF (l_avlv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
1341 l_avlv_rec.attribute4 := NULL;
1342 END IF;
1343 IF (l_avlv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
1344 l_avlv_rec.attribute5 := NULL;
1345 END IF;
1346 IF (l_avlv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
1347 l_avlv_rec.attribute6 := NULL;
1348 END IF;
1349 IF (l_avlv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
1350 l_avlv_rec.attribute7 := NULL;
1351 END IF;
1352 IF (l_avlv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
1353 l_avlv_rec.attribute8 := NULL;
1354 END IF;
1355 IF (l_avlv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
1356 l_avlv_rec.attribute9 := NULL;
1357 END IF;
1358 IF (l_avlv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
1359 l_avlv_rec.attribute10 := NULL;
1360 END IF;
1361 IF (l_avlv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
1362 l_avlv_rec.attribute11 := NULL;
1363 END IF;
1364 IF (l_avlv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
1365 l_avlv_rec.attribute12 := NULL;
1366 END IF;
1367 IF (l_avlv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
1368 l_avlv_rec.attribute13 := NULL;
1369 END IF;
1370 IF (l_avlv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
1371 l_avlv_rec.attribute14 := NULL;
1372 END IF;
1373 IF (l_avlv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
1374 l_avlv_rec.attribute15 := NULL;
1375 END IF;
1376 IF (l_avlv_rec.org_id = OKC_API.G_MISS_NUM) THEN
1377 l_avlv_rec.org_id := NULL;
1378 END IF;
1379 IF (l_avlv_rec.created_by = OKC_API.G_MISS_NUM) THEN
1380 l_avlv_rec.created_by := NULL;
1381 END IF;
1382 IF (l_avlv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
1383 l_avlv_rec.creation_date := NULL;
1384 END IF;
1385 IF (l_avlv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
1386 l_avlv_rec.last_updated_by := NULL;
1387 END IF;
1388 IF (l_avlv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
1389 l_avlv_rec.last_update_date := NULL;
1390 END IF;
1391 IF (l_avlv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
1392 l_avlv_rec.last_update_login := NULL;
1393 END IF;
1394 -- Added by HKPATEL for securitization channges
1395 IF (l_avlv_rec.inv_code = OKC_API.G_MISS_CHAR) THEN
1396 l_avlv_rec.inv_code := NULL;
1397 END IF;
1398 RETURN(l_avlv_rec);
1399 END null_out_defaults;
1400 ---------------------------------------------------------------------------
1401 -- PROCEDURE Validate_Attributes
1402 ---------------------------------------------------------------------------
1403 ------------------------------------------------
1404 -- Validate_Attributes for:OKL_AE_TEMPLATES_V --
1405 ------------------------------------------------
1406 FUNCTION Validate_Attributes (
1407 p_avlv_rec IN avlv_rec_type
1408 ) RETURN VARCHAR2 IS
1409
1410 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1411 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1412
1413 BEGIN
1414
1415 validate_name(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1416 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1417 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1418 x_return_status := l_return_status;
1419 END IF;
1420 END IF;
1421
1422 /* Fixed Bug # 5707866 ssdeshpa start
1423 --Removing Validation Logic for SET_OF_BOOKS_ID
1424 --Since This Column are NULL */
1425 /* validate_set_of_books_id(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1426 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1427 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1428 x_return_status := l_return_status;
1429 END IF;
1430 END IF; */
1431 --Fixed Bug # 5707866 ssdeshpa end
1432
1433 validate_sty_id(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1434 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1435 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1436 x_return_status := l_return_status;
1437 END IF;
1438 END IF;
1439
1440 validate_try_id(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1441 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1442 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1443 x_return_status := l_return_status;
1444 END IF;
1445 END IF;
1446
1447 validate_aes_id(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1448 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1449 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1450 x_return_status := l_return_status;
1451 END IF;
1452 END IF;
1453
1454 validate_syt_code(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1455 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1456 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1457 x_return_status := l_return_status;
1458 END IF;
1459 END IF;
1460
1461 validate_fac_code(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1462 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1463 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1464 x_return_status := l_return_status;
1465 END IF;
1466 END IF;
1467
1468
1469 validate_factoring_synd_flag(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1470 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1471 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1472 x_return_status := l_return_status;
1473 END IF;
1474 END IF;
1475
1476 validate_fma_id(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1477 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1478 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1479 x_return_status := l_return_status;
1480 END IF;
1481 END IF;
1482
1483 -- Santonyr 18th Jul, 2002.
1484 -- Removed the validation as the field is removed from the screen
1485
1486 /*
1487 validate_advance_arrears(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1488 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1489 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1490 x_return_status := l_return_status;
1491 END IF;
1492 END IF;
1493 */
1494 /* Fixed Bug # 5707866 ssdeshpa start
1495 --Removing Validation Logic for POST_TO_GL
1496 --Since This Column are NULL */
1497 /*validate_post_to_gl(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1498 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1499 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1500 x_return_status := l_return_status;
1501 END IF;
1502 END IF; */
1503 -- Fixed Bug # 5707866 ssdeshpa End
1504 -- Santonyr 18th Jul, 2002.
1505 -- Removed the validation as the field is removed from the screen
1506
1507 /*
1508 validate_accrual_yn(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1509 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1510 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1511 x_return_status := l_return_status;
1512 END IF;
1513 END IF;
1514 */
1515 /* Fixed Bug # 5707866 ssdeshpa start
1516 --Removing Validation Logic for VERSION
1517 --Since This Column are NULL */
1518 /* validate_version(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1519 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1520 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1521 x_return_status := l_return_status;
1522 END IF;
1523 END IF;*/
1524 --Fixed Bug # 5707866 ssdeshpa End
1525
1526 validate_memo_yn(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1527 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1528 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1529 x_return_status := l_return_status;
1530 END IF;
1531 END IF;
1532
1533 -- Santonyr 18th Jul, 2002.
1534 -- Removed the validation as the field is removed from the screen
1535
1536 /*
1537 validate_prior_year_yn(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1538 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1539 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1540 x_return_status := l_return_status;
1541 END IF;
1542 END IF;
1543 */
1544 -- Added by HKPATEL for securitization changes
1545 validate_inv_code(x_return_status => l_return_status, p_avlv_rec => p_avlv_rec);
1546 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1547 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1548 x_return_status := l_return_status;
1549 END IF;
1550 END IF;
1551
1552 RETURN(x_return_status);
1553
1554 EXCEPTION
1555 WHEN OTHERS THEN
1556 -- store SQL error message on message stack for caller
1557 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1558 p_msg_name => g_unexpected_error,
1559 p_token1 => g_sqlcode_token,
1560 p_token1_value => SQLCODE,
1561 p_token2 => g_sqlerrm_token,
1562 p_token2_value => SQLERRM);
1563
1564 --notify caller of an UNEXPECTED error
1565 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1566
1567 --return status to caller
1568 RETURN x_return_status;
1569 END;
1570
1571 --END Validate_Attributes;
1572
1573 ---------------------------------------------------------------------------
1574 -- PROCEDURE Validate_Record
1575 ---------------------------------------------------------------------------
1576 --------------------------------------------
1577 -- Validate_Record for:OKL_AE_TEMPLATES_V --
1578 --------------------------------------------
1579 FUNCTION Validate_Record (
1580 p_avlv_rec IN avlv_rec_type
1581 ) RETURN VARCHAR2 IS
1582 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1583 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1584 BEGIN
1585 -- Validate_Unique_Tcn_Record
1586 Validate_Unique_Avl_Record(x_return_status, p_avlv_rec);
1587 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1588 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1589 -- need to leave
1590 l_return_status := x_return_status;
1591 RAISE G_EXCEPTION_HALT_VALIDATION;
1592 ELSE
1593 -- record that there was an error
1594 l_return_status := x_return_status;
1595 END IF;
1596 END IF;
1597
1598 RETURN(l_return_status);
1599 EXCEPTION
1600 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1601 -- exit with return status
1602 NULL;
1603 RETURN (l_return_status);
1604
1605 WHEN OTHERS THEN
1606 -- store SQL error message on message stack for caller
1607 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1608 p_msg_name => g_unexpected_error,
1609 p_token1 => g_sqlcode_token,
1610 p_token1_value => SQLCODE,
1611 p_token2 => g_sqlerrm_token,
1612 p_token2_value => SQLERRM);
1613 -- notify caller of an UNEXPECTED error
1614 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1615 RETURN(l_return_status);
1616 END Validate_Record;
1617
1618 ---------------------------------------------------------------------------
1619 -- PROCEDURE Migrate
1620 ---------------------------------------------------------------------------
1621 PROCEDURE migrate (
1622 p_from IN avlv_rec_type,
1623 p_to IN OUT NOCOPY avl_rec_type
1624 ) IS
1625 BEGIN
1626 p_to.id := p_from.id;
1627 p_to.name := p_from.name;
1628 p_to.set_of_books_id := p_from.set_of_books_id;
1629 p_to.sty_id := p_from.sty_id;
1630 p_to.try_id := p_from.try_id;
1631 p_to.aes_id := p_from.aes_id;
1632 p_to.syt_code := p_from.syt_code;
1633 p_to.fac_code := p_from.fac_code;
1634 p_to.fma_id := p_from.fma_id;
1635 p_to.advance_arrears := p_from.advance_arrears;
1636 p_to.post_to_gl := p_from.post_to_gl;
1637 p_to.version := p_from.version;
1638 p_to.start_date := p_from.start_date;
1639 p_to.object_version_number := p_from.object_version_number;
1640 p_to.memo_yn := p_from.memo_yn;
1641 p_to.prior_year_yn := p_from.prior_year_yn;
1642 p_to.description := p_from.description;
1643 p_to.factoring_synd_flag := p_from.factoring_synd_flag;
1644 p_to.end_date := p_from.end_date;
1645 p_to.accrual_yn := p_from.accrual_yn;
1646 p_to.org_id := p_from.org_id;
1647 p_to.attribute_category := p_from.attribute_category;
1648 p_to.attribute1 := p_from.attribute1;
1649 p_to.attribute2 := p_from.attribute2;
1650 p_to.attribute3 := p_from.attribute3;
1651 p_to.attribute4 := p_from.attribute4;
1652 p_to.attribute5 := p_from.attribute5;
1653 p_to.attribute6 := p_from.attribute6;
1654 p_to.attribute7 := p_from.attribute7;
1655 p_to.attribute8 := p_from.attribute8;
1656 p_to.attribute9 := p_from.attribute9;
1657 p_to.attribute10 := p_from.attribute10;
1658 p_to.attribute11 := p_from.attribute11;
1659 p_to.attribute12 := p_from.attribute12;
1660 p_to.attribute13 := p_from.attribute13;
1661 p_to.attribute14 := p_from.attribute14;
1662 p_to.attribute15 := p_from.attribute15;
1663 p_to.created_by := p_from.created_by;
1664 p_to.creation_date := p_from.creation_date;
1665 p_to.last_updated_by := p_from.last_updated_by;
1666 p_to.last_update_date := p_from.last_update_date;
1667 p_to.last_update_login := p_from.last_update_login;
1668 -- Added by HKPATEL for securitization changes
1669 p_to.inv_code := p_from.inv_code;
1670 END migrate;
1671 PROCEDURE migrate (
1672 p_from IN avl_rec_type,
1673 p_to OUT NOCOPY avlv_rec_type
1674 ) IS
1675 BEGIN
1676 p_to.id := p_from.id;
1677 p_to.name := p_from.name;
1678 p_to.set_of_books_id := p_from.set_of_books_id;
1679 p_to.sty_id := p_from.sty_id;
1680 p_to.try_id := p_from.try_id;
1681 p_to.aes_id := p_from.aes_id;
1682 p_to.syt_code := p_from.syt_code;
1683 p_to.fac_code := p_from.fac_code;
1684 p_to.fma_id := p_from.fma_id;
1685 p_to.advance_arrears := p_from.advance_arrears;
1686 p_to.post_to_gl := p_from.post_to_gl;
1687 p_to.version := p_from.version;
1688 p_to.start_date := p_from.start_date;
1689 p_to.object_version_number := p_from.object_version_number;
1690 p_to.memo_yn := p_from.memo_yn;
1691 p_to.prior_year_yn := p_from.prior_year_yn;
1692 p_to.description := p_from.description;
1693 p_to.factoring_synd_flag := p_from.factoring_synd_flag;
1694 p_to.end_date := p_from.end_date;
1695 p_to.accrual_yn := p_from.accrual_yn;
1696 p_to.org_id := p_from.org_id;
1697 p_to.attribute_category := p_from.attribute_category;
1698 p_to.attribute1 := p_from.attribute1;
1699 p_to.attribute2 := p_from.attribute2;
1700 p_to.attribute3 := p_from.attribute3;
1701 p_to.attribute4 := p_from.attribute4;
1702 p_to.attribute5 := p_from.attribute5;
1703 p_to.attribute6 := p_from.attribute6;
1704 p_to.attribute7 := p_from.attribute7;
1705 p_to.attribute8 := p_from.attribute8;
1706 p_to.attribute9 := p_from.attribute9;
1707 p_to.attribute10 := p_from.attribute10;
1708 p_to.attribute11 := p_from.attribute11;
1709 p_to.attribute12 := p_from.attribute12;
1710 p_to.attribute13 := p_from.attribute13;
1711 p_to.attribute14 := p_from.attribute14;
1712 p_to.attribute15 := p_from.attribute15;
1713 p_to.created_by := p_from.created_by;
1714 p_to.creation_date := p_from.creation_date;
1715 p_to.last_updated_by := p_from.last_updated_by;
1716 p_to.last_update_date := p_from.last_update_date;
1717 p_to.last_update_login := p_from.last_update_login;
1718 -- Added by HKPATEL for securitization changes
1719 p_to.inv_code := p_from.inv_code;
1720 END migrate;
1721
1722 ---------------------------------------------------------------------------
1723 -- PROCEDURE validate_row
1724 ---------------------------------------------------------------------------
1725 -----------------------------------------
1726 -- validate_row for:OKL_AE_TEMPLATES_V --
1727 -----------------------------------------
1728 PROCEDURE validate_row(
1729 p_api_version IN NUMBER,
1730 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1731 x_return_status OUT NOCOPY VARCHAR2,
1732 x_msg_count OUT NOCOPY NUMBER,
1733 x_msg_data OUT NOCOPY VARCHAR2,
1734 p_avlv_rec IN avlv_rec_type) IS
1735
1736 l_api_version CONSTANT NUMBER := 1;
1737 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1738 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1739 l_avlv_rec avlv_rec_type := p_avlv_rec;
1740 l_avl_rec avl_rec_type;
1741 BEGIN
1742 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1743 G_PKG_NAME,
1744 p_init_msg_list,
1745 l_api_version,
1746 p_api_version,
1747 '_PVT',
1748 x_return_status);
1749 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1750 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1751 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1752 RAISE OKC_API.G_EXCEPTION_ERROR;
1753 END IF;
1754 --- Validate all non-missing attributes (Item Level Validation)
1755 l_return_status := Validate_Attributes(l_avlv_rec);
1756 --- If any errors happen abort API
1757 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1758 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1759 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1760 RAISE OKC_API.G_EXCEPTION_ERROR;
1761 END IF;
1762 l_return_status := Validate_Record(l_avlv_rec);
1763 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1764 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1765 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1766 RAISE OKC_API.G_EXCEPTION_ERROR;
1767 END IF;
1768 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1769 EXCEPTION
1770 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1771 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1772 (
1773 l_api_name,
1774 G_PKG_NAME,
1775 'OKC_API.G_RET_STS_ERROR',
1776 x_msg_count,
1777 x_msg_data,
1778 '_PVT'
1779 );
1780 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1781 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1782 (
1783 l_api_name,
1784 G_PKG_NAME,
1785 'OKC_API.G_RET_STS_UNEXP_ERROR',
1786 x_msg_count,
1787 x_msg_data,
1788 '_PVT'
1789 );
1790 WHEN OTHERS THEN
1791 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1792 (
1793 l_api_name,
1794 G_PKG_NAME,
1795 'OTHERS',
1796 x_msg_count,
1797 x_msg_data,
1798 '_PVT'
1799 );
1800 END validate_row;
1801 ------------------------------------------
1802 -- PL/SQL TBL validate_row for:AVLV_TBL --
1803 ------------------------------------------
1804 PROCEDURE validate_row(
1805 p_api_version IN NUMBER,
1806 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1807 x_return_status OUT NOCOPY VARCHAR2,
1808 x_msg_count OUT NOCOPY NUMBER,
1809 x_msg_data OUT NOCOPY VARCHAR2,
1810 p_avlv_tbl IN avlv_tbl_type) IS
1811
1812 l_api_version CONSTANT NUMBER := 1;
1813 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1814 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1815 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1816 i NUMBER := 0;
1817 BEGIN
1818 OKC_API.init_msg_list(p_init_msg_list);
1819 -- Make sure PL/SQL table has records in it before passing
1820 IF (p_avlv_tbl.COUNT > 0) THEN
1821 i := p_avlv_tbl.FIRST;
1822 LOOP
1823 validate_row (
1824 p_api_version => p_api_version,
1825 p_init_msg_list => OKC_API.G_FALSE,
1826 x_return_status => x_return_status,
1827 x_msg_count => x_msg_count,
1828 x_msg_data => x_msg_data,
1829 p_avlv_rec => p_avlv_tbl(i));
1830 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1831 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1832 l_overall_status := x_return_status;
1833 END IF;
1834 END IF;
1835 EXIT WHEN (i = p_avlv_tbl.LAST);
1836 i := p_avlv_tbl.NEXT(i);
1837 END LOOP;
1838 x_return_status := l_overall_status;
1839 END IF;
1840 EXCEPTION
1841 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1842 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1843 (
1844 l_api_name,
1845 G_PKG_NAME,
1846 'OKC_API.G_RET_STS_ERROR',
1847 x_msg_count,
1848 x_msg_data,
1849 '_PVT'
1850 );
1851 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1852 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1853 (
1854 l_api_name,
1855 G_PKG_NAME,
1856 'OKC_API.G_RET_STS_UNEXP_ERROR',
1857 x_msg_count,
1858 x_msg_data,
1859 '_PVT'
1860 );
1861 WHEN OTHERS THEN
1862 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1863 (
1864 l_api_name,
1865 G_PKG_NAME,
1866 'OTHERS',
1867 x_msg_count,
1868 x_msg_data,
1869 '_PVT'
1870 );
1871 END validate_row;
1872
1873 ---------------------------------------------------------------------------
1874 -- PROCEDURE insert_row
1875 ---------------------------------------------------------------------------
1876 -------------------------------------
1877 -- insert_row for:OKL_AE_TEMPLATES --
1878 -------------------------------------
1879 PROCEDURE insert_row(
1880 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1881 x_return_status OUT NOCOPY VARCHAR2,
1882 x_msg_count OUT NOCOPY NUMBER,
1883 x_msg_data OUT NOCOPY VARCHAR2,
1884 p_avl_rec IN avl_rec_type,
1885 x_avl_rec OUT NOCOPY avl_rec_type) IS
1886
1887 l_api_version CONSTANT NUMBER := 1;
1888 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_insert_row';
1889 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1890 l_avl_rec avl_rec_type := p_avl_rec;
1891 l_def_avl_rec avl_rec_type;
1892 -----------------------------------------
1893 -- Set_Attributes for:OKL_AE_TEMPLATES --
1894 -----------------------------------------
1895 FUNCTION Set_Attributes (
1896 p_avl_rec IN avl_rec_type,
1897 x_avl_rec OUT NOCOPY avl_rec_type
1898 ) RETURN VARCHAR2 IS
1899 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1900 BEGIN
1901 x_avl_rec := p_avl_rec;
1902 RETURN(l_return_status);
1903 END Set_Attributes;
1904 BEGIN
1905 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1906 p_init_msg_list,
1907 '_PVT',
1908 x_return_status);
1909 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1910 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1911 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1912 RAISE OKC_API.G_EXCEPTION_ERROR;
1913 END IF;
1914 --- Setting item attributes
1915 l_return_status := Set_Attributes(
1916 p_avl_rec, -- IN
1917 l_avl_rec); -- OUT
1918 --- If any errors happen abort API
1919 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1920 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1921 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1922 RAISE OKC_API.G_EXCEPTION_ERROR;
1923 END IF;
1924 /* Fixed Bug # 5707866 ssdeshpa start
1925 --Insert NULL Value for following Columns
1926 -- SET_OF_BOOKS_ID,POST_TO_GL,VERSION,ACCRUAL_YN
1927 --Since This Column are no longer Used */
1928 INSERT INTO OKL_AE_TEMPLATES(
1929 id,
1930 name,
1931 set_of_books_id,
1932 sty_id,
1933 try_id,
1934 aes_id,
1935 syt_code,
1936 fac_code,
1937 fma_id,
1938 advance_arrears,
1939 post_to_gl,
1940 version,
1941 start_date,
1942 object_version_number,
1943 memo_yn,
1944 prior_year_yn,
1945 description,
1946 factoring_synd_flag,
1947 end_date,
1948 accrual_yn,
1949 org_id,
1950 attribute_category,
1951 attribute1,
1952 attribute2,
1953 attribute3,
1954 attribute4,
1955 attribute5,
1956 attribute6,
1957 attribute7,
1958 attribute8,
1959 attribute9,
1960 attribute10,
1961 attribute11,
1962 attribute12,
1963 attribute13,
1964 attribute14,
1965 attribute15,
1966 created_by,
1967 creation_date,
1968 last_updated_by,
1969 last_update_date,
1970 last_update_login,
1971 -- Added by HKPATEL for securitization changes
1972 inv_code)
1973 VALUES (
1974 l_avl_rec.id,
1975 l_avl_rec.name,
1976 --l_avl_rec.set_of_books_id,
1977 null,
1978 l_avl_rec.sty_id,
1979 l_avl_rec.try_id,
1980 l_avl_rec.aes_id,
1981 l_avl_rec.syt_code,
1982 l_avl_rec.fac_code,
1983 l_avl_rec.fma_id,
1984 l_avl_rec.advance_arrears,
1985 --l_avl_rec.post_to_gl,
1986 null,
1987 --l_avl_rec.version,
1988 null,
1989 l_avl_rec.start_date,
1990 l_avl_rec.object_version_number,
1991 l_avl_rec.memo_yn,
1992 l_avl_rec.prior_year_yn,
1993 l_avl_rec.description,
1994 l_avl_rec.factoring_synd_flag,
1995 l_avl_rec.end_date,
1996 --l_avl_rec.accrual_yn,
1997 null,
1998 l_avl_rec.org_id,
1999 l_avl_rec.attribute_category,
2000 l_avl_rec.attribute1,
2001 l_avl_rec.attribute2,
2002 l_avl_rec.attribute3,
2003 l_avl_rec.attribute4,
2004 l_avl_rec.attribute5,
2005 l_avl_rec.attribute6,
2006 l_avl_rec.attribute7,
2007 l_avl_rec.attribute8,
2008 l_avl_rec.attribute9,
2009 l_avl_rec.attribute10,
2010 l_avl_rec.attribute11,
2011 l_avl_rec.attribute12,
2012 l_avl_rec.attribute13,
2013 l_avl_rec.attribute14,
2014 l_avl_rec.attribute15,
2015 l_avl_rec.created_by,
2016 l_avl_rec.creation_date,
2017 l_avl_rec.last_updated_by,
2018 l_avl_rec.last_update_date,
2019 l_avl_rec.last_update_login,
2020 -- Added by HKPATEL for securitization changes
2021 l_avl_rec.inv_code);
2022 -- Fixed Bug # 5707866 ssdeshpa End
2023 -- Set OUT values
2024 x_avl_rec := l_avl_rec;
2025 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2026 EXCEPTION
2027 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2028 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2029 (
2030 l_api_name,
2031 G_PKG_NAME,
2032 'OKC_API.G_RET_STS_ERROR',
2033 x_msg_count,
2034 x_msg_data,
2035 '_PVT'
2036 );
2037 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2038 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2039 (
2040 l_api_name,
2041 G_PKG_NAME,
2042 'OKC_API.G_RET_STS_UNEXP_ERROR',
2043 x_msg_count,
2044 x_msg_data,
2045 '_PVT'
2046 );
2047 WHEN OTHERS THEN
2048 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2049 (
2050 l_api_name,
2051 G_PKG_NAME,
2052 'OTHERS',
2053 x_msg_count,
2054 x_msg_data,
2055 '_PVT'
2056 );
2057 END insert_row;
2058 ---------------------------------------
2059 -- insert_row for:OKL_AE_TEMPLATES_V --
2060 ---------------------------------------
2061 PROCEDURE insert_row(
2062 p_api_version IN NUMBER,
2063 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2064 x_return_status OUT NOCOPY VARCHAR2,
2065 x_msg_count OUT NOCOPY NUMBER,
2066 x_msg_data OUT NOCOPY VARCHAR2,
2067 p_avlv_rec IN avlv_rec_type,
2068 x_avlv_rec OUT NOCOPY avlv_rec_type) IS
2069
2070 l_api_version CONSTANT NUMBER := 1;
2071 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2072 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2073 l_avlv_rec avlv_rec_type;
2074 l_def_avlv_rec avlv_rec_type;
2075 l_avl_rec avl_rec_type;
2076 lx_avl_rec avl_rec_type;
2077 -------------------------------
2078 -- FUNCTION fill_who_columns --
2079 -------------------------------
2080
2081 FUNCTION fill_who_columns (
2082 p_avlv_rec IN avlv_rec_type
2083 ) RETURN avlv_rec_type IS
2084 l_avlv_rec avlv_rec_type := p_avlv_rec;
2085 BEGIN
2086 l_avlv_rec.CREATION_DATE := SYSDATE;
2087 l_avlv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2088 l_avlv_rec.LAST_UPDATE_DATE := SYSDATE;
2089 l_avlv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2090 l_avlv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2091 RETURN(l_avlv_rec);
2092 END fill_who_columns;
2093 -------------------------------------------
2094 -- Set_Attributes for:OKL_AE_TEMPLATES_V --
2095 -------------------------------------------
2096 FUNCTION Set_Attributes (
2097 p_avlv_rec IN avlv_rec_type,
2098 x_avlv_rec OUT NOCOPY avlv_rec_type
2099 ) RETURN VARCHAR2 IS
2100 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2101 BEGIN
2102 x_avlv_rec := p_avlv_rec;
2103 x_avlv_rec.OBJECT_VERSION_NUMBER := 1;
2104 /*Fixed Bug # 5707866 ssdeshpa start
2105 --Removing Defaulting Logic for POST_TO_GL , SET_OF_BOOKS_ID , ACCRUAL_YN
2106 --;Since This Column are NULL */
2107 --x_avlv_rec.POST_TO_GL := 'Y'; -- this field is not used, so a default provided
2108 x_avlv_rec.ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID();
2109 --x_avlv_rec.set_of_books_id := OKL_ACCOUNTING_UTIL.get_Set_of_books_id;
2110
2111 x_avlv_rec.NAME := UPPER(x_avlv_rec.NAME);
2112 -- Santonyr 18th Jul, 2002.
2113 -- Added the code to set the default value for accrual_yn
2114 -- x_avlv_rec.accrual_yn := 'N';
2115 --Fixed Bug # 5707866 ssdeshpa end
2116 RETURN(l_return_status);
2117 END Set_Attributes;
2118 BEGIN
2119 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2120 G_PKG_NAME,
2121 p_init_msg_list,
2122 l_api_version,
2123 p_api_version,
2124 '_PVT',
2125 x_return_status);
2126
2127 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2128 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2129 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2130 RAISE OKC_API.G_EXCEPTION_ERROR;
2131 END IF;
2132
2133 l_avlv_rec := null_out_defaults(p_avlv_rec);
2134 -- Set primary key value
2135 l_avlv_rec.ID := get_seq_id;
2136 --- Setting item attributes
2137
2138
2139 l_return_status := Set_Attributes(
2140 l_avlv_rec, -- IN
2141 l_def_avlv_rec); -- OUT
2142 --- If any errors happen abort API
2143 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2144 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2145 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2146 RAISE OKC_API.G_EXCEPTION_ERROR;
2147 END IF;
2148 l_def_avlv_rec := fill_who_columns(l_def_avlv_rec);
2149 --- Validate all non-missing attributes (Item Level Validation)
2150 l_return_status := Validate_Attributes(l_def_avlv_rec);
2151 --- If any errors happen abort API
2152 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2153 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2154 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2155 RAISE OKC_API.G_EXCEPTION_ERROR;
2156 END IF;
2157 l_return_status := Validate_Record(l_def_avlv_rec);
2158 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2159 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2160 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2161 RAISE OKC_API.G_EXCEPTION_ERROR;
2162 END IF;
2163 --------------------------------------
2164 -- Move VIEW record to "Child" records
2165 --------------------------------------
2166 migrate(l_def_avlv_rec, l_avl_rec);
2167 --------------------------------------------
2168 -- Call the INSERT_ROW for each child record
2169 --------------------------------------------
2170 insert_row(
2171 p_init_msg_list,
2172 x_return_status,
2173 x_msg_count,
2174 x_msg_data,
2175 l_avl_rec,
2176 lx_avl_rec
2177 );
2178 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2179 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2180 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2181 RAISE OKC_API.G_EXCEPTION_ERROR;
2182 END IF;
2183 migrate(lx_avl_rec, l_def_avlv_rec);
2184 -- Set OUT values
2185 x_avlv_rec := l_def_avlv_rec;
2186 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2187 EXCEPTION
2188 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2189 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2190 (
2191 l_api_name,
2192 G_PKG_NAME,
2193 'OKC_API.G_RET_STS_ERROR',
2194 x_msg_count,
2195 x_msg_data,
2196 '_PVT'
2197 );
2198 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2199 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2200 (
2201 l_api_name,
2202 G_PKG_NAME,
2203 'OKC_API.G_RET_STS_UNEXP_ERROR',
2204 x_msg_count,
2205 x_msg_data,
2206 '_PVT'
2207 );
2208 WHEN OTHERS THEN
2209 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2210 (
2211 l_api_name,
2212 G_PKG_NAME,
2213 'OTHERS',
2214 x_msg_count,
2215 x_msg_data,
2216 '_PVT'
2217 );
2218 END insert_row;
2219 ----------------------------------------
2220 -- PL/SQL TBL insert_row for:AVLV_TBL --
2221 ----------------------------------------
2222 PROCEDURE insert_row(
2223 p_api_version IN NUMBER,
2224 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2225 x_return_status OUT NOCOPY VARCHAR2,
2226 x_msg_count OUT NOCOPY NUMBER,
2227 x_msg_data OUT NOCOPY VARCHAR2,
2228 p_avlv_tbl IN avlv_tbl_type,
2229 x_avlv_tbl OUT NOCOPY avlv_tbl_type) IS
2230
2231 l_api_version CONSTANT NUMBER := 1;
2232 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2233 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2234 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2235 i NUMBER := 0;
2236 BEGIN
2237 OKC_API.init_msg_list(p_init_msg_list);
2238 -- Make sure PL/SQL table has records in it before passing
2239 IF (p_avlv_tbl.COUNT > 0) THEN
2240 i := p_avlv_tbl.FIRST;
2241 LOOP
2242 insert_row (
2243 p_api_version => p_api_version,
2244 p_init_msg_list => OKC_API.G_FALSE,
2245 x_return_status => x_return_status,
2246 x_msg_count => x_msg_count,
2247 x_msg_data => x_msg_data,
2248 p_avlv_rec => p_avlv_tbl(i),
2249 x_avlv_rec => x_avlv_tbl(i));
2250 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2251 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2252 l_overall_status := x_return_status;
2253 END IF;
2254 END IF;
2255 EXIT WHEN (i = p_avlv_tbl.LAST);
2256 i := p_avlv_tbl.NEXT(i);
2257 END LOOP;
2258 x_return_status := l_overall_status;
2259 END IF;
2260 EXCEPTION
2261 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2262 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2263 (
2264 l_api_name,
2265 G_PKG_NAME,
2266 'OKC_API.G_RET_STS_ERROR',
2267 x_msg_count,
2268 x_msg_data,
2269 '_PVT'
2270 );
2271 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2272 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2273 (
2274 l_api_name,
2275 G_PKG_NAME,
2276 'OKC_API.G_RET_STS_UNEXP_ERROR',
2277 x_msg_count,
2278 x_msg_data,
2279 '_PVT'
2280 );
2281 WHEN OTHERS THEN
2282 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2283 (
2284 l_api_name,
2285 G_PKG_NAME,
2286 'OTHERS',
2287 x_msg_count,
2288 x_msg_data,
2289 '_PVT'
2290 );
2291 END insert_row;
2292
2293 ---------------------------------------------------------------------------
2294 -- PROCEDURE lock_row
2295 ---------------------------------------------------------------------------
2296 -----------------------------------
2297 -- lock_row for:OKL_AE_TEMPLATES --
2298 -----------------------------------
2299 PROCEDURE lock_row(
2300 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2301 x_return_status OUT NOCOPY VARCHAR2,
2302 x_msg_count OUT NOCOPY NUMBER,
2303 x_msg_data OUT NOCOPY VARCHAR2,
2304 p_avl_rec IN avl_rec_type) IS
2305
2306 E_Resource_Busy EXCEPTION;
2307 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2308 CURSOR lock_csr (p_avl_rec IN avl_rec_type) IS
2309 SELECT OBJECT_VERSION_NUMBER
2310 FROM OKL_AE_TEMPLATES
2311 WHERE ID = p_avl_rec.id
2312 AND OBJECT_VERSION_NUMBER = p_avl_rec.object_version_number
2313 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2314
2315 CURSOR lchk_csr (p_avl_rec IN avl_rec_type) IS
2316 SELECT OBJECT_VERSION_NUMBER
2317 FROM OKL_AE_TEMPLATES
2318 WHERE ID = p_avl_rec.id;
2319 l_api_version CONSTANT NUMBER := 1;
2320 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_lock_row';
2321 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2322 l_object_version_number OKL_AE_TEMPLATES.OBJECT_VERSION_NUMBER%TYPE;
2323 lc_object_version_number OKL_AE_TEMPLATES.OBJECT_VERSION_NUMBER%TYPE;
2324 l_row_notfound BOOLEAN := FALSE;
2325 lc_row_notfound BOOLEAN := FALSE;
2326 BEGIN
2327 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2328 p_init_msg_list,
2329 '_PVT',
2330 x_return_status);
2331 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2332 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2333 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2334 RAISE OKC_API.G_EXCEPTION_ERROR;
2335 END IF;
2336 BEGIN
2337 OPEN lock_csr(p_avl_rec);
2338 FETCH lock_csr INTO l_object_version_number;
2339 l_row_notfound := lock_csr%NOTFOUND;
2340 CLOSE lock_csr;
2341 EXCEPTION
2342 WHEN E_Resource_Busy THEN
2343 IF (lock_csr%ISOPEN) THEN
2344 CLOSE lock_csr;
2345 END IF;
2346 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2347 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2348 END;
2349
2350 IF ( l_row_notfound ) THEN
2351 OPEN lchk_csr(p_avl_rec);
2352 FETCH lchk_csr INTO lc_object_version_number;
2353 lc_row_notfound := lchk_csr%NOTFOUND;
2354 CLOSE lchk_csr;
2355 END IF;
2356 IF (lc_row_notfound) THEN
2357 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2358 RAISE OKC_API.G_EXCEPTION_ERROR;
2359 ELSIF lc_object_version_number > p_avl_rec.object_version_number THEN
2360 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2361 RAISE OKC_API.G_EXCEPTION_ERROR;
2362 ELSIF lc_object_version_number <> p_avl_rec.object_version_number THEN
2363 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2364 RAISE OKC_API.G_EXCEPTION_ERROR;
2365 ELSIF lc_object_version_number = -1 THEN
2366 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2367 RAISE OKC_API.G_EXCEPTION_ERROR;
2368 END IF;
2369 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2370 EXCEPTION
2371 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2372 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2373 (
2374 l_api_name,
2375 G_PKG_NAME,
2376 'OKC_API.G_RET_STS_ERROR',
2377 x_msg_count,
2378 x_msg_data,
2379 '_PVT'
2380 );
2381 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2382 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2383 (
2384 l_api_name,
2385 G_PKG_NAME,
2386 'OKC_API.G_RET_STS_UNEXP_ERROR',
2387 x_msg_count,
2388 x_msg_data,
2389 '_PVT'
2390 );
2391 WHEN OTHERS THEN
2392 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2393 (
2394 l_api_name,
2395 G_PKG_NAME,
2396 'OTHERS',
2397 x_msg_count,
2398 x_msg_data,
2399 '_PVT'
2400 );
2401 END lock_row;
2402 -------------------------------------
2403 -- lock_row for:OKL_AE_TEMPLATES_V --
2404 -------------------------------------
2405 PROCEDURE lock_row(
2406 p_api_version IN NUMBER,
2407 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2408 x_return_status OUT NOCOPY VARCHAR2,
2409 x_msg_count OUT NOCOPY NUMBER,
2410 x_msg_data OUT NOCOPY VARCHAR2,
2411 p_avlv_rec IN avlv_rec_type) IS
2412
2413 l_api_version CONSTANT NUMBER := 1;
2414 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2415 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2416 l_avl_rec avl_rec_type;
2417 BEGIN
2418 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2419 G_PKG_NAME,
2420 p_init_msg_list,
2421 l_api_version,
2422 p_api_version,
2423 '_PVT',
2424 x_return_status);
2425 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2426 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2427 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2428 RAISE OKC_API.G_EXCEPTION_ERROR;
2429 END IF;
2430 --------------------------------------
2431 -- Move VIEW record to "Child" records
2432 --------------------------------------
2433 migrate(p_avlv_rec, l_avl_rec);
2434 --------------------------------------------
2435 -- Call the LOCK_ROW for each child record
2436 --------------------------------------------
2437 lock_row(
2438 p_init_msg_list,
2439 x_return_status,
2440 x_msg_count,
2441 x_msg_data,
2442 l_avl_rec
2443 );
2444 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2445 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2446 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2447 RAISE OKC_API.G_EXCEPTION_ERROR;
2448 END IF;
2449 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2450 EXCEPTION
2451 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2452 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2453 (
2454 l_api_name,
2455 G_PKG_NAME,
2456 'OKC_API.G_RET_STS_ERROR',
2457 x_msg_count,
2458 x_msg_data,
2459 '_PVT'
2460 );
2461 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2462 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2463 (
2464 l_api_name,
2465 G_PKG_NAME,
2466 'OKC_API.G_RET_STS_UNEXP_ERROR',
2467 x_msg_count,
2468 x_msg_data,
2469 '_PVT'
2470 );
2471 WHEN OTHERS THEN
2472 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2473 (
2474 l_api_name,
2475 G_PKG_NAME,
2476 'OTHERS',
2477 x_msg_count,
2478 x_msg_data,
2479 '_PVT'
2480 );
2481 END lock_row;
2482 --------------------------------------
2483 -- PL/SQL TBL lock_row for:AVLV_TBL --
2484 --------------------------------------
2485 PROCEDURE lock_row(
2486 p_api_version IN NUMBER,
2487 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2488 x_return_status OUT NOCOPY VARCHAR2,
2489 x_msg_count OUT NOCOPY NUMBER,
2490 x_msg_data OUT NOCOPY VARCHAR2,
2491 p_avlv_tbl IN avlv_tbl_type) IS
2492
2493 l_api_version CONSTANT NUMBER := 1;
2494 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2495 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2496 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2497 i NUMBER := 0;
2498 BEGIN
2499 OKC_API.init_msg_list(p_init_msg_list);
2500 -- Make sure PL/SQL table has records in it before passing
2501 IF (p_avlv_tbl.COUNT > 0) THEN
2502 i := p_avlv_tbl.FIRST;
2503 LOOP
2504 lock_row (
2505 p_api_version => p_api_version,
2506 p_init_msg_list => OKC_API.G_FALSE,
2507 x_return_status => x_return_status,
2508 x_msg_count => x_msg_count,
2509 x_msg_data => x_msg_data,
2510 p_avlv_rec => p_avlv_tbl(i));
2511 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2512 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2513 l_overall_status := x_return_status;
2514 END IF;
2515 END IF;
2516 EXIT WHEN (i = p_avlv_tbl.LAST);
2517 i := p_avlv_tbl.NEXT(i);
2518 END LOOP;
2519 x_return_status := l_overall_status;
2520 END IF;
2521 EXCEPTION
2522 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2523 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2524 (
2525 l_api_name,
2526 G_PKG_NAME,
2527 'OKC_API.G_RET_STS_ERROR',
2528 x_msg_count,
2529 x_msg_data,
2530 '_PVT'
2531 );
2532 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2533 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2534 (
2535 l_api_name,
2536 G_PKG_NAME,
2537 'OKC_API.G_RET_STS_UNEXP_ERROR',
2538 x_msg_count,
2539 x_msg_data,
2540 '_PVT'
2541 );
2542 WHEN OTHERS THEN
2543 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2544 (
2545 l_api_name,
2546 G_PKG_NAME,
2547 'OTHERS',
2548 x_msg_count,
2549 x_msg_data,
2550 '_PVT'
2551 );
2552 END lock_row;
2553
2554 ---------------------------------------------------------------------------
2555 -- PROCEDURE update_row
2556 ---------------------------------------------------------------------------
2557 -------------------------------------
2558 -- update_row for:OKL_AE_TEMPLATES --
2559 -------------------------------------
2560 PROCEDURE update_row(
2561 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2562 x_return_status OUT NOCOPY VARCHAR2,
2563 x_msg_count OUT NOCOPY NUMBER,
2564 x_msg_data OUT NOCOPY VARCHAR2,
2565 p_avl_rec IN avl_rec_type,
2566 x_avl_rec OUT NOCOPY avl_rec_type) IS
2567
2568 l_api_version CONSTANT NUMBER := 1;
2569 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_update_row';
2570 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2571 l_avl_rec avl_rec_type := p_avl_rec;
2572 l_def_avl_rec avl_rec_type;
2573 l_row_notfound BOOLEAN := TRUE;
2574 ----------------------------------
2575 -- FUNCTION populate_new_record --
2576 ----------------------------------
2577 FUNCTION populate_new_record (
2578 p_avl_rec IN avl_rec_type,
2579 x_avl_rec OUT NOCOPY avl_rec_type
2580 ) RETURN VARCHAR2 IS
2581 l_avl_rec avl_rec_type;
2582 l_row_notfound BOOLEAN := TRUE;
2583 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2584 BEGIN
2585 x_avl_rec := p_avl_rec;
2586 -- Get current database values
2587 l_avl_rec := get_rec(p_avl_rec, l_row_notfound);
2588 IF (l_row_notfound) THEN
2589 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2590 END IF;
2591 IF (x_avl_rec.id = OKC_API.G_MISS_NUM)
2592 THEN
2593 x_avl_rec.id := l_avl_rec.id;
2594 END IF;
2595 IF (x_avl_rec.name = OKC_API.G_MISS_CHAR)
2596 THEN
2597 x_avl_rec.name := l_avl_rec.name;
2598 END IF;
2599 IF (x_avl_rec.set_of_books_id = OKC_API.G_MISS_NUM)
2600 THEN
2601 x_avl_rec.set_of_books_id := l_avl_rec.set_of_books_id;
2602 END IF;
2603 IF (x_avl_rec.sty_id = OKC_API.G_MISS_NUM)
2604 THEN
2605 x_avl_rec.sty_id := l_avl_rec.sty_id;
2606 END IF;
2607 IF (x_avl_rec.try_id = OKC_API.G_MISS_NUM)
2608 THEN
2609 x_avl_rec.try_id := l_avl_rec.try_id;
2610 END IF;
2611 IF (x_avl_rec.aes_id = OKC_API.G_MISS_NUM)
2612 THEN
2613 x_avl_rec.aes_id := l_avl_rec.aes_id;
2614 END IF;
2615 IF (x_avl_rec.syt_code = OKC_API.G_MISS_CHAR)
2616 THEN
2617 x_avl_rec.syt_code := l_avl_rec.syt_code;
2618 END IF;
2619 IF (x_avl_rec.fac_code = OKC_API.G_MISS_CHAR)
2620 THEN
2621 x_avl_rec.fac_code := l_avl_rec.fac_code;
2622 END IF;
2623 IF (x_avl_rec.fma_id = OKC_API.G_MISS_NUM)
2624 THEN
2625 x_avl_rec.fma_id := l_avl_rec.fma_id;
2626 END IF;
2627 IF (x_avl_rec.advance_arrears = OKC_API.G_MISS_CHAR)
2628 THEN
2629 x_avl_rec.advance_arrears := l_avl_rec.advance_arrears;
2630 END IF;
2631 IF (x_avl_rec.post_to_gl = OKC_API.G_MISS_CHAR)
2632 THEN
2633 x_avl_rec.post_to_gl := l_avl_rec.post_to_gl;
2634 END IF;
2635 IF (x_avl_rec.version = OKC_API.G_MISS_CHAR)
2636 THEN
2637 x_avl_rec.version := l_avl_rec.version;
2638 END IF;
2639 IF (x_avl_rec.start_date = OKC_API.G_MISS_DATE)
2640 THEN
2641 x_avl_rec.start_date := l_avl_rec.start_date;
2642 END IF;
2643 IF (x_avl_rec.object_version_number = OKC_API.G_MISS_NUM)
2644 THEN
2645 x_avl_rec.object_version_number := l_avl_rec.object_version_number;
2646 END IF;
2647 IF (x_avl_rec.memo_yn = OKC_API.G_MISS_CHAR)
2648 THEN
2649 x_avl_rec.memo_yn := l_avl_rec.memo_yn;
2650 END IF;
2651 IF (x_avl_rec.prior_year_yn = OKC_API.G_MISS_CHAR)
2652 THEN
2653 x_avl_rec.prior_year_yn := l_avl_rec.prior_year_yn;
2654 END IF;
2655 IF (x_avl_rec.description = OKC_API.G_MISS_CHAR)
2656 THEN
2657 x_avl_rec.description := l_avl_rec.description;
2658 END IF;
2659 IF (x_avl_rec.factoring_synd_flag = OKC_API.G_MISS_CHAR)
2660 THEN
2661 x_avl_rec.factoring_synd_flag := l_avl_rec.factoring_synd_flag;
2662 END IF;
2663 IF (x_avl_rec.end_date = OKC_API.G_MISS_DATE)
2664 THEN
2665 x_avl_rec.end_date := l_avl_rec.end_date;
2666 END IF;
2667 IF (x_avl_rec.accrual_yn = OKC_API.G_MISS_CHAR)
2668 THEN
2669 x_avl_rec.accrual_yn := l_avl_rec.accrual_yn;
2670 END IF;
2671 IF (x_avl_rec.org_id = OKC_API.G_MISS_NUM)
2672 THEN
2673 x_avl_rec.org_id := l_avl_rec.org_id;
2674 END IF;
2675 IF (x_avl_rec.attribute_category = OKC_API.G_MISS_CHAR)
2676 THEN
2677 x_avl_rec.attribute_category := l_avl_rec.attribute_category;
2678 END IF;
2679 IF (x_avl_rec.attribute1 = OKC_API.G_MISS_CHAR)
2680 THEN
2681 x_avl_rec.attribute1 := l_avl_rec.attribute1;
2682 END IF;
2683 IF (x_avl_rec.attribute2 = OKC_API.G_MISS_CHAR)
2684 THEN
2685 x_avl_rec.attribute2 := l_avl_rec.attribute2;
2686 END IF;
2687 IF (x_avl_rec.attribute3 = OKC_API.G_MISS_CHAR)
2688 THEN
2689 x_avl_rec.attribute3 := l_avl_rec.attribute3;
2690 END IF;
2691 IF (x_avl_rec.attribute4 = OKC_API.G_MISS_CHAR)
2692 THEN
2693 x_avl_rec.attribute4 := l_avl_rec.attribute4;
2694 END IF;
2695 IF (x_avl_rec.attribute5 = OKC_API.G_MISS_CHAR)
2696 THEN
2697 x_avl_rec.attribute5 := l_avl_rec.attribute5;
2698 END IF;
2699 IF (x_avl_rec.attribute6 = OKC_API.G_MISS_CHAR)
2700 THEN
2701 x_avl_rec.attribute6 := l_avl_rec.attribute6;
2702 END IF;
2703 IF (x_avl_rec.attribute7 = OKC_API.G_MISS_CHAR)
2704 THEN
2705 x_avl_rec.attribute7 := l_avl_rec.attribute7;
2706 END IF;
2707 IF (x_avl_rec.attribute8 = OKC_API.G_MISS_CHAR)
2708 THEN
2709 x_avl_rec.attribute8 := l_avl_rec.attribute8;
2710 END IF;
2711 IF (x_avl_rec.attribute9 = OKC_API.G_MISS_CHAR)
2712 THEN
2713 x_avl_rec.attribute9 := l_avl_rec.attribute9;
2714 END IF;
2715 IF (x_avl_rec.attribute10 = OKC_API.G_MISS_CHAR)
2716 THEN
2717 x_avl_rec.attribute10 := l_avl_rec.attribute10;
2718 END IF;
2719 IF (x_avl_rec.attribute11 = OKC_API.G_MISS_CHAR)
2720 THEN
2721 x_avl_rec.attribute11 := l_avl_rec.attribute11;
2722 END IF;
2723 IF (x_avl_rec.attribute12 = OKC_API.G_MISS_CHAR)
2724 THEN
2725 x_avl_rec.attribute12 := l_avl_rec.attribute12;
2726 END IF;
2727 IF (x_avl_rec.attribute13 = OKC_API.G_MISS_CHAR)
2728 THEN
2729 x_avl_rec.attribute13 := l_avl_rec.attribute13;
2730 END IF;
2731 IF (x_avl_rec.attribute14 = OKC_API.G_MISS_CHAR)
2732 THEN
2733 x_avl_rec.attribute14 := l_avl_rec.attribute14;
2734 END IF;
2735 IF (x_avl_rec.attribute15 = OKC_API.G_MISS_CHAR)
2736 THEN
2737 x_avl_rec.attribute15 := l_avl_rec.attribute15;
2738 END IF;
2739 IF (x_avl_rec.created_by = OKC_API.G_MISS_NUM)
2740 THEN
2741 x_avl_rec.created_by := l_avl_rec.created_by;
2742 END IF;
2743 IF (x_avl_rec.creation_date = OKC_API.G_MISS_DATE)
2744 THEN
2745 x_avl_rec.creation_date := l_avl_rec.creation_date;
2746 END IF;
2747 IF (x_avl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2748 THEN
2749 x_avl_rec.last_updated_by := l_avl_rec.last_updated_by;
2750 END IF;
2751 IF (x_avl_rec.last_update_date = OKC_API.G_MISS_DATE)
2752 THEN
2753 x_avl_rec.last_update_date := l_avl_rec.last_update_date;
2754 END IF;
2755 IF (x_avl_rec.last_update_login = OKC_API.G_MISS_NUM)
2756 THEN
2757 x_avl_rec.last_update_login := l_avl_rec.last_update_login;
2758 END IF;
2759 -- Added by HKPATEL for securitization changes
2760 IF (x_avl_rec.inv_code = OKC_API.G_MISS_CHAR)
2761 THEN
2762 x_avl_rec.inv_code := l_avl_rec.inv_code;
2763 END IF;
2764 RETURN(l_return_status);
2765 END populate_new_record;
2766 -----------------------------------------
2767 -- Set_Attributes for:OKL_AE_TEMPLATES --
2768 -----------------------------------------
2769 FUNCTION Set_Attributes (
2770 p_avl_rec IN avl_rec_type,
2771 x_avl_rec OUT NOCOPY avl_rec_type
2772 ) RETURN VARCHAR2 IS
2773 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2774 BEGIN
2775 x_avl_rec := p_avl_rec;
2776 RETURN(l_return_status);
2777 END Set_Attributes;
2778 BEGIN
2779 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2780 p_init_msg_list,
2781 '_PVT',
2782 x_return_status);
2783 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2784 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2785 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2786 RAISE OKC_API.G_EXCEPTION_ERROR;
2787 END IF;
2788 --- Setting item attributes
2789 l_return_status := Set_Attributes(
2790 p_avl_rec, -- IN
2791 l_avl_rec); -- OUT
2792 --- If any errors happen abort API
2793 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2794 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2795 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2796 RAISE OKC_API.G_EXCEPTION_ERROR;
2797 END IF;
2798 l_return_status := populate_new_record(l_avl_rec, l_def_avl_rec);
2799 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2800 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2801 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2802 RAISE OKC_API.G_EXCEPTION_ERROR;
2803 END IF;
2804 UPDATE OKL_AE_TEMPLATES
2805 SET NAME = l_def_avl_rec.name,
2806 SET_OF_BOOKS_ID = l_def_avl_rec.set_of_books_id,
2807 STY_ID = l_def_avl_rec.sty_id,
2808 TRY_ID = l_def_avl_rec.try_id,
2809 AES_ID = l_def_avl_rec.aes_id,
2810 SYT_CODE = l_def_avl_rec.syt_code,
2811 FAC_CODE = l_def_avl_rec.fac_code,
2812 FMA_ID = l_def_avl_rec.fma_id,
2813 ADVANCE_ARREARS = l_def_avl_rec.advance_arrears,
2814 POST_TO_GL = l_def_avl_rec.post_to_gl,
2815 VERSION = l_def_avl_rec.version,
2816 START_DATE = l_def_avl_rec.start_date,
2817 OBJECT_VERSION_NUMBER = l_def_avl_rec.object_version_number,
2818 MEMO_YN = l_def_avl_rec.memo_yn,
2819 PRIOR_YEAR_YN = l_def_avl_rec.prior_year_yn,
2820 DESCRIPTION = l_def_avl_rec.description,
2821 FACTORING_SYND_FLAG = l_def_avl_rec.factoring_synd_flag,
2822 END_DATE = l_def_avl_rec.end_date,
2823 ACCRUAL_YN = l_def_avl_rec.accrual_yn ,
2824 ORG_ID = l_def_avl_rec.org_id,
2825 ATTRIBUTE_CATEGORY = l_def_avl_rec.attribute_category,
2826 ATTRIBUTE1 = l_def_avl_rec.attribute1,
2827 ATTRIBUTE2 = l_def_avl_rec.attribute2,
2828 ATTRIBUTE3 = l_def_avl_rec.attribute3,
2829 ATTRIBUTE4 = l_def_avl_rec.attribute4,
2830 ATTRIBUTE5 = l_def_avl_rec.attribute5,
2831 ATTRIBUTE6 = l_def_avl_rec.attribute6,
2832 ATTRIBUTE7 = l_def_avl_rec.attribute7,
2833 ATTRIBUTE8 = l_def_avl_rec.attribute8,
2834 ATTRIBUTE9 = l_def_avl_rec.attribute9,
2835 ATTRIBUTE10 = l_def_avl_rec.attribute10,
2836 ATTRIBUTE11 = l_def_avl_rec.attribute11,
2837 ATTRIBUTE12 = l_def_avl_rec.attribute12,
2838 ATTRIBUTE13 = l_def_avl_rec.attribute13,
2839 ATTRIBUTE14 = l_def_avl_rec.attribute14,
2840 ATTRIBUTE15 = l_def_avl_rec.attribute15,
2841 CREATED_BY = l_def_avl_rec.created_by,
2842 CREATION_DATE = l_def_avl_rec.creation_date,
2843 LAST_UPDATED_BY = l_def_avl_rec.last_updated_by,
2844 LAST_UPDATE_DATE = l_def_avl_rec.last_update_date,
2845 LAST_UPDATE_LOGIN = l_def_avl_rec.last_update_login,
2846 -- Added by HKPATEL for securitization changes
2847 INV_CODE = l_def_avl_rec.inv_code
2848 WHERE ID = l_def_avl_rec.id;
2849
2850 x_avl_rec := l_def_avl_rec;
2851 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2852 EXCEPTION
2853 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2854 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2855 (
2856 l_api_name,
2857 G_PKG_NAME,
2858 'OKC_API.G_RET_STS_ERROR',
2859 x_msg_count,
2860 x_msg_data,
2861 '_PVT'
2862 );
2863 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2864 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2865 (
2866 l_api_name,
2867 G_PKG_NAME,
2868 'OKC_API.G_RET_STS_UNEXP_ERROR',
2869 x_msg_count,
2870 x_msg_data,
2871 '_PVT'
2872 );
2873 WHEN OTHERS THEN
2874 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2875 (
2876 l_api_name,
2877 G_PKG_NAME,
2878 'OTHERS',
2879 x_msg_count,
2880 x_msg_data,
2881 '_PVT'
2882 );
2883 END update_row;
2884 ---------------------------------------
2885 -- update_row for:OKL_AE_TEMPLATES_V --
2886 ---------------------------------------
2887 PROCEDURE update_row(
2888 p_api_version IN NUMBER,
2889 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2890 x_return_status OUT NOCOPY VARCHAR2,
2891 x_msg_count OUT NOCOPY NUMBER,
2892 x_msg_data OUT NOCOPY VARCHAR2,
2893 p_avlv_rec IN avlv_rec_type,
2894 x_avlv_rec OUT NOCOPY avlv_rec_type) IS
2895
2896 l_api_version CONSTANT NUMBER := 1;
2897 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2898 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2899 l_avlv_rec avlv_rec_type := p_avlv_rec;
2900 l_def_avlv_rec avlv_rec_type;
2901 l_avl_rec avl_rec_type;
2902 lx_avl_rec avl_rec_type;
2903 -------------------------------
2904 -- FUNCTION fill_who_columns --
2905 -------------------------------
2906 FUNCTION fill_who_columns (
2907 p_avlv_rec IN avlv_rec_type
2908 ) RETURN avlv_rec_type IS
2909 l_avlv_rec avlv_rec_type := p_avlv_rec;
2910 BEGIN
2911 l_avlv_rec.LAST_UPDATE_DATE := SYSDATE;
2912 l_avlv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2913 l_avlv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2914 RETURN(l_avlv_rec);
2915 END fill_who_columns;
2916 ----------------------------------
2917 -- FUNCTION populate_new_record --
2918 ----------------------------------
2919 FUNCTION populate_new_record (
2920 p_avlv_rec IN avlv_rec_type,
2921 x_avlv_rec OUT NOCOPY avlv_rec_type
2922 ) RETURN VARCHAR2 IS
2923 l_avlv_rec avlv_rec_type;
2924 l_row_notfound BOOLEAN := TRUE;
2925 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2926 BEGIN
2927 x_avlv_rec := p_avlv_rec;
2928 -- Get current database values
2929 l_avlv_rec := get_rec(p_avlv_rec, l_row_notfound);
2930 IF (l_row_notfound) THEN
2931 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2932 END IF;
2933 IF (x_avlv_rec.id = OKC_API.G_MISS_NUM)
2934 THEN
2935 x_avlv_rec.id := l_avlv_rec.id;
2936 END IF;
2937 IF (x_avlv_rec.object_version_number = OKC_API.G_MISS_NUM)
2938 THEN
2939 x_avlv_rec.object_version_number := l_avlv_rec.object_version_number;
2940 END IF;
2941 IF (x_avlv_rec.try_id = OKC_API.G_MISS_NUM)
2942 THEN
2943 x_avlv_rec.try_id := l_avlv_rec.try_id;
2944 END IF;
2945 IF (x_avlv_rec.aes_id = OKC_API.G_MISS_NUM)
2946 THEN
2947 x_avlv_rec.aes_id := l_avlv_rec.aes_id;
2948 END IF;
2949 IF (x_avlv_rec.sty_id = OKC_API.G_MISS_NUM)
2950 THEN
2951 x_avlv_rec.sty_id := l_avlv_rec.sty_id;
2952 END IF;
2953 IF (x_avlv_rec.fma_id = OKC_API.G_MISS_NUM)
2954 THEN
2955 x_avlv_rec.fma_id := l_avlv_rec.fma_id;
2956 END IF;
2957 IF (x_avlv_rec.set_of_books_id = OKC_API.G_MISS_NUM)
2958 THEN
2959 x_avlv_rec.set_of_books_id := l_avlv_rec.set_of_books_id;
2960 END IF;
2961 IF (x_avlv_rec.fac_code = OKC_API.G_MISS_CHAR)
2962 THEN
2963 x_avlv_rec.fac_code := l_avlv_rec.fac_code;
2964 END IF;
2965 IF (x_avlv_rec.syt_code = OKC_API.G_MISS_CHAR)
2966 THEN
2967 x_avlv_rec.syt_code := l_avlv_rec.syt_code;
2968 END IF;
2969 IF (x_avlv_rec.post_to_gl = OKC_API.G_MISS_CHAR)
2970 THEN
2971 x_avlv_rec.post_to_gl := l_avlv_rec.post_to_gl;
2972 END IF;
2973 IF (x_avlv_rec.advance_arrears = OKC_API.G_MISS_CHAR)
2974 THEN
2975 x_avlv_rec.advance_arrears := l_avlv_rec.advance_arrears;
2976 END IF;
2977 IF (x_avlv_rec.memo_yn = OKC_API.G_MISS_CHAR)
2978 THEN
2979 x_avlv_rec.memo_yn := l_avlv_rec.memo_yn;
2980 END IF;
2981 IF (x_avlv_rec.prior_year_yn = OKC_API.G_MISS_CHAR)
2982 THEN
2983 x_avlv_rec.prior_year_yn := l_avlv_rec.prior_year_yn;
2984 END IF;
2985 IF (x_avlv_rec.name = OKC_API.G_MISS_CHAR)
2986 THEN
2987 x_avlv_rec.name := l_avlv_rec.name;
2988 END IF;
2989 IF (x_avlv_rec.description = OKC_API.G_MISS_CHAR)
2990 THEN
2991 x_avlv_rec.description := l_avlv_rec.description;
2992 END IF;
2993 IF (x_avlv_rec.version = OKC_API.G_MISS_CHAR)
2994 THEN
2995 x_avlv_rec.version := l_avlv_rec.version;
2996 END IF;
2997 IF (x_avlv_rec.factoring_synd_flag = OKC_API.G_MISS_CHAR)
2998 THEN
2999 x_avlv_rec.factoring_synd_flag := l_avlv_rec.factoring_synd_flag;
3000 END IF;
3001 IF (x_avlv_rec.start_date = OKC_API.G_MISS_DATE)
3002 THEN
3003 x_avlv_rec.start_date := l_avlv_rec.start_date;
3004 END IF;
3005 IF (x_avlv_rec.end_date = OKC_API.G_MISS_DATE)
3006 THEN
3007 x_avlv_rec.end_date := l_avlv_rec.end_date;
3008 END IF;
3009 IF (x_avlv_rec.accrual_yn = OKC_API.G_MISS_CHAR)
3010 THEN
3011 x_avlv_rec.accrual_yn := l_avlv_rec.accrual_yn;
3012 END IF;
3013 IF (x_avlv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3014 THEN
3015 x_avlv_rec.attribute_category := l_avlv_rec.attribute_category;
3016 END IF;
3017 IF (x_avlv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3018 THEN
3019 x_avlv_rec.attribute1 := l_avlv_rec.attribute1;
3020 END IF;
3021 IF (x_avlv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3022 THEN
3023 x_avlv_rec.attribute2 := l_avlv_rec.attribute2;
3024 END IF;
3025 IF (x_avlv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3026 THEN
3027 x_avlv_rec.attribute3 := l_avlv_rec.attribute3;
3028 END IF;
3029 IF (x_avlv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3030 THEN
3031 x_avlv_rec.attribute4 := l_avlv_rec.attribute4;
3032 END IF;
3033 IF (x_avlv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3034 THEN
3035 x_avlv_rec.attribute5 := l_avlv_rec.attribute5;
3036 END IF;
3037 IF (x_avlv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3038 THEN
3039 x_avlv_rec.attribute6 := l_avlv_rec.attribute6;
3040 END IF;
3041 IF (x_avlv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3042 THEN
3043 x_avlv_rec.attribute7 := l_avlv_rec.attribute7;
3044 END IF;
3045 IF (x_avlv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3046 THEN
3047 x_avlv_rec.attribute8 := l_avlv_rec.attribute8;
3048 END IF;
3049 IF (x_avlv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3050 THEN
3051 x_avlv_rec.attribute9 := l_avlv_rec.attribute9;
3052 END IF;
3053 IF (x_avlv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3054 THEN
3055 x_avlv_rec.attribute10 := l_avlv_rec.attribute10;
3056 END IF;
3057 IF (x_avlv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3058 THEN
3059 x_avlv_rec.attribute11 := l_avlv_rec.attribute11;
3060 END IF;
3061 IF (x_avlv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3062 THEN
3063 x_avlv_rec.attribute12 := l_avlv_rec.attribute12;
3064 END IF;
3065 IF (x_avlv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3066 THEN
3067 x_avlv_rec.attribute13 := l_avlv_rec.attribute13;
3068 END IF;
3069 IF (x_avlv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3070 THEN
3071 x_avlv_rec.attribute14 := l_avlv_rec.attribute14;
3072 END IF;
3073 IF (x_avlv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3074 THEN
3075 x_avlv_rec.attribute15 := l_avlv_rec.attribute15;
3076 END IF;
3077 IF (x_avlv_rec.org_id = OKC_API.G_MISS_NUM)
3078 THEN
3079 x_avlv_rec.org_id := l_avlv_rec.org_id;
3080 END IF;
3081 IF (x_avlv_rec.created_by = OKC_API.G_MISS_NUM)
3082 THEN
3083 x_avlv_rec.created_by := l_avlv_rec.created_by;
3084 END IF;
3085 IF (x_avlv_rec.creation_date = OKC_API.G_MISS_DATE)
3086 THEN
3087 x_avlv_rec.creation_date := l_avlv_rec.creation_date;
3088 END IF;
3089 IF (x_avlv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3090 THEN
3091 x_avlv_rec.last_updated_by := l_avlv_rec.last_updated_by;
3092 END IF;
3093 IF (x_avlv_rec.last_update_date = OKC_API.G_MISS_DATE)
3094 THEN
3095 x_avlv_rec.last_update_date := l_avlv_rec.last_update_date;
3096 END IF;
3097 IF (x_avlv_rec.last_update_login = OKC_API.G_MISS_NUM)
3098 THEN
3099 x_avlv_rec.last_update_login := l_avlv_rec.last_update_login;
3100 END IF;
3101 -- Added by HKPATEL for securitization changes
3102 IF (x_avlv_rec.inv_code = OKC_API.G_MISS_CHAR)
3103 THEN
3104 x_avlv_rec.inv_code := l_avlv_rec.inv_code;
3105 END IF;
3106 RETURN(l_return_status);
3107 END populate_new_record;
3108 -------------------------------------------
3109 -- Set_Attributes for:OKL_AE_TEMPLATES_V --
3110 -------------------------------------------
3111 FUNCTION Set_Attributes (
3112 p_avlv_rec IN avlv_rec_type,
3113 x_avlv_rec OUT NOCOPY avlv_rec_type
3114 ) RETURN VARCHAR2 IS
3115 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3116 BEGIN
3117 x_avlv_rec := p_avlv_rec;
3118 RETURN(l_return_status);
3119 END Set_Attributes;
3120 BEGIN
3121 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3122 G_PKG_NAME,
3123 p_init_msg_list,
3124 l_api_version,
3125 p_api_version,
3126 '_PVT',
3127 x_return_status);
3128
3129 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3130 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3131 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3132 RAISE OKC_API.G_EXCEPTION_ERROR;
3133 END IF;
3134 --- Setting item attributes
3135 l_return_status := Set_Attributes(
3136 p_avlv_rec, -- IN
3137 l_avlv_rec); -- OUT
3138 --- If any errors happen abort API
3139 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3140 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3141 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3142 RAISE OKC_API.G_EXCEPTION_ERROR;
3143 END IF;
3144 -- HKPATEL added code for securitization
3145
3146 IF (l_avlv_rec.factoring_synd_flag = 'FACTORING') THEN
3147 l_avlv_rec.INV_CODE := NULL;
3148 l_avlv_rec.SYT_CODE := NULL;
3149 ELSIF (l_avlv_rec.factoring_synd_flag = 'SYNDICATION') THEN
3150 l_avlv_rec.INV_CODE := NULL;
3151 l_avlv_rec.FAC_CODE := NULL;
3152 ELSIF (l_avlv_rec.factoring_synd_flag = 'INVESTOR') THEN
3153 l_avlv_rec.SYT_CODE := NULL;
3154 l_avlv_rec.FAC_CODE := NULL;
3155 ELSE
3156 l_avlv_rec.SYT_CODE := NULL;
3157 l_avlv_rec.FAC_CODE := NULL;
3158 l_avlv_rec.INV_CODE := NULL;
3159 END IF;
3160
3161 l_return_status := populate_new_record(l_avlv_rec, l_def_avlv_rec);
3162 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3163 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3164 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3165 RAISE OKC_API.G_EXCEPTION_ERROR;
3166 END IF;
3167 l_def_avlv_rec := fill_who_columns(l_def_avlv_rec);
3168 --- Validate all non-missing attributes (Item Level Validation)
3169 l_return_status := Validate_Attributes(l_def_avlv_rec);
3170 --- If any errors happen abort API
3171 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3172 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3173 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3174 RAISE OKC_API.G_EXCEPTION_ERROR;
3175 END IF;
3176 l_return_status := Validate_Record(l_def_avlv_rec);
3177 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3178 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3179 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3180 RAISE OKC_API.G_EXCEPTION_ERROR;
3181 END IF;
3182
3183 --------------------------------------
3184 -- Move VIEW record to "Child" records
3185 --------------------------------------
3186 migrate(l_def_avlv_rec, l_avl_rec);
3187 --------------------------------------------
3188 -- Call the UPDATE_ROW for each child record
3189 --------------------------------------------
3190 update_row(
3191 p_init_msg_list,
3192 x_return_status,
3193 x_msg_count,
3194 x_msg_data,
3195 l_avl_rec,
3196 lx_avl_rec
3197 );
3198 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3199 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3200 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3201 RAISE OKC_API.G_EXCEPTION_ERROR;
3202 END IF;
3203 migrate(lx_avl_rec, l_def_avlv_rec);
3204 x_avlv_rec := l_def_avlv_rec;
3205 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3206 EXCEPTION
3207 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3208 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3209 (
3210 l_api_name,
3211 G_PKG_NAME,
3212 'OKC_API.G_RET_STS_ERROR',
3213 x_msg_count,
3214 x_msg_data,
3215 '_PVT'
3216 );
3217 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3218 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3219 (
3220 l_api_name,
3221 G_PKG_NAME,
3222 'OKC_API.G_RET_STS_UNEXP_ERROR',
3223 x_msg_count,
3224 x_msg_data,
3225 '_PVT'
3226 );
3227 WHEN OTHERS THEN
3228 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3229 (
3230 l_api_name,
3231 G_PKG_NAME,
3232 'OTHERS',
3233 x_msg_count,
3234 x_msg_data,
3235 '_PVT'
3236 );
3237 END update_row;
3238 ----------------------------------------
3239 -- PL/SQL TBL update_row for:AVLV_TBL --
3240 ----------------------------------------
3241 PROCEDURE update_row(
3242 p_api_version IN NUMBER,
3243 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3244 x_return_status OUT NOCOPY VARCHAR2,
3245 x_msg_count OUT NOCOPY NUMBER,
3246 x_msg_data OUT NOCOPY VARCHAR2,
3247 p_avlv_tbl IN avlv_tbl_type,
3248 x_avlv_tbl OUT NOCOPY avlv_tbl_type) IS
3249
3250 l_api_version CONSTANT NUMBER := 1;
3251 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3252 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3253 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3254 i NUMBER := 0;
3255 BEGIN
3256 OKC_API.init_msg_list(p_init_msg_list);
3257 -- Make sure PL/SQL table has records in it before passing
3258 IF (p_avlv_tbl.COUNT > 0) THEN
3259 i := p_avlv_tbl.FIRST;
3260 LOOP
3261 update_row (
3262 p_api_version => p_api_version,
3263 p_init_msg_list => OKC_API.G_FALSE,
3264 x_return_status => x_return_status,
3265 x_msg_count => x_msg_count,
3266 x_msg_data => x_msg_data,
3267 p_avlv_rec => p_avlv_tbl(i),
3268 x_avlv_rec => x_avlv_tbl(i));
3269 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3270 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
3271 l_overall_status := x_return_status;
3272 END IF;
3273 END IF;
3274 EXIT WHEN (i = p_avlv_tbl.LAST);
3275 i := p_avlv_tbl.NEXT(i);
3276 END LOOP;
3277 x_return_status := l_overall_status;
3278 END IF;
3279 EXCEPTION
3280 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3281 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3282 (
3283 l_api_name,
3284 G_PKG_NAME,
3285 'OKC_API.G_RET_STS_ERROR',
3286 x_msg_count,
3287 x_msg_data,
3288 '_PVT'
3289 );
3290 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3291 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3292 (
3293 l_api_name,
3294 G_PKG_NAME,
3295 'OKC_API.G_RET_STS_UNEXP_ERROR',
3296 x_msg_count,
3297 x_msg_data,
3298 '_PVT'
3299 );
3300 WHEN OTHERS THEN
3301 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3302 (
3303 l_api_name,
3304 G_PKG_NAME,
3305 'OTHERS',
3306 x_msg_count,
3307 x_msg_data,
3308 '_PVT'
3309 );
3310 END update_row;
3311
3312 ---------------------------------------------------------------------------
3313 -- PROCEDURE delete_row
3314 ---------------------------------------------------------------------------
3315 -------------------------------------
3316 -- delete_row for:OKL_AE_TEMPLATES --
3317 -------------------------------------
3318 PROCEDURE delete_row(
3319 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3320 x_return_status OUT NOCOPY VARCHAR2,
3321 x_msg_count OUT NOCOPY NUMBER,
3322 x_msg_data OUT NOCOPY VARCHAR2,
3323 p_avl_rec IN avl_rec_type) IS
3324
3325 l_api_version CONSTANT NUMBER := 1;
3326 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_delete_row';
3327 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3328 l_avl_rec avl_rec_type:= p_avl_rec;
3329 l_row_notfound BOOLEAN := TRUE;
3330 BEGIN
3331 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3332 p_init_msg_list,
3333 '_PVT',
3334 x_return_status);
3335 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3336 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3337 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3338 RAISE OKC_API.G_EXCEPTION_ERROR;
3339 END IF;
3340 DELETE FROM OKL_AE_TEMPLATES
3341 WHERE ID = l_avl_rec.id;
3342
3343 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3344 EXCEPTION
3345 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3346 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3347 (
3348 l_api_name,
3349 G_PKG_NAME,
3350 'OKC_API.G_RET_STS_ERROR',
3351 x_msg_count,
3352 x_msg_data,
3353 '_PVT'
3354 );
3355 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3356 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3357 (
3358 l_api_name,
3359 G_PKG_NAME,
3360 'OKC_API.G_RET_STS_UNEXP_ERROR',
3361 x_msg_count,
3362 x_msg_data,
3363 '_PVT'
3364 );
3365 WHEN OTHERS THEN
3366 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3367 (
3368 l_api_name,
3369 G_PKG_NAME,
3370 'OTHERS',
3371 x_msg_count,
3372 x_msg_data,
3373 '_PVT'
3374 );
3375 END delete_row;
3376 ---------------------------------------
3377 -- delete_row for:OKL_AE_TEMPLATES_V --
3378 ---------------------------------------
3379 PROCEDURE delete_row(
3380 p_api_version IN NUMBER,
3381 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3382 x_return_status OUT NOCOPY VARCHAR2,
3383 x_msg_count OUT NOCOPY NUMBER,
3384 x_msg_data OUT NOCOPY VARCHAR2,
3385 p_avlv_rec IN avlv_rec_type) IS
3386
3387 l_api_version CONSTANT NUMBER := 1;
3388 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3389 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3390 l_avlv_rec avlv_rec_type := p_avlv_rec;
3391 l_avl_rec avl_rec_type;
3392 BEGIN
3393 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3394 G_PKG_NAME,
3395 p_init_msg_list,
3396 l_api_version,
3397 p_api_version,
3398 '_PVT',
3399 x_return_status);
3400 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3401 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3402 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3403 RAISE OKC_API.G_EXCEPTION_ERROR;
3404 END IF;
3405 --------------------------------------
3406 -- Move VIEW record to "Child" records
3407 --------------------------------------
3408 migrate(l_avlv_rec, l_avl_rec);
3409 --------------------------------------------
3410 -- Call the DELETE_ROW for each child record
3411 --------------------------------------------
3412 delete_row(
3413 p_init_msg_list,
3414 x_return_status,
3415 x_msg_count,
3416 x_msg_data,
3417 l_avl_rec
3418 );
3419 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3420 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3421 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3422 RAISE OKC_API.G_EXCEPTION_ERROR;
3423 END IF;
3424 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3425 EXCEPTION
3426 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3427 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3428 (
3429 l_api_name,
3430 G_PKG_NAME,
3431 'OKC_API.G_RET_STS_ERROR',
3432 x_msg_count,
3433 x_msg_data,
3434 '_PVT'
3435 );
3436 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3437 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3438 (
3439 l_api_name,
3440 G_PKG_NAME,
3441 'OKC_API.G_RET_STS_UNEXP_ERROR',
3442 x_msg_count,
3443 x_msg_data,
3444 '_PVT'
3445 );
3446 WHEN OTHERS THEN
3447 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3448 (
3449 l_api_name,
3450 G_PKG_NAME,
3451 'OTHERS',
3452 x_msg_count,
3453 x_msg_data,
3454 '_PVT'
3455 );
3456 END delete_row;
3457 ----------------------------------------
3458 -- PL/SQL TBL delete_row for:AVLV_TBL --
3459 ----------------------------------------
3460 PROCEDURE delete_row(
3461 p_api_version IN NUMBER,
3462 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3463 x_return_status OUT NOCOPY VARCHAR2,
3464 x_msg_count OUT NOCOPY NUMBER,
3465 x_msg_data OUT NOCOPY VARCHAR2,
3466 p_avlv_tbl IN avlv_tbl_type) IS
3467
3468 l_api_version CONSTANT NUMBER := 1;
3469 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3470 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3471 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3472 i NUMBER := 0;
3473 BEGIN
3474 OKC_API.init_msg_list(p_init_msg_list);
3475 -- Make sure PL/SQL table has records in it before passing
3476 IF (p_avlv_tbl.COUNT > 0) THEN
3477 i := p_avlv_tbl.FIRST;
3478 LOOP
3479 delete_row (
3480 p_api_version => p_api_version,
3481 p_init_msg_list => OKC_API.G_FALSE,
3482 x_return_status => x_return_status,
3483 x_msg_count => x_msg_count,
3484 x_msg_data => x_msg_data,
3485 p_avlv_rec => p_avlv_tbl(i));
3486 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3487 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
3488 l_overall_status := x_return_status;
3489 END IF;
3490 END IF;
3491 EXIT WHEN (i = p_avlv_tbl.LAST);
3492 i := p_avlv_tbl.NEXT(i);
3493 END LOOP;
3494 x_return_status := l_overall_status;
3495 END IF;
3496 EXCEPTION
3497 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3498 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3499 (
3500 l_api_name,
3501 G_PKG_NAME,
3502 'OKC_API.G_RET_STS_ERROR',
3503 x_msg_count,
3504 x_msg_data,
3505 '_PVT'
3506 );
3507 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3508 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3509 (
3510 l_api_name,
3511 G_PKG_NAME,
3512 'OKC_API.G_RET_STS_UNEXP_ERROR',
3513 x_msg_count,
3514 x_msg_data,
3515 '_PVT'
3516 );
3517 WHEN OTHERS THEN
3518 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3519 (
3520 l_api_name,
3521 G_PKG_NAME,
3522 'OTHERS',
3523 x_msg_count,
3524 x_msg_data,
3525 '_PVT'
3526 );
3527 END delete_row;
3528 END OKL_AVL_PVT;