Untitled

CREATE TABLE TAX_USAG_INFM
(
    USE_USAG_CD VARCHAR(20),
    LANG_CODE   VARCHAR(2) DEFAULT 'EN',
    USE_YN      VARCHAR(1) DEFAULT 'Y',
    USAG_GRP_CD VARCHAR(3),
    USAG_NM     VARCHAR(100),
    USAG_CONT   TEXT,
    REG_ID      VARCHAR(100),
    REG_DTM     VARCHAR(14),
    MOD_ID      VARCHAR(100),
    MOD_DTM     VARCHAR(14)
);

COMMENT ON TABLE TAX_USAG_INFM IS 'Tax Usag Information';
COMMENT ON COLUMN TAX_USAG_INFM.USE_USAG_CD IS '사용용도코드';
COMMENT ON COLUMN TAX_USAG_INFM.LANG_CODE IS '언어코드';
COMMENT ON COLUMN TAX_USAG_INFM.USE_YN IS '사용여부';
COMMENT ON COLUMN TAX_USAG_INFM.USAG_GRP_CD IS '용도그룹코드';
COMMENT ON COLUMN TAX_USAG_INFM.USAG_NM IS '용도명';
COMMENT ON COLUMN TAX_USAG_INFM.USAG_CONT IS '용도내용';
COMMENT ON COLUMN TAX_USAG_INFM.REG_ID IS '등록자ID';
COMMENT ON COLUMN TAX_USAG_INFM.REG_DTM IS '등록일시';
COMMENT ON COLUMN TAX_USAG_INFM.MOD_ID IS '수정자ID';
COMMENT ON COLUMN TAX_USAG_INFM.MOD_DTM IS '수정일시';

ALTER TABLE TAX_USAG_INFM ADD CONSTRAINT PK_TAX_USAG_INFM PRIMARY KEY (
                                                                       USE_USAG_CD, LANG_CODE
    );

ALTER TABLE TAX_USAG_INFM ALTER COLUMN USE_USAG_CD SET NOT NULL;
ALTER TABLE TAX_USAG_INFM ALTER COLUMN LANG_CODE SET NOT NULL;
ALTER TABLE TAX_USAG_INFM ALTER COLUMN USE_YN SET NOT NULL;
ALTER TABLE TAX_USAG_INFM ALTER COLUMN USAG_GRP_CD SET NOT NULL;
ALTER TABLE TAX_USAG_INFM ALTER COLUMN USAG_NM SET NOT NULL;





-- TAX_USAG_INFM_C001

INSERT INTO TAX_USAG_INFM (
    USE_USAG_CD,
    LANG_CODE,
    USE_YN,
    USAG_GRP_CD,
    USAG_NM,
    USAG_CONT,
    REG_ID,
    REG_DTM,
    MOD_ID,
    MOD_DTM
) VALUES (
             :USE_USAG_CD,
             :LANG_CODE,
             :USE_YN,
             :USAG_GRP_CD,
             :USAG_NM,
             :USAG_CONT,
             :REG_ID,
             :REG_DTM,
             :MOD_ID,
             :MOD_DTM
         );

-- TAX_USAG_INFM_R001
SELECT
    USE_USAG_CD,
    LANG_CODE,
    USE_YN,
    USAG_GRP_CD,
    USAG_NM,
    USAG_CONT,
    REG_ID,
    REG_DTM,
    MOD_ID,
    MOD_DTM
FROM TAX_USAG_INFM
WHERE 1 = 1
    ??;


-- TAX_USAG_INFM_U001
UPDATE TAX_USAG_INFM
SET
    USE_YN      = COALESCE(:USE_YN, USE_YN),
    USAG_GRP_CD = COALESCE(:USAG_GRP_CD, USAG_GRP_CD),
    USAG_NM     = COALESCE(:USAG_NM, USAG_NM),
    USAG_CONT   = COALESCE(:USAG_CONT, USAG_CONT),
    MOD_ID      = COALESCE(:MOD_ID, MOD_ID),
    MOD_DTM     = COALESCE(:MOD_DTM, MOD_DTM)
WHERE USE_USAG_CD = :USE_USAG_CD
  AND LANG_CODE = :LANG_CODE;


-- TAX_USAG_INFM_D001
DELETE FROM TAX_USAG_INFM
WHERE USE_USAG_CD = :USE_USAG_CD
  AND LANG_CODE = :LANG_CODE;


-- TAX_USAG_INFM_U000
UPDATE TAX_USAG_INFM
SET FIELDS_TO_UPDATE = :FIELDS_TO_UPDATE
WHERE USE_USAG_CD = :USE_USAG_CD
  AND LANG_CODE = :LANG_CODE;


-- TAX_USAG_INFM_R001_CNT
SELECT COUNT(*) AS TOT_CNT
FROM TAX_USAG_INFM
WHERE 1 = 1
    ??;