Buscar NF em todas as tabelas

[Former Member]

Sr.,

Gostaria de fazer uma query para procurar  NF por  numero de serie da nota independente da tabela.

Exemplo: Queria que o resultado fosse todas as NF com o numero 1010 na serie.

Fiz a query abaixo mais não esta retornando o resultado esperado.

SELECT T0.[DocNum],T0.[Serial], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM OINV T0 WHERE T0.[Serial] =[%0]
UNION
SELECT T0.[DocNum],T0.[Serial], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM ODLN T0 WHERE T0.[Serial] =[%0]
UNION
SELECT T0.[DocNum],T0.[Serial], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM ORDN T0 WHERE T0.[Serial] =[%0]
UNION
SELECT T0.[DocNum],T0.[Serial], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM ORIN T0 WHERE T0.[Serial] =[%0]
UNION
SELECT T0.[DocNum],T0.[Serial], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM OPDN T0 WHERE T0.[Serial] =[%0]
UNION
SELECT T0.[DocNum],T0.[Serial], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM ORPD T0 WHERE T0.[Serial] =[%0]
UNION
SELECT T0.[DocNum],T0.[Serial], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM OPCH T0 WHERE T0.[Serial] =[%0]
UNION
SELECT T0.[DocNum],T0.[Serial], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM ORPC T0 WHERE T0.[Serial] =[%0]

[eliezer_egm · Accepted solution]

Fala Herenildo boa tarde,

Eu uso uma query para controlar as NFe de um cliente, espero que te ajude.

O select abaixo lista todas as notas com a sequencia 29 que é a de NFe em ordem e se está autorizada ou cancelada:

Select docentry, Serial AS NFe, TipoNota = 'Nota de Saída', Case CANCELED When 'N'Then 'Autorizada' Else 'Inutilizada/Cancelada' END Status

from OINV

where canceled <> 'C' and SeqCode = 29

  UNION

Select docentry, Serial AS NFe, TipoNota = 'Dev. Saída', Case CANCELED When 'N'Then 'Autorizada' Else 'Inutilizada/Cancelada' END Status 

from ORIN

where canceled <> 'C' and SeqCode = 29

  UNION

Select docentry, Serial AS NFe, TipoNota = 'Nota de Entrega', Case CANCELED When 'N'Then 'Autorizada' Else 'Inutilizada/Cancelada' END Status 

from ODLN

where canceled <> 'C' and SeqCode = 29 

  UNION

Select docentry, Serial AS NFe, TipoNota = 'Devolução', Case CANCELED When 'N'Then 'Autorizada' Else 'Inutilizada/Cancelada' END Status 

from ORDN

where canceled <> 'C' and SeqCode = 29

  UNION

Select docentry, Serial AS NFe, TipoNota = 'Nota de Entrada', Case CANCELED When 'N'Then 'Autorizada' Else 'Inutilizada/Cancelada' END Status 

from OPCH

where canceled <> 'C' and SeqCode = 29

  UNION

Select docentry, Serial AS NFe, TipoNota = 'Recebimento', Case CANCELED When 'N'Then 'Autorizada' Else 'Inutilizada/Cancelada' END Status  

from OPDN

where canceled <> 'C' and SeqCode = 29

  UNION

Select docentry, Serial AS NFe, TipoNota = 'Dev. Entrada', Case CANCELED When 'N'Then 'Autorizada' Else 'Inutilizada/Cancelada' END Status 

from ORPC

where canceled <> 'C' and SeqCode = 29

  UNION

Select docentry, Serial AS NFe, TipoNota = 'Dev. Mercadorias', Case CANCELED When 'N'Then 'Autorizada' Else 'Inutilizada/Cancelada' END Status 

from ORPD

where canceled <> 'C' and SeqCode = 29

Order By Serial  desc

Ficara desta forma no B1:

Espero que te ajude.

Att.

Eliezer

[Former Member · Accepted solution]

Eliezer,

Muito obrigado sua query me ajudou muito com ela conseguir aplicar da forma que queria e esta funcionando perfeitamente.

Select t0.docentry, t0.Serial AS NFe,CARDCODE AS COD, CARDNAME AS NOME, TipoNota = 'Nota de Saída',

Case t0.CANCELED When 'N'Then'Autorizada' Else 'Inutilizada/Cancelada' END Status,

Case t0.seqCode when 72 then 'Pituba' when 71 then 'Lauro' Else 'Vasco' END Loja

from OINV t0

where t0.[Serial] =[%0] and canceled <> 'C' and (SeqCode = 72 or SeqCode = 63 or SeqCode = 71)

