cus_con_deposit_pkg.pck 18.3 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220
create or replace package cus_con_deposit_pkg is

  -- Author  : Spencer 3893 
  -- Created : 2019/10/14 20:51:45
  -- Purpose : 保证金

  --罚息测算
  procedure calc_overdue_dayend(p_calc_date  date,
                                p_session_id number,
                                p_user_id    number);

end cus_con_deposit_pkg;
/
create or replace package body cus_con_deposit_pkg is
  g_currency_precision constant number := 2;

  procedure log(p_log_desc varchar2, p_error_message varchar2) is
  begin
    hls_dayend_pkg.log(p_log_desc      => p_log_desc,
                       p_error_message => p_error_message);
  end;
  procedure f_calc_penalty(p_calc_amount       number,
                           p_contract_rec      con_contract%rowtype,
                           p_calc_date         date,
                           p_penalty_calc_date date,
                           p_user_id           number,
                           p_total_penalty_amt out number) is
    v_penalty_days number;
    v_penalty_amt  number;
  
    v_total_penalty_amt number := 0;
  begin
    v_penalty_days := trunc(p_calc_date) - trunc(p_penalty_calc_date) -
                      nvl(p_contract_rec.grace_period, 0);
  
    -- 未核销金额 * 延迟天数 * 延迟履行金率
    if v_penalty_days <= 0 then
      v_penalty_amt := 0;
    else
      v_penalty_days := v_penalty_days +
                        nvl(p_contract_rec.grace_period, 0);
      v_penalty_amt  := p_calc_amount * (v_penalty_days) *
                        p_contract_rec.penalty_rate;
    end if;
  
    /*log(p_log_desc => '未收金额'||to_char(p_cashflow_rec.due_amount - nvl(p_cashflow_rec.received_amount,0)),
        p_error_message => '延迟天数='||v_penalty_days||',延迟金'||v_penalty_amt);
    */
    v_total_penalty_amt := v_total_penalty_amt + v_penalty_amt;
  
    p_total_penalty_amt := round(v_total_penalty_amt,
                                 nvl(p_contract_rec.currency_precision,
                                     g_currency_precision));
  end;

  procedure calc_penalty(p_cashflow_rec      con_contract_cashflow%rowtype,
                         p_contract_rec      con_contract%rowtype,
                         p_calc_date         date,
                         p_user_id           number,
                         p_total_penalty_amt out number) is
  
    v_penalty_days number;
    v_penalty_amt  number;
  
    v_total_penalty_amt  number := 0;
    v_last_creation_date date;
  begin
  
    select trunc(max(a.penalty_calc_date))
      into v_last_creation_date
      from csh_write_off a
     where a.cashflow_id = p_cashflow_rec.cashflow_id;
  
    --modify by wcs
    if not (p_cashflow_rec.penalty_process_status = 'NORMAL') then
      --不满足计算条件
      return;
    end if;
    --已收部分
    for c_write_off in (select a.*
                          from csh_write_off a
                         where a.contract_id = p_contract_rec.contract_id
                           and a.cashflow_id = p_cashflow_rec.cashflow_id
                           and trunc(a.penalty_calc_date) <=
                               trunc(p_calc_date)
                         order by a.penalty_calc_date, a.write_off_date) loop
    
      --核销金额 * 延迟天数 * 延迟履行金率
    
      v_penalty_days := trunc(c_write_off.penalty_calc_date) -
                        trunc(nvl(p_cashflow_rec.ln_user_col_d01,
                                  p_cashflow_rec.due_date)) -
                        nvl(p_contract_rec.grace_period, 0);
    
      if v_penalty_days <= 0 then
        v_penalty_amt := 0;
      else
        v_penalty_days := v_penalty_days +
                          nvl(p_contract_rec.grace_period, 0);
      
        v_penalty_amt := c_write_off.write_off_due_amount * v_penalty_days *
                         p_contract_rec.penalty_rate;
      end if;
    
      /*log(p_log_desc => to_char(c_write_off.penalty_calc_date,'YYYYMMDD')||'收款核销'||c_write_off.write_off_due_amount,
          p_error_message => '延迟天数='||v_penalty_days||',延迟金'||v_penalty_amt);
      
      */
      v_total_penalty_amt := v_total_penalty_amt + v_penalty_amt;
    
    end loop;
  
    --未收部分
    if nvl(p_cashflow_rec.received_amount, 0) < p_cashflow_rec.due_amount then
    
      v_penalty_days := trunc(p_calc_date) -
                        trunc(nvl(p_cashflow_rec.ln_user_col_d01,
                                  p_cashflow_rec.due_date)) -
                        nvl(p_contract_rec.grace_period, 0);
    
      -- 未核销金额 * 延迟天数 * 延迟履行金率
      if v_penalty_days <= 0 then
        v_penalty_amt := 0;
      else
        v_penalty_days := v_penalty_days +
                          nvl(p_contract_rec.grace_period, 0);
        v_penalty_amt  := (p_cashflow_rec.due_amount -
                          nvl(p_cashflow_rec.received_amount, 0)) *
                          (v_penalty_days) * p_contract_rec.penalty_rate;
      end if;
    
      /*log(p_log_desc => '未收金额'||to_char(p_cashflow_rec.due_amount - nvl(p_cashflow_rec.received_amount,0)),
          p_error_message => '延迟天数='||v_penalty_days||',延迟金'||v_penalty_amt);
      */
      v_total_penalty_amt := v_total_penalty_amt + v_penalty_amt;
    
    end if;
    p_total_penalty_amt := round(v_total_penalty_amt,
                                 nvl(p_contract_rec.currency_precision,
                                     g_currency_precision));
  
  end;

  procedure overdue_dayend(p_contract_cashflow_rec con_contract_cashflow%rowtype,
                           p_contract_rec          con_contract%rowtype,
                           p_calc_date             date,
                           p_session_id            number,
                           p_user_id               number) is
  
    v_total_penalty_amt     number;
    v_total_penalty_amt_tmp number;
  
    v_last_creation_date date;
  
    v_deposit_calc_overdue_tmp_rec CUS_DEPOSIT_CALC_OVERDUE_TMP%rowtype;
  begin
    select trunc(max(a.penalty_calc_date))
      into v_last_creation_date
      from csh_write_off a
     where a.cashflow_id = p_contract_cashflow_rec.cashflow_id;
    --计算营业数据overdue
    --逾期
    if p_contract_cashflow_rec.overdue_status = 'Y' then
    
      --已逾期
      if p_contract_cashflow_rec.due_amount >
         nvl(p_contract_cashflow_rec.received_amount, 0) then
        --未收完款
        v_deposit_calc_overdue_tmp_rec.calc_id           := CUS_DEPOSIT_CALC_OVERDUE_TMP_s.Nextval;
        v_deposit_calc_overdue_tmp_rec.session_id        := p_session_id;
        v_deposit_calc_overdue_tmp_rec.calc_date         := p_calc_date;
        v_deposit_calc_overdue_tmp_rec.due_date          := p_contract_cashflow_rec.due_date;
        v_deposit_calc_overdue_tmp_rec.contract_id       := p_contract_rec.contract_id;
        v_deposit_calc_overdue_tmp_rec.cashflow_id       := p_contract_cashflow_rec.cashflow_id;
        v_deposit_calc_overdue_tmp_rec.overdue_max_days  := trunc(p_calc_date) -
                                                            trunc(p_contract_cashflow_rec.due_date);
        v_deposit_calc_overdue_tmp_rec.overdue_amount    := p_contract_cashflow_rec.due_amount -
                                                            nvl(p_contract_cashflow_rec.received_amount,
                                                                0);
        v_deposit_calc_overdue_tmp_rec.overdue_principal := p_contract_cashflow_rec.principal -
                                                            nvl(p_contract_cashflow_rec.received_principal,
                                                                0);
        v_deposit_calc_overdue_tmp_rec.overdue_interest  := p_contract_cashflow_rec.interest -
                                                            nvl(p_contract_cashflow_rec.received_interest,
                                                                0);
        v_deposit_calc_overdue_tmp_rec.created_by        := p_user_id;
        v_deposit_calc_overdue_tmp_rec.creation_date     := sysdate;
      
        insert into CUS_DEPOSIT_CALC_OVERDUE_TMP
        values v_deposit_calc_overdue_tmp_rec;
      end if;
    
    else
      --未逾期
      if p_contract_cashflow_rec.due_amount >
         nvl(p_contract_cashflow_rec.received_amount, 0) then
        v_deposit_calc_overdue_tmp_rec.calc_id           := CUS_DEPOSIT_CALC_OVERDUE_TMP_s.Nextval;
        v_deposit_calc_overdue_tmp_rec.session_id        := p_session_id;
        v_deposit_calc_overdue_tmp_rec.calc_date         := p_calc_date;
        v_deposit_calc_overdue_tmp_rec.due_date          := p_contract_cashflow_rec.due_date;
        v_deposit_calc_overdue_tmp_rec.contract_id       := p_contract_rec.contract_id;
        v_deposit_calc_overdue_tmp_rec.cashflow_id       := p_contract_cashflow_rec.cashflow_id;
        v_deposit_calc_overdue_tmp_rec.overdue_max_days  := trunc(p_calc_date) -
                                                            trunc(p_contract_cashflow_rec.due_date);
        v_deposit_calc_overdue_tmp_rec.overdue_amount    := p_contract_cashflow_rec.due_amount -
                                                            nvl(p_contract_cashflow_rec.received_amount,
                                                                0);
        v_deposit_calc_overdue_tmp_rec.overdue_principal := p_contract_cashflow_rec.principal -
                                                            nvl(p_contract_cashflow_rec.received_principal,
                                                                0);
        v_deposit_calc_overdue_tmp_rec.overdue_interest  := p_contract_cashflow_rec.interest -
                                                            nvl(p_contract_cashflow_rec.received_interest,
                                                                0);
        v_deposit_calc_overdue_tmp_rec.created_by        := p_user_id;
        v_deposit_calc_overdue_tmp_rec.creation_date     := sysdate;
      
        insert into CUS_DEPOSIT_CALC_OVERDUE_TMP
        values v_deposit_calc_overdue_tmp_rec;
      end if;
    end if;
