create or replace
PROCEDURE EXPORTA_APURFATUR(P_FILIAL     IN VARCHAR2,
                            P_NRLANCTONF IN VARCHAR2)
IS
   V_CDUNICO  VARCHAR2(14);
   V_CDPAIS VARCHAR2(04);
   V_SGESTADO VARCHAR2(02);
   V_CDMUNICIBGE VARCHAR2(07);
   V_NRINSJUDESNF VARCHAR2(20);
   V_NMRAZSODESNF VARCHAR2(50);
   V_DSENDEDESTNF VARCHAR2(50);
   V_NMBAIRDESTNF VARCHAR2(30);
   V_IDTPIJUDESNF VARCHAR2(01);
   V_NRMD5ARQTXTNF VARCHAR2(50);
   V_NRCOOCUPOMNF VARCHAR2(10);
   V_NRECFCUPOMNF VARCHAR2(10);
   V_NRSERIEIMPNF VARCHAR2(30);
   V_DSMODELOECFNF VARCHAR2(30);
   V_DSMARCAECFNF VARCHAR2(30);
   V_NRACESSONFCENF VARCHAR2(50);
   V_NRSEQCUPOMNF VARCHAR2(3);
   V_NRSEQDADOSNF VARCHAR2(4);
   V_SEQUENCIAL   INTEGER;
   V_CDPROD       VARCHAR2(10);
   V_CDARVPROD    VARCHAR2(20);

   CURSOR C_DADOSNF(C_CDFILIAL IN VARCHAR2, C_NRLANCTONF IN VARCHAR2) IS
      SELECT M.CDPAIS, M.SGESTADO, M.CDMUNICIBGE, D.NRINSJUDESNF, D.NMRAZSODESNF, D.DSENDEDESTNF, D.NMBAIRDESTNF, D.IDTPIJUDESNF,
             D.NRMD5ARQTXTNF, DECODE(V.IDTPEMISVEND,'S',V.NRNOTAFISCALCE,DECODE(V.IDTPEMISVEND,'N',V.NRNOTAFISCALCE,D.NRCOOCUPOMNF)) AS NRCOOCUPOMNF,
             D.NRECFCUPOMNF, D.NRSERIEIMPNF, D.DSMODELOECFNF, D.DSMARCAECFNF,
             D.NRACESSONFCENF, D.NRSEQCUPOMNF
        FROM DADOSNF D, MUNICIPIO M, VENDA V
       WHERE D.CDFILIAL = C_CDFILIAL
         AND D.NRLANCTONF = C_NRLANCTONF
         AND D.CDPAIS = M.CDPAIS
         AND D.SGESTADO = M.SGESTADO
         AND D.CDMUNICIPIO = M.CDMUNICIPIO
         AND D.NRACESSONFCENF = V.NRACESSONFCE (+);
