CREATE OR REPLACE PROCEDURE ARQMESTRE_SERVTERC_438(
   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;
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.CDSERIEENTR, N.NRNOTAFISC, N.DTEMISSAO, N.NRINSJURNF, N.VRNOTAFISC, N.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, MIN(N.DTEMISSAO) AS DTATUALIZACAO
                         FROM IMPOSTONFE E, NOTAFISCAL N, IMPOSTONF I, IMPOSTO P, FILIAL A,
                              FORNECEDOR F, CLIENTE C, FILIAL L
                        WHERE E.CDIMPOSTONFE = '35' /*35-ISS_RET_TOMADOS*/
                          AND N.CDEMPRESA    = P_CDEMPRESA
                          AND N.CDFILIRECE   = 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.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL))
                          AND N.IDSTATUS    <> 'P'
                          AND N.IDENTRSAIDOP = 'E'
                          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.CDFILIEMIT   = L.CDFILIAL(+)
                        GROUP BY N.CDFILIAL, N.NRLANCTONF, N.CDSERIEENTR, 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;

      FOR C_ARQMESTRE_SERVTERC IN (SELECT RPAD(NVL(C_NOTAS_SERV.CDSERIEENTR, ' '), 5, ' ')                  ||
                                          SUBSTR(LPAD(C_NOTAS_SERV.NRNOTAFISC,20,'0'),12,9)                 ||
                                          RPAD(TO_CHAR(C_NOTAS_SERV.DTEMISSAO, 'DDMMYYYY'), 8, ' ')         ||
                                          SUBSTR(RPAD(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(I.VRALIQNF * 100, 1, 5), 0), 5, 0)                ||
                                          LPAD(NVL(SUBSTR(I.VRBASE_IRRF * 100, 1, 17), 0), 17, 0)           ||
                                          LPAD(NVL(SUBSTR(I.VR_IRRF * 100, 1, 17), 0), 17, 0)               ||
                                          DECODE(C_NOTAS_SERV.IDSTATUS, 'C', 'S', 'N')  AS ITEMSERVICO,
                                          I.CDFILIAL, I.NRLANCTONF,
                                          RPAD(NVL(C_NOTAS_SERV.CDSERIEENTR, ' '), 5, ' ')||SUBSTR(LPAD(C_NOTAS_SERV.NRNOTAFISC,20,'0'),12,9)||RPAD(TO_CHAR(C_NOTAS_SERV.DTEMISSAO, 'DDMMYYYY'), 8, ' ') AS ID_REG,
                                          C_NOTAS_SERV.COD_PART, RPAD(TO_CHAR(C_NOTAS_SERV.DTATUALIZACAO, 'DDMMYYYY'), 8,'0') AS DTEMISSAO, C_NOTAS_SERV.IDENTRSAIDOP, C_NOTAS_SERV.IDORIGDESTNF
                                     FROM (SELECT I.CDFILIAL, I.NRLANCTONF, I.VRALIQNF,
                                                  SUM(I.VRBASECALCNF) AS VRBASE_IRRF, SUM(I.VRIMPOSTONF) AS VR_IRRF
                                             FROM IMPOSTONF I, IMPOSTO IM
                                            WHERE IM.CDIMPOSTONFE IN ('22','23') /* 22-IRRF_TOMADOS, 23-IRRF_ALUGUEL */
                                              AND IM.CDIMPOSTO    = I.CDIMPOSTO
                                              AND I.NRLANCTONF    = C_NOTAS_SERV.NRLANCTONF
                                              AND I.CDFILIAL      = C_NOTAS_SERV.CDFILIAL
                                            GROUP BY I.CDFILIAL, I.NRLANCTONF, I.VRALIQNF) I) LOOP

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

         /*NOTAS - REGISTRO 438*/
         SELECT MAX(CDCHAVEREG)
           INTO V_NOTA
           FROM TMP_REGDADOS_IN86
          WHERE CDREGISTRO = '438'
            AND CDCHAVEREG = C_ARQMESTRE_SERVTERC.CDFILIAL||C_ARQMESTRE_SERVTERC.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
              ('438', V_SEQ_NOTA, C_ARQMESTRE_SERVTERC.CDFILIAL||C_ARQMESTRE_SERVTERC.NRLANCTONF, C_ARQMESTRE_SERVTERC.ID_REG, C_ARQMESTRE_SERVTERC.DTEMISSAO, C_ARQMESTRE_SERVTERC.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_ARQMESTRE_SERVTERC.COD_PART;
         IF (V_PART IS NULL) AND (C_ARQMESTRE_SERVTERC.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_ARQMESTRE_SERVTERC.COD_PART, C_ARQMESTRE_SERVTERC.IDORIGDESTNF, C_ARQMESTRE_SERVTERC.DTEMISSAO);
         END IF;
         V_PART := NULL;

      END LOOP;
   END LOOP;

   COMMIT;
END;
/
