Skip to Content
author's profile photo Former Member
Former Member

Consulta Formatada:

Boa noite a todos!!

Preciso criar uma consulta formatada que liste todas as NF emitidas, com excessao das NF que foram canceladas. Ex.: A consulta deve listar: Numero da NF, Data, Valor do documento e Utilizacao e o nome do cliente. Alguem saberia me ajudar. Obrigado.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 23, 2012 at 07:00 PM

    César bom dia!

    Vê se essa consulta te ajuda

    select distinct oinv.DocNum as [doc SAP], oinv.Serial as nf, oinv.CardCode as [cod forn], OINV.CardName as Forn, oinv.DocTotal as valor, REPLACE(CONVERT(VARCHAR,OINV.DocDate,6),' ','-')as data from oinv inner join INV1 on oinv.DocEntry = INV1.DocEntry where inv1.TargetType != '14'

    Eric

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Segue consulta com utilização, se uma nota tiver mais de uma utilização ela irá aparecer 2 vezes na lista

      select distinct oinv.DocNum as [doc SAP], oinv.Serial as nf, ousg.Usage as utilizacao, oinv.CardCode as [cod forn], OINV.CardName as Forn, oinv.DocTotal as valor, REPLACE(CONVERT(VARCHAR,OINV.DocDate,6),' ','-')as data from oinv inner join INV1 on oinv.DocEntry = INV1.DocEntry inner join OUSG on inv1.Usage = ousg.ID where inv1.TargetType != '14'

  • Posted on Aug 21, 2012 at 02:21 AM

    Cesar, boa noite.

    Você deve tomar cuidado com os seguintes casos

    Nota de SAIDA

    OINV - Cabeçalho

    INV1 - Detalhe.

    Se você fizer uma entrega, deverá também ler o

    ODLN - Cabeçalho

    DLN1 - Detalhe.

    E se você fizer uma devolução de uma nota de compra, deverá ler também

    ORPC - Cabeçalho

    RPC1 - Detalhe.

    Além disso, o valor total não está gravado no SAP (pelo menos eu não encontrei), tive que somar o valor dos produtos + impostos (IPI e ST) - Descontos pra saber o valor total da nota.

    NOTAS CANCELADAS

    - Pra saber se a nota está cancelada, você deve ler se existe a devolução dela, e se essa devolução está "cancelad", o SQL abaixo faz isso.

    Segue a SQL que tive que criar na empresa pra ler as notas fiscais de saída, faz tudo que comentei acima, se alguém encontrar algum erro me avise por gentileza. Estou usando até o momento esta consulta.

    -- Nota Fiscal de SaídaSELECT     T1.[CardCode],     T1.[CardName],     (ISNULL(T1.SeriesStr,'')) as 'Serie',    T1.SERIAL as 'NumeroNF',    T1.[DocDate] as DataEmissao,         T2.[Usage],     T0.[CFOPCode],     SUM(T0.[TotalSumSy]) as ValorProdutos,     SUM(T0.[VatSum]) as ValorIPIeST,    SUM(T0.DistribSum) AS FreteSegOut,     Case WHEN MAX(T1.DiscSum) > 0 then      ((SUM(T0.[TotalSumSy]) / (max(T1.DocTotal)+max(T1.DiscSum))) * max(T1.DiscSum))     else      sum(0)    end as Desconto,    Case WHEN MAX(T1.DiscSum) > 0 then      (SUM(T0.[TotalSumSy]) + SUM(T0.[VatSum]) + SUM(T0.DistribSum)) - ((SUM(T0.[TotalSumSy]) / (max(T1.DocTotal)+max(T1.DiscSum))) * max(T1.DiscSum))     else      (SUM(T0.[TotalSumSy]) + SUM(T0.[VatSum]) + SUM(T0.DistribSum))    end as ValorNota FROM INV1 T0INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OUSG T2 ON T0.Usage = T2.ID WHERE T1.[Model] > 0 and T1.DocEntry NOT IN (SELECT T4.BaseEntry FROM ORIN T3 INNER JOIN RIN1 T4 ON T3.DocEntry = T4.DocEntry WHERE T4.BaseEntry IS NOT NULL and  T3.SeqCode <> -2)GROUP BY T1.[CardCode], T1.[CardName], T1.[DocDate], T1.[SeriesStr], T1.[Serial], T2.[Usage], T0.[CFOPCode]union-- EntregaSELECT     T1.[CardCode],     T1.[CardName],     (ISNULL(T1.SeriesStr,'')) as 'Serie',    T1.SERIAL as 'NumeroNF',    T1.[DocDate] as DataEmissao,        T2.[Usage], T0.[CFOPCode],     SUM(T0.[TotalSumSy]) as ValorProdutos,     SUM(T0.[VatSum]) as ValorIPIeST,    SUM(T0.DistribSum) AS FreteSegOut,     Case WHEN MAX(T1.DiscSum) > 0 then      ((SUM(T0.[TotalSumSy]) / (max(T1.DocTotal)+max(T1.DiscSum))) * max(T1.DiscSum))     else      sum(0)    end as Desconto,    Case WHEN MAX(T1.DiscSum) > 0 then      (SUM(T0.[TotalSumSy]) + SUM(T0.[VatSum]) + SUM(T0.DistribSum)) - ((SUM(T0.[TotalSumSy]) / (max(T1.DocTotal)+max(T1.DiscSum))) * max(T1.DiscSum))     else      (SUM(T0.[TotalSumSy]) + SUM(T0.[VatSum]) + SUM(T0.DistribSum))    end as ValorNota FROM DLN1 T0  INNER JOIN ODLN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OUSG T2 ON T0.Usage = T2.ID WHERE T1.[Model] > 0 and T1.DocEntry NOT IN (SELECT T4.BaseEntry FROM ORDR T3 INNER JOIN RDN1 T4 ON T3.DocEntry = T4.DocEntry WHERE T4.BaseEntry IS NOT NULL and T3.SeqCode <> -2)GROUP BY T1.[CardCode], T1.[CardName], T1.[DocDate], T1.[SeriesStr], T1.[Serial], T2.[Usage], T0.[CFOPCode]union-- Dev. NF EntradaSELECT     T1.[CardCode],     T1.[CardName],     (ISNULL(T1.SeriesStr,'')) as 'Serie',    T1.SERIAL as 'NumeroNF',    T1.[DocDate] as DataEmissao,        T2.[Usage], T0.[CFOPCode],     SUM(T0.[TotalSumSy]) as ValorProdutos,     SUM(T0.[VatSum]) as ValorIPIeST,    SUM(T0.DistribSum) AS FreteSegOut,     Case WHEN MAX(T1.DiscSum) > 0 then      ((SUM(T0.[TotalSumSy]) / (max(T1.DocTotal)+max(T1.DiscSum))) * max(T1.DiscSum))     else      sum(0)    end as Desconto,    Case WHEN MAX(T1.DiscSum) > 0 then      (SUM(T0.[TotalSumSy]) + SUM(T0.[VatSum]) + SUM(T0.DistribSum)) - ((SUM(T0.[TotalSumSy]) / (max(T1.DocTotal)+max(T1.DiscSum))) * max(T1.DiscSum))     else      (SUM(T0.[TotalSumSy]) + SUM(T0.[VatSum]) + SUM(T0.DistribSum))    end as ValorNota FROM RPC1 T0  INNER JOIN ORPC T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OUSG T2 ON T0.Usage = T2.ID WHERE T1.[Model] > 0 GROUP BY T1.[CardCode], T1.[CardName], T1.[DocDate], T1.[SeriesStr], T1.[Serial], T2.[Usage], T0.[CFOPCode]

    Atenciosamente,

    Fabio

    SAP 8.82 PL6HF1

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.