Relatório de Vendas com PIS/COFINS detalhado!!

[Former Member]

Amigos,

Montei a seguinte consulta na qual tenho todas as minhas vendas, dentro de um período, com informações básicas (data, valor, cliente, produto, total por linha e etc...)

SELECT T0.[DocDate], T0.[CardCode],T0.[CardName], T0.[Serial],T1.[CFOPCode], T1.[ItemCode], T1.[Dscription], T1.[U_SKILL_UT], T1.[Quantity], T1.[Price], T1.[LineTotal], T0.[Comments]

FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] >='[%0]' AND  T0.[DocDate] <= '[%1]'

Gostaria de incluir as linhas de PIS e COFINS que está sendo cobrada nos itens vendidos... (Não vendo com ICMS e IPI) que estão nesse print:

Alguém pode me ajudar??

Não consegui achar os campos que armazenam essas informações.

Obrigaduuu!!!

[Former Member · Accepted solution]

Boa tarde! Tudo bem?

Tente rodar esta Query

SELECT T0.[DocDate], T0.[CardCode],T0.[CardName], T0.[Serial],T1.[CFOPCode], T1.[ItemCode], T1.[Dscription],  T1.[U_SKILL_UT],

T1.[Quantity], T1.[Price], T1.[LineTotal], T0.[Comments], CASE when StaCode like 'CF%' then TaxSum else 0 end as 'CONFINS',

CASE when StaCode like 'pi%' then TaxSum else 0 end as 'PIS'

FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

              INNER JOIN INV4 T2 ON T2.DocEntry = T0.DocEntry

              WHERE T0.[DocDate] >='[%0]' AND  T0.[DocDate] <= '[%1]' and StaCode like 'CF%' or StaCode like 'PI%'

Att.

Mayara

[Former Member · Accepted solution]

Raphael

Bom dia, estou em busca de uma consulta que me traga basicamente as informações que você usou na consulta acima, porem alem dos impostos normais eu tenho mais 2 (PPB e PTA) na qual preciso que incluir no report.

Você pode me ajudar no complemento dessa informação para que eu possa usar a sua consulta?

Desde já agradeço

[Former Member · Accepted solution]

Fala Ivan...

Poderia explicar o q seria PPB e PTA?

[Former Member · Accepted solution]

Vamos lá:

PTA: é uma "imposto" configurado no B1 que me permite provisionar como valor a recuperar parte do ICMS da venda.

PPB:  é uma "imposto" configurado no B1 que me permite provisionar como valor a recuperar parte do IPI.

Ambos fazem parte da minha combinação de imposto, e se baseiam em percentuais para calculo como os demais impostos.

Então eu preciso de um report que me traga de forma detalhada as notas de saída ( exceto as copiadas para Devolução) com esses dados, que ao meu ver seria um complemento da sua consulta acima, alem do campo de quantidade.

Caso precise visualizar, por favor me mande seu contato.

[Former Member · Accepted solution]

Ivan,

a tabela que armazena os impostos é a INV4, porém para esses impostos que você criou, é necessário saber em qual tabela os criou...

Visto isso, adicione os campos referente na consulta (ex: Tx.[Campo_PPA],Tx.[Campo_PTB],) na sua consulta.

Acredito que isso atenderia sua necessidade....

[Former Member · Accepted solution]

Raphael

Obrigada pela ajuda, deu certo, consulta trazendo os campos devidos.

Só preciso de mais uma ajuda, percebi que sua consulta nao considera notas canceladas, o que esta correto. Porem tenho casos aqui na empresa que preciso cancelar uma nota mesmo após o prazo estipulado de 24hs.

