CREATE OR REPLACE FUNCTION GIA_RS_X05C(
   P_CDEMPRESA      IN EMPRESA.CDEMPRESA%TYPE,
   P_CDIMPOSTO      IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDINSCESTA     IN INSCRESTAD.CDINSCESTA%TYPE,
   P_DTINIPERIODO   IN MAPAAPURAC.DTINIPERIODO%TYPE,
   P_DTFIMPERIODO   IN MAPAAPURAC.DTINIPERIODO%TYPE,
   P_CDLIVROMAG     IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_VERSAO         IN IMPOSTO.CDIMPOSTO%TYPE,
   P_NRSEQUENCIAL   IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_SEQUENCIA      IN NUMBER,
   P_REG_1          IN VARCHAR2,
   P_NRSEQUMAPAPU   IN MAPAAPURAC.NRSEQUMAPAPU%TYPE)RETURN VARCHAR2
AS
   V_SEQUENCIA  NUMBER;
   V_QUANTIDADE NUMBER;
   V_REG_1      VARCHAR2(1000);

/**********************************ANEXO V - DISCRIMINACAO DAS SAIDAS******************************/
BEGIN
   V_QUANTIDADE:= 0;
   V_SEQUENCIA:= P_SEQUENCIA;
   V_REG_1:= P_REG_1;
   FOR C_REG_SAI IN(SELECT '****'                                                           ||
                           P_VERSAO                                                         ||
                           DECODE(P_VERSAO,'07','00000000','')                              ||/*VERSAO 07*/
                           DECODE(P_VERSAO,'07',LPAD(' ',1,' '),'')                         ||/*VERSAO 07*/
                           LTRIM(TO_CHAR(P_DTINIPERIODO,'DD'))                              ||
                           LTRIM(TO_CHAR(P_DTFIMPERIODO,'DD'))                              ||
                           LTRIM(TO_CHAR(P_DTFIMPERIODO,'MMYYYY'))                          ||
                           LPAD(P_CDINSCESTA,10,'0') As CABECALHO,
                           'X05C'                                                           ||
                           '01'                                                             ||
                           T.CDFISCOPERNF                                                   ||
                           LPAD(T.CDAJUSTE,3,'0')                                           ||
                           REPLACE(LTRIM(TO_CHAR(SUM(T.VRIMPORTEXCL),'00000000000.00')),'.')||
                           LPAD(NVL(T.ESPECIFICACAO,' '),60,' ') AS CORPO
                     FROM (/*Preencher Excluidas com diferenca Valor Contabil - Valores Fiscais  - Excluidas parcialmente*/
                           /*Requer cadastro de cfops, tela FIS07300, em layout codigo 005 - CFOP - EXCLUIDAS VALOR PARCIAL E 002 - EXCLUIDAS INTEGRALMENTE*/
                           /*MOTIVO AJUSTE ---> 001 - ST*/
                           SELECT A.CDFISCOPERNF,
                                  A.CDAJUSTE,
                                  A.VRIMPORTEXCL AS VRIMPORTEXCL,
                                  A.ESPECIFICACAO
                             FROM (SELECT I.CDFISCOPERNF,
                                          CA.CDAJUSTE,
                                          SUM(ROUND(NVL(VALOR_IMPOSTO_NFE(N.CDFILIAL, N.NRLANCTONF,'80','I','1'),0),2) * 0.699) AS VRIMPORTEXCL,
                                          '' AS ESPECIFICACAO
                                     FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P, CFOPLIVROMAG C, CODAJUSTECFOP CA
                                    WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                AND
                                          I.NRLANCTONF   = N.NRLANCTONF                  AND
                                          I.CDFILIAL     = N.CDFILIAL                    AND
                                          N.CDEMPRESA    = P_CDEMPRESA                   AND
                                          I.CDIMPOSTO    = P.CDIMPOSTO                   AND
                                          P.CDIMPOSTONFE = P_CDIMPOSTO                   AND
                                          N.CDINSCESTA   = P_CDINSCESTA                  AND
                                          N.IDENTRSAIDOP = 'S'                           AND
                                          I.CDFISCOPERNF = C.CDCFOPLMAG                  AND
                                          C.CDLIVROMAG   = P_CDLIVROMAG                  AND
                                          C.CDLAYOUTLMAG IN ('002','005')                AND
                                          CA.CDFISCOPERNF = I.CDFISCOPERNF               AND
                                          CA.CDAJUSTE    = '001'                         AND
                                          ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                                    GROUP BY I.CDFISCOPERNF, CA.CDAJUSTE) A
                            WHERE A.VRIMPORTEXCL > 0
                            /*MOTIVO AJUSTE ---> 002 - IPI*/
                            UNION ALL
                           SELECT A.CDFISCOPERNF,
                                  A.CDAJUSTE,
                                  A.VRIMPORTEXCL AS VRIMPORTEXCL,
                                  A.ESPECIFICACAO
                             FROM (SELECT I.CDFISCOPERNF,
                                          CA.CDAJUSTE,
                                          SUM(ROUND(NVL(VALOR_IMPOSTO_NFE(N.CDFILIAL, N.NRLANCTONF,'10','I','1'),0),2) * 0.699) AS VRIMPORTEXCL,
                                          '' AS ESPECIFICACAO
                                     FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P, CFOPLIVROMAG C, CODAJUSTECFOP CA
                                    WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                AND
                                          I.NRLANCTONF   = N.NRLANCTONF                  AND
                                          I.CDFILIAL     = N.CDFILIAL                    AND
                                          N.CDEMPRESA    = P_CDEMPRESA                   AND
                                          I.CDIMPOSTO    = P.CDIMPOSTO                   AND
                                          P.CDIMPOSTONFE = P_CDIMPOSTO                   AND
                                          N.CDINSCESTA   = P_CDINSCESTA                  AND
                                          N.IDENTRSAIDOP = 'S'                           AND
                                          I.CDFISCOPERNF = C.CDCFOPLMAG                  AND
                                          C.CDLIVROMAG   = P_CDLIVROMAG                  AND
                                          C.CDLAYOUTLMAG IN ('002','005')                AND
                                          CA.CDFISCOPERNF = I.CDFISCOPERNF               AND
                                          CA.CDAJUSTE    = '002'                         AND
                                          I.CDFISCOPERNF NOT IN ('5601','5602','5605')   AND
                                          ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                                    GROUP BY I.CDFISCOPERNF, CA.CDAJUSTE ) A
                            WHERE A.VRIMPORTEXCL > 0
                           /*MOTIVO AJUSTE ---> 003 - FRETE*/
                            UNION ALL
                           SELECT A.CDFISCOPERNF,
                                  A.CDAJUSTE,
                                  A.VRIMPORTEXCL,
                                  A.ESPECIFICACAO
                             FROM (SELECT I.CDFISCOPERNF,
                                          CA.CDAJUSTE,
                                          SUM(NVL(VALOR_ENCARGO_NFE(N.CDFILIAL, N.NRLANCTONF, 'A','01'),0)) AS VRIMPORTEXCL,
                                          '' AS ESPECIFICACAO
                                     FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P, CFOPLIVROMAG C, CODAJUSTECFOP CA
                                    WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                AND
                                          I.NRLANCTONF   = N.NRLANCTONF                  AND
                                          I.CDFILIAL     = N.CDFILIAL                    AND
                                          N.CDEMPRESA    = P_CDEMPRESA                   AND
                                          I.CDIMPOSTO    = P.CDIMPOSTO                   AND
                                          P.CDIMPOSTONFE = P_CDIMPOSTO                   AND
                                          N.CDINSCESTA   = P_CDINSCESTA                  AND
                                          N.IDENTRSAIDOP = 'S'                           AND
                                          I.CDFISCOPERNF = C.CDCFOPLMAG                  AND
                                          C.CDLIVROMAG   = P_CDLIVROMAG                  AND
                                          C.CDLAYOUTLMAG IN ('002','005')                AND
                                          CA.CDFISCOPERNF = I.CDFISCOPERNF               AND
                                          CA.CDAJUSTE    = '003'                         AND
                                          I.CDFISCOPERNF NOT IN ('5601','5602','5605')   AND
                                          ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                                    GROUP BY I.CDFISCOPERNF, CA.CDAJUSTE ) A
                            WHERE A.VRIMPORTEXCL > 0
                           /*MOTIVO AJUSTE ---> 004 - VALOR CONTABIL MENOR QUE A BASE DE CALCULO - VALOR DA DIFERENCA*/
                            UNION ALL
                           SELECT A.CDFISCOPERNF,
                                  A.CDAJUSTE,
                                  A.VRIMPORTEXCL,
                                  A.ESPECIFICACAO
                             FROM (SELECT I.CDFISCOPERNF,
                                          CA.CDAJUSTE,
                                          SUM(NVL(ROUND(I.VRBASECALCNF,2)-ROUND(I.VRCONTABILNF,2),0)) AS VRIMPORTEXCL,
                                          '' AS ESPECIFICACAO
                                     FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P, CFOPLIVROMAG C, CODAJUSTECFOP CA
                                    WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                AND
                                          I.NRLANCTONF   = N.NRLANCTONF                  AND
                                          I.CDFILIAL     = N.CDFILIAL                    AND
                                          N.CDEMPRESA    = P_CDEMPRESA                   AND
                                          I.CDIMPOSTO    = P.CDIMPOSTO                   AND
                                          P.CDIMPOSTONFE = P_CDIMPOSTO                   AND
                                          N.CDINSCESTA   = P_CDINSCESTA                  AND
                                          N.IDENTRSAIDOP = 'S'                           AND
                                          I.CDFISCOPERNF = C.CDCFOPLMAG                  AND
                                          C.CDLIVROMAG   = P_CDLIVROMAG                  AND
                                          C.CDLAYOUTLMAG IN ('002','005')                AND
                                          (ROUND(I.VRBASECALCNF,2) - ROUND(I.VRCONTABILNF,2)) > 0 AND
                                          CA.CDFISCOPERNF = I.CDFISCOPERNF               AND
                                          CA.CDAJUSTE    = '004'                         AND
                                          I.CDFISCOPERNF NOT IN ('5601','5602','5605')   AND
                                          ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                                    GROUP BY I.CDFISCOPERNF, CA.CDAJUSTE) A
                            WHERE A.VRIMPORTEXCL > 0
                           /*MOTIVO AJUSTE ---> 005 SOMA DAS COLUNAS BC/IS.N.TRIB/OUTRAS (PREENCHIMENTO AUTOMATICO)*/
                            UNION ALL
                           SELECT A.CDFISCOPERNF,
                                  A.CDAJUSTE,
                                  A.VRIMPORTEXCL,
                                  A.ESPECIFICACAO
                             FROM (SELECT I.CDFISCOPERNF,
                                          CA.CDAJUSTE,
                                          SUM(ROUND(I.VRBASECALCNF+DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)+DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2)) AS VRIMPORTEXCL,
                                          C.DSESPECIFICACAO AS ESPECIFICACAO
                                     FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P, CFOPLIVROMAG C, CODAJUSTECFOP CA
                                    WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                AND
                                          I.NRLANCTONF   = N.NRLANCTONF                  AND
                                          I.CDFILIAL     = N.CDFILIAL                    AND
                                          N.CDEMPRESA    = P_CDEMPRESA                   AND
                                          I.CDIMPOSTO    = P.CDIMPOSTO                   AND
                                          P.CDIMPOSTONFE = P_CDIMPOSTO                   AND
                                          N.CDINSCESTA   = P_CDINSCESTA                  AND
                                          N.IDENTRSAIDOP = 'S'                           AND
                                          I.CDFISCOPERNF = C.CDCFOPLMAG                  AND
                                          C.CDLIVROMAG   = P_CDLIVROMAG                  AND
                                          C.CDLAYOUTLMAG IN ('002','005')                AND
                                          CA.CDFISCOPERNF = I.CDFISCOPERNF               AND
                                          CA.CDAJUSTE    = '005'                         AND
                                          I.CDFISCOPERNF NOT IN ('5601','5602','5605')   AND
                                         ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                                   GROUP BY I.CDFISCOPERNF, CA.CDAJUSTE, C.DSESPECIFICACAO ) A
                            WHERE A.VRIMPORTEXCL > 0
                           /*MOTIVO AJUSTE ---> 006 - EXCLUSOES PARCIAIS*/
                            UNION ALL
                           SELECT A.CDFISCOPERNF,
                                  A.CDAJUSTE,
                                  A.VRIMPORTEXCL,
                                  A.ESPECIFICACAO
                             FROM (SELECT I.CDFISCOPERNF,
                                          CA.CDAJUSTE,
                                          SUM(ROUND(I.VRCONTABILNF-I.VRBASECALCNF-DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)-DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2)) AS VRIMPORTEXCL,
                                          C.DSESPECIFICACAO AS ESPECIFICACAO
                                     FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P, CFOPLIVROMAG C, CODAJUSTECFOP CA
                                    WHERE I.NRSEQUMAPAPU = P_NRSEQUMAPAPU                AND
                                          I.NRLANCTONF   = N.NRLANCTONF                  AND
                                          I.CDFILIAL     = N.CDFILIAL                    AND
                                          N.CDEMPRESA    = P_CDEMPRESA                   AND
                                          I.CDIMPOSTO    = P.CDIMPOSTO                   AND
                                          P.CDIMPOSTONFE = P_CDIMPOSTO                   AND
                                          N.CDINSCESTA   = P_CDINSCESTA                  AND
                                          N.IDENTRSAIDOP = 'S'                           AND
                                          I.CDFISCOPERNF = C.CDCFOPLMAG                  AND
                                          C.CDLIVROMAG   = P_CDLIVROMAG                  AND
                                          C.CDLAYOUTLMAG IN ('002','005')                AND
                                          CA.CDFISCOPERNF = I.CDFISCOPERNF               AND
                                          CA.CDAJUSTE    = '006'                         AND
                                          I.CDFISCOPERNF NOT IN ('5601','5602','5605')   AND
                                          ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                                    GROUP BY I.CDFISCOPERNF, CA.CDAJUSTE, C.DSESPECIFICACAO ) A
                            WHERE A.VRIMPORTEXCL > 0 )T
                    GROUP BY T.CDFISCOPERNF,T.CDAJUSTE, T.ESPECIFICACAO
                    ORDER BY T.CDFISCOPERNF)LOOP

         V_SEQUENCIA:= V_SEQUENCIA+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
         VALUES(LTRIM(TO_CHAR(V_SEQUENCIA,'0000000000')),C_REG_SAI.CABECALHO||LTRIM(TO_CHAR(V_SEQUENCIA,'0000'))||
                C_REG_SAI.CORPO,P_NRSEQUENCIAL);
         V_QUANTIDADE:= V_QUANTIDADE+1;
   END LOOP;

   /*******CONTADOR REGISTRO ABC ********************************/
   V_REG_1:=V_REG_1||LTRIM(TO_CHAR(V_QUANTIDADE,'000'));

  RETURN(V_REG_1);

END;
/