Project

General

Profile

Download (13.1 KB)

IN PYCMS 21102020.txt

Luc Tran Van, 10/21/2020 03:17 PM

 
1 
??--SELECT * FROM TR_REQUEST_DOC WHERE REQ_CODE = 'PUR/2018/00007070'
2 
--SELECT * FROM dbo.TR_PO_MASTER WHERE REQ_DOC_ID = 'TRRDI0001056607'
3
4 
--EXEC TR_REQ_DOC_INS_TO_PO 'TRRDI0001056607'
5
6 
--SELECT * FROM TR_CONTRACT WHERE CONTRACT_ID = 'TRC000001126922'
7 
--SELECT * FROM TR_CONTRACT_DT WHERE CONTRACT_ID = 'TRC000001126922'
8 
--SELECT * FROM TR_CONTRACT_PAYMENT WHERE CONTRACT_ID = 'TRC000001126922'
9
10 
CREATE PROCEDURE [dbo].[rpt_TR_REQUEST_DOC_DT_ById]
11 
@P_REQ_ID varchar(15)
12 
AS
13
14 
DECLARE
15 
@DETAIL_ID VARCHAR(15),
16 
@l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,
17 
@l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0,
18 
@APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15),@APPR4 VARCHAR(15),
19 
@POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50),@POS4 NVARCHAR(50),
20 
@DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50),@DATE4 NVARCHAR(50),
21 
@FULLNAME NVARCHAR(100),@FULLNAME1 NVARCHAR(100),@FULLNAME2 NVARCHAR(100),@FULLNAME3 NVARCHAR(100),@FULLNAME4 NVARCHAR(100)
22
23 
SET @APPR =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
24 
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'
25 
WHERE A.REQ_ID= @P_REQ_ID )
26 
SET @FULLNAME = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR)
27 
SET @POS =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)
28 
SET @DATE =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
29 
FROM PL_PROCESS A
30 
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'
31 
WHERE A.REQ_ID= @P_REQ_ID
32 
ORDER BY A.APPROVE_DT DESC
33 
)
34
35 
SET @APPR1 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
36 
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'
37 
WHERE A.REQ_ID= @P_REQ_ID )
38
39 
SET @FULLNAME1 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR1)
40 
SET @POS1 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)
41 
SET @DATE1 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
42 
FROM PL_PROCESS A
43 
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'
44 
WHERE A.REQ_ID= @P_REQ_ID
45 
ORDER BY A.APPROVE_DT DESC)
46
47 
SET @APPR2 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
48 
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'
49 
WHERE A.REQ_ID= @P_REQ_ID)
50 
SET @FULLNAME2 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR2)
51 
SET @POS2 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)
52 
SET @DATE2 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
53 
FROM PL_PROCESS A
54 
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'
55 
WHERE A.REQ_ID= @P_REQ_ID
56 
ORDER BY A.APPROVE_DT DESC)
57
58 
SET @APPR3 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
59 
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'
60 
WHERE A.REQ_ID= @P_REQ_ID)
61
62 
SET @FULLNAME3 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR3)
63 
SET @POS3 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)
64 
SET @DATE3 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
65 
FROM PL_PROCESS A
66 
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'
67 
WHERE A.REQ_ID= @P_REQ_ID
68 
ORDER BY A.APPROVE_DT DESC)
69 
--
70 
SET @APPR4 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A
71 
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'
72 
WHERE A.REQ_ID= @P_REQ_ID)
73 
SET @FULLNAME4 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR4)
74 
SET @POS4 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR4)
75 
SET @DATE4 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')
76 
FROM PL_PROCESS A
77 
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'
78 
WHERE A.REQ_ID= @P_REQ_ID
79 
ORDER BY A.APPROVE_DT DESC)
80 
--
81 
SET @DETAIL_ID = (SELECT TOP 1 REQDT_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@P_REQ_ID)
82 
SELECT ROW_NUMBER() OVER (ORDER BY KQ.HH_NAME) AS STT, KQ.* FROM
83 
(
84 
SELECT TOP 1 HT.HH_TYPE_NAME,HH.HH_NAME,TRDT.DESCRIPTION,DT.UNIT_NAME,TRDT.QUANTITY,TRDT.CURRENCY,
85 
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,
86 
CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,
87 
@FULLNAME AS DEP_NAME1,
88 
@POS AS TP_NAME1,
89 
@DATE AS TP_DATE_SIGN1,
90
91 
@FULLNAME1 AS DEP_NAME2,
92 
@POS1 AS TP_NAME2,
93 
@DATE1 AS TP_DATE_SIGN2,
94
95 
@FULLNAME2 AS DEP_NAME3,
96 
@POS2 AS TP_NAME3,
97 
@DATE2 AS TP_DATE_SIGN3,
98
99 
@FULLNAME3 AS DEP_NAME4,
100 
@POS3 AS TP_NAME4,
101 
@DATE3 AS TP_DATE_SIGN4,
102
103 
@FULLNAME4 AS DEP_NAME12,
104 
@POS4 AS TP_NAME12,
105 
@DATE4 AS TP_DATE_SIGN12
106
107 
FROM dbo.TR_REQUEST_DOC_DT TRDT
108 
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID
109 
LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID
110 
LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID
111 
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID
112 
LEFT JOIN PL_REQUEST_DOC_DT DT ON TRDT.PL_REQDT_ID = DT.REQDT_ID
113 
WHERE REQ_DOC_ID=@P_REQ_ID
114 
UNION
115 
SELECT HT.HH_TYPE_NAME,HH.HH_NAME,TRDT.DESCRIPTION,DT.UNIT_NAME,TRDT.QUANTITY,TRDT.CURRENCY,
116 
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,
117 
CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,
118 
'' AS DEP_NAME1,
119 
'' AS TP_NAME1,
120 
'' AS TP_DATE_SIGN1,
121 
'' AS DEP_NAME2,
122 
'' AS TP_NAME2,
123 
'' AS TP_DATE_SIGN2,
124 
'' AS DEP_NAME3,
125 
'' AS TP_NAME3,
126 
'' AS TP_DATE_SIGN3,
127 
'' AS DEP_NAME4,
128 
'' AS TP_NAME4,
129 
'' AS TP_DATE_SIGN4,
130 
'' AS DEP_NAME12,
131 
'' AS TP_NAME12,
132 
'' AS TP_DATE_SIGN12
133 
FROM dbo.TR_REQUEST_DOC_DT TRDT
134 
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID
135 
LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID
136 
LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID
137 
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID
138 
LEFT JOIN PL_REQUEST_DOC_DT DT ON TRDT.PL_REQDT_ID = DT.REQDT_ID
139 
WHERE REQ_DOC_ID=@P_REQ_ID AND TRDT.REQDT_ID <> @DETAIL_ID
140 
)
141 
AS KQ
142