ALTER PROCEDURE dbo.PL_REQUEST_DOC_ById
@p_REQ_ID	varchar(15)  = NULL,
@p_USER_LOGIN VARCHAR(50) = NULL
AS
BEGIN
	SELECT A.REQ_ID,A.REQ_CODE,A.REQ_NAME,A.REQ_DT,A.REQ_TYPE,A.REQ_CONTENT, A.REQ_REASON,A.BRANCH_ID,
             A.TOTAL_AMT AS TOTAL_AMT, A.RECORD_STATUS,A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,A.IS_AUTHORITY,
         CASE WHEN A.BRANCH_FEE IS NULL OR A.BRANCH_FEE = '' THEN A.NOTES
              WHEN A.BRANCH_FEE = 'DV0001' THEN DF.DEP_NAME + ' - ' + BF.BRANCH_NAME
              WHEN A.BRANCH_FEE <> 'DV0001' THEN BF.BRANCH_NAME END AS NOTES,
			   UDV.TLFullName AS CHECKER_NAME_DV,
			  
			   A.APPROVE_DT,
               A.PROCESS_ID,
			   D.AUTH_STATUS_NAME AS AUTH_STATUS_NAME_DV,
			   CASE WHEN A.PROCESS_ID='APPROVE' THEN N'Đã duyệt'
			   ELSE N'Chờ duyệt' END AS AUTH_STATUS_NAME,
			   G.BRANCH_CODE,
			   CASE WHEN A.BRANCH_CREATE <> 'DV0001' THEN G.BRANCH_NAME ELSE DEP.DEP_NAME + ' - ' + G.BRANCH_NAME END AS BRANCH_NAME,
			   UP.TLFullName AS CHECKER_NAME_PROCESS,
			   RP.APPROVE_DT AS APPROVE_DT_PROCESS,
			   UC.TLFullName AS MAKER_NAME,
			   RPN.PROCESS_ID AS PROCESS_ID_NEXT,
			   RPN.ROLE_USER, 
			   RP.NOTES AS PROCESS_STATUS , 
			   RPN.NOTES AS PROCESS_STATUS_NEXT,
			   A.DVDM_APP_ID,
			   CD.DVDM_NAME AS DVDM_APP_NAME,
			   A.REQ_PARENT_ID,
			   PARENT.REQ_NAME AS REQ_PARENT_NAME,
			   PARENT.REQ_CODE AS REQ_PARENT_CODE,
			   PARENT.TOTAL_AMT AS REQ_PARENT_AMT,
			   CC.REQ_NAME AS PL_BASED_NAME,
			   CC.REQ_CODE AS PL_BASED_CODE,
			   CC.TOTAL_AMT AS PL_BASED_AMT,
			   A.BRANCH_FEE,
			   A.DEP_ID,
			   A.DEP_FEE,
			   DEP.DEP_NAME,
			   DEP.DEP_CODE,
			   BF.BRANCH_NAME AS BRANCH_FEE_NAME,
			   BF.BRANCH_CODE AS BRANCH_FEE_CODE,
			   DF.DEP_NAME AS DEP_FEE_NAME,
			   DF.DEP_CODE AS DEP_FEE_CODE,
			   '' AS BRANCH_DEP,
			   '' AS BRANCH_DEP_FEE,
			   


			   RPC.TYPE_JOB AS TYPE_JOB,
			   RPC.TLNAME AS USER_JOB,
			   TU.TLFullName AS USER_JOB_NAME,
			   TFM.TLNANME AS TRANSFER_MAKER,
			    RPC.TRANFER_DT AS TRANFER_DT ,
				RPC.MAKER_ID AS TRANSFER_MAKER_ID,
			   A.EFFEC_DT,A.IS_BACKDAY,
			   '' AS TYPE_JOB_XL,
			   '' AS USER_JOB_XL,
			    --PLRP.ID AS REF_ID,
			   -- GIANT 11/11/2021
			   ISNULL(RPC.PROCESS_ID,PLRP.ID) AS REF_ID,


			   RPN.STATUS AS STATUS_NEXT,
			   PLRP.STATUS AS STATUS_CURR,
			   RPC.STATUS_JOB AS STATUS_JOB,
			   A.BRANCH_CREATE,
			   A.DEP_CREATE,
			   A.REQ_LINE,
			   A.TC_NOTES,
			   A.SIGN_USER, TL.TLFullName AS SIGN_USER_NAME,
			   A.KT_NOTES,
			     A.IS_CHECKALL,
			   A.BASED_CONTENT, 
			   '' AS IS_TRANSFER,
			   --NGUOI XU LY
			   --dbo.PL_PROCESS_CURRENT_NGUOIXULY(A.REQ_ID,NULL,'TTCT-DVKD',0) AS NGUOIXULY,
			   '' AS NGUOIXULY
				,A.TK_TGD_NOTES, A.OTHER_NOTES,A.TK_HDQT_NOTES,
			  BTC.BRANCH_TYPE AS BRANCH_TYPE_CRE,
			  BF.BRANCH_TYPE AS BRANCH_TYPE_FEE,
			  A.PL_BASED_ID,
        A.TGD_NOTES, A.DEP_FEE AS DEP_FEE_ID,
        A.CREATOR_NOTES,
        ISNULL(PLRP.SUB_PROCESS_ID, '') AS SUB_PROCESS_ID
		FROM PL_REQUEST_DOC A 	
		--LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS <> 'U' AND (PLRP.IS_HAS_CHILD=1 OR PLRP.PROCESS_ID IN ('TKTGD','TKHDQT'))
		------------BAODNQ 24/6/2022-------------------
		LEFT JOIN dbo.PL_REQUEST_PROCESS PLRP ON PLRP.REQ_ID=A.REQ_ID AND PLRP.STATUS = 'C' AND ((PLRP.PROCESS_ID = 'DVCM' AND EXISTS(
      SELECT pc.COST_ID FROM PL_COSTCENTER pc
      LEFT JOIN PL_COSTCENTER_DT pcd ON pc.COST_ID = pcd.COST_ID
      LEFT JOIN CM_DEPARTMENT cd ON pcd.DEP_ID = cd.DEP_ID OR pcd.DEP_ID = cd.FATHER_ID
      LEFT JOIN TL_USER tu1 ON pcd.BRANCH_ID = tu1.TLSUBBRID AND cd.DEP_ID = tu1.SECUR_CODE
      WHERE (pc.DVDM_ID = PLRP.DVDM_ID OR PLRP.DVDM_ID IS NULL OR PLRP.DVDM_ID = '')
      AND tu1.TLNANME = @p_USER_LOGIN))
      OR (PLRP.PROCESS_ID = 'DVDC' AND EXISTS(
      SELECT * FROM PL_REQUEST_TRANSFER prt
      LEFT JOIN TL_USER tu1 ON prt.FR_BRN_ID = tu1.TLSUBBRID AND prt.FR_DEP_ID = tu1.SECUR_CODE
      WHERE prt.FR_BRN_ID = PLRP.BRANCH_ID AND prt.FR_DEP_ID = PLRP.DEP_ID
      AND tu1.TLNANME = @p_USER_LOGIN))
      OR PLRP.PROCESS_ID NOT IN ('DVCM','DVDC'))
		------------END BAODNQ-------------------
		LEFT JOIN dbo.PL_REQUEST_PROCESS RPN 
		ON RPN.ID IN (SELECT TOP 1 Temp.ID FROM dbo.PL_REQUEST_PROCESS Temp WHERE Temp.REQ_ID=A.REQ_ID AND (Temp.STATUS='C' OR Temp.STATUS='R') )
		LEFT JOIN dbo.PL_REQUEST_PROCESS RP ON RP.ID=(SELECT TOP 1 ID FROM PL_REQUEST_PROCESS WHERE PL_REQUEST_PROCESS.REQ_ID=A.REQ_ID AND PL_REQUEST_PROCESS.PROCESS_ID=RPN.PARENT_PROCESS_ID ORDER BY APPROVE_DT DESC)	
		LEFT JOIN CM_AUTH_STATUS D ON D.AUTH_STATUS = A.AUTH_STATUS
		LEFT JOIN CM_BRANCH G ON A.BRANCH_ID=G.BRANCH_ID
		LEFT JOIN dbo.CM_DEPARTMENT DEP ON DEP.DEP_ID=A.DEP_ID

		LEFT JOIN CM_BRANCH BF ON BF.BRANCH_ID=A.BRANCH_FEE
		LEFT JOIN dbo.CM_DEPARTMENT DF ON DF.DEP_ID=A.DEP_FEE
		LEFT JOIN dbo.TL_USER UC ON UC.TLNANME=A.MAKER_ID
		LEFT JOIN dbo.CM_PROCESS_STATUS PS ON PS.PROCESS_ID = A.PROCESS_ID
		LEFT JOIN dbo.CM_PROCESS_STATUS PSN ON PSN.PROCESS_ID = A.PROCESS_ID
		LEFT JOIN dbo.CM_DVDM CD ON CD.DVDM_ID=A.DVDM_APP_ID
		--LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID = PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')
		-- GIANT 
		LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID AND RPC.PROCESS_ID=PLRP.ID AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R') AND RPC.TLNAME = @p_USER_LOGIN
		--LEFT JOIN dbo.PL_REQUEST_PROCESS_CHILD RPC ON RPC.REQ_ID=A.REQ_ID  AND (RPC.STATUS_JOB='C' OR RPC.STATUS_JOB='R')

		LEFT JOIN dbo.TL_USER UDV ON UDV.TLNANME=A.CHECKER_ID
	
		LEFT JOIN dbo.TL_USER UP ON UP.TLNANME=RP.CHECKER_ID
		LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=RPC.TLNAME
		LEFT JOIN dbo.TL_USER TFM ON TFM.TLNANME=RPC.MAKER_ID
		LEFT JOIN dbo.PL_REQUEST_DOC PARENT ON PARENT.REQ_ID=A.REQ_PARENT_ID
		LEFT JOIN dbo.PL_REQUEST_DOC CC ON CC.REQ_ID = A.PL_BASED_ID

		LEFT JOIN TL_USER TL ON A.SIGN_USER = TL.TLNANME

		-- GiaNT
		LEFT JOIN CM_BRANCH BTC ON BTC.BRANCH_ID = A.BRANCH_CREATE
		WHERE 1 = 1
		AND (A.REQ_ID = @p_REQ_ID ) 
		ORDER BY USER_JOB DESC
END