Relación con la tablas ORCT y OJDT

CJPG · 9 Octubre, 2019 20:48

Hola saperos, tengo una solicitud la cual es generar un reporte en la cual muestra el pagos efectuado en los cuales se este realizando un pago con un asiento contable. he buscado como relacionar las dos tablas pero no he encontrado que campo es el que utiliza el sistema para en realiza la relación.

Realizando una revisión a fondo encontré la tabla RCT2 que en ella si encuentro el TransID de la tabla OJDT pero aun no he podido hacer que sea exacto, ya que me incluye asientos contables que no están en el pago efectuado.

Les dejo el código que estoy utilizando

Select T0.DocEntry[No. Pagos SAP], T0.CardCode[Codigo Clientes], T0.CardName[Nombre Cliente], T0.DocDate[Fecha Pago], T0.CounterRef[No. Recibo], 
	   T3.Descr[Cobrador], T0.Comments[Comentarios], T0.DocTotal[Total Pagos],
	   T2.BaseRef[No. Factura y/o NC], Case When T2.ObjType = '30' Then 'AS' else ' ' End[Clase de Documento], T2.RefDate[Fecha Documento], 
	   Case When T2.ObjType = '30' Then (T2.LocTotal) else ' ' End [Total Liquidado], T0.DocTotal[Total Pago]
	From ORCT T0 inner join 
		 RCT2 T1 on T0.DocEntry = T1.DocNum inner join 
		 OJDT T2 on T1.DocEntry = T2.TransId /*and T1.InvType = T2.ObjType*/ inner join 
		 UFD1 T3 on T0.U_Cobrador = T3.FldValue and T3.FieldID = '2' and T3.TableID = 'ORCT' 
Where /*T0.DocNum = '38693' --and*/ T0.DocDate >= '20190101' and T0.DocDate <= '20190731'
Group By T0.DocEntry, T0.CardCode, T0.CardName, T0.DocDate, T0.CounterRef, 
	   T3.Descr, T0.Comments, T0.DocTotal, T2.BaseRef, T2.ObjType, T2.RefDate, T2.LocTotal
Order By 1

esta es una imagen de uno de los resultado del codigo

Como pueden ver en la imagen la primera linea no corresponde al pago efectuado pero me lo incluyen no se porque.

Agradeceria bastante la ayuda.
Saludos.

IKASHIMI · 9 Octubre, 2019 20:56

alguna vez tuve ese detalle, lo que pasa que al hacer match con inner Join en esos documentos se repiten los numeros de documentos como doc entrys y viceversa.
checa esta estructura haber si te funciona.

SELECT

-- T1."SeriesName" as  "Serie",

CASE T2."InvType"

WHEN 13 THEN 'Factura'

WHEN 14 THEN 'N.Credito'

END as "Docto",

CASE T2."InvType"
WHEN 13 THEN (SELECT A."DocNum" FROM OINV A WHERE A."DocEntry"=T2."DocEntry")
WHEN 14 THEN (SELECT A."DocNum"  FROM ORIN A WHERE A."DocEntry"=T2."DocEntry")
END as  "#SAP",

CASE T2."InvType"
WHEN 13 THEN (SELECT A."DocDate" FROM OINV A WHERE A."DocEntry"=T2."DocEntry")
WHEN 14 THEN (SELECT A."DocDate" FROM ORIN A WHERE A."DocEntry"=T2."DocEntry")
END as "Fecha Docto.",



T0."DocNum" as "No. Pago",

T0."CardCode" as "Código Cliente",

T0."CardName" as "Nombre Cliente",

CASE T2."InvType"
WHEN 13 THEN (SELECT A."DocTotal" FROM OINV A WHERE A."DocEntry"=T2."DocEntry")
WHEN 14 THEN (SELECT A."DocTotal" FROM ORIN A WHERE A."DocEntry"=T2."DocEntry")*-1
END as "Imp.Docto",

CASE T2."InvType"
WHEN 13 THEN T2."SumApplied"
WHEN 14 THEN T2."SumApplied"*-1
END as "Imp.Aplicado",

CASE T2."InvType"
WHEN 13 THEN (SELECT A."PaidToDate" FROM OINV A WHERE A."DocEntry"=T2."DocEntry")
WHEN 14 THEN (SELECT A."PaidToDate" FROM ORIN A WHERE A."DocEntry"=T2."DocEntry")*-1
END as "Tot.Pagado",

T0."CashSum" as "Efectivo",

T0."CheckSum" as "Cheque",

T0."TrsfrSum" as  "Transf",

T0."DocTotal" as  "Total",

T0."DocDate" as  "Fecha",

T4."SlpName"||' '||T4."Memo"

FROM ORCT T0 

INNER JOIN NNM1 T1 ON T0."Series" = T1."Series"

LEFT JOIN RCT2 T2 ON T0."DocEntry" = T2."DocNum"

LEFT JOIN OCRD T3 ON T0."CardCode" = T3."CardCode"

LEFT JOIN OSLP T4 ON T3."SlpCode" = T4."SlpCode"

WHERE T0."Canceled"  = 'N' and T0."DocDate" between [%0] and [%1]

ORDER BY T0."DocNum"

CJPG · 9 Octubre, 2019 21:02

Hola @IKASHIMI gracias por tu pronta respuesta voy a revisar tu codigo y te comento.

Gracias.

Saludos

juliancab · 9 Octubre, 2019 22:51

Y si filtras por Clase de Documento para que solo te tome los que necesitas?

JhosserRomero · 10 Octubre, 2019 12:04

Hola @CJPG, me parece que el problema es que estás usando la tabla RCT2 en lugar de usar la VPM2.

La tabla ORCT corresponde a pagos recibidos, para pagos efectuados es la tabla OVPM.

