create or replace package body PKG_SYS_VENDGROUP is
PROCEDURE SP_ADD_VENDGROUP(P_OUT_NUM OUT NUMBER,
                          P_OUT_MSG OUT VARCHAR2,
                          P_VENDGROUP_ID OUT NUMBER,
                          P_VENDGROUP_CODE IN VARCHAR2,
                          P_VENDGROUPDESC IN VARCHAR2,
                          P_ISVALID IN VARCHAR2,
                          P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
   INSERT INTO SYS_VENDGROUP(
          VENDORGROUP_ID, 
          VENDORGROUP_CODE,
          VENDORGROUPDESC,
          ISVALID,
          UPDATEDATE,
          UPDATEBYUSER_CODE)
   VALUES(SQ_VENDGROUP.NEXTVAL,
         P_VENDGROUP_CODE,
         P_VENDGROUPDESC,
         P_ISVALID,
         SYSDATE,
         P_UPDATEBYUSER_CODE);
         
   COMMIT;
   
   SELECT MAX(VENDORGROUP_ID) INTO P_VENDGROUP_ID FROM SYS_VENDGROUP;
         
   P_OUT_NUM := 0;
   P_OUT_MSG := 'INERT NEW VENDGROUP SUCCESS!';

    PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUP',
                             'SUCCESS',
                             'ADD VENDGROUP',
                             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,
                             'VENDGROUP',
                             'ERROR',
                             'ADD VENDGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);           
END;                            

PROCEDURE SP_UPDATE_VENDGROUP(P_OUT_NUM OUT NUMBER,
                            P_OUT_MSG OUT VARCHAR2,
                            P_VENDGROUP_ID IN NUMBER,
                            P_VENDGROUP_CODE IN VARCHAR2,
                            P_VENDGROUPDESC IN VARCHAR2,
                            P_ISVALID IN VARCHAR2,
                            P_UPDATEBYUSER_CODE IN VARCHAR2) IS
   V_EFFECT_ROW NUMBER := 0;
BEGIN
   SELECT COUNT(*) INTO V_EFFECT_ROW
   FROM SYS_VENDGROUP 
   WHERE VENDORGROUP_ID = P_VENDGROUP_ID;
   
   IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 100;
       P_OUT_MSG := 'VENDGROUP NOT EXISTS!';

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUP',
                             'SUCCESS',
                             'UPD VENDGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);
       RETURN;                      
   END IF;
   
   UPDATE SYS_VENDGROUP SET
        VENDORGROUP_CODE = P_VENDGROUP_CODE,
        VENDORGROUPDESC = P_VENDGROUPDESC,
        ISVALID = P_ISVALID,
        UPDATEDATE = SYSDATE,
        UPDATEBYUSER_CODE = P_UPDATEBYUSER_CODE
   WHERE VENDORGROUP_ID = P_VENDGROUP_ID;
   
   COMMIT;

   P_OUT_NUM := 0;
   P_OUT_MSG := 'UPDATE VENDGROUP SUCCESS!';

    PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUP',
                             'SUCCESS',
                             'UPD VENDGROUP',
                             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,
                             'VENDGROUP',
                             'ERROR',
                             'UPD VENDGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);           
END;
                            
PROCEDURE SP_DELETE_VENDGROUP(P_OUT_NUM OUT NUMBER,
                            P_OUT_MSG OUT VARCHAR2,
                            P_VENDGROUP_ID IN NUMBER,
                            P_UPDATEBYUSER_CODE IN VARCHAR2) IS
   V_EFFECT_ROW NUMBER := 0;
BEGIN
   SELECT COUNT(*) INTO V_EFFECT_ROW
   FROM SYS_VENDGROUP 
   WHERE VENDORGROUP_ID = P_VENDGROUP_ID;
   
   IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 100;
       P_OUT_MSG := 'VENDGROUP NOT EXISTS!';

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUP',
                             'SUCCESS',
                             'DEL VENDGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);
      RETURN;                       
   END IF;
   
   DELETE FROM SYS_VENDGROUP
   WHERE VENDORGROUP_ID = P_VENDGROUP_ID;
   
   COMMIT;
   
   P_OUT_NUM := 0;
   P_OUT_MSG := 'DELETE VENDGROUP SUCCESS!';

    PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'VENDGROUP',
                             'SUCCESS',
                             'DEL VENDGROUP',
                             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,
                             'VENDGROUP',
                             'ERROR',
                             'DEL VENDGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);           
      
END;
end PKG_SYS_VENDGROUP;
/