/****** 개체: 뷰 DZGOLD.VA_ACCT 스크립트 날짜: 2002-01-03 오후 2:40:03 ******/ -- 20030218 : 환율 부분 필수체크 누락 수정 -- KTH 20100818 : 전표조회 및 유관 레포트 조회시('사용자정의'문제) 속도느리는 문제 수정. alter VIEW [dbo].[VA_ACCT] --WITH ENCRYPTION AS -- select * FROM SACCT -- select * FROM VA_ACCT -- select * FROM SDBCFG -- select * FROM SCTRL -- exec USP_ACAC010_BY_ACCT_CD_SELECT @LANGKIND=N'CHS',@CO_CD=N'1000' --SET @USER_WORD = DBO.UFN_GET_STRING_BY_LANGKIND( ( SELECT FSTLANG FROM SDBCFG ) , N'사용자정의') SELECT S.CO_CD, S.ACCT_CD, CASE WHEN B.CON_CD = 'CHS' THEN S.PRT_NM ELSE S.ACCT_NM END ACCT_NM, CASE WHEN B.CON_CD = 'CHS' THEN S.PRT_NMK ELSE S.ACCT_NMK END ACCT_NMK, S.TRCD_TY, CASE WHEN ( LEFT( S.TRCD_TY, 1 ) = 'A' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'A1' AND TR.MODULE_CD IN ( 'A', 'S' ) ) END AS TRCD, CASE WHEN ( LEFT( S.TRCD_TY, 1 ) <> 'A' ) THEN '0' ELSE S.TR_FG END AS TR_FG, S.TRNM_TY, CASE WHEN ( LEFT( S.TRNM_TY, 1 ) = 'B' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'B1' AND TR.MODULE_CD IN ( 'A', 'S' ) ) END AS TRNM, CASE WHEN ( LEFT( S.TRNM_TY, 1 ) <> 'B' ) THEN '0' ELSE S.TRNM_FG END AS TRNM_FG, S.DEPTCD_TY, CASE WHEN ( LEFT( S.DEPTCD_TY, 1 ) = 'C') THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.DEPTCD_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'C1' AND TR.MODULE_CD IN ( 'A','S' ) ) END AS DEPT, CASE WHEN ( LEFT( S.DEPTCD_TY, 1 ) <> 'C' ) THEN '0' ELSE S.DEPTCD_FG END AS DEPTCD_FG, S.PJTCD_TY, CASE WHEN ( LEFT( S.PJTCD_TY, 1 ) = 'D' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.PJTCD_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE 'PROJECT' END AS PJT, CASE WHEN ( LEFT( S.PJTCD_TY, 1 ) <> 'D' ) THEN '0' ELSE S.PJTCD_FG END AS PJTCD_FG, S.CTNB_TY, CASE WHEN ( LEFT( S.CTNB_TY, 1 ) = 'E' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.CTNB_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'EA' AND TR.MODULE_CD IN ( 'A', 'S' ) ) END AS CTNB, CASE WHEN ( LEFT( S.CTNB_TY, 1 ) <> 'E' ) THEN '0' ELSE S.CTNB_FG END AS CTNB_FG, S.FRDT_TY, CASE WHEN ( LEFT( S.FRDT_TY, 1 ) = 'F' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.FRDT_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'F1' AND TR.MODULE_CD IN ( 'A', 'S' ) ) END AS FRDT, CASE WHEN ( LEFT( S.FRDT_TY, 1 ) <> 'F' ) THEN '0' ELSE S.FRDT_FG END AS FRDT_FG, S.TODT_TY, CASE WHEN ( LEFT( S.TODT_TY, 1 ) = 'G' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.TODT_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'G1' AND TR.MODULE_CD IN ( 'A','S' ) ) END AS TODT, CASE WHEN ( LEFT( S.TODT_TY, 1 ) <> 'G' ) THEN '0' ELSE S.TODT_FG END AS TODT_FG, S.QT_TY, CASE WHEN ( LEFT( S.QT_TY, 1 ) = 'H' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.QT_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'H1' AND TR.MODULE_CD IN ( 'A', 'S' ) ) END AS QT, -- (CASE WHEN ( LEFT( S.TODT_TY, 1 ) <>'H') THEN '0' ELSE S.TODT_FG END) AS QT_FG, S.AM_TY, CASE WHEN ( LEFT( S.QT_TY, 1 ) <> 'H' ) THEN '0' ELSE S.QT_FG END AS QT_FG, S.AM_TY, CASE WHEN ( LEFT( S.AM_TY, 1 ) = 'I') THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.AM_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'I2' AND TR.MODULE_CD IN ( 'A', 'S' ) ) END AS AM, CASE WHEN ( LEFT( S.AM_TY, 1 ) <> 'I' ) THEN '0' ELSE S.AM_FG END AS AM_FG, S.RT_TY, CASE WHEN ( LEFT( S.RT_TY, 1 ) = 'J' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.RT_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'J1' AND TR.MODULE_CD IN ( 'A', 'S' ) ) END AS RT, CASE WHEN ( LEFT( S.RT_TY, 1 ) <> 'J' ) THEN '0' ELSE S.RT_FG END AS RT_FG, S.DEAL_TY, CASE WHEN ( LEFT( S.DEAL_TY, 1 ) = 'K' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.DEAL_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND TR.CTRL_CD = 'K1' AND TR.MODULE_CD IN ( 'A', 'S' ) ) END AS DEAL, CASE WHEN ( LEFT( S.DEAL_TY, 1 ) <> 'K' ) THEN '0' ELSE S.DEAL_FG END AS DEAL_FG, S.USER1_TY, CASE WHEN ( LEFT( S.USER1_TY, 1 ) = 'L' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.USER1_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE U.USER_DEF_WORD END AS USER1, CASE WHEN ( LEFT( S.USER1_TY, 1 ) <> 'L' ) THEN '0' ELSE S.USER1_FG END AS USER1_FG, S.USER2_TY, CASE WHEN ( LEFT( S.USER2_TY, 1 ) = 'M' ) THEN ( SELECT TR.CTRL_NM FROM SCTRL TR WHERE S.CO_CD = TR.CO_CD AND S.USER2_TY = TR.CTRL_CD AND TR.MODULE_CD IN ( 'A', 'S' ) ) ELSE U.USER_DEF_WORD END AS USER2, CASE WHEN ( LEFT( S.USER2_TY, 1 ) <> 'M' ) THEN '0' ELSE S.USER2_FG END AS USER2_FG, ISNULL(S.SUB_DISP,'00') AS SUB_DISP, S.BUD_FG, S.DRCR_FG, S.ACCT_FG, S.FILL_YN, S.GROUP_CD, S.PRT_NM, S.PRT_NMK FROM SACCT S LEFT OUTER JOIN SDBCFG B ON '1'='1' LEFT OUTER JOIN (SELECT DBO.UFN_GET_STRING_BY_LANGKIND( ( SELECT FSTLANG FROM SDBCFG ), N'사용자정의') USER_DEF_WORD) U ON '1'='1' WHERE S.CO_CD > '0000' AND S.ACCT_FG > '0'