exec TR_BUDGET_CHECK_LIMIT_YEAR_GET_INFO_LIMIT @p_GD_ID=N'GOO000001061929',@p_BRANCH_ID=N'DV0001',@p_DEP_ID=N'DEP000000000014',@p_MONTH=N'M7',@p_YEAR=N'2023',@p_BRANCH_KIND=N'A',@p_TLNAME=NULL,@p_KHOI_ID=N'DM0000000000017',@p_ROLENAME=NULL,@p_GD_CODE=NULL
go

-- PROCEDURE NAME: TR_BUDGET_CHECK_LIMIT_YEAR_HIST_Byid

DECLARE @p_GD_ID varchar(20) = N'GOO000001061929',
@p_GD_CODE varchar(20) = NULL,
@p_BRANCH_ID varchar(20) = N'DV0001',
@p_DEP_ID varchar(20) = N'DEP000000000014',
@p_BUDGET_MONTH varchar(20) = N'M7',
@p_BUDGET_YEAR varchar(20) = N'2023',
@p_BRANCH_KIND varchar(20) = N'A',
@p_KHOI_ID varchar(20) = N'DM0000000000017',
@p_TLNAME varchar(200) = NULL,
@p_ROLENAME varchar(200) = NULL,
@p_TOP int = NULL

	SET @p_TOP = NULL

	IF(@p_TOP IS NULL OR @p_TOP=0)
	BEGIN  
-- PAGING BEGIN
BEGIN
SELECT COUNT(*) -- SELECT END
    FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL A
	--LEFT JOIN TR_REQ_BUDGET_YEAR_LIMIT B ON A.BRANCH_ID = B.BRANCH_ID AND A.DEP_ID = B.DEP_ID AND A.GD_ID = B.GD_ID
	LEFT JOIN TR_REQ_PAYMENT C ON A.REQ_PAY_ID = C.REQ_PAY_ID
	LEFT JOIN TR_REQ_PAY_BUDGET trpb ON trpb.REQ_PAY_ID = A.REQ_PAY_ID AND trpb.BRANCH_KIND = @p_BRANCH_KIND AND (trpb.AMT_EXE = A.M1+A.M2+A.M3+A.M4+A.M5+A.M6+A.M7+A.M8+A.M9+A.M10+A.M11+A.M12)
	LEFT JOIN TL_USER TL1 ON C.MAKER_ID_KT = TL1.TLNANME
	LEFT JOIN TL_USER TL2 ON C.CHECKER_ID_KT = TL2.TLNANME
	LEFT JOIN CM_BRANCH CM ON A.BRANCH_ID = CM.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DP ON A.DEP_ID = DP.DEP_ID
    WHERE 1=1
	AND A.GD_ID = @p_GD_ID 
	AND A.BRANCH_ID = @p_BRANCH_ID 
	--AND A.DEP_ID = @p_DEP_ID
	AND (A.DEP_ID = @p_DEP_ID OR  A.BRANCH_ID <> 'DV0001')
	AND A.RECORD_STATUS = '1'
	AND A.BUDGET_YEAR = @p_BUDGET_YEAR
    AND A.BRANCH_KIND = @p_BRANCH_KIND
	AND ((ISNULL(@p_TLNAME,'') <> '' AND A.TLNAME = @p_TLNAME) OR ((ISNULL(@p_TLNAME,'') = '' AND (A.TLNAME = '' OR A.TLNAME IS NULL))))
    GROUP BY A.BUDGET_YEAR_LIMIT_DETAIL_ID, C.REQ_PAY_ID, C.REQ_PAY_CODE, CM.BRANCH_NAME, DP.DEP_NAME,
    trpb.AMT_EXE,trpb.REASON,
    C.CREATE_DT, TL1.TLFullName, TL2.TLFullName, C.APPROVE_DT_KT,A.CREATE_DT
    
