CREATE OR REPLACE FUNCTION GIA_RS_X05B(
   P_CDEMPRESA      IN EMPRESA.CDEMPRESA%TYPE,
   P_CDIMPOSTO      IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDINSCESTA     IN INSCRESTAD.CDINSCESTA%TYPE,
   P_CDLIVROMAG     IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   P_DTINIPERIODO   IN MAPAAPURAC.DTINIPERIODO%TYPE,
   P_DTFIMPERIODO   IN MAPAAPURAC.DTINIPERIODO%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.B - DETALHAMENTO SAIDAS OUTRAS*******************************************/
BEGIN
   V_QUANTIDADE:=0;
   V_SEQUENCIA:=P_SEQUENCIA;
   V_REG_1:= P_REG_1;
   FOR C_REG_VB 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,
                          'X05B'                                                         ||
                          '01'                                                           ||
                          T.CDFISCOPERIT                                                 ||
                          LPAD(NVL(T.CDDETALHEPROD,'0'),3,'0')                           ||
                          REPLACE(LTRIM(TO_CHAR(SUM(T.VRCONTSAI),'00000000000.00')),'.')      AS CORPO
                   FROM (SELECT IT.CDFISCOPERIT, IT.CDDETALHEPROD,
                                SUM((CASE WHEN C.CDLAYOUTLMAG = '003' THEN ROUND(IT.VRCONTITIMP-DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0)-DECODE(IT.CDFISCALIT,'2',IT.VRCONTITIMP,0),2) ELSE
                                    (CASE WHEN C.CDLAYOUTLMAG IN ('002','005') THEN ROUND(DECODE(IT.CDFISCALIT,'3',IT.VRCONTITIMP,0),2) ELSE 0
                                    END)END)) AS VRCONTSAI
                          FROM ITEMPROIMPOS IT, ITEMPRODUTO IP, IMPOSTO IM,  CFOPLIVROMAG C,
                               (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                  FROM IMPOSTONF I, NOTAFISCAL N, IMPOSTO P
                                 WHERE I.NRSEQUMAPAPU  = P_NRSEQUMAPAPU   AND
                                       I.NRLANCTONF    = N.NRLANCTONF     AND
                                       I.CDFILIAL      = N.CDFILIAL       AND
                                       N.CDEMPRESA     = P_CDEMPRESA      AND
                                       N.CDINSCESTA    = P_CDINSCESTA     AND
                                       I.CDIMPOSTO     = P.CDIMPOSTO      AND
                                       P.CDIMPOSTONFE  = P_CDIMPOSTO      AND
                                       N.IDENTRSAIDOP  = 'S') NF
                         WHERE NF.CDFILIAL     = IT.CDFILIAL
                           AND NF.NRLANCTONF   = IT.NRLANCTONF
                           AND IT.CDFILIAL     = IP.CDFILIAL
                           AND IT.NRLANCTONF   = IP.NRLANCTONF
                           AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                           AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                           AND IM.CDIMPOSTONFE = P_CDIMPOSTO
                           AND IT.CDFISCOPERIT  = C.CDCFOPLMAG
                           AND C.CDLIVROMAG    = P_CDLIVROMAG
                           AND IT.CDFISCOPERIT NOT IN ('5601','5602','5605')
                           AND ((IT.NRITPROIMPOS <> '3') OR (IT.NRITPROIMPOS IS NULL))
                         GROUP BY IT.CDFISCOPERIT, IT.CDDETALHEPROD
                         ORDER BY IT.CDFISCOPERIT, IT.CDDETALHEPROD)T
                   WHERE T.VRCONTSAI > 0
                   GROUP BY T.CDFISCOPERIT, T.CDDETALHEPROD
                   ORDER BY T.CDFISCOPERIT, T.CDDETALHEPROD)LOOP

         V_SEQUENCIA:= V_SEQUENCIA+1;

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ,DSREGARQMAG,NRSEQARQMAG)
         VALUES(LTRIM(TO_CHAR(V_SEQUENCIA,'0000000000')),C_REG_VB.CABECALHO||LTRIM(TO_CHAR(V_SEQUENCIA,'0000'))||
                C_REG_VB.CORPO,P_NRSEQUENCIAL);

         V_QUANTIDADE:=V_QUANTIDADE+1;

   END LOOP;
   /**************************************CONTADOR ANEXO ABC*******************************************/
   V_REG_1:=V_REG_1||LTRIM(TO_CHAR(V_QUANTIDADE,'000'));
   RETURN(V_REG_1);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('GIA_RS_X05B','6.39.005'); END;
/
