AMS - VB

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