Untitled

CREATE TABLE BUSA_DOC_DETL_VAS_ITEM_INFM
(
    BIZ_KEY     VARCHAR(20),
    USER_ID     VARCHAR(100),
    DOC_ID      VARCHAR(50),
    DISP_ORD    NUMERIC(10),
    VAS_ACCT_NM VARCHAR(200),
    VAS_CD      VARCHAR(10),
    ITEM_CD     VARCHAR(10),
    HAND_REG_YN VARCHAR(1) DEFAULT 'Y',
    MEMO        TEXT,
    REG_ID      VARCHAR(100),
    REG_DTM     VARCHAR(14),
    MOD_ID      VARCHAR(100),
    MOD_DTM     VARCHAR(14)
);


COMMENT ON TABLE BUSA_DOC_DETL_VAS_ITEM_INFM IS 'BUSA_DOC_DETL_VAS_ITEM_INFM';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.BIZ_KEY IS '사업자키';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.USER_ID IS '사용자ID';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.DOC_ID IS '문서ID';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.DISP_ORD IS '표시순서';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.VAS_ACCT_NM IS 'VAS Account Name';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.VAS_CD IS 'VAS Code';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.ITEM_CD IS '품목코드';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.HAND_REG_YN IS '수기등록여부';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.MEMO IS 'Memo';
COMMENT ON COLUMN ACCT_TRNS_RCV_PAY_INFM.REG_ID IS '등록자ID';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.REG_DTM IS '등록일시';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.MOD_ID IS '수정자ID';
COMMENT ON COLUMN BUSA_DOC_DETL_VAS_ITEM_INFM.MOD_DTM IS '수정일시';


ALTER TABLE BUSA_DOC_DETL_VAS_ITEM_INFM
    ADD CONSTRAINT PK_BUSA_DOC_DETL_VAS_ITEM_INFM PRIMARY KEY (
                                                               BIZ_KEY, DOC_ID, DISP_ORD
        );


ALTER TABLE BUSA_DOC_DETL_VAS_ITEM_INFM ALTER COLUMN BIZ_KEY SET NOT NULL;
ALTER TABLE BUSA_DOC_DETL_VAS_ITEM_INFM ALTER COLUMN USER_ID SET NOT NULL;
ALTER TABLE BUSA_DOC_DETL_VAS_ITEM_INFM ALTER COLUMN DOC_ID SET NOT NULL;
ALTER TABLE BUSA_DOC_DETL_VAS_ITEM_INFM ALTER COLUMN DISP_ORD SET NOT NULL;
ALTER TABLE BUSA_DOC_DETL_VAS_ITEM_INFM ALTER COLUMN HAND_REG_YN SET NOT NULL;


--- BUSA_DOC_DETL_VAS_ITEM_INFM_C001 ---
INSERT INTO BUSA_DOC_DETL_VAS_ITEM_INFM (
    BIZ_KEY,
    USER_ID,
    DOC_ID,
    DISP_ORD,
    VAS_ACCT_NM,
    VAS_CD,
    ITEM_CD,
    HAND_REG_YN,
    MEMO,
    REG_ID,
    REG_DTM,
    MOD_ID,
    MOD_DTM
) VALUES (
             :BIZ_KEY,
             :USER_ID,
             :DOC_ID,
             :DISP_ORD,
             :VAS_ACCT_NM,
             :VAS_CD,
             :ITEM_CD,
             :HAND_REG_YN,
             :MEMO,
             :REG_ID,
             :REG_DTM,
             :MOD_ID,
             :MOD_DTM
         );


--- BUSA_DOC_DETL_VAS_ITEM_INFM_R001 ---
SELECT
    BIZ_KEY,
    USER_ID,
    DOC_ID,
    DISP_ORD,
    VAS_ACCT_NM,
    VAS_CD,
    ITEM_CD,
    HAND_REG_YN,
    MEMO,
    REG_ID,
    REG_DTM,
    MOD_ID,
    MOD_DTM
FROM BUSA_DOC_DETL_VAS_ITEM_INFM
WHERE 1 = 1
    ??;


--- BUSA_DOC_DETL_VAS_ITEM_INFM_U001 ---
UPDATE BUSA_DOC_DETL_VAS_ITEM_INFM
SET
    USER_ID     = COALESCE(:USER_ID, USER_ID),
    VAS_ACCT_NM = COALESCE(:VAS_ACCT_NM, VAS_ACCT_NM),
    VAS_CD      = COALESCE(:VAS_CD, VAS_CD),
    ITEM_CD     = COALESCE(:ITEM_CD, ITEM_CD),
    HAND_REG_YN = COALESCE(:HAND_REG_YN, HAND_REG_YN),
    MEMO        = COALESCE(:MEMO, MEMO),
    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 DOC_ID = :DOC_ID
  AND DISP_ORD = :DISP_ORD;


--- BUSA_DOC_DETL_VAS_ITEM_INFM_D001 ---
DELETE FROM BUSA_DOC_DETL_VAS_ITEM_INFM
WHERE BIZ_KEY = :BIZ_KEY
  AND DOC_ID = :DOC_ID
  AND DISP_ORD = :DISP_ORD;


--- BUSA_DOC_DETL_VAS_ITEM_INFM_U000 ---
UPDATE BUSA_DOC_DETL_VAS_ITEM_INFM
SET FIELDS_TO_UPDATE = :FIELDS_TO_UPDATE
WHERE BIZ_KEY = :BIZ_KEY
  AND DOC_ID = :DOC_ID
  AND DISP_ORD = :DISP_ORD;



--- BUSA_DOC_DETL_VAS_ITEM_INFM_R001_CNT ---
SELECT COUNT(*) AS TOT_CNT
FROM BUSA_DOC_DETL_VAS_ITEM_INFM
WHERE 1 = 1
    ??;