Creo que es por eso que la consulta te muestra asientos que no están en el pago efectuado.

Saludos.

CJPG · 23 Octubre, 2019 17:41

Hola gracias @IKASHIMI, @juliancab, @JhosserRomero por su ayuda y sus comentarios al final me funciono de la siguiente forma, dejo el codigo por si alguien le sirve:

-- Facturas de ventas
Select Distinct A."DocNum" as "No. Pago", A."CardCode" as "Código Cliente", A."CardName" as "Nombre Cliente", A."DocDate" as "Fecha de Pago", A."CounterRef" as "No. Recibo", C."Descr" as "Cobrador", 
				A."Comments" as "Comentarios", 
				CASE B."InvType" WHEN '13' THEN 'Factura' WHEN '14' THEN 'N.Credito' WHEN '30' THEN 'Poliza Contable' END as "Docto",
				D.NumAtCard, D.DocTotal, A.DocTotal
	From ORCT A Inner Join 
		 RCT2 B ON A.DocEntry = B.DocNum Inner join
		 UFD1 C on A.U_Cobrador = C.FldValue and C.FieldID = '2' and C.TableID = 'ORCT' inner join
		 OINV D ON B.DocEntry = D.DocEntry
Where A.DocDate >='20191001' and A.DocDate <='20191017' and B.InvType = '13' and A.Canceled = 'N' and A.CardCode not in ('CLIE00357','CLIE00379','CLIE00380','CLIE00381', 'CLIE00384','CLIE00385')
Group By A."DocNum", A."CardCode", A."CardName", A."DocDate", A."CounterRef", C."Descr", A."Comments", B.DocNum, B.InvType, D.NumAtCard, B."InvType", D.DocTotal, A.DocTotal

Union All
-- Pagos directos a cuenta
Select Distinct A."DocNum" as "No. Pago", A."CardCode" as "Código Cliente", A."CardName" as "Nombre Cliente", A."DocDate" as "Fecha de Pago", A."CounterRef" as "No. Recibo", C."Descr" as "Cobrador", 
				A."Comments" as "Comentarios", 
				''Docto, ''NumAtCard, A.NoDocSum[DocTotal], A.DocTotal
	From ORCT A Inner Join 
		 UFD1 C on A.U_Cobrador = C.FldValue and C.FieldID = '2' and C.TableID = 'ORCT' 
Where A.DocDate >='20191001' and A.DocDate <='20191017' and A.Canceled = 'N' and A.ObjType = '24' and A.PayNoDoc = 'Y' and A.CardCode not in ('CLIE00357','CLIE00379','CLIE00380','CLIE00381', 'CLIE00384','CLIE00385')
Group By A."DocNum", A."CardCode", A."CardName", A."DocDate", A."CounterRef", C."Descr", A."Comments", A.DocTotal, A.NoDocSum

Union All
-- Nota de Credito 
Select Distinct A."DocNum" as "No. Pago", A."CardCode" as "Código Cliente", A."CardName" as "Nombre Cliente", A."DocDate" as "Fecha de Pago", A."CounterRef" as "No. Recibo", C."Descr" as "Cobrador", 
				A."Comments" as "Comentarios", 
				CASE B."InvType" WHEN '13' THEN 'Factura' WHEN '14' THEN 'N.Credito' WHEN '30' THEN 'Poliza Contable' END as "Docto",
				D.NumAtCard, (D.DocTotal)*-1, A.DocTotal
	From ORCT A Inner Join 
		 RCT2 B ON A.DocEntry = B.DocNum Inner join
		 UFD1 C on A.U_Cobrador = C.FldValue and C.FieldID = '2' and C.TableID = 'ORCT' inner join
		 ORIN D ON B.DocEntry = D.DocEntry
Where A.DocDate >='20191001' and A.DocDate <='20191017' and B.InvType = '14' and A.Canceled = 'N' and A.CardCode not in ('CLIE00357','CLIE00379','CLIE00380','CLIE00381', 'CLIE00384','CLIE00385')
Group By A."DocNum", A."CardCode", A."CardName", A."DocDate", A."CounterRef", C."Descr", A."Comments", B.DocNum, B.InvType, D.NumAtCard, B."InvType", D.DocTotal, A.DocTotal

Union All
-- Asiento Contable
Select Distinct A."DocNum" as "No. Pago", A."CardCode" as "Código Cliente", A."CardName" as "Nombre Cliente", A."DocDate" as "Fecha de Pago", A."CounterRef" as "No. Recibo", C."Descr" as "Cobrador", 
				A."Comments" as "Comentarios", 
				CASE B."InvType" WHEN '13' THEN 'Factura' WHEN '14' THEN 'N.Credito' WHEN '30' THEN 'Poliza Contable' END as "Docto",
				D.BaseRef, B.SumApplied, A.DocTotal
	From ORCT A Inner Join 
		 RCT2 B ON A.DocEntry = B.DocNum Inner join
		 UFD1 C on A.U_Cobrador = C.FldValue and C.FieldID = '2' and C.TableID = 'ORCT' inner join
		 OJDT D ON B.DocEntry = D.BaseRef
Where A.DocDate >='20191001' and A.DocDate <='20191017' and B.InvType = '30' and A.Canceled = 'N' and A.CardCode not in ('CLIE00357','CLIE00379','CLIE00380','CLIE00381', 'CLIE00384','CLIE00385')
Group By A."DocNum", A."CardCode", A."CardName", A."DocDate", A."CounterRef", C."Descr", A."Comments", B.DocNum, B.InvType, D.BaseRef, B."InvType", B.SumApplied, A.DocTotal
Order By 1

Les agradezco su valiosa ayuda y su tiempo

Saludos.