/* equInContainer is a function that returns equipment contained in a container */

FUNCTION equInContainer (verSet IN NUMBER, contSids IN numberTable, contVers IN numberTable, contOtdTypes IN stringTable, resOtdTypes IN stringTable, contOtds IN numberTable, resOtds IN numberTable, useOtdSid IN NUMBER)
    RETURN stringTable pipelined IS

    objInfo objInfoType;

    CURSOR c1(contSid IN NUMBER,contVer IN NUMBER) IS
    SELECT
        a.equ_otdtype, a.equ_sid, a.equ_ver, a.equ_otd
    FROM
        (
        SELECT
            *
        FROM
            repl_equ_equ_eqt a1
        WHERE (
                verSet = 2 AND a1.equ_ver IN (SELECT * FROM TABLE(PLANNEDVERSET))
                OR verSet = 3 AND a1.equ_ver IN (SELECT * FROM TABLE(LTPLANNEDVERSET))
                OR verSet = 1 AND a1.equ_ver IN (SELECT * FROM TABLE(EXISTVERSET))
            ) AND (
                contOtdTypes IS NULL AND resOtdTypes IS NULL
                OR contOtdTypes IS NOT NULL AND a1.equ_otdtype IN (SELECT * FROM TABLE(contOtdTypes))
                OR resOtdTypes IS NOT NULL AND a1.equ_otdtype IN (SELECT * FROM TABLE(resOtdTypes))
            )
        ) a
    START WITH
        a.cont_sid = contSid AND a.cont_ver = contVer
    CONNECT BY
        a.cont_sid = PRIOR a.equ_sid AND a.cont_ver = PRIOR a.equ_ver;

    CURSOR c2(contSid IN NUMBER,contVer IN NUMBER) IS
    SELECT
        a.equ_otdtype, a.equ_sid, a.equ_ver, a.equ_otd
    FROM
        (
        SELECT
            *
        FROM
            repl_equ_equ_eqt a1
        WHERE (
                verSet = 2 AND a1.equ_ver IN (SELECT * FROM TABLE(PLANNEDVERSET))
                OR verSet = 3 AND a1.equ_ver IN (SELECT * FROM TABLE(LTPLANNEDVERSET))
                OR verSet = 1 AND a1.equ_ver IN (SELECT * FROM TABLE(EXISTVERSET))
            ) AND (
                contOtds IS NULL AND resOtds IS NULL
                OR contOtds IS NOT NULL AND a1.equ_otd IN (SELECT * FROM TABLE(contOtds))
                OR resOtds IS NOT NULL AND a1.equ_otd IN (SELECT * FROM TABLE(resOtds))
            )
        ) a
    START WITH
        a.cont_sid = contSid AND a.cont_ver = contVer
    CONNECT BY
        a.cont_sid = PRIOR a.equ_sid AND a.cont_ver = PRIOR a.equ_ver;

BEGIN
    FOR j IN 1..contSids.COUNT LOOP
        IF (useOtdSid = 0) THEN
            -- qualify on combination of otd and sub-type
            OPEN c1(contSids(j),contVers(j));
            LOOP
                FETCH c1 INTO objInfo;
                EXIT WHEN
                    c1%notfound;

                IF (contOtdTypes IS NULL AND resOtdTypes IS NULL) THEN
                    pipe ROW(TO_CHAR(objInfo.sid) || ' ' || TO_CHAR(objInfo.ver) || ' ' || TO_CHAR(objInfo.otd));
                ELSIF (resOtdTypes IS NOT NULL) THEN
                    FOR i IN 1..resOtdTypes.COUNT LOOP
                        IF (resOtdTypes(i) = objInfo.otdtype) THEN
                            pipe ROW(TO_CHAR(objInfo.sid) || ' ' || TO_CHAR(objInfo.ver) || ' ' || TO_CHAR(objInfo.otd));
                            EXIT;
                        END IF;
                    END LOOP;
                END IF;
            END LOOP;
            CLOSE c1;
        ELSE
            -- qualify on otd only
            OPEN c2(contSids(j),contVers(j));
            LOOP
                FETCH c2 INTO objInfo;
                EXIT WHEN
                    c2%notfound;

                IF (contOtds IS NULL AND resOtds IS NULL) THEN
                    pipe ROW(TO_CHAR(objInfo.sid) || ' ' || TO_CHAR(objInfo.ver) || ' ' || TO_CHAR(objInfo.otd));
                ELSIF (resOtds IS NOT NULL) THEN
                    FOR i IN 1..resOtds.COUNT LOOP
                        IF (resOtds(i) = objInfo.otd) THEN
                            pipe ROW(TO_CHAR(objInfo.sid) || ' ' || TO_CHAR(objInfo.ver) || ' ' || TO_CHAR(objInfo.otd));
                            EXIT;
                        END IF;
                    END LOOP;
                END IF;
            END LOOP;
            CLOSE c2;
        END IF;
    END LOOP;

    RETURN;
EXCEPTION
  WHEN OTHERS THEN
    RETURN;
END;