create or replace package body PKG_SYS_VENDGROUP_VEND is
PROCEDURE SP_ADD_VENDGROUP_VEND(P_OUT_NUM OUT NUMBER,
                                P_OUT_MSG OUT VARCHAR2,
                                P_VENDGROUP_ID IN NUMBER,
                                P_VEND_ID IN NUMBER,
                                P_LOTNO_HDR IN VARCHAR2,
                                P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
    INSERT INTO SYS_VENDGROUP_VEND(
           VENDGROUPVEND_KEY,
           VENDGROUP_ID,
           VEND_ID,
           LOTNO_HDR,
           ISVALID,
           UPDATE_DATE,
           UPDATEBYUSER_CODE)
    VALUES(SQ_VENDGROUP_VEND.NEXTVAL,
           P_VENDGROUP_ID,
           P_VEND_ID,
           P_LOTNO_HDR,
           'Y',
           SYSDATE,
           P_UPDATEBYUSER_CODE);
    
    COMMIT;
    
    P_OUT_NUM := 0;
    P_OUT_MSG := 'INSERT VENDGROUPVEND SUCCESS!';

    PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUPVEND',
                             'SUCCESS',
                             'INS VENDGROUPVEND',
                             P_OUT_MSG,
                             P_OUT_NUM);
    
    EXCEPTION
    WHEN OTHERS THEN
         ROLLBACK;
         P_OUT_NUM := SQLCODE;
         P_OUT_MSG := SUBSTR(SQLERRM,1,500);
         PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUPVEND',
                             'ERROR',
                             'INS VENDGROUPVEND',
                             P_OUT_MSG,
                             P_OUT_NUM);              
           
END;
                                  
PROCEDURE SP_DELETE_VENDGROUP_VEND(P_OUT_NUM OUT NUMBER,
                                   P_OUT_MSG OUT VARCHAR2,
                                   P_VENDGROUP_ID IN NUMBER,
                                   P_VEND_ID IN NUMBER,
                                   P_UPDATEBYUSER_CODE IN VARCHAR2) IS
    V_EFFECT_ROW NUMBER := 0;
BEGIN
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_VENDGROUP_VEND
    WHERE VENDGROUP_ID = P_VENDGROUP_ID
      AND VEND_ID = P_VEND_ID;
    
    IF V_EFFECT_ROW = 0 THEN
        P_OUT_NUM := 100;
        P_OUT_MSG := 'VENDGROUPVEND NOT EXISTS!';

        PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUPVEND',
                             'SUCCESS',
                             'DEL VENDGROUPVEND',
                             P_OUT_MSG,
                             P_OUT_NUM);
        RETURN;                     
    END IF;
    
    DELETE FROM SYS_VENDGROUP_VEND
    WHERE VENDGROUP_ID = P_VENDGROUP_ID
      AND VEND_ID = P_VEND_ID;
    
    COMMIT;
    
    P_OUT_NUM := 0;
    P_OUT_MSG := 'DELETE VENDGROUPVEND SUCCESS!';

    PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUPVEND',
                             'SUCCESS',
                             'DEL VENDGROUPVEND',
                             P_OUT_MSG,
                             P_OUT_NUM);
    
    EXCEPTION
    WHEN OTHERS THEN
         ROLLBACK;
         P_OUT_NUM := SQLCODE;
         P_OUT_MSG := SUBSTR(SQLERRM,1,500);
         PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUPVEND',
                             'ERROR',
                             'DEL VENDGROUPVEND',
                             P_OUT_MSG,
                             P_OUT_NUM);              
END;                                   

end PKG_SYS_VENDGROUP_VEND;
/