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