Reporte facturas de venta con el nº de pedido

SAP Business One
22

Con la tabla ORIN no es igual verdad?

SELECT DISTINCT
T0.[DocNum],
T0.[DocDate],
T0.[CreateDate],
T0.[CardCode] AS ‘Código Cliente’,
T0.[CardName] AS ‘Nombre Cliente’,
T0.[NumAtCard],
T0.[ObjType] AS ‘Tipo Factura’,
T0.[DocTotal] *-1 - T0.[VatSum] * -1 AS ‘Base imponible’,
T0.[VatSum] * -1 AS ‘Impuesto’,
T0.[DocTotal] * -1 AS ‘Total Factura’,
T0.[Comments],
T0.[U_NVT_ORDERTYPE],
T3.DocNum
FROM ORIN T0
INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN [dbo].[RDR1] T2 ON T1.BaseEntry = T2.DocEntry AND T1.BaseLine = T2.LineNum AND T1.BaseType = T2.ObjType
LEFT JOIN [dbo].[ORDR] T3 ON T3.DocEntry = T2.DocEntry
WHERE T0.[CANCELED] =‘N’

porque no me da el pedido bien

1 me gusta
23

Perfecto jose, es pq tenes varios tipos de circuitos. Los primeros que te aparecian antes es pq debes tener remitos relacionados.
Saludos.

24

Mil gracias @Fer_Munoz , en con la tabla ODPI tampoco sale bien el pedido

SELECT DISTINCT
T0.[DocNum],
T0.[DocDate],
T0.[CreateDate],
T0.[CardCode] AS ‘Código Cliente’,
T0.[CardName] AS ‘Nombre Cliente’,
T0.[NumAtCard],
T0.[ObjType] AS ‘Tipo Factura’,
T0.[DocTotal] - T0.[VatSum] AS ‘Base imponible’,
T0.[VatSum] AS ‘Impuesto’,
T0.[DocTotal] AS ‘Total Factura’,
T0.[Comments],
T0.[U_NVT_ORDERTYPE],
T3.DocNum
FROM ODPI T0
INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN [dbo].[RDR1] T2 ON T1.BaseEntry = T2.DocEntry AND T1.BaseLine = T2.LineNum AND T1.BaseType = T2.ObjType
LEFT JOIN [dbo].[ORDR] T3 ON T3.DocEntry = T2.DocEntry
WHERE T0.[CANCELED] =‘N’

25

De nada. No te entendi lo de la tabla “ODPI”

26

@Fer_Munoz que quiero hacer lo mismo, con la tablas ODPI y ORIN, de hecho la consulta completa es esta :cry:

SELECT DISTINCT
–T0.DocEntry,
T0.[DocNum],
T0.[DocDate],
T0.[CreateDate],
T0.[CardCode] AS ‘Código Cliente’,
T0.[CardName] AS ‘Nombre Cliente’,
T0.[NumAtCard],
T0.[ObjType] AS ‘Tipo Factura’,
T0.[DocTotal]- T0.[VatSum] AS ‘Base imponible’,
T0.[VatSum] AS ‘Impuesto’,
T0.[DocTotal] ‘Total Factura’,
T0.[Comments],
T0.[U_NVT_ORDERTYPE],
T3.DocNum AS ‘Nº de Pedido’
FROM [dbo].[OINV]T0
INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN [dbo].[RDR1] T2 ON T1.BaseEntry = T2.DocEntry AND T1.BaseLine = T2.LineNum AND T1.BaseType = T2.ObjType
LEFT JOIN [dbo].[ORDR] T3 ON T3.DocEntry = T2.DocEntry
WHERE T0.CANCELED = ‘N’

UNION ALL

SELECT DISTINCT
T0.[DocNum],
T0.[DocDate],
T0.[CreateDate],
T0.[CardCode] AS ‘Código Cliente’,
T0.[CardName] AS ‘Nombre Cliente’,
T0.[NumAtCard],
T0.[ObjType] AS ‘Tipo Factura’,
T0.[DocTotal] *-1 - T0.[VatSum] * -1 AS ‘Base imponible’,
T0.[VatSum] * -1 AS ‘Impuesto’,
T0.[DocTotal] * -1 AS ‘Total Factura’,
T0.[Comments],
T0.[U_NVT_ORDERTYPE],
T3.DocNum AS ‘Nº de Pedido’
FROM ORIN T0
INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN [dbo].[RDR1] T2 ON T1.BaseEntry = T2.DocEntry AND T1.BaseLine = T2.LineNum AND T1.BaseType = T2.ObjType
LEFT JOIN [dbo].[ORDR] T3 ON T3.DocEntry = T2.DocEntry
WHERE T0.[CANCELED] =‘N’

