create or replace package body PKG_SYS_CUSTGROUP_CUST is
PROCEDURE SP_ADD_CUSTGROUP_CUST(P_OUT_NUM OUT NUMBER,
                                P_OUT_MSG OUT VARCHAR2,
                                P_CUSTGROUP_ID IN NUMBER,
                                P_CUSTOMER_ID IN NUMBER,
                                P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
    INSERT INTO SYS_CUSTGROUP_CUST(
           CUSTGROUPCUST_KEY,
           CUSTGROUP_ID,
           COMPANY_ID,
           CUSTOMER_ID,
           ISVALID,
           UPDATEDATE,
           UPDATEBYUSER_CODE)
    SELECT SQ_CUSTGROUP_CUST.NEXTVAL,
           P_CUSTGROUP_ID,
           COMPANY_ID,
           P_CUSTOMER_ID,
           'Y',
           SYSDATE,
           P_UPDATEBYUSER_CODE
    FROM SYS_CUSTOMER
    WHERE CUSTOMER_ID = P_CUSTOMER_ID;        
           
    COMMIT;
    
    P_OUT_NUM := 0;
    P_OUT_MSG := 'INSERT CUSTGROUPCUST SUCCESS!';

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

PROCEDURE SP_DELETE_CUSTGROUP_CUST(P_OUT_NUM OUT NUMBER,
                                   P_OUT_MSG OUT VARCHAR2,
                                   P_CUSTGROUP_ID IN NUMBER,
                                   P_CUSTOMER_ID IN NUMBER,
                                   P_UPDATEBYUSER_CODE IN VARCHAR2) IS
    V_EFFECT_ROW NUMBER := 0;
BEGIN
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_CUSTGROUP_CUST
    WHERE CUSTOMER_ID = P_CUSTOMER_ID
      AND CUSTGROUP_ID = P_CUSTGROUP_ID;
    
    IF V_EFFECT_ROW = 0 THEN
        P_OUT_NUM := 100;
        P_OUT_MSG := 'CUSTGROUPCUST NOT EXISTS!';

        PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'CUSTGROUPCUST',
                             'SUCCESS',
                             'DEL CUSTGROUPCUST',
                             P_OUT_MSG,
                             P_OUT_NUM);
        RETURN;                     
    END IF;
    
    DELETE FROM SYS_CUSTGROUP_CUST 
    WHERE CUSTOMER_ID = P_CUSTOMER_ID
      AND CUSTGROUP_ID = P_CUSTGROUP_ID;
    
    COMMIT;
    
    P_OUT_NUM := 0;
    P_OUT_MSG := 'DELETE CUSTGROUPCUST SUCCESS!';

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

end PKG_SYS_CUSTGROUP_CUST;
/