1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
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;