|
1
|
ALTER PROCEDURE [dbo].[rpt_TR_REQ_PAYMENT_ByID]
|
|
2
|
@p_REQ_PAY_ID varchar(15)= NULL
|
|
3
|
AS
|
|
4
|
-------
|
|
5
|
DECLARE @TITLE_APPROVE NVARCHAR(50) ='', @TITLE_APPROVE_KSV NVARCHAR(50) =''
|
|
6
|
IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT='S'))
|
|
7
|
BEGIN
|
|
8
|
SET @TITLE_APPROVE =N'Đã từ chối '
|
|
9
|
END
|
|
10
|
ELSE
|
|
11
|
BEGIN
|
|
12
|
SET @TITLE_APPROVE =N'Đã chấp thuận '
|
|
13
|
END
|
|
14
|
----------------
|
|
15
|
IF(EXISTS(SELECT * FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT='R'))
|
|
16
|
BEGIN
|
|
17
|
SET @TITLE_APPROVE_KSV =N'Đã từ chối '
|
|
18
|
END
|
|
19
|
ELSE
|
|
20
|
BEGIN
|
|
21
|
SET @TITLE_APPROVE_KSV =N'Đã chấp thuận '
|
|
22
|
END
|
|
23
|
----------------
|
|
24
|
DECLARE @REQ_TYPE VARCHAR(15)
|
|
25
|
SET @REQ_TYPE =(SELECT REQ_TYPE FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
|
|
26
|
-------------------------------------------
|
|
27
|
DECLARE @CONTENT NVARCHAR(MAX)='', @CONTENT_CK NVARCHAR(MAX)='', @TM_TITLE NVARCHAR(100), @CK_TITLE NVARCHAR(100), @NDTM NVARCHAR(MAX) ='', @NDCK NVARCHAR(MAX) ='',
|
|
28
|
@REQ_PAY_TYPE VARCHAR(5), @ACCNO VARCHAR(50), @ACCNAME NVARCHAR(500), @BANK NVARCHAR(500), @ISUED_DT DATE,
|
|
29
|
@REASON NVARCHAR(MAX), @DESC NVARCHAR(MAX),@NOIDUNG NVARCHAR(MAX) ='',@AMT DECIMAL(18,2),@TYPE_TRANSFER VARCHAR(15)
|
|
30
|
SELECT A.REQ_PAY_CODE, A.REQ_REASON,A.DEP_ID,N'Đã chấp thuận '+ISNULL(FORMAT(A.CREATE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') AS CREATE_DT, A.MAKER_ID_KT,A.CHECKER_ID,
|
|
31
|
CASE WHEN ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,,hh:mm:ss tt'),'') <>'' THEN N'Đã chấp thuận ' +ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS APPROVE_DT,
|
|
32
|
A.CHECKER_ID_KT,MAKER_ID_KT,CASE WHEN ISNULL(FORMAT(A.APPROVE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') <>'' THEN @TITLE_APPROVE_KSV + ISNULL(FORMAT(A.APPROVE_DT_KT,'dd/MM/yyyy,,hh:mm:ss tt'),'')
|
|
33
|
ELSE '' END AS APPROVE_DT_KT ,
|
|
34
|
CASE WHEN ISNULL(FORMAT(A.CREATE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') <>'' THEN @TITLE_APPROVE + ISNULL(FORMAT(A.CREATE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS CREATE_DT_KT,
|
|
35
|
B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
|
|
36
|
TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
|
|
37
|
BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
|
|
38
|
--Luanlt--2019/10/15-Sửa AL,AL1
|
|
39
|
BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, 0 TOTAL_AMT_TEMP, 0 TOTAL_AMT_PAY_HIS,TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,
|
|
40
|
CASE WHEN @REQ_TYPE <> 'I' THEN (ISNULL(X2.SO_TIEN_TAM_UNG,0) + ISNULL(X3.SO_TIEN_TAM_UNG,0)) ELSE ISNULL(X.SOTIEN_TAMUNG,0) END AS SOTIEN_TAMUNG,
|
|
41
|
CASE WHEN @REQ_TYPE <> 'I' THEN (ISNULL(X4.SO_TIEN_TAM_UNG,0.00) + ISNULL(X5.SO_TIEN_TAM_UNG,0.00) +ISNULL(X6.SO_TIEN_TAM_UNG,0.00)) ELSE ISNULL(X.AMT_USE,.00) END AS SO_TIEN_DE_NGHI,
|
|
42
|
--CASE WHEN (ISNULL(X.SOTIEN_TAMUNG,0.00) - ISNULL(X.AMT_USE,0.00)) >0 THEN (ISNULL(X.SOTIEN_TAMUNG,0.00) - ISNULL(X.AMT_USE,0.00)) ELSE 0 END AS SO_TIEN_CL,
|
|
43
|
CASE WHEN @REQ_TYPE <> 'I' THEN (ISNULL(X2.SO_TIEN_TAM_UNG,0) + ISNULL(X3.SO_TIEN_TAM_UNG,0)) - (ISNULL(X4.SO_TIEN_TAM_UNG,0.00) + ISNULL(X5.SO_TIEN_TAM_UNG,0.00) +ISNULL(X6.SO_TIEN_TAM_UNG,0.00))
|
|
44
|
ELSE ISNULL(X.SOTIEN_TAMUNG,0) - ISNULL(X.AMT_USE,.00) END AS SO_TIEN_CL,
|
|
45
|
--ISNULL(X.ST_BO_SUNG,0) SO_TIEN_VUOT,
|
|
46
|
CASE WHEN @REQ_TYPE <> 'I' THEN (ISNULL(X4.SO_TIEN_TAM_UNG,0.00) + ISNULL(X5.SO_TIEN_TAM_UNG,0.00) +ISNULL(X6.SO_TIEN_TAM_UNG,0.00)) -(ISNULL(X2.SO_TIEN_TAM_UNG,0) + ISNULL(X3.SO_TIEN_TAM_UNG,0)) ELSE
|
|
47
|
ISNULL(X.AMT_USE,.00) -ISNULL(X.SOTIEN_TAMUNG,0) END AS SO_TIEN_VUOT,
|
|
48
|
--ISNULL(X.ST_BO_SUNG,0) + ISNULL(X.ST_HOAN_UNG,0) CHI_BO_SUNG,
|
|
49
|
CASE WHEN @REQ_TYPE <> 'I' THEN (ISNULL(X4.SO_TIEN_TAM_UNG,0.00) + ISNULL(X5.SO_TIEN_TAM_UNG,0.00) +ISNULL(X6.SO_TIEN_TAM_UNG,0.00)) -(ISNULL(X2.SO_TIEN_TAM_UNG,0) + ISNULL(X3.SO_TIEN_TAM_UNG,0)) ELSE
|
|
50
|
ISNULL(X.AMT_USE,.00) -ISNULL(X.SOTIEN_TAMUNG,0) END AS CHI_BO_SUNG,
|
|
51
|
DAY(A.CREATE_DT) D_, MONTH(A.CREATE_DT) M_, YEAR(A.CREATE_DT) Y_,
|
|
52
|
'' AS NOIDUNG,
|
|
53
|
'' AS PHUONG_THUC_THANH_TOAN, CASE WHEN EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND CURRENCY <>'VND')
|
|
54
|
THEN (SELECT TOP 1 CURRENCY FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) ELSE'VND' END TYPE_CUR,
|
|
55
|
ISNULL(X.ST_HOAN_UNG,0.00) + ISNULL(X.ST_BO_SUNG,0.00) AS SO_TIEN_PTTT, CASE WHEN A.CONFIRM_NOTE <>'' AND A.CONFIRM_NOTE IS NOT NULL THEN N'Ghi chú: '+ A.CONFIRM_NOTE ELSE '' END AS CONFIRM_NOTE
|
|
56
|
FROM TR_REQ_PAYMENT A
|
|
57
|
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
|
|
58
|
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
|
|
59
|
LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
|
|
60
|
LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
|
|
61
|
LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
|
|
62
|
LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
|
|
63
|
LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
|
|
64
|
LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
|
|
65
|
LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
|
|
66
|
--Luanlt--2019/10/15-Sửa AL,AL1
|
|
67
|
LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='PAY_TYPE'
|
|
68
|
LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'
|
|
69
|
LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
|
|
70
|
LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
|
|
71
|
LEFT JOIN
|
|
72
|
(
|
|
73
|
SELECT A.PAY_ID, SUM(A.AMT_REMAIN) SOTIEN_TAMUNG,
|
|
74
|
SUM(A.AMT_DO) ST_DA_HOAN,
|
|
75
|
SUM(A.AMT_ADD) ST_BO_SUNG,SUM(A.AMT_USE) AS AMT_USE, SUM(A.AMT_REVERT) AS ST_HOAN_UNG FROM TR_REQ_PAYMENT_DT A
|
|
76
|
GROUP BY A.PAY_ID
|
|
77
|
) X ON A.REQ_PAY_ID = X.PAY_ID
|
|
78
|
LEFT JOIN
|
|
79
|
(
|
|
80
|
SELECT P.REQ_PAY_ID,SUM(P.AMT_PAY) AS SO_TIEN_TAM_UNG FROM TR_REQ_PAY_PERIOD P
|
|
81
|
WHERE P.REQ_PAY_ID=@p_REQ_PAY_ID AND P.PROCESS <>'1'
|
|
82
|
GROUP BY P.REQ_PAY_ID
|
|
83
|
)
|
|
84
|
X2 ON A.REQ_PAY_ID = X2.REQ_PAY_ID
|
|
85
|
LEFT JOIN
|
|
86
|
(
|
|
87
|
SELECT P.REQ_PAY_ID,SUM(P.AMT_PAY_REAL) AS SO_TIEN_TAM_UNG FROM TR_REQ_PAY_SCHEDULE P
|
|
88
|
WHERE P.REQ_PAY_ID=@p_REQ_PAY_ID AND P.PROCESS <>'1'
|
|
89
|
GROUP BY P.REQ_PAY_ID
|
|
90
|
)
|
|
91
|
X3 ON A.REQ_PAY_ID = X3.REQ_PAY_ID
|
|
92
|
|
|
93
|
LEFT JOIN
|
|
94
|
(
|
|
95
|
SELECT P.REQ_PAY_ID,SUM(P.AMT_PAY) AS SO_TIEN_TAM_UNG FROM TR_REQ_PAY_PERIOD P
|
|
96
|
WHERE P.REQ_PAY_ID=@p_REQ_PAY_ID
|
|
97
|
GROUP BY P.REQ_PAY_ID
|
|
98
|
)
|
|
99
|
X4 ON A.REQ_PAY_ID = X4.REQ_PAY_ID
|
|
100
|
LEFT JOIN
|
|
101
|
(
|
|
102
|
SELECT P.REQ_PAY_ID,SUM(P.AMT_PAY_REAL) AS SO_TIEN_TAM_UNG FROM TR_REQ_PAY_SCHEDULE P
|
|
103
|
WHERE P.REQ_PAY_ID=@p_REQ_PAY_ID
|
|
104
|
GROUP BY P.REQ_PAY_ID
|
|
105
|
)
|
|
106
|
X5 ON A.REQ_PAY_ID = X5.REQ_PAY_ID
|
|
107
|
LEFT JOIN
|
|
108
|
(
|
|
109
|
SELECT P.REQ_PAY_ID,SUM(P.TOTAL_AMT) AS SO_TIEN_TAM_UNG FROM TR_REQ_PAY_SERVICE P
|
|
110
|
WHERE P.REQ_PAY_ID=@p_REQ_PAY_ID
|
|
111
|
GROUP BY P.REQ_PAY_ID
|
|
112
|
)
|
|
113
|
X6 ON A.REQ_PAY_ID = X6.REQ_PAY_ID
|
|
114
|
WHERE 1=1
|
|
115
|
AND(A.REQ_PAY_ID=@p_REQ_PAY_ID)
|
|
116
|
ORDER BY A.CREATE_DT DESC;
|