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_DETAIL]  

@p_REQ_PAY_ID VARCHAR(15) = NULL  

AS  

BEGIN  

DECLARE @TABLE_RETURN_01 TABLE ([NAME] NVARCHAR(500))  

DECLARE @TABLE_RETURN_01_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500))  

DECLARE @TABLE_RETURN_02 TABLE ([NAME] NVARCHAR(250))  

DECLARE @TABLE_RETURN_02_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500))  

DECLARE @TABLE_RETURN_03 TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500), AMT DECIMAL(18,2))  

DECLARE @TABLE_RETURN_04 TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(500), AMT DECIMAL(18,2))  

DECLARE @TABLE_RETURN_06 TABLE ([NAME] NVARCHAR(500), AMT DECIMAL(18,2))  

DECLARE @INDEX INT =1  

DECLARE @INDEX_TT INT =0  

DECLARE @HINHTHUC_CK NVARCHAR(100)  

DECLARE @CHUOI NVARCHAR(180)

IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'))  

BEGIN  

SET @INDEX_TT = @INDEX_TT+1  

--INSERT INTO @TABLE_RETURN  

--SELECT N'Tiền mặt' AS PHUONG_THUC, ISNULL(SUM (TOTAL_AMT),0) AS T1 FROM TR_REQ_PAY_METHOD  

--WHERE REQ_PAY_TYPE ='1' AND REQ_PAY_ID = @p_REQ_PAY_ID  

INSERT INTO @TABLE_RETURN_03  

SELECT N'. Người nhận: '+ ACC_NAME + CHAR(10) + N' CMND số: ' + ACC_NO + char(10) + N' Được cấp bởi: '+ ISSUED_BY + N' ngày cấp: '+ CONVERT(VARCHAR,ISSUED_DT,103) AS ACC_NAME, SUM(TOTAL_AMT) AS T2  

FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'  

GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT  

-- INSERT BANG TAM THE HIEN SO TIEN  

INSERT INTO @TABLE_RETURN_04  

SELECT 'N'+ CHAR(10) + 'D'+char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T2  

FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1'  

GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT  

END  

IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE <> '1'))  

BEGIN  

SET @INDEX_TT = @INDEX_TT+1  

--INSERT INTO @TABLE_RETURN  

--SELECT N'Chuyển khoản' AS PHUONG_THUC,ISNULL(SUM (TOTAL_AMT),0) AS T3 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_TYPE ='0' AND REQ_PAY_ID = @p_REQ_PAY_ID  

INSERT INTO @TABLE_RETURN_03  

SELECT CASE WHEN TYPE_TRANSFER ='R' AND TYPE_TRANSFER IS NOT NULL AND TYPE_TRANSFER <>'' THEN (N'. Trích từ tài khoản: '+ ACC_NAME + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY)  

ELSE (N'. Đơn vị thụ hưởng: '+ ACC_NAME + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY) END AS ACC_NAME, SUM(TOTAL_AMT) AS T4  

FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE <>'1'  

GROUP BY ACC_NO, ACC_NAME, ISSUED_BY,TYPE_TRANSFER  

-- INSERT BANG TAM THE HIEN SO TIEN  

INSERT INTO @TABLE_RETURN_04  

SELECT 'N'+ CHAR(10)+ 'S'+ char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T4  

FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE <>'1'  

GROUP BY ACC_NO, ACC_NAME, ISSUED_BY,TYPE_TRANSFER  

END  

-- KIEM TRA NOI DUNG SU DUNG CHI PHI VA NGAN SACH  

IF(EXISTS(SELECT * FROM TR_REQ_PAY_PERIOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_SERVICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))  

BEGIN  

--INSERT INTO @TABLE_RETURN_01 VALUES (CONVERT(VARCHAR(15),@INDEX) +N'. Nội dung tạm ứng: ')  

INSERT INTO @TABLE_RETURN_01 VALUES( N'A - Nội dung thanh toán: ')  

--INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_REASON FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  

--INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.TOTAL_AMT FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  

INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REASON FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  

INSERT INTO @TABLE_RETURN_06 SELECT LEFT(A.REASON,150) + LEFT(A.REASON,30), A.AMT_PAY FROM TR_REQ_PAY_PERIOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  

INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_DESC FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  

INSERT INTO @TABLE_RETURN_06 SELECT LEFT(A.REQ_PAY_DESC,150)+ LEFT(A.REQ_PAY_DESC,30), A.AMT_PAY_REAL FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  

INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.[SERVICE_NAME] FROM TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  

INSERT INTO @TABLE_RETURN_06 SELECT LEFT(A.[SERVICE_NAME],200)+LEFT(A.[SERVICE_NAME],70), A.TOTAL_AMT FROM TR_REQ_PAY_SERVICE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID  

