1 PACKAGE BODY GL_OPEN_BAL_REVAL_RPT_PKG AS
2 -- $Header: glxobrvb.pls 120.0.12000000.1 2007/10/23 15:50:40 sgudupat noship $
3 --*************************************************************************
4 -- Copyright (c) 2000 Oracle Corporation
5 -- All rights reserved
6 --*************************************************************************
7 --
8 --
9 -- PROGRAM NAME
10 -- glxobrvb.pls
11 --
12 -- DESCRIPTION
13 -- This script creates the package body of GL_OPEN_BAL_REVAL_RPT_PKG
14 -- This package is used for builidng all the necessary PL/SQL Logic for the
15 -- report "GL Open Balances Revaluation"
16 --
17 -- USAGE
18 -- To install How to Install
19 -- To execute How to Execute
20 --
21 -- PROGRAM LIST DESCRIPTION
22 --
23 -- DEPENDENCIES
24 --
25 -- CALLED BY
26 -- All the public functions are used in the data template GLOPNBALRVLRPT.xml
27 --
28 -- LAST UPDATE DATE 26-FEB-2007
29 --
30 -- HISTORY
31 -- =======
32 --
33 -- VERSION DATE AUTHOR(S) DESCRIPTION
34 -- ------- ----------- --------------- ------------------------------------
35 -- Draft1A 26-FEB-2007 Thirupathi Rao V Draft Version
36 --
37 --*************************************************************************
38 -- Global Type and variable for caching revaluation rate
39 TYPE rvl_conv_rate_cache_tbl_type IS TABLE OF NUMBER INDEX BY VARCHAR2(40);
40 gn_rvl_convrate_cache_tbl rvl_conv_rate_cache_tbl_type;
41 --
42 FUNCTION beforereport RETURN BOOLEAN IS
43 BEGIN
44 -- Populate start and end dates into global variables
45 SELECT
46 MAX(CASE gp.period_name WHEN period_from_param THEN gp.start_date END)
47 ,MAX(CASE gp.period_name WHEN period_to_param THEN gp.end_date END)
48 INTO
49 gd_start_date
50 ,gd_end_date
51 FROM
52 gl_periods gp
53 ,gl_ledgers gll
54 WHERE
55 gll.ledger_id=ledger_id_param
56 AND gp.period_set_name =gll.period_set_name
57 AND gp.period_name IN (period_from_param,period_to_param);
58 --build the where clause for security by access set
59 gc_access_where:= gl_access_set_security_pkg.get_security_clause
60 (ACCESS_SET_ID_PARAM
61 ,'R'
62 ,'LEDGER_COLUMN'
63 ,'LEDGER_ID'
64 ,'gb'
65 ,'SEG_COLUMN'
66 ,null
67 ,'gcc'
68 ,NULL);
69 IF gc_access_where IS NULL THEN
70 gc_access_where:= '1 = 1';
71 END IF;
72 --Build currency where clause
73 IF currency_param IS NOT NULL THEN
74 gc_currency_where:= 'gjh.currency_code=:currency_param';
75 ELSE
76 gc_currency_where:='1=1';
77 END IF;
78 RETURN TRUE;
79 END beforereport;
80 --
81 --
82 FUNCTION get_reval_conversion_rate ( p_code_combination_id IN NUMBER
83 ,p_account IN VARCHAR2
84 ,p_currency IN VARCHAR2) RETURN NUMBER IS
85 ln_reval_conversion_rate NUMBER;
86 ln_reval_header_id PLS_INTEGER;
87 lc_reval_rate_cache_pk VARCHAR2(40);
88 BEGIN
89 lc_reval_rate_cache_pk:=p_code_combination_id||'-'||p_currency;
90 -- If the reavaluation rate for the given CCID and currency is already calculated then return the value
91 -- else calculate
92 IF gn_rvl_convrate_cache_tbl.exists(lc_reval_rate_cache_pk) THEN
93 RETURN(gn_rvl_convrate_cache_tbl(lc_reval_rate_cache_pk));
94 END IF;
95 -- Find the header ID of the latest revalued entry
96 SELECT MAX(gjh.je_header_id)
97 INTO ln_reval_header_id
98 FROM
99 gl_je_headers gjh
100 ,gl_je_lines gjl
101 WHERE
102 gjh.status = 'P'
103 AND gjh.actual_flag = 'A'
104 AND gjh.ledger_id = ledger_id_param
105 AND gjh.je_source = 'Revaluation'
106 AND gjl.je_header_id = gjh.je_header_id
107 AND gjl.code_combination_id = p_code_combination_id
108 AND gjh.currency_code=p_currency
109 AND gjh.period_name=period_to_param;
110 -- If there is no revaluation entry exists in the period period_to_param for the CCID and currency input
111 -- display the message in the log file
112 IF ln_reval_header_id IS NULL THEN
113 gl_message.write_log ('GL_REVAL_ENTRY_NOT_FOUND'
114 ,3
115 ,'ACCT'
116 ,p_account
117 ,'CURRENCY'
118 ,p_currency
119 ,'PERIOD'
120 ,period_to_param
121 );
122 gn_rvl_convrate_cache_tbl(lc_reval_rate_cache_pk):=NULL;
123 RETURN(NULL);
124 ELSE
125 -- Get the revaluation rate for the header ID and cache it
126 SELECT currency_conversion_rate
127 INTO ln_reval_conversion_rate
128 FROM gl_je_headers
129 WHERE je_header_id=ln_reval_header_id;
130 gn_rvl_convrate_cache_tbl(lc_reval_rate_cache_pk):=ln_reval_conversion_rate;
131 RETURN(ln_reval_conversion_rate);
132 END IF;
133 END get_reval_conversion_rate;
134 --
135 --
136 FUNCTION get_data_access_set_name RETURN VARCHAR2 IS
137 lc_access_set_name VARCHAR2(30);
138 BEGIN
139 SELECT name
140 INTO lc_access_set_name
141 FROM gl_access_sets
142 WHERE access_set_id=access_set_id_param;
143 RETURN(lc_access_set_name);
144 END get_data_access_set_name;
145 --
146 --
147 FUNCTION get_ledger_name RETURN VARCHAR2 IS
148 lc_ledger_name VARCHAR2(30);
149 BEGIN
150 SELECT name
151 INTO lc_ledger_name
152 FROM gl_ledgers
153 WHERE ledger_id=ledger_id_param;
154 RETURN(lc_ledger_name);
155 END get_ledger_name;
156 --
157 END GL_OPEN_BAL_REVAL_RPT_PKG;
[Home] [Help]