create or replace package body PKG_SYS_PAGE is
PROCEDURE SP_ADD_PAGE(P_OUT_NUM OUT NUMBER,
                        P_OUT_MSG OUT VARCHAR2,
                        P_PAGE_ID OUT NUMBER,
                        P_PAGE_CODE IN VARCHAR2,
                        P_APP_CODE IN VARCHAR2,
                        P_PAGETYPE_CODE IN VARCHAR2,
                        P_PAGEDESC IN VARCHAR2,
                        P_SPECNUM IN VARCHAR2,
                        P_PAGE_FILE IN VARCHAR2,
                        P_ISVALID IN VARCHAR2,
                        P_PAGEPATH IN VARCHAR2,
                        P_PARENTPAGE_ID IN NUMBER,
                        P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
    INSERT INTO SYS_PAGE(PAGE_ID,
                         PAGE_CODE,
                         APP_CODE,
                         PAGETYPE_CODE,
                         PAGEDESC,
                         SPECNUM,
                         PAGE_FILE,
                         ISVALID,
                         PAGEPATH,
                         UPDATEDATE,
                         UPDATEBYUSER_CODE)
                  VALUES(SQ_PAGE.NEXTVAL,       
                         P_PAGE_CODE,
                         P_APP_CODE,
                         P_PAGETYPE_CODE,
                         P_PAGEDESC,
                         P_SPECNUM,
                         P_PAGE_FILE,
                         P_ISVALID,
                         P_PAGEPATH,
                         SYSDATE,
                         P_UPDATEBYUSER_CODE);
    COMMIT;

    SELECT MAX(PAGE_ID) INTO P_PAGE_ID
    FROM SYS_PAGE;

    IF P_PAGETYPE_CODE != 'MAIN' THEN
       UPDATE SYS_PAGE SET  PARENTPAGE_ID = P_PARENTPAGE_ID
       WHERE PAGE_ID = P_PAGE_ID;       
       
       COMMIT;
    END IF;
                         
    P_OUT_NUM := 0;
    P_OUT_MSG := 'INSERT PAGE SUCCESS!';

    PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'PAGE',
                             'SUCCESS',
                             'INS PAGE',
                             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,
                             'PAGE',
                             'ERROR',
                             'INS PAGE',
                             P_OUT_MSG,     
                             P_OUT_NUM);    
    
END;                        
                        
PROCEDURE SP_UPDATE_PAGE(P_OUT_NUM OUT NUMBER,
                        P_OUT_MSG OUT VARCHAR2,
                        P_PAGE_ID IN NUMBER,
                        P_PAGE_CODE IN VARCHAR2,
                        P_APP_CODE IN VARCHAR2,
                        P_PAGETYPE_CODE IN VARCHAR2,
                        P_PAGEDESC IN VARCHAR2,
                        P_SPECNUM IN VARCHAR2,
                        P_PAGE_FILE IN VARCHAR2,
                        P_ISVALID IN VARCHAR2,
                        P_PAGEPATH IN VARCHAR2,
                        P_PARENTPAGE_ID IN NUMBER,
                        P_UPDATEBYUSER_CODE IN VARCHAR2) IS
    V_EFFECT_ROW NUMBER := 0;                    
BEGIN
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_PAGE
    WHERE PAGE_ID = P_PAGE_ID;
    
    IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 100;
       P_OUT_MSG := 'PAGE NOT EXISTS!';

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'PAGE',
                             'SUCCESS',
                             'UPD PAGE',
                             P_OUT_MSG,
                             P_OUT_NUM);
    END IF;
    
    UPDATE SYS_PAGE SET
           PAGE_CODE = P_PAGE_CODE,
           APP_CODE = P_APP_CODE,
           PAGETYPE_CODE = P_PAGETYPE_CODE,
           PAGEDESC = P_PAGEDESC,
           SPECNUM = P_SPECNUM,
           ISVALID = P_ISVALID,
           PAGEPATH = P_PAGEPATH,
           UPDATEDATE = SYSDATE,
           PARENTPAGE_ID = (CASE WHEN P_PAGETYPE_CODE = 'MAIN' THEN P_PAGE_ID ELSE P_PARENTPAGE_ID END),
           UPDATEBYUSER_CODE = P_UPDATEBYUSER_CODE
    WHERE PAGE_ID = P_PAGE_ID;
    
    COMMIT;
    
    P_OUT_NUM := 0;
    P_OUT_MSG := 'UPDATE PAGE SUCCESS!';

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

PROCEDURE SP_DELETE_PAGE(P_OUT_NUM OUT NUMBER,
                        P_OUT_MSG OUT VARCHAR2,
                        P_PAGE_ID IN NUMBER,
                        P_UPDATEBYUSER_CODE IN VARCHAR2) IS
    V_EFFECT_ROW NUMBER := 0;                    
BEGIN
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_PAGE
    WHERE PAGE_ID = P_PAGE_ID;
    
    IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 100;
       P_OUT_MSG := 'PAGE NOT EXISTS!';

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'PAGE',
                             'SUCCESS',
                             'DEL PAGE',
                             P_OUT_MSG,
                             P_OUT_NUM);
    END IF;    
    
    DELETE FROM SYS_PAGE WHERE PAGE_ID = P_PAGE_ID;
    
    COMMIT;
    
    P_OUT_NUM := 0;
    P_OUT_MSG := 'DEL PAGE SUCCESS!';

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