|
1
|
ALTER PROCEDURE [dbo].[TR_REQ_PAY_DETAIL]
|
|
2
|
@p_REQ_PAY_ID VARCHAR(15) = NULL
|
|
3
|
AS
|
|
4
|
BEGIN
|
|
5
|
|
|
6
|
DECLARE @TABLE_RETURN_01 TABLE ([NAME] NVARCHAR(500))
|
|
7
|
DECLARE @TABLE_RETURN_01_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500))
|
|
8
|
DECLARE @TABLE_RETURN_02 TABLE ([NAME] NVARCHAR(250))
|
|
9
|
DECLARE @TABLE_RETURN_02_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500))
|
|
10
|
DECLARE @TABLE_RETURN_03 TABLE (IND INT IDENTITY(1,1) NOT NULL, [NAME] NVARCHAR(500), AMT DECIMAL(18,2))
|
|
11
|
DECLARE @TABLE_RETURN_04 TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500), AMT DECIMAL(18,2))
|
|
12
|
DECLARE @TABLE_RETURN_06 TABLE ([NAME] NVARCHAR(500), AMT DECIMAL(18,2))
|
|
13
|
DECLARE @INDEX INT =1
|
|
14
|
DECLARE @INDEX_TT INT =0
|
|
15
|
DECLARE @HINHTHUC_CK NVARCHAR(100)
|
|
16
|
DECLARE @CHUOI NVARCHAR(180)
|
|
17
|
|
|
18
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'))
|
|
19
|
BEGIN
|
|
20
|
SET @INDEX_TT = @INDEX_TT+1
|
|
21
|
--INSERT INTO @TABLE_RETURN
|
|
22
|
--SELECT N'Tiền mặt' AS PHUONG_THUC, ISNULL(SUM (TOTAL_AMT),0) AS T1 FROM TR_REQ_PAY_METHOD
|
|
23
|
--WHERE REQ_PAY_TYPE ='1' AND REQ_PAY_ID = @p_REQ_PAY_ID
|
|
24
|
|
|
25
|
INSERT INTO @TABLE_RETURN_03
|
|
26
|
SELECT N'. Người nhận: '+ ACC_NAME + CHAR(10) + N' CMND số: ' + ACC_NO + char(10) + N' Được cấp bởi: '+ ISSUED_BY + N' ngày cấp: '+ CONVERT(VARCHAR,ISSUED_DT,103) AS ACC_NAME, SUM(TOTAL_AMT) AS T2
|
|
27
|
FROM TR_REQ_PAY_METHOD
|
|
28
|
WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'
|
|
29
|
GROUP BY METHOD_ID, ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT
|
|
30
|
ORDER BY CONVERT(DECIMAL(18,0), RIGHT(METHOD_ID, 10))
|
|
31
|
|
|
32
|
|
|
33
|
-- INSERT BANG TAM THE HIEN SO TIEN
|
|
34
|
INSERT INTO @TABLE_RETURN_04
|
|
35
|
SELECT 'N'+ CHAR(10) + 'D'+char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T2
|
|
36
|
FROM TR_REQ_PAY_METHOD
|
|
37
|
WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'
|
|
38
|
GROUP BY METHOD_ID, ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT
|
|
39
|
ORDER BY CONVERT(DECIMAL(18,0), RIGHT(METHOD_ID, 10))
|
|
40
|
END
|
|
41
|
|
|
42
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE <> '1'))
|
|
43
|
BEGIN
|
|
44
|
SET @INDEX_TT = @INDEX_TT+1
|
|
45
|
--INSERT INTO @TABLE_RETURN
|
|
46
|
--SELECT N'Chuyển khoản' AS PHUONG_THUC,ISNULL(SUM (TOTAL_AMT),0) AS T3 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_TYPE ='0' AND REQ_PAY_ID = @p_REQ_PAY_ID
|
|
47
|
|
|
48
|
INSERT INTO @TABLE_RETURN_03
|
|
49
|
SELECT CASE WHEN TYPE_TRANSFER ='R' AND TYPE_TRANSFER IS NOT NULL AND TYPE_TRANSFER <>'' AND ISNULL(SUB_ISSUED_BY, '') = '' THEN (N'. Trích từ tài khoản: '+ ACC_NAME + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY)
|
|
50
|
WHEN TYPE_TRANSFER ='R' AND TYPE_TRANSFER IS NOT NULL AND TYPE_TRANSFER <>'' AND ISNULL(SUB_ISSUED_BY, '') <> '' THEN (N'. Trích từ tài khoản: '+ ACC_NAME + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY + ' - ' + SUB_ISSUED_BY )
|
|
51
|
WHEN TYPE_TRANSFER <> 'R' AND ISNULL(TYPE_TRANSFER, '') <> '' AND ISNULL(SUB_ISSUED_BY, '') <> '' THEN (N'. Đơn vị thụ hưởng: '+ ACC_NAME + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY + ' - ' + SUB_ISSUED_BY)
|
|
52
|
ELSE (N'. Đơn vị thụ hưởng: '+ ACC_NAME + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY)
|
|
53
|
END AS ACC_NAME,
|
|
54
|
SUM(TOTAL_AMT)
|
|
55
|
AS T4
|
|
56
|
FROM TR_REQ_PAY_METHOD
|
|
57
|
WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE <>'1'
|
|
58
|
GROUP BY METHOD_ID, ACC_NO, ACC_NAME, ISSUED_BY,TYPE_TRANSFER , SUB_ISSUED_BY
|
|
59
|
ORDER BY CONVERT(DECIMAL(18,0), RIGHT(METHOD_ID, 10))
|
|
60
|
|
|
61
|
-- INSERT BANG TAM THE HIEN SO TIEN
|
|
62
|
INSERT INTO @TABLE_RETURN_04
|
|
63
|
SELECT 'N'+ CHAR(10)+ 'S'+ char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T4
|
|
64
|
FROM TR_REQ_PAY_METHOD
|
|
65
|
WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE <>'1'
|
|
66
|
GROUP BY METHOD_ID, ACC_NO, ACC_NAME, ISSUED_BY,TYPE_TRANSFER
|
|
67
|
ORDER BY CONVERT(DECIMAL(18,0), RIGHT(METHOD_ID, 10))
|
|
68
|
END
|
|
69
|
|
|
70
|
-- KIEM TRA NOI DUNG SU DUNG CHI PHI VA NGAN SACH
|
|
71
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_PERIOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_SERVICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
72
|
BEGIN
|
|
73
|
--INSERT INTO @TABLE_RETURN_01 VALUES (CONVERT(VARCHAR(15),@INDEX) +N'. Nội dung tạm ứng: ')
|
|
74
|
INSERT INTO @TABLE_RETURN_01 VALUES( N'A - Nội dung thanh toán: ')
|
|
75
|
--INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_REASON FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
76
|
--INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.TOTAL_AMT FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
77
|
INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REASON FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
78
|
INSERT INTO @TABLE_RETURN_06 SELECT LEFT(A.REASON,150) + LEFT(A.REASON,30), A.AMT_PAY FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
79
|
|
|
80
|
INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_DESC FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
81
|
INSERT INTO @TABLE_RETURN_06 SELECT LEFT(A.REQ_PAY_DESC,150)+ LEFT(A.REQ_PAY_DESC,30), A.AMT_PAY_REAL FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
82
|
|
|
83
|
INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.[SERVICE_NAME] FROM TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
84
|
INSERT INTO @TABLE_RETURN_06 SELECT LEFT(A.[SERVICE_NAME],200)+LEFT(A.[SERVICE_NAME],70), A.TOTAL_AMT FROM TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
85
|
END
|
|
86
|
|
|
87
|
--- HĐ & CHỨNG TỪ ĐÍNH KÈM
|
|
88
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
89
|
BEGIN
|
|
90
|
SET @INDEX = @INDEX+1
|
|
91
|
--INSERT INTO @TABLE_RETURN_01 SELECT CONVERT(VARCHAR(15),@INDEX)+ N'. Chứng từ đính kèm: '
|
|
92
|
INSERT INTO @TABLE_RETURN_02 SELECT N'B - Chứng từ đính kèm: '
|
|
93
|
END
|
|
94
|
|
|
95
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='C'))
|
|
96
|
BEGIN
|
|
97
|
--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin hợp đồng đính kèm: '
|
|
98
|
INSERT INTO @TABLE_RETURN_02_DT SELECT ' '+ N'. Hợp đồng số ' + REF_CODE + ISNULL(N' ngày '+ FORMAT(REF_DT,'dd/MM/yyyy'),'')+ CASE WHEN AMT >0 THEN N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') ELSE '' END AS T1 FROM TR_REQ_PAY_ATTACH
|
|
99
|
WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='C'
|
|
100
|
END
|
|
101
|
|
|
102
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='R'))
|
|
103
|
BEGIN
|
|
104
|
--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: '
|
|
105
|
INSERT INTO @TABLE_RETURN_02_DT SELECT N'. '+ [DESCRIPTION]+ CASE WHEN AMT >0 THEN N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') ELSE '' END AS T1 FROM TR_REQ_PAY_ATTACH
|
|
106
|
WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='R'
|
|
107
|
END
|
|
108
|
|
|
109
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
110
|
BEGIN
|
|
111
|
--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: '
|
|
112
|
INSERT INTO @TABLE_RETURN_02_DT SELECT N'. Số hóa đơn '+ HD.INVOICE_NO +ISNULL(N' ngày '+ FORMAT(HD.INVOICE_DT,'dd/MM/yyyy'),'')+ N', số tiền '+ FORMAT( ISNULL(HD.PRICE+HD.VAT,0),'#,#', 'vi-VN') AS T1
|
|
113
|
FROM
|
|
114
|
(
|
|
115
|
SELECT MAX(A.INVOICE_NO) AS INVOICE_NO, MAX(A.INVOICE_DT) AS INVOICE_DT, SUM(A.PRICE) AS PRICE, SUM(A.VAT) AS VAT
|
|
116
|
FROM
|
|
117
|
TR_REQ_PAY_INVOICE A
|
|
118
|
WHERE A.REQ_PAY_ID = @p_REQ_PAY_ID AND A.TYPE_FUNC='HC'
|
|
119
|
GROUP BY A.INVOICE_NO,A.INVOICE_NO_SIGN,A.INVOICE_SIGN,A.TAX_NO
|
|
120
|
)
|
|
121
|
AS HD
|
|
122
|
END
|
|
123
|
|
|
124
|
|
|
125
|
DECLARE @ROLE VARCHAR(15)
|
|
126
|
DECLARE @TABLE_RETURN_07 TABLE (HDQT NVARCHAR(50),TGD NVARCHAR(50),PTGD NVARCHAR(50),GDK NVARCHAR(50),GDDV NVARCHAR(50))
|
|
127
|
DECLARE @GDDV NVARCHAR(50),@GDK NVARCHAR(50),@PTGD NVARCHAR(50),@TGD NVARCHAR(50),@HDQT NVARCHAR(50)
|
|
128
|
DECLARE @APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15),@APPR4 VARCHAR(15),@APPR5 VARCHAR(15)
|
|
129
|
DECLARE @POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50),@POS4 NVARCHAR(50)
|
|
130
|
DECLARE @DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50),@DATE4 NVARCHAR(50)
|
|
131
|
DECLARE @BRANCH_TYPE VARCHAR(15), @BRANCH_RQ VARCHAR(15)
|
|
132
|
DECLARE @PROCESS_GDDV VARCHAR(5), @PROCESS_GDK VARCHAR(5), @PROCESS_PTGD VARCHAR(5), @PROCESS_TGD VARCHAR(5), @PROCESS_HDQT VARCHAR(5)
|
|
133
|
|
|
134
|
-- LAY THONG TIN CAP PHE DUYET
|
|
135
|
IF( EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_TYPE ='I'))
|
|
136
|
BEGIN
|
|
137
|
-- GDDV
|
|
138
|
SET @PROCESS_GDDV = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'GDDV')
|
|
139
|
SET @APPR =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV AND
|
|
140
|
ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV))
|
|
141
|
SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR)
|
|
142
|
SET @APPR =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV AND
|
|
143
|
ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV) AND
|
|
144
|
@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))
|
|
145
|
|
|
146
|
SET @POS =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)
|
|
147
|
SET @DATE =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV
|
|
148
|
AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDDV) AND (@APPR IS NOT NULL AND @APPR <>''))
|
|
149
|
-------------
|
|
150
|
-- GDK
|
|
151
|
SET @PROCESS_GDK = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'GDK')
|
|
152
|
SET @APPR1 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK))
|
|
153
|
SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR1)
|
|
154
|
SET @APPR1 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK AND
|
|
155
|
ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK) AND
|
|
156
|
@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))
|
|
157
|
|
|
158
|
SET @POS1 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)
|
|
159
|
SET @DATE1 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK AND
|
|
160
|
ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID = @PROCESS_GDK) AND (@APPR1 IS NOT NULL AND @APPR1 <>''))
|
|
161
|
------------
|
|
162
|
-- PTGD
|
|
163
|
SET @PROCESS_PTGD = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'PTGD')
|
|
164
|
SET @APPR2 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD))
|
|
165
|
SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR2)
|
|
166
|
SET @APPR2 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD AND
|
|
167
|
ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD) AND
|
|
168
|
@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))
|
|
169
|
|
|
170
|
SET @POS2 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)
|
|
171
|
SET @DATE2 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD AND
|
|
172
|
ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_PTGD) AND (@APPR2 IS NOT NULL AND @APPR2 <>''))
|
|
173
|
-- TGD
|
|
174
|
SET @PROCESS_TGD = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'TGD')
|
|
175
|
SET @APPR3 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD))
|
|
176
|
SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR3)
|
|
177
|
SET @APPR3 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD AND
|
|
178
|
ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD) AND
|
|
179
|
@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))
|
|
180
|
|
|
181
|
SET @POS3 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)
|
|
182
|
SET @DATE3 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD
|
|
183
|
AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_TGD) AND (@APPR3 IS NOT NULL AND @APPR3 <>''))
|
|
184
|
-- HDQT
|
|
185
|
SET @PROCESS_HDQT = (SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER = 'HDQT')
|
|
186
|
SET @APPR4 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT))
|
|
187
|
SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@APPR4)
|
|
188
|
SET @APPR4 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT AND
|
|
189
|
ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT) AND
|
|
190
|
@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))
|
|
191
|
SET @POS4 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR4)
|
|
192
|
SET @DATE4 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT AND
|
|
193
|
ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID =@PROCESS_HDQT) AND @APPR4 IS NOT NULL AND @APPR4 <>'')
|
|
194
|
SET @BRANCH_RQ =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
|
|
195
|
SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)
|
|
196
|
|
|
197
|
IF(@BRANCH_TYPE='CN')
|
|
198
|
BEGIN
|
|
199
|
SET @DATE =(SELECT FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt') FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)
|
|
200
|
|
|
201
|
SET @POS =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN= (SELECT CHECKER_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
202
|
|
|
203
|
INSERT INTO @TABLE_RETURN_07
|
|
204
|
VALUES ( ( SELECT TLFULLNAME
|
|
205
|
FROM TL_USER
|
|
206
|
WHERE TLNANME = @APPR4),
|
|
207
|
(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3),
|
|
208
|
(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR2),
|
|
209
|
(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR1),
|
|
210
|
(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = (SELECT CHECKER_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)))
|
|
211
|
INSERT INTO @TABLE_RETURN_07 VALUES (@POS4,@POS3,@POS2,@POS1,@POS)
|
|
212
|
INSERT INTO @TABLE_RETURN_07 VALUES (CASE WHEN @DATE4 <>'' THEN N'Đã chấp thuận '+ @DATE4 ELSE '' END,CASE WHEN @DATE3 <>'' THEN N'Đã chấp thuận '+ @DATE3 ELSE '' END,
|
|
213
|
CASE WHEN @DATE2 <>'' THEN N'Đã chấp thuận '+ @DATE2 ELSE '' END,CASE WHEN @DATE1 <>'' THEN N'Đã chấp thuận '+ @DATE1 ELSE '' END,CASE WHEN @DATE <>'' THEN N'Đã chấp thuận '+ @DATE ELSE '' END)
|
|
214
|
END
|
|
215
|
ELSE
|
|
216
|
BEGIN
|
|
217
|
INSERT INTO @TABLE_RETURN_07
|
|
218
|
VALUES ( ( SELECT TLFULLNAME
|
|
219
|
FROM TL_USER
|
|
220
|
WHERE TLNANME = @APPR4),
|
|
221
|
( SELECT TLFULLNAME
|
|
222
|
FROM TL_USER
|
|
223
|
WHERE TLNANME = @APPR3),
|
|
224
|
( SELECT TLFULLNAME
|
|
225
|
FROM TL_USER
|
|
226
|
WHERE TLNANME = @APPR2),
|
|
227
|
( SELECT TLFULLNAME
|
|
228
|
FROM TL_USER
|
|
229
|
WHERE TLNANME = @APPR1),
|
|
230
|
( SELECT TLFULLNAME
|
|
231
|
FROM TL_USER
|
|
232
|
WHERE TLNANME = @APPR)
|
|
233
|
)
|
|
234
|
|
|
235
|
INSERT INTO @TABLE_RETURN_07
|
|
236
|
VALUES (@POS4,@POS3,@POS2,@POS1,@POS)
|
|
237
|
|
|
238
|
INSERT INTO @TABLE_RETURN_07
|
|
239
|
VALUES ( CASE WHEN @DATE4 <>'' THEN N'Đã chấp thuận '+ @DATE4
|
|
240
|
ELSE ''
|
|
241
|
END,
|
|
242
|
CASE WHEN @DATE3 <>'' THEN N'Đã chấp thuận '+ @DATE3
|
|
243
|
ELSE ''
|
|
244
|
END,
|
|
245
|
CASE WHEN @DATE2 <>'' THEN N'Đã chấp thuận '+ @DATE2
|
|
246
|
ELSE ''
|
|
247
|
END,
|
|
248
|
CASE WHEN @DATE1 <>'' THEN N'Đã chấp thuận '+ @DATE1
|
|
249
|
ELSE ''
|
|
250
|
END,
|
|
251
|
CASE WHEN @DATE <>'' THEN N'Đã chấp thuận '+ @DATE
|
|
252
|
ELSE ''
|
|
253
|
END
|
|
254
|
)
|
|
255
|
END
|
|
256
|
END
|
|
257
|
|
|
258
|
SELECT * FROM @TABLE_RETURN_01
|
|
259
|
SELECT * FROM @TABLE_RETURN_01_DT
|
|
260
|
SELECT * FROM @TABLE_RETURN_02
|
|
261
|
SELECT * FROM @TABLE_RETURN_02_DT
|
|
262
|
SELECT * FROM @TABLE_RETURN_03
|
|
263
|
SELECT * FROM @TABLE_RETURN_04
|
|
264
|
SELECT * FROM @TABLE_RETURN_06
|
|
265
|
SELECT * FROM @TABLE_RETURN_07
|
|
266
|
END
|
|
267
|
|
|
268
|
GO
|
|
269
|
|
|
270
|
|
|
271
|
ALTER PROCEDURE [dbo].[TR_BUDGET_CHECK_LIMIT_YEAR_Byid]
|
|
272
|
@p_GD_ID varchar(20) = NULL,
|
|
273
|
@p_GD_CODE varchar(20) = NULL,
|
|
274
|
@p_BRANCH_ID varchar(20) = NULL,
|
|
275
|
@p_DEP_ID varchar(20) = NULL,
|
|
276
|
@p_KHOI_ID varchar(20) = NULL,
|
|
277
|
@p_TLNAME varchar(20) = NULL,
|
|
278
|
@p_ROLENAME varchar(20) = NULL,
|
|
279
|
@p_MONTH varchar(20) = NULL,
|
|
280
|
@p_YEAR varchar(20) = NULL,
|
|
281
|
@p_BUDGET_LIMIT_CURRENT decimal(18,0) OUT,
|
|
282
|
@p_BUDGET_USED_CURRENT decimal(18,0) OUT
|
|
283
|
AS
|
|
284
|
-- validation
|
|
285
|
DECLARE @l_BUDGET_LIMIT_CURRENT DECIMAL(18,0)
|
|
286
|
-- Lấy hạn mức đã sử dụng đến hiện tại
|
|
287
|
DECLARE @l_BUDGET_USED_CURRENT DECIMAL(18,0)
|
|
288
|
DECLARE @l_BUDGET_USED_M1 DECIMAL(18,0), @l_BUDGET_USED_M2 DECIMAL(18,0), @l_BUDGET_USED_M3 DECIMAL(18,0), @l_BUDGET_USED_M4 DECIMAL(18,0), @l_BUDGET_USED_M5 DECIMAL(18,0),
|
|
289
|
@l_BUDGET_USED_M6 DECIMAL(18,0), @l_BUDGET_USED_M7 DECIMAL(18,0), @l_BUDGET_USED_M8 DECIMAL(18,0), @l_BUDGET_USED_M9 DECIMAL(18,0), @l_BUDGET_USED_M10 DECIMAL(18,0),
|
|
290
|
@l_BUDGET_USED_M11 DECIMAL(18,0), @l_BUDGET_USED_M12 DECIMAL(18,0)
|
|
291
|
|
|
292
|
IF(ISNULL(@p_ROLENAME, '') = '')
|
|
293
|
BEGIN
|
|
294
|
-- duyet moi kiem tra han muc
|
|
295
|
IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_YEAR_LIMIT where GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND AUTH_STATUS = 'A' AND BUDGET_YEAR = @p_YEAR))
|
|
296
|
BEGIN
|
|
297
|
--Validation is here
|
|
298
|
/*
|
|
299
|
NSCP theo định mức năm sẽ được tính bằng lũy kế.
|
|
300
|
Ban đầu, sẽ có 1 ngân sách năm được cấp, nhưng chỉ là dự kiến(hứa sẽ cấp), còn ngân sách thật sự sẽ được chia từng tháng.
|
|
301
|
VD: Ngân sách năm được cấp 1 tỷ. Nhưng 1 tỷ này sẽ chia ra cho 12 tháng.
|
|
302
|
T1 được cấp 100tr, nếu dùng quá định mức sẽ báo.
|
|
303
|
*/
|
|
304
|
-- Lấy hạn mức lũy kế theo tháng hiện tại
|
|
305
|
SET @l_BUDGET_LIMIT_CURRENT = ISNULL((SELECT BUDGET_LIMIT_CURRENT = CASE WHEN MONTH(GETDATE()) = 1 then M1
|
|
306
|
WHEN MONTH(GETDATE()) = 2 then M2
|
|
307
|
WHEN MONTH(GETDATE()) = 3 then M3
|
|
308
|
WHEN MONTH(GETDATE()) = 4 then M4
|
|
309
|
WHEN MONTH(GETDATE()) = 5 then M5
|
|
310
|
WHEN MONTH(GETDATE()) = 6 then M6
|
|
311
|
WHEN MONTH(GETDATE()) = 7 then M7
|
|
312
|
WHEN MONTH(GETDATE()) = 8 then M8
|
|
313
|
WHEN MONTH(GETDATE()) = 9 then M9
|
|
314
|
WHEN MONTH(GETDATE()) = 10 then M10
|
|
315
|
WHEN MONTH(GETDATE()) = 11 then M11
|
|
316
|
WHEN MONTH(GETDATE()) = 12 then M12
|
|
317
|
ELSE M1
|
|
318
|
END
|
|
319
|
FROM TR_REQ_BUDGET_YEAR_LIMIT
|
|
320
|
WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'),0)
|
|
321
|
-- Lấy hạn mức đã sử dụng đến hiện tại
|
|
322
|
SET @l_BUDGET_USED_M1 = ISNULL((SELECT SUM(M1) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
323
|
SET @l_BUDGET_USED_M2 = ISNULL((SELECT SUM(M2) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
324
|
SET @l_BUDGET_USED_M3 = ISNULL((SELECT SUM(M3) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
325
|
SET @l_BUDGET_USED_M4 = ISNULL((SELECT SUM(M4) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
326
|
SET @l_BUDGET_USED_M5 = ISNULL((SELECT SUM(M5) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
327
|
SET @l_BUDGET_USED_M6 = ISNULL((SELECT SUM(M6) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
328
|
SET @l_BUDGET_USED_M7 = ISNULL((SELECT SUM(M7) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
329
|
SET @l_BUDGET_USED_M8 = ISNULL((SELECT SUM(M8) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
330
|
SET @l_BUDGET_USED_M9 = ISNULL((SELECT SUM(M9) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
331
|
SET @l_BUDGET_USED_M10 = ISNULL((SELECT SUM(M10) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
332
|
SET @l_BUDGET_USED_M11 = ISNULL((SELECT SUM(M11) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
333
|
SET @l_BUDGET_USED_M12 = ISNULL((SELECT SUM(M12) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
334
|
|
|
335
|
SET @l_BUDGET_USED_CURRENT = @l_BUDGET_USED_M1 + @l_BUDGET_USED_M2 + @l_BUDGET_USED_M3 + @l_BUDGET_USED_M4 + @l_BUDGET_USED_M5 + @l_BUDGET_USED_M6 + @l_BUDGET_USED_M7 + @l_BUDGET_USED_M8
|
|
336
|
+ @l_BUDGET_USED_M9 + @l_BUDGET_USED_M10 + @l_BUDGET_USED_M11 + @l_BUDGET_USED_M12
|
|
337
|
|
|
338
|
SET @p_BUDGET_LIMIT_CURRENT = @l_BUDGET_LIMIT_CURRENT;
|
|
339
|
SET @p_BUDGET_USED_CURRENT = @l_BUDGET_USED_CURRENT;
|
|
340
|
|
|
341
|
-- CHECK NEU KHONG AP DUNG TINH HAN MUC THI BYPASS
|
|
342
|
IF(ISNULL((SELECT BUDGET_LIMIT_AMT FROM TR_REQ_BUDGET_YEAR_LIMIT WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND BUDGET_YEAR = @p_YEAR AND ISNULL(ROLENAME, '') = '' ),0) > 0)
|
|
343
|
BEGIN
|
|
344
|
IF(@l_BUDGET_USED_CURRENT > @l_BUDGET_LIMIT_CURRENT)
|
|
345
|
BEGIN
|
|
346
|
RETURN '-1'
|
|
347
|
END
|
|
348
|
ELSE
|
|
349
|
BEGIN
|
|
350
|
RETURN '0'
|
|
351
|
END
|
|
352
|
END
|
|
353
|
ELSE
|
|
354
|
BEGIN
|
|
355
|
RETURN '1'
|
|
356
|
END
|
|
357
|
|
|
358
|
END
|
|
359
|
ELSE
|
|
360
|
BEGIN
|
|
361
|
RETURN '1'
|
|
362
|
END
|
|
363
|
END
|
|
364
|
ELSE
|
|
365
|
BEGIN
|
|
366
|
print 'tlname'
|
|
367
|
-- duyet moi kiem tra han muc
|
|
368
|
IF(EXISTS(SELECT TOP 1 * FROM TR_REQ_BUDGET_YEAR_LIMIT where GD_ID = @p_GD_ID AND KHOI_ID = @p_KHOI_ID AND BRANCH_ID = @p_BRANCH_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND AUTH_STATUS = 'A'))
|
|
369
|
BEGIN
|
|
370
|
print 'da duyet'
|
|
371
|
--Validation is here
|
|
372
|
/*
|
|
373
|
NSCP theo định mức năm sẽ được tính bằng lũy kế.
|
|
374
|
Ban đầu, sẽ có 1 ngân sách năm được cấp, nhưng chỉ là dự kiến(hứa sẽ cấp), còn ngân sách thật sự sẽ được chia từng tháng.
|
|
375
|
VD: Ngân sách năm được cấp 1 tỷ. Nhưng 1 tỷ này sẽ chia ra cho 12 tháng.
|
|
376
|
T1 được cấp 100tr, nếu dùng quá định mức sẽ báo.
|
|
377
|
*/
|
|
378
|
-- Lấy hạn mức lũy kế theo tháng hiện tại
|
|
379
|
SET @l_BUDGET_LIMIT_CURRENT = ISNULL((SELECT BUDGET_LIMIT_CURRENT = CASE WHEN MONTH(GETDATE()) = 1 then M1
|
|
380
|
WHEN MONTH(GETDATE()) = 2 then M2
|
|
381
|
WHEN MONTH(GETDATE()) = 3 then M3
|
|
382
|
WHEN MONTH(GETDATE()) = 4 then M4
|
|
383
|
WHEN MONTH(GETDATE()) = 5 then M5
|
|
384
|
WHEN MONTH(GETDATE()) = 6 then M6
|
|
385
|
WHEN MONTH(GETDATE()) = 7 then M7
|
|
386
|
WHEN MONTH(GETDATE()) = 8 then M8
|
|
387
|
WHEN MONTH(GETDATE()) = 9 then M9
|
|
388
|
WHEN MONTH(GETDATE()) = 10 then M10
|
|
389
|
WHEN MONTH(GETDATE()) = 11 then M11
|
|
390
|
WHEN MONTH(GETDATE()) = 12 then M12
|
|
391
|
ELSE M1
|
|
392
|
END
|
|
393
|
FROM TR_REQ_BUDGET_YEAR_LIMIT
|
|
394
|
WHERE GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND AUTH_STATUS = 'A' AND RECORD_STATUS = '1'),0)
|
|
395
|
-- Lấy hạn mức đã sử dụng đến hiện tại
|
|
396
|
SET @l_BUDGET_USED_M1 = ISNULL((SELECT SUM(M1) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
397
|
SET @l_BUDGET_USED_M2 = ISNULL((SELECT SUM(M2) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
398
|
SET @l_BUDGET_USED_M3 = ISNULL((SELECT SUM(M3) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
399
|
SET @l_BUDGET_USED_M4 = ISNULL((SELECT SUM(M4) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
400
|
SET @l_BUDGET_USED_M5 = ISNULL((SELECT SUM(M5) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
401
|
SET @l_BUDGET_USED_M6 = ISNULL((SELECT SUM(M6) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
402
|
SET @l_BUDGET_USED_M7 = ISNULL((SELECT SUM(M7) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
403
|
SET @l_BUDGET_USED_M8 = ISNULL((SELECT SUM(M8) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
404
|
SET @l_BUDGET_USED_M9 = ISNULL((SELECT SUM(M9) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
405
|
SET @l_BUDGET_USED_M10 = ISNULL((SELECT SUM(M10) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
406
|
SET @l_BUDGET_USED_M11 = ISNULL((SELECT SUM(M11) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
407
|
SET @l_BUDGET_USED_M12 = ISNULL((SELECT SUM(M12) FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR AND RECORD_STATUS = '1' GROUP BY GD_ID),0)
|
|
408
|
|
|
409
|
SET @l_BUDGET_USED_CURRENT = @l_BUDGET_USED_M1 + @l_BUDGET_USED_M2 + @l_BUDGET_USED_M3 + @l_BUDGET_USED_M4 + @l_BUDGET_USED_M5 + @l_BUDGET_USED_M6 + @l_BUDGET_USED_M7 + @l_BUDGET_USED_M8
|
|
410
|
+ @l_BUDGET_USED_M9 + @l_BUDGET_USED_M10 + @l_BUDGET_USED_M11 + @l_BUDGET_USED_M12
|
|
411
|
|
|
412
|
SET @p_BUDGET_LIMIT_CURRENT = @l_BUDGET_LIMIT_CURRENT;
|
|
413
|
SET @p_BUDGET_USED_CURRENT = @l_BUDGET_USED_CURRENT;
|
|
414
|
|
|
415
|
print @p_BUDGET_LIMIT_CURRENT
|
|
416
|
print @p_BUDGET_USED_CURRENT
|
|
417
|
|
|
418
|
-- CHECK NEU KHONG AP DUNG TINH HAN MUC THI BYPASS
|
|
419
|
IF(ISNULL((SELECT BUDGET_LIMIT_AMT FROM TR_REQ_BUDGET_YEAR_LIMIT WHERE 1=1 AND GD_ID = @p_GD_ID AND BRANCH_ID = @p_BRANCH_ID AND KHOI_ID = @p_KHOI_ID AND ROLENAME = @p_ROLENAME AND BUDGET_YEAR = @p_YEAR ),0) > 0)
|
|
420
|
BEGIN
|
|
421
|
IF(@l_BUDGET_USED_CURRENT > @l_BUDGET_LIMIT_CURRENT)
|
|
422
|
BEGIN
|
|
423
|
RETURN '-1'
|
|
424
|
END
|
|
425
|
ELSE
|
|
426
|
BEGIN
|
|
427
|
RETURN '0'
|
|
428
|
END
|
|
429
|
END
|
|
430
|
ELSE
|
|
431
|
BEGIN
|
|
432
|
RETURN '1'
|
|
433
|
END
|
|
434
|
END
|
|
435
|
ELSE
|
|
436
|
BEGIN
|
|
437
|
RETURN '1'
|
|
438
|
END
|
|
439
|
END
|
|
440
|
|