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
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()
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.
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.
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?
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