[Home] [Help]
PACKAGE BODY: APPS.OKL_FEE_PVT
Source
1 PACKAGE BODY OKL_FEE_PVT AS
2 /* $Header: OKLSFEEB.pls 120.2 2007/08/08 21:11:59 rravikir noship $ */
3
4 -------------------------
5 -- PROCEDURE add_language
6 -------------------------
7 PROCEDURE add_language IS
8
9 BEGIN
10
11 DELETE FROM OKL_FEES_TL T
12 WHERE NOT EXISTS (SELECT NULL FROM OKL_FEES_B B WHERE B.ID =T.ID);
13
14 UPDATE OKL_FEES_TL T
15 SET (SHORT_DESCRIPTION,
16 DESCRIPTION,
17 COMMENTS) =
18 (SELECT
19 B.SHORT_DESCRIPTION,
20 B.DESCRIPTION,
21 B.COMMENTS
22 FROM
23 OKL_FEES_TL B
24 WHERE
25 B.ID = T.ID
26 AND B.LANGUAGE = T.SOURCE_LANG)
27 WHERE (T.ID, T.LANGUAGE) IN (SELECT
28 SUBT.ID,
29 SUBT.LANGUAGE
30 FROM
31 OKL_FEES_TL SUBB,
32 OKL_FEES_TL SUBT
33 WHERE
34 SUBB.ID = SUBT.ID
35 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
36 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
37 OR (SUBB.DESCRIPTION <> SUBT.DESCRIPTION)
38 OR (SUBB.COMMENTS <> SUBT.COMMENTS)
39 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
40 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
41 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
42 )
43 );
44
45 INSERT INTO OKL_FEES_TL (
46 ID,
47 LANGUAGE,
48 SOURCE_LANG,
49 SFWT_FLAG,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 SHORT_DESCRIPTION,
56 DESCRIPTION,
57 COMMENTS)
58 SELECT
59 B.ID,
60 L.LANGUAGE_CODE,
61 B.SOURCE_LANG,
62 B.SFWT_FLAG,
63 B.CREATED_BY,
64 B.CREATION_DATE,
65 B.LAST_UPDATED_BY,
66 B.LAST_UPDATE_DATE,
67 B.LAST_UPDATE_LOGIN,
68 B.SHORT_DESCRIPTION,
69 B.DESCRIPTION,
70 B.COMMENTS
71 FROM OKL_FEES_TL B, FND_LANGUAGES L
72 WHERE L.INSTALLED_FLAG IN ('I', 'B')
73 AND B.LANGUAGE = USERENV('LANG')
74 AND NOT EXISTS (
75 SELECT NULL
76 FROM OKL_FEES_TL T
77 WHERE T.ID = B.ID
78 AND T.LANGUAGE = L.LANGUAGE_CODE
79 );
80
81 END add_language;
82
83
84 -----------------------------
85 -- FUNCTION null_out_defaults
86 -----------------------------
87 FUNCTION null_out_defaults (p_feev_rec IN feev_rec_type) RETURN feev_rec_type IS
88
89 l_feev_rec feev_rec_type;
90
91 BEGIN
92
93 l_feev_rec := p_feev_rec;
94
95 -- Not applicable to ID and OBJECT_VERSION_NUMBER
96
97 IF l_feev_rec.attribute_category = FND_API.G_MISS_CHAR THEN
98 l_feev_rec.attribute_category := NULL;
99 END IF;
100 IF l_feev_rec.attribute1 = FND_API.G_MISS_CHAR THEN
101 l_feev_rec.attribute1 := NULL;
102 END IF;
103 IF l_feev_rec.attribute2 = FND_API.G_MISS_CHAR THEN
104 l_feev_rec.attribute2 := NULL;
105 END IF;
106 IF l_feev_rec.attribute3 = FND_API.G_MISS_CHAR THEN
107 l_feev_rec.attribute3 := NULL;
108 END IF;
109 IF l_feev_rec.attribute4 = FND_API.G_MISS_CHAR THEN
110 l_feev_rec.attribute4 := NULL;
111 END IF;
112 IF l_feev_rec.attribute5 = FND_API.G_MISS_CHAR THEN
113 l_feev_rec.attribute5 := NULL;
114 END IF;
115 IF l_feev_rec.attribute6 = FND_API.G_MISS_CHAR THEN
116 l_feev_rec.attribute6 := NULL;
117 END IF;
118 IF l_feev_rec.attribute7 = FND_API.G_MISS_CHAR THEN
119 l_feev_rec.attribute7 := NULL;
120 END IF;
121 IF l_feev_rec.attribute8 = FND_API.G_MISS_CHAR THEN
122 l_feev_rec.attribute8 := NULL;
123 END IF;
124 IF l_feev_rec.attribute9 = FND_API.G_MISS_CHAR THEN
125 l_feev_rec.attribute9 := NULL;
126 END IF;
127 IF l_feev_rec.attribute10 = FND_API.G_MISS_CHAR THEN
128 l_feev_rec.attribute10 := NULL;
129 END IF;
130 IF l_feev_rec.attribute11 = FND_API.G_MISS_CHAR THEN
131 l_feev_rec.attribute11 := NULL;
132 END IF;
133 IF l_feev_rec.attribute12 = FND_API.G_MISS_CHAR THEN
134 l_feev_rec.attribute12 := NULL;
135 END IF;
136 IF l_feev_rec.attribute13 = FND_API.G_MISS_CHAR THEN
137 l_feev_rec.attribute13 := NULL;
138 END IF;
139 IF l_feev_rec.attribute14 = FND_API.G_MISS_CHAR THEN
140 l_feev_rec.attribute14 := NULL;
141 END IF;
142 IF l_feev_rec.attribute15 = FND_API.G_MISS_CHAR THEN
143 l_feev_rec.attribute15 := NULL;
144 END IF;
145 IF l_feev_rec.parent_object_code = FND_API.G_MISS_CHAR THEN
146 l_feev_rec.parent_object_code := NULL;
147 END IF;
148 IF l_feev_rec.parent_object_id = FND_API.G_MISS_NUM THEN
149 l_feev_rec.parent_object_id := NULL;
150 END IF;
151 IF l_feev_rec.stream_type_id = FND_API.G_MISS_NUM THEN
152 l_feev_rec.stream_type_id := NULL;
153 END IF;
154 IF l_feev_rec.fee_type = FND_API.G_MISS_CHAR THEN
155 l_feev_rec.fee_type := NULL;
156 END IF;
157 IF l_feev_rec.structured_pricing = FND_API.G_MISS_CHAR THEN
158 l_feev_rec.structured_pricing := NULL;
159 END IF;
160 IF l_feev_rec.rate_template_id = FND_API.G_MISS_NUM THEN
161 l_feev_rec.rate_template_id := NULL;
162 END IF;
163 IF l_feev_rec.rate_card_id = FND_API.G_MISS_NUM THEN
164 l_feev_rec.rate_card_id := NULL;
165 END IF;
166 IF l_feev_rec.lease_rate_factor = FND_API.G_MISS_NUM THEN
167 l_feev_rec.lease_rate_factor := NULL;
168 END IF;
169 IF l_feev_rec.target_arrears = FND_API.G_MISS_CHAR THEN
170 l_feev_rec.target_arrears := NULL;
171 END IF;
172 IF l_feev_rec.effective_from = FND_API.G_MISS_DATE THEN
173 l_feev_rec.effective_from := NULL;
174 END IF;
175 IF l_feev_rec.effective_to = FND_API.G_MISS_DATE THEN
176 l_feev_rec.effective_to := NULL;
177 END IF;
178 IF l_feev_rec.supplier_id = FND_API.G_MISS_NUM THEN
179 l_feev_rec.supplier_id := NULL;
180 END IF;
181 IF l_feev_rec.rollover_quote_id = FND_API.G_MISS_NUM THEN
182 l_feev_rec.rollover_quote_id := NULL;
183 END IF;
184 IF l_feev_rec.initial_direct_cost = FND_API.G_MISS_NUM THEN
185 l_feev_rec.initial_direct_cost := NULL;
186 END IF;
187 IF l_feev_rec.fee_amount = FND_API.G_MISS_NUM THEN
188 l_feev_rec.fee_amount := NULL;
189 END IF;
190 IF l_feev_rec.target_amount = FND_API.G_MISS_NUM THEN
191 l_feev_rec.target_amount := NULL;
192 END IF;
193 IF l_feev_rec.target_frequency = FND_API.G_MISS_CHAR THEN
194 l_feev_rec.target_frequency := NULL;
195 END IF;
196 IF l_feev_rec.short_description = FND_API.G_MISS_CHAR THEN
197 l_feev_rec.short_description := NULL;
198 END IF;
199 IF l_feev_rec.description = FND_API.G_MISS_CHAR THEN
200 l_feev_rec.description := NULL;
201 END IF;
202 IF l_feev_rec.comments = FND_API.G_MISS_CHAR THEN
203 l_feev_rec.comments := NULL;
204 END IF;
205 IF l_feev_rec.payment_type_id = FND_API.G_MISS_NUM THEN
206 l_feev_rec.payment_type_id := NULL;
207 END IF;
208 IF l_feev_rec.fee_purpose_code = FND_API.G_MISS_CHAR THEN
209 l_feev_rec.fee_purpose_code := NULL;
210 END IF;
211 RETURN l_feev_rec;
212
213 END null_out_defaults;
214
215
216 -------------------
217 -- FUNCTION get_rec
218 -------------------
219 FUNCTION get_rec (p_id IN NUMBER
220 ,x_return_status OUT NOCOPY VARCHAR2) RETURN feev_rec_type IS
221
222 l_feev_rec feev_rec_type;
223 l_prog_name VARCHAR2(61);
224
225 BEGIN
226
227 l_prog_name := G_PKG_NAME||'.get_rec';
228
229 SELECT
230 id
231 ,object_version_number
232 ,attribute_category
233 ,attribute1
234 ,attribute2
235 ,attribute3
236 ,attribute4
237 ,attribute5
238 ,attribute6
239 ,attribute7
240 ,attribute8
241 ,attribute9
242 ,attribute10
243 ,attribute11
244 ,attribute12
245 ,attribute13
246 ,attribute14
247 ,attribute15
248 ,parent_object_code
249 ,parent_object_id
250 ,stream_type_id
251 ,fee_type
252 ,structured_pricing
253 ,rate_template_id
254 ,rate_card_id
255 ,lease_rate_factor
256 ,target_arrears
257 ,effective_from
258 ,effective_to
259 ,supplier_id
260 ,rollover_quote_id
261 ,initial_direct_cost
262 ,fee_amount
263 ,target_amount
264 ,target_frequency
265 ,short_description
266 ,description
267 ,comments
268 ,payment_type_id
269 ,fee_purpose_code
270 INTO
271 l_feev_rec.id
272 ,l_feev_rec.object_version_number
273 ,l_feev_rec.attribute_category
274 ,l_feev_rec.attribute1
275 ,l_feev_rec.attribute2
276 ,l_feev_rec.attribute3
277 ,l_feev_rec.attribute4
278 ,l_feev_rec.attribute5
279 ,l_feev_rec.attribute6
280 ,l_feev_rec.attribute7
281 ,l_feev_rec.attribute8
282 ,l_feev_rec.attribute9
283 ,l_feev_rec.attribute10
284 ,l_feev_rec.attribute11
285 ,l_feev_rec.attribute12
286 ,l_feev_rec.attribute13
287 ,l_feev_rec.attribute14
288 ,l_feev_rec.attribute15
289 ,l_feev_rec.parent_object_code
290 ,l_feev_rec.parent_object_id
291 ,l_feev_rec.stream_type_id
292 ,l_feev_rec.fee_type
293 ,l_feev_rec.structured_pricing
294 ,l_feev_rec.rate_template_id
295 ,l_feev_rec.rate_card_id
296 ,l_feev_rec.lease_rate_factor
297 ,l_feev_rec.target_arrears
298 ,l_feev_rec.effective_from
299 ,l_feev_rec.effective_to
300 ,l_feev_rec.supplier_id
301 ,l_feev_rec.rollover_quote_id
302 ,l_feev_rec.initial_direct_cost
303 ,l_feev_rec.fee_amount
304 ,l_feev_rec.target_amount
305 ,l_feev_rec.target_frequency
306 ,l_feev_rec.short_description
307 ,l_feev_rec.description
308 ,l_feev_rec.comments
309 ,l_feev_rec.payment_type_id
310 ,l_feev_rec.fee_purpose_code
311 FROM OKL_FEES_V
312 WHERE id = p_id;
313
314 x_return_status := G_RET_STS_SUCCESS;
315 RETURN l_feev_rec;
316
317 EXCEPTION
318
319 WHEN OTHERS THEN
320
321 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
322 p_msg_name => G_DB_ERROR,
323 p_token1 => G_PROG_NAME_TOKEN,
324 p_token1_value => l_prog_name,
325 p_token2 => G_SQLCODE_TOKEN,
326 p_token2_value => sqlcode,
327 p_token3 => G_SQLERRM_TOKEN,
328 p_token3_value => sqlerrm);
329
330 x_return_status := G_RET_STS_UNEXP_ERROR;
331
332 END get_rec;
333
334
335 ------------------------
336 -- PROCEDURE validate_id
337 ------------------------
338 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2, p_id IN NUMBER) IS
339 BEGIN
340 IF p_id IS NULL THEN
341 OKL_API.set_message(p_app_name => G_APP_NAME,
342 p_msg_name => G_COL_ERROR,
343 p_token1 => G_COL_NAME_TOKEN,
344 p_token1_value => 'id',
345 p_token2 => G_PKG_NAME_TOKEN,
346 p_token2_value => G_PKG_NAME);
347 RAISE OKL_API.G_EXCEPTION_ERROR;
348 END IF;
349 x_return_status := G_RET_STS_SUCCESS;
350 END validate_id;
351
352
353 -------------------------------------------
354 -- PROCEDURE validate_object_version_number
355 -------------------------------------------
356 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2, p_object_version_number IN NUMBER) IS
357 BEGIN
358 IF p_object_version_number IS NULL THEN
359 OKL_API.set_message(p_app_name => G_APP_NAME,
360 p_msg_name => G_COL_ERROR,
361 p_token1 => G_COL_NAME_TOKEN,
362 p_token1_value => 'object_version_number',
363 p_token2 => G_PKG_NAME_TOKEN,
364 p_token2_value => G_PKG_NAME);
365 RAISE OKL_API.G_EXCEPTION_ERROR;
366 END IF;
367 x_return_status := G_RET_STS_SUCCESS;
368 END validate_object_version_number;
369
370
371 -----------------------------------------
372 -- PROCEDURE validate_parent_object_code
373 -----------------------------------------
374 PROCEDURE validate_parent_object_code (x_return_status OUT NOCOPY VARCHAR2, p_parent_object_code IN VARCHAR2) IS
375 BEGIN
376 IF p_parent_object_code IS NULL THEN
377 OKL_API.set_message(p_app_name => G_APP_NAME,
378 p_msg_name => G_COL_ERROR,
379 p_token1 => G_COL_NAME_TOKEN,
380 p_token1_value => 'parent_object_code',
381 p_token2 => G_PKG_NAME_TOKEN,
382 p_token2_value => G_PKG_NAME);
383 RAISE OKL_API.G_EXCEPTION_ERROR;
384 END IF;
385 x_return_status := G_RET_STS_SUCCESS;
386 END validate_parent_object_code;
387
388
389 -----------------------------------------
390 -- PROCEDURE validate_parent_object_id
391 -----------------------------------------
392 PROCEDURE validate_parent_object_id (x_return_status OUT NOCOPY VARCHAR2, p_parent_object_id IN NUMBER) IS
393 BEGIN
394 IF p_parent_object_id IS NULL THEN
395 OKL_API.set_message(p_app_name => G_APP_NAME,
396 p_msg_name => G_COL_ERROR,
397 p_token1 => G_COL_NAME_TOKEN,
398 p_token1_value => 'parent_object_id',
399 p_token2 => G_PKG_NAME_TOKEN,
400 p_token2_value => G_PKG_NAME);
401 RAISE OKL_API.G_EXCEPTION_ERROR;
402 END IF;
403 x_return_status := G_RET_STS_SUCCESS;
404 END validate_parent_object_id;
405
406
407 -----------------------------------------
408 -- PROCEDURE validate_stream_type_id
409 -----------------------------------------
410 PROCEDURE validate_stream_type_id (x_return_status OUT NOCOPY VARCHAR2, p_stream_type_id IN NUMBER) IS
411 BEGIN
412 IF p_stream_type_id IS NULL THEN
413 OKL_API.set_message(p_app_name => G_APP_NAME,
414 p_msg_name => G_COL_ERROR,
415 p_token1 => G_COL_NAME_TOKEN,
416 p_token1_value => 'stream_type_id',
417 p_token2 => G_PKG_NAME_TOKEN,
418 p_token2_value => G_PKG_NAME);
419 RAISE OKL_API.G_EXCEPTION_ERROR;
420 END IF;
421 x_return_status := G_RET_STS_SUCCESS;
422 END validate_stream_type_id;
423
424
425 -----------------------------------------
426 -- PROCEDURE validate_fee_type
427 -----------------------------------------
428 PROCEDURE validate_fee_type (x_return_status OUT NOCOPY VARCHAR2, p_fee_type IN VARCHAR2) IS
429 BEGIN
430 IF p_fee_type IS NULL THEN
431 OKL_API.set_message(p_app_name => G_APP_NAME,
432 p_msg_name => G_COL_ERROR,
433 p_token1 => G_COL_NAME_TOKEN,
434 p_token1_value => 'fee_type',
435 p_token2 => G_PKG_NAME_TOKEN,
436 p_token2_value => G_PKG_NAME);
437 RAISE OKL_API.G_EXCEPTION_ERROR;
438 END IF;
439 x_return_status := G_RET_STS_SUCCESS;
440 END validate_fee_type;
441
442
443 -------------------------------
444 -- FUNCTION validate_attributes
445 -------------------------------
446 FUNCTION validate_attributes (p_feev_rec IN feev_rec_type) RETURN VARCHAR2 IS
447
448 l_return_status VARCHAR2(1);
449
450 BEGIN
451
452 validate_id (l_return_status, p_feev_rec.id);
453 validate_object_version_number (l_return_status, p_feev_rec.object_version_number);
454 validate_parent_object_code (l_return_status, p_feev_rec.parent_object_code);
455 validate_parent_object_id (l_return_status, p_feev_rec.parent_object_id);
456 validate_stream_type_id (l_return_status, p_feev_rec.stream_type_id);
457 validate_fee_type (l_return_status, p_feev_rec.fee_type);
458
459 RETURN l_return_status;
460
461 END validate_attributes;
462
463 ----------------------------
464 -- PROCEDURE validate_record
465 ----------------------------
466 FUNCTION validate_record (p_feev_rec IN feev_rec_type) RETURN VARCHAR2 IS
467 l_return_status VARCHAR2(1);
468 BEGIN
469 RETURN G_RET_STS_SUCCESS;
470 END validate_record;
471
472
473 -----------------------------
474 -- PROECDURE migrate (V -> B)
475 -----------------------------
476 PROCEDURE migrate (p_from IN feev_rec_type, p_to IN OUT NOCOPY fee_rec_type) IS
477
478 BEGIN
479
480 p_to.id := p_from.id;
481 p_to.object_version_number := p_from.object_version_number;
482 p_to.attribute_category := p_from.attribute_category;
483 p_to.attribute1 := p_from.attribute1;
484 p_to.attribute2 := p_from.attribute2;
485 p_to.attribute3 := p_from.attribute3;
486 p_to.attribute4 := p_from.attribute4;
487 p_to.attribute5 := p_from.attribute5;
488 p_to.attribute6 := p_from.attribute6;
489 p_to.attribute7 := p_from.attribute7;
490 p_to.attribute8 := p_from.attribute8;
491 p_to.attribute9 := p_from.attribute9;
492 p_to.attribute10 := p_from.attribute10;
493 p_to.attribute11 := p_from.attribute11;
494 p_to.attribute12 := p_from.attribute12;
495 p_to.attribute13 := p_from.attribute13;
496 p_to.attribute14 := p_from.attribute14;
497 p_to.attribute15 := p_from.attribute15;
498 p_to.parent_object_code := p_from.parent_object_code;
499 p_to.parent_object_id := p_from.parent_object_id;
500 p_to.stream_type_id := p_from.stream_type_id;
501 p_to.fee_type := p_from.fee_type;
502 p_to.structured_pricing := p_from.structured_pricing;
503 p_to.rate_template_id := p_from.rate_template_id;
504 p_to.rate_card_id := p_from.rate_card_id;
505 p_to.lease_rate_factor := p_from.lease_rate_factor;
506 p_to.target_arrears := p_from.target_arrears;
507 p_to.effective_from := p_from.effective_from;
508 p_to.effective_to := p_from.effective_to;
509 p_to.supplier_id := p_from.supplier_id;
510 p_to.rollover_quote_id := p_from.rollover_quote_id;
511 p_to.initial_direct_cost := p_from.initial_direct_cost;
512 p_to.fee_amount := p_from.fee_amount;
513 p_to.target_amount := p_from.target_amount;
514 p_to.target_frequency := p_from.target_frequency;
515 p_to.payment_type_id := p_from.payment_type_id;
516 p_to.fee_purpose_code := p_from.fee_purpose_code;
517 END migrate;
518
519
520 -----------------------------
521 -- PROCEDURE migrate (V -> TL)
522 -----------------------------
523 PROCEDURE migrate (p_from IN feev_rec_type, p_to IN OUT NOCOPY feetl_rec_type) IS
524 BEGIN
525 p_to.id := p_from.id;
526 p_to.short_description := p_from.short_description;
527 p_to.description := p_from.description;
528 p_to.comments := p_from.comments;
529 END migrate;
530
531
532 ---------------------------
533 -- PROCEDURE insert_row (B)
534 ---------------------------
535 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_fee_rec IN fee_rec_type) IS
536
537 l_prog_name VARCHAR2(61);
538
539 BEGIN
540
541 l_prog_name := G_PKG_NAME||'.insert_row (B)';
542
543 INSERT INTO okl_fees_b (
544 id
545 ,object_version_number
546 ,attribute_category
547 ,attribute1
548 ,attribute2
549 ,attribute3
550 ,attribute4
551 ,attribute5
552 ,attribute6
553 ,attribute7
554 ,attribute8
555 ,attribute9
556 ,attribute10
557 ,attribute11
558 ,attribute12
559 ,attribute13
560 ,attribute14
561 ,attribute15
562 ,created_by
563 ,creation_date
564 ,last_updated_by
565 ,last_update_date
566 ,last_update_login
567 ,parent_object_code
568 ,parent_object_id
569 ,stream_type_id
570 ,fee_type
571 ,structured_pricing
572 ,rate_template_id
573 ,rate_card_id
574 ,lease_rate_factor
575 ,target_arrears
576 ,effective_from
577 ,effective_to
578 ,supplier_id
579 ,rollover_quote_id
580 ,initial_direct_cost
581 ,fee_amount
582 ,target_amount
583 ,target_frequency
584 ,payment_type_id
585 ,fee_purpose_code
586 )
587 VALUES
588 (
589 p_fee_rec.id
590 ,p_fee_rec.object_version_number
591 ,p_fee_rec.attribute_category
592 ,p_fee_rec.attribute1
593 ,p_fee_rec.attribute2
594 ,p_fee_rec.attribute3
595 ,p_fee_rec.attribute4
596 ,p_fee_rec.attribute5
597 ,p_fee_rec.attribute6
598 ,p_fee_rec.attribute7
599 ,p_fee_rec.attribute8
600 ,p_fee_rec.attribute9
601 ,p_fee_rec.attribute10
602 ,p_fee_rec.attribute11
603 ,p_fee_rec.attribute12
604 ,p_fee_rec.attribute13
605 ,p_fee_rec.attribute14
606 ,p_fee_rec.attribute15
607 ,G_USER_ID
608 ,SYSDATE
609 ,G_USER_ID
610 ,SYSDATE
611 ,G_LOGIN_ID
612 ,p_fee_rec.parent_object_code
613 ,p_fee_rec.parent_object_id
614 ,p_fee_rec.stream_type_id
615 ,p_fee_rec.fee_type
616 ,p_fee_rec.structured_pricing
617 ,p_fee_rec.rate_template_id
618 ,p_fee_rec.rate_card_id
619 ,p_fee_rec.lease_rate_factor
620 ,p_fee_rec.target_arrears
621 ,p_fee_rec.effective_from
622 ,p_fee_rec.effective_to
623 ,p_fee_rec.supplier_id
624 ,p_fee_rec.rollover_quote_id
625 ,p_fee_rec.initial_direct_cost
626 ,p_fee_rec.fee_amount
627 ,p_fee_rec.target_amount
628 ,p_fee_rec.target_frequency
629 ,p_fee_rec.payment_type_id
630 ,p_fee_rec.fee_purpose_code
631 );
632
633 x_return_status := G_RET_STS_SUCCESS;
634
635 EXCEPTION
636
637 WHEN OTHERS THEN
638
639 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
640 p_msg_name => G_DB_ERROR,
641 p_token1 => G_PROG_NAME_TOKEN,
642 p_token1_value => l_prog_name,
643 p_token2 => G_SQLCODE_TOKEN,
644 p_token2_value => sqlcode,
645 p_token3 => G_SQLERRM_TOKEN,
646 p_token3_value => sqlerrm);
647
648 x_return_status := G_RET_STS_UNEXP_ERROR;
649
650 END insert_row;
651
652
653 ----------------------------
654 -- PROCEDURE insert_row (TL)
655 ----------------------------
656 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_feetl_rec IN feetl_rec_type) IS
657
658 CURSOR get_languages IS
659 SELECT language_code
660 FROM FND_LANGUAGES
661 WHERE INSTALLED_FLAG IN ('I', 'B');
662
663 l_sfwt_flag VARCHAR2(1);
664
665 l_prog_name VARCHAR2(61);
666
667 BEGIN
668
669 l_prog_name := G_PKG_NAME||'.insert_row (TL)';
670
671 FOR l_lang_rec IN get_languages LOOP
672
673 IF l_lang_rec.language_code = USERENV('LANG') THEN
674 l_sfwt_flag := 'N';
675 ELSE
676 l_sfwt_flag := 'Y';
677 END IF;
678
679 INSERT INTO OKL_FEES_TL (
680 id
681 ,language
682 ,source_lang
683 ,sfwt_flag
684 ,created_by
685 ,creation_date
686 ,last_updated_by
687 ,last_update_date
688 ,last_update_login
689 ,short_description
690 ,description
691 ,comments)
692 VALUES (
693 p_feetl_rec.id
694 ,l_lang_rec.language_code
695 ,USERENV('LANG')
696 ,l_sfwt_flag
697 ,G_USER_ID
698 ,SYSDATE
699 ,G_USER_ID
700 ,SYSDATE
701 ,G_LOGIN_ID
702 ,p_feetl_rec.short_description
703 ,p_feetl_rec.description
704 ,p_feetl_rec.comments);
705
706 END LOOP;
707
708 x_return_status := G_RET_STS_SUCCESS;
709
710 EXCEPTION
711
712 WHEN OTHERS THEN
713
714 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
715 p_msg_name => G_DB_ERROR,
716 p_token1 => G_PROG_NAME_TOKEN,
717 p_token1_value => l_prog_name,
718 p_token2 => G_SQLCODE_TOKEN,
719 p_token2_value => sqlcode,
720 p_token3 => G_SQLERRM_TOKEN,
721 p_token3_value => sqlerrm);
722
723 x_return_status := G_RET_STS_UNEXP_ERROR;
724
725 END insert_row;
726
727
728 ---------------------------
729 -- PROCEDURE insert_row (V)
730 ---------------------------
731 PROCEDURE insert_row (
732 x_return_status OUT NOCOPY VARCHAR2,
733 p_feev_rec IN feev_rec_type,
734 x_feev_rec OUT NOCOPY feev_rec_type) IS
735
736 l_return_status VARCHAR2(1);
737
738 l_feev_rec feev_rec_type;
739 l_fee_rec fee_rec_type;
740 l_feetl_rec feetl_rec_type;
741
742 l_prog_name VARCHAR2(61);
743
744 BEGIN
745
746 l_prog_name := G_PKG_NAME||'.insert_row (V)';
747
748 l_feev_rec := null_out_defaults (p_feev_rec);
749
750 SELECT okl_fee_seq.nextval INTO l_feev_rec.ID FROM DUAL;
751
752 l_feev_rec.OBJECT_VERSION_NUMBER := 1;
753
754 l_return_status := validate_attributes(l_feev_rec);
755
756 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
757 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
758 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
759 RAISE OKL_API.G_EXCEPTION_ERROR;
760 END IF;
761
762 l_return_status := validate_record(l_feev_rec);
763
764 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
765 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
766 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
767 RAISE OKL_API.G_EXCEPTION_ERROR;
768 END IF;
769
770 migrate (l_feev_rec, l_fee_rec);
771 migrate (l_feev_rec, l_feetl_rec);
772
773 insert_row (x_return_status => l_return_status, p_fee_rec => l_fee_rec);
774
775 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
776 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
777 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
778 RAISE OKL_API.G_EXCEPTION_ERROR;
779 END IF;
780
781 insert_row (x_return_status => l_return_status, p_feetl_rec => l_feetl_rec);
782
783 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
784 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
785 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
786 RAISE OKL_API.G_EXCEPTION_ERROR;
787 END IF;
788
789 x_feev_rec := l_feev_rec;
790 x_return_status := l_return_status;
791
792 EXCEPTION
793
794 WHEN OKL_API.G_EXCEPTION_ERROR THEN
795
796 x_return_status := G_RET_STS_ERROR;
797
798 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
799
800 x_return_status := G_RET_STS_UNEXP_ERROR;
801
802 WHEN OTHERS THEN
803
804 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
805 p_msg_name => G_DB_ERROR,
806 p_token1 => G_PROG_NAME_TOKEN,
807 p_token1_value => l_prog_name,
808 p_token2 => G_SQLCODE_TOKEN,
809 p_token2_value => sqlcode,
810 p_token3 => G_SQLERRM_TOKEN,
811 p_token3_value => sqlerrm);
812
813 x_return_status := G_RET_STS_UNEXP_ERROR;
814
815 END insert_row;
816
817
818 -----------------------------
819 -- PROCEDURE insert_row (REC)
820 -----------------------------
821 PROCEDURE insert_row(
822 p_api_version IN NUMBER,
823 p_init_msg_list IN VARCHAR2,
824 x_return_status OUT NOCOPY VARCHAR2,
825 x_msg_count OUT NOCOPY NUMBER,
826 x_msg_data OUT NOCOPY VARCHAR2,
827 p_feev_rec IN feev_rec_type,
828 x_feev_rec OUT NOCOPY feev_rec_type) IS
829
830 l_return_status VARCHAR2(1);
831
832 l_prog_name VARCHAR2(61);
833
834 BEGIN
835
836 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
837
838 IF p_init_msg_list = G_TRUE THEN
839 FND_MSG_PUB.initialize;
840 END IF;
841
842 insert_row (x_return_status => l_return_status,
843 p_feev_rec => p_feev_rec,
844 x_feev_rec => x_feev_rec);
845
846 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
847 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
848 ELSIF l_return_status = G_RET_STS_ERROR THEN
849 RAISE OKL_API.G_EXCEPTION_ERROR;
850 END IF;
851
852 x_return_status := l_return_status;
853
854 EXCEPTION
855
856 WHEN OKL_API.G_EXCEPTION_ERROR THEN
857
858 x_return_status := G_RET_STS_ERROR;
859
860 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
861
862 x_return_status := G_RET_STS_UNEXP_ERROR;
863
864 WHEN OTHERS THEN
865
866 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
867 p_msg_name => G_DB_ERROR,
868 p_token1 => G_PROG_NAME_TOKEN,
869 p_token1_value => l_prog_name,
870 p_token2 => G_SQLCODE_TOKEN,
871 p_token2_value => sqlcode,
872 p_token3 => G_SQLERRM_TOKEN,
873 p_token3_value => sqlerrm);
874
875 x_return_status := G_RET_STS_UNEXP_ERROR;
876
877 END insert_row;
878
879
880 -----------------------------
881 -- PROCEDURE insert_row (TBL)
882 -----------------------------
883 PROCEDURE insert_row(
884 p_api_version IN NUMBER,
885 p_init_msg_list IN VARCHAR2,
886 x_return_status OUT NOCOPY VARCHAR2,
887 x_msg_count OUT NOCOPY NUMBER,
888 x_msg_data OUT NOCOPY VARCHAR2,
889 p_feev_tbl IN feev_tbl_type,
890 x_feev_tbl OUT NOCOPY feev_tbl_type) IS
891
892 l_return_status VARCHAR2(1);
893 i BINARY_INTEGER;
894
895 l_prog_name VARCHAR2(61);
896
897 BEGIN
898
899 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
900
901 IF p_init_msg_list = G_TRUE THEN
902 FND_MSG_PUB.initialize;
903 END IF;
904
905 IF (p_feev_tbl.COUNT > 0) THEN
906 i := p_feev_tbl.FIRST;
907 LOOP
908 IF p_feev_tbl.EXISTS(i) THEN
909
910 insert_row (x_return_status => l_return_status,
911 p_feev_rec => p_feev_tbl(i),
912 x_feev_rec => x_feev_tbl(i));
913
914 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
915 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
916 ELSIF l_return_status = G_RET_STS_ERROR THEN
917 RAISE OKL_API.G_EXCEPTION_ERROR;
918 END IF;
919
920 EXIT WHEN (i = p_feev_tbl.LAST);
921 i := p_feev_tbl.NEXT(i);
922
923 END IF;
924
925 END LOOP;
926
927 ELSE
928
929 l_return_status := G_RET_STS_SUCCESS;
930
931 END IF;
932
933 x_return_status := l_return_status;
934
935 EXCEPTION
936
937 WHEN OKL_API.G_EXCEPTION_ERROR THEN
938
939 x_return_status := G_RET_STS_ERROR;
940
941 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
942
943 x_return_status := G_RET_STS_UNEXP_ERROR;
944
945 WHEN OTHERS THEN
946
947 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
948 p_msg_name => G_DB_ERROR,
949 p_token1 => G_PROG_NAME_TOKEN,
950 p_token1_value => l_prog_name,
951 p_token2 => G_SQLCODE_TOKEN,
952 p_token2_value => sqlcode,
953 p_token3 => G_SQLERRM_TOKEN,
954 p_token3_value => sqlerrm);
955
956 x_return_status := G_RET_STS_UNEXP_ERROR;
957
958 END insert_row;
959
960
961 ---------------------
962 -- PROCEDURE lock_row
963 ---------------------
964 PROCEDURE lock_row (x_return_status OUT NOCOPY VARCHAR2, p_fee_rec IN fee_rec_type) IS
965
966 E_Resource_Busy EXCEPTION;
967
968 PRAGMA EXCEPTION_INIT (E_Resource_Busy, -00054);
969
970 CURSOR lock_csr IS
971 SELECT OBJECT_VERSION_NUMBER
972 FROM OKL_FEES_B
973 WHERE ID = p_fee_rec.id
974 AND OBJECT_VERSION_NUMBER = p_fee_rec.object_version_number
975 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
976
977 CURSOR lchk_csr IS
978 SELECT OBJECT_VERSION_NUMBER
979 FROM OKL_FEES_B
980 WHERE ID = p_fee_rec.id;
981
982 l_object_version_number NUMBER;
983 lc_object_version_number NUMBER;
984
985 l_prog_name VARCHAR2(61);
986
987 BEGIN
988
989 l_prog_name := G_PKG_NAME||'.lock_row';
990
991 BEGIN
992 OPEN lock_csr;
993 FETCH lock_csr INTO l_object_version_number;
994 CLOSE lock_csr;
995 EXCEPTION
996 WHEN E_Resource_Busy THEN
997
998 IF (lock_csr%ISOPEN) THEN
999 CLOSE lock_csr;
1000 END IF;
1001 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1002 p_msg_name => G_OVN_ERROR2,
1003 p_token1 => G_PROG_NAME_TOKEN,
1004 p_token1_value => l_prog_name);
1005 RAISE OKL_API.G_EXCEPTION_ERROR;
1006 END;
1007
1008 IF l_object_version_number IS NULL THEN
1009
1010 OPEN lchk_csr;
1011 FETCH lchk_csr INTO lc_object_version_number;
1012 CLOSE lchk_csr;
1013
1014 IF lc_object_version_number IS NULL THEN
1015
1016 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1017 p_msg_name => G_OVN_ERROR3,
1018 p_token1 => G_PROG_NAME_TOKEN,
1019 p_token1_value => l_prog_name);
1020
1021 ELSIF lc_object_version_number <> p_fee_rec.object_version_number THEN
1022
1023 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1024 p_msg_name => G_OVN_ERROR,
1025 p_token1 => G_PROG_NAME_TOKEN,
1026 p_token1_value => l_prog_name);
1027
1028 END IF;
1029
1030 RAISE OKL_API.G_EXCEPTION_ERROR;
1031
1032 END IF;
1033
1034 x_return_status := G_RET_STS_SUCCESS;
1035
1036 EXCEPTION
1037
1038 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1039
1040 x_return_status := G_RET_STS_ERROR;
1041
1042 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1043
1044 x_return_status := G_RET_STS_UNEXP_ERROR;
1045
1046 WHEN OTHERS THEN
1047
1048 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1049 p_msg_name => G_DB_ERROR,
1050 p_token1 => G_PROG_NAME_TOKEN,
1051 p_token1_value => l_prog_name,
1052 p_token2 => G_SQLCODE_TOKEN,
1053 p_token2_value => sqlcode,
1054 p_token3 => G_SQLERRM_TOKEN,
1055 p_token3_value => sqlerrm);
1056
1057 x_return_status := G_RET_STS_UNEXP_ERROR;
1058
1059 END lock_row;
1060
1061
1062 ---------------------------
1063 -- PROCEDURE update_row (B)
1064 ---------------------------
1065 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_fee_rec IN fee_rec_type) IS
1066
1067 l_return_status VARCHAR2(1);
1068
1069 l_prog_name VARCHAR2(61);
1070
1071 BEGIN
1072
1073 l_prog_name := G_PKG_NAME||'.update_row (B)';
1074
1075 lock_row (x_return_status => l_return_status, p_fee_rec => p_fee_rec);
1076
1077 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1078 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1079 ELSIF l_return_status = G_RET_STS_ERROR THEN
1080 RAISE OKL_API.G_EXCEPTION_ERROR;
1081 END IF;
1082
1083 UPDATE okl_fees_b
1084 SET
1085 object_version_number = p_fee_rec.object_version_number+1
1086 ,attribute_category = p_fee_rec.attribute_category
1087 ,attribute1 = p_fee_rec.attribute1
1088 ,attribute2 = p_fee_rec.attribute2
1089 ,attribute3 = p_fee_rec.attribute3
1090 ,attribute4 = p_fee_rec.attribute4
1091 ,attribute5 = p_fee_rec.attribute5
1092 ,attribute6 = p_fee_rec.attribute6
1093 ,attribute7 = p_fee_rec.attribute7
1094 ,attribute8 = p_fee_rec.attribute8
1095 ,attribute9 = p_fee_rec.attribute9
1096 ,attribute10 = p_fee_rec.attribute10
1097 ,attribute11 = p_fee_rec.attribute11
1098 ,attribute12 = p_fee_rec.attribute12
1099 ,attribute13 = p_fee_rec.attribute13
1100 ,attribute14 = p_fee_rec.attribute14
1101 ,attribute15 = p_fee_rec.attribute15
1102 ,parent_object_code = p_fee_rec.parent_object_code
1103 ,parent_object_id = p_fee_rec.parent_object_id
1104 ,stream_type_id = p_fee_rec.stream_type_id
1105 ,fee_type = p_fee_rec.fee_type
1106 ,structured_pricing = p_fee_rec.structured_pricing
1107 ,rate_template_id = p_fee_rec.rate_template_id
1108 ,rate_card_id = p_fee_rec.rate_card_id
1109 ,lease_rate_factor = p_fee_rec.lease_rate_factor
1110 ,target_arrears = p_fee_rec.target_arrears
1111 ,effective_from = p_fee_rec.effective_from
1112 ,effective_to = p_fee_rec.effective_to
1113 ,supplier_id = p_fee_rec.supplier_id
1114 ,rollover_quote_id = p_fee_rec.rollover_quote_id
1115 ,initial_direct_cost = p_fee_rec.initial_direct_cost
1116 ,fee_amount = p_fee_rec.fee_amount
1117 ,target_amount = p_fee_rec.target_amount
1118 ,target_frequency = p_fee_rec.target_frequency
1119 ,payment_type_id = p_fee_rec.payment_type_id
1120 ,fee_purpose_code = p_fee_rec.fee_purpose_code
1121 WHERE id = p_fee_rec.id;
1122
1123 x_return_status := l_return_status;
1124
1125 EXCEPTION
1126
1127 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1128 x_return_status := G_RET_STS_ERROR;
1129
1130 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1131 x_return_status := G_RET_STS_UNEXP_ERROR;
1132
1133 WHEN OTHERS THEN
1134 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1135 p_msg_name => G_DB_ERROR,
1136 p_token1 => G_PROG_NAME_TOKEN,
1137 p_token1_value => l_prog_name,
1138 p_token2 => G_SQLCODE_TOKEN,
1139 p_token2_value => sqlcode,
1140 p_token3 => G_SQLERRM_TOKEN,
1141 p_token3_value => sqlerrm);
1142
1143 x_return_status := G_RET_STS_UNEXP_ERROR;
1144
1145 END update_row;
1146
1147
1148 ----------------------------
1149 -- PROCEDURE update_row (TL)
1150 ----------------------------
1151 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_feetl_rec IN feetl_rec_type) IS
1152
1153 l_prog_name VARCHAR2(61);
1154
1155 BEGIN
1156
1157 l_prog_name := G_PKG_NAME||'.update_row (TL)';
1158
1159 UPDATE OKL_FEES_TL
1160 SET
1161 source_lang = USERENV('LANG')
1162 ,sfwt_flag = 'Y'
1163 ,last_updated_by = G_USER_ID
1164 ,last_update_date = SYSDATE
1165 ,last_update_login = G_LOGIN_ID
1166 ,short_description = p_feetl_rec.short_description
1167 ,description = p_feetl_rec.description
1168 ,comments = p_feetl_rec.comments
1169 WHERE ID = p_feetl_rec.id;
1170
1171 UPDATE OKL_FEES_TL
1172 SET SFWT_FLAG = 'N'
1173 WHERE ID = p_feetl_rec.id
1174 AND SOURCE_LANG = LANGUAGE;
1175
1176 x_return_status := G_RET_STS_SUCCESS;
1177
1178 EXCEPTION
1179
1180 WHEN OTHERS THEN
1181
1182 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1183 p_msg_name => G_DB_ERROR,
1184 p_token1 => G_PROG_NAME_TOKEN,
1185 p_token1_value => l_prog_name,
1186 p_token2 => G_SQLCODE_TOKEN,
1187 p_token2_value => sqlcode,
1188 p_token3 => G_SQLERRM_TOKEN,
1189 p_token3_value => sqlerrm);
1190
1191 x_return_status := G_RET_STS_UNEXP_ERROR;
1192
1193 END update_row;
1194
1195
1196 ---------------------------
1197 -- PROCEDURE update_row (V)
1198 ---------------------------
1199 PROCEDURE update_row (
1200 x_return_status OUT NOCOPY VARCHAR2,
1201 p_feev_rec IN feev_rec_type,
1202 x_feev_rec OUT NOCOPY feev_rec_type) IS
1203
1204 l_prog_name VARCHAR2(61);
1205
1206 l_return_status VARCHAR2(1);
1207 l_feev_rec feev_rec_type;
1208 l_fee_rec fee_rec_type;
1209 l_feetl_rec feetl_rec_type;
1210
1211 ----------------------
1212 -- populate_new_record
1213 ----------------------
1214 FUNCTION populate_new_record (p_feev_rec IN feev_rec_type,
1215 x_feev_rec OUT NOCOPY feev_rec_type) RETURN VARCHAR2 IS
1216
1217 l_prog_name VARCHAR2(61) := G_PKG_NAME||'.populate_new_record';
1218 l_return_status VARCHAR2(1);
1219 l_db_feev_rec feev_rec_type;
1220
1221 BEGIN
1222
1223 x_feev_rec := p_feev_rec;
1224 l_db_feev_rec := get_rec (p_feev_rec.id, l_return_status);
1225
1226 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1227 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1228 ELSIF l_return_status = G_RET_STS_ERROR THEN
1229 RAISE OKL_API.G_EXCEPTION_ERROR;
1230 END IF;
1231
1232 IF x_feev_rec.attribute_category = FND_API.G_MISS_CHAR THEN
1233 x_feev_rec.attribute_category := l_db_feev_rec.attribute_category;
1234 END IF;
1235 IF x_feev_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1236 x_feev_rec.attribute1 := l_db_feev_rec.attribute1;
1237 END IF;
1238 IF x_feev_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1239 x_feev_rec.attribute2 := l_db_feev_rec.attribute2;
1240 END IF;
1241 IF x_feev_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1242 x_feev_rec.attribute3 := l_db_feev_rec.attribute3;
1243 END IF;
1244 IF x_feev_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1245 x_feev_rec.attribute4 := l_db_feev_rec.attribute4;
1246 END IF;
1247 IF x_feev_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1248 x_feev_rec.attribute5 := l_db_feev_rec.attribute5;
1249 END IF;
1250 IF x_feev_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1251 x_feev_rec.attribute6 := l_db_feev_rec.attribute6;
1252 END IF;
1253 IF x_feev_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1254 x_feev_rec.attribute7 := l_db_feev_rec.attribute7;
1255 END IF;
1256 IF x_feev_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1257 x_feev_rec.attribute8 := l_db_feev_rec.attribute8;
1258 END IF;
1259 IF x_feev_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1260 x_feev_rec.attribute9 := l_db_feev_rec.attribute9;
1261 END IF;
1262 IF x_feev_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1263 x_feev_rec.attribute10 := l_db_feev_rec.attribute10;
1264 END IF;
1265 IF x_feev_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1266 x_feev_rec.attribute11 := l_db_feev_rec.attribute11;
1267 END IF;
1268 IF x_feev_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1269 x_feev_rec.attribute12 := l_db_feev_rec.attribute12;
1270 END IF;
1271 IF x_feev_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1272 x_feev_rec.attribute13 := l_db_feev_rec.attribute13;
1273 END IF;
1274 IF x_feev_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1275 x_feev_rec.attribute14 := l_db_feev_rec.attribute14;
1276 END IF;
1277 IF x_feev_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1278 x_feev_rec.attribute15 := l_db_feev_rec.attribute15;
1279 END IF;
1280 IF x_feev_rec.parent_object_code = FND_API.G_MISS_CHAR THEN
1281 x_feev_rec.parent_object_code := l_db_feev_rec.parent_object_code;
1282 END IF;
1283 IF x_feev_rec.parent_object_id = FND_API.G_MISS_NUM THEN
1284 x_feev_rec.parent_object_id := l_db_feev_rec.parent_object_id;
1285 END IF;
1286 IF x_feev_rec.stream_type_id = FND_API.G_MISS_NUM THEN
1287 x_feev_rec.stream_type_id := l_db_feev_rec.stream_type_id;
1288 END IF;
1289 IF x_feev_rec.fee_type = FND_API.G_MISS_CHAR THEN
1290 x_feev_rec.fee_type := l_db_feev_rec.fee_type;
1291 END IF;
1292 IF x_feev_rec.structured_pricing = FND_API.G_MISS_CHAR THEN
1293 x_feev_rec.structured_pricing := l_db_feev_rec.structured_pricing;
1294 END IF;
1295 IF x_feev_rec.rate_template_id = FND_API.G_MISS_NUM THEN
1296 x_feev_rec.rate_template_id := l_db_feev_rec.rate_template_id;
1297 END IF;
1298 IF x_feev_rec.rate_card_id = FND_API.G_MISS_NUM THEN
1299 x_feev_rec.rate_card_id := l_db_feev_rec.rate_card_id;
1300 END IF;
1301 IF x_feev_rec.lease_rate_factor = FND_API.G_MISS_NUM THEN
1302 x_feev_rec.lease_rate_factor := l_db_feev_rec.lease_rate_factor;
1303 END IF;
1304 IF x_feev_rec.target_arrears = FND_API.G_MISS_CHAR THEN
1305 x_feev_rec.target_arrears := l_db_feev_rec.target_arrears;
1306 END IF;
1307 IF x_feev_rec.effective_from = FND_API.G_MISS_DATE THEN
1308 x_feev_rec.effective_from := l_db_feev_rec.effective_from;
1309 END IF;
1310 IF x_feev_rec.effective_to = FND_API.G_MISS_DATE THEN
1311 x_feev_rec.effective_to := l_db_feev_rec.effective_to;
1312 END IF;
1313 IF x_feev_rec.supplier_id = FND_API.G_MISS_NUM THEN
1314 x_feev_rec.supplier_id := l_db_feev_rec.supplier_id;
1315 END IF;
1316 IF x_feev_rec.rollover_quote_id = FND_API.G_MISS_NUM THEN
1317 x_feev_rec.rollover_quote_id := l_db_feev_rec.rollover_quote_id;
1318 END IF;
1319 IF x_feev_rec.initial_direct_cost = FND_API.G_MISS_NUM THEN
1320 x_feev_rec.initial_direct_cost := l_db_feev_rec.initial_direct_cost;
1321 END IF;
1322 IF x_feev_rec.fee_amount = FND_API.G_MISS_NUM THEN
1323 x_feev_rec.fee_amount := l_db_feev_rec.fee_amount;
1324 END IF;
1325 IF x_feev_rec.target_amount = FND_API.G_MISS_NUM THEN
1326 x_feev_rec.target_amount := l_db_feev_rec.target_amount;
1327 END IF;
1328 IF x_feev_rec.target_frequency = FND_API.G_MISS_CHAR THEN
1329 x_feev_rec.target_frequency := l_db_feev_rec.target_frequency;
1330 END IF;
1331 IF x_feev_rec.short_description = FND_API.G_MISS_CHAR THEN
1332 x_feev_rec.short_description := l_db_feev_rec.short_description;
1333 END IF;
1334 IF x_feev_rec.description = FND_API.G_MISS_CHAR THEN
1335 x_feev_rec.description := l_db_feev_rec.description;
1336 END IF;
1337 IF x_feev_rec.comments = FND_API.G_MISS_CHAR THEN
1338 x_feev_rec.comments := l_db_feev_rec.comments;
1339 END IF;
1340 IF x_feev_rec.payment_type_id = FND_API.G_MISS_NUM THEN
1341 x_feev_rec.payment_type_id := l_db_feev_rec.payment_type_id;
1342 END IF;
1343 IF x_feev_rec.fee_purpose_code = FND_API.G_MISS_CHAR THEN
1344 x_feev_rec.fee_purpose_code := l_db_feev_rec.fee_purpose_code;
1345 END IF;
1346
1347 RETURN l_return_status;
1348
1349 EXCEPTION
1350
1351 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1352
1353 x_return_status := G_RET_STS_ERROR;
1354
1355 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1356
1357 x_return_status := G_RET_STS_UNEXP_ERROR;
1358
1359 WHEN OTHERS THEN
1360
1361 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1362 p_msg_name => G_DB_ERROR,
1363 p_token1 => G_PROG_NAME_TOKEN,
1364 p_token1_value => l_prog_name,
1365 p_token2 => G_SQLCODE_TOKEN,
1366 p_token2_value => sqlcode,
1367 p_token3 => G_SQLERRM_TOKEN,
1368 p_token3_value => sqlerrm);
1369
1370 x_return_status := G_RET_STS_UNEXP_ERROR;
1371
1372 END populate_new_record;
1373
1374 BEGIN
1375
1376 l_prog_name := G_PKG_NAME||'.update_row (V)';
1377
1378 l_return_status := populate_new_record (p_feev_rec, l_feev_rec);
1379
1380 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1381 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1382 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1383 RAISE OKL_API.G_EXCEPTION_ERROR;
1384 END IF;
1385
1386 l_return_status := validate_attributes (l_feev_rec);
1387
1388 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1389 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1390 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1391 RAISE OKL_API.G_EXCEPTION_ERROR;
1392 END IF;
1393
1394 l_return_status := validate_record (l_feev_rec);
1395
1396 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1397 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1398 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1399 RAISE OKL_API.G_EXCEPTION_ERROR;
1400 END IF;
1401
1402 migrate (l_feev_rec, l_fee_rec);
1403 migrate (l_feev_rec, l_feetl_rec);
1404
1405 update_row (x_return_status => l_return_status, p_fee_rec => l_fee_rec);
1406
1407 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1408 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1409 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1410 RAISE OKL_API.G_EXCEPTION_ERROR;
1411 END IF;
1412
1413 update_row (x_return_status => l_return_status, p_feetl_rec => l_feetl_rec);
1414
1415 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1416 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1417 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1418 RAISE OKL_API.G_EXCEPTION_ERROR;
1419 END IF;
1420
1421 x_return_status := l_return_status;
1422 x_feev_rec := l_feev_rec;
1423
1424 EXCEPTION
1425
1426 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1427
1428 x_return_status := G_RET_STS_ERROR;
1429
1430 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1431
1432 x_return_status := G_RET_STS_UNEXP_ERROR;
1433
1434 WHEN OTHERS THEN
1435
1436 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1437 p_msg_name => G_DB_ERROR,
1438 p_token1 => G_PROG_NAME_TOKEN,
1439 p_token1_value => l_prog_name,
1440 p_token2 => G_SQLCODE_TOKEN,
1441 p_token2_value => sqlcode,
1442 p_token3 => G_SQLERRM_TOKEN,
1443 p_token3_value => sqlerrm);
1444
1445 x_return_status := G_RET_STS_UNEXP_ERROR;
1446
1447 END update_row;
1448
1449
1450 -----------------------------
1451 -- PROCEDURE update_row (REC)
1452 -----------------------------
1453 PROCEDURE update_row(
1454 p_api_version IN NUMBER,
1455 p_init_msg_list IN VARCHAR2,
1456 x_return_status OUT NOCOPY VARCHAR2,
1457 x_msg_count OUT NOCOPY NUMBER,
1458 x_msg_data OUT NOCOPY VARCHAR2,
1459 p_feev_rec IN feev_rec_type,
1460 x_feev_rec OUT NOCOPY feev_rec_type) IS
1461
1462 l_return_status VARCHAR2(1);
1463
1464 l_prog_name VARCHAR2(61);
1465
1466 BEGIN
1467
1468 l_prog_name := G_PKG_NAME||'.update_row (REC)';
1469
1470 IF p_init_msg_list = G_TRUE THEN
1471 FND_MSG_PUB.initialize;
1472 END IF;
1473
1474 update_row (x_return_status => l_return_status,
1475 p_feev_rec => p_feev_rec,
1476 x_feev_rec => x_feev_rec);
1477
1478 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1479 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1480 ELSIF l_return_status = G_RET_STS_ERROR THEN
1481 RAISE OKL_API.G_EXCEPTION_ERROR;
1482 END IF;
1483
1484 x_return_status := l_return_status;
1485
1486 EXCEPTION
1487
1488 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1489
1490 x_return_status := G_RET_STS_ERROR;
1491
1492 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1493
1494 x_return_status := G_RET_STS_UNEXP_ERROR;
1495
1496 WHEN OTHERS THEN
1497
1498 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1499 p_msg_name => G_DB_ERROR,
1500 p_token1 => G_PROG_NAME_TOKEN,
1501 p_token1_value => l_prog_name,
1502 p_token2 => G_SQLCODE_TOKEN,
1503 p_token2_value => sqlcode,
1504 p_token3 => G_SQLERRM_TOKEN,
1505 p_token3_value => sqlerrm);
1506
1507 x_return_status := G_RET_STS_UNEXP_ERROR;
1508
1509 END update_row;
1510
1511
1512 -----------------------------
1513 -- PROCEDURE update_row (TBL)
1514 -----------------------------
1515 PROCEDURE update_row(
1516 p_api_version IN NUMBER,
1517 p_init_msg_list IN VARCHAR2,
1518 x_return_status OUT NOCOPY VARCHAR2,
1519 x_msg_count OUT NOCOPY NUMBER,
1520 x_msg_data OUT NOCOPY VARCHAR2,
1521 p_feev_tbl IN feev_tbl_type,
1522 x_feev_tbl OUT NOCOPY feev_tbl_type) IS
1523
1524 l_return_status VARCHAR2(1);
1525 i BINARY_INTEGER;
1526 l_prog_name VARCHAR2(61);
1527
1528 BEGIN
1529
1530 l_prog_name := G_PKG_NAME||'.update_row (TBL)';
1531
1532 IF p_init_msg_list = G_TRUE THEN
1533 FND_MSG_PUB.initialize;
1534 END IF;
1535
1536 x_feev_tbl := p_feev_tbl;
1537
1538 IF (p_feev_tbl.COUNT > 0) THEN
1539
1540 i := p_feev_tbl.FIRST;
1541
1542 LOOP
1543
1544 IF p_feev_tbl.EXISTS(i) THEN
1545 update_row (x_return_status => l_return_status,
1546 p_feev_rec => p_feev_tbl(i),
1547 x_feev_rec => x_feev_tbl(i));
1548
1549 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1550 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1551 ELSIF l_return_status = G_RET_STS_ERROR THEN
1552 RAISE OKL_API.G_EXCEPTION_ERROR;
1553 END IF;
1554
1555 EXIT WHEN (i = p_feev_tbl.LAST);
1556 i := p_feev_tbl.NEXT(i);
1557
1558 END IF;
1559
1560 END LOOP;
1561
1562 ELSE
1563
1564 l_return_status := G_RET_STS_SUCCESS;
1565
1566 END IF;
1567
1568 x_return_status := l_return_status;
1569
1570 EXCEPTION
1571
1572 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1573
1574 x_return_status := G_RET_STS_ERROR;
1575
1576 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1577
1578 x_return_status := G_RET_STS_UNEXP_ERROR;
1579
1580 WHEN OTHERS THEN
1581
1582 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1583 p_msg_name => G_DB_ERROR,
1584 p_token1 => G_PROG_NAME_TOKEN,
1585 p_token1_value => l_prog_name,
1586 p_token2 => G_SQLCODE_TOKEN,
1587 p_token2_value => sqlcode,
1588 p_token3 => G_SQLERRM_TOKEN,
1589 p_token3_value => sqlerrm);
1590
1591 x_return_status := G_RET_STS_UNEXP_ERROR;
1592
1593 END update_row;
1594
1595
1596 -----------------
1597 -- delete_row (V)
1598 -----------------
1599 PROCEDURE delete_row(
1600 x_return_status OUT NOCOPY VARCHAR2,
1601 p_id IN NUMBER) IS
1602
1603 l_prog_name VARCHAR2(61);
1604
1605 BEGIN
1606
1607 l_prog_name := G_PKG_NAME||'.delete_row (V)';
1608
1609 DELETE FROM OKL_FEES_B WHERE id = p_id;
1610 DELETE FROM OKL_FEES_TL WHERE id = p_id;
1611
1612 x_return_status := G_RET_STS_SUCCESS;
1613
1614 EXCEPTION
1615
1616 WHEN OTHERS THEN
1617
1618 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1619 p_msg_name => G_DB_ERROR,
1620 p_token1 => G_PROG_NAME_TOKEN,
1621 p_token1_value => l_prog_name,
1622 p_token2 => G_SQLCODE_TOKEN,
1623 p_token2_value => sqlcode,
1624 p_token3 => G_SQLERRM_TOKEN,
1625 p_token3_value => sqlerrm);
1626
1627 x_return_status := G_RET_STS_UNEXP_ERROR;
1628
1629 END delete_row;
1630
1631
1632 -----------------------------
1633 -- PROCEDURE delete_row (REC)
1634 -----------------------------
1635 PROCEDURE delete_row(
1636 p_api_version IN NUMBER,
1637 p_init_msg_list IN VARCHAR2,
1638 x_return_status OUT NOCOPY VARCHAR2,
1639 x_msg_count OUT NOCOPY NUMBER,
1640 x_msg_data OUT NOCOPY VARCHAR2,
1641 p_feev_rec IN feev_rec_type) IS
1642
1643 l_return_status VARCHAR2(1);
1644
1645 l_prog_name VARCHAR2(61);
1646
1647 BEGIN
1648
1649 l_prog_name := G_PKG_NAME||'.delete_row (REC)';
1650
1651 IF p_init_msg_list = G_TRUE THEN
1652 FND_MSG_PUB.initialize;
1653 END IF;
1654
1655 delete_row (x_return_status => l_return_status,
1656 p_id => p_feev_rec.id);
1657
1658 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1659 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1660 ELSIF l_return_status = G_RET_STS_ERROR THEN
1661 RAISE OKL_API.G_EXCEPTION_ERROR;
1662 END IF;
1663
1664 x_return_status := l_return_status;
1665
1666 EXCEPTION
1667
1668 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1669
1670 x_return_status := G_RET_STS_ERROR;
1671
1672 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1673
1674 x_return_status := G_RET_STS_UNEXP_ERROR;
1675
1676 WHEN OTHERS THEN
1677
1678 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1679 p_msg_name => G_DB_ERROR,
1680 p_token1 => G_PROG_NAME_TOKEN,
1681 p_token1_value => l_prog_name,
1682 p_token2 => G_SQLCODE_TOKEN,
1683 p_token2_value => sqlcode,
1684 p_token3 => G_SQLERRM_TOKEN,
1685 p_token3_value => sqlerrm);
1686
1687 x_return_status := G_RET_STS_UNEXP_ERROR;
1688
1689 END delete_row;
1690
1691
1692 -------------------
1693 -- delete_row (TBL)
1694 -------------------
1695 PROCEDURE delete_row(
1696 p_api_version IN NUMBER,
1697 p_init_msg_list IN VARCHAR2,
1698 x_return_status OUT NOCOPY VARCHAR2,
1699 x_msg_count OUT NOCOPY NUMBER,
1700 x_msg_data OUT NOCOPY VARCHAR2,
1701 p_feev_tbl IN feev_tbl_type) IS
1702
1703 l_return_status VARCHAR2(1);
1704 i BINARY_INTEGER;
1705
1706 l_prog_name VARCHAR2(61);
1707
1708 BEGIN
1709
1710 l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
1711
1712 IF p_init_msg_list = G_TRUE THEN
1713 FND_MSG_PUB.initialize;
1714 END IF;
1715
1716 IF (p_feev_tbl.COUNT > 0) THEN
1717
1718 i := p_feev_tbl.FIRST;
1719
1720 LOOP
1721
1722 IF p_feev_tbl.EXISTS(i) THEN
1723
1724 delete_row (x_return_status => l_return_status,
1725 p_id => p_feev_tbl(i).id);
1726
1727 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1728 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1729 ELSIF l_return_status = G_RET_STS_ERROR THEN
1730 RAISE OKL_API.G_EXCEPTION_ERROR;
1731 END IF;
1732
1733 EXIT WHEN (i = p_feev_tbl.LAST);
1734 i := p_feev_tbl.NEXT(i);
1735
1736 END IF;
1737
1738 END LOOP;
1739
1740 ELSE
1741
1742 l_return_status := G_RET_STS_SUCCESS;
1743
1744 END IF;
1745
1746 x_return_status := l_return_status;
1747
1748 EXCEPTION
1749
1750 WHEN OTHERS THEN
1751
1752 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1753 p_msg_name => G_DB_ERROR,
1754 p_token1 => G_PROG_NAME_TOKEN,
1755 p_token1_value => l_prog_name,
1756 p_token2 => G_SQLCODE_TOKEN,
1757 p_token2_value => sqlcode,
1758 p_token3 => G_SQLERRM_TOKEN,
1759 p_token3_value => sqlerrm);
1760
1761 x_return_status := G_RET_STS_UNEXP_ERROR;
1762
1763 END delete_row;
1764
1765
1766 END OKL_FEE_PVT;