Nesse caso copio a nota de saida p/ devolução de nota de saida, mas preciso validar essa nota na sefaz, ou seja, eu gero uma nota para entrada ( essa nota sua consulta nao esta considerando visto que esta vinculado a um documento de Devolução, mas eu preciso desconsiderar também a nota de saida gerada,

Você pode me ajudar a incluir na consulta essa regra (  nota de saida vinculada a devolução,) será desconsiderada para o Report?

[Former Member · Accepted solution]

Para isto acredito que colocando uma condição WHERE T0.[CANCELED] = 'n' só puxe as notas emitidas e ignore as canceladas...

tente fazer assim e caso funcione, poderia marcar a resposta como "Correct Answer" ou Helpful ???

abs!

[Former Member · Accepted solution]

Raphael

Boa noite, o comando acima eu já tinha feito, talvez não soube me expressar bem na pergunta acima, eu preciso saber como inserir no meu report as NF de devolucao de saída (apenas as que possuem variável SeqCode=29) estou tentando adicionar um select na ORIN mas nao está retornando as NF.

[Former Member · Accepted solution]

Envia teu select por aqui...

[Former Member · Accepted solution]

SELECT

T0.DocEntry,

(ISNULL((SELECT TOP 1 T4.NfmName FROM ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + ISNULL(T0.SeriesStr,'') + ' / ' + CAST(T0.Serial AS VARCHAR) ) as 'Documento',

T1.LineNum,

T1.ItemCode,

T3.ItemName,       

T0.DocDate,

T0.CardCode,

T0.CardName,  

T1.Quantity,

CASE WHEN MAX(T0.DiscSum) > 0 then

((SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum)) - ((SUM(T1.[TotalSumSy]) / (max(T0.DocTotal)+max(T0.DiscSum))) * max(T0.DiscSum)) )

/ SUM(T1.Quantity)

ELSE

(SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum) )

/ SUM(T1.Quantity)

END AS ValorTotal,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS BaseICMS,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS AliqICMS,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS ValorICMS,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PTA%') AS BasePTA,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PTA%') AS AliqPTA,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PTA%') AS ValorPTA,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PPB%') AS BasePPB,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PPB%') AS AliqPPB,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PPB%') AS ValorPPB,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS BaseIPI,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS AliqIPI,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS ValorIPI,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS BasePIS,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS AliqPIS,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS ValorPIS,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS BaseCOF,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS AliqCOF,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS ValorCOF

FROM OINV T0

INNER JOIN INV1 T1 ON (T1.DocEntry = T0.DocEntry)

INNER JOIN OITM T3 ON (T3.Itemcode = T1.Itemcode)

LEFT JOIN ONFM T2 ON T2.AbsEntry = T0.Model

WHERE

ISNULL(T2.NfmCode,'') NOT IN ('Outra', 'Nada', 'Fora', 'FAT', '') -- nao entra notas sem modelo definido

AND T0.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 = 1) -- Não entra nota cancelada (SAP 8.82)

AND T0.CANCELED = 'N' -- Não entra nota cancelada (SAP 9)

AND T0.DocDate BETWEEN '20140101' AND '20141212'

GROUP BY

T0.DocEntry,

T1.LineNum,

T0.SeqCode,

T0.DocDate,

T0.CardCode,

T0.CardName,

T1.CFOPCode,

T0.Model,

T0.SeriesStr,

T0.Serial,

T2.NfmCode,

T1.ItemCode,

T3.ItemName,

T1.Quantity

ORDER BY T0.DocEntry, T1.LineNum

[Former Member · Accepted solution]

Entendi...

Tenta adicionar um inner join com a ORIN... Adiciona a tabela ORIN na consulta (OINV T0  INNER JOIN ORIN Tx ON Tx.[DocEntry] = Tx.[AnnInvDecR]) e adiciona outra condição de WHERE Tx.[CANCELED] = 'n' ......

Não sei se entendi direito, mas pela minha lógica isso atenderia, pois iria incluir a tabela ORIN na consulta e as notas canceladas dela...

[Former Member · Accepted solution]

Raphael,

Boa tarde, fiz o que você sugeriu: inserir a inner join conforme solicitado e usei a condição do WHERE porem para versão 8.82 ( a versão que uso). No entanto a consulta está retornando que não existem os critérios de seleção (mas existe pelo menos 1 devolução de nota fiscal de saída com seqCode = 29.

Outra coisa que percebi que na versão 8.82 a coluna AnnInvDecR = NULL , a coluna que é igual ao DocEntry = DocNum ( Já alterei o inner usando essa condição. porem a consulta retorna a o mesmo resultado inicial, ou seja, sem as notas Devolução com seqCode=29)

SELECT

T0.DocEntry,

(ISNULL((SELECT TOP 1 T4.NfmName FROM ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + ISNULL(T0.SeriesStr,'') + ' / ' + CAST(T0.Serial AS VARCHAR) ) as 'Documento',

T1.LineNum,

T1.ItemCode,

T3.ItemName,       

T0.DocDate,

T0.CardCode,

T0.CardName,  

T1.Quantity,

CASE WHEN MAX(T0.DiscSum) > 0 then

((SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum)) - ((SUM(T1.[TotalSumSy]) / (max(T0.DocTotal)+max(T0.DiscSum))) * max(T0.DiscSum)) )

/ SUM(T1.Quantity)

ELSE

(SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum) )

/ SUM(T1.Quantity)

END AS ValorTotal,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS BaseICMS,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS AliqICMS,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS ValorICMS,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PTA%') AS BasePTA,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PTA%') AS AliqPTA,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PTA%') AS ValorPTA,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PPB%') AS BasePPB,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PPB%') AS AliqPPB,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PPB%') AS ValorPPB,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS BaseIPI,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS AliqIPI,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS ValorIPI,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS BasePIS,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS AliqPIS,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS ValorPIS,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS BaseCOF,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS AliqCOF,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS ValorCOF

