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_penalty                    out number, --���ڷ�Ϣ
                                  p_fund_possession_cost       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);

  --�����������д����ֽ���                                  
  procedure calc_repo_cashflow(p_contract_id     in number,
                               p_calc_session_id IN NUMBER,
                               p_user_id         IN NUMBER,
                               p_msg             OUT VARCHAR2);
  --һ���Իع������ֽ���  
  PROCEDURE calc_one_time_repo_cashflow(p_contract_id IN NUMBER,
                                        p_user_id     IN NUMBER);
                                        
  --�ع��������������׵ı�֤���Զ��ֿۺ��� 
  PROCEDURE calc_cashflow_division_tj(p_contract_id IN NUMBER,
                                        p_user_id     IN NUMBER);
  --�ܾ����ڵ�����ͨ��ʱ����Ϣ֪ͨ��Ʋ��������ࣩ������Ϲ沿�����Խݣ�
  PROCEDURE repo_wfl_approved_mail(p_change_req_id number,
                                   p_user_id       IN NUMBER);
  --��������ʱ�����͸��ύ��
  PROCEDURE repo_wfl_mail_to_submit(p_change_req_id number,
                                    p_user_id       IN NUMBER,
                                    p_wfl_flag_desc varchar2);
                                    
  --�����ͬ��Լ�ع���˰
  procedure deal_repur_cash_leaseback(p_contract_id number,
                                      p_interest out 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;
    v_calc_session_id       number;
    r_hls_fin_calculator_hd hls_fin_calculator_hd%rowtype;
    v_con_change_re_rec     con_contract_change_req%rowtype;
    --�¹ʵ�I-10185
    v_count number;
    begin
  
    v_contract_rec := get_contract_rec(p_contract_id, p_user_id);
  
    --��ʷ��ͬǨ�ƹ����ޱ��ۣ��ֶ�insert ͷ��
    v_calc_session_id := v_contract_rec.calc_session_id;
    if v_calc_session_id is null then
      r_hls_fin_calculator_hd                 := null;
      v_calc_session_id                       := hls_fin_calculator_hd_s.nextval;
      r_hls_fin_calculator_hd.calc_session_id := v_calc_session_id;
      r_hls_fin_calculator_hd.company_id      := v_contract_rec.company_id;
      r_hls_fin_calculator_hd.price_list      := v_contract_rec.price_list;
      insert into hls_fin_calculator_hd values r_hls_fin_calculator_hd;
      update con_contract c
         set c.calc_session_id = v_calc_session_id
       where c.contract_id = v_contract_rec.contract_id;
    end if;
    --�¹ʵ�I-10185 �����Լ�ع�������ڸ�����ֽ��������ܷ����Լ�ع�
   select count(1)
   into v_count
   from csh_payment_req_hd cph, csh_payment_req_ln ln
  where cph.payment_req_id = ln.payment_req_id
    and ln.ref_doc_id = p_contract_id
    and ln.ref_doc_line_id in
        (select t.cashflow_id
           from con_contract_cashflow t
          where t.contract_id = p_contract_id
            and t.cf_direction = 'OUTFLOW'
            and t.write_off_flag <> 'FULL'
            and t.cf_item = '0')
    and cph.approval_status in('NEW','APPROVING');
   
   if v_count>0 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;
  
    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);*/
  
    select *
      into v_con_change_re_rec
      from con_contract_change_req c
     where c.change_req_id = v_change_req_id;
    --���»ع���ŵ���������е��º�ͬ
    update con_contract c
       set c.repurchase_contract_number = v_con_change_re_rec.change_req_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);
    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_penalty                    out number, --���ڷ�Ϣ
                                  p_fund_possession_cost       out number, --�ʽ�ռ�÷�
                                  -- p_sum_received_penalty_amount out number, --����ΥԼ��
                                  p_user_id in number) is
    v_contract_rec       con_contract%rowtype;
    v_old_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_penalty number;
    --����ΥԼ��
    v_sum_received_penalty_amount number;
    v_due_amount                  number;
    v_overdue_max_days            number;
    v_count                       number := 0;
    v_et_date                     date;
    e_et_date_err exception;
    e_write_off_flagg_err exception;
    e_error exception;
    v_ccr_start_times         number;
    r_con_contract_change_req con_contract_change_req%ROWTYPE;
    v_fund_possession_rate    number;
    v_fund_possession_cost    number;
    v_fund_possession_time    number;
    e_defin_error exception;
    e_err_msg          varchar2(2000);
    v_max_penalty_date date;
    v_max_error_date   date;
    v_max_due_date     date;
  begin
    select *
      into r_con_contract_change_req
      from con_contract_change_req t
     where t.change_req_id = p_contract_id;
  
    --���ǰ��ͬ
    select *
      into v_old_contract_rec
      from con_contract t
     where t.contract_id = r_con_contract_change_req.contract_id;
    select count(1)
      into v_count
      from con_contract_cashflow ccc
     where ccc.contract_id = v_old_contract_rec.contract_id
       and decode(ccc.cf_item,
                  1,
                  ccc.due_date,
                  8,
                  ccc.due_date,
                  9,
                  (select due_date
                     from con_contract_cashflow
                    where contract_id = v_old_contract_rec.contract_id
                      and cf_item = 1
                      and times = ccc.times)) >= p_repurchase_date
       and ccc.cf_item in (1, 8, 9)
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE'
       and ((nvl(ccc.write_off_flag, 'NOT') <> 'NOT') OR
           (nvl(ccc.billing_status, 'NOT') <> 'NOT'));
    if v_count > 0 then
      raise e_error;
    end if;
    v_count := 0;
  
    begin
      select max(decode(ccc.cf_item,
                        1,
                        ccc.due_date,
                        8,
                        ccc.due_date,
                        9,
                        (select due_date
                           from con_contract_cashflow
                          where contract_id = v_old_contract_rec.contract_id
                            and cf_item = 1
                            and times = ccc.times)))
        into v_max_error_date
        from con_contract_cashflow ccc
       where ccc.contract_id = v_old_contract_rec.contract_id
         and ccc.cf_item in (1, 8, 9)
         and ccc.cf_direction = 'INFLOW'
         and ccc.cf_status = 'RELEASE'
         and nvl(ccc.write_off_flag, 'NOT') <> 'NOT';
    exception
      when no_data_found then
        v_max_error_date := null;
    end;
  
    if v_max_error_date is not null and
       v_max_error_date > p_repurchase_date then
      e_err_msg := '��Լ�ع����������ڻ�֮������,������,ΥԼ���Ѵ��ڻ���Ϳ�Ʊ��¼�����ȷ��壡';
      raise e_defin_error;
    end if;
    /*
    select max(a.due_date)
      into v_et_date
      from con_contract_cashflow a
     where a.cf_item = 1
       and a.contract_id = v_old_contract_rec.contract_id;
    if v_et_date < p_repurchase_date then
      raise e_et_date_err;
    end if;*/
    select max(a.times)
      into v_times
      from con_contract_cashflow a
     where decode(a.cf_item,
                  1,
                  a.due_date,
                  9,
                  (select due_date
                     from con_contract_cashflow
                    where contract_id = v_old_contract_rec.contract_id
                      and cf_item = 1
                      and times = a.times)) >= p_repurchase_date
       and a.cf_item in (1, 9)
       and a.write_off_flag in ('PARTIAL', 'FULL')
       and a.contract_id = v_old_contract_rec.contract_id;
  
    if v_times >= 1 then
      raise e_write_off_flagg_err;
    end if;
    /* v_count := 0;
    select count(1)
      into v_count
      from con_contract_cashflow ccc
     where ccc.contract_id = v_old_contract_rec.contract_id
       and decode(ccc.cf_item,
                  1,
                  ccc.due_date,
                  9,
                  (select due_date
                     from con_contract_cashflow
                    where contract_id = v_old_contract_rec.contract_id
                      and cf_item = 1
                      and times = ccc.times)) < trunc(p_repurchase_date)
       and ccc.cf_item in (1, 9)
       and ccc.cf_direction = 'INFLOW'
       and ccc.cf_status = 'RELEASE'
       and (nvl(ccc.write_off_flag, 'NOT') <> 'FULL')
       and decode(ccc.cf_item,
                  1,
                  ccc.due_date,
                  9,
                  (select due_date
                     from con_contract_cashflow
                    where contract_id = v_old_contract_rec.contract_id
                      and cf_item = 1
                      and times = ccc.times)) > trunc(sysdate);
    if v_count > 0 then
      e_err_msg := '��Լ�ع���֮ǰ�����,��������δ��ȫ������δ���ڵ����!';
      raise e_defin_error;
    end if;*/
  
    v_contract_rec := get_contract_rec(p_contract_id, p_user_id);
  
    select min(a.times)
      into v_ccr_start_times
      from con_contract_cashflow a
     where a.due_date >= p_repurchase_date
       and a.cf_item = 1
       and a.cf_direction != 'NONCASH'
       and a.contract_id = v_old_contract_rec.contract_id;
    if v_ccr_start_times is null then
      v_ccr_start_times := v_old_contract_rec.lease_times + 1;
    end if;
    --ǰ�����֧��Լ����
    --ȡ����ƻ���С�ڡ���Լ�ع��ա��ġ���������ġ����Ӧ���յ����ֵ
    begin
      select max(f.due_date)
        into v_last_rent_due_date
        from con_contract_cashflow f
       where f.due_date < p_repurchase_date
         and f.cf_item = 1
         and f.cf_type = 1
        -- and nvl(f.due_amount, 0) > 0
         and f.contract_id = v_old_contract_rec.contract_id;
    
    end;
    p_last_rent_due_date := v_last_rent_due_date;
  
    --�ѻ������ �Ѻ����������֮��+�׸���
    select sum(nvl(ccc.received_amount, 0)) +
           nvl(v_old_contract_rec.down_payment, 0)
      into v_sum_received_rent_amount
      from con_contract_cashflow ccc
     where ccc.contract_id = v_old_contract_rec.contract_id
       and ccc.cf_status = 'RELEASE'
       and ccc.cf_item = 1;
    --����ΥԼ��
    select nvl((select sum(nvl(ccc.received_amount, 0))
                 from con_contract_cashflow ccc
                where ccc.contract_id = v_old_contract_rec.contract_id
                  and ccc.cf_status = 'RELEASE'
                  and ccc.cf_item = 9),
               0)
      into v_sum_received_penalty_amount
      from dual;
  
    --ʣ�����
    --�����ʼ�ڣ����������һ�����Ӧ�ս��
    select sum(nvl(ccc.due_amount, 0))
      into v_sum_unreceived_rent_amount
      from con_contract_cashflow ccc
     where ccc.contract_id = v_old_contract_rec.contract_id
       and ccc.times >= v_ccr_start_times
       and ccc.cf_status = 'RELEASE'
       and ccc.cf_item = 1;
  
    --�ʽ�ռ������
    select trunc(p_repurchase_date) - trunc(v_last_rent_due_date) + 1
      into v_fund_possession_time
      from dual;
  
    --�ʽ�ռ�÷��� Ĭ�ϴ�����ͬ����
    v_fund_possession_rate := v_old_contract_rec.int_rate_display;
  
    --ʣ�౾����ǰ������ʼ�ڣ���ǰ�����������ڣ�������Ԥ��֧���գ���ʼ��Ϊ��һ�ڣ���һ�ڵ�ʣ�౾��  
    begin
    select
    --f.principal - nvl(f.received_principal, 0)
     f.outstanding_principal
      into v_sum_unreceived_principal
      from con_contract_cashflow f
     where f.due_date = v_last_rent_due_date
       and f.cf_item = 1
       and f.cf_type = 1
       and f.contract_id = v_old_contract_rec.contract_id
       and f.due_amount>0;
       exception
         --0����ֽ�������������
         when no_data_found then
       select t1.outstanding_principal
        into v_sum_unreceived_principal
        from  (    select
       f.cashflow_id,
     f.outstanding_principal+nvl(f.principal,0) outstanding_principal
     
      from con_contract_cashflow f
     where f.due_date > v_last_rent_due_date
       and f.cf_item = 1
       and f.cf_type = 1
       and f.contract_id = v_old_contract_rec.contract_id
       and f.due_amount>0
       order by f.times) t1 where rownum=1;
   end;
  
    --�ʽ�ռ�÷ѣ�ʣ�౾����ʽ�ռ�÷��ʣ�����������ռ���ʽ�����
    v_fund_possession_cost := v_sum_unreceived_principal *
                              v_fund_possession_time *
                              v_fund_possession_rate / 360;
  
    --������𣺽�Լ�ع���ʼ��֮ǰδ�����֮��
    select nvl(sum(f.due_amount), 0) - nvl(sum(f.received_amount), 0)
      into v_sum_overdue_amount
      from con_contract_cashflow f
     where f.due_date <= v_last_rent_due_date
       and f.cf_item = 1
       and f.cf_type = 1
       and f.contract_id = v_old_contract_rec.contract_id;
  
    /*  -- ���ڷ�Ϣ����Լ�ع���ʼ��֮ǰδ�շ�Ϣ֮��;
    select nvl(sum(f.due_amount), 0) - nvl(sum(f.received_amount), 0)
      into v_penalty
      from con_contract_cashflow f
     where f.times < v_ccr_start_times
       and f.cf_item = 9
       and f.contract_id = v_old_contract_rec.contract_id;*/
    v_penalty := 0;
    --��ѡ��Ϣ
    FOR c_contract_cashflow IN (SELECT a.*
                                  FROM con_contract_cashflow a,
                                       con_contract_cf_item  b
                                 WHERE a.contract_id =
                                       v_old_contract_rec.contract_id
                                   AND a.cf_status = 'RELEASE'
                                   AND a.due_date <= v_last_rent_due_date
                                      --ֻ�������IJż��㷣Ϣ��
                                      -- 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
    
      if nvl(c_contract_cashflow.penalty_process_status, 'N') = 'NORMAL' then
        v_penalty := v_penalty +
                     CON_OVERDUE_PENALTY_PKG.CALC_PENALTY_FOR_WRITE_OFF(p_cashflow_id  => c_contract_cashflow.cashflow_id,
                                                                        p_calc_date    => p_repurchase_date,
                                                                        p_user_id      => p_user_id,
                                                                        p_penalty_flag => 'N');
        -- if nvl(c_contract_cashflow.write_off_flag, 'NOT') <> 'FULL' then
      
        select v_penalty -
               nvl((SELECT sum(nvl(a.received_amount, 0))
                     FROM con_contract_cashflow a
                    WHERE a.contract_id = v_old_contract_rec.contract_id
                      AND a.cf_status = 'RELEASE'
                      AND a.cf_item = 9
                      AND a.times = c_contract_cashflow.times),
                   0)
          into v_penalty
          from dual;
        -- end if;
      end if;
    
      if nvl(c_contract_cashflow.penalty_process_status, 'N') <> 'NORMAL' and
         c_contract_cashflow.cf_item <> 8 then
        select v_penalty +
               nvl((SELECT sum(nvl(a.due_amount, 0) -
                              nvl(a.received_amount, 0))
                     FROM con_contract_cashflow a
                    WHERE a.contract_id = v_old_contract_rec.contract_id
                      AND a.cf_status = 'RELEASE'
                      AND a.cf_item = 9
                      AND a.times = c_contract_cashflow.times),
                   0)
          into v_penalty
          from dual;
      end if;
    
    end loop;
  
    p_sum_overdue_amount         := v_sum_overdue_amount;
    p_due_amount                 := v_penalty;
    p_sum_unreceived_principal   := v_sum_unreceived_principal;
    p_last_rent_due_date         := v_last_rent_due_date;
    p_sum_received_rent_amount   := v_sum_received_rent_amount;
    p_sum_unreceived_rent_amount := v_sum_unreceived_rent_amount;
    p_fund_possession_cost       := v_fund_possession_cost;
  
    update con_contract_change_req
       set repurchase_date    = p_repurchase_date,
           ccr_start_times    = v_ccr_start_times,
           last_update_date   = sysdate,
           last_rent_due_date = v_last_rent_due_date,
           last_updated_by    = p_user_id,
           ref_v07            = 'N'
     where change_req_id = p_contract_id;
    update con_contract ct
       set ct.offset_flag = 'N'
     where contract_id = p_contract_id;
  exception
  
    when e_defin_error then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => e_err_msg,
                                                     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 e_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_date_amount');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    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 e_et_date_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 => '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
      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            => '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);
  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;
    v_mail_list_id Number;
    v_time         Varchar2(1000);
    v_mail_to      Varchar2(1000);
    v_mail_cc      Varchar2(1000);
    v_subject      Varchar2(1000);
    v_body         Long;
    ---��Լ�ع��ύ������У��  18083 2022/11/15
    v_tax_type_rate    Number;
    v_count number;
    v_max_times number;
    e_tax_amount_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);*/
    select *
      into r_change_req_rec
      from con_contract_change_req t
     where t.change_req_id = p_change_req_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);*/
  
    select *
      into r_change_con_rec
      from con_contract t
     where t.contract_id = p_change_req_id;
  
    if r_change_req_rec.req_status not in ('NEW', 'REJECT') then
      raise e_change_req_status_error;
    end if;
    
    --��Լ�ع������ύǰУ���Լ�ع����һ�ڵ���˰���Ƿ����ֽ���˰��һ��  18083 2022/11/15
    if r_change_con_rec.business_type='LEASE' then
    v_tax_type_rate :=0.13;
    else
      v_tax_type_rate :=0.06;
    end if;
    select max(ccc.times)
       into v_max_times
      from con_contract_cashflow ccc
     where ccc.contract_id = p_change_req_id
       and ccc.cf_item = 250;
       
    select count(*)
      into v_count
      from con_contract_cashflow cc
     where cc.contract_id = p_change_req_id
       and cc.cf_item = 250
       and abs(cc.vat_due_amount -
               round(cc.due_amount / (1 + v_tax_type_rate) * v_tax_type_rate, 2)) > 1
      and cc.times=v_max_times;
      
      if v_count>0 then
        null;
        --raise e_tax_amount_error; dev������˰����㲻�ԣ���������ʽ�����д����в��죬��ʱע�ʹ˴�У��
      end if;
  
    /* if r_change_con_rec.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_con_rec.calc_session_id;
    
      if nvl(v_success_flag, 'N') <> 'Y' then
        raise e_calc_error;
      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);
    /*
    CON_CONTRACT_CHANGE_REQ_PKG.CHANGE_REQ_CONFIRM(p_change_req_id,
                                                   p_user_id => p_user_id);*/
    --�ع��ύ���̷����ʼ����������ϼ�
    SELECT replace(wm_concat(ee.email), ',', ';')
      into v_mail_to
      FROM con_contract       cc,
           con_contract_bp    ccb,
           hls_bp_master      hbm,
           exp_org_position   eop,
           exp_emp_assign_e_v eea,
           exp_employees      ee
     where cc.contract_id = ccb.contract_id
       and hbm.bp_code = ccb.bp_code
       and ccb.bp_category = 'AGENT'
       and eea.employee_id = ee.employee_id
       and eop.unit_id = hbm.unit_id
       and eea.position_id = eop.parent_position_id
       and cc.CONTRACT_id = r_change_req_rec.change_req_id;
    v_time    := to_char(Sysdate, 'yyyy"��"mm"��"dd"��"');
    v_subject := '�ع���������������֪ͨ';
    v_body    := '�𾴵��쵼�����ã�' || chr(13) || chr(10) || chr(13) || chr(10) ||
                 chr(32) || chr(32) || chr(32) || chr(32) || chr(32) ||
                 chr(32) || '�ع����빤����-�ع���ţ�' ||
                 r_change_req_rec.change_req_number || '��ͬ��ţ�' ||
                 r_change_con_rec.contract_number ||
                 '�Ѿ��������������ڵ㣬����ǰ������������
  http://10.203.0.125:8082/hlcm_leasing/login' || chr(13) ||
                 chr(10) || chr(13) || chr(10) || ' ʮ�ָ�л��';
    Select zj_sys_mailing_list_s.nextval Into v_mail_list_id From dual;
    ---�������ʼ���¼����
    zj_sys_mail_pkg.insert_mailing_list(p_mail_list_id   => v_mail_list_id,
                                        p_mail_to        => v_mail_to,
                                        p_mail_cc        => v_mail_cc,
                                        p_subject        => v_subject,
                                        p_body           => v_body,
                                        p_user_id        => p_user_id,
                                        p_mail_source    => Null,
                                        p_mail_source_id => Null,
                                        p_content_type   => 'text/plain');
  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_tax_amount_error THEN
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '�ع��ֽ���˰���ȷ�������¼��㣡',
                                                     p_created_by              => -1,
                                                     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);
      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;
  --�ع����봴���ֽ���
  procedure insert_rental_cashflow(p_calculator_ln_rec hls_fin_calculator_ln%rowtype,
                                   p_price_list        varchar2,
                                   p_contract_id       number,
                                   p_company_id        number,
                                   p_user_id           number,
                                   p_max_calc_time     number) is
    v_contract_cashflow_rec con_contract_cashflow%rowtype;
  
    v_cf_item                  hls_cashflow_item.cf_item%type;
    v_cf_type                  hls_cashflow_item.cf_type%type;
    v_cf_direction             hls_cashflow_item.cf_direction%type;
    v_cf_status                con_contract_cashflow.cf_status%type;
    v_con_contract_cashflow_id number;
    v_times                    number;
  begin
  
    --�ع�������⸳ֵ
    v_cf_item      := 250;
    v_cf_type      := 1;
    v_cf_direction := 'INFLOW';
    v_cf_status    := 'RELEASE';
    v_times        := p_calculator_ln_rec.times + p_max_calc_time;
  
    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                     := v_cf_item; --�������б����У������õ��ֽ�����Ŀ
    v_contract_cashflow_rec.cf_type                     := v_cf_type; -- �����ֽ�����Ŀȡ���ֽ�������
    v_contract_cashflow_rec.cf_direction                := v_cf_direction;
    v_contract_cashflow_rec.cf_status                   := v_cf_status;
    v_contract_cashflow_rec.times                       := v_times; --����
    v_contract_cashflow_rec.calc_date                   := to_date(p_calculator_ln_rec.calc_date,
                                                                   'yyyy-mm-dd'); --������
    v_contract_cashflow_rec.due_date                    := to_date(p_calculator_ln_rec.due_date,
                                                                   'yyyy-mm-dd'); --������
    v_contract_cashflow_rec.fin_income_date             := to_date(p_calculator_ln_rec.fin_income_date,
                                                                   'yyyy-mm-dd'); --���������
    v_contract_cashflow_rec.due_amount                  := p_calculator_ln_rec.rental; --���
    v_contract_cashflow_rec.principal                   := p_calculator_ln_rec.principal; --����
    v_contract_cashflow_rec.interest                    := p_calculator_ln_rec.interest; --��Ϣ
    v_contract_cashflow_rec.outstanding_rental          := p_calculator_ln_rec.outstanding_rental; --ʣ�����
    v_contract_cashflow_rec.outstanding_principal       := p_calculator_ln_rec.outstanding_principal; --ʣ�౾��
    v_contract_cashflow_rec.outstanding_interest        := p_calculator_ln_rec.outstanding_interest; --ʣ����Ϣ
    v_contract_cashflow_rec.interest_accrual_balance    := p_calculator_ln_rec.interest_accrual_balance; --��Ϣ���
    v_contract_cashflow_rec.principal_implicit_rate     := p_calculator_ln_rec.principal_implicit_rate; --ʵ�����ʷ�����
    v_contract_cashflow_rec.interest_implicit_rate      := p_calculator_ln_rec.interest_implicit_rate; --ʵ�����ʷ���Ϣ
    v_contract_cashflow_rec.accumulated_unpaid_interest := p_calculator_ln_rec.accumulated_unpaid_interest; --�ۼ�δ������Ϣ
    v_contract_cashflow_rec.interest_period_days        := p_calculator_ln_rec.interest_period_days; --��Ϣ��������Ϣ��������
    v_contract_cashflow_rec.discounting_days            := p_calculator_ln_rec.discounting_days; --��������
    v_contract_cashflow_rec.vat_due_amount              := p_calculator_ln_rec.vat_rental;
    v_contract_cashflow_rec.vat_principal               := p_calculator_ln_rec.vat_principal;
    v_contract_cashflow_rec.vat_interest                := p_calculator_ln_rec.vat_interest;
    v_contract_cashflow_rec.vat_principal_implicit      := p_calculator_ln_rec.vat_principal_implicit; --ʵ�����ʷ�������ֵ˰��
    v_contract_cashflow_rec.vat_interest_implicit       := p_calculator_ln_rec.vat_interest_implicit; --ʵ�����ʷ���Ϣ��ֵ˰��
    v_contract_cashflow_rec.net_due_amount              := p_calculator_ln_rec.net_rental;
    v_contract_cashflow_rec.net_principal               := p_calculator_ln_rec.net_principal;
    v_contract_cashflow_rec.net_interest                := p_calculator_ln_rec.net_interest;
    v_contract_cashflow_rec.net_principal_implicit      := p_calculator_ln_rec.net_principal_implicit; --ʵ�����ʷ�˰�󱾽�
    v_contract_cashflow_rec.net_interest_implicit       := p_calculator_ln_rec.net_interest_implicit; --ʵ�����ʷ�˰����Ϣ
    v_contract_cashflow_rec.fix_principal_flag          := p_calculator_ln_rec.fix_principal_flag; --�̶������־
    v_contract_cashflow_rec.fix_rental_flag             := p_calculator_ln_rec.fix_rental_flag; --�̶�����־
    v_contract_cashflow_rec.interest_only_flag          := p_calculator_ln_rec.interest_only_flag; --��֧����Ϣ
    v_contract_cashflow_rec.equal_flag                  := p_calculator_ln_rec.equal_flag;
    v_contract_cashflow_rec.manual_flag                 := p_calculator_ln_rec.manual_flag;
    v_contract_cashflow_rec.beginning_of_lease_year     := p_calculator_ln_rec.beginning_of_lease_year; --ÿ��������ȵĿ�ʼ
    v_contract_cashflow_rec.salestax                    := p_calculator_ln_rec.salestax; --Ӫҵ˰
    v_contract_cashflow_rec.generated_source            := 'CALCULATOR'; --�ֽ����ɼ���������
    v_contract_cashflow_rec.calc_line_id                := p_calculator_ln_rec.calc_line_id;
    v_contract_cashflow_rec.overdue_status              := 'N';
    v_contract_cashflow_rec.overdue_book_date           := null;
    v_contract_cashflow_rec.overdue_amount              := null;
    v_contract_cashflow_rec.overdue_principal           := null;
    v_contract_cashflow_rec.overdue_interest            := null;
    v_contract_cashflow_rec.overdue_remark              := null;
    v_contract_cashflow_rec.received_amount             := null;
    v_contract_cashflow_rec.received_principal          := null;
    v_contract_cashflow_rec.received_interest           := null;
    v_contract_cashflow_rec.write_off_flag              := 'NOT';
    v_contract_cashflow_rec.last_received_date          := null;
    v_contract_cashflow_rec.full_write_off_date         := null;
    v_contract_cashflow_rec.penalty_process_status      := 'NORMAL';
    v_contract_cashflow_rec.billing_status              := 'NOT';
    --�û��ֶ�����1
    if p_calculator_ln_rec.ln_user_col_d01 is not null then
      v_contract_cashflow_rec.ln_user_col_d01 := to_date(p_calculator_ln_rec.ln_user_col_d01,
                                                         'yyyy-mm-dd');
    else
      v_contract_cashflow_rec.ln_user_col_d01 := null;
    end if;
    if p_calculator_ln_rec.ln_user_col_d02 is not null then
      v_contract_cashflow_rec.ln_user_col_d02 := to_date(p_calculator_ln_rec.ln_user_col_d02,
                                                         'yyyy-mm-dd');
    else
      v_contract_cashflow_rec.ln_user_col_d02 := null;
    end if;
    if p_calculator_ln_rec.ln_user_col_d03 is not null then
      v_contract_cashflow_rec.ln_user_col_d03 := to_date(p_calculator_ln_rec.ln_user_col_d03,
                                                         'yyyy-mm-dd');
    else
      v_contract_cashflow_rec.ln_user_col_d03 := null;
    end if;
    if p_calculator_ln_rec.ln_user_col_d04 is not null then
      v_contract_cashflow_rec.ln_user_col_d04 := to_date(p_calculator_ln_rec.ln_user_col_d04,
                                                         'yyyy-mm-dd');
    else
      v_contract_cashflow_rec.ln_user_col_d04 := null;
    end if;
    if p_calculator_ln_rec.ln_user_col_d05 is not null then
      v_contract_cashflow_rec.ln_user_col_d05 := to_date(p_calculator_ln_rec.ln_user_col_d05,
                                                         'yyyy-mm-dd');
    else
      v_contract_cashflow_rec.ln_user_col_d05 := null;
    end if;
    v_contract_cashflow_rec.ln_user_col_v01               := p_calculator_ln_rec.ln_user_col_v01; --�û��ֶ��ַ�1
    v_contract_cashflow_rec.ln_user_col_v02               := p_calculator_ln_rec.ln_user_col_v02;
    v_contract_cashflow_rec.ln_user_col_v03               := 'REPUR';
    v_contract_cashflow_rec.ln_user_col_v04               := p_calculator_ln_rec.ln_user_col_v04;
    v_contract_cashflow_rec.ln_user_col_v05               := p_calculator_ln_rec.ln_user_col_v05;
    v_contract_cashflow_rec.ln_user_col_n01               := p_calculator_ln_rec.ln_user_col_n01; --�û��ֶ�����1
    v_contract_cashflow_rec.ln_user_col_n02               := p_calculator_ln_rec.ln_user_col_n02;
    v_contract_cashflow_rec.ln_user_col_n03               := p_calculator_ln_rec.ln_user_col_n03;
    v_contract_cashflow_rec.ln_user_col_n04               := p_calculator_ln_rec.ln_user_col_n04;
    v_contract_cashflow_rec.ln_user_col_n05               := p_calculator_ln_rec.ln_user_col_n05;
    v_contract_cashflow_rec.ln_user_col_n06               := p_calculator_ln_rec.ln_user_col_n06;
    v_contract_cashflow_rec.ln_user_col_n07               := p_calculator_ln_rec.ln_user_col_n07;
    v_contract_cashflow_rec.ln_user_col_n08               := p_calculator_ln_rec.ln_user_col_n08;
    v_contract_cashflow_rec.ln_user_col_n09               := p_calculator_ln_rec.ln_user_col_n09;
    v_contract_cashflow_rec.ln_user_col_n10               := p_calculator_ln_rec.ln_user_col_n10;
    v_contract_cashflow_rec.ln_user_col_n11               := p_calculator_ln_rec.ln_user_col_n11;
    v_contract_cashflow_rec.ln_user_col_n12               := p_calculator_ln_rec.ln_user_col_n12;
    v_contract_cashflow_rec.ln_user_col_n13               := p_calculator_ln_rec.ln_user_col_n13;
    v_contract_cashflow_rec.ln_user_col_n14               := p_calculator_ln_rec.ln_user_col_n14;
    v_contract_cashflow_rec.ln_user_col_n15               := p_calculator_ln_rec.ln_user_col_n15;
    v_contract_cashflow_rec.ln_user_col_n16               := p_calculator_ln_rec.ln_user_col_n16;
    v_contract_cashflow_rec.ln_user_col_n17               := p_calculator_ln_rec.ln_user_col_n17;
    v_contract_cashflow_rec.ln_user_col_n18               := p_calculator_ln_rec.ln_user_col_n18;
    v_contract_cashflow_rec.ln_user_col_n19               := p_calculator_ln_rec.ln_user_col_n19;
    v_contract_cashflow_rec.ln_user_col_n20               := p_calculator_ln_rec.ln_user_col_n20;
    v_contract_cashflow_rec.rental_eq_pymt_raw            := p_calculator_ln_rec.rental_eq_pymt_raw; --��ʼ��𣨵ȶ
    v_contract_cashflow_rec.rental_eq_pymt_adj            := p_calculator_ln_rec.rental_eq_pymt_adj; --������𣨵ȶ
    v_contract_cashflow_rec.interest_eq_pymt_raw          := p_calculator_ln_rec.interest_eq_pymt_raw; --��ʼ��Ϣ���ȶ
    v_contract_cashflow_rec.interest_eq_pymt_adj          := p_calculator_ln_rec.interest_eq_pymt_adj; --������Ϣ���ȶ
    v_contract_cashflow_rec.principal_eq_pymt_raw         := p_calculator_ln_rec.principal_eq_pymt_raw; --��ʼ���𣨵ȶ
    v_contract_cashflow_rec.principal_eq_pymt_adj         := p_calculator_ln_rec.principal_eq_pymt_adj; --�������𣨵ȶ
    v_contract_cashflow_rec.rental_eq_prin_raw            := p_calculator_ln_rec.rental_eq_prin_raw; --��ʼ��𣨵ȱ���
    v_contract_cashflow_rec.rental_eq_prin_adj            := p_calculator_ln_rec.rental_eq_prin_adj; --������𣨵ȱ���
    v_contract_cashflow_rec.interest_eq_prin_raw          := p_calculator_ln_rec.interest_eq_prin_raw; --��ʼ��Ϣ���ȱ���
    v_contract_cashflow_rec.interest_eq_prin_adj          := p_calculator_ln_rec.interest_eq_prin_adj; --������Ϣ���ȱ���
    v_contract_cashflow_rec.principal_eq_prin_raw         := p_calculator_ln_rec.principal_eq_prin_raw; --��ʼ���𣨵ȱ���
    v_contract_cashflow_rec.principal_eq_prin_adj         := p_calculator_ln_rec.principal_eq_prin_adj; --�������𣨵ȱ���
    v_contract_cashflow_rec.outstanding_rental_tax_incld  := p_calculator_ln_rec.outstanding_rental_tax_incld;
    v_contract_cashflow_rec.outstanding_prin_tax_incld    := p_calculator_ln_rec.outstanding_prin_tax_incld;
    v_contract_cashflow_rec.outstanding_int_tax_incld     := p_calculator_ln_rec.outstanding_int_tax_incld;
    v_contract_cashflow_rec.interest_accrual_bal_tax_incl := p_calculator_ln_rec.interest_accrual_bal_tax_incl;
    v_contract_cashflow_rec.accumulated_unpd_int_tax_incl := p_calculator_ln_rec.accumulated_unpd_int_tax_incl;
    v_contract_cashflow_rec.exchange_rate_type            := p_calculator_ln_rec.exchange_rate_type;
    v_contract_cashflow_rec.exchange_rate_quotation       := p_calculator_ln_rec.exchange_rate_quotation;
    v_contract_cashflow_rec.exchange_rate                 := p_calculator_ln_rec.exchange_rate;
    v_contract_cashflow_rec.due_amount_cny                := p_calculator_ln_rec.due_amount_cny;
    v_contract_cashflow_rec.main_business_income          := p_calculator_ln_rec.main_business_income;
    v_contract_cashflow_rec.main_business_cost            := p_calculator_ln_rec.main_business_cost;
    v_contract_cashflow_rec.financing_cost                := p_calculator_ln_rec.financing_cost;
  
    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; --ϵͳʱ��
  
    -- liukang
    v_contract_cashflow_rec.payment_deduction_flag   := 'Y';
    v_contract_cashflow_rec.payment_deduction_amount := v_contract_cashflow_rec.due_amount;
    --add by chenlingfeng 2019��4��9��
    v_contract_cashflow_rec.tax_type_id   := p_calculator_ln_rec.tax_type_id;
    v_contract_cashflow_rec.tax_type_rate := p_calculator_ln_rec.tax_type_rate;
    insert into con_contract_cashflow values v_contract_cashflow_rec;
  
  end;

  procedure save_contract_cashflow(p_contract_id           number,
                                   p_fin_calculator_hd_rec hls_fin_calculator_hd%rowtype,
                                   p_user_id               number,
                                   p_max_calc_time         number) is
  begin
    for c_hls_fin_calculator_ln in (select *
                                      from hls_fin_calculator_ln
                                     where calc_session_id =
                                           p_fin_calculator_hd_rec.calc_session_id
                                       and to_number(times) > 0
                                     order by to_number(times)) loop
    
      insert_rental_cashflow(p_calculator_ln_rec => c_hls_fin_calculator_ln,
                             p_price_list        => p_fin_calculator_hd_rec.price_list,
                             p_contract_id       => p_contract_id,
                             p_company_id        => p_fin_calculator_hd_rec.company_id,
                             p_user_id           => p_user_id,
                             p_max_calc_time     => p_max_calc_time);
    end loop;
    
  end;
  
  procedure restore_cashflow_from_con(p_change_req_id number,
                                      p_contract_id   number,
                                      p_user_id       number) is
     doc_pk_list hls_document_transfer_pkg.t_doc_pk_list;
    i           number;
    v_new_id    number;
    begin
          --CON_CONTRACT_CASHFLOW
    delete from con_contract_cashflow
     where contract_id = p_change_req_id;
    doc_pk_list.delete;
    delete from hls_document_history_ref r
     where r.document_history_id=p_change_req_id and r.table_name='CON_CONTRACT_CASHFLOW';
    i := 0;
    for c_cf in (select cashflow_id, calc_line_id, times
                   from con_contract_cashflow
                  where contract_id = p_contract_id
                  order by cashflow_id) loop
      i := i + 1;
      select con_contract_cashflow_s.nextval into v_new_id from dual;
    
      doc_pk_list(i).from_doc_pk := c_cf.cashflow_id;
      doc_pk_list(i).to_doc_pk := v_new_id;
    
      hls_document_history_pkg.create_document_history_ref(p_document_history_id => p_change_req_id,
                                                           p_table_name          => 'CON_CONTRACT_CASHFLOW',
                                                           p_from_pk_id          => c_cf.cashflow_id,
                                                           p_to_pk_id            => v_new_id,
                                                           p_user_id             => p_user_id);
    
      prj_cdd_item_pkg.prj_cdd_item_doc_ref_copy(p_from_document_table => 'CON_CONTRACT_CASHFLOW',
                                                 p_from_document_id    => c_cf.cashflow_id,
                                                 p_to_document_table   => 'CON_CONTRACT_CASHFLOW',
                                                 p_to_document_id      => v_new_id,
                                                 p_user_id             => p_user_id);
    
    end loop;
    hls_document_transfer_pkg.doc_to_doc(p_from_doc_table        => 'CON_CONTRACT_CASHFLOW',
                                         p_to_doc_table          => 'CON_CONTRACT_CASHFLOW',
                                         p_doc_pk_list           => doc_pk_list,
                                         p_copy_method           => 'DOC_TO_HISTORY',
                                         p_to_doc_column_1       => 'contract_id',
                                         p_to_doc_column_1_value => p_change_req_id,
                                         p_user_id               => p_user_id);

    --CON_UNEARNED_FINANCE_INCOME
    delete from con_unearned_finance_income
     where contract_id = p_change_req_id;
    doc_pk_list.delete;
    delete from hls_document_history_ref r
     where r.document_history_id = p_change_req_id
       and r.table_name = 'CON_UNEARNED_FINANCE_INCOME';
    i := 0;
    for c_cf in (select record_id
                   from con_unearned_finance_income
                  where contract_id = p_contract_id) loop
      i         := i + 1;
      select con_unearned_finance_income_s.nextval into v_new_id from dual;
    
      doc_pk_list(i).from_doc_pk := c_cf.record_id;
      doc_pk_list(i).to_doc_pk := v_new_id;
    
      hls_document_history_pkg.create_document_history_ref(p_document_history_id => p_change_req_id,
                                                           p_table_name          => 'CON_UNEARNED_FINANCE_INCOME',
                                                           p_from_pk_id          => c_cf.record_id,
                                                           p_to_pk_id            => v_new_id,
                                                           p_user_id             => p_user_id);
    
    
    end loop;
    hls_document_transfer_pkg.doc_to_doc(p_from_doc_table        => 'CON_UNEARNED_FINANCE_INCOME',
                                         p_to_doc_table          => 'CON_UNEARNED_FINANCE_INCOME',
                                         p_doc_pk_list           => doc_pk_list,
                                         p_copy_method           => 'DOC_TO_HISTORY',
                                         p_to_doc_column_1       => 'contract_id',
                                         p_to_doc_column_1_value => p_change_req_id,
                                         p_user_id               => p_user_id);
  
    update con_unearned_finance_income a
       set a.cashflow_id = (select r.from_pk_id
                              from hls_document_history_ref r
                             where r.document_history_id =
                                   p_change_req_id
                               and r.table_name = 'CON_CONTRACT_CASHFLOW'
                               and r.to_pk_id = a.cashflow_id)
     where a.contract_id = p_change_req_id
       and a.cashflow_id is not null;
   end;
  
  --�¹ʵ�I-10652 �����Լ�ع���Ϣ���������
  procedure deal_repur_cash_leaseback(p_contract_id   in number,
                            p_interest out number) is
    v_contract_rec             con_contract%rowtype;
    r_con_contract_change_req  con_contract_change_req%ROWTYPE;
    v_old_contract_rec         con_contract%rowtype;
    v_vat_principal            number;
    v_due_residual             number;
    v_overdue_interest  number;
    v_vat_sum_principal        number;
    v_vat_fund_possession_cost number;
    v_vat_service_charges      number;
    v_penalty                  number;
    v_tax_type_rate            number;
  begin
    select *
      into r_con_contract_change_req
      from con_contract_change_req t
     where t.change_req_id = p_contract_id;
  
    --���ǰ��ͬ
    select *
      into v_old_contract_rec
      from con_contract t
     where t.contract_id = r_con_contract_change_req.contract_id;
    --����к�ͬ
    select *
      into v_contract_rec
      from con_contract t
     where t.contract_id = p_contract_id;
   /* select ft.tax_type_rate
      into v_tax_type_rate
      from fnd_tax_type_codes ft
     where ft.tax_type_id = v_contract_rec.tax_type_id;*/
   --�в�������������ع����е���Ϣ֮��=������������������Ϣδ�ղ��֣���Ӫҵʵ�աݵ�����Ϣ��Ϊ0��+��������е���Ϣ����+������+���ɽ𣨻ع�����еģ�
   --�޲�������������ع����е���Ϣ֮��=��������е���Ϣ����+������+���ɽ𣨻ع�����еģ�
   --��Լ�ع����𣺽�Լ�ع����-��Լ�ع���Ϣ
    
    --����������
    --δ���� -> ȡinterest
    --���ֺ��� �ֶ���ʽ����
    for c_over_due_amount in (select *
                                from con_contract_cashflow f
                               where f.due_date <=
                                     r_con_contract_change_req.last_rent_due_date
                                 and f.cf_item = 1
                                 and f.cf_type = 1
                                 and f.contract_id =
                                     v_old_contract_rec.contract_id
                                 and f.write_off_flag <> 'FULL') loop
      if c_over_due_amount.write_off_flag = 'PARTIAL' then
        --����Ӫҵʵ�ա��ܡ�����Ӧ�ձ��𡱵��򡰵�����Ϣ��ȫ��Ҫ�㵽�ع�������
        if c_over_due_amount.received_amount<=c_over_due_amount.principal then
          v_overdue_interest :=nvl(v_overdue_interest, 0)+nvl(c_over_due_amount.interest,0);
          else
        --������Ӫҵʵ�ա���������Ӧ�ձ��𡱵��򡰵���Ӧ��-����Ӫҵʵ�ա��IJ���Ҫ���ڻع�������
        v_overdue_interest := nvl(v_overdue_interest, 0) +
                                    (c_over_due_amount.due_amount -
                                     nvl(c_over_due_amount.received_amount,
                                         0));
         end if;
       elsif c_over_due_amount.write_off_flag = 'NOT' then
        v_overdue_interest := nvl(v_overdue_interest, 0) +
                                    c_over_due_amount.interest;
      end if;
    end loop;
    --������
    begin
      select ccw.due_amount
        into v_due_residual
        from con_contract_cashflow ccw
       where ccw.contract_id = v_contract_rec.contract_id
         and cf_item = 8;
    exception
      when others then
        v_due_residual := 0;
    end;
    
    --���ɽ�
    v_penalty :=v_contract_rec.ccr_due_amount;
    p_interest :=nvl(v_overdue_interest,0)+nvl(v_due_residual,0)+nvl(v_penalty,0);
  end;
  
  procedure deal_repur_cash(p_contract_id   in number,
                            p_vat_principal out number) is
    v_contract_rec             con_contract%rowtype;
    r_con_contract_change_req  con_contract_change_req%ROWTYPE;
    v_old_contract_rec         con_contract%rowtype;
    v_vat_principal            number;
    v_vat_residual             number;
    v_overdue_vat_due_amount   number;
    v_vat_sum_principal        number;
    v_vat_fund_possession_cost number;
    v_vat_service_charges      number;
    v_penalty                  number;
    v_tax_type_rate            number;
  begin
    select *
      into r_con_contract_change_req
      from con_contract_change_req t
     where t.change_req_id = p_contract_id;
  
    --���ǰ��ͬ
    select *
      into v_old_contract_rec
      from con_contract t
     where t.contract_id = r_con_contract_change_req.contract_id;
    --����к�ͬ
    select *
      into v_contract_rec
      from con_contract t
     where t.contract_id = p_contract_id;
    select ft.tax_type_rate
      into v_tax_type_rate
      from fnd_tax_type_codes ft
     where ft.tax_type_id = v_contract_rec.tax_type_id;
    --vat_due_amount�ֶΣ��ع�����~���1��/�������+������+����������+���ڷ�Ϣ���+�ʽ�ռ�÷�+�ع�������
    --ע���������������ڷ�Ϣ�в��ֺ��������ʱ����δ������������ȡVat_due_amount��
    --�Բ��ֺ�����������ֻ���Լ����㣬�ȼ���vat�������net
  
    --�ع�����~���1��/�������
    begin
      select sum(ccw.vat_principal)
        into v_vat_principal
        from con_contract_cashflow ccw
       where ccw.contract_id = v_contract_rec.contract_id
         and ccw.times >= r_con_contract_change_req.ccr_start_times
         and ccw.times <= v_old_contract_rec.lease_times
         and cf_item = 1;
    exception
      when others then
        v_vat_principal := 0;
    end;
    --������
    begin
      select ccw.vat_due_amount
        into v_vat_residual
        from con_contract_cashflow ccw
       where ccw.contract_id = v_contract_rec.contract_id
         and cf_item = 8;
    exception
      when others then
        v_vat_residual := 0;
    end;
    --����������
    --δ���� -> ȡvat_due_amount
    --���ֺ��� �ֶ���ʽ����
    for c_over_due_amount in (select *
                                from con_contract_cashflow f
                               where f.due_date <=
                                     r_con_contract_change_req.last_rent_due_date
                                 and f.cf_item = 1
                                 and f.cf_type = 1
                                 and f.contract_id =
                                     v_old_contract_rec.contract_id
                                 and f.write_off_flag <> 'FULL') loop
      if c_over_due_amount.write_off_flag = 'PARTIAL' then
        v_overdue_vat_due_amount := nvl(v_overdue_vat_due_amount, 0) +
                                    (c_over_due_amount.due_amount -
                                     nvl(c_over_due_amount.received_amount,
                                         0)) / (1 + v_tax_type_rate) *
                                    v_tax_type_rate;
      elsif c_over_due_amount.write_off_flag = 'NOT' then
        v_overdue_vat_due_amount := nvl(v_overdue_vat_due_amount, 0) +
                                    c_over_due_amount.vat_due_amount;
      end if;
    end loop;
  
    --���ڷ�Ϣ���
    --�жϵ�����Ϣ�Ƿ�����ڷ�Ϣ�ֶ�һ��
    --δ���� -> ȡvat_due_amount
    --���ֺ��� �ֶ���ʽ����
    if nvl(v_contract_rec.ccr_due_amount, 0) =
       nvl(v_contract_rec.due_amount, 0) then
      for c_overdue_penalty in (select *
                                  from con_contract_cashflow f
                                 where f.times <
                                       r_con_contract_change_req.ccr_start_times
                                   and f.cf_item = 9
                                   and f.contract_id =
                                       v_old_contract_rec.contract_id
                                   and f.write_off_flag <> 'FULL') loop
        if c_overdue_penalty.write_off_flag = 'PARTIAL' then
          v_penalty := nvl(v_penalty, 0) +
                       (c_overdue_penalty.due_amount -
                        nvl(c_overdue_penalty.received_amount, 0)) /
                       (1 + v_tax_type_rate) * v_tax_type_rate;
        elsif c_overdue_penalty.write_off_flag = 'NOT' then
          v_penalty := nvl(v_penalty, 0) + c_overdue_penalty.due_amount/
                       (1 + v_tax_type_rate) * v_tax_type_rate;
        end if;
      end loop;
    else
      v_penalty := round(nvl(v_contract_rec.ccr_due_amount, 0) /
                         (1 + v_tax_type_rate) * v_tax_type_rate,
                         2);
    end if;
    v_vat_fund_possession_cost := round(nvl(v_contract_rec.fund_possession_cost,
                                            0) / (1 + v_tax_type_rate) *
                                        v_tax_type_rate,
                                        2);
    v_vat_service_charges      := round(nvl(v_contract_rec.repurchase_service_charges,
                                            0) / (1 + v_tax_type_rate) *
                                        v_tax_type_rate,
                                        2);
    p_vat_principal            := nvl(v_vat_principal, 0) +
                                  nvl(v_vat_residual, 0) +
                                  nvl(round(v_overdue_vat_due_amount, 2), 0) +
                                  nvl(round(v_penalty, 2), 0) +
                                  v_vat_fund_possession_cost +
                                  v_vat_service_charges;
  end;
  
  ---����ҵ��֤���Զ�����
  --������Ȳ�����ʱ��
  --�ֽ�������ֶ����и���change_req,ģ����ǰ����ɺ�������
  --��ʱ��normal���������ִ�и���normal�ֽ�������������
  procedure calc_deposit_auto_split(p_change_req_id number,
                                    p_user_id number) is
     r_con_contract_rec  con_contract%rowtype;  
     v_deposit_deduct number; 
     v_write_off_interest number;
     v_write_off_princpal number;
     v_write_off_flag     varchar2(30);                     
    begin
      select cc.*
       into r_con_contract_rec from con_contract cc where cc.contract_id=p_change_req_id;
       v_deposit_deduct:=nvl(r_con_contract_rec.deposit_deduct,0);
      if v_deposit_deduct>0 and  r_con_contract_rec.division='70' then
      delete from csh_write_off_interface c where c.contract_id= p_change_req_id and c.trx_interface_id is null;
       for cashflow_tj in (select * from con_contract_cashflow c 
                         where c.contract_id=p_change_req_id
                               and c.write_off_flag <>'FULL'
                               and c.cf_item in ('1','9')
                               and c.cf_status!='CANCEL'
                               order by c.times,decode(c.cf_item,'9',1,2)) LOOP
          v_write_off_interest:=null;
          v_write_off_princpal:=null;
            --�ų��պõ���0������������Ϊ0����
          if v_deposit_deduct=0 then
            exit;
          end if;
          if (cashflow_tj.due_amount-nvl(cashflow_tj.received_amount,0))>v_deposit_deduct then
                v_write_off_flag:='PARTIAL';
                
               if cashflow_tj.cf_item=1 then
                    if (cashflow_tj.principal-nvl(cashflow_tj.received_principal,0))>=v_deposit_deduct then
                      v_write_off_interest:=0;
                      v_write_off_princpal:=v_deposit_deduct; 
                      else
                      v_write_off_interest:=v_deposit_deduct-(cashflow_tj.principal-nvl(cashflow_tj.received_principal,0));
                      v_write_off_princpal:=cashflow_tj.principal-nvl(cashflow_tj.received_principal,0);  
                    end if;
                end if; 
          
               update con_contract_cashflow c
                  set c.received_amount    = v_deposit_deduct +
                                             nvl(cashflow_tj.received_amount,
                                                 0),
                      c.received_principal = nvl(cashflow_tj.received_principal,
                                                 0) +
                                             nvl(v_write_off_princpal, 0),
                      c.received_interest = nvl(cashflow_tj.received_interest,
                                                 0) +
                                             nvl(v_write_off_interest, 0),
                      c.write_off_flag=v_write_off_flag                       
                where c.cashflow_id = cashflow_tj.cashflow_id;
               
               insert into csh_write_off_interface(write_off_interface_id,
                                                   record_type,
                                                   batch_id,
                                                   trx_interface_id,
                                                   trx_number,
                                                   csh_bank_slip_num,
                                                   company_id,
                                                   contract_number,
                                                   contract_id,
                                                   write_off_cashflow_id,
                                                   write_off_type,
                                                   write_off_date,
                                                   write_off_period_name,
                                                   write_off_period_num,
                                                   write_off_times,
                                                   write_off_cf_item,
                                                   write_off_amount,
                                                   write_off_principal,
                                                   write_off_interest,
                                                   write_off_description,
                                                   write_off_bp_id,
                                                   check_flag,
                                                   post_flag,
                                                   write_off_id,
                                                   error_message,
                                                   creation_date,
                                                   created_by,
                                                   last_update_date,
                                                   last_updated_by,
                                                   write_off_classification,
                                                   agent_id,
                                                   assigns_contract_id,
                                                   journal_date,
                                                   conds_account_num,
                                                   conds_account_name) values
                                                    (csh_write_off_interface_s.nextval, 'MANUAL', 
                                                    null, null, null, null, 1, r_con_contract_rec.contract_number, 
                                                    cashflow_tj.contract_id, cashflow_tj.cashflow_id, 'DEPOSIT_CREDIT',
                                                      sysdate,  --��������
                                                    to_char(sysdate,'yyyy-mm'), --�����������ڼ�
                                                    to_number(to_char(sysdate,'yyyymm')),   --�����������·�
                                                    cashflow_tj.times, cashflow_tj.cf_item,
                                                    v_deposit_deduct, --�������
                                                    v_write_off_princpal,  --��������
                                                    v_write_off_interest,            --������Ϣ
                                                    null, 
                                                    r_con_contract_rec.bp_id_tenant,
                                                    'Y', null, null, null, sysdate, 1, sysdate, 1,
                                                        null, null, cashflow_tj.contract_id, sysdate,
                                                         null, null);
               exit;
          else 
             v_write_off_flag:='FULL';
                
               if cashflow_tj.cf_item=1 then
                      v_write_off_interest:=(cashflow_tj.interest-nvl(cashflow_tj.received_interest,0));
                      v_write_off_princpal:=(cashflow_tj.principal-nvl(cashflow_tj.received_principal,0)); 
                end if; 
          
               update con_contract_cashflow c
                  set c.received_amount    = c.due_amount,
                      c.received_principal = c.principal,
                      c.received_interest =  c.interest,
                      c.write_off_flag=v_write_off_flag                       
                where c.cashflow_id = cashflow_tj.cashflow_id;
             insert into csh_write_off_interface(write_off_interface_id,
                                                 record_type,
                                                 batch_id,
                                                 trx_interface_id,
                                                 trx_number,
                                                 csh_bank_slip_num,
                                                 company_id,
                                                 contract_number,
                                                 contract_id,
                                                 write_off_cashflow_id,
                                                 write_off_type,
                                                 write_off_date,
                                                 write_off_period_name,
                                                 write_off_period_num,
                                                 write_off_times,
                                                 write_off_cf_item,
                                                 write_off_amount,
                                                 write_off_principal,
                                                 write_off_interest,
                                                 write_off_description,
                                                 write_off_bp_id,
                                                 check_flag,
                                                 post_flag,
                                                 write_off_id,
                                                 error_message,
                                                 creation_date,
                                                 created_by,
                                                 last_update_date,
                                                 last_updated_by,
                                                 write_off_classification,
                                                 agent_id,
                                                 assigns_contract_id,
                                                 journal_date,
                                                 conds_account_num,
                                                 conds_account_name) values (csh_write_off_interface_s.nextval,
                                                  'MANUAL', null, null, null, null, 1,
                                                   r_con_contract_rec.contract_number, cashflow_tj.contract_id, 
                                                   cashflow_tj.cashflow_id, 'DEPOSIT_CREDIT', 
                                                    sysdate,  --��������
                                                    to_char(sysdate,'yyyy-mm'), --�����������ڼ�
                                                    to_number(to_char(sysdate,'yyyymm')),   --�����������·�
                                                    cashflow_tj.times, cashflow_tj.cf_item, 
                                                    cashflow_tj.due_amount, --�������
                                                    v_write_off_princpal,  --��������
                                                    v_write_off_interest,            --������Ϣ
                                                       null, 
                                                    r_con_contract_rec.bp_id_tenant, 'Y', null, null, null,
                                                     sysdate, 1, sysdate, 1, null, null, 
                                                     cashflow_tj.contract_id, sysdate, null, null);                                  
             v_deposit_deduct:=v_deposit_deduct-cashflow_tj.due_amount+nvl(cashflow_tj.received_amount,0);
             
          end if;
                               
        END LOOP;
    end if;
    end;

  procedure calc_repo_cashflow(p_contract_id     in number,
                               p_calc_session_id IN NUMBER,
                               p_user_id         IN NUMBER,
                               p_msg             OUT VARCHAR2) is
    r_con_contract_rec        con_contract%rowtype;
    v_max_calc_time           NUMBER;
    r_con_contract_change_req con_contract_change_req%ROWTYPE;
    r_calculator_hd_rec       hls_fin_calculator_hd%rowtype;
    v_new_calc_session_id     number;
    v_min_overdue_times       number;
    v_start_change_times      number;
    v_vat_principal           number;
    v_deposit_deduct          number;
    v_division    varchar2(30);
    
      --�¹ʵ�I-10652
    v_cashflow_rec            con_contract_cashflow%ROWTYPE;
    v_interest              NUMBER; --��Ϣ
    v_principal             NUMBER; --����
    v_outstanding_principal NUMBER; --ʣ�౾��
    v_due_date              DATE;
    v_interest_back  number; --�����ͬ��Ϣ
  begin
  
    --step 1 ���������
    aux_single_variable_calc_pkg.calc_main(p_calc_session_id => p_calc_session_id,
                                           p_user_id         => p_user_id,
                                           p_msg             => p_msg);
    /* 
    --���Ƽ�����4�ű�������
    hls_fin_calculator_itfc_pkg.copy_calculator(p_from_calc_session_id => p_calc_session_id,
                                                p_to_calc_session_id   => v_new_calc_session_id,
                                                p_user_id              => p_user_id);*/
    select *
      into r_con_contract_rec
      from con_contract t
     where t.contract_id = p_contract_id;
    SELECT *
      INTO r_con_contract_change_req
      FROM con_contract_change_req t
     WHERE t.change_req_id = p_contract_id;
  
    /*    select *
     into r_calculator_hd_rec
     from hls_fin_calculator_hd h
    where h.source_doc_category = 'CHANGE_CONTRACT'
      and h.source_doc_id = r_con_contract_change_req.contract_id;*/
    select *
      into r_calculator_hd_rec
      from hls_fin_calculator_hd h
     where h.calc_session_id = p_calc_session_id;
  
    --ɾ��ǰһ�α����������Լ�ع������߼�
    delete from con_contract_cashflow t
     where t.contract_id = p_contract_id
       and t.cf_item in (250);
     
     v_deposit_deduct:=nvl(r_con_contract_rec.deposit_deduct,0);
     v_division:=nvl(r_con_contract_rec.division,0);
    --��ԭǰһ�α�����㶳����߼�
    /*update con_contract_cashflow
       set cf_direction = 'INFLOW', cf_status = 'RELEASE'
     where contract_id = p_contract_id
       and cf_direction = 'NONCASH'
       and cf_status = 'BLOCK'
       and ln_user_col_v03 = 'REPUR';*/
    restore_cashflow_from_con(p_change_req_id =>p_contract_id,
                              p_contract_id =>r_con_contract_change_req.contract_id,
                              p_user_id     =>p_user_id);

   if v_deposit_deduct=0 then
    --����ķ�Ϣ�ֽ�������
     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 <=
                                       r_con_contract_change_req.last_rent_due_date
                                      --ֻ�������IJż��㷣Ϣ��
                                   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
      CON_OVERDUE_PENALTY_PKG.overdue_dayend(p_contract_cashflow_rec => c_contract_cashflow,
                                             p_contract_rec          => r_con_contract_rec,
                                             p_calc_date             => r_con_contract_change_req.repurchase_date,
                                             p_user_id               => p_user_id);
    
     END LOOP;
    end if;
    
    --����ҵ��֤���Զ�����
    calc_deposit_auto_split(p_change_req_id =>p_contract_id,p_user_id=>p_user_id);
    
   
    --step 3 ƴ�ӱ����ʼ��֮ǰ���ֽ����ͱ����ʼ��֮����ֽ��� ����µ�cashflow
    --��ȡ�������֮ǰ�����һ��  ��ǰ��֧��Ԥ������������
    select cw.times
      into v_max_calc_time
      from con_contract_cashflow cw
     where cw.contract_id = r_con_contract_change_req.contract_id
       and cw.due_date = r_con_contract_rec.last_rent_due_date
       and cw.cf_item = 1;
    --��ȡ������Сһ������ ����
    select min(cw.times)
      into v_min_overdue_times
      from con_contract_cashflow cw
     where cw.contract_id = r_con_contract_change_req.contract_id
       and cw.times <= v_max_calc_time
       and cw.write_off_flag <> 'FULL'
       and cw.cf_item in (1, 8)
       and nvl(cw.due_amount, 0) <> 0;
    if v_min_overdue_times is null then
      v_min_overdue_times := r_con_contract_change_req.ccr_start_times;
    end if;
    select max(cw.times)
      into v_start_change_times
      from con_contract_cashflow cw
     where cw.contract_id = r_con_contract_change_req.contract_id
       and cw.times <= v_max_calc_time
       and cw.write_off_flag <> 'FULL'
       and cw.cf_item in (1, 8)
       and nvl(cw.due_amount, 0) <> 0;
  
    --��������������ֽ���
    update con_contract_cashflow caw
       set caw.cf_status       = 'BLOCK',
           caw.cf_direction    = 'NONCASH',
           caw.ln_user_col_v03 = 'REPUR'
     where caw.contract_id = r_con_contract_rec.contract_id
       and ((caw.times >= v_min_overdue_times) or
           nvl(caw.due_amount, 0) = 0);
  
    --ƴ�ӱ����ı���
    save_contract_cashflow(p_contract_id           => p_contract_id,
                           p_fin_calculator_hd_rec => r_calculator_hd_rec,
                           p_user_id               => p_user_id,
                           p_max_calc_time         => r_con_contract_change_req.ccr_start_times - 1);
  
    --���±��α��ۼ���ΪY
  
    update con_contract ct
       set ct.offset_flag = 'Y'
     where ct.contract_id = p_contract_id;
     
    --���ڻع����һ�ڲ�˰����ֱ��ͻ���
    --if r_con_contract_rec.business_type='LEASE' then
    --�������һ�ڽ�Լ�ع�������Ϣ��˰
    deal_repur_cash(p_contract_id   => p_contract_id,
                    p_vat_principal => v_vat_principal);
    hls_sys_log_pkg.log('���ڻع���ͬid' || p_contract_id || '˰��' ||v_vat_principal||'����'||r_con_contract_change_req.ccr_start_times||'��������'||r_calculator_hd_rec.lease_times);
  
    --���ڻع�����
    select v_vat_principal - sum(vat_principal)
      into v_vat_principal
      from con_contract_cashflow
     where contract_id = p_contract_id
       and cf_item = 250
       and times > 0
       and times < r_con_contract_change_req.ccr_start_times +
           to_number(r_calculator_hd_rec.lease_times) - 1;
  
    update con_contract_cashflow caw
       set caw.vat_principal  = v_vat_principal,
           caw.net_principal  = caw.principal - v_vat_principal,
           caw.vat_due_amount = v_vat_principal + caw.vat_interest,
           caw.net_due_amount = caw.due_amount - caw.vat_due_amount
     where caw.contract_id = r_con_contract_rec.contract_id
       and caw.cf_item = 250
       and caw.times = r_con_contract_change_req.ccr_start_times +
           to_number(r_calculator_hd_rec.lease_times) - 1;
           
    /* elsif r_con_contract_rec.business_type='LEASEBACK' THEN
     deal_repur_cash_leaseback(p_contract_id  =>p_contract_id,
                               p_interest =>v_interest_back);
     hls_sys_log_pkg.log('��ͬid' || p_contract_id || '˰��' ||v_interest_back);
     update con_contract_cashflow caw
       set caw.interest=v_interest_back,
           caw.vat_interest=round(v_interest_back*0.06/1.06,2),
           caw.net_interest=v_interest_back-round(v_interest_back*0.06/1.06,2),
           caw.principal=caw.due_amount-v_interest_back,
           caw.vat_principal  = 0,
           caw.net_principal  = caw.due_amount-v_interest_back,
           caw.vat_due_amount = round(caw.due_amount*0.06/1.06,2),
           caw.net_due_amount = caw.due_amount - round(caw.due_amount*0.06/1.06,2)
     where caw.contract_id = r_con_contract_rec.contract_id
       and cf_item = 250
       and caw.times = r_con_contract_change_req.ccr_start_times +
           to_number(r_calculator_hd_rec.lease_times) - 1;
     end if;*/
      
     ----�¹ʵ�I-10652  �����ͬ������һ�ڿ�Ʊ�ֽ���     
     if r_con_contract_rec.business_type='LEASEBACK' then
          ---����һ���ۺ������ڻع�����
    v_cashflow_rec          := NULL;
    v_interest              := 0;
    v_principal             := round(r_con_contract_rec.repurchase_tatal_amount,
                                     2);
    v_outstanding_principal := 0;
    v_due_date              := r_con_contract_rec.paymengt_deadlinedate;
  
    v_cashflow_rec.cashflow_id                := con_contract_cashflow_s.nextval;
    v_cashflow_rec.contract_id                := r_con_contract_rec.contract_id;
    v_cashflow_rec.cf_item                    := 251;
    v_cashflow_rec.cf_type                    := 1;
    v_cashflow_rec.cf_direction               := 'INFLOW';
    v_cashflow_rec.cf_status                  := 'RELEASE';
    v_cashflow_rec.times                      := r_con_contract_change_req.ccr_start_times;
    v_cashflow_rec.calc_date                  := v_due_date;
    v_cashflow_rec.due_date                   := v_due_date;
    v_cashflow_rec.due_amount                 := r_con_contract_rec.repurchase_tatal_amount;
    v_cashflow_rec.vat_due_amount             := round(r_con_contract_rec.repurchase_tatal_amount * 0.06 / 1.06,
                                                       2);
    v_cashflow_rec.net_due_amount             := v_cashflow_rec.due_amount -
                                                 v_cashflow_rec.vat_due_amount;
    v_cashflow_rec.principal                  := r_con_contract_rec.repurchase_tatal_amount;
    v_cashflow_rec.vat_principal              := round(v_principal * 0.06 / 1.06,
                                                       2);
    v_cashflow_rec.net_principal              := v_cashflow_rec.principal -
                                                 v_cashflow_rec.vat_principal;
    v_cashflow_rec.interest                   := 0;
    v_cashflow_rec.net_interest               := 0;
    v_cashflow_rec.vat_interest               := 0;
    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             := 'N';
    v_cashflow_rec.penalty_process_status     := 'NORMAL';
    v_cashflow_rec.billing_status             := 'NOT';
    v_cashflow_rec.ln_user_col_v03            := 'REPUR';
    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;
    ---�����ͬ��˰
    cus_con_contract_repo_pkg.deal_repur_cash_leaseback(p_contract_id  =>p_contract_id,
                               p_interest =>v_interest_back);
      
     update con_contract_cashflow caw
       set caw.interest=v_interest_back,
           caw.vat_interest=round(v_interest_back*0.06/1.06,2),
           caw.net_interest=v_interest_back-round(v_interest_back*0.06/1.06,2),
           caw.principal=caw.due_amount-v_interest_back,
           caw.vat_principal  = 0,
           caw.net_principal  = caw.due_amount-v_interest_back,
           caw.vat_due_amount = round(caw.due_amount*0.06/1.06,2),
           caw.net_due_amount = caw.due_amount - round(caw.due_amount*0.06/1.06,2),
           --�����ͬ����һ�����ֽ�����������Ʊ����������;���˴�������տ���
           caw.received_interest=v_interest_back,
           caw.received_principal=caw.due_amount-v_interest_back
     where caw.contract_id = r_con_contract_rec.contract_id
       and cf_item = 251;
       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            => 'cus_con_contract_repo_pkg',
                                                     p_procedure_function_name => 'calc_repo_cashflow');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
  end calc_repo_cashflow;

  PROCEDURE calc_one_time_repo_cashflow(p_contract_id IN NUMBER,
                                        p_user_id     IN NUMBER) IS
    r_con_contract_rec        con_contract%ROWTYPE;
    v_cashflow_rec            con_contract_cashflow%ROWTYPE;
    r_con_contract_change_req con_contract_change_req%ROWTYPE;
    v_max_calc_time           NUMBER;
    v_min_overdue_times       number;
    v_start_change_times      number;
  
    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;
    v_vat_principal number;
    v_deposit_deduct number;
    v_division varchar2(10);
    --�¹ʵ�I-10652 
    v_interest_back  number; --�����ͬ��Ϣ
    --�¹ʵ�I-11074 
    e_last_rent_due_amount_error exception;
    
  BEGIN
    --�ع���ͬ
    select *
      into r_con_contract_rec
      from con_contract t
     where t.contract_id = p_contract_id;
    SELECT *
      INTO r_con_contract_change_req
      FROM con_contract_change_req t
     WHERE t.change_req_id = p_contract_id;
     
     --�¹ʵ�I-11074 �޸Ļع���ֱ���˻ص��µ�ǰ�����֧���ղ�һ�����⴦��
     if r_con_contract_rec.last_rent_due_date<>r_con_contract_change_req.last_rent_due_date then
       raise e_last_rent_due_amount_error;
     end if;
     restore_cashflow_from_con(p_change_req_id =>p_contract_id,
                              p_contract_id =>r_con_contract_change_req.contract_id,
                              p_user_id     =>p_user_id);
    /*--ɾ��ǰһ�α����������Լ�ع������߼�
    delete from con_contract_cashflow t
     where t.contract_id = p_contract_id
       and t.cf_item in (250);
  
    --��ԭǰһ�α�����㶳����߼�
    update con_contract_cashflow
       set cf_direction = 'INFLOW', cf_status = 'RELEASE'
     where contract_id = p_contract_id
       and cf_direction = 'NONCASH'
       and cf_status = 'BLOCK'
       and ln_user_col_v03 = 'REPUR';
  
    --ɾ����Լ�ع���֮��ķ�Ϣ
    delete from con_contract_cashflow t
     where t.contract_id = p_contract_id
       and t.cf_item = 9
       and t.times > r_con_contract_change_req.ccr_start_times
       and t.write_off_flag = 'NOT';*/
     --�ع�У��
     con_change_single_calc_pkg.check_deposit_deduct(p_contract_id,p_user_id);
    --����ķ�Ϣ�ֽ�������
    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 <=
                                       r_con_contract_change_req.last_rent_due_date
                                      --ֻ�������IJż��㷣Ϣ��
                                   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
    
      CON_OVERDUE_PENALTY_PKG.overdue_dayend(p_contract_cashflow_rec => c_contract_cashflow,
                                             p_contract_rec          => r_con_contract_rec,
                                             p_calc_date             => r_con_contract_change_req.repurchase_date,
                                             p_user_id               => p_user_id);
    
    END LOOP;
    
    --����ҵ��֤���Զ�����
    calc_deposit_auto_split(p_change_req_id =>p_contract_id,p_user_id=>p_user_id);
    
    --step 3 ƴ�ӱ����ʼ��֮ǰ���ֽ����ͱ����ʼ��֮����ֽ��� ����µ�cashflow
    --��ȡ�������֮ǰ�����һ��  ��ǰ��֧��Ԥ������������
    select cw.times
      into v_max_calc_time
      from con_contract_cashflow cw
     where cw.contract_id = r_con_contract_change_req.contract_id
       and cw.due_date = r_con_contract_rec.last_rent_due_date
       and cw.cf_item = 1;
    --��ȡ������Сһ������ ����
    select min(cw.times)
      into v_min_overdue_times
      from con_contract_cashflow cw
     where cw.contract_id = r_con_contract_change_req.contract_id
       and cw.times <= v_max_calc_time
       and cw.write_off_flag <> 'FULL'
       and cw.cf_item in (1, 8)
       and nvl(cw.due_amount, 0) <> 0;
    if v_min_overdue_times is null then
      v_min_overdue_times := r_con_contract_change_req.ccr_start_times;
    end if;
    select max(cw.times)
      into v_start_change_times
      from con_contract_cashflow cw
     where cw.contract_id = r_con_contract_change_req.contract_id
       and cw.times <= v_max_calc_time
       and cw.write_off_flag <> 'FULL'
       and cw.cf_item in (1, 8)
       and nvl(cw.due_amount, 0) <> 0;
    --��������������ֽ���
    update con_contract_cashflow caw
       set caw.cf_status       = 'BLOCK',
           caw.cf_direction    = 'NONCASH',
           caw.ln_user_col_v03 = 'REPUR'
     where caw.contract_id = r_con_contract_rec.contract_id
       and ((caw.times >= v_min_overdue_times) or
           nvl(caw.due_amount, 0) = 0);
    -- ����һ��һ���Իع�����       
    v_cashflow_rec          := NULL;
    v_interest              := 0;
    v_principal             := round(r_con_contract_rec.repurchase_tatal_amount,
                                     2);
    v_outstanding_principal := 0;
    v_due_date              := r_con_contract_rec.paymengt_deadlinedate;
  
    v_cashflow_rec.cashflow_id                := con_contract_cashflow_s.nextval;
    v_cashflow_rec.contract_id                := r_con_contract_rec.contract_id;
    v_cashflow_rec.cf_item                    := 250;
    v_cashflow_rec.cf_type                    := 1;
    v_cashflow_rec.cf_direction               := 'INFLOW';
    v_cashflow_rec.cf_status                  := 'RELEASE';
    v_cashflow_rec.times                      := r_con_contract_change_req.ccr_start_times;
    v_cashflow_rec.calc_date                  := v_due_date;
    v_cashflow_rec.due_date                   := v_due_date;
    v_cashflow_rec.due_amount                 := r_con_contract_rec.repurchase_tatal_amount;
    v_cashflow_rec.vat_due_amount             := round(r_con_contract_rec.repurchase_tatal_amount * 0.13 / 1.13,
                                                       2);
    v_cashflow_rec.net_due_amount             := v_cashflow_rec.due_amount -
                                                 v_cashflow_rec.vat_due_amount;
    v_cashflow_rec.principal                  := r_con_contract_rec.repurchase_tatal_amount;
    v_cashflow_rec.vat_principal              := round(v_principal * 0.13 / 1.13,
                                                       2);
    v_cashflow_rec.net_principal              := v_cashflow_rec.principal -
                                                 v_cashflow_rec.vat_principal;
    v_cashflow_rec.interest                   := 0;
    v_cashflow_rec.net_interest               := 0;
    v_cashflow_rec.vat_interest               := 0;
    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             := 'N';
    v_cashflow_rec.penalty_process_status     := 'NORMAL';
    v_cashflow_rec.billing_status             := 'NOT';
    v_cashflow_rec.ln_user_col_v03            := 'REPUR';
    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;
  
    --���±��α��ۼ���ΪY
    update con_contract ct
       set ct.offset_flag = 'Y'
     where ct.contract_id = p_contract_id;
    
    --�¹ʵ�I-10652 �����ͬ��Լ�ع���������
    if r_con_contract_rec.business_type='LEASE' then
    --�������һ�ڽ�Լ�ع�������Ϣ��˰
    deal_repur_cash(p_contract_id   => p_contract_id,
                    p_vat_principal => v_vat_principal);
    hls_sys_log_pkg.log('��ͬid' || p_contract_id || '˰��' ||v_vat_principal);
    update con_contract_cashflow caw
       set caw.vat_principal  = v_vat_principal,
           caw.net_principal  = caw.principal - v_vat_principal,
           caw.vat_due_amount = v_vat_principal,
           caw.net_due_amount = caw.due_amount - v_vat_principal
     where caw.contract_id = r_con_contract_rec.contract_id
       and cf_item = 250;
       
   elsif r_con_contract_rec.business_type='LEASEBACK' THEN
     deal_repur_cash_leaseback(p_contract_id  =>p_contract_id,
                               p_interest =>v_interest_back);
     hls_sys_log_pkg.log('��ͬid' || p_contract_id || '˰��' ||v_interest_back);
     update con_contract_cashflow caw
       set caw.interest=v_interest_back,
           caw.vat_interest=round(v_interest_back*0.06/1.06,2),
           caw.net_interest=v_interest_back-round(v_interest_back*0.06/1.06,2),
           caw.principal=caw.due_amount-v_interest_back,
           caw.vat_principal  = 0,
           caw.net_principal  = caw.due_amount-v_interest_back,
           caw.vat_due_amount = round(caw.due_amount*0.06/1.06,2),
           caw.net_due_amount = caw.due_amount - round(caw.due_amount*0.06/1.06,2)
     where caw.contract_id = r_con_contract_rec.contract_id
       and cf_item = 250;
     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_one_time_repo_cashflow');
    
      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_one_time_repo_cashflow');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
     WHEN e_last_rent_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_one_time_repo_cashflow');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
    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            => 'cus_con_contract_repo_pkg',
                                                     p_procedure_function_name => 'calc_one_time_repo_cashflow');
    
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END calc_one_time_repo_cashflow;
  
  PROCEDURE calc_cashflow_division_tj(p_contract_id IN NUMBER,
                                        p_user_id     IN NUMBER) is
  r_con_contract_rec        con_contract%ROWTYPE;
  v_deposit_deduct          number; 
  v_division                 varchar2(10);                                       
  begin 
     --�ع���ͬ
    select *
      into r_con_contract_rec
      from con_contract t
     where t.contract_id = p_contract_id;      
     
     --����ҵ��֤���Զ�����
    select nvl(req.deposit_deduct,0) into v_deposit_deduct from con_contract req  Where req.contract_id = r_con_contract_rec.contract_id;
    select req.division into v_division from con_contract req  Where req.contract_id = r_con_contract_rec.contract_id;

    if v_deposit_deduct>0 and v_division='70' and r_con_contract_rec.contract_id<>null then
       delete from csh_write_off_interface c where c.contract_id= r_con_contract_rec.contract_id and c.trx_interface_id is null;
       for cashflow_tj in (select * from con_contract_cashflow c 
                         where c.contract_id=r_con_contract_rec.contract_id
                               and c.write_off_flag <>'FULL'
                               and c.cf_item in ('1','9')
                               order by c.times,decode(c.cf_item,'9',1,2)) LOOP
          if (cashflow_tj.due_amount-nvl(cashflow_tj.received_amount,0))>=v_deposit_deduct then
               update con_contract_cashflow c set c.received_amount=v_deposit_deduct+nvl(cashflow_tj.received_amount,0)
                                               where c.cashflow_id=cashflow_tj.cashflow_id;
               insert into csh_write_off_interface values (csh_write_off_interface_s.nextval, 'MANUAL', null, null, null, null, 1, r_con_contract_rec.contract_number, cashflow_tj.contract_id, cashflow_tj.cashflow_id, 'DEPOSIT_CREDIT', sysdate, '2018-11', 201811, cashflow_tj.times, cashflow_tj.cf_item, v_deposit_deduct, v_deposit_deduct, null, null, r_con_contract_rec.bp_id_tenant, 'Y', null, null, null, sysdate, 1, sysdate, 1, null, null, cashflow_tj.contract_id, sysdate, null, null);
               exit;
          else 
             update con_contract_cashflow c set c.received_amount=cashflow_tj.due_amount
                                               where c.cashflow_id=cashflow_tj.cashflow_id;
             insert into csh_write_off_interface values (csh_write_off_interface_s.nextval, 'MANUAL', null, null, null, null, 1, r_con_contract_rec.contract_number, cashflow_tj.contract_id, cashflow_tj.cashflow_id, 'DEPOSIT_CREDIT', sysdate, '2018-11', 201811, cashflow_tj.times, cashflow_tj.cf_item, cashflow_tj.due_amount, cashflow_tj.due_amount, null, null, r_con_contract_rec.bp_id_tenant, 'Y', null, null, null, sysdate, 1, sysdate, 1, null, null, cashflow_tj.contract_id, sysdate, null, null);                                  
             v_deposit_deduct:=v_deposit_deduct-cashflow_tj.due_amount+nvl(cashflow_tj.received_amount,0);
             
          end if;
                               
        END LOOP;
    end if;
                                    
  END   calc_cashflow_division_tj;                                    
  
  PROCEDURE repo_wfl_approved_mail(p_change_req_id number,
                                   p_user_id       IN NUMBER) is
    r_change_con_rec    con_contract%rowtype;
    v_bp_name           varchar2(1000);
    v_repurchase_type_n varchar2(1000);
    v_sumbit_user_id    number;
    v_mail_list_id      Number;
    v_contract_number   varchar2(1000);
    v_time              Varchar2(1000);
    v_mail_to           Varchar2(1000);
    v_mail_cc           Varchar2(1000);
    v_subject           Varchar2(1000);
    v_body              Long;
    v_repurchase_date   DATE;
  begin
    --�ʼ��ı������ֶ�
    select t.contract_number,
           hbm.bp_name,
           (SELECT v.code_value_name
              FROM sys_code_values_v v
             WHERE v.code = 'REPURCHASE_TYPES'
               AND v.code_value = t.REPURCHASE_TYPES) AS REPURCHASE_TYPES_n,
           t.repurchase_date
      into v_contract_number,
           v_bp_name,
           v_repurchase_type_n,
           v_repurchase_date
      from con_contract t, hls_bp_master hbm
     where t.contract_id = p_change_req_id
       and t.bp_id_tenant = hbm.bp_id;
    --mail_to ����
    -- 013 GAOYAN   015 ZHAOJIE
    /*select replace(wm_concat(email), ',', ';')
     into v_mail_to
     from exp_employees
    where employee_code in ('GAOYAN', 'ZHAOJIE');*/
    -- @Description:  @Date:  @Modified:Liyuan.Chen 
    v_mail_to := sys_parameter_pkg.email_by_position('013') ||
                 sys_parameter_pkg.email_by_position('015');
  
    Select zj_sys_mailing_list_s.nextval Into v_mail_list_id From dual;
    v_time    := to_char(v_repurchase_date, 'yyyy"��"mm"��"dd"��"');
    v_subject := '�ع���������������֪ͨ';
    v_body    := '�𾴵��쵼������:' || chr(13) || chr(10) || chr(13) || chr(10) ||
                 chr(32) || chr(32) || chr(32) || chr(32) || chr(32) ||
                 chr(32) || '��ͬ��ţ�' || v_contract_number || ',������:' ||
                 v_bp_name || ',ͨ����' || v_repurchase_type_n || '����,��Լ�ع���:' ||
                 v_time || chr(13) || chr(10) || chr(13) || chr(10) ||
                 '��֪Ϥ,лл��';
    Select zj_sys_mailing_list_s.nextval Into v_mail_list_id From dual;
    ---�������ʼ���¼����
    zj_sys_mail_pkg.insert_mailing_list(p_mail_list_id   => v_mail_list_id,
                                        p_mail_to        => v_mail_to,
                                        p_mail_cc        => v_mail_cc,
                                        p_subject        => v_subject,
                                        p_body           => v_body,
                                        p_user_id        => p_user_id,
                                        p_mail_source    => Null,
                                        p_mail_source_id => Null,
                                        p_content_type   => 'text/plain');
    --���͸��ύ��
    repo_wfl_mail_to_submit(p_change_req_id => p_change_req_id,
                            p_user_id       => p_user_id,
                            p_wfl_flag_desc => 'ͨ��');
  
  end repo_wfl_approved_mail;

  PROCEDURE repo_wfl_mail_to_submit(p_change_req_id number,
                                    p_user_id       IN NUMBER,
                                    p_wfl_flag_desc varchar2) is
    r_change_req_rec    con_contract_change_req%rowtype;
    r_change_con_rec    con_contract%rowtype;
    v_mail_list_id      Number;
    v_contract_number   varchar2(1000);
    v_change_req_number varchar2(1000);
    v_time              Varchar2(1000);
    v_mail_to           Varchar2(1000);
    v_mail_cc           Varchar2(1000);
    v_subject           Varchar2(1000);
    v_body              Long;
  begin
    ----�ʼ��ı������ֶ�,mail_to
    select t.change_req_number, ee.email
      into v_change_req_number, v_mail_to
      from con_contract_change_req t, sys_user su, exp_employees ee
     where t.change_req_id = p_change_req_id
       and t.created_by = su.user_id
       and su.employee_id = ee.employee_id;
    --��ͬ��Ϣ
    select *
      into r_change_con_rec
      from con_contract t
     where t.contract_id = p_change_req_id;
  
    v_time    := to_char(Sysdate, 'yyyy"��"mm"��"dd"��"');
    v_subject := '�ع���������������֪ͨ';
    v_body    := '����:' || chr(13) || chr(10) || chr(13) || chr(10) ||
                 chr(32) || chr(32) || chr(32) || chr(32) || chr(32) ||
                 chr(32) || '�ع����빤����-�ع���ţ�' || v_change_req_number ||
                 '��ͬ��ţ�' || r_change_con_rec.contract_number || ',������������' ||
                 p_wfl_flag_desc || '��' || '
  http://10.203.0.125:8082/hlcm_leasing/login' || chr(13) ||
                 chr(10) || chr(13) || chr(10) || '��֪Ϥ,лл��';
    Select zj_sys_mailing_list_s.nextval Into v_mail_list_id From dual;
    ---�������ʼ���¼����
    zj_sys_mail_pkg.insert_mailing_list(p_mail_list_id   => v_mail_list_id,
                                        p_mail_to        => v_mail_to,
                                        p_mail_cc        => v_mail_cc,
                                        p_subject        => v_subject,
                                        p_body           => v_body,
                                        p_user_id        => p_user_id,
                                        p_mail_source    => Null,
                                        p_mail_source_id => Null,
                                        p_content_type   => 'text/plain');
  end repo_wfl_mail_to_submit;
end cus_con_contract_repo_pkg;
/