CREATE OR REPLACE PROCEDURE CTB_DFE_HISTORICO(
   P_CDFILIAL   FILIAL.CDFILIAL%TYPE,
   P_NRLANCTONF NOTAFISCAL.NRLANCTONF%TYPE,
   P_CDHISTPADR OUT LANCCONT.CDHISTPADR%TYPE,
   P_DSHISTLANC OUT LANCCONT.DSHISTLANC%TYPE)
AS
   V_DSHISTLANC  VARCHAR2(2000);
   V_CDHISTPADR  HISTPADR.CDHISTPADR%TYPE;
   V_DSPRODUTO   ITEMPRODUTO.DSPRODEMIS%TYPE;

BEGIN
   FOR C_NOTA IN
      (SELECT NF.NRNOTAFISC, NF.DTEMISSAO, NF.DTENTRSAID,
              NF.NRLANCTONF, NF.CDFILIRECE,
              FORMATA_PESSOA(SUBSTR(C.CDCLIENTE,  1, 1), C.CDCLIENTE) NRINSJURCLIE, C.NMFANTCLIE, C.NMRAZSOCCLIE,
              FORMATA_PESSOA(SUBSTR(F.CDFORNECED, 1, 1), F.CDFORNECED) NRINSJURFORN, F.NMFANTFORN, F.NMRAZSOCFORN,
              H.CDHISTPADR, H.DSHISTPADR,
              CT.CDTPCTPAGAR, CT.DSCONTAPAG
         FROM TIPOOPERAC T, HISTPADR H, NOTAFISCAL NF, CONTAPAGAR CT,
              FORNECEDOR F, CLIENTE C, CONTARECEBER CR
        WHERE NF.CDFILIAL = P_CDFILIAL
          AND NF.NRLANCTONF = P_NRLANCTONF
          AND NF.IDENTRSAIDOP = T.IDENTRSAIDOP
          AND NF.CDTIPOOPER = T.CDTIPOOPER
          AND NF.CDFILIAL = CT.CDFILIAL(+)
          AND NF.NRLANCTONF = CT.NRLANCTONF(+)
          AND NF.CDFILIAL = CR.CDFILIAL(+)
          AND NF.NRLANCTONF = CR.NRLANCTONF(+)
          AND NF.CDFORNECED = F.CDFORNECED(+)
          AND NF.CDCLIENTE  = C.CDCLIENTE(+)
          AND T.CDHISTPADR = H.CDHISTPADR) LOOP
      V_CDHISTPADR := C_NOTA.CDHISTPADR;
      V_DSHISTLANC := C_NOTA.DSHISTPADR;

      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[DOC]', C_NOTA.NRNOTAFISC);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[FIL]', C_NOTA.CDFILIRECE);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[SEQ]', C_NOTA.NRLANCTONF);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[CLI]', C_NOTA.NRINSJURCLIE);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[NFC]', C_NOTA.NMFANTCLIE);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[RSC]', C_NOTA.NMRAZSOCCLIE);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[FOR]', C_NOTA.NRINSJURFORN);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[NFF]', C_NOTA.NMFANTFORN);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[RSF]', C_NOTA.NMRAZSOCFORN);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[DTEM-MA]', C_NOTA.DTEMISSAO);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[[DTES-MA]]', C_NOTA.DTENTRSAID);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[DTEM]', C_NOTA.DTEMISSAO);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[DTES]', C_NOTA.DTENTRSAID);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[DCP]', C_NOTA.DSCONTAPAG);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[TCP]', C_NOTA.CDTPCTPAGAR);
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[TAP]', ' ');
      V_DSHISTLANC := REPLACE(V_DSHISTLANC, '[DAP]', ' ');

      IF INSTR('[DSPROD]', V_DSHISTLANC) > 0 THEN
         FOR C_PROD IN
            (SELECT DISTINCT CDPRODUTO
               FROM ITEMPRODUTO
              WHERE CDFILIAL = P_CDFILIAL
                AND NRLANCTONF = P_NRLANCTONF)LOOP
            BEGIN
               SELECT NMPRODUTO
                 INTO V_DSPRODUTO
                 FROM PRODUTO
               WHERE CDPRODUTO = C_PROD.CDPRODUTO;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_DSPRODUTO := NULL;
            END;
            IF V_DSPRODUTO IS NOT NULL THEN
               V_DSHISTLANC := V_DSHISTLANC || ' - ' || V_DSPRODUTO;
            END IF;
         END LOOP;
      END IF;
   END LOOP;

   P_CDHISTPADR := V_CDHISTPADR;
   P_DSHISTLANC := SUBSTR(V_DSHISTLANC, 1, 200);
END;
/

