存储过程：期报提示（含有数组）
改造前-期报提示（含有数组）
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;
/* 期报结束日期 */
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;
/* 是否存在这期期报结束日 0-不存在 1-存在 */
v_endDate_flag number := 0;
/* 查询是否存在的提示数据的个数 */
v_reveal_tip_num NUMBER(2) := 0;
/* 计算使用的倍数，从0开始 */
c_N NUMBER(10) := 1;
/* 查询家族信托期报披露频率配置表 */
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
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'
and t2.c_report_status <> 'B05' --期报指令状态：除去已退回的所有状态
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 = '202010804036'
;
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(c_N || '、存在上期期报，项目名称：' ||
projectRevealTip.projectCode || '，成立日期：' ||
to_char(projectRevealTip.setupDate,
'yyyy-MM-dd') || '，上期期报结束日：' ||
to_char(projectRevealTip.periodDateEnd,
'yyyy-MM-dd') || '，期报开始日期：' ||
to_char(d_revealStartDate, 'yyyy-MM-dd'));
/* 1.2、确定期报结束日期 */
if reveal_end_date_arr.count > 0 then
for i in reveal_end_date_arr.first .. reveal_end_date_arr.last loop
v_endDate_flag := 0;
DBMS_OUTPUT.put_line('配置日期：' ||
to_char(d_revealStartDate, 'yyyy') || '-' || reveal_end_date_arr(i).revealEndStr);
/* 期报结束日 */
d_revealEndDate := to_date(to_char(d_revealStartDate, 'yyyy') || '-' || reveal_end_date_arr(i).revealEndStr,
'yyyy-MM-dd');
-- 期报开始日 < 期报结束日 <= 系统日期
if d_revealEndDate > d_revealStartDate and
d_revealEndDate <= trunc(sysdate, 'dd') then
DBMS_OUTPUT.put_line('期报结束日期：' ||
to_char(d_revealEndDate, 'yyyy-MM-dd'));
v_endDate_flag := 1;
/* 新增期报提示表 */
/* 根据项目编号、期报开始结束日期，查询期报披露提示表是否存在，不存在则新增 */
select count(*)
into v_reveal_tip_num
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_num = 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)
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');
end if;
exit;
end if;
end loop;
DBMS_OUTPUT.put_line('是否存在期报结束日：' || v_endDate_flag);
-- 如果不存在期报结束日，则年份加1，继续计算
if v_endDate_flag = 0 then
for i in reveal_end_date_arr.first .. reveal_end_date_arr.last loop
v_endDate_flag := 0;
DBMS_OUTPUT.put_line('配置日期2：' || to_char(add_months(d_revealStartDate,
12),
'yyyy') || '-' || reveal_end_date_arr(i).revealEndStr);
/* 期报结束日 */
d_revealEndDate := to_date(to_char(add_months(d_revealStartDate,
12),
'yyyy') || '-' || reveal_end_date_arr(i).revealEndStr,
'yyyy-MM-dd');
if d_revealEndDate > d_revealStartDate and
d_revealEndDate <= trunc(sysdate, 'dd') then
DBMS_OUTPUT.put_line('期报结束日期2：' ||
to_char(d_revealEndDate, 'yyyy-MM-dd'));
v_endDate_flag := 1;
/* 新增期报提示表 */
/* 根据项目编号、期报开始结束日期，查询期报披露提示表是否存在，不存在则新增 */
select count(*)
into v_reveal_tip_num
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_num = 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)
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');
end if;
exit;
end if;
end loop;
DBMS_OUTPUT.put_line('是否存在期报结束日2：' || v_endDate_flag);
end if;
end if;
else
/* 不存在上期期报，期报开始日：项目成立日 */
d_revealStartDate := trunc(projectRevealTip.setupDate, 'dd');
DBMS_OUTPUT.put_line(c_N || '、不存在上期期报，项目名称：' ||
projectRevealTip.projectCode || '，成立日期：' ||
to_char(projectRevealTip.setupDate,
'yyyy-MM-dd') || '，期报开始日期：' ||
to_char(d_revealStartDate, 'yyyy-MM-dd'));
/* 1.2 确定期报结束日 */
if reveal_end_date_arr.count > 0 then
for i in reveal_end_date_arr.first .. reveal_end_date_arr.last loop
v_endDate_flag := 0;
DBMS_OUTPUT.put_line('配置日期：' ||
to_char(d_revealStartDate, 'yyyy') || '-' || reveal_end_date_arr(i).revealEndStr);
/* 期报结束日 */
d_revealEndDate := fun_get_workdate(to_date(to_char(d_revealStartDate,
'yyyy') || '-' || reveal_end_date_arr(i).revealEndStr,
'yyyy-MM-dd'),
0);
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'));
v_endDate_flag := 1;
/* 新增期报提示表 */
/* 根据项目编号、期报开始结束日期，查询期报披露提示表是否存在，不存在则新增 */
select count(*)
into v_reveal_tip_num
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_num = 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)
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');
end if;
exit;
end if;
end loop;
DBMS_OUTPUT.put_line('是否存在期报结束日：' || v_endDate_flag);
-- 如果不存在期报结束日，则年份加1，继续计算
if v_endDate_flag = 0 then
for i in reveal_end_date_arr.first .. reveal_end_date_arr.last loop
v_endDate_flag := 0;
DBMS_OUTPUT.put_line('配置日期2：' || to_char(add_months(d_revealStartDate,
12),
'yyyy') || '-' || reveal_end_date_arr(i).revealEndStr);
/* 期报结束日 */
d_revealEndDate := fun_get_workdate(to_date(to_char(add_months(d_revealStartDate,
12),
'yyyy') || '-' || reveal_end_date_arr(i).revealEndStr,
'yyyy-MM-dd'),
0);
if d_revealEndDate > d_revealStartDate and
d_revealEndDate <= trunc(sysdate, 'dd') then
DBMS_OUTPUT.put_line('期报结束日期2：' ||
to_char(d_revealEndDate, 'yyyy-MM-dd'));
v_endDate_flag := 1;
/* 新增期报提示表 */
/* 根据项目编号、期报开始结束日期，查询期报披露提示表是否存在，不存在则新增 */
select count(*)
into v_reveal_tip_num
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_num = 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)
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');
end if;
exit;
else
-- 此时还不满足，则直接退出
exit;
end if;
end loop;
DBMS_OUTPUT.put_line('是否存在期报结束日2：' || v_endDate_flag);
end if;
end if;
end if;
c_N := c_N + 1;
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_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;
/* 是否存在这期期报结束日 0-不存在 1-存在 */
v_endDate_flag number := 0;
/* 查询是否存在的提示数据的个数 */
v_reveal_tip_num NUMBER(2) := 0;
/* 计算使用的倍数，从0开始 */
c_N NUMBER(10) := 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'
and t2.c_report_status <> 'B05' --期报指令状态：除去已退回的所有状态
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 = '202010804056'
;
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(c_N || '、存在上期期报，项目名称：' ||
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(c_N || '、不存在上期期报，项目名称：' ||
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_num
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_num = 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;
c_revealStartYear := c_revealStartYear + 1;
end loop;
DBMS_OUTPUT.put_line('是否存在期报结束日：' || v_endDate_flag);
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;
T_REVEAL_PROJECT_TYPE_SCHEME建表语句
-- Create table
create table T_REVEAL_PROJECT_TYPE_SCHEME
(
id NVARCHAR2(36) not null,
c_co_institution NVARCHAR2(10),
c_reveal_type_name NVARCHAR2(36),
reveal_rate NVARCHAR2(5),
c_month_date_str NVARCHAR2(36),
c_reveal_due_time NVARCHAR2(10),
c_reveal_due_end_time NVARCHAR2(10),
c_notice_due_time NVARCHAR2(10),
c_remark NVARCHAR2(200),
delete_flag NVARCHAR2(1),
create_time TIMESTAMP(6),
create_user_id NVARCHAR2(36),
update_time TIMESTAMP(6),
update_user_id NVARCHAR2(36)
)
tablespace FAMILY226
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table T_REVEAL_PROJECT_TYPE_SCHEME
is '项目披露报告配置表';
-- Add comments to the columns
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.id
is '主键ID';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.c_co_institution
is '机构编码';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.c_reveal_type_name
is '项目系列关键字';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.reveal_rate
is '披露频率';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.c_month_date_str
is '月份日期';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.c_reveal_due_time
is '披露日期期限开始';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.c_reveal_due_end_time
is '披露日期期限结束';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.c_notice_due_time
is '通知期限';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.c_remark
is '备注';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.delete_flag
is '删除标识';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.create_time
is '创建时间';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.create_user_id
is '创建者';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.update_time
is '更新时间';
comment on column T_REVEAL_PROJECT_TYPE_SCHEME.update_user_id
is '更新者';
T_REVEAL_REPORT_CLEAR_TIP建表语句
-- Create table
create table T_REVEAL_REPORT_CLEAR_TIP
(
c_reveal_report_clear_tip_id NVARCHAR2(36) not null,
c_project_code NVARCHAR2(36) not null,
d_reveal_date DATE not null,
c_tip_status NVARCHAR2(1),
delete_flag NVARCHAR2(1) not null,
create_time TIMESTAMP(6),
create_user_id NVARCHAR2(36),
update_time TIMESTAMP(6),
update_user_id NVARCHAR2(36),
c_reveal_rate NVARCHAR2(36),
c_reveal_due_time NVARCHAR2(10),
c_notice_due_time NVARCHAR2(10),
d_reveal_start_date DATE,
d_reveal_end_date DATE,
c_reveal_due_end_time NVARCHAR2(10),
c_period_report_id NVARCHAR2(36),
c_isauto_report NVARCHAR2(1) default '0'
)
tablespace FAMILY226
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table T_REVEAL_REPORT_CLEAR_TIP
is '期间管理报告清算提示表';
-- Add comments to the columns
comment on column T_REVEAL_REPORT_CLEAR_TIP.c_reveal_report_clear_tip_id
is 'ID';
comment on column T_REVEAL_REPORT_CLEAR_TIP.c_project_code
is '项目编号';
comment on column T_REVEAL_REPORT_CLEAR_TIP.d_reveal_date
is '披露日期';
comment on column T_REVEAL_REPORT_CLEAR_TIP.c_tip_status
is '处理标志 0-未处理 1-处理中 2-已处理';
comment on column T_REVEAL_REPORT_CLEAR_TIP.delete_flag
is '删除状态';
comment on column T_REVEAL_REPORT_CLEAR_TIP.create_time
is '创建时间';
comment on column T_REVEAL_REPORT_CLEAR_TIP.create_user_id
is '创建者';
comment on column T_REVEAL_REPORT_CLEAR_TIP.update_time
is '更新时间';
comment on column T_REVEAL_REPORT_CLEAR_TIP.update_user_id
is '更新者';
comment on column T_REVEAL_REPORT_CLEAR_TIP.c_reveal_rate
is '披露频率';
comment on column T_REVEAL_REPORT_CLEAR_TIP.c_reveal_due_time
is '披露日期期限开始';
comment on column T_REVEAL_REPORT_CLEAR_TIP.c_notice_due_time
is '通知期限';
comment on column T_REVEAL_REPORT_CLEAR_TIP.d_reveal_start_date
is '期报开始日期';
comment on column T_REVEAL_REPORT_CLEAR_TIP.d_reveal_end_date
is '期报结束日期';
comment on column T_REVEAL_REPORT_CLEAR_TIP.c_reveal_due_end_time
is '披露日期期限结束';
comment on column T_REVEAL_REPORT_CLEAR_TIP.c_period_report_id
is '项目披露报告ID';
comment on column T_REVEAL_REPORT_CLEAR_TIP.c_isauto_report
is '是否自动生成期报标识 0-是 1-否';
-- Create/Recreate indexes
create unique index PK_REVEAL_REPORT_CLEAR_TIP_ID on T_REVEAL_REPORT_CLEAR_TIP (C_REVEAL_REPORT_CLEAR_TIP_ID)
tablespace FAMILY226
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
人生有几个十年呢？点点滴滴，用文字记录
