ALTER PROCEDURE [dbo].[MW_TRANSFER_CONFIRM_Search]
@p_TRANSFER_ID VARCHAR(15) = NULL, @p_TRN_DATE VARCHAR(20) = NULL, @p_TRN_TIME VARCHAR(50) = NULL, @p_QTY INT = NULL, @p_PRICE NUMERIC(18, 0) = NULL, @p_TOTAL_AMT NUMERIC(18, 2) = NULL,
 @p_NOTES NVARCHAR(1000) = NULL, @p_AUTH_STATUS VARCHAR(1) = NULL, @p_MAKER_ID VARCHAR(15) = NULL, @p_CREATE_DT VARCHAR(20) = NULL, @p_CHECKER_ID VARCHAR(15) = NULL,
 @p_APPROVE_DT VARCHAR(20) = NULL, @p_KT_AUTH_STATUS VARCHAR(1) = NULL, @p_KT_MAKER_ID VARCHAR(15) = NULL, @p_KT_CREATE_DT VARCHAR(20) = NULL, @p_KT_CHECKER_ID VARCHAR(15) = NULL,
 @p_KT_APPROVE_DT VARCHAR(20) = NULL, @p_XmlData XML = NULL, @p_MAKER_LOGIN VARCHAR(15) = NULL, @p_LEVEL VARCHAR(50) = 'UNIT', @p_TOP INT = 10, @p_FROM_DATE VARCHAR(20) = NULL,
 @p_TO_DATE VARCHAR(20) = NULL, @p_IS_UPDATE VARCHAR(1) = 'N', @p_BRANCH_CREATE  varchar(15)  = NULL, @p_WARE_HOUSE VARCHAR(15) = NULL,@p_TRANSFER_CODE NVARCHAR(30) = null
AS
BEGIN
	 --Luctv Bo Sung Doan Code Doc Du Lieu Bo Qua Commit Transaction
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	SELECT A.*, B.MAKER_ID, CON.MAKER_ID AS KT_MAKER_ID, B.CREATE_DT, B.KT_CREATE_DT,B.CHECKER_ID, B.KT_CHECKER_ID, B.APPROVE_DT, B.KT_APPROVE_DT, 
	B.AUTH_STATUS, B.KT_AUTH_STATUS, B.NOTES, B.CORE_NOTE,B.TRN_DATE, B.TRN_TIME, DP.DEP_CODE, DP.DEP_NAME, BR.BRANCH_CODE, BR.BRANCH_NAME,
	AU.AUTH_STATUS_NAME AS AUTH_STATUS_NAME, TL.TLFullName AS CREATE_FULL_NAME, TL1.TLFullName AS KT_CREATE_FULL_NAME, (CASE WHEN CON.CHECKER_ID IS NULL THEN 'U' ELSE 'A' END) AS IS_RECIVE,
	TL2.TLFullName AS APPROVE_NAME, TL1.TLFullName KT_APPROVE_NAME, B.WARE_HOUSE, AL.CONTENT WARE_HOUSE_NAME,CON.CHECKER_ID CONF_CHECKER_ID, CON.CREATE_DT CONF_CREATE_DT,
	CON.MAKER_ID CONF_MAKER_ID,TL4.TLFullName CONFIRM_NAME, (CASE WHEN CON.CHECKER_ID IS NULL THEN N'Chưa xác nhận' ELSE N'Chưa xác nhận' END) AS RECIVE_NAME,B.TRANSFER_CODE
	FROM MW_TRANSFER B
	--INNER JOIN MW_TRANSFER_DT A ON A.TRANSFER_ID = B.TRANSFER_ID AND A.TO_BRN_ID = @p_BRANCH_CREATE : CHO NAY SHOW DATA TREN GIAO DIEN BI TRUNG NEN LUCTV THAY BANG DOAN CODE BEN DUOI
	INNER JOIN 
	(
		SELECT TRANSFER_ID, TO_BRN_ID, SUM(QTY) QTY,SUM(TOTAL_AMT)TOTAL_AMT 
		FROM MW_TRANSFER_DT 
		WHERE TO_BRN_ID =@p_BRANCH_CREATE 
		GROUP BY TRANSFER_ID, TO_BRN_ID
	) A ON A.TRANSFER_ID = B.TRANSFER_ID
	LEFT JOIN MW_TRANSFER_CONF CON ON CON.TRANSFER_ID = B.TRANSFER_ID AND CON.BRANCH_RECIVE = @p_BRANCH_CREATE
	LEFT JOIN CM_DEPARTMENT DP ON B.FR_DEPT_ID = DP.DEP_ID
	LEFT JOIN CM_BRANCH BR ON B.BRANCH_CREATE = BR.BRANCH_ID 
	LEFT JOIN CM_AUTH_STATUS AU ON B.KT_AUTH_STATUS = AU.AUTH_STATUS
	LEFT JOIN TL_USER TL ON B.MAKER_ID = TL.TLNANME
	LEFT JOIN TL_USER TL1 ON CON.MAKER_ID = TL1.TLNANME
	LEFT JOIN TL_USER TL2 ON B.CHECKER_ID = TL2.TLNANME
	LEFT JOIN TL_USER TL3 ON B.KT_CHECKER_ID = TL3.TLNANME
	LEFT JOIN TL_USER TL4 ON CON.MAKER_ID = TL4.TLNANME
	LEFT JOIN CM_ALLCODE AL ON B.WARE_HOUSE = AL.CDVAL AND AL.CDNAME='WARE_HOUSE_IN'
	WHERE 1 = 1
	AND (CONVERT (DATE, B.TRN_DATE, 103) >= CONVERT (DATE, @p_FROM_DATE, 103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE = '')
    AND (CONVERT (DATE, B.TRN_DATE, 103) <= CONVERT (DATE, @p_TO_DATE, 103) OR @p_TO_DATE IS NULL OR @p_TO_DATE = '')
	AND B.RECORD_STATUS='1'
	AND (B.WARE_HOUSE= @p_WARE_HOUSE OR @p_WARE_HOUSE ='' OR @p_WARE_HOUSE IS NULL)
	AND B.AUTH_STATUS='A' 
	AND B.KT_AUTH_STATUS ='A'
	AND ((@p_IS_UPDATE = 'Y' AND CON.CHECKER_ID IS NOT NULL) OR (@p_IS_UPDATE = 'N' AND CON.CHECKER_ID IS NULL) OR @p_IS_UPDATE = '' OR @p_IS_UPDATE IS NULL)
    ORDER BY B.CREATE_DT DESC;
END