Untitled

CREATE TABLE ACCT_MSTR_VAS_ACCT
(
    BIZ_KEY       VARCHAR(20),
    WTA_BIZ_KEY   VARCHAR(20),
    CCY_CD        CHAR(3),
    BANK_CD       VARCHAR(20),
    ACCT_NO       VARCHAR(100),
    VAS_ACCT_SEQ  VARCHAR(20),
    ACCT_VAS_DESP VARCHAR(500),
    REG_ID        VARCHAR(100),
    REG_DTM       VARCHAR(14),
    MOD_ID        VARCHAR(100),
    MOD_DTM       VARCHAR(14)
);



COMMENT ON TABLE ACCT_MSTR_VAS_ACCT IS 'ACCT_MSTR_VAS_ACCT';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.BIZ_KEY IS '사업자키';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.WTA_BIZ_KEY IS 'WABOOKS Biz Key';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.CCY_CD IS '통화코드';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.BANK_CD IS '은행코드20';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.ACCT_NO IS '계좌번호';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.VAS_ACCT_SEQ IS 'VAS Account Sequence';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.ACCT_VAS_DESP IS 'Account VAS Description';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.REG_ID IS '등록자ID';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.REG_DTM IS '등록일시';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.MOD_ID IS '수정자ID';
COMMENT ON COLUMN ACCT_MSTR_VAS_ACCT.MOD_DTM IS '수정일시';



ALTER TABLE ACCT_MSTR_VAS_ACCT
    ADD CONSTRAINT PK_ACCT_MSTR_VAS_ACCT PRIMARY KEY (
                                                      BIZ_KEY, WTA_BIZ_KEY
        );


ALTER TABLE ACCT_MSTR_VAS_ACCT
    ALTER COLUMN BIZ_KEY SET NOT NULL;
ALTER TABLE ACCT_MSTR_VAS_ACCT
    ALTER COLUMN WTA_BIZ_KEY SET NOT NULL;
ALTER TABLE ACCT_MSTR_VAS_ACCT
    ALTER COLUMN BANK_CD SET NOT NULL;
ALTER TABLE ACCT_MSTR_VAS_ACCT
    ALTER COLUMN ACCT_NO SET NOT NULL;

---ACCT_MSTR_VAS_ACCT_C001
INSERT INTO ACCT_MSTR_VAS_ACCT ( BIZ_KEY
                               , WTA_BIZ_KEY
                               , CCY_CD
                               , BANK_CD
                               , ACCT_NO
                               , VAS_ACCT_SEQ
                               , ACCT_VAS_DESP
                               , REG_ID
                               , REG_DTM
                               , MOD_ID
                               , MOD_DTM)
VALUES ( :BIZ_KEY
       , :WTA_BIZ_KEY
       , :CCY_CD
       , :BANK_CD
       , :ACCT_NO
       , :VAS_ACCT_SEQ
       , :ACCT_VAS_DESP
       , :REG_ID
       , :REG_DTM
       , :MOD_ID
       , :MOD_DTM);

--- ACCT_MSTR_VAS_ACCT_R001
SELECT BIZ_KEY
     , WTA_BIZ_KEY
     , CCY_CD
     , BANK_CD
     , ACCT_NO
     , VAS_ACCT_SEQ
     , ACCT_VAS_DESP
     , REG_ID
     , REG_DTM
     , MOD_ID
     , MOD_DTM
FROM ACCT_MSTR_VAS_ACCT
WHERE 1 = 1
    ??;
--- ACCT_MSTR_VAS_ACCT_U001
UPDATE ACCT_MSTR_VAS_ACCT
SET CCY_CD        = COALESCE(:CCY_CD, CCY_CD)
  , BANK_CD       = COALESCE(:BANK_CD, BANK_CD)
  , ACCT_NO       = COALESCE(:ACCT_NO, ACCT_NO)
  , VAS_ACCT_SEQ  = COALESCE(:VAS_ACCT_SEQ, VAS_ACCT_SEQ)
  , ACCT_VAS_DESP = COALESCE(:ACCT_VAS_DESP, ACCT_VAS_DESP)
  , 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;


--- ACCT_MSTR_VAS_ACCT_D001
DELETE
FROM ACCT_MSTR_VAS_ACCT
WHERE BIZ_KEY = :BIZ_KEY
  AND WTA_BIZ_KEY = :WTA_BIZ_KEY;


--- ACCT_MSTR_VAS_ACCT_U000
UPDATE ACCT_MSTR_VAS_ACCT
SET FIELDS_TO_UPDATE = :FIELDS_TO_UPDATE
WHERE BIZ_KEY = :BIZ_KEY
  AND WTA_BIZ_KEY = :WTA_BIZ_KEY;


--- ACCT_MSTR_VAS_ACCT_R001_CNT
SELECT COUNT(*) AS TOT_CNT
FROM ACCT_MSTR_VAS_ACCT
WHERE 1 = 1
    ??;