CREATE OR REPLACE FUNCTION ARQUIVO_DMA_CS (
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE, 
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE, 
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE, 
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_CDLIVROMAG   IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER 
AS 
   V_CONTREGCS    NUMBER;
   V_NRSEQUMAPAPU VARCHAR2(10);
   V_VRBASECALCENTR FLOAT;
   V_VRISENTASENTR  FLOAT;
   V_VROUTRASENTR   FLOAT;
   V_VRCONTABILSAID FLOAT;
   V_VRBASECALC     FLOAT;
   V_VRISENTAS      FLOAT;
   V_VROUTRAS       FLOAT;
   ARQ_REGENTRADA   VARCHAR2(100);
BEGIN 
   V_CONTREGCS := 0; 
   
   SELECT MAX(NRSEQUMAPAPU)
     INTO V_NRSEQUMAPAPU
     FROM MAPAAPURAC
    WHERE CDEMPRESA    = P_CDEMPRESA
      AND CDIMPOSTO    = P_CDIMPOSTO
      AND CDINSCESTA   = P_CDINSCESTA
      AND DTINIPERIODO = P_DTINICIAL
      AND DTFIMPERIODO = P_DTFINAL;

   BEGIN
      SELECT SUM(DECODE(NF.IDENTRSAIDOP,'E',DECODE(I.CDFISCALNF, 1, ROUND(I.VRBASECALCNF,2), 0),0)) AS VRBASECALC,
             SUM(DECODE(NF.IDENTRSAIDOP,'E',DECODE(I.CDFISCALNF, 2, ROUND(I.VRCONTABILNF,2), 0),0)) AS VRISENTAS,
             SUM(DECODE(NF.IDENTRSAIDOP,'E',ROUND(I.VRCONTABILNF,2) - DECODE(I.CDFISCALNF,1,ROUND(I.VRBASECALCNF,2),0) - DECODE(I.CDFISCALNF,2,ROUND(I.VRCONTABILNF,2),0),0)) AS VROUTRAS,
             SUM(DECODE(NF.IDENTRSAIDOP,'S',I.VRCONTABILNF,0)) AS VRSAIDAS
        INTO V_VRBASECALCENTR, V_VRISENTASENTR, V_VROUTRASENTR, V_VRCONTABILSAID
        FROM IMPOSTONF I, NOTAFISCAL NF, IMPOSTO IM
       WHERE I.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
         AND I.CDIMPOSTO     = IM.CDIMPOSTO
         AND IM.CDIMPOSTONFE = P_CDIMPOSTO
         AND I.CDFISCOPERNF  = RETORNA_CFOP_CS_DMA(P_CDLIVROMAG,I.CDFISCOPERNF,NF.IDENTRSAIDOP)
         AND I.CDFILIAL      = NF.CDFILIAL
         AND I.NRLANCTONF    = NF.NRLANCTONF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_VRBASECALCENTR := 0;
         V_VRISENTASENTR  := 0;
         V_VROUTRASENTR   := 0;
         V_VRCONTABILSAID := 0;
   END;

   IF V_VRCONTABILSAID > 0 THEN
      FOR C_REGSAIDA IN (SELECT ('31'
                                ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                                ||LPAD(P_CDINSCESTA, 9, '0')
                                ||'S'
                                ||LPAD(NVL(VS.CDMUNIEXPO,'0'), 5, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(VS.VRBASECALCSAID), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(VS.VRISENTASSAID), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(VS.VROUTRASSAID), '999999999.00'), '.', '')), 12, '0')) AS ARQ_REGSAIDA,
                                SUM(VS.VRCONTABILNF) AS VRCONTABIL, LPAD(NVL(VS.CDMUNIEXPO,'0'), 5, '0') AS CDMUNIEXPO
                           FROM (SELECT NVL(MU.CDMUNIEXPO,MU.CDMUNICIPIO) AS CDMUNIEXPO,
                                        SUM(DECODE(I.CDFISCALNF, 1, ROUND(I.VRBASECALCNF,2), 0)) AS VRBASECALCSAID,
                                        SUM(DECODE(I.CDFISCALNF, 2, ROUND(I.VRCONTABILNF,2), 0)) AS VRISENTASSAID,
                                        SUM(ROUND(I.VRCONTABILNF,2) - DECODE(I.CDFISCALNF,1,ROUND(I.VRBASECALCNF,2),0) - DECODE(I.CDFISCALNF,2,ROUND(I.VRCONTABILNF,2),0)) AS VROUTRASSAID,
                                        SUM(I.VRCONTABILNF) AS VRCONTABILNF
                                   FROM IMPOSTONF I, IMPOSTO IM, NOTAFISCAL NF, ENDEFILI EF , MUNICIPIO MU
                                  WHERE I.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                                    AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                    AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                    AND I.CDFISCOPERNF  = RETORNA_CFOP_CS_DMA(P_CDLIVROMAG,I.CDFISCOPERNF,'S')
                                    AND I.CDFILIAL      = NF.CDFILIAL
                                    AND I.NRLANCTONF    = NF.NRLANCTONF
                                    AND NF.IDENTRSAIDOP = 'S'
                                    AND EF.CDFILIAL     = NF.CDFILIEMIT
                                    AND EF.CDPAIS       = MU.CDPAIS
                                    AND EF.SGESTADO     = MU.SGESTADO
                                    AND EF.CDMUNICIPIO  = MU.CDMUNICIPIO
                                    AND NVL(EF.IDTPENDEFILI,'P') = 'P'
                                  GROUP BY NVL(MU.CDMUNIEXPO,MU.CDMUNICIPIO)) VS
                          GROUP BY VS.CDMUNIEXPO) LOOP

         V_VRBASECALC := ROUND(C_REGSAIDA.VRCONTABIL/V_VRCONTABILSAID * V_VRBASECALCENTR,2);
         V_VRISENTAS  := ROUND(C_REGSAIDA.VRCONTABIL/V_VRCONTABILSAID * V_VRISENTASENTR,2);
         V_VROUTRAS   := ROUND(C_REGSAIDA.VRCONTABIL/V_VRCONTABILSAID * V_VROUTRASENTR,2);

         ARQ_REGENTRADA := '30' || TO_CHAR(P_DTINICIAL, 'YYYYMM') || LPAD(P_CDINSCESTA, 9, '0') || 'E' || C_REGSAIDA.CDMUNIEXPO ||
                           LPAD(LTRIM(REPLACE(TO_CHAR(V_VRBASECALC, '9999999999.00'), '.', '')), 12, '0')||
                           LPAD(LTRIM(REPLACE(TO_CHAR(V_VRISENTAS,  '9999999999.00'), '.', '')), 12, '0')||
                           LPAD(LTRIM(REPLACE(TO_CHAR(V_VROUTRAS,   '9999999999.00'), '.', '')), 12, '0');

         V_CONTREGCS := NVL(V_CONTREGCS, 0)+1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('1'||LPAD(V_CONTREGCS,9,'0'), ARQ_REGENTRADA, P_NRSEQARQMAG);

         V_CONTREGCS := NVL(V_CONTREGCS, 0)+1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('2'||LPAD(V_CONTREGCS,9,'0'), C_REGSAIDA.ARQ_REGSAIDA, P_NRSEQARQMAG);
      END LOOP;
   ELSE
      FOR C_REGENTRADA IN (SELECT ('30'
                                ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                                ||LPAD(P_CDINSCESTA, 9, '0')
                                ||'E'
                                ||LPAD(NVL(VE.CDMUNIEXPO,'0'), 5, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(VE.VRBASECALC), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(VE.VRISENTAS), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(VE.VROUTRAS), '999999999.00'), '.', '')), 12, '0')) AS ARQ_REGENTRADA,
                                SUM(VE.VRCONTABILNF) AS VRCONTABIL, LPAD(NVL(VE.CDMUNIEXPO,'0'), 5, '0') AS CDMUNIEXPO
                           FROM (SELECT NVL(MU.CDMUNIEXPO,MU.CDMUNICIPIO) AS CDMUNIEXPO,
                                        SUM(DECODE(I.CDFISCALNF, 1, ROUND(I.VRBASECALCNF,2), 0)) AS VRBASECALC,
                                        SUM(DECODE(I.CDFISCALNF, 2, ROUND(I.VRCONTABILNF,2), 0)) AS VRISENTAS,
                                        SUM(ROUND(I.VRCONTABILNF,2) - DECODE(I.CDFISCALNF,1,ROUND(I.VRBASECALCNF,2),0) - DECODE(I.CDFISCALNF,2,ROUND(I.VRCONTABILNF,2),0)) AS VROUTRAS,
                                        SUM(I.VRCONTABILNF) AS VRCONTABILNF
                                   FROM IMPOSTONF I, IMPOSTO IM, NOTAFISCAL NF, ENDEFILI EF , MUNICIPIO MU
                                  WHERE I.NRSEQUMAPAPU  = V_NRSEQUMAPAPU
                                    AND I.CDIMPOSTO     = IM.CDIMPOSTO
                                    AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                                    AND I.CDFISCOPERNF  = RETORNA_CFOP_CS_DMA(P_CDLIVROMAG,I.CDFISCOPERNF,'E')
                                    AND I.CDFILIAL      = NF.CDFILIAL
                                    AND I.NRLANCTONF    = NF.NRLANCTONF
                                    AND NF.IDENTRSAIDOP = 'E'
                                    AND EF.CDFILIAL     = NF.CDFILIEMIT
                                    AND EF.CDPAIS       = MU.CDPAIS
                                    AND EF.SGESTADO     = MU.SGESTADO
                                    AND EF.CDMUNICIPIO  = MU.CDMUNICIPIO
                                    AND NVL(EF.IDTPENDEFILI,'P') = 'P'
                                  GROUP BY NVL(MU.CDMUNIEXPO,MU.CDMUNICIPIO)) VE
                          GROUP BY VE.CDMUNIEXPO) LOOP
                          
         V_CONTREGCS := NVL(V_CONTREGCS, 0)+1;
         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('1'||LPAD(V_CONTREGCS,9,'0'), C_REGENTRADA.ARQ_REGENTRADA, P_NRSEQARQMAG);
      END LOOP;
   END IF;
   RETURN(V_CONTREGCS);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DMA_CS','6.49.017'); END;
/
