create or replace package body ACR_INVOICE_CLAIM_PKG is g_status_unclaimed Constant Varchar2(30) := 'UNCLAIMED'; g_status_claiming Constant Varchar2(30) := 'CLAIMING'; g_status_confirmed Constant Varchar2(30) := 'CONFIRMED'; g_status_return Constant Varchar2(30) := 'RETURN'; Function lock_acr_invoice(p_invoice_hd_id Number) Return acr_invoice_hd%Rowtype Is r_acr_invoice_hd_rec acr_invoice_hd%Rowtype; Begin Select * Into r_acr_invoice_hd_rec From acr_invoice_hd Where invoice_hd_id = p_invoice_hd_id For Update Nowait; Return r_acr_invoice_hd_rec; End; Procedure apply_invoice(p_invoice_hd_id Number, p_user_id Number) Is r_acr_invoice_hd_rec acr_invoice_hd%Rowtype; e_invoice_claim_status_err Exception; begin --锁表 r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id); --状态检查 If r_acr_invoice_hd_rec.claim_status <> g_status_return and r_acr_invoice_hd_rec.claim_status <>g_status_unclaimed Then Raise e_invoice_claim_status_err; End If; --更新状态 Update acr_invoice_hd hd Set claim_status = g_status_claiming, hd.last_update_date = sysdate, hd.last_updated_by = p_user_id Where invoice_hd_id = p_invoice_hd_id; Exception When e_invoice_claim_status_err Then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR516_INVOICE_CLAIM_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'ACR_INVOICE_CLAIM_PKG', p_procedure_function_name => 'apply_invoice'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; Procedure apply_invoice_confirm(p_invoice_hd_id Number, p_user_id Number) Is r_acr_invoice_hd_rec acr_invoice_hd%Rowtype; e_invoice_claim_status_err Exception; begin --锁表 r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id); --状态检查 If r_acr_invoice_hd_rec.claim_status <> g_status_claiming Then Raise e_invoice_claim_status_err; End If; --更新状态 Update acr_invoice_hd hd Set claim_status = g_status_confirmed, hd.last_update_date = sysdate, hd.last_updated_by = p_user_id Where invoice_hd_id = p_invoice_hd_id; Exception When e_invoice_claim_status_err Then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR516_INVOICE_CLAIM_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'ACR_INVOICE_CLAIM_PKG', p_procedure_function_name => 'apply_invoice_confirm'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; Procedure apply_invoice_return(p_invoice_hd_id Number, p_user_id Number) Is r_acr_invoice_hd_rec acr_invoice_hd%Rowtype; e_invoice_claim_status_err Exception; begin --锁表 r_acr_invoice_hd_rec := lock_acr_invoice(p_invoice_hd_id => p_invoice_hd_id); --状态检查 If r_acr_invoice_hd_rec.claim_status <> g_status_claiming Then Raise e_invoice_claim_status_err; End If; --更新状态 Update acr_invoice_hd hd Set claim_status = g_status_return, hd.last_update_date = sysdate, hd.last_updated_by = p_user_id Where invoice_hd_id = p_invoice_hd_id; Exception When e_invoice_claim_status_err Then sys_raise_app_error_pkg.raise_user_define_error(p_message_code => 'ACR516_INVOICE_CLAIM_STATUS_ERROR', p_created_by => p_user_id, p_package_name => 'ACR_INVOICE_CLAIM_PKG', p_procedure_function_name => 'apply_invoice_return'); raise_application_error(sys_raise_app_error_pkg.c_error_number, sys_raise_app_error_pkg.g_err_line_id); end; end ACR_INVOICE_CLAIM_PKG;