ALTER PROCEDURE dbo.Statements
-- This stored procedure will develop end of month statement data.
-- --------------------------------------------------------------------------------------------------------------------------------
-- Steps include:
-- Retrieve the active accounts	NOTE: All active accounts must be retrieved since
-- we need those with no activity as they may 
-- have a balance brought forward.
-- Retrieve the current transactions
-- Retrieve the Balance Brought Forward (BBF)

/*
	(
	@BeginDate 			SMALLDATETIME,
	@EndDate			SMALLDATETIME
	)
*/
AS

SELECT				-- This select is to hold the results of all of the other selects
	CustNmbr, 
	StmtName, 
	CustName, 
	Address1, 
	Address2, 
	Address3, 
	Country, 
	City, 
	State, 
	Zip, 
	Balance,
	Bucket1,
	Bucket2,
	Bucket3,
	Bucket4,
	PostDate,	
	DocNumbr, 
	ChekNmbr, 
	OrderTransAmount,
	BBF
	
FROM 
(
			SELECT		-- This select gets the customer data, the aging data, and the transactions
				RTRIM(Accounts.CUSTNMBR) AS CUSTNMBR, 
				RTRIM(Accounts.STMTNAME) AS STMTNAME, 
				RTRIM(Accounts.CUSTNAME) AS CUSTNAME, 
				RTRIM(Accounts.ADDRESS1) AS ADDRESS1, 
				RTRIM(Accounts.ADDRESS2) AS ADDRESS2, 
				RTRIM(Accounts.ADDRESS3) AS ADDRESS3, 
				RTRIM(Accounts.COUNTRY)	AS COUNTRY, 
				RTRIM(Accounts.CITY)	AS CITY, 
				RTRIM(Accounts.STATE)	AS STATE, 
				RTRIM(Accounts.ZIP)	AS ZIP, 
				RTRIM(Accounts.INACTIVE)	AS INACTIVE, 
				RTRIM(Aging.CUSTBLNC)	AS Balance,
				RTRIM(Aging.AGPERAMT_1) AS Bucket1,
				RTRIM(Aging.AGPERAMT_2) AS Bucket2,
				RTRIM(Aging.AGPERAMT_3) AS Bucket3,
				RTRIM(Aging.AGPERAMT_4) AS Bucket4,
				RTRIM(CAST(CurrentTX.POSTDATE as SMALLDATETIME)) AS POSTDATE,	
				RTRIM(CurrentTX.DOCNUMBR)	AS DOCNUMBR, 
				RTRIM(CurrentTX.CHEKNMBR)	AS CHEKNMBR, 
				RTRIM(CurrentTX.RMDTYPAL)	AS RMDTYPAL, 
				RTRIM(CurrentTX.ORTRXAMT) AS ORTRXAMT,
				CONVERT(MONEY,CurrentTX.ORTRXAMT * _UPIRMstatementsDocType.Multiplier) AS [OrderTransAmount],
					(SELECT 		--CUSTNMBR ,
						SUM(BBF) AS BBF
					-- Calculates the BBF on the Open Transactions (OTX)
					FROM	(
						SELECT	
						OTX.CUSTNMBR,
						CONVERT(MONEY,ISNULL(SUM(OTX.ORTRXAMT * _UPIRMstatementsDocType.Multiplier), 0) ) AS BBF
						FROM 	UPI.DBO.RM30101 AS OTX
						INNER JOIN dbo._UPIRMstatementsDocType ON OTX.RMDTYPAL = dbo._UPIRMstatementsDocType.RMDTYPAL
						WHERE OTX.VOIDSTTS = 0
						--AND OTX.DOCDATE  < @EndDate
						GROUP BY OTX.CUSTNMBR
					UNION
					-- Calculates the BBF on the Current Transactions (CurTX)	
						SELECT	CurTX.CUSTNMBR,
						CONVERT(MONEY,ISNULL(SUM(CurTX.ORTRXAMT * _UPIRMstatementsDocType.Multiplier), 0) ) AS BBF
						FROM 	UPI.DBO.RM20101 AS CurTX
						INNER JOIN dbo._UPIRMstatementsDocType ON CurTX.RMDTYPAL = dbo._UPIRMstatementsDocType.RMDTYPAL
						WHERE CurTX.VOIDSTTS = 0
						--AND CTX.DOCDATE < @EndDate
					--AS BBF
						GROUP BY CurTX.CUSTNMBR )X
					WHERE X.CUSTNMBR = Accounts.CUSTNMBR
					GROUP BY X.CUSTNMBR)
			FROM 
				UPI.DBO.rm00101 Accounts 
				

GROUP BY X.CUSTNMBR)

GROUP BY X.CUSTNMBR) as foo