create or replace package cus_con_change_req_pkg is

  -- Author  : Spencer 3893
  -- Created : 9/23/2019 4:25:37 PM
  -- Purpose : 合同變更申請

  --创建时维护默认的用印信息
  procedure insert_hls_document_use_seal(p_change_req_id       number,
                                         p_document_table      varchar2,
                                         p_instruments_types   varchar2,
                                         p_common_seal         varchar2,
                                         p_corporate_visa_seal varchar2,
                                         p_legal_seal          varchar2,
                                         p_con_special_seal    varchar2,
                                         p_financial_seal      varchar2,
                                         p_supervisio_seal     varchar2,
                                         p_seal_number         varchar2,
                                         p_user_id             number);
  procedure create_change_req(p_contract_id     number,
                              p_req_date        date,
                              p_req_type        varchar2,
                              p_description     varchar2,
                              p_user_id         number,
                              p_simulation_flag varchar2 default 'N',
                              p_ref_v01         number,
                              p_change_req_id   out number);
  --報價計算                             
  procedure calc_change_req(p_change_req_id         in number,
                            p_ccr_start_times       in number,
                            p_ccr_outstanding_times in number,
                            p_calc_session_id       in out number,
                            p_calc_successful       out varchar2,
                            p_warning_message       out varchar2,
                            p_user_id               in number);
  --承租人變更日計算應繳金額相關信息                           
  procedure calc_tenant_change_req(p_change_req_id       in number,
                                   p_ccr_lease_date      in date,
                                   p_ccr_lease_rental    out number, --应交租金
                                   p_ccr_lease_principal out number, --应交本金
                                   p_ccr_lease_interest  out number, --应交利息
                                   p_ccr_lease_penalty   out number, --应交罚息
                                   p_user_id             in number);
  --提交工作流                                
  procedure submit_change_req(p_change_req_id in number,
                              p_layout_code   in varchar2,
                              p_user_id       in number);
