/*
TEKNISA SOFTWARE

NOME: ALM_PEDIDO_NF

DESCRICAO:
   VIEW PARA EXPORTACAO DE PEDIDOS/NF

CRIACAO:
   30/07/2018 - MATEUS ANASTACIO

@C:\web_605\sql\end_conv\oracle\clientes\alm\0004_ALMVIEO_PEDIDO_NF.SQL

RAISE_APPLICATION_ERROR(-20501,, TRUE);
--CREATE OR REPLACE FORCE VIEW "ALM_PEDIDO_NF" ("DTPEDIDO", "DADOS") AS
*/
CREATE OR REPLACE VIEW ALM_PEDIDO_NF AS
  SELECT DTPEDIDO, DTEMISSAO, NRACESSONFE, NRPEDIDO, DADOS
    FROM (
  /* Pedidos */
  SELECT DISTINCT PED.DTPEDIDO,
                  NF.DTEMISSAO AS DTEMISSAO,
                  NF.NRACESSONFE AS NRACESSONFE,
                  IT.NRPEDIDO AS NRPEDIDO,
                 (IT.NRPEDIDO||/*FATPED*/
                  TO_CHAR(PED.DTPEDIDO,'DDMMYYYY')||/*FATDATPED*/
                  LPAD(NVL(NF.NRNOTAFISC,' '),9)||/*FATNUM*/
                  LPAD(NVL(TO_CHAR(NF.DTEMISSAO,'DDMMYYYY'),' '),8)||/*FATDATNF*/
                  LPAD(CLI.NRINSJURCLIE,20)||/*FATCLI*/
                  LPAD(NVL(PED.CDVENDEDOR,' '),5,' ')||/*FATVEN*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.DSQTDEVOLU,'99999999.999'),'.','')),0),11,' ')||/*FATVOL*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(IT.VR_BC_ICMS,'999999999999.999'),'.','')),0),15,' ')||/*FATSUB*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(IT.VR_IPI,'99999999.999'),'.','')),0),11,' ')||/*FATIPI*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(IT.VR_ICMS,'9999999999.99'),'.','')),0),12,' ')||/*FATICMS*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC,'999999999999.999'),'.','')),LTRIM(REPLACE(TO_CHAR(VALOR_PEDIDO_LIQUIDO(IT.CDFILIAL,IT.NRPEDIDO),'999999999999.999'),'.',''))),15,' ')||/*FATTOT*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VRPBRUTOVOLU,'99999999.999'),'.','')),0),11,' ')||/*FATPESO*/
                  LPAD(NVL(PED.NRINSJURTRAN,' '),20,' ')||/*FATTRANS*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(IT.VR_BC_ICMS_RET, '9999999999.999'),'.','')),0),13,' ')||/*FATDESC*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(IT.VR_ICMS_RET,'9999999999.999'),'.','')),0),13,' ')||/*FATICMRET*/
                  LPAD(CASE WHEN NVL(NF.CDFISOPENTF,'') = '***' THEN '****' ELSE NVL(NF.CDFISOPENTF,' ') END,4,' ')||/*FATCODF*/
                  RPAD(SUBSTR(CLI.DSEND,0,40),40)||/*FATEND*/
                  RPAD(SUBSTR(CLI.DSMUN,0,30),30)||/*FATCID*/
                  RPAD(SUBSTR(CLI.SGEST,0,2),2)||/*FATEST*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(IT.VR_BC_IPI,'9999999999.999'),'.','')),0),13,' ')||/*BASEIPI*/
                  LPAD(NVL(CASE WHEN VENC.NRSEQVENREC = '01' THEN TO_CHAR(VENC.DTATUAVENREC,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO1*/
                  LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN VENC.NRSEQVENREC = '01' THEN VENC.VRATUAREC ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR1*/
                  LPAD(NVL(CASE WHEN VENC.NRSEQVENREC = '02' THEN TO_CHAR(VENC.DTATUAVENREC,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO2*/
                  LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN VENC.NRSEQVENREC = '02' THEN VENC.VRATUAREC ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR2*/
                  LPAD(NVL(CASE WHEN VENC.NRSEQVENREC = '03' THEN TO_CHAR(VENC.DTATUAVENREC,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO3*/
                  LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN VENC.NRSEQVENREC = '03' THEN VENC.VRATUAREC ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR3*/
                  LPAD(NVL(CASE WHEN VENC.NRSEQVENREC = '04' THEN TO_CHAR(VENC.DTATUAVENREC,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO4*/
                  LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN VENC.NRSEQVENREC = '04' THEN VENC.VRATUAREC ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR4*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(IT.VR_BC_ICMS_INSENTO,'999999999999.99'),'.','')),0),14,' ')||/*BICMISENTO*/
                  RPAD(NVL(PED.NRORDEMCMPPED,' '),20)||/*PEDCLI*/
                  LPAD(NVL((AUX.CDVENDEDOR),' '),4)||/*TELECOD*/
                  RPAD(NVL(NF.IDPGTOFRETE,' '),1)||/*FRETE*/
                  LPAD(NVL(LTRIM(REPLACE(TO_CHAR(IT.VR_LISTA_NEGA,'999999999999.99'),'.','')),0),14,' ')||/*LISTANEGA*/
                  LPAD(NVL(NF.NRACESSONFE,' '),44)||/*CHAVE*/
                  NVL(NF.IDSTATUSNFE, ' ')||/*IDSTATUSNFE*/
                  NVL(PED.IDSITPEDIDO, ' ')||/*IDSTATUPED*/
                  RPAD(NVL(PED.DSPEDCLI,' '),10)/*NRPEDINTE*/) AS DADOS
             FROM PEDIDO PED
            INNER JOIN (SELECT CDFILIAL, NRLANCTONF, NRPEDIDO, NVL(VR_BC_ICMS_RET,0) VR_BC_ICMS_RET, NVL(VR_BC_ICMS,0) VR_BC_ICMS, SUM(VR_LISTA_NEGA) VR_LISTA_NEGA, NVL(VR_IPI,0) VR_IPI, NVL(VR_ICMS,0) VR_ICMS, NVL(VR_ICMS_RET,0) VR_ICMS_RET, NVL(VR_BC_IPI,0) VR_BC_IPI, NVL(VR_BC_ICMS_INSENTO,0) VR_BC_ICMS_INSENTO
                          FROM(
                         SELECT ITP.CDFILIAL, ITEMPRODUTO.NRLANCTONF, ITP.NRPEDIDO,
                               (((QTITEMPROD)*(VRUNITITEM))-(VRDESCITEM))VR_LISTA_NEGA,
                               (SELECT SUM(VRTOTIMPONF)
                                  FROM IMPOSNFTOT, IMPOSTO
                                 WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                                   AND IMPOSTO.CDIMPOSTO = '10'
                                   AND IMPOSNFTOT.CDFILIAL = ITEMPRODUTO.CDFILIAL
                                   AND IMPOSNFTOT.NRLANCTONF = ITEMPRODUTO.NRLANCTONF)VR_IPI,
                               (SELECT SUM(VRTOTIMPONF)
                                  FROM IMPOSNFTOT, IMPOSTO
                                 WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                                   AND IMPOSTO.CDIMPOSTO = '00'
                                   AND IMPOSNFTOT.CDFILIAL = ITEMPRODUTO.CDFILIAL
                                   AND IMPOSNFTOT.NRLANCTONF = ITEMPRODUTO.NRLANCTONF)VR_ICMS,
							                        (SELECT SUM(VRTOTBCALCNF)
                                  FROM IMPOSNFTOT, IMPOSTO
                                 WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                                   AND IMPOSTO.CDIMPOSTO = '00'
                                   AND IMPOSNFTOT.CDFILIAL = ITEMPRODUTO.CDFILIAL
                                   AND IMPOSNFTOT.NRLANCTONF = ITEMPRODUTO.NRLANCTONF)VR_BC_ICMS,
                               (SELECT SUM(VRTOTIMPONF)
                                  FROM IMPOSNFTOT, IMPOSTO
                                 WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTO
                                   AND IMPOSTO.CDIMPOSTO = '99'--ST
                                   AND IMPOSNFTOT.CDFILIAL = ITEMPRODUTO.CDFILIAL
                                   AND IMPOSNFTOT.NRLANCTONF = ITEMPRODUTO.NRLANCTONF) VR_ICMS_RET,
							                        (SELECT SUM(VRTOTBCALCNF)
                                  FROM IMPOSNFTOT, IMPOSTO
                                 WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTO
                                   AND IMPOSTO.CDIMPOSTO = '99'--ST
                                   AND IMPOSNFTOT.CDFILIAL = ITEMPRODUTO.CDFILIAL
                                   AND IMPOSNFTOT.NRLANCTONF = ITEMPRODUTO.NRLANCTONF) VR_BC_ICMS_RET,
                               (SELECT SUM(VRTOTBCALCNF)
                                  FROM IMPOSNFTOT, IMPOSTO
                                 WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                                   AND IMPOSTO.CDIMPOSTO = '10'
                                   AND IMPOSNFTOT.CDFILIAL = ITEMPRODUTO.CDFILIAL
                                   AND IMPOSNFTOT.NRLANCTONF = ITEMPRODUTO.NRLANCTONF) VR_BC_IPI,
                               (SELECT SUM(VRIMPOSTOIT)
                                  FROM ITEMPROIMPOS
                                 WHERE CDFILIAL = ITEMPRODUTO.CDFILIAL
                                   AND NRLANCTONF = ITEMPRODUTO.NRLANCTONF
                                   AND NRSEQITPROD = ITEMPRODUTO.NRSEQITPROD
                                   AND ITEMPROIMPOS.CDFISCALIT IN('2','3')
                                   AND CDIMPOSTO = '00' ) VR_BC_ICMS_INSENTO
                          FROM ITEMPEDIDO ITP
                          LEFT JOIN ITEMPRODUTO
                            ON ITP.CDFILIAL  = ITEMPRODUTO.CDFILIALPED
                           AND ITP.NRPEDIDO  = ITEMPRODUTO.NRPEDIDO
                           AND ITP.NRITEMPED = ITEMPRODUTO.NRITEMPED)
                         GROUP BY CDFILIAL, NRLANCTONF, NRPEDIDO, VR_BC_ICMS_RET, VR_BC_ICMS,VR_IPI, VR_ICMS, VR_ICMS_RET, VR_BC_IPI, VR_BC_ICMS_INSENTO
                           ) IT
               ON PED.CDFILIAL  = IT.CDFILIAL
              AND PED.NRPEDIDO  = IT.NRPEDIDO
             LEFT JOIN (SELECT NOTAFISCAL.CDFILIAL, NOTAFISCAL.NRLANCTONF, NOTAFISCAL.CDCLIENTE, NOTAFISCAL.NRNOTAFISC, NOTAFISCAL.DTEMISSAO, NOTAFISCAL.NRINSJURNF,
                               NOTAFISCAL.DSQTDEVOLU, NOTAFISCAL.CDFISOPENTF, NOTAFISCAL.VRPRODUTOS, NOTAFISCAL.VRPBRUTOVOLU, NOTAFISCAL.VRNOTAFISC, NOTAFISCAL.NRINSJURTRAN,
                               NOTAFISCAL.IDPGTOFRETE, NOTAFISCAL.NRACESSONFE, NOTAFISCAL.IDSTATUSNFE,
                               SUM(ITEMENCARGO.VRITEMENCA) VRENC_DESC
                          FROM ENCARGO, ITEMENCARGO, NOTAFISCAL
                         WHERE ITEMENCARGO.CDFILIAL = NOTAFISCAL.CDFILIAL
                           AND ITEMENCARGO.NRLANCTONF = NOTAFISCAL.NRLANCTONF
                           AND ENCARGO.CDENCARGO = ITEMENCARGO.CDENCARGO
                           AND ENCARGO.IDENCARGO = 'D'
                         GROUP BY NOTAFISCAL.CDFILIAL, NOTAFISCAL.NRLANCTONF, NOTAFISCAL.CDCLIENTE, NOTAFISCAL.NRNOTAFISC, NOTAFISCAL.DTEMISSAO, NOTAFISCAL.NRINSJURNF,
                                  NOTAFISCAL.DSQTDEVOLU, NOTAFISCAL.CDFISOPENTF, NOTAFISCAL.VRPRODUTOS, NOTAFISCAL.VRPBRUTOVOLU, NOTAFISCAL.VRNOTAFISC, NOTAFISCAL.NRINSJURTRAN,
                                  NOTAFISCAL.IDPGTOFRETE, NOTAFISCAL.NRACESSONFE, NOTAFISCAL.IDSTATUSNFE) NF
               ON NF.CDFILIAL   = IT.CDFILIAL
              AND NF.NRLANCTONF = IT.NRLANCTONF
             LEFT JOIN (SELECT CL.NRINSJURCLIE, CL.CDCLIENTE, ENCLI.DSENDECLIE AS DSEND, MUN.NMMUNICIPIO AS DSMUN, ENCLI.SGESTADO AS SGEST
                          FROM CLIENTE CL, ENDECLIE ENCLI, MUNICIPIO MUN
                         WHERE CL.CDCLIENTE = ENCLI.CDCLIENTE
                           AND ENCLI.CDPAIS = MUN.CDPAIS
                           AND ENCLI.SGESTADO = MUN.SGESTADO
                           AND ENCLI.CDMUNICIPIO = MUN.CDMUNICIPIO
                           AND ENCLI.IDTPENDECLIE = 'P') CLI
               ON CLI.CDCLIENTE = PED.CDCLIENTE
             LEFT JOIN (SELECT CDFILIAL, NRLANCTONF, NRSEQVENREC, DTATUAVENREC, VRATUAREC
                          FROM CONTARECEBER
                         ORDER BY NRSEQVENREC) VENC
               ON VENC.CDFILIAL = NF.CDFILIAL
              AND VENC.NRLANCTONF = NF.NRLANCTONF
             LEFT JOIN (SELECT VENDPEDAUX.CDFILIAL, VENDPEDAUX.NRPEDIDO, MAX(VENDPEDAUX.CDVENDEDOR) CDVENDEDOR
                          FROM VENDPEDAUX, PEDIDO
                         WHERE VENDPEDAUX.NRPEDIDO = PEDIDO.NRPEDIDO
                           AND VENDPEDAUX.CDFILIAL = PEDIDO.CDFILIAL
                         GROUP BY VENDPEDAUX.CDFILIAL, VENDPEDAUX.NRPEDIDO) AUX
               ON AUX.CDFILIAL = IT.CDFILIAL
              AND AUX.NRPEDIDO = IT.NRPEDIDO
    UNION ALL
   /* NF Saida */
   SELECT NF.DTEMISSAO AS DTPEDIDO,
          NF.DTEMISSAO AS DTEMISSAO,
          NF.NRACESSONFE AS NRACESSONFE,
          ' ' AS NRPEDIDO,
          (LPAD(' ',10)||/*FATPED*/
           LPAD(' ',8)||/*FATDATPED*/
           LPAD(NVL(NF.NRNOTAFISC,' '),9)||/*FATNUM*/
           LPAD(NVL(TO_CHAR(NF.DTEMISSAO,'DDMMYYYY'),' '),8)||/*FATDATNF*/
           LPAD(NF.NRINSJUR,20)||/*FATCLI*/
           LPAD(' ',5)||/*FATVEN*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.DSQTDEVOLU,'99999999.999'),'.','')),0),11,' ')||/*FATVOL*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_BC_ICMS,'999999999999.999'),'.','')),0),15,' ')||/*FATSUB*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_IPI,'99999999.999'),'.','')),0),11,' ')||/*FATIPI*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_ICMS,'9999999999.99'),'.','')),0),12,' ')||/*FATICMS*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC,'999999999999.999'),'.','')),0),15,' ')||/*FATTOT*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VRPBRUTOVOLU,'99999999.999'),'.','')),0),11,' ')||/*FATPESO*/
           LPAD(NVL(NF.NRINSJURTRAN,' '),20,' ')||/*FATTRANS*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_BC_ICMS_RET, '9999999999.999'),'.','')),0),13,' ')||/*FATDESC*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_ICMS_RET,'9999999999.999'),'.','')),0),13,' ')||/*FATICMRET*/
           LPAD(CASE WHEN NVL(NF.CDFISOPENTF,'') = '***' THEN '****' ELSE NVL(NF.CDFISOPENTF,' ') END,4,' ')||/*FATCODF*/
           RPAD(SUBSTR(NF.DSEND,0,40),40)||/*FATEND*/
           RPAD(SUBSTR(NF.DSMUN,0,30),30)||/*FATCID*/
           RPAD(SUBSTR(NF.SGEST,0,2),2)||/*FATEST*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_BC_IPI,'9999999999.999'),'.','')),0),13,' ')||/*BASEIPI*/
           LPAD(NVL(CASE WHEN NF.NRSEQVENREC = '01' THEN TO_CHAR(NF.DTATUAVENREC,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO1*/
           LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN NF.NRSEQVENREC = '01' THEN NF.VRATUAREC ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR1*/
           LPAD(NVL(CASE WHEN NF.NRSEQVENREC = '02' THEN TO_CHAR(NF.DTATUAVENREC,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO2*/
           LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN NF.NRSEQVENREC = '02' THEN NF.VRATUAREC ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR2*/
           LPAD(NVL(CASE WHEN NF.NRSEQVENREC = '03' THEN TO_CHAR(NF.DTATUAVENREC,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO3*/
           LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN NF.NRSEQVENREC = '03' THEN NF.VRATUAREC ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR3*/
           LPAD(NVL(CASE WHEN NF.NRSEQVENREC = '04' THEN TO_CHAR(NF.DTATUAVENREC,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO4*/
           LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN NF.NRSEQVENREC = '04' THEN NF.VRATUAREC ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR4*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_BC_ICMS_INSENTO,'999999999999.99'),'.','')),0),14,' ')||/*BICMISENTO*/
           RPAD(' ',20)||/*PEDCLI*/
           LPAD(' ',4)||/*TELECOD*/
           RPAD(NVL(NF.IDPGTOFRETE,' '),1)||/*FRETE*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_LISTA_NEGA,'999999999999.99'),'.','')),0),14,' ')||/*LISTANEGA*/
           LPAD(NVL(NF.NRACESSONFE,' '),44)||/*CHAVE*/
           NF.IDSTATUSNFE||/*IDSTATUSNFE*/
           ' '||/*IDSTATUPED*/
           RPAD(' ',10)/*NRPEDINTE*/) AS DADOS
      FROM (SELECT NF.DTENTRSAID, NF.NRNOTAFISC, NF.DTEMISSAO, NF.DSQTDEVOLU,
                   NF.VRNOTAFISC, NF.VRPBRUTOVOLU, NF.NRINSJURTRAN, NF.CDFISOPENTF,
                   NF.IDPGTOFRETE, NF.NRACESSONFE, NF.IDSTATUSNFE,
                   DECODE(NVL(NF.IDORIGDESTNF, 'C'), 'C', CLI.NRINSJURCLIE, DECODE(IDORIGDESTNF, 'L', FIL.NRINSJURFILI, FORN.NRINSJURFORN)) AS NRINSJUR,
                   DECODE(NVL(NF.IDORIGDESTNF, 'C'), 'C', CLI.DSEND, DECODE(IDORIGDESTNF, 'L', FIL.DSEND, FORN.DSEND)) AS DSEND,
                   DECODE(NVL(NF.IDORIGDESTNF, 'C'), 'C', CLI.DSMUN, DECODE(IDORIGDESTNF, 'L', FIL.DSMUN, FORN.DSMUN)) AS DSMUN,
                   DECODE(NVL(NF.IDORIGDESTNF, 'C'), 'C', CLI.SGEST, DECODE(IDORIGDESTNF, 'L', FIL.SGEST, FORN.SGEST)) AS SGEST,
                   VENC.NRSEQVENREC, VENC.DTATUAVENREC, VENC.VRATUAREC,
                   (SELECT NVL(SUM(VRTOTBCALCNF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                       AND IMPOSTO.CDIMPOSTO = '00'
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF)VR_BC_ICMS,
                   (SELECT NVL(SUM(VRTOTIMPONF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                       AND IMPOSTO.CDIMPOSTO = '10'
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF)VR_IPI,
                   (SELECT NVL(SUM(VRTOTIMPONF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                       AND IMPOSTO.CDIMPOSTO = '00'
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF)VR_ICMS,
                   (SELECT NVL(SUM(VRTOTBCALCNF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTO
                       AND IMPOSTO.CDIMPOSTO = '99'--ST
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF) VR_BC_ICMS_RET,
                   (SELECT NVL(SUM(VRTOTIMPONF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTO
                       AND IMPOSTO.CDIMPOSTO = '99'--ST
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF) VR_ICMS_RET,
                   (SELECT NVL(SUM(VRTOTBCALCNF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                       AND IMPOSTO.CDIMPOSTO = '10'
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF) VR_BC_IPI,
                   (SELECT NVL(SUM(VRIMPOSTOIT),0)
                      FROM ITEMPROIMPOS
                     WHERE CDFILIAL = NF.CDFILIAL
                       AND NRLANCTONF = NF.NRLANCTONF
                       AND ITEMPROIMPOS.CDFISCALIT IN('2','3')
                       AND CDIMPOSTO = '00' ) VR_BC_ICMS_INSENTO,
                   (SELECT SUM(NVL((QTITEMPROD*VRUNITITEM)- VRDESCITEM,0))
                      FROM ITEMPRODUTO
                     WHERE CDFILIAL = NF.CDFILIAL
                       AND NRLANCTONF = NF.NRLANCTONF) VR_LISTA_NEGA
              FROM NOTAFISCAL NF
                   LEFT JOIN (SELECT CL.NRINSJURCLIE, CL.CDCLIENTE, ENCLI.DSENDECLIE AS DSEND, MUN.NMMUNICIPIO AS DSMUN, ENCLI.SGESTADO AS SGEST
                                FROM CLIENTE CL, ENDECLIE ENCLI, MUNICIPIO MUN
                               WHERE CL.CDCLIENTE = ENCLI.CDCLIENTE
                                 AND ENCLI.CDPAIS = MUN.CDPAIS
                                 AND ENCLI.SGESTADO = MUN.SGESTADO
                                 AND ENCLI.CDMUNICIPIO = MUN.CDMUNICIPIO
                                 AND ENCLI.IDTPENDECLIE = 'P') CLI
                     ON CLI.CDCLIENTE = NF.CDCLIENTE
                   LEFT JOIN (SELECT CL.NRINSJURFORN, CL.CDFORNECED, ENCLI.DSENDEFORN AS DSEND, MUN.NMMUNICIPIO AS DSMUN, ENCLI.SGESTADO AS SGEST
                                FROM FORNECEDOR CL, ENDEFORN ENCLI, MUNICIPIO MUN
                               WHERE CL.CDFORNECED = ENCLI.CDFORNECED
                                 AND ENCLI.CDPAIS = MUN.CDPAIS
                                 AND ENCLI.SGESTADO = MUN.SGESTADO
                                 AND ENCLI.CDMUNICIPIO = MUN.CDMUNICIPIO
                                 AND ENCLI.IDTPENDEFORN = 'P') FORN
                     ON FORN.CDFORNECED = NF.CDFORNECED
                   LEFT JOIN (SELECT CL.NRINSJURFILI, CL.CDFILIAL, ENCLI.DSENDEFILI AS DSEND, MUN.NMMUNICIPIO AS DSMUN, ENCLI.SGESTADO AS SGEST
                                FROM FILIAL CL, ENDEFILI ENCLI, MUNICIPIO MUN
                               WHERE CL.CDFILIAL = ENCLI.CDFILIAL
                                 AND ENCLI.CDPAIS = MUN.CDPAIS
                                 AND ENCLI.SGESTADO = MUN.SGESTADO
                                 AND ENCLI.CDMUNICIPIO = MUN.CDMUNICIPIO
                                 AND ENCLI.IDTPENDEFILI = 'P') FIL
                     ON FIL.CDFILIAL = NF.CDFILIRECE
                   LEFT JOIN (SELECT CDFILIAL, NRLANCTONF, NRSEQVENREC, DTATUAVENREC, VRATUAREC
                                FROM CONTARECEBER
                               ORDER BY NRSEQVENREC) VENC
                     ON VENC.CDFILIAL   = NF.CDFILIAL
                    AND VENC.NRLANCTONF = NF.NRLANCTONF
             WHERE NF.IDENTRSAIDOP = 'S'
               AND NF.IDSTATUSNFE = 'A'
               AND NOT EXISTS (SELECT I.CDFILIAL
                                 FROM ITEMPRODUTO I, ITEMPEDIDO A
                                WHERE I.CDFILIAL   = NF.CDFILIAL
                                  AND I.NRLANCTONF = NF.NRLANCTONF
                                  AND A.CDFILIAL  = I.CDFILIALPED
                                  AND A.NRPEDIDO  = I.NRPEDIDO
                                  AND A.NRITEMPED = I.NRITEMPED)) NF
    UNION ALL
   /* NF Entrada */
   SELECT NF.DTENTRSAID AS DTPEDIDO,
          NF.DTEMISSAO AS DTEMISSAO,
          NF.NRACESSONFE AS NRACESSONFE,
          ' ' AS NRPEDIDO,
          (LPAD(' ',10)||/*FATPED*/
           LPAD(' ',8)||/*FATDATPED*/
           LPAD(NVL(NF.NRNOTAFISC,' '),9)||/*FATNUM*/
           LPAD(NVL(TO_CHAR(NF.DTEMISSAO,'DDMMYYYY'),' '),8)||/*FATDATNF*/
           LPAD(NF.NRINSJUR,20)||/*FATCLI*/
           LPAD(' ',5)||/*FATVEN*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.DSQTDEVOLU,'99999999.999'),'.','')),0),11,' ')||/*FATVOL*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_BC_ICMS,'999999999999.999'),'.','')),0),15,' ')||/*FATSUB*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_IPI,'99999999.999'),'.','')),0),11,' ')||/*FATIPI*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_ICMS,'9999999999.99'),'.','')),0),12,' ')||/*FATICMS*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC,'999999999999.999'),'.','')),0),15,' ')||/*FATTOT*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VRPBRUTOVOLU,'99999999.999'),'.','')),0),11,' ')||/*FATPESO*/
           LPAD(NVL(NF.NRINSJURTRAN,' '),20,' ')||/*FATTRANS*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_BC_ICMS_RET, '9999999999.999'),'.','')),0),13,' ')||/*FATDESC*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_ICMS_RET,'9999999999.999'),'.','')),0),13,' ')||/*FATICMRET*/
           LPAD(CASE WHEN NVL(NF.CDFISOPENTF,'') = '***' THEN '****' ELSE NVL(NF.CDFISOPENTF,' ') END,4,' ')||/*FATCODF*/
           RPAD(SUBSTR(NF.DSEND,0,40),40)||/*FATEND*/
           RPAD(SUBSTR(NF.DSMUN,0,30),30)||/*FATCID*/
           RPAD(SUBSTR(NF.SGEST,0,2),2)||/*FATEST*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_BC_IPI,'9999999999.999'),'.','')),0),13,' ')||/*BASEIPI*/
           LPAD(NVL(CASE WHEN NF.NRSEQVENPAG = '01' THEN TO_CHAR(NF.DTATUAVENPAG,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO1*/
           LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN NF.NRSEQVENPAG = '01' THEN NF.VRATUAPAG ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR1*/
           LPAD(NVL(CASE WHEN NF.NRSEQVENPAG = '02' THEN TO_CHAR(NF.DTATUAVENPAG,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO2*/
           LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN NF.NRSEQVENPAG = '02' THEN NF.VRATUAPAG ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR2*/
           LPAD(NVL(CASE WHEN NF.NRSEQVENPAG = '03' THEN TO_CHAR(NF.DTATUAVENPAG,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO3*/
           LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN NF.NRSEQVENPAG = '03' THEN NF.VRATUAPAG ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR3*/
           LPAD(NVL(CASE WHEN NF.NRSEQVENPAG = '04' THEN TO_CHAR(NF.DTATUAVENPAG,'DDMMYYYY') ELSE '' END,' '),8,' ')||/*VENCIMENTO4*/
           LPAD (LTRIM(REPLACE(TO_CHAR(CASE WHEN NF.NRSEQVENPAG = '04' THEN NF.VRATUAPAG ELSE 0 END,'999999999999.999'),'.',''),0),15,' ')||/*VALOR4*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_BC_ICMS_INSENTO,'999999999999.99'),'.','')),0),14,' ')||/*BICMISENTO*/
           RPAD(' ',20)||/*PEDCLI*/
           LPAD(' ',4)||/*TELECOD*/
           RPAD(NVL(NF.IDPGTOFRETE,' '),1)||/*FRETE*/
           LPAD(NVL(LTRIM(REPLACE(TO_CHAR(NF.VR_LISTA_NEGA,'999999999999.99'),'.','')),0),14,' ')||/*LISTANEGA*/
           LPAD(NVL(NF.NRACESSONFE,' '),44)||/*CHAVE*/
           NF.IDSTATUSNFE||/*IDSTATUSNFE*/
           ' '||/*IDSTATUPED*/
           RPAD(' ',10)/*NRPEDINTE*/) AS DADOS
      FROM (SELECT NF.DTENTRSAID, NF.NRNOTAFISC, NF.DTEMISSAO, NF.DSQTDEVOLU,
                   NF.VRNOTAFISC, NF.VRPBRUTOVOLU, NF.NRINSJURTRAN, NF.CDFISOPENTF,
                   NF.IDPGTOFRETE, NF.NRACESSONFE, NF.IDSTATUSNFE,
                   DECODE(NVL(NF.IDORIGDESTNF, 'F'), 'F', FORN.NRINSJURFORN, DECODE(IDORIGDESTNF, 'L', FIL.NRINSJURFILI, CLI.NRINSJURCLIE)) AS NRINSJUR,
                   DECODE(NVL(NF.IDORIGDESTNF, 'F'), 'F', FORN.DSEND, DECODE(IDORIGDESTNF, 'L', FIL.DSEND, CLI.DSEND)) AS DSEND,
                   DECODE(NVL(NF.IDORIGDESTNF, 'F'), 'F', FORN.DSMUN, DECODE(IDORIGDESTNF, 'L', FIL.DSMUN, CLI.DSMUN)) AS DSMUN,
                   DECODE(NVL(NF.IDORIGDESTNF, 'F'), 'F', FORN.SGEST, DECODE(IDORIGDESTNF, 'L', FIL.SGEST, CLI.SGEST)) AS SGEST,
                   VENC.NRSEQVENPAG, VENC.DTATUAVENPAG, VENC.VRATUAPAG,
                   (SELECT NVL(SUM(VRTOTBCALCNF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                       AND IMPOSTO.CDIMPOSTO = '00'
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF)VR_BC_ICMS,
                   (SELECT NVL(SUM(VRTOTIMPONF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                       AND IMPOSTO.CDIMPOSTO = '10'
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF)VR_IPI,
                   (SELECT NVL(SUM(VRTOTIMPONF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                       AND IMPOSTO.CDIMPOSTO = '00'
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF)VR_ICMS,
                   (SELECT NVL(SUM(VRTOTBCALCNF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTO
                       AND IMPOSTO.CDIMPOSTO = '99'--ST
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF) VR_BC_ICMS_RET,
                   (SELECT NVL(SUM(VRTOTIMPONF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTO
                       AND IMPOSTO.CDIMPOSTO = '99'--ST
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF) VR_ICMS_RET,
                   (SELECT NVL(SUM(VRTOTBCALCNF),0)
                      FROM IMPOSNFTOT, IMPOSTO
                     WHERE IMPOSNFTOT.CDIMPOSTO = IMPOSTO.CDIMPOSTONFE
                       AND IMPOSTO.CDIMPOSTO = '10'
                       AND IMPOSNFTOT.CDFILIAL = NF.CDFILIAL
                       AND IMPOSNFTOT.NRLANCTONF = NF.NRLANCTONF) VR_BC_IPI,
                   (SELECT NVL(SUM(VRIMPOSTOIT),0)
                      FROM ITEMPROIMPOS
                     WHERE CDFILIAL = NF.CDFILIAL
                       AND NRLANCTONF = NF.NRLANCTONF
                       AND ITEMPROIMPOS.CDFISCALIT IN('2','3')
                       AND CDIMPOSTO = '00' ) VR_BC_ICMS_INSENTO,
                   (SELECT SUM(NVL((QTITEMPROD*VRUNITITEM)- VRDESCITEM,0))
                      FROM ITEMPRODUTO
                     WHERE CDFILIAL = NF.CDFILIAL
                       AND NRLANCTONF = NF.NRLANCTONF) VR_LISTA_NEGA
              FROM NOTAFISCAL NF
                   LEFT JOIN (SELECT CL.NRINSJURCLIE, CL.CDCLIENTE, ENCLI.DSENDECLIE AS DSEND, MUN.NMMUNICIPIO AS DSMUN, ENCLI.SGESTADO AS SGEST
                                FROM CLIENTE CL, ENDECLIE ENCLI, MUNICIPIO MUN
                               WHERE CL.CDCLIENTE = ENCLI.CDCLIENTE
                                 AND ENCLI.CDPAIS = MUN.CDPAIS
                                 AND ENCLI.SGESTADO = MUN.SGESTADO
                                 AND ENCLI.CDMUNICIPIO = MUN.CDMUNICIPIO
                                 AND ENCLI.IDTPENDECLIE = 'P') CLI
                     ON CLI.CDCLIENTE = NF.CDCLIENTE
                   LEFT JOIN (SELECT CL.NRINSJURFORN, CL.CDFORNECED, ENCLI.DSENDEFORN AS DSEND, MUN.NMMUNICIPIO AS DSMUN, ENCLI.SGESTADO AS SGEST
                                FROM FORNECEDOR CL, ENDEFORN ENCLI, MUNICIPIO MUN
                               WHERE CL.CDFORNECED = ENCLI.CDFORNECED
                                 AND ENCLI.CDPAIS = MUN.CDPAIS
                                 AND ENCLI.SGESTADO = MUN.SGESTADO
                                 AND ENCLI.CDMUNICIPIO = MUN.CDMUNICIPIO
                                 AND ENCLI.IDTPENDEFORN = 'P') FORN
                     ON FORN.CDFORNECED = NF.CDFORNECED
                   LEFT JOIN (SELECT CL.NRINSJURFILI, CL.CDFILIAL, ENCLI.DSENDEFILI AS DSEND, MUN.NMMUNICIPIO AS DSMUN, ENCLI.SGESTADO AS SGEST
                                FROM FILIAL CL, ENDEFILI ENCLI, MUNICIPIO MUN
                               WHERE CL.CDFILIAL = ENCLI.CDFILIAL
                                 AND ENCLI.CDPAIS = MUN.CDPAIS
                                 AND ENCLI.SGESTADO = MUN.SGESTADO
                                 AND ENCLI.CDMUNICIPIO = MUN.CDMUNICIPIO
                                 AND ENCLI.IDTPENDEFILI = 'P') FIL
                     ON FIL.CDFILIAL = NF.CDFILIRECE
                   LEFT JOIN (SELECT CDFILIAL, NRLANCTONF, NRSEQVENPAG, DTATUAVENPAG, VRATUAPAG
                                FROM CONTAPAGAR
                               ORDER BY NRSEQVENPAG) VENC
                     ON VENC.CDFILIAL   = NF.CDFILIAL
                    AND VENC.NRLANCTONF = NF.NRLANCTONF
             WHERE NF.IDENTRSAIDOP = 'E'
               AND NF.IDSTATUSNFE = 'A'
               AND NOT EXISTS (SELECT I.CDFILIAL
                                 FROM ITEMPRODUTO I, ITEMPEDIDO A
                                WHERE I.CDFILIAL   = NF.CDFILIAL
                                  AND I.NRLANCTONF = NF.NRLANCTONF
                                  AND A.CDFILIAL  = I.CDFILIALPED
                                  AND A.NRPEDIDO  = I.NRPEDIDO
                                  AND A.NRITEMPED = I.NRITEMPED)) NF
)                                  
/

-- VERSAO --
BEGIN ATUVEROBJ('ALM_PEDIDO_NF','6.35.004'); END;
/

