Reporte con tablas ORDR, OINV, ODPI, ORCT

america · 31 Enero, 2018 14:45

Buenos días

Como siempre acudo a ustedes, cuando ya no encuentro una solución.
Espero que me puedan apoyar.
Necesito generar un reporte donde me muestre total de ventas por usuario, donde mi documento base es la ORDR. El importe de la ORDR se debe reflejar cuando la factura haya sido pagada totalmente. Tengo dos casos.

Se genera la ORDR donde tengo 4 articulos, se manda a una factura un articulo y se paga, luego viene el cliente y paga otros articulos, debo considerar en el reporte la fecha del ultimo pago, es decir, la fecha en la cual se cerró la ORDR.
Se genera la ORDR, donde se venden 4 articulos y estos se mandan a una factura, pero esta factura es pagada en 2 parcialidades, en el reporte debo considerar el total de la ORDR considerando como fecha saldada el ultimo pago recibido.

Con la ODPI creo que no hay tanto problema, puedo tomar el total en PaidToDate de la ORDR.

Con esta consulta aun no logro el caso 1, me considera el total de la OV cuando solo ha tenido un pago,

SELECT DISTINCT 
T0.DocNum AS 'OV'
	
	, T11.DocNum AS 'FAC'
	, T0.DocTotal as 'Total OV'
	, T11.DocTotal as 'Total FACT'
	, T11.DocDate as 'Fecha FACT'
	, T15.DocDate as 'Fecha Pago'
	, T15.DocTotal as 'Total Pago'
    , T17.U_Name as 'Usuario'
	, T0.DocDate AS 'DocDate_OV'
	, T0.DocDueDate AS 'DocDueDate_OV'
	, T0.TaxDate AS 'TaxDate_OV'
	, T0.NumAtCard AS 'NumAtCard_OV'
	, T0.Comments AS 'Comments_OV'
	, T0.PickRmrk AS 'PickRmrk_OV'
	, CASE T0.DocStatus WHEN 'O' THEN 'Abierto' WHEN 'C' THEN 'Cerrado' END AS 'ESTADO_OV'
	, T0.AtcEntry AS 'AtcEntry_OV'
	, FORMAT(T0.CreateTs, '00:00:00') AS 'HORA_OV'
FROM [ORDR] T0 /*OV*/ 
INNER JOIN [RDR1] T1 ON T0.DocEntry = T1.DocEntry /*OV*/ 

LEFT JOIN [INV1] T10 ON T0.DocEntry = T10.BaseEntry AND T0.ObjType = T10.BaseType AND T1.LineNum = T10.BaseLine
LEFT JOIN [OINV] T11 ON T10.DocEntry = T11.DocEntry 

LEFT JOIN [OCRD] T13 ON T0.CardCode = T13.CardCode
LEFT JOIN [OSLP] T14 ON T0.SlpCode = T14.SlpCode

LEFT JOIN [ORCT] T15 ON T11.ReceiptNum = T15.DocNum
LEFT JOIN [OHEM] T16 ON T0.OwnerCode=T16.empID
LEFT JOIN OUSR T17 ON T16.USERID=T17.UserID where (T0.Canceled ='N' or T15.Status='N')

Muchas gracias.

GabrielGS · 31 Enero, 2018 16:11

Segun te entendi, el monto de la ORDR unicamente debe mostrarse cuando este cerrada y totalmente factura y pagada, entonces prueba asi:

