CREATE OR REPLACE Package prj_project_pkg Is

  -- Author  : ZHANGLEI
  -- Created : 2013/8/5 14:15:09
  -- Purpose :
  Function get_next_quotation_version(p_project_id Number) Return Varchar2;

  Procedure save_project_from_chance(p_chance_id             In Number,
                                     p_project_name          In Varchar2,
                                     p_project_document_type In Varchar2,
                                     p_business_type         In Varchar2,
                                     p_project_id            Out Number,
                                     p_project_number        Out Varchar2,
                                     p_user_id               In Number);

  Procedure project_update_check(p_project_id Number, p_user_id Number);

  Procedure project_submit(p_project_id Number, p_user_id Number);

  Procedure project_close(p_project_id Number, p_user_id Number);

  --项目报价拆分
  Procedure project_quotation_split(p_quotation_split_id Number,
                                    p_user_id            Number);

  Function project_get_record(p_project_id prj_project.project_id%Type,
                              p_user_id    Number) Return prj_project%Rowtype;

  Procedure update_prj_dealcre_flag(p_project_id   Number,
                                    p_declare_flag Varchar2,
                                    p_user_id      Number);

  --lr 修改商业伙伴参数 2013-08-30
  Procedure update_prj_bp_count_parameter(p_project_id                  Number,
                                          p_number_of_tenant            Number,
                                          p_number_of_guarantor         Number,
                                          p_number_of_actual_controller Number,
                                          p_user_id                     Number);

  Procedure delete_prj_bp_count_parameter(p_project_id  Number,
                                          p_bp_category Varchar2,
                                          p_bp_class    Varchar2,
                                          p_bp_seq      Number,
                                          p_table_name  Varchar2,
                                          p_user_id     Number);

  Procedure update_prj_project_status(p_project_id Number,
                                      p_user_id    Number);

  Procedure project_print(p_project_id Number, p_user_id Number);

  --校验项目的主报价融资金额总和是否小于辅助报价融资金额
  Procedure check_quotation_amount(p_project_id Number, p_user_id Number);

  --发送初评结论邮件
  Procedure send_approval_email(p_rcpt_record_id Number,
                                p_project_id     Number,
                                p_user_id        Number);

  --发送终评结论邮件
  Procedure send_approval_last_email(p_approval_id Number,
                                     p_project_id  Number,
                                     p_user_id     Number);

  --项目创建保存时校验
  Procedure project_create_save_check(p_project_id       Number,
                                      p_user_id          Number,
                                      p_function_code    Varchar2,
                                      p_function_usage   Varchar2,
                                      p_save_source_type Varchar2 Default Null,
                                      p_project_status   Varchar2 Default Null,
                                      p_cdd_list_id      Out Number);

  --创建cdd
  Procedure save_cdd_item_from_templt(p_document_category Varchar2,
                                      p_document_type     Varchar2,
                                      p_document_id       Number,
                                      p_company_id        Number,
                                      p_function_code     Varchar2,
                                      p_function_usage    Varchar2,
                                      p_user_id           Number,
                                      p_cdd_list_id       In Out Number);

  ---项目类承租人保存后校验                                      
  Procedure project_tenant_save_check(p_project_id Number,
                                      p_user_id    Number);

  ---零售特批提交
  Procedure special_retail_submit(p_special_retail_id Number,
                                  p_user_id           Number);

  ---零售特批审批通过
  Procedure approve_special_retail(p_special_retail_id Number,
                                   p_user_id           Number);

  --零售特批审批拒绝
  Procedure reject_special_retail(p_special_retail_id Number,
                                  p_user_id           Number);

  -- 租赁申请创建时填写比例校验选择的产品名称的比例范围  add by liukang 20160420
  Procedure check_price_list(p_price_list         Varchar2,
                             p_int_rate_display   Number,
                             p_down_payment_ratio Number,
                             p_balloon_ratio      Number,
                             p_deposit_ratio      Number,
                             p_lease_times        Number,
                             p_lease_charge_ratio Number,
                             p_user_id            Number);

  Procedure create_cdd(p_document_type  Varchar2,
                       p_project_id     Number,
                       p_company_id     Number,
                       p_function_code  Varchar2,
                       p_function_usage Varchar2,
                       p_user_id        Number,
                       p_cdd_list_id    In Out Number);

  Procedure check_gps_visit(p_product_plan_id Number,
                            p_lease_times     Number,
                            p_dp_ratio        Number,
                            p_finance_amount  Number,
                            p_result          Out Varchar2);

  Function get_prj_project_rec(p_project_id Number, p_user_id Number)
    Return prj_project%Rowtype;

  ---商用车租赁申请预审提交
  Procedure prj_project_submit(p_project_id  Number,
                               p_user_id     Number,
                               p_company_id  Number,
                               p_layout_code Varchar2);

  ---商用车租赁申请预审通过
  Procedure prj_project_approved(p_project_id Number, p_user_id Number);

  ---商用车租赁申请预审拒绝
  Procedure prj_project_rejected(p_project_id Number, p_user_id Number);

  --预审审批拒绝
  Procedure prj_project_pre_rejected(p_project_id Number, p_user_id Number);

  --获取信审风险敞口
  Function get_risk_exposure_amount(p_project_id In Number) Return Number;

  Function get_prj_division(p_project_id In Number) Return Number;

  Procedure project_save_check(p_project_id Number);

  --产品分配大区
  Procedure update_district_ln(p_product_plan_id Number Default Null,
                               p_district_id     Varchar2,
                               p_user_id         Varchar2);

  --得到产品方案是否自定义
  Procedure get_if_self_definition(p_product_plan_id    Number Default Null,
                                   p_if_self_definition Out Varchar2,
                                   p_user_id            Varchar2);

  --得到附件数量
  Procedure get_cdd_count(p_document_table Varchar2,
                          p_document_id    Number,
                          p_cdd_list_id    Number,
                          p_count          Out Number);

  Procedure prj_project_modify_status(p_project_id     Number,
                                      p_user_id        Number,
                                      p_project_status varchar2);

