Saldo de Cuenta de Mayor SAP -Detalle

la siguiente consulta sql nos provee un reporte el cual nos indicara el saldo de la cuenta de mayor esta misma nos tiene que servir para saber el saldo es igual al reporte que sap nos proporciona.

nos lo proporciona de la tabla JDT1 y la Tabla OJDT con las misma columnas este nos sirve para estos procedimientos es funcional para query manager y para trabajarse con management Sql.

nos provee los siguientes datos esto quiere decir

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’

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

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

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

/*SELECT FROM [dbo].[OJDT] F1*/
DECLARE @FIN DATETIME
/* WHERE */
SET @FIN = /* F1.RefDate */  '2020-11-30'

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

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *