存储过程-期报提示生成

渠道系列项目根据披露配置生产期报提示

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;

posted @ 2022-04-13 13:58  鸟不拉诗  阅读(15)  评论(0编辑  收藏  举报