存储过程：期报提示（含有数组）

改造前-期报提示（含有数组）

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
  );