CREATE OR REPLACE PROCEDURE GERA_NF_RET_JOB
IS
BEGIN
   FOR C_NOTAS IN (SELECT N.CDFILIAL, N.NRLANCTONF, P.CDFILIAL AS CDFILIALOL,
                          P.CDFILWMSMHAARM, N.CDFILIEMIT, N.CDFILIRECE,
                          N.DTEMISSAO, N.DTENTRSAID
                     FROM PARAMFILIAL P, NOTAFISCAL N, GERPARAMGERAL PG, TIPOOPERREMRET T
                    WHERE P.CDFILWMSMHAARM IS NOT NULL
                      AND N.CDFILIEMIT = P.CDFILIAL
                      AND (NVL(PG.IDNFREMRETAUTO, 'N') = 'S')
                      AND N.IDENTRSAIDOP = 'S'
                      AND N.IDSTATUSNFE = 'A'
                      AND N.IDORIGDESTNF IN ('F', 'L', 'C')
                      AND N.CDTIPOOPER   = T.CDTIPOOPER
                      AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
                      AND T.IDENTRREMRET  = 'T'
                      AND PG.CDTPOPRETSAID IS NOT NULL
                      AND PG.CDTPOPRETENTR IS NOT NULL
                      AND N.USR_IDRETORNO IS NULL
                      AND N.DTENTRSAID BETWEEN (SYSDATE - 365) AND (SYSDATE - 1)
                      AND N.USR_IDRETORNO IS NULL
                      AND NOT EXISTS (SELECT 1
                                        FROM RELACREMESSARET R
                                       WHERE R.CDFILIALUTIL   = N.CDFILIAL
                                         AND R.NRLANCTONFUTIL = N.NRLANCTONF)
                    ORDER BY N.CDFILIEMIT, N.DTEMISSAO) LOOP

      GERA_NF_RET_ARMAZENA(C_NOTAS.CDFILIAL, C_NOTAS.NRLANCTONF);
      COMMIT;
   END LOOP;
END;
/