create or replace package cus_con_et_pkg is

  -- Author  : Spencer 3893
  -- Created : 9/19/2019 3:40:32 PM
  -- Purpose : 提前结清

  procedure create_change_req_et(p_contract_id     number,
                                 p_req_type        varchar2,
                                 p_user_id         number,
                                 p_simulation_flag varchar2,
                                 p_change_req_id   out number);
  --通过结清日获取结清相关信息                      
  procedure calc_et_date_amount(p_contract_id              in number,
                                p_et_date                  date,
                                p_et_fee                   in out number,
                                p_et_interest_rate         in number,
                                p_et_total_amount          out number,
                                p_et_due_amount            out number,
                                p_overdue_amount           out number,
                                p_penalty                  out number,
                                p_fund_possession_time     out number,
                                p_fund_possession_cost     out number,
                                p_SUM_UNRECEIVED_PRINCIPAL out number,
                                p_user_id                  in number);
  --结清现金流计算
  procedure calc_et_cashflows(p_contract_id in number, p_user_id in number);
  --结清提交
  procedure submit_change_req_et(p_change_req_id number,
                                 p_layout_code   varchar2,
                                 p_user_id       number);
end cus_con_et_pkg;
/
create or replace package body cus_con_et_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_et_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_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_et_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;
  procedure create_change_req_et(p_contract_id     number,
                                 p_req_type        varchar2,
                                 p_user_id         number,
                                 p_simulation_flag varchar2,
                                 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 = 'ET_CHAG' then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '只有起租状态的合同才能进行提前结清申请!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'cus_con_et_pkg',
                                                     p_procedure_function_name => 'create_change_req_et');
    
      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_et_flag           => 'Y',
                                               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 create_change_req_et;
  --通过结清日获取结清相关信息                      
  procedure calc_et_date_amount(p_contract_id              in number,
                                p_et_date                  date,
                                p_et_fee                   in out number,
                                p_et_interest_rate         in number,
                                p_et_total_amount          out number,
                                p_et_due_amount            out number,
                                p_overdue_amount           out number,
                                p_penalty                  out number,
                                p_fund_possession_time     out number,
                                p_fund_possession_cost     out number,
                                p_sum_unreceived_principal out number,
                                p_user_id                  in number) is
    v_contract_rec con_contract%rowtype;
    v_times        number;
    --剩余本金
    v_sum_unreceived_principal number;
    v_fund_possession_cost     number;
    v_fund_possession_time     number;
    v_sum_overdue_amount       number;
    v_penalty                  number;
    v_et_due_amount            number;
    v_et_total_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_et_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;
  
    v_contract_rec := get_contract_rec(p_contract_id, p_user_id);
  
    --结清日最近的未核销现金流,获取剩余本金
    select nvl(f.outstanding_prin_tax_incld, 0)
      into 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_et_date
                         and f.cf_item = 1
                         and f.cf_type = 1
                         and f.contract_id = p_contract_id)
       and f.due_date <= p_et_date
       and f.write_off_flag = 'NOT'
       and f.cf_item = 1
       and f.cf_type = 1
       and f.contract_id = p_contract_id;
    --上一次完全核销现金流
    begin
      select (p_et_date - f.due_date + 1) fund_possession_time
        into v_fund_possession_time
        from con_contract_cashflow f
       where f.due_date > p_et_date
         and f.write_off_flag = 'FULL'
         and f.cf_item = 1
         and f.cf_type = 1
         and f.cf_direction != 'NONCASH'
         and f.contract_id = p_contract_id;
    exception
      when no_data_found then
        v_fund_possession_time := 1;
    end;
    v_fund_possession_cost := v_sum_unreceived_principal *
                              nvl(v_fund_possession_time, 1) *
                              NVL(p_et_interest_rate,
                                  v_contract_rec.int_rate_display) / 360;
    --逾期租金
    select nvl(sum(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_type = 1
       and f.cf_direction != 'NONCASH'
       and f.contract_id = p_contract_id;
    -- 逾期天数 overdue_max_days+现在至回购日天数
    v_overdue_max_days := nvl(v_contract_rec.overdue_max_days, 0) +
                          v_fund_possession_time;
    -- v_penalty 逾期罚息,逾期罚息=逾期租金* 逾期天数 * 0.0004。回购总额中罚息由系统自动计算,可直接进行手动调整,不需要通过罚息减免流程;逾期罚息,计算到回购解约日为止
    v_penalty         := v_sum_overdue_amount * v_overdue_max_days * 0.0004;
    v_et_due_amount   := v_sum_unreceived_principal + v_sum_overdue_amount;
    v_et_total_amount := v_sum_unreceived_principal + p_et_fee +
                         v_fund_possession_cost +
                         nvl(v_contract_rec.residual_value, 0) +
                         v_sum_overdue_amount;
    --回写字段                    
    p_et_total_amount          := v_et_total_amount;
    p_et_due_amount            := v_et_due_amount;
    p_overdue_amount           := v_sum_overdue_amount;
    p_penalty                  := v_penalty;
    p_fund_possession_time     := v_fund_possession_time;
    p_fund_possession_cost     := v_fund_possession_cost;
    p_sum_unreceived_principal := v_sum_unreceived_principal;
  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_et_pkg',
                                                     p_procedure_function_name => 'calc_et_date_amount');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end calc_et_date_amount;
  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 calc_et_cashflows(p_contract_id in number, p_user_id in number) is
    v_change_req_rec con_contract_change_req%rowtype;
    v_cashflow_rec   con_contract_cashflow%rowtype;
    v_principal      number; --本金
    v_interest       number; --利息
    v_due_date       date;
    v_times          number;
  begin
    v_change_req_rec := get_con_change_req_rec(p_contract_id, p_user_id);
    select *
      into v_cashflow_rec
      from con_contract_cashflow f
     where f.times = (select max(f.times)
                        from con_contract_cashflow f
                       where f.due_date <= v_change_req_rec.termination_date
                         and f.cf_item = 1
                         and f.cf_type = 1
                         and f.contract_id = p_contract_id)
       and f.due_date <= v_change_req_rec.termination_date
       and f.cf_item = 1
       and f.cf_type = 1
       and f.contract_id = p_contract_id;
    v_times := v_cashflow_rec.times + 1;
    --计算前删除本次变更起始期及之后期,
    delete_cashflow(p_contract_id, v_cashflow_rec.times);
    --创建提前结清款现金流
    v_cashflow_rec := null;
    v_interest     := round(v_change_req_rec.et_total_amount * 0.13 / 1.13,
                            2);
    v_principal    := round(v_change_req_rec.et_total_amount / 1.13, 2);
    v_due_date     := v_change_req_rec.termination_date;
  
    v_cashflow_rec.cashflow_id                := con_contract_cashflow_s.nextval;
    v_cashflow_rec.contract_id                := p_contract_id;
    v_cashflow_rec.cf_item                    := 200;
    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_change_req_rec.et_total_amount;
    v_cashflow_rec.net_due_amount             := round(v_change_req_rec.et_total_amount / 1.13,
                                                       2);
    v_cashflow_rec.vat_due_amount             := round(v_change_req_rec.et_total_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 := 0;
    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;
  
    --创建提前结清手续费现金流
    v_cashflow_rec := null;
    v_interest     := round(v_change_req_rec.et_fee * 0.13 / 1.13, 2);
    v_principal    := round(v_change_req_rec.et_fee / 1.13, 2);
    v_due_date     := v_change_req_rec.termination_date;
  
    v_cashflow_rec.cashflow_id                := con_contract_cashflow_s.nextval;
    v_cashflow_rec.contract_id                := p_contract_id;
    v_cashflow_rec.cf_item                    := 11;
    v_cashflow_rec.cf_type                    := 11;
    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_change_req_rec.et_fee;
    v_cashflow_rec.net_due_amount             := round(v_change_req_rec.et_fee / 1.13,
                                                       2);
    v_cashflow_rec.vat_due_amount             := round(v_change_req_rec.et_fee * 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 := 0;
    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;
  
    --创建提前结清资金占用费现金流
    v_cashflow_rec := null;
    v_interest     := round(v_change_req_rec.fund_possession_cost * 0.13 / 1.13,
                            2);
    v_principal    := round(v_change_req_rec.fund_possession_cost / 1.13, 2);
    v_due_date     := v_change_req_rec.termination_date;
  
    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                      := v_times;
    v_cashflow_rec.calc_date                  := v_due_date;
    v_cashflow_rec.due_date                   := v_due_date;
    v_cashflow_rec.due_amount                 := v_change_req_rec.fund_possession_cost;
    v_cashflow_rec.net_due_amount             := round(v_change_req_rec.fund_possession_cost / 1.13,
                                                       2);
    v_cashflow_rec.vat_due_amount             := round(v_change_req_rec.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 := 0;
    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 calc_et_cashflows;

  --结清提交
  procedure submit_change_req_et(p_change_req_id number,
                                 p_layout_code   varchar2,
                                 p_user_id       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;
  
    e_change_req_status_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);
    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;
  
    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);
  end submit_change_req_et;
end cus_con_et_pkg;
/