CREATE OR REPLACE PROCEDURE ARQMESTRE_SERVEMIT_435(
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_NRINSJURFILI IN FILIAL.NRINSJURFILI%TYPE,
   P_CDIMPOSTO    IN IMPOSTONF.CDIMPOSTO%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTEMISSAO%TYPE,
   P_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE)
AS
   V_SEQUNCIAL NUMBER;
   V_TOTDESC   NUMBER;
   V_NOTA      VARCHAR(50);
   V_SEQ_NOTA  NUMBER;
   V_PART      VARCHAR(50);
   V_SEQ_PART  NUMBER;
   V_ITEMSERVICO  VARCHAR(250);
BEGIN

   V_SEQ_NOTA := 0;
   V_SEQ_PART := 0;

   SELECT NVL(MAX(CDCONTREGI),0)
     INTO V_SEQ_NOTA
     FROM TMP_REGDADOS_IN86
    WHERE CDREGISTRO = '433';

   SELECT NVL(MAX(CDCONTREGI),0)
     INTO V_SEQ_PART
     FROM TMP_REGDADOS_IN86
    WHERE CDREGISTRO = '491';

   FOR C_NOTAS_SERV IN(SELECT N.CDFILIAL, N.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC, N.DTEMISSAO, N.NRINSJURNF, N.VRNOTAFISC, DECODE(N.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS,
                              DECODE(N.IDORIGDESTNF,'F',F.CDFORNECED,DECODE(N.IDORIGDESTNF,'C',C.CDCLIENTE,L.CDFILIAL)) AS COD_PART,
                              SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.IDENTRSAIDOP, N.IDORIGDESTNF, 
                              LPAD(NVL(SUBSTR(VALOR_IMPOSTO_NFE(N.CDFILIAL,N.NRLANCTONF,'22','A') * 100, 1, 5), 0), 5, 0) AS VRALIQNF,
                              LPAD(NVL(SUBSTR(VALOR_IMPOSTO_NFE(N.CDFILIAL,N.NRLANCTONF,'22','B') * 100, 1, 17), 0), 17, 0) AS VRBASE_IRRF,
                              LPAD(NVL(SUBSTR(VALOR_IMPOSTO_NFE(N.CDFILIAL,N.NRLANCTONF,'22','I') * 100, 1, 17), 0), 17, 0) AS VR_IRRF,
                              RPAD(NVL(N.CDSERIE, ' '), 5, ' ')||SUBSTR(LPAD(N.NRNOTAFISC,20,'0'),12,9)||RPAD(TO_CHAR(N.DTEMISSAO, 'DDMMYYYY'), 8, ' ') AS ID_REG
                         FROM IMPOSTONFE E, NOTAFISCAL N, IMPOSTONF I, IMPOSTO P, FILIAL A,
                              FORNECEDOR F, CLIENTE C, FILIAL L
                        WHERE E.CDIMPOSTONFE  IN ('30','31','32') /*31-ISS_PREST, 32-ISS_RET_PREST*/
                          AND N.CDEMPRESA    = P_CDEMPRESA
                          AND N.CDFILIEMIT   = A.CDFILIAL
                          AND A.NRINSJURFILI = P_NRINSJURFILI
                          AND (((E.IDFORMAPURNFE = 'D') AND (N.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL)) OR
                               ((E.IDFORMAPURNFE = 'E') AND (N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL)) OR
                               (N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL))
                          AND N.IDSTATUS    <> 'P'
                          AND N.IDENTRSAIDOP = 'S'
                          AND N.CDMODDOCFISC <> '99'
                          AND N.CDFILIAL     = I.CDFILIAL
                          AND N.NRLANCTONF   = I.NRLANCTONF
                          AND P.CDIMPOSTO    = I.CDIMPOSTO
                          AND P.CDIMPOSTONFE = E.CDIMPOSTONFE
                          AND N.CDFORNECED   = F.CDFORNECED(+)
                          AND N.CDCLIENTE    = C.CDCLIENTE(+)
                          AND N.CDFILIRECE   = L.CDFILIAL(+)
                        GROUP BY N.CDFILIAL, N.NRLANCTONF, N.CDSERIE, N.NRNOTAFISC, N.DTEMISSAO, N.NRINSJURNF, N.VRNOTAFISC, N.IDSTATUS,
                                 DECODE(N.IDORIGDESTNF,'F',F.CDFORNECED,DECODE(N.IDORIGDESTNF,'C',C.CDCLIENTE,L.CDFILIAL)), N.IDENTRSAIDOP, N.IDORIGDESTNF) LOOP

      V_TOTDESC := 0;
      FOR C_ENCARGODESC IN(SELECT I.CDENCARGO , E.IDENCARGO
                             FROM ITEMENCARGO I, ENCARGO E
                            WHERE I.NRLANCTONF = C_NOTAS_SERV.NRLANCTONF
                              AND I.CDFILIAL   = C_NOTAS_SERV.CDFILIAL
                              AND I.CDENCARGO  = E.CDENCARGO
                              AND UPPER(E.NMENCARGO) LIKE '%DESCONTO%')LOOP
         V_TOTDESC:=VALOR_ENCARGO(C_NOTAS_SERV.CDFILIAL, C_NOTAS_SERV.NRLANCTONF, C_ENCARGODESC.IDENCARGO, C_ENCARGODESC.CDENCARGO)+V_TOTDESC;
      END LOOP;   

      V_ITEMSERVICO := RPAD(NVL(C_NOTAS_SERV.CDSERIE, ' '), 5, ' ')                      ||
                       SUBSTR(LPAD(C_NOTAS_SERV.NRNOTAFISC,20,'0'),12,9)                 ||
                       RPAD(TO_CHAR(C_NOTAS_SERV.DTEMISSAO, 'DDMMYYYY'), 8, ' ')         ||
                       SUBSTR(LPAD(NVL(REPLACE(REPLACE(REPLACE(C_NOTAS_SERV.COD_PART,'/',''),'.',''),'-',''),' '),21,' '),8,14) ||
                       LPAD(NVL(SUBSTR(C_NOTAS_SERV.VRNOTAFISC * 100, 1, 17), 0), 17, 0) ||
                       LPAD(NVL(SUBSTR(V_TOTDESC * 100, 1, 17), 0), 17, 0)               ||
                       LPAD(NVL(SUBSTR(C_NOTAS_SERV.VRALIQNF * 100, 1, 5), 0), 5, 0)                ||
                       LPAD(NVL(SUBSTR(C_NOTAS_SERV.VRBASE_IRRF * 100, 1, 17), 0), 17, 0)           ||
                       LPAD(NVL(SUBSTR(C_NOTAS_SERV.VR_IRRF * 100, 1, 17), 0), 17, 0)               ||
                       C_NOTAS_SERV.IDSTATUS;

      V_SEQUNCIAL := NVL(V_SEQUNCIAL,0) +1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('04'||LPAD(RTRIM(V_SEQUNCIAL), 8, '0'), V_ITEMSERVICO,  P_NRSEQARQMAG);

     /*NOTAS - REGISTRO 435*/
     SELECT MAX(CDCHAVEREG)
       INTO V_NOTA
       FROM TMP_REGDADOS_IN86
      WHERE CDREGISTRO = '435'
        AND CDCHAVEREG = C_NOTAS_SERV.CDFILIAL||C_NOTAS_SERV.NRLANCTONF;
     IF (V_NOTA IS NULL) THEN
        V_SEQ_NOTA := V_SEQ_NOTA + 1;
        INSERT INTO TMP_REGDADOS_IN86
          (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2, CDCAMPO3)
        VALUES
          ('435', V_SEQ_NOTA, C_NOTAS_SERV.CDFILIAL||C_NOTAS_SERV.NRLANCTONF, C_NOTAS_SERV.ID_REG, C_NOTAS_SERV.DTEMISSAO, C_NOTAS_SERV.IDENTRSAIDOP);
     END IF;
     V_NOTA := NULL;

     /*PARTICIPANTE - REGISTRO 491*/
     SELECT MAX(CDCHAVEREG)
       INTO V_PART
       FROM TMP_REGDADOS_IN86
      WHERE CDREGISTRO = '491'
        AND CDCHAVEREG = C_NOTAS_SERV.COD_PART;
     IF (V_PART IS NULL) AND (C_NOTAS_SERV.COD_PART IS NOT NULL) THEN
        V_SEQ_PART := V_SEQ_PART + 1;
        INSERT INTO TMP_REGDADOS_IN86
          (CDREGISTRO, CDCONTREGI, CDCHAVEREG, CDCAMPO1, CDCAMPO2)
        VALUES
          ('491', V_SEQ_PART, C_NOTAS_SERV.COD_PART, C_NOTAS_SERV.IDORIGDESTNF, C_NOTAS_SERV.DTEMISSAO);
     END IF;
     V_PART := NULL; 
   END LOOP;

   COMMIT;
END;
/