END  

--- HĐ & CHỨNG TỪ ĐÍNH KÈM  

IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID) OR EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))  

BEGIN  

SET @INDEX = @INDEX+1  

--INSERT INTO @TABLE_RETURN_01 SELECT CONVERT(VARCHAR(15),@INDEX)+ N'. Chứng từ đính kèm: '  

INSERT INTO @TABLE_RETURN_02 SELECT N'B - Chứng từ đính kèm: '  

END  

IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='C'))  

BEGIN  

--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin hợp đồng đính kèm: '  

INSERT INTO @TABLE_RETURN_02_DT SELECT ' '+ N'. Hợp đồng số ' + REF_CODE + ISNULL(N' ngày '+ FORMAT(REF_DT,'dd/MM/yyyy'),'')+ CASE WHEN AMT >0 THEN N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') ELSE '' END AS T1 FROM TR_REQ_PAY_ATTACH  

WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='C'  

END  

IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='R'))  

BEGIN  

--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: '  

INSERT INTO @TABLE_RETURN_02_DT SELECT N'. '+ [DESCRIPTION]+ CASE WHEN AMT >0 THEN N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') ELSE '' END AS T1 FROM TR_REQ_PAY_ATTACH  

WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='R'  

END  

IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID))  

BEGIN  

--INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: '  

INSERT INTO @TABLE_RETURN_02_DT SELECT N'. Số hóa đơn '+ HD.INVOICE_NO +ISNULL(N' ngày '+ FORMAT(HD.INVOICE_DT,'dd/MM/yyyy'),'')+ N', số tiền '+ FORMAT( ISNULL(HD.PRICE+HD.VAT,0),'#,#', 'vi-VN') AS T1

FROM

(

SELECT MAX(A.INVOICE_NO) AS INVOICE_NO, MAX(A.INVOICE_DT) AS INVOICE_DT, SUM(A.PRICE) AS PRICE, SUM(A.VAT) AS VAT

FROM

TR_REQ_PAY_INVOICE  A

WHERE A.REQ_PAY_ID = @p_REQ_PAY_ID AND A.TYPE_FUNC='HC'  

GROUP BY A.INVOICE_NO,A.INVOICE_NO_SIGN,A.INVOICE_SIGN,A.TAX_NO

)

AS HD

END  

-- LAY THONG TIN CAP PHE DUYET  

IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_TYPE ='I'))  

BEGIN  

DECLARE @ROLE VARCHAR(15)  

DECLARE @TABLE_RETURN_07 TABLE (HDQT NVARCHAR(50),TGD NVARCHAR(50),PTGD NVARCHAR(50),GDK NVARCHAR(50),GDDV NVARCHAR(50))  

DECLARE @GDDV NVARCHAR(50),@GDK NVARCHAR(50),@PTGD NVARCHAR(50),@TGD NVARCHAR(50),@HDQT NVARCHAR(50)  

DECLARE @APPR VARCHAR(15),@APPR1 VARCHAR(15),@APPR2 VARCHAR(15),@APPR3 VARCHAR(15),@APPR4 VARCHAR(15),@APPR5 VARCHAR(15)  

DECLARE @POS NVARCHAR(50),@POS1 NVARCHAR(50),@POS2 NVARCHAR(50),@POS3 NVARCHAR(50),@POS4 NVARCHAR(50)  

DECLARE @DATE NVARCHAR(50),@DATE1 NVARCHAR(50),@DATE2 NVARCHAR(50),@DATE3 NVARCHAR(50),@DATE4 NVARCHAR(50)  

SET @APPR =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='1' AND  

ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='1'))  

SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@APPR)  

SET @APPR =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='1' AND  

ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='1') AND  

@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  

SET @POS =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR)  

SET @DATE =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='1'  

AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='1') AND (@APPR IS NOT NULL AND @APPR <>''))  

--IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='GDDV') BEGIN SET @GDDV =@APPR END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='GDK')BEGIN SET @GDK =@APPR END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='PTGD') BEGIN SET @PTGD =@APPR END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='TGD') BEGIN SET @TGD =@APPR END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR) ='HDQT') BEGIN SET @HDQT =@APPR END  

-------------  

SET @APPR1 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='2' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='2'))  

SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@APPR1)  

SET @APPR1 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='2' AND  

ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='2') AND  

@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  

SET @POS1 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR1)  

SET @DATE1 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='2' AND  

ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='2') AND (@APPR1 IS NOT NULL AND @APPR1 <>''))  

--IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDDV') BEGIN SET @GDDV =@APPR1 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDK')BEGIN SET @GDK =@APPR1 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='PTGD') BEGIN SET @PTGD =@APPR1 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='TGD') BEGIN SET @TGD =@APPR1 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='HDQT') BEGIN SET @HDQT =@APPR1 END  

