--select * from MW_ENTRIES_POST where trn_id = 'MWOUT0001160166' --select * from MW_ENTRIES_POST where trn_id = 'MINM00001160181' --exec MW_ENTRIES_POST_CoreByRef @p_ref_id = 'MINM00001160181' ,@p_trn_type = 'MW_IN' CREATE PROCEDURE [dbo].[ASS_ENTRIES_
POST_CoreByRef] @p_TRN_ID varchar(200) = NULL, @p_TRN_TYPE VARCHAR(15) = ''---AMORT - KH?U HAO, ADDNEW - THEM MOI, USE - XUAT, TRANSF - DIEU CHUYEN, UPDATE - UPDATE, COLLECT - THU HOI, LIQ - THANH LY AS --DECLARE @l_SUM_AMT DECIMAL, @l_REF_NO VARCHAR(20
), @l_ASSET_ID VARCHAR(20) --SELECT TOP 1 @l_TRN_TYPE = TRN_TYPE FROM ASS_ENTRIES_POST A --WHERE A.REF_ID = @p_TRN_ID --SELECT TOP 1 @l_REF_NO = REF_NO, @l_ASSET_ID = ASSET_ID FROM ASS_ENTRIES_POST WHERE REF_ID = @p_TRN_ID --dbo.ReadMoney(A.AMT) as string
Money, --DECLARE @TEMP_AMT TABLE (CR_ACCT VARCHAR(50), AMOUNT DECIMAL) --INSERT INTO @TEMP_AMT select CR_ACCT,sum(AMT) --from ASS_ENTRIES_POST --where REF_ID =@p_TRN_ID --GROUP BY CR_ACCT --[dbo].[FN_GET_ACC_NAME](A.CR_ACCT) AS CR_ACCT_NAME, --SEL
ECT A.ET_ID, -- A.CR_ACCT, CRB.BRANCH_CODE AS CR_BRN, -- A.DR_ACCT, DRB.BRANCH_CODE AS DR_BRN, -- A.AMT AS AMT, A.TRN_DESC AS TRN_DESC, -- 'VND' AS CURRENCY, 1.0 AS EXCRATE, A.MAKER_ID, A.CHECKER_ID --FROM ASS_ENTRIES_POST A ----LEFT JOIN @TEMP_A
MT B ON A.CR_ACCT= B.CR_ACCT ----LEFT JOIN CM_BRANCH DOB ON DOB.BRANCH_ID = A.DO_BRN --LEFT JOIN CM_BRANCH CRB ON CRB.BRANCH_ID = A.CR_BRN --LEFT JOIN CM_BRANCH DRB ON DRB.BRANCH_ID = A.DR_BRN --WHERE A.REF_ID = @p_TRN_ID ----GROUP BY A.TRN_TYPE,A.RE
F_ID,A.CR_ACCT,A.CR_BRN,A.DR_ACCT,A.DR_BRN,A.TRN_DESC,B.AMOUNT DECLARE @ENTRIES_POST_TEM TABLE (ET_ID varchar(15), REF_NO varchar(20), ASSET_ID varchar(15), TRN_TYPE nvarchar(40), REF_ID varchar(15), DO_BRN varchar(15), CR_ACCT varchar(20), CR_BRN varcha
r(15), DR_ACCT varchar(20), DR_BRN varchar(15), AMT decimal(18, 0), EXP_TO_CORE varchar(1), TRN_DATE datetime, TRN_DESC nvarchar(1000), MAKER_ID varchar(15), CHECKER_ID varchar(15), TRN_ID varchar(15), CR_DEP VARCHAR(15), DR_DEP VARCHAR(15)) INSERT INTO
@ENTRIES_POST_TEM SELECT *,'','' FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_TRN_ID AND (REF_NO IS NULL OR REF_NO ='') -- 23/09/2019 luctv bo sung ref_no is null UPDATE @ENTRIES_POST_TEM SET CR_DEP = A.DEP_ID, DR_DEP = A.DEP_ID FROM ASS_AMORT_DT A INNER J
OIN @ENTRIES_POST_TEM B ON A.AMORTDT_ID = B.REF_ID UPDATE @ENTRIES_POST_TEM SET DR_DEP = A.DEPT_ID FROM (SELECT A.*, B.ASSET_ID FROM ASS_COST_ALLOC_DT A LEFT JOIN ASS_COST_ALLOCATION B ON A.COS_ID = B.COS_ID WHERE A.RECORD_STATUS = '1') A INNER JOI
N @ENTRIES_POST_TEM B ON A.ASSET_ID = B.ASSET_ID --SELECT * FROM @ENTRIES_POST_TEM --WHERE CR_DEP <> DR_DEP ------- DECLARE @l_MAKER_ID VARCHAR(15), @l_CHECKER_ID VARCHAR(15), @l_TRN_DESC NVARCHAR(MAX), @lTRN_DATE DATETIME, @p_DO_BRN VARCHAR(50), @p_DO_
BRN_CODE VARCHAR(50) SELECT TOP 1 @l_MAKER_ID = MAKER_ID,@l_CHECKER_ID = CHECKER_ID, @l_TRN_DESC = TRN_DESC, @lTRN_DATE = TRN_DATE, @p_DO_BRN = DO_BRN FROM ASS_ENTRIES_POST WHERE TRN_ID = @p_TRN_ID ---THIEUVQ THEM DO_BRANCH 121120 SET @p_DO_BRN_CODE
= (SELECT BRANCH_CODE FROM CM_BRANCH WHERE BRANCH_ID = @p_DO_BRN) IF @p_TRN_TYPE = 'AMORT' BEGIN SET @l_TRN_DESC = N'KH/PB KY '+CONVERT(VARCHAR(5),MONTH(@lTRN_DATE))+' '+CONVERT(VARCHAR(5),YEAR(@lTRN_DATE)) END SELECT ACC.ET_ID, ACC.CR_ACCT,CR
B.BRANCH_CODE AS CR_BRN, ACC.DR_ACCT,DRB.BRANCH_CODE AS DR_BRN, ACC.AMT, 'VND' AS CURRENCY, 1 AS EXCRATE, @l_MAKER_ID AS MAKER_ID, @l_CHECKER_ID AS CHECKER_ID, @l_TRN_DESC AS TRN_DESC, @p_DO_BRN_CODE AS DO_BRN FROM ( SELECT CR_BRN, CR_DEP, C
R_ACCT, DR_BRN, DR_DEP, DR_ACCT, SUM(AMT) AS AMT, STUFF(( SELECT ',' + C1.ET_ID FROM @ENTRIES_POST_TEM C1 WHERE C2.CR_BRN=C1.CR_BRN AND C2.CR_ACCT=C1.CR_ACCT AND C2.DR_BRN=C1.DR_BRN AND C2.DR_ACCT=C1.DR_ACCT --AND C1.TRN_ID = @p_TRN_ID AND
C2.CR_DEP = C1.CR_DEP AND C2.DR_DEP = C1.DR_DEP FOR XML PATH('') ), 1, 1, '') AS ET_ID --'VND' AS CURRENCY, 1.0 AS EXCRATE, @l_MAKER_ID AS MAKER_ID, @l_CHECKER_ID AS CHECKER_ID, @l_TRN_DESC AS TRN_DESC FROM @ENTRIES_POST_TEM C2 --WHERE C2.T
RN_ID = @p_TRN_ID GROUP BY C2.CR_BRN, C2.CR_ACCT, C2.DR_BRN, C2.DR_ACCT, C2.CR_DEP, C2.DR_DEP ) AS ACC LEFT JOIN CM_BRANCH CRB ON CRB.BRANCH_ID = ACC.CR_BRN LEFT JOIN CM_BRANCH DRB ON DRB.BRANCH_ID = ACC.DR_BRN