on 09-22-2011 11:22 PM
Buen día a todos!
Estoy usando SAP B1 8.8 PL10 y SQL Server 2008 Standar
Esta vez necesito que esta consulta sume todas las facturas de los proveedores y reste los pagos y notas de crédito del saldo de mis proveedores desde la fecha inicial hasta la ultima fecha en la que se ha ingresado un documento en SAP.
La consulta esta así:
SELECT T0.[CardCode],
T0.[CardName],
'',
'',
'',
T1.[CardCode],
0,
isnull((select t10.[Balance]+SUM(t11.[DocTotal])-SUM(t12.[DocTotal])-SUM(t13.[DocTotal])
from ocrd t10 left join opch t11 on t10.[CardCode] = t11.[CardCode]
left join ovpm t12 on t10.[CardCode] = t12.[CardCode]
left join orpc t13 on t10.[CardCode] = t13.[CardCode]
where (t11.[DocDate] >= @fechaini and t11.[DocDate] <= @fechaact) and
(t12.[DocDate] >= @fechaini and t12.[DocDate] <= @fechaact) and
(t13.[DocDate] >= @fechaini and t13.[DocDate] <= @fechaact) and
t12.[Canceled] != 'Y' and t10.[CardCode] != '0001' and t10.[CardType] != 'S' and substring(t10.[cardcode],1,1) != 'E' and
t10.[CardCode] = T0.[CardCode] group by t10.[Balance]),T0.[Balance]),
0.00,
0.00,
0.00,
0.00
FROM OCRD T0
JOIN OCRD T1 ON T0.[CardCode] = T1.[CardCode]
WHERE T0.[CardCode] != '0001' AND T0.[CardType] = 'S' AND SUBSTRING(T0.[CardCode],1,1) != 'E'
GROUP BY T0.[CardCode],
T0.[CardName],
T0.[Balance],
T1.[CardCode]
esta dentro de un SP, al parecer la subquery no esta arrojando ningun resultado pues me muestra el saldo actual del Socio de Negocios.
Alex
De que manera este infome que quieres obtener difiere del antiguedad de saldos?
De todas maneras revisa este post
-
/*SELECT * FROM [dbo].[OCRD] T0 INNER JOIN [dbo].[JDT1] T1 ON T1.ShortName = T0.CardCode WHERE T1.RefDate BETWEEN '[%0]' AND '[%1]'*/
DECLARE @fechaini DATETIME
SET @fechaini = '[%0]'
DECLARE @fechafin DATETIME
SET @fechafin = '[%1]'
SELECT T1.CardCode, T1.CardName,
ISNULL((SELECT SUM(T2.Debit-T2.Credit) FROM JDT1 T2 WHERE T2.ShortName = T1.CardCode AND T2.RefDate < @fechaini),0)'Saldo Inicial',
ISNULL((SELECT SUM(T2.Debit-T2.Credit) FROM JDT1 T2 WHERE T2.ShortName = T1.CardCode AND T2.RefDate BETWEEN @fechaini AND @fechafin),0)'Saldo Mes',
ISNULL((SELECT SUM(T2.Debit-T2.Credit) FROM JDT1 T2 WHERE T2.ShortName = T1.CardCode AND T2.RefDate <= @fechafin),0)'Saldo Final'
FROM [dbo].[OCRD] T1
GROUP BY T1.CardCode, T1.CardName
Saludos
Edited by: Floyola on Sep 23, 2011 10:20 AM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
A como veo tu necesidad, el reporte nativo de SAP te deberia funcionar, ya lo revisaste?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
99 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.