create or replace package con_contract_change_req_pkg is

  -- Author  : GAOYANG
  -- Created : 2013/9/24 14:55:07
  -- Purpose : 变更申请
  -- Version : 20150820

  procedure bm_check_after_save(p_change_req_id in number,
                                p_user_id       in number);

  procedure bm_check_before_delete(p_change_req_id in number,
                                   p_user_id       in number);

  function get_con_change_req_number(p_document_type     varchar2,
                                     p_document_category varchar2,
                                     p_business_type     varchar2 default null,
                                     p_company_id        number,
                                     p_req_date          date,
                                     p_user_id           number)
    return varchar2;

  function get_con_change_req_rec(p_change_req_id con_contract_change_req.change_req_id%type,
                                  p_user_id       con_contract_change_req.created_by%type)
    return con_contract_change_req%rowtype;

  function get_ccr_start_times(p_change_con_rec in con_contract%rowtype,
                               p_date           in date) return number;

  procedure calc_ccr_data(p_change_req_rec in out nocopy con_contract_change_req%rowtype,
                          p_change_con_rec in out nocopy con_contract%rowtype,
                          p_user_id        in number);

  procedure change_req_calc(p_change_req_id      in number,
                            p_calc_session_id    in out number,
                            p_calc_successful    out varchar2,
                            p_warning_message    out varchar2,
                            p_recreate_h_formula in varchar2 default 'Y',
                            p_recreate_l_formula in varchar2 default 'Y',
                            p_recreate_l_std     in varchar2 default 'Y',
                            p_user_id            in number);

  procedure change_req_submit(p_change_req_id    in number,
                              p_ccr_fee          in varchar2 default null,
                              p_ccr_penalty      in varchar2 default null,
                              p_btb_payment_date in date default null,
                              p_layout_code      in varchar2 default null,
                              p_user_id          in number);

  procedure change_req_confirm(p_change_req_id in number,
                               p_user_id       in number);

  procedure change_req_reject(p_change_req_id in number,
                              p_user_id       in number);

  procedure change_req_cancel(p_change_req_id in number,
                              p_user_id       in number);
  --从正式环境搬过来的insert 过程
  procedure submit_con_change_req(p_change_req_id con_contract_change_req.change_req_id%type,
                                  p_user_id       con_contract_change_req.created_by%type);

  procedure insert_con_change_req(p_change_req_id          out con_contract_change_req.change_req_id%type,
                                  p_batch_id               con_contract_change_req.batch_id%type,
                                  p_change_req_number      con_contract_change_req.change_req_number%type default null,
                                  p_document_type          con_contract_change_req.document_type%type,
                                  p_document_category      con_contract_change_req.document_category%type,
                                  p_business_type          con_contract_change_req.business_type%type,
                                  p_company_id             con_contract_change_req.company_id%type,
                                  p_contract_id            con_contract_change_req.contract_id%type,
                                  p_req_status             con_contract_change_req.req_status%type,
                                  p_req_date               con_contract_change_req.req_date%type,
                                  p_change_req_profile     con_contract_change_req.change_req_profile%type default null,
                                  p_reschedule_start_date  con_contract_change_req.reschedule_start_date%type default null,
                                  p_reschedule_start_times con_contract_change_req.reschedule_start_times%type default null,
                                  p_reschedule_new_date    con_contract_change_req.reschedule_new_date%type default null,
                                  p_reschedule_total_times con_contract_change_req.reschedule_total_times%type default null,
                                  p_reschedule_last_time   con_contract_change_req.reschedule_last_time%type default null,
                                  p_reschedule_balance     con_contract_change_req.reschedule_balance%type default null,
                                  p_reschedule_direction   con_contract_change_req.reschedule_direction%type default null,
                                  p_reschedule_days        con_contract_change_req.reschedule_days%type default null,
                                  p_reschedule_fee         con_contract_change_req.reschedule_fee%type default null,
                                  p_calculate_rate         con_contract_change_req.calculate_rate%type default null,
                                  p_termination_date       con_contract_change_req.termination_date%type default null,
                                  p_overdue_amount         con_contract_change_req.overdue_amount%type default null,
                                  p_penalty                con_contract_change_req.penalty%type default null,
                                  p_rentals_payable        con_contract_change_req.rentals_payable%type default null,
                                  p_discount_total_amount  con_contract_change_req.discount_total_amount%type default null,
                                  p_undue_principal        con_contract_change_req.undue_principal%type default null,
                                  p_et_interest            con_contract_change_req.et_interest%type default null,
                                  p_mgt_fee_after_discount con_contract_change_req.mgt_fee_after_discount%type default null,
                                  p_et_fee                 con_contract_change_req.et_fee%type default null,
                                  p_residual_value         con_contract_change_req.residual_value%type default null,
                                  p_et_total_amount        con_contract_change_req.et_total_amount%type default null,
                                  p_last_paydue_date       con_contract_change_req.last_paydue_date%type default null,
                                  p_last_paydue_times      con_contract_change_req.last_paydue_times%type default null,
                                  p_discount_days          con_contract_change_req.discount_days%type default null,
                                  p_submit_date            con_contract_change_req.submit_date%type default null,
                                  p_approved_date          con_contract_change_req.approved_date%type default null,
                                  p_description            con_contract_change_req.description%type default null,
                                  p_calc_session_id        con_contract_change_req.calc_session_id%type default null,
                                  p_wfl_instance_id        con_contract_change_req.wfl_instance_id%type default null,
                                  p_user_id                con_contract_change_req.created_by%type,
                                  p_ref_v01                con_contract_change_req.ref_v01%type default null,
                                  p_ref_v02                con_contract_change_req.ref_v02%type default null,
                                  p_ref_v03                con_contract_change_req.ref_v03%type default null,
                                  p_ref_v04                con_contract_change_req.ref_v04%type default null,
                                  p_ref_v05                con_contract_change_req.ref_v05%type default null,
                                  p_ref_v06                con_contract_change_req.ref_v06%type default null,
                                  p_ref_v07                con_contract_change_req.ref_v07%type default null,
                                  p_ref_v08                con_contract_change_req.ref_v08%type default null,
                                  p_ref_v09                con_contract_change_req.ref_v09%type default null,
                                  p_ref_v10                con_contract_change_req.ref_v10%type default null,
                                  p_ref_n01                con_contract_change_req.ref_n01%type default null,
                                  p_ref_n02                con_contract_change_req.ref_n02%type default null,
                                  p_ref_n03                con_contract_change_req.ref_n03%type default null,
                                  p_ref_n04                con_contract_change_req.ref_n04%type default null,
                                  p_ref_n05                con_contract_change_req.ref_n05%type default null,
                                  p_ref_n06                con_contract_change_req.ref_n06%type default null,
                                  p_ref_n07                con_contract_change_req.ref_n07%type default null,
                                  p_ref_n08                con_contract_change_req.ref_n08%type default null,
                                  p_ref_n09                con_contract_change_req.ref_n09%type default null,
                                  p_ref_n10                con_contract_change_req.ref_n10%type default null,
                                  p_ref_d01                con_contract_change_req.ref_d01%type default null,
                                  p_ref_d02                con_contract_change_req.ref_d02%type default null,
                                  p_ref_d03                con_contract_change_req.ref_d03%type default null,
                                  p_ref_d04                con_contract_change_req.ref_d04%type default null,
                                  p_ref_d05                con_contract_change_req.ref_d05%type default null,
                                  p_ref_d06                con_contract_change_req.ref_d06%type default null,
                                  p_ref_d07                con_contract_change_req.ref_d07%type default null,
                                  p_ref_d08                con_contract_change_req.ref_d08%type default null,
                                  p_ref_d09                con_contract_change_req.ref_d09%type default null,
                                  p_ref_d10                con_contract_change_req.ref_d10%type default null);

  --合同变更(内部)
  procedure change_req_in_confirm(p_change_req_id in number,
                                  p_user_id       in number);

  procedure change_req_confirm_d(p_change_req_id in number,
                                 p_user_id       in number);

  --modify by lpc 9874 合同承租人变更计算应缴纳金额
  procedure calc_leasse_date(p_contract_id          number,
                             p_btb_payment_date     date,
                             p_ccr_leasse_rental    out number, --应缴纳租金
                             p_ccr_leasse_principal out number, --应缴纳本金
                             p_ccr_leasse_interest  out number, --应缴纳利息
                             p_ccr_leasse_penalty   out number,
                             p_ccr_outstanding_cld  out number); --应缴纳罚息

  procedure calc_leasse_pay(p_contract_id           number,
                            p_ccr_start_times       number,
                            p_btb_payment_date      date default null,
                            p_interim_rental        number default null,
                            p_layout_code           varchar2,
                            p_ccr_outstanding_times out number,
                            p_ccr_dept              out varchar2,
                            p_ccr_outstanding_incld out varchar2,
                            p_ccr_fee               out number);
  procedure calc_penalty(p_cashflow_rec      con_contract_cashflow%rowtype,
                         p_contract_rec      con_contract%rowtype,
                         p_calc_date         date,
                         p_calc_method       varchar2,
                         p_total_penalty_amt out number);
  --插入变更手续费304.承租人期数1,租赁物期数2
  procedure insert_ccr_cashflow(p_contract_id        number,
                                p_ccr_document_type  varchar2,
                                p_btb_payment_date   date default null,
                                p_bp_id              number default null,
                                p_lease_item_id      number default null,
                                p_pattern            varchar2 default null,
                                p_machine_number     varchar2 default null,
                                p_interim_times      number default null,
                                p_ccr_start_times    number default null,
                                p_hd_user_col_v04    varchar2 default null,
                                p_btb_finance_amount number default null, --部分中途偿还本金
                                p_user_id            varchar2);
  procedure insert_ccr_fee(p_contract_id   number,
                           p_user_id       number,
                           p_document_type varchar2 default null,
                           p_cf_item       varchar2 default null,
                           p_cf_type       varchar2 default null,
                           p_due_date      date default null,
                           p_ccr_fee       number);

  procedure update_change_req(p_contract_id      number,
                              p_ccr_start_times  number,
                              p_btb_payment_date date);
