CREATE OR REPLACE PACKAGE csh_payment_req_pkg IS -- Author : qwm -- Created : 2013/5/24 16:35:21 -- Purpose : /*procedure csh_payment_req_amount_check(p_cashflow_id number, p_amount number, p_company_id number, p_user_id number, p_transaction_date date, p_amount_flag out varchar2, p_payment_req_number out varchar2, p_document_type varchar2);*/ FUNCTION get_bp_id(p_contract_id NUMBER) RETURN NUMBER; PROCEDURE check_cf_after_ins_temp(p_session_id NUMBER, p_user_id NUMBER); PROCEDURE check_cf_0_total_amt(p_contract_id NUMBER, p_user_id NUMBER); FUNCTION get_bp_category_desc(p_contract_id NUMBER, p_bp_id NUMBER) RETURN VARCHAR2; FUNCTION get_payment_req_number(p_document_type VARCHAR2, p_transaction_date DATE, p_company_id NUMBER, p_user_id NUMBER, p_document_category VARCHAR2) RETURN VARCHAR2; PROCEDURE payment_submit(p_payment_req_id NUMBER, p_user_id NUMBER); PROCEDURE change_print_status(p_contract_id NUMBER, p_user_id NUMBER); PROCEDURE change_printed_status(p_contract_id NUMBER, p_user_id NUMBER); PROCEDURE change_applied_status(p_contract_id NUMBER, p_user_id NUMBER); PROCEDURE csh_payment_apply(p_contract_id NUMBER, p_user_id NUMBER); PROCEDURE csh_payment_print(p_contract_id NUMBER, p_user_id NUMBER); PROCEDURE upd_csh_payment_after_reverse(p_transaction_id csh_transaction.transaction_id%TYPE, p_user_id NUMBER); PROCEDURE close_csh_payment_req_hd(p_payment_req_id NUMBER, p_closed_date DATE, p_closed_note VARCHAR2, p_user_id NUMBER); PROCEDURE check_csh_payment_bp_unique(p_payment_req_id NUMBER, p_user_id NUMBER); PROCEDURE check_csh_payment_bp(p_payment_req_id NUMBER, p_user_id NUMBER); --保证金退还 付款申请 校验 add by Harry 9952 2017/2/24 PROCEDURE check_csh_payment_bond(p_payment_req_id NUMBER, p_user_id NUMBER); PROCEDURE payment_csh_payment(p_payment_req_id NUMBER, p_payment_req_ln_id NUMBER, p_pay_date DATE, p_pay_amount NUMBER, p_company_id NUMBER, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_exchange_rate_type csh_transaction.exchange_rate_type%TYPE, p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE, p_exchange_rate csh_transaction.exchange_rate%TYPE, p_bank_account_id csh_transaction.bank_account_id%TYPE, p_bank_account_num VARCHAR2, p_description csh_transaction.description%TYPE, p_user_id NUMBER, p_cashflow_id NUMBER, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_bp_bank_account_id csh_transaction.bp_bank_account_id%TYPE, p_bp_bank_account_num csh_transaction.bp_bank_account_num%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_merge_flag VARCHAR2 DEFAULT 'N', p_payment_behalf_flag VARCHAR2 DEFAULT 'N', p_payment_status OUT VARCHAR2, p_transaction_id IN OUT NUMBER, p_bp_category varchar2 default null, p_due_amount NUMBER DEFAULT NULL); PROCEDURE payment_csh_payment(p_payment_req_id NUMBER, p_session_id NUMBER, p_pay_date DATE, p_pay_amount NUMBER, p_company_id NUMBER, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_exchange_rate_type csh_transaction.exchange_rate_type%TYPE, p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE, p_exchange_rate csh_transaction.exchange_rate%TYPE, p_bank_account_id csh_transaction.bank_account_id%TYPE, p_bank_account_num VARCHAR2, p_description csh_transaction.description%TYPE, p_user_id NUMBER, p_cashflow_id NUMBER, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_bp_bank_account_id csh_transaction.bp_bank_account_id%TYPE, p_bp_bank_account_num csh_transaction.bp_bank_account_num%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_payment_status OUT VARCHAR2, p_merge_flag VARCHAR2 DEFAULT 'N', p_payment_behalf_flag VARCHAR2 DEFAULT 'N', p_this_pay_agent_amount NUMBER, p_this_pay_vender_amount NUMBER, p_bank_flag VARCHAR2 DEFAULT 'N', p_bp_category varchar2 default null, p_due_amount NUMBER DEFAULT NULL); PROCEDURE ins_csh_pay_cashflow_id_temp(p_session_id NUMBER, p_cashflow_id NUMBER, p_user_id NUMBER); PROCEDURE del_csh_pay_cashflow_id_temp(p_session_id NUMBER, p_user_id NUMBER); PROCEDURE ins_csh_payment_req_hd(p_payment_req_id IN OUT NUMBER, p_company_id NUMBER, p_document_type VARCHAR2, p_payment_req_number VARCHAR2, p_req_date DATE, p_apply_pay_date DATE, p_transaction_category VARCHAR2, p_distribution_set_id NUMBER, p_payment_method_id NUMBER, p_bp_id NUMBER, p_bp_category VARCHAR2, p_amount NUMBER, p_currency_code VARCHAR2, p_description VARCHAR2, p_submitted_flag VARCHAR2, p_printed_flag VARCHAR2, p_printed_times NUMBER, p_last_print_date DATE, p_closed_flag VARCHAR2, p_closed_date DATE, p_closed_note VARCHAR2, p_approval_status VARCHAR2, p_approval_date DATE, p_approval_note VARCHAR2, p_bp_bank_account_id NUMBER DEFAULT NULL, p_bp_bank_account_num VARCHAR2 DEFAULT NULL, p_bp_bank_account_name VARCHAR2 DEFAULT NULL, p_business_type VARCHAR2 DEFAULT NULL, p_branch_id NUMBER, p_city_id NUMBER, p_user_id NUMBER, p_csh_bp_name VARCHAR2 DEFAULT NULL); PROCEDURE ins_csh_payment_req_ln(p_payment_req_ln_id IN OUT NUMBER, p_payment_req_id NUMBER, p_payment_req_ln_type VARCHAR2, p_ref_doc_category VARCHAR2, p_ref_doc_id NUMBER, p_ref_doc_line_id NUMBER, p_bp_id NUMBER, p_currency_code VARCHAR2, p_amount NUMBER, p_amount_paid NUMBER, p_description VARCHAR2, p_payment_status VARCHAR2, p_payment_completed_date DATE, p_apply_pay_date DATE, p_payment_method_id NUMBER, p_bank_account_id NUMBER, p_bank_account_num VARCHAR2, p_bank_account_name VARCHAR2, p_bank_branch_name VARCHAR2, p_bp_bank_account_id NUMBER DEFAULT NULL, p_bp_bank_account_num VARCHAR2 DEFAULT NULL, p_bp_bank_account_name VARCHAR2 DEFAULT NULL, p_user_id NUMBER, p_company_id NUMBER); PROCEDURE del_csh_payment_req_ln(p_payment_req_ln_id NUMBER, p_user_id NUMBER); PROCEDURE del_csh_payment_req_hd(p_payment_req_id NUMBER, p_user_id NUMBER); PROCEDURE update_csh_payment_flag(p_payment_req_id NUMBER, p_payment_flag VARCHAR2, p_user_id NUMBER); PROCEDURE update_payment_req_number(p_payment_req_id IN NUMBER, p_payment_req_number IN VARCHAR2); --add by zxf PROCEDURE update_payment_req_contract_id(p_payment_req_id IN NUMBER, p_contract_id IN NUMBER); PROCEDURE check_ddct_amount(p_payment_req_id IN NUMBER); PROCEDURE check_contract_payment(p_contract_id NUMBER, p_payment_req_id NUMBER, p_user_id NUMBER); --add by 9188 检验付款申请工作流的所有合同是否全部核销 PROCEDURE check_sch_payment_con(p_payment_req_id NUMBER, p_user_id NUMBER); --add by Harry 9952 保证金退还-付款申请校验 PROCEDURE check_status_and_flag(p_cashflow_id NUMBER, p_user_id NUMBER); PROCEDURE create_cdd(p_payment_req_id NUMBER, p_user_id NUMBER, p_cdd_list_id IN OUT NUMBER); PROCEDURE save_cdd_item_from_templt(p_document_category VARCHAR2, p_document_type VARCHAR2, p_document_id NUMBER, p_company_id NUMBER, p_user_id NUMBER, p_cdd_list_id IN OUT NUMBER); END csh_payment_req_pkg; / CREATE OR REPLACE PACKAGE BODY csh_payment_req_pkg IS v_err_code VARCHAR2(2000); e_lock_trx_error EXCEPTION; e_lock_error EXCEPTION; PRAGMA EXCEPTION_INIT(e_lock_trx_error, -54); PRAGMA EXCEPTION_INIT(e_lock_error, -54); FUNCTION get_bp_id(p_contract_id NUMBER) RETURN NUMBER IS v_bp_id hls_bp_master.bp_id%TYPE; --v_contract_rec con_contract%ROWTYPE; BEGIN --库融和保理的经销商不必输,如果为空则取bp_id_agent_level1 /*SELECT nvl(t.bp_id_agent_level1, t.bp_id_tenant)*/ /*SELECT t.bp_id_tenant INTO v_bp_id FROM con_contract t WHERE t.contract_id = p_contract_id;*/ SELECT t3.bp_id INTO v_bp_id FROM con_contract_bp t2, hls_bp_master t3 --delete by WHERE -- t2.bp_category = 'AGENT' AND t3.bp_id = t2.bp_id AND t2.contract_id = p_contract_id; RETURN v_bp_id; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; PROCEDURE check_cf_after_ins_temp(p_session_id NUMBER, p_user_id NUMBER) IS v_62_count NUMBER; v_52_count NUMBER; v_cf_count NUMBER; v_con_count NUMBER; BEGIN --应付保费个数 SELECT COUNT(1) INTO v_62_count FROM csh_payment_cashflow_id_temp t1, con_contract_cashflow ccc WHERE t1.cashflow_id = ccc.cashflow_id AND ccc.cf_item = 62 AND t1.session_id = p_session_id; --现金流个数 SELECT COUNT(1) INTO v_cf_count FROM csh_payment_cashflow_id_temp t1, con_contract_cashflow ccc WHERE t1.cashflow_id = ccc.cashflow_id AND t1.session_id = p_session_id; --校验 --cancel by shen --IF v_62_count > 0 AND v_cf_count <> v_62_count THEN -- sys_raise_app_error_pkg.raise_sys_others_error(p_message => '应付保费只能单独发起付款申请!', -- p_created_by => p_user_id, -- p_package_name => 'csh_payment_req_pkg', -- p_procedure_function_name => 'check_cf_after_ins_temp'); -- raise_application_error(sys_raise_app_error_pkg.c_error_number, -- sys_raise_app_error_pkg.g_err_line_id); --END IF; IF v_62_count > 0 THEN NULL; ELSE --合同个数 /* SELECT COUNT(1) INTO v_cf_count FROM (SELECT ccc.contract_id FROM csh_payment_cashflow_id_temp t1, con_contract_cashflow ccc WHERE t1.cashflow_id = ccc.cashflow_id AND t1.session_id = p_session_id GROUP BY ccc.contract_id) t; IF v_cf_count > 1 THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '请选择合同号相同的单据!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_cf_after_ins_temp'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END IF;*/ SELECT COUNT(1) INTO v_52_count FROM csh_payment_cashflow_id_temp t1, con_contract_cashflow ccc WHERE t1.cashflow_id = ccc.cashflow_id AND ccc.cf_item IN ('52', '509') AND t1.session_id = p_session_id; IF v_52_count > 0 AND v_cf_count <> v_52_count THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '保证金退还只能单独发起付款申请!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_cf_after_ins_temp'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END IF; END IF; END; PROCEDURE check_cf_0_total_amt(p_contract_id NUMBER, p_user_id NUMBER) IS v_cf_0_total_amt NUMBER; e_cf_0_total_amt_err EXCEPTION; v_contract_rec con_contract%ROWTYPE; v_count NUMBER; v_count1 NUMBER; v_count2 NUMBER; v_count3 NUMBER; v_count4 NUMBER; v_count5 NUMBER; v_count6 NUMBER; BEGIN SELECT * INTO v_contract_rec FROM con_contract t WHERE t.contract_id = p_contract_id FOR UPDATE NOWAIT; SELECT COUNT(1) INTO v_count5 FROM csh_payment_cashflow_id_temp t1 WHERE t1.cashflow_id IN (SELECT ccc.cashflow_id FROM con_contract_cashflow ccc WHERE ccc.contract_id = p_contract_id AND ccc.cf_item IN ('52', '509')); SELECT COUNT(1) INTO v_count6 FROM csh_payment_cashflow_id_temp t1 WHERE t1.cashflow_id IN (SELECT ccc.cashflow_id FROM con_contract_cashflow ccc WHERE ccc.contract_id = p_contract_id); IF v_count5 > 0 AND v_count5 <> v_count6 THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '保证金退还只能单独发起付款申请!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_cf_0_total_amt'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END IF; /* --判断首付款是否支付 SELECT COUNT(1) INTO v_count FROM con_contract_cashflow tt WHERE tt.contract_id = p_contract_id AND tt.cf_item = 2 AND tt.cf_direction = 'INFLOW' AND tt.cf_status = 'RELEASE' AND tt.write_off_flag = 'FULL'; --除首付款延期的合同外,如果首付款现金流(2)没有支付,则不能发起付款申请 IF nvl(v_contract_rec.hd_user_col_v09, 0) NOT IN ('20', '30') AND v_count = 0 THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该合同首付款没有支付,不能发起付款申请!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_cf_0_total_amt'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END IF;*/ --判断合同进项发票是否上传 --未开票时 /*IF v_contract_rec.invoice_uploaded_flag = 'N' THEN --查询现金流表未付款的破碎器成本、机械成本 --获取临时表中符合现金流条件的个数 SELECT COUNT(1) INTO v_count1 FROM csh_payment_cashflow_id_temp t1 WHERE t1.cashflow_id IN (SELECT ccc.cashflow_id FROM con_contract_cashflow ccc WHERE ccc.contract_id = p_contract_id AND ccc.cf_item IN ('5', '998') AND ccc.write_off_flag = 'NOT'); --获取现金流表未付款的破碎器成本、机械成本的个数 SELECT COUNT(1) INTO v_count2 FROM con_contract_cashflow ccc WHERE ccc.contract_id = p_contract_id AND ccc.cf_item IN ('5', '998') AND ccc.write_off_flag = 'NOT'; --如果选择的现金流不包含破碎器成本或者机械成本,则报错 IF v_count1 < v_count2 THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该合同未未开票,请勾选正确的成本进行发起!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_cf_0_total_amt'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END IF; ELSE --已开票时 --查询现金流表未付款的破碎器成本&利润、机械成本&利润 --获取临时表中符合现金流条件的个数 SELECT COUNT(1) INTO v_count3 FROM csh_payment_cashflow_id_temp t1 WHERE t1.cashflow_id IN (SELECT ccc.cashflow_id FROM con_contract_cashflow ccc WHERE ccc.contract_id = p_contract_id AND ccc.cf_item IN ('5', '997', '995', '998') AND ccc.write_off_flag = 'NOT'); --获取现金流表未付款的破碎器成本、机械成本的个数 SELECT COUNT(1) INTO v_count4 FROM con_contract_cashflow ccc WHERE ccc.contract_id = p_contract_id AND ccc.cf_item IN ('5', '997', '995', '998') AND ccc.write_off_flag = 'NOT'; --如果选择的现金流不包含破碎器成本或者机械成本,则报错 IF v_count3 < v_count4 THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该合同未未开票,请勾选正确的成本和利润进行发起!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_cf_0_total_amt'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END IF; END IF;*/ EXCEPTION WHEN e_cf_0_total_amt_err THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CON_CONTRACT_VERIFY_PKG.CONTRACT_UPDATE_CF_0_TOTAL_AMT_ERROR', p_created_by => p_user_id, p_package_name => 'CON_CONTRACT_VERIFY_PKG', p_procedure_function_name => 'CONTRACT_UPDATE_CHECK', p_token_1 => '#contract_number', p_token_value_1 => v_contract_rec.contract_number); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; FUNCTION get_con_last_writeoff_date(p_cashflow_id NUMBER) RETURN DATE IS v_last_write_off_date DATE; BEGIN SELECT MAX(w.write_off_date) INTO v_last_write_off_date FROM csh_write_off w WHERE w.cashflow_id = p_cashflow_id; RETURN v_last_write_off_date; END; FUNCTION get_bp_category_desc(p_contract_id NUMBER, p_bp_id NUMBER) RETURN VARCHAR2 IS v_attribute VARCHAR2(32767); BEGIN FOR r_bp_category_rec IN (SELECT c.description FROM con_contract_bp t, hls_bp_category c WHERE t.bp_category = c.bp_category AND t.contract_id = p_contract_id AND t.bp_id = p_bp_id) LOOP IF instr(v_attribute, r_bp_category_rec.description) = 0 OR instr(v_attribute, r_bp_category_rec.description) IS NULL THEN v_attribute := v_attribute || ',' || r_bp_category_rec.description; END IF; END LOOP; IF v_attribute IS NOT NULL THEN v_attribute := substr(v_attribute, 2); END IF; RETURN v_attribute; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; --索合同行表 PROCEDURE lock_con_contract_cashflow(p_cashflow_id con_contract_cashflow.cashflow_id%TYPE, p_user_id NUMBER, p_con_cashflow_rec OUT con_contract_cashflow%ROWTYPE) IS BEGIN SELECT * INTO p_con_cashflow_rec FROM con_contract_cashflow t WHERE t.cashflow_id = p_cashflow_id FOR UPDATE NOWAIT; EXCEPTION WHEN e_lock_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_WRITE_OFF_PKG.CON_LOCK_ERROR', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'lock_con_contract_cashflow'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --锁csh_transaction PROCEDURE lock_csh_payment_req_hd(p_payment_req_id IN NUMBER, p_user_id IN NUMBER, p_csh_payment_req_hd_rec OUT csh_payment_req_hd%ROWTYPE) IS BEGIN SELECT * INTO p_csh_payment_req_hd_rec FROM csh_payment_req_hd t WHERE t.payment_req_id = p_payment_req_id FOR UPDATE NOWAIT; EXCEPTION WHEN e_lock_trx_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_LOCK', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'lock_csh_payment_req_hd'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; ---zlf ---锁合同 PROCEDURE lock_con_contract_rec(p_contract_id IN NUMBER, p_user_id IN NUMBER, p_con_contract_rec OUT con_contract%ROWTYPE) IS BEGIN SELECT * INTO p_con_contract_rec FROM con_contract t WHERE t.contract_id = p_contract_id FOR UPDATE NOWAIT; EXCEPTION WHEN e_lock_trx_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_LOCK', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'lock_con_contract_rec'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE lock_csh_payment_req_ln(p_payment_req_ln_id IN NUMBER, p_user_id IN NUMBER, p_csh_payment_req_ln_rec OUT csh_payment_req_ln%ROWTYPE) IS BEGIN SELECT * INTO p_csh_payment_req_ln_rec FROM csh_payment_req_ln t WHERE t.payment_req_ln_id = p_payment_req_ln_id FOR UPDATE NOWAIT; EXCEPTION WHEN e_lock_trx_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_LOCK', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'lock_csh_payment_req_ln'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; FUNCTION is_payment_close(v_csh_payment_req_hd_rec IN csh_payment_req_hd%ROWTYPE) RETURN BOOLEAN IS BEGIN IF v_csh_payment_req_hd_rec.closed_flag = 'Y' THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END; FUNCTION is_payment_submitted(v_csh_payment_req_hd_rec IN csh_payment_req_hd%ROWTYPE) RETURN BOOLEAN IS BEGIN IF v_csh_payment_req_hd_rec.submitted_flag = 'Y' THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END; FUNCTION is_payment_pay(v_csh_payment_req_hd_rec IN csh_payment_req_hd%ROWTYPE) RETURN BOOLEAN IS BEGIN IF v_csh_payment_req_hd_rec.approval_status <> 'APPROVED' THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END; PROCEDURE check_csh_payment_bp_unique(p_payment_req_id NUMBER, p_user_id NUMBER) IS v_bp_id NUMBER; BEGIN SELECT DISTINCT l.bp_id INTO v_bp_id FROM csh_payment_req_ln l WHERE l.payment_req_id = p_payment_req_id; EXCEPTION WHEN too_many_rows THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '付款对象必须唯一', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_csh_payment_bp_unique'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE check_csh_payment_bp(p_payment_req_id NUMBER, p_user_id NUMBER) IS v_bp_name VARCHAR2(200); e_ln_bp_error EXCEPTION; BEGIN SELECT hbm.bp_name INTO v_bp_name FROM csh_payment_req_ln l, hls_bp_master hbm WHERE l.payment_req_id = p_payment_req_id AND l.bp_id = hbm.bp_id AND l.ref_doc_category = 'CONTRACT' AND NOT EXISTS (SELECT 1 FROM con_contract_bp ccb WHERE ccb.contract_id = l.ref_doc_id AND ccb.bp_id = l.bp_id AND ccb.enabled_flag = 'Y') AND rownum = 1; RAISE e_ln_bp_error; EXCEPTION WHEN no_data_found THEN NULL; WHEN e_ln_bp_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该付款对象在对应的合同中不存在--' || v_bp_name, p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_csh_payment_bp'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --保证金退还 付款申请 校验 add by Harry 9952 2017/2/24 PROCEDURE check_csh_payment_bond(p_payment_req_id NUMBER, p_user_id NUMBER) IS v_bp_name VARCHAR2(200); e_ln_bp_error EXCEPTION; v_write_off_flag VARCHAR2(30); v_count NUMBER; v_cashflow_id NUMBER; e_flag_or_status_error EXCEPTION; BEGIN SELECT l.ref_doc_line_id INTO v_cashflow_id FROM csh_payment_req_ln l WHERE l.payment_req_id = p_payment_req_id; SELECT c.write_off_flag INTO v_write_off_flag FROM con_contract_cashflow c WHERE c.cashflow_id = v_cashflow_id; SELECT COUNT(*) INTO v_count FROM csh_payment_req_hd h WHERE h.payment_req_id IN (SELECT l.payment_req_id FROM csh_payment_req_ln l WHERE l.ref_doc_line_id = v_cashflow_id) AND h.approval_status = 'APPROVING'; IF v_write_off_flag = 'FULL' OR v_count <> 0 THEN RAISE e_flag_or_status_error; END IF; SELECT hbm.bp_name INTO v_bp_name FROM csh_payment_req_ln l, hls_bp_master hbm WHERE l.payment_req_id = p_payment_req_id AND l.bp_id = hbm.bp_id AND l.ref_doc_category = 'CONTRACT' AND NOT EXISTS (SELECT 1 FROM con_contract_bp ccb WHERE ccb.contract_id = l.ref_doc_id AND ccb.bp_id = l.bp_id AND ccb.enabled_flag = 'Y') AND rownum = 1; RAISE e_ln_bp_error; EXCEPTION WHEN no_data_found THEN NULL; WHEN e_ln_bp_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该付款对象在对应的合同中不存在--' || v_bp_name, p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_csh_payment_bond'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_flag_or_status_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该现金流已核销完成或已提交申请!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_csh_payment_bond'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --关闭申请头 PROCEDURE close_csh_payment_req_hd(p_payment_req_id NUMBER, p_closed_date DATE, p_closed_note VARCHAR2, p_user_id NUMBER) IS v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE; e_status_error EXCEPTION; BEGIN lock_csh_payment_req_hd(p_payment_req_id => p_payment_req_id, p_user_id => p_user_id, p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec); IF NOT is_payment_close(v_csh_payment_req_hd_rec) THEN RAISE e_status_error; END IF; UPDATE csh_payment_req_hd SET closed_flag = 'Y', closed_note = p_closed_note, closed_date = SYSDATE, last_update_date = SYSDATE, last_updated_by = p_user_id WHERE payment_req_id = p_payment_req_id; EXCEPTION WHEN e_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => '', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'close_csh_payment_req_hd'); 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 => 'csh_payment_req_pkg', p_procedure_function_name => 'close_csh_payment_req_hd'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; FUNCTION get_payment_req_number(p_document_type VARCHAR2, p_transaction_date DATE, p_company_id NUMBER, p_user_id NUMBER, p_document_category VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => p_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); END; PROCEDURE csh_payment_req_amount_check(p_cashflow_id NUMBER, p_amount NUMBER, p_company_id NUMBER, p_user_id NUMBER) IS v_residual_amount NUMBER; e_req_amount_check EXCEPTION; e_req_amount_zero_check EXCEPTION; v_amount_flag VARCHAR2(1); v_req_amount NUMBER; v_due_amount NUMBER; v_amount NUMBER; v_amount_paid NUMBER; v_amount_approving NUMBER; BEGIN SELECT nvl(f.due_amount, 0) INTO v_due_amount FROM con_contract_cashflow f WHERE f.cashflow_id = p_cashflow_id; SELECT SUM(l.amount) INTO v_amount FROM csh_payment_req_ln l, csh_payment_req_hd h WHERE l.ref_doc_line_id = p_cashflow_id AND l.payment_req_id = h.payment_req_id AND h.submitted_flag = 'Y' AND h.approval_status = 'APPROVED' AND nvl(h.closed_flag, 'N') <> 'Y'; SELECT SUM(nvl(l.amount_paid, 0)) INTO v_amount_paid FROM csh_payment_req_ln l, csh_payment_req_hd h WHERE l.ref_doc_line_id = p_cashflow_id AND l.payment_req_id = h.payment_req_id AND h.submitted_flag = 'Y' AND h.approval_status = 'APPROVED' AND nvl(h.closed_flag, 'N') = 'Y'; SELECT SUM(l.amount) INTO v_amount_approving FROM csh_payment_req_ln l, csh_payment_req_hd h WHERE l.ref_doc_line_id = p_cashflow_id AND l.payment_req_id = h.payment_req_id AND h.submitted_flag = 'Y' AND h.approval_status = 'APPROVING'; IF p_amount > (v_due_amount - nvl(v_amount, 0) - nvl(v_amount_paid, 0) - nvl(v_amount_approving, 0)) THEN RAISE e_req_amount_check; ELSIF p_amount = 0 OR p_amount IS NULL THEN RAISE e_req_amount_zero_check; END IF; EXCEPTION WHEN e_req_amount_check THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.CSH501_REQ_AMOUNT_CHECK', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'csh_payment_req_amount_check'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_req_amount_zero_check THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.CSH501_REQ_AMOUNT_ZERO_CHECK', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'csh_payment_req_amount_check'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE upd_csh_payment_after_reverse(p_transaction_id csh_transaction.transaction_id%TYPE, p_user_id NUMBER) IS v_csh_transaction_rec csh_transaction%ROWTYPE; v_csh_payment_req_ln_rec csh_payment_req_ln%ROWTYPE; v_amount_paid NUMBER; e_bp_category_err exception; BEGIN csh_transaction_pkg.lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); lock_csh_payment_req_ln(p_payment_req_ln_id => v_csh_transaction_rec.source_doc_line_id, p_user_id => p_user_id, p_csh_payment_req_ln_rec => v_csh_payment_req_ln_rec); --付款总金额字段减少 v_amount_paid := nvl(v_csh_payment_req_ln_rec.amount_paid, 0) - nvl(v_csh_transaction_rec.transaction_amount, 0); IF v_amount_paid - v_csh_payment_req_ln_rec.amount < 0 THEN UPDATE csh_payment_req_ln l SET l.amount_paid = v_amount_paid, --UPDATE BY SHEN --l.payment_status = 'PORTION', l.payment_status = 'PARTIAL', l.payment_completed_date = SYSDATE, l.last_update_date = SYSDATE, l.last_updated_by = p_user_id WHERE l.payment_req_ln_id = v_csh_transaction_rec.source_doc_line_id; ELSIF v_amount_paid = 0 THEN UPDATE csh_payment_req_ln l SET l.amount_paid = v_amount_paid, l.payment_status = 'NOT', l.payment_completed_date = SYSDATE, l.last_update_date = SYSDATE, l.last_updated_by = p_user_id WHERE l.payment_req_ln_id = v_csh_transaction_rec.source_doc_line_id; END IF; --根据bp_category 更新付款行的代理商已付金额和厂商已付金额 --add by shen if v_csh_transaction_rec.bp_category='AGENT' THEN UPDATE csh_payment_req_ln cprl set cprl.amount_agent_paid=nvl(cprl.amount_agent_paid,0)-v_csh_transaction_rec.transaction_amount where cprl.payment_req_ln_id= v_csh_transaction_rec.source_doc_line_id; elsif v_csh_transaction_rec.bp_category='VENDER' THEN UPDATE csh_payment_req_ln cprl set cprl.amount_vender_paid=nvl(cprl.amount_vender_paid,0)-v_csh_transaction_rec.transaction_amount where cprl.payment_req_ln_id= v_csh_transaction_rec.source_doc_line_id; --不是厂商和代理店则报错 Else raise e_bp_category_err; end if; UPDATE csh_payment_req_hd h SET h.closed_flag = 'N' WHERE h.payment_req_id = v_csh_transaction_rec.source_doc_id; --异常处理 Exception WHEN e_bp_category_err THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '付款反冲的对象必须为代理店或者厂商!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'upd_csh_payment_after_reverse'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE insert_csh_trx_source(p_transaction_id NUMBER, p_source_doc_category VARCHAR2, p_source_doc_type VARCHAR2, p_source_doc_id NUMBER, p_source_doc_line_id NUMBER, p_source_amt NUMBER, p_user_id NUMBER) IS BEGIN INSERT INTO csh_transaction_source (transaction_source_id, transaction_id, source_doc_category, source_doc_type, source_doc_id, source_doc_line_id, source_amt, creation_date, created_by, last_update_date, last_updated_by) VALUES (csh_transaction_source_s.nextval, p_transaction_id, p_source_doc_category, p_source_doc_type, p_source_doc_id, p_source_doc_line_id, p_source_amt, SYSDATE, p_user_id, SYSDATE, p_user_id); END; PROCEDURE payment_csh_payment_debt(p_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE, p_csh_payment_req_ln_rec csh_payment_req_ln%ROWTYPE, p_con_cashflow_rec con_contract_cashflow%ROWTYPE, p_pay_date DATE, p_pay_amount NUMBER, p_company_id NUMBER, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_exchange_rate_type csh_transaction.exchange_rate_type%TYPE, p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE, p_exchange_rate csh_transaction.exchange_rate%TYPE, p_bank_account_id csh_transaction.bank_account_id%TYPE, p_bank_account_num VARCHAR2, p_description csh_transaction.description%TYPE, p_user_id NUMBER, p_cashflow_id NUMBER, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_bp_bank_account_id csh_transaction.bp_bank_account_id%TYPE, p_bp_bank_account_num csh_transaction.bp_bank_account_num%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_merge_flag VARCHAR2, p_payment_behalf_flag VARCHAR2, --todo Spencer 3893 add 20160722 for 是否代付核销 p_transaction_id IN OUT NUMBER, p_bp_category varchar2 default null, p_due_amount NUMBER DEFAULT NULL) IS e_trx_num_error EXCEPTION; e_req_amount_check EXCEPTION; v_csh_transaction_rec csh_transaction%ROWTYPE; v_transaction_id NUMBER; v_write_off_id NUMBER; v_count_status NUMBER; v_bp_category VARCHAR2(100); v_amount NUMBER; v_amount_paid NUMBER; v_deduction_flag VARCHAR2(1) := 'N'; v_deduction_amount NUMBER := 0; v_session_id NUMBER; v_bp_bank_account_name csh_transaction.bp_bank_account_name%TYPE; v_ddct_transaction_id NUMBER; BEGIN BEGIN SELECT ba.bank_account_name INTO v_bp_bank_account_name FROM hls_bp_master_bank_account ba WHERE ba.bank_account_id = p_bp_bank_account_id; EXCEPTION WHEN OTHERS THEN NULL; END; /* 不修改参数,使用不重复的id,代替session_id */ v_session_id := -1 * p_csh_payment_req_ln_rec.payment_req_ln_id; BEGIN SELECT 'Y' INTO v_deduction_flag FROM dual WHERE EXISTS (SELECT 1 FROM csh_payment_req_ln_ddct a WHERE a.payment_req_ln_id = p_csh_payment_req_ln_rec.payment_req_ln_id AND nvl(a.deduction_flag, 'N') = 'N'); EXCEPTION WHEN no_data_found THEN v_deduction_flag := 'N'; END; FOR c_deduction_rec IN (SELECT * FROM csh_payment_req_ln_ddct a WHERE a.payment_req_ln_id = p_csh_payment_req_ln_rec.payment_req_ln_id AND nvl(a.deduction_flag, 'N') = 'N') LOOP --insert into csh_write_off_temp IF c_deduction_rec.amount > 0 THEN INSERT INTO csh_write_off_temp (session_id, write_off_type, transaction_category, transaction_type, write_off_date, write_off_due_amount, write_off_principal, write_off_interest, write_off_due_amount_cny, write_off_principal_cny, write_off_interest_cny, exchange_rate, company_id, document_category, document_id, document_line_id, opposite_doc_category, opposite_doc_type, opposite_doc_id, opposite_doc_line_id, opposite_doc_detail_id, opposite_write_off_amount, description, creation_date, created_by, last_update_date, last_updated_by) VALUES (v_session_id, csh_write_off_pkg.write_off_type_receipt_credit, 'CSH_TRX', csh_transaction_pkg.csh_trx_type_deduction, p_pay_date, c_deduction_rec.amount, c_deduction_rec.principal, c_deduction_rec.interest, NULL, NULL, NULL, p_exchange_rate, p_csh_payment_req_hd_rec.company_id, 'CONTRACT', c_deduction_rec.ref_doc_id, c_deduction_rec.ref_doc_line_id, 'PAYMENT_REQ_DDCT', --opposite_doc_category , 'PAYMENT_REQ_DDCT', --opposite_doc_type , p_csh_payment_req_ln_rec.payment_req_id, --opposite_doc_id , p_csh_payment_req_ln_rec.payment_req_ln_id, --opposite_doc_line_id , c_deduction_rec.payment_req_ln_ddct_id, --opposite_doc_detail_id , NULL, --opposite_write_off_amount , NULL, SYSDATE, p_user_id, SYSDATE, p_user_id); END IF; v_deduction_amount := v_deduction_amount + nvl(c_deduction_rec.amount, 0); UPDATE csh_payment_req_ln_ddct SET deduction_flag = 'Y' WHERE payment_req_ln_ddct_id = c_deduction_rec.payment_req_ln_ddct_id; END LOOP; IF v_deduction_flag = 'Y' AND v_deduction_amount > 0 THEN INSERT INTO csh_write_off_temp (session_id, write_off_type, transaction_category, transaction_type, write_off_date, write_off_due_amount, write_off_principal, write_off_interest, write_off_due_amount_cny, write_off_principal_cny, write_off_interest_cny, exchange_rate, company_id, document_category, document_id, document_line_id, description, creation_date, created_by, last_update_date, last_updated_by) VALUES (v_session_id, csh_write_off_pkg.write_off_type_payment_debt, 'CSH_TRX', csh_transaction_pkg.csh_trx_type_deduction, p_pay_date, v_deduction_amount, NULL, NULL, NULL, NULL, NULL, p_exchange_rate, p_csh_payment_req_hd_rec.company_id, 'CONTRACT', p_csh_payment_req_ln_rec.ref_doc_id, p_csh_payment_req_ln_rec.ref_doc_line_id, NULL, SYSDATE, p_user_id, SYSDATE, p_user_id); csh_write_off_pkg.deduction_write_off(p_session_id => v_session_id, p_company_id => p_csh_payment_req_hd_rec.company_id, p_bp_id => p_csh_payment_req_ln_rec.bp_id, p_transaction_date => p_pay_date, p_description => '', p_source_csh_trx_type => '', p_source_csh_trx_id => '', p_source_doc_category => p_csh_payment_req_hd_rec.document_category, p_source_doc_type => p_csh_payment_req_hd_rec.document_type, p_source_doc_id => p_csh_payment_req_hd_rec.payment_req_id, p_source_doc_line_id => p_csh_payment_req_ln_rec.payment_req_ln_id, p_user_id => p_user_id, p_transaction_id => v_ddct_transaction_id); UPDATE csh_payment_req_ln t SET amount_paid = nvl(amount_paid, 0) + v_deduction_amount WHERE t.payment_req_ln_id = p_csh_payment_req_ln_rec.payment_req_ln_id; END IF; --预付款抵扣 FOR c_prepay IN (SELECT p.prepayment_trx_id, p.write_off_amt, p.payment_req_ln_prepay_id FROM csh_payment_req_ln_prepay p WHERE p.payment_req_ln_id = p_csh_payment_req_ln_rec.payment_req_ln_id AND nvl(p.write_off_flag, 'N') = 'N') LOOP csh_write_off_pkg.insert_csh_write_off(p_write_off_id => v_write_off_id, p_write_off_type => csh_write_off_pkg.write_off_type_pre_debt, p_write_off_date => p_pay_date, p_internal_period_num => p_internal_period_num, p_period_name => p_period_name, p_csh_transaction_id => c_prepay.prepayment_trx_id, p_csh_write_off_amount => c_prepay.write_off_amt, p_subsequent_csh_trx_id => '', p_subseq_csh_write_off_amount => '', p_reversed_flag => 'N', p_reversed_write_off_id => '', p_reversed_date => '', p_cashflow_id => p_con_cashflow_rec.cashflow_id, p_contract_id => p_con_cashflow_rec.contract_id, p_times => p_con_cashflow_rec.times, p_cf_item => p_con_cashflow_rec.cf_item, p_cf_type => p_con_cashflow_rec.cf_type, p_penalty_calc_date => '', p_write_off_due_amount => c_prepay.write_off_amt, p_write_off_principal => '', p_write_off_interest => '', p_opposite_doc_category => '', p_opposite_doc_type => '', p_opposite_doc_id => '', p_opposite_doc_line_id => '', p_opposite_doc_detail_id => '', p_opposite_write_off_amount => '', p_create_je_mothed => '', p_create_je_flag => 'N', p_gld_interface_flag => 'N', p_description => p_description, p_user_id => p_user_id); csh_write_off_pkg.execute_write_off(p_write_off_id => v_write_off_id, p_user_id => p_user_id); UPDATE csh_payment_req_ln_prepay p SET p.write_off_flag = 'Y' WHERE p.payment_req_ln_prepay_id = c_prepay.payment_req_ln_prepay_id; UPDATE csh_payment_req_ln t SET amount_paid = nvl(amount_paid, 0) + c_prepay.write_off_amt WHERE t.payment_req_ln_id = p_csh_payment_req_ln_rec.payment_req_ln_id; END LOOP; IF p_pay_amount > 0 THEN --付款核销 delete by shen bp_category 由前面付款的对象决定 /*SELECT m.bp_category INTO v_bp_category FROM hls_bp_master m WHERE m.bp_id = p_csh_payment_req_ln_rec.bp_id;*/ v_bp_category := p_bp_category; IF p_merge_flag = 'Y' THEN IF p_transaction_id IS NULL THEN --第一条 csh_transaction_pkg.insert_csh_transaction(p_transaction_id => v_transaction_id, p_transaction_num => '', p_transaction_category => csh_transaction_pkg.csh_trx_category_business, p_transaction_type => csh_transaction_pkg.csh_trx_type_payment, p_transaction_date => p_pay_date, p_penalty_calc_date => '', p_bank_slip_num => p_bank_slip_num, p_company_id => p_company_id, p_internal_period_num => p_internal_period_num, p_period_name => p_period_name, p_payment_method_id => p_payment_method_id, p_distribution_set_id => '', p_cashflow_amount => -1 * p_pay_amount, p_currency_code => p_csh_payment_req_ln_rec.currency_code, p_transaction_amount => p_pay_amount, p_exchange_rate_type => p_exchange_rate_type, p_exchange_rate_quotation => 'DIRECT QUOTATION', p_exchange_rate => p_exchange_rate, p_bank_account_id => p_bank_account_id, p_bp_category => v_bp_category, p_bp_id => p_csh_payment_req_ln_rec.bp_id, p_bp_bank_account_id => nvl(p_bp_bank_account_id, p_csh_payment_req_ln_rec.bp_bank_account_id), p_bp_bank_account_num => nvl(p_bp_bank_account_num, p_csh_payment_req_ln_rec.bp_bank_account_num), p_description => p_description, p_handling_charge => p_handling_charge, --不需要凭证,直接将过账标志置为Y p_posted_flag => 'Y', p_reversed_flag => 'N', p_reversed_date => '', p_returned_flag => 'NOT', p_returned_amount => '', p_write_off_flag => 'FULL', p_write_off_amount => '', p_full_write_off_date => SYSDATE, p_twin_csh_trx_id => '', p_return_from_csh_trx_id => '', p_reversed_csh_trx_id => '', p_source_csh_trx_type => '', p_source_csh_trx_id => '', p_source_doc_category => p_csh_payment_req_hd_rec.document_category, p_source_doc_type => p_csh_payment_req_hd_rec.document_type, p_source_doc_id => p_csh_payment_req_hd_rec.payment_req_id, p_source_doc_line_id => p_csh_payment_req_ln_rec.payment_req_ln_id, p_create_je_mothed => '', p_create_je_flag => 'N', p_gld_interface_flag => 'N', p_user_id => p_user_id, p_csh_bp_name => '', p_paid_byother_flag => NULL, p_ref_v05 => '付款核销'); p_transaction_id := v_transaction_id; ELSE v_transaction_id := p_transaction_id; UPDATE csh_transaction t SET t.cashflow_amount = t.cashflow_amount + (-1) * p_pay_amount, t.transaction_amount = t.transaction_amount + p_pay_amount WHERE t.transaction_id = v_transaction_id; END IF; csh_write_off_pkg.insert_csh_write_off(p_write_off_id => v_write_off_id, p_write_off_type => csh_write_off_pkg.write_off_type_payment_debt, p_write_off_date => p_pay_date, p_internal_period_num => p_internal_period_num, p_period_name => p_period_name, p_csh_transaction_id => v_transaction_id, p_csh_write_off_amount => p_pay_amount, p_subsequent_csh_trx_id => '', p_subseq_csh_write_off_amount => '', p_reversed_flag => 'N', p_reversed_write_off_id => '', p_reversed_date => '', p_cashflow_id => p_con_cashflow_rec.cashflow_id, p_contract_id => p_con_cashflow_rec.contract_id, p_times => p_con_cashflow_rec.times, p_cf_item => p_con_cashflow_rec.cf_item, p_cf_type => p_con_cashflow_rec.cf_type, p_penalty_calc_date => '', p_write_off_due_amount => p_pay_amount, p_write_off_principal => '', p_write_off_interest => '', p_opposite_doc_category => '', p_opposite_doc_type => '', p_opposite_doc_id => '', p_opposite_doc_line_id => '', p_opposite_doc_detail_id => '', p_opposite_write_off_amount => '', p_create_je_mothed => '', p_create_je_flag => 'N', p_gld_interface_flag => 'N', p_description => p_description, p_user_id => p_user_id, p_due_amount => p_due_amount); csh_write_off_pkg.execute_write_off(p_write_off_id => v_write_off_id, p_user_id => p_user_id); ELSE -- 不合并,逐行处理 csh_transaction_pkg.insert_csh_transaction(p_transaction_id => v_transaction_id, p_transaction_num => '', p_transaction_category => csh_transaction_pkg.csh_trx_category_business, p_transaction_type => csh_transaction_pkg.csh_trx_type_payment, p_transaction_date => p_pay_date, p_penalty_calc_date => '', p_bank_slip_num => p_bank_slip_num, p_company_id => p_company_id, p_internal_period_num => p_internal_period_num, p_period_name => p_period_name, p_payment_method_id => p_payment_method_id, p_distribution_set_id => '', p_cashflow_amount => -1 * p_pay_amount, p_currency_code => p_csh_payment_req_ln_rec.currency_code, p_transaction_amount => p_pay_amount, p_exchange_rate_type => p_exchange_rate_type, p_exchange_rate_quotation => 'DIRECT QUOTATION', p_exchange_rate => p_exchange_rate, p_bank_account_id => p_bank_account_id, p_bp_category => v_bp_category, p_bp_id => p_csh_payment_req_ln_rec.bp_id, p_bp_bank_account_id => nvl(p_bp_bank_account_id, p_csh_payment_req_ln_rec.bp_bank_account_id), p_bp_bank_account_num => nvl(p_bp_bank_account_num, p_csh_payment_req_ln_rec.bp_bank_account_num), p_description => p_description, p_handling_charge => p_handling_charge, p_posted_flag => 'Y', p_reversed_flag => 'N', p_reversed_date => '', p_returned_flag => 'NOT', p_returned_amount => '', p_write_off_flag => 'FULL', p_write_off_amount => '', p_full_write_off_date => SYSDATE, p_twin_csh_trx_id => '', p_return_from_csh_trx_id => '', p_reversed_csh_trx_id => '', p_source_csh_trx_type => '', p_source_csh_trx_id => '', p_source_doc_category => p_csh_payment_req_hd_rec.document_category, p_source_doc_type => p_csh_payment_req_hd_rec.document_type, p_source_doc_id => p_csh_payment_req_hd_rec.payment_req_id, p_source_doc_line_id => p_csh_payment_req_ln_rec.payment_req_ln_id, p_create_je_mothed => '', p_create_je_flag => 'N', p_gld_interface_flag => 'N', p_user_id => p_user_id, p_csh_bp_name => '', p_paid_byother_flag => NULL, p_ref_v05 => '付款核销'); csh_write_off_pkg.insert_csh_write_off(p_write_off_id => v_write_off_id, p_write_off_type => csh_write_off_pkg.write_off_type_payment_debt, p_write_off_date => p_pay_date, p_internal_period_num => p_internal_period_num, p_period_name => p_period_name, p_csh_transaction_id => v_transaction_id, p_csh_write_off_amount => p_pay_amount, p_subsequent_csh_trx_id => '', p_subseq_csh_write_off_amount => '', p_reversed_flag => 'N', p_reversed_write_off_id => '', p_reversed_date => '', p_cashflow_id => p_con_cashflow_rec.cashflow_id, p_contract_id => p_con_cashflow_rec.contract_id, p_times => p_con_cashflow_rec.times, p_cf_item => p_con_cashflow_rec.cf_item, p_cf_type => p_con_cashflow_rec.cf_type, p_penalty_calc_date => '', p_write_off_due_amount => p_pay_amount, p_write_off_principal => '', p_write_off_interest => '', p_opposite_doc_category => '', p_opposite_doc_type => '', p_opposite_doc_id => '', p_opposite_doc_line_id => '', p_opposite_doc_detail_id => '', p_opposite_write_off_amount => '', p_create_je_mothed => '', p_create_je_flag => 'N', p_gld_interface_flag => 'N', p_description => p_description, p_user_id => p_user_id, p_due_amount => p_due_amount); csh_write_off_pkg.execute_write_off(p_write_off_id => v_write_off_id, p_user_id => p_user_id); csh_transaction_pkg.post_csh_transaction(p_transaction_id => v_transaction_id, p_user_id => p_user_id); /* --现金事务核销时,现金事务及核销行 合并生成的凭证 csh_transaction_je_pkg.create_trx_write_off_je(p_transaction_id => v_transaction_id, p_user_id => p_user_id);*/ END IF; p_transaction_id := v_transaction_id; insert_csh_trx_source(p_transaction_id => p_transaction_id, p_source_doc_category => p_csh_payment_req_hd_rec.document_category, p_source_doc_type => p_csh_payment_req_hd_rec.document_type, p_source_doc_id => p_csh_payment_req_hd_rec.payment_req_id, p_source_doc_line_id => p_csh_payment_req_ln_rec.payment_req_ln_id, p_source_amt => p_pay_amount, p_user_id => p_user_id); END IF; END; PROCEDURE payment_csh_payment(p_payment_req_id NUMBER, p_payment_req_ln_id NUMBER, p_pay_date DATE, p_pay_amount NUMBER, p_company_id NUMBER, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_exchange_rate_type csh_transaction.exchange_rate_type%TYPE, p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE, p_exchange_rate csh_transaction.exchange_rate%TYPE, p_bank_account_id csh_transaction.bank_account_id%TYPE, p_bank_account_num VARCHAR2, p_description csh_transaction.description%TYPE, p_user_id NUMBER, p_cashflow_id NUMBER, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_bp_bank_account_id csh_transaction.bp_bank_account_id%TYPE, p_bp_bank_account_num csh_transaction.bp_bank_account_num%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_merge_flag VARCHAR2 DEFAULT 'N', p_payment_behalf_flag VARCHAR2 DEFAULT 'N', p_payment_status OUT VARCHAR2, p_transaction_id IN OUT NUMBER, p_bp_category varchar2 default null, p_due_amount NUMBER DEFAULT NULL) IS v_amount NUMBER; v_amount_paid NUMBER; v_amt_tmp NUMBER; v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE; v_csh_payment_req_ln_rec csh_payment_req_ln%ROWTYPE; e_trx_num_error EXCEPTION; e_req_amount_check EXCEPTION; v_count_status NUMBER; v_con_cashflow_rec con_contract_cashflow%ROWTYPE; v_deduction_flag VARCHAR2(1) := 'N'; v_deduction_amount NUMBER := 0; v_session_id NUMBER; e_status_error EXCEPTION; BEGIN lock_csh_payment_req_hd(p_payment_req_id => p_payment_req_id, p_user_id => p_user_id, p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec); lock_csh_payment_req_ln(p_payment_req_ln_id => p_payment_req_ln_id, p_user_id => p_user_id, p_csh_payment_req_ln_rec => v_csh_payment_req_ln_rec); IF v_csh_payment_req_ln_rec.payment_req_ln_type = 'DEBT' THEN lock_con_contract_cashflow(p_cashflow_id => v_csh_payment_req_ln_rec.ref_doc_line_id, p_user_id => p_user_id, p_con_cashflow_rec => v_con_cashflow_rec); END IF; --判断是否是审批通过的单据 IF NOT is_payment_pay(v_csh_payment_req_hd_rec) THEN RAISE e_status_error; END IF; --本次申请金额字段,分开为厂商和代理店 IF p_pay_amount < 0 THEN RAISE e_req_amount_check; END IF; SELECT nvl((SELECT SUM(d.amount) FROM csh_payment_req_ln_ddct d, con_contract_cashflow cf WHERE d.payment_req_ln_id = p_payment_req_ln_id AND nvl(d.deduction_flag, 'N') = 'N' AND nvl(cf.payment_deduction_flag, 'N') = 'Y' AND cf.write_off_flag != 'FULL' AND d.ref_doc_category = 'CONTRACT' AND d.ref_doc_id = cf.contract_id AND d.ref_doc_line_id = cf.cashflow_id), --预支付,付款这里没有用到 0) + nvl((SELECT SUM(p.write_off_amt) FROM csh_payment_req_ln_prepay p WHERE p.payment_req_ln_id = p_payment_req_ln_id AND nvl(p.write_off_flag, 'N') = 'N'), 0) INTO v_amt_tmp FROM dual; --已付款加上本次付款加上抵扣金额 和申请金额进行比较 IF v_amt_tmp + p_pay_amount + nvl(v_csh_payment_req_ln_rec.amount_paid, 0) > v_csh_payment_req_ln_rec.amount THEN RAISE e_req_amount_check; END IF; IF v_csh_payment_req_ln_rec.payment_req_ln_type = 'DEBT' THEN payment_csh_payment_debt(p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec, p_csh_payment_req_ln_rec => v_csh_payment_req_ln_rec, p_con_cashflow_rec => v_con_cashflow_rec, p_pay_date => p_pay_date, p_pay_amount => p_pay_amount, p_company_id => p_company_id, p_internal_period_num => p_internal_period_num, p_period_name => p_period_name, p_exchange_rate_type => p_exchange_rate_type, p_exchange_rate_quotation => p_exchange_rate_quotation, p_exchange_rate => p_exchange_rate, p_bank_account_id => p_bank_account_id, p_bank_account_num => p_bank_account_num, p_description => p_description, p_user_id => p_user_id, p_cashflow_id => p_cashflow_id, p_bank_slip_num => p_bank_slip_num, p_bp_bank_account_id => p_bp_bank_account_id, p_bp_bank_account_num => p_bp_bank_account_num, p_handling_charge => p_handling_charge, p_payment_method_id => p_payment_method_id, p_merge_flag => p_merge_flag, p_payment_behalf_flag => p_payment_behalf_flag, p_transaction_id => p_transaction_id, -- by shen p_bp_category => p_bp_category, p_due_amount => p_due_amount); ELSE RETURN; END IF; SELECT nvl(amount_paid, 0), nvl(amount, 0) INTO v_amount_paid, v_amount FROM csh_payment_req_ln WHERE payment_req_ln_id = p_payment_req_ln_id; IF p_pay_amount + v_amount_paid = v_amount THEN UPDATE csh_payment_req_ln l SET l.payment_status = 'FULL', l.amount_paid = nvl(l.amount_paid, 0) + nvl(p_pay_amount, 0), l.payment_completed_date = SYSDATE, l.last_update_date = SYSDATE, l.last_updated_by = p_user_id WHERE l.payment_req_ln_id = p_payment_req_ln_id; p_payment_status := 'FULL'; ELSIF p_pay_amount + v_amount_paid > v_amount THEN RAISE e_req_amount_check; ELSE UPDATE csh_payment_req_ln l SET l.payment_status = 'PARTIAL', l.amount_paid = nvl(l.amount_paid, 0) + nvl(p_pay_amount, 0), l.payment_completed_date = NULL, l.last_update_date = SYSDATE, l.last_updated_by = p_user_id WHERE l.payment_req_ln_id = p_payment_req_ln_id; p_payment_status := 'PARTIAL'; END IF; SELECT COUNT(*) INTO v_count_status FROM csh_payment_req_ln l WHERE l.payment_req_id = p_payment_req_id AND l.payment_status <> 'FULL'; --关闭申请头状态 IF v_count_status = 0 THEN UPDATE csh_payment_req_hd SET closed_flag = 'Y', closed_date = SYSDATE, last_update_date = SYSDATE, last_updated_by = p_user_id WHERE payment_req_id = p_payment_req_id; END IF; EXCEPTION WHEN e_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.PAYMENT_CSH_PAYMENT_ERROR', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'payment_csh_payment'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_req_amount_check THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.CSH501_PAYMENT_AMOUNT_CHECK', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'payment_csh_payment'); 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 => 'csh_payment_req_pkg', p_procedure_function_name => 'payment_csh_payment'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE payment_csh_payment(p_payment_req_id NUMBER, p_session_id NUMBER, p_pay_date DATE, p_pay_amount NUMBER, p_company_id NUMBER, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_exchange_rate_type csh_transaction.exchange_rate_type%TYPE, p_exchange_rate_quotation csh_transaction.exchange_rate_quotation%TYPE, p_exchange_rate csh_transaction.exchange_rate%TYPE, p_bank_account_id csh_transaction.bank_account_id%TYPE, p_bank_account_num VARCHAR2, p_description csh_transaction.description%TYPE, p_user_id NUMBER, p_cashflow_id NUMBER, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_bp_bank_account_id csh_transaction.bp_bank_account_id%TYPE, p_bp_bank_account_num csh_transaction.bp_bank_account_num%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_payment_status OUT VARCHAR2, p_merge_flag VARCHAR2 DEFAULT 'N', p_payment_behalf_flag VARCHAR2 DEFAULT 'N', p_this_pay_agent_amount number, p_this_pay_vender_amount number, p_bank_flag VARCHAR2 DEFAULT 'N', p_bp_category varchar2 default null, p_due_amount NUMBER DEFAULT NULL) IS v_payment_status VARCHAR2(30); v_transaction_id NUMBER; e_pay_check_error EXCEPTION; v_bank_status VARCHAR2(30); v_already_agent_paid number; v_already_vender_paid number; BEGIN /*for c_req_group in (select l.payment_req_ln_type,sum(t.amt) as pay_amt from csh_payment_req_ln_tmp t, csh_payment_req_ln l where t.session_id = p_sessioN_id and t.type = 'PAYMENT_REQ_PAYMENT' and l.payment_req_ln_id = t.id and l.payment_req_id = p_payment_req_id group by l.payment_req_ln_type) loop if c_req_group.payment_req_ln_type = 'PREPAYMENT' then null; elsif c_req_group.payment_req_ln_type = 'DEBT' then null; end if; end loop; */ IF p_bank_flag = 'N' THEN --如果不是银行付款,需要检测是否已经发起银行交易 SELECT h.nc_pay_state INTO v_bank_status FROM csh_payment_req_hd h WHERE h.payment_req_id = p_payment_req_id; IF v_bank_status IS NOT NULL THEN RAISE e_pay_check_error; END IF; END IF; FOR c_req_group IN (SELECT l.payment_req_ln_type, l.bp_id, l.currency_code FROM csh_payment_req_ln_tmp t, csh_payment_req_ln l WHERE t.session_id = p_session_id AND t.type = 'PAYMENT_REQ_PAYMENT' AND t.id = l.payment_req_ln_id GROUP BY l.payment_req_ln_type, l.bp_id, l.currency_code) LOOP v_transaction_id := NULL; FOR c_req IN (SELECT t.id, t.amt FROM csh_payment_req_ln_tmp t, csh_payment_req_ln l WHERE t.session_id = p_session_id AND t.type = 'PAYMENT_REQ_PAYMENT' AND t.id = l.payment_req_ln_id AND l.payment_req_ln_type = c_req_group.payment_req_ln_type AND l.bp_id = c_req_group.bp_id AND l.currency_code = c_req_group.currency_code) LOOP payment_csh_payment(p_payment_req_id => p_payment_req_id, p_payment_req_ln_id => c_req.id, p_pay_date => p_pay_date, p_pay_amount => c_req.amt, p_company_id => p_company_id, p_internal_period_num => p_internal_period_num, p_period_name => p_period_name, p_exchange_rate_type => p_exchange_rate_type, p_exchange_rate_quotation => p_exchange_rate_quotation, p_exchange_rate => p_exchange_rate, p_bank_account_id => p_bank_account_id, p_bank_account_num => p_bank_account_num, p_description => p_description, p_user_id => p_user_id, p_cashflow_id => p_cashflow_id, p_bank_slip_num => p_bank_slip_num, p_bp_bank_account_id => p_bp_bank_account_id, p_bp_bank_account_num => p_bp_bank_account_num, p_handling_charge => p_handling_charge, p_payment_method_id => p_payment_method_id, p_payment_status => v_payment_status, p_merge_flag => p_merge_flag, p_payment_behalf_flag => p_payment_behalf_flag, p_transaction_id => v_transaction_id, --add by shen p_bp_category => p_bp_category, p_due_amount => p_due_amount); --查询已付款金额 select cprl.amount_agent_paid,cprl.amount_vender_paid into v_already_agent_paid,v_already_vender_paid from csh_payment_req_ln cprl where cprl.payment_req_ln_id=c_req.id for update; --更新金额字段 update csh_payment_req_ln cprl set cprl.amount_agent_paid=nvl(v_already_agent_paid,0)+nvl(p_this_pay_agent_amount,0), cprl.amount_vender_paid=nvl(v_already_vender_paid,0)+nvl(p_this_pay_vender_amount,0) where cprl.payment_req_ln_id=c_req.id; END LOOP; IF p_merge_flag = 'Y' THEN csh_transaction_pkg.post_csh_transaction(p_transaction_id => v_transaction_id, p_user_id => p_user_id); csh_transaction_je_pkg.create_trx_write_off_je(p_transaction_id => v_transaction_id, p_user_id => p_user_id); END IF; END LOOP; DELETE FROM csh_payment_req_ln_tmp WHERE session_id = p_session_id AND TYPE = 'PAYMENT_REQ_PAYMENT'; --20170216 JAINGLEI 放款成功短信 /*hls_sys_sms_job_pkg.generate_pay_succuess_sms(p_payment_req_id => p_payment_req_id, p_user_id => p_user_id);*/ EXCEPTION WHEN e_pay_check_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '已经发起银行付款,不允许手工支付', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'payment_csh_payment'); 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 => 'csh_payment_req_pkg', p_procedure_function_name => 'payment_csh_payment'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE payment_submit(p_payment_req_id NUMBER, p_user_id NUMBER) IS v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE; e_status_error EXCEPTION; v_approval_method hls_document_type.approval_method%TYPE; v_credit_flag VARCHAR2(100); e_credit_error EXCEPTION; v_hd_user_col_v05 NUMBER; v_count NUMBER; v_mortgage_flag VARCHAR2(1); e_mortgage_error EXCEPTION; v_division VARCHAR2(2); v_con_contract_rec con_contract%ROWTYPE; BEGIN lock_csh_payment_req_hd(p_payment_req_id => p_payment_req_id, p_user_id => p_user_id, p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec); --付款前校验支付表代扣账户信息是否维护 modify by lpc for deron /*BEGIN SELECT cc.* INTO v_con_contract_rec FROM con_contract cc WHERE cc.contract_id = v_csh_payment_req_hd_rec.contract_id; IF v_con_contract_rec.deduction_flag IS NULL OR v_con_contract_rec.bankacctname IS NULL OR v_con_contract_rec.bankacctid IS NULL OR v_con_contract_rec.bankid IS NULL OR v_con_contract_rec.acctype IS NULL OR v_con_contract_rec.bank_account_id IS NULL THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '付款前请先去支付表代扣账户维护功能维护账户信息!', p_created_by => p_user_id, p_package_name => 'prj_project_workflow_pkg', p_procedure_function_name => 'workflow_start'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END IF; EXCEPTION 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_workflow_pkg', p_procedure_function_name => 'workflow_start'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --校验授信额度 add by zhuxianfei 20180130 v_credit_flag := hls_bp_master_credit_pkg.calc_overdue_amount_credit(p_bp_id => v_csh_payment_req_hd_rec.bp_id, p_finance_amount => v_csh_payment_req_hd_rec.amount); IF v_credit_flag = 'N' THEN RAISE e_credit_error; END IF; --产品方案中是先抵押后放款时需要检验抵押信息 add by zhuxianfei 20180226 --modify begin BEGIN SELECT --ppd.PRODUCT_NUMBER, ppd.hd_user_col_v05 INTO v_hd_user_col_v05 FROM hls_product_plan_definition ppd WHERE ppd.product_plan_id IN (SELECT pq.product_id FROM prj_quotation pq WHERE pq.document_id = (SELECT cc.project_id FROM con_contract cc WHERE cc.contract_id = v_csh_payment_req_hd_rec.contract_id)); EXCEPTION WHEN no_data_found THEN NULL; END; SELECT division INTO v_division FROM con_contract WHERE contract_id = v_csh_payment_req_hd_rec.contract_id; -- 1:先抵押后付款 IF v_hd_user_col_v05 = 1 AND v_division <> '02' THEN FOR lease_rec IN (SELECT ci.contract_lease_item_id FROM con_contract_lease_item ci WHERE ci.contract_id = v_csh_payment_req_hd_rec.contract_id) LOOP BEGIN SELECT clm.contract_mortgage_id INTO v_count FROM con_contract_lease_mortgage clm WHERE clm.contract_lease_item_id = lease_rec.contract_lease_item_id; EXCEPTION WHEN no_data_found THEN v_count := 0; END; IF v_count = 0 THEN RAISE e_mortgage_error; END IF; END LOOP; END IF; --modify end --校验可抵扣金额 check_ddct_amount(p_payment_req_id => p_payment_req_id); */ IF NOT is_payment_submitted(v_csh_payment_req_hd_rec) THEN RAISE e_status_error; END IF; FOR csh_payment_req_ln_rec IN (SELECT * FROM csh_payment_req_ln l WHERE l.payment_req_id = p_payment_req_id) LOOP csh_payment_req_amount_check(p_cashflow_id => csh_payment_req_ln_rec.ref_doc_line_id, p_amount => csh_payment_req_ln_rec.amount, p_company_id => NULL, p_user_id => p_user_id); END LOOP; SELECT COUNT(1) INTO v_count FROM csh_payment_req_ln t WHERE t.payment_req_id = p_payment_req_id; IF nvl(v_count, 0) < 1 THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '未找到付款申请数据,请检查数据', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'payment_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END IF; SELECT approval_method INTO v_approval_method FROM hls_document_type WHERE document_category = v_csh_payment_req_hd_rec.document_category AND document_type = v_csh_payment_req_hd_rec.document_type; /*if v_approval_method = 'WORK_FLOW' then*/ --启动工作流 csh_payment_workflow_pkg.workflow_start(p_payment_req_id, p_user_id); /*else update csh_payment_req_hd set approval_status = 'APPROVING', last_updated_by = p_user_id, last_update_date = sysdate where payment_req_id = p_payment_req_id; end if;*/ UPDATE csh_payment_req_hd t SET t.submitted_flag = 'Y' WHERE t.payment_req_id = p_payment_req_id; EXCEPTION WHEN e_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.SUBMIT_CSH_PAYMENT_ERROR', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'payment_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_credit_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该商业伙伴的授信额度已占用完毕,无可使用额度,请核查!', p_created_by => p_user_id, p_package_name => 'prj_project_workflow_pkg', p_procedure_function_name => 'workflow_start'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_mortgage_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该产品为先抵押后付款模式,请先维护租赁物抵押信息!', p_created_by => p_user_id, p_package_name => 'prj_project_workflow_pkg', p_procedure_function_name => 'workflow_start'); 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 => 'csh_payment_req_pkg', p_procedure_function_name => 'payment_submit'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE ins_csh_pay_cashflow_id_temp(p_session_id NUMBER, p_cashflow_id NUMBER, p_user_id NUMBER) IS v_contract_cashflow_rec con_contract_cashflow%ROWTYPE; e_amount_before_confirm EXCEPTION; v_contract_id NUMBER; BEGIN v_contract_cashflow_rec := con_contract_pkg.get_cashflow_rec(p_cashflow_id => p_cashflow_id, p_user_id => p_user_id); /* IF v_contract_cashflow_rec.estimated_due_amount IS NULL AND v_contract_cashflow_rec.cf_status = 'RELEASE' THEN RAISE e_amount_before_confirm; END IF;*/ INSERT INTO csh_payment_cashflow_id_temp VALUES (p_session_id, p_cashflow_id, SYSDATE, p_user_id, SYSDATE, p_user_id, v_contract_cashflow_rec.contract_id); --插入付款头表 EXCEPTION WHEN e_amount_before_confirm THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.AMOUNT_BEFORE_CONFIRM', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'ins_csh_pay_cashflow_id_temp'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE del_csh_pay_cashflow_id_temp(p_session_id NUMBER, p_user_id NUMBER) IS BEGIN DELETE FROM csh_payment_cashflow_id_temp t WHERE t.session_id = p_session_id OR (SYSDATE - t.creation_date) > 1; 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 => 'csh_payment_req_pkg', p_procedure_function_name => 'del_csh_pay_cashflow_id_temp'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END del_csh_pay_cashflow_id_temp; PROCEDURE ins_csh_payment_req_hd(p_payment_req_id IN OUT NUMBER, p_company_id NUMBER, p_document_type VARCHAR2, p_payment_req_number VARCHAR2, p_req_date DATE, p_apply_pay_date DATE, p_transaction_category VARCHAR2, p_distribution_set_id NUMBER, p_payment_method_id NUMBER, p_bp_id NUMBER, p_bp_category VARCHAR2, p_amount NUMBER, p_currency_code VARCHAR2, p_description VARCHAR2, p_submitted_flag VARCHAR2, p_printed_flag VARCHAR2, p_printed_times NUMBER, p_last_print_date DATE, p_closed_flag VARCHAR2, p_closed_date DATE, p_closed_note VARCHAR2, p_approval_status VARCHAR2, p_approval_date DATE, p_approval_note VARCHAR2, p_bp_bank_account_id NUMBER DEFAULT NULL, p_bp_bank_account_num VARCHAR2 DEFAULT NULL, p_bp_bank_account_name VARCHAR2 DEFAULT NULL, p_business_type VARCHAR2 DEFAULT NULL, p_branch_id NUMBER, p_city_id NUMBER, p_user_id NUMBER, p_csh_bp_name VARCHAR2 DEFAULT NULL) IS v_payment_req_id NUMBER; v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE; v_payment_req_number VARCHAR2(100); v_bank_full_name VARCHAR2(1000); BEGIN IF p_payment_req_id IS NULL THEN v_payment_req_number := get_payment_req_number(p_document_type => p_document_type, p_transaction_date => SYSDATE, p_company_id => p_company_id, p_user_id => p_user_id, p_document_category => 'PAYMENT_REQ'); SELECT csh_payment_req_hd_s.nextval INTO p_payment_req_id FROM dual; --add by Harry 9952 2017/2/28 创建付款申请时,添加头表开户行字段 SELECT hp.bank_branch_name INTO v_bank_full_name FROM hls_bp_master_bank_account hp WHERE hp.bank_account_id = p_bp_bank_account_id; INSERT INTO csh_payment_req_hd (payment_req_id, company_id, document_type, document_category, payment_req_number, req_date, apply_pay_date, transaction_category, distribution_set_id, payment_method_id, bp_id, bp_category, amount, currency_code, description, submitted_flag, printed_flag, printed_times, last_print_date, closed_flag, closed_date, closed_note, approval_status, approval_date, approval_note, owner_user_id, creation_date, created_by, last_update_date, last_updated_by, bp_bank_account_id, bp_bank_account_num, bp_bank_account_name, business_type, bank_full_name, branch_id, city_id) VALUES (p_payment_req_id, p_company_id, p_document_type, 'PAYMENT_REQ', v_payment_req_number, nvl(p_req_date, SYSDATE), p_apply_pay_date, 'BUSINESS', '', '', p_bp_id, p_bp_category, p_amount, p_currency_code, p_description, 'N', 'N', '', '', 'N', '', '', 'NEW', '', '', p_user_id, SYSDATE, p_user_id, SYSDATE, p_user_id, p_bp_bank_account_id, p_bp_bank_account_num, p_bp_bank_account_name, p_business_type, v_bank_full_name, p_branch_id, p_city_id); --增加权限 aut_document_authority_pkg.insert_trx_user_authority(p_company_id => p_company_id, p_owner_user_id => p_user_id, p_trx_category => 'PAYMENT_REQ', p_trx_id => p_payment_req_id, p_start_date => trunc(SYSDATE), p_end_date => to_date('30000101', 'yyyymmdd'), p_user_id => p_user_id); --提交状态更新 /* payment_submit(p_payment_req_id => p_payment_req_id, p_user_id => p_user_id);*/ ELSE UPDATE csh_payment_req_hd t1 SET t1.req_date = nvl(p_req_date, SYSDATE), t1.apply_pay_date = p_apply_pay_date, t1.bp_id = p_bp_id, t1.bp_category = p_bp_category, t1.amount = p_amount, t1.currency_code = p_currency_code, t1.description = p_description, t1.last_update_date = SYSDATE, t1.last_updated_by = p_user_id WHERE t1.payment_req_id = p_payment_req_id; END IF; EXCEPTION WHEN OTHERS THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' || SQLERRM, p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'ins_csh_payment_req_hd'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE ins_csh_payment_req_ln(p_payment_req_ln_id IN OUT NUMBER, p_payment_req_id NUMBER, p_payment_req_ln_type VARCHAR2, p_ref_doc_category VARCHAR2, p_ref_doc_id NUMBER, p_ref_doc_line_id NUMBER, p_bp_id NUMBER, p_currency_code VARCHAR2, p_amount NUMBER, p_amount_paid NUMBER, p_description VARCHAR2, p_payment_status VARCHAR2, p_payment_completed_date DATE, p_apply_pay_date DATE, p_payment_method_id NUMBER, p_bank_account_id NUMBER, p_bank_account_num VARCHAR2, p_bank_account_name VARCHAR2, p_bank_branch_name VARCHAR2, p_bp_bank_account_id NUMBER DEFAULT NULL, p_bp_bank_account_num VARCHAR2 DEFAULT NULL, p_bp_bank_account_name VARCHAR2 DEFAULT NULL, p_user_id NUMBER, p_company_id NUMBER) IS --v_amount_flag varchar2(1) ; v_document_category con_contract.document_category%TYPE; BEGIN --申请金额校验 csh_payment_req_amount_check(p_cashflow_id => p_ref_doc_line_id, p_amount => p_amount, p_company_id => p_company_id, p_user_id => p_user_id); SELECT t.document_category INTO v_document_category FROM con_contract t WHERE t.contract_id = p_ref_doc_id FOR UPDATE NOWAIT; IF p_payment_req_ln_id IS NULL THEN SELECT csh_payment_req_ln_s.nextval INTO p_payment_req_ln_id FROM dual; INSERT INTO csh_payment_req_ln (payment_req_ln_id, payment_req_id, payment_req_ln_type, ref_doc_category, ref_doc_id, ref_doc_line_id, bp_id, currency_code, amount, amount_paid, description, payment_status, payment_completed_date, apply_pay_date, payment_method_id, bank_account_id, bank_account_num, bank_account_name, bank_branch_name, bp_bank_account_id, bp_bank_account_num, bp_bank_account_name, creation_date, created_by, last_update_date, last_updated_by) VALUES (p_payment_req_ln_id, p_payment_req_id, 'DEBT', v_document_category, p_ref_doc_id, p_ref_doc_line_id, p_bp_id, p_currency_code, p_amount, p_amount_paid, p_description, 'NOT', p_payment_completed_date, p_apply_pay_date, p_payment_method_id, p_bank_account_id, p_bank_account_num, p_bank_account_name, p_bank_branch_name, p_bp_bank_account_id, p_bp_bank_account_num, p_bp_bank_account_name, SYSDATE, p_user_id, SYSDATE, p_user_id); ELSE UPDATE csh_payment_req_ln t1 SET t1.bp_id = p_bp_id, t1.currency_code = p_currency_code, t1.amount = p_amount, t1.amount_paid = p_amount_paid, t1.description = p_description, t1.apply_pay_date = p_apply_pay_date, t1.payment_method_id = p_payment_method_id, t1.bank_account_id = p_bank_account_id, t1.bank_account_num = p_bank_account_num, t1.bank_account_name = p_bank_account_name, t1.bank_branch_name = p_bank_branch_name, t1.bp_bank_account_id = p_bp_bank_account_id, t1.bp_bank_account_num = p_bp_bank_account_num, t1.bp_bank_account_name = p_bp_bank_account_name, t1.last_update_date = SYSDATE, t1.last_updated_by = p_user_id WHERE t1.payment_req_ln_id = p_payment_req_ln_id; END IF; EXCEPTION WHEN OTHERS THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' || SQLERRM, p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'ins_csh_payment_req_ln'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE del_csh_payment_req_ln(p_payment_req_ln_id NUMBER, p_user_id NUMBER) IS v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE; v_csh_payment_req_ln_rec csh_payment_req_ln%ROWTYPE; e_status_error EXCEPTION; BEGIN lock_csh_payment_req_ln(p_payment_req_ln_id => p_payment_req_ln_id, p_user_id => p_user_id, p_csh_payment_req_ln_rec => v_csh_payment_req_ln_rec); lock_csh_payment_req_hd(p_payment_req_id => v_csh_payment_req_ln_rec.payment_req_id, p_user_id => p_user_id, p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec); /*if not is_payment_submitted(v_csh_payment_req_hd_rec) then raise e_status_error; end if;*/ IF nvl(v_csh_payment_req_ln_rec.payment_status, 'NOT') <> 'NOT' THEN RAISE e_status_error; END IF; DELETE FROM csh_payment_req_ln l WHERE l.payment_req_ln_id = p_payment_req_ln_id; EXCEPTION WHEN e_status_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该付款申请行已支付,不能删除', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'del_csh_payment_req_ln'); 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 => 'csh_payment_req_pkg', p_procedure_function_name => 'del_csh_payment_req_ln'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE del_csh_payment_req_hd(p_payment_req_id NUMBER, p_user_id NUMBER) IS v_csh_payment_req_hd_rec csh_payment_req_hd%ROWTYPE; e_status_error EXCEPTION; BEGIN lock_csh_payment_req_hd(p_payment_req_id => p_payment_req_id, p_user_id => p_user_id, p_csh_payment_req_hd_rec => v_csh_payment_req_hd_rec); IF NOT is_payment_submitted(v_csh_payment_req_hd_rec) THEN RAISE e_status_error; END IF; --删除抵扣信息 modify by zhuxianfei 20180323 --begin DELETE FROM csh_payment_req_ln_ddct ld WHERE ld.ref_doc_line_id IN (SELECT t1.ref_doc_line_id FROM csh_payment_req_ln_ddct_lv t1 WHERE payment_req_id = p_payment_req_id); --end DELETE FROM csh_payment_req_hd h WHERE h.payment_req_id = p_payment_req_id; DELETE FROM csh_payment_req_ln l WHERE l.payment_req_id = p_payment_req_id; EXCEPTION WHEN e_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.DELETE_CSH_PAYMENT_ERROR', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'del_csh_payment_req_hd'); 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 => 'csh_payment_req_pkg', p_procedure_function_name => 'del_csh_payment_req_hd'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE change_print_status(p_contract_id NUMBER, p_user_id NUMBER) IS v_con_contract_rec con_contract%ROWTYPE; BEGIN lock_con_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id, p_con_contract_rec => v_con_contract_rec); UPDATE con_contract t SET t.pay_req_print_status = 'PRINT', t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.contract_id = v_con_contract_rec.contract_id; END; PROCEDURE change_printed_status(p_contract_id NUMBER, p_user_id NUMBER) IS v_con_contract_rec con_contract%ROWTYPE; BEGIN /* lock_con_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id, p_con_contract_rec => v_con_contract_rec);*/ UPDATE con_contract t SET t.pay_req_print_status = 'PRINTED', t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.contract_id = p_contract_id; END; PROCEDURE change_applied_status(p_contract_id NUMBER, p_user_id NUMBER) IS v_con_contract_rec con_contract%ROWTYPE; BEGIN lock_con_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id, p_con_contract_rec => v_con_contract_rec); UPDATE con_contract t SET t.pay_req_print_status = 'APPLIED', t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.contract_id = v_con_contract_rec.contract_id; END; PROCEDURE csh_payment_apply(p_contract_id NUMBER, p_user_id NUMBER) IS v_con_contract_rec con_contract%ROWTYPE; v_pay_req_print_status VARCHAR2(30); v_inception_of_lease DATE; e_lease_date_null EXCEPTION; e_print_status_error EXCEPTION; BEGIN lock_con_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id, p_con_contract_rec => v_con_contract_rec); v_pay_req_print_status := v_con_contract_rec.pay_req_print_status; v_inception_of_lease := v_con_contract_rec.inception_of_lease; IF v_pay_req_print_status IN ('PRINT', 'PRINTED', 'APPLIED') THEN RAISE e_print_status_error; END IF; IF v_inception_of_lease IS NULL OR v_inception_of_lease = '' THEN RAISE e_lease_date_null; END IF; change_applied_status(p_contract_id => v_con_contract_rec.contract_id, p_user_id => p_user_id); EXCEPTION WHEN e_print_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.PAYMENT_REQ_PRINT_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'csh_payment_apply'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_lease_date_null THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.LEASE_DATE_NULL', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'csh_payment_apply'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE csh_payment_print(p_contract_id NUMBER, p_user_id NUMBER) IS v_con_contract_rec con_contract%ROWTYPE; v_pay_req_print_status VARCHAR2(30); e_req_print_status_error EXCEPTION; BEGIN lock_con_contract_rec(p_contract_id => p_contract_id, p_user_id => p_user_id, p_con_contract_rec => v_con_contract_rec); v_pay_req_print_status := v_con_contract_rec.pay_req_print_status; /* if v_pay_req_print_status is null or v_pay_req_print_status != 'PRINT' then raise e_req_print_status_error; end if;*/ change_printed_status(p_contract_id => v_con_contract_rec.contract_id, p_user_id => p_user_id); --for doc status yonda_doc_history_pkg.yonda_insert_doc_status(p_document_id => v_con_contract_rec.contract_id, p_document_type => v_con_contract_rec.document_type, p_document_category => v_con_contract_rec.document_category, p_doc_status => yonda_doc_history_pkg.yonda_con_payment_doc_print, p_instance_id => NULL, p_user_id => p_user_id); EXCEPTION WHEN e_req_print_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_PAYMENT_REQ_PKG.REQ_PRINT_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'csh_payment_print'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE update_csh_payment_flag(p_payment_req_id NUMBER, p_payment_flag VARCHAR2, p_user_id NUMBER) IS BEGIN UPDATE csh_payment_req_hd hd SET hd.payment_flag = p_payment_flag, hd.last_update_date = SYSDATE, hd.last_updated_by = p_user_id WHERE hd.payment_req_id = p_payment_req_id; END; PROCEDURE update_payment_req_contract_id(p_payment_req_id IN NUMBER, p_payment_req_number IN VARCHAR2) IS BEGIN UPDATE csh_payment_req_hd h SET h.payment_req_number = p_payment_req_number WHERE h.payment_req_id = p_payment_req_id; END; --创建cdd_list_id PROCEDURE create_cdd(p_payment_req_id NUMBER, p_user_id NUMBER, p_cdd_list_id IN OUT NUMBER) IS v_csh_payment_req_record csh_payment_req_hd%ROWTYPE; BEGIN lock_csh_payment_req_hd(p_payment_req_id => p_payment_req_id, p_user_id => p_user_id, p_csh_payment_req_hd_rec => v_csh_payment_req_record); IF p_cdd_list_id IS NULL THEN save_cdd_item_from_templt(p_document_category => v_csh_payment_req_record.document_category, p_document_type => v_csh_payment_req_record.document_type, p_document_id => p_payment_req_id, p_company_id => v_csh_payment_req_record.company_id, p_user_id => p_user_id, p_cdd_list_id => p_cdd_list_id); UPDATE csh_payment_req_hd d SET d.cdd_list_id = p_cdd_list_id, d.last_updated_by = p_user_id, d.last_update_date = SYSDATE WHERE d.payment_req_id = p_payment_req_id AND d.cdd_list_id IS NULL; END IF; 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_user_id NUMBER, p_cdd_list_id IN OUT NUMBER) IS v_csh_payment_req_record csh_payment_req_hd%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); v_bp_class VARCHAR2(20); BEGIN lock_csh_payment_req_hd(p_payment_req_id => p_document_id, p_user_id => p_user_id, p_csh_payment_req_hd_rec => v_csh_payment_req_record); SELECT h.bp_class INTO v_bp_class FROM con_contract t, hls_bp_master h WHERE t.bp_id_tenant = h.bp_id AND t.contract_id = v_csh_payment_req_record.contract_id; sys_condition_cdd_pkg.matching_condition(p_condition_code => 'CDD00', p_company_id => v_csh_payment_req_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 => '', p_business_type => '', p_document_category => '', p_document_type => '', p_bp_class => v_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; PROCEDURE update_payment_req_number(p_payment_req_id IN NUMBER, p_payment_req_number IN VARCHAR2) IS BEGIN UPDATE csh_payment_req_hd h SET h.payment_req_number = p_payment_req_number WHERE h.payment_req_id = p_payment_req_id; END; /*Function get_payment_record(p_payment_req_id Number, p_user_id Number) Return csh_payment_req_hd%Rowtype Is v_record csh_payment_req_hd%Rowtype; Begin Select * Into v_record From csh_payment_req_hd v Where v.payment_req_id = p_payment_req_id For Update Nowait; Return v_record; 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; --插入合同id PROCEDURE update_payment_req_contract_id(p_payment_req_id IN NUMBER, p_contract_id IN NUMBER) IS v_cdd_list_id NUMBER; BEGIN SELECT cc.cdd_list_id INTO v_cdd_list_id FROM con_contract cc WHERE cc.contract_id = p_contract_id; UPDATE csh_payment_req_hd h SET h.contract_id = p_contract_id, h.cdd_list_id = v_cdd_list_id WHERE h.payment_req_id = p_payment_req_id; --复制附件 /*save_cdd_item_doc_ref(p_source_document_table => 'CON_CONTRACT', p_source_document_id => p_contract_id, p_to_document_table => 'CSH_PAYMENT_REQ_HD', p_to_document_id => p_payment_req_id, p_user_id => 1);*/ END; --检验可抵扣金额 PROCEDURE check_ddct_amount(p_payment_req_id IN NUMBER) IS v_cdd_list_id NUMBER; v_payment_req_ln_id NUMBER; v_un_due_amount NUMBER; e_ddct_amount_error EXCEPTION; BEGIN --可以多行 /*Select l.payment_req_ln_id Into v_payment_req_ln_id From csh_payment_req_ln l Where l.payment_req_id = p_payment_req_id; Select Sum(dt.un_due_amount) Into v_un_due_amount From csh_payment_req_ln_ddct_lv dt Where dt.payment_req_ln_id = v_payment_req_ln_id;*/ SELECT SUM(dt.un_due_amount) INTO v_un_due_amount FROM csh_payment_req_ln_ddct_lv dt WHERE dt.payment_req_ln_id IN (SELECT l.payment_req_ln_id FROM csh_payment_req_ln l WHERE l.payment_req_id = p_payment_req_id); IF v_un_due_amount < 0 THEN RAISE e_ddct_amount_error; END IF; EXCEPTION WHEN e_ddct_amount_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '可抵扣金额小于0,请检查该条单据!', p_created_by => 1, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_ddct_amount'); 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 NULL; END; PROCEDURE check_contract_payment(p_contract_id NUMBER, p_payment_req_id NUMBER, p_user_id NUMBER) IS v_count NUMBER; e_con_not_found_payment_err EXCEPTION; BEGIN SELECT COUNT(1) INTO v_count FROM csh_payment_req_ln l WHERE l.ref_doc_category = 'CONTRACT' AND l.ref_doc_id = p_contract_id AND l.payment_req_id = p_payment_req_id AND l.payment_status <> 'FULL'; IF v_count >= 1 THEN RAISE e_con_not_found_payment_err; END IF; EXCEPTION WHEN e_con_not_found_payment_err THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该合同尚未全部完成支付!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_contract_payment'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --add by 9188 检验付款申请工作流的所有合同是否全部核销 PROCEDURE check_sch_payment_con(p_payment_req_id NUMBER, p_user_id NUMBER) IS v_count NUMBER := 0; e_csh_payment_err EXCEPTION; BEGIN FOR cu IN (SELECT * FROM csh_payment_req_ln t1 WHERE t1.payment_req_id = p_payment_req_id AND t1.ref_doc_category = 'CONTRACT') LOOP IF (cu.payment_status <> 'FULL') THEN v_count := v_count + 1; END IF; END LOOP; IF (v_count >= 1) THEN RAISE e_csh_payment_err; END IF; EXCEPTION WHEN e_csh_payment_err THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该付款申请下存在尚未全部完成支付的合同!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_sch_payment_con'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END check_sch_payment_con; --add by Harry 9952 保证金退还-付款申请校验 PROCEDURE check_status_and_flag(p_cashflow_id NUMBER, p_user_id NUMBER) IS v_count_1 NUMBER; v_count_2 NUMBER; v_count_3 NUMBER; e_closed_flag_error EXCEPTION; e_approving_error EXCEPTION; e_csh_trx_error EXCEPTION; BEGIN SELECT COUNT(*) INTO v_count_1 FROM csh_payment_req_hd h WHERE h.payment_req_id IN (SELECT ln.payment_req_id FROM csh_payment_req_ln ln WHERE ln.ref_doc_line_id = p_cashflow_id) AND h.closed_flag != 'Y'; SELECT COUNT(*) INTO v_count_2 FROM csh_payment_req_hd h WHERE h.payment_req_id IN (SELECT l.payment_req_id FROM csh_payment_req_ln l WHERE l.ref_doc_line_id = p_cashflow_id) AND h.approval_status = 'APPROVING'; -- 收付抵扣工作流中 SELECT COUNT(*) INTO v_count_3 FROM hls_common_wfl c, con_contract_cashflow d, csh_write_off_temp t WHERE c.status = 'APPROVING' AND c.wfl_instance_id = t.wfl_instance_id AND t.document_line_id = d.cashflow_id AND d.cashflow_id = p_cashflow_id AND t.document_category = 'CSH_TRX'; /* if v_count_1 <> 0 then raise e_closed_flag_error; end if;*/ IF v_count_2 <> 0 THEN RAISE e_approving_error; END IF; /* if v_count_3 <> 0 then raise e_csh_trx_error; end if;*/ EXCEPTION WHEN e_closed_flag_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该合同客户保证金正在抵扣工作流中,请勿继续抵扣或支付!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_status_and_flag'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_approving_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该合同客户保证金正在付款工作流中,请勿继续抵扣或支付!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_status_and_flag'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_csh_trx_error THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该合同客户保证金正在抵扣工作流中,请勿继续抵扣或支付!', p_created_by => p_user_id, p_package_name => 'csh_payment_req_pkg', p_procedure_function_name => 'check_status_and_flag'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END check_status_and_flag; END csh_payment_req_pkg; /