EBS中二次开发FSG报表2(SQL)
来源：互联网 发布：京东广告部算法岗位 编辑：程序博客网 时间：2024/04/30 01:41
环境： 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如下：
这样表中已经将数据保存好，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
- EBS中二次开发FSG报表2(SQL)
- EBS中二次开发FSG报表1(XML数据)
- ORACEL EBS之FSG报表
- Oracle EBS FSG报表迁移
- FSG报表打印报错，log文件显示java.sql.SQLException: No corresponding LOB data found
- fsg
- Oracle EBS的BIP报表中显示特殊字体
- EBS报表中使用安全性层次结构控制
- Oracle EBS的BIP报表中显示特殊字体
- 不同环境间FSG报表组件的传输
- OAF-用JDeveloper二次开发EBS
- EBS报表输出图片
- BI 开发EBS报表
- Magento 中执行SQL语句.[magento二次开发]
- Oracle EBS:常用SQL语句(2)
- EBS XMLP 报表乱码解决方案
- EBS XMLP 报表乱码解决方案
- Oracle EBS 报表输出问题
- PowerDesigner 工具生成数据库Report指导
- poj 2739 Sum of Consecutive Prime Numbers
- 《高性能网站建设指南》阅读笔记_规则5- 将样式放在顶部
- ecshop实现后台二次开发后功能模块仍然可以分配权限
- java web项目整体异常处理机制
- EBS中二次开发FSG报表2(SQL)
- 优化SQL Server的内存占用之执行缓存
- libxml/tree.h not found（xcode4.5解决方案）
- hibernate学习笔记1
- ecshop之transport和jquery冲突之完美解决方案
- PhoneGap入门经典——理解PhoneGap应用程序基础
- Java多线程编程环境中单例模式的实现
- android getevent/setevent
- mtk android lcm 打印log信息方法
