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