Informe Antiguedad Saldo SN

[Former Member]

Buen dia a todos,

Estamos necesitando obtener el detalle de los saldos de nuestros clientes al 31/12/2010 para presentar a auditoria. Los auditores nos solicitan un informe que contenga el detalle del saldo de nuestros clientes (Facturas, Notas de Credito, PR,etc) a la fecha dada.

He probado el informe de antiguedad de saldo de clientes pero tengo un inconveniente con ello. Como bien saben, los informes para estos tipos de controles deben mostrar todos los documentos que fueron contabilizados para este ejemplo al 31/12/2010. En el informe de antiguedad de saldos tengo dicha opción, pero con un inconveniente. Al ingresar la fecha de contabilización, el sistema carga automáticamente en la fecha de vencimiento la ultima fecha de contabilización ingresada en el filtro probocando que mi informe no salga con las informaciones que se necesitan.

Realize una comparación del los valores indicados en el balance emitida a la fecha solicitada y la antiguedad de saldo y no coincide, puesto que la antiguedad de saldo simpre filtra por la fecha de vencimiento independientemete si se completa o no dicho campo.

Ya he emitido el libro mayor, pero las informaciones obtenidas son muy detalladas, se necesitan solo dos documentos que están en abierto y el libro mayor trae todos los movimientos del cliente desde el inicio de sus operaciones.

Habría alguna forma de obtener la información que necesito desde el SAP? Les agradeceré cualquier ayuda que me puedan brindar.

Desde ya muchas gracias,

Saludos

[former_member1259626]

Hola a todos un gusto y perdon veo que el post es ya de algunos años atras.

Justo estoy empezando con Sap B1 en hana precisamente, se me ha solicitado lo que es un reporte de antiguedad de saldos por sucursal, por lo cual estaba buscando alguna luz para orientarme, en este caso ubique este query y me parece interesante el cual podría basarme en el.

El detalle es que el alias de la T3 no me lo esta reconociendo me genera un error, agradezco de antemano su ayuda.

Copio el query que literalmente es casi exactamente lo mismo por no decir igual:

SELECT /*Y3.SlpName,*/ T0."CardCode", T0."CardName", T1."TransId" as Asiento, T4."BaseRef" as DocInterno, T4."Folionum" as Folio, T1."RefDate", T1."TaxDate",

T1."DueDate",

CASE

WHEN T3."DebHab" = 'D' THEN (T1."Debit"-T1."Credit"-T3."ReconSum")

WHEN T3."DebHab" = 'C' THEN (T1."Debit"-T1."Credit"+T3."ReconSum")

ELSE (T1."Debit"-T1."Credit")

END As Saldo,

CASE

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') <= 30 and T3."DebHab" = 'D' then T1."Debit"-T1."Credit"-T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') <= 30 and T3."DebHab" = 'C' then T1."Debit"-T1."Credit"+T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') <= 30 then (T1."Debit"-T1."Credit") end as dias0a30,

CASE

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') BETWEEN 31 AND 60 and T3."DebHab" = 'D' then T1."Debit"-T1."Credit"-T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') BETWEEN 31 AND 60 and T3."DebHab" = 'C' then T1."Debit"-T1."Credit"+T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') BETWEEN 31 AND 60 then (T1."Debit"-T1."Credit") end as dias31a60,

CASE

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') BETWEEN 61 AND 90 and T3."DebHab" = 'D' then T1."Debit"-T1."Credit"-T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') BETWEEN 61 AND 90 and T3."DebHab" = 'C' then T1."Debit"-T1."Credit"+T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') BETWEEN 61 AND 90 then (T1."Debit"-T1."Credit") end as dias61a90,

CASE

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') BETWEEN 91 AND 120 and T3."DebHab" = 'D' then T1."Debit"-T1."Credit"-T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') BETWEEN 91 AND 120 and T3."DebHab" = 'C' then T1."Debit"-T1."Credit"+T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') BETWEEN 91 AND 120 then (T1."Debit"-T1."Credit") end as dias91a120,

