create or replace FUNCTION ZHU_RULE_BY_PROJECT(
    P_PROJECT_ID IN ZHU_ACCESS_CONTROL_ELEMENT.PROJECT_ID%TYPE,
    P_GROUP_ID IN VARCHAR2,
    P_NRORG IN NUMBER DEFAULT NULL
)
RETURN ZHU_RULE_BY_PROJECT_TABLE PIPELINED
IS
    OUT_RECORD ZHU_RULE_BY_PROJECT_TYPE := ZHU_RULE_BY_PROJECT_TYPE (NULL, NULL, NULL, NULL, NULL);
    V_IDX NUMBER;
    V_SQL VARCHAR2(2000);
    V_SQ1 VARCHAR2(2000);
    V_SQ2 VARCHAR2(2000);
    V_SQ3 VARCHAR2(2000);
    V_FILTER_NRORG VARCHAR2(500);
    TYPE CURSOR_REF IS REF CURSOR;
    C_ACCESS_CONTROL_ELEMENT CURSOR_REF;
    V_UNIQUE_ID ZHU_ACCESS_CONTROL_ELEMENT.UNIQUE_ID%TYPE;
    V_NAME ZHU_ACCESS_CONTROL_ELEMENT.NAME%TYPE;
    V_PROFILE_ID ZHU_ACCESS_CONTROL_RULE.GROUP_ID%TYPE;
    V_RULE ZHU_ACCESS_CONTROL_RULE.RULE%TYPE;
    V_RULE_AUX ZHU_ACCESS_CONTROL_RULE.RULE%TYPE;
    V_NAME_AUX ZHU_ACCESS_CONTROL_ELEMENT.NAME%TYPE;
    V_UNIQUE_AUX ZHU_ACCESS_CONTROL_ELEMENT.UNIQUE_ID%TYPE;
    V_RULE_UNDEFINED ZHU_ACCESS_CONTROL_RULE.RULE%TYPE;
    V_GROUP_ID_AUX VARCHAR2(2000);
    V_GROUP_ID ZHU_ACCESS_CONTROL_RULE.GROUP_ID%TYPE;
