CREATE OR REPLACE PACKAGE csh_transaction_pkg IS -- Author : GAOYANG -- Created : 2013/5/9 14:43:26 -- Purpose : 现金事务 -- Version : 1.18 ---退款 csh_trx_return_flag_p CONSTANT VARCHAR2(30) := 'PARTIAL'; csh_trx_return_flag_f CONSTANT VARCHAR2(30) := 'FULL'; csh_trx_return_flag_n CONSTANT VARCHAR2(30) := 'NOT'; csh_trx_return_flag_return CONSTANT VARCHAR2(30) := 'RETURN'; --退款类型 --核销 csh_trx_write_off_flag_p CONSTANT VARCHAR2(30) := 'PARTIAL'; -- 部分核销 csh_trx_write_off_flag_n CONSTANT VARCHAR2(30) := 'NOT'; -- 未核销 csh_trx_write_off_flag_f CONSTANT VARCHAR2(30) := 'FULL'; -- 完全核销 ---冲销 csh_trx_reversed_flag_n CONSTANT VARCHAR2(1) := 'N'; csh_trx_reversed_flag_r CONSTANT VARCHAR2(1) := 'R'; csh_trx_reversed_flag_w CONSTANT VARCHAR2(1) := 'W'; csh_trx_type_receipt CONSTANT VARCHAR2(30) := 'RECEIPT'; -- 收款类型 csh_trx_type_prereceipt CONSTANT VARCHAR2(30) := 'ADVANCE_RECEIPT'; -- 预收款类型 csh_trx_type_payment CONSTANT VARCHAR2(30) := 'PAYMENT'; -- 付款类型 csh_trx_type_deduction CONSTANT VARCHAR2(30) := 'DEDUCTION'; -- 收付抵扣 csh_trx_category_business CONSTANT VARCHAR2(30) := 'BUSINESS'; -- 收款类别 csh_trx_category_loan_withdraw CONSTANT VARCHAR2(30) := 'LOAN_WITHDRAW'; -- 银行借款 csh_trx_category_capital_int CONSTANT VARCHAR2(30) := 'LOAN_REPAYMENT_CI'; -- 还本付息 csh_trx_category_capital CONSTANT VARCHAR2(30) := 'LOAN_REPAYMENT_C'; -- 本金 csh_trx_category_interest CONSTANT VARCHAR2(30) := 'LOAN_REPAYMENT_I'; -- 利息 csh_trx_category_factoring CONSTANT VARCHAR2(30) := 'LOAN_REPAYMENT_F'; -- 保理还款 csh_trx_category_factoring_fee CONSTANT VARCHAR2(30) := 'LOAN_REPAYMENT_FF'; -- 保理费 csh_trx_type_deposit CONSTANT VARCHAR2(30) := 'DEPOSIT'; --保证金类型 csh_trx_type_risk CONSTANT VARCHAR2(30) := 'RISK'; --风险金类型 ---过帐 csh_trx_posted_flag_yes CONSTANT VARCHAR2(1) := 'Y'; csh_trx_posted_flag_no CONSTANT VARCHAR2(1) := 'N'; /* 现金事务编号获取函数 */ FUNCTION get_csh_transaction_num(p_transaction_type VARCHAR2, p_transaction_date DATE, p_company_id NUMBER, p_user_id NUMBER) RETURN VARCHAR2; PROCEDURE lock_csh_transaction(p_transaction_id IN NUMBER, p_user_id IN NUMBER, p_csh_transaction_rec OUT csh_transaction%ROWTYPE); --更新现金事务核销状态 PROCEDURE set_csh_trx_write_off_status(p_csh_trx_rec csh_transaction%ROWTYPE, p_write_off_amount NUMBER, p_user_id NUMBER); --更新被反冲现金事务 PROCEDURE update_csh_trx_after_reverse(p_transaction_id NUMBER, p_reversed_date DATE, p_reserved_csh_trx_id NUMBER, p_user_id NUMBER); --核销反冲后更新原现金事务核销金额 PROCEDURE update_trx_amt_after_reverse(p_transaction_id NUMBER, p_reverse_amount NUMBER, p_user_id NUMBER); FUNCTION is_trx_return(p_csh_transaction_rec IN csh_transaction%ROWTYPE) RETURN BOOLEAN; FUNCTION is_trx_reverse(p_csh_transaction_rec IN csh_transaction%ROWTYPE) RETURN BOOLEAN; /*modify by xuls 2014-9-16 add ref_contract_id */ PROCEDURE insert_csh_transaction(p_transaction_id OUT csh_transaction.transaction_id%TYPE, p_transaction_num csh_transaction.transaction_num%TYPE, p_transaction_category csh_transaction.transaction_category%TYPE, p_transaction_type csh_transaction.transaction_type%TYPE, p_transaction_date csh_transaction.transaction_date%TYPE, p_penalty_calc_date csh_transaction.penalty_calc_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_company_id csh_transaction.company_id%TYPE, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_distribution_set_id csh_transaction.distribution_set_id%TYPE, p_cashflow_amount csh_transaction.cashflow_amount%TYPE, p_currency_code csh_transaction.currency_code%TYPE, p_transaction_amount csh_transaction.transaction_amount%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_bp_category csh_transaction.bp_category%TYPE, p_bp_id csh_transaction.bp_id%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_bp_bank_account_name csh_transaction.bp_bank_account_name%TYPE DEFAULT NULL, --add by Spener 3893 20160722 p_description csh_transaction.description%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_posted_flag csh_transaction.posted_flag%TYPE, p_reversed_flag csh_transaction.reversed_flag%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_returned_flag csh_transaction.returned_flag%TYPE, p_returned_amount csh_transaction.returned_amount%TYPE, p_write_off_flag csh_transaction.write_off_flag%TYPE, p_write_off_amount csh_transaction.write_off_amount%TYPE, p_full_write_off_date csh_transaction.full_write_off_date%TYPE, p_twin_csh_trx_id csh_transaction.twin_csh_trx_id%TYPE, p_return_from_csh_trx_id csh_transaction.return_from_csh_trx_id%TYPE, p_reversed_csh_trx_id csh_transaction.reversed_csh_trx_id%TYPE, p_source_csh_trx_type csh_transaction.source_csh_trx_type%TYPE, p_source_csh_trx_id csh_transaction.source_csh_trx_id%TYPE, p_source_doc_category csh_transaction.source_doc_category%TYPE, p_source_doc_type csh_transaction.source_doc_type%TYPE, p_source_doc_id csh_transaction.source_doc_id%TYPE, p_source_doc_line_id csh_transaction.source_doc_line_id%TYPE, p_create_je_mothed csh_transaction.create_je_mothed%TYPE, p_create_je_flag csh_transaction.create_je_flag%TYPE, p_gld_interface_flag csh_transaction.gld_interface_flag%TYPE, p_user_id csh_transaction.created_by%TYPE, p_ref_contract_id csh_transaction.ref_contract_id%TYPE DEFAULT NULL, p_receipt_type csh_transaction.receipt_type%TYPE DEFAULT NULL, p_csh_bp_name csh_transaction.csh_bp_name%TYPE DEFAULT NULL, p_ref_n01 NUMBER DEFAULT NULL, p_bp_bank_name VARCHAR2 DEFAULT NULL, p_bp_bank_branch_name VARCHAR2 DEFAULT NULL, p_collection_classes VARCHAR2 DEFAULT NULL, p_opposite_band_na VARCHAR2 DEFAULT NULL, p_banka VARCHAR2 DEFAULT NULL, p_purpose VARCHAR2 DEFAULT NULL, p_gsber VARCHAR2 DEFAULT NULL, p_sus_belnr VARCHAR2 DEFAULT NULL, p_assigns_contract_id NUMBER DEFAULT NULL, p_conds_account_num VARCHAR2 DEFAULT NULL, p_conds_account_name VARCHAR2 DEFAULT NULL, p_bp_virtual_bank_account VARCHAR2 DEFAULT NULL, p_return_id NUMBER DEFAULT NULL, p_zuonr VARCHAR2 DEFAULT NULL, --add by sf p_paid_byother_flag IN VARCHAR2, p_ref_v05 IN VARCHAR2, p_status IN VARCHAR2 DEFAULT NULL --end; ); PROCEDURE update_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_transaction_num csh_transaction.transaction_num%TYPE, p_transaction_category csh_transaction.transaction_category%TYPE, p_transaction_type csh_transaction.transaction_type%TYPE, p_transaction_date csh_transaction.transaction_date%TYPE, p_penalty_calc_date csh_transaction.penalty_calc_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_company_id csh_transaction.company_id%TYPE, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_distribution_set_id csh_transaction.distribution_set_id%TYPE, p_cashflow_amount csh_transaction.cashflow_amount%TYPE, p_currency_code csh_transaction.currency_code%TYPE, p_transaction_amount csh_transaction.transaction_amount%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_bp_category csh_transaction.bp_category%TYPE, p_bp_id csh_transaction.bp_id%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_description csh_transaction.description%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_posted_flag csh_transaction.posted_flag%TYPE, p_reversed_flag csh_transaction.reversed_flag%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_returned_flag csh_transaction.returned_flag%TYPE, p_returned_amount csh_transaction.returned_amount%TYPE, p_write_off_flag csh_transaction.write_off_flag%TYPE, p_write_off_amount csh_transaction.write_off_amount%TYPE, p_full_write_off_date csh_transaction.full_write_off_date%TYPE, p_twin_csh_trx_id csh_transaction.twin_csh_trx_id%TYPE, p_return_from_csh_trx_id csh_transaction.return_from_csh_trx_id%TYPE, p_reversed_csh_trx_id csh_transaction.reversed_csh_trx_id%TYPE, p_source_csh_trx_type csh_transaction.source_csh_trx_type%TYPE, p_source_csh_trx_id csh_transaction.source_csh_trx_id%TYPE, p_source_doc_category csh_transaction.source_doc_category%TYPE, p_source_doc_type csh_transaction.source_doc_type%TYPE, p_source_doc_id csh_transaction.source_doc_id%TYPE, p_source_doc_line_id csh_transaction.source_doc_line_id%TYPE, p_create_je_mothed csh_transaction.create_je_mothed%TYPE, p_create_je_flag csh_transaction.create_je_flag%TYPE, p_gld_interface_flag csh_transaction.gld_interface_flag%TYPE, p_user_id csh_transaction.created_by%TYPE, p_ref_contract_id csh_transaction.ref_contract_id%TYPE DEFAULT NULL, p_ref_n01 NUMBER DEFAULT NULL, p_bp_bank_name VARCHAR2 DEFAULT NULL, p_bp_bank_branch_name VARCHAR2 DEFAULT NULL, p_collection_classes VARCHAR2 DEFAULT NULL, p_bp_bank_account_name VARCHAR2 DEFAULT NULL, p_opposite_band_na VARCHAR2 DEFAULT NULL, p_banka VARCHAR2 DEFAULT NULL, p_purpose VARCHAR2 DEFAULT NULL, p_gsber VARCHAR2 DEFAULT NULL, p_sus_belnr VARCHAR2 DEFAULT NULL, p_bp_virtual_bank_account VARCHAR2 DEFAULT NULL); --更新退款现金事务信息 PROCEDURE update_csh_trx_after_return(p_transaction_id NUMBER, p_returned_amount NUMBER, p_user_id NUMBER); --货币兑换维护 PROCEDURE update_currency_exchange_trx(p_transaction_id csh_transaction.transaction_id%TYPE, p_transaction_num csh_transaction.transaction_num%TYPE, p_transaction_category csh_transaction.transaction_category%TYPE, p_transaction_type csh_transaction.transaction_type%TYPE, p_transaction_date csh_transaction.transaction_date%TYPE, p_penalty_calc_date csh_transaction.penalty_calc_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_company_id csh_transaction.company_id%TYPE, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_distribution_set_id csh_transaction.distribution_set_id%TYPE, p_cashflow_amount csh_transaction.cashflow_amount%TYPE, p_currency_code csh_transaction.currency_code%TYPE, p_transaction_amount csh_transaction.transaction_amount%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_bp_category csh_transaction.bp_category%TYPE, p_bp_id csh_transaction.bp_id%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_description csh_transaction.description%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_posted_flag csh_transaction.posted_flag%TYPE, p_reversed_flag csh_transaction.reversed_flag%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_returned_flag csh_transaction.returned_flag%TYPE, p_returned_amount csh_transaction.returned_amount%TYPE, p_write_off_flag csh_transaction.write_off_flag%TYPE, p_write_off_amount csh_transaction.write_off_amount%TYPE, p_full_write_off_date csh_transaction.full_write_off_date%TYPE, p_twin_csh_trx_id csh_transaction.twin_csh_trx_id%TYPE, p_return_from_csh_trx_id csh_transaction.return_from_csh_trx_id%TYPE, p_reversed_csh_trx_id csh_transaction.reversed_csh_trx_id%TYPE, p_source_csh_trx_type csh_transaction.source_csh_trx_type%TYPE, p_source_csh_trx_id csh_transaction.source_csh_trx_id%TYPE, p_source_doc_category csh_transaction.source_doc_category%TYPE, p_source_doc_type csh_transaction.source_doc_type%TYPE, p_source_doc_id csh_transaction.source_doc_id%TYPE, p_source_doc_line_id csh_transaction.source_doc_line_id%TYPE, p_create_je_mothed csh_transaction.create_je_mothed%TYPE, p_create_je_flag csh_transaction.create_je_flag%TYPE, p_gld_interface_flag csh_transaction.gld_interface_flag%TYPE, p_user_id csh_transaction.created_by%TYPE); --货币兑换互记TWIN_CSH_TRX_ID PROCEDURE save_twin_csh_trx_id(p_transaction_num csh_transaction.transaction_num%TYPE, p_user_id NUMBER); --货币兑换过账以后维护 PROCEDURE upd_currency_trx_after_post(p_transaction_id csh_transaction.transaction_id%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_description csh_transaction.description%TYPE, p_user_id csh_transaction.created_by%TYPE); --过账以后维护 PROCEDURE update_csh_transaction_post(p_transaction_id csh_transaction.transaction_id%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_bp_bank_account_id csh_transaction.bp_bank_account_id%TYPE, p_user_id csh_transaction.created_by%TYPE); PROCEDURE update_csh_post_flag(p_transaction_id NUMBER, p_user_id NUMBER, p_claim VARCHAR2 DEFAULT NULL); PROCEDURE delete_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_user_id csh_transaction.created_by%TYPE); PROCEDURE post_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_user_id csh_transaction.created_by%TYPE); --通用的反冲入口 PROCEDURE reverse_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_description csh_transaction.description%TYPE, p_user_id csh_transaction.created_by%TYPE, p_reverse_transaction_id OUT csh_transaction.transaction_id%TYPE); --收款反冲入口 PROCEDURE reverse_receipt_csh_trx(p_transaction_id csh_transaction.transaction_id%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_description csh_transaction.description%TYPE, p_user_id csh_transaction.created_by%TYPE); PROCEDURE return_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_returned_date DATE, p_returned_amount csh_transaction.returned_amount%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_bank_account_id csh_transaction.bank_account_id%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_description csh_transaction.description%TYPE, p_user_id csh_transaction.created_by%TYPE, p_return_id NUMBER DEFAULT NULL); PROCEDURE delete_interface(p_batch_id NUMBER, p_user_id NUMBER); PROCEDURE insert_interface(p_header_id NUMBER, p_batch_id NUMBER, p_user_id NUMBER); PROCEDURE check_data(p_batch_id NUMBER, p_user_id NUMBER, p_check_type VARCHAR2 DEFAULT NULL, p_return_id OUT NUMBER); PROCEDURE save_data(p_batch_id NUMBER, p_user_id NUMBER, p_company_id NUMBER, v_save_message OUT VARCHAR2); --收款导入新建现金事务 PROCEDURE save_transaction_data(p_batch_id NUMBER, p_user_id NUMBER, p_company_id NUMBER, v_save_message OUT VARCHAR2); PROCEDURE risk_vat_insert(p_vat NUMBER, p_company_id NUMBER, p_user_id NUMBER); --add by shen --插入手工收款新增数据,检验流水号的唯一性 PROCEDURE save_csh_transaction_data(p_transaction_date IN VARCHAR2, p_cashflow_amount IN NUMBER, p_bank_slip_num IN VARCHAR2, p_paid_byother_flag IN VARCHAR2, p_bp_id IN NUMBER, p_collection_classes IN NUMBER, p_description IN VARCHAR2, p_bp_bank_account_id IN NUMBER, p_bp_bank_name IN VARCHAR2, p_bp_bank_account_num IN VARCHAR2, p_bank_account_id IN VARCHAR2, p_user_id IN VARCHAR2); --excel导入收款新增csh_transaction表 PROCEDURE csh_transaction_import_check(p_header_id NUMBER, p_user_id NUMBER); --改变单据的状态 PROCEDURE update_confirmed_flag(p_transaction_id IN NUMBER, p_wanted_confirmed_flag IN VARCHAR2, p_write_off_flag IN VARCHAR2 DEFAULT NULL, p_user_id IN VARCHAR2); --根据银行卡号获得银行账号的id PROCEDURE get_bank_account_id(p_bank_account_num IN VARCHAR2, p_bp_bank_account_num IN VARCHAR2, p_bank_account_id OUT NUMBER, p_bp_category OUT VARCHAR2, p_bp_id OUT NUMBER, p_csh_bp_name OUT VARCHAR2, p_user_id IN VARCHAR2); PROCEDURE get_bank_account_info(p_bank_account_num IN VARCHAR2, p_bp_bank_account_name IN VARCHAR2, p_bank_account_id OUT NUMBER, p_bp_category OUT VARCHAR2, p_bp_id OUT NUMBER, p_csh_bp_name OUT VARCHAR2, p_user_id IN VARCHAR2); --excel 导入收款数据 PROCEDURE save_receipt_import(p_transaction_id OUT csh_transaction.transaction_id%TYPE, p_transaction_num csh_transaction.transaction_num%TYPE, p_transaction_category csh_transaction.transaction_category%TYPE, p_transaction_type csh_transaction.transaction_type%TYPE, p_transaction_date csh_transaction.transaction_date%TYPE, p_penalty_calc_date csh_transaction.penalty_calc_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_company_id csh_transaction.company_id%TYPE, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_distribution_set_id csh_transaction.distribution_set_id%TYPE, p_cashflow_amount csh_transaction.cashflow_amount%TYPE, p_currency_code csh_transaction.currency_code%TYPE, p_transaction_amount csh_transaction.transaction_amount%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_bp_category csh_transaction.bp_category%TYPE, p_bp_id csh_transaction.bp_id%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_bp_bank_account_name csh_transaction.bp_bank_account_name%TYPE DEFAULT NULL, --add by Spener 3893 20160722 p_description csh_transaction.description%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_posted_flag csh_transaction.posted_flag%TYPE, p_reversed_flag csh_transaction.reversed_flag%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_returned_flag csh_transaction.returned_flag%TYPE, p_returned_amount csh_transaction.returned_amount%TYPE, p_write_off_flag csh_transaction.write_off_flag%TYPE, p_write_off_amount csh_transaction.write_off_amount%TYPE, p_full_write_off_date csh_transaction.full_write_off_date%TYPE, p_twin_csh_trx_id csh_transaction.twin_csh_trx_id%TYPE, p_return_from_csh_trx_id csh_transaction.return_from_csh_trx_id%TYPE, p_reversed_csh_trx_id csh_transaction.reversed_csh_trx_id%TYPE, p_source_csh_trx_type csh_transaction.source_csh_trx_type%TYPE, p_source_csh_trx_id csh_transaction.source_csh_trx_id%TYPE, p_source_doc_category csh_transaction.source_doc_category%TYPE, p_source_doc_type csh_transaction.source_doc_type%TYPE, p_source_doc_id csh_transaction.source_doc_id%TYPE, p_source_doc_line_id csh_transaction.source_doc_line_id%TYPE, p_create_je_mothed csh_transaction.create_je_mothed%TYPE, p_create_je_flag csh_transaction.create_je_flag%TYPE, p_gld_interface_flag csh_transaction.gld_interface_flag%TYPE, p_user_id csh_transaction.created_by%TYPE, p_ref_contract_id csh_transaction.ref_contract_id%TYPE DEFAULT NULL, p_receipt_type csh_transaction.receipt_type%TYPE DEFAULT NULL, p_csh_bp_name csh_transaction.csh_bp_name%TYPE DEFAULT NULL, p_ref_n01 NUMBER DEFAULT NULL, p_bp_bank_name VARCHAR2 DEFAULT NULL, p_bp_bank_branch_name VARCHAR2 DEFAULT NULL, p_collection_classes VARCHAR2 DEFAULT NULL, p_opposite_band_na VARCHAR2 DEFAULT NULL, p_banka VARCHAR2 DEFAULT NULL, p_purpose VARCHAR2 DEFAULT NULL, p_gsber VARCHAR2 DEFAULT NULL, p_sus_belnr VARCHAR2 DEFAULT NULL, p_assigns_contract_id NUMBER DEFAULT NULL, p_conds_account_num VARCHAR2 DEFAULT NULL, p_conds_account_name VARCHAR2 DEFAULT NULL, p_bp_virtual_bank_account VARCHAR2 DEFAULT NULL, p_return_id NUMBER DEFAULT NULL, p_zuonr VARCHAR2 DEFAULT NULL, --add by sf p_paid_byother_flag IN VARCHAR2, p_ref_v05 IN VARCHAR2, --该行信息是否正确 p_status IN VARCHAR2, p_bank_account_num IN VARCHAR2 --end; ); procedure update_confirmer_by_condition( p_transaction_id in number, p_user_id in varchar2 ); --修改单据的状态(作废,驳回,以及确认单据) procedure update_muti_confirmed_flag( p_transaction_id in number, p_wanted_confirmed_flag in varchar2 , p_wanted_written_off_flag in varchar2 default null, p_confirmed_by in varchar2 default null, p_user_id in varchar2 ); END csh_transaction_pkg; / CREATE OR REPLACE PACKAGE BODY csh_transaction_pkg IS v_err_code VARCHAR2(2000); e_lock_trx_error EXCEPTION; PRAGMA EXCEPTION_INIT(e_lock_trx_error, -54); FUNCTION get_csh_transaction_num(p_transaction_type VARCHAR2, p_transaction_date DATE, p_company_id NUMBER, p_user_id NUMBER) RETURN VARCHAR2 IS v_transaction_num csh_transaction.transaction_num%TYPE; e_trx_num_error EXCEPTION; v_transaction_type VARCHAR2(200); BEGIN IF p_transaction_type = 'DEPOSIT_POOL_RECEIPT' THEN v_transaction_type := 'ADVANCE_RECEIPT'; ELSE v_transaction_type := p_transaction_type; END IF; v_transaction_num := fnd_code_rule_pkg.get_rule_next_auto_num(p_document_category => 'CSH_TRX', p_document_type => v_transaction_type, p_company_id => p_company_id, p_operation_unit_id => NULL, p_operation_date => p_transaction_date, p_created_by => p_user_id); IF nvl(v_transaction_num, fnd_code_rule_pkg.c_error) = fnd_code_rule_pkg.c_error THEN RAISE e_trx_num_error; END IF; RETURN v_transaction_num; EXCEPTION WHEN e_trx_num_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_TRANSACTION_NUM_ERROR', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'get_csh_transaction_num'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --录入0金额校验 PROCEDURE check_zero_amount(p_csh_transaction_rec IN csh_transaction%ROWTYPE, p_user_id IN NUMBER) IS v_zero_amounts_allowed VARCHAR2(1); e_amount_error EXCEPTION; BEGIN IF p_csh_transaction_rec.transaction_amount <> 0 OR (p_csh_transaction_rec.transaction_amount = 0 AND p_csh_transaction_rec.transaction_type = csh_trx_type_deduction) THEN RETURN; END IF; /*SELECT nvl(d.zero_amounts_allowed, 'N') INTO v_zero_amounts_allowed FROM csh_bank_account_v d WHERE d.bank_account_id = p_csh_transaction_rec.bank_account_id; IF v_zero_amounts_allowed = 'N' THEN RAISE e_amount_error; END IF;*/ EXCEPTION WHEN e_amount_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_ZERO_AMOUNT_CHECK', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'zero_amount_check'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE lock_csh_transaction(p_transaction_id IN NUMBER, p_user_id IN NUMBER, p_csh_transaction_rec OUT csh_transaction%ROWTYPE) IS BEGIN SELECT * INTO p_csh_transaction_rec FROM csh_transaction t WHERE t.transaction_id = p_transaction_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_transaction_pkg', p_procedure_function_name => 'lock_csh_transaction'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --更新现金事务核销状态(包括已核销金额) PROCEDURE set_csh_trx_write_off_status(p_csh_trx_rec csh_transaction%ROWTYPE, p_write_off_amount NUMBER, p_user_id NUMBER) IS v_csh_transaction_rec csh_transaction%ROWTYPE; v_sum_write_off_amt NUMBER := 0; v_sum_check_amount NUMBER := 0; v_write_off_flag VARCHAR2(30); v_last_write_off_date DATE; e_write_off_amount_error EXCEPTION; BEGIN IF p_csh_trx_rec.transaction_type = csh_trx_type_deduction THEN RETURN; END IF; lock_csh_transaction(p_transaction_id => p_csh_trx_rec.transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); v_sum_write_off_amt := nvl(p_write_off_amount, 0) + nvl(v_csh_transaction_rec.write_off_amount, 0); v_sum_check_amount := nvl(p_write_off_amount, 0) + nvl(v_csh_transaction_rec.write_off_amount, 0) + nvl(v_csh_transaction_rec.returned_amount, 0); IF v_sum_check_amount = 0 THEN v_write_off_flag := csh_trx_write_off_flag_n; v_last_write_off_date := v_csh_transaction_rec.full_write_off_date; ELSIF v_sum_check_amount > 0 AND v_sum_check_amount < nvl(v_csh_transaction_rec.transaction_amount, 0) THEN v_write_off_flag := csh_trx_write_off_flag_p; v_last_write_off_date := v_csh_transaction_rec.full_write_off_date; ELSIF v_sum_check_amount = nvl(v_csh_transaction_rec.transaction_amount, 0) THEN v_write_off_flag := csh_trx_write_off_flag_f; v_last_write_off_date := csh_write_off_pkg.get_last_write_off_date(p_transaction_id => v_csh_transaction_rec.transaction_id); ELSE RAISE e_write_off_amount_error; END IF; UPDATE csh_transaction t SET t.write_off_flag = v_write_off_flag, t.write_off_amount = v_sum_write_off_amt, t.full_write_off_date = v_last_write_off_date, t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.transaction_id = v_csh_transaction_rec.transaction_id; EXCEPTION WHEN e_write_off_amount_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH_WRITE_OFF_AMOUNT_ERROR', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'set_csh_trx_write_off_status'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --更新退款现金事务信息 PROCEDURE update_csh_trx_after_return(p_transaction_id NUMBER, p_returned_amount NUMBER, p_user_id NUMBER) IS v_csh_transaction_rec csh_transaction%ROWTYPE; v_returned_flag csh_transaction.returned_flag%TYPE; v_write_off_flag csh_transaction.write_off_flag%TYPE; v_sum_returned_amount NUMBER := 0; v_sum_writeoff_amount NUMBER := 0; e_sum_returned_amount_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); v_sum_returned_amount := nvl(v_csh_transaction_rec.returned_amount, 0) + nvl(p_returned_amount, 0); v_sum_writeoff_amount := nvl(v_csh_transaction_rec.returned_amount, 0) + nvl(p_returned_amount, 0) + nvl(v_csh_transaction_rec.write_off_amount, 0); IF v_sum_writeoff_amount = v_csh_transaction_rec.transaction_amount THEN v_write_off_flag := csh_trx_write_off_flag_f; ELSIF v_sum_writeoff_amount = 0 THEN v_write_off_flag := csh_trx_write_off_flag_n; ELSIF v_sum_writeoff_amount > 0 AND v_sum_writeoff_amount < v_csh_transaction_rec.transaction_amount THEN v_write_off_flag := csh_trx_write_off_flag_p; ELSE RAISE e_sum_returned_amount_error; END IF; IF v_sum_returned_amount = v_csh_transaction_rec.transaction_amount THEN v_returned_flag := csh_transaction_pkg.csh_trx_return_flag_f; ELSIF v_sum_returned_amount = 0 THEN v_returned_flag := csh_transaction_pkg.csh_trx_return_flag_n; ELSIF v_sum_returned_amount > 0 AND v_sum_returned_amount < v_csh_transaction_rec.transaction_amount THEN v_returned_flag := csh_transaction_pkg.csh_trx_return_flag_p; ELSE RAISE e_sum_returned_amount_error; END IF; UPDATE csh_transaction t SET t.returned_flag = v_returned_flag, t.returned_amount = v_sum_returned_amount, t.write_off_flag = v_write_off_flag, t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.transaction_id = v_csh_transaction_rec.transaction_id; EXCEPTION WHEN e_sum_returned_amount_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH_RETURNED_AMOUNT_ERROR', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'update_csh_trx_after_return'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --更新被反冲现金事务 PROCEDURE update_csh_trx_after_reverse(p_transaction_id NUMBER, p_reversed_date DATE, p_reserved_csh_trx_id NUMBER, p_user_id NUMBER) IS v_csh_transaction_rec csh_transaction%ROWTYPE; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); UPDATE csh_transaction t SET t.reversed_flag = csh_transaction_pkg.csh_trx_reversed_flag_w, t.reversed_date = p_reversed_date, t.reversed_csh_trx_id = p_reserved_csh_trx_id, t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.transaction_id = v_csh_transaction_rec.transaction_id; BEGIN --add by chenlingfeng for ds 删除预分配记录 csh_transaction_interface_pkg.delete_write_off_by_trx(p_transaction_id => v_csh_transaction_rec.transaction_id); EXCEPTION WHEN OTHERS THEN NULL; END; END; --退款反冲后更新原退款现金事务信息 PROCEDURE upd_rtn_csh_trx_after_reverse(p_transaction_id NUMBER, p_user_id NUMBER) IS v_csh_transaction_rec csh_transaction%ROWTYPE; v_source_csh_trx_rec csh_transaction%ROWTYPE; v_returned_flag csh_transaction.returned_flag%TYPE; v_write_off_flag csh_transaction.write_off_flag%TYPE; v_sum_returned_amount NUMBER := 0; v_sum_writeoff_amount NUMBER := 0; e_sum_returned_amount_error EXCEPTION; BEGIN 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_transaction(p_transaction_id => v_csh_transaction_rec.return_from_csh_trx_id, p_user_id => p_user_id, p_csh_transaction_rec => v_source_csh_trx_rec); v_sum_returned_amount := nvl(v_source_csh_trx_rec.returned_amount, 0) - nvl(v_csh_transaction_rec.transaction_amount, 0); v_sum_writeoff_amount := v_sum_returned_amount + nvl(v_source_csh_trx_rec.write_off_amount, 0); IF v_sum_writeoff_amount = v_source_csh_trx_rec.transaction_amount THEN v_write_off_flag := csh_trx_write_off_flag_f; ELSIF v_sum_writeoff_amount = 0 THEN v_write_off_flag := csh_trx_write_off_flag_n; ELSIF v_sum_writeoff_amount > 0 AND v_sum_writeoff_amount < v_source_csh_trx_rec.transaction_amount THEN v_write_off_flag := csh_trx_write_off_flag_p; ELSE RAISE e_sum_returned_amount_error; END IF; IF v_sum_returned_amount = 0 THEN v_returned_flag := csh_trx_return_flag_n; ELSIF v_sum_returned_amount = v_source_csh_trx_rec.transaction_amount THEN v_returned_flag := csh_trx_return_flag_f; ELSIF v_sum_returned_amount > 0 AND v_sum_returned_amount < v_source_csh_trx_rec.transaction_amount THEN v_returned_flag := csh_trx_return_flag_p; ELSE RAISE e_sum_returned_amount_error; END IF; UPDATE csh_transaction t SET t.returned_amount = v_sum_returned_amount, t.returned_flag = v_returned_flag, t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.transaction_id = v_csh_transaction_rec.return_from_csh_trx_id; EXCEPTION WHEN e_sum_returned_amount_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH_RETURNED_AMOUNT_ERROR', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'upd_rtn_csh_trx_after_reverse'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --核销反冲后更新被反冲现金事务核销金额 PROCEDURE update_trx_amt_after_reverse(p_transaction_id NUMBER, p_reverse_amount NUMBER, p_user_id NUMBER) IS v_csh_transaction_rec csh_transaction%ROWTYPE; v_sum_write_off_amount NUMBER; v_sum_check_amount NUMBER; v_write_off_flag VARCHAR2(30); v_last_write_off_date DATE; e_sum_writeoff_amount_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); --抵扣的不用更新现金流金额 IF v_csh_transaction_rec.transaction_type = csh_trx_type_deduction THEN RETURN; END IF; v_sum_write_off_amount := nvl(v_csh_transaction_rec.write_off_amount, 0) - nvl(p_reverse_amount, 0); v_sum_check_amount := nvl(v_csh_transaction_rec.write_off_amount, 0) + nvl(v_csh_transaction_rec.returned_amount, 0) - nvl(p_reverse_amount, 0); IF v_sum_check_amount = 0 THEN v_write_off_flag := csh_trx_write_off_flag_n; v_last_write_off_date := NULL; ELSIF v_sum_check_amount > 0 AND v_sum_check_amount < v_csh_transaction_rec.transaction_amount THEN v_write_off_flag := csh_trx_write_off_flag_p; v_last_write_off_date := NULL; ELSIF v_sum_check_amount = v_csh_transaction_rec.transaction_amount THEN v_write_off_flag := csh_trx_write_off_flag_f; v_last_write_off_date := csh_write_off_pkg.get_last_write_off_date(p_transaction_id => v_csh_transaction_rec.transaction_id); ELSE RAISE e_sum_writeoff_amount_error; END IF; --反冲后更新审批状态为新建 20170216 JIANGLEI UPDATE csh_transaction t SET t.write_off_amount = v_sum_write_off_amount, t.write_off_flag = v_write_off_flag, t.full_write_off_date = v_last_write_off_date, t.status = 'NEW', t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.transaction_id = v_csh_transaction_rec.transaction_id; EXCEPTION WHEN e_sum_writeoff_amount_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH_WRITEOFF_AMOUNT_ERROR', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'update_trx_amt_after_reverse'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --更新银行水单 PROCEDURE update_bank_slip_num(p_transaction_id csh_transaction.transaction_id%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_user_id csh_transaction.created_by%TYPE) IS v_csh_transaction_rec csh_transaction%ROWTYPE; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); UPDATE csh_transaction t SET t.bank_slip_num = p_bank_slip_num, t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.transaction_id = v_csh_transaction_rec.transaction_id; END; FUNCTION is_trx_return(p_csh_transaction_rec IN csh_transaction%ROWTYPE) RETURN BOOLEAN IS BEGIN IF nvl(p_csh_transaction_rec.posted_flag, csh_transaction_pkg.csh_trx_posted_flag_yes) <> csh_transaction_pkg.csh_trx_posted_flag_yes THEN v_err_code := 'CSH_TRANSACTION_PKG.RETURN_POSTED_FLAG_ERROR'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.returned_flag, csh_transaction_pkg.csh_trx_return_flag_n) = csh_transaction_pkg.csh_trx_return_flag_f THEN v_err_code := 'CSH_TRANSACTION_PKG.RETURN_RETURN_FLAG_ERROR'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.reversed_flag, csh_transaction_pkg.csh_trx_reversed_flag_n) <> csh_transaction_pkg.csh_trx_reversed_flag_n THEN v_err_code := 'CSH_TRANSACTION_PKG.RETURN_REVERSED_FLAG_ERROR'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.write_off_flag, csh_transaction_pkg.csh_trx_write_off_flag_n) = csh_transaction_pkg.csh_trx_write_off_flag_f THEN v_err_code := 'CSH_TRANSACTION_PKG.RETURN_WRITE_OFF_FLAG_ERROR'; RETURN FALSE; ELSE RETURN TRUE; END IF; END; FUNCTION is_trx_reverse(p_csh_transaction_rec IN csh_transaction%ROWTYPE) RETURN BOOLEAN IS BEGIN IF nvl(p_csh_transaction_rec.posted_flag, csh_trx_posted_flag_yes) <> csh_trx_posted_flag_yes THEN v_err_code := 'CSH_TRANSACTION_PKG.REVERSE_POSTED_FLAG_ERROR'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.returned_flag, csh_trx_return_flag_n) NOT IN (csh_trx_return_flag_n, csh_trx_return_flag_return) THEN v_err_code := 'CSH_TRANSACTION_PKG.REVERSE_RETURN_FLAG_ERROR'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.reversed_flag, csh_trx_reversed_flag_n) <> csh_trx_reversed_flag_n THEN v_err_code := 'CSH_TRANSACTION_PKG.REVERSE_REVERSED_FLAG_ERROR'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.write_off_flag, csh_trx_write_off_flag_n) <> csh_trx_write_off_flag_n AND nvl(p_csh_transaction_rec.returned_flag, csh_trx_return_flag_n) <> csh_trx_return_flag_return AND p_csh_transaction_rec.transaction_type NOT IN (csh_trx_type_payment, 'DEDUCTION') THEN v_err_code := 'CSH_TRANSACTION_PKG.REVERSE_WRITE_OFF_FLAG_ERROR'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.write_off_flag, csh_trx_write_off_flag_n) <> csh_trx_write_off_flag_f AND nvl(p_csh_transaction_rec.returned_flag, csh_trx_return_flag_n) <> csh_trx_return_flag_n AND p_csh_transaction_rec.transaction_type = csh_trx_type_payment THEN v_err_code := 'CSH_TRANSACTION_PKG.PAYMENT_REVERSE_WRITE_OFF_FLAG_ERROR'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.returned_flag, csh_trx_return_flag_n) = csh_trx_return_flag_return AND nvl(p_csh_transaction_rec.write_off_flag, csh_trx_write_off_flag_n) <> csh_trx_write_off_flag_f THEN v_err_code := 'CSH_TRANSACTION_PKG.RETURN_WRITE_OFF_FLAG_ERROR'; RETURN FALSE; ELSE RETURN TRUE; END IF; END; FUNCTION is_trx_post(p_csh_transaction_rec IN csh_transaction%ROWTYPE) RETURN BOOLEAN IS BEGIN IF nvl(p_csh_transaction_rec.posted_flag, csh_transaction_pkg.csh_trx_posted_flag_no) = csh_transaction_pkg.csh_trx_posted_flag_no THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; FUNCTION is_trx_update(p_csh_transaction_rec IN csh_transaction%ROWTYPE) RETURN BOOLEAN IS BEGIN IF nvl(p_csh_transaction_rec.posted_flag, csh_transaction_pkg.csh_trx_posted_flag_no) = csh_transaction_pkg.csh_trx_posted_flag_no THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; FUNCTION is_trx_update_after_post(p_csh_transaction_rec IN csh_transaction%ROWTYPE) RETURN BOOLEAN IS BEGIN IF nvl(p_csh_transaction_rec.posted_flag, csh_transaction_pkg.csh_trx_posted_flag_no) <> csh_transaction_pkg.csh_trx_posted_flag_yes THEN v_err_code := 'CSH_TRANSACTION_PKG.CSH510_POST_STATUS_CHECK'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.reversed_flag, csh_transaction_pkg.csh_trx_reversed_flag_n) <> csh_transaction_pkg.csh_trx_reversed_flag_n THEN v_err_code := 'CSH_TRANSACTION_PKG.CSH510_REVERSE_FLAG_CHECK'; RETURN FALSE; ELSE RETURN TRUE; END IF; END; FUNCTION is_trx_delete(p_csh_transaction_rec IN csh_transaction%ROWTYPE) RETURN BOOLEAN IS BEGIN IF nvl(p_csh_transaction_rec.posted_flag, csh_transaction_pkg.csh_trx_posted_flag_no) <> csh_transaction_pkg.csh_trx_posted_flag_no THEN v_err_code := 'CSH_TRANSACTION_PKG.CSH510_DELETE_STATUS_CHECK'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.reversed_flag, csh_transaction_pkg.csh_trx_reversed_flag_n) <> csh_transaction_pkg.csh_trx_reversed_flag_n THEN v_err_code := 'CSH_TRANSACTION_PKG.CSH510_DELETE_REVERSE_CHECK'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.returned_flag, csh_transaction_pkg.csh_trx_return_flag_n) <> csh_transaction_pkg.csh_trx_return_flag_n THEN v_err_code := 'CSH_TRANSACTION_PKG.CSH510_DELETE_RETURN_CHECK'; RETURN FALSE; ELSIF nvl(p_csh_transaction_rec.write_off_flag, csh_transaction_pkg.csh_trx_write_off_flag_n) <> csh_transaction_pkg.csh_trx_write_off_flag_n THEN v_err_code := 'CSH_TRANSACTION_PKG.CSH510_DELETE_WRITEOFF_CHECK'; RETURN FALSE; ELSE RETURN TRUE; END IF; END; PROCEDURE insert_csh_transaction(p_csh_transaction_rec IN OUT csh_transaction%ROWTYPE) IS BEGIN IF p_csh_transaction_rec.transaction_id IS NULL THEN SELECT csh_transaction_s.nextval INTO p_csh_transaction_rec.transaction_id FROM dual; END IF; INSERT INTO csh_transaction VALUES p_csh_transaction_rec; END; /*add by xuls for ref_contract_id 2014-9-26 用于增加记录收款合同号 */ PROCEDURE insert_csh_transaction(p_transaction_id OUT csh_transaction.transaction_id%TYPE, p_transaction_num csh_transaction.transaction_num%TYPE, p_transaction_category csh_transaction.transaction_category%TYPE, p_transaction_type csh_transaction.transaction_type%TYPE, p_transaction_date csh_transaction.transaction_date%TYPE, p_penalty_calc_date csh_transaction.penalty_calc_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_company_id csh_transaction.company_id%TYPE, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_distribution_set_id csh_transaction.distribution_set_id%TYPE, p_cashflow_amount csh_transaction.cashflow_amount%TYPE, p_currency_code csh_transaction.currency_code%TYPE, p_transaction_amount csh_transaction.transaction_amount%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_bp_category csh_transaction.bp_category%TYPE, p_bp_id csh_transaction.bp_id%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_bp_bank_account_name csh_transaction.bp_bank_account_name%TYPE DEFAULT NULL, --add by Spener 3893 20160722 p_description csh_transaction.description%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_posted_flag csh_transaction.posted_flag%TYPE, p_reversed_flag csh_transaction.reversed_flag%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_returned_flag csh_transaction.returned_flag%TYPE, p_returned_amount csh_transaction.returned_amount%TYPE, p_write_off_flag csh_transaction.write_off_flag%TYPE, p_write_off_amount csh_transaction.write_off_amount%TYPE, p_full_write_off_date csh_transaction.full_write_off_date%TYPE, p_twin_csh_trx_id csh_transaction.twin_csh_trx_id%TYPE, p_return_from_csh_trx_id csh_transaction.return_from_csh_trx_id%TYPE, p_reversed_csh_trx_id csh_transaction.reversed_csh_trx_id%TYPE, p_source_csh_trx_type csh_transaction.source_csh_trx_type%TYPE, p_source_csh_trx_id csh_transaction.source_csh_trx_id%TYPE, p_source_doc_category csh_transaction.source_doc_category%TYPE, p_source_doc_type csh_transaction.source_doc_type%TYPE, p_source_doc_id csh_transaction.source_doc_id%TYPE, p_source_doc_line_id csh_transaction.source_doc_line_id%TYPE, p_create_je_mothed csh_transaction.create_je_mothed%TYPE, p_create_je_flag csh_transaction.create_je_flag%TYPE, p_gld_interface_flag csh_transaction.gld_interface_flag%TYPE, p_user_id csh_transaction.created_by%TYPE, p_ref_contract_id csh_transaction.ref_contract_id%TYPE DEFAULT NULL, p_receipt_type csh_transaction.receipt_type%TYPE DEFAULT NULL, p_csh_bp_name csh_transaction.csh_bp_name%TYPE DEFAULT NULL, p_ref_n01 NUMBER DEFAULT NULL, --add by chenlingfeng p_bp_bank_name VARCHAR2 DEFAULT NULL, p_bp_bank_branch_name VARCHAR2 DEFAULT NULL, p_collection_classes VARCHAR2 DEFAULT NULL, p_opposite_band_na VARCHAR2 DEFAULT NULL, p_banka VARCHAR2 DEFAULT NULL, p_purpose VARCHAR2 DEFAULT NULL, p_gsber VARCHAR2 DEFAULT NULL, p_sus_belnr VARCHAR2 DEFAULT NULL, p_assigns_contract_id NUMBER DEFAULT NULL, p_conds_account_num VARCHAR2 DEFAULT NULL, p_conds_account_name VARCHAR2 DEFAULT NULL, p_bp_virtual_bank_account VARCHAR2 DEFAULT NULL, --end p_return_id NUMBER DEFAULT NULL, p_zuonr VARCHAR2 DEFAULT NULL, --add by sf p_paid_byother_flag IN VARCHAR2, p_ref_v05 IN VARCHAR2, p_status IN VARCHAR2 DEFAULT NULL --end; ) IS v_csh_transaction_rec csh_transaction%ROWTYPE; v_transaction_num csh_transaction.transaction_num%TYPE; e_trx_num_error EXCEPTION; v_csh_transaction_id NUMBER; -- add by sf v_exit_flag NUMBER := 0; e_slip_num_already_exist EXCEPTION; --end BEGIN --判断插入的状态是否zhengqur -- if p_status is null then return; end if; --add by sf --检查流水号的唯一性 SELECT COUNT(1) INTO v_exit_flag FROM csh_transaction WHERE bank_slip_num = p_bank_slip_num AND transaction_category = 'BUSINESS' AND transaction_type = 'RECEIPT'; IF v_exit_flag != 0 AND p_transaction_type = 'RECEIPT' AND p_transaction_category = 'BUSINESS' THEN RAISE e_slip_num_already_exist; END IF; --end; SELECT csh_transaction_s.nextval INTO v_csh_transaction_id FROM dual; v_csh_transaction_rec.transaction_id := v_csh_transaction_id; IF p_transaction_num IS NULL THEN v_transaction_num := get_csh_transaction_num(p_transaction_type => p_transaction_type, p_company_id => p_company_id, p_transaction_date => p_transaction_date, p_user_id => p_user_id); ELSE v_transaction_num := p_transaction_num; END IF; v_csh_transaction_rec.transaction_num := v_transaction_num; v_csh_transaction_rec.transaction_category := p_transaction_category; v_csh_transaction_rec.transaction_type := p_transaction_type; v_csh_transaction_rec.transaction_date := p_transaction_date; v_csh_transaction_rec.penalty_calc_date := p_penalty_calc_date; v_csh_transaction_rec.bank_slip_num := p_bank_slip_num; v_csh_transaction_rec.company_id := p_company_id; v_csh_transaction_rec.internal_period_num := p_internal_period_num; v_csh_transaction_rec.period_name := p_period_name; v_csh_transaction_rec.payment_method_id := p_payment_method_id; v_csh_transaction_rec.distribution_set_id := p_distribution_set_id; v_csh_transaction_rec.cashflow_amount := p_cashflow_amount; v_csh_transaction_rec.currency_code := p_currency_code; v_csh_transaction_rec.transaction_amount := p_transaction_amount; v_csh_transaction_rec.exchange_rate_type := p_exchange_rate_type; v_csh_transaction_rec.exchange_rate_quotation := p_exchange_rate_quotation; v_csh_transaction_rec.exchange_rate := p_exchange_rate; v_csh_transaction_rec.bank_account_id := p_bank_account_id; v_csh_transaction_rec.bp_category := p_bp_category; v_csh_transaction_rec.bp_id := p_bp_id; v_csh_transaction_rec.bp_bank_account_id := p_bp_bank_account_id; v_csh_transaction_rec.bp_bank_account_num := p_bp_bank_account_num; v_csh_transaction_rec.bp_bank_account_name := p_bp_bank_account_name; v_csh_transaction_rec.description := p_description; v_csh_transaction_rec.handling_charge := p_handling_charge; v_csh_transaction_rec.posted_flag := nvl(p_posted_flag, 'N'); v_csh_transaction_rec.reversed_flag := nvl(p_reversed_flag, csh_transaction_pkg.csh_trx_reversed_flag_n); v_csh_transaction_rec.reversed_date := p_reversed_date; v_csh_transaction_rec.returned_flag := nvl(p_returned_flag, csh_transaction_pkg.csh_trx_return_flag_n); v_csh_transaction_rec.returned_amount := p_returned_amount; v_csh_transaction_rec.write_off_flag := nvl(p_write_off_flag, csh_transaction_pkg.csh_trx_write_off_flag_n); v_csh_transaction_rec.write_off_amount := p_write_off_amount; v_csh_transaction_rec.full_write_off_date := p_full_write_off_date; v_csh_transaction_rec.twin_csh_trx_id := p_twin_csh_trx_id; v_csh_transaction_rec.return_from_csh_trx_id := p_return_from_csh_trx_id; v_csh_transaction_rec.reversed_csh_trx_id := p_reversed_csh_trx_id; v_csh_transaction_rec.source_csh_trx_type := p_source_csh_trx_type; v_csh_transaction_rec.source_doc_category := p_source_doc_category; v_csh_transaction_rec.source_csh_trx_id := p_source_csh_trx_id; v_csh_transaction_rec.source_doc_type := p_source_doc_type; v_csh_transaction_rec.source_doc_id := p_source_doc_id; v_csh_transaction_rec.source_doc_line_id := p_source_doc_line_id; v_csh_transaction_rec.create_je_mothed := p_create_je_mothed; v_csh_transaction_rec.create_je_flag := nvl(p_create_je_flag, 'N'); v_csh_transaction_rec.gld_interface_flag := nvl(p_gld_interface_flag, 'N'); v_csh_transaction_rec.creation_date := SYSDATE; v_csh_transaction_rec.created_by := p_user_id; v_csh_transaction_rec.last_update_date := SYSDATE; v_csh_transaction_rec.last_updated_by := p_user_id; v_csh_transaction_rec.csh_bp_name := p_csh_bp_name; v_csh_transaction_rec.receipt_type := p_receipt_type; /*add by xuls for ref_contract_id 2014-9-26 用于增加记录收款合同号 */ v_csh_transaction_rec.ref_contract_id := p_ref_contract_id; v_csh_transaction_rec.ref_n01 := p_ref_n01; --add by chenlingfeng v_csh_transaction_rec.bp_bank_name := p_bp_bank_name; v_csh_transaction_rec.bp_bank_branch_name := p_bp_bank_branch_name; v_csh_transaction_rec.collection_classes := p_collection_classes; v_csh_transaction_rec.review_status := 'NOT'; v_csh_transaction_rec.opposite_band_na := p_opposite_band_na; v_csh_transaction_rec.banka := p_banka; v_csh_transaction_rec.purpose := p_purpose; v_csh_transaction_rec.gsber := p_gsber; v_csh_transaction_rec.sus_belnr := p_sus_belnr; v_csh_transaction_rec.assigns_contract_id := p_assigns_contract_id; v_csh_transaction_rec.conds_account_num := p_conds_account_num; v_csh_transaction_rec.conds_account_name := p_conds_account_name; v_csh_transaction_rec.bp_virtual_bank_account := p_bp_virtual_bank_account; --end v_csh_transaction_rec.zuonr := p_zuonr; -- add by sf 增加代付以及备注字段 v_csh_transaction_rec.ref_v05 := p_ref_v05; v_csh_transaction_rec.paid_byother_flag := p_paid_byother_flag; v_csh_transaction_rec.confirmed_flag := 'NEW'; v_csh_transaction_rec.write_off_flag := 'DISABLED'; --将退款金额置为0 v_csh_transaction_rec.returned_amount := 0; --end; insert_csh_transaction(v_csh_transaction_rec); --add by Harry 9952 2018/11/13 IF p_return_id IS NOT NULL THEN UPDATE csh_transaction_return r SET r.return_transaction_id = v_csh_transaction_id WHERE r.return_id = p_return_id; END IF; check_zero_amount(p_csh_transaction_rec => v_csh_transaction_rec, p_user_id => p_user_id); --针对界面保存并过账按钮处理 IF nvl(p_posted_flag, csh_transaction_pkg.csh_trx_posted_flag_no) = csh_transaction_pkg.csh_trx_posted_flag_yes THEN post_csh_transaction(p_transaction_id => v_csh_transaction_rec.transaction_id, p_user_id => p_user_id); END IF; p_transaction_id := v_csh_transaction_rec.transaction_id; EXCEPTION --add by shen-- WHEN e_slip_num_already_exist THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'E_SLIP_NUM_ALREADY_EXIST', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'save_csh_transaction_data'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); --end; 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_transaction_pkg', p_procedure_function_name => 'insert_csh_transaction'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; /*add by xuls for ref_contract_id 2014-9-26 用于增加记录收款合同号 */ PROCEDURE update_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_transaction_num csh_transaction.transaction_num%TYPE, p_transaction_category csh_transaction.transaction_category%TYPE, p_transaction_type csh_transaction.transaction_type%TYPE, p_transaction_date csh_transaction.transaction_date%TYPE, p_penalty_calc_date csh_transaction.penalty_calc_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_company_id csh_transaction.company_id%TYPE, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_distribution_set_id csh_transaction.distribution_set_id%TYPE, p_cashflow_amount csh_transaction.cashflow_amount%TYPE, p_currency_code csh_transaction.currency_code%TYPE, p_transaction_amount csh_transaction.transaction_amount%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_bp_category csh_transaction.bp_category%TYPE, p_bp_id csh_transaction.bp_id%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_description csh_transaction.description%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_posted_flag csh_transaction.posted_flag%TYPE, p_reversed_flag csh_transaction.reversed_flag%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_returned_flag csh_transaction.returned_flag%TYPE, p_returned_amount csh_transaction.returned_amount%TYPE, p_write_off_flag csh_transaction.write_off_flag%TYPE, p_write_off_amount csh_transaction.write_off_amount%TYPE, p_full_write_off_date csh_transaction.full_write_off_date%TYPE, p_twin_csh_trx_id csh_transaction.twin_csh_trx_id%TYPE, p_return_from_csh_trx_id csh_transaction.return_from_csh_trx_id%TYPE, p_reversed_csh_trx_id csh_transaction.reversed_csh_trx_id%TYPE, p_source_csh_trx_type csh_transaction.source_csh_trx_type%TYPE, p_source_csh_trx_id csh_transaction.source_csh_trx_id%TYPE, p_source_doc_category csh_transaction.source_doc_category%TYPE, p_source_doc_type csh_transaction.source_doc_type%TYPE, p_source_doc_id csh_transaction.source_doc_id%TYPE, p_source_doc_line_id csh_transaction.source_doc_line_id%TYPE, p_create_je_mothed csh_transaction.create_je_mothed%TYPE, p_create_je_flag csh_transaction.create_je_flag%TYPE, p_gld_interface_flag csh_transaction.gld_interface_flag%TYPE, p_user_id csh_transaction.created_by%TYPE, p_ref_contract_id csh_transaction.ref_contract_id%TYPE DEFAULT NULL, p_ref_n01 NUMBER DEFAULT NULL, p_bp_bank_name VARCHAR2 DEFAULT NULL, p_bp_bank_branch_name VARCHAR2 DEFAULT NULL, p_collection_classes VARCHAR2 DEFAULT NULL, p_bp_bank_account_name VARCHAR2 DEFAULT NULL, p_opposite_band_na VARCHAR2 DEFAULT NULL, p_banka VARCHAR2 DEFAULT NULL, p_purpose VARCHAR2 DEFAULT NULL, p_gsber VARCHAR2 DEFAULT NULL, p_sus_belnr VARCHAR2 DEFAULT NULL, p_bp_virtual_bank_account VARCHAR2 DEFAULT NULL) IS v_csh_transaction_rec csh_transaction%ROWTYPE; e_status_error EXCEPTION; e_transaction_type_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); IF NOT is_trx_update(v_csh_transaction_rec) THEN RAISE e_status_error; END IF; IF v_csh_transaction_rec.transaction_type <> csh_transaction_pkg.csh_trx_type_receipt THEN RAISE e_transaction_type_error; END IF; v_csh_transaction_rec.transaction_amount := p_transaction_amount; v_csh_transaction_rec.bank_account_id := p_bank_account_id; check_zero_amount(p_csh_transaction_rec => v_csh_transaction_rec, p_user_id => p_user_id); UPDATE csh_transaction t1 SET t1.transaction_category = p_transaction_category, t1.transaction_type = p_transaction_type, t1.transaction_date = p_transaction_date, t1.penalty_calc_date = p_penalty_calc_date, t1.bank_slip_num = p_bank_slip_num, t1.company_id = p_company_id, t1.internal_period_num = p_internal_period_num, t1.period_name = p_period_name, t1.payment_method_id = p_payment_method_id, t1.distribution_set_id = p_distribution_set_id, t1.cashflow_amount = p_cashflow_amount, t1.currency_code = p_currency_code, t1.transaction_amount = p_transaction_amount, t1.exchange_rate_type = p_exchange_rate_type, t1.exchange_rate_quotation = p_exchange_rate_quotation, t1.exchange_rate = p_exchange_rate, t1.bank_account_id = p_bank_account_id, t1.bp_category = p_bp_category, t1.bp_id = p_bp_id, t1.bp_bank_account_id = p_bp_bank_account_id, t1.bp_bank_account_num = p_bp_bank_account_num, t1.description = p_description, t1.handling_charge = p_handling_charge, t1.posted_flag = csh_transaction_pkg.csh_trx_posted_flag_no, t1.reversed_flag = nvl(p_reversed_flag, csh_transaction_pkg.csh_trx_reversed_flag_n), t1.reversed_date = p_reversed_date, t1.returned_flag = nvl(p_returned_flag, csh_transaction_pkg.csh_trx_return_flag_n), t1.returned_amount = p_returned_amount, t1.write_off_flag = nvl(p_write_off_flag, csh_transaction_pkg.csh_trx_write_off_flag_n), t1.write_off_amount = p_write_off_amount, t1.full_write_off_date = p_full_write_off_date, t1.twin_csh_trx_id = p_twin_csh_trx_id, t1.return_from_csh_trx_id = p_return_from_csh_trx_id, t1.reversed_csh_trx_id = p_reversed_csh_trx_id, t1.source_csh_trx_type = p_source_csh_trx_type, t1.source_csh_trx_id = p_source_csh_trx_id, t1.source_doc_category = p_source_doc_category, t1.source_doc_type = p_source_doc_type, t1.source_doc_id = p_source_doc_id, t1.source_doc_line_id = p_source_doc_line_id, t1.create_je_mothed = p_create_je_mothed, t1.create_je_flag = nvl(p_create_je_flag, 'N'), t1.gld_interface_flag = nvl(p_gld_interface_flag, 'N'), t1.last_update_date = SYSDATE, t1.last_updated_by = p_user_id, t1.ref_contract_id = p_ref_contract_id, t1.ref_n01 = p_ref_n01, --add by chenlingfeng t1.bp_bank_name = p_bp_bank_name, t1.bp_bank_branch_name = p_bp_bank_branch_name, t1.bp_bank_account_name = p_bp_bank_account_name, t1.collection_classes = p_collection_classes, t1.opposite_band_na = p_opposite_band_na, t1.banka = p_banka, t1.purpose = p_purpose, t1.gsber = p_gsber, t1.sus_belnr = p_sus_belnr, t1.bp_virtual_bank_account = p_bp_virtual_bank_account --end WHERE t1.transaction_id = p_transaction_id; --针对界面保存并过账按钮处理 IF nvl(p_posted_flag, csh_transaction_pkg.csh_trx_posted_flag_no) = csh_transaction_pkg.csh_trx_posted_flag_yes THEN post_csh_transaction(p_transaction_id => v_csh_transaction_rec.transaction_id, p_user_id => p_user_id); END IF; EXCEPTION WHEN e_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_UPDATE_STATUS_CHECK', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'update_status_check'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); WHEN e_transaction_type_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_TRANSACTION_TYPE_CHECK', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'update_status_check'); 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_transaction_pkg', p_procedure_function_name => 'update_csh_transaction'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --货币兑换维护 PROCEDURE update_currency_exchange_trx(p_transaction_id csh_transaction.transaction_id%TYPE, p_transaction_num csh_transaction.transaction_num%TYPE, p_transaction_category csh_transaction.transaction_category%TYPE, p_transaction_type csh_transaction.transaction_type%TYPE, p_transaction_date csh_transaction.transaction_date%TYPE, p_penalty_calc_date csh_transaction.penalty_calc_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_company_id csh_transaction.company_id%TYPE, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_distribution_set_id csh_transaction.distribution_set_id%TYPE, p_cashflow_amount csh_transaction.cashflow_amount%TYPE, p_currency_code csh_transaction.currency_code%TYPE, p_transaction_amount csh_transaction.transaction_amount%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_bp_category csh_transaction.bp_category%TYPE, p_bp_id csh_transaction.bp_id%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_description csh_transaction.description%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_posted_flag csh_transaction.posted_flag%TYPE, p_reversed_flag csh_transaction.reversed_flag%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_returned_flag csh_transaction.returned_flag%TYPE, p_returned_amount csh_transaction.returned_amount%TYPE, p_write_off_flag csh_transaction.write_off_flag%TYPE, p_write_off_amount csh_transaction.write_off_amount%TYPE, p_full_write_off_date csh_transaction.full_write_off_date%TYPE, p_twin_csh_trx_id csh_transaction.twin_csh_trx_id%TYPE, p_return_from_csh_trx_id csh_transaction.return_from_csh_trx_id%TYPE, p_reversed_csh_trx_id csh_transaction.reversed_csh_trx_id%TYPE, p_source_csh_trx_type csh_transaction.source_csh_trx_type%TYPE, p_source_csh_trx_id csh_transaction.source_csh_trx_id%TYPE, p_source_doc_category csh_transaction.source_doc_category%TYPE, p_source_doc_type csh_transaction.source_doc_type%TYPE, p_source_doc_id csh_transaction.source_doc_id%TYPE, p_source_doc_line_id csh_transaction.source_doc_line_id%TYPE, p_create_je_mothed csh_transaction.create_je_mothed%TYPE, p_create_je_flag csh_transaction.create_je_flag%TYPE, p_gld_interface_flag csh_transaction.gld_interface_flag%TYPE, p_user_id csh_transaction.created_by%TYPE) IS v_csh_transaction_rec csh_transaction%ROWTYPE; e_status_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); IF NOT is_trx_update(v_csh_transaction_rec) THEN RAISE e_status_error; END IF; v_csh_transaction_rec.transaction_amount := p_transaction_amount; v_csh_transaction_rec.bank_account_id := p_bank_account_id; check_zero_amount(p_csh_transaction_rec => v_csh_transaction_rec, p_user_id => p_user_id); UPDATE csh_transaction t1 SET t1.transaction_category = p_transaction_category, t1.transaction_type = p_transaction_type, t1.transaction_date = p_transaction_date, t1.penalty_calc_date = p_penalty_calc_date, t1.bank_slip_num = p_bank_slip_num, t1.company_id = p_company_id, t1.internal_period_num = p_internal_period_num, t1.period_name = p_period_name, t1.payment_method_id = p_payment_method_id, t1.distribution_set_id = p_distribution_set_id, t1.cashflow_amount = p_cashflow_amount, t1.currency_code = p_currency_code, t1.transaction_amount = p_transaction_amount, t1.exchange_rate_type = p_exchange_rate_type, t1.exchange_rate_quotation = p_exchange_rate_quotation, t1.exchange_rate = p_exchange_rate, t1.bank_account_id = p_bank_account_id, t1.bp_category = p_bp_category, t1.bp_id = p_bp_id, t1.bp_bank_account_id = p_bp_bank_account_id, t1.bp_bank_account_num = p_bp_bank_account_num, t1.description = p_description, t1.handling_charge = p_handling_charge, t1.posted_flag = csh_transaction_pkg.csh_trx_posted_flag_no, t1.reversed_flag = nvl(p_reversed_flag, csh_transaction_pkg.csh_trx_reversed_flag_n), t1.reversed_date = p_reversed_date, t1.returned_flag = nvl(p_returned_flag, csh_transaction_pkg.csh_trx_return_flag_n), t1.returned_amount = p_returned_amount, t1.write_off_flag = nvl(p_write_off_flag, csh_transaction_pkg.csh_trx_write_off_flag_n), t1.write_off_amount = p_write_off_amount, t1.full_write_off_date = p_full_write_off_date, t1.twin_csh_trx_id = p_twin_csh_trx_id, t1.return_from_csh_trx_id = p_return_from_csh_trx_id, t1.reversed_csh_trx_id = p_reversed_csh_trx_id, t1.source_csh_trx_type = p_source_csh_trx_type, t1.source_csh_trx_id = p_source_csh_trx_id, t1.source_doc_category = p_source_doc_category, t1.source_doc_type = p_source_doc_type, t1.source_doc_id = p_source_doc_id, t1.source_doc_line_id = p_source_doc_line_id, t1.create_je_mothed = p_create_je_mothed, t1.create_je_flag = nvl(p_create_je_flag, 'N'), t1.gld_interface_flag = nvl(p_gld_interface_flag, 'N'), t1.last_update_date = SYSDATE, t1.last_updated_by = p_user_id WHERE t1.transaction_id = p_transaction_id; --针对界面保存并过账按钮处理 IF nvl(p_posted_flag, csh_transaction_pkg.csh_trx_posted_flag_no) = csh_transaction_pkg.csh_trx_posted_flag_yes THEN post_csh_transaction(p_transaction_id => v_csh_transaction_rec.transaction_id, p_user_id => p_user_id); END IF; EXCEPTION WHEN e_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_UPDATE_STATUS_CHECK', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'update_currency_exchange_trx'); 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_transaction_pkg', p_procedure_function_name => 'update_currency_exchange_trx'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --货币兑换互记TWIN_CSH_TRX_ID PROCEDURE save_twin_csh_trx_id(p_transaction_num csh_transaction.transaction_num%TYPE, p_user_id NUMBER) IS v_csh_transaction_rec csh_transaction%ROWTYPE; BEGIN FOR r_trx_rec IN (SELECT d.transaction_id FROM csh_transaction d WHERE d.transaction_num = p_transaction_num) LOOP lock_csh_transaction(p_transaction_id => r_trx_rec.transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); UPDATE csh_transaction c SET c.twin_csh_trx_id = r_trx_rec.transaction_id, c.last_updated_by = p_user_id, c.last_update_date = SYSDATE WHERE c.transaction_num = p_transaction_num AND c.transaction_id <> r_trx_rec.transaction_id; END LOOP; EXCEPTION WHEN OTHERS THEN sys_raise_app_error_pkg.raise_sys_others_error(p_message => dbms_utility.format_error_backtrace || ' ' || SQLERRM, p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'save_twin_csh_trx_id'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --货币兑换过账以后维护 PROCEDURE upd_currency_trx_after_post(p_transaction_id csh_transaction.transaction_id%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_description csh_transaction.description%TYPE, p_user_id csh_transaction.created_by%TYPE) IS v_csh_transaction_rec csh_transaction%ROWTYPE; e_flag_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); IF NOT is_trx_update_after_post(v_csh_transaction_rec) THEN RAISE e_flag_error; END IF; UPDATE csh_transaction t SET t.bank_slip_num = p_bank_slip_num, t.description = p_description, t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.transaction_id = p_transaction_id; EXCEPTION WHEN e_flag_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => v_err_code, p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'upd_currency_trx_after_post'); 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_transaction_pkg', p_procedure_function_name => 'upd_currency_trx_after_post'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --过账以后维护 PROCEDURE update_csh_transaction_post(p_transaction_id csh_transaction.transaction_id%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_bp_bank_account_id csh_transaction.bp_bank_account_id%TYPE, p_user_id csh_transaction.created_by%TYPE) IS v_csh_transaction_rec csh_transaction%ROWTYPE; e_flag_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); /*IF NOT is_trx_update_after_post(v_csh_transaction_rec) THEN RAISE e_flag_error; END IF;*/ UPDATE csh_transaction t SET t.bank_slip_num = p_bank_slip_num, t.bp_bank_account_id = p_bp_bank_account_id, t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.transaction_id = p_transaction_id; EXCEPTION WHEN e_flag_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => v_err_code, p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'update_status_check'); 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_transaction_pkg', p_procedure_function_name => 'update_csh_transaction_post'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE update_csh_post_flag(p_transaction_id NUMBER, p_user_id NUMBER, p_claim VARCHAR2 DEFAULT NULL) IS BEGIN /*update csh_transaction ct set ct.posted_flag = 'Y', ct.last_updated_by = p_user_id where ct.transaction_id = p_transaction_id;*/ IF p_claim IS NULL THEN UPDATE csh_transaction ct SET ct.posted_flag = 'Y', ct.posted_date = trunc(SYSDATE), ct.last_updated_by = p_user_id WHERE ct.transaction_id = p_transaction_id; ELSIF p_claim = 'F' THEN UPDATE csh_transaction ct SET ct.posted_flag = 'F', ct.last_updated_by = p_user_id WHERE ct.transaction_id = p_transaction_id; ELSIF p_claim = 'Y' THEN UPDATE csh_transaction ct SET ct.posted_flag = 'Y', ct.posted_date = trunc(SYSDATE), ct.last_updated_by = p_user_id WHERE ct.transaction_id = p_transaction_id; END IF; END; PROCEDURE delete_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_user_id csh_transaction.created_by%TYPE) IS v_csh_transaction_rec csh_transaction%ROWTYPE; e_status_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); IF NOT is_trx_delete(v_csh_transaction_rec) THEN RAISE e_status_error; END IF; DELETE FROM csh_transaction t WHERE t.transaction_id = p_transaction_id; EXCEPTION WHEN e_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => v_err_code, p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'delete_status_check', p_token_1 => '#TRANSACTION_NUM', p_token_value_1 => v_csh_transaction_rec.transaction_num); 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_transaction_pkg', p_procedure_function_name => 'delete_csh_transaction'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; PROCEDURE post_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_user_id csh_transaction.created_by%TYPE) IS v_csh_transaction_rec csh_transaction%ROWTYPE; e_status_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); /*IF NOT is_trx_post(v_csh_transaction_rec) THEN v_err_code := 'CSH_TRANSACTION_PKG.CSH510_POSTED_ERROR'; RAISE e_status_error; END IF;*/ IF v_csh_transaction_rec.bp_virtual_bank_account IS NOT NULL THEN UPDATE csh_transaction t SET t.bp_bank_account_num = v_csh_transaction_rec.bp_virtual_bank_account, t.bp_virtual_bank_account = v_csh_transaction_rec.bp_bank_account_num WHERE t.transaction_id = v_csh_transaction_rec.transaction_id; END IF; /*UPDATE csh_transaction t SET t.posted_flag = csh_transaction_pkg.csh_trx_posted_flag_yes, t.POSTED_DATE = trunc(SYSDATE), t.last_update_date = SYSDATE, t.last_updated_by = p_user_id WHERE t.transaction_id = v_csh_transaction_rec.transaction_id; --现金事务过账生成的凭证 csh_transaction_je_pkg.create_transaction_je(p_transaction_id => p_transaction_id, p_user_id => p_user_id);*/ csh_transaction_custom_pkg.after_post_trx(p_transaction_id => p_transaction_id, p_user_id => p_user_id); EXCEPTION WHEN e_status_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => v_err_code, p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'post_csh_transaction', p_token_1 => '#TRANSACTION_NUM', p_token_value_1 => v_csh_transaction_rec.transaction_num); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --通用的反冲入口(收款反冲,付款反冲,收款退款反冲,预收款退款反冲) PROCEDURE reverse_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_description csh_transaction.description%TYPE, p_user_id csh_transaction.created_by%TYPE, p_reverse_transaction_id OUT csh_transaction.transaction_id%TYPE) IS v_period_name VARCHAR2(30); v_transaction_id NUMBER; v_write_off_id NUMBER; v_internal_period_num gld_periods.internal_period_num%TYPE; v_csh_transaction_rec csh_transaction%ROWTYPE; e_reverse_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); IF NOT is_trx_reverse(v_csh_transaction_rec) THEN RAISE e_reverse_error; END IF; --校验反冲日期 modify by zhuxianfei 20140224 IF trunc(p_reversed_date) < trunc(v_csh_transaction_rec.transaction_date) THEN v_err_code := 'CSH_TRANSACTION_PKG.REVERSE_DATE_ERROR'; RAISE e_reverse_error; END IF; -- 期间 v_period_name := gld_common_pkg.get_gld_period_name(p_company_id => v_csh_transaction_rec.company_id, p_date => p_reversed_date); IF v_period_name IS NULL THEN v_err_code := 'CSH_TRANSACTION_PKG.REVERSE_PERIOD_ERROR'; RAISE e_reverse_error; END IF; v_internal_period_num := gld_common_pkg.get_gld_internal_period_num(p_company_id => v_csh_transaction_rec.company_id, p_period_name => v_period_name); IF v_internal_period_num IS NULL THEN v_err_code := 'CSH_TRANSACTION_PKG.REVERSE_PERIOD_NUM_ERROR'; RAISE e_reverse_error; END IF; --预收款和收款的退款反冲 IF v_csh_transaction_rec.transaction_category = csh_trx_category_business AND (v_csh_transaction_rec.transaction_type = csh_trx_type_prereceipt OR nvl(v_csh_transaction_rec.returned_flag, csh_trx_return_flag_n) = csh_trx_return_flag_return) THEN --退款反冲后更新原退款现金事务信息 IF v_csh_transaction_rec.returned_flag = csh_trx_return_flag_return THEN upd_rtn_csh_trx_after_reverse(p_transaction_id => p_transaction_id, p_user_id => p_user_id); END IF; FOR csh_write_off_rec IN (SELECT * FROM csh_write_off t WHERE t.subsequent_csh_trx_id = p_transaction_id AND nvl(t.reversed_flag, csh_trx_reversed_flag_n) = csh_trx_reversed_flag_n) LOOP csh_write_off_pkg.reverse_write_off(p_reverse_write_off_id => v_write_off_id, p_write_off_id => csh_write_off_rec.write_off_id, p_reversed_date => p_reversed_date, p_description => p_description, p_user_id => p_user_id, p_from_csh_trx_flag => 'Y'); END LOOP; --付款反冲中的支付反冲 ELSIF v_csh_transaction_rec.transaction_category = csh_trx_category_business AND v_csh_transaction_rec.transaction_type = csh_trx_type_payment THEN --更新付款申请 csh_payment_req_pkg.upd_csh_payment_after_reverse(p_transaction_id => p_transaction_id, p_user_id => p_user_id); FOR csh_write_off_rec IN (SELECT * FROM csh_write_off t WHERE t.csh_transaction_id = p_transaction_id AND nvl(t.reversed_flag, csh_trx_reversed_flag_n) = csh_trx_reversed_flag_n) LOOP csh_write_off_pkg.reverse_write_off(p_reverse_write_off_id => v_write_off_id, p_write_off_id => csh_write_off_rec.write_off_id, p_reversed_date => p_reversed_date, p_description => p_description, p_user_id => p_user_id, p_from_csh_trx_flag => 'Y'); END LOOP; ELSIF v_csh_transaction_rec.transaction_category = csh_trx_category_business AND v_csh_transaction_rec.transaction_type = csh_trx_type_deduction THEN FOR csh_write_off_rec IN (SELECT * FROM csh_write_off t WHERE t.csh_transaction_id = p_transaction_id AND nvl(t.reversed_flag, csh_trx_reversed_flag_n) = csh_trx_reversed_flag_n) LOOP csh_write_off_pkg.reverse_write_off(p_reverse_write_off_id => v_write_off_id, p_write_off_id => csh_write_off_rec.write_off_id, p_reversed_date => p_reversed_date, p_description => p_description, p_user_id => p_user_id, p_from_csh_trx_flag => 'Y'); END LOOP; END IF; lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); insert_csh_transaction(p_transaction_id => v_transaction_id, p_transaction_num => '', p_transaction_category => v_csh_transaction_rec.transaction_category, p_transaction_type => v_csh_transaction_rec.transaction_type, p_transaction_date => p_reversed_date, p_penalty_calc_date => v_csh_transaction_rec.penalty_calc_date, p_bank_slip_num => v_csh_transaction_rec.bank_slip_num, p_company_id => v_csh_transaction_rec.company_id, p_internal_period_num => v_internal_period_num, p_period_name => v_period_name, p_payment_method_id => v_csh_transaction_rec.payment_method_id, p_distribution_set_id => v_csh_transaction_rec.distribution_set_id, p_cashflow_amount => -1 * v_csh_transaction_rec.cashflow_amount, p_currency_code => v_csh_transaction_rec.currency_code, p_transaction_amount => -1 * v_csh_transaction_rec.transaction_amount, p_exchange_rate_type => v_csh_transaction_rec.exchange_rate_type, p_exchange_rate_quotation => v_csh_transaction_rec.exchange_rate_quotation, p_exchange_rate => v_csh_transaction_rec.exchange_rate, p_bank_account_id => v_csh_transaction_rec.bank_account_id, p_bp_category => v_csh_transaction_rec.bp_category, p_bp_id => v_csh_transaction_rec.bp_id, p_bp_bank_account_id => v_csh_transaction_rec.bp_bank_account_id, p_bp_bank_account_num => v_csh_transaction_rec.bp_bank_account_num, p_description => p_description, p_handling_charge => v_csh_transaction_rec.handling_charge, p_posted_flag => csh_transaction_pkg.csh_trx_posted_flag_yes, p_reversed_flag => csh_transaction_pkg.csh_trx_reversed_flag_r, p_reversed_date => '', p_returned_flag => v_csh_transaction_rec.returned_flag, p_returned_amount => v_csh_transaction_rec.returned_amount, p_write_off_flag => v_csh_transaction_rec.write_off_flag, p_write_off_amount => v_csh_transaction_rec.write_off_amount, p_full_write_off_date => v_csh_transaction_rec.full_write_off_date, p_twin_csh_trx_id => v_csh_transaction_rec.twin_csh_trx_id, p_return_from_csh_trx_id => v_csh_transaction_rec.return_from_csh_trx_id, p_reversed_csh_trx_id => p_transaction_id, p_source_csh_trx_type => v_csh_transaction_rec.source_csh_trx_type, p_source_csh_trx_id => v_csh_transaction_rec.source_csh_trx_id, p_source_doc_category => v_csh_transaction_rec.source_doc_category, p_source_doc_type => v_csh_transaction_rec.source_doc_type, p_source_doc_id => v_csh_transaction_rec.source_doc_id, p_source_doc_line_id => v_csh_transaction_rec.source_doc_line_id, p_create_je_mothed => v_csh_transaction_rec.create_je_mothed, p_create_je_flag => 'N', p_gld_interface_flag => 'N', p_user_id => p_user_id, p_csh_bp_name => v_csh_transaction_rec.csh_bp_name, --add by sf p_paid_byother_flag => v_csh_transaction_rec.paid_byother_flag, p_ref_v05 => v_csh_transaction_rec.ref_v05 --end; ); --更新被反冲现金事务 update_csh_trx_after_reverse(p_transaction_id => p_transaction_id, p_reversed_date => p_reversed_date, p_reserved_csh_trx_id => v_transaction_id, p_user_id => p_user_id); --生成现金事务的过账反冲凭证 csh_transaction_je_pkg.create_transaction_reverse_je(p_transaction_id => v_transaction_id, p_user_id => p_user_id); p_reverse_transaction_id := v_transaction_id; EXCEPTION WHEN e_reverse_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => v_err_code, p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'reverse_csh_transaction', p_token_1 => '#TRANSACTION_NUM', p_token_value_1 => v_csh_transaction_rec.transaction_num); 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_transaction_pkg', p_procedure_function_name => 'reverse_csh_transaction'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --收款反冲入口 PROCEDURE reverse_receipt_csh_trx(p_transaction_id csh_transaction.transaction_id%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_description csh_transaction.description%TYPE, p_user_id csh_transaction.created_by%TYPE) IS e_reverse_error EXCEPTION; v_csh_transaction_rec csh_transaction%ROWTYPE; v_transaction_id csh_transaction.transaction_id%TYPE; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); reverse_csh_transaction(p_transaction_id => p_transaction_id, p_reversed_date => p_reversed_date, p_description => p_description, p_user_id => p_user_id, p_reverse_transaction_id => v_transaction_id); --更新银行水单 update_bank_slip_num(p_transaction_id => v_transaction_id, p_bank_slip_num => p_bank_slip_num, p_user_id => p_user_id); -- 反冲回来 过账标志变为N UPDATE csh_transaction ct SET ct.posted_flag = 'N', ct.posted_date = NULL, ct.reversed_flag = 'N' WHERE ct.transaction_id = p_transaction_id; EXCEPTION WHEN e_reverse_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => v_err_code, p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'reverse_receipt_csh_trx', p_token_1 => '#TRANSACTION_NUM', p_token_value_1 => v_csh_transaction_rec.transaction_num); 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_transaction_pkg', p_procedure_function_name => 'reverse_receipt_csh_trx'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --收款退款入口 PROCEDURE return_csh_transaction(p_transaction_id csh_transaction.transaction_id%TYPE, p_returned_date DATE, p_returned_amount csh_transaction.returned_amount%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_bank_account_id csh_transaction.bank_account_id%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_description csh_transaction.description%TYPE, p_user_id csh_transaction.created_by%TYPE, p_return_id NUMBER DEFAULT NULL) IS v_period_name VARCHAR2(30); v_transaction_id NUMBER; v_write_off_id NUMBER; v_internal_period_num gld_periods.internal_period_num%TYPE; v_csh_transaction_rec csh_transaction%ROWTYPE; v_return_transaction_rec csh_transaction%ROWTYPE; v_fin_create_je_flag NUMBER; e_return_error EXCEPTION; BEGIN lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); IF NOT csh_transaction_pkg.is_trx_return(v_csh_transaction_rec) THEN v_err_code := 'CSH_TRANSACTION_PKG.CSH_TRX_RETURN_FLAG_ERROR'; RAISE e_return_error; END IF; --校验退款日期 IF p_returned_date < v_csh_transaction_rec.transaction_date THEN v_err_code := 'CSH_TRANSACTION_PKG.RETURN_DATE_ERROR'; RAISE e_return_error; END IF; -- 期间 v_period_name := gld_common_pkg.get_gld_period_name(p_company_id => v_csh_transaction_rec.company_id, p_date => p_returned_date); IF v_period_name IS NULL THEN v_err_code := 'CSH_TRANSACTION_PKG.RETURN_PERIOD_ERROR'; RAISE e_return_error; END IF; v_internal_period_num := gld_common_pkg.get_gld_internal_period_num(p_company_id => v_csh_transaction_rec.company_id, p_period_name => v_period_name); IF v_internal_period_num IS NULL THEN v_err_code := 'CSH_TRANSACTION_PKG.RETURN_PERIOD_NUM_ERROR'; RAISE e_return_error; END IF; insert_csh_transaction(p_transaction_id => v_transaction_id, p_transaction_num => '', p_transaction_category => v_csh_transaction_rec.transaction_category, p_transaction_type => v_csh_transaction_rec.transaction_type, p_transaction_date => trunc(SYSDATE), p_penalty_calc_date => '', p_bank_slip_num => p_bank_slip_num, p_company_id => v_csh_transaction_rec.company_id, p_internal_period_num => v_internal_period_num, p_period_name => v_period_name, p_payment_method_id => p_payment_method_id, p_distribution_set_id => v_csh_transaction_rec.distribution_set_id, p_cashflow_amount => -1 * p_returned_amount, p_currency_code => v_csh_transaction_rec.currency_code, p_transaction_amount => p_returned_amount, p_exchange_rate_type => v_csh_transaction_rec.exchange_rate_type, p_exchange_rate_quotation => v_csh_transaction_rec.exchange_rate_quotation, p_exchange_rate => v_csh_transaction_rec.exchange_rate, p_bank_account_id => p_bank_account_id, p_bp_category => v_csh_transaction_rec.bp_category, p_bp_id => v_csh_transaction_rec.bp_id, p_bp_bank_account_id => p_bp_bank_account_id, p_bp_bank_account_num => p_bp_bank_account_num, p_description => p_description, p_handling_charge => '', p_posted_flag => csh_transaction_pkg.csh_trx_posted_flag_yes, p_reversed_flag => csh_transaction_pkg.csh_trx_reversed_flag_n, p_reversed_date => '', p_returned_flag => csh_transaction_pkg.csh_trx_return_flag_return, p_returned_amount => '', p_write_off_flag => csh_transaction_pkg.csh_trx_write_off_flag_f, p_write_off_amount => '', p_full_write_off_date => '', p_twin_csh_trx_id => '', p_return_from_csh_trx_id => p_transaction_id, p_reversed_csh_trx_id => '', p_source_csh_trx_type => v_csh_transaction_rec.transaction_type, p_source_csh_trx_id => p_transaction_id, p_source_doc_category => '', p_source_doc_type => '', p_source_doc_id => '', p_source_doc_line_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 => v_csh_transaction_rec.csh_bp_name, p_collection_classes => v_csh_transaction_rec.collection_classes, p_return_id => p_return_id, --add by sf p_paid_byother_flag => v_csh_transaction_rec.paid_byother_flag, p_ref_v05 => v_csh_transaction_rec.ref_v05 --end; ); SELECT * INTO v_return_transaction_rec FROM csh_transaction ct WHERE ct.transaction_id = v_transaction_id; --更新被退款现金事务信息 update_csh_trx_after_return(p_transaction_id => p_transaction_id, p_returned_amount => p_returned_amount, p_user_id => p_user_id); --收款、预收款的退款需要在核销表中插一条退款事务 --add by syj 保证金池退款 IF v_csh_transaction_rec.transaction_type IN (csh_trx_type_prereceipt, csh_trx_type_receipt, csh_trx_type_deposit, 'DEPOSIT_POOL_RECEIPT') THEN csh_write_off_pkg.return_write_off(p_return_write_off_id => v_write_off_id, p_transaction_id => p_transaction_id, p_return_transaction_id => v_transaction_id, p_returned_date => p_returned_date, p_returned_amount => p_returned_amount, p_description => p_description, p_user_id => p_user_id); END IF; --modif by nick 冯 for derong 吉利二开部分,为了是以退款金额来出凭证,注释掉,使用上面write off pkg 里出凭证。 /* IF v_csh_transaction_rec.transaction_type = 'ADVANCE_RECEIPT' THEN v_fin_create_je_flag := gld_je_template_pkg.create_je(p_je_transaction_code => 'CSH_CONSOLIDATION', p_company_id => v_return_transaction_rec.company_id, p_je_company_id => NULL, p_user_id => p_user_id, p_journal_header_id => NULL, p_hd_source_table => 'CSH_TRANSACTION', p_hd_source_id => v_transaction_id, p_ln_source_table => 'CSH_TRANSACTION', p_ln_source_id => v_transaction_id, p_journal_date => v_return_transaction_rec.transaction_date, p_transaction_date => v_return_transaction_rec.transaction_date, p_currency_code => v_return_transaction_rec.currency_code, p_exchange_rate_quotation => v_return_transaction_rec.exchange_rate_quotation, p_hd_exchange_rate_type => v_return_transaction_rec.exchange_rate_type, p_hd_exchange_rate => v_return_transaction_rec.exchange_rate, p_ln_exchange_rate_type => v_return_transaction_rec.exchange_rate_type, p_ln_exchange_rate => v_return_transaction_rec.exchange_rate, p_account_indicator => NULL, p_ignore_history_check => NULL, p_reverse_hd_source_id => NULL); IF v_fin_create_je_flag = 0 THEN UPDATE csh_transaction SET create_je_flag = 'Y', last_update_date = SYSDATE, last_updated_by = p_user_id WHERE transaction_id = v_transaction_id; END IF; END IF;*/ EXCEPTION WHEN e_return_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => v_err_code, p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'return_csh_transaction', p_token_1 => '#TRANSACTION_NUM', p_token_value_1 => v_csh_transaction_rec.transaction_num); 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_transaction_pkg', p_procedure_function_name => 'return_csh_transaction'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; ---add by zlf 2014/11/24 PROCEDURE delete_interface(p_batch_id NUMBER, p_user_id NUMBER) IS BEGIN DELETE FROM csh_transaction_temp t; --WHERE t.batch_id = p_batch_id; 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_TRANSACTION', p_procedure_function_name => 'DELETE_INTERFACE'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END delete_interface; PROCEDURE insert_interface(p_header_id NUMBER, p_batch_id NUMBER, p_user_id NUMBER) IS CURSOR c_data IS SELECT * FROM fnd_interface_lines WHERE header_id = p_header_id AND line_number >= 2; BEGIN --删除临时表数据 delete_interface(p_header_id, p_user_id); FOR c_record IN c_data LOOP INSERT INTO csh_transaction_temp (batch_id, csh_transaction_temp_id, transaction_date, description, transaction_amount, bp_bank_account_num, bp_name, project_number, bank_account_num, --bank_serial_num, --sys_date, created_by, creation_date, last_updated_by, last_update_date) VALUES (p_batch_id, csh_transaction_temp_s.nextval, to_date(c_record.attribute_1, 'YYYY-MM-DD'), --日期 c_record.attribute_2, --备注 to_number(c_record.attribute_3), --金额 c_record.attribute_4, --打款账号 c_record.attribute_5, --打款账户名 c_record.attribute_6, --订单编号 c_record.attribute_7, --收款账号 --to_date(c_record.attribute_8, 'YYYY-MM-DD'), p_user_id, SYSDATE, p_user_id, SYSDATE); END LOOP; END; PROCEDURE delete_temp(p_batch_id NUMBER) IS BEGIN --delete from fnd_interface_lines t where t.line_id = p_batch_id; DELETE FROM csh_transaction_temp t WHERE t.batch_id = p_batch_id; DELETE FROM csh_transaction_temp_logs; END delete_temp; PROCEDURE delete_error_logs(p_batch_id NUMBER) IS BEGIN DELETE FROM csh_transaction_temp_logs a WHERE a.batch_id = p_batch_id; END delete_error_logs; PROCEDURE txn_log(p_temp IN csh_transaction_temp%ROWTYPE, p_log_text IN VARCHAR2, p_user_id IN NUMBER) IS BEGIN INSERT INTO csh_transaction_temp_logs (batch_id, csh_transaction_temp_log_id, transaction_date, description, transaction_amount, bp_bank_account_num, bp_name, bank_account_num, created_by, creation_date, last_updated_by, last_update_date, log_text) VALUES (p_temp.batch_id, csh_transaction_temp_logs_s.nextval, p_temp.transaction_date, p_temp.description, p_temp.transaction_amount, p_temp.bp_bank_account_num, p_temp.bp_name, p_temp.bank_account_num, p_user_id, SYSDATE, p_user_id, SYSDATE, p_log_text); END txn_log; PROCEDURE yonda_auto_write_off_log(p_transaction_id NUMBER DEFAULT NULL, p_write_off_id NUMBER DEFAULT NULL, p_bp_id NUMBER DEFAULT NULL, p_contract_id NUMBER DEFAULT NULL, p_bank_account_id NUMBER DEFAULT NULL, p_message VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO yonda_csh_excel_write_off_log (log_id, log_date, transaction_id, write_off_id, bp_id, contract_id, bank_account_id, message) VALUES (yonda_auto_write_off_log_s.nextval, SYSDATE, p_transaction_id, p_write_off_id, p_bp_id, p_contract_id, p_bank_account_id, p_message); COMMIT; END yonda_auto_write_off_log; PROCEDURE check_data(p_batch_id NUMBER, p_user_id NUMBER, p_check_type VARCHAR2 DEFAULT NULL, p_return_id OUT NUMBER) IS v_bp_name_exist_flag VARCHAR2(2) := 'N'; v_bp_bank_account_exist_flag VARCHAR2(2) := 'N'; v_project_number_exist_flag VARCHAR2(2) := 'N'; v_bp_name_num NUMBER; v_column_num NUMBER := 1; v_line_num NUMBER := 3; v_log_text VARCHAR(500); e_bp_name_num_error EXCEPTION; e_date_null_error EXCEPTION; e_description_null_error EXCEPTION; e_amount_null_error EXCEPTION; e_bp_account_null_error EXCEPTION; e_bank_serial_num_null_error EXCEPTION; BEGIN delete_error_logs(p_batch_id); p_return_id := 1; FOR c_record IN (SELECT * FROM csh_transaction_temp c WHERE c.batch_id = p_batch_id) LOOP BEGIN IF c_record.transaction_date IS NULL THEN RAISE e_date_null_error; END IF; EXCEPTION WHEN e_date_null_error THEN p_return_id := 0; v_column_num := 1; v_log_text := '导入文档中第' || v_line_num || '行第' || v_column_num || '列,入账日期为空'; txn_log(c_record, v_log_text, p_user_id); END; /*begin if c_record.description is null then raise e_description_null_error; end if; exception when e_description_null_error then p_return_id := 0; v_column_num := 2; v_log_text := '导入文档中第' || v_line_num || '行第' || v_column_num || '列,用途为空'; txn_log(c_record, v_log_text, p_user_id); end;*/ BEGIN IF c_record.transaction_amount IS NULL THEN RAISE e_amount_null_error; END IF; EXCEPTION WHEN e_amount_null_error THEN p_return_id := 0; v_column_num := 3; v_log_text := '导入文档中第' || v_line_num || '行第' || v_column_num || '列,入账金额为空'; txn_log(c_record, v_log_text, p_user_id); END; /*begin if c_record.bp_bank_account_num is null then raise e_bp_account_null_error; end if; exception when e_bp_account_null_error then p_return_id := 0; v_column_num := 4; v_log_text := '导入文档中第' || v_line_num || '行第' || v_column_num || '列,对方账户为空'; txn_log(c_record, v_log_text, p_user_id); end;*/ /* begin select 'Y' into v_bp_name_exist_flag from dual where exists (select 1 from con_contract_lv v where v.bp_id_tenant_n = c_record.bp_name and v.data_class = 'NORMAL'); exception when no_data_found then p_return_id := 0; v_column_num := 5; v_log_text := '导入文档中第' || v_line_num || '行第' || v_column_num || '列,对方户名在系统中不存在'; txn_log(c_record, v_log_text, p_user_id); END;*/ /*begin select count(1) into v_bp_name_num from con_contract_lv v where v.bp_id_tenant_n = c_record.bp_name and v.data_class = 'NORMAL'; if v_bp_name_num > 1 then raise e_bp_name_num_error; end if; exception when e_bp_name_num_error then p_return_id := 0; v_column_num := 5; v_log_text := '导入文档中第' || v_line_num || '行第' || v_column_num || '列,对方户名在系统中存在多个'; txn_log(c_record, v_log_text, p_user_id); end;*/ --无支付表 收款导入不校验合同号 2018年3月13日18:17:56 for deron IF p_check_type <> 'TRANSACTION' THEN BEGIN SELECT 'Y' INTO v_project_number_exist_flag FROM dual WHERE EXISTS (SELECT 1 FROM prj_project v WHERE v.project_number = c_record.project_number); EXCEPTION WHEN no_data_found THEN p_return_id := 0; v_column_num := 6; v_log_text := '导入文档中第' || v_line_num || '行第' || v_column_num || '列,订单在系统中不存在'; txn_log(c_record, v_log_text, p_user_id); END; END IF; BEGIN SELECT 'Y' INTO v_bp_bank_account_exist_flag FROM dual WHERE EXISTS (SELECT 1 FROM csh_bank_account_v v WHERE v.bank_account_num = c_record.bank_account_num); EXCEPTION WHEN no_data_found THEN p_return_id := 0; v_column_num := 7; v_log_text := '导入文档中第' || v_line_num || '行第' || v_column_num || '列,收款账号在系统中不存在'; txn_log(c_record, v_log_text, p_user_id); END; --应黄蓉要求 银行流水号 为非必输 /*BEGIN IF c_record.bank_serial_num IS NULL THEN RAISE e_bank_serial_num_null_error; END IF; EXCEPTION WHEN e_bank_serial_num_null_error THEN p_return_id := 0; v_column_num := 7; v_log_text := '导入文档中第' || v_line_num || '行第' || v_column_num || '列,银行流水号为空'; txn_log(c_record, v_log_text, p_user_id); END;*/ v_line_num := v_line_num + 1; END LOOP; END; /*PROCEDURE save_data ( p_batch_id NUMBER, p_user_id NUMBER, p_company_id NUMBER, v_save_message OUT VARCHAR2 ) IS v_bp_id NUMBER; v_bank_account_id NUMBER; v_transaction_id NUMBER; v_transaction_num VARCHAR2(30); v_period_name VARCHAR2(30); v_internal_period_num NUMBER; v_currency_code VARCHAR2(30); v_bp_category VARCHAR2(30); v_bp_bank_account_id NUMBER; v_contract_num NUMBER; v_cashflow_num NUMBER; v_min_times NUMBER; v_session_id NUMBER; v_con_contract_cashflow_rec con_contract_cashflow%ROWTYPE; v_contract_id NUMBER; v_is_error BOOLEAN := FALSE; v_write_off_flag VARCHAR2(30) := 'Y'; e_null_contract_error EXCEPTION; e_too_many_contract_error EXCEPTION; e_null_cashflow_error EXCEPTION; v_contract_rec con_contract%ROWTYPE; BEGIN FOR c_record IN (SELECT * FROM csh_transaction_temp a WHERE a.batch_id = p_batch_id) LOOP -- check bp BEGIN IF c_record.bp_name IS NULL THEN v_bp_id := NULL; v_bp_category := NULL; yonda_auto_write_off_log(p_message => '来自帐号:' || c_record.bank_account_num || '的一笔收款' || c_record.transaction_amount || '没有对方户名,无法匹配商业伙伴,无法自动核销'); ELSE BEGIN SELECT v.bp_id_tenant INTO v_bp_id FROM con_contract_lv v WHERE c_record.bp_name = v.bp_id_tenant_n AND rownum = 1; SELECT t.bp_category INTO v_bp_category FROM hls_bp_master t WHERE t.bp_id = v_bp_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_bp_id := NULL; v_bp_category := NULL; yonda_auto_write_off_log(p_message => c_record.bp_name || ',一笔收款' || c_record.transaction_amount || '未找到对应的商业伙伴,无法自动核销'); END; END IF; END; -- check account BEGIN IF c_record.bank_account_num IS NULL THEN v_bank_account_id := NULL; v_currency_code := 'CNY'; v_bp_bank_account_id := NULL; ELSE SELECT cbav.bank_account_id, cbav.currency_code, nvl(cbav.bank_account_id, NULL) INTO v_bank_account_id, v_currency_code, v_bp_bank_account_id FROM csh_bank_account_v cbav WHERE cbav.bank_account_num = c_record.bank_account_num AND cbav.enabled_flag = 'Y'; END IF; END; SELECT v.period_name, gld_common_pkg.get_gld_internal_period_num(p_company_id, v.period_name) internal_period_num INTO v_period_name, v_internal_period_num FROM (SELECT gld_common_pkg.get_gld_period_name(p_company_id, trunc(c_record.transaction_date)) period_name FROM dual) v; insert_csh_transaction(p_transaction_id => v_transaction_id, p_transaction_num => v_transaction_num, p_transaction_category => 'BUSINESS', p_transaction_type => 'RECEIPT', p_transaction_date => trunc(c_record.transaction_date), p_penalty_calc_date => trunc(c_record.transaction_date), p_bank_slip_num => NULL, p_company_id => p_company_id, p_internal_period_num => v_internal_period_num, p_period_name => v_period_name, p_payment_method_id => 1, p_distribution_set_id => NULL, p_cashflow_amount => c_record.transaction_amount, p_currency_code => v_currency_code, p_transaction_amount => c_record.transaction_amount, p_exchange_rate_type => NULL, p_exchange_rate_quotation => NULL, p_exchange_rate => 1, p_bank_account_id => v_bank_account_id, p_bp_category => v_bp_category, p_bp_id => v_bp_id, p_bp_bank_account_id => v_bp_bank_account_id, p_bp_bank_account_num => c_record.bp_bank_account_num, p_description => c_record.description, p_handling_charge => 0, p_posted_flag => 'Y', p_reversed_flag => 'N', p_reversed_date => NULL, p_returned_flag => 'NOT', p_returned_amount => NULL, p_write_off_flag => 'NOT', p_write_off_amount => NULL, p_full_write_off_date => NULL, p_twin_csh_trx_id => NULL, p_return_from_csh_trx_id => NULL, p_reversed_csh_trx_id => NULL, p_source_csh_trx_type => NULL, p_source_csh_trx_id => NULL, p_source_doc_category => NULL, p_source_doc_type => NULL, p_source_doc_id => NULL, p_source_doc_line_id => NULL, p_create_je_mothed => NULL, p_create_je_flag => 'N', p_gld_interface_flag => 'N', p_user_id => p_user_id, p_ref_contract_id => NULL); UPDATE csh_transaction ct SET ct.receipt_type = 'IMPORT' WHERE ct.transaction_id = v_transaction_id; --begin to write off SAVEPOINT start_auto_write_off; BEGIN SELECT COUNT(1) INTO v_contract_num FROM con_contract c WHERE c.bp_id_tenant = v_bp_id AND data_class = 'NORMAL' AND c.contract_status NOT IN ('CANCEL', 'AD', 'ET', 'TERMINATE'); -- add by qianming 20150819 ----如果未根据承租人找到合同,或者找到多条合同,则不核销 IF v_contract_num < 1 THEN v_write_off_flag := 'N'; IF v_bp_id IS NOT NULL THEN yonda_auto_write_off_log(p_transaction_id => v_transaction_id, p_bp_id => v_bp_id, p_bank_account_id => v_bank_account_id, p_message => c_record.bp_name || ',一笔收款' || c_record.transaction_amount || '未匹配对应的合同,无法自动核销'); END IF; \*raise e_null_contract_error;*\ ELSIF v_contract_num > 1 THEN v_write_off_flag := 'N'; yonda_auto_write_off_log(p_transaction_id => v_transaction_id, p_bp_id => v_bp_id, p_bank_account_id => v_bank_account_id, p_message => c_record.bp_name || ',一笔收款' || c_record.transaction_amount || '匹配到多个合同,无法自动核销'); \*raise e_too_many_contract_error;*\ ELSE ---如果只找到一条合同,记录合同id,并查看与收款金额匹配的该合同的现金流 SELECT c.contract_id INTO v_contract_id FROM con_contract c WHERE c.bp_id_tenant = v_bp_id AND data_class = 'NORMAL' AND c.contract_status NOT IN ('CANCEL', 'AD', 'ET', 'TERMINATE'); SELECT cc.* INTO v_contract_rec FROM con_contract cc WHERE cc.contract_id = v_contract_id; \*check contract status*\ IF v_contract_rec.contract_status IN ('SIGN', 'PRINTED', 'NEW') THEN yonda_auto_write_off_log(p_transaction_id => v_transaction_id, p_bp_id => v_bp_id, p_contract_id => v_contract_id, p_bank_account_id => v_bank_account_id, p_message => c_record.bp_name || ',一笔收款' || c_record.transaction_amount || '匹配的合同状态不正确,无法自动核销'); ELSE SELECT COUNT(1) INTO v_cashflow_num FROM con_contract_cashflow ccf WHERE ccf.cf_status = 'RELEASE' AND ccf.cf_direction = 'INFLOW' AND ccf.due_amount - nvl(ccf.received_amount, 0) = c_record.transaction_amount AND ccf.contract_id = v_contract_id; IF v_cashflow_num < 1 THEN v_write_off_flag := 'N'; yonda_auto_write_off_log(p_transaction_id => v_transaction_id, p_bp_id => v_bp_id, p_bank_account_id => v_bank_account_id, p_message => c_record.bp_name || ',一笔收款' || c_record.transaction_amount || '匹配的合同没有可核销现金流,无法自动核销'); \*raise e_null_cashflow_error;*\ ELSE \*raise e_null_cashflow_error;*\ ---如果找到多条未核销的现金流,则先核销期数最小的那一条 SELECT MIN(ccf.times) INTO v_min_times FROM con_contract_cashflow ccf WHERE ccf.cf_status = 'RELEASE' AND ccf.cf_direction = 'INFLOW' AND ccf.due_amount - nvl(ccf.received_amount, 0) = c_record.transaction_amount AND ccf.contract_id = v_contract_id; SELECT * INTO v_con_contract_cashflow_rec FROM con_contract_cashflow ccf WHERE ccf.cf_status = 'RELEASE' AND ccf.cf_direction = 'INFLOW' AND ccf.due_amount - nvl(ccf.received_amount, 0) = c_record.transaction_amount AND ccf.times = v_min_times AND ccf.contract_id = v_contract_id; IF NOT v_is_error AND v_write_off_flag = 'Y' THEN BEGIN v_session_id := sys_session_s.nextval; csh_write_off_pkg.insert_csh_write_off_temp(p_session_id => v_session_id, p_write_off_type => 'RECEIPT_CREDIT', p_transaction_category => 'BUSINESS', p_transaction_type => 'RECEIPT', p_write_off_date => trunc(SYSDATE), p_write_off_due_amount => c_record.transaction_amount, p_write_off_principal => v_con_contract_cashflow_rec.principal - nvl(v_con_contract_cashflow_rec.received_principal, 0), p_write_off_interest => v_con_contract_cashflow_rec.interest - nvl(v_con_contract_cashflow_rec.received_interest, 0), p_company_id => p_company_id, p_document_category => 'CONTRACT', p_document_id => v_contract_id, p_document_line_id => v_con_contract_cashflow_rec.cashflow_id, p_description => '收款导入自动核销生成', p_user_id => p_user_id); csh_write_off_pkg.main_write_off(p_session_id => v_session_id, p_transaction_id => v_transaction_id, p_user_id => p_user_id); v_save_message := '提交成功,并已自动核销!'; yonda_auto_write_off_log(p_transaction_id => v_transaction_id, p_bp_id => v_bp_id, p_contract_id => v_contract_id, p_bank_account_id => v_bank_account_id, p_message => c_record.bp_name || ',一笔收款' || c_record.transaction_amount || v_save_message); 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_TRANSACTION', p_procedure_function_name => 'SAVE_DATA'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); ROLLBACK TO start_auto_write_off; v_is_error := TRUE; v_save_message := '未能自动核销,请检查数据!'; yonda_auto_write_off_log(p_transaction_id => v_transaction_id, p_bp_id => v_bp_id, p_contract_id => v_contract_id, p_bank_account_id => v_bank_account_id, p_message => c_record.bp_name || ',一笔收款' || c_record.transaction_amount || v_save_message); END; END IF; END IF; END IF; END IF; EXCEPTION WHEN e_null_contract_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_IMPORT_CONTRACT_NOT_FOUND', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'save_data'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); ROLLBACK TO start_auto_write_off; v_is_error := TRUE; v_save_message := '未找到相应合同,无法自动核销!'; WHEN e_too_many_contract_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_IMPORT_TOO_MANY_CONTRACT', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'save_data'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); ROLLBACK TO start_auto_write_off; v_is_error := TRUE; v_save_message := '找到多条合同,无法自动核销!'; WHEN e_null_cashflow_error THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'CSH_TRANSACTION_PKG.CSH510_IMPORT_WRITE_OFF_AMOUNT_NOT_MATCH', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'save_data'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); ROLLBACK TO start_auto_write_off; v_is_error := TRUE; v_save_message := '未找到对应现金流,无法自动核销,请检查金额!'; END; END LOOP; --删除临时表数据 delete_interface(p_batch_id, p_user_id); delete_temp(p_batch_id); END;*/ --收款导入并核销 PROCEDURE save_data(p_batch_id NUMBER, p_user_id NUMBER, p_company_id NUMBER, v_save_message OUT VARCHAR2) AS BEGIN /*sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该功能正在维护中,请稍后尝试!', p_created_by => p_user_id, p_package_name => 'CSH_TRANSACTION', p_procedure_function_name => 'DELETE_INTERFACE'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id);*/ -- transaction & write off csh_batch_transaction_pkg.excel_auto_tran_and_write_off(p_batch_id => p_batch_id, p_user_id => p_user_id, p_company_id => p_company_id); --删除临时表数据 delete_interface(p_batch_id, p_user_id); delete_temp(p_batch_id); END save_data; --收款导入新建现金事务 PROCEDURE save_transaction_data(p_batch_id NUMBER, p_user_id NUMBER, p_company_id NUMBER, v_save_message OUT VARCHAR2) AS BEGIN /*sys_raise_app_error_pkg.raise_sys_others_error(p_message => '该功能正在维护中,请稍后尝试!', p_created_by => p_user_id, p_package_name => 'CSH_TRANSACTION', p_procedure_function_name => 'DELETE_INTERFACE'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id);*/ -- transaction csh_batch_transaction_pkg.excel_auto_transaction(p_batch_id => p_batch_id, p_user_id => p_user_id, p_company_id => p_company_id); --删除临时表数据 delete_interface(p_batch_id, p_user_id); delete_temp(p_batch_id); END save_transaction_data; PROCEDURE risk_vat_insert(p_vat NUMBER, p_company_id NUMBER, p_user_id NUMBER) IS r_transaction csh_transaction%ROWTYPE; r_cwo csh_write_off%ROWTYPE; BEGIN SELECT c.* INTO r_transaction FROM csh_transaction c WHERE c.transaction_type = 'RISK' AND c.company_id = p_company_id FOR UPDATE; r_cwo.write_off_id := csh_write_off_s.nextval; r_cwo.write_off_type := 'RISK_CREDIT'; r_cwo.write_off_date := trunc(SYSDATE); r_cwo.internal_period_num := to_char(SYSDATE, 'YYYYMM'); r_cwo.period_name := to_char(SYSDATE, 'YYYY-MM'); r_cwo.csh_transaction_id := r_transaction.transaction_id; r_cwo.csh_write_off_amount := p_vat; r_cwo.reversed_flag := 'N'; r_cwo.write_off_due_amount := p_vat; r_cwo.create_je_flag := 'Y'; r_cwo.gld_interface_flag := 'N'; r_cwo.created_by := p_user_id; r_cwo.creation_date := SYSDATE; r_cwo.last_updated_by := p_user_id; r_cwo.last_update_date := SYSDATE; r_cwo.pay_dec := '支付风险金税金'; INSERT INTO csh_write_off VALUES r_cwo; UPDATE csh_transaction o SET o.write_off_amount = nvl(o.write_off_amount, 0) + p_vat WHERE o.transaction_id = r_transaction.transaction_id; END risk_vat_insert; --插入手工收款新增数据,检验流水号的唯一性 PROCEDURE save_csh_transaction_data(p_transaction_date IN VARCHAR2, p_cashflow_amount IN NUMBER, p_bank_slip_num IN VARCHAR2, p_paid_byother_flag IN VARCHAR2, p_bp_id IN NUMBER, p_collection_classes IN NUMBER, p_description IN VARCHAR2, p_bp_bank_account_id IN NUMBER, p_bp_bank_name IN VARCHAR2, p_bp_bank_account_num IN VARCHAR2, p_bank_account_id IN VARCHAR2, p_user_id IN VARCHAR2) IS v_exit_flag NUMBER := 0; e_slip_num_already_exist EXCEPTION; BEGIN --检查流水号的唯一性 SELECT COUNT(1) INTO v_exit_flag FROM csh_transaction WHERE bank_slip_num = p_bank_slip_num; IF v_exit_flag != 0 THEN RAISE e_slip_num_already_exist; END IF; --插入数据 INSERT INTO csh_transaction (transaction_id, transaction_date, cashflow_amount, bank_slip_num, paid_byother_flag, bp_id, collection_classes, description, bp_bank_account_id, bp_bank_name, bp_bank_account_num, bank_account_id) VALUES (csh_transaction_s.nextval, to_date(p_transaction_date, 'yyyy-mm-dd'), p_cashflow_amount, p_bank_slip_num, p_paid_byother_flag, p_bp_id, p_collection_classes, p_description, p_bp_bank_account_id, p_bp_bank_name, p_bp_bank_account_num, p_bank_account_id); EXCEPTION WHEN e_slip_num_already_exist THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'E_SLIP_NUM_ALREADY_EXIST', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'save_csh_transaction_data'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --add by shen --excel导入收款新增csh_transaction表 PROCEDURE csh_transaction_import_check(p_header_id NUMBER, p_user_id NUMBER) IS cur fnd_interface_lines%ROWTYPE; cur_excel_check fnd_interface_lines%ROWTYPE; v_error_message VARCHAR2(2000); v_excel_error_message VARCHAR2(2000); v_transaction_id NUMBER; v_transaction_amount csh_transaction.transaction_amount%TYPE; v_bank_slip_num_flag NUMBER := 0; v_bp_flag NUMBER := -1; BEGIN --将数字,文字等格式去除 UPDATE fnd_interface_lines ln SET ln.attribute_1 = TRIM(ln.attribute_1), ln.attribute_2 = TRIM(ln.attribute_2), ln.attribute_3 = TRIM(ln.attribute_3), ln.attribute_5 = TRIM(ln.attribute_5), ln.attribute_8 = TRIM(ln.attribute_8), ln.attribute_9 = TRIM(ln.attribute_9), ln.attribute_10 = TRIM(ln.attribute_10), ln.attribute_14 = TRIM(ln.attribute_14), ln.attribute_23 = TRIM(ln.attribute_23), ln.attribute_24 = TRIM(ln.attribute_24) WHERE ln.header_id = p_header_id AND ln.line_number > 0; FOR cur IN (SELECT * FROM fnd_interface_lines ln WHERE ln.header_id = p_header_id AND ln.line_number > 0 ORDER BY ln.line_number) LOOP v_error_message := NULL; --必输字段校验 IF (cur.attribute_1 IS NULL) THEN v_error_message := v_error_message || ' 账号信息不能为空! '; END IF; IF (cur.attribute_8 IS NULL) THEN v_error_message := v_error_message || ' 对方账户信息不能为空! '; END IF; BEGIN --判断是否是数字 SELECT to_number(cur.attribute_14) INTO v_transaction_amount FROM dual; IF v_transaction_amount < 0 THEN v_error_message := v_error_message || '收款金额不能小于0! '; END IF; --判断流水号是否已经存在 SELECT COUNT(1) INTO v_bank_slip_num_flag FROM csh_transaction WHERE bank_slip_num = cur.attribute_5; IF v_bank_slip_num_flag != 0 THEN v_error_message := v_error_message || '该流水号已经存在'; END IF; --判断是否有商业伙伴 --select count(1) into v_bp_flag -- from hls_bp_master ---- where bank_account_num=cur.attribute_8; --if v_bp_flag !=1 then -- v_error_message := v_error_message || '没有该账户对应的商业伙伴'; --end if; EXCEPTION WHEN OTHERS THEN v_error_message := v_error_message || '数据出错,请重新导入'; END; IF v_error_message IS NOT NULL THEN UPDATE fnd_interface_lines ln SET ln.error_message = v_error_message, ln.status = 'ERROR' WHERE ln.line_id = cur.line_id; END IF; END LOOP; --excel中有重复的数据 for cur_excel_check in ( select * from fnd_interface_lines ln where ln.header_id = p_header_id and ln.attribute_5 in (SELECT ln.attribute_5 FROM fnd_interface_lines ln WHERE ln.header_id = p_header_id AND ln.line_number > 0 group by ln.attribute_5 having count(1) > 1) ) loop v_excel_error_message:=null; v_excel_error_message:=cur_excel_check.error_message||' 该条数据的流水号重复'; --更新数据 UPDATE fnd_interface_lines ln SET ln.error_message = v_excel_error_message, ln.status = 'ERROR' WHERE ln.line_id = cur_excel_check.line_id; end loop; END; --add by shen PROCEDURE update_confirmed_flag(p_transaction_id IN NUMBER, p_wanted_confirmed_flag IN VARCHAR2, p_write_off_flag IN VARCHAR2 DEFAULT NULL, p_user_id IN VARCHAR2) IS v_csh_transaction_rec csh_transaction%ROWTYPE; v_csh_transaction_plan_rec csh_transaction_plan%ROWTYPE; BEGIN --锁表 lock_csh_transaction(p_transaction_id => p_transaction_id, p_user_id => p_user_id, p_csh_transaction_rec => v_csh_transaction_rec); UPDATE csh_transaction SET confirmed_flag = p_wanted_confirmed_flag WHERE transaction_id = p_transaction_id; --判断核销标志,并判断是否更新 IF p_write_off_flag IS NOT NULL THEN UPDATE csh_transaction SET write_off_flag = p_write_off_flag WHERE transaction_id = p_transaction_id; END IF; --确认之后生成保证金、预收款的预选项 IF p_wanted_confirmed_flag = 'APPROVED' THEN v_csh_transaction_plan_rec.plan_id := csh_transaction_plan_s.nextval; v_csh_transaction_plan_rec.transaction_id := p_transaction_id; v_csh_transaction_plan_rec.write_off_type := 'ADVANCE_RECEIPT'; v_csh_transaction_plan_rec.bp_id := v_csh_transaction_rec.bp_id; v_csh_transaction_plan_rec.write_off_date := v_csh_transaction_rec.transaction_date; v_csh_transaction_plan_rec.created_by := v_csh_transaction_rec.created_by; v_csh_transaction_plan_rec.creation_date := SYSDATE; v_csh_transaction_plan_rec.company_id := v_csh_transaction_rec.company_id; --插入预收款预选项 INSERT INTO csh_transaction_plan VALUES v_csh_transaction_plan_rec; --插入保证金预选项 IF v_csh_transaction_rec.bp_category = 'AGENT' THEN v_csh_transaction_plan_rec.write_off_type := 'DEPOSIT'; INSERT INTO csh_transaction_plan VALUES v_csh_transaction_plan_rec; END IF; END IF; END; PROCEDURE create_deposit_advance_plan(p_transaction_id NUMBER, p_user_id NUMBER) IS v_csh_transaction_rec csh_transaction%ROWTYPE; v_csh_transaction_plan_rec csh_transaction_plan%ROWTYPE; BEGIN SELECT * INTO v_csh_transaction_rec FROM csh_transaction WHERE transaction_id = p_transaction_id; v_csh_transaction_plan_rec.plan_id := csh_transaction_plan_s.nextval; v_csh_transaction_plan_rec.transaction_id := p_transaction_id; v_csh_transaction_plan_rec.write_off_type := 'RECEIPT_ADVANCE_RECEIPT'; v_csh_transaction_plan_rec.bp_id := v_csh_transaction_rec.bp_id; v_csh_transaction_plan_rec.write_off_date := v_csh_transaction_rec.transaction_date; v_csh_transaction_plan_rec.created_by := v_csh_transaction_rec.created_by; v_csh_transaction_plan_rec.creation_date := SYSDATE; v_csh_transaction_plan_rec.company_id := v_csh_transaction_rec.company_id; --插入预收款预选项 INSERT INTO csh_transaction_plan VALUES v_csh_transaction_plan_rec; --插入保证金预选项 IF v_csh_transaction_rec.bp_category = 'AGENT' THEN v_csh_transaction_plan_rec.plan_id := csh_transaction_plan_s.nextval; v_csh_transaction_plan_rec.write_off_type := 'RECEIPT_DEPOSIT_POOL'; INSERT INTO csh_transaction_plan VALUES v_csh_transaction_plan_rec; 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_transaction_pkg', p_procedure_function_name => 'return_csh_transaction'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; --根据银行卡号获得银行账号的id PROCEDURE get_bank_account_id(p_bank_account_num IN VARCHAR2, p_bp_bank_account_num IN VARCHAR2, p_bank_account_id OUT NUMBER, p_bp_category OUT VARCHAR2, p_bp_id OUT NUMBER, p_csh_bp_name OUT VARCHAR2, p_user_id IN VARCHAR2) IS v_bank_account_id NUMBER; v_bp_category hls_bp_master.bp_category%TYPE; v_bp_id hls_bp_master.bp_id%TYPE; v_csh_bp_name hls_bp_master.bp_name%TYPE; -- e_no_this_account Exception; BEGIN SELECT nvl(bank_account_id, -1) INTO v_bank_account_id FROM csh_bank_account WHERE bank_account_num = p_bank_account_num; --我方银行账号的id p_bank_account_id := v_bank_account_id; --根据对方的银行账号获得商业伙伴的信息 SELECT bp_category, bp_id, bp_name INTO v_bp_category, v_bp_id, v_csh_bp_name FROM hls_bp_master WHERE bank_account_num = p_bp_bank_account_num; p_bp_category := v_bp_category; p_bp_id := v_bp_id; p_csh_bp_name := v_csh_bp_name; --当没有对应的bp 时,会发生异常 EXCEPTION WHEN OTHERS THEN -- p_bp_category :=null; p_bp_id := -1; -- p_csh_bp_name :=null; END; --根据银行卡号获得银行账号的id,根据bp_name 匹配数据 PROCEDURE get_bank_account_info(p_bank_account_num IN VARCHAR2, p_bp_bank_account_name IN VARCHAR2, p_bank_account_id OUT NUMBER, p_bp_category OUT VARCHAR2, p_bp_id OUT NUMBER, p_csh_bp_name OUT VARCHAR2, p_user_id IN VARCHAR2) IS v_bank_account_id NUMBER; v_bp_category hls_bp_master.bp_category%TYPE; v_bp_id hls_bp_master.bp_id%TYPE; v_csh_bp_name hls_bp_master.bp_name%TYPE; -- e_no_this_account Exception; BEGIN SELECT nvl(bank_account_id, -1) INTO v_bank_account_id FROM csh_bank_account WHERE bank_account_num = p_bank_account_num; --我方银行账号的id p_bank_account_id := v_bank_account_id; --根据对方的银行账号获得商业伙伴的信息 SELECT bp_category, bp_id, bp_name INTO v_bp_category, v_bp_id, v_csh_bp_name FROM hls_bp_master WHERE bp_name = p_bp_bank_account_name; p_bp_category := v_bp_category; p_bp_id := v_bp_id; p_csh_bp_name := v_csh_bp_name; --当没有对应的bp 时,会发生异常 EXCEPTION WHEN OTHERS THEN -- p_bp_category :=null; p_bp_id := -1; -- p_csh_bp_name :=null; END; --excel 导入数据 PROCEDURE save_receipt_import(p_transaction_id OUT csh_transaction.transaction_id%TYPE, p_transaction_num csh_transaction.transaction_num%TYPE, p_transaction_category csh_transaction.transaction_category%TYPE, p_transaction_type csh_transaction.transaction_type%TYPE, p_transaction_date csh_transaction.transaction_date%TYPE, p_penalty_calc_date csh_transaction.penalty_calc_date%TYPE, p_bank_slip_num csh_transaction.bank_slip_num%TYPE, p_company_id csh_transaction.company_id%TYPE, p_internal_period_num csh_transaction.internal_period_num%TYPE, p_period_name csh_transaction.period_name%TYPE, p_payment_method_id csh_transaction.payment_method_id%TYPE, p_distribution_set_id csh_transaction.distribution_set_id%TYPE, p_cashflow_amount csh_transaction.cashflow_amount%TYPE, p_currency_code csh_transaction.currency_code%TYPE, p_transaction_amount csh_transaction.transaction_amount%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_bp_category csh_transaction.bp_category%TYPE, p_bp_id csh_transaction.bp_id%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_bp_bank_account_name csh_transaction.bp_bank_account_name%TYPE DEFAULT NULL, --add by Spener 3893 20160722 p_description csh_transaction.description%TYPE, p_handling_charge csh_transaction.handling_charge%TYPE, p_posted_flag csh_transaction.posted_flag%TYPE, p_reversed_flag csh_transaction.reversed_flag%TYPE, p_reversed_date csh_transaction.reversed_date%TYPE, p_returned_flag csh_transaction.returned_flag%TYPE, p_returned_amount csh_transaction.returned_amount%TYPE, p_write_off_flag csh_transaction.write_off_flag%TYPE, p_write_off_amount csh_transaction.write_off_amount%TYPE, p_full_write_off_date csh_transaction.full_write_off_date%TYPE, p_twin_csh_trx_id csh_transaction.twin_csh_trx_id%TYPE, p_return_from_csh_trx_id csh_transaction.return_from_csh_trx_id%TYPE, p_reversed_csh_trx_id csh_transaction.reversed_csh_trx_id%TYPE, p_source_csh_trx_type csh_transaction.source_csh_trx_type%TYPE, p_source_csh_trx_id csh_transaction.source_csh_trx_id%TYPE, p_source_doc_category csh_transaction.source_doc_category%TYPE, p_source_doc_type csh_transaction.source_doc_type%TYPE, p_source_doc_id csh_transaction.source_doc_id%TYPE, p_source_doc_line_id csh_transaction.source_doc_line_id%TYPE, p_create_je_mothed csh_transaction.create_je_mothed%TYPE, p_create_je_flag csh_transaction.create_je_flag%TYPE, p_gld_interface_flag csh_transaction.gld_interface_flag%TYPE, p_user_id csh_transaction.created_by%TYPE, p_ref_contract_id csh_transaction.ref_contract_id%TYPE DEFAULT NULL, p_receipt_type csh_transaction.receipt_type%TYPE DEFAULT NULL, p_csh_bp_name csh_transaction.csh_bp_name%TYPE DEFAULT NULL, p_ref_n01 NUMBER DEFAULT NULL, p_bp_bank_name VARCHAR2 DEFAULT NULL, p_bp_bank_branch_name VARCHAR2 DEFAULT NULL, p_collection_classes VARCHAR2 DEFAULT NULL, p_opposite_band_na VARCHAR2 DEFAULT NULL, p_banka VARCHAR2 DEFAULT NULL, p_purpose VARCHAR2 DEFAULT NULL, p_gsber VARCHAR2 DEFAULT NULL, p_sus_belnr VARCHAR2 DEFAULT NULL, p_assigns_contract_id NUMBER DEFAULT NULL, p_conds_account_num VARCHAR2 DEFAULT NULL, p_conds_account_name VARCHAR2 DEFAULT NULL, p_bp_virtual_bank_account VARCHAR2 DEFAULT NULL, p_return_id NUMBER DEFAULT NULL, p_zuonr VARCHAR2 DEFAULT NULL, --add by sf p_paid_byother_flag IN VARCHAR2, p_ref_v05 IN VARCHAR2, --该行信息是否正确 p_status IN VARCHAR2, p_bank_account_num IN VARCHAR2 --end; ) IS v_bank_account_id NUMBER; v_bp_category hls_bp_master.bp_category%TYPE; v_bp_id hls_bp_master.bp_id%TYPE; v_csh_bp_name hls_bp_master.bp_name%TYPE; v_real_bp_id hls_bp_master.bp_id%TYPE; v_real_bp_name hls_bp_master.bp_name%TYPE; v_real_bp_category hls_bp_master.bp_category%TYPE; v_real_paid_byother_flag csh_transaction.paid_byother_flag%TYPE; --周期时间 v_internal_period_num csh_transaction.internal_period_num%TYPE; v_period_name csh_transaction.period_name%TYPE; -- v_date varchar2(100); v_transcation_id csh_transaction.transaction_id%TYPE; BEGIN --判断该条记录是否有错 IF p_status IS NOT NULL THEN RETURN; END IF; -- i. 若能找到唯一值且该商业伙伴类型为承租人,则代付字段为“否”、商业伙伴字段为匹配到的商业伙伴; --ii. 若能找到唯一值且该商业伙伴类型为代理店,则代付字段为“是”、商业伙伴字段为匹配到的商业伙伴; --iii. 若无法找到匹配项或匹配到多项,则代付字段为“是”、商业伙伴字段为空; --获得商业伙伴的信息 csh_transaction_pkg.get_bank_account_info(p_bank_account_num => p_bank_account_num, p_bp_bank_account_name => p_bp_bank_account_name, p_bank_account_id => v_bank_account_id, p_bp_category => v_bp_category, p_bp_id => v_bp_id, p_csh_bp_name => v_csh_bp_name, p_user_id => p_user_id); --修改为bp_name 进行匹配 --第三种情况 IF v_bp_id = -1 THEN BEGIN v_real_paid_byother_flag := 'T'; v_real_bp_id := NULL; v_real_bp_name := NULL; v_real_bp_category := NULL; END; ELSIF v_bp_id != -1 AND v_bp_category = 'TENANT' THEN BEGIN v_real_paid_byother_flag := 'F'; v_real_bp_id := v_bp_id; v_real_bp_name := v_csh_bp_name; v_real_bp_category := v_bp_category; END; ELSIF v_bp_id != -1 AND v_bp_category = 'AGENT' THEN BEGIN v_real_paid_byother_flag := 'T'; v_real_bp_id := v_bp_id; v_real_bp_name := v_csh_bp_name; v_real_bp_category := v_bp_category; END; END IF; --处理周期字段 -- select to_char(p_transaction_date,'yyyy-mm-dd') into v_date from dual; SELECT v.period_name, gld_common_pkg.get_gld_internal_period_num(p_company_id, v.period_name) INTO v_period_name, v_internal_period_num --gld_common_pkg.get_gld_internal_period_num(p_company_id,v.period_name) period_num, --gld_common_pkg.get_period_year(p_company_id,v.period_name) period_year, --gld_common_pkg.get_period_set_code(p_company_id) period_set_code FROM (SELECT gld_common_pkg.get_gld_period_name(p_company_id, to_date(to_char(p_transaction_date, 'yyyy-mm-dd'), 'yyyy-mm-dd')) period_name FROM dual) v; --调用插入函数 csh_transaction_pkg.insert_csh_transaction(p_transaction_id => v_transcation_id, p_transaction_num => p_transaction_num, p_transaction_category => p_transaction_category, p_transaction_type => p_transaction_type, p_transaction_date => p_transaction_date, p_penalty_calc_date => p_penalty_calc_date, p_bank_slip_num => p_bank_slip_num, p_company_id => p_company_id, p_internal_period_num => v_internal_period_num, p_period_name => v_period_name, p_payment_method_id => p_payment_method_id, p_distribution_set_id => p_distribution_set_id, p_cashflow_amount => p_cashflow_amount, p_currency_code => p_currency_code, p_transaction_amount => p_transaction_amount, 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 => v_bank_account_id, p_bp_category => v_real_bp_category, p_bp_id => v_real_bp_id, p_bp_bank_account_id => p_bp_bank_account_id, p_bp_bank_account_num => p_bp_bank_account_num, p_description => p_description, p_handling_charge => p_handling_charge, p_posted_flag => p_posted_flag, p_reversed_flag => p_reversed_flag, p_reversed_date => p_reversed_date, p_returned_flag => p_returned_flag, p_returned_amount => p_returned_amount, p_write_off_flag => p_write_off_flag, p_write_off_amount => p_write_off_amount, p_full_write_off_date => p_full_write_off_date, p_twin_csh_trx_id => p_twin_csh_trx_id, p_return_from_csh_trx_id => p_return_from_csh_trx_id, p_reversed_csh_trx_id => p_reversed_csh_trx_id, p_source_csh_trx_type => p_source_csh_trx_type, p_source_csh_trx_id => p_source_csh_trx_id, p_source_doc_category => p_source_doc_category, p_source_doc_type => p_source_doc_type, p_source_doc_id => p_source_doc_id, p_source_doc_line_id => p_source_doc_line_id, p_create_je_mothed => p_create_je_mothed, p_create_je_flag => p_create_je_flag, p_gld_interface_flag => p_gld_interface_flag, p_user_id => p_user_id, p_ref_contract_id => p_ref_contract_id, p_receipt_type => p_receipt_type, p_csh_bp_name => v_real_bp_name, p_ref_n01 => p_ref_n01, p_bp_bank_name => p_bp_bank_name, p_bp_bank_branch_name => p_bp_bank_branch_name, p_collection_classes => p_collection_classes, p_bp_bank_account_name => p_bp_bank_account_name, p_opposite_band_na => p_opposite_band_na, p_banka => p_banka, p_purpose => p_purpose, p_gsber => p_gsber, p_sus_belnr => p_sus_belnr, p_bp_virtual_bank_account => p_bp_virtual_bank_account, p_zuonr => p_bank_slip_num, p_paid_byother_flag => v_real_paid_byother_flag, p_ref_v05 => p_ref_v05, p_status => p_status); END; --选择下一个确认人 PROCEDURE update_confirmer_by_condition(p_transaction_id IN NUMBER, p_user_id IN VARCHAR2) IS v_receipt_type csh_transaction.receipt_type%TYPE; v_bp_category csh_transaction.bp_category%TYPE; v_paid_byother_flag csh_transaction.paid_byother_flag%TYPE; v_confirmed_flag csh_transaction.confirmed_flag%TYPE; BEGIN --查找出相关信息 SELECT c.receipt_type, c.bp_category, c.paid_byother_flag INTO v_receipt_type, v_bp_category, v_paid_byother_flag FROM csh_transaction c WHERE c.transaction_id = p_transaction_id; --如果是手动收款 IF v_receipt_type = 'MANUAL' THEN BEGIN IF v_bp_category = 'AGENT' OR v_paid_byother_flag = 'T' THEN v_confirmed_flag := 'ACCAUDITING'; ELSIF v_bp_category = 'TENANT' THEN v_confirmed_flag := 'DEBTAUDITING'; END IF; END; --excel导入 ELSIF v_receipt_type = 'IMPORT' THEN BEGIN SELECT bp_category INTO v_bp_category FROM hls_bp_master WHERE bp_name = (SELECT c.bp_bank_account_name FROM csh_transaction c WHERE c.transaction_id = p_transaction_id); IF v_bp_category = 'TENANT' THEN v_confirmed_flag := 'DEBTAUDITING'; ELSIF v_bp_category = 'AGENT' THEN v_confirmed_flag := 'ACCAUDITING'; ELSIF v_bp_category IS NULL THEN v_confirmed_flag := 'ACCAUDITING'; END IF; END; END IF; UPDATE csh_transaction c SET c.confirmed_flag = v_confirmed_flag WHERE c.transaction_id = p_transaction_id; EXCEPTION WHEN OTHERS THEN BEGIN v_confirmed_flag := 'ACCAUDITING'; UPDATE csh_transaction c SET c.confirmed_flag = v_confirmed_flag WHERE c.transaction_id = p_transaction_id; END; END; --修改单据的状态(作废,驳回,以及确认单据) PROCEDURE update_muti_confirmed_flag(p_transaction_id IN NUMBER, p_wanted_confirmed_flag IN VARCHAR2, p_wanted_written_off_flag IN VARCHAR2 DEFAULT NULL, p_confirmed_by IN VARCHAR2 DEFAULT NULL, p_user_id IN VARCHAR2) IS v_created_by csh_transaction.created_by%TYPE; E_SAME_RECODER EXCEPTION; BEGIN --更新单据状态 UPDATE csh_transaction c SET c.confirmed_flag = p_wanted_confirmed_flag WHERE c.transaction_id = p_transaction_id; --如果是最终的确认单据,改变核销状态并加上确认人和确认时间 IF p_wanted_written_off_flag IS NOT NULL THEN UPDATE csh_transaction c SET c.write_off_flag = p_wanted_written_off_flag WHERE c.transaction_id = p_transaction_id; --加上确认人和确认时间,需要另一个债权内勤进行确认 --查找出制单人和当前用户进行比较 IF p_confirmed_by = '00217' THEN BEGIN SELECT c.created_by INTO v_created_by FROM csh_transaction c WHERE c.transaction_id = p_transaction_id; IF p_user_id = v_created_by THEN RAISE E_SAME_RECODER; END IF; --如果不是债权内勤,则进行确认 UPDATE csh_transaction c SET c.debt_confirmed_by = p_user_id, c.debt_confirmed_date = SYSDATE WHERE c.transaction_id = p_transaction_id; END; --会计担当直接确认 ELSIF p_confirmed_by = '00321' THEN UPDATE csh_transaction c SET c.acc_confirmed_by = p_user_id, c.acc_confirmed_date = SYSDATE WHERE c.transaction_id = p_transaction_id; END IF; IF p_wanted_written_off_flag = 'NOT' AND p_wanted_confirmed_flag = 'APPROVED' THEN UPDATE csh_transaction SET posted_flag = 'Y' WHERE transaction_id = p_transaction_id; create_deposit_advance_plan(p_transaction_id => p_transaction_id, p_user_id => p_user_id); END IF; END IF; EXCEPTION WHEN E_SAME_RECODER THEN sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'E_SAME_RECODER', p_created_by => p_user_id, p_package_name => 'csh_transaction_pkg', p_procedure_function_name => 'update_muti_confirmed_flag'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); END; END csh_transaction_pkg; /