create or replace package body PKG_SYS_APP is
PROCEDURE SP_ADD_APP(P_OUT_NUM OUT NUMBER,
                     P_OUT_MSG OUT VARCHAR2,
                     P_OUT_APP_ID OUT NUMBER,
                     P_APP_CODE IN VARCHAR2,
                     P_APP_DESC IN VARCHAR2,
                     P_APP_VERSION IN VARCHAR2,
                     P_APP_VERSIONDATE IN VARCHAR2,
                     P_APP_APPTYPE IN VARCHAR2,
                     P_APP_APPSTATUS IN VARCHAR2,
                     P_CURRENTPATCH_ID IN NUMBER,
                     P_HOMEAPPFEATURE_ID IN NUMBER,
                     P_UPDATEBYUSER_CODE IN VARCHAR2) IS
    V_EFFECT_ROW NUMBER;                     
BEGIN
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_APP 
    WHERE APP_CODE = P_APP_CODE;
    
    IF V_EFFECT_ROW > 0 THEN
         P_OUT_NUM := 1;
         P_OUT_MSG := 'APPLICATION CODE ALREADY EXISTS: '|| P_APP_CODE ;	   

         PKG_SYS_LOG.SP_LOG_ACTIVITY(
                      P_UPDATEBYUSER_CODE,				 
                      'SYSINFO',
                      'ERROR',
                      'SYSINFO',
                      P_OUT_MSG,
                      P_OUT_NUM);
     ELSE
         INSERT INTO SYS_APP(APP_ID,
                            APP_CODE,
                            APPDESC,
                            VERSION,
                            VERSIONDATE,
                            APPTYPE_CODE,
                            CURRENTPATCH_ID,
                            HOMEAPPFEATURE_ID,
                            UPDATEDATE,
                            APPSTATUS_CODE,
                            UPDATEBYUSER_CODE)
                     VALUES((SELECT MAX(APP_ID)+1 FROM SYS_APP),
                             P_APP_CODE,
                             P_APP_DESC,
                             P_APP_VERSION,
                             TO_DATE(P_APP_VERSIONDATE,'MM/DD/YYYY'),
                             P_APP_APPTYPE,
                             P_CURRENTPATCH_ID,
                             P_HOMEAPPFEATURE_ID,
                             SYSDATE,
                             P_APP_APPSTATUS,
                             P_UPDATEBYUSER_CODE);
         COMMIT;
         
         SELECT MAX(APP_ID) INTO P_OUT_APP_ID FROM SYS_APP;
        -------------------------- Standard Return Block   - Success 
         P_OUT_NUM := 0;
         P_OUT_MSG := 'INERT NEW APPLICATION:'||P_APP_CODE;

         PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'WEB_PORTAL_ADMIN',
                             'SUCCESS',
                             'ADD_APPLICATION',
                             P_OUT_MSG,
                             P_OUT_NUM);
        --------------------------------------                               
    END IF;
    
    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,
                             'WB_PORTAL_ADMIN',
                             'ERROR',
                             'ADD_APPLICATION',
                             P_OUT_MSG,
                             P_OUT_NUM);
END;                     
                         
PROCEDURE SP_UPDATE_APP(P_OUT_NUM OUT NUMBER,
                        P_OUT_MSG OUT VARCHAR2,
                        P_APP_ID IN NUMBER,
                        P_APP_CODE IN VARCHAR2,
                        P_APP_DESC IN VARCHAR2,
                        P_APP_VERSION IN VARCHAR2,
                        P_APP_VERSIONDATE IN VARCHAR2,
                        P_APP_APPTYPE IN VARCHAR2,
                        P_APP_APPSTATUS IN VARCHAR2,
                        P_CURRENTPATCH_ID IN NUMBER,
                        P_HOMEAPPFEATURE_ID IN NUMBER,
                        P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
    UPDATE SYS_APP SET APP_CODE = P_APP_CODE,
                       APPDESC = P_APP_DESC,
                       VERSION = P_APP_VERSION,
                       VERSIONDATE = TO_DATE(P_APP_VERSIONDATE,'MM/DD/YYYY'),
                       APPTYPE_CODE = P_APP_APPTYPE,
                       APPSTATUS_CODE = P_APP_APPSTATUS,
                       CURRENTPATCH_ID = P_CURRENTPATCH_ID,
                       HOMEAPPFEATURE_ID = P_HOMEAPPFEATURE_ID,
                       UPDATEBYUSER_CODE = P_UPDATEBYUSER_CODE
    WHERE APP_ID = P_APP_ID;

    COMMIT;
    P_OUT_NUM := 0;
    P_OUT_MSG := 'UPDATE APP SUCCESS:'||P_APP_ID;

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

PROCEDURE SP_DELETE_APP(P_OUT_NUM OUT NUMBER,
                        P_OUT_MSG OUT VARCHAR2,
                        P_APP_ID IN NUMBER,
                        P_UPDATEBYUSER_CODE IN VARCHAR2) IS
    V_EFFECT_ROW NUMBER := 0;                    
BEGIN
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_APP
    WHERE APP_ID = P_APP_ID;
    
    IF V_EFFECT_ROW = 0 THEN
        P_OUT_NUM := 100;
        P_OUT_MSG := 'APP INFORMATION NOT EXISTS';
        PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'SYSINFO',
                             'ERROR',
                             'SYSINFO',
                             P_OUT_MSG,
                             P_OUT_NUM);                       
        RETURN;                        
    END IF; 
    
    DELETE FROM SYS_APP WHERE APP_ID = P_APP_ID;
    
    COMMIT;
    P_OUT_NUM := 0;
    P_OUT_MSG := 'DELETE APP SUCCESS:'||P_APP_ID;

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

END;                        
end PKG_SYS_APP;
/