本报表模板，为行集集合和列集合全部都有的情况下，
create or replace package cux_fsg_property_bus_vci_pkg is
procedure main
(
errbuf out varchar2,
retcode out varchar2,
p_period in varchar2,
p_company in varchar2
);
end cux_fsg_property_bus_vci_pkg;
/----包头结束，下面为包体
create or replace package body cux_fsg_property_bus_vci_pkg is
g_ledger_id number;
g_period varchar2(10);
g_company varchar2(20);
g_currency_code varchar2(10);
g_rg_report_name varchar2(40);
g_cg_report_name varchar2(40);
type array1 is table of number index by binary_integer;
type array2 is table of array1 index by binary_integer;
---------------------以上为建立二维数据，用于存储每个格子的数据，其下边对应行列集的序号
array_during_amount array2;
--log
procedure log(p_str in varchar2) is
begin
fnd_file.put_line(fnd_file.log, p_str);
end log;
--output
procedure output(p_str in varchar2) is
begin
fnd_file.put_line(fnd_file.output, p_str);
end output;
--‘账户分配’的期间发生额
function get_ams_amount
(
p_sequence_r in number,
p_axis_set_id_r in number,
p_sequence_c in number,
p_axis_set_id_c in number,
x_code out number
) return number is
l_result number := 0;
l_result_c number := 0;
l_count number;
v_amount number;
l_low_1 varchar2(40);
l_high_1 varchar2(40);
begin
x_code := 0;
----------对前台参数如果放空的处理
if g_company is not null
then
----------以下为判断参数为父值 和子值的处理
select count(1)
into l_count
from fnd_flex_value_norm_hierarchy ffh,
fnd_id_flex_segments ffs,
gl_sets_of_books gb
where ffh.flex_value_set_id = ffs.flex_value_set_id
and gb.short_name = 'PROPERTY_SOB'
and ffs.application_id = 101
and ffs.id_flex_code = 'GL#'
and ffs.id_flex_num = gb.chart_of_accounts_id
and ffs.application_column_name = 'SEGMENT1'
and ffh.parent_flex_value = g_company;
if l_count > 0
then
select ffh.child_flex_value_low, ffh.child_flex_value_high
into l_low_1, l_high_1
from fnd_flex_value_norm_hierarchy ffh,
fnd_id_flex_segments ffs,
gl_sets_of_books gb
where ffh.flex_value_set_id = ffs.flex_value_set_id
and gb.short_name = 'PROPERTY_SOB'
and ffs.application_id = 101
and ffs.id_flex_code = 'GL#'
and ffs.id_flex_num = gb.chart_of_accounts_id
and ffs.application_column_name = 'SEGMENT1'
and ffh.parent_flex_value = g_company;
else
l_low_1 := g_company;
l_high_1 := g_company;
end if;
----------以上为判断参数为父值 和子值的处理
----------外层循环 行集账户分配有多行的前提下
for ams_range_r in (select rrc.*,
decode(rrc.sign, '-', -1, '+', 1) sign_r
from rg_report_axis_contents rrc
where rrc.axis_set_id = p_axis_set_id_r
and rrc.axis_seq = p_sequence_r
order by rrc.axis_seq)
loop
----------内层循环 列集账户分配有多行的前提下
for ams_range_c in (select *
from rg_report_axis_contents rrc
where rrc.axis_set_id = p_axis_set_id_c
and rrc.axis_seq = p_sequence_c
order by rrc.axis_seq)
loop
-- 分摊取数的表cin_circ_method_f_imp，将分摊的余额放在了period_net_dr字段里。所以这里可以直接取
select nvl(sum(nvl(cf.begin_balance_dr, 0) -
nvl(cf.begin_balance_cr, 0) + nvl(cf.period_net_dr, 0)
-nvl(cf.period_net_cr, 0)),0) *
decode(ams_range_c.sign, '-', -1, '+', 1)
into v_amount
from cin_circ_method_f_imp cf
where cf.period_name = g_period
and cf.segment1 between l_low_1 and l_high_1
and cf.segment9 between ams_range_r.segment9_low and
ams_range_r.segment9_high
and cf.segment3 between ams_range_c.segment3_low and
ams_range_c.segment3_high
and cf.segment8 between ams_range_c.segment8_low and
ams_range_c.segment8_high;
l_result_c := l_result_c + v_amount;
end loop ams_range_c;
l_result := l_result + l_result_c * (ams_range_r.sign_r);
end loop ams_range_r;
return l_result;
else -------------------这里是当输入的公司参数为空的前提下
----------外层循环 行集账户分配有多行的前提下
for ams_range_r in (select rrc.*,
decode(rrc.sign, '-', -1, '+', 1) sign_r
from rg_report_axis_contents rrc
where rrc.axis_set_id = p_axis_set_id_r
and rrc.axis_seq = p_sequence_r
order by rrc.axis_seq)
loop
----------内层循环 列集账户分配有多行的前提下
for ams_range_c in (select *
from rg_report_axis_contents rrc
where rrc.axis_set_id = p_axis_set_id_c
and rrc.axis_seq = p_sequence_c
order by rrc.axis_seq)
loop
-- 分摊取数的表cin_circ_method_f_imp，将分摊的余额放在了period_net_dr字段里。所以这里可以直接取
select nvl(sum(nvl(cf.begin_balance_dr, 0) -
nvl(cf.begin_balance_cr, 0) + nvl(cf.period_net_dr, 0)
-nvl(cf.period_net_cr, 0)),0) *
decode(ams_range_c.sign, '-', -1, '+', 1)
into v_amount
from cin_circ_method_f_imp cf
where cf.period_name = g_period
and cf.segment1 in (select cd.company_code from cux_company_sec_dtl_v cd)
--------------这上面的条件是关键点，当输入参数为空的时候，去安全性配置的所有子值
and cf.segment9 between ams_range_r.segment9_low and
ams_range_r.segment9_high
and cf.segment3 between ams_range_c.segment3_low and
ams_range_c.segment3_high
and cf.segment8 between ams_range_c.segment8_low and
ams_range_c.segment8_high;
l_result_c := l_result_c + v_amount;
end loop ams_range_c;
l_result := l_result + l_result_c * (ams_range_r.sign_r);
end loop ams_range_r;
return l_result;
end if ;
exception
when others then
x_code := 2;
log('获取[' || p_sequence_r || ']行，[' || p_sequence_c ||
']列的期间发生额时出错:' || dbms_utility.format_error_stack ||
dbms_utility.format_error_backtrace);
return 0;
end get_ams_amount;
--主入口
procedure main
(
errbuf out varchar2,
retcode out varchar2,
p_period in varchar2,
p_company in varchar2
) is
r_axis_set_id number;
c_axis_set_id number;
v_code number;
l_company_des varchar2(100);
begin
--全部参数初始化
g_ledger_id := fnd_profile.value('GL_SET_OF_BKS_ID');
g_company:=p_company;
g_period := p_period;
g_currency_code := 'CNY';
g_rg_report_name := '交强险损益表-业务';
g_cg_report_name := '交强险损益表-业务';
select r.axis_set_id
into r_axis_set_id
from rg_report_axis_sets r
where r.name = g_rg_report_name
and r.axis_set_type = 'R';
select r.axis_set_id
into c_axis_set_id
from rg_report_axis_sets r
where r.name = g_cg_report_name
and r.axis_set_type = 'C';
------得到机构描述
if p_company is null
then l_company_des :=' ';
log('在机构段为空的情况下，在安全下规则下，报表取以下公司段的数据');
for cur_c in (select * from cux_company_sec_dtl_v cd)
loop
log(cur_c.description);
end loop;
else
select ffv.description
into l_company_des
from fnd_flex_values_vl ffv,
fnd_id_flex_segments ffs,
gl_sets_of_books gb
where ffv.flex_value_set_id = ffs.flex_value_set_id
and gb.short_name = 'PROPERTY_SOB'
and ffv.flex_value = p_company
and ffs.application_id = 101
and ffs.id_flex_code = 'GL#'
and ffs.id_flex_num = gb.chart_of_accounts_id
and ffs.application_column_name = 'SEGMENT1';
end if ;
/* select * from fnd_id_flex_segments ffs where */
----------行集为‘账户分配’情况下
for ams_r in (select rra.*,
decode(rra.change_sign_flag, 'Y', -1, 1) change_sign
from rg_report_axes rra
where rra.axis_set_id = r_axis_set_id
and exists (select 1
from rg_report_axis_contents rrc
where rrc.axis_set_id = rra.axis_set_id
and rrc.axis_seq = rra.axis_seq)
order by rra.axis_seq)
loop
---------列集 也为‘账户分配’的情况下
for ams_c in (select *
from rg_report_axes rra
where rra.axis_set_id = c_axis_set_id
and exists (select 1
from rg_report_axis_contents rrc
where rrc.axis_set_id = rra.axis_set_id
and rrc.axis_seq = rra.axis_seq)
order by rra.axis_seq)
loop
array_during_amount(ams_r.axis_seq)(ams_c.axis_seq) := get_ams_amount(ams_r.axis_seq,
r_axis_set_id,
ams_c.axis_seq,
c_axis_set_id,
v_code);
-------判断前台是否需要改变符号（这里适应前台用行集合定改变符号，而不是用列集 的情况）
array_during_amount(ams_r.axis_seq)(ams_c.axis_seq) := ams_r.change_sign *
array_during_amount(ams_r.axis_seq)
(ams_c.axis_seq);
log('amount（' || ams_r.axis_seq || '）（' || ams_c.axis_seq || '）=' ||
array_during_amount(ams_r.axis_seq) (ams_c.axis_seq));
end loop ams_c;
---------在行集为’账户分配‘前提下，列集为’计算‘的情况
for cal_c in (select *
from rg_report_axes rra
where rra.axis_set_id = c_axis_set_id
and exists (select 1
from rg_report_calculations rrc
where rrc.axis_set_id = rra.axis_set_id
and rrc.axis_seq = rra.axis_seq)
order by rra.axis_seq)
loop
----z找到列之后 ，值为空，先赋值为0，
array_during_amount(ams_r.axis_seq)(cal_c.axis_seq) := 0;
for cal_range_c in (select *
from rg_report_calculations rrc
where rrc.axis_set_id = c_axis_set_id
and rrc.axis_seq = cal_c.axis_seq
order by rrc.calculation_seq)
loop
for cal_range_c2 in (select *
from rg_report_axes rrc
where rrc.axis_set_id = c_axis_set_id
and rrc.axis_seq between
cal_range_c.axis_seq_low and
cal_range_c.axis_seq_high
order by rrc.axis_seq)
loop
if cal_range_c.operator = '+'
then
array_during_amount(ams_r.axis_seq)(cal_c.axis_seq) := array_during_amount(ams_r.axis_seq)
(cal_c.axis_seq) +
nvl(array_during_amount(ams_r.axis_seq)
(cal_range_c2.axis_seq),
0);
elsif cal_range_c.operator = '-'
then
array_during_amount(ams_r.axis_seq)(cal_c.axis_seq) := array_during_amount(ams_r.axis_seq)
(cal_c.axis_seq) -
nvl(array_during_amount(ams_r.axis_seq)
(cal_range_c2.axis_seq),
0);
end if;
end loop cal_range_c2;
end loop cal_range_c;
--------判断前台是否需要改变符号（这里适应前台用行集合定改变符号，而不是用列集 的情况）
array_during_amount(ams_r.axis_seq)(cal_c.axis_seq) := array_during_amount(ams_r.axis_seq)
(cal_c.axis_seq) *
ams_r.change_sign;
log('amount（' || ams_r.axis_seq || '）（' || cal_c.axis_seq || '）=' ||
array_during_amount(ams_r.axis_seq) (cal_c.axis_seq));
end loop cal_c;
end loop ams_r;
-----------------在行集为’计算‘的前提下
for cal_r in (select rra.*,
decode(rra.change_sign_flag, 'Y', -1, 1) change_sign,
rrc.axis_seq_low,
rrc.axis_seq_high
from rg_report_axes rra, rg_report_calculations rrc
where rra.axis_set_id = r_axis_set_id
and rra.axis_set_id = rrc.axis_set_id
and rrc.axis_seq = rra.axis_seq
order by rra.axis_seq)
loop
--------------对于列集包括’账户组合‘和’计算‘的所有的列
for c in (select *
from rg_report_axes rra
where rra.axis_set_id = c_axis_set_id
and rra.axis_seq not in (80, 90) ----------------------------业务规则，8列和9列放空。
order by rra.axis_seq)
loop
array_during_amount(cal_r.axis_seq)(c.axis_seq) := 0;
for cal_range_r in (select *
from rg_report_axes rrc
where rrc.axis_set_id = r_axis_set_id
and rrc.axis_seq between cal_r.axis_seq_low and
cal_r.axis_seq_high
order by rrc.axis_seq)
loop
array_during_amount(cal_r.axis_seq)(c.axis_seq) := array_during_amount(cal_r.axis_seq)
(c.axis_seq) +
nvl(array_during_amount(cal_range_r.axis_seq)
(c.axis_seq),
0);
end loop cal_range_r;
array_during_amount(cal_r.axis_seq)(c.axis_seq) := cal_r.change_sign *
array_during_amount(cal_r.axis_seq)
(c.axis_seq);
log('amount（' || cal_r.axis_seq || '）（' || c.axis_seq || '）=' ||
array_during_amount(cal_r.axis_seq) (c.axis_seq));
end loop c;
end loop cal_r;
output('
<html >
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>交强险分部损益表（业务分部）</title>
</head>
<body>
<table bordercolor="#f00" border="1" style="border-collapse:collapse;border:none;" >
<tr >
<td nowrap="nowrap" colspan="10" style="border:none;"><p align="center" ><strong><big> 交强险分部损益表（业务分部） </big></strong></p></td>
</tr>
<tr >
<td nowrap="nowrap" style="border:none;" ><p align="left"><strong> 编报单位：</strong></p></td>
<td colspan="5" nowrap="nowrap" style="border:none;"><p align="left"><strong>' ||
l_company_des ||
'</strong></p></td>
<td nowrap="nowrap" style="border:none;"><p align="right" ><strong> 报告期：</strong></p></td>
<td nowrap="nowrap" colspan="2" style="border:none;"><p align="left" >' ||
g_period || '</p></td>
<td nowrap="nowrap" style="border:none;"><p align="right" ><strong> 币种:CNY</strong></p></td>
</tr>
<tr >
<td nowrap="nowrap" rowspan="2" ><p align="center" ><strong> 业务分部</strong></p></td>
<td nowrap="nowrap" rowspan="2" ><p align="center" ><strong> 已赚保费 </strong></p></td>
<td nowrap="nowrap" rowspan="2" ><p align="center" ><strong> 赔款支出 </strong></p></td>
<td nowrap="nowrap" rowspan="2" ><p align="center" ><strong> 未决赔款准备金提转差 </strong></p></td>
<td nowrap="nowrap" colspan="2" ><p align="center" ><strong> 经营费用 </strong></p></td>
<td nowrap="nowrap" rowspan="2" ><p align="center" ><strong> 分摊的投资收益 </strong></p></td>
<td nowrap="nowrap" rowspan="2" ><p align="center" ><strong> 经营利润 </strong></p></td>
<td nowrap="nowrap" rowspan="2" ><p align="center" ><strong> 期初累计经营利润 </strong></p></td>
<td nowrap="nowrap" rowspan="2" ><p align="center" ><strong> 期末累计经营利润 </strong></p></td>
</tr>
<tr >
<td nowrap="nowrap" ><p align="center" ><strong> 专属费用 </strong></p></td>
<td nowrap="nowrap" ><p align="center" ><strong> 分摊的共同费用 </strong></p></td>
</tr>');
---输出主要信息
for out_r in (select *
from rg_report_axes rrc
where rrc.axis_set_id = r_axis_set_id
order by rrc.axis_seq)
loop
if out_r.display_flag = 'Y'
then
---------------------判断行是否显示
output('<tr >
<td nowrap="nowrap" ><p align="center" ><strong> ' ||
out_r. description || '</strong></p></td>');
for out_c in (select *
from rg_report_axes rrc
where rrc.axis_set_id = c_axis_set_id
order by rrc.axis_seq)
loop
if out_c.display_flag = 'Y'
then
---------------------判断列是否显示
if out_c.axis_seq <= 70
then
output('<td nowrap="nowrap" ><p align="right">' ||
nvl(array_during_amount(out_r.axis_seq)
(out_c.axis_seq),
0) || '</p></td>');
else
output('<td> </td>');
end if;
else
log('第' || out_c.axis_seq || '列是不显示的。');
end if;
end loop out_c;
output(' </tr>');
else
log('第' || out_r.axis_seq || '行是不显示的。');
end if;
end loop out_r;
-------------报表尾
output(' </table>
</body>
</html>');
exception
when others then
retcode := '2';
errbuf := errbuf || '........................未知错误:' ||
dbms_utility.format_error_stack ||
dbms_utility.format_error_backtrace;
log(errbuf);
end main;
end cux_fsg_property_bus_vci_pkg;
/