ALTER PROCEDURE dbo.TR_REQUEST_SHOP_DOC_DT_ById
@p_REQ_ID varchar(15),
@BRANCH_LOGIN	varchar(15) = NULL,
@ASSET_TYPE	varchar(15) = NULL
AS
DECLARE @l_CCLD_VALUE decimal(18,0) = 0
SET @l_CCLD_VALUE = (SELECT ParaValue FROM SYS_PARAMETERS WHERE ParaKey = 'ASSET_VALUE')
IF @@ERROR <> 0 SET @l_CCLD_VALUE = 0
SELECT 
		A.REQDT_ID,
       A.REQ_DOC_ID,
       A.PLAN_ID,
       A.TRADE_ID,
       A.ASS_GROUP_ID,
       A.DESCRIPTION,
       A.REASON,
       A.UNIT_ID,
       A.QUANTITY,
       A.PRICE,
       A.TOTAL_AMT,
       A.NOTES,
       A.RECEIVE_BRANCH,
       A.RECEIVE_ADDR,
       A.RECEIVE_PERSON,
       A.RECEIVE_TEL,
       A.RECORD_STATUS,
       A.MAKER_ID,
       A.CREATE_DT,
       A.AUTH_STATUS,
       A.CHECKER_ID,
       A.APPROVE_DT,
       A.NOTES_DVCM,
       A.QUANTITY_USE,
       A.RECEIVE_SUBBRANCH,
       A.RECEIVE_DEP,
       A.RECEIVE_EMAIL,
       A.REQ_DT_TYPE,A.PRICE AS PRICE_D,E.BRANCH_CODE, E.BRANCH_ID,E.BRANCH_NAME,  C.UNIT_CODE, C.UNIT_ID, C.UNIT_NAME, F.PLAN_CODE, F.PLAN_NAME,
	   G.BRANCH_CODE AS R_BRANCH_CODE, G.BRANCH_NAME AS R_BRANCH_NAME,A.QTY_ETM,EMP.EMP_CODE, EMP.EMP_ID,EMP.EMP_NAME,
       AG.GROUP_CODE AS ASS_GROUP_CODE,
       AG.GROUP_NAME AS ASS_GROUP_NAME,
        
        CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN E.BRANCH_ID ELSE CBE.BRANCH_ID END AS BRANCH_OLD,
        CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN E.BRANCH_CODE ELSE CBE.BRANCH_CODE END AS BRANCH_OLD_CODE,
        CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN E.BRANCH_NAME ELSE CBE.BRANCH_NAME END AS BRANCH_OLD_NAME,
                                      
        CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN CDR.DEP_ID ELSE CDE.DEP_ID END AS DEP_OLD,
        CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN CDR.DEP_CODE ELSE CDE.DEP_CODE END AS DEP_OLD_CODE,
        CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN CDR.DEP_NAME ELSE CDE.DEP_NAME END AS DEP_OLD_NAME,
        --CBE.BRANCH_ID AS BRANCH_OLD, CBE.BRANCH_CODE AS BRANCH_OLD_CODE, CBE.BRANCH_NAME AS BRANCH_OLD_NAME, 
        CDE.DEP_ID AS DEP_OLD, CDE.DEP_CODE AS DEP_OLD_CODE, CDE.DEP_NAME AS DEP_OLD_NAME, 

		CD.DEP_CODE AS R_DEP_CODE,CD.DEP_NAME AS R_DEP_NAME, AG.DVCM_ID AS ID_DVCM,
    R.REQ_CODE, R.REQ_NAME, A.ALLOCATED,
    (CASE 
    	WHEN A.REQ_DT_TYPE = 'BUYNEW' THEN N'Mua mới'
    	WHEN A.TYPE_XL = 'CPTK' THEN N'Xuất kho'
    	WHEN A.TYPE_XL = 'CPDC' THEN N'Tận dụng/Điều chuyển'
    END) AS REQ_DT_TYPE_NAME
FROM TR_REQUEST_SHOP_DOC_DT A
LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID
LEFT JOIN dbo.TR_REQUEST_SHOP_DOC R ON R.REQ_ID=A.REQ_DOC_ID
LEFT JOIN PL_MASTER F ON F.PLAN_ID = A.PLAN_ID 
LEFT JOIN CM_BRANCH E ON E.BRANCH_ID =  R.BRANCH_ID
LEFT JOIN dbo.CM_DEPARTMENT CDR ON CDR.DEP_ID=R.DEP_ID
LEFT JOIN CM_BRANCH G ON G.BRANCH_ID =  A.RECEIVE_BRANCH
LEFT JOIN CM_EMPLOYEE  EMP ON EMP.EMP_ID = A.EMP_ID
LEFT JOIN CM_BRANCH CBE ON CBE.BRANCH_ID = EMP.BRANCH_ID
LEFT JOIN dbo.CM_DEPARTMENT CDE ON CDE.DEP_ID=EMP.DEP_ID
LEFT JOIN ASS_GROUP AG ON A.ASS_GROUP_ID = AG.GROUP_ID
LEFT JOIN dbo.CM_DEPARTMENT CK ON CK.DEP_ID=A.RECEIVE_SUBBRANCH AND CK.KHOI_ID = 'K'
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=A.RECEIVE_SUBBRANCH
LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=A.RECEIVE_DEP

 WHERE A.REQ_DOC_ID=@p_REQ_ID AND (A.RECEIVE_BRANCH = @BRANCH_LOGIN OR @BRANCH_LOGIN IS NULL OR @BRANCH_LOGIN = '')
		--AND ((@ASSET_TYPE = 'TSCD' AND A.PRICE >= @l_CCLD_VALUE) OR (@ASSET_TYPE = 'CCLD' AND A.PRICE < @l_CCLD_VALUE) OR @ASSET_TYPE IS NULL OR @ASSET_TYPE = '')

ORDER BY A.CREATE_DT DESC