用sql*plus做分组分页报表

最新推荐文章于 2022-09-19 07:50:49 发布

clscp44862

最新推荐文章于 2022-09-19 07:50:49 发布

阅读量180

点赞数

根据机构和币别分组分页列印报表，
首先把currency_code和comp_code栏位定义成变量，并设成不在
报表中显示出来：
col currency_code new_val currency noprint
col comp_code new_val company noprint;
col comp_name new_val company_name noprint;
col currency_desc new_val currency_desc noprint;

把报表日期转换成指定格式并定义成一个变量：
col now new_val today noprint;
select to_char(to_date('&&5','YYYYMMDD'),'YYYY/MM/DD') as now from dual;

然后设定分组分页的依据：
break on comp_code by currency_desc skip page;

定义标题行：
TTI left 'ORG-ID:'company'|REP-ID:'repno'|DAT:&&today|S-ORG-ID:'p_code'|CCY-ID:' currency '|' skip 1-
left '1('repno')' skip 1 center company_name'利润表' skip 1-
' 'currency_desc center today right '单位：万元 ' skip 1 ;
输出的目录
define v_dir='&&1&&2'
spool &v_dir
全文：
/*========================================================================
* PROCEDURE
* NAME :CUXGLTEST.sql
*
* DESCRIPTION:
*
* HISTORY:
* 1.00 2007-08-17 sunnly.zhou Created
*
* The arguments to this script are:
&1 文件输出目录
&2 文件名
&3 P_com_detail
&4 p_set_of_bks_id
&5 p_Accounting_Date
&6 p_Period_Name
&7 P_currency_code
&8 p_frequency
&9 p_rep_no
&10 P_offbalance
&11 p_com_sum
&12 p_batch_id
==========================================================================*/
SET LINESIZE 92
set pagesize 50000
SET NEWPAGE NONE
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON
SET UNDERLINE OFF
set termout off
set trimspool off
set wrap on
set colsep ' '

COL rep_item FOR A50 HEA '项目' justify center
COL seq_no FOR A8 HEA '行号' justify center
COL end_amount for A32 HEA '金额' justify center
col kpi_id noprint
col idno noprint
col currency_code new_val currency noprint
col comp_code new_val company noprint;
col comp_name new_val company_name noprint;
col currency_desc new_val currency_desc noprint;
col rep_no new_val repno noprint;
col parent_code new_val p_code noprint;

col now new_val today noprint;
select to_char(to_date('&&5','YYYYMMDD'),'YYYY/MM/DD') as now from dual;

break on comp_code by currency_desc skip page;
TTI left '1@OD@|@T@|ORG-ID:'company'|REP-ID:'repno'|DAT:&&today|S-ORG-ID:'p_code'|CCY-ID:' currency '|' skip 1-
left '1('repno')' skip 1 center company_name'利润表' skip 1-
' 'currency_desc center today right '单位：万元 ' skip 1 ;
define v_dir='&&1&&2'
spool &v_dir
select cgr.comp_code,
ffvt.description comp_name,
cgrh.rep_no,
cgr.currency_code,
CUX_GL_ODEMPTY_PKG.GET_FLEX_PARENT(cgr.comp_code) parent_code,
flv.DESCRIPTION currency_desc,
cgr.kpi_id,
decode(cgrh.idno,17.1,null,100,lpad('复核：',40),' '||cgr.rep_item) rep_item,
cgrh.idno,
decode(cgrh.idno,17.1,null,100,null,cgr.seq_no) seq_no,
decode(cgrh.idno,17.1,null,100,rpad('制表：',20),to_char(cgr.end_amount/10000,'999,999,999,999,999,999,990.99')) end_amount
from cux_gl_results cgr,
(select set_of_books_id,
cgrh.rep_header_id,
cgrh.rep_no,
cgrl.rep_line_id,
cgrl.seq_no idno,
cgrl.seq_no,
cgrl.rep_item
from cux_gl_headers cgrh, cux_gl_lines cgrl
where cgrh.header_id = cgrl.header_id
and cgrh.header_id = &&9
union all
select cgrh.set_of_books_id,
cgrh.rep_header_id,
cgrh.rep_no,
cgrl.rep_line_id,
decode(rownum,1,17.1,2,100) idno,
cgrl.seq_no,
cgrl.rep_item
from cux_gl_headers cgrh, cux_gl_lines cgrl
where cgrh.header_id = cgrl.header_id
and cgrh.rep_header_id = &&9
and rownum<=2) cgrh,
cux_gl_rules cgrr,
FND_LOOKUP_VALUES_VL flv,
fnd_flex_values ffv,
FND_FLEX_VALUES_TL ffvt,
fnd_flex_value_sets ffvs
where cgr.rep_header_id = cgrr.rep_header_id
and cgr.currency_code = cgrr.currency_code
and cgr.frequency = cgrr.frequency
and cgrr.currency_code = flv.MEANING
and flv.LOOKUP_TYPE = 'GLINT_CURRENCIES'
and cgr.set_of_books_id = cgrh.set_of_books_id
and cgr.rep_header_id = cgrh.rep_header_id
and cgr.rep_line_id = cgrh.rep_line_id
and ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.FLEX_VALUE_ID = ffvt.FLEX_VALUE_ID
AND ffvt.LANGUAGE = userenv('LANG')
and ffvs.flex_value_set_name = 'GL_COA_COMPANY'
and ffv.flex_value=cgr.comp_code
and cgr.set_of_books_id = &&4
and cgr.period_name = '&&6'
and cgr.accounting_date = to_date('&&5', 'YYYYMMDD')
and cgr.frequency = '&&8'
order by cgr.comp_code, cgr.currency_code, cgrh.idno;
spool off
/
输出格式如下
ORG-ID:00004|REP-ID:***0008|DAT:2007/05/31|S-ORG-ID:***4|CCY-ID:CNY|
1(***0008)
中国**湖北省分公司利润表
人民币 2007/05/31 单位：万元
项目行号金额
**收入 1 5,901.79
**支出 2 3,973.25
净**收入 3 1,928.54
手续费及佣金收入 4 3,568.38
手续费及佣金支出 5 112.36
手续费及佣金收支净额 6 3,456.02
净交易收入 7 1,411.29
营业利润 13 -8,667.66
营业外净收入 14 406.79
税前利润 15 -8,260.86
减：所得税 16 1,527.51
税后利润 17 -9,788.37

可供分配利润 18 -9,788.37
本部股东 19 -9,788.37
少数股东 20 0.00
复核：制表：
ORG-ID:00004|REP-ID:***0008|DAT:2007/05/31|S-ORG-ID:***4|CCY-ID:RMB|
1(***0008)
中国**广东省分公司利润表
折人民币 2007/05/31 单位：万元
项目行号金额
**收入 1 8,008.03
**支出 2 1,907.18
净**收入 3 6,100.85
手续费及佣金收入 4 3,652.77
手续费及佣金支出 5 112.52
手续费及佣金收支净额 6 3,540.25
净交易收入 7 2,086.35
营业利润 13 -3,775.76
营业外净收入 14 406.75
税前利润 15 -3,369.00
减：所得税 16 1,527.51
税后利润 17 -4,896.51

可供分配利润 18 -4,896.51
本部股东 19 -4,896.51
少数股东 20 0.00
复核：制表：