Untitled

CREATE TABLE BUSI_DOC_VAS_ITEM_ACTV_HIS
(
    BUSA_ACTV_SEQ VARCHAR(20),
    BIZ_KEY       VARCHAR(20),
    USER_ID       VARCHAR(100),
    DOC_ID        VARCHAR(50),
    DV_CD         CHAR(1),
    ACTV_CD       CHAR(1),
    FLD_TYPE      CHAR(1),
    REF_NO        VARCHAR(500),
    REF_NO2       VARCHAR(500),
    REG_ID        VARCHAR(100),
    REG_DTM       VARCHAR(14),
    MOD_ID        VARCHAR(100),
    MOD_DTM       VARCHAR(14)

);


ALTER TABLE BUSI_DOC_VAS_ITEM_ACTV_HIS
    ADD CONSTRAINT PK_BUSI_DOC_VAS_ITEM_ACTV_HIS PRIMARY KEY (
                                                              BUSA_ACTV_SEQ
        );

COMMENT ON TABLE BUSI_DOC_VAS_ITEM_ACTV_HIS IS 'BUSI_DOC_VAS_ITEM_ACTV_HIS';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.BUSA_ACTV_SEQ IS 'Sales and Expenses Activity Sequence';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.BIZ_KEY IS '사업자키';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.USER_ID IS '사용자ID';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.DOC_ID IS '문서ID';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.DV_CD IS '구분코드';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.ACTV_CD IS 'Activity Code';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.FLD_TYPE IS 'Field Type';
COMMENT ON COLUMN ACCT_TRNS_RCV_PAY_INFM.REF_NO IS '참조번호';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.REF_NO2 IS '참조번호2';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.REG_ID IS '등록자ID';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.REG_DTM IS '등록일시';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.MOD_ID IS '수정자ID';
COMMENT ON COLUMN BUSI_DOC_VAS_ITEM_ACTV_HIS.MOD_DTM IS '수정일시';



ALTER TABLE BUSI_DOC_VAS_ITEM_ACTV_HIS
    ALTER COLUMN BUSA_ACTV_SEQ SET NOT NULL;
ALTER TABLE BUSI_DOC_VAS_ITEM_ACTV_HIS
    ALTER COLUMN BIZ_KEY SET NOT NULL;
ALTER TABLE BUSI_DOC_VAS_ITEM_ACTV_HIS
    ALTER COLUMN USER_ID SET NOT NULL;
ALTER TABLE BUSI_DOC_VAS_ITEM_ACTV_HIS
    ALTER COLUMN DOC_ID SET NOT NULL;
ALTER TABLE BUSI_DOC_VAS_ITEM_ACTV_HIS
    ALTER COLUMN ACTV_CD SET NOT NULL;

--- BUSI_DOC_VAS_ITEM_ACTV_HIS_C001 ---
INSERT INTO BUSI_DOC_VAS_ITEM_ACTV_HIS ( BUSA_ACTV_SEQ
                                       , BIZ_KEY
                                       , USER_ID
                                       , DOC_ID
                                       , DV_CD
                                       , ACTV_CD
                                       , FLD_TYPE
                                       , REF_NO
                                       , REF_NO2
                                       , REG_ID
                                       , REG_DTM
                                       , MOD_ID
                                       , MOD_DTM)
VALUES ( :BUSA_ACTV_SEQ
       , :BIZ_KEY
       , :USER_ID
       , :DOC_ID
       , :DV_CD
       , :ACTV_CD
       , :FLD_TYPE
       , :REF_NO
       , :REF_NO2
       , :REG_ID
       , :REG_DTM
       , :MOD_ID
       , :MOD_DTM);


--- BUSI_DOC_VAS_ITEM_ACTV_HIS_R001 ---
SELECT BUSA_ACTV_SEQ
     , BIZ_KEY
     , USER_ID
     , DOC_ID
     , DV_CD
     , ACTV_CD
     , FLD_TYPE
     , REF_NO
     , REF_NO2
     , REG_ID
     , REG_DTM
     , MOD_ID
     , MOD_DTM
FROM BUSI_DOC_VAS_ITEM_ACTV_HIS
WHERE 1 = 1
    ??;

--- BUSI_DOC_VAS_ITEM_ACTV_HIS_U001 ---
UPDATE BUSI_DOC_VAS_ITEM_ACTV_HIS
SET BIZ_KEY  = COALESCE(:BIZ_KEY, BIZ_KEY)
  , USER_ID  = COALESCE(:USER_ID, USER_ID)
  , DOC_ID   = COALESCE(:DOC_ID, DOC_ID)
  , DV_CD    = COALESCE(:DV_CD, DV_CD)
  , ACTV_CD  = COALESCE(:ACTV_CD, ACTV_CD)
  , FLD_TYPE = COALESCE(:FLD_TYPE, FLD_TYPE)
  , REF_NO   = COALESCE(:REF_NO, REF_NO)
  , REF_NO2  = COALESCE(:REF_NO2, REF_NO2)
  , MOD_ID   = COALESCE(:MOD_ID, MOD_ID)
  , MOD_DTM  = COALESCE(:MOD_DTM, MOD_DTM)
WHERE BUSA_ACTV_SEQ = :BUSA_ACTV_SEQ;

--- BUSI_DOC_VAS_ITEM_ACTV_HIS_D001 ---
DELETE
FROM BUSI_DOC_VAS_ITEM_ACTV_HIS
WHERE BUSA_ACTV_SEQ = :BUSA_ACTV_SEQ
??;


--- BUSI_DOC_VAS_ITEM_ACTV_HIS_U000 ---
UPDATE BUSI_DOC_VAS_ITEM_ACTV_HIS
SET FIELDS_TO_UPDATE = :FIELDS_TO_UPDATE
    WHERE BUSA_ACTV_SEQ = :BUSA_ACTV_SEQ;


-- BUSI_DOC_VAS_ITEM_ACTV_HIS_R001_CNT ---
SELECT COUNT(*) AS TOT_CNT
FROM BUSI_DOC_VAS_ITEM_ACTV_HIS
WHERE 1 = 1
    ??;