FROM OINV T0

INNER JOIN ORIN T5 ON (T5.DocEntry = T5.AnnInvDecR)

INNER JOIN INV1 T1 ON (T1.DocEntry = T0.DocEntry)

INNER JOIN OITM T3 ON (T3.Itemcode = T1.Itemcode)

LEFT JOIN ONFM T2 ON T2.AbsEntry = T0.Model

WHERE

ISNULL(T2.NfmCode,'') NOT IN ('Outra', 'Nada', 'Fora', 'FAT', '') -- nao entra notas sem modelo definido

AND T0.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 = 1) -- Não entra nota cancelada (SAP 8.82)

AND T5.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 = 1) -- Não entra nota cancelada (SAP 8.82)

AND T0.CANCELED = 'N' -- Não entra nota cancelada (SAP 9)

AND T0.DocDate BETWEEN '20140101' AND '20141212'

GROUP BY

T0.DocEntry,

T1.LineNum,

T0.SeqCode,

T0.DocDate,

T0.CardCode,

T0.CardName,

T1.CFOPCode,

T0.Model,

T0.SeriesStr,

T0.Serial,

T2.NfmCode,

T1.ItemCode,

T3.ItemName,

T1.Quantity

ORDER BY T0.DocEntry, T1.LineNum

[fbio_bilicki]

Boa tarde,

segue abaixo uma consulta que fiz para ler algumas notas de saída, espero que ajude.

A tabela de imposto é a INV4