SELECT DISTINCT 
	T0.DocNum AS 'OV'
	, T11.DocNum AS 'FAC'
	, IIF(T0.DocTotal =  T0.PaidToDate,T0.DocTotal,0) as 'Total OV'
	, T11.DocTotal as 'Total FACT'
	, T11.DocDate as 'Fecha FACT'
	, T15.DocDate as 'Fecha Pago'
	, T15.DocTotal as 'Total Pago'
    , T17.U_Name as 'Usuario'
	, T0.DocDate AS 'DocDate_OV'
	, T0.DocDueDate AS 'DocDueDate_OV'
	, T0.TaxDate AS 'TaxDate_OV'
	, T0.NumAtCard AS 'NumAtCard_OV'
	, T0.Comments AS 'Comments_OV'
	, T0.PickRmrk AS 'PickRmrk_OV'
	, CASE T0.DocStatus WHEN 'O' THEN 'Abierto' WHEN 'C' THEN 'Cerrado' END AS 'ESTADO_OV'
	, T0.AtcEntry AS 'AtcEntry_OV'
	, FORMAT(T0.CreateTs, '00:00:00') AS 'HORA_OV'
FROM 
	[ORDR] T0 /*OV*/ 
	INNER JOIN [RDR1] T1 ON T0.DocEntry = T1.DocEntry /*OV*/ 

	LEFT JOIN [INV1] T10 ON T0.DocEntry = T10.BaseEntry AND T0.ObjType = T10.BaseType AND T1.LineNum = T10.BaseLine
	LEFT JOIN [OINV] T11 ON T10.DocEntry = T11.DocEntry 

	LEFT JOIN [OCRD] T13 ON T0.CardCode = T13.CardCode
	LEFT JOIN [OSLP] T14 ON T0.SlpCode = T14.SlpCode

	LEFT JOIN [ORCT] T15 ON T11.ReceiptNum = T15.DocNum
	LEFT JOIN [OHEM] T16 ON T0.OwnerCode=T16.empID
	LEFT JOIN OUSR T17 ON T16.USERID=T17.UserID 
WHERE 
	(T0.Canceled ='N' or T15.Status='N')

Saludos:vulcan_salute:

america · 31 Enero, 2018 16:22

Gracias por tu pronta respuesta.
Ya intente con tu consulta, sigo sin obtener la resultado que necesito. Anexo imagen de mi resultado.

Anexo imagen del caso 1. (Aqui el total de la venta se debe reflejar el dia 12 de Ene, ya que fue el ultimo dia que se recibio un pago y se cerro la orden de venta)

Imagen del Caso 2.(Aqui aun no se debe reflejar en el reporte, ya que aun no esta pagada totalmente la factura)

Gracias por su apoyo

GabrielGS · 31 Enero, 2018 16:29

Preguntas:

En el caso 1:

¿Debes reflejar SOLO UNA LINEA con el total de TODOS los pagos y la fecha del ULTIMO?
¿Solo debe aparecer las OV que tenga su total FACTURADO y PAGADO (Si no es asi, no deben estar)?

Caso 2

¿Si una OV no esta facturada o pagada totalmente no debe aparecer en el reporte?

juanfranc520 · 31 Enero, 2018 16:36

Saludos @america,

Según entiendo, lo que quieres es saber es cuando la OV cambio su estado a cerrado. No he trabajado exactamente ese caso, pero tuve un caso donde necesitaba saber cuando algún campo cambio y lo que hice fue verificar en la tabla ADOC y ADO1 los cambios de un documento de marketing que en tu caso es un tipo de documento Orden de Venta, allí podrás consultar las fechas de modificación los campos que cambiaron. Básicamente la tabla ADOC y ADO1 es donde se almacenan los logs de modificaciones de los documentos. Espero te mi respuesta te brinde una luz para que puedas resolver tu caso.

america · 31 Enero, 2018 16:39

Hola Gabriel

En el caso 1, solo debe aparecer el total de la OV con su total, considerando la ultima fecha.
En el caso 2, no se debe reflejar si no esta factura y pagada totalmente. Si la OV tiene su factura, pero será pagada en parcialidades, debo considerara como fecha pagada, su ultimo pago

Hola @juanfranc520
Gracias por tu respuesta.
Ya he revisado las tablas, mis folios no se reflejan, al menos con los que tengo problemas, no

Gracias por su apoyo

america · 31 Enero, 2018 16:43

