当前位置: 代码迷 >> SQL >> EBS中二次开发FSG表格2(SQL)
  详细解决方案

EBS中二次开发FSG表格2(SQL)

热度:33   发布时间:2016-05-05 12:28:50.0
EBS中二次开发FSG报表2(SQL)

环境: EBS R12, jasperreport, iReport


以资产负债表为例描述下EBS中二次开发FSG报表。


1,定义FSG报表(网上资料很多)

Responsibility: GL

定义Row Set -> 定义Column Set -> 定义FSG Report

Row Set:按顺序定义好各个Row Name(Line Items),可以使用Account Assignments或者Caculations;一般Account Assignment在Account段变化,其最小值和最大值的变化会关联到GL_COMBINATIONS_CODE和GL_BALANCES表中;Caculation会引用序号比自己小的Row,当Low和High相同时指定为当Row。

Column Set:一般可以不定义Caculations和Account Assignments,这些在Row Set中已经定义好;选择合适的Amount Type,比如:年初数(BAL-Actual (FY Start)),期末数(YTD-Actual)。


2,定义一个Package,将report中需要的数据保存到一个临时表里,这里的关键是FSG报表计算的结果数据如何使用SQL实现,这里参考了xiedongwen的帖子,并在R12上通过。

下面给出CURSOR定义,指定了四个参数:ledger, legal entity, period, row set。SQL中使用动态取得的ledger对应的COAt的每个段的最小和最大值,当Row Set中的某个段为空的时候,动态设置为最小和最大值。

  CURSOR cur_gl_balance_sheet(v_ledger NUMBER, v_legal_entity VARCHAR2, v_period VARCHAR2, v_row_set_id NUMBER) IS    SELECT ax.axis_seq          ,ax.description          ,decode(ax.change_sign_flag,                  'Y',                  -decode(rra4.axis_set_id,                          NULL,                          b.year_begin_balance,                          rra4.year_begin_balance),                  decode(rra4.axis_set_id,                         NULL,                         b.year_begin_balance,                         rra4.year_begin_balance)) year_begin_balance          ,decode(ax.change_sign_flag,                  'Y',                  -decode(rra4.axis_set_id,                          NULL,                          b.period_end_balance,                          rra4.period_end_balance),                  decode(rra4.axis_set_id,                         NULL,                         b.period_end_balance,                         rra4.period_end_balance)) period_end_balance      FROM (SELECT a.axis_set_id                  ,a.axis_seq                  ,SUM(decode(a.operator,                              '-',                              -a.year_begin_balance,                              a.year_begin_balance)) year_begin_balance                  ,SUM(decode(a.operator,                              '-',                              -a.period_end_balance,                              a.period_end_balance)) period_end_balance              FROM (SELECT rrc6.axis_set_id                          ,rrc6.axis_seq                          ,rrc6.calculation_seq                          ,rrc6.operator                          ,rra3.year_begin_balance                          ,rra3.period_end_balance                      FROM (SELECT a.axis_set_id                                  ,a.axis_seq                                  ,SUM(decode(a.operation_sign,                                              '+',                                              a.year_begin_balance,                                              '-',                                              -a.year_begin_balance,                                              a.year_begin_balance)) year_begin_balance                                  ,SUM(decode(a.operation_sign,                                              '+',                                              a.period_end_balance,                                              '-',                                              -a.period_end_balance,                                              a.period_end_balance)) period_end_balance                              FROM (SELECT gcc.code_combination_id account_id                                          ,gcc.summary_flag summary_flag                                          ,rra2.range_mode summary                                          ,rra2.axis_set_id                                          ,rra2.axis_seq                                          ,rra2.sign operation_sign                                          ,gcc.segment1 || '.' || gcc.segment2 || '.' ||                                           gcc.segment3 || '.' || gcc.segment4 || '.' ||                                           gcc.segment5 || '.' || gcc.segment6 user_account                                          ,gb.period_name period_name                                          ,nvl(gb.begin_balance_dr,                                               0) + nvl(gb.period_net_dr,                                                        0) -                                           nvl(gb.begin_balance_cr,                                               0) - nvl(gb.period_net_cr,                                                        0) period_end_balance                                          ,(SELECT SUM(nvl(gb1.begin_balance_dr,                                                           0) - nvl(gb1.begin_balance_cr,                                                                    0)) year_begin_balance                                              FROM gl.gl_balances gb1                                             WHERE gb1.period_year = gb.period_year                                               AND gb1.period_num = 1                                               AND gb1.currency_code =                                                   gb.currency_code                                               AND gb1.code_combination_id =                                                   gb.code_combination_id) year_begin_balance                                      FROM gl.gl_balances gb                                          ,gl.gl_code_combinations gcc                                          ,(SELECT rra.axis_set_id                                                  ,rra.axis_seq                                                  ,rra.sign                                                  ,rra.range_mode                                                  ,rra.segment1_low                                                  ,rra.segment1_high                                                  ,rra.segment2_low                                                  ,rra.segment2_high                                                  ,rra.segment3_low                                                  ,rra.segment3_high                                                  ,rra.segment4_low                                                  ,rra.segment4_high                                                  ,rra.segment5_low                                                  ,rra.segment5_high                                                  ,rra.segment6_low                                                  ,rra.segment6_high                                                  ,rra.segment7_low                                                  ,rra.segment7_high                                                  ,rra.segment8_low                                                  ,rra.segment8_high                                                  ,rra.segment9_low                                                  ,rra.segment9_high                                              FROM rg.rg_report_axis_contents rra                                             WHERE rra.axis_set_id = v_row_set_id) rra2                                     WHERE gb.code_combination_id =                                           gcc.code_combination_id                                          --and gb.currency_code = 'CNY'                                       AND gb.actual_flag = 'A'                                       AND gb.period_name = v_period_name                                       AND gb.ledger_id = v_ledger_id                                       AND gcc.segment1 = v_legal_entity                                       AND gcc.segment2 BETWEEN                                           nvl(rra2.segment2_low,                                               wk_segment2_min) AND                                           nvl(rra2.segment2_high,                                               wk_segment2_max)                                       AND gcc.segment3 BETWEEN                                           nvl(rra2.segment3_low,                                               wk_segment3_min) AND                                           nvl(rra2.segment3_high,                                               wk_segment3_max)                                       AND gcc.segment4 BETWEEN                                           nvl(rra2.segment4_low,                                               wk_segment4_min) AND                                           nvl(rra2.segment4_high,                                               wk_segment4_max)                                       AND gcc.segment5 BETWEEN                                           nvl(rra2.segment5_low,                                               wk_segment5_min) AND                                           nvl(rra2.segment5_high,                                               wk_segment5_max)                                       AND gcc.segment6 BETWEEN                                           nvl(rra2.segment6_low,                                               wk_segment6_min) AND                                           nvl(rra2.segment6_high,                                               wk_segment6_max)                                       AND gcc.segment7 BETWEEN                                           nvl(rra2.segment7_low,                                               wk_segment7_min) AND                                           nvl(rra2.segment7_high,                                               wk_segment7_max)                                       AND gcc.segment8 BETWEEN                                           nvl(rra2.segment8_low,                                               wk_segment8_min) AND                                           nvl(rra2.segment8_high,                                               wk_segment8_max)                                       AND gcc.segment9 BETWEEN                                           nvl(rra2.segment9_low,                                               wk_segment9_min) AND                                           nvl(rra2.segment9_high,                                               wk_segment9_max)                                       AND gcc.summary_flag = rra2.range_mode) a                             GROUP BY a.axis_set_id                                     ,a.axis_seq) rra3                          ,(SELECT rrc4.axis_set_id                                  ,rrc4.axis_seq                                  ,rrc4.calculation_seq                                  ,rrc4.operator                                  ,decode(rrc5.axis_seq_low,                                          NULL,                                          rrc4.axis_seq_low,                                          rrc5.axis_seq_low) axis_seq_low                              FROM (SELECT rrc2.axis_set_id                                          ,rrc2.axis_seq                                          ,rrc2.calculation_seq                                          ,rrc2.operator                                          ,decode(rrc3.axis_seq_low,                                                  NULL,                                                  rrc2.axis_seq_low,                                                  rrc3.axis_seq_low) axis_seq_low                                      FROM (SELECT rrc0.axis_set_id                                                  ,rrc0.axis_seq                                                  ,rrc0.calculation_seq                                                  ,rrc0.operator                                                  ,ax3.axis_seq axis_seq_low                                              FROM (SELECT rrc.axis_set_id                                                          ,rrc.axis_seq                                                          ,rrc.calculation_seq                                                          ,rrc.operator                                                          ,decode(rrc.axis_seq_low,                                                                  NULL,                                                                  ax2.axis_seq,                                                                  rrc.axis_seq_low) axis_seq_low                                                          ,rrc.axis_seq_high                                                      FROM rg.rg_report_calculations rrc                                                          ,rg.rg_report_axes         ax2                                                     WHERE rrc.axis_set_id =                                                           ax2.axis_set_id(+)                                                       AND rrc.axis_name_low =                                                           ax2.axis_name(+)) rrc0                                                  ,rg.rg_report_axes ax3                                             WHERE ax3.axis_set_id =                                                   rrc0.axis_set_id                                               AND ax3.axis_seq BETWEEN                                                   rrc0.axis_seq_low AND                                                   rrc0.axis_seq_high) rrc2                                          ,(SELECT rrc0.axis_set_id                                                  ,rrc0.axis_seq                                                  ,rrc0.calculation_seq                                                  ,rrc0.operator                                                  ,ax3.axis_seq axis_seq_low                                              FROM (SELECT rrc.axis_set_id                                                          ,rrc.axis_seq                                                          ,rrc.calculation_seq                                                          ,rrc.operator                                                          ,decode(rrc.axis_seq_low,                                                                  NULL,                                                                  ax2.axis_seq,                                                                  rrc.axis_seq_low) axis_seq_low                                                          ,rrc.axis_seq_high                                                      FROM rg.rg_report_calculations rrc                                                          ,rg.rg_report_axes         ax2                                                     WHERE rrc.axis_set_id =                                                           ax2.axis_set_id(+)                                                       AND rrc.axis_name_low =                                                           ax2.axis_name(+)) rrc0                                                  ,rg.rg_report_axes ax3                                             WHERE ax3.axis_set_id =                                                   rrc0.axis_set_id                                               AND ax3.axis_seq BETWEEN                                                   rrc0.axis_seq_low AND                                                   rrc0.axis_seq_high) rrc3                                     WHERE rrc2.axis_set_id = rrc3.axis_set_id(+)                                       AND rrc2.axis_seq_low = rrc3.axis_seq(+)) rrc4                                  ,(SELECT rrc2.axis_set_id                                          ,rrc2.axis_seq                                          ,rrc2.calculation_seq                                          ,rrc2.operator                                          ,decode(rrc3.axis_seq_low,                                                  NULL,                                                  rrc2.axis_seq_low,                                                  rrc3.axis_seq_low) axis_seq_low                                      FROM (SELECT rrc0.axis_set_id                                                  ,rrc0.axis_seq                                                  ,rrc0.calculation_seq                                                  ,rrc0.operator                                                  ,ax3.axis_seq axis_seq_low                                              FROM (SELECT rrc.axis_set_id                                                          ,rrc.axis_seq                                                          ,rrc.calculation_seq                                                          ,rrc.operator                                                          ,decode(rrc.axis_seq_low,                                                                  NULL,                                                                  ax2.axis_seq,                                                                  rrc.axis_seq_low) axis_seq_low                                                          ,rrc.axis_seq_high                                                      FROM rg.rg_report_calculations rrc                                                          ,rg.rg_report_axes         ax2                                                     WHERE rrc.axis_set_id =                                                           ax2.axis_set_id(+)                                                       AND rrc.axis_name_low =                                                           ax2.axis_name(+)) rrc0                                                  ,rg.rg_report_axes ax3                                             WHERE ax3.axis_set_id =                                                   rrc0.axis_set_id                                               AND ax3.axis_seq BETWEEN                                                   rrc0.axis_seq_low AND                                                   rrc0.axis_seq_high) rrc2                                          ,(SELECT rrc0.axis_set_id                                                  ,rrc0.axis_seq                                                  ,rrc0.calculation_seq                                                  ,rrc0.operator                                                  ,ax3.axis_seq axis_seq_low                                              FROM (SELECT rrc.axis_set_id                                                          ,rrc.axis_seq                                                          ,rrc.calculation_seq                                                          ,rrc.operator                                                          ,decode(rrc.axis_seq_low,                                                                  NULL,                                                                  ax2.axis_seq,                                                                  rrc.axis_seq_low) axis_seq_low                                                          ,rrc.axis_seq_high                                                      FROM rg.rg_report_calculations rrc                                                          ,rg.rg_report_axes         ax2                                                     WHERE rrc.axis_set_id =                                                           ax2.axis_set_id(+)                                                       AND rrc.axis_name_low =                                                           ax2.axis_name(+)) rrc0                                                  ,rg.rg_report_axes ax3                                             WHERE ax3.axis_set_id =                                                   rrc0.axis_set_id                                               AND ax3.axis_seq BETWEEN                                                   rrc0.axis_seq_low AND                                                   rrc0.axis_seq_high) rrc3                                     WHERE rrc2.axis_set_id = rrc3.axis_set_id(+)                                       AND rrc2.axis_seq_low = rrc3.axis_seq(+)) rrc5                             WHERE rrc4.axis_set_id = rrc5.axis_set_id(+)                               AND rrc4.axis_seq_low = rrc5.axis_seq(+)) rrc6                     WHERE rrc6.axis_set_id = rra3.axis_set_id(+)                       AND rrc6.axis_seq_low = rra3.axis_seq(+)) a             GROUP BY a.axis_set_id                     ,a.axis_seq) b          ,(SELECT a.axis_set_id                  ,a.axis_seq                  ,SUM(decode(a.operation_sign,                              '+',                              a.year_begin_balance,                              '-',                              -a.year_begin_balance,                              a.year_begin_balance)) year_begin_balance                  ,SUM(decode(a.operation_sign,                              '+',                              a.period_end_balance,                              '-',                              -a.period_end_balance,                              a.period_end_balance)) period_end_balance              FROM (SELECT gcc.code_combination_id account_id                          ,gcc.summary_flag summary_flag                          ,rra2.range_mode summary                          ,rra2.axis_set_id                          ,rra2.axis_seq                          ,rra2.sign operation_sign                          ,gcc.segment1 || '.' || gcc.segment2 || '.' ||                           gcc.segment3 || '.' || gcc.segment4 || '.' ||                           gcc.segment5 || '.' || gcc.segment6 user_account                          ,gb.period_name period_name                          ,nvl(gb.begin_balance_dr,                               0) + nvl(gb.period_net_dr,                                        0) - nvl(gb.begin_balance_cr,                                                 0) -                           nvl(gb.period_net_cr,                               0) period_end_balance                          ,(SELECT SUM(nvl(gb1.begin_balance_dr,                                           0) - nvl(gb1.begin_balance_cr,                                                    0)) year_begin_balance                              FROM gl.gl_balances gb1                             WHERE gb1.period_year = gb.period_year                               AND gb1.period_num = 1                               AND gb1.currency_code = gb.currency_code                               AND gb1.code_combination_id =                                   gb.code_combination_id) year_begin_balance                      FROM gl.gl_balances gb                          ,gl.gl_code_combinations gcc                          ,(SELECT rra.axis_set_id                                  ,rra.axis_seq                                  ,rra.sign                                  ,rra.range_mode                                  ,rra.segment1_low                                  ,rra.segment1_high                                  ,rra.segment2_low                                  ,rra.segment2_high                                  ,rra.segment3_low                                  ,rra.segment3_high                                  ,rra.segment4_low                                  ,rra.segment4_high                                  ,rra.segment5_low                                  ,rra.segment5_high                                  ,rra.segment6_low                                  ,rra.segment6_high                                  ,rra.segment7_low                                  ,rra.segment7_high                                  ,rra.segment8_low                                  ,rra.segment8_high                                  ,rra.segment9_low                                  ,rra.segment9_high                              FROM rg.rg_report_axis_contents rra                             WHERE rra.axis_set_id = v_row_set_id) rra2                     WHERE gb.code_combination_id = gcc.code_combination_id                          --and gb.currency_code = 'CNY'                       AND gb.actual_flag = 'A'                       AND gb.period_name = v_period_name                       AND gb.ledger_id = v_ledger_id                       AND gcc.segment1 = v_legal_entity                       AND gcc.segment2 BETWEEN                           nvl(rra2.segment2_low,                               wk_segment2_min) AND                           nvl(rra2.segment2_high,                               wk_segment2_max)                       AND gcc.segment3 BETWEEN                           nvl(rra2.segment3_low,                               wk_segment3_min) AND                           nvl(rra2.segment3_high,                               wk_segment3_max)                       AND gcc.segment4 BETWEEN                           nvl(rra2.segment4_low,                               wk_segment4_min) AND                           nvl(rra2.segment4_high,                               wk_segment4_max)                       AND gcc.segment5 BETWEEN                           nvl(rra2.segment5_low,                               wk_segment5_min) AND                           nvl(rra2.segment5_high,                               wk_segment5_max)                       AND gcc.segment6 BETWEEN                           nvl(rra2.segment6_low,                               wk_segment6_min) AND                           nvl(rra2.segment6_high,                               wk_segment6_max)                       AND gcc.segment7 BETWEEN                           nvl(rra2.segment7_low,                               wk_segment7_min) AND                           nvl(rra2.segment7_high,                               wk_segment7_max)                       AND gcc.segment8 BETWEEN                           nvl(rra2.segment8_low,                               wk_segment8_min) AND                           nvl(rra2.segment8_high,                               wk_segment8_max)                       AND gcc.segment9 BETWEEN                           nvl(rra2.segment9_low,                               wk_segment9_min) AND                           nvl(rra2.segment9_high,                               wk_segment9_max)                       AND gcc.summary_flag = rra2.range_mode) a             GROUP BY a.axis_set_id                     ,a.axis_seq) rra4          ,rg.rg_report_axes ax          ,rg.rg_report_axis_sets axs     WHERE ax.axis_set_id = rra4.axis_set_id(+)       AND ax.axis_seq = rra4.axis_seq(+)       AND ax.axis_set_id = b.axis_set_id(+)       AND ax.axis_seq = b.axis_seq(+)       AND ax.axis_set_id = axs.axis_set_id       AND axs.axis_set_type = 'R'       AND axs.axis_set_id = v_row_set_id     ORDER BY ax.axis_seq ASC;