BEGIN
   FOR C_APURFAT IN
      (SELECT N.CDFILIAL||N.NRLANCTONF AS CDUNICO, N.CDEMPRESA, N.CDFILIEMIT,
              N.CDTIPOOPER,
              CASE WHEN N.NRINSJURNF IS NULL
                   THEN N.CDCLIENTE
                   ELSE N.NRINSJURNF
               END AS CDINSCRICAO,
              N.CDMODDOCFISC, N.DTENTRSAID AS DTSAIDA, N.VRNOTAFISC AS VRTOTALNF, N.NRSEQCAPADOC AS NRNUMRETG,
         CASE WHEN T.IDORIGDESTOP = 'L'
              THEN 'CR'
              ELSE T.IDORIGDESTOP
         END AS IDTPINSJURNF,
         F.IDFUNCAOFILI
         FROM NOTAFISCAL N, TIPOOPERAC T, FILIAL F
        WHERE N.CDFILIAL   = P_FILIAL
          AND N.NRLANCTONF = P_NRLANCTONF
          AND N.IDENTRSAIDOP = T.IDENTRSAIDOP
          AND N.CDTIPOOPER   = T.CDTIPOOPER
          AND F.CDFILIAL = N.CDFILIEMIT)
   LOOP
      V_CDUNICO := NULL;

      BEGIN
         SELECT CDUNICO
           INTO V_CDUNICO
           FROM USR_SAP_APURFATUR
          WHERE CDUNICO = C_APURFAT.CDUNICO;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDUNICO := NULL;
      END;

      IF V_CDUNICO IS NULL THEN
         V_SEQUENCIAL := 1;
         OPEN C_DADOSNF(P_FILIAL, P_NRLANCTONF);
         LOOP
            FETCH C_DADOSNF INTO V_CDPAIS, V_SGESTADO, V_CDMUNICIBGE, V_NRINSJUDESNF, V_NMRAZSODESNF, V_DSENDEDESTNF, V_NMBAIRDESTNF, V_IDTPIJUDESNF,
                                 V_NRMD5ARQTXTNF, V_NRCOOCUPOMNF, V_NRECFCUPOMNF, V_NRSERIEIMPNF, V_DSMODELOECFNF, V_DSMARCAECFNF, V_NRACESSONFCENF,
                                 V_NRSEQCUPOMNF;
            EXIT WHEN C_DADOSNF%NOTFOUND;

            SELECT LPAD(TO_CHAR(V_SEQUENCIAL), 4, '0')
              INTO V_NRSEQDADOSNF
              FROM DUAL;

            INSERT INTO USR_SAP_DADOSNF(CDUNICO, CDPAIS, SGESTADO, CDMUNICIPIO, NRINSJUDESNF, NMRAZSODESNF, DSENDEDESTNF, NMBAIRDESTNF, IDTPIJUDESNF,
                                        NRMD5ARQTXTNF, NRCOOCUPOMNF, NRECFCUPOMNF, NRSERIEIMPNF, DSMODELOECFNF, DSMARCAECFNF, NRACESSONFCENF,
                                        NRSEQCUPOMNF, NRSEQDADOSNF,
                                        IDINTERFACE, TPINTERFACE, DTINTERFACEORI, DTINTERFACEDEST, STATUS, APPORIGEM, APPDESTINO)
            VALUES(/* CUPONS REFERENCIADOS A NOTAS */
                   C_APURFAT.CDUNICO, V_CDPAIS, V_SGESTADO, V_CDMUNICIBGE, V_NRINSJUDESNF, V_NMRAZSODESNF, V_DSENDEDESTNF, V_NMBAIRDESTNF, V_IDTPIJUDESNF,
                   V_NRMD5ARQTXTNF, V_NRCOOCUPOMNF, V_NRECFCUPOMNF, V_NRSERIEIMPNF, V_DSMODELOECFNF, V_DSMARCAECFNF, V_NRACESSONFCENF,
                   V_NRSEQCUPOMNF, V_NRSEQDADOSNF,
                   /* CONTROLE SAP */
                   USR_SAP_IDINTERFACE.NEXTVAL, 'I', SYSDATE, NULL, 1, 1, 2);

            V_SEQUENCIAL := V_SEQUENCIAL + 1;
         END LOOP;

         INSERT INTO USR_SAP_APURFATUR(CDUNICO, CDEMPRESA, CDFILIAL, CDTIPOOPER, CDINSCRICAO, CDMODELO, DTSAIDA,
                                       VRTOTALINF, NRNUMRETG,
                                       IDINTERFACE, TPINTERFACE, DTINTERFACEORI, DTINTERFACEDEST, STATUS, APPORIGEM, APPDESTINO,
                                       IDTPINSJURNF)
         VALUES (C_APURFAT.CDUNICO, C_APURFAT.CDEMPRESA, C_APURFAT.CDFILIEMIT, C_APURFAT.CDTIPOOPER, C_APURFAT.CDINSCRICAO, C_APURFAT.CDMODDOCFISC, C_APURFAT.DTSAIDA,
                 C_APURFAT.VRTOTALNF, C_APURFAT.NRNUMRETG,
                 USR_SAP_IDINTERFACE.NEXTVAL, 'I', SYSDATE, NULL, 1, 1, 2,
                 C_APURFAT.IDTPINSJURNF);

         FOR C_ITAPURFATUR IN
            (SELECT I.NRSEQITPROD, I.CDPRODUTO, I.QTITEMPROD, I.VRUNITITEM, I.VRDESCITEM, I.VRTOTAITEM,
                    P.CDCLASFISC AS CDNCM, I.CDFISOPEITEM, I.CDSITUTRIBPR, NVL(I.DSPRODEMIS, P.NMPRODUTO) DSPRODEMIS,
                    I.NRPEDPROD, I.NRSEQPEDPROD
               FROM ITEMPRODUTO I, PRODUTO P
              WHERE I.CDFILIAL = P_FILIAL
                AND I.NRLANCTONF = P_NRLANCTONF
                AND I.CDPRODUTO = P.CDPRODUTO)
         LOOP

            IF NVL(C_APURFAT.IDFUNCAOFILI, 'F') = 'O' THEN
               BEGIN
                  SELECT MIN(CDPRARVORIG) CDPRARVORIG
                    INTO V_CDPROD
                    FROM RELACPRODOL
                   WHERE CDPRODARVOL = C_ITAPURFATUR.CDPRODUTO;
               EXCEPTION
                  WHEN OTHERS THEN
                     V_CDPROD := C_ITAPURFATUR.CDPRODUTO;
               END;
            ELSE
               V_CDPROD := C_ITAPURFATUR.CDPRODUTO;
            END IF;

            IF V_CDPROD IS NULL THEN
               V_CDPROD := C_ITAPURFATUR.CDPRODUTO;
            END IF;

            SELECT CDARVPROD
              INTO V_CDARVPROD
              FROM PRODUTO
             WHERE CDPRODUTO = V_CDPROD;

            INSERT INTO USR_SAP_ITAPURFATUR(CDUNICO, NRSEQITEM, CDPRODUTO, VRQUANTIDADE,
                                            VRUNITARIO, VRDESCITEM, VRTOTAITEM,
                                            IDINTERFACE, TPINTERFACE, DTINTERFACEORI, DTINTERFACEDEST, STATUS, APPORIGEM, APPDESTINO,
                                            CDNCM, CDCFOPITPROD, CDCSTITPROD, DSPRODEMIS,
                                            NRPEDPROD, NRSEQPEDPROD)
            VALUES (C_APURFAT.CDUNICO, C_ITAPURFATUR.NRSEQITPROD, V_CDARVPROD, C_ITAPURFATUR.QTITEMPROD,
                    C_ITAPURFATUR.VRUNITITEM, C_ITAPURFATUR.VRDESCITEM, C_ITAPURFATUR.VRTOTAITEM,
                    USR_SAP_IDINTERFACE.NEXTVAL, 'I', SYSDATE, NULL, 1, 1, 2,
                    C_ITAPURFATUR.CDNCM, C_ITAPURFATUR.CDFISOPEITEM, C_ITAPURFATUR.CDSITUTRIBPR, C_ITAPURFATUR.DSPRODEMIS,
                    C_ITAPURFATUR.NRPEDPROD, C_ITAPURFATUR.NRSEQPEDPROD);
         END LOOP;

         FOR C_DADOSADIC IN
            (SELECT NRDADOADIC, DSDADOADIC, CDMENSAGEM
               FROM DADOSADIC
              WHERE CDFILIAL   = P_FILIAL
                AND NRLANCTONF = P_NRLANCTONF)
         LOOP
            INSERT INTO USR_SAP_DADOSADIC(CDUNICO, NRDADOADIC, DSDADOSADIC, CDMENSAGEM,
                                          IDINTERFACE, TPINTERFACE, DTINTERFACEORI, DTINTERFACEDEST, STATUS, APPORIGEM, APPDESTINO)
            VALUES (C_APURFAT.CDUNICO, C_DADOSADIC.NRDADOADIC, C_DADOSADIC.DSDADOADIC, C_DADOSADIC.CDMENSAGEM,
                    USR_SAP_IDINTERFACE.NEXTVAL, 'I', SYSDATE, NULL, 1, 1, 2);
         END LOOP;
      END IF;
      COMMIT;
   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('EXPORTA_APURFATUR','6.28.XXX'); END;
/
