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; /