create or replace package body PKG_SYS_APPFEATURE is
PROCEDURE SP_ADD_APPFEATURE(P_OUT_NUM OUT NUMBER,
                            P_OUT_MSG OUT VARCHAR2,
                            P_APPFEATURE_ID OUT NUMBER,
                            P_APPFEATURE_CODE IN VARCHAR2,
                            P_APPFEATUREDESC IN VARCHAR2,
                            P_APP_CODE IN VARCHAR2,
                            P_HIERARCHYLEVEL IN NUMBER,
                            P_MENUNAME IN VARCHAR2,
                            P_ISMENUITEM IN VARCHAR2,
                            P_ISMENULINK IN VARCHAR2,
                            P_ISVALID IN VARCHAR2,
                            P_PARENTAPPFEATURE_CODE IN VARCHAR2,
                            P_PAGE_ID IN NUMBER,
                            P_STATUS_CODE IN VARCHAR2,
                            P_ORDER_SEQ IN NUMBER,
                            P_UPDATEBYUSER_CODE VARCHAR2) IS  
    V_EFFECT_ROW NUMBER := 0;                             
BEGIN
    
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_APPFEATURE
    WHERE APPFEATURE_CODE = P_APPFEATURE_CODE;
    
    IF V_EFFECT_ROW > 0 THEN
        P_OUT_NUM := 1;
        P_OUT_MSG := 'APP FEATURE ALREADY EXISTS: '|| P_APP_CODE ;	   

        PKG_SYS_LOG.SP_LOG_ACTIVITY(
                      P_UPDATEBYUSER_CODE,				 
                      'SYSINFO',
                      'ERROR',
                      'ADD APPFEATURE',
                      P_OUT_MSG,
                      P_OUT_NUM);    
        RETURN;              
    END IF; 
    
    INSERT INTO SYS_APPFEATURE(APPFEATURE_ID,
                               APPFEATURE_CODE,
                               APPFEATUREDESC,
                               APP_CODE,
                               HIERARCHYLEVEL,
                               MENUNAME,
                               ISMENUITEM,
                               ISMENULINK,
                               ISVALID,
                               PARENTAPPFEATURE_CODE,
                               PAGE_ID,
                               STATUS_CODE,
                               ORDER_SEQ,
                               UPDATEDATE,
                               UPDATEBYUSER_CODE)
                        VALUES(SQ_APPFEATURE.NEXTVAL,       
                               P_APPFEATURE_CODE,
                               P_APPFEATUREDESC,
                               P_APP_CODE,
                               P_HIERARCHYLEVEL,
                               P_MENUNAME,
                               P_ISMENUITEM,
                               P_ISMENULINK,
                               P_ISVALID,
                               P_PARENTAPPFEATURE_CODE,
                               P_PAGE_ID,
                               P_STATUS_CODE,
                               P_ORDER_SEQ,
                               SYSDATE,
                               P_UPDATEBYUSER_CODE);
                               
    COMMIT;
    
    SELECT MAX(APPFEATURE_ID) INTO P_APPFEATURE_ID FROM SYS_APPFEATURE;
    P_OUT_NUM := 0;
    P_OUT_MSG := 'INSERT APPFEATURE SUCCESS:'||P_APP_CODE;

    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_APPFEATURE(P_OUT_NUM OUT NUMBER,
                              P_OUT_MSG OUT VARCHAR2,
                              P_APPFEATURE_ID IN NUMBER,
                              P_APPFEATURE_CODE IN VARCHAR2,
                              P_APPFEATUREDESC IN VARCHAR2,
                              P_APP_CODE IN VARCHAR2,
                              P_HIERARCHYLEVEL IN NUMBER,
                              P_MENUNAME IN VARCHAR2,
                              P_ISMENUITEM IN VARCHAR2,
                              P_ISMENULINK IN VARCHAR2,
                              P_ISVALID IN VARCHAR2,
                              P_PARENTAPPFEATURE_CODE IN VARCHAR2,
                              P_PAGE_ID IN NUMBER,
                              P_STATUS_CODE IN VARCHAR2,
                              P_ORDER_SEQ IN NUMBER,
                              P_UPDATEBYUSER_CODE VARCHAR2) IS       
    V_EFFECT_ROW NUMBER := 0;                             
BEGIN
    
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_APPFEATURE
    WHERE APPFEATURE_CODE = P_APPFEATURE_CODE;
    
    IF V_EFFECT_ROW = 0 THEN
        P_OUT_NUM := 100;
        P_OUT_MSG := 'APP FEATURE NOT EXISTS: '|| P_APP_CODE ;	   

        PKG_SYS_LOG.SP_LOG_ACTIVITY(
                      P_UPDATEBYUSER_CODE,				 
                      'SYSINFO',
                      'ERROR',
                      'UPD APPFEATURE',
                      P_OUT_MSG,
                      P_OUT_NUM);    
        RETURN;              
    END IF; 
    
    UPDATE SYS_APPFEATURE SET
           APPFEATURE_CODE = P_APPFEATURE_CODE,
           APPFEATUREDESC = P_APPFEATUREDESC,
           APP_CODE = P_APP_CODE,
           HIERARCHYLEVEL =P_HIERARCHYLEVEL,
           MENUNAME = P_MENUNAME,
           ISMENUITEM = P_ISMENUITEM,
           ISMENULINK = P_ISMENULINK,
           ISVALID = P_ISVALID,
           PARENTAPPFEATURE_CODE = P_PARENTAPPFEATURE_CODE ,
           PAGE_ID = P_PAGE_ID,
           STATUS_CODE = P_STATUS_CODE,
           ORDER_SEQ = P_ORDER_SEQ,
           UPDATEDATE = SYSDATE,
           UPDATEBYUSER_CODE = P_UPDATEBYUSER_CODE
    WHERE APPFEATURE_ID = P_APPFEATURE_ID;
           
    COMMIT;
    P_OUT_NUM := 0;
    P_OUT_MSG := 'UPDATE APPFEATURE SUCCESS:'||P_APP_CODE;

    PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'SYSINFO',
                             'SUCCESS',
                             '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',
                             'APPFEATURE',
                             P_OUT_MSG,
                             P_OUT_NUM);

END;                              
                              
PROCEDURE SP_DELETE_APPFEATURE(P_OUT_NUM OUT NUMBER,
                               P_OUT_MSG OUT VARCHAR2,
                               P_APPFEATURE_ID IN NUMBER,
                               P_UPDATEBYUSER_CODE VARCHAR2) IS
    V_EFFECT_ROW NUMBER := 0;                               
BEGIN
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_APPFEATURE 
    WHERE APPFEATURE_ID = P_APPFEATURE_ID;
    
    IF V_EFFECT_ROW = 0 THEN
        P_OUT_NUM := 100;
        P_OUT_MSG := 'APPFEATURE NOT EXISTS!';
        PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'SYSINFO',
                             'ERROR',
                             'DEL APPFEATURE',
                             P_OUT_MSG,
                             P_OUT_NUM);
    END IF;
    
    DELETE FROM SYS_APPFEATURE WHERE APPFEATURE_ID = P_APPFEATURE_ID;
    
    COMMIT;

    P_OUT_NUM := 100;
    P_OUT_MSG := 'DELETE APPFEATURE SUCCESS!';
    PKG_SYS_LOG.SP_LOG_ACTIVITY(
                          P_UPDATEBYUSER_CODE,
                          'SYSINFO',
                          'ERROR',
                          '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_APPFEATURE;
/