[Home] [Help]
PACKAGE BODY: APPS.FV_CFS_PKG
Source
1 PACKAGE BODY fv_cfs_pkg AS
2 /* $Header: FVXCFSPB.pls 120.113 2011/11/15 10:09:43 amaddula ship $ */
3 ------------Global Variables---------------
4 g_module_name VARCHAR2(100);
5 v_cursor_id INTEGER;
6 v_sob gl_ledgers_public_v.ledger_id%TYPE;
7 v_period_name gl_period_statuses.period_name%TYPE;
8 v_units NUMBER;
9 v_report_type VARCHAR2(30);
10 v_table_ind VARCHAR2(1);
11 v_end_date DATE;
12 v_retcode NUMBER DEFAULT 0;
13 v_errbuf VARCHAR2(2000);
14 v_sequence_id NUMBER;
15 v_facts_rep_show VARCHAR2(2);
16 v_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
17 v_currency_code gl_sets_of_books.currency_code%TYPE;
18 v_bal_seg_name VARCHAR2(20);
19 v_acc_seg_name VARCHAR2(20);
20 v_acct_flex_value_set_id fnd_id_flex_segments_vl.flex_value_set_id%TYPE;
21 v_bal_flex_value_set_id fnd_id_flex_segments_vl.flex_value_set_id%TYPE;
22 v_line_id fv_cfs_rep_lines.line_id%TYPE;
23 v_line_details_id fv_cfs_rep_line_dtl.line_detail_id%TYPE;
24 v_line_type fv_cfs_rep_lines.line_type%TYPE;
25 v_line_label fv_cfs_rep_lines.line_label%TYPE;
26 v_by_recipient fv_cfs_rep_lines.by_recipient%TYPE;
27 v_natural_balance_type fv_cfs_rep_lines.natural_balance_type%TYPE;
28 v_amount fv_cfs_rep_temp.col_1_amt%TYPE;
29 v_exception_amount fv_cfs_rep_temp.col_1_amt%TYPE;
30 v_recipient_name fv_facts_report_t2.recipient_name%TYPE;
31 v_code_combination_id gl_code_combinations.code_combination_id%TYPE;
32 v_account gl_code_combinations.segment1%TYPE;
33 v_fund gl_code_combinations.segment1%TYPE;
34 v_col_1_amt fv_cfs_rep_temp.col_1_amt%TYPE;
35 v_col_2_amt fv_cfs_rep_temp.col_1_amt%TYPE;
36 v_col_3_amt fv_cfs_rep_temp.col_1_amt%TYPE;
37 v_col_4_amt fv_cfs_rep_temp.col_1_amt%TYPE;
38 v_col_5_amt fv_cfs_rep_temp.col_1_amt%TYPE;
39 v_col_6_amt fv_cfs_rep_temp.col_1_amt%TYPE;
40 v_col_7_amt fv_cfs_rep_temp.col_1_amt%TYPE;
41 v_col_8_amt fv_cfs_rep_temp.col_1_amt%TYPE;
42 v_sequence_number fv_cfs_rep_lines.sequence_number%TYPE;
43 v_line_number fv_cfs_rep_lines.line_number%TYPE;
44 v_period_fiscal_year gl_period_statuses.period_year%TYPE;
45 v_period_num gl_period_statuses.period_num%TYPE;
46 v_purge_ts_id fv_treasury_symbols.TREASURY_SYMBOL_ID%TYPE;
47 v_select1 VARCHAR2(32000);
48 v_select2 VARCHAR2(32000);
49 v_select3 VARCHAR2(32000);
50 v_select4 VARCHAR2(32000);
51 v_select5 VARCHAR2(32000);
52 v_cursor_id1 INTEGER;
53 v_cursor_id2 INTEGER;
54 v_cursor_id3 INTEGER;
55 v_cursor_id4 INTEGER;
56 v_cursor_id5 INTEGER;
57 v_cursor_id6 INTEGER;
58 v_cursor_id7 INTEGER;
59 v_cursor_id8 INTEGER;
60 gbl_units VARCHAR2(25);
61 v_bud_col VARCHAR2(1);
62 V_nbfa_col VARCHAR2(1);
63 v_glbal_select VARCHAR2(32000);
64 v_glbal_grpby_sel VARCHAR2(32000);
65 v_glbal_curid INTEGER;
66 v_glbal_grpby_curid INTEGER;
67 v_begin_period NUMBER;
68 v_begin_period_name gl_period_statuses.period_name%TYPE;
69 v_begin_period_end_date DATE;
70 v_end_period NUMBER;
71 v_end_period_end_date DATE;
72 v_begin_period_1 NUMBER;
73 v_begin_period_name_1 gl_period_statuses.period_name%TYPE;
74 v_begin_period_1_end_date DATE;
75 v_end_period_1_end_date DATE;
76 v_cy_gl_beg_bal NUMBER;
77 v_cy_gl_end_bal NUMBER;
78 v_py_gl_beg_bal NUMBER;
79 v_py_gl_end_bal NUMBER;
80 v_fct1_attr_select VARCHAR2(32000);
81 v_fct1_sel VARCHAR2(32000);
82 v_fct1_sel_curid INTEGER;
83 v_fct1_rcpt_sel VARCHAR2(32000);
84 v_fct1_rcpt_sel_curid INTEGER;
85 v_fct1_rcpt_sel2 VARCHAR2(32000);
86 v_fct1_rcpt_sel2_curid INTEGER;
87 v_sbr_curid INTEGER;
88 v_cy_fct1_begbal NUMBER;
89 v_cy_fct1_endbal NUMBER;
90 v_py_fct1_begbal NUMBER;
91 v_py_fct1_endbal NUMBER;
92 v_cy_begbal_diff NUMBER;
93 v_py_begbal_diff NUMBER;
94 v_cy_sbr_beg_bal NUMBER;
95 v_cy_sbr_end_bal NUMBER;
96 v_py_sbr_beg_bal NUMBER;
97 v_py_sbr_end_bal NUMBER;
98 v_year_flag VARCHAR2(1):='P'; /* It represent current year(C) or previous year(P)*/
99 v_balance_type fv_sbr_definitions_accts.SBR_BALANCE_TYPE%TYPE;
100 istotal_cal NUMBER;
101 /* ADDED FOR sbr ER */
102 /* START*/
103 g_chart_of_accounts_id gl_ledgers.chart_of_accounts_id%TYPE;
104 g_fund_segment_name VARCHAR2(10);
105 --
106 -- ------------------------------------
107 -- Stored Global Variables
108 -- ------------------------------------
109 g_insert_count NUMBER;
110 --
111 g_error_code NUMBER;
112 g_error_message VARCHAR2(80);
113 --
114 g_period_num NUMBER;
115 g_ts_value_in_process VARCHAR2(25);
116 g_total_start_line_number NUMBER;
117 g_subtotal_start_line_number NUMBER;
118 g_column_number NUMBER;
119 --Added for bug No. 1553099
120 g_currency_code VARCHAR2(15);
121 --
122 c_total_balance NUMBER;
123 c_total_balance_bud NUMBER;
124 c_total_balance_nbfa NUMBER;
125 c_ending_balance NUMBER;
126 c_begin_balance NUMBER;
127 c_begin_select VARCHAR2(200);
128 c_end_select VARCHAR2(200);
129 c_begin_period VARCHAR2(40);
130 c_end_period VARCHAR2(40);
131 -- New Variables declared by Narsimha Balakkari.
132 c_resource_type fv_treasury_symbols.resource_type%TYPE;
133 c_rescission_flag VARCHAR2(10);
134 -- ---------- Flex Segment Name Cursor Variables ---------
135 c_segment_name fnd_id_flex_segments.segment_name%TYPE;
136 c_flex_column_name fnd_id_flex_segments.application_column_name%TYPE;
137 --
138 v_balance_column_name fnd_id_flex_segments.application_column_name%TYPE;
139 g_seg_value_set_id FND_FLEX_VALUE_SETS.FLEX_VALUE_SET_ID%TYPE;
140 -- ---------- Treasury Symbol Report Line Cursor Vaiables -----------
141 c_sbr_ts_value gl_code_combinations.segment1%TYPE;
142 c_sbr_line_id fv_sbr_definitions_lines.sbr_line_id%TYPE;
143 c_sbr_line_number fv_sbr_definitions_lines.sbr_line_number%TYPE;
144 c_sbr_prev_line_number fv_sbr_definitions_lines.sbr_line_number%TYPE;
145 c_sbr_line_type_code fv_sbr_definitions_lines.sbr_line_type_code%TYPE;
146 c_sbr_natural_bal_type fv_sbr_definitions_lines.sbr_natural_balance_type%TYPE;
147 c_sbr_line_category fv_sbr_definitions_lines.sbr_fund_category%TYPE;
148 -- New variable declared by pkpatel to fix Bug 1575992
149 c_sbr_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
150 --
151 -- New variable declared by Narsimha.
152 c_sbr_report_line_number fv_sbr_definitions_lines.sbr_report_line_number%TYPE;
153 c_sbr_gl_balance fv_sbr_definitions_lines.SBR_GL_BALANCE%TYPE;
154
155 -- ---------- Balance Type Cursor Vaiables ---------
156 c_sbr_line_acct_id fv_sbr_definitions_accts.sbr_line_acct_id%TYPE;
157 c_sbr_balance_type fv_sbr_definitions_accts.sbr_balance_type%TYPE;
158 c_acct_number fv_sbr_definitions_accts.acct_number%TYPE;
159 c_direct_or_reimb_code fv_sbr_definitions_accts.direct_or_reimb_code%TYPE;
160 c_apportionment_category_code fv_sbr_definitions_accts.apportionment_category_code%TYPE;
161 c_category_b_code fv_sbr_definitions_accts.category_b_code%TYPE;
162 c_prc_code fv_sbr_definitions_accts. prc_code%TYPE;
163 c_advance_code fv_sbr_definitions_accts.advance_code%TYPE;
164 c_availability_time fv_sbr_definitions_accts.availability_time%TYPE;
165 c_bea_category_code fv_sbr_definitions_accts.bea_category_code%TYPE;
166 c_borrowing_source_code fv_sbr_definitions_accts.borrowing_source_code%TYPE;
167 c_transaction_partner fv_sbr_definitions_accts.transaction_partner%TYPE;
168 c_year_of_budget_authority fv_sbr_definitions_accts.year_of_budget_authority%TYPE;
169 c_prior_year_adjustment fv_sbr_definitions_accts.prior_year_adjustment%TYPE;
170 c_authority_type fv_sbr_definitions_accts.authority_type%TYPE;
171 c_tafs_status fv_sbr_definitions_accts.tafs_status%TYPE;
172 c_availability_type fv_sbr_definitions_accts.availability_type%TYPE;
173 c_expiration_flag fv_sbr_definitions_accts.expiration_flag%TYPE;
174 c_fund_type fv_sbr_definitions_accts.fund_type%TYPE;
175 c_financing_account_code fv_sbr_definitions_accts.financing_account_code%TYPE;
176 exp_date DATE;
177 beg_date DATE;
178 close_date DATE;
179 whether_Exp VARCHAR2(1);
180 report_period_num NUMBER ;
181 parm_tsymbol_id NUMBER;
182 whether_Exp_SameYear VARCHAR2(1);
183 expiring_year NUMBER;
184 -- New variables declared by Narsimha.
185 c_sbr_apportion_amt NUMBER;
186 c_sbr_additional_info fv_sbr_definitions_accts .sbr_additional_info%TYPE;
187 -- ---------- Treasury Symbol Accummulation Cursor Vaiables ---------
188 /* c_sbr_column_amount NUMBER;
189 c_sbr_amount_not_shown fv_sbr_definitions_cols_temp.sbr_amount_not_shown%TYPE;
190 -- ---------- Output Report Line Column Data -------------
191 o_sbr_ts_value fv_sbr_definitions_cols_temp.sbr_fund_value%TYPE;
192 o_sbr_line_id fv_sbr_definitions_cols_temp.sbr_line_id%TYPE;
193 o_sbr_column_number fv_sbr_definitions_cols_temp.sbr_column_number%TYPE;
194 o_sbr_column_amount fv_sbr_definitions_cols_temp.sbr_column_amount%TYPE;
195 o_sbr_amt_not_shown fv_sbr_definitions_cols_temp.sbr_amount_not_shown%TYPE;
196 */
197 errbuf_facts VARCHAR2(1000);
198 retcode_facts NUMBER;
199 run_mode_fact VARCHAR2(15);
200 contact_fname VARCHAR2(15) ;
201 contact_lname VARCHAR2(15);
202 contact_phone NUMBER ;
203 contact_extn NUMBER ;
204 contact_email VARCHAR2(15);
205 contact_fax NUMBER;
206 contact_maiden VARCHAR2(15);
207 supervisor_name VARCHAR2(15);
208 supervisor_phone NUMBER ;
209 supervisor_extn NUMBER ;
210 agency_name_1 VARCHAR2(15);
211 agency_name_2 VARCHAR2(15);
212 address_1 VARCHAR2(15);
213 address_2 VARCHAR2(15);
214 city VARCHAR2(15);
215 state VARCHAR2(15);
216 zip VARCHAR2(15);
217 -- ------------------------------------
218 -- Stored Input Parameters
219 -- ------------------------------------
220 parm_application_id NUMBER;
221 --p_set_of_books_id NUMBER;
222 parm_gl_period_num NUMBER;
223 parm_treasury_value VARCHAR2(35);
224 parm_run_mode VARCHAR2(10);
225 DSum_E NUMBER;
226 CSum_E NUMBER;
227 DSum_B NUMBER;
228 CSum_B NUMBER;
229 e_bal_indicator VARCHAR2(1);
230 b_bal_indicator VARCHAR2(1);
231 CURSOR sbr_report_line_cursor
232 IS
233 SELECT DISTINCT line.sbr_line_id sbr_line_id ,
234 line.sbr_line_number sbr_line_number ,
235 line.sbr_line_type_code sbr_line_type_code ,
236 line.sbr_natural_balance_type sbr_natural_balance_type,
237 line.sbr_fund_category sbr_line_category ,
238 line.sbr_report_line_number sbr_report_line_number ,
239 line.sbr_line_label sbr_line_label ,
240 line.sbr_gl_balance sbr_gl_balance
241 FROM fv_sbr_definitions_lines line
242 WHERE line.set_of_books_id = v_sob
243 AND (line.sbr_line_type_code) IN ('T', 'D', 'D2', 'S') -- To handle subtotals
244 ORDER BY line.sbr_line_number;
245 --
246 -- ---------- Determine Balance Type of Acct -------------
247 --
248 CURSOR balance_type_cursor
249 IS
250 SELECT sbr_line_acct_id ,
251 sbr_balance_type ,
252 acct_number ,
253 direct_or_reimb_code ,
254 apportionment_category_code,
255 category_b_code ,
256 prc_code ,
257 advance_code ,
258 availability_time ,
259 bea_category_code ,
260 borrowing_source_code ,
261 transaction_partner ,
262 year_of_budget_authority ,
263 prior_year_adjustment ,
264 authority_type ,
265 tafs_status ,
266 availability_type ,
267 expiration_flag ,
268 fund_type ,
269 financing_account_code ,
270 sbr_treasury_symbol_id
271 FROM fv_sbr_definitions_accts
272 WHERE sbr_line_id = c_sbr_line_id
273 and set_of_books_id = v_sob;
274
275 /* TREASURY SYMBOLS CURSOR */
276 -- Bug 13355766. Modified query to use the new values for Authority Type Code
277 -- (TIME_FRAME)
278 CURSOR ts_cursor(p_sob NUMBER)
279 IS
280 SELECT treasury_symbol,
281 treasury_symbol_id
282 FROM fv_treasury_symbols
283 WHERE TIME_FRAME IN ('A','M', 'X')
284 AND (FUND_GROUP_CODE NOT BETWEEN '3800' AND '3899')
285 AND (FUND_GROUP_CODE NOT BETWEEN '6001' AND '6999')
286 -- AND treasury_symbol IN ('33-X-3333','11-08-0110','11-04-0100','03-X-0366','03-06-0333')
287 AND set_of_books_id = p_sob
288 ORDER BY treasury_symbol;
289
290 CURSOR get_ts_id_cur (p_acc_num VARCHAR2)
291 IS
292 select distinct fft.treasury_symbol_id from fv_facts_temp fft,
293 fv_treasury_symbols fts
294 where sgl_acct_number like p_acc_num||'%'
295 and fft.treasury_symbol_id= fts.treasury_symbol_id
296 and fft.fct_int_record_type='BLK_DTL'
297 and fts.set_of_books_id=v_sob;
298
299 PROCEDURE build_report_lines;
300 PROCEDURE build_fiscal_line_columns
301 (
302 p_fiscal_year NUMBER);
303 --PROCEDURE build_total_line_columns;
304 /*END OF sbr ER */
305 -- =============================================================
306 PROCEDURE get_qualifier_segments;
307 PROCEDURE build_dynamic_query;
308 PROCEDURE get_one_time_values;
309 PROCEDURE process_report_line;
310 PROCEDURE process_detail_line;
311 PROCEDURE process_total_line;
312 PROCEDURE process_sbr_total_line;
313 PROCEDURE populate_temp_table;
314 PROCEDURE populate_ccid;
315 PROCEDURE purge_csf_temp_table;
316 PROCEDURE get_sbr_py_bal_details(p_fiscal_year NUMBER);
317 PROCEDURE build_sbr_dynamic_query;
318 PROCEDURE purge_facts_transactions;
319 FUNCTION get_bal_type_amt
320 (
321 p_balance_type VARCHAR,
322 p_natural_bal_type VARCHAR,
323 p_beg_bal NUMBER,
324 p_end_bal NUMBER)
325 RETURN NUMBER;
326 --p_bal_type_amt OUT NUMBER);
327 -- =============================================================
328 PROCEDURE main
329 (
330 errbuf OUT NOCOPY VARCHAR2,
331 retcode OUT NOCOPY NUMBER,
332 p_set_of_books_id IN NUMBER,
333 p_report_type IN VARCHAR2,
334 p_units IN VARCHAR2,
335 p_period_name IN VARCHAR2,
336 p_facts_rep_show IN VARCHAR2,
337 p_table_indicator IN VARCHAR2
338 )
339 IS
340 l_module_name VARCHAR2(200) := g_module_name || 'MAIN.';
341 l_request_id NUMBER;
342 l_facts_edit_cnt NUMBER;
343 l_count NUMBER;
344 l_count_acct NUMBER;
345 l_sub_sbr NUMBER:=2;
346 l_one_edit_pass NUMBER:=0;
347 BEGIN
348 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Sob ID: '||p_set_of_books_id);
349 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Period: '||p_period_name);
350 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Units: '||p_units);
351 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Report type: '||p_report_type);
352 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Table Ind: '||p_table_indicator);
353 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_facts_rep_show : '||p_facts_rep_show);
354
355 v_sob := p_set_of_books_id;
356 v_period_name := p_period_name;
357 v_report_type := p_report_type;
358 v_facts_rep_show := p_facts_rep_show;
359
360
361 parm_application_id := '101';
362 gbl_units := p_units;
363 g_error_code :=0;
364 IF p_units = 'Dollars' THEN
365 v_units := 1;
366 ELSIF p_units = 'Thousands' THEN
367 v_units := 1000;
368 ELSIF p_units = 'Millions' THEN
369 v_units := 1000000;
370 END IF;
371 SELECT chart_of_accounts_id,
372 currency_code
373 INTO v_chart_of_accounts_id,
374 v_currency_code
375 FROM gl_ledgers_public_v
376 WHERE ledger_id = v_sob ;
377
378 get_one_time_values;
379 IF v_retcode <> 0 THEN
380 retcode := v_retcode;
381 errbuf := v_errbuf;
382 RETURN;
383 END IF;
384 SELECT TRUNC(end_date),
385 period_num ,
386 period_year
387 INTO v_end_date,
388 v_period_num ,
389 v_period_fiscal_year
390 FROM gl_period_statuses
391 WHERE ledger_id = v_sob
392 AND application_id = parm_application_id
393 AND period_name = v_period_name;
394 /*Sequence for fv_cfs_rep_temp table */
395 SELECT fv_cfs_rep_temp_s.NEXTVAL
396 INTO v_sequence_id
397 FROM DUAL;
398
399 get_qualifier_segments;
400
401 -- Checking whether processing SBR or other report types
402
403 IF UPPER(v_report_type)='SBR' THEN
404
405 -- Check whether the SBR setup is done or not
406 SELECT count(*) into l_count
407 FROM fv_sbr_definitions_lines
408 WHERE set_of_books_id = p_set_of_books_id;
409
410 SELECT count(*) into l_count_acct
411 FROM fv_sbr_definitions_accts
412 WHERE set_of_books_id = p_set_of_books_id;
413
414 if l_count=0 or l_count_acct=0 then
415 errbuf := 'No Setup data for Statement of Budgetary Resources';
416 retcode := 1;
417 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',errbuf);
418 RETURN;
419 end if;
420
421 -- Processing SBR report line definitions
422 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'BEFORE ts_rec CURSOR->'||p_set_of_books_id);
423 purge_csf_temp_table;
424
425 build_sbr_dynamic_query;
426
427 FOR ts_rec IN ts_cursor(p_set_of_books_id)
428 LOOP
429 --fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'in cursor loop');
430 parm_treasury_value := ts_rec.treasury_symbol;
431 parm_tsymbol_id := ts_rec.treasury_symbol_id;
432 v_purge_ts_id := ts_rec.treasury_symbol_id;
433
434 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
435 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SUBMITTING FACTS II process FOR TS.....'||parm_treasury_value);
436 END IF;
437 SELECT period_num
438 INTO report_period_num
439 FROM gl_period_statuses
440 WHERE application_id = parm_application_id
441 AND set_of_books_id = v_sob
442 AND period_name = v_period_name
443 AND period_year = v_period_fiscal_year;
444
445 retcode_facts:=0;
446
447 /*Purging old data from fv_facts_temp tables*/
448 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Purging old data before triggers facts process for Treasury Symbol-> '||parm_treasury_value);
449 purge_facts_transactions;
450
451
452 FV_FACTS_TRANSACTIONS.main(errbuf_facts, retcode_facts, v_sob, parm_treasury_value,
453 v_period_fiscal_year, report_period_num, run_mode_fact, contact_fname, contact_lname,
454 contact_phone, contact_extn, contact_email, contact_fax, contact_maiden, supervisor_name,
455 supervisor_phone, supervisor_extn, agency_name_1, agency_name_2, address_1, address_2,
456 city, state, zip, v_currency_code,v_facts_rep_show);
457
458 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'retcode_facts is '||retcode_facts|| ' FV_FACTS_TRANSACTIONS.v_g_edit_check_code' || FV_FACTS_TRANSACTIONS.v_g_edit_check_code);
459
460 -- Commenting out the code which checks if required edit checks passed
461 /*Checking whether atleast one treasury symbol passed required edit checks successfully*/
462 /* l_sub_sbr:=FV_FACTS_TRANSACTIONS.v_g_edit_check_code;
463 IF l_sub_sbr <> 2 AND l_one_edit_pass=0 THEN
464 l_one_edit_pass:=1;
465 END IF;*/
466
467 IF (retcode_facts <> 0 )THEN
468 IF (retcode_facts =1 )THEN
469
470 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
471 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Unable to process FACTS II in sbr for retcode_facts= 1 and TS ...'|| parm_treasury_value||errbuf_facts);
472 END IF;
473 -- Commenting out the code which checks if required edit checks passed
474 /* IF (FV_FACTS_TRANSACTIONS.v_g_edit_check_code = 2)THEN
475 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Purging old data as Required Edits failed for the Treasury Symbol...'|| parm_treasury_value||errbuf_facts);
476 retcode :=1;
477 --Purging old data from fv_facts_temp table
478 purge_facts_transactions;
479 --return; bug 9191060; if edits fail for one process, the other processes should continue
480 END IF;*/
481
482 END IF;
483 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
484 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Unable to process FACTS II in sbr for TS ...'|| parm_treasury_value||errbuf_facts);
485 END IF;
486
487 END IF;
488 END LOOP;
489 -- end for if retcode_facts <> 0
490 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'FV_FACTS_TRANSACTIONS.v_g_edit_check_code ->'||FV_FACTS_TRANSACTIONS.v_g_edit_check_code);
491
492 -- Commentd out code which checks if required edit checks passedd
493
494 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
495 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'CALLING BUILD REPORT LINES.....');
496 END IF;
497 build_report_lines;
498
499 IF g_error_code <> 0 THEN
500 errbuf := errbuf || 'Processing for Treasury Symbol '|| parm_treasury_value||' FAILED '|| g_error_message;
501 ELSE
502 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
503 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'SUBMITTING sbr REPORT FOR TS.....'||parm_treasury_value);
504 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'BEFORE CALLING REPORT -> v_retcode :'||v_retcode);
505 END IF;
506
507 IF v_retcode IN (0, 1) THEN
508 l_request_id := FND_REQUEST.SUBMIT_REQUEST ('FV', 'FVSBRCMR', '', '', FALSE, v_sequence_id, v_sob, v_period_name, p_units, v_report_type, v_end_date,v_period_fiscal_year);
509 IF l_request_id = 0 THEN
510 errbuf := 'Error submitting Consolidated Financial Statements Report';
511 retcode := -1;
512 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',errbuf);
513 RETURN;
514 ELSE
515 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
516 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'CONCURRENT REQUEST ID FOR CONSOLIDATED FINANCIAL STATEMENTS REPORT - ' || l_request_id);
517 END IF;
518 END IF;
519 END IF;
520 END IF;
521
522 COMMIT;
523
524 if errbuf_facts is not null then
525 errbuf := errbuf_facts|| ' -- Due to Error unable to submit of FACTS II Process ';
526 else
527 errbuf := errbuf|| 'Unable to submit FACSTS II Process due to unknow error';
528 end if ;
529 retcode :=1;
530 -- END IF;
531 ELSE
532 -- Processing NON-SBR report line definitions
533 populate_ccid;
534 build_dynamic_query;
535 IF v_retcode IN (0, 1) THEN
536 process_report_line;
537 END IF;
538 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
539 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'BEFORE CALLING REPORT -> v_retcode :'||v_retcode);
540 END IF;
541 IF v_retcode IN (0, 1) THEN
542 l_request_id := FND_REQUEST.SUBMIT_REQUEST ('FV', 'FVCFSCMR', '', '', FALSE, v_sequence_id, v_sob, v_period_name, p_units, v_report_type, v_end_date );
543 IF l_request_id = 0 THEN
544 errbuf := 'Error submitting Consolidated Financial Statements Report';
545 retcode := -1;
546 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',errbuf);
547 RETURN;
548 ELSE
549
550 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
551 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'CONCURRENT REQUEST ID FOR CONSOLIDATED FINANCIAL STATEMENTS REPORT - ' || l_request_id);
552 END IF;
553 COMMIT;
554 END IF;
555 END IF;
556 retcode := v_retcode;
557 errbuf := v_errbuf;
558 END IF;
559
560 EXCEPTION
561 WHEN OTHERS THEN
562 retcode := SQLCODE ;
563 errbuf := SQLERRM || ' [MAIN] ' ;
564 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
565 ROLLBACK;
566 COMMIT ;
567 END main;
568 -- =============================================================
569 PROCEDURE get_qualifier_segments
570 IS
571 l_module_name VARCHAR2(200) := g_module_name || 'get_qualifier_segments';
572 num_boolean BOOLEAN ;
573 apps_id NUMBER DEFAULT 101 ;
574 flex_code VARCHAR2(25) DEFAULT 'GL#' ;
575 seg_number NUMBER ;
576 seg_app_name VARCHAR2(40) ;
577 seg_prompt VARCHAR2(25) ;
578 seg_value_set_name VARCHAR2(40) ;
579 invalid_segment EXCEPTION ;
580 BEGIN
581 -- Get Accounting Segment
582 num_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM (apps_id, flex_code, v_chart_of_accounts_id, 'GL_ACCOUNT', seg_number);
583 IF(num_boolean) THEN
584 num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO (apps_id, flex_code, v_chart_of_accounts_id, seg_number, v_acc_seg_name, seg_app_name, seg_prompt, seg_value_set_name);
585 SELECT flex_value_set_id
586 INTO v_acct_flex_value_set_id
587 FROM fnd_id_flex_segments_vl
588 WHERE application_id = 101
589 AND id_flex_code = 'GL#'
590 AND id_flex_num = v_chart_of_accounts_id
591 AND enabled_flag = 'Y'
592 AND segment_num = seg_number;
593 ELSE
594 RAISE invalid_segment;
595 END IF;
596 -- Get Balancing Segment
597 num_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM (apps_id, flex_code, v_chart_of_accounts_id, 'GL_BALANCING', seg_number);
598 IF(num_boolean) THEN
599 num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO (apps_id, flex_code, v_chart_of_accounts_id, seg_number, v_bal_seg_name, seg_app_name, seg_prompt, seg_value_set_name);
600 SELECT flex_value_set_id
601 INTO v_bal_flex_value_set_id
602 FROM fnd_id_flex_segments_vl
603 WHERE application_id = 101
604 AND id_flex_code = 'GL#'
605 AND id_flex_num = v_chart_of_accounts_id
606 AND enabled_flag = 'Y'
607 AND segment_num = seg_number;
608 ELSE
609 RAISE invalid_segment;
610 END IF;
611 v_acc_seg_name := UPPER(v_acc_seg_name) ;
612 v_bal_seg_name := UPPER(v_bal_seg_name) ;
613 EXCEPTION
614 WHEN invalid_segment THEN
615 v_retcode := -1;
616 v_errbuf := 'Error getting Balancing and Accounting segments.';
617 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.invalid_segment',v_errbuf);
618 ROLLBACK;
619 RETURN;
620 WHEN OTHERS THEN
621 v_retcode := SQLCODE;
622 v_errbuf := sqlerrm ;
623 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
624 RETURN;
625 END get_qualifier_segments ;
626 -- =============================================================
627 PROCEDURE populate_ccid
628 IS
629 l_module_name VARCHAR2(200) := g_module_name || 'populate_ccid';
630 TYPE t_seg_name_table
631 IS
632 TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
633 TYPE t_seg_str_table
634 IS
635 TABLE OF VARCHAR2(10000) INDEX BY BINARY_INTEGER;
636 v_seg t_seg_name_table;
637 v_statement VARCHAR2(25000);
638 v_insert_statement VARCHAR2(25000);
639 v_seg_str t_seg_str_table;
640 CURSOR flex
641 IS
642 SELECT application_column_name ,
643 flex_value_set_id
644 FROM fnd_id_flex_segments
645 WHERE id_flex_code = 'GL#'
646 AND application_id = 101
647 AND id_flex_num = v_chart_of_accounts_id;
648 CURSOR child_value(seg VARCHAR2,sid NUMBER)
649 IS
650 SELECT child_flex_value_low,
651 child_flex_value_high
652 FROM fnd_flex_value_hierarchies
653 WHERE parent_FLEX_value = seg
654 AND flex_value_set_id = sid;
655
656 child_rec child_value%ROWTYPE;
657 CURSOR CREC
658 IS
659 SELECT d.line_id ,
660 d.line_detail_id,
661 segment1 ,
662 segment2 ,
663 segment3 ,
664 segment4 ,
665 segment5 ,
666 segment6 ,
667 segment7 ,
668 segment8 ,
669 segment9 ,
670 segment10 ,
671 segment11 ,
672 segment12 ,
673 segment13 ,
674 segment14 ,
675 segment15 ,
676 segment16 ,
677 segment17 ,
678 segment18 ,
679 segment19 ,
680 segment20 ,
681 segment21 ,
682 segment22 ,
683 segment23 ,
684 segment24 ,
685 segment25 ,
686 segment26 ,
687 segment27 ,
688 segment28 ,
689 segment29 ,
690 segment30
691 FROM fv_cfs_rep_line_dtl d,
692 fv_cfs_rep_lines L
693 WHERE l.report_type = v_report_type
694 AND d.line_id = l.line_id
695 AND l.set_of_books_id = v_sob
696 ORDER BY 2;
697 CURSOR SBR_CREC
698 IS
699 SELECT d.sbr_line_id ,
700 d.sbr_line_acct_id ,
701 segment1 ,
702 segment2 ,
703 segment3 ,
704 segment4 ,
705 segment5 ,
706 segment6 ,
707 segment7 ,
708 segment8 ,
709 segment9 ,
710 segment10 ,
711 segment11 ,
712 segment12 ,
713 segment13 ,
714 segment14 ,
715 segment15 ,
716 segment16 ,
717 segment17 ,
718 segment18 ,
719 segment19 ,
720 segment20 ,
721 segment21 ,
722 segment22 ,
723 segment23 ,
724 segment24 ,
725 segment25 ,
726 segment26 ,
727 segment27 ,
728 segment28 ,
729 segment29 ,
730 segment30
731 FROM fv_sbr_definitions_accts d,
732 fv_sbr_definitions_lines l
733 WHERE d.sbr_line_id = l.sbr_line_id
734 AND l.set_of_books_id = v_sob
735 ORDER BY 2;
736
737 l_and VARCHAR2(5);
738 l_child VARCHAR2(32000);
739 l_no_of_child NUMBER;
740 l_no_of_seg NUMBER;
741 l_segno NUMBER;
742 l_cnt NUMBER;
743 BEGIN
744
745
746 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Ccid process starts');
747 IF upper(v_report_type)=upper('SBR') THEN
748 FOR sbr_crec_rec IN SBR_CREC
749 LOOP
750 v_seg(1) := sbr_crec_rec.segment1;
751 v_seg(2) := sbr_crec_rec.segment2;
752 v_seg(3) := sbr_crec_rec.segment3;
753 v_seg(4) := sbr_crec_rec.segment4;
754 v_seg(5) := sbr_crec_rec.segment5;
755 v_seg(6) := sbr_crec_rec.segment6;
756 v_seg(7) := sbr_crec_rec.segment7;
757 v_seg(8) := sbr_crec_rec.segment8;
758 v_seg(9) := sbr_crec_rec.segment9;
759 v_seg(10) := sbr_crec_rec.segment10;
760 v_seg(11) := sbr_crec_rec.segment11;
761 v_seg(12) := sbr_crec_rec.segment12;
762 v_seg(13) := sbr_crec_rec.segment13;
763 v_seg(14) := sbr_crec_rec.segment14;
764 v_seg(15) := sbr_crec_rec.segment15;
765 v_seg(16) := sbr_crec_rec.segment16;
766 v_seg(17) := sbr_crec_rec.segment17;
767 v_seg(18) := sbr_crec_rec.segment18;
768 v_seg(19) := sbr_crec_rec.segment19;
769 v_seg(20) := sbr_crec_rec.segment20;
770 v_seg(21) := sbr_crec_rec.segment21;
771 v_seg(22) := sbr_crec_rec.segment22;
772 v_seg(23) := sbr_crec_rec.segment23;
773 v_seg(24) := sbr_crec_rec.segment24;
774 v_seg(25) := sbr_crec_rec.segment25;
775 v_seg(26) := sbr_crec_rec.segment26;
776 v_seg(27) := sbr_crec_rec.segment27;
777 v_seg(28) := sbr_crec_rec.segment28;
778 v_seg(29) := sbr_crec_rec.segment29;
779 v_seg(30) := sbr_crec_rec.segment30;
780 v_statement := NULL;
781 FOR i IN 1 ..30
782 LOOP
783 v_seg_str(i) := NULL;
784 END LOOP;
785 l_no_of_seg := 0;
786 FOR flex_rec IN flex
787 LOOP
788 l_no_of_child := 0;
789 l_and := NULL;
790 -- Check if the segment value is a parent
791 l_segno := SUBSTR(flex_rec.application_column_name,8,2);
792 IF v_seg(l_segno) IS NOT NULL THEN
793 --1
794 SELECT COUNT(*)
795 INTO l_cnt
796 FROM fnd_flex_value_hierarchies
797 WHERE parent_FLEX_value = v_seg(l_segno)
798 AND flex_value_set_id = flex_rec.flex_value_set_id;
799 IF (l_cnt > 0) THEN
800 -- 2
801 OPEN child_value(v_seg(l_segno) , flex_rec.flex_value_set_id);
802 l_and := NULL;
803 IF (l_no_of_seg > 0) THEN
804 l_and := ' AND ';
805 END IF;
806 l_child := l_and || ' ( ';
807 LOOP
808 FETCH child_value INTO child_rec;
809 EXIT
810 WHEN child_value%NOTFOUND ;
811 IF l_no_of_child > 0 THEN
812 l_child := l_child || ' OR ';
813 END IF;
814 l_child := l_child || flex_rec.application_column_name || ' between '|| '''' || child_rec.child_flex_value_low || ''' and ''' || child_rec.child_flex_value_high || '''' || fnd_global.local_chr(10);
815 l_no_of_child := l_no_of_child + 1;
816 END LOOP;
817 l_child := l_child || ' )' ;
818 l_and := NULL;
819 v_statement := v_statement || l_and || L_CHILD || fnd_global.local_chr(10);
820 CLOSE CHILD_VALUE;
821 ELSE
822 -- 2
823 IF (l_no_of_seg > 0) THEN
824 l_and := ' AND ';
825 END IF;
826 v_statement := v_statement || l_and || flex_rec.application_column_name || ' = ''' || v_seg(l_segno) || ''' ' || fnd_global.local_chr(10);
827 END IF;
828 -- cnt > 0
829 l_no_of_seg := l_no_of_seg + 1;
830 END IF;
831 END LOOP;
832 --crec_rec
833 IF (v_statement IS NOT NULL) THEN
834 v_insert_statement := 'insert into fv_sbr_ccids_gt(
835 sbr_line_acct_id,
836 ccid)
837 select ' || sbr_crec_rec.sbr_line_acct_id || ', code_combination_id ' ||
838 ' from gl_code_combinations WHERE ' ||v_acc_seg_name || 'like '':b_account_number%'''' and template_id is null and '
839 || ' chart_of_accounts_id = :B_CHART_OF_ACCOUNTS_ID
840 and not exists (select code_combination_id
841 from fv_sbr_ccids_gt FCT ' || 'where fct.detail_id = :b_line_detail_id '|| ')';
842 EXECUTE immediate v_insert_statement USING V_CHART_OF_ACCOUNTS_ID,
843 sbr_crec_rec.sbr_line_acct_id;
844 COMMIT;
845 END IF;
846 END LOOP;
847 --SBR POPULATE CCID
848 ELSE
849 /*NON SBR data*/
850 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Ccid process starts');
851 FOR crec_rec IN crec
852 LOOP
853 v_seg(1) := crec_rec.segment1;
854 v_seg(2) := crec_rec.segment2;
855 v_seg(3) := crec_rec.segment3;
856 v_seg(4) := crec_rec.segment4;
857 v_seg(5) := crec_rec.segment5;
858 v_seg(6) := crec_rec.segment6;
859 v_seg(7) := crec_rec.segment7;
860 v_seg(8) := crec_rec.segment8;
861 v_seg(9) := crec_rec.segment9;
862 v_seg(10) := crec_rec.segment10;
863 v_seg(11) := crec_rec.segment11;
864 v_seg(12) := crec_rec.segment12;
865 v_seg(13) := crec_rec.segment13;
866 v_seg(14) := crec_rec.segment14;
867 v_seg(15) := crec_rec.segment15;
868 v_seg(16) := crec_rec.segment16;
869 v_seg(17) := crec_rec.segment17;
870 v_seg(18) := crec_rec.segment18;
871 v_seg(19) := crec_rec.segment19;
872 v_seg(20) := crec_rec.segment20;
873 v_seg(21) := crec_rec.segment21;
874 v_seg(22) := crec_rec.segment22;
875 v_seg(23) := crec_rec.segment23;
876 v_seg(24) := crec_rec.segment24;
877 v_seg(25) := crec_rec.segment25;
878 v_seg(26) := crec_rec.segment26;
879 v_seg(27) := crec_rec.segment27;
880 v_seg(28) := crec_rec.segment28;
881 v_seg(29) := crec_rec.segment29;
882 v_seg(30) := crec_rec.segment30;
883 v_statement := NULL;
884 FOR i IN 1 ..30
885 LOOP
886 v_seg_str(i) := NULL;
887 END LOOP;
888 l_no_of_seg := 0;
889 FOR flex_rec IN flex
890 LOOP
891 l_no_of_child := 0;
892 l_and := NULL;
893 -- Check if the segment value is a parent
894 l_segno := SUBSTR(flex_rec.application_column_name,8,2);
895 IF v_seg(l_segno) IS NOT NULL THEN
896 /* 1 */
897 SELECT COUNT(*)
898 INTO l_cnt
899 FROM fnd_flex_value_hierarchies
900 WHERE parent_FLEX_value = v_seg(l_segno)
901 AND flex_value_set_id = flex_rec.flex_value_set_id;
902 IF (l_cnt > 0) THEN
903 /* 2 */
904 OPEN child_value(v_seg(l_segno) , flex_rec.flex_value_set_id);
905 l_and := NULL;
906 IF (l_no_of_seg > 0) THEN
907 l_and := ' AND ';
908 END IF;
909 l_child := l_and || ' ( ';
910 LOOP
911 FETCH child_value INTO child_rec;
912 EXIT
913 WHEN child_value%NOTFOUND ;
914 IF l_no_of_child > 0 THEN
915 l_child := l_child || ' OR ';
916 END IF;
917 l_child := l_child || flex_rec.application_column_name || ' between '|| '''' || child_rec.child_flex_value_low || ''' and ''' || child_rec.child_flex_value_high || '''' || fnd_global.local_chr(10);
918 l_no_of_child := l_no_of_child + 1;
919 END LOOP;
920 l_child := l_child || ' )' ;
921 l_and := NULL;
922 v_statement := v_statement || l_and || L_CHILD || fnd_global.local_chr(10);
923 CLOSE CHILD_VALUE;
924 ELSE
925 /* 2 */
926 IF (l_no_of_seg > 0) THEN
927 l_and := ' AND ';
928 END IF;
929 v_statement := v_statement || l_and || flex_rec.application_column_name || ' = ''' || v_seg(l_segno) || ''' ' || fnd_global.local_chr(10);
930 END IF;
931 /* cnt > 0 */
932 l_no_of_seg := l_no_of_seg + 1;
933 END IF;
934 END LOOP;
935 /* crec_rec */
936 IF (v_statement IS NOT NULL) THEN
937 v_insert_statement := 'insert into FV_CCID_CFS_GT(
938 detail_id,
939 ccid)
940 select ' || crec_rec.line_detail_id || ', code_combination_id '
941 || ' from gl_code_combinations WHERE ' || v_statement || ' and
942 template_id is null and ' || ' chart_of_accounts_id = :B_CHART_OF_ACCOUNTS_ID
943 and not exists (select code_combination_id
944 from fv_ccid_CFS_GT FCT ' || 'where fct.detail_id = :b_line_detail_id '|| ')';
945
946 EXECUTE immediate v_insert_statement USING V_CHART_OF_ACCOUNTS_ID,
947 crec_rec.line_detail_id;
948 COMMIT;
949 END IF;
950 END LOOP;
951 END IF;
952 /* flex_crec */
953 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
954 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Popualte CCID Completed');
955 END IF;
956 EXCEPTION
957 WHEN OTHERS THEN
958 v_retcode := -1;
959 v_errbuf := '[POPULATE-CCID]' || sqlerrm;
960 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
961 RETURN;
962 END populate_ccid;
963 -- =============================================================
964 PROCEDURE build_dynamic_query
965 IS
966 l_module_name VARCHAR2(200) := g_module_name || 'build_dynamic_query';
967 CURSOR flex_columns_cursor
968 IS
969 SELECT UPPER(glflex.application_column_name) column_name,
970 flex_value_set_id
971 FROM fnd_id_flex_segments glflex
972 WHERE glflex.application_id = 101
973 AND glflex.id_flex_num = v_chart_of_accounts_id
974 AND glflex.id_flex_code = 'GL#'
975 ORDER BY glflex.application_column_name;
976
977 l_flex_column_name fnd_id_flex_segments.application_column_name%TYPE;
978 l_flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
979 l_temp1 VARCHAR2(8000) DEFAULT '';
980 l_temp2 VARCHAR2(8000) DEFAULT '';
981 l_table_name VARCHAR2(50);
982 l_period_name_where VARCHAR2(500);
983 l_stage NUMBER;
984 l_out VARCHAR2(32000);
985 l_column_name VARCHAR2(30);
986 l_glbal_temp VARCHAR2(32000);
987 BEGIN
988 v_fct1_attr_select :=
989 ' SELECT SUM(NVL(DECODE(:cv_balance_type,
990 ''B'', ROUND(NVL(fctbal.begin_balance,0),2),
991 ''E'', ROUND(NVL(fctbal.balance_amount,0))),0) )
992 FROM fv_cfs_rep_line_dtl dets,
993 fv_ccid_cfs_gt fvcc,
994 fv_facts1_period_attributes fctbal
995 WHERE dets.line_id = :cv_line_id
996 AND dets.line_detail_id = :cv_line_detail_id
997 AND dets.line_detail_id = fvcc.detail_id
998 AND fctbal.ccid = fvcc.ccid
999 AND fctbal.set_of_books_id = :b_sob
1000 AND fctbal.period_year = :cv_period_fiscal_year
1001 AND nvl(dets.cust_non_cust, nvl(fctbal.cust_non_cust, 1)) = nvl(fctbal.cust_non_cust, 1)
1002 AND nvl(dets.exch_non_exch, nvl(fctbal.exch_non_exch, 1)) = nvl(fctbal.exch_non_exch, 1)
1003 AND EXISTS
1004 (SELECT 1
1005 FROM fv_fund_parameters ffp
1006 WHERE set_of_books_id = :b_sob
1007 AND fund_category like nvl(dets.fund_category, ''%'')
1008 AND ffp.fund_value = fctbal.fund_value
1009 AND ( (dets.fund_status = ''E'' and trunc(fund_expire_date) <= :cv_end_date )
1010 OR (dets.fund_status = ''U''
1011 and (trunc(fund_expire_date) >= :cv_end_date or fund_expire_date is null)
1012 and (trunc(fund_cancel_date) > :cv_end_date or fund_cancel_date is null))
1013 OR (nvl(dets.fund_status,''B'') = ''B'' ))) '
1014 ;
1015 l_stage := 1;
1016 l_out := v_fct1_attr_select;
1017 v_glbal_curid := dbms_sql.open_cursor;
1018 dbms_sql.parse(v_glbal_curid, v_fct1_attr_select, dbms_sql.v7);
1019 dbms_sql.define_column(v_glbal_curid, 1, v_amount);
1020 dbms_sql.bind_variable(v_glbal_curid,':b_sob',v_sob);
1021 v_fct1_sel := 'SELECT ROUND(SUM(NVL(ffrt.amount,0) ),2) ';
1022 v_fct1_rcpt_sel := v_fct1_sel || ' , ffrt.recipient_name ';
1023 l_temp1 := '
1024 FROM fv_ccid_cfs_gt fvcc,
1025 fv_cfs_rep_line_dtl dets,
1026 fv_facts1_period_balances_v ffrt
1027 WHERE dets.line_id = :cv_line_id
1028 AND dets.line_detail_id = :cv_line_detail_id
1029 AND dets.line_detail_id = fvcc.detail_id
1030 AND ffrt.ccid = fvcc.ccid
1031 AND ffrt.period_year = :cv_period_fiscal_year
1032 AND ffrt.set_of_books_id = :b_sob
1033 AND ffrt.period_num <= :cv_period_num
1034 AND ffrt.balance_type = NVL(:cv_balance_type, ffrt.balance_type)
1035 AND nvl(dets.fed_non_fed, nvl(ffrt.g_ng_indicator, 1)) =
1036 REPLACE(nvl(ffrt.g_ng_indicator, nvl(dets.fed_non_fed, 1)), ' || '''' || ' ' || '''' ||
1037 ',
1038 nvl(dets.fed_non_fed, nvl(ffrt.g_ng_indicator, 1)))
1039 AND nvl(dets.cust_non_cust, nvl(ffrt.cust_non_cust, 1)) = nvl(ffrt.cust_non_cust, 1)
1040 AND nvl(dets.exch_non_exch, nvl(ffrt.exch_non_exch, 1)) = nvl(ffrt.exch_non_exch, 1)' ;
1041 v_fct1_sel := v_fct1_sel || l_temp1;
1042 v_fct1_rcpt_sel := v_fct1_rcpt_sel || l_temp1 || ' GROUP BY ffrt.recipient_name ';
1043 l_stage := 3;
1044 l_out := v_fct1_rcpt_sel;
1045 v_fct1_rcpt_sel_curid := dbms_sql.open_cursor;
1046 dbms_sql.parse(v_fct1_rcpt_sel_curid, v_fct1_rcpt_sel, dbms_sql.v7);
1047 dbms_sql.define_column(v_fct1_rcpt_sel_curid, 1, v_amount);
1048 dbms_sql.define_column(v_fct1_rcpt_sel_curid, 2, v_recipient_name, 240);
1049 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':b_sob',v_sob);
1050 v_fct1_rcpt_sel2 := REPLACE(v_fct1_rcpt_sel, 'GROUP BY ffrt.recipient_name', 'AND ffrt.recipient_name = :cv_recipient_name');
1051 v_fct1_rcpt_sel2 := REPLACE(v_fct1_rcpt_sel2, ', ffrt.recipient_name', ', 1');
1052 l_stage := 4;
1053 l_out := v_fct1_rcpt_sel2;
1054 v_fct1_rcpt_sel2_curid := dbms_sql.open_cursor;
1055 dbms_sql.parse(v_fct1_rcpt_sel2_curid, v_fct1_rcpt_sel2, dbms_sql.v7);
1056 dbms_sql.define_column(v_fct1_rcpt_sel2_curid, 1, v_amount);
1057 dbms_sql.define_column(v_fct1_rcpt_sel2_curid, 2, v_recipient_name, 240);
1058 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':b_sob',v_sob);
1059 l_stage := 5;
1060 l_out := v_fct1_sel;
1061 v_fct1_sel_curid := dbms_sql.open_cursor;
1062 dbms_sql.parse(v_fct1_sel_curid, v_fct1_sel, dbms_sql.v7);
1063 dbms_sql.define_column(v_fct1_sel_curid, 1, v_amount);
1064 dbms_sql.bind_variable(v_fct1_sel_curid,':b_sob',v_sob);
1065 v_glbal_select :=
1066 ' SELECT /*+ USE_HASH (glbal) */
1067 NVL(DECODE(:cv_balance_type,
1068 ''B'', ROUND(NVL(SUM(NVL(glbal.begin_balance_dr,0) -
1069 NVL(glbal.begin_balance_cr,0)),0),2),
1070 ''E'', ROUND(NVL(SUM((NVL(glbal.begin_balance_dr,0) -
1071 NVL(glbal.begin_balance_cr,0)) +
1072 (NVL(glbal.period_net_dr,0) -
1073 NVL(glbal.period_net_cr,0))),0),2)),0)
1074 FROM fv_cfs_rep_line_dtl dets,
1075 fv_ccid_cfs_gt fvcc,
1076 gl_code_combinations glc,
1077 gl_balances glbal
1078 WHERE dets.line_id = :cv_line_id
1079 AND dets.line_detail_id = :cv_line_detail_id
1080 AND dets.line_detail_id = fvcc.detail_id
1081 AND glc.code_combination_id = fvcc.ccid
1082 AND glc.chart_of_accounts_id = :b_chart_of_accounts_id
1083 AND glbal.code_combination_id = glc.code_combination_id
1084 AND glbal.ledger_id = :b_sob
1085 AND glbal.period_year = :cv_period_fiscal_year
1086 AND glbal.period_num = :cv_period_num
1087 --AND glbal.currency_code <> ''STAT''
1088 AND glbal.currency_code = :v_currency_code
1089 AND glbal.actual_flag = ''A''
1090 AND EXISTS
1091 (SELECT 1
1092 FROM fv_fund_parameters ffp
1093 WHERE set_of_books_id = :b_sob
1094 AND fund_category like nvl(dets.fund_category, ''%'')
1095 AND ffp.fund_value = glc.'
1096 ||v_bal_seg_name||'
1097 AND ( (dets.fund_status = ''E'' and trunc(fund_expire_date) <= :cv_end_date )
1098 OR (dets.fund_status = ''U''
1099 and (trunc(fund_expire_date) >= :cv_end_date or fund_expire_date is null)
1100 and (trunc(fund_cancel_date) > :cv_end_date or fund_cancel_date is null))
1101 OR (nvl(dets.fund_status,''B'') = ''B'' ))) ';
1102 l_stage := 6;
1103 l_out := v_glbal_select;
1104 v_sbr_curid := dbms_sql.open_cursor;
1105 dbms_sql.parse(v_sbr_curid, v_glbal_select, dbms_sql.v7);
1106 dbms_sql.define_column(v_sbr_curid, 1, v_amount);
1107 dbms_sql.bind_variable(v_sbr_curid,':b_chart_of_accounts_id', v_chart_of_accounts_id);
1108 dbms_sql.bind_variable(v_sbr_curid,':b_sob',v_sob);
1109 dbms_sql.bind_variable(v_sbr_curid,':v_currency_code',v_currency_code);
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 v_retcode := SQLCODE ;
1113 v_errbuf := SQLERRM || ' [BUILD_DYNAMIC_QUERY] ' ;
1114 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
1115 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Stage it errors ' || l_stage);
1116 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_out);
1117 RETURN;
1118 END build_dynamic_query;
1119 -- =============================================================
1120 PROCEDURE process_report_line
1121 IS
1122 l_module_name VARCHAR2(200) := g_module_name || 'process_report_line';
1123 CURSOR fv_cfs_lines_cur
1124 IS
1125 SELECT line_id ,
1126 line_label ,
1127 sequence_number ,
1128 line_number ,
1129 line_type ,
1130 natural_balance_type,
1131 by_recipient
1132 FROM fv_cfs_rep_lines
1133 WHERE set_of_books_id = v_sob
1134 AND report_type = v_report_type
1135 ORDER BY sequence_number;
1136
1137 l_line_cnt NUMBER;
1138 BEGIN
1139 istotal_cal :=0;
1140 FOR lines_rec IN fv_cfs_lines_cur
1141 LOOP
1142 IF v_retcode IN (0, 1) THEN
1143 v_line_id := lines_rec.line_id;
1144 v_line_label := lines_rec.line_label;
1145 v_line_type := lines_rec.line_type;
1146 v_sequence_number := lines_rec.sequence_number;
1147 v_line_number := lines_rec.line_number;
1148 v_natural_balance_type := lines_rec.natural_balance_type;
1149 v_by_recipient := lines_rec.by_recipient;
1150 v_col_1_amt := 0;
1151 v_col_2_amt := 0;
1152 v_col_3_amt := 0;
1153 v_col_4_amt := 0;
1154 -- SCNP ER 9479298
1155 v_col_5_amt := 0;
1156 v_col_6_amt := 0;
1157 v_col_7_amt := 0;
1158 v_col_8_amt := 0;
1159 IF lines_rec.line_type IN ('D', 'D2') THEN
1160 -- $$$dbms_sql.bind_variable(v_cursor_id1,':cv_line_id',v_line_id);
1161 SELECT COUNT(*)
1162 INTO l_line_cnt
1163 FROM fv_cfs_rep_line_dtl
1164 WHERE line_id = v_line_id;
1165 IF l_line_cnt = 0 THEN
1166 NULL;
1167 populate_temp_table;
1168 ELSE
1169 process_detail_line;
1170 END IF;
1171 IF lines_rec.line_type = 'D2' THEN
1172 v_col_1_amt := ABS(v_col_1_amt);
1173 v_col_2_amt := ABS(v_col_1_amt);
1174 v_col_3_amt := ABS(v_col_1_amt);
1175 v_col_4_amt := ABS(v_col_1_amt);
1176 END IF;
1177 ELSIF lines_rec.line_type IN ('S', 'T') THEN
1178 SELECT COUNT( *)
1179 INTO l_line_cnt
1180 FROM fv_cfs_rep_line_calc
1181 WHERE line_id = v_line_id;
1182 IF l_line_cnt = 0 THEN
1183 v_retcode := -1;
1184 v_errbuf := 'SEED Data not properly Loaded. Please Verify and reinvoke the Process.';
1185 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',v_errbuf);
1186 RETURN;
1187 END IF;
1188 -- $$$dbms_sql.bind_variable(v_cursor_id1,':cv_line_id',v_line_id);
1189 process_total_line;
1190 istotal_cal:=1;
1191 populate_temp_table;
1192 istotal_cal :=0;
1193 ELSIF lines_rec.line_type IN ('L', 'F') THEN
1194 v_col_1_amt := NULL;
1195 v_col_2_amt := NULL;
1196 v_col_3_amt := NULL;
1197 v_col_4_amt := NULL;
1198 -- SCNP ER 9479298
1199 v_col_5_amt := NULL;
1200 v_col_6_amt := NULL;
1201 v_col_7_amt := NULL;
1202 v_col_8_amt := NULL;
1203 populate_temp_table;
1204 END IF;
1205 END IF;
1206 END LOOP;
1207 EXCEPTION
1208 WHEN OTHERS THEN
1209 v_retcode := SQLCODE ;
1210 v_errbuf := SQLERRM || ' [PROCESS_REPORT_LINE] ' ;
1211 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
1212 RETURN;
1213 END process_report_line;
1214 -- =============================================================
1215 PROCEDURE get_one_time_values
1216 IS
1217 l_module_name VARCHAR2(200) := g_module_name || 'get_one_time_values';
1218 l_stage NUMBER;
1219 BEGIN
1220 fv_utility.log_mesg('IN: '||l_module_name);
1221 l_stage := 1;
1222 SELECT chart_of_accounts_id,
1223 currency_code
1224 INTO v_chart_of_accounts_id,
1225 v_currency_code
1226 FROM gl_ledgers_public_v
1227 WHERE ledger_id = v_sob ;
1228
1229 fv_utility.log_mesg('After gl_ledgers_public_v: '||l_module_name);
1230 -- Get period number and fiscal year being run for
1231 l_stage := 2;
1232 SELECT TRUNC(end_date),
1233 period_num ,
1234 period_year ,
1235 end_date
1236 INTO v_end_date ,
1237 v_period_num ,
1238 v_period_fiscal_year,
1239 v_end_period_end_date
1240 FROM gl_period_statuses
1241 WHERE ledger_id = v_sob
1242 AND application_id = '101'
1243 AND period_name = v_period_name;
1244
1245 v_end_period := v_period_num;
1246 -- Get begin period num, name and end date of the
1247 -- first non adjusting period of the current year
1248 l_stage := 3;
1249 SELECT period_num,
1250 period_name ,
1251 end_date
1252 INTO v_begin_period,
1253 v_begin_period_name ,
1254 v_begin_period_end_date
1255 FROM gl_period_statuses
1256 WHERE ledger_id = v_sob
1257 AND period_year = v_period_fiscal_year
1258 AND adjustment_period_flag = 'N'
1259 AND application_id = '101'
1260 AND period_num =
1261 (SELECT MIN(period_num)
1262 FROM gl_period_statuses
1263 WHERE ledger_id = v_sob
1264 AND period_year = v_period_fiscal_year
1265 AND adjustment_period_flag = 'N'
1266 AND application_id = '101'
1267 );
1268 -- Get begin period num, name and end date of the
1269 -- first non adjusting period of the prior year
1270 l_stage := 4;
1271 SELECT period_num,
1272 period_name ,
1273 end_date
1274 INTO v_begin_period_1,
1275 v_begin_period_name_1 ,
1276 v_begin_period_1_end_date
1277 FROM gl_period_statuses
1278 WHERE ledger_id = v_sob
1279 AND period_year = v_period_fiscal_year-1
1280 AND adjustment_period_flag = 'N'
1281 AND application_id = '101'
1282 AND period_num =
1283 (SELECT MIN(period_num)
1284 FROM gl_period_statuses
1285 WHERE ledger_id = v_sob
1286 AND period_year = v_period_fiscal_year-1
1287 AND adjustment_period_flag = 'N'
1288 AND application_id = '101'
1289 ) ;
1290 -- Get py period end date for the period being run
1291 l_stage := 5;
1292 SELECT end_date
1293 INTO v_end_period_1_end_date
1294 FROM gl_period_statuses
1295 WHERE ledger_id = v_sob
1296 AND period_year = v_period_fiscal_year-1
1297 AND application_id = '101'
1298 AND period_num = v_period_num;
1299
1300 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Chart of accounts id: '||v_chart_of_accounts_id);
1301 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Fiscal year: '||v_period_fiscal_year);
1302 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_period_name: '||v_period_name);
1303 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_period_num: '||v_period_num);
1304 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_end_date: '||v_end_date);
1305 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_begin_period: '||v_begin_period);
1306 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_begin_period_end_date: '||v_begin_period_end_date);
1307 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_begin_period_1: '||v_begin_period_1);
1308 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_begin_period_1_end_date: '||v_begin_period_1_end_date);
1309 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_end_period_1_end_date: '||v_end_period_1_end_date);
1310 EXCEPTION
1311 WHEN NO_DATA_FOUND THEN
1312 IF l_stage = 4 OR l_stage = 5 THEN
1313 fv_utility.log_mesg('No calendar has been setup for the year: '||TO_CHAR(v_period_fiscal_year-1));
1314 fv_utility.log_mesg('The Prior Year column will have zero amounts.');
1315 v_begin_period_1 := 0;
1316 v_begin_period_name_1 := 'XXX';
1317 v_begin_period_1_end_date := to_date('01/01/1900', 'mm/dd/yyyy');
1318 v_end_period_1_end_date := to_date('01/01/1900', 'mm/dd/yyyy');
1319 ELSE
1320 v_retcode := -1;
1321 v_errbuf := 'When no data found error in get_one_time_values, at stage: '||l_stage;
1322 fnd_file.put_line(fnd_file.log, v_errbuf);
1323 RETURN;
1324 END IF;
1325 WHEN OTHERS THEN
1326 v_retcode := -1;
1327 v_errbuf := 'When others error in get_one_time_values, at stage: '||l_stage;
1328 fnd_file.put_line(fnd_file.log, v_errbuf);
1329 END get_one_time_values;
1330 -- =============================================================
1331 PROCEDURE process_detail_line
1332 IS
1333 l_module_name VARCHAR2(200) := g_module_name || 'process_detail_line';
1334 CURSOR fv_cfs_detail_cur
1335 IS
1336 SELECT line_detail_id,
1337 balance_type,
1338 cum_res,
1339 unexp_approp,
1340 budget_col,
1341 nbfa_col,
1342 flex_further_def,
1343 fed_non_fed,
1344 exch_non_exch,
1345 cust_non_cust,
1346 scnp_elim,
1347 DECODE(v_acc_seg_name, 'SEGMENT1', SEGMENT1, 'SEGMENT11',
1348 SEGMENT11, 'SEGMENT21', SEGMENT21, 'SEGMENT2', SEGMENT2, 'SEGMENT12',
1349 SEGMENT12, 'SEGMENT22', SEGMENT22, 'SEGMENT3', SEGMENT3, 'SEGMENT13',
1350 SEGMENT13, 'SEGMENT23', SEGMENT23, 'SEGMENT4', SEGMENT4, 'SEGMENT14',
1351 SEGMENT14, 'SEGMENT24', SEGMENT24, 'SEGMENT5', SEGMENT5, 'SEGMENT15',
1352 SEGMENT15, 'SEGMENT25', SEGMENT25, 'SEGMENT6', SEGMENT6, 'SEGMENT16', SEGMENT16,
1353 'SEGMENT26', SEGMENT26, 'SEGMENT7', SEGMENT7, 'SEGMENT17', SEGMENT17, 'SEGMENT27',
1354 SEGMENT27, 'SEGMENT8', SEGMENT8, 'SEGMENT18', SEGMENT18, 'SEGMENT28', SEGMENT28,
1355 'SEGMENT9', SEGMENT9, 'SEGMENT19', SEGMENT19, 'SEGMENT29', SEGMENT29, 'SEGMENT10',
1356 SEGMENT10, 'SEGMENT20', SEGMENT20, 'SEGMENT30', SEGMENT30) account_number,
1357 segment1
1358 || '.'
1359 || segment2
1360 || '.'
1361 || segment3
1362 || '.'
1363 || segment4
1364 || '.'
1365 || segment5
1366 || '.'
1367 || segment6
1368 || '.'
1369 || segment7
1370 || '.'
1371 || segment8
1372 || '.'
1373 || segment9
1374 || '.'
1375 || segment10
1376 || '.'
1377 || segment11
1378 || '.'
1379 || segment12
1380 || '.'
1381 || segment13
1382 || '.'
1383 || segment14
1384 || '.'
1385 || segment15
1386 || '.'
1387 || segment16
1388 || '.'
1389 || segment17
1390 || '.'
1391 || segment18
1392 || '.'
1393 || segment19
1394 || '.'
1395 || segment20
1396 || '.'
1397 || segment21
1398 || '.'
1399 || segment22
1400 || '.'
1401 || segment23
1402 || '.'
1403 || segment24
1404 || '.'
1405 || segment25
1406 || '.'
1407 || segment26
1408 || '.'
1409 || segment27
1410 || '.'
1411 || segment28
1412 || '.'
1413 || segment29
1414 || '.'
1415 || segment30 concatenated_segments
1416 FROM fv_cfs_rep_line_dtl
1417 WHERE line_id = v_line_id;
1418
1419 TYPE l_recipient_rec_type
1420 IS
1421 RECORD
1422 (
1423 recipient_name fv_facts_report_t2.recipient_name%TYPE,
1424 col_1_amt fv_cfs_rep_temp.col_1_amt%TYPE := 0,
1425 col_2_amt fv_cfs_rep_temp.col_1_amt%TYPE := 0,
1426 col_3_amt fv_cfs_rep_temp.col_1_amt%TYPE := 0,
1427 col_4_amt fv_cfs_rep_temp.col_1_amt%TYPE := 0);
1428 TYPE l_recipient_table
1429 IS
1430 TABLE OF l_recipient_rec_type INDEX BY BINARY_INTEGER;
1431 l_recipient_rec l_recipient_table;
1432 l_recipient_cnt BINARY_INTEGER := 1;
1433 l_found BOOLEAN;
1434 l_temp_str fv_cfs_rep_lines.line_label%TYPE;
1435 l_ignore INTEGER;
1436 l_prev_year_amount fv_cfs_rep_temp.col_1_amt%TYPE := 0;
1437 l_begin_balance NUMBER;
1438 l_end_balance NUMBER;
1439 l_period_name_1 gl_period_statuses.period_name%TYPE;
1440 l_period_name_2 gl_period_statuses.period_name%TYPE;
1441 l_begin_period_name gl_period_statuses.period_name%TYPE;
1442 l_begin_period_name_1 gl_period_statuses.period_name%TYPE;
1443 l_begin_period_name_2 gl_period_statuses.period_name%TYPE;
1444 l_period_fiscal_year NUMBER;
1445 l_begin_period NUMBER;
1446 l_end_period NUMBER := v_period_num;
1447 l_end_period_1 NUMBER ;
1448 l_end_period_name_1 gl_period_statuses.period_name%TYPE;
1449 l_begin_period_1 NUMBER;
1450 l_begin_period_end_date DATE;
1451 l_end_period_end_date DATE := v_end_date;
1452 l_begin_period_1_end_date DATE;
1453 l_end_period_1_end_date DATE;
1454 l_log_mesg VARCHAR2(32000) := '';
1455 l_conc_segs VARCHAR2(32000);
1456 l_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
1457 l_account_type VARCHAR2(1) := '';
1458 l_account_number gl_code_combinations.segment1%TYPE;
1459 l_balance_determined NUMBER;
1460 l_prev_year_gl_balance NUMBER;
1461 l_curr_year_gl_balance NUMBER;
1462 l_e_ne_ind fv_facts_attributes.exch_non_exch%TYPE := NULL;
1463 l_c_nc_ind fv_facts_attributes.cust_non_cust%TYPE := NULL;
1464 l_diff_amt NUMBER;
1465 l_diff_amt_tot NUMBER := 0;
1466 l_temp_amount NUMBER;
1467 l_ussgl_acct_num NUMBER;
1468 l_period_year gl_period_statuses.period_name%TYPE;
1469 l_amount NUMBER;
1470 l_end_period_num1 NUMBER;
1471 l_end_period_num2 NUMBER;
1472 l_ccid_gl_amt NUMBER;
1473 l_gl_tot_amt NUMBER;
1474 l_bal_type_amt NUMBER;
1475 BEGIN
1476 SELECT concatenated_segment_delimiter
1477 INTO l_delimiter
1478 FROM fnd_id_flex_structures
1479 WHERE application_id = 101
1480 AND id_flex_code = 'GL#'
1481 AND id_flex_num = v_chart_of_accounts_id;
1482
1483 l_log_mesg := '***** Line Number' || v_line_number || ': ' || '*****';
1484 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,l_log_mesg);
1485 FOR detail_rec IN fv_cfs_detail_cur
1486 LOOP ---- L1
1487 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '########');
1488 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Account Num: '||detail_rec.account_number);
1489 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'line id: '||v_line_id);
1490 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'line detail id: '||detail_rec.line_detail_id);
1491 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'balance type: '||detail_rec.balance_type);
1492 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Fed Non Fed: '||detail_rec.fed_non_fed);
1493 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'By Recipient: '||v_by_recipient);
1494 v_balance_type := detail_rec.balance_type;
1495 l_balance_determined := 0;
1496 l_prev_year_gl_balance := 0;
1497 l_curr_year_gl_balance := 0;
1498 l_diff_amt_tot := 0;
1499 SELECT RTRIM(REPLACE(detail_rec.concatenated_segments, '.', l_delimiter),l_delimiter)
1500 INTO l_conc_segs
1501 FROM dual;
1502 IF detail_rec.flex_further_def = 'Y' THEN --- 1
1503 l_log_mesg := 'Accounting Flexfield -1' || l_conc_segs || ' ' || 'Flexfield Needs Further Definition.' ;
1504 fnd_file.put_line(fnd_file.log, 'Warning: Accounting Flexfield - '||l_conc_segs||' needs further definition.');
1505 v_retcode := 1;
1506 ELSE --- 1
1507 IF v_by_recipient = 'Y' THEN --- 2
1508 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_line_id',v_line_id);
1509 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1510 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_balance_type',detail_rec.balance_type);
1511 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1512 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_period_num', v_end_period);
1513 l_ignore := dbms_sql.execute(v_fct1_rcpt_sel_curid);
1514 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_line_id',v_line_id);
1515 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1516 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_balance_type',detail_rec.balance_type);
1517 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1518 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_period_num', v_end_period);
1519 LOOP
1520 l_ignore := dbms_sql.fetch_rows(v_fct1_rcpt_sel_curid);
1521 EXIT
1522 WHEN l_ignore= 0;
1523 dbms_sql.column_value(v_fct1_rcpt_sel_curid, 1, v_amount);
1524 dbms_sql.column_value(v_fct1_rcpt_sel_curid, 2, v_recipient_name);
1525 v_col_1_amt := NVL(v_amount, 0);
1526 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Recipient name: '||v_recipient_name);
1527 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Recipient amount: '||v_amount);
1528 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_recipient_name',v_recipient_name);
1529 l_ignore := dbms_sql.execute_and_fetch(v_fct1_rcpt_sel2_curid);
1530 dbms_sql.column_value(v_fct1_rcpt_sel2_curid, 1, v_amount);
1531 v_col_2_amt := NVL(v_amount, 0);
1532 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Recipient group by amount: '||v_amount);
1533 l_found := FALSE;
1534 FOR i IN 1..l_recipient_cnt - 1
1535 LOOP
1536 IF l_recipient_rec(i).recipient_name = v_recipient_name THEN
1537 l_recipient_rec(i).col_1_amt := l_recipient_rec(i).col_1_amt + v_col_1_amt;
1538 l_recipient_rec(i).col_2_amt := l_recipient_rec(i).col_2_amt + v_col_2_amt;
1539 l_found := TRUE;
1540 END IF;
1541 END LOOP;
1542 IF NOT l_found THEN
1543 l_recipient_rec(l_recipient_cnt).recipient_name := v_recipient_name;
1544 l_recipient_rec(l_recipient_cnt).col_1_amt := v_col_1_amt;
1545 l_recipient_rec(l_recipient_cnt).col_2_amt := v_col_2_amt;
1546 l_recipient_cnt := l_recipient_cnt + 1;
1547 END IF;
1548 END LOOP;
1549 ELSE --- 2
1550 IF detail_rec.account_number IS NOT NULL THEN
1551 BEGIN
1552 SELECT SUBSTR(compiled_value_attributes, 5, 1)
1553 INTO l_account_type
1554 FROM fnd_flex_values
1555 WHERE flex_value = detail_rec.account_number
1556 AND flex_value_set_id = v_acct_flex_value_set_id;
1557 EXCEPTION
1558 WHEN NO_DATA_FOUND THEN
1559 BEGIN
1560 SELECT parent_flex_value
1561 INTO l_account_number
1562 FROM fnd_flex_value_hierarchies
1563 WHERE detail_rec.account_number BETWEEN child_flex_value_low AND child_flex_value_high
1564 AND flex_value_set_id = v_acct_flex_value_set_id
1565 AND ROWNUM = 1;
1566 SELECT SUBSTR(compiled_value_attributes, 5, 1)
1567 INTO l_account_type
1568 FROM fnd_flex_values
1569 WHERE flex_value = l_account_number
1570 AND flex_value_set_id = v_acct_flex_value_set_id;
1571 EXCEPTION
1572 WHEN NO_DATA_FOUND THEN
1573 NULL;
1574 END;
1575 END;
1576 END IF;
1577 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Account Type: '||l_account_type);
1578 -- ===================================================================
1579 v_py_gl_beg_bal := 0;
1580 v_py_gl_end_bal := 0;
1581 v_cy_gl_beg_bal := 0;
1582 v_cy_gl_end_bal := 0;
1583 -- Get beginning balances for current and prior years from
1584 -- facts1 attributes.
1585 dbms_sql.bind_variable(v_glbal_curid,':cv_line_id',v_line_id);
1586 dbms_sql.bind_variable(v_glbal_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1587 -- Get prior year beginning balance
1588 dbms_sql.bind_variable(v_glbal_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1589 dbms_sql.bind_variable(v_glbal_curid,':cv_balance_type','B');
1590 dbms_sql.bind_variable(v_glbal_curid,':cv_end_date', v_begin_period_1_end_date);
1591 l_ignore := dbms_sql.execute_and_fetch(v_glbal_curid);
1592 dbms_sql.column_value(v_glbal_curid, 1, v_py_gl_beg_bal);
1593 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year begin gl bal: '||v_py_gl_beg_bal);
1594 -- Get current year beginning balance
1595 dbms_sql.bind_variable(v_glbal_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1596 dbms_sql.bind_variable(v_glbal_curid,':cv_end_date', v_begin_period_end_date);
1597 dbms_sql.bind_variable(v_glbal_curid,':cv_balance_type','B');
1598 l_ignore := dbms_sql.execute_and_fetch(v_glbal_curid);
1599 dbms_sql.column_value(v_glbal_curid, 1, v_cy_gl_beg_bal);
1600 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year begin gl bal: '||v_cy_gl_beg_bal);
1601 -- ===================================================================
1602 IF v_report_type <> 'sbr' AND l_account_type NOT IN ('D','C') THEN --- 3
1603 v_py_fct1_begbal := 0;
1604 v_py_fct1_endbal := 0;
1605 v_cy_fct1_begbal := 0;
1606 v_cy_fct1_endbal := 0;
1607 -- If the balance type is Net Increase or Net Decrease
1608 -- and the natural balance is blank in the set up form
1609 -- then abort process and return error.
1610 IF ( detail_rec.balance_type IN ('I','J') AND v_natural_balance_type IS NULL ) THEN
1611 l_log_mesg := 'Line Number: '||v_line_number||' has an account
1612 with balance type Net Increase or Net Decrease but
1613 has a blank Natural Balance in the Report Definitions form.
1614 Please select Natural Balance for any line with a Balance Type
1615 of Net Increase or Net Decrease.';
1616 fnd_file.put_line(fnd_file.log, l_log_mesg);
1617 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, l_log_mesg);
1618 v_retcode := -1 ;
1619 v_errbuf := l_log_mesg;
1620 ROLLBACK;
1621 RETURN;
1622 END IF;
1623 --======= PRIOR YEAR CALCULATION ===========
1624 -- Get facts1 beginning balance for prior year
1625 IF detail_rec.balance_type IN ('B','G','I','J') THEN
1626 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year begin bal: '||v_py_gl_beg_bal);
1627 IF detail_rec.balance_type = 'B' THEN
1628 v_amount := v_py_gl_beg_bal;
1629 END IF;
1630 END IF;
1631 -- Get facts1 ending balance for prior year
1632 IF detail_rec.balance_type IN ('C', 'D', 'E', 'G', 'I', 'J') THEN
1633 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_line_id',v_line_id);
1634 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1635 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1636 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_balance_type','');
1637 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_period_num',v_end_period);
1638 l_ignore := dbms_sql.execute_and_fetch(v_fct1_sel_curid);
1639 dbms_sql.column_value(v_fct1_sel_curid, 1, v_py_fct1_endbal);
1640 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year end facts1 bal: '||v_py_fct1_endbal);
1641 v_amount := get_bal_type_amt(detail_rec.balance_type, v_natural_balance_type, NVL(v_py_gl_beg_bal,0), NVL(v_py_fct1_endbal,0));
1642 END IF;
1643 -- Set prior year amounts for reporting
1644 -- Bug 9479298
1645 IF v_report_type = 'SCNP' THEN
1646 IF detail_rec.cum_res = 'Y' THEN
1647 v_col_5_amt := v_col_5_amt + NVL(v_amount, 0);
1648 ELSIF detail_rec.cum_res IS NULL AND detail_rec.budget_col IS NULL THEN
1649 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1650 END IF;
1651 IF detail_rec.unexp_approp = 'Y' THEN
1652 v_col_6_amt := v_col_6_amt + NVL(v_amount, 0);
1653 END IF;
1654 IF detail_rec.scnp_elim = 'Y' THEN
1655 v_col_7_amt := v_col_7_amt + NVL(v_amount, 0);
1656 END IF;
1657 ELSE
1658 IF detail_rec.cum_res = 'Y' OR detail_rec.budget_col = 'Y' THEN
1659 v_col_3_amt := v_col_3_amt + NVL(v_amount, 0);
1660 ELSIF detail_rec.cum_res IS NULL AND detail_rec.budget_col IS NULL THEN
1661 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1662 END IF;
1663 IF detail_rec.unexp_approp = 'Y' OR detail_rec.nbfa_col = 'Y' THEN
1664 v_col_4_amt := v_col_4_amt + NVL(v_amount, 0);
1665 END IF;
1666
1667 END IF;
1668 --======= CURRENT YEAR CALCULATION ===========
1669 -- Get facts1 beginning balance for current year
1670 v_amount := 0;
1671 IF detail_rec.balance_type IN ('B','G','I','J') THEN
1672 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year begin bal: '||v_cy_gl_beg_bal);
1673 IF detail_rec.balance_type = 'B' THEN
1674 v_amount := v_cy_gl_beg_bal;
1675 END IF;
1676 END IF;
1677 -- Get facts1 ending balance for current year
1678 IF detail_rec.balance_type IN ('C', 'D', 'E', 'G', 'I', 'J') THEN
1679 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_line_id',v_line_id);
1680 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1681 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1682 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_balance_type','');
1683 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_period_num',v_end_period);
1684 l_ignore := dbms_sql.execute_and_fetch(v_fct1_sel_curid);
1685 dbms_sql.column_value(v_fct1_sel_curid, 1, v_cy_fct1_endbal);
1686 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year end facsts1 bal: '||v_cy_fct1_endbal);
1687 v_amount := get_bal_type_amt(detail_rec.balance_type, v_natural_balance_type, NVL(v_cy_gl_beg_bal,0), NVL(v_cy_fct1_endbal,0));
1688 END IF;
1689 -- Set current year amounts for reporting
1690 IF detail_rec.cum_res = 'Y' OR detail_rec.budget_col = 'Y' OR (detail_rec.cum_res IS NULL AND detail_rec.budget_col IS NULL) THEN
1691 v_col_1_amt := v_col_1_amt + NVL(v_amount, 0);
1692 END IF;
1693 IF detail_rec.unexp_approp = 'Y' OR detail_rec.nbfa_col = 'Y' THEN
1694 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1695 END IF;
1696
1697 -- Bug 9479298
1698 IF v_report_type = 'SCNP' THEN
1699 IF detail_rec.scnp_elim = 'Y' THEN
1700 v_col_3_amt := v_col_3_amt + NVL(v_amount, 0);
1701 END IF;
1702 END IF;
1703
1704 l_log_mesg := ' Accounting Flexfield -2' || l_conc_segs || ' ' || NVL(v_amount, 0);
1705 END IF; --- 3
1706 IF ((v_report_type = 'sbr' ) OR l_account_type IN ('D','C')) THEN
1707 v_cy_sbr_beg_bal := 0;
1708 v_cy_sbr_end_bal := 0;
1709 v_py_sbr_beg_bal := 0;
1710 v_py_sbr_end_bal := 0;
1711 dbms_sql.bind_variable(v_sbr_curid,':cv_line_id',v_line_id);
1712 dbms_sql.bind_variable(v_sbr_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1713 -- Get Current Year balances --
1714 -------------------------------
1715 IF detail_rec.balance_type = 'B' THEN
1716 -- IF balance type is begin
1717 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
1718 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period);
1719 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_end_date);
1720 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1721 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1722 dbms_sql.column_value(v_sbr_curid, 1, v_cy_sbr_beg_bal);
1723 v_amount := v_cy_sbr_beg_bal;
1724 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year begin bal: '||v_cy_sbr_beg_bal);
1725 ELSIF detail_rec.balance_type IN ('C','D','E','G','I','J') THEN
1726 -- IF balance type is ending, ending cr only or ending dr only
1727 -- Get the begin balance
1728 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
1729 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period);
1730 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_end_date);
1731 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1732 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1733 dbms_sql.column_value(v_sbr_curid, 1, v_cy_sbr_beg_bal);
1734 -- Get the end balance
1735 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','E');
1736 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_end_period);
1737 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_end_period_end_date);
1738 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1739 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1740 dbms_sql.column_value(v_sbr_curid, 1, v_cy_sbr_end_bal);
1741 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year end bal: '||v_cy_sbr_end_bal);
1742 v_amount := get_bal_type_amt(detail_rec.balance_type, v_natural_balance_type, NVL(v_cy_sbr_beg_bal,0), NVL(v_cy_sbr_end_bal,0));
1743 END IF;
1744 -- Set current year amounts for reporting
1745 IF detail_rec.cum_res = 'Y' OR detail_rec.budget_col = 'Y' OR (detail_rec.cum_res IS NULL AND detail_rec.budget_col IS NULL) THEN
1746 v_col_1_amt := v_col_1_amt + NVL(v_amount, 0);
1747 END IF;
1748 IF detail_rec.unexp_approp = 'Y' OR detail_rec.nbfa_col = 'Y' THEN
1749 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1750 END IF;
1751
1752 --Bug 9479298
1753 IF v_report_type = 'SCNP' THEN
1754 IF detail_rec.scnp_elim = 'Y' THEN
1755 v_col_3_amt := v_col_3_amt + NVL(v_amount, 0);
1756 END IF;
1757 END IF;
1758 ---- Get Prior year balances ----
1759 ---------------------------------
1760 IF detail_rec.balance_type = 'B' THEN
1761 -- If balance type is begin
1762 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
1763 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period_1);
1764 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_1_end_date);
1765 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1766 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1767 dbms_sql.column_value(v_sbr_curid, 1, v_py_sbr_beg_bal);
1768 v_amount := v_py_sbr_beg_bal;
1769 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year begin bal: '||v_py_sbr_beg_bal);
1770 -- IF balance type is ending, ending cr only or ending dr only
1771 ELSIF detail_rec.balance_type IN ('C', 'D','E','G','I','J') THEN
1772 -- Get the begin balance
1773 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
1774 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period_1);
1775 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_1_end_date);
1776 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1777 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1778 dbms_sql.column_value(v_sbr_curid, 1, v_py_sbr_beg_bal);
1779 -- Get the end balance
1780 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','E');
1781 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_period_num);
1782 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_end_period_1_end_date);
1783 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1784 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1785 dbms_sql.column_value(v_sbr_curid, 1, v_py_sbr_end_bal);
1786 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year end bal: '||v_py_sbr_end_bal);
1787 v_amount := get_bal_type_amt(detail_rec.balance_type, v_natural_balance_type, NVL(v_py_sbr_beg_bal,0), NVL(v_py_sbr_end_bal,0));
1788 END IF;
1789 l_log_mesg := ', ' || NVL(v_amount, 0);
1790
1791
1792 --Bug 9479298
1793 IF v_report_type = 'SCNP' THEN
1794 IF detail_rec.cum_res = 'Y' OR detail_rec.budget_col = 'Y' THEN
1795 v_col_5_amt := v_col_5_amt + NVL(v_amount, 0);
1796 ELSIF detail_rec.cum_res IS NULL AND detail_rec.budget_col IS NULL THEN
1797 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1798 END IF;
1799 IF detail_rec.unexp_approp = 'Y' OR detail_rec.nbfa_col = 'Y' THEN
1800 v_col_6_amt := v_col_6_amt + NVL(v_amount, 0);
1801 END IF;
1802 IF detail_rec.scnp_elim = 'Y' THEN
1803 v_col_7_amt := v_col_7_amt + NVL(v_amount, 0);
1804 END IF;
1805 ELSE
1806 IF detail_rec.cum_res = 'Y' OR detail_rec.budget_col = 'Y' THEN
1807 v_col_3_amt := v_col_3_amt + NVL(v_amount, 0);
1808 ELSIF detail_rec.cum_res IS NULL AND detail_rec.budget_col IS NULL THEN
1809 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1810 END IF;
1811 IF detail_rec.unexp_approp = 'Y' OR detail_rec.nbfa_col = 'Y' THEN
1812 v_col_4_amt := v_col_4_amt + NVL(v_amount, 0);
1813 END IF;
1814
1815 END IF;
1816 END IF; --- 10
1817 END IF; --- 2
1818 END IF; --- 1
1819 END LOOP; --- L1
1820 IF v_by_recipient = 'Y' THEN
1821 v_col_1_amt := NULL;
1822 v_col_2_amt := NULL;
1823 populate_temp_table;
1824 l_temp_str := SUBSTR(v_line_label,1,LENGTH(v_line_label) - LENGTH(ltrim(v_line_label))) || ' ';
1825 FOR i IN 1..l_recipient_cnt - 1
1826 LOOP
1827 v_line_label := l_temp_str || l_recipient_rec(i).recipient_name;
1828 v_col_1_amt := l_recipient_rec(i).col_1_amt;
1829 v_col_2_amt := l_recipient_rec(i).col_2_amt;
1830 populate_temp_table;
1831 END LOOP;
1832 ELSE
1833 SELECT REPLACE(l_log_mesg, '*****', v_col_1_amt
1834 || ', '
1835 || v_col_2_amt
1836 || ', '
1837 || v_col_3_amt
1838 || ', '
1839 || v_col_4_amt
1840 || ', '
1841 || v_col_5_amt
1842 || ', '
1843 || v_col_6_amt)
1844 INTO l_log_mesg
1845 FROM dual;
1846 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1847 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,L_LOG_MESG);
1848 END IF;
1849 populate_temp_table;
1850 END IF;
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853 v_retcode := SQLCODE ;
1854 v_errbuf := SQLERRM || ' [PROCESS_DETAIL_LINE] ' ;
1855 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
1856 RETURN;
1857 END process_detail_line;
1858
1859 -- =============================================================
1860 PROCEDURE process_total_line
1861 IS
1862 l_module_name VARCHAR2(200) := g_module_name || 'process_total_line';
1863 CURSOR fv_cfs_calc_cur
1864 IS
1865 SELECT calc_sequence_number,
1866 line_low ,
1867 line_high ,
1868 line_low_type ,
1869 line_high_type ,
1870 operator ,
1871 cum_res ,
1872 unexp_approp ,
1873 budget_col ,
1874 nbfa_col ,
1875 scnp_elim
1876 FROM fv_cfs_rep_line_calc
1877 WHERE line_id = v_line_id
1878 ORDER BY calc_sequence_number;
1879 CURSOR fv_cfs_temp_cur (p_line_id NUMBER)
1880 IS
1881 SELECT col_1_amt,
1882 col_2_amt ,
1883 col_3_amt ,
1884 col_4_amt ,
1885 col_5_amt ,
1886 col_6_amt ,
1887 col_7_amt ,
1888 col_8_amt
1889 FROM fv_cfs_rep_temp
1890 WHERE line_id = p_line_id
1891 AND sequence_id = v_sequence_id;
1892 CURSOR fv_cfs_lines_cur(p_lineid_1 NUMBER, p_lineid_2 NUMBER)
1893 IS
1894 SELECT line_id
1895 FROM fv_cfs_rep_lines
1896 WHERE sequence_number >=
1897 (SELECT sequence_number FROM fv_cfs_rep_lines WHERE line_id = p_lineid_1
1898 )
1899 AND sequence_number <=
1900 (SELECT sequence_number FROM fv_cfs_rep_lines WHERE line_id = p_lineid_2
1901 )
1902 AND report_type = v_report_type;
1903
1904 l_line_id fv_cfs_rep_lines.line_id%TYPE;
1905 temp_amt_low1 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1906 temp_amt_low2 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1907 temp_amt_low3 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1908 temp_amt_low4 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1909 temp_amt_low5 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1910 temp_amt_low6 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1911 temp_amt_low7 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1912 temp_amt_low8 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1913 temp_amt_high1 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1914 temp_amt_high2 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1915 temp_amt_high3 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1916 temp_amt_high4 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1917 temp_amt_high5 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1918 temp_amt_high6 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1919 temp_amt_high7 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1920 temp_amt_high8 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1921 TYPE amt_rec
1922 IS
1923 RECORD
1924 (
1925 calc_sequence fv_cfs_rep_line_calc.calc_sequence_number%TYPE,
1926 col_1_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1927 col_2_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1928 col_3_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1929 col_4_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1930 col_5_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1931 col_6_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1932 col_7_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1933 col_8_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1934 cum_res fv_cfs_rep_line_calc.cum_res%TYPE ,
1935 unexp_approp fv_cfs_rep_line_calc.unexp_approp%TYPE ,
1936 budget_col fv_cfs_rep_line_calc.budget_col%TYPE ,
1937 nbfa_col fv_cfs_rep_line_calc.nbfa_col%TYPE ,
1938 scnp_elim fv_cfs_rep_line_calc.scnp_elim%TYPE);
1939 TYPE amt_table
1940 IS
1941 TABLE OF amt_rec INDEX BY BINARY_INTEGER;
1942 amt_array amt_table;
1943 amt_array_cnt BINARY_INTEGER DEFAULT 1;
1944 BEGIN
1945 FOR calc_rec IN fv_cfs_calc_cur
1946 LOOP
1947 amt_array(amt_array_cnt).calc_sequence := calc_rec.calc_sequence_number;
1948 amt_array(amt_array_cnt).cum_res := calc_rec.cum_res;
1949 amt_array(amt_array_cnt).unexp_approp := calc_rec.unexp_approp;
1950 amt_array(amt_array_cnt).budget_col := calc_rec.budget_col;
1951 amt_array(amt_array_cnt).nbfa_col := calc_rec.nbfa_col;
1952 amt_array(amt_array_cnt).scnp_elim := calc_rec.scnp_elim;
1953 IF calc_rec.line_low_type = 'L' AND calc_rec.operator IN ('+','-') THEN
1954 l_line_id := calc_rec.line_low;
1955 OPEN fv_cfs_temp_cur(l_line_id);
1956 FETCH fv_cfs_temp_cur
1957 INTO temp_amt_low1,
1958 temp_amt_low2 ,
1959 temp_amt_low3 ,
1960 temp_amt_low4 ,
1961 temp_amt_low5 ,
1962 temp_amt_low6 ,
1963 temp_amt_low7 ,
1964 temp_amt_low8;
1965
1966 CLOSE fv_cfs_temp_cur;
1967 ELSIF calc_rec.line_low_type = 'C' AND calc_rec.operator IN ('+','-') THEN
1968 FOR i IN 1..amt_array_cnt
1969 LOOP
1970 IF amt_array(i).calc_sequence = calc_rec.line_low THEN
1971 temp_amt_low1 := amt_array(i).col_1_amt;
1972 temp_amt_low1 := temp_amt_low1*v_units;
1973 temp_amt_low2 := amt_array(i).col_2_amt;
1974 temp_amt_low3 := amt_array(i).col_3_amt;
1975 temp_amt_low4 := amt_array(i).col_4_amt;
1976 temp_amt_low5 := amt_array(i).col_5_amt;
1977 temp_amt_low6 := amt_array(i).col_6_amt;
1978 temp_amt_low7 := amt_array(i).col_7_amt;
1979 temp_amt_low8 := amt_array(i).col_8_amt;
1980 END IF;
1981 END LOOP;
1982 END IF;
1983 IF calc_rec.line_high_type = 'L' AND calc_rec.operator IN ('+','-') THEN
1984 l_line_id := calc_rec.line_high;
1985 OPEN fv_cfs_temp_cur(l_line_id);
1986 FETCH fv_cfs_temp_cur
1987 INTO temp_amt_high1,
1988 temp_amt_high2 ,
1989 temp_amt_high3 ,
1990 temp_amt_high4 ,
1991 temp_amt_high5 ,
1992 temp_amt_high6 ,
1993 temp_amt_high7 ,
1994 temp_amt_high8;
1995
1996 CLOSE fv_cfs_temp_cur;
1997 ELSIF calc_rec.line_high_type = 'C' AND calc_rec.operator IN ('+','-') THEN
1998 FOR i IN 1..amt_array_cnt - 1
1999 LOOP
2000 IF amt_array(i).calc_sequence = calc_rec.line_high THEN
2001 temp_amt_high1 := amt_array(i).col_1_amt;
2002 temp_amt_high2 := amt_array(i).col_2_amt;
2003 temp_amt_high3 := amt_array(i).col_3_amt;
2004 temp_amt_high4 := amt_array(i).col_4_amt;
2005 temp_amt_high5 := amt_array(i).col_5_amt;
2006 temp_amt_high6 := amt_array(i).col_6_amt;
2007 temp_amt_high7 := amt_array(i).col_7_amt;
2008 temp_amt_high8 := amt_array(i).col_8_amt;
2009 END IF;
2010 END LOOP;
2011 END IF;
2012 IF calc_rec.operator = '+' THEN
2013 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low1, 0) + NVL(temp_amt_high1, 0);
2014 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_1_amt);
2015 amt_array(amt_array_cnt).col_2_amt := NVL(temp_amt_low2, 0) + NVL(temp_amt_high2, 0);
2016 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_2_amt);
2017 amt_array(amt_array_cnt).col_3_amt := NVL(temp_amt_low3, 0) + NVL(temp_amt_high3, 0);
2018 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_3_amt);
2019 amt_array(amt_array_cnt).col_4_amt := NVL(temp_amt_low4, 0) + NVL(temp_amt_high4, 0);
2020 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_4_amt);
2021 amt_array(amt_array_cnt).col_5_amt := NVL(temp_amt_low5, 0) + NVL(temp_amt_high5, 0);
2022 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_5_amt);
2023 amt_array(amt_array_cnt).col_6_amt := NVL(temp_amt_low6, 0) + NVL(temp_amt_high6, 0);
2024 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_6_amt);
2025 amt_array(amt_array_cnt).col_7_amt := NVL(temp_amt_low7, 0) + NVL(temp_amt_high7, 0);
2026 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_7_amt);
2027 amt_array(amt_array_cnt).col_8_amt := NVL(temp_amt_low8, 0) + NVL(temp_amt_high8, 0);
2028 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_8_amt);
2029
2030 ELSIF calc_rec.operator = '-' THEN
2031 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low1, 0) - NVL(temp_amt_high1, 0);
2032 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_1_amt);
2033 amt_array(amt_array_cnt).col_2_amt := NVL(temp_amt_low2, 0) - NVL(temp_amt_high2, 0);
2034 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_2_amt);
2035 amt_array(amt_array_cnt).col_3_amt := NVL(temp_amt_low3, 0) - NVL(temp_amt_high3, 0);
2036 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_3_amt);
2037 amt_array(amt_array_cnt).col_4_amt := NVL(temp_amt_low4, 0) - NVL(temp_amt_high4, 0);
2038 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_4_amt);
2039 amt_array(amt_array_cnt).col_5_amt := NVL(temp_amt_low5, 0) - NVL(temp_amt_high5, 0);
2040 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_5_amt);
2041 amt_array(amt_array_cnt).col_6_amt := NVL(temp_amt_low6, 0) - NVL(temp_amt_high6, 0);
2042 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_6_amt);
2043 amt_array(amt_array_cnt).col_7_amt := NVL(temp_amt_low7, 0) - NVL(temp_amt_high7, 0);
2044 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_7_amt);
2045 amt_array(amt_array_cnt).col_8_amt := NVL(temp_amt_low8, 0) - NVL(temp_amt_high8, 0);
2046 fnd_file.put_line(fnd_file.log, 'TestMod'||'Begin '|| amt_array(amt_array_cnt).col_8_amt);
2047
2048 ELSE
2049 IF calc_rec.line_low_type = 'L' THEN
2050 FOR lines_rec IN fv_cfs_lines_cur(calc_rec.line_low, calc_rec.line_high)
2051 LOOP
2052 FOR fv_cfs_temp_cur_rec IN fv_cfs_temp_cur(lines_rec.line_id)
2053 LOOP
2054 amt_array(amt_array_cnt).col_1_amt := amt_array(amt_array_cnt).col_1_amt + NVL(fv_cfs_temp_cur_rec.col_1_amt, 0);
2055 amt_array(amt_array_cnt).col_2_amt := amt_array(amt_array_cnt).col_2_amt + NVL(fv_cfs_temp_cur_rec.col_2_amt, 0);
2056 amt_array(amt_array_cnt).col_3_amt := amt_array(amt_array_cnt).col_3_amt + NVL(fv_cfs_temp_cur_rec.col_3_amt, 0);
2057 amt_array(amt_array_cnt).col_4_amt := amt_array(amt_array_cnt).col_4_amt + NVL(fv_cfs_temp_cur_rec.col_4_amt, 0);
2058 amt_array(amt_array_cnt).col_5_amt := amt_array(amt_array_cnt).col_5_amt + NVL(fv_cfs_temp_cur_rec.col_5_amt, 0);
2059 amt_array(amt_array_cnt).col_6_amt := amt_array(amt_array_cnt).col_6_amt + NVL(fv_cfs_temp_cur_rec.col_6_amt, 0);
2060 amt_array(amt_array_cnt).col_7_amt := amt_array(amt_array_cnt).col_7_amt + NVL(fv_cfs_temp_cur_rec.col_7_amt, 0);
2061 amt_array(amt_array_cnt).col_8_amt := amt_array(amt_array_cnt).col_8_amt + NVL(fv_cfs_temp_cur_rec.col_8_amt, 0);
2062 END LOOP;
2063 END LOOP;
2064 ELSIF calc_rec.line_low_type = 'C' THEN
2065 FOR i IN 1..amt_array_cnt - 1
2066 LOOP
2067 IF amt_array(i).calc_sequence >= calc_rec.line_low AND amt_array(i).calc_sequence <= calc_rec.line_high THEN
2068 amt_array(amt_array_cnt).col_1_amt := amt_array(amt_array_cnt).col_1_amt + NVL(amt_array(i).col_1_amt, 0);
2069 amt_array(amt_array_cnt).col_2_amt := amt_array(amt_array_cnt).col_2_amt + NVL(amt_array(i).col_2_amt, 0);
2070 amt_array(amt_array_cnt).col_3_amt := amt_array(amt_array_cnt).col_3_amt + NVL(amt_array(i).col_3_amt, 0);
2071 amt_array(amt_array_cnt).col_4_amt := amt_array(amt_array_cnt).col_4_amt + NVL(amt_array(i).col_4_amt, 0);
2072 amt_array(amt_array_cnt).col_5_amt := amt_array(amt_array_cnt).col_5_amt + NVL(amt_array(i).col_5_amt, 0);
2073 amt_array(amt_array_cnt).col_6_amt := amt_array(amt_array_cnt).col_6_amt + NVL(amt_array(i).col_6_amt, 0);
2074 amt_array(amt_array_cnt).col_7_amt := amt_array(amt_array_cnt).col_7_amt + NVL(amt_array(i).col_7_amt, 0);
2075 amt_array(amt_array_cnt).col_8_amt := amt_array(amt_array_cnt).col_8_amt + NVL(amt_array(i).col_8_amt, 0);
2076 END IF;
2077 END LOOP;
2078 END IF;
2079 END IF;
2080 IF v_report_type = 'SCNP' THEN
2081 IF calc_rec.cum_res = 'N' THEN
2082 amt_array(amt_array_cnt).col_1_amt := 0;
2083 amt_array(amt_array_cnt).col_5_amt := 0;
2084 END IF;
2085 IF calc_rec.unexp_approp = 'N' THEN
2086 amt_array(amt_array_cnt).col_2_amt := 0;
2087 amt_array(amt_array_cnt).col_6_amt := 0;
2088 END IF;
2089 IF calc_rec.scnp_elim = 'N' OR calc_rec.scnp_elim IS NULL THEN
2090 amt_array(amt_array_cnt).col_3_amt := 0;
2091 amt_array(amt_array_cnt).col_7_amt := 0;
2092 END IF;
2093 ELSE
2094 IF calc_rec.cum_res = 'N' OR calc_rec.budget_col = 'N' THEN
2095 amt_array(amt_array_cnt).col_1_amt := 0;
2096 amt_array(amt_array_cnt).col_3_amt := 0;
2097 END IF;
2098 IF calc_rec.unexp_approp = 'N' OR calc_rec.nbfa_col = 'N' THEN
2099 amt_array(amt_array_cnt).col_2_amt := 0;
2100 amt_array(amt_array_cnt).col_4_amt := 0;
2101 END IF;
2102 END IF;
2103
2104 amt_array_cnt := amt_array_cnt + 1;
2105 END LOOP;
2106
2107 -- As SBR report does not use this procedure, removed SBR from the if clause
2108 IF v_report_type ='SCNP' THEN
2109 FOR i IN 1..amt_array_cnt - 1
2110 LOOP
2111 IF amt_array(i).cum_res = 'Y' THEN
2112 v_col_1_amt := amt_array(i).col_1_amt;
2113 v_col_5_amt := amt_array(i).col_5_amt;
2114 END IF;
2115 IF amt_array(i).unexp_approp = 'Y' THEN
2116 v_col_2_amt := amt_array(i).col_2_amt;
2117 v_col_6_amt := amt_array(i).col_6_amt;
2118 END IF;
2119 IF amt_array(i).scnp_elim = 'Y' THEN
2120 v_col_3_amt := amt_array(i).col_3_amt;
2121 v_col_7_amt := amt_array(i).col_7_amt;
2122 END IF;
2123
2124 v_col_4_amt := nvl(v_col_1_amt,0) + nvl(v_col_2_amt,0) - nvl(v_col_3_amt,0);
2125 v_col_8_amt := nvl(v_col_5_amt,0) + nvl(v_col_6_amt,0) - nvl(v_col_7_amt,0);
2126 END LOOP;
2127 ELSE
2128 v_col_1_amt := amt_array(amt_array_cnt - 1).col_1_amt;
2129 v_col_2_amt := amt_array(amt_array_cnt - 1).col_2_amt;
2130 v_col_3_amt := amt_array(amt_array_cnt - 1).col_3_amt;
2131 v_col_4_amt := amt_array(amt_array_cnt - 1).col_4_amt;
2132 v_col_5_amt := amt_array(amt_array_cnt - 1).col_5_amt;
2133 v_col_6_amt := amt_array(amt_array_cnt - 1).col_6_amt;
2134 v_col_7_amt := amt_array(amt_array_cnt - 1).col_7_amt;
2135 v_col_8_amt := amt_array(amt_array_cnt - 1).col_8_amt;
2136 END IF;
2137 EXCEPTION
2138 WHEN OTHERS THEN
2139 v_retcode := SQLCODE ;
2140 v_errbuf := SQLERRM || ' [PROCESS_TOTAL_LINE] ' ;
2141 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
2142 RETURN;
2143 END process_total_line;
2144 /* SBR developement*/
2145 /*Processing the SBR line totals*/
2146 PROCEDURE process_sbr_total_line
2147 IS
2148 l_module_name VARCHAR2(200) := g_module_name || 'process_sbr_total_line';
2149 CURSOR fv_sbr_calc_cur
2150 IS
2151 SELECT calc_sequence_number,
2152 line_low ,
2153 line_high ,
2154 line_low_type ,
2155 line_high_type ,
2156 operator
2157 FROM fv_sbr_rep_line_calc
2158 WHERE line_id = c_sbr_line_id
2159 ORDER BY calc_sequence_number;
2160 CURSOR fv_cfs_temp_cur (p_line_id NUMBER)
2161 IS
2162 SELECT col_1_amt,
2163 col_2_amt ,
2164 col_3_amt ,
2165 col_4_amt
2166 FROM fv_cfs_rep_temp
2167 WHERE line_id = p_line_id
2168 AND sequence_id = v_sequence_id;
2169 -- Bug 9191098
2170 CURSOR fv_sbr_lines_cur(p_lineid_1 NUMBER, p_lineid_2 NUMBER)
2171 IS
2172 SELECT sbr_line_id
2173 FROM fv_sbr_definitions_lines
2174 WHERE sbr_line_number >=
2175 (SELECT sbr_line_number
2176 FROM fv_sbr_definitions_lines
2177 WHERE sbr_line_id = p_lineid_1
2178 )
2179 AND sbr_line_number <=
2180 (SELECT sbr_line_number
2181 FROM fv_sbr_definitions_lines
2182 WHERE sbr_line_id = p_lineid_2
2183 );
2184
2185 l_line_id fv_sbr_definitions_lines.sbr_line_id%TYPE;
2186 temp_amt_low1 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
2187 temp_amt_low2 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
2188 temp_amt_low3 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
2189 temp_amt_low4 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
2190 temp_amt_high1 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
2191 temp_amt_high2 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
2192 temp_amt_high3 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
2193 temp_amt_high4 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
2194 TYPE amt_rec
2195 IS
2196 RECORD
2197 (
2198 calc_sequence fv_cfs_rep_line_calc.calc_sequence_number%TYPE,
2199 col_1_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
2200 col_2_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
2201 col_3_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
2202 col_4_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
2203 cum_res fv_cfs_rep_line_calc.cum_res%TYPE ,
2204 unexp_approp fv_cfs_rep_line_calc.unexp_approp%TYPE ,
2205 budget_col fv_cfs_rep_line_calc.budget_col%TYPE ,
2206 nbfa_col fv_cfs_rep_line_calc.nbfa_col%TYPE );
2207 TYPE amt_table
2208 IS
2209 TABLE OF amt_rec INDEX BY BINARY_INTEGER;
2210 amt_array amt_table;
2211 amt_array_cnt BINARY_INTEGER DEFAULT 1;
2212 --v_col_1_amt fv_sbr_definitions_cols_temp.sf133_column_amount%TYPE;
2213 BEGIN
2214 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,'Inside process_sbr_total_line');
2215 FOR calc_rec IN fv_sbr_calc_cur
2216 LOOP
2217 amt_array(amt_array_cnt).calc_sequence := calc_rec.calc_sequence_number;
2218 IF calc_rec.line_low_type = 'L' AND calc_rec.operator IN ('+','-') THEN
2219 l_line_id := calc_rec.line_low;
2220 OPEN fv_cfs_temp_cur(l_line_id);
2221 FETCH fv_cfs_temp_cur
2222 INTO temp_amt_low1,
2223 temp_amt_low2 ,
2224 temp_amt_low3 ,
2225 temp_amt_low4;
2226
2227 CLOSE fv_cfs_temp_cur;
2228 ELSIF calc_rec.line_low_type = 'C' AND calc_rec.operator IN ('+','-') THEN
2229 FOR i IN 1..amt_array_cnt
2230 LOOP
2231 IF amt_array(i).calc_sequence = calc_rec.line_low THEN
2232 temp_amt_low1 := amt_array(i).col_1_amt;
2233 temp_amt_low1 := temp_amt_low1*v_units;
2234 temp_amt_low2 := amt_array(i).col_2_amt;
2235 temp_amt_low3 := amt_array(i).col_3_amt;
2236 temp_amt_low4 := amt_array(i).col_4_amt;
2237 END IF;
2238 END LOOP;
2239 END IF;
2240 IF calc_rec.line_high_type = 'L' AND calc_rec.operator IN ('+','-') THEN
2241 l_line_id := calc_rec.line_high;
2242 OPEN fv_cfs_temp_cur(l_line_id);
2243 FETCH fv_cfs_temp_cur
2244 INTO temp_amt_high1,
2245 temp_amt_high2 ,
2246 temp_amt_high3 ,
2247 temp_amt_high4;
2248
2249 CLOSE fv_cfs_temp_cur;
2250 ELSIF calc_rec.line_high_type = 'C' AND calc_rec.operator IN ('+','-') THEN
2251 FOR i IN 1..amt_array_cnt - 1
2252 LOOP
2253 IF amt_array(i).calc_sequence = calc_rec.line_high THEN
2254 temp_amt_high1 := amt_array(i).col_1_amt;
2255 temp_amt_high2 := amt_array(i).col_2_amt;
2256 temp_amt_high3 := amt_array(i).col_3_amt;
2257 temp_amt_high4 := amt_array(i).col_4_amt;
2258 END IF;
2259 END LOOP;
2260 END IF;
2261 IF calc_rec.operator = '+' THEN
2262 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low1, 0) + NVL(temp_amt_high1, 0);
2263 fnd_file.put_line(fnd_file.log, 'TestMod col_1_amt '||'Begin '|| amt_array(amt_array_cnt).col_1_amt);
2264 amt_array(amt_array_cnt).col_2_amt := NVL(temp_amt_low2, 0) + NVL(temp_amt_high2, 0);
2265 fnd_file.put_line(fnd_file.log, 'TestMod col_2_amt '||'Begin '|| amt_array(amt_array_cnt).col_2_amt);
2266 amt_array(amt_array_cnt).col_3_amt := NVL(temp_amt_low3, 0) + NVL(temp_amt_high3, 0);
2267 fnd_file.put_line(fnd_file.log, 'TestMod col_3_amt'||'Begin '|| amt_array(amt_array_cnt).col_3_amt);
2268 amt_array(amt_array_cnt).col_4_amt := NVL(temp_amt_low4, 0) + NVL(temp_amt_high4, 0);
2269 fnd_file.put_line(fnd_file.log, 'TestMod col_4_amt'||'Begin '|| amt_array(amt_array_cnt).col_4_amt);
2270 ELSIF calc_rec.operator = '-' THEN
2271 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low1, 0) - NVL(temp_amt_high1, 0);
2272 fnd_file.put_line(fnd_file.log, 'TestMod col_1_amt'||'Begin '|| amt_array(amt_array_cnt).col_1_amt);
2273 amt_array(amt_array_cnt).col_2_amt := NVL(temp_amt_low2, 0) - NVL(temp_amt_high2, 0);
2274 fnd_file.put_line(fnd_file.log, 'TestMod col_2_amt'||'Begin '|| amt_array(amt_array_cnt).col_2_amt);
2275 amt_array(amt_array_cnt).col_3_amt := NVL(temp_amt_low3, 0) - NVL(temp_amt_high3, 0);
2276 fnd_file.put_line(fnd_file.log, 'TestMod col_3_amt'||'Begin '|| amt_array(amt_array_cnt).col_3_amt);
2277 amt_array(amt_array_cnt).col_4_amt := NVL(temp_amt_low4, 0) - NVL(temp_amt_high4, 0);
2278 fnd_file.put_line(fnd_file.log, 'TestMod col_4_amt'||'Begin '|| amt_array(amt_array_cnt).col_4_amt);
2279 ELSE
2280 IF calc_rec.line_low_type = 'L' THEN
2281 FOR lines_rec IN fv_sbr_lines_cur(calc_rec.line_low, calc_rec.line_high)
2282 LOOP
2283 FOR fv_cfs_temp_cur_rec IN fv_cfs_temp_cur(lines_rec.sbr_line_id)
2284 LOOP
2285 amt_array(amt_array_cnt).col_1_amt := amt_array(amt_array_cnt).col_1_amt + NVL(fv_cfs_temp_cur_rec.col_1_amt, 0);
2286 amt_array(amt_array_cnt).col_2_amt := amt_array(amt_array_cnt).col_2_amt + NVL(fv_cfs_temp_cur_rec.col_2_amt, 0);
2287 amt_array(amt_array_cnt).col_3_amt := amt_array(amt_array_cnt).col_3_amt + NVL(fv_cfs_temp_cur_rec.col_3_amt, 0);
2288 amt_array(amt_array_cnt).col_4_amt := amt_array(amt_array_cnt).col_4_amt + NVL(fv_cfs_temp_cur_rec.col_4_amt, 0);
2289 END LOOP;
2290 END LOOP;
2291 ELSIF calc_rec.line_low_type = 'C' THEN
2292 FOR i IN 1..amt_array_cnt - 1
2293 LOOP
2294 IF amt_array(i).calc_sequence >= calc_rec.line_low AND amt_array(i).calc_sequence <= calc_rec.line_high THEN
2295 amt_array(amt_array_cnt).col_1_amt := amt_array(amt_array_cnt).col_1_amt + NVL(amt_array(i).col_1_amt, 0);
2296 amt_array(amt_array_cnt).col_2_amt := amt_array(amt_array_cnt).col_2_amt + NVL(amt_array(i).col_2_amt, 0);
2297 amt_array(amt_array_cnt).col_3_amt := amt_array(amt_array_cnt).col_3_amt + NVL(amt_array(i).col_3_amt, 0);
2298 amt_array(amt_array_cnt).col_4_amt := amt_array(amt_array_cnt).col_4_amt + NVL(amt_array(i).col_4_amt, 0);
2299 END IF;
2300 END LOOP;
2301 END IF;
2302 END IF;
2303 amt_array_cnt := amt_array_cnt + 1;
2304 END LOOP;
2305 v_col_1_amt := amt_array(amt_array_cnt - 1).col_1_amt;
2306 v_col_2_amt := amt_array(amt_array_cnt - 1).col_2_amt;
2307 v_col_3_amt := amt_array(amt_array_cnt - 1).col_3_amt;
2308 v_col_4_amt := amt_array(amt_array_cnt - 1).col_4_amt;
2309 fnd_file.put_line(fnd_file.log, 'Before populate tables -> '||'v_col_1_amt-> '|| v_col_1_amt);
2310 fnd_file.put_line(fnd_file.log, 'Before populate tables -> '||'v_col_2_amt-> '|| v_col_2_amt);
2311 fnd_file.put_line(fnd_file.log, 'Before populate tables -> '||'v_col_3_amt-> '|| v_col_3_amt);
2312 fnd_file.put_line(fnd_file.log, 'Before populate tables -> '||'v_col_4_amt-> '|| v_col_4_amt);
2313 populate_temp_table;
2314 EXCEPTION
2315 WHEN OTHERS THEN
2316 v_retcode := SQLCODE ;
2317 v_errbuf := SQLERRM || ' [PROCESS_TOTAL_LINE] ' ;
2318 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
2319 RETURN;
2320 END process_sbr_total_line;
2321 /*SBR */
2322 -- =============================================================
2323 PROCEDURE populate_temp_table
2324 IS
2325 l_module_name VARCHAR2(200) := g_module_name || 'populate_temp_table';
2326 BEGIN
2327 -- Bug 4927632. If units are 'Dollars and Cents'
2328 -- then do not round off the amounts
2329 -- Bug 5491457. If the report type is SF and natural balance type is Net Increase or
2330 -- Net Decrease, then drop the sign from the amounts.
2331
2332 IF (v_report_type = 'SF' AND v_balance_type IN ('I', 'J')) THEN
2333 IF gbl_units <> 'Dollars and Cents' THEN
2334 IF istotal_cal=0 THEN
2335 INSERT
2336 INTO fv_cfs_rep_temp
2337 (
2338 sequence_id,
2339 line_id ,
2340 line_label ,
2341 col_1_amt ,
2342 col_2_amt ,
2343 col_3_amt ,
2344 col_4_amt,
2345 PERIOD_YEAR,
2346 PERIOD_NUM,
2347 REPORTY_TYPE,
2348 LEDGER_ID
2349 )
2350 VALUES
2351 (
2352 v_sequence_id ,
2353 v_line_id ,
2354 v_line_label ,
2355 ABS(ROUND(v_col_1_amt/v_units)),
2356 ABS(ROUND(v_col_2_amt/v_units)),
2357 ABS(ROUND(v_col_3_amt/v_units)),
2358 ABS(ROUND(v_col_4_amt/v_units)),
2359 v_period_fiscal_year,
2360 v_period_num ,
2361 v_report_type,
2362 v_sob
2363 );
2364 ELSE
2365 INSERT
2366 INTO fv_cfs_rep_temp
2367 (
2368 sequence_id,
2369 line_id ,
2370 line_label ,
2371 col_1_amt ,
2372 col_2_amt ,
2373 col_3_amt ,
2374 col_4_amt,
2375 PERIOD_YEAR,
2376 PERIOD_NUM,
2377 REPORTY_TYPE,
2378 LEDGER_ID
2379 )
2380 VALUES
2381 (
2382 v_sequence_id ,
2383 v_line_id ,
2384 v_line_label ,
2385 ABS(ROUND(v_col_1_amt)),
2386 ABS(ROUND(v_col_2_amt)),
2387 ABS(ROUND(v_col_3_amt)),
2388 ABS(ROUND(v_col_4_amt)),
2389 v_period_fiscal_year,
2390 v_period_num ,
2391 v_report_type,
2392 v_sob
2393 );
2394 END IF;
2395 ELSE
2396 INSERT
2397 INTO fv_cfs_rep_temp
2398 (
2399 sequence_id,
2400 line_id ,
2401 line_label ,
2402 col_1_amt ,
2403 col_2_amt ,
2404 col_3_amt ,
2405 col_4_amt,
2406 PERIOD_YEAR,
2407 PERIOD_NUM,
2408 REPORTY_TYPE,
2409 LEDGER_ID
2410 )
2411 VALUES
2412 (
2413 v_sequence_id ,
2414 v_line_id ,
2415 v_line_label ,
2416 ABS(v_col_1_amt),
2417 ABS(v_col_2_amt),
2418 ABS(v_col_3_amt),
2419 ABS(v_col_4_amt),
2420 v_period_fiscal_year,
2421 v_period_num ,
2422 v_report_type,
2423 v_sob
2424 );
2425 END IF;
2426 ELSE
2427 IF gbl_units <> 'Dollars and Cents' THEN
2428 IF istotal_cal=0 THEN
2429 INSERT
2430 INTO fv_cfs_rep_temp
2431 (
2432 sequence_id,
2433 line_id ,
2434 line_label ,
2435 col_1_amt ,
2436 col_2_amt ,
2437 col_3_amt ,
2438 col_4_amt ,
2439 col_5_amt ,
2440 col_6_amt ,
2441 col_7_amt ,
2442 col_8_amt,
2443 PERIOD_YEAR,
2444 PERIOD_NUM,
2445 REPORTY_TYPE,
2446 LEDGER_ID
2447 )
2448 VALUES
2449 (
2450 v_sequence_id ,
2451 v_line_id ,
2452 v_line_label ,
2453 DECODE(v_natural_balance_type, 'C', ROUND(v_col_1_amt/v_units) * -1, ROUND(v_col_1_amt/v_units)),
2454 DECODE(v_natural_balance_type, 'C', ROUND(v_col_2_amt/v_units) * -1, ROUND(v_col_2_amt/v_units)),
2455 DECODE(v_natural_balance_type, 'C', ROUND(v_col_3_amt/v_units) * -1, ROUND(v_col_3_amt/v_units)),
2456 DECODE(v_natural_balance_type, 'C', ROUND(v_col_4_amt/v_units) * -1, ROUND(v_col_4_amt/v_units)),
2457 DECODE(v_natural_balance_type, 'C', ROUND(v_col_5_amt/v_units) * -1, ROUND(v_col_5_amt/v_units)),
2458 DECODE(v_natural_balance_type, 'C', ROUND(v_col_6_amt/v_units) * -1, ROUND(v_col_6_amt/v_units)),
2459 DECODE(v_natural_balance_type, 'C', ROUND(v_col_7_amt/v_units) * -1, ROUND(v_col_7_amt/v_units)),
2460 DECODE(v_natural_balance_type, 'C', ROUND(v_col_8_amt/v_units) * -1, ROUND(v_col_8_amt/v_units)),
2461 v_period_fiscal_year,
2462 v_period_num ,
2463 v_report_type,
2464 v_sob
2465 );
2466 ELSE
2467 INSERT
2468 INTO fv_cfs_rep_temp
2469 (
2470 sequence_id,
2471 line_id ,
2472 line_label ,
2473 col_1_amt ,
2474 col_2_amt ,
2475 col_3_amt ,
2476 col_4_amt ,
2477 col_5_amt ,
2478 col_6_amt ,
2479 col_7_amt ,
2480 col_8_amt,
2481 PERIOD_YEAR,
2482 PERIOD_NUM,
2483 REPORTY_TYPE,
2484 LEDGER_ID
2485 )
2486 VALUES
2487 (
2488 v_sequence_id ,
2489 v_line_id ,
2490 v_line_label ,
2491 DECODE(v_natural_balance_type, 'C', ROUND(v_col_1_amt) * -1, ROUND(v_col_1_amt)),
2492 DECODE(v_natural_balance_type, 'C', ROUND(v_col_2_amt) * -1, ROUND(v_col_2_amt)),
2493 DECODE(v_natural_balance_type, 'C', ROUND(v_col_3_amt) * -1, ROUND(v_col_3_amt)),
2494 DECODE(v_natural_balance_type, 'C', ROUND(v_col_4_amt) * -1, ROUND(v_col_4_amt)),
2495 DECODE(v_natural_balance_type, 'C', ROUND(v_col_5_amt) * -1, ROUND(v_col_5_amt)),
2496 DECODE(v_natural_balance_type, 'C', ROUND(v_col_6_amt) * -1, ROUND(v_col_6_amt)),
2497 DECODE(v_natural_balance_type, 'C', ROUND(v_col_7_amt) * -1, ROUND(v_col_7_amt)),
2498 DECODE(v_natural_balance_type, 'C', ROUND(v_col_8_amt) * -1, ROUND(v_col_8_amt)),
2499 v_period_fiscal_year,
2500 v_period_num ,
2501 v_report_type,
2502 v_sob
2503 );
2504 END IF;
2505 fnd_file.put_line
2506 (
2507 fnd_file.log, 'TestMod'||'insert '|| ROUND(v_col_1_amt/v_units) ||' '|| ROUND(v_col_2_amt/v_units) ||' ' || ROUND(v_col_3_amt/v_units) ||' '|| ROUND(v_col_4_amt/v_units)
2508 ||' '||ROUND(v_col_5_amt/v_units) ||' '|| ROUND(v_col_6_amt/v_units) ||' ' || ROUND(v_col_7_amt/v_units) ||' '|| ROUND(v_col_8_amt/v_units)
2509 )
2510 ;
2511 ELSE
2512 INSERT
2513 INTO fv_cfs_rep_temp
2514 (
2515 sequence_id,
2516 line_id ,
2517 line_label ,
2518 col_1_amt ,
2519 col_2_amt ,
2520 col_3_amt ,
2521 col_4_amt ,
2522 col_5_amt ,
2523 col_6_amt ,
2524 col_7_amt ,
2525 col_8_amt,
2526 PERIOD_YEAR,
2527 PERIOD_NUM,
2528 REPORTY_TYPE,
2529 LEDGER_ID
2530 )
2531 VALUES
2532 (
2533 v_sequence_id ,
2534 v_line_id ,
2535 v_line_label ,
2536 DECODE(v_natural_balance_type, 'C', v_col_1_amt * -1, v_col_1_amt),
2537 DECODE(v_natural_balance_type, 'C', v_col_2_amt * -1, v_col_2_amt),
2538 DECODE(v_natural_balance_type, 'C', v_col_3_amt * -1, v_col_3_amt),
2539 DECODE(v_natural_balance_type, 'C', v_col_4_amt * -1, v_col_4_amt),
2540 DECODE(v_natural_balance_type, 'C', v_col_5_amt * -1, v_col_5_amt),
2541 DECODE(v_natural_balance_type, 'C', v_col_6_amt * -1, v_col_6_amt),
2542 DECODE(v_natural_balance_type, 'C', v_col_7_amt * -1, v_col_7_amt),
2543 DECODE(v_natural_balance_type, 'C', v_col_8_amt * -1, v_col_8_amt),
2544 v_period_fiscal_year,
2545 v_period_num ,
2546 v_report_type,
2547 v_sob
2548 );
2549
2550 fnd_file.put_line
2551 (
2552 fnd_file.log, 'TestMod'||'insert '|| v_col_1_amt ||' '|| v_col_2_amt ||' ' || v_col_3_amt||' '|| v_col_4_amt
2553 || v_col_5_amt ||' '|| v_col_6_amt ||' ' || v_col_7_amt||' '|| v_col_8_amt
2554 )
2555 ;
2556 END IF;
2557 END IF;
2558
2559 IF v_report_type = 'SCNP' THEN
2560 UPDATE fv_cfs_rep_temp SET col_4_amt = NVL(col_1_amt,0) + NVL(col_2_amt,0) - NVL(col_3_amt,0),
2561 col_8_amt = NVL(col_5_amt,0) + NVL(col_6_amt,0) - NVL(col_7_amt,0)
2562 WHERE sequence_id = v_sequence_id AND line_id = v_line_id;
2563
2564 fnd_file.put_line
2565 (
2566 fnd_file.log, 'TestMod'||'UPDATE STATEMENT TO SET CONSOLIDATED TOTAL FOR SCNP '|| v_col_1_amt ||' '|| v_col_2_amt ||' ' || v_col_3_amt||' '|| v_col_4_amt
2567 || v_col_5_amt ||' '|| v_col_6_amt ||' ' || v_col_7_amt||' '|| v_col_8_amt
2568 );
2569 END IF;
2570
2571 EXCEPTION
2572 WHEN OTHERS THEN
2573 v_retcode := -1;
2574 v_errbuf := SQLERRM;
2575 FV_UTILITY.LOG_MESG
2576 (
2577 FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf
2578 )
2579 ;
2580 RAISE;
2581 END populate_temp_table;
2582 -- =============================================================
2583 FUNCTION get_bal_type_amt
2584 (
2585 p_balance_type VARCHAR,
2586 p_natural_bal_type VARCHAR,
2587 p_beg_bal NUMBER,
2588 p_end_bal NUMBER
2589 )
2590 RETURN NUMBER
2591 IS
2592 l_module_name VARCHAR2
2593 (
2594 200
2595 )
2596 := g_module_name || 'get_bal_type_amt';
2597 l_end_minus_beg_amt NUMBER;
2598 BEGIN
2599 fv_utility.debug_mesg
2600 (
2601 fnd_log.level_statement, l_module_name, 'IN get_bal_type_amt function'
2602 )
2603 ;
2604 fv_utility.debug_mesg
2605 (
2606 fnd_log.level_statement, l_module_name, 'Natural balance: '||p_natural_bal_type
2607 )
2608 ;
2609 fv_utility.debug_mesg
2610 (
2611 fnd_log.level_statement, l_module_name, 'p_beg_bal: '||p_beg_bal
2612 )
2613 ;
2614 fv_utility.debug_mesg
2615 (
2616 fnd_log.level_statement, l_module_name, 'p_end_bal: '||p_end_bal
2617 )
2618 ;
2619 IF p_balance_type = 'E' THEN
2620 RETURN p_end_bal;
2621 END IF;
2622 -- If balance type is Ending (Cr only) or
2623 -- Ending (DR only)
2624 IF p_balance_type = 'C' THEN
2625 IF p_end_bal >= 0 THEN
2626 RETURN 0;
2627 ELSE
2628 RETURN p_end_bal;
2629 END IF;
2630 ELSIF p_balance_type = 'D' THEN
2631 IF p_end_bal <= 0 THEN
2632 RETURN 0;
2633 ELSE
2634 RETURN p_end_bal;
2635 END IF;
2636 END IF;
2637 -- If balance type is End minus Begin, Net Increase or
2638 -- Net Decrease then report amount depending on the
2639 -- natural balance type
2640 l_end_minus_beg_amt := p_end_bal - p_beg_bal;
2641 fv_utility.debug_mesg
2642 (
2643 fnd_log.level_statement, l_module_name, 'end minus begin: '||l_end_minus_beg_amt
2644 )
2645 ;
2646 IF p_balance_type = 'G' THEN
2647 RETURN l_end_minus_beg_amt;
2648 END IF;
2649 IF p_balance_type = 'I' THEN
2650 IF p_natural_bal_type = 'C' THEN
2651 IF l_end_minus_beg_amt <= 0 THEN
2652 RETURN l_end_minus_beg_amt;
2653 ELSE
2654 RETURN 0;
2655 END IF;
2656 ELSIF p_natural_bal_type = 'D' THEN
2657 IF l_end_minus_beg_amt > 0 THEN
2658 RETURN l_end_minus_beg_amt;
2659 ELSE
2660 RETURN 0;
2661 END IF;
2662 END IF;
2663 ELSIF p_balance_type = 'J' THEN
2664 IF p_natural_bal_type = 'C' THEN
2665 IF l_end_minus_beg_amt >= 0 THEN
2666 RETURN l_end_minus_beg_amt;
2667 ELSE
2668 RETURN 0;
2669 END IF;
2670 ELSIF p_natural_bal_type = 'D' THEN
2671 IF l_end_minus_beg_amt < 0 THEN
2672 RETURN l_end_minus_beg_amt;
2673 ELSE
2674 RETURN 0;
2675 END IF;
2676 END IF;
2677 END IF;
2678 EXCEPTION
2679 WHEN OTHERS THEN
2680 v_retcode := -1;
2681 v_errbuf := SQLERRM;
2682 FV_UTILITY.LOG_MESG
2683 (
2684 FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf
2685 )
2686 ;
2687 END get_bal_type_amt;
2688
2689
2690 PROCEDURE build_report_lines
2691 --
2692 AS
2693 l_module_name VARCHAR2
2694 (
2695 200
2696 )
2697 ;
2698 l_line_cnt NUMBER;
2699 --
2700 -- ----------------------------------------
2701 BEGIN
2702 l_module_name := g_module_name || 'build_report_lines';
2703 --
2704 IF
2705 (
2706 FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL
2707 )
2708 THEN
2709 FV_UTILITY.DEBUG_MESG
2710 (
2711 FND_LOG.LEVEL_STATEMENT, l_module_name,'START BUILD_REPORT_LINES'
2712 )
2713 ;
2714 END IF;
2715 --
2716 -- ----------------------------------------
2717 -- Find first period_number that is not an adjusting period
2718 -- ----------------------------------------
2719 --
2720 --
2721 SELECT MIN(period_num)
2722 INTO g_period_num
2723 FROM gl_period_statuses
2724 WHERE ledger_id = v_sob
2725 AND period_year = v_period_fiscal_year
2726 AND adjustment_period_flag = 'N'
2727 AND application_id = '101' ;
2728 -- Added on 4/28/98 by Surya Padmanabhan
2729 -- Get the Period Number For the Quarter
2730 SELECT PERIOD_NUM
2731 INTO parm_gl_period_num
2732 FROM GL_PERIOD_STATUSES
2733 WHERE LEDGER_ID = v_sob
2734 AND PERIOD_YEAR = v_period_fiscal_year
2735 AND APPLICATION_ID = '101'
2736 AND CLOSING_STATUS IN ('O','C')
2737 AND PERIOD_NAME = v_period_name;
2738 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2739 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Min Period num->'||g_period_num);
2740 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Period Number for quarter->'||parm_gl_period_num);
2741 END IF;
2742 -- ----------------------------------------------------
2743 -- Get Next sbr Treasury Symbol Line from Cursor
2744 -- ----------------------------------------------------
2745 --
2746 g_ts_value_in_process := NULL;
2747 --
2748 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2749 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Before cursor.....sbr_report_line_cursor');
2750 END IF;
2751 /*INSERTING ALL LABELS TO Report table*/
2752 INSERT
2753 INTO fv_cfs_rep_temp
2754 (
2755 SEQUENCE_ID,
2756 LINE_ID ,
2757 LINE_LABEL,
2758 PERIOD_YEAR ,
2759 PERIOD_NUM ,
2760 REPORTY_TYPE,
2761 LEDGER_ID
2762 )
2763 SELECT DISTINCT line.sbr_line_number sbr_line_number,
2764 line.sbr_line_id sbr_line_id ,
2765 line.sbr_line_label sbr_line_label,
2766 v_period_fiscal_year,
2767 v_period_num ,
2768 v_report_type,
2769 v_sob
2770 FROM fv_sbr_definitions_lines line
2771 WHERE line.set_of_books_id = v_sob
2772 AND line.sbr_line_type_code IN ('L','F')
2773 ORDER BY line.sbr_line_number;
2774
2775 FOR sbr_report_line_entry IN sbr_report_line_cursor
2776 LOOP
2777 --
2778 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2779 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'In cursor.....sbr_report_line_cursor');
2780 END IF;
2781 c_sbr_line_id := sbr_report_line_entry.sbr_line_id;
2782 c_sbr_line_number := sbr_report_line_entry.sbr_line_number;
2783 c_sbr_line_type_code := sbr_report_line_entry.sbr_line_type_code;
2784 c_sbr_natural_bal_type := sbr_report_line_entry.sbr_natural_balance_type;
2785 c_sbr_report_line_number := sbr_report_line_entry.sbr_report_line_number;
2786 c_sbr_gl_balance := sbr_report_line_entry.sbr_gl_balance;
2787 v_line_id := c_sbr_line_id;
2788 v_line_label := sbr_report_line_entry.sbr_line_label;
2789 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2790 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_line_id:'||c_sbr_line_id);
2791 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_line_number:'||c_sbr_line_number);
2792 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_line_type_code:'||c_sbr_line_type_code);
2793 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_natural_bal_type:'||c_sbr_natural_bal_type);
2794 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_report_line_number:'||c_sbr_report_line_number);
2795 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_line_label:'||v_line_label);
2796 END IF;
2797 IF g_error_code = 0 THEN
2798 IF c_sbr_line_type_code = 'D' OR c_sbr_line_type_code = 'D2' THEN
2799 g_column_number := 1;
2800 IF ( UPPER(NVL(c_sbr_gl_balance,'N')) = 'N' ) THEN
2801 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Calling build_fiscal_line_columns :'||v_period_fiscal_year);
2802 build_fiscal_line_columns(v_period_fiscal_year);
2803 END IF;
2804 /*Pulling data for current year from GL BALANCES tables directly with out FV_FACT_TEMP*/
2805 IF ( UPPER(NVL(c_sbr_gl_balance,'N')) = 'Y' ) THEN
2806 -- As get_sbr_py_bal_details pulls data for previous year
2807 -- increasing fiscal year by one
2808 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Calling get_sbr_py_bal_details :'||v_period_fiscal_year);
2809 v_year_flag:='C';
2810 get_sbr_py_bal_details(v_period_fiscal_year+1);
2811
2812 /*Populating previous fiscal year amount */
2813 v_col_3_amt:=0;
2814 v_col_4_amt:=0;
2815 BEGIN
2816 SELECT col_1_amt, col_2_amt
2817 INTO v_col_3_amt,v_col_4_amt
2818 FROM fv_cfs_rep_temp
2819 WHERE line_id=v_line_id
2820 AND ledger_id=v_sob
2821 AND reporty_type='SBR'
2822 AND period_num=v_period_num
2823 AND period_year=v_period_fiscal_year-1;
2824 EXCEPTION
2825 WHEN OTHERS THEN
2826 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_REPORT_LINES:Either fv_cfs_rep_temp table does not have data or some unknown error');
2827 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',SQLERRM);
2828 END;
2829
2830 populate_temp_table;
2831 END IF;
2832
2833 ELSIF c_sbr_line_type_code = 'T' or c_sbr_line_type_code = 'S' THEN -- to handle subtotal lines
2834 SELECT COUNT(*)
2835 INTO l_line_cnt
2836 FROM fv_sbr_rep_line_calc
2837 WHERE line_id = c_sbr_line_id;
2838 IF l_line_cnt = 0 THEN
2839 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error','Total line does not contain calculations. SEED Data not properly Loaded. Please Verify and reinvoke the Process.');
2840 RETURN;
2841 END IF;
2842 process_sbr_total_line;
2843 END IF; -- end of IF c_sbr_line_type_code = 'D' or c_sbr_line_type_code = 'D2' THEN
2844 END IF;
2845 --
2846 END LOOP;
2847 --
2848 -- ------------------------------------
2849 -- Exceptions
2850 -- ------------------------------------
2851 EXCEPTION
2852 --
2853 WHEN OTHERS THEN
2854 IF sbr_report_line_cursor%ISOPEN THEN
2855 CLOSE sbr_report_line_cursor;
2856 END IF;
2857 g_error_code := SQLCODE;
2858 g_error_message := SQLERRM;
2859 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_REPORT_LINES');
2860 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message);
2861 --
2862 END build_report_lines;
2863 -- --------------------------------------------------------
2864 -- ----------------------------------------------
2865 PROCEDURE build_fiscal_line_columns
2866 (
2867 p_fiscal_year NUMBER)
2868 --
2869 IS
2870 l_module_name VARCHAR2(200);
2871 --
2872 -- ----------------------------------------------
2873 l_ignore INTEGER;
2874 query_fetch_bal_bud VARCHAR2(8600);
2875 query_fetch_bal_nbfa VARCHAR2(8600);
2876 where_clause VARCHAR2(8600);
2877 financing_account_treas FV_FACTS_FEDERAL_ACCOUNTS.financing_account%TYPE;
2878 availability_type_treas fv_sbr_definitions_accts.availability_type%TYPE;
2879 fund_type_treas fv_treasury_symbols.fund_group_code%TYPE;
2880 group_by_clause VARCHAR2(50):= 'group by bud_col,nbfa_col';
2881
2882
2883 BEGIN
2884 l_module_name := g_module_name || 'build_fiscal_line_columns';
2885 --
2886 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2887 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'START BUILD_FISCAL_LINE_COLUMNS');
2888 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, '-- LINE('||C_sbr_LINE_NUMBER||')' || ' Tresury Symbol('||c_sbr_ts_value ||')' || ' '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh:mi:ss'));
2889 END IF;
2890 --
2891 -- ----------------------------------------
2892 -- Get Fund Accummulation
2893 -- ----------------------------------------
2894 c_total_balance := 0;
2895 c_total_balance_bud :=0;
2896 c_total_balance_nbfa :=0;
2897 -- c_sbr_amount_not_shown := 0;
2898 c_begin_balance := 0;
2899 c_ending_balance := 0;
2900 c_begin_period := g_period_num;
2901 c_end_period := parm_gl_period_num;
2902 CSum_E :=0;
2903 DSum_E :=0;
2904 CSum_B :=0;
2905 CSum_B :=0;
2906 /* Mofified SBR ER Bug 9445574*/
2907 v_col_1_amt:=0;
2908 v_col_2_amt:=0;
2909 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Before balance_type_cursor :');
2910 -- for the line find all accounts and sum
2911 FOR balance_type_rec IN balance_type_cursor
2912 LOOP
2913 c_sbr_line_acct_id := balance_type_rec.sbr_line_acct_id;
2914 c_sbr_balance_type := balance_type_rec.sbr_balance_type;
2915 c_acct_number := balance_type_rec.acct_number;
2916 c_direct_or_reimb_code := balance_type_rec.direct_or_reimb_code;
2917 c_apportionment_category_code := balance_type_rec.apportionment_category_code;
2918 c_category_b_code := balance_type_rec.category_b_code;
2919 c_prc_code := balance_type_rec. prc_code;
2920 c_advance_code := balance_type_rec.advance_code;
2921 c_availability_time := balance_type_rec.availability_time;
2922 c_bea_category_code := balance_type_rec.bea_category_code;
2923 c_borrowing_source_code := balance_type_rec.borrowing_source_code;
2924 c_transaction_partner := balance_type_rec.transaction_partner;
2925 c_year_of_budget_authority := balance_type_rec.year_of_budget_authority;
2926 c_prior_year_adjustment := balance_type_rec.prior_year_adjustment;
2927 c_authority_type := balance_type_rec.authority_type;
2928 c_tafs_status := balance_type_rec.tafs_status;
2929 c_availability_type := balance_type_rec.availability_type;
2930 c_expiration_flag := balance_type_rec.expiration_flag;
2931 c_fund_type := balance_type_rec.fund_type;
2932 c_financing_account_code := balance_type_rec.financing_account_code;
2933 c_sbr_treasury_symbol_id := balance_type_rec.sbr_treasury_symbol_id;
2934 /* Initializing for each account amount Bug 9453402*/
2935 c_total_balance := 0;
2936 c_total_balance_bud :=0;
2937 c_total_balance_nbfa :=0;
2938 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'in balance_type_cursor :c_acct_number->'||c_acct_number);
2939
2940 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Before gl_period_statuses ');
2941
2942 SELECT start_date,
2943 end_date
2944 INTO beg_date,
2945 close_date
2946 FROM gl_period_statuses
2947 WHERE period_year = p_fiscal_year
2948 AND period_num = report_period_num
2949 AND application_id = 101
2950 AND set_of_books_id = v_sob;
2951
2952
2953 FOR get_ts_id_rec IN get_ts_id_cur(c_acct_number)
2954 LOOP
2955 --Initializing for each account amount Bug 9453402
2956 c_total_balance :=0;
2957 c_total_balance_bud :=0;
2958 c_total_balance_nbfa :=0;
2959
2960 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'in get_ts_id_rec :get_ts_id_rec.treasury_symbol_id ->'||get_ts_id_rec.treasury_symbol_id);
2961 /*
2962 * If Treasury symbol is defined on SBR Definitions form for an account then amount for that
2963 * Treasury symbol is only considered on report
2964 */
2965 if (c_sbr_treasury_symbol_id is null or c_sbr_treasury_symbol_id=get_ts_id_rec.treasury_symbol_id) then
2966 c_rescission_flag := 'FALSE';
2967 IF upper(c_sbr_additional_info) = 'RESCISSION' THEN
2968 SELECT upper(resource_type)
2969 INTO c_resource_type
2970 FROM fv_treasury_symbols
2971 WHERE treasury_symbol_id = get_ts_id_rec.treasury_symbol_id
2972 AND set_of_books_id = v_sob;
2973 IF c_resource_type LIKE '%APPROPRIATION%' THEN
2974 IF ltrim(rtrim(c_sbr_report_line_number)) = '1A' THEN
2975 c_rescission_flag := 'TRUE';
2976 ELSE
2977 c_rescission_flag := 'FALSE';
2978 END IF;
2979 ELSIF c_resource_type LIKE '%BORROWING%' THEN
2980 IF ltrim(rtrim(c_sbr_report_line_number)) = '1B' THEN
2981 c_rescission_flag := 'TRUE';
2982 ELSE
2983 c_rescission_flag := 'FALSE';
2984 END IF;
2985 ELSIF c_resource_type LIKE '%CONTRACT%' THEN
2986 IF ltrim(rtrim(c_sbr_report_line_number)) = '1C' THEN
2987 c_rescission_flag := 'TRUE';
2988 ELSE
2989 c_rescission_flag := 'FALSE';
2990 END IF;
2991 END IF;
2992 ELSE
2993 c_rescission_flag := 'TRUE';
2994 END IF;
2995
2996 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'Before c_rescission_flag = TRUE ');
2997
2998 IF c_rescission_flag = 'TRUE' THEN
2999
3000 /*Avialability type and Treasury symbol */
3001 BEGIN
3002
3003 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'before fv_treasury_symbols');
3004
3005 -- Bug 13355766. Modified query to use the new values for Authority Type Code
3006 -- (TIME_FRAME)
3007 --SELECT DECODE(time_frame,'NO_YEAR','X',NULL) ,
3008 SELECT DECODE(time_frame,'X','X',NULL) ,
3009 ffg.fund_type ,
3010 expiration_Date
3011 INTO availability_type_treas,
3012 fund_type_treas ,
3013 exp_date
3014 FROM fv_treasury_symbols fts,
3015 fv_fund_groups ffg
3016 WHERE fts.treasury_symbol_id=get_ts_id_rec.treasury_symbol_id
3017 AND fts.set_of_books_id = v_sob
3018 AND fts.set_of_books_id = ffg.set_of_books_id
3019 AND ffg.fund_group_code = fts.fund_group_code;
3020
3021 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'After fv_treasury_symbols/fv_fund_groups');
3022 -- Extract expiration date of treasury symbol and determine if the TS expired
3023 -- or will it expire in the year for which the process is run
3024 IF(exp_date < close_date ) THEN
3025 whether_Exp := 'E';
3026 ELSE
3027 whether_Exp := 'U';
3028 END IF;
3029
3030 IF (exp_date IS NULL) THEN
3031 whether_Exp := 'U';
3032 whether_Exp_SameYear := 'N';
3033 END IF;
3034
3035 IF (exp_date IS NOT NULL) THEN
3036 SELECT extract ( YEAR FROM expiration_date)
3037 INTO expiring_year
3038 FROM fv_treasury_symbols
3039 WHERE treasury_symbol_id=get_ts_id_rec.treasury_symbol_id
3040 AND set_of_books_id=v_sob;
3041
3042 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'After expiring_year');
3043
3044 IF (expiring_year IS NOT NULL AND expiring_year = v_period_fiscal_year) THEN
3045 whether_Exp_SameYear := 'Y';
3046 elsif ( expiring_year > v_period_fiscal_year) THEN
3047 whether_Exp_SameYear := 'N';
3048 END IF;
3049 END IF;
3050
3051 SELECT fed.financing_account
3052 INTO financing_account_treas
3053 FROM FV_FACTS_FEDERAL_ACCOUNTS fed,
3054 fv_treasury_symbols treas
3055 WHERE fed.federal_acct_symbol_id = treas.federal_acct_symbol_id
3056 AND treas.treasury_symbol_id =get_ts_id_rec.treasury_symbol_id
3057 AND treas.set_of_books_id = v_sob;
3058
3059 EXCEPTION
3060 WHEN OTHERS THEN
3061 g_error_code := SQLCODE;
3062 g_error_message := SQLERRM;
3063 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, '.exception','For Treasury symbol '||get_ts_id_rec.treasury_symbol_id||' '||G_ERROR_MESSAGE);
3064
3065 END;
3066 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'After FV_FACTS_FEDERAL_ACCOUNTS');
3067 query_fetch_bal_bud :=NULL;
3068 query_fetch_bal_nbfa :=NULL;
3069 where_clause := ' ';
3070
3071
3072 IF (c_direct_or_reimb_code IS NOT NULL) THEN
3073 where_clause := where_clause||' '||' and trim(reimburseable_flag) = '''||c_direct_or_reimb_code|| ''' ';
3074 END IF;
3075 IF (c_apportionment_category_code IS NOT NULL) THEN
3076 where_clause := where_clause||' '||' and trim(appor_cat_code) = '''||c_apportionment_category_code|| ''' ';
3077 END IF;
3078 IF (c_category_b_code IS NOT NULL) THEN
3079 where_clause := where_clause||' '||' and trim(appor_cat_b_dtl) = '''||c_category_b_code|| ''' ';
3080 END IF;
3081 IF (c_advance_code IS NOT NULL) THEN
3082 where_clause := where_clause||' '||' and trim(advance_flag) = '''||c_advance_code|| ''' ';
3083 END IF;
3084 IF (c_availability_time IS NOT NULL) THEN
3085 where_clause := where_clause||' '||' and trim(availability_flag) = '''||c_availability_time|| ''' ';
3086 END IF;
3087 IF (c_bea_category_code IS NOT NULL) THEN
3088 where_clause := where_clause||' '||' and trim(bea_category) = '''||c_bea_category_code|| ''' ';
3089 END IF;
3090 IF (c_borrowing_source_code IS NOT NULL) THEN
3091 where_clause := where_clause||' '||' and trim(borrowing_source) = '''||c_borrowing_source_code|| ''' ';
3092 END IF;
3093 IF (c_transaction_partner IS NOT NULL) THEN
3094 where_clause := where_clause||' '||' and trim(fac.transaction_partner) = '''||c_transaction_partner|| ''' ';
3095 END IF;
3096 IF (c_year_of_budget_authority IS NOT NULL) THEN
3097 where_clause := where_clause||' '||' and trim(year_budget_auth) = '''||c_year_of_budget_authority|| ''' ';
3098 END IF;
3099 IF (c_prior_year_adjustment IS NOT NULL) THEN
3100 where_clause := where_clause||' '||' and trim(pya_flag) = '''||c_prior_year_adjustment|| ''' ';
3101 END IF;
3102 IF (c_prc_code IS NOT NULL) THEN
3103 where_clause := where_clause||' '||' and trim(PROGRAM_RPT_CAT_NUM) = '''||c_prc_code|| ''' ';
3104 END IF;
3105 IF (c_authority_type IS NOT NULL) THEN
3106 where_clause := where_clause||' '||' and trim(fac.authority_type) = '''||c_authority_type|| ''' ';
3107 END IF;
3108 -- Modified code for SBR ER bug 9466381
3109 -- Undo the changes to fix the bug 9506794
3110
3111 IF (c_expiration_flag IS NOT NULL ) THEN
3112 where_clause := where_clause||' '||'and expiration_flag = '''||whether_Exp_SameYear||''' ';
3113 END IF;
3114 IF (c_tafs_status IS NOT NULL) THEN
3115 where_clause := where_clause||' '||'and trim(tafs_status) = '''||whether_Exp|| ''' ';
3116 END IF;
3117 --IF (c_availability_type IS NOT NULL AND c_availability_type ='X' ) THEN
3118 IF (c_availability_type IS NOT NULL ) THEN
3119 where_clause := where_clause||' '||'and trim(availability_type) = '''||availability_type_treas||''' ';
3120 END IF;
3121 IF (c_fund_type IS NOT NULL ) THEN
3122 where_clause := where_clause||' '||'and trim(fund_type) = '''||fund_type_treas||''' ';
3123 END IF;
3124
3125 IF (c_financing_account_code IS NOT NULL ) THEN
3126 where_clause := where_clause||' '||'and trim(financing_account_code) = '''||financing_account_treas||''' ';
3127 END IF;
3128
3129 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'After FV_FACTS_FEDERAL_ACCOUNTS');
3130
3131
3132 IF( c_sbr_balance_type = 'B' OR c_sbr_balance_type = 'E') THEN
3133 IF(( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='D') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='C'))THEN
3134 query_fetch_bal_bud := 'select sum(nvl(amount,0)),acct.bud_col,acct.nbfa_col from fv_facts_temp fac, fv_sbr_definitions_accts acct
3135 where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
3136 AND acct.sbr_line_id = :cv_sbr_line_id
3137 AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
3138 AND acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
3139 AND fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
3140 AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
3141 where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
3142 AND begin_end = '''||c_sbr_balance_type||'''';
3143
3144
3145 elsif (( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='C') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='D') ) THEN
3146 query_fetch_bal_bud := 'select sum(nvl(amount,0)*(-1)),acct.bud_col,acct.nbfa_col from fv_facts_temp fac, fv_sbr_definitions_accts acct
3147 where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
3148 AND acct.sbr_line_id = :cv_sbr_line_id
3149 AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
3150 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
3151 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
3152 AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
3153 where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
3154 AND begin_end = '''||c_sbr_balance_type||'''';
3155
3156
3157 END IF;
3158 /*Executing the query_fetch_bud dynamic query to get the sum of amounts that has to be displayed under bud_col*/
3159 IF (query_fetch_bal_bud IS NOT NULL) THEN
3160 v_cursor_id := dbms_sql.open_cursor;
3161 query_fetch_bal_bud := query_fetch_bal_bud ||' '|| where_clause||' '||group_by_clause;
3162 -- print query
3163 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal_bud);
3164 dbms_sql.parse(v_cursor_id, query_fetch_bal_bud, dbms_sql.v7);
3165 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
3166 dbms_sql.define_column(v_cursor_id, 2, v_bud_col,1);
3167 dbms_sql.define_column(v_cursor_id, 3, v_nbfa_col,1);
3168 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_acct_id',c_sbr_line_acct_id);
3169 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sbr_treasury_symbol_id);
3170 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_id',c_sbr_line_id);
3171 --print bind variables
3172 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_balance_type:'||c_sbr_balance_type);
3173 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||c_sbr_treasury_symbol_id);
3174 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sbr_line_acct_id:'||c_sbr_line_acct_id);
3175 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_line_id:'||c_sbr_line_id);
3176 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
3177 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3178 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
3179 END IF;
3180 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
3181 dbms_sql.column_value(v_cursor_id, 2, v_bud_col);
3182 dbms_sql.column_value(v_cursor_id, 3, v_nbfa_col);
3183 dbms_sql.close_cursor(v_cursor_id);
3184
3185 v_bud_col:=nvl(v_bud_col,'Y');
3186 v_nbfa_col:=nvl(v_nbfa_col,'N');
3187 IF UPPER(v_nbfa_col) ='Y' THEN
3188 c_total_balance_nbfa:=c_total_balance_nbfa+c_total_balance;
3189 ELSIF UPPER(v_bud_col) ='Y' THEN
3190 c_total_balance_bud :=c_total_balance_bud+c_total_balance;
3191 END IF;
3192 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
3193 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_nbfa:'||c_total_balance_bud);
3194 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_bud:'||c_total_balance_bud);
3195 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_bud_col:'||v_bud_col);
3196 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_nbfa_col:'||v_nbfa_col);
3197 END IF;
3198
3199 elsif c_sbr_balance_type = 'E-B' THEN -- balance type is end-begin
3200 IF(( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='D') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='C') ) THEN
3201
3202 query_fetch_bal_bud := ' select
3203 SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) ),
3204 acct.bud_col,acct.nbfa_col
3205 from fv_facts_temp fac, fv_sbr_definitions_accts acct
3206 where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
3207 AND acct.sbr_line_id = :cv_sbr_line_id
3208 AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
3209 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
3210 AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
3211 where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
3212 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''';
3213
3214 v_cursor_id := dbms_sql.open_cursor;
3215 query_fetch_bal_bud := query_fetch_bal_bud ||' '|| where_clause||' '||group_by_clause;
3216 dbms_sql.parse(v_cursor_id, query_fetch_bal_bud, dbms_sql.v7);
3217 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
3218 dbms_sql.define_column(v_cursor_id, 2, v_bud_col,1);
3219 dbms_sql.define_column(v_cursor_id, 3, v_nbfa_col,1);
3220 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_acct_id',c_sbr_line_acct_id);
3221 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sbr_treasury_symbol_id);
3222 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_id',c_sbr_line_id);
3223 -- print query
3224 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal_bud);
3225 --print bind variables
3226 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_balance_type:'||c_sbr_balance_type);
3227 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||c_sbr_treasury_symbol_id);
3228 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sbr_line_acct_id:'||c_sbr_line_acct_id);
3229 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_line_id:'||c_sbr_line_id);
3230 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
3231 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3232 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
3233 END IF;
3234 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
3235 dbms_sql.column_value(v_cursor_id, 2, v_bud_col);
3236 dbms_sql.column_value(v_cursor_id, 3, v_nbfa_col);
3237 dbms_sql.close_cursor(v_cursor_id);
3238 v_bud_col:=nvl(v_bud_col,'Y');
3239 v_nbfa_col:=nvl(v_nbfa_col,'N');
3240 IF UPPER(v_nbfa_col) ='Y' THEN
3241 c_total_balance_nbfa:=c_total_balance_nbfa+c_total_balance;
3242 ELSIF UPPER(v_bud_col) ='Y' THEN
3243 c_total_balance_bud :=c_total_balance_bud+c_total_balance;
3244 END IF;
3245 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
3246 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_bud:'||c_total_balance_bud);
3247 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_nbfa:'||c_total_balance_nbfa);
3248 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_bud_col:'||v_bud_col);
3249 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_nbfa_col:'||v_nbfa_col);
3250
3251 elsif (( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='C') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='D'))THEN
3252 /*FOR BUD_COL*/
3253 query_fetch_bal_bud := ' select
3254 SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) )*(-1),acct.bud_col,acct.nbfa_col
3255 from fv_facts_temp fac, fv_sbr_definitions_accts acct
3256 where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
3257 AND acct.sbr_line_id = :cv_sbr_line_id
3258 AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
3259 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
3260 AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
3261 where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
3262 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''';
3263
3264 v_cursor_id := dbms_sql.open_cursor;
3265 query_fetch_bal_bud := query_fetch_bal_bud ||' '|| where_clause ||' '||group_by_clause;
3266 dbms_sql.parse(v_cursor_id, query_fetch_bal_bud, dbms_sql.v7);
3267 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
3268 dbms_sql.define_column(v_cursor_id, 2, v_bud_col,1);
3269 dbms_sql.define_column(v_cursor_id, 3, v_nbfa_col,1);
3270 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_acct_id',c_sbr_line_acct_id);
3271 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sbr_treasury_symbol_id);
3272 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_id',c_sbr_line_id);
3273 -- print query
3274 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal_bud);
3275 --print bind variables
3276 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_balance_type:'||c_sbr_balance_type);
3277 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||c_sbr_treasury_symbol_id);
3278 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sbr_line_acct_id:'||c_sbr_line_acct_id);
3279 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_line_id:'||c_sbr_line_id);
3280 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
3281 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3282 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
3283 END IF;
3284 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
3285 dbms_sql.column_value(v_cursor_id, 2, v_bud_col);
3286 dbms_sql.column_value(v_cursor_id, 3, v_nbfa_col);
3287 dbms_sql.close_cursor(v_cursor_id);
3288 v_bud_col:=nvl(v_bud_col,'Y');
3289 v_nbfa_col:=nvl(v_nbfa_col,'N');
3290 IF UPPER(v_nbfa_col) ='Y' THEN
3291 c_total_balance_nbfa:=c_total_balance_nbfa+c_total_balance;
3292 ELSIF UPPER(v_bud_col) ='Y' THEN
3293 c_total_balance_bud :=c_total_balance_bud+c_total_balance;
3294 END IF;
3295 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
3296 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_nbfa:'||c_total_balance_nbfa);
3297 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_bud:'||c_total_balance_bud);
3298 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_bud_col:'||v_bud_col);
3299 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_nbfa_col:'||v_nbfa_col);
3300
3301 elsif (( c_sbr_balance_type= 'ED') OR( c_sbr_balance_type= 'EC')) THEN -- bal type is ending debit or ending credit only
3302 /*FOR BUD COL */
3303 query_fetch_bal_bud := 'select sum(nvl(amount,0)),acct.bud_col,acct.nbfa_col
3304 from fv_facts_temp fac, fv_sbr_definitions_accts acct
3305 where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
3306 AND acct.sbr_line_id = :cv_sbr_line_id
3307 AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
3308 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
3309 AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
3310 where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
3311 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''
3312 AND begin_end = ''E''';
3313
3314 IF (query_fetch_bal_bud IS NOT NULL) THEN
3315 v_cursor_id := dbms_sql.open_cursor;
3316 query_fetch_bal_bud := query_fetch_bal_bud ||' '|| where_clause||' '||group_by_clause;
3317 dbms_sql.parse(v_cursor_id, query_fetch_bal_bud, dbms_sql.v7);
3318 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
3319 dbms_sql.define_column(v_cursor_id, 2, v_bud_col,1);
3320 dbms_sql.define_column(v_cursor_id, 3, v_nbfa_col,1);
3321 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_acct_id',c_sbr_line_acct_id);
3322 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sbr_treasury_symbol_id);
3323 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_id',c_sbr_line_id);
3324 -- print query
3325 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal_bud);
3326 --print bind variables
3327 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_balance_type:'||c_sbr_balance_type);
3328 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'parm_tsymbol_id:'||c_sbr_treasury_symbol_id);
3329 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sbr_line_acct_id:'||c_sbr_line_acct_id);
3330 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_line_id:'||c_sbr_line_id);
3331 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
3332 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3333 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
3334 END IF;
3335 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
3336 dbms_sql.column_value(v_cursor_id, 2, v_bud_col);
3337 dbms_sql.column_value(v_cursor_id, 3, v_nbfa_col);
3338 dbms_sql.close_cursor(v_cursor_id);
3339 v_bud_col:=nvl(v_bud_col,'Y');
3340 v_nbfa_col:=nvl(v_nbfa_col,'N');
3341 IF ( c_sbr_balance_type = 'ED')THEN
3342 IF (c_total_balance < 0) THEN
3343 c_total_balance := 0;
3344 END IF;
3345 elsif ( c_sbr_balance_type = 'EC')THEN
3346 IF (c_total_balance > 0) THEN
3347 c_total_balance := 0;
3348 END IF;
3349 END IF;
3350 IF(( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='D') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='C'))THEN
3351 c_total_balance := c_total_balance;
3352 END IF;
3353 IF (( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='C') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='D')) THEN
3354 c_total_balance := c_total_balance*(-1);
3355 END IF;
3356 IF UPPER(v_nbfa_col) ='Y' THEN
3357 c_total_balance_nbfa:=c_total_balance_nbfa+c_total_balance;
3358 ELSIF UPPER(v_bud_col) ='Y' THEN
3359 c_total_balance_bud :=c_total_balance_nbfa+c_total_balance;
3360 END IF;
3361 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
3362 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_bud:'||c_total_balance_bud);
3363 fV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_nbfa:'||c_total_balance_nbfa);
3364 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_bud_col:'||v_bud_col);
3365 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_nbfa_col:'||v_nbfa_col);
3366 END IF;
3367
3368 elsif( (c_sbr_balance_type= 'E-BD') OR (c_sbr_balance_type='E-BC')) THEN -- bal type is end begin debit only
3369 /*FOR BUD_COL*/
3370 query_fetch_bal_bud := ' select
3371 SUM(DECODE (begin_end,''E'',nvl(AMOUNT,0),''B'',nvl(AMOUNT,0)*(-1)) ),acct.bud_col,acct.nbfa_col
3372 from fv_facts_temp fac, fv_sbr_definitions_accts acct
3373 where (acct.sbr_treasury_symbol_id is null or acct.sbr_treasury_symbol_id = :cv_treasury_symbol_id)
3374 AND acct.sbr_line_id = :cv_sbr_line_id
3375 AND acct.sbr_line_acct_id = :cv_sbr_line_acct_id
3376 and acct_number like fac.sgl_acct_number||''%'' and fac.sgl_acct_number is not null
3377 AND fac.treasury_symbol_id = (select treasury_symbol_id from fv_treasury_symbols
3378 where set_of_books_id='||v_sob||' AND treasury_symbol_id='||get_ts_id_rec.treasury_symbol_id||')
3379 and fac.fct_int_record_category = ''REPORTED_NEW'' and fac.fct_int_record_tYPE = ''BLK_DTL''';
3380
3381 v_cursor_id := dbms_sql.open_cursor;
3382 query_fetch_bal_bud := query_fetch_bal_bud ||' '|| where_clause||' '||group_by_clause;
3383 dbms_sql.parse(v_cursor_id, query_fetch_bal_bud, dbms_sql.v7);
3384 dbms_sql.define_column(v_cursor_id, 1, c_total_balance);
3385 dbms_sql.define_column(v_cursor_id, 2, v_bud_col,1);
3386 dbms_sql.define_column(v_cursor_id, 3, v_nbfa_col,1);
3387 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_acct_id',c_sbr_line_acct_id);
3388 dbms_sql.bind_variable(v_cursor_id,':cv_treasury_symbol_id',c_sbr_treasury_symbol_id);
3389 dbms_sql.bind_variable(v_cursor_id,':cv_sbr_line_id',c_sbr_line_id);
3390 -- print query
3391 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name ,query_fetch_bal_bud);
3392 --print bind variables
3393 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_balance_type:'||c_sbr_balance_type);
3394 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_treasury_symbol_id:'||c_sbr_treasury_symbol_id);
3395 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'cv_sbr_line_acct_id:'||c_sbr_line_acct_id);
3396 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_sbr_line_id:'||c_sbr_line_id);
3397 l_ignore := dbms_sql.execute_and_fetch(v_cursor_id);
3398
3399
3400 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3401 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'l_ignore := '||l_ignore);
3402 END IF;
3403 dbms_sql.column_value(v_cursor_id, 1, c_total_balance);
3404 dbms_sql.column_value(v_cursor_id, 2, v_bud_col);
3405 dbms_sql.column_value(v_cursor_id, 3, v_nbfa_col);
3406 dbms_sql.close_cursor(v_cursor_id);
3407
3408
3409 IF (c_sbr_balance_type = 'E-BD') THEN
3410 IF (c_total_balance > 0) THEN
3411 IF(( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='D') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='C') ) THEN
3412 c_total_balance := c_total_balance;
3413 END IF;
3414 IF(( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='C') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='D') ) THEN
3415 c_total_balance := c_total_balance*-1;
3416 END IF;
3417 ELSE
3418 c_total_balance :=0; -- consider the balance only if E-B is positive
3419 END IF;
3420 END IF; -- end for if (c_sbr_balance_type= 'E-BD') then
3421 IF (c_sbr_balance_type = 'E-BC') THEN
3422 IF (c_total_balance < 0) THEN
3423 IF(( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='D') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='C') ) THEN
3424 c_total_balance :=c_total_balance;
3425 END IF;
3426 IF(( c_sbr_line_type_code = 'D' AND c_sbr_natural_bal_type ='C') OR ( c_sbr_line_type_code = 'D2' AND c_sbr_natural_bal_type ='D') ) THEN
3427 c_total_balance :=c_total_balance*-1;
3428 END IF;
3429 ELSE
3430 c_total_balance :=0; -- consider the balance only if E-B is negative
3431 END IF;
3432 v_bud_col:=nvl(v_bud_col,'Y');
3433 v_nbfa_col:=nvl(v_nbfa_col,'N');
3434
3435 IF UPPER(v_nbfa_col) ='Y' THEN
3436 c_total_balance_nbfa:=c_total_balance_nbfa + c_total_balance;
3437 ELSIF UPPER(v_bud_col) ='Y' THEN
3438 c_total_balance_bud :=c_total_balance_bud + c_total_balance;
3439 END IF;
3440 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance:'||c_total_balance);
3441 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_nbfa:'||c_total_balance_nbfa);
3442 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_bud:'||c_total_balance_bud);
3443 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_bud_col:'||v_bud_col);
3444 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_nbfa_col:'||v_nbfa_col);
3445 END IF;
3446
3447 END IF;
3448 END IF; -- end checking for balance types
3449 END IF; -- end for if rescission condition
3450 -- sum the line amount
3451 IF (c_total_balance_bud IS NULL) THEN
3452 c_total_balance_bud :=0;
3453 END IF;
3454 IF (c_total_balance_nbfa IS NULL) THEN
3455 c_total_balance_nbfa :=0;
3456 END IF;
3457 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3458 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_bud = '||c_total_balance_bud);
3459 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'c_total_balance_nbfa = '||c_total_balance_nbfa);
3460 END IF;
3461
3462 end if;
3463 -- Added for SBR ER bug 9439646
3464 v_col_1_amt := v_col_1_amt + c_total_balance_bud;
3465 v_col_2_amt := v_col_2_amt + c_total_balance_nbfa;
3466 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3467 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_col_1_amt ='||v_col_1_amt);
3468 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_col_2_amt ='||v_col_2_amt);
3469 END IF;
3470
3471
3472 END LOOP;
3473
3474 END LOOP;
3475 --
3476 -- set up correct display sign
3477 --
3478 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3479 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NATURAL BAL TYPE = '||C_sbr_NATURAL_BAL_TYPE);
3480 END IF;
3481
3482 /*
3483 Commented for SBR ER bug 9439646
3484 v_col_1_amt := c_total_balance_bud;
3485 v_col_2_amt := c_total_balance_nbfa;
3486 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3487 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_col_1_amt ='||v_col_1_amt);
3488 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'v_col_2_amt ='||v_col_2_amt);
3489 END IF;*/
3490
3491 v_col_3_amt:=0;
3492 v_col_4_amt:=0;
3493
3494 /*Populating previous fiscal year amount */
3495 BEGIN
3496 SELECT col_1_amt, col_2_amt
3497 INTO v_col_3_amt,v_col_4_amt
3498 FROM fv_cfs_rep_temp
3499 WHERE line_id=v_line_id
3500 AND ledger_id=v_sob
3501 AND reporty_type='SBR'
3502 AND period_num=v_period_num
3503 AND period_year=v_period_fiscal_year-1;
3504 EXCEPTION
3505 WHEN OTHERS THEN
3506 --g_error_code := SQLCODE;
3507 g_error_message := SQLERRM;
3508 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_FISCAL_LINE_COLUMNS:Either fv_cfs_rep_temp table does not have data or some unknown error');
3509 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',G_ERROR_MESSAGE);
3510 END;
3511
3512 populate_temp_table;
3513
3514
3515
3516 --
3517 -- ------------------------------------
3518 -- Exceptions
3519 -- ------------------------------------
3520 EXCEPTION
3521 --
3522 --
3523 WHEN OTHERS THEN
3524 g_error_code := SQLCODE;
3525 g_error_message := SQLERRM;
3526 IF balance_type_cursor%ISOPEN THEN
3527 CLOSE balance_type_cursor;
3528 ELSIF dbms_sql.is_open(v_cursor_id) THEN
3529 dbms_sql.close_cursor(v_cursor_id);
3530 END IF;
3531 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception','BUILD_FISCAL_LINE_COLUMNS:'||G_ERROR_MESSAGE);
3532 --
3533 END build_fiscal_line_columns;
3534
3535 PROCEDURE purge_csf_temp_table
3536 --
3537 IS
3538 l_module_name VARCHAR2(200) ;
3539 --
3540 BEGIN
3541 l_module_name := g_module_name || 'purge_csf_temp_table';
3542 --
3543 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3544 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'START PURGE_TEMP_TABLE');
3545 END IF;
3546 --
3547
3548 DELETE
3549 FROM fv_cfs_rep_temp
3550 WHERE (line_id) IN
3551 (SELECT sbr_line_id
3552 FROM fv_sbr_definitions_lines
3553 WHERE set_of_books_id = v_sob
3554 )
3555 and upper(reporty_type) <> 'SBR';
3556 --
3557 COMMIT;
3558 --
3559 -- ------------------------------------
3560 -- Exceptions
3561 -- ------------------------------------
3562 EXCEPTION
3563 --
3564 WHEN NO_DATA_FOUND THEN
3565 NULL;
3566 --
3567 WHEN OTHERS THEN
3568 g_error_code := SQLCODE;
3569 g_error_message := 'purge_temp_table/'||SQLERRM;
3570 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception',g_error_message);
3571 --
3572 END purge_csf_temp_table;
3573
3574
3575 /*
3576 build_sbr_dynamic_query procedure will build a dynamic
3577 to pull data from GL_BALANCES table
3578 */
3579
3580 PROCEDURE build_sbr_dynamic_query
3581 IS
3582 l_module_name VARCHAR2(200) := g_module_name || 'build_sbr_dynamic_query';
3583 CURSOR flex_columns_cursor
3584 IS
3585 SELECT UPPER(glflex.application_column_name) column_name,
3586 flex_value_set_id
3587 FROM fnd_id_flex_segments glflex
3588 WHERE glflex.application_id = 101
3589 AND glflex.id_flex_num = v_chart_of_accounts_id
3590 AND glflex.id_flex_code = 'GL#'
3591 ORDER BY glflex.application_column_name;
3592
3593 l_flex_column_name fnd_id_flex_segments.application_column_name%TYPE;
3594 l_flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
3595 l_temp1 VARCHAR2(8000) DEFAULT '';
3596 l_temp2 VARCHAR2(8000) DEFAULT '';
3597 l_table_name VARCHAR2(50);
3598 l_period_name_where VARCHAR2(500);
3599 l_stage NUMBER;
3600 l_out VARCHAR2(32000);
3601 l_column_name VARCHAR2(30);
3602 l_glbal_temp VARCHAR2(32000);
3603 BEGIN
3604 /* v_fct1_attr_select :=
3605 ' SELECT SUM(NVL(DECODE(:cv_balance_type,
3606 ''B'', ROUND(NVL(fctbal.begin_balance,0),2),
3607 ''E'', ROUND(NVL(fctbal.balance_amount,0))),0) )
3608 FROM fv_sbr_definitions_accts dets,
3609 fv_sbr_definitions_lines fsdl ,
3610 fv_sbr_ccids_gt fvcc,
3611 fv_facts1_period_attributes fctbal
3612 WHERE dets.sbr_line_id = :cv_line_id
3613 AND fsdl.sbr_line_id=dets.sbr_line_id
3614 AND dets.sbr_line_acct_id = :cv_line_detail_id
3615 AND dets.sbr_line_acct_id = fvcc.sbr_line_acct_id
3616 AND fctbal.ccid = fvcc.ccid
3617 AND fctbal.set_of_books_id = :b_sob
3618 AND fctbal.period_year = :cv_period_fiscal_year
3619 AND EXISTS
3620 (SELECT 1
3621 FROM fv_fund_parameters ffp
3622 WHERE set_of_books_id = :b_sob
3623 AND fund_category like nvl(fsdl.sbr_fund_category, ''%'')
3624 AND ffp.fund_value = fctbal.fund_value )';
3625
3626 AND ( trunc(fund_expire_date) <= :cv_end_date
3627 OR ((trunc(fund_expire_date) >= :cv_end_date or fund_expire_date is null)
3628 and (trunc(fund_cancel_date) > :cv_end_date or fund_cancel_date is null)))';
3629
3630 l_stage := 1;
3631 l_out := v_fct1_attr_select;
3632 v_glbal_curid := dbms_sql.open_cursor;
3633 dbms_sql.parse(v_glbal_curid, v_fct1_attr_select, dbms_sql.v7);
3634 dbms_sql.define_column(v_glbal_curid, 1, v_amount);
3635 dbms_sql.bind_variable(v_glbal_curid,':b_sob',v_sob);
3636
3637 v_fct1_sel := 'SELECT ROUND(SUM(NVL(ffrt.amount,0) ),2) ';
3638 v_fct1_rcpt_sel := v_fct1_sel || ' , ffrt.recipient_name ';
3639 l_temp1 := '
3640 FROM fv_sbr_definitions_accts dets,
3641 fv_sbr_ccids_gt fvcc,
3642 fv_facts1_period_balances_v ffrt
3643 WHERE dets.sbr_line_id = :cv_line_id
3644 AND dets.sbr_line_acct_id = :cv_line_detail_id
3645 AND dets.sbr_line_acct_id = fvcc.sbr_line_acct_id
3646 AND ffrt.ccid = fvcc.ccid
3647 AND ffrt.period_year = :cv_period_fiscal_year
3648 AND ffrt.set_of_books_id = :b_sob
3649 AND ffrt.period_num <= :cv_period_num
3650 AND ffrt.balance_type = NVL(:cv_balance_type, ffrt.balance_type)' ;
3651
3652
3653 v_fct1_sel := v_fct1_sel || l_temp1;
3654 v_fct1_rcpt_sel := v_fct1_rcpt_sel || l_temp1 || ' GROUP BY ffrt.recipient_name ';
3655 l_stage := 3;
3656 l_out := v_fct1_rcpt_sel;
3657
3658 v_fct1_rcpt_sel_curid := dbms_sql.open_cursor;
3659 dbms_sql.parse(v_fct1_rcpt_sel_curid, v_fct1_rcpt_sel, dbms_sql.v7);
3660 dbms_sql.define_column(v_fct1_rcpt_sel_curid, 1, v_amount);
3661 dbms_sql.define_column(v_fct1_rcpt_sel_curid, 2, v_recipient_name, 240);
3662 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':b_sob',v_sob);
3663 v_fct1_rcpt_sel2 := REPLACE(v_fct1_rcpt_sel, 'GROUP BY ffrt.recipient_name', 'AND ffrt.recipient_name = :cv_recipient_name');
3664 v_fct1_rcpt_sel2 := REPLACE(v_fct1_rcpt_sel2, ', ffrt.recipient_name', ', 1');
3665 l_stage := 4;
3666 l_out := v_fct1_rcpt_sel2;
3667 v_fct1_rcpt_sel2_curid := dbms_sql.open_cursor;
3668 dbms_sql.parse(v_fct1_rcpt_sel2_curid, v_fct1_rcpt_sel2, dbms_sql.v7);
3669 dbms_sql.define_column(v_fct1_rcpt_sel2_curid, 1, v_amount);
3670 dbms_sql.define_column(v_fct1_rcpt_sel2_curid, 2, v_recipient_name, 240);
3671 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':b_sob',v_sob);
3672 l_stage := 5;
3673 l_out := v_fct1_sel;
3674 v_fct1_sel_curid := dbms_sql.open_cursor;
3675 dbms_sql.parse(v_fct1_sel_curid, v_fct1_sel, dbms_sql.v7);
3676 dbms_sql.define_column(v_fct1_sel_curid, 1, v_amount);
3677 dbms_sql.bind_variable(v_fct1_sel_curid,':b_sob',v_sob);
3678 */
3679
3680 v_glbal_select :=
3681 ' SELECT
3682 NVL(DECODE(:cv_balance_type,
3683 ''B'', ROUND(NVL(SUM(NVL(glbal.begin_balance_dr,0) -
3684 NVL(glbal.begin_balance_cr,0)),0),2),
3685 ''E'', ROUND(NVL(SUM((NVL(glbal.begin_balance_dr,0) -
3686 NVL(glbal.begin_balance_cr,0)) +
3687 (NVL(glbal.period_net_dr,0) -
3688 NVL(glbal.period_net_cr,0))),0),2)),0)
3689 FROM fv_sbr_definitions_accts dets,
3690 fv_sbr_definitions_lines fsdl ,
3691 gl_code_combinations glc,
3692 gl_code_combinations glc1,
3693 gl_balances glbal
3694 WHERE dets.sbr_line_id = :cv_line_id
3695 AND dets.sbr_line_acct_id = :cv_line_detail_id
3696 AND dets.sbr_line_id = fsdl.sbr_line_id
3697 AND glc1.'||v_acc_seg_name|| ' like :cv_acc_num
3698 AND glc.code_combination_id=glc1.code_combination_id
3699 AND glc1.chart_of_accounts_id = :b_chart_of_accounts_id
3700 AND glc.chart_of_accounts_id = :b_chart_of_accounts_id
3701 AND glbal.code_combination_id = glc.code_combination_id
3702 AND glbal.code_combination_id = glc1.code_combination_id
3703 AND glbal.ledger_id = :b_sob
3704 AND glbal.period_year = :cv_period_fiscal_year
3705 AND glbal.period_num = :cv_period_num
3706 --AND glbal.currency_code <> ''STAT''
3707 AND glbal.currency_code = :v_currency_code
3708 AND glbal.actual_flag = ''A''
3709 AND EXISTS
3710 (SELECT 1
3711 FROM fv_fund_parameters ffp
3712 WHERE set_of_books_id = :b_sob
3713 AND fund_category like nvl(fsdl.sbr_fund_category, ''%'')
3714 AND ffp.fund_value = glc.'||v_bal_seg_name||')';
3715 /*AND ( trunc(fund_expire_date) <= :cv_end_date
3716 OR ((trunc(fund_expire_date) >= :cv_end_date or fund_expire_date is null)
3717 and (trunc(fund_cancel_date) > :cv_end_date or fund_cancel_date is null)))) ';*/
3718 l_stage := 6;
3719 l_out := v_glbal_select;
3720 v_sbr_curid := dbms_sql.open_cursor;
3721 dbms_sql.parse(v_sbr_curid, v_glbal_select, dbms_sql.v7);
3722
3723 dbms_sql.bind_variable(v_sbr_curid,':b_chart_of_accounts_id', v_chart_of_accounts_id);
3724 dbms_sql.bind_variable(v_sbr_curid,':b_sob',v_sob);
3725 dbms_sql.bind_variable(v_sbr_curid,':v_currency_code',v_currency_code);
3726
3727 EXCEPTION
3728 WHEN OTHERS THEN
3729 v_retcode := SQLCODE ;
3730 v_errbuf := SQLERRM || ' [build_sbr_dynamic_query] ' ;
3731 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
3732 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Stage it errors ' || l_stage);
3733 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_out);
3734 RETURN;
3735 END build_sbr_dynamic_query;
3736
3737 PROCEDURE get_sbr_py_bal_details(p_fiscal_year NUMBER)
3738 IS
3739 l_module_name VARCHAR2(200) := g_module_name || 'get_sbr_py_bal_details';
3740 CURSOR fv_sbr_detail_cur
3741 IS
3742 SELECT
3743 sbr_line_acct_id,
3744 sbr_balance_type,
3745 bud_col,
3746 nbfa_col,
3747 acct_number
3748 FROM fv_sbr_definitions_accts
3749 WHERE sbr_line_id = v_line_id
3750 and set_of_books_id = v_sob;
3751
3752 l_ignore INTEGER;
3753 l_prev_year_amount fv_cfs_rep_temp.col_1_amt%TYPE := 0;
3754 l_begin_balance NUMBER;
3755 l_end_balance NUMBER;
3756 l_period_name_1 gl_period_statuses.period_name%TYPE;
3757 l_period_name_2 gl_period_statuses.period_name%TYPE;
3758 l_begin_period_name gl_period_statuses.period_name%TYPE;
3759 l_begin_period_name_1 gl_period_statuses.period_name%TYPE;
3760 l_begin_period_name_2 gl_period_statuses.period_name%TYPE;
3761 l_period_fiscal_year NUMBER;
3762 l_begin_period NUMBER;
3763 l_end_period NUMBER := v_period_num;
3764 l_end_period_1 NUMBER ;
3765 l_end_period_name_1 gl_period_statuses.period_name%TYPE;
3766 l_begin_period_1 NUMBER;
3767 l_begin_period_end_date DATE;
3768 l_end_period_end_date DATE := v_end_date;
3769 l_begin_period_1_end_date DATE;
3770 l_end_period_1_end_date DATE;
3771 l_log_mesg VARCHAR2(32000) := '';
3772 l_conc_segs VARCHAR2(32000);
3773 l_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
3774 l_account_type VARCHAR2(1) := '';
3775 l_account_number gl_code_combinations.segment1%TYPE;
3776 l_balance_determined NUMBER;
3777 l_prev_year_gl_balance NUMBER;
3778 l_curr_year_gl_balance NUMBER;
3779 l_e_ne_ind fv_facts_attributes.exch_non_exch%TYPE := NULL;
3780 l_c_nc_ind fv_facts_attributes.cust_non_cust%TYPE := NULL;
3781 l_diff_amt NUMBER;
3782 l_diff_amt_tot NUMBER := 0;
3783 l_temp_amount NUMBER;
3784 l_ussgl_acct_num NUMBER;
3785 l_period_year gl_period_statuses.period_name%TYPE;
3786 l_amount NUMBER;
3787 l_end_period_num1 NUMBER;
3788 l_end_period_num2 NUMBER;
3789 l_ccid_gl_amt NUMBER;
3790 l_gl_tot_amt NUMBER;
3791 l_bal_type_amt NUMBER;
3792 BEGIN
3793 SELECT concatenated_segment_delimiter
3794 INTO l_delimiter
3795 FROM fnd_id_flex_structures
3796 WHERE application_id = 101
3797 AND id_flex_code = 'GL#'
3798 AND id_flex_num = v_chart_of_accounts_id;
3799
3800 IF ( UPPER(v_year_flag) = 'P') THEN
3801 v_col_3_amt:=0;
3802 v_col_4_amt:=0;
3803 END IF;
3804
3805 IF ( UPPER(v_year_flag) = 'C') THEN
3806 v_col_1_amt:=0;
3807 v_col_2_amt:=0;
3808 END IF;
3809
3810 l_log_mesg := '***** Line Number' || v_line_number || ': ' || '*****';
3811 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,l_log_mesg);
3812 FOR detail_rec IN fv_sbr_detail_cur
3813 LOOP ---- L1
3814 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '########');
3815 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Account Num: '||detail_rec.acct_number);
3816 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'line id: '||v_line_id);
3817 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'line detail id: '||detail_rec.sbr_line_acct_id);
3818 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'balance type: '||detail_rec.sbr_balance_type);
3819
3820 v_balance_type := detail_rec.sbr_balance_type;
3821 l_balance_determined := 0;
3822 l_prev_year_gl_balance := 0;
3823 l_curr_year_gl_balance := 0;
3824 l_diff_amt_tot := 0;
3825 --- 1
3826 IF detail_rec.acct_number IS NOT NULL THEN
3827 BEGIN
3828 SELECT SUBSTR(compiled_value_attributes, 5, 1)
3829 INTO l_account_type
3830 FROM fnd_flex_values
3831 WHERE flex_value = detail_rec.acct_number
3832 AND flex_value_set_id = v_acct_flex_value_set_id;
3833 EXCEPTION
3834 WHEN NO_DATA_FOUND THEN
3835 BEGIN
3836 SELECT parent_flex_value
3837 INTO l_account_number
3838 FROM fnd_flex_value_hierarchies
3839 WHERE detail_rec.acct_number BETWEEN child_flex_value_low AND child_flex_value_high
3840 AND flex_value_set_id = v_acct_flex_value_set_id
3841 AND ROWNUM = 1;
3842 SELECT SUBSTR(compiled_value_attributes, 5, 1)
3843 INTO l_account_type
3844 FROM fnd_flex_values
3845 WHERE flex_value = l_account_number
3846 AND flex_value_set_id = v_acct_flex_value_set_id;
3847 EXCEPTION
3848 WHEN NO_DATA_FOUND THEN
3849 NULL;
3850 END;
3851 END;
3852 END IF;
3853 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Account Type: '||l_account_type);
3854 -- ===================================================================
3855 v_py_gl_beg_bal := 0;
3856 v_py_gl_end_bal := 0;
3857 v_cy_gl_beg_bal := 0;
3858 v_cy_gl_end_bal := 0;
3859 /* -- Get beginning balances for current and prior years from
3860 -- facts1 attributes.
3861 dbms_sql.bind_variable(v_glbal_curid,':cv_line_id',v_line_id);
3862 dbms_sql.bind_variable(v_glbal_curid,':cv_line_detail_id',detail_rec.sbr_line_acct_id);
3863 -- Get prior year beginning balance
3864 dbms_sql.bind_variable(v_glbal_curid,':cv_period_fiscal_year',p_fiscal_year-1);
3865 dbms_sql.bind_variable(v_glbal_curid,':cv_balance_type','B');
3866 --dbms_sql.bind_variable(v_glbal_curid,':cv_end_date', v_begin_period_1_end_date);
3867 l_ignore := dbms_sql.execute_and_fetch(v_glbal_curid);
3868 dbms_sql.column_value(v_glbal_curid, 1, v_py_gl_beg_bal);
3869 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year begin gl bal: '||v_py_gl_beg_bal);
3870 */
3871 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_report_type: '||v_report_type);
3872 IF UPPER(v_report_type) = 'SBR' THEN
3873 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'IN SIDE IF : ');
3874 v_cy_sbr_beg_bal := 0;
3875 v_cy_sbr_end_bal := 0;
3876 v_py_sbr_beg_bal := 0;
3877 v_py_sbr_end_bal := 0;
3878 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'bEFORE IF detail_rec.sbr_balance_type:detail_rec.acct_number-> '||detail_rec.sbr_balance_type||'::'||detail_rec.acct_number);
3879 dbms_sql.bind_variable(v_sbr_curid,':cv_line_id',v_line_id);
3880 dbms_sql.bind_variable(v_sbr_curid,':cv_line_detail_id',detail_rec.sbr_line_acct_id);
3881 --dbms_sql.bind_variable(v_sbr_curid,':cv_account_number',detail_rec.acct_number);
3882 dbms_sql.bind_variable(v_sbr_curid,':cv_acc_num',detail_rec.acct_number||'%');
3883
3884
3885 ---- Get Prior year balances ----
3886 ---------------------------------
3887
3888 IF detail_rec.sbr_balance_type = 'B' THEN
3889 -- If balance type is begin
3890 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'inside IF detail_rec.sbr_balance_type: '||detail_rec.sbr_balance_type);
3891 dbms_sql.define_column(v_sbr_curid, 1, v_py_sbr_beg_bal);
3892 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
3893 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period_1);
3894
3895
3896 ---dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_1_end_date);
3897 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',p_fiscal_year-1);
3898 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_sbr_curid : ');
3899 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
3900
3901 dbms_sql.column_value(v_sbr_curid, 1, v_py_sbr_beg_bal);
3902 v_amount := v_py_sbr_beg_bal;
3903 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year begin bal: '||v_amount);
3904 -- IF balance type is ending, ending cr only or ending dr only
3905 ELSIF detail_rec.sbr_balance_type IN ('C', 'D','E','G','I','J') THEN
3906 -- Get the begin balance
3907 dbms_sql.define_column(v_sbr_curid, 1, v_py_sbr_beg_bal);
3908 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
3909 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period_1);
3910 --dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_1_end_date);
3911 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',p_fiscal_year-1);
3912 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
3913 dbms_sql.column_value(v_sbr_curid, 1, v_py_sbr_beg_bal);
3914
3915 -- Get the end balance
3916 dbms_sql.define_column(v_sbr_curid, 1, v_py_sbr_beg_bal);
3917 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','E');
3918 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_period_num);
3919 --dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_end_period_1_end_date);
3920 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',p_fiscal_year-1);
3921 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
3922 dbms_sql.column_value(v_sbr_curid, 1, v_py_sbr_end_bal);
3923
3924 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year end bal: '||v_py_sbr_end_bal);
3925 v_amount := get_bal_type_amt(detail_rec.sbr_balance_type, v_natural_balance_type, NVL(v_py_sbr_beg_bal,0), NVL(v_py_sbr_end_bal,0));
3926 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_amount: '||v_amount);
3927 END IF;
3928 l_log_mesg := ', ' || NVL(v_amount, 0);
3929 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'detail_rec.bud_col: '||detail_rec.bud_col);
3930 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'detail_rec.nbfa_col: '||detail_rec.nbfa_col);
3931
3932 -- Added upper function for bug 9453175
3933 /*Summing previous years amount*/
3934 IF ( UPPER(v_year_flag) = 'P') THEN
3935 IF UPPER(nvl(detail_rec.bud_col,'Y')) = 'Y' THEN
3936 v_col_3_amt := v_col_3_amt + NVL(v_amount, 0);
3937 ELSIF UPPER(nvl(detail_rec.nbfa_col,'N')) = 'Y' THEN
3938 v_col_4_amt := v_col_4_amt + NVL(v_amount, 0);
3939 END IF;
3940 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_col_3_amt: '||v_col_3_amt);
3941 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_col_4_amt: '||v_col_4_amt);
3942 END IF;
3943 /*Summing current years amount*/
3944 IF ( UPPER(v_year_flag) = 'C') THEN
3945 IF UPPER(nvl(detail_rec.bud_col,'Y')) = 'Y' THEN
3946 v_col_1_amt := v_col_1_amt + NVL(v_amount, 0);
3947 ELSIF UPPER(nvl(detail_rec.nbfa_col,'N')) = 'Y' THEN
3948 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
3949 END IF;
3950 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_col_1_amt: '||v_col_1_amt);
3951 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_col_2_amt: '||v_col_2_amt);
3952 END IF;
3953
3954 END IF; --- 10
3955
3956 END LOOP; --- L1
3957 EXCEPTION
3958 WHEN OTHERS THEN
3959 v_retcode := SQLCODE ;
3960 v_errbuf := SQLERRM || ' [get_sbr_py_bal_details] ' ;
3961 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
3962 RETURN;
3963 END get_sbr_py_bal_details;
3964
3965 PROCEDURE purge_facts_transactions
3966 IS
3967 l_module_name VARCHAR2(200);
3968 BEGIN
3969 l_module_name := g_module_name || 'purge_facts_transactions';
3970
3971 DELETE FROM fv_facts_temp
3972 WHERE treasury_symbol_id = v_purge_ts_id;
3973
3974 DELETE FROM fv_facts_edit_check_status
3975 WHERE treasury_symbol_id = v_purge_ts_id;
3976
3977 DELETE FROM fv_cfs_rep_temp
3978 WHERE ledger_id=v_sob
3979 AND reporty_type='SBR'
3980 AND period_num=v_period_num
3981 AND period_year=v_period_fiscal_year;
3982
3983
3984 COMMIT ;
3985
3986 EXCEPTION
3987 -- Exception Processing
3988 When NO_DATA_FOUND Then
3989 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'NO DATA FOUND IN FV_FACTS_TEMP / FV_FACTS_EDIT_CHECK_STATUS tables ');
3990 When Others Then
3991 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.final_exception',SQLERRM);
3992 END purge_facts_transactions ;
3993
3994
3995
3996 -- =============================================================
3997 BEGIN
3998 g_module_name := 'fv.plsql.FV_CFS_PKG.';
3999 END fv_cfs_pkg;
4000 -- =============================================================