IF OBJECT_ID (N'DBO.FNC_MENU') IS NOT NULL
    DROP FUNCTION DBO.FNC_MENU;
GO


CREATE FUNCTION [DBO].[FNC_MENU]( @P_MENU_CD    VARCHAR(18) = ROOT)
RETURNS TABLE
AS
RETURN 
(
    WITH MENU_TREE
    AS
    (
        SELECT  M.SYS_ID
        ,       M.MENU_CD
        ,       CAST(L.MENU_NM AS NVARCHAR(100))                                                                        AS MENU_NM
        ,       M.LINK_URL
        ,       CAST(L.MENU_NM AS NVARCHAR(100))                                                                        AS MENU_PATH
        ,       M.UP_MENU_CD
        ,       1                                                                                                       AS MENU_LEVEL
        ,       M.SORT_ORD
        ,       M.USE_YN
        ,       M.USR_CLS
        ,       CAST(RIGHT('0000' + CAST(ISNULL(M.SORT_ORD, 99) AS VARCHAR), 4) AS VARCHAR(100))                        AS MENU_SORT_ORDER
        ,       L.LANG_CD

        FROM ESAAUMM M
        JOIN ESAAUML L  ON  L.SYS_ID    = M.SYS_ID
                        AND L.MENU_CD   = M.MENU_CD
                        AND L.LANG_CD   = 'ko_KR'

        WHERE M.SYS_ID = '100'
        AND M.UP_MENU_CD = @P_MENU_CD
        AND M.USE_YN = 'Y'
        AND M.STS <> 'D' 

        UNION ALL

        SELECT  M.SYS_ID
        ,       M.MENU_CD
        ,       CAST(CONCAT(SPACE(T.MENU_LEVEL), '- ', L.MENU_NM) AS NVARCHAR(100))                                     AS MENU_NM
        ,       M.LINK_URL
        ,       CAST(T.MENU_PATH + ' > ' + L.MENU_NM AS NVARCHAR(100))                                                  AS MENU_PATH
        ,       M.UP_MENU_CD
        ,       T.MENU_LEVEL + 1                                                                                        AS MENU_LEVEL
        ,       M.SORT_ORD
        ,       M.USE_YN
        ,       M.USR_CLS
        ,       CAST(T.MENU_SORT_ORDER + RIGHT('0000' + CAST(ISNULL(M.SORT_ORD, 99) AS VARCHAR), 4) AS VARCHAR(100))    AS MENU_SORT_ORDER
        ,       L.LANG_CD

        FROM MENU_TREE T
        JOIN ESAAUMM M  ON  M.SYS_ID        = T.SYS_ID
                        AND M.UP_MENU_CD    = T.MENU_CD
                        AND M.USE_YN        = T.USE_YN
                        AND M.STS          <> 'D'

        JOIN ESAAUML L  ON  L.SYS_ID        = M.SYS_ID
                        AND L.MENU_CD       = M.MENU_CD
                        AND L.LANG_CD       = T.LANG_CD
    )
    SELECT  M.*
    ,      (SELECT ISNULL(MAX('N'), 'Y')
            WHERE EXISTS   (SELECT *
                            FROM ESAAUMM T
                            WHERE T.SYS_ID = M.SYS_ID
                            AND T.UP_MENU_CD = M.MENU_CD))              AS IS_LEAF
    ,       ROW_NUMBER() OVER (ORDER BY M.USR_CLS, M.MENU_SORT_ORDER)   AS NO
    FROM MENU_TREE M
);
GO