Acabo de ver que en la INV1 hay un campo que es ActDelDate, al menos con el ejemploi me muestra la fecha real, intentaré con este campo.

Nada, ese campo no me funcionó

GabrielGS · 31 Enero, 2018 20:27

Prueba con esto, observa bien las 3 ultimas condiciones del WHERE, que permiten filtrar los casos.

SELECT DISTINCT 
	T0.DocNum AS 'OV'
	, T11.DocNum AS 'FAC'
	,T0.DocTotal
	, IIF(T0.DocTotal =  T0.PaidToDate,T0.DocTotal,0) as 'Total OV'
	, T11.DocTotal as 'Total FACT'
	, T11.DocDate as 'Fecha FACT'
	, T15.DocDate as 'Fecha Pago'
	, T15.DocTotal as 'Total Pago'
    , T17.U_Name as 'Usuario'
	, T0.DocDate AS 'DocDate_OV'
	, T0.DocDueDate AS 'DocDueDate_OV'
	, T0.TaxDate AS 'TaxDate_OV'
	, T0.NumAtCard AS 'NumAtCard_OV'
	, T0.Comments AS 'Comments_OV'
	, T0.PickRmrk AS 'PickRmrk_OV'
	, CASE T0.DocStatus WHEN 'O' THEN 'Abierto' WHEN 'C' THEN 'Cerrado' END AS 'ESTADO_OV'
	, T0.AtcEntry AS 'AtcEntry_OV'
	, FORMAT(T0.CreateTs, '00:00:00') AS 'HORA_OV'
FROM 
	[ORDR] T0 /*OV*/ 
	INNER JOIN [RDR1] T1 ON T0.DocEntry = T1.DocEntry /*OV*/ 

	LEFT JOIN [INV1] T10 ON T0.DocEntry = T10.BaseEntry AND T0.ObjType = T10.BaseType AND T1.LineNum = T10.BaseLine
	LEFT JOIN [OINV] T11 ON T10.DocEntry = T11.DocEntry 

	LEFT JOIN [OCRD] T13 ON T0.CardCode = T13.CardCode
	LEFT JOIN [OSLP] T14 ON T0.SlpCode = T14.SlpCode

	LEFT JOIN (SELECT DocNum,SUM(DocTotal) as DocTotal, MAX(DocDate) as DocDate
				FROM [ORCT]
				WHERE Status='N'
				GROUP BY DocNum
					) T15 ON T11.ReceiptNum = T15.DocNum
	LEFT JOIN [OHEM] T16 ON T0.OwnerCode=T16.empID
	LEFT JOIN OUSR T17 ON T16.USERID=T17.UserID 
WHERE 
	(T0.Canceled ='N')
	AND T11.DocNum IS NOT NULL -- NO MUESTRA LAS OV SIN FACTURAS
	AND T15.DocTotal IS NOT NULL -- NO MUESTRA LAS OV SIN PAGOS
	AND T0.DocTotal =  T0.PaidToDate -- MUESTRA LAS OV SOLO PAGOS COMPLETOS

Saludos.

america · 31 Enero, 2018 21:34

Gracias por tu respuesta. Sigo con el dato duplicado o hay algo que hago mal tal vez, el filtro por fecha base a cual campo lo debo hacer?

Anexo Imagen

El folio 34607 tiene dos facturas y sus respectivos pagos, uno con fecha 8 y el otro con fecha 12. Si hago un filtro por fechas los 3519.01 se debe reflejar en el día 12.

GabrielGS · 1 Febrero, 2018 12:15

Prueba ahora con esto:

SELECT DISTINCT 
	T0.DocNum AS 'OV'
	, T11.DocNum AS 'FAC'
	,T0.DocTotal
	, IIF(T0.DocTotal =  T0.PaidToDate,T0.DocTotal,0) as 'Total OV'
	, T11.DocTotal as 'Total FACT'
	, T11.DocDate as 'Fecha FACT'
	, T15.DocDate as 'Fecha Pago'
	, T15.PagadoFact as 'Total Pago'
    , T17.U_Name as 'Usuario'
	, T0.DocDate AS 'DocDate_OV'
	, T0.DocDueDate AS 'DocDueDate_OV'
	, T0.TaxDate AS 'TaxDate_OV'
	, T0.NumAtCard AS 'NumAtCard_OV'
	, T0.Comments AS 'Comments_OV'
	, T0.PickRmrk AS 'PickRmrk_OV'
	, CASE T0.DocStatus WHEN 'O' THEN 'Abierto' WHEN 'C' THEN 'Cerrado' END AS 'ESTADO_OV'
	, T0.AtcEntry AS 'AtcEntry_OV'
	, FORMAT(T0.CreateTs, '00:00:00') AS 'HORA_OV'
FROM 
	[ORDR] T0 /*OV*/ 
	INNER JOIN [RDR1] T1 ON T0.DocEntry = T1.DocEntry /*OV*/ 

	LEFT JOIN [INV1] T10 ON T0.DocEntry = T10.BaseEntry AND T0.ObjType = T10.BaseType AND T1.LineNum = T10.BaseLine
	LEFT JOIN [OINV] T11 ON T10.DocEntry = T11.DocEntry 

	LEFT JOIN [OCRD] T13 ON T0.CardCode = T13.CardCode
	LEFT JOIN [OSLP] T14 ON T0.SlpCode = T14.SlpCode

	LEFT JOIN (SELECT T1.DocEntry, T1.InvType, SUM(T1.SumApplied) as PagadoFact, MAX(DocDate) as DocDate
				FROM [ORCT] T0 JOIN [RCT2] T1 ON T0.DocNum = T1.DocNum
				WHERE Status='N'
				GROUP BY T1.DocEntry, T1.InvType
					) T15 ON T11.DocEntry = T15.DocEntry AND T11.ObjType = T15.InvType
	LEFT JOIN [OHEM] T16 ON T0.OwnerCode=T16.empID
	LEFT JOIN OUSR T17 ON T16.USERID=T17.UserID 
WHERE 
	(T0.Canceled ='N')
	AND T11.DocNum IS NOT NULL -- NO MUESTRA LAS OV SIN FACTURAS
	AND T15.PagadoFact IS NOT NULL -- NO MUESTRA LAS OV SIN PAGOS
	AND T0.DocTotal =  T0.PaidToDate -- MUESTRA LAS OV SOLO PAGOS COMPLETOS

Saludos!

america · 2 Febrero, 2018 00:12

Gracias @GabrielGS
Sigo con el mismo resultado. Estoy buscando otra forma de relacionar esas ventas, con el query me muestra las dos fechas y no la última. Quizás si lo tome de las entregas me pueda servir.
He revisado la tabla ordr en busca de un campo de día cerrado y no encuentro.
Te haré saber si obtengo lo deseado.

GabrielGS · 2 Febrero, 2018 11:57

Ah! Jejeje claro, es que una OV puede tener N facturas y N pagos , mas tarde te paso la correccion de Query, lo que pasa es que todas las bases que tengo para probar contienen una factura por cada OV (Se factura luego de despachar el 100% de la OV).

america · 2 Febrero, 2018 21:38

Buenas tardes.
Gracias

america · 12 Febrero, 2018 14:51

Buenos días

Hola @GabrielGS no había podido darle continuidad a mi reporte, apenas retomaré el tema nuevamente. Gracias por tu apoyo.

Me rindo con esa consulta, no obtengo lo deseado, incluso ya intente tomar los datos de las entregas, pero sigo obteniendo doble resultado

Alguien tendrá alguna otra idea?
Mil gracias

america · 17 Febrero, 2018 15:54

Buenos días

Al fin ya lo he logrado.
Asi es como queda:

SELECT DISTINCT 
	T0.DocNum as 'OV'
	,T0.U_Convenio as 'Conv OV'
	,T11.U_Convenio as 'Conv Fact'
	
	,T11.[Total Fact] as 'Total Fact'
	,T11.Vendedor as 'Vendedor Fact'
	,T0.U_TIPO_Convenio as 'Tipo Conv OV'
	, IIF(T0.DocTotal =  T0.PaidToDate,T0.DocTotal,0) as 'Tot OV'
	, T18.FechaFactAme as 'Fecha Fact ame'
    , T17.U_Name as 'Usuario'
	, T0.DocDate AS 'DocDate_OV'
	, T0.DocDueDate AS 'DocDueDate_OV'
	, T0.TaxDate AS 'TaxDate_OV'
	, T0.NumAtCard AS 'NumAtCard_OV'
	, T0.Comments AS 'Comments_OV'
	, T0.PickRmrk AS 'PickRmrk_OV'
	, CASE T0.DocStatus WHEN 'O' THEN 'Abierto' WHEN 'C' THEN 'Cerrado' END AS 'ESTADO_OV'
	, T0.AtcEntry AS 'AtcEntry_OV'
	, FORMAT(T0.CreateTs, '00:00:00') AS 'HORA_OV'
FROM 
	[ORDR] T0 /*OV*/ 
	INNER JOIN [RDR1] T1 ON T0.DocEntry = T1.DocEntry /*OV*/ 

	LEFT JOIN [INV1] T10 ON T0.DocEntry = T10.BaseEntry AND T0.ObjType = T10.BaseType AND T1.LineNum = T10.BaseLine
	
	---T11---
	LEFT JOIN (SELECT T2.DocEntry, t2.U_Convenio,T2.U_TIPO_Convenio,T2.DocTotal 'Total Fact', T2.SlpCode as 'Vendedor'
		FROM OINV T2 
		WHERE T2.CANCELED='N' and T2.U_Convenio='SI') T11 ON T10.DocEntry = T11.DocEntry 

	LEFT JOIN [OCRD] T13 ON T0.CardCode = T13.CardCode
	LEFT JOIN [OSLP] T14 ON T0.SlpCode = T14.SlpCode

	LEFT JOIN (SELECT T1.DocEntry, T1.InvType, SUM(T1.SumApplied) as PagadoFact, MAX(DocDate) as DocDate
				FROM [ORCT] T0 JOIN [RCT2] T1 ON T0.DocNum = T1.DocNum
				WHERE Status='N'
				GROUP BY T1.DocEntry, T1.InvType
					) T15 ON T11.DocEntry = T15.DocEntry --AND T11.ObjType = T15.InvType
	LEFT JOIN [OHEM] T16 ON T0.OwnerCode=T16.empID
	LEFT JOIN OUSR T17 ON T16.USERID=T17.UserID 

	LEFT JOIN (SELECT t2.U_Convenio,T2.U_TIPO_Convenio, 
	T0.DocEntry, T0.DocNum 'Orden', 
	T0.DocDate 'Fecha orden', 
	max(T2.DocDate) as FechaFactAme
	--,IIF(T2.U_Convenio = 'SI') as 'Total OV'
		FROM ORDR T0
		INNER JOIN INV1 T1 ON T1.BaseEntry=T0.DocEntry AND T1.BaseType='17'  
		INNER JOIN OINV T2 ON T2.DocEntry=T1.DocEntry
		WHERE T2.CANCELED='N' group by t0.DocEntry, t0.DocNum, t0.DocDate,t2.U_Convenio,T2.U_TIPO_Convenio) T18 on T0.DocEntry=T18.DocEntry 
WHERE 
	T0.Canceled ='N'
	AND T0.DocTotal =  T0.PaidToDate -- MUESTRA LAS OV SOLO PAGOS COMPLETOS
	--and T11.CANCELED='N'
	AND T0.DocStatus='C'

system · 24 Febrero, 2018 15:54

Este tema se cerró automáticamente 7 días después del último post. No se permiten nuevas respuestas.