Ledger的COA定义:

CURSOR cur_segment_low_high(v_ledger_id NUMBER) IS    SELECT fifs.application_column_name          ,MIN(a.flex_value) AS min_value          ,MAX(a.flex_value) AS max_value      FROM gl_ledgers             led          ,fnd_id_flex_structures fifs2          ,fnd_id_flex_segments   fifs          ,fnd_flex_values        a     WHERE led.ledger_id = v_ledger_id       AND led.chart_of_accounts_id = fifs2.id_flex_num       AND fifs2.application_id = 101       AND fifs2.id_flex_code = 'GL#'       AND fifs2.application_id = fifs.application_id       AND fifs2.id_flex_code = fifs.id_flex_code       AND fifs2.id_flex_num = fifs.id_flex_num       AND fifs.flex_value_set_id = a.flex_value_set_id     GROUP BY led.chart_of_accounts_id             ,fifs.application_column_name;

3,关于年初数,期末数等Column的计算

FSG报表中的数据来源是GL_BALANCES表,这个表中begin_balance_dr和begin_balance_cr表示期初值,而period_net_dr和period_net_cr是期间发生的值。period_year是会计期间所在年,period_num可表示所在月份。

1) 年初数对应的正是会计期间所在年份的period_num=1的时候,begin_balance_dr-begin_balance_cr的合计值。

