[Home] [Help]
PACKAGE BODY: APPS.JA_CN_CFSSE_CALCULATE_PKG
Source
1 PACKAGE BODY JA_CN_CFSSE_CALCULATE_PKG AS
2 --$Header: JACNCSCB.pls 120.2.12010000.2 2008/10/28 06:26:06 shyan ship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNCSCB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is used to implement calculation for main part of |
13 --| cash flow statement |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Populate_LastYear_Period_Names |
17 --| PROCEDURE Generate_Cfs_Xml |
18 --| |
19 --| HISTORY |
20 --| 03/22/2006 Jackey Li Created
21 --| 22/09/2008 Chaoqun Wu Updated for CNAO Enhancement |
22 --| 14/10/2008 Chaoqun Wu Fix bug# 7481478
23 --+======================================================================*/
24
25 --TYPE G_PERIOD_NAME_TBL IS TABLE OF gl_periods.period_name%TYPE INDEX BY BINARY_INTEGER;
26
27 --==== Golbal Variables ============
28 g_module_name VARCHAR2(30) := 'JA_CN_CFSSE_CALCULATE_PKG';
29 g_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
30 g_proc_level NUMBER := FND_LOG.Level_Procedure;
31 g_stmt_level NUMBER := FND_LOG.Level_Statement;
32
33 --==========================================================================
34 -- PROCEDURE NAME:
35 -- Populate_LastYear_Period_Names Private
36 --
37 -- DESCRIPTION:
38 -- It is to retrieve period names through the whole last year
39 -- depends on the parameter 'p_period_name'.
40 --
41 -- PARAMETERS:
42 -- In: p_le_id legal entity ID
43 -- p_period_name period name
44 -- x_period_names period names as G_PERIOD_NAME_TBL type
45 --
46 -- DESIGN REFERENCES:
47 -- CNAO_Cashflow_Statement_Generation(SE)_TD.doc
48 --
49 -- CHANGE HISTORY:
50 -- 03/22/2006 Jackey Li Created
51 --===========================================================================
52 PROCEDURE Populate_LastYear_Period_Names(p_ledger_id IN NUMBER
53 ,p_period_name IN VARCHAR2
54 ,x_period_names OUT NOCOPY JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL) IS
55
56 l_procedure_name VARCHAR2(30) := 'Populate_LastYear_Period_Names';
57 l_ledger_id gl_ledgers.ledger_id %TYPE := p_ledger_id;--updated by lyb
58 l_period_set_name gl_periods.period_set_name%TYPE;
59 l_period_type gl_periods.period_type%TYPE;
60 l_period_name gl_periods.period_name%TYPE := p_period_name;
61 l_cur_period_year gl_periods.period_year%TYPE;
62 l_last_period_year gl_periods.period_year%TYPE;
63 l_lastyear_period_name gl_periods.period_name%TYPE;
64
65 l_period_idx NUMBER;
66 l_period_names JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL;
67
68 -- this cursor is
69 CURSOR c_period_set
70 IS
71 SELECT
72 period_set_name
73 ,accounted_period_type
74 FROM
75 gl_ledgers
76 WHERE ledger_id=l_ledger_id;
77
78 -- this cursor is
79 CURSOR c_cur_period_year IS
80 SELECT period_year
81 FROM gl_periods
82 WHERE period_set_name = l_period_set_name
83 AND period_name = l_period_name;
84
85 -- this cursor is
86 CURSOR c_lastyear_period_names IS
87 SELECT period_name
88 FROM gl_periods
89 WHERE period_set_name = l_period_set_name
90 AND period_year = l_last_period_year
91 AND period_type = l_period_type;
92
93 BEGIN
94 --log for debug
95 IF (g_proc_level >= g_dbg_level)
96 THEN
97 FND_LOG.STRING(g_proc_level,
98 g_module_name || '.' || l_procedure_name || '.begin',
99 'begin procedure');
100 END IF; --( g_proc_level >= g_dbg_level)
101
102 --To Get current period set name and period type per gl set of book
103 OPEN c_period_set;
104 FETCH c_period_set
105 INTO l_period_set_name, l_period_type;
106 CLOSE c_period_set;
107
108 --To retrive set of period names according to parameters period_name and period_type
109
110 --Get current period year by the parameter period_name
111 OPEN c_cur_period_year;
112 FETCH c_cur_period_year
113 INTO l_cur_period_year;
114 CLOSE c_cur_period_year;
115
116 l_last_period_year := l_cur_period_year - 1;
117 l_period_idx := 0;
118
119 OPEN c_lastyear_period_names;
120 FETCH c_lastyear_period_names
121 INTO l_lastyear_period_name;
122 WHILE c_lastyear_period_names%FOUND
123 LOOP
124 l_period_idx := l_period_idx + 1;
125 l_period_names(l_period_idx) := l_lastyear_period_name;
126 FETCH c_lastyear_period_names
127 INTO l_lastyear_period_name;
128 END LOOP; -- WHILE c_lastyear_period_names%FOUND
129
130 --FND_FILE.Put_Line(FND_FILE.LOG,
131 -- 'LastYear Period amount = ' || to_char(l_period_idx));
132 x_period_names := l_period_names;
133
134 --log for debug
135 IF (g_proc_level >= g_dbg_level)
136 THEN
137 FND_LOG.STRING(g_proc_level,
138 g_module_name || '.' || l_procedure_name || '.end',
139 'end procedure');
140 END IF; --( g_proc_level >= g_dbg_level)
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 IF (FND_LOG.LEVEL_UNEXPECTED >= g_dbg_level)
145 THEN
146 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
147 g_module_name || l_procedure_name ||
148 '.OTHER_EXCEPTION',
149 SQLCODE || ':' || SQLERRM);
150 END IF;
151 RAISE;
152
153 END Populate_LastYear_Period_Names;
154
155 --==========================================================================
156 -- PROCEDURE NAME:
157 -- Generate_Cfs_Xml Public
158 --
159 -- DESCRIPTION:
160 -- It is to generate xml output for main part of cash flow statement
161 -- for small enterprise by following format of FSG xml output.
162 --
163 -- PARAMETERS:
164 -- In: p_legal_entity_id legal entity ID
165 -- p_set_of_bks_id set of books ID
166 -- p_period_name period name
167 -- p_axis_set_id axis set id
168 -- p_rounding_option rounding option
169 -- p_balance_type balance type
170 -- p_internal_trx_flag is intercompany transactions
171 --
172 -- DESIGN REFERENCES:
173 -- CNAO_Cashflow_Statement_Generation(SE)_TD.doc
174 --
175 -- CHANGE HISTORY:
176 -- 03/22/2006 Jackey Li Created
177 -- 22/09/2008 Chaoqun Wu Updated for CNAO Enhancement
178 -- 14/10/2008 Chaoqun Wu Fix bug# 7481478
179 --===========================================================================
180 PROCEDURE Generate_Cfs_Xml(p_legal_entity_id IN NUMBER
181 ,p_ledger_id IN NUMBER--updated by lyb
182 ,p_period_name IN VARCHAR2
183 ,p_axis_set_id IN NUMBER
184 ,p_rounding_option IN VARCHAR2
185 ,p_balance_type IN VARCHAR2
186 -- ,p_internal_trx_flag IN VARCHAR2
187 ,p_coa IN NUMBER
188 ,p_segment_override IN VARCHAR2 --Added for CNAO Enhancement
189 ) IS --added by lyb
190 l_coa Number :=p_coa;--added by lyb
191 l_segment_override VARCHAR2(100) :=p_segment_override; --addded for CNAO Enhancement
192 l_procedure_name VARCHAR2(30) := 'Generate_Cfs_Xml';
193 l_thousands_separator_flag VARCHAR2(1);
194 l_format_mask VARCHAR2(100);
195 l_final_display_format VARCHAR2(30);
196 l_legal_entity_id NUMBER := p_legal_entity_id;
197 -- l_set_of_bks_id gl_sets_of_books.set_of_books_id%TYPE := p_set_of_bks_id;
198 l_ledger_id gl_ledgers.ledger_id%TYPE :=p_ledger_id;
199 l_func_currency_code fnd_currencies.currency_code%TYPE;
200 l_period_name gl_periods.period_name%TYPE := p_period_name;
201 l_axis_set_id rg_report_axis_sets.axis_set_id%TYPE := p_axis_set_id;
202 l_rounding_option VARCHAR2(50) := p_rounding_option;
203 l_balance_type VARCHAR2(50) := p_balance_type;
204 --l_internal_trx_flag VARCHAR2(1) := p_internal_trx_flag;
205 l_period_names JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL;
206 l_lastyear_period_names JA_CN_CFS_CALCULATE_PKG.G_PERIOD_NAME_TBL;
207 l_axis_seq rg_report_axes.axis_seq%TYPE;
208 l_type VARCHAR2(1);
209 l_calculation_flag VARCHAR2(1);
210 l_display_zero_amount_flag VARCHAR2(1);
211 l_change_sign_flag VARCHAR2(1);
212 l_display_format VARCHAR2(30);
213 l_amount NUMBER;
214 l_amount_display VARCHAR2(40);
215 l_last_year_amount NUMBER;
216 l_last_year_amount_display VARCHAR2(40);
217 l_row_count NUMBER;
218
219 l_rowcnt VARCHAR2(50);
220 l_lincnt VARCHAR2(50);
221 l_colcnt1 VARCHAR2(50) := 'c1001';
222 l_colcnt2 VARCHAR2(50) := 'c1002';
223 l_rptcnt VARCHAR2(50) := 'p1001';
224
225 l_xml_output_row XMLTYPE;
226 l_xml_output XMLTYPE;
227 l_xml_output_root XMLTYPE;
228
229 l_operator VARCHAR2(10);
230 l_operand VARCHAR2(500);
231 l_operands1 VARCHAR2(4000);
232 l_operands2 VARCHAR2(4000);
233 l_formula VARCHAR2(4000);
234
235 l_cal_lincnt VARCHAR2(50);
236 l_error_message VARCHAR2(4000);
237
238 -- this cursor is
239 CURSOR c_axis_seq IS
240 SELECT axis_seq
241 FROM ja_cn_cfs_row_cgs_gt
242 WHERE axis_set_id = l_axis_set_id
243 AND display_flag = 'Y'
244 ORDER BY axis_seq
245 FOR UPDATE;
246
247 -- this cursor is
248 CURSOR c_rows IS
249 SELECT axis_seq
250 ,TYPE
251 ,calculation_flag
252 ,display_zero_amount_flag
253 ,change_sign_flag
254 ,display_format
255 ,amount
256 ,last_year_amount
257 ,rowcnt
258 ,lincnt
259 FROM ja_cn_cfs_row_cgs_gt
260 WHERE axis_set_id = l_axis_set_id
261 AND display_flag = 'Y'
262 ORDER BY axis_seq;
263
264 -- this cursor is
265 CURSOR c_calculation_lines(pc_axis_seg rg_report_axes.axis_seq%TYPE) IS
266 SELECT jcccg.operator
267 ,jccrcg.lincnt
268 ,jccrcg.change_sign_flag
269 FROM ja_cn_cfs_calculations_gt jcccg
270 ,ja_cn_cfs_row_cgs_gt jccrcg
271 WHERE jcccg.axis_set_id = l_axis_set_id
272 AND jcccg.axis_seq = pc_axis_seg
273 AND jcccg.axis_set_id = jccrcg.axis_set_id
274 AND jcccg.cal_axis_seq = jccrcg.axis_seq
275 ORDER BY jcccg.calculation_seq;
276
277 BEGIN
278 --log for debug
279 IF (g_proc_level >= g_dbg_level)
280 THEN
281 FND_LOG.STRING(g_proc_level,
282 g_module_name || '.' || l_procedure_name || '.begin',
283 'begin procedure');
284 FND_LOG.STRING(g_proc_level,
285 g_module_name || '.' || l_procedure_name ||
286 '.p_legal_entity_id is',
287 p_legal_entity_id);
288 FND_LOG.STRING(g_proc_level,
289 g_module_name || '.' || l_procedure_name ||
290 '.p_ledger_id is',
291 p_ledger_id);
292 FND_LOG.STRING(g_proc_level,
293 g_module_name || '.' || l_procedure_name ||
294 '.p_period_name is',
295 p_period_name);
296 FND_LOG.STRING(g_proc_level,
297 g_module_name || '.' || l_procedure_name ||
298 '.p_axis_set_id is',
299 p_axis_set_id);
300 FND_LOG.STRING(g_proc_level,
301 g_module_name || '.' || l_procedure_name ||
302 '.p_rounding_option is',
303 p_rounding_option);
304 FND_LOG.STRING(g_proc_level,
305 g_module_name || '.' || l_procedure_name ||
306 '.p_balance_type is',
307 p_balance_type);
308
309 FND_LOG.STRING(g_proc_level,
310 g_module_name || '.' || l_procedure_name ||
311 '.p_coa',
312 p_coa);
313
314 FND_LOG.STRING(g_proc_level, --Added for CNAO Enhancement
315 g_module_name || '.' || l_segment_override ||
316 '.p_segment_override',
317 p_segment_override);
318 END IF; --( g_proc_level >= g_dbg_level)
319
320 --To get value of the profile 'CURRENCY: Thousands Separator' to decide if
321 --it is need to export throusands separator for amount.
322 l_thousands_separator_flag := fnd_profile.VALUE(NAME => 'CURRENCY:THOUSANDS_SEPARATOR');
323
324 --To get format mask for functional currency
325 --updated by lyb
326 SELECT
327 currency_code
328 INTO
329 l_func_currency_code
330 FROM
331 gl_ledgers
332 WHERE
333 ledger_id=l_ledger_id ;
334
335 l_format_mask := FND_CURRENCY.Get_Format_Mask(currency_code => l_func_currency_code,
336 field_length => 30);
337
338 --Fix bug# 7481478 added begin
339 --Call the procedure 'JA_CN_CFS_CALCULATE_PKG.Populate_Fomula' to popluate most detailed
340 --calculation lines for FSG row with calculation.
341 JA_CN_CFS_CALCULATE_PKG.Populate_Formula(p_coa =>p_coa
342 ,p_axis_set_id =>l_axis_set_id
343 );
344 --Fix bug# 7481478 added end
345
346 --FND_FILE.Put_Line(FND_FILE.LOG,
347 -- 'Categorize_Rows start');
348 --Call the procedure 'JA_CN_CFS_CALCULATE_PKG.Categorize_Rows' to categorize FSG row
349 JA_CN_CFS_CALCULATE_PKG.Categorize_Rows(p_coa => p_coa,
350 p_axis_set_id => l_axis_set_id);
351
352 --FND_FILE.Put_Line(FND_FILE.LOG,
353 -- 'Categorize_Rows end');
354
355 --log for debug
356 IF (g_stmt_level >= g_dbg_level)
357 THEN
358 FND_LOG.STRING(g_stmt_level,
359 g_module_name || '.' || l_procedure_name ||
360 '.call JA_CN_CFS_CALCULATE_PKG.Categorize_Rows',
361 'Successfully');
362 END IF; --( g_stmt_level >= g_dbg_level)
363
364 --Call the procedure 'JA_CN_CFS_CALCULATE_PKG.Populate_Period_Names'
365 -- to populate qualified period names by 'period name' and
366 -- 'balance type' for calculation
367 --FND_FILE.Put_Line(FND_FILE.LOG,
368 -- 'Populate_Period_Names start');
369 JA_CN_CFS_CALCULATE_PKG.Populate_Period_Names(p_ledger_id =>l_ledger_id,--updated by lyb
370 p_period_name => l_period_name,
371 p_balance_type => l_balance_type,
372 x_period_names => l_period_names);
373 --FND_FILE.Put_Line(FND_FILE.LOG,
374 -- 'Populate_Period_Names end');
375
376 --log for debug
377 IF (g_stmt_level >= g_dbg_level)
378 THEN
379 FND_LOG.STRING(g_stmt_level,
380 g_module_name || '.' || l_procedure_name ||
381 '.call JA_CN_CFS_CALCULATE_PKG.Populate_Period_Names',
382 'Successfully');
383 END IF; --( g_stmt_level >= g_dbg_level)
384
385 --Call the procedure 'JA_CN_CFSSE_CALCULATE_PKG.Populate_LastYear_Period_Names'
386 -- to populate qualified period names belonging to last fiscal year
387 -- by 'period name' for calculation
388 --FND_FILE.Put_Line(FND_FILE.LOG,
389 -- 'Populate_LastYear_Period_Names start');
390 JA_CN_CFSSE_CALCULATE_PKG.Populate_LastYear_Period_Names(p_ledger_id => l_ledger_id,--updted by lyb
391 p_period_name => l_period_name,
392 x_period_names => l_lastyear_period_names);
393
394 --FND_FILE.Put_Line(FND_FILE.LOG,
395 -- 'Populate_LastYear_Period_Names end');
396
397 --Call the procedure 'JA_CN_CFS_CALCULATE_PKG.Calculate_Rows_Amount' to
398 --calculate amount for items in the main part of Cash Flow Statement
399 --FND_FILE.Put_Line(FND_FILE.LOG,
400 -- 'Calculate_Rows_Amount start');
401 JA_CN_CFS_CALCULATE_PKG.Calculate_Rows_Amount(p_legal_entity_id => l_legal_entity_id,
402 p_ledger_id => l_ledger_id,--updated by lyb
403 p_coa => l_coa,
404 p_axis_set_id => l_axis_set_id,
405 p_period_names => l_period_names,
406 p_lastyear_period_names => l_lastyear_period_names,
407 p_rounding_option => l_rounding_option,
408 p_segment_override =>l_segment_override ); --added for CNAO Enhancement
409 -- p_internal_trx_flag => l_internal_trx_flag
410
411
412 --FND_FILE.Put_Line(FND_FILE.LOG,
413 -- 'Calculate_Rows_Amount end');
414
415 --log for debug
416 IF (g_stmt_level >= g_dbg_level)
417 THEN
418 FND_LOG.STRING(g_stmt_level,
419 g_module_name || '.' || l_procedure_name ||
420 '.call JA_CN_CFS_CALCULATE_PKG.Calculate_Rows_Amount',
421 'Successfully');
422 END IF; --( g_stmt_level >= g_dbg_level)
423
424 --To populate row count and line count for each row in the rowset <l_axis_set_id>
425 l_row_count := 0;
426
427 FOR rec_axis_seq IN c_axis_seq
428 LOOP
429 --To number the row
430 l_row_count := l_row_count + 1;
431
432 --To populate rowcount and linecount for output xml like FSG
433 l_rowcnt := 'r1' || lpad(to_char(l_row_count),
434 5,
435 '0');
436 l_lincnt := 'l1' || lpad(to_char(l_row_count),
437 5,
438 '0');
439
440 --Update current row with row count and line count
441 UPDATE ja_cn_cfs_row_cgs_gt
442 SET rowcnt = l_rowcnt,
443 lincnt = l_lincnt
444 WHERE CURRENT OF c_axis_seq;
445
446 END LOOP;
447
448 --FND_FILE.Put_Line(FND_FILE.LOG,
449 -- 'update row number');
450
451 --Retrive all rows which display_flag is 'Y' and belong to rowset 'l_rowset_id' from
452 --the table JA_CN_CFS_ROW_CGS_gt by cursor c_rows in ascending order of axis_seq
453 FOR rec_rows IN c_rows
454 LOOP
455
456 --log for debug
457 IF (g_stmt_level >= g_dbg_level)
458 THEN
459 FND_LOG.STRING(g_stmt_level,
460 g_module_name || '.' || l_procedure_name ||
461 '.operate each row',
462 rec_rows.TYPE);
463 END IF; --( g_stmt_level >= g_dbg_level)
464
465 --If the type of current row is 'F', then the row is a item in the
466 --subsidiary part of cash flow statement,it will not be handle the
467 --by this program, just skip it
468 IF rec_rows.TYPE = 'F'
469 THEN
470 NULL;
471
472 --If the type of current row is 'C', then the row is a item in the
473 --main part of cash flow statment, it would be exported in FSG xml
474 --output format
475 ELSIF rec_rows.TYPE = 'C'
476 THEN
477 --To judge if output zero for the row or not
478 IF rec_rows.display_zero_amount_flag = 'N'
479 THEN
480 IF NVL(rec_rows.amount,
481 0) = 0
482 THEN
483 rec_rows.amount := '';
484 END IF;
485
486 IF NVL(rec_rows.last_year_amount,
487 0) = 0
488 THEN
489 rec_rows.last_year_amount := '';
490 END IF;
491
492 ELSE
493 --To change sign for the amount if need
494 IF rec_rows.change_sign_flag = 'Y'
495 THEN
496 rec_rows.amount := nvl(rec_rows.amount,
497 0) * -1;
498 rec_rows.last_year_amount := nvl(rec_rows.last_year_amount,
499 0) * -1;
500 END IF; --l_change_sign_flag='Y'
501
502 --To apply format_mask to amount if any
503 IF rec_rows.display_format IS NOT NULL
504 THEN
505 SELECT to_char(nvl(rec_rows.amount,
506 0),
507 'FM' || to_char(rec_rows.display_format,
508 l_format_mask))
509 INTO l_amount_display
510 FROM dual;
511
512 SELECT to_char(nvl(rec_rows.last_year_amount,
513 0),
514 'FM' || to_char(rec_rows.display_format,
515 l_format_mask))
516 INTO l_last_year_amount_display
517 FROM dual;
518
519 ELSE
520 SELECT to_char(nvl(rec_rows.amount,
521 0),
522 l_format_mask)
523 INTO l_amount_display
524 FROM dual;
525
526 SELECT to_char(nvl(rec_rows.last_year_amount,
527 0),
528 l_format_mask)
529 INTO l_last_year_amount_display
530 FROM dual;
531 END IF; -- rec_rows.display_format IS NOT NULL
532
533 END IF; --l_display_zero_amount_flag='N' AND NVL(l_amount,0)=0
534
535 --To generate xml output for current row
536 SELECT XMLELEMENT("fsggRptLine",
537 XMLATTRIBUTES(l_rptcnt AS "RptCnt",
538 rec_rows.rowcnt AS "RowCnt",
539 rec_rows.lincnt AS "LinCnt"),
540 XMLELEMENT("fsggRptCell",
541 XMLATTRIBUTES(l_colcnt1 AS "ColCnt"),
542 nvl(l_last_year_amount_display,
543 0)),
544 XMLELEMENT("fsggRptCell",
545 XMLATTRIBUTES(l_colcnt2 AS "ColCnt"),
546 nvl(l_amount_display,
547 0)))
548 INTO l_xml_output_row
549 FROM dual;
550
551 --log for debug
552 IF (g_stmt_level >= g_dbg_level)
553 THEN
554 FND_LOG.STRING(g_stmt_level,
555 g_module_name || '.' || l_procedure_name ||
556 '.row detail',
557 l_xml_output_row.getclobval());
558 END IF; --( g_stmt_level >= g_dbg_level)
559
560 --To concatenate xml output
561 IF l_xml_output IS NULL
562 THEN
563 l_xml_output := l_xml_output_row;
564 ELSE
565 SELECT XMLCONCAT(l_xml_output,
566 l_xml_output_row)
567 INTO l_xml_output
568 FROM dual;
569 END IF; --l_xml_output IS NULL
570
571 --If the type of current row is 'M', then the row is calculated by
572 --items in both main part and subsidiary part of cash flow statment
573 --so export formula for this row in xml and 'Cash Flow Statement - combination'
574 --program will perform calcuation for the row.
575 --The formula format should be like '<Formula DisplayZero="Y" ChangeSign="N">
576 --LinCnt1:ColCnt=+LinCnt2:ColCnt+LinCnt3:ColCnt </Formula>
577 ELSIF rec_rows.TYPE = 'M'
578 THEN
579 --Retrieve calculation lines for current row
580 --to Populate formula as requirement of combination
581
582 --Variables initialization
583 l_operands1 := '';
584 l_operands2 := '';
585 l_formula := '';
586
587 --To populater operands and operaters at right side of '=' in the formula
588 FOR rec_calculation_lines IN c_calculation_lines(rec_rows.axis_seq)
589 LOOP
590
591 --operator should be generated according to 'Change Sign Flag
592 IF rec_calculation_lines.change_sign_flag = 'Y'
593 THEN
594 SELECT decode(rec_calculation_lines.operator,
595 '+',
596 '-',
597 '-',
598 '+',
599 '+')
600 INTO l_operator
601 FROM dual;
602 ELSE
603 l_operator := rec_calculation_lines.operator;
604 END IF; --_calculation_lines.change_sign_flag='Y'
605
606 IF l_operands1 IS NULL
607 THEN
608 l_operands1 := rec_calculation_lines.operator ||
609 rec_calculation_lines.lincnt || ':' || l_colcnt1;
610 ELSE
611 l_operands1 := l_operands1 || rec_calculation_lines.operator ||
612 rec_calculation_lines.lincnt || ':' || l_colcnt1;
613 END IF; --l_operands1 IS NULL
614
615 IF l_operands2 IS NULL
616 THEN
617 l_operands2 := rec_calculation_lines.operator ||
618 rec_calculation_lines.lincnt || ':' || l_colcnt2;
619 ELSE
620 l_operands2 := l_operands2 || rec_calculation_lines.operator ||
621 rec_calculation_lines.lincnt || ':' || l_colcnt2;
622 END IF; --l_operands2 IS NULL
623
624 END LOOP; --For rec_calculation_lines
625
626 --To populate final display format
627 IF rec_rows.display_format IS NOT NULL
628 THEN
629 SELECT 'FM' || to_char(rec_rows.display_format,
630 l_format_mask)
631 INTO l_final_display_format
632 FROM dual;
633 ELSE
634 l_final_display_format := l_format_mask;
635 END IF; --l_display_format IS NOT NULL
636
637 --To populate final formula for last year amount
638 l_formula := rec_rows.lincnt || ':' || l_colcnt1 || '=' ||
639 l_operands1;
640
641 --To generate xml output that contains formula for currrent row
642 SELECT XMLELEMENT("Formula",
643 XMLATTRIBUTES(rec_rows.display_zero_amount_flag AS
644 "DisplayZero",
645 rec_rows.change_sign_flag AS
646 "ChangeSign",
647 l_final_display_format AS
648 "DisplayFormat"),
649 l_formula)
650 INTO l_xml_output_row
651 FROM dual;
652
653 --log for debug
654 IF (g_stmt_level >= g_dbg_level)
655 THEN
656 FND_LOG.STRING(g_stmt_level,
657 g_module_name || '.' || l_procedure_name ||
658 '.row detail',
659 l_xml_output_row.getclobval());
660 END IF; --( g_stmt_level >= g_dbg_level)
661
662 --To concatenate xml output
663 IF l_xml_output IS NULL
664 THEN
665 l_xml_output := l_xml_output_row;
666 ELSE
667 SELECT XMLCONCAT(l_xml_output,
668 l_xml_output_row)
669 INTO l_xml_output
670 FROM dual;
671 END IF; --l_xml_output IS NULL
672
673 --To populate final formula for amount
674 l_formula := rec_rows.lincnt || ':' || l_colcnt2 || '=' ||
675 l_operands2;
676
677 --To generate xml output that contains formula for currrent row
678 SELECT XMLELEMENT("Formula",
679 XMLATTRIBUTES(rec_rows.display_zero_amount_flag AS
680 "DisplayZero",
681 rec_rows.change_sign_flag AS
682 "ChangeSign",
683 l_final_display_format AS
684 "DisplayFormat"),
685 l_formula)
686 INTO l_xml_output_row
687 FROM dual;
688
689 --log for debug
690 IF (g_stmt_level >= g_dbg_level)
691 THEN
692 FND_LOG.STRING(g_stmt_level,
693 g_module_name || '.' || l_procedure_name ||
694 '.row detail',
695 l_xml_output_row.getclobval());
696 END IF; --( g_stmt_level >= g_dbg_level)
697
698 --To concatenate xml output
699 IF l_xml_output IS NULL
700 THEN
701 l_xml_output := l_xml_output_row;
702 ELSE
703 SELECT XMLCONCAT(l_xml_output,
704 l_xml_output_row)
705 INTO l_xml_output
706 FROM dual;
707 END IF; --l_xml_output IS NULL
708
709 --If the type of current row is 'E', then the row is calculated item,but its formual
710 --is wrong, so amount of current row cannot be calculated, an error message will be
711 --output in xml instead.
712 ELSIF rec_rows.TYPE = 'E'
713 THEN
714 --Get error message from FND message directory
715 -- TODO:
716 l_error_message := 'wrong fomula exists';
717
718 --To generate xml output for current row
719 SELECT XMLELEMENT("fsggRptLine",
720 XMLATTRIBUTES(l_rptcnt AS "RptCnt",
721 rec_rows.rowcnt AS "RowCnt",
722 rec_rows.lincnt AS "LinCnt"),
723 XMLELEMENT("fsggRptCell",
724 XMLATTRIBUTES(l_colcnt1 AS "ColCnt"),
725 l_error_message),
726 XMLELEMENT("fsggRptCell",
727 XMLATTRIBUTES(l_colcnt2 AS "ColCnt"),
728 l_error_message))
729 INTO l_xml_output_row
730 FROM dual;
731
732 --To concatenate xml output
733 IF l_xml_output IS NULL
734 THEN
735 l_xml_output := l_xml_output_row;
736 ELSE
737 SELECT XMLCONCAT(l_xml_output,
738 l_xml_output_row)
739 INTO l_xml_output
740 FROM dual;
741
742 END IF; --l_xml_output IS NULL
743
744 END IF; --l_type='F'
745
746 END LOOP; --for rec_rows
747
748 --FND_FILE.Put_Line(FND_FILE.LOG,
749 -- 'output all rows');
750
751 --To add root node for the xml output
752 SELECT XMLELEMENT("MasterReport",
753 XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-instance' AS
754 "xmlns:xsi",
755 'http://www.oracle.com/fsg/2002-03-20/' AS
756 "xmlns:fsg",
757 'http://www.oracle.com/2002-03-20/fsg.xsd' AS
758 "xsi:schemaLocation"),
759 l_xml_output)
760 INTO l_xml_output_root
761 FROM dual;
762
763 --FND_FILE.Put_Line(FND_FILE.LOG,
764 -- REPLACE(l_xml_output_root.getclobval(),
765 -- 'fsgg',
766 -- 'fsg:'));
767
768 FND_FILE.Put_Line(FND_FILE.Output,
769 REPLACE(l_xml_output_root.getclobval(),
770 'fsgg',
771 'fsg:'));
772
773 --log for debug
774 IF (g_proc_level >= g_dbg_level)
775 THEN
776 FND_LOG.STRING(g_proc_level,
777 g_module_name || '.' || l_procedure_name || '.end',
778 'end procedure');
779 END IF; --( g_proc_level >= g_dbg_level)
780
781 EXCEPTION
782 WHEN OTHERS THEN
783 IF (FND_LOG.LEVEL_UNEXPECTED >= g_dbg_level)
784 THEN
785 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
786 g_module_name || l_procedure_name ||
787 '.OTHER_EXCEPTION',
788 SQLCODE || ':' || SQLERRM);
789 END IF;
790 RAISE;
791
792 END Generate_Cfs_Xml;
793
794 END JA_CN_CFSSE_CALCULATE_PKG;