create or replace package body PKG_SYS_APPGROUP is
PROCEDURE SP_ADD_APPGROUP(P_OUT_NUM OUT NUMBER,
                          P_OUT_MSG OUT VARCHAR2,
                          P_APPGROUP_ID OUT NUMBER,
                          P_APPGROUP_CODE IN VARCHAR2,
                          P_APPGROUPDESC IN VARCHAR2,
                          P_ISVALID IN VARCHAR2,
                          P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
   INSERT INTO SYS_APPGROUP(
          APPGROUP_ID, 
          APPGROUP_CODE,
          APPGROUPDESC,
          ISVALID,
          UPDATEDATE,
          UPDATEBYUSER_CODE)
   VALUES(SQ_APPGROUP.NEXTVAL,
         P_APPGROUP_CODE,
         P_APPGROUPDESC,
         P_ISVALID,
         SYSDATE,
         P_UPDATEBYUSER_CODE);
         
   COMMIT;
   
   SELECT MAX(APPGROUP_ID) INTO P_APPGROUP_ID FROM SYS_APPGROUP;
         
   P_OUT_NUM := 0;
   P_OUT_MSG := 'INERT NEW APPGROUP SUCCESS!';

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

PROCEDURE SP_UPDATE_APPGROUP(P_OUT_NUM OUT NUMBER,
                            P_OUT_MSG OUT VARCHAR2,
                            P_APPGROUP_ID IN NUMBER,
                            P_APPGROUP_CODE IN VARCHAR2,
                            P_APPGROUPDESC 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_APPGROUP 
   WHERE APPGROUP_ID = P_APPGROUP_ID;
   
   IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 100;
       P_OUT_MSG := 'APPGROUP NOT EXISTS!';

        PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'APGROUP',
                             'SUCCESS',
                             'UPD APPGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);
   END IF;
   
   UPDATE SYS_APPGROUP SET
        APPGROUP_CODE = P_APPGROUP_CODE,
        APPGROUPDESC = P_APPGROUPDESC,
        ISVALID = P_ISVALID,
        UPDATEDATE = SYSDATE,
        UPDATEBYUSER_CODE = P_UPDATEBYUSER_CODE
   WHERE APPGROUP_ID = P_APPGROUP_ID;     
   
   COMMIT;

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

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

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'APPGROUP',
                             'SUCCESS',
                             'DEL APPGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);
   END IF;
   
   DELETE FROM SYS_APPGROUP
   WHERE APPGROUP_ID = P_APPGROUP_ID;
   
   COMMIT;
   
   P_OUT_NUM := 0;
   P_OUT_MSG := 'DELETE APPGROUP SUCCESS!';

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