create or replace package hl_con_contract_content_pkg is PROCEDURE contract_content_create(p_contract_id NUMBER, p_content_id out number, p_user_id NUMBER, p_templet_usage VARCHAR2 DEFAULT NULL, p_content_type VARCHAR2 DEFAULT NULL); end hl_con_contract_content_pkg; / create or replace package body hl_con_contract_content_pkg is g_check_yes CONSTANT VARCHAR2(1) := 'Y'; g_check_no CONSTANT VARCHAR2(1) := 'N'; PROCEDURE con_contract_check(p_contract_id NUMBER, p_user_id NUMBER, p_con_rec OUT con_contract%ROWTYPE) IS e_con_status_error EXCEPTION; BEGIN p_con_rec := con_contract_pkg.get_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id); /*if p_con_rec.contract_status not in ('NEW', 'SIGN', 'INCEPT', 'PAID', 'APPROVING') then raise e_con_status_error; end if;*/ EXCEPTION WHEN e_con_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CONTENT_PKG.CON_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_content_pkg', p_procedure_function_name => 'con_contract_check'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --删除合同伙伴已被删除的打印文本 PROCEDURE delete_when_create_check(p_contract_id NUMBER, p_user_id NUMBER) IS v_cdd_list_id number; BEGIN DELETE FROM con_contract_content t WHERE t.contract_id = p_contract_id; END; FUNCTION contract_tmpt_check(p_contract_id NUMBER, p_tmpt_id NUMBER, p_user_id NUMBER, p_bp_type VARCHAR2, p_bp_class VARCHAR2, p_bp_id NUMBER, p_content_type VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS v_count NUMBER; v_exist NUMBER; v_nationality VARCHAR2(30); --国籍 v_country_code VARCHAR2(30); v_data_class VARCHAR2(30); BEGIN SELECT COUNT(*) INTO v_count FROM con_contract_tmpt_clause c WHERE c.tmpt_id = p_tmpt_id; IF v_count = 0 THEN RETURN g_check_yes; END IF; /* BEGIN SELECT nvl(fc.country_code, 'CHN') INTO v_country_code FROM hls_bp_master bm, fnd_country fc WHERE bm.nationality = fc.country_id(+) AND bm.bp_id = p_bp_id; EXCEPTION WHEN OTHERS THEN v_country_code := 'CHN'; END; IF v_country_code = 'CHN' THEN v_nationality := 'inland'; ELSE v_nationality := 'foreign'; END IF;*/ SELECT cc.data_class INTO v_data_class FROM con_contract cc WHERE cc.contract_id = p_contract_id; FOR cv IN (SELECT * FROM con_contract_tmpt_clause c WHERE c.tmpt_id = p_tmpt_id) LOOP IF v_data_class = 'NORMAL' THEN SELECT COUNT(*) INTO v_exist FROM con_contract ct, con_contract_mortgage ccm, con_contract_bp ccb --add by zhangyu 2015-07-20 WHERE ct.contract_id = p_contract_id --add by zhangyu 2015-07-20 and ct.contract_id = ccb.contract_id and ccm.contract_id(+) = ccb.contract_id and ccm.bp_id_mortgagor(+) = ccb.bp_id and ccb.bp_type = p_bp_type and ccb.bp_class = p_bp_class and ccb.bp_id = p_bp_id --end AND ct.data_class = 'NORMAL' AND ct.business_type = nvl(cv.business_type, ct.business_type) --业务类型 /* AND ct.contract_id = nvl(cv.contract_id, ct.contract_id) --合同 AND ct.lease_organization = nvl(cv.lease_organization, ct.lease_organization) --事业部 AND ct.lease_channel = nvl(cv.lease_channel, ct.lease_channel) --商业模式 AND ct.document_type = nvl(cv.document_type, ct.document_type) --单据类型 AND nvl(ct.price_list, '##') = nvl(cv.price_list, nvl(ct.price_list, '##')) --报价方案 AND ct.division = nvl(cv.division, ct.division) --产品线 AND p_bp_type = nvl(cv.bp_category, p_bp_type) AND p_bp_class = nvl(cv.bp_class, p_bp_class) AND v_nationality = nvl(cv.other_tmpt_clause, v_nationality) AND nvl(p_content_type, 'N') = nvl(cv.content_type, nvl(p_content_type, 'N')) AND nvl(cv.data_class, 'NORMAL') = ct.data_class --add by zhangyu 2015-07-20 and nvl(ccm.mortgage_asset_detail, 'N') = nvl(cv.mortgage_asset_detail, nvl(ccm.mortgage_asset_detail, 'N')) and decode(ct.down_payment, '0', 'N', null, 'N', 'Y') = nvl(cv.down_payment_flag, decode(ct.down_payment, '0', 'N', null, 'N', 'Y')) -- end */ /*AND ct.OTHER_TMPT_CLAUSE = nvl(cv.other_tmpt_clause, ct.OTHER_TMPT_CLAUSE)*/ ; ELSE SELECT COUNT(*) INTO v_exist FROM con_contract ct, con_contract_change_req cr WHERE ct.contract_id = p_contract_id --AND ct.data_class = 'NORMAL' AND ct.business_type = nvl(cv.business_type, ct.business_type) --业务类型 AND ct.contract_id = nvl(cv.contract_id, ct.contract_id) --合同 AND ct.lease_organization = nvl(cv.lease_organization, ct.lease_organization) --事业部 AND ct.lease_channel = nvl(cv.lease_channel, ct.lease_channel) --商业模式 AND ct.document_type = nvl(cv.document_type, ct.document_type) --单据类型 AND ct.price_list = nvl(cv.price_list, ct.price_list) --报价方案 AND ct.division = nvl(cv.division, ct.division) --产品线 AND p_bp_type = nvl(cv.bp_category, p_bp_type) AND p_bp_class = nvl(cv.bp_class, p_bp_class) AND v_nationality = nvl(cv.other_tmpt_clause, v_nationality) AND nvl(p_content_type, 'N') = nvl(cv.content_type, nvl(p_content_type, 'N')) AND nvl(cv.data_class, 'NORMAL') = ct.data_class AND cr.change_req_id = ct.contract_id AND (cr.reschedule_flag = nvl(cv.reschedule_flag, 'A') OR cr.et_flag = nvl(cv.et_flag, 'A') OR cr.tenant_change_flag = nvl(cv.tenant_change_flag, 'A') OR cr.bank_acc_change_flag = nvl(cv.bank_acc_change_flag, 'A') OR cr.lease_item_change_flag = nvl(cv.lease_item_change_flag, 'A') OR cr.mortgage_change_flag = nvl(cv.mortgage_change_flag, 'A') OR cr.guarantor_change_flag = nvl(cv.guarantor_change_flag, 'A') OR cr.contract_cancel_flag = nvl(cv.contract_cancel_flag, 'A') OR cr.cashflow_change_flag = nvl(cv.cashflow_change_flag, 'A')) /*AND ct.OTHER_TMPT_CLAUSE = nvl(cv.other_tmpt_clause, ct.OTHER_TMPT_CLAUSE)*/ ; END IF; IF v_exist = 0 THEN NULL; --RETURN g_check_no; ELSE RETURN g_check_yes; END IF; END LOOP; RETURN g_check_no; END contract_tmpt_check; PROCEDURE insert_contract_content(p_content_id OUT con_contract_content.content_id%TYPE, p_contract_id con_contract_content.contract_id%TYPE, p_content_number con_contract_content.content_number%TYPE, p_clause_usage con_contract_content.clause_usage%TYPE, p_templet_id con_contract_content.templet_id%TYPE, p_con_contract_bp_id con_contract_content.con_contract_bp_id%TYPE, p_mortgage_id con_contract_content.mortgage_id%TYPE, p_content_print_flag con_contract_content.content_print_flag%TYPE, p_available_flag con_contract_content.available_flag%TYPE, p_user_id con_contract_content.created_by%TYPE, p_bp_id number default null) IS BEGIN SELECT con_contract_content_s.nextval INTO p_content_id FROM dual; -- p_content_id := con_contract_content_s.nextval; INSERT INTO con_contract_content (content_id, contract_id, content_number, clause_usage, templet_id, con_contract_bp_id, mortgage_id, content_print_flag, available_flag, creation_date, created_by, last_update_date, last_updated_by) VALUES (p_content_id, p_contract_id, p_content_number, p_clause_usage, p_templet_id, p_con_contract_bp_id, p_mortgage_id, p_content_print_flag, p_available_flag, SYSDATE, p_user_id, SYSDATE, p_user_id); END; PROCEDURE contract_content_create(p_contract_id NUMBER, p_content_id out number, p_user_id NUMBER, p_templet_usage VARCHAR2 DEFAULT NULL, p_content_type VARCHAR2 DEFAULT NULL) IS v_content_id NUMBER; v_record_id NUMBER; v_clause_usage con_contract_content.clause_usage%TYPE; v_business_type con_contract.business_type%TYPE; r_prj_rec prj_project%ROWTYPE; r_con_rec con_contract%ROWTYPE; e_clause_usage_null_error EXCEPTION; v_contract_bp_id NUMBER; v_tmpt_count NUMBER; v_contract_number VARCHAR2(64); v_lease_channel VARCHAR2(24); v_cdd_list_id NUMBER; v_exists NUMBER; v_count_normal NUMBER; v_source_con_id NUMBER; v_need_rec_0 NUMBER; r_con_clause_templet con_clause_templet%ROWTYPE; BEGIN con_contract_check(p_contract_id => p_contract_id, p_user_id => p_user_id, p_con_rec => r_con_rec); /* r_prj_rec := prj_project_pkg.project_get_record(p_project_id => r_con_rec.project_id, p_user_id => p_user_id);*/ ----删除合同伙伴已被删除的打印文本 delete_when_create_check(p_contract_id => r_con_rec.contract_id, p_user_id => p_user_id); FOR cv IN (SELECT * FROM con_clause_templet t WHERE t.enabled_flag = 'Y' and t.templet_usage = nvl(p_templet_usage, t.templet_usage)--FOR TEST 20170628 BOBO ) LOOP FOR c_cps IN (SELECT cb.record_id, ct.contract_number, cb.bp_id, cb.bp_category, --modify by zhangyu 2015-08-20 cb.bp_type, cb.bp_class FROM con_contract ct, con_contract_bp cb --, --hls_bp_master bm WHERE ct.contract_id = p_contract_id AND cb.contract_id = ct.contract_id and cb.bp_category = 'TENANT' --AND cb.bp_id = bm.bp_id(+) ) LOOP IF contract_tmpt_check(p_contract_id, cv.templet_id, p_user_id, c_cps.bp_category, c_cps.bp_class, c_cps.bp_id, p_content_type) = g_check_yes THEN SELECT COUNT(*) INTO v_tmpt_count FROM con_contract_content cc WHERE cc.contract_id = p_contract_id AND cc.templet_id = cv.templet_id AND cc.con_contract_bp_id = c_cps.record_id; IF v_tmpt_count = 0 THEN insert_contract_content(p_content_id => v_content_id, p_contract_id => p_contract_id, p_content_number => cv.description, p_clause_usage => cv.templet_usage, p_templet_id => cv.templet_id, p_con_contract_bp_id => c_cps.record_id, p_mortgage_id => '', p_content_print_flag => 'N', p_available_flag => 'Y', p_user_id => p_user_id ); UPDATE con_contract_content ct SET ct.ref_n05 = cv.print_num -- ct.content_type = p_content_type, -- ct.data_class = r_con_rec.data_class WHERE ct.content_id = v_content_id; END IF; END IF; END LOOP; END LOOP; hls_document_save_pkg.save_cdd_item_from_doc(p_document_category => 'CON_CONTRACT', p_document_id => p_contract_id, p_user_id => p_user_id, p_cdd_list_id => r_con_rec.cdd_list_id); EXCEPTION WHEN e_clause_usage_null_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_CONTENT_PKG.CLAUSE_USAGE_NULL_ERROR', p_created_by => p_user_id, p_package_name => 'con_contract_content_pkg', p_procedure_function_name => 'contract_content_create'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; end hl_con_contract_content_pkg; /