ALTER PROCEDURE [dbo].[TR_REQ_DOC_Ins_To_PO] @p_TR_REQ_ID VARCHAR(15) AS BEGIN TRANSACTION; DECLARE @l_SUP_ID VARCHAR(15) DECLARE @p_PO_CODE VARCHAR(15), @p_BRANCH_ID VARCHAR(20), @p_SUP_ID VARCHAR(20), @p_TOTAL_AMT DECIMAL(18,2), @p_MAKER_ID VARCHAR(20), @p_TR_REQ_CODE VARCHAR(20), @p_SUP_NAME NVARCHAR(200), @p_SUP_ADDR NVARCHAR(200), @p_BRANCH_RE VARCHAR(20), @p_PO_NAME NVARCHAR(100); --- LAY DANH SACH NHA CUNG CAP CUA HANG HOA TRONG PYCMS - LUCTV 25052020 DECLARE @TABLE_NCC TABLE (SUP_ID VARCHAR(15)) INSERT INTO @TABLE_NCC SELECT SUP_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_TR_REQ_ID GROUP BY SUP_ID WHILE((SELECT COUNT(*) FROM @TABLE_NCC)>0) BEGIN SET @l_SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC) --insert master DECLARE @l_PO_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_PO_MASTER', @l_PO_ID OUT; IF @l_PO_ID = '' OR @l_PO_ID IS NULL GOTO ABORT; SELECT @p_BRANCH_ID=BRANCH_DVMS,@p_BRANCH_RE=BRANCH_CREATE,@p_TR_REQ_CODE=REQ_CODE,@p_PO_NAME=REQ_REASON,@p_MAKER_ID=USER_DVMS FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_TR_REQ_ID --SELECT TOP 1 @p_SUP_ID = SUP_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID SET @p_SUP_ID =@l_SUP_ID SET @p_TOTAL_AMT= (SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID) --TU PHAT SINH SO PO DECLARE @ldate INT = (SELECT YEAR(GETDATE()) ); EXEC [dbo].[TR_PO_MASTER_NO_Gen] @p_BRANCH_ID, @ldate, @p_PO_CODE OUT; IF @p_PO_CODE = '' OR @p_PO_CODE IS NULL GOTO ABORT; INSERT INTO TR_PO_MASTER ( [PO_TYPE], [PO_ID], [PO_CODE], [PO_NAME], [CONTRACT_ID], [SUP_ID], [SUP_NAME], [SUP_ADDR], [INPUT_DT], [PAYMENT_DT], [TOTAL_AMT], [REQ_DOC_ID], [DELIVERY_DT], [PAYAPP_DT], [NOTES], [RECORD_STATUS], [MAKER_ID], [CREATE_DT], [AUTH_STATUS], [CHECKER_ID], [APPROVE_DT], BRANCH_ID ) VALUES (1, @l_PO_ID, @p_PO_CODE, @p_PO_NAME, '', @p_SUP_ID, @p_SUP_NAME, @p_SUP_ADDR, GETDATE(), NULL, @p_TOTAL_AMT, @p_TR_REQ_ID, NULL, NULL, '', '1', @p_MAKER_ID, GETDATE(), 'E', NULL, NULL, @p_BRANCH_ID); IF @@Error <> 0 GOTO ABORT; PRINT 'INSERT MASTER SUCCESS'; INSERT INTO dbo.TR_PO_MASTER_TEMP ( PO_ID, TR_REQ_ID, TR_REQ_CODE ) VALUES ( @l_PO_ID, -- PO_ID - varchar(15) @p_TR_REQ_ID, -- TR_REQ_ID - varchar(20) @p_TR_REQ_CODE -- TR_REQ_CODE - varchar(20) ); DECLARE @HANGHOA_ID VARCHAR(20),@QUANTITY DECIMAL(18,0),@PRICE DECIMAL(18,2),@TOTAL_AMT DECIMAL(18,2),@HH_TYPE VARCHAR(20),@HH_NAME NVARCHAR(100) DECLARE lstData CURSOR FOR SELECT HANGHOA_ID,QUANTITY,PRICE,TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID OPEN lstData FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT WHILE @@FETCH_STATUS = 0 BEGIN SELECT @HH_NAME= HH_NAME,@HH_TYPE=HH_TYPE_ID FROM dbo.CM_HANGHOA WHERE HH_ID=@HANGHOA_ID DECLARE @l_PD_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'TR_PO_DETAIL', @l_PD_ID out IF @l_PD_ID='' OR @l_PD_ID IS NULL GOTO ABORT INSERT INTO dbo.TR_PO_DETAIL ( PD_ID, PO_ID, PLAN_ID, TRADE_ID, GOODS_ID, DESCRIPTION, UNIT_ID, QUANTITY, PRICE, TOTAL_AMT, IS_DELIVERY, DELIVERY_DT, PAYMENT_STATUS, AMOUNT_PAID, PAID_DT, INVOICENO, NOTES, RECEIVE_BRANCH, RECEIVE_ADDR, RECEIVE_PERSON, RECEIVE_TEL, RECORD_STATUS, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, EXP_DELIVERY_DT, GOODS_NAME, INVOICE_DT, GOODSTYPE_REAL, VAT, PRICE_VAT, CONTRACT_DT ) VALUES ( @l_PD_ID, -- PD_ID - varchar(15) @l_PO_ID, -- PO_ID - varchar(15) '', -- PLAN_ID - varchar(15) '', -- TRADE_ID - varchar(15) @HANGHOA_ID, -- GOODS_ID - varchar(15) N'', -- DESCRIPTION - nvarchar(500) '', -- UNIT_ID - varchar(15) @QUANTITY, -- QUANTITY - decimal(18, 0) ROUND(@PRICE/1.1,0), -- PRICE - decimal(18, 0) @TOTAL_AMT, -- TOTAL_AMT - decimal(18, 0) '', -- IS_DELIVERY - varchar(1) NULL, -- DELIVERY_DT - datetime 'CTT', -- PAYMENT_STATUS - varchar(4) NULL, -- AMOUNT_PAID - decimal(18, 0) NULL, -- PAID_DT - datetime '', -- INVOICENO - varchar(1000) N'', -- NOTES - nvarchar(1000) @p_BRANCH_RE, -- RECEIVE_BRANCH - varchar(15) N'', -- RECEIVE_ADDR - nvarchar(1000) N'', -- RECEIVE_PERSON - nvarchar(500) '', -- RECEIVE_TEL - varchar(100) '', -- RECORD_STATUS - varchar(1) @p_MAKER_ID, -- MAKER_ID - varchar(15) GETDATE(), -- CREATE_DT - datetime '', -- AUTH_STATUS - varchar(50) '', -- CHECKER_ID - varchar(15) NULL, -- APPROVE_DT - datetime NULL, -- EXP_DELIVERY_DT - datetime @HH_NAME, -- GOODS_NAME - nvarchar(500) NULL, -- INVOICE_DT - datetime @HH_TYPE, -- GOODSTYPE_REAL - varchar(15) 10, -- VAT - decimal(18, 2) --@TOTAL_AMT - (ROUND(@PRICE/1.1,0) *@QUANTITY), -- PRICE_VAT - decimal(18, 0) @PRICE -ROUND(@PRICE/1.1,0), '' -- CONTRACT_DT - varchar(15) ) FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT END CLOSE lstData; DEALLOCATE lstData; SET @TOTAL_AMT=(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID) DECLARE @l_PAY_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT INSERT INTO dbo.TR_PO_PAYMENT ( PAY_ID, PO_ID, PAY_PHASE, EXP_DT, [PERCENT], AMOUNT, NOTES, RECORD_STATUS, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT ) VALUES ( @l_PAY_ID, -- PAY_ID - varchar(15) @l_PO_ID, -- PO_ID - varchar(15) '1', -- PAY_PHASE - varchar(20) GETDATE(), -- EXP_DT - datetime 100, -- PERCENT - decimal(18, 0) @TOTAL_AMT , -- AMOUNT - decimal(18, 0) N'', -- NOTES - nvarchar(1000) '1', -- RECORD_STATUS - varchar(1) @p_MAKER_ID, -- MAKER_ID - varchar(15) GETDATE(), -- CREATE_DT - datetime 'U', -- AUTH_STATUS - varchar(50) '', -- CHECKER_ID - varchar(15) NULL -- APPROVE_DT - datetime ) EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT --INSERT INTO dbo.TR_PO_PAYMENT --( -- PAY_ID, -- PO_ID, -- PAY_PHASE, -- EXP_DT, -- [PERCENT], -- AMOUNT, -- NOTES, -- RECORD_STATUS, -- MAKER_ID, -- CREATE_DT, -- AUTH_STATUS, -- CHECKER_ID, -- APPROVE_DT --) --VALUES --( @l_PAY_ID, -- PAY_ID - varchar(15) -- @l_PO_ID, -- PO_ID - varchar(15) -- '2', -- PAY_PHASE - varchar(20) -- GETDATE(), -- EXP_DT - datetime -- 50, -- PERCENT - decimal(18, 0) -- @TOTAL_AMT / 2 , -- AMOUNT - decimal(18, 0) -- N'', -- NOTES - nvarchar(1000) -- '1', -- RECORD_STATUS - varchar(1) -- @p_MAKER_ID, -- MAKER_ID - varchar(15) -- GETDATE(), -- CREATE_DT - datetime -- 'E', -- AUTH_STATUS - varchar(50) -- '', -- CHECKER_ID - varchar(15) -- NULL -- APPROVE_DT - datetime -- ) IF(@TOTAL_AMT <=5000000) BEGIN UPDATE dbo.TR_PO_MASTER SET AUTH_STATUS='A' WHERE PO_ID=@l_PO_ID END --XOA DI NHA CUNG CAP DELETE FROM @TABLE_NCC WHERE SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC) --- KET THUC VONG LAP KIEM TRA DANH SACH NHA CUNG CAP PYCMS END COMMIT TRANSACTION; RETURN 1; ABORT: BEGIN ROLLBACK TRANSACTION; RETURN 1; END; ABORT1: BEGIN CLOSE lstData; DEALLOCATE lstData; ROLLBACK TRANSACTION; RETURN 1; END; 2 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 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') 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(((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, 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 -- 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_DT = CONVERT(DATE,@p_REQ_DT,103), 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,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),@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 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 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 --IF(CONVERT(DATE, @TO_DATE,103) 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,0),@REQ_PAY_TYPE varchar(1), @ACC_NO VARCHAR(50), @ACC_NAME NVARCHAR(50), @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,0),REQ_PAY_TYPE varchar(1), REQ_PAY_DESC nvarchar(MAX),REQ_PAY_ENTRIES nvarchar(MAX),ACC_NO VARCHAR(50), ACC_NAME NVARCHAR(50), 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,0),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 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 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,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' 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) 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 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' 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 3 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 --DECLARE @AMT_AD_PAY DECIMAL(18,0) =0 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--- 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 ='')) BEGIN ROLLBACK TRANSACTION 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 ) 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 -- 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, 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, 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 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,@p_REQ_DT,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,@p_RECORD_STATUS, @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,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),@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 --IF(CONVERT(DATE, @TO_DATE,103) 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,0),@REQ_PAY_TYPE varchar(1), @ACC_NO VARCHAR(50), @ACC_NAME NVARCHAR(50), @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,0),REQ_PAY_TYPE varchar(1), REQ_PAY_DESC nvarchar(MAX),REQ_PAY_ENTRIES nvarchar(MAX),ACC_NO VARCHAR(50), ACC_NAME NVARCHAR(50), 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,0),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 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 --- 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' 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) 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' 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 4 ALTER PROCEDURE [dbo].[TR_CONTRACT_PAYMENT_ById] @CONTRACT_ID varchar(15) AS IF((SELECT CONT_TYPE FROM TR_CONTRACT WHERE CONTRACT_ID =@CONTRACT_ID) ='DK') BEGIN SELECT '' AS PAY_ID,@CONTRACT_ID AS CONTRACT_ID,B.PAY_PHASE AS PAY_PHASE, B.PAY_DT_REAL AS EXPECTED_DT,100.0 AS [PERCENT], B.AMT_PAY AS AMOUNT,'' AS PAY_STATUS,B.REASON AS NOTES,'' AS MAKER_ID,B.PAY_DT_REAL AS CREATE_DT,'A' AS AUTH_STATUS,'' AS CHECKER_ID,B.PAY_DT_REAL AS APPROVE_DT,B.PAY_DT_REAL AS PAY_DT, B.AMT_PAY AS PAY_AMOUNT,'' AS CONTRACT_CODE, '' AS REQUEST_ID,'' AS CONTRACT_PARENT, B.AMT_PAY AS AMT_ADVANCE, 0.0 AS AMT_PAY_DO, CASE WHEN B.PROCESS ='2' THEN 'TTX' ELSE 'DTU' END AS CDVAL,'' AS CONTENT, 0.0 AS AMT_LINK, 0.0 AS REMAIN_AMT FROM TR_REQ_PAY_PERIOD B WHERE B.PAY_TYPE ='ADV_PAY' AND B.AUTH_STATUS_KT ='A' AND B.CONTRACT_ID =@CONTRACT_ID UNION SELECT '' AS PAY_ID,@CONTRACT_ID AS CONTRACT_ID,B.PAY_PHASE AS PAY_PHASE, B.PAY_DT_REAL AS EXPECTED_DT,100 AS [PERCENT], B.AMT_PAY AS AMOUNT,'' AS PAY_STATUS,'' AS NOTES,'' AS MAKER_ID,B.PAY_DT_REAL AS CREATE_DT,'A' AS AUTH_STATUS,'' AS CHECKER_ID,B.PAY_DT_REAL AS APPROVE_DT,B.PAY_DT_REAL AS PAY_DT, B.AMT_PAY AS PAY_AMOUNT,'' AS CONTRACT_CODE, '' AS REQUEST_ID,'' AS CONTRACT_PARENT, 0.0 AS AMT_ADVANCE, B.AMT_PAY AS AMT_PAY_DO, 'TTX' CDVAL,'' AS CONTENT, 0.0 AS AMT_LINK, 0.0 AS REMAIN_AMT FROM TR_REQ_PAY_PERIOD B WHERE B.PAY_TYPE ='PAY' AND B.PROCESS ='1' AND B.AUTH_STATUS_KT ='A' AND B.CONTRACT_ID =@CONTRACT_ID END ELSE BEGIN SELECT A.*, ISNULL(B.AMT_ADVANCE,0) AS AMT_ADVANCE, ISNULL(B2.AMT_PAY_DO,0) AS AMT_PAY_DO , ISNULL(C.CDVAL,(CASE WHEN ISNULL(B.AMT_ADVANCE,0) >0 AND ISNULL(B2.AMT_PAY_DO,0) =0 THEN 'DTU' WHEN ISNULL(B2.AMT_PAY_DO,0) >0 AND ISNULL((A.AMOUNT - ISNULL(B2.AMT_PAY_DO,0) - ISNULL(B.AMT_ADVANCE,0)-ISNULL(B3.AMT_LINK,0)),0) >0 THEN 'DTT' WHEN ISNULL((A.AMOUNT - ISNULL(B2.AMT_PAY_DO,0) - ISNULL(B.AMT_ADVANCE,0)-ISNULL(B3.AMT_LINK,0)),0) =0 THEN 'TTX' ELSE 'CTT' END)) AS CDVAL, C.CONTENT, ISNULL(B3.AMT_LINK,0) AS AMT_LINK, ISNULL((A.AMOUNT - ISNULL(B2.AMT_PAY_DO,0) - ISNULL(B.AMT_ADVANCE,0) - ISNULL(B3.AMT_LINK,0)),0) AS REMAIN_AMT FROM TR_CONTRACT_PAYMENT A --- TINH SO TIEN DA TAM UNG LEFT JOIN ( SELECT P.PAY_ID , SUM(P.AMT_PAY_REAL) AMT_ADVANCE, MAX(P.AMT_PAY_DO) AMT_PAY_DO, MAX(P.AMT_PAY) AMT_PAY, MAX(P.PAYMENT_STATUS) PAYMENT_STATUS FROM TR_REQ_PAY_SCHEDULE P WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE='ADV_PAY' GROUP BY P.PAY_ID ) B ON A.PAY_ID = B.PAY_ID LEFT JOIN ( SELECT P.PAY_ID , ISNULL(MAX(P.AMT_ADVANCE),0) AMT_ADVANCE, ISNULL(SUM(P.AMT_PAY_REAL),0) AMT_PAY_DO, ISNULL(MAX(P.AMT_PAY),0) AMT_PAY, MAX(P.PAYMENT_STATUS) PAYMENT_STATUS FROM TR_REQ_PAY_SCHEDULE P WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE='PAY' GROUP BY P.PAY_ID ) B2 ON A.PAY_ID = B2.PAY_ID -- LEFT JOIN DE LAY SO TIEN LINK TOI PHIEU TAM UNG LEFT JOIN ( SELECT P.PAY_ID , ISNULL(MAX(P.AMT_REMAIN),0) AS AMT_LINK, SUM((P.AMT_PAY_REAL)) AMT_PAY_DO, ISNULL(MAX(P.AMT_PAY),0) AMT_PAY, MAX(P.PAYMENT_STATUS) PAYMENT_STATUS FROM TR_REQ_PAY_SCHEDULE P WHERE P.AUTH_STATUS_KT='A' AND TRN_TYPE='PAY' AND P.REQ_ADV_ID IS NOT NULL AND P.REQ_ADV_ID <>'' GROUP BY P.PAY_ID ) B3 ON A.PAY_ID = B3.PAY_ID LEFT JOIN CM_ALLCODE C ON C.CDVAL = B.PAYMENT_STATUS AND C.CDNAME = 'PAYMENT_STATUS' WHERE A.CONTRACT_ID= @CONTRACT_ID END 6 ALTER PROC [dbo].[TR_REQ_PAY_DETAIL] @p_REQ_PAY_ID VARCHAR(15) = NULL AS BEGIN DECLARE @TABLE_RETURN_01 TABLE ([NAME] NVARCHAR(250)) DECLARE @TABLE_RETURN_01_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250)) DECLARE @TABLE_RETURN_02 TABLE ([NAME] NVARCHAR(250)) DECLARE @TABLE_RETURN_02_DT TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250)) DECLARE @TABLE_RETURN_03 TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250), AMT DECIMAL(18,0)) DECLARE @TABLE_RETURN_04 TABLE (IND INT IDENTITY(1,1) NOT NULL,[NAME] NVARCHAR(250), AMT DECIMAL(18,0)) DECLARE @TABLE_RETURN_06 TABLE ([NAME] NVARCHAR(250), AMT DECIMAL(18,0)) DECLARE @INDEX INT =1 DECLARE @INDEX_TT INT =0 IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1')) BEGIN SET @INDEX_TT = @INDEX_TT+1 --INSERT INTO @TABLE_RETURN --SELECT N'Tiền mặt' AS PHUONG_THUC, ISNULL(SUM (TOTAL_AMT),0) AS T1 FROM TR_REQ_PAY_METHOD --WHERE REQ_PAY_TYPE ='1' AND REQ_PAY_ID = @p_REQ_PAY_ID INSERT INTO @TABLE_RETURN_03 SELECT N'. Người nhận: '+ ACC_NAME + CHAR(10) + N' CMND số: ' + ACC_NO + char(10) + N' Được cấp bởi: '+ ISSUED_BY + N' ngày cấp: '+ CONVERT(VARCHAR,ISSUED_DT,103) AS ACC_NAME, SUM(TOTAL_AMT) AS T2 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1' GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT -- INSERT BANG TAM THE HIEN SO TIEN INSERT INTO @TABLE_RETURN_04 SELECT 'N'+ CHAR(10) + 'D'+char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T2 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='1' GROUP BY ACC_NO, ACC_NAME, ISSUED_BY, ISSUED_DT END IF(EXISTS(SELECT * FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='0')) BEGIN SET @INDEX_TT = @INDEX_TT+1 --INSERT INTO @TABLE_RETURN --SELECT N'Chuyển khoản' AS PHUONG_THUC,ISNULL(SUM (TOTAL_AMT),0) AS T3 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_TYPE ='0' AND REQ_PAY_ID = @p_REQ_PAY_ID INSERT INTO @TABLE_RETURN_03 SELECT N'. Đơn vị thụ hưởng: '+ ACC_NAME + CHAR(10)+ N' Số tài khoản: ' + ACC_NO + char(10) + N' Tại ngân hàng: '+ ISSUED_BY AS ACC_NAME, SUM(TOTAL_AMT) AS T4 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='0' GROUP BY ACC_NO, ACC_NAME, ISSUED_BY -- INSERT BANG TAM THE HIEN SO TIEN INSERT INTO @TABLE_RETURN_04 SELECT 'N'+ CHAR(10)+ 'S'+ char(10) AS ACC_NAME, SUM(TOTAL_AMT) AS T4 FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND REQ_PAY_TYPE ='0' GROUP BY ACC_NO, ACC_NAME, ISSUED_BY END -- KIEM TRA NOI DUNG SU DUNG CHI PHI VA NGAN SACH IF(EXISTS(SELECT * FROM TR_REQ_PAY_BUDGET WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) BEGIN --INSERT INTO @TABLE_RETURN_01 VALUES (CONVERT(VARCHAR(15),@INDEX) +N'. Nội dung tạm ứng: ') INSERT INTO @TABLE_RETURN_01 VALUES( N'A - Nội dung thanh toán: ') INSERT INTO @TABLE_RETURN_01_DT SELECT N'. '+ A.REQ_PAY_REASON FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID INSERT INTO @TABLE_RETURN_06 SELECT 'A', A.TOTAL_AMT FROM TR_REQ_PAY_METHOD A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID END --- HĐ & CHỨNG TỪ ĐÍNH KÈM IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) BEGIN SET @INDEX = @INDEX+1 --INSERT INTO @TABLE_RETURN_01 SELECT CONVERT(VARCHAR(15),@INDEX)+ N'. Chứng từ đính kèm: ' INSERT INTO @TABLE_RETURN_02 SELECT N'B - Chứng từ đính kèm: ' END IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='C')) BEGIN --INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin hợp đồng đính kèm: ' INSERT INTO @TABLE_RETURN_02_DT SELECT ' '+ N'. Hợp đồng số ' + REF_CODE + ISNULL(N' ngày '+ CONVERT(VARCHAR,REF_DT,101),'')+ N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') AS T1 FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='C' END IF(EXISTS(SELECT * FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID AND TYPE ='R')) BEGIN --INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: ' INSERT INTO @TABLE_RETURN_02_DT SELECT N'. '+ [DESCRIPTION]+ N', số tiền '+ FORMAT( ISNULL(AMT,0),'#,#', 'vi-VN') AS T1 FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND [TYPE] ='R' END IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID =@p_REQ_PAY_ID)) BEGIN --INSERT INTO @TABLE_RETURN_01 SELECT N'Thông tin chứng từ khác đính kèm: ' INSERT INTO @TABLE_RETURN_02_DT SELECT N'. Số hóa đơn '+ INVOICE_NO +ISNULL(N' ngày '+ CONVERT(VARCHAR,INVOICE_DT,101),'')+ N', số tiền '+ FORMAT( ISNULL(PRICE+VAT,0),'#,#', 'vi-VN') AS T1 FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID = @p_REQ_PAY_ID AND TYPE_FUNC='HC' END SELECT * FROM @TABLE_RETURN_01 SELECT * FROM @TABLE_RETURN_01_DT SELECT * FROM @TABLE_RETURN_02 SELECT * FROM @TABLE_RETURN_02_DT SELECT * FROM @TABLE_RETURN_03 SELECT * FROM @TABLE_RETURN_04 SELECT * FROM @TABLE_RETURN_06 END 7 ALTER PROC [dbo].[TR_REQ_PAY_BY_REF] @p_REF_ID VARCHAR(15) = NULL, @p_TYPE VARCHAR(15) = NULL AS BEGIN SELECT B.REQ_PAY_CODE, B.MAKER_ID,A.REQ_PAY_DESC AS REQ_REASON,A.AMT_PAY_REAL AS REQ_AMT,B.PAY_AMT,B.REQ_DT,B.REQ_PAY_ID,B.CHECKER_ID,B.MAKER_ID_KT,B.CHECKER_ID_KT,B.APPROVE_DT_KT, A.PAY_PHASE AS REQ_TYPE_NAME,C.REF_TYPE,C.[TYPE] FROM TR_REQ_PAY_SCHEDULE A INNER JOIN TR_REQ_ADVANCE_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID INNER JOIN TR_REQ_ADVANCE_DT C ON A.REF_ID = C.REF_ID AND C.TYPE ='ADV_PAY' WHERE A.REF_ID =@p_REF_ID UNION SELECT B.REQ_PAY_CODE, B.MAKER_ID,A.REASON AS REQ_REASON,A.AMT_PAY AS REQ_AMT,B.PAY_AMT,B.REQ_DT,B.REQ_PAY_ID,B.CHECKER_ID,B.MAKER_ID_KT,B.CHECKER_ID_KT,B.APPROVE_DT_KT, A.PAY_PHASE AS REQ_TYPE_NAME,C.REF_TYPE,C.[TYPE] FROM TR_REQ_PAY_PERIOD A INNER JOIN TR_REQ_ADVANCE_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID INNER JOIN TR_REQ_ADVANCE_DT C ON A.CONTRACT_ID = C.REF_ID AND C.TYPE ='ADV_PAY' WHERE A.CONTRACT_ID =@p_REF_ID UNION SELECT B.REQ_PAY_CODE, B.MAKER_ID,A.REQ_PAY_DESC AS REQ_REASON,A.AMT_PAY_REAL AS REQ_AMT,0.0 AS PAY_AMT,B.REQ_DT,B.REQ_PAY_ID,B.CHECKER_ID,B.MAKER_ID_KT,B.CHECKER_ID_KT,B.APPROVE_DT_KT, A.PAY_PHASE AS REQ_TYPE_NAME,C.REF_TYPE,C.[TYPE] FROM TR_REQ_PAY_SCHEDULE A INNER JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID INNER JOIN TR_REQ_ADVANCE_DT C ON A.REF_ID = C.REF_ID AND C.TYPE ='PAY' WHERE A.REF_ID =@p_REF_ID AND A.PROCESS =1 UNION SELECT B.REQ_PAY_CODE, B.MAKER_ID,A.REASON AS REQ_REASON,A.AMT_PAY AS REQ_AMT,0.0 AS PAY_AMT,B.REQ_DT,B.REQ_PAY_ID,B.CHECKER_ID,B.MAKER_ID_KT,B.CHECKER_ID_KT,B.APPROVE_DT_KT, A.PAY_PHASE AS REQ_TYPE_NAME,C.REF_TYPE,C.[TYPE] FROM TR_REQ_PAY_PERIOD A INNER JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID INNER JOIN TR_REQ_ADVANCE_DT C ON A.CONTRACT_ID = C.REF_ID AND C.TYPE ='PAY' WHERE A.CONTRACT_ID =@p_REF_ID AND A.PROCESS =1 --LEFT JOIN --( -- SELECT P.REQ_PAY_ID, P.REF_ID, SUM(AMT_PAY_REAL) AS ST_THANH_TOAN FROM TR_REQ_PAY_SCHEDULE P -- GROUP BY P.REQ_PAY_ID, P.REF_ID --) X ON A.REQ_PAY_ID = X.REQ_PAY_ID AND A.REF_ID = X.REF_ID --LEFT JOIN --( -- SELECT P.REQ_PAY_ID, P.CONTRACT_ID, SUM(AMT_PAY) AS ST_DK FROM TR_REQ_PAY_PERIOD P -- GROUP BY P.REQ_PAY_ID, P.CONTRACT_ID --) X2 ON A.REQ_PAY_ID = X2.REQ_PAY_ID AND A.REF_ID = X2.CONTRACT_ID --WHERE A.CONTRACT_ID =@p_REF_ID --UNION --SELECT B.REQ_PAY_CODE, B.MAKER_ID, B.REQ_REASON, ISNULL((ISNULL(X.ST_TAM_UNG,0.0) + ISNULL(X2.ST_DK,0.0)),0.0) AS REQ_AMT,0 AS PAY_AMT,B.REQ_DT,B.REQ_PAY_ID, B.CHECKER_ID,B.MAKER_ID_KT,B.CHECKER_ID_KT,B.APPROVE_DT_KT,'' AS REQ_TYPE_NAME,A.REF_TYPE,A.[TYPE] FROM TR_REQ_ADVANCE_DT A --INNER JOIN TR_REQ_PAYMENT B ON A.REQ_PAY_ID = B.REQ_PAY_ID --LEFT JOIN --( -- SELECT P.REQ_PAY_ID, P.REF_ID, SUM(AMT_PAY_REAL) AS ST_TAM_UNG FROM TR_REQ_PAY_SCHEDULE P -- GROUP BY P.REQ_PAY_ID, P.REF_ID --) X ON A.REQ_PAY_ID = X.REQ_PAY_ID AND A.REF_ID = X.REF_ID --LEFT JOIN --( -- SELECT P.REQ_PAY_ID, P.CONTRACT_ID, SUM(AMT_PAY) AS ST_DK FROM TR_REQ_PAY_PERIOD P -- GROUP BY P.REQ_PAY_ID, P.CONTRACT_ID --) X2 ON A.REQ_PAY_ID = X2.REQ_PAY_ID AND A.REF_ID = X2.CONTRACT_ID --WHERE A.REF_ID =@p_REF_ID END 8 ALTER PROCEDURE [dbo].[TR_REQ_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_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_RATE DECIMAL(18,2) = 0, @p_IS_PERIOD VARCHAR(5) = NULL, @p_XMP_TEMP XML = NULL, @p_XMP_TEMP_2 XML = NULL, @p_TYPE_FUNCTION VARCHAR(15) = 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 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 phép để trống' ErrorDesc RETURN '-1' END IF EXISTS(SELECT * FROM TR_REQ_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 BEGIN TRANSACTION DECLARE @VAT decimal(18, 0) =NULL, @TRANS_NO nvarchar(50)=NULL, @TRANS_DT VARCHAR(20)=NULL,@INVOICE_SIGN nvarchar(50) = NULL,@INVOICE_NO_SIGN nvarchar(50) = NULL,@INVOICE_NO nvarchar(50) = NULL,@INVOICE_DT VARCHAR(20) = NULL,@SELLER nvarchar(50) = NULL,@TAX_NO nvarchar(15) = NULL,@GOODS_NAME nvarchar(500) = NULL, @PRICE decimal(18, 0) = NULL,@TAX decimal(18, 0) = NULL,@NOTES NVARCHAR(MAX) = NULL,@VAT_RATE DECIMAL(18,0),@ACC_NO VARCHAR(25),@ACC_NAME NVARCHAR(250),@ISSUED_BY NVARCHAR(250), @ISSUED_DT VARCHAR(20),@CURRENCY VARCHAR(15)= NULL,@RATE DECIMAL(18,2), @TYPE_VAT VARCHAR(15),@TYPE_FUNC VARCHAR(15) DECLARE @PAY_ADV_ID VARCHAR(15),@TYPE_TRANS VARCHAR(15),@REQ_PAY_ADV_CODE VARCHAR(15),@REASON NVARCHAR(1000),@REF_TYPE VARCHAR(15) DECLARE @AMT_ADVANCED DECIMAL(18,0),@AMT_DO DECIMAL(18,0), @AMT_REMAIN DECIMAL(18,0),@AMT_PAY DECIMAL(18,0),@AMT_USE DECIMAL(18,0),@AMT_REVERT DECIMAL(18,2), @AMT_ADD DECIMAL(18,2) DECLARE @TOTAL_SCHEDULE_AMT DECIMAL(18,0) =0 DECLARE @INDEX_AD INT =0, @INDEX_SV INT =0,@INDEX INT =0, @INDEX_IV INT =0, @INDEX_NS INT =0 DECLARE @hdoc INT, @hDoc2 INT EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP; EXEC sp_xml_preparedocument @hDoc2 OUTPUT, @p_XMP_TEMP_2; DECLARE XmlData CURSOR FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlData', 2) WITH(TRANS_NO nvarchar(50),TRANS_DT VARCHAR(20),INVOICE_SIGN nvarchar(50),INVOICE_NO_SIGN nvarchar(50), INVOICE_NO nvarchar(50),INVOICE_DT VARCHAR(20) ,SELLER nvarchar(50),TAX_NO nvarchar(15),GOODS_NAME nvarchar(500) , PRICE decimal(18,2),TAX decimal(18, 2),VAT decimal(18,2),NOTE NVARCHAR(MAX),VAT_RATE decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),TYPE_VAT VARCHAR(15),TYPE_FUNC VARCHAR(15)) OPEN XmlData; --CURSOR 2 DECLARE XmlDataPay CURSOR FOR SELECT * FROM OPENXML(@hDoc2, '/Root/XmlDataPay', 2) WITH(PAY_ADV_ID nvarchar(50),AMT_ADVANCED decimal(18, 0),AMT_DO decimal(18,2), AMT_REMAIN decimal(18,2),AMT_PAY decimal(18,2),AMT_USE decimal(18,2),AMT_REVERT DECIMAL(18,0),AMT_ADD DECIMAL(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2)) OPEN XmlDataPay; ------------------------ --Luanlt--- --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),@REQ_PAY_DESC nvarchar(MAX),@REQ_PAY_ENTRIES nvarchar(MAX),@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(25), ACC_NAME NVARCHAR(250),ISSUED_BY NVARCHAR(250), ISSUED_DT VARCHAR(20),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),CHECK_IN VARCHAR(15),TYPE_TRANS VARCHAR(15)) OPEN XmlDataMethod ---------- --CatCursor DECLARE @REQ_ADV_ID varchar(15),@CAT_NAME nvarchar(100),@TOTAL_AMT_CAT decimal(18,2), @DEPT_ID VARCHAR(15) DECLARE XmlDataCat CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataCat',2) WITH(REQ_ADV_ID VARCHAR(15),CAT_NAME nvarchar(100),TOTAL_AMT decimal(18,2), DEPT_ID VARCHAR(15),CURRENCY VARCHAR(15), RATE DECIMAL(18,2)) OPEN XmlDataCat --------------- --ServiceCursor DECLARE @REQ_PAY_SERVICE_NAME nvarchar(100),@RECEIVE_ID_SERVICE varchar(15),@RECEIVE_NAME_SERVICE nvarchar(100),@REQ_PAY_REASON_SERVICE nvarchar(MAX), @TOTAL_AMT_SERVICE decimal(18,2), @REQ_PAY_TYPE_SERVICE varchar(1),@REQ_PAY_DESC_SERVICE nvarchar(MAX),@REQ_PAY_ENTRIES_SERVICE nvarchar(MAX),@DEPT_ID_SRV VARCHAR(15) DECLARE XmlDataService CURSOR FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataService',2) WITH(REQ_PAY_SERVICE_NAME nvarchar(100),RECEIVE_ID_SERVICE varchar(15),RECEIVE_NAME_SERVICE nvarchar(100),REQ_PAY_REASON_SERVICE nvarchar(MAX), TOTAL_AMT_SERVICE decimal(18,2),REQ_PAY_TYPE_SERVICE varchar(1),REQ_PAY_DESC_SERVICE nvarchar(MAX),REQ_PAY_ENTRIES_SERVICE nvarchar(MAX), DEPT_ID VARCHAR(15),CURRENCY VARCHAR(15), RATE DECIMAL(18,2)) OPEN XmlDataService --------------- --ScheduleCursor DECLARE @PO_ID varchar(15),@TERM_PAY varchar(15),@AMT_PAY_SCHEDULE decimal(18,2),@REQ_ADV_ID_SCHE VARCHAR(15),@AMT_ADVANCE_SCHEDULE decimal(18,2), @AMT_REMAIN_SCHEDULE decimal(18,2),@REQ_AD_DT varchar(20),@PROCESS varchar(15),@PAY_ID VARCHAR(15),@AMT_PAY_DO DECIMAL(18,2),@AMT_PAY_REAL DECIMAL(18,2) 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,2),REQ_ADV_ID varchar(15),AMT_ADVANCE_SCHEDULE decimal(18,2), AMT_REMAIN_SCHEDULE decimal(18,2),REQ_AD_DT varchar(20),PROCESS varchar(15), PAY_ID VARCHAR(15),REF_ID VARCHAR(15),REQ_PAY_DESC NVARCHAR(250), REQ_PAY_ENTRIES NVARCHAR(250),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),AMT_PAY_DO DECIMAL(18,2),AMT_PAY_REAL DECIMAL(18,2)) OPEN XmlDataSchedule --END luanlt--- -- DS HANG MUC NGAN SACH DECLARE @TRADE_ID varchar(15),@GD_ID varchar(15),@AMT_APP 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 ---- END CURSOR HANG MUC NGAN SACH -- DINH KEM CHUNG TU DINH KEM 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) 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 -- END UPDATE TR_REQ_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,REQ_DT =CONVERT(DATE,@p_REQ_DT,103), NOTES= @p_NOTES, REQ_TYPE= @p_REQ_TYPE,REQ_DESCRIPTION = @p_REQ_DESCRIPTION,RECEIVER_PO = @p_RECEIVER_PO,TRANSFER_MAKER =@p_TRANSFER_MAKER, REQ_TEMP_AMT=@p_REQ_TEMP_AMT,REQ_ENTRIES = @P_REQ_ENTRIES,BRANCH_ID = @p_BRANCH_ID,REQ_PAY_CODE = @p_REQ_PAY_CODE, AUTH_STATUS='E', IS_PERIOD =@p_IS_PERIOD, TRASFER_USER_RECIVE =@p_TRASFER_USER_RECIVE WHERE REQ_PAY_ID =@p_REQ_PAY_ID IF @@Error <> 0 GOTO ABORT --Insert XmlData DELETE FROM TR_REQ_PAY_INVOICE WHERE REQ_PAY_ID = @p_REQ_PAY_ID FETCH NEXT FROM XmlData INTO @TRANS_NO , @TRANS_DT ,@INVOICE_SIGN ,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER , @TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@TYPE_VAT,@TYPE_FUNC WHILE @@fetch_status=0 BEGIN SET @INDEX_IV = @INDEX_IV +1 -- KHI NAO BAM GUI PHE DUYET HE THONG MOI CHECK VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN IF(LEN(@INVOICE_NO) >7) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' không được vượt quá 7 kí tự' ErrorDesc RETURN '-1' END IF(LEN(@INVOICE_NO) <7) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' phải đủ 7 kí tự' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE INVOICE_NO =@INVOICE_NO AND INVOICE_NO_SIGN = @INVOICE_NO_SIGN AND INVOICE_SIGN =@INVOICE_SIGN AND TAX_NO =@TAX_NO AND TAX =@TAX)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc RETURN '-1' END END --SET @INDEX = @INDEX +1 DECLARE @p_REQ_INV_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_INVOICE', @p_REQ_INV_ID OUT; IF @p_REQ_INV_ID='' OR @p_REQ_INV_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_INVOICE(REQ_PAYDT_ID,REQ_PAY_ID,TRANS_NO,TRANS_DT,INVOICE_SIGN,INVOICE_NO,INVOICE_DT,SELLER,TAX_NO,GOODS_NAME,PRICE,TAX,VAT,NOTE, 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,RECORD_STATUS,INVOICE_NO_SIGN,VAT_RATE,CURRENCY,RATE,PRICE_KT,VAT_KT,TOTAL_AMT_KT,TYPE_VAT,TYPE_FUNC) VALUES (@p_REQ_INV_ID,@p_REQ_PAY_ID , @TRANS_NO , NULL ,@INVOICE_SIGN ,@INVOICE_NO ,CONVERT(DATE,@INVOICE_DT,103) ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES, @p_MAKER_ID,GETDATE(),NULL,'U',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',@INVOICE_NO_SIGN,@VAT_RATE,@CURRENCY,@RATE,@PRICE,@VAT,@PRICE+@VAT,@TYPE_VAT,@TYPE_FUNC) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlData INTO @TRANS_NO , @TRANS_DT,@INVOICE_SIGN,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@TYPE_VAT,@TYPE_FUNC END; CLOSE XmlData; DEALLOCATE XmlData; --- INSERT CAC HANG MUC NGAN SACH DELETE FROM TR_REQ_PAY_BUDGET WHERE REQ_PAY_ID=@p_REQ_PAY_ID --- INSERT CAC HANG MUC NGAN SACH 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 HE THONG 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 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; --INSERT FROM MethodCursor DELETE FROM TR_REQ_PAY_METHOD WHERE REQ_PAY_ID = @p_REQ_PAY_ID SET @INDEX = 0 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,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANS WHILE @@fetch_status=0 BEGIN IF(@REQ_PAY_TYPE<>'1') BEGIN SET @ISSUED_DT = NULL END SET @INDEX = @INDEX +1 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 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,@ISSUED_BY,CONVERT(DATE,@ISSUED_DT,103),@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANS) 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,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANS END CLOSE XmlDataMethod; DEALLOCATE XmlDataMethod; --- END INSERT NGAN SACH --- INSERT VAO BANG CHUNG TU DINH KEM -- INSERT CHUNG TU DINH KEM DELETE FROM TR_REQ_PAY_ATTACH WHERE REQ_PAY_ID =@p_REQ_PAY_ID --OPEN XmlAttach; FETCH NEXT FROM XmlAttach INTO @ATTACH_ID,@CON_ID,@CONT_CODE,@NOTES,@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,@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,@CON_DESC,@CON_TYPE,@AMT_ATT,@REF_DT END CLOSE XmlAttach; DEALLOCATE XmlAttach; ----END ------------------------ IF(@p_REQ_TYPE = 'I') BEGIN --BEGIN CURRSOR 2 DELETE FROM TR_REQ_PAYMENT_DT WHERE PAY_ID = @p_REQ_PAY_ID FETCH NEXT FROM XmlDataPay INTO @PAY_ADV_ID, @AMT_ADVANCED ,@AMT_DO,@AMT_REMAIN,@AMT_PAY ,@AMT_USE ,@AMT_REVERT,@AMT_ADD,@CURRENCY,@RATE WHILE @@fetch_status=0 BEGIN SET @INDEX_AD = @INDEX_AD +1 SET @REQ_PAY_ADV_CODE = (SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@PAY_ADV_ID) SET @INDEX_AD = @INDEX_AD +1 -- KHI NAO BAM GUI PHE DUYET HE THONG MOI CHECK VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN -- KIEM TRA XEM CO PHIEU NAO DANG DUOC THANH TOAN HOAN TAM UNG MA CHUA DUYET HAY CHUA IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_DT WHERE (PAY_ADV_ID =@PAY_ADV_ID AND (AUTH_STATUS_KT <>'A' OR AUTH_STATUS_KT IS NULL)) AND PAY_ID <> @p_REQ_PAY_ID)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_AD)+ N': Phiếu tạm ứng số '+@REQ_PAY_ADV_CODE+ N' đang được thanh toán hoàn tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END -- KIEM TRA NEU SO TIEN DE NGHI HOAN TAM ƯNG LON HON SO TIEN CON LAI CAN PHAI TAM UNG IF(@AMT_REVERT>(@AMT_REMAIN -@AMT_USE) AND @AMT_REVERT >0) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_AD)+ N': Số tiền hoàn ứng không được vượt quá '+FORMAT((@AMT_REMAIN -@AMT_USE),'#,#', 'vi-VN') ErrorDesc RETURN '-1' END END DECLARE @p_REQ_PAYDT_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAYMENT_DT', @p_REQ_PAYDT_ID OUT; IF @p_REQ_PAYDT_ID='' OR @p_REQ_PAYDT_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAYMENT_DT VALUES (@p_REQ_PAYDT_ID,@PAY_ADV_ID,@p_REQ_PAY_ID , ISNULL(@AMT_ADVANCED,0) ,ISNULL(@AMT_DO,0),ISNULL(@AMT_REMAIN,0), ISNULL(@AMT_PAY,0) ,ISNULL(@AMT_USE,0) ,ISNULL(@AMT_REVERT,0), ISNULL(@AMT_ADD,0), GETDATE() ,@p_MAKER_ID ,GETDATE() ,NULL ,NULL,'U' ,NULL ,NULL ,NULL ,NULL,NULL,@CURRENCY,@RATE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataPay INTO @PAY_ADV_ID, @AMT_ADVANCED ,@AMT_DO,@AMT_REMAIN,@AMT_PAY ,@AMT_USE ,@AMT_REVERT,@AMT_ADD,@CURRENCY,@RATE END CLOSE XmlDataPay; DEALLOCATE XmlDataPay; --END CURSOR 2-------------------------- ------------------------- --INSERT FROM CatCursor DELETE FROM TR_REQ_PAY_CAT WHERE REQ_PAY_ID = @p_REQ_PAY_ID FETCH NEXT FROM XmlDataCat INTO @REQ_ADV_ID,@CAT_NAME,@TOTAL_AMT_CAT,@DEPT_ID,@CURRENCY,@RATE WHILE @@fetch_status=0 BEGIN SET @INDEX = @INDEX +1 DECLARE @p_REQ_PAY_CAT_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_CAT', @p_REQ_PAY_CAT_ID OUT; IF @p_REQ_PAY_CAT_ID='' OR @p_REQ_PAY_CAT_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_CAT VALUES (@p_REQ_PAY_CAT_ID,@p_REQ_PAY_ID,@REQ_ADV_ID,@CAT_NAME,@TOTAL_AMT_CAT,@DEPT_ID,@p_MAKER_ID,GETDATE(),@CURRENCY,@RATE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataCat INTO @REQ_ADV_ID,@CAT_NAME,@TOTAL_AMT_CAT,@DEPT_ID,@CURRENCY,@RATE END CLOSE XmlDataCat; DEALLOCATE XmlDataCat; DELETE FROM TR_REQ_PAY_PERIOD WHERE REQ_PAY_ID = @p_REQ_PAY_ID 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), AD_PAY_ID VARCHAR(15), PROCESS VARCHAR(5),PARENT_ID VARCHAR(15),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),@AD_PAY_ID VARCHAR(15), @_PROCESS VARCHAR(15),@PARENT_ID VARCHAR(15),@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,@AD_PAY_ID, @_PROCESS,@PARENT_ID,@PAY_PHASE,@REASON_TTDK WHILE @@fetch_status=0 BEGIN --IF(CONVERT(DATE, @TO_DATE,103) 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,PARENT_ID,PAY_PHASE,REASON) VALUES (@PERIOD_ID,@p_REQ_PAY_ID,'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),@AD_PAY_ID, @_PROCESS,@PARENT_ID,@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,@AD_PAY_ID, @_PROCESS,@PARENT_ID,@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 IF(@p_REQ_TYPE = 'D' OR @p_REQ_TYPE ='I') BEGIN ---------------------------- DELETE FROM TR_REQ_PAY_SERVICE WHERE REQ_PAY_ID = @p_REQ_PAY_ID --INSERT FROM ServiceCursor SET @INDEX = 0 FETCH NEXT FROM XmlDataService INTO @REQ_PAY_SERVICE_NAME,@RECEIVE_ID_SERVICE,@RECEIVE_NAME_SERVICE,@REQ_PAY_REASON_SERVICE,@TOTAL_AMT_SERVICE, @REQ_PAY_TYPE_SERVICE,@REQ_PAY_DESC_SERVICE,@REQ_PAY_ENTRIES_SERVICE,@DEPT_ID_SRV,@CURRENCY,@RATE WHILE @@fetch_status=0 BEGIN SET @INDEX = @INDEX +1 DECLARE @p_REQ_PAY_SERVICE_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_SERVICE', @p_REQ_PAY_SERVICE_ID OUT; IF @p_REQ_PAY_SERVICE_ID='' OR @p_REQ_PAY_SERVICE_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_SERVICE(SERVICE_ID,REQ_PAY_ID,SERVICE_NAME,EMP_ID,REQ_PAY_REASON,TOTAL_AMT,REQ_PAY_TYPE,REQ_PAY_DESC,REQ_PAY_ENTRIES,MAKER_ID,CREATE_DT,DEPT_ID,CURRENCY,RATE) VALUES (@p_REQ_PAY_SERVICE_ID,@p_REQ_PAY_ID,@REQ_PAY_SERVICE_NAME,@RECEIVE_ID_SERVICE,@REQ_PAY_REASON_SERVICE,@TOTAL_AMT_SERVICE, @REQ_PAY_TYPE_SERVICE,@REQ_PAY_DESC_SERVICE,@REQ_PAY_ENTRIES_SERVICE,@p_MAKER_ID,GETDATE(),@DEPT_ID_SRV,@CURRENCY,@RATE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataService INTO @REQ_PAY_SERVICE_NAME,@RECEIVE_ID_SERVICE,@RECEIVE_NAME_SERVICE,@REQ_PAY_REASON_SERVICE,@TOTAL_AMT_SERVICE,@REQ_PAY_TYPE_SERVICE, @REQ_PAY_DESC_SERVICE,@REQ_PAY_ENTRIES_SERVICE,@DEPT_ID_SRV,@CURRENCY,@RATE END CLOSE XmlDataService; DEALLOCATE XmlDataService; END IF((@p_REQ_TYPE = 'P' AND (@p_IS_PERIOD <>'Y' OR @p_IS_PERIOD IS NULL)) OR @p_REQ_TYPE ='I') BEGIN ---------------------------- DELETE FROM TR_REQ_ADVANCE_DT WHERE REQ_PAY_ID= @p_REQ_PAY_ID DECLARE @INDEX_PO INT, @REF_ID VARCHAR(15),@IS_CLOSED VARCHAR(1) 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 -- KHI NAO BAM GUI PHE DUYET HE THONG 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': 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,'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; --INSERT FROM ScheduleCursor DELETE FROM TR_REQ_PAY_SCHEDULE WHERE REQ_PAY_ID = @p_REQ_PAY_ID SET @INDEX_PO = 0 FETCH NEXT FROM XmlDataSchedule INTO @PO_ID,@TERM_PAY,@AMT_PAY_SCHEDULE,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE,@AMT_REMAIN_SCHEDULE,@REQ_AD_DT, @PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_DO,@AMT_PAY_REAL WHILE @@fetch_status=0 BEGIN --IF(@AMT_REMAIN_SCHEDULE =0) --BEGIN -- SET @PROCESS ='3' --END --IF(@PROCESS <>'2') --BEGIN -- SET @TOTAL_SCHEDULE_AMT = @TOTAL_SCHEDULE_AMT +@AMT_REMAIN_SCHEDULE --END SET @TOTAL_SCHEDULE_AMT = @TOTAL_SCHEDULE_AMT +(@AMT_PAY_SCHEDULE -@AMT_ADVANCE_SCHEDULE -@AMT_PAY_DO) ---- SET @INDEX_PO = @INDEX_PO +1 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,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE, --IIF(@PROCESS<>'2',(@AMT_PAY_SCHEDULE-@AMT_ADVANCE_SCHEDULE),0), @AMT_PAY_DO, @AMT_REMAIN_SCHEDULE,GETDATE(),CONVERT(DATETIME,@REQ_AD_DT,103),@PROCESS,@p_MAKER_ID,GETDATE(),'U','','PAY',@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL) 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_REMAIN_SCHEDULE,@REQ_AD_DT, @PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_DO,@AMT_PAY_REAL END CLOSE XmlDataSchedule; DEALLOCATE XmlDataSchedule; ---- VALIDATE SO TIEN --IF(@p_REQ_AMT > ISNULL(@TOTAL_SCHEDULE_AMT,0)) --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 -- NEU LA THANH TOAN CAC HOP DONG DINH KY ELSE IF((@p_REQ_TYPE = 'P' AND @p_IS_PERIOD = 'Y') OR @p_REQ_TYPE='I') 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,'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), AD_PAY_ID VARCHAR(15), PROCESS VARCHAR(5),PARENT_ID VARCHAR(15),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),@AD_PAY_ID VARCHAR(15), @_PROCESS VARCHAR(15),@PARENT_ID VARCHAR(15),@PAY_PHASE NVARCHAR(250) 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,@AD_PAY_ID, @_PROCESS,@PARENT_ID,@PAY_PHASE,@REASON_TTDK WHILE @@fetch_status=0 BEGIN --IF(CONVERT(DATE, @TO_DATE,103) 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,PARENT_ID,PAY_PHASE,REASON) VALUES (@PERIOD_ID,@p_REQ_PAY_ID,'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),@AD_PAY_ID, @_PROCESS,@PARENT_ID,@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,@AD_PAY_ID, @_PROCESS,@PARENT_ID,@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 COMMIT TRANSACTION IF(@p_TYPE_FUNCTION ='SEND') -- KIEM TRA NEU 2 BIEN XML KHAC NULL TUC LA DANG TRONG QUA TRINH LUU NHAP BEGIN -- CAP NHAT TINH TRANG DUYET KE TOAN LA DANG XU LY UPDATE TR_REQ_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') SELECT '4' as Result, '' 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 CLOSE XmlData; DEALLOCATE XmlData; CLOSE XmlDataPay; DEALLOCATE XmlDataPay; Close XmlDataMethod; Close XmlDataCat; CLOSE XmlDataService; CLOSE XmlDataSchedule; Deallocate XmlDataMethod; Deallocate XmlDataCat; DEALLOCATE XmlDataService; DEALLOCATE XmlDataSchedule; ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID, '' ErrorDesc RETURN '-1' End 9 ALTER PROCEDURE [dbo].[TR_REQ_PAYMENT_Ins] @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_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_RATE DECIMAL(18,2) = 0, @p_IS_PERIOD VARCHAR(5) = NULL, @p_TYPE_FUNCTION VARCHAR(15) = NULL, @p_XMP_TEMP XML = NULL, @p_XMP_TEMP_2 XML = NULL AS BEGIN TRANSACTION DECLARE @VAT decimal(18, 0) =NULL, @TRANS_NO nvarchar(50)=NULL, @TRANS_DT VARCHAR(20)=NULL,@INVOICE_SIGN nvarchar(50) = NULL,@INVOICE_NO_SIGN nvarchar(50)=null, @INVOICE_NO nvarchar(50) = NULL,@INVOICE_DT VARCHAR(20) = NULL,@SELLER nvarchar(50) = NULL,@TAX_NO nvarchar(15) = NULL,@GOODS_NAME nvarchar(500) = NULL, @PRICE decimal(18, 0) = NULL,@TAX decimal(18, 0) = NULL,@NOTES NVARCHAR(MAX) = NULL,@VAT_RATE decimal(18,2), @REF_ID VARCHAR(15),@IS_CLOSED VARCHAR(1), @ACC_NO VARCHAR(25),@ACC_NAME NVARCHAR(250),@ISSUED_BY NVARCHAR(250),@ISSUED_DT VARCHAR(20),@CURRENCY VARCHAR(15)= NULL,@RATE DECIMAL(18,2),@TYPE_TRANS VARCHAR(15), @TYPE_VAT VARCHAR(15),@TYPE_FUNC VARCHAR(15),@REASON NVARCHAR(1000),@REF_TYPE VARCHAR(15) DECLARE @PAY_ADV_ID VARCHAR(15), @REQ_PAY_ADV_CODE VARCHAR(15) DECLARE @AMT_ADVANCED decimal(18,2),@AMT_DO decimal(18,2), @AMT_REMAIN decimal(18,2),@AMT_PAY decimal(18,2),@AMT_USE decimal(18,2),@AMT_REVERT DECIMAL(18,2), @AMT_ADD DECIMAL(18,2) DECLARE @TOTAL_SCHEDULE_AMT decimal(18,2) =0 DECLARE @INDEX_PO INT =0, @INDEX_AD INT =0, @INDEX_IV INT=0,@INDEX_NS INT =0 DECLARE @hdoc INT, @hDoc2 INT EXEC sp_xml_preparedocument @hdoc OUTPUT, @p_XMP_TEMP; EXEC sp_xml_preparedocument @hDoc2 OUTPUT, @p_XMP_TEMP_2; DECLARE XmlData CURSOR LOCAL FOR SELECT * FROM OPENXML(@hdoc, '/Root/XmlData', 2) WITH(TRANS_NO nvarchar(50),TRANS_DT VARCHAR(20),INVOICE_SIGN nvarchar(50),INVOICE_NO_SIGN nvarchar(50), INVOICE_NO nvarchar(50),INVOICE_DT VARCHAR(20) ,SELLER nvarchar(50),TAX_NO nvarchar(15),GOODS_NAME nvarchar(500) , PRICE decimal(18,2),TAX decimal(18, 2),VAT decimal(18,2),NOTE NVARCHAR(MAX),VAT_RATE decimal(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),TYPE_VAT VARCHAR(15), TYPE_FUNC VARCHAR(15)) OPEN XmlData; --CURSOR 2 DECLARE XmlDataPay CURSOR LOCAL FOR SELECT * FROM OPENXML(@hDoc2, '/Root/XmlDataPay', 2) WITH(PAY_ADV_ID nvarchar(50),AMT_ADVANCED decimal(18, 0),AMT_DO decimal(18,2), AMT_REMAIN decimal(18,2),AMT_PAY decimal(18,2),AMT_USE decimal(18,2),AMT_REVERT DECIMAL(18,2),AMT_ADD DECIMAL(18,2),CURRENCY VARCHAR(15), RATE DECIMAL(18,2)) OPEN XmlDataPay; ------------------------ --Luanlt--- --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),@REQ_PAY_DESC nvarchar(MAX),@REQ_PAY_ENTRIES nvarchar(MAX),@CHECK_IN VARCHAR(15) DECLARE XmlDataMethod CURSOR LOCAL 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(25), ACC_NAME NVARCHAR(250),ISSUED_BY NVARCHAR(250), ISSUED_DT VARCHAR(20),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),CHECK_IN VARCHAR(15),TYPE_TRANS VARCHAR(15)) OPEN XmlDataMethod ---------- --CatCursor DECLARE @REQ_ADV_ID varchar(15),@CAT_NAME nvarchar(100),@TOTAL_AMT_CAT decimal(18,2), @DEPT_ID VARCHAR(15) DECLARE XmlDataCat CURSOR LOCAL FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataCat',2) WITH(REQ_ADV_ID VARCHAR(15),CAT_NAME nvarchar(100),TOTAL_AMT decimal(18,2), DEPT_ID VARCHAR(15),CURRENCY VARCHAR(15), RATE DECIMAL(18,2)) OPEN XmlDataCat --------------- --ServiceCursor DECLARE @REQ_PAY_SERVICE_NAME nvarchar(100),@RECEIVE_ID_SERVICE varchar(15),@RECEIVE_NAME_SERVICE nvarchar(100),@REQ_PAY_REASON_SERVICE nvarchar(MAX), @TOTAL_AMT_SERVICE decimal(18,2), @REQ_PAY_TYPE_SERVICE varchar(1),@REQ_PAY_DESC_SERVICE nvarchar(MAX),@REQ_PAY_ENTRIES_SERVICE nvarchar(MAX),@DEPT_ID_SRV VARCHAR(15) DECLARE XmlDataService CURSOR LOCAL FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataService',2) WITH(REQ_PAY_SERVICE_NAME nvarchar(100),RECEIVE_ID_SERVICE varchar(15),RECEIVE_NAME_SERVICE nvarchar(100),REQ_PAY_REASON_SERVICE nvarchar(MAX), TOTAL_AMT_SERVICE decimal(18,2),REQ_PAY_TYPE_SERVICE varchar(1),REQ_PAY_DESC_SERVICE nvarchar(MAX),REQ_PAY_ENTRIES_SERVICE nvarchar(MAX), DEPT_ID VARCHAR(15),CURRENCY VARCHAR(15), RATE DECIMAL(18,2)) OPEN XmlDataService --------------- --ScheduleCursor DECLARE @PO_ID varchar(15),@TERM_PAY varchar(15),@AMT_PAY_SCHEDULE decimal(18,2),@REQ_ADV_ID_SCHE VARCHAR(15),@AMT_ADVANCE_SCHEDULE decimal(18,2), @AMT_REMAIN_SCHEDULE decimal(18,2),@REQ_AD_DT varchar(20),@PROCESS varchar(15),@PAY_ID VARCHAR(15),@AMT_PAY_REAL DECIMAL(18,2), @AMT_PAY_DO DECIMAL(18,2) DECLARE XmlDataSchedule CURSOR LOCAL FOR SELECT * FROM OPENXML(@hdoc, 'Root/XmlDataSchedule',2) WITH(PO_ID varchar(15),TERM_PAY varchar(15),AMT_PAY_SCHEDULE decimal(18,2),REQ_ADV_ID varchar(15),AMT_ADVANCE_SCHEDULE decimal(18,2), AMT_REMAIN_SCHEDULE decimal(18,2),REQ_AD_DT varchar(20),PROCESS varchar(15), PAY_ID VARCHAR(15),REF_ID VARCHAR(15),REQ_PAY_DESC NVARCHAR(250), REQ_PAY_ENTRIES NVARCHAR(250),CURRENCY VARCHAR(15), RATE DECIMAL(18,2),AMT_PAY_DO DECIMAL(18,2),AMT_PAY_REAL DECIMAL(18,2)) OPEN XmlDataSchedule --END luanlt--- -- DS HANG MUC NGAN SACH DECLARE @TRADE_ID varchar(15),@GD_ID varchar(15),@AMT_APP decimal(18,2),@AMT_EXE decimal(18,2),@AMT_REMAIN_GD decimal(18,2) DECLARE XmlDataGood CURSOR LOCAL 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 ---- END CURSOR --CatCursor 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) DECLARE XmlAttach CURSOR LOCAL 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 --THIEUVQ 281119--- EXEC [dbo].[PAY_CODE_GenKey] 'TR_REQ_PAYMENT', '','', @p_REQ_PAY_CODE out --END-- DECLARE @INDEX INT =0 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 EXISTS(SELECT * FROM TR_REQ_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 đề nghị thanh toán đã tồn tại trong hệ thống' ErrorDesc RETURN '-1' END EXEC SYS_CodeMasters_Gen 'TR_REQ_PAYMENT', @p_REQ_PAY_ID out IF @p_REQ_PAY_ID='' OR @p_REQ_PAY_ID IS NULL GOTO ABORT INSERT INTO [dbo].[TR_REQ_PAYMENT] ([REQ_PAY_ID] ,[REQ_PAY_CODE],[REQ_DT] ,[BRANCH_ID] ,[DEP_ID] ,[REQ_REASON] ,[REQ_TYPE],REQ_ENTRIES, [REQ_DESCRIPTION] ,REF_ID, RECEIVER_PO ,[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_NOTE] ,[BRANCH_CREATE] ,[NOTES],RECORD_STATUS,TRANSFER_MAKER,TRANSFER_DT,TRASFER_USER_RECIVE,PROCESS, PAY_PHASE,IS_PERIOD) VALUES (@p_REQ_PAY_ID, @p_REQ_PAY_CODE,CONVERT(DATE,@p_REQ_DT,103), @p_BRANCH_ID, @p_DEP_ID, @p_REQ_REASON, @p_REQ_TYPE, @P_REQ_ENTRIES, @p_REQ_DESCRIPTION, @p_REF_ID, @p_RECEIVER_PO, @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,@p_RECORD_STATUS, @p_TRANSFER_MAKER, NULL, @p_TRASFER_USER_RECIVE, NULL,@p_PAY_PHASE,ISNULL(@p_IS_PERIOD,'N')) IF @@Error <> 0 GOTO ABORT --- INSERT LICH THANH TOAN --INSERT FROM MethodCursor SET @INDEX = 0 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,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANS WHILE @@fetch_status=0 BEGIN IF(@REQ_PAY_TYPE<>'1') BEGIN SET @ISSUED_DT = NULL END SET @INDEX = @INDEX +1 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 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,@ISSUED_BY,CONVERT(DATE,@ISSUED_DT,103),@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANS) 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,@ISSUED_BY, @ISSUED_DT,@CURRENCY,@RATE,@CHECK_IN,@TYPE_TRANS END CLOSE XmlDataMethod; DEALLOCATE XmlDataMethod; ------------------------- -- INSERT CHUNG TU DINH KEM FETCH NEXT FROM XmlAttach INTO @ATTACH_ID,@CON_ID,@CONT_CODE,@NOTES,@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,@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,@CON_DESC,@CON_TYPE,@AMT_ATT,@REF_DT END CLOSE XmlAttach; DEALLOCATE XmlAttach; ----END --Insert XmlData FETCH NEXT FROM XmlData INTO @TRANS_NO , @TRANS_DT ,@INVOICE_SIGN ,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER , @TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@TYPE_VAT,@TYPE_FUNC WHILE @@fetch_status=0 BEGIN -- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN SET @INDEX_IV = @INDEX_IV+1 IF(LEN(@INVOICE_NO) >7) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' không được vượt quá 7 kí tự' ErrorDesc RETURN '-1' END IF(LEN(@INVOICE_NO) <7) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' phải đủ 7 kí tự' ErrorDesc RETURN '-1' END IF(EXISTS(SELECT * FROM TR_REQ_PAY_INVOICE WHERE INVOICE_NO =@INVOICE_NO AND INVOICE_NO_SIGN = @INVOICE_NO_SIGN AND INVOICE_SIGN =@INVOICE_SIGN AND TAX_NO =@TAX_NO AND TAX =@TAX)) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng ' + CONVERT(VARCHAR(5),@INDEX_IV)+N': Số hóa đơn '+ @INVOICE_NO +N' đã tồn tại trong hệ thống' ErrorDesc RETURN '-1' END END SET @INDEX = @INDEX +1 DECLARE @p_REQ_INV_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_INVOICE', @p_REQ_INV_ID OUT; IF @p_REQ_INV_ID='' OR @p_REQ_INV_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_INVOICE(REQ_PAYDT_ID,REQ_PAY_ID,TRANS_NO,TRANS_DT,INVOICE_SIGN,INVOICE_NO,INVOICE_DT,SELLER,TAX_NO,GOODS_NAME,PRICE,TAX,VAT,NOTE, 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,RECORD_STATUS,INVOICE_NO_SIGN,VAT_RATE,CURRENCY,RATE,PRICE_KT,VAT_KT,TOTAL_AMT_KT,TYPE_VAT,TYPE_FUNC) VALUES (@p_REQ_INV_ID,@p_REQ_PAY_ID , @TRANS_NO , NULL ,@INVOICE_SIGN ,@INVOICE_NO ,CONVERT(DATE,@INVOICE_DT,103) ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES, @p_MAKER_ID,GETDATE(),NULL,'U',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',@INVOICE_NO_SIGN,@VAT_RATE,@CURRENCY,@RATE,@PRICE,@VAT,@PRICE+@VAT,@TYPE_VAT,@TYPE_FUNC) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlData INTO @TRANS_NO , @TRANS_DT,@INVOICE_SIGN,@INVOICE_NO_SIGN,@INVOICE_NO ,@INVOICE_DT ,@SELLER ,@TAX_NO ,@GOODS_NAME ,@PRICE ,@TAX,@VAT,@NOTES,@VAT_RATE,@CURRENCY,@RATE,@TYPE_VAT,@TYPE_FUNC END; CLOSE XmlData; DEALLOCATE XmlData; --- INSERT CAC HANG MUC NGAN SACH 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 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 --------------------------- --Luanlt-- ------------------------ IF(@p_REQ_TYPE = 'I') BEGIN FETCH NEXT FROM XmlDataPay INTO @PAY_ADV_ID, @AMT_ADVANCED ,@AMT_DO,@AMT_REMAIN,@AMT_PAY ,@AMT_USE ,@AMT_REVERT,@AMT_ADD,@CURRENCY,@RATE WHILE @@fetch_status=0 BEGIN SET @REQ_PAY_ADV_CODE = (SELECT REQ_PAY_CODE FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID =@PAY_ADV_ID) SET @INDEX_AD = @INDEX_AD +1 -- KHI NAO BAM GUI PHE DUYET THI MOI CHECK VALIDATE IF(@p_TYPE_FUNCTION ='SEND') BEGIN -- KIEM TRA XEM CO PHIEU NAO DANG DUOC THANH TOAN HOAN TAM UNG MA CHUA DUYET HAY CHUA IF(EXISTS (SELECT * FROM TR_REQ_PAYMENT_DT WHERE (PAY_ADV_ID =@PAY_ADV_ID AND (AUTH_STATUS_KT <>'A' OR AUTH_STATUS_KT IS NULL)) AND 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_AD)+ N': Phiếu tạm ứng số '+@REQ_PAY_ADV_CODE+ N' đang được thanh toán hoàn tạm ứng. Vui lòng đợi giao dịch hoàn tất' ErrorDesc RETURN '-1' END -- KIEM TRA NEU SO TIEN DE NGHI HOAN TAM ƯNG LON HON SO TIEN CON LAI CAN PHAI TAM UNG IF(@AMT_REVERT>(@AMT_REMAIN -@AMT_USE) AND @AMT_REVERT >0) BEGIN ROLLBACK TRANSACTION SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Dòng '+ CONVERT(VARCHAR(5),@INDEX_AD)+ N': Số tiền hoàn ứng không được vượt quá '+FORMAT((@AMT_REMAIN -@AMT_USE),'#,#', 'vi-VN') ErrorDesc RETURN '-1' END END DECLARE @p_REQ_PAYDT_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAYMENT_DT', @p_REQ_PAYDT_ID OUT; IF @p_REQ_PAYDT_ID='' OR @p_REQ_PAYDT_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAYMENT_DT VALUES (@p_REQ_PAYDT_ID,@PAY_ADV_ID,@p_REQ_PAY_ID , ISNULL(@AMT_ADVANCED,0) ,ISNULL(@AMT_DO,0),ISNULL(@AMT_REMAIN,0), ISNULL(@AMT_PAY,0) ,ISNULL(@AMT_USE,0) ,ISNULL(@AMT_REVERT,0), ISNULL(@AMT_ADD,0), GETDATE() ,@p_MAKER_ID ,GETDATE() ,NULL ,NULL,'U' ,NULL ,NULL ,NULL ,NULL,NULL,@CURRENCY,@RATE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataPay INTO @PAY_ADV_ID, @AMT_ADVANCED ,@AMT_DO,@AMT_REMAIN,@AMT_PAY ,@AMT_USE ,@AMT_REVERT,@AMT_ADD,@CURRENCY,@RATE END CLOSE XmlDataPay; DEALLOCATE XmlDataPay; --INSERT FROM CatCursor SET @INDEX = 0 FETCH NEXT FROM XmlDataCat INTO @REQ_ADV_ID,@CAT_NAME,@TOTAL_AMT_CAT,@DEPT_ID,@CURRENCY,@RATE WHILE @@fetch_status=0 BEGIN SET @INDEX = @INDEX +1 DECLARE @p_REQ_PAY_CAT_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_CAT', @p_REQ_PAY_CAT_ID OUT; IF @p_REQ_PAY_CAT_ID='' OR @p_REQ_PAY_CAT_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_CAT VALUES (@p_REQ_PAY_CAT_ID,@p_REQ_PAY_ID,@REQ_ADV_ID,@CAT_NAME,@TOTAL_AMT_CAT,@DEPT_ID,@p_MAKER_ID,GETDATE(),@CURRENCY,@RATE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataCat INTO @REQ_ADV_ID,@CAT_NAME,@TOTAL_AMT_CAT,@DEPT_ID,@CURRENCY,@RATE END CLOSE XmlDataCat; DEALLOCATE XmlDataCat; END IF(@p_REQ_TYPE = 'D' OR @p_REQ_TYPE='I') BEGIN ---------------------------- --INSERT FROM ServiceCursor SET @INDEX = 0 FETCH NEXT FROM XmlDataService INTO @REQ_PAY_SERVICE_NAME,@RECEIVE_ID_SERVICE,@RECEIVE_NAME_SERVICE,@REQ_PAY_REASON_SERVICE,@TOTAL_AMT_SERVICE, @REQ_PAY_TYPE_SERVICE,@REQ_PAY_DESC_SERVICE,@REQ_PAY_ENTRIES_SERVICE,@DEPT_ID_SRV,@CURRENCY,@RATE WHILE @@fetch_status=0 BEGIN SET @INDEX = @INDEX +1 DECLARE @p_REQ_PAY_SERVICE_ID VARCHAR(15); EXEC SYS_CodeMasters_Gen 'TR_REQ_PAY_SERVICE', @p_REQ_PAY_SERVICE_ID OUT; IF @p_REQ_PAY_SERVICE_ID='' OR @p_REQ_PAY_SERVICE_ID IS NULL GOTO ABORT; INSERT INTO TR_REQ_PAY_SERVICE(SERVICE_ID,REQ_PAY_ID,SERVICE_NAME,EMP_ID,REQ_PAY_REASON,TOTAL_AMT,REQ_PAY_TYPE,REQ_PAY_DESC,REQ_PAY_ENTRIES,MAKER_ID,CREATE_DT,DEPT_ID,CURRENCY,RATE) VALUES (@p_REQ_PAY_SERVICE_ID,@p_REQ_PAY_ID,@REQ_PAY_SERVICE_NAME,@RECEIVE_ID_SERVICE,@REQ_PAY_REASON_SERVICE,@TOTAL_AMT_SERVICE, @REQ_PAY_TYPE_SERVICE,@REQ_PAY_DESC_SERVICE,@REQ_PAY_ENTRIES_SERVICE,@p_MAKER_ID,GETDATE(),@DEPT_ID_SRV,@CURRENCY,@RATE) IF @@error<>0 GOTO ABORT; FETCH NEXT FROM XmlDataService INTO @REQ_PAY_SERVICE_NAME,@RECEIVE_ID_SERVICE,@RECEIVE_NAME_SERVICE,@REQ_PAY_REASON_SERVICE,@TOTAL_AMT_SERVICE,@REQ_PAY_TYPE_SERVICE, @REQ_PAY_DESC_SERVICE,@REQ_PAY_ENTRIES_SERVICE,@DEPT_ID_SRV,@CURRENCY,@RATE END CLOSE XmlDataService; DEALLOCATE XmlDataService; END IF((@p_REQ_TYPE = 'P' AND (@p_IS_PERIOD <>'Y' OR @p_IS_PERIOD IS NULL)) OR @p_REQ_TYPE ='I') BEGIN DECLARE XmlDataPO CURSOR LOCAL 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 ---- 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))) --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,'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 ScheduleCursor SET @INDEX_PO = 0 FETCH NEXT FROM XmlDataSchedule INTO @PO_ID,@TERM_PAY,@AMT_PAY_SCHEDULE,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE,@AMT_REMAIN_SCHEDULE,@REQ_AD_DT, @PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_DO,@AMT_PAY_REAL WHILE @@fetch_status=0 BEGIN --IF(@AMT_REMAIN_SCHEDULE =0) --BEGIN -- SET @PROCESS ='3' --END --IF(@PROCESS <>'2') --BEGIN -- SET @TOTAL_SCHEDULE_AMT = @TOTAL_SCHEDULE_AMT +@AMT_REMAIN_SCHEDULE --END ---- SET @TOTAL_SCHEDULE_AMT =@TOTAL_SCHEDULE_AMT+(@AMT_PAY_SCHEDULE -@AMT_ADVANCE_SCHEDULE-@AMT_PAY_DO) SET @INDEX_PO = @INDEX_PO +1 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,@REQ_ADV_ID_SCHE,@AMT_ADVANCE_SCHEDULE, --IIF(@PROCESS<>'2',(@AMT_PAY_SCHEDULE-@AMT_ADVANCE_SCHEDULE),0), @AMT_PAY_DO, @AMT_REMAIN_SCHEDULE,GETDATE(),CONVERT(DATETIME,@REQ_AD_DT,103),@PROCESS,@p_MAKER_ID,GETDATE(),'U','','PAY', @REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_REAL) 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_REMAIN_SCHEDULE,@REQ_AD_DT, @PROCESS,@PAY_ID,@REF_ID,@REQ_PAY_DESC,@REQ_PAY_ENTRIES,@CURRENCY,@RATE,@AMT_PAY_DO,@AMT_PAY_REAL END CLOSE XmlDataSchedule; DEALLOCATE XmlDataSchedule; -- VALIDATE DANH SACH HOAN UNG -- LUCTV BO SUNG 19-11-2019. CHUA BIET CO VALIDATE HAY KHONG NEN VALIDATE DUOI STORE --IF(@INDEX_PO =0) --BEGIN -- ROLLBACK TRANSACTION -- SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, N'Danh sách các khoản hoàn tạm ứng không được phép trống' ErrorDesc -- RETURN '-1' --END -- 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 IF((@p_REQ_TYPE = 'P' AND @p_IS_PERIOD='Y') OR @p_REQ_TYPE ='I') BEGIN DECLARE XmlDataPO CURSOR LOCAL 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,'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 LOCAL 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),AD_PAY_ID VARCHAR(15), PROCESS VARCHAR(5), PARENT_ID VARCHAR(15), 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),@AD_PAY_ID VARCHAR(15), @_PROCESS VARCHAR(5),@PARENT_ID VARCHAR(15),@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,@AD_PAY_ID, @_PROCESS,@PARENT_ID,@PAY_PHASE,@REASON_TTDK WHILE @@fetch_status=0 BEGIN --IF(CONVERT(DATE, @TO_DATE,103) 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,PARENT_ID,PAY_PHASE,REASON) VALUES (@PERIOD_ID,@p_REQ_PAY_ID,'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),@AD_PAY_ID, @_PROCESS,@PARENT_ID,@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,@AD_PAY_ID, @_PROCESS,@PARENT_ID,@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 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_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 thanh toán 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' 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 CLOSE XmlData; DEALLOCATE XmlData; CLOSE XmlDataPay; DEALLOCATE XmlDataPay; Close XmlDataMethod; Close XmlDataCat; CLOSE XmlDataService; CLOSE XmlDataSchedule; Deallocate XmlDataMethod; Deallocate XmlDataCat; DEALLOCATE XmlDataService; DEALLOCATE XmlDataSchedule; SELECT '-1' as Result, '' REQ_PAY_ID,'' AS REQ_PAY_CODE, '' ErrorDesc RETURN '-1' End 10 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 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 --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 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 @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)) INSERT into @tmp_CN SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@BRANCH_RQ) --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) 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) -- 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 WHERE A.BRANCH_ID = @BRANCH_RQ AND A.AUTH_STATUS_KT ='A' AND C.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(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_ID) AND A.AUTH_STATUS ='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(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' ) END DECLARE @LIMIT_REMAIN DECIMAL(18,0) SET @LIMIT_REMAIN =ISNULL(@TOTAL_ADVANCE,0) - ISNULL(@TOTAL_PAYBACK,0) print @LIMIT_REMAIN 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_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 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 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 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 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 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 FROM TR_REQ_ADVANCE_PAYMENT WHERE REQ_PAY_ID=@p_REQ_PAY_ID) --- 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 ) --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) SET @BRANCH_ID_PROC= (SELECT BRANCH_ID FROM TL_SYSROLE_LIMIT WHERE ROLE_ID=@NEXT_ROLE AND LIMIT_TYPE='ADV_PAY') 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 TL_SYSROLE_LIMIT WHERE LIMIT_VALUE >@t_REQ_AMT AND ROLE_ID NOT IN (SELECT ROLE_USER FROM PL_REQUEST_PROCESS WHERE REQ_ID = @p_REQ_PAY_ID) AND LIMIT_TYPE ='ADV_PAY') SET @BRANCH_ID_PROC= (SELECT BRANCH_ID FROM TL_SYSROLE_LIMIT 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 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 ROLE_DESC + N' xác nhận' FROM TL_SYSROLE WHERE ROLE_ID = @ROLE_ID) WHERE ROLE_USER =@ROLE_ID 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 ,'' 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