CREATE PROCEDURE [dbo].[rpt_TR_REQUEST_DOC_DT_ById]  
@P_REQ_ID varchar(15)  
AS  
 
DECLARE  
@DETAIL_ID VARCHAR(15),  
@l_QUANTITY numeric(18,0)=0, @l_QUANTITY_EXE decimal(18,0)=0, @l_QUANTITY_REMAIN decimal(18,0)=0,  
@l_AMT numeric(18,0)=0, @l_AMT_EXE decimal(18,0)=0, @l_AMT_REMAIN decimal(18,0)=0,  
@APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15),@APPR4 VARCHAR(15),  
@POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50),@POS4 NVARCHAR(50),  
@DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50),@DATE4 NVARCHAR(50),  
@FULLNAME NVARCHAR(100),@FULLNAME1 NVARCHAR(100),@FULLNAME2 NVARCHAR(100),@FULLNAME3 NVARCHAR(100),@FULLNAME4 NVARCHAR(100)  
 
SET @APPR =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'  
WHERE A.REQ_ID= @P_REQ_ID )  
SET @FULLNAME = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR)  
SET @POS =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)  
SET @DATE =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
FROM PL_PROCESS A  
INNER JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME AND A.PROCESS_ID='APPNEW'  
WHERE A.REQ_ID= @P_REQ_ID  
ORDER BY A.APPROVE_DT DESC  
)  
 
SET @APPR1 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
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'  
WHERE A.REQ_ID= @P_REQ_ID )  
 
SET @FULLNAME1 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR1)  
SET @POS1 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)  
SET @DATE1 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
FROM PL_PROCESS A  
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'  
WHERE A.REQ_ID= @P_REQ_ID  
ORDER BY A.APPROVE_DT DESC)  
 
SET @APPR2 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
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'  
WHERE A.REQ_ID= @P_REQ_ID)  
SET @FULLNAME2 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR2)  
SET @POS2 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)  
SET @DATE2 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
FROM PL_PROCESS A  
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'  
WHERE A.REQ_ID= @P_REQ_ID  
ORDER BY A.APPROVE_DT DESC)  
 
SET @APPR3 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
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'  
WHERE A.REQ_ID= @P_REQ_ID)  
 
SET @FULLNAME3 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR3)  
SET @POS3 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)  
SET @DATE3 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
FROM PL_PROCESS A  
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'  
WHERE A.REQ_ID= @P_REQ_ID  
ORDER BY A.APPROVE_DT DESC)  
--  
SET @APPR4 =(SELECT TOP 1 B.TLNANME FROM PL_PROCESS A  
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'  
WHERE A.REQ_ID= @P_REQ_ID)  
SET @FULLNAME4 = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = @APPR4)  
SET @POS4 =(SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR4)  
SET @DATE4 =(SELECT TOP 1 ISNULL( FORMAT(A.APPROVE_DT,'dd/MM/yyyy,HH:mm:ss'),'')  
FROM PL_PROCESS A  
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'  
WHERE A.REQ_ID= @P_REQ_ID  
ORDER BY A.APPROVE_DT DESC)  
--  
SET @DETAIL_ID = (SELECT TOP 1 REQDT_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@P_REQ_ID)  
SELECT ROW_NUMBER() OVER (ORDER BY KQ.HH_NAME) AS STT, KQ.* FROM  
(  
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,  
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,  
CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,  
@FULLNAME AS DEP_NAME1,  
@POS AS TP_NAME1,  
@DATE AS TP_DATE_SIGN1,  
 
@FULLNAME1 AS DEP_NAME2,  
@POS1 AS TP_NAME2,  
@DATE1 AS TP_DATE_SIGN2,  
 
@FULLNAME2 AS DEP_NAME3,  
@POS2 AS TP_NAME3,  
@DATE2 AS TP_DATE_SIGN3,  
 
@FULLNAME3 AS DEP_NAME4,  
@POS3 AS TP_NAME4,  
@DATE3 AS TP_DATE_SIGN4,  
 
@FULLNAME4 AS DEP_NAME12,  
@POS4 AS TP_NAME12,  
@DATE4 AS TP_DATE_SIGN12  
 
FROM dbo.TR_REQUEST_DOC_DT TRDT  
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID  
LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID  
LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID  
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID  
LEFT JOIN PL_REQUEST_DOC_DT DT ON TRDT.PL_REQDT_ID = DT.REQDT_ID  
WHERE REQ_DOC_ID=@P_REQ_ID  
UNION  
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,  
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,  
CS.SUP_NAME, (SELECT ISNULL(FORMAT(TRDT.REQ_DT,'dd/MM/yyyy'),'')) REQ_DT,  
'' AS DEP_NAME1,  
'' AS TP_NAME1,  
'' AS TP_DATE_SIGN1,  
'' AS DEP_NAME2,  
'' AS TP_NAME2,  
'' AS TP_DATE_SIGN2,  
'' AS DEP_NAME3,  
'' AS TP_NAME3,  
'' AS TP_DATE_SIGN3,  
'' AS DEP_NAME4,  
'' AS TP_NAME4,  
'' AS TP_DATE_SIGN4,  
'' AS DEP_NAME12,  
'' AS TP_NAME12,  
'' AS TP_DATE_SIGN12  
FROM dbo.TR_REQUEST_DOC_DT TRDT  
LEFT JOIN dbo.CM_HANGHOA HH ON HH.HH_ID=TRDT.HANGHOA_ID  
LEFT JOIN dbo.CM_HANGHOA_TYPE HT ON HT.HH_TYPE_ID=HH.HH_TYPE_ID  
LEFT JOIN dbo.CM_UNIT CU ON CU.UNIT_ID=HH.UNIT_ID  
LEFT JOIN dbo.CM_SUPPLIER CS ON CS.SUP_ID=TRDT.SUP_ID  
LEFT JOIN PL_REQUEST_DOC_DT DT ON TRDT.PL_REQDT_ID = DT.REQDT_ID  
WHERE REQ_DOC_ID=@P_REQ_ID AND TRDT.REQDT_ID <> @DETAIL_ID  
)  
AS KQ