ALTER PROCEDURE dbo.ASS_AMORT_BackDate
  @p_ASSET_ID VARCHAR(20),
  @p_MAKER_ID VARCHAR(100),
  @p_CHECKER_ID VARCHAR(100),
  @p_APPROVE_DT DATETIME,
  @p_TRN_ID VARCHAR(20),
  @p_TRN_REF_NO VARCHAR(20),
  @p_TRN_TYPE VARCHAR(50)
AS
DECLARE @p_ASSET_TYPE VARCHAR(25),@sExecDT DATETIME,@l_LAST_AMORT_ID VARCHAR(25)
	declare @l_AMORT_ID varchar(15), @l_AMORTDT_ID varchar(15)
	declare @l_AMORT_TERM nvarchar(50) = ''
	declare @l_ASSET_ID varchar(15)
	declare @l_AMORTIZED_AMT decimal(18,0)
	declare @l_AMORTIZED_MONTH int
	declare @l_AMORT_AMT decimal(18,0)
	declare @l_AMORT_MONTH decimal(18,2)
	declare @l_AMORT_END_DATE datetime, @l_AMORT_START_DATE datetime
	declare @l_FIRST_AMORT_AMT decimal(18,0), @l_MONTHLY_AMORT_AMT decimal(18,0)
	declare @l_BRANCH_ID varchar(15)
	declare @l_DEP_ID varchar(15)	
	declare @l_BRANCH_ID_DO varchar(15) = NULL, @l_BRANCH_TYPE_DO VARCHAR(15)
	declare @l_CURR_AMORT_AMT decimal(18,0)
	declare @l_TOTAL_AMT decimal(18,0)
	declare @l_CURR_AMORT_STATUS nvarchar(100)
	declare @l_DESC nvarchar(1000)
	declare @l_BUY_DATE DATETIME,@l_GROUP_ID VARCHAR(20)
	DECLARE @l_AMORT_RUN_DATE DATETIME = (SELECT TOP 1 TTS.SENT_DATE FROM THREAD_TIME_SEND TTS WHERE TTS.TIME_VALUE = 'KH')

		 SELECT @l_ASSET_ID=A.ASSET_ID,@l_AMORTIZED_AMT= A.AMORTIZED_AMT,@l_AMORTIZED_MONTH= A.AMORTIZED_MONTH,
		@l_AMORT_AMT	=	A.AMORT_AMT,  @l_AMORT_MONTH=A.AMORT_MONTH,@l_AMORT_END_DATE= A.AMORT_END_DATE,@l_AMORT_START_DATE= A.AMORT_START_DATE,
			@l_FIRST_AMORT_AMT=	A.FIRST_AMORT_AMT,@l_MONTHLY_AMORT_AMT= A.MONTHLY_AMORT_AMT,@l_BRANCH_ID= A.BRANCH_ID,@l_DEP_ID= A.DEPT_ID,@l_BUY_DATE= A.BUY_DATE
		  ,@l_GROUP_ID=A.GROUP_ID
    FROM ASS_MASTER A
		 WHERE A.ASSET_ID =@p_ASSET_ID


DECLARE @DATE_AMORT INT = DAY(@l_AMORT_RUN_DATE)
DECLARE @MONTH_AMORT INT= MONTH(@l_AMORT_RUN_DATE)

DECLARE @FIRST_DATE_AMORT DATETIME =(CONVERT(VARCHAR(10),(YEAR(@l_AMORT_START_DATE))) + '-' + CONVERT(VARCHAR(10),MONTH(@l_AMORT_START_DATE))+'-' + CONVERT(VARCHAR(10),DAY(@l_AMORT_RUN_DATE)) )

IF(@FIRST_DATE_AMORT <= @l_AMORT_START_DATE)
  SET @FIRST_DATE_AMORT=@l_AMORT_START_DATE

SET @sExecDT=@FIRST_DATE_AMORT

