[Home] [Help]
PACKAGE BODY: APPS.GMF_GL_GET_BALANCES
Source
1 PACKAGE BODY GMF_GL_GET_BALANCES AS
2 /* $Header: gmfbalnb.pls 120.5.12020000.3 2013/02/21 13:14:46 pmarada ship $ */
3
4 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 Start**/
5 /*** Bug 14178149 - commented below query as part of Performance improvement ***/
6 /*CURSOR cur_gl_get_balances
7 (
8 in_set_of_books number
9 , in_period_year number
10 , in_period_num number
11 , l_period_name VARCHAR2
12 , in_account_type varchar2
13 , in_currency_code varchar2
14 , in_actual_flag varchar2
15 , in_ytd_ptd number
16 , in_from_segments gmf_segment_values_tbl
17 , in_to_segments gmf_segment_values_tbl
18 , l_segment_delimiter VARCHAR2
19 , l_currency_code gl_sets_of_books.currency_code%TYPE -- Bug 4066174 Added
20 )
21 IS
22 SELECT /*+ index(glbal GL_BALANCES_N1 ) *
23 in_set_of_books,
24 glcc.chart_of_accounts_id,
25 glbal.period_year,
26 glbal.period_num,
27 glcc.account_type,
28 glbal.currency_code,
29 glcc.segment1||l_segment_delimiter||
30 glcc.segment2||l_segment_delimiter||
31 glcc.segment3||l_segment_delimiter||
32 glcc.segment4||l_segment_delimiter||
33 glcc.segment5||l_segment_delimiter||
34 glcc.segment6||l_segment_delimiter||
35 glcc.segment7||l_segment_delimiter||
36 glcc.segment8||l_segment_delimiter||
37 glcc.segment9||l_segment_delimiter||
38 glcc.segment10||l_segment_delimiter||
39 glcc.segment11||l_segment_delimiter||
40 glcc.segment12||l_segment_delimiter||
41 glcc.segment13||l_segment_delimiter||
42 glcc.segment14||l_segment_delimiter||
43 glcc.segment15||l_segment_delimiter||
44 glcc.segment16||l_segment_delimiter||
45 glcc.segment17||l_segment_delimiter||
46 glcc.segment18||l_segment_delimiter||
47 glcc.segment19||l_segment_delimiter||
48 glcc.segment20||l_segment_delimiter||
49 glcc.segment21||l_segment_delimiter||
50 glcc.segment22||l_segment_delimiter||
51 glcc.segment23||l_segment_delimiter||
52 glcc.segment24||l_segment_delimiter||
53 glcc.segment25||l_segment_delimiter||
54 glcc.segment26||l_segment_delimiter||
55 glcc.segment27||l_segment_delimiter||
56 glcc.segment28||l_segment_delimiter||
57 glcc.segment29||l_segment_delimiter||
58 glcc.segment30 code_combinations,
59 glbal.actual_flag,
60 DECODE(in_ytd_ptd, 0, (period_net_dr - period_net_cr),
61 1, (begin_balance_dr - begin_balance_cr) +
62 (period_net_dr - period_net_cr),
63 (begin_balance_dr - begin_balance_cr) +
64 (period_net_dr - period_net_cr)),
65 l_segment_delimiter
66 , glcc.code_combination_id ccid /*B12600219 - Fetching ccid of the account
67 FROM
68 gl_code_combinations glcc,
69 gl_balances glbal
70 WHERE
71 glbal.ledger_id = in_set_of_books /* this is used as ledger id INVCONV sschinch*
72 AND glbal.code_combination_id = glcc.code_combination_id
73 AND glbal.period_name = l_period_name /* bug13803220 added period_name to improve the performance *
74 AND glbal.period_year = in_period_year
75 AND glbal.period_num = in_period_num
76 AND glbal.currency_code = nvl( in_currency_code, l_currency_code )
77 --AND glbal.set_of_books_id = in_set_of_books
78 AND glbal.actual_flag = nvl(in_actual_flag, glbal.actual_flag)
79 AND glcc.account_type = nvl(in_account_type, glcc.account_type)
80 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)))
81 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)))
82 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)))
83 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)))
84 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)))
85 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)))
86 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)))
87 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)))
88 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)))
89 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)))
90 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)))
91 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)))
92 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)))
93 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)))
94 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)))
95 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)))
96 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)))
97 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)))
98 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)))
99 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)))
100 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)))
101 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)))
102 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)))
103 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)))
104 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)))
105 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)))
106 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)))
107 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)))
108 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)))
109 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)));
110 */
111 /*Bug#1260021 (START) - Cursor for building 30-segmented account with sequential segments*/
112 CURSOR cur_rebuild_30seg_acct
113 ( in_account_ccid number
114 , l_segment_delimiter VARCHAR2
115 )
116 IS
117 SELECT glcc.segment1||l_segment_delimiter||
118 glcc.segment2||l_segment_delimiter||
119 glcc.segment3||l_segment_delimiter||
120 glcc.segment4||l_segment_delimiter||
121 glcc.segment5||l_segment_delimiter||
122 glcc.segment6||l_segment_delimiter||
123 glcc.segment7||l_segment_delimiter||
124 glcc.segment8||l_segment_delimiter||
125 glcc.segment9||l_segment_delimiter||
126 glcc.segment10||l_segment_delimiter||
127 glcc.segment11||l_segment_delimiter||
128 glcc.segment12||l_segment_delimiter||
129 glcc.segment13||l_segment_delimiter||
130 glcc.segment14||l_segment_delimiter||
131 glcc.segment15||l_segment_delimiter||
132 glcc.segment16||l_segment_delimiter||
133 glcc.segment17||l_segment_delimiter||
134 glcc.segment18||l_segment_delimiter||
135 glcc.segment19||l_segment_delimiter||
136 glcc.segment20||l_segment_delimiter||
137 glcc.segment21||l_segment_delimiter||
138 glcc.segment22||l_segment_delimiter||
139 glcc.segment23||l_segment_delimiter||
140 glcc.segment24||l_segment_delimiter||
141 glcc.segment25||l_segment_delimiter||
142 glcc.segment26||l_segment_delimiter||
143 glcc.segment27||l_segment_delimiter||
144 glcc.segment28||l_segment_delimiter||
145 glcc.segment29||l_segment_delimiter||
146 glcc.segment30 code_combinations
147 FROM gl_code_combinations glcc
148 WHERE glcc.code_combination_id = in_account_ccid ;
149 /*Bug#1260021 (START) - Cursor for building 30-segmented account with sequential segments*/
150
151 PROCEDURE fetch_segment_values
152 (
153 p_from_segment IN VARCHAR2
154 , p_to_Segment IN VARCHAR2
155 , p_delimiter IN VARCHAR2
156 , x_from_segment IN OUT NOCOPY GMF_SEGMENT_VALUES_TBL
157 , x_to_segment IN OUT NOCOPY GMF_SEGMENT_VALUES_TBL
158 , x_no_of_segments OUT NOCOPY NUMBER /* Bug 14178149 */
159 )
160 IS
161 l_from_segment VARCHAR2(2000);
162 l_to_segment VARCHAR2(2000);
163 l_from_value VARCHAR2(150);
164 l_to_value VARCHAR2(150);
165 BEGIN
166 l_from_segment := p_from_segment;
167 l_to_segment := p_to_segment;
168 x_no_of_segments := 0; /* Bug 14178149 */
169
170 FOR i IN 1 .. 30
171 LOOP
172 l_from_value := SUBSTR( l_from_segment , 1, INSTR( l_from_segment, p_delimiter, 1 ) - 1 );
173 l_to_value := SUBSTR( l_to_segment , 1, INSTR( l_to_segment, p_delimiter, 1 ) - 1 );
174 l_from_segment := SUBSTR( l_from_segment, INSTR( l_from_segment, p_delimiter, 1 ) + 1 );
175 l_to_segment := SUBSTR( l_to_segment, INSTR( l_to_segment, p_delimiter, 1 ) + 1 );
176
177 /**** phiriyan Bug 9869501 - Start ****/
178 IF (l_from_value IS NULL AND l_to_value IS NULL) /* i.e Last segment */
179 THEN
180 IF (l_from_segment IS NOT NULL) THEN
181 l_from_value := l_from_segment;
182 l_from_segment := null;
183 END IF;
184
185 IF (l_to_segment IS NOT NULL) THEN
186 l_to_value := l_to_segment;
187 l_to_segment := null;
188 END IF;
189 END IF;
190 /**** phiriyan Bug 9869501 - End ****/
191 x_from_segment(i) := l_from_value;
192 x_to_segment(i) := l_to_value;
193 /* Bug 14178149 */
194 IF (l_from_value IS NULL AND x_no_of_segments = 0) THEN /* Bug 14178149 */
195 x_no_of_segments := i-2; /*bug 14034706 */
196 END IF;
197 l_from_value := null;
198 l_to_value := null;
199 END LOOP;
200 END fetch_segment_values;
201
202 /* bug 14178149, commented below procedure and moved the relevent code to proc_gl_get_balances procedure
203 PROCEDURE open_cur_gl_get_balances
204 (
205 in_set_of_books in number
206 , in_chart_of_accounts in number
207 , in_period_year in number
208 , in_period_num in number
209 , in_account_type in varchar2
210 , in_currency_code in varchar2
211 , start_segments in varchar2
212 , to_segments in varchar2
213 , in_actual_flag in varchar2
214 , in_ytd_ptd in number
215 )
216 IS
217 l_segment_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
218 segment_where_clause VARCHAR2(4000);
219 l_from_segments gmf_segment_values_tbl;
220 l_to_segments gmf_segment_values_tbl;
221 l_application_id fnd_application.application_id%TYPE;
222 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
223 l_currency_code gl_sets_of_books.currency_code%TYPE;
224 l_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
225
226 /*Bug#12600219 - START *
227 l_start_segments_ccid number;
228 l_to_segments_ccid number;
229 x_start_segments_new varchar2(2000);
230 x_to_segments_new varchar2(2000);
231 /*Bug#12600219 - END *
232
233 l_period_name gl_periods.period_name%TYPE;
234
235 BEGIN
236
237 /***** Bug 4066174 - Added the new queries below *****
238
239 -- Newly added Query 1 for Bug 4066174
240 SELECT application_id INTO l_application_id
241 FROM fnd_application
242 WHERE application_short_name = 'SQLGL' ;
243
244 SELECT currency_code,set_of_books_id, chart_of_accounts_id
245 INTO l_currency_code, l_set_of_books_id, l_chart_of_accounts_id
246 FROM gl_sets_of_books
247 WHERE set_of_books_id = in_set_of_books;
248
249 SELECT fifstr.concatenated_segment_delimiter
250 INTO l_segment_delimiter
251 FROM fnd_id_flex_structures fifstr
252 WHERE fifstr.id_flex_code = 'GL#'
253 AND fifstr.application_id = l_application_id
254 AND fifstr.id_flex_num = l_chart_of_accounts_id; --in_chart_of_accounts;
255
256 /***************************** Bug#12600219(START) ****************************
257 /* Rebuild 30 Segmented account using CCIds of From/To accounts */
258 /* (i)Identify the CCIds of From/To accounts *
259 l_start_segments_ccid := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
260 key_flex_code => 'GL#',
261 structure_number => l_chart_of_accounts_id,
262 validation_date => to_char(SYSDATE,FND_FLEX_EXT.DATE_FORMAT),
263 concatenated_segments => start_segments
264 );
265
266 l_to_segments_ccid := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
267 key_flex_code => 'GL#',
268 structure_number => l_chart_of_accounts_id,
269 validation_date => to_char(SYSDATE,FND_FLEX_EXT.DATE_FORMAT),
270 concatenated_segments => to_segments
271 );
272
273 /* (ii)Rebuild 30 segmented From/To accounts *
274 OPEN cur_rebuild_30seg_acct(l_start_segments_ccid, l_segment_delimiter);
275 FETCH cur_rebuild_30seg_acct INTO x_start_segments_new;
276 CLOSE cur_rebuild_30seg_acct;
277
278 OPEN cur_rebuild_30seg_acct(l_to_segments_ccid, l_segment_delimiter);
279 FETCH cur_rebuild_30seg_acct INTO x_to_segments_new;
280 CLOSE cur_rebuild_30seg_acct;
281
282 gmf_util.trace( ' From/To after rebld: ('||to_char(l_start_segments_ccid)||' / '||to_char(l_to_segments_ccid)
283 ||') '||x_start_segments_new||' / '||x_to_segments_new
284 ,3 ,C_LOG_FILE);
285
286 /***************************** Bug#12600219(END) ****************************/
287
288 /*Bug#12600219 - Replaced this call with following
289 fetch_segment_values (start_segments, to_segments, l_segment_delimiter, l_from_segments, l_to_segments ); *
290
291 fetch_segment_values (x_start_segments_new, x_to_segments_new, l_segment_delimiter
292 , l_from_segments, l_to_segments ); /*Bug#12600219 *
293
294 /***** Bug 4066174 - Performance Improvement - Modified the below cursor and its arguments. *****/
295 /* bug 13803220 fetching period_name and passing to the get balances cursor to improve the performance *
296 SELECT gp.period_name INTO l_period_name
297 FROM gl_periods gp, gl_ledgers gl
298 WHERE gp.period_year = in_period_year
299 AND gp.period_num = in_period_num
300 and gp.PERIOD_SET_NAME= gl.PERIOD_SET_NAME
301 and gl.ledger_id = in_set_of_books ;
302
303 gmf_util.trace( ' Periord name = '||l_period_name ,3 ,C_LOG_FILE);
304 OPEN cur_gl_get_balances (
305 in_set_of_books
306 -- , in_chart_of_accounts -- We don't need anymore as we are spliting the query
307 , in_period_year
308 , in_period_num
309 , l_period_name
310 , in_account_type
311 , in_currency_code
312 , in_actual_flag
313 , in_ytd_ptd
314 , l_from_segments
315 , l_to_segments
316 , l_segment_delimiter -- Bug 4066174 Added
317 , l_currency_code -- Bug 4066174 Added
318 );
319 END open_cur_gl_get_balances;
320 /* end for bug 14178149 */
321 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 End **/
322
323
324 PROCEDURE proc_gl_get_balances(
325 in_set_of_books in out NOCOPY number,
326 in_chart_of_accounts in out NOCOPY number,
327 in_period_year in out NOCOPY number,
328 in_period_num in out NOCOPY number,
329 in_account_type in out NOCOPY varchar2,
330 in_currency_code in out NOCOPY varchar2,
331 start_segments in varchar2,
332 to_segments in out NOCOPY varchar2,
333 in_actual_flag in out NOCOPY varchar2,
334 in_ytd_ptd in number,
335 amount out NOCOPY number,
336 segment_delimiter out NOCOPY varchar2,
337 row_to_fetch in out NOCOPY number,
338 error_status out NOCOPY number,
339 to_segments_ccid out NOCOPY number, /*Bug#12600219*/
340 cur_gl_get_balances in out NOCOPY GMF_GL_GET_BALANCES.cur_gl_get_balances_rc
341 ) IS
342
343 l_segment_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
344 segment_where_clause VARCHAR2(4000);
345 l_from_segments gmf_segment_values_tbl;
346 l_to_segments gmf_segment_values_tbl;
347 l_application_id fnd_application.application_id%TYPE;
348 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
349 l_currency_code gl_sets_of_books.currency_code%TYPE;
350 l_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
351
352 /*Bug#12600219 - START */
353 l_start_segments_ccid number;
354 l_to_segments_ccid number;
355 x_start_segments_new varchar2(2000);
356 x_to_segments_new varchar2(2000);
357 /*Bug#12600219 - END */
358
359 l_period_name gl_periods.period_name%TYPE;
360 l_no_of_segments NUMBER :=0;
361 l_sql_stmt VARCHAR2(4000);
362
363 BEGIN /* Beginning of procedure proc_gl_get_balances*/
364
365 IF( NOT cur_gl_get_balances%ISOPEN )
366 THEN
367 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 Start **/
368 /* commented this call bug 14178149
369 open_cur_gl_get_balances
370 (
371 in_set_of_books
372 , in_chart_of_accounts
373 , in_period_year
374 , in_period_num
375 , in_account_type
376 , in_currency_code
377 , start_segments
378 , to_segments
379 , in_actual_flag
380 , in_ytd_ptd
381 ); */
382 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 End **/
383 /***** Bug 4066174 - Added the new queries below *****/
384
385 -- Newly added Query 1 for Bug 4066174
386 SELECT application_id INTO l_application_id
387 FROM fnd_application
388 WHERE application_short_name = 'SQLGL' ;
389
390 SELECT currency_code,set_of_books_id, chart_of_accounts_id
391 INTO l_currency_code, l_set_of_books_id, l_chart_of_accounts_id
392 FROM gl_sets_of_books
393 WHERE set_of_books_id = in_set_of_books;
394
395 SELECT fifstr.concatenated_segment_delimiter
396 INTO l_segment_delimiter
397 FROM fnd_id_flex_structures fifstr
398 WHERE fifstr.id_flex_code = 'GL#'
399 AND fifstr.application_id = l_application_id
400 AND fifstr.id_flex_num = l_chart_of_accounts_id; --in_chart_of_accounts;
401
402 /***************************** Bug#12600219(START) ****************************/
403 /* Rebuild 30 Segmented account using CCIds of From/To accounts */
404 /* (i)Identify the CCIds of From/To accounts */
405 l_start_segments_ccid := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
406 key_flex_code => 'GL#',
407 structure_number => l_chart_of_accounts_id,
408 validation_date => to_char(SYSDATE,FND_FLEX_EXT.DATE_FORMAT),
409 concatenated_segments => start_segments
410 );
411
412 l_to_segments_ccid := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
413 key_flex_code => 'GL#',
414 structure_number => l_chart_of_accounts_id,
415 validation_date => to_char(SYSDATE,FND_FLEX_EXT.DATE_FORMAT),
416 concatenated_segments => to_segments
417 );
418
419 /* (ii)Rebuild 30 segmented From/To accounts */
420 OPEN cur_rebuild_30seg_acct(l_start_segments_ccid, l_segment_delimiter);
421 FETCH cur_rebuild_30seg_acct INTO x_start_segments_new;
422 CLOSE cur_rebuild_30seg_acct;
423
424 OPEN cur_rebuild_30seg_acct(l_to_segments_ccid, l_segment_delimiter);
425 FETCH cur_rebuild_30seg_acct INTO x_to_segments_new;
426 CLOSE cur_rebuild_30seg_acct;
427
428 gmf_util.trace( ' From/To after rebld: ('||to_char(l_start_segments_ccid)||' / '||to_char(l_to_segments_ccid)
429 ||') '||x_start_segments_new||' / '||x_to_segments_new ,3 ,C_LOG_FILE);
430 /***************************** Bug#12600219(END) ****************************/
431
432 /*Bug#12600219 - Replaced this call with following
433 fetch_segment_values (start_segments, to_segments, l_segment_delimiter, l_from_segments, l_to_segments ); */
434
435 fetch_segment_values (x_start_segments_new, x_to_segments_new, l_segment_delimiter
436 , l_from_segments, l_to_segments , l_no_of_segments ); /*Bug#12600219 */
437
438 /***** Bug 4066174 - Performance Improvement - Modified the below cursor and its arguments. *****/
439 /* bug 13803220 fetching period_name and passing to the get balances cursor to improve the performance */
440 SELECT gp.period_name INTO l_period_name
441 FROM gl_periods gp, gl_ledgers gl
442 WHERE gp.period_year = in_period_year
443 AND gp.period_num = in_period_num
444 AND gp.PERIOD_SET_NAME= gl.PERIOD_SET_NAME
445 AND gl.ledger_id = in_set_of_books ;
446
447 gmf_util.trace( ' Periord name = '||l_period_name ,3 ,C_LOG_FILE);
448
449 /* Create Dynamic Sql for Getting Balances bug 14178149 */
450 l_sql_stmt := 'SELECT '||
451 in_set_of_books ||','||
452 'glcc.chart_of_accounts_id,' ||
453 'glbal.period_year,' ||
454 'glbal.period_num,' ||
455 'glcc.account_type,' ||
456 'glbal.currency_code,' ||
457 'glcc.segment1||:l_segment_delimiter||' ||
458 'glcc.segment2||:l_segment_delimiter||' ||
459 'glcc.segment3||:l_segment_delimiter||' ||
460 'glcc.segment4||:l_segment_delimiter||' ||
461 'glcc.segment5||:l_segment_delimiter||' ||
462 'glcc.segment6||:l_segment_delimiter||' ||
463 'glcc.segment7||:l_segment_delimiter||' ||
464 'glcc.segment8||:l_segment_delimiter||' ||
465 'glcc.segment9||:l_segment_delimiter||' ||
466 'glcc.segment10||:l_segment_delimiter||' ||
467 'glcc.segment11||:l_segment_delimiter||' ||
468 'glcc.segment12||:l_segment_delimiter||' ||
469 'glcc.segment13||:l_segment_delimiter||' ||
470 'glcc.segment14||:l_segment_delimiter||' ||
471 'glcc.segment15||:l_segment_delimiter||' ||
472 'glcc.segment16||:l_segment_delimiter||' ||
473 'glcc.segment17||:l_segment_delimiter||' ||
474 'glcc.segment18||:l_segment_delimiter||' ||
475 'glcc.segment19||:l_segment_delimiter||' ||
476 'glcc.segment20||:l_segment_delimiter||' ||
477 'glcc.segment21||:l_segment_delimiter||' ||
478 'glcc.segment22||:l_segment_delimiter||' ||
479 'glcc.segment23||:l_segment_delimiter||' ||
480 'glcc.segment24||:l_segment_delimiter||' ||
481 'glcc.segment25||:l_segment_delimiter||' ||
482 'glcc.segment26||:l_segment_delimiter||' ||
483 'glcc.segment27||:l_segment_delimiter||' ||
484 'glcc.segment28||:l_segment_delimiter||' ||
485 'glcc.segment29||:l_segment_delimiter||' ||
486 'glcc.segment30 code_combinations,' ||
487 'glbal.actual_flag,'||
488 'DECODE('||in_ytd_ptd||',0,(glbal.period_net_dr - glbal.period_net_cr),'||
489 '1,(glbal.begin_balance_dr - glbal.begin_balance_cr)+(glbal.period_net_dr - glbal.period_net_cr),'||
490 '(glbal.begin_balance_dr - glbal.begin_balance_cr)+(period_net_dr - period_net_cr) ) amount,'''
491 ||l_segment_delimiter||
492 ''',glcc.code_combination_id ccid'||
493 ' FROM ' ||
494 ' gl_code_combinations glcc, '||
495 ' gl_balances glbal '||
496 ' WHERE '||
497 'glbal.ledger_id = '|| in_set_of_books||
498 ' AND glbal.code_combination_id = glcc.code_combination_id' ||
499 ' AND glbal.period_name = :l_period_name ' ||
500 ' AND glbal.period_year = '|| in_period_year ||
501 ' AND glbal.period_num = '|| in_period_num ||
502 ' AND glbal.currency_code = nvl(:in_currency_code, :l_currency_code)'||
503 ' AND glbal.actual_flag = nvl( '''|| in_actual_flag ||''',glbal.actual_flag)'||
504 ' AND glcc.account_type = nvl( :in_account_type,glcc.account_type)'||
505 ' AND (glcc.segment1>='''||l_from_segments(1) ||
506 ''' and glcc.segment1<='''||l_to_segments(1)||''')';
507
508 /* Complete the where clause to add segments bug 14034706 */
509 FOR i in 2..l_no_of_segments LOOP
510 l_sql_stmt := l_sql_stmt||' AND (glcc.segment'||i||'>= '''||l_from_segments(i)||''' and glcc.segment'||i||'<='''|| l_to_segments(i)||''')';
511 END LOOP;
512
513 FND_FILE.PUT_LINE( FND_FILE.OUTPUT, 'Sql Statement: '||l_sql_stmt);
514 OPEN cur_gl_get_balances FOR l_sql_stmt USING
515 l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,
516 l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,
517 l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,
518 l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,
519 l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,
520 l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,
521 l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,l_segment_delimiter,
522 l_segment_delimiter, l_period_name, in_currency_code,l_currency_code,in_account_type;
523 /* End for bug 14178149 */
524 END IF;
525
526 FETCH cur_gl_get_balances
527 INTO
528 in_set_of_books,
529 in_chart_of_accounts,
530 in_period_year,
531 in_period_num,
532 in_account_type,
533 in_currency_code,
534 to_segments,
535 in_actual_flag,
536 amount,
537 segment_delimiter,
538 to_segments_ccid /*B12600219*/
539 ;
540
541 IF( cur_gl_get_balances%NOTFOUND )
542 THEN
543 error_status := 100;
544 /*B12600219 - Added Debug message */
545 gmf_util.trace( ' proc_gl_get_balances(): error_status = '||error_status||' [From/To acct:'||start_segments||' / '||to_segments || ']'
546 ,3 ,C_LOG_FILE);
547 /*B12600219 - Added Debug message */
548 END IF;
549
550 IF( ( cur_gl_get_balances%NOTFOUND ) OR ( row_to_fetch = 1 ) )
551 THEN
552 CLOSE cur_gl_get_balances;
553 END IF;
554
555 EXCEPTION
556
557 WHEN others THEN
558 error_status := SQLCODE;
559
560 END proc_gl_get_balances; /* End of procedure proc_gl_get_balances*/
561
562 /** Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 End **/
563
564 PROCEDURE initialize(
565 in_set_of_books_id IN NUMBER
566 )
567 IS
568
569 BEGIN
570 /* Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 Start */
571
572 RETURN;
573
574 /* Anand thiyagarajan GL Expense Allocation Enhancement 26-04-2004 End */
575
576 END initialize;
577
578 END GMF_GL_GET_BALANCES; /* END GMF_GL_GET_BALANCES*/