Query Saldo de cuenta

Buenas tardes,

Por favor solicito su ayuda para crear un query similar al de Saldo de cuenta de Sap B1, a continuación un ejemplo de saldo de cuenta

El campo con el cual necesito su ayuda es el de Saldo acumulado(ML). Quisiera saber como Sap realiza ese calculo o si estos valores estan almacenados en alguna tabla.

Cree el siguiente query, al final el valor del saldo al comparar con el que saco de sap es correcto


pero a la hora de comparar la trazabilidad en varios documentos el valor es diferentes ejemplo


este es el query

SELECT  
      T0.Account AS Cuenta
	 ,T0.RefDate AS [Fehca de contabilizacion]
	 ,T0.TaxDate  AS [Fecha de documento]
	 ,T0.TransId AS [No Trans]
	 ,TransType AS [Origen]
	 ,BaseRef AS [Numero de origen]
	 ,T0.ContraAct AS [Cuenta de contrapartida]
	 ,T0.LineMemo AS [Info.detallada]
	 ,T0.Debit ,T0.Credit 
	 ,(SELECT SUM(Y.Debit-Y.Credit) FROM JDT1 AS Y WHERE Y.TransId <= T0.TransId AND Y.Account = T0.Account) AS [Saldo acumulado]
FROM 
    JDT1 AS T0
WHERE 
   T0.Account=  '11050502' 
   AND TaxDate BETWEEN '2019-04-01' AND GETDATE()

Cordial saludo

hola @YesidG

Has pensado en utilizar el profile de sql para cachar el query que genera SAP, seguro ahí te darás cuenta como SAP determina dicho valor, a grandes rasgos que comento son los cargos menos los abonos de la cuenta en cuestión.

Saludos

2 Me gusta

Hola @YesidG revisa lo siguiente

Andres Ramirez Jaramillo :colombia:

1 me gusta

Gracias @andresramirez por tu ayuda, el query es bueno ya lo habia utilizado pero no es lo que necesito, es muy similar al que comparti al realizar la publicación.

Al parecer es tema es muy compliclado ya que nadie dio con el tema, para mi es una falla de Sap que no guarda historiales de los valores.

Finalmente lo he conseguido pero solo filtrandolo por una cuenta, lo que necesitaba era que me mostrara un historial del saldo por cuenta.

SELECT  
      T0.Account AS CUENTA
	 ,[No ASIENTO]
	 ,TERCERO
	 ,T0.RefDate AS [FECHA DE CONTABILIZACION]
	 ,T0.TaxDate AS [FECHA DOCUMENTO]
	 ,[FECHA VENCIMIENTO]
	 ,T0.LineMemo AS NOTA
	 ,ORIGEN
	 ,SERIE
	 ,BaseRef AS [No DOC]
	 ,T0.TransId AS [No TRANSACCION]
	 ,T0.Debit AS DEBITO
	 ,T0.Credit AS CREDITO
	 ,SUM(T0.Debit-T0.Credit) OVER (ORDER BY T0.TaxDate, T0.TransId ,T0.Line_ID , T0.Account )
	 +(SELECT SUM(Y.Debit-Y.Credit) FROM JDT1 AS Y WHERE Y.TaxDate < '2019-04-01'  AND Y.Account = T0.Account) AS SALDO
