存储过程-期报提示生成
渠道系列项目根据披露配置生产期报提示
CREATE OR REPLACE PROCEDURE P_REVEAL_PROJECT_TYPE_SCHEME(v_res OUT NUMBER,
v_errorCode OUT NVARCHAR2,
v_errorMsg OUT NVARCHAR2) IS
/* 渠道系列项目根据披露配置生产期报提示 */
/* 期报开始日期 */
d_revealStartDate DATE;
c_revealStartYear NVARCHAR2(4);
/* 期报结束日期 */
d_revealEndDate DATE;
/*家族系统产品受益级别信息数组下标*/
v_count number := 0;
/* 定义截取的配置信息的日期 */
type reveal_end_date_list is record(
revealEndStr t_reveal_project_type_scheme.c_month_date_str%type);
/*期间管理报告提示期报结束日信息变量*/
v_revealEndStr t_reveal_project_type_scheme.c_month_date_str%type;
/*定义数组类型*/
type reveal_end_date_arr_type is table of reveal_end_date_list index by binary_integer;
/* 期间管理报告提示期报结束日数组 */
reveal_end_date_arr reveal_end_date_arr_type;
/*动态游标*/
type sync_cursor is ref cursor;
/* 期间管理报告协议类型期报提示的披露频率日期格式动态游标变量 */
familyProjectRateInfos sync_cursor;
/* 查询是否存在的提示数据的个数 */
v_reveal_tip_count NUMBER(2) := 0;
/* 查询家族信托期报披露频率配置表 */
cursor projectTypeSchemes is
select t.c_co_institution as cropNo,
t.c_reveal_type_name as revealTypeName,
t.reveal_rate as revealRate,
t.c_month_date_str as monthDateStr,
t.c_reveal_due_time as revealDueTime,
t.c_reveal_due_end_time as revealDueEndTime,
t.c_notice_due_time as noticeDueTime,
t.c_remark as remark,
t.c_report_template_id as reportTemplateId
from t_reveal_project_type_scheme t
where t.delete_flag = '0';
/* 查询家族信托项目包含最近期间管理报告的数据 */
cursor projectRevealTips(cropNo NVARCHAR2, revealTypeName NVARCHAR2) is
select t1.project_code as projectCode,
t1.d_setupdate as setupDate,
nvl(t1.d_actual_enddate, to_date('9999-12-31', 'yyyy-MM-dd')) as actualEndDate, --项目实际结束日期
t2.c_period_date_start as periodDateStart, --已存在的期报开始日期
t2.c_period_date_end as periodDateEnd --已存在的期报结束日期
from t_family_project t1
left join (select row_number() over(partition by prr.project_code order by prr.c_period_date_end desc) rn,
prr.project_code,
prr.delete_flag,
prr.c_report_status,
prr.c_period_date_start,
prr.c_period_date_end
from t_project_reveal_report prr) t2
on t1.project_code = t2.project_code
and t2.rn = 1 --移至这里，不影响主表查询
and t2.delete_flag = '0'
where 1 = 1
and t1.project_shortname like '%' || revealTypeName || '%' --配置表的参数
and t1.c_co_institution = cropNo --配置表的参数
and t1.delete_flag = '0'
and t1.c_projectphase <> '03' --除清算阶段外 01-成立阶段; 02-期间管理阶段; 03-清算阶段
--and t1.project_code = '201910804021'
;
BEGIN
--================================================================================
-------------------------------【执行sql文】--------------------------------------
--================================================================================
DBMS_OUTPUT.ENABLE(buffer_size => null); --表示输出buffer不受限制
/* 渠道系列项目根据披露配置生产期报提示 Start */
/* 循环项目协议披露频率配置信息 */
for projectTypeScheme in projectTypeSchemes loop
--循环开始数组下标置0
v_count := 0;
-- 打开指定家族系统产品的受益级别信息游标
open familyProjectRateInfos for
select regexp_substr(t.c_month_date_str, '[^;]+', 1, level) value
from t_reveal_project_type_scheme t
where 1 = 1
and t.c_co_institution = projectTypeScheme.Cropno
and t.c_reveal_type_name = projectTypeScheme.Revealtypename
connect by level <= regexp_count(t.c_month_date_str, '[^;]+')
order by value asc;
/*取一个家族系统产品的受益级别进行同步处理*/
loop
fetch familyProjectRateInfos
into v_revealEndStr;
exit when familyProjectRateInfos%notfound;
reveal_end_date_arr(v_count).revealEndStr := v_revealEndStr;
-- 数组下标+1
v_count := v_count + 1;
end loop;
/* 查询家族信托项目包含最近期间管理报告的数据 */
for projectRevealTip IN projectRevealTips(projectTypeScheme.Cropno,
projectTypeScheme.Revealtypename) loop
--DBMS_OUTPUT.put_line(c_N || '、项目名称：' || projectRevealTip.projectCode);
-- 1、确定期报开始日期
-- 判断是否存存在期间管理报告
if projectRevealTip.periodDateStart is not null and
projectRevealTip.periodDateEnd is not null then
-- 存在上期期报，期报开始日：上期报结束日 + 1
d_revealStartDate := trunc(projectRevealTip.periodDateEnd + 1, 'dd');
DBMS_OUTPUT.put_line('存在上期期报，项目名称：' ||
projectRevealTip.projectCode || '，成立日期：' ||
to_char(projectRevealTip.setupDate,
'yyyy-MM-dd') || '，上期期报结束日：' ||
to_char(projectRevealTip.periodDateEnd,
'yyyy-MM-dd') || '，期报开始日期：' ||
to_char(d_revealStartDate, 'yyyy-MM-dd'));
else
-- 不存在上期期报，期报开始日：成立日
d_revealStartDate := trunc(projectRevealTip.setupDate, 'dd');
DBMS_OUTPUT.put_line('不存在上期期报，项目名称：' ||
projectRevealTip.projectCode || '，成立日期：' ||
to_char(projectRevealTip.setupDate,
'yyyy-MM-dd') || '，期报开始日期：' ||
to_char(d_revealStartDate, 'yyyy-MM-dd'));
end if;
-- 1.2、确定期报结束日期
if reveal_end_date_arr.count > 0 then
-- 期报开始日期的年份
c_revealStartYear := to_char(d_revealStartDate, 'yyyy');
loop
for i in reveal_end_date_arr.first .. reveal_end_date_arr.last loop
-- 期报结束日，节假日顺延
d_revealEndDate := fun_get_workdate(to_date(c_revealStartYear || '-' || reveal_end_date_arr(i).revealEndStr,
'yyyy-MM-dd'),
0);
DBMS_OUTPUT.put_line('计算的期报结束日：' ||
to_char(d_revealEndDate, 'yyyy-MM-dd'));
-- 期报开始日 < 期报结束日 <= 系统日期
if d_revealEndDate > add_months(d_revealStartDate, 2) and
d_revealEndDate <= trunc(sysdate, 'dd') then
DBMS_OUTPUT.put_line('确认的期报结束日期：' ||
to_char(d_revealEndDate, 'yyyy-MM-dd'));
-- 新增期报提示表
-- 根据项目编号、期报开始结束日期，查询期报披露提示表是否存在，不存在则新增
select count(*)
into v_reveal_tip_count
from t_reveal_report_clear_tip t
where 1 = 1
and t.delete_flag = '0'
and t.c_project_code = projectRevealTip.projectCode
and t.d_reveal_start_date = d_revealStartDate
and t.d_reveal_end_date = d_revealEndDate;
if v_reveal_tip_count = 0 then
-- 如果不存在，则新增期报提示表
insert into t_reveal_report_clear_tip
(c_reveal_report_clear_tip_id,
c_project_code,
d_reveal_date,
c_reveal_rate,
d_reveal_start_date,
d_reveal_end_date,
C_REVEAL_DUE_TIME,
C_REVEAL_DUE_END_TIME,
C_NOTICE_DUE_TIME,
C_ISAUTO_REPORT, --是否自动生成期报标识 1-是 0-否
delete_flag,
create_time,
create_user_id,
c_report_template_id)
values
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
projectRevealTip.projectCode,
d_revealEndDate + 1,
projectTypeScheme.Revealrate,
d_revealStartDate,
d_revealEndDate,
projectTypeScheme.Revealduetime,
projectTypeScheme.Revealdueendtime,
projectTypeScheme.Noticeduetime,
'1',
'0',
SYSDATE,
'admin',
projectTypeScheme.Reporttemplateid);
d_revealStartDate := d_revealEndDate + 1;
end if;
end if;
end loop;
if d_revealEndDate >= trunc(sysdate, 'dd') then
exit;
end if;
-- 给下一期期报赋值 = 本期报结束日 + 1
c_revealStartYear := c_revealStartYear + 1;
end loop;
end if;
end loop;
end loop;
/* 渠道系列项目根据披露配置生产期报提示 End */
v_res := 0;
v_errorCode := SQLCODE;
v_errorMsg := 'P_REVEAL_PROJECT_TYPE_SCHEME' || ':' || TO_CHAR(SQLERRM);
DBMS_OUTPUT.put_line('----------------end------------------');
/* 提交 */
commit;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_res := -1;
v_errorCode := SQLCODE;
v_errorMsg := 'P_REVEAL_PROJECT_TYPE_SCHEME' || ':' ||
TO_CHAR(SQLERRM);
DBMS_OUTPUT.put_line('P_REVEAL_PROJECT_TYPE_SCHEME' || ':' || '异常错误为：' ||
sqlerrm || '--' || sqlcode || '--' ||
dbms_utility.format_error_backtrace);
END P_REVEAL_PROJECT_TYPE_SCHEME;
根据期间管理报告披露频率生成期间管理报告的披露日期
CREATE OR REPLACE PROCEDURE P_REVEAL_REPORT_CLEAR_TIP(v_res OUT NUMBER,
v_errorCode OUT NVARCHAR2,
v_errorMsg OUT NVARCHAR2) IS
--根据期间管理报告披露频率生成期间管理报告的披露日期
v_reveal_tip_num NUMBER(8) := 0; -- 查询是否存在的提示数据的个数
c_N NUMBER(10) := 1; -- 计算使用的倍数，从0开始
c_clearDate NVARCHAR2(10); -- 披露日期
c_firstClearDate NVARCHAR2(10); -- 第一次生成的提示日期
c_quarter NVARCHAR2(1); -- 第几季度
c_alloMonth NVARCHAR2(10); -- 特定周期月
c_alloDay NVARCHAR2(10); -- 特定周期日
d_revealStartDate DATE; -- 披露起始日期
d_revealEndDate DATE; -- 披露结束日期
c_startDate DATE; --期报开始日期
--删除的披露频率，并重新生成提示（提示状态为未处理的全部先删除）
CURSOR create_reveal_infos IS
SELECT trr.project_code AS projectCode, --项目编号
trr.reveal_rate AS revealRate, --披露频率
tfp.d_setupdate AS setupDate, --项目成立日期
trr.c_reveal_due_time AS revealDueTime, --披露日期期限
trr.c_notice_due_time AS noticeDueTime, --通知期限
t2.c_period_date_start AS periodDateStart, --已存在的期报开始日期
t2.c_period_date_end AS periodDateEnd --已存在的期报结束日期
FROM t_reveal_report_scheme trr
INNER JOIN t_family_project tfp
on tfp.project_code = trr.project_code
AND tfp.delete_flag = '0'
AND tfp.d_setupdate is not null
left join (select row_number() over(partition by prr.project_code order by prr.c_period_date_end desc) rn,
prr.project_code,
prr.delete_flag,
prr.c_report_status,
prr.c_period_date_start,
prr.c_period_date_end
from t_project_reveal_report prr) t2
on tfp.project_code = t2.project_code
and t2.rn = 1 --移至这里，不影响主表查询
and t2.delete_flag = '0'
WHERE 1 = 1
AND trr.delete_flag = '0'
and tfp.c_projectphase <> '03' --除清算阶段外 01-成立阶段; 02-期间管理阶段; 03-清算阶段
and tfp.project_shortname not like '恒字'
--and tfp.project_code = '201810804082'
;
BEGIN
--================================================================================
-------------------------------【执行sql文】--------------------------------------
--================================================================================
/* 期间管理报告清算提示 Start */
-- 1.从合同信息中生成频率规则实时调用生成提示信息
-- 生成期间管理报告清算提示的披露日期
FOR create_reveal_info IN create_reveal_infos LOOP
-- 从期报开始日开始
if create_reveal_info.perioddateend is null then
-- 如果不存在期报，则以成立日开始
c_startDate := create_reveal_info.setupdate;
else
-- 如果存在最新一期期报结束日，则开始日为期报结束日+1
c_startDate := create_reveal_info.perioddateend + 1;
end if;
-- 每次循环初始化计算倍数
c_N := 1;
-- 自然年
IF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'Y') THEN
-- 生成第一次提示日期
SELECT extract(YEAR FROM c_startDate) || '-' || '01' || '-' || '01'
INTO c_firstClearDate
FROM dual;
c_clearDate := c_firstClearDate;
-- 进入循环获取披露日期
LOOP
-- 生成披露日期小于等系统日期的提示数据
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -12) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 直接生成期间管理报告清算提示信息
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
UPDATE_TIME,
UPDATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
else
exit;
END IF;
-- 用第一次提示日期计算下一次的提示日期
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 12),
'yyyy-MM-') || '01'
INTO c_clearDate
FROM dual;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
-- 自然半年（每年的7月1日）
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'H') THEN
--系统日期同7月份进行比较
IF (to_char(c_startDate, 'mmdd') > '0101') AND
(to_char(c_startDate, 'mmdd') <= '0701') THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '07' || '-' || '01'
INTO c_firstClearDate
FROM dual;
ELSE
SELECT extract(YEAR FROM c_startDate) || '-' || '01' || '-' || '01'
INTO c_firstClearDate
FROM dual;
END IF;
c_clearDate := c_firstClearDate;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -6) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 直接生成期间管理报告清算提示信息
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
UPDATE_TIME,
UPDATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
END IF;
-- 用第一次提示日期计算下一次的提示日期
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 12),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
-- 自然季度（1月1日，4月1日，7月1日，10月1日）
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'Q') THEN
-- 通过传入日期，计算出当前所在季度
SELECT to_char(c_startDate, 'Q') INTO c_quarter FROM dual;
IF c_quarter = '1' THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '01' || '-' || '01'
INTO c_firstClearDate
FROM dual;
ELSIF c_quarter = '2' THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '04' || '-' || '01'
INTO c_firstClearDate
FROM dual;
ELSIF c_quarter = '3' THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '07' || '-' || '01'
INTO c_firstClearDate
FROM dual;
ELSIF c_quarter = '4' THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '10' || '-' || '01'
INTO c_firstClearDate
FROM dual;
END IF;
c_clearDate := c_firstClearDate;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 生成披露起始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -3) into d_revealStartDate FROM dual;
-- 生成披露结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 直接生成期间管理报告清算提示信息
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
UPDATE_TIME,
UPDATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
END IF;
-- 用第一次提示日期计算下一次的提示日期
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 3),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
-- 信托年
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'TY') THEN
-- 当成立日不为空时生成提示信息
IF create_reveal_info.setupdate IS NOT NULL THEN
-- 获取成立日期
SELECT to_char(create_reveal_info.setupdate, '-mm')
INTO c_alloMonth
FROM dual;
SELECT to_char(create_reveal_info.setupdate, '-dd')
INTO c_alloDay
FROM dual;
-- 得到第一次披露日期
select to_char(c_startDate, 'yyyy') || c_alloMonth || c_alloDay
INTO c_firstClearDate
FROM dual;
c_clearDate := c_firstClearDate;
-- 如果生成日期不是有效日期，则取当月最后一天日期
IF is_date(c_clearDate) = 0 THEN
SELECT to_char(last_day(to_date(substr(c_clearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -12) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 新增期报披露提示表
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
UPDATE_TIME,
UPDATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
ELSE
EXIT;
END IF;
-- 用第一次提示日期计算下一次的提示日期
-- 如果第一次提示日期不是有效日期，则取当月最后一天日期
IF is_date(c_firstClearDate) = 0 THEN
-- 先将第一次日期变为有效日期
SELECT to_char(last_day(to_date(substr(c_firstClearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_firstClearDate
FROM dual;
-- 再生成下一次日期
SELECT to_char(add_months(to_date(c_firstClearDate,
'yyyy-mm-dd'),
c_N * 12),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
ELSE
-- 如果是有效日期，则直接生成下一次日期
SELECT to_char(add_months(to_date(c_firstClearDate,
'yyyy-mm-dd'),
c_N * 12),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 如果生成日期不是有效日期，则取当月最后一天日期
IF is_date(c_clearDate) = 0 THEN
SELECT to_char(last_day(to_date(substr(c_clearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
END IF;
-- 信托半年
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'TH') THEN
-- 当成立日不为空时生成提示信息
IF create_reveal_info.setupdate IS NOT NULL THEN
-- 变量赋值
SELECT to_char(create_reveal_info.setupdate, 'yyyy-MM-dd')
INTO c_firstClearDate
FROM dual;
SELECT to_char(create_reveal_info.setupdate, 'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -6) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 新增期报披露提示表
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
UPDATE_TIME,
UPDATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
END IF;
-- 用第一次获取的成立日期计算下一次的提示日期（不需要对成立日期做判断日期是否有效）
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 6),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 如果生成日期不是有效日期，则取当月最后一天日期
IF is_date(c_clearDate) = 0 THEN
SELECT to_char(last_day(to_date(substr(c_clearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
END IF;
-- 信托季度
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'TQ') THEN
-- 当成立日不为空时生成提示信息
IF create_reveal_info.setupdate IS NOT NULL THEN
-- 变量赋值
SELECT to_char(create_reveal_info.setupdate, 'yyyy-MM-dd')
INTO c_firstClearDate
FROM dual;
SELECT to_char(create_reveal_info.setupdate, 'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -3) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 新增期报披露提示表
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
UPDATE_TIME,
UPDATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
END IF;
-- 用第一次获取的成立日期计算下一次的提示日期（不需要对成立日期做判断日期是否有效）
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 3),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 如果生成日期不是有效日期，则取当月最后一天日期
IF is_date(c_clearDate) = 0 THEN
SELECT to_char(last_day(to_date(substr(c_clearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
END IF;
END IF;
END LOOP;
--输出放回状态信息
v_res := 0;
v_errorCode := SQLCODE;
v_errorMsg := 'P_REVEAL_REPORT_CLEAR_TIP' || ':' || TO_CHAR(SQLERRM);
COMMIT;
/* 期间管理报告清算提示 End */
--异常处理
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_res := -1;
v_errorCode := SQLCODE;
v_errorMsg := 'P_REVEAL_REPORT_CLEAR_TIP' || ':' || TO_CHAR(SQLERRM);
DBMS_OUTPUT.put_line('P_REVEAL_REPORT_CLEAR_TIP' || ':' || '异常错误为：' ||
sqlerrm || '--' || sqlcode || '--' ||
dbms_utility.format_error_backtrace);
END P_REVEAL_REPORT_CLEAR_TIP;
合并之后的结果
CREATE OR REPLACE PROCEDURE P_REVEAL_REPORT_CLEAR_TIP(v_res OUT NUMBER,
v_errorCode OUT NVARCHAR2,
v_errorMsg OUT NVARCHAR2) IS
--根据期间管理报告披露频率生成期间管理报告的披露日期
v_reveal_tip_num NUMBER(8) := 0; -- 查询是否存在的提示数据的个数
c_N NUMBER(10) := 1; -- 计算使用的倍数，从0开始
c_clearDate NVARCHAR2(10); -- 披露日期
c_firstClearDate NVARCHAR2(10); -- 第一次生成的提示日期
c_quarter NVARCHAR2(1); -- 第几季度
c_alloMonth NVARCHAR2(10); -- 特定周期月
c_alloDay NVARCHAR2(10); -- 特定周期日
d_revealStartDate DATE; -- 披露起始日期
d_revealEndDate DATE; -- 披露结束日期
c_startDate DATE; --期报开始日期
/* 恒字系列期报开始日期 */
d_revealTreatyStartDate DATE;
/* 恒字系列期报结束日期 */
d_revealTreatyEndDate DATE;
/* 期报开始日期年份 */
c_revealStartYear NVARCHAR2(4);
/*家族系统产品受益级别信息数组下标*/
v_count number := 0;
/* 定义截取的配置信息的日期 */
type reveal_end_date_list is record(
revealEndStr t_reveal_project_type_scheme.c_month_date_str%type);
/*期间管理报告提示期报结束日信息变量*/
v_revealEndStr t_reveal_project_type_scheme.c_month_date_str%type;
/*定义数组类型*/
type reveal_end_date_arr_type is table of reveal_end_date_list index by binary_integer;
/* 期间管理报告提示期报结束日数组 */
reveal_end_date_arr reveal_end_date_arr_type;
/*动态游标*/
type sync_cursor is ref cursor;
/* 期间管理报告协议类型期报提示的披露频率日期格式动态游标变量 */
familyProjectRateInfos sync_cursor;
/* 查询是否存在的提示数据的个数 */
v_reveal_tip_count NUMBER(2) := 0;
--删除的披露频率，并重新生成提示（提示状态为未处理的全部先删除）
CURSOR create_reveal_infos IS
SELECT trr.project_code AS projectCode, --项目编号
trr.reveal_rate AS revealRate, --披露频率
tfp.d_setupdate AS setupDate, --项目成立日期
trr.c_reveal_due_time AS revealDueTime, --披露日期期限
trr.c_notice_due_time AS noticeDueTime, --通知期限
t2.c_period_date_start AS periodDateStart, --已存在的期报开始日期
t2.c_period_date_end AS periodDateEnd --已存在的期报结束日期
FROM t_reveal_report_scheme trr
INNER JOIN t_family_project tfp
on tfp.project_code = trr.project_code
AND tfp.delete_flag = '0'
AND tfp.d_setupdate is not null
left join (select row_number() over(partition by prr.project_code order by prr.c_period_date_end desc) rn,
prr.project_code,
prr.delete_flag,
prr.c_report_status,
prr.c_period_date_start,
prr.c_period_date_end
from t_project_reveal_report prr) t2
on tfp.project_code = t2.project_code
and t2.rn = 1 --移至这里，不影响主表查询
and t2.delete_flag = '0'
WHERE 1 = 1
AND trr.delete_flag = '0'
and tfp.c_projectphase <> '03' --除清算阶段外 01-成立阶段; 02-期间管理阶段; 03-清算阶段
and tfp.project_shortname not like '恒字'
--and tfp.project_code = '201810804082'
;
/* 查询家族信托期报披露频率配置表 */
cursor projectTypeSchemes is
select t.c_co_institution as cropNo,
t.c_reveal_type_name as revealTypeName,
t.reveal_rate as revealRate,
t.c_month_date_str as monthDateStr,
t.c_reveal_due_time as revealDueTime,
t.c_reveal_due_end_time as revealDueEndTime,
t.c_notice_due_time as noticeDueTime,
t.c_remark as remark,
t.c_report_template_id as reportTemplateId
from t_reveal_project_type_scheme t
where t.delete_flag = '0';
/* 查询家族信托项目包含最近期间管理报告的数据 */
cursor projectRevealTips(cropNo NVARCHAR2, revealTypeName NVARCHAR2) is
select t1.project_code as projectCode,
t1.d_setupdate as setupDate,
nvl(t1.d_actual_enddate, to_date('9999-12-31', 'yyyy-MM-dd')) as actualEndDate, --项目实际结束日期
t2.c_period_date_start as periodDateStart, --已存在的期报开始日期
t2.c_period_date_end as periodDateEnd --已存在的期报结束日期
from t_family_project t1
left join (select row_number() over(partition by prr.project_code order by prr.c_period_date_end desc) rn,
prr.project_code,
prr.delete_flag,
prr.c_report_status,
prr.c_period_date_start,
prr.c_period_date_end
from t_project_reveal_report prr) t2
on t1.project_code = t2.project_code
and t2.rn = 1 --移至这里，不影响主表查询
and t2.delete_flag = '0'
where 1 = 1
and t1.project_shortname like '%' || revealTypeName || '%' --配置表的参数
and t1.c_co_institution = cropNo --配置表的参数
and t1.delete_flag = '0'
and t1.c_projectphase <> '03' --除清算阶段外 01-成立阶段; 02-期间管理阶段; 03-清算阶段
--and t1.project_code = '201910804021'
;
BEGIN
--================================================================================
-------------------------------【执行sql文】--------------------------------------
--================================================================================
DBMS_OUTPUT.ENABLE(buffer_size => null); --表示输出buffer不受限制
/* 期间管理报告清算提示 Start */
-- 1.从合同信息中生成频率规则实时调用生成提示信息
-- 生成期间管理报告清算提示的披露日期
FOR create_reveal_info IN create_reveal_infos LOOP
-- 从期报开始日开始
if create_reveal_info.perioddateend is null then
-- 如果不存在期报，则以成立日开始
c_startDate := create_reveal_info.setupdate;
else
-- 如果存在最新一期期报结束日，则开始日为期报结束日+1
c_startDate := create_reveal_info.perioddateend + 1;
end if;
-- 每次循环初始化计算倍数
c_N := 1;
-- 自然年
IF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'Y') THEN
-- 生成第一次提示日期
SELECT extract(YEAR FROM c_startDate) || '-' || '01' || '-' || '01'
INTO c_firstClearDate
FROM dual;
c_clearDate := c_firstClearDate;
-- 进入循环获取披露日期
LOOP
-- 生成披露日期小于等系统日期的提示数据
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -12) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 直接生成期间管理报告清算提示信息
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
else
exit;
END IF;
-- 用第一次提示日期计算下一次的提示日期
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 12),
'yyyy-MM-') || '01'
INTO c_clearDate
FROM dual;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
-- 自然半年（每年的7月1日）
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'H') THEN
--系统日期同7月份进行比较
IF (to_char(c_startDate, 'mmdd') > '0101') AND
(to_char(c_startDate, 'mmdd') <= '0701') THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '07' || '-' || '01'
INTO c_firstClearDate
FROM dual;
ELSE
SELECT extract(YEAR FROM c_startDate) || '-' || '01' || '-' || '01'
INTO c_firstClearDate
FROM dual;
END IF;
c_clearDate := c_firstClearDate;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -6) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 直接生成期间管理报告清算提示信息
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
END IF;
-- 用第一次提示日期计算下一次的提示日期
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 12),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
-- 自然季度（1月1日，4月1日，7月1日，10月1日）
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'Q') THEN
-- 通过传入日期，计算出当前所在季度
SELECT to_char(c_startDate, 'Q') INTO c_quarter FROM dual;
IF c_quarter = '1' THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '01' || '-' || '01'
INTO c_firstClearDate
FROM dual;
ELSIF c_quarter = '2' THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '04' || '-' || '01'
INTO c_firstClearDate
FROM dual;
ELSIF c_quarter = '3' THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '07' || '-' || '01'
INTO c_firstClearDate
FROM dual;
ELSIF c_quarter = '4' THEN
SELECT extract(YEAR FROM c_startDate) || '-' || '10' || '-' || '01'
INTO c_firstClearDate
FROM dual;
END IF;
c_clearDate := c_firstClearDate;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 生成披露起始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -3) into d_revealStartDate FROM dual;
-- 生成披露结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 直接生成期间管理报告清算提示信息
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
END IF;
-- 用第一次提示日期计算下一次的提示日期
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 3),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
-- 信托年
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'TY') THEN
-- 当成立日不为空时生成提示信息
IF create_reveal_info.setupdate IS NOT NULL THEN
-- 获取成立日期
SELECT to_char(create_reveal_info.setupdate, '-mm')
INTO c_alloMonth
FROM dual;
SELECT to_char(create_reveal_info.setupdate, '-dd')
INTO c_alloDay
FROM dual;
-- 得到第一次披露日期
select to_char(c_startDate, 'yyyy') || c_alloMonth || c_alloDay
INTO c_firstClearDate
FROM dual;
c_clearDate := c_firstClearDate;
-- 如果生成日期不是有效日期，则取当月最后一天日期
IF is_date(c_clearDate) = 0 THEN
SELECT to_char(last_day(to_date(substr(c_clearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -12) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 新增期报披露提示表
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
ELSE
EXIT;
END IF;
-- 用第一次提示日期计算下一次的提示日期
-- 如果第一次提示日期不是有效日期，则取当月最后一天日期
IF is_date(c_firstClearDate) = 0 THEN
-- 先将第一次日期变为有效日期
SELECT to_char(last_day(to_date(substr(c_firstClearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_firstClearDate
FROM dual;
-- 再生成下一次日期
SELECT to_char(add_months(to_date(c_firstClearDate,
'yyyy-mm-dd'),
c_N * 12),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
ELSE
-- 如果是有效日期，则直接生成下一次日期
SELECT to_char(add_months(to_date(c_firstClearDate,
'yyyy-mm-dd'),
c_N * 12),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 如果生成日期不是有效日期，则取当月最后一天日期
IF is_date(c_clearDate) = 0 THEN
SELECT to_char(last_day(to_date(substr(c_clearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
END IF;
-- 信托半年
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'TH') THEN
-- 当成立日不为空时生成提示信息
IF create_reveal_info.setupdate IS NOT NULL THEN
-- 变量赋值
SELECT to_char(create_reveal_info.setupdate, 'yyyy-MM-dd')
INTO c_firstClearDate
FROM dual;
SELECT to_char(create_reveal_info.setupdate, 'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -6) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 新增期报披露提示表
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
END IF;
-- 用第一次获取的成立日期计算下一次的提示日期（不需要对成立日期做判断日期是否有效）
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 6),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 如果生成日期不是有效日期，则取当月最后一天日期
IF is_date(c_clearDate) = 0 THEN
SELECT to_char(last_day(to_date(substr(c_clearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
END IF;
-- 信托季度
ELSIF (create_reveal_info.revealRate IS NOT NULL) AND
(create_reveal_info.revealRate = 'TQ') THEN
-- 当成立日不为空时生成提示信息
IF create_reveal_info.setupdate IS NOT NULL THEN
-- 变量赋值
SELECT to_char(create_reveal_info.setupdate, 'yyyy-MM-dd')
INTO c_firstClearDate
FROM dual;
SELECT to_char(create_reveal_info.setupdate, 'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 进入循环获取披露日期
LOOP
-- 当披露日期大于系统日期，得生成期间管理报告清算提示信息
IF c_clearDate <= to_char(trunc(sysdate, 'dd'), 'yyyy-MM-dd') THEN
-- 查询是否存在该提示
SELECT COUNT(*)
INTO v_reveal_tip_num
from t_reveal_report_clear_tip trc
where trc.c_project_code = create_reveal_info.projectCode
and trc.c_reveal_rate = create_reveal_info.revealRate
and trc.d_reveal_date = to_date(c_clearDate, 'yyyy-MM-dd')
and trc.delete_flag = '0';
-- 若不存在，则生成提示信息
IF v_reveal_tip_num = 0 THEN
-- 期报开始日期
d_revealStartDate := c_startDate;
--SELECT add_months(to_date(c_clearDate,'yyyy-MM-dd'), -3) into d_revealStartDate FROM dual;
-- 期报结束日期
select to_date(c_clearDate, 'yyyy-MM-dd') - 1
into d_revealEndDate
from dual;
-- 期报结束日大于等于开始日+2个月
if add_months(d_revealStartDate, 2) <= d_revealEndDate then
-- 新增期报披露提示表
INSERT INTO T_REVEAL_REPORT_CLEAR_TIP
(C_REVEAL_REPORT_CLEAR_TIP_ID,
C_PROJECT_CODE,
D_REVEAL_DATE,
C_REVEAL_RATE,
C_ISAUTO_REPORT,
DELETE_FLAG,
CREATE_TIME,
CREATE_USER_ID,
C_REVEAL_DUE_TIME,
C_NOTICE_DUE_TIME,
D_REVEAL_START_DATE,
D_REVEAL_END_DATE)
VALUES
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
create_reveal_info.projectCode,
to_date(c_clearDate, 'yyyy-MM-dd'),
create_reveal_info.revealRate,
'0',
'0',
SYSDATE,
'admin',
create_reveal_info.revealduetime,
create_reveal_info.noticeduetime,
d_revealStartDate,
d_revealEndDate);
-- 赋值下一期期报开始日
c_startDate := d_revealEndDate + 1;
end if;
END IF;
END IF;
-- 用第一次获取的成立日期计算下一次的提示日期（不需要对成立日期做判断日期是否有效）
SELECT to_char(add_months(to_date(c_firstClearDate, 'yyyy-mm-dd'),
c_N * 3),
'yyyy-MM-dd')
INTO c_clearDate
FROM dual;
-- 如果生成日期不是有效日期，则取当月最后一天日期
IF is_date(c_clearDate) = 0 THEN
SELECT to_char(last_day(to_date(substr(c_clearDate, 0, 7) ||
'-01',
'yyyy-mm-dd')),
'yyyy-mm-dd')
INTO c_clearDate
FROM dual;
END IF;
-- 生成小于系统日期的提示
if to_date(c_clearDate, 'yyyy-MM-dd') >= trunc(sysdate, 'dd') then
EXIT;
end if;
c_N := c_N + 1;
END LOOP;
END IF;
END IF;
END LOOP;
/* 渠道系列项目根据披露配置生产期报提示 Start */
/* 循环项目协议披露频率配置信息 */
for projectTypeScheme in projectTypeSchemes loop
--循环开始数组下标置0
v_count := 0;
-- 打开指定家族系统产品的受益级别信息游标
open familyProjectRateInfos for
select regexp_substr(t.c_month_date_str, '[^;]+', 1, level) value
from t_reveal_project_type_scheme t
where 1 = 1
and t.c_co_institution = projectTypeScheme.Cropno
and t.c_reveal_type_name = projectTypeScheme.Revealtypename
connect by level <= regexp_count(t.c_month_date_str, '[^;]+')
order by value asc;
/*取一个家族系统产品的受益级别进行同步处理*/
loop
fetch familyProjectRateInfos
into v_revealEndStr;
exit when familyProjectRateInfos%notfound;
reveal_end_date_arr(v_count).revealEndStr := v_revealEndStr;
-- 数组下标+1
v_count := v_count + 1;
end loop;
/* 查询家族信托项目包含最近期间管理报告的数据 */
for projectRevealTip IN projectRevealTips(projectTypeScheme.Cropno,
projectTypeScheme.Revealtypename) loop
--DBMS_OUTPUT.put_line(c_N || '、项目名称：' || projectRevealTip.projectCode);
-- 1、确定期报开始日期
-- 判断是否存存在期间管理报告
if projectRevealTip.periodDateStart is not null and
projectRevealTip.periodDateEnd is not null then
-- 存在上期期报，期报开始日：上期报结束日 + 1
d_revealTreatyStartDate := trunc(projectRevealTip.periodDateEnd + 1,
'dd');
DBMS_OUTPUT.put_line('存在上期期报，项目名称：' ||
projectRevealTip.projectCode || '，成立日期：' ||
to_char(projectRevealTip.setupDate,
'yyyy-MM-dd') || '，上期期报结束日：' ||
to_char(projectRevealTip.periodDateEnd,
'yyyy-MM-dd') || '，期报开始日期：' ||
to_char(d_revealTreatyStartDate, 'yyyy-MM-dd'));
else
-- 不存在上期期报，期报开始日：成立日
d_revealTreatyStartDate := trunc(projectRevealTip.setupDate, 'dd');
DBMS_OUTPUT.put_line('不存在上期期报，项目名称：' ||
projectRevealTip.projectCode || '，成立日期：' ||
to_char(projectRevealTip.setupDate,
'yyyy-MM-dd') || '，期报开始日期：' ||
to_char(d_revealTreatyStartDate, 'yyyy-MM-dd'));
end if;
-- 1.2、确定期报结束日期
if reveal_end_date_arr.count > 0 then
-- 期报开始日期的年份
c_revealStartYear := to_char(d_revealTreatyStartDate, 'yyyy');
loop
for i in reveal_end_date_arr.first .. reveal_end_date_arr.last loop
-- 期报结束日，节假日顺延
d_revealTreatyEndDate := fun_get_workdate(to_date(c_revealStartYear || '-' || reveal_end_date_arr(i).revealEndStr,
'yyyy-MM-dd'),
0);
-- DBMS_OUTPUT.put_line('计算的期报结束日：' || to_char(d_revealTreatyEndDate, 'yyyy-MM-dd'));
-- 期报开始日 < 期报结束日 <= 系统日期
if d_revealTreatyEndDate >
add_months(d_revealTreatyStartDate, 2) and
d_revealTreatyEndDate <= trunc(sysdate, 'dd') then
-- DBMS_OUTPUT.put_line('确认的期报结束日期：' || to_char(d_revealTreatyEndDate, 'yyyy-MM-dd'));
-- 新增期报提示表
-- 根据项目编号、期报开始结束日期，查询期报披露提示表是否存在，不存在则新增
select count(*)
into v_reveal_tip_count
from t_reveal_report_clear_tip t
where 1 = 1
and t.delete_flag = '0'
and t.c_project_code = projectRevealTip.projectCode
and t.d_reveal_start_date = d_revealTreatyStartDate
and t.d_reveal_end_date = d_revealTreatyEndDate;
DBMS_OUTPUT.put_line('计算的期报开始日：' ||
to_char(d_revealTreatyStartDate,
'yyyy-MM-dd') || '，计算的期报的结束日:' ||
to_char(d_revealTreatyEndDate,
'yyyy-MM-dd'));
if v_reveal_tip_count = 0 then
-- 如果不存在，则新增期报提示表
insert into t_reveal_report_clear_tip
(c_reveal_report_clear_tip_id,
c_project_code,
d_reveal_date,
c_reveal_rate,
d_reveal_start_date,
d_reveal_end_date,
C_REVEAL_DUE_TIME,
C_REVEAL_DUE_END_TIME,
C_NOTICE_DUE_TIME,
C_ISAUTO_REPORT, --是否自动生成期报标识 1-是 0-否
delete_flag,
create_time,
create_user_id,
c_report_template_id)
values
('RCT' || to_char(SYSDATE, 'YYYYMMDDHH24MISS') ||
SEQ_ORIGINAL_DEFAULT.NEXTVAL,
projectRevealTip.projectCode,
d_revealTreatyEndDate + 1,
projectTypeScheme.Revealrate,
d_revealTreatyStartDate,
d_revealTreatyEndDate,
projectTypeScheme.Revealduetime,
projectTypeScheme.Revealdueendtime,
projectTypeScheme.Noticeduetime,
'2',
'0',
SYSDATE,
'admin',
projectTypeScheme.Reporttemplateid);
v_reveal_tip_count := 0;
end if;
end if;
-- 给下一期期报赋值 = 本期报结束日 + 1
d_revealTreatyStartDate := d_revealTreatyEndDate + 1;
end loop;
-- 当计算出来的期报结束日大于 系统日期，则跳出
if d_revealTreatyEndDate > trunc(sysdate, 'dd') then
exit;
end if;
-- 拼接的年份 + 1
c_revealStartYear := c_revealStartYear + 1;
end loop;
end if;
end loop;
end loop;
--输出放回状态信息
v_res := 0;
v_errorCode := SQLCODE;
v_errorMsg := 'P_REVEAL_REPORT_CLEAR_TIP' || ':' || TO_CHAR(SQLERRM);
COMMIT;
/* 期间管理报告清算提示 End */
--异常处理
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_res := -1;
v_errorCode := SQLCODE;
v_errorMsg := 'P_REVEAL_REPORT_CLEAR_TIP' || ':' || TO_CHAR(SQLERRM);
DBMS_OUTPUT.put_line('P_REVEAL_REPORT_CLEAR_TIP' || ':' || '异常错误为：' ||
sqlerrm || '--' || sqlcode || '--' ||
dbms_utility.format_error_backtrace);
END P_REVEAL_REPORT_CLEAR_TIP;
人生有几个十年呢？点点滴滴，用文字记录
