DROP TYPE QRY_PROD_FILIAL_OL_TABLE
/

DROP TYPE QRY_PROD_FILIAL_OL_TYPE
/

CREATE TYPE QRY_PROD_FILIAL_OL_TYPE AS OBJECT
  (NRSEQPARPROD   VARCHAR2(20),
   CDFILIOPELOG   VARCHAR2(4),
   NMFILIALOL     VARCHAR2(30),
   CDFILIAL       VARCHAR2(4),
   NMFILIAL       VARCHAR2(30),
   CDPRODUTO      VARCHAR2(10),
   CDARVPROD      VARCHAR2(20),
   NMPRODUTO      VARCHAR2(50),
   SGUNIDADE      VARCHAR2(10),
   CDGRUPPROD     VARCHAR2(10),
   CDSUBGRPROD    VARCHAR2(10),
   CDFILISUBGR    VARCHAR2(1),
   NMSUBGRPROD    VARCHAR2(40),
   NRORG          NUMBER
  )
/

CREATE TYPE QRY_PROD_FILIAL_OL_TABLE AS TABLE OF QRY_PROD_FILIAL_OL_TYPE
/

CREATE OR REPLACE FUNCTION QRY_PROD_FILIAL_OL
                          (P_CDFILIAL          IN VARCHAR2,
                           P_TCDFILIAL         IN VARCHAR2,
                           P_CDFILIOL          IN VARCHAR2,
                           P_TCDFILIOL         IN VARCHAR2,
                           P_CDFILIOPELOG      IN VARCHAR2,
                           P_CDPRODINI         IN VARCHAR2,
                           P_CDPRODFIN         IN VARCHAR2,
                           P_SUBGRUPO          IN VARCHAR2,
                           P_CDGRUPPROD        IN VARCHAR2,
                           P_CDSUBGRARVOR      IN VARCHAR2,
                           P_IDITEMSEMPARAM    IN VARCHAR2,
                           P_IDUTLSUBGRPRODFIL IN VARCHAR2)
                           RETURN QRY_PROD_FILIAL_OL_TABLE PIPELINED IS
   OUT_RECORD QRY_PROD_FILIAL_OL_TYPE := QRY_PROD_FILIAL_OL_TYPE (NULL, NULL, NULL, NULL,
                                                                  NULL, NULL, NULL, NULL,
                                                                  NULL, NULL, NULL, NULL,
                                                                  NULL,0);
   TYPE CURTYPE             IS REF CURSOR;
   C_PARAMPRODSUP           CURTYPE;
   V_CDFILIOPELOG           FILIAL.CDFILIAL%TYPE;
   V_CDPRODUTO              PRODUTO.CDPRODUTO%TYPE;
   V_CDARVPROD              PRODUTO.CDARVPROD%TYPE;
   V_NMPRODUTO              PRODUTO.NMPRODUTO%TYPE;
   V_SGUNIDADE              PRODUTO.SGUNIDADE%TYPE;
   V_CDNVPRODUTO            PRODUTO.CDNVPRODUTO%TYPE;
   V_COUNT_PARAM            NUMBER;
   V_COUNT_PARAM1           NUMBER;
   V_NRSEQPARPROD           PARAMPRODSUP.NRSEQPARPROD%TYPE;