CASE

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') > 120 and T3."DebHab" = 'D' then T1."Debit"-T1."Credit"-T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') > 120 and T3."DebHab" = 'C' then T1."Debit"-T1."Credit"+T3."ReconSum"

WHEN DATEDIFF(DAY,T1."RefDate",'2022/12/31') > 120 then (T1."Debit"-T1."Credit") end as diasmas120,

CASE T1."TransType"

WHEN '13' THEN (SELECT Y."Comments" FROM OINV Y WHERE Y."TransId" = T1."TransId")

WHEN '14' THEN (SELECT Y."Comments" FROM ORIN Y WHERE Y."TransId" = T1."TransId")

ELSE T1."LineMemo"

END AS Comentarios

FROM OCRD T0

INNER JOIN JDT1 T1 ON T1."ShortName" = T0."CardCode"

INNER JOIN OACT T2 ON T2."AcctCode" = T1."Account"

INNER JOIN OJDT T4 ON T4."TransId" = T1."TransId"

/*LEFT JOIN dbo.OINV Y1 ON Y1.TransId = T1.TransId

LEFT JOIN dbo.ORIN Y2 ON Y2.TransId = T1.TransId

LEFT JOIN dbo.OSLP Y3 ON Y3.SlpCode = Y1.SlpCode OR Y3.SlpCode = Y2.SlpCode*/

LEFT JOIN (

SELECT X0."ShortName" AS SN, X0."TransId" AS TransId, SUM(X0."ReconSum") AS ReconSum, X0."IsCredit" AS DebHab, X0."TransRowId" as Linea

FROM ITR1 X0

INNER JOIN OITR X1 ON X1."ReconNum" = X0."ReconNum"

WHERE X1."ReconDate" <= '2022/12/31' AND X1."CancelAbs" = ''

GROUP BY X0."ShortName", X0."TransId", X0."IsCredit", X0."TransRowId"

) as T3 ON T3."TransId" = T1."TransId" AND T3."SN" = T1."ShortName" AND T3."Linea" = T1."Line_ID"

WHERE T0."CardType" = 'C' AND T1."RefDate" <= '2022/12/31' AND

(CASE

WHEN T3."DebHab" = 'D' THEN (T1."Debit"-T1."Credit"-T3."ReconSum")

WHEN T3."DebHab" = 'C' THEN (T1."Debit"-T1."Credit"+T3."ReconSum")

ELSE (T1."Debit"-T1."Credit")

END) != '0'

ORDER BY Y3."SlpCode", T0."CardCode", T1."TransId"

[moshenaveh]

Hello, While we're happy that you've come to SAP Community to get an answer to your question, you posted your question as an answer in an old thread.
I've converted your answer to a comment, but even so -- posting in older threads is not the best way to get guidance.
If you're looking for help, you should ask a new question: https://answers.sap.com/questions/ask.html.
Here are some tips to help you craft an effective question for our community: https://community.sap.com/resources/questions-and-answers, https://developers.sap.com/tutorials/community-qa.html, https://groups.community.sap.com/t5/welcome-corner-discussions/advice-from-sap-champions-questions-a....
I encourage you to follow this guidance, as I'd really like to see you get a solution to your problem.
I hope you find this advice useful!

[felipe_loyolarodriguez]

Hola

Prueba este query

DECLARE @VAR INT, @FECHAFIN DATETIME
SET @VAR = (SELECT TOP 1 A.TransId FROM [dbo].[JDT1] A WHERE A.RefDate <='[%0]')
SET @FECHAFIN = '[%0]'
 
SELECT /*Y3.SlpName,*/ T0.CardCode, T0.CardName, T1.TransId'Asiento', T4.BaseRef 'Doc Interno', T4.Folionum'Folio', T1.RefDate, T1.TaxDate, T1.DueDate,
CASE
	WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)
	WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)
	ELSE (T1.Debit-T1.Credit)