------------  

SET @APPR2 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='3' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='3'))  

SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@APPR2)  

SET @APPR2 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='3' AND  

ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='3') AND  

@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  

SET @POS2 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR2)  

SET @DATE2 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='3' AND  

ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='3') AND (@APPR2 IS NOT NULL AND @APPR2 <>''))  

--IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDDV') BEGIN SET @GDDV =@APPR2 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDK')BEGIN SET @GDK =@APPR2 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='PTGD') BEGIN SET @PTGD =@APPR2 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='TGD') BEGIN SET @TGD =@APPR2 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='HDQT') BEGIN SET @HDQT =@APPR2 END  

SET @APPR3 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='4' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='4'))  

SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@APPR3)  

SET @APPR3 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='4' AND  

ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='4') AND  

@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  

SET @POS3 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR3)  

SET @DATE3 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='4'  

AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='4') AND (@APPR3 IS NOT NULL AND @APPR3 <>''))  

SET @APPR4 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='5' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='5'))  

SET @ROLE =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@APPR4)  

SET @APPR4 =(SELECT CHECKER_ID FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='5' AND  

ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='5') AND  

@ROLE IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID AND STATUS <>'C'))  

SET @POS4 =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@APPR4)  

SET @DATE4 =(SELECT ISNULL( FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='5' AND  

ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='5') AND @APPR4 IS NOT NULL AND @APPR4 <>'')  

DECLARE @BRANCH_TYPE VARCHAR(15), @BRANCH_RQ VARCHAR(15)  

SET @BRANCH_RQ =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)  

SET @BRANCH_TYPE = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)  

IF(@BRANCH_TYPE='CN')  

BEGIN  

SET @DATE =(SELECT FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt') FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)

SET @POS =(SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN= (SELECT CHECKER_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID))

INSERT INTO @TABLE_RETURN_07 VALUES ((SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR4),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3),  

(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR2),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR1),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = (SELECT CHECKER_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)))  

INSERT INTO @TABLE_RETURN_07 VALUES (@POS4,@POS3,@POS2,@POS1,@POS)  

INSERT INTO @TABLE_RETURN_07 VALUES (CASE WHEN @DATE4 <>'' THEN N'Đã chấp thuận '+ @DATE4 ELSE '' END,CASE WHEN @DATE3 <>'' THEN N'Đã chấp thuận '+ @DATE3 ELSE '' END,  

CASE WHEN @DATE2 <>'' THEN N'Đã chấp thuận '+ @DATE2 ELSE '' END,CASE WHEN @DATE1 <>'' THEN N'Đã chấp thuận '+ @DATE1 ELSE '' END,CASE WHEN @DATE <>'' THEN N'Đã chấp thuận '+ @DATE ELSE '' END)  

END  

ELSE  

BEGIN  

INSERT INTO @TABLE_RETURN_07 VALUES ((SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR4),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR3),  

(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR2),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR1),(SELECT TLFULLNAME FROM TL_USER WHERE TLNANME = @APPR))  

INSERT INTO @TABLE_RETURN_07 VALUES (@POS4,@POS3,@POS2,@POS1,@POS)  

INSERT INTO @TABLE_RETURN_07 VALUES (CASE WHEN @DATE4 <>'' THEN N'Đã chấp thuận '+ @DATE4 ELSE '' END,CASE WHEN @DATE3 <>'' THEN N'Đã chấp thuận '+ @DATE3 ELSE '' END,  

CASE WHEN @DATE2 <>'' THEN N'Đã chấp thuận '+ @DATE2 ELSE '' END,CASE WHEN @DATE1 <>'' THEN N'Đã chấp thuận '+ @DATE1 ELSE '' END,CASE WHEN @DATE <>'' THEN N'Đã chấp thuận '+ @DATE ELSE '' END)  

END  

--IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDDV') BEGIN SET @GDDV =@APPR2 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='GDK')BEGIN SET @GDK =@APPR2 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='PTGD') BEGIN SET @PTGD =@APPR2 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='TGD') BEGIN SET @TGD =@APPR2 END  

--ELSE IF((SELECT RoleName FROM TL_USER WHERE TLNANME =@APPR1) ='HDQT') BEGIN SET @HDQT =@APPR2 END  

END  

SELECT * FROM @TABLE_RETURN_01  

SELECT * FROM @TABLE_RETURN_01_DT  

SELECT * FROM @TABLE_RETURN_02  

SELECT * FROM @TABLE_RETURN_02_DT  

SELECT * FROM @TABLE_RETURN_03  

SELECT * FROM @TABLE_RETURN_04  

SELECT * FROM @TABLE_RETURN_06  

SELECT * FROM @TABLE_RETURN_07  

END