create or replace package body PKG_SYS_CUSTGROUP is
PROCEDURE SP_ADD_CUSTGROUP(P_OUT_NUM OUT NUMBER,
                          P_OUT_MSG OUT VARCHAR2,
                          P_CUSTGROUP_ID OUT NUMBER,
                          P_CUSTGROUP_CODE IN VARCHAR2,
                          P_CUSTGROUPDESC IN VARCHAR2,
                          P_ISVALID IN VARCHAR2,
                          P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
   INSERT INTO SYS_CUSTGROUP(
          CUSTGROUP_ID, 
          CUSTGROUP_CODE,
          CUSTGROUPDESC,
          ISVALID,
          UPDATEDATE,
          UPDATEBYUSER_CODE)
   VALUES(SQ_APPGROUP.NEXTVAL,
         P_CUSTGROUP_CODE,
         P_CUSTGROUPDESC,
         P_ISVALID,
         SYSDATE,
         P_UPDATEBYUSER_CODE);
         
   COMMIT;
   
   SELECT MAX(CUSTGROUP_ID) INTO P_CUSTGROUP_ID FROM SYS_CUSTGROUP;
         
   P_OUT_NUM := 0;
   P_OUT_MSG := 'INERT NEW CUSTGROUP SUCCESS!';

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

PROCEDURE SP_UPDATE_CUSTGROUP(P_OUT_NUM OUT NUMBER,
                            P_OUT_MSG OUT VARCHAR2,
                            P_CUSTGROUP_ID IN NUMBER,
                            P_CUSTGROUP_CODE IN VARCHAR2,
                            P_CUSTGROUPDESC 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_CUSTGROUP 
   WHERE CUSTGROUP_ID = P_CUSTGROUP_ID;
   
   IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 100;
       P_OUT_MSG := 'CUSTGROUP NOT EXISTS!';

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'CUSTGROUP',
                             'SUCCESS',
                             'UPD CUSTGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);
       RETURN;                      
   END IF;
   
   UPDATE SYS_CUSTGROUP SET
        CUSTGROUP_CODE = P_CUSTGROUP_CODE,
        CUSTGROUPDESC = P_CUSTGROUPDESC,
        ISVALID = P_ISVALID,
        UPDATEDATE = SYSDATE,
        UPDATEBYUSER_CODE = P_UPDATEBYUSER_CODE
   WHERE CUSTGROUP_ID = P_CUSTGROUP_ID;
   
   COMMIT;

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

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

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'CUSTGROUP',
                             'SUCCESS',
                             'DEL CUSTGROUP',
                             P_OUT_MSG,
                             P_OUT_NUM);
       RETURN;                      
   END IF;
   
   DELETE FROM SYS_CUSTGROUP
   WHERE CUSTGROUP_ID = P_CUSTGROUP_ID;
   
   COMMIT;
   
   P_OUT_NUM := 0;
   P_OUT_MSG := 'DELETE CUSTGROUP SUCCESS!';

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

end PKG_SYS_CUSTGROUP;
/