DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

-- Stored Procedure

CREATE PROC [dbo].[TR_REQ_PAY_ENTRIES_ById]

@p_TRN_ID varchar(15) = NULL

AS

BEGIN

DECLARE @TABLE_RETURN TABLE (REQ_PAY_DT_ID VARCHAR(15),REQ_PAY_ID VARCHAR(15),FUNCTION_TYPE VARCHAR(15),REF_ID VARCHAR(15),ENTRY_PAIR VARCHAR(15),DR_CR VARCHAR(15),

DR_CR_NAME VARCHAR(15),ACCT VARCHAR(25),ACCT_NAME VARCHAR(100),BRANCH_ID VARCHAR(15),DEP_ID VARCHAR(15),AMT DECIMAL(18,0),CURRENCY VARCHAR(15),EXC_RATE INT,TRN_DATE DATETIME,TRN_DESC VARCHAR(150),

MAKER_ID VARCHAR(15),CREATE_DT DATETIME,AUTH_STATUS VARCHAR(1),CHECKER_ID VARCHAR(15),APPROVE_DT DATETIME,MAKER_ID_KT VARCHAR(15),CREATE_DT_KT VARCHAR(15),

AUTH_STATUS_KT VARCHAR(15),CHECKER_ID_KT VARCHAR(15),RECORD_STATUS VARCHAR(15),BRANCH_CODE VARCHAR(15),BRANCH_NAME VARCHAR(150),BR_FULLNAME VARCHAR(150),

DEP_CODE VARCHAR(15),DEP_NAME NVARCHAR(150),DP_FULLNAME VARCHAR(150))

DECLARE @TRN_DESC VARCHAR(100), @MAKER_ID VARCHAR(15), @MAKER_ID_KT VARCHAR(15), @CREATE_DT DATETIME

SET @TRN_DESC =(SELECT dbo.fChuyenCoDauThanhKhongDau(REQ_REASON) FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_TRN_ID)

SET @MAKER_ID =(SELECT MAKER_ID FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_TRN_ID)

SET @CREATE_DT =(SELECT CREATE_DT FROM TR_REQ_PAYMENT WHERE REQ_PAY_ID =@p_TRN_ID)

--IF(NOT EXISTS(SELECT * FROM TR_REQ_PAY_ENTRIES WHERE REQ_PAY_ID =@p_TRN_ID))

--BEGIN

--INSERT INTO @TABLE_RETURN VALUES ('',@p_TRN_ID,'PAY',@p_TRN_ID,'1','C',N'Có','06970410080980',N'TAI KHOAN HACH TOAN',(SELECT BRANCH_ID FROM CM_BRANCH WHERE BRANCH_CODE =(LEFT('06970410080980',3))),'',0,

--'VND',1.0,GETDATE(),@TRN_DESC,@MAKER_ID,@CREATE_DT,'U','',NULL,'',NULL,'','','1',(SELECT BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_CODE =(LEFT('06970410080980',3))),

--(SELECT BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_CODE =(LEFT('06970410080980',3))),'FULLNAME','','','')

--SELECT * FROM @TABLE_RETURN

--END

--ELSE

--BEGIN

	SELECT A.REQ_PAY_DT_ID, A.REQ_PAY_ID,A.FUNCTION_TYPE,A.REF_ID,A.ENTRY_PAIR,A.DR_CR,A.DR_CR_NAME,A.ACCT,ISNULL(C.ACC_NAME,'') AS ACCT_NAME,A.BRANCH_ID,A.DEP_ID,A.AMT,A.CURRENCY,A.EXC_RATE,A.TRN_DATE,A.TRN_DESC,

	A.MAKER_ID,A.CREATE_DT,A.AUTH_STATUS,A.CHECKER_ID,A.APPROVE_DT,A.MAKER_ID_KT,A.CREATE_DT_KT,A.AUTH_STATUS_KT,A.CHECKER_ID_KT,A.RECORD_STATUS

	, BR.BRANCH_CODE, BR.BRANCH_NAME, BR.BRANCH_CODE+' - '+ BR.BRANCH_NAME AS BR_FULLNAME,

	DP.DEP_CODE, DP.DEP_NAME, DP.DEP_CODE+ ' - '+ DP.DEP_NAME AS DP_FULLNAME, ISNULL(P.REF_NO,'') AS REF_NO

	FROM TR_REQ_PAY_ENTRIES A

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

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

	LEFT JOIN CM_ACCOUNT C ON A.ACCT = C.ACC_NO

	LEFT JOIN PAY_ENTRIES_POST P ON A.REQ_PAY_DT_ID = p.REF_ID

	--LEFT JOIN 

	--(

	--SELECT MAX(REF_NO) AS REF_NO, ENTRY_PAIR,TRN_ID FROM PAY_ENTRIES_POST

	--GROUP BY ENTRY_PAIR, TRN_ID

	--) R ON A.ENTRY_PAIR = R.ENTRY_PAIR AND A.REQ_PAY_ID = R.TRN_ID

	WHERE A.REQ_PAY_ID =@p_TRN_ID

	ORDER BY A.ENTRY_PAIR ASC, A.DR_CR DESC

--END

END

GO