create or replace package cus_con_contract_repo_pkg is

  -- Author  : Spencer 3893
  -- Created : 9/10/2019 2:21:13 PM
  -- Purpose : 合同回购
  function get_contract_repo_number(p_document_type varchar2,
                                    p_document_date date,
                                    p_company_id    number,
                                    p_user_id       number,
                                    p_contract_id   number default null)
    return varchar2;
  --add by Spencer 3893 20190911 回购申请                             
  procedure create_change_req_repo(p_contract_id     in number,
                                   p_req_type        in varchar2,
                                   p_user_id         in number,
                                   p_simulation_flag in varchar2 default 'N',
                                   p_change_req_id   out number);

  --回购现金流计算
  procedure calc_repo_cashflows(p_contract_id in number,
                                p_user_id     in number);
  --通过回购解约日获取回购相关信息                      
  procedure calc_repo_date_amount(p_contract_id                in number,
                                  p_repurchase_date            date,
                                  p_last_rent_due_date         out date,
                                  p_sum_received_rent_amount   out number,
                                  p_sum_unreceived_rent_amount out number,
                                  p_sum_overdue_amount         out number,
                                  p_sum_unreceived_principal   out number,
                                  p_due_amount                 out number,
                                  p_user_id                    in number);
  --提交工作流
  procedure submit_change_req_repo(p_change_req_id in number,
                                   p_layout_code   in varchar2,
                                   p_user_id       in number);
