DROP TYPE PED_COLE_TABLE
/

DROP TYPE PED_COLE_TYPE
/

CREATE TYPE PED_COLE_TYPE AS
OBJECT( CDPRODCOMPL     VARCHAR2(10),
        CDFORNECED      VARCHAR2(21))
/

CREATE TYPE PED_COLE_TABLE AS TABLE OF PED_COLE_TYPE
/

CREATE OR REPLACE FUNCTION PED_COLE(P_CDFORNECED       IN VARCHAR2,
                                    P_T_CDFORNECED     IN VARCHAR2,
                                    P_CDPRODINI        IN VARCHAR2,
                                    P_CDPRODFIN        IN VARCHAR2,
                                    P_TPRODUTO         IN VARCHAR2,
                                    P_CDCOMPRADOR      IN VARCHAR2,
                                    P_T_CDCOMPRADOR    IN VARCHAR2,
                                    P_PORTAL           IN VARCHAR2 DEFAULT 'S') RETURN PED_COLE_TABLE PIPELINED
IS
  TYPE CURTYPE             IS REF CURSOR;
  C_ITENS                  CURTYPE;
  V_ITENS_CDPRODUTO        PRODUTO.CDPRODUTO%TYPE;
  V_ITENS_CDFORNECED       FORNECEDOR.CDFORNECED%TYPE;
  OUT_RECORD PED_COLE_TYPE := PED_COLE_TYPE (NULL, NULL);
BEGIN
   IF P_T_CDCOMPRADOR = 'T' THEN
      IF P_PORTAL = 'S' THEN
         OPEN C_ITENS FOR
            SELECT PRODUTO.CDPRODUTO, PRODFORNCOMP.CDFORNECED
              FROM PRODFORNCOMP, PRODUTO
             WHERE PRODFORNCOMP.CDPRODCOMPL = PRODUTO.CDPRODUTO
               AND (INSTR(P_CDFORNECED, PRODFORNCOMP.CDFORNECED) <> 0 OR (P_T_CDFORNECED = 'T'))
               AND ((PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN) OR (P_TPRODUTO = 'T'))
               AND EXISTS (SELECT /* + FIRST_ROWS(1) */
                                  1
                             FROM PRODCOMP, PRODUTO PR
                            WHERE PRODCOMP.CDPRODUTO = PR.CDPRODUTO
                              AND PRODUTO.CDARVPROD LIKE PR.CDARVPROD || '%')
               AND EXISTS (SELECT /* + FIRST_ROWS(1) */
                                  1
                             FROM DADOSFORNECOMMERCE
                            WHERE PRODFORNCOMP.CDFORNECED = DADOSFORNECOMMERCE.CDFORNECED );
      ELSE
         OPEN C_ITENS FOR
            SELECT PRODUTO.CDPRODUTO, PRODFORNCOMP.CDFORNECED
              FROM PRODFORNCOMP, PRODUTO
             WHERE PRODFORNCOMP.CDPRODCOMPL = PRODUTO.CDPRODUTO
               AND (INSTR(P_CDFORNECED, PRODFORNCOMP.CDFORNECED) <> 0 OR (P_T_CDFORNECED = 'T'))
               AND ((PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN) OR (P_TPRODUTO = 'T'))
               AND EXISTS (SELECT /* + FIRST_ROWS(1) */
                                  1
                             FROM PRODCOMP, PRODUTO PR
                            WHERE PRODCOMP.CDPRODUTO = PR.CDPRODUTO
                              AND PRODUTO.CDARVPROD LIKE PR.CDARVPROD || '%');
      END IF;
   ELSE
      IF P_PORTAL = 'S' THEN
         OPEN C_ITENS FOR
            SELECT PRODUTO.CDPRODUTO, PRODFORNCOMP.CDFORNECED
              FROM PRODFORNCOMP, PRODUTO
             WHERE PRODFORNCOMP.CDPRODCOMPL = PRODUTO.CDPRODUTO
               AND (INSTR(P_CDFORNECED, PRODFORNCOMP.CDFORNECED) <> 0 OR (P_T_CDFORNECED = 'T'))
               AND ((PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN) OR (P_TPRODUTO = 'T'))
               AND EXISTS (SELECT /* + FIRST_ROWS(1) */
                                  1
                             FROM PRODCOMP, PRODUTO PR
                            WHERE PRODCOMP.CDPRODUTO = PR.CDPRODUTO
                              AND PRODUTO.CDARVPROD LIKE PR.CDARVPROD || '%'
                              AND INSTR(P_CDCOMPRADOR, PRODCOMP.CDOPERADOR) <> 0)
               AND EXISTS (SELECT /* + FIRST_ROWS(1) */
                                  1
                             FROM DADOSFORNECOMMERCE
                            WHERE PRODFORNCOMP.CDFORNECED = DADOSFORNECOMMERCE.CDFORNECED );
      ELSE
         OPEN C_ITENS FOR
            SELECT PRODUTO.CDPRODUTO, PRODFORNCOMP.CDFORNECED
              FROM PRODFORNCOMP, PRODUTO
             WHERE PRODFORNCOMP.CDPRODCOMPL = PRODUTO.CDPRODUTO
               AND (INSTR(P_CDFORNECED, PRODFORNCOMP.CDFORNECED) <> 0 OR (P_T_CDFORNECED = 'T'))
               AND ((PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN) OR (P_TPRODUTO = 'T'))
               AND EXISTS (SELECT /* + FIRST_ROWS(1) */
                                  1
                             FROM PRODCOMP, PRODUTO PR
                            WHERE PRODCOMP.CDPRODUTO = PR.CDPRODUTO
                              AND PRODUTO.CDARVPROD LIKE PR.CDARVPROD || '%'
                              AND INSTR(P_CDCOMPRADOR, PRODCOMP.CDOPERADOR) <> 0);
      END IF;
   END IF;
   LOOP
   FETCH C_ITENS INTO V_ITENS_CDPRODUTO, V_ITENS_CDFORNECED;
   EXIT WHEN C_ITENS%NOTFOUND;
      OUT_RECORD.CDPRODCOMPL     := V_ITENS_CDPRODUTO;
      OUT_RECORD.CDFORNECED      := V_ITENS_CDFORNECED;
      PIPE ROW (OUT_RECORD);
   END LOOP;
   CLOSE C_ITENS;
END;
/