DECLARE @l_MONTH_BACKDATE DECIMAL = DATEDIFF(MONTH,  @l_AMORT_START_DATE, @l_AMORT_RUN_DATE)


	WHILE  CAST(@sExecDT AS DATE)  <	 CAST(@l_AMORT_RUN_DATE AS DATE) AND   dbo.FN_GetLastDayOfMonth(@sExecDT) <= dbo.FN_GetLastDayOfMonth(@l_AMORT_END_DATE)
	BEGIN

        

    	
		IF (@l_AMORTIZED_MONTH = 0) --Khau hao thang dau tien
		BEGIN
			SET @l_CURR_AMORT_AMT = @l_FIRST_AMORT_AMT
				SET @l_CURR_AMORT_STATUS = 'DKH'
		END
		ELSE IF dbo.FN_GetLastDayOfMonth(@sExecDT) = dbo.FN_GetLastDayOfMonth(@l_AMORT_END_DATE) --Ky khau hao cuoi cung
		BEGIN
			SET @l_CURR_AMORT_AMT = @l_AMORT_AMT - @l_AMORTIZED_AMT
				SET @l_CURR_AMORT_STATUS = 'KHX'		
			print 'last amort'	+ @l_ASSET_ID
		END
		ELSE --tai san dang khau hao binh thuong
		BEGIN
			SET @l_CURR_AMORT_AMT = @l_MONTHLY_AMORT_AMT
			SET @l_CURR_AMORT_STATUS = 'DKH'
			--THIEUVQ THEM DK KHONG KHAU HAO AM 13112017
			IF (@l_AMORTIZED_AMT + @l_CURR_AMORT_AMT) > @l_AMORT_AMT
				SET @l_CURR_AMORT_AMT = @l_AMORT_AMT - @l_AMORTIZED_AMT
		END
        
		--Insert to bang ASS_AMORT_DT
		EXEC SYS_CodeMasters_Gen 'ASS_AMORT_DT', @l_AMORTDT_ID out
	
		INSERT INTO ASS_AMORT_DT
		(
			AMORTDT_ID,
			AMORT_ID,
			AMORT_DT,
			CRDR,
			BRANCH_ID,
			DEP_ID,
			ASSET_ID,
			AMORT_AMT,
			GROUP_ID,
			EXECUTED,
			TRN_TYPE,
			TRN_DESC
		)
		VALUES
		(
			@l_AMORTDT_ID,
			@l_AMORT_ID,
			@sExecDT,
			'C',
			@l_BRANCH_ID,
			@l_DEP_ID,
			@l_ASSET_ID,
			@l_CURR_AMORT_AMT,
			NULL, --Khong chay khau hao theo group
			'1',
			'AMORT',
			N'Khấu hao lùi'
		)
    

    UPDATE ASS_MASTER 
		SET AMORTIZED_AMT = isnull(AMORTIZED_AMT, 0) + @l_CURR_AMORT_AMT,
		AMORTIZED_MONTH = isnull(AMORTIZED_MONTH, 0) + 1,
		AMORT_STATUS = @l_CURR_AMORT_STATUS
		WHERE ASSET_ID = @l_ASSET_ID

    SET @sExecDT= DATEADD(MONTH,1,@sExecDT)
		SET @l_AMORTIZED_AMT= @l_AMORTIZED_AMT + @l_CURR_AMORT_AMT
    SET @l_AMORTIZED_MONTH = @l_AMORTIZED_MONTH + 1
	END

---- Hạch toán

  DECLARE @l_BRANCH_CODE VARCHAR(25), @l_ASSET_CODE VARCHAR(25),@l_ETP_ID VARCHAR(25),@l_ET_ID VARCHAR(25), @l_AMT_OVER DECIMAL(18,0),@DEP_CODE VARCHAR(25)
 declare @l_EXP_GL varchar(50)
	DECLARE @l_AMORT_GL varchar(50)
  declare @l_DO_BRANCH_ID varchar(15)= NULL
  DECLARE @l_DESC_CORE nvarchar(200)
  	DECLARE @l_DR_BRN_ID varchar(15), @l_CR_BRN_ID varchar(15),@l_TYPE_ID VARCHAR(25),@l_AMT_UNDER  DECIMAL(18,0)
    
 SET @l_BRANCH_CODE = (SELECT cb.BRANCH_CODE FROM CM_BRANCH cb WHERE cb.BRANCH_ID=@l_BRANCH_ID)
SET @l_ASSET_CODE =(SELECT am.ASSET_CODE FROM ASS_MASTER am WHERE am.ASSET_ID=@l_ASSET_ID)
SET @l_AMORTIZED_AMT =(SELECT SUM(aad.AMORT_AMT) FROM ASS_AMORT_DT aad  WHERE aad.ASSET_ID=@l_ASSET_ID)

SET @DEP_CODE= (SELECT cd.DEP_CODE FROM CM_DEPARTMENT cd WHERE cd.DEP_ID=@l_DEP_ID)
		


	SELECT @l_EXP_GL = G.EXP_ACCTNO, @l_AMORT_GL = G.AMORT_ACCTNO,@l_TYPE_ID = G.[TYPE_ID]
		FROM ASS_GROUP G
		WHERE G.GROUP_ID = @l_GROUP_ID




IF(@l_AMORT_AMT > 1600000000)
BEGIN
    SET @l_AMT_UNDER = ROUND(@l_AMORTIZED_AMT * (1600000000/@l_AMORT_AMT),0)
    SET @l_AMT_OVER = @l_AMORTIZED_AMT- @l_AMT_UNDER
