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