BEGIN
    V_FILTER_NRORG := '';
    IF P_NRORG IS NOT NULL THEN
        V_FILTER_NRORG := 'ORGANIZATION_ID = ' || P_NRORG || ' AND';
    END IF;
    V_SQ1 := 'SELECT ACL.UNIQUE_ID, ACL.NAME, ACL.PROFILE_ID, RULE.RULE AS RULE
                FROM (SELECT ZE.ELEMENT_ID, ZE.UNIQUE_ID, ZE.TYPE, ZE.PROJECT_ID, ZE.NAME, ZE.LABEL, ZE.PARENT_ID, ZE.IS_ACTIVE, P.ID AS PROFILE_ID
                        FROM (SELECT ID AS ELEMENT_ID, UNIQUE_ID, TYPE, PROJECT_ID, NAME, LABEL, PARENT_ID, IS_ACTIVE
                                FROM ZHU_ACCESS_CONTROL_ELEMENT
                               WHERE PROJECT_ID = '||P_PROJECT_ID||') ZE,
    (';
    V_SQ2 := '';
    V_GROUP_ID_AUX := NVL(P_GROUP_ID, '0');
    LOOP
        V_IDX := INSTR (V_GROUP_ID_AUX, ',');
        IF V_IDX > 0 THEN
            V_GROUP_ID := SUBSTR (V_GROUP_ID_AUX, 1, V_IDX - 1);
            V_SQ2 := V_SQ2 || 'SELECT '||V_GROUP_ID||' AS ID FROM DUAL UNION ALL ';
            V_GROUP_ID_AUX := SUBSTR (V_GROUP_ID_AUX, V_IDX + 1);
        ELSE
            V_GROUP_ID := V_GROUP_ID_AUX;
            V_SQ2 := V_SQ2 || 'SELECT '||V_GROUP_ID||' AS ID FROM DUAL';
            EXIT;
        END IF;
    END LOOP;
    V_SQ3 := '                     ) P) ACL,
    (SELECT ACCESS_CONTROL_ELEMENT_ID, GROUP_ID, RULE
       FROM ZHU_ACCESS_CONTROL_RULE
      WHERE '||V_FILTER_NRORG||' IS_ACTIVE = ''Y''
        AND GROUP_ID IN ('||NVL(P_GROUP_ID, '0')||')) RULE
  WHERE ACL.ELEMENT_ID = RULE.ACCESS_CONTROL_ELEMENT_ID(+)
    AND ACL.PROFILE_ID = RULE.GROUP_ID(+)
  ORDER BY ACL.UNIQUE_ID, RULE.RULE NULLS LAST';
    V_SQL := V_SQ1||V_SQ2||V_SQ3;
    V_UNIQUE_AUX := ' ';
    V_RULE_AUX := NULL;
    V_GROUP_ID_AUX := ' ';
    V_RULE_UNDEFINED := 'N';
    OPEN C_ACCESS_CONTROL_ELEMENT FOR V_SQL;
    LOOP
        FETCH C_ACCESS_CONTROL_ELEMENT INTO V_UNIQUE_ID, V_NAME, V_PROFILE_ID, V_RULE;
        EXIT WHEN C_ACCESS_CONTROL_ELEMENT%NOTFOUND;
        IF V_UNIQUE_AUX <> V_UNIQUE_ID THEN
            IF V_UNIQUE_AUX <> ' ' THEN
                IF V_GROUP_ID_AUX = ' ' THEN
                    V_RULE_UNDEFINED := 'N';
                    V_GROUP_ID_AUX := NULL;
                ELSE
                    V_RULE_UNDEFINED := 'Y';
                END IF;
                IF V_RULE_AUX IS NOT NULL THEN
                OUT_RECORD.UNIQUE_ID := V_UNIQUE_AUX;
                OUT_RECORD.NAME := V_NAME_AUX;
                OUT_RECORD.RULE := V_RULE_AUX;
                OUT_RECORD.RULE_UNDEFINED := V_RULE_UNDEFINED;
                OUT_RECORD.GROUP_ID := V_GROUP_ID_AUX;
                PIPE ROW (OUT_RECORD);
                END IF;
            END IF;
            V_UNIQUE_AUX := V_UNIQUE_ID;
            V_NAME_AUX := V_NAME;
            V_RULE_AUX := V_RULE;
            V_GROUP_ID_AUX := ' ';
            IF V_RULE IS NULL THEN
                V_GROUP_ID_AUX := TO_CHAR(V_PROFILE_ID);
            END IF;
            ELSE
                V_RULE_AUX := CASE WHEN V_RULE_AUX = 'A' THEN V_RULE_AUX
                                   WHEN V_RULE = 'A'     THEN V_RULE
                                   WHEN V_RULE_AUX = 'R' THEN V_RULE_AUX
                                   WHEN V_RULE = 'R'     THEN V_RULE
                                   WHEN V_RULE_AUX = 'I' THEN V_RULE_AUX
                                   WHEN V_RULE = 'I'     THEN V_RULE
                                   ELSE V_RULE
                               END;
            IF V_RULE IS NULL THEN
            V_GROUP_ID_AUX := CASE WHEN V_GROUP_ID_AUX = ' ' 
                                   THEN TO_CHAR(V_PROFILE_ID) 
                                   ELSE V_GROUP_ID_AUX || ',' || TO_CHAR(V_PROFILE_ID) 
                               END;
            END IF;
        END IF;
    END LOOP;
    CLOSE C_ACCESS_CONTROL_ELEMENT;
    IF V_UNIQUE_AUX <> ' ' THEN
        IF V_GROUP_ID_AUX = ' ' THEN
            V_RULE_UNDEFINED := 'N';
        ELSE
            V_RULE_UNDEFINED := 'Y';
        END IF;
        IF V_RULE_AUX IS NOT NULL THEN
            OUT_RECORD.UNIQUE_ID := V_UNIQUE_AUX;
            OUT_RECORD.NAME := V_NAME_AUX;
            OUT_RECORD.RULE := V_RULE_AUX;
            OUT_RECORD.RULE_UNDEFINED := V_RULE_UNDEFINED;
            OUT_RECORD.GROUP_ID := V_GROUP_ID_AUX;
            PIPE ROW (OUT_RECORD);
        END IF;
    END IF;
    RETURN;
END;