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