CREATE OR REPLACE PROCEDURE REPLICDADOS_SUBGRUPO(
   P_REPLICADADOS IN VARCHAR,
   P_NMPRODUTO    IN VARCHAR,
   P_CDPRODUTO    IN VARCHAR,
   P_OPERACAO     IN VARCHAR,
   P_OPERADOR     IN VARCHAR,
   P_CDFILIAL     IN VARCHAR,
   P_IDERRO       OUT VARCHAR)
AS
   V_CDARVPROD    PRODUTO.CDARVPROD%TYPE;
   V_CDNVPRODUTO  PRODUTO.CDNVPRODUTO%TYPE;
   V_CDPICTPROD   PARAMGERAL.CDPICTPROD%TYPE;
   V_CDPICTPRODR  PARAMGERAL.CDPICTPROD%TYPE;
   V_CDPRODUTO    PRODUTO.CDPRODUTO%TYPE;
   V_CDSETOR      SETOR.CDSETOR%TYPE;
   V_SETOR        SETOR.CDSETOR%TYPE;
   V_SGUNIDADE    UNIDMEDIDA.SGUNIDADE%TYPE;
   V_UNIDADE      UNIDMEDIDA.SGUNIDADE%TYPE;
   V_CDESPEPROD   ESPEPROD.CDESPEPROD%TYPE;
   V_TIPOCOMP     TCOMPPRO.CDTIPOCOMP%TYPE;
   V_CDTIPOINSU   TIPOINSU.CDTIPOINSU%TYPE;


   V_SELECT_TEXT  VARCHAR2(1000);
   V_IDERRO       VARCHAR2(200);
   C_SELECT       INTEGER;
   V_RESULT       INTEGER;

