CREATE OR REPLACE PROCEDURE EXPORTACAO_SAMMED(
   P_NRSEQUENCIAL IN NUMBER,
   P_CDEMPRESA IN VARCHAR,
   P_DTINI IN DATE,
   P_DTFIN IN DATE,
   P_CDTIPOOPER IN VARCHAR,
   P_ARQUIVOXLS IN VARCHAR)
AS
BEGIN
   IF (P_ARQUIVOXLS = 'S') THEN
      FOR C_PROD IN
         (SELECT P.CDGGREM, IDTIPORAMO,
                 TO_CHAR(NF.DTEMISSAO, 'MM') MES,
                 TO_CHAR(NF.DTEMISSAO, 'YYYY') ANO,
                 SUM(ROUND(QTITEMPROD, 4)) QTITEM, SUM(ROUND(VRBRUTITEM, 2)) VRFAT
            FROM (SELECT NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO,
                         NVL(A.IDTIPORAMO, 'N') IDTIPORAMO
                    FROM NOTAFISCAL NF, EMPRESA E, CLIENTE C, ATIVCLIENTE A
                   WHERE NF.CDEMPRESA = P_CDEMPRESA
                     AND NF.DTEMISSAO BETWEEN P_DTINI AND P_DTFIN
                     AND NF.CDEMPRESA = E.CDEMPRESA
                     AND NF.CDCLIENTE = C.CDCLIENTE
                     AND NF.IDENTRSAIDOP = 'S'
                     AND ((INSTR(P_CDTIPOOPER, NF.CDTIPOOPER) > 0) OR ('T' = P_CDTIPOOPER))
                     AND NF.IDSISTEMA IN ('DFS', 'EXP', 'FAI', 'PED')
                     AND NF.IDSTATUS IN ('A', 'E')
                     AND C.CDATIVCLI = A.CDATIVCLI
                     AND A.IDTIPORAMO <> 'N') NF, ITEMPRODUTO I, PRODUTO P
           WHERE NF.CDFILIAL = I.CDFILIAL
             AND NF.NRLANCTONF = I.NRLANCTONF
             AND I.CDPRODUTO = P.CDPRODUTO
             AND P.CDGGREM IS NOT NULL
           GROUP BY P.CDGGREM, IDTIPORAMO,
                    TO_CHAR(NF.DTEMISSAO, 'MM'),
                    TO_CHAR(NF.DTEMISSAO, 'YYYY')) LOOP
         INSERT INTO DADOSEXPSAMMED
            (NRSEQUENCIAL, CDGGREM, CDANO,
             CDMES, IDTIPORAMO, QTITEM, VRFAT)
         VALUES
            (P_NRSEQUENCIAL, C_PROD.CDGGREM, C_PROD.ANO,
             C_PROD.MES, C_PROD.IDTIPORAMO, C_PROD.QTITEM, C_PROD.VRFAT);
      END LOOP;
   ELSE
      FOR C_PROD IN
         (SELECT P.CDGGREM, IDTIPORAMO,
                 (CASE IDOPERDEVONF WHEN 'N' THEN 'V' ELSE IDOPERDEVONF END) AS TIPOOPER,
                 TO_CHAR(NF.DTEMISSAO, 'MM') MES,
                 TO_CHAR(NF.DTEMISSAO, 'YYYY') ANO,
                 SUM(ROUND(QTITEMPROD, 4)) QTITEM, SUM(ROUND(VRBRUTITEM, 2)) VRFAT
            FROM (SELECT NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO,
                         NVL(A.IDTIPORAMO, 'N') IDTIPORAMO,
                         (CASE T.CDTIPOOPER WHEN '06' THEN 'D' ELSE NVL(T.IDOPERDEVONF, 'N') END) AS IDOPERDEVONF
                    FROM NOTAFISCAL NF, EMPRESA E, CLIENTE C, ATIVCLIENTE A, TIPOOPERAC T
                   WHERE NF.CDEMPRESA = P_CDEMPRESA
                     AND NF.DTEMISSAO BETWEEN P_DTINI AND P_DTFIN
                     AND NF.CDEMPRESA = E.CDEMPRESA
                     AND NF.CDCLIENTE = C.CDCLIENTE
                     AND NF.IDENTRSAIDOP = 'S'
                     AND NF.CDTIPOOPER   = T.CDTIPOOPER
                     AND NF.IDENTRSAIDOP = T.IDENTRSAIDOP
                     AND ((INSTR(P_CDTIPOOPER, NF.CDTIPOOPER) > 0) OR ('T' = P_CDTIPOOPER))
                     AND NF.IDSISTEMA IN ('DFS', 'EXP', 'FAI', 'PED')
                     AND NF.IDSTATUS IN ('A', 'E')
                     AND C.CDATIVCLI = A.CDATIVCLI
                     AND A.IDTIPORAMO <> 'N') NF, ITEMPRODUTO I, PRODUTO P
           WHERE NF.CDFILIAL = I.CDFILIAL
             AND NF.NRLANCTONF = I.NRLANCTONF
             AND I.CDPRODUTO = P.CDPRODUTO
             AND P.CDGGREM IS NOT NULL
           GROUP BY P.CDGGREM, IDTIPORAMO,
                    TO_CHAR(NF.DTEMISSAO, 'MM'),
                    TO_CHAR(NF.DTEMISSAO, 'YYYY'),
                    (CASE IDOPERDEVONF WHEN 'N' THEN 'V' ELSE IDOPERDEVONF END)) LOOP
         INSERT INTO DADOSEXPSAMMED
            (NRSEQUENCIAL, CDGGREM, CDANO,
             CDMES, IDTIPORAMO, QTITEM, VRFAT, TIPOOPER)
         VALUES
            (P_NRSEQUENCIAL, C_PROD.CDGGREM, C_PROD.ANO,
             C_PROD.MES, C_PROD.IDTIPORAMO, C_PROD.QTITEM, C_PROD.VRFAT, C_PROD.TIPOOPER);
      END LOOP;
   END IF;
END;
/