1 PACKAGE BODY okl_accounting_util AS
2 /* $Header: OKLRAUTB.pls 120.19 2011/05/20 04:27:36 rpillay ship $ */
3
4 /**************************************************************************/
5
6 PROCEDURE get_segment_array
7 (p_concate_segments IN VARCHAR2,
8 p_delimiter IN VARCHAR2,
9 p_seg_array_type OUT NOCOPY seg_array_type)
10 AS
11 j INTEGER := 1;
12 l_next_char VARCHAR2(1);
13 l_seg_value VARCHAR2(100);
14 BEGIN
15 FOR i IN 1..LENGTH(p_concate_segments)
16 LOOP
17 l_next_char := SUBSTR(p_concate_segments, i,1);
18 IF l_next_char = p_delimiter
19 THEN
20 p_seg_array_type(j) := l_seg_value;
21 l_seg_value := NULL;
22 j := j + 1;
23 ELSE
24 l_seg_value := l_seg_value || l_next_char;
25 END IF;
26 END LOOP;
27 p_seg_array_type(j) := l_seg_value;
28 END get_segment_array;
29
30 /**************************************************************************/
31
32 FUNCTION get_concate_desc
33 (p_chart_of_account_id IN NUMBER,
34 p_concate_segments IN VARCHAR2)
35 RETURN VARCHAR2
36 AS
37 CURSOR flex_segment_csr
38 IS
39 SELECT flex_value_set_id
40 FROM fnd_id_flex_segments
41 WHERE id_flex_num = p_chart_of_account_id
42 AND application_id = 101
43 AND id_flex_code = 'GL#'
44 AND enabled_flag = 'Y'
45 ORDER BY segment_num;
46
47 i INTEGER := 1;
48 l_delimiter VARCHAR2(1);
49 l_seg_array OKL_ACCOUNTING_UTIL.seg_array_type;
50 l_seg_desc VARCHAR2(2000);
51 --Bug# 12336692
52 l_concate_desc VARCHAR2(2000);
53 l_parent_id fnd_flex_value_sets.parent_flex_value_set_id%TYPE;
54 l_parent_value_low FND_FLEX_VALUES.PARENT_FLEX_VALUE_LOW%TYPE;
55
56 -- Changed by Santonyr on 13-May-2004
57 -- Fixed Bug Number 3628755
58
59 TYPE segment_rec_type IS RECORD
60 (segment_value fnd_flex_values_vl.flex_value%TYPE,
61 flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE
62 );
63
64 TYPE segment_table_type IS TABLE OF segment_rec_type INDEX BY BINARY_INTEGER;
65
66 l_segment_table segment_table_type;
67 l_segment_table_1 segment_table_type;
68
69
70 CURSOR parent_flex_value_csr(l_flex_value_set_id NUMBER)
71 IS
72 SELECT parent_flex_value_set_id
73 FROM fnd_flex_value_sets
74 WHERE flex_value_set_id = l_flex_value_set_id;
75
76
77 --rkuttiya removed the INTO clause from this Cursor SELECT
78 --found during 12.1 Multi GAAP Test
79 CURSOR parent_flex_value_desc_csr(
80 l_flex_value_set_id NUMBER,
81 l_segment_value VARCHAR2)
82 IS
83 SELECT description
84 FROM fnd_flex_values_vl
85 WHERE flex_value_set_id = l_flex_value_set_id
86 AND flex_value = l_segment_value;
87
88
89 --rkuttiya removed the INTO clause from this Cursor SELECT
90 --found during Multi GAAP test
91 CURSOR parent_flex_low_desc_csr(
92 l_flex_value_set_id NUMBER,
93 l_segment_value VARCHAR2,
94 l_parent_value_low VARCHAR2
95 ) IS
96 SELECT description
97 FROM fnd_flex_values_vl
98 WHERE flex_value_set_id = l_flex_value_set_id
99 AND flex_value = l_segment_value
100 AND parent_flex_value_low = l_parent_value_low;
101
102 BEGIN
103
104 l_delimiter := fnd_flex_apis.get_segment_delimiter(
105 x_application_id => 101,
106 x_id_flex_code => 'GL#',
107 x_id_flex_num => p_chart_of_account_id);
108
109 OKL_ACCOUNTING_UTIL.get_segment_array
110 (p_concate_segments => p_concate_segments,
111 p_delimiter => l_delimiter,
112 p_seg_array_type => l_seg_array);
113
114 FOR i IN l_seg_array.FIRST..l_seg_array.LAST LOOP
115 l_segment_table(i).segment_value := l_seg_array(i);
116 END LOOP;
117
118 i := 1;
119 FOR l_flex_segment_csr IN flex_segment_csr LOOP
120 l_segment_table(i).flex_value_set_id := l_flex_segment_csr.flex_value_set_id;
121 i := i + 1;
122 END LOOP;
123
124 l_segment_table_1 := l_segment_table;
125
126 FOR i IN l_segment_table.FIRST..l_segment_table.LAST LOOP
127
128 OPEN parent_flex_value_csr(l_segment_table(i).flex_value_set_id) ;
129 FETCH parent_flex_value_csr INTO l_parent_id;
130 CLOSE parent_flex_value_csr;
131
132 IF l_parent_id IS NULL THEN
133
134 OPEN parent_flex_value_desc_csr(
135 l_segment_table(i).flex_value_set_id,
136 l_segment_table(i).segment_value);
137 FETCH parent_flex_value_desc_csr INTO l_seg_desc;
138 CLOSE parent_flex_value_desc_csr;
139
140 ELSE
141
142 FOR j IN l_segment_table_1.FIRST.. l_segment_table_1.LAST LOOP
143 IF l_parent_id = l_segment_table_1(j).flex_value_set_id THEN
144 l_parent_value_low := l_segment_table_1(j).segment_value;
145 EXIT;
146 END IF;
147 END LOOP;
148
149 IF l_parent_value_low IS NOT NULL THEN
150 OPEN parent_flex_low_desc_csr(
151 l_segment_table(i).flex_value_set_id,
152 l_segment_table(i).segment_value,
153 l_parent_value_low);
154 FETCH parent_flex_low_desc_csr INTO l_seg_desc;
155 CLOSE parent_flex_low_desc_csr;
156 END IF;
157
158 END IF;
159
160 IF i = 1 THEN
161 l_concate_desc := l_seg_desc;
162 ELSE
163 l_concate_desc := l_concate_desc || l_delimiter || l_seg_desc;
164 END IF;
165
166 END LOOP;
167
168 RETURN l_concate_desc;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 RETURN(SQLERRM);
173
174 END get_concate_desc;
175
176 /**************************************************************************/
177
178 FUNCTION validate_lookup_code
179 (p_lookup_type IN VARCHAR2,
180 p_lookup_code IN VARCHAR2,
181 p_app_id IN NUMBER DEFAULT 540,
182 p_view_app_id IN NUMBER DEFAULT 0)
183 RETURN VARCHAR2
184 AS
185 l_found VARCHAR2(1);
186 l_sysdate DATE := G_SYSDATE;
187
188 CURSOR lkup_csr IS
189 SELECT '1'
190 FROM fnd_lookup_values flv,
191 fnd_lookup_types flt
192 WHERE
193 flv.lookup_type = p_lookup_type
194 AND flv.view_application_id = p_view_app_id
195 AND flv.lookup_code = p_lookup_code
196 AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id)
197 AND flv.LANGUAGE = USERENV('LANG')
198 AND flv.enabled_flag = 'Y'
199 AND TRUNC(NVL(flv.start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
200 AND TRUNC(NVL(flv.end_date_active, l_sysdate)) >= TRUNC(l_sysdate)
201 AND flv.lookup_type = flt.lookup_type
202 AND flv.view_application_id = flt.view_application_id
203 AND flv.security_group_id = flt.security_group_id
204 AND flt.application_id = p_app_id;
205
206 BEGIN
207
208 OPEN lkup_csr ;
209 FETCH lkup_csr INTO l_found;
210 CLOSE lkup_csr;
211
212 IF l_found IS NOT NULL THEN
213 RETURN Okl_Api.g_true;
214 ELSE
215 RETURN Okl_Api.g_false;
216 END IF;
217
218 EXCEPTION
219 WHEN OTHERS THEN
220 RETURN Okl_Api.g_false;
221
222 END validate_lookup_code;
223
224
225 /**************************************************************************/
226
227 PROCEDURE get_error_message
228 (p_msg_count OUT NOCOPY NUMBER,
229 p_msg_text OUT NOCOPY VARCHAR2)
230 IS
231 l_msg_text VARCHAR2(1000);
232 BEGIN
233 p_msg_count := fnd_msg_pub.count_msg;
234 FOR i IN 1..p_msg_count
235 LOOP
236 fnd_msg_pub.get
237 (p_data => l_msg_text,
238 p_msg_index_out => p_msg_count,
239 p_encoded => fnd_api.g_false,
240 p_msg_index => fnd_msg_pub.g_next
241 );
242 IF i = 1 THEN
243 p_msg_text := l_msg_text;
244 ELSE
245 p_msg_text := p_msg_text || '--' || l_msg_text;
246 END IF;
247 END LOOP;
248 EXCEPTION
249 WHEN OTHERS THEN
250 p_msg_text := SQLERRM;
251 END get_error_message;
252
253 /**************************************************************************/
254
255 PROCEDURE get_error_message(p_all_message OUT NOCOPY error_message_type)
256 IS
257 l_msg_text VARCHAR2(2000);
258 l_msg_count NUMBER ;
259 BEGIN
260 l_msg_count := fnd_msg_pub.count_msg;
261 FOR i IN 1..l_msg_count
262 LOOP
263 fnd_msg_pub.get
264 (p_data => p_all_message(i),
265 p_msg_index_out => l_msg_count,
266 p_encoded => fnd_api.g_false,
267 p_msg_index => fnd_msg_pub.g_next
268 );
269 END LOOP;
270 EXCEPTION
271 WHEN OTHERS THEN
272 NULL;
273 END get_error_message;
274
275 /**************************************************************************/
276 --Bug 4700150. SGIYER. Wrote new procedure as first msg is not printing using std
277 -- fnd procedure above. fnd_msg_pub.g_next does not return first msg in stack.
278 -- For below procedure, stack must be cleared if processing within a loop.
279 PROCEDURE get_error_msg(p_all_message OUT NOCOPY error_message_type)
280 IS
281 l_msg_text VARCHAR2(2000);
282 l_msg_count NUMBER ;
283 l_msg_index_out NUMBER;
284 l_counter NUMBER := 1;
285 BEGIN
286 l_msg_count := fnd_msg_pub.count_msg;
287
288 FOR i IN 1..l_msg_count
289 LOOP
290 l_msg_text := NULL;
291 IF i = 1 THEN
292 fnd_msg_pub.get
293 (p_data => l_msg_text,
294 p_msg_index_out => l_msg_index_out,
295 p_encoded => fnd_api.g_false,
296 p_msg_index => fnd_msg_pub.g_first
297 );
298 ELSE
299 fnd_msg_pub.get
300 (p_data => l_msg_text,
301 p_msg_index_out => l_msg_index_out,
302 p_encoded => fnd_api.g_false,
303 p_msg_index => fnd_msg_pub.g_next
304 );
305 END IF;
306 IF l_msg_text IS NOT NULL THEN
307 p_all_message(l_counter) := l_msg_text;
308 l_counter := l_counter + 1;
309 END IF;
310 END LOOP;
311 EXCEPTION
312 WHEN OTHERS THEN
313 NULL;
314 END get_error_msg;
315
316 /**************************************************************************/
317
318 FUNCTION validate_currency_code(p_currency_code IN VARCHAR2)
319 RETURN VARCHAR2
320 AS
321 l_found VARCHAR2(1);
322 l_sysdate DATE := G_SYSDATE;
323
324 CURSOR curr_csr IS
325 SELECT '1'
326 FROM fnd_currencies_vl
327 WHERE currency_code = p_currency_code
328 AND ENABLED_FLAG = 'Y'
329 AND TRUNC(NVL(start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
330 AND TRUNC(NVL(end_date_active, l_sysdate)) >= TRUNC(l_sysdate);
331
332 BEGIN
333
334 OPEN curr_csr;
335 FETCH curr_csr INTO l_found;
336 CLOSE curr_csr;
337
338 IF l_found IS NOT NULL THEN
339 RETURN Okl_Api.G_TRUE;
340 ELSE
341 RETURN Okl_Api.G_FALSE;
342 END IF;
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 RETURN Okl_Api.G_FALSE;
347 END validate_currency_code;
348
349 /**************************************************************************/
350 /* rkuttiya modified for Multi GAAP project
351 * 10-JUL-2008 Added new parameter p_ledger_id
352 */
353 FUNCTION validate_gl_ccid(p_ccid IN VARCHAR2,
354 p_ledger_id IN NUMBER DEFAULT NULL)
355 RETURN VARCHAR2
356 AS
357 l_found VARCHAR2(1);
358 l_chart_of_accounts_id NUMBER;
359 l_sysdate DATE := G_SYSDATE;
360
361 CURSOR coa_csr(p_ledger_id IN NUMBER) IS
362 SELECT chart_of_accounts_id
363 FROM GL_LEDGERS_PUBLIC_V
364 WHERE ledger_id = NVL(p_ledger_id,get_set_of_books_id);
365
366 CURSOR ccid_csr (l_chart_of_accounts_id NUMBER) IS
367 SELECT '1'
368 FROM gl_code_combinations
369 WHERE code_combination_id = p_ccid
370 AND chart_of_accounts_id = l_chart_of_accounts_id
371 AND ENABLED_FLAG = 'Y'
372 AND TRUNC(NVL(start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
373 AND TRUNC(NVL(end_date_active, l_sysdate)) >= TRUNC(l_sysdate);
374
375 BEGIN
376
377 OPEN coa_csr(p_ledger_id);
378 FETCH coa_csr INTO l_chart_of_accounts_id;
379 CLOSE coa_csr;
380
381 OPEN ccid_csr (l_chart_of_accounts_id);
382 FETCH ccid_csr INTO l_found;
383 CLOSE ccid_csr;
384
385 IF l_found IS NOT NULL THEN
386 RETURN Okl_Api.G_TRUE;
387 ELSE
388 RETURN Okl_Api.G_FALSE;
389 END IF;
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 RETURN Okl_Api.G_FALSE;
394 END validate_gl_ccid;
395
396 /**************************************************************************/
397 --Added p_ledger_id argument as part of bug 5707866 by nikshah
398 --If p_ledger_id passed to the API is null then it considers ledger from primary representation.
399 --Otherwise, it considers the ledger id that is passed to it.
400 --Cursor changed to accept one parameter: l_ledger_id
401
402 FUNCTION get_okl_period_status(p_period_name IN VARCHAR2, p_ledger_id IN NUMBER DEFAULT NULL)
403 RETURN VARCHAR2
404 AS
405 l_period_status VARCHAR2(1);
406 l_ledger_id NUMBER;
407
408 CURSOR sts_csr (l_ledger_id NUMBER) IS
409 SELECT closing_status
410 FROM gl_period_statuses
411 WHERE application_id = 540
412 AND ledger_id = l_ledger_id
413 AND period_name = p_period_name;
414
415 BEGIN
416 l_ledger_id := p_ledger_id;
417 IF l_ledger_id IS NULL
418 THEN
419 l_ledger_id := get_set_of_books_id;
420 END IF;
421 OPEN sts_csr (l_ledger_id);
422 FETCH sts_csr INTO l_period_status;
423 CLOSE sts_csr;
424
425 RETURN l_period_status;
426
427 EXCEPTION
428 WHEN OTHERS THEN
429 RETURN(NULL);
430 END get_okl_period_status;
431
432 /**************************************************************************/
433 --Added p_ledger_id argument as part of bug 5707866 by nikshah
434 --If p_ledger_id passed to the API is null then it considers ledger from primary representation.
435 --Otherwise, it considers the ledger id that is passed to it.
436 --Cursor changed to accept one parameter: l_ledger_id
437
438 FUNCTION get_gl_period_status(p_period_name IN VARCHAR2, p_ledger_id IN NUMBER DEFAULT NULL)
439 RETURN VARCHAR2
440 AS
441 l_period_status VARCHAR2(1);
442 l_ledger_id NUMBER;
443
444 CURSOR sts_csr (l_ledger_id NUMBER) IS
445 SELECT closing_status
446 FROM gl_period_statuses
447 WHERE application_id = 101
448 AND ledger_id = l_ledger_id
449 AND period_name = p_period_name;
450
451
452 BEGIN
453 l_ledger_id := p_ledger_id;
454 IF l_ledger_id IS NULL
455 THEN
456 l_ledger_id := get_set_of_books_id;
457 END IF;
458 OPEN sts_csr (l_ledger_id);
459 FETCH sts_csr INTO l_period_status;
460 CLOSE sts_csr;
461
462 RETURN l_period_status;
463 EXCEPTION
464 WHEN OTHERS THEN
465 RETURN(NULL);
466 END get_gl_period_status;
467
468 /**************************************************************************/
469 --Added p_ledger_id argument as part of bug 5707866 by nikshah
470 --If p_ledger_id passed to the API is null then it considers ledger from primary representation.
471 --Otherwise, it considers the ledger id that is passed to it.
472 --Cursor changed to accept one parameter: l_ledger_id
473
474 PROCEDURE get_period_info(p_date IN DATE,
475 p_period_name OUT NOCOPY VARCHAR2,
476 p_start_date OUT NOCOPY DATE,
477 p_end_date OUT NOCOPY DATE, p_ledger_id IN NUMBER DEFAULT NULL)
478 AS
479 l_period_name VARCHAR2(15);
480 l_period_set_name VARCHAR2(15);
481 l_user_period_type VARCHAR2(15);
482 l_ledger_id NUMBER;
483
484 CURSOR prd_set_csr (l_ledger_id NUMBER) IS
485 SELECT period_set_name, accounted_period_type
486 FROM GL_LEDGERS_PUBLIC_V
487 WHERE ledger_id = l_ledger_id;
488
489 CURSOR prd_name_csr (l_period_set_name VARCHAR2, l_user_period_type VARCHAR2) IS
490 SELECT period_name, start_date, end_date
491 FROM gl_periods
492 WHERE TRUNC(start_date) <= TRUNC(p_date)
493 AND TRUNC(end_date) >= TRUNC(p_date)
494 AND period_set_name = l_period_set_name
495 AND period_type = l_user_period_type
496 -- Added by Santonyr on 24-Dec-2002 for the bug fix 2675596
497 AND NVL(ADJUSTMENT_PERIOD_FLAG, 'N') = 'N';
498
499 BEGIN
500 l_ledger_id := p_ledger_id;
501 IF l_ledger_id IS NULL
502 THEN
503 l_ledger_id := get_set_of_books_id;
504 END IF;
505
506 OPEN prd_set_csr(l_ledger_id);
507 FETCH prd_set_csr INTO l_period_set_name, l_user_period_type;
508 CLOSE prd_set_csr;
509
510 OPEN prd_name_csr (l_period_set_name, l_user_period_type);
511 FETCH prd_name_csr INTO p_period_name, p_start_date, p_end_date;
512 CLOSE prd_name_csr;
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 NULL;
517 END get_period_info;
518
519 /**************************************************************************/
520 --Added p_ledger_id argument as part of bug 5707866 by nikshah
521 --If p_ledger_id passed to the API is null then it considers ledger from primary representation.
522 --Otherwise, it considers the ledger id that is passed to it.
523 --Cursor changed to accept one parameter: l_ledger_id
524
525 PROCEDURE get_period_info(p_period_name IN VARCHAR2,
526 p_start_date OUT NOCOPY DATE,
527 p_end_date OUT NOCOPY DATE, p_ledger_id IN NUMBER DEFAULT NULL)
528 AS
529 l_period_name VARCHAR2(15);
530 l_period_set_name VARCHAR2(15);
531 l_user_period_type VARCHAR2(15);
532 l_ledger_id NUMBER;
533
534 CURSOR perd_set_csr(l_ledger_id NUMBER) IS
535 SELECT period_set_name, accounted_period_type
536 FROM GL_LEDGERS_PUBLIC_V
537 WHERE ledger_id = l_ledger_id;
538
539 CURSOR perd_dt_csr (l_period_set_name VARCHAR2, l_user_period_type VARCHAR2) IS
540 SELECT start_date, end_date
541 FROM gl_periods
542 WHERE period_name = p_period_name
543 AND period_set_name = l_period_set_name
544 AND period_type = l_user_period_type;
545
546 BEGIN
547 l_ledger_id := p_ledger_id;
548 IF l_ledger_id IS NULL
549 THEN
550 l_ledger_id := get_set_of_books_id;
551 END IF;
552 OPEN perd_set_csr(l_ledger_id);
553 FETCH perd_set_csr INTO l_period_set_name, l_user_period_type;
554 CLOSE perd_set_csr;
555
556 OPEN perd_dt_csr(l_period_set_name, l_user_period_type);
557 FETCH perd_dt_csr INTO p_start_date, p_end_date;
558 CLOSE perd_dt_csr;
559
560 EXCEPTION
561 WHEN OTHERS THEN
562 NULL;
563 END get_period_info;
564
565 /**************************************************************************/
566
567 FUNCTION validate_source_id_table
568 (p_source_id IN NUMBER,
569 p_source_table IN VARCHAR2)
570 RETURN VARCHAR2
571 AS
572 l_source_table_status VARCHAR2(1);
573 TYPE ref_cursor IS REF CURSOR;
574 source_csr ref_cursor;
575 l_select_string VARCHAR2(500);
576 l_found VARCHAR2(1);
577 BEGIN
578 l_source_table_status := validate_lookup_code
579 (p_lookup_type => 'OKL_SOURCE_TABLE',
580 p_lookup_code => p_source_table,
581 p_app_id => 540,
582 p_view_app_id => 0);
583
584 IF l_source_table_status = 'T'
585 THEN
586 l_select_string := ' SELECT ''1'' FROM ' || p_source_table || ' WHERE id = :l_id ' ;
587
588 EXECUTE IMMEDIATE l_select_string
589 INTO l_found
590 USING p_source_id;
591 ELSE
592 RETURN okl_api.g_false;
593 END IF;
594 RETURN okl_api.g_true;
595 EXCEPTION
596 WHEN OTHERS THEN
597 RETURN okl_api.g_false;
598 END validate_source_id_table;
599
600 /**************************************************************************/
601 --Added p_ledger_id argument as part of bug 5707866 by nikshah
602 --If p_ledger_id passed to the API is null then it considers ledger from primary representation.
603 --Otherwise, it considers the ledger id that is passed to it.
604 --Cursor changed to accept one parameter: l_ledger_id
605
606 PROCEDURE get_set_of_books
607 (p_set_of_books_id OUT NOCOPY NUMBER,
608 p_set_of_books_name OUT NOCOPY VARCHAR2, p_ledger_id IN NUMBER DEFAULT NULL)
609 AS
610 l_ledger_id NUMBER;
611 BEGIN
612 l_ledger_id := p_ledger_id;
613 IF l_ledger_id IS NULL
614 THEN
615 l_ledger_id := get_set_of_books_id;
616 END IF;
617 p_set_of_books_id := l_ledger_id;
618 p_set_of_books_name := get_set_of_books_name(p_set_of_books_id => l_ledger_id);
619 END get_set_of_books;
620
621 /**************************************************************************/
622
623 /* rkuttiya modified on 10-JUl-2008
624 * for Multi GAAP Project added new parameter p_representation_type
625 */
626
627 FUNCTION get_set_of_books_id(p_representation_type IN VARCHAR2 DEFAULT
628 'PRIMARY')
629 RETURN NUMBER
630 IS
631 l_set_of_books_id NUMBER;
632
633 CURSOR set_of_book_id
634 IS
635 SELECT set_of_books_id
636 FROM OKL_SYS_ACCT_OPTS;
637
638 CURSOR c_ledger_id IS
639 SELECT ledger_id
640 FROM okl_representations_v
641 WHERE representation_type = 'SECONDARY';
642
643 BEGIN
644 IF p_representation_type = 'PRIMARY' THEN
645 OPEN set_of_book_id;
646 FETCH set_of_book_id INTO l_set_of_books_id;
647 CLOSE set_of_book_id;
648 ELSIF p_representation_type = 'SECONDARY' THEN
649 OPEN c_ledger_id;
650 FETCH c_ledger_id INTO l_set_of_books_id;
651 CLOSE c_ledger_id;
652 END IF;
653 RETURN l_set_of_books_id;
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 RETURN NULL;
658 END get_set_of_books_id;
659
660 /**************************************************************************/
661
662 FUNCTION get_set_of_books_name(p_set_of_books_id IN NUMBER)
663 RETURN VARCHAR2
664 IS
665 l_set_of_books_name VARCHAR2(30);
666
667 CURSOR set_of_book_name IS
668 SELECT name
669 FROM GL_LEDGERS_PUBLIC_V
670 WHERE ledger_id = p_set_of_books_id;
671
672 BEGIN
673
674 OPEN set_of_book_name;
675 FETCH set_of_book_name INTO l_set_of_books_name;
676 CLOSE set_of_book_name;
677
678 RETURN l_set_of_books_name;
679
680 EXCEPTION
681 WHEN OTHERS THEN
682 RETURN NULL;
683 END get_set_of_books_name;
684
685 /**************************************************************************/
686
687 FUNCTION round_amount
688 (p_amount IN NUMBER,
689 p_currency_code IN VARCHAR2)
690 RETURN NUMBER
691 AS
692 l_rounding_rule VARCHAR2(30);
693 l_precision NUMBER;
694 l_rounded_amount NUMBER := 0;
695 l_pos_dot NUMBER;
696 l_to_add NUMBER := 1;
697 l_sysdate DATE := G_SYSDATE;
698
699 CURSOR ael_csr IS
700 SELECT ael_rounding_rule
701 FROM OKL_SYS_ACCT_OPTS;
702
703 CURSOR prec_csr IS
704 SELECT PRECISION
705 FROM fnd_currencies_vl
706 WHERE currency_code = p_currency_code
707 AND enabled_flag = 'Y'
708 AND TRUNC(NVL(start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
709 AND TRUNC(NVL(end_date_active, l_sysdate)) >= TRUNC(l_sysdate);
710
711 BEGIN
712
713 OPEN ael_csr;
714 FETCH ael_csr INTO l_rounding_rule;
715 CLOSE ael_csr;
716
717 OPEN prec_csr;
718 FETCH prec_csr INTO l_precision;
719 CLOSE prec_csr;
720
721 IF (l_rounding_rule = 'UP') THEN
722 l_pos_dot := INSTR(TO_CHAR(p_amount),'.') ;
723 IF (l_pos_dot > 0) AND (SUBSTR(p_amount,l_pos_dot+l_precision+1,1) IS NOT NULL) THEN
724 FOR i IN 1..l_precision LOOP
725 l_to_add := l_to_add/10;
726 END LOOP;
727 l_rounded_amount := p_amount + l_to_add;
728 ELSE
729 l_rounded_amount := p_amount;
730 END IF;
731 l_rounded_amount := TRUNC(l_rounded_amount,l_precision);
732 ELSIF l_rounding_rule = 'DOWN' THEN
733 l_rounded_amount := TRUNC(p_amount, l_precision);
734
735 ELSIF l_rounding_rule = 'NEAREST' THEN
736 l_rounded_amount := ROUND(p_amount, l_precision);
737 END IF;
738
739 RETURN l_rounded_amount;
740 EXCEPTION
741 WHEN OTHERS THEN
742 RETURN 0;
743 END round_amount;
744
745 /******************************************************************************
746 The Procedure accepts 3 values.
747 Amount
748 Currency Code
749 Round Option(For rounding cross currency pass the value 'CC',for Streams
750 'STM' and for Accounting Lines 'AEL')
751 *******************************************************************************/
752
753 PROCEDURE round_amount
754 (p_api_version IN NUMBER,
755 p_init_msg_list IN VARCHAR2,
756 x_return_status OUT NOCOPY VARCHAR2,
757 x_msg_count OUT NOCOPY NUMBER,
758 x_msg_data OUT NOCOPY VARCHAR2,
759 p_amount IN NUMBER,
760 p_currency_code IN VARCHAR2,
761 p_round_option IN VARCHAR2,
762 x_rounded_amount OUT NOCOPY NUMBER)
763
764 IS
765 l_rounding_rule VARCHAR2(30);
766 l_precision NUMBER;
767 l_rounded_amount NUMBER := 0;
768 l_pos_dot NUMBER;
769 l_to_add NUMBER := 1;
770 l_sysdate DATE := G_SYSDATE;
771
772 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
773 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
774 l_msg_count NUMBER := 0;
775 l_msg_data VARCHAR2(2000);
776 l_api_version NUMBER := 1.0;
777
778 CURSOR round_csr IS
779 SELECT cc_rounding_rule,
780 ael_rounding_rule,
781 stm_rounding_rule
782 FROM OKL_SYS_ACCT_OPTS;
783
784
785 CURSOR prec_csr IS
786 SELECT PRECISION
787 FROM fnd_currencies_vl
788 WHERE currency_code = p_currency_code
789 AND enabled_flag = 'Y'
790 AND TRUNC(NVL(start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
791 AND TRUNC(NVL(end_date_active, l_sysdate)) >= TRUNC(l_sysdate);
792
793 BEGIN
794
795 x_return_status := OKL_API.G_RET_STS_SUCCESS;
796
797 IF (p_round_option ='CC') THEN
798 FOR round_rec IN round_csr LOOP
799 l_rounding_rule := round_rec.cc_rounding_rule;
800 END LOOP;
801 ELSIF (p_round_option ='STM') THEN
802 FOR round_rec IN round_csr LOOP
803 l_rounding_rule := round_rec.stm_rounding_rule;
804 END LOOP;
805 ELSIF (p_round_option = 'AEL') THEN
806 FOR round_rec IN round_csr LOOP
807 l_rounding_rule := round_rec.ael_rounding_rule;
808 END LOOP;
809 ELSE
810 Okc_Api.SET_MESSAGE(p_app_name => g_app_name
811 ,p_msg_name => g_invalid_value
812 ,p_token1 => g_col_name_token
813 ,p_token1_value => 'ROUND_OPTION');
814 x_return_status := OKL_API.G_RET_STS_ERROR;
815 RAISE OKL_API.G_EXCEPTION_ERROR;
816
817 END IF;
818
819
820 IF l_rounding_rule IS NULL THEN
821 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
822 p_msg_name => 'OKL_NO_ROUNDING_RULE');
823 x_return_status := OKL_API.G_RET_STS_ERROR;
824 RAISE OKL_API.G_EXCEPTION_ERROR;
825 END IF;
826
827
828 FOR prec_rec IN prec_csr LOOP
829 l_precision := prec_rec.precision;
830 END LOOP;
831
832 IF l_precision IS NULL THEN
833 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
834 p_msg_name => 'OKL_NO_CURR_PRECISION',
835 p_token1 => 'CURRENCY_CODE',
836 p_token1_value => p_currency_code);
837 x_return_status := OKL_API.G_RET_STS_ERROR;
838 RAISE OKL_API.G_EXCEPTION_ERROR;
839 END IF;
840
841
842 IF (l_rounding_rule = 'UP') THEN
843 l_pos_dot := INSTR(TO_CHAR(p_amount),'.') ;
844 IF (l_pos_dot > 0) AND (SUBSTR(p_amount,l_pos_dot+l_precision+1,1) IS NOT NULL) THEN
845 FOR i IN 1..l_precision LOOP
846 l_to_add := l_to_add/10;
847 END LOOP;
848 l_rounded_amount := p_amount + l_to_add;
849 ELSE
850 l_rounded_amount := p_amount;
851 END IF;
852 l_rounded_amount := TRUNC(l_rounded_amount,l_precision);
853 ELSIF l_rounding_rule = 'DOWN' THEN
854 l_rounded_amount := TRUNC(p_amount, l_precision);
855
856 ELSIF l_rounding_rule = 'NEAREST' THEN
857 l_rounded_amount := ROUND(p_amount, l_precision);
858 END IF;
859
860 x_rounded_amount := l_rounded_amount;
861
862 EXCEPTION
863
864 WHEN OKL_API.G_EXCEPTION_ERROR THEN
865 x_return_status := OKL_API.G_RET_STS_ERROR ;
866
867 WHEN OTHERS THEN
868 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
869 p_msg_name => 'OKL_ERROR_ROUNDING_AMT');
870 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
871
872 END round_amount;
873
874
875
876
877
878 /**************************************************************************/
879
880 FUNCTION get_curr_con_rate
881 (p_from_curr_code IN VARCHAR2,
882 p_to_curr_code IN VARCHAR2,
883 p_con_date IN DATE,
884 p_con_type IN VARCHAR2)
885 RETURN NUMBER
886 AS
887 BEGIN
888
889 RETURN (Gl_Currency_Api.get_rate_sql
890 (x_from_currency => p_from_curr_code,
891 x_to_currency => p_to_curr_code,
892 x_conversion_date => p_con_date,
893 x_conversion_type => p_con_type));
894
895 EXCEPTION
896 WHEN OTHERS THEN
897 RETURN 0;
898 END get_curr_con_rate;
899
900 /**************************************************************************/
901
902
903 /*
904 Returns currency conversion rate
905 */
906
907 PROCEDURE get_curr_con_rate
908 (p_api_version IN NUMBER,
909 p_init_msg_list IN VARCHAR2,
910 x_return_status OUT NOCOPY VARCHAR2,
911 x_msg_count OUT NOCOPY NUMBER,
912 x_msg_data OUT NOCOPY VARCHAR2,
913 p_from_curr_code IN VARCHAR2,
914 p_to_curr_code IN VARCHAR2,
915 p_con_date IN DATE,
916 p_con_type IN VARCHAR2,
917 x_conv_rate OUT NOCOPY NUMBER)
918
919 IS
920 l_rate NUMBER;
921 BEGIN
922
923 x_return_status := OKL_API.G_RET_STS_SUCCESS;
924
925 l_rate := Gl_Currency_Api.get_rate_sql
926 (x_from_currency => p_from_curr_code,
927 x_to_currency => p_to_curr_code,
928 x_conversion_date => p_con_date,
929 x_conversion_type => p_con_type);
930
931 IF l_rate = -1 THEN
932 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
933 p_msg_name => 'OKL_NO_RATE_FOR_CONV',
934 p_token1 => 'FROM_CURR',
935 p_token1_value => p_from_curr_code,
936 p_token2 => 'TO_CURR',
937 p_token2_value => p_to_curr_code);
938 x_return_status := OKL_API.G_RET_STS_ERROR;
939 RAISE OKL_API.G_EXCEPTION_ERROR;
940 END IF;
941
942 IF l_rate = -2 THEN
943 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
944 p_msg_name => 'OKL_NO_CURR_FOR_CONV');
945 x_return_status := OKL_API.G_RET_STS_ERROR;
946 RAISE OKL_API.G_EXCEPTION_ERROR;
947 END IF;
948
949 x_conv_rate := l_rate;
950
951 EXCEPTION
952
953 WHEN OKL_API.G_EXCEPTION_ERROR THEN
954 x_return_status := OKL_API.G_RET_STS_ERROR ;
955
956 WHEN OTHERS THEN
957 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
958
959 END get_curr_con_rate;
960
961 /**************************************************************************/
962
963 /* rkuttiya modified 10-Jul-2008
964 * Multi GAAP Project added new parametr p_ledger_id
965 */
966
967 PROCEDURE get_accounting_segment
968 (p_segment_array OUT NOCOPY seg_num_name_type,
969 p_ledger_id IN NUMBER DEFAULT NULL)
970 AS
971 l_chart_of_accounts_id NUMBER;
972
973 CURSOR coa_csr(p_ledger_id IN NUMBER) IS
974 SELECT chart_of_accounts_id
975 FROM GL_LEDGERS_PUBLIC_V
976 WHERE ledger_id = NVL(p_ledger_id,get_set_of_books_id);
977
978 CURSOR seg_csr (l_chart_of_accounts_id NUMBER) IS
979 SELECT segment_num,
980 application_column_name,
981 form_left_prompt
982 FROM fnd_id_flex_segments_vl
983 WHERE application_id = 101
984 AND id_flex_code = 'GL#'
985 AND enabled_flag = 'Y'
986 AND id_flex_num = l_chart_of_accounts_id;
987
988 BEGIN
989
990 OPEN coa_csr(p_ledger_id);
991 FETCH coa_csr INTO l_chart_of_accounts_id;
992 CLOSE coa_csr;
993
994 OPEN seg_csr (l_chart_of_accounts_id);
995 FETCH seg_csr BULK COLLECT INTO p_segment_array.seg_num,
996 p_segment_array.seg_name,
997 p_segment_array.seg_desc;
998 CLOSE seg_csr;
999
1000 EXCEPTION
1001 WHEN OTHERS THEN
1002 NULL;
1003 END get_accounting_segment;
1004
1005 /* rkuttiya modified 10-Jul-2008
1006 * Multi GAAP Project added new parameter p_ledgeR_id
1007 */
1008
1009 FUNCTION get_segment_desc(p_segment IN VARCHAR2,
1010 p_ledger_id IN NUMBER DEFAULT NULL)
1011 RETURN VARCHAR2
1012 AS
1013
1014 l_chart_of_accounts_id NUMBER;
1015 l_segment_desc FND_ID_FLEX_SEGMENTS_VL.FORM_LEFT_PROMPT%TYPE;
1016
1017 CURSOR coa_csr(p_ledger_id IN NUMBER) IS
1018 SELECT chart_of_accounts_id
1019 FROM GL_LEDGERS_PUBLIC_V
1020 WHERE ledger_id = NVL(p_ledger_id,get_set_of_books_id);
1021
1022 CURSOR seg_csr (l_chart_of_accounts_id NUMBER) IS
1023 SELECT form_left_prompt
1024 FROM fnd_id_flex_segments_vl
1025 WHERE application_id = 101
1026 AND id_flex_code = 'GL#'
1027 AND enabled_flag = 'Y'
1028 AND id_flex_num = l_chart_of_accounts_id
1029 AND application_column_name = p_segment;
1030
1031 BEGIN
1032
1033 OPEN coa_csr(p_ledger_id);
1034 FETCH coa_csr INTO l_chart_of_accounts_id;
1035 CLOSE coa_csr;
1036
1037 OPEN seg_csr (l_chart_of_accounts_id);
1038 FETCH seg_csr INTO l_segment_desc;
1039 CLOSE seg_csr;
1040
1041 RETURN (l_segment_desc);
1042
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 RETURN NULL;
1046
1047 END get_segment_desc;
1048
1049 /**************************************************************************/
1050
1051 FUNCTION get_lookup_meaning
1052 (p_lookup_type IN VARCHAR2,
1053 p_lookup_code IN VARCHAR2,
1054 p_app_id IN NUMBER DEFAULT 540,
1055 p_view_app_id IN NUMBER DEFAULT 0)
1056 RETURN VARCHAR2
1057 AS
1058 l_meaning VARCHAR2(240);
1059 l_sysdate DATE := G_SYSDATE;
1060
1061 CURSOR lkup_csr IS
1062 SELECT meaning
1063 FROM fnd_lookup_values flv,
1064 fnd_lookup_types flt
1065 WHERE
1066 flv.lookup_type = p_lookup_type
1067 AND flv.view_application_id = p_view_app_id
1068 AND flv.lookup_code = p_lookup_code
1069 AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id)
1070 AND flv.LANGUAGE = USERENV('LANG')
1071 AND flv.enabled_flag = 'Y'
1072 AND TRUNC(NVL(flv.start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
1073 AND TRUNC(NVL(flv.end_date_active, l_sysdate)) >= TRUNC(l_sysdate)
1074 AND flv.lookup_type = flt.lookup_type
1075 AND flv.view_application_id = flt.view_application_id
1076 AND flv.security_group_id = flt.security_group_id
1077 AND flt.application_id = p_app_id;
1078
1079 BEGIN
1080
1081 OPEN lkup_csr;
1082 FETCH lkup_csr INTO l_meaning;
1083 CLOSE lkup_csr;
1084
1085 RETURN l_meaning;
1086
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 RETURN NULL;
1090 END get_lookup_meaning;
1091
1092 /**************************************************************************/
1093
1094 FUNCTION get_lookup_meaning_lang
1095 (p_lookup_type IN VARCHAR2,
1096 p_lookup_code IN VARCHAR2,
1097 p_app_id IN NUMBER DEFAULT 540,
1098 p_view_app_id IN NUMBER DEFAULT 0,
1099 p_language IN VARCHAR2 DEFAULT USERENV('LANG'))
1100 RETURN VARCHAR2
1101 AS
1102 l_meaning VARCHAR2(240);
1103 l_sysdate DATE := G_SYSDATE;
1104
1105 CURSOR lkup_csr IS
1106 SELECT meaning
1107 FROM fnd_lookup_values flv,
1108 fnd_lookup_types flt
1109 WHERE
1110 flv.lookup_type = p_lookup_type
1111 AND flv.view_application_id = p_view_app_id
1112 AND flv.lookup_code = p_lookup_code
1113 AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id)
1114 AND flv.LANGUAGE = p_language
1115 AND flv.enabled_flag = 'Y'
1116 AND TRUNC(NVL(flv.start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
1117 AND TRUNC(NVL(flv.end_date_active, l_sysdate)) >= TRUNC(l_sysdate)
1118 AND flv.lookup_type = flt.lookup_type
1119 AND flv.view_application_id = flt.view_application_id
1120 AND flv.security_group_id = flt.security_group_id
1121 AND flt.application_id = p_app_id;
1122
1123 BEGIN
1124
1125 OPEN lkup_csr;
1126 FETCH lkup_csr INTO l_meaning;
1127 CLOSE lkup_csr;
1128
1129 RETURN l_meaning;
1130
1131 EXCEPTION
1132 WHEN OTHERS THEN
1133 RETURN NULL;
1134 END get_lookup_meaning_lang;
1135
1136 /**************************************************************************/
1137
1138 FUNCTION validate_currency_con_type
1139 (p_currency_con_type IN VARCHAR2)
1140 RETURN VARCHAR2
1141 AS
1142 l_return VARCHAR2(1);
1143
1144 CURSOR curr_csr IS
1145 SELECT '1'
1146 FROM gl_daily_conversion_types
1147 WHERE conversion_type = p_currency_con_type;
1148
1149 BEGIN
1150
1151 OPEN curr_csr;
1152 FETCH curr_csr INTO l_return;
1153 CLOSE curr_csr;
1154
1155 IF l_return IS NOT NULL THEN
1156 RETURN okl_api.g_true;
1157 ELSE
1158 RETURN okl_api.g_false;
1159 END IF;
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 RETURN okl_api.g_false;
1164 END validate_currency_con_type;
1165
1166 /**************************************************************************/
1167 --Added p_ledger_id argument as part of bug 5707866 by nikshah
1168 --If p_ledger_id passed to the API is null then it considers ledger from primary representation.
1169 --Otherwise, it considers the ledger id that is passed to it.
1170 --Cursor changed to accept one parameter: l_ledger_id
1171 FUNCTION get_func_curr_code (p_ledger_id IN NUMBER DEFAULT NULL)
1172 RETURN VARCHAR2 IS
1173 l_currency_code VARCHAR2(15);
1174 l_ledger_id NUMBER;
1175
1176 CURSOR curr_csr (l_ledger_id NUMBER) IS
1177 SELECT currency_code
1178 FROM GL_LEDGERS_PUBLIC_V
1179 WHERE ledger_id = l_ledger_id;
1180
1181 BEGIN
1182 l_ledger_id := p_ledger_id;
1183 IF l_ledger_id IS NULL
1184 THEN
1185 l_ledger_id := get_set_of_books_id;
1186 END IF;
1187
1188 OPEN curr_csr(l_ledger_id);
1189 FETCH curr_csr INTO l_currency_code;
1190 CLOSE curr_csr;
1191
1192 RETURN l_currency_code;
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 RETURN NULL;
1196 END get_func_curr_code;
1197
1198 /*************************************************************************/
1199 FUNCTION validate_journal_category(p_category IN VARCHAR2)
1200 RETURN VARCHAR2
1201 IS
1202 l_found VARCHAR2(1);
1203
1204 CURSOR cat_csr IS
1205 SELECT '1'
1206 FROM gl_je_categories
1207 WHERE je_category_name = p_category;
1208
1209 BEGIN
1210
1211 OPEN cat_csr;
1212 FETCH cat_csr INTO l_found;
1213 CLOSE cat_csr;
1214
1215 IF l_found IS NOT NULL THEN
1216 RETURN okl_api.g_true;
1217 ELSE
1218 RETURN okl_api.g_false;
1219 END IF;
1220
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 RETURN okl_api.g_false;
1224 END validate_journal_category;
1225
1226 /*************************************************************************/
1227 --Added p_ledger_id argument as part of bug 5707866 by nikshah
1228 --If p_ledger_id passed to the API is null then it considers ledger from primary representation.
1229 --Otherwise, it considers the ledger id that is passed to it.
1230 --Cursor changed to accept one parameter: l_ledger_id
1231
1232 FUNCTION get_chart_of_accounts_id (p_ledger_id IN NUMBER DEFAULT NULL)
1233 RETURN NUMBER
1234 AS
1235 l_chart_of_accounts_id NUMBER;
1236 l_ledger_id NUMBER;
1237
1238 CURSOR coa_csr (l_ledger_id NUMBER) IS
1239 SELECT chart_of_accounts_id
1240 FROM GL_LEDGERS_PUBLIC_V
1241 WHERE ledger_id = l_ledger_id;
1242
1243 BEGIN
1244 l_ledger_id := p_ledger_id;
1245 IF l_ledger_id IS NULL
1246 THEN
1247 l_ledger_id := get_set_of_books_id;
1248 END IF;
1249
1250 OPEN coa_csr(l_ledger_id);
1251 FETCH coa_csr INTO l_chart_of_accounts_id;
1252 CLOSE coa_csr;
1253
1254 RETURN l_chart_of_accounts_id;
1255
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258 RETURN -1;
1259 END get_chart_of_accounts_id;
1260
1261
1262 -- mvasudev , 9/25/01
1263 ---------------------------------------------------------------------------
1264 -- PROCEDURE check_overlaps
1265 -- To avoid overlapping of dates with other versions of the same attribute-value
1266 -- Applicable with any given attribute and its value
1267 ---------------------------------------------------------------------------
1268 PROCEDURE check_overlaps (
1269 p_id IN NUMBER,
1270 p_attrib_tbl IN overlap_attrib_tbl_type,
1271 p_start_date_attribute_name IN VARCHAR2 DEFAULT 'START_DATE',
1272 p_start_date IN DATE,
1273 p_end_date_attribute_name IN VARCHAR2 DEFAULT 'END_DATE',
1274 p_end_date IN DATE,
1275 p_view IN VARCHAR2,
1276 x_return_status OUT NOCOPY VARCHAR2,
1277 x_valid OUT NOCOPY BOOLEAN)
1278 IS
1279
1280 TYPE GenericCurTyp IS REF CURSOR;
1281 okl_all_overlaps_csr GenericCurTyp;
1282 l_where_clause VARCHAR2(500) := '';
1283 i INTEGER := 0;
1284 l_apostrophe VARCHAR2(5) := '';
1285 l_sql_stmt VARCHAR2(1000);
1286 l_check VARCHAR2(1) := '?';
1287 l_row_found BOOLEAN := FALSE;
1288 l_col_names VARCHAR2(50);
1289 l_start_date DATE;
1290 l_end_date DATE;
1291
1292 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
1293 G_VERSION_OVERLAPS CONSTANT VARCHAR2(200) := 'OKL_VERSION_OVERLAPS';
1294 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLERRM';
1295 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLCODE';
1296 G_TABLE_TOKEN CONSTANT VARCHAR2(100) := 'OKL_TABLE_NAME';
1297 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(100) := OKL_API.G_PARENT_TABLE_TOKEN;
1298 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(100) := OKL_API.G_CHILD_TABLE_TOKEN;
1299 G_COL_NAME_TOKEN CONSTANT VARCHAR2(100) := OKL_API.G_COL_NAME_TOKEN;
1300
1301 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
1302
1303 FUNCTION format_date(p_date DATE) RETURN VARCHAR2
1304 IS
1305 l_str VARCHAR2(200);
1306 BEGIN
1307
1308 -- Changed the format mask from DD-MM-RRRR to DD/MM/RRRR
1309 l_str := 'TO_DATE(' || '''' || TO_CHAR(p_date,'DD/MM/RRRR') || '''' || ',(' || '''' || 'DD/MM/RRRR' || '''' || '))';
1310 RETURN l_str;
1311 END;
1312
1313
1314
1315
1316 BEGIN
1317 x_valid := TRUE;
1318 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1319
1320 l_start_date := p_start_date;
1321 l_end_date := p_end_date;
1322 IF l_end_date IS NULL
1323 THEN l_end_date := G_FINAL_DATE;
1324 END IF;
1325
1326 i := p_attrib_tbl.FIRST;
1327 LOOP
1328
1329 IF p_attrib_tbl(i).attrib_type = G_VARCHAR2
1330 THEN
1331 l_apostrophe := '''';
1332 ELSIF p_attrib_tbl(i).attrib_type = G_NUMBER
1333 THEN
1334 l_apostrophe := '';
1335 END IF;
1336
1337 IF LENGTH(l_where_clause) > 0 THEN
1338 l_col_names := ', ';
1339 END IF;
1340 l_col_names := l_col_names || p_attrib_tbl(i).attribute;
1341
1342 l_where_clause := l_where_clause || ' AND ' || p_attrib_tbl(i).attribute || '=' || l_apostrophe || p_attrib_tbl(i).value || l_apostrophe ;
1343
1344 EXIT WHEN (i = p_attrib_tbl.LAST);
1345 i := p_attrib_tbl.NEXT(i);
1346 END LOOP;
1347
1348 -- Check for overlaps
1349 l_sql_stmt := 'SELECT ''1'' ' ||
1350 'FROM ' || p_view ||
1351 ' WHERE id <> ' || p_id ||
1352 l_where_clause ||
1353 ' AND ( ' || format_date(l_start_date ) ||
1354 ' BETWEEN ' || p_start_date_attribute_name || ' AND ' ||
1355 ' NVL(' || p_end_date_attribute_name || ',' || format_date(g_final_date) || ') OR '
1356 || format_date(l_end_date) ||
1357 ' BETWEEN ' || p_start_date_attribute_name || ' AND ' ||
1358 ' NVL(' || p_end_date_attribute_name || ', ' || format_date(g_final_date) || ')) ' ||
1359 ' UNION ALL ' ||
1360 'SELECT ''2'' ' ||
1361 'FROM ' || p_view ||
1362 ' WHERE id <> ' || p_id ||
1363 l_where_clause ||
1364 ' AND ' || format_date(l_start_date ) ||
1365 ' <= ' || p_start_date_attribute_name ||
1366 ' AND ' || format_date(l_end_date ) ||
1367 ' >= NVL(' || p_end_date_attribute_name || ', ' || format_date(g_final_date) || ') ';
1368
1369 OPEN okl_all_overlaps_csr
1370 FOR l_sql_stmt;
1371 FETCH okl_all_overlaps_csr INTO l_check;
1372 l_row_found := okl_all_overlaps_csr%FOUND;
1373 CLOSE okl_all_overlaps_csr;
1374
1375 IF l_row_found = TRUE THEN
1376 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1377 p_msg_name => G_VERSION_OVERLAPS,
1378 p_token1 => G_TABLE_TOKEN,
1379 p_token1_value => p_view,
1380 p_token2 => G_COL_NAME_TOKEN,
1381 p_token2_value => l_col_names);
1382 x_valid := FALSE;
1383 x_return_status := OKL_API.G_RET_STS_ERROR;
1384 END IF;
1385
1386
1387 EXCEPTION
1388 WHEN OTHERS THEN
1389 -- store SQL error message on message stack
1390 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1391 p_msg_name => G_UNEXPECTED_ERROR,
1392 p_token1 => G_SQLCODE_TOKEN,
1393 p_token1_value => SQLCODE,
1394 p_token2 => G_SQLERRM_TOKEN,
1395 p_token2_value => SQLERRM);
1396 x_valid := FALSE;
1397 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1398
1399 IF (okl_all_overlaps_csr%ISOPEN) THEN
1400 CLOSE okl_all_overlaps_csr;
1401 END IF;
1402
1403
1404 END check_overlaps;
1405 -- mvasudev, end
1406
1407
1408 -- mvasudev, 10/02/01
1409 ---------------------------------------------------------------------------
1410 -- PROCEDURE get_version to calculate the new version number for the
1411 -- entity to be created - with any attribute and its value to check with
1412 ---------------------------------------------------------------------------
1413 PROCEDURE get_version(
1414 p_attrib_tbl IN overlap_attrib_tbl_type,
1415 p_cur_version IN VARCHAR2,
1416 p_end_date_attribute_name IN VARCHAR2 DEFAULT 'END_DATE',
1417 p_end_date IN DATE,
1418 p_view IN VARCHAR2,
1419 x_return_status OUT NOCOPY VARCHAR2,
1420 x_new_version OUT NOCOPY VARCHAR2) IS
1421
1422 TYPE GenericCurTyp IS REF CURSOR;
1423 okl_all_laterversionsexist_csr GenericCurTyp;
1424 l_where_clause VARCHAR2(500) := '';
1425 i INTEGER := 0;
1426 l_apostrophe VARCHAR2(5) := '';
1427 l_and VARCHAR2(10) := ' WHERE ';
1428 l_sql_stmt VARCHAR2(1000);
1429 l_check VARCHAR2(1) := '?';
1430 l_row_not_found BOOLEAN := FALSE;
1431
1432
1433 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
1434 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLERRM';
1435 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLCODE';
1436 G_TABLE_TOKEN CONSTANT VARCHAR2(100) := 'OKL_TABLE_NAME';
1437 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(100) := OKL_API.G_PARENT_TABLE_TOKEN;
1438 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(100) := OKL_API.G_CHILD_TABLE_TOKEN;
1439 G_COL_NAME_TOKEN CONSTANT VARCHAR2(100) := OKL_API.G_COL_NAME_TOKEN;
1440
1441 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
1442
1443 G_INIT_VERSION CONSTANT NUMBER := 1.0;
1444 G_VERSION_MAJOR_INCREMENT CONSTANT NUMBER := 1.0;
1445 G_VERSION_MINOR_INCREMENT CONSTANT NUMBER := 0.1;
1446 G_VERSION_FORMAT CONSTANT VARCHAR2(100) := 'FM999.0999';
1447
1448
1449 BEGIN
1450 IF p_cur_version = OKL_API.G_MISS_CHAR THEN
1451 x_new_version := G_INIT_VERSION;
1452 ELSE
1453 i := p_attrib_tbl.FIRST;
1454 LOOP
1455 IF p_attrib_tbl(i).attrib_type = G_VARCHAR2
1456 THEN
1457 l_apostrophe := '''';
1458 ELSIF p_attrib_tbl(i).attrib_type = G_NUMBER
1459 THEN
1460 l_apostrophe := '';
1461 END IF;
1462
1463 IF LENGTH(l_where_clause) > 0 THEN
1464 l_and := ' AND ';
1465 ELSE
1466 l_and := ' WHERE ';
1467 END IF;
1468
1469 l_where_clause := l_where_clause || l_and || p_attrib_tbl(i).attribute || '=' || l_apostrophe || p_attrib_tbl(i).value || l_apostrophe ;
1470
1471 EXIT WHEN (i = p_attrib_tbl.LAST);
1472 i := p_attrib_tbl.NEXT(i);
1473 END LOOP;
1474 -- Check for future versions of the same pricing template
1475 l_sql_stmt := 'SELECT ''1'' ' ||
1476 'FROM ' || p_view ||
1477 l_where_clause ||
1478 ' AND NVL(' || p_end_date_attribute_name || ', ' ||
1479 '''' || OKL_API.G_MISS_DATE || '''' || ') > ' ||
1480 '''' || p_end_date || '''';
1481
1482 OPEN okl_all_laterversionsexist_csr
1483 FOR l_sql_stmt;
1484 FETCH okl_all_laterversionsexist_csr INTO l_check;
1485 l_row_not_found := okl_all_laterversionsexist_csr%NOTFOUND;
1486 CLOSE okl_all_laterversionsexist_csr;
1487
1488 IF l_row_not_found = TRUE THEN
1489 x_new_version := TO_CHAR(TO_NUMBER(p_cur_version, G_VERSION_FORMAT)
1490 + G_VERSION_MAJOR_INCREMENT, G_VERSION_FORMAT);
1491 ELSE
1492 x_new_version := TO_CHAR(TO_NUMBER(p_cur_version, G_VERSION_FORMAT)
1493 + G_VERSION_MINOR_INCREMENT, G_VERSION_FORMAT);
1494 END IF;
1495 END IF;
1496
1497 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1498 EXCEPTION
1499 WHEN OTHERS THEN
1500 -- store SQL error message on message stack
1501 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1502 p_msg_name => G_UNEXPECTED_ERROR,
1503 p_token1 => G_SQLCODE_TOKEN,
1504 p_token1_value => SQLCODE,
1505 p_token2 => G_SQLERRM_TOKEN,
1506 p_token2_value => SQLERRM);
1507 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1508
1509 IF (okl_all_laterversionsexist_csr%ISOPEN) THEN
1510 CLOSE okl_all_laterversionsexist_csr;
1511 END IF;
1512
1513 END get_version;
1514 -- mvasudev, end
1515
1516 ---------------------------------------------------------------------------
1517 -- PROCEDURE okl_upper to convert a string in upper
1518 ---------------------------------------------------------------------------
1519
1520 FUNCTION okl_upper(p_string IN VARCHAR2)
1521 RETURN VARCHAR2
1522 IS
1523 BEGIN
1524 IF USERENV('LANG') IN ('US', 'UK')
1525 THEN
1526 RETURN UPPER(p_string);
1527 ELSE
1528 RETURN p_string;
1529 END IF;
1530 END okl_upper;
1531
1532 ---------------------------------------------------------------------------
1533 -- PROCEDURE get_concate_segments to get the concatenated segment values
1534 -- based on CCID.
1535 ---------------------------------------------------------------------------
1536
1537 FUNCTION get_concat_segments(p_ccid IN NUMBER)
1538 RETURN VARCHAR2
1539 AS
1540 l_concatenated_segments VARCHAR2(1000);
1541
1542 CURSOR ccid_csr IS
1543 SELECT concatenated_segments
1544 FROM gl_code_combinations_kfv
1545 WHERE code_combination_id = p_ccid;
1546
1547 BEGIN
1548
1549 OPEN ccid_csr;
1550 FETCH ccid_csr INTO l_concatenated_segments;
1551 CLOSE ccid_csr;
1552
1553 RETURN l_concatenated_segments;
1554 EXCEPTION
1555 WHEN OTHERS THEN
1556 RETURN NULL;
1557 END get_concat_segments;
1558
1559 ---------------------------------------------------------------------------
1560 -- PROCEDURE get_concate_desc to get the concatenated segment values
1561 -- based on CCID.
1562 ---------------------------------------------------------------------------
1563 FUNCTION get_concate_desc(p_code_combination_id IN NUMBER)
1564 RETURN VARCHAR2
1565 AS
1566 l_concatenated_segments VARCHAR2(100);
1567 l_chart_of_accounts_id NUMBER;
1568 --Bug# 12336692
1569 l_concate_desc VARCHAR2(2000);
1570
1571 CURSOR ccid_csr IS
1572 SELECT concatenated_segments, chart_of_accounts_id
1573 FROM gl_code_combinations_kfv
1574 WHERE code_combination_id = p_code_combination_id;
1575
1576 BEGIN
1577 OPEN ccid_csr;
1578 FETCH ccid_csr INTO l_concatenated_segments, l_chart_of_accounts_id;
1579 CLOSE ccid_csr;
1580
1581 IF (l_concatenated_segments IS NOT NULL) AND
1582 (l_chart_of_accounts_id IS NOT NULL) THEN
1583 l_concate_desc := get_concate_desc
1584 (p_chart_of_account_id => l_chart_of_accounts_id,
1585 p_concate_segments => l_concatenated_segments);
1586 END IF;
1587
1588 RETURN l_concate_desc;
1589 EXCEPTION
1590 WHEN OTHERS THEN
1591 RETURN NULL;
1592 END get_concate_desc;
1593
1594
1595 ---------------------------------------------------------------------------
1596 -- get lookup meaning from fa lookup tables.
1597 ---------------------------------------------------------------------------
1598
1599 FUNCTION get_fa_lookup_meaning(p_lookup_type IN VARCHAR2
1600 ,p_lookup_code IN VARCHAR2)
1601 RETURN VARCHAR2
1602 AS
1603 l_meaning VARCHAR2(240);
1604 l_sysdate DATE := G_SYSDATE;
1605
1606 CURSOR fa_lkup_csr IS
1607 SELECT meaning
1608 FROM fa_lookups fal
1609 WHERE fal.lookup_type = p_lookup_type
1610 AND fal.lookup_code = p_lookup_code
1611 AND fal.enabled_flag = 'Y'
1612 AND TRUNC(NVL(fal.start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
1613 AND TRUNC(NVL(fal.end_date_active, l_sysdate)) >= TRUNC(l_sysdate);
1614
1615 BEGIN
1616
1617 OPEN fa_lkup_csr;
1618 FETCH fa_lkup_csr INTO l_meaning;
1619 CLOSE fa_lkup_csr;
1620
1621 RETURN l_meaning;
1622
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 RETURN NULL;
1626 END get_fa_lookup_meaning;
1627
1628 ---------------------------------------------------------------------------
1629 -- get the format mask for given currency code with profile options.
1630 ---------------------------------------------------------------------------
1631
1632 FUNCTION get_format_mask(p_currency_code IN VARCHAR2)
1633 RETURN VARCHAR2
1634 AS
1635 l_format_mask VARCHAR2(1000);
1636 l_field_width NUMBER := 60;
1637 l_precision NUMBER := 0;
1638 l_ext_precision NUMBER := 0;
1639 l_min_acct_unit NUMBER := 0;
1640 l_curr_separator BOOLEAN;
1641 l_curr_neg_format VARCHAR2(30);
1642 l_curr_pos_format VARCHAR2(30);
1643 l_mask VARCHAR2(100);
1644 l_whole_width NUMBER;
1645 l_decimal_width NUMBER;
1646 l_sign_width NUMBER;
1647 l_profl_val VARCHAR2(80);
1648
1649 CURSOR cur_csr IS
1650 SELECT fc.precision, fc.extended_precision, fc.minimum_accountable_unit
1651 FROM fnd_currencies fc
1652 WHERE fc.currency_code = p_currency_code;
1653
1654 BEGIN
1655
1656 OPEN cur_csr;
1657 FETCH cur_csr INTO l_precision, l_ext_precision, l_min_acct_unit;
1658 IF cur_csr%NOTFOUND THEN
1659 l_PRECISION := 0;
1660 l_ext_precision := 0;
1661 l_min_acct_unit := 0;
1662 END IF;
1663 CLOSE cur_csr;
1664
1665 IF (fnd_profile.value('CURRENCY:THOUSANDS_SEPARATOR') = 'Y' ) THEN
1666 l_curr_separator := TRUE;
1667 ELSE
1668 l_curr_separator := FALSE;
1669 END IF;
1670 l_curr_neg_format := OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('CURRENCY:NEGATIVE_FORMAT', fnd_profile.value('CURRENCY:NEGATIVE_FORMAT'), 0, 0);
1671 l_curr_pos_format := OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('CURRENCY:POSITIVE_FORMAT', fnd_profile.value('CURRENCY:POSITIVE_FORMAT'), 0, 0);
1672
1673 IF (L_PRECISION > 0) THEN
1674 l_decimal_width := 1 + l_PRECISION;
1675 ELSE
1676 l_decimal_width := 0;
1677 END IF;
1678
1679 IF (l_curr_neg_format = '<XXX>') THEN
1680 l_sign_width := 2;
1681 ELSE
1682 l_sign_width := 1;
1683 END IF;
1684
1685 l_whole_width := l_field_width - l_decimal_width - l_sign_width - 1;
1686
1687 IF (l_whole_width < 0) THEN
1688 l_format_mask := '';
1689 END IF;
1690
1691 l_mask := '0' || l_mask;
1692
1693 IF (l_whole_width > 1) THEN
1694 FOR i IN 2..l_whole_width LOOP
1695 IF (l_curr_separator) AND (MOD(i, 4) = 0) THEN
1696 IF (i < l_whole_width - 1) THEN
1697 l_mask := 'G' || l_mask;
1698 END IF;
1699 ELSIF (i <> l_whole_width) THEN
1700 l_mask := '9' || l_mask;
1701 END IF;
1702 END LOOP;
1703 END IF;
1704
1705 IF (l_PRECISION > 0) THEN
1706 l_mask := l_mask || 'D';
1707 FOR i IN 1..l_PRECISION LOOP
1708 l_mask := l_mask || '0';
1709 END LOOP;
1710 END IF;
1711
1712 l_mask := 'FM' || l_mask;
1713
1714 IF (l_curr_neg_format = 'XXX-') THEN
1715 l_mask := l_mask || 'MI';
1716 ELSIF (l_curr_neg_format = '<XXX>') THEN
1717 l_mask := l_mask || 'PR';
1718 ELSIF (l_curr_pos_format = '+XXX') THEN
1719 l_mask := 'S' || l_mask;
1720 END IF;
1721
1722 l_format_mask := l_mask;
1723 RETURN l_format_mask;
1724
1725 END GET_FORMAT_MASK;
1726
1727 ---------------------------------------------------------------------------
1728 -- format the amount according to profile options and currency code.
1729 ---------------------------------------------------------------------------
1730
1731 FUNCTION format_amount(p_amount IN NUMBER
1732 ,p_currency_code IN VARCHAR2)
1733 RETURN VARCHAR2
1734 AS
1735 l_format_mask VARCHAR2(1000);
1736 BEGIN
1737 l_format_mask := get_format_mask(p_currency_code);
1738 RETURN TO_CHAR(p_amount, l_format_mask);
1739 END format_amount;
1740
1741 ---------------------------------------------------------------------------
1742 -- validate amount accoridng to currency code.
1743 ---------------------------------------------------------------------------
1744
1745 FUNCTION validate_amount(p_amount IN NUMBER
1746 ,p_currency_code IN VARCHAR2)
1747 RETURN NUMBER
1748 AS
1749 l_precision NUMBER;
1750 BEGIN
1751 BEGIN -- get currency info
1752 SELECT PRECISION
1753 INTO l_precision
1754 FROM fnd_currencies fc
1755 WHERE fc.currency_code = p_currency_code;
1756 EXCEPTION
1757 WHEN NO_DATA_FOUND THEN
1758 l_precision := 0;
1759 END; -- get currency info
1760
1761 RETURN ROUND(p_amount, l_precision);
1762 END validate_amount;
1763
1764
1765 ---------------------------------------------------------------------------
1766 -- validate get_rule_meaning.
1767 ---------------------------------------------------------------------------
1768 FUNCTION get_rule_meaning(p_rule_code IN VARCHAR2)
1769 RETURN VARCHAR2
1770 AS
1771 cursor csr_get_rule_meaning(c_rule_code varchar2) IS
1772 SELECT meaning
1773 FROM OKC_RULE_DEFS_V rdef
1774 WHERE rdef.rule_code = c_rule_code;
1775
1776 /* cursor csr_get_rule_meaning(c_rule_code varchar2) IS
1777 SELECT meaning
1778 FROM fnd_lookup_types flt,
1779 fnd_lookup_values flv
1780 WHERE flv.lookup_type = flt.lookup_type
1781 AND flv.security_group_id = flt.security_group_id
1782 AND flv.view_application_id = flt.view_application_id
1783 AND flv.LANGUAGE = USERENV('LANG')
1784 AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id)
1785 AND flt.lookup_type = 'OKC_RULE_DEF'
1786 AND flv.lookup_code = c_rule_code
1787 AND flt.application_id = 510
1788 AND flv.view_application_id = 0
1789 AND ENABLED_FLAG = 'Y'
1790 AND NVL(start_date_active, G_SYSDATE) <= G_SYSDATE
1791 AND NVL(end_date_active, G_SYSDATE) >= G_SYSDATE;*/
1792
1793 l_row_found BOOLEAN := FALSE;
1794 l_meaning VARCHAR2(240);
1795 x_return_status VARCHAR2(2);
1796
1797 BEGIN
1798
1799 OPEN csr_get_rule_meaning(p_rule_code);
1800 FETCH csr_get_rule_meaning INTO l_meaning;
1801 l_row_found := csr_get_rule_meaning%FOUND;
1802 CLOSE csr_get_rule_meaning;
1803
1804 IF l_row_found = FALSE THEN
1805 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1806 p_msg_name => G_RULE_DEF_NOT_FOUND);
1807 x_return_status := OKL_API.G_RET_STS_ERROR;
1808 END IF;
1809
1810 RETURN l_meaning;
1811
1812 EXCEPTION
1813 WHEN OTHERS THEN
1814 -- store SQL error message on message stack
1815 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1816 p_msg_name => G_UNEXPECTED_ERROR,
1817 p_token1 => G_SQLCODE_TOKEN,
1818 p_token1_value => SQLCODE,
1819 p_token2 => G_SQLERRM_TOKEN,
1820 p_token2_value => SQLERRM);
1821 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1822
1823 IF (csr_get_rule_meaning%ISOPEN) THEN
1824 CLOSE csr_get_rule_meaning;
1825 END IF;
1826 END get_rule_meaning;
1827
1828
1829 /* ============================================================================
1830 Added by Santonyr 06/20/2002
1831 Function : Get_Message_Token
1832
1833 Parameters :
1834 p_region_code
1835 p_attribute_code
1836 p_application_id
1837
1838 Description : This Function gets the label for a particular AK attribute belongs
1839 a particular region. This is called at the time of displaying a token
1840 along with the message. It returns NULL if the region or the attribute
1841 is not found.
1842
1843 ============================================================================*/
1844
1845 FUNCTION Get_Message_Token
1846 (
1847 p_region_code IN ak_region_items.region_code%TYPE,
1848 p_attribute_code IN ak_region_items.attribute_code%TYPE,
1849 p_application_id IN fnd_application.application_id%TYPE DEFAULT 540
1850 )
1851 RETURN VARCHAR2
1852 IS
1853
1854 l_attribute_label ak_attributes_tl.attribute_label_long%TYPE := NULL;
1855
1856 -- Cursor which selects the label of an attribute belongs to a region.
1857
1858 CURSOR label_cur IS
1859 SELECT rit.attribute_label_long
1860 FROM ak_region_items ri, ak_region_items_tl rit
1861 WHERE ri.region_code = p_region_code AND
1862 ri.attribute_code = p_attribute_code AND
1863 ri.region_application_id = p_application_id AND
1864 ri.attribute_application_id = p_application_id AND
1865 rit.language = USERENV('LANG')AND
1866 ri.region_code = rit.region_code AND
1867 ri.attribute_code = rit.attribute_code AND
1868 ri.region_application_id = rit.region_application_id AND
1869 ri.attribute_application_id = rit.attribute_application_id ;
1870
1871 BEGIN
1872
1873 -- Open the cursor and fetch the value of the label and return it back.
1874
1875 OPEN label_cur ;
1876 FETCH label_cur INTO l_attribute_label;
1877 CLOSE label_cur;
1878
1879 RETURN l_attribute_label;
1880
1881
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884 IF label_cur%ISOPEN THEN
1885 CLOSE label_cur;
1886 END IF;
1887 RETURN NULL;
1888
1889 END Get_Message_Token;
1890
1891 /* =====================================================================================
1892 Function : cross_currency_round_amount
1893 Added by Santonyr 18-Nov-2002
1894
1895 Parameters :
1896 IN
1897 p_amount
1898 p_currency_code
1899
1900 Description : This function rounds the amount passed to this function according to
1901 the cross currency rounding rule.
1902
1903 ======================================================================================*/
1904
1905 FUNCTION cross_currency_round_amount
1906 (p_amount IN NUMBER,
1907 p_currency_code IN VARCHAR2)
1908 RETURN NUMBER
1909 AS
1910 l_rounding_rule VARCHAR2(30);
1911 l_precision NUMBER;
1912 l_rounded_amount NUMBER := 0;
1913 l_pos_dot NUMBER;
1914 l_to_add NUMBER := 1;
1915 l_sysdate DATE := G_SYSDATE;
1916 BEGIN
1917
1918 SELECT cc_rounding_rule INTO l_rounding_rule
1919 FROM OKL_SYS_ACCT_OPTS;
1920
1921 SELECT PRECISION INTO l_precision
1922 FROM fnd_currencies_vl
1923 WHERE currency_code = p_currency_code
1924 AND enabled_flag = 'Y'
1925 AND TRUNC(NVL(start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
1926 AND TRUNC(NVL(end_date_active, l_sysdate)) >= TRUNC(l_sysdate);
1927
1928 IF (l_rounding_rule = 'UP') THEN
1929 l_pos_dot := INSTR(TO_CHAR(p_amount),'.') ;
1930 IF (l_pos_dot > 0) AND (SUBSTR(p_amount,l_pos_dot+l_precision+1,1) IS NOT NULL) THEN
1931 FOR i IN 1..l_precision LOOP
1932 l_to_add := l_to_add/10;
1933 END LOOP;
1934 l_rounded_amount := p_amount + l_to_add;
1935 ELSE
1936 l_rounded_amount := p_amount;
1937 END IF;
1938 l_rounded_amount := TRUNC(l_rounded_amount,l_precision);
1939 ELSIF l_rounding_rule = 'DOWN' THEN
1940 l_rounded_amount := TRUNC(p_amount, l_precision);
1941
1942 ELSIF l_rounding_rule = 'NEAREST' THEN
1943 l_rounded_amount := ROUND(p_amount, l_precision);
1944 END IF;
1945
1946 RETURN l_rounded_amount;
1947 EXCEPTION
1948 WHEN OTHERS THEN
1949 RETURN 0;
1950 END cross_currency_round_amount;
1951
1952 /******************************************************************************/
1953
1954 PROCEDURE cross_currency_round_amount
1955 (p_api_version IN NUMBER,
1956 p_init_msg_list IN VARCHAR2,
1957 x_return_status OUT NOCOPY VARCHAR2,
1958 x_msg_count OUT NOCOPY NUMBER,
1959 x_msg_data OUT NOCOPY VARCHAR2,
1960 p_amount IN NUMBER,
1961 p_currency_code IN VARCHAR2,
1962 x_rounded_amount OUT NOCOPY NUMBER)
1963
1964 IS
1965 l_rounding_rule VARCHAR2(30);
1966 l_precision NUMBER;
1967 l_rounded_amount NUMBER := 0;
1968 l_pos_dot NUMBER;
1969 l_to_add NUMBER := 1;
1970 l_sysdate DATE := G_SYSDATE;
1971
1972 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
1973 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1974 l_msg_count NUMBER := 0;
1975 l_msg_data VARCHAR2(2000);
1976 l_api_version NUMBER := 1.0;
1977
1978 CURSOR rnd_rul_csr IS
1979 SELECT cc_rounding_rule
1980 FROM OKL_SYS_ACCT_OPTS;
1981
1982 CURSOR prec_csr IS
1983 SELECT PRECISION
1984 FROM fnd_currencies_vl
1985 WHERE currency_code = p_currency_code
1986 AND enabled_flag = 'Y'
1987 AND TRUNC(NVL(start_date_active, l_sysdate)) <= TRUNC(l_sysdate)
1988 AND TRUNC(NVL(end_date_active, l_sysdate)) >= TRUNC(l_sysdate);
1989
1990 BEGIN
1991
1992 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1993
1994 FOR rnd_rul_rec IN rnd_rul_csr LOOP
1995 l_rounding_rule := rnd_rul_rec.cc_rounding_rule;
1996 END LOOP;
1997
1998 IF l_rounding_rule IS NULL THEN
1999 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2000 p_msg_name => 'OKL_NO_ROUNDING_RULE');
2001 x_return_status := OKL_API.G_RET_STS_ERROR;
2002 RAISE OKL_API.G_EXCEPTION_ERROR;
2003 END IF;
2004
2005
2006 FOR prec_rec IN prec_csr LOOP
2007 l_precision := prec_rec.precision;
2008 END LOOP;
2009
2010
2011 IF l_precision IS NULL THEN
2012 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2013 p_msg_name => 'OKL_NO_CURR_PRECISION',
2014 p_token1 => 'CURRENCY_CODE',
2015 p_token1_value => p_currency_code);
2016 x_return_status := OKL_API.G_RET_STS_ERROR;
2017 RAISE OKL_API.G_EXCEPTION_ERROR;
2018 END IF;
2019
2020
2021 IF (l_rounding_rule = 'UP') THEN
2022 l_pos_dot := INSTR(TO_CHAR(p_amount),'.') ;
2023 IF (l_pos_dot > 0) AND (SUBSTR(p_amount,l_pos_dot+l_precision+1,1) IS NOT NULL) THEN
2024 FOR i IN 1..l_precision LOOP
2025 l_to_add := l_to_add/10;
2026 END LOOP;
2027 l_rounded_amount := p_amount + l_to_add;
2028 ELSE
2029 l_rounded_amount := p_amount;
2030 END IF;
2031 l_rounded_amount := TRUNC(l_rounded_amount,l_precision);
2032 ELSIF l_rounding_rule = 'DOWN' THEN
2033 l_rounded_amount := TRUNC(p_amount, l_precision);
2034
2035 ELSIF l_rounding_rule = 'NEAREST' THEN
2036 l_rounded_amount := ROUND(p_amount, l_precision);
2037 END IF;
2038
2039 x_rounded_amount := l_rounded_amount;
2040
2041 EXCEPTION
2042
2043 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2044 x_return_status := OKL_API.G_RET_STS_ERROR ;
2045
2046 WHEN OTHERS THEN
2047 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2048 p_msg_name => 'OKL_ERROR_ROUNDING_AMT');
2049 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2050
2051 END cross_currency_round_amount;
2052
2053 /******************************************************************************/
2054
2055 /* =====================================================================================
2056 Procedure : convert_to_functional_currency
2057 Added by Santonyr 15-Nov-2002
2058
2059 Parameters :
2060 IN
2061 p_khr_id
2062 p_to_currency
2063 p_transaction_date
2064 p_amount
2065
2066 OUT
2067 x_contract_currency
2068 x_currency_conversion_type
2069 x_currency_conversion_rate
2070 x_currency_conversion_date
2071 x_converted_amount
2072
2073 Description : This procedure converts the amount from contract currency to functional
2074 currency. And then returns the rounded amount. This also returns the
2075 currency conversion factors along.
2076
2077 Assumptions : In this version of the API, we assume that the contract and
2078 the transaction currencies are same.
2079 ======================================================================================*/
2080
2081 PROCEDURE convert_to_functional_currency
2082 (
2083 p_khr_id IN OKC_K_HEADERS_B.id%TYPE,
2084 p_to_currency IN fnd_currencies.currency_code%TYPE,
2085 p_transaction_date IN DATE,
2086 p_amount IN NUMBER,
2087 x_contract_currency OUT NOCOPY OKC_K_HEADERS_B.currency_code%TYPE,
2088 x_currency_conversion_type OUT NOCOPY OKL_K_HEADERS.currency_conversion_type%TYPE,
2089 x_currency_conversion_rate OUT NOCOPY OKL_K_HEADERS.currency_conversion_rate%TYPE,
2090 x_currency_conversion_date OUT NOCOPY OKL_K_HEADERS.currency_conversion_date%TYPE,
2091 x_converted_amount OUT NOCOPY NUMBER
2092 )
2093 AS
2094
2095 -- Cursor to select the currency conversion factors for the contract id passed
2096 CURSOR cntrct_cur IS
2097 SELECT CURRENCY_CODE,
2098 CURRENCY_CONVERSION_TYPE,
2099 CURRENCY_CONVERSION_RATE,
2100 CURRENCY_CONVERSION_DATE
2101 FROM OKL_K_HEADERS_FULL_V
2102 WHERE ID = p_khr_id;
2103
2104 l_func_currency VARCHAR2(15);
2105 l_contract_currency VARCHAR2(15);
2106 l_currency_conversion_type VARCHAR2(30);
2107 l_currency_conversion_rate NUMBER;
2108 l_currency_conversion_date DATE;
2109 l_converted_amount NUMBER;
2110
2111 BEGIN
2112 -- Fetch the currency conversion factors from the contract cursor
2113 FOR cntrct_rec IN cntrct_cur LOOP
2114 l_contract_currency := cntrct_rec.currency_code;
2115 l_currency_conversion_type := cntrct_rec.currency_conversion_type;
2116 l_currency_conversion_rate := cntrct_rec.currency_conversion_rate;
2117 l_currency_conversion_date := cntrct_rec.currency_conversion_date;
2118 END LOOP;
2119
2120 -- Get the functional currency code using Accounting Util API if the passed
2121 -- currency code is null
2122
2123 l_func_currency := p_to_currency;
2124 IF l_func_currency IS NULL THEN
2125 l_func_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
2126 END IF;
2127
2128 -- Currency conversion rate is 1 if both the contract and functional
2129 -- currencies are same
2130 IF l_func_currency = l_contract_currency THEN
2131 l_currency_conversion_rate := 1;
2132 ELSE
2133
2134 -- Get the currency conversion rate using get_curr_con_rate API if the
2135 -- conversion type is not 'USER'
2136
2137 IF UPPER(l_currency_conversion_type) <> 'USER' THEN
2138 l_currency_conversion_date := p_transaction_date;
2139 l_currency_conversion_rate := OKL_ACCOUNTING_UTIL.get_curr_con_rate
2140 (p_from_curr_code => l_contract_currency,
2141 p_to_curr_code => l_func_currency,
2142 p_con_date => l_currency_conversion_date,
2143 p_con_type => l_currency_conversion_type);
2144 END IF; -- The type is not USER
2145
2146 END IF; -- Functional and contract currencies are not same.
2147
2148 -- Calculate the converted amount
2149 l_converted_amount := p_amount * l_currency_conversion_rate;
2150
2151 -- Round the converted amount
2152 -- l_converted_amount := OKL_ACCOUNTING_UTIL.CROSS_CURRENCY_ROUND_AMOUNT(l_converted_amount, l_contract_currency);
2153
2154 -- Populate the OUT parameters.
2155 x_converted_amount := l_converted_amount;
2156 x_contract_currency := l_contract_currency;
2157 x_currency_conversion_type := l_currency_conversion_type;
2158 x_currency_conversion_rate := l_currency_conversion_rate;
2159 x_currency_conversion_date := l_currency_conversion_date;
2160
2161 EXCEPTION
2162 WHEN OTHERS THEN
2163 x_converted_amount := -1;
2164
2165 END convert_to_functional_currency;
2166
2167 /* =====================================================================================
2168 Procedure : convert_to_functional_currency
2169 Added by Santonyr 20-Dec-2002
2170
2171 Parameters :
2172 IN
2173 p_khr_id
2174 p_to_currency
2175 p_transaction_date
2176 p_amount
2177
2178 OUT
2179 x_return_status
2180 x_contract_currency
2181 x_currency_conversion_type
2182 x_currency_conversion_rate
2183 x_currency_conversion_date
2184 x_converted_amount
2185
2186 Description : This overloaded procedure converts the amount from contract currency to
2187 functional currency. And then returns the rounded amount. This also
2188 returns the currency conversion factors and return_status along.
2189 ======================================================================================*/
2190
2191 PROCEDURE convert_to_functional_currency
2192 (
2193 p_khr_id IN OKC_K_HEADERS_B.id%TYPE,
2194 p_to_currency IN fnd_currencies.currency_code%TYPE,
2195 p_transaction_date IN DATE,
2196 p_amount IN NUMBER,
2197 x_return_status OUT NOCOPY VARCHAR2,
2198 x_contract_currency OUT NOCOPY OKC_K_HEADERS_B.currency_code%TYPE,
2199 x_currency_conversion_type OUT NOCOPY OKL_K_HEADERS.currency_conversion_type%TYPE,
2200 x_currency_conversion_rate OUT NOCOPY OKL_K_HEADERS.currency_conversion_rate%TYPE,
2201 x_currency_conversion_date OUT NOCOPY OKL_K_HEADERS.currency_conversion_date%TYPE,
2202 x_converted_amount OUT NOCOPY NUMBER
2203 )
2204 AS
2205
2206 -- Cursor to select the currency conversion factors for the contract id passed
2207 CURSOR cntrct_cur IS
2208 SELECT CURRENCY_CODE,
2209 CURRENCY_CONVERSION_TYPE,
2210 CURRENCY_CONVERSION_RATE,
2211 CURRENCY_CONVERSION_DATE
2212 FROM OKL_K_HEADERS_FULL_V
2213 WHERE ID = p_khr_id;
2214
2215 l_func_currency VARCHAR2(15);
2216 l_contract_currency VARCHAR2(15);
2217 l_currency_conversion_type VARCHAR2(30);
2218 l_currency_conversion_rate NUMBER;
2219 l_currency_conversion_date DATE;
2220 l_converted_amount NUMBER;
2221 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2222 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
2223 l_msg_count NUMBER := 0;
2224 l_msg_data VARCHAR2(2000);
2225 l_api_version NUMBER := 1.0;
2226
2227
2228 BEGIN
2229
2230 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2231
2232 -- Fetch the currency conversion factors from the contract cursor
2233 FOR cntrct_rec IN cntrct_cur LOOP
2234 l_contract_currency := cntrct_rec.currency_code;
2235 l_currency_conversion_type := cntrct_rec.currency_conversion_type;
2236 l_currency_conversion_rate := cntrct_rec.currency_conversion_rate;
2237 l_currency_conversion_date := cntrct_rec.currency_conversion_date;
2238 END LOOP;
2239
2240 IF l_contract_currency IS NULL THEN
2241 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2242 p_msg_name => 'OKL_NO_CONTRACT_FOR_CONV');
2243 x_return_status := OKL_API.G_RET_STS_ERROR;
2244 RAISE OKL_API.G_EXCEPTION_ERROR;
2245 END IF;
2246
2247
2248 -- Get the functional currency code using Accounting Util API if the passed
2249 -- currency code is null
2250
2251 l_func_currency := p_to_currency;
2252 IF l_func_currency IS NULL THEN
2253 l_func_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
2254 END IF;
2255
2256 -- Currency conversion rate is 1 if both the contract and functional
2257 -- currencies are same
2258 IF l_func_currency = l_contract_currency THEN
2259 l_currency_conversion_rate := 1;
2260 ELSE
2261
2262 -- Get the currency conversion rate using get_curr_con_rate API if the
2263 -- conversion type is not 'USER'
2264
2265 IF UPPER(l_currency_conversion_type) <> 'USER' THEN
2266
2267 l_currency_conversion_date := p_transaction_date;
2268
2269 get_curr_con_rate
2270 (p_api_version => l_api_version,
2271 p_init_msg_list => l_init_msg_list,
2272 x_return_status => l_return_status,
2273 x_msg_count => l_msg_count,
2274 x_msg_data => l_msg_data,
2275 p_from_curr_code => l_contract_currency,
2276 p_to_curr_code => l_func_currency,
2277 p_con_date => l_currency_conversion_date,
2278 p_con_type => l_currency_conversion_type,
2279 x_conv_rate => l_currency_conversion_rate );
2280
2281
2282
2283
2284 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2285 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2286 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2287 RAISE OKL_API.G_EXCEPTION_ERROR;
2288 END IF;
2289
2290 END IF; -- The type is not USER
2291
2292 END IF; -- Functional and contract currencies are not same.
2293
2294 -- Calculate the converted amount
2295 l_converted_amount := p_amount * l_currency_conversion_rate;
2296
2297 -- Round the converted amount
2298 -- l_converted_amount := OKL_ACCOUNTING_UTIL.CROSS_CURRENCY_ROUND_AMOUNT(l_converted_amount, l_contract_currency);
2299
2300 -- Populate the OUT parameters.
2301 x_converted_amount := l_converted_amount;
2302 x_contract_currency := l_contract_currency;
2303 x_currency_conversion_type := l_currency_conversion_type;
2304 x_currency_conversion_rate := l_currency_conversion_rate;
2305 x_currency_conversion_date := l_currency_conversion_date;
2306
2307 EXCEPTION
2308
2309 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2310 x_return_status := OKL_API.G_RET_STS_ERROR ;
2311 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2312 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2313 WHEN OTHERS THEN
2314 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2315
2316 END convert_to_functional_currency;
2317
2318
2319 /* ============================================================================
2320 Procedure : convert_to_contract_currency
2321 Added by Santonyr 15-Nov-2002
2322
2323 Parameters :
2324 IN
2325 p_khr_id
2326 p_from_currency
2327 p_transaction_date
2328 p_amount
2329
2330 OUT
2331 x_contract_currency
2332 x_currency_conversion_type
2333 x_currency_conversion_rate
2334 x_currency_conversion_date
2335 x_converted_amount
2336
2337 Description : This procedure converts the amount from functional currency to contract
2338 currency. And then returns the rounded amount. This also returns the
2339 currency conversion factors along.
2340
2341 Assumptions : In this version of the API, we assume that the contract and
2342 the transaction currencies are same.
2343 ============================================================================*/
2344
2345
2346 PROCEDURE convert_to_contract_currency
2347 (
2348 p_khr_id IN OKC_K_HEADERS_B.id%TYPE,
2349 p_from_currency IN fnd_currencies.currency_code%TYPE,
2350 p_transaction_date IN DATE,
2351 p_amount IN NUMBER,
2352 x_contract_currency OUT NOCOPY OKC_K_HEADERS_B.currency_code%TYPE,
2353 x_currency_conversion_type OUT NOCOPY OKL_K_HEADERS.currency_conversion_type%TYPE,
2354 x_currency_conversion_rate OUT NOCOPY OKL_K_HEADERS.currency_conversion_rate%TYPE,
2355 x_currency_conversion_date OUT NOCOPY OKL_K_HEADERS.currency_conversion_date%TYPE,
2356 x_converted_amount OUT NOCOPY NUMBER
2357 )
2358 AS
2359
2360 -- Cursor to select the currency conversion factors for the contract id passed
2361 CURSOR cntrct_cur IS
2362 SELECT CURRENCY_CODE,
2363 CURRENCY_CONVERSION_TYPE,
2364 CURRENCY_CONVERSION_RATE,
2365 CURRENCY_CONVERSION_DATE
2366 FROM OKL_K_HEADERS_FULL_V
2367 WHERE ID = p_khr_id;
2368
2369 l_func_currency VARCHAR2(15);
2370 l_contract_currency VARCHAR2(15);
2371 l_currency_conversion_type VARCHAR2(30);
2372 l_currency_conversion_rate NUMBER;
2373 l_currency_conversion_date DATE;
2374 l_converted_amount NUMBER;
2375
2376 BEGIN
2377
2378 -- Fetch the currency conversion factors from the contract cursor
2379 FOR cntrct_rec IN cntrct_cur LOOP
2380 l_contract_currency := cntrct_rec.currency_code;
2381 l_currency_conversion_type := cntrct_rec.currency_conversion_type;
2382 l_currency_conversion_rate := cntrct_rec.currency_conversion_rate;
2383 l_currency_conversion_date := cntrct_rec.currency_conversion_date;
2384 END LOOP;
2385
2386 -- Get the functional currency code using Accounting Util API if the passed
2387 -- currency code is null
2388 l_func_currency := p_from_currency;
2389 IF l_func_currency IS NULL THEN
2390 l_func_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
2391 END IF;
2392
2393 -- Currency conversion rate is 1 if both the contract and functional
2394 -- currencies are same
2395
2396 IF l_func_currency = l_contract_currency THEN
2397 l_currency_conversion_rate := 1;
2398
2399 ELSE
2400
2401 IF UPPER(l_currency_conversion_type) = 'USER' THEN
2402 l_currency_conversion_rate := 1/l_currency_conversion_rate;
2403
2404 ELSE
2405
2406 -- Get the currency conversion rate using get_curr_con_rate API if the
2407 -- conversion type is not 'USER'
2408
2409 l_currency_conversion_date := p_transaction_date;
2410 l_currency_conversion_rate := OKL_ACCOUNTING_UTIL.get_curr_con_rate
2411 (p_from_curr_code => l_func_currency,
2412 p_to_curr_code => l_contract_currency,
2413 p_con_date => l_currency_conversion_date,
2414 p_con_type => l_currency_conversion_type);
2415
2416 END IF; -- The type is USER
2417 END IF; -- Functional and contract currencies are not same.
2418
2419
2420 -- Calculate the converted amount
2421 l_converted_amount := p_amount * l_currency_conversion_rate;
2422
2423 -- Round the converted amount
2424 -- l_converted_amount := OKL_ACCOUNTING_UTIL.CROSS_CURRENCY_ROUND_AMOUNT(l_converted_amount, l_contract_currency);
2425
2426 -- Populate the OUT parameters.
2427 x_converted_amount := l_converted_amount;
2428 x_contract_currency := l_contract_currency;
2429 x_currency_conversion_type := l_currency_conversion_type;
2430 x_currency_conversion_rate := l_currency_conversion_rate;
2431 x_currency_conversion_date := l_currency_conversion_date;
2432
2433 EXCEPTION
2434 WHEN OTHERS THEN
2435 x_converted_amount := -1;
2436
2437 END convert_to_contract_currency;
2438
2439 /* ============================================================================
2440 Procedure : convert_to_contract_currency
2441 Added by Santonyr 20-Dec-2002
2442
2443 Parameters :
2444 IN
2445 p_khr_id
2446 p_from_currency
2447 p_transaction_date
2448 p_amount
2449
2450 OUT
2451 x_return_status
2452 x_contract_currency
2453 x_currency_conversion_type
2454 x_currency_conversion_rate
2455 x_currency_conversion_date
2456 x_converted_amount
2457
2458 Description : This overloaded procedure converts the amount from functional currency
2459 to contract currency. And then returns the rounded amount. This also
2460 returns the currency conversion factors and return_status along.
2461
2462 ============================================================================*/
2463
2464
2465 PROCEDURE convert_to_contract_currency
2466 (
2467 p_khr_id IN OKC_K_HEADERS_B.id%TYPE,
2468 p_from_currency IN fnd_currencies.currency_code%TYPE,
2469 p_transaction_date IN DATE,
2470 p_amount IN NUMBER,
2471 x_return_status OUT NOCOPY VARCHAR2,
2472 x_contract_currency OUT NOCOPY OKC_K_HEADERS_B.currency_code%TYPE,
2473 x_currency_conversion_type OUT NOCOPY OKL_K_HEADERS.currency_conversion_type%TYPE,
2474 x_currency_conversion_rate OUT NOCOPY OKL_K_HEADERS.currency_conversion_rate%TYPE,
2475 x_currency_conversion_date OUT NOCOPY OKL_K_HEADERS.currency_conversion_date%TYPE,
2476 x_converted_amount OUT NOCOPY NUMBER
2477 )
2478 AS
2479
2480 -- Cursor to select the currency conversion factors for the contract id passed
2481 CURSOR cntrct_cur IS
2482 SELECT CURRENCY_CODE,
2483 CURRENCY_CONVERSION_TYPE,
2484 CURRENCY_CONVERSION_RATE,
2485 CURRENCY_CONVERSION_DATE
2486 FROM OKL_K_HEADERS_FULL_V
2487 WHERE ID = p_khr_id;
2488
2489 l_func_currency VARCHAR2(15);
2490 l_contract_currency VARCHAR2(15);
2491 l_currency_conversion_type VARCHAR2(30);
2492 l_currency_conversion_rate NUMBER;
2493 l_currency_conversion_date DATE;
2494 l_converted_amount NUMBER;
2495 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
2496 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2497 l_msg_count NUMBER := 0;
2498 l_msg_data VARCHAR2(2000);
2499 l_api_version NUMBER := 1.0;
2500
2501
2502 BEGIN
2503 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2504
2505 -- Fetch the currency conversion factors from the contract cursor
2506 FOR cntrct_rec IN cntrct_cur LOOP
2507 l_contract_currency := cntrct_rec.currency_code;
2508 l_currency_conversion_type := cntrct_rec.currency_conversion_type;
2509 l_currency_conversion_rate := cntrct_rec.currency_conversion_rate;
2510 l_currency_conversion_date := cntrct_rec.currency_conversion_date;
2511 END LOOP;
2512
2513
2514 IF l_contract_currency IS NULL THEN
2515 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2516 p_msg_name => 'OKL_NO_CONTRACT_FOR_CONV');
2517 x_return_status := OKL_API.G_RET_STS_ERROR;
2518 RAISE OKL_API.G_EXCEPTION_ERROR;
2519 END IF;
2520
2521
2522 -- Get the functional currency code using Accounting Util API if the passed
2523 -- currency code is null
2524 l_func_currency := p_from_currency;
2525 IF l_func_currency IS NULL THEN
2526 l_func_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
2527 END IF;
2528
2529 -- Currency conversion rate is 1 if both the contract and functional
2530 -- currencies are same
2531
2532 IF l_func_currency = l_contract_currency THEN
2533 l_currency_conversion_rate := 1;
2534
2535 ELSE
2536
2537 IF UPPER(l_currency_conversion_type) = 'USER' THEN
2538 l_currency_conversion_rate := 1/l_currency_conversion_rate;
2539
2540 ELSE
2541
2542 -- Get the currency conversion rate using get_curr_con_rate API if the
2543 -- conversion type is not 'USER'
2544
2545 l_currency_conversion_date := p_transaction_date;
2546 get_curr_con_rate
2547 (p_api_version => l_api_version,
2548 p_init_msg_list => l_init_msg_list,
2549 x_return_status => l_return_status,
2550 x_msg_count => l_msg_count,
2551 x_msg_data => l_msg_data,
2552 p_from_curr_code => l_func_currency,
2553 p_to_curr_code => l_contract_currency,
2554 p_con_date => l_currency_conversion_date,
2555 p_con_type => l_currency_conversion_type,
2556 x_conv_rate => l_currency_conversion_rate );
2557
2558 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2559 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2560 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2561 RAISE OKL_API.G_EXCEPTION_ERROR;
2562 END IF;
2563
2564 END IF; -- The type is USER
2565 END IF; -- Functional and contract currencies are not same.
2566
2567
2568 -- Calculate the converted amount
2569 l_converted_amount := p_amount * l_currency_conversion_rate;
2570
2571 -- Round the converted amount
2572 -- l_converted_amount := OKL_ACCOUNTING_UTIL.CROSS_CURRENCY_ROUND_AMOUNT(l_converted_amount, l_contract_currency);
2573
2574 -- Populate the OUT parameters.
2575 x_converted_amount := l_converted_amount;
2576 x_contract_currency := l_contract_currency;
2577 x_currency_conversion_type := l_currency_conversion_type;
2578 x_currency_conversion_rate := l_currency_conversion_rate;
2579 x_currency_conversion_date := l_currency_conversion_date;
2580
2581 EXCEPTION
2582
2583 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2584 x_return_status := OKL_API.G_RET_STS_ERROR ;
2585 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2586 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2587 WHEN OTHERS THEN
2588 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2589
2590 END convert_to_contract_currency;
2591
2592
2593
2594 /* ============================================================================
2595 Procedure : cc_round_format_amount
2596 Added by Santonyr 09-Dec-2002
2597
2598 Parameters :
2599 IN
2600 p_currency_code
2601 p_amount
2602
2603 RETURN : Rounded and formatted amount
2604
2605 Description : This function returns the rounded and formatted amount
2606 for a given amount and currency code using the cross currency rounding rule
2607
2608 ============================================================================*/
2609
2610 FUNCTION cc_round_format_amount
2611 (p_amount IN NUMBER,
2612 p_currency_code IN VARCHAR2)
2613
2614 RETURN VARCHAR2
2615
2616 AS
2617
2618 l_rounded_amount NUMBER;
2619 l_formatted_amount VARCHAR2(1000);
2620
2621 BEGIN
2622
2623 -- Round the amount.
2624 l_rounded_amount := cross_currency_round_amount(
2625 p_amount => p_amount,
2626 p_currency_code => p_currency_code);
2627
2628 -- Format the amount
2629 l_formatted_amount := format_amount(
2630 p_amount => l_rounded_amount,
2631 p_currency_code => p_currency_code) ;
2632
2633 RETURN l_formatted_amount;
2634
2635 EXCEPTION
2636 WHEN OTHERS THEN
2637 RETURN '-1';
2638
2639 END cc_round_format_amount;
2640
2641 /************************************************************************************/
2642
2643 PROCEDURE cc_round_format_amount
2644 (p_api_version IN NUMBER,
2645 p_init_msg_list IN VARCHAR2,
2646 x_return_status OUT NOCOPY VARCHAR2,
2647 x_msg_count OUT NOCOPY NUMBER,
2648 x_msg_data OUT NOCOPY VARCHAR2,
2649 p_amount IN NUMBER,
2650 p_currency_code IN VARCHAR2,
2651 x_formatted_amount OUT NOCOPY VARCHAR2)
2652 IS
2653
2654 l_rounded_amount NUMBER;
2655 l_formatted_amount VARCHAR2(1000);
2656 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2657
2658 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
2659 l_msg_count NUMBER := 0;
2660 l_msg_data VARCHAR2(2000);
2661 l_api_version NUMBER := 1.0;
2662
2663 BEGIN
2664
2665 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2666 -- Round the amount.
2667
2668 cross_currency_round_amount
2669 (p_api_version => l_api_version,
2670 p_init_msg_list => l_init_msg_list,
2671 x_return_status => l_return_status,
2672 x_msg_count => l_msg_count,
2673 x_msg_data => l_msg_data,
2674 p_amount => p_amount,
2675 p_currency_code => p_currency_code,
2676 x_rounded_amount => l_rounded_amount);
2677
2678
2679 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2680 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2681 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2682 RAISE OKL_API.G_EXCEPTION_ERROR;
2683 END IF;
2684
2685 -- Format the amount
2686 l_formatted_amount := format_amount(
2687 p_amount => l_rounded_amount,
2688 p_currency_code => p_currency_code) ;
2689
2690 IF l_formatted_amount IS NULL THEN
2691 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
2692 p_msg_name => 'OKL_ERROR_FORMAT_AMT');
2693 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2694 RAISE OKL_API.G_EXCEPTION_ERROR;
2695 END IF;
2696
2697 x_formatted_amount := l_formatted_amount;
2698
2699 EXCEPTION
2700
2701 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2702 x_return_status := OKL_API.G_RET_STS_ERROR ;
2703 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2704 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2705 WHEN OTHERS THEN
2706 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2707
2708 END cc_round_format_amount;
2709
2710 /************************************************************************************/
2711
2712 /* ============================================================================
2713 Function : get_valid_gl_date
2714 Added by Keerthi 10-Jan-2003
2715 Modified by nikshah 22-Jan-2007
2716
2717 Parameters :
2718 IN
2719 p_gl_date
2720 p_ledger_id
2721
2722 RETURN : DATE
2723
2724 Description : This function accepts a GL Date. It validates this GL Date. If
2725 this Date falls into an Open or future open period then the same date is
2726 returned. If it does not, then it tries to find out a valid GL date before this
2727 Date or after this date. If none of the period is open then this returns a NULL.
2728 Done as part of the bug 2738336
2729
2730 Added p_ledger_id argument as part of bug 5707866 by nikshah
2731 If p_ledger_id passed to the API is null then it considers ledger from primary representation.
2732 Otherwise, it considers the ledger id that is passed to it.
2733 Two cursors changed to accept one parameter: l_ledger_id
2734 Changed call to get_period_info and get_okl_period_status procedures to pass p_ledger_id parameter
2735
2736 ============================================================================*/
2737
2738 FUNCTION get_valid_gl_date(p_gl_date IN DATE, p_ledger_id IN NUMBER DEFAULT NULL)
2739 RETURN DATE
2740
2741 AS
2742
2743 CURSOR bef_csr (l_ledger_id NUMBER) IS
2744 SELECT MAX(end_date)
2745 FROM gl_period_statuses
2746 WHERE application_id = 540
2747 AND ledger_id = l_ledger_id
2748 AND closing_status IN ('F','O')
2749 AND TRUNC(end_date) <= TRUNC(p_gl_date)
2750 AND adjustment_period_flag = 'N' ;
2751
2752
2753 CURSOR aft_csr (l_ledger_id NUMBER) IS
2754 SELECT MIN(start_date)
2755 FROM gl_period_statuses
2756 WHERE application_id = 540
2757 AND ledger_id = l_ledger_id
2758 AND closing_status IN ('F','O')
2759 AND TRUNC(start_date) >= TRUNC(p_gl_date)
2760 AND adjustment_period_flag = 'N' ;
2761
2762 l_period_name gl_periods.period_name%TYPE;
2763 l_start_date DATE;
2764 l_end_date DATE;
2765 l_dummy_date DATE := NULL;
2766 l_period_status gl_period_statuses.closing_status%TYPE;
2767 l_ledger_id NUMBER;
2768
2769
2770
2771
2772 BEGIN
2773 l_ledger_id := p_ledger_id;
2774 IF l_ledger_id IS NULL
2775 THEN
2776 l_ledger_id := get_set_of_books_id;
2777 END IF;
2778
2779 get_period_info
2780 (p_date => p_gl_date,
2781 p_period_name => l_period_name,
2782 p_start_date => l_start_date,
2783 p_end_date => l_end_date,
2784 p_ledger_id => l_ledger_id);
2785
2786 l_period_status := get_okl_period_status(p_period_name => l_period_name, p_ledger_id => l_ledger_id);
2787
2788 IF l_period_status IN ('F','O') THEN
2789 RETURN p_gl_date;
2790 END IF;
2791
2792 OPEN aft_csr(l_ledger_id);
2793 FETCH aft_csr INTO l_dummy_date;
2794 IF (l_dummy_date IS NULL) THEN
2795 CLOSE aft_csr;
2796 OPEN bef_csr(l_ledger_id);
2797 FETCH bef_csr INTO l_dummy_date;
2798 IF (l_dummy_date IS NULL) THEN
2799 CLOSE bef_csr;
2800 RETURN NULL;
2801 END IF;
2802 CLOSE bef_csr;
2803 RETURN l_dummy_date;
2804 END IF;
2805 CLOSE aft_csr;
2806
2807 RETURN l_dummy_date;
2808
2809 END get_valid_gl_date;
2810
2811
2812 -- Added by Santonyr 02-Aug-2004 for bug 3808697.
2813 -- This function is to derive the transaction amount for each transaction from FA.
2814
2815 FUNCTION get_fa_trx_amount
2816 (p_book_type_code IN VARCHAR2,
2817 p_asset_id IN NUMBER,
2818 p_transaction_type IN VARCHAR2,
2819 p_transaction_header_id IN NUMBER )
2820 RETURN NUMBER
2821
2822 IS
2823
2824 l_before_amount NUMBER := NULL;
2825 l_after_amount NUMBER := NULL;
2826 l_trx_amount NUMBER := NULL;
2827
2828 -- Cursor to get the asset amount after the transaction
2829
2830 CURSOR after_csr IS
2831 SELECT bk.cost
2832 FROM fa_books bk
2833 WHERE bk.book_type_code = p_book_type_code
2834 AND bk.asset_id = p_asset_id
2835 AND bk.transaction_header_id_in = p_transaction_header_id;
2836
2837 -- Cursor to get the asset amount before the transaction
2838
2839 CURSOR before_csr IS
2840 SELECT bk.cost
2841 FROM fa_books bk
2842 WHERE bk.book_type_code = p_book_type_code
2843 AND bk.asset_id = p_asset_id
2844 AND bk.transaction_header_id_out = p_transaction_header_id;
2845
2846
2847 BEGIN
2848
2849 -- Get the amount from the cursor
2850
2851 FOR after_rec IN after_csr LOOP
2852 l_after_amount := after_rec.cost;
2853 END LOOP;
2854
2855 -- Get the amount from the cursor
2856
2857 FOR before_rec IN before_csr LOOP
2858 l_before_amount := before_rec.cost;
2859 END LOOP;
2860
2861 -- Get the transaction amount based on the transaction.
2862
2863 IF p_transaction_type = 'ADDITION' THEN
2864 RETURN NVL(l_after_amount, 0) ;
2865 ELSE
2866 RETURN NVL(l_after_amount, 0) - NVL(l_before_amount, 0) ;
2867 END IF;
2868
2869 EXCEPTION
2870 WHEN OTHERS THEN
2871 RETURN NULL;
2872
2873 END get_fa_trx_amount;
2874
2875 -- Added by Santonyr 10-Oct-2004.
2876 -- This function is returns if a OKL transaction is actual or draft..
2877 -- Added p_khr_id by abhsaxen 22-Mar-2007 for bug 5660408
2878
2879 FUNCTION Get_Draft_Actual_Trx
2880 (p_trx_id IN NUMBER,
2881 p_source_table IN VARCHAR2,
2882 p_khr_id IN NUMBER )
2883 RETURN VARCHAR2
2884 IS
2885
2886 l_draft VARCHAR2(30);
2887 l_status VARCHAR2(30);
2888
2889
2890 CURSOR c_draft_csr IS
2891 SELECT
2892 DIST.POST_TO_GL
2893 FROM
2894 OKL_TXL_CNTRCT_LNS LN,
2895 OKL_TRNS_ACC_DSTRS DIST
2896 WHERE
2897 LN.TCN_ID = p_trx_id
2898 AND LN.ID = DIST.SOURCE_ID
2899 AND DIST.SOURCE_TABLE = p_source_table
2900 AND DIST.POST_TO_GL = 'Y';
2901
2902
2903 -- Added LN.DNZ_KHR_ID =p_khr_id and p_source_table by abhsaxen 22-Mar-2007 for bug 5660408
2904 CURSOR a_draft_csr IS
2905 SELECT
2906 DIST.POST_TO_GL
2907 FROM
2908 OKL_TXL_ASSETS_B LN,
2909 OKL_TRNS_ACC_DSTRS DIST
2910 WHERE
2911 LN.TAS_ID = p_trx_id
2912 AND LN.DNZ_KHR_ID =p_khr_id
2913 AND LN.ID = DIST.SOURCE_ID
2914 AND DIST.SOURCE_TABLE = p_source_table
2915 AND DIST.POST_TO_GL = 'Y';
2916
2917 BEGIN
2918
2919 -- If the transaction is a contract transaction
2920
2921 IF p_source_table = 'OKL_TXL_CNTRCT_LNS' THEN
2922
2923 OPEN c_draft_csr;
2924 FETCH c_draft_csr INTO l_draft;
2925 IF c_draft_csr%NOTFOUND THEN
2926 l_status := 'DRAFT';
2927 ELSE
2928 l_status := 'ACTUAL';
2929 END IF;
2930 CLOSE c_draft_csr;
2931
2932 -- If the transaction is an asset transaction
2933
2934 ELSIF p_source_table = 'OKL_TXL_ASSETS_B' THEN
2935
2936 OPEN a_draft_csr;
2937 FETCH a_draft_csr INTO l_draft;
2938 IF a_draft_csr%NOTFOUND THEN
2939 l_status := 'DRAFT';
2940 ELSE
2941 l_status := 'ACTUAL';
2942 END IF;
2943 CLOSE a_draft_csr;
2944
2945
2946 END IF;
2947
2948 RETURN l_status;
2949
2950 EXCEPTION
2951 WHEN OTHERS THEN
2952 IF a_draft_csr%ISOPEN THEN
2953 CLOSE a_draft_csr;
2954 END IF;
2955
2956 IF c_draft_csr%ISOPEN THEN
2957 CLOSE c_draft_csr;
2958 END IF;
2959
2960 RETURN NULL;
2961
2962 END Get_Draft_Actual_Trx;
2963
2964 -- Added by Santonyr 10-Dec-2004 for bug 4028662.
2965 -- This function is to return the FA transaction date.
2966
2967 FUNCTION get_fa_trx_date
2968 (p_book_type_code IN VARCHAR2)
2969 RETURN DATE
2970 IS
2971
2972 l_sysdate DATE := G_SYSDATE;
2973 l_fa_trx_date DATE;
2974
2975 -- Cursor to fetch the fa trx date
2976
2977 CURSOR fa_date_csr IS
2978 SELECT GREATEST(calendar_period_open_date,
2979 LEAST(l_sysdate, calendar_period_close_date))
2980 FROM fa_deprn_periods
2981 WHERE book_type_code = p_book_type_code
2982 AND period_close_date IS NULL;
2983
2984 BEGIN
2985
2986 OPEN fa_date_csr;
2987 FETCH fa_date_csr INTO l_fa_trx_date;
2988 CLOSE fa_date_csr;
2989
2990 RETURN l_fa_trx_date;
2991
2992 EXCEPTION
2993 WHEN OTHERS THEN
2994 RETURN NULL;
2995
2996 END get_fa_trx_date;
2997
2998
2999
3000 -- Added by Santonyr 10-Dec-2004 for bug 4028662.
3001 -- This procedure is to return the FA transaction date.
3002
3003 PROCEDURE get_fa_trx_date
3004 (p_book_type_code IN VARCHAR2,
3005 x_return_status OUT NOCOPY VARCHAR2,
3006 x_fa_trx_date OUT NOCOPY DATE)
3007
3008 IS
3009
3010 l_fa_trx_date DATE;
3011
3012 BEGIN
3013 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3014
3015 l_fa_trx_date := Okl_Accounting_Util.get_fa_trx_date(p_book_type_code);
3016
3017 IF l_fa_trx_date IS NULL THEN
3018 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
3019 p_msg_name => 'OKL_NO_FA_TRX_DATE');
3020 RAISE Okl_Api.G_EXCEPTION_ERROR;
3021 END IF;
3022
3023 x_fa_trx_date := l_fa_trx_date;
3024
3025 EXCEPTION
3026 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3027 x_return_status := Okl_Api.G_RET_STS_ERROR ;
3028 WHEN OTHERS THEN
3029 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
3030
3031 END get_fa_trx_date;
3032
3033
3034 -- Added by Santonyr 10-Dec-2004 for bug 4028662.
3035 -- This procedure is to return the first FA transaction date for a contract.
3036
3037 FUNCTION get_fa_trx_start_date
3038 (p_asset_number IN VARCHAR2,
3039 p_corporate_book IN VARCHAR2,
3040 p_khr_id IN NUMBER,
3041 p_sts_code IN VARCHAR2)
3042 RETURN DATE
3043 IS
3044
3045 l_fa_date DATE;
3046
3047 CURSOR fa_trx_st_dt_csr IS
3048 SELECT
3049 TXL.FA_TRX_DATE
3050 FROM
3051 OKL_TXL_ASSETS_B txl,
3052 OKL_TRX_ASSETS TRX,
3053 OKL_TRX_TYPES_V TRY
3054 WHERE
3055 TXL.ASSET_NUMBER = p_asset_number AND
3056 TXL.CORPORATE_BOOK = p_corporate_book AND
3057 TXL.DNZ_KHR_ID = p_khr_id AND
3058 TXL.TAS_ID = TRX.ID AND
3059 TRX.TRY_ID = TRY.ID AND
3060 TRY.NAME IN ('Internal Asset Creation', 'Release');
3061
3062
3063 BEGIN
3064
3065 IF p_sts_code IN ('NEW', 'INCOMPLETE', 'PASSED', 'COMPLETE', 'APPROVED','PENDING_APPROVAL') THEN
3066 RETURN Okl_Accounting_Util.g_final_date;
3067 ELSE
3068
3069 OPEN fa_trx_st_dt_csr;
3070 FETCH fa_trx_st_dt_csr INTO l_fa_date;
3071 CLOSE fa_trx_st_dt_csr;
3072
3073 RETURN l_fa_date;
3074 END IF;
3075
3076 EXCEPTION
3077 WHEN OTHERS THEN
3078 RETURN NULL;
3079
3080 END get_fa_trx_start_date;
3081
3082
3083 -- Added by Santonyr 10-Dec-2004 for bug 4028662.
3084 -- This procedure is to return the last FA transaction date for a contract.
3085
3086 FUNCTION get_fa_trx_end_date
3087 (p_asset_number IN VARCHAR2,
3088 p_corporate_book IN VARCHAR2,
3089 p_khr_id IN NUMBER)
3090 RETURN DATE
3091 IS
3092
3093 l_fa_date DATE;
3094 l_fa_date_found DATE;
3095
3096 CURSOR fa_trx_end_dt_csr IS
3097 SELECT
3098 MAX(TXL.FA_TRX_DATE)
3099 FROM
3100 OKL_TXL_ASSETS_B txl,
3101 OKL_TRX_ASSETS TRX,
3102 OKL_TRX_TYPES_V TRY
3103 WHERE
3104 TXL.ASSET_NUMBER = p_asset_number AND
3105 TXL.CORPORATE_BOOK = p_corporate_book AND
3106 TXL.DNZ_KHR_ID = p_khr_id AND
3107 TXL.TAS_ID = TRX.ID AND
3108 TRX.TRY_ID = TRY.ID AND
3109 TRY.NAME IN ('Off Lease Amortization', 'Asset Disposition');
3110
3111 BEGIN
3112
3113 OPEN fa_trx_end_dt_csr;
3114 FETCH fa_trx_end_dt_csr INTO l_fa_date;
3115 CLOSE fa_trx_end_dt_csr;
3116
3117 RETURN l_fa_date;
3118
3119 EXCEPTION
3120 WHEN OTHERS THEN
3121 RETURN NULL;
3122
3123 END get_fa_trx_end_date;
3124
3125 -- Added by nikshah 22-Jan-2007 for bug 5707866.
3126 -- This procedure returns valuation method code
3127
3128 FUNCTION get_valuation_method_code( p_ledger_id NUMBER DEFAULT NULL)
3129 RETURN VARCHAR2
3130 AS
3131 l_ledger_id NUMBER;
3132 l_val_meth_code GL_LEDGERS.SHORT_NAME%TYPE;
3133
3134 CURSOR get_gl_short_name_csr(l_ledger_id NUMBER) IS
3135 SELECT short_name
3136 FROM gl_ledgers
3137 WHERE ledger_id = l_ledger_id;
3138
3139 BEGIN
3140 l_ledger_id := p_ledger_id;
3141 IF l_ledger_id IS NULL
3142 THEN
3143 l_ledger_id := get_set_of_books_id;
3144 END IF;
3145 OPEN get_gl_short_name_csr (l_ledger_id);
3146 FETCH get_gl_short_name_csr INTO l_val_meth_code;
3147 CLOSE get_gl_short_name_csr;
3148
3149 RETURN l_val_meth_code;
3150
3151 EXCEPTION
3152 WHEN OTHERS THEN
3153 IF (get_gl_short_name_csr%ISOPEN) THEN
3154 CLOSE get_gl_short_name_csr;
3155 END IF;
3156 RETURN(NULL);
3157 END get_valuation_method_code;
3158
3159 -- Added by nikshah 08-Feb-2007 for bug 5707866.
3160 -- This function returns account derivation option
3161
3162 FUNCTION get_account_derivation
3163 RETURN VARCHAR2
3164 AS
3165 l_acct_derv okl_sys_acct_opts.account_derivation%TYPE;
3166
3167 CURSOR get_acct_derivation_csr
3168 IS
3169 SELECT account_derivation
3170 FROM okl_sys_acct_opts;
3171 BEGIN
3172 OPEN get_acct_derivation_csr;
3173 FETCH get_acct_derivation_csr INTO l_acct_derv;
3174 CLOSE get_acct_derivation_csr;
3175 RETURN l_acct_derv;
3176 EXCEPTION
3177 WHEN OTHERS THEN
3178 IF (get_acct_derivation_csr%ISOPEN) THEN
3179 CLOSE get_acct_derivation_csr;
3180 END IF;
3181 RETURN (NULL);
3182 END get_account_derivation;
3183
3184 -- MGAAP 7263041
3185 FUNCTION get_fa_reporting_book( p_org_id NUMBER DEFAULT NULL)
3186 RETURN VARCHAR2 IS
3187 l_org_id NUMBER;
3188 l_fa_reporting_book OKL_SYSTEM_PARAMS_ALL.RPT_PROD_BOOK_TYPE_CODE%TYPE;
3189 CURSOR c_fa_reporting_book(l_org_id NUMBER) IS
3190 SELECT RPT_PROD_BOOK_TYPE_CODE
3191 FROM OKL_SYSTEM_PARAMS_ALL
3192 WHERE ORG_ID = l_org_id;
3193 BEGIN
3194 IF (p_org_id is NULL OR
3195 p_org_id = OKL_API.G_MISS_NUM) THEN
3196 l_org_id := mo_global.get_current_org_id;
3197 ELSE
3198 l_org_id := p_org_id;
3199 END IF;
3200 FOR r IN c_fa_reporting_book(l_org_id)
3201 LOOP
3202 l_fa_reporting_book := r.RPT_PROD_BOOK_TYPE_CODE;
3203 END LOOP;
3204 return(l_fa_reporting_book);
3205 END get_fa_reporting_book;
3206
3207 FUNCTION get_fa_reporting_book( p_kle_id NUMBER ) RETURN VARCHAR2 IS
3208 l_org_id NUMBER;
3209 l_fa_reporting_book OKL_SYSTEM_PARAMS_ALL.RPT_PROD_BOOK_TYPE_CODE%TYPE;
3210 CURSOR c_org_id(l_kle_id NUMBER) IS
3211 SELECT a.authoring_org_id ORG_ID
3212 FROM okc_k_headers_all_b a,
3213 okc_k_lines_b b
3214 WHERE b.id = p_kle_id
3215 AND b.dnz_chr_id = a.id;
3216 BEGIN
3217 FOR recindex IN c_org_id(p_kle_id)
3218 LOOP
3219 l_org_id := recindex.ORG_ID;
3220 l_fa_reporting_book := get_fa_reporting_book(p_org_id => l_org_id);
3221 END LOOP;
3222 RETURN(l_fa_reporting_book);
3223 END get_fa_reporting_book;
3224
3225 -- Start of comments
3226 --
3227 -- Procedure Name : get_reporting_product
3228 -- Description : This procedure checks if there is a reporting product attached to the contract
3229 -- Business Rules :
3230 -- Parameters : p_contract_id - Contract ID
3231 -- Version : 1.0
3232 -- History : SECHAWLA 09-mar-2009 MG Impact on Investor Agreement - Created
3233 -- End of comments
3234 PROCEDURE get_reporting_product(p_api_version IN NUMBER,
3235 p_init_msg_list IN VARCHAR2,
3236 x_return_status OUT NOCOPY VARCHAR2,
3237 x_msg_count OUT NOCOPY NUMBER,
3238 x_msg_data OUT NOCOPY VARCHAR2,
3239 p_contract_id IN NUMBER,
3240 x_rep_product OUT NOCOPY VARCHAR2,
3241 x_rep_product_id OUT NOCOPY NUMBER,
3242 x_rep_deal_type OUT NOCOPY VARCHAR2 ) IS
3243 -- Get the financial product of the contract
3244 CURSOR l_get_fin_product(cp_khr_id IN NUMBER) IS
3245 SELECT a.start_date, a.contract_number, b.pdt_id
3246 FROM okc_k_headers_b a, okl_k_headers b
3247 WHERE a.id = b.id
3248 AND a.id = cp_khr_id;
3249 SUBTYPE pdtv_rec_type IS OKL_SETUPPRODUCTS_PUB.pdtv_rec_type;
3250 SUBTYPE pdt_parameters_rec_type IS OKL_SETUPPRODUCTS_PUB.pdt_parameters_rec_type;
3251 l_fin_product_id NUMBER;
3252 l_start_date DATE;
3253 l_contract_number VARCHAR2(120);
3254 lp_pdtv_rec pdtv_rec_type;
3255 lp_empty_pdtv_rec pdtv_rec_type;
3256 lx_pdt_parameter_rec pdt_parameters_rec_type ;
3257 l_reporting_product OKL_PRODUCTS_V.NAME%TYPE;
3258 l_reporting_product_id NUMBER;
3259 lx_no_data_found BOOLEAN;
3260 l_mg_rep_book fa_book_controls.book_type_code%TYPE;
3261 mg_error EXCEPTION;
3262 l_rep_deal_type okl_product_parameters_v.deal_type%TYPE;
3263 BEGIN
3264 -- get the financial product of the contract
3265 OPEN l_get_fin_product(p_contract_id);
3266 FETCH l_get_fin_product INTO l_start_date, l_contract_number, l_fin_product_id;
3267 CLOSE l_get_fin_product;
3268 lp_pdtv_rec.id := l_fin_product_id;
3269 -- check if the fin product has a reporting product
3270 OKL_SETUPPRODUCTS_PUB.Getpdt_parameters( p_api_version => p_api_version,
3271 p_init_msg_list => OKC_API.G_FALSE,
3272 x_return_status => x_return_status,
3273 x_no_data_found => lx_no_data_found,
3274 x_msg_count => x_msg_count,
3275 x_msg_data => x_msg_data,
3276 p_pdtv_rec => lp_pdtv_rec,
3277 p_product_date => l_start_date,
3278 p_pdt_parameter_rec => lx_pdt_parameter_rec);
3279 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3280 -- Error getting financial product parameters for contract CONTRACT_NUMBER.
3281 OKC_API.set_message( p_app_name => 'OKL',
3282 p_msg_name => 'OKL_AM_FIN_PROD_PARAM_ERR',
3283 p_token1 => 'CONTRACT_NUMBER',
3284 p_token1_value => l_contract_number);
3285 ELSE
3286 x_rep_product := lx_pdt_parameter_rec.reporting_product;
3287 x_rep_product_id := lx_pdt_parameter_rec.reporting_pdt_id;
3288
3289 IF x_rep_product IS NOT NULL AND x_rep_product <> OKC_API.G_MISS_CHAR THEN
3290 lp_pdtv_rec := lp_empty_pdtv_rec;
3291 lp_pdtv_rec.id := x_rep_product_id;
3292
3293 -- get the deal type of the reporting product
3294 OKL_SETUPPRODUCTS_PUB.Getpdt_parameters( p_api_version => p_api_version,
3295 p_init_msg_list => OKC_API.G_FALSE,
3296 x_return_status => x_return_status,
3297 x_no_data_found => lx_no_data_found,
3298 x_msg_count => x_msg_count,
3299 x_msg_data => x_msg_data,
3300 p_pdtv_rec => lp_pdtv_rec,
3301 p_product_date => l_start_date,
3302 p_pdt_parameter_rec => lx_pdt_parameter_rec);
3303
3304 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
3305 -- Error getting reporting product parameters for contract CONTRACT_NUMBER.
3306 OKC_API.set_message( p_app_name => 'OKL',
3307 p_msg_name => 'OKL_AM_REP_PROD_PARAM_ERR',
3308 p_token1 => 'CONTRACT_NUMBER',
3309 p_token1_value => l_contract_number);
3310
3311
3312 ELSE
3313
3314 l_rep_deal_type := lx_pdt_parameter_rec.Deal_Type;
3315 IF l_rep_deal_type IS NULL OR l_rep_deal_type = OKC_API.G_MISS_CHAR THEN
3316 --Deal Type not defined for Reporting product REP_PROD.
3317 OKC_API.set_message( p_app_name => 'OKL',
3318 p_msg_name => 'OKL_AM_NO_MG_DEAL_TYPE',
3319 p_token1 => 'REP_PROD',
3320 p_token1_value => l_reporting_product);
3321
3322 x_return_status := OKL_API.G_RET_STS_ERROR;
3323 ELSE
3324 x_rep_deal_type := l_rep_deal_type ;
3325 END IF;
3326 END IF;
3327 END IF;
3328 END IF;
3329 EXCEPTION
3330 WHEN OTHERS THEN
3331 IF l_get_fin_product%ISOPEN THEN
3332 CLOSE l_get_fin_product;
3333 END IF;
3334 OKL_API.set_message(p_app_name => 'OKC',
3335 p_msg_name => g_unexpected_error,
3336 p_token1 => g_sqlcode_token,
3337 p_token1_value => sqlcode,
3338 p_token2 => g_sqlerrm_token,
3339 p_token2_value => sqlerrm);
3340 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3341 END get_reporting_product;
3342
3343 END okl_accounting_util;
[Home] [Help]