|
1
|
ALTER PROCEDURE [dbo].[rpt_TR_REQUEST_DOC_DT_ById]
|
|
2
|
@P_REQ_ID varchar(15)
|
|
3
|
AS
|
|
4
|
|
|
5
|
DECLARE
|
|
6
|
@DETAIL_ID VARCHAR(15),
|
|
7
|
@l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,
|
|
8
|
@l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0,
|
|
9
|
@APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15),@APPR4 VARCHAR(15),
|
|
10
|
@POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50),@POS4 NVARCHAR(50),
|
|
11
|
@DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50),@DATE4 NVARCHAR(50),
|
|
12
|
@FULLNAME NVARCHAR(100),@FULLNAME1 NVARCHAR(100),@FULLNAME2 NVARCHAR(100),@FULLNAME3 NVARCHAR(100),@FULLNAME4 NVARCHAR(100)
|
|
13
|
|
|
14
|
SET @APPR =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
|
|
15
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'
|
|
16
|
WHERE A.REQ_ID= @P_REQ_ID )
|
|
17
|
SET @FULLNAME = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR)
|
|
18
|
SET @POS =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)
|
|
19
|
SET @DATE =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
|
|
20
|
FROM PL_PROCESS A
|
|
21
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'
|
|
22
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
23
|
ORDER BY A.APPROVE_DT DESC
|
|
24
|
)
|
|
25
|
|
|
26
|
SET @APPR1 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
|
|
27
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.NOTES =N'Nhân viên xử lý gửi phê duyệt'
|
|
28
|
WHERE A.REQ_ID= @P_REQ_ID )
|
|
29
|
|
|
30
|
SET @FULLNAME1 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR1)
|
|
31
|
SET @POS1 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)
|
|
32
|
SET @DATE1 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
|
|
33
|
FROM PL_PROCESS A
|
|
34
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.NOTES =N'Nhân viên xử lý gửi phê duyệt'
|
|
35
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
36
|
ORDER BY A.APPROVE_DT DESC)
|
|
37
|
|
|
38
|
SET @APPR2 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
|
|
39
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='DMMS' AND A.NOTES =N'Kiểm soát viên đã phê duyệt'
|
|
40
|
WHERE A.REQ_ID= @P_REQ_ID)
|
|
41
|
SET @FULLNAME2 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR2)
|
|
42
|
SET @POS2 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)
|
|
43
|
SET @DATE2 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
|
|
44
|
FROM PL_PROCESS A
|
|
45
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='DMMS' AND A.NOTES = N'Kiểm soát viên đã phê duyệt'
|
|
46
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
47
|
ORDER BY A.APPROVE_DT DESC)
|
|
48
|
|
|
49
|
SET @APPR3 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
|
|
50
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='DMMS' AND A.NOTES = N'Trưởng đơn vị đã phê duyệt'
|
|
51
|
WHERE A.REQ_ID= @P_REQ_ID)
|
|
52
|
|
|
53
|
SET @FULLNAME3 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR3)
|
|
54
|
SET @POS3 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)
|
|
55
|
SET @DATE3 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
|
|
56
|
FROM PL_PROCESS A
|
|
57
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.NOTES = N'Trưởng đơn vị đã phê duyệt' AND A.PROCESS_ID ='DMMS'
|
|
58
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
59
|
ORDER BY A.APPROVE_DT DESC)
|
|
60
|
--
|
|
61
|
SET @APPR4 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
|
|
62
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='GDK_PYC' AND A.NOTES = N'Giám đốc khối đã phê duyệt'
|
|
63
|
WHERE A.REQ_ID= @P_REQ_ID)
|
|
64
|
SET @FULLNAME4 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR4)
|
|
65
|
SET @POS4 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR4)
|
|
66
|
SET @DATE4 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
|
|
67
|
FROM PL_PROCESS A
|
|
68
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.NOTES = N'Giám đốc khối đã phê duyệt' AND A.PROCESS_ID ='GDK_PYC'
|
|
69
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
70
|
ORDER BY A.APPROVE_DT DESC)
|
|
71
|
--
|
|
72
|
SET @DETAIL_ID = (SELECT TOP 1 REQDT_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@P_REQ_ID)
|
|
73
|
SELECT ROW_NUMBER() OVER (ORDER BY KQ.HH_NAME DESC) AS STT, KQ.* FROM
|
|
74
|
(
|
|
75
|
SELECT TOP 1 HT.HH_TYPE_NAME,HH.HH_NAME,TRDT.DESCRIPTION,CASE WHEN TRDT.NOTES IS NOT NULL AND TRDT.NOTES <> '' AND LEN(TRDT.NOTES) <10 THEN TRDT.NOTES ELSE DT.UNIT_NAME END AS UNIT_NAME,TRDT.QUANTITY,TRDT.CURRENCY,
|
|
76
|
ISNULL(TRDT.PRICE_ETM,0) AS PRICE_ETM,ISNULL(TRDT.TOTAL_AMT_ETM,0) AS TOTAL_AMT_ETM,ISNULL(TRDT.PRICE,0) AS PRICE,ISNULL(TRDT.TOTAL_AMT,0) AS TOTAL_AMT,
|
|
77
|
CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,
|
|
78
|
@FULLNAME AS DEP_NAME1,
|
|
79
|
@POS AS TP_NAME1,
|
|
80
|
@DATE AS TP_DATE_SIGN1,
|
|
81
|
|
|
82
|
@FULLNAME1 AS DEP_NAME2,
|
|
83
|
@POS1 AS TP_NAME2,
|
|
84
|
@DATE1 AS TP_DATE_SIGN2,
|
|
85
|
|
|
86
|
@FULLNAME2 AS DEP_NAME3,
|
|
87
|
@POS2 AS TP_NAME3,
|
|
88
|
@DATE2 AS TP_DATE_SIGN3,
|
|
89
|
|
|
90
|
@FULLNAME3 AS DEP_NAME4,
|
|
91
|
@POS3 AS TP_NAME4,
|
|
92
|
@DATE3 AS TP_DATE_SIGN4,
|
|
93
|
|
|
94
|
@FULLNAME4 AS DEP_NAME12,
|
|
95
|
@POS4 AS TP_NAME12,
|
|
96
|
@DATE4 AS TP_DATE_SIGN12
|
|
97
|
|
|
98
|
FROM dbo.TR_REQUEST_DOC_DT TRDT
|
|
99
|
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID
|
|
100
|
LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID
|
|
101
|
LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID
|
|
102
|
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID
|
|
103
|
LEFT JOIN PL_REQUEST_DOC_DT DT ON TRDT.PL_REQDT_ID = DT.REQDT_ID
|
|
104
|
WHERE REQ_DOC_ID=@P_REQ_ID
|
|
105
|
UNION
|
|
106
|
SELECT HT.HH_TYPE_NAME,HH.HH_NAME,TRDT.DESCRIPTION,CASE WHEN TRDT.NOTES IS NOT NULL AND TRDT.NOTES <> '' AND LEN(TRDT.NOTES) <10 THEN TRDT.NOTES ELSE DT.UNIT_NAME END AS UNIT_NAME,TRDT.QUANTITY,TRDT.CURRENCY,
|
|
107
|
ISNULL(TRDT.PRICE_ETM,0) AS PRICE_ETM,ISNULL(TRDT.TOTAL_AMT_ETM,0) AS TOTAL_AMT_ETM,ISNULL(TRDT.PRICE,0) AS PRICE,ISNULL(TRDT.TOTAL_AMT,0) AS TOTAL_AMT,
|
|
108
|
CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,
|
|
109
|
'' AS DEP_NAME1,
|
|
110
|
'' AS TP_NAME1,
|
|
111
|
'' AS TP_DATE_SIGN1,
|
|
112
|
'' AS DEP_NAME2,
|
|
113
|
'' AS TP_NAME2,
|
|
114
|
'' AS TP_DATE_SIGN2,
|
|
115
|
'' AS DEP_NAME3,
|
|
116
|
'' AS TP_NAME3,
|
|
117
|
'' AS TP_DATE_SIGN3,
|
|
118
|
'' AS DEP_NAME4,
|
|
119
|
'' AS TP_NAME4,
|
|
120
|
'' AS TP_DATE_SIGN4,
|
|
121
|
'' AS DEP_NAME12,
|
|
122
|
'' AS TP_NAME12,
|
|
123
|
'' AS TP_DATE_SIGN12
|
|
124
|
FROM dbo.TR_REQUEST_DOC_DT TRDT
|
|
125
|
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID
|
|
126
|
LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID
|
|
127
|
LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID
|
|
128
|
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID
|
|
129
|
LEFT JOIN PL_REQUEST_DOC_DT DT ON TRDT.PL_REQDT_ID = DT.REQDT_ID
|
|
130
|
WHERE REQ_DOC_ID=@P_REQ_ID AND TRDT.REQDT_ID <> @DETAIL_ID
|
|
131
|
|
|
132
|
UNION
|
|
133
|
SELECT N'Tổng cộng' AS HH_TYPE_NAME,''AS HH_NAME,'' AS DESCRIPTION,'' AS UNIT_NAME,NULL AS QUANTITY,'' AS CURRENCY,
|
|
134
|
(SELECT SUM(ISNULL(PRICE_ETM,0)) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@P_REQ_ID) AS PRICE_ETM,
|
|
135
|
(SELECT SUM(ISNULL(TOTAL_AMT_ETM,0)) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@P_REQ_ID) AS TOTAL_AMT_ETM,
|
|
136
|
(SELECT SUM(ISNULL(PRICE,0)) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@P_REQ_ID) AS PRICE,
|
|
137
|
(SELECT SUM(ISNULL(TOTAL_AMT,0)) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@P_REQ_ID) AS TOTAL_AMT,
|
|
138
|
'' AS SUP_NAME, '' REQ_DT,
|
|
139
|
'' AS DEP_NAME1,
|
|
140
|
'' AS TP_NAME1,
|
|
141
|
'' AS TP_DATE_SIGN1,
|
|
142
|
|
|
143
|
'' AS DEP_NAME2,
|
|
144
|
'' AS TP_NAME2,
|
|
145
|
'' AS TP_DATE_SIGN2,
|
|
146
|
|
|
147
|
'' AS DEP_NAME3,
|
|
148
|
'' AS TP_NAME3,
|
|
149
|
'' AS TP_DATE_SIGN3,
|
|
150
|
|
|
151
|
'' AS DEP_NAME4,
|
|
152
|
'' AS TP_NAME4,
|
|
153
|
'' AS TP_DATE_SIGN4,
|
|
154
|
|
|
155
|
'' AS DEP_NAME12,
|
|
156
|
'' AS TP_NAME12,
|
|
157
|
'' AS TP_DATE_SIGN12
|
|
158
|
)
|
|
159
|
AS KQ
|