CREATE OR REPLACE FUNCTION GIA_RS_X05(
   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,
                           'X05 '                                                                ||
                           '01'                                                                  ||
                           T.CDFISCOPERNF                                                        ||
                           REPLACE(LTRIM(TO_CHAR(SUM(T.VRCONTSAI),'00000000000.00')),'.')        ||
                           REPLACE(LTRIM(TO_CHAR(SUM(T.VRBASESAI),'00000000000.00')),'.')        ||
                           REPLACE(LTRIM(TO_CHAR(SUM(T.VRIMPOSTOSAI),'00000000000.00')),'.')     ||
                           REPLACE(LTRIM(TO_CHAR(SUM(T.VRISENTOSAI),'00000000000.00')),'.')      ||
                           REPLACE(LTRIM(TO_CHAR(SUM(DECODE(T.VROUTRASSAI,ABS(T.VROUTRASSAI),T.VROUTRASSAI,0)),'00000000000.00')),'.')      ||
                           REPLACE(LTRIM(TO_CHAR(SUM(DECODE(T.VRIMPORTEXCL,ABS(T.VRIMPORTEXCL),T.VRIMPORTEXCL,0)),'00000000000000.00')),'.') AS CORPO
                     FROM (/*Preencher Excluidas com diferenca Valor Contabil - Valores Fiscais PARCIAL*/
                           /*Requer cadastro de cfops, tela FIS07300, em layout codigo 005 - CFOP - EXCLUIDAS VALOR PARCIAL*/
                           SELECT I.CDFISCOPERNF,
                                  SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTSAI,
                                  SUM(ROUND(I.VRBASECALCNF,2)) AS VRBASESAI,
                                  SUM(ROUND(I.VRIMPOSTONF,2))  AS VRIMPOSTOSAI,
                                  SUM(ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0),2)) AS  VRISENTOSAI,
                                  SUM(ROUND(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2)) AS  VROUTRASSAI,
                                  SUM(ROUND(I.VRCONTABILNF-I.VRBASECALCNF-DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)-DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2)) AS VRIMPORTEXCL,
                                  N.CDFILIAL, N.NRLANCTONF
                             FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P, CFOPLIVROMAG C
                            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 = '005'                         AND
                                  I.CDFISCOPERNF NOT IN ('5601','5602','5605')   AND
                                  ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                            GROUP BY I.CDFISCOPERNF, N.IDENTRSAIDOP, N.CDFILIAL, N.NRLANCTONF
                           UNION ALL
                           /*Preencher apenas Valor Contabil e Excluidas*/
                           /*Requer cadastro de cfops, tela FIS07300, em layout codigo 002 - CFOP - EXCLUIDAS VALOR INTEGRAL*/
                           SELECT I.CDFISCOPERNF,
                                  SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTSAI,
                                  SUM(ROUND(I.VRBASECALCNF,2)) AS VRBASESAI,
                                  SUM(ROUND(I.VRIMPOSTONF,2)) AS  VRIMPOSTOSAI,
                                  SUM(ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0),2)) AS  VRISENTOSAI,
                                  SUM(ROUND(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2)) AS  VROUTRASSAI,
                                  SUM(ROUND(I.VRBASECALCNF,2)+ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0),2)+ROUND(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0),2)) AS VRIMPORTEXCL,
                                  N.CDFILIAL, N.NRLANCTONF
                             FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P, CFOPLIVROMAG C
                            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 = '002'                         AND
                                  I.CDFISCOPERNF NOT IN ('5601','5602','5605')   AND
                                  ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                            GROUP BY I.CDFISCOPERNF, N.IDENTRSAIDOP, N.CDFILIAL, N.NRLANCTONF
                           UNION ALL
                           /*Nao preencher Excluidas - Jogar diferenca para coluna outras*/
                           /*Requer cadastro de cfops, tela FIS07300, em layout codigo 003 - CFOP - NAO PREENCHER EXCLUIDAS*/
                           SELECT I.CDFISCOPERNF,
                                  SUM(ROUND(I.VRCONTABILNF,2)) AS VRCONTSAI,
                                  SUM(ROUND(I.VRBASECALCNF,2)) AS VRBASESAI,
                                  SUM(ROUND(I.VRIMPOSTONF,2))  AS VRIMPOSTOSAI,
                                  SUM(ROUND(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0),2)) AS  VRISENTOSAI,
                                  SUM(ROUND(I.VRCONTABILNF-I.VRBASECALCNF-DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0),2)) AS  VROUTRASSAI,
                                  0 AS VRIMPORTEXCL, N.CDFILIAL, N.NRLANCTONF
                             FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P, CFOPLIVROMAG C
                            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 = '003'                         AND
                                  I.CDFISCOPERNF NOT IN ('5601','5602','5605')   AND
                                  ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                           GROUP BY I.CDFISCOPERNF, N.IDENTRSAIDOP,N.CDFILIAL, N.NRLANCTONF
                           UNION ALL
                           /*SELECT PARA SOMAR OS VALORES DOS COD. AJUSTE 1,2,3,4 DO ANEXO V.C*/
                           SELECT I.CDFISCOPERNF,
                                  0 AS VRCONTSAI,
                                  0 AS VRBASESAI,
                                  0 AS  VRIMPOSTOSAI,
                                  0 AS  VRISENTOSAI,
                                  0 AS  VROUTRASSAI,
                                  SUM((CASE WHEN CA.CDAJUSTE = '001' THEN (NVL(VALOR_IMPOSTO_NFE(N.CDFILIAL, N.NRLANCTONF,'80','I','1'),0)*0.699) ELSE
                                           (CASE WHEN CA.CDAJUSTE = '002' THEN (NVL(VALOR_IMPOSTO_NFE(N.CDFILIAL, N.NRLANCTONF,'10','I','1'),0)*0.699) ELSE
                                           (CASE WHEN CA.CDAJUSTE = '003' THEN (NVL(VALOR_ENCARGO_NFE(N.CDFILIAL, N.NRLANCTONF, 'A','01'),0)*0.699)    ELSE
                                           (CASE WHEN CA.CDAJUSTE = '004' THEN (CASE WHEN (ROUND(I.VRBASECALCNF,2) - ROUND(I.VRCONTABILNF,2)) > 0
                                                                                     THEN NVL(ROUND(I.VRBASECALCNF,2)-ROUND(I.VRCONTABILNF,2),0) ELSE 0 END) ELSE 0
                                            END)END)END)END)) AS VRIMPORTEXCL,
                                  N.CDFILIAL, N.NRLANCTONF
                             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.CDAJUSTE    IN ('001','002','003','004')    AND
                                  I.CDFISCOPERNF NOT IN ('5601','5602','5605')   AND
                                  I.CDFISCOPERNF = CA.CDFISCOPERNF(+)            AND
                                  ((I.IDIMPOSTONF <> '3') OR (I.IDIMPOSTONF IS NULL))
                            GROUP BY I.CDFISCOPERNF, N.IDENTRSAIDOP,N.CDFILIAL, N.NRLANCTONF)T
                    GROUP BY T.CDFISCOPERNF
                    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;
/