create or replace package body PKG_SYS_PRODGROUP_PROD is
PROCEDURE SP_ADD_PRODGROUP_PROD(P_OUT_NUM OUT NUMBER,
                                P_OUT_MSG OUT VARCHAR,
                                P_PRODGROUP_ID IN NUMBER,
                                P_PRODUCT_TYPE IN VARCHAR2,
                                P_UPDATEBYUSER_CODE IN VARCHAR2) IS
BEGIN
    INSERT INTO SYS_PRODGROUP_PRODTYPE(
            PRODGROUPPROD_KEY,
            PRODGROUP_ID,
            COMPANY_ID,
            PRODUCT_TYPE,
            ISVALID,
            UPDATEDATE,
            UPDATEBYUSER_CODE)
     SELECT SQ_PRODGROUP_PROD.NEXTVAL,
            P_PRODGROUP_ID,
            COMPANY_ID,
            P_PRODUCT_TYPE,
            'Y',
            SYSDATE,
            P_UPDATEBYUSER_CODE
     FROM SYS_PRODUCT
     WHERE PRODUCT_TYPE = P_PRODUCT_TYPE;
            
         
    COMMIT;
     
    P_OUT_NUM := 0;
    P_OUT_MSG := 'INSERT PRODGROUPPROD SUCCESS!';

    PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'PRODGROUPPROD',
                             'SUCCESS',
                             'INS PRODGROUPPROD',
                             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,
                             'PRODGROUPPROD',
                             'ERROR',
                             'INS PRODGROUPPROD',
                             P_OUT_MSG,
                             P_OUT_NUM);          
     
            
END;
                                  
PROCEDURE SP_DELETE_PRODGROUP_PROD(P_OUT_NUM OUT NUMBER,
                                  P_OUT_MSG OUT VARCHAR,
                                  P_PRODGROUP_ID IN NUMBER,
                                  P_PRODUCT_TYPE IN VARCHAR2,
                                  P_UPDATEBYUSER_CODE IN VARCHAR2) IS
    V_EFFECT_ROW NUMBER := 0;                              
BEGIN
    SELECT COUNT(*) INTO V_EFFECT_ROW
    FROM SYS_PRODGROUP_PRODTYPE
    WHERE PRODGROUP_ID = P_PRODGROUP_ID
      AND PRODUCT_TYPE = P_PRODUCT_TYPE;
    
    IF V_EFFECT_ROW = 0 THEN
       P_OUT_NUM := 100;
       P_OUT_MSG := 'PRODGROUPPROD NOT EXISTS!';

       PKG_SYS_LOG.SP_LOG_ACTIVITY(
                             P_UPDATEBYUSER_CODE,
                             'PRODGROUPPROD',
                             'SUCCESS',
                             'DEL PRODGROUPPROD',
                             P_OUT_MSG,
                             P_OUT_NUM);
        RETURN;                     
    END IF;
    
    DELETE FROM SYS_PRODGROUP_PRODTYPE
    WHERE PRODGROUP_ID = P_PRODGROUP_ID
      AND PRODUCT_TYPE = P_PRODUCT_TYPE;
     
    COMMIT;

    P_OUT_NUM := 0;
    P_OUT_MSG := 'DELETE PRODGROUPPROD SUCCESS!';

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