END
ELSE
BEGIN
    SET @l_AMT_UNDER=@l_AMORTIZED_AMT
    SET @l_AMT_OVER=0
END





EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST', @l_ET_ID out
		INSERT INTO ASS_ENTRIES_POST
		(
			ET_ID,REF_NO, ASSET_ID, TRN_TYPE, REF_ID,DO_BRN, CR_ACCT, CR_BRN, DR_ACCT, DR_BRN, AMT,EXP_TO_CORE, TRN_DATE, TRN_DESC, MAKER_ID, CHECKER_ID,TRN_ID
		)
		VALUES
		(
			@l_ET_ID,@p_TRN_REF_NO, @l_ASSET_ID, @p_TRN_TYPE, @l_AMORTDT_ID,@l_DO_BRANCH_ID, dbo.fn_replace_acctno(@l_AMORT_GL,@l_CR_BRN_ID), @l_CR_BRN_ID, @l_EXP_GL, @l_DR_BRN_ID, @l_AMORT_AMT-@l_AMT_OVER,'Y',
			@sExecDT, @l_DESC_CORE, @p_MAKER_ID, @p_CHECKER_ID,@p_TRN_ID
		)
        EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST_SYNC', @l_ETP_ID OUT
          INSERT INTO ASS_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO)
        VALUES (@l_ETP_ID, @l_ET_ID,@p_TRN_ID ,@l_BRANCH_CODE , @l_AMORT_GL, 'VND', 'C', @l_AMT_UNDER,1, @l_AMT_UNDER, @l_ASSET_CODE,@p_APPROVE_DT, @p_MAKER_ID, @p_CHECKER_ID, @DEP_CODE, @l_DESC_CORE,@p_TRN_REF_NO);

         EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST_SYNC', @l_ETP_ID out

         INSERT INTO ASS_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO)
         VALUES (@l_ETP_ID, @l_ET_ID, @p_TRN_ID,@l_BRANCH_CODE ,@l_EXP_GL,'VND', 'D',@l_AMT_UNDER, 1,@l_AMT_UNDER, @l_ASSET_CODE,@p_APPROVE_DT, @p_MAKER_ID, @p_CHECKER_ID, @DEP_CODE, @l_DESC_CORE,@p_TRN_REF_NO);


IF(@l_AMT_OVER>0)
BEGIN
EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST', @l_ET_ID out
  	INSERT INTO ASS_ENTRIES_POST
		(
			ET_ID,REF_NO, ASSET_ID, TRN_TYPE, REF_ID,DO_BRN, CR_ACCT, CR_BRN, DR_ACCT, DR_BRN, AMT,EXP_TO_CORE, TRN_DATE, TRN_DESC, MAKER_ID, CHECKER_ID,TRN_ID
		)
		VALUES
		(
			@l_ET_ID,@p_TRN_REF_NO, @l_ASSET_ID, @p_TRN_TYPE, @l_AMORTDT_ID,@l_DO_BRANCH_ID, dbo.fn_replace_acctno(@l_AMORT_GL,@l_CR_BRN_ID), @l_CR_BRN_ID, @l_EXP_GL, @l_DR_BRN_ID, @l_AMT_OVER,'Y',
			@sExecDT, @l_DESC_CORE, @p_MAKER_ID, @p_CHECKER_ID,@p_TRN_ID
		)
        EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST_SYNC', @l_ETP_ID OUT
          INSERT INTO ASS_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO)
        VALUES (@l_ETP_ID, @l_ET_ID,@p_TRN_ID ,@l_BRANCH_CODE , @l_AMORT_GL, 'VND', 'C', @l_AMT_OVER,1, @l_AMT_OVER, @l_ASSET_CODE,@sExecDT, @p_MAKER_ID, @p_CHECKER_ID, @DEP_CODE, @l_DESC_CORE,@p_TRN_REF_NO);

         EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST_SYNC', @l_ETP_ID out

         INSERT INTO ASS_ENTRIES_POST_SYNC (ETP_ID, ET_ID, TRN_ID, AC_BRANCH, AC_NO, AC_CCY, DRCR_IND, FCY_AMOUNT, EXCH_RATE, LCY_AMOUNT, RELATED_REFERENCE, TRN_DT, USER_ID, AUTH_ID, DEPT_CD, DESC_TRANS,TRN_NO)
         VALUES (@l_ETP_ID, @l_ET_ID, @p_TRN_ID,@l_BRANCH_CODE ,'871001005','VND', 'D', @l_AMT_OVER, 1, @l_AMT_OVER, @l_ASSET_CODE,@sExecDT, @p_MAKER_ID, @p_CHECKER_ID, @DEP_CODE, @l_DESC_CORE,@p_TRN_REF_NO);
END