CREATE OR REPLACE PACKAGE CHEN_TEST_PACKGE IS
type cursor_type is ref cursor;

/************************************************************************************/
/* 功能说明：查询某种公告报表 */
/* 参数说明： */
/* i_id_capital_dynamic_manage IN VARCHAR2 某种公告ID */
/* o_cursor OUT cursor_type 返回游标 */
/* */
/* 创建日期 姓名 */
/* 2013-03-08 路人甲 */
/************************************************************************************/

PROCEDURE p_list_bulletin_report( i_id_capital_dynamic_manage IN VARCHAR2,
o_cursor OUT cursor_type);

END CHEN_TEST_PACKGE;

CREATE OR REPLACE PACKAGE BODY CHEN_TEST_PACKGE IS

/************************************************************************************/
/* 功能说明：查询某种公告报表 */
/* 参数说明： */
/* i_id_capital_dynamic_manage IN VARCHAR2 某种公告ID */
/* o_cursor OUT bulletin_report_type 返回游标 */
/* */
/* 创建日期 姓名 */
/* 2013-03-08 路人甲 */
/************************************************************************************/

PROCEDURE p_list_bulletin_report( i_id_capital_dynamic_manage IN VARCHAR2,
o_cursor OUT bulletin_report_type)
AS
set_id_bulletin_report_temp VARCHAR2(50); -- 定义临时变量
BEGIN
begin
--给临时变量赋值
--select to_char(sysdate,'yyyymmddhh24missSSS') into set_id_bulletin_report_temp from dual;
select i_id_capital_dynamic_manage into set_id_bulletin_report_temp from dual;
--获取数据插入临时表
insert into scms_bulletin_report_temp
(
id_bulletin_report_temp,
biz_Name,
t01,
t07,
t14,
t21,
t1M,
t2M,
t3M,
t4M,
t5M,
t6M,
t1Y,
t2Y,
tCount,
sort_no
)
select c.*,
rownum as sort_no
from(
select
set_id_bulletin_report_temp as id_bulletin_report_temp,
scms_common_packge.get_biz_name(b.biz_id) as biz_Name,
max(case when b.term_type='T01' then b.c else 0 end) as T01,
max(case when b.term_type='T07' then b.c else 0 end) as T07,
max(case when b.term_type='T14' then b.c else 0 end) as T14,
max(case when b.term_type='T21' then b.c else 0 end) as T21,
max(case when b.term_type='T1M' then b.c else 0 end) as T1M,
max(case when b.term_type='T2M' then b.c else 0 end) as T2M,
max(case when b.term_type='T3M' then b.c else 0 end) as T3M,
max(case when b.term_type='T4M' then b.c else 0 end) as T4M,
max(case when b.term_type='T5M' then b.c else 0 end) as T5M,
max(case when b.term_type='T6M' then b.c else 0 end) as T6M,
max(case when b.term_type='T1Y' then b.c else 0 end) as T1Y,
max(case when b.term_type='T2Y' then b.c else 0 end) as T2Y,
sum(b.c) as BIZ_ID_COUNT
from
(
select a.term_type,a.biz_id,sum(a.capital_claim) c
from (select report.capital_claim,
report.biz_id,
detail.term_type
from scms_capital_claim_report report,
scms_capital_assign_detail detail,
scms_capital_dynamic_manage manager
where manager.id_capital_dynamic_manage = detail.id_capital_dynamic_manage
and report.id_capital_assign_detail = detail.id_capital_assign_detail
and detail.id_capital_dynamic_manage = i_id_capital_dynamic_manage
and manager.IS_SETTLEMENT = '1'
and manager.IS_CONFIRM = '1'
) a
group by a.term_type,a.biz_id
) b group by b.biz_id
) c; -- 插入总记录数
insert into scms_bulletin_report_temp
(
id_bulletin_report_temp,
(select max(sort_no)+1 from scms_bulletin_report_temp te where te.id_bulletin_report_temp = set_id_bulletin_report_temp ) as sort_no
from(
select
set_id_bulletin_report_temp as id_bulletin_report_temp,
'总计（天数）' as biz_Name,'biz_id_count' as biz_id,
scms_capital_dynamic_manage manager
where manager.id_capital_dynamic_manage = detail.id_capital_dynamic_manage
and report.id_capital_assign_detail = detail.id_capital_assign_detail
and detail.id_capital_dynamic_manage = i_id_capital_dynamic_manage
and manager.IS_SETTLEMENT = '1'
and manager.IS_CONFIRM = '1'
) a
group by a.term_type
) b group by b.biz_id
) c;
-- 查询刚刚插入的表记录
open o_cursor for
select
id_bulletin_report_temp as idBulletinReportTemp,
biz_Name as bizName,
t01 as t01,
t07 as t07,
t14 as t14,
t21 as t21,
t1M as t1M,
t2M as t2M,
t3M as t3M,
t4M as t4M,
t5M as t5M,
t6M as t6M,
t1Y as t1Y,
t2Y as t2Y,
tCount as tCount,
sort_no as sortNo
from scms_bulletin_report_temp temp
where temp.id_bulletin_report_temp = set_id_bulletin_report_temp
order by sortNo asc;
-- 删除：根据ID删除刚刚插入的记录
delete from scms_bulletin_report_temp temp where temp.id_bulletin_report_temp = set_id_bulletin_report_temp;
commit;
end;
END p_list_bulletin_report;
END CHEN_TEST_PACKGE;
/

{call CHEN_TEST_PACKGE.p_list_bulletin_report(?,?)}