221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236
  
    --计算penalty
    if p_contract_cashflow_rec.write_off_flag <> 'FULL' then
      calc_penalty(p_cashflow_rec      => p_contract_cashflow_rec,
                   p_contract_rec      => p_contract_rec,
                   p_calc_date         => p_calc_date,
                   p_total_penalty_amt => v_total_penalty_amt,
                   p_user_id           => p_user_id);
      --会计逾期金额默认营业数据,当会计没核销实际上和营业逾期一致
      update CUS_DEPOSIT_CALC_OVERDUE_TMP
         set OVERDUE_PENALTY_AMT   = v_total_penalty_amt,
             f_overdue_amount      = overdue_amount,
             f_overdue_penalty_amt = v_total_penalty_amt
       where calc_id = v_deposit_calc_overdue_tmp_rec.calc_id;
    end if;
  
237 238 239 240 241 242 243
    --计算会计逾期数据,取会计核销的最大期与营业现金流数据比较
    for c_f in (select gwo.cf_item,
                       gwo.times,
                       sum(gwo.write_off_amount) write_off_amount,
                       gwo.penalty_calc_date
                  from GLD_WRITE_OFF gwo
                 where /*gwo.create_je_flag = 'Y'
244
                                                                                                                                                                                                                                                                                                                                                                                                                AND */
245 246 247 248 249 250
                 gwo.reversed_flag = 'N'
             and gwo.contract_id = p_contract_rec.contract_id
             and gwo.times =
                 (select max(gwo.times) max_times
                    from GLD_WRITE_OFF gwo
                   where /*gwo.create_je_flag = 'Y'
251
                                                                                                                                                                                                                                                                                                                                                                                                                                                  AND */
252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384
                   gwo.reversed_flag = 'N'
                and gwo.contract_id = p_contract_rec.contract_id)
                 group by gwo.cf_item, gwo.times, gwo.penalty_calc_date) loop
      --会计出凭证的数据同类型同期现金流,并营业应收金额大于会计出凭证的和及营业应收日小于计算日。就视为这一期现金流 会计逾期
      if p_contract_cashflow_rec.cf_item = c_f.cf_item and
         p_contract_cashflow_rec.times = c_f.times and
         p_contract_cashflow_rec.due_amount > c_f.write_off_amount and
         p_contract_cashflow_rec.due_date < p_calc_date then
      
        --会计已收收计算罚息
        f_calc_penalty(p_calc_amount       => c_f.write_off_amount,
                       p_contract_rec      => p_contract_rec,
                       p_calc_date         => p_calc_date,
                       p_penalty_calc_date => c_f.penalty_calc_date,
                       p_user_id           => p_user_id,
                       p_total_penalty_amt => v_total_penalty_amt_tmp);
        --会计未收计算罚息
        f_calc_penalty(p_calc_amount       => p_contract_cashflow_rec.due_amount -
                                              c_f.write_off_amount,
                       p_contract_rec      => p_contract_rec,
                       p_calc_date         => p_calc_date,
                       p_penalty_calc_date => p_contract_cashflow_rec.due_date,
                       p_user_id           => p_user_id,
                       p_total_penalty_amt => v_total_penalty_amt);
        --更新会计逾期租金=营业应收租金-会计核销租金
        update CUS_DEPOSIT_CALC_OVERDUE_TMP t
           set t.f_overdue_amount      = p_contract_cashflow_rec.due_amount -
                                         c_f.write_off_amount,
               t.f_overdue_penalty_amt = round(v_total_penalty_amt_tmp +
                                               v_total_penalty_amt,
                                               2)
         where t.cashflow_id = p_contract_cashflow_rec.cashflow_id
           and t.contract_id = p_contract_rec.contract_id
           and t.session_id = p_session_id;
        --会计出凭证的数据同类型及会计未来期现金流,并营业应收日小于计算日。就视为这一期现金流 会计逾期    
      elsif p_contract_cashflow_rec.cf_item = c_f.cf_item and
            p_contract_cashflow_rec.times > c_f.times and
            p_contract_cashflow_rec.due_date < p_calc_date then
        --会计未收计算罚息
        f_calc_penalty(p_calc_amount       => p_contract_cashflow_rec.due_amount,
                       p_contract_rec      => p_contract_rec,
                       p_calc_date         => p_calc_date,
                       p_penalty_calc_date => p_contract_cashflow_rec.due_date,
                       p_user_id           => p_user_id,
                       p_total_penalty_amt => v_total_penalty_amt);
        --更新会计逾期租金=营业应收租金
        update CUS_DEPOSIT_CALC_OVERDUE_TMP t
           set t.f_overdue_amount      = p_contract_cashflow_rec.due_amount,
               t.f_overdue_penalty_amt = v_total_penalty_amt
         where t.cashflow_id = p_contract_cashflow_rec.cashflow_id
           and t.contract_id = p_contract_rec.contract_id
           and t.session_id = p_session_id;
      end if;
    end loop;
  end;

  procedure overdue_dayend(p_contract_id number,
                           p_calc_date   date,
                           p_session_id  number,
                           p_user_id     number) is
  
    v_contract_rec con_contract%rowtype;
  
  begin
    begin
      select *
        into v_contract_rec
        from con_contract
       where contract_id = p_contract_id
         and contract_status = 'INCEPT'
         for update nowait;
    exception
      when no_data_found then
        return;
    end;
  
    for c_contract_cashflow in (select a.*
                                  from con_contract_cashflow a,
                                       con_contract_cf_item  b
                                 where a.contract_id = p_contract_id
                                   and a.cf_status = 'RELEASE'
                                   and a.due_date <= p_calc_date
                                      --只有正常的才计算罚息。
                                   and a.penalty_process_status = 'NORMAL'
                                   and a.cf_direction = 'INFLOW'
                                   and a.contract_id = b.contract_id
                                   and a.cf_item = b.cf_item
                                   and b.calc_penalty = 'Y'
                                 order by times, due_date
                                   for update nowait) loop
    
      overdue_dayend(p_contract_cashflow_rec => c_contract_cashflow,
                     p_contract_rec          => v_contract_rec,
                     p_calc_date             => p_calc_date,
                     p_session_id            => p_session_id,
                     p_user_id               => p_user_id);
    
    end loop;
  end;

  --罚息测算
  procedure calc_overdue_dayend(p_calc_date  date,
                                p_session_id number,
                                p_user_id    number) is
    v_count number;
  begin
    -- 同样的罚息测算日如果存在数据,不在进行计算
    select count(*)
      into v_count
      from CUS_DEPOSIT_CALC_OVERDUE_TMP t
     where t.calc_date = p_calc_date
    --and t.session_id = p_session_id
    ;
    if v_count >= 1 then
      null;
    else
      -- 之前的计算数据
      delete from CUS_DEPOSIT_CALC_OVERDUE_TMP t
       where t.calc_date <= p_calc_date;
      --t.session_id = p_session_id;
      --根据测算日重算罚息
      for c_con in (select *
                      from con_contract t
                     where t.contract_status = 'INCEPT') loop
        overdue_dayend(p_contract_id => c_con.contract_id,
                       p_calc_date   => p_calc_date,
                       p_session_id  => p_session_id,
                       p_user_id     => p_user_id);
      end loop;
    end if;
  end calc_overdue_dayend;
end cus_con_deposit_pkg;
/