|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[rpt_TR_REQUEST_DOC_DT_ById]
|
|
3
|
@P_REQ_ID varchar(15)
|
|
4
|
AS
|
|
5
|
|
|
6
|
DECLARE
|
|
7
|
@DETAIL_ID VARCHAR(15),
|
|
8
|
@l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,
|
|
9
|
@l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0,
|
|
10
|
@APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15),
|
|
11
|
@POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50),
|
|
12
|
@DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50),
|
|
13
|
@FULLNAME NVARCHAR(100),@FULLNAME1 NVARCHAR(100),@FULLNAME2 NVARCHAR(100),@FULLNAME3 NVARCHAR(100)
|
|
14
|
|
|
15
|
SET @APPR =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
|
|
16
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'
|
|
17
|
WHERE A.REQ_ID= @P_REQ_ID )
|
|
18
|
SET @FULLNAME = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR)
|
|
19
|
SET @POS =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)
|
|
20
|
SET @DATE =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
|
|
21
|
FROM PL_PROCESS A
|
|
22
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'
|
|
23
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
24
|
ORDER BY A.APPROVE_DT DESC
|
|
25
|
)
|
|
26
|
|
|
27
|
SET @APPR1 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
|
|
28
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='NVMS'
|
|
29
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
30
|
AND NOT EXISTS(SELECT MAKER_ID FROM TR_REQUEST_DOC WHERE REQ_ID= @P_REQ_ID AND MAKER_ID = A.CHECKER_ID))
|
|
31
|
SET @FULLNAME1 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR1)
|
|
32
|
SET @POS1 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)
|
|
33
|
SET @DATE1 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
|
|
34
|
FROM PL_PROCESS A
|
|
35
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND B.RoleName ='NVMS'
|
|
36
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
37
|
ORDER BY A.APPROVE_DT DESC)
|
|
38
|
|
|
39
|
SET @APPR2 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
|
|
40
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='DMMS' AND B.RoleName ='TBP'
|
|
41
|
WHERE A.REQ_ID= @P_REQ_ID)
|
|
42
|
SET @FULLNAME2 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR2)
|
|
43
|
SET @POS2 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)
|
|
44
|
SET @DATE2 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
|
|
45
|
FROM PL_PROCESS A
|
|
46
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='DMMS' AND B.RoleName ='TBP'
|
|
47
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
48
|
ORDER BY A.APPROVE_DT DESC)
|
|
49
|
|
|
50
|
SET @APPR3 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
|
|
51
|
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID ='DMMS' AND B.RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW ='GDDV')
|
|
52
|
WHERE A.REQ_ID= @P_REQ_ID)
|
|
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 B.RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW ='GDDV') AND A.PROCESS_ID ='DMMS'
|
|
58
|
WHERE A.REQ_ID= @P_REQ_ID
|
|
59
|
ORDER BY A.APPROVE_DT DESC)
|
|
60
|
SET @DETAIL_ID = (SELECT TOP 1 REQDT_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@P_REQ_ID)
|
|
61
|
SELECT ROW_NUMBER() OVER (ORDER BY KQ.HH_NAME) AS STT, KQ.* FROM
|
|
62
|
(
|
|
63
|
SELECT TOP 1 HT.HH_TYPE_NAME,HH.HH_NAME,TRDT.DESCRIPTION,CU.UNIT_NAME,TRDT.QUANTITY,TRDT.CURRENCY,
|
|
64
|
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,
|
|
65
|
CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,
|
|
66
|
@FULLNAME AS DEP_NAME1,
|
|
67
|
@POS AS TP_NAME1,
|
|
68
|
@DATE AS TP_DATE_SIGN1,
|
|
69
|
@FULLNAME1 AS DEP_NAME2,
|
|
70
|
@POS1 AS TP_NAME2,
|
|
71
|
@DATE1 AS TP_DATE_SIGN2,
|
|
72
|
@FULLNAME2 AS DEP_NAME3,
|
|
73
|
@POS2 AS TP_NAME3,
|
|
74
|
@DATE2 AS TP_DATE_SIGN3,
|
|
75
|
@FULLNAME3 AS DEP_NAME4,
|
|
76
|
@POS3 AS TP_NAME4,
|
|
77
|
@DATE3 AS TP_DATE_SIGN4
|
|
78
|
FROM dbo.TR_REQUEST_DOC_DT TRDT
|
|
79
|
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID
|
|
80
|
LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID
|
|
81
|
LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID
|
|
82
|
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID
|
|
83
|
WHERE REQ_DOC_ID=@P_REQ_ID
|
|
84
|
UNION
|
|
85
|
SELECT HT.HH_TYPE_NAME,HH.HH_NAME,TRDT.DESCRIPTION,CU.UNIT_NAME,TRDT.QUANTITY,TRDT.CURRENCY,
|
|
86
|
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,
|
|
87
|
CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,
|
|
88
|
'' AS DEP_NAME1,
|
|
89
|
'' AS TP_NAME1,
|
|
90
|
'' AS TP_DATE_SIGN1,
|
|
91
|
'' AS DEP_NAME2,
|
|
92
|
'' AS TP_NAME2,
|
|
93
|
'' AS TP_DATE_SIGN2,
|
|
94
|
'' AS DEP_NAME3,
|
|
95
|
'' AS TP_NAME3,
|
|
96
|
'' AS TP_DATE_SIGN3,
|
|
97
|
'' AS DEP_NAME4,
|
|
98
|
'' AS TP_NAME4,
|
|
99
|
'' AS TP_DATE_SIGN4
|
|
100
|
FROM dbo.TR_REQUEST_DOC_DT TRDT
|
|
101
|
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID
|
|
102
|
LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID
|
|
103
|
LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID
|
|
104
|
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID
|
|
105
|
WHERE REQ_DOC_ID=@P_REQ_ID AND REQDT_ID <> @DETAIL_ID
|
|
106
|
)
|
|
107
|
AS KQ
|