1. comp_cd 없는 공통코드
CREATE OR REPLACE VIEW V_CODE
AS
SELECT DECODE(D.NO, 999, ' ', NVL(T.CODE, T.GRP_CD)) AS CODE
, DECODE(D.NO, 999, ' ', T.KO_KR) AS KO_KR
, DECODE(D.NO, 999, ' ', T.EN_US) AS EN_US
, DECODE(D.NO, 999, ' ', T.ZH_CN) AS ZH_CN
, DECODE(D.NO, 999, ' ', T.SORT_ORD) AS SORT_ORD
, DECODE(D.NO, 999, ' ', T.USE_YN) AS USE_YN
, DECODE(D.NO, 999, ' ', NVL(T.REM, ' ')) AS REM
, T.GRP_CD
--, D.NO
, DECODE(D.NO, 999, ' ', T.TYP_NM) AS TYP_NM
FROM (SELECT G.SYS_ID
, G.GRP_CD
, NULL AS CODE
, G.GRP_CD_NM AS KO_KR
, G.GRP_CD_EN_NM AS EN_US
, NULL AS ZH_CN
, -1 AS SORT_ORD
, G.USE_YN
, G.REM
, G.STS
, T.DTL_CD_NM AS TYP_NM
FROM ESACDGP G
JOIN ESACDDL T ON T.SYS_ID = G.SYS_ID
AND T.LANG_CD = 'ko_KR'
AND T.GRP_CD = 'C999'
AND T.DTL_CD = G.CD_TYP
UNION ALL
SELECT D.SYS_ID
, D.GRP_CD
, D.DTL_CD AS CODE
, MIN(DECODE(L.LANG_CD, 'ko_KR', L.DTL_CD_NM)) AS KO_KR
, MIN(DECODE(L.LANG_CD, 'en_US', L.DTL_CD_NM)) AS EN_US
, MIN(DECODE(L.LANG_CD, 'zh_CN', L.DTL_CD_NM)) AS ZH_CN
, MIN(NVL(D.SORT_ORD, 99)) AS SORT_ORD
, MIN(D.USE_YN) AS USE_YN
, MIN(D.REM) AS REM
, MIN(D.STS) AS STS
, NULL AS TYP_NM
FROM ESACDDT D
LEFT OUTER JOIN ESACDDL L ON L.SYS_ID = D.SYS_ID
AND L.LANG_CD IN ('ko_KR', 'en_US', 'zh_CN')
AND L.GRP_CD = D.GRP_CD
AND L.DTL_CD = D.DTL_CD
GROUP BY D.SYS_ID
, D.GRP_CD
, D.DTL_CD
) T
JOIN (SELECT LEVEL - 2 AS NO
FROM DUAL
CONNECT BY LEVEL <= 1001) D ON D.NO IN (DECODE(T.SORT_ORD, -1, 999), T.SORT_ORD)
WHERE T.SYS_ID = '100'
AND T.STS <> 'D'
ORDER BY T.GRP_CD, D.NO, T.CODE;
2. comp_cd 있는 공통코드
CREATE OR REPLACE VIEW V_CODE_COMP
AS
SELECT DECODE(D.NO, 999, ' ', NVL(T.CODE, T.GRP_CD)) AS CODE
, DECODE(D.NO, 999, ' ', T.KO_KR) AS KO_KR
, DECODE(D.NO, 999, ' ', T.EN_US) AS EN_US
, DECODE(D.NO, 999, ' ', T.ZH_CN) AS ZH_CN
, DECODE(D.NO, 999, ' ', T.SORT_ORD) AS SORT_ORD
, DECODE(D.NO, 999, ' ', T.USE_YN) AS USE_YN
, DECODE(D.NO, 999, ' ', NVL(T.REM, ' ')) AS REM
, T.GRP_CD
--, D.NO
, DECODE(D.NO, 999, ' ', T.TYP_NM) AS TYP_NM
, (SELECT '[' || L.DTL_CD || '] ' || L.DTL_CD_NM
FROM ESACDDL L
WHERE L.SYS_ID = T.SYS_ID
AND L.LANG_CD = 'ko_KR'
AND L.GRP_CD = 'C997'
AND L.DTL_CD = T.COMP_CD) AS COMP_NM
FROM (SELECT G.SYS_ID
, G.GRP_CD
, NULL AS CODE
, G.GRP_CD_NM AS KO_KR
, G.GRP_CD_EN_NM AS EN_US
, NULL AS ZH_CN
, -1 AS SORT_ORD
, G.USE_YN
, G.REM
, G.STS
, T.DTL_CD_NM AS TYP_NM
, '0000' AS COMP_CD
FROM ESACCGP G
JOIN ESACDDL T ON T.SYS_ID = G.SYS_ID
AND T.LANG_CD = 'ko_KR'
AND T.GRP_CD = 'C999'
AND T.DTL_CD = G.CD_TYP
UNION ALL
SELECT D.SYS_ID
, D.GRP_CD
, D.DTL_CD AS CODE
, MIN(DECODE(L.LANG_CD, 'ko_KR', L.DTL_CD_NM)) AS KO_KR
, MIN(DECODE(L.LANG_CD, 'en_US', L.DTL_CD_NM)) AS EN_US
, MIN(DECODE(L.LANG_CD, 'zh_CN', L.DTL_CD_NM)) AS ZH_CN
, MIN(NVL(D.SORT_ORD, 99)) AS SORT_ORD
, MIN(D.USE_YN) AS USE_YN
, MIN(D.REM) AS REM
, MIN(D.STS) AS STS
, NULL AS TYP_NM
, D.COMP_CD AS COMP_CD
FROM ESACCDT D
LEFT OUTER JOIN ESACCDL L ON L.SYS_ID = D.SYS_ID
AND L.LANG_CD IN ('ko_KR', 'en_US', 'zh_CN')
AND L.GRP_CD = D.GRP_CD
AND L.DTL_CD = D.DTL_CD
AND L.COMP_CD = D.COMP_CD
GROUP BY D.SYS_ID
, D.GRP_CD
, D.DTL_CD
, D.COMP_CD
) T
JOIN (SELECT LEVEL - 2 AS NO
FROM DUAL
CONNECT BY LEVEL <= 1001) D ON D.NO IN (DECODE(T.SORT_ORD, -1, 999), T.SORT_ORD)
WHERE T.SYS_ID = '100'
AND T.STS <> 'D'
ORDER BY T.GRP_CD, DECODE(D.NO, -1, ' ', 999, '_', T.COMP_CD), D.NO, T.CODE;