1 PACKAGE BODY JA_CN_CFS_CALCULATE_PKG AS
2 --$Header: JACNCCEB.pls 120.1.12010000.3 2009/01/04 06:29:44 shyan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNCCEB.pls
10 --|
11 --| DESCRIPTION
12 --|
13 --| This package contains the following PL/SQL tables/procedures/functions
14 --| to implement calculation for main part of cash flow statement according
15 --| to data that collected by 'Cash Flow Statement - Data Collection'
16 --| program and stored in JA_CN_CFS_ACTIVITIES_ALL table, and rules defined
17 --| in Cash Flow Statement Assignments form and Calculation window in FSG
18 --| Row Set.
19 --|
20 --| TYPE LIEST
21 --| G_PERIOD_NAME_TBL
22 --|
23 --| PROCEDURE LIST
24 --| Populate_Period_Names
25 --| Populate_Formula
26 --| Categorize_Rows
27 --| Calculate_Row_Amount
28 --| Calculate_Rows_Amount
29 --| Generate_Cfs_Xml
30 --|
31 --| HISTORY
32 --| 14-Mar-2006 Donghai Wang Created
33 --| 29-Aug-2008 Chaoqun Wu CNAO Enhancement
34 --| Updated procedures Calculate_Row_Amount and Calculate_Rows_Amount
35 --| Added BSV parameter for CFS-Generation
36 --| 23-Sep-2008 Chaoqun Wu Fix bug# 7427067
37 -- 14-Oct-2008 Chaoqun Wu Fix bug# 7481516
38 --| 16-Dec-2008 Shujuan Yan Fix bug 7626489
39 --+======================================================================*/
40
41 l_module_prefix VARCHAR2(100):='JA_CN_CFS_CALCULATE_PKG';
42
43
44 --==========================================================================
45 -- PROCEDURE NAME:
46 --
47 -- Populate_Period_Names Public
48 --
49 -- DESCRIPTION:
50 --
51 -- This procedure is to retrieve period names from gl_periods by the
52 -- parameter 'p_parameter' and the parameter p_balance_type, alternative
53 -- value is 'YTD/QTD/PTD'
54 --
55 -- PARAMETERS:
56 -- In: p_set_of_bks_id Identifier of GL set of book, a required
57 -- parameter for FSG report
58 -- p_period_name GL period Name
59 -- p_balace_type Type of balance, available value is 'YTD/QTD/PTD'.
60 -- a required parameter for FSG report
61 --
62 -- Out: x_period_names Qualified period names for cash flow statement
63 -- calculation
64 --
65 -- DESIGN REFERENCES:
66 -- CNAO_Cashflow_Statement_Generation_TD.doc
67 --
68 -- CHANGE HISTORY:
69 --
70 -- 14-Mar-2006 Donghai Wang Created
71 --
72 --===========================================================================
73 PROCEDURE Populate_Period_Names
74 (p_ledger_id IN NUMBER
75 ,p_period_name IN VARCHAR2
76 ,p_balance_type IN VARCHAR2
77 ,x_period_names OUT NOCOPY JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL
78 )
79 IS
80 l_ledger_id gl_ledgers.ledger_id%TYPE :=p_ledger_id;
81 l_period_set_name gl_periods.period_set_name%TYPE;
82 l_accounted_period_type gl_periods.period_type%TYPE;
83 l_period_name gl_periods.period_name%TYPE :=p_period_name;
84 l_period_year gl_periods.period_year%TYPE;
85 l_period_num gl_periods.period_num%TYPE;
86 l_quarter_num gl_periods.quarter_num%TYPE;
87
88 CURSOR c_period_set_name
89 IS
90 SELECT
91 period_set_name
92 ,accounted_period_type
93 FROM
94 gl_ledgers
95 WHERE ledger_id=l_ledger_id;
96
97 CURSOR c_period_name_attribute
98 IS
99 SELECT
100 period_year
101 ,period_num
102 ,quarter_num
103 FROM
104 gl_periods
105 WHERE period_set_name=l_period_set_name
106 AND period_type=l_accounted_period_type
107 AND period_name=l_period_name;
108
109 CURSOR c_ytd_period_names
110 IS
111 SELECT
112 period_name
113 FROM
114 gl_periods
115 WHERE period_set_name=l_period_set_name
116 AND period_type=l_accounted_period_type
117 AND period_year=l_period_year
118 AND period_num<=l_period_num;
119
120 CURSOR c_qtd_period_names
121 IS
122 SELECT
123 period_name
124 FROM
125 gl_periods
126 WHERE period_set_name=l_period_set_name
127 AND period_type=l_accounted_period_type
128 AND period_year=l_period_year
129 AND quarter_num=l_quarter_num
130 AND period_num<=l_period_num;
131
132 l_period_idx NUMBER;
133 l_curr_period_name gl_periods.period_name%TYPE;
134 l_period_names JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL;
135
136 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
137 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
138 l_proc_name VARCHAR2(100) :='Populate_Period_Names';
139
140
141 BEGIN
142
143 --log for debug
144 IF (l_proc_level >= l_dbg_level)
145 THEN
146 FND_LOG.String(l_proc_level
147 ,l_module_prefix||'.'||l_proc_name||'.begin'
148 ,'Enter procedure'
149 );
150
151 FND_LOG.String(l_proc_level
152 ,l_module_prefix||'.'||l_proc_name||'.parameters'
153 ,'p_ledger_id '||p_ledger_id
154 );
155
156 FND_LOG.String(l_proc_level
157 ,l_module_prefix||'.'||l_proc_name||'.parameters'
158 ,'p_period_name '||p_period_name
159 );
160
161 FND_LOG.String(l_proc_level
162 ,l_module_prefix||'.'||l_proc_name||'.parameters'
163 ,'p_balance_type '||p_balance_type
164 );
165 END IF; --(l_proc_level >= l_dbg_level)
166
167 --To Get current period set name per gl set of book
168 OPEN c_period_set_name;
169 FETCH c_period_set_name INTO l_period_set_name,l_accounted_period_type;
170 CLOSE c_period_set_name;
171
172 --To retrive set of period names according to parameters period_name and balance_type
173
174 --Get period attributes for the period passed by the parameter period_name
175 OPEN c_period_name_attribute;
176 FETCH c_period_name_attribute INTO l_period_year,l_period_num,l_quarter_num;
177 CLOSE c_period_name_attribute;
178
179 l_period_idx:=0;
180
181 --If balance type is YTD, then set of periods include all periods
182 --from year beginning to period parameter
183 IF p_balance_type='YTD'
184 THEN
185
186 OPEN c_ytd_period_names;
187 FETCH c_ytd_period_names INTO l_curr_period_name;
188 WHILE c_ytd_period_names%FOUND
189 LOOP
190 l_period_idx:=l_period_idx+1;
191 l_period_names(l_period_idx):=l_curr_period_name;
192 FETCH c_ytd_period_names INTO l_curr_period_name;
193 END LOOP; -- WHILE c_ytd_period_names%FOUND
194
195 CLOSE c_ytd_period_names;
196
197 --If balance type is QTD, then set of periods include all periods from first period of
198 --same quarter as that of period parameter to period parameter
199
200 ELSIF p_balance_type='QTD'
201 THEN
202 OPEN c_qtd_period_names;
203 FETCH c_qtd_period_names INTO l_curr_period_name;
204 WHILE c_qtd_period_names%FOUND
205 LOOP
206 l_period_idx:=l_period_idx+1;
207 l_period_names(l_period_idx):=l_curr_period_name;
208 FETCH c_qtd_period_names INTO l_curr_period_name;
209 END LOOP; --WHILE c_qtd_period_names%FOUND
210
211 CLOSE c_qtd_period_names;
212
213 ELSIF p_balance_type='PTD'
214 THEN
215 l_period_idx:=l_period_idx+1;
216 l_period_names(l_period_idx):=l_period_name;
217 END IF; --p_balance_type='YTD'
218
219 x_period_names:=l_period_names;
220
221 --log for debug
222 IF ( l_proc_level >= l_dbg_level)
223 THEN
224 FND_LOG.STRING(l_proc_level
225 ,l_module_prefix||'.'||l_proc_name||'.end'
226 ,'Exit procedure'
227 );
228 END IF; --( l_proc_level >= l_dbg_level )
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 IF (l_proc_level >= l_dbg_level)
233 THEN
234 FND_LOG.String(l_proc_level
235 ,l_module_prefix||'.'||l_proc_name||'. Other_Exception '
236 ,SQLCODE||':'||SQLERRM
237 );
238 END IF; --(l_proc_level >= l_dbg_level)
239 END Populate_Period_Names;
240
241 --==========================================================================
242 -- PROCEDURE NAME:
243 --
244 -- Populate_Formula Public
245 --
246 -- DESCRIPTION:
247 --
248 -- For Cash Flow Statement, user would define calculation rule for items
249 -- on 'FSG Rowset' form, one item can be calculated by other items, it
250 -- can have multiple calculation lines. In one calculation line, user can
251 -- define a range for rows from low to high, or specify a specific row.
252 -- Also, rows selected in such calculation rules can be also items that
253 -- are calculated by other items. Just so, it is hard for calculating
254 -- directly. The procedure 'Populate_Formula' is used to convert involved
255 -- calculating items in calculation lines to most detailed items for all
256 -- calculated items, hereinto, most detailed items mean items that are
257 -- directly calculated by FSG account assignments or Cash Flow item assignments.
258 --
259 -- PARAMETERS:
260 -- In: p_axis_set_id Identifier of FSG Row Set
261 --
262 -- DESIGN REFERENCES:
263 -- CNAO_Cashflow_Statement_Generation_TD.doc
264 --
265 -- CHANGE HISTORY:
266 --
267 -- 14-Mar-2006 Donghai Wang Created
268 -- 23-Sep-2008 Chaoqun Wu Fix bug# 7427067
269 --
270 --===========================================================================
271 PROCEDURE Populate_Formula
272 (p_coa IN NUMBER --Fix bug# 7427067
273 ,p_axis_set_id IN NUMBER
274 )
275 IS
276 l_coa NUMBER := p_coa;
277 l_axis_set_id rg_report_axis_sets.axis_set_id%TYPE :=p_axis_set_id;
278 l_calculation_seq NUMBER;
279 l_application_id rg_report_axes.application_id%TYPE;
280 l_axis_seq rg_report_axes.axis_seq%TYPE;
281 l_axis_seq_low rg_report_calculations.axis_seq_low%TYPE;
282 l_axis_seq_high rg_report_calculations.axis_seq_high%TYPE;
283 l_axis_name_low rg_report_calculations.axis_name_low%TYPE;
284 l_operator rg_report_calculations.operator%TYPE;
285 l_operator_flag NUMBER;
286 l_exit_flag VARCHAR2(1);
287 l_cal_axis_seq rg_report_axes.axis_seq%TYPE;
288 l_constant rg_report_calculations.constant%TYPE;
289 l_type VARCHAR2(1);
290 l_display_flag VARCHAR2(1);
291 l_display_zero_flag VARCHAR2(1);
292 l_change_sign_flag VARCHAR2(1);
293 l_calculated_row_count NUMBER;
294 l_calculated_axis_seq rg_report_axes.axis_seq%TYPE;
295
296 -----FOR TEST
297 L_GT_COUNTS NUMBER:=-1;
298 l_gt_counts1 NUMBER:=-1;
299
300 CURSOR c_report_axis
301 IS
302 SELECT
303 DISTINCT
304 rra.application_id
305 ,rra.axis_seq
306 ,rra.display_flag
307 ,rra.display_zero_amount_flag
308 ,rra.change_sign_flag
309 FROM
310 rg_report_axes rra
311 ,rg_report_calculations rrc
312 WHERE rra.axis_set_id=l_axis_set_id
313 AND rra.axis_set_id=rrc.axis_set_id
314 AND rra.axis_seq=rrc.axis_seq;
315
316 CURSOR c_report_calculations
317 IS
318 SELECT
319 operator
320 ,axis_seq_low
321 ,axis_seq_high
322 ,axis_name_low
323 ,constant
324 FROM
325 rg_report_calculations
326 WHERE application_id=l_application_id
327 AND axis_set_id=l_axis_set_id
328 AND axis_seq=l_axis_seq
329 ORDER BY calculation_seq;
330
331 CURSOR c_axis_seqs_per_line
332 IS
333 SELECT
334 rra.axis_seq
335 FROM
336 rg_report_axes rra
337 WHERE rra.axis_set_id=l_axis_set_id
338 AND rra.axis_seq BETWEEN l_axis_seq_low AND l_axis_seq_high
339 --Fix bug# 7427067 begin
340 AND (EXISTS (SELECT
341 rrac.axis_seq
342 FROM
343 rg_report_axis_contents rrac
344 WHERE rrac.application_id=rra.application_id
345 AND rrac.axis_set_id=rra.axis_set_id
346 AND rrac.axis_seq=rra.axis_seq
347 )
348 OR
349 EXISTS (SELECT
350 jccaa.axis_seq
351 FROM
352 ja_cn_cfs_assignments_all jccaa
353 WHERE jccaa.chart_of_accounts_id=l_coa
354 AND rra.axis_set_id=jccaa.axis_set_id
355 AND jccaa.axis_seq=rra.axis_seq
356 )
357 OR
358 EXISTS (SELECT
359 rrc.axis_seq
360 FROM
361 rg_report_calculations rrc
362 WHERE rrc.application_id=rra.application_id
363 AND rra.axis_set_id=rrc.axis_set_id
364 AND rrc.axis_seq=rra.axis_seq
365 )
366 );
367 --Fix bug# 7427067 end
368
369
370 CURSOR c_axis_seq
371 IS
372 SELECT
373 axis_seq
374 FROM
375 rg_report_axes
376 WHERE application_id=l_application_id
377 AND axis_set_id=l_axis_set_id
378 AND axis_name=l_axis_name_low;
379
380 CURSOR c_calculation_rows
381 IS
382 SELECT
383 axis_seq
384 ,type
385 FROM
386 ja_cn_cfs_row_cgs_gt
387 WHERE application_id=l_application_id
388 AND axis_set_id=l_axis_set_id
389 AND calculation_flag='Y'
390 ORDER BY axis_seq;
391
392 CURSOR c_calculated_rows_count
393 IS
394 SELECT
395 count(DISTINCT ccg.axis_seq)
396 FROM
397 ja_cn_cfs_row_cgs_gt crcg
398 ,ja_cn_cfs_calculations_gt ccg
399 WHERE crcg.application_id=l_application_id
400 AND crcg.axis_set_id=l_axis_set_id
401 AND (crcg.type IS NULL) OR (crcg.type<>'E')
402 AND ccg.application_id=crcg.application_id
403 AND ccg.axis_set_id=crcg.axis_set_id
404 AND ccg.cal_axis_seq=l_cal_axis_seq;
405
406
407 CURSOR c_calculated_rows
408 IS
409 SELECT
410 DISTINCT ccg.axis_seq
411 FROM
412 ja_cn_cfs_row_cgs_gt crcg
413 ,ja_cn_cfs_calculations_gt ccg
414 WHERE crcg.application_id=l_application_id
415 AND crcg.axis_set_id=l_axis_set_id
416 AND (crcg.type IS NULL) OR (crcg.type<>'E')
417 AND ccg.application_id=crcg.application_id
418 AND ccg.axis_set_id=crcg.axis_set_id
419 AND ccg.cal_axis_seq=l_cal_axis_seq;
420
421 CURSOR c_cfs_calculation_lines
422 IS
423 SELECT
424 calculation_seq
425 ,operator
426 ,operator_flag
427 ,cal_axis_seq
428 ,constant
429 FROM
430 ja_cn_cfs_calculations_gt
431 WHERE application_id=l_application_id
432 AND axis_set_id=l_axis_set_id
433 AND axis_seq=l_calculated_axis_seq
434 AND cal_axis_seq=l_cal_axis_seq
435 ORDER BY calculation_seq;
436
437 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
438 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
439 l_proc_name VARCHAR2(100) :='Populate_Formula';
440
441
442 BEGIN
443
444 --log for debug
445 IF (l_proc_level >= l_dbg_level)
446 THEN
447 FND_LOG.String(l_proc_level
448 ,l_module_prefix||'.'||l_proc_name||'.begin'
449 ,'Enter procedure'
450 );
451
452 FND_LOG.String(l_proc_level
453 ,l_module_prefix||'.'||l_proc_name||'.parameters'
454 ,'p_axis_set_id '||p_axis_set_id
455 );
456 END IF; --(l_proc_level >= l_dbg_level)
457
458 --Retrive initial canculation lines from RG_REPORT_CALCULATIONS table for
459 --each item in FSG Row Set that have calculation definition,
460 --if a calculation line contain sequence number range that perform calculation,
461 --then split this calculation line to multiple calculation lines and each line only
462 --have one sequence number that perform calcluation, instead of a range.
463
464
465 --Retrive all rows that have calculation definition for current FSG Row Set
466 OPEN c_report_axis;
467 FETCH c_report_axis INTO l_application_id,l_axis_seq,l_display_flag,l_display_zero_flag,l_change_sign_flag;
468 WHILE c_report_axis%FOUND
469 LOOP
470 l_calculation_seq:=1;
471 l_exit_flag:='N';
472
473 --Retrive calculation lines for current row
474 OPEN c_report_calculations;
475 FETCH c_report_calculations INTO l_operator,l_axis_seq_low,l_axis_seq_high,l_axis_name_low,l_constant;
476 WHILE c_report_calculations%FOUND
477 LOOP
478
479 --Calculation for Cash flow statment only supports '+'/'-'/Enter as operator, if operator is '*'/'/'
480 --or any other operaters, then program would not perform calcuation for current FSG row and will show
481 --an error message in correspondng item of Cash Flow Statement that is ultimately generated
482 --by xml publisher
483
484 --If l_operator is not '+'/'-'/'Enter', then doesn't continue checking curent cash flow item
485 --and marked calculation for this item as error
486 IF l_operator NOT IN ('+','-','ENTER')
487 THEN
488 l_exit_flag:='Y';
489 EXIT;
490 ELSE
491
492 --If current calculation line is defined by sequence number range,then
493 --Convert the range to multiple single sequence number, so the calculation line
494 --would be split to multiple calculation line and inserted into temporary table
495 --'JA_CN_CFS_CALCULATION_GBLTEMP
496
497 --Operator 'Enter' has the same function as'+'
498 IF l_operator='ENTER'
499 THEN
500 l_operator:='+';
501 END IF; --l_operator='ENTER'
502
503 --To set operator flag
504 IF l_operator='+'
505 THEN
506 l_operator_flag:=1;
507 ELSIF l_operator='-'
508 THEN
509 l_operator_flag:=-1;
510 END IF;--l_operator='+'
511
512
513 IF (l_axis_seq_low IS NOT NULL) AND
514 (l_axis_seq_high IS NOT NULL)
515 THEN
516
517 --Split current line to multiple lines by sequence number range,
518 --each line only contain one sequence number
519
520 OPEN c_axis_seqs_per_line;
521 FETCH c_axis_seqs_per_line INTO l_cal_axis_seq;
522 WHILE c_axis_seqs_per_line%FOUND
523 LOOP
524
525
526 INSERT
527 INTO
528 ja_cn_cfs_calculations_gt
529 (application_id,axis_set_id
530 ,axis_seq
531 ,calculation_seq
532 ,operator
533 ,operator_flag
534 ,cal_axis_seq
535 ,constant
536 )
537 VALUES
538 (l_application_id
539 ,l_axis_set_id
540 ,l_axis_seq
541 ,l_calculation_seq
542 ,l_operator
543 ,l_operator_flag
544 ,l_cal_axis_seq
545 ,''
546 );
547
548 -----------FOR TEST--------------------
549 SELECT COUNT(*)
550 INTO L_GT_COUNTS
551 FROM ja_cn_cfs_calculations_gt;
552 ---------------------------------------
553 l_calculation_seq:=l_calculation_seq+1;
554 FETCH c_axis_seqs_per_line INTO l_cal_axis_seq;
555 END LOOP;
556
557 CLOSE c_axis_seqs_per_line;
558
559 --If current calculation line only contains one row name as operand,
560 --then retrieve sequence number by row name and insert it into temporary table
561 --'JA_CN_CFS_CALCULATION_GBLTEMP'
562 ELSIF l_axis_name_low IS NOT NULL
563 THEN
564 OPEN c_axis_seq;
565 FETCH c_axis_seq INTO l_cal_axis_seq;
566 CLOSE c_axis_seq;
567
568 INSERT
569 INTO
570 ja_cn_cfs_calculations_gt
571 (application_id,axis_set_id
572 ,axis_seq
573 ,calculation_seq
574 ,operator
575 ,operator_flag
576 ,cal_axis_seq
577 ,constant
578 )
579 VALUES
580 (l_application_id
581 ,l_axis_set_id
582 ,l_axis_seq
583 ,l_calculation_seq
584 ,l_operator
585 ,l_operator_flag
586 ,l_cal_axis_seq
587 ,''
588 );
589
590 l_calculation_seq:=l_calculation_seq+1;
591
592 --If current calculation line only constains a constant,then directly insert this
593 --line into temporary table 'JA_CN_CFS_CALCULATION_GBLTEMP'
594 ELSIF l_constant IS NOT NULL THEN
595
596
597 INSERT
598 INTO
599 ja_cn_cfs_calculations_gt
600 (application_id,axis_set_id
601 ,axis_seq
602 ,calculation_seq
603 ,operator
604 ,operator_flag
605 ,cal_axis_seq
606 ,constant
607 )
608 VALUES
609 (l_application_id
610 ,l_axis_set_id
611 ,l_axis_seq
612 ,l_calculation_seq
613 ,l_operator
614 ,l_operator_flag
615 ,''
616 ,l_constant
617 );
618
619 l_calculation_seq:=l_calculation_seq+1;
620 END IF; --(l_axis_seq_low IS NOT NULL)
621 END IF; --l_operator<>'+' or l_operator<>'-' or l_operator <> 'ENTER'
622 FETCH c_report_calculations INTO l_operator,l_axis_seq_low,l_axis_seq_high,l_axis_name_low,l_constant;
623 END LOOP; --WHILE c_report_calculation%FOUND
624
625 CLOSE c_report_calculations;
626
627 --Insert into current item into the tempoary table 'ja_cn_cfs_row_cgs_gt'
628 --and set value of the column 'CALCULATION_FLAG' as 'Y'. If current item has calcuation lines with
629 --wrong operator, then set value of the column 'TYPE' as 'E'
630
631 IF l_exit_flag='Y'
632 THEN
633 l_type:='E';
634 ELSE
635 l_type:='';
636 END IF; --l_exit_flag='Y'
637
638 INSERT
639 INTO
640 ja_cn_cfs_row_cgs_gt
641 (application_id
642 ,axis_set_id
643 ,axis_seq
644 ,type
645 ,calculation_flag
646 ,display_flag
647 ,display_zero_amount_flag
648 ,change_sign_flag
649 )
650 VALUES
651 (l_application_id
652 ,l_axis_set_id
653 ,l_axis_seq
654 ,l_type
655 ,'Y'
656 ,l_display_flag
657 ,l_display_zero_flag
658 ,l_change_sign_flag
659 );
660
661 FETCH c_report_axis INTO l_application_id,l_axis_seq,l_display_flag,l_display_zero_flag,l_change_sign_flag;
662 END LOOP; --c_report_axis%FOUND
663
664 CLOSE c_report_axis;
665
666 --It is possible that a row in calculation lines of a item is also a calcuated item, so the following
667 --steps will translate rows in calculation lines into most detail rows
668
669 --Retrive calculated items from the temporary table 'ja_cn_cfs_row_cgs_gt'
670
671 OPEN c_calculation_rows;
672 FETCH c_calculation_rows INTO l_cal_axis_seq,l_type;
673 WHILE c_calculation_rows%FOUND
674 LOOP
675
676 l_calculated_row_count:=0;
677
678 OPEN c_calculated_rows_count;
679 FETCH c_calculated_rows_count INTO l_calculated_row_count;
680 CLOSE c_calculated_rows_count;
681
682 --Judge if there are other rows that use current row as operand
683 IF l_calculated_row_count>0
684 THEN
685
686 --If current row with type 'E', then all other calculation rows that has this row as operand should be with type 'E' as well
687 IF l_type='E'
688 THEN
689 UPDATE
690 ja_cn_cfs_row_cgs_gt crcg
691 SET
692 crcg.type='E'
693 WHERE crcg.application_id=l_application_id
694 AND crcg.axis_set_id=l_axis_set_id
695 AND (crcg.type IS NULL OR crcg.type<>'E')
696 AND crcg.axis_seq IN (SELECT
697 DISTINCT ccg.axis_seq
698 FROM
699 ja_cn_cfs_calculations_gt ccg
700 WHERE ccg.application_id=crcg.application_id
701 AND ccg.axis_set_id=crcg.axis_set_id
702 AND ccg.cal_axis_seq=l_cal_axis_seq
703 );
704 ELSE
705
706 --Begin to decompose current row number, replace calculation lines that are of
707 --curent row number with calculation lines that belong to current row for all
708 --other fsg rows that have current row as operands.
709 OPEN c_calculated_rows;
710 FETCH c_calculated_rows INTO l_calculated_axis_seq;
711 WHILE c_calculated_rows%FOUND
712 LOOP
713
714
715
716 FOR l_cal_line IN c_cfs_calculation_lines
717 LOOP
718
719 --Decompose current row number
720 INSERT
721 INTO
722 ja_cn_cfs_calculations_gt
723 (application_id,axis_set_id
724 ,axis_seq
725 ,calculation_seq
726 ,operator
727 ,operator_flag
728 ,cal_axis_seq
729 ,constant
730 )
731 SELECT
732 l_application_id
733 ,l_axis_set_id
734 ,l_calculated_axis_seq
735 ,l_cal_line.calculation_seq+calculation_seq/10000
736 ,decode(l_cal_line.operator_flag*operator_flag
737 ,1
738 ,'+'
739 ,-1
740 ,'-'
741 ,'+'
742 )
743 ,l_cal_line.operator_flag*operator_flag
744 ,cal_axis_seq
745 ,constant
746 FROM
747 ja_cn_cfs_calculations_gt
748 WHERE application_id=l_application_id
749 AND axis_set_id=l_axis_set_id
750 AND axis_seq=l_cal_axis_seq;
751
752
753 END LOOP; --l_cal_line IN c_cfs_calculation_lines
754
755 --Delete lines with cal_axis_seq 'l_cal_axis_seq' from calculation lines
756 --of row l_calculated_axis_seq
757 --
758 DELETE
759 FROM
760 ja_cn_cfs_calculations_gt
761 WHERE application_id=l_application_id
762 AND axis_set_id=l_axis_set_id
763 AND axis_seq=l_calculated_axis_seq
764 AND cal_axis_seq=l_cal_axis_seq;
765
766 FETCH c_calculated_rows INTO l_calculated_axis_seq;
767
768 END LOOP; --WHILE c_calculated_rows%FOUND
769
770
771
772 CLOSE c_calculated_rows;
773
774 END IF;--l_type='E'
775
776 END IF; --l_calculated_row_count>0
777
778 FETCH c_calculation_rows INTO l_cal_axis_seq,l_type;
779
780 END LOOP; --WHILE c_calculation_rows%FOUND
781
782 CLOSE c_calculation_rows;
783
784 -----------FOR TEST--------------------
785 SELECT COUNT(*)
786 INTO l_gt_counts1
787 FROM ja_cn_cfs_row_cgs_gt;
788 ---------------------------------------
789
790
791
792 -----------FOR TEST--------------------
793 SELECT COUNT(*)
794 INTO L_GT_COUNTS
795 FROM ja_cn_cfs_calculations_gt;
796 ---------------------------------------
797
798
799
800 --log for debug
801 IF ( l_proc_level >= l_dbg_level)
802 THEN
803 FND_LOG.STRING(l_proc_level
804 ,l_module_prefix||'.'||l_proc_name||'.end'
805 ,'Exit procedure'
806 );
807 END IF; --( l_proc_level >= l_dbg_level )
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 IF (l_proc_level >= l_dbg_level)
812 THEN
813 FND_LOG.String(l_proc_level
814 ,l_module_prefix||'.'||l_proc_name||'. Other_Exception '
815 ,SQLCODE||':'||SQLERRM
816 );
817 END IF; --(l_proc_level >= l_dbg_level)
818
819 END Populate_Formula;
820
821 --==========================================================================
822 -- PROCEDURE NAME:
823 --
824 -- Categorize_Rows Public
825 --
826 -- DESCRIPTION:
827 --
828 -- The 'Categorize_Rows' procedure is to categorize rows in FSG rowsets that
829 -- are defined for Cash Flow statement with the following three types:
830 -- 1. Rows belong to subsidiary part of Cash Flow Statement
831 -- 2. Rows belong to main part of Cash Flow Statement
832 -- 3. Rows that have calculation on FSG rowset form, but those rows
833 -- involved in calculation respectively belong to above the type one
834 -- and the type two.
835 --
836 -- PARAMETERS:
837 -- In: p_legal_entity_id Identifier of legal entity
838 -- p_axis_set_id Identifier of FSG Row Set
839 --
840 -- DESIGN REFERENCES:
841 -- CNAO_Cashflow_Statement_Generation_TD.doc
842 --
843 -- CHANGE HISTORY:
844 --
845 -- 14-Mar-2006 Donghai Wang Created
846 --
847 --===========================================================================
848 PROCEDURE Categorize_Rows
849 (p_coa IN NUMBER
850 ,p_axis_set_id IN NUMBER
851 )
852 IS
853 l_axis_set_id rg_report_axis_sets.axis_set_id%TYPE :=p_axis_set_id;
854 l_coa NUMBER :=p_coa;
855 l_cal_type VARCHAR2(1);
856 l_type VARCHAR2(1);
857
858 CURSOR c_axis_seq_fsg
859 IS
860 SELECT
861 rra.application_id
862 ,rra.axis_seq
863 ,rra.display_flag
864 ,rra.display_zero_amount_flag
865 ,rra.change_sign_flag
866 ,rra.display_format
867 FROM
868 rg_report_axes rra
869 WHERE rra.axis_set_id=l_axis_set_id
870 AND EXISTS (SELECT
871 rrac.axis_seq
872 FROM
873 rg_report_axis_contents rrac
874 WHERE rrac.application_id=rra.application_id
875 AND rrac.axis_set_id=rra.axis_set_id
876 AND rrac.axis_seq=rra.axis_seq
877 );
878
879 CURSOR c_axis_seq_cfs
880 IS
881 SELECT
882 DISTINCT
883 rra.application_id
884 ,jccaa.axis_seq
885 ,rra.display_flag
886 ,rra.display_zero_amount_flag
887 ,rra.change_sign_flag
888 ,rra.display_format
889 FROM
890 ja_cn_cfs_assignments_all jccaa
891 ,rg_report_axes rra
892 WHERE jccaa.chart_of_accounts_id=l_coa
893 AND jccaa.axis_set_id=l_axis_set_id
894 AND rra.axis_set_id=jccaa.axis_set_id
895 AND jccaa.axis_seq=rra.axis_seq
896 AND jccaa.axis_seq NOT IN (SELECT
897 jccrcg.axis_seq
898 FROM
899 ja_cn_cfs_row_cgs_gt jccrcg
900 WHERE axis_set_id=l_axis_set_id
901 );
902
903 CURSOR c_axis_seq_desc
904 IS
905 SELECT
906 rra.application_id
907 ,rra.axis_seq
908 ,rra.display_flag
909 ,rra.display_zero_amount_flag
910 ,rra.change_sign_flag
911 ,rra.display_format
912 FROM
913 rg_report_axes rra
914 WHERE rra.axis_set_id=l_axis_set_id
915 AND NOT EXISTS(SELECT
916 crcg.axis_seq
917 FROM
918 ja_cn_cfs_row_cgs_gt crcg
919 WHERE crcg.axis_set_id=rra.axis_set_id
920 AND crcg.axis_seq=rra.axis_seq
921 );
922
923 CURSOR c_cal_axis_seqs
924 IS
925 SELECT
926 application_id
927 ,axis_seq
928 FROM
929 ja_cn_cfs_row_cgs_gt
930 WHERE axis_set_id=l_axis_set_id
931 AND calculation_flag='Y'
932 AND (type IS NULL OR type<>'E')
933 FOR UPDATE;
934
935 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
936 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
937 l_proc_name VARCHAR2(100) :='Categorize_Rows';
938
939 BEGIN
940
941 --log for debug
942 IF (l_proc_level >= l_dbg_level)
943 THEN
944 FND_LOG.String(l_proc_level
945 ,l_module_prefix||'.'||l_proc_name||'.begin'
946 ,'Enter procedure'
947 );
948
949 FND_LOG.String(l_proc_level
950 ,l_module_prefix||'.'||l_proc_name||'.parameters'
951 ,'p_coa '||p_coa
952 );
953
954 FND_LOG.String(l_proc_level
955 ,l_module_prefix||'.'||l_proc_name||'.parameters'
956 ,'p_axis_set_id '||p_axis_set_id
957 );
958 END IF; --(l_proc_level >= l_dbg_level)
959 --Retrive rows that have account assignment in FSG rowset,which are most detailed items for subsidiary
960 --part of Cash flow Statement, and then insert these rows into the temporary table 'ja_cn_cfs_row_cgs_gt'
961 --with type 'F'
962
963 FOR l_axis_seq_fsg IN c_axis_seq_fsg
964 LOOP
965 INSERT
966 INTO
967 ja_cn_cfs_row_cgs_gt
968 (application_id
969 ,axis_set_id
970 ,axis_seq
971 ,type
972 ,calculation_flag
973 ,display_flag
974 ,display_zero_amount_flag
975 ,change_sign_flag
976 ,display_format
977 )
978 VALUES
979 (l_axis_seq_fsg.application_id
980 ,l_axis_set_id
981 ,l_axis_seq_fsg.axis_seq
982 ,'F'
983 ,'N'
984 ,l_axis_seq_fsg.display_flag
985 ,l_axis_seq_fsg.display_zero_amount_flag
986 ,l_axis_seq_fsg.change_sign_flag
987 ,l_axis_seq_fsg.display_format
988 );
989
990 END LOOP; -- l_axis_seq_fsg IN c_axis_seq_fsg
991
992
993
994 --If rows in FSG rowset have not account assignment and calculation, but have assignments by Cash Flow Item
995 --Assignment form, they should be regarded as most detailed items for main part of Cash Flow Statement. Insert
996 --these rows into the temporary table 'ja_cn_cfs_row_cgs_gt' with type 'C' after they are identified
997
998 --So rows that are most detailed item for main part of Cash flow statement, shoud be those in the table
999 --JA_CN_CFS_ASSIGNMENTS_ALL for current legal entity and row set, and not in the table 'ja_cn_cfs_row_cgs_gt'
1000 FOR l_axis_seq_cfs IN c_axis_seq_cfs
1001 LOOP
1002 INSERT
1003 INTO
1004 ja_cn_cfs_row_cgs_gt
1005 (application_id
1006 ,axis_set_id
1007 ,axis_seq
1008 ,type
1009 ,calculation_flag
1010 ,display_flag
1011 ,display_zero_amount_flag
1012 ,change_sign_flag
1013 ,display_format
1014 )
1015 VALUES
1016 (l_axis_seq_cfs.application_id
1017 ,l_axis_set_id
1018 ,l_axis_seq_cfs.axis_seq
1019 ,'C'
1020 ,'N'
1021 ,l_axis_seq_cfs.display_flag
1022 ,l_axis_seq_cfs.display_zero_amount_flag
1023 ,l_axis_seq_cfs.change_sign_flag
1024 ,l_axis_seq_cfs.display_format
1025 );
1026
1027 END LOOP; --FOR l_axis_seq_cfs IN c_axis_seq_cfs
1028
1029
1030
1031 --For all rows in FSG rowset that do not have calculation, account
1032 --assignments and cash flow item assignments,-they are description
1033 --lines in cash flow statment, we will store them into table
1034 --'ja_cn_cfs_row_cgs_gt' and mark them with type 'F'.
1035
1036 FOR l_axis_seq_desc IN c_axis_seq_desc
1037 LOOP
1038 INSERT
1039 INTO
1040 ja_cn_cfs_row_cgs_gt
1041 (application_id
1042 ,axis_set_id
1043 ,axis_seq
1044 ,type
1045 ,calculation_flag
1046 ,display_flag
1047 ,display_zero_amount_flag
1048 ,change_sign_flag
1049 ,display_format
1050 )
1051 VALUES
1052 (l_axis_seq_desc.application_id
1053 ,l_axis_set_id
1054 ,l_axis_seq_desc.axis_seq
1055 ,'F'
1056 ,'N'
1057 ,l_axis_seq_desc.display_flag
1058 ,l_axis_seq_desc.display_zero_amount_flag
1059 ,l_axis_seq_desc.change_sign_flag
1060 ,l_axis_seq_desc.display_format
1061 );
1062
1063 END LOOP; --FOR l_axis_seq_desc IN c_axis_seq_desc
1064
1065
1066
1067
1068 --For a row with calculation_flag 'Y' in the table ja_cn_cfs_row_cgs_gt,
1069 --it means this row is a item which amount is calcuated by other rows.
1070 --In this case, if all rows that are involved in calculation for it belong to
1071 --main part of cash flow statment, then this row should belong to main part of
1072 --cash flow statemnt too, it should be marked as type 'C' in the table
1073 --'ja_cn_cfs_row_cgs_gt'.Else, if all rows that are involved in
1074 --calculation for it are belong to subsidiary part of cash flow statement,
1075 --then this row should belong to subsidiary part of cash flow statemnt as well,
1076 --it should be marked as type 'F' in the table 'ja_cn_cfs_row_cgs_gt'.
1077 --Else, if some rows that are involved in calcuation for it belong to main part
1078 --of cash flow statement, but others belong to subsidiary part of cash flow
1079 --statement, this row should be marked as type 'M', it would be processed by
1080 --procedure Generate_Cfs_Xml.
1081 FOR l_cal_axis_seq IN c_cal_axis_seqs
1082 LOOP
1083
1084 BEGIN
1085 --To get types of calculation lines of current row
1086 SELECT
1087 DISTINCT crcg.type
1088 INTO
1089 l_cal_type
1090 FROM
1091 ja_cn_cfs_row_cgs_gt crcg
1092 ,ja_cn_cfs_calculations_gt ccg
1093 WHERE ccg.application_id=l_cal_axis_seq.application_id
1094 AND ccg.axis_set_id=l_axis_set_id
1095 AND ccg.axis_seq=l_cal_axis_seq.axis_seq
1096 AND crcg.application_id=ccg.application_id
1097 AND crcg.axis_set_id=ccg.axis_set_id
1098 AND crcg.axis_seq=ccg.cal_axis_seq;
1099
1100 --If all operandS in calculation lines have type 'F',then the calculated
1101 --row should have type 'F' as well
1102 IF l_cal_type='F'
1103 THEN
1104 l_type:='F';
1105
1106 ----If all operandS in calculation lines have type 'C',then the calculated
1107 --row should have type 'C' as well
1108 ELSIF l_cal_type='C'
1109 THEN
1110 l_type:='C';
1111 END IF; -- l_cal_type='F'
1112
1113 EXCEPTION
1114 --If the sql raise NO_DATA_FOUND exception,it means all calculation lines
1115 --of current row are comprised by CONSTANT, not include any other fsg row
1116 --so the type of current should be 'F', which is an item in subsidiary part of
1117 --cash flow statement
1118 WHEN NO_DATA_FOUND THEN
1119 l_type:='F';
1120
1121 --If the sql raise TOO_MANY_ROWS exception,it means some calculation lines
1122 --of current row belong to subsidiary part of cash flow statement and others
1123 --belong to main part of cash flow statement, so the type of current row should
1124 --be 'M', it means amount of current row will be calculated by lines from both
1125 --parts
1126 WHEN TOO_MANY_ROWS THEN
1127 l_type:='M';
1128
1129 WHEN OTHERS THEN
1130 RAISE;
1131 END;
1132
1133 --Update type of current row in the table ja_cn_cfs_row_cgs_gt
1134 UPDATE
1135 ja_cn_cfs_row_cgs_gt
1136 SET
1137 type=l_type
1138 WHERE CURRENT OF c_cal_axis_seqs;
1139
1140 END LOOP; --l_cal_axis_seq IN c_cal_axis_seqs
1141
1142 --log for debug
1143 IF ( l_proc_level >= l_dbg_level)
1144 THEN
1145 FND_LOG.STRING(l_proc_level
1146 ,l_module_prefix||'.'||l_proc_name||'.end'
1147 ,'Exit procedure'
1148 );
1149 END IF; --( l_proc_level >= l_dbg_level )
1150
1151 EXCEPTION
1152 WHEN OTHERS THEN
1153 IF (l_proc_level >= l_dbg_level)
1154 THEN
1155 FND_LOG.String(l_proc_level
1156 ,l_module_prefix||'.'||l_proc_name||'. Other_Exception '
1157 ,SQLCODE||':'||SQLERRM
1158 );
1159 END IF; --(l_proc_level >= l_dbg_level)
1160 END Categorize_Rows;
1161
1162 --==========================================================================
1163 -- PROCEDURE NAME:
1164 --
1165 -- Calculate_Row_Amount Public
1166 --
1167 -- DESCRIPTION:
1168 --
1169 -- The procedure 'Calculate_Row_Amount' is used to calculate amount for a
1170 -- specific cash flow item in the main part of cash flow statement according
1171 -- to assignment in the table 'JA_CN_CFS_ASSIGNMENTS_ALL' and amount of detailed
1172 -- cash flow item in the table 'JA_CN_CFS_ACTIVITIES_ALL'.
1173 --
1174 -- PARAMETERS:
1175 -- In: p_legal_entity_id Identifier of legal entity
1176 -- p_set_of_bks_id Identifier of GL set of book, a required
1177 -- parameter for FSG report
1178 -- p_axis_set_id Identifier of FSG Row Set
1179 -- p_axis_seq Sequence number of FSG row
1180 --
1181 -- p_balace_type Type of balance, available value is 'YTD/QTD/PTD'.
1182 -- a required parameter for FSG report
1183 -- p_period_names Qualified period names for cash flow statement
1184 -- calculation
1185 -- p_rounding_option Rounding option for amount in Cash Flow statement
1186 -- p_internal_trx_flag To indicate if intercompany transactions
1187 -- should be involved in amount calculation
1188 -- of cash flow statement.
1189 --
1190 --
1191 -- Out: x_amount Amount of cash flow item
1192 --
1193 -- DESIGN REFERENCES:
1194 -- CNAO_Cashflow_Statement_Generation_TD.doc
1195 --
1196 -- CHANGE HISTORY:
1197 --
1198 -- 14-Mar-2006 Donghai Wang Created
1199 -- 1-Sep-2008 Chaoqun Wu Added BSV parameter for CNAO Enhancement
1200 --
1201 --===========================================================================
1202 PROCEDURE Calculate_Row_Amount
1203 (p_legal_entity_id IN NUMBER
1204 ,p_ledger_id IN NUMBER
1205 ,p_coa IN NUMBER --added by lyb
1206 ,p_axis_set_id IN NUMBER
1207 ,p_axis_seq IN NUMBER
1208 ,p_period_names IN JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL
1209 ,p_rounding_option IN VARCHAR2
1210 ,p_balancing_segment_value IN VARCHAR2 --added for CNAO Enhancement
1211 --,p_internal_trx_flag IN VARCHAR2
1212 ,x_amount OUT NOCOPY NUMBER
1213 )
1214 IS
1215 l_legal_entity_id NUMBER :=p_legal_entity_id;
1216 l_ledger_id gl_ledgers.ledger_id%TYPE :=p_ledger_id;
1217 l_period_names JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL :=p_period_names;
1218 l_rounding_option VARCHAR2(50) :=p_rounding_option;
1219 l_axis_set_id rg_report_axis_sets.axis_set_id%TYPE :=p_axis_set_id;
1220 l_axis_seq rg_report_axes.axis_seq%TYPE :=p_axis_seq;
1221 l_amount_per_period NUMBER;
1222 l_amount NUMBER;
1223 l_period_count NUMBER;
1224 l_period_name VARCHAR2(15);
1225 l_precision fnd_currencies.precision%TYPE;
1226 l_balancing_segment_value VARCHAR2(25) := p_balancing_segment_value; --added for CNAO Enhancement
1227 --l_internal_trx_flag VARCHAR2(1) :=p_internal_trx_flag;
1228
1229 CURSOR c_precision
1230 IS
1231 SELECT
1232 nvl(PRECISION,0)
1233 FROM
1234 fnd_currencies
1235 WHERE currency_code=(SELECT
1236 currency_code
1237 FROM
1238 gl_ledgers
1239 WHERE ledger_id=l_ledger_id
1240 );
1241
1242
1243 --this cursor is updated by lyb,delete some sentence and change the parameter legal_entity_id to COA
1244 CURSOR c_amount_per_period
1245 IS
1246 SELECT
1247 nvl(SUM(round(func_amount,decode(l_rounding_option,'R',l_precision,50))),0)
1248 FROM
1249 ja_cn_cfs_activities_all
1250 WHERE period_name=l_period_name
1251 AND legal_entity_id=l_legal_entity_id
1252 -- added for CNAO Enhancement begin
1253 AND (
1254 ( l_balancing_segment_value IS NULL
1255 OR
1256 l_balancing_segment_value = balancing_segment
1257 )
1258 AND EXISTS
1259 (
1260 SELECT *
1261 FROM Gl_Ledgers Lg,
1262 Gl_Ledger_Relationships Rs,
1263 Gl_Ledger_Norm_Seg_Vals nbsv
1264 WHERE lg.bal_seg_value_option_code='I'
1265 AND Rs.Application_Id = 101
1266 AND Lg.Ledger_Id = l_ledger_id --Using variable ledger_id
1267 AND Lg.Ledger_Id = Rs.Target_Ledger_Id
1268 AND Nvl(Lg.Complete_Flag, 'Y') = 'Y'
1269 AND nbsv.Segment_Type_Code = 'B'
1270 AND Nvl(nbsv.Status_Code, 'I') <> 'D'
1271 AND(( Rs.Relationship_Type_Code = 'NONE'
1272 AND Rs.Target_Ledger_Id = LG.Ledger_Id
1273 )
1274 OR ( Rs.Target_Ledger_Category_Code = 'ALC'
1275 AND Rs.Relationship_Type_Code IN ('SUBLEDGER', 'JOURNAL')
1276 AND Rs.Source_Ledger_Id = LG.Ledger_Id
1277 )
1278 )
1279 AND nbsv.ledger_id=Lg.Ledger_Id
1280 AND nbsv.legal_entity_id = l_legal_entity_id --Using variable legal_entity_id
1281 AND nbsv.Segment_Value = balancing_segment
1282 )
1283 )
1284 -- added for CNAO Enhancement end
1285 AND detailed_cfs_item IN (SELECT
1286 detailed_cfs_item
1287 FROM
1288 ja_cn_cfs_assignments_all
1289 WHERE chart_of_accounts_id=p_coa --updated by lyb
1290 AND axis_set_id=l_axis_set_id
1291 AND axis_seq=l_axis_seq
1292 );
1293
1294 --AND intercompany_flag LIKE decode(l_internal_trx_flag,'Y','%', 'N');
1295
1296 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1297 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1298 l_proc_name VARCHAR2(100) :='Calculate_Row_Amount';
1299
1300 BEGIN
1301 l_period_count:=l_period_names.COUNT;
1302 --log for debug
1303 IF (l_proc_level >= l_dbg_level)
1304 THEN
1305 FND_LOG.String(l_proc_level
1306 ,l_module_prefix||'.'||l_proc_name||'.begin'
1307 ,'Enter procedure'
1308 );
1309
1310 FND_LOG.String(l_proc_level
1311 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1312 ,'p_legal_entity_id '||p_legal_entity_id
1313 );
1314
1315 FND_LOG.String(l_proc_level
1316 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1317 ,'p_ledger_id '||p_ledger_id
1318 );
1319
1320 FND_LOG.String(l_proc_level
1321 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1322 ,'p_axis_set_id '||p_axis_set_id
1323 );
1324
1325 FND_LOG.String(l_proc_level
1326 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1327 ,'p_axis_seq '||p_axis_seq
1328 );
1329
1330 FND_LOG.String(l_proc_level
1331 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1332 ,'p_period_names '
1333 );
1334
1335
1336 FOR l_count IN 1..l_period_count
1337 LOOP
1338 FND_LOG.String(l_proc_level
1339 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1340 ,'p_period_names'||l_count||' '||l_period_names(l_count)
1341 );
1342 END LOOP;-- FOR l_count IN 1..l_period_count
1343
1344 FND_LOG.String(l_proc_level
1345 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1346 ,'p_rounding_option '||p_rounding_option
1347 );
1348
1349 FND_LOG.String(l_proc_level --added for CNAO Enhancement
1350 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1351 ,'p_balancing_segment_value '||p_balancing_segment_value
1352 );
1353
1354 /* FND_LOG.String(l_proc_level
1355 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1356 ,'p_internal_trx_flag '||p_internal_trx_flag
1357 );*/
1358
1359 END IF; --(l_proc_level >= l_dbg_level)
1360
1361 --To get precision of functional currecy of current gl set of book
1362 OPEN c_precision;
1363 FETCH c_precision INTO l_precision;
1364 CLOSE c_precision;
1365
1366 --Calculate amount for a specific cash flow item according to assignment of detailed cash flow items
1367 --in the 'JA_CN_CFS_ACTIVITIES_ALL' and amount of detailed cash flow items that are populated by
1368 --'Cash flow Statment - collection' program
1369
1370 --Initialize variable
1371 l_amount:=0;
1372
1373 --To get the number of periods that are involved in calculation
1374
1375
1376 FOR l_count IN 1..l_period_count
1377 LOOP
1378 l_period_name:=l_period_names(l_count);
1379 OPEN c_amount_per_period;
1380 FETCH c_amount_per_period INTO l_amount_per_period;
1381 CLOSE c_amount_per_period;
1382
1383 l_amount:=l_amount+l_amount_per_period;
1384 END LOOP; --l_count IN 1..l_period_count
1385 x_amount:=round(l_amount,l_precision);
1386
1387 --log for debug
1388 IF ( l_proc_level >= l_dbg_level)
1389 THEN
1390
1391 FND_LOG.STRING(l_proc_level
1392 ,l_module_prefix||'.'||l_proc_name||'.out'
1393 ,'x_amount '||x_amount
1394 );
1395
1396
1397 FND_LOG.STRING(l_proc_level
1398 ,l_module_prefix||'.'||l_proc_name||'.end'
1399 ,'Exit procedure'
1400 );
1401 END IF; --( l_proc_level >= l_dbg_level )
1402
1403 EXCEPTION
1404 WHEN OTHERS THEN
1405 IF (l_proc_level >= l_dbg_level)
1406 THEN
1407 FND_LOG.String(l_proc_level
1408 ,l_module_prefix||'.'||l_proc_name||'. Other_Exception '
1409 ,SQLCODE||':'||SQLERRM
1410 );
1411 END IF; --(l_proc_level >= l_dbg_level)
1412 END Calculate_Row_Amount;
1413
1414
1415 --==========================================================================
1416 -- PROCEDURE NAME:
1417 --
1418 -- Calculate_Rows_Amount Public
1419 --
1420 -- DESCRIPTION:
1421 --
1422 -- The procedure Calculate_Rows_Amount is used to calculate amount for items
1423 -- in the main part of Cash Flow Statement.
1424 --
1425 -- PARAMETERS:
1426 -- In: p_legal_entity_id Identifier of legal entity
1427 -- p_set_of_bks_id Identifier of GL set of book, a required
1428 -- parameter for FSG report
1429 -- p_axis_set_id Identifier of FSG Row Set
1430 -- p_period_names Qualified period names for cash flow statement
1431 -- calculation
1432 -- p_lastyear_period_names Qualified period names in last year for cash
1433 -- flow statement calculation
1434 -- p_rounding_option Rounding option for amount in Cash Flow statement
1435 -- p_internal_trx_flag To indicate if intercompany transactions
1436 -- should be involved in amount calculation
1437 -- of cash flow statement.
1438 --
1439 -- DESIGN REFERENCES:
1440 -- CNAO_Cashflow_Statement_Generation_TD.doc
1441 --
1442 -- CHANGE HISTORY:
1443 --
1444 -- 14-Mar-2006 Donghai Wang Created
1445 -- 14-Oct-2008 Chaoqun Wu Fix bug# 7481516
1446 --
1447 --===========================================================================
1448 PROCEDURE Calculate_Rows_Amount
1449 (p_legal_entity_id IN NUMBER
1450 ,p_ledger_id IN NUMBER
1451 ,p_coa IN NUMBER
1452 ,p_axis_set_id IN NUMBER
1453 ,p_period_names IN JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL
1454 ,p_lastyear_period_names IN JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL
1455 ,p_rounding_option IN VARCHAR2
1456 ,p_segment_override IN VARCHAR2 --added for CNAO Enhancement
1457 --,p_internal_trx_flag IN VARCHAR2
1458 )
1459 IS
1460 l_legal_entity_id NUMBER :=p_legal_entity_id;
1461 l_ledger_id gl_ledgers.ledger_id%TYPE :=p_ledger_id;
1462 l_period_names JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL :=p_period_names;
1463 l_lastyear_period_names JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL :=p_lastyear_period_names;
1464 l_rounding_option VARCHAR2(50) :=p_rounding_option;
1465 l_axis_set_id rg_report_axis_sets.axis_set_id%TYPE :=p_axis_set_id;
1466 l_axis_seq rg_report_axes.axis_seq%TYPE;
1467 l_amount NUMBER;
1468 l_lastyear_amount NUMBER;
1469 l_precision fnd_currencies.precision%TYPE;
1470 l_cal_axis_seq rg_report_axes.axis_seq%TYPE;
1471 l_segment_override VARCHAR2(100) := p_segment_override; --added for CNAO Enhancement
1472 l_balancing_segment_value JA_CN_CFS_ACTIVITIES_ALL.BALANCING_SEGMENT%TYPE; --added for CNAO Enhancement
1473 --l_internal_trx_flag VARCHAR2(1);
1474 l_cal_seq_amount NUMBER;
1475 l_cal_seq_lastyear_amount NUMBER;
1476 l_period_count NUMBER;
1477 l_lastyear_period_count NUMBER;
1478
1479 CURSOR c_precision
1480 IS
1481 SELECT
1482 nvl(PRECISION,0)
1483 FROM
1484 fnd_currencies
1485 WHERE currency_code=(SELECT
1486 currency_code
1487 FROM
1488 gl_ledgers
1489 WHERE ledger_id=l_ledger_id
1490 );
1491
1492 CURSOR c_detailed_cfs_rows
1493 IS
1494 SELECT
1495 axis_seq
1496 FROM
1497 ja_cn_cfs_row_cgs_gt
1498 WHERE axis_set_id=l_axis_set_id
1499 AND type='C'
1500 AND calculation_flag='N'
1501 FOR UPDATE;
1502
1503 CURSOR c_cal_cfs_rows
1504 IS
1505 SELECT
1506 axis_seq
1507 FROM
1508 ja_cn_cfs_row_cgs_gt
1509 WHERE axis_set_id=l_axis_set_id
1510 AND type='C'
1511 AND calculation_flag='Y'
1512 FOR UPDATE;
1513
1514 CURSOR c_calculation_lines
1515 IS
1516 SELECT
1517 operator
1518 ,cal_axis_seq
1519 ,constant
1520 FROM
1521 ja_cn_cfs_calculations_gt
1522 WHERE axis_set_id=l_axis_set_id
1523 AND axis_seq=l_axis_seq
1524 ORDER BY calculation_seq;
1525
1526 CURSOR c_cal_seq_amount
1527 IS
1528 SELECT
1529 nvl(amount,0)
1530 ,nvl(last_year_amount,0)
1531 FROM
1532 ja_cn_cfs_row_cgs_gt
1533 WHERE axis_set_id=l_axis_set_id
1534 AND axis_seq=l_cal_axis_seq;
1535
1536 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1537 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1538 l_proc_name VARCHAR2(100) :='Calculate_Rows_Amount';
1539
1540 BEGIN
1541
1542 --log for debug
1543 IF (l_proc_level >= l_dbg_level)
1544 THEN
1545 FND_LOG.String(l_proc_level
1546 ,l_module_prefix||'.'||l_proc_name||'.begin'
1547 ,'Enter procedure'
1548 );
1549
1550 FND_LOG.String(l_proc_level
1551 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1552 ,'p_legal_entity_id '||p_legal_entity_id
1553 );
1554
1555 FND_LOG.String(l_proc_level
1556 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1557 ,'p_ledger_id '||p_ledger_id
1558 );
1559
1560 FND_LOG.String(l_proc_level
1561 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1562 ,'p_axis_set_id '||p_axis_set_id
1563 );
1564
1565 FND_LOG.String(l_proc_level
1566 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1567 ,'p_period_names '
1568 );
1569
1570 l_period_count:=l_period_names.COUNT;
1571
1572 FOR l_count IN 1..l_period_count
1573 LOOP
1574 FND_LOG.String(l_proc_level
1575 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1576 ,'p_period_names'||l_count||' '||l_period_names(l_count)
1577 );
1578 END LOOP;-- FOR l_count IN 1..l_period_count
1579
1580 FND_LOG.String(l_proc_level
1581 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1582 ,'p_lastyear_period_names '
1583 );
1584
1585 l_lastyear_period_count:=l_lastyear_period_names.COUNT;
1586
1587 FOR l_count IN 1..l_lastyear_period_count
1588 LOOP
1589 FND_LOG.String(l_proc_level
1590 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1591 ,'p_lastyear_period_names'||l_count||' '||l_lastyear_period_names(l_count)
1592 );
1593 END LOOP;-- FOR l_count IN 1..l_lastyear_period_count
1594
1595 FND_LOG.String(l_proc_level
1596 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1597 ,'p_rounding_option '||p_rounding_option
1598 );
1599
1600 FND_LOG.String(l_proc_level --added for CNAO Enhancement
1601 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1602 ,'p_segment_override '||p_segment_override
1603 );
1604
1605 /* FND_LOG.String(l_proc_level
1606 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1607 ,'p_internal_trx_flag '||p_internal_trx_flag
1608 );*/
1609
1610 END IF; --(l_proc_level >= l_dbg_level)
1611 --To get precision of currecy of current gl set of book
1612 OPEN c_precision;
1613 FETCH c_precision INTO l_precision;
1614 CLOSE c_precision;
1615
1616 --added for CNAO Enhancement
1617 l_balancing_segment_value := JA_CN_UTILITY.Get_Balancing_Segment_Value(
1618 p_coa,
1619 l_segment_override);
1620
1621 --Calculate amount for rows with type 'C' and calculation flag 'N'
1622 -- in the table ja_cn_cfs_row_cgs_gt,-which are items in main
1623 --part of cash flow statment and formula are defined by Cash Flow
1624 --Statement Assignment form
1625 FOR l_detailed_cfs_row IN c_detailed_cfs_rows
1626 LOOP
1627 l_amount:=0;
1628 l_lastyear_amount:=0;
1629 JA_CN_CFS_CALCULATE_PKG.Calculate_Row_Amount(p_legal_entity_id => l_legal_entity_id
1630 ,p_ledger_id => l_ledger_id
1631 ,p_coa => p_coa --added by lyb
1632 ,p_axis_set_id => l_axis_set_id
1633 ,p_axis_seq => l_detailed_cfs_row.axis_seq
1634 ,p_period_names => l_period_names
1635 ,p_rounding_option => l_rounding_option
1636 ,p_balancing_segment_value =>l_balancing_segment_value
1637 -- ,p_internal_trx_flag => l_internal_trx_flag
1638 ,x_amount => l_amount
1639 );
1640
1641 --keep the amount to ja_cn_cfs_row_cgs_gt
1642 UPDATE
1643 ja_cn_cfs_row_cgs_gt
1644 SET
1645 amount=l_amount
1646 WHERE CURRENT OF c_detailed_cfs_rows;
1647 --If the parameter l_lastyear_period_names is not null, then calculate last year amount for current row
1648 IF l_lastyear_period_names IS NOT NULL
1649 THEN
1650
1651 JA_CN_CFS_CALCULATE_PKG.Calculate_Row_Amount(p_legal_entity_id => l_legal_entity_id
1652 ,p_ledger_id => l_ledger_id
1653 ,p_coa => p_coa --added by lyb
1654 ,p_axis_set_id => l_axis_set_id
1655 ,p_axis_seq => l_detailed_cfs_row.axis_seq
1656 ,p_period_names => l_lastyear_period_names
1657 ,p_rounding_option => l_rounding_option
1658 ,p_balancing_segment_value =>l_balancing_segment_value --added for CNAO Enhancement
1659 -- ,p_internal_trx_flag => l_internal_trx_flag
1660 ,x_amount => l_lastyear_amount
1661 );
1662
1663 --keep the last year amount to ja_cn_cfs_row_cgs_gt
1664 UPDATE
1665 ja_cn_cfs_row_cgs_gt
1666 SET
1667 last_year_amount=l_lastyear_amount
1668 WHERE CURRENT OF c_detailed_cfs_rows;
1669 END IF; --l_lastyear_period_names IS NOT NULL
1670
1671
1672 END LOOP; --FOR l_detailed_cfs_row IN c_detailed_cfs_rows
1673
1674
1675
1676
1677
1678 --Calculate amount for rows with type 'C' and calculation flag 'Y'
1679 -- in the table ja_cn_cfs_row_cgs_gt,which are items in main
1680 --part of cash flow statment. These row can calculated by other rows
1681 --with formula defined in FSG row set
1682
1683 FOR l_cal_cfs_row IN c_cal_cfs_rows
1684 LOOP
1685 l_amount:=0;
1686 l_lastyear_amount:=0;
1687
1688 --calculate amount and last year amount for current row according to relative
1689 --calculation lines in the table 'JA_CN_CFS_CALCULATION_GBLTEMP'
1690
1691 l_axis_seq:=l_cal_cfs_row.axis_seq;
1692
1693 FOR l_calculation_line IN c_calculation_lines
1694 LOOP
1695
1696 l_cal_axis_seq:=l_calculation_line.cal_axis_seq;
1697
1698 OPEN c_cal_seq_amount;
1699 FETCH c_cal_seq_amount INTO l_cal_seq_amount,l_cal_seq_lastyear_amount;
1700 CLOSE c_cal_seq_amount;
1701
1702 IF l_calculation_line.operator='+'
1703 THEN
1704 l_amount:=l_amount+l_cal_seq_amount;
1705 l_lastyear_amount:=l_lastyear_amount+l_cal_seq_lastyear_amount;
1706 ELSIF l_calculation_line.operator='-'
1707 THEN
1708 l_amount:=l_amount-l_cal_seq_amount; --Fix bug# 7481516 updated
1709 l_lastyear_amount:=l_lastyear_amount-l_cal_seq_lastyear_amount; --Fix bug# 7481516 updated
1710 END IF; --l_operator='+'
1711 END LOOP; --FOR l_calculation_line IN c_calculation_lines
1712
1713 --keep the amount and last year amount for current row in ja_cn_cfs_row_cgs_gt
1714 UPDATE
1715 ja_cn_cfs_row_cgs_gt
1716 SET
1717 amount=l_amount
1718 ,last_year_amount=l_lastyear_amount
1719 WHERE CURRENT OF c_cal_cfs_rows;
1720
1721 END LOOP; --FOR l_cal_cfs_row IN c_cal_cfs_rows
1722
1723 --log for debug
1724 IF ( l_proc_level >= l_dbg_level)
1725 THEN
1726 FND_LOG.STRING(l_proc_level
1727 ,l_module_prefix||'.'||l_proc_name||'.end'
1728 ,'Exit procedure'
1729 );
1730 END IF; --( l_proc_level >= l_dbg_level )
1731
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 IF (l_proc_level >= l_dbg_level)
1735 THEN
1736 FND_LOG.String(l_proc_level
1737 ,l_module_prefix||'.'||l_proc_name||'. Other_Exception '
1738 ,SQLCODE||':'||SQLERRM
1739 );
1740 END IF; --(l_proc_level >= l_dbg_level)
1741
1742 END Calculate_Rows_Amount;
1743
1744
1745 --==========================================================================
1746 -- PROCEDURE NAME:
1747 --
1748 -- Generate_Cfs_Xml Public
1749 --
1750 -- DESCRIPTION:
1751 --
1752 -- The procedure Generate_Cfs_Xml is to generate xml output for main part of
1753 -- cash flow statement by following format of FSG xml output.
1754 --
1755 -- PARAMETERS:
1756 -- In: p_legal_entity_id Identifier of legal entity
1757 -- p_set_of_bks_id Identifier of GL set of book, a required
1758 -- parameter for FSG report
1759 -- p_period_name GL period Name
1760 -- p_axis_set_id Identifier of FSG Row Set
1761 -- p_rounding_option Rounding option for amount in Cash Flow statement
1762 -- p_balance_type Type of balance, available value is
1763 -- 'YTD/QTD/PTD'. a required parameter for FSG
1764 -- report
1765 -- p_internal_trx_flag To indicate if intercompany transactions
1766 -- should be involved in amount calculation
1767 -- of cash flow statement.
1768 --
1769 -- DESIGN REFERENCES:
1770 -- CNAO_Cashflow_Statement_Generation_TD.doc
1771 --
1772 -- CHANGE HISTORY:
1773 --
1774 -- 14-Mar-2006 Donghai Wang Created
1775 -- 16-Dec-2008 Shujuan yan Fixed bug 7626489
1776 --===========================================================================
1777 PROCEDURE Generate_Cfs_Xml
1778 (p_legal_entity_id IN NUMBER
1779 ,p_ledger_id IN NUMBER
1780 ,p_period_name IN VARCHAR2
1781 ,p_axis_set_id IN NUMBER
1782 ,p_rounding_option IN VARCHAR2
1783 ,p_balance_type IN VARCHAR2
1784 --,p_internal_trx_flag IN VARCHAR2
1785 ,p_coa IN NUMBER
1786 ,p_segment_override IN VARCHAR2 --added for CNAO Enhancement
1787 )
1788 IS
1789 l_coa Number :=p_coa;
1790 l_thousands_separator_flag VARCHAR2(1);
1791 l_format_mask VARCHAR2(100);
1792 l_final_display_format VARCHAR2(30);
1793 l_legal_entity_id NUMBER :=p_legal_entity_id;
1794 l_ledger_id gl_ledgers.ledger_id%TYPE :=p_ledger_id;
1795 l_func_currency_code fnd_currencies.currency_code%TYPE;
1796 l_period_name gl_periods.period_name%TYPE :=p_period_name;
1797 l_axis_set_id rg_report_axis_sets.axis_set_id%TYPE :=p_axis_set_id;
1798 l_rounding_option VARCHAR2(50) :=p_rounding_option;
1799 l_balance_type VARCHAR2(50) :=p_balance_type ;
1800 l_segment_override VARCHAR2(100) :=p_segment_override; --addded for CNAO Enhancement
1801 --l_internal_trx_flag VARCHAR2(1) :=p_internal_trx_flag ;
1802 l_period_names JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL;
1803 l_lastyear_period_names JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL;
1804 l_axis_seq rg_report_axes.axis_seq%TYPE;
1805 l_type VARCHAR2(1);
1806 l_calculation_flag VARCHAR2(1);
1807 l_display_zero_amount_flag VARCHAR2(1);
1808 l_change_sign_flag VARCHAR2(1);
1809 l_display_format VARCHAR2(30);
1810 l_amount NUMBER;
1811 l_amount_display VARCHAR2(40);
1812 l_row_count NUMBER;
1813
1814 l_rowcnt VARCHAR2(50);
1815 l_lincnt VARCHAR2(50);
1816 l_colcnt VARCHAR2(50):='c1001';
1817 l_rptcnt VARCHAR2(50):='p1001';
1818
1819 l_xml_output_row XMLTYPE;
1820 l_xml_output XMLTYPE;
1821 l_xml_output_root XMLTYPE;
1822
1823 l_operator VARCHAR2(10);
1824 l_operand VARCHAR2(500);
1825 l_operands VARCHAR2(4000);
1826 l_formula VARCHAR2(4000);
1827
1828 l_cal_lincnt VARCHAR2(50);
1829 l_error_message VARCHAR2(4000);
1830 l_characterset varchar(245);
1831
1832
1833
1834 CURSOR c_axis_seq
1835 IS
1836 SELECT
1837 axis_seq
1838 FROM
1839 ja_cn_cfs_row_cgs_gt
1840 WHERE axis_set_id=l_axis_set_id
1841 AND display_flag='Y'
1842 ORDER BY axis_seq
1843 FOR UPDATE;
1844
1845 CURSOR c_rows
1846 IS
1847 SELECT
1848 axis_seq
1849 ,type
1850 ,calculation_flag
1851 ,display_zero_amount_flag
1852 ,change_sign_flag
1853 ,display_format
1854 ,amount
1855 ,rowcnt
1856 ,lincnt
1857 FROM
1858 ja_cn_cfs_row_cgs_gt
1859 WHERE axis_set_id=l_axis_set_id
1860 AND display_flag='Y'
1861 ORDER BY axis_seq;
1862
1863 CURSOR c_calculation_lines
1864 IS
1865 SELECT
1866 jcccg.operator
1867 ,jccrcg.lincnt
1868 ,jccrcg.change_sign_flag
1869 FROM
1870 ja_cn_cfs_calculations_gt jcccg
1871 ,ja_cn_cfs_row_cgs_gt jccrcg
1872 WHERE jcccg.axis_set_id=l_axis_set_id
1873 AND jcccg.axis_seq=l_axis_seq
1874 AND jcccg.axis_set_id=jccrcg.axis_set_id
1875 AND jcccg.cal_axis_seq=jccrcg.axis_seq
1876 ORDER BY jcccg.calculation_seq;
1877
1878 l_dbg_level NUMBER :=FND_LOG.G_Current_Runtime_Level;
1879 l_proc_level NUMBER :=FND_LOG.Level_Procedure;
1880 l_proc_name VARCHAR2(100) :='Generate_Cfs_Xml';
1881
1882 BEGIN
1883
1884 --log for debug
1885 IF (l_proc_level >= l_dbg_level)
1886 THEN
1887 FND_LOG.String(l_proc_level
1888 ,l_module_prefix||'.'||l_proc_name||'.begin'
1889 ,'Enter procedure'
1890 );
1891
1892 FND_LOG.String(l_proc_level
1893 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1894 ,'p_legal_entity_id '||p_legal_entity_id
1895 );
1896
1897 FND_LOG.String(l_proc_level
1898 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1899 ,'p_ledger_id '||p_ledger_id
1900 );
1901
1902 FND_LOG.String(l_proc_level
1903 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1904 ,'p_period_name '||p_period_name
1905 );
1906
1907 FND_LOG.String(l_proc_level
1908 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1909 ,'p_axis_set_id '||p_axis_set_id
1910 );
1911
1912 FND_LOG.String(l_proc_level
1913 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1914 ,'p_rounding_option '||p_rounding_option
1915 );
1916
1917 FND_LOG.String(l_proc_level
1918 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1919 ,'p_balance_type '||p_balance_type
1920 );
1921
1922 FND_LOG.String(l_proc_level --addded for CNAO Enhancement
1923 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1924 ,'p_segment_override '||p_segment_override
1925 );
1926 /* FND_LOG.String(l_proc_level
1927 ,l_module_prefix||'.'||l_proc_name||'.parameters'
1928 ,'p_internal_trx_flag '||p_internal_trx_flag
1929 );*/
1930 END IF; --(l_proc_level >= l_dbg_level)
1931
1932 --To get value of the profile 'CURRENCY: Thousands Separator' to decide if
1933 --it is need to export throusands separator for amount.
1934 l_thousands_separator_flag:=fnd_profile.value(name => 'CURRENCY:THOUSANDS_SEPARATOR');
1935
1936
1937 --To get format mask for functional currency
1938 SELECT
1939 currency_code
1940 INTO
1941 l_func_currency_code
1942 FROM
1943 gl_ledgers
1944 WHERE
1945 ledger_id=l_ledger_id ;
1946
1947 l_format_mask:=FND_CURRENCY.Get_Format_Mask(currency_code => l_func_currency_code
1948 ,field_length => 30
1949 );
1950
1951
1952
1953 --Call the procedure 'JA_CN_CFS_CALCULATE_PKG.Populate_Fomula' to popluate most detailed
1954 --calculation lines for FSG row with calculation.
1955 JA_CN_CFS_CALCULATE_PKG.Populate_Formula(p_coa =>l_coa --Fix bug# 7427067 begin
1956 ,p_axis_set_id =>l_axis_set_id
1957 );
1958
1959 --Call the procedure 'JA_CN_CFS_CALCULATE_PKG.Categorize_Rows' to categorize FSG row
1960 JA_CN_CFS_CALCULATE_PKG.Categorize_Rows(p_coa =>l_coa
1961 ,p_axis_set_id =>l_axis_set_id
1962 );
1963
1964 --Call the procedure 'JA_CN_CFS_CALCULATE_PKG.Populate_Period_Names' to populate qualified period names
1965 --by 'period name' and 'balance type' for calculation
1966 JA_CN_CFS_CALCULATE_PKG.Populate_Period_Names(p_ledger_id => l_ledger_id
1967 ,p_period_name => l_period_name
1968 ,p_balance_type => l_balance_type
1969 ,x_period_names => l_period_names
1970 );
1971
1972
1973 --Call the procedure 'JA_CN_CFS_CALCULATE_PKG.Calculate_Rows_Amount' to
1974 --calculate amount for items in the main part of Cash Flow Statement
1975 JA_CN_CFS_CALCULATE_PKG.Calculate_Rows_Amount(p_legal_entity_id =>l_legal_entity_id
1976 ,p_ledger_id =>l_ledger_id
1977 ,p_coa =>p_coa
1978 ,p_axis_set_id =>l_axis_set_id
1979 ,p_period_names =>l_period_names
1980 ,p_lastyear_period_names =>l_lastyear_period_names
1981 ,p_rounding_option =>l_rounding_option
1982 ,p_segment_override =>l_segment_override --added for CNAO Enhancement
1983 -- ,p_internal_trx_flag =>l_internal_trx_flag
1984 );
1985
1986
1987 --Generate XML output for items in the main part of Cash Flow Statement,
1988 --the output will have similar format as FSG xml output for combination intention
1989
1990 --To populate row count and line count for each row in the rowset <l_axis_set_id>
1991 l_row_count:=0;
1992
1993 OPEN c_axis_seq;
1994 FETCH c_axis_seq INTO l_axis_seq;
1995
1996 WHILE c_axis_seq%FOUND
1997 LOOP
1998 --To number the row
1999 l_row_count:=l_row_count+1;
2000
2001 --To populate rowcount and linecount for output xml like FSG
2002 l_rowcnt:='r1'||lpad(to_char(l_row_count),5,'0');
2003 l_lincnt:='l1'||lpad(to_char(l_row_count),5,'0');
2004
2005 --Update current row with row count and line count
2006 UPDATE
2007 ja_cn_cfs_row_cgs_gt
2008 SET
2009 rowcnt=l_rowcnt
2010 ,lincnt=l_lincnt
2011 WHERE CURRENT OF c_axis_seq;
2012
2013 FETCH c_axis_seq INTO l_axis_seq;
2014 END LOOP; --c_axis_seq%FOUND
2015
2016 CLOSE c_axis_seq;
2017
2018 --Retrive all rows which display_flag is 'Y' and belong to rowset 'l_rowset_id' from
2019 --the table ja_cn_cfs_row_cgs_gt by cursor c_rows in ascending order of axis_seq
2020
2021
2022 OPEN c_rows;
2023 FETCH
2024 c_rows
2025 INTO
2026 l_axis_seq
2027 ,l_type
2028 ,l_calculation_flag
2029 ,l_display_zero_amount_flag
2030 ,l_change_sign_flag
2031 ,l_display_format
2032 ,l_amount
2033 ,l_rowcnt
2034 ,l_lincnt
2035 ;
2036 WHILE c_rows%FOUND
2037 LOOP
2038
2039 --If the type of current row is 'F', then the row is a item in the
2040 --subsidiary part of cash flow statement,it will not be handle the
2041 --by this program, just skip it
2042 IF l_type='F'
2043 THEN
2044 NULL;
2045
2046 --If the type of current row is 'C', then the row is a item in the
2047 --main part of cash flow statment, it would be exported in FSG xml
2048 --output format
2049 ELSIF l_type='C'
2050 THEN
2051 --To judge if output zero for the row or not
2052 IF l_display_zero_amount_flag='N' AND NVL(l_amount,0)=0
2053 THEN
2054 l_amount:='';
2055 l_amount_display:='';
2056 ELSE
2057 --To change sign for the amount if need be
2058 IF l_change_sign_flag='Y'
2059 THEN
2060 l_amount:=nvl(l_amount,0)*-1;
2061 END IF; --l_change_sign_flag='Y'
2062
2063 --To apply format_mask to amount if any
2064 IF l_display_format IS NOT NULL
2065 THEN
2066 SELECT
2067 to_char(nvl(l_amount,0),'FM'||to_char(l_display_format,l_format_mask))
2068 INTO
2069 l_amount_display
2070 FROM dual;
2071 ELSE
2072 SELECT
2073 to_char(nvl(l_amount,0),l_format_mask)
2074 INTO
2075 l_amount_display
2076 FROM dual;
2077 END IF;-- l_display_format IS NOT NULL
2078
2079
2080 END IF; --l_display_zero_amount_flag='N' AND NVL(l_amount,0)=0
2081
2082
2083
2084
2085 --To generate xml output for current row
2086 SELECT
2087 XMLELEMENT("fsgRptLine"
2088 ,XMLATTRIBUTES(l_rptcnt AS "RptCnt"
2089 ,l_rowcnt AS "RowCnt"
2090 ,l_lincnt AS "LinCnt"
2091 )
2092 ,XMLELEMENT("fsgRptCell"
2093 ,XMLATTRIBUTES(l_colcnt AS "ColCnt"
2094 )
2095 ,nvl(l_amount_display,0)
2096 )
2097 )
2098 INTO
2099 l_xml_output_row
2100 FROM
2101 dual;
2102
2103 --To concatenate xml output
2104 IF l_xml_output IS NULL
2105 THEN
2106 l_xml_output:=l_xml_output_row;
2107 ELSE
2108 SELECT
2109 XMLCONCAT(l_xml_output
2110 ,l_xml_output_row
2111 )
2112 INTO
2113 l_xml_output
2114 FROM
2115 dual;
2116 END IF; --l_xml_output IS NULL
2117
2118
2119 --If the type of current row is 'M', then the row is calculated by
2120 --items in both main part and subsidiary part of cash flow statment
2121 --so export formula for this row in xml and 'Cash Flow Statement - combination'
2122 --program will perform calcuation for the row.
2123 --The formula format should be like '<Formula DisplayZero="Y" ChangeSign="N">
2124 --LinCnt1:ColCnt=+LinCnt2:ColCnt+LinCnt3:ColCnt </Formula>
2125 ELSIF l_type='M'
2126 THEN
2127 --Retrieve calculation lines for current row
2128 --to Populate formula as requirement of combination
2129
2130 --Variables initialization
2131 l_operands:='';
2132 l_formula:='';
2133
2134
2135 --To populater operands and operaters at right side of '=' in the formula
2136 FOR l_calculation_lines IN c_calculation_lines
2137 LOOP
2138
2139 --operator should be generated according to 'Change Sign Flag
2140 IF l_calculation_lines.change_sign_flag='Y'
2141 THEN
2142 SELECT
2143 decode(l_calculation_lines.operator,'+','-','-','+','+')
2144 INTO
2145 l_operator
2146 FROM dual;
2147 ELSE
2148 l_operator:=l_calculation_lines.operator;
2149 END IF; --_calculation_lines.change_sign_flag='Y'
2150
2151 IF l_operands IS NULL
2152 THEN
2153 l_operands:=l_operator||l_calculation_lines.lincnt||':'||l_colcnt;
2154 ELSE
2155 l_operands:=l_operands||l_operator||l_calculation_lines.lincnt||':'||l_colcnt;
2156 END IF;--l_operands IS NULL
2157 END LOOP; --FOR l_calculation_lines IN c_calculation_lines
2158
2159
2160
2161 --To populate final formula
2162 l_formula:=l_lincnt||':'||l_colcnt||'='||l_operands;
2163
2164 --To populate final display format
2165 IF l_display_format IS NOT NULL
2166 THEN
2167 SELECT
2168 'FM'||to_char(l_display_format,l_format_mask)
2169 INTO
2170 l_final_display_format
2171 FROM
2172 dual;
2173 ELSE
2174 l_final_display_format:=l_format_mask;
2175 END IF; --l_display_format IS NOT NULL
2176
2177
2178 --To generate xml output that contains formula for currrent row
2179 SELECT
2180 XMLELEMENT("Formula"
2181 ,XMLATTRIBUTES(l_display_zero_amount_flag AS "DisplayZero"
2182 ,l_change_sign_flag AS "ChangeSign"
2183 ,l_final_display_format AS "DisplayFormat"
2184 )
2185 ,l_formula
2186 )
2187 INTO
2188 l_xml_output_row
2189 FROM
2190 dual;
2191
2192 --To concatenate xml output
2193 IF l_xml_output IS NULL
2194 THEN
2195 l_xml_output:=l_xml_output_row;
2196 ELSE
2197 SELECT
2198 XMLCONCAT(l_xml_output
2199 ,l_xml_output_row
2200 )
2201 INTO
2202 l_xml_output
2203 FROM
2204 dual;
2205 END IF; --l_xml_output IS NULL
2206
2207 --If the type of current row is 'E', then the row is calculated item,but its formual
2208 --is wrong, so amount of current row cannot be calculated, an error message will be
2209 --output in xml instead.
2210 ELSIF l_type='E'
2211 THEN
2212 --Get error message from FND message directory
2213 FND_MESSAGE.Set_Name(application => 'JA'
2214 ,name =>'JA_CN_ERROR_FORMULA');
2215 l_error_message:=FND_MESSAGE.Get;
2216
2217 --To generate xml output for current row
2218 SELECT
2219 XMLELEMENT("fsgRptLine"
2220 ,XMLATTRIBUTES(l_rptcnt AS "RptCnt"
2221 ,l_rowcnt AS "RowCnt"
2222 ,l_lincnt AS "LinCnt"
2223 )
2224 ,XMLELEMENT("fsgRptCell"
2225 ,XMLATTRIBUTES(l_colcnt AS "ColCnt"
2226 )
2227 ,l_error_message
2228 )
2229 )
2230 INTO
2231 l_xml_output_row
2232 FROM
2233 dual;
2234
2235 --To concatenate xml output
2236 IF l_xml_output IS NULL
2237 THEN
2238 l_xml_output:=l_xml_output_row;
2239 ELSE
2240 SELECT
2241 XMLCONCAT(l_xml_output
2242 ,l_xml_output_row
2243 )
2244 INTO
2245 l_xml_output
2246 FROM
2247 dual;
2248 END IF; --l_xml_output IS NULL
2249
2250 END IF; --l_type='F'
2251 FETCH
2252 c_rows
2253 INTO
2254 l_axis_seq
2255 ,l_type
2256 ,l_calculation_flag
2257 ,l_display_zero_amount_flag
2258 ,l_change_sign_flag
2259 ,l_display_format
2260 ,l_amount
2261 ,l_rowcnt
2262 ,l_lincnt
2263 ;
2264
2265 END LOOP; --c_rows%FOUND
2266
2267 CLOSE c_rows;
2268
2269 --To add root node for the xml output
2270 SELECT XMLELEMENT("MasterReport"
2271 ,XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi"
2272 ,'http://www.oracle.com/fsg/2002-03-20/' AS "xmlns:fsg"
2273 ,'http://www.oracle.com/2002-03-20/fsg.xsd' AS "xsi:schemaLocation"
2274 )
2275 ,l_xml_output
2276 )
2277 INTO l_xml_output_root
2278 FROM dual;
2279
2280 --Replace fsg with fsg; for XML output of fsg segments of main part of
2281 --cash flow statemen, for avoid XML schema analyzing in XML API.
2282 IF l_xml_output_root IS NOT NULL
2283 THEN
2284 -- Updated by shujuan for bug 7626489
2285 l_characterset :=Fnd_Profile.VALUE(NAME => 'ICX_CLIENT_IANA_ENCODING');
2286 FND_FILE.put_line(FND_FILE.output,'<?xml version="1.0" encoding= '||'"'||l_characterset||'"?>');
2287 --FND_FILE.Put_Line(FND_FILE.Output,'<?xml version="1.0" encoding="utf-8" ?>');
2288 FND_FILE.Put_Line(FND_FILE.Output,REPLACE(l_xml_output_root.getclobval(),'fsgRpt','fsg:Rpt'));
2289 END IF;
2290
2291 --log for debug
2292 IF ( l_proc_level >= l_dbg_level)
2293 THEN
2294 FND_LOG.STRING(l_proc_level
2295 ,l_module_prefix||'.'||l_proc_name||'.end'
2296 ,'Exit procedure'
2297 );
2298 END IF; --( l_proc_level >= l_dbg_level )
2299
2300 EXCEPTION
2301 WHEN OTHERS THEN
2302 IF (l_proc_level >= l_dbg_level)
2303 THEN
2304 FND_LOG.String(l_proc_level
2305 ,l_module_prefix||'.'||l_proc_name||'. Other_Exception '
2306 ,SQLCODE||':'||SQLERRM
2307 );
2308 END IF; --(l_proc_level >= l_dbg_level)
2309 END Generate_Cfs_Xml;
2310
2311
2312 END JA_CN_CFS_CALCULATE_PKG;
[Home] [Help]