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, CASE WHEN A.REQ_TYPE <> 'I' THEN DP.DEP_NAME ELSE DP_R.DEP_NAME END AS 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 LEFT JOIN TL_USER TL_R ON A.REF_ID = TL_R.TLNANME LEFT JOIN CM_DEPARTMENT DP_R ON TL_R.SECUR_CODE = DP_R.DEP_ID WHERE 1=1 AND(A.REQ_PAY_ID=@p_REQ_PAY_ID) ORDER BY A.CREATE_DT DESC; ¿ ALTER PROCEDURE [dbo].[PAY_ENTRIES_POST_UpdRef] @p_ET_ID varchar(50) = NULL, @p_PAIR_ID varchar(50) = NULL, @p_REF_NO VARCHAR(100) = ''---SO REF NO CUA CORE TRA VE AS BEGIN TRY ---- -- CODE ANH THIEU ---- --UPDATE PAY_ENTRIES_POST --SET REF_NO = @p_REF_NO --WHERE ET_ID = @p_ET_ID AND ENTRY_PAIR = @p_PAIR_ID --WHERE ENTRY_PAIR = @p_PAIR_ID AND TRN_ID =@REQ_ID --WHERE ET_ID IN (SELECT * FROM wsiSplit(@p_ET_ID,',')) --SELECT * FROM wsiSplit('PET000000000016,PET000000000017,PET000000000018,PET000000000019',',') ---- --- END --- CODE LUCTV DU KIEN DIEU CHINH DECLARE @TRN_ID VARCHAR(15), @ENTRY_PAIR VARCHAR(15) SET @TRN_ID =(SELECT TOP 1 TRN_ID FROM PAY_ENTRIES_POST WHERE ET_ID IN (SELECT * FROM wsiSplit(@p_ET_ID,','))) SET @ENTRY_PAIR =(SELECT TOP 1 ENTRY_PAIR FROM PAY_ENTRIES_POST WHERE ET_ID IN (SELECT * FROM wsiSplit(@p_ET_ID,','))) UPDATE PAY_ENTRIES_POST SET REF_NO = @p_REF_NO WHERE ISNULL(REF_NO,'') = '' AND TRN_ID =@TRN_ID AND ENTRY_PAIR =@ENTRY_PAIR SELECT '0' RESULT, '' ERROR RETURN 0 END TRY BEGIN CATCH SELECT '-1' RESULT, ERROR_MESSAGE() ERROR RETURN -1 END CATCH ¿ 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 @TYPE_COST VARCHAR(25), @FR_LEVEL INT ,@TO_LEVEL INT 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),TYPE_COST VARCHAR(25) , FR_LEVEL INT ,TO_LEVEL INT) OPEN XmlDataGood FETCH NEXT FROM XmlDataGood INTO @TRADE_ID,@GD_ID ,@AMT_APP ,@AMT_DO,@AMT_EXE ,@AMT_REMAIN_GD,@CURRENCY,@RATE,@REASON,@TYPE_COST , @FR_LEVEL ,@TO_LEVEL 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,TYPE_COST , FR_LEVEL ,TO_LEVEL) 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,@TYPE_COST , @FR_LEVEL ,@TO_LEVEL) 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,@TYPE_COST , @FR_LEVEL ,@TO_LEVEL 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' ORDER BY LIMIT_VALUE ASC 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' ORDER BY LIMIT_VALUE ASC 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') ORDER BY LIMIT_VALUE ASC 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' ORDER BY LIMIT_VALUE ASC 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' ORDER BY LIMIT_VALUE ASC 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 AND ROLE_ID ='GDDV' --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 AND ROLE_ID ='TPGD' --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 ORDER BY A.AMT ASC 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') --IF(@ROLE_ID <> 'TKTGD' AND @ROLE_ID <> 'TKHDQT') --BEGIN BREAK; --END 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 DECLARE @SUM_TEMP_PAY DECIMAL, @SUM_PAY DECIMAL IF (@p_REQ_TYPE ='I') BEGIN SET @SUM_TEMP_PAY = (SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE REF_ID =@p_REF_ID AND AUTH_STATUS ='A' AND REQ_TYPE ='I') SET @SUM_PAY = (SELECT SUM (ISNULL(PAY_AMT,0)) FROM TR_REQ_ADVANCE_PAYMENT WHERE REF_ID =@p_REF_ID AND AUTH_STATUS_KT ='A' AND REQ_TYPE ='I') UPDATE TR_REQ_ADVANCE_PAYMENT SET REQ_TEMP_AMT =ISNULL(@SUM_TEMP_PAY,0) -ISNULL(@SUM_PAY,0) 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 UPDATE PL_REQUEST_PROCESS SET ROLE_USER ='GDDV' WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER ='PGD' UPDATE PL_REQUEST_PROCESS SET ROLE_USER ='TPGD' WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER ='PPGD' 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 -- FIX PHONG HO LOI LIEN QUAN DEN VIEC TAI KHOAN TAM UNG BI SAI DECLARE @REQ_PAY_ID varchar(15), @l_REF_ID varchar(15) DECLARE cursorProduct CURSOR LOCAL FOR SELECT REQ_PAY_ID,REF_ID FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.REQ_TYPE ='I' Open cursorProduct FETCH NEXT FROM cursorProduct INTO @REQ_PAY_ID, @l_REF_ID WHILE @@FETCH_STATUS = 0 BEGIN UPDATE TR_REQ_ADVANCE_PAYMENT SET RECEIVER_DEBIT =(SELECT TOP 1 ISNULL(ACC_NUM,'') FROM CM_ACCOUNT_PAY WHERE REF_ID =@l_REF_ID AND ACC_TYPE ='ADV_PAY' ) WHERE REQ_PAY_ID =@REQ_PAY_ID FETCH NEXT FROM cursorProduct INTO @REQ_PAY_ID, @l_REF_ID 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].[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 @TYPE_COST VARCHAR(5) , @FR_LEVEL INT ,@TO_LEVEL INT 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),TYPE_COST VARCHAR(5) , FR_LEVEL INT ,TO_LEVEL INT) OPEN XmlDataGood FETCH NEXT FROM XmlDataGood INTO @TRADE_ID,@GD_ID ,@AMT_APP ,@AMT_DO,@AMT_EXE ,@AMT_REMAIN_GD,@CURRENCY,@RATE,@REASON,@TYPE_COST, @FR_LEVEL,@TO_LEVEL 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,TYPE_COST, FR_LEVEL,TO_LEVEL) 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,@TYPE_COST, @FR_LEVEL,@TO_LEVEL) 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,@TYPE_COST, @FR_LEVEL,@TO_LEVEL 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 AND ROLE_ID ='GDDV' --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 AND ROLE_ID ='TPGD' --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 ORDER BY A.AMT ASC 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') --IF(@ROLE_ID <> 'TKTGD' AND @ROLE_ID <> 'TKHDQT') --BEGIN BREAK; --END 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' DECLARE @SUM_TEMP_PAY DECIMAL, @SUM_PAY DECIMAL IF (@p_REQ_TYPE ='I') BEGIN SET @SUM_TEMP_PAY = (SELECT SUM(REQ_AMT*ISNULL(RATE,1)) FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_TYPE=@p_REQ_TYPE AND REF_ID =@p_REF_ID AND AUTH_STATUS ='A' AND REQ_TYPE ='I') SET @SUM_PAY = (SELECT SUM (ISNULL(PAY_AMT,0)) FROM TR_REQ_ADVANCE_PAYMENT WHERE REF_ID =@p_REF_ID AND AUTH_STATUS_KT ='A' AND REQ_TYPE ='I') UPDATE TR_REQ_ADVANCE_PAYMENT SET REQ_TEMP_AMT =ISNULL(@SUM_TEMP_PAY,0) -ISNULL(@SUM_PAY,0), RECEIVER_DEBIT =(SELECT TOP 1 ISNULL(ACC_NUM,'') FROM CM_ACCOUNT_PAY WHERE REF_ID =@p_REF_ID AND ACC_TYPE ='ADV_PAY' ) WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_TYPE ='I' END 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 UPDATE PL_REQUEST_PROCESS SET ROLE_USER ='GDDV' WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER ='PGD' UPDATE PL_REQUEST_PROCESS SET ROLE_USER ='TPGD' WHERE REQ_ID =@p_REQ_PAY_ID AND ROLE_USER ='PPGD' 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 -- FIX PHONG HO LOI LIEN QUAN DEN VIEC TAI KHOAN TAM UNG BI SAI DECLARE @REQ_PAY_ID varchar(15), @l_REF_ID varchar(15) DECLARE cursorProduct CURSOR LOCAL FOR SELECT REQ_PAY_ID,REF_ID FROM TR_REQ_ADVANCE_PAYMENT A WHERE A.REQ_TYPE ='I' Open cursorProduct FETCH NEXT FROM cursorProduct INTO @REQ_PAY_ID, @l_REF_ID WHILE @@FETCH_STATUS = 0 BEGIN UPDATE TR_REQ_ADVANCE_PAYMENT SET RECEIVER_DEBIT =(SELECT TOP 1 ISNULL(ACC_NUM,'') FROM CM_ACCOUNT_PAY WHERE REF_ID =@l_REF_ID AND ACC_TYPE ='ADV_PAY' ) WHERE REQ_PAY_ID =@REQ_PAY_ID FETCH NEXT FROM cursorProduct INTO @REQ_PAY_ID, @l_REF_ID 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