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