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