end cus_con_change_req_pkg;
/
create or replace package body cus_con_change_req_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_change_req_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_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;
  --创建时维护默认的用印信息
  procedure insert_hls_document_use_seal(p_change_req_id       number,
                                         p_document_table      varchar2,
                                         p_instruments_types   varchar2,
                                         p_common_seal         varchar2,
                                         p_corporate_visa_seal varchar2,
                                         p_legal_seal          varchar2,
                                         p_con_special_seal    varchar2,
                                         p_financial_seal      varchar2,
                                         p_supervisio_seal     varchar2,
                                         p_seal_number         varchar2,
                                         p_user_id             number) is
  begin
    insert into hls_document_use_seal
      (document_use_seal_id,
       document_table,
       document_id,
       instruments_types,
       common_seal,
       corporate_visa_seal,
       legal_seal,
       con_special_seal,
       financial_seal,
       supervisio_seal,
       seal_number,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date)
    values
      (hls_document_use_seal_s.nextval,
       p_document_table,
       p_change_req_id,
       p_instruments_types,
       p_common_seal,
       p_corporate_visa_seal,
       p_legal_seal,
       p_con_special_seal,
       p_financial_seal,
       p_supervisio_seal,
       p_seal_number,
       p_user_id,
       sysdate,
       p_user_id,
       sysdate);
  end insert_hls_document_use_seal;
  procedure create_change_req(p_contract_id     number,
                              p_req_date        date,
                              p_req_type        varchar2,
                              p_description     varchar2,
                              p_user_id         number,
                              p_simulation_flag varchar2 default 'N',
                              p_ref_v01         number,
                              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 in ('CUT_CHAG', 'LEASE_CHAG', 'ACC_CHAG') then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '只有起租状态的合同才能进行合同变更申请!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'cus_con_change_req_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;
    con_contract_history_pkg.create_change_req(p_contract_id          => p_contract_id,
                                               p_change_req_id        => v_change_req_id,
                                               p_req_date             => p_req_date,
                                               p_ref_v01              => p_ref_v01,
                                               p_description          => p_description,
                                               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_cashflow_change_flag => 'Y',
                                               p_user_id              => p_user_id);
    if p_req_type = 'LEASE_CHAG' then
      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_TW_NUMBER',
                                                          p_user_id             => p_user_id);
    end if;
    p_change_req_id := v_change_req_id;
  end create_change_req;

  --報價計算                             
  procedure calc_change_req(p_change_req_id         in number,
                            p_ccr_start_times       in number,
                            p_ccr_outstanding_times in number,
                            p_calc_session_id       in out number,
                            p_calc_successful       out varchar2,
                            p_warning_message       out varchar2,
                            p_user_id               in number) is
    r_change_req            con_contract_change_req%rowtype;
    r_contract_rec          con_contract%rowtype;
    v_ccr_outstanding_incld number;
    v_exists                number;
    v_count                 number;
  begin
    r_change_req   := get_con_change_req_rec(p_change_req_id, p_user_id);
    r_contract_rec := get_contract_rec(p_change_req_id, p_user_id);
  
    --根据开始期数计算未到期本金
    select nvl(sum(nvl(ccc.principal, 0) - nvl(ccc.received_principal, 0)),
               0) sum_ccr_outstanding_incld
      into v_ccr_outstanding_incld
      from con_contract_cashflow ccc
     where ccc.contract_id = r_change_req.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;
  
    --核销的不能进行更改,要从没核销的期数开始修改
    select count(1)
      into v_count
      from con_contract_cashflow ccc
     where ccc.contract_id = r_change_req.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            => 'cus_con_change_req_pkg',
                                                     p_procedure_function_name => 'calc_change_req');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    end if;
  
    if r_change_req.ccr_start_times is not null then
      begin
        select 1
          into v_exists
          from dual
         where exists (select 1
                  from con_contract_cashflow a
                 where contract_id = p_change_req_id
                   and cf_type = 1
                   and times >= r_change_req.ccr_start_times
                   and nvl(a.received_amount, 0) <> 0);
        sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CHANGE_REQ_CALC_ITFC_PKG.CCR_START_TIMES_ERROR',
                                                        p_created_by              => p_user_id,
                                                        p_package_name            => 'cus_con_change_req_pkg',
                                                        p_procedure_function_name => 'calc_change_req');
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
      exception
        when no_data_found then
          null;
      end;
    end if;
  
    select hls_fin_calculator_hd_s.nextval
      into r_contract_rec.calc_session_id
      from dual;
  
    hls_fin_calculator_ccr_pkg.create_calculate(p_change_req_id      => p_change_req_id,
                                                p_calc_session_id    => r_contract_rec.calc_session_id,
                                                p_recreate_h_formula => 'Y', --modify 2017年12月18日16:40:01
                                                p_recreate_l_std     => 'Y',
                                                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 calc_session_id     = r_contract_rec.calc_session_id,
           ccr_calc_session_id = r_contract_rec.calc_session_id,
           is_calc_flag        = 'Y'
     where change_req_id = p_change_req_id;
  
    update con_contract
       set calc_session_id = r_contract_rec.calc_session_id
     where contract_id = p_change_req_id;
  
    p_calc_session_id := r_contract_rec.calc_session_id;
  
  end calc_change_req;
  --承租人變更日計算應繳金額相關信息                           
  procedure calc_tenant_change_req(p_change_req_id       in number,
                                   p_ccr_lease_date      in date,
                                   p_ccr_lease_rental    out number, --应交租金
                                   p_ccr_lease_principal out number, --应交本金
                                   p_ccr_lease_interest  out number, --应交利息
                                   p_ccr_lease_penalty   out number, --应交罚息
                                   p_user_id             in number) is
    v_ccr_lease_rental    number; --应交租金
    v_ccr_lease_principal number; --应交本金
    v_ccr_lease_interest  number; --应交利息
    v_ccr_lease_penalty   number; --应交罚息
  begin
    select nvl(sum(f.due_amount), 0),
           nvl(sum(f.principal), 0),
           nvl(sum(f.interest), 0)
      into v_ccr_lease_rental, v_ccr_lease_principal, v_ccr_lease_interest
      from con_contract_cashflow f
     where f.due_date <= p_ccr_lease_date
       and f.write_off_flag = 'NOT'
       and f.cf_item = 1
       and f.cf_type = 1
       and f.cf_direction = 'INFLOW'
       and f.contract_id = p_change_req_id;
  
    select nvl(sum(f.due_amount), 0)
      into v_ccr_lease_penalty
      from con_contract_cashflow f
     where f.due_date <= p_ccr_lease_date
       and f.write_off_flag = 'NOT'
       and f.cf_item = 9
       and f.cf_type = 9
       and f.cf_direction = 'INFLOW'
       and f.contract_id = p_change_req_id;
  
    p_ccr_lease_rental    := v_ccr_lease_rental;
    p_ccr_lease_principal := v_ccr_lease_principal;
    p_ccr_lease_interest  := v_ccr_lease_interest;
    p_ccr_lease_penalty   := v_ccr_lease_penalty;
  
  exception
    when others then
      null;
  end calc_tenant_change_req;
  --提交工作流                                
  procedure submit_change_req(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;
  
    e_change_req_status_error exception;
    e_is_calc_flag_err        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;
    --租金計劃變更需要校驗是否計算報價
    if p_layout_code = 'CON_CHANGE_MODIFY' then
      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_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_change_req_pkg',
                                                      p_procedure_function_name => 'submit_change_req');
      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_change_req_pkg',
                                                      p_procedure_function_name => 'submit_change_req');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end submit_change_req;
end cus_con_change_req_pkg;
/