create or replace package body PKG_SYS_APPGROUP_FEATURE is
PROCEDURE SP_ADD_APPGROUP_FEATURE(P_OUT_NUM OUT NUMBER,
                                  P_OUT_MSG OUT VARCHAR2,
                                  P_APPGROUP_ID IN NUMBER,
                                  P_APP_CODE IN VARCHAR2,
                                  P_APPFEATURE_CODE IN VARCHAR2,
                                  P_VIEW_ENABLED IN VARCHAR2,
                                  P_APPEND_ENABLED IN VARCHAR2,
                                  P_UPDATE_ENABLED IN VARCHAR2,
                                  P_DELETE_ENABLED IN VARCHAR2,
                                  P_EXPORT_ENABLED IN VARCHAR2,
                                  P_ISVALID IN VARCHAR2,
                                  P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
    INSERT INTO SYS_APPGROUP_FEATURE
            (APPGROUPFEATURE_KEY,
             APPGROUP_ID,
             APP_CODE,
             APPFEATURE_CODE,
             VIEW_ENABLED,
             APPEND_ENABLED,
             UPDATE_ENABLED,
             DELETE_ENABLED,
             EXPORT_ENABLED,
             ISVALID,
             UPDATEDATE,
             UPDATEBYUSER_CODE) 
      VALUES(SQ_SYS_APPGROUP_FEATURE.NEXTVAL,
             P_APPGROUP_ID,
             P_APP_CODE,
             P_APPFEATURE_CODE,
             P_VIEW_ENABLED,
             P_APPEND_ENABLED,
             P_UPDATE_ENABLED,
             P_DELETE_ENABLED,
             P_EXPORT_ENABLED,
             P_ISVALID,
             SYSDATE,
             P_UPDATEBYUSER_CODE);
     COMMIT;   
     
   P_OUT_NUM := 0;
   P_OUT_MSG := 'INSERT APPGROUP FEATURE:'||P_APPFEATURE_CODE||' SUCCESS!';

   PKG_SYS_LOG.SP_LOG_ACTIVITY(
                           P_UPDATEBYUSER_CODE,
                           'SYSINFO',
                           'SUCCESS',
                           'ADD APPFEATURE',
                           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,
                             'SYSINFO',
                             'ERROR',
                             'ADD APPFEATURE',
                             P_OUT_MSG,
                             P_OUT_NUM);             
END;
                                    
PROCEDURE SP_UPDATE_APPGROUP_FEATURE(P_OUT_NUM OUT NUMBER,
                                    P_OUT_MSG OUT VARCHAR2,
                                    P_APPGROUP_ID IN NUMBER,
                                    P_APPFEATURE_CODE IN VARCHAR2,
                                    P_VIEW_ENABLED IN VARCHAR2,
                                    P_APPEND_ENABLED IN VARCHAR2,
                                    P_UPDATE_ENABLED IN VARCHAR2,
                                    P_DELETE_ENABLED IN VARCHAR2,
                                    P_EXPORT_ENABLED 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_FEATURE
   WHERE APPGROUP_ID = P_APPGROUP_ID
     AND APPFEATURE_CODE = P_APPFEATURE_CODE;
   
   IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 0;
       P_OUT_MSG := 'APPFEATURE NOT EXISTS!';

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                           P_UPDATEBYUSER_CODE,
                           'SYSINFO',
                           'SUCCESS',
                           'UPD APPFEATURE',
                           P_OUT_MSG,
                           P_OUT_NUM);
   END IF;
   
   UPDATE SYS_APPGROUP_FEATURE SET 
                   VIEW_ENABLED = P_VIEW_ENABLED,
                   APPEND_ENABLED = P_APPEND_ENABLED,
                   UPDATE_ENABLED = P_UPDATE_ENABLED,
                   DELETE_ENABLED = P_DELETE_ENABLED,
                   EXPORT_ENABLED = P_EXPORT_ENABLED,
                   ISVALID = P_ISVALID,
                   UPDATEDATE = SYSDATE,
                   UPDATEBYUSER_CODE = P_UPDATEBYUSER_CODE
   
   WHERE APPGROUP_ID = P_APPGROUP_ID
     AND APPFEATURE_CODE = P_APPFEATURE_CODE;
     
   COMMIT;
     
   P_OUT_NUM := 0;
   P_OUT_MSG := 'UPDATE APPGROUP FEATURE:'||P_APPFEATURE_CODE||' SUCCESS!';

   PKG_SYS_LOG.SP_LOG_ACTIVITY(
                           P_UPDATEBYUSER_CODE,
                           'SYSINFO',
                           'SUCCESS',
                           'UPD APPFEATURE',
                           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,
                             'SYSINFO',
                             'ERROR',
                             'UPD APPFEATURE',
                             P_OUT_MSG,
                             P_OUT_NUM);   
END;                                    
                                    
PROCEDURE SP_DELETE_APPGROUP_FEATURE(P_OUT_NUM OUT NUMBER,
                                    P_OUT_MSG OUT VARCHAR2,
                                    P_APPFEATURE_CODE IN VARCHAR2,
                                    P_UPDATEBYUSER_CODE IN VARCHAR2) IS
   V_EFFECT_ROW NUMBER := 0;
BEGIN
   SELECT COUNT(*) INTO V_EFFECT_ROW
   FROM SYS_APPGROUP_FEATURE
   WHERE APPFEATURE_CODE = P_APPFEATURE_CODE;
   
   IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 0;
       P_OUT_MSG := 'APPFEATURE NOT EXISTS!';

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                           P_UPDATEBYUSER_CODE,
                           'SYSINFO',
                           'SUCCESS',
                           'DEL APPFEATURE',
                           P_OUT_MSG,
                           P_OUT_NUM);
   END IF;
   
   DELETE FROM SYS_APPGROUP_FEATURE
   WHERE APPFEATURE_CODE =P_APPFEATURE_CODE;
   
   COMMIT;
   
   P_OUT_NUM := 0;
   P_OUT_MSG := 'DELETE APPGROUP FEATURE:'||P_APPFEATURE_CODE;

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