AMS - VB

ALTER PROC dbo.rpt_ASS_USE_MULTI_CONFIRM_Excel

@USER_MASTER_ID VARCHAR(15) = null,

@BRANCH_ID VARCHAR(15) = null,

@FROMDATE VARCHAR(15) = null,

@TODATE VARCHAR(15) = null,

@IS_CONFIRM VARCHAR(1) = null

as

BEGIN

--	SELECT 

--		B.ASSET_CODE, B.ASSET_NAME, B.AMORT_MONTH, B.AMORTIZED_MONTH, B.AMORT_AMT, B.AMORTIZED_AMT, AMORT_AMT - B.AMORTIZED_AMT AS GT_CL, B.NOTES,

--		A.USER_MASTER_ID,CB.BRANCH_CODE,CB.BRANCH_NAME,1 AS SL,B.BUY_PRICE,'' AS UNIT,B.ASSET_SERIAL_NO AS SERIAL_NUMBER,

--		dbo.FN_GET_BRANCH_LEADER(C.BRANCH_ID, c.DEPT_ID) AS TLFullName,D.DEP_CODE,D.DEP_NAME,E.REQ_CODE

--	FROM ASS_USE_MULTI_MASTER A

--		INNER JOIN ASS_USE_MULTI_DT C ON C.USER_MASTER_ID = A.USER_MASTER_ID

--		INNER JOIN ASS_MASTER B ON B.ASSET_ID = C.ASSET_ID

--		LEFT JOIN CM_BRANCH CB ON CB.BRANCH_ID = C.BRANCH_ID

--    LEFT JOIN CM_DEPARTMENT D ON A.DEP_ID = D.DEP_ID

--    LEFT JOIN TR_REQUEST_SHOP_DOC E ON C.REQ_ID = E.REQ_ID

--		WHERE (A.USER_MASTER_ID = @USER_MASTER_ID OR @USER_MASTER_ID IS NULL OR @USER_MASTER_ID = '')

--		AND A.AUTH_STATUS = 'A' 

--		AND (C.BRANCH_ID = @BRANCH_ID OR @BRANCH_ID IS NULL OR @BRANCH_ID = '')

--		AND ((@IS_CONFIRM = 'Y' AND A.USER_MASTER_ID IN (SELECT USER_MASTER_ID FROM ASS_CONFIRM_USE_MASTER)) 

--		OR (@IS_CONFIRM = 'N' AND A.USER_MASTER_ID NOT IN (SELECT USER_MASTER_ID FROM ASS_CONFIRM_USE_MASTER)

--		OR (@IS_CONFIRM= '' OR @IS_CONFIRM IS NULL)))

--		AND	(CONVERT(DATE,A.APPROVE_DT) >= CONVERT(DATE, @FROMDATE, 103) OR @FROMDATE is NULL OR @FROMDATE = '')

