Factura Nota Débito. Código y dudas

[Doom]

Bueno estoy verificando si tengo bien el código de una factura nota débido Crystal Reports. Tengo una duda en la parte del from en negrita si estoy trayendo las tablas correctas. Y como puedo optimizar este código.

SELECT

T0.DocNum’FACTURA’ ,T0.NumAtCard ,T2.DocNum Entrega , T0.DocEntry, T0.U_HBT_ConceptoNC,T0.U_HBT_IdFactura,T0.Series,T0.DocTotal,T0.DocDate,t0.DocDueDate,T0.CardCode,T0.CardName,T0.Address,T0.TotalExpns,T0.VatSum,T0.WTSum,T0.SlpCode, T0.COMMENTS,
T1.DiscPrcnt’DESC%’,T1.VatPrcnt’IVA%’,T1.unitMsr,T4.Quantity ‘Q LOTE’, T1.Quantity, T1.PriceBefDi,T1.ItemCode,T1.Dscription,T1.LineTotal, T10.Name ‘Persona de Contacto’,
T4.BatchNum, T0.DiscSum, t0.DiscPrcnt ‘Descuento%’, T0.RoundDif ‘REDONDEDO_$’, T0.Printed ‘Impreso’,
T5.LicTradNum,T5.Phone1,T7.SlpName, t8.Usertext, t9.pymntgroup, T11.City ‘CIUDAD’, T12.NAME ‘PAIS’,

CASE WHEN
T8.ManBtchNum=‘N’
THEN
T1.Quantity WHEN T8.ManBtchNum=‘Y’ THEN T4.Quantity END ‘Cantidad Definitiva’

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

LEFT JOIN OINV T2
ON T1.BaseEntry=T2.DocEntry

LEFT JOIN INV1 T3
ON T2.DocEntry=T3.DocEntry
AND T1.ItemCode=T3.ItemCode
AND T1.WhsCode=T3.WhsCode
AND T1.BaseLine = T3.LineNum

LEFT JOIN IBT1 T4
ON T4.BaseEntry = T3.DocEntry
AND T4.BaseLinNum = T3.LineNum
AND T4.BaseType = T3.ObjType
AND T4.ItemCode = T3.ItemCode
AND T4.WhsCode = T3.WhsCode

LEFT JOIN OCRD T5
ON T0.CardCode=T5.CardCode

LEFT JOIN OSLP T7
ON T0.SlpCode=T7.SlpCode

LEFT JOIN OITM T8
ON T1.ItemCode=T8.ItemCode

LEFT JOIN OCTG T9
ON T9.GroupNum = T0.GroupNum

LEFT JOIN OCPR T10
ON T10.CntctCode=T0.CntctCode

LEFT JOIN CRD1 T11
ON T5.CardCode=T11.CardCode AND T11.AdresType = ‘B’ AND T11.LineNum=0

LEFT JOIN OCRY T12
ON T11.Country=T12.Code

WHERE T0.DocEntry= {?Dockey@}

[JoSeVc]

Hola @Doom,

@Doom

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

Esta relacion si esta bien… pero veo que la relacion T2 tambien la haces con la OINV (Factura de cliente) pero en el select al T2 le indicas Entrega. entonces…

LEFT JOIN OINV T2
ON T1.BaseEntry=T2.DocEntry

Esta relacion no deberia ser con la OINV, sino con la ODLN.

y esta otra deberia ser DLN1 en lugar de INV1.

LEFT JOIN INV1 T3
ON T2.DocEntry=T3.DocEntry
AND T1.ItemCode=T3.ItemCode
AND T1.WhsCode=T3.WhsCode
AND T1.BaseLine = T3.LineNum

[Doom]

SELECT 

T0.DocNum'FACTURA' ,T0.NumAtCard ,T2.DocNum Entrega , T0.DocEntry, T0.U_HBT_ConceptoNC,T0.U_HBT_IdFactura,T0.Series,T0.DocTotal,T0.DocDate,t0.DocDueDate,T0.CardCode,T0.CardName,T0.Address,T0.TotalExpns,T0.VatSum,T0.WTSum,T0.SlpCode, T0.COMMENTS,
T1.DiscPrcnt'DESC%',T1.VatPrcnt'IVA%',T1.unitMsr,T4.Quantity 'Q LOTE', T1.Quantity, T1.PriceBefDi,T1.ItemCode,T1.Dscription,T1.LineTotal, T10.Name 'Persona de Contacto',
T4.BatchNum, T0.DiscSum, t0.DiscPrcnt 'Descuento%', T0.RoundDif 'REDONDEDO_$', T0.Printed 'Impreso',
T5.LicTradNum,T5.Phone1,T7.SlpName, t8.Usertext, t9.pymntgroup, T11.City 'CIUDAD', T12.NAME 'PAIS',

CASE WHEN 
T8.ManBtchNum='N'
THEN
T1.Quantity WHEN T8.ManBtchNum='Y' THEN T4.Quantity  END 'Cantidad Definitiva'


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

LEFT JOIN ODLN T2
ON T1.BaseEntry=T2.DocEntry

  LEFT JOIN DLN1 T3
  ON T2.DocEntry=T3.DocEntry
  AND T1.ItemCode=T3.ItemCode 
  AND T1.WhsCode=T3.WhsCode
  AND T1.BaseLine = T3.LineNum
       
LEFT JOIN IBT1 T4
       ON T4.BaseEntry = T3.DocEntry 
       AND T4.BaseLinNum = T3.LineNum 
       AND T4.BaseType = T3.ObjType 
       AND T4.ItemCode = T3.ItemCode 
       AND T4.WhsCode = T3.WhsCode 

       
LEFT JOIN OCRD T5
ON T0.CardCode=T5.CardCode


LEFT JOIN OSLP T7
ON T0.SlpCode=T7.SlpCode

LEFT JOIN OITM T8
ON T1.ItemCode=T8.ItemCode

LEFT JOIN OCTG T9
ON T9.GroupNum = T0.GroupNum

LEFT JOIN OCPR  T10
ON T10.CntctCode=T0.CntctCode

LEFT JOIN CRD1 T11
ON T5.CardCode=T11.CardCode AND T11.AdresType = 'B' AND T11.LineNum=0

LEFT JOIN OCRY T12
ON T11.Country=T12.Code

WHERE T0.DocEntry= {?Dockey@}