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

ALTER 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, R.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 

	(

	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

¿

ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_ByRefId] 

@p_REF_ID VARCHAR(200) = NULL, 

@p_BRANCH_ID VARCHAR(50) = NULL, 

@RefNo VARCHAR(500) = NULL

AS

-- LUCTV 03062020 BO SUNG BANG MAP GL

DECLARE  @TABLE_MAP_GL TABLE (ACCT VARCHAR(100), TL_GL VARCHAR(100), GL_NAME VARCHAR(1000))

INSERT INTO @TABLE_MAP_GL SELECT ACC_NO, TK_GL, TK_GL_NAME FROM CM_ACCOUNT

--INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM, TK_GL, TK_GL_NAME FROM CM_ACCOUNT_PAY

--INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM, (SELECT ParaValue FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL'), (SELECT Description FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL') FROM CM_SUPPLIER

--INSERT INTO @TABLE_MAP_GL SELECT ACC_NUM_OUT, (SELECT ParaValue FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL_O'), (SELECT Description FROM  SYS_PARAMETERS WHERE ParaKey ='NCC_GL_O') FROM CM_SUPPLIER

---

SELECT A.REF_NO, MAX(A.DR_ACCT) AS DR_ACCT, MAX(A.DR_ACCT_NAME) AS DR_ACCT_NAME,

MAX(A.DR_BRN) AS DR_BRN,MAX(A.DR_D) AS DR_D, MAX(A.ACCT) AS ACCT, MAX(A.REF_ID) AS REF_ID, MAX(A.DR_D) AS DR_D, MAX(A.ENTRY_PAIR) AS ENTRY_PAIR,

MAX(A.DRCR) AS DRCR,  REPLACE (FORMAT (SUM(A.AMT),'#,###'),',','.') +',00 VND' AS AMT

FROM

(

SELECT A.AMT, A.ACCT +' '+ BR.BRANCH_CODE AS DR_ACCT, [dbo].[FN_GET_ACC_NAME_PAY](A.ACCT) AS DR_ACCT_NAME, '' DR_BRN, 

(CASE WHEN A.DRCR = 'D' THEN N'Nợ/Debit' ELSE N'Có/Credit' END) AS ACCT, '' AS REF_ID, ISNULL(DP.DEP_CODE,'') AS DR_D, A.ENTRY_PAIR,A.DRCR,A.REF_NO,A.DEPT_ID

FROM PAY_ENTRIES_POST A

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

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

WHERE A.TRN_ID = @p_REF_ID AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '') 

AND LEN(A.ACCT) <= 9

--ORDER BY A.ENTRY_PAIR, A.DRCR DESC

UNION 

SELECT A.AMT, GL.TL_GL +' '+ BR.BRANCH_CODE  AS DR_ACCT, GL.GL_NAME AS DR_ACCT_NAME, '' DR_BRN, 

(CASE WHEN A.DRCR = 'D' THEN N'Nợ/Debit' ELSE N'Có/Credit' END) AS ACCT, '' AS REF_ID, ISNULL(DP.DEP_CODE,'') AS DR_D,A.ENTRY_PAIR,A.DRCR,A.REF_NO,A.DEPT_ID

FROM PAY_ENTRIES_POST A

INNER JOIN @TABLE_MAP_GL GL ON A.ACCT = GL.ACCT

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

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

WHERE A.TRN_ID = @p_REF_ID AND (A.REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '') 

AND LEN(A.ACCT) > 9

) A

--ORDER BY A.ENTRY_PAIR, A.DRCR,A.REF_NO

GROUP BY A.REF_NO, A.DR_ACCT, A.DRCR, A.DR_BRN,A.DEPT_ID

ORDER BY A.DRCR DESC, A.DR_ACCT DESC

--DECLARE @ENT_TEMP TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15))

--DECLARE @ENT_TEMP_ROOT TABLE (ID INT,REF_ID VARCHAR(15), CR_ACCT VARCHAR(50), CR_BRN VARCHAR(15), DR_ACCT VARCHAR(50), DR_BRN VARCHAR(15), AMT DECIMAL(18,0), TRN_DESC NVARCHAR(500), ASSET_ID VARCHAR(15),DR_NAME NVARCHAR(500), CR_NAME NVARCHAR(500))

--DECLARE @TRN_DESC NVARCHAR(500) = '', @TRN_TYPE VARCHAR(50)

----------------------------LAY DANH SACH BUT TOAN -- LU VAO BANG DU LIEU GOC--------------------------

--INSERT INTO @ENT_TEMP_ROOT SELECT Row_number() over(order by A.CR_ACCT),'',A.CR_ACCT, B.BRANCH_CODE CR_BRN, A.DR_ACCT, C.BRANCH_CODE DR_BRN, SUM(A.AMT) AS AMT,@TRN_DESC, A.MAST_PRICE_ID AS ASSET_ID,NULL, NULL

--FROM MW_ENTRIES_POST A

