[Home] [Help]
PACKAGE BODY: APPS.OKL_AVL_PVT
Source
1 PACKAGE BODY OKL_AVL_PVT AS
2 /* $Header: OKLSAVLB.pls 120.4.12020000.2 2012/08/07 13:31:22 rpillay ship $ */
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 --changed the following by jjuneja
1978 --null,
1979 l_avl_rec.set_of_books_id,
1980 --end of change by jjuneja
1981 l_avl_rec.sty_id,
1982 l_avl_rec.try_id,
1983 l_avl_rec.aes_id,
1984 l_avl_rec.syt_code,
1985 l_avl_rec.fac_code,
1986 l_avl_rec.fma_id,
1987 l_avl_rec.advance_arrears,
1988 --l_avl_rec.post_to_gl,
1989 null,
1990 --l_avl_rec.version,
1991 null,
1992 l_avl_rec.start_date,
1993 l_avl_rec.object_version_number,
1994 l_avl_rec.memo_yn,
1995 l_avl_rec.prior_year_yn,
1996 l_avl_rec.description,
1997 l_avl_rec.factoring_synd_flag,
1998 l_avl_rec.end_date,
1999 --l_avl_rec.accrual_yn,
2000 null,
2001 l_avl_rec.org_id,
2002 l_avl_rec.attribute_category,
2003 l_avl_rec.attribute1,
2004 l_avl_rec.attribute2,
2005 l_avl_rec.attribute3,
2006 l_avl_rec.attribute4,
2007 l_avl_rec.attribute5,
2008 l_avl_rec.attribute6,
2009 l_avl_rec.attribute7,
2010 l_avl_rec.attribute8,
2011 l_avl_rec.attribute9,
2012 l_avl_rec.attribute10,
2013 l_avl_rec.attribute11,
2014 l_avl_rec.attribute12,
2015 l_avl_rec.attribute13,
2016 l_avl_rec.attribute14,
2017 l_avl_rec.attribute15,
2018 l_avl_rec.created_by,
2019 l_avl_rec.creation_date,
2020 l_avl_rec.last_updated_by,
2021 l_avl_rec.last_update_date,
2022 l_avl_rec.last_update_login,
2023 -- Added by HKPATEL for securitization changes
2024 l_avl_rec.inv_code);
2025 -- Fixed Bug # 5707866 ssdeshpa End
2026 -- Set OUT values
2027 x_avl_rec := l_avl_rec;
2028 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2029 EXCEPTION
2030 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2031 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2032 (
2033 l_api_name,
2034 G_PKG_NAME,
2035 'OKC_API.G_RET_STS_ERROR',
2036 x_msg_count,
2037 x_msg_data,
2038 '_PVT'
2039 );
2040 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2041 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2042 (
2043 l_api_name,
2044 G_PKG_NAME,
2045 'OKC_API.G_RET_STS_UNEXP_ERROR',
2046 x_msg_count,
2047 x_msg_data,
2048 '_PVT'
2049 );
2050 WHEN OTHERS THEN
2051 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2052 (
2053 l_api_name,
2054 G_PKG_NAME,
2055 'OTHERS',
2056 x_msg_count,
2057 x_msg_data,
2058 '_PVT'
2059 );
2060 END insert_row;
2061 ---------------------------------------
2062 -- insert_row for:OKL_AE_TEMPLATES_V --
2063 ---------------------------------------
2064 PROCEDURE insert_row(
2065 p_api_version IN NUMBER,
2066 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2067 x_return_status OUT NOCOPY VARCHAR2,
2068 x_msg_count OUT NOCOPY NUMBER,
2069 x_msg_data OUT NOCOPY VARCHAR2,
2070 p_avlv_rec IN avlv_rec_type,
2071 x_avlv_rec OUT NOCOPY avlv_rec_type) IS
2072
2073 l_api_version CONSTANT NUMBER := 1;
2074 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2075 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2076 l_avlv_rec avlv_rec_type;
2077 l_def_avlv_rec avlv_rec_type;
2078 l_avl_rec avl_rec_type;
2079 lx_avl_rec avl_rec_type;
2080 -------------------------------
2081 -- FUNCTION fill_who_columns --
2082 -------------------------------
2083
2084 FUNCTION fill_who_columns (
2085 p_avlv_rec IN avlv_rec_type
2086 ) RETURN avlv_rec_type IS
2087 l_avlv_rec avlv_rec_type := p_avlv_rec;
2088 BEGIN
2089 l_avlv_rec.CREATION_DATE := SYSDATE;
2090 l_avlv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2091 l_avlv_rec.LAST_UPDATE_DATE := SYSDATE;
2092 l_avlv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2093 l_avlv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2094 RETURN(l_avlv_rec);
2095 END fill_who_columns;
2096 -------------------------------------------
2097 -- Set_Attributes for:OKL_AE_TEMPLATES_V --
2098 -------------------------------------------
2099 FUNCTION Set_Attributes (
2100 p_avlv_rec IN avlv_rec_type,
2101 x_avlv_rec OUT NOCOPY avlv_rec_type
2102 ) RETURN VARCHAR2 IS
2103 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2104 BEGIN
2105 x_avlv_rec := p_avlv_rec;
2106 x_avlv_rec.OBJECT_VERSION_NUMBER := 1;
2107 /*Fixed Bug # 5707866 ssdeshpa start
2108 --Removing Defaulting Logic for POST_TO_GL , SET_OF_BOOKS_ID , ACCRUAL_YN
2109 --;Since This Column are NULL */
2110 --x_avlv_rec.POST_TO_GL := 'Y'; -- this field is not used, so a default provided
2111 x_avlv_rec.ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID();
2112 --x_avlv_rec.set_of_books_id := OKL_ACCOUNTING_UTIL.get_Set_of_books_id;
2113
2114 x_avlv_rec.NAME := UPPER(x_avlv_rec.NAME);
2115 -- Santonyr 18th Jul, 2002.
2116 -- Added the code to set the default value for accrual_yn
2117 -- x_avlv_rec.accrual_yn := 'N';
2118 --Fixed Bug # 5707866 ssdeshpa end
2119 RETURN(l_return_status);
2120 END Set_Attributes;
2121 BEGIN
2122 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2123 G_PKG_NAME,
2124 p_init_msg_list,
2125 l_api_version,
2126 p_api_version,
2127 '_PVT',
2128 x_return_status);
2129
2130 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2131 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2132 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2133 RAISE OKC_API.G_EXCEPTION_ERROR;
2134 END IF;
2135
2136 l_avlv_rec := null_out_defaults(p_avlv_rec);
2137 -- Set primary key value
2138 l_avlv_rec.ID := get_seq_id;
2139 --- Setting item attributes
2140
2141
2142 l_return_status := Set_Attributes(
2143 l_avlv_rec, -- IN
2144 l_def_avlv_rec); -- OUT
2145 --- If any errors happen abort API
2146 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2147 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2148 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2149 RAISE OKC_API.G_EXCEPTION_ERROR;
2150 END IF;
2151 l_def_avlv_rec := fill_who_columns(l_def_avlv_rec);
2152 --- Validate all non-missing attributes (Item Level Validation)
2153 l_return_status := Validate_Attributes(l_def_avlv_rec);
2154 --- If any errors happen abort API
2155 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2156 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2157 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2158 RAISE OKC_API.G_EXCEPTION_ERROR;
2159 END IF;
2160 l_return_status := Validate_Record(l_def_avlv_rec);
2161 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2162 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2163 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2164 RAISE OKC_API.G_EXCEPTION_ERROR;
2165 END IF;
2166 --------------------------------------
2167 -- Move VIEW record to "Child" records
2168 --------------------------------------
2169 migrate(l_def_avlv_rec, l_avl_rec);
2170 --------------------------------------------
2171 -- Call the INSERT_ROW for each child record
2172 --------------------------------------------
2173 insert_row(
2174 p_init_msg_list,
2175 x_return_status,
2176 x_msg_count,
2177 x_msg_data,
2178 l_avl_rec,
2179 lx_avl_rec
2180 );
2181 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2182 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2183 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2184 RAISE OKC_API.G_EXCEPTION_ERROR;
2185 END IF;
2186 migrate(lx_avl_rec, l_def_avlv_rec);
2187 -- Set OUT values
2188 x_avlv_rec := l_def_avlv_rec;
2189 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2190 EXCEPTION
2191 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2192 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2193 (
2194 l_api_name,
2195 G_PKG_NAME,
2196 'OKC_API.G_RET_STS_ERROR',
2197 x_msg_count,
2198 x_msg_data,
2199 '_PVT'
2200 );
2201 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2202 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2203 (
2204 l_api_name,
2205 G_PKG_NAME,
2206 'OKC_API.G_RET_STS_UNEXP_ERROR',
2207 x_msg_count,
2208 x_msg_data,
2209 '_PVT'
2210 );
2211 WHEN OTHERS THEN
2212 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2213 (
2214 l_api_name,
2215 G_PKG_NAME,
2216 'OTHERS',
2217 x_msg_count,
2218 x_msg_data,
2219 '_PVT'
2220 );
2221 END insert_row;
2222 ----------------------------------------
2223 -- PL/SQL TBL insert_row for:AVLV_TBL --
2224 ----------------------------------------
2225 PROCEDURE insert_row(
2226 p_api_version IN NUMBER,
2227 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2228 x_return_status OUT NOCOPY VARCHAR2,
2229 x_msg_count OUT NOCOPY NUMBER,
2230 x_msg_data OUT NOCOPY VARCHAR2,
2231 p_avlv_tbl IN avlv_tbl_type,
2232 x_avlv_tbl OUT NOCOPY avlv_tbl_type) IS
2233
2234 l_api_version CONSTANT NUMBER := 1;
2235 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2236 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2237 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2238 i NUMBER := 0;
2239 BEGIN
2240 OKC_API.init_msg_list(p_init_msg_list);
2241 -- Make sure PL/SQL table has records in it before passing
2242 IF (p_avlv_tbl.COUNT > 0) THEN
2243 i := p_avlv_tbl.FIRST;
2244 LOOP
2245 insert_row (
2246 p_api_version => p_api_version,
2247 p_init_msg_list => OKC_API.G_FALSE,
2248 x_return_status => x_return_status,
2249 x_msg_count => x_msg_count,
2250 x_msg_data => x_msg_data,
2251 p_avlv_rec => p_avlv_tbl(i),
2252 x_avlv_rec => x_avlv_tbl(i));
2253 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2254 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2255 l_overall_status := x_return_status;
2256 END IF;
2257 END IF;
2258 EXIT WHEN (i = p_avlv_tbl.LAST);
2259 i := p_avlv_tbl.NEXT(i);
2260 END LOOP;
2261 x_return_status := l_overall_status;
2262 END IF;
2263 EXCEPTION
2264 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2265 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2266 (
2267 l_api_name,
2268 G_PKG_NAME,
2269 'OKC_API.G_RET_STS_ERROR',
2270 x_msg_count,
2271 x_msg_data,
2272 '_PVT'
2273 );
2274 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2275 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2276 (
2277 l_api_name,
2278 G_PKG_NAME,
2279 'OKC_API.G_RET_STS_UNEXP_ERROR',
2280 x_msg_count,
2281 x_msg_data,
2282 '_PVT'
2283 );
2284 WHEN OTHERS THEN
2285 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2286 (
2287 l_api_name,
2288 G_PKG_NAME,
2289 'OTHERS',
2290 x_msg_count,
2291 x_msg_data,
2292 '_PVT'
2293 );
2294 END insert_row;
2295
2296 ---------------------------------------------------------------------------
2297 -- PROCEDURE lock_row
2298 ---------------------------------------------------------------------------
2299 -----------------------------------
2300 -- lock_row for:OKL_AE_TEMPLATES --
2301 -----------------------------------
2302 PROCEDURE lock_row(
2303 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2304 x_return_status OUT NOCOPY VARCHAR2,
2305 x_msg_count OUT NOCOPY NUMBER,
2306 x_msg_data OUT NOCOPY VARCHAR2,
2307 p_avl_rec IN avl_rec_type) IS
2308
2309 E_Resource_Busy EXCEPTION;
2310 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2311 CURSOR lock_csr (p_avl_rec IN avl_rec_type) IS
2312 SELECT OBJECT_VERSION_NUMBER
2313 FROM OKL_AE_TEMPLATES
2314 WHERE ID = p_avl_rec.id
2315 AND OBJECT_VERSION_NUMBER = p_avl_rec.object_version_number
2316 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2317
2318 CURSOR lchk_csr (p_avl_rec IN avl_rec_type) IS
2319 SELECT OBJECT_VERSION_NUMBER
2320 FROM OKL_AE_TEMPLATES
2321 WHERE ID = p_avl_rec.id;
2322 l_api_version CONSTANT NUMBER := 1;
2323 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_lock_row';
2324 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2325 l_object_version_number OKL_AE_TEMPLATES.OBJECT_VERSION_NUMBER%TYPE;
2326 lc_object_version_number OKL_AE_TEMPLATES.OBJECT_VERSION_NUMBER%TYPE;
2327 l_row_notfound BOOLEAN := FALSE;
2328 lc_row_notfound BOOLEAN := FALSE;
2329 BEGIN
2330 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2331 p_init_msg_list,
2332 '_PVT',
2333 x_return_status);
2334 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2335 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2336 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2337 RAISE OKC_API.G_EXCEPTION_ERROR;
2338 END IF;
2339 BEGIN
2340 OPEN lock_csr(p_avl_rec);
2341 FETCH lock_csr INTO l_object_version_number;
2342 l_row_notfound := lock_csr%NOTFOUND;
2343 CLOSE lock_csr;
2344 EXCEPTION
2345 WHEN E_Resource_Busy THEN
2346 IF (lock_csr%ISOPEN) THEN
2347 CLOSE lock_csr;
2348 END IF;
2349 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2350 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2351 END;
2352
2353 IF ( l_row_notfound ) THEN
2354 OPEN lchk_csr(p_avl_rec);
2355 FETCH lchk_csr INTO lc_object_version_number;
2356 lc_row_notfound := lchk_csr%NOTFOUND;
2357 CLOSE lchk_csr;
2358 END IF;
2359 IF (lc_row_notfound) THEN
2360 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
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 <> p_avl_rec.object_version_number THEN
2366 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2367 RAISE OKC_API.G_EXCEPTION_ERROR;
2368 ELSIF lc_object_version_number = -1 THEN
2369 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2370 RAISE OKC_API.G_EXCEPTION_ERROR;
2371 END IF;
2372 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2373 EXCEPTION
2374 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2375 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2376 (
2377 l_api_name,
2378 G_PKG_NAME,
2379 'OKC_API.G_RET_STS_ERROR',
2380 x_msg_count,
2381 x_msg_data,
2382 '_PVT'
2383 );
2384 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2385 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2386 (
2387 l_api_name,
2388 G_PKG_NAME,
2389 'OKC_API.G_RET_STS_UNEXP_ERROR',
2390 x_msg_count,
2391 x_msg_data,
2392 '_PVT'
2393 );
2394 WHEN OTHERS THEN
2395 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2396 (
2397 l_api_name,
2398 G_PKG_NAME,
2399 'OTHERS',
2400 x_msg_count,
2401 x_msg_data,
2402 '_PVT'
2403 );
2404 END lock_row;
2405 -------------------------------------
2406 -- lock_row for:OKL_AE_TEMPLATES_V --
2407 -------------------------------------
2408 PROCEDURE lock_row(
2409 p_api_version IN NUMBER,
2410 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2411 x_return_status OUT NOCOPY VARCHAR2,
2412 x_msg_count OUT NOCOPY NUMBER,
2413 x_msg_data OUT NOCOPY VARCHAR2,
2414 p_avlv_rec IN avlv_rec_type) IS
2415
2416 l_api_version CONSTANT NUMBER := 1;
2417 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2418 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2419 l_avl_rec avl_rec_type;
2420 BEGIN
2421 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2422 G_PKG_NAME,
2423 p_init_msg_list,
2424 l_api_version,
2425 p_api_version,
2426 '_PVT',
2427 x_return_status);
2428 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2429 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2430 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2431 RAISE OKC_API.G_EXCEPTION_ERROR;
2432 END IF;
2433 --------------------------------------
2434 -- Move VIEW record to "Child" records
2435 --------------------------------------
2436 migrate(p_avlv_rec, l_avl_rec);
2437 --------------------------------------------
2438 -- Call the LOCK_ROW for each child record
2439 --------------------------------------------
2440 lock_row(
2441 p_init_msg_list,
2442 x_return_status,
2443 x_msg_count,
2444 x_msg_data,
2445 l_avl_rec
2446 );
2447 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2448 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2449 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2450 RAISE OKC_API.G_EXCEPTION_ERROR;
2451 END IF;
2452 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2453 EXCEPTION
2454 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2455 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2456 (
2457 l_api_name,
2458 G_PKG_NAME,
2459 'OKC_API.G_RET_STS_ERROR',
2460 x_msg_count,
2461 x_msg_data,
2462 '_PVT'
2463 );
2464 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2465 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2466 (
2467 l_api_name,
2468 G_PKG_NAME,
2469 'OKC_API.G_RET_STS_UNEXP_ERROR',
2470 x_msg_count,
2471 x_msg_data,
2472 '_PVT'
2473 );
2474 WHEN OTHERS THEN
2475 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2476 (
2477 l_api_name,
2478 G_PKG_NAME,
2479 'OTHERS',
2480 x_msg_count,
2481 x_msg_data,
2482 '_PVT'
2483 );
2484 END lock_row;
2485 --------------------------------------
2486 -- PL/SQL TBL lock_row for:AVLV_TBL --
2487 --------------------------------------
2488 PROCEDURE lock_row(
2489 p_api_version IN NUMBER,
2490 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2491 x_return_status OUT NOCOPY VARCHAR2,
2492 x_msg_count OUT NOCOPY NUMBER,
2493 x_msg_data OUT NOCOPY VARCHAR2,
2494 p_avlv_tbl IN avlv_tbl_type) IS
2495
2496 l_api_version CONSTANT NUMBER := 1;
2497 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2498 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2499 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2500 i NUMBER := 0;
2501 BEGIN
2502 OKC_API.init_msg_list(p_init_msg_list);
2503 -- Make sure PL/SQL table has records in it before passing
2504 IF (p_avlv_tbl.COUNT > 0) THEN
2505 i := p_avlv_tbl.FIRST;
2506 LOOP
2507 lock_row (
2508 p_api_version => p_api_version,
2509 p_init_msg_list => OKC_API.G_FALSE,
2510 x_return_status => x_return_status,
2511 x_msg_count => x_msg_count,
2512 x_msg_data => x_msg_data,
2513 p_avlv_rec => p_avlv_tbl(i));
2514 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2515 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2516 l_overall_status := x_return_status;
2517 END IF;
2518 END IF;
2519 EXIT WHEN (i = p_avlv_tbl.LAST);
2520 i := p_avlv_tbl.NEXT(i);
2521 END LOOP;
2522 x_return_status := l_overall_status;
2523 END IF;
2524 EXCEPTION
2525 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2526 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2527 (
2528 l_api_name,
2529 G_PKG_NAME,
2530 'OKC_API.G_RET_STS_ERROR',
2531 x_msg_count,
2532 x_msg_data,
2533 '_PVT'
2534 );
2535 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2536 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2537 (
2538 l_api_name,
2539 G_PKG_NAME,
2540 'OKC_API.G_RET_STS_UNEXP_ERROR',
2541 x_msg_count,
2542 x_msg_data,
2543 '_PVT'
2544 );
2545 WHEN OTHERS THEN
2546 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2547 (
2548 l_api_name,
2549 G_PKG_NAME,
2550 'OTHERS',
2551 x_msg_count,
2552 x_msg_data,
2553 '_PVT'
2554 );
2555 END lock_row;
2556
2557 ---------------------------------------------------------------------------
2558 -- PROCEDURE update_row
2559 ---------------------------------------------------------------------------
2560 -------------------------------------
2561 -- update_row for:OKL_AE_TEMPLATES --
2562 -------------------------------------
2563 PROCEDURE update_row(
2564 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2565 x_return_status OUT NOCOPY VARCHAR2,
2566 x_msg_count OUT NOCOPY NUMBER,
2567 x_msg_data OUT NOCOPY VARCHAR2,
2568 p_avl_rec IN avl_rec_type,
2569 x_avl_rec OUT NOCOPY avl_rec_type) IS
2570
2571 l_api_version CONSTANT NUMBER := 1;
2572 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_update_row';
2573 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2574 l_avl_rec avl_rec_type := p_avl_rec;
2575 l_def_avl_rec avl_rec_type;
2576 l_row_notfound BOOLEAN := TRUE;
2577 ----------------------------------
2578 -- FUNCTION populate_new_record --
2579 ----------------------------------
2580 FUNCTION populate_new_record (
2581 p_avl_rec IN avl_rec_type,
2582 x_avl_rec OUT NOCOPY avl_rec_type
2583 ) RETURN VARCHAR2 IS
2584 l_avl_rec avl_rec_type;
2585 l_row_notfound BOOLEAN := TRUE;
2586 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2587 BEGIN
2588 x_avl_rec := p_avl_rec;
2589 -- Get current database values
2590 l_avl_rec := get_rec(p_avl_rec, l_row_notfound);
2591 IF (l_row_notfound) THEN
2592 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2593 END IF;
2594 IF (x_avl_rec.id = OKC_API.G_MISS_NUM)
2595 THEN
2596 x_avl_rec.id := l_avl_rec.id;
2597 END IF;
2598 IF (x_avl_rec.name = OKC_API.G_MISS_CHAR)
2599 THEN
2600 x_avl_rec.name := l_avl_rec.name;
2601 END IF;
2602 IF (x_avl_rec.set_of_books_id = OKC_API.G_MISS_NUM)
2603 THEN
2604 x_avl_rec.set_of_books_id := l_avl_rec.set_of_books_id;
2605 END IF;
2606 IF (x_avl_rec.sty_id = OKC_API.G_MISS_NUM)
2607 THEN
2608 x_avl_rec.sty_id := l_avl_rec.sty_id;
2609 END IF;
2610 IF (x_avl_rec.try_id = OKC_API.G_MISS_NUM)
2611 THEN
2612 x_avl_rec.try_id := l_avl_rec.try_id;
2613 END IF;
2614 IF (x_avl_rec.aes_id = OKC_API.G_MISS_NUM)
2615 THEN
2616 x_avl_rec.aes_id := l_avl_rec.aes_id;
2617 END IF;
2618 IF (x_avl_rec.syt_code = OKC_API.G_MISS_CHAR)
2619 THEN
2620 x_avl_rec.syt_code := l_avl_rec.syt_code;
2621 END IF;
2622 IF (x_avl_rec.fac_code = OKC_API.G_MISS_CHAR)
2623 THEN
2624 x_avl_rec.fac_code := l_avl_rec.fac_code;
2625 END IF;
2626 IF (x_avl_rec.fma_id = OKC_API.G_MISS_NUM)
2627 THEN
2628 x_avl_rec.fma_id := l_avl_rec.fma_id;
2629 END IF;
2630 IF (x_avl_rec.advance_arrears = OKC_API.G_MISS_CHAR)
2631 THEN
2632 x_avl_rec.advance_arrears := l_avl_rec.advance_arrears;
2633 END IF;
2634 IF (x_avl_rec.post_to_gl = OKC_API.G_MISS_CHAR)
2635 THEN
2636 x_avl_rec.post_to_gl := l_avl_rec.post_to_gl;
2637 END IF;
2638 IF (x_avl_rec.version = OKC_API.G_MISS_CHAR)
2639 THEN
2640 x_avl_rec.version := l_avl_rec.version;
2641 END IF;
2642 IF (x_avl_rec.start_date = OKC_API.G_MISS_DATE)
2643 THEN
2644 x_avl_rec.start_date := l_avl_rec.start_date;
2645 END IF;
2646 IF (x_avl_rec.object_version_number = OKC_API.G_MISS_NUM)
2647 THEN
2648 x_avl_rec.object_version_number := l_avl_rec.object_version_number;
2649 END IF;
2650 IF (x_avl_rec.memo_yn = OKC_API.G_MISS_CHAR)
2651 THEN
2652 x_avl_rec.memo_yn := l_avl_rec.memo_yn;
2653 END IF;
2654 IF (x_avl_rec.prior_year_yn = OKC_API.G_MISS_CHAR)
2655 THEN
2656 x_avl_rec.prior_year_yn := l_avl_rec.prior_year_yn;
2657 END IF;
2658 IF (x_avl_rec.description = OKC_API.G_MISS_CHAR)
2659 THEN
2660 x_avl_rec.description := l_avl_rec.description;
2661 END IF;
2662 IF (x_avl_rec.factoring_synd_flag = OKC_API.G_MISS_CHAR)
2663 THEN
2664 x_avl_rec.factoring_synd_flag := l_avl_rec.factoring_synd_flag;
2665 END IF;
2666 IF (x_avl_rec.end_date = OKC_API.G_MISS_DATE)
2667 THEN
2668 x_avl_rec.end_date := l_avl_rec.end_date;
2669 END IF;
2670 IF (x_avl_rec.accrual_yn = OKC_API.G_MISS_CHAR)
2671 THEN
2672 x_avl_rec.accrual_yn := l_avl_rec.accrual_yn;
2673 END IF;
2674 IF (x_avl_rec.org_id = OKC_API.G_MISS_NUM)
2675 THEN
2676 x_avl_rec.org_id := l_avl_rec.org_id;
2677 END IF;
2678 IF (x_avl_rec.attribute_category = OKC_API.G_MISS_CHAR)
2679 THEN
2680 x_avl_rec.attribute_category := l_avl_rec.attribute_category;
2681 END IF;
2682 IF (x_avl_rec.attribute1 = OKC_API.G_MISS_CHAR)
2683 THEN
2684 x_avl_rec.attribute1 := l_avl_rec.attribute1;
2685 END IF;
2686 IF (x_avl_rec.attribute2 = OKC_API.G_MISS_CHAR)
2687 THEN
2688 x_avl_rec.attribute2 := l_avl_rec.attribute2;
2689 END IF;
2690 IF (x_avl_rec.attribute3 = OKC_API.G_MISS_CHAR)
2691 THEN
2692 x_avl_rec.attribute3 := l_avl_rec.attribute3;
2693 END IF;
2694 IF (x_avl_rec.attribute4 = OKC_API.G_MISS_CHAR)
2695 THEN
2696 x_avl_rec.attribute4 := l_avl_rec.attribute4;
2697 END IF;
2698 IF (x_avl_rec.attribute5 = OKC_API.G_MISS_CHAR)
2699 THEN
2700 x_avl_rec.attribute5 := l_avl_rec.attribute5;
2701 END IF;
2702 IF (x_avl_rec.attribute6 = OKC_API.G_MISS_CHAR)
2703 THEN
2704 x_avl_rec.attribute6 := l_avl_rec.attribute6;
2705 END IF;
2706 IF (x_avl_rec.attribute7 = OKC_API.G_MISS_CHAR)
2707 THEN
2708 x_avl_rec.attribute7 := l_avl_rec.attribute7;
2709 END IF;
2710 IF (x_avl_rec.attribute8 = OKC_API.G_MISS_CHAR)
2711 THEN
2712 x_avl_rec.attribute8 := l_avl_rec.attribute8;
2713 END IF;
2714 IF (x_avl_rec.attribute9 = OKC_API.G_MISS_CHAR)
2715 THEN
2716 x_avl_rec.attribute9 := l_avl_rec.attribute9;
2717 END IF;
2718 IF (x_avl_rec.attribute10 = OKC_API.G_MISS_CHAR)
2719 THEN
2720 x_avl_rec.attribute10 := l_avl_rec.attribute10;
2721 END IF;
2722 IF (x_avl_rec.attribute11 = OKC_API.G_MISS_CHAR)
2723 THEN
2724 x_avl_rec.attribute11 := l_avl_rec.attribute11;
2725 END IF;
2726 IF (x_avl_rec.attribute12 = OKC_API.G_MISS_CHAR)
2727 THEN
2728 x_avl_rec.attribute12 := l_avl_rec.attribute12;
2729 END IF;
2730 IF (x_avl_rec.attribute13 = OKC_API.G_MISS_CHAR)
2731 THEN
2732 x_avl_rec.attribute13 := l_avl_rec.attribute13;
2733 END IF;
2734 IF (x_avl_rec.attribute14 = OKC_API.G_MISS_CHAR)
2735 THEN
2736 x_avl_rec.attribute14 := l_avl_rec.attribute14;
2737 END IF;
2738 IF (x_avl_rec.attribute15 = OKC_API.G_MISS_CHAR)
2739 THEN
2740 x_avl_rec.attribute15 := l_avl_rec.attribute15;
2741 END IF;
2742 IF (x_avl_rec.created_by = OKC_API.G_MISS_NUM)
2743 THEN
2744 x_avl_rec.created_by := l_avl_rec.created_by;
2745 END IF;
2746 IF (x_avl_rec.creation_date = OKC_API.G_MISS_DATE)
2747 THEN
2748 x_avl_rec.creation_date := l_avl_rec.creation_date;
2749 END IF;
2750 IF (x_avl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2751 THEN
2752 x_avl_rec.last_updated_by := l_avl_rec.last_updated_by;
2753 END IF;
2754 IF (x_avl_rec.last_update_date = OKC_API.G_MISS_DATE)
2755 THEN
2756 x_avl_rec.last_update_date := l_avl_rec.last_update_date;
2757 END IF;
2758 IF (x_avl_rec.last_update_login = OKC_API.G_MISS_NUM)
2759 THEN
2760 x_avl_rec.last_update_login := l_avl_rec.last_update_login;
2761 END IF;
2762 -- Added by HKPATEL for securitization changes
2763 IF (x_avl_rec.inv_code = OKC_API.G_MISS_CHAR)
2764 THEN
2765 x_avl_rec.inv_code := l_avl_rec.inv_code;
2766 END IF;
2767 RETURN(l_return_status);
2768 END populate_new_record;
2769 -----------------------------------------
2770 -- Set_Attributes for:OKL_AE_TEMPLATES --
2771 -----------------------------------------
2772 FUNCTION Set_Attributes (
2773 p_avl_rec IN avl_rec_type,
2774 x_avl_rec OUT NOCOPY avl_rec_type
2775 ) RETURN VARCHAR2 IS
2776 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2777 BEGIN
2778 x_avl_rec := p_avl_rec;
2779 RETURN(l_return_status);
2780 END Set_Attributes;
2781 BEGIN
2782 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2783 p_init_msg_list,
2784 '_PVT',
2785 x_return_status);
2786 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2787 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2788 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2789 RAISE OKC_API.G_EXCEPTION_ERROR;
2790 END IF;
2791 --- Setting item attributes
2792 l_return_status := Set_Attributes(
2793 p_avl_rec, -- IN
2794 l_avl_rec); -- OUT
2795 --- If any errors happen abort API
2796 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2797 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2798 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2799 RAISE OKC_API.G_EXCEPTION_ERROR;
2800 END IF;
2801 l_return_status := populate_new_record(l_avl_rec, l_def_avl_rec);
2802 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2803 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2804 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2805 RAISE OKC_API.G_EXCEPTION_ERROR;
2806 END IF;
2807 UPDATE OKL_AE_TEMPLATES
2808 SET NAME = l_def_avl_rec.name,
2809 SET_OF_BOOKS_ID = l_def_avl_rec.set_of_books_id,
2810 STY_ID = l_def_avl_rec.sty_id,
2811 TRY_ID = l_def_avl_rec.try_id,
2812 AES_ID = l_def_avl_rec.aes_id,
2813 SYT_CODE = l_def_avl_rec.syt_code,
2814 FAC_CODE = l_def_avl_rec.fac_code,
2815 FMA_ID = l_def_avl_rec.fma_id,
2816 ADVANCE_ARREARS = l_def_avl_rec.advance_arrears,
2817 POST_TO_GL = l_def_avl_rec.post_to_gl,
2818 VERSION = l_def_avl_rec.version,
2819 START_DATE = l_def_avl_rec.start_date,
2820 OBJECT_VERSION_NUMBER = l_def_avl_rec.object_version_number,
2821 MEMO_YN = l_def_avl_rec.memo_yn,
2822 PRIOR_YEAR_YN = l_def_avl_rec.prior_year_yn,
2823 DESCRIPTION = l_def_avl_rec.description,
2824 FACTORING_SYND_FLAG = l_def_avl_rec.factoring_synd_flag,
2825 END_DATE = l_def_avl_rec.end_date,
2826 ACCRUAL_YN = l_def_avl_rec.accrual_yn ,
2827 ORG_ID = l_def_avl_rec.org_id,
2828 ATTRIBUTE_CATEGORY = l_def_avl_rec.attribute_category,
2829 ATTRIBUTE1 = l_def_avl_rec.attribute1,
2830 ATTRIBUTE2 = l_def_avl_rec.attribute2,
2831 ATTRIBUTE3 = l_def_avl_rec.attribute3,
2832 ATTRIBUTE4 = l_def_avl_rec.attribute4,
2833 ATTRIBUTE5 = l_def_avl_rec.attribute5,
2834 ATTRIBUTE6 = l_def_avl_rec.attribute6,
2835 ATTRIBUTE7 = l_def_avl_rec.attribute7,
2836 ATTRIBUTE8 = l_def_avl_rec.attribute8,
2837 ATTRIBUTE9 = l_def_avl_rec.attribute9,
2838 ATTRIBUTE10 = l_def_avl_rec.attribute10,
2839 ATTRIBUTE11 = l_def_avl_rec.attribute11,
2840 ATTRIBUTE12 = l_def_avl_rec.attribute12,
2841 ATTRIBUTE13 = l_def_avl_rec.attribute13,
2842 ATTRIBUTE14 = l_def_avl_rec.attribute14,
2843 ATTRIBUTE15 = l_def_avl_rec.attribute15,
2844 CREATED_BY = l_def_avl_rec.created_by,
2845 CREATION_DATE = l_def_avl_rec.creation_date,
2846 LAST_UPDATED_BY = l_def_avl_rec.last_updated_by,
2847 LAST_UPDATE_DATE = l_def_avl_rec.last_update_date,
2848 LAST_UPDATE_LOGIN = l_def_avl_rec.last_update_login,
2849 -- Added by HKPATEL for securitization changes
2850 INV_CODE = l_def_avl_rec.inv_code
2851 WHERE ID = l_def_avl_rec.id;
2852
2853 x_avl_rec := l_def_avl_rec;
2854 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2855 EXCEPTION
2856 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2857 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2858 (
2859 l_api_name,
2860 G_PKG_NAME,
2861 'OKC_API.G_RET_STS_ERROR',
2862 x_msg_count,
2863 x_msg_data,
2864 '_PVT'
2865 );
2866 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2867 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2868 (
2869 l_api_name,
2870 G_PKG_NAME,
2871 'OKC_API.G_RET_STS_UNEXP_ERROR',
2872 x_msg_count,
2873 x_msg_data,
2874 '_PVT'
2875 );
2876 WHEN OTHERS THEN
2877 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2878 (
2879 l_api_name,
2880 G_PKG_NAME,
2881 'OTHERS',
2882 x_msg_count,
2883 x_msg_data,
2884 '_PVT'
2885 );
2886 END update_row;
2887 ---------------------------------------
2888 -- update_row for:OKL_AE_TEMPLATES_V --
2889 ---------------------------------------
2890 PROCEDURE update_row(
2891 p_api_version IN NUMBER,
2892 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2893 x_return_status OUT NOCOPY VARCHAR2,
2894 x_msg_count OUT NOCOPY NUMBER,
2895 x_msg_data OUT NOCOPY VARCHAR2,
2896 p_avlv_rec IN avlv_rec_type,
2897 x_avlv_rec OUT NOCOPY avlv_rec_type) IS
2898
2899 l_api_version CONSTANT NUMBER := 1;
2900 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2901 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2902 l_avlv_rec avlv_rec_type := p_avlv_rec;
2903 l_def_avlv_rec avlv_rec_type;
2904 l_avl_rec avl_rec_type;
2905 lx_avl_rec avl_rec_type;
2906 -------------------------------
2907 -- FUNCTION fill_who_columns --
2908 -------------------------------
2909 FUNCTION fill_who_columns (
2910 p_avlv_rec IN avlv_rec_type
2911 ) RETURN avlv_rec_type IS
2912 l_avlv_rec avlv_rec_type := p_avlv_rec;
2913 BEGIN
2914 l_avlv_rec.LAST_UPDATE_DATE := SYSDATE;
2915 l_avlv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2916 l_avlv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2917 RETURN(l_avlv_rec);
2918 END fill_who_columns;
2919 ----------------------------------
2920 -- FUNCTION populate_new_record --
2921 ----------------------------------
2922 FUNCTION populate_new_record (
2923 p_avlv_rec IN avlv_rec_type,
2924 x_avlv_rec OUT NOCOPY avlv_rec_type
2925 ) RETURN VARCHAR2 IS
2926 l_avlv_rec avlv_rec_type;
2927 l_row_notfound BOOLEAN := TRUE;
2928 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2929 BEGIN
2930 x_avlv_rec := p_avlv_rec;
2931 -- Get current database values
2932 l_avlv_rec := get_rec(p_avlv_rec, l_row_notfound);
2933 IF (l_row_notfound) THEN
2934 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2935 END IF;
2936 IF (x_avlv_rec.id = OKC_API.G_MISS_NUM)
2937 THEN
2938 x_avlv_rec.id := l_avlv_rec.id;
2939 END IF;
2940 IF (x_avlv_rec.object_version_number = OKC_API.G_MISS_NUM)
2941 THEN
2942 x_avlv_rec.object_version_number := l_avlv_rec.object_version_number;
2943 END IF;
2944 IF (x_avlv_rec.try_id = OKC_API.G_MISS_NUM)
2945 THEN
2946 x_avlv_rec.try_id := l_avlv_rec.try_id;
2947 END IF;
2948 IF (x_avlv_rec.aes_id = OKC_API.G_MISS_NUM)
2949 THEN
2950 x_avlv_rec.aes_id := l_avlv_rec.aes_id;
2951 END IF;
2952 IF (x_avlv_rec.sty_id = OKC_API.G_MISS_NUM)
2953 THEN
2954 x_avlv_rec.sty_id := l_avlv_rec.sty_id;
2955 END IF;
2956 IF (x_avlv_rec.fma_id = OKC_API.G_MISS_NUM)
2957 THEN
2958 x_avlv_rec.fma_id := l_avlv_rec.fma_id;
2959 END IF;
2960 IF (x_avlv_rec.set_of_books_id = OKC_API.G_MISS_NUM)
2961 THEN
2962 x_avlv_rec.set_of_books_id := l_avlv_rec.set_of_books_id;
2963 END IF;
2964 IF (x_avlv_rec.fac_code = OKC_API.G_MISS_CHAR)
2965 THEN
2966 x_avlv_rec.fac_code := l_avlv_rec.fac_code;
2967 END IF;
2968 IF (x_avlv_rec.syt_code = OKC_API.G_MISS_CHAR)
2969 THEN
2970 x_avlv_rec.syt_code := l_avlv_rec.syt_code;
2971 END IF;
2972 IF (x_avlv_rec.post_to_gl = OKC_API.G_MISS_CHAR)
2973 THEN
2974 x_avlv_rec.post_to_gl := l_avlv_rec.post_to_gl;
2975 END IF;
2976 IF (x_avlv_rec.advance_arrears = OKC_API.G_MISS_CHAR)
2977 THEN
2978 x_avlv_rec.advance_arrears := l_avlv_rec.advance_arrears;
2979 END IF;
2980 IF (x_avlv_rec.memo_yn = OKC_API.G_MISS_CHAR)
2981 THEN
2982 x_avlv_rec.memo_yn := l_avlv_rec.memo_yn;
2983 END IF;
2984 IF (x_avlv_rec.prior_year_yn = OKC_API.G_MISS_CHAR)
2985 THEN
2986 x_avlv_rec.prior_year_yn := l_avlv_rec.prior_year_yn;
2987 END IF;
2988 IF (x_avlv_rec.name = OKC_API.G_MISS_CHAR)
2989 THEN
2990 x_avlv_rec.name := l_avlv_rec.name;
2991 END IF;
2992 IF (x_avlv_rec.description = OKC_API.G_MISS_CHAR)
2993 THEN
2994 x_avlv_rec.description := l_avlv_rec.description;
2995 END IF;
2996 IF (x_avlv_rec.version = OKC_API.G_MISS_CHAR)
2997 THEN
2998 x_avlv_rec.version := l_avlv_rec.version;
2999 END IF;
3000 IF (x_avlv_rec.factoring_synd_flag = OKC_API.G_MISS_CHAR)
3001 THEN
3002 x_avlv_rec.factoring_synd_flag := l_avlv_rec.factoring_synd_flag;
3003 END IF;
3004 IF (x_avlv_rec.start_date = OKC_API.G_MISS_DATE)
3005 THEN
3006 x_avlv_rec.start_date := l_avlv_rec.start_date;
3007 END IF;
3008 IF (x_avlv_rec.end_date = OKC_API.G_MISS_DATE)
3009 THEN
3010 x_avlv_rec.end_date := l_avlv_rec.end_date;
3011 END IF;
3012 IF (x_avlv_rec.accrual_yn = OKC_API.G_MISS_CHAR)
3013 THEN
3014 x_avlv_rec.accrual_yn := l_avlv_rec.accrual_yn;
3015 END IF;
3016 IF (x_avlv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3017 THEN
3018 x_avlv_rec.attribute_category := l_avlv_rec.attribute_category;
3019 END IF;
3020 IF (x_avlv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3021 THEN
3022 x_avlv_rec.attribute1 := l_avlv_rec.attribute1;
3023 END IF;
3024 IF (x_avlv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3025 THEN
3026 x_avlv_rec.attribute2 := l_avlv_rec.attribute2;
3027 END IF;
3028 IF (x_avlv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3029 THEN
3030 x_avlv_rec.attribute3 := l_avlv_rec.attribute3;
3031 END IF;
3032 IF (x_avlv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3033 THEN
3034 x_avlv_rec.attribute4 := l_avlv_rec.attribute4;
3035 END IF;
3036 IF (x_avlv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3037 THEN
3038 x_avlv_rec.attribute5 := l_avlv_rec.attribute5;
3039 END IF;
3040 IF (x_avlv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3041 THEN
3042 x_avlv_rec.attribute6 := l_avlv_rec.attribute6;
3043 END IF;
3044 IF (x_avlv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3045 THEN
3046 x_avlv_rec.attribute7 := l_avlv_rec.attribute7;
3047 END IF;
3048 IF (x_avlv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3049 THEN
3050 x_avlv_rec.attribute8 := l_avlv_rec.attribute8;
3051 END IF;
3052 IF (x_avlv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3053 THEN
3054 x_avlv_rec.attribute9 := l_avlv_rec.attribute9;
3055 END IF;
3056 IF (x_avlv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3057 THEN
3058 x_avlv_rec.attribute10 := l_avlv_rec.attribute10;
3059 END IF;
3060 IF (x_avlv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3061 THEN
3062 x_avlv_rec.attribute11 := l_avlv_rec.attribute11;
3063 END IF;
3064 IF (x_avlv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3065 THEN
3066 x_avlv_rec.attribute12 := l_avlv_rec.attribute12;
3067 END IF;
3068 IF (x_avlv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3069 THEN
3070 x_avlv_rec.attribute13 := l_avlv_rec.attribute13;
3071 END IF;
3072 IF (x_avlv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3073 THEN
3074 x_avlv_rec.attribute14 := l_avlv_rec.attribute14;
3075 END IF;
3076 IF (x_avlv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3077 THEN
3078 x_avlv_rec.attribute15 := l_avlv_rec.attribute15;
3079 END IF;
3080 IF (x_avlv_rec.org_id = OKC_API.G_MISS_NUM)
3081 THEN
3082 x_avlv_rec.org_id := l_avlv_rec.org_id;
3083 END IF;
3084 IF (x_avlv_rec.created_by = OKC_API.G_MISS_NUM)
3085 THEN
3086 x_avlv_rec.created_by := l_avlv_rec.created_by;
3087 END IF;
3088 IF (x_avlv_rec.creation_date = OKC_API.G_MISS_DATE)
3089 THEN
3090 x_avlv_rec.creation_date := l_avlv_rec.creation_date;
3091 END IF;
3092 IF (x_avlv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3093 THEN
3094 x_avlv_rec.last_updated_by := l_avlv_rec.last_updated_by;
3095 END IF;
3096 IF (x_avlv_rec.last_update_date = OKC_API.G_MISS_DATE)
3097 THEN
3098 x_avlv_rec.last_update_date := l_avlv_rec.last_update_date;
3099 END IF;
3100 IF (x_avlv_rec.last_update_login = OKC_API.G_MISS_NUM)
3101 THEN
3102 x_avlv_rec.last_update_login := l_avlv_rec.last_update_login;
3103 END IF;
3104 -- Added by HKPATEL for securitization changes
3105 IF (x_avlv_rec.inv_code = OKC_API.G_MISS_CHAR)
3106 THEN
3107 x_avlv_rec.inv_code := l_avlv_rec.inv_code;
3108 END IF;
3109 RETURN(l_return_status);
3110 END populate_new_record;
3111 -------------------------------------------
3112 -- Set_Attributes for:OKL_AE_TEMPLATES_V --
3113 -------------------------------------------
3114 FUNCTION Set_Attributes (
3115 p_avlv_rec IN avlv_rec_type,
3116 x_avlv_rec OUT NOCOPY avlv_rec_type
3117 ) RETURN VARCHAR2 IS
3118 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3119 BEGIN
3120 x_avlv_rec := p_avlv_rec;
3121 RETURN(l_return_status);
3122 END Set_Attributes;
3123 BEGIN
3124 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3125 G_PKG_NAME,
3126 p_init_msg_list,
3127 l_api_version,
3128 p_api_version,
3129 '_PVT',
3130 x_return_status);
3131
3132 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3133 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3134 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3135 RAISE OKC_API.G_EXCEPTION_ERROR;
3136 END IF;
3137 --- Setting item attributes
3138 l_return_status := Set_Attributes(
3139 p_avlv_rec, -- IN
3140 l_avlv_rec); -- OUT
3141 --- If any errors happen abort API
3142 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3143 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3144 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3145 RAISE OKC_API.G_EXCEPTION_ERROR;
3146 END IF;
3147 -- HKPATEL added code for securitization
3148
3149 IF (l_avlv_rec.factoring_synd_flag = 'FACTORING') THEN
3150 l_avlv_rec.INV_CODE := NULL;
3151 l_avlv_rec.SYT_CODE := NULL;
3152 ELSIF (l_avlv_rec.factoring_synd_flag = 'SYNDICATION') THEN
3153 l_avlv_rec.INV_CODE := NULL;
3154 l_avlv_rec.FAC_CODE := NULL;
3155 ELSIF (l_avlv_rec.factoring_synd_flag = 'INVESTOR') THEN
3156 l_avlv_rec.SYT_CODE := NULL;
3157 l_avlv_rec.FAC_CODE := NULL;
3158 ELSE
3159 l_avlv_rec.SYT_CODE := NULL;
3160 l_avlv_rec.FAC_CODE := NULL;
3161 l_avlv_rec.INV_CODE := NULL;
3162 END IF;
3163
3164 l_return_status := populate_new_record(l_avlv_rec, l_def_avlv_rec);
3165 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3166 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3167 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3168 RAISE OKC_API.G_EXCEPTION_ERROR;
3169 END IF;
3170 l_def_avlv_rec := fill_who_columns(l_def_avlv_rec);
3171 --- Validate all non-missing attributes (Item Level Validation)
3172 l_return_status := Validate_Attributes(l_def_avlv_rec);
3173 --- If any errors happen abort API
3174 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3175 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3176 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3177 RAISE OKC_API.G_EXCEPTION_ERROR;
3178 END IF;
3179 l_return_status := Validate_Record(l_def_avlv_rec);
3180 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3181 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3182 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3183 RAISE OKC_API.G_EXCEPTION_ERROR;
3184 END IF;
3185
3186 --------------------------------------
3187 -- Move VIEW record to "Child" records
3188 --------------------------------------
3189 migrate(l_def_avlv_rec, l_avl_rec);
3190 --------------------------------------------
3191 -- Call the UPDATE_ROW for each child record
3192 --------------------------------------------
3193 update_row(
3194 p_init_msg_list,
3195 x_return_status,
3196 x_msg_count,
3197 x_msg_data,
3198 l_avl_rec,
3199 lx_avl_rec
3200 );
3201 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3202 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3203 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3204 RAISE OKC_API.G_EXCEPTION_ERROR;
3205 END IF;
3206 migrate(lx_avl_rec, l_def_avlv_rec);
3207 x_avlv_rec := l_def_avlv_rec;
3208 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3209 EXCEPTION
3210 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3211 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3212 (
3213 l_api_name,
3214 G_PKG_NAME,
3215 'OKC_API.G_RET_STS_ERROR',
3216 x_msg_count,
3217 x_msg_data,
3218 '_PVT'
3219 );
3220 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3221 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3222 (
3223 l_api_name,
3224 G_PKG_NAME,
3225 'OKC_API.G_RET_STS_UNEXP_ERROR',
3226 x_msg_count,
3227 x_msg_data,
3228 '_PVT'
3229 );
3230 WHEN OTHERS THEN
3231 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3232 (
3233 l_api_name,
3234 G_PKG_NAME,
3235 'OTHERS',
3236 x_msg_count,
3237 x_msg_data,
3238 '_PVT'
3239 );
3240 END update_row;
3241 ----------------------------------------
3242 -- PL/SQL TBL update_row for:AVLV_TBL --
3243 ----------------------------------------
3244 PROCEDURE update_row(
3245 p_api_version IN NUMBER,
3246 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3247 x_return_status OUT NOCOPY VARCHAR2,
3248 x_msg_count OUT NOCOPY NUMBER,
3249 x_msg_data OUT NOCOPY VARCHAR2,
3250 p_avlv_tbl IN avlv_tbl_type,
3251 x_avlv_tbl OUT NOCOPY avlv_tbl_type) IS
3252
3253 l_api_version CONSTANT NUMBER := 1;
3254 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3255 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3256 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3257 i NUMBER := 0;
3258 BEGIN
3259 OKC_API.init_msg_list(p_init_msg_list);
3260 -- Make sure PL/SQL table has records in it before passing
3261 IF (p_avlv_tbl.COUNT > 0) THEN
3262 i := p_avlv_tbl.FIRST;
3263 LOOP
3264 update_row (
3265 p_api_version => p_api_version,
3266 p_init_msg_list => OKC_API.G_FALSE,
3267 x_return_status => x_return_status,
3268 x_msg_count => x_msg_count,
3269 x_msg_data => x_msg_data,
3270 p_avlv_rec => p_avlv_tbl(i),
3271 x_avlv_rec => x_avlv_tbl(i));
3272 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3273 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
3274 l_overall_status := x_return_status;
3275 END IF;
3276 END IF;
3277 EXIT WHEN (i = p_avlv_tbl.LAST);
3278 i := p_avlv_tbl.NEXT(i);
3279 END LOOP;
3280 x_return_status := l_overall_status;
3281 END IF;
3282 EXCEPTION
3283 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3284 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3285 (
3286 l_api_name,
3287 G_PKG_NAME,
3288 'OKC_API.G_RET_STS_ERROR',
3289 x_msg_count,
3290 x_msg_data,
3291 '_PVT'
3292 );
3293 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3294 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3295 (
3296 l_api_name,
3297 G_PKG_NAME,
3298 'OKC_API.G_RET_STS_UNEXP_ERROR',
3299 x_msg_count,
3300 x_msg_data,
3301 '_PVT'
3302 );
3303 WHEN OTHERS THEN
3304 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3305 (
3306 l_api_name,
3307 G_PKG_NAME,
3308 'OTHERS',
3309 x_msg_count,
3310 x_msg_data,
3311 '_PVT'
3312 );
3313 END update_row;
3314
3315 ---------------------------------------------------------------------------
3316 -- PROCEDURE delete_row
3317 ---------------------------------------------------------------------------
3318 -------------------------------------
3319 -- delete_row for:OKL_AE_TEMPLATES --
3320 -------------------------------------
3321 PROCEDURE delete_row(
3322 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3323 x_return_status OUT NOCOPY VARCHAR2,
3324 x_msg_count OUT NOCOPY NUMBER,
3325 x_msg_data OUT NOCOPY VARCHAR2,
3326 p_avl_rec IN avl_rec_type) IS
3327
3328 l_api_version CONSTANT NUMBER := 1;
3329 l_api_name CONSTANT VARCHAR2(30) := 'TEMPLATES_delete_row';
3330 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3331 l_avl_rec avl_rec_type:= p_avl_rec;
3332 l_row_notfound BOOLEAN := TRUE;
3333 BEGIN
3334 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3335 p_init_msg_list,
3336 '_PVT',
3337 x_return_status);
3338 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3339 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3340 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3341 RAISE OKC_API.G_EXCEPTION_ERROR;
3342 END IF;
3343 DELETE FROM OKL_AE_TEMPLATES
3344 WHERE ID = l_avl_rec.id;
3345
3346 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3347 EXCEPTION
3348 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3349 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3350 (
3351 l_api_name,
3352 G_PKG_NAME,
3353 'OKC_API.G_RET_STS_ERROR',
3354 x_msg_count,
3355 x_msg_data,
3356 '_PVT'
3357 );
3358 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3359 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3360 (
3361 l_api_name,
3362 G_PKG_NAME,
3363 'OKC_API.G_RET_STS_UNEXP_ERROR',
3364 x_msg_count,
3365 x_msg_data,
3366 '_PVT'
3367 );
3368 WHEN OTHERS THEN
3369 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3370 (
3371 l_api_name,
3372 G_PKG_NAME,
3373 'OTHERS',
3374 x_msg_count,
3375 x_msg_data,
3376 '_PVT'
3377 );
3378 END delete_row;
3379 ---------------------------------------
3380 -- delete_row for:OKL_AE_TEMPLATES_V --
3381 ---------------------------------------
3382 PROCEDURE delete_row(
3383 p_api_version IN NUMBER,
3384 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3385 x_return_status OUT NOCOPY VARCHAR2,
3386 x_msg_count OUT NOCOPY NUMBER,
3387 x_msg_data OUT NOCOPY VARCHAR2,
3388 p_avlv_rec IN avlv_rec_type) IS
3389
3390 l_api_version CONSTANT NUMBER := 1;
3391 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3392 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3393 l_avlv_rec avlv_rec_type := p_avlv_rec;
3394 l_avl_rec avl_rec_type;
3395 BEGIN
3396 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3397 G_PKG_NAME,
3398 p_init_msg_list,
3399 l_api_version,
3400 p_api_version,
3401 '_PVT',
3402 x_return_status);
3403 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3404 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3405 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3406 RAISE OKC_API.G_EXCEPTION_ERROR;
3407 END IF;
3408 --------------------------------------
3409 -- Move VIEW record to "Child" records
3410 --------------------------------------
3411 migrate(l_avlv_rec, l_avl_rec);
3412 --------------------------------------------
3413 -- Call the DELETE_ROW for each child record
3414 --------------------------------------------
3415 delete_row(
3416 p_init_msg_list,
3417 x_return_status,
3418 x_msg_count,
3419 x_msg_data,
3420 l_avl_rec
3421 );
3422 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3423 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3424 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3425 RAISE OKC_API.G_EXCEPTION_ERROR;
3426 END IF;
3427 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3428 EXCEPTION
3429 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3430 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3431 (
3432 l_api_name,
3433 G_PKG_NAME,
3434 'OKC_API.G_RET_STS_ERROR',
3435 x_msg_count,
3436 x_msg_data,
3437 '_PVT'
3438 );
3439 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3440 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3441 (
3442 l_api_name,
3443 G_PKG_NAME,
3444 'OKC_API.G_RET_STS_UNEXP_ERROR',
3445 x_msg_count,
3446 x_msg_data,
3447 '_PVT'
3448 );
3449 WHEN OTHERS THEN
3450 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3451 (
3452 l_api_name,
3453 G_PKG_NAME,
3454 'OTHERS',
3455 x_msg_count,
3456 x_msg_data,
3457 '_PVT'
3458 );
3459 END delete_row;
3460 ----------------------------------------
3461 -- PL/SQL TBL delete_row for:AVLV_TBL --
3462 ----------------------------------------
3463 PROCEDURE delete_row(
3464 p_api_version IN NUMBER,
3465 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3466 x_return_status OUT NOCOPY VARCHAR2,
3467 x_msg_count OUT NOCOPY NUMBER,
3468 x_msg_data OUT NOCOPY VARCHAR2,
3469 p_avlv_tbl IN avlv_tbl_type) IS
3470
3471 l_api_version CONSTANT NUMBER := 1;
3472 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3473 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3474 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3475 i NUMBER := 0;
3476 BEGIN
3477 OKC_API.init_msg_list(p_init_msg_list);
3478 -- Make sure PL/SQL table has records in it before passing
3479 IF (p_avlv_tbl.COUNT > 0) THEN
3480 i := p_avlv_tbl.FIRST;
3481 LOOP
3482 delete_row (
3483 p_api_version => p_api_version,
3484 p_init_msg_list => OKC_API.G_FALSE,
3485 x_return_status => x_return_status,
3486 x_msg_count => x_msg_count,
3487 x_msg_data => x_msg_data,
3488 p_avlv_rec => p_avlv_tbl(i));
3489 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3490 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
3491 l_overall_status := x_return_status;
3492 END IF;
3493 END IF;
3494 EXIT WHEN (i = p_avlv_tbl.LAST);
3495 i := p_avlv_tbl.NEXT(i);
3496 END LOOP;
3497 x_return_status := l_overall_status;
3498 END IF;
3499 EXCEPTION
3500 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3501 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3502 (
3503 l_api_name,
3504 G_PKG_NAME,
3505 'OKC_API.G_RET_STS_ERROR',
3506 x_msg_count,
3507 x_msg_data,
3508 '_PVT'
3509 );
3510 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3511 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3512 (
3513 l_api_name,
3514 G_PKG_NAME,
3515 'OKC_API.G_RET_STS_UNEXP_ERROR',
3516 x_msg_count,
3517 x_msg_data,
3518 '_PVT'
3519 );
3520 WHEN OTHERS THEN
3521 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3522 (
3523 l_api_name,
3524 G_PKG_NAME,
3525 'OTHERS',
3526 x_msg_count,
3527 x_msg_data,
3528 '_PVT'
3529 );
3530 END delete_row;
3531 END OKL_AVL_PVT;