FROM 
    JDT1 AS T0	
	JOIN 
	(
SELECT 
     T1.TransId AS Trans
	,T1.Line_ID AS LINEID
    ,T1.Account AS CUENTA
	,T0.AcctName AS [NOMBRE DE CUENTA]
	,T2.Number AS [No ASIENTO]
	,CASE 	
	   WHEN T2.TransType = 13  THEN T5.CardName
	   WHEN T2.TransType = 14  THEN T7.CardName
	   WHEN T2.TransType = 15  THEN T9.CardName
	   WHEN T2.TransType = 16  THEN T11.CardName
	   WHEN T2.TransType = 18  THEN T13.CardName
	   WHEN T2.TransType = 19  THEN T15.CardName
	   WHEN T2.TransType = 20  THEN T17.CardName
	   WHEN T2.TransType = 21  THEN T19.CardName
	   WHEN T2.TransType = 24  THEN T21.CardName
	   WHEN T2.TransType = 30  THEN T3.CardName
	   WHEN T2.TransType = 46  THEN T23.CardName
	   WHEN T2.TransType = 59  THEN T25.CardName
	   WHEN T2.TransType = 60  THEN T27.CardName
	   WHEN T2.TransType = 67  THEN T29.CardName
	   WHEN T2.TransType = 69  THEN T31.SuppName
	   WHEN T2.TransType = 162 THEN T33.CardName
	   WHEN T2.TransType = 202 THEN T35.CardCode
	   WHEN T2.TransType = 204 THEN T37.CardName
	   WHEN T2.TransType = 321 THEN ''
	 ELSE T3.CardName END AS TERCERO 
	,T2.DueDate AS [FECHA VENCIMIENTO]
	,CASE 	
	   WHEN T2.TransType = 13  THEN 'RF-Factura Deudores' 
	   WHEN T2.TransType = 14  THEN 'RC-Nota Credito Clientes'
	   WHEN T2.TransType = 15  THEN 'NE-Entrega'
	   WHEN T2.TransType = 16  THEN 'DV-Devolucion'
	   WHEN T2.TransType = 18  THEN 'TT-Factura Proveedores'
	   WHEN T2.TransType = 19  THEN 'PC-Nota Credito Proveedores'
	   WHEN T2.TransType = 20  THEN 'EP-Entrada Mercancias'
	   WHEN T2.TransType = 21  THEN 'DM-Devolucion Mercancías'
	   WHEN T2.TransType = 24  THEN 'PR-Pagos Recibidos'
	   WHEN T2.TransType = 30  THEN 'AS-Asiento'
	   WHEN T2.TransType = 46  THEN 'PP-Pagos Efectuados'
	   WHEN T2.TransType = 59  THEN 'EM-Entrada Mercancías'
	   WHEN T2.TransType = 60  THEN 'OA-Emisión para producción'
	   WHEN T2.TransType = 67  THEN 'IM-Transferencia de Stock'
	   WHEN T2.TransType = 69  THEN 'DI-Precio Entrega'
	   WHEN T2.TransType = 162 THEN 'RI-Revalorización Inventario'
	   WHEN T2.TransType = 202 THEN 'OF-Orden de Fabricación'
	   WHEN T2.TransType = 204 THEN 'AN-F Anticipo Proveedores'
	   WHEN T2.TransType = 321 THEN 'ID-Reconciliación Interna'
	  ELSE T2.TransType
	END AS ORIGEN
    ,CASE 
	  WHEN T2.TransType = 13  THEN T6.SeriesName 	
	  WHEN T2.TransType = 14  THEN T8.SeriesName 
	  WHEN T2.TransType = 15  THEN T10.SeriesName 
	  WHEN T2.TransType = 16  THEN T12.SeriesName 
	  WHEN T2.TransType = 18  THEN T14.SeriesName 
	  WHEN T2.TransType = 19  THEN T16.SeriesName 
	  WHEN T2.TransType = 20  THEN T18.SeriesName 
	  WHEN T2.TransType = 21  THEN T20.SeriesName 
	  WHEN T2.TransType = 24  THEN T22.SeriesName 
	  WHEN T2.TransType = 30  THEN T4.SeriesName
	  WHEN T2.TransType = 46  THEN T24.SeriesName 
	  WHEN T2.TransType = 59  THEN T26.SeriesName 
	  WHEN T2.TransType = 60  THEN T28.SeriesName 
	  WHEN T2.TransType = 67  THEN T30.SeriesName 
	  WHEN T2.TransType = 69  THEN T32.SeriesName 
	  WHEN T2.TransType = 162 THEN T34.SeriesName 
	  WHEN T2.TransType = 202 THEN T36.SeriesName 
	  WHEN T2.TransType = 204 THEN T38.SeriesName 
	 ELSE  T4.SeriesName END AS SERIE
 FROM
    OACT AS T0 
        JOIN JDT1 AS T1  ON T0.AcctCode = T1.Account  
        JOIN OJDT AS T2  ON T1.TransId = T2.TransId
   LEFT JOIN OCRD AS T3  ON T1.ContraAct = T3.CardCode OR T1.U_InfoCo01 = T3.CardCode
   LEFT JOIN NNM1 AS T4  ON T2.Series = T4.Series
   LEFT JOIN OINV AS T5  ON T2.BaseRef = T5.DocNum AND T2.TransType = T5.ObjType
   LEFT JOIN NNM1 AS T6  ON T5.Series = T6.Series 
   LEFT JOIN ORIN AS T7  ON T2.BaseRef = T7.DocNum AND T2.TransType = T7.ObjType
   LEFT JOIN NNM1 AS T8  ON T7.Series = T8.Series 
   LEFT JOIN ODLN AS T9  ON T2.BaseRef = T9.DocNum AND T2.TransType = T9.ObjType
   LEFT JOIN NNM1 AS T10 ON T9.Series = T10.Series 
   LEFT JOIN ORDN AS T11 ON T2.BaseRef = T11.DocNum AND T2.TransType = T11.ObjType
   LEFT JOIN NNM1 AS T12 ON T11.Series = T12.Series 
   LEFT JOIN OPCH AS T13 ON T2.BaseRef = T13.DocNum AND T2.TransType = T13.ObjType
   LEFT JOIN NNM1 AS T14 ON T13.Series = T14.Series 
   LEFT JOIN ORPC AS T15 ON T2.BaseRef = T15.DocNum AND T2.TransType = T15.ObjType
   LEFT JOIN NNM1 AS T16 ON T15.Series = T16.Series 
   LEFT JOIN OPDN AS T17 ON T2.BaseRef = T17.DocNum AND T2.TransType = T17.ObjType
   LEFT JOIN NNM1 AS T18 ON T17.Series = T18.Series 
   LEFT JOIN ORPD AS T19 ON T2.BaseRef = T19.DocNum AND T2.TransType = T19.ObjType
   LEFT JOIN NNM1 AS T20 ON T19.Series = T20.Series 
   LEFT JOIN ORCT AS T21 ON T2.BaseRef = T21.DocNum AND T2.TransType = T21.ObjType
   LEFT JOIN NNM1 AS T22 ON T21.Series = T22.Series 
   LEFT JOIN OVPM AS T23 ON T2.BaseRef = T23.DocNum AND T2.TransType = T23.ObjType
   LEFT JOIN NNM1 AS T24 ON T23.Series = T24.Series 
   LEFT JOIN OIGN AS T25 ON T2.BaseRef = T25.DocNum AND T2.TransType = T25.ObjType
   LEFT JOIN NNM1 AS T26 ON T25.Series = T26.Series 
   LEFT JOIN OIGE AS T27 ON T2.BaseRef = T27.DocNum AND T2.TransType = T27.ObjType
   LEFT JOIN NNM1 AS T28 ON T27.Series = T28.Series 
   LEFT JOIN OWTR AS T29 ON T2.BaseRef = T29.DocNum AND T2.TransType = T29.ObjType
   LEFT JOIN NNM1 AS T30 ON T29.Series = T30.Series 
   LEFT JOIN OIPF AS T31 ON T2.BaseRef = T31.DocNum AND T2.TransType = T31.ObjType
   LEFT JOIN NNM1 AS T32 ON T31.Series = T32.Series 
   LEFT JOIN OMRV AS T33 ON T2.BaseRef = T33.DocNum AND T2.TransType = T33.ObjType
   LEFT JOIN NNM1 AS T34 ON T33.Series = T34.Series 
   LEFT JOIN OWOR AS T35 ON T2.BaseRef = T35.DocNum 
   LEFT JOIN NNM1 AS T36 ON T35.Series = T36.Series 
   LEFT JOIN ODPO AS T37 ON T2.BaseRef = T37.DocNum AND T2.TransType = T37.ObjType
   LEFT JOIN NNM1 AS T38 ON T37.Series = T38.Series 
   LEFT JOIN OITR AS T39 ON T2.BaseRef = T39.ReconNum 
) AS TX ON T0.Account = TX.CUENTA AND TX.Trans = T0.TransID AND TX.LINEID = T0.Line_ID
WHERE 
    T0.Account = '11050504' 
    AND TaxDate BETWEEN '2019-04-01' AND GETDATE()