--LEFT JOIN CM_BRANCH B ON A.CR_BRN = B.BRANCH_ID

--LEFT JOIN CM_BRANCH C ON A.DR_BRN = C.BRANCH_ID

--WHERE TRN_ID = @p_REF_ID AND (A.DO_BRN = @p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID = '')  AND (REF_NO = @RefNo OR @RefNo IS NULL OR @RefNo = '')

--GROUP BY A.CR_ACCT, B.BRANCH_CODE, A.DR_ACCT, C.BRANCH_CODE,A.MAST_PRICE_ID

----ORDER BY LEN(CR_ACCT)

----select * from @ENT_TEMP_ROOT

----DECLARE @COUNT INT = (SELECT COUNT(*) - COUNT(DISTINCT ASSET_ID) FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID)

----IF (SELECT COUNT(DISTINCT TRN_TYPE) FROM MW_ENTRIES_POST WHERE TRN_ID = @p_REF_ID) > 1

----SET @TRN_TYPE = 'ADD_NEW'

----ELSE

----SET @TRN_TYPE = (SELECT TOP 1 TRN_TYPE FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_REF_ID )

------------------------------TACH 1 DONG DU LIEU THANH 2 DONG NAM LIEN KE NHAU DE BINDING WORD--------------------------

---------LAY TAI KHOAN GL TUONG UNG CASA TRONG CM_BRANCH

--UPDATE @ENT_TEMP_ROOT SET CR_ACCT = B.TEL, CR_NAME = B.PROVICE

--FROM CM_BRANCH B

--INNER JOIN @ENT_TEMP_ROOT C ON C.CR_ACCT = B.DAO_CODE AND LEN(C.CR_ACCT)>9

--UPDATE @ENT_TEMP_ROOT SET DR_ACCT = B.TEL, DR_NAME = B.PROVICE

--FROM CM_BRANCH B

--INNER JOIN @ENT_TEMP_ROOT C ON C.DR_ACCT = B.DAO_CODE AND LEN(C.DR_ACCT)>9

----IF @TRN_TYPE = 'ADD_NEW'  OR @TRN_TYPE = 'ASS_USE' OR @TRN_TYPE='ASS_TRANSFER' 

----BEGIN

--	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D

--	FROM

--	(

--		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, ISNULL(A.DR_NAME,[dbo].[FN_GET_ACC_NAME](A.DR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D

--		FROM @ENT_TEMP_ROOT A

--		--WHERE LEN(A.DR_ACCT) <= 9

--		UNION 

--		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, ISNULL(A.CR_NAME,[dbo].[FN_GET_ACC_NAME](A.CR_ACCT)) AS DR_ACCT_NAME, '' AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D

--		FROM @ENT_TEMP_ROOT A

--		--WHERE LEN(A.CR_ACCT) <= 9

--		--ORDER BY ID, ACCT DESC

--	) AG

--	GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D

--	ORDER BY SUM(REF_ID), AG.ACCT DESC

----END

----ELSE

----BEGIN

----	--IF LEFT(@p_REF_ID,4) = 'ASSL'--THANH LY TAI SAN

----	SELECT SUM(AG.AMT) AMT, AG.DR_ACCT, AG.DR_ACCT_NAME, AG.DR_BRN, AG.ACCT,SUM(REF_ID) REF_ID, AG.DR_D DR_D

----	FROM

----	(

----		SELECT A.AMT, A.DR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.DR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Nợ/Debit' AS ACCT,ID AS REF_ID, A.DR_BRN AS DR_D

----		FROM @ENT_TEMP_ROOT A

----		UNION 

----		SELECT A.AMT, A.CR_ACCT AS DR_ACCT, [dbo].[FN_GET_ACC_NAME](A.CR_ACCT) AS DR_ACCT_NAME, A.ASSET_ID AS DR_BRN, N'Có/Credit' AS ACCT,ID AS REF_ID, A.CR_BRN AS DR_D

----		FROM @ENT_TEMP_ROOT A

----	--ORDER BY ID, ACCT DESC

----	) AG

----	GROUP BY AG.DR_ACCT, AG.DR_ACCT_NAME,AG.DR_BRN,AG.DR_ACCT,AG.ACCT, AG.DR_D

----	ORDER BY AG.DR_BRN, AG.ACCT DESC

----END

--DELETE @ENT_TEMP

--DELETE @ENT_TEMP_ROOT

--------------------------DONE--------------------------

¿

DECLARE @REQ_ID VARCHAR(15), @DATE DATETIME

SET @DATE ='2021-03-23 12:00:00 AM'

SET @REQ_ID =(SELECT REQ_ID FROM TR_REQUEST_DOC WHERE REQ_CODE ='PUR/2021/000575')

UPDATE PL_PROCESS SET APPROVE_DT =@DATE WHERE REQ_ID =@REQ_ID

UPDATE TR_REQUEST_DOC SET REQ_DT =@DATE, APPROVE_DT =@DATE WHERE REQ_ID =@REQ_ID

¿