--		AND	(CONVERT(DATE,A.APPROVE_DT) <= CONVERT(DATE, @TODATE, 103) OR @TODATE is NULL OR @TODATE = '')

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT

          ,RE.SO_PYC

          ,RE.NGAY_TAO_PYC

          ,RE.MA_PHIEU_XUAT

          ,RE.NOI_DUNG_BIEN_BAN

          ,RE.MA_DV_NHAN

          ,RE.TEN_DV_NHAN

          ,RE.MA_PB_NHAN

          ,RE.TEN_PB_NHAN

          ,RE.NGAY_XUAT

          ,RE.NGUOI_XUAT

          ,RE.NGUOI_DUYET

          ,RE.NGUOI_XAC_NHAN

    FROM (

        SELECT 

        A.REQ_CODE AS SO_PYC

        ,FORMAT(A.REQ_DT,'dd/MM/yyyy') AS NGAY_TAO_PYC

        ,B.USER_MASTER_ID AS MA_PHIEU_XUAT

        ,D.CONTENT AS NOI_DUNG_BIEN_BAN

        ,H.BRANCH_CODE AS MA_DV_NHAN

        ,H.BRANCH_NAME AS TEN_DV_NHAN

        ,I.DEP_CODE AS MA_PB_NHAN

        ,I.DEP_NAME AS TEN_PB_NHAN

        ,FORMAT(D.CREATE_DT,'dd/MM/yyyy') AS NGAY_XUAT

        ,E.TLFullName AS NGUOI_XUAT

        ,F.TLFullName AS NGUOI_DUYET

        ,G.TLFullName AS NGUOI_XAC_NHAN

        FROM TR_REQUEST_SHOP_DOC A

        LEFT JOIN ASS_USE_MULTI_DT B ON A.REQ_ID = B.REQ_ID

        LEFT JOIN ASS_USE_MULTI_MASTER D ON B.USER_MASTER_ID = D.USER_MASTER_ID

        LEFT JOIN ASS_USE_CONFIRM_MASTER C ON B.USER_MASTER_ID = C.USER_MASTER_ID

        LEFT JOIN TL_USER E ON D.MAKER_ID = E.TLNANME

        LEFT JOIN TL_USER F ON D.CHECKER_ID = F.TLNANME

        LEFT JOIN TL_USER G ON C.MAKER_ID = G.TLNANME

        LEFT JOIN CM_BRANCH H ON D.BRANCH_ID = H.BRANCH_ID

        LEFT JOIN CM_DEPARTMENT I ON D.DEP_ID = I.DEP_ID

        WHERE ((A.STATUS = 'APPROVE' AND @IS_CONFIRM = 'N')

		OR (A.STATUS = 'DONE' AND @IS_CONFIRM = 'Y')

		OR ((@IS_CONFIRM IS NULL OR @IS_CONFIRM = '') AND A.STATUS IN ('APPROVE','DONE')))

        AND A.REQ_TYPE = 'CPTS'

        AND (D.USER_MASTER_ID = @USER_MASTER_ID OR @USER_MASTER_ID IS NULL OR @USER_MASTER_ID = '')

        AND ((@IS_CONFIRM = 'Y' AND C.USER_CONFIRM_MASTER_ID IS NOT NULL AND C.USER_CONFIRM_MASTER_ID <> '') 

        		OR (@IS_CONFIRM = 'N' AND C.USER_CONFIRM_MASTER_ID IS NULL)

        		OR @IS_CONFIRM= '' OR @IS_CONFIRM IS NULL)

    		AND	(CONVERT(DATE,A.APPROVE_DT) >= CONVERT(DATE, @FROMDATE, 103) OR @FROMDATE is NULL OR @FROMDATE = '')

    		AND	(CONVERT(DATE,A.APPROVE_DT) <= CONVERT(DATE, @TODATE, 103) OR @TODATE is NULL OR @TODATE = '')

    	GROUP BY A.REQ_CODE,A.REQ_DT,B.USER_MASTER_ID,D.CONTENT,H.BRANCH_CODE,H.BRANCH_NAME,I.DEP_CODE,I.DEP_NAME,D.CREATE_DT,E.TLFullName,F.TLFullName,G.TLFullName

        UNION ALL    

        SELECT 

          NULL AS SO_PYC

          ,NULL AS NGAY_TAO_PYC

          ,A.USER_MASTER_ID AS MA_PHIEU_XUAT

          ,A.CONTENT AS NOI_DUNG_BIEN_BAN

          ,H.BRANCH_CODE AS MA_DV_NHAN

          ,H.BRANCH_NAME AS TEN_DV_NHAN

          ,I.DEP_CODE AS MA_PB_NHAN

          ,I.DEP_NAME AS TEN_PB_NHAN

          ,FORMAT(A.CREATE_DT,'dd/MM/yyyy') AS NGAY_XUAT

          ,E.TLFullName AS NGUOI_XUAT

          ,F.TLFullName AS NGUOI_DUYET

          ,G.TLFullName AS NGUOI_XAC_NHAN

        FROM ASS_USE_MULTI_MASTER A

        LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID

        LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME 

        LEFT JOIN TL_USER F ON A.CHECKER_ID = F.TLNANME

        LEFT JOIN TL_USER G ON B.MAKER_ID = G.TLNANME

        LEFT JOIN CM_BRANCH H ON A.BRANCH_ID = H.BRANCH_ID

        LEFT JOIN CM_DEPARTMENT I ON A.DEP_ID = I.DEP_ID

        WHERE NOT EXISTS(SELECT 1 FROM ASS_USE_MULTI_DT K 

                      WHERE K.USER_MASTER_ID = A.USER_MASTER_ID AND K.REQ_ID IS NOT NULL AND K.REQ_ID <> '')

        AND (A.USER_MASTER_ID = @USER_MASTER_ID OR @USER_MASTER_ID IS NULL OR @USER_MASTER_ID = '')

        AND ((@IS_CONFIRM = 'Y' AND B.USER_CONFIRM_MASTER_ID IS NOT NULL AND B.USER_CONFIRM_MASTER_ID <> '') 

        		OR (@IS_CONFIRM = 'N' AND B.USER_CONFIRM_MASTER_ID IS NULL)

        		OR @IS_CONFIRM= '' OR @IS_CONFIRM IS NULL)

    		AND	(CONVERT(DATE,A.APPROVE_DT) >= CONVERT(DATE, @FROMDATE, 103) OR @FROMDATE is NULL OR @FROMDATE = '')

    		AND	(CONVERT(DATE,A.APPROVE_DT) <= CONVERT(DATE, @TODATE, 103) OR @TODATE is NULL OR @TODATE = '')

   ) RE

   ORDER BY RE.SO_PYC

END