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