DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

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