SELECT 
    T0.DocEntry,
    (ISNULL((SELECT TOP 1 T4.NfmName FROM ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + ISNULL(T0.SeriesStr,'') + ' / ' + CAST(T0.Serial AS VARCHAR) ) as 'Documento',
    T1.LineNum,
    T1.ItemCode,
    T3.ItemName,        
    T0.DocDate,
    T0.CardCode,
    T0.CardName,   
    CASE WHEN MAX(T0.DiscSum) > 0 then
        ((SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum)) - ((SUM(T1.[TotalSumSy]) / (max(T0.DocTotal)+max(T0.DiscSum))) * max(T0.DiscSum)) ) 
        / SUM(T1.Quantity)
    ELSE
        (SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum) ) 
        / SUM(T1.Quantity)
    END AS ValorTotal,

    (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS BaseICMS,
    (SELECT MAX(T8.TaxRate)                    FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS AliqICMS,
    (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS ValorICMS,
    (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS BaseIPI,
    (SELECT MAX(T8.TaxRate)                    FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS AliqIPI,
    (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS ValorIPI,
    (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS BasePIS,
    (SELECT MAX(T8.TaxRate)                    FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS AliqPIS,
    (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS ValorPIS,
    (SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS BaseCOF,
    (SELECT MAX(T8.TaxRate)                    FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS AliqCOF,
    (SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS ValorCOF

FROM OINV T0
INNER JOIN INV1 T1 ON (T1.DocEntry = T0.DocEntry)
INNER JOIN OITM T3 ON (T3.Itemcode = T1.Itemcode)
LEFT JOIN ONFM T2 ON T2.AbsEntry = T0.Model

WHERE
    ISNULL(T2.NfmCode,'') NOT IN ('Outra', 'Nada', 'Fora', '') -- nao entra notas sem modelo definido
    AND T0.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 = 1) -- Não entra nota cancelada (SAP 8.82)
    AND T0.CANCELED = 'N' -- Não entra nota cancelada (SAP 9)
    AND T0.DocDate BETWEEN '01/01/2014' AND '31/01/2014'

GROUP BY
    T0.DocEntry,
    T1.LineNum,
    T0.SeqCode,
    T0.DocDate,
    T0.CardCode,
    T0.CardName,
    T1.CFOPCode,
    T0.Model,
    T0.SeriesStr,
    T0.Serial,
    T2.NfmCode,
    T1.ItemCode,
    T3.ItemName

ORDER BY T0.DocEntry, T1.LineNum

Abraço

Fabio

SAP 8.82 PL16 / SAP 9.0 PL11 (Test)

[Former Member]

Excelente Fábio, porém preciso que o sistema me dê a opção de escolher a data... Onde altero? (não sou expert em sql rsrs)

[fbio_bilicki]

Ola Raphael,

se você for usar no Crystal Reports, tem que criar dois parametros, data inicial e final, então troque só no BETWEEN na consulta acima.

... BETWEEN {?Dataini} AND {?Datafim}

Se for usar como uma consulta dentro do SAP, troque para:

...BETWEEN [%0] AND [%1]

Só que infelizmente no meu SAP deu erro, da a impressão que ele não interpreta alguns comandos em SQL complexos, muitas vezes tive que refazer o SQL ou deixar dentro de algum relatório no Crystal Reports por causa desse erro.

o meu apresenta o erro

"1). [Microsoft][SQL Server Native Client 10.0][SQL Server]A conversão de um tipo de dados varchar em um tipo de dados datetime resultou em um valor fora do intervalo.

'Contrato guarda-chuva' (OOAT)".

Já tentei até convertendo o parâmetro [%0] pra data mas não vai, vou tentar fazer algumas alterações nesse comando.

Abraço

Fabio

[Former Member]

Fala Fábio, pra mim tb deu esse erro, porém verifiquei na query que era o formato de data...

Trocar o tipo de data de '01/01/2014' para o formato '20140101'

Ficou assim e rodou dentro do SAP.

SELECT

T0.DocEntry,

(ISNULL((SELECT TOP 1 T4.NfmName FROM ONFM T4 WHERE T4.AbsEntry = T0.Model),'') + ' / ' + ISNULL(T0.SeriesStr,'') + ' / ' + CAST(T0.Serial AS VARCHAR) ) as 'Documento',

T1.LineNum,

T1.ItemCode,

T3.ItemName,       

T0.DocDate,

T0.CardCode,

T0.CardName,  

CASE WHEN MAX(T0.DiscSum) > 0 then

((SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum)) - ((SUM(T1.[TotalSumSy]) / (max(T0.DocTotal)+max(T0.DiscSum))) * max(T0.DiscSum)) )

/ SUM(T1.Quantity)

ELSE

(SUM(T1.[TotalSumSy]) + SUM(T1.[VatSum]) + SUM(T1.DistribSum) )

/ SUM(T1.Quantity)

END AS ValorTotal,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS BaseICMS,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS AliqICMS,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'ICM%') AS ValorICMS,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS BaseIPI,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS AliqIPI,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'IPI%') AS ValorIPI,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS BasePIS,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS AliqPIS,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'PIS%') AS ValorPIS,

(SELECT SUM(T8.BaseSum) / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS BaseCOF,

(SELECT MAX(T8.TaxRate) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS AliqCOF,

(SELECT SUM(T8.TaxSum)  / SUM(T1.Quantity) FROM INV4 T8 WHERE T8.DocEntry = T0.DocEntry AND T8.LineNum = T1.LineNum AND T8.StaCode LIKE 'COF%') AS ValorCOF

FROM OINV T0

INNER JOIN INV1 T1 ON (T1.DocEntry = T0.DocEntry)

INNER JOIN OITM T3 ON (T3.Itemcode = T1.Itemcode)

LEFT JOIN ONFM T2 ON T2.AbsEntry = T0.Model

WHERE

ISNULL(T2.NfmCode,'') NOT IN ('Outra', 'Nada', 'Fora', '') -- nao entra notas sem modelo definido

AND T0.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 = 1) -- Não entra nota cancelada (SAP 8.82)

AND T0.CANCELED = 'N' -- Não entra nota cancelada (SAP 9)

AND T0.DocDate BETWEEN '20140101' AND '20141212'



GROUP BY

T0.DocEntry,

T1.LineNum,

T0.SeqCode,

T0.DocDate,

T0.CardCode,

T0.CardName,

T1.CFOPCode,

T0.Model,

T0.SeriesStr,

T0.Serial,

T2.NfmCode,

T1.ItemCode,

T3.ItemName

ORDER BY T0.DocEntry, T1.LineNum

[Former Member]

Oi Fabio, tudo bem?

Estou usando a consulta que você disponibilizou acima, porem estou tentando trazer para ela as notas de Devolução de Nota de saída apenas com SeqCode=29..

Segui algumas dicas que nossa amigo Raphael me mandou, porem não obtive sucesso.

Será que você pode me ajudar?

Desde já agradeço.

[fbio_bilicki]

Boa tarde Ivan,

Você deve trocar todos os arquivos para devolução de nota de saída, ou seja, tudo que for OINV (ou INV), deve trocar para ORIN (RIN).

as devoluções de nota ficam dentro essas tabelas,

Abraço

Fabio.

[Former Member]

Oi Fabio

Boa tarde, obrigada pela ajuda..agora deu certo, problema resolvido!!!

Abraço