 IF EXISTS (SELECT 
              * 
            FROM
              SYSOBJECTS O, SYSUSERS U
            WHERE
              O.NAME = 'ARQDIGITAL_INICIO' AND
              TYPE   = 'P' 		   AND
              U.UID  = O.UID               AND
              U.NAME = USER)

DROP PROCEDURE ARQDIGITAL_INICIO
GO

CREATE PROCEDURE ARQDIGITAL_INICIO
   @P_CDIMPOSTO    VARCHAR(02),
   @P_CDEMPRESA    VARCHAR(02),
   @P_CDINSCESTA   VARCHAR(14),
   @P_DTINICIAL    DATETIME,
   @P_DTFINAL      DATETIME,
   @P_TIPO         VARCHAR(4000),
   @P_FINALIDADE   VARCHAR(4000),
   @P_INDESCRITU   VARCHAR(4000),
   @P_NRSEQARQMAG  VARCHAR(4000),
   @P_IDCENTRAL    VARCHAR(4000),
   @P_RETURN       NUMERIC(11)OUTPUT
AS

DECLARE   @V_CONTREGA  NUMERIC;
DECLARE   @V_CODIGO    VARCHAR(21);
DECLARE   @V_ORIGEM    VARCHAR(01);
DECLARE   @V_ARQ_0000  VARCHAR(4000);
DECLARE   @V_ARQ_0005  VARCHAR(4000);
DECLARE   @V_ARQ_0010  VARCHAR(4000);
DECLARE   @V_ARQ_0100  VARCHAR(4000);
DECLARE   @V_ARQ_0125  VARCHAR(4000);
DECLARE   @V_ARQ_0150  VARCHAR(4000);
DECLARE   @V_ARQ_0175  VARCHAR(4000);
DECLARE   @V_ARQ_0200  VARCHAR(4000);
DECLARE   @V_ARQ_0400  VARCHAR(4000);
DECLARE   @V_CNPJ_0150 VARCHAR(4000); 
DECLARE   @V_CDARVPROD VARCHAR(4000);

BEGIN