BEGIN
   V_IDERRO := '';

   IF P_OPERACAO = '1' THEN
      SELECT CDPICTPROD INTO V_CDPICTPROD
      FROM PARAMGERAL;

      V_SELECT_TEXT := '';
      V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDPICTPROD)';
      V_SELECT_TEXT := V_SELECT_TEXT||' FROM PARAMGERAL@ '||P_REPLICADADOS;
      C_SELECT := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
      DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDPICTPRODR,30);
      V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
      IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
         DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDPICTPRODR);
      END IF;
      DBMS_SQL.CLOSE_CURSOR(C_SELECT);


      IF V_CDPICTPROD <> V_CDPICTPRODR THEN
         V_IDERRO := V_IDERRO||'Mascaradiferente_';
      END IF;

      SELECT MAX(CDARVPROD), MAX(CDNVPRODUTO) INTO V_CDARVPROD, V_CDNVPRODUTO
      FROM PRODUTO
      WHERE (CDPRODUTO = P_CDPRODUTO) AND
            (IDPRODCOMISS = '2');


      IF V_CDNVPRODUTO > 1 THEN
         V_SELECT_TEXT := '';
         V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDPRODUTO)';
         V_SELECT_TEXT := V_SELECT_TEXT||' FROM PRODUTO@'||P_REPLICADADOS;
         V_SELECT_TEXT := V_SELECT_TEXT||' WHERE ('''||V_CDARVPROD||''' LIKE CDARVPROD || ''%'')  AND';
         V_SELECT_TEXT := V_SELECT_TEXT||'       ('||V_CDNVPRODUTO||' -1 = CDNVPRODUTO)  AND';
         V_SELECT_TEXT := V_SELECT_TEXT||'       (IDPRODCOMISS = ''2'')';
         C_SELECT := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
         DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDPRODUTO,30);
         V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
         IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
            DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDPRODUTO);
         END IF;
         DBMS_SQL.CLOSE_CURSOR(C_SELECT);

         IF V_CDPRODUTO IS NULL THEN
            V_IDERRO  := V_IDERRO||'ProdNivel_';
         END IF;
      END IF;

      V_SELECT_TEXT := '';
      V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDPRODUTO)';
      V_SELECT_TEXT := V_SELECT_TEXT||' FROM PRODUTO@'||P_REPLICADADOS;
      V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''') AND';
      V_SELECT_TEXT := V_SELECT_TEXT||'       (IDPRODCOMISS = ''2'')';
      C_SELECT := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
      DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDPRODUTO,30);
      V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
      IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
         DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDPRODUTO);
      END IF;
      DBMS_SQL.CLOSE_CURSOR(C_SELECT);

      IF V_CDPRODUTO IS NOT NULL THEN
         V_IDERRO := V_IDERRO||'ProdCad_';
      END IF;


      SELECT MAX(CDSETOR) INTO V_CDSETOR
      FROM PRODUTO
      WHERE (CDPRODUTO    = P_CDPRODUTO) AND
            (IDPRODCOMISS = '2');

      IF V_CDSETOR IS NOT NULL THEN
	 V_SELECT_TEXT := '';
   	 V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDSETOR)';
      	 V_SELECT_TEXT := V_SELECT_TEXT||' FROM SETOR@'||P_REPLICADADOS;
	 V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDSETOR = '''||V_CDSETOR||''')';
   	 C_SELECT := DBMS_SQL.OPEN_CURSOR;
      	 DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
	 DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_SETOR,30);
   	 V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
      	 IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
            DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_SETOR);
	 END IF;
   	 DBMS_SQL.CLOSE_CURSOR(C_SELECT);

	 IF V_SETOR IS NULL THEN
            V_IDERRO  := V_IDERRO||'SetorNCad_';
      	 END IF;
      END IF;

      SELECT MAX(SGUNIDADE) INTO V_SGUNIDADE
      FROM PRODUTO
      WHERE (CDPRODUTO    = P_CDPRODUTO) AND
            (IDPRODCOMISS = '2');

      IF V_SGUNIDADE IS NOT NULL THEN
	 V_SELECT_TEXT := '';
   	 V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(SGUNIDADE)';
      	 V_SELECT_TEXT := V_SELECT_TEXT||' FROM UNIDMEDIDA@'||P_REPLICADADOS;
	 V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (SGUNIDADE = '''||V_SGUNIDADE||''')';
   	 C_SELECT := DBMS_SQL.OPEN_CURSOR;
      	 DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
	 DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_UNIDADE,30);
   	 V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
      	 IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
	    DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_UNIDADE);
   	 END IF;
      	 DBMS_SQL.CLOSE_CURSOR(C_SELECT);
	 IF V_UNIDADE IS NULL THEN
            V_IDERRO  := V_IDERRO||'UnidadeNCad_';
      	 END IF;
      END IF;

      IF V_IDERRO IS NULL THEN
         V_SELECT_TEXT := '';
         V_SELECT_TEXT := V_SELECT_TEXT||' INSERT INTO PRODUTO@'||P_REPLICADADOS;
         V_SELECT_TEXT := V_SELECT_TEXT||' (CDARVPROD,    CDPRODUTO,    CDNVPRODUTO,  CDPRODESTO,   NMPRODUTO,  ';
         V_SELECT_TEXT := V_SELECT_TEXT||'  CDSETOR,      SGUNIDADE,    NMPRODESPANH, NMPRODINGLES, CDPRODINTE, ';
         V_SELECT_TEXT := V_SELECT_TEXT||'  IDPRODATIVPR, IDPRODCOMISS, IDPRODPROD)';
         V_SELECT_TEXT := V_SELECT_TEXT||' SELECT';
         V_SELECT_TEXT := V_SELECT_TEXT||'  CDARVPROD,    CDPRODUTO,    CDNVPRODUTO,  CDPRODESTO,  '''||P_NMPRODUTO||''',';
         V_SELECT_TEXT := V_SELECT_TEXT||'  CDSETOR,      SGUNIDADE,    NMPRODESPANH, NMPRODINGLES, CDPRODINTE,';
         V_SELECT_TEXT := V_SELECT_TEXT||'  IDPRODATIVPR, IDPRODCOMISS, IDPRODPROD';
         V_SELECT_TEXT := V_SELECT_TEXT||' FROM PRODUTO ';
         V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''') AND';
         V_SELECT_TEXT := V_SELECT_TEXT||'       (IDPRODCOMISS = ''2'')';
         C_SELECT := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
         V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
         DBMS_SQL.CLOSE_CURSOR(C_SELECT);
      END IF;


      FOR C_ESPEPROD IN (SELECT CDPRODUTO, CDESPEPROD, IDCOMPPADRAO
						       FROM ESPEPROD
						       WHERE (CDPRODUTO  = P_CDPRODUTO ) AND
						   		    (IDTPMANUAL IS NULL       )) LOOP

	 IF C_ESPEPROD.CDESPEPROD IS NOT NULL THEN
            V_SELECT_TEXT := '';
	    V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDESPEPROD) ';
	    V_SELECT_TEXT := V_SELECT_TEXT||' FROM ESPEPROD@'||P_REPLICADADOS;
	    V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||'''               ) AND';
            V_SELECT_TEXT := V_SELECT_TEXT||'       (IDCOMPPADRAO = '''||C_ESPEPROD.IDCOMPPADRAO||''') AND';
            V_SELECT_TEXT := V_SELECT_TEXT||'       (IDTPMANUAL IS NULL                              ) AND';
            V_SELECT_TEXT := V_SELECT_TEXT||'       (CDFILIAL = '''||P_CDFILIAL||''')';
            C_SELECT := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
            DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDESPEPROD,30);
	    V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
      	    IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
               DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDESPEPROD);
	    END IF;
      	    DBMS_SQL.CLOSE_CURSOR(C_SELECT);

	    IF V_CDESPEPROD IS NOT NULL THEN
               V_IDERRO  := V_IDERRO||'TipoEspecificacao_';
	    END IF;

            IF V_IDERRO IS NULL THEN 
   	       V_SELECT_TEXT := '';
   	       V_SELECT_TEXT := V_SELECT_TEXT||' INSERT INTO ESPEPROD@'||P_REPLICADADOS;
	       V_SELECT_TEXT := V_SELECT_TEXT||' (CDPRODUTO,    CDESPEPROD,   NMESPEPROD,   CDOPERADOR, CDFILIAL,';
	       V_SELECT_TEXT := V_SELECT_TEXT||'  DTVALESPEPRO, DTINCESPEPRO, QTPADESPEPRO, IDCOMPPADRAO)';
	       V_SELECT_TEXT := V_SELECT_TEXT||' SELECT';
   	       V_SELECT_TEXT := V_SELECT_TEXT||'  CDPRODUTO,    CDESPEPROD,   NMESPEPROD, '''||P_OPERADOR||''', '''||P_CDFILIAL||''',';
	       V_SELECT_TEXT := V_SELECT_TEXT||'  DTVALESPEPRO, DTINCESPEPRO, QTPADESPEPRO, IDCOMPPADRAO';
   	       V_SELECT_TEXT := V_SELECT_TEXT||' FROM ESPEPROD';
      	       V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''') AND';
               V_SELECT_TEXT := V_SELECT_TEXT||'       (CDESPEPROD = '''||C_ESPEPROD.CDESPEPROD||''')';
	       C_SELECT := DBMS_SQL.OPEN_CURSOR;
	       DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
	       V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   	       DBMS_SQL.CLOSE_CURSOR(C_SELECT);
            END IF;

            FOR TCOMPPROD IN (SELECT TC.CDTIPOCOMP
            		      FROM TCOMPPRO TC
 			      WHERE (TC.CDPRODUTO     = P_CDPRODUTO          ) AND
                   	            (TC.CDESPEPROD    = C_ESPEPROD.CDESPEPROD)) LOOP

               IF TCOMPPROD.CDTIPOCOMP IS NOT NULL THEN
		  V_SELECT_TEXT := '';
		  V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDTIPOCOMP)';
		  V_SELECT_TEXT := V_SELECT_TEXT||' FROM TIPOCOMP@'||P_REPLICADADOS;
		  V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDTIPOCOMP = '''||TCOMPPROD.CDTIPOCOMP||''')';
		  C_SELECT := DBMS_SQL.OPEN_CURSOR;
		  DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
		  DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_TIPOCOMP,30);
		  V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
		  IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
		     DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_TIPOCOMP);
		  END IF;
		  DBMS_SQL.CLOSE_CURSOR(C_SELECT);

                  IF V_IDERRO IS NULL THEN
		     IF V_TIPOCOMP IS NULL THEN
                        V_SELECT_TEXT := '';
		        V_SELECT_TEXT := V_SELECT_TEXT||' INSERT INTO TIPOCOMP@'||P_REPLICADADOS;
		        V_SELECT_TEXT := V_SELECT_TEXT||' (CDTIPOCOMP, NMTIPOCOMP)';
        	        V_SELECT_TEXT := V_SELECT_TEXT||' SELECT';
                        V_SELECT_TEXT := V_SELECT_TEXT||'CDTIPOCOMP, NMTIPOCOMP';
                        V_SELECT_TEXT := V_SELECT_TEXT||' FROM TIPOCOMP';
                        V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDTIPOCOMP = '''||TCOMPPROD.CDTIPOCOMP||''')';
		        C_SELECT := DBMS_SQL.OPEN_CURSOR;
		        DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
		        V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   		        DBMS_SQL.CLOSE_CURSOR(C_SELECT);
		     END IF;

               	     V_SELECT_TEXT := '';
	   	     V_SELECT_TEXT := V_SELECT_TEXT||' INSERT INTO TCOMPPRO@'||P_REPLICADADOS;
		     V_SELECT_TEXT := V_SELECT_TEXT||' (CDPRODUTO, CDESPEPROD, CDTIPOCOMP, QTPRODTCOMP, IDTCOMPPROC)';
		     V_SELECT_TEXT := V_SELECT_TEXT||' SELECT';
   	   	     V_SELECT_TEXT := V_SELECT_TEXT||'  CDPRODUTO, CDESPEPROD, CDTIPOCOMP, QTPRODTCOMP, IDTCOMPPROC';
   		     V_SELECT_TEXT := V_SELECT_TEXT||' FROM TCOMPPRO';
      		     V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''') AND';
         	     V_SELECT_TEXT := V_SELECT_TEXT||'       (CDESPEPROD = '''||C_ESPEPROD.CDESPEPROD||''') AND';
         	     V_SELECT_TEXT := V_SELECT_TEXT||'       (CDTIPOCOMP = '''||TCOMPPROD.CDTIPOCOMP||''')';
	      	     C_SELECT := DBMS_SQL.OPEN_CURSOR;
		     DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
		     V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   		     DBMS_SQL.CLOSE_CURSOR(C_SELECT);
                  END IF;

                  FOR COMPPROD IN (SELECT CP.CDPRODUTO,  CP.CDESPEPROD,   CP.CDTIPOCOMP,CP.CDPRODINSUMO,
                  	                  CP.CDTIPOINSU, CP.IDUTAPROVREQ, CP.QTINSUMO,  CP.IDSEMIACCOMP,
                     	                  PR.NMPRODUTO,  CP.SGUNIDADE,    PR.SGUNIDADE AS UN,
                        	          PR.CDARVPROD,  CP.IDUTILOTEPR
            			   FROM COMPPROD CP, PRODUTO PR
				   WHERE (CP.CDPRODUTO    = P_CDPRODUTO           ) AND
				         (CP.CDESPEPROD   = C_ESPEPROD.CDESPEPROD ) AND
                                    	 (CP.CDTIPOCOMP   = TCOMPPROD.CDTIPOCOMP  ) AND
					 (CP.CDPRODINSUMO = PR.CDPRODUTO          ) AND
                                  	 (PR.IDPRODCOMISS IS NULL                 )) LOOP

                     IF COMPPROD.CDPRODINSUMO IS NOT NULL THEN
                        V_SELECT_TEXT := '';
	   		V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDARVPROD)';
		   	V_SELECT_TEXT := V_SELECT_TEXT||' FROM PRODUTO@'||P_REPLICADADOS;
			V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||COMPPROD.CDPRODINSUMO||''')';
			C_SELECT := DBMS_SQL.OPEN_CURSOR;
			DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
			DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDARVPROD,30);
			V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   			IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
	   		   DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDARVPROD);
		   	END IF;
			DBMS_SQL.CLOSE_CURSOR(C_SELECT);

                     	IF V_CDARVPROD IS NULL THEN
                           V_IDERRO  := V_IDERRO||'ProdMaPriInsumo_';
			END IF;
                     END IF;

                     IF COMPPROD.UN IS NOT NULL THEN
                     	V_SELECT_TEXT := '';
	   		V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(SGUNIDADE)';
		   	V_SELECT_TEXT := V_SELECT_TEXT||' FROM UNIDMEDIDA@'||P_REPLICADADOS;
			V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (UPPER(SGUNIDADE) = UPPER('''||COMPPROD.UN||'''))';
			C_SELECT := DBMS_SQL.OPEN_CURSOR;
  			DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
			DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_UNIDADE,30);
			V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   			IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
	   		   DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_UNIDADE);
		   	END IF;
			DBMS_SQL.CLOSE_CURSOR(C_SELECT);
			IF V_UNIDADE IS NULL THEN
                           V_IDERRO  := V_IDERRO||'UniAdicional_';
			END IF;
                     END IF;

                     V_UNIDADE     := '';
                     IF COMPPROD.SGUNIDADE IS NOT NULL THEN
                     	V_SELECT_TEXT := '';
	   		V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(SGUNIDADE)';
		   	V_SELECT_TEXT := V_SELECT_TEXT||' FROM UNIDMEDIDA@'||P_REPLICADADOS;
			V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (UPPER(SGUNIDADE) = UPPER('''||COMPPROD.SGUNIDADE||'''))';
			C_SELECT := DBMS_SQL.OPEN_CURSOR;
  			DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
			DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_UNIDADE,30);
			V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   			IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
	   		   DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_UNIDADE);
		   	END IF;
			DBMS_SQL.CLOSE_CURSOR(C_SELECT);

			IF V_UNIDADE IS NULL THEN
                           V_IDERRO  := V_IDERRO||'UniAdicional_';
			END IF;
                     END IF;

                     V_CDTIPOINSU  := '';
                     IF COMPPROD.CDTIPOINSU IS NOT NULL THEN
         	        V_SELECT_TEXT := '';
	   		V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDTIPOINSU)';
		   	V_SELECT_TEXT := V_SELECT_TEXT||' FROM TIPOINSU@'||P_REPLICADADOS;
			V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDTIPOINSU = '''||COMPPROD.CDTIPOINSU||''')';
			C_SELECT := DBMS_SQL.OPEN_CURSOR;
			DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
			DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDTIPOINSU,30);
			V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   			IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
	   		   DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDTIPOINSU);
		   	END IF;
			DBMS_SQL.CLOSE_CURSOR(C_SELECT);
			IF V_CDTIPOINSU IS NULL THEN
                           V_IDERRO  := V_IDERRO||'TipoMaterial_';
			END IF;
                     END IF;

                     IF V_IDERRO IS NULL THEN
         	        V_SELECT_TEXT := '';
		        V_SELECT_TEXT := V_SELECT_TEXT||' INSERT INTO COMPPROD@'||P_REPLICADADOS;
		        V_SELECT_TEXT := V_SELECT_TEXT||' (CDPRODUTO,    CDESPEPROD, CDTIPOCOMP,   CDPRODINSUMO, CDTIPOINSU,';
  		        V_SELECT_TEXT := V_SELECT_TEXT||'  IDUTAPROVREQ, QTINSUMO,   IDSEMIACCOMP, SGUNIDADE,    IDUTILOTEPR)';
		        V_SELECT_TEXT := V_SELECT_TEXT||' VALUES';
   		        V_SELECT_TEXT := V_SELECT_TEXT||'  ('''||COMPPROD.CDPRODUTO||''','''||COMPPROD.CDESPEPROD||''','''||COMPPROD.CDTIPOCOMP||''','''||COMPPROD.CDPRODINSUMO||''', '''||COMPPROD.CDTIPOINSU||''', ';
   		        V_SELECT_TEXT := V_SELECT_TEXT||'   '''||COMPPROD.IDUTAPROVREQ||''','''||COMPPROD.QTINSUMO||''','''||COMPPROD.IDSEMIACCOMP||''','''||COMPPROD.SGUNIDADE||''', '''||COMPPROD.IDUTILOTEPR||''')';
		        C_SELECT := DBMS_SQL.OPEN_CURSOR;
		        DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
		        V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   		        DBMS_SQL.CLOSE_CURSOR(C_SELECT);
                     END IF;
               	  END LOOP;

               	  FOR COMPRECE IN (SELECT CP.CDPRODUTO,  CP.CDESPEPROD,   CP.CDTIPOCOMP, CP.CDPRODINSUMO,
                  	                  CP.CDTIPOINSU, CP.IDUTAPROVREQ, CP.QTINSUMO,   CP.IDSEMIACCOMP,
                     	                  PR.NMPRODUTO,  CP.SGUNIDADE,    PR.SGUNIDADE AS UN,
                        	          PR.CDARVPROD,  CP.IDUTILOTEPR,  PR.IDPRODATIVPR
            			   FROM COMPPROD CP, PRODUTO PR
				   WHERE (CP.CDPRODUTO    = P_CDPRODUTO           ) AND
				         (CP.CDESPEPROD   = C_ESPEPROD.CDESPEPROD ) AND
                                         (CP.CDTIPOCOMP   = TCOMPPROD.CDTIPOCOMP  ) AND
				         (CP.CDPRODINSUMO = PR.CDPRODUTO          ) AND
                                         (PR.IDPRODCOMISS = '3'                   )) LOOP

                     IF COMPRECE.CDPRODINSUMO IS NOT NULL THEN
                        V_SELECT_TEXT := '';
	      	        V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDARVPROD)';
		        V_SELECT_TEXT := V_SELECT_TEXT||' FROM PRODUTO@'||P_REPLICADADOS;
		        V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||COMPRECE.CDPRODINSUMO||''')';
		        C_SELECT := DBMS_SQL.OPEN_CURSOR;
		        DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
		        DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDARVPROD,30);
		        V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   		        IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
	   	         	 DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDARVPROD);
		        END IF;
		        DBMS_SQL.CLOSE_CURSOR(C_SELECT);
      	                IF V_CDARVPROD IS NULL THEN
                           V_IDERRO  := V_IDERRO||'ProdutoInsumoRecComp_';
		        END IF;
                     END IF;

                     IF COMPRECE.UN IS NOT NULL THEN
                        V_SELECT_TEXT := '';
      		        V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(SGUNIDADE)';
		        V_SELECT_TEXT := V_SELECT_TEXT||' FROM UNIDMEDIDA@'||P_REPLICADADOS;
		        V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (UPPER(SGUNIDADE) = UPPER('''||COMPRECE.UN||'''))';
		        C_SELECT := DBMS_SQL.OPEN_CURSOR;
		        DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
		        DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_UNIDADE,30);
		        V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   		        IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
	   	           DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_UNIDADE);
		        END IF;
		        DBMS_SQL.CLOSE_CURSOR(C_SELECT);
      	                IF V_UNIDADE IS NULL THEN
                           V_IDERRO  := V_IDERRO||'UNAdicionalReceCom_';
		        END IF;
                     END IF;

                     V_UNIDADE     := '';
                     IF COMPRECE.SGUNIDADE IS NOT NULL THEN
                        V_SELECT_TEXT := '';
      		        V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(SGUNIDADE)';
		        V_SELECT_TEXT := V_SELECT_TEXT||' FROM UNIDMEDIDAD@'||P_REPLICADADOS;
		        V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (UPPER(SGUNIDADE) = UPPER('''||COMPRECE.SGUNIDADE||'''))';
		        C_SELECT := DBMS_SQL.OPEN_CURSOR;
		        DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
		        DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_UNIDADE,30);
		        V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   		        IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
	   	           DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_UNIDADE);
		        END IF;
		        DBMS_SQL.CLOSE_CURSOR(C_SELECT);
      	                IF V_UNIDADE IS NULL THEN
                           V_IDERRO  := V_IDERRO||'UNAdicionalReceCom_';
		        END IF;
                     END IF;

                     V_CDTIPOINSU  := '';
                     IF COMPRECE.CDTIPOINSU IS NOT NULL THEN
                        V_SELECT_TEXT := '';
	   	        V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDTIPOINSU)';
		        V_SELECT_TEXT := V_SELECT_TEXT||' FROM TIPOINSU@'||P_REPLICADADOS;
		        V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDTIPOINSU = '''||COMPRECE.CDTIPOINSU||''')';
		        C_SELECT := DBMS_SQL.OPEN_CURSOR;
		        DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
		        DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDTIPOINSU,30);
		        V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   		        IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
	   	           DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDTIPOINSU);
		        END IF;
		        DBMS_SQL.CLOSE_CURSOR(C_SELECT);
                        IF V_CDTIPOINSU IS NULL THEN
                           V_IDERRO  := V_IDERRO||'TipoMateRece_';
		        END IF;
                     END IF;

                     IF V_IDERRO IS NULL THEN
   	                V_SELECT_TEXT := '';
		        V_SELECT_TEXT := V_SELECT_TEXT||' INSERT INTO COMPPROD@'||P_REPLICADADOS;
		        V_SELECT_TEXT := V_SELECT_TEXT||' (CDPRODUTO,    CDESPEPROD, CDTIPOCOMP,   CDPRODINSUMO, CDTIPOINSU,';
  		        V_SELECT_TEXT := V_SELECT_TEXT||'  IDUTAPROVREQ, QTINSUMO,   IDSEMIACCOMP, SGUNIDADE,    IDUTILOTEPR)';
		        V_SELECT_TEXT := V_SELECT_TEXT||' VALUES';
   	   	        V_SELECT_TEXT := V_SELECT_TEXT||'  ('''||COMPRECE.CDPRODUTO||''','''||COMPRECE.CDESPEPROD||''','''||COMPRECE.CDTIPOCOMP||''','''||COMPRECE.CDPRODINSUMO||''', '''||COMPRECE.CDTIPOINSU||''',';
	   	        V_SELECT_TEXT := V_SELECT_TEXT||'   '''||COMPRECE.IDUTAPROVREQ||''','''||COMPRECE.QTINSUMO||''','''||COMPRECE.IDSEMIACCOMP||''','''||COMPRECE.SGUNIDADE||''', '''||COMPRECE.IDUTILOTEPR||''')';
		        C_SELECT := DBMS_SQL.OPEN_CURSOR;
		        DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
		        V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
   		        DBMS_SQL.CLOSE_CURSOR(C_SELECT);
                     END IF;
               	  END LOOP;
               END IF;
            END LOOP;
         END IF;
      END LOOP;
   END IF;

   IF P_OPERACAO = '2' THEN
      V_SELECT_TEXT := '';
      V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDPRODUTO)';
      V_SELECT_TEXT := V_SELECT_TEXT||' FROM PRODUTO@'||P_REPLICADADOS;
      V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''') AND';
      V_SELECT_TEXT := V_SELECT_TEXT||'       (IDPRODCOMISS = ''2'')';
      C_SELECT := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
      DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDPRODUTO,30);
      V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
      IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
         DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDPRODUTO);
      END IF;
      DBMS_SQL.CLOSE_CURSOR(C_SELECT);

      IF V_CDPRODUTO IS NULL THEN
         V_IDERRO  := V_IDERRO||'ReceitaNCad_';
      END IF;

      SELECT MAX(CDARVPROD), MAX(CDNVPRODUTO) INTO V_CDARVPROD, V_CDNVPRODUTO
      FROM PRODUTO
      WHERE (CDPRODUTO = P_CDPRODUTO) AND
            (IDPRODCOMISS = '2');

      V_SELECT_TEXT := '';
      V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDPRODUTO)';
      V_SELECT_TEXT := V_SELECT_TEXT||' FROM PRODUTO@'||P_REPLICADADOS;
      V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDARVPROD LIKE '''||V_CDARVPROD||''' || ''%'')  AND';
      V_SELECT_TEXT := V_SELECT_TEXT||'       ('||V_CDNVPRODUTO||' +1 = CDNVPRODUTO)  AND';
      V_SELECT_TEXT := V_SELECT_TEXT||'       (IDPRODCOMISS = ''2'')';
      C_SELECT := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
      DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDPRODUTO,30);
      V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
      IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
         DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDPRODUTO);
      END IF;
      DBMS_SQL.CLOSE_CURSOR(C_SELECT);
      IF V_CDPRODUTO IS NOT NULL THEN
         V_IDERRO  := V_IDERRO||'ReceitaNivelPos_';
      END IF;

      V_SELECT_TEXT := '';
      V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CP.CDPRODUTO)';
      V_SELECT_TEXT := V_SELECT_TEXT||' FROM COMPPROD@'||P_REPLICADADOS||' CP, PRODUTO@'||P_REPLICADADOS||' PR';
      V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CP.CDPRODUTO = '''||P_CDPRODUTO||''') AND';
      V_SELECT_TEXT := V_SELECT_TEXT||'       (CP.CDPRODINSUMO = PR.CDPRODUTO      ) AND';
      V_SELECT_TEXT := V_SELECT_TEXT||'       (PR.IDPRODCOMISS IS NULL             )';
      C_SELECT := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
      DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDPRODUTO,30);
      V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
      IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
         DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDPRODUTO);
      END IF;
      DBMS_SQL.CLOSE_CURSOR(C_SELECT);

      IF V_IDERRO IS NULL THEN
         IF V_CDPRODUTO IS not NULL THEN
            V_SELECT_TEXT := '';
         	 V_SELECT_TEXT := V_SELECT_TEXT||' DELETE FROM COMPPROD@'||P_REPLICADADOS;
         	 V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''')';
            C_SELECT := DBMS_SQL.OPEN_CURSOR;
         	 DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
         	 V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
         	 DBMS_SQL.CLOSE_CURSOR(C_SELECT);
         END IF;

         V_SELECT_TEXT := '';
         V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CP.CDPRODUTO)';
         V_SELECT_TEXT := V_SELECT_TEXT||' FROM COMPPROD@'||P_REPLICADADOS||' CP, PRODUTO@'||P_REPLICADADOS||' PR';
         V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CP.CDPRODUTO = '''||P_CDPRODUTO||''') AND';
         V_SELECT_TEXT := V_SELECT_TEXT||'       (CP.CDPRODINSUMO = PR.CDPRODUTO      ) AND';
         V_SELECT_TEXT := V_SELECT_TEXT||'       (PR.IDPRODCOMISS = ''3''             )';
         C_SELECT := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
         DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDPRODUTO,30);
         V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
         IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
            DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDPRODUTO);
         END IF;
         DBMS_SQL.CLOSE_CURSOR(C_SELECT);

         IF V_CDPRODUTO IS not NULL THEN
            V_SELECT_TEXT := '';
            V_SELECT_TEXT := V_SELECT_TEXT||' DELETE FROM DOCUPROD@'||P_REPLICADADOS;
            V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''') ';
            C_SELECT := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
            V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
            DBMS_SQL.CLOSE_CURSOR(C_SELECT);

            V_SELECT_TEXT := '';
            V_SELECT_TEXT := V_SELECT_TEXT||' DELETE FROM COMPPROD@'||P_REPLICADADOS;
            V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''') ';
            C_SELECT := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
            V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
            DBMS_SQL.CLOSE_CURSOR(C_SELECT);
         END IF;

         V_SELECT_TEXT := '';
         V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDTIPOCOMP)';
         V_SELECT_TEXT := V_SELECT_TEXT||' FROM TCOMPPRO@'||P_REPLICADADOS;
         V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''')';
         C_SELECT := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
         DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_TIPOCOMP,30);
         V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
         IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
            DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_TIPOCOMP);
         END IF;
         DBMS_SQL.CLOSE_CURSOR(C_SELECT);

         IF V_TIPOCOMP IS not NULL THEN
            V_SELECT_TEXT := '';
            V_SELECT_TEXT := V_SELECT_TEXT||' DELETE FROM TCOMPPRO@'||P_REPLICADADOS;
            V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''')';
            C_SELECT := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
            V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
            DBMS_SQL.CLOSE_CURSOR(C_SELECT);
         END IF;

         V_SELECT_TEXT := '';
         V_SELECT_TEXT := V_SELECT_TEXT||' SELECT MAX(CDPRODUTO)';
         V_SELECT_TEXT := V_SELECT_TEXT||' FROM ESPEPROD@'||P_REPLICADADOS;
         V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''') AND ';
         V_SELECT_TEXT := V_SELECT_TEXT||'       (IDTPMANUAL IS NULL                   )';
         C_SELECT := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
         DBMS_SQL.DEFINE_COLUMN(C_SELECT,1,V_CDPRODUTO,30);
         V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
         IF DBMS_SQL.FETCH_ROWS(C_SELECT)>0 THEN
            DBMS_SQL.COLUMN_VALUE(C_SELECT,1,V_CDPRODUTO);
         END IF;

         V_SELECT_TEXT := '';
         V_SELECT_TEXT := V_SELECT_TEXT||' DELETE FROM ESPEPROD@'||P_REPLICADADOS;
         V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''')';
         C_SELECT := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
         V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
         DBMS_SQL.CLOSE_CURSOR(C_SELECT);

         V_SELECT_TEXT := '';
         V_SELECT_TEXT := V_SELECT_TEXT||' DELETE FROM PRODUTO@'||P_REPLICADADOS;
         V_SELECT_TEXT := V_SELECT_TEXT||' WHERE (CDPRODUTO = '''||P_CDPRODUTO||''') AND';
         V_SELECT_TEXT := V_SELECT_TEXT||'       (IDPRODCOMISS = ''2'')';
         C_SELECT := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(C_SELECT,V_SELECT_TEXT,DBMS_SQL.V7);
         V_RESULT := DBMS_SQL.EXECUTE(C_SELECT);
         DBMS_SQL.CLOSE_CURSOR(C_SELECT);
      END IF;
   END IF;
   IF V_IDERRO IS NULL THEN
      V_IDERRO := 'OK';
      COMMIT;
   END IF;
   P_IDERRO := V_IDERRO;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('REPLICDADOS_SUBGRUPO','4.07.040'); END;
/
