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