原文链接： http://www.itpub.net/thread-1163664-1-1.html
| 可不可以用SQL写FSG报表呢？答案是肯定的，网上也有一些资料，不过都比较简单。FSG是 ORACLE ERP功能强大的报表设计器，但他的版面设计太简单，又不易于导入EXCEL，因此很多用户报怨，还不如提份数据给他们，就得用SQL来写了。还好，花了三天时间，整出了SQL版的FSG报表两份：资产负债表和损益表，现贴出资产负债表（损益表写法与资产负债表相同)，供从事ORACLE ERP开发的同事参考。 /* 程序名称：资产负债表 作者：谢东文 必要：需要修改a的账套ID、本位币种及科目设定；需要修改报表行集ID RRC系列:展开基于计算的行引用 a:基于账户分配的行金额 A:基于计算的行金额明细 B:基于计算的行金额汇总 本程序在11.5.9及11.5.10测试通过 */ SELECT AX.AXIS_SEQ, AX.DESCRIPTION, DECODE(AX.CHANGE_SIGN_FLAG,’Y',-DECODE(RRA4.AXIS_SET_ID,NULL,B.期初余额,RRA4.期初余额), DECODE(RRA4.AXIS_SET_ID,NULL,B.期初余额,RRA4.期初余额)) 期初数, DECODE(AX.CHANGE_SIGN_FLAG,’Y',-DECODE(RRA4.AXIS_SET_ID,NULL,B.期末数,RRA4.期末数), DECODE(RRA4.AXIS_SET_ID,NULL,B.期末数,RRA4.期末数)) 期末数, DECODE(AX.CHANGE_SIGN_FLAG,’Y',-DECODE(RRA4.AXIS_SET_ID,NULL,B.上年同期,RRA4.上年同期), DECODE(RRA4.AXIS_SET_ID,NULL,B.上年同期,RRA4.上年同期)) 上年同期 FROM (SELECT A.AXIS_SET_ID, A.AXIS_SEQ, SUM(DECODE(A.OPERATOR,’-',-A.期初余额,A.期初余额)) 期初余额, SUM(DECODE(A.OPERATOR,’-',-A.期末数,A.期末数)) 期末数, SUM(DECODE(A.OPERATOR,’-',-A.上年同期,A.上年同期)) 上年同期 FROM ( SELECT RRC6.AXIS_SET_ID, RRC6.AXIS_SEQ, RRC6.CALCULATION_SEQ, RRC6.OPERATOR, RRA3.期初余额, RRA3.期末数, RRA3.上年同期 FROM (select a.axis_set_id, a.axis_seq, SUM(DECODE(a.运算符号,’+',a.期初余额,’-',-a.期初余额,a.期初余额)) 期初余额, SUM(Decode(a.运算符号,’+',a.期末数,’-',-a.期末数,a.期末数)) 期末数, SUM(Decode(a.运算符号,’+',a.上年同期累计,’-',-a.上年同期累计,a.上年同期累计)) 上年同期 from (select gcc.code_combination_id 科目ID, gcc.summary_flag 汇总标记, rra2.range_mode 汇总, rra2.axis_set_id, rra2.axis_seq, rra2.sign 运算符号, gcc.segment1||’.'||gcc.segment2||’.'||gcc.segment3||’.'||gcc.segment4 ||’.'||gcc.segment5||’.'||gcc.segment6 账户, gb.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) 期末数, (select NVL(gb2.begin_balance_dr,0)-NVL(gb2.begin_balance_cr,0) 期初借方余额 from gl.gl_balances gb2 where gb2.period_year=gb.period_year and gb2.period_num=1 and gb2.currency_code=gb.currency_code and gb2.code_combination_id=gb.code_combination_id) 期初余额, (select NVL(gb1.begin_balance_dr,0)+NVL(gb1.period_net_dr,0) -NVL(gb1.begin_balance_cr,0)-NVL(gb1.period_net_cr,0) 本年累计 from gl.gl_balances gb1 where gb1.period_year=gb.period_year-1 and gb1.period_num=gb.period_num and gb1.currency_code=gb.currency_code and gb1.code_combination_id=gb.code_combination_id) 上年同期累计 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 from rg.rg_report_axis_contents rra where (rra.set_of_books_id=1 OR RRA.SET_OF_BOOKS_ID IS NULL)) rra2 where gb.code_combination_id=gcc.code_combination_id and gb.currency_code=’CNY’ and gb.actual_flag=’A’ and gb.period_name=’&请输入会计期间’ and gcc.segment1 in(’&公司段1′,’&公司段2′) and gcc.segment2 between NVL(rra2.segment2_low,’00′) and NVL(rra2.segment2_high,’T') and gcc.segment3 between rra2.segment3_low and rra2.segment3_high and gcc.segment4 between NVL(rra2.segment4_low,’00000000′) and NVL(rra2.segment4_high,’T') and gcc.segment5 between NVL(rra2.segment5_low,’000000′) and NVL(rra2.segment5_high,’T') and gcc.segment6 between NVL(rra2.segment6_low,’0000′) and NVL(rra2.segment6_high,’T') and gcc.summary_flag=rra2.range_mode )a –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, –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 –RR3,与RR2相同，自引用 WHERE RRC2.AXIS_SET_ID=RRC3.AXIS_SET_ID(+) AND RRC2.AXIS_SEQ_LOW=RRC3.AXIS_SEQ(+)) RRC4, –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, –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 –RR3,与RRC2相同，自引用 WHERE RRC2.AXIS_SET_ID=RRC3.AXIS_SET_ID(+) AND RRC2.AXIS_SEQ_LOW=RRC3.AXIS_SEQ(+)) RRC5 –RRC5,与RRC4相同，自引用 WHERE RRC4.AXIS_SET_ID=RRC5.AXIS_SET_ID(+) AND RRC4.AXIS_SEQ_LOW=RRC5.AXIS_SEQ(+) ) RRC6 –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.运算符号,’+',a.期初余额,’-',-a.期初余额,a.期初余额)) 期初余额, SUM(DECODE(a.运算符号,’+',a.期末数,’-',-a.期末数,a.期末数)) 期末数, SUM(Decode(a.运算符号,’+',a.上年同期累计,’-',-a.上年同期累计,a.上年同期累计)) 上年同期 from (select gcc.code_combination_id 科目ID, gcc.summary_flag 汇总标记, rra2.range_mode 汇总, rra2.axis_set_id, rra2.axis_seq, rra2.sign 运算符号, gcc.segment1||’.'||gcc.segment2||’.'||gcc.segment3||’.'||gcc.segment4 ||’.'||gcc.segment5||’.'||gcc.segment6 账户, gb.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) 期末数, (select NVL(gb2.begin_balance_dr,0)-NVL(gb2.begin_balance_cr,0) 期初借方余额 from gl.gl_balances gb2 where gb2.period_year=gb.period_year and gb2.period_num=1 and gb2.currency_code=gb.currency_code and gb2.code_combination_id=gb.code_combination_id) 期初余额, (select NVL(gb1.begin_balance_dr,0)+NVL(gb1.period_net_dr,0) -NVL(gb1.begin_balance_cr,0)-NVL(gb1.period_net_cr,0) 本年累计 from gl.gl_balances gb1 where gb1.period_year=gb.period_year-1 and gb1.period_num=gb.period_num and gb1.currency_code=gb.currency_code and gb1.code_combination_id=gb.code_combination_id) 上年同期累计 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 from rg.rg_report_axis_contents rra where (rra.set_of_books_id=1 OR RRA.SET_OF_BOOKS_ID IS NULL)) rra2 where gb.code_combination_id=gcc.code_combination_id and gb.currency_code=’CNY’ and gb.actual_flag=’A’ and gb.period_name=’&请输入会计期间’ and gcc.segment1 in (’&公司段1′,’&公司段2′) and gcc.segment2 between NVL(rra2.segment2_low,’00′) and NVL(rra2.segment2_high,’T') and gcc.segment3 between rra2.segment3_low and rra2.segment3_high and gcc.segment4 between NVL(rra2.segment4_low,’00000000′) and NVL(rra2.segment4_high,’T') and gcc.segment5 between NVL(rra2.segment5_low,’000000′) and NVL(rra2.segment5_high,’T') and gcc.segment6 between NVL(rra2.segment6_low,’0000′) and NVL(rra2.segment6_high,’T') and gcc.summary_flag=rra2.range_mode )a –a,此段代码用于计算账户分配金额 group by a.axis_set_id,a.axis_seq ) RRA4, RG.RG_REPORT_AXES AX 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=1259 –需要修改此报表行集ID ORDER BY AX.AXIS_SEQ |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17087603/viewspace-759146/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17087603/viewspace-759146/
383
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
