[Home] [Help]
PACKAGE BODY: APPS.FV_GTAS_TBAL_TRX_PKG
Source
1 PACKAGE BODY FV_GTAS_TBAL_TRX_PKG AS
2 /* $Header: FVGTATBB.pls 120.1.12020000.10 2013/02/21 21:18:49 snama noship $ */
3
4 g_module VARCHAR2(200);
5 gbl_set_of_books_id gl_ledgers_public_v.ledger_id%TYPE;
6 gbl_period_name gl_period_statuses.period_name%TYPE;
7 gbl_coa_id gl_sets_of_books.chart_of_accounts_id%TYPE;
8 gbl_error_code NUMBER;
9 gbl_error_buf VARCHAR2(300);
10 gbl_fiscal_year gl_period_statuses.period_year%TYPE;
11
12 gbl_period_num_low gl_period_statuses.period_num%TYPE;
13 gbl_period_num_high gl_period_statuses.period_num%TYPE;
14 gbl_bal_segment_name VARCHAR2(10);
15 gbl_acc_segment_name VARCHAR2(10);
16 gbl_acc_value_set_id NUMBER;
17 gbl_aid_clause varchar2(100);
18 gbl_main_acct_clause varchar2(100);
19 gbl_segs_where_clause VARCHAR2(2000);
20 gbl_tas_clause VARCHAR2(100);
21 --------------------------------------------------------------------------------
22 PROCEDURE log (module IN VARCHAR2,
23 message_line IN VARCHAR2);
24
25 PROCEDURE get_segment_names;
26
27 PROCEDURE cleanup_process;
28
29 -----addded for TB report --------------------------------------
30
31 PROCEDURE rollup_process;
32
33 -- Global Variables for Trial Balance processing
34 gbl_period_num Gl_Balances.period_num%TYPE;
35 gbl_period_year gl_period_statuses.period_year%TYPE;
36 --------------------------------------------------------------------------------
37 -- Get balancing and accounting segments
38 --------------------------------------------------------------------------------
39 PROCEDURE GET_SEGMENT_NAMES
40 IS
41
42 l_module VARCHAR2(200):= g_module || 'GET_SEGMENT_NAMES';
43 l_temp_mesg VARCHAR2(100);
44 l_app_id NUMBER := 101;
45 l_flex_code VARCHAR2(10) := 'GL#';
46 l_segment_found BOOLEAN;
47 invalid_bal_segment EXCEPTION;
48 invalid_acc_segment EXCEPTION;
49
50 BEGIN
51
52 log(l_module, 'Begin');
53 FV_UTILITY.LOG_MESG('In '||l_module);
54
55 l_temp_mesg := ' getting balancing/accounting segment. ';
56
57
58 SELECT chart_of_accounts_id
59 INTO gbl_coa_id
60 FROM gl_ledgers_public_v
61 WHERE ledger_id = gbl_set_of_books_id;
62
63 FV_UTILITY.LOG_MESG('COA ID: '||gbl_coa_id);
64
65 -- Get Balancing Segment Name
66 -----------------------------
67 l_segment_found := FND_FLEX_APIS.get_segment_column
68 (l_app_id,
69 l_flex_code,
70 gbl_coa_id,
71 'GL_BALANCING',
72 gbl_bal_segment_name) ;
73
74 IF NOT l_segment_found THEN
75 RAISE invalid_bal_segment;
76 END IF;
77
78 -- Get Accounting Segment Name
79 ------------------------------
80 l_segment_found := FND_FLEX_APIS.get_segment_column
81 (l_app_id,
82 l_flex_code,
83 gbl_coa_id,
84 'GL_ACCOUNT',
85 gbl_acc_segment_name);
86 IF NOT l_segment_found THEN
87 RAISE invalid_acc_segment;
88 END IF;
89
90 -- Get the value set id
91 l_temp_mesg := ' getting account value set id. ';
92 SELECT flex_value_set_id
93 INTO gbl_acc_value_set_id
94 FROM fnd_id_flex_segments
95 WHERE application_column_name = gbl_acc_segment_name
96 AND id_flex_code = 'GL#'
97 AND id_flex_num = gbl_coa_id;
98
99 log(l_module, 'Balancing Segment: '||gbl_bal_segment_name);
100 log(l_module, 'Accounting Segment: '||gbl_acc_segment_name);
101 log(l_module, 'Accounting value set id: '||gbl_acc_value_set_id);
102 log(l_module, 'End');
103
104 EXCEPTION
105 WHEN invalid_bal_segment THEN
106 gbl_error_code := -1 ;
107 gbl_error_buf := 'Error while fetching balancing segment.';
108 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module,gbl_error_buf);
109 WHEN invalid_acc_segment THEN
110 gbl_error_code := -1 ;
111 gbl_error_buf := 'Error while fetching accounting segment.';
112 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module,gbl_error_buf);
113 WHEN NO_DATA_FOUND THEN
114 gbl_error_code := -1 ;
115 gbl_error_buf := l_module||' - No data found when'||l_temp_mesg;
116 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module,gbl_error_buf);
117 WHEN OTHERS THEN
118 gbl_error_code := -1 ;
119 gbl_error_buf := l_module||' - When others error when'||
120 l_temp_mesg||SQLERRM;
121 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module,gbl_error_buf);
122
123 END get_segment_names;
124 --------------------------------------------------------------------------------
125 -- PROCEDURE CLEANUP_PROCESS
126 --------------------------------------------------------------------------------
127 PROCEDURE CLEANUP_PROCESS IS
128
129 l_module VARCHAR2(200):= g_module || 'CLEANUP_PROCESS';
130
131 BEGIN
132
133 log(l_module, 'Begin');
134
135 DELETE FROM fv_gtas1_report_t2
136 WHERE set_of_books_id = gbl_set_of_books_id;
137
138 log(l_module, 'End');
139 EXCEPTION
140 WHEN OTHERS THEN
141 gbl_error_code := -1 ;
142 gbl_error_buf := l_module||' - When others exception - '||SQLERRM;
143 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,l_module,gbl_error_buf);
144 END;
145 -------------------------------------------------------------------------------
146 FUNCTION BEFOREREPORT
147 RETURN BOOLEAN IS
148
149 BEGIN
150 TRIAL_BALANCE_MAIN(P_LEDGER_ID,P_CHART_OF_ACCOUNTS_ID,
151 P_FLEX_LOW, P_FLEX_HIGH, P_PERIOD,
152 P_AID_LOW, P_AID_HIGH,
153 P_MAIN_ACCT_LOW, P_MAIN_ACCT_HIGH,
154 P_TAS);
155 RETURN (TRUE);
156 EXCEPTION
157 WHEN OTHERS THEN
158 FV_UTILITY.LOG_MESG(
159 ' An error occured in the before report trigger. Error : '
160 || SUBSTR(SQLERRM,1,200));
161 RETURN(FALSE);
162
163 END beforereport;
164 -------------------------------------------------------------------------------
165 PROCEDURE TRIAL_BALANCE_MAIN (
166 --p_errbuf OUT NOCOPY Varchar2,
167 --p_retcode OUT NOCOPY Number,
168 P_LEDGER_ID Gl_ledgers_public_v.ledger_id%TYPE,
169 P_CHART_OF_ACCOUNTS_ID Gl_Code_Combinations.chart_of_accounts_id%TYPE,
170 P_FLEX_LOW IN VARCHAR2,
171 P_FLEX_HIGH IN VARCHAR2,
172 P_PERIOD Varchar2,
173 P_AID_LOW IN VARCHAR2,
174 P_AID_HIGH IN VARCHAR2,
175 P_MAIN_ACCT_LOW IN VARCHAR2,
176 P_MAIN_ACCT_HIGH IN VARCHAR2,
177 P_TAS IN VARCHAR2)
178 IS
179 l_module VARCHAR2(200) := g_module || 'TRIAL_BALANCE_MAIN';
180
181
182 BEGIN
183
184 gbl_error_code := 0;
185
186 -- Store the passed set of books id and chart of accounts id
187 -- in the global variables
188 gbl_set_of_books_id := P_LEDGER_ID;
189 gbl_coa_id := P_CHART_OF_ACCOUNTS_ID;
190
191 log(l_module,'Begin');
192 log(l_module,'Set of Books ID: '|| GBL_SET_OF_BOOKS_ID);
193 log(l_module,'Chart of Accounts ID: '|| P_CHART_OF_ACCOUNTS_ID);
194 log(l_module,'Flex Low: '|| P_FLEX_LOW);
195 log(l_module,'Flex High: '|| P_FLEX_HIGH);
196 log(l_module,'Period Name: '|| P_PERIOD);
197 log(l_module,'Agency ID Low: '|| P_AID_LOW);
198 log(l_module,'Agency ID High: '|| P_AID_HIGH);
199 log(l_module,'Main Acct Low: '|| P_MAIN_ACCT_LOW);
200 log(l_module,'Main Acct High: '|| P_MAIN_ACCT_HIGH);
201 log(l_module,'TAS : '|| P_TAS);
202
203 --Getting the period number
204 BEGIN
205 SELECT period_num, period_year
206 INTO gbl_period_num_high, gbl_fiscal_year
207 FROM gl_period_statuses
208 WHERE period_name = p_period
209 AND application_id = 101
210 AND closing_status NOT IN ('F','N')
211 AND ledger_id = gbl_set_of_books_id;
212
213 gbl_period_name := p_period;
214 gbl_period_year := gbl_fiscal_year;
215
216 SELECT MIN(period_num)
217 INTO gbl_period_num_low
218 FROM gl_period_statuses
219 WHERE period_year = gbl_fiscal_year
220 AND application_id = 101
221 AND closing_status <> 'F'
222 AND closing_status <> 'N'
223 AND adjustment_period_flag = 'N'
224 AND ledger_id = gbl_set_of_books_id;
225
226 EXCEPTION
227 WHEN NO_DATA_FOUND THEN
228 gbl_error_code := -1;
229 gbl_error_buf := l_module||' No data found getting period num/year.';
230 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module,gbl_error_buf);
231
232 WHEN OTHERS THEN
233 gbl_error_code := -1;
234 gbl_error_buf := l_module||' When others error getting period num/year.';
235 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module,gbl_error_buf);
236 END;
237
238 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
239 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module,'Period Num - '||gbl_period_num);
240 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module,'Period Year - '||gbl_period_year);
241 END IF;
242
243 -- Purge Temp tables
244 CLEANUP_PROCESS;
245
246 --Build the where clauses
247 IF P_AID_LOW IS NOT NULL THEN
248 gbl_aid_clause := ' AND fts.department_id BETWEEN '||''''||P_AID_LOW||''''||
249 ' AND '||''''||P_AID_HIGH||'''' ;
250 log(l_module, 'gbl_aid_clause: '||gbl_aid_clause);
251 END IF;
252
253 IF P_MAIN_ACCT_LOW IS NOT NULL THEN
254 gbl_main_acct_clause := ' AND fts.fund_group_code BETWEEN '||''''||P_MAIN_ACCT_LOW||''''||
255 ' AND '||''''||P_MAIN_ACCT_HIGH||'''' ;
256 log(l_module, 'gbl_main_acct_clause: '||gbl_main_acct_clause);
257 END IF;
258
259 IF (P_FLEX_LOW IS NOT NULL AND
260 P_FLEX_HIGH IS NOT NULL) THEN
261 build_segs_where_clause(p_flex_low, p_flex_high,
262 gbl_set_of_books_id, gbl_segs_where_clause);
263 log(l_module, 'gbl_segs_where_clause: '||gbl_segs_where_clause);
264 END IF;
265
266 IF P_TAS IS NOT NULL THEN
267 gbl_tas_clause := ' AND fts.treasury_symbol = '||''''||p_tas||'''' ;
268 END IF;
269
270 IF (gbl_error_code = 0) THEN
271 log(l_module,'ENTERING ROLLUP_PROCESS ...');
272 ROLLUP_PROCESS;
273 END IF;
274
275 IF (gbl_error_code <> 0) THEN
276 ROLLBACK;
277 fnd_file.put_line(fnd_file.log, 'Error in GTAS Trial Balance Process: '||gbl_error_buf);
278 END IF;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 -- p_retcode := '-1' ;
283 -- p_errbuf := SQLERRM ||
284 -- ' -- Error in Trial_Balance_Main';
285 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'.final_exception',
286 SQLERRM ||' -- Error in Trial_Balance_Main');
287 END TRIAL_BALANCE_MAIN;
288 --------------------------------------------------------------------------------
289 -- PROCEDURE ROLLUP_PROCESS
290 --------------------------------------------------------------------------------
291 -- Rollup_Process get called from Trial_Balance_Main procedure.
292 -- The purpose of this procedure is to build a 'group by' clause using
293 -- segments chosen in an attribute set of RXi. This procedure also does
294 -- rollup of the trial balance records in fv_facts_report_t2 table by
295 -- the SEGMENTS.
296 -- ---------------------------------------------------------------------
297 PROCEDURE ROLLUP_PROCESS
298 IS
299 l_module VARCHAR2(200) := g_module || 'ROLLUP_PROCESS';
300
301 l_statement VARCHAR2(5000);
302
303 BEGIN
304 log(l_module, 'Begin');
305
306 l_statement := '
307 INSERT INTO fv_gtas1_report_t2
308 ( account_number,
309 d_c_indicator,
310 amount,
311 record_category,
312 ussgl_account,
313 set_of_books_id,
314 exch_non_exch,
315 cust_non_cust,
316 ccid,
317 account_type,
318 direct_or_reimb_code,
319 appor_cat_code,
320 appor_cat_b_code,
321 program_rept_code,
322 bea_cat_code ,
323 borr_src_code ,
324 new_bal_code ,
325 cur_subsequent_code ,
326 pya_code ,
327 credit_cohort_yr ,
328 program_cost_ind ,
329 treasury_symbol_id ,
330 fed_non_fed ,
331 authority_type_code,
332 budget_impact_ind,
333 trading_partner_agency_id,
334 trading_partner_main_account,
335 fund_value,
336 beginning_balance,
337 dr_amount,
338 cr_amount )
339 (SELECT t2.account_number,
340 '||''''||'N'||''''||',
341 0,
342 '||''''||'TRIAL_BAL'||''''||',
343 t2.ussgl_account,
344 :gbl_set_of_books_id,
345 t2.exch_non_exch,
346 t2.cust_non_cust,
347 t2.ccid,
348 t2.account_type,
349 t2.direct_or_reimb_code,
350 t2.appor_cat_code,
351 t2.appor_cat_b_code,
352 t2.program_rept_code,
353 t2.bea_cat_code,
354 t2.borr_src_code,
355 t2.new_bal_code,
356 t2.cur_subsequent_code,
357 t2.pya_code,
358 t2.credit_cohort_yr,
359 t2.program_cost_ind,
360 t2.treasury_symbol_id,
361 t2.fed_non_fed ,
362 t2.authority_type_code,
363 t2.budget_impact_ind,
364 t2.trading_partner_agency_id,
365 t2.trading_partner_main_account,
366 t2.fund_value,
367 t2.period_begin_bal,
368 nvl(t2.period_dr,0),
369 nvl(t2.period_cr,0)
370 --FROM fv_gtas1_period_balances_v t2, gl_code_combinations gcc
371 FROM FV_GTAS_PERIOD_BALANCES_TB_V t2, gl_code_combinations gcc,
372 fv_treasury_symbols fts
373 WHERE t2.set_of_books_id = :gbl_set_of_books_id
374 AND t2.ccid = gcc.code_combination_id
375 AND t2.period_num <= :gbl_period_num_high
376 AND t2.period_year = :gbl_fiscal_year
377 AND fts.treasury_symbol_id = t2.treasury_symbol_id
378 '||
379 gbl_tas_clause||
380 gbl_aid_clause||
381 gbl_main_acct_clause||
382 gbl_segs_where_clause||
383 ' AND (period_begin_bal <> 0 OR
384 period_dr <> 0 OR
385 period_cr <> 0)
386 )';
387
388 log(l_module, L_STATEMENT);
389
390 EXECUTE IMMEDIATE l_statement
391 USING gbl_set_of_books_id, gbl_set_of_books_id,
392 gbl_period_num_high, gbl_fiscal_year;
393
394 DELETE FROM fv_gtas1_report_t2
395 WHERE record_category <> 'TRIAL_BAL'
396 AND set_of_books_id = gbl_set_of_books_id;
397
398 COMMIT;
399 log(l_module, 'End');
400 EXCEPTION
401
402 WHEN NO_DATA_FOUND THEN
403 ROLLBACK;
404 gbl_error_code := -1;
405 gbl_error_buf := SQLERRM ||
406 ' -- Error in Rollup_Process';
407 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module,gbl_error_buf);
408
409 WHEN OTHERS THEN
410 ROLLBACK;
411 gbl_error_code := -1 ;
412 gbl_error_buf := SQLERRM ||
413 ' -- Error in Rollup_Process';
414 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||
415 '.final_exception',gbl_error_buf);
416
417 END ROLLUP_PROCESS;
418 ------------------------------- -----------------------------------------------
419 PROCEDURE LOG (module IN VARCHAR2, message_line IN VARCHAR2) IS
420
421 BEGIN
422 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
423 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, module, message_line);
424 END IF;
425 EXCEPTION
426 WHEN OTHERS THEN
427 FV_UTILITY.LOG_MESG('When others error in module: log: '||sqlerrm);
428 END LOG;
429 --------------------------------------------------------------------------------
430 PROCEDURE BUILD_SEGS_WHERE_CLAUSE (p_flex_low IN VARCHAR2,
431 p_flex_high IN VARCHAR2,
432 p_sob_id IN NUMBER,
433 p_segs_where_clause OUT NOCOPY VARCHAR2) IS
434
435 l_module VARCHAR2(200) := g_module||'BUILD_SEGS_WHERE_CLAUSE';
436 l_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
437 l_coa_id NUMBER;
438 l_num_segs_low NUMBER;
439 l_num_segs_high NUMBER;
440 l_segs_low fnd_flex_ext.segmentarray;
441 l_segs_high fnd_flex_ext.segmentarray;
442 l_app_id fnd_id_flex_structures.application_id%TYPE := 101;
443 l_id_flex_code Fnd_Id_Flex_Structures.id_flex_code%TYPE := 'GL#';
444 i NUMBER := 1;
445 l_select VARCHAR2(1000);
446
447 CURSOR segments_cur IS
448 SELECT application_column_name
449 FROM fnd_id_flex_segments
450 WHERE application_id = l_app_id
451 AND id_flex_code = l_id_flex_code
452 AND id_flex_num = l_coa_id
453 ORDER BY segment_num;
454
455 BEGIN
456 log(l_module, 'Begin');
457
458 SELECT CHART_OF_ACCOUNTS_ID
459 INTO l_coa_id
460 FROM gl_ledgers
461 where ledger_id = p_sob_id;
462 --Get the flex delimiter
463 log(l_module, 'l_coa_id: '||l_coa_id);
464 l_delimiter := fnd_flex_ext.get_delimiter
465 ('SQLGL',
466 l_id_flex_code,
467 l_coa_id
468 );
469 log(l_module, 'l_delimiter: '||l_delimiter);
470 --Get the low segments
471 l_num_segs_low := fnd_flex_ext.breakup_segments
472 (p_flex_low,
473 l_delimiter,
474 l_segs_low);
475 log(l_module, 'l_num_segs_low: '||l_num_segs_low);
476 --Get the high segments
477 l_num_segs_high := fnd_flex_ext.breakup_segments
478 (p_flex_high,
479 l_delimiter,
480 l_segs_high);
481 log(l_module, 'l_num_segs_high: '||l_num_segs_high);
482
483 FOR segments IN segments_cur LOOP
484 IF (l_segs_low(i) IS NOT NULL AND
485 l_segs_high(i) IS NOT NULL) THEN
486 l_select := l_select||' AND gcc.'||segments.application_column_name||' BETWEEN '||
487 ''''||l_segs_low(i)||''''||' AND '||''''||l_segs_high(i)||'''';
488 END IF;
489 i := i+1 ;
490 END LOOP;
491 p_segs_where_clause := l_select;
492
493 log(l_module, 'End');
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 gbl_error_code := -1 ;
498 gbl_error_buf := l_module||' '||SQLERRM;
499 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
500 l_module||' '||gbl_error_buf);
501 END BUILD_SEGS_WHERE_CLAUSE;
502 --------------------------------------------------------------------------------
503 BEGIN
504 g_module := 'fv.plsql.FV_GTAS_TBAL_TRX_PKG.';
505 --------------------------------------------------------------------------------
506 END FV_GTAS_TBAL_TRX_PKG;