[Home] [Help]
PACKAGE BODY: APPS.OKL_POT_PVT
Source
1 PACKAGE BODY OKL_POT_PVT AS
2 /* $Header: OKLSPOTB.pls 115.1 2002/12/12 01:43:06 mvasudev noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKC_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 OKC_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKC_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 <> OKC_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKC_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_TYPES_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_potv_rec IN potv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN potv_rec_type IS
100 CURSOR okl_potv_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 OBJECT_VERSION_NUMBER,
104 CODE,
105 DESCRIPTION,
106 SHORT_DESCRIPTION,
107 FROM_DATE,
108 TO_DATE,
109 ATTRIBUTE_CATEGORY,
110 ATTRIBUTE1,
111 ATTRIBUTE2,
112 ATTRIBUTE3,
113 ATTRIBUTE4,
114 ATTRIBUTE5,
115 ATTRIBUTE6,
116 ATTRIBUTE7,
117 ATTRIBUTE8,
118 ATTRIBUTE9,
119 ATTRIBUTE10,
120 ATTRIBUTE11,
121 ATTRIBUTE12,
122 ATTRIBUTE13,
123 ATTRIBUTE14,
124 ATTRIBUTE15,
125 CREATED_BY,
126 CREATION_DATE,
127 LAST_UPDATED_BY,
128 LAST_UPDATE_DATE,
129 LAST_UPDATE_LOGIN
130 FROM OKL_POOL_TYPES_V
131 WHERE OKL_POOL_TYPES_V.id = p_id;
132 l_okl_potv_pk okl_potv_pk_csr%ROWTYPE;
133 l_potv_rec potv_rec_type;
134 BEGIN
135 x_no_data_found := TRUE;
136 -- Get current database values
137 OPEN okl_potv_pk_csr (p_potv_rec.id);
138 FETCH okl_potv_pk_csr INTO
139 l_potv_rec.id,
140 l_potv_rec.object_version_number,
141 l_potv_rec.code,
142 l_potv_rec.description,
143 l_potv_rec.short_description,
144 l_potv_rec.from_date,
145 l_potv_rec.TO_DATE,
146 l_potv_rec.attribute_category,
147 l_potv_rec.attribute1,
148 l_potv_rec.attribute2,
149 l_potv_rec.attribute3,
150 l_potv_rec.attribute4,
151 l_potv_rec.attribute5,
152 l_potv_rec.attribute6,
153 l_potv_rec.attribute7,
154 l_potv_rec.attribute8,
155 l_potv_rec.attribute9,
156 l_potv_rec.attribute10,
157 l_potv_rec.attribute11,
158 l_potv_rec.attribute12,
159 l_potv_rec.attribute13,
160 l_potv_rec.attribute14,
161 l_potv_rec.attribute15,
162 l_potv_rec.created_by,
163 l_potv_rec.creation_date,
164 l_potv_rec.last_updated_by,
165 l_potv_rec.last_update_date,
166 l_potv_rec.last_update_login;
167 x_no_data_found := okl_potv_pk_csr%NOTFOUND;
168 CLOSE okl_potv_pk_csr;
169 RETURN(l_potv_rec);
170 END get_rec;
171
172 ------------------------------------------------------------------
173 -- This version of get_rec sets error messages if no data found --
174 ------------------------------------------------------------------
175 FUNCTION get_rec (
176 p_potv_rec IN potv_rec_type,
177 x_return_status OUT NOCOPY VARCHAR2
178 ) RETURN potv_rec_type IS
179 l_potv_rec potv_rec_type;
180 l_row_notfound BOOLEAN := TRUE;
181 BEGIN
182 x_return_status := OKC_API.G_RET_STS_SUCCESS;
183 l_potv_rec := get_rec(p_potv_rec, l_row_notfound);
184 IF (l_row_notfound) THEN
185 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
186 x_return_status := OKC_API.G_RET_STS_ERROR;
187 END IF;
188 RETURN(l_potv_rec);
189 END get_rec;
190 -----------------------------------------------------------
191 -- So we don't have to pass an "l_row_notfound" variable --
192 -----------------------------------------------------------
193 FUNCTION get_rec (
194 p_potv_rec IN potv_rec_type
195 ) RETURN potv_rec_type IS
196 l_row_not_found BOOLEAN := TRUE;
197 BEGIN
198 RETURN(get_rec(p_potv_rec, l_row_not_found));
199 END get_rec;
200 ---------------------------------------------------------------------------
201 -- FUNCTION get_rec for: okl_pool_types
202 ---------------------------------------------------------------------------
203 FUNCTION get_rec (
204 p_pot_rec IN pot_rec_type,
205 x_no_data_found OUT NOCOPY BOOLEAN
206 ) RETURN pot_rec_type IS
207 CURSOR okl_pool_types_pk_csr (p_id IN NUMBER) IS
208 SELECT
209 ID,
210 OBJECT_VERSION_NUMBER,
211 CODE,
212 DESCRIPTION,
213 SHORT_DESCRIPTION,
214 FROM_DATE,
215 TO_DATE,
216 ATTRIBUTE_CATEGORY,
217 ATTRIBUTE1,
218 ATTRIBUTE2,
219 ATTRIBUTE3,
220 ATTRIBUTE4,
221 ATTRIBUTE5,
222 ATTRIBUTE6,
223 ATTRIBUTE7,
224 ATTRIBUTE8,
225 ATTRIBUTE9,
226 ATTRIBUTE10,
227 ATTRIBUTE11,
228 ATTRIBUTE12,
229 ATTRIBUTE13,
230 ATTRIBUTE14,
231 ATTRIBUTE15,
232 CREATED_BY,
233 CREATION_DATE,
234 LAST_UPDATED_BY,
235 LAST_UPDATE_DATE,
236 LAST_UPDATE_LOGIN
237 FROM okl_pool_types
238 WHERE okl_pool_types.id = p_id;
239 l_okl_pool_types_pk okl_pool_types_pk_csr%ROWTYPE;
240 l_pot_rec pot_rec_type;
241 BEGIN
242 x_no_data_found := TRUE;
243 -- Get current database values
244 OPEN okl_pool_types_pk_csr (p_pot_rec.id);
245 FETCH okl_pool_types_pk_csr INTO
246 l_pot_rec.id,
247 l_pot_rec.object_version_number,
248 l_pot_rec.code,
249 l_pot_rec.description,
250 l_pot_rec.short_description,
251 l_pot_rec.from_date,
252 l_pot_rec.TO_DATE,
253 l_pot_rec.attribute_category,
254 l_pot_rec.attribute1,
255 l_pot_rec.attribute2,
256 l_pot_rec.attribute3,
257 l_pot_rec.attribute4,
258 l_pot_rec.attribute5,
259 l_pot_rec.attribute6,
260 l_pot_rec.attribute7,
261 l_pot_rec.attribute8,
262 l_pot_rec.attribute9,
263 l_pot_rec.attribute10,
264 l_pot_rec.attribute11,
265 l_pot_rec.attribute12,
266 l_pot_rec.attribute13,
267 l_pot_rec.attribute14,
268 l_pot_rec.attribute15,
269 l_pot_rec.created_by,
270 l_pot_rec.creation_date,
271 l_pot_rec.last_updated_by,
272 l_pot_rec.last_update_date,
273 l_pot_rec.last_update_login;
274 x_no_data_found := okl_pool_types_pk_csr%NOTFOUND;
275 CLOSE okl_pool_types_pk_csr;
276 RETURN(l_pot_rec);
277 END get_rec;
278
279 ------------------------------------------------------------------
280 -- This version of get_rec sets error messages if no data found --
281 ------------------------------------------------------------------
282 FUNCTION get_rec (
283 p_pot_rec IN pot_rec_type,
284 x_return_status OUT NOCOPY VARCHAR2
285 ) RETURN pot_rec_type IS
286 l_pot_rec pot_rec_type;
287 l_row_notfound BOOLEAN := TRUE;
288 BEGIN
289 x_return_status := OKC_API.G_RET_STS_SUCCESS;
290 l_pot_rec := get_rec(p_pot_rec, l_row_notfound);
291 IF (l_row_notfound) THEN
292 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
293 x_return_status := OKC_API.G_RET_STS_ERROR;
294 END IF;
295 RETURN(l_pot_rec);
296 END get_rec;
297 -----------------------------------------------------------
298 -- So we don't have to pass an "l_row_notfound" variable --
299 -----------------------------------------------------------
300 FUNCTION get_rec (
301 p_pot_rec IN pot_rec_type
302 ) RETURN pot_rec_type IS
303 l_row_not_found BOOLEAN := TRUE;
304 BEGIN
305 RETURN(get_rec(p_pot_rec, l_row_not_found));
306 END get_rec;
307
308 ---------------------------------------------------------------------------
309 -- FUNCTION null_out_defaults for: OKL_POOL_TYPES_V
310 -- mvasudev, hold this off for now
311 ---------------------------------------------------------------------------
312 FUNCTION null_out_defaults (
313 p_potv_rec IN potv_rec_type
314 ) RETURN potv_rec_type IS
315 l_potv_rec potv_rec_type := p_potv_rec;
316 BEGIN
317 IF (l_potv_rec.id = OKC_API.G_MISS_NUM ) THEN
318 l_potv_rec.id := NULL;
319 END IF;
320 IF (l_potv_rec.object_version_number = OKC_API.G_MISS_NUM ) THEN
321 l_potv_rec.object_version_number := NULL;
322 END IF;
323 IF (l_potv_rec.code = OKC_API.G_MISS_CHAR ) THEN
324 l_potv_rec.code := NULL;
325 END IF;
326 IF (l_potv_rec.description = OKC_API.G_MISS_CHAR ) THEN
327 l_potv_rec.description := NULL;
328 END IF;
329 IF (l_potv_rec.short_description = OKC_API.G_MISS_CHAR ) THEN
330 l_potv_rec.short_description := NULL;
331 END IF;
332 IF (l_potv_rec.from_date = OKC_API.G_MISS_DATE ) THEN
333 l_potv_rec.from_date := NULL;
334 END IF;
335 IF (l_potv_rec.TO_DATE = OKC_API.G_MISS_DATE ) THEN
336 l_potv_rec.TO_DATE := NULL;
337 END IF;
338 IF (l_potv_rec.attribute_category = OKC_API.G_MISS_CHAR ) THEN
339 l_potv_rec.attribute_category := NULL;
340 END IF;
341 IF (l_potv_rec.attribute1 = OKC_API.G_MISS_CHAR ) THEN
342 l_potv_rec.attribute1 := NULL;
343 END IF;
344 IF (l_potv_rec.attribute2 = OKC_API.G_MISS_CHAR ) THEN
345 l_potv_rec.attribute2 := NULL;
346 END IF;
347 IF (l_potv_rec.attribute3 = OKC_API.G_MISS_CHAR ) THEN
348 l_potv_rec.attribute3 := NULL;
349 END IF;
350 IF (l_potv_rec.attribute4 = OKC_API.G_MISS_CHAR ) THEN
351 l_potv_rec.attribute4 := NULL;
352 END IF;
353 IF (l_potv_rec.attribute5 = OKC_API.G_MISS_CHAR ) THEN
354 l_potv_rec.attribute5 := NULL;
355 END IF;
356 IF (l_potv_rec.attribute6 = OKC_API.G_MISS_CHAR ) THEN
357 l_potv_rec.attribute6 := NULL;
358 END IF;
359 IF (l_potv_rec.attribute7 = OKC_API.G_MISS_CHAR ) THEN
360 l_potv_rec.attribute7 := NULL;
361 END IF;
362 IF (l_potv_rec.attribute8 = OKC_API.G_MISS_CHAR ) THEN
363 l_potv_rec.attribute8 := NULL;
364 END IF;
365 IF (l_potv_rec.attribute9 = OKC_API.G_MISS_CHAR ) THEN
366 l_potv_rec.attribute9 := NULL;
367 END IF;
368 IF (l_potv_rec.attribute10 = OKC_API.G_MISS_CHAR ) THEN
369 l_potv_rec.attribute10 := NULL;
370 END IF;
371 IF (l_potv_rec.attribute11 = OKC_API.G_MISS_CHAR ) THEN
372 l_potv_rec.attribute11 := NULL;
373 END IF;
374 IF (l_potv_rec.attribute12 = OKC_API.G_MISS_CHAR ) THEN
375 l_potv_rec.attribute12 := NULL;
376 END IF;
377 IF (l_potv_rec.attribute13 = OKC_API.G_MISS_CHAR ) THEN
378 l_potv_rec.attribute13 := NULL;
379 END IF;
380 IF (l_potv_rec.attribute14 = OKC_API.G_MISS_CHAR ) THEN
381 l_potv_rec.attribute14 := NULL;
382 END IF;
383 IF (l_potv_rec.attribute15 = OKC_API.G_MISS_CHAR ) THEN
384 l_potv_rec.attribute15 := NULL;
385 END IF;
386 IF (l_potv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
387 l_potv_rec.created_by := NULL;
388 END IF;
389 IF (l_potv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
390 l_potv_rec.creation_date := NULL;
391 END IF;
392 IF (l_potv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
393 l_potv_rec.last_updated_by := NULL;
394 END IF;
395 IF (l_potv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
396 l_potv_rec.last_update_date := NULL;
397 END IF;
398 IF (l_potv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
399 l_potv_rec.last_update_login := NULL;
400 END IF;
401
402 RETURN(l_potv_rec);
403 END null_out_defaults;
404
405 ---------------------------------
406 -- Validate_Attributes for: ID --
407 ---------------------------------
408 PROCEDURE validate_id(
409 x_return_status OUT NOCOPY VARCHAR2,
410 p_id IN NUMBER) IS
411 BEGIN
412 x_return_status := OKC_API.G_RET_STS_SUCCESS;
413 IF (p_id = OKC_API.G_MISS_NUM OR
414 p_id IS NULL)
415 THEN
416 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
417 x_return_status := OKC_API.G_RET_STS_ERROR;
418 RAISE G_EXCEPTION_HALT_VALIDATION;
419 END IF;
420 EXCEPTION
421 WHEN G_EXCEPTION_HALT_VALIDATION THEN
422 NULL;
423 WHEN OTHERS THEN
424 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
425 ,p_msg_name => G_UNEXPECTED_ERROR
426 ,p_token1 => G_SQLCODE_TOKEN
427 ,p_token1_value => SQLCODE
428 ,p_token2 => G_SQLERRM_TOKEN
429 ,p_token2_value => SQLERRM);
430 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
431 END validate_id;
432
433 ----------------------------------------------------
434 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
435 ----------------------------------------------------
436 PROCEDURE validate_object_version_number(
437 x_return_status OUT NOCOPY VARCHAR2,
438 p_object_version_number IN NUMBER) IS
439 BEGIN
440 x_return_status := OKC_API.G_RET_STS_SUCCESS;
441 IF (p_object_version_number = OKC_API.G_MISS_NUM OR
442 p_object_version_number IS NULL)
443 THEN
444 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
445 x_return_status := OKC_API.G_RET_STS_ERROR;
446 RAISE G_EXCEPTION_HALT_VALIDATION;
447 END IF;
448 EXCEPTION
449 WHEN G_EXCEPTION_HALT_VALIDATION THEN
450 NULL;
451 WHEN OTHERS THEN
452 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
453 ,p_msg_name => G_UNEXPECTED_ERROR
454 ,p_token1 => G_SQLCODE_TOKEN
455 ,p_token1_value => SQLCODE
456 ,p_token2 => G_SQLERRM_TOKEN
457 ,p_token2_value => SQLERRM);
458 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
459 END validate_object_version_number;
460
461 -------------------------------------
462 -- Validate_Attributes for: code --
463 -------------------------------------
464 PROCEDURE validate_code(
465 x_return_status OUT NOCOPY VARCHAR2,
466 p_code IN VARCHAR2) IS
467 BEGIN
468 x_return_status := OKC_API.G_RET_STS_SUCCESS;
469 IF (p_code = OKC_API.G_MISS_CHAR OR
470 p_code IS NULL)
471 THEN
472 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'code');
473 x_return_status := OKC_API.G_RET_STS_ERROR;
474 RAISE G_EXCEPTION_HALT_VALIDATION;
475 END IF;
476 EXCEPTION
477 WHEN G_EXCEPTION_HALT_VALIDATION THEN
478 NULL;
479 WHEN OTHERS THEN
480 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
481 ,p_msg_name => G_UNEXPECTED_ERROR
482 ,p_token1 => G_SQLCODE_TOKEN
483 ,p_token1_value => SQLCODE
484 ,p_token2 => G_SQLERRM_TOKEN
485 ,p_token2_value => SQLERRM);
486 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
487 END validate_code;
488
489 ---------------------------------------------------------------------------
490 -- FUNCTION Validate_Attributes
491 ---------------------------------------------------------------------------
492 ---------------------------------------------------
493 -- Validate_Attributes for:OKL_POOL_TYPES_V --
494 ---------------------------------------------------
495 FUNCTION Validate_Attributes (
496 p_potv_rec IN potv_rec_type
497 ) RETURN VARCHAR2 IS
498 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
499 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
500 BEGIN
501 -----------------------------
502 -- Column Level Validation --
503 -----------------------------
504 -- ***
505 -- id
506 -- ***
507 validate_id(x_return_status, p_potv_rec.id);
508 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
509 l_return_status := x_return_status;
510 RAISE G_EXCEPTION_HALT_VALIDATION;
511 END IF;
512
513 -- ***
514 -- object_version_number
515 -- ***
516 validate_object_version_number(x_return_status, p_potv_rec.object_version_number);
517 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
518 l_return_status := x_return_status;
519 RAISE G_EXCEPTION_HALT_VALIDATION;
520 END IF;
521
522 -- ***
523 -- code
524 -- ***
525 validate_code(x_return_status, p_potv_rec.code);
526 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
527 l_return_status := x_return_status;
528 RAISE G_EXCEPTION_HALT_VALIDATION;
529 END IF;
530
531 RETURN(l_return_status);
532 EXCEPTION
533 WHEN G_EXCEPTION_HALT_VALIDATION THEN
534 RETURN(l_return_status);
535 WHEN OTHERS THEN
536 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
537 ,p_msg_name => G_UNEXPECTED_ERROR
538 ,p_token1 => G_SQLCODE_TOKEN
539 ,p_token1_value => SQLCODE
540 ,p_token2 => G_SQLERRM_TOKEN
541 ,p_token2_value => SQLERRM);
542 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
543 RETURN(l_return_status);
544 END Validate_Attributes;
545 ---------------------------------------------------------------------------
546 -- PROCEDURE Validate_Record
547 ---------------------------------------------------------------------------
548 -----------------------------------------
549 -- Validate Record for:OKL_POOL_TYPES_V --
550 -----------------------------------------
551 FUNCTION Validate_Record (
552 p_potv_rec IN potv_rec_type,
553 p_db_potv_rec IN potv_rec_type
554 ) RETURN VARCHAR2 IS
555 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
556 ------------------------------------
557 -- FUNCTION validate_foreign_keys --
558 ------------------------------------
559 FUNCTION validate_foreign_keys (
560 p_potv_rec IN potv_rec_type,
561 p_db_potv_rec IN potv_rec_type
562 ) RETURN VARCHAR2 IS
563 item_not_found_error EXCEPTION;
564 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
565 l_row_notfound BOOLEAN := TRUE;
566 BEGIN
567 RETURN (l_return_status);
568 EXCEPTION
569 WHEN item_not_found_error THEN
570 l_return_status := OKC_API.G_RET_STS_ERROR;
571 RETURN (l_return_status);
572 END validate_foreign_keys;
573 BEGIN
574 l_return_status := validate_foreign_keys(p_potv_rec, p_db_potv_rec);
575 RETURN (l_return_status);
576 END Validate_Record;
577 FUNCTION Validate_Record (
578 p_potv_rec IN potv_rec_type
579 ) RETURN VARCHAR2 IS
580 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
581 l_db_potv_rec potv_rec_type := get_rec(p_potv_rec);
582 BEGIN
583 l_return_status := Validate_Record(p_potv_rec => p_potv_rec,
584 p_db_potv_rec => l_db_potv_rec);
585 RETURN (l_return_status);
586 END Validate_Record;
587
588
589 ---------------------------------------------------------------------------
590 -- PROCEDURE Migrate
591 ---------------------------------------------------------------------------
592 PROCEDURE migrate (
593 p_from IN potv_rec_type,
594 p_to IN OUT NOCOPY pot_rec_type
595 ) IS
596 BEGIN
597 p_to.id := p_from.id;
598 p_to.object_version_number := p_from.object_version_number;
599 p_to.code := p_from.code;
600 p_to.description := p_from.description;
601 p_to.short_description := p_from.short_description;
602 p_to.from_date := p_from.from_date;
603 p_to.TO_DATE := p_from.TO_DATE;
604 p_to.attribute_category := p_from.attribute_category;
605 p_to.attribute1 := p_from.attribute1;
606 p_to.attribute2 := p_from.attribute2;
607 p_to.attribute3 := p_from.attribute3;
608 p_to.attribute4 := p_from.attribute4;
609 p_to.attribute5 := p_from.attribute5;
610 p_to.attribute6 := p_from.attribute6;
611 p_to.attribute7 := p_from.attribute7;
612 p_to.attribute8 := p_from.attribute8;
613 p_to.attribute9 := p_from.attribute9;
614 p_to.attribute10 := p_from.attribute10;
615 p_to.attribute11 := p_from.attribute11;
616 p_to.attribute12 := p_from.attribute12;
617 p_to.attribute13 := p_from.attribute13;
618 p_to.attribute14 := p_from.attribute14;
619 p_to.attribute15 := p_from.attribute15;
620 p_to.created_by := p_from.created_by;
621 p_to.creation_date := p_from.creation_date;
622 p_to.last_updated_by := p_from.last_updated_by;
623 p_to.last_update_date := p_from.last_update_date;
624 p_to.last_update_login := p_from.last_update_login;
625 END migrate;
626 PROCEDURE migrate (
627 p_from IN pot_rec_type,
628 p_to IN OUT NOCOPY potv_rec_type
629 ) IS
630 BEGIN
631 p_to.id := p_from.id;
632 p_to.object_version_number := p_from.object_version_number;
633 p_to.code := p_from.code;
634 p_to.description := p_from.description;
635 p_to.short_description := p_from.short_description;
636 p_to.from_date := p_from.from_date;
637 p_to.TO_DATE := p_from.TO_DATE;
638 p_to.attribute_category := p_from.attribute_category;
639 p_to.attribute1 := p_from.attribute1;
640 p_to.attribute2 := p_from.attribute2;
641 p_to.attribute3 := p_from.attribute3;
642 p_to.attribute4 := p_from.attribute4;
643 p_to.attribute5 := p_from.attribute5;
644 p_to.attribute6 := p_from.attribute6;
645 p_to.attribute7 := p_from.attribute7;
646 p_to.attribute8 := p_from.attribute8;
647 p_to.attribute9 := p_from.attribute9;
648 p_to.attribute10 := p_from.attribute10;
649 p_to.attribute11 := p_from.attribute11;
650 p_to.attribute12 := p_from.attribute12;
651 p_to.attribute13 := p_from.attribute13;
652 p_to.attribute14 := p_from.attribute14;
653 p_to.attribute15 := p_from.attribute15;
654 p_to.created_by := p_from.created_by;
655 p_to.creation_date := p_from.creation_date;
656 p_to.last_updated_by := p_from.last_updated_by;
657 p_to.last_update_date := p_from.last_update_date;
658 p_to.last_update_login := p_from.last_update_login;
659 END migrate;
660 ---------------------------------------------------------------------------
661 -- PROCEDURE validate_row
662 ---------------------------------------------------------------------------
663 --------------------------------------------
664 -- validate_row for:OKL_POOL_TYPES_V --
665 --------------------------------------------
666 PROCEDURE validate_row(
667 p_api_version IN NUMBER,
668 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
669 x_return_status OUT NOCOPY VARCHAR2,
670 x_msg_count OUT NOCOPY NUMBER,
671 x_msg_data OUT NOCOPY VARCHAR2,
672 p_potv_rec IN potv_rec_type) IS
673
674 l_api_version CONSTANT NUMBER := 1;
675 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
676 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
677 l_potv_rec potv_rec_type := p_potv_rec;
678 l_pot_rec pot_rec_type;
679 l_pot_rec pot_rec_type;
680 BEGIN
681 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
682 G_PKG_NAME,
683 p_init_msg_list,
684 l_api_version,
685 p_api_version,
686 '_PVT',
687 x_return_status);
688 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
689 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
690 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
691 RAISE OKC_API.G_EXCEPTION_ERROR;
692 END IF;
693 --- Validate all non-missing attributes (Item Level Validation)
694 l_return_status := Validate_Attributes(l_potv_rec);
695 --- If any errors happen abort API
696 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
697 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
698 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
699 RAISE OKC_API.G_EXCEPTION_ERROR;
700 END IF;
701 l_return_status := Validate_Record(l_potv_rec);
702 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
703 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
704 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
705 RAISE OKC_API.G_EXCEPTION_ERROR;
706 END IF;
707 x_return_status := l_return_status;
708 EXCEPTION
709 WHEN OKC_API.G_EXCEPTION_ERROR THEN
710 x_return_status := OKC_API.HANDLE_EXCEPTIONS
711 (
712 l_api_name,
713 G_PKG_NAME,
714 'OKC_API.G_RET_STS_ERROR',
715 x_msg_count,
716 x_msg_data,
717 '_PVT'
718 );
719 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
720 x_return_status := OKC_API.HANDLE_EXCEPTIONS
721 (
722 l_api_name,
723 G_PKG_NAME,
724 'OKC_API.G_RET_STS_UNEXP_ERROR',
725 x_msg_count,
726 x_msg_data,
727 '_PVT'
728 );
729 WHEN OTHERS THEN
730 x_return_status := OKC_API.HANDLE_EXCEPTIONS
731 (
732 l_api_name,
733 G_PKG_NAME,
734 'OTHERS',
735 x_msg_count,
736 x_msg_data,
737 '_PVT'
738 );
739 END validate_row;
740 -------------------------------------------------------
741 -- PL/SQL TBL validate_row for:OKL_POOL_TYPES_V --
742 -------------------------------------------------------
743 PROCEDURE validate_row(
744 p_api_version IN NUMBER,
745 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
746 x_return_status OUT NOCOPY VARCHAR2,
747 x_msg_count OUT NOCOPY NUMBER,
748 x_msg_data OUT NOCOPY VARCHAR2,
749 p_potv_tbl IN potv_tbl_type,
750 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
751
752 l_api_version CONSTANT NUMBER := 1;
753 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
754 i NUMBER := 0;
755 BEGIN
756 OKC_API.init_msg_list(p_init_msg_list);
757 -- Make sure PL/SQL table has records in it before passing
758 IF (p_potv_tbl.COUNT > 0) THEN
759 i := p_potv_tbl.FIRST;
760 LOOP
761 DECLARE
762 l_error_rec OKC_API.ERROR_REC_TYPE;
763 BEGIN
764 l_error_rec.api_name := l_api_name;
765 l_error_rec.api_package := G_PKG_NAME;
766 l_error_rec.idx := i;
767 validate_row (
768 p_api_version => p_api_version,
769 p_init_msg_list => OKC_API.G_FALSE,
770 x_return_status => l_error_rec.error_type,
771 x_msg_count => l_error_rec.msg_count,
772 x_msg_data => l_error_rec.msg_data,
773 p_potv_rec => p_potv_tbl(i));
774 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
775 l_error_rec.SQLCODE := SQLCODE;
776 load_error_tbl(l_error_rec, px_error_tbl);
777 ELSE
778 x_msg_count := l_error_rec.msg_count;
779 x_msg_data := l_error_rec.msg_data;
780 END IF;
781 EXCEPTION
782 WHEN OKC_API.G_EXCEPTION_ERROR THEN
783 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
784 l_error_rec.SQLCODE := SQLCODE;
785 load_error_tbl(l_error_rec, px_error_tbl);
786 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
787 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
788 l_error_rec.SQLCODE := SQLCODE;
789 load_error_tbl(l_error_rec, px_error_tbl);
790 WHEN OTHERS THEN
791 l_error_rec.error_type := 'OTHERS';
792 l_error_rec.SQLCODE := SQLCODE;
793 load_error_tbl(l_error_rec, px_error_tbl);
794 END;
795 EXIT WHEN (i = p_potv_tbl.LAST);
796 i := p_potv_tbl.NEXT(i);
797 END LOOP;
798 END IF;
799 -- Loop through the error_tbl to find the error with the highest severity
800 -- and return it.
801 x_return_status := find_highest_exception(px_error_tbl);
802 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
803 EXCEPTION
804 WHEN OKC_API.G_EXCEPTION_ERROR THEN
805 x_return_status := OKC_API.HANDLE_EXCEPTIONS
806 (
807 l_api_name,
808 G_PKG_NAME,
809 'OKC_API.G_RET_STS_ERROR',
810 x_msg_count,
811 x_msg_data,
812 '_PVT'
813 );
814 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
815 x_return_status := OKC_API.HANDLE_EXCEPTIONS
816 (
817 l_api_name,
818 G_PKG_NAME,
819 'OKC_API.G_RET_STS_UNEXP_ERROR',
820 x_msg_count,
821 x_msg_data,
822 '_PVT'
823 );
824 WHEN OTHERS THEN
825 x_return_status := OKC_API.HANDLE_EXCEPTIONS
826 (
827 l_api_name,
828 G_PKG_NAME,
829 'OTHERS',
830 x_msg_count,
831 x_msg_data,
832 '_PVT'
833 );
834 END validate_row;
835
836 -------------------------------------------------------
837 -- PL/SQL TBL validate_row for:OKL_POOL_TYPES_V --
838 -------------------------------------------------------
839 PROCEDURE validate_row(
840 p_api_version IN NUMBER,
841 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2,
845 p_potv_tbl IN potv_tbl_type) IS
846
847 l_api_version CONSTANT NUMBER := 1;
848 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
849 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
850 l_error_tbl OKC_API.ERROR_TBL_TYPE;
851 BEGIN
852 OKC_API.init_msg_list(p_init_msg_list);
853 -- Make sure PL/SQL table has records in it before passing
854 IF (p_potv_tbl.COUNT > 0) THEN
855 validate_row (
856 p_api_version => p_api_version,
857 p_init_msg_list => OKC_API.G_FALSE,
858 x_return_status => x_return_status,
859 x_msg_count => x_msg_count,
860 x_msg_data => x_msg_data,
861 p_potv_tbl => p_potv_tbl,
862 px_error_tbl => l_error_tbl);
863 END IF;
864 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
865 EXCEPTION
866 WHEN OKC_API.G_EXCEPTION_ERROR THEN
867 x_return_status := OKC_API.HANDLE_EXCEPTIONS
868 (
869 l_api_name,
870 G_PKG_NAME,
871 'OKC_API.G_RET_STS_ERROR',
872 x_msg_count,
873 x_msg_data,
874 '_PVT'
875 );
876 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
877 x_return_status := OKC_API.HANDLE_EXCEPTIONS
878 (
879 l_api_name,
880 G_PKG_NAME,
881 'OKC_API.G_RET_STS_UNEXP_ERROR',
882 x_msg_count,
883 x_msg_data,
884 '_PVT'
885 );
886 WHEN OTHERS THEN
887 x_return_status := OKC_API.HANDLE_EXCEPTIONS
888 (
889 l_api_name,
890 G_PKG_NAME,
891 'OTHERS',
892 x_msg_count,
893 x_msg_data,
894 '_PVT'
895 );
896 END validate_row;
897
898 ---------------------------------------------------------------------------
899 -- PROCEDURE insert_row
900 ---------------------------------------------------------------------------
901 ----------------------------------------
902 -- insert_row for:okl_pool_types --
903 ----------------------------------------
904 PROCEDURE insert_row(
905 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
906 x_return_status OUT NOCOPY VARCHAR2,
907 x_msg_count OUT NOCOPY NUMBER,
908 x_msg_data OUT NOCOPY VARCHAR2,
909 p_pot_rec IN pot_rec_type,
910 x_pot_rec OUT NOCOPY pot_rec_type) IS
911
912 l_api_version CONSTANT NUMBER := 1;
913 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
914 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
915 l_pot_rec pot_rec_type := p_pot_rec;
916 l_def_pot_rec pot_rec_type;
917 --------------------------------------------
918 -- Set_Attributes for:okl_pool_types --
919 --------------------------------------------
920 FUNCTION Set_Attributes (
921 p_pot_rec IN pot_rec_type,
922 x_pot_rec OUT NOCOPY pot_rec_type
923 ) RETURN VARCHAR2 IS
924 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
925 BEGIN
926 x_pot_rec := p_pot_rec;
927 RETURN(l_return_status);
928 END Set_Attributes;
929 BEGIN
930 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
931 p_init_msg_list,
932 '_PVT',
933 x_return_status);
934 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
935 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
936 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
937 RAISE OKC_API.G_EXCEPTION_ERROR;
938 END IF;
939 --- Setting item atributes
940 l_return_status := Set_Attributes(
941 p_pot_rec, -- IN
942 l_pot_rec); -- OUT
943 --- If any errors happen abort API
944 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
945 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
946 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
947 RAISE OKC_API.G_EXCEPTION_ERROR;
948 END IF;
949 INSERT INTO okl_pool_types(
950 ID,
951 OBJECT_VERSION_NUMBER,
952 CODE,
953 DESCRIPTION,
954 SHORT_DESCRIPTION,
955 FROM_DATE,
956 TO_DATE,
957 ATTRIBUTE_CATEGORY,
958 ATTRIBUTE1,
959 ATTRIBUTE2,
960 ATTRIBUTE3,
961 ATTRIBUTE4,
962 ATTRIBUTE5,
963 ATTRIBUTE6,
964 ATTRIBUTE7,
965 ATTRIBUTE8,
966 ATTRIBUTE9,
967 ATTRIBUTE10,
968 ATTRIBUTE11,
969 ATTRIBUTE12,
970 ATTRIBUTE13,
971 ATTRIBUTE14,
972 ATTRIBUTE15,
973 CREATED_BY,
974 CREATION_DATE,
975 LAST_UPDATED_BY,
976 LAST_UPDATE_DATE,
977 LAST_UPDATE_LOGIN)
978 VALUES (
979 l_pot_rec.id,
980 l_pot_rec.object_version_number,
981 l_pot_rec.code,
982 l_pot_rec.description,
983 l_pot_rec.short_description,
984 l_pot_rec.from_date,
985 l_pot_rec.TO_DATE,
986 l_pot_rec.attribute_category,
987 l_pot_rec.attribute1,
988 l_pot_rec.attribute2,
989 l_pot_rec.attribute3,
990 l_pot_rec.attribute4,
991 l_pot_rec.attribute5,
992 l_pot_rec.attribute6,
993 l_pot_rec.attribute7,
994 l_pot_rec.attribute8,
995 l_pot_rec.attribute9,
996 l_pot_rec.attribute10,
997 l_pot_rec.attribute11,
998 l_pot_rec.attribute12,
999 l_pot_rec.attribute13,
1000 l_pot_rec.attribute14,
1001 l_pot_rec.attribute15,
1002 l_pot_rec.created_by,
1003 l_pot_rec.creation_date,
1004 l_pot_rec.last_updated_by,
1005 l_pot_rec.last_update_date,
1006 l_pot_rec.last_update_login);
1007 -- Set OUT values
1008 x_pot_rec := l_pot_rec;
1009 x_return_status := l_return_status;
1010 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1011 EXCEPTION
1012 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1013 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1014 (
1015 l_api_name,
1016 G_PKG_NAME,
1017 'OKC_API.G_RET_STS_ERROR',
1018 x_msg_count,
1019 x_msg_data,
1020 '_PVT'
1021 );
1022 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1023 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1024 (
1025 l_api_name,
1026 G_PKG_NAME,
1027 'OKC_API.G_RET_STS_UNEXP_ERROR',
1028 x_msg_count,
1029 x_msg_data,
1030 '_PVT'
1031 );
1032 WHEN OTHERS THEN
1033 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1034 (
1035 l_api_name,
1036 G_PKG_NAME,
1037 'OTHERS',
1038 x_msg_count,
1039 x_msg_data,
1040 '_PVT'
1041 );
1042 END insert_row;
1043 -------------------------------------------
1044 -- insert_row for :OKL_POOL_TYPES_V --
1045 -------------------------------------------
1046 PROCEDURE insert_row(
1047 p_api_version IN NUMBER,
1048 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1049 x_return_status OUT NOCOPY VARCHAR2,
1050 x_msg_count OUT NOCOPY NUMBER,
1051 x_msg_data OUT NOCOPY VARCHAR2,
1052 p_potv_rec IN potv_rec_type,
1053 x_potv_rec OUT NOCOPY potv_rec_type) IS
1054
1055 l_api_version CONSTANT NUMBER := 1;
1056 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1057 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1058 l_potv_rec potv_rec_type := p_potv_rec;
1059 l_def_potv_rec potv_rec_type;
1060 l_pot_rec pot_rec_type;
1061 lx_pot_rec pot_rec_type;
1062 -------------------------------
1063 -- FUNCTION fill_who_columns --
1064 -------------------------------
1065 FUNCTION fill_who_columns (
1066 p_potv_rec IN potv_rec_type
1067 ) RETURN potv_rec_type IS
1068 l_potv_rec potv_rec_type := p_potv_rec;
1069 BEGIN
1070 l_potv_rec.CREATION_DATE := SYSDATE;
1071 l_potv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1072 l_potv_rec.LAST_UPDATE_DATE := l_potv_rec.CREATION_DATE;
1073 l_potv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1074 l_potv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1075 RETURN(l_potv_rec);
1076 END fill_who_columns;
1077 ----------------------------------------------
1078 -- Set_Attributes for:OKL_POOL_TYPES_V --
1079 ----------------------------------------------
1080 FUNCTION Set_Attributes (
1081 p_potv_rec IN potv_rec_type,
1082 x_potv_rec OUT NOCOPY potv_rec_type
1083 ) RETURN VARCHAR2 IS
1084 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1085 BEGIN
1086 x_potv_rec := p_potv_rec;
1087 x_potv_rec.OBJECT_VERSION_NUMBER := 1;
1088 RETURN(l_return_status);
1089 END Set_Attributes;
1090 BEGIN
1091 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1092 G_PKG_NAME,
1093 p_init_msg_list,
1094 l_api_version,
1095 p_api_version,
1096 '_PVT',
1097 x_return_status);
1098 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1099 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1100 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1101 RAISE OKC_API.G_EXCEPTION_ERROR;
1102 END IF;
1103 l_potv_rec := null_out_defaults(p_potv_rec);
1104 -- Set primary key value
1105 l_potv_rec.ID := get_seq_id;
1106 -- Setting item attributes
1107 l_return_Status := Set_Attributes(
1108 l_potv_rec, -- IN
1109 l_def_potv_rec); -- OUT
1110 --- If any errors happen abort API
1111 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1112 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1113 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1114 RAISE OKC_API.G_EXCEPTION_ERROR;
1115 END IF;
1116 l_def_potv_rec := fill_who_columns(l_def_potv_rec);
1117 --- Validate all non-missing attributes (Item Level Validation)
1118 l_return_status := Validate_Attributes(l_def_potv_rec);
1119 --- If any errors happen abort API
1120 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1121 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1122 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1123 RAISE OKC_API.G_EXCEPTION_ERROR;
1124 END IF;
1125 l_return_status := Validate_Record(l_def_potv_rec);
1126 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1127 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1128 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1129 RAISE OKC_API.G_EXCEPTION_ERROR;
1130 END IF;
1131 -----------------------------------------
1132 -- Move VIEW record to "Child" records --
1133 -----------------------------------------
1134 migrate(l_def_potv_rec, l_pot_rec);
1135 -----------------------------------------------
1136 -- Call the INSERT_ROW for each child record --
1137 -----------------------------------------------
1138 insert_row(
1139 p_init_msg_list,
1140 l_return_status,
1141 x_msg_count,
1142 x_msg_data,
1143 l_pot_rec,
1144 lx_pot_rec
1145 );
1146 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1147 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1148 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1149 RAISE OKC_API.G_EXCEPTION_ERROR;
1150 END IF;
1151 migrate(lx_pot_rec, l_def_potv_rec);
1152 -- Set OUT values
1153 x_potv_rec := l_def_potv_rec;
1154 x_return_status := l_return_status;
1155 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1156 EXCEPTION
1157 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1158 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1159 (
1160 l_api_name,
1161 G_PKG_NAME,
1162 'OKC_API.G_RET_STS_ERROR',
1163 x_msg_count,
1164 x_msg_data,
1165 '_PVT'
1166 );
1167 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1168 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1169 (
1170 l_api_name,
1171 G_PKG_NAME,
1172 'OKC_API.G_RET_STS_UNEXP_ERROR',
1173 x_msg_count,
1174 x_msg_data,
1175 '_PVT'
1176 );
1177 WHEN OTHERS THEN
1178 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1179 (
1180 l_api_name,
1181 G_PKG_NAME,
1182 'OTHERS',
1183 x_msg_count,
1184 x_msg_data,
1185 '_PVT'
1186 );
1187 END insert_row;
1188 ----------------------------------------
1189 -- PL/SQL TBL insert_row for:potv_TBL --
1190 ----------------------------------------
1191 PROCEDURE insert_row(
1192 p_api_version IN NUMBER,
1193 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1194 x_return_status OUT NOCOPY VARCHAR2,
1195 x_msg_count OUT NOCOPY NUMBER,
1196 x_msg_data OUT NOCOPY VARCHAR2,
1197 p_potv_tbl IN potv_tbl_type,
1198 x_potv_tbl OUT NOCOPY potv_tbl_type,
1199 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1200
1201 l_api_version CONSTANT NUMBER := 1;
1202 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1203 i NUMBER := 0;
1204 BEGIN
1205 OKC_API.init_msg_list(p_init_msg_list);
1206 -- Make sure PL/SQL table has records in it before passing
1207 IF (p_potv_tbl.COUNT > 0) THEN
1208 i := p_potv_tbl.FIRST;
1209 LOOP
1210 DECLARE
1211 l_error_rec OKC_API.ERROR_REC_TYPE;
1212 BEGIN
1213 l_error_rec.api_name := l_api_name;
1214 l_error_rec.api_package := G_PKG_NAME;
1215 l_error_rec.idx := i;
1216 insert_row (
1217 p_api_version => p_api_version,
1218 p_init_msg_list => OKC_API.G_FALSE,
1219 x_return_status => l_error_rec.error_type,
1220 x_msg_count => l_error_rec.msg_count,
1221 x_msg_data => l_error_rec.msg_data,
1222 p_potv_rec => p_potv_tbl(i),
1223 x_potv_rec => x_potv_tbl(i));
1224 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1225 l_error_rec.SQLCODE := SQLCODE;
1226 load_error_tbl(l_error_rec, px_error_tbl);
1227 ELSE
1228 x_msg_count := l_error_rec.msg_count;
1229 x_msg_data := l_error_rec.msg_data;
1230 END IF;
1231 EXCEPTION
1232 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1233 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1234 l_error_rec.SQLCODE := SQLCODE;
1235 load_error_tbl(l_error_rec, px_error_tbl);
1236 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1237 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1238 l_error_rec.SQLCODE := SQLCODE;
1239 load_error_tbl(l_error_rec, px_error_tbl);
1240 WHEN OTHERS THEN
1241 l_error_rec.error_type := 'OTHERS';
1242 l_error_rec.SQLCODE := SQLCODE;
1243 load_error_tbl(l_error_rec, px_error_tbl);
1244 END;
1245 EXIT WHEN (i = p_potv_tbl.LAST);
1246 i := p_potv_tbl.NEXT(i);
1247 END LOOP;
1248 END IF;
1249 -- Loop through the error_tbl to find the error with the highest severity
1250 -- and return it.
1251 x_return_status := find_highest_exception(px_error_tbl);
1252 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1253 EXCEPTION
1254 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1255 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1256 (
1257 l_api_name,
1258 G_PKG_NAME,
1259 'OKC_API.G_RET_STS_ERROR',
1260 x_msg_count,
1261 x_msg_data,
1262 '_PVT'
1263 );
1264 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1265 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1266 (
1267 l_api_name,
1268 G_PKG_NAME,
1269 'OKC_API.G_RET_STS_UNEXP_ERROR',
1270 x_msg_count,
1271 x_msg_data,
1272 '_PVT'
1273 );
1274 WHEN OTHERS THEN
1275 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1276 (
1277 l_api_name,
1278 G_PKG_NAME,
1279 'OTHERS',
1280 x_msg_count,
1281 x_msg_data,
1282 '_PVT'
1283 );
1284 END insert_row;
1285
1286 ----------------------------------------
1287 -- PL/SQL TBL insert_row for:potv_TBL --
1288 ----------------------------------------
1289 PROCEDURE insert_row(
1290 p_api_version IN NUMBER,
1291 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1292 x_return_status OUT NOCOPY VARCHAR2,
1293 x_msg_count OUT NOCOPY NUMBER,
1294 x_msg_data OUT NOCOPY VARCHAR2,
1295 p_potv_tbl IN potv_tbl_type,
1296 x_potv_tbl OUT NOCOPY potv_tbl_type) IS
1297
1298 l_api_version CONSTANT NUMBER := 1;
1299 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1300 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1301 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1302 BEGIN
1303 OKC_API.init_msg_list(p_init_msg_list);
1304 -- Make sure PL/SQL table has records in it before passing
1305 IF (p_potv_tbl.COUNT > 0) THEN
1306 insert_row (
1307 p_api_version => p_api_version,
1308 p_init_msg_list => OKC_API.G_FALSE,
1309 x_return_status => x_return_status,
1310 x_msg_count => x_msg_count,
1311 x_msg_data => x_msg_data,
1312 p_potv_tbl => p_potv_tbl,
1313 x_potv_tbl => x_potv_tbl,
1314 px_error_tbl => l_error_tbl);
1315 END IF;
1316 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1317 EXCEPTION
1318 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1319 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1320 (
1321 l_api_name,
1322 G_PKG_NAME,
1323 'OKC_API.G_RET_STS_ERROR',
1324 x_msg_count,
1325 x_msg_data,
1326 '_PVT'
1327 );
1328 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1329 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1330 (
1331 l_api_name,
1332 G_PKG_NAME,
1333 'OKC_API.G_RET_STS_UNEXP_ERROR',
1334 x_msg_count,
1335 x_msg_data,
1336 '_PVT'
1337 );
1338 WHEN OTHERS THEN
1339 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1340 (
1341 l_api_name,
1342 G_PKG_NAME,
1343 'OTHERS',
1344 x_msg_count,
1345 x_msg_data,
1346 '_PVT'
1347 );
1348 END insert_row;
1349
1350 ---------------------------------------------------------------------------
1351 -- PROCEDURE lock_row
1352 ---------------------------------------------------------------------------
1353 --------------------------------------
1354 -- lock_row for:okl_pool_types --
1355 --------------------------------------
1356 PROCEDURE lock_row(
1357 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1358 x_return_status OUT NOCOPY VARCHAR2,
1359 x_msg_count OUT NOCOPY NUMBER,
1360 x_msg_data OUT NOCOPY VARCHAR2,
1361 p_pot_rec IN pot_rec_type) IS
1362
1363 E_Resource_Busy EXCEPTION;
1364 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1365 CURSOR lock_csr (p_pot_rec IN pot_rec_type) IS
1366 SELECT OBJECT_VERSION_NUMBER
1367 FROM okl_pool_types
1368 WHERE ID = p_pot_rec.id
1369 AND OBJECT_VERSION_NUMBER = p_pot_rec.object_version_number
1370 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1371
1372 CURSOR lchk_csr (p_pot_rec IN pot_rec_type) IS
1373 SELECT OBJECT_VERSION_NUMBER
1374 FROM okl_pool_types
1375 WHERE ID = p_pot_rec.id;
1376 l_api_version CONSTANT NUMBER := 1;
1377 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1378 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1379 l_object_version_number okl_pool_types.OBJECT_VERSION_NUMBER%TYPE;
1380 lc_object_version_number okl_pool_types.OBJECT_VERSION_NUMBER%TYPE;
1381 l_row_notfound BOOLEAN := FALSE;
1382 lc_row_notfound BOOLEAN := FALSE;
1383 BEGIN
1384 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1385 p_init_msg_list,
1386 '_PVT',
1387 x_return_status);
1388 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1389 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1390 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1391 RAISE OKC_API.G_EXCEPTION_ERROR;
1392 END IF;
1393 BEGIN
1394 OPEN lock_csr(p_pot_rec);
1395 FETCH lock_csr INTO l_object_version_number;
1396 l_row_notfound := lock_csr%NOTFOUND;
1397 CLOSE lock_csr;
1398 EXCEPTION
1399 WHEN E_Resource_Busy THEN
1400 IF (lock_csr%ISOPEN) THEN
1401 CLOSE lock_csr;
1402 END IF;
1403 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1404 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1405 END;
1406
1407 IF ( l_row_notfound ) THEN
1408 OPEN lchk_csr(p_pot_rec);
1409 FETCH lchk_csr INTO lc_object_version_number;
1410 lc_row_notfound := lchk_csr%NOTFOUND;
1411 CLOSE lchk_csr;
1412 END IF;
1413 IF (lc_row_notfound) THEN
1414 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1415 RAISE OKC_API.G_EXCEPTION_ERROR;
1416 ELSIF lc_object_version_number > p_pot_rec.object_version_number THEN
1417 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1418 RAISE OKC_API.G_EXCEPTION_ERROR;
1419 ELSIF lc_object_version_number <> p_pot_rec.object_version_number THEN
1420 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1421 RAISE OKC_API.G_EXCEPTION_ERROR;
1422 ELSIF lc_object_version_number = -1 THEN
1423 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1424 RAISE OKC_API.G_EXCEPTION_ERROR;
1425 END IF;
1426 x_return_status := l_return_status;
1427 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1428 EXCEPTION
1429 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1430 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1431 (
1432 l_api_name,
1433 G_PKG_NAME,
1434 'OKC_API.G_RET_STS_ERROR',
1435 x_msg_count,
1436 x_msg_data,
1437 '_PVT'
1438 );
1439 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1440 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1441 (
1442 l_api_name,
1443 G_PKG_NAME,
1444 'OKC_API.G_RET_STS_UNEXP_ERROR',
1445 x_msg_count,
1446 x_msg_data,
1447 '_PVT'
1448 );
1449 WHEN OTHERS THEN
1450 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1451 (
1452 l_api_name,
1453 G_PKG_NAME,
1454 'OTHERS',
1455 x_msg_count,
1456 x_msg_data,
1457 '_PVT'
1458 );
1459 END lock_row;
1460 -----------------------------------------
1461 -- lock_row for: OKL_POOL_TYPES_V --
1462 -----------------------------------------
1463 PROCEDURE lock_row(
1464 p_api_version IN NUMBER,
1465 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1466 x_return_status OUT NOCOPY VARCHAR2,
1467 x_msg_count OUT NOCOPY NUMBER,
1468 x_msg_data OUT NOCOPY VARCHAR2,
1469 p_potv_rec IN potv_rec_type) IS
1470
1471 l_api_version CONSTANT NUMBER := 1;
1472 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1473 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1474 l_pot_rec pot_rec_type;
1475 BEGIN
1476 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1477 G_PKG_NAME,
1478 p_init_msg_list,
1479 l_api_version,
1480 p_api_version,
1481 '_PVT',
1482 x_return_status);
1483 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1484 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1485 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1486 RAISE OKC_API.G_EXCEPTION_ERROR;
1487 END IF;
1488 -----------------------------------------
1489 -- Move VIEW record to "Child" records --
1490 -----------------------------------------
1491 migrate(p_potv_rec, l_pot_rec);
1492 ---------------------------------------------
1493 -- Call the LOCK_ROW for each child record --
1494 ---------------------------------------------
1495 lock_row(
1496 p_init_msg_list,
1497 l_return_status,
1498 x_msg_count,
1499 x_msg_data,
1500 l_pot_rec
1501 );
1502 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1503 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1504 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1505 RAISE OKC_API.G_EXCEPTION_ERROR;
1506 END IF;
1507 x_return_status := l_return_status;
1508 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1509 EXCEPTION
1510 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1511 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1512 (
1513 l_api_name,
1514 G_PKG_NAME,
1515 'OKC_API.G_RET_STS_ERROR',
1516 x_msg_count,
1517 x_msg_data,
1518 '_PVT'
1519 );
1520 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1521 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1522 (
1523 l_api_name,
1524 G_PKG_NAME,
1525 'OKC_API.G_RET_STS_UNEXP_ERROR',
1526 x_msg_count,
1527 x_msg_data,
1528 '_PVT'
1529 );
1530 WHEN OTHERS THEN
1531 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1532 (
1533 l_api_name,
1534 G_PKG_NAME,
1535 'OTHERS',
1536 x_msg_count,
1537 x_msg_data,
1538 '_PVT'
1539 );
1540 END lock_row;
1541 --------------------------------------
1542 -- PL/SQL TBL lock_row for:potv_TBL --
1543 --------------------------------------
1544 PROCEDURE lock_row(
1545 p_api_version IN NUMBER,
1546 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1547 x_return_status OUT NOCOPY VARCHAR2,
1548 x_msg_count OUT NOCOPY NUMBER,
1549 x_msg_data OUT NOCOPY VARCHAR2,
1550 p_potv_tbl IN potv_tbl_type,
1551 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1552
1553 l_api_version CONSTANT NUMBER := 1;
1554 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1555 i NUMBER := 0;
1556 BEGIN
1557 OKC_API.init_msg_list(p_init_msg_list);
1558 -- Make sure PL/SQL table has recrods in it before passing
1559 IF (p_potv_tbl.COUNT > 0) THEN
1560 i := p_potv_tbl.FIRST;
1561 LOOP
1562 DECLARE
1563 l_error_rec OKC_API.ERROR_REC_TYPE;
1564 BEGIN
1565 l_error_rec.api_name := l_api_name;
1566 l_error_rec.api_package := G_PKG_NAME;
1567 l_error_rec.idx := i;
1568 lock_row(
1569 p_api_version => p_api_version,
1570 p_init_msg_list => OKC_API.G_FALSE,
1571 x_return_status => l_error_rec.error_type,
1572 x_msg_count => l_error_rec.msg_count,
1573 x_msg_data => l_error_rec.msg_data,
1574 p_potv_rec => p_potv_tbl(i));
1575 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1576 l_error_rec.SQLCODE := SQLCODE;
1577 load_error_tbl(l_error_rec, px_error_tbl);
1578 ELSE
1579 x_msg_count := l_error_rec.msg_count;
1580 x_msg_data := l_error_rec.msg_data;
1581 END IF;
1582 EXCEPTION
1583 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1584 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1585 l_error_rec.SQLCODE := SQLCODE;
1586 load_error_tbl(l_error_rec, px_error_tbl);
1587 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1588 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1589 l_error_rec.SQLCODE := SQLCODE;
1590 load_error_tbl(l_error_rec, px_error_tbl);
1591 WHEN OTHERS THEN
1592 l_error_rec.error_type := 'OTHERS';
1593 l_error_rec.SQLCODE := SQLCODE;
1594 load_error_tbl(l_error_rec, px_error_tbl);
1595 END;
1596 EXIT WHEN (i = p_potv_tbl.LAST);
1597 i := p_potv_tbl.NEXT(i);
1598 END LOOP;
1599 END IF;
1600 -- Loop through the error_tbl to find the error with the highest severity
1601 -- and return it.
1602 x_return_status := find_highest_exception(px_error_tbl);
1603 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1604 EXCEPTION
1605 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1606 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1607 (
1608 l_api_name,
1609 G_PKG_NAME,
1610 'OKC_API.G_RET_STS_ERROR',
1611 x_msg_count,
1612 x_msg_data,
1613 '_PVT'
1614 );
1615 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1616 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1617 (
1618 l_api_name,
1619 G_PKG_NAME,
1620 'OKC_API.G_RET_STS_UNEXP_ERROR',
1621 x_msg_count,
1622 x_msg_data,
1623 '_PVT'
1624 );
1625 WHEN OTHERS THEN
1626 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1627 (
1628 l_api_name,
1629 G_PKG_NAME,
1630 'OTHERS',
1631 x_msg_count,
1632 x_msg_data,
1633 '_PVT'
1634 );
1635 END lock_row;
1636 --------------------------------------
1637 -- PL/SQL TBL lock_row for:potv_TBL --
1638 --------------------------------------
1639 PROCEDURE lock_row(
1640 p_api_version IN NUMBER,
1641 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1642 x_return_status OUT NOCOPY VARCHAR2,
1643 x_msg_count OUT NOCOPY NUMBER,
1644 x_msg_data OUT NOCOPY VARCHAR2,
1645 p_potv_tbl IN potv_tbl_type) IS
1646
1647 l_api_version CONSTANT NUMBER := 1;
1648 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1649 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1650 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1651 BEGIN
1652 OKC_API.init_msg_list(p_init_msg_list);
1653 -- Make sure PL/SQL table has recrods in it before passing
1654 IF (p_potv_tbl.COUNT > 0) THEN
1655 lock_row(
1656 p_api_version => p_api_version,
1657 p_init_msg_list => OKC_API.G_FALSE,
1658 x_return_status => x_return_status,
1659 x_msg_count => x_msg_count,
1660 x_msg_data => x_msg_data,
1661 p_potv_tbl => p_potv_tbl,
1662 px_error_tbl => l_error_tbl);
1663 END IF;
1664 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1665 EXCEPTION
1666 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1667 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1668 (
1669 l_api_name,
1670 G_PKG_NAME,
1671 'OKC_API.G_RET_STS_ERROR',
1672 x_msg_count,
1673 x_msg_data,
1674 '_PVT'
1675 );
1676 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1677 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1678 (
1679 l_api_name,
1680 G_PKG_NAME,
1681 'OKC_API.G_RET_STS_UNEXP_ERROR',
1682 x_msg_count,
1683 x_msg_data,
1684 '_PVT'
1685 );
1686 WHEN OTHERS THEN
1687 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1688 (
1689 l_api_name,
1690 G_PKG_NAME,
1691 'OTHERS',
1692 x_msg_count,
1693 x_msg_data,
1694 '_PVT'
1695 );
1696 END lock_row;
1697 ---------------------------------------------------------------------------
1698 -- PROCEDURE update_row
1699 ---------------------------------------------------------------------------
1700 ----------------------------------------
1701 -- update_row for:okl_pool_types --
1702 ----------------------------------------
1703 PROCEDURE update_row(
1704 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1705 x_return_status OUT NOCOPY VARCHAR2,
1706 x_msg_count OUT NOCOPY NUMBER,
1707 x_msg_data OUT NOCOPY VARCHAR2,
1708 p_pot_rec IN pot_rec_type,
1709 x_pot_rec OUT NOCOPY pot_rec_type) IS
1710
1711 l_api_version CONSTANT NUMBER := 1;
1712 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1713 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1714 l_pot_rec pot_rec_type := p_pot_rec;
1715 l_def_pot_rec pot_rec_type;
1716 l_row_notfound BOOLEAN := TRUE;
1717 ----------------------------------
1718 -- FUNCTION populate_new_record --
1719 ----------------------------------
1720 FUNCTION populate_new_record (
1721 p_pot_rec IN pot_rec_type,
1722 x_pot_rec OUT NOCOPY pot_rec_type
1723 ) RETURN VARCHAR2 IS
1724 l_pot_rec pot_rec_type;
1725 l_row_notfound BOOLEAN := TRUE;
1726 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1727 BEGIN
1728 x_pot_rec := p_pot_rec;
1729 -- Get current database values
1730 l_pot_rec := get_rec(p_pot_rec, l_return_status);
1731 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
1732
1733 IF (x_pot_rec.id = OKC_API.G_MISS_NUM)
1734 THEN
1735 x_pot_rec.id := l_pot_rec.id;
1736 END IF;
1737 IF (x_pot_rec.object_version_number = OKC_API.G_MISS_NUM)
1738 THEN
1739 x_pot_rec.object_version_number := l_pot_rec.object_version_number;
1740 END IF;
1741 IF (x_pot_rec.code = OKC_API.G_MISS_CHAR)
1742 THEN
1743 x_pot_rec.code := l_pot_rec.code;
1744 END IF;
1745 IF (x_pot_rec.description = OKC_API.G_MISS_CHAR)
1746 THEN
1747 x_pot_rec.description := l_pot_rec.description;
1748 END IF;
1749 IF (x_pot_rec.short_description = OKC_API.G_MISS_CHAR)
1750 THEN
1751 x_pot_rec.short_description := l_pot_rec.short_description;
1752 END IF;
1753 IF (x_pot_rec.from_date = OKC_API.G_MISS_DATE)
1754 THEN
1755 x_pot_rec.from_date := l_pot_rec.from_date;
1756 END IF;
1757 IF (x_pot_rec.TO_DATE = OKC_API.G_MISS_DATE)
1758 THEN
1759 x_pot_rec.TO_DATE := l_pot_rec.TO_DATE;
1760 END IF;
1761 IF (x_pot_rec.attribute_category = OKC_API.G_MISS_CHAR)
1762 THEN
1763 x_pot_rec.attribute_category := l_pot_rec.attribute_category;
1764 END IF;
1765 IF (x_pot_rec.attribute1 = OKC_API.G_MISS_CHAR)
1766 THEN
1767 x_pot_rec.attribute1 := l_pot_rec.attribute1;
1768 END IF;
1769 IF (x_pot_rec.attribute2 = OKC_API.G_MISS_CHAR)
1770 THEN
1771 x_pot_rec.attribute2 := l_pot_rec.attribute2;
1772 END IF;
1773 IF (x_pot_rec.attribute3 = OKC_API.G_MISS_CHAR)
1774 THEN
1775 x_pot_rec.attribute3 := l_pot_rec.attribute3;
1776 END IF;
1777 IF (x_pot_rec.attribute4 = OKC_API.G_MISS_CHAR)
1778 THEN
1779 x_pot_rec.attribute4 := l_pot_rec.attribute4;
1780 END IF;
1781 IF (x_pot_rec.attribute5 = OKC_API.G_MISS_CHAR)
1782 THEN
1783 x_pot_rec.attribute5 := l_pot_rec.attribute5;
1784 END IF;
1785 IF (x_pot_rec.attribute6 = OKC_API.G_MISS_CHAR)
1786 THEN
1787 x_pot_rec.attribute6 := l_pot_rec.attribute6;
1788 END IF;
1789 IF (x_pot_rec.attribute7 = OKC_API.G_MISS_CHAR)
1790 THEN
1791 x_pot_rec.attribute7 := l_pot_rec.attribute7;
1792 END IF;
1793 IF (x_pot_rec.attribute8 = OKC_API.G_MISS_CHAR)
1794 THEN
1795 x_pot_rec.attribute8 := l_pot_rec.attribute8;
1796 END IF;
1797 IF (x_pot_rec.attribute9 = OKC_API.G_MISS_CHAR)
1798 THEN
1799 x_pot_rec.attribute9 := l_pot_rec.attribute9;
1800 END IF;
1801 IF (x_pot_rec.attribute10 = OKC_API.G_MISS_CHAR)
1802 THEN
1803 x_pot_rec.attribute10 := l_pot_rec.attribute10;
1804 END IF;
1805 IF (x_pot_rec.attribute11 = OKC_API.G_MISS_CHAR)
1806 THEN
1807 x_pot_rec.attribute11 := l_pot_rec.attribute11;
1808 END IF;
1809 IF (x_pot_rec.attribute12 = OKC_API.G_MISS_CHAR)
1810 THEN
1811 x_pot_rec.attribute12 := l_pot_rec.attribute12;
1812 END IF;
1813 IF (x_pot_rec.attribute13 = OKC_API.G_MISS_CHAR)
1814 THEN
1815 x_pot_rec.attribute13 := l_pot_rec.attribute13;
1816 END IF;
1817 IF (x_pot_rec.attribute14 = OKC_API.G_MISS_CHAR)
1818 THEN
1819 x_pot_rec.attribute14 := l_pot_rec.attribute14;
1820 END IF;
1821 IF (x_pot_rec.attribute15 = OKC_API.G_MISS_CHAR)
1822 THEN
1823 x_pot_rec.attribute15 := l_pot_rec.attribute15;
1824 END IF;
1825 IF (x_pot_rec.created_by = OKC_API.G_MISS_NUM)
1826 THEN
1827 x_pot_rec.created_by := l_pot_rec.created_by;
1828 END IF;
1829 IF (x_pot_rec.creation_date = OKC_API.G_MISS_DATE)
1830 THEN
1831 x_pot_rec.creation_date := l_pot_rec.creation_date;
1832 END IF;
1833 IF (x_pot_rec.last_updated_by = OKC_API.G_MISS_NUM)
1834 THEN
1835 x_pot_rec.last_updated_by := l_pot_rec.last_updated_by;
1836 END IF;
1837 IF (x_pot_rec.last_update_date = OKC_API.G_MISS_DATE)
1838 THEN
1839 x_pot_rec.last_update_date := l_pot_rec.last_update_date;
1840 END IF;
1841 IF (x_pot_rec.last_update_login = OKC_API.G_MISS_NUM)
1842 THEN
1843 x_pot_rec.last_update_login := l_pot_rec.last_update_login;
1844 END IF;
1845 END IF;
1846 RETURN(l_return_status);
1847 END populate_new_record;
1848 --------------------------------------------
1849 -- Set_Attributes for:okl_pool_types --
1850 --------------------------------------------
1851 FUNCTION Set_Attributes (
1852 p_pot_rec IN pot_rec_type,
1853 x_pot_rec OUT NOCOPY pot_rec_type
1854 ) RETURN VARCHAR2 IS
1855 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1856 BEGIN
1857 x_pot_rec := p_pot_rec;
1858 x_pot_rec.OBJECT_VERSION_NUMBER := p_pot_rec.OBJECT_VERSION_NUMBER + 1;
1859 RETURN(l_return_status);
1860 END Set_Attributes;
1861 BEGIN
1862 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1863 p_init_msg_list,
1864 '_PVT',
1865 x_return_status);
1866 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1867 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1868 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1869 RAISE OKC_API.G_EXCEPTION_ERROR;
1870 END IF;
1871 --- Setting item attributes
1872 l_return_status := Set_Attributes(
1873 p_pot_rec, -- IN
1874 l_pot_rec); -- OUT
1875 --- If any errors happen abort API
1876 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1877 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1878 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1879 RAISE OKC_API.G_EXCEPTION_ERROR;
1880 END IF;
1881 l_return_status := populate_new_record(l_pot_rec, l_def_pot_rec);
1882 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1883 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1884 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1885 RAISE OKC_API.G_EXCEPTION_ERROR;
1886 END IF;
1887 UPDATE okl_pool_types
1888 SET OBJECT_VERSION_NUMBER = l_def_pot_rec.object_version_number,
1889 CODE = l_def_pot_rec.code,
1890 DESCRIPTION = l_def_pot_rec.description,
1891 SHORT_DESCRIPTION = l_def_pot_rec.short_description,
1892 FROM_DATE = l_def_pot_rec.from_date,
1893 TO_DATE = l_def_pot_rec.TO_DATE,
1894 ATTRIBUTE_CATEGORY = l_def_pot_rec.attribute_category,
1895 ATTRIBUTE1 = l_def_pot_rec.attribute1,
1896 ATTRIBUTE2 = l_def_pot_rec.attribute2,
1897 ATTRIBUTE3 = l_def_pot_rec.attribute3,
1898 ATTRIBUTE4 = l_def_pot_rec.attribute4,
1899 ATTRIBUTE5 = l_def_pot_rec.attribute5,
1900 ATTRIBUTE6 = l_def_pot_rec.attribute6,
1901 ATTRIBUTE7 = l_def_pot_rec.attribute7,
1902 ATTRIBUTE8 = l_def_pot_rec.attribute8,
1903 ATTRIBUTE9 = l_def_pot_rec.attribute9,
1904 ATTRIBUTE10 = l_def_pot_rec.attribute10,
1905 ATTRIBUTE11 = l_def_pot_rec.attribute11,
1906 ATTRIBUTE12 = l_def_pot_rec.attribute12,
1907 ATTRIBUTE13 = l_def_pot_rec.attribute13,
1908 ATTRIBUTE14 = l_def_pot_rec.attribute14,
1909 ATTRIBUTE15 = l_def_pot_rec.attribute15,
1910 CREATED_BY = l_def_pot_rec.created_by,
1911 CREATION_DATE = l_def_pot_rec.creation_date,
1912 LAST_UPDATED_BY = l_def_pot_rec.last_updated_by,
1913 LAST_UPDATE_DATE = l_def_pot_rec.last_update_date,
1914 LAST_UPDATE_LOGIN = l_def_pot_rec.last_update_login
1915 WHERE ID = l_def_pot_rec.id;
1916
1917 x_pot_rec := l_pot_rec;
1918 x_return_status := l_return_status;
1919 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1920 EXCEPTION
1921 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1922 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1923 (
1924 l_api_name,
1925 G_PKG_NAME,
1926 'OKC_API.G_RET_STS_ERROR',
1927 x_msg_count,
1928 x_msg_data,
1929 '_PVT'
1930 );
1931 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1932 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1933 (
1934 l_api_name,
1935 G_PKG_NAME,
1936 'OKC_API.G_RET_STS_UNEXP_ERROR',
1937 x_msg_count,
1938 x_msg_data,
1939 '_PVT'
1940 );
1941 WHEN OTHERS THEN
1942 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1943 (
1944 l_api_name,
1945 G_PKG_NAME,
1946 'OTHERS',
1947 x_msg_count,
1948 x_msg_data,
1949 '_PVT'
1950 );
1951 END update_row;
1952 ------------------------------------------
1953 -- update_row for:OKL_POOL_TYPES_V --
1954 ------------------------------------------
1955 PROCEDURE update_row(
1956 p_api_version IN NUMBER,
1957 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1958 x_return_status OUT NOCOPY VARCHAR2,
1959 x_msg_count OUT NOCOPY NUMBER,
1960 x_msg_data OUT NOCOPY VARCHAR2,
1961 p_potv_rec IN potv_rec_type,
1962 x_potv_rec OUT NOCOPY potv_rec_type) IS
1963
1964 l_api_version CONSTANT NUMBER := 1;
1965 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1966 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1967 l_potv_rec potv_rec_type := p_potv_rec;
1968 l_def_potv_rec potv_rec_type;
1969 l_db_potv_rec potv_rec_type;
1970 l_pot_rec pot_rec_type;
1971 lx_pot_rec pot_rec_type;
1972 -------------------------------
1973 -- FUNCTION fill_who_columns --
1974 -------------------------------
1975 FUNCTION fill_who_columns (
1976 p_potv_rec IN potv_rec_type
1977 ) RETURN potv_rec_type IS
1978 l_potv_rec potv_rec_type := p_potv_rec;
1979 BEGIN
1980 l_potv_rec.LAST_UPDATE_DATE := SYSDATE;
1981 l_potv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1982 l_potv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1983 RETURN(l_potv_rec);
1984 END fill_who_columns;
1985 ----------------------------------
1986 -- FUNCTION populate_new_record --
1987 ----------------------------------
1988 FUNCTION populate_new_record (
1989 p_potv_rec IN potv_rec_type,
1990 x_potv_rec OUT NOCOPY potv_rec_type
1991 ) RETURN VARCHAR2 IS
1992 l_row_notfound BOOLEAN := TRUE;
1993 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1994 BEGIN
1995 x_potv_rec := p_potv_rec;
1996 -- Get current database values
1997 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
1998 -- so it may be verified through LOCK_ROW.
1999 l_db_potv_rec := get_rec(p_potv_rec, l_return_status);
2000 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2001 IF (x_potv_rec.id = OKC_API.G_MISS_NUM)
2002 THEN
2003 x_potv_rec.id := l_db_potv_rec.id;
2004 END IF;
2005 IF (x_potv_rec.object_version_number = OKC_API.G_MISS_NUM)
2006 THEN
2007 x_potv_rec.object_version_number := l_db_potv_rec.object_version_number;
2008 END IF;
2009 IF (x_potv_rec.code = OKC_API.G_MISS_CHAR)
2010 THEN
2011 x_potv_rec.code := l_db_potv_rec.code;
2012 END IF;
2013 IF (x_potv_rec.description = OKC_API.G_MISS_CHAR)
2014 THEN
2015 x_potv_rec.description := l_db_potv_rec.description;
2016 END IF;
2017 IF (x_potv_rec.short_description = OKC_API.G_MISS_CHAR)
2018 THEN
2019 x_potv_rec.short_description := l_db_potv_rec.short_description;
2020 END IF;
2021 IF (x_potv_rec.from_date = OKC_API.G_MISS_DATE)
2022 THEN
2023 x_potv_rec.from_date := l_db_potv_rec.from_date;
2024 END IF;
2025 IF (x_potv_rec.TO_DATE = OKC_API.G_MISS_DATE)
2026 THEN
2027 x_potv_rec.TO_DATE := l_db_potv_rec.TO_DATE;
2028 END IF;
2029 IF (x_potv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2030 THEN
2031 x_potv_rec.attribute_category := l_db_potv_rec.attribute_category;
2032 END IF;
2033 IF (x_potv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2034 THEN
2035 x_potv_rec.attribute1 := l_db_potv_rec.attribute1;
2036 END IF;
2037 IF (x_potv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2038 THEN
2039 x_potv_rec.attribute2 := l_db_potv_rec.attribute2;
2040 END IF;
2041 IF (x_potv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2042 THEN
2043 x_potv_rec.attribute3 := l_db_potv_rec.attribute3;
2044 END IF;
2045 IF (x_potv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2046 THEN
2047 x_potv_rec.attribute4 := l_db_potv_rec.attribute4;
2048 END IF;
2049 IF (x_potv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2050 THEN
2051 x_potv_rec.attribute5 := l_db_potv_rec.attribute5;
2052 END IF;
2053 IF (x_potv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2054 THEN
2055 x_potv_rec.attribute6 := l_db_potv_rec.attribute6;
2056 END IF;
2057 IF (x_potv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2058 THEN
2059 x_potv_rec.attribute7 := l_db_potv_rec.attribute7;
2060 END IF;
2061 IF (x_potv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2062 THEN
2063 x_potv_rec.attribute8 := l_db_potv_rec.attribute8;
2064 END IF;
2065 IF (x_potv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2066 THEN
2067 x_potv_rec.attribute9 := l_db_potv_rec.attribute9;
2068 END IF;
2069 IF (x_potv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2070 THEN
2071 x_potv_rec.attribute10 := l_db_potv_rec.attribute10;
2072 END IF;
2073 IF (x_potv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2074 THEN
2075 x_potv_rec.attribute11 := l_db_potv_rec.attribute11;
2076 END IF;
2077 IF (x_potv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2078 THEN
2079 x_potv_rec.attribute12 := l_db_potv_rec.attribute12;
2080 END IF;
2081 IF (x_potv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2082 THEN
2083 x_potv_rec.attribute13 := l_db_potv_rec.attribute13;
2084 END IF;
2085 IF (x_potv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2086 THEN
2087 x_potv_rec.attribute14 := l_db_potv_rec.attribute14;
2088 END IF;
2089 IF (x_potv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2090 THEN
2091 x_potv_rec.attribute15 := l_db_potv_rec.attribute15;
2092 END IF;
2093 IF (x_potv_rec.created_by = OKC_API.G_MISS_NUM)
2094 THEN
2095 x_potv_rec.created_by := l_db_potv_rec.created_by;
2096 END IF;
2097 IF (x_potv_rec.creation_date = OKC_API.G_MISS_DATE)
2098 THEN
2099 x_potv_rec.creation_date := l_db_potv_rec.creation_date;
2100 END IF;
2101 IF (x_potv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2102 THEN
2103 x_potv_rec.last_updated_by := l_db_potv_rec.last_updated_by;
2104 END IF;
2105 IF (x_potv_rec.last_update_date = OKC_API.G_MISS_DATE)
2106 THEN
2107 x_potv_rec.last_update_date := l_db_potv_rec.last_update_date;
2108 END IF;
2109 IF (x_potv_rec.last_update_login = OKC_API.G_MISS_NUM)
2110 THEN
2111 x_potv_rec.last_update_login := l_db_potv_rec.last_update_login;
2112 END IF;
2113 END IF;
2114 RETURN(l_return_status);
2115 END populate_new_record;
2116 ----------------------------------------------
2117 -- Set_Attributes for:OKL_POOL_TYPES_V --
2118 ----------------------------------------------
2119 FUNCTION Set_Attributes (
2120 p_potv_rec IN potv_rec_type,
2121 x_potv_rec OUT NOCOPY potv_rec_type
2122 ) RETURN VARCHAR2 IS
2123 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2124 BEGIN
2125 x_potv_rec := p_potv_rec;
2126 RETURN(l_return_status);
2127 END Set_Attributes;
2128 BEGIN
2129 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2130 G_PKG_NAME,
2131 p_init_msg_list,
2132 l_api_version,
2133 p_api_version,
2134 '_PVT',
2135 x_return_status);
2136 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2137 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2138 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2139 RAISE OKC_API.G_EXCEPTION_ERROR;
2140 END IF;
2141 --- Setting item attributes
2142 l_return_status := Set_Attributes(
2143 p_potv_rec, -- IN
2144 x_potv_rec); -- OUT
2145 --- If any errors happen abort API
2146 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2147 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2148 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2149 RAISE OKC_API.G_EXCEPTION_ERROR;
2150 END IF;
2151 l_return_status := populate_new_record(l_potv_rec, l_def_potv_rec);
2152 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2153 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2154 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2155 RAISE OKC_API.G_EXCEPTION_ERROR;
2156 END IF;
2157 l_def_potv_rec := fill_who_columns(l_def_potv_rec);
2158 --- Validate all non-missing attributes (Item Level Validation)
2159 l_return_status := Validate_Attributes(l_def_potv_rec);
2160 --- If any errors happen abort API
2161 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2162 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2163 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2164 RAISE OKC_API.G_EXCEPTION_ERROR;
2165 END IF;
2166 l_return_status := Validate_Record(l_def_potv_rec, l_db_potv_rec);
2167 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2168 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2169 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2170 RAISE OKC_API.G_EXCEPTION_ERROR;
2171 END IF;
2172
2173 /*
2174 MVASUDEV, COMMENTED, 11/08/2002
2175 -- Lock the Record
2176 lock_row(
2177 p_api_version => p_api_version,
2178 p_init_msg_list => p_init_msg_list,
2179 x_return_status => l_return_status,
2180 x_msg_count => x_msg_count,
2181 x_msg_data => x_msg_data,
2182 p_potv_rec => p_potv_rec);
2183 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2184 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2185 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2186 RAISE OKC_API.G_EXCEPTION_ERROR;
2187 END IF;
2188 */
2189
2190 -----------------------------------------
2191 -- Move VIEW record to "Child" records --
2192 -----------------------------------------
2193 migrate(l_def_potv_rec, l_pot_rec);
2194 -----------------------------------------------
2195 -- Call the UPDATE_ROW for each child record --
2196 -----------------------------------------------
2197 update_row(
2198 p_init_msg_list,
2199 l_return_status,
2200 x_msg_count,
2201 x_msg_data,
2202 l_pot_rec,
2203 lx_pot_rec
2204 );
2205 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2206 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2207 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2208 RAISE OKC_API.G_EXCEPTION_ERROR;
2209 END IF;
2210 migrate(lx_pot_rec, l_def_potv_rec);
2211 x_potv_rec := l_def_potv_rec;
2212 x_return_status := l_return_status;
2213 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2214 EXCEPTION
2215 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2216 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2217 (
2218 l_api_name,
2219 G_PKG_NAME,
2220 'OKC_API.G_RET_STS_ERROR',
2221 x_msg_count,
2222 x_msg_data,
2223 '_PVT'
2224 );
2225 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2226 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2227 (
2228 l_api_name,
2229 G_PKG_NAME,
2230 'OKC_API.G_RET_STS_UNEXP_ERROR',
2231 x_msg_count,
2232 x_msg_data,
2233 '_PVT'
2234 );
2235 WHEN OTHERS THEN
2236 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2237 (
2238 l_api_name,
2239 G_PKG_NAME,
2240 'OTHERS',
2241 x_msg_count,
2242 x_msg_data,
2243 '_PVT'
2244 );
2245 END update_row;
2246 ----------------------------------------
2247 -- PL/SQL TBL update_row for:potv_tbl --
2248 ----------------------------------------
2249 PROCEDURE update_row(
2250 p_api_version IN NUMBER,
2251 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2252 x_return_status OUT NOCOPY VARCHAR2,
2253 x_msg_count OUT NOCOPY NUMBER,
2254 x_msg_data OUT NOCOPY VARCHAR2,
2255 p_potv_tbl IN potv_tbl_type,
2256 x_potv_tbl OUT NOCOPY potv_tbl_type,
2257 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2258
2259 l_api_version CONSTANT NUMBER := 1;
2260 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2261 i NUMBER := 0;
2262 BEGIN
2263 OKC_API.init_msg_list(p_init_msg_list);
2264 -- Make sure PL/SQL table has records in it before passing
2265 IF (p_potv_tbl.COUNT > 0) THEN
2266 i := p_potv_tbl.FIRST;
2267 LOOP
2268 DECLARE
2269 l_error_rec OKC_API.ERROR_REC_TYPE;
2270 BEGIN
2271 l_error_rec.api_name := l_api_name;
2272 l_error_rec.api_package := G_PKG_NAME;
2273 l_error_rec.idx := i;
2274 update_row (
2275 p_api_version => p_api_version,
2276 p_init_msg_list => OKC_API.G_FALSE,
2277 x_return_status => l_error_rec.error_type,
2278 x_msg_count => l_error_rec.msg_count,
2279 x_msg_data => l_error_rec.msg_data,
2280 p_potv_rec => p_potv_tbl(i),
2281 x_potv_rec => x_potv_tbl(i));
2282 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2283 l_error_rec.SQLCODE := SQLCODE;
2284 load_error_tbl(l_error_rec, px_error_tbl);
2285 ELSE
2286 x_msg_count := l_error_rec.msg_count;
2287 x_msg_data := l_error_rec.msg_data;
2288 END IF;
2289 EXCEPTION
2290 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2291 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2292 l_error_rec.SQLCODE := SQLCODE;
2293 load_error_tbl(l_error_rec, px_error_tbl);
2294 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2295 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2296 l_error_rec.SQLCODE := SQLCODE;
2297 load_error_tbl(l_error_rec, px_error_tbl);
2298 WHEN OTHERS THEN
2299 l_error_rec.error_type := 'OTHERS';
2300 l_error_rec.SQLCODE := SQLCODE;
2301 load_error_tbl(l_error_rec, px_error_tbl);
2302 END;
2303 EXIT WHEN (i = p_potv_tbl.LAST);
2304 i := p_potv_tbl.NEXT(i);
2305 END LOOP;
2306 END IF;
2307 -- Loop through the error_tbl to find the error with the highest severity
2308 -- and return it.
2309 x_return_status := find_highest_exception(px_error_tbl);
2310 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2311 EXCEPTION
2312 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2313 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2314 (
2315 l_api_name,
2316 G_PKG_NAME,
2317 'OKC_API.G_RET_STS_ERROR',
2318 x_msg_count,
2319 x_msg_data,
2320 '_PVT'
2321 );
2322 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2323 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2324 (
2325 l_api_name,
2326 G_PKG_NAME,
2327 'OKC_API.G_RET_STS_UNEXP_ERROR',
2328 x_msg_count,
2329 x_msg_data,
2330 '_PVT'
2331 );
2332 WHEN OTHERS THEN
2333 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2334 (
2335 l_api_name,
2336 G_PKG_NAME,
2337 'OTHERS',
2338 x_msg_count,
2339 x_msg_data,
2340 '_PVT'
2341 );
2342 END update_row;
2343
2344 ----------------------------------------
2345 -- PL/SQL TBL update_row for:potv_TBL --
2346 ----------------------------------------
2347 PROCEDURE update_row(
2348 p_api_version IN NUMBER,
2349 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2350 x_return_status OUT NOCOPY VARCHAR2,
2351 x_msg_count OUT NOCOPY NUMBER,
2352 x_msg_data OUT NOCOPY VARCHAR2,
2353 p_potv_tbl IN potv_tbl_type,
2354 x_potv_tbl OUT NOCOPY potv_tbl_type) IS
2355
2356 l_api_version CONSTANT NUMBER := 1;
2357 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2358 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2359 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2360 BEGIN
2361 OKC_API.init_msg_list(p_init_msg_list);
2362 -- Make sure PL/SQL table has records in it before passing
2363 IF (p_potv_tbl.COUNT > 0) THEN
2364 update_row (
2365 p_api_version => p_api_version,
2366 p_init_msg_list => OKC_API.G_FALSE,
2367 x_return_status => x_return_status,
2368 x_msg_count => x_msg_count,
2369 x_msg_data => x_msg_data,
2370 p_potv_tbl => p_potv_tbl,
2371 x_potv_tbl => x_potv_tbl,
2372 px_error_tbl => l_error_tbl);
2373 END IF;
2374 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2375 EXCEPTION
2376 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2377 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2378 (
2379 l_api_name,
2380 G_PKG_NAME,
2381 'OKC_API.G_RET_STS_ERROR',
2382 x_msg_count,
2383 x_msg_data,
2384 '_PVT'
2385 );
2386 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2387 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2388 (
2389 l_api_name,
2390 G_PKG_NAME,
2391 'OKC_API.G_RET_STS_UNEXP_ERROR',
2392 x_msg_count,
2393 x_msg_data,
2394 '_PVT'
2395 );
2396 WHEN OTHERS THEN
2397 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2398 (
2399 l_api_name,
2400 G_PKG_NAME,
2401 'OTHERS',
2402 x_msg_count,
2403 x_msg_data,
2404 '_PVT'
2405 );
2406 END update_row;
2407
2408 ---------------------------------------------------------------------------
2409 -- PROCEDURE delete_row
2410 ---------------------------------------------------------------------------
2411 ----------------------------------------
2412 -- delete_row for:okl_pool_types --
2413 ----------------------------------------
2414 PROCEDURE delete_row(
2415 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2416 x_return_status OUT NOCOPY VARCHAR2,
2417 x_msg_count OUT NOCOPY NUMBER,
2418 x_msg_data OUT NOCOPY VARCHAR2,
2419 p_pot_rec IN pot_rec_type) IS
2420
2421 l_api_version CONSTANT NUMBER := 1;
2422 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2423 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2424 l_pot_rec pot_rec_type := p_pot_rec;
2425 l_row_notfound BOOLEAN := TRUE;
2426 BEGIN
2427 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2428 p_init_msg_list,
2429 '_PVT',
2430 x_return_status);
2431 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2432 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2433 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2434 RAISE OKC_API.G_EXCEPTION_ERROR;
2435 END IF;
2436
2437 DELETE FROM okl_pool_types
2438 WHERE ID = p_pot_rec.id;
2439
2440 x_return_status := l_return_status;
2441 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2442 EXCEPTION
2443 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2444 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2445 (
2446 l_api_name,
2447 G_PKG_NAME,
2448 'OKC_API.G_RET_STS_ERROR',
2449 x_msg_count,
2450 x_msg_data,
2451 '_PVT'
2452 );
2453 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2454 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2455 (
2456 l_api_name,
2457 G_PKG_NAME,
2458 'OKC_API.G_RET_STS_UNEXP_ERROR',
2459 x_msg_count,
2460 x_msg_data,
2461 '_PVT'
2462 );
2463 WHEN OTHERS THEN
2464 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2465 (
2466 l_api_name,
2467 G_PKG_NAME,
2468 'OTHERS',
2469 x_msg_count,
2470 x_msg_data,
2471 '_PVT'
2472 );
2473 END delete_row;
2474 ------------------------------------------
2475 -- delete_row for:OKL_POOL_TYPES_V --
2476 ------------------------------------------
2477 PROCEDURE delete_row(
2478 p_api_version IN NUMBER,
2479 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2480 x_return_status OUT NOCOPY VARCHAR2,
2481 x_msg_count OUT NOCOPY NUMBER,
2482 x_msg_data OUT NOCOPY VARCHAR2,
2483 p_potv_rec IN potv_rec_type) IS
2484
2485 l_api_version CONSTANT NUMBER := 1;
2486 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2487 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2488 l_potv_rec potv_rec_type := p_potv_rec;
2489 l_pot_rec pot_rec_type;
2490 BEGIN
2491 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2492 G_PKG_NAME,
2493 p_init_msg_list,
2494 l_api_version,
2495 p_api_version,
2496 '_PVT',
2497 x_return_status);
2498 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2499 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2500 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2501 RAISE OKC_API.G_EXCEPTION_ERROR;
2502 END IF;
2503 -----------------------------------------
2504 -- Move VIEW record to "Child" records --
2505 -----------------------------------------
2506 migrate(l_potv_rec, l_pot_rec);
2507 -----------------------------------------------
2508 -- Call the DELETE_ROW for each child record --
2509 -----------------------------------------------
2510 delete_row(
2511 p_init_msg_list,
2512 l_return_status,
2513 x_msg_count,
2514 x_msg_data,
2515 l_pot_rec
2516 );
2517 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2518 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2519 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2520 RAISE OKC_API.G_EXCEPTION_ERROR;
2521 END IF;
2522 x_return_status := l_return_status;
2523 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2524 EXCEPTION
2525 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2526 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2527 (
2528 l_api_name,
2529 G_PKG_NAME,
2530 'OKC_API.G_RET_STS_ERROR',
2531 x_msg_count,
2532 x_msg_data,
2533 '_PVT'
2534 );
2535 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2536 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2537 (
2538 l_api_name,
2539 G_PKG_NAME,
2540 'OKC_API.G_RET_STS_UNEXP_ERROR',
2541 x_msg_count,
2542 x_msg_data,
2543 '_PVT'
2544 );
2545 WHEN OTHERS THEN
2546 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2547 (
2548 l_api_name,
2549 G_PKG_NAME,
2550 'OTHERS',
2551 x_msg_count,
2552 x_msg_data,
2553 '_PVT'
2554 );
2555 END delete_row;
2556 -----------------------------------------------------
2557 -- PL/SQL TBL delete_row for:OKL_POOL_TYPES_V --
2558 -----------------------------------------------------
2559 PROCEDURE delete_row(
2560 p_api_version IN NUMBER,
2561 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2562 x_return_status OUT NOCOPY VARCHAR2,
2563 x_msg_count OUT NOCOPY NUMBER,
2564 x_msg_data OUT NOCOPY VARCHAR2,
2565 p_potv_tbl IN potv_tbl_type,
2566 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2567
2568 l_api_version CONSTANT NUMBER := 1;
2569 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
2570 i NUMBER := 0;
2571 BEGIN
2572 OKC_API.init_msg_list(p_init_msg_list);
2573 -- Make sure PL/SQL table has records in it before passing
2574 IF (p_potv_tbl.COUNT > 0) THEN
2575 i := p_potv_tbl.FIRST;
2576 LOOP
2577 DECLARE
2578 l_error_rec OKC_API.ERROR_REC_TYPE;
2579 BEGIN
2580 l_error_rec.api_name := l_api_name;
2581 l_error_rec.api_package := G_PKG_NAME;
2582 l_error_rec.idx := i;
2583 delete_row (
2584 p_api_version => p_api_version,
2585 p_init_msg_list => OKC_API.G_FALSE,
2586 x_return_status => l_error_rec.error_type,
2587 x_msg_count => l_error_rec.msg_count,
2588 x_msg_data => l_error_rec.msg_data,
2589 p_potv_rec => p_potv_tbl(i));
2590 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2591 l_error_rec.SQLCODE := SQLCODE;
2592 load_error_tbl(l_error_rec, px_error_tbl);
2593 ELSE
2594 x_msg_count := l_error_rec.msg_count;
2595 x_msg_data := l_error_rec.msg_data;
2596 END IF;
2597 EXCEPTION
2598 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2599 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2600 l_error_rec.SQLCODE := SQLCODE;
2601 load_error_tbl(l_error_rec, px_error_tbl);
2602 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2603 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2604 l_error_rec.SQLCODE := SQLCODE;
2605 load_error_tbl(l_error_rec, px_error_tbl);
2606 WHEN OTHERS THEN
2607 l_error_rec.error_type := 'OTHERS';
2608 l_error_rec.SQLCODE := SQLCODE;
2609 load_error_tbl(l_error_rec, px_error_tbl);
2610 END;
2611 EXIT WHEN (i = p_potv_tbl.LAST);
2612 i := p_potv_tbl.NEXT(i);
2613 END LOOP;
2614 END IF;
2615 -- Loop through the error_tbl to find the error with the highest severity
2616 -- and return it.
2617 x_return_status := find_highest_exception(px_error_tbl);
2618 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2619 EXCEPTION
2620 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2621 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2622 (
2623 l_api_name,
2624 G_PKG_NAME,
2625 'OKC_API.G_RET_STS_ERROR',
2626 x_msg_count,
2627 x_msg_data,
2628 '_PVT'
2629 );
2630 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2631 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2632 (
2633 l_api_name,
2634 G_PKG_NAME,
2635 'OKC_API.G_RET_STS_UNEXP_ERROR',
2636 x_msg_count,
2637 x_msg_data,
2638 '_PVT'
2639 );
2640 WHEN OTHERS THEN
2641 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2642 (
2643 l_api_name,
2644 G_PKG_NAME,
2645 'OTHERS',
2646 x_msg_count,
2647 x_msg_data,
2648 '_PVT'
2649 );
2650 END delete_row;
2651
2652 -----------------------------------------------------
2653 -- PL/SQL TBL delete_row for:OKL_POOL_TYPES_V --
2654 -----------------------------------------------------
2655 PROCEDURE delete_row(
2656 p_api_version IN NUMBER,
2657 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2658 x_return_status OUT NOCOPY VARCHAR2,
2659 x_msg_count OUT NOCOPY NUMBER,
2660 x_msg_data OUT NOCOPY VARCHAR2,
2661 p_potv_tbl IN potv_tbl_type) IS
2662
2663 l_api_version CONSTANT NUMBER := 1;
2664 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2665 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2666 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2667 BEGIN
2668 OKC_API.init_msg_list(p_init_msg_list);
2669 -- Make sure PL/SQL table has records in it before passing
2670 IF (p_potv_tbl.COUNT > 0) THEN
2671 delete_row (
2672 p_api_version => p_api_version,
2673 p_init_msg_list => OKC_API.G_FALSE,
2674 x_return_status => x_return_status,
2675 x_msg_count => x_msg_count,
2676 x_msg_data => x_msg_data,
2677 p_potv_tbl => p_potv_tbl,
2678 px_error_tbl => l_error_tbl);
2679 END IF;
2680 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2681 EXCEPTION
2682 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2683 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2684 (
2685 l_api_name,
2686 G_PKG_NAME,
2687 'OKC_API.G_RET_STS_ERROR',
2688 x_msg_count,
2689 x_msg_data,
2690 '_PVT'
2691 );
2692 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2693 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2694 (
2695 l_api_name,
2696 G_PKG_NAME,
2697 'OKC_API.G_RET_STS_UNEXP_ERROR',
2698 x_msg_count,
2699 x_msg_data,
2700 '_PVT'
2701 );
2702 WHEN OTHERS THEN
2703 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2704 (
2705 l_api_name,
2706 G_PKG_NAME,
2707 'OTHERS',
2708 x_msg_count,
2709 x_msg_data,
2710 '_PVT'
2711 );
2712 END delete_row;
2713
2714 END OKL_POT_PVT;