ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Appr] --Luanlt 2019/17/10 - Sửa params @p_REQ_PAY_ID varchar(15)= NULL, @p_CHECKER_ID varchar(15) = NULL, @p_AUTH_STATUS varchar(15) = NULL, @p_COST_ID VARCHAR(15) = NULL AS BEGIN TRANSACTION -- KHAI BAO THEM ROLE NHAN UY QUYEN DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50)) --INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT ROLENAME FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) AND CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) -- KHAI BAO NHUNG PHONG BAN MA 1 USER KIEM NHIEM --DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15)) --INSERT INTO @DEP_AUTH SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@p_CHECKER_ID AND CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) DECLARE @BRANCH_CREATE VARCHAR(15), @p_DEP_ID VARCHAR(15) --PRINT @BRANCH_CREATE SET @BRANCH_CREATE =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) DECLARE @DEP_CODE_NEXT VARCHAR(15) IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_CREATE)<>'HS') BEGIN SET @p_DEP_ID =(SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE LEFT(DEP_CODE,5) IN (SELECT TOP 1 LEFT(DVDM_CODE,5) FROM CM_DVDM WHERE DVDM_ID =@p_COST_ID)) SET @DEP_CODE_NEXT = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID =@p_DEP_ID) --PRINT @DEP_CODE_NEXT END ELSE BEGIN SET @p_DEP_ID =(SELECT DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID) SET @DEP_CODE_NEXT = (SELECT DEP_CODE FROM CM_DEPARTMENT WHERE DEP_ID =@p_DEP_ID) END IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE MAKER_ID =@p_CHECKER_ID AND REQ_PAY_ID =@p_REQ_PAY_ID) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Người phê duyệt phiếu phải khác với người tạo phiếu! Bạn không được phép duyệt đối tượng này' ErrorDesc RETURN '-1' END -- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc RETURN '-1' END -- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='R' AND REQ_PAY_ID =@p_REQ_PAY_ID) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đang được trả về bộ phận yêu cầu. Vui lòng cập nhật lại thông tin trước khi duyệt' ErrorDesc RETURN '-1' END -- KIEM TRA NEU DANG TRA VE THI PHAI CHO CAP NHAT LAI THONG TIN MOI DUOC PHEP DUYET IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đã được KSV phê duyệt trước đó' ErrorDesc RETURN '-1' END IF EXISTS (SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS ='A' AND REQ_PAY_ID =@p_REQ_PAY_ID) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đã được phê duyệt trước đó' ErrorDesc RETURN '-1' END --- KHAI BAO CHUUNG DECLARE @ROLE_ID VARCHAR(200), @BRANCH_TYPE VARCHAR(15), @TOTAL_ADVANCE DECIMAL(18,0), @TOTAL_PAYBACK DECIMAL(18,0), @BRANCH_ID VARCHAR(15), @DEP_ID VARCHAR(15), @COSTCENTER_ID VARCHAR(15)= NULL, @BRANCH_RQ VARCHAR(15) = NULL,@DEP_ID_RQ VARCHAR(15), @BRANCH_LOGIN VARCHAR(15),@LIMIT_ONE_OF DECIMAL(18,2) DECLARE @LIMIT_AMT DECIMAL(18,0), @REQ_AMT DECIMAL(18,2) =0, @TONG_PGD DECIMAL(18,0), @TONG_PGD_HOAN DECIMAL(18,0) SET @REQ_AMT = (SELECT REQ_AMT *ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) --SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID) SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID) --SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN) IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC')) BEGIN PRINT @ROLE_ID END ELSE BEGIN SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_CHECKER_ID) IF(@ROLE_ID IS NULL OR @ROLE_ID ='') BEGIN SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID)) END END SET @BRANCH_ID = (SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) SET @BRANCH_RQ =(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) SET @DEP_ID_RQ =(SELECT DEP_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) DECLARE @tmp table(BRANCH_ID varchar(15)) INSERT into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_ID) DECLARE @tmp_CN table(BRANCH_ID varchar(15)) IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='CN') BEGIN --INSERT into @tmp_CN SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_RQ) INSERT into @tmp_CN VALUES (@BRANCH_RQ) END ELSE IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='PGD') BEGIN --DECLARE @FATHER_ID VARCHAR(15) = NULL --SET @FATHER_ID =(SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ) --INSERT into @tmp_CN SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@FATHER_ID) INSERT into @tmp_CN VALUES (@BRANCH_RQ) END --DECLARE @DEP_ID_LG VARCHAR(15) = NULL --SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_CHECKER_ID) DECLARE @TMP_DVDM TABLE(DVDM_ID VARCHAR(15)) INSERT INTO @TMP_DVDM SELECT A.DVDM_ID--, A.COST_CODE, A.COST_NAME, B.DEP_ID --, C.DEP_CODE, C.DEP_NAME FROM PL_COSTCENTER A LEFT JOIN PL_COSTCENTER_DT B ON A.COST_ID = B.COST_ID WHERE B.DEP_ID = @DEP_ID_RQ GROUP BY A.DVDM_ID -- KHAI BAO BRANCH CUA USER DUYET SET @BRANCH_LOGIN = (SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) DECLARE @LIMIT_REMAIN DECIMAL(18,0) --CAP NHAT CODE TRONG QUA TRINH TEST UAT IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_TYPE='I')) BEGIN IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER=@ROLE_ID AND REQ_ID = @p_REQ_PAY_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn không có quyền phê duyệt phiếu tạm ứng nội bộ. Vui lòng chọn giao dịch khác để duyệt' ErrorDesc RETURN '-1' END --IF(EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER=@ROLE_ID AND REQ_ID = @p_REQ_PAY_ID AND BRANCH_ID= @BRANCH_ID AND STATUS ='P')) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn đã thực hiện xác nhận phiếu tạm ứng trước đó' ErrorDesc -- RETURN '-1' --END -- KIEM TRA AUTH_STATUS TRUYEN XUONG LA GI, A: DUYET, C: CONFIRM IF(@p_AUTH_STATUS='U') -- KIEM TRA HAN MUC CON LAI CUA USER VA THONG BAO CHO NGUOI DUYET BEGIN -- BAT DAU DUYET THEO NGAN SACH --- LAY HAN MUC CUA USER SET @LIMIT_AMT =(SELECT LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN) print @LIMIT_AMT -- SET @LIMIT_ONE_OF =(SELECT LIMIT_PERCENT FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN) IF(@ROLE_ID ='GDK' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011')) BEGIN SET @LIMIT_AMT =1000000000 END IF(@ROLE_ID ='PTGD' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011')) BEGIN SET @LIMIT_AMT =3000000000 END -- NEU LA HOI SO THI LAY TONG SO TIEN TAM UNG CUA CAC PHONG BAN CHUNG VOI KHOI CUA PHIEU DANG DUOC DUYET SET @TONG_PGD =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID = @BRANCH_RQ AND REQ_TYPE ='I' AND AUTH_STATUS ='A') + @REQ_AMT SET @TONG_PGD_HOAN =( --SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT)) --FROM TR_REQ_PAYMENT_DT B --INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID --INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID SELECT SUM (ISNULL(A.PAY_AMT,0)) FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.BRANCH_ID = @BRANCH_RQ AND A.AUTH_STATUS_KT ='A' AND A.REQ_TYPE='I') IF(@BRANCH_TYPE='HS') BEGIN SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE DEP_ID IN (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ) AND REQ_TYPE ='I' AND AUTH_STATUS ='A'),0) SET @TOTAL_PAYBACK = ISNULL( ( SELECT (SUM(ISNULL(C.PAY_AMT,0))) FROM TR_REQ_ADVANCE_PAYMENT C WHERE C.DEP_ID IN (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_ID) AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I' ),0) END -- NEU PHIEU DUOC TAO KHAC VOI HOI SO THI TIEN HANH TINH TONG SO TIEN TAM UNG CUA CN VA PHONG GIAO DICH TRUC THUOC ELSE IF(@BRANCH_TYPE <>'HS') BEGIN SET @TOTAL_ADVANCE =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN) AND REQ_TYPE ='I' AND AUTH_STATUS='A') SET @TOTAL_PAYBACK = ( SELECT (SUM(ISNULL(B.PAY_AMT,0))) FROM TR_REQ_ADVANCE_PAYMENT B WHERE B.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN) AND B.AUTH_STATUS_KT ='A' AND B.REQ_TYPE='I' ) END SET @LIMIT_REMAIN =ISNULL(@TOTAL_ADVANCE,0) - ISNULL(@TOTAL_PAYBACK,0) -- KIEM TRA NEU LA CHU TICH HDQT THI PHE DUYET LUON, KHONG CAN XET HAN MUC GI IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName ='HDQT')) BEGIN ROLLBACK TRANSACTION SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, --N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') --+ CHAR(10) + --N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') --+ CHAR(10) + CHAR(13) + N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc RETURN '-4' END IF(@LIMIT_AMT <(SELECT REQ_AMT * ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) + @LIMIT_REMAIN) BEGIN ROLLBACK TRANSACTION SELECT '-2' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Tổng hạn mức phê duyệt là: '+ FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') + CHAR(10)+ N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') + CHAR(10) + CHAR(13)+ N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN +@REQ_AMT ,'#,#', 'vi-VN') + CHAR(10) + N'Số tiền tạm ứng đã vượt mức phê duyệt là: '+ FORMAT((@LIMIT_REMAIN +@REQ_AMT) -@LIMIT_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Bạn muốn chuyển giao dịch lên cấp cao hơn để duyệt hay tiếp tục chờ duyệt' ErrorDesc RETURN '-2' END --- NEU DU HAN MUC THI THONG BAO DE NGUOI DUYET CAN NHAC CO NEN DUYET PHIEU HAY KHONG IF(@LIMIT_AMT >=(SELECT REQ_AMT * ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) +@LIMIT_REMAIN) BEGIN IF(@REQ_AMT >@LIMIT_ONE_OF) BEGIN ROLLBACK TRANSACTION SELECT '-2' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Tổng hạn mức phê duyệt là: '+ FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') + CHAR(10)+ N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') + CHAR(10) + CHAR(13) + N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN +@REQ_AMT ,'#,#', 'vi-VN') + CHAR(10) + N'Số tiền tạm ứng đã vượt mức so với số tiền của một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') + CHAR(10) + N'Bạn muốn chuyển giao dịch lên cấp cao hơn để duyệt hay tiếp tục chờ duyệt' ErrorDesc RETURN '-2' END ELSE BEGIN -- KIEM TRA NEU LA PGD THI CANH BAO IF(@BRANCH_TYPE ='HS') BEGIN IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName NOT IN('TGD','HQDT'))) BEGIN ROLLBACK TRANSACTION SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') + CHAR(10) + CHAR(13) + N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') + CHAR(10) + N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc RETURN '-4' END ELSE BEGIN ROLLBACK TRANSACTION SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, --N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') --+ CHAR(10) + CHAR(13) + --N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') --+ CHAR(10) + N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc RETURN '-4' END END ELSE IF(@BRANCH_TYPE ='CN') BEGIN IF(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0) > 40000000) BEGIN ROLLBACK TRANSACTION SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') + CHAR(10) + CHAR(13) + N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Số tiền tạm ứng đang vượt quá số tổng hạn mức tạm ứng của 1 CN: '+ FORMAT(40000000,'#,#', 'vi-VN') +N'. Số tiền vượt là: '+ FORMAT(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0)-40000000,'#,#', 'vi-VN') + CHAR(10) + N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc RETURN '-4' END ELSE BEGIN IF(EXISTS(SELECT * FROM TL_USER WHERE TLNANME=@p_CHECKER_ID AND RoleName NOT IN('TGD','HQDT'))) BEGIN ROLLBACK TRANSACTION SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') + CHAR(10) + CHAR(13) + N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc RETURN '-4' END ELSE BEGIN ROLLBACK TRANSACTION SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, --N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') --+ CHAR(10) + --N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') --+ CHAR(10) + CHAR(13) + N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc RETURN '-4' END END END ELSE IF(@BRANCH_TYPE ='PGD') BEGIN IF(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0) > 10000000) BEGIN ROLLBACK TRANSACTION SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN')+ CHAR(10) + N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') + CHAR(10) + CHAR(13) + N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Số tiền tạm ứng đang vượt quá số tổng hạn mức tạm ứng của 1 PGD: '+ FORMAT(10000000,'#,#', 'vi-VN') +N'. Số tiền vượt là: '+ FORMAT(ISNULL(@TONG_PGD,0) -ISNULL(@TONG_PGD_HOAN,0)-10000000,'#,#', 'vi-VN') + CHAR(10) + N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc RETURN '-4' END ELSE BEGIN ROLLBACK TRANSACTION SELECT '-4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Tổng hạn mức phê duyệt của bạn là: '+FORMAT(@LIMIT_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Hạn mức trên một lần phê duyệt là: '+ FORMAT(@LIMIT_ONE_OF,'#,#', 'vi-VN') + CHAR(10) + CHAR(13) + N'Số dư tạm ứng lũy kế của đơn vị tính tới thời điểm này là : '+ FORMAT(@LIMIT_REMAIN+@REQ_AMT,'#,#', 'vi-VN') + CHAR(10) + N'Bạn có muốn tiếp tục duyệt hay không' ErrorDesc RETURN '-4' END END END END -- NEU THOA MAN CAC DIEU KIEN SE TIEN HANH DUYET END -- END DU HAN MUC DUYET ELSE IF(@p_AUTH_STATUS='A') BEGIN UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS='A', CHECKER_ID = @p_CHECKER_ID, APPROVE_DT = GETDATE(), AUTH_STATUS_KT='U' WHERE REQ_PAY_ID = @p_REQ_PAY_ID --- CAP NHAT LAI TINH TRANG TRONG REQUEST_PROCESS DECLARE @PROCESS_CURR VARCHAR(5) SET @PROCESS_CURR =(SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND ROLE_USER =@ROLE_ID) INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@PROCESS_CURR,@p_CHECKER_ID, GETDATE(),(SELECT ROLE_DESC + N' duyệt' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),N'Các cấp phê duyệt theo hạn mức') --- CAP NHAT PROCESS CUA PHIEU DE NGHI TAM UNG LA DA DUYET UPDATE TR_REQ_ADVANCE_PAYMENT SET PROCESS=@PROCESS_CURR WHERE REQ_PAY_ID=@p_REQ_PAY_ID -- UPDATE PL_REQUEST_PROCESS SET STATUS ='A', NOTES = (SELECT ROLE_DESC + N' duyệt' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID) WHERE ROLE_USER =@ROLE_ID AND REQ_ID = @p_REQ_PAY_ID DECLARE @l_REQ_AMT DECIMAL(18,0) = (SELECT REQ_AMT*ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID) SET @LIMIT_AMT =(SELECT LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN) -- SET @LIMIT_ONE_OF =(SELECT LIMIT_PERCENT FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@ROLE_ID AND LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@BRANCH_LOGIN) IF(@ROLE_ID ='GDK' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011')) BEGIN SET @LIMIT_AMT =1000000000 END IF(@ROLE_ID ='PTGD' AND ((SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) ='DEP000000000011')) BEGIN SET @LIMIT_AMT =3000000000 END -- NEU LA HOI SO THI LAY TONG SO TIEN TAM UNG CUA CAC PHONG BAN CHUNG VOI KHOI CUA PHIEU DANG DUOC DUYET SET @TONG_PGD =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID = @BRANCH_RQ AND REQ_TYPE ='I' AND AUTH_STATUS ='A') + @REQ_AMT SET @TONG_PGD_HOAN =( --SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT)) --FROM TR_REQ_PAYMENT_DT B --INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID --INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID SELECT SUM (ISNULL(A.PAY_AMT,0)) FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.BRANCH_ID = @BRANCH_RQ AND A.AUTH_STATUS_KT ='A' AND A.REQ_TYPE='I') IF(@BRANCH_TYPE='HS') BEGIN SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE DEP_ID IN (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ) AND REQ_TYPE ='I' AND AUTH_STATUS ='A'),0) SET @TOTAL_PAYBACK = ISNULL( ( SELECT (SUM(ISNULL(C.PAY_AMT,0))) FROM TR_REQ_ADVANCE_PAYMENT C WHERE C.DEP_ID IN (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_ID) AND C.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I' ),0) END -- NEU PHIEU DUOC TAO KHAC VOI HOI SO THI TIEN HANH TINH TONG SO TIEN TAM UNG CUA CN VA PHONG GIAO DICH TRUC THUOC ELSE IF(@BRANCH_TYPE <>'HS') BEGIN SET @TOTAL_ADVANCE =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN) AND REQ_TYPE ='I' AND AUTH_STATUS='A') SET @TOTAL_PAYBACK = ( SELECT (SUM(ISNULL(B.PAY_AMT,0))) FROM TR_REQ_ADVANCE_PAYMENT B WHERE B.BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN) AND B.AUTH_STATUS_KT ='A' AND B.REQ_TYPE='I' ) END SET @LIMIT_REMAIN =ISNULL(@TOTAL_ADVANCE,0) - ISNULL(@TOTAL_PAYBACK,0) --- INSERT VAO BANG LOG INSERT INTO TR_REQ_ADVANCE_LIMIT_LOG VALUES (@p_REQ_PAY_ID,@LIMIT_REMAIN,GETDATE()) ---- END ELSE -- NEW KHONG DU HAN MUC THI BAT DAU XAC NHAN VA DUYET THEO HAN MUC BEGIN DECLARE @t_REQ_AMT DECIMAL(18,0) = (SELECT REQ_AMT*ISNULL(RATE,1) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID) --PRINT @t_REQ_AMT --- NEU GIAM DOC KHOI VAN CHUA DU HAN MUC THI GIAM DOC KHOI XAC NHAN VÀ TIEP TUC BUOC TIEP THEO LA PHO TONG GIAM DOC DECLARE @MAX_STEP INT, @NEXT_ROLE VARCHAR(20), @MESSAGE NVARCHAR(100), @PROCESS_ID_NEXT VARCHAR(5), @PROCESS_PARENT VARCHAR(5), @BRANCH_ID_PROC VARCHAR(15), @NEX_ROLE_STEP VARCHAR(25), @CURRENT_PROCESS VARCHAR(15) SET @CURRENT_PROCESS =(SELECT PROCESS_ID FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND ROLE_USER=@ROLE_ID) SET @NEX_ROLE_STEP = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE PARENT_PROCESS_ID =@CURRENT_PROCESS AND REQ_ID = @p_REQ_PAY_ID ) --PRINT @NEX_ROLE_STEP --SET @MAX_STEP = CONVERT(INT,(SELECT MAX(PROCESS_ID) FROM TR_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_PAY_ID)) SET @PROCESS_ID_NEXT = CONVERT(VARCHAR(5),@CURRENT_PROCESS+1) --SET @PROCESS_PARENT = CONVERT(VARCHAR(5),@MAX_STEP) DECLARE @TMP_ROLE TABLE (ROLE_ID VARCHAR(15), BRANCH_ID VARCHAR(15), LIMIT_VALUE DECIMAL(18,0),LIMIT_TYPE VARCHAR(15)) --IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06908%' AND DEP_ID =@p_DEP_ID) IF(LEFT(@DEP_CODE_NEXT,5) ='06908' OR @DEP_CODE_NEXT ='0690605' OR @DEP_CODE_NEXT ='0690405') BEGIN INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK' END --ELSE IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06910%' AND DEP_ID =@p_DEP_ID) ELSE IF(LEFT(@DEP_CODE_NEXT,5) ='06910') BEGIN INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK' END --ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06900%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06901%' AND DEP_ID =@p_DEP_ID) --OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06902%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06903%' AND DEP_ID =@p_DEP_ID) --OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06904%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06920%' AND DEP_ID =@p_DEP_ID)) ELSE IF((LEFT(@DEP_CODE_NEXT,5) ='06900') OR (LEFT(@DEP_CODE_NEXT,5)) ='06901' OR (LEFT(@DEP_CODE_NEXT,5))='06904' OR (LEFT(@DEP_CODE_NEXT,5))='06902' OR (LEFT(@DEP_CODE_NEXT,5))='06903' OR (LEFT(@DEP_CODE_NEXT,5))='06920') BEGIN INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID NOT IN ('GDK','PTGD') END --ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06907%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06909%' AND DEP_ID =@p_DEP_ID) --OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06911%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06921%' AND DEP_ID =@p_DEP_ID) --OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06922%' AND DEP_ID =@p_DEP_ID)) ELSE IF((LEFT(@DEP_CODE_NEXT,5) ='06907') OR (LEFT(@DEP_CODE_NEXT,5)) ='06909' OR (LEFT(@DEP_CODE_NEXT,5))='06911' OR (LEFT(@DEP_CODE_NEXT,5))='06922' OR (LEFT(@DEP_CODE_NEXT,5))='06921' OR (LEFT(@DEP_CODE_NEXT,5))='06920') BEGIN INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'PTGD' END ELSE BEGIN INSERT INTO @TMP_ROLE SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE,A.LIMIT_TYPE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' END SET @BRANCH_ID_PROC= (SELECT BRANCH_ID FROM @TMP_ROLE WHERE ROLE_ID=@NEXT_ROLE AND LIMIT_TYPE='ADV_PAY') print @DEP_CODE_NEXT print @BRANCH_ID_PROC print @NEX_ROLE_STEP IF(NOT EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE ROLE_USER=@NEX_ROLE_STEP AND REQ_ID= @p_REQ_PAY_ID)) BEGIN --SET @NEXT_ROLE =(SELECT TOP 1 ROLE_ID FROM @TMP_ROLE WHERE LIMIT_VALUE >@t_REQ_AMT SET @NEXT_ROLE =(SELECT TOP 1 ROLE_ID FROM @TMP_ROLE WHERE 1 =1 AND ROLE_ID NOT IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID) AND LIMIT_TYPE ='ADV_PAY') --print @NEXT_ROLE SET @BRANCH_ID_PROC= (SELECT BRANCH_ID FROM @TMP_ROLE WHERE ROLE_ID=@NEXT_ROLE AND LIMIT_TYPE='ADV_PAY') SET @MESSAGE = (SELECT N'Đợi '+ ROLE_DESC + N' phê duyệt' FROM TL_SYSROLE WHERE ROLE_ID = @NEXT_ROLE) INSERT INTO PL_REQUEST_PROCESS (REQ_ID,PROCESS_ID,STATUS,ROLE_USER,BRANCH_ID,CHECKER_ID,APPROVE_DT,PARENT_PROCESS_ID,IS_LEAF, COST_ID, DVDM_ID, NOTES) VALUES (@p_REQ_PAY_ID,@PROCESS_ID_NEXT,'C',@NEXT_ROLE,@BRANCH_ID_PROC,@NEXT_ROLE,GETDATE(),@PROCESS_PARENT,'Y',NULL,@p_COST_ID,@MESSAGE) INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@CURRENT_PROCESS,@p_CHECKER_ID, GETDATE(),(SELECT ROLE_DESC + N' xác nhận' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),N'Các cấp phê duyệt theo hạn mức') END ELSE BEGIN --print @NEX_ROLE_STEP SET @NEXT_ROLE =@NEX_ROLE_STEP UPDATE PL_REQUEST_PROCESS SET STATUS='C',DVDM_ID=@p_COST_ID WHERE ROLE_USER =@NEXT_ROLE AND REQ_ID = @p_REQ_PAY_ID INSERT INTO PL_PROCESS VALUES (@p_REQ_PAY_ID,@CURRENT_PROCESS,@p_CHECKER_ID, GETDATE(),(SELECT ROLE_DESC + N' xác nhận' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID),N'Các cấp phê duyệt theo hạn mức') END -- IF @@Error <> 0 GOTO ABORT -- UPDATE STATUS CUA STEP HIEN TAI UPDATE PL_REQUEST_PROCESS SET STATUS='P',IS_LEAF ='N', NOTES = (SELECT TOP 1 ROLE_DESC + N' xác nhận' FROM TL_SYSROLE WHERE ROLE_ID =@ROLE_ID) WHERE ROLE_USER IN(SELECT * FROM @TABLE_ROLE) AND REQ_ID = @p_REQ_PAY_ID --INSERT VAO TR_PROCESS UPDATE TR_REQ_ADVANCE_PAYMENT SET PROCESS =@CURRENT_PROCESS , AUTH_STATUS='U',DVDM_ID = @p_COST_ID WHERE REQ_PAY_ID =@p_REQ_PAY_ID --- UPDATE PL_REQUEST_PROCESS SET DVDM_ID= @p_COST_ID WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER <>@ROLE_ID END END ELSE BEGIN --- NEU LA TAM UNG NHA CUNG CAP THI CHECK XEM KI TAM UNG DO DA HOAN THANH HAY CHUA, NEU DA HOAN THANH THI KHONG CHO DUYET DECLARE @PAY_ID VARCHAR(15), @SUM_OF_PAY_ID DECIMAL(18,2), @SUM_ADVANCE DECIMAL(18,2), @AMOUNT DECIMAL(18,2),@AMT_PAY_DO DECIMAL(18,2),@SCH_ID VARCHAR(15),@AMT_ADVANCE DECIMAL(18,0) DECLARE CUR_SH CURSOR FOR SELECT A.PAY_ID,A.AMT_PAY, A.AMT_PAY_DO,A.SCHEDULE_ID,A.AMT_ADVANCE FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID= @p_REQ_PAY_ID OPEN CUR_SH FETCH NEXT FROM CUR_SH INTO @PAY_ID,@AMOUNT,@AMT_PAY_DO,@SCH_ID,@AMT_ADVANCE WHILE @@FETCH_STATUS =0 BEGIN SET @SUM_OF_PAY_ID =(SELECT SUM(AMT_PAY_DO) FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND AUTH_STATUS_KT='A') SET @SUM_ADVANCE =(SELECT SUM(AMT_ADVANCE) FROM TR_REQ_PAY_SCHEDULE WHERE PAY_ID =@PAY_ID AND AUTH_STATUS_KT='A') IF(@SUM_OF_PAY_ID +@AMT_PAY_DO >@AMOUNT) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Số tiền tạm ứng đã vượt mức số tiền còn lại cần phải thanh toán' RETURN '-1' END IF(@SUM_OF_PAY_ID+@AMT_PAY_DO+@SUM_ADVANCE < @AMOUNT) BEGIN UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTU' WHERE SCHEDULE_ID=@SCH_ID END ELSE IF(@SUM_OF_PAY_ID+@AMT_PAY_DO+@SUM_ADVANCE = @AMOUNT) BEGIN UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='DTT' WHERE SCHEDULE_ID=@SCH_ID END ELSE IF(@SUM_OF_PAY_ID=0 AND @SUM_ADVANCE =0 ) BEGIN UPDATE TR_REQ_PAY_SCHEDULE SET PAYMENT_STATUS ='CTT' WHERE SCHEDULE_ID=@SCH_ID END FETCH NEXT FROM CUR_SH INTO @PAY_ID,@AMOUNT,@AMT_PAY_DO,@SCH_ID,@AMT_ADVANCE END CLOSE CUR_SH DEALLOCATE CUR_SH UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS='A', CHECKER_ID=@p_CHECKER_ID, APPROVE_DT = GETDATE(),AUTH_STATUS_KT='U' WHERE REQ_PAY_ID =@p_REQ_PAY_ID SET @p_AUTH_STATUS ='A' --- INSERT 1 DONG VAO PL_PROCESS -- INSERT VAO BANG PL_PROCESS 1 DONG TRA VE INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES) VALUES(@p_REQ_PAY_ID,'APP',@p_CHECKER_ID,GETDATE(), N'Trưởng đơn vị đã phê duyệt',N'Trưởng đơn vị phê duyệt') END IF @@Error <> 0 GOTO ABORT COMMIT TRANSACTION IF(@p_AUTH_STATUS='A') BEGIN SELECT '0' as Result, @p_REQ_PAY_ID REQ_PAY_ID ,N'12345' +@DEP_CODE_NEXT ErrorDesc RETURN '0' END ELSE BEGIN SELECT '1' as Result, @p_REQ_PAY_ID REQ_PAY_ID ,'' ErrorDesc RETURN '1' END ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc RETURN '-1' END ¿ ALTER PROC [dbo].[TL_SYSROLE_LIMIT_Auto] @p_BRANCH_ID VARCHAR(15) = NULL, @p_LIMIT_TYPE VARCHAR(15) = NULL AS BEGIN TRANSACTION IF(@p_LIMIT_TYPE='ADV_PAY') BEGIN DECLARE @BRANCH_ID VARCHAR(15), @BRANCH_NAMME NVARCHAR(200), @BRANCH_TYPE VARCHAR(15),@p_LIMIT_ID VARCHAR(15), @TOTAL_PGD DECIMAL(18,0), @DMMS_ID VARCHAR(15), @LIMIT_PGD DECIMAL(18,0), @LIMIT_CN DECIMAL(18,0) SET @LIMIT_PGD =10000000 SET @LIMIT_CN =40000000 DECLARE CUR_DV CURSOR FOR SELECT A.BRANCH_ID, A.BRANCH_NAME, A.BRANCH_TYPE FROM CM_BRANCH A WHERE A.BRANCH_TYPE IN ('CN','PGD') AND (A.BRANCH_ID =@p_BRANCH_ID OR @p_BRANCH_ID IS NULL OR @p_BRANCH_ID ='') OPEN CUR_DV FETCH NEXT FROM CUR_DV INTO @BRANCH_ID,@BRANCH_NAMME,@BRANCH_TYPE WHILE @@FETCH_STATUS = 0 BEGIN --SET @TOTAL_PGD =(SELECT COUNT(*) FROM CM_BRANCH WHERE FATHER_ID =@BRANCH_ID)*10000000 SET @TOTAL_PGD =0 EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out IF(@BRANCH_TYPE ='CN' AND NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='GDDV')) BEGIN INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'GDDV',@LIMIT_CN+@TOTAL_PGD,@BRANCH_NAMME,'admin','A','tscd_hoiso',GETDATE(),'1',@BRANCH_ID,'ADV_PAY', @LIMIT_CN, NULL) END ELSE IF(@BRANCH_TYPE ='PGD' AND NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='TPGD' AND @BRANCH_TYPE ='PGD')) BEGIN INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'TPGD',@LIMIT_PGD,@BRANCH_NAMME,'admin','A','tscd_hoiso',GETDATE(),'1',@BRANCH_ID,'ADV_PAY', @LIMIT_PGD, NULL) END ELSE IF (@BRANCH_TYPE ='CN' AND EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='GDDV')) BEGIN UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =@LIMIT_CN +@TOTAL_PGD, LIMIT_PERCENT= @LIMIT_CN WHERE BRANCH_ID =@BRANCH_ID AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='GDDV' END -- KIỂM TRA XEM ĐÃ KHAI BÁO ĐƠN VỊ / CHI NHÁNH NÀY TRONG CM_DMMS HAY CHƯA IF(NOT EXISTS (SELECT * FROM CM_DMMS WHERE BRANCH_ID =@BRANCH_ID)) BEGIN EXEC SYS_CodeMasters_Gen 'CM_DMMS', @DMMS_ID out INSERT INTO CM_DMMS (DMMS_ID,BRANCH_ID, DEP_ID,DVDM_ID) VALUES (@DMMS_ID,@BRANCH_ID,(SELECT TOP 1 DEP_ID FROM CM_DEPARTMENT WHERE BRANCH_ID =@BRANCH_ID),'') END FETCH NEXT FROM CUR_DV INTO @BRANCH_ID,@BRANCH_NAMME,@BRANCH_TYPE END CLOSE CUR_DV DEALLOCATE CUR_DV END ELSE BEGIN -- INSERT HAN MUC CHO HOI SO -- GIAM DOC KHOI IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='GDK')) BEGIN EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'GDK',100000000,N'HAN MUC GIAM DOC KHOI','admin','A','tscd_hoiso',GETDATE(),'1','DV0001','ADV_PAY',50000000,NULL) END BEGIN UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =100000000, LIMIT_PERCENT= 50000000 WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='GDK' END -- PHO TONG GIAM DOC IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='PTGD')) BEGIN EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'PTGD',200000000,N'HAN MUC PHE DUYET PHO TONG GIAM DOC','admin','A','tscd_hoiso',GETDATE(),'1','DV0001','ADV_PAY',100000000,NULL) END BEGIN UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =200000000, LIMIT_PERCENT= 100000000 WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='PTGD' END IF(NOT EXISTS(SELECT * FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE='ADV_PAY' AND ROLE_ID ='TGD')) BEGIN EXEC SYS_CodeMasters_Gen 'TL_SYSROLE_LIMIT', @p_LIMIT_ID out INSERT INTO TL_SYSROLE_LIMIT VALUES (@p_LIMIT_ID,'TGD',2000000000,N'HAN MUC PHE DUYET TONG GIAM DOC','admin','A','tscd_hoiso',GETDATE(),'1','DV0001','ADV_PAY',500000000,NULL) END BEGIN UPDATE TL_SYSROLE_LIMIT SET LIMIT_VALUE =2000000000, LIMIT_PERCENT= 500000000 WHERE BRANCH_ID ='DV0001' AND LIMIT_TYPE ='ADV_PAY' AND ROLE_ID ='TGD' END END COMMIT TRANSACTION SELECT '0' as Result, '' LIMIT_ID, (SELECT TOP 1 LIMIT_VALUE FROM TL_SYSROLE_LIMIT WHERE BRANCH_ID =@p_BRANCH_ID AND LIMIT_TYPE ='ADV_PAY') AS LIMIT_VALUE,'' ErrorDesc RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' LIMIT_ID, 0.0 AS LIMIT_VALUE, '' ErrorDesc RETURN '-1' END ¿ UPDATE SYS_CODEMASTERS SET CurValue = CurValue+300 WHERE Prefix ='DMMS' ¿ ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_App] @p_REQ_ID VARCHAR(15) = NULL, @p_AUTH_STATUS VARCHAR(1) = NULL, @p_CHECKER_ID varchar(15) = NULL, @p_APPROVE_DT DATETIME = NULL, @p_ROLE_LOGIN VARCHAR(50) = NULL, @p_BRANCH_LOGIN VARCHAR(15), @p_PROCESS_DES NVARCHAR(500) AS --Validation is here DECLARE @ERRORSYS NVARCHAR(15) = '' IF ( NOT EXISTS ( SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID = @p_REQ_ID)) SET @ERRORSYS = 'REQ-00002' IF @ERRORSYS <> '' BEGIN SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS RETURN '0' END BEGIN TRANSACTION -- LUCTV 20052020 BO SUNG CHECK NEU TRA VE THI KHONG DUYET DUOC IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND AUTH_STATUS ='R')) BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang bị từ chối. Vui lòng đợi nhân viên xử lý phiếu và gửi phê duyệt lại!' ErrorDesc RETURN '-1' END --- PHE DUYET TRUNG GIAN DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE_N VARCHAR(20) ,@DEP_CREATE_N VARCHAR(20),@BRANCH_TYPE VARCHAR(10), @BRANCH_CREATE_TYPE VARCHAR(10) SELECT @BRANCH_CREATE_N=BRANCH_CREATE,@DEP_CREATE_N=DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID SET @BRANCH_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID) SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE_N) -- LUCTV 08122020 KIỂM TRA NẾU TỜ TRÌNH LINK TỚI PYCMS LÀ TỜ TRÌNH TOÀN HÀNG THÌ ĐMMS GIAO CHO ĐƠN VỊ TỰ MUA SẮM DECLARE @PL_REQ_REF VARCHAR(15), @ISCHECK_ALL VARCHAR(1) SET @PL_REQ_REF =(SELECT TOP 1 PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID) SET @ISCHECK_ALL =(SELECT TOP 1 IS_CHECKALL FROM PL_REQUEST_DOC WHERE REQ_ID =@PL_REQ_REF) -- LUCTV 08122020: END IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER =@p_CHECKER_ID AND PROCESS_ID ='SIGN')) BEGIN DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID INSERT INTO dbo.PL_PROCESS ( REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC,NOTES ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'SIGN', -- PROCESS_ID - varchar(10) @p_CHECKER_ID, -- CHECKER_ID - varchar(15) @p_APPROVE_DT , -- APPROVE_DT - datetime N'Cấp phê duyệt trung gian xác nhận phiếu yêu cầu mua sắm', N'Cấp phê duyệt trung gian' ) --- DUA CAP PHE DUYET TRUONG DON VI --IF(@BRANCH_CREATE_TYPE='PGD') -- SET @BRANCH_CREATE_N=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE_N) INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, DEP_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'APPNEW', -- PROCESS_ID - varchar(10) 'C', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) @BRANCH_CREATE_N, @DEP_CREATE_N, -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime '', -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', -- DVDM_ID - varchar(15) N'Chờ trưởng đơn vị phê duyệt', -- NOTES - nvarchar(500) NULL -- IS_HAS_CHILD - bit ) --- UPDATE PROCESS_ID VE APP_NEW UPDATE TR_REQUEST_DOC SET PROCESS_ID ='APPNEW' WHERE REQ_ID =@p_REQ_ID END ELSE --- PHE DUYET GIU NGUYEN NHU THUONG BEGIN DECLARE @ROLE_USER_LOGIN VARCHAR(15)='' SET @ROLE_USER_LOGIN =(SELECT RoleName FROM TL_USER WHERE TLNANME =@p_CHECKER_ID) --- KIEM TRA XEM DA PHE DUYET TRUNG GIAN HAY CHUA IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <> '')) BEGIN IF(NOT EXISTS (SELECT * FROM PL_PROCESS WHERE PROCESS_ID='SIGN' AND REQ_ID =@p_REQ_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang đợi cấp phê duyệt trung gian xác nhận. Vui lòng đợi nhân viên '+(SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)+' xác nhận phiếu!' ErrorDesc RETURN '-1' END IF(@p_CHECKER_ID = (SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đang đợi trưởng đơn vị phê duyệt. Bạn không có thẩm quyền phê duyệt cấp trưởng đơn vị! Vui lòng xem lịch sử xử lý phiếu' ErrorDesc RETURN '-1' END END -- LUCTV: 08 12 2020: KIỂM TRA NẾU CẤP DUYỆT KHÔNG NẰM TRONG CẤP TP, PP THÌ KHÔNG CHO DUYỆT TRƯỞNG ĐƠN VỊ --IF( DELETE FROM dbo.PL_REQUEST_PROCESS WHERE REQ_ID=@p_REQ_ID DECLARE @CURR_PROCESS VARCHAR(20),@TOTAL_AMT DECIMAL(18,2), @LIMIT_VALUE DECIMAL(18,0), @LIMIT_DVCM DECIMAL(18,0),@IS_NEXT BIT,@DMMS_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20),@BRANCH_HS VARCHAR(20),@DEP_CREATE VARCHAR(15) SET @BRANCH_HS= (SELECT TOP 1 BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_TYPE='HS') SET @LIMIT_VALUE=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PYCMS_DVKD') SET @LIMIT_DVCM=(SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE LIMIT_TYPE='PYCMS_DVCM') SET @TOTAL_AMT=(SELECT TOTAL_AMT FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) SET @CURR_PROCESS = (SELECT PROCESS_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='A',REQ_TYPE=0, CHECKER_ID=@p_CHECKER_ID,APPROVE_DT=CAST(@p_APPROVE_DT AS DATE),BRANCH_DVMS=BRANCH_CREATE, PROCESS_ID='DMMS' WHERE REQ_ID=@p_REQ_ID -- SET @IS_NEXT=(SELECT dbo.FN_CHECK_LIMIT_TR_REQ(@p_REQ_ID,'GDDV')) SET @BRANCH_CREATE=(SELECT BRANCH_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) SET @DEP_CREATE=(SELECT DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) --IF(@BRANCH_CREATE_TYPE='PGD') -- SET @BRANCH_CREATE=(SELECT BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE) IF(EXISTS (SELECT BRANCH_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE AND BRANCH_TYPE='HS')) BEGIN IF(@TOTAL_AMT <= @LIMIT_DVCM) BEGIN SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID) UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID END ELSE BEGIN SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS) UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID END END ELSE BEGIN IF(EXISTS( SELECT RD.REQDT_ID FROM dbo.TR_REQUEST_DOC_DT RD LEFT JOIN dbo.SYS_HH_GROUP_LIMIT HGL ON RD.HANGHOA_ID=HGL.HH_ID LEFT JOIN dbo.SYS_GROUP_LIMIT GL ON GL.GROUP_ID=HGL.GROUP_LIMIT_ID WHERE GL.IS_HO=1 AND RD.REQ_DOC_ID=@p_REQ_ID)) BEGIN IF(@TOTAL_AMT <= @LIMIT_DVCM) BEGIN SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID) UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID END ELSE BEGIN SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS) UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID END END ELSE BEGIN IF(@TOTAL_AMT<=@LIMIT_VALUE) BEGIN SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE) UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID END ELSE BEGIN SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS) UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID END END END -- LUCTV 08122020 KIỂM TRA NẾU TỜ TRÌNH LINK TỚI PYCMS LÀ TỜ TRÌNH TOÀN HÀNG THÌ ĐMMS GIAO CHO ĐƠN VỊ TỰ MUA SẮM IF(@ISCHECK_ALL ='1') BEGIN SET @DMMS_ID= (SELECT TOP 1 DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE) UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID END IF(@TOTAL_AMT > 20000000) BEGIN SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS) UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID END -- LUCTV 08122020 --IF(EXISTS( SELECT RD.REQDT_ID FROM dbo.TR_REQUEST_DOC_DT RD -- LEFT JOIN dbo.SYS_HH_GROUP_LIMIT HGL ON RD.HANGHOA_ID=HGL.HH_ID -- LEFT JOIN dbo.SYS_GROUP_LIMIT GL ON GL.GROUP_ID=HGL.GROUP_LIMIT_ID -- WHERE GL.IS_HO=1 AND RD.REQ_DOC_ID=@p_REQ_ID) OR @BRANCH_CREATE=@BRANCH_HS) -- BEGIN -- IF(@TOTAL_AMT<=@LIMIT_VALUE AND NOT EXISTS(SELECT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT -- LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID -- GROUP BY HC.DVCM_ID HAVING COUNT(REQDT_ID)>1)) -- BEGIN -- SET @DMMS_ID= (SELECT DISTINCT HC.DVCM_ID FROM dbo.TR_REQUEST_DOC_DT DT -- LEFT JOIN dbo.CM_HANGHOA_DVCM HC ON HC.HH_ID=DT.HANGHOA_ID WHERE REQ_DOC_ID=@p_REQ_ID) -- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=2 WHERE REQ_ID=@p_REQ_ID -- END -- ELSE -- BEGIN -- SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS) -- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID -- END -- END -- ELSE -- BEGIN -- IF(@TOTAL_AMT<=@LIMIT_VALUE) -- BEGIN -- SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_CREATE) -- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_CREATE,DMMS_ID=@DMMS_ID,REQ_TYPE=0 WHERE REQ_ID=@p_REQ_ID -- END -- ELSE -- BEGIN -- SET @DMMS_ID= (SELECT DMMS_ID FROM dbo.CM_DMMS WHERE BRANCH_ID=@BRANCH_HS) -- UPDATE dbo.TR_REQUEST_DOC SET BRANCH_DVMS=@BRANCH_HS,DMMS_ID=@DMMS_ID,REQ_TYPE=1 WHERE REQ_ID=@p_REQ_ID -- END -- END INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD, DEP_ID ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'APPNEW', -- PROCESS_ID - varchar(10) 'P', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) @BRANCH_CREATE, -- BRANCH_ID - varchar(15) @p_CHECKER_ID, -- CHECKER_ID - varchar(15) GETDATE(), -- APPROVE_DT - datetime '', -- PARENT_PROCESS_ID - varchar(10) '', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', -- DVDM_ID - varchar(15) N'Trưởng đơn vị phê duyệt', -- NOTES - nvarchar(500) 0 , -- IS_HAS_CHILD - bit @DEP_CREATE ) INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'DMMS', -- PROCESS_ID - varchar(10) 'C', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) '', -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) GETDATE(), -- APPROVE_DT - datetime 'APPNEW', -- PARENT_PROCESS_ID - varchar(10) '', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', -- DVDM_ID - varchar(15) N'Chờ đầu mối mua sắm xử lý', -- NOTES - nvarchar(500) 1 -- IS_HAS_CHILD - bit ) INSERT INTO dbo.PL_PROCESS ( REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC,NOTES ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'APPNEW', -- PROCESS_ID - varchar(10) @p_CHECKER_ID, -- CHECKER_ID - varchar(15) @p_APPROVE_DT , -- APPROVE_DT - datetime @p_PROCESS_DES, N'Trưởng đơn vị phê duyệt' -- PROCESS_DESC - nvarchar(1000) ) END IF @@Error <> 0 GOTO ABORT COMMIT TRANSACTION IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE AUTH_STATUS ='A' AND REQ_ID =@p_REQ_ID)) BEGIN SELECT '0' as Result, '' ErrorDesc RETURN '0' END ELSE BEGIN SELECT '4' as Result, N'Phiếu yêu cầu mua sắm số: '+(SELECT REQ_CODE FROM TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) +N' đã được đợi cấp phê duyệt trung gian xác nhận thành công. Vui lòng đợi trưởng đơn vị phê duyệt' ErrorDesc RETURN '4' END ABORT: BEGIN PRINT 'ERROR' ROLLBACK TRANSACTION SELECT '-1' as Result, '' ErrorDesc RETURN '-1' End ¿ ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Ins] @p_REQ_PAY_ID varchar(15)= NULL, @p_REQ_PAY_CODE varchar(50) = NULL, @p_REQ_DT VARCHAR(10)= NULL, @p_BRANCH_ID varchar(15) = NULL, @p_DEP_ID varchar(15) = NULL, @p_REQ_REASON nvarchar(MAX) = NULL, @p_REQ_TYPE varchar(15) = NULL, @P_REQ_ENTRIES nvarchar(MAX) = NULL, @p_REQ_DESCRIPTION nvarchar(MAX) = NULL, @p_REF_ID varchar(15) = NULL, @p_RECEIVER_PO nvarchar(250) = NULL, @p_RECEIVER_DEBIT nvarchar(250) = NULL, @p_REQ_PAY_TYPE varchar(15) = NULL, @p_REQ_TYPE_CURRENCY nvarchar(50) = NULL, @p_REQ_AMT decimal(18, 0) = NULL, @p_REQ_TEMP_AMT decimal(18, 0) = NULL, @p_MAKER_ID varchar(15) = NULL, @p_CREATE_DT varchar(25) = NULL, @p_EDITOR_ID varchar(15) = NULL, @p_AUTH_STATUS varchar(1) = NULL, @p_CHECKER_ID varchar(15) = NULL, @p_APPROVE_DT varchar(25) = NULL, @p_CREATE_DT_KT varchar(25) = NULL, @p_MAKER_ID_KT varchar(15) = NULL, @p_AUTH_STATUS_KT varchar(1) = NULL, @p_CHECKER_ID_KT varchar(1) = NULL, @p_APPROVE_DT_KT varchar(25)= null, @p_CONFIRM_NOTE nvarchar(500) = NULL, @p_BRANCH_CREATE varchar(15) = NULL, @p_NOTES varchar(15) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_TRANSFER_MAKER nvarchar(50) = NULL, @p_TRANSFER_DT varchar(25) = NULL, @p_TRASFER_USER_RECIVE varchar(15) = NULL, @p_PROCESS varchar(15) = NULL, @p_PAY_PHASE VARCHAR(15)= NULL, @p_DVDM_ID VARCHAR(15) = NULL, @p_RATE DECIMAL(18,0)= NULL, @p_RECIVER_MONEY VARCHAR(15) = NULL, @p_IS_PERIOD VARCHAR(5) = NULL, @p_TYPE_FUNCTION VARCHAR(15) = NULL, @p_XMP_TEMP XML = NULL AS --Validation is here /* DECLARE @ERRORSYS NVARCHAR(15) = '' IF ( NOT EXISTS ( SELECT * FROM ASS_COLLECT WHERE )) SET @ERRORSYS = '' IF @ERRORSYS <> '' BEGIN SELECT ErrorCode Result, '' CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS RETURN '0' END */ --DECLARE @ERRORSYS NVARCHAR(15) = '' --IF EXISTS(SELECT * FROM TR_PO_MASTER WHERE REF_ID = @p_REF_ID ) --BEGIN -- SET @ERRORSYS = 'ASSC-00005' -- SELECT ErrorCode Result, '' COLLECT_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS -- RETURN '0' --END IF(@p_REQ_TYPE ='I') BEGIN DECLARE @AMT_AD_PAY DECIMAL(18,0) =0 DECLARE @AMT_AD_PAY_BACK DECIMAL(18,0) =0 DECLARE @AMT_AD_PAY_REMAIN DECIMAL(18,0) =0 SET @AMT_AD_PAY = (SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE REF_ID=@p_MAKER_ID AND AUTH_STATUS_KT='A' AND REQ_TYPE ='I') SET @AMT_AD_PAY_BACK = (SELECT SUM(D.AMT_USE) +SUM(D.AMT_REVERT) - SUM (D.AMT_ADD) FROM TR_REQ_PAYMENT_DT D LEFT JOIN TR_REQ_ADVANCE_PAYMENT X ON D.PAY_ADV_ID = X.REQ_PAY_ID AND D.AUTH_STATUS_KT ='A' WHERE X.REF_ID =@p_REF_ID) SET @AMT_AD_PAY_REMAIN =ISNULL(@AMT_AD_PAY,0) -ISNULL(@AMT_AD_PAY_BACK,0) SET @p_REQ_TEMP_AMT =ISNULL(@AMT_AD_PAY_REMAIN,0) END BEGIN TRANSACTION -- LUCTV 17H51 BO SUNG LAY SO TIEN DA TAM UNG CUA NHAN VIEN -- --SET @AMT_DO =(SELECT * FROM [dbo].TR_RE) --THIEUVQ 281119--- DECLARE @REC_DEBIT_AUTO VARCHAR(15),@ROLE_KI_NHAY VARCHAR(50) SET @REC_DEBIT_AUTO =(SELECT TOP 1 ACC_NUM FROM CM_ACCOUNT_PAY WHERE REF_ID = @p_REF_ID) --IF (@p_REQ_TYPE='I') --BEGIN -- --SET @p_REF_ID = @p_MAKER_ID -- --IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_NUM = @p_RECEIVER_DEBIT)) -- --BEGIN -- -- SET @p_RECEIVER_DEBIT = @p_RECEIVER_DEBIT+'' -- --END -- --ELSE -- --BEGIN -- -- IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_NUM = @p_RECEIVER_DEBIT AND REF_ID != @p_REF_ID ) AND @p_TYPE_FUNCTION ='SEND') -- -- BEGIN -- -- SET @p_RECEIVER_DEBIT =(SELECT ACC_NUM FROM CM_ACCOUNT_PAY WHERE ACC_NUM = @p_RECEIVER_DEBIT) -- -- END -- --END -- IF(@p_RECEIVER_DEBIT IS NOT NULL AND @p_RECEIVER_DEBIT <>'') -- BEGIN -- IF(@REC_DEBIT_AUTO IS NOT NULL AND @REC_DEBIT_AUTO <>'' AND @REC_DEBIT_AUTO <> @p_RECEIVER_DEBIT) -- BEGIN -- SET @p_RECEIVER_DEBIT =@REC_DEBIT_AUTO -- END -- --IF(EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE REF_ID <> @p_REF_ID AND ACC_NUM =@p_RECEIVER_DEBIT)) -- --BEGIN -- -- ROLLBACK TRANSACTION -- -- SELECT '-1' Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Tài khoản tạm ứng này đã tồn tại và thuộc về nhân viên khác' ErrorDesc -- -- RETURN '-1' -- --END -- END -- ELSE -- BEGIN -- SET @p_RECEIVER_DEBIT =(SELECT TOP 1 ACC_NUM FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID) -- END --END EXEC [dbo].[ADVANCE_CODE_GenKey] 'TR_REQ_ADVANCE_PAYMENT', '',@p_REQ_TYPE, @p_REQ_PAY_CODE out --END-- IF @p_REQ_PAY_CODE IS NULL OR @p_REQ_PAY_CODE ='' BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Mã số phiếu không được để trống' ErrorDesc RETURN '-1' END IF (@p_REQ_TYPE='I' AND (@p_RECEIVER_DEBIT IS NULL OR @p_RECEIVER_DEBIT ='') AND @p_TYPE_FUNCTION ='SEND') BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Tài khoản nhân viên tạm ứng không được để trống' ErrorDesc RETURN '-1' END IF EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_CODE = @p_REQ_PAY_CODE ) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Số phiếu tạm ứng đã tồn tại trong hệ thống' ErrorDesc RETURN '-1' END IF(LEN(ISNULL(@p_RECEIVER_DEBIT,''))>15 AND @p_REQ_TYPE ='I') BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Tài khoản tạm ứng này không được vượt quá 15 kí tự' ErrorDesc RETURN '-1' END -- START 19-11-2019 -- LUCTV BO SUNG VALIDATE TRONG QUA TRINH TEST SAU UAT- KIEM TRA NEU DOT THANH TOAN CHUA CO THI KHONG DUOC THEM MOI --IF(@p_REQ_TYPE='P' AND (@p_PAY_PHASE IS NULL OR @p_PAY_PHASE ='')) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Đợt thanh toán không được phép để trống' ErrorDesc -- RETURN '-1' --END ---- NEU THANH TOAN XU LY NO THI NHAN VIEN NHAN TIEN TAM UNG KHONG DUOC PHEP TRONG --IF(@p_REQ_TYPE='D' AND (@p_RECEIVER_DEBIT IS NULL OR @p_RECEIVER_DEBIT ='')) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Nhân viên nhận tạm ứng không được phép để trống' ErrorDesc -- RETURN '-1' --END -- SO TIEN THANH TOÁN PHAI LON HON KHONG IF(@p_REQ_AMT <=0) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Số tiền tạm ứng phải lớn hơn 0' ErrorDesc RETURN '-1' END ----- ---- KIEM TRA NEU CHUA CHON KHOI PHE DUYET THI TU DONG CHUYEN VÊ KHOI TAI CHINH --IF(@p_DVDM_ID IS NULL OR @p_DVDM_ID ='') --BEGIN -- SET @p_DVDM_ID = (SELECT TOP 1 DVDM_ID FROM CM_DVDM WHERE DVDM_NAME ='KHOI TAI CHINH') --END -- --KIEM TRA NEU TAI KHOAN NHAN VIEN TAM UNG NEU CHUA TON TAI TRONG HE THONG THI BAO LOI --IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID) AND @p_REQ_TYPE='I') --BEGIN -- INSERT INTO CM_ACCOUNT_PAY (REF_ID,ACC_TYPE,ACC_NUM,ACC_NAME,FUNTION,MAKER_ID,AUTH_STATUS,CREATE_DT,TK_GL,TK_GL_NAME) VALUES (@p_REF_ID,'ADV_PAY',@p_RECEIVER_DEBIT,'TK TAM UNG CUA NHAN VIEN ' +@p_REF_ID,'I',@p_MAKER_ID,'U',GETDATE(),'361200001','TAM UNG DE HOAT DONG NGHIEP VU') --END --ELSE --BEGIN -- IF(((SELECT TOP 1 ACC_NUM FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID)<>@p_RECEIVER_DEBIT) AND @p_REQ_TYPE='I') -- BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE, N'Tài khoản tạm ứng của nhân viên đã tồn tại trong hệ thống! Bạn không được phép chỉnh sửa' ErrorDesc -- RETURN '-1' -- END --END --SET @ROLE_KI_NHAY =(SELECT RoleName FROM TL_USER_V2 WHERE TLNANME =@p_TRASFER_USER_RECIVE) --IF(@ROLE_KI_NHAY IS NOT NULL AND @ROLE_KI_NHAY <> '' AND @ROLE_KI_NHAY IN ('TP','GDDV','KTT','TPTC','TC')) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' Result, '' REQ_PAY_ID, N'Bạn không được phép chọn cấp trưởng đơn vị làm cấp phê duyệt trung gian. Vui lòng bỏ qua hoặc chọn cấp phó phòng, trưởng bộ phận' ErrorDesc -- RETURN '-1' --END --DECLARE @ROLE_KI_NHAY VARCHAR(50) IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_NUM =@p_RECEIVER_DEBIT AND REF_ID = @p_REF_ID) AND @p_REQ_TYPE='I') BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Tài khoản tạm ứng số '+ @p_RECEIVER_DEBIT + N' không phải tài khoản của user: '+@p_REF_ID ErrorDesc RETURN '-1' END SET @ROLE_KI_NHAY =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_TRASFER_USER_RECIVE) IF(@ROLE_KI_NHAY IS NOT NULL AND @ROLE_KI_NHAY <>'' AND @ROLE_KI_NHAY IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC')) BEGIN PRINT @ROLE_KI_NHAY END ELSE BEGIN SET @ROLE_KI_NHAY =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_TRASFER_USER_RECIVE) IF(@ROLE_KI_NHAY IS NULL OR @ROLE_KI_NHAY ='') BEGIN SET @ROLE_KI_NHAY =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_TRASFER_USER_RECIVE)) END END --SET @ROLE_KI_NHAY =(SELECT RoleName FROM TL_USER_V2 WHERE TLNANME =@p_TRASFER_USER_RECIVE) IF(@ROLE_KI_NHAY IS NOT NULL AND @ROLE_KI_NHAY <> '' AND @ROLE_KI_NHAY IN ('TP','GDDV','KTT','TPTC','TC','TPGD') AND @p_REQ_TYPE <> 'I') BEGIN ROLLBACK TRANSACTION SELECT '-1' Result, '' REQ_PAY_ID, N'Bạn không được phép chọn cấp trưởng đơn vị làm cấp phê duyệt trung gian. Vui lòng bỏ qua hoặc chọn cấp phó phòng, trưởng bộ phận' ErrorDesc RETURN '-1' END EXEC SYS_CodeMasters_Gen 'TR_REQ_ADVANCE_PAMENT', @p_REQ_PAY_ID out IF @p_REQ_PAY_ID='' OR @p_REQ_PAY_ID IS NULL GOTO ABORT INSERT INTO [dbo].[TR_REQ_ADVANCE_PAYMENT] ([REQ_PAY_ID] ,[REQ_PAY_CODE] ,[BRANCH_ID],[REQ_DT], [DEP_ID] ,[REQ_REASON] ,[REQ_TYPE],REQ_ENTRIES, [REQ_DESCRIPTION] ,REF_ID, RECEIVER_PO, RECEIVER_DEBIT ,[REQ_PAY_TYPE] ,[REQ_TYPE_CURRENCY] ,[REQ_AMT] ,[REQ_TEMP_AMT] ,[MAKER_ID] ,[CREATE_DT] ,[EDITOR_ID] ,[AUTH_STATUS] ,[CHECKER_ID] ,[APPROVE_DT] ,[CREATE_DT_KT] ,[MAKER_ID_KT] ,[AUTH_STATUS_KT] ,[CHECKER_ID_KT] ,[APPROVE_DT_KT] ,[CONFIRM_NOTES] ,[BRANCH_CREATE] ,[NOTES],RECORD_STATUS,TRANSFER_MAKER,TRANSFER_DT,TRASFER_USER_RECIVE,PROCESS,PAY_PHASE,DVDM_ID,RATE,RECIVER_MONEY,IS_PERIOD) VALUES (@p_REQ_PAY_ID, @p_REQ_PAY_CODE, @p_BRANCH_ID,CONVERT(DATE,GETDATE(),103), @p_DEP_ID, @p_REQ_REASON, @p_REQ_TYPE, @P_REQ_ENTRIES, @p_REQ_DESCRIPTION, @p_REF_ID, @p_RECEIVER_PO, @p_RECEIVER_DEBIT, @p_REQ_PAY_TYPE, @p_REQ_TYPE_CURRENCY, @p_REQ_AMT, @p_REQ_TEMP_AMT, @p_MAKER_ID, GETDATE(), @p_EDITOR_ID, 'E', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, @p_BRANCH_CREATE, @p_NOTES,'1', @p_TRANSFER_MAKER, NULL, @p_TRASFER_USER_RECIVE, @p_PROCESS,@p_PAY_PHASE,@p_DVDM_ID,@p_RATE,@p_RECIVER_MONEY,@p_IS_PERIOD) IF @@Error <> 0 GOTO ABORT --- INSERT VÀO BẢNG DETAIL NÊU TẠM ỨNG LÀ TẠM ỨNG THANH TOÁN DECLARE @hdoc INT EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP; -- KIEM TRA NEU TAM UNG THANH TOAN IF((@p_REQ_TYPE='P' AND (@p_IS_PERIOD <>'Y' OR @p_IS_PERIOD IS NULL)) OR @p_REQ_TYPE='D') BEGIN DECLARE @PO_ID varchar(15),@TERM_PAY varchar(15),@AMT_PAY_SCHEDULE decimal(18,0),@REQ_ADV_ID_SCHE VARCHAR(15),@AMT_ADVANCE_SCHEDULE decimal(18,0),@AMT_PAY_DO decimal(18,2), @AMT_REMAIN_SCHEDULE decimal(18,0),@REQ_AD_DT varchar(20),@PROCESS varchar(15),@PAY_ID VARCHAR(15), @REF_ID VARCHAR(15),@REQ_PAY_DESC NVARCHAR(500), @REQ_PAY_ENTRIES NVARCHAR(250),@CURRENCY VARCHAR(15),@RATE DECIMAL(18,0),@CUST_ID VARCHAR(15), @EMP_ID VARCHAR(15), @AMT_PAY_REAL DECIMAL(18,2), @IS_CLOSED VARCHAR(1)= NULL,@INDEX_NS INT =0,@REASON NVARCHAR(1000), @REF_TYPE VARCHAR(15) DECLARE XmlDataPO CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataPO', 2) WITH(REF_ID varchar(15),IS_CLOSED VARCHAR(1),REF_TYPE VARCHAR(15)) OPEN XmlDataPO; DECLARE @INDEX_PO INT =0 SET @INDEX_PO = 0 FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE WHILE @@fetch_status=0 BEGIN SET @INDEX_PO = @INDEX_PO +1 -- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH --IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID)) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO/ hợp đồng số '+(SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc -- RETURN '-1' --END ---- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH --IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL))) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO/ hợp đồng số '+(SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được thanh toán. Vui lòng đợi giao dịch hoàn tất' ErrorDesc -- RETURN '-1' --END -- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH -- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='C' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': Hợp đồng số '+ (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @REF_ID)+N' đang được tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END -- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='C' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': Hợp đồng số '+ (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @REF_ID)+N' đang được thanh toán. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END -- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='P' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO số '+ (SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END -- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='P' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO số '+ (SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được thanh toán. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END END DECLARE @REQ_PAYDTID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_ADVANCE_DT', @REQ_PAYDTID OUT; IF @REQ_PAYDTID='' OR @REQ_PAYDTID IS NULL GOTO ABORT; INSERT INTO TR_REQ_ADVANCE_DT (REQ_PAYDTID,REQ_PAY_ID,REF_ID,TYPE,MAKER_ID,CREATE_DT,IS_CLOSED,REF_TYPE) VALUES (@REQ_PAYDTID,@p_REQ_PAY_ID,@REF_ID,'ADV_PAY',@p_MAKER_ID,GETDATE(),@IS_CLOSED,@REF_TYPE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE END CLOSE XmlDataPO; DEALLOCATE XmlDataPO; --- CUSOR DANH SACH LICH THANH TOAN PO - HD DECLARE XmlDataSchedule CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataSchedule',2) WITH(PO_ID varchar(15),TERM_PAY varchar(15),AMT_PAY_SCHEDULE decimal(18,0),REQ_ADV_ID varchar(15),AMT_ADVANCE_SCHEDULE decimal(18,0), AMT_PAY_DO decimal(18,0), AMT_REMAIN_SCHEDULE decimal(18,0),REQ_AD_DT varchar(20),PROCESS varchar(15), PAY_ID VARCHAR(15),REF_ID VARCHAR(15), REQ_PAY_DESC NVARCHAR(500), REQ_PAY_ENTRIES NVARCHAR(250),CURRENCY VARCHAR(15),RATE DECIMAL(18,0),AMT_PAY_REAL DECIMAL(18,2)) OPEN XmlDataSchedule FETCH NEXT FROM XmlDataSchedule INTO @PO_ID,@TERM_PAY,@AMT_PAY_SCHEDULE,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE,@AMT_PAY_DO, @AMT_REMAIN_SCHEDULE,@REQ_AD_DT,@PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL WHILE @@fetch_status=0 BEGIN DECLARE @p_REQ_PAY_SCHEDULE_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_SCHEDULE', @p_REQ_PAY_SCHEDULE_ID OUT; IF @p_REQ_PAY_SCHEDULE_ID='' OR @p_REQ_PAY_SCHEDULE_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_SCHEDULE( SCHEDULE_ID,REQ_PAY_ID,PAY_ID,AMT_PAY,PAY_PHASE,REQ_ADV_ID,AMT_ADVANCE,AMT_PAY_DO,AMT_REMAIN,REQ_PAY_DT,REQ_AD_DT,PROCESS,MAKER_ID, CREATE_DT,AUTH_STATUS_KT,PAYMENT_STATUS,TRN_TYPE,REF_ID,REQ_PAY_DESC,REQ_PAY_ENTRIES,CURRENCY,RATE,AMT_PAY_REAL) VALUES (@p_REQ_PAY_SCHEDULE_ID,@p_REQ_PAY_ID,@PAY_ID,@AMT_PAY_SCHEDULE,@TERM_PAY,@p_REQ_PAY_ID,@AMT_ADVANCE_SCHEDULE,@AMT_PAY_DO,@AMT_REMAIN_SCHEDULE, GETDATE(),CONVERT(DATETIME,@REQ_AD_DT,103),NULL,@p_MAKER_ID,GETDATE(),'U','','ADV_PAY',@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL) --- END KHAI BAO CURSOR IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataSchedule INTO @PO_ID,@TERM_PAY,@AMT_PAY_SCHEDULE,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE,@AMT_PAY_DO, @AMT_REMAIN_SCHEDULE,@REQ_AD_DT,@PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL END CLOSE XmlDataSchedule; DEALLOCATE XmlDataSchedule; END --- END TẠM ỨNG THANH TOÁN --- TẠM ỨNG HĐ ĐỊNH KỲ IF(@p_REQ_TYPE = 'P' AND @p_IS_PERIOD='Y') BEGIN DECLARE XmlDataPO CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataPO', 2) WITH(REF_ID varchar(15),IS_CLOSED VARCHAR(1),REF_TYPE VARCHAR(15)) OPEN XmlDataPO; SET @INDEX_PO = 0 FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE WHILE @@fetch_status=0 BEGIN SET @INDEX_PO = @INDEX_PO +1 DECLARE @REQ_PAYDTID_C VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_ADVANCE_DT', @REQ_PAYDTID_C OUT; IF @REQ_PAYDTID_C='' OR @REQ_PAYDTID_C IS NULL GOTO ABORT; INSERT INTO TR_REQ_ADVANCE_DT (REQ_PAYDTID,REQ_PAY_ID,REF_ID,TYPE,MAKER_ID,CREATE_DT,IS_CLOSED,REF_TYPE) VALUES (@REQ_PAYDTID_C,@p_REQ_PAY_ID,@REF_ID,'ADV_PAY',@p_MAKER_ID,GETDATE(),@IS_CLOSED,@REF_TYPE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE END CLOSE XmlDataPO; DEALLOCATE XmlDataPO; --- CUSOR DANH SACH LICH THANH TOAN PO - HD ---------------------------- --INSERT FROM PERIOD DECLARE XmlDataPeriod CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataPeriod', 2) WITH(CONTRACT_ID varchar(15),PAY_DT_REAL VARCHAR(20),PAY_TYPE VARCHAR(5), OLD_INDEX DECIMAL(18,0),NEW_INDEX DECIMAL(18,0),AMT_PAY DECIMAL(18,2),CURRENCY VARCHAR(5), RATE DECIMAL(18,0), FROM_DATE VARCHAR(20), TO_DATE VARCHAR(20),PAY_PHASE NVARCHAR(250),REASON NVARCHAR(2000)) OPEN XmlDataPeriod; DECLARE @CONTRACT_ID varchar(15),@PAY_DT_REAL VARCHAR(20),@PAY_TYPE VARCHAR(5), @OLD_INDEX DECIMAL(18,0),@NEW_INDEX DECIMAL(18,0),@AMT_PAY_PERIOD DECIMAL(18,2),@FROM_DATE VARCHAR(20),@TO_DATE VARCHAR(20),@PAY_PHASE NVARCHAR(250),@REASON_TTDK NVARCHAR(2000) FETCH NEXT FROM XmlDataPeriod INTO @CONTRACT_ID ,@PAY_DT_REAL ,@PAY_TYPE , @OLD_INDEX ,@NEW_INDEX ,@AMT_PAY_PERIOD,@CURRENCY, @RATE,@FROM_DATE,@TO_DATE,@PAY_PHASE,@REASON_TTDK WHILE @@fetch_status=0 BEGIN -- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN IF(EXISTS(SELECT CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @REF_ID AND IS_CLOSED='Y' )) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Tạm ứng hợp đồng định kì đã đóng' ErrorDesc RETURN '-1' END IF(@NEW_INDEX <=@OLD_INDEX AND @NEW_INDEX >0 AND @OLD_INDEX >0 ) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, @p_REQ_PAY_ID REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Chỉ số mới phải lớn hơn chỉ số cũ' ErrorDesc RETURN '-1' END END DECLARE @PERIOD_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_PERIOD', @PERIOD_ID OUT; IF @PERIOD_ID='' OR @PERIOD_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_PERIOD(PERIOD_ID,REQ_PAY_ID,PAY_TYPE, CONTRACT_ID,PAY_DT_REAL,OLD_INDEX,NEW_INDEX,AMT_PAY,AUTH_STATUS_KT,CURRENCY,RATE,FROM_DATE, TO_DATE,AD_PAY_ID,PROCESS,PAY_PHASE,REASON) VALUES (@PERIOD_ID,@p_REQ_PAY_ID,'ADV_PAY',@CONTRACT_ID,CONVERT(DATE,@PAY_DT_REAL,103), @OLD_INDEX ,@NEW_INDEX ,@AMT_PAY_PERIOD,'U',@CURRENCY, @RATE,CONVERT(DATE,@FROM_DATE,103),CONVERT(DATE,@TO_DATE,103),@p_REQ_PAY_ID,'0',@PAY_PHASE,@REASON_TTDK) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataPeriod INTO @CONTRACT_ID ,@PAY_DT_REAL ,@PAY_TYPE , @OLD_INDEX ,@NEW_INDEX ,@AMT_PAY_PERIOD,@CURRENCY, @RATE,@FROM_DATE,@TO_DATE,@PAY_PHASE,@REASON_TTDK END CLOSE XmlDataPeriod; DEALLOCATE XmlDataPeriod; -- VALIDATE SO TIEN --IF(@p_REQ_AMT >= @TOTAL_SCHEDULE_AMT) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Số tiền thanh toán phải nhỏ hơn hoặc bằng tổng số tiền còn lại cần thanh toán: '+FORMAT(SUM(@TOTAL_SCHEDULE_AMT),'#,#', 'vi-VN') ErrorDesc -- RETURN '-1' --END ---- END ---- END TẠM ỨNG HỢP ĐỒNG ĐỊNH KÌ --- INSERT PHƯƠNG THỨC THANH TOÁN ----MethodCursor DECLARE @RECEIVE_ID varchar(15),@RECEIVE_NAME nvarchar(100),@REQ_PAY_REASON nvarchar(MAX),@TOTAL_AMT_METHOD decimal(18,2),@REQ_PAY_TYPE varchar(1), @ACC_NO VARCHAR(50), @ACC_NAME NVARCHAR(200), @ISSED_BY NVARCHAR(250), @ISSUED_DT VARCHAR(20),@CHECK_IN VARCHAR(15) DECLARE XmlDataMethod CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataMethod',2) WITH(RECEIVE_ID varchar(15),RECEIVE_NAME nvarchar(100),REQ_PAY_REASON nvarchar(MAX),TOTAL_AMT decimal(18,2),REQ_PAY_TYPE varchar(1), REQ_PAY_DESC nvarchar(MAX),REQ_PAY_ENTRIES nvarchar(MAX),ACC_NO VARCHAR(50), ACC_NAME NVARCHAR(200), ISSUED_BY NVARCHAR(250), ISSUED_DT VARCHAR(20),CURRENCY VARCHAR(15),RATE DECIMAL(18,0),CHECK_IN VARCHAR(15)) OPEN XmlDataMethod FETCH NEXT FROM XmlDataMethod INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO,@ACC_NAME ,@ISSED_BY , @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN WHILE @@fetch_status=0 BEGIN IF(@REQ_PAY_TYPE <>'1') BEGIN SET @ISSUED_DT = NULL END DECLARE @p_REQ_PAY_METHOD_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_METHOD', @p_REQ_PAY_METHOD_ID OUT; IF @p_REQ_PAY_METHOD_ID='' OR @p_REQ_PAY_METHOD_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_METHOD(METHOD_ID,REQ_PAY_ID,EMP_ID,REQ_PAY_REASON,TOTAL_AMT,REQ_PAY_TYPE,REQ_PAY_DESC,REQ_PAY_ENTRIES, TEMP,MAKER_ID, CREATE_DT,ACC_NO,ACC_NAME,ISSUED_BY,ISSUED_DT,CURRENCY,RATE,CHECK_IN) VALUES (@p_REQ_PAY_METHOD_ID,@p_REQ_PAY_ID,@RECEIVE_ID,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,'', @p_MAKER_ID,GETDATE(),@ACC_NO,@ACC_NAME,@ISSED_BY, CONVERT(DATE, @ISSUED_DT, 103),@CURRENCY,@RATE,@CHECK_IN) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataMethod INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO,@ACC_NAME , @ISSED_BY , @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN END CLOSE XmlDataMethod; DEALLOCATE XmlDataMethod ----END INSERT PHƯƠNG THỨC THANH TOÁN ----INSERT VAO BANG DS KHACH HANG DECLARE XmlDataCus CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataCus', 2) WITH(CUS_ID varchar(15), EMP_ID VARCHAR(15), AMT_PAY_DO DECIMAL(18,2),CURRENCY VARCHAR(15),RATE DECIMAL(18,0)) OPEN XmlDataCus; FETCH NEXT FROM XmlDataCus INTO @CUST_ID, @EMP_ID,@AMT_PAY_DO,@CURRENCY,@RATE WHILE @@fetch_status=0 BEGIN INSERT INTO TR_REQ_ADVANCE_CUS(REQ_PAY_ID,CUST_ID,EMP_ID,AMT_PAY_DO,CURRENCY,RATE,MAKER_ID,CREATE_DT) VALUES (@p_REQ_PAY_ID, @CUST_ID, @EMP_ID,@AMT_PAY_DO,@CURRENCY,@RATE,@p_MAKER_ID,GETDATE()) --- END KHAI BAO CURSOR IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataCus INTO @CUST_ID, @EMP_ID,@AMT_PAY_DO,@CURRENCY,@RATE END CLOSE XmlDataCus; DEALLOCATE XmlDataCus; ----END -- HANG MUC CHI PHI VA NGAN SACH DECLARE @TRADE_ID varchar(15),@GD_ID varchar(15),@AMT_APP decimal(18,2), @AMT_DO decimal(18,2), @AMT_EXE decimal(18,2),@AMT_REMAIN_GD decimal(18,2) DECLARE XmlDataGood CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataGood',2) WITH(TRADE_ID varchar(15),GD_ID varchar(15),AMT_APP decimal(18,2),AMT_DO decimal(18,2),AMT_EXE decimal(18,2),AMT_REMAIN decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),REASON NVARCHAR(1000)) OPEN XmlDataGood FETCH NEXT FROM XmlDataGood INTO @TRADE_ID,@GD_ID ,@AMT_APP ,@AMT_DO,@AMT_EXE ,@AMT_REMAIN_GD,@CURRENCY,@RATE,@REASON WHILE @@fetch_status=0 BEGIN --- KIEM TRA NEU NGAN SACH SU DUNG THUC THE LON HON NGAN SACH CON LAI SET @INDEX_NS = @INDEX_NS +1 -- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE --IF(@p_TYPE_FUNCTION ='SEND') --BEGIN --IF((@AMT_EXE > @AMT_REMAIN_GD)) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_NS)+ N': Ngân sách sử dụng thực tế không được vượt quá ngân sách còn lại.' ErrorDesc -- RETURN '-1' --END --END IF(@p_TYPE_FUNCTION ='SEND') BEGIN IF(ISNULL(@AMT_EXE,0) =0) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_NS)+ N': Ngân sách sử dụng thực tế phải lớn hơn không.' ErrorDesc RETURN '-1' END IF((@AMT_EXE > @AMT_REMAIN_GD) AND ((SELECT GD_TYPE_ID FROM CM_GOODS WHERE GD_ID =@GD_ID) ='NS')) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_NS)+ N': Ngân sách sử dụng thực tế không được vượt quá ngân sách còn lại.' ErrorDesc RETURN '-1' END END DECLARE @p_BUDGET_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_BUDGET', @p_BUDGET_ID OUT; IF @p_BUDGET_ID='' OR @p_BUDGET_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_BUDGET(BUDG_ID,GD_ID,REQ_PAY_ID,TRADE_ID,AMT_APP,AMT_DO, AMT_EXE,AMT_REMAIN,MAKER_ID,CREATE_DT,CURRENCY,RATE,REASON) VALUES (@p_BUDGET_ID,@GD_ID,@p_REQ_PAY_ID,@TRADE_ID,@AMT_APP,@AMT_DO, @AMT_EXE,@AMT_REMAIN_GD,@p_MAKER_ID, GETDATE(),@CURRENCY,@RATE,@REASON) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataGood INTO @TRADE_ID,@GD_ID ,@AMT_APP ,@AMT_DO,@AMT_EXE ,@AMT_REMAIN_GD,@CURRENCY,@RATE,@REASON END; CLOSE XmlDataGood; DEALLOCATE XmlDataGood; --- END INSERT NGAN SACH DECLARE @ATTACH_ID varchar(15),@CON_ID varchar(100),@CONT_CODE VARCHAR(50), @CON_NOTES VARCHAR(1000),@CON_DESC NVARCHAR(1000),@CON_TYPE VARCHAR(15),@AMT_ATT DECIMAL(18,2),@REF_DT VARCHAR(20),@NOTES_ATT NVARCHAR(1000) DECLARE XmlAttach CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlAttach',2) WITH(ATTACH_ID varchar(15),CON_ID varchar(100),CONT_CODE VARCHAR(50), CON_NOTES NVARCHAR(1000),CON_DESC NVARCHAR(1000),CON_TYPE VARCHAR(15),AMT_ATT DECIMAL(18,2),REF_DT VARCHAR(20)) OPEN XmlAttach --INSERT CHUNG TU DINH KEM FETCH NEXT FROM XmlAttach INTO @ATTACH_ID,@CON_ID,@CONT_CODE,@NOTES_ATT,@CON_DESC,@CON_TYPE,@AMT_ATT,@REF_DT WHILE @@fetch_status=0 BEGIN IF (@REF_DT='') BEGIN SET @REF_DT = NULL END EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ATTACH', @ATTACH_ID OUT; IF @ATTACH_ID='' OR @ATTACH_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_ATTACH(ATTACH_ID,REQ_PAY_ID,REF_ID,REF_CODE,NOTES,[DESCRIPTION],[TYPE],AMT,REF_DT) VALUES (@ATTACH_ID,@p_REQ_PAY_ID,@CON_ID,@CONT_CODE,@NOTES_ATT,@CON_DESC,@CON_TYPE,@AMT_ATT, CONVERT(DATE, @REF_DT,103)) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlAttach INTO @ATTACH_ID,@CON_ID,@CONT_CODE,@NOTES_ATT,@CON_DESC,@CON_TYPE,@AMT_ATT,@REF_DT END CLOSE XmlAttach; DEALLOCATE XmlAttach; ----END --- BAT DAU VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN DECLARE @SUM_PTTT DECIMAL(18,0), @SUM_NS DECIMAL(18,0),@SUM_LICH_TT DECIMAL(18,0) SET @SUM_PTTT =(SELECT SUM(TOTAL_AMT * ISNULL(RATE,1)) FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) IF(EXISTS(SELECT * FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID =@p_REQ_PAY_ID) AND @p_REQ_TYPE ='D') BEGIN SET @SUM_LICH_TT =(SELECT SUM(AMT_PAY_REAL* ISNULL(RATE,1)) FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID =@p_REQ_PAY_ID) IF(@p_REQ_AMT*ISNULL(@p_RATE,1) != ISNULL(@SUM_LICH_TT,0)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Tổng số tiền lịch thanh toán PO và hợp đồng liên quan đến khách hàng xử lý nợ phải bằng số tiền tạm ứng' ErrorDesc RETURN '-1' END END IF(EXISTS(SELECT * FROM TR_REQ_PAY_BUDGET WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) BEGIN SET @SUM_NS =(SELECT SUM(AMT_EXE * ISNULL(RATE,1)) FROM TR_REQ_PAY_BUDGET WHERE REQ_PAY_ID =@p_REQ_PAY_ID) IF(@p_REQ_AMT*ISNULL(@p_RATE,1) != ISNULL(@SUM_PTTT,0)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Tổng số tiền sử dụng ngân sách và chi phí phải bằng số tiền tạm ứng' ErrorDesc RETURN '-1' END END ELSE IF(@p_REQ_AMT*ISNULL(@p_RATE,1) != ISNULL(@SUM_PTTT,0)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Tổng số tiền phương thức thanh toán phải bằng số tiền tạm ứng' ErrorDesc RETURN '-1' END END ----END --- INSERT SẴN VÀO BẢNG TR_REQUEST_PROCESS IF(@p_REQ_TYPE ='I') BEGIN DECLARE @tmp table(ROLE_ID VARCHAR(25),BRANCH_ID varchar(15),AMT DECIMAL(18,0)) IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_CREATE) ='HS') BEGIN --INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE (DEP_CODE LIKE '%06908%' OR DEP_CODE ='0690605' OR DEP_CODE ='0690405') AND DEP_ID =@p_DEP_ID) BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK' END ELSE IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06910%' AND DEP_ID =@p_DEP_ID) BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK' END ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06900%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06901%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06902%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06903%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06904%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06920%' AND DEP_ID =@p_DEP_ID)) BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID NOT IN ('GDK','PTGD') END ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06907%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06909%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06911%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06921%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06922%' AND DEP_ID =@p_DEP_ID)) BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'PTGD' END ELSE BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' END END ELSE BEGIN IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_CREATE) ='CN') BEGIN --- BAO GOM DON VI & 3 CAP DUYET CUA HOI SO INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@p_BRANCH_CREATE --INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' END ELSE IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_CREATE) ='PGD') BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY' --AND BRANCH_ID =(SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_CREATE) AND BRANCH_ID = @p_BRANCH_CREATE --INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' END END DECLARE @ROLE_ID VARCHAR(20), @BRANCH_ID VARCHAR(20), @INDEX INT =0, @SL_ROLE INT =0,@LIMIT_VALUE DECIMAL(18,0) SET @SL_ROLE = (SELECT COUNT(*) FROM @tmp) DECLARE @ISLEAF VARCHAR(1) = 'Y', @PARENT_ID VARCHAR(10) = '', @STATUS VARCHAR(1) = 'C', @NOTES NVARCHAR(500) DECLARE CUR_PR CURSOR FOR SELECT A.ROLE_ID, A.BRANCH_ID,A.AMT FROM @tmp A OPEN CUR_PR FETCH NEXT FROM CUR_PR INTO @ROLE_ID,@BRANCH_ID,@LIMIT_VALUE WHILE @@FETCH_STATUS =0 --AND @STOP=0 BEGIN SET @INDEX= @INDEX+1 IF @INDEX = @SL_ROLE SET @ISLEAF = 'Y' ELSE SET @ISLEAF = 'N' SET @NOTES = (SELECT N'Đợi '+ ROLE_DESC + N' phê duyệt' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID) IF(@INDEX=1 ) BEGIN SET @PARENT_ID = NULL SET @STATUS = 'C' --INSERT INTO TR_REQUEST_PROCESS VALUES (@p_REQ_PAY_ID,CONVERT(VARCHAR(5), @INDEX),'C',@ROLE_ID,@BRANCH_ID,NULL,NULL,NULL,@ISLEAF) END ELSE BEGIN SET @PARENT_ID = CONVERT(VARCHAR(5),@INDEX -1) SET @STATUS = 'U' END IF(@LIMIT_VALUE >= @p_REQ_AMT) BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES ) VALUES (@p_REQ_PAY_ID,CONVERT(VARCHAR(5), @INDEX),@STATUS,@ROLE_ID,@BRANCH_ID,NULL,NULL, @PARENT_ID,'Y', '',@p_DVDM_ID,@NOTES) --INSERT INTO PL_REQUEST_PROCESS VALUES (@p_REQ_PAY_ID,CONVERT(VARCHAR(5), @INDEX),@STATUS,@ROLE_ID,@BRANCH_ID,NULL,NULL, @PARENT_ID,'Y') BREAK; END ELSE INSERT INTO PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES ) VALUES (@p_REQ_PAY_ID,CONVERT(VARCHAR(5), @INDEX),@STATUS,@ROLE_ID,@BRANCH_ID,NULL,NULL, @PARENT_ID,@ISLEAF, '', @p_DVDM_ID, @NOTES) --END FETCH NEXT FROM CUR_PR INTO @ROLE_ID,@BRANCH_ID,@LIMIT_VALUE END CLOSE CUR_PR DEALLOCATE CUR_PR END --- CAP NHAT THANG CUOI CUNG LA Y --UPDATE TR_REQUEST_PROCESS SET IS_LEAF='Y' WHERE ID = (SELECT MAX(ID) FROM TR_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID) COMMIT TRANSACTION IF(@p_TYPE_FUNCTION ='SEND')-- BAT DAU GUI PHE DUYET BEGIN --ROLLBACK TRANSACTION -- CAP NHAT TINH TRANG DUYET KE TOAN LA DANG XU LY --UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS ='U' WHERE REQ_PAY_ID =@p_REQ_PAY_ID --INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES) --VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID,GETDATE(), N'Đơn vị gửi phê duyệt',N'Đơn vị tạo phiếu tạm ứng và gửi phê duyệt') --SELECT '4' as Result, @p_REQ_PAY_ID AS REQ_PAY_ID, N'Gửi phê duyệt thành công' ErrorDesc --RETURN '4' IF(@p_TYPE_FUNCTION ='SEND') -- KIEM TRA NEU 2 BIEN XML KHAC NULL TUC LA DANG TRONG QUA TRINH LUU NHAP BEGIN DECLARE @BRANCH_TYPE_CR VARCHAR(15) SET @BRANCH_TYPE_CR =(SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_CREATE) IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE TRASFER_USER_RECIVE ='' OR TRASFER_USER_RECIVE IS NULL AND REQ_PAY_ID =@p_REQ_PAY_ID) AND @BRANCH_TYPE_CR ='HS' AND @p_REQ_TYPE ='I') BEGIN DECLARE @USER_TP VARCHAR(15) ='' --SET @USER_TP =(SELECT TOP 1 TLNANME FROM TL_USER WHERE SECUR_CODE =@p_DEP_ID -- AND (RoleName IN ('GDDV','TPTC','TC','KTT') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW ='GDDV'))) IF(@USER_TP IS NULL OR @USER_TP ='') BEGIN SET @USER_TP = (SELECT TOP 1 TLNANME FROM TL_USER_V2 WHERE SECUR_CODE =@p_DEP_ID AND (RoleName IN ('GDDV','TPTC','TC','KTT'))) END UPDATE TR_REQ_ADVANCE_PAYMENT SET TRASFER_USER_RECIVE =@USER_TP WHERE REQ_PAY_ID =@p_REQ_PAY_ID END --ELSE IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE TRASFER_USER_RECIVE ='' OR TRASFER_USER_RECIVE IS NULL AND REQ_PAY_ID =@p_REQ_PAY_ID) AND @BRANCH_TYPE_CR ='PGD' AND @p_REQ_TYPE ='I') --BEGIN -- DECLARE @USER_TPGD VARCHAR(15) ='' -- SET @USER_TPGD =(SELECT TOP 1 TLNANME FROM TL_USER WHERE SECUR_CODE =@p_DEP_ID AND RoleName IN ('TPGD')) -- --SET @USER_TPGD =(SELECT TOP 1 TLNANME FROM TL_USER WHERE SECUR_CODE =@p_DEP_ID AND (RoleName IN ('TPGD') OR -- --RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW ='TPGD'))) -- IF(@USER_TPGD IS NULL OR @USER_TPGD ='') -- BEGIN -- SET @USER_TPGD = (SELECT TOP 1 TLNANME FROM TL_USER_V2 WHERE TLSUBBRID =@p_BRANCH_CREATE -- AND RoleName ='TPGD') -- END -- UPDATE TR_REQ_ADVANCE_PAYMENT SET TRASFER_USER_RECIVE =@USER_TPGD WHERE REQ_PAY_ID =@p_REQ_PAY_ID --END IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS='U')) BEGIN SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đã được gửi phê duyệt trước đó! Vui lòng đợi các cấp phê duyệt' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE TRASFER_USER_RECIVE <> '' AND TRASFER_USER_RECIVE IS NOT NULL AND REQ_PAY_ID =@p_REQ_PAY_ID) AND @BRANCH_TYPE_CR ='PGD' AND @p_REQ_TYPE <>'I') BEGIN SELECT '-1' as Result, '' REQ_PAY_ID, N'Ở cấp độ phòng giao dịch. Vui lòng không được chọn cấp phê duyệt trung gian' ErrorDesc RETURN '-1' END -- CAP NHAT TINH TRANG DUYET KE TOAN LA DANG XU LY UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS ='U', PROCESS = NULL WHERE REQ_PAY_ID =@p_REQ_PAY_ID INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES) VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID,GETDATE(), N'Đơn vị gửi phê duyệt',N'Đơn vị tạo phiếu tạm ứng và gửi phê duyệt') IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE (TRASFER_USER_RECIVE <> '' OR TRASFER_USER_RECIVE IS NOT NULL) AND REQ_PAY_ID =@p_REQ_PAY_ID)) BEGIN SELECT '4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Đơn vị gửi phiếu phê duyệt phiếu đề nghị tạm ứng số: ' + ISNULL(@p_REQ_PAY_CODE,'') + N' thành công. Vui lòng đợi cấp phê duyệt trung gian xác nhận phiếu' ErrorDesc RETURN '4' END ELSE BEGIN SELECT '4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Đơn vị gửi phiếu phê duyệt phiếu đề nghị tạm ứng số: ' + ISNULL(@p_REQ_PAY_CODE,'') + N' thành công. Vui lòng đợi trưởng đơn vị phê duyệt phiếu' ErrorDesc RETURN '4' END END END SELECT '0' as Result, @p_REQ_PAY_ID REQ_PAY_ID,@p_REQ_PAY_CODE AS REQ_PAY_CODE, '' ErrorDesc RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, '' ErrorDesc RETURN '-1' End ¿ ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Upd] @p_REQ_PAY_ID varchar(15)= NULL, @p_REQ_PAY_CODE varchar(50) = NULL, @p_REQ_DT VARCHAR(20)= NULL, @p_BRANCH_ID varchar(15) = NULL, @p_DEP_ID varchar(15) = NULL, @p_REQ_REASON nvarchar(MAX) = NULL, @p_REQ_TYPE varchar(15) = NULL, @P_REQ_ENTRIES nvarchar(MAX) = NULL, @p_REQ_DESCRIPTION nvarchar(MAX) = NULL, @p_REF_ID varchar(15) = NULL, @p_RECEIVER_PO nvarchar(250) = NULL, @p_RECEIVER_DEBIT nvarchar(250) = NULL, @p_REQ_PAY_TYPE varchar(15) = NULL, @p_REQ_TYPE_CURRENCY nvarchar(50) = NULL, @p_REQ_AMT decimal(18, 0) = NULL, @p_REQ_TEMP_AMT decimal(18, 0) = NULL, @p_MAKER_ID varchar(15) = NULL, @p_CREATE_DT varchar(25) = NULL, @p_EDITOR_ID varchar(15) = NULL, @p_AUTH_STATUS varchar(1) = NULL, @p_CHECKER_ID varchar(15) = NULL, @p_APPROVE_DT varchar(25) = NULL, @p_CREATE_DT_KT varchar(25) = NULL, @p_MAKER_ID_KT varchar(15) = NULL, @p_AUTH_STATUS_KT varchar(1) = NULL, @p_CHECKER_ID_KT varchar(1) = NULL, @p_APPROVE_DT_KT varchar(25)= null, @p_CONFIRM_NOTE nvarchar(500) = NULL, @p_BRANCH_CREATE varchar(15) = NULL, @p_NOTES varchar(15) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_TRANSFER_MAKER nvarchar(50) = NULL, @p_TRANSFER_DT varchar(25) = NULL, @p_TRASFER_USER_RECIVE varchar(15) = NULL, @p_PROCESS varchar(15) = NULL, @p_PAY_PHASE VARCHAR(15)= NULL, @p_DVDM_ID VARCHAR(15)= NULL, @p_RATE DECIMAL(18,0) =0, @p_RECIVER_MONEY VARCHAR(15)= NULL, @p_XMP_TEMP XML = NULL, @p_TYPE_FUNCTION VARCHAR(15) = NULL, @p_IS_PERIOD VARCHAR(5) = NULL AS --Validation is here /* DECLARE @ERRORSYS NVARCHAR(15) = '' IF ( NOT EXISTS ( SELECT * FROM ASS_COLLECT WHERE )) SET @ERRORSYS = '' IF @ERRORSYS <> '' BEGIN SELECT ErrorCode Result, '' CAR_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS RETURN '0' END */ --Luanlt-2019/10/15 Disable Validation --DECLARE @ERRORSYS NVARCHAR(15) = '' --IF EXISTS(SELECT * FROM TR_PO_MASTER WHERE REF_ID = @p_REF_ID ) --BEGIN -- SET @ERRORSYS = 'ASSC-00005' -- SELECT ErrorCode Result, '' REQ_PAY_ID, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS -- RETURN '-1' --END DECLARE @BRANCH_TYPE_CR VARCHAR(15), @ROLE_KI_NHAY VARCHAR(50) --SET @ROLE_KI_NHAY =(SELECT RoleName FROM TL_USER_V2 WHERE TLNANME =@p_TRASFER_USER_RECIVE) --IF(@ROLE_KI_NHAY IS NOT NULL AND @ROLE_KI_NHAY <> '' AND @ROLE_KI_NHAY IN ('TP','GDDV','KTT','TPTC','TC')) --BEGIN -- SELECT '-1' Result, '' REQ_PAY_ID, N'Bạn không được phép chọn cấp trưởng đơn vị làm cấp phê duyệt trung gian. Vui lòng bỏ qua hoặc chọn cấp phó phòng, trưởng bộ phận' ErrorDesc -- RETURN '-1' --END SET @ROLE_KI_NHAY =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_TRASFER_USER_RECIVE) IF(@ROLE_KI_NHAY IS NOT NULL AND @ROLE_KI_NHAY <>'' AND @ROLE_KI_NHAY IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC')) BEGIN PRINT @ROLE_KI_NHAY END ELSE BEGIN SET @ROLE_KI_NHAY =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_TRASFER_USER_RECIVE) IF(@ROLE_KI_NHAY IS NULL OR @ROLE_KI_NHAY ='') BEGIN SET @ROLE_KI_NHAY =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_TRASFER_USER_RECIVE)) END END --SET @ROLE_KI_NHAY =(SELECT RoleName FROM TL_USER_V2 WHERE TLNANME =@p_TRASFER_USER_RECIVE) IF(@ROLE_KI_NHAY IS NOT NULL AND @ROLE_KI_NHAY <> '' AND @ROLE_KI_NHAY IN ('TP','GDDV','KTT','TPTC','TC','TPGD') AND @p_REQ_TYPE <> 'I') BEGIN SELECT '-1' Result, '' REQ_PAY_ID, N'Bạn không được phép chọn cấp trưởng đơn vị làm cấp phê duyệt trung gian. Vui lòng bỏ qua hoặc chọn cấp phó phòng, trưởng bộ phận' ErrorDesc RETURN '-1' END SET @BRANCH_TYPE_CR =(SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_CREATE) DECLARE @ACC_NUM VARCHAR(15) SET @ACC_NUM = (SELECT TOP 1 ACC_NUM FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID) IF @p_REQ_PAY_CODE IS NULL OR @p_REQ_PAY_CODE ='' BEGIN SELECT '-1' Result, '' REQ_PAY_ID, N'Mã số phiếu không được để trống' ErrorDesc RETURN '-1' END IF (@p_REQ_TYPE='I' AND (@p_RECEIVER_DEBIT IS NULL OR @p_RECEIVER_DEBIT ='')) BEGIN SELECT '-1' Result, '' REQ_PAY_ID, N'Tài khoản nhân viên tạm ứng không được để trống' ErrorDesc RETURN '-1' END IF EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_CODE = @p_REQ_PAY_CODE AND REQ_PAY_ID <> @p_REQ_PAY_ID) BEGIN SELECT '-1' Result, '' REQ_PAY_ID, N'Mã số phiếu đã tồn tại trong hệ thống' ErrorDesc RETURN '-1' END --IF(((SELECT TOP 1 ACC_NUM FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID)<>@p_RECEIVER_DEBIT) AND @p_REQ_TYPE='I') --IF(@ACC_NUM <>@p_RECEIVER_DEBIT AND @p_REQ_TYPE='I' AND LEN(ISNULL(@ACC_NUM,''))>0) --BEGIN -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Tài khoản tạm ứng của nhân viên đã tồn tại trong hệ thống! Bạn không được phép chỉnh sửa' ErrorDesc -- RETURN '-1' --END --IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID) AND @p_REQ_TYPE='I') -- BEGIN -- INSERT INTO CM_ACCOUNT_PAY (REF_ID,ACC_TYPE,ACC_NUM,ACC_NAME,FUNTION,MAKER_ID,AUTH_STATUS,CREATE_DT,TK_GL,TK_GL_NAME) VALUES (@p_REF_ID,'ADV_PAY',@p_RECEIVER_DEBIT,'TK TAM UNG CUA NHAN VIEN ' +@p_REF_ID,'I',@p_MAKER_ID,'U',GETDATE(),'361200001','TAM UNG DE HOAT DONG NGHIEP VU') -- END --ELSE -- BEGIN -- IF(@ACC_NUM <>@p_RECEIVER_DEBIT AND @p_REQ_TYPE='I' AND LEN(ISNULL(@ACC_NUM,''))>0) -- BEGIN -- --ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Tài khoản tạm ứng của nhân viên đã tồn tại trong hệ thống! Bạn không được phép chỉnh sửa' ErrorDesc -- RETURN '-1' -- END --END --IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_NUM =@p_RECEIVER_DEBIT AND REF_ID = @p_REF_ID AND @p_REQ_TYPE='I')) --BEGIN -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Tài khoản tạm ứng số '+ @p_RECEIVER_DEBIT + N' không phải tài khoản của user: '+@p_REF_ID ErrorDesc -- RETURN '-1' --END IF(NOT EXISTS(SELECT * FROM CM_ACCOUNT_PAY WHERE ACC_NUM =@p_RECEIVER_DEBIT AND REF_ID = @p_REF_ID) AND @p_REQ_TYPE='I') BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Tài khoản tạm ứng số '+ @p_RECEIVER_DEBIT + N' không phải tài khoản của user: '+@p_REF_ID ErrorDesc RETURN '-1' END IF(LEN(ISNULL(@p_RECEIVER_DEBIT,''))>15 AND @p_REQ_TYPE ='I') BEGIN SELECT '-1' as Result, '' REQ_PAY_ID, N'Tài khoản tạm ứng này không được vượt quá 15 kí tự' ErrorDesc RETURN '-1' END -- 19-11-2019 LUCTV BO SUNG VALIDATE TRONG QUA TRINH TEST SAU KHI UAT -- NEU TAM UNG CHO NHA CUNG CAP THI DOT THANH TOAN KHONG DUOC PHEP NULL -- SO TIEN THANH TOÁN PHAI LON HON KHONG IF(@p_REQ_AMT <=0) BEGIN SELECT '-1' as Result, '' REQ_PAY_ID, N'Số tiền tạm ứng phải lớn hơn 0' ErrorDesc RETURN '-1' END -- END VALIDATE TRONG QUA TRINH TEST UAT BEGIN TRANSACTION --IF(@p_DVDM_ID IS NULL OR @p_DVDM_ID ='') --BEGIN -- SET @p_DVDM_ID = (SELECT TOP 1 DVDM_ID FROM CM_DVDM WHERE DVDM_NAME ='KHOI TAI CHINH') --END UPDATE TR_REQ_ADVANCE_PAYMENT SET REF_ID = @p_REF_ID, DEP_ID= @p_DEP_ID,REQ_REASON = @p_REQ_REASON, REQ_PAY_TYPE = @p_REQ_PAY_TYPE, REQ_AMT = @p_REQ_AMT, NOTES= @p_NOTES, REQ_TYPE= @p_REQ_TYPE,REQ_DESCRIPTION = @p_REQ_DESCRIPTION,RECEIVER_PO =@p_RECEIVER_PO, RECEIVER_DEBIT= @p_RECEIVER_DEBIT, REQ_TEMP_AMT=@p_REQ_TEMP_AMT,REQ_ENTRIES = @P_REQ_ENTRIES,BRANCH_ID = @p_BRANCH_ID,REQ_PAY_CODE = @p_REQ_PAY_CODE, PAY_PHASE =@p_PAY_PHASE, AUTH_STATUS='E', DVDM_ID=@p_DVDM_ID, RATE =@p_RATE,REQ_TYPE_CURRENCY =@p_REQ_TYPE_CURRENCY, RECIVER_MONEY= @p_RECIVER_MONEY, TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE,IS_PERIOD =@p_IS_PERIOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID IF @@Error <> 0 GOTO ABORT --- INSERT VÀO BẢNG DETAIL NÊU TẠM ỨNG LÀ TẠM ỨNG THANH TOÁN DECLARE @hdoc INT EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP; -- KIEM TRA NEU TAM UNG THANH TOAN IF((@p_REQ_TYPE='P' AND (@p_IS_PERIOD <>'Y' OR @p_IS_PERIOD IS NULL)) OR @p_REQ_TYPE='D') BEGIN DELETE FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID=@p_REQ_PAY_ID DELETE FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID=@p_REQ_PAY_ID DECLARE @PO_ID varchar(15),@TERM_PAY varchar(15),@AMT_PAY_SCHEDULE decimal(18,0),@REQ_ADV_ID_SCHE VARCHAR(15),@AMT_ADVANCE_SCHEDULE decimal(18,0),@AMT_PAY_DO decimal(18,2), @AMT_REMAIN_SCHEDULE decimal(18,0),@REQ_AD_DT varchar(20),@PROCESS varchar(15),@PAY_ID VARCHAR(15), @REF_ID VARCHAR(15),@REQ_PAY_DESC NVARCHAR(500), @REQ_PAY_ENTRIES NVARCHAR(250),@CURRENCY VARCHAR(15),@RATE DECIMAL(18,0),@CUST_ID VARCHAR(15), @EMP_ID VARCHAR(15),@AMT_PAY_REAL DECIMAL(18,2),@CHECK_IN VARCHAR(15),@IS_CLOSED VARCHAR(1)= NULL,@INDEX_NS INT =0,@REASON NVARCHAR(1000),@REF_TYPE VARCHAR(15) DECLARE XmlDataPO CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataPO', 2) WITH(REF_ID varchar(15),IS_CLOSED VARCHAR(1),REF_TYPE VARCHAR(15)) OPEN XmlDataPO; DECLARE @INDEX_PO INT =0 SET @INDEX_PO = 0 FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE WHILE @@fetch_status=0 BEGIN SET @INDEX_PO = @INDEX_PO +1 -- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN -- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='C' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO/ hợp đồng số '+ (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @REF_ID)+N' đang được tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END -- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='C' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': Hợp đồng số '+ (SELECT CONTRACT_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @REF_ID)+N' đang được thanh toán. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END -- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='P' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO số '+ (SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END -- KIEM TRA NEU CO PO NAO DO DANG THUC HIEN THANH TOAN HOAC TAM UNG THI PHAI THONG BAO CHO HOAN TAT GIAO DICH IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='P' AND REQ_PAY_ID IN (SELECT REQ_PAY_ID FROM TR_REQ_PAYMENT WHERE AUTH_STATUS_KT<>'A' OR AUTH_STATUS_KT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO số '+ (SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đang được thanh toán. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END --- KIEM TRA XEM PO NAY DUOC THANH TOAN HAY CHUA IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_DT WHERE REF_ID =@REF_ID AND TYPE ='P' AND REF_ID IN (SELECT PO_ID FROM TR_PO_MASTER WHERE IS_CLOSED ='Y' ))) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_PO)+ N': PO số '+ (SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID = @REF_ID)+N' đã được thanh toán. Vui lòng hủy bản nháp!' ErrorDesc RETURN '-1' END END DECLARE @REQ_PAYDTID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_ADVANCE_DT', @REQ_PAYDTID OUT; IF @REQ_PAYDTID='' OR @REQ_PAYDTID IS NULL GOTO ABORT; INSERT INTO TR_REQ_ADVANCE_DT (REQ_PAYDTID,REQ_PAY_ID,REF_ID,TYPE,MAKER_ID,CREATE_DT,IS_CLOSED,REF_TYPE) VALUES (@REQ_PAYDTID,@p_REQ_PAY_ID,@REF_ID,'ADV_PAY',@p_MAKER_ID,GETDATE(),@IS_CLOSED,@REF_TYPE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE END CLOSE XmlDataPO; DEALLOCATE XmlDataPO; --- CUSOR DANH SACH LICH THANH TOAN PO - HD DECLARE XmlDataSchedule CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataSchedule',2) WITH(PO_ID varchar(15),TERM_PAY varchar(15),AMT_PAY_SCHEDULE decimal(18,0),REQ_ADV_ID varchar(15),AMT_ADVANCE_SCHEDULE decimal(18,0), AMT_PAY_DO decimal(18,2), AMT_REMAIN_SCHEDULE decimal(18,0),REQ_AD_DT varchar(20),PROCESS varchar(15), PAY_ID VARCHAR(15),REF_ID VARCHAR(15), REQ_PAY_DESC NVARCHAR(500), REQ_PAY_ENTRIES NVARCHAR(250),CURRENCY VARCHAR(15),RATE DECIMAL(18,0),AMT_PAY_REAL DECIMAL(18,2)) OPEN XmlDataSchedule FETCH NEXT FROM XmlDataSchedule INTO @PO_ID,@TERM_PAY,@AMT_PAY_SCHEDULE,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE,@AMT_PAY_DO, @AMT_REMAIN_SCHEDULE,@REQ_AD_DT,@PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL WHILE @@fetch_status=0 BEGIN DECLARE @p_REQ_PAY_SCHEDULE_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_SCHEDULE', @p_REQ_PAY_SCHEDULE_ID OUT; IF @p_REQ_PAY_SCHEDULE_ID='' OR @p_REQ_PAY_SCHEDULE_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_SCHEDULE( SCHEDULE_ID,REQ_PAY_ID,PAY_ID,AMT_PAY,PAY_PHASE,REQ_ADV_ID,AMT_ADVANCE,AMT_PAY_DO,AMT_REMAIN,REQ_PAY_DT,REQ_AD_DT,PROCESS,MAKER_ID, CREATE_DT,AUTH_STATUS_KT,PAYMENT_STATUS,TRN_TYPE,REF_ID,REQ_PAY_DESC,REQ_PAY_ENTRIES,CURRENCY,RATE, AMT_PAY_REAL) VALUES (@p_REQ_PAY_SCHEDULE_ID,@p_REQ_PAY_ID,@PAY_ID,@AMT_PAY_SCHEDULE,@TERM_PAY,@p_REQ_PAY_ID,@AMT_ADVANCE_SCHEDULE,@AMT_PAY_DO,@AMT_REMAIN_SCHEDULE, GETDATE(),CONVERT(DATETIME,@REQ_AD_DT,103),NULL,@p_MAKER_ID,GETDATE(),'U','','ADV_PAY',@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL) --- END KHAI BAO CURSOR IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataSchedule INTO @PO_ID,@TERM_PAY,@AMT_PAY_SCHEDULE,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE,@AMT_PAY_DO, @AMT_REMAIN_SCHEDULE,@REQ_AD_DT,@PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL END CLOSE XmlDataSchedule; DEALLOCATE XmlDataSchedule; END --- END TẠM ỨNG THANH TOÁN ---- TẠM ỨNG HĐ ĐỊNH KÌ IF(@p_REQ_TYPE = 'P' AND @p_IS_PERIOD='Y') BEGIN DELETE FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID =@p_REQ_PAY_ID DELETE FROM TR_REQ_PAY_PERIOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID DECLARE XmlDataPO CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataPO', 2) WITH(REF_ID varchar(15),IS_CLOSED VARCHAR(1),REF_TYPE VARCHAR(15)) OPEN XmlDataPO; SET @INDEX_PO = 0 FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE WHILE @@fetch_status=0 BEGIN SET @INDEX_PO = @INDEX_PO +1 DECLARE @REQ_PAYDTID_C VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_ADVANCE_DT', @REQ_PAYDTID_C OUT; IF @REQ_PAYDTID_C='' OR @REQ_PAYDTID_C IS NULL GOTO ABORT; INSERT INTO TR_REQ_ADVANCE_DT (REQ_PAYDTID,REQ_PAY_ID,REF_ID,TYPE,MAKER_ID,CREATE_DT,IS_CLOSED,REF_TYPE) VALUES (@REQ_PAYDTID_C,@p_REQ_PAY_ID,@REF_ID,'ADV_PAY',@p_MAKER_ID,GETDATE(),@IS_CLOSED,@REF_TYPE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataPO INTO @REF_ID,@IS_CLOSED,@REF_TYPE END CLOSE XmlDataPO; DEALLOCATE XmlDataPO; --- CUSOR DANH SACH LICH THANH TOAN PO - HD ---------------------------- --INSERT FROM PERIOD DECLARE XmlDataPeriod CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataPeriod', 2) WITH(CONTRACT_ID varchar(15),PAY_DT_REAL VARCHAR(20),PAY_TYPE VARCHAR(5), OLD_INDEX DECIMAL(18,0),NEW_INDEX DECIMAL(18,0),AMT_PAY DECIMAL(18,2),CURRENCY VARCHAR(5), RATE DECIMAL(18,0), FROM_DATE VARCHAR(20), TO_DATE VARCHAR(20),PAY_PHASE NVARCHAR(250),REASON NVARCHAR(2000) ) OPEN XmlDataPeriod; DECLARE @CONTRACT_ID varchar(15),@PAY_DT_REAL VARCHAR(20),@PAY_TYPE VARCHAR(5), @OLD_INDEX DECIMAL(18,0),@NEW_INDEX DECIMAL(18,0),@AMT_PAY_PERIOD DECIMAL(18,2),@FROM_DATE VARCHAR(20),@TO_DATE VARCHAR(20),@PAY_PHASE NVARCHAR(250), @REASON_TTDK NVARCHAR(2000) FETCH NEXT FROM XmlDataPeriod INTO @CONTRACT_ID ,@PAY_DT_REAL ,@PAY_TYPE , @OLD_INDEX ,@NEW_INDEX ,@AMT_PAY_PERIOD,@CURRENCY, @RATE,@FROM_DATE,@TO_DATE,@PAY_PHASE,@REASON_TTDK WHILE @@fetch_status=0 BEGIN -- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN --IF(EXISTS(SELECT CONTRACT_ID --FROM TR_CONTRACT --WHERE CONTRACT_ID = @REF_ID AND CONVERT(DATE,END_DT,103) < CONVERT(DATE,GETDATE(),103) AND END_DT IS NOT NULL AND CONT_TYPE ='DK' AND CONT_TYPE IS NOT NULL AND CONT_TYPE <>'')) ----AND IS_CLOSED='Y' )) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Tạm ứng hợp đồng định kì đã đóng' ErrorDesc -- RETURN '-1' --END IF(@NEW_INDEX <=@OLD_INDEX AND @NEW_INDEX >0 AND @OLD_INDEX >0) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, @p_REQ_PAY_ID REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Chỉ số mới phải lớn hơn chỉ số cũ' ErrorDesc RETURN '-1' END END DECLARE @PERIOD_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_PERIOD', @PERIOD_ID OUT; IF @PERIOD_ID='' OR @PERIOD_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_PERIOD(PERIOD_ID,REQ_PAY_ID,PAY_TYPE, CONTRACT_ID,PAY_DT_REAL,OLD_INDEX,NEW_INDEX,AMT_PAY,AUTH_STATUS_KT,CURRENCY,RATE,FROM_DATE, TO_DATE,AD_PAY_ID,PROCESS,PAY_PHASE, REASON) VALUES (@PERIOD_ID,@p_REQ_PAY_ID,'ADV_PAY',@CONTRACT_ID,CONVERT(DATE,@PAY_DT_REAL,103), @OLD_INDEX ,@NEW_INDEX ,@AMT_PAY_PERIOD,'U',@CURRENCY, @RATE,CONVERT(DATE,@FROM_DATE,103),CONVERT(DATE,@TO_DATE,103),@p_REQ_PAY_ID,'0',@PAY_PHASE,@REASON_TTDK) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataPeriod INTO @CONTRACT_ID ,@PAY_DT_REAL ,@PAY_TYPE , @OLD_INDEX ,@NEW_INDEX ,@AMT_PAY_PERIOD,@CURRENCY, @RATE,@FROM_DATE,@TO_DATE,@PAY_PHASE,@REASON_TTDK END CLOSE XmlDataPeriod; DEALLOCATE XmlDataPeriod; -- VALIDATE SO TIEN --IF(@p_REQ_AMT >= @TOTAL_SCHEDULE_AMT) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Số tiền thanh toán phải nhỏ hơn hoặc bằng tổng số tiền còn lại cần thanh toán: '+FORMAT(SUM(@TOTAL_SCHEDULE_AMT),'#,#', 'vi-VN') ErrorDesc -- RETURN '-1' --END ---- END ---- END TẠM ỨNG HĐ ĐỊNH KÌ --- INSERT PHƯƠNG THỨC THANH TOÁN ----MethodCursor DELETE FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID DECLARE @RECEIVE_ID varchar(15),@RECEIVE_NAME nvarchar(100),@REQ_PAY_REASON nvarchar(MAX),@TOTAL_AMT_METHOD decimal(18,2),@REQ_PAY_TYPE varchar(1), @ACC_NO VARCHAR(50), @ACC_NAME NVARCHAR(250), @ISSED_BY NVARCHAR(250), @ISSUED_DT VARCHAR(20) DECLARE XmlDataMethod CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataMethod',2) WITH(RECEIVE_ID varchar(15),RECEIVE_NAME nvarchar(100),REQ_PAY_REASON nvarchar(MAX),TOTAL_AMT decimal(18,2),REQ_PAY_TYPE varchar(1), REQ_PAY_DESC nvarchar(MAX),REQ_PAY_ENTRIES nvarchar(MAX),ACC_NO VARCHAR(50), ACC_NAME NVARCHAR(250), ISSUED_BY NVARCHAR(250), ISSUED_DT VARCHAR(20),CURRENCY VARCHAR(15),RATE DECIMAL(18,0),CHECK_IN VARCHAR(15)) OPEN XmlDataMethod FETCH NEXT FROM XmlDataMethod INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO,@ACC_NAME,@ISSED_BY,@ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN WHILE @@fetch_status=0 BEGIN IF(@REQ_PAY_TYPE<>'1') BEGIN SET @ISSUED_DT = NULL END DECLARE @p_REQ_PAY_METHOD_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_METHOD', @p_REQ_PAY_METHOD_ID OUT; IF @p_REQ_PAY_METHOD_ID='' OR @p_REQ_PAY_METHOD_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_METHOD(METHOD_ID,REQ_PAY_ID,EMP_ID,REQ_PAY_REASON,TOTAL_AMT,REQ_PAY_TYPE,REQ_PAY_DESC,REQ_PAY_ENTRIES, TEMP,MAKER_ID, CREATE_DT,ACC_NO,ACC_NAME,ISSUED_BY,ISSUED_DT,CURRENCY,RATE,CHECK_IN) VALUES (@p_REQ_PAY_METHOD_ID,@p_REQ_PAY_ID,@RECEIVE_ID,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,'', @p_MAKER_ID,GETDATE(),@ACC_NO,@ACC_NAME,@ISSED_BY, CONVERT(DATE, @ISSUED_DT, 103),@CURRENCY,@RATE,@CHECK_IN) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataMethod INTO @RECEIVE_ID,@RECEIVE_NAME,@REQ_PAY_REASON,@TOTAL_AMT_METHOD,@REQ_PAY_TYPE,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@ACC_NO,@ACC_NAME , @ISSED_BY , @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN END CLOSE XmlDataMethod; DEALLOCATE XmlDataMethod ----END INSERT PHƯƠNG THỨC THANH TOÁN ----INSERT VAO BANG DS KHACH HANG DELETE FROM TR_REQ_ADVANCE_CUS WHERE REQ_PAY_ID = @p_REQ_PAY_ID DECLARE XmlDataCus CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlDataCus', 2) WITH(CUS_ID varchar(15), EMP_ID VARCHAR(15), AMT_PAY_DO decimal(18,2),CURRENCY VARCHAR(15),RATE DECIMAL(18,0)) OPEN XmlDataCus; FETCH NEXT FROM XmlDataCus INTO @CUST_ID, @EMP_ID,@AMT_PAY_DO,@CURRENCY,@RATE WHILE @@fetch_status=0 BEGIN INSERT INTO TR_REQ_ADVANCE_CUS(REQ_PAY_ID,CUST_ID,EMP_ID,AMT_PAY_DO,CURRENCY,RATE,MAKER_ID,CREATE_DT) VALUES (@p_REQ_PAY_ID, @CUST_ID, @EMP_ID,@AMT_PAY_DO,@CURRENCY,@RATE,@p_MAKER_ID,GETDATE()) --- END KHAI BAO CURSOR IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataCus INTO @CUST_ID, @EMP_ID,@AMT_PAY_DO,@CURRENCY,@RATE END CLOSE XmlDataCus; DEALLOCATE XmlDataCus; ----END -- HANG MUC CHI PHI VA NGAN SACH DELETE FROM TR_REQ_PAY_BUDGET WHERE REQ_PAY_ID =@p_REQ_PAY_ID DECLARE @TRADE_ID varchar(15),@GD_ID varchar(15),@AMT_APP decimal(18,2),@AMT_DO decimal(18,2),@AMT_EXE decimal(18,2),@AMT_REMAIN_GD decimal(18,2) DECLARE XmlDataGood CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataGood',2) WITH(TRADE_ID varchar(15),GD_ID varchar(15),AMT_APP decimal(18,2),AMT_DO decimal(18,2), AMT_EXE decimal(18,2),AMT_REMAIN decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),REASON NVARCHAR(1000)) OPEN XmlDataGood FETCH NEXT FROM XmlDataGood INTO @TRADE_ID,@GD_ID ,@AMT_APP ,@AMT_DO,@AMT_EXE ,@AMT_REMAIN_GD,@CURRENCY,@RATE,@REASON WHILE @@fetch_status=0 BEGIN --- KIEM TRA NEU NGAN SACH SU DUNG THUC THE LON HON NGAN SACH CON LAI SET @INDEX_NS = @INDEX_NS +1 -- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE --IF(@p_TYPE_FUNCTION ='SEND') --BEGIN --IF((@AMT_EXE > @AMT_REMAIN_GD)) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_NS)+ N': Ngân sách sử dụng thực tế không được vượt quá ngân sách còn lại.' ErrorDesc -- RETURN '-1' --END --END IF(@p_TYPE_FUNCTION ='SEND') BEGIN IF(ISNULL(@AMT_EXE,0) =0) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_NS)+ N': Ngân sách sử dụng thực tế phải lớn hơn không.' ErrorDesc RETURN '-1' END --IF((@AMT_EXE > @AMT_REMAIN_GD) AND ((SELECT GD_TYPE_ID FROM CM_GOODS WHERE GD_ID =@GD_ID) ='NS')) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_NS)+ N': Ngân sách sử dụng thực tế không được vượt quá ngân sách còn lại.' ErrorDesc -- RETURN '-1' --END END DECLARE @p_BUDGET_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_BUDGET', @p_BUDGET_ID OUT; IF @p_BUDGET_ID='' OR @p_BUDGET_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_BUDGET(BUDG_ID,GD_ID,REQ_PAY_ID,TRADE_ID,AMT_APP,AMT_DO,AMT_EXE,AMT_REMAIN,MAKER_ID,CREATE_DT,CURRENCY,RATE,REASON) VALUES (@p_BUDGET_ID,@GD_ID,@p_REQ_PAY_ID,@TRADE_ID,@AMT_APP,@AMT_DO,@AMT_EXE,@AMT_REMAIN_GD,@p_MAKER_ID, GETDATE(),@CURRENCY,@RATE,@REASON) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataGood INTO @TRADE_ID,@GD_ID ,@AMT_APP ,@AMT_DO,@AMT_EXE ,@AMT_REMAIN_GD,@CURRENCY,@RATE,@REASON END; CLOSE XmlDataGood; DEALLOCATE XmlDataGood; --- END INSERT NGAN SACH DELETE FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID DECLARE @ATTACH_ID varchar(15),@CON_ID varchar(100),@CONT_CODE VARCHAR(50), @CON_NOTES VARCHAR(1000),@CON_DESC NVARCHAR(1000),@CON_TYPE VARCHAR(15),@AMT_ATT DECIMAL(18,2),@REF_DT VARCHAR(20),@NOTES_ATT NVARCHAR(1000) DECLARE XmlAttach CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlAttach',2) WITH(ATTACH_ID varchar(15),CON_ID varchar(100),CONT_CODE VARCHAR(50), CON_NOTES NVARCHAR(1000),CON_DESC NVARCHAR(1000),CON_TYPE VARCHAR(15),AMT_ATT DECIMAL(18,2),REF_DT VARCHAR(20)) OPEN XmlAttach --INSERT CHUNG TU DINH KEM FETCH NEXT FROM XmlAttach INTO @ATTACH_ID,@CON_ID,@CONT_CODE,@NOTES_ATT,@CON_DESC,@CON_TYPE,@AMT_ATT,@REF_DT WHILE @@fetch_status=0 BEGIN IF (@REF_DT='') BEGIN SET @REF_DT = NULL END EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_ATTACH', @ATTACH_ID OUT; IF @ATTACH_ID='' OR @ATTACH_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_ATTACH(ATTACH_ID,REQ_PAY_ID,REF_ID,REF_CODE,NOTES,[DESCRIPTION],[TYPE],AMT,REF_DT) VALUES (@ATTACH_ID,@p_REQ_PAY_ID,@CON_ID,@CONT_CODE,@NOTES_ATT,@CON_DESC,@CON_TYPE,@AMT_ATT, CONVERT(DATE, @REF_DT,103)) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlAttach INTO @ATTACH_ID,@CON_ID,@CONT_CODE,@NOTES_ATT,@CON_DESC,@CON_TYPE,@AMT_ATT,@REF_DT END CLOSE XmlAttach; DEALLOCATE XmlAttach; ----END --- BAT DAU VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN DECLARE @SUM_PTTT DECIMAL(18,0), @SUM_NS DECIMAL(18,0),@SUM_LICH_TT DECIMAL(18,0) SET @SUM_PTTT =(SELECT SUM(TOTAL_AMT * ISNULL(RATE,1)) FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) IF(EXISTS(SELECT * FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID =@p_REQ_PAY_ID) AND @p_REQ_TYPE ='D') BEGIN SET @SUM_LICH_TT =(SELECT SUM(AMT_PAY_REAL* ISNULL(RATE,1)) FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID =@p_REQ_PAY_ID) IF(@p_REQ_AMT*ISNULL(@p_RATE,1) != ISNULL(@SUM_LICH_TT,0)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Tổng số tiền lịch thanh toán PO và hợp đồng liên quan đến khách hàng xử lý nợ phải bằng số tiền tạm ứng' ErrorDesc RETURN '-1' END END IF(EXISTS(SELECT * FROM TR_REQ_PAY_BUDGET WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) BEGIN SET @SUM_NS =(SELECT SUM(AMT_EXE * ISNULL(RATE,1)) FROM TR_REQ_PAY_BUDGET WHERE REQ_PAY_ID =@p_REQ_PAY_ID) IF(@p_REQ_AMT*ISNULL(@p_RATE,1) != ISNULL(@SUM_PTTT,0)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Tổng số tiền sử dụng ngân sách và chi phí phải bằng số tiền tạm ứng' ErrorDesc RETURN '-1' END END ELSE IF(@p_REQ_AMT*ISNULL(@p_RATE,1) != ISNULL(@SUM_PTTT,0)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' AS REQ_PAY_CODE,N'Tổng số tiền phương thức thanh toán phải bằng số tiền tạm ứng' ErrorDesc RETURN '-1' END END ----END IF(@p_REQ_TYPE ='I') BEGIN DELETE FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID DECLARE @tmp table(ROLE_ID VARCHAR(25),BRANCH_ID varchar(15),AMT decimal(18,2)) IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_CREATE) ='HS') BEGIN --DECLARE @DEP_CODE VARCHAR(15) --SET @DEP_CODE =(SELECT * FROM CM_DEPARTMENT WHERE DEP_ID =@p_DEP_ID) IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE (DEP_CODE LIKE '%06908%' OR DEP_CODE ='0690605' OR DEP_CODE ='0690405') AND DEP_ID =@p_DEP_ID) BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK' END ELSE IF EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06910%' AND DEP_ID =@p_DEP_ID) BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'GDK' END ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06900%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06901%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06902%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06903%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06904%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06920%' AND DEP_ID =@p_DEP_ID)) BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID NOT IN ('GDK','PTGD') END ELSE IF (EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06907%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06909%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06911%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06921%' AND DEP_ID =@p_DEP_ID) OR EXISTS(SELECT * FROM CM_DEPARTMENT WHERE DEP_CODE LIKE '%06922%' AND DEP_ID =@p_DEP_ID)) BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' AND A.ROLE_ID <>'PTGD' END ELSE BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' END END ELSE BEGIN IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_CREATE) ='CN') BEGIN --- BAO GOM DON VI & 3 CAP DUYET CUA HOI SO INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@p_BRANCH_CREATE --INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' END ELSE IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_CREATE) ='PGD') BEGIN INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY' AND BRANCH_ID =@p_BRANCH_CREATE --BRANCH_ID =(SELECT FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_CREATE) --INSERT INTO @tmp SELECT A.ROLE_ID, A.BRANCH_ID,A.LIMIT_VALUE FROM TL_SYSROLE_LIMIT A WHERE A.LIMIT_TYPE='ADV_PAY'AND BRANCH_ID ='DV0001' END END DECLARE @ROLE_ID VARCHAR(20), @BRANCH_ID VARCHAR(20), @INDEX INT =0, @SL_ROLE INT =0,@LIMIT_VALUE DECIMAL(18,0) SET @SL_ROLE = (SELECT COUNT(*) FROM @tmp) DECLARE @ISLEAF VARCHAR(1) = 'Y', @PARENT_ID VARCHAR(10) = '', @STATUS VARCHAR(1) = 'C', @NOTES NVARCHAR(500) DECLARE CUR_PR CURSOR FOR SELECT A.ROLE_ID, A.BRANCH_ID,A.AMT FROM @tmp A OPEN CUR_PR FETCH NEXT FROM CUR_PR INTO @ROLE_ID,@BRANCH_ID,@LIMIT_VALUE WHILE @@FETCH_STATUS =0 --AND @STOP=0 BEGIN SET @INDEX= @INDEX+1 IF @INDEX = @SL_ROLE SET @ISLEAF = 'Y' ELSE SET @ISLEAF = 'N' SET @NOTES = (SELECT N'Đợi '+ ROLE_DESC + N' phê duyệt' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID) IF(@INDEX=1 ) BEGIN SET @PARENT_ID = NULL SET @STATUS = 'C' --INSERT INTO TR_REQUEST_PROCESS VALUES (@p_REQ_PAY_ID,CONVERT(VARCHAR(5), @INDEX),'C',@ROLE_ID,@BRANCH_ID,NULL,NULL,NULL,@ISLEAF) END ELSE BEGIN SET @PARENT_ID = CONVERT(VARCHAR(5),@INDEX -1) SET @STATUS = 'U' END IF(@LIMIT_VALUE >= @p_REQ_AMT) BEGIN INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES ) VALUES (@p_REQ_PAY_ID,CONVERT(VARCHAR(5), @INDEX),@STATUS,@ROLE_ID,@BRANCH_ID,NULL,NULL, @PARENT_ID,'Y', '',@p_DVDM_ID,@NOTES) --INSERT INTO PL_REQUEST_PROCESS VALUES (@p_REQ_PAY_ID,CONVERT(VARCHAR(5), @INDEX),@STATUS,@ROLE_ID,@BRANCH_ID,NULL,NULL, @PARENT_ID,'Y') BREAK; END ELSE INSERT INTO PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES ) VALUES (@p_REQ_PAY_ID,CONVERT(VARCHAR(5), @INDEX),@STATUS,@ROLE_ID,@BRANCH_ID,NULL,NULL, @PARENT_ID,@ISLEAF, '', @p_DVDM_ID, @NOTES) --END FETCH NEXT FROM CUR_PR INTO @ROLE_ID,@BRANCH_ID,@LIMIT_VALUE END CLOSE CUR_PR DEALLOCATE CUR_PR END COMMIT TRANSACTION IF(@p_TYPE_FUNCTION ='SEND')-- BAT DAU GUI PHE DUYET BEGIN --ROLLBACK TRANSACTION -- CAP NHAT TINH TRANG DUYET KE TOAN LA DANG XU LY IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE (TRASFER_USER_RECIVE ='' OR TRASFER_USER_RECIVE IS NULL) AND REQ_PAY_ID =@p_REQ_PAY_ID ) AND @BRANCH_TYPE_CR ='HS' AND @p_REQ_TYPE ='I') BEGIN DECLARE @USER_TP VARCHAR(15) ='' --SET @USER_TP =(SELECT TOP 1 TLNANME FROM TL_USER WHERE SECUR_CODE =@p_DEP_ID -- AND (RoleName IN ('GDDV','TPTC','TC','KTT') OR RoleName IN (SELECT ROLE_OLD FROM TL_SYS_ROLE_MAPPING WHERE ROLE_NEW ='GDDV'))) IF(@USER_TP IS NULL OR @USER_TP ='') BEGIN SET @USER_TP = (SELECT TOP 1 TLNANME FROM TL_USER_V2 WHERE SECUR_CODE =@p_DEP_ID AND (RoleName IN ('TBP','PP'))) END UPDATE TR_REQ_ADVANCE_PAYMENT SET TRASFER_USER_RECIVE =@USER_TP WHERE REQ_PAY_ID =@p_REQ_PAY_ID END --IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE TRASFER_USER_RECIVE ='' OR TRASFER_USER_RECIVE IS NULL AND REQ_PAY_ID =@p_REQ_PAY_ID) AND @BRANCH_TYPE_CR ='PGD' AND @p_REQ_TYPE ='I') --BEGIN -- DECLARE @USER_TPGD VARCHAR(15) ='' -- SET @USER_TPGD =(SELECT TOP 1 TLNANME FROM TL_USER WHERE SECUR_CODE =@p_DEP_ID AND (RoleName IN ('TPGD'))) -- IF(@USER_TPGD IS NULL OR @USER_TPGD ='') -- BEGIN -- SET @USER_TPGD = (SELECT TOP 1 TLNANME FROM TL_USER_V2 WHERE TLSUBBRID =@p_BRANCH_CREATE -- AND RoleName ='TPGD') -- END -- UPDATE TR_REQ_ADVANCE_PAYMENT SET TRASFER_USER_RECIVE =@USER_TPGD WHERE REQ_PAY_ID =@p_REQ_PAY_ID --END DECLARE @SUM_THANH_TOAN DECIMAL(18,0) =0, @SUM_PHUONG_THUC DECIMAL(18,0) =0 SET @SUM_THANH_TOAN =(SELECT ISNULL(SUM(REQ_AMT * ISNULL(RATE,1)),0) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) SET @SUM_PHUONG_THUC =(SELECT ISNULL(SUM(TOTAL_AMT * ISNULL(RATE,1)),0) FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) IF(ROUND(@SUM_THANH_TOAN,0) <> ROUND(@SUM_PHUONG_THUC,0)) BEGIN SELECT '-1' as Result, '' REQ_PAY_ID, N'Số tiền phương thức thanh toán phải bằng số tiền bạn cần thanh toán là: ' + FORMAT(SUM(@SUM_THANH_TOAN),'#,#', 'vi-VN') ErrorDesc RETURN '-1' END IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE TRASFER_USER_RECIVE <>'' AND TRASFER_USER_RECIVE IS NOT NULL AND REQ_PAY_ID =@p_REQ_PAY_ID) AND @BRANCH_TYPE_CR ='PGD' AND @p_REQ_TYPE <>'I') BEGIN SELECT '-1' as Result, '' REQ_PAY_ID, N'Ở cấp độ phòng giao dịch. Vui lòng không được chọn cấp phê duyệt trung gian' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS='U')) BEGIN SELECT '-1' as Result, '' REQ_PAY_ID, N'Phiếu đề nghị tạm ứng đã được gửi phê duyệt trước đó! Vui lòng đợi các cấp phê duyệt' ErrorDesc RETURN '-1' END --IF(NOT EXISTS(SELECT * FROM TR_REQ_PAY_BUDGET WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND @p_REQ_TYPE='P' AND (@p_IS_PERIOD IS NULL OR @p_IS_PERIOD ='' OR @p_IS_PERIOD ='N'))) --BEGIN -- SELECT '-1' as Result, '' REQ_PAY_ID, N'Thông tin hạng mục ngân sách và chi phí không được để trống. Vui lòng chọn ngân sách và chi phí' ErrorDesc -- RETURN '-1' --END UPDATE TR_REQ_ADVANCE_PAYMENT SET AUTH_STATUS ='U',CREATE_DT = GETDATE() WHERE REQ_PAY_ID =@p_REQ_PAY_ID INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES) VALUES(@p_REQ_PAY_ID,'SEND',@p_MAKER_ID,GETDATE(), N'Đơn vị gửi phê duyệt',N'Đơn vị tạo phiếu tạm ứng và gửi phê duyệt') IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE (TRASFER_USER_RECIVE <> '' OR TRASFER_USER_RECIVE IS NOT NULL) AND REQ_PAY_ID =@p_REQ_PAY_ID)) BEGIN SELECT '4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Đơn vị gửi phiếu phê duyệt phiếu đề nghị tạm ứng số: ' + ISNULL(@p_REQ_PAY_CODE,'') + N' thành công. Vui lòng đợi cấp phê duyệt trung gian xác nhận phiếu' ErrorDesc RETURN '4' END ELSE BEGIN SELECT '4' as Result, @p_REQ_PAY_ID REQ_PAY_ID, N'Đơn vị gửi phiếu phê duyệt phiếu đề nghị tạm ứng số: ' + ISNULL(@p_REQ_PAY_CODE,'') + N' thành công. Vui lòng đợi trưởng đơn vị phê duyệt phiếu' ErrorDesc RETURN '4' END --SELECT '4' as Result, @p_REQ_PAY_ID AS REQ_PAY_ID, N'Gửi phê duyệt thành công' ErrorDesc --RETURN '4' END SELECT '0' as Result, @p_REQ_PAY_ID REQ_PAY_ID, '' ErrorDesc RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc RETURN '-1' End ¿ ALTER PROCEDURE [dbo].[rpt_TR_ADVANCE_TEMP_PAYMENT_ByID] @p_REQ_PAY_ID varchar(15)= NULL AS ------- DECLARE @TITLE_APPROVE NVARCHAR(50) ='', @TITLE_APPROVE_KSV NVARCHAR(50) ='' IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT='S')) BEGIN SET @TITLE_APPROVE =N'Đã từ chối ' END ELSE BEGIN SET @TITLE_APPROVE =N'Đã chấp thuận ' END ---------------- IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT='R')) BEGIN SET @TITLE_APPROVE_KSV =N'Đã từ chối ' END ELSE BEGIN SET @TITLE_APPROVE_KSV =N'Đã chấp thuận ' END ---------------- DECLARE @TRUONG_DV NVARCHAR(50),@DVKD_APP_DT NVARCHAR(50), @SO_DU_TAM_UNG DECIMAL(18,2) DECLARE @DATE_APPR DATETIME SET @DATE_APPR =ISNULL((SELECT TOP 1 CONVERT(DATETIME,APPROVE_DT,103) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID),CONVERT(DATETIME,GETDATE(),103)) DECLARE @REQ_TYPE VARCHAR(15) SET @REQ_TYPE =(SELECT TOP 1 REQ_TYPE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID) ------------------------------------------- DECLARE @CONTENT NVARCHAR(MAX)='', @CONTENT_CK NVARCHAR(MAX)='', @REQ_PAY_TYPE VARCHAR(5), @ACCNO VARCHAR(50), @ACCNAME NVARCHAR(500), @BANK NVARCHAR(500), @ISUED_DT DATE,@REASON NVARCHAR(MAX), @DESC NVARCHAR(MAX),@NDTM NVARCHAR(MAX) ='', @NDCK NVARCHAR(MAX) ='' DECLARE CURS CURSOR FOR SELECT A.REQ_PAY_TYPE, A.ACC_NO,A.ACC_NAME,A.ISSUED_BY,A.ISSUED_DT,A.REQ_PAY_REASON,A.REQ_PAY_ENTRIES FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID DECLARE @NOIDUNG NVARCHAR(MAX) ='' --- LAY NOI DUNG THANH TOAN NEU THANH TOAN CHO NHA CUNG CAP DECLARE CURS_PO CURSOR FOR SELECT A.REQ_PAY_DESC,A.REQ_PAY_ENTRIES FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID DECLARE @NOIDUNG_PO NVARCHAR(MAX) ='' DECLARE @BRANCH_TYPE VARCHAR(15), @BRANCH_RQ VARCHAR(15),@TOTAL_ADVANCE DECIMAL(18,0), @TOTAL_PAYBACK DECIMAL(18,0) SET @BRANCH_RQ =(SELECT TOP 1 BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) SET @BRANCH_TYPE = (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ) IF(@BRANCH_TYPE ='HS' AND @REQ_TYPE ='I') BEGIN SET @TRUONG_DV = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = (SELECT top 1 CHECKER_ID FROM PL_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND PROCESS_ID='0' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='0'))) SET @DVKD_APP_DT =(SELECT TOP 1 ISNULL(FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND PROCESS_ID='0' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='0')) IF(@DVKD_APP_DT IS NOT NULL AND @DVKD_APP_DT <>'') BEGIN SET @DVKD_APP_DT =N'Đã chấp thuận '+ @DVKD_APP_DT END END ELSE IF (@BRANCH_TYPE = 'PGD' AND @REQ_TYPE ='I') BEGIN SET @TRUONG_DV =(SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = (SELECT TOP 1 CHECKER_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) --AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='0'))) SET @DVKD_APP_DT =(SELECT TOP 1 ISNULL(FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) --AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='0')) IF(@DVKD_APP_DT IS NOT NULL AND @DVKD_APP_DT <>'') BEGIN SET @DVKD_APP_DT =N'Đã chấp thuận '+ @DVKD_APP_DT END END ELSE BEGIN SET @TRUONG_DV = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = (SELECT top 1 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 @DVKD_APP_DT =(SELECT TOP 1 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') SET @DVKD_APP_DT = (SELECT TOP 1 ISNULL(FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID ) IF(@DVKD_APP_DT IS NOT NULL AND @DVKD_APP_DT <>'') BEGIN SET @DVKD_APP_DT =N'Đã chấp thuận '+ @DVKD_APP_DT END END DECLARE @TMP_DVDM TABLE(DVDM_ID VARCHAR(15)) INSERT INTO @TMP_DVDM SELECT A.DVDM_ID--, A.COST_CODE, A.COST_NAME, B.DEP_ID --, C.DEP_CODE, C.DEP_NAME FROM PL_COSTCENTER A LEFT JOIN PL_COSTCENTER_DT B ON A.COST_ID = B.COST_ID WHERE B.DEP_ID = (SELECT TOP 1 DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) GROUP BY A.DVDM_ID DECLARE @tmp_CN table(BRANCH_ID varchar(15)) IF((SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='CN') BEGIN INSERT into @tmp_CN SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_RQ) END ELSE IF((SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='PGD') BEGIN DECLARE @FATHER_ID VARCHAR(15) = NULL SET @FATHER_ID =(SELECT TOP 1 FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ) INSERT into @tmp_CN SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@FATHER_ID) END SET @SO_DU_TAM_UNG = (SELECT AMT FROM TR_REQ_ADVANCE_LIMIT_LOG WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND ID =(SELECT MAX(ID) FROM TR_REQ_ADVANCE_LIMIT_LOG WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) --IF(@BRANCH_TYPE='HS' AND @REQ_TYPE ='I') -- BEGIN -- SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE -- DEP_ID IN -- (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A -- LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID -- WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ) -- AND REQ_TYPE ='I' AND AUTH_STATUS ='A' AND (CONVERT(DATETIME,APPROVE_DT,103) <=@DATE_APPR OR APPROVE_DT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID),0) -- SET @TOTAL_PAYBACK = -- ISNULL( -- ( -- SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT)) -- FROM TR_REQ_PAYMENT_DT B -- INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID -- INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID -- WHERE A.DEP_ID IN -- (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A -- LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID -- WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ) -- AND A.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I' AND (CONVERT(DATETIME,A.APPROVE_DT_KT,103) <=@DATE_APPR OR A.APPROVE_DT_KT IS NULL) AND B.PAY_ADV_ID <>@p_REQ_PAY_ID -- ),0) -- END -- -- NEU PHIEU DUOC TAO KHAC VOI HOI SO THI TIEN HANH TINH TONG SO TIEN TAM UNG CUA CN VA PHONG GIAO DICH TRUC THUOC -- ELSE IF(@BRANCH_TYPE <>'HS') -- BEGIN -- SET @TOTAL_ADVANCE =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN) -- AND REQ_TYPE ='I' AND AUTH_STATUS='A' AND (CONVERT(DATETIME,APPROVE_DT,103) <=@DATE_APPR OR APPROVE_DT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID) -- SET @TOTAL_PAYBACK = -- ( -- SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT)) -- FROM TR_REQ_PAYMENT_DT B -- INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID -- INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID -- WHERE A.BRANCH_ID IN -- (SELECT BRANCH_ID FROM @tmp_CN) -- AND A.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I' AND (CONVERT(DATETIME,A.APPROVE_DT_KT,103) <=@DATE_APPR OR A.APPROVE_DT_KT IS NULL) AND B.PAY_ADV_ID <>@p_REQ_PAY_ID -- ) -- END --- SELECT A.REQ_PAY_CODE, A.REQ_REASON,A.DEP_ID, N'Đã chấp thuận '+ISNULL(FORMAT(A.CREATE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') AS CREATE_DT, A.MAKER_ID_KT,A.CHECKER_ID, CASE WHEN ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') <>'' THEN N'Đã chấp thuận '+ ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS APPROVE_DT,A.CHECKER_ID_KT,MAKER_ID_KT, CASE WHEN ISNULL(FORMAT(A.APPROVE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') <>'' THEN @TITLE_APPROVE_KSV + ISNULL(FORMAT(A.APPROVE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS APPROVE_DT_KT , CASE WHEN ISNULL(FORMAT(A.CREATE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') <>'' THEN @TITLE_APPROVE +ISNULL(FORMAT(A.CREATE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS CREATE_DT_KT, B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, CASE WHEN A.REF_ID IS NOT NULL AND A.REF_ID <>'' THEN TL6.TLFullName ELSE TL.TLFullName END CREATE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT, BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ, --Luanlt--2019/10/15-Sửa AL,AL1 BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, 0 TOTAL_AMT_TEMP, 0 TOTAL_AMT_PAY_HIS,TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME, DAY(A.CREATE_DT) D_, MONTH(A.CREATE_DT) M_, YEAR(A.CREATE_DT) Y_, CASE WHEN A.REQ_TYPE='I' THEN CAST(1 AS BIT) WHEN A.REQ_TYPE <>'I' THEN CAST(0 AS BIT) END AS IS_NB, CASE WHEN A.REQ_TYPE='P' THEN CAST(1 AS BIT) WHEN A.REQ_TYPE <>'P' THEN CAST(0 AS BIT) END AS IS_NCC, CASE WHEN A.REQ_TYPE='D' THEN CAST(1 AS BIT) WHEN A.REQ_TYPE <>'D' THEN CAST(0 AS BIT) END AS IS_XLN, CASE WHEN LEN(@CONTENT) >0 THEN LEFT(@CONTENT,LEN(@CONTENT)-2) ELSE '' END AS TTTM, CASE WHEN LEN(@CONTENT_CK) >0 THEN LEFT(@CONTENT_CK, LEN(@CONTENT_CK)-2) ELSE NULL END AS TTCK, CASE WHEN LEN(@CONTENT) >0 THEN N'Tiền mặt:' ELSE '' END AS TM_TITLE, CASE WHEN LEN(@CONTENT_CK) >0 THEN N'Chuyển khoản:' ELSE NULL END AS CK_TITLE, --CASE WHEN @REQ_TYPE <>'P' THEN LEFT(@NOIDUNG, LEN(@NOIDUNG)-2) ELSE LEFT(@NOIDUNG_PO,LEN(@NOIDUNG_PO)-2) END AS NOIDUNG, TL1.TLFullName AS APPROVE_FULLNAME, --ISNULL(A.REQ_AMT,0) AS SO_TIEN_PTTT, CASE WHEN LEN(@NDTM + @NDCK) >0 THEN LEFT(@NDTM + @NDCK,LEN(@NDTM + @NDCK)-2) ELSE '' END AS PHUONG_THUC_THANH_TOAN, CASE WHEN EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND CURRENCY <>'VND') THEN (SELECT TOP 1 CURRENCY FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) ELSE 'VND' END AS TYPE_CUR, CASE WHEN EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND CURRENCY <>'VND') THEN (SELECT SUM(TOTAL_AMT) FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) ELSE A.REQ_AMT END AS SO_TIEN_PTTT, --(ISNULL(@TOTAL_ADVANCE,0) -ISNULL(@TOTAL_PAYBACK,0))+A.REQ_AMT*A.RATE AS SO_DU_TAM_UNG, ISNULL(@SO_DU_TAM_UNG,0.00) AS SO_DU_TAM_UNG, CASE WHEN EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND CURRENCY <>'VND') THEN (SELECT SUM(TOTAL_AMT) FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) ELSE A.REQ_AMT END AS REQ_AMT, @TRUONG_DV AS TRUONG_DV, @DVKD_APP_DT AS DVKD_APP_DT,CASE WHEN A.CONFIRM_NOTES <>'' AND A.CONFIRM_NOTES IS NOT NULL THEN N'Ghi chú: '+ A.CONFIRM_NOTES ELSE '' END AS CONFIRM_NOTES FROM TR_REQ_ADVANCE_PAYMENT A LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID --Luanlt--2019/10/15-Sửa AL,AL1 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE' LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME LEFT JOIN TL_USER TL6 ON A.REF_ID = TL6.TLNANME WHERE 1=1 AND(A.REQ_PAY_ID=@p_REQ_PAY_ID) ORDER BY A.CREATE_DT DESC; ¿ ALTER PROCEDURE [dbo].[rpt_TR_ADVANCE_TEMP_PAYMENT_ByID] @p_REQ_PAY_ID varchar(15)= NULL AS ------- DECLARE @TITLE_APPROVE NVARCHAR(50) ='', @TITLE_APPROVE_KSV NVARCHAR(50) ='' IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT='S')) BEGIN SET @TITLE_APPROVE =N'Đã từ chối ' END ELSE BEGIN SET @TITLE_APPROVE =N'Đã chấp thuận ' END ---------------- IF(EXISTS(SELECT * FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND AUTH_STATUS_KT='R')) BEGIN SET @TITLE_APPROVE_KSV =N'Đã từ chối ' END ELSE BEGIN SET @TITLE_APPROVE_KSV =N'Đã chấp thuận ' END ---------------- DECLARE @TRUONG_DV NVARCHAR(50),@DVKD_APP_DT NVARCHAR(50), @SO_DU_TAM_UNG DECIMAL(18,2) DECLARE @DATE_APPR DATETIME SET @DATE_APPR =ISNULL((SELECT TOP 1 CONVERT(DATETIME,APPROVE_DT,103) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID),CONVERT(DATETIME,GETDATE(),103)) DECLARE @REQ_TYPE VARCHAR(15) SET @REQ_TYPE =(SELECT TOP 1 REQ_TYPE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID) ------------------------------------------- DECLARE @CONTENT NVARCHAR(MAX)='', @CONTENT_CK NVARCHAR(MAX)='', @REQ_PAY_TYPE VARCHAR(5), @ACCNO VARCHAR(50), @ACCNAME NVARCHAR(500), @BANK NVARCHAR(500), @ISUED_DT DATE,@REASON NVARCHAR(MAX), @DESC NVARCHAR(MAX),@NDTM NVARCHAR(MAX) ='', @NDCK NVARCHAR(MAX) ='' DECLARE CURS CURSOR FOR SELECT A.REQ_PAY_TYPE, A.ACC_NO,A.ACC_NAME,A.ISSUED_BY,A.ISSUED_DT,A.REQ_PAY_REASON,A.REQ_PAY_ENTRIES FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID DECLARE @NOIDUNG NVARCHAR(MAX) ='' --- LAY NOI DUNG THANH TOAN NEU THANH TOAN CHO NHA CUNG CAP DECLARE CURS_PO CURSOR FOR SELECT A.REQ_PAY_DESC,A.REQ_PAY_ENTRIES FROM TR_REQ_PAY_SCHEDULE A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID DECLARE @NOIDUNG_PO NVARCHAR(MAX) ='' DECLARE @BRANCH_TYPE VARCHAR(15), @BRANCH_RQ VARCHAR(15),@TOTAL_ADVANCE DECIMAL(18,0), @TOTAL_PAYBACK DECIMAL(18,0) SET @BRANCH_RQ =(SELECT TOP 1 BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) SET @BRANCH_TYPE = (SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ) IF(@BRANCH_TYPE ='HS' AND @REQ_TYPE ='I') BEGIN SET @TRUONG_DV = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = (SELECT top 1 CHECKER_ID FROM PL_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND PROCESS_ID='0' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='0'))) SET @DVKD_APP_DT =(SELECT TOP 1 ISNULL(FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM PL_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID AND PROCESS_ID='0' AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='0')) IF(@DVKD_APP_DT IS NOT NULL AND @DVKD_APP_DT <>'') BEGIN SET @DVKD_APP_DT =N'Đã chấp thuận '+ @DVKD_APP_DT END END ELSE IF (@BRANCH_TYPE = 'PGD' AND @REQ_TYPE ='I') BEGIN SET @TRUONG_DV =(SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = (SELECT TOP 1 CHECKER_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) --AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='0'))) SET @DVKD_APP_DT =(SELECT TOP 1 ISNULL(FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) --AND ID =(SELECT MAX(ID) FROM PL_PROCESS WHERE REQ_ID= @p_REQ_PAY_ID AND PROCESS_ID ='0')) IF(@DVKD_APP_DT IS NOT NULL AND @DVKD_APP_DT <>'') BEGIN SET @DVKD_APP_DT =N'Đã chấp thuận '+ @DVKD_APP_DT END END ELSE BEGIN SET @TRUONG_DV = (SELECT TOP 1 TLFullName FROM TL_USER WHERE TLNANME = (SELECT top 1 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 @DVKD_APP_DT =(SELECT TOP 1 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') SET @DVKD_APP_DT = (SELECT TOP 1 ISNULL(FORMAT(APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID ) IF(@DVKD_APP_DT IS NOT NULL AND @DVKD_APP_DT <>'') BEGIN SET @DVKD_APP_DT =N'Đã chấp thuận '+ @DVKD_APP_DT END END DECLARE @TMP_DVDM TABLE(DVDM_ID VARCHAR(15)) INSERT INTO @TMP_DVDM SELECT A.DVDM_ID--, A.COST_CODE, A.COST_NAME, B.DEP_ID --, C.DEP_CODE, C.DEP_NAME FROM PL_COSTCENTER A LEFT JOIN PL_COSTCENTER_DT B ON A.COST_ID = B.COST_ID WHERE B.DEP_ID = (SELECT TOP 1 DEP_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID) GROUP BY A.DVDM_ID DECLARE @tmp_CN table(BRANCH_ID varchar(15)) IF((SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='CN') BEGIN INSERT into @tmp_CN SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_RQ) END ELSE IF((SELECT TOP 1 BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ)='PGD') BEGIN DECLARE @FATHER_ID VARCHAR(15) = NULL SET @FATHER_ID =(SELECT TOP 1 FATHER_ID FROM CM_BRANCH WHERE BRANCH_ID =@BRANCH_RQ) INSERT into @tmp_CN SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@FATHER_ID) END SET @SO_DU_TAM_UNG = (SELECT AMT FROM TR_REQ_ADVANCE_LIMIT_LOG WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND ID =(SELECT MAX(ID) FROM TR_REQ_ADVANCE_LIMIT_LOG WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) --IF(@BRANCH_TYPE='HS' AND @REQ_TYPE ='I') -- BEGIN -- SET @TOTAL_ADVANCE =ISNULL((SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE -- DEP_ID IN -- (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A -- LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID -- WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ) -- AND REQ_TYPE ='I' AND AUTH_STATUS ='A' AND (CONVERT(DATETIME,APPROVE_DT,103) <=@DATE_APPR OR APPROVE_DT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID),0) -- SET @TOTAL_PAYBACK = -- ISNULL( -- ( -- SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT)) -- FROM TR_REQ_PAYMENT_DT B -- INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID -- INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID -- WHERE A.DEP_ID IN -- (SELECT A.DEP_ID FROM PL_COSTCENTER_DT A -- LEFT JOIN PL_COSTCENTER B ON A.COST_ID = B.COST_ID -- WHERE B.DVDM_ID IN (SELECT DVDM_ID FROM @TMP_DVDM) AND A.BRANCH_ID =@BRANCH_RQ) -- AND A.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I' AND (CONVERT(DATETIME,A.APPROVE_DT_KT,103) <=@DATE_APPR OR A.APPROVE_DT_KT IS NULL) AND B.PAY_ADV_ID <>@p_REQ_PAY_ID -- ),0) -- END -- -- NEU PHIEU DUOC TAO KHAC VOI HOI SO THI TIEN HANH TINH TONG SO TIEN TAM UNG CUA CN VA PHONG GIAO DICH TRUC THUOC -- ELSE IF(@BRANCH_TYPE <>'HS') -- BEGIN -- SET @TOTAL_ADVANCE =(SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE BRANCH_ID IN (SELECT BRANCH_ID FROM @tmp_CN) -- AND REQ_TYPE ='I' AND AUTH_STATUS='A' AND (CONVERT(DATETIME,APPROVE_DT,103) <=@DATE_APPR OR APPROVE_DT IS NULL) AND REQ_PAY_ID <>@p_REQ_PAY_ID) -- SET @TOTAL_PAYBACK = -- ( -- SELECT (SUM(B.AMT_USE) - SUM(B.AMT_ADD) + SUM (B.AMT_REVERT)) -- FROM TR_REQ_PAYMENT_DT B -- INNER JOIN TR_REQ_PAYMENT A ON B.PAY_ID = A.REQ_PAY_ID -- INNER JOIN TR_REQ_ADVANCE_PAYMENT C ON C.REQ_PAY_ID = B.PAY_ADV_ID -- WHERE A.BRANCH_ID IN -- (SELECT BRANCH_ID FROM @tmp_CN) -- AND A.AUTH_STATUS_KT ='A' AND C.REQ_TYPE='I' AND (CONVERT(DATETIME,A.APPROVE_DT_KT,103) <=@DATE_APPR OR A.APPROVE_DT_KT IS NULL) AND B.PAY_ADV_ID <>@p_REQ_PAY_ID -- ) -- END --- SELECT A.REQ_PAY_CODE, A.REQ_REASON,A.DEP_ID, N'Đã chấp thuận '+ISNULL(FORMAT(A.CREATE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') AS CREATE_DT, A.MAKER_ID_KT,A.CHECKER_ID, CASE WHEN ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') <>'' THEN N'Đã chấp thuận '+ ISNULL(FORMAT(A.APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS APPROVE_DT,A.CHECKER_ID_KT,MAKER_ID_KT, CASE WHEN ISNULL(FORMAT(A.APPROVE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') <>'' THEN @TITLE_APPROVE_KSV + ISNULL(FORMAT(A.APPROVE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS APPROVE_DT_KT , CASE WHEN ISNULL(FORMAT(A.CREATE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') <>'' THEN @TITLE_APPROVE +ISNULL(FORMAT(A.CREATE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') ELSE '' END AS CREATE_DT_KT, B.AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, CASE WHEN A.REF_ID IS NOT NULL AND A.REF_ID <>'' THEN TL6.TLFullName ELSE TL.TLFullName END CREATE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT, BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ, --Luanlt--2019/10/15-Sửa AL,AL1 BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, 0 TOTAL_AMT_TEMP, 0 TOTAL_AMT_PAY_HIS,TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME, DAY(A.CREATE_DT) D_, MONTH(A.CREATE_DT) M_, YEAR(A.CREATE_DT) Y_, CASE WHEN A.REQ_TYPE='I' THEN CAST(1 AS BIT) WHEN A.REQ_TYPE <>'I' THEN CAST(0 AS BIT) END AS IS_NB, CASE WHEN A.REQ_TYPE='P' THEN CAST(1 AS BIT) WHEN A.REQ_TYPE <>'P' THEN CAST(0 AS BIT) END AS IS_NCC, CASE WHEN A.REQ_TYPE='D' THEN CAST(1 AS BIT) WHEN A.REQ_TYPE <>'D' THEN CAST(0 AS BIT) END AS IS_XLN, CASE WHEN LEN(@CONTENT) >0 THEN LEFT(@CONTENT,LEN(@CONTENT)-2) ELSE '' END AS TTTM, CASE WHEN LEN(@CONTENT_CK) >0 THEN LEFT(@CONTENT_CK, LEN(@CONTENT_CK)-2) ELSE NULL END AS TTCK, CASE WHEN LEN(@CONTENT) >0 THEN N'Tiền mặt:' ELSE '' END AS TM_TITLE, CASE WHEN LEN(@CONTENT_CK) >0 THEN N'Chuyển khoản:' ELSE NULL END AS CK_TITLE, --CASE WHEN @REQ_TYPE <>'P' THEN LEFT(@NOIDUNG, LEN(@NOIDUNG)-2) ELSE LEFT(@NOIDUNG_PO,LEN(@NOIDUNG_PO)-2) END AS NOIDUNG, TL1.TLFullName AS APPROVE_FULLNAME, --ISNULL(A.REQ_AMT,0) AS SO_TIEN_PTTT, CASE WHEN LEN(@NDTM + @NDCK) >0 THEN LEFT(@NDTM + @NDCK,LEN(@NDTM + @NDCK)-2) ELSE '' END AS PHUONG_THUC_THANH_TOAN, CASE WHEN EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND CURRENCY <>'VND') THEN (SELECT TOP 1 CURRENCY FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) ELSE 'VND' END AS TYPE_CUR, CASE WHEN EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND CURRENCY <>'VND') THEN (SELECT SUM(TOTAL_AMT) FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) ELSE A.REQ_AMT END AS SO_TIEN_PTTT, --(ISNULL(@TOTAL_ADVANCE,0) -ISNULL(@TOTAL_PAYBACK,0))+A.REQ_AMT*A.RATE AS SO_DU_TAM_UNG, ISNULL(@SO_DU_TAM_UNG,0.00) AS SO_DU_TAM_UNG, CASE WHEN EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND CURRENCY <>'VND') THEN (SELECT SUM(TOTAL_AMT) FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID) ELSE A.REQ_AMT END AS REQ_AMT, @TRUONG_DV AS TRUONG_DV, @DVKD_APP_DT AS DVKD_APP_DT,CASE WHEN A.CONFIRM_NOTES <>'' AND A.CONFIRM_NOTES IS NOT NULL THEN N'Ghi chú: '+ A.CONFIRM_NOTES ELSE '' END AS CONFIRM_NOTES FROM TR_REQ_ADVANCE_PAYMENT A LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID --Luanlt--2019/10/15-Sửa AL,AL1 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE' LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME LEFT JOIN TL_USER TL6 ON A.REF_ID = TL6.TLNANME WHERE 1=1 AND(A.REQ_PAY_ID=@p_REQ_PAY_ID) ORDER BY A.CREATE_DT DESC; ¿ ALTER PROCEDURE [dbo].[TR_REQ_ADVANCE_PAYMENT_Search] @p_REQ_PAY_ID varchar(15)= NULL, @p_REQ_PAY_CODE varchar(50) = NULL, @p_REQ_DT VARCHAR(20)= NULL, @p_BRANCH_ID varchar(15) = NULL, @p_DEP_ID varchar(15) = NULL, @p_REQ_REASON nvarchar(MAX) = NULL, @p_REQ_TYPE varchar(15) = NULL, @P_REQ_ENTRIES nvarchar(MAX) = NULL, @p_REQ_DESCRIPTION nvarchar(MAX) = NULL, @p_REF_ID varchar(15) = NULL, @p_RECEIVER_PO nvarchar(250) = NULL, @p_RECEIVER_DEBIT nvarchar(250) = NULL, @p_REQ_PAY_TYPE varchar(15) = NULL, @p_REQ_TYPE_CURRENCY nvarchar(50) = NULL, @p_REQ_AMT decimal(18, 0) = NULL, @p_REQ_TEMP_AMT decimal(18, 0) = NULL, @p_MAKER_ID varchar(15) = NULL, @p_CREATE_DT varchar(25) = NULL, @p_EDITOR_ID varchar(15) = NULL, @p_AUTH_STATUS varchar(1) = NULL, @p_CHECKER_ID varchar(15) = NULL, @p_APPROVE_DT varchar(25) = NULL, @p_CREATE_DT_KT varchar(25) = NULL, @p_MAKER_ID_KT varchar(15) = NULL, @p_AUTH_STATUS_KT varchar(1) = NULL, @p_CHECKER_ID_KT varchar(1) = NULL, @p_APPROVE_DT_KT varchar(25)= null, @p_CORE_NOTE nvarchar(500) = NULL, @p_BRANCH_CREATE varchar(15) = NULL, @p_NOTES varchar(15) = NULL, @p_RECORD_STATUS varchar(1) = NULL, @p_TRANSFER_MAKER nvarchar(50) = NULL, @p_TRANSFER_DT varchar(25) = NULL, @p_TRASFER_USER_RECIVE varchar(15) = NULL, @p_PROCESS varchar(15) = NULL, @p_PAY_PHASE VARCHAR(15)= NULL, @p_XMP_TEMP XML = NULL, @p_TOP INT = 10, @p_LEVEL varchar(10) = NULL, @p_FRMDATE VARCHAR(20)= NULL, @p_TODATE VARCHAR(20) = NULL, @p_BRANCH_LOGIN VARCHAR(15) = NULL, @p_IS_UPDATE_KT VARCHAR(15) = NULL, @p_IS_TRANSFER VARCHAR(15)= NULL, @p_DVDM_ID VARCHAR(15)= NULL, @p_USER_LOGIN VARCHAR(15) = NULL, @p_RATE DECIMAL(18,0) =0, @p_FUNCTION VARCHAR(15) = NULL, @p_TYPE_SEARCH VARCHAR(15) = NULL, @p_TEMP_PAY_STATUS VARCHAR(10) = NULL --select * from CM_ALLCODE where CDTYPE = 'TEMPPAYSTATUS' --HT Hoàn tất --DCHU Đang chờ hoàn ứng --DHU Đã hoàn ứng --'' tất cả AS SET @p_TOP = NULL DECLARE @TB_TYPE TABLE (BRANCH_TYPE VARCHAR(15)) IF((SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID= @p_BRANCH_LOGIN)='HS') BEGIN INSERT INTO @TB_TYPE VALUES('HS') END ELSE BEGIN INSERT INTO @TB_TYPE VALUES('PGD') INSERT INTO @TB_TYPE VALUES('CN') END declare @tmp table(BRANCH_ID varchar(15)) insert into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_ID) declare @tmp_Login table(BRANCH_ID varchar(15)) insert into @tmp_Login SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN) DECLARE @ROLE_ID VARCHAR(20) , @DEP_ID_LG VARCHAR(15) = NULL, @COST_LG VARCHAR(15), @DVDM_ID VARCHAR(15) DECLARE @BRANCH_TYPE VARCHAR(15) SET @BRANCH_TYPE =(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=(SELECT BRANCH_ID FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) SET @ROLE_ID = (SELECT ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN) SET @DEP_ID_LG =(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME=@p_USER_LOGIN) DECLARE @TMP_DEP TABLE(DEP_ID VARCHAR(15)) SET @COST_LG =(SELECT TOP 1 COST_ID FROM PL_COSTCENTER_DT WHERE DEP_ID =@DEP_ID_LG) SET @DVDM_ID =(SELECT TOP 1 DVDM_ID FROM PL_COSTCENTER WHERE COST_ID =@COST_LG) print @DVDM_ID INSERT INTO @TMP_DEP SELECT B.DEP_ID FROM PL_COSTCENTER_DT B WHERE COST_ID =@COST_LG DECLARE @BRANCH_TYPE_LG VARCHAR(15) SET @BRANCH_TYPE_LG = (SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID=@p_BRANCH_LOGIN) --IF(EXISTS(SELECT * FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID)) -- SET @ROLE_ID=(SELECT ROLE_NEW FROM dbo.TL_SYS_ROLE_MAPPING WHERE ROLE_OLD=@ROLE_ID) SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN) IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD')) BEGIN PRINT @ROLE_ID END ELSE BEGIN --SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_USER_LOGIN) SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_USER_LOGIN) IF(@ROLE_ID IS NULL OR @ROLE_ID ='') BEGIN SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_USER_LOGIN)) END END -- KHAI BAO THEM ROLE NHAN UY QUYEN DECLARE @TABLE_ROLE TABLE (ROLE_AUTH VARCHAR(50)) INSERT INTO @TABLE_ROLE SELECT ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =@ROLE_ID --AND CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) -- KHAI BAO NHUNG PHONG BAN MA 1 USER KIEM NHIEM DECLARE @DEP_AUTH TABLE (DEP_AUTH VARCHAR(15)) --INSERT INTO @DEP_AUTH SELECT DEP_ID FROM TL_SYS_ROLE_MAPPING WHERE TLNAME =@P_USER_LOGIN AND CONVERT(DATE,GETDATE(),103) >= CONVERT(DATE,EFF_DATE, 103) AND CONVERT(DATE, GETDATE(),103) <=CONVERT(DATE,EXP_DATE, 103) IF(@p_TOP=NULL OR @p_TOP='' OR @p_TOP=0)BEGIN SELECT A.*,'' as TEMP_PAY_STATUS , PR.PROCESS_DESC AS AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME, TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT, BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME, DP.DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ, --Luanlt--2019/10/15-Sửa AL,AL1 BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, A.REQ_AMT AS TOTAL_AMT_TEMP, --CASE WHEN H.SOTIEN_TT IS NULL THEN ISNULL(A.PAY_AMT,0) ELSE ISNULL(H.SOTIEN_TT,0) END AS TOTAL_AMT_PAY_HIS, ISNULL(A.PAY_AMT,0) AS TOTAL_AMT_PAY_HIS, TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME,TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE, TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE, PO.PO_CODE, PO.PO_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS CUST_NAME, PM.PAY_PHASE AS KY_TAM_UNG, DV.DVDM_CODE, DV.DVDM_NAME, PC.ROLE_USER AS ROLE_CONF, PR.PROCESS_DESC AS CONF_STATUS, PC1.NOTES AS NEXT_STEP, PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB, PC3.TLNAME AS EXEC_USER, TL8.TLFullName AS RECIVER_MONEY_FULLNAME, BR1.BRANCH_TYPE AS BRANCH_TYPE_CR, BR.BRANCH_TYPE, @p_TYPE_SEARCH AS TYPE_SEARCH,SR.ROLE_ID AS ROLE_ID_CRE, BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE FROM TR_REQ_ADVANCE_PAYMENT A LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DP ON DP.DEP_ID=A.DEP_ID --Luanlt--2019/10/15-Sửa AL,AL1 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE'--Loại thanh toán (Nội bộ/Thanh toán/Nợ) LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE'--Hình thức thanh toán( Tiền mặt/Chuyển khoản) LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME LEFT JOIN TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D' LEFT JOIN ( SELECT PAY_ADV_ID,(ISNULL(SUM(AMT_USE),0) - ISNULL(SUM(AMT_ADD),0) + ISNULL(SUM(AMT_REVERT),0)) AS SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID ) H ON A.REQ_PAY_ID = H.PAY_ADV_ID LEFT JOIN TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I' LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P' LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P' LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D' LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P' LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER = @ROLE_ID AND PC.STATUS='C' LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C' LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C' LEFT JOIN TL_USER TL8 ON A.RECIVER_MONEY = TL8.TLNANME LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID WHERE 1=1 AND (A.REQ_PAY_ID =@p_REQ_PAY_ID OR @p_REQ_PAY_ID IS NULL OR @p_REQ_PAY_ID ='') AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' OR @p_REQ_REASON='' OR @p_REQ_REASON IS NULL) AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL) AND((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE_KT='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat OR((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE_KT='N'))OR @p_IS_UPDATE_KT IS NULL OR @p_IS_UPDATE_KT='') AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL) --Luanlt-2019/10/14-Thêm Filter REQ_TYPE,FROMDATE TODATE REF_ID AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL) AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='') AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='') --Luanlt-2019/10/17-Thêm Filter REQ_PAY_CODE REQ_PAY_NAME BRANCH_ID DEP_ID KT_AUTH AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL) AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'') AND(A.BRANCH_CREATE = @p_BRANCH_CREATE or @p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL) AND(A.DEP_ID = @p_DEP_ID or @p_DEP_ID='' OR @p_DEP_ID IS NULL) AND(A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT OR @p_AUTH_STATUS_KT='' OR @p_AUTH_STATUS_KT IS NULL) AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL) AND((A.TRANSFER_MAKER IS NOT NULL AND @p_IS_TRANSFER='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat OR((A.TRANSFER_MAKER IS NULL AND @p_IS_TRANSFER='N'))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='') AND((@p_LEVEL='ALL' AND A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp)) OR((@p_LEVEL='UNIT' AND A.BRANCH_ID=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL))) AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp_Login)) OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL))) --- KIEM TRA NEU LA MAN HINH TIM KIEM PHIEU TAM UNG DE HOAN TAM UNG THI CHI LAY NHUNG PHIEU TAM UNG CO SO TIEN TAM UNG CON LAI =0 AND(((@p_PROCESS='PM' AND (A.REQ_AMT- ISNULL(H.SOTIEN_TT,0)) >0) AND A.REQ_TYPE='I') OR @p_PROCESS IS NULL OR @p_PROCESS ='') AND ((@p_FUNCTION ='KT' AND A.MAKER_ID_KT IS NOT NULL AND A.MAKER_ID_KT ='') OR @p_FUNCTION IS NULL OR @p_FUNCTION ='') AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='') AND ( (A.REQ_TYPE IN ('I','P','D') AND (@p_TYPE_SEARCH='HC' AND EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @p_BRANCH_LOGIN AND BRANCH_TYPE ='HS') AND ( (A.PROCESS IS NOT NULL AND A.PROCESS <> '' AND (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =A.MAKER_ID) = (SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)) OR(A.MAKER_ID =@P_USER_LOGIN OR A.TRASFER_USER_RECIVE =@p_USER_LOGIN)) OR (@p_TYPE_SEARCH='HC' AND NOT EXISTS(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN AND BRANCH_TYPE ='HS') AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)) OR (@p_TYPE_SEARCH ='HC' AND (A.DVDM_ID IS NOT NULL OR A.DVDM_ID <>'' AND A.DVDM_ID =@DVDM_ID AND A.PROCESS IS NOT NULL AND A.PROCESS <>'') OR (A.DEP_ID IN (SELECT * FROM @TMP_DEP) AND A.PROCESS IS NOT NULL AND A.PROCESS <>'')) OR (@p_TYPE_SEARCH ='HC' AND EXISTS(SELECT * FROM PL_REQUEST_PROCESS WHERE REQ_ID= A.REQ_PAY_ID AND ROLE_USER ='TGD' AND STATUS ='C')) -- HOAC NGUOC LAI NEU HC NHUNG PHIEU DUOC TAO O PGD, CN OR (@p_TYPE_SEARCH='KT' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login))) ) OR @p_TYPE_SEARCH IS NULL OR @p_TYPE_SEARCH ='') --AND ( --(A.PAY_AMT = A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'HT')) --OR (A.PAY_AMT = 0 AND (@p_TEMP_PAY_STATUS = 'DCHU') ) --OR (A.PAY_AMT <> 0 and A.PAY_AMT < A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'DHU')) --OR @p_TEMP_PAY_STATUS IS NULL OR @p_TEMP_PAY_STATUS = '' --) ORDER BY A.CREATE_DT DESC; END; ELSE BEGIN SELECT A.*,'' as TEMP_PAY_STATUS ,ISNULL(B.AUTH_STATUS_NAME,N'Chờ duyệt') AS AUTH_STATUS_NAME, C.AUTH_STATUS_NAME AUTH_STATUS_NAME_KT, TL.TLFullName CREATE_FULLNAME, TL1.TLFullName APPROVE_FULLNAME,TL2.TLFullName CREATE_FULLNAME_KT,TL3.TLFullName APPROVE_FULLNAME_KT, BR.BRANCH_NAME BRANCH_NAME_REQ, BR1.BRANCH_NAME BRANCH_NAME_CRE, DP.DEP_NAME AS DEP_NAME, DP.DEP_CODE AS DEP_CODE, BR.BRANCH_CODE BRANCH_CODE_REQ, --Luanlt--2019/10/15-Sửa AL,AL1 BR1.BRANCH_CODE BRANCH_CODE_CRE, AL.CONTENT REQ_TYPE_NAME,AL1.CONTENT REQ_PAY_TYPE_NAME, A.REQ_AMT AS TOTAL_AMT_TEMP,CASE WHEN H.SOTIEN_TT IS NULL THEN ISNULL(A.PAY_AMT,0) ELSE ISNULL(A.PAY_AMT,0) END AS TOTAL_AMT_PAY_HIS, TL4.TLFullName AS TRANSFER_FULLNAME,TL5.TLFullName AS TRANSFER_RECIEVER_FULLNAME, TL6.TLFullName AS REC_DEBIT_NAME, TL6.TLNANME AS REC_DEBIT_CODE, TL7.TLFullName AS EMP_NAME, TL7.TLNANME AS EMP_CODE, PO.PO_CODE, PO.PO_NAME,SUP.SUP_NAME,SUP.TAX_NO AS SUP_TAX_NO,CUS.CUSTOMER_CODE AS CUST_CODE, CUS.CUSTOMER_NAME AS CUST_NAME,PM.PAY_PHASE AS KY_TAM_UNG, DV.DVDM_CODE, DV.DVDM_NAME, CASE WHEN PC.ROLE_USER ='GDDV' THEN (SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN) ELSE PC.ROLE_USER END AS ROLE_CONF, PR.PROCESS_DESC +N' vào lúc '+ CONVERT(VARCHAR(30), CONVERT(DATETIME, PR.APP_DT,103)) AS CONF_STATUS, PC1.NOTES AS NEXT_STEP, PC3.LEVEL_JOB, PC3.STATUS_JOB,PC3.TYPE_JOB,PC3.TLNAME AS EXEC_USER,TL8.TLFullName AS RECIVER_MONEY_FULLNAME, CASE WHEN BR1.BRANCH_TYPE <> 'PGD'THEN BR1.BRANCH_TYPE ELSE 'CN' END AS BRANCH_TYPE_CR, BR.BRANCH_TYPE,@p_TYPE_SEARCH AS TYPE_SEARCH, SR.ROLE_ID AS ROLE_ID_CRE, ISNULL(H.SOTIEN_TT,0) AS STTT, A.REQ_AMT - ISNULL(H.SOTIEN_TT,0) AS SL_CL,BR_CORE.BRANCH_CODE AS BRANCH_CODE_CORE, BR_CORE.BRANCH_NAME AS BRANCH_NAME_CORE FROM TR_REQ_ADVANCE_PAYMENT A LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS=B.AUTH_STATUS LEFT JOIN CM_AUTH_STATUS C ON A.AUTH_STATUS_KT=C.AUTH_STATUS LEFT JOIN TL_USER TL ON A.MAKER_ID=TL.TLNANME LEFT JOIN TL_USER TL1 ON A.CHECKER_ID=TL1.TLNANME LEFT JOIN TL_USER TL2 ON A.MAKER_ID_KT=TL2.TLNANME LEFT JOIN TL_USER TL3 ON A.CHECKER_ID_KT=TL3.TLNANME LEFT JOIN dbo.CM_BRANCH BR ON A.BRANCH_ID=BR.BRANCH_ID LEFT JOIN dbo.CM_BRANCH BR1 ON A.BRANCH_CREATE=BR1.BRANCH_ID LEFT JOIN dbo.CM_DEPARTMENT DP ON A.DEP_ID= DP.DEP_ID --Luanlt--2019/10/15-Sửa AL,AL1 LEFT JOIN CM_ALLCODE AL ON A.REQ_TYPE = AL.CDVAL AND AL.CDNAME='REQ_TYPE' LEFT JOIN CM_ALLCODE AL1 ON A.REQ_PAY_TYPE = AL1.CDVAL AND AL1.CDNAME='REQ_PAY_TYPE' LEFT JOIN TL_USER TL4 ON A.TRANSFER_MAKER = TL4.TLNANME LEFT JOIN TL_USER TL5 ON A.TRASFER_USER_RECIVE = TL5.TLNANME LEFT JOIN TL_USER TL6 ON A.RECEIVER_DEBIT = TL6.TLNANME AND A.REQ_TYPE='D' LEFT JOIN ( SELECT PAY_ADV_ID,(ISNULL(SUM(AMT_USE),0) - ISNULL(SUM(AMT_ADD),0) + ISNULL(SUM(AMT_REVERT),0)) AS SOTIEN_TT FROM TR_REQ_PAYMENT_DT WHERE AUTH_STATUS_KT ='A' GROUP BY PAY_ADV_ID ) H ON A.REQ_PAY_ID = H.PAY_ADV_ID LEFT JOIN TL_USER TL7 ON TL7.TLNANME = A.REF_ID AND A.REQ_TYPE='I' LEFT JOIN TR_PO_MASTER PO ON A.REF_ID = PO.PO_ID AND A.REQ_TYPE='P' LEFT JOIN CM_SUPPLIER SUP ON PO.SUP_ID = SUP.SUP_ID AND A.REQ_TYPE='P' LEFT JOIN LS_CUSTOMER CUS ON A.REF_ID = CUS.CUSTOMER_ID AND A.REQ_TYPE='D' LEFT JOIN TR_PO_PAYMENT PM ON A.PAY_PHASE = PM.PAY_ID AND A.REQ_TYPE='P' LEFT JOIN CM_DVDM DV ON A.DVDM_ID = DV.DVDM_ID --LEFT JOIN PL_PROCESS PR ON A.PROCESS = PR.PROCESS_ID AND A.REQ_PAY_ID = PR.REQ_ID LEFT JOIN ( SELECT P.REQ_ID, P.PROCESS_DESC, P.APPROVE_DT AS APP_DT FROM PL_PROCESS P INNER JOIN ( SELECT MAX (PR.ID) AS ID, MAX(PR.REQ_ID) AS REQ_ID FROM PL_PROCESS PR GROUP BY PR.REQ_ID ) PR ON PR.REQ_ID = P.REQ_ID AND P.ID = PR.ID ) AS PR ON A.REQ_PAY_ID = PR.REQ_ID --LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER = @ROLE_ID AND PC.STATUS='C' LEFT JOIN PL_REQUEST_PROCESS PC ON PC.REQ_ID= A.REQ_PAY_ID AND PC.ROLE_USER IN (SELECT * FROM @TABLE_ROLE) AND PC.STATUS='C' LEFT JOIN PL_REQUEST_PROCESS PC1 ON PC1.REQ_ID= A.REQ_PAY_ID AND PC1.STATUS='C' LEFT JOIN PL_REQUEST_PROCESS_CHILD PC3 ON PC3.REQ_ID = A.REQ_PAY_ID AND PC3.STATUS_JOB ='C' LEFT JOIN PL_REQUEST_PROCESS_CHILD PC4 ON PC4.REQ_ID = A.REQ_PAY_ID AND PC4.STATUS_JOB ='C' LEFT JOIN TL_USER TL8 ON A.RECIVER_MONEY = TL8.TLNANME LEFT JOIN TL_SYSROLE SR ON TL.RoleName = SR.ROLE_ID LEFT JOIN CM_BRANCH BR_CORE ON TL2.TLSUBBRID = BR_CORE.BRANCH_ID WHERE 1=1 AND (( @p_REQ_PAY_CODE IS NOT NULL AND @p_REQ_PAY_CODE <> '' AND A.REQ_PAY_CODE =@p_REQ_PAY_CODE AND A.AUTH_STATUS_KT ='A' AND (A.REQ_AMT - ISNULL(A.PAY_AMT,0))>0) OR ( (A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' OR @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL) AND(A.REQ_REASON LIKE N'%'+@p_REQ_REASON+'%' OR @p_REQ_REASON='' OR @p_REQ_REASON IS NULL) AND(A.AUTH_STATUS=@p_AUTH_STATUS OR @p_AUTH_STATUS='' OR @p_AUTH_STATUS IS NULL) AND((A.MAKER_ID_KT IS NOT NULL AND @p_IS_UPDATE_KT='Y') --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat OR((A.MAKER_ID_KT IS NULL AND @p_IS_UPDATE_KT='N'))OR @p_IS_UPDATE_KT IS NULL OR @p_IS_UPDATE_KT='') AND(A.NOTES LIKE N'%'+@p_NOTES+'%' OR @p_NOTES='' OR @p_NOTES IS NULL) --Luanlt-2019/10/14-Thêm Filter REQ_TYPE,FROMDATE TODATE REF_ID AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' OR @p_REQ_TYPE = '' OR @p_REQ_TYPE IS NULL) AND(CONVERT(DATE, A.CREATE_DT)>=CONVERT(DATE, @p_FRMDATE, 103)OR @p_FRMDATE IS NULL OR @p_FRMDATE='' OR A.CREATE_DT IS NULL OR A.REQ_DT IS NULL) AND(CONVERT(DATE, A.CREATE_DT)<=CONVERT(DATE, @p_TODATE, 103)OR @p_TODATE IS NULL OR @p_TODATE='' OR A.CREATE_DT IS NULL OR A.REQ_DT IS NULL) --Luanlt-2019/10/17-Thêm Filter REQ_PAY_CODE REQ_PAY_NAME BRANCH_ID DEP_ID KT_AUTH AND(A.REQ_PAY_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL) --AND(A.TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE or @p_TRASFER_USER_RECIVE='' OR @p_TRASFER_USER_RECIVE IS NULL OR A.MAKER_ID_KT IS NOT NULL OR A.MAKER_ID_KT <>'') AND(A.DEP_ID = @p_DEP_ID or @p_DEP_ID='' OR @p_DEP_ID IS NULL) AND((A.AUTH_STATUS_KT=@p_AUTH_STATUS_KT OR @p_AUTH_STATUS_KT='' OR @p_AUTH_STATUS_KT IS NULL) OR (@p_AUTH_STATUS_KT ='P' AND A.AUTH_STATUS_KT IN ('P','S'))) AND(A.REF_ID =@p_REF_ID OR @p_REF_ID='' OR @p_REF_ID IS NULL) AND(( @p_IS_TRANSFER='Y' AND (EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID) OR A.AUTH_STATUS_KT ='A')) --Lay danh sach chua duoc phong KT cap nhat va duoc cap nhat OR((@p_IS_TRANSFER='N' AND NOT EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID) AND A.AUTH_STATUS_KT <>'A'))OR @p_IS_TRANSFER IS NULL OR @p_IS_TRANSFER='') AND(((@p_PROCESS='PM' AND (A.REQ_AMT- ISNULL(A.PAY_AMT,0.00)) >0) AND (A.REQ_TYPE IN('I','D') OR (@DEP_ID_LG ='DEP000000000014' AND A.REQ_TYPE IN ('I','P','D'))) AND ((A.DEP_ID =@DEP_ID_LG OR A.MAKER_ID =@p_USER_LOGIN OR A.MAKER_ID IS NULL) OR(@BRANCH_TYPE_LG <>'HS' AND A.BRANCH_ID =@p_BRANCH_LOGIN))) OR @p_PROCESS IS NULL OR @p_PROCESS ='') AND((@p_LEVEL='ALL' AND A.BRANCH_ID IN(SELECT BRANCH_ID FROM @tmp)) OR((@p_LEVEL='UNIT' AND A.BRANCH_ID=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL))) --AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp_Login)) -- OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_CREATE)OR(@p_BRANCH_CREATE='' OR @p_BRANCH_CREATE IS NULL))) --AND ((@p_TRASFER_USER_RECIVE IS NOT NULL AND EXISTS (SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE TLNAME = @p_TRASFER_USER_RECIVE )) --OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='') AND ((@p_FUNCTION ='KT' AND (EXISTS(SELECT TOP 1 * FROM PL_REQUEST_PROCESS_CHILD WHERE REQ_ID = A.REQ_PAY_ID AND TLNAME= @p_USER_LOGIN)) OR A.CHECKER_ID_KT ='admin' OR A.AUTH_STATUS_KT ='A') OR @p_FUNCTION IS NULL OR @p_FUNCTION ='' OR @p_FUNCTION ='TF') AND(A.MAKER_ID LIKE '%'+@p_MAKER_ID+'%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID ='') --AND(A.MAKER_ID_KT LIKE '%'+@p_TRASFER_USER_RECIVE+'%' OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='') --AND(PC4.TLNAME = @p_TRASFER_USER_RECIVE OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE ='') AND(EXISTS(SELECT * FROM PL_REQUEST_PROCESS_CHILD WHERE TLNAME =@p_TRASFER_USER_RECIVE AND TYPE_JOB='XL' AND REQ_ID = A.REQ_PAY_ID) OR @p_TRASFER_USER_RECIVE IS NULL OR @p_TRASFER_USER_RECIVE='') AND ( A.MAKER_ID =@p_USER_LOGIN OR (A.TRASFER_USER_RECIVE = @p_USER_LOGIN AND A.AUTH_STATUS NOT IN ('E','R')) OR( --A.AUTH_STATUS <>'E' AND((@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS' AND A.REQ_TYPE ='I' -- AND (A.TRASFER_USER_RECIVE = @p_USER_LOGIN -- OR (@ROLE_ID = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID AND ROLE_USER =@ROLE_ID) -- OR (A.DVDM_ID IS NULL AND A.DEP_ID =@DEP_ID_LG)) -- )) A.AUTH_STATUS <>'E' AND((@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS' AND A.REQ_TYPE ='I' AND ( (@ROLE_ID IN ('GDDV','TP','PP') AND A.BRANCH_ID =@p_BRANCH_LOGIN AND A.DEP_ID = @DEP_ID_LG) OR (@ROLE_ID NOT IN ('GDDV','TP','PP') AND (A.TRASFER_USER_RECIVE = @p_USER_LOGIN OR (@ROLE_ID = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID AND ROLE_USER =@ROLE_ID) OR (A.DVDM_ID IS NULL AND A.DEP_ID =@DEP_ID_LG) OR(A.DEP_ID =@DEP_ID_LG) OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH)) ))) AND ( 1=1 AND ( @ROLE_ID IN ('GDK','PTGD') AND (A.DVDM_ID =@DVDM_ID OR (A.DVDM_ID IS NULL OR A.DVDM_ID ='')) AND ((A.PROCESS IS NOT NULL AND A.PROCESS <>'') OR A.DEP_ID ='DEP000000000052' OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH))) OR (@ROLE_ID NOT IN ('GDK','PTGD')) ) ) OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG ='HS' AND A.REQ_TYPE <> 'I' AND (A.TRASFER_USER_RECIVE = @p_USER_LOGIN OR (@ROLE_ID IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') AND A.BRANCH_ID =@p_BRANCH_LOGIN AND (A.DEP_ID =@DEP_ID_LG OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH)) AND( A.TRASFER_USER_RECIVE IS NULL OR A.TRASFER_USER_RECIVE ='' OR (A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'' AND A.PROCESS IS NOT NULL AND A.PROCESS <>''))) OR (@ROLE_ID NOT IN ('GDDV','GDK','KTT','TPTC','PP','TBP','TP','TC') AND (A.DEP_ID =@DEP_ID_LG OR A.DEP_ID IN (SELECT * FROM @DEP_AUTH))) )) --OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'CN' AND (@ROLE_ID IN ('GDDV','PGD','TPGD') AND --( --(A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'' AND A.PROCESS IS NOT NULL AND A.PROCESS <>'') --OR A.TRASFER_USER_RECIVE ='' OR A.TRASFER_USER_RECIVE IS NULL) --OR ((A.BRANCH_CREATE <> @p_BRANCH_LOGIN AND A.TRASFER_USER_RECIVE IS NOT NULL AND A.TRASFER_USER_RECIVE <>'') OR A.BRANCH_CREATE = @p_BRANCH_LOGIN) ----AND A.BRANCH_ID IN (SELECT * FROM @tmp))) --AND A.BRANCH_ID =@p_BRANCH_LOGIN)) --OR (@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'CN' AND A.BRANCH_ID IN (SELECT * FROM @tmp)) -- OR (@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG = 'CN' AND (@ROLE_ID IN ('GDDV','TPGD','PPGD','PGD') AND A.BRANCH_ID =@p_BRANCH_LOGIN) OR(@p_TYPE_SEARCH ='HC' AND @BRANCH_TYPE_LG IN ('PGD','CN') AND (@ROLE_ID IN ('GDDV','TPGD','PPGD','PGD') AND A.BRANCH_ID =@p_BRANCH_LOGIN)) OR(@p_TYPE_SEARCH ='HC' AND (A.DVDM_ID IS NOT NULL AND A.DVDM_ID <>'' AND A.DVDM_ID =@DVDM_ID AND A.PROCESS IS NOT NULL AND A.PROCESS <>'' AND @ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID)) OR(A.DEP_ID IN (SELECT * FROM @TMP_DEP) AND A.PROCESS IS NOT NULL AND A.PROCESS <>'' AND @ROLE_ID IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID =A.REQ_PAY_ID))) OR(@p_TYPE_SEARCH ='HC' AND (@ROLE_ID = (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID= A.REQ_PAY_ID AND ROLE_USER IN ('TGD','HDQT') AND STATUS ='C'))) OR(@p_TYPE_SEARCH='KT' AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)) OR(@p_TYPE_SEARCH='HC' AND @ROLE_ID IN ('KSV','GDV') AND A.BRANCH_ID IN (SELECT BRANCH_ID from @tmp_Login)) OR(@p_TYPE_SEARCH='PAY' AND (A.REQ_AMT -ISNULL(A.PAY_AMT,0)) >=0 AND ( (@BRANCH_TYPE_LG ='HS' AND (A.DEP_ID =@DEP_ID_LG OR A.MAKER_ID =@p_USER_LOGIN OR A.MAKER_ID IS NULL)) OR (@BRANCH_TYPE_LG <> 'HS' AND A.BRANCH_ID = @p_BRANCH_LOGIN))) ))) AND ( (A.PAY_AMT = A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'HT')) OR (A.PAY_AMT = 0 AND (@p_TEMP_PAY_STATUS = 'DCHU') ) OR (A.PAY_AMT <> 0 and A.PAY_AMT < A.REQ_AMT AND (@p_TEMP_PAY_STATUS = 'DHU')) OR @p_TEMP_PAY_STATUS IS NULL OR @p_TEMP_PAY_STATUS = '' ) ) ) ORDER BY A.CREATE_DT DESC; END; ¿ ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_SendApp] @p_REQ_ID VARCHAR(20), @p_PROCESS_ID VARCHAR(20), @p_TLNAME VARCHAR(20), @p_MAKER_ID VARCHAR(20) AS BEGIN TRANSACTION IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (REQ_DT IS NULL OR REQ_DT='')) ) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Ngày yêu cầu bắt buộc nhập' ErrorDesc RETURN '-1' END IF((SELECT CONVERT(DATE, REQ_DT, 103) FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) > CONVERT(DATE, GETDATE(),103)) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Ngày yêu cầu mua sắm không được phép lớn hơn ngày hiện tại' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (REQ_REASON IS NULL OR REQ_REASON='')) ) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Mục đích yêu cầu bắt buộc nhập' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (PL_REQ_ID IS NULL OR PL_REQ_ID='')) ) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Tờ trình chủ trương bắt buộc nhập' ErrorDesc RETURN '-1' END IF(NOT EXISTS(SELECT REQ_DOC_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID ) ) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Chi tiết hàng hóa bắt buộc nhập' ErrorDesc RETURN '-1' END --- XOA DATA CU DELETE PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID ----- DECLARE @NS_TO_TRINH DECIMAL(18,0), @NS_PYCMS_USE DECIMAL(18,0), @NS_LUY_KE DECIMAL(18,0),@PL_REQ_CODE VARCHAR(50) SET @PL_REQ_CODE =(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)) SET @NS_TO_TRINH =(SELECT TOTAL_AMT FROM PL_REQUEST_DOC WHERE REQ_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)) SET @NS_PYCMS_USE =(SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID) SET @NS_LUY_KE =ISNULL((SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID IN (SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID=(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID) AND PROCESS_ID='APPROVE')),0) DECLARE @p_PL_REQ_ID VARCHAR(15),@p_REQ_DATE DATETIME SELECT @p_PL_REQ_ID=PL_REQ_ID,@p_REQ_DATE=REQ_DT FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID --IF( EXISTS( -- SELECT Temp.GD_ID FROM (SELECT GD_ID,SUM(TOTAL_AMT) AS TOTAL_AMT_ETM FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID -- GROUP BY GD_ID) Temp WHERE TOTAL_AMT_ETM > -- ((SELECT SUM(TEMP2.TOTAL_AMT) AS TOTAL_AMT_APP FROM -- (SELECT GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT -- WHERE REQ_ID=@p_PL_REQ_ID -- UNION -- SELECT GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT -- WHERE REQ_ID=(SELECT REQ_PARENT_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_PL_REQ_ID) ) TEMP2 WHERE TEMP2.GOODS_ID=Temp.GD_ID -- GROUP BY TEMP2.GOODS_ID) - (SELECT ISNULL(SUM(TOTAL_AMT),0) AS TOTAL_AMT_EXE FROM dbo.TR_REQUEST_DOC_DT WHERE GD_ID=Temp.GD_ID AND REQ_DOC_ID IN (SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID=@p_PL_REQ_ID AND PROCESS_ID='APPROVE') -- GROUP BY GD_ID)))) -- BEGIN -- ROLLBACK TRANSACTION -- SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, -- N'Thông tin liên quan tới phiếu yêu cầu mua sắm có sử dụng tờ trình số: '+ @PL_REQ_CODE -- +CHAR(10)+ N'Tổng số tiền lũy kế sử dụng ngân sách tờ trình: '+FORMAT(@NS_LUY_KE ,'#,#', 'vi-VN') -- +CHAR(10)+N'Tổng số tiền ngân sách còn lại của tờ trình: '+ FORMAT(@NS_TO_TRINH -@NS_LUY_KE ,'#,#', 'vi-VN') -- +CHAR(10)+ N'Số tiền sử dụng ngân sách cho phiếu yêu cầu mua sắm hiện tại vượt '+FORMAT(@NS_LUY_KE+@NS_PYCMS_USE -@NS_TO_TRINH ,'#,#', 'vi-VN')+N' so với tổng số tiền ngân sách còn lại của tờ trình.' -- +CHAR(10) +N'Vui lòng tra cứu tổng số tiền các phiếu yêu cầu mua sắm để biết thêm thông tin chi tiêt!' ErrorDesc -- RETURN '0' -- END -- LUCTV: 05-12-2020: KHAI BAO CURSOR DE KIEM TRA TONG HAN MUC SU DUNG PYC LINK TỚI TỜ TRÌNH -- LẤY SUM TỜ TRÌNH CHA DECLARE @SUM_TO_TRINH_CHINH DECIMAL(18) =0, @SUM_TO_TRINH_CON DECIMAL(18,0), @SUM_PYC_LUY_KE DECIMAL(18,0), @PYCMS_CODE_LIST VARCHAR(100) SET @SUM_TO_TRINH_CHINH =(SELECT ISNULL(SUM(TOTAL_AMT),0) FROM PL_REQUEST_DOC_DT WHERE REQ_ID =@p_PL_REQ_ID) -- LẤY SUM TỜ TRÌNH CON SET @SUM_TO_TRINH_CON =(SELECT ISNULL(SUM(TOTAL_AMT),0) FROM PL_REQUEST_DOC_DT WHERE REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_DOC WHERE REQ_PARENT_ID =@p_PL_REQ_ID)) --- SET @SUM_PYC_LUY_KE =(SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID IN (SELECT REQ_ID FROM TR_REQUEST_DOC WHERE PL_REQ_ID =@p_PL_REQ_ID AND AUTH_STATUS IN ('U','A')) AND REQ_DOC_ID <> @p_REQ_ID) IF(ISNULL(@SUM_TO_TRINH_CHINH,0) + ISNULL(@SUM_TO_TRINH_CON,0) <@SUM_PYC_LUY_KE+@NS_PYCMS_USE) BEGIN ROLLBACK TRANSACTION SET @PYCMS_CODE_LIST = (select STUFF( (select '; ' + DTA.REQ_CODE FROM TR_REQUEST_DOC DTA WHERE PL_REQ_ID =@p_PL_REQ_ID AND AUTH_STATUS IN ('U','A') FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')) SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'Thông tin liên quan tới phiếu yêu cầu mua sắm có sử dụng tờ trình số: '+ @PL_REQ_CODE +CHAR(10)+ N'Tổng số tiền lũy kế sử dụng ngân sách tờ trình: '+FORMAT(@SUM_PYC_LUY_KE ,'#,#', 'vi-VN') +CHAR(10)+N'Tổng số tiền ngân sách còn lại của tờ trình: '+ FORMAT(ISNULL(@SUM_TO_TRINH_CHINH,0)+ ISNULL(@SUM_TO_TRINH_CON,0) -@SUM_PYC_LUY_KE ,'#,#', 'vi-VN') +CHAR(10)+ N'Số tiền sử dụng ngân sách cho phiếu yêu cầu mua sắm hiện tại vượt '+FORMAT(ISNULL(@SUM_PYC_LUY_KE,0)+@NS_PYCMS_USE -(ISNULL(@SUM_TO_TRINH_CHINH,0)+ ISNULL(@SUM_TO_TRINH_CON,0)) ,'#,#', 'vi-VN')+N' so với tổng số tiền ngân sách còn lại của tờ trình.' +CHAR(10) +N'Vui lòng tra cứu tổng số tiền các phiếu yêu cầu mua sắm số: '+ISNULL(@PYCMS_CODE_LIST,'')+N' để biết thêm thông tin chi tiêt!' ErrorDesc RETURN '0' END -- KHAI BÁO CURRS0R ĐỂ CHECK LŨY KẾ TỪNG HẠNG MỤC DECLARE @PL_DT_ID VARCHAR(15), @SUM_@PL_DT_ID DECIMAL(18,0), @INDEX INT =0, @TOTAL_AMT_DETAIL DECIMAL(18,0), @REMAIN_AMT DECIMAL(18,0), @AMT_PL_DT DECIMAL(18,0), @AMT_PL_DT_EX DECIMAL(18,0) DECLARE CURS CURSOR FOR SELECT PL_REQDT_ID, TOTAL_AMT FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID OPEN CURS FETCH NEXT FROM CURS INTO @PL_DT_ID,@TOTAL_AMT_DETAIL WHILE @@FETCH_STATUS = 0 BEGIN SET @INDEX = @INDEX +1 -- KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET --IF((SELECT A.REQ_AMT - A.PAY_AMT FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.REQ_PAY_ID = @PAY_ADVANCE_ID)=0) SET @SUM_@PL_DT_ID =(SELECT SUM (TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID IN (SELECT REQ_ID FROM TR_REQUEST_DOC WHERE PL_REQ_ID =@p_PL_REQ_ID AND AUTH_STATUS IN ('U','A')) AND PL_REQDT_ID =@PL_DT_ID) SET @AMT_PL_DT =(SELECT ISNULL(TOTAL_AMT,0) FROM PL_REQUEST_DOC_DT WHERE REQDT_ID =@PL_DT_ID) SET @REMAIN_AMT = ISNULL(@AMT_PL_DT,0) -ISNULL(@SUM_@PL_DT_ID,0) --SET @SUM_@PL_DT_ID = ISNULL(@SUM_@PL_DT_ID,0)+ISNULL(@TOTAL_AMT_DETAIL,0) SET @AMT_PL_DT_EX =(SELECT SUM (TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID AND PL_REQDT_ID =@PL_DT_ID) --AND PL_REQDT_ID =@PL_DT_ID) --PRINT @SUM_@PL_DT_ID IF(ISNULL(@SUM_@PL_DT_ID,0)+ ISNULL(@AMT_PL_DT_EX,0) > @AMT_PL_DT) --IF(1<2) BEGIN ROLLBACK TRANSACTION SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Ngân sách sử dụng của hạng mục: '+ (SELECT ISNULL(DESCRIPTION,'') FROM PL_REQUEST_DOC_DT WHERE REQDT_ID =@PL_DT_ID) +N' đã vượt quá số tiền trình chủ trương.' +CHAR(10)+N'Số tiền đã gửi phê duyệt thành công trong những PUR khác: '+ FORMAT(ISNULL(@SUM_@PL_DT_ID,0) ,'#,#', 'vi-VN') +CHAR(10)+ N'Số tiền còn lại có thể sử dụng: '+ CASE WHEN @REMAIN_AMT >0 THEN FORMAT(ISNULL(@REMAIN_AMT,0) ,'#,#', 'vi-VN') ELSE '0.00' END --+CHAR(10)+N'Số tiền vượt hạn mức: '+ FORMAT(ISNULL(@SUM_@PL_DT_ID,0) +ISNULL(@TOTAL_AMT_DETAIL,0) - (@AMT_PL_DT) ,'#,#', 'vi-VN') ErrorDesc RETURN '-1' END --UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT = ISNULL(PAY_AMT,0) + ISNULL(@AMT_PAY,0.00) +ISNULL(@AMT_REVERT,0.00) WHERE REQ_PAY_ID =@PAY_ADVANCE_ID -- SO SÁNH TỔNG BREAK DOWN TỪ 1 HẠNG MỤC --IF(SELECT SUM()) FETCH NEXT FROM CURS INTO @PL_DT_ID,@TOTAL_AMT_DETAIL END CLOSE CURS DEALLOCATE CURS --: HẾT ĐOẠN BỔ SUNG CỦA LỰCTV --IF(EXISTS(SELECT REQDT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND REQ_DT <= @p_REQ_DATE)) --IF(EXISTS(SELECT REQPL_DT_ID FROM dbo.TR_REQUEST_DOC_PL_DT WHERE REQ_DOC_ID=@p_REQ_ID AND REQ_DT <= @p_REQ_DATE)) -- BEGIN -- ROLLBACK TRANSACTION -- SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'Lưới thông tin hàng hóa theo tờ trình: Ngày cần không được phép để trống và phải lớn hơn ngày yêu cầu mua sắm' ErrorDesc -- RETURN '0' --END IF(EXISTS(SELECT REQPL_DT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND REQ_DT <= @p_REQ_DATE)) BEGIN ROLLBACK TRANSACTION SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'Lưới thông tin hàng hóa mua sắm thực tế: Ngày cần không được phép để trống và phải lớn hơn ngày yêu cầu mua sắm' ErrorDesc RETURN '0' END IF(EXISTS(SELECT REQPL_DT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND (REQ_DT IS NULL OR REQ_DT =''))) BEGIN ROLLBACK TRANSACTION SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'Lưới thông tin hàng hóa mua sắm thực tế: Ngày cần không được phép để trống và phải lớn hơn ngày yêu cầu mua sắm' ErrorDesc RETURN '0' END DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20) ,@DEP_CREATE VARCHAR(20),@BRANCH_TYPE VARCHAR(10), @BRANCH_CREATE_TYPE VARCHAR(10) SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID SET @BRANCH_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID) SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE) -- KIEM TRA XEM CO CAP PHE DUYET HAY KHONG IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <>'')) BEGIN UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='SIGN' WHERE REQ_ID=@p_REQ_ID INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, DEP_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'SIGN', -- PROCESS_ID - varchar(10) 'C', -- STATUS - varchar(5) (SELECT RoleName FROM TL_USER WHERE TLNANME =(SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)), -- ROLE_USER - varchar(50) @BRANCH_CREATE, @DEP_CREATE, -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime '', -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', -- DVDM_ID - varchar(15) N'Chờ cấp phê duyệt trung gian xác nhận phiếu', -- NOTES - nvarchar(500) NULL -- IS_HAS_CHILD - bit ) END ELSE BEGIN --IF(@BRANCH_CREATE_TYPE='PGD') -- SET @BRANCH_CREATE=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE) UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='APPNEW' WHERE REQ_ID=@p_REQ_ID INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, DEP_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'APPNEW', -- PROCESS_ID - varchar(10) 'C', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) @BRANCH_CREATE, @DEP_CREATE, -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime '', -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', -- DVDM_ID - varchar(15) N'Chờ trưởng đơn vị phê duyệt', -- NOTES - nvarchar(500) NULL -- IS_HAS_CHILD - bit ) END INSERT INTO dbo.PL_PROCESS ( REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) --@p_PROCESS_ID, -- PROCESS_ID - varchar(10) 'SEND', @p_TLNAME, -- CHECKER_ID - varchar(15) GETDATE(), -- APPROVE_DT - datetime N'Nhân viên tạo phiếu và gửi phê duyệt thành công' , -- PROCESS_DESC - nvarchar(1000) N'Nhân viên gửi phê duyệt ' -- NOTES - nvarchar(1000) ) IF @@Error <> 0 GOTO ABORT COMMIT TRANSACTION SELECT '0' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc RETURN '-1' End ¿ ALTER PROCEDURE [dbo].[TR_REQUEST_DOC_SendApp] @p_REQ_ID VARCHAR(20), @p_PROCESS_ID VARCHAR(20), @p_TLNAME VARCHAR(20), @p_MAKER_ID VARCHAR(20) AS BEGIN TRANSACTION IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (REQ_DT IS NULL OR REQ_DT='')) ) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Ngày yêu cầu bắt buộc nhập' ErrorDesc RETURN '-1' END IF((SELECT CONVERT(DATE, REQ_DT, 103) FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID) > CONVERT(DATE, GETDATE(),103)) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Ngày yêu cầu mua sắm không được phép lớn hơn ngày hiện tại' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (REQ_REASON IS NULL OR REQ_REASON='')) ) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Mục đích yêu cầu bắt buộc nhập' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID AND (PL_REQ_ID IS NULL OR PL_REQ_ID='')) ) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Tờ trình chủ trương bắt buộc nhập' ErrorDesc RETURN '-1' END IF(NOT EXISTS(SELECT REQ_DOC_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID ) ) BEGIN ROLLBACK TRANSACTION SELECT '-1' AS Result ,'' REQ_CODE, '' REQ_ID, N'Chi tiết hàng hóa bắt buộc nhập' ErrorDesc RETURN '-1' END --- XOA DATA CU DELETE PL_REQUEST_PROCESS WHERE REQ_ID =@p_REQ_ID ----- DECLARE @NS_TO_TRINH DECIMAL(18,0), @NS_PYCMS_USE DECIMAL(18,0), @NS_LUY_KE DECIMAL(18,0),@PL_REQ_CODE VARCHAR(50) SET @PL_REQ_CODE =(SELECT REQ_CODE FROM PL_REQUEST_DOC WHERE REQ_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)) SET @NS_TO_TRINH =(SELECT TOTAL_AMT FROM PL_REQUEST_DOC WHERE REQ_ID =(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)) SET @NS_PYCMS_USE =(SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID) SET @NS_LUY_KE =ISNULL((SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID IN (SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID=(SELECT PL_REQ_ID FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID) AND PROCESS_ID='APPROVE')),0) DECLARE @p_PL_REQ_ID VARCHAR(15),@p_REQ_DATE DATETIME SELECT @p_PL_REQ_ID=PL_REQ_ID,@p_REQ_DATE=REQ_DT FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID --IF( EXISTS( -- SELECT Temp.GD_ID FROM (SELECT GD_ID,SUM(TOTAL_AMT) AS TOTAL_AMT_ETM FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID -- GROUP BY GD_ID) Temp WHERE TOTAL_AMT_ETM > -- ((SELECT SUM(TEMP2.TOTAL_AMT) AS TOTAL_AMT_APP FROM -- (SELECT GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT -- WHERE REQ_ID=@p_PL_REQ_ID -- UNION -- SELECT GOODS_ID,TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT -- WHERE REQ_ID=(SELECT REQ_PARENT_ID FROM dbo.PL_REQUEST_DOC WHERE REQ_ID=@p_PL_REQ_ID) ) TEMP2 WHERE TEMP2.GOODS_ID=Temp.GD_ID -- GROUP BY TEMP2.GOODS_ID) - (SELECT ISNULL(SUM(TOTAL_AMT),0) AS TOTAL_AMT_EXE FROM dbo.TR_REQUEST_DOC_DT WHERE GD_ID=Temp.GD_ID AND REQ_DOC_ID IN (SELECT REQ_ID FROM dbo.TR_REQUEST_DOC WHERE PL_REQ_ID=@p_PL_REQ_ID AND PROCESS_ID='APPROVE') -- GROUP BY GD_ID)))) -- BEGIN -- ROLLBACK TRANSACTION -- SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, -- N'Thông tin liên quan tới phiếu yêu cầu mua sắm có sử dụng tờ trình số: '+ @PL_REQ_CODE -- +CHAR(10)+ N'Tổng số tiền lũy kế sử dụng ngân sách tờ trình: '+FORMAT(@NS_LUY_KE ,'#,#', 'vi-VN') -- +CHAR(10)+N'Tổng số tiền ngân sách còn lại của tờ trình: '+ FORMAT(@NS_TO_TRINH -@NS_LUY_KE ,'#,#', 'vi-VN') -- +CHAR(10)+ N'Số tiền sử dụng ngân sách cho phiếu yêu cầu mua sắm hiện tại vượt '+FORMAT(@NS_LUY_KE+@NS_PYCMS_USE -@NS_TO_TRINH ,'#,#', 'vi-VN')+N' so với tổng số tiền ngân sách còn lại của tờ trình.' -- +CHAR(10) +N'Vui lòng tra cứu tổng số tiền các phiếu yêu cầu mua sắm để biết thêm thông tin chi tiêt!' ErrorDesc -- RETURN '0' -- END -- LUCTV: 05-12-2020: KHAI BAO CURSOR DE KIEM TRA TONG HAN MUC SU DUNG PYC LINK TỚI TỜ TRÌNH -- LẤY SUM TỜ TRÌNH CHA DECLARE @SUM_TO_TRINH_CHINH DECIMAL(18) =0, @SUM_TO_TRINH_CON DECIMAL(18,0), @SUM_PYC_LUY_KE DECIMAL(18,0), @PYCMS_CODE_LIST VARCHAR(100) SET @SUM_TO_TRINH_CHINH =(SELECT ISNULL(SUM(TOTAL_AMT),0) FROM PL_REQUEST_DOC_DT WHERE REQ_ID =@p_PL_REQ_ID) -- LẤY SUM TỜ TRÌNH CON SET @SUM_TO_TRINH_CON =(SELECT ISNULL(SUM(TOTAL_AMT),0) FROM PL_REQUEST_DOC_DT WHERE REQ_ID IN (SELECT REQ_ID FROM PL_REQUEST_DOC WHERE REQ_PARENT_ID =@p_PL_REQ_ID)) --- SET @SUM_PYC_LUY_KE =(SELECT SUM(TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID IN (SELECT REQ_ID FROM TR_REQUEST_DOC WHERE PL_REQ_ID =@p_PL_REQ_ID AND AUTH_STATUS IN ('U','A')) AND REQ_DOC_ID <> @p_REQ_ID) IF(ISNULL(@SUM_TO_TRINH_CHINH,0) + ISNULL(@SUM_TO_TRINH_CON,0) <@SUM_PYC_LUY_KE+@NS_PYCMS_USE) BEGIN ROLLBACK TRANSACTION SET @PYCMS_CODE_LIST = (select STUFF( (select '; ' + DTA.REQ_CODE FROM TR_REQUEST_DOC DTA WHERE PL_REQ_ID =@p_PL_REQ_ID AND AUTH_STATUS IN ('U','A') FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')) SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'Thông tin liên quan tới phiếu yêu cầu mua sắm có sử dụng tờ trình số: '+ @PL_REQ_CODE +CHAR(10)+ N'Tổng số tiền lũy kế sử dụng ngân sách tờ trình: '+FORMAT(@SUM_PYC_LUY_KE ,'#,#', 'vi-VN') +CHAR(10)+N'Tổng số tiền ngân sách còn lại của tờ trình: '+ FORMAT(ISNULL(@SUM_TO_TRINH_CHINH,0)+ ISNULL(@SUM_TO_TRINH_CON,0) -@SUM_PYC_LUY_KE ,'#,#', 'vi-VN') +CHAR(10)+ N'Số tiền sử dụng ngân sách cho phiếu yêu cầu mua sắm hiện tại vượt '+FORMAT(ISNULL(@SUM_PYC_LUY_KE,0)+@NS_PYCMS_USE -(ISNULL(@SUM_TO_TRINH_CHINH,0)+ ISNULL(@SUM_TO_TRINH_CON,0)) ,'#,#', 'vi-VN')+N' so với tổng số tiền ngân sách còn lại của tờ trình.' +CHAR(10) +N'Vui lòng tra cứu tổng số tiền các phiếu yêu cầu mua sắm số: '+ISNULL(@PYCMS_CODE_LIST,'')+N' để biết thêm thông tin chi tiêt!' ErrorDesc RETURN '0' END -- KHAI BÁO CURRS0R ĐỂ CHECK LŨY KẾ TỪNG HẠNG MỤC DECLARE @PL_DT_ID VARCHAR(15), @SUM_@PL_DT_ID DECIMAL(18,0), @INDEX INT =0, @TOTAL_AMT_DETAIL DECIMAL(18,0), @REMAIN_AMT DECIMAL(18,0), @AMT_PL_DT DECIMAL(18,0), @AMT_PL_DT_EX DECIMAL(18,0) DECLARE CURS CURSOR FOR SELECT PL_REQDT_ID, TOTAL_AMT FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID OPEN CURS FETCH NEXT FROM CURS INTO @PL_DT_ID,@TOTAL_AMT_DETAIL WHILE @@FETCH_STATUS = 0 BEGIN SET @INDEX = @INDEX +1 -- KIEM TRA NEU SO PHIEU TAM UNG NAY DA THANH TOAN XONG THI KHONG CHO PHEP DUYET --IF((SELECT A.REQ_AMT - A.PAY_AMT FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.REQ_PAY_ID = @PAY_ADVANCE_ID)=0) SET @SUM_@PL_DT_ID =(SELECT SUM (TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID IN (SELECT REQ_ID FROM TR_REQUEST_DOC WHERE PL_REQ_ID =@p_PL_REQ_ID AND AUTH_STATUS IN ('U','A')) AND PL_REQDT_ID =@PL_DT_ID) SET @AMT_PL_DT =(SELECT ISNULL(TOTAL_AMT,0) FROM PL_REQUEST_DOC_DT WHERE REQDT_ID =@PL_DT_ID) SET @REMAIN_AMT = ISNULL(@AMT_PL_DT,0) -ISNULL(@SUM_@PL_DT_ID,0) --SET @SUM_@PL_DT_ID = ISNULL(@SUM_@PL_DT_ID,0)+ISNULL(@TOTAL_AMT_DETAIL,0) SET @AMT_PL_DT_EX =(SELECT SUM (TOTAL_AMT) FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_REQ_ID AND PL_REQDT_ID =@PL_DT_ID) --AND PL_REQDT_ID =@PL_DT_ID) --PRINT @SUM_@PL_DT_ID IF(ISNULL(@SUM_@PL_DT_ID,0)+ ISNULL(@AMT_PL_DT_EX,0) > @AMT_PL_DT) --IF(1<2) BEGIN ROLLBACK TRANSACTION SELECT '-1' Result,'' REQ_PAY_ID,N'Dòng '+ CONVERT(VARCHAR(5),@INDEX)+ N': Ngân sách sử dụng của hạng mục: '+ (SELECT ISNULL(DESCRIPTION,'') FROM PL_REQUEST_DOC_DT WHERE REQDT_ID =@PL_DT_ID) +N' đã vượt quá số tiền trình chủ trương.' +CHAR(10)+N'Số tiền đã gửi phê duyệt thành công trong những PUR khác: '+ FORMAT(ISNULL(@SUM_@PL_DT_ID,0) ,'#,#', 'vi-VN') +CHAR(10)+ N'Số tiền còn lại có thể sử dụng: '+ CASE WHEN @REMAIN_AMT >0 THEN FORMAT(ISNULL(@REMAIN_AMT,0) ,'#,#', 'vi-VN') ELSE '0.00' END --+CHAR(10)+N'Số tiền vượt hạn mức: '+ FORMAT(ISNULL(@SUM_@PL_DT_ID,0) +ISNULL(@TOTAL_AMT_DETAIL,0) - (@AMT_PL_DT) ,'#,#', 'vi-VN') ErrorDesc RETURN '-1' END --UPDATE TR_REQ_ADVANCE_PAYMENT SET PAY_AMT = ISNULL(PAY_AMT,0) + ISNULL(@AMT_PAY,0.00) +ISNULL(@AMT_REVERT,0.00) WHERE REQ_PAY_ID =@PAY_ADVANCE_ID -- SO SÁNH TỔNG BREAK DOWN TỪ 1 HẠNG MỤC --IF(SELECT SUM()) FETCH NEXT FROM CURS INTO @PL_DT_ID,@TOTAL_AMT_DETAIL END CLOSE CURS DEALLOCATE CURS --: HẾT ĐOẠN BỔ SUNG CỦA LỰCTV --IF(EXISTS(SELECT REQDT_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND REQ_DT <= @p_REQ_DATE)) --IF(EXISTS(SELECT REQPL_DT_ID FROM dbo.TR_REQUEST_DOC_PL_DT WHERE REQ_DOC_ID=@p_REQ_ID AND REQ_DT <= @p_REQ_DATE)) -- BEGIN -- ROLLBACK TRANSACTION -- SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'Lưới thông tin hàng hóa theo tờ trình: Ngày cần không được phép để trống và phải lớn hơn ngày yêu cầu mua sắm' ErrorDesc -- RETURN '0' --END IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND REQ_DT <= @p_REQ_DATE)) BEGIN ROLLBACK TRANSACTION SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'Lưới thông tin hàng hóa mua sắm thực tế: Ngày cần không được phép để trống và phải lớn hơn ngày yêu cầu mua sắm' ErrorDesc RETURN '0' END IF(EXISTS(SELECT * FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_REQ_ID AND (REQ_DT IS NULL OR REQ_DT =''))) BEGIN ROLLBACK TRANSACTION SELECT 'REQ-00001' Result,'' REQ_CODE, '' REQ_ID, N'Lưới thông tin hàng hóa mua sắm thực tế: Ngày cần không được phép để trống và phải lớn hơn ngày yêu cầu mua sắm' ErrorDesc RETURN '0' END DECLARE @BRANCH_ID VARCHAR(20), @DEP_ID VARCHAR(20),@BRANCH_CREATE VARCHAR(20) ,@DEP_CREATE VARCHAR(20),@BRANCH_TYPE VARCHAR(10), @BRANCH_CREATE_TYPE VARCHAR(10) SELECT @BRANCH_CREATE=BRANCH_CREATE,@DEP_CREATE=DEP_CREATE FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_REQ_ID SET @BRANCH_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_ID) SET @BRANCH_CREATE_TYPE=(SELECT BRANCH_TYPE FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE) -- KIEM TRA XEM CO CAP PHE DUYET HAY KHONG IF(EXISTS(SELECT * FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID AND SIGN_USER IS NOT NULL AND SIGN_USER <>'')) BEGIN UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='SIGN' WHERE REQ_ID=@p_REQ_ID INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, DEP_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'SIGN', -- PROCESS_ID - varchar(10) 'C', -- STATUS - varchar(5) (SELECT RoleName FROM TL_USER WHERE TLNANME =(SELECT SIGN_USER FROM TR_REQUEST_DOC WHERE REQ_ID =@p_REQ_ID)), -- ROLE_USER - varchar(50) @BRANCH_CREATE, @DEP_CREATE, -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime '', -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', -- DVDM_ID - varchar(15) N'Chờ cấp phê duyệt trung gian xác nhận phiếu', -- NOTES - nvarchar(500) NULL -- IS_HAS_CHILD - bit ) END ELSE BEGIN --IF(@BRANCH_CREATE_TYPE='PGD') -- SET @BRANCH_CREATE=(SELECT FATHER_ID FROM dbo.CM_BRANCH WHERE BRANCH_ID=@BRANCH_CREATE) UPDATE dbo.TR_REQUEST_DOC SET AUTH_STATUS='U',PROCESS_ID='APPNEW' WHERE REQ_ID=@p_REQ_ID INSERT INTO dbo.PL_REQUEST_PROCESS ( REQ_ID, PROCESS_ID, STATUS, ROLE_USER, BRANCH_ID, DEP_ID, CHECKER_ID, APPROVE_DT, PARENT_PROCESS_ID, IS_LEAF, COST_ID, DVDM_ID, NOTES, IS_HAS_CHILD ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) 'APPNEW', -- PROCESS_ID - varchar(10) 'C', -- STATUS - varchar(5) 'GDDV', -- ROLE_USER - varchar(50) @BRANCH_CREATE, @DEP_CREATE, -- BRANCH_ID - varchar(15) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime '', -- PARENT_PROCESS_ID - varchar(10) 'N', -- IS_LEAF - varchar(1) '', -- COST_ID - varchar(15) '', -- DVDM_ID - varchar(15) N'Chờ trưởng đơn vị phê duyệt', -- NOTES - nvarchar(500) NULL -- IS_HAS_CHILD - bit ) END INSERT INTO dbo.PL_PROCESS ( REQ_ID, PROCESS_ID, CHECKER_ID, APPROVE_DT, PROCESS_DESC, NOTES ) VALUES ( @p_REQ_ID, -- REQ_ID - varchar(15) --@p_PROCESS_ID, -- PROCESS_ID - varchar(10) 'SEND', @p_TLNAME, -- CHECKER_ID - varchar(15) GETDATE(), -- APPROVE_DT - datetime N'Nhân viên tạo phiếu và gửi phê duyệt thành công' , -- PROCESS_DESC - nvarchar(1000) N'Nhân viên gửi phê duyệt ' -- NOTES - nvarchar(1000) ) IF @@Error <> 0 GOTO ABORT COMMIT TRANSACTION SELECT '0' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc RETURN '0' ABORT: BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result,'' REQ_CODE,''REQ_ID, '' ErrorDesc RETURN '-1' End