本文介绍了一种批量处理基金交易数据的方法，包括全量导入交易确认流水表、按日期更新数据以及计算管理费用等关键步骤。
CREATE OR REPLACE PACKAGE BODY PCKG_CIF_PROFIT_BATCH_2 AS
--日期
/*********************************************************************
*将交易确认流水表的的数据导入warehouse交易确认流水表中,跑全量！
**********************************************************************/
PROCEDURE PROFIT_SUM_INI_2(p_i_datetime IN VARCHAR2,
P_O_FLAG OUT VARCHAR2 --传出执行结果状态
) is
BEGIN
insert into STG_PROFIT_TCONFIRM
select c_taaccountid,
c_transactioncfmdate,
c_businesscode,
c_fundcode,
c_confirmedamount,
c_status,
f_balance
--投资人基金帐号 基金代码 TA确认日期 业务代码 交易确认金额 交易处理返回代码 交易申请金额
--from cif_tconfirm @ cifdblink where c_transactioncfmdate = p_i_datetime;
from cif_tconfirm @ cifdblink where c_transactioncfmdate = p_i_datetime;
--and c_transactioncfmdate>=20061228 ;
/*(select
--1.基金账户
tc.c_taaccountid,
--2.基金代码
tc.c_fundcode,
--3.TA确认日期
tc.c_transactioncfmdate,
--4.业务代码
tc.c_businesscode,
--5.交易确认金额
tc.c_confirmedamount,
--6.交易处理返回代码
tc.c_status,f_balance,
--7.交易申请金额
tc.f_balance);*/
--表名 @dblink
commit;
END;
/*********************************************
* 时间函数每日的日期进行加一
**********************************************/
procedure call_PROFIT_SUM_INI_2(v_date varchar2) as
v_start_dt date;
v_end_dt date := to_date('20121105', 'yyyymmdd'); --trunc(sysdate);
--v_end_dt date := to_date('20061228', 'yyyymmdd'); --trunc(sysdate);
v_var varchar2(100);
BEGIN
if (v_date is not null) then
v_start_dt := to_date(v_date, 'yyyymmdd');
end if;
while v_start_dt <= v_end_dt loop
PROFIT_SUM_INI_2(to_char(v_start_dt, 'yyyymmdd'), v_var);
v_start_dt := v_start_dt + 1;
end loop;
END call_PROFIT_SUM_INI_2;
/******************************************************
*cif每日增量跑批量，导入warehouse交易确认流水表中。
******************************************************/
PROCEDURE PROFIT_SUM_CTRL_2(p_i_datetime IN VARCHAR2, --日期 记录日志日期
out_retcode OUT VARCHAR2 --存储过程返回码
) IS
--ICOUNT1 VARCHAR2(8);
ICOUNT2 VARCHAR2(8);
--营业时间标志
--v_workflag NUMBER(1);
--日志变量
v_thisprocid LOG_BATCH.PROC_ID%TYPE := '1201000';
v_thisprocname LOG_BATCH.PROC_NAME%TYPE := 'PCKG_CIF_PROFIT_BATCH.PROFIT_SUM_CTRL_2';
v_stepno LOG_BATCH.STEP_NUM%TYPE;
v_time LOG_BATCH.TIME%TYPE;
v_time_flag LOG_BATCH.TIME_FLAG%TYPE;
v_deal_flag LOG_BATCH.DEAL_FLAG%TYPE := '0'; --判断成功标志 ‘0’成功，‘1’失败
v_note LOG_BATCH.NOTE%TYPE;
BEGIN
out_retcode := '0';
v_stepno := 1;
v_time := to_char(sysdate, 'yyyymmddhh24miss');
v_time_flag := p_i_datetime;
IF p_i_datetime IS NULL THEN
out_retcode := 'CIF-00000';
v_deal_flag := '1';
v_note := out_retcode || ':' || '输入日期参数空';
--dbms_output.put_line(sqlcode);
--dbms_output.put_line(sqlerrm);
--记录日志
/* 处理失败后记存储过程错误日志和存储过程调度日志 */
PACK_REPORT_LOG.P_BATCHLOG_ERR(v_thisprocid,
v_thisprocname,
v_stepno,
v_time,
v_time_flag,
v_deal_flag,
v_note);
RETURN;
ELSE
/*--获取营业日期标志
select l_workflag into v_workflag
from CIF_TOPENDAY --基金服务日
where d_date = to_date(p_i_datetime, 'yyyy/MM/dd');
IF v_workflag IS NULL OR v_workflag = 0 THEN
out_retcode := 'CIF-00000';
v_deal_flag := '1';
v_note := out_retcode || ':' || '非营业时间';
--dbms_output.put_line(sqlcode);
--dbms_output.put_line(sqlerrm);*/
--记录日志
/* 处理失败后记存储过程错误日志和存储过程调度日志 */
Pack_REPORT_LOG.P_BATCHLOG_ERR(v_thisprocid,
v_thisprocname,
v_stepno,
v_time,
v_time_flag,
v_deal_flag,
v_note);
--RETURN;
END IF;
--1.初始条件判定
-- select MAX(tcon1.c_transactioncfmdate) into ICOUNT1 from cif_tconfirm @cifdblink tcon1;
select MAX(tcon2.c_transactioncfmdate)
into ICOUNT2
from STG_PROFIT_TCONFIRM tcon2;
IF (ICOUNT2 >= p_i_datetime) THEN
delete from STG_PROFIT_tconfirm tcon2
where tcon2.c_transactioncfmdate >= p_i_datetime;
--and cf1.c_taaccountid = STG_PROFIT_TCONFIRM.c_taaccountid;
--commit;
--3.将每日新增数据插入交易确认流水表中
insert into STG_PROFIT_TCONFIRM
select c_taaccountid,
c_transactioncfmdate,
c_businesscode,
c_fundcode,
c_confirmedamount,
c_status,
f_balance
--投资人基金帐号 基金代码 TA确认日期 业务代码 交易确认金额 交易处理返回代码 交易申请金额
from cif_tconfirm @cifdblink
where c_transactioncfmdate = p_i_datetime;
delete from STG_PROFIT_TCONFIRM tco
where --c_transactioncfmdate
tco.c_transactioncfmdate <
(select MIN(a.c_transactioncfmdate)
from (select distinct tf1.c_transactioncfmdate
from STG_PROFIT_TCONFIRM tf1
where tf1.c_transactioncfmdate > '20120530'
order by c_transactioncfmdate desc) a
where 1 = 1
and rownum <= 5);
/*select COUNT(c_transactioncfmdate) into icount from
(select distinct c_transactioncfmdate from STG_PROFIT_TCONFIRM );
IF (icount>5)
THEN
delete from STG_PROFIT_tconfirm tc2 where tc2.c_transactioncfmdate =
(select distinct c_transactioncfmdate from STG_PROFIT_tconfirm order by desc
where rownum>5 );*/
-- select COUNT(distinct )
-- commit;
ELSE
insert into STG_PROFIT_TCONFIRM
select c_taaccountid,
c_transactioncfmdate,
c_businesscode,
c_fundcode,
c_confirmedamount,
c_status,
f_balance
--投资人基金帐号 TA确认日期 业务代码 交易确认金额 基金代码 交易处理返回代码 交易申请金额
from cif_tconfirm @cifdblink tc
where tc.c_transactioncfmdate = p_i_datetime;
-- commit;
delete from STG_PROFIT_TCONFIRM tco
where --c_transactioncfmdate
tco.c_transactioncfmdate <
(select MIN(a.c_transactioncfmdate)
from (select distinct tf1.c_transactioncfmdate
from STG_PROFIT_TCONFIRM tf1
where tf1.c_transactioncfmdate > '20120530'
order by c_transactioncfmdate desc) a
where 1 = 1
and rownum <= 5);
END IF;
commit;
--2.批量处理开始记录日志
Pack_REPORT_LOG.P_BATCHLOG( -- Pack_REPORT_LOG.P_BATCHLOG_ERR
v_thisprocid,
v_thisprocname,
v_stepno,
v_time,
v_time_flag,
v_deal_flag,
v_note);
v_stepno := 2;
--记录日志
v_stepno := 999;
--记载数据处理日志
v_deal_flag := '0';
--批量处理完成记录日志
v_time := TO_CHAR(sysdate, 'yyyymmddhh24miss');
Pack_REPORT_LOG.P_BATCHLOG(v_thisprocid,
v_thisprocname,
v_stepno,
v_time,
v_time_flag,
v_deal_flag,
v_note);
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_time := TO_CHAR(sysdate, 'yyyymmddhh24miss');
out_retcode := SQLCODE;
v_deal_flag := '1';
v_note := SQLCODE || ':' || SUBSTR(SQLERRM, 1, 200);
--dbms_output.put_line(sqlcode);
--dbms_output.put_line(sqlerrm);
--记录日志
/* 处理失败后记存储过程错误日志和存储过程调度日志 */
Pack_REPORT_LOG.P_BATCHLOG_ERR( --PACK_REPORT_LOG
v_thisprocid,
v_thisprocname,
v_stepno,
v_time,
v_time_flag,
v_deal_flag,
v_note);
RAISE;
END;
/*********************************************************************
*将交易确认流水表的的数据导入warehouse交易确认流水表中,跑全量！
**********************************************************************/
procedure P_STG_MANAGEMENTFEE(P_I_DATETIME IN VARCHAR2, --传入参数 日期
P_O_FLAG OUT VARCHAR2 --传出执行结果状态
) as
--日志变量
v_thisprocid LOG_BATCH.PROC_ID%TYPE := '2012122700';
v_thisprocname LOG_BATCH.PROC_NAME%TYPE := 'PCKG_CIF_PROFIT_BATCH_2.P_STG_MANAGEMENTFEE';
v_stepno LOG_BATCH.STEP_NUM%TYPE;
v_time LOG_BATCH.TIME%TYPE;
v_time_flag LOG_BATCH.TIME_FLAG%TYPE;
v_deal_flag LOG_BATCH.DEAL_FLAG%TYPE := '0'; --判断成功标志 ‘0’成功，‘1’失败
v_note LOG_BATCH.NOTE%TYPE;
v_P_I_DATETIME varchar2(10) := P_I_DATETIME;
v_n_netvalue number;
v_profitYields number;
v_isexist_flag number := 0; --判断当天管理费是否已累加到月表
CURSOR list_cur IS
/* select
--1.基金账户
tc.c_taaccountid,
--2.基金代码
tc.c_fundcode,
--3.基金名称
(select fu.c_fundname
from stg_tainfo_fundinfo fu
where fu.c_fundcode = tc.c_fundcode) c_fundname,
--4.当前市值
\* NVL(*\
\* (select cf1.n_netvalue
from stg_tfundinfo cf1
where cf1.C_CDATE <= to_date('2012-12-26', 'yyyy-MM-dd')
and cf1.c_fundcode = tc.c_fundcode
--order by cf1.C_CDATE desc
where rownum = 1
) **\
sum(t4.n_currentshares) \*,
0) *\ as F_CURRENTVALUE, --现有份额市值 ------------------------------------
--5.赎回及转出确认金额
NVL(sum(decode(tc.c_businesscode,
'03',
tc.c_confirmedamount,
'13',
tc.c_confirmedamount,
'53',
tc.c_confirmedamount,
'95',
tc.c_confirmedamount,
0)),
0) as f_reconfirmamount,
--7.现金分红
NVL(sum(t7.n_dividendindeed), 0) as f_dividendindeed,
-- 8.认购/申购申请金额
NVL(sum(decode(tc.c_businesscode,
'01',
tc.f_balance,
'02',
tc.f_balance,
'39',
tc.f_balance,
0)),
0) as f_purchaseamount,
--9.转换入确认金额
NVL(sum(decode(tc.c_businesscode,
'16',
tc.c_confirmedamount,
0)),
0) as f_inamount,
to_date(P_I_DATETIME, 'yyyyMMdd') D_DATE,
--11.系统时间
sysdate D_SYSDATE
from stg_profit_tconfirm tc,
(select ts.c_taaccountid,
ts.c_fundcode,
sum(ts.n_currentshares) n_currentshares
from stg_tshare ts
where ts.n_currentshares > 0
group by ts.c_taaccountid, ts.c_fundcode) t4,
(select td.c_taaccountid,
td.c_fundcode,
sum(td.n_dividendindeed) n_dividendindeed
from stg_tdividend td
group by td.c_taaccountid, td.c_fundcode) t7
where tc.c_status = '1'
and tc.c_taaccountid = t4.c_taaccountid(+)
and tc.c_fundcode = t4.c_fundcode(+)
and tc.c_taaccountid = t7.c_taaccountid(+)
and tc.c_fundcode = t7.c_fundcode(+)
-- and tc.c_fundcode = '481004'
group by tc.c_taaccountid, tc.c_fundcode;*/
select
--1.基金账户
tc1.c_taaccountid,
--2.基金代码
tc1.c_fundcode,
--3.基金名称
(select fu.c_fundname
from stg_tainfo_fundinfo fu
where fu.c_fundcode = tc1.c_fundcode) c_fundname,
nvl(t4.n_currentshares, 0) as F_CURRENTVALUE, --现有份额市值 ------------------------------------
--5.赎回及转出确认金额
tc1.f_reconfirmamount,
--7.现金分红
NVL((t7.n_dividendindeed), 0) as f_dividendindeed,
-- 8.认购/申购申请金额
tc1.f_purchaseamount,
--9.转换入确认金额
tc1.f_inamount,
to_date(P_I_DATETIME, 'yyyyMMdd') D_DATE,
--11.系统时间
sysdate D_SYSDATE
from (select
--1.基金账户
tc.c_taaccountid,
--2.基金代码
tc.c_fundcode,
--3.基金名称
(select fu.c_fundname
from stg_tainfo_fundinfo fu
where fu.c_fundcode = tc.c_fundcode) c_fundname,
--5.赎回及转出确认金额
NVL(sum(decode(tc.c_businesscode,
'03',
tc.c_confirmedamount,
'13',
tc.c_confirmedamount,
'53',
tc.c_confirmedamount,
'95',
tc.c_confirmedamount,
0)),
0) as f_reconfirmamount,
-- 8.认购/申购申请金额
NVL(sum(decode(tc.c_businesscode,
'01',
tc.f_balance,
'02',
tc.f_balance,
'39',
tc.f_balance,
0)),
0) as f_purchaseamount,
--9.转换入确认金额
NVL(sum(decode(tc.c_businesscode,
'16',
tc.c_confirmedamount,
0)),
0) as f_inamount
from stg_profit_tconfirm tc
where tc.c_status = '1'
group by tc.c_taaccountid, tc.c_fundcode) tc1,
(select ts.c_taaccountid,
ts.c_fundcode,
sum(ts.n_currentshares) n_currentshares
from stg_tshare ts
where ts.n_currentshares > 0
group by ts.c_taaccountid, ts.c_fundcode) t4,
(select td.c_taaccountid,
td.c_fundcode,
sum(td.n_dividendindeed) n_dividendindeed
from stg_tdividend td
group by td.c_taaccountid, td.c_fundcode) t7
where tc1.c_taaccountid = t4.c_taaccountid(+)
and tc1.c_fundcode = t4.c_fundcode(+)
and tc1.c_taaccountid = t7.c_taaccountid(+)
and tc1.c_fundcode = t7.c_fundcode(+);
begin
--批量处理开始记录日志
v_stepno := 1;
P_O_FLAG := 'ok';
v_time_flag := P_I_DATETIME;
v_time := to_char(sysdate, 'yyyymmddhh24miss');
PACK_REPORT_LOG.P_BATCHLOG(v_thisprocid,
v_thisprocname,
v_stepno,
v_time,
v_time_flag,
v_deal_flag,
v_note);
v_stepno := 2;
if v_isexist_flag > 0 then
delete from STG_MANAGEMENTFEE where c_date = P_I_DATETIME;
end if;
v_stepno := 3;
FOR C_I IN LIST_CUR LOOP
begin
/* select * from (
select cf1.n_netvalue
into v_n_netvalue
from stg_tfundinfo cf1
where cf1.C_CDATE <= to_date('2012-12-26', 'yyyy-MM-dd')
and cf1.c_fundcode = c_i.c_fundcode
order by cf1.C_CDATE desc )
where rownum = 1;*/
select n_netvalue
into v_n_netvalue
from (select c_fundcode, cf1.n_netvalue, cf1.C_CDATE
--into v_n_netvalue
from stg_tfundinfo cf1
where cf1.C_CDATE <= P_I_DATETIME
and cf1.c_fundcode = '481004'
order by cf1.C_CDATE desc) t
where rownum = 1;
-- when exception
end;
v_profitYields := (case when(c_i.f_purchaseamount + c_i.f_inamount) = 0 then 0 else trunc((((c_i.F_CURRENTVALUE + c_i.f_reconfirmamount + c_i.f_dividendindeed) - (c_i.f_purchaseamount + c_i.f_inamount)) / (c_i.f_purchaseamount + c_i.f_inamount) * 100),
2) --trunc保留两位小数
end);
insert into stg_PROFIT_SUM t
(C_TAACCOUNTID,
C_FUNDCODE,
F_CURRENTVALUE, --4
F_RECONFIRMAMOUNT, --5
F_DIVIDENDINDEED, --7
F_PURCHASEAMOUNT, --7
F_INAMOUNT, --8
PROFITINCOME, --8
PROFITYIELDS, --9
D_DATE,
D_SYSDATE)
values
(c_i.c_taaccountid,
C_I.C_FUNDCODE,
NVL(C_I.f_CURRENTVALUE * v_n_netvalue, 0),
c_i.f_reconfirmamount,
c_i.f_dividendindeed,
c_i.f_purchaseamount,
C_I.f_inamount,
((c_i.F_CURRENTVALUE + c_i.f_reconfirmamount +
c_i.f_dividendindeed) - (c_i.f_purchaseamount + c_i.f_inamount)),
v_profitYields,
c_i.d_date,
C_I.D_SYSDATE);
/* c_taaccountid,
c_fundcode,
F_CURRENTVALUE,
f_reconfirmamount,
f_dividendindeed,
f_purchaseamount,
f_inamoun*/
END LOOP;
--记录日志
v_stepno := 999;
--记载数据处理日志
v_deal_flag := '0';
--批量处理完成记录日志
v_time := to_char(sysdate, 'yyyymmddhh24miss');
PACK_REPORT_LOG.P_BATCHLOG(v_thisprocid,
v_thisprocname,
v_stepno,
v_time,
v_time_flag,
v_deal_flag,
v_note);
COMMIT;
exception
when others then
rollback;
v_time := to_char(sysdate, 'yyyymmddhh24miss');
P_O_FLAG := sqlcode;
v_deal_flag := '1';
v_note := SQLCODE || ':' || SUBSTR(SQLERRM, 1, 200);
--记录日志
/* 处理失败后记存储过程错误日志和存储过程调度日志 */
PACK_REPORT_LOG.P_BATCHLOG_ERR(v_thisprocid,
v_thisprocname,
v_stepno,
v_time,
v_time_flag,
v_deal_flag,
v_note);
raise;
end P_STG_MANAGEMENTFEE;
END PCKG_CIF_PROFIT_BATCH_2;
扫一扫
2933
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
