[Home] [Help]
PACKAGE BODY: APPS.OKL_GTS_PVT
Source
1 PACKAGE BODY OKL_GTS_PVT AS
2 /* $Header: OKLSGTSB.pls 120.6 2007/10/15 16:44:33 dpsingh noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- FUNCTION get_rec for: OKL_ST_GEN_TMPT_SETS
13 ---------------------------------------------------------------------------
14 FUNCTION get_rec (
15 p_gts_rec IN gts_rec_type,
16 x_no_data_found OUT NOCOPY BOOLEAN
17 ) RETURN gts_rec_type IS
18 CURSOR okl_st_gen_tmpt_sets_pk_csr (p_id IN NUMBER) IS
19 SELECT ID
20 ,OBJECT_VERSION_NUMBER
21 ,NAME
22 ,DESCRIPTION
23 ,PRODUCT_TYPE
24 ,TAX_OWNER
25 ,DEAL_TYPE
26 ,PRICING_ENGINE
27 ,ORG_ID
28 ,CREATED_BY
29 ,CREATION_DATE
30 ,LAST_UPDATED_BY
31 ,LAST_UPDATE_DATE
32 ,LAST_UPDATE_LOGIN
33 ,INTEREST_CALC_METH_CODE
34 ,REVENUE_RECOG_METH_CODE
35 ,DAYS_IN_MONTH_CODE
36 ,DAYS_IN_YR_CODE
37 ,ISG_ARREARS_PAY_DATES_OPTION
38 FROM OKL_ST_GEN_TMPT_SETS
39 WHERE OKL_ST_GEN_TMPT_SETS.id = p_id;
40
41 l_okl_st_gen_tmpt_sets_pk okl_st_gen_tmpt_sets_pk_csr%ROWTYPE;
42 l_gts_rec gts_rec_type;
43 BEGIN
44 x_no_data_found := TRUE;
45
46 -- Get current database values
47 OPEN okl_st_gen_tmpt_sets_pk_csr (p_gts_rec.id);
48
49 FETCH okl_st_gen_tmpt_sets_pk_csr INTO
50 l_gts_rec.id
51 ,l_gts_rec.object_version_number
52 ,l_gts_rec.name
53 ,l_gts_rec.description
54 ,l_gts_rec.product_type
55 ,l_gts_rec.tax_owner
56 ,l_gts_rec.deal_type
57 ,l_gts_rec.pricing_engine
58 ,l_gts_rec.org_id
59 ,l_gts_rec.created_by
60 ,l_gts_rec.creation_date
61 ,l_gts_rec.last_updated_by
62 ,l_gts_rec.last_update_date
63 ,l_gts_rec.last_update_login
64 ,l_gts_rec.interest_calc_meth_code
65 ,l_gts_rec.revenue_recog_meth_code
66 ,l_gts_rec.days_in_month_code
67 ,l_gts_rec.days_in_yr_code
68 ,l_gts_rec.isg_arrears_pay_dates_option;
69
70
71 x_no_data_found := okl_st_gen_tmpt_sets_pk_csr%NOTFOUND;
72 CLOSE okl_st_gen_tmpt_sets_pk_csr;
73
74 RETURN(l_gts_rec);
75
76 END get_rec;
77
78 FUNCTION get_rec (
79 p_gts_rec IN gts_rec_type
80 ) RETURN gts_rec_type IS
81 l_row_notfound BOOLEAN := TRUE;
82 BEGIN
83 RETURN(get_rec(p_gts_rec, l_row_notfound));
84 END get_rec;
85 ---------------------------------------------------------------------------
86 -- FUNCTION get_rec for: OKL_SYS_ACCT_OPTS_V
87 ---------------------------------------------------------------------------
88 FUNCTION get_rec (
89 p_gtsv_rec IN gtsv_rec_type,
90 x_no_data_found OUT NOCOPY BOOLEAN
91 ) RETURN gtsv_rec_type IS
92 CURSOR okl_gtsv_pk_csr (p_id IN NUMBER) IS
93 SELECT ID
94 ,OBJECT_VERSION_NUMBER
95 ,NAME
96 ,DESCRIPTION
97 ,PRODUCT_TYPE
98 ,TAX_OWNER
99 ,DEAL_TYPE
100 ,PRICING_ENGINE
101 ,ORG_ID
102 ,CREATED_BY
103 ,CREATION_DATE
104 ,LAST_UPDATED_BY
105 ,LAST_UPDATE_DATE
106 ,LAST_UPDATE_LOGIN
107 ,INTEREST_CALC_METH_CODE
108 ,REVENUE_RECOG_METH_CODE
109 ,DAYS_IN_MONTH_CODE
110 ,DAYS_IN_YR_CODE
111 ,ISG_ARREARS_PAY_DATES_OPTION
112 FROM OKL_ST_GEN_TMPT_SETS
113 WHERE OKL_ST_GEN_TMPT_SETS.ID = p_id;
114
115 l_okl_gtsv_pk okl_gtsv_pk_csr%ROWTYPE;
116 l_gtsv_rec gtsv_rec_type;
117 BEGIN
118 x_no_data_found := TRUE;
119 -- Get current database values
120 OPEN okl_gtsv_pk_csr (p_gtsv_rec.id);
121 FETCH okl_gtsv_pk_csr INTO
122 l_gtsv_rec.id
123 ,l_gtsv_rec.object_version_number
124 ,l_gtsv_rec.name
125 ,l_gtsv_rec.description
126 ,l_gtsv_rec.product_type
127 ,l_gtsv_rec.tax_owner
128 ,l_gtsv_rec.deal_type
129 ,l_gtsv_rec.pricing_engine
130 ,l_gtsv_rec.org_id
131 ,l_gtsv_rec.created_by
132 ,l_gtsv_rec.creation_date
133 ,l_gtsv_rec.last_updated_by
134 ,l_gtsv_rec.last_update_date
135 ,l_gtsv_rec.last_update_login
136 ,l_gtsv_rec.interest_calc_meth_code
137 ,l_gtsv_rec.revenue_recog_meth_code
138 ,l_gtsv_rec.days_in_month_code
139 ,l_gtsv_rec.days_in_yr_code
140 ,l_gtsv_rec.isg_arrears_pay_dates_option;
141
142 x_no_data_found := okl_gtsv_pk_csr%NOTFOUND;
143 CLOSE okl_gtsv_pk_csr;
144 RETURN(l_gtsv_rec);
145 END get_rec;
146
147 FUNCTION get_rec (
148 p_gtsv_rec IN gtsv_rec_type
149 ) RETURN gtsv_rec_type IS
150 l_row_notfound BOOLEAN := TRUE;
151 BEGIN
152 RETURN(get_rec(p_gtsv_rec, l_row_notfound));
153 END get_rec;
154
155 ---------------------------------------------------------------------------
156 -- PROCEDURE validate_arrears_pay_dt_opt
157 ---------------------------------------------------------------------------
158
159 PROCEDURE validate_arrears_pay_dt_opt(
160 p_gtsv_rec IN gtsv_rec_type,
161 x_return_status OUT NOCOPY VARCHAR2
162 ) IS
163 l_dummy varchar2(1);
164
165 BEGIN
166 -- initialize return status
167 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
168
169 IF (p_gtsv_rec.ISG_ARREARS_PAY_DATES_OPTION <> Okl_Api.G_MISS_CHAR ) AND
170 (p_gtsv_rec.ISG_ARREARS_PAY_DATES_OPTION IS NOT NULL) THEN
171
172 l_dummy
173 := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_ISG_ARREAR_PAY_DATE_OPTION',
174 p_lookup_code => p_gtsv_rec.isg_arrears_pay_dates_option);
175
176 IF (l_dummy = Okl_Api.G_FALSE) THEN
177 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
178 ,p_msg_name => g_invalid_value
179 ,p_token1 => g_col_name_token
180 ,p_token1_value => 'ISG_ARREARS_PAY_DATES_OPTION');
181 x_return_status := Okl_Api.G_RET_STS_ERROR;
182 RAISE G_EXCEPTION_HALT_VALIDATION;
183 END IF;
184
185 END IF;
186
187 EXCEPTION
188 WHEN G_EXCEPTION_HALT_VALIDATION THEN
189 -- no processing necessary; validation can continue
190 -- with the next column
191 NULL;
192
193 WHEN OTHERS THEN
194 -- store SQL error message on message stack for caller
195 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME
196 ,p_msg_name => G_UNEXPECTED_ERROR
197 ,p_token1 => G_SQLCODE_TOKEN
198 ,p_token1_value => SQLCODE
199 ,p_token2 => G_SQLERRM_TOKEN
200 ,p_token2_value => SQLERRM);
201
202 -- notify caller of an UNEXPECTED error
203 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
204
205 END validate_arrears_pay_dt_opt;
206
207 ---------------------------------------------------------------------------
208 -- PROCEDURE validate_id
209 ---------------------------------------------------------------------------
210 -- Start of comments
211 --
212 -- Procedure Name : Validate_Id
213 -- Description :
214 -- Business Rules :
215 -- Parameters :
216 -- Version : 1.0
217 -- End of comments
218 ---------------------------------------------------------------------------
219 PROCEDURE Validate_Id(
220 p_gtsv_rec IN gtsv_rec_type,
221 x_return_status OUT NOCOPY VARCHAR2
222 ) IS
223
224 BEGIN
225 -- initialize return status
226 x_return_status := okl_api.G_RET_STS_SUCCESS;
227
228 IF p_gtsv_rec.id = okl_api.G_MISS_NUM OR
229 p_gtsv_rec.id IS NULL
230 THEN
231 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
232 x_return_status := okl_api.G_RET_STS_ERROR;
233 RAISE G_EXCEPTION_HALT_VALIDATION;
234 END IF;
235
236 EXCEPTION
237 WHEN G_EXCEPTION_HALT_VALIDATION THEN
238 -- no processing necessary; validation can continue
239 -- with the next column
240 NULL;
241
242 WHEN OTHERS THEN
243 -- store SQL error message on message stack for caller
244 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
245 ,p_msg_name => G_UNEXPECTED_ERROR
246 ,p_token1 => G_SQLCODE_TOKEN
247 ,p_token1_value => SQLCODE
248 ,p_token2 => G_SQLERRM_TOKEN
249 ,p_token2_value => SQLERRM);
250
251 -- notify caller of an UNEXPECTED error
252 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
253
254 END Validate_Id;
255
256 ---------------------------------------------------------------------------
257 -- PROCEDURE validate_object_version_number
258 ---------------------------------------------------------------------------
259 -- Start of comments
260 --
261 -- Procedure Name : validate_object_version_number
262 -- Description :
263 -- Business Rules :
264 -- Parameters :
265 -- Version : 1.0
266 -- End of comments
267 ---------------------------------------------------------------------------
268 PROCEDURE Validate_Object_Version_Number(
269 p_gtsv_rec IN gtsv_rec_type,
270 x_return_status OUT NOCOPY VARCHAR2
271 ) IS
272
273 BEGIN
274
275 -- initialize return status
276 x_return_status := okl_api.G_RET_STS_SUCCESS;
277
278 IF p_gtsv_rec.object_version_number = okl_api.G_MISS_NUM OR
279 p_gtsv_rec.object_version_number IS NULL
280 THEN
281 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
282 x_return_status := okl_api.G_RET_STS_ERROR;
283 RAISE G_EXCEPTION_HALT_VALIDATION;
284 END IF;
285
286 EXCEPTION
287 WHEN G_EXCEPTION_HALT_VALIDATION THEN
288 -- no processing necessary; validation can continue
289 -- with the next column
290 NULL;
291
292 WHEN OTHERS THEN
293 -- store SQL error message on message stack for caller
294 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
295 ,p_msg_name => G_UNEXPECTED_ERROR
296 ,p_token1 => G_SQLCODE_TOKEN
297 ,p_token1_value => SQLCODE
298 ,p_token2 => G_SQLERRM_TOKEN
299 ,p_token2_value => SQLERRM);
300
301 -- notify caller of an UNEXPECTED error
302 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
303
304 END Validate_Object_Version_Number;
305
306 ---------------------------------------------------------------------------
307 -- PROCEDURE Validate_name
308 ---------------------------------------------------------------------------
309 -- Start of comments
310 --
311 -- Procedure Name : Validate_name
312 -- Description :
313 -- Business Rules :
314 -- Parameters :
315 -- Version : 1.0
316 -- End of comments
317 ---------------------------------------------------------------------------
318 PROCEDURE Validate_name(
319 p_gtsv_rec IN gtsv_rec_type,
320 x_return_status OUT NOCOPY VARCHAR2
321 ) IS
322 CURSOR okl_st_gen_tempt_sets_csr(p_name OKL_ST_GEN_TMPT_SETS.name%type) IS
323 SELECT ID
324 ,name tmpt_set_name
325 FROM OKL_ST_GEN_TMPT_SETS gts
326 WHERE UPPER(gts.name) = upper( p_name );
327 l_name_in_use VARCHAR2(1) := okl_api.G_FALSE;
328
329 BEGIN
330 -- initialize return status
331 x_return_status := okl_api.G_RET_STS_SUCCESS;
332
333 IF p_gtsv_rec.name = okl_api.G_MISS_CHAR OR
334 p_gtsv_rec.name IS NULL
335 THEN
336 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'TEMPLATE_SET_NAME');
337 x_return_status := okl_api.G_RET_STS_ERROR;
338 RAISE G_EXCEPTION_HALT_VALIDATION;
339 ELSE
340 FOR gtsv_temp_rec In okl_st_gen_tempt_sets_csr(p_gtsv_rec.name)
341 LOOP
342 IF( gtsv_temp_rec.id <> p_gtsv_rec.id )
343 THEN
344 l_name_in_use := Okl_Api.G_TRUE;
345 END IF;
346 END LOOP;
347 IF ( l_name_in_use = Okl_Api.G_TRUE )
348 THEN
349 okl_api.set_message(G_APP_NAME,
350 'OKL_NAME_VERSION_NOT_UNIQUE'
351 );
352 x_return_status := okl_api.G_RET_STS_ERROR;
353 RAISE G_EXCEPTION_HALT_VALIDATION;
354 END IF;
355
356 END IF;
357
358 EXCEPTION
359 WHEN G_EXCEPTION_HALT_VALIDATION THEN
360 -- no processing necessary; validation can continue
361 -- with the next column
362 NULL;
363
364 WHEN OTHERS THEN
365 -- store SQL error message on message stack for caller
366 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
367 ,p_msg_name => G_UNEXPECTED_ERROR
368 ,p_token1 => G_SQLCODE_TOKEN
369 ,p_token1_value => SQLCODE
370 ,p_token2 => G_SQLERRM_TOKEN
371 ,p_token2_value => SQLERRM);
372
373 -- notify caller of an UNEXPECTED error
374 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
375
376 END Validate_name;
377
378
379 ---------------------------------------------------------------------------
380 -- PROCEDURE validate_product_type
381 ---------------------------------------------------------------------------
382 -- Start of comments
383 --
384 -- Procedure Name : validate_tax_owner
385 -- Description :
386 -- Business Rules :
387 -- Parameters :
388 -- Version : 1.0
389 -- End of comments
390 ---------------------------------------------------------------------------
391 PROCEDURE validate_product_type(
392 p_gtsv_rec IN gtsv_rec_type,
393 x_return_status OUT NOCOPY VARCHAR2
394 ) IS
395 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
396
397 BEGIN
398 -- initialize return status
399 x_return_status := okl_api.G_RET_STS_SUCCESS;
400
401 -- check for data before processing
402 IF (p_gtsv_rec.product_type IS NOT NULL) AND
403 (p_gtsv_rec.product_type <> okl_api.G_MISS_CHAR) THEN
404
405 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_STREAM_PRODUCT_TYPE',
406 p_lookup_code => p_gtsv_rec.product_type);
407
408
409 IF (l_dummy = okl_api.g_false) THEN
410 okl_api.SET_MESSAGE(p_app_name => g_app_name,
411 p_msg_name => g_invalid_value,
412 p_token1 => g_col_name_token,
413 p_token1_value => 'PRODUCT_TYPE');
414 x_return_status := okl_api.G_RET_STS_ERROR;
415 RAISE G_EXCEPTION_HALT_VALIDATION;
416 END IF;
417
418 END IF;
419
420 EXCEPTION
421 WHEN G_EXCEPTION_HALT_VALIDATION THEN
422 -- no processing necessary; validation can continue
423 -- with the next column
424 NULL;
425
426 WHEN OTHERS THEN
427 -- store SQL error message on message stack for caller
428 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
429 ,p_msg_name => G_UNEXPECTED_ERROR
430 ,p_token1 => G_SQLCODE_TOKEN
431 ,p_token1_value => SQLCODE
432 ,p_token2 => G_SQLERRM_TOKEN
433 ,p_token2_value => SQLERRM);
434
435 -- notify caller of an UNEXPECTED error
436 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
437
438 END validate_product_type;
439 ---------------------------------------------------------------------------
440 -- PROCEDURE validate_tax_owner
441 ---------------------------------------------------------------------------
442 -- Start of comments
443 --
444 -- Procedure Name : validate_tax_owner
445 -- Description :
446 -- Business Rules :
447 -- Parameters :
448 -- Version : 1.0
449 -- End of comments
450 ---------------------------------------------------------------------------
451 PROCEDURE validate_tax_owner(
452 p_gtsv_rec IN gtsv_rec_type,
453 x_return_status OUT NOCOPY VARCHAR2
454 ) IS
455 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
456
457 BEGIN
458 -- initialize return status
459 x_return_status := okl_api.G_RET_STS_SUCCESS;
460
461 -- check for data before processing
462 IF (p_gtsv_rec.tax_owner IS NOT NULL) AND
463 (p_gtsv_rec.tax_owner <> okl_api.G_MISS_CHAR) THEN
464
465 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_TAX_OWNER',
466 p_lookup_code => p_gtsv_rec.tax_owner);
467
468
469 IF (l_dummy = okl_api.g_false) THEN
470 okl_api.SET_MESSAGE(p_app_name => g_app_name,
471 p_msg_name => g_invalid_value,
472 p_token1 => g_col_name_token,
473 p_token1_value => 'TAX_OWNER');
474 x_return_status := okl_api.G_RET_STS_ERROR;
475 RAISE G_EXCEPTION_HALT_VALIDATION;
476 END IF;
477
478 END IF;
479
480 EXCEPTION
481 WHEN G_EXCEPTION_HALT_VALIDATION THEN
482 -- no processing necessary; validation can continue
483 -- with the next column
484 NULL;
485
486 WHEN OTHERS THEN
487 -- store SQL error message on message stack for caller
488 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
489 ,p_msg_name => G_UNEXPECTED_ERROR
490 ,p_token1 => G_SQLCODE_TOKEN
491 ,p_token1_value => SQLCODE
492 ,p_token2 => G_SQLERRM_TOKEN
493 ,p_token2_value => SQLERRM);
494
495 -- notify caller of an UNEXPECTED error
496 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
497
498 END validate_tax_owner;
499
500 ---------------------------------------------------------------------------
501 -- PROCEDURE validate_deal_type
502 ---------------------------------------------------------------------------
503 -- Start of comments
504 --
505 -- Procedure Name : validate_deal_type
506 -- Description :
507 -- Business Rules :
508 -- Parameters :
509 -- Version : 1.0
510 -- End of comments
511 ---------------------------------------------------------------------------
512 PROCEDURE validate_deal_type(
513 p_gtsv_rec IN gtsv_rec_type,
514 x_return_status OUT NOCOPY VARCHAR2
515 ) IS
516 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
517
518 BEGIN
519 -- initialize return status
520 x_return_status := okl_api.G_RET_STS_SUCCESS;
521
522 -- check for data before processing
523 IF (p_gtsv_rec.deal_type IS NOT NULL) AND
524 (p_gtsv_rec.deal_type <> okl_api.G_MISS_CHAR) THEN
525
526
527 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_STREAM_ALL_BOOK_CLASS',
528 p_lookup_code => p_gtsv_rec.deal_type);
529
530 IF (l_dummy = okl_api.g_false) THEN
531 okl_api.SET_MESSAGE(p_app_name => g_app_name,
532 p_msg_name => g_invalid_value,
533 p_token1 => g_col_name_token,
534 p_token1_value => 'DEAL_TYPE');
535 x_return_status := okl_api.G_RET_STS_ERROR;
536 RAISE G_EXCEPTION_HALT_VALIDATION;
537 END IF;
538
539 END IF;
540
541 EXCEPTION
542 WHEN G_EXCEPTION_HALT_VALIDATION THEN
543 -- no processing necessary; validation can continue
544 -- with the next column
545 NULL;
546
547 WHEN OTHERS THEN
548 -- store SQL error message on message stack for caller
549 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
550 ,p_msg_name => G_UNEXPECTED_ERROR
551 ,p_token1 => G_SQLCODE_TOKEN
552 ,p_token1_value => SQLCODE
553 ,p_token2 => G_SQLERRM_TOKEN
554 ,p_token2_value => SQLERRM);
555
556 -- notify caller of an UNEXPECTED error
557 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
558
559 END validate_deal_type;
560
561 ---------------------------------------------------------------------------
562 -- PROCEDURE validate_pricing_engine
563 ---------------------------------------------------------------------------
564 -- Start of comments
565 --
566 -- Procedure Name : validate_pricing_engine
567 -- Description :
568 -- Business Rules :
569 -- Parameters :
570 -- Version : 1.0
571 -- End of comments
572 ---------------------------------------------------------------------------
573 PROCEDURE validate_pricing_engine(
574 p_gtsv_rec IN gtsv_rec_type,
575 x_return_status OUT NOCOPY VARCHAR2
576 ) IS
577 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
578
579 BEGIN
580 -- initialize return status
581 x_return_status := okl_api.G_RET_STS_SUCCESS;
582
583 -- check for data before processing
584 IF (p_gtsv_rec.pricing_engine IS NOT NULL) AND
585 (p_gtsv_rec.pricing_engine <> okl_api.G_MISS_CHAR) THEN
586
587 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_STREAM_PRICING',
588 p_lookup_code => p_gtsv_rec.pricing_engine);
589
590
591 IF (l_dummy = okl_api.g_false) THEN
592 okl_api.SET_MESSAGE(p_app_name => g_app_name,
593 p_msg_name => g_invalid_value,
594 p_token1 => g_col_name_token,
595 p_token1_value => 'PRICING_ENGINE');
596 x_return_status := okl_api.G_RET_STS_ERROR;
597 RAISE G_EXCEPTION_HALT_VALIDATION;
598 END IF;
599
600 END IF;
601
602 EXCEPTION
603 WHEN G_EXCEPTION_HALT_VALIDATION THEN
604 -- no processing necessary; validation can continue
605 -- with the next column
606 NULL;
607
608 WHEN OTHERS THEN
609 -- store SQL error message on message stack for caller
610 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
611 ,p_msg_name => G_UNEXPECTED_ERROR
612 ,p_token1 => G_SQLCODE_TOKEN
613 ,p_token1_value => SQLCODE
614 ,p_token2 => G_SQLERRM_TOKEN
615 ,p_token2_value => SQLERRM);
616
617 -- notify caller of an UNEXPECTED error
618 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
619
620 END validate_pricing_engine;
621
622 ---------------------------------------------------------------------------
623 -- PROCEDURE val_interest_calc_meth_code
624 ---------------------------------------------------------------------------
625 -- Start of comments
626 --
627 -- Procedure Name : val_interest_calc_meth_code
628 -- Description : Validates that INTEREST_CALC_METH_CODE is entered / valid
629 -- Business Rules :
630 -- Parameters :
631 -- Version : 1.0
632 -- End of comments
633 ---------------------------------------------------------------------------
634 PROCEDURE val_interest_calc_meth_code(
635 p_gtsv_rec IN gtsv_rec_type,
636 x_return_status OUT NOCOPY VARCHAR2
637 ) IS
638 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
639
640 BEGIN
641 -- initialize return status
642 x_return_status := okl_api.G_RET_STS_SUCCESS;
643
644 -- check for data before processing
645 IF p_gtsv_rec.INTEREST_CALC_METH_CODE = okl_api.G_MISS_CHAR OR
646 p_gtsv_rec.INTEREST_CALC_METH_CODE IS NULL
647 THEN
648 IF p_gtsv_rec.product_type = OKL_GTS_PVT.G_FINANCIAL_TYPE THEN
649 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'INTEREST_CALC_METH_CODE');
650 x_return_status := okl_api.G_RET_STS_ERROR;
651 RAISE G_EXCEPTION_HALT_VALIDATION;
652 END IF;
653 ELSE
654 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_INTEREST_CALCULATION_BASIS',
655 p_lookup_code => p_gtsv_rec.INTEREST_CALC_METH_CODE);
656 IF (l_dummy = okl_api.g_false) THEN
657 okl_api.SET_MESSAGE(p_app_name => g_app_name,
658 p_msg_name => g_invalid_value,
659 p_token1 => g_col_name_token,
660 p_token1_value => 'INTEREST_CALC_METH_CODE');
661 x_return_status := okl_api.G_RET_STS_ERROR;
662 RAISE G_EXCEPTION_HALT_VALIDATION;
663 END IF;
664
665 END IF;
666
667 EXCEPTION
668 WHEN G_EXCEPTION_HALT_VALIDATION THEN
669 -- no processing necessary; validation can continue
670 -- with the next column
671 NULL;
672
673 WHEN OTHERS THEN
674 -- store SQL error message on message stack for caller
675 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
676 ,p_msg_name => G_UNEXPECTED_ERROR
677 ,p_token1 => G_SQLCODE_TOKEN
678 ,p_token1_value => SQLCODE
679 ,p_token2 => G_SQLERRM_TOKEN
680 ,p_token2_value => SQLERRM);
681
682 -- notify caller of an UNEXPECTED error
683 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
684
685 END val_interest_calc_meth_code;
686
687 ---------------------------------------------------------------------------
688 -- PROCEDURE val_revenue_recog_meth_code
689 ---------------------------------------------------------------------------
690 -- Start of comments
691 --
692 -- Procedure Name : val_revenue_recog_meth_code
693 -- Description : Validates that REVENUE_RECOG_METH_CODE is entered / valid
694 -- Business Rules :
695 -- Parameters :
696 -- Version : 1.0
697 -- End of comments
698 ---------------------------------------------------------------------------
699 PROCEDURE val_revenue_recog_meth_code(
700 p_gtsv_rec IN gtsv_rec_type,
701 x_return_status OUT NOCOPY VARCHAR2
702 ) IS
703 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
704
705 BEGIN
706 -- initialize return status
707 x_return_status := okl_api.G_RET_STS_SUCCESS;
708
709 -- check for data before processing
710 IF p_gtsv_rec.REVENUE_RECOG_METH_CODE = okl_api.G_MISS_CHAR OR
711 p_gtsv_rec.REVENUE_RECOG_METH_CODE IS NULL
712 THEN
713 IF p_gtsv_rec.product_type = OKL_GTS_PVT.G_FINANCIAL_TYPE THEN
714 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'REVENUE_RECOG_METH_CODE');
715 x_return_status := okl_api.G_RET_STS_ERROR;
716 RAISE G_EXCEPTION_HALT_VALIDATION;
717 END IF;
718 ELSE
719 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_REVENUE_RECOGNITION_METHOD',
720 p_lookup_code => p_gtsv_rec.REVENUE_RECOG_METH_CODE);
721 IF (l_dummy = okl_api.g_false) THEN
722 okl_api.SET_MESSAGE(p_app_name => g_app_name,
723 p_msg_name => g_invalid_value,
724 p_token1 => g_col_name_token,
725 p_token1_value => 'REVENUE_RECOG_METH_CODE');
726 x_return_status := okl_api.G_RET_STS_ERROR;
727 RAISE G_EXCEPTION_HALT_VALIDATION;
728 END IF;
729
730 END IF;
731
732 EXCEPTION
733 WHEN G_EXCEPTION_HALT_VALIDATION THEN
734 -- no processing necessary; validation can continue
735 -- with the next column
736 NULL;
737
738 WHEN OTHERS THEN
739 -- store SQL error message on message stack for caller
740 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
741 ,p_msg_name => G_UNEXPECTED_ERROR
742 ,p_token1 => G_SQLCODE_TOKEN
743 ,p_token1_value => SQLCODE
744 ,p_token2 => G_SQLERRM_TOKEN
745 ,p_token2_value => SQLERRM);
746
747 -- notify caller of an UNEXPECTED error
748 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
749
750 END val_revenue_recog_meth_code;
751
752 ---------------------------------------------------------------------------
753 -- PROCEDURE validate_days_in_month_code
754 ---------------------------------------------------------------------------
755 -- Start of comments
756 --
757 -- Procedure Name : validate_days_in_month_code
758 -- Description : Validates that validate_days_in_month_code is entered / valid
759 -- Business Rules :
760 -- Parameters :
761 -- Version : 1.0
762 -- End of comments
763 ---------------------------------------------------------------------------
764 PROCEDURE validate_days_in_month_code(
765 p_gtsv_rec IN gtsv_rec_type,
766 x_return_status OUT NOCOPY VARCHAR2
767 ) IS
768 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
769
770 BEGIN
771 -- initialize return status
772 x_return_status := okl_api.G_RET_STS_SUCCESS;
773
774 -- check for data before processing
775 IF p_gtsv_rec.DAYS_IN_MONTH_CODE = okl_api.G_MISS_CHAR OR
776 p_gtsv_rec.DAYS_IN_MONTH_CODE IS NULL
777 THEN
778 IF p_gtsv_rec.product_type = OKL_GTS_PVT.G_FINANCIAL_TYPE THEN
779 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'DAYS_IN_MONTH_CODE');
780 x_return_status := okl_api.G_RET_STS_ERROR;
781 RAISE G_EXCEPTION_HALT_VALIDATION;
782 END IF;
783 ELSE
784 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_MONTH_TYPE',
785 p_lookup_code => p_gtsv_rec.DAYS_IN_MONTH_CODE);
786
787 IF (l_dummy = okl_api.g_false) THEN
788 okl_api.SET_MESSAGE(p_app_name => g_app_name,
789 p_msg_name => g_invalid_value,
790 p_token1 => g_col_name_token,
791 p_token1_value => 'DAYS_IN_MONTH_CODE');
792 x_return_status := okl_api.G_RET_STS_ERROR;
793 RAISE G_EXCEPTION_HALT_VALIDATION;
794 END IF;
795 END IF;
796
797 EXCEPTION
798 WHEN G_EXCEPTION_HALT_VALIDATION THEN
799 -- no processing necessary; validation can continue
800 -- with the next column
801 NULL;
802
803 WHEN OTHERS THEN
804 -- store SQL error message on message stack for caller
805 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
806 ,p_msg_name => G_UNEXPECTED_ERROR
807 ,p_token1 => G_SQLCODE_TOKEN
808 ,p_token1_value => SQLCODE
809 ,p_token2 => G_SQLERRM_TOKEN
810 ,p_token2_value => SQLERRM);
811
812 -- notify caller of an UNEXPECTED error
813 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
814
815 END validate_days_in_month_code;
816
817 ---------------------------------------------------------------------------
818 -- PROCEDURE validate_days_in_yr_code
819 ---------------------------------------------------------------------------
820 -- Start of comments
821 --
822 -- Procedure Name : validate_days_in_yr_code
823 -- Description : Validates that from validate_days_in_yr_code is entered / valid
824 -- Business Rules :
825 -- Parameters :
826 -- Version : 1.0
827 -- End of comments
828 ---------------------------------------------------------------------------
829 PROCEDURE validate_days_in_yr_code(
830 p_gtsv_rec IN gtsv_rec_type,
831 x_return_status OUT NOCOPY VARCHAR2
832 ) IS
833 l_dummy VARCHAR2(1) := okl_api.G_FALSE;
834
835 BEGIN
836 -- initialize return status
837 x_return_status := okl_api.G_RET_STS_SUCCESS;
838
839 -- check for data before processing
840 IF p_gtsv_rec.DAYS_IN_YR_CODE = okl_api.G_MISS_CHAR OR
841 p_gtsv_rec.DAYS_IN_YR_CODE IS NULL
842 THEN
843 IF p_gtsv_rec.product_type = OKL_GTS_PVT.G_FINANCIAL_TYPE THEN
844 okl_api.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'OKL_YEAR_TYPE');
845 x_return_status := okl_api.G_RET_STS_ERROR;
846 RAISE G_EXCEPTION_HALT_VALIDATION;
847 END IF;
848 ELSE
849 l_dummy := OKL_ACCOUNTING_UTIL.VALIDATE_LOOKUP_CODE(p_lookup_type => 'OKL_YEAR_TYPE',
850 p_lookup_code => p_gtsv_rec.DAYS_IN_YR_CODE);
851
852 IF (l_dummy = okl_api.g_false) THEN
853 okl_api.SET_MESSAGE(p_app_name => g_app_name,
854 p_msg_name => g_invalid_value,
855 p_token1 => g_col_name_token,
856 p_token1_value => 'DAYS_IN_YR_CODE');
857 x_return_status := okl_api.G_RET_STS_ERROR;
858 RAISE G_EXCEPTION_HALT_VALIDATION;
859 END IF;
860 END IF;
861
862 EXCEPTION
863 WHEN G_EXCEPTION_HALT_VALIDATION THEN
864 -- no processing necessary; validation can continue
865 -- with the next column
866 NULL;
867
868 WHEN OTHERS THEN
869 -- store SQL error message on message stack for caller
870 okl_api.SET_MESSAGE(p_app_name => G_APP_NAME
871 ,p_msg_name => G_UNEXPECTED_ERROR
872 ,p_token1 => G_SQLCODE_TOKEN
873 ,p_token1_value => SQLCODE
874 ,p_token2 => G_SQLERRM_TOKEN
875 ,p_token2_value => SQLERRM);
876
877 -- notify caller of an UNEXPECTED error
878 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
879
880 END validate_days_in_yr_code;
881
882 ---------------------------------------------------------------------------
883 -- FUNCTION Validate_Attributes
884 ---------------------------------------------------------------------------
885 -- Start of comments
886 --
887 -- Procedure Name : Validate_Attributes
888 -- Description :
889 -- Business Rules :
890 -- Parameters :
891 -- Version : 1.0
892 -- End of comments
893 ---------------------------------------------------------------------------
894
895 FUNCTION Validate_Attributes (
896 p_gtsv_rec IN gtsv_rec_type
897 ) RETURN VARCHAR2 IS
898
899 x_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
900 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
901 BEGIN
902 -- call each column-level validation
903
904
905 -- Validate_Id
906 Validate_Id(p_gtsv_rec, x_return_status);
907 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
908 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
909 -- need to exit
910 l_return_status := x_return_status;
911 RAISE G_EXCEPTION_HALT_VALIDATION;
912 ELSE
913 -- there was an error
914 l_return_status := x_return_status;
915 END IF;
916 END IF;
917
918 -- Validate_Object_Version_Number
919 validate_object_version_number(p_gtsv_rec, x_return_status);
920 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
921 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
922 -- need to exit
923 l_return_status := x_return_status;
924 RAISE G_EXCEPTION_HALT_VALIDATION;
925 ELSE
926 -- there was an error
927 l_return_status := x_return_status;
928 END IF;
929 END IF;
930 -- validate_name
931 -- Need to find a solution so as to check for the uniqueness
932 -- during the duplicate scenario.
933 validate_name(p_gtsv_rec, x_return_status);
934 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
935 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
936 -- need to exit
937 l_return_status := x_return_status;
938 RAISE G_EXCEPTION_HALT_VALIDATION;
939 ELSE
940 -- there was an error
941 l_return_status := x_return_status;
942 END IF;
943 END IF;
944 Validate_product_type(p_gtsv_rec, x_return_status);
945 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
946 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
947 -- need to exit
948 l_return_status := x_return_status;
949 RAISE G_EXCEPTION_HALT_VALIDATION;
950 ELSE
951 -- there was an error
952 l_return_status := x_return_status;
953 END IF;
954 END IF;
955
956 validate_tax_owner(p_gtsv_rec, x_return_status);
957 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
958 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
959 -- need to exit
960 l_return_status := x_return_status;
961 RAISE G_EXCEPTION_HALT_VALIDATION;
962 ELSE
963 -- there was an error
964 l_return_status := x_return_status;
965 END IF;
966 END IF;
967
968 validate_deal_type(p_gtsv_rec, x_return_status);
969 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
970 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
971 -- need to exit
972 l_return_status := x_return_status;
973 RAISE G_EXCEPTION_HALT_VALIDATION;
974 ELSE
975 -- there was an error
976 l_return_status := x_return_status;
977 END IF;
978 END IF;
979
980 validate_pricing_engine(p_gtsv_rec, x_return_status);
981 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
982 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
983 -- need to exit
984 l_return_status := x_return_status;
985 RAISE G_EXCEPTION_HALT_VALIDATION;
986 ELSE
987 -- there was an error
988 l_return_status := x_return_status;
989 END IF;
990 END IF;
991
992 val_interest_calc_meth_code(p_gtsv_rec, x_return_status);
993 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
994 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
995 -- need to exit
996 l_return_status := x_return_status;
997 RAISE G_EXCEPTION_HALT_VALIDATION;
998 ELSE
999 -- there was an error
1000 l_return_status := x_return_status;
1001 END IF;
1002 END IF;
1003
1004 val_revenue_recog_meth_code(p_gtsv_rec, x_return_status);
1005 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
1006 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1007 -- need to exit
1008 l_return_status := x_return_status;
1009 RAISE G_EXCEPTION_HALT_VALIDATION;
1010 ELSE
1011 -- there was an error
1012 l_return_status := x_return_status;
1013 END IF;
1014 END IF;
1015
1016 validate_days_in_month_code(p_gtsv_rec, x_return_status);
1017 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
1018 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1019 -- need to exit
1020 l_return_status := x_return_status;
1021 RAISE G_EXCEPTION_HALT_VALIDATION;
1022 ELSE
1023 -- there was an error
1024 l_return_status := x_return_status;
1025 END IF;
1026 END IF;
1027
1028 validate_days_in_yr_code(p_gtsv_rec, x_return_status);
1029 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
1030 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1031 -- need to exit
1032 l_return_status := x_return_status;
1033 RAISE G_EXCEPTION_HALT_VALIDATION;
1034 ELSE
1035 -- there was an error
1036 l_return_status := x_return_status;
1037 END IF;
1038 END IF;
1039
1040 -- Added for new field by dpsingh for bug 6274342
1041 validate_arrears_pay_dt_opt (p_gtsv_rec, x_return_status);
1042 IF (x_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
1043 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1044 -- need to exit
1045 l_return_status := x_return_status;
1046 RAISE G_EXCEPTION_HALT_VALIDATION;
1047 ELSE
1048 -- there was an error
1049 l_return_status := x_return_status;
1050 END IF;
1051 END IF;
1052
1053 RETURN(l_return_status);
1054
1055 EXCEPTION
1056 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1057 -- just come out with return status
1058 NULL;
1059 RETURN (l_return_status);
1060 WHEN OTHERS THEN
1061 -- store SQL error message on message stack for caller
1062 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
1063 p_msg_name => g_unexpected_error,
1064 p_token1 => g_sqlcode_token,
1065 p_token1_value => SQLCODE,
1066 p_token2 => g_sqlerrm_token,
1067 p_token2_value => SQLERRM);
1068 -- notify caller of an UNEXPECTED error
1069 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1070 RETURN(l_return_status);
1071 END;
1072
1073
1074
1075 ----------------------------------------------
1076 -- validate_record for:OKL_ST_GEN_TMPT_SETS --
1077 ----------------------------------------------
1078 FUNCTION validate_record (
1079 p_gtsv_rec IN gtsv_rec_type
1080 ) RETURN VARCHAR2 IS
1081 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1082 BEGIN
1083 RETURN (l_return_status);
1084 END validate_record;
1085
1086 ---------------------------------------------------------------------------
1087 -- PROCEDURE qc
1088 ---------------------------------------------------------------------------
1089 PROCEDURE qc IS
1090 BEGIN
1091 NULL;
1092 END qc;
1093
1094 ---------------------------------------------------------------------------
1095 -- PROCEDURE change_version
1096 ---------------------------------------------------------------------------
1097 PROCEDURE change_version IS
1098 BEGIN
1099 NULL;
1100 END change_version;
1101
1102 ---------------------------------------------------------------------------
1103 -- PROCEDURE api_copy
1104 ---------------------------------------------------------------------------
1105 PROCEDURE api_copy IS
1106 BEGIN
1107 NULL;
1108 END api_copy;
1109
1110
1111 ---------------------------------------------------------------------------
1112 -- PROCEDURE Migrate
1113 ---------------------------------------------------------------------------
1114 PROCEDURE migrate (
1115 p_from IN gts_rec_type,
1116 p_to IN OUT NOCOPY gtsv_rec_type
1117 ) IS
1118 BEGIN
1119 p_to.id := p_from.id;
1120 p_to.object_version_number := p_from.object_version_number;
1121 p_to.name := p_from.name;
1122 p_to.description := p_from.description;
1123 p_to.product_type := p_from.product_type;
1124 p_to.tax_owner := p_from.tax_owner;
1125 p_to.deal_type := p_from.deal_type;
1126 p_to.pricing_engine := p_from.pricing_engine;
1127 p_to.org_id := p_from.org_id;
1128 p_to.created_by := p_from.created_by;
1129 p_to.creation_date := p_from.creation_date;
1130 p_to.last_updated_by := p_from.last_updated_by;
1131 p_to.last_update_date := p_from.last_update_date;
1132 p_to.last_update_login := p_from.last_update_login;
1133 p_to.interest_calc_meth_code := p_from.interest_calc_meth_code;
1134 p_to.revenue_recog_meth_code := p_from.revenue_recog_meth_code;
1135 p_to.days_in_month_code := p_from.days_in_month_code;
1136 p_to.days_in_yr_code := p_from.days_in_yr_code;
1137 p_to.isg_arrears_pay_dates_option := p_from.isg_arrears_pay_dates_option;
1138
1139 END;
1140
1141
1142 ---------------------------------------------------------------------------
1143 -- PROCEDURE Migrate
1144 ---------------------------------------------------------------------------
1145 PROCEDURE migrate (
1146 p_from IN gtsv_rec_type,
1147 p_to IN OUT NOCOPY gts_rec_type
1148 ) IS
1149 BEGIN
1150 p_to.id := p_from.id;
1151 p_to.object_version_number := p_from.object_version_number;
1152 p_to.name := p_from.name;
1153 p_to.description := p_from.description;
1154 p_to.product_type := p_from.product_type;
1155 p_to.tax_owner := p_from.tax_owner;
1156 p_to.deal_type := p_from.deal_type;
1157 p_to.pricing_engine := p_from.pricing_engine;
1158 p_to.org_id := p_from.org_id;
1159 p_to.created_by := p_from.created_by;
1160 p_to.creation_date := p_from.creation_date;
1161 p_to.last_updated_by := p_from.last_updated_by;
1162 p_to.last_update_date := p_from.last_update_date;
1163 p_to.last_update_login := p_from.last_update_login;
1164 p_to.interest_calc_meth_code := p_from.interest_calc_meth_code;
1165 p_to.revenue_recog_meth_code := p_from.revenue_recog_meth_code;
1166 p_to.days_in_month_code := p_from.days_in_month_code;
1167 p_to.days_in_yr_code := p_from.days_in_yr_code;
1168 p_to.isg_arrears_pay_dates_option := p_from.isg_arrears_pay_dates_option;
1169 END;
1170 ---------------------------------------------------------------------------
1171 -- FUNCTION null_out_defaults
1172 ---------------------------------------------------------------------------
1173 -- Start of comments
1174 --
1175 -- Function Name : null_out_defaults
1176 -- Description : nulling out the defaults
1177 -- Business Rules :
1178 -- Parameters :
1179 -- Version : 1.0
1180 -- End of comments
1181 ---------------------------------------------------------------------------
1182 FUNCTION null_out_defaults (
1183 p_gtsv_rec IN gtsv_rec_type
1184 ) RETURN gtsv_rec_type IS
1185 l_gtsv_rec gtsv_rec_type := p_gtsv_rec;
1186 BEGIN
1187
1188 IF (l_gtsv_rec.id = okl_api.G_MISS_NUM) THEN
1189 l_gtsv_rec.id := NULL;
1190 END IF;
1191 IF (l_gtsv_rec.object_version_number = okl_api.G_MISS_NUM) THEN
1192 l_gtsv_rec.object_version_number := NULL;
1193 END IF;
1194 IF (l_gtsv_rec.name = okl_api.G_MISS_CHAR) THEN
1195 l_gtsv_rec.name := NULL;
1196 END IF;
1197 IF (l_gtsv_rec.description = okl_api.G_MISS_CHAR) THEN
1198 l_gtsv_rec.description := NULL;
1199 END IF;
1200 IF (l_gtsv_rec.product_type = okl_api.G_MISS_CHAR) THEN
1201 l_gtsv_rec.product_type := NULL;
1202 END IF;
1203 IF (l_gtsv_rec.tax_owner = okl_api.G_MISS_CHAR) THEN
1204 l_gtsv_rec.tax_owner := NULL;
1205 END IF;
1206 IF (l_gtsv_rec.deal_type = okl_api.G_MISS_CHAR) THEN
1207 l_gtsv_rec.deal_type := NULL;
1208 END IF;
1209 IF (l_gtsv_rec.pricing_engine = okl_api.G_MISS_CHAR) THEN
1210 l_gtsv_rec.pricing_engine := NULL;
1211 END IF;
1212 IF (l_gtsv_rec.org_id = okl_api.G_MISS_NUM) THEN
1213 l_gtsv_rec.org_id := NULL;
1214 END IF;
1215 IF (l_gtsv_rec.created_by = okl_api.G_MISS_NUM) THEN
1216 l_gtsv_rec.created_by := NULL;
1217 END IF;
1218 IF (l_gtsv_rec.creation_date = okl_api.G_MISS_DATE) THEN
1219 l_gtsv_rec.creation_date := NULL;
1220 END IF;
1221 IF (l_gtsv_rec.last_updated_by = okl_api.G_MISS_NUM) THEN
1222 l_gtsv_rec.last_updated_by := NULL;
1223 END IF;
1224 IF (l_gtsv_rec.last_update_date = okl_api.G_MISS_DATE) THEN
1225 l_gtsv_rec.last_update_date := NULL;
1226 END IF;
1227 IF (l_gtsv_rec.last_update_login = okl_api.G_MISS_NUM) THEN
1228 l_gtsv_rec.last_update_login := NULL;
1229 END IF;
1230 IF (l_gtsv_rec.INTEREST_CALC_METH_CODE = okl_api.G_MISS_CHAR) THEN
1231 l_gtsv_rec.INTEREST_CALC_METH_CODE := NULL;
1232 END IF;
1233 IF (l_gtsv_rec.REVENUE_RECOG_METH_CODE = okl_api.G_MISS_CHAR) THEN
1234 l_gtsv_rec.REVENUE_RECOG_METH_CODE := NULL;
1235 END IF;
1236 IF (l_gtsv_rec.DAYS_IN_MONTH_CODE = okl_api.G_MISS_CHAR) THEN
1237 l_gtsv_rec.DAYS_IN_MONTH_CODE := NULL;
1238 END IF;
1239 IF (l_gtsv_rec.DAYS_IN_YR_CODE = okl_api.G_MISS_CHAR) THEN
1240 l_gtsv_rec.DAYS_IN_YR_CODE := NULL;
1241 END IF;
1242 IF (l_gtsv_rec.ISG_ARREARS_PAY_DATES_OPTION = okl_api.G_MISS_CHAR) THEN
1243 l_gtsv_rec.ISG_ARREARS_PAY_DATES_OPTION := NULL;
1244 END IF;
1245 RETURN(l_gtsv_rec);
1246 END null_out_defaults;
1247
1248 ---------------------------------------------------------------------------
1249 -- PROCEDURE insert_row
1250 ---------------------------------------------------------------------------
1251 -- Start of comments
1252 --
1253 -- Procedure Name : insert_row
1254 -- Description : procedure for inserting the records in
1255 -- table OKL_ST_GEN_TMPT_SETS
1256 -- Business Rules :
1257 -- Parameters :
1258 -- Version : 1.0
1259 -- End of comments
1260 ---------------------------------------------------------------------------
1261
1262 PROCEDURE insert_row(
1263 p_api_version IN NUMBER,
1264 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1265 x_return_status OUT NOCOPY VARCHAR2,
1266 x_msg_count OUT NOCOPY NUMBER,
1267 x_msg_data OUT NOCOPY VARCHAR2,
1268 p_gts_rec IN gts_rec_type,
1269 x_gts_rec OUT NOCOPY gts_rec_type ) IS
1270
1271 -- Local Variables within the function
1272 l_api_version CONSTANT NUMBER := 1;
1273 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1274 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1275 l_gts_rec gts_rec_type := p_gts_rec;
1276 l_def_gts_rec gts_rec_type;
1277 ---------------------------------------------
1278 -- Set_Attributes for:OKL_ST_GEN_TMPT_SETS --
1279 ---------------------------------------------
1280 FUNCTION Set_Attributes (
1281 p_gts_rec IN gts_rec_type,
1282 x_gts_rec OUT NOCOPY gts_rec_type
1283 ) RETURN VARCHAR2 IS
1284 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1285 BEGIN
1286 x_gts_rec := p_gts_rec;
1287 RETURN(l_return_status);
1288 END Set_Attributes;
1289 BEGIN
1290 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1291 p_init_msg_list,
1292 '_PVT',
1293 x_return_status);
1294 --- If any errors happen abort API
1295 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1296 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1297 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1298 RAISE okl_api.G_EXCEPTION_ERROR;
1299 END IF;
1300
1301 --- Setting item attributes
1302 l_return_status := Set_Attributes(
1303 p_gts_rec, -- IN
1304 l_gts_rec -- OUT
1305 );
1306 --- If any errors happen abort API
1307 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1308 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1309 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1310 RAISE okl_api.G_EXCEPTION_ERROR;
1311 END IF;
1312
1313 INSERT INTO OKL_ST_GEN_TMPT_SETS(
1314 Id
1315 ,object_version_number
1316 ,name
1317 ,description
1318 ,product_type
1319 ,tax_owner
1320 ,deal_type
1321 ,pricing_engine
1322 ,org_id
1323 ,created_by
1324 ,creation_date
1325 ,last_updated_by
1326 ,last_update_date
1327 ,last_update_login
1328 ,interest_calc_meth_code
1329 ,revenue_recog_meth_code
1330 ,days_in_month_code
1331 ,days_in_yr_code
1332 ,isg_arrears_pay_dates_option
1333 )
1334 VALUES (
1335 l_gts_rec.Id
1336 ,l_gts_rec.object_version_number
1337 ,l_gts_rec.name
1338 ,l_gts_rec.description
1339 ,l_gts_rec.product_type
1340 ,l_gts_rec.tax_owner
1341 ,l_gts_rec.deal_type
1342 ,l_gts_rec.pricing_engine
1343 ,l_gts_rec.org_id
1344 ,l_gts_rec.created_by
1345 ,l_gts_rec.creation_date
1346 ,l_gts_rec.last_updated_by
1347 ,l_gts_rec.last_update_date
1348 ,l_gts_rec.last_update_login
1349 ,l_gts_rec.interest_calc_meth_code
1350 ,l_gts_rec.revenue_recog_meth_code
1351 ,l_gts_rec.days_in_month_code
1352 ,l_gts_rec.days_in_yr_code
1353 ,l_gts_rec.isg_arrears_pay_dates_option
1354 );
1355
1356 -- Set OUT values
1357 x_gts_rec := l_gts_rec;
1358 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1359
1360 EXCEPTION
1361 WHEN okl_api.G_EXCEPTION_ERROR THEN
1362 x_return_status := okl_api.HANDLE_EXCEPTIONS
1363 (
1364 l_api_name,
1365 G_PKG_NAME,
1366 'okl_api.G_RET_STS_ERROR',
1367 x_msg_count,
1368 x_msg_data,
1369 '_PVT'
1370 );
1371 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1372 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1373 (
1374 l_api_name,
1375 G_PKG_NAME,
1376 'okl_api.G_RET_STS_UNEXP_ERROR',
1377 x_msg_count,
1378 x_msg_data,
1379 '_PVT'
1380 );
1381 WHEN OTHERS THEN
1382 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1383 (
1384 l_api_name,
1385 G_PKG_NAME,
1386 'OTHERS',
1387 x_msg_count,
1388 x_msg_data,
1389 '_PVT'
1390 );
1391 END insert_row;
1392
1393 PROCEDURE insert_row(
1394 p_api_version IN NUMBER,
1395 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1396 x_return_status OUT NOCOPY VARCHAR2,
1397 x_msg_count OUT NOCOPY NUMBER,
1398 x_msg_data OUT NOCOPY VARCHAR2,
1399 p_gtsv_rec IN gtsv_rec_type,
1400 x_gtsv_rec OUT NOCOPY gtsv_rec_type ) IS
1401
1402 -- Local Variables within the function
1403 l_api_version CONSTANT NUMBER := 1;
1404 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1405 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1406 l_gtsv_rec gtsv_rec_type;
1407 l_def_gtsv_rec gtsv_rec_type;
1408 l_gts_rec gts_rec_type;
1409 lx_gts_rec gts_rec_type;
1410 -------------------------------
1411 -- FUNCTION fill_who_columns --
1412 -------------------------------
1413 FUNCTION fill_who_columns (
1414 p_gtsv_rec IN gtsv_rec_type
1415 ) RETURN gtsv_rec_type IS
1416 l_gtsv_rec gtsv_rec_type := p_gtsv_rec;
1417 BEGIN
1418 l_gtsv_rec.CREATION_DATE := SYSDATE;
1419 l_gtsv_rec.CREATED_BY := Fnd_Global.USER_ID;
1420 l_gtsv_rec.LAST_UPDATE_DATE := SYSDATE;
1421 l_gtsv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
1422 l_gtsv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1423 RETURN(l_gtsv_rec);
1424 END fill_who_columns;
1425
1426 -----------------------------------------------
1427 -- Set_Attributes for:OKL_ST_GEN_TMPT_SETS_V --
1428 -----------------------------------------------
1429 FUNCTION Set_Attributes (
1430 p_gtsv_rec IN gtsv_rec_type,
1431 x_gtsv_rec OUT NOCOPY gtsv_rec_type
1432 ) RETURN VARCHAR2 IS
1433 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1434 BEGIN
1435 x_gtsv_rec := p_gtsv_rec;
1436 x_gtsv_rec.OBJECT_VERSION_NUMBER := 1;
1437 x_gtsv_rec.ORG_ID := MO_GLOBAL.GET_CURRENT_ORG_ID();
1438
1439 RETURN(l_return_status);
1440 END Set_Attributes;
1441
1442 BEGIN
1443 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1444 G_PKG_NAME,
1445 p_init_msg_list,
1446 l_api_version,
1447 p_api_version,
1448 '_PVT',
1449 x_return_status);
1450 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1451 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1452 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1453 RAISE okl_api.G_EXCEPTION_ERROR;
1454 END IF;
1455
1456 l_gtsv_rec := null_out_defaults(p_gtsv_rec);
1457 -- Set primary key value
1458 l_gtsv_rec.ID := get_seq_id;
1459
1460 --- Setting item attributes
1461 l_return_status := Set_Attributes(
1462 l_gtsv_rec, -- IN
1463 l_def_gtsv_rec); -- OUT
1464 --- If any errors happen abort API
1465 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1466 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1467 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1468 RAISE okl_api.G_EXCEPTION_ERROR;
1469 END IF;
1470
1471 -- fill who columns for the l_def_gtsv_rec
1472 l_def_gtsv_rec := fill_who_columns(l_def_gtsv_rec);
1473
1474 --- Validate all non-missing attributes (Item Level Validation)
1475 l_return_status := Validate_Attributes(l_def_gtsv_rec);
1476 --- If any errors happen abort API
1477 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1478 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1479 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1480 RAISE okl_api.G_EXCEPTION_ERROR;
1481 END IF;
1482 -- Perfrom all row level validations
1483 l_return_status := validate_record(l_def_gtsv_rec);
1484 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1485 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1486 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1487 RAISE okl_api.G_EXCEPTION_ERROR;
1488 END IF;
1489 --------------------------------------
1490 -- Move VIEW record to "Child" records
1491 --------------------------------------
1492 migrate(l_def_gtsv_rec, l_gts_rec);
1493 --------------------------------------------
1494 -- Call the INSERT_ROW for each child record
1495 --------------------------------------------
1496 insert_row(
1497 p_api_version => l_api_version,
1498 p_init_msg_list => p_init_msg_list,
1499 x_return_status => x_return_status,
1500 x_msg_count => x_msg_count,
1501 x_msg_data => x_msg_data,
1502 p_gts_rec => l_gts_rec,
1503 x_gts_rec => lx_gts_rec
1504 );
1505 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1506 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1507 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1508 RAISE okl_api.G_EXCEPTION_ERROR;
1509 END IF;
1510 migrate(lx_gts_rec, l_def_gtsv_rec);
1511
1512 -- Set OUT values
1513 x_gtsv_rec := l_def_gtsv_rec;
1514 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1515
1516 EXCEPTION
1517 WHEN okl_api.G_EXCEPTION_ERROR THEN
1518 x_return_status := okl_api.HANDLE_EXCEPTIONS
1519 (
1520 l_api_name,
1521 G_PKG_NAME,
1522 'okl_api.G_RET_STS_ERROR',
1523 x_msg_count,
1524 x_msg_data,
1525 '_PVT'
1526 );
1527 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1528 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1529 (
1530 l_api_name,
1531 G_PKG_NAME,
1532 'okl_api.G_RET_STS_UNEXP_ERROR',
1533 x_msg_count,
1534 x_msg_data,
1535 '_PVT'
1536 );
1537 WHEN OTHERS THEN
1538 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1539 (
1540 l_api_name,
1541 G_PKG_NAME,
1542 'OTHERS',
1543 x_msg_count,
1544 x_msg_data,
1545 '_PVT'
1546 );
1547 END; -- insert_row
1548
1549 ----------------------------------------
1550 -- PL/SQL TBL insert_row for:GTSV_TBL --
1551 ----------------------------------------
1552 PROCEDURE insert_row(
1553 p_api_version IN NUMBER,
1554 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1555 x_return_status OUT NOCOPY VARCHAR2,
1556 x_msg_count OUT NOCOPY NUMBER,
1557 x_msg_data OUT NOCOPY VARCHAR2,
1558 p_gtsv_tbl IN gtsv_tbl_type,
1559 x_gtsv_tbl OUT NOCOPY gtsv_tbl_type) IS
1560
1561 l_api_version CONSTANT NUMBER := 1;
1562 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1563 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1564 i NUMBER := 0;
1565 l_overall_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1566 BEGIN
1567 okl_api.init_msg_list(p_init_msg_list);
1568
1569 -- Making sure PL/SQL table has records in it before passing
1570 IF (p_gtsv_tbl.COUNT > 0) THEN
1571 i := p_gtsv_tbl.FIRST;
1572 LOOP
1573
1574 insert_row (
1575 p_api_version => p_api_version,
1576 p_init_msg_list => okl_api.G_FALSE,
1577 x_return_status => x_return_status,
1578 x_msg_count => x_msg_count,
1579 x_msg_data => x_msg_data,
1580 p_gtsv_rec => p_gtsv_tbl(i),
1581 x_gtsv_rec => x_gtsv_tbl(i));
1582
1583 -- store the highest degree of error
1584 IF x_return_status <> okl_api.G_RET_STS_SUCCESS THEN
1585 IF l_overall_status <> okl_api.G_RET_STS_UNEXP_ERROR THEN
1586 l_overall_status := x_return_status;
1587 END IF;
1588 END IF;
1589 EXIT WHEN (i = p_gtsv_tbl.LAST);
1590 i := p_gtsv_tbl.NEXT(i);
1591 END LOOP;
1592
1593 -- return overall status
1594 x_return_status := l_overall_status;
1595 END IF;
1596 EXCEPTION
1597 WHEN okl_api.G_EXCEPTION_ERROR THEN
1598 x_return_status := okl_api.HANDLE_EXCEPTIONS
1599 (
1600 l_api_name,
1601 G_PKG_NAME,
1602 'okl_api.G_RET_STS_ERROR',
1603 x_msg_count,
1604 x_msg_data,
1605 '_PVT'
1606 );
1607 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1608 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1609 (
1610 l_api_name,
1611 G_PKG_NAME,
1612 'okl_api.G_RET_STS_UNEXP_ERROR',
1613 x_msg_count,
1614 x_msg_data,
1615 '_PVT'
1616 );
1617 WHEN OTHERS THEN
1618 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1619 (
1620 l_api_name,
1621 G_PKG_NAME,
1622 'OTHERS',
1623 x_msg_count,
1624 x_msg_data,
1625 '_PVT'
1626 );
1627 END insert_row;
1628
1629 ---------------------------------------------------------------------------
1630 -- PROCEDURE update_row
1631 ---------------------------------------------------------------------------
1632 -- Start of comments
1633 --
1634 -- Procedure Name : update_row
1635 -- Description : procedure for updating the records in
1636 -- table OKL_ST_GEN_TMPT_SETS
1637 -- Business Rules :
1638 -- Parameters :
1639 -- Version : 1.0
1640 -- End of comments
1641 --------------------------------------------------------------------------
1642
1643 PROCEDURE update_row(
1644 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1645 x_return_status OUT NOCOPY VARCHAR2,
1646 x_msg_count OUT NOCOPY NUMBER,
1647 x_msg_data OUT NOCOPY VARCHAR2,
1648 p_gts_rec IN gts_rec_type,
1649 x_gts_rec OUT NOCOPY gts_rec_type) IS
1650
1651 l_api_version CONSTANT NUMBER := 1;
1652 l_api_name CONSTANT VARCHAR2(30) := 'OPTS_update_row';
1653 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1654 l_gts_rec gts_rec_type := p_gts_rec;
1655 l_def_gts_rec gts_rec_type;
1656 l_row_notfound BOOLEAN := TRUE;
1657 ----------------------------------
1658 -- FUNCTION populate_new_record --
1659 ----------------------------------
1660 FUNCTION populate_new_record (
1661 p_gts_rec IN gts_rec_type,
1662 x_gts_rec OUT NOCOPY gts_rec_type
1663 ) RETURN VARCHAR2 IS
1664 l_gts_rec gts_rec_type;
1665 l_row_notfound BOOLEAN := TRUE;
1666 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1667 BEGIN
1668 x_gts_rec := p_gts_rec;
1669
1670 -- Get current database values
1671 l_gts_rec := get_rec(p_gts_rec, l_row_notfound);
1672
1673 IF (l_row_notfound) THEN
1674 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
1675 END IF;
1676 IF (x_gts_rec.id = okl_api.G_MISS_NUM)
1677 THEN
1678 x_gts_rec.id := l_gts_rec.id;
1679 END IF;
1680 IF (x_gts_rec.object_version_number = okl_api.G_MISS_NUM)
1681 THEN
1682 x_gts_rec.object_version_number := l_gts_rec.object_version_number;
1683 END IF;
1684 IF ( x_gts_rec.name = okl_api.G_MISS_CHAR )
1685 THEN
1686 x_gts_rec.name := l_gts_rec.name;
1687 END IF;
1688 IF ( x_gts_rec.description = okl_api.G_MISS_CHAR )
1689 THEN
1690 x_gts_rec.description := l_gts_rec.description;
1691 END IF;
1692 IF ( x_gts_rec.product_type = okl_api.G_MISS_CHAR )
1693 THEN
1694 x_gts_rec.product_type := l_gts_rec.product_type;
1695 END IF;
1696 IF ( x_gts_rec.tax_owner = okl_api.G_MISS_CHAR )
1697 THEN
1698 x_gts_rec.tax_owner := l_gts_rec.tax_owner;
1699 END IF;
1700 IF ( x_gts_rec.deal_type = okl_api.G_MISS_CHAR )
1701 THEN
1702 x_gts_rec.deal_type := l_gts_rec.deal_type;
1703 END IF;
1704 IF ( x_gts_rec.pricing_engine = okl_api.G_MISS_CHAR )
1705 THEN
1706 x_gts_rec.pricing_engine := l_gts_rec.pricing_engine;
1707 END IF;
1708 IF (x_gts_rec.org_id = okl_api.G_MISS_NUM)
1709 THEN
1710 x_gts_rec.org_id := l_gts_rec.org_id;
1711 END IF;
1712 IF (x_gts_rec.created_by = okl_api.G_MISS_NUM)
1713 THEN
1714 x_gts_rec.created_by := l_gts_rec.created_by;
1715 END IF;
1716 IF (x_gts_rec.creation_date = okl_api.G_MISS_DATE)
1717 THEN
1718 x_gts_rec.creation_date := l_gts_rec.creation_date;
1719 END IF;
1720 IF (x_gts_rec.last_updated_by = okl_api.G_MISS_NUM)
1721 THEN
1722 x_gts_rec.last_updated_by := l_gts_rec.last_updated_by;
1723 END IF;
1724 IF (x_gts_rec.last_update_date = okl_api.G_MISS_DATE)
1725 THEN
1726 x_gts_rec.last_update_date := l_gts_rec.last_update_date;
1727 END IF;
1728 IF (x_gts_rec.last_update_login = okl_api.G_MISS_NUM)
1729 THEN
1730 x_gts_rec.last_update_login := l_gts_rec.last_update_login;
1731 END IF;
1732
1733 if (x_gts_rec.interest_calc_meth_code = okl_api.g_miss_char)
1734 then
1735 x_gts_rec.interest_calc_meth_code := l_gts_rec.interest_calc_meth_code;
1736 end if;
1737
1738 if (x_gts_rec.revenue_recog_meth_code = okl_api.g_miss_char)
1739 then
1740 x_gts_rec.revenue_recog_meth_code := l_gts_rec.revenue_recog_meth_code;
1741 end if;
1742
1743 if (x_gts_rec.days_in_month_code = okl_api.g_miss_char)
1744 then
1745 x_gts_rec.days_in_month_code := l_gts_rec.days_in_month_code;
1746 end if;
1747
1748 if (x_gts_rec.days_in_yr_code = okl_api.g_miss_char)
1749 then
1750 x_gts_rec.days_in_yr_code := l_gts_rec.days_in_yr_code;
1751 end if;
1752
1753 if (x_gts_rec.isg_arrears_pay_dates_option = okl_api.g_miss_char)
1754 then
1755 x_gts_rec.isg_arrears_pay_dates_option := l_gts_rec.isg_arrears_pay_dates_option;
1756 end if;
1757
1758 RETURN(l_return_status);
1759 END populate_new_record;
1760 ---------------------------------------------
1761 -- Set_Attributes for:OKL_ST_GEN_TMPT_SETS --
1762 ---------------------------------------------
1763 FUNCTION Set_Attributes (
1764 p_gts_rec IN gts_rec_type,
1765 x_gts_rec OUT NOCOPY gts_rec_type
1766 ) RETURN VARCHAR2 IS
1767 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1768 BEGIN
1769 x_gts_rec := p_gts_rec;
1770 RETURN(l_return_status);
1771 END Set_Attributes;
1772 BEGIN
1773 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1774 p_init_msg_list,
1775 '_PVT',
1776 x_return_status);
1777 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1778 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1779 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1780 RAISE okl_api.G_EXCEPTION_ERROR;
1781 END IF;
1782
1783 --- Setting item attributes
1784 l_return_status := Set_Attributes(
1785 p_gts_rec, -- IN
1786 l_gts_rec); -- OUT
1787 --- If any errors happen abort API
1788 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1789 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1790 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1791 RAISE okl_api.G_EXCEPTION_ERROR;
1792 END IF;
1793
1794 l_return_status := populate_new_record(l_gts_rec, l_def_gts_rec);
1795 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1796 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1797 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1798 RAISE okl_api.G_EXCEPTION_ERROR;
1799 END IF;
1800
1801 UPDATE OKL_ST_GEN_TMPT_SETS
1802 SET ID = l_def_gts_rec.id
1803 ,OBJECT_VERSION_NUMBER = l_def_gts_rec.object_version_number
1804 ,NAME = l_def_gts_rec.name
1805 ,DESCRIPTION = l_def_gts_rec.description
1806 ,PRODUCT_TYPE = l_def_gts_rec.product_type
1807 ,TAX_OWNER = l_def_gts_rec.tax_owner
1808 ,DEAL_TYPE = l_def_gts_rec.deal_type
1809 ,PRICING_ENGINE = l_def_gts_rec.pricing_engine
1810 ,ORG_ID = l_def_gts_rec.org_id
1811 ,CREATED_BY = l_def_gts_rec.created_by
1812 ,CREATION_DATE = l_def_gts_rec.creation_date
1813 ,LAST_UPDATED_BY = l_def_gts_rec.last_updated_by
1814 ,LAST_UPDATE_DATE = l_def_gts_rec.last_update_date
1815 ,LAST_UPDATE_LOGIN = l_def_gts_rec.last_update_login
1816 ,INTEREST_CALC_METH_CODE = l_def_gts_rec.INTEREST_CALC_METH_CODE
1817 ,REVENUE_RECOG_METH_CODE = l_def_gts_rec.REVENUE_RECOG_METH_CODE
1818 ,DAYS_IN_MONTH_CODE = l_def_gts_rec.DAYS_IN_MONTH_CODE
1819 ,DAYS_IN_YR_CODE = l_def_gts_rec.DAYS_IN_YR_CODE
1820 ,ISG_ARREARS_PAY_DATES_OPTION = l_def_gts_rec.ISG_ARREARS_PAY_DATES_OPTION
1821 WHERE ID = l_def_gts_rec.id;
1822
1823 x_gts_rec := l_def_gts_rec;
1824 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1825 EXCEPTION
1826 WHEN okl_api.G_EXCEPTION_ERROR THEN
1827 x_return_status := okl_api.HANDLE_EXCEPTIONS
1828 (
1829 l_api_name,
1830 G_PKG_NAME,
1831 'okl_api.G_RET_STS_ERROR',
1832 x_msg_count,
1833 x_msg_data,
1834 '_PVT'
1835 );
1836 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1837 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1838 (
1839 l_api_name,
1840 G_PKG_NAME,
1841 'okl_api.G_RET_STS_UNEXP_ERROR',
1842 x_msg_count,
1843 x_msg_data,
1844 '_PVT'
1845 );
1846 WHEN OTHERS THEN
1847 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1848 (
1849 l_api_name,
1850 G_PKG_NAME,
1851 'OTHERS',
1852 x_msg_count,
1853 x_msg_data,
1854 '_PVT'
1855 );
1856 END update_row;
1857
1858 PROCEDURE update_row(
1859 p_api_version IN NUMBER,
1860 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1861 x_return_status OUT NOCOPY VARCHAR2,
1862 x_msg_count OUT NOCOPY NUMBER,
1863 x_msg_data OUT NOCOPY VARCHAR2,
1864 p_gtsv_rec IN gtsv_rec_type,
1865 x_gtsv_rec OUT NOCOPY gtsv_rec_type) IS
1866
1867 l_api_version CONSTANT NUMBER := 1;
1868 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1869 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1870 l_gtsv_rec gtsv_rec_type := p_gtsv_rec;
1871 l_def_gtsv_rec gtsv_rec_type;
1872 l_gts_rec gts_rec_type;
1873 lx_gts_rec gts_rec_type;
1874 -------------------------------
1875 -- FUNCTION fill_who_columns --
1876 -------------------------------
1877 FUNCTION fill_who_columns (
1878 p_gtsv_rec IN gtsv_rec_type
1879 ) RETURN gtsv_rec_type IS
1880 l_gtsv_rec gtsv_rec_type := p_gtsv_rec;
1881 BEGIN
1882 l_gtsv_rec.LAST_UPDATE_DATE := SYSDATE;
1883 l_gtsv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
1884 l_gtsv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1885 RETURN(l_gtsv_rec);
1886 END fill_who_columns;
1887 ----------------------------------
1888 -- FUNCTION populate_new_record --
1889 ----------------------------------
1890 FUNCTION populate_new_record (
1891 p_gtsv_rec IN gtsv_rec_type,
1892 x_gtsv_rec OUT NOCOPY gtsv_rec_type
1893 ) RETURN VARCHAR2 IS
1894 l_gtsv_rec gtsv_rec_type;
1895 l_row_notfound BOOLEAN := TRUE;
1896 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1897 BEGIN
1898 x_gtsv_rec := p_gtsv_rec;
1899
1900 -- Get current database values
1901 l_gtsv_rec := get_rec(p_gtsv_rec, l_row_notfound);
1902
1903 IF (l_row_notfound) THEN
1904 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
1905 END IF;
1906 IF (x_gtsv_rec.id = okl_api.G_MISS_NUM)
1907 THEN
1908 x_gtsv_rec.id := l_gts_rec.id;
1909 END IF;
1910 IF (x_gtsv_rec.object_version_number = okl_api.G_MISS_NUM)
1911 THEN
1912 x_gtsv_rec.object_version_number := l_gtsv_rec.object_version_number;
1913 END IF;
1914 IF ( x_gtsv_rec.name = okl_api.G_MISS_CHAR )
1915 THEN
1916 x_gtsv_rec.name := l_gtsv_rec.name;
1917 END IF;
1918 IF ( x_gtsv_rec.description = okl_api.G_MISS_CHAR )
1919 THEN
1920 x_gtsv_rec.description := l_gtsv_rec.description;
1921 END IF;
1922 IF ( x_gtsv_rec.product_type = okl_api.G_MISS_CHAR )
1923 THEN
1924 x_gtsv_rec.product_type := l_gtsv_rec.product_type;
1925 END IF;
1926 IF ( x_gtsv_rec.tax_owner = okl_api.G_MISS_CHAR )
1927 THEN
1928 x_gtsv_rec.tax_owner := l_gtsv_rec.tax_owner;
1929 END IF;
1930 IF ( x_gtsv_rec.deal_type = okl_api.G_MISS_CHAR )
1931 THEN
1932 x_gtsv_rec.deal_type := l_gtsv_rec.deal_type;
1933 END IF;
1934 IF ( x_gtsv_rec.pricing_engine = okl_api.G_MISS_CHAR )
1935 THEN
1936 x_gtsv_rec.pricing_engine := l_gtsv_rec.pricing_engine;
1937 END IF;
1938 IF (x_gtsv_rec.org_id = okl_api.G_MISS_NUM)
1939 THEN
1940 x_gtsv_rec.org_id := l_gtsv_rec.org_id;
1941 END IF;
1942 IF (x_gtsv_rec.created_by = okl_api.G_MISS_NUM)
1943 THEN
1944 x_gtsv_rec.created_by := l_gtsv_rec.created_by;
1945 END IF;
1946 IF (x_gtsv_rec.creation_date = okl_api.G_MISS_DATE)
1947 THEN
1948 x_gtsv_rec.creation_date := l_gtsv_rec.creation_date;
1949 END IF;
1950 IF (x_gtsv_rec.last_updated_by = okl_api.G_MISS_NUM)
1951 THEN
1952 x_gtsv_rec.last_updated_by := l_gtsv_rec.last_updated_by;
1953 END IF;
1954 IF (x_gtsv_rec.last_update_date = okl_api.G_MISS_DATE)
1955 THEN
1956 x_gtsv_rec.last_update_date := l_gtsv_rec.last_update_date;
1957 END IF;
1958 IF (x_gtsv_rec.last_update_login = okl_api.G_MISS_NUM)
1959 THEN
1960 x_gtsv_rec.last_update_login := l_gtsv_rec.last_update_login;
1961 END IF;
1962 IF ( x_gtsv_rec.INTEREST_CALC_METH_CODE = okl_api.G_MISS_CHAR )
1963 THEN
1964 x_gtsv_rec.INTEREST_CALC_METH_CODE := l_gtsv_rec.INTEREST_CALC_METH_CODE;
1965 END IF;
1966 IF ( x_gtsv_rec.REVENUE_RECOG_METH_CODE = okl_api.G_MISS_CHAR )
1967 THEN
1968 x_gtsv_rec.REVENUE_RECOG_METH_CODE := l_gtsv_rec.REVENUE_RECOG_METH_CODE;
1969 END IF;
1970 RETURN(l_return_status);
1971 END populate_new_record;
1972 ----------------------------------------------
1973 -- Set_Attributes for: OKL_ST_GEN_TMPT_SETS --
1974 ----------------------------------------------
1975 FUNCTION Set_Attributes (
1976 p_gtsv_rec IN gtsv_rec_type,
1977 x_gtsv_rec OUT NOCOPY gtsv_rec_type
1978 ) RETURN VARCHAR2 IS
1979 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1980 BEGIN
1981 x_gtsv_rec := p_gtsv_rec;
1982 RETURN(l_return_status);
1983 END Set_Attributes;
1984 BEGIN
1985 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1986 G_PKG_NAME,
1987 p_init_msg_list,
1988 l_api_version,
1989 p_api_version,
1990 '_PVT',
1991 x_return_status);
1992 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1993 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1994 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1995 RAISE okl_api.G_EXCEPTION_ERROR;
1996 END IF;
1997 --- Setting item attributes
1998 l_return_status := Set_Attributes(
1999 p_gtsv_rec, -- IN
2000 l_gtsv_rec); -- OUT
2001 --- If any errors happen abort API
2002 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2003 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2004 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2005 RAISE okl_api.G_EXCEPTION_ERROR;
2006 END IF;
2007
2008 l_return_status := populate_new_record(l_gtsv_rec, l_def_gtsv_rec);
2009 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2010 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2011 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2012 RAISE okl_api.G_EXCEPTION_ERROR;
2013 END IF;
2014 l_def_gtsv_rec := fill_who_columns(l_def_gtsv_rec);
2015
2016 --- Validate all non-missing attributes (Item Level Validation)
2017 l_return_status := Validate_Attributes(l_def_gtsv_rec);
2018
2019 --- If any errors happen abort API
2020 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2021 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2022 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2023 RAISE okl_api.G_EXCEPTION_ERROR;
2024 END IF;
2025
2026 l_return_status := Validate_Record(l_def_gtsv_rec);
2027 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2028 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2029 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2030 RAISE okl_api.G_EXCEPTION_ERROR;
2031 END IF;
2032
2033 --------------------------------------
2034 -- Move VIEW record to "Child" records
2035 --------------------------------------
2036 migrate(l_def_gtsv_rec, l_gts_rec);
2037 --------------------------------------------
2038 -- Call the UPDATE_ROW for each child record
2039 --------------------------------------------
2040 update_row(
2041 p_init_msg_list => p_init_msg_list,
2042 x_return_status => x_return_status,
2043 x_msg_count => x_msg_count,
2044 x_msg_data => x_msg_data,
2045 p_gts_rec => l_gts_rec,
2046 x_gts_rec => lx_gts_rec
2047 );
2048 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2049 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2050 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
2051 RAISE okl_api.G_EXCEPTION_ERROR;
2052 END IF;
2053
2054 migrate(lx_gts_rec, l_def_gtsv_rec);
2055
2056 x_gtsv_rec := l_def_gtsv_rec;
2057 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2058 EXCEPTION
2059 WHEN okl_api.G_EXCEPTION_ERROR THEN
2060 x_return_status := okl_api.HANDLE_EXCEPTIONS
2061 (
2062 l_api_name,
2063 G_PKG_NAME,
2064 'okl_api.G_RET_STS_ERROR',
2065 x_msg_count,
2066 x_msg_data,
2067 '_PVT'
2068 );
2069 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2070 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2071 (
2072 l_api_name,
2073 G_PKG_NAME,
2074 'okl_api.G_RET_STS_UNEXP_ERROR',
2075 x_msg_count,
2076 x_msg_data,
2077 '_PVT'
2078 );
2079 WHEN OTHERS THEN
2080 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2081 (
2082 l_api_name,
2083 G_PKG_NAME,
2084 'OTHERS',
2085 x_msg_count,
2086 x_msg_data,
2087 '_PVT'
2088 );
2089 END;
2090
2091 ----------------------------------------------------
2092 -- PL/SQL TBL update_row for:OKL_ST_GEN_TMPT_SETS --
2093 ----------------------------------------------------
2094 PROCEDURE update_row(
2095 p_api_version IN NUMBER,
2096 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2097 x_return_status OUT NOCOPY VARCHAR2,
2098 x_msg_count OUT NOCOPY NUMBER,
2099 x_msg_data OUT NOCOPY VARCHAR2,
2100 p_gtsv_tbl IN gtsv_tbl_type,
2101 x_gtsv_tbl OUT NOCOPY gtsv_tbl_type) IS
2102
2103 l_api_version CONSTANT NUMBER := 1;
2104 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2105 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2106 i NUMBER := 0;
2107
2108 l_overall_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2109 BEGIN
2110 okl_api.init_msg_list(p_init_msg_list);
2111 -- Make sure PL/SQL table has records in it before passing
2112 IF (p_gtsv_tbl.COUNT > 0) THEN
2113 i := p_gtsv_tbl.FIRST;
2114 LOOP
2115 update_row (
2116 p_api_version => p_api_version,
2117 p_init_msg_list => okl_api.G_FALSE,
2118 x_return_status => x_return_status,
2119 x_msg_count => x_msg_count,
2120 x_msg_data => x_msg_data,
2121 p_gtsv_rec => p_gtsv_tbl(i),
2122 x_gtsv_rec => x_gtsv_tbl(i));
2123
2124 IF x_return_status <> okl_api.G_RET_STS_SUCCESS THEN
2125 IF l_overall_status <> okl_api.G_RET_STS_UNEXP_ERROR THEN
2126 l_overall_status := x_return_status;
2127 END IF;
2128 END IF;
2129
2130 EXIT WHEN (i = p_gtsv_tbl.LAST);
2131 i := p_gtsv_tbl.NEXT(i);
2132 END LOOP;
2133
2134 x_return_status := l_overall_status;
2135
2136 END IF;
2137 EXCEPTION
2138 WHEN okl_api.G_EXCEPTION_ERROR THEN
2139 x_return_status := okl_api.HANDLE_EXCEPTIONS
2140 (
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 (
2151 l_api_name,
2152 G_PKG_NAME,
2153 'okl_api.G_RET_STS_UNEXP_ERROR',
2154 x_msg_count,
2155 x_msg_data,
2156 '_PVT'
2157 );
2158 WHEN OTHERS THEN
2159 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2160 (
2161 l_api_name,
2162 G_PKG_NAME,
2163 'OTHERS',
2164 x_msg_count,
2165 x_msg_data,
2166 '_PVT'
2167 );
2168 END update_row;
2169
2170
2171 ---------------------------------------------------------------------------
2172 -- PROCEDURE delete_row
2173 ---------------------------------------------------------------------------
2174 -----------------------------------------
2175 -- delete_row for:OKL_ST_GEN_TMPT_SETS --
2176 -----------------------------------------
2177 PROCEDURE delete_row(
2178 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2179 x_return_status OUT NOCOPY VARCHAR2,
2180 x_msg_count OUT NOCOPY NUMBER,
2181 x_msg_data OUT NOCOPY VARCHAR2,
2182 p_gts_rec IN gts_rec_type) IS
2183
2184 l_api_version CONSTANT NUMBER := 1;
2185 l_api_name CONSTANT VARCHAR2(30) := 'OPTS_delete_row';
2186 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2187 l_gts_rec gts_rec_type:= p_gts_rec;
2188 l_row_notfound BOOLEAN := TRUE;
2189 BEGIN
2190 l_return_status := okl_api.START_ACTIVITY(l_api_name,
2191 p_init_msg_list,
2192 '_PVT',
2193 x_return_status);
2194 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2195 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2196 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2197 RAISE okl_api.G_EXCEPTION_ERROR;
2198 END IF;
2199
2200 -- Actual deletion of the row
2201 DELETE FROM OKL_ST_GEN_TMPT_SETS
2202 WHERE ID = l_gts_rec.id;
2203
2204 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2205
2206 EXCEPTION
2207 WHEN okl_api.G_EXCEPTION_ERROR THEN
2208 x_return_status := okl_api.HANDLE_EXCEPTIONS
2209 (
2210 l_api_name,
2211 G_PKG_NAME,
2212 'okl_api.G_RET_STS_ERROR',
2213 x_msg_count,
2214 x_msg_data,
2215 '_PVT'
2216 );
2217 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2218 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2219 (
2220 l_api_name,
2221 G_PKG_NAME,
2222 'okl_api.G_RET_STS_UNEXP_ERROR',
2223 x_msg_count,
2224 x_msg_data,
2225 '_PVT'
2226 );
2227 WHEN OTHERS THEN
2228 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2229 (
2230 l_api_name,
2231 G_PKG_NAME,
2232 'OTHERS',
2233 x_msg_count,
2234 x_msg_data,
2235 '_PVT'
2236 );
2237 END delete_row;
2238
2239 ----------------------------------------
2240 -- delete_row for:OKL_ST_GEN_TMPT_SETS --
2241 ----------------------------------------
2242 PROCEDURE delete_row(
2243 p_api_version IN NUMBER,
2244 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2245 x_return_status OUT NOCOPY VARCHAR2,
2246 x_msg_count OUT NOCOPY NUMBER,
2247 x_msg_data OUT NOCOPY VARCHAR2,
2248 p_gtsv_rec IN gtsv_rec_type) IS
2249
2250 l_api_version CONSTANT NUMBER := 1;
2251 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2252 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2253 l_gtsv_rec gtsv_rec_type := p_gtsv_rec;
2254 l_gts_rec gts_rec_type;
2255 BEGIN
2256 l_return_status := okl_api.START_ACTIVITY(l_api_name,
2257 G_PKG_NAME,
2258 p_init_msg_list,
2259 l_api_version,
2260 p_api_version,
2261 '_PVT',
2262 x_return_status);
2263 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2264 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2265 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2266 RAISE okl_api.G_EXCEPTION_ERROR;
2267 END IF;
2268 --------------------------------------
2269 -- Move VIEW record to "Child" records
2270 --------------------------------------
2271 migrate(l_gtsv_rec, l_gts_rec);
2272 --------------------------------------------
2273 -- Call the DELETE_ROW for each child record
2274 --------------------------------------------
2275 delete_row(
2276 p_init_msg_list => p_init_msg_list,
2277 x_return_status => x_return_status,
2278 x_msg_count => x_msg_count,
2279 x_msg_data => x_msg_data,
2280 p_gts_rec => l_gts_rec
2281 );
2282
2283 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2284 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2285 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
2286 RAISE okl_api.G_EXCEPTION_ERROR;
2287 END IF;
2288 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2289 EXCEPTION
2290 WHEN okl_api.G_EXCEPTION_ERROR THEN
2291 x_return_status := okl_api.HANDLE_EXCEPTIONS
2292 (
2293 l_api_name,
2294 G_PKG_NAME,
2295 'okl_api.G_RET_STS_ERROR',
2296 x_msg_count,
2297 x_msg_data,
2298 '_PVT'
2299 );
2300 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2301 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2302 (
2303 l_api_name,
2304 G_PKG_NAME,
2305 'okl_api.G_RET_STS_UNEXP_ERROR',
2306 x_msg_count,
2307 x_msg_data,
2308 '_PVT'
2309 );
2310 WHEN OTHERS THEN
2311 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2312 (
2313 l_api_name,
2314 G_PKG_NAME,
2315 'OTHERS',
2316 x_msg_count,
2317 x_msg_data,
2318 '_PVT'
2319 );
2320 END delete_row;
2321
2322
2323 ----------------------------------------------------
2324 -- PL/SQL TBL delete_row for:OKL_ST_GEN_TMPT_SETS --
2325 ----------------------------------------------------
2326 PROCEDURE delete_row(
2327 p_api_version IN NUMBER,
2328 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2329 x_return_status OUT NOCOPY VARCHAR2,
2330 x_msg_count OUT NOCOPY NUMBER,
2331 x_msg_data OUT NOCOPY VARCHAR2,
2332 p_gtsv_tbl IN gtsv_tbl_type) IS
2333
2334 l_api_version CONSTANT NUMBER := 1;
2335 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2336 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2337 i NUMBER := 0;
2338
2339 l_overall_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2340
2341 BEGIN
2342 okl_api.init_msg_list(p_init_msg_list);
2343
2344 -- Make sure PL/SQL table has records in it before passing
2345 IF (p_gtsv_tbl.COUNT > 0) THEN
2346 i := p_gtsv_tbl.FIRST;
2347 LOOP
2348 delete_row (
2349 p_api_version => p_api_version,
2350 p_init_msg_list => okl_api.G_FALSE,
2351 x_return_status => x_return_status,
2352 x_msg_count => x_msg_count,
2353 x_msg_data => x_msg_data,
2354 p_gtsv_rec => p_gtsv_tbl(i));
2355
2356 IF x_return_status <> okl_api.G_RET_STS_SUCCESS THEN
2357 IF l_overall_status <> okl_api.G_RET_STS_UNEXP_ERROR THEN
2358 l_overall_status := x_return_status;
2359 END IF;
2360 END IF;
2361
2362 EXIT WHEN (i = p_gtsv_tbl.LAST);
2363 i := p_gtsv_tbl.NEXT(i);
2364 END LOOP;
2365
2366 x_return_status := l_overall_status;
2367 END IF;
2368 EXCEPTION
2369 WHEN okl_api.G_EXCEPTION_ERROR THEN
2370 x_return_status := okl_api.HANDLE_EXCEPTIONS
2371 (
2372 l_api_name,
2373 G_PKG_NAME,
2374 'okl_api.G_RET_STS_ERROR',
2375 x_msg_count,
2376 x_msg_data,
2377 '_PVT'
2378 );
2379 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2380 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2381 (
2382 l_api_name,
2383 G_PKG_NAME,
2384 'okl_api.G_RET_STS_UNEXP_ERROR',
2385 x_msg_count,
2386 x_msg_data,
2387 '_PVT'
2388 );
2389 WHEN OTHERS THEN
2390 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2391 (
2392 l_api_name,
2393 G_PKG_NAME,
2394 'OTHERS',
2395 x_msg_count,
2396 x_msg_data,
2397 '_PVT'
2398 );
2399 END delete_row;
2400 END okl_gts_pvt;