/*
  Nome: 9985_CMPFUNO_SQL_PESQ_PROD_FORN.SQL

  Descricao: Busca os produtos que serao utilizados.

  Verifica:
    1. Produto com qualificação por fornecedor
    2. Produto por fornecedor

  Retorno: Preenche a tabela com os produtos. Caso nao encontre os produtos
  retorna as seguintes mensagens:
  NAO_HA_PROD_QUALIF_FORN: "Nao ha produto com qualificacao por fornecedor."
  NAO_HA_PROD_FORN: "Nao ha produto por fornecedor."
  ERRO_SQL_PESQUISA_PRODUTO: "Erro ao executar a funcao SQL_PESQUISA_PRODUTO"

  Parâmetros: 
  P_CDFILIAL: Filial utilizada na pesquisa
  P_PRODNVCOMPL: Nivel completo 'S' / Nivel incompleto 'N'
  P_CDFILCENTCMP: Codigo  da filial central de compras
  P_CDFORNECED: Codigo do fornecedor
  P_DTAUTOFORN: Data de lancamento
*/

DROP TYPE SQL_PESQ_PROD_FORN_TABLE
/

DROP TYPE SQL_PESQ_PROD_FORN_TYPE
/

CREATE TYPE SQL_PESQ_PROD_FORN_TYPE AS
OBJECT( CDARVPROD     VARCHAR2(20),
        CDPRODUTO     VARCHAR2(10),
        CDPRODESTO    VARCHAR2(10),
        SGUNIDADE     VARCHAR2(10),
        NMPRODUTO     VARCHAR2(50),
        VRFATOCONV    FLOAT,
        CDNVPRODUTO   VARCHAR2(1),
        CDPRODINTE    VARCHAR2(20),
        DSERRO        VARCHAR2(200))
/

CREATE TYPE SQL_PESQ_PROD_FORN_TABLE AS TABLE OF SQL_PESQ_PROD_FORN_TYPE
/

CREATE OR REPLACE FUNCTION SQL_PESQ_PROD_FORN(P_CDFILIAL     IN VARCHAR2,
                                              P_PRODNVCOMPL  IN VARCHAR2,
                                              P_CDFILCENTCMP IN VARCHAR2 := NULL,
                                              P_CDFORNECED   IN VARCHAR2 := NULL,
                                              P_DTAUTOFORN   IN DATE := NULL,
                                              P_NRORG        IN NOVOCODIGO.NRORG%TYPE DEFAULT 1) RETURN SQL_PESQ_PROD_FORN_TABLE PIPELINED