UNION

Select t0.docentry, t0.Serial AS NFe,CARDCODE AS COD, CARDNAME AS NOME,TipoNota = 'Dev. Saída',

Case t0.CANCELED When 'N'Then'Autorizada' Else 'Inutilizada/Cancelada' END Status,

Case t0.seqCode when 72 then 'Pituba' when 71 then 'Lauro' Else 'Vasco' END Loja

from ORIN t0

where t0.[Serial] =[%0] and canceled <> 'C' and (SeqCode = 72 or SeqCode = 63 or SeqCode = 71)

UNION

Select t0.docentry, t0.Serial AS NFe,CARDCODE AS COD, CARDNAME AS NOME, TipoNota = 'Nota de Entrega',

Case t0.CANCELED When 'N'Then'Autorizada' Else 'Inutilizada/Cancelada' END Status,

Case t0.seqCode when 72 then 'Pituba' when 71 then 'Lauro' Else 'Vasco' END Loja

from ODLN t0

where t0.[Serial] =[%0] and canceled <> 'C' and (SeqCode = 72 or SeqCode = 63 or SeqCode = 71)

UNION

Select t0.docentry, t0.Serial AS NFe,CARDCODE AS COD, CARDNAME AS NOME, TipoNota = 'Devolução',

Case t0.CANCELED When 'N'Then'Autorizada' Else 'Inutilizada/Cancelada' END Status,

Case t0.seqCode when 72 then 'Pituba' when 71 then 'Lauro' Else 'Vasco' END Loja

from ORDN t0

where t0.[Serial] =[%0] and canceled <> 'C' and (SeqCode = 72 or SeqCode = 63 or SeqCode = 71)

UNION

Select t0.docentry, t0.Serial AS NFe,CARDCODE AS COD, CARDNAME AS NOME,TipoNota = 'Nota de Entrada',

Case t0.CANCELED When 'N'Then'Autorizada' Else 'Inutilizada/Cancelada' END Status,

Case t0.seqCode when 72 then 'Pituba' when 71 then 'Lauro' Else 'Vasco' END Loja

from OPCH t0

where t0.[Serial] =[%0] and canceled <> 'C' and (SeqCode = 72 or SeqCode = 63 or SeqCode = 71 OR SeqCode = -2) AND MODEL=39

UNION

Select t0.docentry, t0.Serial AS NFe,CARDCODE AS COD, CARDNAME AS NOME,TipoNota = 'Recebimento',

Case t0.CANCELED When 'N'Then'Autorizada' Else 'Inutilizada/Cancelada' END Status,

Case t0.seqCode when 72 then 'Pituba' when 71 then 'Lauro' Else 'Vasco' END Loja

from OPDN t0

where t0.[Serial] =[%0] and canceled <> 'C' and (SeqCode = 72 or SeqCode = 63 or SeqCode = 71)

UNION

Select t0.docentry, t0.Serial AS NFe,CARDCODE AS COD, CARDNAME AS NOME, TipoNota = 'Dev. Entrada',

Case t0.CANCELED When 'N'Then'Autorizada' Else 'Inutilizada/Cancelada' END Status,

Case t0.seqCode when 72 then 'Pituba' when 71 then 'Lauro' Else 'Vasco' END Loja

from ORPC t0

where t0.[Serial] =[%0] and canceled <> 'C' and (SeqCode = 72 or SeqCode = 63 or SeqCode = 71)

UNION

Select t0.docentry, t0.Serial AS NFe,CARDCODE AS COD, CARDNAME AS NOME, TipoNota = 'Dev. Entrada',

Case t0.CANCELED When 'N'Then'Autorizada' Else 'Inutilizada/Cancelada' END Status,

Case t0.seqCode when 72 then 'Pituba' when 71 then 'Lauro' Else 'Vasco' END Loja

from ORPC t0

where t0.[Serial] =[%0] and canceled <> 'C' and (SeqCode = 72 or SeqCode = 63 or SeqCode = 71)

UNION

Select t0.docentry, t0.Serial AS NFe,CARDCODE AS COD, CARDNAME AS NOME, TipoNota = 'Dev. Mercadorias',

Case t0.CANCELED When 'N'Then'Autorizada' Else 'Inutilizada/Cancelada' END Status,

Case t0.seqCode when 72 then 'Pituba' when 71 then 'Lauro' Else 'Vasco' END Loja

from ORPD t0

where t0.[Serial] =[%0] and canceled <> 'C' and (SeqCode = 72 or SeqCode = 63 or SeqCode = 71)

Order By Serial  desc