DECLARE @AsOnDate SMALLDATETIME
DECLARE @FlagProduction AS BIT
DECLARE @tblAcAging AS TABLE (
	fLocation VARCHAR(3)
	,fCategory VARCHAR(15)
	,fAccount INT
	,fName VARCHAR(30)
	,fOffice VARCHAR(2)
	,fPhone VARCHAR(14)
	,fType TINYINT
	,-- 1 - Private, 2 - Contract , 3 - Insurance, 0 - Unapplied Payments, 4 - Production
	fOver180 MONEY
	,fOver120 MONEY
	,fOver90 MONEY
	,fOver60 MONEY
	,fOver30 MONEY
	,fCurrent MONEY
	,fBalance MONEY
	,fLastPay MONEY
	,fLastDate SMALLDATETIME
	,fCheck VARCHAR(10)
	,fTotal MONEY
	,fFlag VARCHAR(1)
	,--* means sent for collection
	fCredit MONEY
	)

SET @FlagProduction = 1
SET @AsOnDate = CONVERT(SMALLDATETIME, '2012/NOV/30')

IF @FlagProduction = 1
BEGIN
	DECLARE @mFlocation VARCHAR(3)
	DECLARE @mFaccount INT
	DECLARE @mFPatient INT
	DECLARE @mFcategory VARCHAR(25)
	DECLARE @mFtxnumber INT
	DECLARE @mFtxsuffix TINYINT
	DECLARE @mFtxdate SMALLDATETIME
	DECLARE @mForthogen TINYINT
	DECLARE @mfDelete TINYINT
	DECLARE @mePvt MONEY
	DECLARE @meIns MONEY
	DECLARE @mePayPvt MONEY
	DECLARE @mePvtCR MONEY
	DECLARE @mePvtDB MONEY
	DECLARE @mePayIns MONEY
	DECLARE @meInsCR MONEY
	DECLARE @meInsDB MONEY
	DECLARE @meOff VARCHAR(1)
	DECLARE @meLast VARCHAR(25)
	DECLARE @meFirst VARCHAR(25)
	DECLARE @mePhone VARCHAR(25)
	DECLARE @meColl BIT

	DECLARE TempCursor CURSOR
	FOR
	SELECT Account.Flocation
		,Account.Faccount
		,TxPayPlan.FPatient
		,TxPayPlan.Fcategory
		,TxPayPlan.Ftxnumber
		,TxPayPlan.Ftxsuffix
		,TxPayPlan.Ftxdate
		,Txpayplan.Forthogen
		,TxPayPlan.fDelete
		,ePvt = CASE 
			WHEN (TxPayPlan.fContractNo = 0)
				THEN Fpvtdue
			ELSE 0
			END
		,eIns = TxPayPlan.Finsdue
		,ePayPvt = Sum(CASE 
				WHEN (
						(Payment.fPayType <= 50)
						AND (Payment.finsurance <> 1)
						AND (PayDtl.fContractNo = 0)
						AND (Txpayplan.Finscode = PayDtl.FinsKode)
						AND (TxPayPlan.FPatient = PayDtl.FPatient)
						AND (PayDtl.FDelete <> 1)
						AND Payment.fDelete <> 1
						--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
						AND (Payment.fpaydate <= @AsOnDate)
						)
					THEN PayDtl.fAmount
				ELSE 0
				END)
		,ePvtCR = Sum(CASE 
				WHEN (
						(Payment.fpaytype > 50)
						AND (Payment.fpaytype <= 60)
						AND (Payment.finsurance <> 1)
						AND (PayDtl.fContractNo = 0)
						AND (Txpayplan.Finscode = PayDtl.FinsKode)
						AND (TxPAyPlan.FPatient = PayDtl.FPatient)
						AND (PayDtl.FDelete <> 1)
						AND Payment.fDelete <> 1
						--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
						AND (Payment.fpaydate <= @AsOnDate)
						)
					THEN PayDtl.fAmount
				ELSE 0
				END)
		,ePvtDB = Sum(CASE 
				WHEN (
						(Payment.fpaytype > 60)
						AND (Payment.finsurance <> 1)
						AND (PayDtl.fContractNo = 0)
						AND (Txpayplan.Finscode = PayDtl.FinsKode)
						AND (TxPAyPlan.FPatient = PayDtl.FPatient)
						AND (PayDtl.FDelete <> 1)
						AND Payment.fDelete <> 1
						--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
						AND (Payment.fpaydate <= @AsOnDate)
						)
					THEN PayDtl.fAmount
				ELSE 0
				END)
		,ePayIns = Sum(CASE 
				WHEN (
						(payment.fpaytype <= 50)
						AND (Payment.finsurance = 1)
						AND (Txpayplan.Finscode = PayDtl.FinsKode)
						AND (TxPAyPlan.FPatient = PayDtl.FPatient)
						AND (PayDtl.FDelete <> 1)
						AND Payment.fDelete <> 1
						--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
						AND (Payment.fpaydate <= @AsOnDate)
						)
					THEN PayDtl.fAmount
				ELSE 0
				END)
		,eInsCR = Sum(CASE 
				WHEN (
						(payment.fpaytype > 50)
						AND (Payment.fpaytype <= 60)
						AND (Payment.finsurance = 1)
						AND (Txpayplan.Finscode = PayDtl.FinsKode)
						AND (TxPAyPlan.FPatient = PayDtl.FPatient)
						AND (PayDtl.FDelete <> 1)
						AND Payment.fDelete <> 1
						--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
						AND (Payment.fpaydate <= @AsOnDate)
						)
					THEN PayDtl.fAmount
				ELSE 0
				END)
		,eInsDB = Sum(CASE 
				WHEN (
						(Payment.fpaytype > 60)
						AND (Payment.finsurance = 1)
						AND (Txpayplan.Finscode = PayDtl.FinsKode)
						AND (TxPAyPlan.FPatient = PayDtl.FPatient)
						AND (PayDtl.FDelete <> 1)
						AND Payment.fDelete <> 1
						--AND (@rOrthoGen IN (Payment.fOrthoGen,10))
						AND (Payment.fpaydate <= @AsOnDate)
						)
					THEN PayDtl.fAmount
				ELSE 0
				END)
		,eOff = TxPayPlan.Foffice
		,eLast = Account.Flastname
		,eFirst = Account.Ffirstname
		,ePhone = Account.Fhomephone
		,eColl = Account.fCollSend
	FROM (
		(
			Account INNER JOIN TxPayPlan ON (Account.Flocation = TxPayPlan.Flocation)
				AND (Account.Faccount = TxPayPlan.Faccount)
			) LEFT JOIN PayDtl ON (TxPayPlan.Flocation = PayDtl.fLocation)
			AND (TxPayPlan.Faccount = PayDtl.fAccount)
			AND (TxPayPlan.Ftxnumber = PayDtl.fTxNumber)
		)
	LEFT JOIN Payment ON (PayDtl.fLocation = Payment.fLocation)
		AND (PayDtl.fPayBatchNo = Payment.fPayBatchNo)
	WHERE
		--TxPayPlan.flocation = @rLocation AND
		--@rOffice IN (TxPayPlan.foffice,'')
		--AND @rCategory IN (TxPayPlan.fcategory,'')
		--AND @rOrthoGen IN (TxPayPlan.fOrthoGen,10)
		--AND
		TxPayPlan.fDelete <> 1
	GROUP BY Account.Flocation
		,Account.Faccount
		,TxPayPlan.FPatient
		,TxPayPlan.Fcategory
		,TxPayPlan.Ftxnumber
		,TxPayPlan.Ftxsuffix
		,TxPayPlan.Ftxdate
		,TxPayPlan.Forthogen
		,TxPayPlan.fDelete
		,TxPayPlan.fContractNo
		,TxPayPlan.fInsdue
		,TxPayPlan.fPvtDue
		,TxPayPlan.fOffice
		,Account.fLastName
		,Account.fFirstName
		,Account.fHomePhone
		,Account.fCollSend
	ORDER BY Account.Faccount;

	OPEN TempCursor

	FETCH NEXT
	FROM TempCursor
	INTO @mFlocation
		,@mFaccount
		,@mFPatient
		,@mFcategory
		,@mFtxnumber
		,@mFtxsuffix
		,@mFtxdate
		,@mForthogen
		,@mfDelete
		,@mePvt
		,@meIns
		,@mePayPvt
		,@mePvtCR
		,@mePvtDB
		,@mePayIns
		,@meInsCR
		,@meInsDB
		,@meOff
		,@meLast
		,@meFirst
		,@mePhone
		,@meColl;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE @PrivateAmount AS MONEY
		DECLARE @Insuranceamount AS MONEY
		DECLARE @Days INT

		SET @PrivateAmount = (ISNULL(@mePvt, 0) + ISNULL(@mepvtdb, 0)) - (ISNULL(@mepaypvt, 0) + ISNULL(@mepvtcr, 0))
		SET @Insuranceamount = (ISNULL(@meins, 0) + ISNULL(@meinsDB, 0)) - (ISNULL(@mepayins, 0) + ISNULL(@meinsCR, 0))

		IF @PrivateAmount = 0
			AND @Insuranceamount = 0
			GOTO SkipTxPayPlan

		SET @Days = DATEDIFF(d, @mFtxdate, @AsOnDate) + 1

		IF @Days < 0
			GOTO SkipTxPayPlan

		IF @PrivateAmount = 0
			GOTO NextInsurance

		IF NOT EXISTS (
				SELECT *
				FROM @tblAcAging
				WHERE fLocation = @mFlocation
					AND fAccount = @mFaccount
					AND fCategory = @mFcategory
					AND fType = 1
				)
		BEGIN
			INSERT INTO @tblAcAging (
				fLocation
				,fAccount
				,fCategory
				,fType
				,fName
				,fOffice
				,fPhone
				,fOver180
				,fOver120
				,fOver90
				,fOver60
				,fOver30
				,fCurrent
				,fFlag
				)
			VALUES (
				@mFlocation
				,@mFaccount
				,@mFcategory
				,1
				,ISNULL(@meLast, '') + ' ' + ISNULL(@meFirst, '')
				,ISNULL(@meOff, '')
				,ISNULL(@mePhone, '')
				,0
				,0
				,0
				,0
				,0
				,0
				,CASE 
					WHEN @meColl = 1
						THEN '*'
					ELSE ''
					END
				)
		END

		DECLARE @_Over180 AS MONEY
		DECLARE @_Over120 AS MONEY
		DECLARE @_Over90 AS MONEY
		DECLARE @_Over60 AS MONEY
		DECLARE @_Over30 AS MONEY
		DECLARE @_Current AS MONEY

		SET @_Over180 = 0
		SET @_Over120 = 0
		SET @_Over90 = 0
		SET @_Over60 = 0
		SET @_Over30 = 0
		SET @_Current = 0

		IF @Days >= 180
			SET @_Over180 = @PrivateAmount

		IF @Days >= 120
			AND @Days < 180
			SET @_Over120 = @PrivateAmount

		IF @Days >= 90
			AND @Days < 120
			SET @_Over90 = @PrivateAmount

		IF @Days >= 60
			AND @Days < 90
			SET @_Over60 = @PrivateAmount

		IF @Days >= 30
			AND @Days < 60
			SET @_Over30 = @PrivateAmount

		IF @Days >= 0
			AND @Days < 30
			SET @_Current = @PrivateAmount

		UPDATE @tblAcAging
		SET fOver180 = fOver180 + @_Over180
			,fOver120 = fOver120 + @_Over120
			,fOver90 = fOver90 + @_Over90
			,fOver60 = fOver60 + @_Over60
			,fOver30 = fOver30 + @_Over30
			,fCurrent = fCurrent + @_Current
			,fBalance = fCurrent + @_Current + fOver30 + @_Over30 + fOver60 + @_Over60 + fOver90 + @_Over90 + fOver120 + @_Over120 + fOver180 + @_Over180
		WHERE fLocation = @mFlocation
			AND fAccount = @mFaccount
			AND fCategory = @mFcategory
			AND fType = 1;

		NextInsurance:

		IF @Insuranceamount = 0
			GOTO SkipTxPayPlan

		IF NOT EXISTS (
				SELECT *
				FROM @tblAcAging
				WHERE fLocation = @mFlocation
					AND fAccount = @mFaccount
					AND fCategory = @mFcategory
					AND fType = 3
				)
		BEGIN
			INSERT INTO @tblAcAging (
				fLocation
				,fAccount
				,fCategory
				,fType
				,fName
				,fOffice
				,fPhone
				,fOver180
				,fOver120
				,fOver90
				,fOver60
				,fOver30
				,fCurrent
				,fFlag
				)
			VALUES (
				@mFlocation
				,@mFaccount
				,@mFcategory
				,3
				,ISNULL(@meLast, '') + ' ' + ISNULL(@meFirst, '')
				,ISNULL(@meOff, '')
				,ISNULL(@mePhone, '')
				,0
				,0
				,0
				,0
				,0
				,0
				,CASE 
					WHEN @meColl = 1
						THEN '*'
					ELSE ''
					END
				)
		END

		SET @_Over180 = 0
		SET @_Over120 = 0
		SET @_Over90 = 0
		SET @_Over60 = 0
		SET @_Over30 = 0
		SET @_Current = 0

		IF @Days >= 180
			SET @_Over180 = @Insuranceamount

		IF @Days >= 120
			AND @Days < 180
			SET @_Over120 = @Insuranceamount

		IF @Days >= 90
			AND @Days < 120
			SET @_Over90 = @Insuranceamount

		IF @Days >= 60
			AND @Days < 90
			SET @_Over60 = @Insuranceamount

		IF @Days >= 30
			AND @days < 60
			SET @_Over30 = @Insuranceamount

		IF @Days >= 0
			AND @Days < 30
			SET @_Current = @Insuranceamount

		UPDATE @tblAcAging
		SET fOver180 = fOver180 + @_Over180
			,fOver120 = fOver120 + @_Over120
			,fOver90 = fOver90 + @_Over90
			,fOver60 = fOver60 + @_Over60
			,fOver30 = fOver30 + @_Over30
			,fCurrent = fCurrent + @_Current
			,fBalance = fCurrent + @_Current + fOver30 + @_Over30 + fOver60 + @_Over60 + fOver90 + @_Over90 + fOver120 + @_Over120 + fOver180 + @_Over180
		WHERE fLocation = @mFlocation
			AND fAccount = @mFaccount
			AND fCategory = @mFcategory
			AND fType = 3;

		SkipTxPayPlan:

		FETCH NEXT
		FROM TempCursor
		INTO @mFlocation
			,@mFaccount
			,@mFPatient
			,@mFcategory
			,@mFtxnumber
			,@mFtxsuffix
			,@mFtxdate
			,@mForthogen
			,@mfDelete
			,@mePvt
			,@meIns
			,@mePayPvt
			,@mePvtCR
			,@mePvtDB
			,@mePayIns
			,@meInsCR
			,@meInsDB
			,@meOff
			,@meLast
			,@meFirst
			,@mePhone
			,@meColl;
	END

	CLOSE TempCursor

	DEALLOCATE TempCursor
END

SELECT *
FROM @tblAcAging t
WHERE t.fAccount = 966