Este informe es como un libro auxiliar, espero les sirva.

1 me gusta

Pero… si partimos del hecho de que lo que está bien y donde se tiene que ver la información es en el mayor de la cuenta en SAP… ¿Para que hacer un query que sustituya lo que ya tienes?

1 me gusta

Por que solo saca los saldo por una sola cuenta no por varias.

Buen día,

Esta es la solución completa disculpen la demora, no habia tenido tiempo compartirla espero les sirva.

/*SELECT FROM [dbo].[OACT] C0*/
DECLARE @CUENTA1 VARCHAR(30)
/* WHERE */
SET @CUENTA1 = /* C0.AcctCode */'11050501'

/*SELECT FROM [dbo].[OACT] C1*/
DECLARE @CUENTA2 VARCHAR(30)
/* WHERE */
SET @CUENTA2  = /* C1.AcctCode */'11050505'

/*SELECT FROM [dbo].[OJDT] F0*/
DECLARE @INI DATETIME 
/* WHERE */
SET @INI = /* F0.RefDate  */ '2019-04-01'

/*SELECT FROM [dbo].[OJDT] F1*/
DECLARE @FIN DATETIME
/* WHERE */
SET @FIN = /* F1.RefDate */  GETDATE()

SELECT
      CUENTA
	 ,[No ASIENTO]
	 ,TERCERO 
	 ,[FECHA DE CONTABILIZACION]
	 ,[FECHA DOCUMENTO]
	 ,[FECHA VENCIMIENTO]
	 ,NOTA
	 ,LINE
	 ,ORIGEN
	 ,SERIE
	 ,[No DOC]
	 ,[No TRANSACCION]
	 ,DEBITO
	 ,CREDITO
	 ,SALDO