;WITH QUERY_DATA AS ( 
    SELECT A.BUDGET_YEAR_LIMIT_DETAIL_ID, C.REQ_PAY_ID, C.REQ_PAY_CODE, CM.BRANCH_NAME, DP.DEP_NAME,
    trpb.AMT_EXE AS 'REQ_AMT',
    --C.REQ_AMT, 
        trpb.REASON AS 'REQ_REASON',
    --C.REQ_REASON, 
    C.CREATE_DT AS PAYMENT_CREATE_DT, TL1.TLFullName AS GDV, TL2.TLFullName AS KSV, C.APPROVE_DT_KT
, ROW_NUMBER() OVER (ORDER BY  A.CREATE_DT DESC
) AS __ROWNUM-- SELECT END
    FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL A
	--LEFT JOIN TR_REQ_BUDGET_YEAR_LIMIT B ON A.BRANCH_ID = B.BRANCH_ID AND A.DEP_ID = B.DEP_ID AND A.GD_ID = B.GD_ID
	LEFT JOIN TR_REQ_PAYMENT C ON A.REQ_PAY_ID = C.REQ_PAY_ID
	LEFT JOIN TR_REQ_PAY_BUDGET trpb ON trpb.REQ_PAY_ID = A.REQ_PAY_ID AND trpb.BRANCH_KIND = @p_BRANCH_KIND AND (trpb.AMT_EXE = A.M1+A.M2+A.M3+A.M4+A.M5+A.M6+A.M7+A.M8+A.M9+A.M10+A.M11+A.M12)
	LEFT JOIN TL_USER TL1 ON C.MAKER_ID_KT = TL1.TLNANME
	LEFT JOIN TL_USER TL2 ON C.CHECKER_ID_KT = TL2.TLNANME
	LEFT JOIN CM_BRANCH CM ON A.BRANCH_ID = CM.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DP ON A.DEP_ID = DP.DEP_ID
    WHERE 1=1
	AND A.GD_ID = @p_GD_ID 
	AND A.BRANCH_ID = @p_BRANCH_ID 
	--AND A.DEP_ID = @p_DEP_ID
	AND (A.DEP_ID = @p_DEP_ID OR  A.BRANCH_ID <> 'DV0001')
	AND A.RECORD_STATUS = '1'
	AND A.BUDGET_YEAR = @p_BUDGET_YEAR
    AND A.BRANCH_KIND = @p_BRANCH_KIND
	AND ((ISNULL(@p_TLNAME,'') <> '' AND A.TLNAME = @p_TLNAME) OR ((ISNULL(@p_TLNAME,'') = '' AND (A.TLNAME = '' OR A.TLNAME IS NULL))))
    GROUP BY A.BUDGET_YEAR_LIMIT_DETAIL_ID, C.REQ_PAY_ID, C.REQ_PAY_CODE, CM.BRANCH_NAME, DP.DEP_NAME,
    trpb.AMT_EXE,trpb.REASON,
    C.CREATE_DT, TL1.TLFullName, TL2.TLFullName, C.APPROVE_DT_KT,A.CREATE_DT
    
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
END-- PAGING END
    END;
    ELSE 
	BEGIN
-- PAGING BEGIN
BEGIN
SELECT COUNT(*) FROM(
    SELECT TOP(CONVERT(INT, @p_TOP)) A.BUDGET_YEAR_LIMIT_DETAIL_ID, C.REQ_PAY_ID, C.REQ_PAY_CODE, CM.BRANCH_NAME, DP.DEP_NAME,
    trpb.AMT_EXE AS 'REQ_AMT',
    -- C.REQ_AMT,
    trpb.REASON AS 'REQ_REASON',
    --C.REQ_REASON, 
    C.CREATE_DT AS PAYMENT_CREATE_DT, TL1.TLFullName AS GDV, TL2.TLFullName AS KSV, C.APPROVE_DT_KT
-- SELECT END
    FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL A
	--LEFT JOIN TR_REQ_BUDGET_YEAR_LIMIT B ON A.BRANCH_ID = B.BRANCH_ID AND A.DEP_ID = B.DEP_ID AND A.GD_ID = B.GD_ID
	LEFT JOIN TR_REQ_PAYMENT C ON A.REQ_PAY_ID = C.REQ_PAY_ID
	LEFT JOIN TR_REQ_PAY_BUDGET trpb ON trpb.REQ_PAY_ID = A.REQ_PAY_ID AND trpb.BRANCH_KIND = @p_BRANCH_KIND AND (trpb.AMT_EXE = A.M1+A.M2+A.M3+A.M4+A.M5+A.M6+A.M7+A.M8+A.M9+A.M10+A.M11+A.M12)
	LEFT JOIN TL_USER TL1 ON C.MAKER_ID_KT = TL1.TLNANME
	LEFT JOIN TL_USER TL2 ON C.CHECKER_ID_KT = TL2.TLNANME
	LEFT JOIN CM_BRANCH CM ON A.BRANCH_ID = CM.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DP ON A.DEP_ID = DP.DEP_ID
    WHERE 1=1
	AND A.GD_ID = @p_GD_ID 
	AND A.BRANCH_ID = @p_BRANCH_ID 
	--AND A.DEP_ID = @p_DEP_ID
	AND (A.DEP_ID = @p_DEP_ID OR  A.BRANCH_ID <> 'DV0001')
	AND A.RECORD_STATUS = '1'
	AND A.BUDGET_YEAR = @p_BUDGET_YEAR
    AND A.BRANCH_KIND = @p_BRANCH_KIND
	AND ((ISNULL(@p_TLNAME,'') <> '' AND A.TLNAME = @p_TLNAME) OR ((ISNULL(@p_TLNAME,'') = '' AND (A.TLNAME = '' OR A.TLNAME IS NULL))))
    GROUP BY A.BUDGET_YEAR_LIMIT_DETAIL_ID, C.REQ_PAY_ID, C.REQ_PAY_CODE, CM.BRANCH_NAME, DP.DEP_NAME,
    trpb.AMT_EXE,trpb.REASON,
    C.CREATE_DT, TL1.TLFullName, TL2.TLFullName, C.APPROVE_DT_KT,A.CREATE_DT
    ORDER BY  A.CREATE_DT DESC
) COUNTER_TOP;WITH QUERY_DATA AS ( 
    SELECT TOP(CONVERT(INT, @p_TOP)) A.BUDGET_YEAR_LIMIT_DETAIL_ID, C.REQ_PAY_ID, C.REQ_PAY_CODE, CM.BRANCH_NAME, DP.DEP_NAME,
    trpb.AMT_EXE AS 'REQ_AMT',
    -- C.REQ_AMT,
    trpb.REASON AS 'REQ_REASON',
    --C.REQ_REASON, 
    C.CREATE_DT AS PAYMENT_CREATE_DT, TL1.TLFullName AS GDV, TL2.TLFullName AS KSV, C.APPROVE_DT_KT
, ROW_NUMBER() OVER (ORDER BY  A.CREATE_DT DESC
) AS __ROWNUM-- SELECT END
    FROM TR_REQ_BUDGET_YEAR_LIMIT_DETAIL A
	--LEFT JOIN TR_REQ_BUDGET_YEAR_LIMIT B ON A.BRANCH_ID = B.BRANCH_ID AND A.DEP_ID = B.DEP_ID AND A.GD_ID = B.GD_ID
	LEFT JOIN TR_REQ_PAYMENT C ON A.REQ_PAY_ID = C.REQ_PAY_ID
	LEFT JOIN TR_REQ_PAY_BUDGET trpb ON trpb.REQ_PAY_ID = A.REQ_PAY_ID AND trpb.BRANCH_KIND = @p_BRANCH_KIND AND (trpb.AMT_EXE = A.M1+A.M2+A.M3+A.M4+A.M5+A.M6+A.M7+A.M8+A.M9+A.M10+A.M11+A.M12)
	LEFT JOIN TL_USER TL1 ON C.MAKER_ID_KT = TL1.TLNANME
	LEFT JOIN TL_USER TL2 ON C.CHECKER_ID_KT = TL2.TLNANME
	LEFT JOIN CM_BRANCH CM ON A.BRANCH_ID = CM.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DP ON A.DEP_ID = DP.DEP_ID
    WHERE 1=1
	AND A.GD_ID = @p_GD_ID 
	AND A.BRANCH_ID = @p_BRANCH_ID 
	--AND A.DEP_ID = @p_DEP_ID
	AND (A.DEP_ID = @p_DEP_ID OR  A.BRANCH_ID <> 'DV0001')
	AND A.RECORD_STATUS = '1'
	AND A.BUDGET_YEAR = @p_BUDGET_YEAR
    AND A.BRANCH_KIND = @p_BRANCH_KIND
	AND ((ISNULL(@p_TLNAME,'') <> '' AND A.TLNAME = @p_TLNAME) OR ((ISNULL(@p_TLNAME,'') = '' AND (A.TLNAME = '' OR A.TLNAME IS NULL))))
    GROUP BY A.BUDGET_YEAR_LIMIT_DETAIL_ID, C.REQ_PAY_ID, C.REQ_PAY_CODE, CM.BRANCH_NAME, DP.DEP_NAME,
    trpb.AMT_EXE,trpb.REASON,
    C.CREATE_DT, TL1.TLFullName, TL2.TLFullName, C.APPROVE_DT_KT,A.CREATE_DT
    ORDER BY  A.CREATE_DT DESC
) SELECT * FROM QUERY_DATA WHERE __ROWNUM > 0 AND __ROWNUM <= 10
END-- PAGING END
    END;

go
--13072023_secretkey