end cus_con_contract_repo_pkg;
/
create or replace package body cus_con_contract_repo_pkg is
  e_lock_table exception;
  pragma exception_init(e_lock_table, -54);

  function get_contract_rec(p_contract_id number, p_user_id number)
    return con_contract%rowtype is
    v_con_contract_rec con_contract%rowtype;
  begin
    select *
      into v_con_contract_rec
      from con_contract t
     where t.contract_id = p_contract_id
       for update nowait;
    return v_con_contract_rec;
  exception
    when e_lock_table then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_PKG.CONTRACT_LOCK_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'cus_con_contract_repo_pkg',
                                                      p_procedure_function_name => 'get_contract_rec');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;
  function get_contract_repo_number(p_document_type varchar2,
                                    p_document_date date,
                                    p_company_id    number,
                                    p_user_id       number,
                                    p_contract_id   number default null)
    return varchar2 is
    v_no            con_contract.repurchase_contract_number%type;
    v_agent_bp_code varchar2(30);
    v_price_ref_v05 varchar2(200);
    v_product_code  varchar2(2000);
  
    e_product_code_err        exception;
    e_get_contract_number_err exception;
  begin
    begin
      select hbm.bp_code
        into v_agent_bp_code
        from con_contract_bp t, hls_bp_master hbm
       where t.contract_id = p_contract_id
         and t.bp_category = 'AGENT'
         and t.enabled_flag = 'Y'
         and t.bp_id = hbm.bp_id
         and rownum = 1;
    exception
      when no_data_found then
        null;
    end;
  
    begin
      select l.ref_v05
        into v_price_ref_v05
        from con_contract t, hls_price_list l
       where t.contract_id = p_contract_id
         and t.price_list = l.price_list;
    exception
      when no_data_found then
        null;
    end;
  
    v_no := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => 'CHANGE_REQUEST',
                                                     p_document_type     => p_document_type,
                                                     p_company_id        => p_company_id,
                                                     p_operation_unit_id => null,
                                                     p_operation_date    => p_document_date,
                                                     p_agent_code        => v_agent_bp_code,
                                                     p_price_ref_v05     => v_price_ref_v05,
                                                     p_product_code      => v_product_code,
                                                     p_created_by        => p_user_id);
  
    if v_no = fnd_code_rule_pkg.c_error then
      raise e_get_contract_number_err;
    end if;
  
    return v_no;
  
  exception
    when e_get_contract_number_err then
    
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '获取编码失败,请联系系统管理员!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'cus_con_contract_repo_pkg',
                                                     p_procedure_function_name => 'get_contract_repo_number');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  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            => 'cus_con_contract_repo_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;
  --add by Spencer 3893 20190911 回购申请                             
  procedure create_change_req_repo(p_contract_id     in number,
                                   p_req_type        in varchar2,
                                   p_user_id         in number,
                                   p_simulation_flag in varchar2 default 'N',
                                   p_change_req_id   out number) is
    v_change_req_id number;
  
    v_contract_rec con_contract%rowtype;
  
  begin
    v_contract_rec := get_contract_rec(p_contract_id, p_user_id);
  
    if v_contract_rec.contract_status <> 'INCEPT' and p_req_type = 'REPUR' then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '只有起租状态的合同才能进行回购申请!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'cus_con_contract_repo_pkg',
                                                     p_procedure_function_name => 'create_change_req_repo');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    end if;
  
    con_contract_history_pkg.create_change_req(p_contract_id       => p_contract_id,
                                               p_change_req_id     => v_change_req_id,
                                               p_req_date          => sysdate,
                                               p_ref_v01           => p_user_id,
                                               p_description       => null,
                                               p_change_quotation  => null,
                                               p_change_lease_item => null,
                                               p_change_bp         => null,
                                               p_req_doc_type      => p_req_type,
                                               p_simulation_flag   => p_simulation_flag,
                                               p_repo_change_flag  => 'Y',
                                               p_user_id           => p_user_id);
    --生成回购编号                                            
    v_contract_rec.repurchase_contract_number := get_contract_repo_number(p_document_type => p_req_type,
                                                                          p_document_date => sysdate,
                                                                          p_company_id    => v_contract_rec.company_id,
                                                                          p_user_id       => p_user_id);
    --更新回购编号到变更流程中的新合同
    update con_contract c
       set c.repurchase_contract_number = v_contract_rec.repurchase_contract_number,
           c.last_updated_by            = p_user_id,
           c.last_update_date           = sysdate
     where c.contract_id = v_change_req_id;
  
    cus_con_change_req_pkg.insert_hls_document_use_seal(p_change_req_id       => v_change_req_id,
                                                        p_document_table      => 'CON_CONTRACT_CHANGE_REQ',
                                                        p_instruments_types   => '解除融资租赁合同协议',
                                                        p_common_seal         => 'Y',
                                                        p_corporate_visa_seal => 'Y',
                                                        p_legal_seal          => 'N',
                                                        p_con_special_seal    => 'N',
                                                        p_financial_seal      => 'N',
                                                        p_supervisio_seal     => 'N',
                                                        p_seal_number         => 'SEAL_O_F_NUMBER',
                                                        p_user_id             => p_user_id);
    cus_con_change_req_pkg.insert_hls_document_use_seal(p_change_req_id       => v_change_req_id,
                                                        p_document_table      => 'CON_CONTRACT_CHANGE_REQ',
                                                        p_instruments_types   => '《融资租赁合同》之补充协议(回购合同)',
                                                        p_common_seal         => 'Y',
                                                        p_corporate_visa_seal => 'Y',
                                                        p_legal_seal          => 'N',
                                                        p_con_special_seal    => 'N',
                                                        p_financial_seal      => 'N',
                                                        p_supervisio_seal     => 'N',
                                                        p_seal_number         => 'SEAL_O_F_NUMBER',
                                                        p_user_id             => p_user_id);
    p_change_req_id := v_change_req_id;
  end;
  procedure delete_cashflow(p_contract_id number, p_times number) is
  begin
    delete from con_contract_cashflow cf
     where cf.times > p_times
       and cf.cf_item in (1, 9)
       and cf.contract_id = p_contract_id;
  end;
  -- 手续费处理
  procedure sc_cashflow_hander(p_contract_id           number,
                               p_service_charges       number,
                               p_paymengt_deadlinedate date,
                               p_times                 number,
                               p_user_id               in number) is
    v_cashflow_rec          con_contract_cashflow%rowtype;
    v_outstanding_principal number; --剩余本金
    v_principal             number; --本金
    v_interest              number; --利息
    v_due_date              date;
  begin
    v_interest              := round(p_service_charges * 0.13 / 1.13, 2);
    v_principal             := round(p_service_charges / 1.13, 2);
    v_outstanding_principal := 0;
    v_due_date              := p_paymengt_deadlinedate;
  
    v_cashflow_rec.cashflow_id                := con_contract_cashflow_s.nextval;
    v_cashflow_rec.contract_id                := p_contract_id;
    v_cashflow_rec.cf_item                    := 304;
    v_cashflow_rec.cf_type                    := 3;
    v_cashflow_rec.cf_direction               := 'INFLOW';
    v_cashflow_rec.cf_status                  := 'RELEASE';
    v_cashflow_rec.times                      := p_times;
    v_cashflow_rec.calc_date                  := v_due_date;
    v_cashflow_rec.due_date                   := v_due_date;
    v_cashflow_rec.due_amount                 := p_service_charges;
    v_cashflow_rec.net_due_amount             := round(p_service_charges / 1.13,
                                                       2);
    v_cashflow_rec.vat_due_amount             := round(p_service_charges * 0.13 / 1.13,
                                                       2);
    v_cashflow_rec.principal                  := v_principal;
    v_cashflow_rec.net_principal              := round(v_principal / 1.13,
                                                       2);
    v_cashflow_rec.vat_principal              := round(v_principal * 0.13 / 1.13,
                                                       2);
    v_cashflow_rec.interest                   := v_interest;
    v_cashflow_rec.net_interest               := round(v_interest / 1.13, 2);
    v_cashflow_rec.vat_interest               := round(v_interest * 0.13 / 1.13,
                                                       2);
    v_cashflow_rec.received_amount            := 0;
    v_cashflow_rec.received_principal         := 0;
    v_cashflow_rec.received_interest          := 0;
    v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal;
    v_cashflow_rec.write_off_flag             := 'NOT';
    v_cashflow_rec.overdue_status             := 'NOT';
    v_cashflow_rec.penalty_process_status     := 'NORMAL';
    v_cashflow_rec.billing_status             := 'N';
  
    v_cashflow_rec.generated_source := 'MANUAL';
    v_cashflow_rec.created_by       := p_user_id;
    v_cashflow_rec.creation_date    := sysdate;
    v_cashflow_rec.last_updated_by  := p_user_id;
    v_cashflow_rec.last_update_date := sysdate;
    insert into con_contract_cashflow values v_cashflow_rec;
  end sc_cashflow_hander;
  -- Refactored procedure fund_prossession_cost_hander 
  procedure fund_prossession_cost_hander(p_contract_id           number,
                                         p_fund_possession_cost  number,
                                         p_paymengt_deadlinedate date,
                                         p_times                 number,
                                         p_user_id               number) is
    v_cashflow_rec          con_contract_cashflow%rowtype;
    v_outstanding_principal number; --剩余本金
    v_principal             number; --本金
    v_interest              number; --利息
    v_due_date              date;
  begin
    v_interest              := round(p_fund_possession_cost * 0.13 / 1.13,
                                     2);
    v_principal             := round(p_fund_possession_cost / 1.13, 2);
    v_outstanding_principal := 0;
    v_due_date              := p_paymengt_deadlinedate;
  
    v_cashflow_rec.cashflow_id                := con_contract_cashflow_s.nextval;
    v_cashflow_rec.contract_id                := p_contract_id;
    v_cashflow_rec.cf_item                    := 14;
    v_cashflow_rec.cf_type                    := 14;
    v_cashflow_rec.cf_direction               := 'INFLOW';
    v_cashflow_rec.cf_status                  := 'RELEASE';
    v_cashflow_rec.times                      := p_times;
    v_cashflow_rec.calc_date                  := v_due_date;
    v_cashflow_rec.due_date                   := v_due_date;
    v_cashflow_rec.due_amount                 := p_fund_possession_cost;
    v_cashflow_rec.net_due_amount             := round(p_fund_possession_cost / 1.13,
                                                       2);
    v_cashflow_rec.vat_due_amount             := round(p_fund_possession_cost * 0.13 / 1.13,
                                                       2);
    v_cashflow_rec.principal                  := v_principal;
    v_cashflow_rec.net_principal              := round(v_principal / 1.13,
                                                       2);
    v_cashflow_rec.vat_principal              := round(v_principal * 0.13 / 1.13,
                                                       2);
    v_cashflow_rec.interest                   := v_interest;
    v_cashflow_rec.net_interest               := round(v_interest / 1.13, 2);
    v_cashflow_rec.vat_interest               := round(v_interest * 0.13 / 1.13,
                                                       2);
    v_cashflow_rec.received_amount            := 0;
    v_cashflow_rec.received_principal         := 0;
    v_cashflow_rec.received_interest          := 0;
    v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal;
    v_cashflow_rec.write_off_flag             := 'NOT';
    v_cashflow_rec.overdue_status             := 'NOT';
    v_cashflow_rec.penalty_process_status     := 'NORMAL';
    v_cashflow_rec.billing_status             := 'N';
  
    v_cashflow_rec.generated_source := 'MANUAL';
    v_cashflow_rec.created_by       := p_user_id;
    v_cashflow_rec.creation_date    := sysdate;
    v_cashflow_rec.last_updated_by  := p_user_id;
    v_cashflow_rec.last_update_date := sysdate;
    insert into con_contract_cashflow values v_cashflow_rec;
  end fund_prossession_cost_hander;
  --回购现金流计算
  procedure calc_repo_cashflows(p_contract_id in number,
                                p_user_id     in number) is
    v_contract_rec con_contract%rowtype;
    v_cashflow_rec con_contract_cashflow%rowtype;
  
    v_due_amount            number; --每期租金
    v_outstanding_principal number; --剩余本金
    v_principal             number; --本金
    v_interest              number; --利息
    v_due_date              date;
    v_calc_due_date         date;
    v_times                 number;
    v_calc_days             number;
  
    e_due_amount_error       exception;
    e_repo_calc_amount_error exception;
  begin
    --回购合同
    v_contract_rec := get_contract_rec(p_contract_id, p_user_id);
    select *
      into v_cashflow_rec
      from con_contract_cashflow f
     where f.due_date = v_contract_rec.last_rent_due_date
       and f.cf_item = 1
       and f.cf_type = 1
       and f.contract_id = p_contract_id;
  
    --计算前删除本次变更起始期及之后期,
    delete_cashflow(p_contract_id, v_cashflow_rec.times);
    -- 一次性回购     
    if v_contract_rec.repurchase_types = 'ONE_TIME_REPURCHASE' then
      v_times                 := v_cashflow_rec.times + 1;
      v_cashflow_rec          := null;
      v_interest              := round(v_contract_rec.repurchase_tatal_amount * 0.13 / 1.13,
                                       2);
      v_principal             := round(v_contract_rec.repurchase_tatal_amount / 1.13,
                                       2);
      v_outstanding_principal := 0;
      v_due_date              := v_contract_rec.paymengt_deadlinedate;
    
      v_cashflow_rec.cashflow_id                := con_contract_cashflow_s.nextval;
      v_cashflow_rec.contract_id                := v_contract_rec.contract_id;
      v_cashflow_rec.cf_item                    := 1;
      v_cashflow_rec.cf_type                    := 1;
      v_cashflow_rec.cf_direction               := 'INFLOW';
      v_cashflow_rec.cf_status                  := 'RELEASE';
      v_cashflow_rec.times                      := v_times;
      v_cashflow_rec.calc_date                  := v_due_date;
      v_cashflow_rec.due_date                   := v_due_date;
      v_cashflow_rec.due_amount                 := v_contract_rec.repurchase_tatal_amount;
      v_cashflow_rec.net_due_amount             := round(v_contract_rec.repurchase_tatal_amount / 1.13,
                                                         2);
      v_cashflow_rec.vat_due_amount             := round(v_contract_rec.repurchase_tatal_amount * 0.13 / 1.13,
                                                         2);
      v_cashflow_rec.principal                  := v_principal;
      v_cashflow_rec.net_principal              := round(v_principal / 1.13,
                                                         2);
      v_cashflow_rec.vat_principal              := round(v_principal * 0.13 / 1.13,
                                                         2);
      v_cashflow_rec.interest                   := v_interest;
      v_cashflow_rec.net_interest               := round(v_interest / 1.13,
                                                         2);
      v_cashflow_rec.vat_interest               := round(v_interest * 0.13 / 1.13,
                                                         2);
      v_cashflow_rec.received_amount            := 0;
      v_cashflow_rec.received_principal         := 0;
      v_cashflow_rec.received_interest          := 0;
      v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal;
      v_cashflow_rec.write_off_flag             := 'NOT';
      v_cashflow_rec.overdue_status             := 'NOT';
      v_cashflow_rec.penalty_process_status     := 'NORMAL';
      v_cashflow_rec.billing_status             := 'N';
    
      v_cashflow_rec.generated_source := 'MANUAL';
      v_cashflow_rec.created_by       := p_user_id;
      v_cashflow_rec.creation_date    := sysdate;
      v_cashflow_rec.last_updated_by  := p_user_id;
      v_cashflow_rec.last_update_date := sysdate;
      insert into con_contract_cashflow values v_cashflow_rec;
      --手续费
      if v_contract_rec.repurchase_service_charges is not null and
         v_contract_rec.repurchase_service_charges > 0 then
        v_cashflow_rec := null;
        sc_cashflow_hander(p_contract_id           => v_contract_rec.contract_id,
                           p_service_charges       => v_contract_rec.repurchase_service_charges,
                           p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate,
                           p_times                 => v_times,
                           p_user_id               => p_user_id);
      end if;
      --资金占用费
      if v_contract_rec.fund_possession_cost is not null and
         v_contract_rec.fund_possession_cost > 0 then
        v_cashflow_rec := null;
        fund_prossession_cost_hander(p_contract_id           => v_contract_rec.contract_id,
                                     p_fund_possession_cost  => v_contract_rec.fund_possession_cost,
                                     p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate,
                                     p_times                 => v_times,
                                     p_user_id               => p_user_id);
      end if;
    
      --分期回购  
    elsif v_contract_rec.repurchase_types = 'STAGE_REPURCHASE' then
      v_outstanding_principal := v_contract_rec.repurchase_tatal_amount; --回购价款总额
      if v_outstanding_principal > 0 then
        -- 计算租金
        select round(abs(hls_financial_calculate_pkg.pmt(nvl(v_contract_rec.repurchase_interest_rate,
                                                             v_contract_rec.int_rate_display) / 12, --租赁年利率/支付频率
                                                         v_contract_rec.payment_period, --租赁期限
                                                         v_outstanding_principal, --剩余本金
                                                         0,
                                                         0)),
                     2)
          into v_due_amount
          from dual;
      
        if nvl(v_due_amount, 0) <= 0 then
          hls_sys_log_pkg.log('回购合同计算租金异常,计算利率:' ||
                              v_contract_rec.repurchase_interest_rate ||
                              ' ,支付频率:' ||
                              v_contract_rec.payment_frequency ||
                              ' ,租赁期限:' || v_contract_rec.payment_period ||
                              ' ,回购价款总额:' || v_outstanding_principal);
          raise e_due_amount_error;
        end if;
        v_due_date := v_contract_rec.paymengt_deadlinedate;
        v_times    := v_cashflow_rec.times + 1;
        for cur in 1 .. v_contract_rec.payment_period loop
          v_cashflow_rec := null;
          --最后一期
          if cur = v_contract_rec.payment_period then
            v_interest              := v_due_amount -
                                       v_outstanding_principal;
            v_principal             := v_outstanding_principal;
            v_outstanding_principal := 0;
          else
            if cur >= 2 then
              v_calc_due_date := add_months(v_due_date, 1);
              v_calc_days     := v_calc_due_date - v_due_date;
              v_interest      := round(v_outstanding_principal *
                                       nvl(v_calc_days, 1) *
                                       (nvl(v_contract_rec.repurchase_interest_rate,
                                            v_contract_rec.int_rate_display) / 360),
                                       2);
            else
              --第一期利息天数
              v_interest := round(v_outstanding_principal * 1 *
                                  (nvl(v_contract_rec.repurchase_interest_rate,
                                       v_contract_rec.int_rate_display) / 360),
                                  2);
            end if;
            v_principal             := v_due_amount - v_interest;
            v_outstanding_principal := v_outstanding_principal -
                                       v_principal;
          end if;
          v_cashflow_rec.cashflow_id                := con_contract_cashflow_s.nextval;
          v_cashflow_rec.contract_id                := v_contract_rec.contract_id;
          v_cashflow_rec.cf_item                    := 1;
          v_cashflow_rec.cf_type                    := 1;
          v_cashflow_rec.cf_direction               := 'INFLOW';
          v_cashflow_rec.cf_status                  := 'RELEASE';
          v_cashflow_rec.times                      := v_times;
          v_cashflow_rec.calc_date                  := v_due_date;
          v_cashflow_rec.due_date                   := v_due_date;
          v_cashflow_rec.due_amount                 := v_due_amount;
          v_cashflow_rec.net_due_amount             := round(v_due_amount / 1.13,
                                                             2);
          v_cashflow_rec.vat_due_amount             := round(v_due_amount * 0.13 / 1.13,
                                                             2);
          v_cashflow_rec.principal                  := v_principal;
          v_cashflow_rec.net_principal              := round(v_principal / 1.13,
                                                             2);
          v_cashflow_rec.vat_principal              := round(v_principal * 0.13 / 1.13,
                                                             2);
          v_cashflow_rec.interest                   := v_interest;
          v_cashflow_rec.net_interest               := round(v_interest / 1.13,
                                                             2);
          v_cashflow_rec.vat_interest               := round(v_interest * 0.13 / 1.13,
                                                             2);
          v_cashflow_rec.received_amount            := 0;
          v_cashflow_rec.received_principal         := 0;
          v_cashflow_rec.received_interest          := 0;
          v_cashflow_rec.outstanding_prin_tax_incld := v_outstanding_principal;
          v_cashflow_rec.write_off_flag             := 'NOT';
          v_cashflow_rec.overdue_status             := 'NOT';
          v_cashflow_rec.penalty_process_status     := 'NORMAL';
          v_cashflow_rec.billing_status             := 'N';
        
          v_cashflow_rec.generated_source := 'MANUAL';
          v_cashflow_rec.created_by       := p_user_id;
          v_cashflow_rec.creation_date    := sysdate;
          v_cashflow_rec.last_updated_by  := p_user_id;
          v_cashflow_rec.last_update_date := sysdate;
        
          insert into con_contract_cashflow values v_cashflow_rec;
          --最后期不需要校验剩余本金是否小于0
          if cur < 6 then
            v_due_date := add_months(v_due_date, 1);
            v_times    := v_times + 1;
          
            if v_outstanding_principal < 0 then
              raise e_repo_calc_amount_error;
            end if;
          end if;
        end loop;
        --手续费
        if v_contract_rec.repurchase_service_charges is not null and
           v_contract_rec.repurchase_service_charges > 0 then
          v_cashflow_rec := null;
          sc_cashflow_hander(p_contract_id           => v_contract_rec.contract_id,
                             p_service_charges       => v_contract_rec.repurchase_service_charges,
                             p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate,
                             p_times                 => v_times,
                             p_user_id               => p_user_id);
        end if;
        --资金占用费
        if v_contract_rec.fund_possession_cost is not null and
           v_contract_rec.fund_possession_cost > 0 then
          v_cashflow_rec := null;
          fund_prossession_cost_hander(p_contract_id           => v_contract_rec.contract_id,
                                       p_fund_possession_cost  => v_contract_rec.fund_possession_cost,
                                       p_paymengt_deadlinedate => v_contract_rec.paymengt_deadlinedate,
                                       p_times                 => v_times,
                                       p_user_id               => p_user_id);
        end if;
      elsif v_outstanding_principal = 0 then
        raise e_repo_calc_amount_error;
      end if;
    end if;
  
  exception
    when e_repo_calc_amount_error then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '回购价款总额小于0,请检查!',
                                                     p_created_by              => -1,
                                                     p_package_name            => 'cus_con_contract_repo_pkg',
                                                     p_procedure_function_name => 'calc_repo_cashflows');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_due_amount_error then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '回购合同计算租金出错,请联系系统管理员!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'cus_con_contract_repo_pkg',
                                                     p_procedure_function_name => 'calc_repo_cashflows');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end calc_repo_cashflows;
  --通过回购解约日获取回购相关信息                   
  procedure calc_repo_date_amount(p_contract_id                in number,
                                  p_repurchase_date            date,
                                  p_last_rent_due_date         out date,
                                  p_sum_received_rent_amount   out number,
                                  p_sum_unreceived_rent_amount out number,
                                  p_sum_overdue_amount         out number,
                                  p_sum_unreceived_principal   out number,
                                  p_due_amount                 out number,
                                  p_user_id                    in number) is
    v_contract_rec       con_contract%rowtype;
    v_cashflow_rec       con_contract_cashflow%rowtype;
    v_times              number;
    v_last_rent_due_date date;
    --已回收租金
    v_sum_received_rent_amount number;
    --剩余租金
    v_sum_unreceived_rent_amount number;
    --逾期租金
    v_sum_overdue_amount number;
    --剩余本金
    v_sum_unreceived_principal number;
    -- 逾期罚息
    v_due_amount       number;
    v_overdue_max_days number;
    e_write_off_flagg_err exception;
  begin
    select max(a.times)
      into v_times
      from con_contract_cashflow a
     where a.due_date >= p_repurchase_date
       and a.cf_item in (1, 9)
       and a.write_off_flag in ('PARTIAL', 'FULL')
       and a.contract_id = p_contract_id;
  
    if v_times >= 1 then
      raise e_write_off_flagg_err;
    end if;
    --回购日上一期due_date
    select f.due_date, f.outstanding_rental_tax_incld
      into v_last_rent_due_date, v_sum_unreceived_principal
      from con_contract_cashflow f
     where f.times = (select max(f.times)
                        from con_contract_cashflow f
                       where f.due_date < p_repurchase_date
                         and f.cf_item = 1
                         and f.cf_type = 1
                         and f.contract_id = p_contract_id)
       and f.due_date <= p_repurchase_date
       and f.cf_item = 1
       and f.cf_type = 1
       and f.contract_id = p_contract_id;
    p_last_rent_due_date := v_last_rent_due_date;
    --已回收租金
    select sum(nvl(F.RECEIVED_AMOUNT, 0))
      into v_sum_received_rent_amount
      from con_contract_cashflow f
     where f.write_off_flag != 'NOT'
       and f.cf_item = 1
       and f.cf_type = 1
       and f.contract_id = p_contract_id;
    p_sum_received_rent_amount := v_sum_received_rent_amount;
    --剩余租金
    select sum(f.due_amount) - sum(f.received_amount)
      into v_sum_unreceived_rent_amount
      from con_contract_cashflow f
     where f.cf_item = 1
       and f.cf_type = 1
       and f.contract_id = p_contract_id;
    p_sum_unreceived_rent_amount := v_sum_unreceived_rent_amount;
    --逾期租金
    select sum(nvl(f.overdue_amount, 0))
      into v_sum_overdue_amount
      from con_contract_cashflow f
     where f.write_off_flag != 'FULL'
       and f.overdue_status = 'Y'
       and f.cf_item = 1
       and f.cf_direction != 'NONCASH'
       and f.contract_id = p_contract_id;
    p_sum_overdue_amount := nvl(v_sum_overdue_amount, 0);
    --剩余本金
    p_sum_unreceived_principal := v_sum_unreceived_principal;
    v_contract_rec             := get_contract_rec(p_contract_id, p_user_id);
    -- 逾期天数 overdue_max_days+现在至回购日天数
    v_overdue_max_days := nvl(v_contract_rec.overdue_max_days, 0) +
                          trunc(p_repurchase_date - sysdate);
    -- due_amount 逾期罚息,逾期罚息=逾期租金* 逾期天数 * 0.0004。回购总额中罚息由系统自动计算,可直接进行手动调整,不需要通过罚息减免流程;逾期罚息,计算到回购解约日为止
    v_due_amount := p_sum_overdue_amount * v_overdue_max_days * 0.0004;
    p_due_amount := v_due_amount;
  exception
    when e_write_off_flagg_err then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '第' ||
                                                                                  v_times ||
                                                                                  '期已有核销记录,请重新选择期数' ||
                                                                                  v_times ||
                                                                                  '之后数据发起回购!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'cus_con_contract_repo_pkg',
                                                     p_procedure_function_name => 'calc_repo_date_amount');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when others then
    
      null;
  end calc_repo_date_amount;

  --提交工作流
  procedure submit_change_req_repo(p_change_req_id in number,
                                   p_layout_code   in varchar2,
                                   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;
    e_attachment_error         exception;
  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);
  
    r_change_req_rec := get_con_change_req_rec(p_change_req_id => p_change_req_id,
                                               p_user_id       => p_user_id);
  
    /*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);
  
    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);
  
    cus_con_change_req_wfl_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);
  
  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            => 'cus_con_contract_repo_pkg',
                                                      p_procedure_function_name => 'submit_change_req_repo');
      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            => 'cus_con_contract_repo_pkg',
                                                      p_procedure_function_name => 'submit_change_req_repo');
      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            => 'cus_con_contract_repo_pkg',
                                                      p_procedure_function_name => 'submit_change_req_repo');
      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            => 'cus_con_contract_repo_pkg',
                                                      p_procedure_function_name => 'submit_change_req_repo');
      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            => 'cus_con_contract_repo_pkg',
                                                      p_procedure_function_name => 'submit_change_req_repo');
      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            => 'cus_con_contract_repo_pkg',
                                                      p_procedure_function_name => 'submit_change_req_repo');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
  end submit_change_req_repo;
end cus_con_contract_repo_pkg;
/