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