CREATE OR REPLACE FUNCTION PROD_NAO_COMPRADO_PART(P_CDFILIAL     IN VARCHAR2,
                                                  P_CDFILCENTCMP IN VARCHAR2,
                                                  P_CDREGIAO     IN VARCHAR2,
                                                  P_CDCOMPRADOR  IN VARCHAR2,
                                                  P_DTINICIAL    IN DATE,
                                                  P_DTFINAL      IN DATE,
                                                  P_DTINICOT     IN DATE,
                                                  P_DTFINCOT     IN DATE,
                                                  P_CDPRODINI    IN VARCHAR2,
                                                  P_CDPRODFIN    IN VARCHAR2) RETURN PROD_NAO_COMPRADO_PART_TB PIPELINED IS
   OUT_RECORD PROD_NAO_CMP_PART_TYPE :=
              PROD_NAO_CMP_PART_TYPE (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
                                      NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
                                      NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
   V_COUNT            FLOAT;
   V_DESCPARTIC       VARCHAR2(8000);
   V_DESCINCONSIST    VARCHAR2(8000); 
   W_1 CHAR(1) := '1';
   W_2 CHAR(1) := '2';
   W_3 CHAR(1) := '3';
   W_4 CHAR(1) := '4';
   W_5 CHAR(1) := '5';
   W_6 CHAR(1) := '6';
   W_7 CHAR(1) := '7';
   W_8 CHAR(1) := '8';
   W_9 CHAR(1) := '9';
   V_EXISTS_PMARCA CHAR(1);
   V_EXISTS_PFORN CHAR(1);
   V_EXISTS_PFORNMARCA CHAR(1);
   V_PART_FORN_OK  CHAR(1);
   V_PART_MARCA_OK  CHAR(1);
   V_PART_FORNMARCA_OK  CHAR(1);
   V_PART_RM_OK CHAR(1);
   V_PART_RF_OK CHAR(1);
   V_PART_RMF_OK CHAR(1);
   V_QM_OK CHAR(1);
   V_QF_OK CHAR(1);
   V_QPF_OK CHAR(1);
   V_FNF_OK CHAR(1);
   V_FNR_OK CHAR(1);
   V_PCF_OK CHAR(1);
   V_PNC_OK CHAR(1);
   V_AND_OK CHAR(1);
   V_IDSISDOS CHAR(1);
   V_VALIDAGR CHAR(1);
   V_EXISTS_COTACAO CHAR(1);
BEGIN
   SELECT NVL(IDSISDOS, '1')
     INTO V_IDSISDOS
     FROM PARAMGERAL;
   FOR C_PREVCONS IN (SELECT B.CDFILIAL, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO,
                             PRODUTO.CDPRODUTO, SUM(B.QTPREVCONS) QTPREVCONS,
                             MIN(B.DTPREVCONS) DTPREVCONS_MIN, MAX(B.DTPREVCONS) DTPREVCONS_MAX,
                             B.CDREGIAO, PRODCOMP.CDOPERADOR
                        FROM TABLE (CONSPLA_NAO_COTADOS(P_CDFILIAL,
                                                        P_CDFILCENTCMP,
                                                        P_CDREGIAO,
                                                        P_CDCOMPRADOR,
                                                        P_DTINICIAL,
                                                        P_DTFINAL,
                                                        0,
                                                        P_CDPRODINI,
                                                        P_CDPRODFIN)) B 
                        INNER JOIN PRODUTO ON B.CDPRODUTO = PRODUTO.CDPRODUTO
                        INNER JOIN PRODUTO PRODUTOCMP ON PRODUTO.CDARVPROD LIKE PRODUTOCMP.CDARVPROD||'%'
                        INNER JOIN PRODCOMP ON PRODUTOCMP.CDPRODUTO = PRODCOMP.CDPRODUTO
                        WHERE ((INSTR(P_CDCOMPRADOR, PRODCOMP.CDOPERADOR) > 0) OR (P_CDCOMPRADOR = 'T'))
                        GROUP BY B.CDFILIAL, PRODUTO.CDARVPROD, PRODUTO.NMPRODUTO,
                                 PRODUTO.CDPRODUTO, B.CDREGIAO, PRODCOMP.CDOPERADOR) LOOP
      OUT_RECORD.DTINICIAL    := P_DTINICIAL;
      OUT_RECORD.DTFINAL      := P_DTFINAL;          
      OUT_RECORD.CDARVPROD    := C_PREVCONS.CDARVPROD;
      OUT_RECORD.NMPRODUTO    := C_PREVCONS.NMPRODUTO; 
      OUT_RECORD.PARTFILI     := NULL;
      OUT_RECORD.CDMARCA      := NULL;
      OUT_RECORD.CDFORNECED   := NULL;
      OUT_RECORD.COTADO       := 'N';
      OUT_RECORD.DTINICOT     := P_DTINICOT;
      OUT_RECORD.DTFINCOT     := P_DTFINCOT;
      OUT_RECORD.CDOPERADOR   := NULL;
      OUT_RECORD.NMOPERADOR   := NULL;
      OUT_RECORD.CDREGIAO     := NULL;
      OUT_RECORD.NMREGIAO     := NULL;     
      OUT_RECORD.IDAPRMARPRO  := NULL;
      OUT_RECORD.CDQUALIF     := NULL;
      OUT_RECORD.QFOPRODFORN  := NULL;
      OUT_RECORD.REFORFIL     := NULL;
      OUT_RECORD.REFOREG      := NULL;
      OUT_RECORD.PRCMPFILI    := NULL;
      OUT_RECORD.PRNCMP       := NULL;
      OUT_RECORD.IDSITUCOLETA := NULL;
      V_EXISTS_COTACAO := 'N';
      V_PART_FORN_OK := 'N';
      V_PART_MARCA_OK := 'N';
      V_PART_FORNMARCA_OK := 'N';
      V_PART_RM_OK := 'N';
      V_PART_RF_OK := 'N';
      V_PART_RMF_OK := 'N';
      V_DESCPARTIC := '';
      V_DESCINCONSIST := '';
      V_QM_OK := 'N';
      V_QF_OK := 'N';
      V_QPF_OK := 'N';
      V_FNF_OK := 'N';
      V_FNR_OK := 'N';
      V_PCF_OK := 'N';
      V_PNC_OK := 'N';
      V_AND_OK := 'N';
      /*GR*/
      IF V_IDSISDOS = '3' THEN
         BEGIN
            SELECT 1
              INTO V_COUNT
              FROM PARAMFILIAL
             WHERE PARAMFILIAL.CDFILIAL = C_PREVCONS.CDFILIAL
               AND PARAMFILIAL.DTPROCESSA >= (SYSDATE-30)
               AND EXISTS (SELECT 1
                             FROM GRUPFILI INNER JOIN FILGRUPFILI ON GRUPFILI.CDGRUPFILI = FILGRUPFILI.CDGRUPFILI
                            WHERE FILGRUPFILI.CDFILIAL = PARAMFILIAL.CDFILIAL
                              AND GRUPFILI.NMGRUPFILI LIKE 'PLANEJADORA%');
            V_VALIDAGR := 'S'; 
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_VALIDAGR := 'N';
         END;                  
      ELSE
         V_VALIDAGR := 'S';
      END IF;
      FOR C_ITEMCOLE IN (SELECT PRODUTO.CDPRODESTO, ITEMCOLE.CDPRODUTO, ITEMCOLE.DTINIVALITM, ITEMCOLE.DTFINVALITM, PRODUTO.CDARVPROD, 
                                NVL(COLETAPR.IDSITUCOLETA, 'C') IDSITUCOLETA, ITEMCOLE.CDMARCA, ITEMCOLE.CDFORNECED, COLETAPR.CDOPERADOR, 
                                ITEMCOLE.CDREGIAO
                           FROM COLETAPR 
                          INNER JOIN ITEMCOLE ON COLETAPR.CDFILIAL = ITEMCOLE.CDFILIAL
                                              AND COLETAPR.NRCOLEPRECO = ITEMCOLE.NRCOLEPRECO
                          INNER JOIN PRODUTO ON ITEMCOLE.CDPRODUTO = PRODUTO.CDPRODUTO
                          INNER JOIN PARASUPRI ON ITEMCOLE.CDFILIAL = PARASUPRI.CDFILIAL
                          WHERE ITEMCOLE.CDFILIAL = P_CDFILCENTCMP
                            AND ITEMCOLE.CDREGIAO = C_PREVCONS.CDREGIAO
                            AND COLETAPR.CDOPERADOR = C_PREVCONS.CDOPERADOR
                            AND PRODUTO.CDPRODESTO = C_PREVCONS.CDPRODUTO
                            AND ITEMCOLE.VRPRECUNIITM <> 0
                            AND ITEMCOLE.DTINIVALITM BETWEEN CASE 
                                                              WHEN ITEMCOLE.DTINIVALITM < P_DTINICOT
                                                              THEN ITEMCOLE.DTINIVALITM
                                                              ELSE P_DTINICOT
                                                             END AND P_DTFINCOT
                                                         AND ITEMCOLE.DTFINVALITM BETWEEN P_DTINICOT AND CASE
                                                                                                          WHEN ITEMCOLE.DTFINVALITM > P_DTFINCOT
                                                                                                          THEN ITEMCOLE.DTFINVALITM
                                                                                                          ELSE P_DTFINCOT
                                                                                                         END
                          GROUP BY PRODUTO.CDPRODESTO, ITEMCOLE.CDPRODUTO, ITEMCOLE.DTINIVALITM, ITEMCOLE.DTFINVALITM, PRODUTO.CDARVPROD, 
                                   NVL(COLETAPR.IDSITUCOLETA, 'C'), ITEMCOLE.CDMARCA, ITEMCOLE.CDFORNECED, COLETAPR.CDOPERADOR,
                                   ITEMCOLE.CDREGIAO)
      LOOP
         V_EXISTS_COTACAO := 'S';
         /*VERIFICA SE EXISTE PARTICULARIDADE PARA O PRODUTO FILIAL*/
		   /*MARCA*/
         SELECT COUNT(1)
           INTO V_COUNT
           FROM PARTFILI
          WHERE NRCOLEPRECO IS NULL
            AND CDTIPPART  IN ('1', '2') 
            AND CDPRODUTO = C_ITEMCOLE.CDPRODUTO
            AND CDFILIAL = C_PREVCONS.CDFILIAL;
		   IF V_COUNT > 0 THEN
			   V_EXISTS_PMARCA := 'S';
		   ELSE
			   V_EXISTS_PMARCA := 'N'; 
		   END IF;
         IF V_EXISTS_PMARCA = 'S' AND V_PART_MARCA_OK = 'N' THEN          
            SELECT COUNT(1)
              INTO V_COUNT
              FROM PARTFILI
             WHERE NRCOLEPRECO IS NULL
               AND CDTIPPART  IN (W_1, W_2)
               AND CDFILIAL   = C_PREVCONS.CDFILIAL
               AND CDPRODUTO  = C_ITEMCOLE.CDPRODUTO
               AND CDMARCA    = C_ITEMCOLE.CDMARCA;            
            IF V_COUNT > 0 THEN
               V_PART_MARCA_OK := 'S';
            END IF;         
         ELSE
            V_PART_MARCA_OK := 'S';
         END IF;
         IF V_PART_RM_OK = 'N' THEN
            SELECT COUNT(1)
              INTO V_COUNT
              FROM PARTFILI
             WHERE NRCOLEPRECO IS NULL
               AND CDTIPPART = W_3
               AND CDFILIAL   = C_PREVCONS.CDFILIAL
               AND CDPRODUTO  = C_ITEMCOLE.CDPRODUTO
               AND CDMARCA    = C_ITEMCOLE.CDMARCA;
            IF V_COUNT = 0 THEN
               V_PART_RM_OK := 'S';
            END IF; 
         END IF;
		   /*FORNECEDOR*/
         SELECT COUNT(1)
            INTO V_COUNT
            FROM PARTFILI
         WHERE NRCOLEPRECO IS NULL
            AND CDTIPPART  IN ('4', '5') 
            AND CDPRODUTO = C_ITEMCOLE.CDPRODUTO
            AND CDFILIAL = C_PREVCONS.CDFILIAL;
         IF V_COUNT > 0 THEN
            V_EXISTS_PFORN := 'S';
         ELSE
            V_EXISTS_PFORN := 'N'; 
         END IF;
         IF V_EXISTS_PFORN = 'S' AND V_PART_FORN_OK = 'N' THEN          
            SELECT COUNT(1)
              INTO V_COUNT
              FROM PARTFILI
             WHERE NRCOLEPRECO IS NULL
               AND CDTIPPART  IN (W_4, W_5)
               AND CDFILIAL   = C_PREVCONS.CDFILIAL
               AND CDPRODUTO  = C_ITEMCOLE.CDPRODUTO
               AND CDFORNECED = C_ITEMCOLE.CDFORNECED;
            IF V_COUNT > 0 THEN
               V_PART_FORN_OK := 'S';
            END IF;         
         ELSE
            V_PART_FORN_OK := 'S';
         END IF;
         IF V_PART_RF_OK = 'N' THEN
            SELECT COUNT(1)
              INTO V_COUNT
              FROM PARTFILI
             WHERE NRCOLEPRECO IS NULL
               AND CDTIPPART = W_6
               AND CDFILIAL   = C_PREVCONS.CDFILIAL
               AND CDPRODUTO  = C_ITEMCOLE.CDPRODUTO
               AND CDFORNECED = C_ITEMCOLE.CDFORNECED;
            IF V_COUNT = 0 THEN
               V_PART_RF_OK := 'S';
            END IF;
         END IF;
         /*MARCA FORNECEDOR*/ 
         SELECT COUNT(1)
           INTO V_COUNT
           FROM PARTFILI
          WHERE NRCOLEPRECO IS NULL
            AND CDTIPPART  IN ('7', '8') 
            AND CDPRODUTO = C_ITEMCOLE.CDPRODUTO
            AND CDFILIAL = C_PREVCONS.CDFILIAL;
         IF V_COUNT > 0 THEN
            V_EXISTS_PFORNMARCA := 'S';
         ELSE
            V_EXISTS_PFORNMARCA := 'N'; 
         END IF;
         IF V_EXISTS_PFORNMARCA = 'S' AND V_PART_FORNMARCA_OK = 'N' THEN          
            SELECT COUNT(1)
              INTO V_COUNT
              FROM PARTFILI
             WHERE NRCOLEPRECO IS NULL
               AND CDTIPPART  IN (W_7, W_8)
               AND CDFILIAL   = C_PREVCONS.CDFILIAL
               AND CDPRODUTO  = C_ITEMCOLE.CDPRODUTO
               AND CDMARCA = C_ITEMCOLE.CDMARCA
               AND CDFORNECED = C_ITEMCOLE.CDFORNECED;            
            IF V_COUNT > 0 THEN
               V_PART_FORNMARCA_OK := 'S';
            END IF;         
         ELSE
            V_PART_FORNMARCA_OK := 'S';
         END IF;
         IF V_PART_RMF_OK = 'N' THEN
            SELECT COUNT(1)
			     INTO V_COUNT
			     FROM PARTFILI
             WHERE NRCOLEPRECO IS NULL
               AND CDTIPPART = W_9
               AND CDFILIAL   = C_PREVCONS.CDFILIAL
               AND CDPRODUTO  = C_ITEMCOLE.CDPRODUTO
               AND CDMARCA    = C_ITEMCOLE.CDMARCA
               AND CDFORNECED = C_ITEMCOLE.CDFORNECED;
				IF V_COUNT = 0 THEN
				   V_PART_RMF_OK := 'S';
				END IF;
			END IF;	
         /*CONSULTA QUALIFICACAO DE MARCA*/
         IF V_QM_OK = 'N' THEN
            SELECT COUNT(1)
              INTO V_COUNT
              FROM (SELECT PARASUPRI.CDFILIAL
                      FROM QFOMARPROD, PARASUPRI
                     WHERE PARASUPRI.CDFILIAL = P_CDFILCENTCMP
                       AND QFOMARPROD.CDPRODUTO = C_ITEMCOLE.CDPRODUTO
                       AND QFOMARPROD.CDMARCA = C_ITEMCOLE.CDMARCA
                       AND ((QFOMARPROD.IDAPRMARPRO = 'N'  AND QFOMARPROD.IDAUTMARPRO = 'S')
                         OR (QFOMARPROD.IDAPRMARPRO = 'S' AND ((PARASUPRI.IDULTIDTVALQ = 'M' AND C_ITEMCOLE.DTFINVALITM <= LAST_DAY(QFOMARPROD.DTVALAPROVA))
                                                            OR (PARASUPRI.IDULTIDTVALQ  = 'D' AND C_ITEMCOLE.DTFINVALITM <= QFOMARPROD.DTVALAPROVA))))
                     UNION ALL
                    SELECT PARASUPRI.CDFILIAL
                      FROM PARASUPRI
                      WHERE PARASUPRI.CDFILIAL = P_CDFILCENTCMP
                        AND PARASUPRI.IDMARSEMQUA  <> 'S'
                        AND NOT EXISTS (SELECT QFOMARPROD.CDMARCA
                                          FROM QFOMARPROD
                                          WHERE (QFOMARPROD.CDPRODUTO = C_ITEMCOLE.CDPRODUTO)
                                            AND (QFOMARPROD.CDMARCA   = C_ITEMCOLE.CDMARCA)));
            IF V_COUNT > 0 THEN
               V_QM_OK := 'S';
            END IF;
         END IF;       
         IF V_QF_OK = 'N' THEN
            SELECT COUNT(1)
              INTO V_COUNT
              FROM (SELECT PARASUPRI.CDFILIAL
                      FROM QUALIFICACAO, PARASUPRI, FORNECEDOR
                     WHERE FORNECEDOR.CDFORNECED   = C_ITEMCOLE.CDFORNECED
                       AND PARASUPRI.CDFILIAL      = P_CDFILCENTCMP
                       AND FORNECEDOR.CDQUALIF     = QUALIFICACAO.CDQUALIF
                       AND (QUALIFICACAO.IDPERCOTCMP <> '2' AND (FORNECEDOR.IDFORNAUTO = 'S' OR 
                            ((PARASUPRI.IDULTIDTVALQ = 'D' AND C_ITEMCOLE.DTFINVALITM < FORNECEDOR.DTVALQUALIF) OR 
                             (PARASUPRI.IDULTIDTVALQ  = 'M' AND C_ITEMCOLE.DTFINVALITM < LAST_DAY(FORNECEDOR.DTVALQUALIF)))))
                     UNION ALL
                    SELECT PARASUPRI.CDFILIAL
                      FROM PARASUPRI, FORNECEDOR
                     WHERE FORNECEDOR.CDFORNECED   = C_ITEMCOLE.CDFORNECED
                       AND PARASUPRI.CDFILIAL      = P_CDFILCENTCMP
                       AND FORNECEDOR.CDQUALIF     IS NULL);
            IF V_COUNT > 0 THEN
               V_QF_OK := 'S';
            END IF;
         END IF;      
         IF V_QPF_OK = 'N' THEN
            SELECT COUNT(1)
              INTO V_COUNT
              FROM DUAL
             WHERE EXISTS (SELECT 1
                             FROM QFOPRODFORN, PARASUPRI
                            WHERE PARASUPRI.CDFILIAL = P_CDFILCENTCMP
                              AND (QFOPRODFORN.CDPRODUTO  = C_ITEMCOLE.CDPRODUTO)
                              AND (QFOPRODFORN.CDFORNECED = C_ITEMCOLE.CDFORNECED)
                              AND ((QFOPRODFORN.IDFORNAPROV = 'N'  AND QFOPRODFORN.IDFORNAUTOR = 'N') OR 
                                   ((QFOPRODFORN.IDFORNAPROV = 'S') AND ((PARASUPRI.IDULTIDTVALQ = 'M' AND C_ITEMCOLE.DTFINVALITM > LAST_DAY(QFOPRODFORN.DTVALPRFORN)) 
                                                                      OR (PARASUPRI.IDULTIDTVALQ = 'D' AND C_ITEMCOLE.DTFINVALITM > QFOPRODFORN.DTVALPRFORN)) ) ));
            IF V_COUNT = 0 THEN
               V_QPF_OK := 'S';
            END IF;
         END IF;      
         IF V_FNF_OK = 'N' THEN
            SELECT COUNT(1)
              INTO V_COUNT
              FROM (SELECT 1
                      FROM REFORFIL
                     WHERE REFORFIL.CDFILIAL       = C_PREVCONS.CDFILIAL
                       AND REFORFIL.CDFORNECED     = C_ITEMCOLE.CDFORNECED
                       AND REFORFIL.CDPERIENTR     IS NOT NULL
                     UNION ALL
                    SELECT 1
                      FROM DUAL
                     WHERE NOT EXISTS (SELECT 1
                                         FROM REFORFIL
                                        WHERE REFORFIL.CDFILIAL   = C_PREVCONS.CDFILIAL
                                          AND REFORFIL.CDFORNECED = C_ITEMCOLE.CDFORNECED));
            IF V_COUNT > 0 THEN
               V_FNF_OK := 'S';
            END IF;
         END IF;      
         IF V_FNR_OK = 'N' THEN
            SELECT COUNT(1)
              INTO V_COUNT
              FROM (SELECT 1
                      FROM REFOREG
                     WHERE REFOREG.CDREGIAO        = C_ITEMCOLE.CDREGIAO
                       AND REFOREG.CDFORNECED      = C_ITEMCOLE.CDFORNECED
                       AND REFOREG.CDPERIENTR      IS NOT NULL
                     UNION ALL
                    SELECT 1
                      FROM DUAL
                     WHERE NOT EXISTS (SELECT REFOREG.CDREGIAO
                                         FROM REFOREG
                                        WHERE REFOREG.CDREGIAO   = C_ITEMCOLE.CDREGIAO
                                          AND REFOREG.CDFORNECED = C_ITEMCOLE.CDFORNECED));
            IF V_COUNT > 0 THEN
               V_FNR_OK := 'S';
            END IF;
         END IF;     
         IF V_PCF_OK = 'N' THEN
            SELECT COUNT(1)
              INTO V_COUNT
              FROM DUAL
             WHERE EXISTS (SELECT 1
                             FROM PRODCMPFILI, PRODUTO P
                            WHERE PRODCMPFILI.CDPRODUTO = P.CDPRODUTO
                              AND PRODCMPFILI.CDFILIAL = C_PREVCONS.CDFILIAL
                              AND C_ITEMCOLE.CDARVPROD LIKE P.CDARVPROD||'%'
                            UNION ALL
                           SELECT 1
                             FROM DUAL
                            WHERE NOT EXISTS (SELECT 1
                                                FROM PRODCMPFILI, PRODUTO P
                                               WHERE PRODCMPFILI.CDPRODUTO = P.CDPRODUTO
                                                 AND PRODCMPFILI.CDFILIAL = C_PREVCONS.CDFILIAL
                                                 AND C_ITEMCOLE.CDARVPROD LIKE P.CDARVPROD||'%'));  
            IF V_COUNT > 0 THEN
               V_PCF_OK := 'S';
            END IF;
         END IF;   
         IF V_PNC_OK = 'N' THEN
            SELECT COUNT(1)
              INTO V_COUNT
              FROM PRODNCMP, PRODUTO P
             WHERE PRODNCMP.CDPRODUTO = P.CDPRODUTO
               AND PRODNCMP.CDFILIAL = C_PREVCONS.CDFILIAL
               AND C_ITEMCOLE.CDARVPROD LIKE P.CDARVPROD||'%';
            IF V_COUNT = 0 THEN
               V_PNC_OK := 'S';
            END IF;
         END IF;   
         IF V_AND_OK = 'N' THEN		
            IF C_ITEMCOLE.IDSITUCOLETA = 'C' THEN
               V_AND_OK := 'S';
            END IF;
         END IF; 			
      END LOOP;
      IF V_VALIDAGR = 'S' AND (V_EXISTS_COTACAO = 'N'    OR 
                               V_PART_FORN_OK = 'N'      OR 
                               V_PART_MARCA_OK = 'N'     OR 
                               V_PART_FORNMARCA_OK = 'N' OR 
                               V_PART_RM_OK = 'N'        OR 
	                            V_QM_OK = 'N'             OR 
                               V_QF_OK = 'N'             OR 
                               V_QPF_OK = 'N'            OR 
                               V_FNF_OK = 'N'            OR 
                               V_FNR_OK = 'N'            OR 
                               V_PCF_OK = 'N'            OR
                               V_PNC_OK = 'N'            OR 
                               V_AND_OK = 'N'          ) THEN
         IF V_EXISTS_COTACAO = 'S' THEN
            IF V_QM_OK = 'N' THEN
               V_DESCINCONSIST := V_DESCINCONSIST || 'QM; ';
               OUT_RECORD.IDAPRMARPRO := 'S';
            ELSE
               OUT_RECORD.IDAPRMARPRO := 'N';              
            END IF;
            IF V_QF_OK = 'N' THEN
               OUT_RECORD.CDQUALIF := 'S';
               V_DESCINCONSIST := V_DESCINCONSIST || 'QF; ';
            ELSE
               OUT_RECORD.CDQUALIF := 'N';            
            END IF;
            IF V_QPF_OK = 'N' THEN
               OUT_RECORD.QFOPRODFORN  := 'S';
               V_DESCINCONSIST := V_DESCINCONSIST || 'QPF; ';
            ELSE
               OUT_RECORD.QFOPRODFORN  := 'N';
            END IF;   
            IF V_FNF_OK = 'N' THEN
               OUT_RECORD.REFORFIL := 'S';
               V_DESCINCONSIST := V_DESCINCONSIST || 'FNF; ';
            ELSE
               OUT_RECORD.REFORFIL  := 'N';
            END IF;        
            IF V_FNR_OK = 'N' THEN
               OUT_RECORD.REFOREG := 'S';
               V_DESCINCONSIST := V_DESCINCONSIST || 'FNR; ';
            ELSE
               OUT_RECORD.REFOREG  := 'N';
            END IF;        
            IF V_PCF_OK = 'N' THEN
               OUT_RECORD.PRCMPFILI := 'S';
               V_DESCINCONSIST := V_DESCINCONSIST || 'PCF; ';
            ELSE
               OUT_RECORD.PRCMPFILI  := 'N';
            END IF;
            IF V_PNC_OK = 'N' THEN
               OUT_RECORD.PRNCMP := 'S';
               V_DESCINCONSIST := V_DESCINCONSIST || 'PNF; ';
            ELSE
               OUT_RECORD.PRNCMP  := 'N';
            END IF;
            IF V_AND_OK = 'N' THEN
               OUT_RECORD.IDSITUCOLETA := 'S';
               V_DESCINCONSIST := V_DESCINCONSIST || 'AND; ';
            ELSE
               OUT_RECORD.IDSITUCOLETA  := 'N';
            END IF;
         END IF;
         BEGIN
            SELECT NMFILIAL
              INTO OUT_RECORD.NMFILIAL
              FROM FILIAL
             WHERE CDFILIAL = C_PREVCONS.CDFILIAL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               OUT_RECORD.NMFILIAL := '';
         END;   
         OUT_RECORD.CDFILIAL := C_PREVCONS.CDFILIAL;
         BEGIN
            SELECT SGESTADO
              INTO OUT_RECORD.SGESTADO
              FROM ENDEFILI
             WHERE IDTPENDEFILI = 'P'
               AND CDFILIAL = C_PREVCONS.CDFILIAL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               OUT_RECORD.SGESTADO := '';   
         END;
         BEGIN
            SELECT REGIAO.NMREGIAO
              INTO OUT_RECORD.NMREGIAO
              FROM REGIAO INNER JOIN FILIAL ON FILIAL.CDREGIAO = REGIAO.CDREGIAO
             WHERE FILIAL.CDFILIAL = C_PREVCONS.CDFILIAL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               OUT_RECORD.NMREGIAO := '';   
         END;
         OUT_RECORD.CDREGIAO := C_PREVCONS.CDREGIAO;
         BEGIN
            SELECT NMOPERADOR
              INTO OUT_RECORD.NMOPERADOR
              FROM OPERADOR
             WHERE CDOPERADOR = C_PREVCONS.CDOPERADOR;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               OUT_RECORD.NMREGIAO := '';   
         END;   
         OUT_RECORD.CDOPERADOR := C_PREVCONS.CDOPERADOR;
         BEGIN
            SELECT DESCPARTIC
              INTO V_DESCPARTIC
              FROM (SELECT LISTAGG(CASE 
                                    WHEN CDTIPPART = '4'
                                    THEN 'FO - '|| NRINSJURFORN||' - '||SUBSTR(NMFANTFORN, 1, 20)
                                    ELSE CASE
                                          WHEN CDTIPPART = '5'
                                          THEN 'FP - '|| NRINSJURFORN||' - '||SUBSTR(NMFANTFORN, 1, 20)
                                          ELSE 'RF - '|| NRINSJURFORN||' - '||SUBSTR(NMFANTFORN, 1, 20)
                                         END
                                   END, '; ' ) WITHIN GROUP (ORDER BY CDTIPPART) OVER (PARTITION BY 1) DESCPARTIC
                      FROM (SELECT CDTIPPART, FORNECEDOR.CDFORNECED, NMFANTFORN, NRINSJURFORN
                              FROM PARTFILI INNER JOIN FORNECEDOR ON PARTFILI.CDFORNECED = FORNECEDOR.CDFORNECED 
                             WHERE NRCOLEPRECO IS NULL
                               AND CDTIPPART IN ('4', '5', '6')
                               AND CDFILIAL  = C_PREVCONS.CDFILIAL
                               AND CDPRODUTO = C_PREVCONS.CDPRODUTO 
                             GROUP BY CDTIPPART, FORNECEDOR.CDFORNECED, NMFANTFORN, NRINSJURFORN
                             ORDER BY CDTIPPART))
             WHERE ROWNUM = 1;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               NULL;
         END;  
         BEGIN 
            SELECT V_DESCPARTIC||'; '||DESCPARTIC
              INTO V_DESCPARTIC
              FROM (SELECT LISTAGG(CASE 
                                    WHEN CDTIPPART = '1'
                                    THEN 'MO - '|| CDMARCA||' - '||SUBSTR(NMMARCA, 1, 10)
                                    ELSE CASE
                                          WHEN CDTIPPART = '2'
                                          THEN 'MP - '|| CDMARCA||' - '||SUBSTR(NMMARCA, 1, 10)
                                          ELSE 'RM - '|| CDMARCA||' - '||SUBSTR(NMMARCA, 1, 10)
                                         END
                                   END, '; ' ) WITHIN GROUP (ORDER BY CDTIPPART) OVER (PARTITION BY 1) DESCPARTIC
                      FROM (SELECT PARTFILI.CDTIPPART, MARCA.CDMARCA, MARCA.NMMARCA
                              FROM PARTFILI INNER JOIN MARCA ON PARTFILI.CDMARCA = MARCA.CDMARCA
                             WHERE NRCOLEPRECO IS NULL
                               AND CDTIPPART IN ('1', '2', '3')
                               AND CDFILIAL  = C_PREVCONS.CDFILIAL
                               AND CDPRODUTO = C_PREVCONS.CDPRODUTO 
                             GROUP BY PARTFILI.CDTIPPART, MARCA.CDMARCA, MARCA.NMMARCA
                             ORDER BY CDTIPPART))
             WHERE ROWNUM = 1;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               NULL;
         END;
         BEGIN   
            SELECT V_DESCPARTIC||'; '||DESCPARTIC
              INTO V_DESCPARTIC
              FROM (SELECT LISTAGG(CASE 
                                    WHEN CDTIPPART = '7'
                                    THEN 'MFO - '|| CDMARCA||' '||SUBSTR(NMMARCA, 1, 10)||' / '||NRINSJURFORN||' '||SUBSTR(NMFANTFORN, 1, 20)
                                    ELSE CASE
                                          WHEN CDTIPPART = '8'
                                          THEN 'MFP - '|| CDMARCA||' '||SUBSTR(NMMARCA, 1, 10)||' / '||NRINSJURFORN||' '||SUBSTR(NMFANTFORN, 1, 20)
                                          ELSE 'RMF - '|| CDMARCA||' '||SUBSTR(NMMARCA, 1, 10)||' / '||NRINSJURFORN||' '||SUBSTR(NMFANTFORN, 1, 20)
                                       END
                                   END, '; ' ) WITHIN GROUP (ORDER BY CDTIPPART) OVER (PARTITION BY 1) DESCPARTIC
                     FROM (SELECT CDTIPPART, MARCA.CDMARCA, NMMARCA, FORNECEDOR.CDFORNECED, NMFANTFORN, NRINSJURFORN
                             FROM PARTFILI 
                            INNER JOIN MARCA ON PARTFILI.CDMARCA = MARCA.CDMARCA
                            INNER JOIN FORNECEDOR ON PARTFILI.CDFORNECED = FORNECEDOR.CDFORNECED                
                            WHERE NRCOLEPRECO IS NULL
                              AND CDTIPPART IN ('7', '8', '9')
                              AND CDFILIAL  = C_PREVCONS.CDFILIAL
                              AND CDPRODUTO = C_PREVCONS.CDPRODUTO 
                            GROUP BY CDTIPPART, MARCA.CDMARCA, NMMARCA, FORNECEDOR.CDFORNECED, NMFANTFORN, NRINSJURFORN
                            ORDER BY CDTIPPART))
             WHERE ROWNUM = 1; 
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               NULL;
         END;
         OUT_RECORD.DESCPARTIC := V_DESCPARTIC;
         OUT_RECORD.DESCINCONSIST := V_DESCINCONSIST;
         OUT_RECORD.NRORG := NVL(SYS_CONTEXT('NRORG_CTX', 'NRORG'), 1);
         PIPE ROW (OUT_RECORD);
      END IF;
   END LOOP;    
END PROD_NAO_COMPRADO_PART;