on 07-23-2012 4:00 AM
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.
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ída
SELECT
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 T0
INNER 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
-- Entrega
SELECT
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 Entrada
SELECT
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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'
User | Count |
---|---|
99 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.