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