IS
  V_CDFORNECED VARCHAR2(21);
  V_CDPRODUTO  VARCHAR2(10);
  V_DSERRO     VARCHAR2(200);
  V_COUNT      NUMERIC;
  OUT_RECORD SQL_PESQ_PROD_FORN_TYPE := SQL_PESQ_PROD_FORN_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
  BEGIN
    V_COUNT := 0;
    SELECT COUNT(1) CDPRODUTO
      INTO V_COUNT
      FROM TABLE(SQL_PESQUISA_PRODUTO(P_CDFILIAL, P_PRODNVCOMPL, P_CDFILCENTCMP, P_NRORG));
    IF V_COUNT = 1 THEN
      SELECT CDPRODUTO, DSERRO
        INTO V_CDPRODUTO, V_DSERRO
        FROM TABLE(SQL_PESQUISA_PRODUTO(P_CDFILIAL, P_PRODNVCOMPL, P_CDFILCENTCMP, P_NRORG));
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      OUT_RECORD.DSERRO := 'ERRO_SQL_PESQUISA_PRODUTO';
      PIPE ROW (OUT_RECORD); 
  END;

  /*Verifica se ocorreu erro na pesquisa anterior*/
  IF ((V_COUNT = 1) AND (V_CDPRODUTO IS NULL) AND (V_DSERRO IS NOT NULL)) THEN
    OUT_RECORD.DSERRO := V_DSERRO;
    PIPE ROW (OUT_RECORD);      
  ELSE 
    IF ((P_DTAUTOFORN IS NOT NULL) AND (P_CDFORNECED IS NOT NULL) AND (P_CDFILCENTCMP IS NOT NULL)) THEN
      BEGIN
        SELECT MAX(QFOPRODFORN.CDFORNECED)
          INTO V_CDFORNECED
          FROM QFOPRODFORN
         WHERE QFOPRODFORN.CDFORNECED = P_CDFORNECED
           AND QFOPRODFORN.NRORG = P_NRORG;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          V_CDFORNECED := '';   
      END;
      IF V_CDFORNECED IS NOT NULL THEN
        V_COUNT := 0;
        FOR C_PRODUTO IN 
          (SELECT FI.CDPRODUTO,  FI.CDARVPROD,  FI.CDPRODESTO,
                  FI.NMPRODUTO,  FI.VRFATOCONV, FI.SGUNIDADE, FI.CDNVPRODUTO,
                  FI.CDPRODINTE
             FROM PRODFORN PF, PRODUTO PR,
                  (SELECT CDPRODUTO,  CDARVPROD,  CDPRODESTO,
                          NMPRODUTO,  VRFATOCONV, SGUNIDADE, CDNVPRODUTO,
                          CDPRODINTE
                     FROM TABLE(SQL_PESQUISA_PRODUTO(P_CDFILIAL, P_PRODNVCOMPL, P_CDFILCENTCMP, P_NRORG))) FI
                    WHERE PF.CDFORNECED = P_CDFORNECED
                      AND PF.NRORG      = P_NRORG
                      AND PR.NRORG      = P_NRORG
                      AND PF.CDPRODUTO  = PR.CDPRODUTO
                      AND FI.CDARVPROD  LIKE PR.CDARVPROD||'%'
          )
        LOOP
          BEGIN
            SELECT Q.CDFORNECED
              INTO V_CDFORNECED
              FROM QFOPRODFORN Q, PARASUPRI P
             WHERE (P.CDFILIAL   = P_CDFILCENTCMP)
               AND (Q.NRORG      = P_NRORG)
               AND (P.NRORG      = P_NRORG)
               AND (Q.CDPRODUTO  = C_PRODUTO.CDPRODUTO)
               AND (Q.CDFORNECED = P_CDFORNECED)
               AND ((Q.IDFORNAPROV = 'N' AND Q.IDFORNAUTOR = 'S') OR (Q.IDFORNAPROV = 'S'))
               AND ((P.IDULTIDTVALQ  = 'M' AND TO_DATE(P_DTAUTOFORN)  < LAST_DAY(Q.DTVALPRFORN))
                OR  (P.IDULTIDTVALQ  = 'D' AND TO_DATE(P_DTAUTOFORN)  < Q.DTVALPRFORN));        
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
              V_CDFORNECED := '';
          END;
          IF V_CDFORNECED IS NOT NULL THEN
            OUT_RECORD.CDARVPROD   := C_PRODUTO.CDARVPROD;
            OUT_RECORD.CDPRODUTO   := C_PRODUTO.CDPRODUTO;
            OUT_RECORD.CDPRODESTO  := C_PRODUTO.CDPRODESTO;
            OUT_RECORD.SGUNIDADE   := C_PRODUTO.SGUNIDADE;
            OUT_RECORD.NMPRODUTO   := C_PRODUTO.NMPRODUTO;
            OUT_RECORD.VRFATOCONV  := C_PRODUTO.VRFATOCONV;
            OUT_RECORD.CDNVPRODUTO := C_PRODUTO.CDNVPRODUTO;
            OUT_RECORD.CDPRODINTE  := C_PRODUTO.CDPRODINTE;
            OUT_RECORD.DSERRO      := '';
            PIPE ROW (OUT_RECORD);          
            V_COUNT := V_COUNT + 1;
          ELSE
            BEGIN
              SELECT Q.CDFORNECED
                INTO V_CDFORNECED
                FROM QFOPRODFORN Q, PARASUPRI P
               WHERE (P.CDFILIAL   = P_CDFILCENTCMP)
                 AND (Q.NRORG      = P_NRORG)
                 AND (P.NRORG      = P_NRORG)
                 AND (Q.CDPRODUTO  = C_PRODUTO.CDPRODUTO)
                 AND (Q.CDFORNECED = P_CDFORNECED); 
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                V_CDFORNECED := '';
            END;
            IF V_CDFORNECED IS NULL THEN
              OUT_RECORD.CDARVPROD   := C_PRODUTO.CDARVPROD;
              OUT_RECORD.CDPRODUTO   := C_PRODUTO.CDPRODUTO;
              OUT_RECORD.CDPRODESTO  := C_PRODUTO.CDPRODESTO;
              OUT_RECORD.SGUNIDADE   := C_PRODUTO.SGUNIDADE;
              OUT_RECORD.NMPRODUTO   := C_PRODUTO.NMPRODUTO;
              OUT_RECORD.VRFATOCONV  := C_PRODUTO.VRFATOCONV;
              OUT_RECORD.CDNVPRODUTO := C_PRODUTO.CDNVPRODUTO;
              OUT_RECORD.CDPRODINTE  := C_PRODUTO.CDPRODINTE;
              OUT_RECORD.DSERRO      := '';
              PIPE ROW (OUT_RECORD);          
              V_COUNT := V_COUNT + 1;
            END IF;
          END IF;
        END LOOP;
        IF V_COUNT = 0 THEN
          OUT_RECORD.DSERRO := 'NAO_HA_PROD_QUALIF_FORN';
            PIPE ROW (OUT_RECORD);
        END IF;
      ELSE
        V_COUNT := 0;
        FOR C_PRODUTO IN 
          ( SELECT FI.CDPRODUTO,  FI.CDARVPROD,  FI.CDPRODESTO,
                   FI.NMPRODUTO,  FI.VRFATOCONV, FI.SGUNIDADE, FI.CDNVPRODUTO,
                   FI.CDPRODINTE
              FROM PRODFORN PF, PRODUTO PR,
                   (SELECT CDPRODUTO,  CDARVPROD,  CDPRODESTO,
                           NMPRODUTO,  VRFATOCONV, SGUNIDADE, CDNVPRODUTO,
                           CDPRODINTE
                      FROM TABLE(SQL_PESQUISA_PRODUTO(P_CDFILIAL, P_PRODNVCOMPL, P_CDFILCENTCMP, P_NRORG))) FI
             WHERE PF.CDFORNECED = P_CDFORNECED
               AND PF.NRORG      = P_NRORG
               AND PR.NRORG      = P_NRORG
               AND PF.CDPRODUTO  = PR.CDPRODUTO
               AND FI.CDARVPROD  LIKE PR.CDARVPROD||'%'
          )
        LOOP
          OUT_RECORD.CDARVPROD   := C_PRODUTO.CDARVPROD;
          OUT_RECORD.CDPRODUTO   := C_PRODUTO.CDPRODUTO;
          OUT_RECORD.CDPRODESTO  := C_PRODUTO.CDPRODESTO;
          OUT_RECORD.SGUNIDADE   := C_PRODUTO.SGUNIDADE;
          OUT_RECORD.NMPRODUTO   := C_PRODUTO.NMPRODUTO;
          OUT_RECORD.VRFATOCONV  := C_PRODUTO.VRFATOCONV;
          OUT_RECORD.CDNVPRODUTO := C_PRODUTO.CDNVPRODUTO;
          OUT_RECORD.CDPRODINTE  := C_PRODUTO.CDPRODINTE;
          OUT_RECORD.DSERRO      := '';
          PIPE ROW (OUT_RECORD);
          V_COUNT := V_COUNT + 1;
        END LOOP;      
        IF V_COUNT = 0 THEN
          OUT_RECORD.DSERRO := 'NAO_HA_PROD_FORN';
            PIPE ROW (OUT_RECORD);
        END IF;        
      END IF;
    ELSE
      IF P_CDFORNECED IS NOT NULL THEN
        V_COUNT := 0;
        FOR C_PRODUTO IN 
          ( SELECT FI.CDPRODUTO,  FI.CDARVPROD,  FI.CDPRODESTO,
                   FI.NMPRODUTO,  FI.VRFATOCONV, FI.SGUNIDADE, FI.CDNVPRODUTO,
                   FI.CDPRODINTE
              FROM PRODFORN PF, PRODUTO PR,
                   (SELECT CDPRODUTO,  CDARVPROD,  CDPRODESTO,
                           NMPRODUTO,  VRFATOCONV, SGUNIDADE, CDNVPRODUTO,
                           CDPRODINTE
                      FROM TABLE(SQL_PESQUISA_PRODUTO(P_CDFILIAL, P_PRODNVCOMPL, P_CDFILCENTCMP, P_NRORG))) FI
             WHERE PF.CDFORNECED = P_CDFORNECED
               AND PF.NRORG      = P_NRORG
               AND PR.NRORG      = P_NRORG
               AND PF.CDPRODUTO  = PR.CDPRODUTO
               AND FI.CDARVPROD  LIKE PR.CDARVPROD||'%'
          )
        LOOP
          OUT_RECORD.CDARVPROD   := C_PRODUTO.CDARVPROD;
          OUT_RECORD.CDPRODUTO   := C_PRODUTO.CDPRODUTO;
          OUT_RECORD.CDPRODESTO  := C_PRODUTO.CDPRODESTO;
          OUT_RECORD.SGUNIDADE   := C_PRODUTO.SGUNIDADE;
          OUT_RECORD.NMPRODUTO   := C_PRODUTO.NMPRODUTO;
          OUT_RECORD.VRFATOCONV  := C_PRODUTO.VRFATOCONV;
          OUT_RECORD.CDNVPRODUTO := C_PRODUTO.CDNVPRODUTO;
          OUT_RECORD.CDPRODINTE  := C_PRODUTO.CDPRODINTE;
          OUT_RECORD.DSERRO      := '';
          PIPE ROW (OUT_RECORD);
          V_COUNT := V_COUNT + 1;
        END LOOP;
        IF V_COUNT = 0 THEN
          OUT_RECORD.DSERRO := 'NAO_HA_PROD_FORN';
            PIPE ROW (OUT_RECORD);
        END IF;
      ELSE
        FOR C_PRODUTO IN 
          ( SELECT CDPRODUTO,  CDARVPROD,  CDPRODESTO,
                   NMPRODUTO,  VRFATOCONV, SGUNIDADE, CDNVPRODUTO,
                   CDPRODINTE, DSERRO
              FROM TABLE(SQL_PESQUISA_PRODUTO(P_CDFILIAL, P_PRODNVCOMPL, P_CDFILCENTCMP, P_NRORG))
          )
        LOOP
          OUT_RECORD.CDARVPROD   := C_PRODUTO.CDARVPROD;
          OUT_RECORD.CDPRODUTO   := C_PRODUTO.CDPRODUTO;
          OUT_RECORD.CDPRODESTO  := C_PRODUTO.CDPRODESTO;
          OUT_RECORD.SGUNIDADE   := C_PRODUTO.SGUNIDADE;
          OUT_RECORD.NMPRODUTO   := C_PRODUTO.NMPRODUTO;
          OUT_RECORD.VRFATOCONV  := C_PRODUTO.VRFATOCONV;
          OUT_RECORD.CDNVPRODUTO := C_PRODUTO.CDNVPRODUTO;
          OUT_RECORD.CDPRODINTE  := C_PRODUTO.CDPRODINTE;
          OUT_RECORD.DSERRO      := C_PRODUTO.DSERRO;
          PIPE ROW (OUT_RECORD);
        END LOOP;
      END IF;    
    END IF;
  END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('SQL_PESQ_PROD_FORN','6.05.000'); END;
/