create or replace package body PKG_SYS_PRODGROUP is
PROCEDURE SP_ADD_PRODGROUP(P_OUT_NUM OUT NUMBER,
                          P_OUT_MSG OUT VARCHAR2,
                          P_PRODGROUP_ID OUT NUMBER,
                          P_PRODGROUP_CODE IN VARCHAR2,
                          P_PRODGROUPDESC IN VARCHAR2,
                          P_ISVALID IN VARCHAR2,
                          P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
   INSERT INTO SYS_PRODGROUP(
          PRODGROUP_ID, 
          PRODGROUP_CODE,
          PRODGROUPDESC,
          ISVALID,
          UPDATEDATE,
          UPDATEBYUSER_CODE)
   VALUES(SQ_PRODGROUP.NEXTVAL,
         P_PRODGROUP_CODE,
         P_PRODGROUPDESC,
         P_ISVALID,
         SYSDATE,
         P_UPDATEBYUSER_CODE);
         
   COMMIT;
   
   SELECT MAX(PRODGROUP_ID) INTO P_PRODGROUP_ID FROM SYS_PRODGROUP;
         
   P_OUT_NUM := 0;
   P_OUT_MSG := 'INERT NEW PRODGROUP SUCCESS!';

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

PROCEDURE SP_UPDATE_PRODGROUP(P_OUT_NUM OUT NUMBER,
                            P_OUT_MSG OUT VARCHAR2,
                            P_PRODGROUP_ID IN NUMBER,
                            P_PRODGROUP_CODE IN VARCHAR2,
                            P_PRODGROUPDESC 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_PRODGROUP 
   WHERE PRODGROUP_ID = P_PRODGROUP_ID;
   
   IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 100;
       P_OUT_MSG := 'PRODGROUP NOT EXISTS!';

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'PRODGROUP',
                             'SUCCESS',
                             'UPD PRODGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);
      RETURN;                       
   END IF;
   
   UPDATE SYS_PRODGROUP SET
        PRODGROUP_CODE = P_PRODGROUP_CODE,
        PRODGROUPDESC = P_PRODGROUPDESC,
        ISVALID = P_ISVALID,
        UPDATEDATE = SYSDATE,
        UPDATEBYUSER_CODE = P_UPDATEBYUSER_CODE
   WHERE PRODGROUP_ID = P_PRODGROUP_ID;     
   
   COMMIT;

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

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

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'PRODGROUP',
                             'SUCCESS',
                             'DEL PRODGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);
       RETURN;                      
   END IF;
   
   DELETE FROM SYS_PRODGROUP
   WHERE PRODGROUP_ID = P_PRODGROUP_ID;
   
   COMMIT;
   
   P_OUT_NUM := 0;
   P_OUT_MSG := 'DELETE PRODGROUP SUCCESS!';

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