|
1
|
ALTER PROCEDURE [dbo].[rpt_TR_ADVANCE_TEMP_PAYMENT_ByID]
|
|
2
|
@p_REQ_PAY_ID varchar(15)= NULL
|
|
3
|
AS
|
|
4
|
-------
|
|
5
|
DECLARE @REQ_TYPE VARCHAR(15)
|
|
6
|
SET @REQ_TYPE =(SELECT REQ_TYPE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID)
|
|
7
|
-------------------------------------------
|
|
8
|
DECLARE @CONTENT NVARCHAR(MAX)='', @CONTENT_CK NVARCHAR(MAX)='', @REQ_PAY_TYPE VARCHAR(5), @ACCNO VARCHAR(50), @ACCNAME NVARCHAR(500), @BANK NVARCHAR(500),
|
|
9
|
@ISUED_DT DATE,@REASON NVARCHAR(MAX), @DESC NVARCHAR(MAX),@NDTM NVARCHAR(MAX) ='', @NDCK NVARCHAR(MAX) =''
|
|
10
|
DECLARE CURS CURSOR FOR SELECT A.REQ_PAY_TYPE, A.ACC_NO,A.ACC_NAME,A.ISSUED_BY,A.ISSUED_DT,A.REQ_PAY_REASON,A.REQ_PAY_ENTRIES FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
11
|
DECLARE @NOIDUNG NVARCHAR(MAX) =''
|
|
12
|
OPEN CURS
|
|
13
|
FETCH NEXT FROM CURS INTO @REQ_PAY_TYPE, @ACCNO , @ACCNAME , @BANK , @ISUED_DT,@REASON,@DESC
|
|
14
|
WHILE @@FETCH_STATUS = 0
|
|
15
|
BEGIN
|
|
16
|
SET @NOIDUNG =@NOIDUNG + N'Nội dung thanh toán: '+ @REASON + ' ' + CHAR(13) + CHAR(10) + N'Chứng từ đính kèm: '+ @DESC+CHAR(13) + CHAR(10)
|
|
17
|
IF(@REQ_PAY_TYPE='1')
|
|
18
|
BEGIN
|
|
19
|
SET @CONTENT = @CONTENT + N'Người nhận ' +@ACCNAME + CHAR(13) + CHAR(10)+N'Số CMND '+ @ACCNO +N' được cấp bởi '+ @BANK + N' ngày cấp ' +CONVERT(VARCHAR,@ISUED_DT,103) + CHAR(13) + CHAR(10)
|
|
20
|
END
|
|
21
|
ELSE
|
|
22
|
BEGIN
|
|
23
|
SET @CONTENT_CK = @CONTENT_CK + N'Người nhận ' +@ACCNAME +CHAR(13) + CHAR(10) +N'Số tài khoản '+ @ACCNO +N' tại ngân hàng '+ @BANK + ' ' + CHAR(13) + CHAR(10)
|
|
24
|
END
|
|
25
|
FETCH NEXT FROM CURS INTO @REQ_PAY_TYPE, @ACCNO , @ACCNAME , @BANK , @ISUED_DT,@REASON,@DESC
|
|
26
|
END
|
|
27
|
CLOSE CURS
|
|
28
|
DEALLOCATE CURS
|
|
29
|
--- LAY NOI DUNG THANH TOAN NEU THANH TOAN CHO NHA CUNG CAP
|
|
30
|
DECLARE CURS_PO CURSOR FOR SELECT A.REQ_PAY_DESC,A.REQ_PAY_ENTRIES FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
31
|
DECLARE @NOIDUNG_PO NVARCHAR(MAX) =''
|
|
32
|
OPEN CURS_PO
|
|
33
|
FETCH NEXT FROM CURS_PO INTO @REASON,@DESC
|
|
34
|
WHILE @@FETCH_STATUS = 0
|
|
35
|
BEGIN
|
|
36
|
SET @NOIDUNG_PO =@NOIDUNG_PO + N'Nội dung : '+ @REASON + ' ' + CHAR(13) + CHAR(10) + N'Chứng từ đính kèm: '+CHAR(13) + CHAR(10)+ @DESC+CHAR(13) + CHAR(10)
|
|
37
|
FETCH NEXT FROM CURS_PO INTO @REASON,@DESC
|
|
38
|
END
|
|
39
|
CLOSE CURS_PO
|
|
40
|
DEALLOCATE CURS_PO
|
|
41
|
----
|
|
42
|
--IF (LEN(@CONTENT) >0)
|
|
43
|
--BEGIN
|
|
44
|
-- SET @NDTM = N'Tiền mặt: '+ CHAR(13) + CHAR(10) +LEFT(@CONTENT,LEN(@CONTENT)-2) +CHAR(13) + CHAR(10)
|
|
45
|
--END
|
|
46
|
--IF(LEN(@CONTENT_CK) >0)
|
|
47
|
--BEGIN
|
|
48
|
-- SET @NDCK= N'Chuyển khoản: '+ CHAR(13) + CHAR(10) + LEFT(@CONTENT_CK, LEN(@CONTENT_CK)-2) +CHAR(13) + CHAR(10)
|
|
49
|
--END
|
|
50
|
----
|
|
51
|
SELECT A.REQ_PAY_CODE, A.REQ_REASON,A.DEP_ID, ISNULL(FORMAT(A.CREATE_DT,'dd/MM/yyyy'),'') AS CREATE_DT, A.MAKER_ID_KT,A.CHECKER_ID,
|
|
52
|
ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy'),'') AS APPROVE_DT,A.CHECKER_ID_KT,MAKER_ID_KT,ISNULL(FORMAT(A.APPROVE_DT_KT,'dd/MM/yyyy'),'') AS APPROVE_DT_KT , ISNULL(FORMAT(A.CREATE_DT_KT,'dd/MM/yyyy'),'') AS CREATE_DT_KT,B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME,
|
|
53
|
ISNULL(TL7.TLFullName,TL14.TLFullName) APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT,
|
|
54
|
BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ,
|
|
55
|
--Luanlt--2019/10/15-Sửa AL,AL1
|
|
56
|
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,
|
|
57
|
DAY(A.CREATE_DT) D_, MONTH(A.CREATE_DT) M_, YEAR(A.CREATE_DT) Y_,
|
|
58
|
CASE
|
|
59
|
WHEN A.REQ_TYPE='I' THEN CAST(1 AS BIT)
|
|
60
|
WHEN A.REQ_TYPE <>'I' THEN CAST(0 AS BIT)
|
|
61
|
END AS IS_NB,
|
|
62
|
CASE
|
|
63
|
WHEN A.REQ_TYPE='P' THEN CAST(1 AS BIT)
|
|
64
|
WHEN A.REQ_TYPE <>'P' THEN CAST(0 AS BIT)
|
|
65
|
END AS IS_NCC,
|
|
66
|
CASE
|
|
67
|
WHEN A.REQ_TYPE='D' THEN CAST(1 AS BIT)
|
|
68
|
WHEN A.REQ_TYPE <>'D' THEN CAST(0 AS BIT)
|
|
69
|
END AS IS_XLN,
|
|
70
|
CASE WHEN LEN(@CONTENT) >0 THEN LEFT(@CONTENT,LEN(@CONTENT)-2) ELSE '' END AS TTTM,
|
|
71
|
CASE WHEN LEN(@CONTENT_CK) >0 THEN LEFT(@CONTENT_CK, LEN(@CONTENT_CK)-2) ELSE NULL END AS TTCK,
|
|
72
|
CASE WHEN LEN(@CONTENT) >0 THEN N'Tiền mặt:' ELSE '' END AS TM_TITLE,
|
|
73
|
CASE WHEN LEN(@CONTENT_CK) >0 THEN N'Chuyển khoản:' ELSE NULL END AS CK_TITLE,
|
|
74
|
--CASE WHEN @REQ_TYPE <>'P' THEN LEFT(@NOIDUNG, LEN(@NOIDUNG)-2) ELSE LEFT(@NOIDUNG_PO,LEN(@NOIDUNG_PO)-2) END AS NOIDUNG,
|
|
75
|
TL6.TLFullName AS DV_APPROVE_NAME,
|
|
76
|
ISNULL(TL7.TLFullName,TL11.TLFullName) AS GDDV_APP,
|
|
77
|
ISNULL(TL8.TLFullName,TL11.TLFullName) AS GDK_APP, ISNULL(TL9.TLFullName,TL12.TLFullName) AS PTGD_APP, ISNULL(TL10.TLFullName,TL13.TLFullName) AS TGD_APP,
|
|
78
|
ISNULL(TL15.TLFullName,TL16.TLFullName) AS HDQT,
|
|
79
|
ISNULL(A.REQ_AMT,0) AS SO_TIEN_PTTT,
|
|
80
|
CASE WHEN LEN(@NDTM + @NDCK) >0 THEN LEFT(@NDTM + @NDCK,LEN(@NDTM + @NDCK)-2) ELSE '' END AS PHUONG_THUC_THANH_TOAN, A.REQ_TYPE_CURRENCY AS TYPE_CUR,
|
|
81
|
ISNULL(P2.APPROVE_DT,P9.APPROVE_DT) AS DVKD_APP_DT
|
|
82
|
FROM TR_REQ_ADVANCE_PAYMENT A
|
|
83
|
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS
|
|
84
|
LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS
|
|
85
|
LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME
|
|
86
|
LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME
|
|
87
|
LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME
|
|
88
|
LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME
|
|
89
|
LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID
|
|
90
|
LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID
|
|
91
|
LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID
|
|
92
|
--Luanlt--2019/10/15-Sửa AL,AL1
|
|
93
|
LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE'
|
|
94
|
LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'
|
|
95
|
LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME
|
|
96
|
LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME
|
|
97
|
LEFT JOIN PL_PROCESS P1 ON A.REQ_PAY_ID = P1.REQ_ID AND P1.PROCESS_ID='0' AND A.BRANCH_ID <>'DV0001'
|
|
98
|
LEFT JOIN PL_PROCESS P2 ON A.REQ_PAY_ID = P2.REQ_ID AND P2.PROCESS_ID='1' AND A.BRANCH_ID <>'DV0001'
|
|
99
|
LEFT JOIN PL_PROCESS P3 ON A.REQ_PAY_ID = P3.REQ_ID AND P3.PROCESS_ID='2' AND A.BRANCH_ID <>'DV0001'
|
|
100
|
LEFT JOIN PL_PROCESS P4 ON A.REQ_PAY_ID = P4.REQ_ID AND P4.PROCESS_ID='3' AND A.BRANCH_ID <>'DV0001'
|
|
101
|
LEFT JOIN PL_PROCESS P5 ON A.REQ_PAY_ID = P5.REQ_ID AND P5.PROCESS_ID='4' AND A.BRANCH_ID <>'DV0001'
|
|
102
|
LEFT JOIN PL_PROCESS P10 ON A.REQ_PAY_ID = P10.REQ_ID AND P10.PROCESS_ID='5' AND A.BRANCH_ID <>'DV0001'
|
|
103
|
--- CAC BUOC DUYÊT CUA HOI SO
|
|
104
|
LEFT JOIN PL_PROCESS P6 ON A.REQ_PAY_ID = P6.REQ_ID AND P6.PROCESS_ID='1' AND A.BRANCH_ID = 'DV0001'
|
|
105
|
LEFT JOIN PL_PROCESS P7 ON A.REQ_PAY_ID = P7.REQ_ID AND P7.PROCESS_ID='2' AND A.BRANCH_ID = 'DV0001'
|
|
106
|
LEFT JOIN PL_PROCESS P8 ON A.REQ_PAY_ID = P8.REQ_ID AND P8.PROCESS_ID='3' AND A.BRANCH_ID = 'DV0001'
|
|
107
|
-- CAP PHE DUYET TRUNG GIAN
|
|
108
|
LEFT JOIN PL_PROCESS P9 ON A.REQ_PAY_ID = P9.REQ_ID AND P9.PROCESS_ID='0' AND A.BRANCH_ID = 'DV0001'
|
|
109
|
LEFT JOIN PL_PROCESS P11 ON A.REQ_PAY_ID = P11.REQ_ID AND P11.PROCESS_ID='4' AND A.BRANCH_ID = 'DV0001'
|
|
110
|
---
|
|
111
|
LEFT JOIN TL_USER TL6 ON P1.CHECKER_ID= TL6.TLNANME
|
|
112
|
LEFT JOIN TL_USER TL7 ON P2.CHECKER_ID= TL7.TLNANME
|
|
113
|
LEFT JOIN TL_USER TL8 ON P3.CHECKER_ID= TL8.TLNANME
|
|
114
|
LEFT JOIN TL_USER TL9 ON P4.CHECKER_ID= TL9.TLNANME
|
|
115
|
LEFT JOIN TL_USER TL10 ON P5.CHECKER_ID= TL10.TLNANME
|
|
116
|
LEFT JOIN TL_USER TL15 ON P10.CHECKER_ID = TL15.TLNANME
|
|
117
|
--- LAY THONG TIN CAC USER DUYET CUA HOI SO
|
|
118
|
LEFT JOIN TL_USER TL11 ON P6.CHECKER_ID= TL11.TLNANME
|
|
119
|
LEFT JOIN TL_USER TL12 ON P7.CHECKER_ID= TL12.TLNANME
|
|
120
|
LEFT JOIN TL_USER TL13 ON P8.CHECKER_ID= TL13.TLNANME
|
|
121
|
LEFT JOIN TL_USER TL14 ON P9.CHECKER_ID = TL14.TLNANME
|
|
122
|
LEFT JOIN TL_USER TL16 ON P11.CHECKER_ID = TL16.TLNANME
|
|
123
|
WHERE 1=1
|
|
124
|
AND(A.REQ_PAY_ID=@p_REQ_PAY_ID)
|
|
125
|
ORDER BY A.CREATE_DT DESC;
|