ALTER PROC dbo.ASS_TRANSFER_MULTI_MASTER_BBBG @TRANS_MULTI_MASTER_ID varchar(15) = null AS DECLARE @REQ_ID VARCHAR(20) = (SELECT REQ_ID FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) DECLARE @REQ_MAKER VARCHAR(110) = (SELECT TRSD.MAKER_ID FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID) DECLARE @REQ_MAKER_DT VARCHAR(110) = (SELECT TRSD.CREATE_DT FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID) DECLARE @REQ_BRANCH VARCHAR(110) = (SELECT TRSD.BRANCH_ID FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID) DECLARE @REQ_DEP VARCHAR(110) = (SELECT TRSD.DEP_ID FROM TR_REQUEST_SHOP_DOC TRSD WHERE TRSD.REQ_ID = @REQ_ID) DECLARE @REQ_DEP_NAME NVARCHAR(510) = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @REQ_DEP) DECLARE @REQ_POS NVARCHAR(510) = (SELECT CEL.POS_NAME FROM CM_EMPLOYEE_LOG CEL WHERE CEL.USER_DOMAIN = @REQ_MAKER) DECLARE @REQ_MAKER_NAME NVARCHAR(500) = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @REQ_MAKER) DECLARE @REQ_MAKER_PHONE NVARCHAR(500) = (SELECT TU.PHONE FROM TL_USER TU WHERE TU.TLNANME = @REQ_MAKER) DECLARE @REQ_MAKER_ADDR NVARCHAR(500) = (SELECT TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @REQ_MAKER) --Khiemchg lấy danh sách đơn vị nhận và cho DECLARE @INFO_TRANSFER_RECIEVE TABLE ( BRN_SD VARCHAR(50), BRN_SD_NAME NVARCHAR(500), K_SD VARCHAR(50), K_SD_NAME NVARCHAR(500), TT_SD VARCHAR(50), TT_SD_NAME NVARCHAR(500), DEP_SD VARCHAR(50), DEP_SD_NAME NVARCHAR(500), PER_SD VARCHAR(50), BRN_N VARCHAR(50), BRN_N_NAME NVARCHAR(500), K_N VARCHAR(50), K_N_NAME NVARCHAR(500), TT_N VARCHAR(50), TT_N_NAME NVARCHAR(500), DEP_N VARCHAR(50), DEP_N_NAME NVARCHAR(500), PER_N VARCHAR(50) ) INSERT INTO @INFO_TRANSFER_RECIEVE SELECT DISTINCT BRANCH_ID_OLD, BB.BRANCH_NAME, KHOI_ID_OLD, BD.DEP_NAME, CENTER_ID_OLD, BF.DEP_NAME, DEPT_ID_OLD, BE.DEP_NAME, EMP_ID_OLD, A.BRANCH_ID, CB.BRANCH_NAME, A.KHOI_ID, CD.DEP_NAME, CENTER_ID, CF.DEP_NAME, DEPT_ID, CE.DEP_NAME, EMP_ID FROM ASS_TRANSFER_MULTI_DT A LEFT JOIN CM_BRANCH BB ON A.BRANCH_ID_OLD = BB.BRANCH_ID LEFT JOIN CM_DEPARTMENT BD ON BD.DEP_ID = A.KHOI_ID_OLD LEFT JOIN CM_DEPARTMENT BE ON BE.DEP_ID = A.DEPT_ID_OLD LEFT JOIN CM_DEPARTMENT BF ON BF.DEP_ID = A.CENTER_ID_OLD LEFT JOIN CM_BRANCH CB ON A.BRANCH_ID = CB.BRANCH_ID LEFT JOIN CM_DEPARTMENT CD ON CD.DEP_ID = A.KHOI_ID LEFT JOIN CM_DEPARTMENT CE ON CE.DEP_ID = A.DEPT_ID LEFT JOIN CM_DEPARTMENT CF ON CF.DEP_ID = A.CENTER_ID WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID GROUP BY BRANCH_ID_OLD, BB.BRANCH_NAME, KHOI_ID_OLD, BD.DEP_NAME, CENTER_ID_OLD, BF.DEP_NAME, DEPT_ID_OLD, BE.DEP_NAME, EMP_ID_OLD, A.BRANCH_ID, CB.BRANCH_NAME, A.KHOI_ID, CD.DEP_NAME, CENTER_ID, CF.DEP_NAME, DEPT_ID, CE.DEP_NAME, EMP_ID --DELETE @INFO_TRANSFER_RECIEVE WHERE (BRN_SD = BRN_N AND DEP_SD = DEP_N) DECLARE @SIGN_QLTS_NAME NVARCHAR(500) = '' DECLARE @SIGN_QLTS_PHONE NVARCHAR(500) = '' DECLARE @SIGN_QLTS_ADDR NVARCHAR(500) = '' DECLARE @SIGN_QLTS_POS NVARCHAR(500) = '' DECLARE @SIGN_QLTS NVARCHAR(100) = (SELECT ATMM.CHECKER_ID FROM ASS_TRANSFER_MULTI_MASTER ATMM WHERE ATMM.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) DECLARE @SIGN_QLTS_DT DATETIME = (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PP.PROCESS_ID = 'QLTS_D' ORDER BY PP.APPROVE_DT DESC) DECLARE @MAKER_ID NVARCHAR(100) = (SELECT ATMM.MAKER_ID FROM ASS_TRANSFER_MULTI_MASTER ATMM WHERE ATMM.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) DECLARE @MAKER_ID_DT DATETIME = (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PP.PROCESS_ID = 'SEND' ORDER BY PP.APPROVE_DT DESC) DECLARE @SIGN_QLTS_BR NVARCHAR(500) = (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = (SELECT TU.TLSUBBRID FROM TL_USER TU WHERE TU.TLNANME = @SIGN_QLTS)) DECLARE @SIGN_QLTS_DEP NVARCHAR(500) = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = (SELECT TU.SECUR_CODE FROM TL_USER TU WHERE TU.TLNANME = @SIGN_QLTS)) DECLARE @MAKER_NAME NVARCHAR(500) = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @MAKER_ID) DECLARE @MAKER_POS NVARCHAR(500) = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@MAKER_ID) IF(EXISTS (SELECT * FROM dbo.FN_GET_USER_BY_ROLE_VB('QLTS', 'DV0001', 'DEP000000000048') FGUBRV WHERE FGUBRV.TLNANME = @SIGN_QLTS)) BEGIN SELECT @SIGN_QLTS_NAME = TU.TLFullName, @SIGN_QLTS_PHONE = TU.PHONE, @SIGN_QLTS_ADDR = TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @SIGN_QLTS SET @SIGN_QLTS_POS = (SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @SIGN_QLTS) END -- Thông tin chữ kí DECLARE @SIGN_CHECKER_ID_KT VARCHAR(100),@SIGN_MAKER_ID VARCHAR(100),@SIGN_APPROVE_DT_KT DATETIME,@SIGN_CREATE_DT DATETIME,@SIGN_APPROVE_ID VARCHAR(20),@SIGN_APPROVE_DT DATETIME, @SIGN_RECEVI_MAKER_ID VARCHAR(100),@SIGN_RECEVI_DT DATETIME,@SIGN_MAKER_ID_KT VARCHAR(100),@SIGN_MAKER_ID_CREATE VARCHAR(50),@AUTH_STATUS VARCHAR(50) -- Thông tin bên giao DECLARE @DEP_TRANSFER NVARCHAR(1500) = (SELECT TOP 1 DEP_SD FROM @INFO_TRANSFER_RECIEVE) DECLARE @BRANCH_TRANSFER NVARCHAR(500) = (SELECT TOP 1 BRN_SD FROM @INFO_TRANSFER_RECIEVE) DECLARE @KHOI_TRANSFER NVARCHAR(500) = (SELECT TOP 1 K_SD FROM @INFO_TRANSFER_RECIEVE) DECLARE @TT_TRANSFER NVARCHAR(500) = (SELECT TOP 1 TT_SD FROM @INFO_TRANSFER_RECIEVE) DECLARE @POSITION NVARCHAR(500) --= (SELECT * FROM @INFO_TRANSFER_RECIEVE) DECLARE @TRANSFER_MAKER_ID VARCHAR(100) = (SELECT TOP 1 PER_SD FROM @INFO_TRANSFER_RECIEVE) DECLARE @CREATE_DT NVARCHAR(500) = (SELECT FORMAT(ATMM.CREATE_DT, 'dd/MM/yyyy') FROM ASS_TRANSFER_MULTI_MASTER ATMM WHERE ATMM.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) DECLARE @TRANSFER_CONFIRM NVARCHAR(500) = (SELECT TOP 1 PRP.CHECKER_ID FROM PL_REQUEST_PROCESS PRP WHERE PRP.PROCESS_ID = 'XNGN' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PRP.BRANCH_ID = @BRANCH_TRANSFER AND ISNULL(PRP.DEP_ID,'') = ISNULL(@DEP_TRANSFER, '')) DECLARE @TRANSFER_CONFIRM_DT DATETIME = (SELECT TOP 1 PRP.APPROVE_DT FROM PL_PROCESS PRP WHERE PRP.PROCESS_ID = 'CONFIRM' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PRP.CHECKER_ID = @TRANSFER_CONFIRM ORDER BY PRP.APPROVE_DT DESC) DECLARE @NAME_TRANSFER NVARCHAR(500) = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @TRANSFER_CONFIRM) DECLARE @POS_TRANSFER NVARCHAR(500) = (SELECT CEL.POS_NAME FROM CM_EMPLOYEE_LOG CEL WHERE CEL.USER_DOMAIN = @TRANSFER_CONFIRM) DECLARE @PHONE_TRANSFER NVARCHAR(500) = (SELECT TU.PHONE FROM TL_USER TU WHERE TU.TLNANME = @TRANSFER_CONFIRM) DECLARE @ADDR_TRANSFER NVARCHAR(500) = (SELECT TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @TRANSFER_CONFIRM) -- Thông tin bên nhận DECLARE @DEP_RECIVE NVARCHAR(1500) = (SELECT TOP 1 DEP_N FROM @INFO_TRANSFER_RECIEVE) DECLARE @BRANCH_RECIVE NVARCHAR(500) = (SELECT TOP 1 BRN_N FROM @INFO_TRANSFER_RECIEVE) DECLARE @KHOI_RECIVE NVARCHAR(500) = (SELECT TOP 1 K_N FROM @INFO_TRANSFER_RECIEVE) DECLARE @TT_RECIVE NVARCHAR(500) = (SELECT TOP 1 TT_N FROM @INFO_TRANSFER_RECIEVE) DECLARE @POSITION_RECIVE NVARCHAR(500) DECLARE @RECIVE_MAKER_ID VARCHAR(100) = (SELECT TOP 1 PER_N FROM @INFO_TRANSFER_RECIEVE) DECLARE @RECEIVE_CONFIRM NVARCHAR(500) = (SELECT TOP 1 PRP.CHECKER_ID FROM PL_REQUEST_PROCESS PRP WHERE PRP.PROCESS_ID = 'XNGN' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PRP.BRANCH_ID = @BRANCH_RECIVE AND ISNULL(PRP.DEP_ID,'') = ISNULL(@DEP_RECIVE, '')) DECLARE @RECEIVE_CONFIRM_DT DATETIME = (SELECT TOP 1 PRP.APPROVE_DT FROM PL_PROCESS PRP WHERE PRP.PROCESS_ID = 'CONFIRM' AND PRP.REQ_ID = @TRANS_MULTI_MASTER_ID AND PRP.CHECKER_ID = @RECEIVE_CONFIRM ORDER BY PRP.APPROVE_DT DESC) DECLARE @NAME_RECIVE NVARCHAR(500) = (SELECT TU.TLFullName FROM TL_USER TU WHERE TU.TLNANME = @RECEIVE_CONFIRM) DECLARE @POS_RECIVE NVARCHAR(500) = (SELECT CEL.POS_NAME FROM CM_EMPLOYEE_LOG CEL WHERE CEL.USER_DOMAIN = @RECEIVE_CONFIRM) DECLARE @PHONE_RECIVE NVARCHAR(500) = (SELECT TU.PHONE FROM TL_USER TU WHERE TU.TLNANME = @RECEIVE_CONFIRM) DECLARE @ADDR_RECIVE NVARCHAR(500) = (SELECT TU.ADDRESS FROM TL_USER TU WHERE TU.TLNANME = @RECEIVE_CONFIRM) -- Thông tin Ngày điều chuyển DECLARE @TRANSFER_DT DATETIME, @CREATE_DT_TRANSFER DATETIME SELECT @TRANSFER_DT=TRANSFER_DT,@CREATE_DT_TRANSFER =CREATE_DT FROM dbo.ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID -- BÊN GIAO -- Thông tin chữ kí SET @SIGN_MAKER_ID = ( SELECT MAKER_ID_BG FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) SET @SIGN_CREATE_DT = ( SELECT CONFIRM_DT_BG FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) SET @SIGN_APPROVE_ID = (SELECT CHECKER_ID FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) SET @SIGN_APPROVE_DT = (SELECT APPROVE_DT FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) -- Thông tin bên giao --SET @BRANCH_TRANSFER = ISNULL((SELECT TOP 1 A.BRANCH_NAME FROM dbo.CM_BRANCH A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.BRANCH_ID = B.BRANCH_ID_OLD WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '') -- + ISNULL((SELECT TOP 1 ' - ' + A.DEP_NAME FROM dbo.CM_DEPARTMENT A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.DEP_ID = B.DEPT_ID_OLD WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '') --SET @NAME_TRANSFER =(SELECT TLFullName FROM TL_USER WHERE TLNANME =@TRANSFER_MAKER_ID) SET @POSITION = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@TRANSFER_CONFIRM) SET @CREATE_DT = N'Ngày ' + CAST((SELECT DAY(APPROVE_DT_KT) FROM dbo.ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) AS VARCHAR(500)) + N' tháng ' + CAST((SELECT MONTH(APPROVE_DT_KT) FROM dbo.ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) AS VARCHAR(500)) + N' năm ' + CAST((SELECT YEAR(APPROVE_DT_KT) FROM dbo.ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) AS VARCHAR(500)) + N' tại ' + (SELECT B.BRANCH_NAME FROM dbo.ASS_TRANSFER_MULTI_MASTER A JOIN dbo.CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) -- BÊN NHẬN -- Thông tin chữ kí SET @SIGN_RECEVI_MAKER_ID = ( SELECT MAKER_ID_BN FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) SET @SIGN_RECEVI_DT = ( SELECT CONFIRM_DT_BN FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) -- Thông tin bên nhận SET @RECIVE_MAKER_ID = (SELECT TOP 1 MAKER_ID_BN FROM ASS_TRANSFER_CONFIRM_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) --SET @BRANCH_RECIVE = ISNULL((SELECT TOP 1 A.BRANCH_NAME FROM dbo.CM_BRANCH A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.BRANCH_ID = B.BRANCH_ID WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '') -- + ISNULL((SELECT TOP 1 ' - ' + A.DEP_NAME FROM dbo.CM_DEPARTMENT A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.DEP_ID = B.DEPT_ID WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '') --SET @DEP_RECIVE = (SELECT TOP 1 DEPT_ID FROM ASS_TRANSFER_MULTI_DT WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) --SET @NAME_RECIVE= (SELECT TLFullName FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_ID) SET @POSITION_RECIVE = (SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @RECEIVE_CONFIRM) --SET @KHOI_RECIVE= (SELECT TOP 1 DVDM_NAME FROM CM_DVDM WHERE DVDM_ID IN (SELECT DVDM_ID FROM PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM PL_COSTCENTER_DT -- WHERE DEP_ID =@DEP_RECIVE)) AND IS_KHOI ='1') -- KẾ TOÁN SET @SIGN_MAKER_ID_CREATE = ( SELECT A.MAKER_ID FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) SET @SIGN_CHECKER_ID_KT = ( SELECT CHECKER_ID_KT FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) SET @SIGN_APPROVE_DT_KT = ( SELECT APPROVE_DT_KT FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) SET @SIGN_MAKER_ID_KT = ( SELECT A.MAKER_ID_KT FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) -- DANH SÁCH TÀI SẢN SELECT ROW_NUMBER()OVER(ORDER BY A.ASSET_ID) AS STT, A.ASSET_ID, A.[TYPE_ID],C.[TYPE_CODE], C.[TYPE_NAME], A.GROUP_ID,B.GROUP_CODE, B.GROUP_NAME, A.ASSET_NAME, A.ASSET_DESC, A.SUP_ID, FORMAT(tmp.BUY_PRICE, '#,#') AS BUY_PRICE, A.AMORT_AMT, A.ASS_TYPE, TR.BRANCH_ID,BR_R.BRANCH_CODE,BR_R.BRANCH_NAME,H.DEP_ID, H.DEP_CODE, H.DEP_NAME, A.EMP_ID,EM_R.EMP_CODE,EM_R.EMP_NAME, A.BUY_DATE, A.USE_DATE, A.SPECIAL_ASS, CAST(A.AMORT_MONTH AS INT) AS AMORT_MONTH, A.AMORT_RATE, A.AMORT_START_DATE, A.AMORT_END_DATE, A.FIRST_AMORT_AMT, --CAST(A.AMORTIZED_MONTH AS INT) AS AMORTIZED_MONTH, CAST((A.AMORT_MONTH - A.AMORTIZED_MONTH) AS INT) AS REMAIN_MONTH, -- CASE WHEN DATEDIFF(month,A.AMORT_START_DATE,@TRANSFER_DT) > A.AMORT_MONTH THEN CAST(A.AMORT_MONTH AS INT) ELSE CAST(DATEDIFF(month,A.AMORT_START_DATE,@TRANSFER_DT)AS INT) END AS AMORTIZED_MONTH, --CAST(DATEDIFF(month,A.AMORT_START_DATE,tmp2.AMORT_DT)AS INT) AS AMORTIZED_MONTH, ISNULL(CAST(tmp2.AMORTIZED_MONTH AS INT),0) AS AMORTIZED_MONTH, A.AMORTIZED_AMT, FORMAT(tmp.BUY_PRICE - ISNULL(tmp1.AMORTIZED_AMT,0) , '#,#') AS GT_CL, --So tien khau hao con lai A.PO_ID, A.WAREHOUSE_ID, A.LOCATION, A.REF_ASSET_ID, A.REF_AMORTIZED_AMT, A.WARRANTY_MONTHS, A.AMORT_STATUS, D.STATUS_NAME AMORT_STATUS_NAME, A.ASS_STATUS, CU.UNIT_NAME, E.STATUS_NAME ASS_STATUS_NAME, --Tinh trang tai san A.ASS_STATUS_DESC, A.RECORD_STATUS, A.AUTH_STATUS, ZZ.AUTH_STATUS_NAME, A.MAKER_ID, convert(varchar, A.CREATE_DT, 103) as CREATE_DT_ASS, convert(varchar, TR.CREATE_DT, 103) as CREATE_DT, A.CHECKER_ID, A.APPROVE_DT, TR.[DESCRIPTION], TR.BRANCH_CREATE,TR.LOCATION,TR.[DESCRIPTION],TR.TRANSFER_MULTI_ID, A.AMORT_AMT-A.AMORTIZED_AMT ASREMAIN_AMT, convert(varchar, B.CREATE_DT, 103) as CREATE_DT_ASS, 1 AS SOLUONG, TR.DESCRIPTION AS NOTES, A.ASSET_CODE, -- CASE -- WHEN A.REQ_CODE IS NOT NULL AND A.PL IS NOT NULL THEN A.REQ_CODE + '/' + A.PL -- WHEN A.REQ_CODE IS NOT NULL AND A.PL IS NULL THEN A.REQ_CODE -- WHEN A.REQ_CODE IS NULL AND A.PL IS NOT NULL THEN A.PL ELSE NULL -- END AS PYC_PL ATMM.REQ_CODE + ISNULL(CHAR(10) + CHAR(10) + A.PL,'') AS PYC_PL FROM [dbo].[ASS_TRANSFER_MULTI_DT] TR LEFT JOIN [ASS_TRANSFER_MULTI_MASTER] ATMM ON TR.TRANS_MULTI_MASTER_ID = ATMM.TRANS_MULTI_MASTER_ID LEFT JOIN ASS_MASTER A ON A.ASSET_ID = TR.ASSET_ID LEFT JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID LEFT JOIN ASS_TYPE C ON A.[TYPE_ID] = C.[TYPE_ID] LEFT JOIN CM_UNIT CU ON B.UNIT = CU.UNIT_ID LEFT JOIN ASS_AMORT_STATUS D ON A.AMORT_STATUS = D.STATUS_CODE LEFT JOIN ASS_STATUS E ON A.ASS_STATUS = E.STATUS_ID LEFT JOIN CM_AUTH_STATUS ZZ ON ZZ.AUTH_STATUS = TR.AUTH_STATUS LEFT JOIN CM_BRANCH BR_R ON BR_R.BRANCH_ID=TR.BRANCH_ID LEFT JOIN CM_EMPLOYEE EM_R ON EM_R.EMP_ID=TR.EMP_ID LEFT JOIN CM_DEPARTMENT H ON H.DEP_ID = TR.DEPT_ID -- PHONGNT 4/9/22 LẤY GIÁ TRỊ NGUYÊN GIÁ, KHẤU HAO TẠI THỜI ĐIỂM NGÀY ĐIỀU CHUYỂN LEFT JOIN (SELECT ASSET_ID,(SUM(CASE WHEN CRDR='C' AND TRN_DT < @TRANSFER_DT AND ASSET_AMT IS NOT NULL THEN ASSET_AMT END) - ISNULL(SUM(CASE WHEN CRDR='D' AND TRN_DT < @TRANSFER_DT AND ASSET_AMT IS NOT NULL THEN ASSET_AMT END),0)) AS BUY_PRICE FROM dbo.ASS_VALUES GROUP BY ASSET_ID) tmp ON A.ASSET_ID=tmp.ASSET_ID LEFT JOIN (SELECT ASSET_ID,(SUM(CASE WHEN CRDR='C' AND AMORT_DT < @TRANSFER_DT AND AMORT_AMT IS NOT NULL THEN AMORT_AMT END) - ISNULL(SUM(CASE WHEN CRDR='D' AND AMORT_DT < @TRANSFER_DT AND AMORT_AMT IS NOT NULL THEN AMORT_AMT END),0)) AS AMORTIZED_AMT FROM dbo.ASS_AMORT_DT GROUP BY ASSET_ID) tmp1 ON A.ASSET_ID=tmp1.ASSET_ID -- LEFT JOIN (SELECT ASSET_ID,max(AMORT_DT) AS AMORT_DT FROM ASS_AMORT_DT WHERE CRDR ='C' AND AMORT_DT<@TRANSFER_DT GROUP BY ASSET_ID) tmp2 ON tmp2.ASSET_ID = A.ASSET_ID LEFT JOIN (SELECT ASSET_ID,COUNT(*) AS AMORTIZED_MONTH FROM ASS_AMORT_DT WHERE CRDR ='C' AND AMORT_DT<@TRANSFER_DT GROUP BY ASSET_ID) tmp2 ON tmp2.ASSET_ID = A.ASSET_ID -- END WHERE 1=1 AND TR.TRANS_MULTI_MASTER_ID= @TRANS_MULTI_MASTER_ID IF(CONVERT(VARCHAR,@CREATE_DT_TRANSFER,103) '') --BEGIN -- SELECT -- IIF((SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @REQ_BRANCH) = 'HS', (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @REQ_BRANCH) + ' - ' + ISNULL(@REQ_DEP_NAME,''), (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @REQ_BRANCH)) -- AS KHOI_TRANSFER, (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @REQ_BRANCH) AS BRANCH_TRANSFER, @REQ_MAKER_NAME AS NAME_TRANSFER, @REQ_MAKER_ADDR AS ADDR_TRANSFER, @REQ_MAKER_PHONE AS PHONE_TRANSFER, @REQ_POS AS POSITION_SEND, @CREATE_DT AS A1 -- --END --ELSE BEGIN -- SELECT -- IIF((SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER) = 'HS', (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER) + ' - ' + ISNULL(@GIAO,''), (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER)) -- AS KHOI_TRANSFER, (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_TRANSFER) AS BRANCH_TRANSFER, @MAKER_NAME AS NAME_TRANSFER, @ADDR_TRANSFER AS ADDR_TRANSFER, @PHONE_TRANSFER AS PHONE_TRANSFER, @MAKER_POS AS POSITION_SEND, @CREATE_DT AS A1 SELECT IIF((SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER) = 'HS', (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER) + ' - ' + ISNULL(@GIAO,''), (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_TRANSFER)) AS KHOI_TRANSFER, (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_TRANSFER) AS BRANCH_TRANSFER, @NAME_TRANSFER AS NAME_TRANSFER, @ADDR_TRANSFER AS ADDR_TRANSFER, @PHONE_TRANSFER AS PHONE_TRANSFER, @POS_TRANSFER AS POSITION_SEND, @CREATE_DT AS A1 END --CHECK XEM CÓ K/PB/TT NHẬN không, không có thì lấy BRANCH DECLARE @NHAN NVARCHAR(500) IF(@KHOI_RECIVE IS NOT NULL AND @DEP_RECIVE IS NOT NULL AND @TT_RECIVE IS NOT NULL) BEGIN SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_RECIVE) END ELSE IF(@KHOI_RECIVE IS NULL AND @DEP_RECIVE IS NULL AND @TT_RECIVE IS NULL) BEGIN SET @NHAN = (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_RECIVE) END ELSE IF(@KHOI_RECIVE IS NULL AND @DEP_RECIVE IS NULL AND @TT_RECIVE IS NOT NULL) BEGIN SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @TT_RECIVE) END ELSE IF(@KHOI_RECIVE IS NULL AND @DEP_RECIVE IS NOT NULL AND @TT_RECIVE IS NULL) BEGIN SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @DEP_RECIVE) END ELSE IF(@KHOI_RECIVE IS NOT NULL AND @DEP_RECIVE IS NULL AND @TT_RECIVE IS NULL) BEGIN SET @NHAN = (SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_ID = @KHOI_RECIVE) END -- THÔNG TIN BÊN NHẬN Table2 SELECT IIF((SELECT CB.BRANCH_TYPE FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_RECIVE) = 'HS', (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_RECIVE) + ' - ' + ISNULL(@NHAN,''), (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_RECIVE)) AS KHOI_RECEIVE, (SELECT CD.BRANCH_NAME FROM CM_BRANCH CD WHERE CD.BRANCH_ID = @BRANCH_RECIVE) AS BRANCH_RECIVE, @NAME_RECIVE AS NAME_RECIVE, @ADDR_RECIVE AS ADDR_RECIVE, @PHONE_RECIVE AS PHONE_RECIVE, @POSITION_RECIVE AS POSTION_RECIVE -- CHỮ KÍ DECLARE @S1 NVARCHAR(500),@S2 NVARCHAR(500),@S3 NVARCHAR(500),@S4 NVARCHAR(500),@S5 NVARCHAR(500),@S6 NVARCHAR(500) SET @S1 = (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_MAKER_ID) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_CREATE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') SET @S2 = N'Người duyệt'+ CHAR(10) + (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_APPROVE_ID) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') --PHONGNT bổ sung người nhập KT SET @S3 =N'Người nhập'+ CHAR(10) + (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_MAKER_ID_KT) + CHAR(10) SET @S6 =N'Người duyệt'+ CHAR(10) + (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_CHECKER_ID_KT) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_APPROVE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'') SET @S4 =(SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_RECEVI_MAKER_ID) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_RECEVI_DT,'dd/MM/yyyy,hh:mm:ss tt'),'') --PHONGNT bổ sung người nhập SET @S5 =N'Người nhập'+ CHAR(10) + (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_MAKER_ID_CREATE) + CHAR(10) ; -- Table-3 Chữ ký SELECT @NAME_TRANSFER AS S1, ISNULL(@POS_TRANSFER, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(@TRANSFER_CONFIRM_DT, 'dd/MM/yyyy HH:mm:ss') AS S1_POS, @SIGN_QLTS_NAME AS S2, ISNULL(@SIGN_QLTS_POS, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(@SIGN_QLTS_DT, 'dd/MM/yyyy HH:mm:ss') AS S2_POS, --'' AS S2, --'' AS S2_POS, @NAME_RECIVE AS S3,@S4 AS S4, ISNULL(@POS_RECIVE,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT(@RECEIVE_CONFIRM_DT, 'dd/MM/yyyy HH:mm:ss') AS S3_POS, IIF(NOT EXISTS(SELECT 1 FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID=@TRANS_MULTI_MASTER_ID AND AUTH_STATUS ='E'),@S5,NULL) AS S5, @S6 AS S6 -- TABLE4 thông tin trung gian *(nếu có) SELECT @SIGN_QLTS_BR+ ' - ' + ISNULL(@SIGN_QLTS_DEP,'') AS KHOI_MID, @SIGN_QLTS_BR AS BRANCH_MID, @SIGN_QLTS_NAME AS MID_NAME, @SIGN_QLTS_ADDR AS MID_ADDR, @SIGN_QLTS_PHONE AS MID_PHONE, @SIGN_QLTS_POS AS MID_POS --SELECT '' AS MID_NAME, '' AS MID_ADDR, '' AS MID_PHONE, '' AS MID_POS --TABLE 5 NỘI DUNG BIÊN BẢN SELECT TRANSFER_CONTENT AS TITLE FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID --TABLE 6 SỐ SELECT @TRANS_MULTI_MASTER_ID AS NO SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_RECIVE