BEGIN
   SELECT MAX(CDNVPRODUTO)
     INTO V_CDNVPRODUTO
     FROM PRODUTO
    WHERE NVL(CDNVPRODUTO, 'W') <> 'W'
      AND PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN;
   FOR C_FILIALOL IN
         (SELECT CDFILIAL CDFILIOPELOG, NMFILIAL  NMFILIALOL
            FROM FILIAL
           WHERE NVL(FILIAL.IDCNTRRESC, 'N') <> 'S'
             AND NVL(FILIAL.IDFUNCAOFILI, 'T') = 'O'
             AND ((INSTR(P_CDFILIOL, CDFILIAL) > 0) OR (P_TCDFILIOL = 'T')))
   LOOP
      FOR C_SUBGRUPPROD IN (SELECT SUBGRUPPROD.CDGRUPPROD, CDSUBGRPROD, NMSUBGRPROD, CASE
                                                                            WHEN P_IDUTLSUBGRPRODFIL = 'S'
                                                                            THEN P_CDFILIOPELOG
                                                                            ELSE 'P'
                                                                         END CDFILISUBGR
                              FROM SUBGRUPPROD
                             WHERE CDGRUPPROD  =  P_CDGRUPPROD
                               AND CDSUBGRPROD <> P_CDSUBGRARVOR
                               AND ((INSTR(P_SUBGRUPO, CDSUBGRPROD) > 0) OR (P_SUBGRUPO = 'N')))
      LOOP
         FOR C_FILDEST IN
            (SELECT CDFILIAL, NMFILIAL
               FROM FILIAL
              WHERE NVL(FILIAL.IDCNTRRESC, 'N') <> 'S'
                AND NVL(FILIAL.IDFUNCAOFILI, 'T') <> 'O'
                AND ((INSTR(P_CDFILIAL, CDFILIAL) > 0) OR (P_TCDFILIAL = 'T'))
             )
         LOOP
            IF (P_IDITEMSEMPARAM = 'N') THEN
               SELECT COUNT(*)
                 INTO V_COUNT_PARAM
                 FROM PARAMPRODSUP
                WHERE PARAMPRODSUP.CDFILIAL = C_FILDEST.CDFILIAL;
            ELSIF (P_IDITEMSEMPARAM = 'S') THEN
               SELECT COUNT(*)
                 INTO V_COUNT_PARAM1
                 FROM PARAMPRODSUP
                WHERE PARAMPRODSUP.CDFILIOPELOG = C_FILIALOL.CDFILIOPELOG
                  AND PARAMPRODSUP.CDFILIAL = C_FILDEST.CDFILIAL;
            ELSE
               V_COUNT_PARAM  := 0;
               V_COUNT_PARAM1 := 0;
               SELECT COUNT(*)
                 INTO V_COUNT_PARAM1
                 FROM PARAMPRODSUP
                WHERE PARAMPRODSUP.CDFILIOPELOG = C_FILIALOL.CDFILIOPELOG
                  AND PARAMPRODSUP.CDFILIAL     = C_FILDEST.CDFILIAL;
               IF V_COUNT_PARAM1 = 0 THEN
                  SELECT COUNT(*)
                    INTO V_COUNT_PARAM
                    FROM PARAMPRODSUP
                   WHERE PARAMPRODSUP.CDFILIAL = C_FILDEST.CDFILIAL;
               END IF;
            END IF;
            IF P_IDITEMSEMPARAM = 'S' THEN
               IF V_COUNT_PARAM1 > 0 THEN
                  OPEN C_PARAMPRODSUP FOR
                     SELECT 'S' CDFILIOPELOG, PRODUTO.CDPRODUTO, PRODUTO.CDARVPROD,
                            PRODUTO.NMPRODUTO, PRODUTO.SGUNIDADE, MAX(PARAMPRODSUP.NRSEQPARPROD) NRSEQPARPROD
                       FROM PARAMPRODSUP, PRODUTO
                      WHERE PARAMPRODSUP.CDFILIOPELOG = C_FILIALOL.CDFILIOPELOG
                        AND PARAMPRODSUP.CDFILIAL     = C_FILDEST.CDFILIAL
                        AND PARAMPRODSUP.CDGRUPPROD   = C_SUBGRUPPROD.CDSUBGRPROD
                        AND PARAMPRODSUP.CDSUBGRPROD  = C_SUBGRUPPROD.CDSUBGRPROD
                        AND PARAMPRODSUP.CDSUBGRPROD  <> P_CDSUBGRARVOR
                        AND PARAMPRODSUP.CDPRODUTO    = PRODUTO.CDPRODUTO
                        AND PARAMPRODSUP.CDFILISUBGR  = C_SUBGRUPPROD.CDFILISUBGR
                        AND PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN
                      GROUP BY PRODUTO.CDPRODUTO, PRODUTO.CDARVPROD,
                            PRODUTO.NMPRODUTO, PRODUTO.SGUNIDADE;
               END IF;
            ELSIF (P_IDITEMSEMPARAM = 'N') THEN
               IF V_COUNT_PARAM = 0 THEN
                  OPEN C_PARAMPRODSUP FOR
                     SELECT 'N' CDFILIOPELOG, PRODUTO.CDPRODUTO, PRODUTO.CDARVPROD,
                            PRODUTO.NMPRODUTO, PRODUTO.SGUNIDADE, '' NRSEQPARPROD
                       FROM PRSUBGRPROD, PRODUTO
                      WHERE PRSUBGRPROD.CDPRODUTO    = PRODUTO.CDPRODUTO
                        AND PRSUBGRPROD.CDGRUPPROD   = C_SUBGRUPPROD.CDGRUPPROD
                        AND PRSUBGRPROD.CDSUBGRPROD  = C_SUBGRUPPROD.CDSUBGRPROD
                        AND PRSUBGRPROD.CDSUBGRPROD  <> P_CDSUBGRARVOR
                        AND PRSUBGRPROD.CDFILISUBGR  = C_SUBGRUPPROD.CDFILISUBGR
                        AND PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN
                        AND PRODUTO.CDNVPRODUTO      = V_CDNVPRODUTO;
               END IF;
            ELSE
               IF ((V_COUNT_PARAM = 0) OR (V_COUNT_PARAM1 > 0)) THEN
                  OPEN C_PARAMPRODSUP FOR
                     SELECT MAX(CDFILIOPELOG), CDPRODUTO, CDARVPROD, NMPRODUTO, SGUNIDADE, MAX(NRSEQPARPROD)
                       FROM (SELECT 'S' CDFILIOPELOG, PRODUTO.CDPRODUTO, PRODUTO.CDARVPROD,
                                    PRODUTO.NMPRODUTO, PRODUTO.SGUNIDADE, PARAMPRODSUP.NRSEQPARPROD
                               FROM PARAMPRODSUP, PRODUTO
                              WHERE PARAMPRODSUP.CDFILIOPELOG = C_FILIALOL.CDFILIOPELOG
                                AND PARAMPRODSUP.CDFILIAL     = C_FILDEST.CDFILIAL
                                AND PARAMPRODSUP.CDGRUPPROD   = C_SUBGRUPPROD.CDGRUPPROD
                                AND PARAMPRODSUP.CDSUBGRPROD  = C_SUBGRUPPROD.CDSUBGRPROD
                                AND PARAMPRODSUP.CDSUBGRPROD  <> P_CDSUBGRARVOR
                                AND PARAMPRODSUP.CDPRODUTO    = PRODUTO.CDPRODUTO
                                AND PARAMPRODSUP.CDFILISUBGR  = C_SUBGRUPPROD.CDFILISUBGR
                                AND PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN
                              UNION
                             SELECT 'N' CDFILIOPELOG, PRODUTO.CDPRODUTO, PRODUTO.CDARVPROD,
                                    PRODUTO.NMPRODUTO, PRODUTO.SGUNIDADE, '' NRSEQPARPROD
                               FROM PRSUBGRPROD, PRODUTO
                              WHERE PRSUBGRPROD.CDPRODUTO    = PRODUTO.CDPRODUTO
                                AND PRSUBGRPROD.CDGRUPPROD   = C_SUBGRUPPROD.CDGRUPPROD
                                AND PRSUBGRPROD.CDSUBGRPROD  = C_SUBGRUPPROD.CDSUBGRPROD
                                AND PRSUBGRPROD.CDSUBGRPROD  <> P_CDSUBGRARVOR
                                AND PRSUBGRPROD.CDFILISUBGR  = C_SUBGRUPPROD.CDFILISUBGR
                                AND PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN
                                AND PRODUTO.CDNVPRODUTO      = V_CDNVPRODUTO)
                              GROUP BY CDPRODUTO, CDARVPROD, NMPRODUTO, SGUNIDADE;
               ELSE
                  OPEN C_PARAMPRODSUP FOR
                     SELECT '' CDFILIOPELOG, '' CDPRODUTO, '' CDARVPROD, '' NMPRODUTO, '' SGUNIDADE, '' NRSEQPARPROD
                       FROM DUAL;
               END IF;
            END IF;
            LOOP
            FETCH C_PARAMPRODSUP INTO V_CDFILIOPELOG, V_CDPRODUTO, V_CDARVPROD, V_NMPRODUTO, V_SGUNIDADE, V_NRSEQPARPROD;
            EXIT WHEN C_PARAMPRODSUP%NOTFOUND;
               IF V_CDFILIOPELOG = 'S' THEN
                  OUT_RECORD.CDFILIOPELOG  := C_FILIALOL.CDFILIOPELOG;
                  OUT_RECORD.NMFILIALOL    := C_FILIALOL.NMFILIALOL;
               ELSE
                  OUT_RECORD.CDFILIOPELOG  := '';
                  OUT_RECORD.NMFILIALOL    := '';
               END IF;
               OUT_RECORD.NRSEQPARPROD  := V_NRSEQPARPROD;
               OUT_RECORD.CDFILIAL      := C_FILDEST.CDFILIAL;
               OUT_RECORD.NMFILIAL      := C_FILDEST.NMFILIAL;
               OUT_RECORD.CDGRUPPROD    := C_SUBGRUPPROD.CDGRUPPROD;
               OUT_RECORD.CDSUBGRPROD   := C_SUBGRUPPROD.CDSUBGRPROD;
               OUT_RECORD.NMSUBGRPROD   := C_SUBGRUPPROD.NMSUBGRPROD;
               OUT_RECORD.CDFILISUBGR   := C_SUBGRUPPROD.CDFILISUBGR;
               OUT_RECORD.CDPRODUTO     := V_CDPRODUTO;
               OUT_RECORD.CDARVPROD     := V_CDARVPROD;
               OUT_RECORD.NMPRODUTO     := V_NMPRODUTO;
               OUT_RECORD.SGUNIDADE     := V_SGUNIDADE;
               PIPE ROW (OUT_RECORD);
            END LOOP;
         END LOOP;
      END LOOP;
   END LOOP;
   RETURN;
END;
/