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