end con_contract_change_req_pkg;
/
create or replace package body con_contract_change_req_pkg is

  e_lock_table exception;
  pragma exception_init(e_lock_table, -54);

  procedure bm_check_after_save(p_change_req_id in number,
                                p_user_id       in number) is
  begin
    null;
  end;

  procedure bm_check_before_delete(p_change_req_id in number,
                                   p_user_id       in number) is
  begin
    null;
  end;

  function get_con_change_req_number(p_document_type     varchar2,
                                     p_document_category varchar2,
                                     p_business_type     varchar2 default null,
                                     p_company_id        number,
                                     p_req_date          date,
                                     p_user_id           number)
    return varchar2 is
  
    v_document_number varchar2(100);
  begin
    v_document_number := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => p_document_category,
                                                                  p_document_type     => p_document_type,
                                                                  p_company_id        => p_company_id,
                                                                  p_operation_unit_id => null,
                                                                  p_operation_date    => p_req_date,
                                                                  p_created_by        => p_user_id);
    if v_document_number = fnd_code_rule_pkg.c_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CON_CHANGE_REQ_NUMBER_NULL',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'get_con_change_req_number');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    end if;
    return v_document_number;
  end;

  function get_con_change_req_rec(p_change_req_id con_contract_change_req.change_req_id%type,
                                  p_user_id       con_contract_change_req.created_by%type)
    return con_contract_change_req%rowtype is
    v_change_req_rec con_contract_change_req%rowtype;
  begin
    select *
      into v_change_req_rec
      from con_contract_change_req t
     where t.change_req_id = p_change_req_id
       for update nowait;
    return v_change_req_rec;
  exception
    when e_lock_table then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CON_CHANGE_REQ_LOCK',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'get_con_change_req_rec');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;

  function get_ccr_start_times(p_change_con_rec in con_contract%rowtype,
                               p_date           in date) return number is
    v_times number;
  begin
    select min(times)
      into v_times
      from con_contract_cashflow
     where contract_id = p_change_con_rec.contract_id
       and cf_item = 1
       and cf_direction = 'INFLOW'
       and due_date > p_date;
  
    if v_times is null then
      if p_change_con_rec.inception_of_lease is null then
        v_times := 1;
      else
        v_times := null;
      end if;
    end if;
  
    return v_times;
  
  end;

  procedure calc_ccr_data(p_change_req_rec in out nocopy con_contract_change_req%rowtype,
                          p_change_con_rec in out nocopy con_contract%rowtype,
                          p_user_id        in number) is
  
    v_ccr_outstanding_times   number; -- 到期未支付租金
    v_ccr_overdue_rental      number; -- 到期未支付租金(逾期租金)
    v_ccr_overdue_prin        number; -- 到期未支付本金(逾期本金)
    v_ccr_overdue_interest    number; -- 到期未支付利息(逾期利息)
    v_ccr_outs_rental         number; -- 未到期租金
    v_ccr_outs_prin_tax_incld number; -- 自变更期起的剩余本金
    v_ccr_outs_prin_ti_total  number; -- 所有期的剩余本金
    v_ccr_other_receivables   number; -- 剩余其他应收
  
    v_ccr_penalty         number; --
    v_ccr_deposit_balance number; --
  begin
  
    select lease_times - p_change_con_rec.ccr_start_times + 1
      into v_ccr_outstanding_times
      from con_contract
     where contract_id = p_change_con_rec.contract_id;
  
    -- 到期未支付租金
    select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0),
           nvl(sum(a.principal - nvl(a.received_principal, 0)), 0),
           nvl(sum(a.interest - nvl(a.received_interest, 0)), 0)
      into v_ccr_overdue_rental, v_ccr_overdue_prin, v_ccr_overdue_interest
      from con_contract_cashflow a
     where a.contract_id = p_change_req_rec.contract_id
       and a.cf_type = 1
       and a.times > 0
       and a.times < p_change_con_rec.ccr_start_times
       and a.cf_direction = 'INFLOW';
  
    -- 未到期租金
    select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0)
      into v_ccr_outs_rental
      from con_contract_cashflow a
     where a.contract_id = p_change_req_rec.contract_id
       and a.cf_type = 1
       and a.times >= p_change_con_rec.ccr_start_times
       and a.cf_direction = 'INFLOW';
  
    --自变更期起的剩余本金
    select nvl(sum(a.principal - nvl(a.received_principal, 0)), 0)
      into v_ccr_outs_prin_tax_incld
      from con_contract_cashflow a
     where a.contract_id = p_change_req_rec.contract_id
       and a.cf_type = 1
       and a.times >= p_change_con_rec.ccr_start_times
       and a.cf_direction = 'INFLOW';
  
    --所有期的剩余本金
    select nvl(sum(a.principal - nvl(a.received_principal, 0)), 0)
      into v_ccr_outs_prin_ti_total
      from con_contract_cashflow a
     where a.contract_id = p_change_req_rec.contract_id
       and a.cf_type = 1
       and a.times > 0
       and a.cf_direction = 'INFLOW';
  
    /* 未收逾期罚息
       提供2种算法,
       1) 从以计算的现金流(9) 中取数
       2) 通过罚息计算,获取变更日当时的罚息(调用罚息试算,更新罚息现金流)
      con_overdue_penalty_pkg.overdue_dayend(p_contract_id => p_change_req_rec.change_req_id,
                                           p_calc_date   => p_et_date-1,
                                           p_user_id     => v_old_contract_rec.created_by,
                                           p_calc_method => con_overdue_penalty_pkg.c_penalty_calc);
    
    
    
    */
    select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0)
      into v_ccr_penalty
      from con_contract_cashflow a
     where a.contract_id = p_change_req_rec.change_req_id
       and a.cf_type = 9
       and a.times > 0
       and a.cf_direction = 'INFLOW';
  
    --保证金余额
    select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0)
      into v_ccr_deposit_balance
      from con_contract_cashflow a
     where a.contract_id = p_change_req_rec.contract_id
       and a.cf_type = 5 -- 未必,尚未考虑第三方保证金,anthony
       and a.cf_item = 52 --排除第三方保证金
       and a.cf_direction = 'OUTFLOW';
  
    --剩余其他应收
    select nvl(sum(a.due_amount - nvl(a.received_amount, 0)), 0)
      into v_ccr_other_receivables
      from con_contract_cashflow a
     where a.contract_id = p_change_req_rec.contract_id
       and a.cf_type not in (1, 5, 9)
       and a.cf_direction = 'INFLOW';
  
    p_change_con_rec.ccr_original_finance_amount    := p_change_con_rec.finance_amount;
    p_change_con_rec.ccr_outstanding_prin_tax_incld := v_ccr_outs_prin_tax_incld;
    p_change_con_rec.ccr_overdue_rental             := v_ccr_overdue_rental;
    p_change_con_rec.ccr_overdue_prin               := v_ccr_overdue_prin;
    p_change_con_rec.ccr_overdue_interest           := v_ccr_overdue_interest;
    p_change_con_rec.ccr_penalty                    := v_ccr_penalty;
    p_change_con_rec.ccr_outstanding_prin_ti_total  := v_ccr_outs_prin_ti_total;
    p_change_con_rec.ccr_outstanding_times          := v_ccr_outstanding_times;
    p_change_con_rec.ccr_outstanding_rental         := v_ccr_outs_rental;
    p_change_con_rec.ccr_other_receivables          := v_ccr_other_receivables;
  
    p_change_con_rec.ccr_deposit_balance := v_ccr_deposit_balance;
  
    update con_contract
       set row = p_change_con_rec
     where contract_id = p_change_con_rec.contract_id;
  
    if p_change_con_rec.ccr_start_times is not null and
       p_change_con_rec.ccr_financing_overdue_rental = '1' then
    
      for c_before_cf in (select *
                            from con_contract_cashflow a
                           where a.contract_id =
                                 p_change_req_rec.change_req_id
                             and a.times < p_change_con_rec.ccr_start_times
                             and a.cf_direction = 'INFLOW'
                             and a.cf_type = 1) loop
      
        if nvl(c_before_cf.interest, 0) = 0 then
          null;
        else
          c_before_cf.vat_interest := round(c_before_cf.vat_interest *
                                            (nvl(c_before_cf.received_interest,
                                                 0) / c_before_cf.interest),
                                            2);
          c_before_cf.interest     := nvl(c_before_cf.received_interest, 0);
          c_before_cf.net_interest := c_before_cf.interest -
                                      nvl(c_before_cf.vat_interest, 0);
        end if;
      
        if nvl(c_before_cf.principal, 0) = 0 then
          null;
        else
          c_before_cf.vat_principal := round(c_before_cf.vat_principal *
                                             (nvl(c_before_cf.received_principal,
                                                  0) /
                                             c_before_cf.principal),
                                             2);
          c_before_cf.principal     := nvl(c_before_cf.received_principal,
                                           0);
          c_before_cf.net_principal := c_before_cf.principal -
                                       nvl(c_before_cf.vat_principal, 0);
        end if;
      
        if nvl(c_before_cf.due_amount, 0) = 0 then
          null;
        else
          c_before_cf.vat_due_amount := round(c_before_cf.vat_due_amount *
                                              (nvl(c_before_cf.received_amount,
                                                   0) /
                                              c_before_cf.due_amount),
                                              2);
          c_before_cf.due_amount     := nvl(c_before_cf.received_amount, 0);
          c_before_cf.net_due_amount := c_before_cf.due_amount -
                                        nvl(c_before_cf.vat_due_amount, 0);
        end if;
      
        c_before_cf.write_off_flag         := 'FULL';
        c_before_cf.penalty_process_status := 'SUSPEND';
        c_before_cf.full_write_off_date    := c_before_cf.last_received_date;
        c_before_cf.last_updated_by        := p_user_id;
        c_before_cf.last_update_date       := sysdate;
      
        update con_contract_cashflow a
           set row = c_before_cf
         where cashflow_id = c_before_cf.cashflow_id;
      
      end loop;
    
    end if;
  
  end;

  procedure change_req_calc(p_change_req_id      in number,
                            p_calc_session_id    in out number,
                            p_calc_successful    out varchar2,
                            p_warning_message    out varchar2,
                            p_recreate_h_formula in varchar2 default 'Y',
                            p_recreate_l_formula in varchar2 default 'Y',
                            p_recreate_l_std     in varchar2 default 'Y',
                            p_user_id            in number) is
  
  begin
    hls_fin_calculator_ccr_pkg.create_calculate(p_change_req_id      => p_change_req_id,
                                                p_calc_session_id    => p_calc_session_id,
                                                p_recreate_h_formula => nvl(p_recreate_h_formula,
                                                                            'Y'),
                                                p_recreate_l_std     => nvl(p_recreate_l_std,
                                                                            'Y'),
                                                p_recreate_l_formula => nvl(p_recreate_l_formula,
                                                                            'Y'),
                                                p_calc_successful    => p_calc_successful,
                                                p_warning_message    => p_warning_message,
                                                p_user_id            => p_user_id);
  
    update con_contract_change_req
       set is_calc_flag = 'Y'
     where change_req_id = p_change_req_id;
  
  end;

  procedure change_req_submit(p_change_req_id    in number,
                              p_ccr_fee          in varchar2 default null,
                              p_ccr_penalty      in varchar2 default null,
                              p_btb_payment_date in date default null,
                              p_layout_code      in varchar2 default null,
                              p_user_id          in number) is
    r_change_req_rec con_contract_change_req%rowtype;
    r_change_con_rec con_contract%rowtype;
  
    v_approval_method hls_document_type.approval_method%type;
    v_calc_hd_rec     hls_fin_calculator_hd%rowtype;
  
    v_success_flag varchar2(1);
  
    e_change_req_status_error exception;
  
    e_calc_error               exception;
    e_simulation_error         exception;
    e_calc_session_id_notfound exception;
    e_is_calc_flag_err         exception;
    v_request_num number;
    e_attachment_error exception;
    v_cf_count           number;
    v_rate               number;
    v_total_interest     number;
    v_vat_total_interest number;
  begin
    r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id,
                                               p_user_id       => p_user_id);
    --冬雨季,承租人,债务再调整走起案,只计算变更手续费
    if r_change_req_rec.document_type not in
       ('CONEXTHAG', 'DEBT_CHAG', 'LEASSE_CHAG') then
      if r_change_req_rec.simulation_flag = 'Y' then
        raise e_simulation_error;
      end if;
    
      /*add by Harry 9952 2017/1/19
      purpose: verify the atttachment required property when submit the document
      */
      --应deron业务需要去除校验
      /* select count(*)
        into v_request_num
        from prj_cdd_item_doc_ref      dr,
             prj_cdd_item_check        ic,
             prj_cdd_item_list_grp_tab gt
       where dr.document_table = 'PRJ_PROJECT'
         and dr.document_id =
             (select c.project_id
                from con_contract c
               where c.contract_id = r_change_req_rec.contract_id)
         and dr.check_id = ic.check_id
         and ic.cdd_item_id = gt.cdd_item_id
         and gt.important_flag = 'Y'
         and not exists (select 1
                from fnd_atm_attachment_multi fam
               where fam.table_name = 'PRJ_CDD_ITEM_CHECK'
                 and fam.table_pk_value = ic.check_id);
      if v_request_num > 0 then
        raise e_attachment_error;
      end if;*/
    
      r_change_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id,
                                                            p_user_id     => p_user_id);
    
      --r_change_req_rec.req_status := 'NEW';
      if r_change_req_rec.req_status not in ('NEW', 'REJECT') then
        raise e_change_req_status_error;
      end if;
    
      --当是罚息变更申请的时候,没有存contract_id,所以不需要走这一步
      if r_change_req_rec.ccr_price_list is not null then
        if r_change_req_rec.ccr_calc_session_id is null then
          raise e_calc_session_id_notfound;
        else
          select calc_successful
            into v_success_flag
            from hls_fin_calculator_hd
           where calc_session_id = r_change_req_rec.ccr_calc_session_id;
        
          if nvl(v_success_flag, 'N') <> 'Y' then
            raise e_calc_error;
          end if;
        
        end if;
      
        if nvl(r_change_req_rec.is_calc_flag, 'N') = 'N' then
          raise e_is_calc_flag_err;
        end if;
      
      end if;
    
      con_change_req_custom_pkg.before_submit_change_req(p_change_req_id => p_change_req_id,
                                                         p_user_id       => p_user_id);
    
      con_contract_history_pkg.calc_change_history(p_change_req_id => p_change_req_id,
                                                   p_user_id       => p_user_id);
    
      if r_change_req_rec.document_type = 'DEBT_CHAG' then
        if to_number(p_ccr_fee) < 2000 then
          update con_contract t
             set t.fee_waiver = 'Y'
           where t.contract_id = p_change_req_id;
        else
          update con_contract t
             set t.fee_waiver = 'N'
           where t.contract_id = p_change_req_id;
        end if;
      elsif r_change_req_rec.document_type in
            ('CONEXTHAG', 'LEASEHOLD_CHAG', 'LEASSE_CHAG') then
        if to_number(p_ccr_fee) < 1000 then
          update con_contract t
             set t.fee_waiver = 'Y'
           where t.contract_id = p_change_req_id;
        else
          update con_contract t
             set t.fee_waiver = 'N'
           where t.contract_id = p_change_req_id;
        end if;
      elsif r_change_req_rec.document_type = 'PART_CHAG' then
        if to_number(p_ccr_fee) < 500 then
          update con_contract t
             set t.fee_waiver = 'Y'
           where t.contract_id = p_change_req_id;
        else
          update con_contract t
             set t.fee_waiver = 'N'
           where t.contract_id = p_change_req_id;
        end if;
      end if;
    
      --还款日变更不插手续费更新现金流cf_item = 1 或者103的利息字段
      if r_change_req_rec.document_type <> 'PAYDATE_CHAG' then
        --债权债务待调整插入罚息总额
        if r_change_req_rec.document_type = 'DEBT_CHAG' then
          --插入手续费日期为基准日期
          insert_ccr_fee(p_contract_id   => p_change_req_id,
                         p_user_id       => p_user_id,
                         p_cf_item       => 304,
                         p_cf_type       => 3,
                         p_document_type => r_change_req_rec.document_type,
                         p_due_date      => p_btb_payment_date,
                         p_ccr_fee       => p_ccr_fee);
        
          insert_ccr_fee(p_contract_id   => p_change_req_id,
                         p_user_id       => p_user_id,
                         p_cf_item       => 10,
                         p_cf_type       => 9,
                         p_document_type => r_change_req_rec.document_type,
                         p_due_date      => p_btb_payment_date,
                         p_ccr_fee       => p_ccr_penalty);
        elsif r_change_req_rec.document_type <> 'LEASEHOLD_CHAG' then
          --变更插入变更手续费现金流(租赁物不加手续费,modify by liuhaojie)
          insert_ccr_fee(p_contract_id   => p_change_req_id,
                         p_user_id       => p_user_id,
                         p_cf_item       => 304,
                         p_cf_type       => 3,
                         p_document_type => r_change_req_rec.document_type,
                         p_due_date      => sysdate,
                         p_ccr_fee       => p_ccr_fee);
        end if;
      else
        select count(1)
          into v_cf_count
          from con_contract_cashflow ci
         where ci.contract_id = p_change_req_id
           and ci.cf_item = 1
           and ci.times = r_change_con_rec.ccr_start_times;
        --取税率的方法
        select con_credit_report_pkg.get_tax_type_rate(p_change_req_id)
          into v_rate
          from dual;
        --cf_item = 1 租金的due_amount=原有金额+延期手续费。
        --net_due_amount=due_amount-vat_due_amount 
        --Vat_due_amount=原有vat_due_amount+round(延期手续费*税率/(1+税率),2)
        --Interest=延期手续费+原有的利息
        --Vat_interest= 原有vat_interest+round(延期手续费*税率/(1+税率),2)
        --Net_interest=interest-vat_interest
        --net_interest_implicit=net_interest
      
        if v_cf_count > 0 then
          update con_contract_cashflow ccc
             set ccc.due_amount            = ccc.due_amount + p_ccr_fee,
                 ccc.net_due_amount       =
                 (ccc.due_amount + p_ccr_fee) -
                 (ccc.vat_due_amount +
                 round(p_ccr_fee * v_rate / (1 + v_rate), 2)),
                 ccc.vat_due_amount        = ccc.vat_due_amount +
                                             round(p_ccr_fee * v_rate /
                                                   (1 + v_rate),
                                                   2),
                 ccc.interest              = ccc.interest + p_ccr_fee,
                 ccc.net_interest         =
                 (ccc.interest + p_ccr_fee) -
                 (ccc.vat_interest +
                 round(p_ccr_fee * v_rate / (1 + v_rate), 2)),
                 ccc.vat_interest          = ccc.vat_interest +
                                             round(p_ccr_fee * v_rate /
                                                   (1 + v_rate),
                                                   2),
                 ccc.net_interest_implicit =
                 (ccc.interest + p_ccr_fee) -
                 (ccc.vat_interest +
                 round(p_ccr_fee * v_rate / (1 + v_rate), 2))
           where ccc.contract_id = p_change_req_id
             and ccc.cf_direction = 'INFLOW'
             and ccc.cf_status = 'RELEASE'
             and ccc.cf_item = 1
             and ccc.times = r_change_con_rec.ccr_start_times;
          begin
            select cc1.total_interest
              into v_total_interest
              from con_contract cc1
             where cc1.contract_id =
                   (select ccr.contract_id
                      from con_contract_change_req ccr
                     where ccr.change_req_id = p_change_req_id);
          exception
            when no_data_found then
              v_total_interest := 0;
          end;
        
          update con_contract cc
             set cc.total_interest     = nvl(v_total_interest, 0) +
                                         nvl(p_ccr_fee, 0),
                 cc.vat_total_interest =
                 (select (sum(nvl(ccc.vat_interest, 0)))
                    from con_contract_cashflow ccc
                   where ccc.contract_id = p_change_req_id
                     and ccc.cf_direction = 'INFLOW'
                     and ccc.cf_status = 'RELEASE'
                     and ccc.cf_item = 1),
                 cc.net_total_interest =
                 (select (sum(nvl(ccc.net_interest, 0)))
                    from con_contract_cashflow ccc
                   where ccc.contract_id = p_change_req_id
                     and ccc.cf_direction = 'INFLOW'
                     and ccc.cf_status = 'RELEASE'
                     and ccc.cf_item = 1)
           where cc.contract_id = p_change_req_id;
        
          update con_contract cc
             set cc.repayment_date_day = extract(day from p_btb_payment_date)
           where cc.contract_id = p_change_req_id;
        
        else
          --cf_item = 103
          update con_contract_cashflow ccc
             set ccc.due_amount     = p_ccr_fee + ccc.due_amount,
                 ccc.net_due_amount =
                 (ccc.due_amount + p_ccr_fee) -
                 (ccc.vat_due_amount +
                 round(p_ccr_fee * v_rate / (1 + v_rate), 2)),
                 ccc.vat_due_amount = ccc.vat_due_amount +
                                      round(p_ccr_fee * v_rate /
                                            (1 + v_rate),
                                            2)
           where ccc.contract_id = p_change_req_id
             and ccc.cf_direction = 'INFLOW'
             and ccc.cf_status = 'RELEASE'
             and ccc.cf_item = 103
             and ccc.times = r_change_con_rec.ccr_start_times;
          begin
            select cc1.total_interest, cc1.vat_total_interest
              into v_total_interest, v_vat_total_interest
              from con_contract cc1
             where cc1.contract_id =
                   (select ccr.contract_id
                      from con_contract_change_req ccr
                     where ccr.change_req_id = p_change_req_id);
          exception
            when no_data_found then
              v_total_interest     := 0;
              v_vat_total_interest := 0;
          end;
        
          update con_contract cc
             set cc.total_interest     = nvl(v_total_interest, 0) +
                                         nvl(p_ccr_fee, 0),
                 cc.vat_total_interest = nvl(v_vat_total_interest, 0) +
                                         (round(p_ccr_fee * v_rate /
                                                (1 + v_rate),
                                                2)),
                 cc.net_total_interest = nvl(v_total_interest, 0) +
                                         nvl(p_ccr_fee, 0) -
                                         (nvl(v_vat_total_interest, 0) +
                                          (round(p_ccr_fee * v_rate /
                                                 (1 + v_rate),
                                                 2)))
          
           where cc.contract_id = p_change_req_id;
          update con_contract cc
             set cc.repayment_date_day = extract(day from p_btb_payment_date)
           where cc.contract_id = p_change_req_id;
        end if;
      end if;
    
      select approval_method
        into v_approval_method
        from hls_document_type
       where document_category = r_change_req_rec.document_category
         and document_type = r_change_req_rec.document_type;
    
      if v_approval_method = 'WORK_FLOW' then
      
        con_contract_change_req_wf_pkg.workflow_start(p_change_req_rec => r_change_req_rec,
                                                      p_change_con_rec => r_change_con_rec,
                                                      p_layout_code    => p_layout_code,
                                                      p_user_id        => p_user_id);
      else
        update con_contract_change_req t
           set t.req_status     = 'APPROVING',
               t.submit_date    = sysdate,
               last_updated_by  = p_user_id,
               last_update_date = sysdate
         where t.change_req_id = p_change_req_id;
        change_req_confirm(p_change_req_id => p_change_req_id,
                           p_user_id       => p_user_id);
      end if;
    end if;
  
  exception
    when e_attachment_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_WORKFLOW_PKG.ATTACHMENT_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_change_req_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CHANGE_REQ_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_calc_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CALCULATOR_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_is_calc_flag_err then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.IS_CALC_FLAG_ERR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
    when e_calc_session_id_notfound then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CALC_SESSION_ID_NOTFOUND',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
    when e_simulation_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.SIMULATION_FLAG_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;

  --合同变更申请同意
  procedure change_req_confirm(p_change_req_id in number,
                               p_user_id       in number) is
    r_change_req_rec      con_contract_change_req%rowtype;
    r_change_con_rec      con_contract%rowtype;
    v_create_je_flag      number;
    v_times               number;
    v_down_payment_ratio  number;
    v_instance_id         number;
    v_start_period        number;
    v_bill_bp_id          number;
    v_start_times         number;
    v_start_req_time      date;
    v_before_change_bp_id number;
    v_bp_id               number;
  begin
  
    r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id,
                                               p_user_id       => p_user_id);
  
    r_change_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id,
                                                          p_user_id     => p_user_id);
  
    -- 1)  生成变更凭证
    --LEASE_CHAG承租人期数1,LEASEHOLD_CHAG租赁物期数2,CONEXTHAG冬雨季期数3,DEBT_CHAG债务再调整期数4,PART_CHAG部分中途偿还期数5,PAY_CHAG非月转月付期数6
    --PAYDATE_CHAG还款日变更 ,ET_CHAG提前结清,REPUR回购申请,CUT_CHAG租金计划变更
  
    if nvl(r_change_req_rec.create_je_flag, 'N') = 'N' and
       r_change_req_rec.document_type in
       ('LEASE_CHAG',
        'DEBT_CHAG',
        'PART_CHAG',
        'PAY_CHAG',
        'CONEXTHAG',
        'PAYDATE_CHAG',
        'CUT_CHAG',
        'ET_CHAG',
        'REPUR') then
    
      v_create_je_flag := gld_je_template_pkg.create_je(p_je_transaction_code => 'CON_CHANGE_REQ_CONFIRM',
                                                        p_company_id          => r_change_req_rec.company_id,
                                                        p_je_company_id       => r_change_req_rec.company_id,
                                                        p_user_id             => p_user_id,
                                                        /*凭证头id*/
                                                        p_journal_header_id => null,
                                                        /*以下为业务参数*/
                                                        p_hd_source_table         => 'CON_CONTRACT_CHANGE_REQ',
                                                        p_hd_source_id            => p_change_req_id,
                                                        p_ln_source_table         => 'CON_CONTRACT_CHANGE_REQ',
                                                        p_ln_source_id            => p_change_req_id,
                                                        p_journal_date            => trunc(sysdate),
                                                        p_transaction_date        => trunc(sysdate),
                                                        p_currency_code           => r_change_con_rec.currency,
                                                        p_exchange_rate_quotation => 'DIRECT QUOTATION',
                                                        p_hd_exchange_rate_type   => 'MANUAL',
                                                        p_hd_exchange_rate        => 1,
                                                        p_ln_exchange_rate_type   => 'MANUAL',
                                                        p_ln_exchange_rate        => 1);
    
      if v_create_je_flag = 0 then
        update con_contract_change_req
           set create_je_flag = 'Y'
         where change_req_id = p_change_req_id;
      end if;
    
    end if;
    --1.1 把合同头表上的bp_id_tenant更改为行上的承租人,(以防出现变更承租人,头行不一致)
  
    select cb.bp_id
      into v_bp_id
      from con_contract_bp cb
     where cb.contract_id = p_change_req_id
       and cb.bp_category = 'TENANT'
       and cb.enabled_flag = 'Y';
  
    update con_contract cc
       set cc.bp_id_tenant = v_bp_id
     where cc.contract_id = p_change_req_id;
  
    --add by chenlingfeng
    --在覆盖数据前,獲取變更前 tenant_bp_id
    select cc.bp_id_tenant
      into v_before_change_bp_id
      from con_contract cc
     where cc.contract_id = r_change_req_rec.contract_id;
  
    -- 2) 确认数据,覆盖原始合同
    con_contract_history_pkg.change_req_confirm(p_change_req_id => p_change_req_id,
                                                p_user_id       => p_user_id);
    --覆蓋billing_bp_id
    update con_contract_cashflow t
       set t.billing_bp_id = v_before_change_bp_id
     where t.contract_id = r_change_req_rec.contract_id
       and t.billing_bp_id is null;
  
    --将变更后的承租人id更新现金流表的开票人id 
    --add by zhuxianfei
    select t.bp_id_tenant, t.btb_payment_date
      into v_bill_bp_id, v_start_req_time
      from con_contract t
     where t.contract_id = r_change_req_rec.contract_id;
  
    update con_contract_cashflow ccc
       set ccc.billing_bp_id = v_bill_bp_id
     where ccc.contract_id = r_change_req_rec.contract_id
       and ccc.cf_status = 'RELEASE'
       and ccc.due_date > v_start_req_time;
  
    --3) 更新合同表历史合同id字段  add by Harry 9952 2017/1/18                                            
    update con_contract c
       set c.history_contract_id =
           (select max(h.document_history_id)
              from hls_document_history h
             where h.document_id = r_change_req_rec.contract_id
               and h.usage_code = 'HISTORY')
     where c.contract_id in
           (r_change_con_rec.contract_id, r_change_req_rec.change_req_id);
    --承租人变更结束更新CALL_DETAIL的bp_id,modify by lpc for doosan
    if r_change_req_rec.document_type = 'LEASSE_CHAG' then
      update call_detail cd
         set cd.bp_id =
             (select t.bp_id_tenant
                from con_contract t
               where t.contract_id = r_change_req_rec.contract_id)
       where cd.wo_id in
             (select wo_id
                from work_orders
               where business_type <> 6
                 and contract_id = r_change_req_rec.contract_id)
         and cd.call_detail_type in ('TENANT', 'AFTERLEASE');
      --只有在承租人变更通过的时候,这4个字段deduct_bank, bankacctname, bankacctid,expireddate要删除
      update con_contract c
         set c.deduct_bank  = null,
             c.bankacctname = null,
             c.bankacctid   = null,
             c.expireddate  = null
       where c.contract_id = r_change_req_rec.contract_id;
    end if;
  
    --add end
  
    --add by chenlingfeng 
    --更新从app来的担保人信息
    if r_change_req_rec.document_type = 'LEASSE_CHAG' then
    
      ds_document_interface_pkg.set_app_guarantor_to_con(p_contract_id => r_change_req_rec.contract_id,
                                                         p_user_id     => p_user_id);
    
    end if;
  
    /*SELECT ci.lease_times,
          ci.down_payment_ratio
     INTO v_times,
          v_down_payment_ratio
     FROM con_contract_lease_item ci
    WHERE ci.contract_id = p_change_req_id;*/
  
    select cc.wfl_instance_id
      into v_instance_id
      from con_contract cc
     where cc.contract_id = p_change_req_id;
  
    /*UPDATE zj_wfl_workflow_instance zi
      SET zi.times              = v_times,
          zi.down_payment_ratio = v_down_payment_ratio
    WHERE zi.instance_id = v_instance_id;*/
    --承租人变更、回款账户变更不进行收入确认处理 add by 20191012 Spencer 3893
    if r_change_req_rec.document_type not in ('LEASE_CHAG', 'ACC_CHAG') then
      begin
        select to_number(to_char(min(cf.due_date), 'yyyymm'))
          into v_start_period
          from con_contract_cashflow cf, con_contract cc
         where cf.contract_id = p_change_req_id
           and cc.contract_id = cf.contract_id
              
           and cf.cf_direction = 'INFLOW'
           and cf.times = cc.ccr_start_times;
      
        --收益分摊
        con_unearned_fin_income_pkg.allocate_unearned_fin_income(p_biz_type                  => 'LEASE',
                                                                 p_contract_id               => r_change_req_rec.contract_id,
                                                                 p_start_internal_period_num => v_start_period,
                                                                 p_user_id                   => p_user_id);
      
      exception
        when no_data_found then
          null; --找不到期间不做处理 --chenlingfeng
      end;
    end if;
  exception
    when others then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'con_contract_change_req_pkg',
                                                     p_procedure_function_name => 'change_req_cinfirm');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
  end;

  --合同变更申请拒绝
  procedure change_req_reject(p_change_req_id in number,
                              p_user_id       in number) is
  
  begin
    con_contract_history_pkg.change_req_reject(p_change_req_id => p_change_req_id,
                                               p_user_id       => p_user_id);
  
  exception
    when others then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'con_contract_change_req_pkg',
                                                     p_procedure_function_name => 'change_req_reject');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;

  --合同变更申请取消
  procedure change_req_cancel(p_change_req_id in number,
                              p_user_id       in number) is
  
  begin
    con_contract_history_pkg.change_req_cancel(p_change_req_id => p_change_req_id,
                                               p_user_id       => p_user_id);
  
  exception
    when others then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'con_contract_change_req_pkg',
                                                     p_procedure_function_name => 'change_req_cancel');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;

  --从正式环境搬过来的insert 过程
  procedure submit_con_change_req(p_change_req_id con_contract_change_req.change_req_id%type,
                                  p_user_id       con_contract_change_req.created_by%type) is
    v_change_req_rec  con_contract_change_req%rowtype;
    v_contract_rec    con_contract%rowtype;
    v_approval_method hls_document_type.approval_method%type;
    v_calc_hd_rec     hls_fin_calculator_hd%rowtype;
    e_change_req_status_error exception;
    e_con_status_error        exception;
    e_calc_error              exception;
    e_simulation_error        exception;
    v_bp_id_tenant number;
  begin
    v_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id,
                                               p_user_id       => p_user_id);
  
    if v_change_req_rec.simulation_flag = 'Y' then
      raise e_simulation_error;
    end if;
  
    --当是罚息变更申请的时候,没有存contract_id,所以不需要走这一步
    if v_change_req_rec.contract_id is not null then
      v_contract_rec := con_contract_pkg.get_contract_rec(p_contract_id => v_change_req_rec.contract_id,
                                                          p_user_id     => p_user_id);
      if v_change_req_rec.req_status not in ('NEW', 'REJECT') then
        raise e_change_req_status_error;
      end if;
      --导入的历史数据 不校验
      if nvl(v_contract_rec.history_flag, 'N') <> 'Y' then
        begin
          select *
            into v_calc_hd_rec
            from hls_fin_calculator_hd
           where calc_session_id = v_contract_rec.calc_session_id;
        
          if nvl(v_calc_hd_rec.calc_successful, 'N') = 'N' or
             v_calc_hd_rec.price_list <> v_contract_rec.price_list then
            raise e_calc_error;
          end if;
        exception
          when no_data_found then
            null;
        end;
      end if;
    
    end if;
    /*if v_contract_rec.contract_status not in ('NEW', 'PAID','SIGN','PRINTED') then
      raise e_con_status_error;
    end if;*/
  
    --如果变更承租人,需要更新合同头
    begin
      select b.bp_id
        into v_bp_id_tenant
        from con_contract_bp b
       where b.contract_id = p_change_req_id
         and b.enabled_flag = 'Y'
         and b.bp_category = 'TENANT'
         and rownum = 1;
      update con_contract a
         set a.bp_id_tenant = v_bp_id_tenant
       where a.contract_id = p_change_req_id;
    exception
      when no_data_found then
        null;
    end;
  
    select approval_method
      into v_approval_method
      from hls_document_type
     where document_category = v_change_req_rec.document_category
       and document_type = v_change_req_rec.document_type;
  
    if v_approval_method = 'WORK_FLOW' then
      con_contract_chg_req_wfl_pkg.workflow_start(p_change_req_id => p_change_req_id,
                                                  p_user_id       => p_user_id);
    else
      update con_contract_change_req t
         set t.req_status     = 'APPROVING',
             t.submit_date    = sysdate,
             last_updated_by  = p_user_id,
             last_update_date = sysdate
       where t.change_req_id = p_change_req_id;
    end if;
  exception
    when e_change_req_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CHANGE_REQ_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'submit_con_change_req');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_con_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CON_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'submit_con_change_req');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_calc_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CALCULATOR_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'submit_con_change_req');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_simulation_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.SIMULATION_FLAG_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'submit_con_change_req');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;

  function get_con_contract_change_req_s return number is
    v_con_contract_change_req_id number;
  begin
    select con_contract_change_req_s.nextval
      into v_con_contract_change_req_id
      from dual;
    return v_con_contract_change_req_id;
  end;

  procedure insert_con_change_req(p_change_req_id          out con_contract_change_req.change_req_id%type,
                                  p_batch_id               con_contract_change_req.batch_id%type,
                                  p_change_req_number      con_contract_change_req.change_req_number%type default null,
                                  p_document_type          con_contract_change_req.document_type%type,
                                  p_document_category      con_contract_change_req.document_category%type,
                                  p_business_type          con_contract_change_req.business_type%type,
                                  p_company_id             con_contract_change_req.company_id%type,
                                  p_contract_id            con_contract_change_req.contract_id%type,
                                  p_req_status             con_contract_change_req.req_status%type,
                                  p_req_date               con_contract_change_req.req_date%type,
                                  p_change_req_profile     con_contract_change_req.change_req_profile%type default null,
                                  p_reschedule_start_date  con_contract_change_req.reschedule_start_date%type default null,
                                  p_reschedule_start_times con_contract_change_req.reschedule_start_times%type default null,
                                  p_reschedule_new_date    con_contract_change_req.reschedule_new_date%type default null,
                                  p_reschedule_total_times con_contract_change_req.reschedule_total_times%type default null,
                                  p_reschedule_last_time   con_contract_change_req.reschedule_last_time%type default null,
                                  p_reschedule_balance     con_contract_change_req.reschedule_balance%type default null,
                                  p_reschedule_direction   con_contract_change_req.reschedule_direction%type default null,
                                  p_reschedule_days        con_contract_change_req.reschedule_days%type default null,
                                  p_reschedule_fee         con_contract_change_req.reschedule_fee%type default null,
                                  p_calculate_rate         con_contract_change_req.calculate_rate%type default null,
                                  p_termination_date       con_contract_change_req.termination_date%type default null,
                                  p_overdue_amount         con_contract_change_req.overdue_amount%type default null,
                                  p_penalty                con_contract_change_req.penalty%type default null,
                                  p_rentals_payable        con_contract_change_req.rentals_payable%type default null,
                                  p_discount_total_amount  con_contract_change_req.discount_total_amount%type default null,
                                  p_undue_principal        con_contract_change_req.undue_principal%type default null,
                                  p_et_interest            con_contract_change_req.et_interest%type default null,
                                  p_mgt_fee_after_discount con_contract_change_req.mgt_fee_after_discount%type default null,
                                  p_et_fee                 con_contract_change_req.et_fee%type default null,
                                  p_residual_value         con_contract_change_req.residual_value%type default null,
                                  p_et_total_amount        con_contract_change_req.et_total_amount%type default null,
                                  p_last_paydue_date       con_contract_change_req.last_paydue_date%type default null,
                                  p_last_paydue_times      con_contract_change_req.last_paydue_times%type default null,
                                  p_discount_days          con_contract_change_req.discount_days%type default null,
                                  p_submit_date            con_contract_change_req.submit_date%type default null,
                                  p_approved_date          con_contract_change_req.approved_date%type default null,
                                  p_description            con_contract_change_req.description%type default null,
                                  p_calc_session_id        con_contract_change_req.calc_session_id%type default null,
                                  p_wfl_instance_id        con_contract_change_req.wfl_instance_id%type default null,
                                  p_user_id                con_contract_change_req.created_by%type,
                                  p_ref_v01                con_contract_change_req.ref_v01%type default null,
                                  p_ref_v02                con_contract_change_req.ref_v02%type default null,
                                  p_ref_v03                con_contract_change_req.ref_v03%type default null,
                                  p_ref_v04                con_contract_change_req.ref_v04%type default null,
                                  p_ref_v05                con_contract_change_req.ref_v05%type default null,
                                  p_ref_v06                con_contract_change_req.ref_v06%type default null,
                                  p_ref_v07                con_contract_change_req.ref_v07%type default null,
                                  p_ref_v08                con_contract_change_req.ref_v08%type default null,
                                  p_ref_v09                con_contract_change_req.ref_v09%type default null,
                                  p_ref_v10                con_contract_change_req.ref_v10%type default null,
                                  p_ref_n01                con_contract_change_req.ref_n01%type default null,
                                  p_ref_n02                con_contract_change_req.ref_n02%type default null,
                                  p_ref_n03                con_contract_change_req.ref_n03%type default null,
                                  p_ref_n04                con_contract_change_req.ref_n04%type default null,
                                  p_ref_n05                con_contract_change_req.ref_n05%type default null,
                                  p_ref_n06                con_contract_change_req.ref_n06%type default null,
                                  p_ref_n07                con_contract_change_req.ref_n07%type default null,
                                  p_ref_n08                con_contract_change_req.ref_n08%type default null,
                                  p_ref_n09                con_contract_change_req.ref_n09%type default null,
                                  p_ref_n10                con_contract_change_req.ref_n10%type default null,
                                  p_ref_d01                con_contract_change_req.ref_d01%type default null,
                                  p_ref_d02                con_contract_change_req.ref_d02%type default null,
                                  p_ref_d03                con_contract_change_req.ref_d03%type default null,
                                  p_ref_d04                con_contract_change_req.ref_d04%type default null,
                                  p_ref_d05                con_contract_change_req.ref_d05%type default null,
                                  p_ref_d06                con_contract_change_req.ref_d06%type default null,
                                  p_ref_d07                con_contract_change_req.ref_d07%type default null,
                                  p_ref_d08                con_contract_change_req.ref_d08%type default null,
                                  p_ref_d09                con_contract_change_req.ref_d09%type default null,
                                  p_ref_d10                con_contract_change_req.ref_d10%type default null) is
    v_batch_id          number;
    v_change_req_number con_contract_change_req.change_req_number%type;
  begin
    p_change_req_id := get_con_contract_change_req_s;
    if p_batch_id is null then
      v_batch_id := get_con_contract_change_req_s;
    else
      v_batch_id := p_batch_id;
    end if;
    if p_change_req_number is null then
      v_change_req_number := get_con_change_req_number(p_document_type     => p_document_type,
                                                       p_document_category => p_document_category,
                                                       p_company_id        => p_company_id,
                                                       p_req_date          => p_req_date,
                                                       p_user_id           => p_user_id);
    else
      v_change_req_number := p_change_req_number;
    end if;
    insert into con_contract_change_req
      (change_req_id,
       batch_id,
       change_req_number,
       document_type,
       document_category,
       business_type,
       company_id,
       contract_id,
       req_status,
       req_date,
       change_req_profile,
       reschedule_start_date,
       reschedule_start_times,
       reschedule_new_date,
       reschedule_total_times,
       reschedule_last_time,
       reschedule_balance,
       reschedule_direction,
       reschedule_days,
       reschedule_fee,
       calculate_rate,
       termination_date,
       overdue_amount,
       penalty,
       rentals_payable,
       discount_total_amount,
       undue_principal,
       et_interest,
       mgt_fee_after_discount,
       et_fee,
       residual_value,
       et_total_amount,
       last_paydue_date,
       last_paydue_times,
       discount_days,
       submit_date,
       approved_date,
       description,
       calc_session_id,
       wfl_instance_id,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date,
       ref_v01,
       ref_v02,
       ref_v03,
       ref_v04,
       ref_v05,
       ref_v06,
       ref_v07,
       ref_v08,
       ref_v09,
       ref_v10,
       ref_n01,
       ref_n02,
       ref_n03,
       ref_n04,
       ref_n05,
       ref_n06,
       ref_n07,
       ref_n08,
       ref_n09,
       ref_n10,
       ref_d01,
       ref_d02,
       ref_d03,
       ref_d04,
       ref_d05,
       ref_d06,
       ref_d07,
       ref_d08,
       ref_d09,
       ref_d10)
    values
      (p_change_req_id,
       v_batch_id,
       v_change_req_number,
       p_document_type,
       p_document_category,
       p_business_type,
       p_company_id,
       p_contract_id,
       p_req_status,
       p_req_date,
       p_change_req_profile,
       p_reschedule_start_date,
       p_reschedule_start_times,
       p_reschedule_new_date,
       p_reschedule_total_times,
       p_reschedule_last_time,
       p_reschedule_balance,
       p_reschedule_direction,
       p_reschedule_days,
       p_reschedule_fee,
       p_calculate_rate,
       p_termination_date,
       p_overdue_amount,
       p_penalty,
       p_rentals_payable,
       p_discount_total_amount,
       p_undue_principal,
       p_et_interest,
       p_mgt_fee_after_discount,
       p_et_fee,
       p_residual_value,
       p_et_total_amount,
       p_last_paydue_date,
       p_last_paydue_times,
       p_discount_days,
       p_submit_date,
       p_approved_date,
       p_description,
       p_calc_session_id,
       p_wfl_instance_id,
       p_user_id,
       sysdate,
       p_user_id,
       sysdate,
       p_ref_v01,
       p_ref_v02,
       p_ref_v03,
       p_ref_v04,
       p_ref_v05,
       p_ref_v06,
       p_ref_v07,
       p_ref_v08,
       p_ref_v09,
       p_ref_v10,
       p_ref_n01,
       p_ref_n02,
       p_ref_n03,
       p_ref_n04,
       p_ref_n05,
       p_ref_n06,
       p_ref_n07,
       p_ref_n08,
       p_ref_n09,
       p_ref_n10,
       p_ref_d01,
       p_ref_d02,
       p_ref_d03,
       p_ref_d04,
       p_ref_d05,
       p_ref_d06,
       p_ref_d07,
       p_ref_d08,
       p_ref_d09,
       p_ref_d10);
  
    /*aut_document_authority_pkg.insert_trx_user_authority
    (p_companY_id => p_company_id,
     p_owner_user_id => p_owner_user_id,
     p_trx_category => p_document_category,
     p_trx_id       => p_change_req_id,
     p_start_date => trunc(sysdate),
     p_end_date => null,
     p_user_id => p_user_id);   */
  
  end;

  procedure change_req_in_submit(p_change_req_id in number,
                                 p_user_id       in number) is
    r_change_req_rec con_contract_change_req%rowtype;
    r_change_con_rec con_contract%rowtype;
  
    v_approval_method hls_document_type.approval_method%type;
    v_calc_hd_rec     hls_fin_calculator_hd%rowtype;
  
    v_success_flag varchar2(1);
  
    e_change_req_status_error exception;
  
    e_calc_error               exception;
    e_simulation_error         exception;
    e_calc_session_id_notfound exception;
    e_is_calc_flag_err         exception;
  begin
    r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id,
                                               p_user_id       => p_user_id);
  
    if r_change_req_rec.simulation_flag = 'Y' then
      raise e_simulation_error;
    end if;
  
    r_change_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id,
                                                          p_user_id     => p_user_id);
  
    --r_change_req_rec.req_status := 'NEW';
    if r_change_req_rec.req_status not in ('NEW', 'REJECT') then
      raise e_change_req_status_error;
    end if;
  
    --当是罚息变更申请的时候,没有存contract_id,所以不需要走这一步
    if r_change_req_rec.ccr_price_list is not null then
      if r_change_req_rec.ccr_calc_session_id is null then
        raise e_calc_session_id_notfound;
      else
        select calc_successful
          into v_success_flag
          from hls_fin_calculator_hd
         where calc_session_id = r_change_req_rec.ccr_calc_session_id;
      
        if nvl(v_success_flag, 'N') <> 'Y' then
          raise e_calc_error;
        end if;
      
      end if;
    
      if nvl(r_change_req_rec.is_calc_flag, 'N') = 'N' then
        raise e_is_calc_flag_err;
      end if;
    
    end if;
  
    con_change_req_custom_pkg.before_submit_change_req(p_change_req_id => p_change_req_id,
                                                       p_user_id       => p_user_id);
  
    con_contract_history_pkg.calc_change_history(p_change_req_id => p_change_req_id,
                                                 p_user_id       => p_user_id);
  
    select approval_method
      into v_approval_method
      from hls_document_type
     where document_category = r_change_req_rec.document_category
       and document_type = r_change_req_rec.document_type;
  
    update con_contract_change_req t
       set t.req_status     = 'APPROVING',
           t.submit_date    = sysdate,
           last_updated_by  = p_user_id,
           last_update_date = sysdate
     where t.change_req_id = p_change_req_id;
  exception
    when e_change_req_status_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CHANGE_REQ_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_in_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_calc_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CALCULATOR_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_in_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_is_calc_flag_err then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.IS_CALC_FLAG_ERR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_in_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
    when e_calc_session_id_notfound then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.CALC_SESSION_ID_NOTFOUND',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_in_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
    when e_simulation_error then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_CHANGE_REQ_PKG.SIMULATION_FLAG_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_change_req_pkg',
                                                      p_procedure_function_name => 'change_req_in_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end change_req_in_submit;

  --合同变更(内部)
  procedure change_req_in_confirm(p_change_req_id in number,
                                  p_user_id       in number) is
  begin
    hls_document_compare_pkg.con_contract_compare(p_table_name    => 'CON_CONTRACT',
                                                  p_change_req_id => p_change_req_id,
                                                  p_from_table_pk => '',
                                                  p_to_table_pk   => p_change_req_id,
                                                  p_user_id       => p_user_id);
  
    change_req_in_submit(p_change_req_id => p_change_req_id,
                         p_user_id       => p_user_id);
  
    change_req_confirm(p_change_req_id => p_change_req_id,
                       p_user_id       => p_user_id);
  end change_req_in_confirm;

  --合同变更申请同意 add by fengyong for derong 
  procedure change_req_confirm_d(p_change_req_id in number,
                                 p_user_id       in number) is
    r_change_req_rec con_contract_change_req%rowtype;
    r_change_con_rec con_contract%rowtype;
    v_create_je_flag number;
    v_start_period   number;
  begin
  
    r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id,
                                               p_user_id       => p_user_id);
  
    r_change_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_change_req_id,
                                                          p_user_id     => p_user_id);
    --modify by fengyong 更新审批通过日期                                                         
    update con_contract_change_req q
       set q.approved_date = sysdate
     where change_req_id = p_change_req_id;
  
    --modify by fengyong at derong把生成变更凭证放到 “确认数据,覆盖原始合同”之前,
    --因为如果覆盖数据后,req的数据和normal的数据相同了,无法生成变更利息差额凭证
  
    -- 1)  生成变更凭证
    if nvl(r_change_req_rec.create_je_flag, 'N') = 'N' and
       r_change_req_rec.document_type = 'CONEXTHAG' then
    
      v_create_je_flag := gld_je_template_pkg.create_je(p_je_transaction_code => 'CON_CHANGE_REQ_CONFIRM',
                                                        p_company_id          => r_change_req_rec.company_id,
                                                        p_je_company_id       => r_change_req_rec.company_id,
                                                        p_user_id             => p_user_id,
                                                        /*凭证头id*/
                                                        p_journal_header_id => null,
                                                        /*以下为业务参数*/
                                                        p_hd_source_table         => 'CON_CONTRACT_CHANGE_REQ',
                                                        p_hd_source_id            => p_change_req_id,
                                                        p_ln_source_table         => 'CON_CONTRACT_CHANGE_REQ',
                                                        p_ln_source_id            => p_change_req_id,
                                                        p_journal_date            => trunc(sysdate),
                                                        p_transaction_date        => trunc(sysdate),
                                                        p_currency_code           => r_change_con_rec.currency,
                                                        p_exchange_rate_quotation => 'DIRECT QUOTATION',
                                                        p_hd_exchange_rate_type   => 'MANUAL',
                                                        p_hd_exchange_rate        => 1,
                                                        p_ln_exchange_rate_type   => 'MANUAL',
                                                        p_ln_exchange_rate        => 1);
    
      --IF v_create_je_flag = 0 THEN
      update con_contract_change_req
         set create_je_flag = 'Y'
       where change_req_id = p_change_req_id;
      --END IF;
    
    end if;
  
    -- 2) 确认数据,覆盖原始合同
    con_contract_history_pkg.change_req_confirm(p_change_req_id => p_change_req_id,
                                                p_user_id       => p_user_id);
  
    select to_number(to_char(cf.due_date, 'yyyymm'))
      into v_start_period
      from con_contract_cashflow cf, con_contract cc
     where cf.contract_id = p_change_req_id
       and cc.contract_id = cf.contract_id
       and cf.cf_item = 1
       and cf.cf_direction = 'INFLOW'
       and cf.times = cc.ccr_start_times;
  
    --收益分摊
    con_unearned_fin_income_pkg.allocate_unearned_fin_income(p_biz_type                  => 'LEASE',
                                                             p_contract_id               => r_change_req_rec.contract_id,
                                                             p_start_internal_period_num => v_start_period,
                                                             p_user_id                   => p_user_id);
  
  exception
    when others then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'con_contract_change_req_pkg',
                                                     p_procedure_function_name => 'change_req_cinfirm');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
  end;

  --modify by lpc 9874 合同承租人变更计算应缴纳金额
  procedure calc_leasse_date(p_contract_id          number,
                             p_btb_payment_date     date,
                             p_ccr_leasse_rental    out number, --应缴纳租金
                             p_ccr_leasse_principal out number, --应缴纳本金
                             p_ccr_leasse_interest  out number, --应缴纳利息
                             p_ccr_leasse_penalty   out number,
                             p_ccr_outstanding_cld  out number) is
    --应缴纳罚息
    v_contract_rec            con_contract%rowtype;
    v_con_contract_change_req con_contract_change_req%rowtype;
    v_total_penalty_amt       number;
    v_times                   number;
    v_received_penalty        number;
    v_count                   number;
    v_count1                  number;
  begin
  
    select *
      into v_con_contract_change_req
      from con_contract_change_req
     where change_req_id = p_contract_id;
  
    --如果开始日期之后有发生核销或者第一期之前的则不能选
    select count(1)
      into v_count
      from con_contract_cashflow ccc
     where ccc.contract_id = p_contract_id
       and ccc.due_date > p_btb_payment_date
       and ccc.cf_item = 1
       and ccc.times = 1
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE';
    if v_con_contract_change_req.document_type <> 'LEASSE_CHAG' then
      if v_count > 0 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '变更开始日期要大于第一期租金的应收日,请选择正确的日期!',
                                                       p_created_by              => -1,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'calc_leasse_date');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
    end if;
  
    select count(1)
      into v_count1
      from con_contract_cashflow ccc
     where ccc.contract_id = p_contract_id
       and ccc.due_date > p_btb_payment_date
       and ccc.cf_item <> 304
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE'
       and ccc.write_off_flag <> 'NOT';
    if v_count1 > 0 then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '变更开始日期之后的期数存在核销,请选择正确的日期或者进行核销反冲!',
                                                     p_created_by              => -1,
                                                     p_package_name            => 'con_contract_change_req_pkg',
                                                     p_procedure_function_name => 'calc_leasse_date');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    end if;
    --根据结算日期计算应缴纳金额,结算日期之前未完全核销租金
    select nvl(sum(nvl(ccc.due_amount, 0) - nvl(ccc.received_amount, 0)), 0) sum_ccr_leasse_rental, --应缴纳租金
           nvl(sum(nvl(ccc.principal, 0) - nvl(ccc.received_principal, 0)),
               0) sum_ccr_leasse_principal, --应缴纳本金
           nvl(sum(nvl(ccc.interest, 0) - nvl(ccc.received_interest, 0)), 0) sum_ccr_leasse_interest --应缴纳利息
      into p_ccr_leasse_rental,
           p_ccr_leasse_principal,
           p_ccr_leasse_interest
      from con_contract_cashflow ccc
     where ccc.contract_id = p_contract_id
       and ccc.write_off_flag <> 'FULL'
       and ccc.cf_item = 1
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE'
       and ccc.due_date <= p_btb_payment_date;
  
    --计算应缴罚息
    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_btb_payment_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) loop
    
      --计算penalty
    
      calc_penalty(p_cashflow_rec      => c_contract_cashflow,
                   p_contract_rec      => v_contract_rec,
                   p_calc_date         => p_btb_payment_date,
                   p_calc_method       => 'DAY_END',
                   p_total_penalty_amt => v_total_penalty_amt);
    
      p_ccr_leasse_penalty := nvl(p_ccr_leasse_penalty, 0) +
                              v_total_penalty_amt;
    
    end loop;
  
    /*--计算已核销的逾期罚息
    Select nvl(Sum(nvl(ccc.received_amount, 0)), 0)
      Into v_received_penalty
      From con_contract_cashflow ccc
     Where ccc.due_date < p_btb_payment_date
       And ccc.cf_item = 9
       And ccc.contract_id = p_contract_id
       And ccc.cf_direction = 'INFLOW'
       And ccc.cf_status = 'RELEASE';
    --去除已核销的罚息
    p_ccr_leasse_penalty := p_ccr_leasse_penalty - v_received_penalty;*/
    --计算未到期本金
    --获取当前变更日期是第几期
    select min(ccc.times)
      into v_times
      from con_contract_cashflow ccc
     where ccc.due_date > p_btb_payment_date
       and ccc.cf_item = 1
       and ccc.contract_id = p_contract_id
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE';
  
    --根据开始期数计算未到期本金
    select nvl(sum(nvl(ccc.principal, 0) - nvl(ccc.received_principal, 0)),
               0) sum_ccr_outstanding_incld
      into p_ccr_outstanding_cld
      from con_contract_cashflow ccc
     where ccc.contract_id =
           (select ccr.contract_id
              from con_contract_change_req ccr
             where ccr.change_req_id = p_contract_id)
       and ccc.write_off_flag <> 'FULL'
       and ccc.cf_item = 1
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE'
       and ccc.times >= v_times;
  
  end;

  procedure calc_leasse_pay(p_contract_id           number,
                            p_ccr_start_times       number,
                            p_btb_payment_date      date default null,
                            p_interim_rental        number default null,
                            p_layout_code           varchar2,
                            p_ccr_outstanding_times out number,
                            p_ccr_dept              out varchar2,
                            p_ccr_outstanding_incld out varchar2,
                            p_ccr_fee               out number) is
    v_contract_rec con_contract%rowtype;
    v_his_due_date date;
    v_count        number;
  begin
    select t.*
      into v_contract_rec
      from con_contract t
     where t.contract_id = p_contract_id;
    -- 非月付变更时计算变更费用
    p_ccr_outstanding_times := v_contract_rec.lease_times -
                               p_ccr_start_times + 1;
    --根据开始期数计算债权余额和未到期本金
    select nvl(sum(nvl(ccc.due_amount, 0)), 0) sum_ccr_dept
      into p_ccr_dept
      from con_contract_cashflow ccc
     where ccc.contract_id =
           (select cr.contract_id
              from con_contract_change_req cr
             where cr.change_req_id = p_contract_id)
       and ccc.write_off_flag <> 'FULL'
       and ccc.cf_item in (1, 8)
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE'
       and ccc.times >= p_ccr_start_times;
  
    --根据开始期数计算未到期本金
    select nvl(sum(nvl(ccc.principal, 0) - nvl(ccc.received_principal, 0)),
               0) sum_ccr_outstanding_incld
      into p_ccr_outstanding_incld
      from con_contract_cashflow ccc
     where ccc.contract_id =
           (select cr.contract_id
              from con_contract_change_req cr
             where cr.change_req_id = p_contract_id)
       and ccc.write_off_flag <> 'FULL'
       and ccc.cf_item = 1
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE'
       and ccc.times >= p_ccr_start_times;
    --还款日变更
    if p_layout_code = 'CCR_UNIVERSAL_PAYDATE' then
      --核销的不能进行更改,要从没核销的期数开始修改
      select count(1)
        into v_count
        from con_contract_cashflow ccc
       where ccc.contract_id = p_contract_id
         and ccc.times >= p_ccr_start_times
         and ccc.cf_item <> 304
         and ccc.cf_direction = 'INFLOW'
         and ccc.cf_status = 'RELEASE'
         and ccc.write_off_flag <> 'NOT';
      if v_count > 0 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '延期开始期数之后的期数存在核销,请选择正确的日期或者进行核销反冲!',
                                                       p_created_by              => -1,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'calc_leasse_pay');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
      --p_ccr_outstanding_times := v_contract_rec.lease_times - p_ccr_start_times + 1;
      --变更手续费,录入或修改延期开始期数,还款日,延期利率的时候重算,计算逻辑如果开始期数是第一期,用首期支付日,即round((变更后的还款日期-合同上的first_pay_date)*延期利率* 剩余本金/360,2),如果不是第一期 ,就取round(变更后的还款日期-(原合同的上一期的应付日+1个月),2),*延期利率*剩余本金/360,2)
      if p_ccr_start_times = 1 then
        p_ccr_fee := round((p_btb_payment_date -
                           v_contract_rec.first_pay_date) *
                           p_interim_rental * p_ccr_outstanding_incld / 360,
                           2);
      else
        --取原合同现金流延期开始期数的应收日
        select min(ccc.due_date)
          into v_his_due_date
          from con_contract_cashflow ccc
         where ccc.contract_id =
               (select cr.contract_id
                  from con_contract_change_req cr
                 where cr.change_req_id = p_contract_id)
           and ccc.times = p_ccr_start_times
           and ccc.cf_direction = 'INFLOW'
           and ccc.cf_status = 'RELEASE';
        if v_his_due_date > p_btb_payment_date then
          sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '变更后首期还款日不能小于原合同现金流延期开始期数的应收日!',
                                                         p_created_by              => -1,
                                                         p_package_name            => 'con_contract_change_req_pkg',
                                                         p_procedure_function_name => 'calc_leasse_pay');
        
          raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                  sys_raise_app_error_pkg.g_err_line_id);
        end if;
        p_ccr_fee := round((p_btb_payment_date - v_his_due_date) *
                           p_interim_rental * p_ccr_outstanding_incld / 360,
                           2);
      end if;
    end if;
  end;

  procedure calc_penalty(p_cashflow_rec      con_contract_cashflow%rowtype,
                         p_contract_rec      con_contract%rowtype,
                         p_calc_date         date,
                         p_calc_method       varchar2,
                         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;
  
    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  := round(c_write_off.write_off_due_amount *
                                v_penalty_days *
                                p_contract_rec.penalty_rate,
                                nvl(p_contract_rec.currency_precision, 2));
      end if;
    
      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  := round((p_cashflow_rec.due_amount -
                                nvl(p_cashflow_rec.received_amount, 0)) *
                                (v_penalty_days) *
                                p_contract_rec.penalty_rate,
                                nvl(p_contract_rec.currency_precision, 2));
      end if;
    
      v_total_penalty_amt := v_total_penalty_amt + v_penalty_amt;
    
    end if;
    p_total_penalty_amt := v_total_penalty_amt;
  
  end;

  --LEASSE_CHAG承租人期数1,LEASEHOLD_CHAG租赁物期数2,CONEXTHAG冬雨季期数3,DEBT_CHAG债务再调整期数4,PART_CHAG部分中途偿还期数5,PAY_CHAG非月转月付期数6
  procedure insert_ccr_cashflow(p_contract_id        number,
                                p_ccr_document_type  varchar2, --变更类型
                                p_btb_payment_date   date default null,
                                p_bp_id              number default null,
                                p_lease_item_id      number default null,
                                p_pattern            varchar2 default null,
                                p_machine_number     varchar2 default null,
                                p_interim_times      number default null, --延期期数
                                p_ccr_start_times    number default null, --延期开始期数
                                p_hd_user_col_v04    varchar2 default null,
                                p_btb_finance_amount number default null, --部分中途偿还本金
                                p_user_id            varchar2) is
    v_con_contract       con_contract%rowtype;
    v_bp_master_rec      hls_bp_master%rowtype;
    v_count              number;
    v_count1             number;
    v_sql                varchar2(32767);
    v_column_list        varchar2(32767);
    v_from_doc_pk_column varchar2(1000);
    v_to_doc_pk_column   varchar2(1000); --目标头表主键列
    v_pk_value           number;
    v_project_number     varchar2(100);
    v_lease_times        number;
    v_times              number;
    v_irr                number;
    v_conexthag_count    number;
    v_contract_id        number;
    v_lease_item_count   number;
    v_ccr_start_date     date; --ADDED BY FENG FOR DS 20181109
    v_due_date           date;
    v_not_contract_id    varchar2(100);
    v_con_bp_id          number;
  begin
    select *
      into v_con_contract
      from con_contract
     where contract_id = p_contract_id;
  
    --取原合同的总期数
    select t.lease_times, t.irr, t.contract_id
      into v_lease_times, v_irr, v_contract_id
      from con_contract t
     where t.contract_id =
           (select cr.contract_id
              from con_contract_change_req cr
             where cr.change_req_id = p_contract_id);
    --当报价计算时将变更日期存放到合同表中,应李媛要求
    if p_ccr_document_type not in ('LEASSE_CHAG', 'LEASEHOLD_CHAG') then
      update con_contract t
         set t.hd_user_col_d01 =
             (select tt.req_date
                from con_contract_change_req tt
               where tt.change_req_id = p_contract_id)
       where t.contract_id = p_contract_id;
    end if;
    --承租人变更
    if p_ccr_document_type = 'LEASSE_CHAG' then
    
      --如果没有变更之后的主承租人记录则删除原有主承租人,插入现有承租人
      select *
        into v_bp_master_rec
        from hls_bp_master t1
       where t1.bp_id = p_bp_id;
    
      begin
        select b.record_id
          into v_con_bp_id
          from con_contract_bp b
         where b.bp_category = 'TENANT'
           and b.bp_type = 'TENANT'
           and b.enabled_flag = 'Y'
           and b.contract_id = p_contract_id;
      exception
        when no_data_found then
          v_con_bp_id := con_contract_bp_s.nextval;
        
      end;
    
      hls_document_transfer_pkg.doc_to_doc(p_from_doc_table        => 'HLS_BP_MASTER',
                                           p_from_doc_pk           => v_bp_master_rec.bp_id,
                                           p_to_doc_table          => 'CON_CONTRACT_BP',
                                           p_to_doc_pk             => v_con_bp_id,
                                           p_to_doc_column_1       => 'CONTRACT_ID',
                                           p_to_doc_column_1_value => p_CONTRACT_id,
                                           p_to_doc_column_2       => 'CONTRACT_SEQ',
                                           p_to_doc_column_2_value => '1',
                                           p_to_doc_column_3       => 'BP_SEQ',
                                           p_to_doc_column_3_value => '1',
                                           p_to_doc_column_4       => 'CREAT_BP_FLG',
                                           p_to_doc_column_4_value => 'N',
                                           p_to_doc_column_5       => 'BP_CATEGORY',
                                           p_to_doc_column_5_value => 'TENANT',
                                           p_to_doc_column_6       => 'BP_TYPE',
                                           p_to_doc_column_6_value => 'TENANT',
                                           p_user_id               => p_user_id);
    
      /* DELETE FROM con_contract_bp t
       WHERE t.contract_id = p_contract_id
         AND t.bp_category = 'TENANT';
      
      --获取from_doc_table to_doc_table主键字段
      v_from_doc_pk_column := contract_change_req_pkg.get_pk_column(p_table_name => 'HLS_BP_MASTER');
      v_to_doc_pk_column   := contract_change_req_pkg.get_pk_column(p_table_name => 'CON_CONTRACT_BP');
      --获取表list
      
      v_pk_value    := con_contract_bp_s.nextval;
      v_column_list := contract_change_req_pkg.get_column_list(p_from_doc_table => 'HLS_BP_MASTER',
                                                               p_to_doc_table   => 'CON_CONTRACT_BP');
      v_sql         := 'insert into CON_CONTRACT_BP (' ||
                       v_to_doc_pk_column || ',' || v_column_list ||
                       ',contract_id,bp_id,bp_code
             ,created_by,creation_date,last_updated_by,last_update_date' || ')' ||
                       ' ( select ' || v_pk_value || ',' || v_column_list || ',
            ' || p_contract_id || ',' || p_bp_id ||
                       ',''' || v_bp_master_rec.bp_code || ''',' ||
                       p_user_id || ',sysdate' || ',' || p_user_id ||
                       ',sysdate ' || ' from HLS_BP_MASTER  where ' ||
                       v_from_doc_pk_column || ' = ' || p_bp_id || ')';
      \*INSERT INTO ccx_temp_logs
        (log_id,
         msg,
         creation_date,
         created_by,
         last_update_date,
         last_updated_by)
      VALUES
        (ccx_temp_logs_s.nextval,
         v_sql,
         SYSDATE,
         1,
         SYSDATE,
         1);
      COMMIT;*\
      EXECUTE IMMEDIATE v_sql;*/
    
      select p.project_number
        into v_project_number
        from prj_project p
       where p.project_id =
             (select t.project_id
                from con_contract t
               where t.contract_id = p_contract_id);
      --更新合同名称
      update con_contract t
         set t.contract_name = '斗山租赁-' || v_bp_master_rec.bp_name || '-' ||
                               v_project_number
       where t.contract_id = p_contract_id;
      --租赁物变更
    elsif p_ccr_document_type = 'LEASEHOLD_CHAG' then
    
      select count(1)
        into v_lease_item_count
        from con_contract_lease_item ci
       where ci.contract_id not in ((select cr.change_req_id
                                      from con_contract_change_req cr
                                     where cr.contract_id = v_contract_id
                                    union all
                                    select v_contract_id
                                      from dual))
         and (ci.machine_number || '-' || ci.pattern) =
             (p_machine_number || '-' || p_pattern)
         and ci.equipment_type = 'MAIN'
         and exists (select 1
                from con_contract c
               where c.contract_id = ci.contract_id
                 and c.data_class = 'NORMAL'
                 and c.contract_status <> ('CANCEL'));
      if v_lease_item_count > 0 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '租赁物变更的机型机号不能与系统中其他合同的机型机号相同!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'insert_ccr_cashflow');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      else
        --更新con_contract_lease_item表租赁物id,型号,机号
        update con_contract_lease_item t
           set t.lease_item_id  = p_lease_item_id,
               t.pattern        = p_pattern,
               t.machine_number = p_machine_number
         where t.contract_id = p_contract_id
           and t.equipment_type = 'MAIN';
      end if;
      --冬雨季变更
    elsif p_ccr_document_type = 'CONEXTHAG' then
      --变更开始期数不能填租金计划的前3期和后3期
      if p_ccr_start_times in (1,
                               2,
                               3,
                               v_con_contract.lease_times - 2,
                               v_con_contract.lease_times - 1,
                               v_con_contract.lease_times) then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '变更开始期数不能填租金计划的前3期和后3期!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'insert_ccr_cashflow');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
      --延期期数不能超过3期
      if p_interim_times > 3 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '延期期数不能超过3期!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'insert_ccr_cashflow');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
      --如果起始期数之后有发生核销的则不能选
      select count(1)
        into v_count1
        from con_contract_cashflow ccc
       where ccc.contract_id = p_contract_id
         and ccc.times > p_ccr_start_times
         and ccc.cf_item <> 304
         and ccc.cf_direction = 'INFLOW'
         and ccc.cf_status = 'RELEASE'
         and ccc.write_off_flag <> 'NOT';
    
      if v_count1 > 0 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '变更开始期数之后的期数存在核销,请选择正确的期数!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'insert_ccr_cashflow');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
      --顺延,INTERIM_TIMES延期期数 CCR_START_TIMES变更开始期数 LEASE_TIMES 还款期数 CCR_OUTSTANDING_TIMES剩余期数 =INTERIM_TIMES延期期数+(LEASE_TIMES-CCR_START_TIMES+1)
      if p_hd_user_col_v04 = 'DELAY' then
        --更新合同表的剩余期数
        update con_contract t
           set t.ccr_outstanding_times = p_interim_times +
                                         (v_lease_times - p_ccr_start_times + 1),
               t.lease_times           = v_lease_times + p_interim_times
         where t.contract_id = p_contract_id;
        --平摊,AVERAGE平摊时 原期数不变 ,CCR_OUTSTANDING_TIMES剩余期数=总期数-开始期数+1
      elsif p_hd_user_col_v04 = 'AVERAGE' then
        update con_contract t
           set t.ccr_outstanding_times = v_lease_times - p_ccr_start_times + 1
         where t.contract_id = p_contract_id;
      end if;
      --缩期变更
    elsif p_ccr_document_type = 'CUT_CHAG' then
      --如果起始期数之后有发生核销的则不能选
      select count(1)
        into v_count1
        from con_contract_cashflow ccc
       where ccc.contract_id = p_contract_id
         and ccc.times > p_ccr_start_times
         and ccc.cf_item <> 304
         and ccc.cf_direction = 'INFLOW'
         and ccc.cf_status = 'RELEASE'
         and ccc.write_off_flag <> 'NOT';
    
      if v_count1 > 0 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '变更开始期数之后的期数存在核销,请选择正确的期数!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'insert_ccr_cashflow');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
    
      --更新合同表的剩余期数
      update con_contract t
         set t.ccr_outstanding_times = v_lease_times - p_ccr_start_times -
                                       p_interim_times + 1,
             t.lease_times           = v_lease_times + p_interim_times
       where t.contract_id = p_contract_id;
      --月付变更
    elsif p_ccr_document_type = 'PAY_CHAG' then
      /*--原期数不变 ,CCR_OUTSTANDING_TIMES剩余期数=总期数-开始期数+1
      UPDATE con_contract t
         SET t.ccr_outstanding_times = v_lease_times - p_ccr_start_times + 1
       WHERE t.contract_id = p_contract_id;*/
      --起始期数的应收日要大于当前系统日期
      select ccc.due_date
        into v_due_date
        from con_contract_cashflow ccc
       where ccc.contract_id = p_contract_id
         and ccc.times = p_ccr_start_times
         and ccc.cf_item = 1
         and ccc.cf_direction = 'INFLOW'
         and ccc.cf_status = 'RELEASE';
    
      if v_due_date <= sysdate then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '起始期数的应收日要大于当前系统日期,请选择正确的期数!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'insert_ccr_cashflow');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
    
      --如果起始期数之后有发生核销的则不能选
      select count(1)
        into v_count1
        from con_contract_cashflow ccc
       where ccc.contract_id = p_contract_id
         and ccc.times > p_ccr_start_times
         and ccc.cf_item <> 304
         and ccc.cf_direction = 'INFLOW'
         and ccc.cf_status = 'RELEASE'
         and ccc.write_off_flag <> 'NOT';
    
      if v_count1 > 0 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '变更开始期数之后的期数存在核销,请选择正确的期数!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'insert_ccr_cashflow');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
      --部分中途偿还期数
    elsif p_ccr_document_type = 'PART_CHAG' then
      --部分中途偿还本金金额不超过剩余本金
      if p_btb_finance_amount >=
         v_con_contract.ccr_outstanding_prin_tax_incld then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '部分中途偿还金额不能超过剩余本金!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'con_contract_change_req_pkg',
                                                       p_procedure_function_name => 'insert_ccr_cashflow');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
      --获取当前变更日期是第几期
      select min(ccc.times)
        into v_times
        from con_contract_cashflow ccc
       where ccc.due_date > p_btb_payment_date
         and ccc.cf_item = 1
         and ccc.contract_id = v_contract_id --用原来的合同判断 modify by chenlingfeng
         and ccc.cf_direction = 'INFLOW'
         and ccc.cf_status = 'RELEASE';
      --原期数加1 ,CCR_OUTSTANDING_TIMES剩余期数=总期数-开始期数+2
      update con_contract t
         set t.ccr_outstanding_times = v_lease_times - v_times + 2,
             t.ccr_start_times       = v_times,
             t.lease_times           = v_lease_times + 1
       where t.contract_id = p_contract_id;
      --债务再调整期数
    elsif p_ccr_document_type = 'DEBT_CHAG' then
      --获取冬雨季延期变更通过的总次数
      select count(1)
        into v_conexthag_count
        from con_contract_change_req cr
       where cr.contract_id = p_contract_id
         and cr.document_type = 'CONEXTHAG'
         and cr.req_status = 'APPROVED';
      --历史冬雨季延期申请次数等于1的话,延期期数不能大于4
      if v_conexthag_count = 1 and p_interim_times > 4 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '历史冬雨季延期申请次数等于1,延期期数不能大于4!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'CON_CHANGE_REQ_CALC_ITFC_PKG',
                                                       p_procedure_function_name => 'create_change_req');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
    
      --历史冬雨季延期申请次数等于0的话,延期期数不能大于6
      if v_conexthag_count = 0 and p_interim_times > 6 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '历史冬雨季延期申请次数等于0,延期期数不能大于6!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'CON_CHANGE_REQ_CALC_ITFC_PKG',
                                                       p_procedure_function_name => 'create_change_req');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
    
      --获取当前变更日期是第几期
      select min(ccc.times)
        into v_times
        from con_contract_cashflow ccc
       where ccc.due_date > p_btb_payment_date
         and ccc.cf_item = 1
         and ccc.contract_id = v_contract_id --用原来的合同判断 modify by chenlingfeng
         and ccc.cf_direction = 'INFLOW'
         and ccc.cf_status = 'RELEASE';
    
      --ADDED BY FENG FOR DS 20181109 
      --获取变更开始日期
      select due_date
        into v_ccr_start_date
        from con_contract_cashflow ccc
       where ccc.contract_id = v_contract_id --用原来的合同判断 modify by chenlingfeng
         and ccc.cf_item = 1
         and ccc.times = v_times - 1;
    
      --延期开始日期小于原日程第6期的日期,则不能申请
      if v_times < 7 then
        sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '延期开始日期小于原日程第6期的日期,不能发起申请!',
                                                       p_created_by              => p_user_id,
                                                       p_package_name            => 'CON_CHANGE_REQ_CALC_ITFC_PKG',
                                                       p_procedure_function_name => 'create_change_req');
      
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      end if;
    
      --原期数加1 ,CCR_OUTSTANDING_TIMES剩余期数=总期数-开始期数+1
      update con_contract t
         set t.ccr_outstanding_times = p_interim_times +
                                       (v_lease_times - v_times + 1),
             t.ccr_start_times       = v_times,
             t.ccr_start_date        = v_ccr_start_date, --ADDED BY FENG FOR DS 20181109
             t.lease_times           = v_lease_times + p_interim_times,
             t.int_rate_display      = v_irr --将原合同的irr更新到变更的利率
       where t.contract_id = p_contract_id;
    end if;
  end;

  --变更插入手续费
  procedure insert_ccr_fee(p_contract_id   number,
                           p_user_id       number,
                           p_document_type varchar2 default null,
                           p_cf_item       varchar2 default null,
                           p_cf_type       varchar2 default null,
                           p_due_date      date default null,
                           p_ccr_fee       number) is
    v_contract_cashflow_rec    con_contract_cashflow%rowtype;
    v_con_contract             con_contract%rowtype;
    v_con_contract_cashflow_id number;
    v_count                    number;
    v_times                    number;
    v_time                     number;
    v_tax_rate                 number;
  begin
  
    select *
      into v_con_contract
      from con_contract
     where contract_id = p_contract_id;
    --当手续费为空或0时不插
    if p_ccr_fee = 0 or p_ccr_fee is null then
      null;
    else
      begin
        --获取当前变更日期是第几期
        select nvl(min(ccc.times), v_con_contract.lease_times)
          into v_times
          from con_contract_cashflow ccc
         where ccc.due_date > p_due_date
           and ccc.cf_item = 1
           and ccc.contract_id = p_contract_id
           and ccc.cf_direction = 'INFLOW'
           and ccc.cf_status = 'RELEASE';
        --获取当前期数变更手续费次数
        select count(1)
          into v_count
          from con_contract_cashflow
         where contract_id = p_contract_id
           and (times between v_times and v_times + 1)
           and cf_item = '304';
        if p_document_type = 'DEBT_CHAG' then
          --获取期数
          select decode(p_cf_item,
                        '10',
                        v_times,
                        to_number(v_times || '.' || (v_count + 1)))
            into v_time
            from dual;
        else
          select to_number(v_times || '.' || (v_count + 1))
            into v_time
            from dual;
        end if;
        --获取税率
        select decode(p_cf_item,
                      '304',
                      con_contract_pkg.get_tax_rate(p_contract_id => p_contract_id,
                                                    p_cf_item     => p_cf_item),
                      '10',
                      con_contract_pkg.get_tax_rate(p_contract_id => p_contract_id,
                                                    p_cf_item     => p_cf_item))
          into v_tax_rate
          from dual;
      
        select con_contract_cashflow_s.nextval
          into v_con_contract_cashflow_id
          from dual;
      
        v_contract_cashflow_rec.cashflow_id     := v_con_contract_cashflow_id;
        v_contract_cashflow_rec.contract_id     := p_contract_id; --当前合同ID
        v_contract_cashflow_rec.cf_item         := p_cf_item; --计算器行表的列,所配置的现金流项目
        v_contract_cashflow_rec.cf_type         := p_cf_type; -- 根据现金流项目取得现金流类型
        v_contract_cashflow_rec.cf_direction    := 'INFLOW';
        v_contract_cashflow_rec.cf_status       := 'RELEASE';
        v_contract_cashflow_rec.times           := v_time; --期数
        v_contract_cashflow_rec.calc_date       := to_date(to_char(p_due_date,
                                                                   'yyyy-mm-dd'),
                                                           'yyyy-mm-dd'); --计算日 --modify by liuhaojie 原为p_due_date
        v_contract_cashflow_rec.due_date        := to_date(to_char(p_due_date,
                                                                   'yyyy-mm-dd'),
                                                           'yyyy-mm-dd'); --到期日
        v_contract_cashflow_rec.fin_income_date := to_date(to_char(p_due_date,
                                                                   'yyyy-mm-dd'),
                                                           'yyyy-mm-dd'); --收入计算日
        v_contract_cashflow_rec.due_amount      := p_ccr_fee; --租金
        v_contract_cashflow_rec.principal       := null; --本金
        v_contract_cashflow_rec.interest        := null; --利息
      
        v_contract_cashflow_rec.overdue_status         := 'N';
        v_contract_cashflow_rec.write_off_flag         := 'NOT';
        v_contract_cashflow_rec.penalty_process_status := 'NORMAL';
        v_contract_cashflow_rec.billing_status         := 'NOT';
        v_contract_cashflow_rec.vat_due_amount         := round(p_ccr_fee / (1 +
                                                                v_tax_rate) *
                                                                v_tax_rate,
                                                                2);
        v_contract_cashflow_rec.net_due_amount         := p_ccr_fee -
                                                          v_contract_cashflow_rec.vat_due_amount;
        v_contract_cashflow_rec.created_by             := p_user_id; --当前用户
        v_contract_cashflow_rec.creation_date          := sysdate; --系统时间
        v_contract_cashflow_rec.last_updated_by        := p_user_id; --当前用户
        v_contract_cashflow_rec.last_update_date       := sysdate; --系统时间
      
        con_contract_pkg.insert_contract_cashflow(p_contract_cashflow_rec => v_contract_cashflow_rec);
      end;
    end if;
  end;
  --还款日变更更新还款日
  procedure update_change_req(p_contract_id      number,
                              p_ccr_start_times  number,
                              p_btb_payment_date date) is
    v_contract_rec con_contract%rowtype;
    v_count        number;
  begin
    select t.*
      into v_contract_rec
      from con_contract t
     where t.contract_id = p_contract_id;
    --2.录入开始变更的期数,从这一期开始变,将支付日变成变更后还款日,之后每一期都顺延一个月,add_months(变更后还款日,12/H6),5.刷新现金流上日期,从变更开始期数修改 +,开始期数的日期为变更后开始的还款日,然后之后每期顺延,过个现金流,例如 租金1 ,客户手续费3  延期利息102,103,104 罚息9 相同期数的日期刷成一样的
    for c_rec in p_ccr_start_times .. v_contract_rec.lease_times + 1 loop
      v_count := c_rec - p_ccr_start_times;
      update con_contract_cashflow ccc
         set ccc.due_date        = add_months(p_btb_payment_date, v_count),
             ccc.calc_date       = add_months(p_btb_payment_date, v_count),
             ccc.fin_income_date = add_months(p_btb_payment_date, v_count)
       where ccc.contract_id = p_contract_id
         and ccc.times = c_rec
         and ccc.cf_item <> '9'
         and ccc.cf_direction = 'INFLOW'
         and ccc.cf_status = 'RELEASE';
    end loop;
  exception
    when others then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  sqlerrm,
                                                     p_created_by              => -1,
                                                     p_package_name            => 'con_contract_change_req_pkg',
                                                     p_procedure_function_name => 'update_change_req');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;

end con_contract_change_req_pkg;
/