Skip to Content
0
Former Member
Feb 12, 2015 at 05:19 PM

not able to drop the added transactions from OPDF table

17 Views

Hope Anyone Would Help,

The Problem is that I am not able to drop the added transactions from OPDF table results, due to which the report shows overstated balances

the Stored Procedure is as written below

USE [***_Live]

GO

/****** Object: StoredProcedure [dbo].[Customer_Recovery_Report] Script Date: 12/10/2014 13:52:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[Customer_Recovery_Report]

-- Add the parameters for the stored procedure here

@TODATE date,

@City nvarchar(50),

@Number1 int

AS

BEGIN

DECLARE @Dt1 date

DECLARE @Dt2 date

DECLARE @Dt3 date

SET @Dt1 = DATEADD(DAY, -@Number1, @TODATE)

SET @Dt2 = DATEADD(DAY, -@Number1, @Dt1)

SET @Dt3 = DATEADD(DAY, -@Number1, @Dt2)

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT T0.[CardCode], T0.[CardName], T0.[City],

[dbo].[Customer_Balance](@TODATE,T0.[CardCode]) as [Balance],

SUM(T3.[DocTotal]) AS [D-Payments], T0.[OrdersBal],

[dbo].[Customer_Payments](@Dt1,@TODATE,T0.[CardCode]) as [Payment1],

[dbo].[Customer_Invoices](@Dt1,@TODATE,T0.[CardCode]) as [Sales1],

[dbo].[Customer_Payments](@Dt2,@Dt1,T0.[CardCode]) as [Payment2],

[dbo].[Customer_Invoices](@Dt2,@Dt1,T0.[CardCode]) as [Sales2],

[dbo].[Customer_Payments](@Dt3,@Dt2,T0.[CardCode]) as [Payment3],

[dbo].[Customer_Invoices](@Dt3,@Dt2,T0.[CardCode]) as [Sales3]

FROM OCRD T0 LEFT OUTER JOIN OPDF T3 ON T0.CardCode = T3.[CardCode]

WHERE T0.City = @City and T0.frozenFor = 'N' and T3.DocType <> 'A'

GROUP BY T0.[CardCode], T0.[CardName], T0.[City], [Balance], T0.[OrdersBal]

END