UNION ALL

SELECT DISTINCT
T0.[DocNum],
T0.[DocDate],
T0.[CreateDate],
T0.[CardCode] AS ‘Código Cliente’,
T0.[CardName] AS ‘Nombre Cliente’,
T0.[NumAtCard],
T0.[ObjType] AS ‘Tipo Factura’,
T0.[DocTotal] - T0.[VatSum] AS ‘Base imponible’,
T0.[VatSum] AS ‘Impuesto’,
T0.[DocTotal] AS ‘Total Factura’,
T0.[Comments],
T0.[U_NVT_ORDERTYPE],
T3.DocNum AS ‘Nº de Pedido’
FROM ODPI T0
INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN [dbo].[RDR1] T2 ON T1.BaseEntry = T2.DocEntry AND T1.BaseLine = T2.LineNum AND T1.BaseType = T2.ObjType
LEFT JOIN [dbo].[ORDR] T3 ON T3.DocEntry = T2.DocEntry
WHERE T0.[CANCELED] =‘N’

27

Cada tabla tiene sus tablas hijas. en el caso de la ORIN es la RIN1 no la INV1

28

y la nota de credito tenes que relacionarla con la factura primero luego con el pedido

29

Gracias @Fer_Munoz mañana lo sigo intentando

1 me gusta
30

Gracias @Fer_Munoz por tu ayuda y sobre todo a @Un_Tal_Erik_J que ha sido el genio que ha rematado la consulta.

La comparto con ustedes por si les sirve.

Saludos desde España

SELECT DISTINCT
T0.[DocNum],
T0.[DocDate],
T0.[CreateDate],
T0.[CardCode] AS ‘Código Cliente’,
T0.[CardName] AS ‘Nombre Cliente’,
T0.[NumAtCard],
T0.[ObjType] AS ‘Tipo Factura’,
T0.[DocTotal]- T0.[VatSum] AS ‘Base imponible’,
T0.[VatSum] AS ‘Impuesto’,
T0.[DocTotal] ‘Total Factura’,
T0.[Comments],
T0.[U_NVT_ORDERTYPE]
,(select DocNum from ORDR ow where T1.[BaseEntry] = OW.[DocEntry] )‘PEDIDO’ – AGREGA UNA COLUMNA SIN DECLARAR LA TABLA PERO TIENE QUE TENER UNION CON LAS TABLAS DECLARADAS
FROM OINV T0 LEFT JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] AND T1.BaseType = ‘17’
WHERE T0.CANCELED = ‘N’

UNION ALL

SELECT DISTINCT
T0.[DocNum],
T0.[DocDate],
T0.[CreateDate],
T0.[CardCode] AS ‘Código Cliente’,
T0.[CardName] AS ‘Nombre Cliente’,
T0.[NumAtCard],
T0.[ObjType] AS ‘Tipo Factura’,
T0.[DocTotal] *-1 - T0.[VatSum] * -1 AS ‘Base imponible’,
T0.[VatSum] * -1 AS ‘Impuesto’,
T0.[DocTotal] * -1 AS ‘Total Factura’,
T0.[Comments],
T0.[U_NVT_ORDERTYPE]
,(select DocNum from ORDR ow where T1.[BaseEntry] = OW.[DocEntry] )‘PEDIDO’
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.CANCELED =‘N’

UNION ALL

SELECT DISTINCT
T0.[DocNum],
T0.[DocDate],
T0.[CreateDate],
T0.[CardCode] AS ‘Código Cliente’,
T0.[CardName] AS ‘Nombre Cliente’,
T0.[NumAtCard],
T0.[ObjType] AS ‘Tipo Factura’,
T0.[DocTotal] - T0.[VatSum] AS ‘Base imponible’,
T0.[VatSum] AS ‘Impuesto’,
T0.[DocTotal] AS ‘Total Factura’,
T0.[Comments],
T0.[U_NVT_ORDERTYPE]
,(select DocNum from ORDR ow where T1.[BaseEntry] = OW.[DocEntry] )‘PEDIDO’
FROM ODPI T0 INNER JOIN DPI1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.CANCELED =‘N’

1 me gusta
31

Este tema se cerró automáticamente 7 días después de la última publicación. No se permiten nuevas respuestas.