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