End prj_project_pkg;
/
CREATE OR REPLACE Package Body prj_project_pkg Is

  g_document_category Constant Varchar2(100) := 'PROJECT';

  e_get_project_number_err Exception;

  e_lock_table Exception;

  Pragma Exception_Init(e_lock_table, -54);

  Function get_project_id Return Number Is
    v_id Number;
  Begin
    Select prj_project_s.nextval Into v_id From dual;
    Return v_id;
  End;

  Function get_project_bp_id Return Number Is
    v_id Number;
  Begin
    Select prj_project_bp_s.nextval Into v_id From dual;
    Return v_id;
  End;

  Function get_project_lease_item_id Return Number Is
    v_id Number;
  Begin
    Select prj_project_lease_item_s.nextval Into v_id From dual;
    Return v_id;
  End;

  Function get_quotation_id Return Number Is
    v_id Number;
  Begin
    Select prj_quotation_s.nextval Into v_id From dual;
    Return v_id;
  End;

  Procedure insert_project(p_project_rec prj_project%Rowtype) Is
  Begin
    Insert Into prj_project Values p_project_rec;
  End;

  Procedure update_project(p_project_rec prj_project%Rowtype) Is
  Begin
    Update prj_project
       Set Row = p_project_rec
     Where project_id = p_project_rec.project_id;
  End;

  Procedure insert_project_bp(p_project_bp_rec prj_project_bp%Rowtype) Is
  Begin
    Insert Into prj_project_bp Values p_project_bp_rec;
  End;

  Procedure insert_project_lease_item(p_project_lease_item_rec prj_project_lease_item%Rowtype) Is
  Begin
    Insert Into prj_project_lease_item Values p_project_lease_item_rec;
  End;

  Procedure insert_quotation(p_quotation_rec prj_quotation%Rowtype) Is
  Begin
    Insert Into prj_quotation Values p_quotation_rec;
  End;

  Function get_project_number(p_document_type    Varchar2,
                              p_transaction_date Date,
                              p_company_id       Number,
                              p_user_id          Number) Return Varchar2 Is
    v_no Varchar2(100);
  Begin
    v_no := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => g_document_category,
                                                     p_document_type     => p_document_type,
                                                     p_company_id        => p_company_id,
                                                     p_operation_unit_id => Null,
                                                     p_operation_date    => p_transaction_date,
                                                     p_created_by        => p_user_id);
    If v_no = fnd_code_rule_pkg.c_error Then
      Raise e_get_project_number_err;
    End If;
    Return v_no;
  End;

  Function is_nan(p_text Varchar2) Return Number Is
    v_text Number;
  Begin
    v_text := to_number(p_text);
    Return v_text;
  Exception
    When Others Then
      Return - 999;
  End;

  Function get_next_quotation_version(p_project_id Number) Return Varchar2 Is
    v_max_version Number := 0;
    v_version     Number := 0;
    v_temp        Varchar2(200);
  Begin
    For ex In (Select t.version
                 From prj_quotation t
                Where t.document_id = p_project_id
                  And t.document_category = 'PROJECT'
                  And version Like 'v%') Loop
      v_temp    := substr(ex.version, instr(ex.version, 'v') + 1);
      v_version := is_nan(p_text => v_temp);
      If v_version <> -999 And v_max_version < v_version Then
        v_max_version := v_version;
      End If;
    End Loop;
    v_max_version := v_max_version + 1;
    Return 'v' || v_max_version || '.0';
  End;

  Procedure save_cdd_item_doc_ref(p_source_document_table Varchar2,
                                  p_source_document_id    Number,
                                  p_to_document_table     Varchar2,
                                  p_to_document_id        Number,
                                  p_user_id               Number) Is
  Begin
    --save prj_cdd_item_doc_ref
    Insert Into prj_cdd_item_doc_ref
      (doc_ref_id,
       document_table,
       document_id,
       check_id,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date)
      (Select prj_cdd_item_doc_ref_s.nextval,
              p_to_document_table,
              p_to_document_id,
              check_id,
              p_user_id,
              Sysdate,
              p_user_id,
              Sysdate
         From prj_cdd_item_doc_ref
        Where document_table = p_source_document_table
          And document_id = p_source_document_id);
  End;

  Procedure save_project(p_chance_id             Number,
                         p_chance_rec            prj_chance%Rowtype,
                         p_project_id            Number,
                         p_project_name          Varchar2,
                         p_project_document_type Varchar2,
                         p_business_type         Varchar2,
                         p_project_number        Out Varchar2,
                         p_user_id               Number) Is
    r_project_rec prj_project%Rowtype;
  Begin
    r_project_rec.project_id                  := p_project_id;
    r_project_rec.chance_id                   := p_chance_id;
    r_project_rec.company_id                  := p_chance_rec.company_id;
    r_project_rec.document_category           := g_document_category;
    r_project_rec.business_type               := p_business_type;
    r_project_rec.document_type               := p_project_document_type;
    r_project_rec.project_name                := p_project_name;
    r_project_rec.project_number              := get_project_number(p_document_type    => 'CARLS',
                                                                    p_transaction_date => trunc(Sysdate),
                                                                    p_company_id       => r_project_rec.company_id,
                                                                    p_user_id          => p_user_id);
    r_project_rec.project_status              := 'NEW';
    r_project_rec.lease_organization          := p_chance_rec.lease_organization;
    r_project_rec.lease_channel               := p_chance_rec.lease_channel;
    r_project_rec.division                    := p_chance_rec.division;
    r_project_rec.bp_class                    := p_chance_rec.bp_class;
    r_project_rec.number_of_tenant            := 1;
    r_project_rec.number_of_guarantor         := 1;
    r_project_rec.number_of_actual_controller := 1;
    r_project_rec.number_of_quotation         := 1;
    r_project_rec.created_by                  := p_user_id;
    r_project_rec.creation_date               := Sysdate;
    r_project_rec.last_updated_by             := p_user_id;
    r_project_rec.last_update_date            := Sysdate;
    insert_project(p_project_rec => r_project_rec);
    --单据复制
    hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'PRJ_CHANCE',
                                         p_from_doc_pk    => p_chance_id,
                                         p_to_doc_table   => 'PRJ_PROJECT',
                                         p_to_doc_pk      => p_project_id,
                                         p_function_code  => Null,
                                         p_function_usage => Null,
                                         p_user_id        => p_user_id);
    --处理个别字段
    /*select *
      into r_project_rec
      from prj_project
     where project_id = p_project_id;
    
    
    
    update_project(p_project_rec => r_project_rec);
    */
    save_cdd_item_doc_ref(p_source_document_table => 'PRJ_CHANCE',
                          p_source_document_id    => p_chance_id,
                          p_to_document_table     => 'PRJ_PROJECT',
                          p_to_document_id        => p_project_id,
                          p_user_id               => p_user_id);
    aut_document_authority_pkg.copy_trx_user_authority(p_from_doc_category => 'CHANCE',
                                                       p_from_doc_id       => p_chance_id,
                                                       p_to_doc_category   => 'PROJECT',
                                                       p_to_doc_id         => p_project_id,
                                                       p_user_id           => p_user_id);
    aut_document_authority_pkg.insert_trx_user_authority(p_company_id    => p_chance_rec.company_id,
                                                         p_owner_user_id => p_chance_rec.owner_user_id,
                                                         p_trx_category  => 'PROJECT',
                                                         p_trx_id        => p_project_id,
                                                         p_start_date    => trunc(Sysdate),
                                                         p_end_date      => to_date('30000101',
                                                                                    'yyyymmdd'),
                                                         p_user_id       => p_user_id);
    p_project_number := r_project_rec.project_number;
  End;

  Procedure save_project_bp(p_chance_rec prj_chance%Rowtype,
                            p_project_id Number,
                            p_user_id    Number) Is
    r_project_bp_rec prj_project_bp%Rowtype;
  Begin
    r_project_bp_rec.prj_bp_id    := get_project_bp_id;
    r_project_bp_rec.bp_seq       := 1;
    r_project_bp_rec.bp_name      := p_chance_rec.bp_name;
    r_project_bp_rec.project_id   := p_project_id;
    r_project_bp_rec.creat_bp_flg := 'N';
    r_project_bp_rec.bp_category  := 'TENANT';
    r_project_bp_rec.bp_class     := p_chance_rec.bp_class;
    r_project_bp_rec.contract_seq := 1;
    r_project_bp_rec.industry     := p_chance_rec.industry;
    /*r_project_bp_rec.primary_business        := p_chance_rec.primary_business;
    r_project_bp_rec.prime_operating_revenue := p_chance_rec.prime_operating_revenue;
    r_project_bp_rec.net_profit              := p_chance_rec.net_profit;
    r_project_bp_rec.currency                := p_chance_rec.currency;
    r_project_bp_rec.country_id              := p_chance_rec.country_id;
    r_project_bp_rec.province_id             := p_chance_rec.province_id;
    r_project_bp_rec.city_id                 := p_chance_rec.city_id;
    r_project_bp_rec.district_id             := p_chance_rec.district_id;
    r_project_bp_rec.street                  := p_chance_rec.street;
    r_project_bp_rec.address                 := p_chance_rec.address;
    r_project_bp_rec.contact_person          := p_chance_rec.contact_person_1;
    r_project_bp_rec.position                := p_chance_rec.position_1;
    r_project_bp_rec.cell_phone              := p_chance_rec.cell_phone_1;
    r_project_bp_rec.phone                   := p_chance_rec.phone_1;*/
    r_project_bp_rec.created_by       := p_user_id;
    r_project_bp_rec.creation_date    := Sysdate;
    r_project_bp_rec.last_updated_by  := p_user_id;
    r_project_bp_rec.last_update_date := Sysdate;
    insert_project_bp(p_project_bp_rec => r_project_bp_rec);
  End;

  Procedure save_project_lease_item(p_chance_rec prj_chance%Rowtype,
                                    p_project_id Number,
                                    p_user_id    Number) Is
    r_project_lease_item_rec prj_project_lease_item%Rowtype;
  Begin
    r_project_lease_item_rec.project_lease_item_id := get_project_lease_item_id;
    r_project_lease_item_rec.project_id            := p_project_id;
    r_project_lease_item_rec.creat_item_flg        := 'N';
    r_project_lease_item_rec.short_name            := p_chance_rec.lease_item_short_name;
    r_project_lease_item_rec.full_name             := p_chance_rec.lease_item_short_name;
    r_project_lease_item_rec.vender_name           := p_chance_rec.vender_name;
    r_project_lease_item_rec.contract_seq          := 1;
    r_project_lease_item_rec.price                 := p_chance_rec.lease_item_amount;
    r_project_lease_item_rec.installation_site     := p_chance_rec.installation_site;
    r_project_lease_item_rec.annual_pay_times      := p_chance_rec.annual_pay_times;
    r_project_lease_item_rec.created_by            := p_user_id;
    r_project_lease_item_rec.creation_date         := Sysdate;
    r_project_lease_item_rec.last_updated_by       := p_user_id;
    r_project_lease_item_rec.last_update_date      := Sysdate;
    insert_project_lease_item(p_project_lease_item_rec => r_project_lease_item_rec);
  End;

  Procedure save_quotation(p_chance_id  Number,
                           p_project_id Number,
                           p_user_id    Number) Is
    r_quotation_rec prj_quotation%Rowtype;
  Begin
    For c_quotation In (Select *
                          From prj_quotation
                         Where document_category = 'CHANCE'
                           And document_id = p_chance_id
                           And enabled_flag = 'Y'
                           And calc_session_id Is Not Null) Loop
      r_quotation_rec                   := c_quotation;
      r_quotation_rec.quotation_id      := get_quotation_id;
      r_quotation_rec.document_category := 'PROJECT';
      r_quotation_rec.document_id       := p_project_id;
      r_quotation_rec.quotation_type    := 'MAJOR';
      r_quotation_rec.created_by        := p_user_id;
      r_quotation_rec.creation_date     := Sysdate;
      r_quotation_rec.last_updated_by   := p_user_id;
      r_quotation_rec.last_update_date  := Sysdate;
      If c_quotation.calc_session_id Is Not Null Then
        --复制计算器4张表的数据
        hls_fin_calculator_itfc_pkg.copy_calculator(p_from_calc_session_id => c_quotation.calc_session_id,
                                                    p_to_calc_session_id   => r_quotation_rec.calc_session_id,
                                                    p_user_id              => p_user_id);
      End If;
      insert_quotation(p_quotation_rec => r_quotation_rec);
    End Loop;
  End;

  Procedure save_project_from_chance(p_chance_id             In Number,
                                     p_project_name          In Varchar2,
                                     p_project_document_type In Varchar2,
                                     p_business_type         In Varchar2,
                                     p_project_id            Out Number,
                                     p_project_number        Out Varchar2,
                                     p_user_id               In Number) Is
    r_chance_rec prj_chance%Rowtype;
    v_project_id prj_project.project_id%Type;
    e_chance_status_err Exception;
    e_lock_error        Exception;
    Pragma Exception_Init(e_lock_error, -54);
  Begin
    Select *
      Into r_chance_rec
      From prj_chance
     Where chance_id = p_chance_id
       For Update Nowait;
    --状态检查
    If Not r_chance_rec.chance_status In ('NEW') Then
      Raise e_chance_status_err;
    End If;
    v_project_id := get_project_id;
    /* 处理 prj_project */
    save_project(p_chance_id             => p_chance_id,
                 p_chance_rec            => r_chance_rec,
                 p_project_id            => v_project_id,
                 p_project_name          => p_project_name,
                 p_project_document_type => p_project_document_type,
                 p_business_type         => p_business_type,
                 p_project_number        => p_project_number,
                 p_user_id               => p_user_id);
    p_project_id := v_project_id;
    hls_document_flow_pkg.insert_document_flow(p_doc_category        => 'PROJECT',
                                               p_doc_id              => v_project_id,
                                               p_doc_line_id         => Null,
                                               p_doc_number          => p_project_number,
                                               p_source_doc_category => 'CHANCE',
                                               p_source_doc_id       => p_chance_id,
                                               p_source_doc_line_id  => Null,
                                               p_source_doc_number   => r_chance_rec.chance_number,
                                               p_user_id             => p_user_id);
    /* 处理 prj_project_bp */
    /*save_project_bp(p_chance_rec => r_chance_rec,
    p_project_id => v_project_id,
    p_user_id => p_user_id);*/
    /* 处理 prj_project_lease_item */
    save_project_lease_item(p_chance_rec => r_chance_rec,
                            p_project_id => v_project_id,
                            p_user_id    => p_user_id);
    /* 处理 prj_quotation */
    save_quotation(p_chance_id  => p_chance_id,
                   p_project_id => v_project_id,
                   p_user_id    => p_user_id);
    Update prj_chance
       Set chance_status    = 'PRJ_CREATED',
           last_update_date = Sysdate,
           last_updated_by  = p_user_id
     Where chance_id = p_chance_id;
    --插入商机操作历史
    hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => r_chance_rec.document_category,
                                                           p_document_id       => p_chance_id,
                                                           p_operation_code    => 'PRJ_CREATED',
                                                           p_user_id           => p_user_id,
                                                           p_operation_time    => Sysdate,
                                                           p_description       => Null);
    --插入项目操作历史
    hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => 'PROJECT',
                                                           p_document_id       => v_project_id,
                                                           p_operation_code    => hls_doc_operate_history_pkg.c_generate,
                                                           p_user_id           => p_user_id,
                                                           p_operation_time    => Sysdate,
                                                           p_description       => Null);
    yonda_doc_history_pkg.yonda_insert_doc_status(p_document_id       => v_project_id,
                                                  p_document_type     => 'PRJ',
                                                  p_document_category => 'PROJECT',
                                                  p_doc_status        => yonda_doc_history_pkg.yonda_prj_create,
                                                  p_instance_id       => Null,
                                                  p_user_id           => p_user_id);
  Exception
    When e_get_project_number_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.GET_PRJ_PROJECT_NUMBER_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'PRJ_PROJECT_PKG',
                                                      p_procedure_function_name => 'SAVE_PROJECT_FROM_CHANCE');
      --DBMS_OUTPUT.PUT_LINE('1');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_chance_status_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.PRJ_CHANCE_STATUS_CHECK_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'PRJ_PROJECT_PKG',
                                                      p_procedure_function_name => 'SAVE_PROJECT_FROM_CHANCE');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_lock_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.LOCK_PRJ_CHANCE_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'PRJ_PROJECT_PKG',
                                                      p_procedure_function_name => 'SAVE_PROJECT_FROM_CHANCE');
      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            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'SAVE_PROJECT_FROM_CHANCE');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure project_update_check(p_project_id Number, p_user_id Number) Is
    v_project_record prj_project%Rowtype;
    e_status_error Exception;
    v_count Number;
  
    v_err_msg Varchar2(2000);
    e_com_err Exception;
     v_sum number;
    e_count_err exception;
  
    v_lease_item_num Number;
    v_ton_code       Varchar2(200);
  
    Procedure check_bp_con_seq(p_project_id Number, p_user_id Number) Is
      v_exists Number;
      e_prj_bp_con_seq_error Exception;
    
    Begin
      Select Count(1)
        Into v_exists
        From prj_project_bp t
       Where t.project_id = p_project_id
         And t.bp_category = 'TENANT'
       Group By t.bp_category, t.contract_seq
      Having Count(*) > 1;
      Raise e_prj_bp_con_seq_error;
    Exception
      When no_data_found Then
        Null;
      When e_prj_bp_con_seq_error Then
        sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'HLS_DOCUMENT_SAVE_PKG.PRJ_BP_CON_SEQ_ERROR',
                                                        p_created_by              => p_user_id,
                                                        p_package_name            => 'hls_document_save_pkg',
                                                        p_procedure_function_name => 'check_bp_con_seq');
        raise_application_error(sys_raise_app_error_pkg.c_error_number,
                                sys_raise_app_error_pkg.g_err_line_id);
    End;
  
  Begin
    v_project_record := project_get_record(p_project_id => p_project_id,
                                           p_user_id    => p_user_id);
    check_bp_con_seq(p_project_id => p_project_id, p_user_id => p_user_id);
  
    --add by chenlingfeng 2018-7-24 09:32:44                 
    Select Count(1)
      Into v_count
      From prj_project_bp a
     Where a.project_id = p_project_id
       And a.bp_category = 'TENANT';
  
   -- If v_count <> 1 Then
   --   v_err_msg := '有且只能有一个主承租人!';
    --  Raise e_com_err;
  --  End If;
   Select count(*)
      Into v_sum
      From prj_project_lease_item i
     Where i.project_id =p_project_id
     and i.machine_number in (select t.machine_number
      from prj_project_lease_item t where t.machine_number  is not  null);
   -- if v_sum <> 0 then
    --  raise e_count_err;
  --  end if;
  
    Select Count(1)
      Into v_lease_item_num
      From prj_project_lease_item i
     Where i.project_id = p_project_id
       And i.equipment_type = 'MAIN';
  
    If v_lease_item_num <> 1 Then
      v_err_msg := '租赁物有且只能有一个主物件';
      Raise e_com_err;
    End If;
    
    --吨位类型
    Begin
    
      Select v.ref_v01
        Into v_ton_code
        From sys_code_values_v v
       Where v.code_value =
             (Select i.ton_code
                From prj_project_lease_item i
               Where i.project_id = p_project_id
                 And i.equipment_type = 'MAIN')
         And v.code = 'DS_TON_NAME'; --吨位类型 --报价中使用
    
    Exception
      When no_data_found Then
        v_ton_code := '0';
    End;
  
    Update prj_quotation pq
       Set pq.pmt_first = v_ton_code
     Where pq.document_id = p_project_id
       And pq.document_category = 'PROJECT';
  
    Update hls_fin_calculator_hd h
       Set h.pmt_first = v_ton_code
     Where h.calc_session_id In
           (Select pq.calc_session_id
              From prj_quotation pq
             Where pq.document_id = p_project_id
               And pq.document_category = 'PROJECT');
    --end
  
    --更新bp
    Update prj_project_bp t
       Set (t.bp_code,
            t.bp_class,
            t.bp_name,
            t.id_type,
            t.id_card_no,
            t.organization_code,
            t.tax_registry_num,
            t.cell_phone,
            t.phone,
            t.phone_2) =
           (Select t1.bp_code,
                   t1.bp_class,
                   t1.bp_name,
                   t1.id_type,
                   t1.id_card_no,
                   t1.organization_code,
                   t1.tax_registry_num,
                   t1.cell_phone,
                   t1.phone,
                   t1.phone_2
              From hls_bp_master t1
             Where t1.bp_id = t.bp_id)
     Where t.project_id = p_project_id;
    --
  
    If Not v_project_record.project_status In
        ('APPROVED', 'APPROVED_RETURN', 'CONDITIONAL', 'NEW') Then
      Raise e_status_error;
    End If;
  
    Update prj_project t
       Set t.bp_id_tenant =
           (Select a.bp_id
              From prj_project_bp a
             Where a.project_id = p_project_id
               And a.bp_category = 'TENANT')
     Where t.project_id = p_project_id;
  
    aut_document_authority_pkg.insert_trx_user_authority(p_company_id    => v_project_record.company_id,
                                                         p_owner_user_id => v_project_record.owner_user_id,
                                                         p_trx_category  => 'PROJECT',
                                                         p_trx_id        => p_project_id,
                                                         p_start_date    => trunc(Sysdate),
                                                         p_end_date      => to_date('30000101',
                                                                                    'yyyymmdd'),
                                                         p_user_id       => p_user_id);
  Exception
    When e_com_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => v_err_msg,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'PROJECT_UPDATE_CHECK');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    
    When e_status_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.PROJECT_UPDATE_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'PRJ_PROJECT_PKG',
                                                      p_procedure_function_name => 'PROJECT_UPDATE_CHECK');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    when e_count_err then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该机号已存在,请重新确认!',
                                                     
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'prj_project_check_pkg',
                                                     p_procedure_function_name => 'check_prj_lease_item');
      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            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'PROJECT_UPDATE_CHECK');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  --项目报价拆分
  Procedure project_quotation_split(p_quotation_split_id Number,
                                    p_user_id            Number) Is
    r_prj_quotation_split_rec prj_quotation_split%Rowtype;
    r_quotation_original_rec  prj_quotation%Rowtype;
    r_quotation_new_rec       prj_quotation%Rowtype;
    v_prj_quotation_id        Number;
  Begin
    --调用计算器拆分程序
    hls_fin_calculator_split_pkg.calculator_split(p_quotation_split_id => p_quotation_split_id,
                                                  p_user_id            => p_user_id);
    Select *
      Into r_prj_quotation_split_rec
      From prj_quotation_split
     Where quotation_split_id = p_quotation_split_id
       For Update Nowait;
    Select *
      Into r_quotation_original_rec
      From prj_quotation
     Where quotation_id = r_prj_quotation_split_rec.quotation_id;
    For c_split In (Select *
                      From hls_fin_calculator_split
                     Where quotation_split_id = p_quotation_split_id
                     Order By split_seq) Loop
      r_quotation_new_rec := r_quotation_original_rec;
      Select prj_quotation_s.nextval Into v_prj_quotation_id From dual;
      r_quotation_new_rec.quotation_id     := v_prj_quotation_id;
      r_quotation_new_rec.calc_session_id  := c_split.split_calc_session_id;
      r_quotation_new_rec.quotation_date   := trunc(Sysdate);
      r_quotation_new_rec.quotation_type   := 'MAJOR';
      r_quotation_new_rec.version          := get_next_quotation_version(r_quotation_original_rec.document_id);
      r_quotation_new_rec.internal_confirm := 'N';
      r_quotation_new_rec.external_confirm := 'N';
      r_quotation_new_rec.enabled_flag     := 'Y';
      r_quotation_new_rec.created_by       := p_user_id;
      r_quotation_new_rec.creation_date    := Sysdate;
      r_quotation_new_rec.last_updated_by  := p_user_id;
      r_quotation_new_rec.last_update_date := Sysdate;
      insert_quotation(r_quotation_new_rec);
      --单据复制
      hls_document_transfer_pkg.doc_to_doc(p_from_doc_table => 'HLS_FIN_CALCULATOR_HD',
                                           p_from_doc_pk    => c_split.split_calc_session_id,
                                           p_to_doc_table   => 'PRJ_QUOTATION',
                                           p_to_doc_pk      => r_quotation_new_rec.quotation_id,
                                           p_function_code  => Null,
                                           p_function_usage => Null,
                                           p_user_id        => p_user_id);
    End Loop;
  Exception
    When Others Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => dbms_utility.format_error_backtrace || ' ' ||
                                                                                  Sqlerrm,
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'project_quotation_split');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  /*procedure project_confirm(p_project_id number,
                            p_user_id    number)is
    r_project_rec        prj_project%rowtype;
    v_approval_method    hls_document_type.approval_method%type;
    e_project_confirm_status_err exception;
  
  begin
  
    r_project_rec :=  project_get_record(p_project_id,p_user_id);
  
    if not r_project_rec.project_status in ('NEW','REJECT') then
      raise e_project_confirm_status_err;
    end if;
  
    update prj_project
       set project_status = 'CONFIRM',last_updated_by = p_user_id,last_update_date = sysdate
     where project_id = p_project_id;
  
  
  exception
    when e_project_confirm_status_err then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.PROJECT_CONFIRM_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'PRJ_PROJECT_PKG',
                                                      p_procedure_function_name => 'PROJECT_CONFIRM');
  
      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            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'PROJECT_CONFIRM');
  
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  end;*/
  Procedure project_submit(p_project_id Number, p_user_id Number) Is
    v_document_type     prj_project.document_type%Type;
    v_document_category prj_project.document_category%Type;
    r_project_rec       prj_project%Rowtype;
    v_approval_method   hls_document_type.approval_method%Type;
    e_project_submit_status_err Exception;
      v_major_quo_number number;
    v_major_quo_number_error exception;
  Begin
    r_project_rec := project_get_record(p_project_id, p_user_id);
    select count(1)
      into v_major_quo_number
      from prj_project p, prj_quotation q,hls_fin_calculator_hd  hd
     where p.project_id = q.document_id
       and q.document_category = 'PROJECT'
       and   hd.calc_session_id=q.calc_session_id
       and  hd.calc_successful ='Y'
       --and q.quotation_type = 'MAJOR'
       and p.project_id = p_project_id;
    if v_major_quo_number <> 1 then
      raise v_major_quo_number_error;
    end if;
      
    Update prj_project t
       Set t.bp_id_tenant =
           (Select a.bp_id
              From prj_project_bp a
             Where a.project_id = p_project_id
               And a.bp_category = 'TENANT')
     Where t.project_id = r_project_rec.project_id;
    If Not (r_project_rec.project_status In
        ('NEW', 'CONDITIONAL', 'APPROVED_RETURN')) Then
      Raise e_project_submit_status_err;
    End If;
    Select approval_method
      Into v_approval_method
      From hls_document_type
     Where document_category = r_project_rec.document_category
       And document_type = r_project_rec.document_type;
    If v_approval_method = 'WORK_FLOW' Then
      prj_project_workflow_pkg.workflow_start(p_project_id => p_project_id,
                                              p_user_id    => p_user_id);
    Else
      Update prj_project
         Set project_status   = 'APPROVING',
             last_updated_by  = p_user_id,
             last_update_date = Sysdate
       Where project_id = p_project_id;
    End If;
    --add by wcs 2014-10-28 for doc_status
    Select t.document_type, t.document_category
      Into v_document_type, v_document_category
      From prj_project t
     Where t.project_id = p_project_id;
    hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => r_project_rec.document_category,
                                                           p_document_id       => p_project_id,
                                                           p_operation_code    => hls_doc_operate_history_pkg.c_submit,
                                                           p_user_id           => p_user_id,
                                                           p_operation_time    => Sysdate,
                                                           p_description       => Null);
    yonda_doc_history_pkg.yonda_insert_doc_status(p_document_id       => p_project_id,
                                                  p_document_type     => v_document_type,
                                                  p_document_category => v_document_category,
                                                  p_doc_status        => yonda_doc_history_pkg.yonda_doc_submit,
                                                  p_instance_id       => Null,
                                                  p_user_id           => p_user_id);
  Exception
    When e_project_submit_status_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.PROJECT_SUBMIT_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'PRJ_PROJECT_PKG',
                                                      p_procedure_function_name => 'PROJECT_SUBMIT');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
     when v_major_quo_number_error then
     sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '请维护报价!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'PROJECT_SUBMIT');
      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            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'PROJECT_SUBMIT');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure project_submit(p_document_category Varchar2,
                           p_document_id       Number,
                           p_user_id           Number) Is
  Begin
    project_submit(p_project_id => p_document_id, p_user_id => p_user_id);
  End;

  Function project_get_record(p_project_id prj_project.project_id%Type,
                              p_user_id    Number) Return prj_project%Rowtype Is
    v_record prj_project%Rowtype;
  Begin
    Select *
      Into v_record
      From prj_project v
     Where v.project_id = p_project_id
       For Update Nowait;
    Return v_record;
  Exception
    When e_lock_table Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.PROJECT_BUSY_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'prj_project_pkg',
                                                      p_procedure_function_name => 'project_get_record');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure project_close(p_project_id Number, p_user_id Number) Is
    v_record prj_project%Rowtype;
    e_close_status_error Exception;
    v_credit_used Number;
  Begin
    v_record := project_get_record(p_project_id => p_project_id,
                                   p_user_id    => p_user_id);
    If v_record.project_status <> 'NEW' And
       v_record.project_status <> 'APPROVED_RETURN' Then
      Raise e_close_status_error;
    End If;
    Update prj_project t
       Set t.project_status   = 'CLOSED',
           t.last_update_date = Sysdate,
           t.last_updated_by  = p_user_id
     Where t.project_id = p_project_id;
    /*select t.hd_user_col_n03
     into v_credit_used
     from hls_fin_calculator_hd t, prj_quotation pq
    where pq.calc_session_id = t.calc_session_id
      and pq.document_id = p_project_id;
    for v_record in (select t.*
                      from prj_project_bp t
                     where t.project_id = p_project_id
                       and t.bp_category in ('TENANT', 'GUARANTOR')) loop
      hls_bp_credit_pkg.credit_release(p_bp_id  => v_record.bp_id,
                          p_credit_date    =>sysdate,
                          p_credit_amount  =>v_credit_used ,
                          p_source_type    =>'PROJECT_CLOSE',
                          p_source_id     => p_project_id,            
                          p_user_id        => p_user_id) ;
      end loop;*/
    hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => v_record.document_category,
                                                           p_document_id       => v_record.project_id,
                                                           p_operation_code    => hls_doc_operate_history_pkg.c_close,
                                                           p_user_id           => p_user_id,
                                                           p_operation_time    => Sysdate,
                                                           p_description       => Null);
  Exception
    When e_close_status_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.PROJECT_CLOSE_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'prj_project_pkg',
                                                      p_procedure_function_name => 'project_close');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure update_prj_dealcre_flag(p_project_id   Number,
                                    p_declare_flag Varchar2,
                                    p_user_id      Number) Is
    v_prj_record prj_project%Rowtype;
    e_status_error Exception;
  Begin
    v_prj_record := project_get_record(p_project_id => p_project_id,
                                       p_user_id    => p_user_id);
    If v_prj_record.project_status = 'NEW' Then
      Update prj_project t
         Set t.declare_flag     = nvl(p_declare_flag, 'N'),
             t.last_updated_by  = p_user_id,
             t.last_update_date = Sysdate
       Where t.project_id = p_project_id;
    Else
      Raise e_status_error;
    End If;
  Exception
    When e_status_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.PRJ_DECLARE_FLAG_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'prj_project_pkg',
                                                      p_procedure_function_name => 'update_prj_dealcre_flag');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure update_seq_after_modify(p_project_id  Number,
                                    p_table_name  Varchar2,
                                    p_bp_category Varchar2,
                                    p_user_id     Number) Is
    v_max_seq   Number;
    v_prj_bp_id Number;
  Begin
    If p_table_name = 'PB' Then
      Select nvl(Max(b.bp_seq), 0)
        Into v_max_seq
        From prj_project_bp b
       Where b.project_id = p_project_id
         And b.bp_category = p_bp_category;
      For i In 1 .. v_max_seq Loop
        Begin
          Select d.prj_bp_id
            Into v_prj_bp_id
            From prj_project_bp d
           Where d.project_id = p_project_id
             And d.bp_category = p_bp_category
             And d.bp_seq = i;
        Exception
          When no_data_found Then
            Update prj_project_bp b
               Set b.bp_seq           = i,
                   b.last_update_date = Sysdate,
                   b.last_updated_by  = p_user_id
             Where b.project_id = p_project_id
               And b.bp_category = p_bp_category
               And b.bp_seq = (Select Min(d.bp_seq)
                                 From prj_project_bp d
                                Where d.project_id = p_project_id
                                  And b.bp_category = p_bp_category
                                  And d.bp_seq > i);
        End;
      End Loop;
    Elsif p_table_name = 'PAH' Then
      Select nvl(Max(b.bp_seq), 0)
        Into v_max_seq
        From prj_project_act_ctrler_hd b
       Where b.project_id = p_project_id;
      For i In 1 .. v_max_seq Loop
        Begin
          Select d.prj_bp_id
            Into v_prj_bp_id
            From prj_project_act_ctrler_hd d
           Where d.project_id = p_project_id
             And d.bp_seq = i;
        Exception
          When no_data_found Then
            Update prj_project_act_ctrler_hd b
               Set b.bp_seq           = i,
                   b.last_update_date = Sysdate,
                   b.last_updated_by  = p_user_id
             Where b.project_id = p_project_id
               And b.bp_seq = (Select Min(d.bp_seq)
                                 From prj_project_act_ctrler_hd d
                                Where d.project_id = p_project_id
                                  And d.bp_seq > i);
        End;
      End Loop;
    End If;
  End;

  Procedure update_prj_bp_count_parameter(p_project_id                  Number,
                                          p_number_of_tenant            Number,
                                          p_number_of_guarantor         Number,
                                          p_number_of_actual_controller Number,
                                          p_user_id                     Number) Is
    v_prj_record prj_project%Rowtype;
    e_status_error Exception;
  Begin
    v_prj_record := project_get_record(p_project_id => p_project_id,
                                       p_user_id    => p_user_id);
    If v_prj_record.project_status In
       ('APPROVED', 'APPROVED_RETURN', 'CONDITIONAL', 'NEW') Then
      Update prj_project p
         Set p.number_of_tenant            = p_number_of_tenant,
             p.number_of_guarantor         = p_number_of_guarantor,
             p.number_of_actual_controller = p_number_of_actual_controller,
             p.last_updated_by             = p_user_id,
             p.last_update_date            = Sysdate
       Where p.project_id = p_project_id;
      update_seq_after_modify(p_project_id  => p_project_id,
                              p_table_name  => 'PB',
                              p_bp_category => 'TENANT',
                              p_user_id     => p_user_id);
      update_seq_after_modify(p_project_id  => p_project_id,
                              p_table_name  => 'PB',
                              p_bp_category => 'GUARANTOR',
                              p_user_id     => p_user_id);
      update_seq_after_modify(p_project_id  => p_project_id,
                              p_table_name  => 'PAH',
                              p_bp_category => '',
                              p_user_id     => p_user_id);
      Delete From prj_project_bp pb
       Where pb.project_id = p_project_id
         And pb.bp_category = 'TENANT'
         And pb.bp_seq > p_number_of_tenant;
      Delete From prj_project_bp pb
       Where pb.project_id = p_project_id
         And pb.bp_category = 'GUARANTOR'
         And pb.bp_seq > p_number_of_guarantor;
      Delete From prj_project_act_ctrler_hd h
       Where h.project_id = p_project_id
         And h.bp_seq > p_number_of_actual_controller;
    Else
      Raise e_status_error;
    End If;
  Exception
    When e_status_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.UPDATE_PRJ_BP_COUNT_PARAMETER_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'prj_project_pkg',
                                                      p_procedure_function_name => 'update_prj_bp_count_parameter');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure delete_prj_bp_count_parameter(p_project_id  Number,
                                          p_bp_category Varchar2,
                                          p_bp_class    Varchar2,
                                          p_bp_seq      Number,
                                          p_table_name  Varchar2,
                                          p_user_id     Number) Is
    v_prj_record prj_project%Rowtype;
    e_status_error Exception;
  Begin
    v_prj_record := project_get_record(p_project_id => p_project_id,
                                       p_user_id    => p_user_id);
    If v_prj_record.project_status = 'NEW' Or
       v_prj_record.project_status = 'APPROVED' Then
      If p_table_name = 'PB' And
         (p_bp_category = 'TENANT' Or p_bp_category = 'TENANT_SEC') Then
        Delete From prj_project_bp pb
         Where pb.project_id = p_project_id
           And pb.bp_category = p_bp_category
           And pb.bp_seq = p_bp_seq;
        Update prj_project p
           Set p.number_of_tenant = p.number_of_tenant - 1,
               p.last_update_date = Sysdate,
               p.last_updated_by  = p_user_id
         Where p.project_id = p_project_id;
      Else
        If p_table_name = 'PB' And p_bp_category = 'GUARANTOR' And
           p_bp_class = 'ORG' Then
          Delete From prj_project_bp pb
           Where pb.project_id = p_project_id
             And pb.bp_category = p_bp_category
             And pb.bp_seq = p_bp_seq;
          Update prj_project p
             Set p.number_of_guarantor = p.number_of_guarantor - 1,
                 p.last_update_date    = Sysdate,
                 p.last_updated_by     = p_user_id
           Where p.project_id = p_project_id;
        Else
          If p_table_name = 'PAH' And p_bp_category = 'GUARANTOR' And
             p_bp_class = 'NP' Then
            Delete From prj_project_act_ctrler_hd pah
             Where pah.project_id = p_project_id
               And pah.bp_seq = p_bp_seq;
            Update prj_project p
               Set p.number_of_actual_controller = p.number_of_actual_controller - 1,
                   p.last_update_date            = Sysdate,
                   p.last_updated_by             = p_user_id
             Where p.project_id = p_project_id;
          End If;
        End If;
      End If;
      update_seq_after_modify(p_project_id  => p_project_id,
                              p_table_name  => p_table_name,
                              p_bp_category => p_bp_category,
                              p_user_id     => p_user_id);
    Else
      Raise e_status_error;
    End If;
  Exception
    When e_status_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.UPDATE_PRJ_BP_COUNT_PARAMETER_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'prj_project_pkg',
                                                      p_procedure_function_name => 'delete_prj_bp_count_parameter');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure update_prj_project_status(p_project_id Number,
                                      p_user_id    Number) Is
    v_prj_record prj_project%Rowtype;
  Begin
    v_prj_record := project_get_record(p_project_id => p_project_id,
                                       p_user_id    => p_user_id);
    --remove by chenlingfeng for ds 2018-7-17 16:09:15
    -- IF v_prj_record.project_status = 'APPROVED' THEN
    Update prj_project p
       Set p.project_status   = 'CONTRACT_CREATED',
           p.last_updated_by  = p_user_id,
           p.last_update_date = Sysdate
     Where p.project_id = p_project_id;
    --   END IF;
  End;

  Procedure project_print(p_project_id Number, p_user_id Number) Is
    v_prj_record prj_project%Rowtype;
    e_status_error Exception;
  Begin
    v_prj_record := project_get_record(p_project_id => p_project_id,
                                       p_user_id    => p_user_id);
    If v_prj_record.project_status In ('REJECT', 'CLOSE') Then
      Raise e_status_error;
    End If;
    Update prj_project p
       Set p.prj_print_status = 'PRINTED',
           p.prj_print_times  = nvl(p.prj_print_times, 0) + 1,
           p.last_update_date = Sysdate,
           p.last_updated_by  = p_user_id
     Where p.project_id = p_project_id;
    hls_doc_operate_history_pkg.insert_doc_operate_history(p_document_category => 'PROJECT',
                                                           p_document_id       => p_project_id,
                                                           p_operation_code    => 'PRJ_PRINT',
                                                           p_user_id           => p_user_id,
                                                           p_operation_time    => Sysdate,
                                                           p_description       => Null);
  Exception
    When e_status_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.PROJECT_PRINT_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'prj_project_pkg',
                                                      p_procedure_function_name => 'project_print');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  --校验项目的主报价融资金额总和是否小于辅助报价融资金额
  Procedure check_quotation_amount(p_project_id Number, p_user_id Number) Is
    v_major_finance_amount     Number;
    v_secondary_finance_amount Number;
    e_amount_error Exception;
  Begin
    Select Sum(q.finance_amount)
      Into v_major_finance_amount
      From prj_quotation q
     Where q.document_category = 'PROJECT'
       And q.document_id = p_project_id
       And q.quotation_type = 'MAJOR'
       And q.enabled_flag = 'Y';
    Select Sum(q.finance_amount)
      Into v_secondary_finance_amount
      From prj_quotation q
     Where q.document_category = 'PROJECT'
       And q.document_id = p_project_id
       And q.quotation_type = 'SECONDARY'
       And q.enabled_flag = 'Y';
    --如果主报价金额大于辅助报价金额则报错
    If v_major_finance_amount Is Null Then
      Return;
    Elsif v_major_finance_amount > v_secondary_finance_amount Then
      Raise e_amount_error;
    End If;
  Exception
    When e_amount_error Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.FINANCE_AMOUNT_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'prj_project_pkg',
                                                      p_procedure_function_name => 'check_quotation_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            => 'prj_project_pkg',
                                                     p_procedure_function_name => 'check_quotation_amount');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End check_quotation_amount;

  --发送初评结论邮件
  Procedure send_approval_email(p_rcpt_record_id Number,
                                p_project_id     Number,
                                p_user_id        Number) Is
    v_parameter_list     zj_sys_notify_core_pkg.notify_parameter_list;
    v_result             Number;
    v_notify_template_id Number;
    v_pre_assment        Varchar2(4000); --初评意见
    v_project            prj_project%Rowtype;
  Begin
    Select p.*
      Into v_project
      From prj_project p
     Where p.project_id = p_project_id;
    Select r.comment_text
      Into v_pre_assment
      From zj_wfl_approve_record r
     Where r.rcpt_record_id = p_rcpt_record_id;
    Select t.notify_template_id
      Into v_notify_template_id
      From zj_sys_notify_template t
     Where t.notify_template_code = 'MAIL_PRE';
    zj_sys_notify_core_pkg.add_parameter_list(v_parameter_list,
                                              '&PROJECT_NUMBER&',
                                              v_project.project_number);
    zj_sys_notify_core_pkg.add_parameter_list(v_parameter_list,
                                              '&PROJECT_NAME&',
                                              v_project.project_name);
    zj_sys_notify_core_pkg.add_parameter_list(v_parameter_list,
                                              '&DATE&',
                                              to_char(Sysdate,
                                                      'yyyy"年"mm"月"dd"日"'));
    For c_record In (Select e.name, e.email
                       From exp_org_position     p,
                            exp_employee_assigns v,
                            exp_employees        e,
                            exp_employee_assigns a,
                            sys_user             u
                      Where u.employee_id = v.employee_id
                        And u.user_id = v_project.owner_user_id
                        And v.primary_position_flag = 'Y'
                        And v.position_id = p.position_id
                        And p.parent_position_id = a.position_id
                        And a.employee_id = e.employee_id
                     Union
                     Select ee.name, ee.email
                       From exp_employees ee, sys_user u
                      Where ee.employee_id = u.employee_id
                        And u.user_id = v_project.owner_user_id
                     Union
                     Select ee.name, ee.email
                       From exp_employees ee, exp_employee_assigns_v av
                      Where ee.employee_id = av.employee_id
                        And av.position_code In ('05', '07', '03', '02')) Loop
      zj_sys_notify_core_pkg.add_parameter_list(v_parameter_list,
                                                '&EMPLOYEE_NAME&',
                                                c_record.name);
      v_result := zj_sys_notify_core_pkg.create_mail_message(p_notify_template_id    => v_notify_template_id,
                                                             p_content_type          => 'text/html',
                                                             p_mail_to               => c_record.email,
                                                             p_mail_cc               => '',
                                                             p_notify_parameter_list => v_parameter_list,
                                                             p_user_id               => p_user_id);
    End Loop;
  End send_approval_email;

  --发送终评结论邮件
  Procedure send_approval_last_email(p_approval_id Number,
                                     p_project_id  Number,
                                     p_user_id     Number) Is
    v_project                  prj_project%Rowtype;
    v_approver_comment_summary Varchar2(3000);
    v_notify_template_id       Number;
    v_parameter_list           zj_sys_notify_core_pkg.notify_parameter_list;
    v_result                   Number;
  Begin
    Select *
      Into v_project
      From prj_project p
     Where p.project_id = p_project_id;
    Select a.approver_comment_summary
      Into v_approver_comment_summary
      From prj_project_approval a
     Where a.approval_id = p_approval_id;
    Select t.notify_template_id
      Into v_notify_template_id
      From zj_sys_notify_template t
     Where t.notify_template_code = 'MAIL_FINAL';
    zj_sys_notify_core_pkg.add_parameter_list(v_parameter_list,
                                              '&PROJECT_NUMBER&',
                                              v_project.project_number);
    zj_sys_notify_core_pkg.add_parameter_list(v_parameter_list,
                                              '&PROJECT_NAME&',
                                              v_project.project_name);
    zj_sys_notify_core_pkg.add_parameter_list(v_parameter_list,
                                              '&DATE&',
                                              to_char(Sysdate,
                                                      'yyyy"年"mm"月"dd"日"'));
    zj_sys_notify_core_pkg.add_parameter_list(v_parameter_list,
                                              '&FINAL_ASSMENT&',
                                              v_approver_comment_summary);
    For c_record In (Select e.name, e.email
                       From exp_org_position     p,
                            exp_employee_assigns v,
                            exp_employees        e,
                            exp_employee_assigns a,
                            sys_user             u
                      Where u.employee_id = v.employee_id
                        And u.user_id = v_project.owner_user_id
                        And v.primary_position_flag = 'Y'
                        And v.position_id = p.position_id
                        And p.parent_position_id = a.position_id
                        And a.employee_id = e.employee_id
                     Union
                     Select ee.name, ee.email
                       From exp_employees ee, sys_user u
                      Where ee.employee_id = u.employee_id
                        And u.user_id = v_project.owner_user_id
                     Union
                     Select ee.name, ee.email
                       From exp_employees ee, exp_employee_assigns_v av
                      Where ee.employee_id = av.employee_id
                        And av.position_code In
                            ('01', '02', '03', '04', '05', '07', '15', '19')) Loop
      zj_sys_notify_core_pkg.add_parameter_list(v_parameter_list,
                                                '&EMPLOYEE_NAME&',
                                                c_record.name);
      v_result := zj_sys_notify_core_pkg.create_mail_message(p_notify_template_id    => v_notify_template_id,
                                                             p_content_type          => 'text/html',
                                                             p_mail_to               => c_record.email,
                                                             p_mail_cc               => '',
                                                             p_notify_parameter_list => v_parameter_list,
                                                             p_user_id               => p_user_id);
    End Loop;
  End;

  Function get_project_record(p_project_id Number, p_user_id Number)
    Return prj_project%Rowtype Is
    v_record prj_project%Rowtype;
  Begin
    Select *
      Into v_record
      From prj_project v
     Where v.project_id = p_project_id
       For Update Nowait;
    Return v_record;
  Exception
    When e_lock_table Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.PRJ_PROJECT_LOCK_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'prj_project_pkg',
                                                      p_procedure_function_name => 'get_project_record');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  --项目创建是插权限表
  Procedure project_create_user_authority(p_project_id Number,
                                          p_user_id    Number) Is
    r_project_rec prj_project%Rowtype;
  Begin
    r_project_rec := get_project_record(p_project_id => p_project_id,
                                        p_user_id    => p_user_id);
    aut_document_authority_pkg.insert_trx_user_authority(p_company_id    => r_project_rec.company_id,
                                                         p_owner_user_id => nvl(r_project_rec.owner_user_id,
                                                                                p_user_id),
                                                         p_trx_category  => 'PROJECT',
                                                         p_trx_id        => p_project_id,
                                                         p_start_date    => trunc(Sysdate),
                                                         p_end_date      => to_date('30000101',
                                                                                    'yyyymmdd'),
                                                         p_user_id       => p_user_id);
  End;

  --创建cdd
  Procedure save_cdd_item_from_templt(p_document_category Varchar2,
                                      p_document_type     Varchar2,
                                      p_document_id       Number,
                                      p_company_id        Number,
                                      p_function_code     Varchar2,
                                      p_function_usage    Varchar2,
                                      p_user_id           Number,
                                      p_cdd_list_id       In Out Number) Is
    v_project_record    prj_project%Rowtype;
    v_cdd_item_id       Number;
    v_templet_head_code Varchar2(1000);
    v_templet_head_id   Number;
    v_cond_para1        Varchar2(255) := 'NEW';
    v_cond_para2        Varchar2(255);
    v_cond_para3        Varchar2(255);
    v_cond_para4        Varchar2(255);
    v_cond_para5        Varchar2(255);
  Begin
    v_project_record := get_project_record(p_project_id => p_document_id,
                                           p_user_id    => p_user_id);
    sys_condition_cdd_pkg.matching_condition(p_condition_code    => 'CDD00',
                                             p_company_id        => v_project_record.company_id,
                                             p_role_id           => '',
                                             p_user_id           => p_user_id,
                                             p_function_code     => '',
                                             p_currency          => '',
                                             p_lease_org         => '',
                                             p_lease_channel     => '',
                                             p_division          => v_project_record.division,
                                             p_business_type     => '',
                                             p_document_category => '',
                                             p_document_type     => '',
                                             p_bp_class          => v_project_record.bp_class,
                                             p_cond_para1        => v_cond_para1,
                                             p_cond_para2        => v_cond_para2,
                                             p_cond_para3        => v_cond_para3,
                                             p_cond_para4        => v_cond_para4,
                                             p_cond_para5        => v_cond_para5,
                                             p_cdd_code          => v_templet_head_code);
    Begin
      Select h.templet_head_id
        Into v_templet_head_id
        From prj_cdd_item_templet_hd h
       Where h.templet_code = v_templet_head_code
         And h.enabled_flag = 'Y';
      prj_cdd_item_pkg.create_cdd_item_from_templet(p_templet_head_id => v_templet_head_id,
                                                    p_cdd_list_id     => p_cdd_list_id,
                                                    p_user_id         => p_user_id);
    Exception
      When no_data_found Then
        Null;
    End;
  End;

  --项目不经过商机创建时,创建cdd
  Procedure create_cdd(p_document_type  Varchar2,
                       p_project_id     Number,
                       p_company_id     Number,
                       p_function_code  Varchar2,
                       p_function_usage Varchar2,
                       p_user_id        Number,
                       p_cdd_list_id    In Out Number) Is
    v_project_record prj_project%Rowtype;
  Begin
    v_project_record := get_project_record(p_project_id => p_project_id,
                                           p_user_id    => p_user_id);
    If p_cdd_list_id Is Null Then
      save_cdd_item_from_templt(p_document_category => 'PROJECT',
                                p_document_type     => p_document_type,
                                p_document_id       => p_project_id,
                                p_company_id        => p_company_id,
                                p_function_code     => p_function_code,
                                p_function_usage    => p_function_usage,
                                p_user_id           => p_user_id,
                                p_cdd_list_id       => p_cdd_list_id);
      Update prj_project d
         Set d.cdd_list_id      = p_cdd_list_id,
             d.last_updated_by  = p_user_id,
             d.last_update_date = Sysdate
       Where d.project_id = p_project_id
         And d.cdd_list_id Is Null;
    End If;
  End;

  --项目创建保存时校验
  Procedure project_create_save_check(p_project_id       Number,
                                      p_user_id          Number,
                                      p_function_code    Varchar2,
                                      p_function_usage   Varchar2,
                                      p_save_source_type Varchar2 Default Null,
                                      p_project_status   Varchar2 Default Null,
                                      p_cdd_list_id      Out Number) Is
    --  PRAGMA AUTONOMOUS_TRANSACTION;                                   
    v_project_record     prj_project%Rowtype;
    v_project_bp_record  prj_project_bp%Rowtype;
    v_project_status     Varchar2(200);
    v_wfl_instance_id    Number;
    v_creation_date      Date;
    v_status             Varchar2(100);
    v_project_rec        prj_project%Rowtype;
    v_bp                 hls_bp_master%Rowtype;
     v_bp_tenant         hls_bp_master%Rowtype;
    v_document_type      Varchar2(100);
    v_lease_organization Varchar2(100);
    e_project_create_save_err Exception;
    e_check_stauts_err        Exception;
    e_project_status_err      Exception;
  Begin
    v_project_record := get_project_record(p_project_id => p_project_id,
                                           p_user_id    => p_user_id);
    If p_project_status = 'NEW' And v_project_record.back_flag != 'Y' And
       v_project_record.wfl_instance_id Is Not Null Then
      Raise e_project_status_err;
    End If;
    Select *
      Into v_project_rec
      From prj_project pp
     Where pp.project_id = p_project_id;
    Select pp.project_status, pp.wfl_instance_id
      Into v_project_status, v_wfl_instance_id
      From prj_project pp
     Where pp.project_id = p_project_id;
    /*Begin
      Select zw.status
        Into v_status
        From zj_wfl_workflow_instance zw
       Where zw.instance_id = v_wfl_instance_id
         And p_save_source_type != 'WFL';
    
      If v_status = 1 Then
        Raise e_check_stauts_err;
      End If;
    Exception
      When no_data_found Then
        Null;
    End;*/
    If (v_project_status <> 'APPROVING' And p_save_source_type = 'WFL') Or
       (p_save_source_type != 'WFL' And
       v_project_status Not In ('NEW', 'REJECT', 'CANCEL', 'CLOSED')) Then
      Raise e_project_create_save_err;
    End If;
    If (v_project_record.owner_user_id Is Null) Then
      Update prj_project p
         Set p.owner_user_id = p_user_id
       Where p.project_id = p_project_id;
    End If;
    If v_project_record.bp_id_tenant Is Null Then
      Update prj_project t
         Set t.bp_id_tenant =
             (Select a.bp_id
                From prj_project_bp a
               Where a.project_id = p_project_id
                 And a.bp_category = 'TENANT')
       Where t.project_id = p_project_id;
    End If;
    --项目创建是插权限表
    project_create_user_authority(p_project_id => p_project_id,
                                  p_user_id    => p_user_id);
    p_cdd_list_id := v_project_record.cdd_list_id;
    --项目经过商机创建时,创建cdd
    create_cdd(p_document_type  => v_project_record.document_type,
               p_project_id     => v_project_record.project_id,
               p_company_id     => v_project_record.company_id,
               p_function_code  => p_function_code,
               p_function_usage => p_function_usage,
               p_user_id        => p_user_id,
               p_cdd_list_id    => p_cdd_list_id);
    Select pp.document_type
      Into v_document_type
      From prj_project pp
     Where pp.project_id = p_project_id;
    Begin
    
      Select *
        Into v_bp
        From hls_bp_master hbm
       Where hbm.bp_id = v_project_record.invoice_agent_id;
       
     
    
      Delete From prj_project_bp pb
       Where pb.project_id = p_project_id
         And pb.bp_category = 'AGENT';
      Insert Into prj_project_bp pb
        (prj_bp_id,
         project_id,
         bp_category,
         bp_id,
         contract_seq,
         bp_seq,
         creat_bp_flg,
         bp_code,
         bp_type,
         bp_class,
         bp_name,
         extra_nam,
         external_bp_code,
         id_type,
         id_card_no,
         gender,
         age,
         date_of_birth,
         place_of_birth,
         liv_province,
         liv_city,
         liv_district,
         liv_street,
         living_address,
         add_city,
         add_province,
         resident_addres,
         resident_status,
         state_of_health,
         academic_background,
         marital_status,
         ethnic_group,
         fertility_status,
         number_of_children,
         contact_person,
         position,
         phone,
         phone_2,
         cell_phone,
         fax,
         zipcode,
         email,
         cell_phone_2,
         website,
         e_mail,
         qq,
         wei_chat,
         source_of_income,
         annual_income,
         other_annual_income,
         other_asset,
         capital_of_family,
         liability_of_family,
         guarantee_amount,
         ownship_of_house,
         house_loans_flag,
         send_address,
         work_type,
         work_unit,
         work_province,
         work_city,
         work_unit_address,
         work_unit_phone,
         work_unit_zip,
         company_nature,
         industry_work_exper,
         main_driver_of_car,
         driver_license_no,
         living_situation,
         source_of_income_db,
         release_form,
         business_license_num,
         organization_code,
         tax_registry_num,
         loan_card_num,
         taxpayer_type,
         corporate_code,
         registered_place,
         founded_date,
         registered_capital,
         paid_up_capital,
         currency,
         legal_form,
         industry,
         enterprise_scale,
         shareholders_background,
         foa_rate,
         porportion_of_guarantee,
         earnings_condition,
         main_business_growth,
         roe,
         current_ratio,
         interest_cover_ratio,
         debt_to_asset_ratio,
         bp_name_sp,
         card_type_sp,
         id_no_sp,
         gender_sp,
         date_of_birth_sp,
         age_sp,
         cell_phone_sp,
         academic_background_sp,
         living_add_city,
         living_add_province,
         living_address_sp,
         resident_add_province,
         resident_add_city,
         resident_addres_sp,
         work_type_sp,
         industry_sp,
         industry_work_experience_sp,
         work_unit_name_sp,
         work_unit_phone_sp,
         work_province_sp,
         work_city_sp,
         work_unit_address_sp,
         position_sp,
         source_of_income_sp,
         annual_income_sp,
         other_annual_income_sp,
         relationship_sp,
         bank_id,
         bank_account_name,
         bank_account_num,
         invoice_bp_address_phone_num,
         invoice_bp_bank_account,
         invoice_title,
         invoice_send_address,
         bp_name_leg,
         id_card_no_leg,
         gender_leg,
         date_of_birth_leg,
         age_leg,
         marital_status_leg,
         academic_background_leg,
         years_of_living_house_leg,
         resident_status_leg,
         cell_phone_leg,
         email_leg,
         start_work_leg,
         liv_province_leg,
         liv_city_leg,
         liv_district_leg,
         liv_street_leg,
         net_monthly_income_leg,
         ownership_of_house_leg,
         other_asset_leg,
         capital_of_family_leg,
         liability_of_family_leg,
         agent_type,
         created_by,
         creation_date,
         last_updated_by,
         last_update_date,
         ref_v01,
         ref_v02,
         ref_v03,
         ref_v04,
         ref_v05,
         ref_v06,
         ref_n01,
         ref_n02,
         ref_n03,
         ref_n04,
         ref_n05,
         ref_n06,
         ref_n07,
         ref_n10,
         ref_d01,
         ref_d02,
         ref_d03,
         ref_d04,
         ref_d05,
         net_monthly_income,
         years_of_living_house,
         total_assets,
         company_id,
         work_district,
         wokr_street,
         name_legal,
         work_street,
         id_type_leg,
         net_monthly_income_gua,
         registered_capital_org,
         bp_property,
         credit_bank,
         driving_flag,
         driving_provement
         /*number_of_car,
         manage_of_car,
         number_of_cus,
         period_of_year,
         asset_size,
         debt,
         revenue,
         net_profit,
         land_situation,
         mortgage_guarantee,
         estimation_lease,
         guarantee_of_repayment,
         risk_measure,
         credit_record,
         criminal_record,
         executed_asset*/)
      Values
        (prj_project_bp_s.nextval,
         p_project_id,
         'AGENT',
         v_bp.bp_id,
         Null,
         Null,
         Null,
         v_bp.bp_code,
         v_bp.bp_type,
         v_bp.bp_class,
         v_bp.bp_name,
         v_bp.extra_nam,
         v_bp.external_bp_code,
         v_bp.id_type,
         v_bp.id_card_no,
         v_bp.gender,
         v_bp.age,
         v_bp.date_of_birth,
         v_bp.place_of_birth,
         v_bp.liv_province,
         v_bp.liv_city,
         v_bp.liv_district,
         v_bp.liv_street,
         v_bp.living_address,
         v_bp.add_city,
         v_bp.add_province,
         v_bp.resident_addres,
         v_bp.resident_status,
         v_bp.state_of_health,
         v_bp.academic_background,
         v_bp.marital_status,
         v_bp.ethnic_group,
         v_bp.fertility_status,
         v_bp.number_of_children,
         v_bp.contact_person,
         v_bp.position,
         v_bp.phone,
         v_bp.phone_2,
         v_bp.cell_phone,
         v_bp.fax,
         v_bp.zipcode,
         v_bp.email,
         v_bp.cell_phone_2,
         v_bp.website,
         v_bp.e_mail,
         v_bp.qq,
         v_bp.wei_chat,
         v_bp.source_of_income,
         v_bp.annual_income,
         v_bp.other_annual_income,
         v_bp.other_asset,
         v_bp.capital_of_family,
         v_bp.liability_of_family,
         v_bp.guarantee_amount,
         v_bp.ownship_of_house,
         v_bp.house_loans_flag,
         v_bp.send_address,
         v_bp.work_type,
         v_bp.work_unit,
         v_bp.work_province,
         v_bp.work_city,
         v_bp.work_unit_address,
         v_bp.work_unit_phone,
         v_bp.work_unit_zip,
         v_bp.company_nature,
         v_bp.industry_work_exper,
         v_bp.main_driver_of_car,
         v_bp.driver_license_no,
         v_bp.living_situation,
         v_bp.source_of_income_db,
         v_bp.release_form,
         v_bp.business_license_num,
         v_bp.organization_code,
         v_bp.tax_registry_num,
         v_bp.loan_card_num,
         v_bp.taxpayer_type,
         v_bp.corporate_code,
         v_bp.registered_place,
         v_bp.founded_date,
         v_bp.registered_capital,
         v_bp.paid_up_capital,
         v_bp.currency,
         v_bp.legal_form,
         v_bp.industry,
         v_bp.enterprise_scale,
         v_bp.shareholders_background,
         v_bp.foa_rate,
         v_bp.porportion_of_guarantee,
         v_bp.earnings_condition,
         v_bp.main_business_growth,
         v_bp.roe,
         v_bp.current_ratio,
         v_bp.interest_cover_ratio,
         v_bp.debt_to_asset_ratio,
         v_bp.bp_name_sp,
         v_bp.card_type_sp,
         v_bp.id_no_sp,
         v_bp.gender_sp,
         v_bp.date_of_birth_sp,
         v_bp.age_sp,
         v_bp.cell_phone_sp,
         v_bp.academic_background_sp,
         v_bp.living_add_city,
         v_bp.living_add_province,
         v_bp.living_address_sp,
         v_bp.resident_add_province,
         v_bp.resident_add_city,
         v_bp.resident_addres_sp,
         v_bp.work_type_sp,
         v_bp.industry_sp,
         v_bp.industry_work_experience_sp,
         v_bp.work_unit_name_sp,
         v_bp.work_unit_phone_sp,
         v_bp.work_province_sp,
         v_bp.work_city_sp,
         v_bp.work_unit_address_sp,
         v_bp.position_sp,
         v_bp.source_of_income_sp,
         v_bp.annual_income_sp,
         v_bp.other_annual_income_sp,
         v_bp.relationship_sp,
         v_bp.bank_id,
         v_bp.bank_account_name,
         v_bp.bank_account_num,
         v_bp.invoice_bp_address_phone_num,
         v_bp.invoice_bp_bank_account,
         v_bp.invoice_title,
         v_bp.invoice_send_address,
         v_bp.bp_name_leg,
         v_bp.id_card_no_leg,
         v_bp.gender_leg,
         v_bp.date_of_birth_leg,
         v_bp.age_leg,
         v_bp.marital_status_leg,
         v_bp.academic_background_leg,
         v_bp.years_of_living_house_leg,
         v_bp.resident_status_leg,
         v_bp.cell_phone_leg,
         v_bp.email_leg,
         v_bp.start_work_leg,
         v_bp.liv_province_leg,
         v_bp.liv_city_leg,
         v_bp.liv_district_leg,
         v_bp.liv_street_leg,
         v_bp.net_monthly_income_leg,
         v_bp.ownership_of_house_leg,
         v_bp.other_asset_leg,
         v_bp.capital_of_family_leg,
         v_bp.liability_of_family_leg,
         v_bp.agent_type,
         1,
         Sysdate,
         1,
         Sysdate,
         v_bp.ref_v01,
         v_bp.ref_v02,
         v_bp.ref_v03,
         v_bp.ref_v04,
         v_bp.ref_v05,
         v_bp.ref_v06,
         v_bp.ref_n01,
         v_bp.ref_n02,
         v_bp.ref_n03,
         v_bp.ref_n04,
         v_bp.ref_n05,
         v_bp.ref_n06,
         v_bp.ref_n07,
         v_bp.ref_n10,
         v_bp.ref_d01,
         v_bp.ref_d02,
         v_bp.ref_d03,
         v_bp.ref_d04,
         v_bp.ref_d05,
         v_bp.net_monthly_income,
         v_bp.years_of_living_house,
         v_bp.total_assets,
         v_bp.company_id,
         v_bp.work_district,
         v_bp.wokr_street,
         v_bp.name_legal,
         v_bp.work_street,
         Null,
         v_bp.net_monthly_income_gua,
         v_bp.registered_capital_org,
         Null,
         Null,
         Null,
         Null);
        
    Select *
        Into v_bp_tenant
        From hls_bp_master hbm
       Where hbm.bp_id = v_project_record.bp_id_tenant;
       
        Delete From prj_project_bp pb
       Where pb.project_id = p_project_id
         And pb.bp_category = 'TENANT';
      Insert Into prj_project_bp pb
        (prj_bp_id,
         project_id,
         bp_category,
         bp_id,
         contract_seq,
         bp_seq,
         creat_bp_flg,
         bp_code,
         bp_type,
         bp_class,
         bp_name,
         extra_nam,
         external_bp_code,
         id_type,
         id_card_no,
         gender,
         age,
         date_of_birth,
         place_of_birth,
         liv_province,
         liv_city,
         liv_district,
         liv_street,
         living_address,
         add_city,
         add_province,
         resident_addres,
         resident_status,
         state_of_health,
         academic_background,
         marital_status,
         ethnic_group,
         fertility_status,
         number_of_children,
         contact_person,
         position,
         phone,
         phone_2,
         cell_phone,
         fax,
         zipcode,
         email,
         cell_phone_2,
         website,
         e_mail,
         qq,
         wei_chat,
         source_of_income,
         annual_income,
         other_annual_income,
         other_asset,
         capital_of_family,
         liability_of_family,
         guarantee_amount,
         ownship_of_house,
         house_loans_flag,
         send_address,
         work_type,
         work_unit,
         work_province,
         work_city,
         work_unit_address,
         work_unit_phone,
         work_unit_zip,
         company_nature,
         industry_work_exper,
         main_driver_of_car,
         driver_license_no,
         living_situation,
         source_of_income_db,
         release_form,
         business_license_num,
         organization_code,
         tax_registry_num,
         loan_card_num,
         taxpayer_type,
         corporate_code,
         registered_place,
         founded_date,
         registered_capital,
         paid_up_capital,
         currency,
         legal_form,
         industry,
         enterprise_scale,
         shareholders_background,
         foa_rate,
         porportion_of_guarantee,
         earnings_condition,
         main_business_growth,
         roe,
         current_ratio,
         interest_cover_ratio,
         debt_to_asset_ratio,
         bp_name_sp,
         card_type_sp,
         id_no_sp,
         gender_sp,
         date_of_birth_sp,
         age_sp,
         cell_phone_sp,
         academic_background_sp,
         living_add_city,
         living_add_province,
         living_address_sp,
         resident_add_province,
         resident_add_city,
         resident_addres_sp,
         work_type_sp,
         industry_sp,
         industry_work_experience_sp,
         work_unit_name_sp,
         work_unit_phone_sp,
         work_province_sp,
         work_city_sp,
         work_unit_address_sp,
         position_sp,
         source_of_income_sp,
         annual_income_sp,
         other_annual_income_sp,
         relationship_sp,
         bank_id,
         bank_account_name,
         bank_account_num,
         invoice_bp_address_phone_num,
         invoice_bp_bank_account,
         invoice_title,
         invoice_send_address,
         bp_name_leg,
         id_card_no_leg,
         gender_leg,
         date_of_birth_leg,
         age_leg,
         marital_status_leg,
         academic_background_leg,
         years_of_living_house_leg,
         resident_status_leg,
         cell_phone_leg,
         email_leg,
         start_work_leg,
         liv_province_leg,
         liv_city_leg,
         liv_district_leg,
         liv_street_leg,
         net_monthly_income_leg,
         ownership_of_house_leg,
         other_asset_leg,
         capital_of_family_leg,
         liability_of_family_leg,
         agent_type,
         created_by,
         creation_date,
         last_updated_by,
         last_update_date,
         ref_v01,
         ref_v02,
         ref_v03,
         ref_v04,
         ref_v05,
         ref_v06,
         ref_n01,
         ref_n02,
         ref_n03,
         ref_n04,
         ref_n05,
         ref_n06,
         ref_n07,
         ref_n10,
         ref_d01,
         ref_d02,
         ref_d03,
         ref_d04,
         ref_d05,
         net_monthly_income,
         years_of_living_house,
         total_assets,
         company_id,
         work_district,
         wokr_street,
         name_legal,
         work_street,
         id_type_leg,
         net_monthly_income_gua,
         registered_capital_org,
         bp_property,
         credit_bank,
         driving_flag,
         driving_provement
         /*number_of_car,
         manage_of_car,
         number_of_cus,
         period_of_year,
         asset_size,
         debt,
         revenue,
         net_profit,
         land_situation,
         mortgage_guarantee,
         estimation_lease,
         guarantee_of_repayment,
         risk_measure,
         credit_record,
         criminal_record,
         executed_asset*/)
      Values
      (prj_project_bp_s.nextval,
         p_project_id,
         'TENANT',
         v_bp_tenant.Bp_Id,
         Null,
         Null,
         Null,
         v_bp_tenant.bp_code,
         v_bp_tenant.bp_type,
         v_bp_tenant.bp_class,
         v_bp_tenant.bp_name,
         v_bp_tenant.extra_nam,
         v_bp_tenant.external_bp_code,
         v_bp_tenant.id_type,
         v_bp_tenant.id_card_no,
         v_bp_tenant.gender,
         v_bp_tenant.age,
         v_bp_tenant.date_of_birth,
         v_bp_tenant.place_of_birth,
         v_bp_tenant.liv_province,
         v_bp_tenant.liv_city,
         v_bp_tenant.liv_district,
         v_bp_tenant.liv_street,
         v_bp_tenant.living_address,
         v_bp_tenant.add_city,
         v_bp_tenant.add_province,
         v_bp_tenant.resident_addres,
         v_bp_tenant.resident_status,
         v_bp_tenant.state_of_health,
         v_bp_tenant.academic_background,
         v_bp_tenant.marital_status,
         v_bp_tenant.ethnic_group,
         v_bp_tenant.fertility_status,
         v_bp_tenant.number_of_children,
         v_bp_tenant.contact_person,
         v_bp_tenant.position,
         v_bp_tenant.phone,
         v_bp_tenant.phone_2,
         v_bp_tenant.cell_phone,
         v_bp_tenant.fax,
         v_bp_tenant.zipcode,
         v_bp_tenant.email,
         v_bp_tenant.cell_phone_2,
         v_bp_tenant.website,
         v_bp_tenant.e_mail,
         v_bp_tenant.qq,
         v_bp_tenant.wei_chat,
         v_bp_tenant.source_of_income,
         v_bp_tenant.annual_income,
         v_bp_tenant.other_annual_income,
         v_bp_tenant.other_asset,
         v_bp_tenant.capital_of_family,
         v_bp_tenant.liability_of_family,
         v_bp_tenant.guarantee_amount,
         v_bp_tenant.ownship_of_house,
         v_bp_tenant.house_loans_flag,
         v_bp_tenant.send_address,
         v_bp_tenant.work_type,
         v_bp_tenant.work_unit,
         v_bp_tenant.work_province,
         v_bp_tenant.work_city,
         v_bp_tenant.work_unit_address,
         v_bp_tenant.work_unit_phone,
         v_bp_tenant.work_unit_zip,
         v_bp_tenant.company_nature,
         v_bp_tenant.industry_work_exper,
         v_bp_tenant.main_driver_of_car,
         v_bp_tenant.driver_license_no,
         v_bp_tenant.living_situation,
         v_bp_tenant.source_of_income_db,
         v_bp_tenant.release_form,
         v_bp_tenant.business_license_num,
         v_bp_tenant.organization_code,
         v_bp_tenant.tax_registry_num,
         v_bp_tenant.loan_card_num,
         v_bp_tenant.taxpayer_type,
         v_bp_tenant.corporate_code,
         v_bp_tenant.registered_place,
         v_bp_tenant.founded_date,
         v_bp_tenant.registered_capital,
         v_bp_tenant.paid_up_capital,
         v_bp_tenant.currency,
         v_bp_tenant.legal_form,
         v_bp_tenant.industry,
         v_bp_tenant.enterprise_scale,
         v_bp_tenant.shareholders_background,
         v_bp_tenant.foa_rate,
         v_bp_tenant.porportion_of_guarantee,
         v_bp_tenant.earnings_condition,
         v_bp_tenant.main_business_growth,
         v_bp_tenant.roe,
         v_bp_tenant.current_ratio,
         v_bp_tenant.interest_cover_ratio,
         v_bp_tenant.debt_to_asset_ratio,
         v_bp_tenant.bp_name_sp,
         v_bp_tenant.card_type_sp,
         v_bp_tenant.id_no_sp,
         v_bp_tenant.gender_sp,
         v_bp_tenant.date_of_birth_sp,
         v_bp_tenant.age_sp,
         v_bp_tenant.cell_phone_sp,
         v_bp_tenant.academic_background_sp,
         v_bp_tenant.living_add_city,
         v_bp_tenant.living_add_province,
         v_bp_tenant.living_address_sp,
         v_bp_tenant.resident_add_province,
         v_bp_tenant.resident_add_city,
         v_bp_tenant.resident_addres_sp,
         v_bp_tenant.work_type_sp,
         v_bp_tenant.industry_sp,
         v_bp_tenant.industry_work_experience_sp,
         v_bp_tenant.work_unit_name_sp,
         v_bp_tenant.work_unit_phone_sp,
         v_bp_tenant.work_province_sp,
         v_bp_tenant.work_city_sp,
         v_bp_tenant.work_unit_address_sp,
         v_bp_tenant.position_sp,
         v_bp_tenant.source_of_income_sp,
         v_bp_tenant.annual_income_sp,
         v_bp_tenant.other_annual_income_sp,
         v_bp_tenant.relationship_sp,
         v_bp_tenant.bank_id,
         v_bp_tenant.bank_account_name,
         v_bp_tenant.bank_account_num,
         v_bp_tenant.invoice_bp_address_phone_num,
         v_bp_tenant.invoice_bp_bank_account,
         v_bp_tenant.invoice_title,
         v_bp_tenant.invoice_send_address,
         v_bp_tenant.bp_name_leg,
         v_bp_tenant.id_card_no_leg,
         v_bp_tenant.gender_leg,
         v_bp_tenant.date_of_birth_leg,
         v_bp_tenant.age_leg,
         v_bp_tenant.marital_status_leg,
         v_bp_tenant.academic_background_leg,
         v_bp_tenant.years_of_living_house_leg,
         v_bp_tenant.resident_status_leg,
         v_bp_tenant.cell_phone_leg,
         v_bp_tenant.email_leg,
         v_bp_tenant.start_work_leg,
         v_bp_tenant.liv_province_leg,
         v_bp_tenant.liv_city_leg,
         v_bp_tenant.liv_district_leg,
         v_bp_tenant.liv_street_leg,
         v_bp_tenant.net_monthly_income_leg,
         v_bp_tenant.ownership_of_house_leg,
         v_bp_tenant.other_asset_leg,
         v_bp_tenant.capital_of_family_leg,
         v_bp_tenant.liability_of_family_leg,
         v_bp_tenant.agent_type,
         1,
         Sysdate,
         1,
         Sysdate,
         v_bp_tenant.ref_v01,
         v_bp_tenant.ref_v02,
         v_bp_tenant.ref_v03,
         v_bp_tenant.ref_v04,
         v_bp_tenant.ref_v05,
         v_bp_tenant.ref_v06,
         v_bp_tenant.ref_n01,
         v_bp_tenant.ref_n02,
         v_bp_tenant.ref_n03,
         v_bp_tenant.ref_n04,
         v_bp_tenant.ref_n05,
         v_bp_tenant.ref_n06,
         v_bp_tenant.ref_n07,
         v_bp_tenant.ref_n10,
         v_bp_tenant.ref_d01,
         v_bp_tenant.ref_d02,
         v_bp_tenant.ref_d03,
         v_bp_tenant.ref_d04,
         v_bp_tenant.ref_d05,
         v_bp_tenant.net_monthly_income,
         v_bp_tenant.years_of_living_house,
         v_bp_tenant.total_assets,
         v_bp_tenant.company_id,
         v_bp_tenant.work_district,
         v_bp_tenant.wokr_street,
         v_bp_tenant.name_legal,
         v_bp_tenant.work_street,
         Null,
         v_bp_tenant.net_monthly_income_gua,
         v_bp_tenant.registered_capital_org,
         Null,
         Null,
         Null,
         Null);
        
    Exception
      When Others Then
        Null;
    End;
    -- COMMIT;
    /* Select *
     Into v_project_bp_record
     From prj_project_bp ppb
    Where ppb.project_id = p_project_id;
    
    If v_project_record.division = '02' Then
      Update prj_project pp
         Set pp.bp_category = 'AGENT', 
             pp.bp_id_tenant = v_project_bp_record.bp_id
       Where pp.project_id = p_project_id;
    End If;*/
    --add by zhuxianfei  10160
    --将单据客户经理所在部门所属事业部更新到prj_project 
    Begin
      Select eou.lease_organization
        Into v_lease_organization
        From exp_employee_assigns ea,
             exp_org_position     eop,
             exp_org_unit         eou,
             zj_wfl_users_v       a1
       Where ea.position_id = eop.position_id
         And eop.unit_id = eou.unit_id
         And a1.employee_id = ea.employee_id
         And a1.employee_id = v_project_record.employee_id
         And ea.enabled_flag = 'Y'
         And ea.primary_position_flag = 'Y';
    Exception
      When no_data_found Then
        v_lease_organization := '';
    End;
    Update prj_project p
       Set p.lease_organization = v_lease_organization,
           p.last_updated_by    = p_user_id,
           p.last_update_date   = Sysdate
     Where p.project_id = v_project_record.project_id;
    --end 10160
  Exception
    When e_project_status_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '此单据存在并发,请关闭页面后再打开',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'PROJECT_CREATE_SAVE_CHECK');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_project_create_save_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '此单据正在被人操作,请确认!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'PROJECT_CREATE_SAVE_CHECK');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_check_stauts_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '此单据已经进行了提交操作,请关闭页面再进行操作!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'PROJECT_CREATE_SAVE_CHECK');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure project_save_check(p_project_id Number) Is
    v_project_bp_record prj_project_bp%Rowtype;
    v_project_record    prj_project%Rowtype;
  Begin
    Select *
      Into v_project_record
      From prj_project t
     Where t.project_id = p_project_id;
    Select *
      Into v_project_bp_record
      From prj_project_bp ppb
     Where ppb.project_id = p_project_id
       And ppb.bp_category = 'TENANT';
    If v_project_record.division = '02' Then
      Update prj_project pp
         Set pp.bp_category  = 'TENANT',
             pp.bp_id_tenant = v_project_bp_record.bp_id
       Where pp.project_id = p_project_id;
    End If;
  End;

  Procedure project_tenant_save_check(p_project_id Number,
                                      p_user_id    Number) Is
    v_max_seq        Number;
    v_project_record prj_project%Rowtype;
    v_prj_bp_id      Number;
  Begin
    v_project_record := get_project_record(p_project_id => p_project_id,
                                           p_user_id    => p_user_id);
    Select nvl(Max(b.bp_seq), 0)
      Into v_max_seq
      From prj_project_bp b
     Where b.project_id = v_project_record.project_id
       And b.bp_category = 'TENANT';
    For i In 1 .. v_max_seq Loop
      Begin
        Select d.prj_bp_id
          Into v_prj_bp_id
          From prj_project_bp d
         Where d.project_id = p_project_id
           And d.bp_category = 'TENANT'
           And d.bp_seq = i;
        If i > 1 Then
          Update prj_project_bp p
             Set p.bp_category      = 'TENANT_SEC',
                 p.bp_type          = 'TENANT_SEC',
                 p.last_updated_by  = p_user_id,
                 p.last_update_date = Sysdate
           Where p.prj_bp_id = v_prj_bp_id;
        End If;
      Exception
        When no_data_found Then
          Null;
      End;
    End Loop;
  End;

  ---add by zlf
  Procedure special_retail_submit(p_special_retail_id Number,
                                  p_user_id           Number) Is
    p_special_retail_record prj_special_retail%Rowtype;
    e_project_special_status_err Exception;
    v_approval_method hls_document_type.approval_method%Type;
    v_document_info   Varchar2(200);
    v_instance_id     Number;
  Begin
    Select *
      Into p_special_retail_record
      From prj_special_retail p
     Where p.special_retail_id = p_special_retail_id
       For Update;
    If p_special_retail_record.status != 'NEW' Then
      Raise e_project_special_status_err;
    End If;
    Select approval_method
      Into v_approval_method
      From hls_document_type
     Where document_category = p_special_retail_record.document_category
       And document_type = p_special_retail_record.document_type;
    If v_approval_method = 'WORK_FLOW' Then
      v_document_info := p_special_retail_record.special_retail_number ||
                         '零售特批申请';
      Update prj_special_retail t
         Set t.status = 'SUBMITED'
       Where t.special_retail_id =
             p_special_retail_record.special_retail_id;
      hls_workflow_pkg.workflow_start(p_instance_id       => v_instance_id,
                                      p_document_category => 'CONTRACT',
                                      p_document_type     => 'SPECIAL_APPROVAL',
                                      p_company_id        => p_special_retail_record.company_id,
                                      p_user_id           => p_user_id,
                                      p_parameter_1       => 'SPECIAL_RETAIL_ID',
                                      p_parameter_1_value => p_special_retail_record.special_retail_id,
                                      p_parameter_2       => 'DOCUMENT_INFO',
                                      p_parameter_2_value => v_document_info);
    End If;
  Exception
    When e_project_special_status_err Then
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'PRJ_PROJECT_PKG.SPECIAL_RETAIL_SUBMIT_STATUS_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'PRJ_PROJECT_PKG',
                                                      p_procedure_function_name => 'SPECIAL_RETAIL_SUBMIT');
      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            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'SPECIAL_RETAIL_SUBMIT');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure approve_special_retail(p_special_retail_id Number,
                                   p_user_id           Number) Is
    p_special_retail_record prj_special_retail%Rowtype;
  Begin
    Select *
      Into p_special_retail_record
      From prj_special_retail p
     Where p.special_retail_id = p_special_retail_id
       For Update;
    Update prj_special_retail t
       Set t.status           = 'APPROVED',
           t.last_updated_by  = p_user_id,
           t.last_update_date = Sysdate
     Where t.special_retail_id = p_special_retail_record.special_retail_id;
  End;

  Procedure reject_special_retail(p_special_retail_id Number,
                                  p_user_id           Number) Is
    p_special_retail_record prj_special_retail%Rowtype;
  Begin
    Select *
      Into p_special_retail_record
      From prj_special_retail p
     Where p.special_retail_id = p_special_retail_id
       For Update;
    Update prj_special_retail t
       Set t.status           = 'REJECTED',
           t.last_updated_by  = p_user_id,
           t.last_update_date = Sysdate
     Where t.special_retail_id = p_special_retail_record.special_retail_id;
  End;

  -- 租赁申请创建时填写比例校验选择的产品名称的比例范围 add by liukang 20160420
  Procedure check_price_list(p_price_list         Varchar2,
                             p_int_rate_display   Number,
                             p_down_payment_ratio Number,
                             p_balloon_ratio      Number,
                             p_deposit_ratio      Number,
                             p_lease_times        Number,
                             p_lease_charge_ratio Number,
                             p_user_id            Number) Is
    e_lease_times_err        Exception;
    e_down_payment_ratio_err Exception;
    e_balloon_ratio_err      Exception;
    e_deposit_ratio_err      Exception;
    e_lease_charge_ratio_err Exception;
    e_customer_ratio_err     Exception;
    v_times_from              Number;
    v_times_to                Number;
    v_down_payment_ratio_from Number;
    v_down_payment_ratio_to   Number;
    v_balloon_ratio_from      Number;
    v_balloon_ratio_to        Number;
    v_deposit_ratio_from      Number;
    v_deposit_ratio_to        Number;
    v_lease_charge_ratio      Number;
    v_customer_ratio          Number;
  Begin
    /*select hpp.times_from,
           hpp.times_to,
           (hpp.down_payment_ratio_from / 100),
           (hpp.down_payment_ratio_to / 100),
           (hpp.balloon_ratio_from / 100),
           (hpp.balloon_ratio_to / 100),
           (hpp.deposit_ratio_from / 100),
           (hpp.deposit_ratio_to / 100),
           (hpp.lease_charge_ratio / 100),
           (hpp.customer_ratio / 100)
      into v_times_from,
           v_times_to,
           v_down_payment_ratio_from,
           v_down_payment_ratio_to,
           v_balloon_ratio_from,
           v_balloon_ratio_to,
           v_deposit_ratio_from,
           v_deposit_ratio_to,
           v_lease_charge_ratio,
           v_customer_ratio
      from hls_product_plan_definition hpp
     where hpp.product_plan_id = p_price_list;
    if (p_lease_times < v_times_from or p_lease_times > v_times_to) then
      RAISE e_lease_times_err;
    end if;
    if (p_balloon_ratio < v_balloon_ratio_from or
       p_balloon_ratio > v_balloon_ratio_to) then
      RAISE e_balloon_ratio_err;
    end if;
    if (p_deposit_ratio < v_deposit_ratio_from or
       p_deposit_ratio > v_deposit_ratio_to) then
      RAISE e_deposit_ratio_err;
    end if;
    if (p_down_payment_ratio < v_down_payment_ratio_from or
       p_down_payment_ratio > v_down_payment_ratio_to) then
      RAISE e_down_payment_ratio_err;
    end if;
    if (p_lease_charge_ratio <> v_lease_charge_ratio) then
      RAISE e_lease_charge_ratio_err;
    end if;
    if (p_int_rate_display <> v_customer_ratio) then
      RAISE e_customer_ratio_err;
    end if;*/
    Null;
  Exception
    When e_lease_times_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '填写的租赁期数与产品名称中的租赁期数的上下限不符,请核对后再填写!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'CHECK_PRICE_LIST');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_balloon_ratio_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '填写的尾付款比例与产品名称中的尾付款比例上下限不符,请核对后再填写!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'CHECK_PRICE_LIST');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_deposit_ratio_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '填写的保证金比例与产品名称中的保证金比例上下限不符,请核对后再填写!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'CHECK_PRICE_LIST');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_down_payment_ratio_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '填写的首付款比例与产品名称中的首付款比例上下限不符,请核对后再填写!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'CHECK_PRICE_LIST');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_lease_charge_ratio_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '填写的手续费比例与产品名称中的手续费比例不符,请核对后再填写!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'CHECK_PRICE_LIST');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When e_customer_ratio_err Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '填写的租赁年利率与产品名称中的不符,请核对后再填写!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'CHECK_PRICE_LIST');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
    When no_data_found Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '数据未找到,请联系管理员!',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'CHECK_PRICE_LIST');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  Procedure check_gps_visit(p_product_plan_id Number,
                            p_lease_times     Number,
                            p_dp_ratio        Number,
                            p_finance_amount  Number,
                            p_result          Out Varchar2) As
    v_gps_flag              Varchar2(100) := 'N';
    v_visit_flag            Varchar2(100) := 'N';
    v_min_dp_ratio          Number;
    v_times_limit           Number;
    v_finance_amt_max       Number;
    v_min_dp_ratio_visit    Number;
    v_times_limit_visit     Number;
    v_finance_amt_max_visit Number;
  Begin
    For rec In (Select *
                  From hls_product_no_gps a
                 Where a.product_plan_id = p_product_plan_id) Loop
      v_min_dp_ratio    := rec.min_dp_ratio;
      v_times_limit     := rec.times_limit;
      v_finance_amt_max := rec.finance_amt_max;
      If v_min_dp_ratio Is Null And v_times_limit Is Null And
         v_finance_amt_max Is Null Then
        v_gps_flag := 'Y';
      Else
        If v_min_dp_ratio Is Null Then
          v_min_dp_ratio := 0;
        End If;
        If v_times_limit Is Null Then
          v_times_limit := p_lease_times;
        End If;
        If v_finance_amt_max Is Null Then
          v_finance_amt_max := 999999999;
        End If;
        If v_times_limit = p_lease_times And v_min_dp_ratio <= p_dp_ratio And
           v_finance_amt_max >= p_finance_amount Then
          v_gps_flag := 'Y';
        End If;
      End If;
      If v_gps_flag = 'Y' Then
        Exit;
      End If;
    End Loop;
    For rec In (Select *
                  From hls_product_no_visit b
                 Where b.product_plan_id = p_product_plan_id) Loop
      v_min_dp_ratio_visit    := rec.min_dp_ratio_visit;
      v_times_limit_visit     := rec.times_limit_visit;
      v_finance_amt_max_visit := rec.finance_amt_max_visit;
      If v_min_dp_ratio_visit Is Null And v_times_limit_visit Is Null And
         v_finance_amt_max_visit Is Null Then
        v_visit_flag := 'Y';
      Else
        If v_min_dp_ratio_visit Is Null Then
          v_min_dp_ratio_visit := 0;
        End If;
        If v_times_limit_visit Is Null Then
          v_times_limit_visit := p_lease_times;
        End If;
        If v_finance_amt_max_visit Is Null Then
          v_finance_amt_max_visit := 999999999;
        End If;
        If v_times_limit_visit = p_lease_times And
           v_min_dp_ratio_visit <= p_dp_ratio And
           v_finance_amt_max_visit >= p_finance_amount Then
          v_visit_flag := 'Y';
        End If;
      End If;
      If v_visit_flag = 'Y' Then
        Exit;
      End If;
    End Loop;
    If v_gps_flag = 'Y' And v_visit_flag = 'Y' Then
      p_result := 'BOTH';
    Elsif v_gps_flag = 'Y' And v_visit_flag = 'N' Then
      p_result := 'GPS';
    Elsif v_gps_flag = 'N' And v_visit_flag = 'Y' Then
      p_result := 'VISIT';
    Else
      p_result := 'NOT';
    End If;
  End check_gps_visit;

  Function get_prj_project_rec(p_project_id Number, p_user_id Number)
    Return prj_project%Rowtype Is
    v_prj_project_rec prj_project%Rowtype;
    e_lock_table Exception;
  Begin
    Select *
      Into v_prj_project_rec
      From prj_project t
     Where t.project_id = p_project_id
       For Update Nowait;
    Return v_prj_project_rec;
  Exception
    When e_lock_table Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该资源正忙',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'hls_bp_master',
                                                     p_procedure_function_name => 'get_bp_master_rec');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  ---租赁申请预审提交
  Procedure prj_project_submit(p_project_id  Number,
                               p_user_id     Number,
                               p_company_id  Number,
                               p_layout_code Varchar2) Is
    v_prj_project_rec prj_project%Rowtype;
    v_instance_id     prj_project.pjd_instance_id%Type;
    v_division_desc   Varchar2(200);
    v_special_note    Varchar2(500);
    v_bp_name         Varchar2(500);
    e_submit_error Exception;
    v_workflow_desc Varchar2(2000);
  Begin
    v_prj_project_rec := get_prj_project_rec(p_project_id => p_project_id,
                                             p_user_id    => p_user_id);
    If v_prj_project_rec.project_status Not In ('NEW') Then
      Raise e_submit_error;
    End If;
    Update prj_project t
       Set t.project_status      = 'PRE_APPROVING',
           t.pjd_instance_status = 'PRE_APPROVING',
           t.last_updated_by     = p_user_id,
           t.last_update_date    = Sysdate
     Where t.project_id = v_prj_project_rec.project_id;
    Select bp.bp_name
      Into v_bp_name
      From hls_bp_master bp
     Where bp.bp_id = v_prj_project_rec.bp_id_tenant;
    Select hd.description
      Into v_division_desc
      From hls_division hd
     Where hd.division = v_prj_project_rec.division;
    Select ppd.special_note
      Into v_special_note
      From hls_product_plan_definition ppd
     Where ppd.product_plan_id In
           (Select pq.product_id
              From prj_quotation pq
             Where pq.document_id = v_prj_project_rec.project_id);
    Begin
      Select (Select z.workflow_desc
                From zj_wfl_workflow z
               Where z.workflow_id = t.workflow_id) As workflow_desc
        Into v_workflow_desc
        From sys_conds_table_gen_1022 t
       Where t.function = 'PRJ500D';
    Exception
      When Others Then
        Null;
    End;
    hls_workflow_pkg.workflow_start(p_instance_id       => v_instance_id,
                                    p_document_category => 'PROJECT',
                                    p_document_type     => '',
                                    p_business_type     => v_prj_project_rec.business_type,
                                    p_company_id        => v_prj_project_rec.company_id,
                                    p_user_id           => p_user_id,
                                    p_function_code     => 'PRJ500D',
                                    -- parameter values
                                    p_parameter_1       => 'PROJECT_ID',
                                    p_parameter_1_value => v_prj_project_rec.project_id,
                                    p_parameter_2       => 'DOCUMENT_NUMBER',
                                    p_parameter_2_value => v_prj_project_rec.project_number,
                                    p_parameter_3       => 'OWNER_USER_ID',
                                    p_parameter_3_value => p_user_id,
                                    p_parameter_4       => 'LAYOUT_CODE',
                                    p_parameter_4_value => p_layout_code,
                                    p_parameter_5       => 'DOCUMENT_INFO',
                                    p_parameter_5_value => v_workflow_desc || ':' ||
                                                           v_prj_project_rec.project_number || '-' ||
                                                           v_bp_name || '-' ||
                                                           v_division_desc || '-' ||
                                                           v_special_note);
  Exception
    When e_submit_error Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该单据状态有误,不可提交',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'prj_project_submit');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  --审批通过
  Procedure prj_project_approved(p_project_id Number, p_user_id Number) Is
    v_prj_project_rec prj_project%Rowtype;
    v_instance_id     prj_project.pjd_instance_id%Type;
    e_submit_error Exception;
  Begin
    v_prj_project_rec := get_prj_project_rec(p_project_id => p_project_id,
                                             p_user_id    => p_user_id);
    If v_prj_project_rec.project_status <> 'PRE_APPROVING' Then
      Raise e_submit_error;
    End If;
    Update prj_project t
       Set t.project_status      = 'NEW',
           t.pjd_instance_status = 'APPROVED',
           t.last_updated_by     = p_user_id,
           t.last_update_date    = Sysdate
     Where t.project_id = v_prj_project_rec.project_id;
  Exception
    When e_submit_error Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该单据状态有误,请核查',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'HLS_BP_MASTER_PKG',
                                                     p_procedure_function_name => 'APPROVE_AGENT_ACCESS_WFL');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  --审批拒绝
  Procedure prj_project_rejected(p_project_id Number, p_user_id Number) Is
    v_prj_project_rec prj_project%Rowtype;
    v_instance_id     prj_project.pjd_instance_id%Type;
    e_submit_error Exception;
  Begin
    v_prj_project_rec := get_prj_project_rec(p_project_id => p_project_id,
                                             p_user_id    => p_user_id);
    If v_prj_project_rec.project_status <> 'PRE_APPROVING' Then
      Raise e_submit_error;
    End If;
    Update prj_project t
       Set t.project_status      = 'PRE_REJECT',
           t.pjd_instance_status = 'PRE_REJECT',
           t.last_updated_by     = p_user_id,
           t.last_update_date    = Sysdate
     Where t.project_id = v_prj_project_rec.project_id;
  Exception
    When e_submit_error Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该单据状态有误,请核查',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'prj_project_rejected');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  --预审审批拒绝
  Procedure prj_project_pre_rejected(p_project_id Number, p_user_id Number) Is
    v_prj_project_rec prj_project%Rowtype;
    v_instance_id     prj_project.pjd_instance_id%Type;
    e_submit_error Exception;
  Begin
    v_prj_project_rec := get_prj_project_rec(p_project_id => p_project_id,
                                             p_user_id    => p_user_id);
    If v_prj_project_rec.project_status <> 'PRE_APPROVING' Then
      Raise e_submit_error;
    End If;
    Update prj_project t
       Set t.project_status      = 'PRE_REJECT',
           t.pjd_instance_status = 'REJECT',
           t.last_updated_by     = p_user_id,
           t.last_update_date    = Sysdate
     Where t.project_id = v_prj_project_rec.project_id;
  Exception
    When e_submit_error Then
      sys_raise_app_error_pkg.raise_sys_others_error(p_message                 => '该单据状态有误,请核查',
                                                     p_created_by              => p_user_id,
                                                     p_package_name            => 'PRJ_PROJECT_PKG',
                                                     p_procedure_function_name => 'prj_project_rejected');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  End;

  --获取信审风险敞口
  Function get_risk_exposure_amount(p_project_id In Number) Return Number Is
    v_risk_exposure_amount Number;
    v_risk                 Number;
  Begin
    Select (Select nvl(Sum(ccc.principal), 0) -
                   nvl(Sum(ccc.received_principal), 0)
              From con_contract_cashflow ccc
             Where ccc.contract_id In
                   (Select contract_id
                      From con_contract cc
                     Where cc.bp_id_tenant = pp.bp_id_tenant
                       And cc.data_class = 'NORMAL'
                       And cc.contract_status In ('INCEPT', 'PENDING'))
               And ccc.cf_status = 'RELEASE'
               And ccc.cf_item = 1) +
           nvl(get_write_off_amount(pp.project_id), 0) +
           (Select nvl(Sum(pq.finance_amount), 0)
              From prj_quotation pq
             Where pq.document_category = 'PROJECT'
               And pq.document_id = pp.project_id)
      Into v_risk_exposure_amount
      From prj_project pp
     Where pp.project_id = p_project_id;
    If v_risk_exposure_amount < 800000 Then
      v_risk := 1;
    Elsif v_risk_exposure_amount >= 800000 And
          v_risk_exposure_amount < 1000000 Then
      v_risk := 2;
    Elsif v_risk_exposure_amount >= 1000000 And
          v_risk_exposure_amount <= 2000000 Then
      v_risk := 3;
    Elsif v_risk_exposure_amount > 2000000 Then
      v_risk := 4;
    End If;
    Return v_risk;
  End;

  Function get_prj_division(p_project_id In Number) Return Number Is
    v_division_flag Number;
    v_division      Varchar2(10);
  Begin
    --批发走区域经理,零售不走
    Select p.division
      Into v_division
      From prj_project p
     Where p.project_id = p_project_id;
    If v_division = '00' Then
      v_division_flag := 1;
    Elsif v_division = '07' Then
      v_division_flag := 1;
    Elsif v_division = '09' Then
      v_division_flag := 1;
    Else
      v_division_flag := 0;
    End If;
    Return v_division_flag;
  End;

  Procedure update_district_ln(p_product_plan_id Number Default Null,
                               p_district_id     Varchar2,
                               p_user_id         Varchar2) Is
  Begin
    Insert Into hls_product_plan_district t
      (record_id,
       product_plan_id,
       unit_id,
       created_by,
       creation_date,
       last_updated_by,
       last_update_date)
    Values
      (hls_product_plan_district_s.nextval,
       p_product_plan_id,
       p_district_id,
       p_user_id,
       Sysdate,
       p_user_id,
       Sysdate);
  End;

  --得到产品方案是否自定义
  Procedure get_if_self_definition(p_product_plan_id    Number Default Null,
                                   p_if_self_definition Out Varchar2,
                                   p_user_id            Varchar2) Is
  
  Begin
    Select nvl(h.if_self_definition, 'N')
      Into p_if_self_definition
      From hls_product_plan_definition h
     Where h.product_plan_id = p_product_plan_id;
  End;

  --得到附件数量
  Procedure get_cdd_count(p_document_table Varchar2,
                          p_document_id    Number,
                          p_cdd_list_id    Number,
                          p_count          Out Number) Is
  
  Begin
    Select Count(*)
      Into p_count
      From (Select pcf.check_id,
                   pcf.document_id,
                   pcf.document_table,
                   pck.cdd_item_id,
                   pck.send_flag,
                   pck.paper_required,
                   pck.attachment_required,
                   pck.not_applicable
              From prj_cdd_item_doc_ref pcf, prj_cdd_item_check pck
             Where pcf.document_table = p_document_table
               And pcf.document_id = p_document_id
               And pck.check_id = pcf.check_id) pp,
           prj_cdd_item pci,
           prj_cdd_item_list_grp_tab lgt,
           prj_cdd_item_tab_group tg,
           fnd_atm_attachment_multi fm,
           fnd_atm_attachment f
     Where pci.cdd_item_id = lgt.cdd_item_id
       And lgt.tab_group_id = tg.tab_group_id
       And lgt.enabled_flag = 'Y'
       And tg.enabled_flag = 'Y'
       And pci.cdd_item_id = pp.cdd_item_id(+)
       And pci.cdd_list_id = p_cdd_list_id
       And lgt.cdd_list_id = pci.cdd_list_id
       And pci.enabled_flag = 'Y'
       And fm.attachment_id = f.attachment_id
       And fm.table_name = 'PRJ_CDD_ITEM_CHECK'
       And fm.table_pk_value = pp.check_id
     Order By lgt.seq_num Asc;
  
  End;

  Procedure prj_project_modify_status(p_project_id     Number,
                                      p_user_id        Number,
                                      p_project_status varchar2) Is
  v_project_status     prj_project.project_status%TYPE;
    v_approval           prj_project_approval%ROWTYPE;
    r_project_rec        prj_project%ROWTYPE;
    r_bp_master_rec      hls_bp_master%ROWTYPE;
    v_tenant_name        VARCHAR2(200);
    v_business_type      VARCHAR2(200);
    v_billing_method     VARCHAR2(200);
    v_contract_id        NUMBER;
    v_contract_number    VARCHAR2(30);
    v_affiliated_company VARCHAR2(200);
    v_exist_flag         VARCHAR2(1);
  begin
      SELECT *
      INTO r_project_rec
      FROM prj_project
     WHERE project_id = p_project_id;
    Update prj_project t
       Set t.project_status   = p_project_status,
           t.approved_date       =Sysdate,
           t.last_updated_by  = p_user_id,
           t.last_update_date = Sysdate
     Where t.project_id = p_project_id;
       SELECT a.bp_name
        INTO v_tenant_name
        FROM prj_project_bp a
       WHERE a.project_id = p_project_id
         AND a.bp_category = 'TENANT'
         AND rownum < 2;
      SELECT a.business_type
        INTO v_business_type
        FROM prj_project a
       WHERE a.project_id = p_project_id;
      --直租开租金票回租开租息票
      SELECT decode(v_business_type,
                    'LEASE',
                    'L_RENTAL',
                    'LB_INTEREST')
        INTO v_billing_method
        FROM dual;
          IF r_project_rec.division = '09' THEN
          --从项目表中取保险公司,只有保单分期POLICY类型并且保险公司有值才插入
          FOR c_rec IN (SELECT DISTINCT fi.insurance_company
                          FROM prj_project_factor_info fi
                         WHERE fi.project_id = p_project_id
                           AND fi.business_type = 'POLICY'
                           AND fi.insurance_company IS NOT NULL)
          LOOP
            --从商业伙伴表中获取该保险公司的信息
            SELECT *
              INTO r_bp_master_rec
              FROM hls_bp_master bp
             WHERE bp.bp_id = to_number(c_rec.insurance_company);
            BEGIN
              --判断prj_project_bp表中是否有该保险公司
              SELECT 'Y'
                INTO v_exist_flag
                FROM dual
               WHERE EXISTS (SELECT 1
                        FROM prj_project_bp bp
                       WHERE bp.project_id = p_project_id
                         AND bp.bp_id = r_bp_master_rec.bp_id
                         AND bp.bp_category = 'INSURER');
            EXCEPTION
              --如果prj_project_bp表中没有则插入
              WHEN no_data_found THEN
                INSERT INTO prj_project_bp pb
                  (prj_bp_id,
                   project_id,
                   bp_category,
                   bp_id,
                   bp_code,
                   bp_type,
                   bp_class,
                   bp_name)
                VALUES
                  (prj_project_bp_s.nextval,
                   p_project_id,
                   'INSURER',
                   r_bp_master_rec.bp_id,
                   r_bp_master_rec.bp_code,
                   'INSURER',
                   r_bp_master_rec.bp_class,
                   r_bp_master_rec.bp_name);
            END;
          END LOOP;
        END IF;
        --暂时不做产品线的控制,插入挂靠公司
        BEGIN
          --如果挂靠公司为空则不插入
          SELECT t.affiliated_company
            INTO v_affiliated_company
            FROM pro_project_invest_return t
           WHERE t.project_id = p_project_id;
        EXCEPTION
          WHEN no_data_found THEN
            NULL;
          WHEN OTHERS THEN
            --从商业伙伴表中获取该挂靠公司的信息
            SELECT *
              INTO r_bp_master_rec
              FROM hls_bp_master bp
             WHERE bp.bp_id = to_number(v_affiliated_company);
            BEGIN
              --判断prj_project_bp表中是否有该保险公司
              SELECT 'Y'
                INTO v_exist_flag
                FROM dual
               WHERE EXISTS (SELECT 1
                        FROM prj_project_bp bp
                       WHERE bp.project_id = p_project_id
                         AND bp.bp_id = r_bp_master_rec.bp_id
                         AND bp.bp_category = 'AFFILIATED_COMPANY');
            EXCEPTION
              --如果prj_project_bp表中没有则插入
              WHEN no_data_found THEN
                INSERT INTO prj_project_bp pb
                  (prj_bp_id,
                   project_id,
                   bp_category,
                   bp_id,
                   bp_code,
                   bp_type,
                   bp_class,
                   bp_name)
                VALUES
                  (prj_project_bp_s.nextval,
                   p_project_id,
                   'AFFILIATED_COMPANY',
                   r_bp_master_rec.bp_id,
                   r_bp_master_rec.bp_code,
                   'AFFILIATED_COMPANY',
                   r_bp_master_rec.bp_class,
                   r_bp_master_rec.bp_name);
            END;
        END;
          IF r_project_rec.document_type = 'CARLS' AND  p_project_status= 'APPROVED' THEN
          con_contract_pkg.save_contract_from_project(p_project_id        => p_project_id,
                                                      p_contract_seq      => 1,
                                                      p_bp_contract_seq   => 1,
                                                      p_con_document_type => 'CARCON',
                                                      p_con_contract_name => v_tenant_name || '的合同',
                                                      p_billing_method    => v_billing_method,
                                                      p_contract_id       => v_contract_id,
                                                      p_contract_number   => v_contract_number,
                                                      p_user_id           => p_user_id);
         END IF;
  
  end;

End prj_project_pkg;
/