1 PACKAGE BODY GMF_GL_GET_BALANCES AS
2 /* $Header: gmfbalnb.pls 120.3 2005/10/11 02:50:54 sschinch noship $ */
3
4 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 Start**/
5
6 CURSOR cur_gl_get_balances
7 (
8 in_set_of_books number
9 , in_period_year number
10 , in_period_num number
11 , in_account_type varchar2
12 , in_currency_code varchar2
13 , in_actual_flag varchar2
14 , in_ytd_ptd number
15 , in_from_segments gmf_segment_values_tbl
16 , in_to_segments gmf_segment_values_tbl
17 , l_segment_delimiter VARCHAR2
18 , l_currency_code gl_sets_of_books.currency_code%TYPE -- Bug 4066174 Added
19 )
20 IS
21 SELECT /*+ index(glbal gl_balances_n1 ) */
22 in_set_of_books,
23 glcc.chart_of_accounts_id,
24 glbal.period_year,
25 glbal.period_num,
26 glcc.account_type,
27 glbal.currency_code,
28 glcc.segment1||l_segment_delimiter||
29 glcc.segment2||l_segment_delimiter||
30 glcc.segment3||l_segment_delimiter||
31 glcc.segment4||l_segment_delimiter||
32 glcc.segment5||l_segment_delimiter||
33 glcc.segment6||l_segment_delimiter||
34 glcc.segment7||l_segment_delimiter||
35 glcc.segment8||l_segment_delimiter||
36 glcc.segment9||l_segment_delimiter||
37 glcc.segment10||l_segment_delimiter||
38 glcc.segment11||l_segment_delimiter||
39 glcc.segment12||l_segment_delimiter||
40 glcc.segment13||l_segment_delimiter||
41 glcc.segment14||l_segment_delimiter||
42 glcc.segment15||l_segment_delimiter||
43 glcc.segment16||l_segment_delimiter||
44 glcc.segment17||l_segment_delimiter||
45 glcc.segment18||l_segment_delimiter||
46 glcc.segment19||l_segment_delimiter||
47 glcc.segment20||l_segment_delimiter||
48 glcc.segment21||l_segment_delimiter||
49 glcc.segment22||l_segment_delimiter||
50 glcc.segment23||l_segment_delimiter||
51 glcc.segment24||l_segment_delimiter||
52 glcc.segment25||l_segment_delimiter||
53 glcc.segment26||l_segment_delimiter||
54 glcc.segment27||l_segment_delimiter||
55 glcc.segment28||l_segment_delimiter||
56 glcc.segment29||l_segment_delimiter||
57 glcc.segment30 code_combinations,
58 glbal.actual_flag,
59 DECODE(in_ytd_ptd, 0, (period_net_dr - period_net_cr),
60 1, (begin_balance_dr - begin_balance_cr) +
61 (period_net_dr - period_net_cr),
62 (begin_balance_dr - begin_balance_cr) +
63 (period_net_dr - period_net_cr)),
64 l_segment_delimiter
65 FROM
66 gl_code_combinations glcc,
67 gl_balances glbal
68 WHERE
69 glbal.code_combination_id = glcc.code_combination_id
70 AND glbal.period_year = in_period_year
71 AND glbal.period_num = in_period_num
72 AND glbal.currency_code = nvl( in_currency_code, l_currency_code )
73 --AND glbal.set_of_books_id = in_set_of_books
74 AND glbal.ledger_id = in_set_of_books /* this is used as ledger id INVCONV sschinch*/
75 AND glbal.actual_flag = nvl(in_actual_flag, glbal.actual_flag)
76 AND glcc.account_type = nvl(in_account_type, glcc.account_type)
77 AND (in_from_segments(1) IS NULL OR in_to_segments(1) IS NULL OR (glcc.segment1 IS NULL) OR (glcc.segment1 >= nvl(in_from_segments(1),glcc.segment1) and glcc.segment1 <= nvl(in_to_segments(1),glcc.segment1)))
78 AND (in_from_segments(2) IS NULL OR in_to_segments(2) IS NULL OR (glcc.segment2 IS NULL) OR (glcc.segment2 >= nvl(in_from_segments(2),glcc.segment2) and glcc.segment2 <= nvl(in_to_segments(2),glcc.segment2)))
79 AND (in_from_segments(3) IS NULL OR in_to_segments(3) IS NULL OR (glcc.segment3 IS NULL) OR (glcc.segment3 >= nvl(in_from_segments(3),glcc.segment3) and glcc.segment3 <= nvl(in_to_segments(3),glcc.segment3)))
80 AND (in_from_segments(4) IS NULL OR in_to_segments(4) IS NULL OR (glcc.segment4 IS NULL) OR (glcc.segment4 >= nvl(in_from_segments(4),glcc.segment4) and glcc.segment4 <= nvl(in_to_segments(4),glcc.segment4)))
81 AND (in_from_segments(5) IS NULL OR in_to_segments(5) IS NULL OR (glcc.segment5 IS NULL) OR (glcc.segment5 >= nvl(in_from_segments(5),glcc.segment5) and glcc.segment5 <= nvl(in_to_segments(5),glcc.segment5)))
82 AND (in_from_segments(6) IS NULL OR in_to_segments(6) IS NULL OR (glcc.segment6 IS NULL) OR (glcc.segment6 >= nvl(in_from_segments(6),glcc.segment6) and glcc.segment6 <= nvl(in_to_segments(6),glcc.segment6)))
83 AND (in_from_segments(7) IS NULL OR in_to_segments(7) IS NULL OR (glcc.segment7 IS NULL) OR (glcc.segment7 >= nvl(in_from_segments(7),glcc.segment7) and glcc.segment7 <= nvl(in_to_segments(7),glcc.segment7)))
84 AND (in_from_segments(8) IS NULL OR in_to_segments(8) IS NULL OR (glcc.segment8 IS NULL) OR (glcc.segment8 >= nvl(in_from_segments(8),glcc.segment8) and glcc.segment8 <= nvl(in_to_segments(8),glcc.segment8)))
85 AND (in_from_segments(9) IS NULL OR in_to_segments(9) IS NULL OR (glcc.segment9 IS NULL) OR (glcc.segment9 >= nvl(in_from_segments(9),glcc.segment9) and glcc.segment9 <= nvl(in_to_segments(9),glcc.segment9)))
86 AND (in_from_segments(10) IS NULL OR in_to_segments(10) IS NULL OR (glcc.segment10 IS NULL) OR (glcc.segment10 >= nvl(in_from_segments(10),glcc.segment10) and glcc.segment10 <= nvl(in_to_segments(10),glcc.segment10)))
87 AND (in_from_segments(11) IS NULL OR in_to_segments(11) IS NULL OR (glcc.segment11 IS NULL) OR (glcc.segment11 >= nvl(in_from_segments(11),glcc.segment11) and glcc.segment11 <= nvl(in_to_segments(11),glcc.segment11)))
88 AND (in_from_segments(12) IS NULL OR in_to_segments(12) IS NULL OR (glcc.segment12 IS NULL) OR (glcc.segment12 >= nvl(in_from_segments(12),glcc.segment12) and glcc.segment12 <= nvl(in_to_segments(12),glcc.segment12)))
89 AND (in_from_segments(13) IS NULL OR in_to_segments(13) IS NULL OR (glcc.segment13 IS NULL) OR (glcc.segment13 >= nvl(in_from_segments(13),glcc.segment13) and glcc.segment13 <= nvl(in_to_segments(13),glcc.segment13)))
90 AND (in_from_segments(14) IS NULL OR in_to_segments(14) IS NULL OR (glcc.segment14 IS NULL) OR (glcc.segment14 >= nvl(in_from_segments(14),glcc.segment14) and glcc.segment14 <= nvl(in_to_segments(14),glcc.segment14)))
91 AND (in_from_segments(15) IS NULL OR in_to_segments(15) IS NULL OR (glcc.segment15 IS NULL) OR (glcc.segment15 >= nvl(in_from_segments(15),glcc.segment15) and glcc.segment15 <= nvl(in_to_segments(15),glcc.segment15)))
92 AND (in_from_segments(16) IS NULL OR in_to_segments(16) IS NULL OR (glcc.segment16 IS NULL) OR (glcc.segment16 >= nvl(in_from_segments(16),glcc.segment16) and glcc.segment16 <= nvl(in_to_segments(16),glcc.segment16)))
93 AND (in_from_segments(17) IS NULL OR in_to_segments(17) IS NULL OR (glcc.segment17 IS NULL) OR (glcc.segment17 >= nvl(in_from_segments(17),glcc.segment17) and glcc.segment17 <= nvl(in_to_segments(17),glcc.segment17)))
94 AND (in_from_segments(18) IS NULL OR in_to_segments(18) IS NULL OR (glcc.segment18 IS NULL) OR (glcc.segment18 >= nvl(in_from_segments(18),glcc.segment18) and glcc.segment18 <= nvl(in_to_segments(18),glcc.segment18)))
95 AND (in_from_segments(19) IS NULL OR in_to_segments(19) IS NULL OR (glcc.segment19 IS NULL) OR (glcc.segment19 >= nvl(in_from_segments(19),glcc.segment19) and glcc.segment19 <= nvl(in_to_segments(19),glcc.segment19)))
96 AND (in_from_segments(20) IS NULL OR in_to_segments(20) IS NULL OR (glcc.segment20 IS NULL) OR (glcc.segment20 >= nvl(in_from_segments(20),glcc.segment20) and glcc.segment20 <= nvl(in_to_segments(20),glcc.segment20)))
97 AND (in_from_segments(21) IS NULL OR in_to_segments(21) IS NULL OR (glcc.segment21 IS NULL) OR (glcc.segment21 >= nvl(in_from_segments(21),glcc.segment21) and glcc.segment21 <= nvl(in_to_segments(21),glcc.segment21)))
98 AND (in_from_segments(22) IS NULL OR in_to_segments(22) IS NULL OR (glcc.segment22 IS NULL) OR (glcc.segment22 >= nvl(in_from_segments(22),glcc.segment22) and glcc.segment22 <= nvl(in_to_segments(22),glcc.segment22)))
99 AND (in_from_segments(23) IS NULL OR in_to_segments(23) IS NULL OR (glcc.segment23 IS NULL) OR (glcc.segment23 >= nvl(in_from_segments(23),glcc.segment23) and glcc.segment23 <= nvl(in_to_segments(23),glcc.segment23)))
100 AND (in_from_segments(24) IS NULL OR in_to_segments(24) IS NULL OR (glcc.segment24 IS NULL) OR (glcc.segment24 >= nvl(in_from_segments(24),glcc.segment24) and glcc.segment24 <= nvl(in_to_segments(24),glcc.segment24)))
101 AND (in_from_segments(25) IS NULL OR in_to_segments(25) IS NULL OR (glcc.segment25 IS NULL) OR (glcc.segment25 >= nvl(in_from_segments(25),glcc.segment25) and glcc.segment25 <= nvl(in_to_segments(25),glcc.segment25)))
102 AND (in_from_segments(26) IS NULL OR in_to_segments(26) IS NULL OR (glcc.segment26 IS NULL) OR (glcc.segment26 >= nvl(in_from_segments(26),glcc.segment26) and glcc.segment26 <= nvl(in_to_segments(26),glcc.segment26)))
103 AND (in_from_segments(27) IS NULL OR in_to_segments(27) IS NULL OR (glcc.segment27 IS NULL) OR (glcc.segment27 >= nvl(in_from_segments(27),glcc.segment27) and glcc.segment27 <= nvl(in_to_segments(27),glcc.segment27)))
104 AND (in_from_segments(28) IS NULL OR in_to_segments(28) IS NULL OR (glcc.segment28 IS NULL) OR (glcc.segment28 >= nvl(in_from_segments(28),glcc.segment28) and glcc.segment28 <= nvl(in_to_segments(28),glcc.segment28)))
105 AND (in_from_segments(29) IS NULL OR in_to_segments(29) IS NULL OR (glcc.segment29 IS NULL) OR (glcc.segment29 >= nvl(in_from_segments(29),glcc.segment29) and glcc.segment29 <= nvl(in_to_segments(29),glcc.segment29)))
106 AND (in_from_segments(30) IS NULL OR in_to_segments(30) IS NULL OR (glcc.segment30 IS NULL) OR (glcc.segment30 >= nvl(in_from_segments(30),glcc.segment30) and glcc.segment30 <= nvl(in_to_segments(30),glcc.segment30)));
107
108
109
110 PROCEDURE fetch_segment_values
111 (
112 p_from_segment IN VARCHAR2
113 , p_to_Segment IN VARCHAR2
114 , p_delimiter IN VARCHAR2
115 , x_from_segment IN OUT NOCOPY GMF_SEGMENT_VALUES_TBL
116 , x_to_segment IN OUT NOCOPY GMF_SEGMENT_VALUES_TBL
117 )
118 IS
119 l_from_segment VARCHAR2(2000);
120 l_to_segment VARCHAR2(2000);
121 l_from_value VARCHAR2(150);
122 l_to_value VARCHAR2(150);
123 BEGIN
124 l_from_segment := p_from_segment;
125 l_to_segment := p_to_segment;
126 FOR i IN 1 .. 30
127 LOOP
128 l_from_value := SUBSTR( l_from_segment , 1, INSTR( l_from_segment, p_delimiter, 1 ) - 1 );
129 l_to_value := SUBSTR( l_to_segment , 1, INSTR( l_to_segment, p_delimiter, 1 ) - 1 );
130 l_from_segment := SUBSTR( l_from_segment, INSTR( l_from_segment, p_delimiter, 1 ) + 1 );
131 l_to_segment := SUBSTR( l_to_segment, INSTR( l_to_segment, p_delimiter, 1 ) + 1 );
132 x_from_segment(i) := l_from_value;
133 x_to_segment(i) := l_to_value;
134 l_from_value := null;
135 l_to_value := null;
136 END LOOP;
137 END fetch_segment_values;
138
139 PROCEDURE open_cur_gl_get_balances
140 (
141 in_set_of_books in number
142 , in_chart_of_accounts in number
143 , in_period_year in number
144 , in_period_num in number
145 , in_account_type in varchar2
146 , in_currency_code in varchar2
147 , start_segments in varchar2
148 , to_segments in varchar2
149 , in_actual_flag in varchar2
150 , in_ytd_ptd in number
151 )
152 IS
153 l_segment_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
154 segment_where_clause VARCHAR2(4000);
155 l_from_segments gmf_segment_values_tbl;
156 l_to_segments gmf_segment_values_tbl;
157 l_application_id fnd_application.application_id%TYPE;
158 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
159 l_currency_code gl_sets_of_books.currency_code%TYPE;
160 l_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
161
162 BEGIN
163
164 /***** Bug 4066174 - Added the new queries below *****/
165
166 -- Newly added Query 1 for Bug 4066174
167 SELECT application_id INTO l_application_id
168 FROM fnd_application
169 WHERE application_short_name = 'SQLGL' ;
170
171 SELECT currency_code,set_of_books_id, chart_of_accounts_id
172 INTO l_currency_code, l_set_of_books_id, l_chart_of_accounts_id
173 FROM gl_sets_of_books
174 WHERE set_of_books_id = in_set_of_books;
175
176 SELECT fifstr.concatenated_segment_delimiter
177 INTO l_segment_delimiter
178 FROM fnd_id_flex_structures fifstr
179 WHERE fifstr.id_flex_code = 'GL#'
180 AND fifstr.application_id = l_application_id
181 AND fifstr.id_flex_num = l_chart_of_accounts_id; --in_chart_of_accounts;
182
183
184 fetch_segment_values (start_segments, to_segments, l_segment_delimiter, l_from_segments, l_to_segments );
185
186 /***** Bug 4066174 - Performance Improvement - Modified the below cursor and its arguments. *****/
187
188 OPEN cur_gl_get_balances (
189 in_set_of_books
190 -- , in_chart_of_accounts -- We don't need anymore as we are spliting the query
191 , in_period_year
192 , in_period_num
193 , in_account_type
194 , in_currency_code
195 , in_actual_flag
196 , in_ytd_ptd
197 , l_from_segments
198 , l_to_segments
199 , l_segment_delimiter -- Bug 4066174 Added
200 , l_currency_code -- Bug 4066174 Added
201 );
202 END open_cur_gl_get_balances;
203
204 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 End **/
205
206
207 PROCEDURE proc_gl_get_balances(
208 in_set_of_books in out NOCOPY number,
209 in_chart_of_accounts in out NOCOPY number,
210 in_period_year in out NOCOPY number,
211 in_period_num in out NOCOPY number,
212 in_account_type in out NOCOPY varchar2,
213 in_currency_code in out NOCOPY varchar2,
214 start_segments in varchar2,
215 to_segments in out NOCOPY varchar2,
216 in_actual_flag in out NOCOPY varchar2,
217 in_ytd_ptd in number,
218 amount out NOCOPY number,
219 segment_delimiter out NOCOPY varchar2,
220 row_to_fetch in out NOCOPY number,
221 error_status out NOCOPY number) IS
222
223 BEGIN /* Beginning of procedure proc_gl_get_balances*/
224
225 IF( NOT cur_gl_get_balances%ISOPEN )
226 THEN
227 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 Start **/
228
229 open_cur_gl_get_balances
230 (
231 in_set_of_books
232 , in_chart_of_accounts
233 , in_period_year
234 , in_period_num
235 , in_account_type
236 , in_currency_code
237 , start_segments
238 , to_segments
239 , in_actual_flag
240 , in_ytd_ptd
241 );
242 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 End **/
243 END IF;
244
245 FETCH cur_gl_get_balances
246 INTO
247 in_set_of_books,
248 in_chart_of_accounts,
249 in_period_year,
250 in_period_num,
251 in_account_type,
252 in_currency_code,
253 to_segments,
254 in_actual_flag,
255 amount,
256 segment_delimiter;
257
258 IF( cur_gl_get_balances%NOTFOUND )
259 THEN
260 error_status := 100;
261 END IF;
262
263 IF( ( cur_gl_get_balances%NOTFOUND ) OR ( row_to_fetch = 1 ) )
264 THEN
265 CLOSE cur_gl_get_balances;
266 END IF;
267
268 EXCEPTION
269
270 WHEN others THEN
271 error_status := SQLCODE;
272
273 END proc_gl_get_balances; /* End of procedure proc_gl_get_balances*/
274
275 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 End **/
276
277 PROCEDURE initialize(
278 in_set_of_books_id IN NUMBER
279 )
280 IS
281
282 BEGIN
283 /* Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 Start */
284
285 RETURN;
286
287 /* Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 End */
288
289 END initialize;
290
291
292 END GMF_GL_GET_BALANCES; /* END GMF_GL_GET_BALANCES*/
[Home] [Help]