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