Untitled

CREATE TABLE VAS_ACCT_INFM
(
    BIZ_KEY      VARCHAR(20),
    WTA_BIZ_KEY  VARCHAR(20),
    VAS_ACCT_SEQ VARCHAR(20),
    VAS_CD       VARCHAR(10),
    VAS_ACCT_NM  VARCHAR(200),
    STTS         VARCHAR(1) DEFAULT 'Y',
    REG_ID       VARCHAR(100),
    REG_DTM      VARCHAR(14),
    MOD_ID       VARCHAR(100),
    MOD_DTM      VARCHAR(14)
);



COMMENT ON TABLE VAS_ACCT_INFM IS '회계계정과목';
COMMENT ON COLUMN VAS_ACCT_INFM.BIZ_KEY IS '사업자키';
COMMENT ON COLUMN VAS_ACCT_INFM.WTA_BIZ_KEY IS 'WABOOKS Biz Key';
COMMENT ON COLUMN VAS_ACCT_INFM.VAS_ACCT_SEQ IS 'VAS Account Sequence';
COMMENT ON COLUMN VAS_ACCT_INFM.VAS_CD IS 'VAS Code';
COMMENT ON COLUMN VAS_ACCT_INFM.VAS_ACCT_NM IS 'VAS Account Name';
COMMENT ON COLUMN VAS_ACCT_INFM.STTS IS 'Status';
COMMENT ON COLUMN VAS_ACCT_INFM.REG_ID IS '등록자ID';
COMMENT ON COLUMN VAS_ACCT_INFM.REG_DTM IS '등록일시';
COMMENT ON COLUMN VAS_ACCT_INFM.MOD_ID IS '수정자ID';
COMMENT ON COLUMN VAS_ACCT_INFM.MOD_DTM IS '수정일시';



ALTER TABLE VAS_ACCT_INFM
    ADD CONSTRAINT PK_VAS_ACCT_INFM PRIMARY KEY (
                                                 BIZ_KEY, WTA_BIZ_KEY, VAS_ACCT_SEQ
        );

ALTER TABLE VAS_ACCT_INFM
    ALTER COLUMN BIZ_KEY SET NOT NULL;
ALTER TABLE VAS_ACCT_INFM
    ALTER COLUMN WTA_BIZ_KEY SET NOT NULL;
ALTER TABLE VAS_ACCT_INFM
    ALTER COLUMN VAS_ACCT_SEQ SET NOT NULL;
ALTER TABLE VAS_ACCT_INFM
    ALTER COLUMN VAS_CD SET NOT NULL;
ALTER TABLE VAS_ACCT_INFM
    ALTER COLUMN VAS_ACCT_NM SET NOT NULL;
ALTER TABLE VAS_ACCT_INFM
    ALTER COLUMN STTS SET NOT NULL;


--- VAS_ACCT_INFM_C001
INSERT INTO VAS_ACCT_INFM (BIZ_KEY,
                           WTA_BIZ_KEY,
                           VAS_ACCT_SEQ,
                           VAS_CD,
                           VAS_ACCT_NM,
                           STTS,
                           REG_ID,
                           REG_DTM,
                           MOD_ID,
                           MOD_DTM)
VALUES (:BIZ_KEY,
        :WTA_BIZ_KEY,
        :VAS_ACCT_SEQ,
        :VAS_CD,
        :VAS_ACCT_NM,
        :STTS,
        :REG_ID,
        :REG_DTM,
        :MOD_ID,
        :MOD_DTM);


--- VAS_ACCT_INFM_R001
SELECT BIZ_KEY,
       WTA_BIZ_KEY,
       VAS_ACCT_SEQ,
       VAS_CD,
       VAS_ACCT_NM,
       STTS,
       REG_ID,
       REG_DTM,
       MOD_ID,
       MOD_DTM
FROM VAS_ACCT_INFM
WHERE 1 = 1
    ??;


--- VAS_ACCT_INFM_U001
UPDATE VAS_ACCT_INFM
SET VAS_CD      = COALESCE(:VAS_CD, VAS_CD),
    VAS_ACCT_NM = COALESCE(:VAS_ACCT_NM, VAS_ACCT_NM),
    STTS        = COALESCE(:STTS, STTS),
    REG_ID      = COALESCE(:REG_ID, REG_ID),
    REG_DTM     = COALESCE(:REG_DTM, REG_DTM),
    MOD_ID      = COALESCE(:MOD_ID, MOD_ID),
    MOD_DTM     = COALESCE(:MOD_DTM, MOD_DTM)
WHERE BIZ_KEY = :BIZ_KEY
  AND WTA_BIZ_KEY = :WTA_BIZ_KEY
  AND VAS_ACCT_SEQ = :VAS_ACCT_SEQ;


--- VAS_ACCT_INFM_D001
DELETE
FROM VAS_ACCT_INFM
WHERE BIZ_KEY = :BIZ_KEY
  AND WTA_BIZ_KEY = :WTA_BIZ_KEY
  AND VAS_ACCT_SEQ = :VAS_ACCT_SEQ;


--- VAS_ACCT_INFM_U000
UPDATE VAS_ACCT_INFM
SET FIELDS_TO_UPDATE = :FIELDS_TO_UPDATE
WHERE BIZ_KEY = :BIZ_KEY
  AND WTA_BIZ_KEY = :WTA_BIZ_KEY
  AND VAS_ACCT_SEQ = :VAS_ACCT_SEQ;


--- VAS_ACCT_INFM_R001_CNT
SELECT COUNT(*) AS TOT_CNT
FROM VAS_ACCT_INFM
WHERE 1 = 1
    ??;