SQL 2008 Query Help

ALTER PROCEDURE [dbo].[PXQ6380a] (@AsMM as varchar(2),
				            @AsYYYY as varchar(4),
				            @InsGrpNme as varchar(100),
				            @LicNbr as varchar(100),
/*C04*/						@gbloptState varchar(4))
AS
	SET NOCOUNT ON

	Declare @AsOfPriorMonth as datetime
	Declare @ParmDate as datetime

	SELECT @ParmDate = dbo.LastDayInMonth(Convert(datetime, @AsMM+'/01/'+@AsYYYY))
	SELECT @AsOfPriorMonth = dbo.LastDayInMonth(DATEADD(month, -1, @ParmDate))

	SELECT  Insured,
			GroupFirmName,
			Agent,
			Producer,
			LoanCheckbox,
			License,
			ARKey,
			BilledDate,
			Policy,
			PolEffDate,
			ExpDate,
			TransCovEffDate,
			CoAcctgDate,
			DueDate,
			EntryDate,
			AmtType,
			[Premium Billed],
			'1' as HeaderConstant,
			CONVERT(money, SUM(ISNULL([Premium Received],0))) as [Premium Received],
			CONVERT(money, ([Premium Billed] + SUM(ISNULL([Premium Received],0)))) AS [Premium Outstanding]
	FROM
	(	
		SELECT Insured =
				CASE 
					WHEN Insured.FirstName = ' ' THEN Insured.LastName
					ELSE Insured.LastName + ', ' + Insured.FirstName
				END, 
			Insured.GroupFirmName as GroupFirmName,
			Agent =
				CASE 
					WHEN Agent.FirstName = ' ' THEN Agent.LastName
					ELSE Agent.LastName + ', ' + Agent.FirstName
				END, 	
			Producer =
				CASE 
					WHEN Producer.FirstName = ' ' THEN Producer.LastName
					ELSE Producer.LastName + ', ' + Producer.FirstName
				END, 
			LoanCheckbox = 
				CASE wellandp.ARItem.LoanCheckbox 
					WHEN 'Y' THEN 'Y' 
					ELSE 'N' 
				END,
			SUBSTRING(wellandp.ARItem.ReferKey, 2, LEN(wellandp.ARItem.ReferKey)) AS License, 
			wellandp.ARItem.ARKey,
			BilledDate =
				CASE 
/* C02*/				WHEN wellandp.ARItem.AmtType = 'Prem70' AND ARDocuments.ChangedDate IS NULL AND ARDocuments.SystemDate IS NULL THEN Prem30BilledDateFile_1.BilledDate 
					WHEN ARDocuments.ChangedDate > ARDocuments.SystemDate THEN ARDocuments.ChangedDate
					WHEN ARDocuments.ChangedDate is null THEN ARDocuments.SystemDate
					ELSE ARDocuments.SystemDate
				END,
			wellandp.ARItem.GrossAmt AS [Premium Billed],
			[Premium Received] =
				CASE 
--           					WHEN ISNULL(CashItemFile.DisbRecptDate, CONVERT(datetime, '1/1/2999')) < @AsOfPriorMonth THEN wellandp.ARItem_OffAmounts.OffAmounts
					WHEN SUBSTRING(wellandp.ARItem_OffPeriods.OffPeriods,1,4)+SUBSTRING(wellandp.ARItem_OffPeriods.OffPeriods,6,2) <=  Convert(varchar(4), Year(@AsOfPriorMonth)) + CASE WHEN Convert(varchar(2), Month(@AsOfPriorMonth)) < 10 THEN '0' + Convert(varchar(2), Month(@AsOfPriorMonth)) ELSE Convert(varchar(2), Month(@AsOfPriorMonth)) END THEN wellandp.ARItem_OffAmounts.OffAmounts
					ELSE 0 
				END,
			CashItemFile.DisbRecptDate, 
			wellandp.PolicyGeneral.PolNo AS Policy, 
			wellandp.PolicyGeneral.PolEffDate, 
			wellandp.PolicyGeneral.ExpDate,
			wellandp.PolicyScollect.TransCovEffDate, 
			wellandp.PolicyScollect.CoBookDate, 
/* *************************************************** */
			CoAcctgDate = 
				CASE	WHEN PolicyStat.CoAcctgDate IS NOT NULL THEN PolicyStat.CoAcctgDate
					WHEN wellandp.PolicyScollect.CoBookDate >= wellandp.PolicyScollect.TransCovEffDate THEN wellandp.PolicyScollect.CoBookDate
					ELSE wellandp.PolicyScollect.TransCovEffDate
				END,
--			PolicyStat.CoAcctgDate,
/* *************************************************** */
			wellandp.ARItem.DueDate,
			wellandp.ARItem.Entrydate,
			wellandp.ARItem.AmtType
		FROM	wellandp.ARItem
--           			INNER JOIN wellandp.ARItem_ActivityKey ON wellandp.ARItem.ARKey = wellandp.ARItem_ActivityKey.ARKey 
			INNER JOIN wellandp.PolicyScollect ON wellandp.ARItem.PolScolKey = wellandp.PolicyScollect.PolScolKey 
/*C01*/		INNER JOIN (SELECT DISTINCT ActivityKey, CoAcctgDate from dbo.vw_PolicyStat WHERE TOB IN ('DM', 'DR')) PolicyStat ON PolicyStat.ActivityKey = wellandp.PolicyScollect.ActivityKey 
			INNER JOIN wellandp.PolicyGeneral ON wellandp.PolicyScollect.PolGenKey = wellandp.PolicyGeneral.PolGenKey 
			INNER JOIN wellandp.ReferMaster Insured ON wellandp.ARItem.ReferKey = Insured.ReferKey 
			LEFT OUTER JOIN wellandp.ARItem_OffKeys ON wellandp.ARItem.ARKey = wellandp.ARItem_OffKeys.ARKey 
			LEFT OUTER JOIN wellandp.ARItem_OffAmounts ON wellandp.ARItem_OffKeys.ARItem_OffKeys_ID = wellandp.ARItem_OffAmounts.ARItem_OffAmounts_ID 
  		    LEFT OUTER JOIN wellandp.ARItem_OffPeriods ON wellandp.ARItem_OffPeriods.ARItem_OffPeriods_ID = wellandp.ARItem_OffKeys.ARItem_OffKeys_ID
/*C03*/  --		LEFT OUTER JOIN wellandp.ARDocuments ON wellandp.ARDocuments.SeqNo = wellandp.ARItem.ReferenceNo
/*C03*/		LEFT OUTER JOIN dbo.vw_ARDocuments_INV AS ARDocuments ON	ARDocuments.SeqNo = wellandp.ARItem.ReferenceNo AND 
/*C05*/																	ARDocuments.OptState = @Gbloptstate			
			LEFT OUTER JOIN wellandp.ReferMaster Agent ON 'G' + wellandp.PolicyGeneral.AgentCode = Agent.ReferKey 
			LEFT OUTER JOIN wellandp.ReferMaster Producer ON 'P' + wellandp.PolicyGeneral.ProducerCode = Producer.ReferKey 
			LEFT OUTER JOIN wellandp.CashItem_ARKeys ON wellandp.ARItem_OffKeys.OffKeys = wellandp.CashItem_ARKeys.ARKeys 
			LEFT OUTER JOIN wellandp.CashItem AS CashItemFile ON wellandp.CashItem_ARKeys.CashItemKey = CashItemFile.CashItemKey
/* C02*/ /*C03*/		LEFT OUTER JOIN dbo.vw_Prem30_BilledDate AS Prem30BilledDateFile_1 ON wellandp.PolicyScollect.PolNo = Prem30BilledDateFile_1.Policy AND
/* C02*/									wellandp.PolicyScollect.PolEffDate = Prem30BilledDateFile_1.PolEffDate AND
/* C02*/									SUBSTRING(wellandp.ARItem.ReferKey,2,LEN(wellandp.ARItem.ReferKey)) = Prem30BilledDateFile_1.License AND
/* C02*/									wellandp.PolicyScollect.ActivityKey  = Prem30BilledDateFile_1.ActivityKey
		WHERE	 ISNULL(wellandp.ARItem.ToARKey, ' ') = ' ' 
		AND ((wellandp.ARItem.Status = 'C' 
		AND wellandp.ARItem.OpenAmt = 0) 
		OR (wellandp.ARItem.Status = 'O')) 
		AND wellandp.ARItem.GrossAmt > 0  
		AND
/*C04*/		--	 wellandp.ARItem.ArKey LIKE @gbloptState + '%' AND
/*C04*/		substring(wellandp.ARItem.ARKey, 1, charindex('/', wellandp.ARItem.ARKey)) = @gbloptState  
        AND
        	(@InsGrpNme = ' All Insureds' OR @LicNbr = SUBSTRING(Insured.ReferKey,2,LEN(Insured.ReferKey))  OR @InsGrpNme = Insured.GroupFirmName)
		) AS ARFile
	WHERE	ARFile.CoAcctgDate <=  @AsOfPriorMonth
	GROUP BY 	Insured,
			GroupFirmName,
			Agent,
			Producer,
			LoanCheckbox,
			License,
			ARKey,
			BilledDate,
			[Premium Billed],
			Policy,
			PolEffDate,
			ExpDate,
			TransCovEffDate,
			CoAcctgDate,
			DueDate,
			EntryDate,
			AmtType
	HAVING	([Premium Billed] + SUM(ISNULL([Premium Received],0))) > 0
	ORDER BY	Insured

Select allOpen in new window