END 'Saldo',
CASE
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) <= 30 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) <= 30 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) <= 30 then (T1.Debit-T1.Credit) end '0-30 dias',
CASE
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 31 AND 60 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 31 AND 60 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 31 AND 60 then (T1.Debit-T1.Credit) end '31-60 dias',
CASE
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 61 AND 90 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 61 AND 90 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 61 AND 90 then (T1.Debit-T1.Credit) end '61-90 dias',
CASE
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 91 AND 120 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 91 AND 120 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) BETWEEN 91 AND 120 then (T1.Debit-T1.Credit) end '91-120 dias',
CASE
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) > 120 and T3.DebHab = 'D'  then T1.Debit-T1.Credit-T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) > 120 and T3.DebHab = 'C'  then T1.Debit-T1.Credit+T3.ReconSum
WHEN DATEDIFF(DAY,T1.RefDate,@FECHAFIN) > 120 then (T1.Debit-T1.Credit) end '+120 dias'
CASE T1.TransType
	WHEN '13' THEN (SELECT Y.Comments FROM OINV Y WHERE Y.TransId = T1.TransId)
	WHEN '14' THEN (SELECT Y.Comments FROM ORIN Y WHERE Y.TransId = T1.TransId)
	ELSE T1.LineMemo
END 'Comentarios'
FROM dbo.OCRD T0
INNER JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode
INNER JOIN dbo.OACT T2 ON T2.AcctCode = T1.Account
INNER JOIN dbo.OJDT T4 ON T4.TransId = T1.TransId
/*LEFT JOIN dbo.OINV Y1 ON Y1.TransId = T1.TransId
LEFT JOIN dbo.ORIN Y2 ON Y2.TransId = T1.TransId
LEFT JOIN dbo.OSLP Y3 ON Y3.SlpCode = Y1.SlpCode OR Y3.SlpCode = Y2.SlpCode*/
LEFT JOIN (	
	SELECT X0.ShortName 'SN', X0.TransId 'TransId', SUM(X0.ReconSum)'ReconSum', X0.IsCredit 'DebHab', X0.TransRowId 'Linea'
	FROM dbo.ITR1 X0
	INNER JOIN dbo.OITR X1 ON X1.ReconNum = X0.ReconNum
	WHERE X1.ReconDate <= @FECHAFIN AND X1.CancelAbs = ''
	GROUP BY X0.ShortName, X0.TransId, X0.IsCredit, X0.TransRowId
	) T3 ON T3.TransId = T1.TransId AND T3.SN = T1.ShortName AND T3.Linea = T1.Line_ID
WHERE T0.CardType = 'C' AND T1.RefDate <= @FECHAFIN AND
(CASE
	WHEN T3.DebHab = 'D' THEN (T1.Debit-T1.Credit-T3.ReconSum)
	WHEN T3.DebHab = 'C' THEN (T1.Debit-T1.Credit+T3.ReconSum)
	ELSE (T1.Debit-T1.Credit)
END) != '0'
ORDER BY Y3.SlpCode, T0.CardCode, T1.TransId
FOR BROWSE

Saludos

[Former Member]

Ya está resuelto el caso, muchas gracias a todos. En el filtro Fecha de Contabilización tengo que colocar la misma fecha que he utilizado para emitir mi balance y tildar la opción "Incluir clientes con saldo cero"

[Former Member]

Estimado,

Favor no olvides si està resuelto calificar y cerrar los post.

gracias.

[Former Member]

Hola Cristhian,

El informe de Antiguedad te presenta la informacion tal como estaba a la fecha determina (ej. 31-12-2010), para que funcione debes marcar el campo VISUALIZAR CLIENTE CON SALDO CERO

De esta manera el resultado va ser solo los dcoumentos que tenian saldo a la fecha dada.

Saludos,

JAMS