|
1
|
|
|
2
|
ALTER PROC [dbo].[PL_REQUEST_DOC_Settlment]
|
|
3
|
@p_REQ_ID VARCHAR (15) = NULL,
|
|
4
|
@p_AMT_SETT DECIMAL(18,2) =NULL,
|
|
5
|
@p_AMT_USE DECIMAL(18,2) = NULL,
|
|
6
|
@p_AMT_REVERT DECIMAL(18,2)= NULL
|
|
7
|
AS
|
|
8
|
BEGIN TRANSACTION
|
|
9
|
|
|
10
|
|
|
11
|
|
|
12
|
DECLARE @ERROR NVARCHAR(500)
|
|
13
|
|
|
14
|
IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE IS_SETTLMENT=1 AND REQ_ID=@p_REQ_ID))
|
|
15
|
BEGIN
|
|
16
|
SET @ERROR = (SELECT ErrorDesc FROM dbo.SYS_ERROR WHERE ErrorCode='SETTLMENT-001')
|
|
17
|
ROLLBACK TRANSACTION
|
|
18
|
SELECT '-1' as Result, @p_REQ_ID AS REQ_PAY_ID, N'Quyết toán tờ trình thất bại! Tờ trình đã được quyết toán' ErrorDesc
|
|
19
|
RETURN '-1'
|
|
20
|
END
|
|
21
|
IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE PROCESS_ID <>'APPROVE' AND REQ_ID=@p_REQ_ID))
|
|
22
|
BEGIN
|
|
23
|
--SET @ERROR = (SELECT ErrorDesc FROM dbo.SYS_ERROR WHERE ErrorCode='SETTLMENT-001')
|
|
24
|
ROLLBACK TRANSACTION
|
|
25
|
SELECT '-1' as Result, @p_REQ_ID AS REQ_PAY_ID, N'Quyết toán tờ trình số '+ (SELECT ISNULL(REQ_CODE, REQ_ID) FROM PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)+N' thất bại! Tờ trình chưa được phê duyệt hoàn tất' ErrorDesc
|
|
26
|
RETURN '-1'
|
|
27
|
END
|
|
28
|
IF(EXISTS(SELECT REQ_ID FROM dbo.PL_REQUEST_DOC WHERE PROCESS_ID <>'APPROVE' AND REQ_PARENT_ID=@p_REQ_ID))
|
|
29
|
BEGIN
|
|
30
|
--SET @ERROR = (SELECT ErrorDesc FROM dbo.SYS_ERROR WHERE ErrorCode='SETTLMENT-001')
|
|
31
|
ROLLBACK TRANSACTION
|
|
32
|
SELECT '-1' as Result, @p_REQ_ID AS REQ_PAY_ID, N'Quyết toán tờ trình số '+ (SELECT ISNULL(REQ_CODE, REQ_ID) FROM PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)+N' thất bại! Tờ trình bổ sung chưa được phê duyệt hoàn tất' ErrorDesc
|
|
33
|
RETURN '-1'
|
|
34
|
END
|
|
35
|
IF(EXISTS(SELECT REQ_CODE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (REQ_PARENT_ID IS NOT NULL AND REQ_PARENT_ID='')))
|
|
36
|
BEGIN
|
|
37
|
ROLLBACK TRANSACTION
|
|
38
|
SELECT '-1' as Result, @p_REQ_ID AS REQ_PAY_ID, N'Quyết toán tờ trình số '+ (SELECT ISNULL(REQ_CODE, REQ_ID) FROM PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)+N' thất bại! Đây là tờ trình con, vui lòng quyết toán tờ trình chính.' ErrorDesc
|
|
39
|
RETURN '-1'
|
|
40
|
END
|
|
41
|
IF(EXISTS(SELECT REQ_CODE FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND(PL_BASED_ID IS NOT NULL AND PL_BASED_ID='') ))
|
|
42
|
BEGIN
|
|
43
|
ROLLBACK TRANSACTION
|
|
44
|
SELECT '-1' as Result, @p_REQ_ID AS REQ_PAY_ID, N'Quyết toán tờ trình số '+ (SELECT ISNULL(REQ_CODE, REQ_ID) FROM PL_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID)+N' thất bại! Đây là tờ trình con, vui lòng quyết toán tờ trình chính.' ErrorDesc
|
|
45
|
RETURN '-1'
|
|
46
|
END
|
|
47
|
|
|
48
|
DECLARE @ERROR_CODE NVARCHAR(MAX)
|
|
49
|
|
|
50
|
IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID =@p_REQ_ID AND PROCESS_ID <> 'APPROVE' ) )
|
|
51
|
BEGIN
|
|
52
|
SET @ERROR_CODE=(select STUFF( (select '; ' + DTA.REQ_CODE FROM TR_REQUEST_DOC DTA WHERE DTA.PL_REQ_ID=@p_REQ_ID AND DTA.PROCESS_ID<> 'APPROVE'
|
|
53
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))
|
|
54
|
ROLLBACK TRANSACTION
|
|
55
|
SELECT '-1' as Result, @p_REQ_ID AS REQ_PAY_ID, N'Quyết toán tờ trình thất bại! Đang tồn tại phiếu yêu cầu mua sắm chưa được phê duyệt: '+@ERROR_CODE AS ErrorDesc
|
|
56
|
RETURN '-1'
|
|
57
|
END
|
|
58
|
|
|
59
|
IF(EXISTS(SELECT PS.SERVICE_ID FROM dbo.TR_REQ_PAYMENT TP
|
|
60
|
LEFT JOIN dbo.TR_REQ_PAY_SERVICE PS ON PS.REQ_PAY_ID=TP.REQ_PAY_ID
|
|
61
|
WHERE PS.EMP_ID=@p_REQ_ID AND TP.AUTH_STATUS_KT <>'A'))
|
|
62
|
BEGIN
|
|
63
|
SET @ERROR_CODE=(select STUFF( (select '; ' + DTA.REQ_PAY_CODE FROM dbo.TR_REQ_PAYMENT DTA WHERE DTA.AUTH_STATUS_KT <>'A' AND DTA.REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM dbo.TR_REQ_PAY_SERVICE WHERE EMP_ID=@p_REQ_ID)
|
|
64
|
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, ''))
|
|
65
|
ROLLBACK TRANSACTION
|
|
66
|
SELECT '-1' as Result, @p_REQ_ID AS REQ_PAY_ID, N'Quyết toán tờ trình thất bại! Đang có giao dịch thanh toán sử dụng ngân sách của tờ trình. Vui lòng hủy phiếu thanh toán trước khi quyết toán tờ trình: '+@ERROR_CODE AS ErrorDesc
|
|
67
|
RETURN '-1'
|
|
68
|
END
|
|
69
|
|
|
70
|
|
|
71
|
|
|
72
|
|
|
73
|
|
|
74
|
DECLARE @listTRADE TABLE
|
|
75
|
(
|
|
76
|
TRADE_ID VARCHAR(20),
|
|
77
|
AMT_APP DECIMAL(18,2),
|
|
78
|
AMT_ROLEBACK DECIMAL(18,2)
|
|
79
|
)
|
|
80
|
|
|
81
|
INSERT @listTRADE
|
|
82
|
SELECT T.TRADE_ID,
|
|
83
|
SUM(T.TOTAL_AMT),SUM(T.ATM_ROLL) FROM(
|
|
84
|
SELECT TRADE_ID,SUM(TOTAL_AMT) AS TOTAL_AMT,0 AS ATM_ROLL FROM dbo.PL_REQUEST_DOC_DT
|
|
85
|
WHERE REQ_ID=@p_REQ_ID GROUP BY TRADE_ID
|
|
86
|
UNION ALL
|
|
87
|
SELECT DT.TRADE_ID,SUM(DT.TOTAL_AMT) AS TOTAL_AMT,0 AS ATM_ROLL FROM dbo.PL_REQUEST_DOC PL
|
|
88
|
LEFT JOIN dbo.PL_REQUEST_DOC_DT DT ON DT.REQ_ID=PL.REQ_ID
|
|
89
|
WHERE PL.REQ_PARENT_ID=@p_REQ_ID
|
|
90
|
GROUP BY TRADE_ID
|
|
91
|
) T
|
|
92
|
GROUP BY T.TRADE_ID
|
|
93
|
|
|
94
|
-- LAY DS PYCMS CUA TO TRINH
|
|
95
|
DECLARE @listID TABLE (
|
|
96
|
TR_REQ_ID VARCHAR(15)
|
|
97
|
)
|
|
98
|
|
|
99
|
INSERT INTO @listID
|
|
100
|
SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID=@p_REQ_ID
|
|
101
|
|
|
102
|
|
|
103
|
DECLARE @listREQPAYID TABLE (
|
|
104
|
|
|
105
|
REQ_PAY_ID VARCHAR(20)
|
|
106
|
)
|
|
107
|
INSERT INTO @listREQPAYID
|
|
108
|
SELECT REQ_PAY_ID FROM dbo.TR_REQ_PAY_SERVICE WHERE EMP_ID=@p_REQ_ID
|
|
109
|
GROUP BY REQ_PAY_ID
|
|
110
|
|
|
111
|
|
|
112
|
DECLARE @listTRADE_EXE TABLE
|
|
113
|
(
|
|
114
|
TRADE_ID VARCHAR(20),
|
|
115
|
AMT_EXE DECIMAL(18,2)
|
|
116
|
|
|
117
|
)
|
|
118
|
INSERT INTO @listTRADE_EXE
|
|
119
|
SELECT T.TRADE_ID,SUM(T.AMT_EXE) FROM
|
|
120
|
(
|
|
121
|
SELECT TP.TRADE_ID,SUM(TP.AMT_EXE) AS AMT_EXE FROM dbo.TR_REQ_PAY_BUDGET TP
|
|
122
|
WHERE EXISTS(SELECT L.REQ_PAY_ID FROM @listREQPAYID L WHERE L.REQ_PAY_ID=TP.REQ_PAY_ID)
|
|
123
|
GROUP BY TP.TRADE_ID
|
|
124
|
UNION ALL
|
|
125
|
SELECT PLDT.TRADE_ID,SUM(DT.TOTAL_AMT) AS AMT_EXE FROM dbo.TR_REQUEST_DOC_DT DT
|
|
126
|
LEFT JOIN dbo.PL_REQUEST_DOC_DT PLDT ON PLDT.REQDT_ID=DT.PL_REQDT_ID
|
|
127
|
WHERE DT.REQ_DOC_ID IN (SELECT TR_REQ_ID FROM @listID)
|
|
128
|
GROUP BY PLDT.TRADE_ID
|
|
129
|
)T
|
|
130
|
GROUP BY T.TRADE_ID
|
|
131
|
|
|
132
|
UPDATE @listTRADE SET AMT_ROLEBACK=AMT_APP-(SELECT AMT_EXE FROM @listTRADE_EXE WHERE [@listTRADE].TRADE_ID=[@listTRADE_EXE].TRADE_ID) WHERE EXISTS (SELECT TRADE_ID FROM @listTRADE_EXE WHERE [@listTRADE].TRADE_ID=[@listTRADE_EXE].TRADE_ID)
|
|
133
|
|
|
134
|
|
|
135
|
UPDATE dbo.PL_TRADEDETAIL SET AMT_ETM=AMT_ETM- (SELECT AMT_ROLEBACK FROM @listTRADE WHERE [@listTRADE].TRADE_ID=PL_TRADEDETAIL.TRADE_ID)
|
|
136
|
WHERE EXISTS (SELECT TRADE_ID FROM @listTRADE WHERE [@listTRADE].TRADE_ID=PL_TRADEDETAIL.TRADE_ID)
|
|
137
|
|
|
138
|
|
|
139
|
UPDATE dbo.PL_REQUEST_DOC SET IS_SETTLMENT=1 WHERE REQ_ID=@p_REQ_ID
|
|
140
|
|
|
141
|
|
|
142
|
COMMIT TRANSACTION
|
|
143
|
SELECT '0' as Result, @p_REQ_ID REQ_PAY_ID ,N'Quyết toán tờ trình thành công' ErrorDesc
|
|
144
|
RETURN '0'
|
|
145
|
ABORT:
|
|
146
|
BEGIN
|
|
147
|
ROLLBACK TRANSACTION
|
|
148
|
SELECT '-1' as Result, @p_REQ_ID AS REQ_PAY_ID, N'Quyết toán tờ trình thất bại' ErrorDesc
|
|
149
|
RETURN '-1'
|
|
150
|
END
|