CREATE PROCEDURE dbo.ASS_AMORT_Start @p_AMORT_ID varchar(15) = NULL AS --declare @l_CURR_AMORT_AMT decimal(18,0) declare @l_CURR_AMORT_STATUS nvarchar(100) declare @l_EXP_GL varchar(50) DECLARE @l_AMORT_GL varchar(50) declare @l_GROUP_ID varchar(1
5) declare @l_ASSET_ID varchar(15) declare @l_AMORT_AMT decimal(18,0) declare @l_BRANCH_ID varchar(15) declare @l_AMORTIZED_MONTH DECIMAL(18,2) declare @l_ET_ID varchar(15) declare @l_AMORTDT_ID varchar(15) declare @l_AMORT_END_DATE datetime dec
lare @sToday varchar(10) = convert(varchar(10), getdate(), 103) declare @sExecDT datetime declare @l_TRN_REF_NO varchar(20) = NULL declare @l_DO_BRANCH_ID varchar(15)= NULL DECLARE @l_TYPE_ID varchar(15) = NULL DECLARE @l_DR_BRN_ID varchar(15), @l
_CR_BRN_ID varchar(15) DECLARE @l_HO_BRN_ID varchar(15) DECLARE @l_DEP_ID varchar(15) DECLARE @l_MAKER_ID varchar(15), @l_CHECKER_ID varchar(15), @l_BRANCH_ID_DO varchar(20), @l_ASSET_TYPE_DO varchar(15), @l_BRANCH_TYPE_DO varchar(15) DECLARE @l
_ASSET_TYPE VARCHAR(15), @l_DESC_CORE nvarchar(200),@l_AMORT_TERM varchar(20) --thieuvq them loai tai san de dien giai hach toan --Put valodation here --Phong added for cost allocate starts :: 1/11/2018 declare @l_AMORTCOSTDT_ID varchar(15) declare
@l_Cost decimal(18,0) declare @l_COSTDT_ID varchar(15) declare @l_COST_EXP_GL varchar(50) declare @l_COST_BRANCH_ID varchar(15),@l_AMORT_START_DATE datetime --Phong added for cost allocate ends :: 1/11/2018 --get data select @sExecDT = EXECUTE_D
T, @l_MAKER_ID = MAKER_ID, @l_CHECKER_ID = CHECKER_ID, @l_AMORT_TERM = AMORT_TERM, -- @l_BRANCH_ID_DO = BRANCH_ID, @l_ASSET_TYPE_DO = ASSET_TYPE--thieuvq them 01/08/2014 -- FROM ASS_AMORT where AMORT_ID = @p_AMORT_ID SET @l_AMORT_TERM = REPLACE(@l
_AMORT_TERM,'/',' ') --Lay branch_id cua user duyet select @l_DO_BRANCH_ID = a.TLSUBBRID from TL_USER a where a.TLNANME = (SELECT MAKER_ID FROM ASS_AMORT WHERE AMORT_ID = @p_AMORT_ID) --BRN_ID HOI SO SELECT @l_HO_BRN_ID = BRANCH_ID FROM CM_BRANCH
A WHERE A.BRANCH_TYPE = 'HS' --lay branch type cua don vi thuc hien SELECT @l_BRANCH_TYPE_DO = BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID = @l_BRANCH_ID_DO --oepn cursor declare pCur cursor for SELECT A.AMORTDT_ID, A.ASSET_ID, A.BRANCH_ID, A.DE
P_ID, A.AMORT_AMT, AM.GROUP_ID, AM.AMORTIZED_MONTH, AM.AMORT_END_DATE, AM.[TYPE_ID], AM.AMORT_START_DATE FROM ASS_AMORT_DT A LEFT JOIN ASS_MASTER AM ON AM.ASSET_ID = A.ASSET_ID WHERE AMORT_ID = @p_AMORT_ID AND A.EXECUTED = '0' open pCur
DECLARE @SOKY INT = 1 DECLARE @BeginDate DATE = CONVERT(DATE,(CONVERT(VARCHAR(10),YEAR(@sExecDT))+'-'+CONVERT(VARCHAR(4),MONTH(@sExecDT)) +'-1')) --SELECT @BeginDate BEGIN TRANSACTION --chay khau hao FETCH NEXT FROM pCur INTO @l_AMORTDT_ID, @l_ASSET
_ID, @l_BRANCH_ID, @l_DEP_ID, @l_AMORT_AMT, @l_GROUP_ID, @l_AMORTIZED_MONTH, @l_AMORT_END_DATE, @l_ASSET_TYPE, @l_AMORT_START_DATE WHILE @@FETCH_STATUS = 0 BEGIN --lay thong tin tai khoan SELECT @l_EXP_GL = G.EXP_ACCTNO, @l_AMORT_GL = G.AMOR
T_ACCTNO,@l_Type_ID = G.[TYPE_ID] FROM ASS_GROUP G WHERE G.GROUP_ID = @l_GROUP_ID IF @@ERROR<>0 GOTO ABORT SET @SOKY = 1 IF (@l_AMORTIZED_MONTH = 0) --Khau hao thang dau tien BEGIN SET @l_CURR_AMORT_STATUS = 'DKH' --------THIEUVQ KHA
U HAO LUI 15012019----------------- --------BEGIN IF @l_AMORT_START_DATE < @BeginDate BEGIN --TINH SO KY KHAO HAO --SELECT DATEDIFF(MONTH,'2018-11-10','2019-1-1') SET @SOKY = DATEDIFF(MONTH, @l_AMORT_START_DATE, @BeginDate) + 1 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' END END --------END
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' END ELSE --tai san dang
khau hao binh thuong BEGIN SET @l_CURR_AMORT_STATUS = 'DKH' END --Insert vao bang dbo.ASS_ENTRIES_POST EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST', @l_ET_ID out IF @l_ET_ID='' OR @l_ET_ID IS NULL GOTO ABORT --ref_no de ben core tra
ve thieuvq - 071118 --EXEC ASS_TRN_REF_NO_Gen @l_Type_ID, @sToday, @l_TRN_REF_NO out --IF @l_BRANCH_ID = @l_HO_BRN_ID --BEGIN -- SET @l_DR_BRN_ID = @l_DEP_ID --END --ELSE --BEGIN SET @l_DR_BRN_ID = @l_BRANCH_ID --END --thieuvq d
ien giai hach toan khau hao IF @l_ASSET_TYPE = 'TSCD' BEGIN SET @l_DESC_CORE = 'KHAU HAO TSCD KY ' + @l_AMORT_TERM SET @l_CR_BRN_ID = @l_BRANCH_ID--@l_HO_BRN_ID END ELSE BEGIN SET @l_DESC_CORE = 'PHAN BO CHI PHI CCLD KY ' + @l_AMORT_TER
M SET @l_CR_BRN_ID = @l_BRANCH_ID END 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,@l_TRN_REF_NO, @l_ASSET_ID, 'AMORT', @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,'Y', @sExecDT, @l_DESC_CORE, @l_MAKER_ID, @l_CHECKER_ID,@p_AMORT_ID ) IF
@@Error <> 0 GOTO ABORT --- Phong added for cost allocate starts :: 1/11/2018 IF EXISTS(SELECT 1 FROM ASS_AMORT_COST_DT A WHERE A.AMORTDT_ID = @l_AMORTDT_ID) BEGIN --update lai chi phi phan bo cho don vi chinh IF EXISTS(SELECT 1 FROM ASS_
AMORT_COST_DT WHERE AMORTDT_ID = @l_AMORTDT_ID AND BRANCH_ID = @l_BRANCH_ID/*THIEUVQ THEM DK IS NULL NAY 140519*/AND COSTDT_ID IS NULL AND EXECUTED = '0') BEGIN SELECT @l_Cost = ISNULL(SUM(COST_AMT),0) FROM ASS_AMORT_COST_DT
WHERE AMORTDT_ID = @l_AMORTDT_ID AND BRANCH_ID = @l_BRANCH_ID/*THIEUVQ THEM DK IS NULL NAY 140519*/AND COSTDT_ID IS NULL AND EXECUTED = '0' END ELSE BEGIN SET @l_Cost = 0 END UPDATE ASS_ENTRIES_POST SET AMT = @l_Cost W
HERE ET_ID = @l_ET_ID IF @@Error <> 0 GOTO ABORT --hach toan Phan bo chi phi declare pCurCost cursor for SELECT a.AMORTCOSTDT_ID, a.COSTDT_ID, A.COST_AMT, A.BRANCH_ID, B.EXP_ACCTNO FROM ASS_AMORT_COST_DT A INNER JOIN ASS_COST_ALLOC
_DT B ON A.COSTDT_ID = B.COSTDT_ID WHERE AMORTDT_ID = @l_AMORTDT_ID /*THIEUVQ SUA DIEU KIEN 140519*/AND A.COSTDT_ID IS NOT NULL--AND A.BRANCH_ID <> @l_BRANCH_ID AND EXECUTED = '0' open pCurCost FETCH NEXT FROM pCurCost INTO @l_AMORTCOS
TDT_ID, @l_COSTDT_ID, @l_Cost, @l_COST_BRANCH_ID, @l_COST_EXP_GL WHILE @@FETCH_STATUS = 0 BEGIN EXEC SYS_CodeMasters_Gen 'ASS_ENTRIES_POST', @l_ET_ID out IF @l_ET_ID='' OR @l_ET_ID IS NULL GOTO ABORT 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,@l_TRN_REF_NO, @l_ASSET_ID, 'AMORT', @l_AMORTDT_ID,@l_DO_BRAN
CH_ID, dbo.fn_replace_acctno(@l_AMORT_GL,@l_CR_BRN_ID), @l_CR_BRN_ID, @l_COST_EXP_GL, /*@l_DR_BRN_ID*/ @l_COST_BRANCH_ID,@l_Cost/* @l_AMORT_AMT*/,'Y', @sExecDT, @l_DESC_CORE, @l_MAKER_ID, @l_CHECKER_ID,@p_AMORT_ID ) IF @@Error <> 0 GOTO ABORT
UPDATE ASS_COST_ALLOC_DT SET COSTED_MONTH = ISNULL(COSTED_MONTH,0) + @SOKY, COSTED_AMT = ISNULL(COSTED_AMT,0) + @l_Cost WHERE COSTDT_ID = @l_COSTDT_ID UPDATE ASS_AMORT_COST_DT SET EXECUTED = '1' WHERE AMORTCOSTDT_ID = @l_A
MORTCOSTDT_ID FETCH NEXT FROM pCurCost INTO @l_AMORTCOSTDT_ID, @l_COSTDT_ID, @l_Cost, @l_COST_BRANCH_ID, @l_COST_EXP_GL END CLOSE pCurCost DEALLOCATE pCurCost END --- Phong added for cost allocate ends :: 1/11/2018 --UPDATE BA
NG ASS_MASTER UPDATE ASS_MASTER SET AMORTIZED_AMT = isnull(AMORTIZED_AMT, 0) + @l_AMORT_AMT, AMORTIZED_MONTH = isnull(AMORTIZED_MONTH, 0) + @SOKY, AMORT_STATUS = @l_CURR_AMORT_STATUS WHERE ASSET_ID = @l_ASSET_ID IF @@Error <> 0 GOTO ABORT
--update da hach toan UPDATE ASS_AMORT_DT SET EXECUTED = '1' WHERE AMORTDT_ID = @l_AMORTDT_ID IF @@Error <> 0 GOTO ABORT FETCH NEXT FROM pCur INTO @l_AMORTDT_ID, @l_ASSET_ID, @l_BRANCH_ID, @l_DEP_ID, @l_AMORT_AMT, @l_GROUP_ID, @
l_AMORTIZED_MONTH, @l_AMORT_END_DATE, @l_ASSET_TYPE, @l_AMORT_START_DATE END --UPDATE STATUS CHO BANG ASS_AMORT UPDATE ASS_AMORT SET PROCESS_STATUS = 'S' WHERE AMORT_ID = @p_AMORT_ID IF @@Error <> 0 GOTO ABORT /*****THIEUVQ bo doan nay vi BAN
VIET khong co dung /****CAP NHAT CCLD TINH TRANG: DPB(Đã phân bổ chi phí khi xuất sử dụng) VE DANG KHAU HAO***/ --IF @l_ASSET_TYPE_DO = 'CCLD' --BEGIN -- UPDATE ASS_MASTER SET AMORT_STATUS = 'DKH' -- WHERE AMORT_STATUS IN ('DPB') -- --AND convert
(date, AMORT_START_DATE) <= convert(date, @sExecDT) -- AND convert(date, AMORT_START_DATE) <= convert(date, dbo.FN_GetLastDayOfMonth(@sExecDT)) -- AND AMORTIZED_AMT < AMORT_AMT -- AND RECORD_STATUS = '1' -- AND AUTH_STATUS = 'A' -- AND TYPE
_ID = 'CCLD' -- AND (BRANCH_ID = @l_BRANCH_ID_DO -- OR (@l_BRANCH_TYPE_DO = 'CN' --NEU LA CN THI PHAN BO LUON CHO PGD THUOC CHI NHANH DO -- --AND BRANCH_ID IN (SELECT * FROM [dbo].[CM_BRANCH_GETCHILDID](@l_BRANCH_ID_DO)))) --THIEUVQ --
AND BRANCH_ID IN (SELECT BR.BRANCH_ID FROM ASS_AMORT_BRN BR WHERE BR.AMORT_ID = @p_AMORT_ID))) --THIEUVQ 28092016 --END ******/ /*******************THIEUVQ - HUY CAC GIAO DICH CHUA DUYET - 251218 - BEGIN:*****************************/ UPDATE ASS_A
DDNEW SET AUTH_STATUS = 'D', AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL) UPDATE ASS_USE_MULTI_DT SET AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE USER_MASTER_ID
IN (SELECT A.USER_MASTER_ID FROM ASS_USE_MULTI_MASTER A WHERE A.AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL)) UPDATE ASS_USE_MULTI_MASTER SET AUTH_STATUS = 'D', AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE AUTH_STATUS =
'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL) UPDATE ASS_LIQUIDATION_DT SET AUTH_STATUS = 'D', MAKER_ID = 'system' WHERE LIQ_ID IN (SELECT A.LIQ_ID FROM ASS_LIQUIDATION A WHERE A.AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS
NOT NULL)) UPDATE ASS_LIQUIDATION SET AUTH_STATUS = 'D', AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL) UPDATE ASS_COLLECT_MULTI_DT SET AUTH_STATUS_KT = 'D', CHECKER_ID_KT
= 'system' WHERE COL_MULTI_MASTER_ID IN (SELECT A.COL_MULTI_MASTER_ID FROM ASS_COLLECT_MULTI_MASTER A WHERE A.AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL)) UPDATE ASS_COLLECT_MULTI_MASTER SET AUTH_STATUS = 'D', AUTH_STATUS_KT
= 'D', CHECKER_ID_KT = 'system' WHERE AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL) UPDATE ASS_TRANSFER_MULTI_DT SET AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE TRANS_MULTI_MASTER_ID IN (SELECT A.TRANS_MULTI_MASTER_ID
FROM ASS_TRANSFER_MULTI_MASTER A WHERE A.AUTH_STATUS = 'U' OR (AUTH_STATUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL)) UPDATE ASS_TRANSFER_MULTI_MASTER SET AUTH_STATUS = 'D', AUTH_STATUS_KT = 'D', CHECKER_ID_KT = 'system' WHERE AUTH_STATUS = 'U' OR (AUTH_STA
TUS_KT = 'U' AND MAKER_ID_KT IS NOT NULL) UPDATE ASS_UPDATE SET AUTH_STATUS = 'D', CHECKER_ID = 'system' WHERE AUTH_STATUS = 'U' /*********************END:***************************/ CLOSE pCur DEALLOCATE pCur COMMIT TRANSACTION SELECT '0' as Resu
lt, '' ErrorDesc RETURN '0' ABORT: BEGIN CLOSE pCur DEALLOCATE pCur CLOSE pCurCost DEALLOCATE pCurCost ROLLBACK TRANSACTION SELECT '-1' as Result, ERROR_MESSAGE() ErrorDesc RETURN '-1' End