2) 期末数就是begin_balance_dr+begin_balance_dr-begin_balance_cr-begin_balance_cr的合计值。

其它Column,像期初数的计算方法类似推就可以了。


4,iReport开发报表

在数据库中定义好临时表,分成两部分,左边是资产,右边是负债,这样SQL取出来的一条数据正好和资产负债表的一行对应。

表中数据Sample如下:

ROW_NAME1LINE_NO1FY_START_ACTUAL1YTD_ACTUAL1ROW_NAME2LINE_NO2FY_START_ACTUAL2YTD_ACTUAL2CORPARATIONROW_SEQREPORT_DATEREQUEST_IDCREATED_BYCREATION_DATE
流动资产:1  流动负债:36  有限公司12012年12月31日60804719311/28/2013 12:15
货币资金2100000021010000短期借款37 -210000有限公司22012年12月31日60804719311/28/2013 12:15
 3   38  有限公司32012年12月31日60804719311/28/2013 12:15
应收票据4 10000000应付票据39 30000有限公司42012年12月31日60804719311/28/2013 12:15

这样表中已经将数据保存好,iReport中的工作就非常简单了,下面就不介绍了。


资产负债表:

http://baike.baidu.com/view/16173.htm

http://www.dongao.com/zjzcgl/fdzl/201210/86836.shtml

http://www.itpub.net/thread-1163664-1-1.html

  相关解决方案