|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_KT_Upd]
|
|
3
|
@p_REQ_PAY_ID varchar(15)= NULL,
|
|
4
|
@p_REQ_PAY_CODE varchar(50) = NULL,
|
|
5
|
@p_REQ_DT VARCHAR(20)= NULL,
|
|
6
|
@p_BRANCH_ID varchar(15) = NULL,
|
|
7
|
@p_DEP_ID varchar(15) = NULL,
|
|
8
|
@p_REQ_REASON nvarchar(MAX) = NULL,
|
|
9
|
@p_REQ_TYPE varchar(15) = NULL,
|
|
10
|
@P_REQ_ENTRIES nvarchar(MAX) = NULL,
|
|
11
|
@p_REQ_DESCRIPTION nvarchar(MAX) = NULL,
|
|
12
|
@p_REF_ID varchar(15) = NULL,
|
|
13
|
@p_RECEIVER_PO nvarchar(250) = NULL,
|
|
14
|
@p_RECEIVER_DEBIT nvarchar(250) = NULL,
|
|
15
|
@p_REQ_PAY_TYPE varchar(15) = NULL,
|
|
16
|
@p_REQ_TYPE_CURRENCY nvarchar(50) = NULL,
|
|
17
|
@p_REQ_AMT decimal(18, 0) = NULL,
|
|
18
|
@p_REQ_TEMP_AMT decimal(18, 0) = NULL,
|
|
19
|
@p_MAKER_ID varchar(15) = NULL,
|
|
20
|
@p_CREATE_DT varchar(25) = NULL,
|
|
21
|
@p_EDITOR_ID varchar(15) = NULL,
|
|
22
|
@p_AUTH_STATUS varchar(1) = NULL,
|
|
23
|
@p_CHECKER_ID varchar(15) = NULL,
|
|
24
|
@p_APPROVE_DT varchar(25) = NULL,
|
|
25
|
@p_CREATE_DT_KT varchar(25) = NULL,
|
|
26
|
@p_MAKER_ID_KT varchar(15) = NULL,
|
|
27
|
@p_AUTH_STATUS_KT varchar(1) = NULL,
|
|
28
|
@p_CHECKER_ID_KT varchar(1) = NULL,
|
|
29
|
@p_APPROVE_DT_KT varchar(25)= null,
|
|
30
|
@p_CONFIRM_NOTE nvarchar(500) = NULL,
|
|
31
|
@p_BRANCH_CREATE varchar(15) = NULL,
|
|
32
|
@p_NOTES varchar(15) = NULL,
|
|
33
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
34
|
@p_TRANSFER_MAKER nvarchar(50) = NULL,
|
|
35
|
@p_TRANSFER_DT varchar(25) = NULL,
|
|
36
|
@p_TRASFER_USER_RECIVE varchar(15) = NULL,
|
|
37
|
@p_PROCESS varchar(15) = NULL,
|
|
38
|
@p_PAY_PHASE VARCHAR(15)= NULL,
|
|
39
|
@p_DVDM_ID VARCHAR(15) = NULL,
|
|
40
|
@p_RATE DECIMAL(18,0)= 0,
|
|
41
|
@p_XMP_TEMP XML = NULL
|
|
42
|
AS
|
|
43
|
--Validation is here
|
|
44
|
/*
|
|
45
|
END
|
|
46
|
*/
|
|
47
|
DECLARE @ENTRY_PAIR varchar(20),@DR_CR varchar(20),@ACCT VARCHAR(50), @ACCT_NAME VARCHAR(500), @AMT decimal(18,2),@CURRENCY VARCHAR(15), @EXC_RATE DECIMAL(18,0),
|
|
48
|
@BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15),@TRN_DESC nvarchar(1000),@GL_CODE VARCHAR(10)
|
|
49
|
DECLARE @hdoc INT;
|
|
50
|
EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP;
|
|
51
|
DECLARE XmlData CURSOR FOR
|
|
52
|
SELECT *
|
|
53
|
FROM
|
|
54
|
OPENXML(@hdoc, '/Root/XmlData', 2)
|
|
55
|
WITH(ENTRY_PAIR varchar(20),DR_CR varchar(20),ACCT VARCHAR(50), ACCT_NAME VARCHAR(500), AMT decimal(18,2),CURRENCY VARCHAR(15), EXC_RATE DECIMAL(18,0),
|
|
56
|
BRANCH_ID VARCHAR(15), DEP_ID VARCHAR(15),TRN_DESC nvarchar(1000), GL_CODE VARCHAR(10))
|
|
57
|
OPEN XmlData;
|
|
58
|
DECLARE @INDEX INT =0
|
|
59
|
BEGIN TRANSACTION
|
|
60
|
-- KHAI BAO CAC BUOC DUYET- XAC NHAN
|
|
61
|
DECLARE @LEVEL_JOB VARCHAR(5), @LEVEL_JOB_PREV INT
|
|
62
|
SET @LEVEL_JOB =(SELECT LEVEL_JOB FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID =@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT AND TYPE_JOB ='XL')
|
|
63
|
SET @LEVEL_JOB_PREV = CONVERT(INT,@LEVEL_JOB) -1
|
|
64
|
----
|
|
65
|
--IF(EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND MAKER_ID_KT IS NULL))
|
|
66
|
IF(@p_XMP_TEMP IS NOT NULL)
|
|
67
|
BEGIN
|
|
68
|
UPDATE TR_REQ_ADVANCE_PAYMENT SET
|
|
69
|
AUTH_STATUS_KT ='U', CREATE_DT_KT = GETDATE(),MAKER_ID_KT =@p_MAKER_ID_KT,CHECKER_ID_KT=NULL,APPROVE_DT_KT = NULL, CONFIRM_NOTES=@p_CONFIRM_NOTE
|
|
70
|
WHERE REQ_PAY_ID =@p_REQ_PAY_ID
|
|
71
|
DELETE FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID = @p_REQ_PAY_ID
|
|
72
|
IF @@Error <> 0 GOTO ABORT
|
|
73
|
DECLARE @DR_CR_NAME NVARCHAR(50)
|
|
74
|
FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE
|
|
75
|
WHILE @@fetch_status=0 BEGIN
|
|
76
|
|
|
77
|
SET @INDEX = @INDEX +1
|
|
78
|
IF(@DR_CR='D')
|
|
79
|
BEGIN
|
|
80
|
SET @DR_CR_NAME =N'Nợ'
|
|
81
|
END
|
|
82
|
ELSE
|
|
83
|
BEGIN
|
|
84
|
SET @DR_CR_NAME =N'Có'
|
|
85
|
END
|
|
86
|
--SET @ACCT_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@ACCT)
|
|
87
|
IF(@ACCT_NAME IS NULL OR @ACCT_NAME ='')
|
|
88
|
BEGIN
|
|
89
|
ROLLBACK TRANSACTION
|
|
90
|
CLOSE XmlData;
|
|
91
|
DEALLOCATE XmlData;
|
|
92
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tên tài khoản hạch toán chưa đúng. Vui lòng bấm vào nút Check GL & Check CASA để đồng bộ tài khoản từ Core' ErrorDesc
|
|
93
|
RETURN '-1'
|
|
94
|
END
|
|
95
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
96
|
BEGIN
|
|
97
|
IF(@DR_CR ='C' AND ISNULL(@AMT,0) <> (SELECT ISNULL(SUM(TOTAL_AMT),0) FROM TR_REQ_PAY_METHOD WHERE ACC_NO =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
98
|
BEGIN
|
|
99
|
ROLLBACK TRANSACTION
|
|
100
|
CLOSE XmlData;
|
|
101
|
DEALLOCATE XmlData;
|
|
102
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Số tiền hạch toán có của tài khoản: '+@ACCT + 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 =@ACCT AND REQ_PAY_ID =@p_REQ_PAY_ID),'#,###') ErrorDesc
|
|
103
|
RETURN '-1'
|
|
104
|
END
|
|
105
|
END
|
|
106
|
IF(LEN(@ACCT) >9 AND (@GL_CODE IS NULL OR @GL_CODE =''))
|
|
107
|
BEGIN
|
|
108
|
ROLLBACK TRANSACTION
|
|
109
|
CLOSE XmlData;
|
|
110
|
DEALLOCATE XmlData;
|
|
111
|
SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N'Nếu bạn đang hạch toán CASA. Vui lòng bấm vào nút kiểm tra tài khoản để lấy chính xác thông tin tài khoản (tên tài khoản, tài khoản GL tương tứng) từ Core' ErrorDesc
|
|
112
|
RETURN '-1'
|
|
113
|
---
|
|
114
|
END
|
|
115
|
--IF(@DR_ACCT IS NULL OR @DR_ACCT ='')
|
|
116
|
--BEGIN
|
|
117
|
-- ROLLBACK TRANSACTION
|
|
118
|
-- CLOSE XmlData;
|
|
119
|
-- DEALLOCATE XmlData;
|
|
120
|
-- SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Tài khoản có không được phép để trống' ErrorDesc
|
|
121
|
-- RETURN '-1'
|
|
122
|
--END
|
|
123
|
--IF(@TRN_DESC IS NULL OR @TRN_DESC ='')
|
|
124
|
--BEGIN
|
|
125
|
-- ROLLBACK TRANSACTION
|
|
126
|
-- CLOSE XmlData;
|
|
127
|
-- DEALLOCATE XmlData;
|
|
128
|
-- SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng: '+ CONVERT(VARCHAR(5),@INDEX) + N' Diễn giải hạch toán không được phép để trống' ErrorDesc
|
|
129
|
-- RETURN '-1'
|
|
130
|
--END
|
|
131
|
DECLARE @p_ET_ID VARCHAR(15);
|
|
132
|
EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ENTRIES', @p_ET_ID OUT;
|
|
133
|
IF @p_ET_ID='' OR @p_ET_ID IS NULL GOTO ABORT;
|
|
134
|
INSERT INTO TR_REQ_PAY_ENTRIES (REQ_PAY_DT_ID,REQ_PAY_ID,ENTRY_PAIR,DR_CR, DR_CR_NAME,ACCT,ACCT_NAME,AMT,CURRENCY,EXC_RATE,BRANCH_ID,DEP_ID,TRN_DESC,TRN_DATE,MAKER_ID_KT)
|
|
135
|
VALUES (@p_ET_ID,@p_REQ_PAY_ID,@ENTRY_PAIR,@DR_CR,@DR_CR_NAME,@ACCT,@ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE, @BRANCH_ID, @DEP_ID,@TRN_DESC,GETDATE(),@p_MAKER_ID_KT)
|
|
136
|
|
|
137
|
-- NEU CHUA CO TAI KHOAN THI THEM VO CM_ACCOUNT
|
|
138
|
IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT WHERE ACC_NO =@ACCT))
|
|
139
|
BEGIN
|
|
140
|
INSERT INTO CM_ACCOUNT (ACC_NO, ACC_NAME, TK_GL, TK_GL_NAME, MAKER_ID, CHECKER_ID) VALUES
|
|
141
|
(@ACCT,@ACCT_NAME,@GL_CODE,(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE),'admin','admin')
|
|
142
|
END
|
|
143
|
ELSE
|
|
144
|
BEGIN
|
|
145
|
IF(@GL_CODE IS NOT NULL AND @GL_CODE <> '')
|
|
146
|
BEGIN
|
|
147
|
UPDATE CM_ACCOUNT SET ACC_NAME =@ACCT_NAME, TK_GL =@GL_CODE, TK_GL_NAME =(SELECT TOP 1 ACC_NAME FROM CM_ACCOUNT WHERE ACC_NO =@GL_CODE)
|
|
148
|
WHERE ACC_NO=@ACCT
|
|
149
|
END
|
|
150
|
END
|
|
151
|
IF @@error<>0 GOTO ABORT;
|
|
152
|
FETCH NEXT FROM XmlData INTO @ENTRY_PAIR ,@DR_CR,@ACCT, @ACCT_NAME, @AMT ,@CURRENCY, @EXC_RATE,@BRANCH_ID, @DEP_ID,@TRN_DESC,@GL_CODE
|
|
153
|
END;
|
|
154
|
CLOSE XmlData;
|
|
155
|
DEALLOCATE XmlData;
|
|
156
|
END
|
|
157
|
COMMIT TRANSACTION
|
|
158
|
--- BAT DAU CAP NHAT XAC DINH CAC BUOC DUYET SAU KHI KE TOAN CAP NHAT THONG TIN
|
|
159
|
IF(@p_XMP_TEMP IS NULL)
|
|
160
|
BEGIN
|
|
161
|
-- CHAN NEU CHI CO 1 BUT NO 1 BUT CO THI KHONG DUOC DI TIEP
|
|
162
|
IF(EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_REQ_PAY_ID))
|
|
163
|
BEGIN
|
|
164
|
DECLARE @SUM_CR DECIMAL(18,2), @SUM_DR DECIMAL(18,2)
|
|
165
|
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)
|
|
166
|
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)
|
|
167
|
IF(@SUM_CR<>@SUM_DR)
|
|
168
|
BEGIN
|
|
169
|
--ROLLBACK TRANSACTION
|
|
170
|
--CLOSE XmlData;
|
|
171
|
--DEALLOCATE XmlData;
|
|
172
|
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
|
|
173
|
RETURN '-1'
|
|
174
|
END
|
|
175
|
END
|
|
176
|
UPDATE PL_REQUEST_PROCESS_CHILD SET STATUS_JOB ='P' WHERE REQ_ID=@p_REQ_PAY_ID AND TLNAME=@p_MAKER_ID_KT
|
|
177
|
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)
|
|
178
|
-- CAP NHAT TINH TRANG VE DANG XU LY
|
|
179
|
UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS_KT='P',CREATE_DT_KT =GETDATE() WHERE REQ_PAY_ID=@p_REQ_PAY_ID
|
|
180
|
INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
|
|
181
|
VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID_KT,GETDATE(), N'Giao dịch viên gửi phê duyệt phiếu',N'Giao dịch viên cập nhật thông tin')
|
|
182
|
SELECT '4' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng số: '+(SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)+N'đã được gửi phê duyệt thành công! Vui lòng đợi kiểm soát viên phê duyệt phiếu' ErrorDesc
|
|
183
|
RETURN '4'
|
|
184
|
END
|
|
185
|
SELECT '0' as Result, @p_REQ_PAY_ID REQ_PAY_ID, '' ErrorDesc
|
|
186
|
RETURN '0'
|
|
187
|
ABORT:
|
|
188
|
BEGIN
|
|
189
|
ROLLBACK TRANSACTION
|
|
190
|
CLOSE XmlData;
|
|
191
|
DEALLOCATE XmlData;
|
|
192
|
SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc
|
|
193
|
RETURN '-1'
|
|
194
|
End
|
|
195
|
|