FROM
(
SELECT 
     T0.AcctCode AS CUENTA
	,T2.Number AS [No ASIENTO]
	,CASE 	
	   WHEN T2.TransType = 13  THEN T5.CardName
	   WHEN T2.TransType = 14  THEN T7.CardName
	   WHEN T2.TransType = 15  THEN T9.CardName
	   WHEN T2.TransType = 16  THEN T11.CardName
	   WHEN T2.TransType = 18  THEN T13.CardName
	   WHEN T2.TransType = 19  THEN T15.CardName
	   WHEN T2.TransType = 20  THEN T17.CardName
	   WHEN T2.TransType = 21  THEN T19.CardName
	   WHEN T2.TransType = 24  THEN T21.CardName
	   WHEN T2.TransType = 30  THEN T3.CardName
	   WHEN T2.TransType = 46  THEN T23.CardName
	   WHEN T2.TransType = 59  THEN T25.CardName
	   WHEN T2.TransType = 60  THEN T27.CardName
	   WHEN T2.TransType = 67  THEN T29.CardName
	   WHEN T2.TransType = 69  THEN T31.SuppName
	   WHEN T2.TransType = 162 THEN T33.CardName
	   WHEN T2.TransType = 202 THEN T35.CardCode
	   WHEN T2.TransType = 204 THEN T37.CardName
	   WHEN T2.TransType = 321 THEN ''
	 ELSE T3.CardName END AS TERCERO 
	,T2.RefDate AS [FECHA DE CONTABILIZACION]
	,T2.TaxDate AS [FECHA DOCUMENTO]
	,T2.DueDate AS [FECHA VENCIMIENTO]
	,T1.LineMemo AS NOTA
	,CASE 	
	   WHEN T2.TransType = 13  THEN 'RF-Factura Deudores' 
	   WHEN T2.TransType = 14  THEN 'RC-Nota Credito Clientes'
	   WHEN T2.TransType = 15  THEN 'NE-Entrega'
	   WHEN T2.TransType = 16  THEN 'DV-Devolucion'
	   WHEN T2.TransType = 18  THEN 'TT-Factura Proveedores'
	   WHEN T2.TransType = 19  THEN 'PC-Nota Credito Proveedores'
	   WHEN T2.TransType = 20  THEN 'EP-Entrada Mercancias'
	   WHEN T2.TransType = 21  THEN 'DM-Devolucion Mercancías'
	   WHEN T2.TransType = 24  THEN 'PR-Pagos Recibidos'
	   WHEN T2.TransType = 30  THEN 'AS-Asiento'
	   WHEN T2.TransType = 46  THEN 'PP-Pagos Efectuados'
	   WHEN T2.TransType = 59  THEN 'EM-Entrada Mercancías'
	   WHEN T2.TransType = 60  THEN 'OA-Emisión para producción'
	   WHEN T2.TransType = 67  THEN 'IM-Transferencia de Stock'
	   WHEN T2.TransType = 69  THEN 'DI-Precio Entrega'
	   WHEN T2.TransType = 162 THEN 'RI-Revalorización Inventario'
	   WHEN T2.TransType = 202 THEN 'OF-Orden de Fabricación'
	   WHEN T2.TransType = 204 THEN 'AN-F Anticipo Proveedores'
	   WHEN T2.TransType = 321 THEN 'ID-Reconciliación Interna'
	  ELSE T2.TransType
	END AS ORIGEN
    ,CASE 
	  WHEN T2.TransType = 13  THEN T6.SeriesName 	
	  WHEN T2.TransType = 14  THEN T8.SeriesName 
	  WHEN T2.TransType = 15  THEN T10.SeriesName 
	  WHEN T2.TransType = 16  THEN T12.SeriesName 
	  WHEN T2.TransType = 18  THEN T14.SeriesName 
	  WHEN T2.TransType = 19  THEN T16.SeriesName 
	  WHEN T2.TransType = 20  THEN T18.SeriesName 
	  WHEN T2.TransType = 21  THEN T20.SeriesName 
	  WHEN T2.TransType = 24  THEN T22.SeriesName 
	  WHEN T2.TransType = 30  THEN T4.SeriesName
	  WHEN T2.TransType = 46  THEN T24.SeriesName 
	  WHEN T2.TransType = 59  THEN T26.SeriesName 
	  WHEN T2.TransType = 60  THEN T28.SeriesName 
	  WHEN T2.TransType = 67  THEN T30.SeriesName 
	  WHEN T2.TransType = 69  THEN T32.SeriesName 
	  WHEN T2.TransType = 162 THEN T34.SeriesName 
	  WHEN T2.TransType = 202 THEN T36.SeriesName 
	  WHEN T2.TransType = 204 THEN T38.SeriesName 
	 ELSE  T4.SeriesName END AS SERIE
	 ,T1.BaseRef AS [No DOC]
	 ,T2.TransId AS [No TRANSACCION]
	 ,T1.Line_ID AS LINE
	 ,T1.Debit AS DEBITO
	 ,T1.Credit AS CREDITO
 FROM  
    OACT AS T0 
        JOIN JDT1 AS T1  ON T0.AcctCode = T1.Account 
        JOIN OJDT AS T2  ON T1.TransId = T2.TransId
   LEFT JOIN OCRD AS T3  ON T1.ContraAct = T3.CardCode
   LEFT JOIN NNM1 AS T4  ON T2.Series = T4.Series
   LEFT JOIN OINV AS T5  ON T2.BaseRef = T5.DocNum AND T2.TransType = T5.ObjType
   LEFT JOIN NNM1 AS T6  ON T5.Series = T6.Series 
   LEFT JOIN ORIN AS T7  ON T2.BaseRef = T7.DocNum AND T2.TransType = T7.ObjType
   LEFT JOIN NNM1 AS T8  ON T7.Series = T8.Series 
   LEFT JOIN ODLN AS T9  ON T2.BaseRef = T9.DocNum AND T2.TransType = T9.ObjType
   LEFT JOIN NNM1 AS T10 ON T9.Series = T10.Series 
   LEFT JOIN ORDN AS T11 ON T2.BaseRef = T11.DocNum AND T2.TransType = T11.ObjType
   LEFT JOIN NNM1 AS T12 ON T11.Series = T12.Series 
   LEFT JOIN OPCH AS T13 ON T2.BaseRef = T13.DocNum AND T2.TransType = T13.ObjType
   LEFT JOIN NNM1 AS T14 ON T13.Series = T14.Series 
   LEFT JOIN ORPC AS T15 ON T2.BaseRef = T15.DocNum AND T2.TransType = T15.ObjType
   LEFT JOIN NNM1 AS T16 ON T15.Series = T16.Series 
   LEFT JOIN OPDN AS T17 ON T2.BaseRef = T17.DocNum AND T2.TransType = T17.ObjType
   LEFT JOIN NNM1 AS T18 ON T17.Series = T18.Series 
   LEFT JOIN ORPD AS T19 ON T2.BaseRef = T19.DocNum AND T2.TransType = T19.ObjType
   LEFT JOIN NNM1 AS T20 ON T19.Series = T20.Series 
   LEFT JOIN ORCT AS T21 ON T2.BaseRef = T21.DocNum AND T2.TransType = T21.ObjType
   LEFT JOIN NNM1 AS T22 ON T21.Series = T22.Series 
   LEFT JOIN OVPM AS T23 ON T2.BaseRef = T23.DocNum AND T2.TransType = T23.ObjType
   LEFT JOIN NNM1 AS T24 ON T23.Series = T24.Series 
   LEFT JOIN OIGN AS T25 ON T2.BaseRef = T25.DocNum AND T2.TransType = T25.ObjType
   LEFT JOIN NNM1 AS T26 ON T25.Series = T26.Series 
   LEFT JOIN OIGE AS T27 ON T2.BaseRef = T27.DocNum AND T2.TransType = T27.ObjType
   LEFT JOIN NNM1 AS T28 ON T27.Series = T28.Series 
   LEFT JOIN OWTR AS T29 ON T2.BaseRef = T29.DocNum AND T2.TransType = T29.ObjType
   LEFT JOIN NNM1 AS T30 ON T29.Series = T30.Series 
   LEFT JOIN OIPF AS T31 ON T2.BaseRef = T31.DocNum AND T2.TransType = T31.ObjType
   LEFT JOIN NNM1 AS T32 ON T31.Series = T32.Series 
   LEFT JOIN OMRV AS T33 ON T2.BaseRef = T33.DocNum AND T2.TransType = T33.ObjType
   LEFT JOIN NNM1 AS T34 ON T33.Series = T34.Series 
   LEFT JOIN OWOR AS T35 ON T2.BaseRef = T35.DocNum 
   LEFT JOIN NNM1 AS T36 ON T35.Series = T36.Series 
   LEFT JOIN ODPO AS T37 ON T2.BaseRef = T37.DocNum AND T2.TransType = T37.ObjType
   LEFT JOIN NNM1 AS T38 ON T37.Series = T38.Series 
   LEFT JOIN OITR AS T39 ON T2.BaseRef = T39.ReconNum 
) AS T1
JOIN
(
  SELECT
     T0.Account ,T0.TransId  ,T0.Line_ID
	 ,SUM(T0.Debit-T0.Credit) OVER (PARTITION BY T0.Account ORDER BY  T0.TaxDate ,T0.TransId ,T0.Line_ID  )  AS SALDO 
  FROM JDT1 AS T0  
) AS T2 ON T1.CUENTA = T2.Account AND [No TRANSACCION] = TransId AND T1.LINE = T2.Line_ID 
WHERE 
  CUENTA BETWEEN @CUENTA1 AND @CUENTA2
  AND [FECHA DOCUMENTO] BETWEEN @INI AND @FIN
ORDER BY CUENTA ,[FECHA DOCUMENTO],[No TRANSACCION] ,LINE

4 Me gusta