[Home] [Help]
PACKAGE BODY: APPS.FV_TBAL_BY_TS
Source
1 PACKAGE BODY fv_tbal_by_ts AS
2 /* $Header: FVTBTRSB.pls 120.3.12010000.1 2008/07/28 06:32:03 appldev ship $ */
3
4 g_module_name VARCHAR2(100) := 'fv.plsql.fv_tbal_by_ts.';
5 errbuf VARCHAR2(250);
6 retcode NUMBER;
7 g_sob_id NUMBER;
8
9 PROCEDURE purge_summary_txns;
10 PROCEDURE group_by_columns(x_report_id IN NUMBER,
11 x_attribute_set IN VARCHAR2,
12 x_group_by OUT NOCOPY VARCHAR2);
13
14 PROCEDURE main (errbuf OUT NOCOPY VARCHAR2,
15 retcode OUT NOCOPY NUMBER ,
16 p_ledger_id NUMBER,
17 p_treasury_symbol_low VARCHAR2,
18 p_treasury_symbol_high VARCHAR2,
19 p_period_name VARCHAR2,
20 p_amount_type VARCHAR2,
21 p_currency_code VARCHAR2,
22 p_report_id NUMBER ,
23 p_attribute_set VARCHAR2 ,
24 p_output_format VARCHAR2)
25
26 IS
27
28 CURSOR Cur_ledger
29 IS
30 SELECT name, chart_of_accounts_id FROM gl_ledgers_public_v
31 WHERE ledger_id = p_ledger_id;
32
33 CURSOR Cur_seg_name (p_chart_of_accounts_id number, p_attribute_type varchar2)
34 IS
35 /* Bug 6244171 - Current query for cursor commented out and new select statement added to fetch correct segment*/
36 /* SELECT fifsv.application_column_name
37 FROM
38 fnd_id_flex_segments_vl fifsv,
39 fnd_segment_attribute_values fsav
40 WHERE
41 fifsv.application_id = 101
42 AND fifsv.id_flex_code = 'GL#'
43 AND fifsv.id_flex_num = fsav.id_flex_num
44 AND fifsv.id_flex_num = p_chart_of_accounts_id
45 AND fsav.segment_attribute_type = p_attribute_type; */
46
47 SELECT application_column_name
48 FROM
49 fnd_segment_attribute_values
50 WHERE
51 application_id = 101
52 AND id_flex_code = 'GL#'
53 AND id_flex_num = p_chart_of_accounts_id
54 AND segment_attribute_type = p_attribute_type
55 AND attribute_value = 'Y';
56
57 l_module_name VARCHAR2(200) := g_module_name || 'main';
58 v_ledger_name gl_ledgers_public_v.name%TYPE;
59 v_chart_of_accounts_id NUMBER;
60 v_acc_seg_name VARCHAR2(25);
61 v_bal_seg_name VARCHAR2(25);
62 v_statement VARCHAR2(32767);
63 v_select_begin_balance VARCHAR2(2000);
64 v_select_period_dr VARCHAR2(2000);
65 v_select_period_cr VARCHAR2(2000);
66 v_period_list VARCHAR2(2000);
67 v_first_period VARCHAR2(15);
68 v_err_buf VARCHAR2(132);
69 v_group_by VARCHAR2(2000);
70 v_req_id NUMBER;
71
72 BEGIN
73
74 g_sob_id := p_ledger_id;
75 retcode := 0;
76
77 OPEN cur_ledger;
78 FETCH cur_ledger INTO v_ledger_name, v_chart_of_accounts_id;
79 CLOSE cur_ledger;
80
81 OPEN cur_seg_name (v_chart_of_accounts_id, 'GL_ACCOUNT');
82 FETCH cur_seg_name INTO v_acc_seg_name;
83 CLOSE cur_seg_name;
84
85 OPEN cur_seg_name (v_chart_of_accounts_id, 'GL_BALANCING');
86 FETCH cur_seg_name INTO v_bal_seg_name ;
87 CLOSE cur_seg_name;
88
89 purge_summary_txns;
90
91 group_by_columns(p_report_id, p_attribute_set, v_group_by);
92
93 IF retcode <> 0
94 THEN RETURN;
95 END IF;
96
97 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
98 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_group_by: '||v_group_by);
99 END IF;
100
101 IF p_amount_type = 'PTD'
102 THEN
103 v_select_begin_balance :=
104 'SUM(NVL(begin_balance_dr, 0) - NVL(begin_balance_cr, 0))';
105
106 v_select_period_dr := 'SUM(NVL(period_net_dr, 0))';
107 v_select_period_cr := 'SUM(NVL(period_net_cr, 0))';
108
109 v_period_list := '(''' || p_period_name || ''')';
110 ELSE
111
112 BEGIN
113
114 fv_utility.gl_get_first_period(g_sob_id,
115 p_period_name,
116 v_first_period,
117 v_err_buf);
118 IF v_err_buf IS NOT NULL
119 THEN
120 retcode := -2;
121 errbuf := v_err_buf;
122 RETURN;
123 END IF;
124
125 v_select_begin_balance :=
126 'SUM(DECODE(glb.period_name, ''' || v_first_period || ''',
127 NVL(begin_balance_dr, 0) - NVL(begin_balance_cr, 0), 0))';
128
129 v_select_period_dr :=
130 'SUM(DECODE(glb.period_name, '''||p_period_name||''',
131 NVL(begin_balance_dr,0) + NVL(period_net_dr,0),0) + '||
132 'DECODE(glb.period_name, ''' ||v_first_period || ''',
133 - NVL(begin_balance_dr, 0),0))';
134
135 v_select_period_cr :=
136 'SUM(DECODE(glb.period_name, ''' ||p_period_name||''',
137 NVL(begin_balance_cr,0) + NVL(period_net_cr,0),0) + '||
138 'DECODE(glb.period_name, ''' ||v_first_period || ''',
139 - NVL(begin_balance_cr, 0), 0))';
140
141 v_period_list :=
142 '(''' || p_period_name || ''','''
143 || v_first_period || ''')';
144 END;
145
146 END IF;
147
148 v_statement :=
149 ' INSERT INTO fv_facts_temp
150 (treasury_symbol_id, sgl_acct_number, fct_int_record_category,
151 amount, amount1, amount2 '||
152 REPLACE(v_group_by, 'glcc.' )||')'||
153 ' SELECT
154 fts.treasury_symbol_id,
155 glcc.'||v_acc_seg_name||', ''STBAL'','||
156 v_select_begin_balance||', '||
157 v_select_period_dr||', '||
158 v_select_period_cr||
159 v_group_by||
160 ' FROM gl_balances glb,
161 gl_code_combinations glcc,
162 fv_treasury_symbols fts,
163 fv_fund_parameters ffp
164 WHERE glcc.chart_of_accounts_id = '||v_chart_of_accounts_id||
165 ' AND glcc.template_id IS NULL
166 AND glb.code_combination_id = glcc.code_combination_id
167 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
168 AND fts.set_of_books_id = '||g_sob_id||
169 ' AND ffp.fund_value = glcc.'||v_bal_seg_name||
170 ' AND (fts.treasury_symbol
171 BETWEEN '''||p_treasury_symbol_low||''' AND '''||
172 p_treasury_symbol_high||''' )
173 AND glb.ledger_id = '||g_sob_id||
174 ' AND glb.actual_flag = ''A''
175 AND glb.period_name IN '||v_period_list||'
176 AND glb.currency_code = '''||p_currency_code||'''
177 AND (glb.translated_flag <> ''R''
178 OR glb.translated_flag IS NULL)
179 GROUP BY fts.treasury_symbol_id, glcc.'||v_acc_seg_name ||
180 v_group_by ;
181
182 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
183 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Select: '|| v_statement);
184 END IF;
185
186 EXECUTE IMMEDIATE v_statement;
187
188 v_req_id :=
189 fnd_request.submit_request ('FV','RXFVTBTS','','',FALSE,
190 'DIRECT',
191 p_report_id,
192 p_attribute_set,
193 p_output_format,
194 v_ledger_name,
195 p_currency_code,
196 p_treasury_symbol_low,
197 p_treasury_symbol_high,
198 p_period_name,
199 p_amount_type);
200
201 IF v_req_id = 0 THEN
202 retcode := -2;
203 errbuf := 'Error submitting Trial Balance report' ;
204 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception1',errbuf);
205 END IF;
206
207 EXCEPTION WHEN OTHERS THEN
208 retcode := sqlcode;
209 errbuf := SUBSTR(sqlerrm,1,250);
210 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
211 END main;
212 --------------------------------------------------------------------------------------
213 PROCEDURE purge_summary_txns IS
214 l_module_name VARCHAR2(200) := g_module_name || 'purge_summary_txns';
215 BEGIN
216
217 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
218 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Purging facts temp table');
219 END IF;
220
221 DELETE
222 FROM fv_facts_temp
223 WHERE fct_int_record_category = 'STBAL';
224 EXCEPTION
225 WHEN OTHERS THEN
226 retcode := -1;
227 errbuf := 'Error while grouping by: '||SUBSTR(sqlerrm,1,200);
228 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
229 RAISE;
230 END purge_summary_txns;
231 --------------------------------------------------------------------------------
232 -- This procedure selects the break columns, which the user has setup for the
233 -- RXi report, and uses these columns as the group by columns in the main
234 -- select.
235 --------------------------------------------------------------------------------
236 PROCEDURE group_by_columns(x_report_id IN NUMBER,
237 x_attribute_set IN VARCHAR2,
238 x_group_by OUT NOCOPY VARCHAR2)
239 IS
240 l_module_name VARCHAR2(200) := g_module_name || 'group_by_columns';
241
242 CURSOR c_group IS
243 SELECT column_name
244 FROM fa_rx_rep_columns_b
245 WHERE report_id = x_report_id
246 AND attribute_set = x_attribute_set
247 AND break = 'Y';
248
249 BEGIN
250
251 FOR crec IN c_group
252 LOOP
253 IF crec.column_name LIKE 'SEGMENT%'
254 THEN
255 IF x_group_by IS NOT NULL
256 THEN
257 x_group_by := x_group_by || ',' ;
258 END IF;
259 x_group_by := x_group_by || 'glcc.' || crec.column_name;
260 END IF;
261
262 END LOOP;
263
264 IF x_group_by IS NOT NULL
265 THEN
266 x_group_by := ',' || x_group_by;
267 END IF;
268 EXCEPTION WHEN OTHERS THEN
269 retcode := -1;
270 errbuf := 'Error while grouping by: '||SUBSTR(sqlerrm,1,200);
271 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
272
273 END group_by_columns;
274 --------------------------------------------------------------------------------------
275 END fv_tbal_by_ts;