CREATE OR REPLACE FUNCTION ARQUIVO_GIA_MS_02(
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA  IN NOTAFISCAL.CDINSCESTA%TYPE,
   P_CDIMPOSTO   IN IMPOSTO.CDIMPOSTO%TYPE,
   P_DTINICIAL   IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_TPDEC       IN VARCHAR,
   P_PDEC        IN VARCHAR,
   P_CONTADOR    IN VARCHAR,
   P_SEQMAGNET   IN VARCHAR)
RETURN VARCHAR
AS
   V_MESINI    VARCHAR2(2);
   V_CONTADOR  NUMBER;
   V_INSERT    VARCHAR2(100);
BEGIN
   V_CONTADOR := NVL(P_CONTADOR,0);

   IF P_PDEC = '2' THEN
      V_MESINI := '00';
   ELSE
      V_MESINI := TO_CHAR(P_DTINICIAL,'MM');
   END IF;


   V_INSERT := SUBSTR(RPAD(REPLACE(REPLACE(REPLACE(NVL(P_CDINSCESTA, ' '),'.',''),'-',''),'ISENT','ISENTO'),9,' '),1,9)
               ||TO_CHAR(P_DTINICIAL,'YYYY')
               ||V_MESINI
               ||TO_CHAR(P_DTFINAL,'MM')
               ||P_TPDEC
               ||'02';


   FOR C_REGISTRO_02 IN(
      SELECT LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRNORMDEBI,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VROUTRDEBI,0)),2),'9999999999990.00')),'.',''),15,0) AS PARTE_1,
             LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRESTOCRED,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRTOTADEBI,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRNORMCRED,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRSLDCRANTE,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VROUTRCRED,0)),2),'9999999999990.00')),'.',''),15,0) 
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRESTODEBI,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRTOTACRED,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(CASE WHEN (R.VRTOTADEBI-R.VRTOTACRED) < 0 THEN 0 ELSE (R.VRTOTADEBI-R.VRTOTACRED) END ,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRSALDOCRED,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRDEDUCAO,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(M.VRTOTAIMPOS,0)),2),'9999999999990.00')),'.',''),15,0)
             ||LPAD(REPLACE(LTRIM(TO_CHAR(ROUND(SUM(NVL(R.VRSALDOCRED,0)),2),'9999999999990.00')),'.',''),15,0) AS PARTE_2
        FROM MAPAAPURAC M, MAPARECUP R
       WHERE M.CDEMPRESA = P_CDEMPRESA
         AND M.CDINSCESTA = P_CDINSCESTA
         AND M.CDIMPOSTO = P_CDIMPOSTO
         AND M.DTINIPERIODO >= P_DTINICIAL
         AND M.DTFIMPERIODO <= P_DTFINAL
         AND M.NRSEQUMAPAPU = R.NRSEQUMAPAPU ) LOOP

      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
      (LPAD(V_CONTADOR, 10, '0'),V_INSERT||C_REGISTRO_02.PARTE_1||LPAD(0,15,0)||C_REGISTRO_02.PARTE_2||LPAD(0,15,0),P_SEQMAGNET);

   END LOOP;
RETURN (TO_CHAR(LPAD(V_CONTADOR, 10, '0')));

END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_GIA_MS_02','5.30.000'); END;
/

