[Home] [Help]
PACKAGE BODY: APPS.OKL_SFE_PVT
Source
1 PACKAGE BODY OKL_SFE_PVT AS
2 /* $Header: OKLSSFEB.pls 120.5.12010000.3 2009/07/21 00:26:36 sechawla ship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(okc_p_util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 NULL;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 NULL;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 NULL;
33 END api_copy;
34
35 ---------------------------------------------------------------------------
36 -- FUNCTION get_rec for: OKL_SIF_FEES
37
38 ---------------------------------------------------------------------------
39 FUNCTION get_rec (
40 p_sfe_rec IN sfe_rec_type,
41 x_no_data_found OUT NOCOPY BOOLEAN
42 ) RETURN sfe_rec_type IS
43 CURSOR sfe_pk_csr (p_id IN NUMBER) IS
44 SELECT
45 ID,
46 SFE_TYPE,
47 DATE_START,
48 DATE_PAID,
49 AMOUNT,
50 IDC_ACCOUNTING_FLAG,
51 INCOME_OR_EXPENSE,
52 DESCRIPTION,
53 FEE_INDEX_NUMBER,
54 LEVEL_INDEX_NUMBER,
55 ADVANCE_OR_ARREARS,
56 LEVEL_TYPE,
57 LOCK_LEVEL_STEP,
58 PERIOD,
59 NUMBER_OF_PERIODS,
60 LEVEL_LINE_NUMBER,
61 SIF_ID,
62 KLE_ID,
63 SIL_ID,
64 RATE,
65 -- mvasudev, 05/13/2002
66 QUERY_LEVEL_YN,
67 STRUCTURE,
68 DAYS_IN_PERIOD,
69 --
70 cash_effect_yn,
71 tax_effect_yn,
72 days_in_month,
73 days_in_year,
74 balance_type_code,
75 OBJECT_VERSION_NUMBER,
76 STREAM_INTERFACE_ATTRIBUTE01,
77 STREAM_INTERFACE_ATTRIBUTE02,
78 STREAM_INTERFACE_ATTRIBUTE03,
79 STREAM_INTERFACE_ATTRIBUTE04,
80 STREAM_INTERFACE_ATTRIBUTE05,
81 STREAM_INTERFACE_ATTRIBUTE06,
82 STREAM_INTERFACE_ATTRIBUTE07,
83 STREAM_INTERFACE_ATTRIBUTE08,
84 STREAM_INTERFACE_ATTRIBUTE09,
85 STREAM_INTERFACE_ATTRIBUTE10,
86 STREAM_INTERFACE_ATTRIBUTE11,
87 STREAM_INTERFACE_ATTRIBUTE12,
88 STREAM_INTERFACE_ATTRIBUTE13,
89 STREAM_INTERFACE_ATTRIBUTE14,
90 STREAM_INTERFACE_ATTRIBUTE15,
91 STREAM_INTERFACE_ATTRIBUTE16,
92 STREAM_INTERFACE_ATTRIBUTE17,
93 STREAM_INTERFACE_ATTRIBUTE18,
94 STREAM_INTERFACE_ATTRIBUTE19,
95 STREAM_INTERFACE_ATTRIBUTE20,
96 CREATED_BY,
97 LAST_UPDATED_BY,
98 CREATION_DATE,
99 LAST_UPDATE_DATE,
100 LAST_UPDATE_LOGIN,
101 DOWN_PAYMENT_AMOUNT,
102 orig_contract_line_id
103 FROM Okl_Sif_Fees
104 WHERE okl_sif_fees.id = p_id;
105 l_sfe_pk sfe_pk_csr%ROWTYPE;
106 l_sfe_rec sfe_rec_type;
107 BEGIN
108 x_no_data_found := TRUE;
109 -- Get current database values
110 OPEN sfe_pk_csr (p_sfe_rec.id);
111 FETCH sfe_pk_csr INTO
112 l_sfe_rec.ID,
113 l_sfe_rec.SFE_TYPE,
114 l_sfe_rec.DATE_START,
115 l_sfe_rec.DATE_PAID,
116 l_sfe_rec.AMOUNT,
117 l_sfe_rec.IDC_ACCOUNTING_FLAG,
118 l_sfe_rec.INCOME_OR_EXPENSE,
119 l_sfe_rec.DESCRIPTION,
120 l_sfe_rec.FEE_INDEX_NUMBER,
121 l_sfe_rec.LEVEL_INDEX_NUMBER,
122 l_sfe_rec.ADVANCE_OR_ARREARS,
123 l_sfe_rec.LEVEL_TYPE,
124 l_sfe_rec.LOCK_LEVEL_STEP,
125 l_sfe_rec.PERIOD,
126 l_sfe_rec.NUMBER_OF_PERIODS,
127 l_sfe_rec.LEVEL_LINE_NUMBER,
128 l_sfe_rec.SIF_ID,
129 l_sfe_rec.KLE_ID,
130 l_sfe_rec.SIL_ID,
131 l_sfe_rec.RATE,
132 -- mvasudev, 05/13/2002
133 l_sfe_rec.QUERY_LEVEL_YN,
134 l_sfe_rec.STRUCTURE,
135 l_sfe_rec.DAYS_IN_PERIOD,
136 --
137 l_sfe_rec.OBJECT_VERSION_NUMBER,
138 l_sfe_rec.cash_effect_yn,
139 l_sfe_rec.tax_effect_yn,
140 l_sfe_rec.days_in_month,
141 l_sfe_rec.days_in_year,
142 l_sfe_rec.balance_type_code ,
143 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE01,
144 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE02,
145 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE03,
146 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE04,
147 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE05,
148 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE06,
149 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE07,
150 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE08,
151 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE09,
152 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE10,
153 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE11,
154 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE12,
155 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE13,
156 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE14,
157 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE15,
158 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE16,
159 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE17,
160 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE18,
161 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE19,
162 l_sfe_rec.STREAM_INTERFACE_ATTRIBUTE20,
163 l_sfe_rec.CREATED_BY,
164 l_sfe_rec.LAST_UPDATED_BY,
165 l_sfe_rec.CREATION_DATE,
166 l_sfe_rec.LAST_UPDATE_DATE,
167 l_sfe_rec.LAST_UPDATE_LOGIN,
168 l_sfe_rec.DOWN_PAYMENT_AMOUNT,
169 l_sfe_rec.orig_contract_line_id;
170 x_no_data_found := sfe_pk_csr%NOTFOUND;
171 CLOSE sfe_pk_csr;
172 RETURN(l_sfe_rec);
173 END get_rec;
174
175 FUNCTION get_rec (
176 p_sfe_rec IN sfe_rec_type
177 ) RETURN sfe_rec_type IS
178 l_row_notfound BOOLEAN := TRUE;
179 BEGIN
180 RETURN(get_rec(p_sfe_rec, l_row_notfound));
181 END get_rec;
182 ---------------------------------------------------------------------------
183 -- FUNCTION get_rec for: OKL_SIF_FEES_V
184 ---------------------------------------------------------------------------
185 FUNCTION get_rec (
186 p_sfev_rec IN sfev_rec_type,
187 x_no_data_found OUT NOCOPY BOOLEAN
188 ) RETURN sfev_rec_type IS
189 CURSOR sfev_pk_csr (p_id IN NUMBER) IS
190 SELECT
191 ID,
192 SFE_TYPE,
193 DATE_START,
194 DATE_PAID,
195 AMOUNT,
196 IDC_ACCOUNTING_FLAG,
197 INCOME_OR_EXPENSE,
198 DESCRIPTION,
199 FEE_INDEX_NUMBER,
200 LEVEL_INDEX_NUMBER,
201 ADVANCE_OR_ARREARS,
202 LEVEL_TYPE,
203 LOCK_LEVEL_STEP,
204 PERIOD,
205 NUMBER_OF_PERIODS,
206 LEVEL_LINE_NUMBER,
207 SIF_ID,
208 KLE_ID,
209 SIL_ID,
210 RATE,
211 -- mvasudev, 05/13/2002
212 QUERY_LEVEL_YN,
213 STRUCTURE,
214 DAYS_IN_PERIOD,
215 --
216 OBJECT_VERSION_NUMBER,
217 cash_effect_yn,
218 tax_effect_yn,
219 days_in_month,
220 days_in_year,
221 balance_type_code ,
222 STREAM_INTERFACE_ATTRIBUTE01,
223 STREAM_INTERFACE_ATTRIBUTE02,
224 STREAM_INTERFACE_ATTRIBUTE03,
225 STREAM_INTERFACE_ATTRIBUTE04,
226 STREAM_INTERFACE_ATTRIBUTE05,
227 STREAM_INTERFACE_ATTRIBUTE06,
228 STREAM_INTERFACE_ATTRIBUTE07,
229 STREAM_INTERFACE_ATTRIBUTE08,
230 STREAM_INTERFACE_ATTRIBUTE09,
231 STREAM_INTERFACE_ATTRIBUTE10,
232 STREAM_INTERFACE_ATTRIBUTE11,
233 STREAM_INTERFACE_ATTRIBUTE12,
234 STREAM_INTERFACE_ATTRIBUTE13,
235 STREAM_INTERFACE_ATTRIBUTE14,
236 STREAM_INTERFACE_ATTRIBUTE15,
237 STREAM_INTERFACE_ATTRIBUTE16,
238 STREAM_INTERFACE_ATTRIBUTE17,
239 STREAM_INTERFACE_ATTRIBUTE18,
240 STREAM_INTERFACE_ATTRIBUTE19,
241 STREAM_INTERFACE_ATTRIBUTE20,
242 CREATED_BY,
243 LAST_UPDATED_BY,
244 CREATION_DATE,
245 LAST_UPDATE_DATE,
246 LAST_UPDATE_LOGIN,
247 DOWN_PAYMENT_AMOUNT,
248 orig_contract_line_id
249 FROM Okl_Sif_Fees_V
250 WHERE okl_sif_fees_v.id = p_id;
251 l_sfev_pk sfev_pk_csr%ROWTYPE;
252 l_sfev_rec sfev_rec_type;
253 BEGIN
254 x_no_data_found := TRUE;
255 -- Get current database values
256 OPEN sfev_pk_csr (p_sfev_rec.id);
257
258 FETCH sfev_pk_csr INTO
259 l_sfev_rec.ID,
260 l_sfev_rec.SFE_TYPE,
261 l_sfev_rec.DATE_START,
262 l_sfev_rec.DATE_PAID,
263 l_sfev_rec.AMOUNT,
264 l_sfev_rec.IDC_ACCOUNTING_FLAG,
265 l_sfev_rec.INCOME_OR_EXPENSE,
266 l_sfev_rec.DESCRIPTION,
267 l_sfev_rec.FEE_INDEX_NUMBER,
268 l_sfev_rec.LEVEL_INDEX_NUMBER,
269 l_sfev_rec.ADVANCE_OR_ARREARS,
270 l_sfev_rec.LEVEL_TYPE,
271 l_sfev_rec.LOCK_LEVEL_STEP,
272 l_sfev_rec.PERIOD,
273 l_sfev_rec.NUMBER_OF_PERIODS,
274 l_sfev_rec.LEVEL_LINE_NUMBER,
275 l_sfev_rec.SIF_ID,
276 l_sfev_rec.KLE_ID,
277 l_sfev_rec.SIL_ID,
278 l_sfev_rec.RATE,
279 -- mvasudev, 05/13/2002
280 l_sfev_rec.QUERY_LEVEL_YN,
281 l_sfev_rec.STRUCTURE,
282 l_sfev_rec.DAYS_IN_PERIOD,
283 --
284 l_sfev_rec.OBJECT_VERSION_NUMBER,
285 l_sfev_rec.cash_effect_yn,
286 l_sfev_rec.tax_effect_yn,
287 l_sfev_rec.days_in_month,
288 l_sfev_rec.days_in_year,
289 l_sfev_rec.balance_type_code ,
290 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE01,
291 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE02,
292 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE03,
293 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE04,
294 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE05,
295 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE06,
296 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE07,
297 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE08,
298 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE09,
299 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE10,
300 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE11,
301 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE12,
302 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE13,
303 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE14,
304 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE15,
305 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE16,
306 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE17,
307 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE18,
308 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE19,
309 l_sfev_rec.STREAM_INTERFACE_ATTRIBUTE20,
310 l_sfev_rec.CREATED_BY,
311 l_sfev_rec.LAST_UPDATED_BY,
312 l_sfev_rec.CREATION_DATE,
313 l_sfev_rec.LAST_UPDATE_DATE,
314 l_sfev_rec.LAST_UPDATE_LOGIN,
315 l_sfev_rec.DOWN_PAYMENT_AMOUNT,
316 l_sfev_rec.orig_contract_line_id;
317 x_no_data_found := sfev_pk_csr%NOTFOUND;
318 CLOSE sfev_pk_csr;
319 RETURN(l_sfev_rec);
320 END get_rec;
321
322 FUNCTION get_rec (
323 p_sfev_rec IN sfev_rec_type
324 ) RETURN sfev_rec_type IS
325 l_row_notfound BOOLEAN := TRUE;
326 BEGIN
327 RETURN(get_rec(p_sfev_rec, l_row_notfound));
328 END get_rec;
329
330 ----------------------------------------------------
331 -- FUNCTION null_out_defaults for: OKL_SIF_FEES_V --
332 ----------------------------------------------------
333 FUNCTION null_out_defaults (
334 p_sfev_rec IN sfev_rec_type
335 ) RETURN sfev_rec_type IS
336 l_sfev_rec sfev_rec_type := p_sfev_rec;
337 BEGIN
338 IF (l_sfev_rec.sfe_type = OKC_API.G_MISS_CHAR) THEN
339 l_sfev_rec.sfe_type := NULL;
340 END IF;
341 IF (l_sfev_rec.date_start = OKC_API.G_MISS_DATE) THEN
342 l_sfev_rec.date_start := NULL;
343 END IF;
344 IF (l_sfev_rec.date_paid = OKC_API.G_MISS_DATE) THEN
345 l_sfev_rec.date_paid := NULL;
346 END IF;
347 IF (l_sfev_rec.amount = OKC_API.G_MISS_NUM) THEN
348 l_sfev_rec.amount := NULL;
349 END IF;
350 IF (l_sfev_rec.idc_accounting_flag = OKC_API.G_MISS_CHAR) THEN
351 l_sfev_rec.idc_accounting_flag := NULL;
352 END IF;
353 IF (l_sfev_rec.income_or_expense = OKC_API.G_MISS_CHAR) THEN
354 l_sfev_rec.income_or_expense := NULL;
355 END IF;
356 IF (l_sfev_rec.description = OKC_API.G_MISS_CHAR) THEN
357 l_sfev_rec.description := NULL;
358 END IF;
359 IF (l_sfev_rec.fee_index_number = OKC_API.G_MISS_NUM) THEN
360 l_sfev_rec.fee_index_number := NULL;
361 END IF;
362 IF (l_sfev_rec.level_index_number = OKC_API.G_MISS_NUM) THEN
363 l_sfev_rec.level_index_number := NULL;
364
365 END IF;
366 IF (l_sfev_rec.advance_or_arrears = OKC_API.G_MISS_CHAR) THEN
367 l_sfev_rec.advance_or_arrears := NULL;
368 END IF;
369
370 IF (l_sfev_rec.cash_effect_yn = OKC_API.G_MISS_CHAR) THEN
371 l_sfev_rec.cash_effect_yn := NULL;
372 END IF;
373
374 IF (l_sfev_rec.tax_effect_yn = OKC_API.G_MISS_CHAR) THEN
375 l_sfev_rec.tax_effect_yn := NULL;
376 END IF;
377
378 IF (l_sfev_rec.days_in_month = OKC_API.G_MISS_CHAR) THEN
379 l_sfev_rec.days_in_month := NULL;
380 END IF;
381
382 IF (l_sfev_rec.days_in_year = OKC_API.G_MISS_CHAR) THEN
383 l_sfev_rec.days_in_year := NULL;
384 END IF;
385
386 IF (l_sfev_rec.balance_type_code = OKC_API.G_MISS_CHAR) THEN
387 l_sfev_rec.balance_type_code := NULL;
388 END IF;
389
390 IF (l_sfev_rec.level_type = OKC_API.G_MISS_CHAR) THEN
391 l_sfev_rec.level_type := NULL;
392 END IF;
393 IF (l_sfev_rec.lock_level_step = OKC_API.G_MISS_CHAR) THEN
394 l_sfev_rec.lock_level_step := NULL;
395 END IF;
396 IF (l_sfev_rec.period = OKC_API.G_MISS_CHAR) THEN
397 l_sfev_rec.period := NULL;
398 END IF;
399 IF (l_sfev_rec.number_of_periods = OKC_API.G_MISS_NUM) THEN
400 l_sfev_rec.number_of_periods := NULL;
401 END IF;
402 IF (l_sfev_rec.level_line_number = OKC_API.G_MISS_NUM) THEN
403 l_sfev_rec.level_line_number := NULL;
404 END IF;
405 IF (l_sfev_rec.sif_id = OKC_API.G_MISS_NUM) THEN
406 l_sfev_rec.sif_id := NULL;
407 END IF;
408 IF (l_sfev_rec.kle_id = OKC_API.G_MISS_NUM) THEN
409 l_sfev_rec.kle_id := NULL;
410 END IF;
411 IF (l_sfev_rec.sil_id = OKC_API.G_MISS_NUM) THEN
412 l_sfev_rec.sil_id := NULL;
413 END IF;
414 IF (l_sfev_rec.rate = OKC_API.G_MISS_NUM) THEN
415 l_sfev_rec.rate := NULL;
416 END IF;
417 -- mvasudev, 05/13/2002
418 IF (l_sfev_rec.query_level_yn = OKC_API.G_MISS_CHAR) THEN
419 l_sfev_rec.query_level_yn := NULL;
420 END IF;
421 IF (l_sfev_rec.structure = OKC_API.G_MISS_CHAR) THEN
422 l_sfev_rec.structure := NULL;
423 END IF;
424 IF (l_sfev_rec.DAYS_IN_PERIOD = OKC_API.G_MISS_NUM) THEN
425 l_sfev_rec.DAYS_IN_PERIOD := NULL;
426 END IF;
427 --
428 IF (l_sfev_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
429 l_sfev_rec.object_version_number := NULL;
430 END IF;
431 IF (l_sfev_rec.stream_interface_attribute01 = OKC_API.G_MISS_CHAR) THEN
432 l_sfev_rec.stream_interface_attribute01 := NULL;
433 END IF;
434 IF (l_sfev_rec.stream_interface_attribute02 = OKC_API.G_MISS_CHAR) THEN
435 l_sfev_rec.stream_interface_attribute02 := NULL;
436 END IF;
437 IF (l_sfev_rec.stream_interface_attribute03 = OKC_API.G_MISS_CHAR) THEN
438 l_sfev_rec.stream_interface_attribute03 := NULL;
439 END IF;
440 IF (l_sfev_rec.stream_interface_attribute04 = OKC_API.G_MISS_CHAR) THEN
441 l_sfev_rec.stream_interface_attribute04 := NULL;
442 END IF;
443 IF (l_sfev_rec.stream_interface_attribute05 = OKC_API.G_MISS_CHAR) THEN
444 l_sfev_rec.stream_interface_attribute05 := NULL;
445 END IF;
446 IF (l_sfev_rec.stream_interface_attribute06 = OKC_API.G_MISS_CHAR) THEN
447 l_sfev_rec.stream_interface_attribute06 := NULL;
448 END IF;
449 IF (l_sfev_rec.stream_interface_attribute07 = OKC_API.G_MISS_CHAR) THEN
450 l_sfev_rec.stream_interface_attribute07 := NULL;
451 END IF;
452 IF (l_sfev_rec.stream_interface_attribute08 = OKC_API.G_MISS_CHAR) THEN
453 l_sfev_rec.stream_interface_attribute08 := NULL;
454 END IF;
455 IF (l_sfev_rec.stream_interface_attribute09 = OKC_API.G_MISS_CHAR) THEN
456 l_sfev_rec.stream_interface_attribute09 := NULL;
457 END IF;
458 IF (l_sfev_rec.stream_interface_attribute10 = OKC_API.G_MISS_CHAR) THEN
459 l_sfev_rec.stream_interface_attribute10 := NULL;
460 END IF;
461 IF (l_sfev_rec.stream_interface_attribute11 = OKC_API.G_MISS_CHAR) THEN
462 l_sfev_rec.stream_interface_attribute11 := NULL;
463 END IF;
464 IF (l_sfev_rec.stream_interface_attribute12 = OKC_API.G_MISS_CHAR) THEN
465 l_sfev_rec.stream_interface_attribute12 := NULL;
466 END IF;
467 IF (l_sfev_rec.stream_interface_attribute13 = OKC_API.G_MISS_CHAR) THEN
468 l_sfev_rec.stream_interface_attribute13 := NULL;
469 END IF;
470 IF (l_sfev_rec.stream_interface_attribute14 = OKC_API.G_MISS_CHAR) THEN
471 l_sfev_rec.stream_interface_attribute14 := NULL;
472 END IF;
473 IF (l_sfev_rec.stream_interface_attribute15 = OKC_API.G_MISS_CHAR) THEN
474 l_sfev_rec.stream_interface_attribute15 := NULL;
475 END IF;
476 IF (l_sfev_rec.stream_interface_attribute16 = OKC_API.G_MISS_CHAR) THEN
477 l_sfev_rec.stream_interface_attribute16 := NULL;
478 END IF;
479 IF (l_sfev_rec.stream_interface_attribute17 = OKC_API.G_MISS_CHAR) THEN
480 l_sfev_rec.stream_interface_attribute17 := NULL;
481 END IF;
482 IF (l_sfev_rec.stream_interface_attribute18 = OKC_API.G_MISS_CHAR) THEN
483 l_sfev_rec.stream_interface_attribute18 := NULL;
484 END IF;
485
486 IF (l_sfev_rec.stream_interface_attribute19 = OKC_API.G_MISS_CHAR) THEN
487 l_sfev_rec.stream_interface_attribute19 := NULL;
488 END IF;
489 IF (l_sfev_rec.stream_interface_attribute20 = OKC_API.G_MISS_CHAR) THEN
490 l_sfev_rec.stream_interface_attribute20 := NULL;
491 END IF;
492 IF (l_sfev_rec.created_by = OKC_API.G_MISS_NUM) THEN
493 l_sfev_rec.created_by := NULL;
494 END IF;
495 IF (l_sfev_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
496 l_sfev_rec.last_updated_by := NULL;
497 END IF;
498 IF (l_sfev_rec.creation_date = OKC_API.G_MISS_DATE) THEN
499 l_sfev_rec.creation_date := NULL;
500 END IF;
501 IF (l_sfev_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
502 l_sfev_rec.last_update_date := NULL;
503 END IF;
504 IF (l_sfev_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
505 l_sfev_rec.last_update_login := NULL;
506 END IF;
507 IF (l_sfev_rec.down_payment_amount = OKC_API.G_MISS_NUM) THEN
508 l_sfev_rec.down_payment_amount := NULL;
509 END IF;
510
511 IF (l_sfev_rec.orig_contract_line_id = OKC_API.G_MISS_NUM) THEN
512 l_sfev_rec.orig_contract_line_id := NULL;
513 END IF;
514
515 RETURN(l_sfev_rec);
516 END null_out_defaults;
517
518 -- START change : akjain , 09/05/2001
519 /*
520 -- TAPI CODE COMMENTED OUT IN FAVOUR OF WRITING SEPARATE PROCEDURES FOR EACH ATTRIBUTE/COLUMN
521
522 ---------------------------------------------------------------------------
523 -- PROCEDURE Validate_Attributes
524 ---------------------------------------------------------------------------
525 --------------------------------------------
526 -- Validate_Attributes for:OKL_SIF_FEES_V --
527 --------------------------------------------
528 FUNCTION Validate_Attributes (
529 p_sfev_rec IN sfev_rec_type
530 ) RETURN VARCHAR2 IS
531 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
532 BEGIN
533 IF p_sfev_rec.id = OKC_API.G_MISS_NUM OR
534 p_sfev_rec.id IS NULL
535 THEN
536 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
537 l_return_status := OKC_API.G_RET_STS_ERROR;
538 ELSIF p_sfev_rec.sfe_type = OKC_API.G_MISS_CHAR OR
539 p_sfev_rec.sfe_type IS NULL
540 THEN
541 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'sfe_type');
542 l_return_status := OKC_API.G_RET_STS_ERROR;
543 ELSIF p_sfev_rec.income_or_expense = OKC_API.G_MISS_CHAR OR
544 p_sfev_rec.income_or_expense IS NULL
545 THEN
546 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'income_or_expense');
547 l_return_status := OKC_API.G_RET_STS_ERROR;
548 ELSIF p_sfev_rec.fee_index_number = OKC_API.G_MISS_NUM OR
549 p_sfev_rec.fee_index_number IS NULL
550 THEN
551 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'fee_index_number');
552 l_return_status := OKC_API.G_RET_STS_ERROR;
553 ELSIF p_sfev_rec.level_index_number = OKC_API.G_MISS_NUM OR
554 p_sfev_rec.level_index_number IS NULL
555 THEN
556 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'level_index_number');
557 l_return_status := OKC_API.G_RET_STS_ERROR;
558 ELSIF p_sfev_rec.advance_or_arrears = OKC_API.G_MISS_CHAR OR
559 p_sfev_rec.advance_or_arrears IS NULL
560 THEN
561 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'advance_or_arrears');
562 l_return_status := OKC_API.G_RET_STS_ERROR;
563 ELSIF p_sfev_rec.level_type = OKC_API.G_MISS_CHAR OR
564 p_sfev_rec.level_type IS NULL
565 THEN
566 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'level_type');
567 l_return_status := OKC_API.G_RET_STS_ERROR;
568 ELSIF p_sfev_rec.period = OKC_API.G_MISS_CHAR OR
569 p_sfev_rec.period IS NULL
570 THEN
571 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'period');
572 l_return_status := OKC_API.G_RET_STS_ERROR;
573 ELSIF p_sfev_rec.number_of_periods = OKC_API.G_MISS_NUM OR
574 p_sfev_rec.number_of_periods IS NULL
575 THEN
576 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'number_of_periods');
577 l_return_status := OKC_API.G_RET_STS_ERROR;
578 ELSIF p_sfev_rec.level_line_number = OKC_API.G_MISS_NUM OR
579 p_sfev_rec.level_line_number IS NULL
580 THEN
581 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'level_line_number');
582 l_return_status := OKC_API.G_RET_STS_ERROR;
583 ELSIF p_sfev_rec.object_version_number = OKC_API.G_MISS_NUM OR
584 p_sfev_rec.object_version_number IS NULL
585 THEN
586 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
587 l_return_status := OKC_API.G_RET_STS_ERROR;
588 END IF;
589 RETURN(l_return_status);
590 END Validate_Attributes;
591
592 */
593
594 -- END COMMENTED CODE akjain
595
596
597 /**
598 * Adding Individual Procedures for each Attribute that
599 * needs to be validated
600 */
601 ---------------------------------------------------------------------------
602 -- PROCEDURE Validate_Id
603 ---------------------------------------------------------------------------
604 -- Start of comments
605 --
606 -- Procedure Name : Validate_Id
607 -- Description :
608 -- Business Rules :
609 -- Parameters :
610 -- Version : 1.0
611 -- End of comments
612 ---------------------------------------------------------------------------
613 PROCEDURE Validate_Id(
614 p_sfev_rec IN sfev_rec_type,
615 x_return_status OUT NOCOPY VARCHAR2
616 ) IS
617
618 BEGIN
619 -- initialize return status
620 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
621
622 IF p_sfev_rec.id = Okc_Api.G_MISS_NUM OR
623 p_sfev_rec.id IS NULL
624 THEN
625 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
626 x_return_status := Okc_Api.G_RET_STS_ERROR;
627 RAISE G_EXCEPTION_HALT_VALIDATION;
628 END IF;
629
630 EXCEPTION
631 WHEN G_EXCEPTION_HALT_VALIDATION THEN
632 -- no processing necessary; validation can continue
633 -- with the next column
634 NULL;
635
636 WHEN OTHERS THEN
637 -- store SQL error message on message stack for caller
638 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
639 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
640 ,p_token1 => G_OKL_SQLCODE_TOKEN
641 ,p_token1_value => SQLCODE
642 ,p_token2 => G_OKL_SQLERRM_TOKEN
643 ,p_token2_value => SQLERRM);
644
645 -- notify caller of an UNEXPECTED error
646 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
647
648 END Validate_Id;
649
650 ---------------------------------------------------------------------------
651 -- PROCEDURE Validate_Object_Version_Number
652 ---------------------------------------------------------------------------
653 -- Start of comments
654 --
655 -- Procedure Name : Validate_Object_Version_Number
656 -- Description :
657 -- Business Rules :
658 -- Parameters :
659 -- Version : 1.0
660 -- End of comments
661 ---------------------------------------------------------------------------
662 PROCEDURE Validate_Object_Version_Number(
663 p_sfev_rec IN sfev_rec_type,
664 x_return_status OUT NOCOPY VARCHAR2
665 ) IS
666
667 BEGIN
668
669 -- initialize return status
670 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
671
672 IF p_sfev_rec.object_version_number = Okc_Api.G_MISS_NUM OR
673 p_sfev_rec.object_version_number IS NULL
674 THEN
675 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
676 x_return_status := Okc_Api.G_RET_STS_ERROR;
677 RAISE G_EXCEPTION_HALT_VALIDATION;
678 END IF;
679
680 EXCEPTION
681 WHEN G_EXCEPTION_HALT_VALIDATION THEN
682 -- no processing necessary; validation can continue
683 -- with the next column
684 NULL;
685
686 WHEN OTHERS THEN
687 -- store SQL error message on message stack for caller
688 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
689 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
690 ,p_token1 => G_OKL_SQLCODE_TOKEN
691 ,p_token1_value => SQLCODE
692 ,p_token2 => G_OKL_SQLERRM_TOKEN
693
694 ,p_token2_value => SQLERRM);
695
696 -- notify caller of an UNEXPECTED error
697 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
698
699 END Validate_Object_Version_Number;
700
701 ---------------------------------------------------------------------------
702 -- PROCEDURE Validate_Sfe_Type
703 ---------------------------------------------------------------------------
704 -- Start of comments
705 --
706 -- Procedure Name : Validate_Sfe_Type
707 -- Description :
708 -- Business Rules :
709 -- Parameters :
710 -- Version : 1.0
711 -- End of comments
712 ---------------------------------------------------------------------------
713 PROCEDURE Validate_Sfe_Type(
714 p_sfev_rec IN sfev_rec_type,
715 x_return_status OUT NOCOPY VARCHAR2
716 ) IS
717 l_found VARCHAR2(1);
718 BEGIN
719
720 -- initialize return status
721 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
722
723 IF p_sfev_rec.Sfe_Type = Okc_Api.G_MISS_CHAR OR
724 p_sfev_rec.Sfe_Type IS NULL
725 THEN
726 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Sfe_Type');
727 x_return_status := Okc_Api.G_RET_STS_ERROR;
728 RAISE G_EXCEPTION_HALT_VALIDATION;
729 ELSE
730 --Check if Sfe_Type exists in the fnd_common_lookups or not
731 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_SFE_TYPE',
732 p_lookup_code => p_sfev_rec.Sfe_Type);
733
734
735 IF (l_found <> OKL_API.G_TRUE ) THEN
736 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Sfe_Type');
737 x_return_status := Okc_Api.G_RET_STS_ERROR;
738 -- raise the exception as there's no matching foreign key value
739 RAISE G_EXCEPTION_HALT_VALIDATION;
740 END IF;
741
742
743 END IF;
744
745 EXCEPTION
746 WHEN G_EXCEPTION_HALT_VALIDATION THEN
747 -- no processing necessary; validation can continue
748 -- with the next column
749 NULL;
750
751 WHEN OTHERS THEN
752 -- store SQL error message on message stack for caller
753 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
754 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
755 ,p_token1 => G_OKL_SQLCODE_TOKEN
756 ,p_token1_value => SQLCODE
757 ,p_token2 => G_OKL_SQLERRM_TOKEN
758 ,p_token2_value => SQLERRM);
759
760 -- notify caller of an UNEXPECTED error
761 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
762
763 END Validate_Sfe_Type;
764
765
766 ---------------------------------------------------------------------------
767 -- PROCEDURE Validate_Income_Or_Expense
768 ---------------------------------------------------------------------------
769 -- Start of comments
770 --
771 -- Procedure Name : Validate_Income_Or_Expense
772 -- Description :
773 -- Business Rules :
774 -- Parameters :
775 -- Version : 1.0
776 -- End of comments
777 ---------------------------------------------------------------------------
778 PROCEDURE Validate_Income_Or_Expense(
779 p_sfev_rec IN sfev_rec_type,
780 x_return_status OUT NOCOPY VARCHAR2
781 ) IS
782
783 BEGIN
784
785 -- initialize return status
786 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
787 --dbms_output.put_line('Validate_Income_Or_Expense value '||p_sfev_rec.Income_Or_Expense);
788 IF p_sfev_rec.Income_Or_Expense = Okc_Api.G_MISS_CHAR OR
789 p_sfev_rec.Income_Or_Expense IS NULL
790 THEN
791 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Income_Or_Expense');
792 x_return_status := Okc_Api.G_RET_STS_ERROR;
793
794 RAISE G_EXCEPTION_HALT_VALIDATION;
795 END IF;
796
797 EXCEPTION
798 WHEN G_EXCEPTION_HALT_VALIDATION THEN
799 -- no processing necessary; validation can continue
800 -- with the next column
801 NULL;
802
803 WHEN OTHERS THEN
804 -- store SQL error message on message stack for caller
805 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
806 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
807 ,p_token1 => G_OKL_SQLCODE_TOKEN
808 ,p_token1_value => SQLCODE
809 ,p_token2 => G_OKL_SQLERRM_TOKEN
810 ,p_token2_value => SQLERRM);
811
812 -- notify caller of an UNEXPECTED error
813 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
814
815 END Validate_Income_Or_Expense;
816
817
818 ---------------------------------------------------------------------------
819 -- PROCEDURE Validate_Fee_Index_Number
820 ---------------------------------------------------------------------------
821 -- Start of comments
822 --
823 -- Procedure Name : Validate_Fee_Index_Number
824 -- Description :
825 -- Business Rules :
826 -- Parameters :
827 -- Version : 1.0
828 -- End of comments
829 ---------------------------------------------------------------------------
830 PROCEDURE Validate_Fee_Index_Number(
831 p_sfev_rec IN sfev_rec_type,
832 x_return_status OUT NOCOPY VARCHAR2
833 ) IS
834
835 BEGIN
836
837 -- initialize return status
838 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
839
840 IF p_sfev_rec.Fee_Index_Number = Okc_Api.G_MISS_NUM OR
841 p_sfev_rec.Fee_Index_Number IS NULL
842 THEN
843 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Fee_Index_Number');
844 x_return_status := Okc_Api.G_RET_STS_ERROR;
845 RAISE G_EXCEPTION_HALT_VALIDATION;
846 END IF;
847
848 EXCEPTION
849 WHEN G_EXCEPTION_HALT_VALIDATION THEN
850 -- no processing necessary; validation can continue
851 -- with the next column
852 NULL;
853
854 WHEN OTHERS THEN
855 -- store SQL error message on message stack for caller
856 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
857 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
858 ,p_token1 => G_OKL_SQLCODE_TOKEN
859 ,p_token1_value => SQLCODE
860 ,p_token2 => G_OKL_SQLERRM_TOKEN
861 ,p_token2_value => SQLERRM);
862
863 -- notify caller of an UNEXPECTED error
864 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
865
866 END Validate_Fee_Index_Number;
867
868
869
870 ---------------------------------------------------------------------------
871 -- PROCEDURE Validate_Level_Index_Number
872 ---------------------------------------------------------------------------
873 -- Start of comments
874 --
875 -- Procedure Name : Validate_Level_Index_Number
876 -- Description :
877 -- Business Rules :
878 -- Parameters :
879 -- Version : 1.0
880 -- End of comments
881 ---------------------------------------------------------------------------
882 PROCEDURE Validate_Level_Index_Number(
883 p_sfev_rec IN sfev_rec_type,
884 x_return_status OUT NOCOPY VARCHAR2
885 ) IS
886
887 BEGIN
888
889 -- initialize return status
890 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
891
892 IF p_sfev_rec.Level_Index_Number = Okc_Api.G_MISS_NUM OR
893
894 p_sfev_rec.Level_Index_Number IS NULL
895 THEN
896 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Level_Index_Number');
897 x_return_status := Okc_Api.G_RET_STS_ERROR;
898 RAISE G_EXCEPTION_HALT_VALIDATION;
899 END IF;
900
901 EXCEPTION
902 WHEN G_EXCEPTION_HALT_VALIDATION THEN
903 -- no processing necessary; validation can continue
904 -- with the next column
905 NULL;
906
907 WHEN OTHERS THEN
908 -- store SQL error message on message stack for caller
909 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
910 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
911 ,p_token1 => G_OKL_SQLCODE_TOKEN
912 ,p_token1_value => SQLCODE
913 ,p_token2 => G_OKL_SQLERRM_TOKEN
914 ,p_token2_value => SQLERRM);
915
916 -- notify caller of an UNEXPECTED error
917 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
918
919 END Validate_Level_Index_Number;
920
921 ---------------------------------------------------------------------------
922 -- PROCEDURE Validate_Advance_Or_Arrears
923 ---------------------------------------------------------------------------
924 -- Start of comments
925 --
926 -- Procedure Name : Validate_Advance_Or_Arrears
927 -- Description :
928 -- Business Rules :
929 -- Parameters :
930 -- Version : 1.0
931 -- End of comments
932 ---------------------------------------------------------------------------
933 PROCEDURE Validate_Advance_Or_Arrears(
934 p_sfev_rec IN sfev_rec_type,
935 x_return_status OUT NOCOPY VARCHAR2
936 ) IS
937
938 BEGIN
939
940 -- initialize return status
941 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
942 --dbms_output.put_line('Validate_Advance_Or_Arrears value '||p_sfev_rec.Advance_Or_Arrears);
943 IF p_sfev_rec.Advance_Or_Arrears = Okc_Api.G_MISS_CHAR OR
944 p_sfev_rec.Advance_Or_Arrears IS NULL
945 THEN
946 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Advance_Or_Arrears');
947 x_return_status := Okc_Api.G_RET_STS_ERROR;
948 RAISE G_EXCEPTION_HALT_VALIDATION;
949 END IF;
950
951 EXCEPTION
952 WHEN G_EXCEPTION_HALT_VALIDATION THEN
953 -- no processing necessary; validation can continue
954 -- with the next column
955 NULL;
956
957 WHEN OTHERS THEN
958 -- store SQL error message on message stack for caller
959 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
960 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
961 ,p_token1 => G_OKL_SQLCODE_TOKEN
962 ,p_token1_value => SQLCODE
963 ,p_token2 => G_OKL_SQLERRM_TOKEN
964 ,p_token2_value => SQLERRM);
965
966 -- notify caller of an UNEXPECTED error
967 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
968
969 END Validate_Advance_Or_Arrears;
970
971 /*
972 -- mvasudev -- 02/21/2002
973 -- Validation removed as LEVEL_TYPE is made NULLABLE.
974
975 ---------------------------------------------------------------------------
976 -- PROCEDURE Validate_Level_Type
977 ---------------------------------------------------------------------------
978 -- Start of comments
979 --
980 -- Procedure Name : Validate_Level_Type
981 -- Description :
982 -- Business Rules :
983 -- Parameters :
984 -- Version : 1.0
985 -- End of comments
986 ---------------------------------------------------------------------------
987 PROCEDURE Validate_Level_Type(
988 p_sfev_rec IN sfev_rec_type,
989 x_return_status OUT NOCOPY VARCHAR2
990 ) IS
991 l_found VARCHAR2(1);
992 BEGIN
993
994
995 -- initialize return status
996 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
997
998 IF p_sfev_rec.Level_Type = Okc_Api.G_MISS_CHAR OR
999 p_sfev_rec.Level_type IS NULL
1000 THEN
1001 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Level_type');
1002 x_return_status := Okc_Api.G_RET_STS_ERROR;
1003 RAISE G_EXCEPTION_HALT_VALIDATION;
1004 /*
1005 -- UnComment this Code if an FND entity is created for this
1006 ELSE
1007 --Check if Level_type exists in the fnd_common_lookups or not
1008 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_SFE_LEVEL',
1009 p_lookup_code => p_sfev_rec.Level_type);
1010
1011
1012 IF (l_found <> OKL_API.G_TRUE ) THEN
1013 OKC_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'Level_type');
1014 x_return_status := Okc_Api.G_RET_STS_ERROR;
1015 -- raise the exception as there's no matching foreign key value
1016 RAISE G_EXCEPTION_HALT_VALIDATION;
1017 END IF;
1018 *
1019 END IF;
1020
1021 EXCEPTION
1022 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1023 -- no processing necessary; validation can continue
1024 -- with the next column
1025 NULL;
1026
1027 WHEN OTHERS THEN
1028 -- store SQL error message on message stack for caller
1029 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1030 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1031 ,p_token1 => G_OKL_SQLCODE_TOKEN
1032 ,p_token1_value => SQLCODE
1033 ,p_token2 => G_OKL_SQLERRM_TOKEN
1034 ,p_token2_value => SQLERRM);
1035
1036 -- notify caller of an UNEXPECTED error
1037 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1038
1039 END Validate_Level_type;
1040
1041 -- end,mvasudev -- 02/21/2002
1042 */
1043
1044 ---------------------------------------------------------------------------
1045 -- PROCEDURE Validate_Sif_Id
1046 ---------------------------------------------------------------------------
1047 -- Start of comments
1048 --
1049 -- Procedure Name : Validate_Sif_Id
1050 -- Description :
1051 -- Business Rules :
1052 -- Parameters :
1053 -- Version : 1.0
1054 -- End of comments
1055 ---------------------------------------------------------------------------
1056 PROCEDURE Validate_Sif_Id(
1057 p_sfev_rec IN sfev_rec_type,
1058 x_return_status OUT NOCOPY VARCHAR2
1059 ) IS
1060
1061 l_dummy VARCHAR2(1) := '?';
1062 l_row_not_found BOOLEAN := FALSE;
1063
1064 -- Cursor For OKL_SIF_FK;
1065 CURSOR okl_sifv_pk_csr (p_id IN OKL_SIF_FEES_V.sif_id%TYPE) IS
1066 SELECT '1'
1067 FROM OKL_STREAM_INTERFACES_V
1068 WHERE OKL_STREAM_INTERFACES_V.id = p_id;
1069
1070 BEGIN
1071
1072 -- initialize return status
1073 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1074
1075 IF p_sfev_rec.sif_id = Okc_Api.G_MISS_NUM OR
1076 p_sfev_rec.sif_id IS NULL
1077 THEN
1078 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Sif_id');
1079 x_return_status := Okc_Api.G_RET_STS_ERROR;
1080 RAISE G_EXCEPTION_HALT_VALIDATION;
1081 END IF;
1082
1083 OPEN okl_sifv_pk_csr(p_sfev_rec.Sif_id);
1084 FETCH okl_sifv_pk_csr INTO l_dummy;
1085 l_row_not_found := okl_sifv_pk_csr%NOTFOUND;
1086 CLOSE okl_sifv_pk_csr;
1087
1088 IF l_row_not_found THEN
1089 Okc_Api.set_message(G_APP_NAME,G_OKL_UNQS);
1090 x_return_status := Okc_Api.G_RET_STS_ERROR;
1091 END IF;
1092
1093
1094 EXCEPTION
1095 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1096 -- no processing necessary; validation can continue
1097 -- with the next column
1098 NULL;
1099
1100 WHEN OTHERS THEN
1101 -- store SQL error message on message stack for caller
1102 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1103 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1104 ,p_token1 => G_OKL_SQLCODE_TOKEN
1105 ,p_token1_value => SQLCODE
1106 ,p_token2 => G_OKL_SQLERRM_TOKEN
1107 ,p_token2_value => SQLERRM);
1108
1109 -- notify caller of an UNEXPECTED error
1110 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1111
1112 -- verify that the cursor was closed
1113 IF okl_sifv_pk_csr%ISOPEN THEN
1114 CLOSE okl_sifv_pk_csr;
1115 END IF;
1116
1117 END Validate_Sif_Id;
1118
1119 ---------------------------------------------------------------------------
1120 -- PROCEDURE Validate_Kle_Id
1121 ---------------------------------------------------------------------------
1122 -- Start of comments
1123 --
1124 -- Procedure Name : Validate_Kle_Id
1125 -- Description :
1126 -- Business Rules :
1127 -- Parameters :
1128 -- Version : 1.0
1129 -- End of comments
1130 ---------------------------------------------------------------------------
1131 PROCEDURE Validate_Kle_Id(
1132 p_sfev_rec IN sfev_rec_type,
1133 x_return_status OUT NOCOPY VARCHAR2
1134 ) IS
1135
1136 l_dummy VARCHAR2(1) := '?';
1137 l_row_not_found BOOLEAN := FALSE;
1138
1139 -- Cursor For OKL_SIL_KLE_FK;
1140 CURSOR okl_klev_pk_csr (p_id IN OKL_SIF_FEES_V.kle_id%TYPE) IS
1141 SELECT '1'
1142 FROM OKL_K_LINES_V
1143 WHERE OKL_K_LINES_V.id = p_id;
1144
1145 BEGIN
1146
1147 -- initialize return status
1148 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1149
1150 IF p_sfev_rec.kle_id <> Okc_Api.G_MISS_NUM AND
1151 p_sfev_rec.kle_id IS NOT NULL
1152 THEN
1153 OPEN okl_klev_pk_csr(p_sfev_rec.kle_id);
1154 FETCH okl_klev_pk_csr INTO l_dummy;
1155 l_row_not_found := okl_klev_pk_csr%NOTFOUND;
1156 CLOSE okl_klev_pk_csr;
1157
1158 IF l_row_not_found THEN
1159 Okc_Api.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'Kle_id');
1160 x_return_status := Okc_Api.G_RET_STS_ERROR;
1161 END IF;
1162 END IF;
1163
1164 EXCEPTION
1165 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1166 -- no processing necessary; validation can continue
1167 -- with the next column
1168 NULL;
1169
1170 WHEN OTHERS THEN
1171 -- store SQL error message on message stack for caller
1172 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1173 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1174 ,p_token1 => G_OKL_SQLCODE_TOKEN
1175 ,p_token1_value => SQLCODE
1176 ,p_token2 => G_OKL_SQLERRM_TOKEN
1177 ,p_token2_value => SQLERRM);
1178
1179 -- notify caller of an UNEXPECTED error
1180 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1181
1182 -- verify that the cursor was closed
1183 IF okl_klev_pk_csr%ISOPEN THEN
1184 CLOSE okl_klev_pk_csr;
1185 END IF;
1186
1187 END Validate_Kle_Id;
1188
1189
1190 ---------------------------------------------------------------------------
1191 -- PROCEDURE Validate_Sil_Id
1192 ---------------------------------------------------------------------------
1193
1194 -- Start of comments
1195 --
1196 -- Procedure Name : Validate_Sil_Id
1197 -- Description :
1198 -- Business Rules :
1199 -- Parameters :
1200 -- Version : 1.0
1201 -- End of comments
1202 ---------------------------------------------------------------------------
1203 PROCEDURE Validate_Sil_Id(
1204 p_sfev_rec IN sfev_rec_type,
1205 x_return_status OUT NOCOPY VARCHAR2
1206 ) IS
1207
1208 l_dummy VARCHAR2(1) := '?';
1209 l_row_not_found BOOLEAN := FALSE;
1210
1211 -- Cursor For OKL_SIL_SIL_FK;
1212 CURSOR okl_sfev_pk_csr (p_id IN OKL_SIF_FEES_V.Sil_id%TYPE) IS
1213 SELECT '1'
1214 FROM OKL_SIF_LINES_V
1215 WHERE OKL_SIF_LINES_V.id = p_id;
1216
1217 BEGIN
1218
1219 -- initialize return status
1220 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1221
1222 IF p_sfev_rec.Sil_id <> Okc_Api.G_MISS_NUM AND
1223 p_sfev_rec.Sil_id IS NOT NULL
1224 THEN
1225 OPEN okl_sfev_pk_csr(p_sfev_rec.Sil_id);
1226 FETCH okl_sfev_pk_csr INTO l_dummy;
1227 l_row_not_found := okl_sfev_pk_csr%NOTFOUND;
1228 CLOSE okl_sfev_pk_csr;
1229 IF l_row_not_found THEN
1230 Okc_Api.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'Sil_id');
1231 x_return_status := Okc_Api.G_RET_STS_ERROR;
1232 END IF;
1233 END IF;
1234
1235 EXCEPTION
1236 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1237 -- no processing necessary; validation can continue
1238 -- with the next column
1239 NULL;
1240
1241 WHEN OTHERS THEN
1242 -- store SQL error message on message stack for caller
1243 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1244 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1245 ,p_token1 => G_OKL_SQLCODE_TOKEN
1246 ,p_token1_value => SQLCODE
1247 ,p_token2 => G_OKL_SQLERRM_TOKEN
1248 ,p_token2_value => SQLERRM);
1249
1250 -- notify caller of an UNEXPECTED error
1251 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1252
1253 -- verify that the cursor was closed
1254 IF okl_sfev_pk_csr%ISOPEN THEN
1255 CLOSE okl_sfev_pk_csr;
1256 END IF;
1257
1258 END Validate_Sil_Id;
1259
1260 ---------------------------------------------------------------------------
1261 -- PROCEDURE Validate_Date_Start
1262 ---------------------------------------------------------------------------
1263 -- Start of comments
1264 --
1265 -- Procedure Name : Validate_Date_Start
1266 -- Description :
1267 -- Business Rules :
1268 -- Parameters :
1269 -- Version : 1.0
1270 -- End of comments
1271 ---------------------------------------------------------------------------
1272 PROCEDURE Validate_Date_Start(
1273 p_sfev_rec IN sfev_rec_type,
1274 x_return_status OUT NOCOPY VARCHAR2
1275 ) IS
1276
1277 BEGIN
1278
1279 -- initialize return status
1280 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1281
1282 IF p_sfev_rec.Date_Start = Okc_Api.G_MISS_DATE OR
1283 p_sfev_rec.Date_Start IS NULL
1284 THEN
1285 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Date_Start');
1286 x_return_status := Okc_Api.G_RET_STS_ERROR;
1287 RAISE G_EXCEPTION_HALT_VALIDATION;
1288 END IF;
1289
1290 EXCEPTION
1291 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1292 -- no processing necessary; validation can continue
1293
1294 -- with the next column
1295 NULL;
1296
1297 WHEN OTHERS THEN
1298 -- store SQL error message on message stack for caller
1299 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1300 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1301 ,p_token1 => G_OKL_SQLCODE_TOKEN
1302 ,p_token1_value => SQLCODE
1303 ,p_token2 => G_OKL_SQLERRM_TOKEN
1304 ,p_token2_value => SQLERRM);
1305
1306 -- notify caller of an UNEXPECTED error
1307 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1308
1309 END Validate_Date_Start;
1310
1311 ---------------------------------------------------------------------------
1312 -- PROCEDURE Validate_Date_Paid
1313 ---------------------------------------------------------------------------
1314 -- Start of comments
1315 --
1316 -- Procedure Name : Validate_Date_Paid
1317 -- Description :
1318 -- Business Rules :
1319 -- Parameters :
1320 -- Version : 1.0
1321 -- End of comments
1322 ---------------------------------------------------------------------------
1323 PROCEDURE Validate_Date_Paid(
1324 p_sfev_rec IN sfev_rec_type,
1325 x_return_status OUT NOCOPY VARCHAR2
1326 ) IS
1327
1328 BEGIN
1329
1330 -- initialize return status
1331 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1332
1333 IF p_sfev_rec.Date_Paid = Okc_Api.G_MISS_DATE OR
1334 p_sfev_rec.Date_Paid IS NULL
1335 THEN
1336 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Date_Paid');
1337 x_return_status := Okc_Api.G_RET_STS_ERROR;
1338 RAISE G_EXCEPTION_HALT_VALIDATION;
1339 END IF;
1340
1341 EXCEPTION
1342 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1343 -- no processing necessary; validation can continue
1344 -- with the next column
1345 NULL;
1346
1347 WHEN OTHERS THEN
1348 -- store SQL error message on message stack for caller
1349 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1350 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1351 ,p_token1 => G_OKL_SQLCODE_TOKEN
1352 ,p_token1_value => SQLCODE
1353 ,p_token2 => G_OKL_SQLERRM_TOKEN
1354 ,p_token2_value => SQLERRM);
1355
1356 -- notify caller of an UNEXPECTED error
1357 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1358
1359 END Validate_Date_Paid;
1360
1361 ---------------------------------------------------------------------------
1362 -- PROCEDURE Validate_Amount
1363 ---------------------------------------------------------------------------
1364 -- Start of comments
1365 --
1366 -- Procedure Name : Validate_Amount
1367 -- Description :
1368 -- Business Rules :
1369 -- Parameters :
1370 -- Version : 1.0
1371 -- End of comments
1372 ---------------------------------------------------------------------------
1373 PROCEDURE Validate_Amount(
1374 p_sfev_rec IN sfev_rec_type,
1375 x_return_status OUT NOCOPY VARCHAR2
1376 ) IS
1377
1378 BEGIN
1379
1380 -- initialize return status
1381 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1382
1383 IF p_sfev_rec.Rate IS NULL AND (p_sfev_rec.Amount = Okc_Api.G_MISS_NUM OR
1384 p_sfev_rec.Amount IS NULL)
1385 THEN
1386 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Amount');
1387 x_return_status := Okc_Api.G_RET_STS_ERROR;
1388 RAISE G_EXCEPTION_HALT_VALIDATION;
1389 END IF;
1390
1391 EXCEPTION
1392 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1393
1394 -- no processing necessary; validation can continue
1395 -- with the next column
1396 NULL;
1397
1398 WHEN OTHERS THEN
1399 -- store SQL error message on message stack for caller
1400 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1401 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1402 ,p_token1 => G_OKL_SQLCODE_TOKEN
1403 ,p_token1_value => SQLCODE
1404 ,p_token2 => G_OKL_SQLERRM_TOKEN
1405 ,p_token2_value => SQLERRM);
1406
1407 -- notify caller of an UNEXPECTED error
1408 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1409
1410 END Validate_Amount;
1411
1412
1413 ---------------------------------------------------------------------------
1414 -- PROCEDURE Validate_Period
1415 ---------------------------------------------------------------------------
1416 -- Start of comments
1417 --
1418 -- Procedure Name : Validate_Period
1419 -- Description :
1420 -- Business Rules :
1421 -- Parameters :
1422 -- Version : 1.0
1423 -- End of comments
1424 ---------------------------------------------------------------------------
1425 PROCEDURE Validate_Period(
1426 p_sfev_rec IN sfev_rec_type,
1427 x_return_status OUT NOCOPY VARCHAR2
1428 ) IS
1429
1430 BEGIN
1431
1432 -- initialize return status
1433 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1434
1435 IF p_sfev_rec.Period = Okc_Api.G_MISS_CHAR OR
1436 p_sfev_rec.Period IS NULL
1437 THEN
1438 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Period');
1439 x_return_status := Okc_Api.G_RET_STS_ERROR;
1440 RAISE G_EXCEPTION_HALT_VALIDATION;
1441 END IF;
1442
1443 EXCEPTION
1444 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1445 -- no processing necessary; validation can continue
1446 -- with the next column
1447 NULL;
1448
1449 WHEN OTHERS THEN
1450 -- store SQL error message on message stack for caller
1451 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1452 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1453 ,p_token1 => G_OKL_SQLCODE_TOKEN
1454 ,p_token1_value => SQLCODE
1455 ,p_token2 => G_OKL_SQLERRM_TOKEN
1456 ,p_token2_value => SQLERRM);
1457
1458 -- notify caller of an UNEXPECTED error
1459 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1460
1461 END Validate_Period;
1462
1463 ---------------------------------------------------------------------------
1464 -- PROCEDURE Validate_Number_Of_Periods
1465 ---------------------------------------------------------------------------
1466 -- Start of comments
1467 --
1468 -- Procedure Name : Validate_Number_Of_Periods
1469 -- Description :
1470 -- Business Rules :
1471 -- Parameters :
1472 -- Version : 1.0
1473 -- End of comments
1474 ---------------------------------------------------------------------------
1475 PROCEDURE Validate_Number_Of_Periods(
1476 p_sfev_rec IN sfev_rec_type,
1477 x_return_status OUT NOCOPY VARCHAR2
1478 ) IS
1479
1480 BEGIN
1481
1482 -- initialize return status
1483 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
1484
1485 IF p_sfev_rec.Number_Of_Periods = Okc_Api.G_MISS_NUM OR
1486 p_sfev_rec.Number_Of_Periods IS NULL
1487 THEN
1488 Okc_Api.set_message(G_OKC_APP, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'Number_Of_Periods');
1489 x_return_status := Okc_Api.G_RET_STS_ERROR;
1490 RAISE G_EXCEPTION_HALT_VALIDATION;
1491 END IF;
1492
1493
1494 EXCEPTION
1495 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1496 -- no processing necessary; validation can continue
1497 -- with the next column
1498 NULL;
1499
1500 WHEN OTHERS THEN
1501 -- store SQL error message on message stack for caller
1502 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1503 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1504 ,p_token1 => G_OKL_SQLCODE_TOKEN
1505 ,p_token1_value => SQLCODE
1506 ,p_token2 => G_OKL_SQLERRM_TOKEN
1507 ,p_token2_value => SQLERRM);
1508
1509 -- notify caller of an UNEXPECTED error
1510 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1511
1512 END Validate_Number_Of_Periods;
1513
1514 ---------------------------------------------------------------------------
1515 -- FUNCTION Validate_Attributes
1516 ---------------------------------------------------------------------------
1517 -- Start of comments
1518 --
1519 -- Procedure Name : Validate_Attributes
1520 -- Description :
1521 -- Business Rules :
1522 -- Parameters :
1523 -- Version : 1.0
1524 -- End of comments
1525 ---------------------------------------------------------------------------
1526
1527 FUNCTION Validate_Attributes (
1528 p_sfev_rec IN sfev_rec_type
1529 ) RETURN VARCHAR2 IS
1530
1531 x_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1532 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
1533 BEGIN
1534 -- call each column-level validation
1535 -- Validate_Id
1536 Validate_Id(p_sfev_rec, x_return_status);
1537 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1538 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1539 -- need to exit
1540 l_return_status := x_return_status;
1541 RAISE G_EXCEPTION_HALT_VALIDATION;
1542 ELSE
1543 -- there was an error
1544 l_return_status := x_return_status;
1545 END IF;
1546 END IF;
1547
1548 -- Validate_Object_Version_Number
1549 Validate_Object_Version_Number(p_sfev_rec, x_return_status);
1550 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1551 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1552 -- need to exit
1553 l_return_status := x_return_status;
1554 RAISE G_EXCEPTION_HALT_VALIDATION;
1555 ELSE
1556 -- there was an error
1557 l_return_status := x_return_status;
1558 END IF;
1559 END IF;
1560
1561 -- Validate_Sif_id
1562 Validate_Sif_id(p_sfev_rec, x_return_status);
1563 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1564 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1565
1566 -- need to exit
1567 l_return_status := x_return_status;
1568 RAISE G_EXCEPTION_HALT_VALIDATION;
1569 ELSE
1570 -- there was an error
1571 l_return_status := x_return_status;
1572 END IF;
1573 END IF;
1574
1575 -- Validate_Kle_Id
1576 Validate_Kle_Id(p_sfev_rec, x_return_status);
1577 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1578 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1579 -- need to exit
1580 l_return_status := x_return_status;
1581 RAISE G_EXCEPTION_HALT_VALIDATION;
1582 ELSE
1583 -- there was an error
1584 l_return_status := x_return_status;
1585 END IF;
1586 END IF;
1587
1588 -- Validate_Sil_Id
1589 Validate_Sil_Id(p_sfev_rec, x_return_status);
1590 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1591 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1592 -- need to exit
1593 l_return_status := x_return_status;
1594 RAISE G_EXCEPTION_HALT_VALIDATION;
1595 ELSE
1596 -- there was an error
1597 l_return_status := x_return_status;
1598 END IF;
1599 END IF;
1600
1601
1602 -- Validate_Sfe_Type
1603 Validate_Sfe_Type(p_sfev_rec, x_return_status);
1604 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1605 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1606 -- need to exit
1607 l_return_status := x_return_status;
1608 RAISE G_EXCEPTION_HALT_VALIDATION;
1609 ELSE
1610 -- there was an error
1611 l_return_status := x_return_status;
1612 END IF;
1613 END IF;
1614
1615 -- Validate_Income_Or_Expense
1616 Validate_Income_Or_Expense(p_sfev_rec, x_return_status);
1617 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1618 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1619 -- need to exit
1620 l_return_status := x_return_status;
1621 RAISE G_EXCEPTION_HALT_VALIDATION;
1622 ELSE
1623 -- there was an error
1624 l_return_status := x_return_status;
1625 END IF;
1626 END IF;
1627
1628 -- Validate_Advance_Or_Arrears
1629 Validate_Advance_Or_Arrears(p_sfev_rec, x_return_status);
1630 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1631 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1632 -- need to exit
1633 l_return_status := x_return_status;
1634 RAISE G_EXCEPTION_HALT_VALIDATION;
1635 ELSE
1636 -- there was an error
1637 l_return_status := x_return_status;
1638 END IF;
1639 END IF;
1640
1641 -- Validate_Amount
1642 Validate_Amount(p_sfev_rec, x_return_status);
1643 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1644 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1645 -- need to exit
1646 l_return_status := x_return_status;
1647 RAISE G_EXCEPTION_HALT_VALIDATION;
1648 ELSE
1649 -- there was an error
1650 l_return_status := x_return_status;
1651 END IF;
1652 END IF;
1653
1654 IF (p_sfev_rec.Sfe_Type = G_SFE_TYPE_ONE_OFF)
1655 THEN
1656 -- Validate_Date_Paid
1657 Validate_Date_Paid(p_sfev_rec, x_return_status);
1658 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1659 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1660 -- need to exit
1661 l_return_status := x_return_status;
1662 RAISE G_EXCEPTION_HALT_VALIDATION;
1663 ELSE
1664 -- there was an error
1665 l_return_status := x_return_status;
1666 END IF;
1667 END IF;
1668 ELSIF ( p_sfev_rec.Sfe_Type = G_SFE_TYPE_PERIODIC_EXPENSE
1669 OR p_sfev_rec.Sfe_Type = G_SFE_TYPE_RENT
1670 OR p_sfev_rec.Sfe_Type = G_SFE_TYPE_PERIODIC_INCOME
1671 OR p_sfev_rec.Sfe_Type = G_SFE_TYPE_LOAN )
1672 THEN
1673 -- Validate_Date_Start
1674 Validate_Date_Start(p_sfev_rec, x_return_status);
1675 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1676 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1677 -- need to exit
1678 l_return_status := x_return_status;
1679 RAISE G_EXCEPTION_HALT_VALIDATION;
1680 ELSE
1681 -- there was an error
1682 l_return_status := x_return_status;
1683 END IF;
1684 END IF;
1685
1686 -- Validate_Level_Index_Number
1687 Validate_Level_Index_Number(p_sfev_rec, x_return_status);
1688
1689 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1690 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1691 -- need to exit
1692 l_return_status := x_return_status;
1693 RAISE G_EXCEPTION_HALT_VALIDATION;
1694 ELSE
1695 -- there was an error
1696 l_return_status := x_return_status;
1697 END IF;
1698 END IF;
1699
1700 /*
1701 -- mvasudev -- 02/21/2002
1702 -- Validation removed as LEVEL_TYPE is made NULLABLE.
1703
1704 -- Validate_Level_Type
1705 Validate_Level_Type(p_sfev_rec, x_return_status);
1706 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1707 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1708 -- need to exit
1709 l_return_status := x_return_status;
1710 RAISE G_EXCEPTION_HALT_VALIDATION;
1711 ELSE
1712 -- there was an error
1713 l_return_status := x_return_status;
1714 END IF;
1715 END IF;
1716 -- end,mvasudev -- 02/21/2002
1717 */
1718
1719 -- Validate_Period
1720 Validate_Period(p_sfev_rec, x_return_status);
1721
1722 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1723 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1724 -- need to exit
1725 l_return_status := x_return_status;
1726 RAISE G_EXCEPTION_HALT_VALIDATION;
1727 ELSE
1728 -- there was an error
1729 l_return_status := x_return_status;
1730 END IF;
1731 END IF;
1732 -- Validate_Number_Of_Periods
1733 Validate_Number_Of_Periods(p_sfev_rec, x_return_status);
1734 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1735 IF (x_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1736 -- need to exit
1737 l_return_status := x_return_status;
1738 RAISE G_EXCEPTION_HALT_VALIDATION;
1739 ELSE
1740 -- there was an error
1741 l_return_status := x_return_status;
1742 END IF;
1743 END IF;
1744 END IF;
1745 RETURN(l_return_status);
1746 EXCEPTION
1747 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1748 -- exit with return status
1749 NULL;
1750 RETURN (l_return_status);
1751
1752 WHEN OTHERS THEN
1753 -- store SQL error message on message stack for caller
1754 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
1755 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1756 p_token1 => G_OKL_SQLCODE_TOKEN,
1757 p_token1_value => SQLCODE,
1758 p_token2 => G_OKL_SQLERRM_TOKEN,
1759 p_token2_value => SQLERRM);
1760 -- notify caller of an UNEXPECTED error
1761 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1762
1763 RETURN(l_return_status);
1764 END Validate_Attributes;
1765 -- END change : akjain
1766
1767 PROCEDURE Validate_Unique_Sfe_Record(
1768 p_sfev_rec IN sfev_rec_type,
1769 x_return_status OUT NOCOPY VARCHAR2
1770 ) IS
1771
1772 l_dummy VARCHAR2(1) := '?';
1773 l_row_found BOOLEAN := FALSE;
1774
1775 -- Cursor For OKL_SIF_FEES_V - Unique Key Constraint
1776 CURSOR okl_sfe_unique_csr (p_rec IN sfev_rec_type) IS
1777 SELECT '1'
1778 FROM OKL_SIF_FEES_V
1779 WHERE OKL_SIF_FEES_V.sif_id = p_rec.sif_id
1780 AND
1781 OKL_SIF_FEES_V.sfe_type = p_rec.sfe_type
1782 AND
1783 OKL_SIF_FEES_V.fee_index_number = p_rec.fee_index_number
1784 AND
1785 OKL_SIF_FEES_V.sil_id = p_rec.sil_id
1786 AND
1787 OKL_SIF_FEES_V.level_index_number = p_rec.level_index_number
1788 AND
1789 id <> NVL(p_rec.id,-9999);
1790
1791 BEGIN
1792 OPEN okl_sfe_unique_csr (p_sfev_rec);
1793 FETCH okl_sfe_unique_csr INTO l_dummy;
1794 l_row_found := okl_sfe_unique_csr%FOUND;
1795 CLOSE okl_sfe_unique_csr;
1796
1797 IF l_row_found THEN
1798 Okc_Api.set_message(G_APP_NAME,G_OKL_UNQS);
1799 x_return_status := Okc_Api.G_RET_STS_ERROR;
1800 END IF;
1801 EXCEPTION
1802 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1803 -- no processing necessary; validation can continue
1804 -- with the next column
1805 NULL;
1806 IF okl_sfe_unique_csr%ISOPEN THEN
1807 CLOSE okl_sfe_unique_csr;
1808 END IF;
1809
1810 WHEN OTHERS THEN
1811 -- store SQL error message on message stack for caller
1812 Okc_Api.SET_MESSAGE(p_app_name => G_APP_NAME
1813 ,p_msg_name => G_OKL_UNEXPECTED_ERROR
1814 ,p_token1 => G_OKL_SQLCODE_TOKEN
1815 ,p_token1_value => SQLCODE
1816 ,p_token2 => G_OKL_SQLERRM_TOKEN
1817 ,p_token2_value => SQLERRM);
1818
1819 -- notify caller of an UNEXPECTED error
1820 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1821
1822 -- verify that the cursor was closed
1823 IF okl_sfe_unique_csr%ISOPEN THEN
1824 CLOSE okl_sfe_unique_csr;
1825 END IF;
1826 END Validate_Unique_Sfe_Record;
1827
1828 ---------------------------------------------------------------------------
1829 -- PROCEDURE Validate_Record
1830 ---------------------------------------------------------------------------
1831 ----------------------------------------
1832 -- Validate_Record for:OKL_SIF_FEES_V --
1833 ----------------------------------------
1834 FUNCTION Validate_Record (
1835 p_sfev_rec IN sfev_rec_type
1836 ) RETURN VARCHAR2 IS
1837 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1838 BEGIN
1839 -- Validate_Unique_Sfe_Record
1840 Validate_Unique_Sfe_Record(p_sfev_rec, l_return_status);
1841 IF (l_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1842 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
1843 RAISE G_EXCEPTION_HALT_VALIDATION;
1844 END IF;
1845 END IF;
1846
1847 RETURN(l_return_status);
1848 EXCEPTION
1849 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1850 -- exit with return status
1851 NULL;
1852 RETURN (l_return_status);
1853
1854 WHEN OTHERS THEN
1855 -- store SQL error message on message stack for caller
1856 Okc_Api.SET_MESSAGE(p_app_name => g_app_name,
1857 p_msg_name => G_OKL_UNEXPECTED_ERROR,
1858 p_token1 => G_OKL_SQLCODE_TOKEN,
1859 p_token1_value => SQLCODE,
1860 p_token2 => G_OKL_SQLERRM_TOKEN,
1861 p_token2_value => SQLERRM);
1862 -- notify caller of an UNEXPECTED error
1863 l_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
1864
1865 RETURN (l_return_status);
1866 END Validate_Record;
1867
1868 ---------------------------------------------------------------------------
1869 -- PROCEDURE Migrate
1870 ---------------------------------------------------------------------------
1871 PROCEDURE migrate (
1872 p_from IN sfev_rec_type,
1873 p_to IN OUT NOCOPY sfe_rec_type
1874 ) IS
1875 BEGIN
1876 p_to.id := p_from.id;
1877 p_to.sfe_type := p_from.sfe_type;
1878 p_to.date_start := p_from.date_start;
1879 p_to.date_paid := p_from.date_paid;
1880 p_to.amount := p_from.amount;
1881 p_to.idc_accounting_flag := p_from.idc_accounting_flag;
1882 p_to.income_or_expense := p_from.income_or_expense;
1883 p_to.description := p_from.description;
1884 p_to.fee_index_number := p_from.fee_index_number;
1885 p_to.level_index_number := p_from.level_index_number;
1886 p_to.advance_or_arrears := p_from.advance_or_arrears;
1887 p_to.level_type := p_from.level_type;
1888 p_to.lock_level_step := p_from.lock_level_step;
1889 p_to.period := p_from.period;
1890 p_to.number_of_periods := p_from.number_of_periods;
1891 p_to.level_line_number := p_from.level_line_number;
1892 p_to.sif_id := p_from.sif_id;
1893 p_to.kle_id := p_from.kle_id;
1894 p_to.sil_id := p_from.sil_id;
1895 p_to.rate := p_from.rate;
1896 -- mvasudev, 05/13/2002
1897 p_to.query_level_yn := p_from.query_level_yn;
1898 p_to.structure := p_from.structure;
1899 p_to.days_in_period := p_from.days_in_period;
1900 --
1901 p_to.object_version_number := p_from.object_version_number;
1902 p_to.cash_effect_yn := p_from.cash_effect_yn;
1903 p_to.tax_effect_yn := p_from.tax_effect_yn;
1904 p_to.days_in_month := p_from.days_in_month;
1905 p_to.days_in_year := p_from.days_in_year;
1906 p_to.balance_type_code := p_from.balance_type_code ;
1907 p_to.stream_interface_attribute01 := p_from.stream_interface_attribute01;
1908 p_to.stream_interface_attribute02 := p_from.stream_interface_attribute02;
1909 p_to.stream_interface_attribute03 := p_from.stream_interface_attribute03;
1910 p_to.stream_interface_attribute04 := p_from.stream_interface_attribute04;
1911 p_to.stream_interface_attribute05 := p_from.stream_interface_attribute05;
1912 p_to.stream_interface_attribute06 := p_from.stream_interface_attribute06;
1913 p_to.stream_interface_attribute07 := p_from.stream_interface_attribute07;
1914 p_to.stream_interface_attribute08 := p_from.stream_interface_attribute08;
1915 p_to.stream_interface_attribute09 := p_from.stream_interface_attribute09;
1916 p_to.stream_interface_attribute10 := p_from.stream_interface_attribute10;
1917 p_to.stream_interface_attribute11 := p_from.stream_interface_attribute11;
1918 p_to.stream_interface_attribute12 := p_from.stream_interface_attribute12;
1919 p_to.stream_interface_attribute13 := p_from.stream_interface_attribute13;
1920 p_to.stream_interface_attribute14 := p_from.stream_interface_attribute14;
1921 p_to.stream_interface_attribute15 := p_from.stream_interface_attribute15;
1922 p_to.stream_interface_attribute16 := p_from.stream_interface_attribute16;
1923 p_to.stream_interface_attribute17 := p_from.stream_interface_attribute17;
1924 p_to.stream_interface_attribute18 := p_from.stream_interface_attribute18;
1925 p_to.stream_interface_attribute19 := p_from.stream_interface_attribute19;
1926 p_to.stream_interface_attribute20 := p_from.stream_interface_attribute20;
1927 p_to.created_by := p_from.created_by;
1928 p_to.last_updated_by := p_from.last_updated_by;
1929 p_to.creation_date := p_from.creation_date;
1930 p_to.last_update_date := p_from.last_update_date;
1931 p_to.last_update_login := p_from.last_update_login;
1932 p_to.down_payment_amount:= p_from.down_payment_amount;
1933 p_to.orig_contract_line_id:= p_from.orig_contract_line_id;
1934 END migrate;
1935 PROCEDURE migrate (
1936 p_from IN sfe_rec_type,
1937 p_to IN OUT NOCOPY sfev_rec_type
1938 ) IS
1939 BEGIN
1940 p_to.id := p_from.id;
1941 p_to.sfe_type := p_from.sfe_type;
1942 p_to.date_start := p_from.date_start;
1943 p_to.date_paid := p_from.date_paid;
1944
1945 p_to.amount := p_from.amount;
1946 p_to.idc_accounting_flag := p_from.idc_accounting_flag;
1947 p_to.income_or_expense := p_from.income_or_expense;
1948 p_to.description := p_from.description;
1949 p_to.fee_index_number := p_from.fee_index_number;
1950 p_to.level_index_number := p_from.level_index_number;
1951 p_to.advance_or_arrears := p_from.advance_or_arrears;
1952 p_to.level_type := p_from.level_type;
1953 p_to.lock_level_step := p_from.lock_level_step;
1954 p_to.period := p_from.period;
1955 p_to.number_of_periods := p_from.number_of_periods;
1956 p_to.level_line_number := p_from.level_line_number;
1957 p_to.sif_id := p_from.sif_id;
1958 p_to.kle_id := p_from.kle_id;
1959 p_to.sil_id := p_from.sil_id;
1960 p_to.rate := p_from.rate;
1961 -- mvasudev, 05/13/2002
1962 p_to.query_level_yn := p_from.query_level_yn;
1963 p_to.structure := p_from.structure;
1964 p_to.days_in_period := p_from.days_in_period;
1965 --
1966 p_to.object_version_number := p_from.object_version_number;
1967 p_to.cash_effect_yn := p_from.cash_effect_yn;
1968 p_to.tax_effect_yn := p_from.tax_effect_yn;
1969 p_to.days_in_month := p_from.days_in_month;
1970 p_to.days_in_year := p_from.days_in_year;
1971 p_to.balance_type_code := p_from.balance_type_code ;
1972 p_to.stream_interface_attribute01 := p_from.stream_interface_attribute01;
1973 p_to.stream_interface_attribute02 := p_from.stream_interface_attribute02;
1974 p_to.stream_interface_attribute03 := p_from.stream_interface_attribute03;
1975 p_to.stream_interface_attribute04 := p_from.stream_interface_attribute04;
1976 p_to.stream_interface_attribute05 := p_from.stream_interface_attribute05;
1977 p_to.stream_interface_attribute06 := p_from.stream_interface_attribute06;
1978 p_to.stream_interface_attribute07 := p_from.stream_interface_attribute07;
1979 p_to.stream_interface_attribute08 := p_from.stream_interface_attribute08;
1980 p_to.stream_interface_attribute09 := p_from.stream_interface_attribute09;
1981 p_to.stream_interface_attribute10 := p_from.stream_interface_attribute10;
1982 p_to.stream_interface_attribute11 := p_from.stream_interface_attribute11;
1983 p_to.stream_interface_attribute12 := p_from.stream_interface_attribute12;
1984 p_to.stream_interface_attribute13 := p_from.stream_interface_attribute13;
1985 p_to.stream_interface_attribute14 := p_from.stream_interface_attribute14;
1986 p_to.stream_interface_attribute15 := p_from.stream_interface_attribute15;
1987 p_to.stream_interface_attribute16 := p_from.stream_interface_attribute16;
1988 p_to.stream_interface_attribute17 := p_from.stream_interface_attribute17;
1989 p_to.stream_interface_attribute18 := p_from.stream_interface_attribute18;
1990 p_to.stream_interface_attribute19 := p_from.stream_interface_attribute19;
1991 p_to.stream_interface_attribute20 := p_from.stream_interface_attribute20;
1992 p_to.created_by := p_from.created_by;
1993 p_to.last_updated_by := p_from.last_updated_by;
1994 p_to.creation_date := p_from.creation_date;
1995 p_to.last_update_date := p_from.last_update_date;
1996 p_to.last_update_login := p_from.last_update_login;
1997 p_to.down_payment_amount:= p_from.down_payment_amount;
1998 p_to.orig_contract_line_id:= p_from.orig_contract_line_id;
1999 END migrate;
2000
2001 ---------------------------------------------------------------------------
2002 -- PROCEDURE validate_row
2003 ---------------------------------------------------------------------------
2004 -------------------------------------
2005 -- validate_row for:OKL_SIF_FEES_V --
2006 -------------------------------------
2007 PROCEDURE validate_row(
2008 p_api_version IN NUMBER,
2009 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2010 x_return_status OUT NOCOPY VARCHAR2,
2011 x_msg_count OUT NOCOPY NUMBER,
2012 x_msg_data OUT NOCOPY VARCHAR2,
2013 p_sfev_rec IN sfev_rec_type) IS
2014
2015 l_api_version CONSTANT NUMBER := 1;
2016 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2017 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2018 l_sfev_rec sfev_rec_type := p_sfev_rec;
2019 l_sfe_rec sfe_rec_type;
2020 BEGIN
2021 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2022 G_PKG_NAME,
2023 p_init_msg_list,
2024 l_api_version,
2025 p_api_version,
2026 '_PVT',
2027 x_return_status);
2028 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2029 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2030 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2031 RAISE OKC_API.G_EXCEPTION_ERROR;
2032 END IF;
2033 --- Validate all non-missing attributes (Item Level Validation)
2034 l_return_status := Validate_Attributes(l_sfev_rec);
2035 --- If any errors happen abort API
2036 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2037 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2038 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2039 RAISE OKC_API.G_EXCEPTION_ERROR;
2040 END IF;
2041 l_return_status := Validate_Record(l_sfev_rec);
2042 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2043 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2044 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2045 RAISE OKC_API.G_EXCEPTION_ERROR;
2046 END IF;
2047 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2048 EXCEPTION
2049 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2050 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2051
2052 (
2053 l_api_name,
2054 G_PKG_NAME,
2055 'OKC_API.G_RET_STS_ERROR',
2056 x_msg_count,
2057 x_msg_data,
2058 '_PVT'
2059 );
2060 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2061 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2062 (
2063 l_api_name,
2064 G_PKG_NAME,
2065 'OKC_API.G_RET_STS_UNEXP_ERROR',
2066 x_msg_count,
2067 x_msg_data,
2068 '_PVT'
2069 );
2070 WHEN OTHERS THEN
2071 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2072 (
2073 l_api_name,
2074 G_PKG_NAME,
2075 'OTHERS',
2076 x_msg_count,
2077 x_msg_data,
2078 '_PVT'
2079 );
2080 END validate_row;
2081 ------------------------------------------
2082 -- PL/SQL TBL validate_row for:SFEV_TBL --
2083 ------------------------------------------
2084 PROCEDURE validate_row(
2085 p_api_version IN NUMBER,
2086 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2087 x_return_status OUT NOCOPY VARCHAR2,
2088 x_msg_count OUT NOCOPY NUMBER,
2089 x_msg_data OUT NOCOPY VARCHAR2,
2090 p_sfev_tbl IN sfev_tbl_type) IS
2091
2092 l_api_version CONSTANT NUMBER := 1;
2093 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
2094 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2095 i NUMBER := 0;
2096
2097 -- START change : akjain, 09/05/2001
2098 -- Adding OverAll Status Flag
2099 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2100 -- END change : akjain
2101
2102 BEGIN
2103 OKC_API.init_msg_list(p_init_msg_list);
2104 -- Make sure PL/SQL table has records in it before passing
2105 IF (p_sfev_tbl.COUNT > 0) THEN
2106 i := p_sfev_tbl.FIRST;
2107 LOOP
2108 validate_row (
2109 p_api_version => p_api_version,
2110 p_init_msg_list => OKC_API.G_FALSE,
2111 x_return_status => x_return_status,
2112 x_msg_count => x_msg_count,
2113 x_msg_data => x_msg_data,
2114 p_sfev_rec => p_sfev_tbl(i));
2115 -- START change : akjain, 09/05/2001
2116 -- store the highest degree of error
2117 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2118 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2119 l_overall_status := x_return_status;
2120 END IF;
2121 END IF;
2122 -- END change : akjain
2123 EXIT WHEN (i = p_sfev_tbl.LAST);
2124 i := p_sfev_tbl.NEXT(i);
2125 END LOOP;
2126 -- START change : akjain, 09/05/2001
2127 -- return overall status
2128 x_return_status := l_overall_status;
2129 -- END change : akjain
2130
2131 END IF;
2132 EXCEPTION
2133 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2134 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2135 (
2136 l_api_name,
2137 G_PKG_NAME,
2138 'OKC_API.G_RET_STS_ERROR',
2139 x_msg_count,
2140 x_msg_data,
2141 '_PVT'
2142 );
2143 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2144 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2145 (
2146 l_api_name,
2147 G_PKG_NAME,
2148 'OKC_API.G_RET_STS_UNEXP_ERROR',
2149 x_msg_count,
2150 x_msg_data,
2151
2152 '_PVT'
2153 );
2154 WHEN OTHERS THEN
2155 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2156 (
2157 l_api_name,
2158 G_PKG_NAME,
2159 'OTHERS',
2160 x_msg_count,
2161 x_msg_data,
2162 '_PVT'
2163 );
2164 END validate_row;
2165
2166 ---------------------------------------------------------------------------
2167 -- PROCEDURE insert_row
2168 ---------------------------------------------------------------------------
2169 ---------------------------------
2170 -- insert_row for:OKL_SIF_FEES --
2171 ---------------------------------
2172 PROCEDURE insert_row(
2173 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2174 x_return_status OUT NOCOPY VARCHAR2,
2175 x_msg_count OUT NOCOPY NUMBER,
2176 x_msg_data OUT NOCOPY VARCHAR2,
2177 p_sfe_rec IN sfe_rec_type,
2178 x_sfe_rec OUT NOCOPY sfe_rec_type) IS
2179
2180 l_api_version CONSTANT NUMBER := 1;
2181 l_api_name CONSTANT VARCHAR2(30) := 'FEES_insert_row';
2182 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2183 l_sfe_rec sfe_rec_type := p_sfe_rec;
2184 l_def_sfe_rec sfe_rec_type;
2185 -------------------------------------
2186 -- Set_Attributes for:OKL_SIF_FEES --
2187 -------------------------------------
2188 FUNCTION Set_Attributes (
2189 p_sfe_rec IN sfe_rec_type,
2190 x_sfe_rec OUT NOCOPY sfe_rec_type
2191 ) RETURN VARCHAR2 IS
2192 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2193 BEGIN
2194 x_sfe_rec := p_sfe_rec;
2195 RETURN(l_return_status);
2196 END Set_Attributes;
2197 BEGIN
2198 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2199 p_init_msg_list,
2200 '_PVT',
2201 x_return_status);
2202 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2203 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2204 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2205 RAISE OKC_API.G_EXCEPTION_ERROR;
2206 END IF;
2207 --- Setting item attributes
2208 l_return_status := Set_Attributes(
2209 p_sfe_rec, -- IN
2210 l_sfe_rec); -- OUT
2211 --- If any errors happen abort API
2212 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2213 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2214 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2215 RAISE OKC_API.G_EXCEPTION_ERROR;
2216 END IF;
2217
2218 INSERT INTO OKL_SIF_FEES(
2219 id,
2220 sfe_type,
2221 date_start,
2222 date_paid,
2223 amount,
2224 idc_accounting_flag,
2225 income_or_expense,
2226 description,
2227 fee_index_number,
2228 level_index_number,
2229 advance_or_arrears,
2230 level_type,
2231 lock_level_step,
2232 period,
2233 number_of_periods,
2234 level_line_number,
2235 sif_id,
2236 kle_id,
2237 sil_id,
2238 rate,
2239 -- mvasudev, 05/13/2002
2240 query_level_yn,
2241 structure,
2242 days_in_period,
2243 --
2244 object_version_number,
2245 cash_effect_yn,
2246 tax_effect_yn,
2247 days_in_month,
2248 days_in_year,
2249 balance_type_code ,
2250 stream_interface_attribute01,
2251 stream_interface_attribute02,
2252 stream_interface_attribute03,
2253 stream_interface_attribute04,
2254 stream_interface_attribute05,
2255 stream_interface_attribute06,
2256 stream_interface_attribute07,
2257 stream_interface_attribute08,
2258 stream_interface_attribute09,
2259 stream_interface_attribute10,
2260 stream_interface_attribute11,
2261 stream_interface_attribute12,
2262 stream_interface_attribute13,
2263 stream_interface_attribute14,
2264 stream_interface_attribute15,
2265 stream_interface_attribute16,
2266 stream_interface_attribute17,
2267 stream_interface_attribute18,
2268 stream_interface_attribute19,
2269 stream_interface_attribute20,
2270 created_by,
2271 last_updated_by,
2272 creation_date,
2273 last_update_date,
2274 last_update_login,
2275 down_payment_amount,
2276 orig_contract_line_id)
2277 VALUES (
2278 l_sfe_rec.id,
2279 l_sfe_rec.sfe_type,
2280 l_sfe_rec.date_start,
2281 l_sfe_rec.date_paid,
2282 l_sfe_rec.amount,
2283 l_sfe_rec.idc_accounting_flag,
2284 l_sfe_rec.income_or_expense,
2285 l_sfe_rec.description,
2286 l_sfe_rec.fee_index_number,
2287 l_sfe_rec.level_index_number,
2288 l_sfe_rec.advance_or_arrears,
2289 l_sfe_rec.level_type,
2290 l_sfe_rec.lock_level_step,
2291 l_sfe_rec.period,
2292 l_sfe_rec.number_of_periods,
2293 l_sfe_rec.level_line_number,
2294 l_sfe_rec.sif_id,
2295 l_sfe_rec.kle_id,
2296 l_sfe_rec.sil_id,
2297 l_sfe_rec.rate,
2298 -- mvasudev, 05/13/2002
2299 l_sfe_rec.QUERY_LEVEL_YN,
2300 l_sfe_rec.STRUCTURE,
2301 l_sfe_rec.DAYS_IN_PERIOD,
2302 --
2303 l_sfe_rec.object_version_number,
2304 l_sfe_rec.cash_effect_yn,
2305 l_sfe_rec.tax_effect_yn,
2306 l_sfe_rec.days_in_month,
2307 l_sfe_rec.days_in_year,
2308 l_sfe_rec.balance_type_code,
2309 l_sfe_rec.stream_interface_attribute01,
2310 l_sfe_rec.stream_interface_attribute02,
2311 l_sfe_rec.stream_interface_attribute03,
2312 l_sfe_rec.stream_interface_attribute04,
2313 l_sfe_rec.stream_interface_attribute05,
2314 l_sfe_rec.stream_interface_attribute06,
2315 l_sfe_rec.stream_interface_attribute07,
2316 l_sfe_rec.stream_interface_attribute08,
2317 l_sfe_rec.stream_interface_attribute09,
2318 l_sfe_rec.stream_interface_attribute10,
2319 l_sfe_rec.stream_interface_attribute11,
2320 l_sfe_rec.stream_interface_attribute12,
2321 l_sfe_rec.stream_interface_attribute13,
2322 l_sfe_rec.stream_interface_attribute14,
2323 l_sfe_rec.stream_interface_attribute15,
2324 l_sfe_rec.stream_interface_attribute16,
2325 l_sfe_rec.stream_interface_attribute17,
2326 l_sfe_rec.stream_interface_attribute18,
2327 l_sfe_rec.stream_interface_attribute19,
2328 l_sfe_rec.stream_interface_attribute20,
2329 l_sfe_rec.created_by,
2330 l_sfe_rec.last_updated_by,
2331 l_sfe_rec.creation_date,
2332 l_sfe_rec.last_update_date,
2333 l_sfe_rec.last_update_login,
2334 l_sfe_rec.down_payment_amount,
2335 l_sfe_rec.orig_contract_line_id);
2336 -- Set OUT values
2337 x_sfe_rec := l_sfe_rec;
2338 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2339 EXCEPTION
2340 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2341 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2342 (
2343 l_api_name,
2344 G_PKG_NAME,
2345 'OKC_API.G_RET_STS_ERROR',
2346 x_msg_count,
2347 x_msg_data,
2348 '_PVT'
2349 );
2350 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2351 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2352 (
2353 l_api_name,
2354 G_PKG_NAME,
2355 'OKC_API.G_RET_STS_UNEXP_ERROR',
2356 x_msg_count,
2357 x_msg_data,
2358 '_PVT'
2359 );
2360 WHEN OTHERS THEN
2361 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2362 (
2363 l_api_name,
2364 G_PKG_NAME,
2365
2366 'OTHERS',
2367 x_msg_count,
2368 x_msg_data,
2369 '_PVT'
2370 );
2371 END insert_row;
2372 -----------------------------------
2373 -- insert_row for:OKL_SIF_FEES_V --
2374 -----------------------------------
2375 PROCEDURE insert_row(
2376 p_api_version IN NUMBER,
2377 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2378 x_return_status OUT NOCOPY VARCHAR2,
2379 x_msg_count OUT NOCOPY NUMBER,
2380 x_msg_data OUT NOCOPY VARCHAR2,
2381 p_sfev_rec IN sfev_rec_type,
2382 x_sfev_rec OUT NOCOPY sfev_rec_type) IS
2383
2384 l_api_version CONSTANT NUMBER := 1;
2385 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
2386 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2387 l_sfev_rec sfev_rec_type;
2388 l_def_sfev_rec sfev_rec_type;
2389 l_sfe_rec sfe_rec_type;
2390 lx_sfe_rec sfe_rec_type;
2391 -------------------------------
2392 -- FUNCTION fill_who_columns --
2393 -------------------------------
2394 FUNCTION fill_who_columns (
2395 p_sfev_rec IN sfev_rec_type
2396 ) RETURN sfev_rec_type IS
2397 l_sfev_rec sfev_rec_type := p_sfev_rec;
2398 BEGIN
2399 l_sfev_rec.CREATION_DATE := SYSDATE;
2400 l_sfev_rec.CREATED_BY := FND_GLOBAL.USER_ID;
2401 l_sfev_rec.LAST_UPDATE_DATE := SYSDATE;
2402 l_sfev_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2403 l_sfev_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2404 RETURN(l_sfev_rec);
2405 END fill_who_columns;
2406 ---------------------------------------
2407 -- Set_Attributes for:OKL_SIF_FEES_V --
2408 ---------------------------------------
2409 FUNCTION Set_Attributes (
2410 p_sfev_rec IN sfev_rec_type,
2411 x_sfev_rec OUT NOCOPY sfev_rec_type
2412 ) RETURN VARCHAR2 IS
2413 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2414 BEGIN
2415 x_sfev_rec := p_sfev_rec;
2416 x_sfev_rec.OBJECT_VERSION_NUMBER := 1;
2417 RETURN(l_return_status);
2418 END Set_Attributes;
2419 BEGIN
2420 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2421 G_PKG_NAME,
2422 p_init_msg_list,
2423 l_api_version,
2424 p_api_version,
2425 '_PVT',
2426 x_return_status);
2427 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2428 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2429 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2430 RAISE OKC_API.G_EXCEPTION_ERROR;
2431 END IF;
2432 l_sfev_rec := null_out_defaults(p_sfev_rec);
2433 -- Set primary key value
2434 l_sfev_rec.ID := get_seq_id;
2435 --- Setting item attributes
2436 l_return_status := Set_Attributes(
2437 l_sfev_rec, -- IN
2438 l_def_sfev_rec); -- OUT
2439 --- If any errors happen abort API
2440 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2441 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2442 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2443 RAISE OKC_API.G_EXCEPTION_ERROR;
2444 END IF;
2445 l_def_sfev_rec := fill_who_columns(l_def_sfev_rec);
2446 --- Validate all non-missing attributes (Item Level Validation)
2447 l_return_status := Validate_Attributes(l_def_sfev_rec);
2448 --- If any errors happen abort API
2449 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2450 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2451 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2452 RAISE OKC_API.G_EXCEPTION_ERROR;
2453 END IF;
2454 l_return_status := Validate_Record(l_def_sfev_rec);
2455 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2456 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2457 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2458 RAISE OKC_API.G_EXCEPTION_ERROR;
2459 END IF;
2460 --------------------------------------
2461 -- Move VIEW record to "Child" records
2462 --------------------------------------
2463 migrate(l_def_sfev_rec, l_sfe_rec);
2464 --------------------------------------------
2465 -- Call the INSERT_ROW for each child record
2466 --------------------------------------------
2467 insert_row(
2468 p_init_msg_list,
2469 x_return_status,
2470 x_msg_count,
2471 x_msg_data,
2472 l_sfe_rec,
2473 lx_sfe_rec
2474 );
2475 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2476 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2477 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2478 RAISE OKC_API.G_EXCEPTION_ERROR;
2479 END IF;
2480 migrate(lx_sfe_rec, l_def_sfev_rec);
2481 -- Set OUT values
2482 x_sfev_rec := l_def_sfev_rec;
2483 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2484 EXCEPTION
2485 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2486 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2487 (
2488 l_api_name,
2489 G_PKG_NAME,
2490 'OKC_API.G_RET_STS_ERROR',
2491 x_msg_count,
2492 x_msg_data,
2493 '_PVT'
2494 );
2495 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2496 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2497 (
2498 l_api_name,
2499 G_PKG_NAME,
2500 'OKC_API.G_RET_STS_UNEXP_ERROR',
2501 x_msg_count,
2502 x_msg_data,
2503 '_PVT'
2504 );
2505 WHEN OTHERS THEN
2506 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2507 (
2508 l_api_name,
2509 G_PKG_NAME,
2510 'OTHERS',
2511 x_msg_count,
2512 x_msg_data,
2513 '_PVT'
2514 );
2515 END insert_row;
2516 ----------------------------------------
2517 -- PL/SQL TBL insert_row for:SFEV_TBL --
2518 ----------------------------------------
2519 PROCEDURE insert_row(
2520 p_api_version IN NUMBER,
2521 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2522 x_return_status OUT NOCOPY VARCHAR2,
2523 x_msg_count OUT NOCOPY NUMBER,
2524 x_msg_data OUT NOCOPY VARCHAR2,
2525 p_sfev_tbl IN sfev_tbl_type,
2526 x_sfev_tbl OUT NOCOPY sfev_tbl_type) IS
2527
2528 l_api_version CONSTANT NUMBER := 1;
2529 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2530 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2531 i NUMBER := 0;
2532 -- START change : akjain, 09/05/2001
2533 -- Adding OverAll Status Flag
2534 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2535 -- END change : akjain
2536
2537 BEGIN
2538 OKC_API.init_msg_list(p_init_msg_list);
2539 -- Make sure PL/SQL table has records in it before passing
2540 IF (p_sfev_tbl.COUNT > 0) THEN
2541 i := p_sfev_tbl.FIRST;
2542 LOOP
2543 insert_row (
2544 p_api_version => p_api_version,
2545 p_init_msg_list => OKC_API.G_FALSE,
2546 x_return_status => x_return_status,
2547 x_msg_count => x_msg_count,
2548 x_msg_data => x_msg_data,
2549 p_sfev_rec => p_sfev_tbl(i),
2550 x_sfev_rec => x_sfev_tbl(i));
2551 -- START change : akjain, 09/05/2001
2552 -- store the highest degree of error
2553 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2554 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2555 l_overall_status := x_return_status;
2556 END IF;
2557 END IF;
2558 -- END change : akjain
2559
2560 EXIT WHEN (i = p_sfev_tbl.LAST);
2561 i := p_sfev_tbl.NEXT(i);
2562 END LOOP;
2563 -- START change : akjain, 09/05/2001
2564 -- return overall status
2565 x_return_status := l_overall_status;
2566 -- END change : akjain
2567 END IF;
2568 EXCEPTION
2569 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2570 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2571 (
2572 l_api_name,
2573 G_PKG_NAME,
2574 'OKC_API.G_RET_STS_ERROR',
2575 x_msg_count,
2576 x_msg_data,
2577 '_PVT'
2578 );
2579 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2580 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2581 (
2582 l_api_name,
2583 G_PKG_NAME,
2584 'OKC_API.G_RET_STS_UNEXP_ERROR',
2585 x_msg_count,
2586 x_msg_data,
2587 '_PVT'
2588 );
2589 WHEN OTHERS THEN
2590 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2591 (
2592 l_api_name,
2593 G_PKG_NAME,
2594 'OTHERS',
2595 x_msg_count,
2596 x_msg_data,
2597 '_PVT'
2598 );
2599 END insert_row;
2600
2601 ---------------------------------------------------------------------------
2602 -- PROCEDURE lock_row
2603 ---------------------------------------------------------------------------
2604 -------------------------------
2605 -- lock_row for:OKL_SIF_FEES --
2606 -------------------------------
2607 PROCEDURE lock_row(
2608 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2609 x_return_status OUT NOCOPY VARCHAR2,
2610 x_msg_count OUT NOCOPY NUMBER,
2611 x_msg_data OUT NOCOPY VARCHAR2,
2612 p_sfe_rec IN sfe_rec_type) IS
2613
2614 E_Resource_Busy EXCEPTION;
2615 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2616 CURSOR lock_csr (p_sfe_rec IN sfe_rec_type) IS
2617 SELECT OBJECT_VERSION_NUMBER
2618 FROM OKL_SIF_FEES
2619 WHERE ID = p_sfe_rec.id
2620 AND OBJECT_VERSION_NUMBER = p_sfe_rec.object_version_number
2621 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2622
2623 CURSOR lchk_csr (p_sfe_rec IN sfe_rec_type) IS
2624 SELECT OBJECT_VERSION_NUMBER
2625 FROM OKL_SIF_FEES
2626 WHERE ID = p_sfe_rec.id;
2627 l_api_version CONSTANT NUMBER := 1;
2628 l_api_name CONSTANT VARCHAR2(30) := 'FEES_lock_row';
2629 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2630 l_object_version_number OKL_SIF_FEES.OBJECT_VERSION_NUMBER%TYPE;
2631 lc_object_version_number OKL_SIF_FEES.OBJECT_VERSION_NUMBER%TYPE;
2632 l_row_notfound BOOLEAN := FALSE;
2633 lc_row_notfound BOOLEAN := FALSE;
2634 BEGIN
2635 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2636 p_init_msg_list,
2637 '_PVT',
2638 x_return_status);
2639 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2640 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2641 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2642 RAISE OKC_API.G_EXCEPTION_ERROR;
2643 END IF;
2644 BEGIN
2645 OPEN lock_csr(p_sfe_rec);
2646 FETCH lock_csr INTO l_object_version_number;
2647 l_row_notfound := lock_csr%NOTFOUND;
2648 CLOSE lock_csr;
2649 EXCEPTION
2650 WHEN E_Resource_Busy THEN
2651 IF (lock_csr%ISOPEN) THEN
2652 CLOSE lock_csr;
2653 END IF;
2654 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2655 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2656 END;
2657
2658 IF ( l_row_notfound ) THEN
2659
2660 OPEN lchk_csr(p_sfe_rec);
2661 FETCH lchk_csr INTO lc_object_version_number;
2662 lc_row_notfound := lchk_csr%NOTFOUND;
2663 CLOSE lchk_csr;
2664 END IF;
2665 IF (lc_row_notfound) THEN
2666 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2667 RAISE OKC_API.G_EXCEPTION_ERROR;
2668 ELSIF lc_object_version_number > p_sfe_rec.object_version_number THEN
2669 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2670 RAISE OKC_API.G_EXCEPTION_ERROR;
2671 ELSIF lc_object_version_number <> p_sfe_rec.object_version_number THEN
2672 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2673 RAISE OKC_API.G_EXCEPTION_ERROR;
2674 ELSIF lc_object_version_number = -1 THEN
2675 OKC_API.set_message(G_OKC_APP,G_RECORD_LOGICALLY_DELETED);
2676 RAISE OKC_API.G_EXCEPTION_ERROR;
2677 END IF;
2678 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2679 EXCEPTION
2680 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2681 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2682 (
2683 l_api_name,
2684 G_PKG_NAME,
2685 'OKC_API.G_RET_STS_ERROR',
2686 x_msg_count,
2687 x_msg_data,
2688 '_PVT'
2689 );
2690 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2691 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2692 (
2693 l_api_name,
2694 G_PKG_NAME,
2695 'OKC_API.G_RET_STS_UNEXP_ERROR',
2696 x_msg_count,
2697 x_msg_data,
2698 '_PVT'
2699 );
2700 WHEN OTHERS THEN
2701 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2702 (
2703 l_api_name,
2704 G_PKG_NAME,
2705 'OTHERS',
2706 x_msg_count,
2707 x_msg_data,
2708 '_PVT'
2709 );
2710 END lock_row;
2711 ---------------------------------
2712 -- lock_row for:OKL_SIF_FEES_V --
2713 ---------------------------------
2714 PROCEDURE lock_row(
2715 p_api_version IN NUMBER,
2716 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2717 x_return_status OUT NOCOPY VARCHAR2,
2718 x_msg_count OUT NOCOPY NUMBER,
2719 x_msg_data OUT NOCOPY VARCHAR2,
2720 p_sfev_rec IN sfev_rec_type) IS
2721
2722 l_api_version CONSTANT NUMBER := 1;
2723 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2724 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2725 l_sfe_rec sfe_rec_type;
2726 BEGIN
2727 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2728 G_PKG_NAME,
2729 p_init_msg_list,
2730 l_api_version,
2731 p_api_version,
2732 '_PVT',
2733 x_return_status);
2734 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2735 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2736 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2737 RAISE OKC_API.G_EXCEPTION_ERROR;
2738 END IF;
2739 --------------------------------------
2740 -- Move VIEW record to "Child" records
2741 --------------------------------------
2742 migrate(p_sfev_rec, l_sfe_rec);
2743 --------------------------------------------
2744 -- Call the LOCK_ROW for each child record
2745 --------------------------------------------
2746 lock_row(
2747 p_init_msg_list,
2748 x_return_status,
2749 x_msg_count,
2750 x_msg_data,
2751 l_sfe_rec
2752 );
2753 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2754 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2755 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2756 RAISE OKC_API.G_EXCEPTION_ERROR;
2757 END IF;
2758 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2759
2760 EXCEPTION
2761 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2762 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2763 (
2764 l_api_name,
2765 G_PKG_NAME,
2766 'OKC_API.G_RET_STS_ERROR',
2767 x_msg_count,
2768 x_msg_data,
2769 '_PVT'
2770 );
2771 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2772 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2773 (
2774 l_api_name,
2775 G_PKG_NAME,
2776 'OKC_API.G_RET_STS_UNEXP_ERROR',
2777 x_msg_count,
2778 x_msg_data,
2779 '_PVT'
2780 );
2781 WHEN OTHERS THEN
2782 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2783 (
2784 l_api_name,
2785 G_PKG_NAME,
2786 'OTHERS',
2787 x_msg_count,
2788 x_msg_data,
2789 '_PVT'
2790 );
2791 END lock_row;
2792 --------------------------------------
2793 -- PL/SQL TBL lock_row for:SFEV_TBL --
2794 --------------------------------------
2795 PROCEDURE lock_row(
2796 p_api_version IN NUMBER,
2797 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2798 x_return_status OUT NOCOPY VARCHAR2,
2799 x_msg_count OUT NOCOPY NUMBER,
2800 x_msg_data OUT NOCOPY VARCHAR2,
2801 p_sfev_tbl IN sfev_tbl_type) IS
2802
2803 l_api_version CONSTANT NUMBER := 1;
2804 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2805 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2806 i NUMBER := 0;
2807 -- START change : akjain, 09/05/2001
2808 -- Adding OverAll Status Flag
2809 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
2810 -- END change : akjain
2811
2812 BEGIN
2813 OKC_API.init_msg_list(p_init_msg_list);
2814 -- Make sure PL/SQL table has records in it before passing
2815 IF (p_sfev_tbl.COUNT > 0) THEN
2816 i := p_sfev_tbl.FIRST;
2817 LOOP
2818 lock_row (
2819 p_api_version => p_api_version,
2820 p_init_msg_list => OKC_API.G_FALSE,
2821 x_return_status => x_return_status,
2822 x_msg_count => x_msg_count,
2823 x_msg_data => x_msg_data,
2824 p_sfev_rec => p_sfev_tbl(i));
2825 -- START change : akjain, 09/05/2001
2826 -- store the highest degree of error
2827 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
2828 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
2829 l_overall_status := x_return_status;
2830 END IF;
2831 END IF;
2832 -- END change : akjain
2833 EXIT WHEN (i = p_sfev_tbl.LAST);
2834 i := p_sfev_tbl.NEXT(i);
2835 END LOOP;
2836 -- START change : akjain, 09/05/2001
2837 -- return overall status
2838 x_return_status := l_overall_status;
2839 -- END change : akjain
2840 END IF;
2841 EXCEPTION
2842 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2843 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2844 (
2845 l_api_name,
2846 G_PKG_NAME,
2847 'OKC_API.G_RET_STS_ERROR',
2848 x_msg_count,
2849 x_msg_data,
2850 '_PVT'
2851 );
2852 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2853 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2854 (
2855 l_api_name,
2856 G_PKG_NAME,
2857 'OKC_API.G_RET_STS_UNEXP_ERROR',
2858 x_msg_count,
2859
2860 x_msg_data,
2861 '_PVT'
2862 );
2863 WHEN OTHERS THEN
2864 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2865 (
2866 l_api_name,
2867 G_PKG_NAME,
2868 'OTHERS',
2869 x_msg_count,
2870 x_msg_data,
2871 '_PVT'
2872 );
2873 END lock_row;
2874
2875 ---------------------------------------------------------------------------
2876 -- PROCEDURE update_row
2877 ---------------------------------------------------------------------------
2878 ---------------------------------
2879 -- update_row for:OKL_SIF_FEES --
2880 ---------------------------------
2881 PROCEDURE update_row(
2882 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2883 x_return_status OUT NOCOPY VARCHAR2,
2884 x_msg_count OUT NOCOPY NUMBER,
2885 x_msg_data OUT NOCOPY VARCHAR2,
2886 p_sfe_rec IN sfe_rec_type,
2887 x_sfe_rec OUT NOCOPY sfe_rec_type) IS
2888
2889 l_api_version CONSTANT NUMBER := 1;
2890 l_api_name CONSTANT VARCHAR2(30) := 'FEES_update_row';
2891 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2892 l_sfe_rec sfe_rec_type := p_sfe_rec;
2893 l_def_sfe_rec sfe_rec_type;
2894 l_row_notfound BOOLEAN := TRUE;
2895 ----------------------------------
2896 -- FUNCTION populate_new_record --
2897 ----------------------------------
2898 FUNCTION populate_new_record (
2899 p_sfe_rec IN sfe_rec_type,
2900 x_sfe_rec OUT NOCOPY sfe_rec_type
2901 ) RETURN VARCHAR2 IS
2902 l_sfe_rec sfe_rec_type;
2903 l_row_notfound BOOLEAN := TRUE;
2904 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2905 BEGIN
2906 x_sfe_rec := p_sfe_rec;
2907 -- Get current database values
2908 l_sfe_rec := get_rec(p_sfe_rec, l_row_notfound);
2909 IF (l_row_notfound) THEN
2910 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2911 END IF;
2912 IF (x_sfe_rec.id = OKC_API.G_MISS_NUM)
2913 THEN
2914 x_sfe_rec.id := l_sfe_rec.id;
2915 END IF;
2916 IF (x_sfe_rec.sfe_type = OKC_API.G_MISS_CHAR)
2917 THEN
2918 x_sfe_rec.sfe_type := l_sfe_rec.sfe_type;
2919 END IF;
2920 IF (x_sfe_rec.date_start = OKC_API.G_MISS_DATE)
2921 THEN
2922 x_sfe_rec.date_start := l_sfe_rec.date_start;
2923 END IF;
2924 IF (x_sfe_rec.date_paid = OKC_API.G_MISS_DATE)
2925 THEN
2926 x_sfe_rec.date_paid := l_sfe_rec.date_paid;
2927 END IF;
2928 IF (x_sfe_rec.amount = OKC_API.G_MISS_NUM)
2929 THEN
2930 x_sfe_rec.amount := l_sfe_rec.amount;
2931 END IF;
2932 IF (x_sfe_rec.idc_accounting_flag = OKC_API.G_MISS_CHAR)
2933 THEN
2934 x_sfe_rec.idc_accounting_flag := l_sfe_rec.idc_accounting_flag;
2935 END IF;
2936 IF (x_sfe_rec.income_or_expense = OKC_API.G_MISS_CHAR)
2937 THEN
2938 x_sfe_rec.income_or_expense := l_sfe_rec.income_or_expense;
2939 END IF;
2940 IF (x_sfe_rec.description = OKC_API.G_MISS_CHAR)
2941 THEN
2942 x_sfe_rec.description := l_sfe_rec.description;
2943 END IF;
2944 IF (x_sfe_rec.fee_index_number = OKC_API.G_MISS_NUM)
2945 THEN
2946 x_sfe_rec.fee_index_number := l_sfe_rec.fee_index_number;
2947 END IF;
2948 IF (x_sfe_rec.level_index_number = OKC_API.G_MISS_NUM)
2949 THEN
2950 x_sfe_rec.level_index_number := l_sfe_rec.level_index_number;
2951 END IF;
2952 IF (x_sfe_rec.advance_or_arrears = OKC_API.G_MISS_CHAR)
2953 THEN
2954 x_sfe_rec.advance_or_arrears := l_sfe_rec.advance_or_arrears;
2955 END IF;
2956
2957 IF (x_sfe_rec.cash_effect_yn = OKC_API.G_MISS_CHAR)
2958 THEN
2959 x_sfe_rec.cash_effect_yn := l_sfe_rec.cash_effect_yn;
2960 END IF;
2961
2962 IF (x_sfe_rec.tax_effect_yn = OKC_API.G_MISS_CHAR)
2963 THEN
2964 x_sfe_rec.tax_effect_yn := l_sfe_rec.tax_effect_yn;
2965 END IF;
2966
2967 IF (x_sfe_rec.days_in_month = OKC_API.G_MISS_CHAR)
2968 THEN
2969 x_sfe_rec.days_in_month := l_sfe_rec.days_in_month;
2970 END IF;
2971
2972 IF (x_sfe_rec.days_in_year = OKC_API.G_MISS_CHAR)
2973 THEN
2974 x_sfe_rec.days_in_year := l_sfe_rec.days_in_year;
2975 END IF;
2976
2977 IF (x_sfe_rec.balance_type_code = OKC_API.G_MISS_CHAR)
2978 THEN
2979 x_sfe_rec.balance_type_code := l_sfe_rec.balance_type_code;
2980 END IF;
2981
2982 IF (x_sfe_rec.level_type = OKC_API.G_MISS_CHAR)
2983 THEN
2984 x_sfe_rec.level_type := l_sfe_rec.level_type;
2985
2986 END IF;
2987 IF (x_sfe_rec.lock_level_step = OKC_API.G_MISS_CHAR)
2988 THEN
2989 x_sfe_rec.lock_level_step := l_sfe_rec.lock_level_step;
2990 END IF;
2991 IF (x_sfe_rec.period = OKC_API.G_MISS_CHAR)
2992 THEN
2993 x_sfe_rec.period := l_sfe_rec.period;
2994 END IF;
2995 IF (x_sfe_rec.number_of_periods = OKC_API.G_MISS_NUM)
2996 THEN
2997 x_sfe_rec.number_of_periods := l_sfe_rec.number_of_periods;
2998 END IF;
2999 IF (x_sfe_rec.level_line_number = OKC_API.G_MISS_NUM)
3000 THEN
3001 x_sfe_rec.level_line_number := l_sfe_rec.level_line_number;
3002 END IF;
3003 IF (x_sfe_rec.sif_id = OKC_API.G_MISS_NUM)
3004 THEN
3005 x_sfe_rec.sif_id := l_sfe_rec.sif_id;
3006 END IF;
3007 IF (x_sfe_rec.kle_id = OKC_API.G_MISS_NUM)
3008 THEN
3009 x_sfe_rec.kle_id := l_sfe_rec.kle_id;
3010 END IF;
3011 IF (x_sfe_rec.sil_id = OKC_API.G_MISS_NUM)
3012 THEN
3013 x_sfe_rec.sil_id := l_sfe_rec.sil_id;
3014 END IF;
3015 IF (x_sfe_rec.rate = OKC_API.G_MISS_NUM)
3016 THEN
3017 x_sfe_rec.rate := l_sfe_rec.rate;
3018 END IF;
3019 -- mvasudev, 05/13/2002
3020 IF (x_sfe_rec.query_level_yn = OKC_API.G_MISS_CHAR)
3021 THEN
3022 x_sfe_rec.query_level_yn := l_sfe_rec.query_level_yn;
3023 END IF;
3024 IF (x_sfe_rec.STRUCTURE = OKC_API.G_MISS_CHAR)
3025 THEN
3026 x_sfe_rec.STRUCTURE := l_sfe_rec.STRUCTURE;
3027 END IF;
3028 IF (x_sfe_rec.DAYS_IN_PERIOD = OKC_API.G_MISS_NUM)
3029 THEN
3030 x_sfe_rec.DAYS_IN_PERIOD := l_sfe_rec.DAYS_IN_PERIOD;
3031 END IF;
3032 --
3033 IF (x_sfe_rec.object_version_number = OKC_API.G_MISS_NUM)
3034 THEN
3035 x_sfe_rec.object_version_number := l_sfe_rec.object_version_number;
3036 END IF;
3037 IF (x_sfe_rec.stream_interface_attribute01 = OKC_API.G_MISS_CHAR)
3038 THEN
3039 x_sfe_rec.stream_interface_attribute01 := l_sfe_rec.stream_interface_attribute01;
3040 END IF;
3041 IF (x_sfe_rec.stream_interface_attribute02 = OKC_API.G_MISS_CHAR)
3042 THEN
3043 x_sfe_rec.stream_interface_attribute02 := l_sfe_rec.stream_interface_attribute02;
3044 END IF;
3045 IF (x_sfe_rec.stream_interface_attribute03 = OKC_API.G_MISS_CHAR)
3046 THEN
3047 x_sfe_rec.stream_interface_attribute03 := l_sfe_rec.stream_interface_attribute03;
3048 END IF;
3049 IF (x_sfe_rec.stream_interface_attribute04 = OKC_API.G_MISS_CHAR)
3050 THEN
3051 x_sfe_rec.stream_interface_attribute04 := l_sfe_rec.stream_interface_attribute04;
3052 END IF;
3053 IF (x_sfe_rec.stream_interface_attribute05 = OKC_API.G_MISS_CHAR)
3054 THEN
3055 x_sfe_rec.stream_interface_attribute05 := l_sfe_rec.stream_interface_attribute05;
3056 END IF;
3057 IF (x_sfe_rec.stream_interface_attribute06 = OKC_API.G_MISS_CHAR)
3058 THEN
3059 x_sfe_rec.stream_interface_attribute06 := l_sfe_rec.stream_interface_attribute06;
3060 END IF;
3061 IF (x_sfe_rec.stream_interface_attribute07 = OKC_API.G_MISS_CHAR)
3062 THEN
3063 x_sfe_rec.stream_interface_attribute07 := l_sfe_rec.stream_interface_attribute07;
3064 END IF;
3065 IF (x_sfe_rec.stream_interface_attribute08 = OKC_API.G_MISS_CHAR)
3066 THEN
3067 x_sfe_rec.stream_interface_attribute08 := l_sfe_rec.stream_interface_attribute08;
3068 END IF;
3069 IF (x_sfe_rec.stream_interface_attribute09 = OKC_API.G_MISS_CHAR)
3070 THEN
3071 x_sfe_rec.stream_interface_attribute09 := l_sfe_rec.stream_interface_attribute09;
3072 END IF;
3073 IF (x_sfe_rec.stream_interface_attribute10 = OKC_API.G_MISS_CHAR)
3074 THEN
3075 x_sfe_rec.stream_interface_attribute10 := l_sfe_rec.stream_interface_attribute10;
3076 END IF;
3077 IF (x_sfe_rec.stream_interface_attribute11 = OKC_API.G_MISS_CHAR)
3078 THEN
3079 x_sfe_rec.stream_interface_attribute11 := l_sfe_rec.stream_interface_attribute11;
3080 END IF;
3081 IF (x_sfe_rec.stream_interface_attribute12 = OKC_API.G_MISS_CHAR)
3082 THEN
3083 x_sfe_rec.stream_interface_attribute12 := l_sfe_rec.stream_interface_attribute12;
3084 END IF;
3085
3086 IF (x_sfe_rec.stream_interface_attribute13 = OKC_API.G_MISS_CHAR)
3087 THEN
3088 x_sfe_rec.stream_interface_attribute13 := l_sfe_rec.stream_interface_attribute13;
3089 END IF;
3090 IF (x_sfe_rec.stream_interface_attribute14 = OKC_API.G_MISS_CHAR)
3091 THEN
3092 x_sfe_rec.stream_interface_attribute14 := l_sfe_rec.stream_interface_attribute14;
3093 END IF;
3094 IF (x_sfe_rec.stream_interface_attribute15 = OKC_API.G_MISS_CHAR)
3095 THEN
3096 x_sfe_rec.stream_interface_attribute15 := l_sfe_rec.stream_interface_attribute15;
3097 END IF;
3098 IF (x_sfe_rec.stream_interface_attribute16 = OKC_API.G_MISS_CHAR)
3099 THEN
3100 x_sfe_rec.stream_interface_attribute16 := l_sfe_rec.stream_interface_attribute16;
3101 END IF;
3102 IF (x_sfe_rec.stream_interface_attribute17 = OKC_API.G_MISS_CHAR)
3103 THEN
3104 x_sfe_rec.stream_interface_attribute17 := l_sfe_rec.stream_interface_attribute17;
3105 END IF;
3106 IF (x_sfe_rec.stream_interface_attribute18 = OKC_API.G_MISS_CHAR)
3107 THEN
3108 x_sfe_rec.stream_interface_attribute18 := l_sfe_rec.stream_interface_attribute18;
3109 END IF;
3110 IF (x_sfe_rec.stream_interface_attribute19 = OKC_API.G_MISS_CHAR)
3111 THEN
3112 x_sfe_rec.stream_interface_attribute19 := l_sfe_rec.stream_interface_attribute19;
3113 END IF;
3114 IF (x_sfe_rec.stream_interface_attribute20 = OKC_API.G_MISS_CHAR)
3115 THEN
3116 x_sfe_rec.stream_interface_attribute20 := l_sfe_rec.stream_interface_attribute20;
3117 END IF;
3118 IF (x_sfe_rec.created_by = OKC_API.G_MISS_NUM)
3119 THEN
3120 x_sfe_rec.created_by := l_sfe_rec.created_by;
3121 END IF;
3122 IF (x_sfe_rec.last_updated_by = OKC_API.G_MISS_NUM)
3123 THEN
3124 x_sfe_rec.last_updated_by := l_sfe_rec.last_updated_by;
3125 END IF;
3126 IF (x_sfe_rec.creation_date = OKC_API.G_MISS_DATE)
3127 THEN
3128 x_sfe_rec.creation_date := l_sfe_rec.creation_date;
3129 END IF;
3130 IF (x_sfe_rec.last_update_date = OKC_API.G_MISS_DATE)
3131 THEN
3132 x_sfe_rec.last_update_date := l_sfe_rec.last_update_date;
3133 END IF;
3134 IF (x_sfe_rec.last_update_login = OKC_API.G_MISS_NUM)
3135 THEN
3136 x_sfe_rec.last_update_login := l_sfe_rec.last_update_login;
3137 END IF;
3138 IF (x_sfe_rec.down_payment_amount = OKC_API.G_MISS_NUM)
3139 THEN
3140 x_sfe_rec.down_payment_amount := l_sfe_rec.down_payment_amount;
3141 END IF;
3142
3143 IF (x_sfe_rec.orig_contract_line_id = OKC_API.G_MISS_NUM)
3144 THEN
3145 x_sfe_rec.orig_contract_line_id := l_sfe_rec.orig_contract_line_id;
3146 END IF;
3147
3148 RETURN(l_return_status);
3149 END populate_new_record;
3150 -------------------------------------
3151 -- Set_Attributes for:OKL_SIF_FEES --
3152 -------------------------------------
3153 FUNCTION Set_Attributes (
3154 p_sfe_rec IN sfe_rec_type,
3155 x_sfe_rec OUT NOCOPY sfe_rec_type
3156 ) RETURN VARCHAR2 IS
3157 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3158 BEGIN
3159 x_sfe_rec := p_sfe_rec;
3160 RETURN(l_return_status);
3161 END Set_Attributes;
3162 BEGIN
3163 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3164 p_init_msg_list,
3165 '_PVT',
3166 x_return_status);
3167 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3168 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3169 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3170 RAISE OKC_API.G_EXCEPTION_ERROR;
3171 END IF;
3172 --- Setting item attributes
3173 l_return_status := Set_Attributes(
3174 p_sfe_rec, -- IN
3175 l_sfe_rec); -- OUT
3176 --- If any errors happen abort API
3177 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3178 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3179 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3180 RAISE OKC_API.G_EXCEPTION_ERROR;
3181 END IF;
3182 l_return_status := populate_new_record(l_sfe_rec, l_def_sfe_rec);
3183 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3184 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3185 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3186 RAISE OKC_API.G_EXCEPTION_ERROR;
3187 END IF;
3188 UPDATE OKL_SIF_FEES
3189 SET SFE_TYPE = l_def_sfe_rec.sfe_type,
3190 DATE_START = l_def_sfe_rec.date_start,
3191 DATE_PAID = l_def_sfe_rec.date_paid,
3192 AMOUNT = l_def_sfe_rec.amount,
3193 IDC_ACCOUNTING_FLAG = l_def_sfe_rec.idc_accounting_flag,
3194 INCOME_OR_EXPENSE = l_def_sfe_rec.income_or_expense,
3195 DESCRIPTION = l_def_sfe_rec.description,
3196 FEE_INDEX_NUMBER = l_def_sfe_rec.fee_index_number,
3197 LEVEL_INDEX_NUMBER = l_def_sfe_rec.level_index_number,
3198 ADVANCE_OR_ARREARS = l_def_sfe_rec.advance_or_arrears,
3199 LEVEL_TYPE = l_def_sfe_rec.level_type,
3200 LOCK_LEVEL_STEP = l_def_sfe_rec.lock_level_step,
3201 -- mvasudev, 05/13/2002
3202 QUERY_LEVEL_YN = l_def_sfe_rec.query_level_yn,
3203 STRUCTURE = l_def_sfe_rec.STRUCTURE,
3204 DAYS_IN_PERIOD = l_def_sfe_rec.DAYS_IN_PERIOD,
3205 --
3206 PERIOD = l_def_sfe_rec.period,
3207 cash_effect_yn = l_def_sfe_rec.cash_effect_yn,
3208 tax_effect_yn = l_def_sfe_rec.tax_effect_yn,
3209 days_in_month = l_def_sfe_rec.days_in_month,
3210 days_in_year = l_def_sfe_rec.days_in_year,
3211 balance_type_code = l_def_sfe_rec.balance_type_code ,
3212 NUMBER_OF_PERIODS = l_def_sfe_rec.number_of_periods,
3213 LEVEL_LINE_NUMBER = l_def_sfe_rec.level_line_number,
3214 SIF_ID = l_def_sfe_rec.sif_id,
3215 KLE_ID = l_def_sfe_rec.kle_id,
3216 SIL_ID = l_def_sfe_rec.sil_id,
3217 RATE = l_def_sfe_rec.rate,
3218 OBJECT_VERSION_NUMBER = l_def_sfe_rec.object_version_number,
3219 STREAM_INTERFACE_ATTRIBUTE01 = l_def_sfe_rec.stream_interface_attribute01,
3220 STREAM_INTERFACE_ATTRIBUTE02 = l_def_sfe_rec.stream_interface_attribute02,
3221 STREAM_INTERFACE_ATTRIBUTE03 = l_def_sfe_rec.stream_interface_attribute03,
3222 STREAM_INTERFACE_ATTRIBUTE04 = l_def_sfe_rec.stream_interface_attribute04,
3223 STREAM_INTERFACE_ATTRIBUTE05 = l_def_sfe_rec.stream_interface_attribute05,
3224 STREAM_INTERFACE_ATTRIBUTE06 = l_def_sfe_rec.stream_interface_attribute06,
3225 STREAM_INTERFACE_ATTRIBUTE07 = l_def_sfe_rec.stream_interface_attribute07,
3226 STREAM_INTERFACE_ATTRIBUTE08 = l_def_sfe_rec.stream_interface_attribute08,
3227 STREAM_INTERFACE_ATTRIBUTE09 = l_def_sfe_rec.stream_interface_attribute09,
3228 STREAM_INTERFACE_ATTRIBUTE10 = l_def_sfe_rec.stream_interface_attribute10,
3229 STREAM_INTERFACE_ATTRIBUTE11 = l_def_sfe_rec.stream_interface_attribute11,
3230 STREAM_INTERFACE_ATTRIBUTE12 = l_def_sfe_rec.stream_interface_attribute12,
3231 STREAM_INTERFACE_ATTRIBUTE13 = l_def_sfe_rec.stream_interface_attribute13,
3232 STREAM_INTERFACE_ATTRIBUTE14 = l_def_sfe_rec.stream_interface_attribute14,
3233 STREAM_INTERFACE_ATTRIBUTE15 = l_def_sfe_rec.stream_interface_attribute15,
3234 STREAM_INTERFACE_ATTRIBUTE16 = l_def_sfe_rec.stream_interface_attribute16,
3235 STREAM_INTERFACE_ATTRIBUTE17 = l_def_sfe_rec.stream_interface_attribute17,
3236 STREAM_INTERFACE_ATTRIBUTE18 = l_def_sfe_rec.stream_interface_attribute18,
3237 STREAM_INTERFACE_ATTRIBUTE19 = l_def_sfe_rec.stream_interface_attribute19,
3238 STREAM_INTERFACE_ATTRIBUTE20 = l_def_sfe_rec.stream_interface_attribute20,
3239 CREATED_BY = l_def_sfe_rec.created_by,
3240 LAST_UPDATED_BY = l_def_sfe_rec.last_updated_by,
3241 CREATION_DATE = l_def_sfe_rec.creation_date,
3242 LAST_UPDATE_DATE = l_def_sfe_rec.last_update_date,
3243 LAST_UPDATE_LOGIN = l_def_sfe_rec.last_update_login,
3244 DOWN_PAYMENT_AMOUNT = l_def_sfe_rec.down_payment_amount,
3245 orig_contract_line_id = l_def_sfe_rec.orig_contract_line_id
3246 WHERE ID = l_def_sfe_rec.id;
3247
3248 x_sfe_rec := l_def_sfe_rec;
3249 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3250 EXCEPTION
3251 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3252 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3253 (
3254 l_api_name,
3255 G_PKG_NAME,
3256 'OKC_API.G_RET_STS_ERROR',
3257 x_msg_count,
3258 x_msg_data,
3259 '_PVT'
3260 );
3261 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3262 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3263 (
3264 l_api_name,
3265 G_PKG_NAME,
3266 'OKC_API.G_RET_STS_UNEXP_ERROR',
3267 x_msg_count,
3268 x_msg_data,
3269 '_PVT'
3270 );
3271 WHEN OTHERS THEN
3272 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3273 (
3274 l_api_name,
3275 G_PKG_NAME,
3276 'OTHERS',
3277 x_msg_count,
3278 x_msg_data,
3279 '_PVT'
3280 );
3281 END update_row;
3282 -----------------------------------
3283 -- update_row for:OKL_SIF_FEES_V --
3284 -----------------------------------
3285 PROCEDURE update_row(
3286 p_api_version IN NUMBER,
3287 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3288 x_return_status OUT NOCOPY VARCHAR2,
3289 x_msg_count OUT NOCOPY NUMBER,
3290 x_msg_data OUT NOCOPY VARCHAR2,
3291 p_sfev_rec IN sfev_rec_type,
3292 x_sfev_rec OUT NOCOPY sfev_rec_type) IS
3293
3294 l_api_version CONSTANT NUMBER := 1;
3295 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
3296 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3297 l_sfev_rec sfev_rec_type := p_sfev_rec;
3298 l_def_sfev_rec sfev_rec_type;
3299 l_sfe_rec sfe_rec_type;
3300 lx_sfe_rec sfe_rec_type;
3301
3302 -------------------------------
3303 -- FUNCTION fill_who_columns --
3304 -------------------------------
3305 FUNCTION fill_who_columns (
3306 p_sfev_rec IN sfev_rec_type
3307 ) RETURN sfev_rec_type IS
3308 l_sfev_rec sfev_rec_type := p_sfev_rec;
3309 BEGIN
3310 l_sfev_rec.LAST_UPDATE_DATE := SYSDATE;
3311 l_sfev_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3312 l_sfev_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3313 RETURN(l_sfev_rec);
3314 END fill_who_columns;
3315 ----------------------------------
3316 -- FUNCTION populate_new_record --
3317 ----------------------------------
3318 FUNCTION populate_new_record (
3319 p_sfev_rec IN sfev_rec_type,
3320 x_sfev_rec OUT NOCOPY sfev_rec_type
3321 ) RETURN VARCHAR2 IS
3322 l_sfev_rec sfev_rec_type;
3323 l_row_notfound BOOLEAN := TRUE;
3324 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3325 BEGIN
3326 x_sfev_rec := p_sfev_rec;
3327 -- Get current database values
3328 l_sfev_rec := get_rec(p_sfev_rec, l_row_notfound);
3329 IF (l_row_notfound) THEN
3330 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3331 END IF;
3332 IF (x_sfev_rec.id = OKC_API.G_MISS_NUM)
3333 THEN
3334 x_sfev_rec.id := l_sfev_rec.id;
3335 END IF;
3336 IF (x_sfev_rec.sfe_type = OKC_API.G_MISS_CHAR)
3337 THEN
3338 x_sfev_rec.sfe_type := l_sfev_rec.sfe_type;
3339 END IF;
3340 IF (x_sfev_rec.date_start = OKC_API.G_MISS_DATE)
3341 THEN
3342 x_sfev_rec.date_start := l_sfev_rec.date_start;
3343 END IF;
3344 IF (x_sfev_rec.date_paid = OKC_API.G_MISS_DATE)
3345 THEN
3346 x_sfev_rec.date_paid := l_sfev_rec.date_paid;
3347 END IF;
3348 IF (x_sfev_rec.amount = OKC_API.G_MISS_NUM)
3349 THEN
3350 x_sfev_rec.amount := l_sfev_rec.amount;
3351 END IF;
3352 IF (x_sfev_rec.idc_accounting_flag = OKC_API.G_MISS_CHAR)
3353 THEN
3354 x_sfev_rec.idc_accounting_flag := l_sfev_rec.idc_accounting_flag;
3355 END IF;
3356 IF (x_sfev_rec.income_or_expense = OKC_API.G_MISS_CHAR)
3357 THEN
3358 x_sfev_rec.income_or_expense := l_sfev_rec.income_or_expense;
3359 END IF;
3360 IF (x_sfev_rec.description = OKC_API.G_MISS_CHAR)
3361 THEN
3362 x_sfev_rec.description := l_sfev_rec.description;
3363 END IF;
3364 IF (x_sfev_rec.fee_index_number = OKC_API.G_MISS_NUM)
3365 THEN
3366 x_sfev_rec.fee_index_number := l_sfev_rec.fee_index_number;
3367 END IF;
3368 IF (x_sfev_rec.level_index_number = OKC_API.G_MISS_NUM)
3369 THEN
3370 x_sfev_rec.level_index_number := l_sfev_rec.level_index_number;
3371 END IF;
3372 IF (x_sfev_rec.advance_or_arrears = OKC_API.G_MISS_CHAR)
3373 THEN
3374 x_sfev_rec.advance_or_arrears := l_sfev_rec.advance_or_arrears;
3375 END IF;
3376
3377 IF (x_sfev_rec.cash_effect_yn = OKC_API.G_MISS_CHAR)
3378 THEN
3379 x_sfev_rec.cash_effect_yn := l_sfev_rec.cash_effect_yn;
3380 END IF;
3381
3382 IF (x_sfev_rec.tax_effect_yn = OKC_API.G_MISS_CHAR)
3383 THEN
3384 x_sfev_rec.tax_effect_yn := l_sfev_rec.tax_effect_yn;
3385 END IF;
3386
3387 IF (x_sfev_rec.days_in_month = OKC_API.G_MISS_CHAR)
3388 THEN
3389 x_sfev_rec.days_in_month := l_sfev_rec.days_in_month;
3390 END IF;
3391
3392 IF (x_sfev_rec.days_in_year = OKC_API.G_MISS_CHAR)
3393 THEN
3394 x_sfev_rec.days_in_year := l_sfev_rec.days_in_year;
3395 END IF;
3396
3397 IF (x_sfev_rec.balance_type_code = OKC_API.G_MISS_CHAR)
3398 THEN
3399 x_sfev_rec.balance_type_code := l_sfev_rec.balance_type_code;
3400 END IF;
3401
3402 IF (x_sfev_rec.level_type = OKC_API.G_MISS_CHAR)
3403 THEN
3404 x_sfev_rec.level_type := l_sfev_rec.level_type;
3405 END IF;
3406 IF (x_sfev_rec.lock_level_step = OKC_API.G_MISS_CHAR)
3407 THEN
3408 x_sfev_rec.lock_level_step := l_sfev_rec.lock_level_step;
3409 END IF;
3410 -- mvasudev, 05/13/2002
3411 IF (x_sfev_rec.query_level_yn = OKC_API.G_MISS_CHAR)
3412 THEN
3413 x_sfev_rec.query_level_yn := l_sfev_rec.query_level_yn;
3414 END IF;
3415 IF (x_sfev_rec.STRUCTURE = OKC_API.G_MISS_CHAR)
3416 THEN
3417 x_sfev_rec.STRUCTURE := l_sfev_rec.STRUCTURE;
3418 END IF;
3419 IF (x_sfev_rec.DAYS_IN_PERIOD = OKC_API.G_MISS_NUM)
3420 THEN
3421 x_sfev_rec.DAYS_IN_PERIOD := l_sfev_rec.DAYS_IN_PERIOD;
3422 END IF;
3423 --
3424 IF (x_sfev_rec.period = OKC_API.G_MISS_CHAR)
3425 THEN
3426 x_sfev_rec.period := l_sfev_rec.period;
3427
3428 END IF;
3429 IF (x_sfev_rec.number_of_periods = OKC_API.G_MISS_NUM)
3430 THEN
3431 x_sfev_rec.number_of_periods := l_sfev_rec.number_of_periods;
3432 END IF;
3433 IF (x_sfev_rec.level_line_number = OKC_API.G_MISS_NUM)
3434 THEN
3435 x_sfev_rec.level_line_number := l_sfev_rec.level_line_number;
3436 END IF;
3437 IF (x_sfev_rec.sif_id = OKC_API.G_MISS_NUM)
3438 THEN
3439 x_sfev_rec.sif_id := l_sfev_rec.sif_id;
3440 END IF;
3441 IF (x_sfev_rec.kle_id = OKC_API.G_MISS_NUM)
3442 THEN
3443 x_sfev_rec.kle_id := l_sfev_rec.kle_id;
3444 END IF;
3445 IF (x_sfev_rec.sil_id = OKC_API.G_MISS_NUM)
3446 THEN
3447 x_sfev_rec.sil_id := l_sfev_rec.sil_id;
3448 END IF;
3449 IF (x_sfev_rec.rate = OKC_API.G_MISS_NUM)
3450 THEN
3451 x_sfev_rec.rate := l_sfev_rec.rate;
3452 END IF;
3453 IF (x_sfev_rec.object_version_number = OKC_API.G_MISS_NUM)
3454 THEN
3455 x_sfev_rec.object_version_number := l_sfev_rec.object_version_number;
3456 END IF;
3457 IF (x_sfev_rec.stream_interface_attribute01 = OKC_API.G_MISS_CHAR)
3458 THEN
3459 x_sfev_rec.stream_interface_attribute01 := l_sfev_rec.stream_interface_attribute01;
3460 END IF;
3461 IF (x_sfev_rec.stream_interface_attribute02 = OKC_API.G_MISS_CHAR)
3462 THEN
3463 x_sfev_rec.stream_interface_attribute02 := l_sfev_rec.stream_interface_attribute02;
3464 END IF;
3465 IF (x_sfev_rec.stream_interface_attribute03 = OKC_API.G_MISS_CHAR)
3466 THEN
3467 x_sfev_rec.stream_interface_attribute03 := l_sfev_rec.stream_interface_attribute03;
3468 END IF;
3469 IF (x_sfev_rec.stream_interface_attribute04 = OKC_API.G_MISS_CHAR)
3470 THEN
3471 x_sfev_rec.stream_interface_attribute04 := l_sfev_rec.stream_interface_attribute04;
3472 END IF;
3473 IF (x_sfev_rec.stream_interface_attribute05 = OKC_API.G_MISS_CHAR)
3474 THEN
3475 x_sfev_rec.stream_interface_attribute05 := l_sfev_rec.stream_interface_attribute05;
3476 END IF;
3477 IF (x_sfev_rec.stream_interface_attribute06 = OKC_API.G_MISS_CHAR)
3478 THEN
3479 x_sfev_rec.stream_interface_attribute06 := l_sfev_rec.stream_interface_attribute06;
3480 END IF;
3481 IF (x_sfev_rec.stream_interface_attribute07 = OKC_API.G_MISS_CHAR)
3482 THEN
3483 x_sfev_rec.stream_interface_attribute07 := l_sfev_rec.stream_interface_attribute07;
3484 END IF;
3485 IF (x_sfev_rec.stream_interface_attribute08 = OKC_API.G_MISS_CHAR)
3486 THEN
3487 x_sfev_rec.stream_interface_attribute08 := l_sfev_rec.stream_interface_attribute08;
3488 END IF;
3489 IF (x_sfev_rec.stream_interface_attribute09 = OKC_API.G_MISS_CHAR)
3490 THEN
3491 x_sfev_rec.stream_interface_attribute09 := l_sfev_rec.stream_interface_attribute09;
3492 END IF;
3493 IF (x_sfev_rec.stream_interface_attribute10 = OKC_API.G_MISS_CHAR)
3494 THEN
3495 x_sfev_rec.stream_interface_attribute10 := l_sfev_rec.stream_interface_attribute10;
3496 END IF;
3497 IF (x_sfev_rec.stream_interface_attribute11 = OKC_API.G_MISS_CHAR)
3498 THEN
3499 x_sfev_rec.stream_interface_attribute11 := l_sfev_rec.stream_interface_attribute11;
3500 END IF;
3501 IF (x_sfev_rec.stream_interface_attribute12 = OKC_API.G_MISS_CHAR)
3502 THEN
3503 x_sfev_rec.stream_interface_attribute12 := l_sfev_rec.stream_interface_attribute12;
3504 END IF;
3505 IF (x_sfev_rec.stream_interface_attribute13 = OKC_API.G_MISS_CHAR)
3506 THEN
3507 x_sfev_rec.stream_interface_attribute13 := l_sfev_rec.stream_interface_attribute13;
3508 END IF;
3509 IF (x_sfev_rec.stream_interface_attribute14 = OKC_API.G_MISS_CHAR)
3510 THEN
3511 x_sfev_rec.stream_interface_attribute14 := l_sfev_rec.stream_interface_attribute14;
3512 END IF;
3513 IF (x_sfev_rec.stream_interface_attribute15 = OKC_API.G_MISS_CHAR)
3514 THEN
3515 x_sfev_rec.stream_interface_attribute15 := l_sfev_rec.stream_interface_attribute15;
3516 END IF;
3517 IF (x_sfev_rec.stream_interface_attribute16 = OKC_API.G_MISS_CHAR)
3518 THEN
3519 x_sfev_rec.stream_interface_attribute16 := l_sfev_rec.stream_interface_attribute16;
3520 END IF;
3521 IF (x_sfev_rec.stream_interface_attribute17 = OKC_API.G_MISS_CHAR)
3522 THEN
3523 x_sfev_rec.stream_interface_attribute17 := l_sfev_rec.stream_interface_attribute17;
3524 END IF;
3525 IF (x_sfev_rec.stream_interface_attribute18 = OKC_API.G_MISS_CHAR)
3526 THEN
3527
3528 x_sfev_rec.stream_interface_attribute18 := l_sfev_rec.stream_interface_attribute18;
3529 END IF;
3530 IF (x_sfev_rec.stream_interface_attribute19 = OKC_API.G_MISS_CHAR)
3531 THEN
3532 x_sfev_rec.stream_interface_attribute19 := l_sfev_rec.stream_interface_attribute19;
3533 END IF;
3534 IF (x_sfev_rec.stream_interface_attribute20 = OKC_API.G_MISS_CHAR)
3535 THEN
3536 x_sfev_rec.stream_interface_attribute20 := l_sfev_rec.stream_interface_attribute20;
3537 END IF;
3538 IF (x_sfev_rec.created_by = OKC_API.G_MISS_NUM)
3539 THEN
3540 x_sfev_rec.created_by := l_sfev_rec.created_by;
3541 END IF;
3542 IF (x_sfev_rec.last_updated_by = OKC_API.G_MISS_NUM)
3543 THEN
3544 x_sfev_rec.last_updated_by := l_sfev_rec.last_updated_by;
3545 END IF;
3546 IF (x_sfev_rec.creation_date = OKC_API.G_MISS_DATE)
3547 THEN
3548 x_sfev_rec.creation_date := l_sfev_rec.creation_date;
3549 END IF;
3550 IF (x_sfev_rec.last_update_date = OKC_API.G_MISS_DATE)
3551 THEN
3552 x_sfev_rec.last_update_date := l_sfev_rec.last_update_date;
3553 END IF;
3554 IF (x_sfev_rec.last_update_login = OKC_API.G_MISS_NUM)
3555 THEN
3556 x_sfev_rec.last_update_login := l_sfev_rec.last_update_login;
3557 END IF;
3558 IF (x_sfev_rec.down_payment_amount = OKC_API.G_MISS_NUM)
3559 THEN
3560 x_sfev_rec.down_payment_amount := l_sfev_rec.down_payment_amount;
3561 END IF;
3562
3563 IF (x_sfev_rec.orig_contract_line_id = OKC_API.G_MISS_NUM)
3564 THEN
3565 x_sfev_rec.orig_contract_line_id := l_sfev_rec.orig_contract_line_id;
3566 END IF;
3567
3568 RETURN(l_return_status);
3569 END populate_new_record;
3570 ---------------------------------------
3571 -- Set_Attributes for:OKL_SIF_FEES_V --
3572 ---------------------------------------
3573 FUNCTION Set_Attributes (
3574 p_sfev_rec IN sfev_rec_type,
3575 x_sfev_rec OUT NOCOPY sfev_rec_type
3576 ) RETURN VARCHAR2 IS
3577 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3578 BEGIN
3579 x_sfev_rec := p_sfev_rec;
3580 x_sfev_rec.OBJECT_VERSION_NUMBER := NVL(x_sfev_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3581 RETURN(l_return_status);
3582 END Set_Attributes;
3583 BEGIN
3584 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3585 G_PKG_NAME,
3586 p_init_msg_list,
3587 l_api_version,
3588 p_api_version,
3589 '_PVT',
3590 x_return_status);
3591 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3592 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3593 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3594 RAISE OKC_API.G_EXCEPTION_ERROR;
3595 END IF;
3596 --- Setting item attributes
3597 l_return_status := Set_Attributes(
3598 p_sfev_rec, -- IN
3599 l_sfev_rec); -- OUT
3600 --- If any errors happen abort API
3601 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3602 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3603 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3604 RAISE OKC_API.G_EXCEPTION_ERROR;
3605 END IF;
3606 l_return_status := populate_new_record(l_sfev_rec, l_def_sfev_rec);
3607 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3608 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3609 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3610 RAISE OKC_API.G_EXCEPTION_ERROR;
3611 END IF;
3612 l_def_sfev_rec := fill_who_columns(l_def_sfev_rec);
3613 --- Validate all non-missing attributes (Item Level Validation)
3614 l_return_status := Validate_Attributes(l_def_sfev_rec);
3615 --- If any errors happen abort API
3616 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3617 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3618 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3619 RAISE OKC_API.G_EXCEPTION_ERROR;
3620 END IF;
3621 l_return_status := Validate_Record(l_def_sfev_rec);
3622 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3623 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3624 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3625 RAISE OKC_API.G_EXCEPTION_ERROR;
3626 END IF;
3627
3628 --------------------------------------
3629 -- Move VIEW record to "Child" records
3630 --------------------------------------
3631 migrate(l_def_sfev_rec, l_sfe_rec);
3632 --------------------------------------------
3633 -- Call the UPDATE_ROW for each child record
3634 --------------------------------------------
3635 update_row(
3636 p_init_msg_list,
3637
3638 x_return_status,
3639 x_msg_count,
3640 x_msg_data,
3641 l_sfe_rec,
3642 lx_sfe_rec
3643 );
3644 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3645 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3646 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3647 RAISE OKC_API.G_EXCEPTION_ERROR;
3648 END IF;
3649 migrate(lx_sfe_rec, l_def_sfev_rec);
3650 x_sfev_rec := l_def_sfev_rec;
3651 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3652 EXCEPTION
3653 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3654 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3655 (
3656 l_api_name,
3657 G_PKG_NAME,
3658 'OKC_API.G_RET_STS_ERROR',
3659 x_msg_count,
3660 x_msg_data,
3661 '_PVT'
3662 );
3663 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3664 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3665 (
3666 l_api_name,
3667 G_PKG_NAME,
3668 'OKC_API.G_RET_STS_UNEXP_ERROR',
3669 x_msg_count,
3670 x_msg_data,
3671 '_PVT'
3672 );
3673 WHEN OTHERS THEN
3674 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3675 (
3676 l_api_name,
3677 G_PKG_NAME,
3678 'OTHERS',
3679 x_msg_count,
3680 x_msg_data,
3681 '_PVT'
3682 );
3683 END update_row;
3684 ----------------------------------------
3685 -- PL/SQL TBL update_row for:SFEV_TBL --
3686 ----------------------------------------
3687 PROCEDURE update_row(
3688 p_api_version IN NUMBER,
3689 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3690 x_return_status OUT NOCOPY VARCHAR2,
3691 x_msg_count OUT NOCOPY NUMBER,
3692 x_msg_data OUT NOCOPY VARCHAR2,
3693 p_sfev_tbl IN sfev_tbl_type,
3694 x_sfev_tbl OUT NOCOPY sfev_tbl_type) IS
3695
3696 l_api_version CONSTANT NUMBER := 1;
3697 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3698 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3699 i NUMBER := 0;
3700 -- START change : akjain, 09/05/2001
3701 -- Adding OverAll Status Flag
3702 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3703 -- END change : akjain
3704
3705 BEGIN
3706 OKC_API.init_msg_list(p_init_msg_list);
3707 -- Make sure PL/SQL table has records in it before passing
3708 IF (p_sfev_tbl.COUNT > 0) THEN
3709 i := p_sfev_tbl.FIRST;
3710 LOOP
3711 update_row (
3712 p_api_version => p_api_version,
3713 p_init_msg_list => OKC_API.G_FALSE,
3714 x_return_status => x_return_status,
3715 x_msg_count => x_msg_count,
3716 x_msg_data => x_msg_data,
3717 p_sfev_rec => p_sfev_tbl(i),
3718 x_sfev_rec => x_sfev_tbl(i));
3719 -- START change : akjain, 09/05/2001
3720 -- store the highest degree of error
3721 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
3722 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
3723 l_overall_status := x_return_status;
3724 END IF;
3725 END IF;
3726 -- END change : akjain
3727 EXIT WHEN (i = p_sfev_tbl.LAST);
3728 i := p_sfev_tbl.NEXT(i);
3729 END LOOP;
3730 -- START change : akjain, 09/05/2001
3731 -- return overall status
3732 x_return_status := l_overall_status;
3733 -- END change : akjain
3734 END IF;
3735 EXCEPTION
3736 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3737
3738 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3739 (
3740 l_api_name,
3741 G_PKG_NAME,
3742 'OKC_API.G_RET_STS_ERROR',
3743 x_msg_count,
3744 x_msg_data,
3745 '_PVT'
3746 );
3747 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3748 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3749 (
3750 l_api_name,
3751 G_PKG_NAME,
3752 'OKC_API.G_RET_STS_UNEXP_ERROR',
3753 x_msg_count,
3754 x_msg_data,
3755 '_PVT'
3756 );
3757 WHEN OTHERS THEN
3758 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3759 (
3760 l_api_name,
3761 G_PKG_NAME,
3762 'OTHERS',
3763 x_msg_count,
3764 x_msg_data,
3765 '_PVT'
3766 );
3767 END update_row;
3768
3769 ---------------------------------------------------------------------------
3770 -- PROCEDURE delete_row
3771 ---------------------------------------------------------------------------
3772 ---------------------------------
3773 -- delete_row for:OKL_SIF_FEES --
3774 ---------------------------------
3775 PROCEDURE delete_row(
3776 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3777 x_return_status OUT NOCOPY VARCHAR2,
3778 x_msg_count OUT NOCOPY NUMBER,
3779 x_msg_data OUT NOCOPY VARCHAR2,
3780 p_sfe_rec IN sfe_rec_type) IS
3781
3782 l_api_version CONSTANT NUMBER := 1;
3783 l_api_name CONSTANT VARCHAR2(30) := 'FEES_delete_row';
3784 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3785 l_sfe_rec sfe_rec_type:= p_sfe_rec;
3786 l_row_notfound BOOLEAN := TRUE;
3787 BEGIN
3788 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3789 p_init_msg_list,
3790 '_PVT',
3791 x_return_status);
3792 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3793 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3794 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3795 RAISE OKC_API.G_EXCEPTION_ERROR;
3796 END IF;
3797 DELETE FROM OKL_SIF_FEES
3798 WHERE ID = l_sfe_rec.id;
3799
3800 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3801 EXCEPTION
3802 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3803 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3804 (
3805 l_api_name,
3806 G_PKG_NAME,
3807 'OKC_API.G_RET_STS_ERROR',
3808 x_msg_count,
3809 x_msg_data,
3810 '_PVT'
3811 );
3812 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3813 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3814 (
3815 l_api_name,
3816 G_PKG_NAME,
3817 'OKC_API.G_RET_STS_UNEXP_ERROR',
3818 x_msg_count,
3819 x_msg_data,
3820 '_PVT'
3821 );
3822 WHEN OTHERS THEN
3823 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3824 (
3825 l_api_name,
3826 G_PKG_NAME,
3827 'OTHERS',
3828 x_msg_count,
3829 x_msg_data,
3830 '_PVT'
3831 );
3832 END delete_row;
3833 -----------------------------------
3834 -- delete_row for:OKL_SIF_FEES_V --
3835 -----------------------------------
3836 PROCEDURE delete_row(
3837
3838 p_api_version IN NUMBER,
3839 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3840 x_return_status OUT NOCOPY VARCHAR2,
3841 x_msg_count OUT NOCOPY NUMBER,
3842 x_msg_data OUT NOCOPY VARCHAR2,
3843 p_sfev_rec IN sfev_rec_type) IS
3844
3845 l_api_version CONSTANT NUMBER := 1;
3846 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3847 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3848 l_sfev_rec sfev_rec_type := p_sfev_rec;
3849 l_sfe_rec sfe_rec_type;
3850 BEGIN
3851 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3852 G_PKG_NAME,
3853 p_init_msg_list,
3854 l_api_version,
3855 p_api_version,
3856 '_PVT',
3857 x_return_status);
3858 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3859 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3860 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3861 RAISE OKC_API.G_EXCEPTION_ERROR;
3862 END IF;
3863 --------------------------------------
3864 -- Move VIEW record to "Child" records
3865 --------------------------------------
3866 migrate(l_sfev_rec, l_sfe_rec);
3867 --------------------------------------------
3868 -- Call the DELETE_ROW for each child record
3869 --------------------------------------------
3870 delete_row(
3871 p_init_msg_list,
3872 x_return_status,
3873 x_msg_count,
3874 x_msg_data,
3875 l_sfe_rec
3876 );
3877 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3878 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3879 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3880 RAISE OKC_API.G_EXCEPTION_ERROR;
3881 END IF;
3882 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3883 EXCEPTION
3884 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3885 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3886 (
3887 l_api_name,
3888 G_PKG_NAME,
3889 'OKC_API.G_RET_STS_ERROR',
3890 x_msg_count,
3891 x_msg_data,
3892 '_PVT'
3893 );
3894 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3895 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3896 (
3897 l_api_name,
3898 G_PKG_NAME,
3899 'OKC_API.G_RET_STS_UNEXP_ERROR',
3900 x_msg_count,
3901 x_msg_data,
3902 '_PVT'
3903 );
3904 WHEN OTHERS THEN
3905 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3906 (
3907 l_api_name,
3908 G_PKG_NAME,
3909 'OTHERS',
3910 x_msg_count,
3911 x_msg_data,
3912 '_PVT'
3913 );
3914 END delete_row;
3915 ----------------------------------------
3916 -- PL/SQL TBL delete_row for:SFEV_TBL --
3917 ----------------------------------------
3918 PROCEDURE delete_row(
3919 p_api_version IN NUMBER,
3920 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3921 x_return_status OUT NOCOPY VARCHAR2,
3922 x_msg_count OUT NOCOPY NUMBER,
3923 x_msg_data OUT NOCOPY VARCHAR2,
3924 p_sfev_tbl IN sfev_tbl_type) IS
3925
3926 l_api_version CONSTANT NUMBER := 1;
3927 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3928 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3929 i NUMBER := 0;
3930 -- START change : akjain, 09/05/2001
3931 -- Adding OverAll Status Flag
3932 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
3933 -- END change : akjain
3934
3935 BEGIN
3936 OKC_API.init_msg_list(p_init_msg_list);
3937
3938 -- Make sure PL/SQL table has records in it before passing
3939 IF (p_sfev_tbl.COUNT > 0) THEN
3940 i := p_sfev_tbl.FIRST;
3941 LOOP
3942 delete_row (
3943 p_api_version => p_api_version,
3944 p_init_msg_list => OKC_API.G_FALSE,
3945 x_return_status => x_return_status,
3946 x_msg_count => x_msg_count,
3947 x_msg_data => x_msg_data,
3948 p_sfev_rec => p_sfev_tbl(i));
3949 -- START change : akjain, 09/05/2001
3950 -- store the highest degree of error
3951 IF x_return_status <> Okc_Api.G_RET_STS_SUCCESS THEN
3952 IF l_overall_status <> Okc_Api.G_RET_STS_UNEXP_ERROR THEN
3953 l_overall_status := x_return_status;
3954 END IF;
3955 END IF;
3956 -- END change : akjain
3957 EXIT WHEN (i = p_sfev_tbl.LAST);
3958 i := p_sfev_tbl.NEXT(i);
3959 END LOOP;
3960 -- START change : akjain, 09/05/2001
3961 -- return overall status
3962 x_return_status := l_overall_status;
3963 -- END change : akjain
3964 END IF;
3965 EXCEPTION
3966 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3967 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3968 (
3969 l_api_name,
3970 G_PKG_NAME,
3971 'OKC_API.G_RET_STS_ERROR',
3972 x_msg_count,
3973 x_msg_data,
3974 '_PVT'
3975 );
3976 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3977 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3978 (
3979 l_api_name,
3980 G_PKG_NAME,
3981 'OKC_API.G_RET_STS_UNEXP_ERROR',
3982 x_msg_count,
3983 x_msg_data,
3984 '_PVT'
3985 );
3986 WHEN OTHERS THEN
3987 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3988 (
3989 l_api_name,
3990 G_PKG_NAME,
3991 'OTHERS',
3992 x_msg_count,
3993 x_msg_data,
3994 '_PVT'
3995 );
3996 END delete_row;
3997 END OKL_SFE_PVT;