on 01-21-2014 1:47 PM
Srs.,
Qual o campo que relaciona as tabelas de Pedido de venda e a de NF de Saida (ORDR e OINV)?
Grato.
Primeiro você tem que fazer um JOIN com as linhas da RDR1 com INV1 para depois ligar na OINV
Segue:
Select * from ORDR
INNER JOIN RDR1 ON ORDR.DOCENTRY = RDR1.DocEntry
INNER JOIN INV1 ON INV1.BaseRef = RDR1.DocEntry
INNER JOIN OINV ON OINV.DocEntry = INV1.Docentry
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Amigos,
Boa tarde.
Como relaciono OOPR com OCLG ?
E como descubro isso na pratica qual chave com qual ?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Prezados, bom dia!
Segue a minha contribuição sobre este caso
---
SELECT T0.DOCNUM AS [PEDIDO VENDA] ,CONVERT(VARCHAR(10),T0.CreateDate,103) AS [DATA CRIAÇÃO] ,T1.ItemCode AS [COD. ITEM] ,T1.Dscription AS [DESCRIÇÃO] ,T1.Quantity AS [QUANTIDADE] ,T1.unitMsr AS [UM] ,CONVERT(VARCHAR(10),T2.ActDelDate,103) AS [DATA SAÍDA MATERIAL] ,T0.CardCode AS [COD.PN] ,T0.CARDNAME AS [RAZÃO SOCIAL] ,CONCAT( DateDiff(d, T0.[CreateDate], T2.[ActDelDate]),' ', ' Dias') As 'Entregue em...' FROM ORDR T0 LEFT JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN INV1 T2 ON T2.BASEENTRY = T1.DOCENTRY AND T2.BASELINE = T1.lINEnUM LEFT JOIN OINV T3 ON T1.DOCENTRY = T3.BaseEntry WHERE T0.CreateDate >= [%0] AND T0.CreateDate <= [%1] ORDER BY T1.ItemCode
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Eu ligo as linhas, além do DocEntry, por causa de quando um faturamento for parcial.
Assim:
select *
from ORDR p0
join RDR1 p1 on p1.DocEntry = p0.DocEntry
join INV1 nf1 on nf1.BaseEntry = p1.DocEntry and nf1.BaseLine = p1.LineNum
join OINV nf0 on nf0.DocEntry = nf1.DocEntry
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Bom dia Herenildo,
O campo que faz o relacionamento é o BaseRef, que fica nas tabelas INV1 e RDR1.
Espero que ajude.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
107 | |
12 | |
11 | |
6 | |
5 | |
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.