AMS - VB

SELECT A.ASSET_ID, A.AMORTIZED_AMT, A.AMORTIZED_MONTH,
A.AMORT_AMT, A.AMORT_MONTH, A.AMORT_END_DATE, A.AMORT_START_DATE,
A.FIRST_AMORT_AMT, A.MONTHLY_AMORT_AMT, A.BRANCH_ID, A.DEPT_ID
FROM ASS_MASTER A
WHERE A.AMORT_STATUS IN ('CKH','DKH')
AND (convert(date, A.AMORT_START_DATE) <= convert(date, @sExecDT) OR ( dbo.FN_GetLastDayOfMonth(@sExecDT) = dbo.FN_GetLastDayOfMonth(A.AMORT_START_DATE)))
AND A.AMORTIZED_AMT < A.AMORT_AMT
AND A.RECORD_STATUS = '1'
AND A.AUTH_STATUS = 'A'
AND A.TYPE_ID = @p_ASSET_TYPE

DELETE FROM ASS_AMORT_DT_LATEST
GO
INSERT INTO ASS_AMORT_DT_LATEST (AMORTDT_ID, AMORT_ID, AMORT_DT, CRDR, BRANCH_ID, DEP_ID, ASSET_ID, AMORT_AMT, GROUP_ID, EXECUTED, TRN_TYPE, TRN_DESC, TYPE_ID)
SELECT aad.AMORTDT_ID
,aad.AMORT_ID
,aad.AMORT_DT
,aad.CRDR
,aad.BRANCH_ID
,aad.DEP_ID
,aad.ASSET_ID
,aad.AMORT_AMT
,aad.GROUP_ID
,aad.EXECUTED
,aad.TRN_TYPE
,aad.TRN_DESC,'TSCD' FROM ASS_AMORT_DT aad WHERE aad.AMORT_ID ='ASM000000000096'

GO
INSERT INTO ASS_AMORT_DT_LATEST (AMORTDT_ID, AMORT_ID, AMORT_DT, CRDR, BRANCH_ID, DEP_ID, ASSET_ID, AMORT_AMT, GROUP_ID, EXECUTED, TRN_TYPE, TRN_DESC, TYPE_ID)
SELECT aad.AMORTDT_ID
,aad.AMORT_ID
,aad.AMORT_DT
,aad.CRDR
,aad.BRANCH_ID
,aad.DEP_ID
,aad.ASSET_ID
,aad.AMORT_AMT
,aad.GROUP_ID
,aad.EXECUTED
,aad.TRN_TYPE
,aad.TRN_DESC,'CCLD' FROM ASS_AMORT_DT aad WHERE aad.AMORT_ID ='ASM000000000095'

UPDATE ASS_ENTRIES_POST_SYNC SET TRN_DATE='2023-05-23' WHERE TRN_ID IN (SELECT aad.AMORTDT_ID FROM ASS_AMORT_DT aad WHERE aad.AMORT_ID='ASM000000000097')
UPDATE ASS_ENTRIES_POST_SYNC SET TRN_DATE='2023-05-23' WHERE TRN_ID IN (SELECT aad.AMORTDT_ID FROM ASS_AMORT_DT aad WHERE aad.AMORT_ID='ASM000000000098')
UPDATE ASS_ENTRIES_POST SET TRN_DATE='2023-05-23' WHERE REF_ID IN (SELECT aad.AMORTDT_ID FROM ASS_AMORT_DT aad WHERE aad.AMORT_ID='ASM000000000097')
UPDATE ASS_ENTRIES_POST SET TRN_DATE='2023-05-23' WHERE REF_ID IN (SELECT aad.AMORTDT_ID FROM ASS_AMORT_DT aad WHERE aad.AMORT_ID='ASM000000000098')
UPDATE ASS_AMORT_DT SET AMORT_DT='2023-05-23' WHERE AMORT_ID ='ASM000000000097'
UPDATE ASS_AMORT_DT SET AMORT_DT='2023-05-23' WHERE AMORT_ID ='ASM000000000098'
UPDATE ASS_AMORT SET EXECUTE_DT='2023-05-23' WHERE AMORT_ID ='ASM000000000097'
UPDATE ASS_AMORT SET EXECUTE_DT='2023-05-23' WHERE AMORT_ID ='ASM000000000098'

INSERT INTO CM_ALLCODE ( CDNAME, CDVAL, CONTENT, CDTYPE, LSTODR) VALUES
( 'ASS_UPDATE', N'A', N'Đã duyệt', 'ASS_UPDATE', 1),
( 'ASS_UPDATE', N'U', N'Chờ duyệt', 'ASS_UPDATE', 2),
( 'ASS_UPDATE', N'R', N'Từ chối', 'ASS_UPDATE', 3),
( 'ASS_UPDATE', N'E', N'Lưu nháp', 'ASS_UPDATE', 5)

ALTER TABLE ASS_MASTER
ADD CONSTRAINT df_AMORTIZED_AMT_0
DEFAULT 0 FOR AMORTIZED_AMT;

SELECT * FROM CM_ATTACH_FILE CAF WHERE CAF.REF_MASTER = 'TRSD00000000180'