[Home] [Help]
PACKAGE BODY: APPS.FV_CFS_PKG
Source
1 PACKAGE BODY fv_cfs_pkg AS
2 /* $Header: FVXCFSPB.pls 120.82 2007/11/27 10:49:30 nisgupta ship $ */
3
4 ------------Global Variables---------------
5 g_module_name VARCHAR2(100);
6
7 v_sob gl_ledgers_public_v.ledger_id%TYPE;
8 v_period_name gl_period_statuses.period_name%TYPE;
9 v_units NUMBER;
10 v_report_type VARCHAR2(30);
11 v_table_ind VARCHAR2(1);
12 v_end_date DATE;
13 v_retcode NUMBER DEFAULT 0;
14 v_errbuf VARCHAR2(2000);
15 v_sequence_id NUMBER;
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_sequence_number fv_cfs_rep_lines.sequence_number%TYPE;
39 v_line_number fv_cfs_rep_lines.line_number%TYPE;
40 v_period_fiscal_year gl_period_statuses.period_year%TYPE;
41 v_period_num gl_period_statuses.period_num%TYPE;
42 v_select1 VARCHAR2(32000);
43 v_select2 VARCHAR2(32000);
44 v_select3 VARCHAR2(32000);
45 v_select4 VARCHAR2(32000);
46 v_select5 VARCHAR2(32000);
47 v_cursor_id1 INTEGER;
48 v_cursor_id2 INTEGER;
49 v_cursor_id3 INTEGER;
50 v_cursor_id4 INTEGER;
51 v_cursor_id5 INTEGER;
52 v_cursor_id6 INTEGER;
53 v_cursor_id7 INTEGER;
54 v_cursor_id8 INTEGER;
55
56 gbl_units VARCHAR2(25);
57 v_glbal_select VARCHAR2(32000);
58 v_glbal_grpby_sel VARCHAR2(32000);
59 v_glbal_curid INTEGER;
60 v_glbal_grpby_curid INTEGER;
61
62 v_begin_period NUMBER;
63 v_begin_period_name gl_period_statuses.period_name%TYPE;
64 v_begin_period_end_date DATE;
65
66 v_end_period NUMBER;
67 v_end_period_end_date DATE;
68 v_begin_period_1 NUMBER;
69 v_begin_period_name_1 gl_period_statuses.period_name%TYPE;
70 v_begin_period_1_end_date DATE;
71 v_end_period_1_end_date DATE;
72
73 v_cy_gl_beg_bal NUMBER;
74 v_cy_gl_end_bal NUMBER;
75 v_py_gl_beg_bal NUMBER;
76 v_py_gl_end_bal NUMBER;
77
78 v_fct1_attr_select VARCHAR2(32000);
79
80 v_fct1_sel VARCHAR2(32000);
81 v_fct1_sel_curid INTEGER;
82 v_fct1_rcpt_sel VARCHAR2(32000);
83 v_fct1_rcpt_sel_curid INTEGER;
84
85 v_fct1_rcpt_sel2 VARCHAR2(32000);
86 v_fct1_rcpt_sel2_curid INTEGER;
87
88 v_sbr_curid INTEGER;
89
90 v_cy_fct1_begbal NUMBER;
91 v_cy_fct1_endbal NUMBER;
92 v_py_fct1_begbal NUMBER;
93 v_py_fct1_endbal NUMBER;
94
95 v_cy_begbal_diff NUMBER;
96 v_py_begbal_diff NUMBER;
97
98 v_cy_sbr_beg_bal NUMBER;
99 v_cy_sbr_end_bal NUMBER;
100 v_py_sbr_beg_bal NUMBER;
101 v_py_sbr_end_bal NUMBER;
102
103
104 v_balance_type fv_cfs_rep_line_dtl.balance_type%TYPE;
105
106 -- =============================================================
107 PROCEDURE get_qualifier_segments;
108 PROCEDURE build_dynamic_query;
109 PROCEDURE get_one_time_values;
110 PROCEDURE process_report_line;
111 PROCEDURE process_detail_line;
112 PROCEDURE process_total_line;
113 PROCEDURE populate_temp_table;
114 PROCEDURE populate_ccid;
115 FUNCTION get_bal_type_amt(p_balance_type VARCHAR,
116 p_natural_bal_type VARCHAR,
117 p_beg_bal NUMBER, p_end_bal NUMBER)
118 RETURN NUMBER;
119 --p_bal_type_amt OUT NUMBER);
120 -- =============================================================
121 PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
122 retcode OUT NOCOPY NUMBER,
123 p_set_of_books_id IN NUMBER,
124 p_period_name IN VARCHAR2,
125 p_units IN VARCHAR2,
126 p_report_type IN VARCHAR2,
127 p_table_indicator IN VARCHAR2)
128 IS
129
130 l_module_name VARCHAR2(200) := g_module_name || 'MAIN.';
131
132 l_request_id NUMBER;
133 l_facts_edit_cnt NUMBER;
134
135 BEGIN
136
137 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Sob ID: '||p_set_of_books_id);
138 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Period: '||p_period_name);
139 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Units: '||p_units);
140 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Report type: '||p_report_type);
141 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Table Ind: '||p_table_indicator);
142
143 v_sob := p_set_of_books_id;
144 v_period_name := p_period_name;
145 v_report_type := p_report_type;
146
147 gbl_units := p_units;
148
149 IF p_units = 'Dollars' THEN
150 v_units := 1;
151 ELSIF p_units = 'Thousands' THEN
152 v_units := 1000;
153 ELSIF p_units = 'Millions' THEN
154 v_units := 1000000;
155 END IF;
156
157 SELECT chart_of_accounts_id, currency_code
158 INTO v_chart_of_accounts_id, v_currency_code
159 FROM gl_ledgers_public_v
160 WHERE ledger_id = v_sob ;
161
162 get_one_time_values;
163
164 IF v_retcode <> 0 THEN
165 retcode := v_retcode;
166 errbuf := v_errbuf;
167 RETURN;
168 END IF;
169
170 SELECT trunc(end_date), period_num, period_year
171 INTO v_end_date, v_period_num, v_period_fiscal_year
172 FROM gl_period_statuses
173 WHERE ledger_id = v_sob
174 AND application_id = '101'
175 AND period_name = v_period_name;
176
177 SELECT fv_cfs_rep_temp_s.NEXTVAL
178 INTO v_sequence_id FROM DUAL;
179
180 get_qualifier_segments;
181
182 populate_ccid;
183 build_dynamic_query;
184
185 IF v_retcode IN (0, 1) THEN
186 process_report_line;
187 END IF;
188
189 IF v_retcode IN (0, 1) THEN
190 l_request_id := FND_REQUEST.SUBMIT_REQUEST ('FV', 'FVCFSCMR', '', '', FALSE,
191 v_sequence_id, v_sob, v_period_name, p_units, v_report_type, v_end_date );
192
193 IF l_request_id = 0 THEN
194 errbuf := 'Error submitting Consolidated Financial Statements Report';
195 retcode := -1;
196 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',errbuf);
197 return;
198 ELSE
199 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
200 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
201 'CONCURRENT REQUEST ID FOR CONSOLIDATED FINANCIAL STATEMENTS REPORT - '
202 || l_request_id);
203 END IF;
204 END IF;
205 END IF;
206
207 dbms_sql.close_cursor(v_glbal_curid);
208 dbms_sql.close_cursor(v_fct1_sel_curid);
209 dbms_sql.close_cursor(v_fct1_rcpt_sel_curid);
210 dbms_sql.close_cursor(v_fct1_rcpt_sel2_curid);
211
212 retcode := v_retcode;
213 errbuf := v_errbuf;
214
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 retcode := SQLCODE ;
219 errbuf := SQLERRM || ' [MAIN] ' ;
220 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
221 ROLLBACK;
222 COMMIT ;
223 END main;
224 -- =============================================================
225 Procedure get_qualifier_segments
226 IS
227 l_module_name VARCHAR2(200) := g_module_name || 'get_qualifier_segments';
228 num_boolean BOOLEAN ;
229 apps_id Number DEFAULT 101 ;
230 flex_code Varchar2(25) DEFAULT 'GL#' ;
231 seg_number Number ;
232 seg_app_name Varchar2(40) ;
233 seg_prompt Varchar2(25) ;
234 seg_value_set_name Varchar2(40) ;
235 invalid_segment EXCEPTION ;
236
237 BEGIN
238
239 -- Get Accounting Segment
240 num_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM
241 (apps_id,
242 flex_code,
243 v_chart_of_accounts_id,
244 'GL_ACCOUNT',
245 seg_number);
246
247 IF(num_boolean) THEN
248 num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
249 (apps_id,
250 flex_code,
251 v_chart_of_accounts_id,
252 seg_number,
253 v_acc_seg_name,
254 seg_app_name,
255 seg_prompt,
256 seg_value_set_name);
257
258 SELECT flex_value_set_id
259 INTO v_acct_flex_value_set_id
260 FROM fnd_id_flex_segments_vl
261 WHERE application_id = 101
262 AND id_flex_code = 'GL#'
263 AND id_flex_num = v_chart_of_accounts_id
264 AND enabled_flag = 'Y'
265 AND segment_num = seg_number;
266 ELSE
267 RAISE invalid_segment;
268 END IF;
269
270 -- Get Balancing Segment
271 num_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM
272 (apps_id,
273 flex_code,
274 v_chart_of_accounts_id,
275 'GL_BALANCING',
276 seg_number);
277
278 IF(num_boolean) THEN
279 num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
280 (apps_id,
281 flex_code,
282 v_chart_of_accounts_id,
283 seg_number,
284 v_bal_seg_name,
285 seg_app_name,
286 seg_prompt,
287 seg_value_set_name);
288
289 SELECT flex_value_set_id
290 INTO v_bal_flex_value_set_id
291 FROM fnd_id_flex_segments_vl
292 WHERE application_id = 101
293 AND id_flex_code = 'GL#'
294 AND id_flex_num = v_chart_of_accounts_id
295 AND enabled_flag = 'Y'
296 AND segment_num = seg_number;
297 ELSE
298 RAISE invalid_segment;
299 END IF;
300
301 v_acc_seg_name := UPPER(v_acc_seg_name) ;
302 v_bal_seg_name := UPPER(v_bal_seg_name) ;
303
304 EXCEPTION
305 WHEN invalid_segment THEN
306 v_retcode := -1;
307 v_errbuf := 'Error getting Balancing and Accounting segments.';
308 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.invalid_segment',v_errbuf);
309 ROLLBACK;
310 RETURN;
311 WHEN OTHERS THEN
312 v_retcode := sqlcode;
313 v_errbuf := sqlerrm ;
314 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
315 RETURN;
316 END get_qualifier_segments ;
317 -- =============================================================
318 PROCEDURE populate_ccid IS
319 l_module_name VARCHAR2(200) := g_module_name || 'populate_ccid';
320
321 TYPE t_seg_name_table IS TABLE OF VARCHAR2(30)
322 INDEX BY BINARY_INTEGER;
323
324
325 TYPE t_seg_str_table IS TABLE OF VARCHAR2(10000)
326 INDEX BY BINARY_INTEGER;
327
328 v_seg t_seg_name_table;
329 v_statement varchar2(25000);
330 v_insert_statement varchar2(25000);
331
332 v_seg_str t_seg_str_table;
333
334 CURSOR flex IS
335 SELECT application_column_name , flex_value_set_id
336 FROM fnd_id_flex_segments
337 WHERE id_flex_code = 'GL#'
338 AND application_id = 101
339 AND id_flex_num = v_chart_of_accounts_id;
340
341 CURSOR child_value(seg varchar2,sid number) IS
342 SELECT child_flex_value_low,child_flex_value_high
343 FROM fnd_flex_value_hierarchies
344 WHERE parent_FLEX_value = seg
345 AND flex_value_set_id = sid;
346
347 child_rec child_value%ROWTYPE;
348
349 CURSOR CREC IS
350 SELECT d.line_id,d.line_detail_id,
351 segment1, segment2, segment3, segment4,
352 segment5, segment6, segment7, segment8,
353 segment9, segment10, segment11, segment12,
354 segment13, segment14, segment15, segment16,
355 segment17, segment18, segment19, segment20,
356 segment21, segment22, segment23, segment24,
357 segment25, segment26, segment27, segment28,
358 segment29, segment30
359 FROM fv_cfs_rep_line_dtl d,
360 fv_cfs_rep_lines L
361 WHERE l.report_type = v_report_type
362 AND d.line_id = l.line_id
363 AND l.set_of_books_id = v_sob
364 ORDER BY 2;
365
366 l_and varchar2(5);
367 l_child varchar2(32000);
368 l_no_of_child number;
369 l_no_of_seg number;
370 l_segno number;
371 l_cnt number;
372
373 BEGIN
374
375 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Ccid process starts');
376
377 For crec_rec in crec
378
379 loop
380 v_seg(1) := crec_rec.segment1;
381 v_seg(2) := crec_rec.segment2;
382 v_seg(3) := crec_rec.segment3;
383 v_seg(4) := crec_rec.segment4;
384 v_seg(5) := crec_rec.segment5;
385 v_seg(6) := crec_rec.segment6;
386 v_seg(7) := crec_rec.segment7;
387 v_seg(8) := crec_rec.segment8;
388 v_seg(9) := crec_rec.segment9;
389 v_seg(10) := crec_rec.segment10;
390 v_seg(11) := crec_rec.segment11;
391 v_seg(12) := crec_rec.segment12;
392 v_seg(13) := crec_rec.segment13;
393 v_seg(14) := crec_rec.segment14;
394 v_seg(15) := crec_rec.segment15;
395 v_seg(16) := crec_rec.segment16;
396 v_seg(17) := crec_rec.segment17;
397 v_seg(18) := crec_rec.segment18;
398 v_seg(19) := crec_rec.segment19;
399 v_seg(20) := crec_rec.segment20;
400 v_seg(21) := crec_rec.segment21;
401 v_seg(22) := crec_rec.segment22;
402 v_seg(23) := crec_rec.segment23;
403 v_seg(24) := crec_rec.segment24;
404 v_seg(25) := crec_rec.segment25;
405 v_seg(26) := crec_rec.segment26;
406 v_seg(27) := crec_rec.segment27;
407 v_seg(28) := crec_rec.segment28;
408 v_seg(29) := crec_rec.segment29;
409 v_seg(30) := crec_rec.segment30;
410
411 v_statement := null;
412 for i in 1 ..30
413 loop
414 v_seg_str(i) := null;
415 End loop;
416
417 l_no_of_seg := 0;
418
419 FOR flex_rec in flex
420
421 LOOP
422
423 l_no_of_child := 0;
424 l_and := NULL;
425
426 -- Check if the segment value is a parent
427 l_segno := substr(flex_rec.application_column_name,8,2);
428 If v_seg(l_segno) is not null then /* 1 */
429
430 select count(*) into l_cnt
431 FROM fnd_flex_value_hierarchies
432 where parent_FLEX_value = v_seg(l_segno)
433 AND flex_value_set_id = flex_rec.flex_value_set_id;
434
435 if (l_cnt > 0) then /* 2 */
436
437 OPEN child_value(v_seg(l_segno) , flex_rec.flex_value_set_id);
438
439 l_and := NULL;
440
441 if (l_no_of_seg > 0) then
442 l_and := ' AND ';
443 End if;
444 l_child := l_and || ' ( ';
445 Loop
446 FETCH child_value into child_rec;
447 exit when child_value%NOTFOUND ;
448
449 if l_no_of_child > 0 then
450 l_child := l_child || ' OR ';
451 End if;
452
453 l_child := l_child || flex_rec.application_column_name ||
454 ' between '|| '''' ||
455 child_rec.child_flex_value_low || ''' and '''
456 || child_rec.child_flex_value_high || '''' || fnd_global.local_chr(10);
457 l_no_of_child := l_no_of_child + 1;
458 End loop;
459 l_child := l_child || ' )' ;
460 l_and := NULL;
461 v_statement := v_statement || l_and || L_CHILD || fnd_global.local_chr(10);
462 CLOSE CHILD_VALUE;
463 ELSE /* 2 */
464 if (l_no_of_seg > 0) then
465 l_and := ' AND ';
466 End if;
467 v_statement := v_statement || l_and ||
468 flex_rec.application_column_name || ' = ''' || v_seg(l_segno) || ''' ' || fnd_global.local_chr(10);
469 End if; /* cnt > 0 */
470
471 l_no_of_seg := l_no_of_seg + 1;
472 End if;
473
474 End loop; /* crec_rec */
475
476 if (v_statement is not null) then
477 v_insert_statement := 'insert into FV_CCID_CFS_GT(
478 detail_id,
479 ccid)
480 select ' ||
481 crec_rec.line_detail_id
482 || ', code_combination_id '
483 || ' from gl_code_combinations WHERE ' || v_statement
484 || ' and template_id is null and '
485 || ' chart_of_accounts_id = :B_CHART_OF_ACCOUNTS_ID
486 and not exists (select code_combination_id
487 from fv_ccid_CFS_GT FCT '
488 || 'where fct.detail_id = :b_line_detail_id '|| ')';
489
490
491 execute immediate v_insert_statement
492 using V_CHART_OF_ACCOUNTS_ID, crec_rec.line_detail_id;
493
494 commit;
495 end if;
496
497
498 End loop; /* flex_crec */
499 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
500 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Popualte CCID Completed');
501 END IF;
502 exception
503 when others then
504 v_retcode := -1;
505 v_errbuf := '[POPULATE-CCID]' || sqlerrm;
506 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
507 return;
508 End populate_ccid;
509 -- =============================================================
510 PROCEDURE build_dynamic_query IS
511 l_module_name VARCHAR2(200) := g_module_name || 'build_dynamic_query';
512
513 CURSOR flex_columns_cursor IS
514 SELECT UPPER(glflex.application_column_name) column_name, flex_value_set_id
515 FROM fnd_id_flex_segments glflex
516 WHERE glflex.application_id = 101
517 AND glflex.id_flex_num = v_chart_of_accounts_id
518 AND glflex.id_flex_code = 'GL#'
519 ORDER BY glflex.application_column_name;
520
521 l_flex_column_name fnd_id_flex_segments.application_column_name%TYPE;
522 l_flex_value_set_id fnd_id_flex_segments.flex_value_set_id%TYPE;
523 l_temp1 VARCHAR2(8000) DEFAULT '';
524 l_temp2 VARCHAR2(8000) DEFAULT '';
525 l_table_name VARCHAR2(50);
526 l_period_name_where VARCHAR2(500);
527 l_stage NUMBER;
528 l_out VARCHAR2(32000);
529 l_column_name VARCHAR2(30);
530 l_glbal_temp VARCHAR2(32000);
531
532 BEGIN
533
534 v_fct1_attr_select :=
535 ' SELECT SUM(NVL(DECODE(:cv_balance_type,
536 ''B'', ROUND(NVL(fctbal.begin_balance,0),2),
537 ''E'', ROUND(NVL(fctbal.balance_amount,0))),0) )
538 FROM fv_cfs_rep_line_dtl dets,
539 fv_ccid_cfs_gt fvcc,
540 fv_facts1_period_attributes fctbal
541 WHERE dets.line_id = :cv_line_id
542 AND dets.line_detail_id = :cv_line_detail_id
543 AND dets.line_detail_id = fvcc.detail_id
544 AND fctbal.ccid = fvcc.ccid
545 AND fctbal.set_of_books_id = :b_sob
546 AND fctbal.period_year = :cv_period_fiscal_year
547 AND nvl(dets.cust_non_cust, nvl(fctbal.cust_non_cust, 1)) = nvl(fctbal.cust_non_cust, 1)
548 AND nvl(dets.exch_non_exch, nvl(fctbal.exch_non_exch, 1)) = nvl(fctbal.exch_non_exch, 1)
549 AND EXISTS
550 (SELECT 1
551 FROM fv_fund_parameters ffp
552 WHERE set_of_books_id = :b_sob
553 AND fund_category like nvl(dets.fund_category, ''%'')
554 AND ffp.fund_value = fctbal.fund_value
555 AND ( (dets.fund_status = ''E'' and trunc(fund_expire_date) <= :cv_end_date )
556 OR (dets.fund_status = ''U''
557 and (trunc(fund_expire_date) >= :cv_end_date or fund_expire_date is null)
558 and (trunc(fund_cancel_date) > :cv_end_date or fund_cancel_date is null))
559 OR (nvl(dets.fund_status,''B'') = ''B'' ))) ';
560
561
562 l_stage := 1;
563 l_out := v_fct1_attr_select;
564
565 v_glbal_curid := dbms_sql.open_cursor;
566 dbms_sql.parse(v_glbal_curid, v_fct1_attr_select, dbms_sql.v7);
567 dbms_sql.define_column(v_glbal_curid, 1, v_amount);
568 dbms_sql.bind_variable(v_glbal_curid,':b_sob',v_sob);
569
570 v_fct1_sel :=
571 'SELECT ROUND(SUM(NVL(ffrt.amount,0) ),2) ';
572
573 v_fct1_rcpt_sel :=
574 v_fct1_sel || ' , ffrt.recipient_name ';
575
576 l_temp1 := '
577 FROM fv_ccid_cfs_gt fvcc,
578 fv_cfs_rep_line_dtl dets,
579 fv_facts1_period_balances_v ffrt
580 WHERE dets.line_id = :cv_line_id
581 AND dets.line_detail_id = :cv_line_detail_id
582 AND dets.line_detail_id = fvcc.detail_id
583 AND ffrt.ccid = fvcc.ccid
584 AND ffrt.period_year = :cv_period_fiscal_year
585 AND ffrt.set_of_books_id = :b_sob
586 AND ffrt.period_num <= :cv_period_num
587 AND ffrt.balance_type = NVL(:cv_balance_type, ffrt.balance_type)
588 AND nvl(dets.fed_non_fed, nvl(ffrt.g_ng_indicator, 1)) =
589 REPLACE(nvl(ffrt.g_ng_indicator, nvl(dets.fed_non_fed, 1)), ' || '''' || ' ' || '''' || ',
590 nvl(dets.fed_non_fed, nvl(ffrt.g_ng_indicator, 1)))
591 AND nvl(dets.cust_non_cust, nvl(ffrt.cust_non_cust, 1)) = nvl(ffrt.cust_non_cust, 1)
592 AND nvl(dets.exch_non_exch, nvl(ffrt.exch_non_exch, 1)) = nvl(ffrt.exch_non_exch, 1)' ;
593
594 v_fct1_sel := v_fct1_sel || l_temp1;
595 v_fct1_rcpt_sel := v_fct1_rcpt_sel || l_temp1 || ' GROUP BY ffrt.recipient_name ';
596
597 l_stage := 3;
598 l_out := v_fct1_rcpt_sel;
599 v_fct1_rcpt_sel_curid := dbms_sql.open_cursor;
600 dbms_sql.parse(v_fct1_rcpt_sel_curid, v_fct1_rcpt_sel, dbms_sql.v7);
601 dbms_sql.define_column(v_fct1_rcpt_sel_curid, 1, v_amount);
602 dbms_sql.define_column(v_fct1_rcpt_sel_curid, 2, v_recipient_name, 240);
603 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':b_sob',v_sob);
604
605 v_fct1_rcpt_sel2 := REPLACE(v_fct1_rcpt_sel, 'GROUP BY ffrt.recipient_name',
606 'AND ffrt.recipient_name = :cv_recipient_name');
607 v_fct1_rcpt_sel2 := REPLACE(v_fct1_rcpt_sel2, ', ffrt.recipient_name', ', 1');
608
609
610 l_stage := 4;
611 l_out := v_fct1_rcpt_sel2;
612 v_fct1_rcpt_sel2_curid := dbms_sql.open_cursor;
613 dbms_sql.parse(v_fct1_rcpt_sel2_curid, v_fct1_rcpt_sel2, dbms_sql.v7);
614 dbms_sql.define_column(v_fct1_rcpt_sel2_curid, 1, v_amount);
615 dbms_sql.define_column(v_fct1_rcpt_sel2_curid, 2, v_recipient_name, 240);
616 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':b_sob',v_sob);
617
618
619 l_stage := 5;
620 l_out := v_fct1_sel;
621
622 v_fct1_sel_curid := dbms_sql.open_cursor;
623 dbms_sql.parse(v_fct1_sel_curid, v_fct1_sel, dbms_sql.v7);
624 dbms_sql.define_column(v_fct1_sel_curid, 1, v_amount);
625 dbms_sql.bind_variable(v_fct1_sel_curid,':b_sob',v_sob);
626
627
628 v_glbal_select :=
629 ' SELECT /*+ USE_HASH (glbal) */
630 NVL(DECODE(:cv_balance_type,
631 ''B'', ROUND(NVL(SUM(NVL(glbal.begin_balance_dr,0) -
632 NVL(glbal.begin_balance_cr,0)),0),2),
633 ''E'', ROUND(NVL(SUM((NVL(glbal.begin_balance_dr,0) -
634 NVL(glbal.begin_balance_cr,0)) +
635 (NVL(glbal.period_net_dr,0) -
636 NVL(glbal.period_net_cr,0))),0),2)),0)
637 FROM fv_cfs_rep_line_dtl dets,
638 fv_ccid_cfs_gt fvcc,
639 gl_code_combinations glc,
640 gl_balances glbal
641 WHERE dets.line_id = :cv_line_id
642 AND dets.line_detail_id = :cv_line_detail_id
643 AND dets.line_detail_id = fvcc.detail_id
644 AND glc.code_combination_id = fvcc.ccid
645 AND glc.chart_of_accounts_id = :b_chart_of_accounts_id
646 AND glbal.code_combination_id = glc.code_combination_id
647 AND glbal.ledger_id = :b_sob
648 AND glbal.period_year = :cv_period_fiscal_year
649 AND glbal.period_num = :cv_period_num
650 --AND glbal.currency_code <> ''STAT''
651 AND glbal.currency_code = :v_currency_code
652 AND glbal.actual_flag = ''A''
653 AND EXISTS
654 (SELECT 1
655 FROM fv_fund_parameters ffp
656 WHERE set_of_books_id = :b_sob
657 AND fund_category like nvl(dets.fund_category, ''%'')
658 AND ffp.fund_value = glc.'||v_bal_seg_name||'
659 AND ( (dets.fund_status = ''E'' and trunc(fund_expire_date) <= :cv_end_date )
660 OR (dets.fund_status = ''U''
661 and (trunc(fund_expire_date) >= :cv_end_date or fund_expire_date is null)
662 and (trunc(fund_cancel_date) > :cv_end_date or fund_cancel_date is null))
663 OR (nvl(dets.fund_status,''B'') = ''B'' ))) ';
664
665 l_stage := 6;
666 l_out := v_glbal_select;
667
668 v_sbr_curid := dbms_sql.open_cursor;
669 dbms_sql.parse(v_sbr_curid, v_glbal_select, dbms_sql.v7);
670 dbms_sql.define_column(v_sbr_curid, 1, v_amount);
671 dbms_sql.bind_variable(v_sbr_curid,':b_chart_of_accounts_id', v_chart_of_accounts_id);
672 dbms_sql.bind_variable(v_sbr_curid,':b_sob',v_sob);
673 dbms_sql.bind_variable(v_sbr_curid,':v_currency_code',v_currency_code);
674
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 v_retcode := SQLCODE ;
679 v_errbuf := SQLERRM || ' [BUILD_DYNAMIC_QUERY] ' ;
680 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
681 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception','Stage it errors ' || l_stage);
682 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_out);
683 RETURN;
684 END build_dynamic_query;
685 -- =============================================================
686 PROCEDURE process_report_line
687 IS
688 l_module_name VARCHAR2(200) := g_module_name || 'process_report_line';
689
690 CURSOR fv_cfs_lines_cur IS
691 SELECT line_id, line_label, sequence_number,
692 line_number, line_type, natural_balance_type, by_recipient
693 FROM fv_cfs_rep_lines
694 WHERE set_of_books_id = v_sob
695 AND report_type = v_report_type
696 ORDER BY sequence_number;
697
698 l_line_cnt NUMBER;
699
700 BEGIN
701
702 FOR lines_rec IN fv_cfs_lines_cur
703 LOOP
704 IF v_retcode IN (0, 1) THEN
705 v_line_id := lines_rec.line_id;
706 v_line_label := lines_rec.line_label;
707 v_line_type := lines_rec.line_type;
708 v_sequence_number := lines_rec.sequence_number;
709 v_line_number := lines_rec.line_number;
710 v_natural_balance_type := lines_rec.natural_balance_type;
711 v_by_recipient := lines_rec.by_recipient;
712 v_col_1_amt := 0;
713 v_col_2_amt := 0;
714 v_col_3_amt := 0;
715 v_col_4_amt := 0;
716 IF lines_rec.line_type IN ('D', 'D2') THEN
717 -- $$$dbms_sql.bind_variable(v_cursor_id1,':cv_line_id',v_line_id);
718 SELECT count(*)
719 INTO l_line_cnt
720 FROM fv_cfs_rep_line_dtl
721 WHERE line_id = v_line_id;
722 IF l_line_cnt = 0 THEN NULL;
723 populate_temp_table;
724 ELSE
725 process_detail_line;
726 END IF;
727 IF lines_rec.line_type = 'D2' THEN
728 v_col_1_amt := ABS(v_col_1_amt);
729 v_col_2_amt := ABS(v_col_1_amt);
730 v_col_3_amt := ABS(v_col_1_amt);
731 v_col_4_amt := ABS(v_col_1_amt);
732 END IF;
733 ELSIF lines_rec.line_type IN ('S', 'T') THEN
734 SELECT count(*)
735 INTO l_line_cnt
736 FROM fv_cfs_rep_line_calc
737 WHERE line_id = v_line_id;
738 IF l_line_cnt = 0 THEN
739 v_retcode := -1;
740 v_errbuf := 'SEED Data not properly Loaded. Please Verify and reinvoke the Process.';
741 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',v_errbuf);
742 RETURN;
743 END IF;
744 -- $$$dbms_sql.bind_variable(v_cursor_id1,':cv_line_id',v_line_id);
745 process_total_line;
746
747 populate_temp_table;
748 ELSIF lines_rec.line_type IN ('L', 'F') THEN
749 v_col_1_amt := NULL;
750 v_col_2_amt := NULL;
751 v_col_3_amt := NULL;
752 v_col_4_amt := NULL;
753 populate_temp_table;
754 END IF;
755 END IF;
756 END LOOP;
757 EXCEPTION
758 WHEN OTHERS THEN
759 v_retcode := SQLCODE ;
760 v_errbuf := SQLERRM || ' [PROCESS_REPORT_LINE] ' ;
761 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
762 RETURN;
763 END process_report_line;
764 -- =============================================================
765 PROCEDURE get_one_time_values
766 IS
767 l_module_name VARCHAR2(200) := g_module_name || 'get_one_time_values';
768 l_stage NUMBER;
769
770 BEGIN
771
772 fv_utility.log_mesg('IN: '||l_module_name);
773
774 l_stage := 1;
775 SELECT chart_of_accounts_id, currency_code
776 INTO v_chart_of_accounts_id, v_currency_code
777 FROM gl_ledgers_public_v
778 WHERE ledger_id = v_sob ;
779
780 -- Get period number and fiscal year being run for
781 l_stage := 2;
782 SELECT trunc(end_date), period_num, period_year, end_date
783 INTO v_end_date, v_period_num, v_period_fiscal_year, v_end_period_end_date
784 FROM gl_period_statuses
785 WHERE ledger_id = v_sob
786 AND application_id = '101'
787 AND period_name = v_period_name;
788
789 v_end_period := v_period_num;
790
791 -- Get begin period num, name and end date of the
792 -- first non adjusting period of the current year
793 l_stage := 3;
794 SELECT period_num, period_name, end_date
795 INTO v_begin_period, v_begin_period_name, v_begin_period_end_date
796 FROM gl_period_statuses
797 WHERE ledger_id = v_sob
798 AND period_year = v_period_fiscal_year
799 AND adjustment_period_flag = 'N'
800 AND application_id = '101'
801 AND period_num =
802 (SELECT MIN(period_num)
803 FROM gl_period_statuses
804 WHERE ledger_id = v_sob
805 AND period_year = v_period_fiscal_year
806 AND adjustment_period_flag = 'N'
807 AND application_id = '101' );
808
809 -- Get begin period num, name and end date of the
810 -- first non adjusting period of the prior year
811 l_stage := 4;
812 SELECT period_num, period_name, end_date
813 INTO v_begin_period_1, v_begin_period_name_1, v_begin_period_1_end_date
814 FROM gl_period_statuses
815 WHERE ledger_id = v_sob
816 AND period_year = v_period_fiscal_year-1
817 AND adjustment_period_flag = 'N'
818 AND application_id = '101'
819 AND period_num =
820 (SELECT MIN(period_num)
821 FROM gl_period_statuses
822 WHERE ledger_id = v_sob
823 AND period_year = v_period_fiscal_year-1
824 AND adjustment_period_flag = 'N'
825 AND application_id = '101') ;
826
827
828 -- Get py period end date for the period being run
829 l_stage := 5;
830 SELECT end_date
831 INTO v_end_period_1_end_date
832 FROM gl_period_statuses
833 WHERE ledger_id = v_sob
834 AND period_year = v_period_fiscal_year-1
835 AND application_id = '101'
836 AND period_num = v_period_num;
837
838 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Chart of accounts id: '||v_chart_of_accounts_id);
839 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Fiscal year: '||v_period_fiscal_year);
840 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_period_num: '||v_period_num);
841 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_end_date: '||v_end_date);
842 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_begin_period: '||v_begin_period);
843 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_begin_period_end_date: '||v_begin_period_end_date);
844 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_begin_period_1: '||v_begin_period_1);
845 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_begin_period_1_end_date: '||v_begin_period_1_end_date);
846 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'v_end_period_1_end_date: '||v_end_period_1_end_date);
847
848 EXCEPTION
849 WHEN NO_DATA_FOUND THEN
850 IF l_stage = 4 OR l_stage = 5 THEN
851 fv_utility.log_mesg('No calendar has been setup for the year: '||to_char(v_period_fiscal_year-1));
852 fv_utility.log_mesg('The Prior Year column will have zero amounts.');
853 v_begin_period_1 := 0;
854 v_begin_period_name_1 := 'XXX';
855 v_begin_period_1_end_date := to_date('01/01/1900', 'mm/dd/yyyy');
856 v_end_period_1_end_date := to_date('01/01/1900', 'mm/dd/yyyy');
857 ELSE
858 v_retcode := -1;
859 v_errbuf := 'When no data found error in get_one_time_values, at stage: '||l_stage;
860 fnd_file.put_line(fnd_file.log, v_errbuf);
861 RETURN;
862 END IF;
863 WHEN OTHERS THEN
864 v_retcode := -1;
865 v_errbuf := 'When others error in get_one_time_values, at stage: '||l_stage;
866 fnd_file.put_line(fnd_file.log, v_errbuf);
867 END get_one_time_values;
868 -- =============================================================
869 PROCEDURE process_detail_line
870 IS
871 l_module_name VARCHAR2(200) := g_module_name || 'process_detail_line';
872
873 CURSOR fv_cfs_detail_cur IS
874 SELECT line_detail_id, balance_type, cum_res,
875 unexp_approp, budget_col, nbfa_col, flex_further_def, fed_non_fed, exch_non_exch, cust_non_cust,
876 DECODE(v_acc_seg_name, 'SEGMENT1', SEGMENT1, 'SEGMENT11', SEGMENT11, 'SEGMENT21', SEGMENT21,
877 'SEGMENT2', SEGMENT2, 'SEGMENT12', SEGMENT12, 'SEGMENT22', SEGMENT22,
878 'SEGMENT3', SEGMENT3, 'SEGMENT13', SEGMENT13, 'SEGMENT23', SEGMENT23,
879 'SEGMENT4', SEGMENT4, 'SEGMENT14', SEGMENT14, 'SEGMENT24', SEGMENT24,
880 'SEGMENT5', SEGMENT5, 'SEGMENT15', SEGMENT15, 'SEGMENT25', SEGMENT25,
881 'SEGMENT6', SEGMENT6, 'SEGMENT16', SEGMENT16, 'SEGMENT26', SEGMENT26,
882 'SEGMENT7', SEGMENT7, 'SEGMENT17', SEGMENT17, 'SEGMENT27', SEGMENT27,
883 'SEGMENT8', SEGMENT8, 'SEGMENT18', SEGMENT18, 'SEGMENT28', SEGMENT28,
884 'SEGMENT9', SEGMENT9, 'SEGMENT19', SEGMENT19, 'SEGMENT29', SEGMENT29,
885 'SEGMENT10', SEGMENT10, 'SEGMENT20', SEGMENT20, 'SEGMENT30', SEGMENT30) account_number,
886 segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.' || segment5 || '.' ||
887 segment6 || '.' || segment7 || '.' || segment8 || '.' || segment9 || '.' || segment10 || '.' ||
888 segment11 || '.' || segment12 || '.' || segment13 || '.' || segment14 || '.' || segment15 || '.' ||
889 segment16 || '.' || segment17 || '.' || segment18 || '.' || segment19 || '.' || segment20 || '.' ||
890 segment21 || '.' || segment22 || '.' || segment23 || '.' || segment24 || '.' || segment25 || '.' ||
891 segment26 || '.' || segment27 || '.' || segment28 || '.' || segment29 || '.' || segment30
892 concatenated_segments
893 FROM fv_cfs_rep_line_dtl
894 WHERE line_id = v_line_id;
895
896 TYPE l_recipient_rec_type IS RECORD (
897 recipient_name fv_facts_report_t2.recipient_name%TYPE,
898 col_1_amt fv_cfs_rep_temp.col_1_amt%TYPE := 0,
899 col_2_amt fv_cfs_rep_temp.col_1_amt%TYPE := 0,
900 col_3_amt fv_cfs_rep_temp.col_1_amt%TYPE := 0,
901 col_4_amt fv_cfs_rep_temp.col_1_amt%TYPE := 0);
902
903 TYPE l_recipient_table IS TABLE OF l_recipient_rec_type
904 INDEX BY BINARY_INTEGER;
905
906 l_recipient_rec l_recipient_table;
907 l_recipient_cnt BINARY_INTEGER := 1;
908 l_found BOOLEAN;
909 l_temp_str fv_cfs_rep_lines.line_label%TYPE;
910
911 l_ignore INTEGER;
912 l_prev_year_amount fv_cfs_rep_temp.col_1_amt%TYPE := 0;
913 l_begin_balance NUMBER;
914 l_end_balance NUMBER;
915 l_period_name_1 gl_period_statuses.period_name%TYPE;
916 l_period_name_2 gl_period_statuses.period_name%TYPE;
917 l_begin_period_name gl_period_statuses.period_name%TYPE;
918 l_begin_period_name_1 gl_period_statuses.period_name%TYPE;
919 l_begin_period_name_2 gl_period_statuses.period_name%TYPE;
920 l_period_fiscal_year NUMBER;
921 l_begin_period NUMBER;
922 l_end_period NUMBER := v_period_num;
923 l_end_period_1 NUMBER ;
924 l_end_period_name_1 gl_period_statuses.period_name%TYPE;
925 l_begin_period_1 NUMBER;
926 l_begin_period_end_date DATE;
927 l_end_period_end_date DATE := v_end_date;
928 l_begin_period_1_end_date DATE;
929 l_end_period_1_end_date DATE;
930 l_log_mesg VARCHAR2(32000) := '';
931 l_conc_segs VARCHAR2(32000);
932 l_delimiter fnd_id_flex_structures.concatenated_segment_delimiter%TYPE;
933 l_account_type VARCHAR2(1) := '';
934 l_account_number gl_code_combinations.segment1%TYPE;
935 l_balance_determined NUMBER;
936 l_prev_year_gl_balance NUMBER;
937 l_curr_year_gl_balance NUMBER;
938 l_e_ne_ind fv_facts_attributes.exch_non_exch%TYPE := NULL;
939 l_c_nc_ind fv_facts_attributes.cust_non_cust%TYPE := NULL;
940 l_diff_amt NUMBER;
941 l_diff_amt_tot NUMBER := 0;
942 l_temp_amount NUMBER;
943 l_ussgl_acct_num NUMBER;
944 l_period_year gl_period_statuses.period_name%TYPE;
945
946 l_amount NUMBER;
947
948 l_end_period_num1 NUMBER;
949 l_end_period_num2 NUMBER;
950 l_ccid_gl_amt NUMBER;
951 l_gl_tot_amt NUMBER;
952 l_bal_type_amt NUMBER;
953
954 BEGIN
955
956 SELECT concatenated_segment_delimiter
957 INTO l_delimiter
958 FROM fnd_id_flex_structures
959 WHERE application_id = 101
960 AND id_flex_code = 'GL#'
961 AND id_flex_num = v_chart_of_accounts_id;
962
963 l_log_mesg := '***** Line Number' || v_line_number || ': ' || '*****';
964 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,l_log_mesg);
965
966 FOR detail_rec IN fv_cfs_detail_cur
967 LOOP ---- L1
968 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, '########');
969 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Account Num: '||detail_rec.account_number);
970 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'line id: '||v_line_id);
971 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'line detail id: '||detail_rec.line_detail_id);
972 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'balance type: '||detail_rec.balance_type);
973 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Fed Non Fed: '||detail_rec.fed_non_fed);
974 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'By Recipient: '||v_by_recipient);
975
976 v_balance_type := detail_rec.balance_type;
977
978 l_balance_determined := 0;
979 l_prev_year_gl_balance := 0;
980 l_curr_year_gl_balance := 0;
981 l_diff_amt_tot := 0;
982
983 SELECT RTRIM(REPLACE(detail_rec.concatenated_segments, '.', l_delimiter),l_delimiter)
984 INTO l_conc_segs
985 FROM dual;
986
987 IF detail_rec.flex_further_def = 'Y' THEN --- 1
988 l_log_mesg :=
989 'Accounting Flexfield -1' || l_conc_segs || ' ' || 'Flexfield Needs Further Definition.' ;
990 fnd_file.put_line(fnd_file.log, 'Warning: Accounting Flexfield - '||l_conc_segs||' needs further definition.');
991 v_retcode := 1;
992 ELSE --- 1
993 IF v_by_recipient = 'Y' THEN --- 2
994
995 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_line_id',v_line_id);
996 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_line_detail_id',detail_rec.line_detail_id);
997 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_balance_type',detail_rec.balance_type);
998 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_period_fiscal_year',v_period_fiscal_year);
999 dbms_sql.bind_variable(v_fct1_rcpt_sel_curid,':cv_period_num', v_end_period);
1000 l_ignore := dbms_sql.execute(v_fct1_rcpt_sel_curid);
1001
1002 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_line_id',v_line_id);
1003 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1004 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_balance_type',detail_rec.balance_type);
1005 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1006 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_period_num', v_end_period);
1007
1008 LOOP
1009 l_ignore := dbms_sql.fetch_rows(v_fct1_rcpt_sel_curid);
1010 EXIT WHEN l_ignore= 0;
1011 dbms_sql.column_value(v_fct1_rcpt_sel_curid, 1, v_amount);
1012 dbms_sql.column_value(v_fct1_rcpt_sel_curid, 2, v_recipient_name);
1013 v_col_1_amt := nvl(v_amount, 0);
1014
1015 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Recipient name: '||v_recipient_name);
1016 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Recipient amount: '||v_amount);
1017
1018 dbms_sql.bind_variable(v_fct1_rcpt_sel2_curid,':cv_recipient_name',v_recipient_name);
1019 l_ignore := dbms_sql.execute_and_fetch(v_fct1_rcpt_sel2_curid);
1020 dbms_sql.column_value(v_fct1_rcpt_sel2_curid, 1, v_amount);
1021 v_col_2_amt := nvl(v_amount, 0);
1022 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Recipient group by amount: '||v_amount);
1023
1024 l_found := FALSE;
1025 FOR i IN 1..l_recipient_cnt - 1
1026 LOOP
1027 IF l_recipient_rec(i).recipient_name = v_recipient_name THEN
1028 l_recipient_rec(i).col_1_amt := l_recipient_rec(i).col_1_amt + v_col_1_amt;
1029 l_recipient_rec(i).col_2_amt := l_recipient_rec(i).col_2_amt + v_col_2_amt;
1030 l_found := TRUE;
1031 END IF;
1032 END LOOP;
1033 IF NOT l_found THEN
1034 l_recipient_rec(l_recipient_cnt).recipient_name := v_recipient_name;
1035 l_recipient_rec(l_recipient_cnt).col_1_amt := v_col_1_amt;
1036 l_recipient_rec(l_recipient_cnt).col_2_amt := v_col_2_amt;
1037 l_recipient_cnt := l_recipient_cnt + 1;
1038 END IF;
1039 END LOOP;
1040
1041 ELSE --- 2
1042 IF detail_rec.account_number IS NOT NULL THEN
1043 BEGIN
1044 SELECT SUBSTR(compiled_value_attributes, 5, 1)
1045 INTO l_account_type
1046 FROM fnd_flex_values
1047 WHERE flex_value = detail_rec.account_number
1048 AND flex_value_set_id = v_acct_flex_value_set_id;
1049 EXCEPTION
1050 WHEN NO_DATA_FOUND THEN
1051 BEGIN
1052 SELECT parent_flex_value
1053 INTO l_account_number
1054 FROM fnd_flex_value_hierarchies
1055 WHERE detail_rec.account_number
1056 BETWEEN child_flex_value_low AND child_flex_value_high
1057 AND flex_value_set_id = v_acct_flex_value_set_id
1058 AND ROWNUM = 1;
1059 SELECT SUBSTR(compiled_value_attributes, 5, 1)
1060 INTO l_account_type
1061 FROM fnd_flex_values
1062 WHERE flex_value = l_account_number
1063 AND flex_value_set_id = v_acct_flex_value_set_id;
1064 EXCEPTION
1065 WHEN NO_DATA_FOUND THEN
1066 NULL;
1067 END;
1068 END;
1069 END IF;
1070 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Account Type: '||l_account_type);
1071 -- ===================================================================
1072 v_py_gl_beg_bal := 0;
1073 v_py_gl_end_bal := 0;
1074 v_cy_gl_beg_bal := 0;
1075 v_cy_gl_end_bal := 0;
1076
1077 -- Get beginning balances for current and prior years from
1078 -- facts1 attributes.
1079 dbms_sql.bind_variable(v_glbal_curid,':cv_line_id',v_line_id);
1080 dbms_sql.bind_variable(v_glbal_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1081
1082 -- Get prior year beginning balance
1083 dbms_sql.bind_variable(v_glbal_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1084 dbms_sql.bind_variable(v_glbal_curid,':cv_balance_type','B');
1085
1086 dbms_sql.bind_variable(v_glbal_curid,':cv_end_date', v_begin_period_1_end_date);
1087 l_ignore := dbms_sql.execute_and_fetch(v_glbal_curid);
1088 dbms_sql.column_value(v_glbal_curid, 1, v_py_gl_beg_bal);
1089 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year begin gl bal: '||v_py_gl_beg_bal);
1090
1091 -- Get current year beginning balance
1092 dbms_sql.bind_variable(v_glbal_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1093
1094 dbms_sql.bind_variable(v_glbal_curid,':cv_end_date', v_begin_period_end_date);
1095 dbms_sql.bind_variable(v_glbal_curid,':cv_balance_type','B');
1096 l_ignore := dbms_sql.execute_and_fetch(v_glbal_curid);
1097 dbms_sql.column_value(v_glbal_curid, 1, v_cy_gl_beg_bal);
1098 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year begin gl bal: '||v_cy_gl_beg_bal);
1099
1100 -- ===================================================================
1101
1102 IF v_report_type <> 'SBR'
1103 AND l_account_type NOT IN ('D','C') THEN --- 3
1104
1105 v_py_fct1_begbal := 0;
1106 v_py_fct1_endbal := 0;
1107 v_cy_fct1_begbal := 0;
1108 v_cy_fct1_endbal := 0;
1109
1110 -- If the balance type is Net Increase or Net Decrease
1111 -- and the natural balance is blank in the set up form
1112 -- then abort process and return error.
1113 IF ( detail_rec.balance_type IN ('I','J') AND
1114 v_natural_balance_type IS NULL ) THEN
1115 l_log_mesg :=
1116 'Line Number: '||v_line_number||' has an account
1117 with balance type Net Increase or Net Decrease but
1118 has a blank Natural Balance in the Report Definitions form.
1119 Please select Natural Balance for any line with a Balance Type
1120 of Net Increase or Net Decrease.';
1121 fnd_file.put_line(fnd_file.log, l_log_mesg);
1122 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, l_log_mesg);
1123 v_retcode := -1 ;
1124 v_errbuf := l_log_mesg;
1125 ROLLBACK;
1126 RETURN;
1127 END IF;
1128
1129 --======= PRIOR YEAR CALCULATION ===========
1130 -- Get facts1 beginning balance for prior year
1131 IF detail_rec.balance_type IN ('B','G','I','J') THEN
1132
1133 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year begin bal: '||v_py_gl_beg_bal);
1134
1135 IF detail_rec.balance_type = 'B' THEN
1136 v_amount:= v_py_gl_beg_bal;
1137 END IF;
1138 END IF;
1139
1140 -- Get facts1 ending balance for prior year
1141 IF detail_rec.balance_type IN ('C', 'D', 'E', 'G', 'I', 'J') THEN
1142 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_line_id',v_line_id);
1143 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1144 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1145 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_balance_type','');
1146 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_period_num',v_end_period);
1147 l_ignore := dbms_sql.execute_and_fetch(v_fct1_sel_curid);
1148 dbms_sql.column_value(v_fct1_sel_curid, 1, v_py_fct1_endbal);
1149 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year end facts1 bal: '||v_py_fct1_endbal);
1150
1151 v_amount := get_bal_type_amt(detail_rec.balance_type,
1152 v_natural_balance_type,
1153 NVL(v_py_gl_beg_bal,0),
1154 NVL(v_py_fct1_endbal,0));
1155 END IF;
1156 -- Set prior year amounts for reporting
1157 IF detail_rec.cum_res = 'Y' OR detail_rec.budget_col = 'Y' THEN
1158 v_col_3_amt := v_col_3_amt + NVL(v_amount, 0);
1159 ELSIF detail_rec.cum_res IS NULL AND detail_rec.budget_col IS NULL THEN
1160 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1161 END IF;
1162 IF detail_rec.unexp_approp = 'Y' OR detail_rec.nbfa_col = 'Y' THEN
1163 v_col_4_amt := v_col_4_amt + NVL(v_amount, 0);
1164 END IF;
1165
1166 --======= CURRENT YEAR CALCULATION ===========
1167 -- Get facts1 beginning balance for current year
1168 v_amount := 0;
1169 IF detail_rec.balance_type IN ('B','G','I','J') THEN
1170 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year begin bal: '||v_cy_gl_beg_bal);
1171 IF detail_rec.balance_type = 'B' THEN
1172 v_amount := v_cy_gl_beg_bal;
1173 END IF;
1174 END IF;
1175
1176 -- Get facts1 ending balance for current year
1177 IF detail_rec.balance_type IN ('C', 'D', 'E', 'G', 'I', 'J') THEN
1178 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_line_id',v_line_id);
1179 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1180 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1181 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_balance_type','');
1182 dbms_sql.bind_variable(v_fct1_sel_curid,':cv_period_num',v_end_period);
1183 l_ignore := dbms_sql.execute_and_fetch(v_fct1_sel_curid);
1184 dbms_sql.column_value(v_fct1_sel_curid, 1, v_cy_fct1_endbal);
1185 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year end facsts1 bal: '||v_cy_fct1_endbal);
1186
1187 v_amount := get_bal_type_amt(detail_rec.balance_type,
1188 v_natural_balance_type,
1189 NVL(v_cy_gl_beg_bal,0),
1190 NVL(v_cy_fct1_endbal,0));
1191 END IF;
1192 -- Set current year amounts for reporting
1193 IF detail_rec.cum_res = 'Y' OR
1194 detail_rec.budget_col = 'Y'
1195 OR (detail_rec.cum_res IS NULL AND
1196 detail_rec.budget_col IS NULL) THEN
1197 v_col_1_amt := v_col_1_amt + NVL(v_amount, 0);
1198 END IF;
1199 IF detail_rec.unexp_approp = 'Y' OR detail_rec.nbfa_col = 'Y' THEN
1200 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1201 END IF;
1202
1203 l_log_mesg := ' Accounting Flexfield -2' || l_conc_segs ||
1204 ' ' || NVL(v_amount, 0);
1205
1206
1207 END IF; --- 3
1208
1209 IF ((v_report_type = 'SBR' ) OR
1210 l_account_type IN ('D','C')) THEN
1211
1212 v_cy_sbr_beg_bal := 0;
1213 v_cy_sbr_end_bal := 0;
1214 v_py_sbr_beg_bal := 0;
1215 v_py_sbr_end_bal := 0;
1216
1217 dbms_sql.bind_variable(v_sbr_curid,':cv_line_id',v_line_id);
1218 dbms_sql.bind_variable(v_sbr_curid,':cv_line_detail_id',detail_rec.line_detail_id);
1219
1220 -- Get Current Year balances --
1221 -------------------------------
1222 IF detail_rec.balance_type = 'B' THEN
1223 -- IF balance type is begin
1224 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
1225 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period);
1226 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_end_date);
1227 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1228 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1229 dbms_sql.column_value(v_sbr_curid, 1, v_cy_sbr_beg_bal);
1230 v_amount := v_cy_sbr_beg_bal;
1231 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year begin bal: '||v_cy_sbr_beg_bal);
1232 ELSIF detail_rec.balance_type IN ('C','D','E','G','I','J') THEN
1233 -- IF balance type is ending, ending cr only or ending dr only
1234 -- Get the begin balance
1235 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
1236 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period);
1237 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_end_date);
1238 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1239 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1240 dbms_sql.column_value(v_sbr_curid, 1, v_cy_sbr_beg_bal);
1241 -- Get the end balance
1242 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','E');
1243 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_end_period);
1244 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_end_period_end_date);
1245 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year);
1246 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1247 dbms_sql.column_value(v_sbr_curid, 1, v_cy_sbr_end_bal);
1248 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Current year end bal: '||v_cy_sbr_end_bal);
1249
1250 v_amount := get_bal_type_amt(detail_rec.balance_type,
1251 v_natural_balance_type,
1252 NVL(v_cy_sbr_beg_bal,0),
1253 NVL(v_cy_sbr_end_bal,0));
1254 END IF;
1255
1256 -- Set current year amounts for reporting
1257 IF detail_rec.cum_res = 'Y' OR detail_rec.budget_col = 'Y'
1258 OR (detail_rec.cum_res IS NULL AND detail_rec.budget_col IS NULL) THEN
1259 v_col_1_amt := v_col_1_amt + NVL(v_amount, 0);
1260 END IF;
1261 IF detail_rec.unexp_approp = 'Y' OR detail_rec.nbfa_col = 'Y' THEN
1262 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1263 END IF;
1264
1265 ---- Get Prior year balances ----
1266 ---------------------------------
1267 IF detail_rec.balance_type = 'B' THEN
1268 -- If balance type is begin
1269 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
1270 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period_1);
1271 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_1_end_date);
1272 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1273 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1274 dbms_sql.column_value(v_sbr_curid, 1, v_py_sbr_beg_bal);
1275 v_amount := v_py_sbr_beg_bal;
1276 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year begin bal: '||v_py_sbr_beg_bal);
1277 -- IF balance type is ending, ending cr only or ending dr only
1278 ELSIF detail_rec.balance_type IN ('C', 'D','E','G','I','J') THEN
1279 -- Get the begin balance
1280 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','B');
1281 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_begin_period_1);
1282 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_begin_period_1_end_date);
1283 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1284 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1285 dbms_sql.column_value(v_sbr_curid, 1, v_py_sbr_beg_bal);
1286 -- Get the end balance
1287 dbms_sql.bind_variable(v_sbr_curid,':cv_balance_type','E');
1288 dbms_sql.bind_variable(v_sbr_curid,':cv_period_num',v_period_num);
1289 dbms_sql.bind_variable(v_sbr_curid,':cv_end_date', v_end_period_1_end_date);
1290 dbms_sql.bind_variable(v_sbr_curid,':cv_period_fiscal_year',v_period_fiscal_year-1);
1291 l_ignore := dbms_sql.execute_and_fetch(v_sbr_curid);
1292 dbms_sql.column_value(v_sbr_curid, 1, v_py_sbr_end_bal);
1293 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Prior year end bal: '||v_py_sbr_end_bal);
1294 v_amount := get_bal_type_amt(detail_rec.balance_type,
1295 v_natural_balance_type,
1296 NVL(v_py_sbr_beg_bal,0),
1297 NVL(v_py_sbr_end_bal,0));
1298 END IF;
1299
1300 l_log_mesg := ', ' || NVL(v_amount, 0);
1301
1302 IF detail_rec.cum_res = 'Y' OR detail_rec.budget_col = 'Y' THEN
1303 v_col_3_amt := v_col_3_amt + NVL(v_amount, 0);
1304 ELSIF detail_rec.cum_res IS NULL AND detail_rec.budget_col IS NULL THEN
1305 v_col_2_amt := v_col_2_amt + NVL(v_amount, 0);
1306 END IF;
1307 IF detail_rec.unexp_approp = 'Y' OR detail_rec.nbfa_col = 'Y' THEN
1308 v_col_4_amt := v_col_4_amt + NVL(v_amount, 0);
1309 END IF;
1310
1311 END IF; --- 10
1312
1313 END IF; --- 2
1314 END IF; --- 1
1315 END LOOP; --- L1
1316
1317 IF v_by_recipient = 'Y' THEN
1318 v_col_1_amt := NULL;
1319 v_col_2_amt := NULL;
1320 populate_temp_table;
1321 l_temp_str := substr(v_line_label,1,length(v_line_label) - length(ltrim(v_line_label))) || ' ';
1322 FOR i IN 1..l_recipient_cnt - 1
1323 LOOP
1324 v_line_label := l_temp_str || l_recipient_rec(i).recipient_name;
1325 v_col_1_amt := l_recipient_rec(i).col_1_amt;
1326 v_col_2_amt := l_recipient_rec(i).col_2_amt;
1327 populate_temp_table;
1328 END LOOP;
1329 ELSE
1330 SELECT REPLACE(l_log_mesg, '*****', v_col_1_amt || ', ' || v_col_2_amt || ', '
1331 || v_col_3_amt || ', ' || v_col_4_amt)
1332 INTO l_log_mesg FROM dual;
1333 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1334 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,L_LOG_MESG);
1335 END IF;
1336 populate_temp_table;
1337 END IF;
1338
1339 EXCEPTION
1340 WHEN OTHERS THEN
1341 v_retcode := SQLCODE ;
1342 v_errbuf := SQLERRM || ' [PROCESS_DETAIL_LINE] ' ;
1343 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
1344 RETURN;
1345
1346 END process_detail_line;
1347 -- =============================================================
1348 PROCEDURE process_total_line
1349 IS
1350 l_module_name VARCHAR2(200) := g_module_name || 'process_total_line';
1351
1352 CURSOR fv_cfs_calc_cur IS
1353 SELECT calc_sequence_number, line_low, line_high, line_low_type, line_high_type,
1354 operator, cum_res, unexp_approp, budget_col, nbfa_col
1355 FROM fv_cfs_rep_line_calc
1356 WHERE line_id = v_line_id
1357 ORDER BY calc_sequence_number;
1358
1359 CURSOR fv_cfs_temp_cur (p_line_id NUMBER) IS
1360 SELECT col_1_amt, col_2_amt,
1361 col_3_amt, col_4_amt
1362 FROM fv_cfs_rep_temp
1363 WHERE line_id = p_line_id
1364 AND sequence_id = v_sequence_id;
1365
1366 CURSOR fv_cfs_lines_cur(p_lineid_1 NUMBER, p_lineid_2 NUMBER) IS
1367 SELECT line_id
1368 FROM fv_cfs_rep_lines
1369 WHERE sequence_number >=
1370 (SELECT sequence_number FROM fv_cfs_rep_lines
1371 WHERE line_id = p_lineid_1 )
1372 AND sequence_number <=
1373 (SELECT sequence_number FROM fv_cfs_rep_lines
1374 WHERE line_id = p_lineid_2 )
1375 AND report_type = v_report_type;
1376
1377 l_line_id fv_cfs_rep_lines.line_id%TYPE;
1378 temp_amt_low1 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1379 temp_amt_low2 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1380 temp_amt_low3 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1381 temp_amt_low4 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1382 temp_amt_high1 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1383 temp_amt_high2 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1384 temp_amt_high3 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1385 temp_amt_high4 fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0;
1386
1387 TYPE amt_rec IS RECORD (
1388 calc_sequence fv_cfs_rep_line_calc.calc_sequence_number%TYPE,
1389 col_1_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1390 col_2_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1391 col_3_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1392 col_4_amt fv_cfs_rep_temp.col_1_amt%TYPE DEFAULT 0,
1393 cum_res fv_cfs_rep_line_calc.cum_res%TYPE ,
1394 unexp_approp fv_cfs_rep_line_calc.unexp_approp%TYPE ,
1395 budget_col fv_cfs_rep_line_calc.budget_col%TYPE ,
1396 nbfa_col fv_cfs_rep_line_calc.nbfa_col%TYPE );
1397
1398 TYPE amt_table IS TABLE OF amt_rec
1399 INDEX BY BINARY_INTEGER;
1400
1401 amt_array amt_table;
1402 amt_array_cnt BINARY_INTEGER DEFAULT 1;
1403
1404 BEGIN
1405 FOR calc_rec IN fv_cfs_calc_cur
1406 LOOP
1407 amt_array(amt_array_cnt).calc_sequence := calc_rec.calc_sequence_number;
1408 amt_array(amt_array_cnt).cum_res := calc_rec.cum_res;
1409 amt_array(amt_array_cnt).unexp_approp := calc_rec.unexp_approp;
1410 amt_array(amt_array_cnt).budget_col := calc_rec.budget_col;
1411 amt_array(amt_array_cnt).nbfa_col := calc_rec.nbfa_col;
1412 IF calc_rec.line_low_type = 'L' AND calc_rec.operator IN ('+','-') THEN
1413 l_line_id := calc_rec.line_low;
1414 OPEN fv_cfs_temp_cur(l_line_id);
1415 FETCH fv_cfs_temp_cur
1416 INTO temp_amt_low1, temp_amt_low2, temp_amt_low3, temp_amt_low4;
1417 CLOSE fv_cfs_temp_cur;
1418 ELSIF calc_rec.line_low_type = 'C' AND calc_rec.operator IN ('+','-') THEN
1419 FOR i IN 1..amt_array_cnt
1420 LOOP
1421 IF amt_array(i).calc_sequence = calc_rec.line_low THEN
1422 temp_amt_low1 := amt_array(i).col_1_amt;
1423 temp_amt_low2 := amt_array(i).col_2_amt;
1424 temp_amt_low3 := amt_array(i).col_3_amt;
1425 temp_amt_low4 := amt_array(i).col_4_amt;
1426 END IF;
1427 END LOOP;
1428 END IF;
1429
1430 IF calc_rec.line_high_type = 'L' AND calc_rec.operator IN ('+','-') THEN
1431 l_line_id := calc_rec.line_high;
1432 OPEN fv_cfs_temp_cur(l_line_id);
1433 FETCH fv_cfs_temp_cur
1434 INTO temp_amt_high1, temp_amt_high2, temp_amt_high3, temp_amt_high4;
1435 CLOSE fv_cfs_temp_cur;
1436 ELSIF calc_rec.line_high_type = 'C' AND calc_rec.operator IN ('+','-') THEN
1437 FOR i IN 1..amt_array_cnt - 1
1438 LOOP
1439 IF amt_array(i).calc_sequence = calc_rec.line_high THEN
1440 temp_amt_high1 := amt_array(i).col_1_amt;
1441 temp_amt_high2 := amt_array(i).col_2_amt;
1442 temp_amt_high3 := amt_array(i).col_3_amt;
1443 temp_amt_high4 := amt_array(i).col_4_amt;
1444 END IF;
1445 END LOOP;
1446 END IF;
1447
1448 IF calc_rec.operator = '+' THEN
1449 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low1, 0) + NVL(temp_amt_high1, 0);
1450 amt_array(amt_array_cnt).col_2_amt := NVL(temp_amt_low2, 0) + NVL(temp_amt_high2, 0);
1451 amt_array(amt_array_cnt).col_3_amt := NVL(temp_amt_low3, 0) + NVL(temp_amt_high3, 0);
1452 amt_array(amt_array_cnt).col_4_amt := NVL(temp_amt_low4, 0) + NVL(temp_amt_high4, 0);
1453 ELSIF calc_rec.operator = '-' THEN
1454 amt_array(amt_array_cnt).col_1_amt := NVL(temp_amt_low1, 0) - NVL(temp_amt_high1, 0);
1455 amt_array(amt_array_cnt).col_2_amt := NVL(temp_amt_low2, 0) - NVL(temp_amt_high2, 0);
1456 amt_array(amt_array_cnt).col_3_amt := NVL(temp_amt_low3, 0) - NVL(temp_amt_high3, 0);
1457 amt_array(amt_array_cnt).col_4_amt := NVL(temp_amt_low4, 0) - NVL(temp_amt_high4, 0);
1458 ELSE
1459 IF calc_rec.line_low_type = 'L' THEN
1460 FOR lines_rec IN fv_cfs_lines_cur(calc_rec.line_low, calc_rec.line_high)
1461 LOOP
1462 FOR fv_cfs_temp_cur_rec IN fv_cfs_temp_cur(lines_rec.line_id)
1463 LOOP
1464 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);
1465 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);
1466 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);
1467 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);
1468 END LOOP;
1469 END LOOP;
1470 ELSIF calc_rec.line_low_type = 'C' THEN
1471 FOR i IN 1..amt_array_cnt - 1
1472 LOOP
1473 IF amt_array(i).calc_sequence >= calc_rec.line_low
1474 AND amt_array(i).calc_sequence <= calc_rec.line_high THEN
1475 amt_array(amt_array_cnt).col_1_amt := amt_array(amt_array_cnt).col_1_amt + NVL(amt_array(i).col_1_amt, 0);
1476 amt_array(amt_array_cnt).col_2_amt := amt_array(amt_array_cnt).col_2_amt + NVL(amt_array(i).col_2_amt, 0);
1477 amt_array(amt_array_cnt).col_3_amt := amt_array(amt_array_cnt).col_3_amt + NVL(amt_array(i).col_3_amt, 0);
1478 amt_array(amt_array_cnt).col_4_amt := amt_array(amt_array_cnt).col_4_amt + NVL(amt_array(i).col_4_amt, 0);
1479 END IF;
1480 END LOOP;
1481 END IF;
1482 END IF;
1483
1484 IF calc_rec.cum_res = 'N' OR calc_rec.budget_col = 'N' THEN
1485 amt_array(amt_array_cnt).col_1_amt := 0;
1486 amt_array(amt_array_cnt).col_3_amt := 0;
1487 END IF;
1488 IF calc_rec.unexp_approp = 'N' OR calc_rec.nbfa_col = 'N' THEN
1489 amt_array(amt_array_cnt).col_2_amt := 0;
1490 amt_array(amt_array_cnt).col_4_amt := 0;
1491 END IF;
1492 amt_array_cnt := amt_array_cnt + 1;
1493 END LOOP;
1494
1495 IF v_report_type IN ('SBR','SCNP') THEN
1496 FOR i IN 1..amt_array_cnt - 1
1497 LOOP
1498 IF amt_array(i).cum_res = 'Y' OR amt_array(i).budget_col = 'Y' THEN
1499 v_col_1_amt := amt_array(i).col_1_amt;
1500 v_col_3_amt := amt_array(i).col_3_amt;
1501 END IF;
1502 IF amt_array(i).unexp_approp = 'Y' OR amt_array(i).nbfa_col = 'Y' THEN
1503 v_col_2_amt := amt_array(i).col_2_amt;
1504 v_col_4_amt := amt_array(i).col_4_amt;
1505 END IF;
1506 END LOOP;
1507 ELSE
1508 v_col_1_amt := amt_array(amt_array_cnt - 1).col_1_amt;
1509 v_col_2_amt := amt_array(amt_array_cnt - 1).col_2_amt;
1510 v_col_3_amt := amt_array(amt_array_cnt - 1).col_3_amt;
1511 v_col_4_amt := amt_array(amt_array_cnt - 1).col_4_amt;
1512 END IF;
1513
1514 EXCEPTION
1515 WHEN OTHERS THEN
1516 v_retcode := SQLCODE ;
1517 v_errbuf := SQLERRM || ' [PROCESS_TOTAL_LINE] ' ;
1518 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
1519 RETURN;
1520 END process_total_line;
1521 -- =============================================================
1522 PROCEDURE populate_temp_table
1523 IS
1524 l_module_name VARCHAR2(200) := g_module_name || 'populate_temp_table';
1525 BEGIN
1526
1527 -- Bug 4927632. If units are 'Dollars and Cents'
1528 -- then do not round off the amounts
1529 -- Bug 5491457. If the report type is SF and natural balance type is Net Increase or
1530 -- Net Decrease, then drop the sign from the amounts.
1531 IF (v_report_type = 'SF' AND v_balance_type IN ('I', 'J')) THEN
1532 IF gbl_units <> 'Dollars and Cents' THEN
1533 INSERT INTO fv_cfs_rep_temp(sequence_id, line_id, line_label, col_1_amt,
1534 col_2_amt, col_3_amt, col_4_amt)
1535 VALUES(v_sequence_id, v_line_id, v_line_label,
1536 ABS(ROUND(v_col_1_amt/v_units)),
1537 ABS(ROUND(v_col_2_amt/v_units)),
1538 ABS(ROUND(v_col_3_amt/v_units)),
1539 ABS(ROUND(v_col_4_amt/v_units)));
1540 ELSE
1541 INSERT INTO fv_cfs_rep_temp(sequence_id, line_id, line_label, col_1_amt,
1542 col_2_amt, col_3_amt, col_4_amt)
1543 VALUES(v_sequence_id, v_line_id, v_line_label,
1544 ABS(v_col_1_amt),
1545 ABS(v_col_2_amt),
1546 ABS(v_col_3_amt),
1547 ABS(v_col_4_amt));
1548 END IF;
1549
1550 ELSE
1551
1552 IF gbl_units <> 'Dollars and Cents' THEN
1553 INSERT INTO fv_cfs_rep_temp(sequence_id, line_id, line_label, col_1_amt,
1554 col_2_amt, col_3_amt, col_4_amt)
1555 VALUES(v_sequence_id, v_line_id, v_line_label,
1556 DECODE(v_natural_balance_type, 'C', ROUND(v_col_1_amt/v_units) * -1, ROUND(v_col_1_amt/v_units)),
1557 DECODE(v_natural_balance_type, 'C', ROUND(v_col_2_amt/v_units) * -1, ROUND(v_col_2_amt/v_units)),
1558 DECODE(v_natural_balance_type, 'C', ROUND(v_col_3_amt/v_units) * -1, ROUND(v_col_3_amt/v_units)),
1559 DECODE(v_natural_balance_type, 'C', ROUND(v_col_4_amt/v_units) * -1, ROUND(v_col_4_amt/v_units)));
1560 ELSE
1561
1562 INSERT INTO fv_cfs_rep_temp(sequence_id, line_id, line_label, col_1_amt,
1563 col_2_amt, col_3_amt, col_4_amt)
1564 VALUES(v_sequence_id, v_line_id, v_line_label,
1565 DECODE(v_natural_balance_type, 'C', v_col_1_amt * -1, v_col_1_amt),
1566 DECODE(v_natural_balance_type, 'C', v_col_2_amt * -1, v_col_2_amt),
1567 DECODE(v_natural_balance_type, 'C', v_col_3_amt * -1, v_col_3_amt),
1568 DECODE(v_natural_balance_type, 'C', v_col_4_amt * -1, v_col_4_amt));
1569 END IF;
1570
1571 END IF;
1572
1573 EXCEPTION
1574 WHEN OTHERS THEN
1575 v_retcode := -1;
1576 v_errbuf := SQLERRM;
1577 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
1578 RAISE;
1579
1580 END populate_temp_table;
1581 -- =============================================================
1582 FUNCTION get_bal_type_amt(p_balance_type VARCHAR,
1583 p_natural_bal_type VARCHAR,
1584 p_beg_bal NUMBER, p_end_bal NUMBER)
1585 RETURN NUMBER
1586 IS
1587 l_module_name VARCHAR2(200) := g_module_name || 'populate_temp_table';
1588 l_end_minus_beg_amt NUMBER;
1589 BEGIN
1590 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'IN get_bal_type_amt function');
1591 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'Natural balance: '||p_natural_bal_type);
1592 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_beg_bal: '||p_beg_bal);
1593 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'p_end_bal: '||p_end_bal);
1594
1595
1596 IF p_balance_type = 'E' THEN
1597 RETURN p_end_bal;
1598 END IF;
1599 -- If balance type is Ending (Cr only) or
1600 -- Ending (DR only)
1601 IF p_balance_type = 'C' THEN
1602 IF p_end_bal >= 0 THEN
1603 RETURN 0;
1604 ELSE
1605 RETURN p_end_bal;
1606 END IF;
1607 ELSIF p_balance_type = 'D' THEN
1608 IF p_end_bal <= 0 THEN
1609 RETURN 0;
1610 ELSE
1611 RETURN p_end_bal;
1612 END IF;
1613 END IF;
1614
1615 -- If balance type is End minus Begin, Net Increase or
1616 -- Net Decrease then report amount depending on the
1617 -- natural balance type
1618 l_end_minus_beg_amt := p_end_bal - p_beg_bal;
1619 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name, 'end minus begin: '||l_end_minus_beg_amt);
1620 IF p_balance_type = 'G' THEN
1621 RETURN l_end_minus_beg_amt;
1622 END IF;
1623 IF p_balance_type = 'I' THEN
1624 IF p_natural_bal_type = 'C' THEN
1625 IF l_end_minus_beg_amt <= 0 THEN
1626 RETURN l_end_minus_beg_amt;
1627 ELSE
1628 RETURN 0;
1629 END IF;
1630 ELSIF p_natural_bal_type = 'D' THEN
1631 IF l_end_minus_beg_amt > 0 THEN
1632 RETURN l_end_minus_beg_amt;
1633 ELSE
1634 RETURN 0;
1635 END IF;
1636 END IF;
1637 ELSIF
1638 p_balance_type = 'J' THEN
1639 IF p_natural_bal_type = 'C' THEN
1640 IF l_end_minus_beg_amt >= 0 THEN
1641 RETURN l_end_minus_beg_amt;
1642 ELSE
1643 RETURN 0;
1644 END IF;
1645 ELSIF
1646 p_natural_bal_type = 'D' THEN
1647 IF l_end_minus_beg_amt < 0 THEN
1648 RETURN l_end_minus_beg_amt;
1649 ELSE
1650 RETURN 0;
1651 END IF;
1652 END IF;
1653 END IF;
1654
1655 EXCEPTION
1656 WHEN OTHERS THEN
1657 v_retcode := -1;
1658 v_errbuf := SQLERRM;
1659 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',v_errbuf);
1660 END get_bal_type_amt;
1661 -- =============================================================
1662 BEGIN
1663
1664 g_module_name := 'fv.plsql.FV_CFS_PKG.';
1665
1666
1667 END fv_cfs_pkg;
1668
1669 -- =============================================================