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