|
1
|
|
|
2
|
|
|
3
|
CREATE TABLE [dbo].[PL_TRADEDETAIL_PAYMENT](
|
|
4
|
[TRADE_ID] [varchar](15) NOT NULL,
|
|
5
|
[AMT_ETM] [decimal](18, 2) NULL,
|
|
6
|
[CREATE_DT] [datetime] NULL
|
|
7
|
)
|
|
8
|
GO
|
|
9
|
|
|
10
|
|
|
11
|
ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_KT_Appr]
|
|
12
|
--Luanlt 2019/17/10 - Sửa params
|
|
13
|
@p_REQ_PAY_ID varchar(15)= NULL,
|
|
14
|
@p_CHECKER_ID_KT varchar(15) = NULL
|
|
15
|
AS
|
|
16
|
--Luanlt 2019/10/17 Validate CORE NOTE không được rỗng
|
|
17
|
--IF ((SELECT CORE_NOTE FROM TR_REQUEST_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID) IS NULL OR (SELECT CORE_NOTE FROM TR_REQUEST_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID) = '')
|
|
18
|
--Luanlt 2019/10/29 Sửa lại validate tồn tại diễn giải hạch toán từ REQUEST_ENTRIES_POST
|
|
19
|
--IF (NOT EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID))
|
|
20
|
--BEGIN
|
|
21
|
-- SELECT '-1' Result,'' REQ_PAY_ID,N'Chưa cập nhật thông tin hạch toán' ErrorDesc
|
|
22
|
-- RETURN '-1'
|
|
23
|
--END
|
|
24
|
-- BEGIN VALIDATE
|
|
25
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE MAKER_ID_KT = @p_CHECKER_ID_KT AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
|
26
|
BEGIN
|
|
27
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn không được phép duyệt phiếu đề nghị thanh toán này! Giao dịch viên không có quyền duyệt phiếu đề nghị thanh toán' ErrorDesc
|
|
28
|
RETURN '-1'
|
|
29
|
END
|
|
30
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
|
31
|
BEGIN
|
|
32
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc
|
|
33
|
RETURN '-1'
|
|
34
|
END
|
|
35
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
|
36
|
BEGIN
|
|
37
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được kiểm soát viên phê duyệt trước đó' ErrorDesc
|
|
38
|
RETURN '-1'
|
|
39
|
END
|
|
40
|
IF EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID)
|
|
41
|
BEGIN
|
|
42
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đang được trả về giao dịch viên. Vui lòng kiểm tra lại thông tin' ErrorDesc
|
|
43
|
RETURN '-1'
|
|
44
|
END
|
|
45
|
-- CHAN CUOI
|
|
46
|
IF (EXISTS (SELECT * FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT <> 'P' AND AUTH_STATUS_KT <> 'S' AND REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
47
|
BEGIN
|
|
48
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' chưa được gửi phê duyệt. Vui lòng đợi người tạo gửi phê duyệt trước khi phê duyệt phiếu' ErrorDesc
|
|
49
|
RETURN '-1'
|
|
50
|
END
|
|
51
|
-- VALIDATE LUOI HACH TOAN
|
|
52
|
|
|
53
|
DECLARE @REQ_PAY_ID varchar(15), @ET_ID varchar(15), @AMT_ET DECIMAL(18,0), @ACC_ET varchar(25), @DRCR_ET varchar(25), @SUM_ET DECIMAL(18,0) = 0
|
|
54
|
DECLARE cursorProduct CURSOR LOCAL FOR SELECT REQ_PAY_ID,ENTRY_PAIR,AMT,ACCT,DR_CR FROM TR_REQ_PAY_ENTRIES A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
55
|
Open cursorProduct
|
|
56
|
FETCH NEXT FROM cursorProduct INTO @REQ_PAY_ID , @ET_ID, @AMT_ET, @ACC_ET , @DRCR_ET
|
|
57
|
WHILE @@FETCH_STATUS = 0
|
|
58
|
BEGIN
|
|
59
|
IF(@DRCR_ET='C' AND EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND ACC_NO =@ACC_ET))
|
|
60
|
BEGIN
|
|
61
|
SET @SUM_ET =(SELECT SUM(AMT) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C' AND ACCT =@ACC_ET)
|
|
62
|
IF(ISNULL(@SUM_ET,0) <> (SELECT SUM(TOTAL_AMT) FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND ACC_NO =@ACC_ET))
|
|
63
|
BEGIN
|
|
64
|
SELECT '-1' as Result, '' REQ_PAY_ID, N' Số tiền hạch toán có của tài khoản: '+@ACC_ET + N' phải bằng với số tiền trên phương thức thanh toán của tài khoản đó là: '+FORMAT((SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACC_ET AND REQ_PAY_ID =@p_REQ_PAY_ID),'#,###') ErrorDesc
|
|
65
|
RETURN '-1'
|
|
66
|
END
|
|
67
|
END
|
|
68
|
-- KIEM TRA SO TIEN NO CO
|
|
69
|
IF (ISNULL((SELECT SUM(AMT) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C' AND ENTRY_PAIR =@ET_ID),0) <> ISNULL((SELECT SUM(AMT) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='D' AND ENTRY_PAIR =@ET_ID),0))
|
|
70
|
BEGIN
|
|
71
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Mã bút toán: ' + @ET_ID + N' Số tiền bút toán Nợ phải bằng với số tiền bút toán Có' ErrorDesc
|
|
72
|
RETURN '-1'
|
|
73
|
END
|
|
74
|
FETCH NEXT FROM cursorProduct INTO @REQ_PAY_ID , @ET_ID, @AMT_ET, @ACC_ET , @DRCR_ET
|
|
75
|
END
|
|
76
|
-- CHAN NEU CHI CO 1 BUT NO 1 BUT CO THI KHONG DUOC DI TIEP
|
|
77
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
78
|
BEGIN
|
|
79
|
DECLARE @SUM_CR DECIMAL(18,2), @SUM_DR DECIMAL(18,2)
|
|
80
|
SET @SUM_CR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='C'),0)
|
|
81
|
SET @SUM_DR =ISNULL((SELECT SUM(AMT*ISNULL(EXC_RATE,1)) FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND DR_CR ='D'),0)
|
|
82
|
IF(@SUM_CR <> @SUM_DR)
|
|
83
|
BEGIN
|
|
84
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Tổng hạch toán nợ phải bằng tổng hạch toán có' ErrorDesc
|
|
85
|
RETURN '-1'
|
|
86
|
END
|
|
87
|
END
|
|
88
|
-- END VALIDATE
|
|
89
|
BEGIN TRANSACTION
|
|
90
|
DECLARE @p_MAKER_ID VARCHAR(15)
|
|
91
|
SET @p_MAKER_ID=(SELECT MAKER_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
|
|
92
|
-- CAC BUOC DUYET
|
|
93
|
DECLARE @LEVEL_JOB VARCHAR(5), @LEVEL_JOB_PREV INT
|
|
94
|
SET @LEVEL_JOB =(SELECT TOP 1 LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_PAY_ID AND TYPE_JOB='KS')
|
|
95
|
SET @LEVEL_JOB_PREV = CONVERT(INT,@LEVEL_JOB) -1
|
|
96
|
---
|
|
97
|
DECLARE @INDEX1 INT =0, @INDEX INT =0, @INDEX_AD INT =0
|
|
98
|
DECLARE @REQ_TYPE VARCHAR(15),@PAY_ADVANCE_ID VARCHAR(15),@AMT_PAY DECIMAL(18,0)
|
|
99
|
DECLARE @PAY_ID VARCHAR(15),@PAY_PHASE VARCHAR(15), @AMT_ADVANCE DECIMAL(18,0),@SCHEDULE_ID VARCHAR(15),@AMT_PAY_REAL DECIMAL(18,2), @AMT_REMAIN DECIMAL(18,2),@REF_ID VARCHAR(15),
|
|
100
|
@TRADED_ID VARCHAR(15), @AMT_EXE DECIMAL(18,2), @AMT_EXE_REMAIN DECIMAL(18,2), @AMT_ADD DECIMAL(18,2), @AMT_REVERT DECIMAL(18,2), @AD_PAY_ID VARCHAR(15),@PARENT_ID VARCHAR(15),
|
|
101
|
@REASON_TTDK NVARCHAR(2000), @GD_ID VARCHAR(15), @IS_PERIOD VARCHAR(15)
|
|
102
|
SET @REQ_TYPE =(SELECT REQ_TYPE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
|
|
103
|
SET @IS_PERIOD =(SELECT IS_PERIOD FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID) ---- 15.04.2023 LUCTV BỔ SUNG KHAI BÁO KIỂM TRA XEM CÓ PHẢI THANH TOÁN CHO CÁC HỢP ĐỒNG ĐỊNH KỲ HAY KHÔNG
|
|
104
|
--- KIEM TRA XEM CO PHAI BUOC DUYET CUOI CUNG CHUA
|
|
105
|
IF(@LEVEL_JOB='1')
|
|
106
|
BEGIN
|
|
107
|
---TRU NGAN SACH
|
|
108
|
INSERT INTO PL_TRADE_DETAIL_LOG_PAY SELECT *, GETDATE(), N'Trước khi duyệt kế toán' FROM PL_TRADEDETAIL WHERE TRADE_ID IN (SELECT DISTINCT TRADE_ID FROM tr_req_pay_budget WHERE REQ_PAY_ID = @p_REQ_PAY_ID)
|
|
109
|
|
|
110
|
DECLARE CURS_TRADE CURSOR FOR SELECT A.TRADE_ID, A.AMT_EXE,A.GD_ID FROM TR_REQ_PAY_BUDGET A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
111
|
OPEN CURS_TRADE
|
|
112
|
FETCH NEXT FROM CURS_TRADE INTO @TRADED_ID,@AMT_EXE,@GD_ID
|
|
113
|
WHILE @@FETCH_STATUS = 0
|
|
114
|
BEGIN
|
|
115
|
IF(@REQ_TYPE <> 'P')
|
|
116
|
BEGIN
|
|
117
|
SET @INDEX = @INDEX +1
|
|
118
|
--IF(EXISTS(SELECT * FROM CM_GOODS WHERE GD_ID =@GD_ID AND GD_TYPE_ID ='NS'))
|
|
119
|
--BEGIN
|
|
120
|
-- SET @AMT_EXE_REMAIN =(SELECT A.AMT_APP - A.AMT_EXE FROM PL_TRADEDETAIL A WHERE A.TRADE_ID = @TRADED_ID)
|
|
121
|
-- --KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET
|
|
122
|
-- IF((@AMT_EXE_REMAIN -@AMT_EXE)<0)
|
|
123
|
-- BEGIN
|
|
124
|
-- ROLLBACK TRANSACTION
|
|
125
|
-- SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Ngân sách sử dụng thực tế đã vượt mức ngân sách sử dụng còn lại: '+ FORMAT(@AMT_EXE_REMAIN,'#,#', 'vi-VN') ErrorDesc
|
|
126
|
-- RETURN '-1'
|
|
127
|
-- END
|
|
128
|
--END
|
|
129
|
END
|
|
130
|
IF(@REQ_TYPE = 'P' AND ISNULL(@IS_PERIOD,'') ='Y') --- 20062023 LUCTV BỔ SUNG NẾU NHƯ THANH TOÁN CHO CÁC HĐ ĐỊNH KỲ (TỨC KHÔNG LINK TỚI TỜ TRÌNH THÌ KHI THANH TOÁN SẼ + AMT_ETM)
|
|
131
|
BEGIN
|
|
132
|
DECLARE @RATE DECIMAL(18,2)
|
|
133
|
SET @RATE = (SELECT TOP 1 RATE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID)
|
|
134
|
|
|
135
|
IF(NOT EXISTS(SELECT * FROM PL_TRADEDETAIL_PAYMENT WHERE TRADE_ID = @TRADED_ID))
|
|
136
|
BEGIN
|
|
137
|
INSERT INTO PL_TRADEDETAIL_PAYMENT(TRADE_ID, AMT_ETM, CREATE_DT)
|
|
138
|
VALUES(@TRADED_ID, @AMT_EXE*@RATE, GETDATE())
|
|
139
|
END
|
|
140
|
ELSE
|
|
141
|
BEGIN
|
|
142
|
UPDATE PL_TRADEDETAIL_PAYMENT SET AMT_ETM = ISNULL(AMT_ETM,0) + @AMT_EXE*@RATE WHERE TRADE_ID = @TRADED_ID
|
|
143
|
END
|
|
144
|
END
|
|
145
|
---- END LUCTV 15.04.2023
|
|
146
|
UPDATE PL_TRADEDETAIL SET AMT_EXE = ISNULL(AMT_EXE,0) + @AMT_EXE*(SELECT TOP 1 RATE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID)
|
|
147
|
WHERE TRADE_ID =@TRADED_ID
|
|
148
|
FETCH NEXT FROM CURS_TRADE INTO @TRADED_ID,@AMT_EXE,@GD_ID
|
|
149
|
END
|
|
150
|
CLOSE CURS_TRADE
|
|
151
|
DEALLOCATE CURS_TRADE
|
|
152
|
|
|
153
|
INSERT INTO PL_TRADE_DETAIL_LOG_PAY SELECT *, GETDATE(), N'Sau khi duyệt kế toán' FROM PL_TRADEDETAIL WHERE TRADE_ID IN (SELECT DISTINCT TRADE_ID FROM tr_req_pay_budget WHERE REQ_PAY_ID = @p_REQ_PAY_ID)
|
|
154
|
|
|
155
|
----
|
|
156
|
IF(@REQ_TYPE ='I')
|
|
157
|
BEGIN
|
|
158
|
DECLARE CURS CURSOR FOR SELECT A.PAY_ADV_ID,A.AMT_USE,A.AMT_ADD,A.AMT_REVERT FROM TR_REQ_PAYMENT_DT A WHERE A.PAY_ID =@p_REQ_PAY_ID
|
|
159
|
OPEN CURS
|
|
160
|
FETCH NEXT FROM CURS INTO @PAY_ADVANCE_ID,@AMT_PAY,@AMT_ADD , @AMT_REVERT
|
|
161
|
WHILE @@FETCH_STATUS = 0
|
|
162
|
BEGIN
|
|
163
|
SET @INDEX_AD = @INDEX_AD +1
|
|
164
|
--KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET
|
|
165
|
IF((SELECT A.REQ_AMT - A.PAY_AMT FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.REQ_PAY_ID = @PAY_ADVANCE_ID)<=0)
|
|
166
|
BEGIN
|
|
167
|
ROLLBACK TRANSACTION
|
|
168
|
SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_AD)+ N': Phiếu tạm ứng này đã được hoàn tạm ứng xong. Vui lòng xóa khỏi danh sách hoàn tạm ứng' ErrorDesc
|
|
169
|
RETURN '-1'
|
|
170
|
END
|
|
171
|
UPDATE TR_REQ_ADVANCE_PAYMENT
|
|
172
|
SET PAY_AMT = ISNULL(PAY_AMT,0)+@AMT_PAY +@AMT_REVERT-@AMT_ADD WHERE REQ_PAY_ID=@PAY_ADVANCE_ID
|
|
173
|
-- NEU HOAN UNG PDN TAM UNG HD DINH KI THI CUNG PHAI UPDATE
|
|
174
|
--UPDATE TR_REQ_PAY_PERIOD SET PROCESS ='2' WHERE REQ_PAY_ID =@PAY_ADVANCE_ID
|
|
175
|
UPDATE TR_REQ_PAY_SCHEDULE SET PROCESS ='2' WHERE REQ_PAY_ID =@PAY_ADVANCE_ID
|
|
176
|
-- UPDATE PDN TAM UNG THANH PROCESS = 2 ==> DA HOAN UNG
|
|
177
|
DECLARE @PAY_AMOUNT_I DECIMAL(18,0),@PAY_DT_I DATETIME,@l_CONTRACT_ID_I VARCHAR(15), @OLD_INDEX_I DECIMAL(18, 0), @NEW_INDEX_I DECIMAL(18, 0), @PARENT_ID_I VARCHAR(15),
|
|
178
|
@PAY_PHASE_I VARCHAR(15), @AD_PAY_ID_I VARCHAR(15), @PROCESS_I VARCHAR(15), @REASON_TTDK_I VARCHAR(15)
|
|
179
|
DECLARE CURS_PERIOD_I CURSOR FOR SELECT A.AMT_PAY,A.PAY_DT_REAL,A.CONTRACT_ID,A.PAY_PHASE,A.AD_PAY_ID,A.PROCESS,A.PARENT_ID,A.REASON, A.OLD_INDEX, A.NEW_INDEX FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID ORDER BY A.CONTRACT_ID
|
|
180
|
OPEN CURS_PERIOD_I
|
|
181
|
FETCH NEXT FROM CURS_PERIOD_I INTO @PAY_AMOUNT_I ,@PAY_DT_I ,@l_CONTRACT_ID_I,@PAY_PHASE_I,@AD_PAY_ID_I, @PROCESS_I,@PARENT_ID_I,@REASON_TTDK_I, @OLD_INDEX_I, @NEW_INDEX_I
|
|
182
|
WHILE @@FETCH_STATUS = 0
|
|
183
|
BEGIN
|
|
184
|
UPDATE TR_REQ_PAY_PERIOD SET PROCESS ='2' WHERE (PERIOD_ID =@PARENT_ID_I OR AD_PAY_ID =@PARENT_ID_I) AND PAY_TYPE ='ADV_PAY'
|
|
185
|
AND PAY_PHASE = @PAY_PHASE_I AND CONTRACT_ID = @l_CONTRACT_ID_I AND OLD_INDEX = @OLD_INDEX_I AND NEW_INDEX = @NEW_INDEX_I
|
|
186
|
IF @@Error <> 0 GOTO ABORT
|
|
187
|
FETCH NEXT FROM CURS_PERIOD_I INTO @PAY_AMOUNT_I ,@PAY_DT_I ,@l_CONTRACT_ID_I,@PAY_PHASE_I,@AD_PAY_ID_I, @PROCESS_I,@PARENT_ID_I,@REASON_TTDK_I, @OLD_INDEX_I, @NEW_INDEX_I
|
|
188
|
END
|
|
189
|
CLOSE CURS_PERIOD_I
|
|
190
|
DEALLOCATE CURS_PERIOD_I
|
|
191
|
FETCH NEXT FROM CURS INTO @PAY_ADVANCE_ID,@AMT_PAY,@AMT_ADD , @AMT_REVERT
|
|
192
|
END
|
|
193
|
CLOSE CURS
|
|
194
|
DEALLOCATE CURS
|
|
195
|
END
|
|
196
|
-- KIEM TRA NEU LA THANH TOAN PO
|
|
197
|
ELSE IF((@REQ_TYPE ='P' AND ((SELECT IS_PERIOD FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) <>'Y' OR (SELECT IS_PERIOD FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) IS NULL )) OR (@REQ_TYPE ='I'))
|
|
198
|
BEGIN
|
|
199
|
DECLARE @PROCESS VARCHAR(15), @PDN_TAM_UNG VARCHAR(15), @SO_TIEN_TAM_UNG DECIMAL(18,0)
|
|
200
|
DECLARE CURS_AD CURSOR FOR SELECT A.SCHEDULE_ID,A.AMT_PAY_DO,A.PAY_ID,A.PAY_PHASE,A.AMT_ADVANCE,A.AMT_PAY_REAL, A.AMT_REMAIN,A.REF_ID,A.PROCESS
|
|
201
|
FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
202
|
OPEN CURS_AD
|
|
203
|
FETCH NEXT FROM CURS_AD INTO @SCHEDULE_ID,@AMT_PAY,@PAY_ID,@PAY_PHASE,@AMT_ADVANCE,@AMT_PAY_REAL, @AMT_REMAIN,@REF_ID,@PROCESS
|
|
204
|
WHILE @@FETCH_STATUS = 0
|
|
205
|
BEGIN
|
|
206
|
SET @INDEX1 = @INDEX1 +1
|
|
207
|
-- KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET
|
|
208
|
--IF((SELECT MIN(A.AMT_REMAIN) FROM TR_REQ_PAY_SCHEDULE A WHERE A.PAY_ID = @PAY_ID GROUP BY A.PAY_ID )=0)
|
|
209
|
--BEGIN
|
|
210
|
-- ROLLBACK TRANSACTION
|
|
211
|
-- SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX1)+ N':Kì '+ @PAY_PHASE+N' đã thanh toán xong' ErrorDesc
|
|
212
|
-- RETURN '-1'
|
|
213
|
--END
|
|
214
|
IF(@AMT_ADVANCE >0 AND @AMT_PAY=0)
|
|
215
|
BEGIN
|
|
216
|
UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTU' WHERE SCHEDULE_ID=@SCHEDULE_ID
|
|
217
|
END
|
|
218
|
ELSE IF(@AMT_PAY_REAL>0)
|
|
219
|
BEGIN
|
|
220
|
UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTT' WHERE SCHEDULE_ID=@SCHEDULE_ID
|
|
221
|
END
|
|
222
|
ELSE IF(@AMT_PAY=0 AND @AMT_ADVANCE =0 AND @AMT_PAY_REAL =0)
|
|
223
|
BEGIN
|
|
224
|
UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='CTT' WHERE SCHEDULE_ID=@SCHEDULE_ID
|
|
225
|
END
|
|
226
|
ELSE IF(@AMT_REMAIN =0)
|
|
227
|
BEGIN
|
|
228
|
UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='TTX' WHERE SCHEDULE_ID=@SCHEDULE_ID
|
|
229
|
END
|
|
230
|
-- HOAN TAM UNG 100% CHO 1 DOT THANH TOAN PO NAO DO
|
|
231
|
IF(@PROCESS ='2')
|
|
232
|
BEGIN
|
|
233
|
SET @PDN_TAM_UNG = (SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' ORDER BY SCHEDULE_ID)
|
|
234
|
SET @SO_TIEN_TAM_UNG =(SELECT TOP 1 AMT_PAY_REAL FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' ORDER BY SCHEDULE_ID)
|
|
235
|
UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT =ISNULL(PAY_AMT,0) + @SO_TIEN_TAM_UNG WHERE REQ_PAY_ID =@PDN_TAM_UNG
|
|
236
|
-- CAP NHAT DOT TAM UNG VE TINH TRANG LA DA HOAN TAM UNG
|
|
237
|
UPDATE TR_REQ_PAY_SCHEDULE SET PROCESS ='2' WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' AND REQ_PAY_ID=@PDN_TAM_UNG
|
|
238
|
END
|
|
239
|
IF(@PROCESS ='0')
|
|
240
|
BEGIN
|
|
241
|
SET @PDN_TAM_UNG = (SELECT TOP 1 REQ_PAY_ID FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' ORDER BY SCHEDULE_ID)
|
|
242
|
SET @SO_TIEN_TAM_UNG =(SELECT TOP 1 AMT_PAY_REAL FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' ORDER BY SCHEDULE_ID)
|
|
243
|
UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT =ISNULL(PAY_AMT,0) + @SO_TIEN_TAM_UNG WHERE REQ_PAY_ID =@PDN_TAM_UNG
|
|
244
|
-- CAP NHAT DOT TAM UNG VE TINH TRANG LA DA HOAN TAM UNG
|
|
245
|
UPDATE TR_REQ_PAY_SCHEDULE SET PROCESS ='2' WHERE PAY_ID =@PAY_ID AND TRN_TYPE ='ADV_PAY' AND REQ_PAY_ID=@PDN_TAM_UNG
|
|
246
|
END
|
|
247
|
--UPDATE TR_REQ_ADVANCE_PAYMENT
|
|
248
|
--SET PAY_AMT = ISNULL(PAY_AMT,0)+@AMT_PAY
|
|
249
|
FETCH NEXT FROM CURS_AD INTO @SCHEDULE_ID,@AMT_PAY,@PAY_ID,@PAY_PHASE,@AMT_ADVANCE,@AMT_PAY_REAL, @AMT_REMAIN,@REF_ID,@PROCESS
|
|
250
|
END
|
|
251
|
CLOSE CURS_AD
|
|
252
|
DEALLOCATE CURS_AD
|
|
253
|
DECLARE @IS_CLOSED VARCHAR(1)
|
|
254
|
DECLARE CURS_PO CURSOR FOR SELECT A.REF_ID, A.IS_CLOSED FROM TR_REQ_ADVANCE_DT A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
255
|
OPEN CURS_PO
|
|
256
|
FETCH NEXT FROM CURS_PO INTO @REF_ID , @IS_CLOSED
|
|
257
|
WHILE @@FETCH_STATUS = 0
|
|
258
|
BEGIN -- BAT DAU KIEM TRA DONG PO
|
|
259
|
IF(@IS_CLOSED ='Y')
|
|
260
|
BEGIN
|
|
261
|
IF(EXISTS (SELECT * FROM TR_CONTRACT WHERE CONTRACT_ID =@REF_ID))
|
|
262
|
BEGIN
|
|
263
|
UPDATE TR_CONTRACT SET IS_CLOSED='Y' WHERE CONTRACT_ID =@REF_ID
|
|
264
|
END
|
|
265
|
ELSE
|
|
266
|
BEGIN
|
|
267
|
UPDATE TR_PO_MASTER SET IS_CLOSED='Y' WHERE PO_ID =@REF_ID
|
|
268
|
END
|
|
269
|
END
|
|
270
|
ELSE
|
|
271
|
BEGIN
|
|
272
|
DECLARE @TOAL_PAY_REAL DECIMAL(18,2) =0,@TOAL_PAY_DK DECIMAL(18,2) =0, @TOTAL_AMT_BACK DECIMAL(18,2)
|
|
273
|
-- TONG SO TIEN THANH TOAN VA HOAN UNG
|
|
274
|
SET @TOAL_PAY_REAL =(SELECT SUM(AMT_PAY_REAL) FROM TR_REQ_PAY_SCHEDULE WHERE REF_ID=@REF_ID)
|
|
275
|
-- @TOTAL_AMT_BACK: TONG SO TIEN CON LAI CAN THANH TOAN
|
|
276
|
SET @TOTAL_AMT_BACK =(SELECT SUM(AMT_REMAIN) FROM TR_REQ_PAY_SCHEDULE WHERE REF_ID=@REF_ID AND REQ_ADV_ID IS NOT NULL)
|
|
277
|
IF(EXISTS (SELECT * FROM TR_CONTRACT WHERE CONTRACT_ID =@REF_ID))
|
|
278
|
BEGIN
|
|
279
|
-- @TOAL_PAY_DK: TONG SO TIEN TAT CA KY THANH TOAN
|
|
280
|
SET @TOAL_PAY_DK =(SELECT SUM(AMOUNT) FROM TR_CONTRACT_PAYMENT WHERE CONTRACT_ID=@REF_ID)
|
|
281
|
IF((@TOAL_PAY_DK-@TOAL_PAY_REAL-@TOTAL_AMT_BACK)=0)
|
|
282
|
BEGIN
|
|
283
|
UPDATE TR_CONTRACT SET IS_CLOSED='Y' WHERE CONTRACT_ID =@REF_ID
|
|
284
|
END
|
|
285
|
END
|
|
286
|
BEGIN
|
|
287
|
SET @TOAL_PAY_DK =(SELECT SUM(AMOUNT) FROM TR_PO_PAYMENT WHERE PO_ID=@REF_ID)
|
|
288
|
IF((@TOAL_PAY_DK- @TOAL_PAY_REAL-@TOTAL_AMT_BACK)=0)
|
|
289
|
BEGIN
|
|
290
|
UPDATE TR_PO_MASTER SET IS_CLOSED='Y' WHERE PO_ID =@REF_ID
|
|
291
|
END
|
|
292
|
END
|
|
293
|
END
|
|
294
|
FETCH NEXT FROM CURS_PO INTO @REF_ID , @IS_CLOSED
|
|
295
|
END -- KET THUC KIEM TRA DONG PO
|
|
296
|
CLOSE CURS_PO
|
|
297
|
DEALLOCATE CURS_PO
|
|
298
|
END -- KET THUC KIEM TRA NEU LA THANH TOAN PO
|
|
299
|
--- KIEM TRA NEU LA THANH TOAN CAC HOP DONG DINH KI
|
|
300
|
ELSE IF((@REQ_TYPE ='P' AND (SELECT IS_PERIOD FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) ='Y') OR (@REQ_TYPE ='I'))
|
|
301
|
BEGIN
|
|
302
|
UPDATE TR_REQ_PAY_PERIOD SET PAY_DT_REAL = GETDATE() WHERE REQ_PAY_ID =@p_REQ_PAY_ID
|
|
303
|
DECLARE @PAY_AMOUNT DECIMAL(18,0),@PAY_DT DATETIME,@l_CONTRACT_ID VARCHAR(15), @OLD_INDEX DECIMAL(18, 0), @NEW_INDEX DECIMAL(18, 0)
|
|
304
|
DECLARE CURS_PERIOD CURSOR FOR SELECT A.AMT_PAY,A.PAY_DT_REAL,A.CONTRACT_ID,A.PAY_PHASE,A.AD_PAY_ID,A.PROCESS,A.PARENT_ID,A.REASON, A.OLD_INDEX, A.NEW_INDEX FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID ORDER BY A.CONTRACT_ID
|
|
305
|
OPEN CURS_PERIOD
|
|
306
|
FETCH NEXT FROM CURS_PERIOD INTO @PAY_AMOUNT ,@PAY_DT ,@l_CONTRACT_ID,@PAY_PHASE,@AD_PAY_ID, @PROCESS,@PARENT_ID,@REASON_TTDK, @OLD_INDEX, @NEW_INDEX
|
|
307
|
WHILE @@FETCH_STATUS = 0
|
|
308
|
BEGIN
|
|
309
|
IF(@PROCESS ='1')
|
|
310
|
BEGIN
|
|
311
|
DECLARE @l_PAY_ID VARCHAR(15)
|
|
312
|
EXEC SYS_CodeMasters_Gen 'TR_CONTRACT_PAYMENT', @l_PAY_ID out
|
|
313
|
IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
|
|
314
|
INSERT INTO TR_CONTRACT_PAYMENT([PAY_AMOUNT],[PAY_DT], [PAY_ID],[CONTRACT_ID],[PAY_PHASE],[EXPECTED_DT],[PERCENT],[AMOUNT],[PAY_STATUS],[NOTES],[RECORD_STATUS],[MAKER_ID],[CREATE_DT],[AUTH_STATUS],[CHECKER_ID],[APPROVE_DT])
|
|
315
|
VALUES(@PAY_AMOUNT,CONVERT(DATE,@PAY_DT,103),
|
|
316
|
@l_PAY_ID ,@l_CONTRACT_ID ,@PAY_PHASE ,CONVERT(DATE,@PAY_DT,103),100,@PAY_AMOUNT,'1',@REASON_TTDK ,'1' ,@p_MAKER_ID ,GETDATE() ,'A' ,@p_CHECKER_ID_KT,GETDATE())
|
|
317
|
END
|
|
318
|
ELSE
|
|
319
|
BEGIN
|
|
320
|
IF(@PROCESS ='2' AND (@AD_PAY_ID IS NULL OR @AD_PAY_ID =''))
|
|
321
|
BEGIN
|
|
322
|
ROLLBACK TRANSACTION
|
|
323
|
SELECT '-1' Result,'' REQ_PAY_ID,N'Lưới chi tiết thanh toán định kì: Tại hình thức thanh toán Hoàn ứng, số phiếu tạm ứng không được phép để trống' ErrorDesc
|
|
324
|
RETURN '-1'
|
|
325
|
END
|
|
326
|
UPDATE TR_REQ_PAY_PERIOD SET PROCESS ='2' WHERE (PERIOD_ID =@PARENT_ID OR AD_PAY_ID =@PARENT_ID) AND PAY_TYPE ='ADV_PAY' AND PAY_PHASE = @PAY_PHASE AND CONTRACT_ID = @l_CONTRACT_ID AND OLD_INDEX = @OLD_INDEX AND NEW_INDEX = @NEW_INDEX
|
|
327
|
UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT = ISNULL(PAY_AMT, 0) + @PAY_AMOUNT WHERE REQ_PAY_ID =@AD_PAY_ID
|
|
328
|
END
|
|
329
|
IF @@Error <> 0 GOTO ABORT
|
|
330
|
FETCH NEXT FROM CURS_PERIOD INTO @PAY_AMOUNT ,@PAY_DT ,@l_CONTRACT_ID,@PAY_PHASE,@AD_PAY_ID, @PROCESS,@PARENT_ID,@REASON_TTDK, @OLD_INDEX, @NEW_INDEX
|
|
331
|
END
|
|
332
|
CLOSE CURS_PERIOD
|
|
333
|
DEALLOCATE CURS_PERIOD
|
|
334
|
END
|
|
335
|
---END
|
|
336
|
UPDATE TR_REQ_PAYMENT
|
|
337
|
SET AUTH_STATUS_KT='A', CHECKER_ID_KT = @p_CHECKER_ID_KT, APPROVE_DT_KT = GETDATE()
|
|
338
|
WHERE REQ_PAY_ID = @p_REQ_PAY_ID
|
|
339
|
-- DOANPTT 291222: THEM LOG KPI
|
|
340
|
UPDATE TR_REQ_KPI
|
|
341
|
SET AUTH_STATUS_KT ='A', APPROVE_DT_KT = GETDATE(),CHECKER_ID_KT = @p_CHECKER_ID_KT, REQ_STATUS = 'APP', IS_DONE = '1'
|
|
342
|
WHERE REQ_ID = @p_REQ_PAY_ID AND AUTH_STATUS_KT = 'P' AND IS_DONE = '0'
|
|
343
|
|
|
344
|
---UPDATE DETAIL
|
|
345
|
UPDATE TR_REQ_PAYMENT_DT SET AUTH_STATUS_KT='A',CHECKER_ID_KT =@p_CHECKER_ID_KT,APPROVE_DT_KT = GETDATE()
|
|
346
|
WHERE PAY_ID =@p_REQ_PAY_ID
|
|
347
|
-- UPDATE SCHEDULE
|
|
348
|
UPDATE TR_REQ_PAY_SCHEDULE SET AUTH_STATUS_KT='A' WHERE REQ_PAY_ID =@p_REQ_PAY_ID
|
|
349
|
-- UPDATE PERIOD
|
|
350
|
UPDATE TR_REQ_PAY_PERIOD SET AUTH_STATUS_KT='A' WHERE REQ_PAY_ID =@p_REQ_PAY_ID
|
|
351
|
-- INSERT VAO PL_PROCESS
|
|
352
|
INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
|
|
353
|
VALUES(@p_REQ_PAY_ID,'APP',@p_CHECKER_ID_KT,GETDATE(), N'Kiểm soát viên phê duyệt phiếu',N'Kế toán duyệt phiếu')
|
|
354
|
IF @@Error <> 0 GOTO ABORT
|
|
355
|
UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TYPE_JOB ='KS' AND LEVEL_JOB =@LEVEL_JOB
|
|
356
|
--thieuvq ban vao core - 171219 BEGIN
|
|
357
|
DECLARE @MAKER VARCHAR(15) = (SELECT MAKER_ID_KT FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID)
|
|
358
|
DECLARE @RES VARCHAR(10)
|
|
359
|
--- BAN BUT TOAN VAO CORE
|
|
360
|
EXEC [dbo].[PAY_ENTRIES_POST_SetApp] @p_REQ_PAY_ID,@MAKER,@p_CHECKER_ID_KT, @RES OUT
|
|
361
|
--END
|
|
362
|
END
|
|
363
|
ELSE
|
|
364
|
BEGIN
|
|
365
|
-- CAP NHAT C CHO CAP CHA
|
|
366
|
UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TLNAME=@p_CHECKER_ID_KT
|
|
367
|
UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='C' WHERE REQ_ID=@p_REQ_PAY_ID AND LEVEL_JOB = CONVERT(VARCHAR(5),@LEVEL_JOB_PREV)
|
|
368
|
SELECT '2' as Result, @p_REQ_PAY_ID REQ_PAY_ID ,N'Xác nhận phiếu thành công' ErrorDesc
|
|
369
|
RETURN '2'
|
|
370
|
|
|
371
|
END
|
|
372
|
|
|
373
|
|
|
374
|
IF((SELECT COUNT(*) FROM PL_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND PROCESS_ID = 'APP' AND PROCESS_DESC = N'Kiểm soát viên phê duyệt phiếu') > 1)
|
|
375
|
BEGIN
|
|
376
|
ROLLBACK TRANSACTION
|
|
377
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị thanh toán số: '+(SELECT ISNULL(REQ_PAY_CODE, '') FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID)+N' đã được kiểm soát viên phê duyệt trước đó. Lỗi này xảy ra khi duyệt 2 lần liên tiếp. Vui lòng liên hệ IT để được hỗ trợ thêm' ErrorDesc
|
|
378
|
RETURN '-1'
|
|
379
|
END
|
|
380
|
|
|
381
|
|
|
382
|
COMMIT TRANSACTION
|
|
383
|
SELECT '0' as Result, @p_REQ_PAY_ID REQ_PAY_ID ,N'Phiếu đề nghị thanh toán số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N' đã được phê duyệt thành công!' ErrorDesc
|
|
384
|
RETURN '0'
|
|
385
|
ABORT:
|
|
386
|
BEGIN
|
|
387
|
ROLLBACK TRANSACTION
|
|
388
|
SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
|
|
389
|
RETURN '-1'
|
|
390
|
END
|
|
391
|
--23062023_secretkey
|
|
392
|
|
|
393
|
|