[Home] [Help]
PACKAGE BODY: APPS.OKL_POC_PVT
Source
1 PACKAGE BODY OKL_POC_PVT AS
2 /* $Header: OKLSPOCB.pls 120.2 2006/07/11 10:24:55 dkagrawa noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 NULL;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 NULL;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 NULL;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKL_POOL_CONTENTS_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_pocv_rec IN pocv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN pocv_rec_type IS
100 CURSOR okl_pocv_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 OBJECT_VERSION_NUMBER,
104 POL_ID,
105 KHR_ID,
106 KLE_ID,
107 STY_ID,
108 STM_ID,
109 STY_CODE,
110 POX_ID,
111 STREAMS_FROM_DATE,
112 STREAMS_TO_DATE,
113 TRANSACTION_NUMBER_IN,
114 TRANSACTION_NUMBER_OUT,
115 DATE_INACTIVE,
116 ATTRIBUTE_CATEGORY,
117 STATUS_CODE,
118 ATTRIBUTE1 ,
119 ATTRIBUTE2,
120 ATTRIBUTE3,
121 ATTRIBUTE4,
122 ATTRIBUTE5,
123 ATTRIBUTE6,
124 ATTRIBUTE7,
125 ATTRIBUTE8,
126 ATTRIBUTE9,
127 ATTRIBUTE10,
128 ATTRIBUTE11,
129 ATTRIBUTE12,
130 ATTRIBUTE13,
131 ATTRIBUTE14,
132 ATTRIBUTE15,
133 REQUEST_ID,
134 PROGRAM_APPLICATION_ID,
135 PROGRAM_ID,
136 PROGRAM_UPDATE_DATE,
137 CREATED_BY,
138 CREATION_DATE,
139 LAST_UPDATED_BY,
140 LAST_UPDATE_DATE,
141 LAST_UPDATE_LOGIN
142 FROM OKL_POOL_CONTENTS_V
143 WHERE OKL_POOL_CONTENTS_V.id = p_id;
144 l_okl_pocv_pk okl_pocv_pk_csr%ROWTYPE;
145 l_pocv_rec pocv_rec_type;
146 BEGIN
147 x_no_data_found := TRUE;
148 -- Get current database values
149 OPEN okl_pocv_pk_csr (p_pocv_rec.id);
150 FETCH okl_pocv_pk_csr INTO
151 l_pocv_rec.id,
152 l_pocv_rec.object_version_number,
153 l_pocv_rec.pol_id,
154 l_pocv_rec.khr_id,
155 l_pocv_rec.kle_id,
156 l_pocv_rec.sty_id,
157 l_pocv_rec.stm_id,
158 l_pocv_rec.sty_code,
159 l_pocv_rec.pox_id,
160 l_pocv_rec.streams_from_date,
161 l_pocv_rec.streams_to_date,
162 l_pocv_rec.transaction_number_in,
163 l_pocv_rec.transaction_number_out,
164 l_pocv_rec.date_inactive,
165 l_pocv_rec.attribute_category,
166 l_pocv_rec.status_code,
167 l_pocv_rec.attribute1 ,
168 l_pocv_rec.attribute2,
169 l_pocv_rec.attribute3,
170 l_pocv_rec.attribute4,
171 l_pocv_rec.attribute5,
172 l_pocv_rec.attribute6,
173 l_pocv_rec.attribute7,
174 l_pocv_rec.attribute8,
175 l_pocv_rec.attribute9,
176 l_pocv_rec.attribute10,
177 l_pocv_rec.attribute11,
178 l_pocv_rec.attribute12,
179 l_pocv_rec.attribute13,
180 l_pocv_rec.attribute14,
181 l_pocv_rec.attribute15,
182 l_pocv_rec.request_id,
183 l_pocv_rec.program_application_id,
184 l_pocv_rec.program_id,
185 l_pocv_rec.program_update_date,
186 l_pocv_rec.created_by,
187 l_pocv_rec.creation_date,
188 l_pocv_rec.last_updated_by,
189 l_pocv_rec.last_update_date,
190 l_pocv_rec.last_update_login;
191 x_no_data_found := okl_pocv_pk_csr%NOTFOUND;
192 CLOSE okl_pocv_pk_csr;
193 RETURN(l_pocv_rec);
194 END get_rec;
195
196 ------------------------------------------------------------------
197 -- This version of get_rec sets error messages if no data found --
198 ------------------------------------------------------------------
199 FUNCTION get_rec (
200 p_pocv_rec IN pocv_rec_type,
201 x_return_status OUT NOCOPY VARCHAR2
202 ) RETURN pocv_rec_type IS
203 l_pocv_rec pocv_rec_type;
204 l_row_notfound BOOLEAN := TRUE;
205 BEGIN
206 x_return_status := OKL_API.G_RET_STS_SUCCESS;
207 l_pocv_rec := get_rec(p_pocv_rec, l_row_notfound);
208 IF (l_row_notfound) THEN
209 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
210 x_return_status := OKL_API.G_RET_STS_ERROR;
211 END IF;
212 RETURN(l_pocv_rec);
213 END get_rec;
214 -----------------------------------------------------------
215 -- So we don't have to pass an "l_row_notfound" variable --
216 -----------------------------------------------------------
217 FUNCTION get_rec (
218 p_pocv_rec IN pocv_rec_type
219 ) RETURN pocv_rec_type IS
220 l_row_not_found BOOLEAN := TRUE;
221 BEGIN
222 RETURN(get_rec(p_pocv_rec, l_row_not_found));
223 END get_rec;
224 ---------------------------------------------------------------------------
225 -- FUNCTION get_rec for: okl_pool_contents
226 ---------------------------------------------------------------------------
227 FUNCTION get_rec (
228 p_poc_rec IN poc_rec_type,
229 x_no_data_found OUT NOCOPY BOOLEAN
230 ) RETURN poc_rec_type IS
231 CURSOR okl_pool_contents_pk_csr (p_id IN NUMBER) IS
232 SELECT
233 ID,
234 OBJECT_VERSION_NUMBER,
235 POL_ID,
236 KHR_ID,
237 KLE_ID,
238 STY_ID,
239 STM_ID,
240 STY_CODE,
241 POX_ID,
242 STREAMS_FROM_DATE,
243 STREAMS_TO_DATE,
244 TRANSACTION_NUMBER_IN,
245 TRANSACTION_NUMBER_OUT,
246 DATE_INACTIVE,
247 ATTRIBUTE_CATEGORY,
248 STATUS_CODE,
249 ATTRIBUTE1 ,
250 ATTRIBUTE2,
251 ATTRIBUTE3,
252 ATTRIBUTE4,
253 ATTRIBUTE5,
254 ATTRIBUTE6,
255 ATTRIBUTE7,
256 ATTRIBUTE8,
257 ATTRIBUTE9,
258 ATTRIBUTE10,
259 ATTRIBUTE11,
260 ATTRIBUTE12,
261 ATTRIBUTE13,
262 ATTRIBUTE14,
263 ATTRIBUTE15,
264 REQUEST_ID,
265 PROGRAM_APPLICATION_ID,
266 PROGRAM_ID,
267 PROGRAM_UPDATE_DATE,
268 CREATED_BY,
269 CREATION_DATE,
270 LAST_UPDATED_BY,
271 LAST_UPDATE_DATE,
272 LAST_UPDATE_LOGIN
273 FROM okl_pool_contents
274 WHERE okl_pool_contents.id = p_id;
275 l_okl_pool_contents_pk okl_pool_contents_pk_csr%ROWTYPE;
276 l_poc_rec poc_rec_type;
277 BEGIN
278 x_no_data_found := TRUE;
279 -- Get current database values
280 OPEN okl_pool_contents_pk_csr (p_poc_rec.id);
281 FETCH okl_pool_contents_pk_csr INTO
282 l_poc_rec.id,
283 l_poc_rec.object_version_number,
284 l_poc_rec.pol_id,
285 l_poc_rec.khr_id,
286 l_poc_rec.kle_id,
287 l_poc_rec.sty_id,
288 l_poc_rec.stm_id,
289 l_poc_rec.sty_code,
290 l_poc_rec.pox_id,
291 l_poc_rec.streams_from_date,
292 l_poc_rec.streams_to_date,
293 l_poc_rec.transaction_number_in,
294 l_poc_rec.transaction_number_out,
295 l_poc_rec.date_inactive,
296 l_poc_rec.attribute_category,
297 l_poc_rec.status_code,
298 l_poc_rec.attribute1 ,
299 l_poc_rec.attribute2,
300 l_poc_rec.attribute3,
301 l_poc_rec.attribute4,
302 l_poc_rec.attribute5,
303 l_poc_rec.attribute6,
304 l_poc_rec.attribute7,
305 l_poc_rec.attribute8,
306 l_poc_rec.attribute9,
307 l_poc_rec.attribute10,
308 l_poc_rec.attribute11,
309 l_poc_rec.attribute12,
310 l_poc_rec.attribute13,
311 l_poc_rec.attribute14,
312 l_poc_rec.attribute15,
313 l_poc_rec.request_id,
314 l_poc_rec.program_application_id,
315 l_poc_rec.program_id,
316 l_poc_rec.program_update_date,
317 l_poc_rec.created_by,
318 l_poc_rec.creation_date,
319 l_poc_rec.last_updated_by,
320 l_poc_rec.last_update_date,
321 l_poc_rec.last_update_login;
322 x_no_data_found := okl_pool_contents_pk_csr%NOTFOUND;
323 CLOSE okl_pool_contents_pk_csr;
324 RETURN(l_poc_rec);
325 END get_rec;
326
327 ------------------------------------------------------------------
328 -- This version of get_rec sets error messages if no data found --
329 ------------------------------------------------------------------
330 FUNCTION get_rec (
331 p_poc_rec IN poc_rec_type,
332 x_return_status OUT NOCOPY VARCHAR2
333 ) RETURN poc_rec_type IS
334 l_poc_rec poc_rec_type;
335 l_row_notfound BOOLEAN := TRUE;
336 BEGIN
337 x_return_status := OKL_API.G_RET_STS_SUCCESS;
338 l_poc_rec := get_rec(p_poc_rec, l_row_notfound);
339 IF (l_row_notfound) THEN
340 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
341 x_return_status := OKL_API.G_RET_STS_ERROR;
342 END IF;
343 RETURN(l_poc_rec);
344 END get_rec;
345 -----------------------------------------------------------
346 -- So we don't have to pass an "l_row_notfound" variable --
347 -----------------------------------------------------------
348 FUNCTION get_rec (
349 p_poc_rec IN poc_rec_type
350 ) RETURN poc_rec_type IS
351 l_row_not_found BOOLEAN := TRUE;
352 BEGIN
353 RETURN(get_rec(p_poc_rec, l_row_not_found));
354 END get_rec;
355
356 ---------------------------------------------------------------------------
357 -- FUNCTION null_out_defaults for: OKL_POOL_CONTENTS_V
358 -- mvasudev, hold this off for now
359 ---------------------------------------------------------------------------
360 FUNCTION null_out_defaults (
361 p_pocv_rec IN pocv_rec_type
362 ) RETURN pocv_rec_type IS
363 l_pocv_rec pocv_rec_type := p_pocv_rec;
364 BEGIN
365 IF (l_pocv_rec.id = OKL_API.G_MISS_NUM ) THEN
366 l_pocv_rec.id := NULL;
367 END IF;
368 IF (l_pocv_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
369 l_pocv_rec.object_version_number := NULL;
370 END IF;
371 IF (l_pocv_rec.pol_id = OKL_API.G_MISS_NUM ) THEN
372 l_pocv_rec.pol_id := NULL;
373 END IF;
374 IF (l_pocv_rec.khr_id = OKL_API.G_MISS_NUM ) THEN
375 l_pocv_rec.khr_id := NULL;
376 END IF;
377 IF (l_pocv_rec.kle_id = OKL_API.G_MISS_NUM ) THEN
378 l_pocv_rec.kle_id := NULL;
379 END IF;
380 IF (l_pocv_rec.sty_id = OKL_API.G_MISS_NUM ) THEN
381 l_pocv_rec.sty_id := NULL;
382 END IF;
383 IF (l_pocv_rec.stm_id = OKL_API.G_MISS_NUM ) THEN
384 l_pocv_rec.stm_id := NULL;
385 END IF;
386 IF (l_pocv_rec.sty_code = OKL_API.G_MISS_CHAR ) THEN
387 l_pocv_rec.sty_code := NULL;
388 END IF;
389 IF (l_pocv_rec.pox_id = OKL_API.G_MISS_NUM ) THEN
390 l_pocv_rec.pox_id := NULL;
391 END IF;
392 IF (l_pocv_rec.streams_from_date = OKL_API.G_MISS_DATE ) THEN
393 l_pocv_rec.streams_from_date := NULL;
394 END IF;
395 IF (l_pocv_rec.streams_to_date = OKL_API.G_MISS_DATE ) THEN
396 l_pocv_rec.streams_to_date := NULL;
397 END IF;
398 IF (l_pocv_rec.transaction_number_in = OKL_API.G_MISS_NUM ) THEN
399 l_pocv_rec.transaction_number_in := NULL;
400 END IF;
401 IF (l_pocv_rec.transaction_number_out = OKL_API.G_MISS_NUM ) THEN
402 l_pocv_rec.transaction_number_out := NULL;
403 END IF;
404 IF (l_pocv_rec.date_inactive = OKL_API.G_MISS_DATE ) THEN
405 l_pocv_rec.date_inactive := NULL;
406 END IF;
407 IF (l_pocv_rec.status_code = OKL_API.G_MISS_CHAR ) THEN
408 l_pocv_rec.status_code := NULL;
409 END IF;
410 IF (l_pocv_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
411 l_pocv_rec.attribute_category := NULL;
412 END IF;
413 IF (l_pocv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
414 l_pocv_rec.attribute1 := NULL;
415 END IF;
416 IF (l_pocv_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
417 l_pocv_rec.attribute2 := NULL;
418 END IF;
419 IF (l_pocv_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
420 l_pocv_rec.attribute3 := NULL;
421 END IF;
422 IF (l_pocv_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
423 l_pocv_rec.attribute4 := NULL;
424 END IF;
425 IF (l_pocv_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
426 l_pocv_rec.attribute5 := NULL;
427 END IF;
428 IF (l_pocv_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
429 l_pocv_rec.attribute6 := NULL;
430 END IF;
431 IF (l_pocv_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
432 l_pocv_rec.attribute7 := NULL;
433 END IF;
434 IF (l_pocv_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
435 l_pocv_rec.attribute8 := NULL;
436 END IF;
437 IF (l_pocv_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
438 l_pocv_rec.attribute9 := NULL;
439 END IF;
440 IF (l_pocv_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
441 l_pocv_rec.attribute10 := NULL;
442 END IF;
443 IF (l_pocv_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
444 l_pocv_rec.attribute11 := NULL;
445 END IF;
446 IF (l_pocv_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
447 l_pocv_rec.attribute12 := NULL;
448 END IF;
449 IF (l_pocv_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
450 l_pocv_rec.attribute13 := NULL;
451 END IF;
452 IF (l_pocv_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
453 l_pocv_rec.attribute14 := NULL;
454 END IF;
455 IF (l_pocv_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
456 l_pocv_rec.attribute15 := NULL;
457 END IF;
458 IF (l_pocv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
459 l_pocv_rec.attribute1 := NULL;
460 END IF;
461 IF (l_pocv_rec.request_id = OKL_API.G_MISS_NUM ) THEN
462 l_pocv_rec.request_id := NULL;
463 END IF;
464 IF (l_pocv_rec.program_application_id = OKL_API.G_MISS_NUM ) THEN
465 l_pocv_rec.program_application_id := NULL;
466 END IF;
467 IF (l_pocv_rec.program_id = OKL_API.G_MISS_NUM ) THEN
468 l_pocv_rec.program_id := NULL;
469 END IF;
470 IF (l_pocv_rec.program_update_date = OKL_API.G_MISS_DATE ) THEN
471 l_pocv_rec.program_update_date := NULL;
472 END IF;
473 IF (l_pocv_rec.created_by = OKL_API.G_MISS_NUM ) THEN
474 l_pocv_rec.created_by := NULL;
475 END IF;
476 IF (l_pocv_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
477 l_pocv_rec.creation_date := NULL;
478 END IF;
479 IF (l_pocv_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
480 l_pocv_rec.last_updated_by := NULL;
481 END IF;
482 IF (l_pocv_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
483 l_pocv_rec.last_update_date := NULL;
484 END IF;
485 IF (l_pocv_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
486 l_pocv_rec.last_update_login := NULL;
487 END IF;
488
489 RETURN(l_pocv_rec);
490 END null_out_defaults;
491
492 ---------------------------------
493 -- Validate_Attributes for: ID --
494 ---------------------------------
495 PROCEDURE validate_id(
496 x_return_status OUT NOCOPY VARCHAR2,
497 p_id IN NUMBER) IS
498 BEGIN
499 x_return_status := OKL_API.G_RET_STS_SUCCESS;
500 IF (p_id = OKL_API.G_MISS_NUM OR
501 p_id IS NULL)
502 THEN
503 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
504 x_return_status := OKL_API.G_RET_STS_ERROR;
505 RAISE G_EXCEPTION_HALT_VALIDATION;
506 END IF;
507 EXCEPTION
508 WHEN G_EXCEPTION_HALT_VALIDATION THEN
509 NULL;
510 WHEN OTHERS THEN
511 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
512 ,p_msg_name => G_UNEXPECTED_ERROR
513 ,p_token1 => G_SQLCODE_TOKEN
514 ,p_token1_value => SQLCODE
515 ,p_token2 => G_SQLERRM_TOKEN
516 ,p_token2_value => SQLERRM);
517 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
518 END validate_id;
519
520 ----------------------------------------------------
521 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
522 ----------------------------------------------------
523 PROCEDURE validate_object_version_number(
524 x_return_status OUT NOCOPY VARCHAR2,
525 p_object_version_number IN NUMBER) IS
526 BEGIN
527 x_return_status := OKL_API.G_RET_STS_SUCCESS;
528 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
529 p_object_version_number IS NULL)
530 THEN
531 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
532 x_return_status := OKL_API.G_RET_STS_ERROR;
533 RAISE G_EXCEPTION_HALT_VALIDATION;
534 END IF;
535 EXCEPTION
536 WHEN G_EXCEPTION_HALT_VALIDATION THEN
537 NULL;
538 WHEN OTHERS THEN
539 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
540 ,p_msg_name => G_UNEXPECTED_ERROR
541 ,p_token1 => G_SQLCODE_TOKEN
542 ,p_token1_value => SQLCODE
543 ,p_token2 => G_SQLERRM_TOKEN
544 ,p_token2_value => SQLERRM);
545 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
546 END validate_object_version_number;
547
548 -------------------------------------
549 -- Validate_Attributes for: pol_id --
550 -------------------------------------
551 PROCEDURE validate_pol_id(
552 x_return_status OUT NOCOPY VARCHAR2,
553 p_pol_id IN NUMBER) IS
554
555 l_dummy VARCHAR2(1) := '?';
556 l_row_not_found BOOLEAN := FALSE;
557
558 -- Cursor For OKL_POL_POT_FK;
559 CURSOR okl_polv_pk_csr (p_id IN OKL_POOL_CONTENTS_V.pol_id%TYPE) IS
560 SELECT '1'
561 FROM OKL_POOLS
562 WHERE OKL_POOLS.id = p_id;
563
564 BEGIN
565 x_return_status := OKL_API.G_RET_STS_SUCCESS;
566 IF (p_pol_id = OKL_API.G_MISS_NUM OR
567 p_pol_id IS NULL)
568 THEN
569 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'pol_id');
570 x_return_status := OKL_API.G_RET_STS_ERROR;
571 RAISE G_EXCEPTION_HALT_VALIDATION;
572 END IF;
573
574 OPEN okl_polv_pk_csr(p_pol_id);
575 FETCH okl_polv_pk_csr INTO l_dummy;
576 l_row_not_found := okl_polv_pk_csr%NOTFOUND;
577 CLOSE okl_polv_pk_csr;
578
579 IF l_row_not_found THEN
580 OKL_API.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'pol_id');
581 x_return_status := OKL_API.G_RET_STS_ERROR;
582 END IF;
583
584 EXCEPTION
585 WHEN G_EXCEPTION_HALT_VALIDATION THEN
586 NULL;
587 WHEN OTHERS THEN
588 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
589 ,p_msg_name => G_UNEXPECTED_ERROR
590 ,p_token1 => G_SQLCODE_TOKEN
591 ,p_token1_value => SQLCODE
592 ,p_token2 => G_SQLERRM_TOKEN
593 ,p_token2_value => SQLERRM);
594 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
595 IF okl_polv_pk_csr%ISOPEN THEN
596 CLOSE okl_polv_pk_csr;
597 END IF;
598 END validate_pol_id;
599
600 -------------------------------------
601 -- Validate_Attributes for: khr_id --
602 -------------------------------------
603 PROCEDURE validate_khr_id(
604 x_return_status OUT NOCOPY VARCHAR2,
605 p_khr_id IN NUMBER) IS
606
607 l_dummy VARCHAR2(1) := '?';
608 l_row_not_found BOOLEAN := FALSE;
609
610 -- Cursor For OKL_POL_KHR_FK;
611 CURSOR okl_khrv_pk_csr (p_id IN OKL_POOL_CONTENTS_V.khr_id%TYPE) IS
612 SELECT '1'
613 FROM OKL_K_HEADERS_V
614 WHERE OKL_K_HEADERS_V.id = p_id;
615
616 BEGIN
617 x_return_status := OKL_API.G_RET_STS_SUCCESS;
618
619 IF (p_khr_id = OKL_API.G_MISS_NUM OR
620 p_khr_id IS NULL)
621 THEN
622 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'khr_id');
623 x_return_status := OKL_API.G_RET_STS_ERROR;
624 RAISE G_EXCEPTION_HALT_VALIDATION;
625 END IF;
626
627 OPEN okl_khrv_pk_csr(p_khr_id);
628 FETCH okl_khrv_pk_csr INTO l_dummy;
629 l_row_not_found := okl_khrv_pk_csr%NOTFOUND;
630 CLOSE okl_khrv_pk_csr;
631
632 IF l_row_not_found THEN
633 OKL_API.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'khr_id');
634 x_return_status := OKL_API.G_RET_STS_ERROR;
635 END IF;
636
637 EXCEPTION
638 WHEN G_EXCEPTION_HALT_VALIDATION THEN
639 NULL;
640 WHEN OTHERS THEN
641 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
642 ,p_msg_name => G_UNEXPECTED_ERROR
643 ,p_token1 => G_SQLCODE_TOKEN
644 ,p_token1_value => SQLCODE
645 ,p_token2 => G_SQLERRM_TOKEN
646 ,p_token2_value => SQLERRM);
647 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
648 IF okl_khrv_pk_csr%ISOPEN THEN
649 CLOSE okl_khrv_pk_csr;
650 END IF;
651 END validate_khr_id;
652
653 -------------------------------------
654 -- Validate_Attributes for: kle_id --
655 -------------------------------------
656 PROCEDURE validate_kle_id(
657 x_return_status OUT NOCOPY VARCHAR2,
658 p_kle_id IN NUMBER) IS
659
660 l_dummy VARCHAR2(1) := '?';
661 l_row_not_found BOOLEAN := FALSE;
662
663 -- Cursor For OKL_POL_KHR_FK;
664 CURSOR okl_klev_pk_csr (p_id IN OKL_POOL_CONTENTS_V.kle_id%TYPE) IS
665 SELECT '1'
666 FROM OKL_K_LINES_V
667 WHERE OKL_K_LINES_V.id = p_id;
668
669 BEGIN
670 x_return_status := OKL_API.G_RET_STS_SUCCESS;
671
672 IF (p_kle_id = OKL_API.G_MISS_NUM OR
673 p_kle_id IS NULL)
674 THEN
675 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'kle_id');
676 x_return_status := OKL_API.G_RET_STS_ERROR;
677 RAISE G_EXCEPTION_HALT_VALIDATION;
678 END IF;
679
680 OPEN okl_klev_pk_csr(p_kle_id);
681 FETCH okl_klev_pk_csr INTO l_dummy;
682 l_row_not_found := okl_klev_pk_csr%NOTFOUND;
683 CLOSE okl_klev_pk_csr;
684
685 IF l_row_not_found THEN
686 OKL_API.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'kle_id');
687 x_return_status := OKL_API.G_RET_STS_ERROR;
688 END IF;
689
690 EXCEPTION
691 WHEN G_EXCEPTION_HALT_VALIDATION THEN
692 NULL;
693 WHEN OTHERS THEN
694 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
695 ,p_msg_name => G_UNEXPECTED_ERROR
696 ,p_token1 => G_SQLCODE_TOKEN
697 ,p_token1_value => SQLCODE
698 ,p_token2 => G_SQLERRM_TOKEN
699 ,p_token2_value => SQLERRM);
700 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
701 IF okl_klev_pk_csr%ISOPEN THEN
702 CLOSE okl_klev_pk_csr;
703 END IF;
704 END validate_kle_id;
705
706 -------------------------------------
707 -- Validate_Attributes for: sty_id --
708 -------------------------------------
709 PROCEDURE validate_sty_id(
710 x_return_status OUT NOCOPY VARCHAR2,
711 p_sty_id IN NUMBER) IS
712
713 l_dummy VARCHAR2(1) := '?';
714 l_row_not_found BOOLEAN := FALSE;
715
716 -- Cursor For OKL_POL_KHR_FK;
717 CURSOR okl_styv_pk_csr (p_id IN OKL_POOL_CONTENTS_V.sty_id%TYPE) IS
718 SELECT '1'
719 FROM OKL_STRM_TYPE_V
720 WHERE OKL_STRM_TYPE_V.id = p_id;
721
722 BEGIN
723 x_return_status := OKL_API.G_RET_STS_SUCCESS;
724
725 IF (p_sty_id = OKL_API.G_MISS_NUM OR
726 p_sty_id IS NULL)
727 THEN
728 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'sty_id');
729 x_return_status := OKL_API.G_RET_STS_ERROR;
730 RAISE G_EXCEPTION_HALT_VALIDATION;
731 END IF;
732
733 OPEN okl_styv_pk_csr(p_sty_id);
734 FETCH okl_styv_pk_csr INTO l_dummy;
735 l_row_not_found := okl_styv_pk_csr%NOTFOUND;
736 CLOSE okl_styv_pk_csr;
737
738 IF l_row_not_found THEN
739 OKL_API.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'sty_id');
740 x_return_status := OKL_API.G_RET_STS_ERROR;
741 END IF;
742
743 EXCEPTION
744 WHEN G_EXCEPTION_HALT_VALIDATION THEN
745 NULL;
746 WHEN OTHERS THEN
747 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
748 ,p_msg_name => G_UNEXPECTED_ERROR
749 ,p_token1 => G_SQLCODE_TOKEN
750 ,p_token1_value => SQLCODE
751 ,p_token2 => G_SQLERRM_TOKEN
752 ,p_token2_value => SQLERRM);
753 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
754 IF okl_styv_pk_csr%ISOPEN THEN
755 CLOSE okl_styv_pk_csr;
756 END IF;
757 END validate_sty_id;
758
759 -------------------------------------
760 -- Validate_Attributes for: stm_id --
761 -------------------------------------
762 PROCEDURE validate_stm_id(
763 x_return_status OUT NOCOPY VARCHAR2,
764 p_stm_id IN NUMBER) IS
765
766 l_dummy VARCHAR2(1) := '?';
767 l_row_not_found BOOLEAN := FALSE;
768
769 -- Cursor For OKL_POL_KHR_FK;
770 CURSOR okl_stm_pk_csr (p_id IN OKL_POOL_CONTENTS_V.stm_id%TYPE) IS
771 SELECT '1'
772 FROM OKL_STREAMS
773 WHERE OKL_STREAMS.id = p_id;
774
775 BEGIN
776 x_return_status := OKL_API.G_RET_STS_SUCCESS;
777
778 IF (p_stm_id = OKL_API.G_MISS_NUM OR
779 p_stm_id IS NULL)
780 THEN
781 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'p_stm_id');
782 x_return_status := OKL_API.G_RET_STS_ERROR;
783 RAISE G_EXCEPTION_HALT_VALIDATION;
784 END IF;
785
786 OPEN okl_stm_pk_csr(p_stm_id);
787 FETCH okl_stm_pk_csr INTO l_dummy;
788 l_row_not_found := okl_stm_pk_csr%NOTFOUND;
789 CLOSE okl_stm_pk_csr;
790
791 IF l_row_not_found THEN
792 OKL_API.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'stm_id');
793 x_return_status := OKL_API.G_RET_STS_ERROR;
794 END IF;
795
796 EXCEPTION
797 WHEN G_EXCEPTION_HALT_VALIDATION THEN
798 NULL;
799 WHEN OTHERS THEN
800 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
801 ,p_msg_name => G_UNEXPECTED_ERROR
802 ,p_token1 => G_SQLCODE_TOKEN
803 ,p_token1_value => SQLCODE
804 ,p_token2 => G_SQLERRM_TOKEN
805 ,p_token2_value => SQLERRM);
806 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
807 IF okl_stm_pk_csr%ISOPEN THEN
808 CLOSE okl_stm_pk_csr;
809 END IF;
810 END validate_stm_id;
811
812 -------------------------------------
813 -- Validate_Attributes for: pox_id --
814 -------------------------------------
815 PROCEDURE validate_pox_id(
816 x_return_status OUT NOCOPY VARCHAR2,
817 p_pox_id IN NUMBER) IS
818
819 l_dummy VARCHAR2(1) := '?';
820 l_row_not_found BOOLEAN := FALSE;
821
822 -- Cursor For OKL_POL_KHR_FK;
823 CURSOR okl_poxv_pk_csr (p_id IN OKL_POOL_CONTENTS_V.pox_id%TYPE) IS
824 SELECT '1'
825 FROM OKL_POOL_TRANSACTIONS_V
826 WHERE OKL_POOL_TRANSACTIONS_V.id = p_id;
827
828 BEGIN
829 x_return_status := OKL_API.G_RET_STS_SUCCESS;
830
831 IF (p_pox_id <> OKL_API.G_MISS_NUM AND p_pox_id IS NOT NULL)
832 THEN
833 OPEN okl_poxv_pk_csr(p_pox_id);
834 FETCH okl_poxv_pk_csr INTO l_dummy;
835 l_row_not_found := okl_poxv_pk_csr%NOTFOUND;
836 CLOSE okl_poxv_pk_csr;
837
838 IF l_row_not_found THEN
839 OKL_API.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'pox_id');
840 x_return_status := OKL_API.G_RET_STS_ERROR;
841 END IF;
842 END IF;
843
844 EXCEPTION
845 WHEN G_EXCEPTION_HALT_VALIDATION THEN
846 NULL;
847 WHEN OTHERS THEN
848 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
849 ,p_msg_name => G_UNEXPECTED_ERROR
850 ,p_token1 => G_SQLCODE_TOKEN
851 ,p_token1_value => SQLCODE
852 ,p_token2 => G_SQLERRM_TOKEN
853 ,p_token2_value => SQLERRM);
854 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
855 IF okl_poxv_pk_csr%ISOPEN THEN
856 CLOSE okl_poxv_pk_csr;
857 END IF;
858 END validate_pox_id;
859
860 --------------------------------------
861 -- Validate_Attributes for: streams_from_date --
862 --------------------------------------
863 PROCEDURE validate_streams_from_date(
864 x_return_status OUT NOCOPY VARCHAR2,
865 p_streams_from_date IN VARCHAR2) IS
866 BEGIN
867 x_return_status := OKL_API.G_RET_STS_SUCCESS;
868 IF (p_streams_from_date = OKL_API.G_MISS_DATE OR
869 p_streams_from_date IS NULL)
870 THEN
871 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'streams_from_date');
872 x_return_status := OKL_API.G_RET_STS_ERROR;
873 RAISE G_EXCEPTION_HALT_VALIDATION;
874 END IF;
875 EXCEPTION
876 WHEN G_EXCEPTION_HALT_VALIDATION THEN
877 NULL;
878 WHEN OTHERS THEN
879 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
880 ,p_msg_name => G_UNEXPECTED_ERROR
881 ,p_token1 => G_SQLCODE_TOKEN
882 ,p_token1_value => SQLCODE
883 ,p_token2 => G_SQLERRM_TOKEN
884 ,p_token2_value => SQLERRM);
885 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
886 END validate_streams_from_date;
887
888 --------------------------------------------
889 -- Validate_Attributes for: transaction_number_in --
890 --------------------------------------------
891 PROCEDURE validate_transaction_number_in(
892 x_return_status OUT NOCOPY VARCHAR2,
893 p_transaction_number_in IN VARCHAR2) IS
894
895 l_dummy VARCHAR2(1) := '?';
896 l_row_not_found BOOLEAN := FALSE;
897
898 -- Cursor For OKL_CURRENCIES;
899 CURSOR okl_poxv_trans_csr (p_trans_num IN OKL_POOL_CONTENTS_V.transaction_number_in%TYPE) IS
900 SELECT '1'
901 FROM OKL_POOL_TRANSACTIONS_V
902 WHERE OKL_POOL_TRANSACTIONS_V.transaction_number = p_trans_num;
903
904
905 BEGIN
906 x_return_status := OKL_API.G_RET_STS_SUCCESS;
907 IF (p_transaction_number_in = OKL_API.G_MISS_NUM AND p_transaction_number_in IS NOT NULL)
908 THEN
909 OPEN okl_poxv_trans_csr(p_transaction_number_in);
910 FETCH okl_poxv_trans_csr INTO l_dummy;
911 l_row_not_found := okl_poxv_trans_csr%NOTFOUND;
912 CLOSE okl_poxv_trans_csr;
913
914 IF l_row_not_found THEN
915 OKL_API.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'transaction_number_in');
916 x_return_status := OKL_API.G_RET_STS_ERROR;
917 END IF;
918 END IF;
919
920 EXCEPTION
921 WHEN G_EXCEPTION_HALT_VALIDATION THEN
922 NULL;
923 WHEN OTHERS THEN
924 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
925 ,p_msg_name => G_UNEXPECTED_ERROR
926 ,p_token1 => G_SQLCODE_TOKEN
927 ,p_token1_value => SQLCODE
928 ,p_token2 => G_SQLERRM_TOKEN
929 ,p_token2_value => SQLERRM);
930 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
931 IF okl_poxv_trans_csr%ISOPEN THEN
932 CLOSE okl_poxv_trans_csr;
933 END IF;
934 END validate_transaction_number_in;
935
936 --------------------------------------------
937 -- Validate_Attributes for: transaction_number_out --
938 --------------------------------------------
939 PROCEDURE validate_transaction_num_out(
940 x_return_status OUT NOCOPY VARCHAR2,
941 p_transaction_number_out IN VARCHAR2) IS
942
943 l_dummy VARCHAR2(1) := '?';
944 l_row_not_found BOOLEAN := FALSE;
945
946 -- Cursor For OKL_CURRENCIES;
947 CURSOR okl_poxv_trans_csr (p_trans_num IN OKL_POOL_CONTENTS_V.transaction_number_out%TYPE) IS
948 SELECT '1'
949 FROM OKL_POOL_TRANSACTIONS_V
950 WHERE OKL_POOL_TRANSACTIONS_V.transaction_number = p_trans_num;
951
952 BEGIN
953 x_return_status := OKL_API.G_RET_STS_SUCCESS;
954 IF (p_transaction_number_out = OKL_API.G_MISS_NUM AND p_transaction_number_out IS NOT NULL)
955 THEN
956 OPEN okl_poxv_trans_csr(p_transaction_number_out);
957 FETCH okl_poxv_trans_csr INTO l_dummy;
958 l_row_not_found := okl_poxv_trans_csr%NOTFOUND;
959 CLOSE okl_poxv_trans_csr;
960
961 IF l_row_not_found THEN
962 OKL_API.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'transaction_number_out');
963 x_return_status := OKL_API.G_RET_STS_ERROR;
964 END IF;
965 END IF;
966
967 EXCEPTION
968 WHEN G_EXCEPTION_HALT_VALIDATION THEN
969 NULL;
970 WHEN OTHERS THEN
971 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
972 ,p_msg_name => G_UNEXPECTED_ERROR
973 ,p_token1 => G_SQLCODE_TOKEN
974 ,p_token1_value => SQLCODE
975 ,p_token2 => G_SQLERRM_TOKEN
976 ,p_token2_value => SQLERRM);
977 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
978 IF okl_poxv_trans_csr%ISOPEN THEN
979 CLOSE okl_poxv_trans_csr;
980 END IF;
981 END validate_transaction_num_out;
982
983 ---------------------------------------------------------------------------
984 -- FUNCTION Validate_Attributes
985 ---------------------------------------------------------------------------
986 ---------------------------------------------------
987 -- Validate_Attributes for:OKL_POOL_CONTENTS_V --
988 ---------------------------------------------------
989 FUNCTION Validate_Attributes (
990 p_pocv_rec IN pocv_rec_type
991 ) RETURN VARCHAR2 IS
992 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
993 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
994 BEGIN
995 -----------------------------
996 -- Column Level Validation --
997 -----------------------------
998 -- ***
999 -- id
1000 -- ***
1001 validate_id(x_return_status, p_pocv_rec.id);
1002 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1003 l_return_status := x_return_status;
1004 RAISE G_EXCEPTION_HALT_VALIDATION;
1005 END IF;
1006
1007 -- ***
1008 -- object_version_number
1009 -- ***
1010 validate_object_version_number(x_return_status, p_pocv_rec.object_version_number);
1011 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1012 l_return_status := x_return_status;
1013 RAISE G_EXCEPTION_HALT_VALIDATION;
1014 END IF;
1015
1016 -- ***
1017 -- pol_id
1018 -- ***
1019 validate_pol_id(x_return_status, p_pocv_rec.pol_id);
1020 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1021 l_return_status := x_return_status;
1022 RAISE G_EXCEPTION_HALT_VALIDATION;
1023 END IF;
1024
1025 -- ***
1026 -- khr_id
1027 -- ***
1028 validate_khr_id(x_return_status, p_pocv_rec.khr_id);
1029 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1030 l_return_status := x_return_status;
1031 RAISE G_EXCEPTION_HALT_VALIDATION;
1032 END IF;
1033
1034 -- ***
1035 -- kle_id
1036 -- ***
1037 validate_kle_id(x_return_status, p_pocv_rec.kle_id);
1038 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1039 l_return_status := x_return_status;
1040 RAISE G_EXCEPTION_HALT_VALIDATION;
1041 END IF;
1042
1043 -- ***
1044 -- sty_id
1045 -- ***
1046 validate_sty_id(x_return_status, p_pocv_rec.sty_id);
1047 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1048 l_return_status := x_return_status;
1049 RAISE G_EXCEPTION_HALT_VALIDATION;
1050 END IF;
1051
1052 -- ***
1053 -- stm_id
1054 -- ***
1055 validate_stm_id(x_return_status, p_pocv_rec.stm_id);
1056 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1057 l_return_status := x_return_status;
1058 RAISE G_EXCEPTION_HALT_VALIDATION;
1059 END IF;
1060
1061 -- ***
1062 -- pox_id
1063 -- ***
1064 validate_pox_id(x_return_status, p_pocv_rec.pox_id);
1065 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1066 l_return_status := x_return_status;
1067 RAISE G_EXCEPTION_HALT_VALIDATION;
1068 END IF;
1069
1070 -- ***
1071 -- streams_from_date
1072 -- ***
1073 validate_streams_from_date(x_return_status, p_pocv_rec.streams_from_date);
1074 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1075 l_return_status := x_return_status;
1076 RAISE G_EXCEPTION_HALT_VALIDATION;
1077 END IF;
1078
1079 -- ***
1080 -- transaction_number_in
1081 -- ***
1082 validate_transaction_number_in(x_return_status, p_pocv_rec.transaction_number_in);
1083 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1084 l_return_status := x_return_status;
1085 RAISE G_EXCEPTION_HALT_VALIDATION;
1086 END IF;
1087
1088 -- ***
1089 -- transaction_number_out
1090 -- ***
1091 validate_transaction_num_out(x_return_status, p_pocv_rec.transaction_number_out);
1092 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1093 l_return_status := x_return_status;
1094 RAISE G_EXCEPTION_HALT_VALIDATION;
1095 END IF;
1096
1097 RETURN(l_return_status);
1098 EXCEPTION
1099 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1100 RETURN(l_return_status);
1101 WHEN OTHERS THEN
1102 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1103 ,p_msg_name => G_UNEXPECTED_ERROR
1104 ,p_token1 => G_SQLCODE_TOKEN
1105 ,p_token1_value => SQLCODE
1106 ,p_token2 => G_SQLERRM_TOKEN
1107 ,p_token2_value => SQLERRM);
1108 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1109 RETURN(l_return_status);
1110 END Validate_Attributes;
1111 ---------------------------------------------------------------------------
1112 -- PROCEDURE Validate_Record
1113 ---------------------------------------------------------------------------
1114 -----------------------------------------
1115 -- Validate Record for:OKL_POOL_CONTENTS_V --
1116 -----------------------------------------
1117 FUNCTION Validate_Record (
1118 p_pocv_rec IN pocv_rec_type,
1119 p_db_pocv_rec IN pocv_rec_type
1120 ) RETURN VARCHAR2 IS
1121 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1122 ------------------------------------
1123 -- FUNCTION validate_foreign_keys --
1124 ------------------------------------
1125 FUNCTION validate_foreign_keys (
1126 p_pocv_rec IN pocv_rec_type,
1127 p_db_pocv_rec IN pocv_rec_type
1128 ) RETURN VARCHAR2 IS
1129 item_not_found_error EXCEPTION;
1130 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1131 l_row_notfound BOOLEAN := TRUE;
1132 BEGIN
1133 RETURN (l_return_status);
1134 EXCEPTION
1135 WHEN item_not_found_error THEN
1136 l_return_status := OKL_API.G_RET_STS_ERROR;
1137 RETURN (l_return_status);
1138 END validate_foreign_keys;
1139 BEGIN
1140 l_return_status := validate_foreign_keys(p_pocv_rec, p_db_pocv_rec);
1141 RETURN (l_return_status);
1142 END Validate_Record;
1143 FUNCTION Validate_Record (
1144 p_pocv_rec IN pocv_rec_type
1145 ) RETURN VARCHAR2 IS
1146 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1147 l_db_pocv_rec pocv_rec_type := get_rec(p_pocv_rec);
1148 BEGIN
1149 l_return_status := Validate_Record(p_pocv_rec => p_pocv_rec,
1150 p_db_pocv_rec => l_db_pocv_rec);
1151 RETURN (l_return_status);
1152 END Validate_Record;
1153
1154
1155 ---------------------------------------------------------------------------
1156 -- PROCEDURE Migrate
1157 ---------------------------------------------------------------------------
1158 PROCEDURE migrate (
1159 p_from IN pocv_rec_type,
1160 p_to IN OUT NOCOPY poc_rec_type
1161 ) IS
1162 BEGIN
1163
1164 p_to.id := p_from.id;
1165 p_to.object_version_number := p_from.object_version_number;
1166 p_to.pol_id := p_from.pol_id;
1167 p_to.khr_id := p_from.khr_id;
1168 p_to.kle_id := p_from.kle_id;
1169 p_to.sty_id := p_from.sty_id;
1170 p_to.stm_id := p_from.stm_id;
1171 p_to.sty_code := p_from.sty_code;
1172 p_to.pox_id := p_from.pox_id;
1173 p_to.streams_from_date := p_from.streams_from_date;
1174 p_to.streams_to_date := p_from.streams_to_date;
1175 p_to.transaction_number_in := p_from.transaction_number_in;
1176 p_to.transaction_number_out := p_from.transaction_number_out;
1177 p_to.date_inactive := p_from.date_inactive;
1178 p_to.status_code := p_from.status_code;
1179 p_to.attribute_category := p_from.attribute_category;
1180 p_to.attribute1 := p_from.attribute1;
1181 p_to.attribute2 := p_from.attribute2;
1182 p_to.attribute3 := p_from.attribute3;
1183 p_to.attribute4 := p_from.attribute4;
1184 p_to.attribute5 := p_from.attribute5;
1185 p_to.attribute6 := p_from.attribute6;
1186 p_to.attribute7 := p_from.attribute7;
1187 p_to.attribute8 := p_from.attribute8;
1188 p_to.attribute9 := p_from.attribute9;
1189 p_to.attribute10 := p_from.attribute10;
1190 p_to.attribute11 := p_from.attribute11;
1191 p_to.attribute12 := p_from.attribute12;
1192 p_to.attribute13 := p_from.attribute13;
1193 p_to.attribute14 := p_from.attribute14;
1194 p_to.attribute15 := p_from.attribute15;
1195 p_to.request_id := p_from.request_id;
1196 p_to.program_application_id := p_from.program_application_id;
1197 p_to.program_id := p_from.program_id;
1198 p_to.program_update_date := p_from.program_update_date;
1199 p_to.created_by := p_from.created_by;
1200 p_to.creation_date := p_from.creation_date;
1201 p_to.last_updated_by := p_from.last_updated_by;
1202 p_to.last_update_date := p_from.last_update_date;
1203 p_to.last_update_login := p_from.last_update_login;
1204 END migrate;
1205 PROCEDURE migrate (
1206 p_from IN poc_rec_type,
1207 p_to IN OUT NOCOPY pocv_rec_type
1208 ) IS
1209 BEGIN
1210 p_to.id := p_from.id;
1211 p_to.object_version_number := p_from.object_version_number;
1212 p_to.pol_id := p_from.pol_id;
1213 p_to.khr_id := p_from.khr_id;
1214 p_to.kle_id := p_from.kle_id;
1215 p_to.sty_id := p_from.sty_id;
1216 p_to.stm_id := p_from.stm_id;
1217 p_to.sty_code := p_from.sty_code;
1218 p_to.pox_id := p_from.pox_id;
1219 p_to.streams_from_date := p_from.streams_from_date;
1220 p_to.streams_to_date := p_from.streams_to_date;
1221 p_to.transaction_number_in := p_from.transaction_number_in;
1222 p_to.transaction_number_out := p_from.transaction_number_out;
1223 p_to.date_inactive := p_from.date_inactive;
1224 p_to.status_code := p_from.status_code;
1225 p_to.attribute_category := p_from.attribute_category;
1226 p_to.attribute1 := p_from.attribute1;
1227 p_to.attribute2 := p_from.attribute2;
1228 p_to.attribute3 := p_from.attribute3;
1229 p_to.attribute4 := p_from.attribute4;
1230 p_to.attribute5 := p_from.attribute5;
1231 p_to.attribute6 := p_from.attribute6;
1232 p_to.attribute7 := p_from.attribute7;
1233 p_to.attribute8 := p_from.attribute8;
1234 p_to.attribute9 := p_from.attribute9;
1235 p_to.attribute10 := p_from.attribute10;
1236 p_to.attribute11 := p_from.attribute11;
1237 p_to.attribute12 := p_from.attribute12;
1238 p_to.attribute13 := p_from.attribute13;
1239 p_to.attribute14 := p_from.attribute14;
1240 p_to.attribute15 := p_from.attribute15;
1241 p_to.request_id := p_from.request_id;
1242 p_to.program_application_id := p_from.program_application_id;
1243 p_to.program_id := p_from.program_id;
1244 p_to.program_update_date := p_from.program_update_date;
1245 p_to.created_by := p_from.created_by;
1246 p_to.creation_date := p_from.creation_date;
1247 p_to.last_updated_by := p_from.last_updated_by;
1248 p_to.last_update_date := p_from.last_update_date;
1249 p_to.last_update_login := p_from.last_update_login;
1250 END migrate;
1251 ---------------------------------------------------------------------------
1252 -- PROCEDURE validate_row
1253 ---------------------------------------------------------------------------
1254 --------------------------------------------
1255 -- validate_row for:OKL_POOL_CONTENTS_V --
1256 --------------------------------------------
1257 PROCEDURE validate_row(
1258 p_api_version IN NUMBER,
1259 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1260 x_return_status OUT NOCOPY VARCHAR2,
1261 x_msg_count OUT NOCOPY NUMBER,
1262 x_msg_data OUT NOCOPY VARCHAR2,
1263 p_pocv_rec IN pocv_rec_type) IS
1264
1265 l_api_version CONSTANT NUMBER := 1;
1266 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1267 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1268 l_pocv_rec pocv_rec_type := p_pocv_rec;
1269 l_poc_rec poc_rec_type;
1270 l_poc_rec poc_rec_type;
1271 BEGIN
1272 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1273 G_PKG_NAME,
1274 p_init_msg_list,
1275 l_api_version,
1276 p_api_version,
1277 '_PVT',
1278 x_return_status);
1279 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1280 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1281 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1282 RAISE OKL_API.G_EXCEPTION_ERROR;
1283 END IF;
1284 --- Validate all non-missing attributes (Item Level Validation)
1285 l_return_status := Validate_Attributes(l_pocv_rec);
1286 --- If any errors happen abort API
1287 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1288 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1289 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1290 RAISE OKL_API.G_EXCEPTION_ERROR;
1291 END IF;
1292 l_return_status := Validate_Record(l_pocv_rec);
1293 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1294 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1295 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1296 RAISE OKL_API.G_EXCEPTION_ERROR;
1297 END IF;
1298 x_return_status := l_return_status;
1299 EXCEPTION
1300 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1301 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1302 (
1303 l_api_name,
1304 G_PKG_NAME,
1305 'OKL_API.G_RET_STS_ERROR',
1306 x_msg_count,
1307 x_msg_data,
1308 '_PVT'
1309 );
1310 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1311 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1312 (
1313 l_api_name,
1314 G_PKG_NAME,
1315 'OKL_API.G_RET_STS_UNEXP_ERROR',
1316 x_msg_count,
1317 x_msg_data,
1318 '_PVT'
1319 );
1320 WHEN OTHERS THEN
1321 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1322 (
1323 l_api_name,
1324 G_PKG_NAME,
1325 'OTHERS',
1326 x_msg_count,
1327 x_msg_data,
1328 '_PVT'
1329 );
1330 END validate_row;
1331 -------------------------------------------------------
1332 -- PL/SQL TBL validate_row for:OKL_POOL_CONTENTS_V --
1333 -------------------------------------------------------
1334 PROCEDURE validate_row(
1335 p_api_version IN NUMBER,
1336 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1337 x_return_status OUT NOCOPY VARCHAR2,
1338 x_msg_count OUT NOCOPY NUMBER,
1339 x_msg_data OUT NOCOPY VARCHAR2,
1340 p_pocv_tbl IN pocv_tbl_type,
1341 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1342
1343 l_api_version CONSTANT NUMBER := 1;
1344 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
1345 i NUMBER := 0;
1346 BEGIN
1347 OKL_API.init_msg_list(p_init_msg_list);
1348 -- Make sure PL/SQL table has records in it before passing
1349 IF (p_pocv_tbl.COUNT > 0) THEN
1350 i := p_pocv_tbl.FIRST;
1351 LOOP
1352 DECLARE
1353 l_error_rec OKL_API.ERROR_REC_TYPE;
1354 BEGIN
1355 l_error_rec.api_name := l_api_name;
1356 l_error_rec.api_package := G_PKG_NAME;
1357 l_error_rec.idx := i;
1358 validate_row (
1359 p_api_version => p_api_version,
1360 p_init_msg_list => OKL_API.G_FALSE,
1361 x_return_status => l_error_rec.error_type,
1362 x_msg_count => l_error_rec.msg_count,
1363 x_msg_data => l_error_rec.msg_data,
1364 p_pocv_rec => p_pocv_tbl(i));
1365 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1366 l_error_rec.SQLCODE := SQLCODE;
1367 load_error_tbl(l_error_rec, px_error_tbl);
1368 ELSE
1369 x_msg_count := l_error_rec.msg_count;
1370 x_msg_data := l_error_rec.msg_data;
1371 END IF;
1372 EXCEPTION
1373 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1374 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1375 l_error_rec.SQLCODE := SQLCODE;
1376 load_error_tbl(l_error_rec, px_error_tbl);
1377 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1378 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1379 l_error_rec.SQLCODE := SQLCODE;
1380 load_error_tbl(l_error_rec, px_error_tbl);
1381 WHEN OTHERS THEN
1382 l_error_rec.error_type := 'OTHERS';
1383 l_error_rec.SQLCODE := SQLCODE;
1384 load_error_tbl(l_error_rec, px_error_tbl);
1385 END;
1386 EXIT WHEN (i = p_pocv_tbl.LAST);
1387 i := p_pocv_tbl.NEXT(i);
1388 END LOOP;
1389 END IF;
1390 -- Loop through the error_tbl to find the error with the highest severity
1391 -- and return it.
1392 x_return_status := find_highest_exception(px_error_tbl);
1393 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1394 EXCEPTION
1395 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1396 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1397 (
1398 l_api_name,
1399 G_PKG_NAME,
1400 'OKL_API.G_RET_STS_ERROR',
1401 x_msg_count,
1402 x_msg_data,
1403 '_PVT'
1404 );
1405 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1406 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1407 (
1408 l_api_name,
1409 G_PKG_NAME,
1410 'OKL_API.G_RET_STS_UNEXP_ERROR',
1411 x_msg_count,
1412 x_msg_data,
1413 '_PVT'
1414 );
1415 WHEN OTHERS THEN
1416 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1417 (
1418 l_api_name,
1419 G_PKG_NAME,
1420 'OTHERS',
1421 x_msg_count,
1422 x_msg_data,
1423 '_PVT'
1424 );
1425 END validate_row;
1426
1427 -------------------------------------------------------
1428 -- PL/SQL TBL validate_row for:OKL_POOL_CONTENTS_V --
1429 -------------------------------------------------------
1430 PROCEDURE validate_row(
1431 p_api_version IN NUMBER,
1432 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1433 x_return_status OUT NOCOPY VARCHAR2,
1434 x_msg_count OUT NOCOPY NUMBER,
1435 x_msg_data OUT NOCOPY VARCHAR2,
1436 p_pocv_tbl IN pocv_tbl_type) IS
1437
1438 l_api_version CONSTANT NUMBER := 1;
1439 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1440 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1441 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1442 BEGIN
1443 OKL_API.init_msg_list(p_init_msg_list);
1444 -- Make sure PL/SQL table has records in it before passing
1445 IF (p_pocv_tbl.COUNT > 0) THEN
1446 validate_row (
1447 p_api_version => p_api_version,
1448 p_init_msg_list => OKL_API.G_FALSE,
1449 x_return_status => x_return_status,
1450 x_msg_count => x_msg_count,
1451 x_msg_data => x_msg_data,
1452 p_pocv_tbl => p_pocv_tbl,
1453 px_error_tbl => l_error_tbl);
1454 END IF;
1455 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1456 EXCEPTION
1457 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1458 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1459 (
1460 l_api_name,
1461 G_PKG_NAME,
1462 'OKL_API.G_RET_STS_ERROR',
1463 x_msg_count,
1464 x_msg_data,
1465 '_PVT'
1466 );
1467 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1468 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1469 (
1470 l_api_name,
1471 G_PKG_NAME,
1472 'OKL_API.G_RET_STS_UNEXP_ERROR',
1473 x_msg_count,
1474 x_msg_data,
1475 '_PVT'
1476 );
1477 WHEN OTHERS THEN
1478 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1479 (
1480 l_api_name,
1481 G_PKG_NAME,
1482 'OTHERS',
1483 x_msg_count,
1484 x_msg_data,
1485 '_PVT'
1486 );
1487 END validate_row;
1488
1489 ---------------------------------------------------------------------------
1490 -- PROCEDURE insert_row
1491 ---------------------------------------------------------------------------
1492 ----------------------------------------
1493 -- insert_row for:okl_pool_contents --
1494 ----------------------------------------
1495 PROCEDURE insert_row(
1496 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1497 x_return_status OUT NOCOPY VARCHAR2,
1498 x_msg_count OUT NOCOPY NUMBER,
1499 x_msg_data OUT NOCOPY VARCHAR2,
1500 p_poc_rec IN poc_rec_type,
1501 x_poc_rec OUT NOCOPY poc_rec_type) IS
1502
1503 l_api_version CONSTANT NUMBER := 1;
1504 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1505 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1506 l_poc_rec poc_rec_type := p_poc_rec;
1507 l_def_poc_rec poc_rec_type;
1508 --------------------------------------------
1509 -- Set_Attributes for:okl_pool_contents --
1510 --------------------------------------------
1511 FUNCTION Set_Attributes (
1512 p_poc_rec IN poc_rec_type,
1513 x_poc_rec OUT NOCOPY poc_rec_type
1514 ) RETURN VARCHAR2 IS
1515 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1516 BEGIN
1517 x_poc_rec := p_poc_rec;
1518 RETURN(l_return_status);
1519 END Set_Attributes;
1520 BEGIN
1521 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1522 p_init_msg_list,
1523 '_PVT',
1524 x_return_status);
1525 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1526 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1527 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1528 RAISE OKL_API.G_EXCEPTION_ERROR;
1529 END IF;
1530 --- Setting item atributes
1531 l_return_status := Set_Attributes(
1532 p_poc_rec, -- IN
1533 l_poc_rec); -- OUT
1534 --- If any errors happen abort API
1535 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1536 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1537 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1538 RAISE OKL_API.G_EXCEPTION_ERROR;
1539 END IF;
1540 INSERT INTO okl_pool_contents(
1541 ID,
1542 OBJECT_VERSION_NUMBER,
1543 POL_ID,
1544 KHR_ID,
1545 KLE_ID,
1546 STY_ID,
1547 STM_ID,
1548 STY_CODE,
1549 POX_ID,
1550 STREAMS_FROM_DATE,
1551 STREAMS_TO_DATE,
1552 TRANSACTION_NUMBER_IN,
1553 TRANSACTION_NUMBER_OUT,
1554 DATE_INACTIVE,
1555 ATTRIBUTE_CATEGORY,
1556 STATUS_CODE,
1557 ATTRIBUTE1,
1558 ATTRIBUTE2,
1559 ATTRIBUTE3,
1560 ATTRIBUTE4,
1561 ATTRIBUTE5,
1562 ATTRIBUTE6,
1563 ATTRIBUTE7,
1564 ATTRIBUTE8,
1565 ATTRIBUTE9,
1566 ATTRIBUTE10,
1567 ATTRIBUTE11,
1568 ATTRIBUTE12,
1569 ATTRIBUTE13,
1570 ATTRIBUTE14,
1571 ATTRIBUTE15,
1572 REQUEST_ID,
1573 PROGRAM_APPLICATION_ID,
1574 PROGRAM_ID,
1575 PROGRAM_UPDATE_DATE,
1576 CREATED_BY,
1577 CREATION_DATE,
1578 LAST_UPDATED_BY,
1579 LAST_UPDATE_DATE,
1580 LAST_UPDATE_LOGIN)
1581 VALUES (
1582 l_poc_rec.id,
1583 l_poc_rec.object_version_number,
1584 l_poc_rec.pol_id,
1585 l_poc_rec.khr_id,
1586 l_poc_rec.kle_id,
1587 l_poc_rec.sty_id,
1588 l_poc_rec.stm_id,
1589 l_poc_rec.sty_code,
1590 l_poc_rec.pox_id,
1591 l_poc_rec.streams_from_date,
1592 l_poc_rec.streams_to_date,
1593 l_poc_rec.transaction_number_in,
1594 l_poc_rec.transaction_number_out,
1595 l_poc_rec.date_inactive,
1596 l_poc_rec.attribute_category,
1597 l_poc_rec.status_code,
1598 l_poc_rec.attribute1 ,
1599 l_poc_rec.attribute2,
1600 l_poc_rec.attribute3,
1601 l_poc_rec.attribute4,
1602 l_poc_rec.attribute5,
1603 l_poc_rec.attribute6,
1604 l_poc_rec.attribute7,
1605 l_poc_rec.attribute8,
1606 l_poc_rec.attribute9,
1607 l_poc_rec.attribute10,
1608 l_poc_rec.attribute11,
1609 l_poc_rec.attribute12,
1610 l_poc_rec.attribute13,
1611 l_poc_rec.attribute14,
1612 l_poc_rec.attribute15,
1613 l_poc_rec.request_id,
1614 l_poc_rec.program_application_id,
1615 l_poc_rec.program_id,
1616 l_poc_rec.program_update_date,
1617 l_poc_rec.created_by,
1618 l_poc_rec.creation_date,
1619 l_poc_rec.last_updated_by,
1620 l_poc_rec.last_update_date,
1621 l_poc_rec.last_update_login);
1622 -- Set OUT values
1623 x_poc_rec := l_poc_rec;
1624 x_return_status := l_return_status;
1625 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1626 EXCEPTION
1627 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1628 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1629 (
1630 l_api_name,
1631 G_PKG_NAME,
1632 'OKL_API.G_RET_STS_ERROR',
1633 x_msg_count,
1634 x_msg_data,
1635 '_PVT'
1636 );
1637 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1638 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1639 (
1640 l_api_name,
1641 G_PKG_NAME,
1642 'OKL_API.G_RET_STS_UNEXP_ERROR',
1643 x_msg_count,
1644 x_msg_data,
1645 '_PVT'
1646 );
1647 WHEN OTHERS THEN
1648 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1649 (
1650 l_api_name,
1651 G_PKG_NAME,
1652 'OTHERS',
1653 x_msg_count,
1654 x_msg_data,
1655 '_PVT'
1656 );
1657 END insert_row;
1658 -------------------------------------------
1659 -- insert_row for :OKL_POOL_CONTENTS_V --
1660 -------------------------------------------
1661 PROCEDURE insert_row(
1662 p_api_version IN NUMBER,
1663 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1664 x_return_status OUT NOCOPY VARCHAR2,
1665 x_msg_count OUT NOCOPY NUMBER,
1666 x_msg_data OUT NOCOPY VARCHAR2,
1667 p_pocv_rec IN pocv_rec_type,
1668 x_pocv_rec OUT NOCOPY pocv_rec_type) IS
1669
1670 l_api_version CONSTANT NUMBER := 1;
1671 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1672 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1673 l_pocv_rec pocv_rec_type := p_pocv_rec;
1674 l_def_pocv_rec pocv_rec_type;
1675 l_poc_rec poc_rec_type;
1676 lx_poc_rec poc_rec_type;
1677 -------------------------------
1678 -- FUNCTION fill_who_columns --
1679 -------------------------------
1680 FUNCTION fill_who_columns (
1681 p_pocv_rec IN pocv_rec_type
1682 ) RETURN pocv_rec_type IS
1683 l_pocv_rec pocv_rec_type := p_pocv_rec;
1684 BEGIN
1685 l_pocv_rec.CREATION_DATE := SYSDATE;
1686 l_pocv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1687 l_pocv_rec.LAST_UPDATE_DATE := l_pocv_rec.CREATION_DATE;
1688 l_pocv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1689 l_pocv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1690 RETURN(l_pocv_rec);
1691 END fill_who_columns;
1692 ----------------------------------------------
1693 -- Set_Attributes for:OKL_POOL_CONTENTS_V --
1694 ----------------------------------------------
1695 FUNCTION Set_Attributes (
1696 p_pocv_rec IN pocv_rec_type,
1697 x_pocv_rec OUT NOCOPY pocv_rec_type
1698 ) RETURN VARCHAR2 IS
1699 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1700 BEGIN
1701 x_pocv_rec := p_pocv_rec;
1702 x_pocv_rec.OBJECT_VERSION_NUMBER := 1;
1703
1704 -- concurrent program columns
1705 SELECT DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL,Fnd_Global.CONC_REQUEST_ID),
1706 DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL,Fnd_Global.PROG_APPL_ID),
1707 DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL,Fnd_Global.CONC_PROGRAM_ID),
1708 DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
1709 INTO x_pocv_rec.REQUEST_ID
1710 ,x_pocv_rec.PROGRAM_APPLICATION_ID
1711 ,x_pocv_rec.PROGRAM_ID
1712 ,x_pocv_rec.PROGRAM_UPDATE_DATE
1713 FROM DUAL;
1714 RETURN(l_return_status);
1715 END Set_Attributes;
1716 BEGIN
1717 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1718 G_PKG_NAME,
1719 p_init_msg_list,
1720 l_api_version,
1721 p_api_version,
1722 '_PVT',
1723 x_return_status);
1724 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1725 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1726 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1727 RAISE OKL_API.G_EXCEPTION_ERROR;
1728 END IF;
1729 l_pocv_rec := null_out_defaults(p_pocv_rec);
1730 -- Set primary key value
1731 l_pocv_rec.ID := get_seq_id;
1732 -- Setting item attributes
1733 l_return_Status := Set_Attributes(
1734 l_pocv_rec, -- IN
1735 l_def_pocv_rec); -- OUT
1736 --- If any errors happen abort API
1737 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1738 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1739 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1740 RAISE OKL_API.G_EXCEPTION_ERROR;
1741 END IF;
1742 l_def_pocv_rec := fill_who_columns(l_def_pocv_rec);
1743 --- Validate all non-missing attributes (Item Level Validation)
1744 l_return_status := Validate_Attributes(l_def_pocv_rec);
1745 --- If any errors happen abort API
1746 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1747 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1748 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1749 RAISE OKL_API.G_EXCEPTION_ERROR;
1750 END IF;
1751 l_return_status := Validate_Record(l_def_pocv_rec);
1752 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1753 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1754 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1755 RAISE OKL_API.G_EXCEPTION_ERROR;
1756 END IF;
1757 -----------------------------------------
1758 -- Move VIEW record to "Child" records --
1759 -----------------------------------------
1760 migrate(l_def_pocv_rec, l_poc_rec);
1761 -----------------------------------------------
1762 -- Call the INSERT_ROW for each child record --
1763 -----------------------------------------------
1764 insert_row(
1765 p_init_msg_list,
1766 l_return_status,
1767 x_msg_count,
1768 x_msg_data,
1769 l_poc_rec,
1770 lx_poc_rec
1771 );
1772 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1773 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1774 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1775 RAISE OKL_API.G_EXCEPTION_ERROR;
1776 END IF;
1777 migrate(lx_poc_rec, l_def_pocv_rec);
1778 -- Set OUT values
1779 x_pocv_rec := l_def_pocv_rec;
1780 x_return_status := l_return_status;
1781 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1782 EXCEPTION
1783 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1784 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1785 (
1786 l_api_name,
1787 G_PKG_NAME,
1788 'OKL_API.G_RET_STS_ERROR',
1789 x_msg_count,
1790 x_msg_data,
1791 '_PVT'
1792 );
1793 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1794 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1795 (
1796 l_api_name,
1797 G_PKG_NAME,
1798 'OKL_API.G_RET_STS_UNEXP_ERROR',
1799 x_msg_count,
1800 x_msg_data,
1801 '_PVT'
1802 );
1803 WHEN OTHERS THEN
1804 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1805 (
1806 l_api_name,
1807 G_PKG_NAME,
1808 'OTHERS',
1809 x_msg_count,
1810 x_msg_data,
1811 '_PVT'
1812 );
1813 END insert_row;
1814 ----------------------------------------
1815 -- PL/SQL TBL insert_row for:pocv_TBL --
1816 ----------------------------------------
1817 PROCEDURE insert_row(
1818 p_api_version IN NUMBER,
1819 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1820 x_return_status OUT NOCOPY VARCHAR2,
1821 x_msg_count OUT NOCOPY NUMBER,
1822 x_msg_data OUT NOCOPY VARCHAR2,
1823 p_pocv_tbl IN pocv_tbl_type,
1824 x_pocv_tbl OUT NOCOPY pocv_tbl_type,
1825 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1826
1827 l_api_version CONSTANT NUMBER := 1;
1828 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1829 i NUMBER := 0;
1830 BEGIN
1831 OKL_API.init_msg_list(p_init_msg_list);
1832 -- Make sure PL/SQL table has records in it before passing
1833 IF (p_pocv_tbl.COUNT > 0) THEN
1834 i := p_pocv_tbl.FIRST;
1835 LOOP
1836 DECLARE
1837 l_error_rec OKL_API.ERROR_REC_TYPE;
1838 BEGIN
1839 l_error_rec.api_name := l_api_name;
1840 l_error_rec.api_package := G_PKG_NAME;
1841 l_error_rec.idx := i;
1842 insert_row (
1843 p_api_version => p_api_version,
1844 p_init_msg_list => OKL_API.G_FALSE,
1845 x_return_status => l_error_rec.error_type,
1846 x_msg_count => l_error_rec.msg_count,
1847 x_msg_data => l_error_rec.msg_data,
1848 p_pocv_rec => p_pocv_tbl(i),
1849 x_pocv_rec => x_pocv_tbl(i));
1850 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1851 l_error_rec.SQLCODE := SQLCODE;
1852 load_error_tbl(l_error_rec, px_error_tbl);
1853 ELSE
1854 x_msg_count := l_error_rec.msg_count;
1855 x_msg_data := l_error_rec.msg_data;
1856 END IF;
1857 EXCEPTION
1858 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1859 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1860 l_error_rec.SQLCODE := SQLCODE;
1861 load_error_tbl(l_error_rec, px_error_tbl);
1862 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1863 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1864 l_error_rec.SQLCODE := SQLCODE;
1865 load_error_tbl(l_error_rec, px_error_tbl);
1866 WHEN OTHERS THEN
1867 l_error_rec.error_type := 'OTHERS';
1868 l_error_rec.SQLCODE := SQLCODE;
1869 load_error_tbl(l_error_rec, px_error_tbl);
1870 END;
1871 EXIT WHEN (i = p_pocv_tbl.LAST);
1872 i := p_pocv_tbl.NEXT(i);
1873 END LOOP;
1874 END IF;
1875 -- Loop through the error_tbl to find the error with the highest severity
1876 -- and return it.
1877 x_return_status := find_highest_exception(px_error_tbl);
1878 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1879 EXCEPTION
1880 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1881 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1882 (
1883 l_api_name,
1884 G_PKG_NAME,
1885 'OKL_API.G_RET_STS_ERROR',
1886 x_msg_count,
1887 x_msg_data,
1888 '_PVT'
1889 );
1890 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1891 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1892 (
1893 l_api_name,
1894 G_PKG_NAME,
1895 'OKL_API.G_RET_STS_UNEXP_ERROR',
1896 x_msg_count,
1897 x_msg_data,
1898 '_PVT'
1899 );
1900 WHEN OTHERS THEN
1901 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1902 (
1903 l_api_name,
1904 G_PKG_NAME,
1905 'OTHERS',
1906 x_msg_count,
1907 x_msg_data,
1908 '_PVT'
1909 );
1910 END insert_row;
1911
1912 ----------------------------------------
1913 -- PL/SQL TBL insert_row for:pocv_TBL --
1914 ----------------------------------------
1915 PROCEDURE insert_row(
1916 p_api_version IN NUMBER,
1917 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1918 x_return_status OUT NOCOPY VARCHAR2,
1919 x_msg_count OUT NOCOPY NUMBER,
1920 x_msg_data OUT NOCOPY VARCHAR2,
1921 p_pocv_tbl IN pocv_tbl_type,
1922 x_pocv_tbl OUT NOCOPY pocv_tbl_type) IS
1923
1924 l_api_version CONSTANT NUMBER := 1;
1925 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1926 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1927 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1928 BEGIN
1929 OKL_API.init_msg_list(p_init_msg_list);
1930 -- Make sure PL/SQL table has records in it before passing
1931 IF (p_pocv_tbl.COUNT > 0) THEN
1932 insert_row (
1933 p_api_version => p_api_version,
1934 p_init_msg_list => OKL_API.G_FALSE,
1935 x_return_status => x_return_status,
1936 x_msg_count => x_msg_count,
1937 x_msg_data => x_msg_data,
1938 p_pocv_tbl => p_pocv_tbl,
1939 x_pocv_tbl => x_pocv_tbl,
1940 px_error_tbl => l_error_tbl);
1941 END IF;
1942 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1943 EXCEPTION
1944 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1945 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1946 (
1947 l_api_name,
1948 G_PKG_NAME,
1949 'OKL_API.G_RET_STS_ERROR',
1950 x_msg_count,
1951 x_msg_data,
1952 '_PVT'
1953 );
1954 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1955 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1956 (
1957 l_api_name,
1958 G_PKG_NAME,
1959 'OKL_API.G_RET_STS_UNEXP_ERROR',
1960 x_msg_count,
1961 x_msg_data,
1962 '_PVT'
1963 );
1964 WHEN OTHERS THEN
1965 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1966 (
1967 l_api_name,
1968 G_PKG_NAME,
1969 'OTHERS',
1970 x_msg_count,
1971 x_msg_data,
1972 '_PVT'
1973 );
1974 END insert_row;
1975
1976 ---------------------------------------------------------------------------
1977 -- PROCEDURE lock_row
1978 ---------------------------------------------------------------------------
1979 --------------------------------------
1980 -- lock_row for:okl_pool_contents --
1981 --------------------------------------
1982 PROCEDURE lock_row(
1983 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1984 x_return_status OUT NOCOPY VARCHAR2,
1985 x_msg_count OUT NOCOPY NUMBER,
1986 x_msg_data OUT NOCOPY VARCHAR2,
1987 p_poc_rec IN poc_rec_type) IS
1988
1989 E_Resource_Busy EXCEPTION;
1990 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1991 CURSOR lock_csr (p_poc_rec IN poc_rec_type) IS
1992 SELECT OBJECT_VERSION_NUMBER
1993 FROM okl_pool_contents
1994 WHERE ID = p_poc_rec.id
1995 AND OBJECT_VERSION_NUMBER = p_poc_rec.object_version_number
1996 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1997
1998 CURSOR lchk_csr (p_poc_rec IN poc_rec_type) IS
1999 SELECT OBJECT_VERSION_NUMBER
2000 FROM okl_pool_contents
2001 WHERE ID = p_poc_rec.id;
2002 l_api_version CONSTANT NUMBER := 1;
2003 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2004 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2005 l_object_version_number okl_pool_contents.OBJECT_VERSION_NUMBER%TYPE;
2006 lc_object_version_number okl_pool_contents.OBJECT_VERSION_NUMBER%TYPE;
2007 l_row_notfound BOOLEAN := FALSE;
2008 lc_row_notfound BOOLEAN := FALSE;
2009 BEGIN
2010 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2011 p_init_msg_list,
2012 '_PVT',
2013 x_return_status);
2014 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2015 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2016 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2017 RAISE OKL_API.G_EXCEPTION_ERROR;
2018 END IF;
2019 BEGIN
2020 OPEN lock_csr(p_poc_rec);
2021 FETCH lock_csr INTO l_object_version_number;
2022 l_row_notfound := lock_csr%NOTFOUND;
2023 CLOSE lock_csr;
2024 EXCEPTION
2025 WHEN E_Resource_Busy THEN
2026 IF (lock_csr%ISOPEN) THEN
2027 CLOSE lock_csr;
2028 END IF;
2029 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2030 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2031 END;
2032
2033 IF ( l_row_notfound ) THEN
2034 OPEN lchk_csr(p_poc_rec);
2035 FETCH lchk_csr INTO lc_object_version_number;
2036 lc_row_notfound := lchk_csr%NOTFOUND;
2037 CLOSE lchk_csr;
2038 END IF;
2039 IF (lc_row_notfound) THEN
2040 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2041 RAISE OKL_API.G_EXCEPTION_ERROR;
2042 ELSIF lc_object_version_number > p_poc_rec.object_version_number THEN
2043 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2044 RAISE OKL_API.G_EXCEPTION_ERROR;
2045 ELSIF lc_object_version_number <> p_poc_rec.object_version_number THEN
2046 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2047 RAISE OKL_API.G_EXCEPTION_ERROR;
2048 ELSIF lc_object_version_number = -1 THEN
2049 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2050 RAISE OKL_API.G_EXCEPTION_ERROR;
2051 END IF;
2052 x_return_status := l_return_status;
2053 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2054 EXCEPTION
2055 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2056 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2057 (
2058 l_api_name,
2059 G_PKG_NAME,
2060 'OKL_API.G_RET_STS_ERROR',
2061 x_msg_count,
2062 x_msg_data,
2063 '_PVT'
2064 );
2065 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2066 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2067 (
2068 l_api_name,
2069 G_PKG_NAME,
2070 'OKL_API.G_RET_STS_UNEXP_ERROR',
2071 x_msg_count,
2072 x_msg_data,
2073 '_PVT'
2074 );
2075 WHEN OTHERS THEN
2076 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2077 (
2078 l_api_name,
2079 G_PKG_NAME,
2080 'OTHERS',
2081 x_msg_count,
2082 x_msg_data,
2083 '_PVT'
2084 );
2085 END lock_row;
2086 -----------------------------------------
2087 -- lock_row for: OKL_POOL_CONTENTS_V --
2088 -----------------------------------------
2089 PROCEDURE lock_row(
2090 p_api_version IN NUMBER,
2091 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2092 x_return_status OUT NOCOPY VARCHAR2,
2093 x_msg_count OUT NOCOPY NUMBER,
2094 x_msg_data OUT NOCOPY VARCHAR2,
2095 p_pocv_rec IN pocv_rec_type) IS
2096
2097 l_api_version CONSTANT NUMBER := 1;
2098 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2099 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2100 l_poc_rec poc_rec_type;
2101 BEGIN
2102 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2103 G_PKG_NAME,
2104 p_init_msg_list,
2105 l_api_version,
2106 p_api_version,
2107 '_PVT',
2108 x_return_status);
2109 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2110 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2111 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2112 RAISE OKL_API.G_EXCEPTION_ERROR;
2113 END IF;
2114 -----------------------------------------
2115 -- Move VIEW record to "Child" records --
2116 -----------------------------------------
2117 migrate(p_pocv_rec, l_poc_rec);
2118 ---------------------------------------------
2119 -- Call the LOCK_ROW for each child record --
2120 ---------------------------------------------
2121 lock_row(
2122 p_init_msg_list,
2123 l_return_status,
2124 x_msg_count,
2125 x_msg_data,
2126 l_poc_rec
2127 );
2128 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2129 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2130 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2131 RAISE OKL_API.G_EXCEPTION_ERROR;
2132 END IF;
2133 x_return_status := l_return_status;
2134 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2135 EXCEPTION
2136 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2137 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2138 (
2139 l_api_name,
2140 G_PKG_NAME,
2141 'OKL_API.G_RET_STS_ERROR',
2142 x_msg_count,
2143 x_msg_data,
2144 '_PVT'
2145 );
2146 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2147 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2148 (
2149 l_api_name,
2150 G_PKG_NAME,
2151 'OKL_API.G_RET_STS_UNEXP_ERROR',
2152 x_msg_count,
2153 x_msg_data,
2154 '_PVT'
2155 );
2156 WHEN OTHERS THEN
2157 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2158 (
2159 l_api_name,
2160 G_PKG_NAME,
2161 'OTHERS',
2162 x_msg_count,
2163 x_msg_data,
2164 '_PVT'
2165 );
2166 END lock_row;
2167 --------------------------------------
2168 -- PL/SQL TBL lock_row for:pocv_TBL --
2169 --------------------------------------
2170 PROCEDURE lock_row(
2171 p_api_version IN NUMBER,
2172 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2173 x_return_status OUT NOCOPY VARCHAR2,
2174 x_msg_count OUT NOCOPY NUMBER,
2175 x_msg_data OUT NOCOPY VARCHAR2,
2176 p_pocv_tbl IN pocv_tbl_type,
2177 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2178
2179 l_api_version CONSTANT NUMBER := 1;
2180 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
2181 i NUMBER := 0;
2182 BEGIN
2183 OKL_API.init_msg_list(p_init_msg_list);
2184 -- Make sure PL/SQL table has recrods in it before passing
2185 IF (p_pocv_tbl.COUNT > 0) THEN
2186 i := p_pocv_tbl.FIRST;
2187 LOOP
2188 DECLARE
2189 l_error_rec OKL_API.ERROR_REC_TYPE;
2190 BEGIN
2191 l_error_rec.api_name := l_api_name;
2192 l_error_rec.api_package := G_PKG_NAME;
2193 l_error_rec.idx := i;
2194 lock_row(
2195 p_api_version => p_api_version,
2196 p_init_msg_list => OKL_API.G_FALSE,
2197 x_return_status => l_error_rec.error_type,
2198 x_msg_count => l_error_rec.msg_count,
2199 x_msg_data => l_error_rec.msg_data,
2200 p_pocv_rec => p_pocv_tbl(i));
2201 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2202 l_error_rec.SQLCODE := SQLCODE;
2203 load_error_tbl(l_error_rec, px_error_tbl);
2204 ELSE
2205 x_msg_count := l_error_rec.msg_count;
2206 x_msg_data := l_error_rec.msg_data;
2207 END IF;
2208 EXCEPTION
2209 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2210 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2211 l_error_rec.SQLCODE := SQLCODE;
2212 load_error_tbl(l_error_rec, px_error_tbl);
2213 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2214 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2215 l_error_rec.SQLCODE := SQLCODE;
2216 load_error_tbl(l_error_rec, px_error_tbl);
2217 WHEN OTHERS THEN
2218 l_error_rec.error_type := 'OTHERS';
2219 l_error_rec.SQLCODE := SQLCODE;
2220 load_error_tbl(l_error_rec, px_error_tbl);
2221 END;
2222 EXIT WHEN (i = p_pocv_tbl.LAST);
2223 i := p_pocv_tbl.NEXT(i);
2224 END LOOP;
2225 END IF;
2226 -- Loop through the error_tbl to find the error with the highest severity
2227 -- and return it.
2228 x_return_status := find_highest_exception(px_error_tbl);
2229 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2230 EXCEPTION
2231 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2232 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2233 (
2234 l_api_name,
2235 G_PKG_NAME,
2236 'OKL_API.G_RET_STS_ERROR',
2237 x_msg_count,
2238 x_msg_data,
2239 '_PVT'
2240 );
2241 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2242 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2243 (
2244 l_api_name,
2245 G_PKG_NAME,
2246 'OKL_API.G_RET_STS_UNEXP_ERROR',
2247 x_msg_count,
2248 x_msg_data,
2249 '_PVT'
2250 );
2251 WHEN OTHERS THEN
2252 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2253 (
2254 l_api_name,
2255 G_PKG_NAME,
2256 'OTHERS',
2257 x_msg_count,
2258 x_msg_data,
2259 '_PVT'
2260 );
2261 END lock_row;
2262 --------------------------------------
2263 -- PL/SQL TBL lock_row for:pocv_TBL --
2264 --------------------------------------
2265 PROCEDURE lock_row(
2266 p_api_version IN NUMBER,
2267 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2268 x_return_status OUT NOCOPY VARCHAR2,
2269 x_msg_count OUT NOCOPY NUMBER,
2270 x_msg_data OUT NOCOPY VARCHAR2,
2271 p_pocv_tbl IN pocv_tbl_type) IS
2272
2273 l_api_version CONSTANT NUMBER := 1;
2274 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2275 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2276 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2277 BEGIN
2278 OKL_API.init_msg_list(p_init_msg_list);
2279 -- Make sure PL/SQL table has recrods in it before passing
2280 IF (p_pocv_tbl.COUNT > 0) THEN
2281 lock_row(
2282 p_api_version => p_api_version,
2283 p_init_msg_list => OKL_API.G_FALSE,
2284 x_return_status => x_return_status,
2285 x_msg_count => x_msg_count,
2286 x_msg_data => x_msg_data,
2287 p_pocv_tbl => p_pocv_tbl,
2288 px_error_tbl => l_error_tbl);
2289 END IF;
2290 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2291 EXCEPTION
2292 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2293 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2294 (
2295 l_api_name,
2296 G_PKG_NAME,
2297 'OKL_API.G_RET_STS_ERROR',
2298 x_msg_count,
2299 x_msg_data,
2300 '_PVT'
2301 );
2302 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2303 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2304 (
2305 l_api_name,
2306 G_PKG_NAME,
2307 'OKL_API.G_RET_STS_UNEXP_ERROR',
2308 x_msg_count,
2309 x_msg_data,
2310 '_PVT'
2311 );
2312 WHEN OTHERS THEN
2313 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2314 (
2315 l_api_name,
2316 G_PKG_NAME,
2317 'OTHERS',
2318 x_msg_count,
2319 x_msg_data,
2320 '_PVT'
2321 );
2322 END lock_row;
2323 ---------------------------------------------------------------------------
2324 -- PROCEDURE update_row
2325 ---------------------------------------------------------------------------
2326 ----------------------------------------
2327 -- update_row for:okl_pool_contents --
2328 ----------------------------------------
2329 PROCEDURE update_row(
2330 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2331 x_return_status OUT NOCOPY VARCHAR2,
2332 x_msg_count OUT NOCOPY NUMBER,
2333 x_msg_data OUT NOCOPY VARCHAR2,
2334 p_poc_rec IN poc_rec_type,
2335 x_poc_rec OUT NOCOPY poc_rec_type) IS
2336
2337 l_api_version CONSTANT NUMBER := 1;
2338 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2339 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2340 l_poc_rec poc_rec_type := p_poc_rec;
2341 l_def_poc_rec poc_rec_type;
2342 l_row_notfound BOOLEAN := TRUE;
2343 ----------------------------------
2344 -- FUNCTION populate_new_record --
2345 ----------------------------------
2346 FUNCTION populate_new_record (
2347 p_poc_rec IN poc_rec_type,
2348 x_poc_rec OUT NOCOPY poc_rec_type
2349 ) RETURN VARCHAR2 IS
2350 l_poc_rec poc_rec_type;
2351 l_row_notfound BOOLEAN := TRUE;
2352 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2353 BEGIN
2354 x_poc_rec := p_poc_rec;
2355 -- Get current database values
2356 l_poc_rec := get_rec(p_poc_rec, l_return_status);
2357 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
2358
2359 IF (x_poc_rec.id = OKL_API.G_MISS_NUM)
2360 THEN
2361 x_poc_rec.id := l_poc_rec.id;
2362 END IF;
2363 IF (x_poc_rec.object_version_number = OKL_API.G_MISS_NUM)
2364 THEN
2365 x_poc_rec.object_version_number := l_poc_rec.object_version_number;
2366 END IF;
2367 IF (x_poc_rec.pol_id = OKL_API.G_MISS_NUM)
2368 THEN
2369 x_poc_rec.pol_id := l_poc_rec.pol_id;
2370 END IF;
2371 IF (x_poc_rec.khr_id = OKL_API.G_MISS_NUM)
2372 THEN
2373 x_poc_rec.khr_id := l_poc_rec.khr_id;
2374 END IF;
2375 IF (x_poc_rec.kle_id = OKL_API.G_MISS_NUM)
2376 THEN
2377 x_poc_rec.kle_id := l_poc_rec.kle_id;
2378 END IF;
2379 IF (x_poc_rec.sty_id = OKL_API.G_MISS_NUM)
2380 THEN
2381 x_poc_rec.sty_id := l_poc_rec.sty_id;
2382 END IF;
2383 IF (x_poc_rec.stm_id = OKL_API.G_MISS_NUM)
2384 THEN
2385 x_poc_rec.stm_id := l_poc_rec.stm_id;
2386 END IF;
2387 IF (x_poc_rec.sty_code = OKL_API.G_MISS_CHAR)
2388 THEN
2389 x_poc_rec.sty_code := l_poc_rec.sty_code;
2390 END IF;
2391 IF (x_poc_rec.pox_id = OKL_API.G_MISS_NUM)
2392 THEN
2393 x_poc_rec.pox_id := l_poc_rec.pox_id;
2394 END IF;
2395 IF (x_poc_rec.streams_from_date = OKL_API.G_MISS_DATE)
2396 THEN
2397 x_poc_rec.streams_from_date := l_poc_rec.streams_from_date;
2398 END IF;
2399 IF (x_poc_rec.streams_to_date = OKL_API.G_MISS_DATE)
2400 THEN
2401 x_poc_rec.streams_to_date := l_poc_rec.streams_to_date;
2402 END IF;
2403 IF (x_poc_rec.transaction_number_in = OKL_API.G_MISS_NUM)
2404 THEN
2405 x_poc_rec.transaction_number_in := l_poc_rec.transaction_number_in;
2406 END IF;
2407 IF (x_poc_rec.transaction_number_out = OKL_API.G_MISS_NUM)
2408 THEN
2409 x_poc_rec.transaction_number_out := l_poc_rec.transaction_number_out;
2410 END IF;
2411 IF (x_poc_rec.date_inactive = OKL_API.G_MISS_DATE)
2412 THEN
2413 x_poc_rec.date_inactive := l_poc_rec.date_inactive;
2414 END IF;
2415 IF (x_poc_rec.status_code = OKL_API.G_MISS_CHAR)
2416 THEN
2417 x_poc_rec.status_code := l_poc_rec.status_code;
2418 END IF;
2419 IF (x_poc_rec.attribute_category = OKL_API.G_MISS_CHAR)
2420 THEN
2421 x_poc_rec.attribute_category := l_poc_rec.attribute_category;
2422 END IF;
2423 IF (x_poc_rec.attribute1 = OKL_API.G_MISS_CHAR)
2424 THEN
2425 x_poc_rec.attribute1 := l_poc_rec.attribute1;
2426 END IF;
2427 IF (x_poc_rec.attribute2 = OKL_API.G_MISS_CHAR)
2428 THEN
2429 x_poc_rec.attribute2 := l_poc_rec.attribute2;
2430 END IF;
2431 IF (x_poc_rec.attribute3 = OKL_API.G_MISS_CHAR)
2432 THEN
2433 x_poc_rec.attribute3 := l_poc_rec.attribute3;
2434 END IF;
2435 IF (x_poc_rec.attribute4 = OKL_API.G_MISS_CHAR)
2436 THEN
2437 x_poc_rec.attribute4 := l_poc_rec.attribute4;
2438 END IF;
2439 IF (x_poc_rec.attribute5 = OKL_API.G_MISS_CHAR)
2440 THEN
2441 x_poc_rec.attribute5 := l_poc_rec.attribute5;
2442 END IF;
2443 IF (x_poc_rec.attribute6 = OKL_API.G_MISS_CHAR)
2444 THEN
2445 x_poc_rec.attribute6 := l_poc_rec.attribute6;
2446 END IF;
2447 IF (x_poc_rec.attribute7 = OKL_API.G_MISS_CHAR)
2448 THEN
2449 x_poc_rec.attribute7 := l_poc_rec.attribute7;
2450 END IF;
2451 IF (x_poc_rec.attribute8 = OKL_API.G_MISS_CHAR)
2452 THEN
2453 x_poc_rec.attribute8 := l_poc_rec.attribute8;
2454 END IF;
2455 IF (x_poc_rec.attribute9 = OKL_API.G_MISS_CHAR)
2456 THEN
2457 x_poc_rec.attribute9 := l_poc_rec.attribute9;
2458 END IF;
2459 IF (x_poc_rec.attribute10 = OKL_API.G_MISS_CHAR)
2460 THEN
2461 x_poc_rec.attribute10 := l_poc_rec.attribute10;
2462 END IF;
2463 IF (x_poc_rec.attribute11 = OKL_API.G_MISS_CHAR)
2464 THEN
2465 x_poc_rec.attribute11 := l_poc_rec.attribute11;
2466 END IF;
2467 IF (x_poc_rec.attribute12 = OKL_API.G_MISS_CHAR)
2468 THEN
2469 x_poc_rec.attribute12 := l_poc_rec.attribute12;
2470 END IF;
2471 IF (x_poc_rec.attribute13 = OKL_API.G_MISS_CHAR)
2472 THEN
2473 x_poc_rec.attribute13 := l_poc_rec.attribute13;
2474 END IF;
2475 IF (x_poc_rec.attribute14 = OKL_API.G_MISS_CHAR)
2476 THEN
2477 x_poc_rec.attribute14 := l_poc_rec.attribute14;
2478 END IF;
2479 IF (x_poc_rec.attribute15 = OKL_API.G_MISS_CHAR)
2480 THEN
2481 x_poc_rec.attribute15 := l_poc_rec.attribute15;
2482 END IF;
2483 IF (x_poc_rec.request_id = OKL_API.G_MISS_NUM)
2484 THEN
2485 x_poc_rec.request_id := l_poc_rec.request_id;
2486 END IF;
2487 IF (x_poc_rec.program_application_id = OKL_API.G_MISS_NUM)
2488 THEN
2489 x_poc_rec.program_application_id := l_poc_rec.program_application_id;
2490 END IF;
2491 IF (x_poc_rec.program_id = OKL_API.G_MISS_NUM)
2492 THEN
2493 x_poc_rec.program_id := l_poc_rec.program_id;
2494 END IF;
2495 IF (x_poc_rec.program_update_date = OKL_API.G_MISS_DATE)
2496 THEN
2497 x_poc_rec.program_update_date := l_poc_rec.program_update_date;
2498 END IF;
2499 IF (x_poc_rec.created_by = OKL_API.G_MISS_NUM)
2500 THEN
2501 x_poc_rec.created_by := l_poc_rec.created_by;
2502 END IF;
2503 IF (x_poc_rec.creation_date = OKL_API.G_MISS_DATE)
2504 THEN
2505 x_poc_rec.creation_date := l_poc_rec.creation_date;
2506 END IF;
2507 IF (x_poc_rec.last_updated_by = OKL_API.G_MISS_NUM)
2508 THEN
2509 x_poc_rec.last_updated_by := l_poc_rec.last_updated_by;
2510 END IF;
2511 IF (x_poc_rec.last_update_date = OKL_API.G_MISS_DATE)
2512 THEN
2513 x_poc_rec.last_update_date := l_poc_rec.last_update_date;
2514 END IF;
2515 IF (x_poc_rec.last_update_login = OKL_API.G_MISS_NUM)
2516 THEN
2517 x_poc_rec.last_update_login := l_poc_rec.last_update_login;
2518 END IF;
2519 END IF;
2520 RETURN(l_return_status);
2521 END populate_new_record;
2522 --------------------------------------------
2523 -- Set_Attributes for:okl_pool_contents --
2524 --------------------------------------------
2525 FUNCTION Set_Attributes (
2526 p_poc_rec IN poc_rec_type,
2527 x_poc_rec OUT NOCOPY poc_rec_type
2528 ) RETURN VARCHAR2 IS
2529 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2530 BEGIN
2531 x_poc_rec := p_poc_rec;
2532 x_poc_rec.OBJECT_VERSION_NUMBER := p_poc_rec.OBJECT_VERSION_NUMBER + 1;
2533 RETURN(l_return_status);
2534 END Set_Attributes;
2535 BEGIN
2536 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2537 p_init_msg_list,
2538 '_PVT',
2539 x_return_status);
2540 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2541 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2542 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2543 RAISE OKL_API.G_EXCEPTION_ERROR;
2544 END IF;
2545 --- Setting item attributes
2546 l_return_status := Set_Attributes(
2547 p_poc_rec, -- IN
2548 l_poc_rec); -- OUT
2549 --- If any errors happen abort API
2550 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2551 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2552 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2553 RAISE OKL_API.G_EXCEPTION_ERROR;
2554 END IF;
2555 l_return_status := populate_new_record(l_poc_rec, l_def_poc_rec);
2556 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2557 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2558 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2559 RAISE OKL_API.G_EXCEPTION_ERROR;
2560 END IF;
2561 UPDATE okl_pool_contents
2562 SET OBJECT_VERSION_NUMBER = l_def_poc_rec.object_version_number,
2563 POL_ID = l_def_poc_rec.pol_id,
2564 KHR_ID = l_def_poc_rec.khr_id,
2565 KLE_ID = l_def_poc_rec.kle_id,
2566 STY_ID = l_def_poc_rec.sty_id,
2567 STM_ID = l_def_poc_rec.stm_id,
2568 STY_CODE = l_def_poc_rec.sty_code,
2569 POX_ID = l_def_poc_rec.pox_id,
2570 STREAMS_FROM_DATE = l_def_poc_rec.streams_from_date,
2571 STREAMS_TO_DATE = l_def_poc_rec.streams_to_date,
2572 TRANSACTION_NUMBER_IN = l_def_poc_rec.transaction_number_in,
2573 TRANSACTION_NUMBER_OUT = l_def_poc_rec.transaction_number_out,
2574 DATE_INACTIVE = l_def_poc_rec.date_inactive,
2575 STATUS_CODE = l_def_poc_rec.status_code,
2576 ATTRIBUTE_CATEGORY = l_def_poc_rec.attribute_category,
2577 ATTRIBUTE1 = l_def_poc_rec.attribute1,
2578 ATTRIBUTE2 = l_def_poc_rec.attribute2,
2579 ATTRIBUTE3 = l_def_poc_rec.attribute3,
2580 ATTRIBUTE4 = l_def_poc_rec.attribute4,
2581 ATTRIBUTE5 = l_def_poc_rec.attribute5,
2582 ATTRIBUTE6 = l_def_poc_rec.attribute6,
2583 ATTRIBUTE7 = l_def_poc_rec.attribute7,
2584 ATTRIBUTE8 = l_def_poc_rec.attribute8,
2585 ATTRIBUTE9 = l_def_poc_rec.attribute9,
2586 ATTRIBUTE10 = l_def_poc_rec.attribute10,
2587 ATTRIBUTE11 = l_def_poc_rec.attribute11,
2588 ATTRIBUTE12 = l_def_poc_rec.attribute12,
2589 ATTRIBUTE13 = l_def_poc_rec.attribute13,
2590 ATTRIBUTE14 = l_def_poc_rec.attribute14,
2591 ATTRIBUTE15 = l_def_poc_rec.attribute15,
2592 REQUEST_ID = l_def_poc_rec.request_id,
2593 PROGRAM_APPLICATION_ID = l_def_poc_rec.program_application_id,
2594 PROGRAM_ID = l_def_poc_rec.program_id,
2595 PROGRAM_UPDATE_DATE = l_def_poc_rec.program_update_date,
2596 CREATED_BY = l_def_poc_rec.created_by,
2597 CREATION_DATE = l_def_poc_rec.creation_date,
2598 LAST_UPDATED_BY = l_def_poc_rec.last_updated_by,
2599 LAST_UPDATE_DATE = l_def_poc_rec.last_update_date,
2600 LAST_UPDATE_LOGIN = l_def_poc_rec.last_update_login
2601 WHERE ID = l_def_poc_rec.id;
2602
2603 x_poc_rec := l_poc_rec;
2604 x_return_status := l_return_status;
2605 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2606 EXCEPTION
2607 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2608 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2609 (
2610 l_api_name,
2611 G_PKG_NAME,
2612 'OKL_API.G_RET_STS_ERROR',
2613 x_msg_count,
2614 x_msg_data,
2615 '_PVT'
2616 );
2617 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2618 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2619 (
2620 l_api_name,
2621 G_PKG_NAME,
2622 'OKL_API.G_RET_STS_UNEXP_ERROR',
2623 x_msg_count,
2624 x_msg_data,
2625 '_PVT'
2626 );
2627 WHEN OTHERS THEN
2628 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2629 (
2630 l_api_name,
2631 G_PKG_NAME,
2632 'OTHERS',
2633 x_msg_count,
2634 x_msg_data,
2635 '_PVT'
2636 );
2637 END update_row;
2638 ------------------------------------------
2639 -- update_row for:OKL_POOL_CONTENTS_V --
2640 ------------------------------------------
2641 PROCEDURE update_row(
2642 p_api_version IN NUMBER,
2643 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2644 x_return_status OUT NOCOPY VARCHAR2,
2645 x_msg_count OUT NOCOPY NUMBER,
2646 x_msg_data OUT NOCOPY VARCHAR2,
2647 p_pocv_rec IN pocv_rec_type,
2648 x_pocv_rec OUT NOCOPY pocv_rec_type) IS
2649
2650 l_api_version CONSTANT NUMBER := 1;
2651 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2652 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2653 l_pocv_rec pocv_rec_type := p_pocv_rec;
2654 l_def_pocv_rec pocv_rec_type;
2655 l_db_pocv_rec pocv_rec_type;
2656 l_poc_rec poc_rec_type;
2657 lx_poc_rec poc_rec_type;
2658 -------------------------------
2659 -- FUNCTION fill_who_columns --
2660 -------------------------------
2661 FUNCTION fill_who_columns (
2662 p_pocv_rec IN pocv_rec_type
2663 ) RETURN pocv_rec_type IS
2664 l_pocv_rec pocv_rec_type := p_pocv_rec;
2665 BEGIN
2666 l_pocv_rec.LAST_UPDATE_DATE := SYSDATE;
2667 l_pocv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2668 l_pocv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2669 RETURN(l_pocv_rec);
2670 END fill_who_columns;
2671 ----------------------------------
2672 -- FUNCTION populate_new_record --
2673 ----------------------------------
2674 FUNCTION populate_new_record (
2675 p_pocv_rec IN pocv_rec_type,
2676 x_pocv_rec OUT NOCOPY pocv_rec_type
2677 ) RETURN VARCHAR2 IS
2678 l_row_notfound BOOLEAN := TRUE;
2679 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2680 BEGIN
2681 x_pocv_rec := p_pocv_rec;
2682 -- Get current database values
2683 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2684 -- so it may be verified through LOCK_ROW.
2685 l_db_pocv_rec := get_rec(p_pocv_rec, l_return_status);
2686 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
2687 IF (x_pocv_rec.id = OKL_API.G_MISS_NUM)
2688 THEN
2689 x_pocv_rec.id := l_db_pocv_rec.id;
2690 END IF;
2691 IF (x_pocv_rec.object_version_number = OKL_API.G_MISS_NUM)
2692 THEN
2693 x_pocv_rec.object_version_number := l_db_pocv_rec.object_version_number;
2694 END IF;
2695 IF (x_pocv_rec.pol_id = OKL_API.G_MISS_NUM)
2696 THEN
2697 x_pocv_rec.pol_id := l_db_pocv_rec.pol_id;
2698 END IF;
2699 IF (x_pocv_rec.khr_id = OKL_API.G_MISS_NUM)
2700 THEN
2701 x_pocv_rec.khr_id := l_db_pocv_rec.khr_id;
2702 END IF;
2703 IF (x_pocv_rec.kle_id = OKL_API.G_MISS_NUM)
2704 THEN
2705 x_pocv_rec.kle_id := l_db_pocv_rec.kle_id;
2706 END IF;
2707 IF (x_pocv_rec.sty_id = OKL_API.G_MISS_NUM)
2708 THEN
2709 x_pocv_rec.sty_id := l_db_pocv_rec.sty_id;
2710 END IF;
2711 IF (x_pocv_rec.stm_id = OKL_API.G_MISS_NUM)
2712 THEN
2713 x_pocv_rec.stm_id := l_db_pocv_rec.stm_id;
2714 END IF;
2715 IF (x_pocv_rec.sty_code = OKL_API.G_MISS_CHAR)
2716 THEN
2717 x_pocv_rec.sty_code := l_db_pocv_rec.sty_code;
2718 END IF;
2719 IF (x_pocv_rec.pox_id = OKL_API.G_MISS_NUM)
2720 THEN
2721 x_pocv_rec.pox_id := l_db_pocv_rec.pox_id;
2722 END IF;
2723 IF (x_pocv_rec.streams_from_date = OKL_API.G_MISS_DATE)
2724 THEN
2725 x_pocv_rec.streams_from_date := l_db_pocv_rec.streams_from_date;
2726 END IF;
2727 IF (x_pocv_rec.streams_to_date = OKL_API.G_MISS_DATE)
2728 THEN
2729 x_pocv_rec.streams_to_date := l_db_pocv_rec.streams_to_date;
2730 END IF;
2731 IF (x_pocv_rec.transaction_number_in = OKL_API.G_MISS_NUM)
2732 THEN
2733 x_pocv_rec.transaction_number_in := l_db_pocv_rec.transaction_number_in;
2734 END IF;
2735 IF (x_pocv_rec.transaction_number_out = OKL_API.G_MISS_NUM)
2736 THEN
2737 x_pocv_rec.transaction_number_out := l_db_pocv_rec.transaction_number_out;
2738 END IF;
2739 IF (x_pocv_rec.date_inactive = OKL_API.G_MISS_DATE)
2740 THEN
2741 x_pocv_rec.date_inactive := l_db_pocv_rec.date_inactive;
2742 END IF;
2743 IF (x_pocv_rec.status_code = OKL_API.G_MISS_CHAR)
2744 THEN
2745 x_pocv_rec.status_code := l_db_pocv_rec.status_code;
2746 END IF;
2747 IF (x_pocv_rec.attribute_category = OKL_API.G_MISS_CHAR)
2748 THEN
2749 x_pocv_rec.attribute_category := l_db_pocv_rec.attribute_category;
2750 END IF;
2751 IF (x_pocv_rec.attribute1 = OKL_API.G_MISS_CHAR)
2752 THEN
2753 x_pocv_rec.attribute1 := l_db_pocv_rec.attribute1;
2754 END IF;
2755 IF (x_pocv_rec.attribute2 = OKL_API.G_MISS_CHAR)
2756 THEN
2757 x_pocv_rec.attribute2 := l_db_pocv_rec.attribute2;
2758 END IF;
2759 IF (x_pocv_rec.attribute3 = OKL_API.G_MISS_CHAR)
2760 THEN
2761 x_pocv_rec.attribute3 := l_db_pocv_rec.attribute3;
2762 END IF;
2763 IF (x_pocv_rec.attribute4 = OKL_API.G_MISS_CHAR)
2764 THEN
2765 x_pocv_rec.attribute4 := l_db_pocv_rec.attribute4;
2766 END IF;
2767 IF (x_pocv_rec.attribute5 = OKL_API.G_MISS_CHAR)
2768 THEN
2769 x_pocv_rec.attribute5 := l_db_pocv_rec.attribute5;
2770 END IF;
2771 IF (x_pocv_rec.attribute6 = OKL_API.G_MISS_CHAR)
2772 THEN
2773 x_pocv_rec.attribute6 := l_db_pocv_rec.attribute6;
2774 END IF;
2775 IF (x_pocv_rec.attribute7 = OKL_API.G_MISS_CHAR)
2776 THEN
2777 x_pocv_rec.attribute7 := l_db_pocv_rec.attribute7;
2778 END IF;
2779 IF (x_pocv_rec.attribute8 = OKL_API.G_MISS_CHAR)
2780 THEN
2781 x_pocv_rec.attribute8 := l_db_pocv_rec.attribute8;
2782 END IF;
2783 IF (x_pocv_rec.attribute9 = OKL_API.G_MISS_CHAR)
2784 THEN
2785 x_pocv_rec.attribute9 := l_db_pocv_rec.attribute9;
2786 END IF;
2787 IF (x_pocv_rec.attribute10 = OKL_API.G_MISS_CHAR)
2788 THEN
2789 x_pocv_rec.attribute10 := l_db_pocv_rec.attribute10;
2790 END IF;
2791 IF (x_pocv_rec.attribute11 = OKL_API.G_MISS_CHAR)
2792 THEN
2793 x_pocv_rec.attribute11 := l_db_pocv_rec.attribute11;
2794 END IF;
2795 IF (x_pocv_rec.attribute12 = OKL_API.G_MISS_CHAR)
2796 THEN
2797 x_pocv_rec.attribute12 := l_db_pocv_rec.attribute12;
2798 END IF;
2799 IF (x_pocv_rec.attribute13 = OKL_API.G_MISS_CHAR)
2800 THEN
2801 x_pocv_rec.attribute13 := l_db_pocv_rec.attribute13;
2802 END IF;
2803 IF (x_pocv_rec.attribute14 = OKL_API.G_MISS_CHAR)
2804 THEN
2805 x_pocv_rec.attribute14 := l_db_pocv_rec.attribute14;
2806 END IF;
2807 IF (x_pocv_rec.attribute15 = OKL_API.G_MISS_CHAR)
2808 THEN
2809 x_pocv_rec.attribute15 := l_db_pocv_rec.attribute15;
2810 END IF;
2811 IF (x_pocv_rec.request_id = OKL_API.G_MISS_NUM)
2812 THEN
2813 x_pocv_rec.request_id := l_db_pocv_rec.request_id;
2814 END IF;
2815 IF (x_pocv_rec.program_application_id = OKL_API.G_MISS_NUM)
2816 THEN
2817 x_pocv_rec.program_application_id := l_db_pocv_rec.program_application_id;
2818 END IF;
2819 IF (x_pocv_rec.program_id = OKL_API.G_MISS_NUM)
2820 THEN
2821 x_pocv_rec.program_id := l_db_pocv_rec.program_id;
2822 END IF;
2823 IF (x_pocv_rec.program_update_date = OKL_API.G_MISS_DATE)
2824 THEN
2825 x_pocv_rec.program_update_date := l_db_pocv_rec.program_update_date;
2826 END IF;
2827 IF (x_pocv_rec.created_by = OKL_API.G_MISS_NUM)
2828 THEN
2829 x_pocv_rec.created_by := l_db_pocv_rec.created_by;
2830 END IF;
2831 IF (x_pocv_rec.creation_date = OKL_API.G_MISS_DATE)
2832 THEN
2833 x_pocv_rec.creation_date := l_db_pocv_rec.creation_date;
2834 END IF;
2835 IF (x_pocv_rec.last_updated_by = OKL_API.G_MISS_NUM)
2836 THEN
2837 x_pocv_rec.last_updated_by := l_db_pocv_rec.last_updated_by;
2838 END IF;
2839 IF (x_pocv_rec.last_update_date = OKL_API.G_MISS_DATE)
2840 THEN
2841 x_pocv_rec.last_update_date := l_db_pocv_rec.last_update_date;
2842 END IF;
2843 IF (x_pocv_rec.last_update_login = OKL_API.G_MISS_NUM)
2844 THEN
2845 x_pocv_rec.last_update_login := l_db_pocv_rec.last_update_login;
2846 END IF;
2847 END IF;
2848 RETURN(l_return_status);
2849 END populate_new_record;
2850 ----------------------------------------------
2851 -- Set_Attributes for:OKL_POOL_CONTENTS_V --
2852 ----------------------------------------------
2853 FUNCTION Set_Attributes (
2854 p_pocv_rec IN pocv_rec_type,
2855 x_pocv_rec OUT NOCOPY pocv_rec_type
2856 ) RETURN VARCHAR2 IS
2857 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2858 BEGIN
2859 x_pocv_rec := p_pocv_rec;
2860 RETURN(l_return_status);
2861 END Set_Attributes;
2862 BEGIN
2863 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2864 G_PKG_NAME,
2865 p_init_msg_list,
2866 l_api_version,
2867 p_api_version,
2868 '_PVT',
2869 x_return_status);
2870 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2871 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2872 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2873 RAISE OKL_API.G_EXCEPTION_ERROR;
2874 END IF;
2875 --- Setting item attributes
2876 l_return_status := Set_Attributes(
2877 p_pocv_rec, -- IN
2878 x_pocv_rec); -- OUT
2879 --- If any errors happen abort API
2880 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2881 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2882 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2883 RAISE OKL_API.G_EXCEPTION_ERROR;
2884 END IF;
2885 l_return_status := populate_new_record(l_pocv_rec, l_def_pocv_rec);
2886 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2887 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2888 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2889 RAISE OKL_API.G_EXCEPTION_ERROR;
2890 END IF;
2891 l_def_pocv_rec := fill_who_columns(l_def_pocv_rec);
2892 --- Validate all non-missing attributes (Item Level Validation)
2893 l_return_status := Validate_Attributes(l_def_pocv_rec);
2894 --- If any errors happen abort API
2895 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2896 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2897 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2898 RAISE OKL_API.G_EXCEPTION_ERROR;
2899 END IF;
2900 l_return_status := Validate_Record(l_def_pocv_rec, l_db_pocv_rec);
2901 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2902 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2903 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2904 RAISE OKL_API.G_EXCEPTION_ERROR;
2905 END IF;
2906
2907 /*
2908 -- MVASUDEV COMMENTED
2909 -- Lock the Record
2910 lock_row(
2911 p_api_version => p_api_version,
2912 p_init_msg_list => p_init_msg_list,
2913 x_return_status => l_return_status,
2914 x_msg_count => x_msg_count,
2915 x_msg_data => x_msg_data,
2916 p_pocv_rec => p_pocv_rec);
2917 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2918 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2919 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2920 RAISE OKL_API.G_EXCEPTION_ERROR;
2921 END IF;
2922 */
2923
2924 -----------------------------------------
2925 -- Move VIEW record to "Child" records --
2926 -----------------------------------------
2927 migrate(l_def_pocv_rec, l_poc_rec);
2928 -----------------------------------------------
2929 -- Call the UPDATE_ROW for each child record --
2930 -----------------------------------------------
2931 update_row(
2932 p_init_msg_list,
2933 l_return_status,
2934 x_msg_count,
2935 x_msg_data,
2936 l_poc_rec,
2937 lx_poc_rec
2938 );
2939 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2940 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2941 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2942 RAISE OKL_API.G_EXCEPTION_ERROR;
2943 END IF;
2944 migrate(lx_poc_rec, l_def_pocv_rec);
2945 x_pocv_rec := l_def_pocv_rec;
2946 x_return_status := l_return_status;
2947 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2948 EXCEPTION
2949 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2950 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2951 (
2952 l_api_name,
2953 G_PKG_NAME,
2954 'OKL_API.G_RET_STS_ERROR',
2955 x_msg_count,
2956 x_msg_data,
2957 '_PVT'
2958 );
2959 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2960 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2961 (
2962 l_api_name,
2963 G_PKG_NAME,
2964 'OKL_API.G_RET_STS_UNEXP_ERROR',
2965 x_msg_count,
2966 x_msg_data,
2967 '_PVT'
2968 );
2969 WHEN OTHERS THEN
2970 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2971 (
2972 l_api_name,
2973 G_PKG_NAME,
2974 'OTHERS',
2975 x_msg_count,
2976 x_msg_data,
2977 '_PVT'
2978 );
2979 END update_row;
2980 ----------------------------------------
2981 -- PL/SQL TBL update_row for:pocv_tbl --
2982 ----------------------------------------
2983 PROCEDURE update_row(
2984 p_api_version IN NUMBER,
2985 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2986 x_return_status OUT NOCOPY VARCHAR2,
2987 x_msg_count OUT NOCOPY NUMBER,
2988 x_msg_data OUT NOCOPY VARCHAR2,
2989 p_pocv_tbl IN pocv_tbl_type,
2990 x_pocv_tbl OUT NOCOPY pocv_tbl_type,
2991 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2992
2993 l_api_version CONSTANT NUMBER := 1;
2994 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2995 i NUMBER := 0;
2996 BEGIN
2997 OKL_API.init_msg_list(p_init_msg_list);
2998 -- Make sure PL/SQL table has records in it before passing
2999 IF (p_pocv_tbl.COUNT > 0) THEN
3000 i := p_pocv_tbl.FIRST;
3001 LOOP
3002 DECLARE
3003 l_error_rec OKL_API.ERROR_REC_TYPE;
3004 BEGIN
3005 l_error_rec.api_name := l_api_name;
3006 l_error_rec.api_package := G_PKG_NAME;
3007 l_error_rec.idx := i;
3008 update_row (
3009 p_api_version => p_api_version,
3010 p_init_msg_list => OKL_API.G_FALSE,
3011 x_return_status => l_error_rec.error_type,
3012 x_msg_count => l_error_rec.msg_count,
3013 x_msg_data => l_error_rec.msg_data,
3014 p_pocv_rec => p_pocv_tbl(i),
3015 x_pocv_rec => x_pocv_tbl(i));
3016 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
3017 l_error_rec.SQLCODE := SQLCODE;
3018 load_error_tbl(l_error_rec, px_error_tbl);
3019 ELSE
3020 x_msg_count := l_error_rec.msg_count;
3021 x_msg_data := l_error_rec.msg_data;
3022 END IF;
3023 EXCEPTION
3024 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3025 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
3026 l_error_rec.SQLCODE := SQLCODE;
3027 load_error_tbl(l_error_rec, px_error_tbl);
3028 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3029 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
3030 l_error_rec.SQLCODE := SQLCODE;
3031 load_error_tbl(l_error_rec, px_error_tbl);
3032 WHEN OTHERS THEN
3033 l_error_rec.error_type := 'OTHERS';
3034 l_error_rec.SQLCODE := SQLCODE;
3035 load_error_tbl(l_error_rec, px_error_tbl);
3036 END;
3037 EXIT WHEN (i = p_pocv_tbl.LAST);
3038 i := p_pocv_tbl.NEXT(i);
3039 END LOOP;
3040 END IF;
3041 -- Loop through the error_tbl to find the error with the highest severity
3042 -- and return it.
3043 x_return_status := find_highest_exception(px_error_tbl);
3044 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3045 EXCEPTION
3046 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3047 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3048 (
3049 l_api_name,
3050 G_PKG_NAME,
3051 'OKL_API.G_RET_STS_ERROR',
3052 x_msg_count,
3053 x_msg_data,
3054 '_PVT'
3055 );
3056 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3057 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3058 (
3059 l_api_name,
3060 G_PKG_NAME,
3061 'OKL_API.G_RET_STS_UNEXP_ERROR',
3062 x_msg_count,
3063 x_msg_data,
3064 '_PVT'
3065 );
3066 WHEN OTHERS THEN
3067 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3068 (
3069 l_api_name,
3070 G_PKG_NAME,
3071 'OTHERS',
3072 x_msg_count,
3073 x_msg_data,
3074 '_PVT'
3075 );
3076 END update_row;
3077
3078 ----------------------------------------
3079 -- PL/SQL TBL update_row for:pocv_TBL --
3080 ----------------------------------------
3081 PROCEDURE update_row(
3082 p_api_version IN NUMBER,
3083 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3084 x_return_status OUT NOCOPY VARCHAR2,
3085 x_msg_count OUT NOCOPY NUMBER,
3086 x_msg_data OUT NOCOPY VARCHAR2,
3087 p_pocv_tbl IN pocv_tbl_type,
3088 x_pocv_tbl OUT NOCOPY pocv_tbl_type) IS
3089
3090 l_api_version CONSTANT NUMBER := 1;
3091 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3092 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3093 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3094 BEGIN
3095 OKL_API.init_msg_list(p_init_msg_list);
3096 -- Make sure PL/SQL table has records in it before passing
3097 IF (p_pocv_tbl.COUNT > 0) THEN
3098 update_row (
3099 p_api_version => p_api_version,
3100 p_init_msg_list => OKL_API.G_FALSE,
3101 x_return_status => x_return_status,
3102 x_msg_count => x_msg_count,
3103 x_msg_data => x_msg_data,
3104 p_pocv_tbl => p_pocv_tbl,
3105 x_pocv_tbl => x_pocv_tbl,
3106 px_error_tbl => l_error_tbl);
3107 END IF;
3108 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3109 EXCEPTION
3110 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3111 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3112 (
3113 l_api_name,
3114 G_PKG_NAME,
3115 'OKL_API.G_RET_STS_ERROR',
3116 x_msg_count,
3117 x_msg_data,
3118 '_PVT'
3119 );
3120 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3121 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3122 (
3123 l_api_name,
3124 G_PKG_NAME,
3125 'OKL_API.G_RET_STS_UNEXP_ERROR',
3126 x_msg_count,
3127 x_msg_data,
3128 '_PVT'
3129 );
3130 WHEN OTHERS THEN
3131 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3132 (
3133 l_api_name,
3134 G_PKG_NAME,
3135 'OTHERS',
3136 x_msg_count,
3137 x_msg_data,
3138 '_PVT'
3139 );
3140 END update_row;
3141
3142 ---------------------------------------------------------------------------
3143 -- PROCEDURE delete_row
3144 ---------------------------------------------------------------------------
3145 ----------------------------------------
3146 -- delete_row for:okl_pool_contents --
3147 ----------------------------------------
3148 PROCEDURE delete_row(
3149 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3150 x_return_status OUT NOCOPY VARCHAR2,
3151 x_msg_count OUT NOCOPY NUMBER,
3152 x_msg_data OUT NOCOPY VARCHAR2,
3153 p_poc_rec IN poc_rec_type) IS
3154
3155 l_api_version CONSTANT NUMBER := 1;
3156 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3157 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3158 l_poc_rec poc_rec_type := p_poc_rec;
3159 l_row_notfound BOOLEAN := TRUE;
3160 BEGIN
3161 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3162 p_init_msg_list,
3163 '_PVT',
3164 x_return_status);
3165 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3166 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3167 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3168 RAISE OKL_API.G_EXCEPTION_ERROR;
3169 END IF;
3170
3171 DELETE FROM okl_pool_contents
3172 WHERE ID = p_poc_rec.id;
3173
3174 x_return_status := l_return_status;
3175 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3176 EXCEPTION
3177 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3178 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3179 (
3180 l_api_name,
3181 G_PKG_NAME,
3182 'OKL_API.G_RET_STS_ERROR',
3183 x_msg_count,
3184 x_msg_data,
3185 '_PVT'
3186 );
3187 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3188 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3189 (
3190 l_api_name,
3191 G_PKG_NAME,
3192 'OKL_API.G_RET_STS_UNEXP_ERROR',
3193 x_msg_count,
3194 x_msg_data,
3195 '_PVT'
3196 );
3197 WHEN OTHERS THEN
3198 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3199 (
3200 l_api_name,
3201 G_PKG_NAME,
3202 'OTHERS',
3203 x_msg_count,
3204 x_msg_data,
3205 '_PVT'
3206 );
3207 END delete_row;
3208 ------------------------------------------
3209 -- delete_row for:OKL_POOL_CONTENTS_V --
3210 ------------------------------------------
3211 PROCEDURE delete_row(
3212 p_api_version IN NUMBER,
3213 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3214 x_return_status OUT NOCOPY VARCHAR2,
3215 x_msg_count OUT NOCOPY NUMBER,
3216 x_msg_data OUT NOCOPY VARCHAR2,
3217 p_pocv_rec IN pocv_rec_type) IS
3218
3219 l_api_version CONSTANT NUMBER := 1;
3220 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3221 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3222 l_pocv_rec pocv_rec_type := p_pocv_rec;
3223 l_poc_rec poc_rec_type;
3224 BEGIN
3225 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3226 G_PKG_NAME,
3227 p_init_msg_list,
3228 l_api_version,
3229 p_api_version,
3230 '_PVT',
3231 x_return_status);
3232 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3233 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3234 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3235 RAISE OKL_API.G_EXCEPTION_ERROR;
3236 END IF;
3237 -----------------------------------------
3238 -- Move VIEW record to "Child" records --
3239 -----------------------------------------
3240 migrate(l_pocv_rec, l_poc_rec);
3241 -----------------------------------------------
3242 -- Call the DELETE_ROW for each child record --
3243 -----------------------------------------------
3244 delete_row(
3245 p_init_msg_list,
3246 l_return_status,
3247 x_msg_count,
3248 x_msg_data,
3249 l_poc_rec
3250 );
3251 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3252 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3253 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3254 RAISE OKL_API.G_EXCEPTION_ERROR;
3255 END IF;
3256 x_return_status := l_return_status;
3257 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3258 EXCEPTION
3259 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3260 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3261 (
3262 l_api_name,
3263 G_PKG_NAME,
3264 'OKL_API.G_RET_STS_ERROR',
3265 x_msg_count,
3266 x_msg_data,
3267 '_PVT'
3268 );
3269 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3270 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3271 (
3272 l_api_name,
3273 G_PKG_NAME,
3274 'OKL_API.G_RET_STS_UNEXP_ERROR',
3275 x_msg_count,
3276 x_msg_data,
3277 '_PVT'
3278 );
3279 WHEN OTHERS THEN
3280 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3281 (
3282 l_api_name,
3283 G_PKG_NAME,
3284 'OTHERS',
3285 x_msg_count,
3286 x_msg_data,
3287 '_PVT'
3288 );
3289 END delete_row;
3290 -----------------------------------------------------
3291 -- PL/SQL TBL delete_row for:OKL_POOL_CONTENTS_V --
3292 -----------------------------------------------------
3293 PROCEDURE delete_row(
3294 p_api_version IN NUMBER,
3295 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3296 x_return_status OUT NOCOPY VARCHAR2,
3297 x_msg_count OUT NOCOPY NUMBER,
3298 x_msg_data OUT NOCOPY VARCHAR2,
3299 p_pocv_tbl IN pocv_tbl_type,
3300 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3301
3302 l_api_version CONSTANT NUMBER := 1;
3303 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
3304 i NUMBER := 0;
3305 BEGIN
3306 OKL_API.init_msg_list(p_init_msg_list);
3307 -- Make sure PL/SQL table has records in it before passing
3308 IF (p_pocv_tbl.COUNT > 0) THEN
3309 i := p_pocv_tbl.FIRST;
3310 LOOP
3311 DECLARE
3312 l_error_rec OKL_API.ERROR_REC_TYPE;
3313 BEGIN
3314 l_error_rec.api_name := l_api_name;
3315 l_error_rec.api_package := G_PKG_NAME;
3316 l_error_rec.idx := i;
3317 delete_row (
3318 p_api_version => p_api_version,
3319 p_init_msg_list => OKL_API.G_FALSE,
3320 x_return_status => l_error_rec.error_type,
3321 x_msg_count => l_error_rec.msg_count,
3322 x_msg_data => l_error_rec.msg_data,
3323 p_pocv_rec => p_pocv_tbl(i));
3324 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
3325 l_error_rec.SQLCODE := SQLCODE;
3326 load_error_tbl(l_error_rec, px_error_tbl);
3327 ELSE
3328 x_msg_count := l_error_rec.msg_count;
3329 x_msg_data := l_error_rec.msg_data;
3330 END IF;
3331 EXCEPTION
3332 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3333 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
3334 l_error_rec.SQLCODE := SQLCODE;
3335 load_error_tbl(l_error_rec, px_error_tbl);
3336 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3337 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
3338 l_error_rec.SQLCODE := SQLCODE;
3339 load_error_tbl(l_error_rec, px_error_tbl);
3340 WHEN OTHERS THEN
3341 l_error_rec.error_type := 'OTHERS';
3342 l_error_rec.SQLCODE := SQLCODE;
3343 load_error_tbl(l_error_rec, px_error_tbl);
3344 END;
3345 EXIT WHEN (i = p_pocv_tbl.LAST);
3346 i := p_pocv_tbl.NEXT(i);
3347 END LOOP;
3348 END IF;
3349 -- Loop through the error_tbl to find the error with the highest severity
3350 -- and return it.
3351 x_return_status := find_highest_exception(px_error_tbl);
3352 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3353 EXCEPTION
3354 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3355 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3356 (
3357 l_api_name,
3358 G_PKG_NAME,
3359 'OKL_API.G_RET_STS_ERROR',
3360 x_msg_count,
3361 x_msg_data,
3362 '_PVT'
3363 );
3364 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3365 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3366 (
3367 l_api_name,
3368 G_PKG_NAME,
3369 'OKL_API.G_RET_STS_UNEXP_ERROR',
3370 x_msg_count,
3371 x_msg_data,
3372 '_PVT'
3373 );
3374 WHEN OTHERS THEN
3375 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3376 (
3377 l_api_name,
3378 G_PKG_NAME,
3379 'OTHERS',
3380 x_msg_count,
3381 x_msg_data,
3382 '_PVT'
3383 );
3384 END delete_row;
3385
3386 -----------------------------------------------------
3387 -- PL/SQL TBL delete_row for:OKL_POOL_CONTENTS_V --
3388 -----------------------------------------------------
3389 PROCEDURE delete_row(
3390 p_api_version IN NUMBER,
3391 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3392 x_return_status OUT NOCOPY VARCHAR2,
3393 x_msg_count OUT NOCOPY NUMBER,
3394 x_msg_data OUT NOCOPY VARCHAR2,
3395 p_pocv_tbl IN pocv_tbl_type) IS
3396
3397 l_api_version CONSTANT NUMBER := 1;
3398 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3399 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3400 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3401 BEGIN
3402 OKL_API.init_msg_list(p_init_msg_list);
3403 -- Make sure PL/SQL table has records in it before passing
3404 IF (p_pocv_tbl.COUNT > 0) THEN
3405 delete_row (
3406 p_api_version => p_api_version,
3407 p_init_msg_list => OKL_API.G_FALSE,
3408 x_return_status => x_return_status,
3409 x_msg_count => x_msg_count,
3410 x_msg_data => x_msg_data,
3411 p_pocv_tbl => p_pocv_tbl,
3412 px_error_tbl => l_error_tbl);
3413 END IF;
3414 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3415 EXCEPTION
3416 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3417 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3418 (
3419 l_api_name,
3420 G_PKG_NAME,
3421 'OKL_API.G_RET_STS_ERROR',
3422 x_msg_count,
3423 x_msg_data,
3424 '_PVT'
3425 );
3426 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3427 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3428 (
3429 l_api_name,
3430 G_PKG_NAME,
3431 'OKL_API.G_RET_STS_UNEXP_ERROR',
3432 x_msg_count,
3433 x_msg_data,
3434 '_PVT'
3435 );
3436 WHEN OTHERS THEN
3437 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3438 (
3439 l_api_name,
3440 G_PKG_NAME,
3441 'OTHERS',
3442 x_msg_count,
3443 x_msg_data,
3444 '_PVT'
3445 );
3446 END delete_row;
3447
3448 END OKL_POC_PVT;