ALTER PROCEDURE [dbo].[TR_REQ_PAY_TRANSFER_SEARCH]
@p_TRANSER_CODE VARCHAR(15) = NULL,
@p_REQ_PAY_CODE VARCHAR(40)= NULL,
@p_FR_DATE VARCHAR(30)= NULL,
@p_TO_DATE VARCHAR(30)= NULL,
@p_AUTH_STATUS VARCHAR(1) = NULL,
@p_MAKER_ID VARCHAR(15)= NULL,
@p_CHECKER_ID VARCHAR(15)= NULL,
@_FR_USER VARCHAR(15) = NULL,
@p_TO_USER VARCHAR(15) = NULL
AS
BEGIN
SELECT A. *, B.TLFullName AS CREATE_FULLNAME,C.TLFullName AS APPR_FULLNAME , D.AUTH_STATUS_NAME,
A.TRANSFER_ID AS REQ_PAY_ID, '' AS REASON, 0.00 AS TOTAL_AMT,0.00 AS TOAL_AMT_DO,0.00 AS TOTAL_AMT_REMAIN, 0.00 AS TOTAL_AMT_REAL,0.00 AS TOTAL_AMT_REMAIN_FN, '' AS REQ_PAY_CODE, '' REQ_REASON,
X.BRANCH_CODE, X.BRANCH_NAME, X1.TLFullName AS FR_FULLNAME, X2.TLFullName AS TO_FULLNAME, '' AS FR_BRANCH_CODE , '' FR_BRANCH_NAME, '' AS TO_BRANCH_CODE, '' AS TO_BRANCH_NAME,
C1.ACC_NAME,C1.ACC_NUM, C1.ACC_TYPE, C1.TK_GL, C2.TK_GL_NAME,
C2.ACC_NAME AS TO_ACC_NAME,C2.ACC_NUM AS TO_ACC_NUM, C2.ACC_TYPE AS TO_ACC_TYPE, C2.TK_GL AS TO_TK_GL, C2.TK_GL_NAME AS TO_TK_GL_NAME, X1.TLSUBBRID AS FR_BR_ID,B1.BRANCH_CODE AS FR_BR_CODE, B1.BRANCH_NAME AS FR_BR_NAME,
B1.BRANCH_CODE + ' - '+ B1.BRANCH_NAME AS  FR_BR_FULLNAME, X2.TLSUBBRID AS TO_BR_ID,B2.BRANCH_CODE AS TO_BR_CODE, B2.BRANCH_NAME AS TO_BR_NAME, B2.BRANCH_CODE + ' - '+ B2.BRANCH_NAME AS TO_BR_FULLNAME ,
F.DEP_CODE AS FR_DEP_CODE, F.DEP_NAME AS FR_DEP_NAME, F.DEP_CODE +'-'+ F.DEP_NAME AS FR_DEP_FULL_NAME,
F1.DEP_CODE AS TO_DEP_CODE, F1.DEP_NAME AS TO_DEP_NAME, F1.DEP_CODE +'-'+ F1.DEP_NAME AS TO_DEP_FULL_NAME
FROM TR_REQ_PAY_TRANSFER A
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
LEFT JOIN TL_USER C ON A.CHECKER_ID = C.TLNANME
LEFT JOIN CM_AUTH_STATUS D ON A.AUTH_STATUS = D.AUTH_STATUS
--LEFT JOIN TR_REQ_PAY_TRANSFER_DT DT ON A.TRANSFER_ID = DT.TRANSFER_ID
LEFT JOIN CM_BRANCH X ON B.TLSUBBRID = X.BRANCH_ID
LEFT JOIN TL_USER X1 ON A.FR_USER = X1.TLNANME
LEFT JOIN TL_USER X2 ON A.TO_USER = X2.TLNANME
LEFT JOIN CM_BRANCH B1 ON B1.BRANCH_ID = X1.TLSUBBRID
LEFT JOIN CM_BRANCH B2 ON B2.BRANCH_ID = X2.TLSUBBRID
LEFT JOIN CM_ACCOUNT_PAY C1 ON A.FR_USER = C1.REF_ID
LEFT JOIN CM_ACCOUNT_PAY C2 ON A.TO_USER = C2.REF_ID
LEFT JOIN CM_DEPARTMENT F ON X1.SECUR_CODE = F.DEP_ID
LEFT JOIN CM_DEPARTMENT F1 ON X2.SECUR_CODE = F1.DEP_ID
WHERE 1=1
	AND(A.TRANSFER_CODE=@p_TRANSER_CODE OR @p_TRANSER_CODE='' OR @p_TRANSER_CODE IS NULL)
	AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @p_FR_DATE, 103) OR @p_FR_DATE IS NULL OR @p_FR_DATE='' OR A.CREATE_DT IS NULL)
    AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TO_DATE, 103) OR @p_TO_DATE IS NULL OR @p_TO_DATE='' OR A.CREATE_DT IS NULL)
	AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL)
	AND(A.MAKER_ID=@p_MAKER_ID OR @p_MAKER_ID='' OR @p_MAKER_ID IS NULL)
	AND(A.CHECKER_ID=@p_CHECKER_ID OR @p_CHECKER_ID='' OR @p_CHECKER_ID IS NULL)
	AND(A.FR_USER=@_FR_USER OR @_FR_USER='' OR @_FR_USER IS NULL)
	AND(A.TO_USER=@p_TO_USER OR @p_TO_USER='' OR @p_TO_USER IS NULL)
	AND A.AUTH_STATUS <> 'D'
END