DECLARE C_REG0000 CURSOR FOR (SELECT  ('|0000'                                                           + '|' +
                                        'LFPD'                                                           + '|' +
                                        '002'                                                            + '|' +
                                        '000'                                                            + '|' +
                                        ISNULL(@P_FINALIDADE, '')                                        + '|' +
                                        ISNULL(REPLACE(CONVERT(VARCHAR,@P_DTINICIAL,103),'/', ''), '')   + '|' +
                                        ISNULL(REPLACE(CONVERT(VARCHAR,@P_DTFINAL,103),'/', ''), '')     + '|' +
                                        ISNULL(@P_TIPO, '')                                                      + '|' +
                                        ISNULL(@P_INDESCRITU, '')                                                + '|' +
                                        ISNULL(E.NMEMPRESA, '')                                                  + '|' +
                                        ISNULL(I.NRINSJURIEST, '')                                               + '|' +
                                        ''                                                                       + '|' +
                                        ''                                                                       + '|' +
                                        ISNULL(I.SGESTADO, '')                                                   + '|' +
                                        ISNULL(REPLACE(REPLACE(REPLACE(I.CDINSCESTA,'.',''),'-',''),'/',''), '') + '|' +
                                        ISNULL(RIGHT(REPLICATE('0', 7)+ISNULL(M.CDMUNICIBGE,'0'),7), '')         + '|' +
                                        ISNULL(E.CDINSCMUNI, '')                                                 + '|' +
                                        ''                                                                       + '|'  ) AS ARQ_0000
                               FROM EMPRESA E, MUNICIPIO M, INSCRESTAD I
                               WHERE I.CDEMPRESA   = @P_CDEMPRESA   AND
                                     I.CDINSCESTA  = @P_CDINSCESTA  AND
                                     I.CDPAIS      = M.CDPAIS       AND
                                     I.SGESTADO    = M.SGESTADO     AND
                                     I.CDMUNICIPIO = M.CDMUNICIPIO  AND
                                     E.CDEMPRESA   = I.CDEMPRESA      )
   OPEN C_REG0000;
 
   FETCH NEXT FROM C_REG0000 INTO @V_ARQ_0000;
 
   WHILE (@@FETCH_STATUS = 0) BEGIN 
       
     SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
     INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
     VALUES
        ('00'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0000, @P_NRSEQARQMAG);
     FETCH NEXT FROM C_REG0000 INTO @V_ARQ_0000;
   END;

   CLOSE C_REG0000;
   DEALLOCATE C_REG0000;

   /* ABERTURA DO ARQUIVO 0000 */
   SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('01'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), '|0001'+'|'+'0'+ '|', @P_NRSEQARQMAG);

   DECLARE C_REG0005 CURSOR FOR (SELECT ('|0005'                                  + '|' +
	                                  ISNULL(EP.NMRAZSOCEMPR, '')             + '|' +
                             		  ISNULL(REPLACE(IE.NRCEPIEST,'-',''), '')+ '|' +
                             		  ISNULL(IE.DSENDEIEST, '')               + '|' +
                             		  ''                                      + '|' +
                              	          ISNULL(IE.DSCOMPENDIES, '')             + '|' +
                            		  ISNULL(IE.NMBAIRIEST, '')               + '|' +
                            		  ''                                      + '|' +
                            		  ''                                      + '|' +
                            		  ISNULL(IE.NRTELEIEST, '')               + '|' +
                            		  ''                                      + '|' +
                             		  ''                          + '|'  ) ARQ_0005
                     		 FROM EMPRESA EP, INSCRESTAD IE
                     		 WHERE IE.CDEMPRESA  = @P_CDEMPRESA  AND
                          	       IE.CDINSCESTA = @P_CDINSCESTA AND
                           	       IE.CDEMPRESA  = EP.CDEMPRESA     ) 
   OPEN C_REG0005;

   FETCH NEXT FROM C_REG0005 INTO @V_ARQ_0005;

   WHILE (@@FETCH_STATUS = 0) BEGIN 
       
      SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('02'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0005, @P_NRSEQARQMAG);
      SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;

      FETCH NEXT FROM C_REG0005 INTO @V_ARQ_0005;
   END;

   CLOSE C_REG0005;
   DEALLOCATE C_REG0005;

   IF @P_IDCENTRAL IN ('1', '2') BEGIN
      DECLARE C_REG0010 CURSOR FOR (SELECT ('|0010'                                               + '|' +
	                                    ISNULL(A.NRINSJURIEST, '')                            + '|' +
             		                    ''                                                    + '|' +
                          		    ISNULL(A.SGESTADO, '')                                + '|' +
                                	    ISNULL(A.CDINSCESTA, '')                              + '|' +
                               		    ISNULL(RIGHT(REPLICATE('0', 7)+ C.CDMUNICIBGE,7), '') + '|' +
                               		    ISNULL(IM.CDINSCMUNI, '')                             + '|'  ) AS ARQ_0010
                        	   FROM
                           		INSCRESTAD A, MUNICIPIO C, FILIAL E, INSCRMUNICIP IM
                        	   WHERE
                           		A.CDEMPRESA   = @P_CDEMPRESA   AND
                           		A.CDINSCESTA  = @P_CDINSCESTA  AND
                           		A.CDPAIS      = C.CDPAIS       AND
                           		A.SGESTADO    = C.SGESTADO     AND
                          		A.CDMUNICIPIO = C.CDMUNICIPIO  AND
                           		A.CDINSCESTA  = E.CDINSCESTA   AND
                           		E.CDEMPRESA   = IM.CDEMPRESA   AND
                           		E.CDINSCMUNI  = IM.CDINSCMUNI )
      OPEN C_REG0010;
      FETCH NEXT FROM C_REG0010 INTO @V_ARQ_0010;

      WHILE (@@FETCH_STATUS = 0) BEGIN 
       
    	  SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
      	  INSERT INTO ARQMAGNETICO
        	(NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
	        ('03'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0010, @P_NRSEQARQMAG);
          SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;

          FETCH NEXT FROM C_REG0010 INTO @V_ARQ_0010;
      END;

      CLOSE C_REG0010;
      DEALLOCATE C_REG0010;

   END;  -- if

   DECLARE C_REG0100 CURSOR FOR (SELECT ('|0100'                                               + '|' +
		                         ISNULL(NMCONTADOR, '')                                + '|' +
                		         ''                                                    + '|' +
                            		 ISNULL(NRCPFRESPONS, '')                              + '|' +
                            		 ISNULL(REPLACE(REPLACE(NRCRCCONT2,'/',''),'-',''), '')+ '|' +
                            		 ISNULL(SGESTADO, '')                                  + '|' +
                            		 ISNULL(REPLACE(REPLACE(NRCEP,'/',''),'-',''), '')     + '|' +
                            		 ISNULL(DSLOGRADOURO, '')                              + '|' +
                            		 ''                                                    + '|' +
                            		 ISNULL(DSCOMPLLOGRA, '')                              + '|' +
                            		 ISNULL(DSBAIRRO, '')                                  + '|' +
                            		 ''                                                    + '|' +
                            		 ''                                                    + '|' +
                            		 ISNULL(NRTELCONTATO, '')                              + '|' +
                            		 ISNULL(NRFAXCONTATO, '')                              + '|' +
                            		 ISNULL(DSENDINTERN, '')                               + '|'  ) ARQ_0100
                    		 FROM CONTADOR) 
   OPEN C_REG0100;

   FETCH NEXT FROM C_REG0100 INTO @V_ARQ_0100;

   WHILE (@@FETCH_STATUS = 0) BEGIN 
      SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
      
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('04'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0100, @P_NRSEQARQMAG);
      SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
      FETCH NEXT FROM C_REG0100 INTO @V_ARQ_0100;
   END;

   CLOSE C_REG0100;
   DEALLOCATE C_REG0100;

   DECLARE C_REG0125 CURSOR FOR (SELECT  ('|0125'                 + '|' +
		                          'Teknisa Software Ltda' + '|' +
                	                  '26269316000177'        + '|' +
                            		  ''                      + '|' +
                           		  '21222300'              + '|' +
                            		  ''                      + '|' +
                            		  ''                      + '|'  ) AS ARQ_0125) 
   
   OPEN C_REG0125;

   FETCH NEXT FROM C_REG0125 INTO @V_ARQ_0125;

   WHILE (@@FETCH_STATUS = 0) BEGIN 
       
    	SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
      	INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
        VALUES
         ('05'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0125, @P_NRSEQARQMAG);
        SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;

        FETCH NEXT FROM C_REG0125 INTO @V_ARQ_0125;
   END;
   CLOSE C_REG0125;
   DEALLOCATE C_REG0125;

   DECLARE C_REG0150 CURSOR FOR (SELECT DISTINCT ('|0150'                         + '|' +       
	                                          ISNULL(CODIGO, '')              + '|' +
            		                          ISNULL(NMRAZAO, '')             + '|' +
                        		          ISNULL(CDPAIS, '')              + '|' +
          		                          ISNULL(CNPJ1, '')               + '|' +
                        		          ISNULL(CPF, '')                 + '|' +
           		                          ISNULL(CEI, '')                 + '|' +
                        		          ISNULL(NIT, '')                 + '|' +
       		                                  ISNULL(ESTADO, '')              + '|' +
                   		                  ISNULL(INSCESTA, '')            + '|' +
            		                          ISNULL(INSCESTST, '')           + '|' +
                        		          ISNULL(MUNICIPIO, '')           + '|' +
    		                                  ISNULL(INSCMUNI, '')            + '|' +
                	                          ISNULL(SUFRAMA, '')             + '|' ) ARQ_0150,  
                            		          CNPJ
                   		 FROM(SELECT DISTINCT ISNULL(FI.CDFILIAL, '')       AS CODIGO,
                                		      ISNULL(FI.NMFILIAL, '')       AS NMRAZAO,
                                 		      ISNULL(PA.CDPAISEXPFIS, '')   AS CDPAIS,
                             			      ISNULL(FI.NRINSJURFILI, '')   AS CNPJ1,
      		                                      ''             		    AS CPF,
                       			              ''                	    AS CEI,
      			                              ''           		    AS NIT,
                        		              ISNULL(EF.SGESTADO, '')       AS ESTADO,
         		                              ISNULL(FI.NRINSJURFILI, '')   AS INSCESTA,
                          		              ''                            AS INSCESTST,
		                                      ISNULL(RIGHT(REPLICATE('0', 7)+ISNULL(M.CDMUNICIBGE,'0'),7), '') AS MUNICIPIO, 
                 		                      ''                            AS INSCMUNI,
                                 		      ''                            AS SUFRAMA,
                                   	              ISNULL(FI.CDFILIAL, '')       AS CNPJ
                     		    FROM FILIAL FI, ENDEFILI EF, MUNICIPIO M, PAIS PA,
                          	   (SELECT DISTINCT CASE N.IDENTRSAIDOP WHEN 'E' THEN N.CDFILIEMIT ELSE N.CDFILIRECE END AS CDFILIAL, 
                                   		         N.IDORIGDESTNF
                          	    FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
        	                    WHERE M.CDEMPRESA       = @P_CDEMPRESA             AND
                 	                  M.CDINSCESTA      = @P_CDINSCESTA            AND
                          	          M.DTINIPERIODO    = @P_DTINICIAL             AND
   		                          M.DTFIMPERIODO    = @P_DTFINAL               AND
                	                  M.CDIMPOSTO       = @P_CDIMPOSTO             AND
                           		  N.IDORIGDESTNF    =  'L'                     AND
   	                                  I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU           AND
            		                  I.CDIMPOSTO       = @P_CDIMPOSTO             AND
  	                                  N.CDMODDOCFISC    NOT IN ('02', '03')        AND
           		                  N.CDFILIAL        = I.CDFILIAL               AND
                       		          N.NRLANCTONF      = I.NRLANCTONF             AND
                                	 ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E')) )  NF 
                	     WHERE FI.CDFILIAL       = NF.CDFILIAL                     AND
                          	   EF.CDFILIAL    = FI.CDFILIAL                        AND
                                   ISNULL(EF.IDTPENDEFILI,'P') = 'P'                   AND
	                           EF.CDPAIS      = M.CDPAIS                           AND
        	                   EF.CDMUNICIPIO = M.CDMUNICIPIO                      AND
                	           EF.CDPAIS      = PA.CDPAIS
                    	    UNION
                    	   /* issqn */
                    	    SELECT DISTINCT ISNULL(FI.CDFILIAL, '')                        AS CODIGO,
                           	            ISNULL(FI.NMFILIAL, '')                        AS NMRAZAO,
                                  	    ISNULL(PA.CDPAISEXPFIS, '')                    AS CDPAIS,
	                                    ISNULL(FI.NRINSJURFILI, '')                    AS CNPJ1,
         	                            ''                                             AS CPF,
                	                    ''                                             AS CEI,
                                            ''                                             AS NIT,
	                                    ISNULL(EF.SGESTADO, '')                        AS ESTADO,
        	                            ISNULL(FI.NRINSJURFILI, '')                    AS INSCESTA,
      	         	                    ''                                             AS INSCESTST,
        	                            ISNULL(RIGHT(REPLICATE('0', 7)+ISNULL(M.CDMUNICIBGE,'0'),7), '') AS MUNICIPIO,
                	                    ''                                             AS INSCMUNI,
                        	            ''                                             AS SUFRAMA,
                                	    ISNULL(FI.CDFILIAL, '') AS CNPJ
                     	   FROM FILIAL FI, ENDEFILI EF, MUNICIPIO M, PAIS PA,
                   	        (SELECT DISTINCT CASE N.IDENTRSAIDOP WHEN 'E' THEN N.CDFILIEMIT ELSE N.CDFILIRECE END AS CDFILIAL, 
                                           N.IDORIGDESTNF
                        	 FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                          	 WHERE M.CDEMPRESA       = @P_CDEMPRESA             AND
                                       M.CDINSCMUNI      = @P_CDINSCESTA            AND
           	                       M.DTINIPERIODO    = @P_DTINICIAL             AND
                                       M.DTFIMPERIODO    = @P_DTFINAL               AND
                     	               M.CDIMPOSTO       IN ('30','31','42')        AND
                          	       N.IDORIGDESTNF    =  'L'                     AND
  	                               I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU           AND
        	                       I.CDIMPOSTO       IN ('30','31','42')        AND
                	               N.CDMODDOCFISC    IN ('01','03')             AND
                        	       N.CDFILIAL        = I.CDFILIAL               AND
                                       N.NRLANCTONF      = I.NRLANCTONF             AND 
                                       ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E')) )  NF 
                     	   WHERE FI.CDFILIAL       = NF.CDFILIAL                    AND
                           	 EF.CDFILIAL    = FI.CDFILIAL                       AND
                                 ISNULL(EF.IDTPENDEFILI,'P') = 'P'                  AND
            	                 EF.CDPAIS      = M.CDPAIS                          AND
                  	         EF.SGESTADO    = M.SGESTADO                        AND
                         	 EF.CDMUNICIPIO = M.CDMUNICIPIO                     AND
                         	 EF.CDPAIS      = PA.CDPAIS
                    	  UNION
                     	  SELECT DISTINCT ISNULL(CL.CDCLIENTE, '')                                       AS CODIGO,
                          	          ISNULL(CL.NMRAZSOCCLIE, '')                                    AS NMRAZAO,
                                	  ISNULL(PA.CDPAISEXPFIS, '')                                    AS CDPAIS,
     	                                  CASE CL.IDTPIJURCLIE WHEN 'J' THEN CL.NRINSJURCLIE ELSE '' END AS CNPJ1,
           	                          CASE CL.IDTPIJURCLIE WHEN 'F' THEN CL.NRINSJURCLIE ELSE '' END AS CPF,
               	 	                  ''                                                             AS CEI,
                        	          ''                                                             AS NIT,
                                	  ISNULL(EC.SGESTADO, '')                                                    AS ESTADO,
 	                                  ISNULL(CL.NRINSESTCLIE, '')                                               AS INSCESTA,
        	                          ''                                                             AS INSCESTST,
                	                  ISNULL(RIGHT(REPLICATE('0', 7)+ISNULL(M.CDMUNICIBGE,'0'),7), '')           AS MUNICIPIO,
                        	          ''                                                             AS INSCMUNI,
                                	  ''                                                             AS SUFRAMA,
                          	          ISNULL(CL.CDCLIENTE, '')                                                   AS CNPJ
                  	 FROM CLIENTE CL, ENDECLIE EC, MUNICIPIO M, PAIS PA,
                              (SELECT DISTINCT N.CDCLIENTE,
                                               N.IDORIGDESTNF       
                               FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                               WHERE M.CDEMPRESA       = @P_CDEMPRESA             AND
                               	     M.CDINSCESTA      = @P_CDINSCESTA            AND
                                     M.DTINIPERIODO    = @P_DTINICIAL             AND
                                     M.DTFIMPERIODO    = @P_DTFINAL               AND
                                     M.CDIMPOSTO       = @P_CDIMPOSTO             AND
                                     I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU           AND
                                     I.CDIMPOSTO       = @P_CDIMPOSTO             AND
                                     N.CDFILIAL        = I.CDFILIAL               AND
                                     N.IDORIGDESTNF    =  'C'                     AND
                                     N.CDMODDOCFISC    NOT IN ('02', '03')        AND
                                     N.NRLANCTONF      = I.NRLANCTONF             AND
                                     ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E')) )  NF 
                   	WHERE CL.CDCLIENTE       = NF.CDCLIENTE                   AND
                              EC.CDCLIENTE    = CL.CDCLIENTE                      AND
                              EC.IDTPENDECLIE = ISNULL(CL.IDENDEMISCLI,'P')       AND
                              EC.CDPAIS          = M.CDPAIS                       AND
                              EC.SGESTADO        = M.SGESTADO                     AND
                              EC.CDMUNICIPIO     = M.CDMUNICIPIO                  AND
                              EC.CDPAIS          = PA.CDPAIS
                        UNION
                        /* issqn */
                        SELECT DISTINCT ISNULL(CL.CDCLIENTE, '')                                                   AS CODIGO,
                                        ISNULL(CL.NMRAZSOCCLIE, '')                             		       AS NMRAZAO,
                                        ISNULL(PA.CDPAISEXPFIS, '')                              		       AS CDPAIS,
                                        CASE CL.IDTPIJURCLIE WHEN 'J' THEN CL.NRINSJURCLIE ELSE '' END AS CNPJ1,
                                        CASE CL.IDTPIJURCLIE WHEN 'F' THEN CL.NRINSJURCLIE ELSE '' END AS CPF,
                                        ''                                           		       AS CEI,
                                        ''                                             		       AS NIT,
                                        ISNULL(EC.SGESTADO, '')                                	       AS ESTADO,
                                        ISNULL(CL.NRINSESTCLIE, '')                              		       AS INSCESTA,
                                        ''                                                             AS INSCESTST,
                                        ISNULL(RIGHT(REPLICATE('0',7)+ISNULL(M.CDMUNICIBGE,'0'),7), '')            AS MUNICIPIO,
                                        ''                                                             AS INSCMUNI,
                                        ''							       AS SUFRAMA,
                                        ISNULL(CL.CDCLIENTE, '') 						       AS CNPJ
                       FROM CLIENTE CL, ENDECLIE EC, MUNICIPIO M, PAIS PA,
                            (SELECT DISTINCT N.CDCLIENTE, N.IDORIGDESTNF  
                             FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                             WHERE M.CDEMPRESA       = @P_CDEMPRESA             AND
                                   M.CDINSCMUNI      = @P_CDINSCESTA            AND
                                   M.DTINIPERIODO    = @P_DTINICIAL             AND
                                   M.DTFIMPERIODO    = @P_DTFINAL               AND
                                   M.CDIMPOSTO       IN ('30','31','42')        AND
                                   I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU           AND
                                   I.CDIMPOSTO       IN ('30','31','42')        AND
                                   N.CDFILIAL        = I.CDFILIAL               AND
                                   N.IDORIGDESTNF    =  'C'                     AND
                                   N.CDMODDOCFISC    IN ('01','03')             AND
                                   N.NRLANCTONF      = I.NRLANCTONF             AND
                                   ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E')) )  NF 
                       WHERE CL.CDCLIENTE       = NF.CDCLIENTE                  AND
                             EC.CDCLIENTE    = CL.CDCLIENTE                     AND
                             EC.IDTPENDECLIE = ISNULL(CL.IDENDEMISCLI,'P')      AND
                             EC.CDPAIS          = M.CDPAIS                      AND
                             EC.SGESTADO        = M.SGESTADO                    AND
                             EC.CDMUNICIPIO     = M.CDMUNICIPIO                 AND
                             EC.CDPAIS          = PA.CDPAIS
                       UNION
                       SELECT DISTINCT ISNULL(FO.CDFORNECED, '')                                                  AS CODIGO,
                                       ISNULL(FO.NMRAZSOCFORN, '')                                                AS NMRAZAO,
                                       ISNULL(PA.CDPAISEXPFIS, '')                                  	      AS CDPAIS,
                                       CASE FO.IDTPIJURFORN WHEN 'J' THEN FO.NRINSJURFORN ELSE '' END AS CNPJ1,
                                       CASE FO.IDTPIJURFORN WHEN 'F' THEN FO.NRINSJURFORN ELSE '' END AS CPF,
                                       ''                                           		      AS CEI,
                                       ''                                            		      AS NIT,
                                       ISNULL(EF.SGESTADO, '')                                     		      AS ESTADO,
                                       ISNULL(FO.NRINSESTFORN, '')                                 	              AS INSCESTA,
                                       ''                                               	      AS INSCESTST,
                                       ISNULL(RIGHT(REPLICATE('0', 7)+ISNULL(M.CDMUNICIBGE,'0'),7), '')           AS MUNICIPIO ,
                                       ISNULL(REPLACE(REPLACE(FO.NRINSMUNFORN,'-',''),'/',''), '') 		      AS INSCMUNI,
                                       ''                                                             AS SUFRAMA,
                                       ISNULL(FO.CDFORNECED, '')						      AS CNPJ
                      FROM FORNECEDOR FO, ENDEFORN EF, MUNICIPIO M, PAIS PA,
                           (SELECT DISTINCT N.CDFORNECED, N.IDORIGDESTNF 
                            FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                            WHERE M.CDEMPRESA       = @P_CDEMPRESA             AND
                                  M.CDINSCESTA      = @P_CDINSCESTA            AND
                                  M.DTINIPERIODO    = @P_DTINICIAL             AND
                                  M.DTFIMPERIODO    = @P_DTFINAL               AND
                                  M.CDIMPOSTO       = @P_CDIMPOSTO             AND
                                  I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU           AND
                                  I.CDIMPOSTO       = @P_CDIMPOSTO             AND
                                  N.CDMODDOCFISC    NOT IN ('02', '03')        AND
                                  N.CDFILIAL        = I.CDFILIAL               AND
                                  N.NRLANCTONF      = I.NRLANCTONF             AND
                                  N.IDORIGDESTNF    =  'F'                     AND
                                  ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E')) )  NF 
                      WHERE FO.CDFORNECED      = NF.CDFORNECED                 AND
                            EF.CDFORNECED   = FO.CDFORNECED                    AND
                            EF.IDTPENDEFORN = ISNULL(FO.IDENDEMISFOR,'P')      AND
                            EF.CDPAIS          = M.CDPAIS                      AND
                            EF.SGESTADO        = M.SGESTADO                    AND
                            EF.CDMUNICIPIO     = M.CDMUNICIPIO                 AND
                            EF.CDPAIS          = PA.CDPAIS
                      UNION
                     /* issqn */
                      SELECT DISTINCT ISNULL(FO.CDFORNECED, '')                                       AS CODIGO,
                                      ISNULL(FO.NMRAZSOCFORN, '')                                     AS NMRAZAO,
                                      ISNULL(PA.CDPAISEXPFIS, '')                                     AS CDPAIS,
                                      CASE FO.IDTPIJURFORN WHEN 'J' THEN FO.NRINSJURFORN ELSE '' END  AS CNPJ1,
                                      CASE FO.IDTPIJURFORN WHEN 'F' THEN FO.NRINSJURFORN ELSE '' END  AS CPF,
                                      ''                                                              AS CEI,
                                      ''                                                              AS NIT,
                                      ISNULL(EF.SGESTADO, '')                                         AS ESTADO,
                                      ISNULL(FO.NRINSESTFORN, '')                                     AS INSCESTA,
                                      ''                                                              AS INSCESTST,
                                      ISNULL(RIGHT(REPLICATE('0', 7)+ISNULL(M.CDMUNICIBGE,'0'),7), '')AS MUNICIPIO ,
                                      ISNULL(REPLACE(REPLACE(FO.NRINSMUNFORN,'-',''),'/',''), '')     AS INSCMUNI,
                                      ''                                                              AS SUFRAMA,
                                      ISNULL(FO.CDFORNECED, '') 				      AS CNPJ
                      FROM FORNECEDOR FO, ENDEFORN EF, MUNICIPIO M, PAIS PA,
                           (SELECT DISTINCT N.CDFORNECED, N.IDORIGDESTNF 
                            FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I
                            WHERE M.CDEMPRESA       = @P_CDEMPRESA             AND
                                  M.CDINSCMUNI      = @P_CDINSCESTA            AND
                                  M.DTINIPERIODO    = @P_DTINICIAL             AND
                                  M.DTFIMPERIODO    = @P_DTFINAL               AND
                                  M.CDIMPOSTO       IN ('30','31','42')        AND
                                  I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU           AND
                                  I.CDIMPOSTO       IN ('30','31','42')        AND
                                  N.CDMODDOCFISC    IN ('01','03')             AND
                                  N.CDFILIAL        = I.CDFILIAL               AND
                                  N.NRLANCTONF      = I.NRLANCTONF             AND
                                  N.IDORIGDESTNF    =  'F'                     AND
                                  ((N.IDSTATUS = 'A') OR (N.IDSTATUS = 'E')) )  NF 
                      WHERE FO.CDFORNECED      = NF.CDFORNECED                 AND
                            EF.CDFORNECED   = FO.CDFORNECED                    AND
                            EF.IDTPENDEFORN = ISNULL(FO.IDENDEMISFOR,'P')      AND
                            EF.CDPAIS          = M.CDPAIS                      AND
                            EF.SGESTADO        = M.SGESTADO                    AND
                            EF.CDMUNICIPIO     = M.CDMUNICIPIO                 AND
                            EF.CDPAIS          = PA.CDPAIS
                     ) ORIG ) ORDER BY ORIG.CNPJ
   OPEN C_REG0150;

   FETCH NEXT FROM C_REG0150 INTO @V_ARQ_0150, @V_CNPJ_0150;

   WHILE (@@FETCH_STATUS = 0) BEGIN
      /* busca tipo de origem F(fornecedor), C(cliente), L(filial) */     
         IF @V_CODIGO IS NOT NULL BEGIN
            SET @V_ORIGEM = 'F';
         END;

         SELECT @V_CODIGO = CDCLIENTE
         FROM CLIENTE
         WHERE CDCLIENTE = @V_CNPJ_0150;

         IF @V_CODIGO IS NOT NULL BEGIN
           SET @V_ORIGEM = 'C';
         END
         ELSE BEGIN
           SET @V_ORIGEM = 'L';
         END;

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('06'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0150, @P_NRSEQARQMAG);
         SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;         

         IF @V_ORIGEM = 'C' BEGIN
           DECLARE C_REG0175 CURSOR FOR (SELECT DISTINCT ('|0175'                                		     + '|' +
                                                           ISNULL(REPLACE(REPLACE(EF.NRCEPFILI,'-',''),'.',''), '')  + '|' +
               		   	                           ISNULL(EF.DSENDEFILI, '')                                 + '|' +
                                			   ''                                        		     + '|' +
       			                                   ISNULL(EF.DSCOMPENDFIL, '')               		     + '|' +
                        		                   ISNULL(EF.NMBAIRFILI, '')               		     + '|' +
                                        		   ''                             			     + '|' +
       			                                   ''                            			     + '|' +
                        		                   ISNULL(REPLACE(REPLACE(EF.NRTELEFILI,'-',''),'.',''), '') + '|' + 
                                        		   ISNULL(REPLACE(REPLACE(EF.NRFAXFILI, '-',''),'.',''), '') + '|'   ) ARQ_0175
                          		 FROM  FILIAL FI, ENDEFILI EF
                           		 WHERE FI.CDFILIAL    = @V_CNPJ_0150                     AND
                             		       EF.CDFILIAL = FI.CDFILIAL                         AND
   		                               ISNULL(EF.IDTPENDEFILI,'P') = 'P'                 AND
                 		               EF.SGESTADO = 'DF'                                AND
                                	       ((EF.DSENDEFILI IS NOT NULL) OR (EF.NMBAIRFILI IS NOT NULL) OR (EF.NRCEPFILI  IS NOT NULL)) )
	    OPEN C_REG0175;
 	
            FETCH NEXT FROM C_REG0175 INTO @V_ARQ_0175;

            WHILE (@@FETCH_STATUS = 0) BEGIN

               INSERT INTO ARQMAGNETICO
                (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                ('06'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8),@V_ARQ_0175, @P_NRSEQARQMAG);
               SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;

               FETCH NEXT FROM C_REG0175 INTO @V_ARQ_0175;
        END;
        CLOSE C_REG0175;
        DEALLOCATE C_REG0175;
    
      END
      ELSE 
      IF @V_ORIGEM = 'F' BEGIN
         DECLARE C_REG0175 CURSOR FOR (SELECT DISTINCT ('|0175'                                        + '|' +
		                                         ISNULL(REPLACE(REPLACE(EC.NRCEPCLIE,'-',''),'.',''), '')  + '|' +
                		                         ISNULL(EC.DSENDECLIE, '')                		       + '|' +
                                		         ''                           		       + '|' +
	                                                 ISNULL(EC.DSCOMPENDCLI, '')              		       + '|' +
             		                                 ISNULL(EC.NMBAIRCLIE, '')               		       + '|' +
                          		                 ''                            		       + '|' +
                                        		 ''                              	       + '|' +
 		                                         ISNULL(REPLACE(REPLACE(EC.NRTELECLIE,'-',''),'.',''), '') + '|' +
                		                         ISNULL(REPLACE(REPLACE(EC.NRFAXCLIE,'-',''),'.',''), '')  + '|'  ) ARQ_0175
	                               FROM CLIENTE CL, ENDECLIE EC
            		               WHERE CL.CDCLIENTE       = @V_CNPJ_0150                 AND
                        		     EC.CDCLIENTE    = CL.CDCLIENTE                    AND
  		                             EC.IDTPENDECLIE = ISNULL(CL.IDENDEMISCLI,'P')     AND
                 		             EC.SGESTADO = 'DF'                                AND
                               		     ((EC.DSENDECLIE IS NOT NULL) OR (EC.NMBAIRCLIE IS NOT NULL) OR (EC.NRCEPCLIE IS NOT NULL)) ) 
         OPEN C_REG0175;

         FETCH NEXT FROM C_REG0175 INTO @V_ARQ_0175;
   
         WHILE (@@FETCH_STATUS = 0) BEGIN 
           
            SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
            INSERT INTO ARQMAGNETICO
             (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
             ('06'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0175, @P_NRSEQARQMAG);
            SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;

            FETCH NEXT FROM C_REG0175 INTO @V_ARQ_0175;
        END;
        CLOSE C_REG0175;
        DEALLOCATE C_REG0175;

     END
     ELSE IF @V_ORIGEM = 'L' BEGIN
         DECLARE  C_REG0175 CURSOR FOR (SELECT DISTINCT ('|0175'                                       + '|' +
        	                                         ISNULL(REPLACE(REPLACE(EF.NRCEPFORN,'-',''),'.',''), '')  + '|' +
	                                                 ISNULL(EF.DSENDEFORN, '')                                 + '|' +
                                                         ''                                            + '|' +
           		                                 ISNULL(EF.DSCOMPENDFOR, '')                               + '|' +
                        		                 ISNULL(EF.NMBAIRFORN, '')                                 + '|' +
                                        		 ''                                            + '|' +
    		                                         ''                                            + '|' +
                 		                         ISNULL(REPLACE(REPLACE(EF.NRTELEFORN,'-',''),'.',''), '') + '|' +
                                		         ISNULL(REPLACE(REPLACE(EF.NRFAXFORN,'-',''),'.',''), '')  + '|'  ) AS ARQ_0175
                     		        FROM FORNECEDOR FO, ENDEFORN EF
                         		WHERE FO.CDFORNECED     = @V_CNPJ_0150                  AND
   		                              EF.CDFORNECED     = FO.CDFORNECED                 AND
                		              EF.IDTPENDEFORN   = ISNULL(FO.IDENDEMISFOR,'P')   AND
 		                              EF.SGESTADO = 'DF'                                AND
                		             ((EF.DSENDEFORN IS NOT NULL) OR (EF.NMBAIRFORN IS NOT NULL) OR (EF.NRCEPFORN IS NOT NULL)) ) 
                     
        OPEN C_REG0175;

        FETCH NEXT FROM C_REG0175 INTO @V_ARQ_0175;

        WHILE (@@FETCH_STATUS = 0) BEGIN 
       
    	 SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
      	 INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('06'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0175, @P_NRSEQARQMAG);
         SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;

         FETCH NEXT FROM C_REG0175 INTO @V_ARQ_0175;
       END;
       CLOSE C_REG0175;
       DEALLOCATE C_REG_0175;
     END;

     FETCH NEXT FROM C_REG0150 INTO @V_ARQ_0150, @V_CNPJ_0150;  

   END;
   CLOSE C_REG0150;
   DEALLOCATE C_REG0150;

   DECLARE C_REG0200 CURSOR FOR SELECT DISTINCT ('|0200'                  + '|' +
                               		         ISNULL(PR.CDARVPROD, '') + '|' +
	                                  	 ISNULL(PR.NMPRODUTO, '') + '|' +
                                  	         ISNULL(PR.CDGENPROD, '') + '|' +
 		                                 ''           + '|'  ) AS ARQ_0200,
                	                         PR.CDARVPROD
                    		FROM (SELECT PR.CDARVPROD AS CDARVPROD,
                                	     PR.NMPRODUTO AS NMPRODUTO,
                              		     PR.CDGENPROD AS CDGENPROD
                           	      FROM NOTAFISCAL N, ITEMPRODUTO I, PRODUTO PR,
                               		   IMPOSTONF IM, MAPAAPURAC M
                           	      WHERE N.CDFILIAL        = I.CDFILIAL        AND
                                	    N.NRLANCTONF      = I.NRLANCTONF      AND
     		                            N.CDFILIAL        = IM.CDFILIAL       AND
                	                    N.NRLANCTONF      = IM.NRLANCTONF     AND
                           	            I.CDPRODUTO       = PR.CDPRODUTO      AND
 	                                    M.NRSEQUMAPAPU    = IM.NRSEQUMAPAPU   AND
            		                    M.CDEMPRESA       = @P_CDEMPRESA      AND
                        	            M.CDINSCESTA      = @P_CDINSCESTA     AND
	                                    M.DTINIPERIODO    = @P_DTINICIAL      AND
           	 	                    M.DTFIMPERIODO    = @P_DTFINAL        AND
                        	            M.CDIMPOSTO       = @P_CDIMPOSTO      AND
		                            IM.CDIMPOSTO      = @P_CDIMPOSTO      AND
                                            N.CDMODDOCFISC    IN ('01','02','04') AND
 		                            EXISTS
                      		           (SELECT IP.CDIMPOSTO
                                	    FROM ITEMPROIMPOS IP
                  		            WHERE IP.CDFILIAL    = I.CDFILIAL    AND
                                	          IP.NRLANCTONF  = I.NRLANCTONF  AND
      		                                  IP.NRSEQITPROD = I.NRSEQITPROD AND
                		                  IP.CDIMPOSTO   = IM.CDIMPOSTO)
      			                    UNION
                         		    SELECT PR.CDARVPROD AS CDARVPROD,
		                                   PR.NMPRODUTO AS NMPRODUTO,
                 		                   PR.CDGENPROD AS CDGENPROD
		                            FROM INVENTFISC I, PRODUTO PR
                 		            WHERE I.CDEMPRESA    = @P_CDEMPRESA     AND
 		                                  I.CDINSCESTA   = @P_CDINSCESTA    AND
                  		                  I.DTINVEFISCAL = @P_DTFINAL       AND
     			                          I.CDPRODUTO    = PR.CDPRODUTO     AND
                        		          I.QTESTINVFISC <> 0) PR
                   			    ORDER BY PR.CDARVPROD ASC 
   OPEN C_REG0200;

   FETCH NEXT FROM C_REG0200 INTO @V_ARQ_0200, @V_CDARVPROD;

   WHILE (@@FETCH_STATUS = 0) BEGIN 
      SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;
      INSERT INTO ARQMAGNETICO 
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
           ('08'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0200, @P_NRSEQARQMAG);
      SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;

      FETCH NEXT FROM C_REG0200 INTO @V_ARQ_0200, @V_CDARVPROD;
   END;
   CLOSE C_REG0200;
   DEALLOCATE C_REG0200;

   DECLARE C_REG0400 CURSOR FOR (SELECT DISTINCT('|0400' 	   + '|' +
                 	                         ISNULL(CDFISCOPER, '')        + '|' +
                             		         ISNULL(MAX(DSCDFISCOPER), '') + '|'   ) ARQ_0400
                   		   FROM   CODFISCOPER
                    		 GROUP BY CDFISCOPER ) 
   OPEN C_REG0400;

   FETCH NEXT FROM C_REG0400 INTO @V_ARQ_0400;
 
   WHILE (@@FETCH_STATUS = 0) BEGIN       
     INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
     VALUES
           ('09'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), @V_ARQ_0400, @P_NRSEQARQMAG);
     SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;   
     FETCH NEXT FROM C_REG0400 INTO @V_ARQ_0400;    
   END;
   CLOSE C_REG0400;
   DEALLOCATE C_REG0400;

   /* ENCERRAMENTO DO BLOCO 0 */
   SET @V_CONTREGA = ISNULL(@V_CONTREGA,0) + 1;

   INSERT INTO ARQMAGNETICO
     (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
     ('10'+RIGHT(REPLICATE('0', 8)+ RTRIM(@V_CONTREGA), 8), '|0990'+ '|' +CONVERT(VARCHAR, @V_CONTREGA)+ '|' , @P_NRSEQARQMAG);
 
   SET @P_RETURN = @V_CONTREGA;
END
GO 

-- VERSAO --
EXEC ATUVEROBJ 'ARQDIGITAL_INICIO','5.05.008';
GO

