Skip to Content
author's profile photo Former Member
Former Member

Saldo de Socios de Negocios a fechas anteriores

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Sep 23, 2011 at 11:39 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 23, 2011 at 02:45 PM

    A como veo tu necesidad, el reporte nativo de SAP te deberia funcionar, ya lo revisaste?

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.