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