[Home] [Help]
PACKAGE BODY: APPS.GL_RX_TRIAL_BALANCE_PKG
Source
1 package body gl_rx_trial_balance_pkg as
2 /* $Header: glurxtbb.pls 120.5 2005/05/05 01:43:04 kvora ship $ */
3
4 PROCEDURE create_rows (
5 errbuf out NOCOPY varchar2,
6 retcode out NOCOPY varchar2,
7 p_ledger_name in varchar2 ,
8 p_period_name in varchar2 ,
9 p_account_from in varchar2 default null,
10 p_account_to in varchar2 default null,
11 p_balancing_value in varchar2 default null,
12 p_currency_code in varchar2 default null,
13 p_translated_flag in varchar2 default 'N',
14 p_summary_flag in varchar2 default 'N',
15 p_summary_digits in number default 1,
16 p_statutory_rfj_flag in varchar2 default 'N') IS
17
18 l_request_id FND_CONCURRENT_REQUESTS.request_id%type;
19 l_ledger_id GL_LEDGERS.ledger_id%type;
20 l_functional_currency GL_LEDGERS.currency_code%type;
21 l_currency_code GL_LEDGERS.currency_code%type;
22 l_chart_of_accounts_id GL_LEDGERS.chart_of_accounts_id%type;
23 l_fiscal_year GL_PERIOD_STATUSES.period_year%type;
24 l_first_period_name GL_PERIOD_STATUSES.period_name%type;
25 l_adjustment_flag GL_PERIOD_STATUSES.adjustment_period_flag%type;
26 l_balancing_where varchar2(240);
27 l_account_from_where varchar2(240);
28 l_account_to_where varchar2(240);
29 l_translate_where varchar2(240);
30 l_application_id FND_APPLICATION.application_id%type := 101;
31 l_id_flex_code FND_ID_FLEXS.id_flex_code%type := 'GL#';
32 l_segnum FND_ID_FLEX_SEGMENTS.segment_num%type;
33 l_segname FND_ID_FLEX_SEGMENTS.segment_name%type;
34 l_prompt FND_ID_FLEX_SEGMENTS_TL.form_left_prompt%type;
35 l_balancing_value_set FND_FLEX_VALUE_SETS.flex_value_set_name%type;
36 l_account_value_set FND_FLEX_VALUE_SETS.flex_value_set_name%type;
37 l_balancing_segment_column FND_ID_FLEX_SEGMENTS.application_column_name%type;
38 l_account_segment_column FND_ID_FLEX_SEGMENTS.application_column_name%type;
39 l_balancing_description FND_FLEX_VALUES_TL.description%type;
40 l_account_description FND_FLEX_VALUES_TL.description%type;
41 l_balancing GL_CODE_COMBINATIONS.segment1%type;
42 l_account GL_CODE_COMBINATIONS.segment1%type;
43 l_rowid rowid;
44 l_dyn_stmt VARCHAR2(4000);
45 l_dyn_cursor number;
46 l_dyn_rows number;
47 segment_not_found exception;
48 debug_mode VARCHAR2(1);
49
50 CURSOR c1 IS
51 SELECT rowid,
52 balancing_segment,
53 account_segment
54 FROM gl_rx_trial_balance_itf
55 WHERE request_id = l_request_id;
56
57 BEGIN
58 --
59 -- Figure out the debug_mode
60 -- This program will be running in debug mode when the Utilities: Trace
61 -- Has been set to Yes.
62 -- We cannot use "additional parameter" approach since the RX does not support hidden parameters
63 --
64 FND_PROFILE.get('SQL_TRACE',debug_mode);
65 IF (debug_mode = 'Y') THEN
66 -- dbms_output.put_line('> RXGLTB.create_rows');
67 NULL;
68 END IF;
69 --
70 -- use api to get the request id
71 --
72 l_request_id := FND_GLOBAL.conc_request_id();
73 --
74 -- get ledgers and period year info
75 --
76 SELECT led.ledger_id,
77 led.currency_code,
78 led.chart_of_accounts_id,
79 ps.period_year
80 INTO l_ledger_id,
81 l_functional_currency,
82 l_chart_of_accounts_id,
83 l_fiscal_year
84 FROM gl_ledgers led,
85 gl_period_statuses ps
86 WHERE led.name = p_ledger_name
87 AND led.ledger_id = ps.ledger_id
88 AND ps.application_id = l_application_id
89 AND ps.period_name = p_period_name;
90
91 IF (p_currency_code is NULL) THEN
92 l_currency_code := l_functional_currency;
93 ELSE
94 l_currency_code := p_currency_code;
95 END IF;
96 --
97 -- get year's first period info
98 --
99 SELECT period_name,
100 adjustment_period_flag
101 INTO l_first_period_name,
102 l_adjustment_flag
103 FROM gl_period_statuses
104 WHERE application_id = l_application_id
105 AND ledger_id = l_ledger_id
106 AND period_year = l_fiscal_year
107 AND period_num = ( SELECT MIN(period_num)
108 FROM gl_period_statuses
109 WHERE application_id = l_application_id
110 AND ledger_id = l_ledger_id
111 AND period_year = l_fiscal_year);
112 --
113 -- use apis to get the BALANCING segment info
114 -- fnd_flex_apis.get_qualifier_segnum gets the segment number
115 -- fnd_flex_apis.get_segment_info gets the column name
116 --
117 IF (FND_FLEX_APIS.get_qualifier_segnum(l_application_id,
118 l_id_flex_code,
119 l_chart_of_accounts_id,
120 'GL_BALANCING',
121 l_segnum)) THEN
122 IF (FND_FLEX_APIS.get_segment_info( l_application_id,
123 l_id_flex_code,
124 l_chart_of_accounts_id,
125 l_segnum,
126 l_balancing_segment_column,
127 l_segname,
128 l_prompt,
129 l_balancing_value_set)) THEN
130 IF (debug_mode = 'Y') THEN
131 -- dbms_output.put_line('successfully identified the balancing segment');
132 NULL;
133 END IF;
134 ELSE
135 --
136 -- Failure in get_segment_info
137 --
138 IF (debug_mode = 'Y') THEN
139 -- dbms_output.put_line('failure at FND_FLEX_APIS.get_segment_info for balancing segment');
140 NULL;
141 END IF;
142 raise segment_not_found;
143 END IF;
144 ELSE
145 IF (debug_mode = 'Y') THEN
146 -- dbms_output.put_line('failure at FND_FLEX_APIS.get_qualifier segnum for balancing segment');
147 NULL;
148 END IF;
149 raise segment_not_found;
150 END IF;
151 --
152 -- use apis to get the ACCOUNT segment info
153 -- fnd_flex_apis.get_qualifier_segnum gets the segment number
154 -- fnd_flex_apis.get_segment_info gets the column name
155 --
156 IF (FND_FLEX_APIS.get_qualifier_segnum( l_application_id,
157 l_id_flex_code,
158 l_chart_of_accounts_id,
159 'GL_ACCOUNT',
160 l_segnum)) THEN
161 IF (FND_FLEX_APIS.get_segment_info (l_application_id,
162 l_id_flex_code,
163 l_chart_of_accounts_id,
164 l_segnum,
165 l_account_segment_column,
166 l_segname,
167 l_prompt,
168 l_account_value_set)) THEN
169 IF (debug_mode = 'Y') THEN
170 -- dbms_output.put_line('successfully identified the account segment');
171 NULL;
172 END IF;
173 ELSE
174 IF (debug_mode = 'Y') THEN
175 -- dbms_output.put_line('failure at FND_FLEX_APIS.get_segment_info for account segment');
176 NULL;
177 END IF;
178 raise segment_not_found;
179 END IF;
180 ELSE
181 IF (debug_mode = 'Y') THEN
182 -- dbms_output.put_line('failure at FND_FLEX_APIS.get_qualifier_segnum fore account segment');
183 NULL;
184 END IF;
185 raise segment_not_found;
186 END IF;
187 --
188 -- set the where clause conditions for the balancing segment and account
189 --
190 IF (p_balancing_value is not null) THEN
191 l_balancing_where := ' and gcc.'||l_balancing_segment_column||' = '||''''||p_balancing_value||'''';
192 IF (debug_mode = 'Y') THEN
193 -- dbms_output.put_line('l_balancing_where = '||l_balancing_where);
194 NULL;
195 END IF;
196 END IF;
197
198 IF (p_account_from is not null) THEN
199 l_account_from_where := ' and gcc.'||l_account_segment_column||' >= '||''''||p_account_from||'''';
200 IF (debug_mode = 'Y') THEN
201 -- dbms_output.put_line('l_account_from_where = '||l_account_from_where);
202 NULL;
203 END IF;
204 END IF;
205
206 IF (p_account_to is not null) THEN
207 l_account_to_where := ' and gcc.'||l_account_segment_column||' <= '||''''||p_account_to||'''';
208 IF (debug_mode = 'Y') THEN
209 -- dbms_output.put_line('l_account_to_where = '||l_account_to_where);
210 NULL;
211 END IF;
212 END IF;
213 --
214 -- set the where clause condition for the translated_flag
215 --
216 IF (l_currency_code in (l_functional_currency,'STAT')) THEN
217 l_translate_where := ' and bal.translated_flag is null';
218 ELSE
219 IF (p_translated_flag = 'N') THEN
220 l_translate_where := ' and bal.translated_flag = '||''''||'R'||'''';
221 ELSE
222 l_translate_where := ' and bal.translated_flag in ('||''''||'Y'||''''||','||''''||'N'||''''||')';
223 END IF;
224 END IF;
225 IF (debug_mode = 'Y') THEN
226 -- dbms_output.put_line('l_translate_where = '||l_translate_where);
227 NULL;
228 END IF;
229 --
230 -- set the sql statement for dynamic sql
231 --
232 l_dyn_stmt := 'insert into gl_rx_trial_balance_itf (
233 request_id,
234 period_name,
235 ledger_id,
236 ledger_name,
237 chart_of_accounts_id,
238 currency_code,
239 balancing_segment,
240 account_segment,
241 summary_flag,
242 begin_year_balance_dr,
243 begin_year_balance_cr,
244 begin_adj_period_net_dr,
245 begin_adj_period_net_cr,
246 begin_period_balance_dr,
247 begin_period_balance_cr,
248 period_net_dr,
249 period_net_cr)
250 SELECT
251 '||to_char(l_request_id)||',
252 '||''''||p_period_name||''''||',
253 '||to_char(l_ledger_id)||',
254 '||''''||p_ledger_name||''''||',
255 '||to_char(l_chart_of_accounts_id)||',
256 '||''''||l_currency_code||''''||',
257 gcc.'||l_balancing_segment_column||',
258 '||'decode('||''''||p_summary_flag||''''||','||
259 ''''||'N'||''''||','||'gcc.'||l_account_segment_column||','||
260 ''''||'Y'||''''||',substr(gcc.'||l_account_segment_column||
261 ',1,'||to_char(nvl(p_summary_digits,1))||')),
262 '||''''||p_summary_flag||''''||',
263 sum(decode(period_name,'||''''||l_first_period_name||''''||',
264 nvl(begin_balance_dr,0), 0)),
265 sum(decode(period_name,'||''''||l_first_period_name||''''||',
266 nvl(begin_balance_cr,0), 0)),
267 sum(decode('||''''||l_adjustment_flag||''''||',
268 '||''''||'Y'||''''||
269 ', decode(period_name,'||''''||l_first_period_name||''''||',
270 nvl(period_net_dr,0), 0), 0)),
271 sum(decode('||''''||l_adjustment_flag||''''||',
272 '||''''||'Y'||''''||
273 ', decode(period_name,'||''''||l_first_period_name||''''||',
274 nvl(period_net_cr,0), 0), 0)),
275 sum(decode(period_name,'||''''||p_period_name||''''||',
276 nvl(begin_balance_dr,0), 0)),
277 sum(decode(period_name,'||''''||p_period_name||''''||',
278 nvl(begin_balance_cr,0), 0)),
279 sum(decode(period_name,'||''''||p_period_name||''''||',
280 nvl(period_net_dr,0), 0)),
281 sum(decode(period_name,'||''''||p_period_name||''''||',
282 nvl(period_net_cr,0), 0))
283 FROM gl_balances bal,
284 gl_code_combinations gcc
285 WHERE bal.code_combination_id = gcc.code_combination_id
286 AND bal.ledger_id = '||to_char(l_ledger_id)||'
287 AND bal.currency_code = '||''''||l_currency_code||''''||'
288 AND bal.period_name in ('||''''||p_period_name||''''||','||
289 ''''||l_first_period_name||''''||')
290 AND bal.actual_flag = '||''''||'A'||''''||'
291 AND gcc.chart_of_accounts_id = '||to_char(l_chart_of_accounts_id)||'
292 AND gcc.template_id is null'||
293 l_balancing_where||
294 l_account_from_where||
295 l_account_to_where||
296 l_translate_where||'
297 GROUP BY
298 gcc.'||l_balancing_segment_column||',
299 decode('||''''||p_summary_flag||''''||','||
300 ''''||'N'||''''||',gcc.'||l_account_segment_column||','||
301 ''''||'Y'||''''||',substr(gcc.'||l_account_segment_column||',1,'||
302 to_char(nvl(p_summary_digits,1))||'))
303 HAVING
304 sum(decode(period_name,'||''''||l_first_period_name||''''||',
305 nvl(begin_balance_dr,0), 0)) <> 0 OR
306 sum(decode(period_name,'||''''||l_first_period_name||''''||',
307 nvl(begin_balance_cr,0), 0)) <> 0 OR
308 sum(decode('||''''||l_adjustment_flag||''''||',
309 '||''''||'Y'||''''||',decode(period_name,'||''''||l_first_period_name||''''||',
310 nvl(period_net_dr,0), 0), 0)) <> 0 OR
311 sum(decode('||''''||l_adjustment_flag||''''||',
312 '||''''||'Y'||''''||',decode(period_name,'||''''||l_first_period_name||''''||',
313 nvl(period_net_cr,0), 0), 0)) <> 0 OR
314 sum(decode(period_name,'||''''||p_period_name||''''||',
315 nvl(begin_balance_dr,0), 0)) <> 0 OR
316 sum(decode(period_name,'||''''||p_period_name||''''||',
317 nvl(begin_balance_cr,0), 0)) <> 0 OR
318 sum(decode(period_name,'||''''||p_period_name||''''||',
319 nvl(period_net_dr,0), 0)) <> 0 OR
320 sum(decode(period_name,'||''''||p_period_name||''''||',
321 nvl(period_net_cr,0), 0)) <> 0';
322
323 IF (debug_mode = 'Y') THEN
324 -- dbms_output.put_line('l_dyn_stm = '||l_dyn_stmt);
325 NULL;
326 END IF;
327 --
328 -- execute the dynamic sql
329 --
330 l_dyn_cursor := dbms_sql.open_cursor;
331 dbms_sql.parse(l_dyn_cursor, l_dyn_stmt, dbms_sql.native);
332 l_dyn_rows := dbms_sql.execute(l_dyn_cursor);
333 dbms_sql.close_cursor(l_dyn_cursor);
334 IF (debug_mode = 'Y') THEN
335 -- dbms_output.put_line('Done with the main cursor');
336 NULL;
337 END IF;
338 --
339 -- if the statutory_rfj_flag = 'Y'
340 -- and the beginning adjustment period is open
341 -- subtract the previous year's balances
342 -- This is because companies cannot run the Closing Journals functionality before
343 -- they have recorded all the activity to the closing period.
344 -- In some countries you can keep the closing period open for months.
345 -- Until you run the Closing Journals, the balances are incorrectly rolled forward.
346 -- This update updates the opening balances to 0, pretending that there are no opening balances
347 -- (as if the Closing Journals program was already ran)
348 --
349 IF (p_statutory_rfj_flag = 'Y') THEN
350 IF (debug_mode = 'Y') THEN
351 -- dbms_output.put_line('Subtracting the opening year balances');
352 NULL;
353 END IF;
354 UPDATE gl_rx_trial_balance_itf set
355 begin_year_balance_dr = 0,
356 begin_year_balance_cr = 0,
357 begin_period_balance_dr = (begin_period_balance_dr - begin_year_balance_dr),
358 begin_period_balance_cr = (begin_period_balance_cr - begin_year_balance_cr)
359 WHERE request_id = l_request_id;
360 END IF;
361 --
362 -- update the descriptions and calculated columns in the interface table
363 -- a cursor is needed due to pragma restrictions
364 -- on fa_rx_shared_pkg.get_flex_val_meaning
365 --
366 IF (debug_mode = 'Y') THEN
367 -- dbms_output.put_line('Opening the update cursor');
368 NULL;
369 END IF;
370 OPEN c1;
371 LOOP
372 FETCH c1 INTO l_rowid, l_balancing, l_account;
373 exit when c1%notfound;
374 l_balancing_description := fa_rx_shared_pkg.get_flex_val_meaning(NULL, l_balancing_value_set, l_balancing);
375 l_account_description := fa_rx_shared_pkg.get_flex_val_meaning(NULL, l_account_value_set, l_account);
376 UPDATE gl_rx_trial_balance_itf SET
377 balancing_description = l_balancing_description,
378 account_description = l_account_description,
379 prior_periods_net_dr = (begin_period_balance_dr - (begin_year_balance_dr + begin_adj_period_net_dr)),
380 prior_periods_net_cr = (begin_period_balance_cr - (begin_year_balance_cr + begin_adj_period_net_cr)),
381 year_to_date_balance_dr = (begin_period_balance_dr + period_net_dr),
382 year_to_date_balance_cr = (begin_period_balance_cr + period_net_cr),
383 year_to_date_net_difference_dr = DECODE(SIGN((begin_period_balance_dr + period_net_dr) -
384 (begin_period_balance_cr + period_net_cr)), 1,
385 ((begin_period_balance_dr + period_net_dr) -
386 (begin_period_balance_cr + period_net_cr)), 0),
387 year_to_date_net_difference_cr = DECODE(SIGN((begin_period_balance_dr + period_net_dr) -
388 (begin_period_balance_cr + period_net_cr)), -1,
389 abs((begin_period_balance_dr + period_net_dr) -
390 (begin_period_balance_cr + period_net_cr)), 0)
391 WHERE
392 rowid = l_rowid;
393 END LOOP;
394 CLOSE C1;
395
396 COMMIT;
397 retcode := 0;
398 IF (debug_mode = 'Y') THEN
399 -- dbms_output.put_line('< RXGLTB.create_rows');
400 NULL;
401 END IF;
402 EXCEPTION
403 WHEN segment_not_found THEN
404 retcode := 2;
405 WHEN others THEN
406 errbuf := SQLERRM;
407 retcode := 2;
408 end create_rows;
409 end gl_rx_trial_balance_pkg;