DECLARE @BRANCH_CODE VARCHAR(20) = '', @DEP_CODE VARCHAR(20) = '' DECLARE cur CURSOR FOR SELECT A.BRANCH_CODE, ISNULL(B.DEP_CODE,'') FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num,VALUE AS BRANCH_CODE FROM STRING_SPLIT('1800,1400,1400,0719,0731,2200,2200,0900,0900,0900,1902,0717,2600,2600,2600,2500,2500,0903,0902,0901,0703,0700,2300,2300,3000,0726,0726,1801,2000,0500',',')) AS A LEFT JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num,VALUE AS DEP_CODE FROM STRING_SPLIT(',,,,,,,,,,,,,,,,,,,,,,,,,,,,,05P24',',')) AS B ON A.row_num = B.row_num OPEN cur FETCH NEXT FROM cur INTO @BRANCH_CODE,@DEP_CODE WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @ASSET_CODE VARCHAR(20) = (SELECT TOP 1 AM.ASSET_CODE FROM ASS_MASTER AM WHERE AM.CURRENT_TRANS IS NULL AND AM.DEPT_ID = 'DEP000000000048') DECLARE @p_BRANCH_ID VARCHAR(20), @p_TRANSFER_DT VARCHAR(20) = '31/03/2023', @p_USER_TRANSFER varchar(200) = 'nghianh', @p_NOTES nvarchar(1000), @p_RECORD_STATUS varchar(1) = '1', @p_AUTH_STATUS varchar(1) = 'E', @p_MAKER_ID varchar(200) = 'nghianh', @p_CREATE_DT VARCHAR(20) = '31/03/2023', @p_APPROVE_DT VARCHAR(20) = '31/03/2023', @p_CHECKER_ID varchar(100) = NULL, @p_AUTH_STATUS_KT varchar(15) = NULL, @p_CREATE_DT_KT varchar(100) = NULL, @p_APPROVE_DT_KT VARCHAR(20) = NULL, @p_MAKER_ID_KT varchar(100) = NULL, @p_CHECKER_ID_KT varchar(100) = NULL, @p_BRANCH_CREATE varchar(15) = 'DV0001', @p_TRANSFER_CONTENT NVARCHAR(MAX) = N'Bàn giao tài sản điều chuyển cho chi nhánh' DECLARE @ASSET_ID varchar(15), @KHOI_ID varchar(15), @CENTER_ID varchar(15), @DEPT_ID varchar(15), @EMP_ID varchar(15), @LOCATION varchar(500), @DESCRIPTION nvarchar(1000), @BRANCH_ID_OLD varchar(15), @KHOI_ID_OLD varchar(15), @CENTER_ID_OLD varchar(15), @DEPT_ID_OLD varchar(15), @EMP_ID_OLD varchar(15), @REMAIN_VALUE DECIMAL(18,0), @BUY_PRICE DECIMAL(18,0), @DEPT_CREATE VARCHAR(15) IF(@DEP_CODE <> '') BEGIN SELECT @KHOI_ID = (CASE WHEN DP.TYPE = 'K' THEN DP.DEP_ID ELSE K.DEP_ID END), @CENTER_ID = (CASE WHEN DP.TYPE = 'TT' THEN DP.DEP_ID ELSE TT.DEP_ID END), @DEPT_ID = (CASE WHEN DP.TYPE = 'PB' THEN DP.DEP_ID ELSE NULL END) FROM CM_DEPARTMENT DP LEFT JOIN CM_DEPARTMENT TT ON TT.DEP_ID = DP.FATHER_ID LEFT JOIN CM_DEPARTMENT K ON K.DEP_ID = DP.KHOI_ID WHERE DP.DEP_CODE = @DEP_CODE END SET @p_BRANCH_ID = (SELECT CB.BRANCH_ID FROM CM_BRANCH CB WHERE CB.BRANCH_CODE = @BRANCH_CODE) SELECT @ASSET_ID = AM.ASSET_ID, @BRANCH_ID_OLD = AM.BRANCH_ID, @KHOI_ID_OLD = CASE WHEN DP.TYPE = 'K' THEN DP.DEP_ID ELSE K.DEP_ID END, @CENTER_ID_OLD = CASE WHEN DP.TYPE = 'TT' THEN DP.DEP_ID ELSE TT.DEP_ID END, @DEPT_ID_OLD = CASE WHEN DP.TYPE = 'PB' THEN DP.DEP_ID ELSE NULL END, @EMP_ID_OLD = AM.EMP_ID, @LOCATION = AM.LOCATION, @DESCRIPTION = AM.ASSET_DESC, @REMAIN_VALUE = (ISNULL(AM.AMORT_AMT,0) - ISNULL(AM.AMORTIZED_AMT,0)), @BUY_PRICE = AM.BUY_PRICE FROM ASS_MASTER AM LEFT JOIN CM_DEPARTMENT DP ON DP.DEP_ID = AM.DEPT_ID LEFT JOIN CM_DEPARTMENT TT ON TT.DEP_ID = DP.FATHER_ID LEFT JOIN CM_DEPARTMENT K ON K.DEP_ID = DP.KHOI_ID WHERE AM.ASSET_CODE = @ASSET_CODE AND (AM.CURRENT_TRANS IS NULL OR AM.CURRENT_TRANS = '') IF(@DEP_CODE <> '') SET @p_NOTES = N'Điều chuyển tài sản ' +@ASSET_CODE+ N' từ ' + ISNULL((SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_ID_OLD),'') + ' - ' + ISNULL((SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_CODE = @DEPT_ID_OLD),'') + N' sang chi nhánh ' + (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @p_BRANCH_ID) + ' - ' + ISNULL((SELECT CD.DEP_NAME FROM CM_DEPARTMENT CD WHERE CD.DEP_CODE = @DEP_CODE),'') IF(@DEP_CODE = '') SET @p_NOTES = N'Điều chuyển tài sản ' +@ASSET_CODE+ N' từ ' + ISNULL((SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @BRANCH_ID_OLD),'') + N' sang chi nhánh ' + (SELECT CB.BRANCH_NAME FROM CM_BRANCH CB WHERE CB.BRANCH_ID = @p_BRANCH_ID) SET @DEPT_CREATE = (SELECT SECUR_CODE FROM dbo.TL_USER WHERE TLNANME = @p_MAKER_ID) DECLARE @l_TRANS_MULTI_MASTER_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'ASS_TRANSFER_MULTI_MASTER', @l_TRANS_MULTI_MASTER_ID out INSERT INTO ASS_TRANSFER_MULTI_MASTER([TRANS_MULTI_MASTER_ID],[BRANCH_ID],[TRANSFER_DT],[USER_TRANSFER],[NOTES],[RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],[REPORT_STATUS],[DEPT_CREATE], TRANSFER_CONTENT, STATUS) VALUES(@l_TRANS_MULTI_MASTER_ID ,@p_BRANCH_CREATE ,CONVERT(DATETIME, @p_TRANSFER_DT, 103) ,@p_USER_TRANSFER ,@p_NOTES ,@p_RECORD_STATUS ,@p_AUTH_STATUS ,@p_MAKER_ID ,CONVERT(DATETIME, @p_CREATE_DT, 103) ,@p_CHECKER_ID ,CONVERT(DATETIME, @p_APPROVE_DT, 103) ,'N',@DEPT_CREATE, @p_TRANSFER_CONTENT, 'ADDNEW') DECLARE @l_TRANSFER_MULTI_ID VARCHAR(15) EXEC SYS_CodeMasters_Gen 'ASS_TRANSFER_MULTI_DT', @l_TRANSFER_MULTI_ID out INSERT INTO ASS_TRANSFER_MULTI_DT( [TRANSFER_MULTI_ID], [TRANS_MULTI_MASTER_ID], [ASSET_ID], [BRANCH_ID], [KHOI_ID], [CENTER_ID], [DEPT_ID], [EMP_ID], [BRANCH_ID_OLD], [KHOI_ID_OLD], [CENTER_ID_OLD], [DEPT_ID_OLD], [EMP_ID_OLD], [USE_START_DT], [DESCRIPTION], [LOCATION], [RECORD_STATUS], [AUTH_STATUS], [MAKER_ID], [CREATE_DT], [CHECKER_ID], [APPROVE_DT], [BRANCH_CREATE], [REPORT_STATUS], REMAIN_VALUE, BUY_PRICE ) VALUES( @l_TRANSFER_MULTI_ID, @l_TRANS_MULTI_MASTER_ID, @ASSET_ID, @p_BRANCH_ID, @KHOI_ID, @CENTER_ID, @DEPT_ID, @EMP_ID, @BRANCH_ID_OLD, @KHOI_ID_OLD, @CENTER_ID_OLD, @DEPT_ID_OLD, @EMP_ID_OLD, CONVERT(DATETIME, @p_TRANSFER_DT, 103), @DESCRIPTION, @LOCATION, @p_RECORD_STATUS, @p_AUTH_STATUS, @p_MAKER_ID, CONVERT(DATETIME, @p_CREATE_DT, 103), @p_CHECKER_ID, CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_BRANCH_CREATE , 'N', @REMAIN_VALUE, @BUY_PRICE ) INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID, DEP_ID) VALUES(@l_TRANS_MULTI_MASTER_ID,'ADDNEW','C','QLTS','DV0001','',@DEPT_CREATE) INSERT INTO PL_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],[STATUS],ROLE_USER,BRANCH_ID,PARENT_PROCESS_ID, DEP_ID) VALUES(@l_TRANS_MULTI_MASTER_ID,'QLTS_D','U','KSV_QLTS','DV0001','ADDNEW',@DEPT_CREATE) EXEC ASS_TRANSFER_MULTI_Master_SendAppr @p_TRANSFER_MULTI_ID = @l_TRANS_MULTI_MASTER_ID ,@p_TYPE_APP = N'S_TDV' ,@p_USER_LOGIN = 'nghianh' ,@p_DESC = N'' EXEC ASS_TRANSFER_MULTI_MASTER_App @p_TRANS_MULTI_MASTER_ID = @l_TRANS_MULTI_MASTER_ID ,@p_AUTH_STATUS = 'A' ,@p_CHECKER_ID = 'bichnn' ,@p_DESC = N'Chấp thuận' ,@p_APPROVE_DT = '31/03/2023' EXEC ASS_TRANSFER_MULTI_Master_SendAppr @p_TRANSFER_MULTI_ID = @l_TRANS_MULTI_MASTER_ID ,@p_TYPE_APP = N'CONFIRM' ,@p_USER_LOGIN = 'bichnn' ,@p_DESC = N'' FETCH NEXT FROM cur INTO @BRANCH_CODE,@DEP_CODE END CLOSE cur DEALLOCATE cur