SET NOCOUNT ON
--Use vpmser
declare @TableToDelete varchar(255), @SQL_CMD nvarchar(4000)

declare A_Cursor Cursor Static For 
	Select [Name] From dbo.sysobjects 
	Where [Name] like N'zTaxDetails%' 
	and OBJECTPROPERTY(id, N'IsTable') = 1
	
open A_Cursor
	Fetch next from A_Cursor into @TableToDelete

	 WHILE @@FETCH_STATUS = 0
	   BEGIN
			Set @SQL_CMD = N'Drop Table ' + @TableToDelete 

			exec sp_executesql @SQL_CMD
			
		  FETCH NEXT FROM A_Cursor into  @TableToDelete
	   END
		   
CLOSE A_cursor
DEALLOCATE A_cursor

IF exists (Select * From dbo.sysobjects Where id = object_id(N'zTaxAddresses') and OBJECTPROPERTY(id, N'IsTable') = 1)
	Drop Table zTaxAddresses

IF exists (Select * From dbo.sysobjects Where id = object_id(N'zCDSDetails') and OBJECTPROPERTY(id, N'IsTable') = 1)
	Drop Table zCDSDetails

IF exists (Select * From dbo.sysobjects Where id = object_id(N'xCDSDetails') and OBJECTPROPERTY(id, N'IsTable') = 1)
	Drop Table xCDSDetails

IF exists (Select * From dbo.sysobjects Where id = object_id(N'zLodgmentHistory') and OBJECTPROPERTY(id, N'IsTable') = 1)
	Drop Table zLodgmentHistory

IF exists (Select * From dbo.sysobjects Where id = object_id(N'zAEPMPlusLog') and OBJECTPROPERTY(id, N'IsTable') = 1)
	Drop Table zAEPMPlusLog
	
IF OBJECT_ID('tempdb..#AETaxTables') IS NOT NULL
	Drop Table #AETaxTables

IF OBJECT_ID('tempdb..#AddressTypes') IS NOT NULL
	Drop Table #AddressTypes

IF OBJECT_ID('tempdb..#Addresses') IS NOT NULL
	Drop Table #Addresses

IF OBJECT_ID('tempdb..#ResponsibilityTypes') IS NOT NULL
	Drop Table #ResponsibilityTypes

 GO

Create Table zAEPMPlusLog (LogId int IDENTITY (1,1), Summary int, ErrMsg varchar(1000))

Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, '*** AE PM Plus Pre Implementation Data Check Utility ***'
Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'Version 2.2'
Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'Date Run: ' + convert(varchar(12), getdate(), 103)
Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, '********************************************************'

--	AE PM Plus Pre Implementation Data Check Utility
--	Created:	7 April 2011
--	Created By:	Stephen Gaff
--
--	Description:	This utility runs a number of data checks in 
--					preparation for an AE PM Plus installation.
--
--	Checks included:
--	SQL TAX database exists
--	SQL Current Tax Year and Central Database exists
--	1:Many Client Groups
--	Client Groups without a return code the same as the Client Group code
--	1:1 Clients with different return code
--	CDS Codes not in AE PM 
--	Tax returns with different name in AE PM
--	Client Type in AE PM does not match Form Type in AE Tax
--	Contact Type in AE PM does not match Form Type in AE Tax
--	Returns with an invalid Partner or Manager or Employee
--	AE PM database does not have at least 3 address types
--
--	Modified:
--	Version	Date		By			Description
--	1.1		15/04/2011	S Gaff		Improve SQL tax table checking
--									Display SQL version
--									Add field headings in checks output
--	1.2		16/08/2011	S Gaff		Add return codes/names to 1:Many list
--									Add address checking routines
--	1.3		01/09/2011	S Gaff		Add responsibility checking routines
--									Add Completed but not Lodged check
--									Create error log table and produce summary as well as detail
--	1.4		12/09/2011	S Gaff		Exclude Postal from the AE PM addresses missing corresponding Tax Front Cover address type test
--	1.5		15/09/2011	S Gaff		Fix issue with Tax Ledgers beginning with 00 e.g. 00TAX
--									Add SQL statements to log file
--	1.6		28/09/2011	S Gaff		Add new checks for Return Code in 1:Many group already existing in CDS and/or AE PM
--	1.7		06/02/2012	S Gaff		Add the AE PM Office field where relevant to sort results by Office
--	1.8		08/02/2012	S Gaff		Modify the CDS Codes not in AE PM test to include attached returns
--	1.9		03/07/2012	S Gaff		Add new checks for client codes > 10 characters in AE PM
--									Add new checks for invalid characters in AE PM client code, CDS code & Tax return code
--									Add new checks for Return Code in 1:1 different codes that already exist in CDS or AE PM
--	1.10	19/09/2012	S Gaff		Add test for lower case characters in client/CDS/return codes.
--									Include all years and forms in Completed but not yet Lodged data check. 
--									Include new check for AE PM Address Types with the same first 10 characters.
--									Include test for existing Address Type mappings, e.g. Corporate Compliance
--									Add CDS and Return names to the Different 1 to 1 data check
--
--	2.0		22/07/2013	S Gaff		Update script to handle the new table names for Tax 2013
--									Update Completed but Not Lodged check to include only I,C,P,T,F forms
--									Add prior year history tables for Lodgment History test
--	2.1		05/08/2013	S Gaff		Add lodged status to CDS Not in PM test
--									1 to Many test, change zCDSDetails to xCDSDetails to pick up name for Other type CDS records
--	2.2		10/10/2013	S Gaff		Add Phone Number Type check for TFN, ABN and ACN
--									Add Prior Years check for 1 to Many Groups that have returns not in the current year.
--									Add SQL server name and AE PM database name
--									Dislay Company names from Company table instead of the Registered Name from the Config table.
--									Registered Name is no longer used in Licencing.
--									Add Mail tab address to zTaxAddress as Address Type, Mail, for future use.
--

declare @ContactId int, @Name varchar(255), @ClientCode varchar(21), @ClientCodePrior varchar(21)
Declare @AffectedReturns int, @ReturnCode varchar(21), @ErrMsg varchar(255)
Declare @Title varchar(50), @GivenNames varchar(255), @Surname varchar(255), @FormType varchar(255), @FormTypeAEPM varchar(255)
Declare @Pref varchar(50), @FName varchar(255), @MName varchar(255), @LName varchar(255)
Declare @Middle varchar(60), @Last varchar(255), @First varchar(255), @Prefix varchar(255)
Declare @Count int, @ContactType varchar(255), @EmployeeCode varchar(255), @TaxEmployee varchar(255)
Declare @AETaxDatabase varchar(50), @DeleteTempFiles bit, @AETaxLedger varchar(50), @TaxYear varchar(4)
declare @Params nvarchar(4000), @SQL nvarchar(4000), @TableName varchar(50), @taxYearID CHAR(2)
declare @Year varchar(4), @ClientName varchar(255), @CDSTableName varchar(50)
declare @Office varchar(60), @OfficePrior varchar(60)
declare @Status varchar (60), @CDSName varchar(255), @CDSCode varchar(10), @CDS_Status varchar(60)
Declare @DateCompleted varchar(10), @FormId varchar(10)
Declare @ResultDB bit, @NextYearFound bit, @SQL_errors bit
Declare @ResultTaxYear bit, @ResultCDS bit
DECLARE @RETURN_TABLE_PREFIX CHAR(4), @RETURN_TABLE_SUFFIX CHAR(2)
Declare @AddressType varchar(50), @AddressType_AEPM varchar(50), @AddressTypeId int, @AddressType2 varchar(50)
Declare @AddressTypeMappingCode varchar(50), @AddressTypeMappingName varchar(50)
Declare @ResponsibilityType varchar(50), @ResponsibilityType_AEPM varchar(50), @ResponsibilityTypeId int
Declare @Addr1_Tx varchar(255), @Addr2_Tx varchar(255), @Town_Tx varchar(255), @State_Tx varchar(255), @Postcode_Tx varchar(255), @Country_Tx varchar(255)
Declare @Addr1_PM varchar(255), @Addr2_PM varchar(255), @Addr3_PM varchar(255), @Town_PM varchar(255), @State_PM varchar(255), @Postcode_PM varchar(255), @Country_PM varchar(255)
Declare @YearPriorsCount int, @YearPriors char(4), @taxYearPriorsID CHAR(2), @YearPriorsCountLimit int, @IsLodged varchar(255), @IsDeletedOnRollover varchar(255)
Declare @PhoneNumDesc varchar(255), @TableToDelete varchar(255)

SET @RETURN_TABLE_PREFIX = 'DS6T'	
SET @RETURN_TABLE_SUFFIX = '11'	
SET @CDSTableName = 'DS6CD101'

Set @YearPriorsCountLimit = 2		-- how many prior years to include, default is two years

SET @DeleteTempFiles = 0		-- 0 = do not delete temp files, 1 = delete temp files


Create Table #AddressTypes (AddressType_Tax varchar(50), AddressType_AEPM varchar(50), AddressTypeId int)
Insert Into #AddressTypes (AddressType_Tax, AddressType_AEPM)
	Select 'Postal', Min(AddressTypeDesc)
	From AddressType
	where AddressTypeDesc Like 'Postal%'

Insert Into #AddressTypes (AddressType_Tax, AddressType_AEPM)
	Select 'Home', Min(AddressTypeDesc)
	From AddressType
	where AddressTypeDesc Like 'Home%'

Insert Into #AddressTypes (AddressType_Tax, AddressType_AEPM)
	Select 'Business', Min(AddressTypeDesc)
	From AddressType
	where AddressTypeDesc Like 'Business%'

Update #AddressTypes
Set AddressTypeId = AST.AddressTypeId
From #AddressTypes T
Inner Join AddressType AST on T.AddressType_AEPM COLLATE DATABASE_DEFAULT = AST.AddressTypeDesc

Create Table #ResponsibilityTypes (ResponsibilityType_Tax varchar(50), ResponsibilityType_AEPM varchar(50), ResponsibilityTypeId int)
Insert Into #ResponsibilityTypes (ResponsibilityType_Tax, ResponsibilityType_AEPM)
	Select 'Manager', Min([Description])
	From ResponsibilityType
	where [Description] Like 'Manager%'

Insert Into #ResponsibilityTypes (ResponsibilityType_Tax, ResponsibilityType_AEPM)
	Select 'Employee', Min([Description])
	From ResponsibilityType
	where [Description] Like 'Employee%'

Update #ResponsibilityTypes
Set ResponsibilityTypeId = RT.ResponsibilityTypeId
From #ResponsibilityTypes R
Inner Join ResponsibilityType RT on R.ResponsibilityType_AEPM COLLATE DATABASE_DEFAULT = RT.[Description]

--	select * from #ResponsibilityTypes

--	Find AE PM companies

Set @ClientName = (select CompanyName From Company Where CompanyId = (select MIN(CompanyId) From Company))
Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'AE PM Company Names : ' 

declare A_Cursor Cursor Static For 
	select CompanyName From Company
	Order By CompanyId
	
Open A_Cursor	
		Fetch next from A_Cursor into @ClientName

		 WHILE @@FETCH_STATUS = 0
		   BEGIN
				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, '  ' + @ClientName
				
			  FETCH NEXT FROM A_Cursor into  @ClientName
		   END
CLOSE A_cursor
DEALLOCATE A_cursor				

Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, '********************************************************'

--	Display SQL version

Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0,  'SQL Details:'
Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0,  'SQL Server Name: ' + @@SERVERNAME
Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0,  @@version


Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, '********************************************************'

--	Check whether AE PM is linked to a sql tax database
Set @ResultDB = 0
Set @SQL_errors = 0


Set @AETaxDatabase = (Select KeyValue From AETAX_Configuration where KeyName = 'Connection_Database' )
Set @AETaxLedger = (Select KeyValue From AETAX_Configuration where KeyName = 'SystemServices_Client' )

Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'MYOB AE database = ' + DB_NAME()
Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'SQL Tax database = ' + @AETaxDatabase
Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'SQL Tax ledger   = ' + @AETaxLedger

Set @SQL = N'IF (select DB_ID(N''' + @AETaxDatabase + ''')) is not null'
Set @SQL = @SQL + N' Begin Set @ResultDB = 1 End '

Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL

exec sp_executesql @sql, N'@ResultDB bit out', @ResultDB out

IF  @ResultDB <> 1
	Begin
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'SQL Tax database ' + @AETaxDatabase + ' not found.'
		Set @SQL_errors = 1
	End
Else
	Begin
	
		CREATE TABLE #AETaxTables (DBName VARCHAR(256),
								 SchemaName VARCHAR(256),
								 TableName VARCHAR(256))
								 
		SET @SQL = 'USE ' + @AETaxDatabase + ';
					INSERT INTO #AETaxTables
					SELECT '''+ @AETaxDatabase + ''' AS DBName,
					SCHEMA_NAME(schema_id) AS SchemaName,
					name AS TableName
					FROM sys.tables'

		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL

		EXEC (@SQL)
								 
		If  datepart(m, getdate()) > 6
			Begin
				set @Year =  datepart(yyyy, getdate())
			End
		Else
			Begin
				set @Year = datepart(yyyy, getdate()) - 1
			End;

		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'Current Tax Year = ' + @Year

		set @taxYearID = (select case @Year
			when '2003' then 'A4'
			when '2004' then 'A5'
			when '2005' then 'A6'
			when '2006' then 'A7'
			when '2007' then 'A8'
			when '2008' then 'A9'
			when '2009' then 'A0'
			when '2010' then 'A1'
			when '2011' then 'A2'
			when '2012' then 'A3'
			when '2013' then 'C4'
			when '2014' then 'C5'
			when '2015' then 'C6'
			when '2016' then 'C7'
			when '2017' then 'C8'
			when '2018' then 'C9'
			when '2019' then 'C0'
			else ''
			end )
			


		Set @TableName = @RETURN_TABLE_PREFIX + @taxYearID + @RETURN_TABLE_SUFFIX
		Set @SQL = N'IF EXISTS (SELECT DBName From #AETaxTables Where TableName = ''' + @TableName + N''')'
		Set @SQL = @SQL + N'Set @ResultTaxYear = 1 '
		Set @ResultTaxYear = 0
		
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL
		
		exec sp_executesql @sql, N'@ResultTaxYear bit out', @ResultTaxYear out

		IF  @ResultTaxYear <> 1
			Begin
				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'SQL Tax Database Table ' + @TableName + ' not found'
				Set @SQL_errors = 1
			End
		Else
			Begin
--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'SQL Tax Database Table ' + @TableName + ' found'
				Set @SQL = N'SELECT * Into zTaxDetails From ' + @AETaxDatabase + N'..'+ @TableName 

				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL

				exec sp_executesql @sql

				--If  datepart(m, getdate()) > 5
				--	Begin
				--		Set @NextYearFound = 0
				--		Set @SQL = N'IF EXISTS (SELECT DBName From #AETaxTables Where TableName = '''+ @RETURN_TABLE_PREFIX + LTrim(RTrim(Str((convert(int,@taxYearID)+1)))) + @RETURN_TABLE_SUFFIX + N''')'
				--		Set @SQL = @SQL + N'Set @NextYearFound = 1 '

				--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL

				--		exec sp_executesql @sql, N'@NextYearFound bit out', @NextYearFound out
						
				--		IF  @NextYearFound = 1
				--			Begin
				--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, '*** Warning: Next Tax Year ' + LTrim(RTrim(Str((convert(int,@Year)+1)))) + ' found.'
				--			End;
				--	End

				Set @ResultCDS = 0
				Set @SQL = N'IF EXISTS (SELECT DBName From #AETaxTables Where TableName = ''' + @CDSTableName + N''')'
				Set @SQL = @SQL + N'Set @ResultCDS = 1 '

				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL

				exec sp_executesql @sql, N'@ResultCDS bit out', @ResultCDS out

				IF  @ResultCDS <> 1
					Begin
						Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'SQL Central Database Table  ' + @CDSTableName + ' not found'
						Set @SQL_errors = 1
					End
				Else

					Begin
--						Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'SQL Central Database Table ' + @CDSTableName + ' found'
						Set @SQL = N'SELECT * Into xCDSDetails From ' + @AETaxDatabase + N'..'+ @CDSTableName 

						Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL

						exec sp_executesql @sql


						Set @SQL = N'Select Distinct TAX.[Return], R.[FormType], R.IsLodged, ' 
						Set @SQL = @SQL + N'TAX.Area, Case  When TAX.Area = ''Ad   01  '' then ''Postal'' '
						Set @SQL = @SQL + N'When TAX.Area = ''Ad   03  '' then ''Previous'' '
						Set @SQL = @SQL + N'When TAX.Area = ''Ad   02  '' and R.[FormType] = ''I'' Then ''Home'' '
						Set @SQL = @SQL + N'When TAX.Area = ''Ad   02  '' and R.[FormType] = ''C'' Then ''Business'' '
						Set @SQL = @SQL + N'When TAX.Area = ''Ad   04  '' and R.[FormType] in (''I'',''P'',''T'', ''F'') then ''Business'' '
						Set @SQL = @SQL + N'When TAX.Area = ''Ad   05  '' then ''Mail'' '
						Set @SQL = @SQL + N'Else '''' End as AddressType, '
						Set @SQL = @SQL + N'TAX.IsStoredInCDS, CDS.[Table Code], '
						Set @SQL = @SQL + N'TAX.Street1, TAX.Street2, TAX.[Town/Suburb], TAX.State, TAX.Postcode, TAX.Country '
						Set @SQL = @SQL + N'Into zTaxAddresses '
						Set @SQL = @SQL + N'From ' + @AETaxDatabase + N'..' + @TableName + N' R '
						Set @SQL = @SQL + N'Inner Join ' + @AETaxDatabase + N'..' + @RETURN_TABLE_PREFIX + @taxYearID + N'41 TAX on R.ReturnCode = TAX.[Return] '
						Set @SQL = @SQL + N'left Join ' + @AETaxDatabase + N'..DS6CD107 CDS on TAX.CDSCODE = CDS.[Table Code]+ LTrim(Str(CDS.[Unique Id])) '
						Set @SQL = @SQL + N'left Join ' + @AETaxDatabase + N'..DS6CD103 CADDR on RTrim(CDS.[Data Code]) = RTrim(CADDR.[Data Code]) '
						Set @SQL = @SQL + N'Where  R.BackupOfReturn IS NULL  '
						Set @SQL = @SQL + N'and TAX.Area in (''Ad   01  '', ''Ad   02  '', ''Ad   03  '', ''Ad   04  '', ''Ad   05  '')  '
						Set @SQL = @SQL + N'Order By TAX.[Return], TAX.Area '

						Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL

						exec sp_executesql @sql
--						Print @SQL

						Set @SQL = N'Select  T.ReturnCode, T.[FormType], T.[Year], Convert(varchar(10),LH.[Date completed],103) as DateCompleted, LH.FormId, LH.[State], ' 
						Set @SQL = @SQL + N'RTrim(T.Title) as Title, RTrim(Replace(T.GivenNames, ''  '', '' '')) as GivenNames, RTrim(T.Surname) as Surname  '
						Set @SQL = @SQL + N'Into zLodgmentHistory '
						Set @SQL = @SQL + N'From ' + @AETaxDatabase + N'..' + @TableName + N' T '
						Set @SQL = @SQL + N'Inner Join ' + @AETaxDatabase + N'..[' + @AETaxLedger +  N'_DZZTB011] LH on T.ReturnCode = LH.[Return] and T.[Year] = LH.[Year] '
						Set @SQL = @SQL + N'Where T.DateLodged is  null and LH.[Date completed] is not null  '
						Set @SQL = @SQL + N'and LH.FormId in (''EC 01'', ''ET 01'', ''EI 01'', ''EP 01'', ''EF 01'')  '
						Set @SQL = @SQL + N'and Substring(LH.FormID, 2, 1) in (''I'',''P'',''T'',''C'',''F'')  '
						Set @SQL = @SQL + N'and LH.[state] NOT IN (''A'', ''a'', ''B'')  '
						Set @SQL = @SQL + N'and LH.[Year] = ' + @Year + N'  '
						Set @SQL = @SQL + N'Order By T.ReturnCode '

						Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL

						exec sp_executesql @sql

					End
			
			
				Set @YearPriorsCount = 1
				set @YearPriors = Convert(varchar(4),Convert(int, @Year) - @YearPriorsCount)
					
							
				WHILE ( @YearPriorsCount <= @YearPriorsCountLimit )
						 BEGIN
														
							set @taxYearPriorsID = (select case @YearPriors
								when '2003' then 'A4'
								when '2004' then 'A5'
								when '2005' then 'A6'
								when '2006' then 'A7'
								when '2007' then 'A8'
								when '2008' then 'A9'
								when '2009' then 'A0'
								when '2010' then 'A1'
								when '2011' then 'A2'
								when '2012' then 'A3'
								when '2013' then 'C4'
								when '2014' then 'C5'
								when '2015' then 'C6'
								when '2016' then 'C7'
								when '2017' then 'C8'
								when '2018' then 'C9'
								when '2019' then 'C0'
								else ''
								end )
								
							Set @TableName = @RETURN_TABLE_PREFIX + @taxYearPriorsID + @RETURN_TABLE_SUFFIX
							
							Set @SQL = N'SELECT * Into zTaxDetailsPrior' + @YearPriors + N' From ' + @AETaxDatabase + N'..'+ @TableName 

							--Print @SQL + ' ' + @YearPriors

							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL
							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'Prior Tax Year ' + Ltrim(RTrim(str(@YearPriorsCount))) + ' = ' + @YearPriors

							exec sp_executesql @SQL
							
							Set @SQL =  N' Insert Into zLodgmentHistory (ReturnCode, FormType, [Year], DateCompleted, FormId, [State], Title, GivenNames, Surname)  '
							Set @SQL = @SQL + N'Select  T.ReturnCode, T.[FormType], T.[Year], Convert(varchar(10),LH.[Date completed],103) as DateCompleted, LH.FormId, LH.[State], ' 
							Set @SQL = @SQL + N'RTrim(T.Title), RTrim(Replace(T.GivenNames, ''  '', '' '')), RTrim(T.Surname)  '
							Set @SQL = @SQL + N'From ' + @AETaxDatabase + N'..' + @TableName + N' T '
							Set @SQL = @SQL + N'Inner Join ' + @AETaxDatabase + N'..[' + @AETaxLedger +  N'_DZZTB011] LH on T.ReturnCode = LH.[Return] and T.[Year] = LH.[Year] '
							Set @SQL = @SQL + N'Where T.DateLodged is  null and LH.[Date completed] is not null  '
							Set @SQL = @SQL + N'and LH.FormId in (''EC 01'', ''ET 01'', ''EI 01'', ''EP 01'', ''EF 01'')  '
							Set @SQL = @SQL + N'and Substring(LH.FormID, 2, 1) in (''I'',''P'',''T'',''C'',''F'')  '
							Set @SQL = @SQL + N'and LH.[State] NOT IN (''A'', ''a'', ''B'')  '
							Set @SQL = @SQL + N'and LH.[Year] = ' + @YearPriors + N'  '
							Set @SQL = @SQL + N'Order By T.ReturnCode '

							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  9, @SQL

							exec sp_executesql @sql

							Set @YearPriorsCount = @YearPriorsCount + 1
							set @YearPriors = Convert(varchar(4),Convert(int, @Year) - @YearPriorsCount)
							

							
						END;
				End			
		End


If @SQL_errors = 1
	Begin
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'SQL data errors found.  Please contact MYOB Support.'
	End
Else
	Begin
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  0, 'SQL data table checks = Ok'
		
		

		Select IsNull(O.OfficeName,'') as Office, CDS.*
		Into zCDSDetails
		FROM  xCDSDetails CDS
		Left Join ClientSupplier CS on CDS.Code COLLATE Database_Default = CS.ClientCode
		Left Join Office O on CS.OfficeID = O.OfficeID
		Where  CDS.[Type] = 'C' and CDS.[Group] = 'C'

--		Select * From zCDSDetails
		
		set @OfficePrior = '***************'

		--	1:Many Client Groups

		Set @Count = 0

		declare A_Cursor Cursor Static For 
			SELECT CDS.Office, ClientGroup, AffectedReturns
			  FROM
			  (

					-- Obtain the client groups which are not one-one
					SELECT ClientCode AS ClientGroup,  COUNT(ReturnCode) AS AffectedReturns
					FROM
					(
						  -- Obtain the unique returns
						  SELECT ReturnCode, ClientCode
						  FROM zTaxDetails 
						  WHERE BackupOfReturn IS NULL 
					) LastAmendedReturn
					GROUP BY ClientCode
					HAVING COUNT(ReturnCode) > 1
			  ) ClientGroups
			  Left Join zCDSDetails CDS on convert (varbinary(10),ClientGroups.ClientGroup) = convert (varbinary(10),CDS.Code)
			Order By CDS.Office, ClientGroup

		open A_Cursor
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, '********************************************************'
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of 1 to Many Client Groups = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Client    : Number of returns'

		Fetch next from A_Cursor into @Office, @ClientCode, @AffectedReturns

		 WHILE @@FETCH_STATUS = 0
		   BEGIN
				Set @Name = (Select RTrim([Name]) 
							From xCDSDetails 
							Where convert (varbinary(10), [Code]) =  convert (varbinary(10), @ClientCode)
							and [GROUP] = 'C'
							) 
							
				If @Office <> @OfficePrior
					Begin
							Set @OfficePrior = @Office
							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
					End

				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' + IsNull(@Name,'') + ' : ' + LTrim(RTrim(Str(@AffectedReturns))) + ' returns'
				
				Set @Count = @Count + @AffectedReturns
				
				declare B_Cursor Cursor Static For 
					select T.ReturnCode, T.[FormType],
					RTrim(IsNull(T.Title,'')), RTrim(Replace(IsNull(T.GivenNames,''), '  ', ' ')), RTrim(IsNull(T.Surname,''))
					FROM zTaxDetails T
					Where T.ClientCode = @ClientCode and T.ReturnCode is not null
					Order By T.ReturnCode


				open B_Cursor
				Fetch next from B_Cursor into  @ReturnCode,  @FormType, @Title, @GivenNames, @Surname

				 WHILE @@FETCH_STATUS = 0
				   BEGIN

						Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '   ' + IsNull(@ReturnCode,'') + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
					Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
											  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End

					  FETCH NEXT FROM B_Cursor into  @ReturnCode, @FormType,  @Title, @GivenNames, @Surname
				   END
				CLOSE B_cursor
				DEALLOCATE B_cursor		
					
			  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode,  @AffectedReturns
		   END
		CLOSE A_cursor
		DEALLOCATE A_cursor

		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Returns in 1 to Many Client Groups = ' + Ltrim(RTrim(STR(@Count)))


		--	Client Groups without a return code the same as the Client Group code

		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Client Groups without a return code the same as the Client Group code.' 
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Client    : Number of returns'

		set @Count = 0
		set @OfficePrior = '***************'

		declare A_Cursor Cursor Static For 

				-- Obtain the client groups which are  one-many
			SELECT CDS.Office, ClientGroup, AffectedReturns
			  FROM
			  (

					-- Obtain the client groups which are not one-one
					SELECT ClientCode AS ClientGroup,  COUNT(ReturnCode) AS AffectedReturns
					FROM
					(
						  -- Obtain the unique returns
						  SELECT ReturnCode, ClientCode
						  FROM zTaxDetails --zTaxDetails for Tax 2010, DS6TA011 for 2009, DS6TA911 for 2008
						  WHERE BackupOfReturn IS NULL 
					) LastAmendedReturn
					GROUP BY ClientCode
					HAVING COUNT(ReturnCode) > 1
			  ) ClientGroups
			  Left Join zCDSDetails CDS on convert (varbinary(10),ClientGroups.ClientGroup) = convert (varbinary(10),CDS.Code)
			Order By CDS.Office, ClientGroup
			


		open A_Cursor

		Fetch next from A_Cursor into  @Office, @ClientCode, @AffectedReturns

		 WHILE @@FETCH_STATUS = 0
		   BEGIN
		   
			If @Office <> @OfficePrior
				Begin
						Set @OfficePrior = @Office
						Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
				
				End
					
			If @ClientCode not in (SELECT ReturnCode
							  FROM zTaxDetails
							  WHERE BackupOfReturn IS NULL )
				Begin
					Set @Name = (Select RTrim([Name]) 
							From xCDSDetails 
							Where convert (varbinary(10), [Code]) =  convert (varbinary(10), @ClientCode)
							and [GROUP] = 'C'
							)
							
					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' + IsNull(@Name,'') + ' : ' + LTrim(RTrim(Str(@AffectedReturns))) + ' returns'
					set @Count = @Count + 1
				End

			  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode, @AffectedReturns
		   END
		CLOSE A_cursor
		DEALLOCATE A_cursor
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Client Groups without a return code the same as the Client Group code = ' + Ltrim(RTrim(STR(@Count)))


		--	Return Code in 1:Many group already existing in CDS

		set @Count = 0
		set @OfficePrior = '***************'
			
		declare A_Cursor Cursor Static For 
			SELECT CDS.Office, ClientGroup, AffectedReturns
			  FROM
			  (

					-- Obtain the client groups which are not one-one
					SELECT ClientCode AS ClientGroup,  COUNT(ReturnCode) AS AffectedReturns
					FROM
					(
						  -- Obtain the unique returns
						  SELECT ReturnCode, ClientCode
						  FROM zTaxDetails 
						  WHERE BackupOfReturn IS NULL 
					) LastAmendedReturn
					GROUP BY ClientCode
					HAVING COUNT(ReturnCode) > 1
			  ) ClientGroups
			  Left Join zCDSDetails CDS on convert (varbinary(10),ClientGroups.ClientGroup) = convert (varbinary(10),CDS.Code)
			Order By CDS.Office, ClientGroup

		open A_Cursor
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return Codes in 1 to Many Client Groups that already exist in CDS ' 

		Fetch next from A_Cursor into  @Office, @ClientCode, @AffectedReturns

		 WHILE @@FETCH_STATUS = 0
		   BEGIN
				If @Office <> @OfficePrior
					Begin
							Set @OfficePrior = @Office
							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
					End
									
				declare B_Cursor Cursor Static For 
					select T.ReturnCode, T.[FormType],
					RTrim(IsNull(T.Title,'')), RTrim(Replace(IsNull(T.GivenNames,''), '  ', ' ')), RTrim(IsNull(T.Surname,'')),
					RTrim(C.[Name]) 
					FROM zTaxDetails T
					Inner Join zCDSDetails C on convert (varbinary(10), T.ReturnCode) =  convert (varbinary(10), C.[Code])
					Where T.ClientCode = @ClientCode and T.ReturnCode is not null and T.ReturnCode <> T.ClientCode
					Order By T.ReturnCode


				open B_Cursor
				If @@CURSOR_ROWS > 0
					begin
							Insert Into zAEPMPlusLog (Summary, ErrMsg) 
							Select  2, @ClientCode + ': ' + RTrim([Name]) 
							From zCDSDetails 
							Where convert (varbinary(10), [Code]) =  convert (varbinary(10), @ClientCode)
							and [GROUP] = 'C'
							
					End
				
				
				Fetch next from B_Cursor into  @ReturnCode,  @FormType, @Title, @GivenNames, @Surname, @Name

				 WHILE @@FETCH_STATUS = 0
				   BEGIN
				   
				   		set @Count = @Count + 1
	
						Insert Into zAEPMPlusLog (Summary, ErrMsg) 
						Select  2, '   ' + IsNull(@ReturnCode,'') + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
						Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
												  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End +
						' : CDS Client Name : ' + @Name

					  FETCH NEXT FROM B_Cursor into  @ReturnCode, @FormType,  @Title, @GivenNames, @Surname, @Name
				   END
				CLOSE B_cursor
				DEALLOCATE B_cursor		
					
			  FETCH NEXT FROM A_Cursor into @Office,  @ClientCode,  @AffectedReturns
		   END
		CLOSE A_cursor
		DEALLOCATE A_cursor
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Return Codes in 1 to Many Client Groups that already exist in CDS = ' + Ltrim(RTrim(STR(@Count)))



		--	Return Code in 1:Many group already existing in AE PM

		set @Count = 0
		set @OfficePrior = '***************'

		declare A_Cursor Cursor Static For 
			SELECT CDS.Office, ClientGroup, AffectedReturns
			  FROM
			  (

					-- Obtain the client groups which are not one-one
					SELECT ClientCode AS ClientGroup,  COUNT(ReturnCode) AS AffectedReturns
					FROM
					(
						  -- Obtain the unique returns
						  SELECT ReturnCode, ClientCode
						  FROM zTaxDetails --zTaxDetails for Tax 2010, DS6TA011 for 2009, DS6TA911 for 2008
						  WHERE BackupOfReturn IS NULL 
					) LastAmendedReturn
					GROUP BY ClientCode
					HAVING COUNT(ReturnCode) > 1
			  ) ClientGroups
			  Left Join zCDSDetails CDS on convert (varbinary(10),ClientGroups.ClientGroup) = convert (varbinary(10),CDS.Code)
			Order By CDS.Office, ClientGroup

		open A_Cursor
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return Codes in 1 to Many Client Groups that already exist in AE PM'

		Fetch next from A_Cursor into  @Office, @ClientCode, @AffectedReturns

		 WHILE @@FETCH_STATUS = 0
		   BEGIN
				If @Office <> @OfficePrior
					Begin
							Set @OfficePrior = @Office
							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
					End	
										
				declare B_Cursor Cursor Static For 
					select T.ReturnCode, T.[FormType],
					RTrim(IsNull(T.Title,'')), RTrim(Replace(IsNull(T.GivenNames,''), '  ', ' ')), RTrim(IsNull(T.Surname,'')),
					LTrim(IsNull(C.Pref,'') + ' ') + LTrim(IsNull(C.FName,'') + ' ') + LTrim(IsNull(C.MName,'') + ' ') +  RTrim(C.LName) 
					FROM zTaxDetails T
					Inner Join ClientSupplier CS on  T.ReturnCode Collate Database_Default =   CS.ClientCode
					Inner Join Contact C on CS.ContactId = C.ContactId
					Where T.ClientCode = @ClientCode and T.ReturnCode is not null and T.ReturnCode <> T.ClientCode
					Order By T.ReturnCode


				open B_Cursor
				
				If @@CURSOR_ROWS > 0
					begin
							Insert Into zAEPMPlusLog (Summary, ErrMsg) 
							Select  2, @ClientCode + ': ' + RTrim([Name]) 
							From zCDSDetails 
							Where convert (varbinary(10), [Code]) =  convert (varbinary(10), @ClientCode)
							and [GROUP] = 'C'
							
					End
					
				Fetch next from B_Cursor into  @ReturnCode,  @FormType, @Title, @GivenNames, @Surname, @Name

				 WHILE @@FETCH_STATUS = 0
				   BEGIN
				   
				   		set @Count = @Count + 1
		
						Insert Into zAEPMPlusLog (Summary, ErrMsg) 
						Select  2, '   ' + IsNull(@ReturnCode,'') + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
						Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
												  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End +
						' : AE PM Client Name : ' + @Name

					  FETCH NEXT FROM B_Cursor into  @ReturnCode, @FormType,  @Title, @GivenNames, @Surname, @Name
				   END
				CLOSE B_cursor
				DEALLOCATE B_cursor		
					
			  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode,  @AffectedReturns
		   END
		CLOSE A_cursor
		DEALLOCATE A_cursor
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Return Codes in 1 to Many Client Groups that already exist in AE PM = ' + Ltrim(RTrim(STR(@Count)))

--		Returns in 1 to Many Groups for prior tax years not in Current Year

		Set @YearPriorsCount = 1
		set @YearPriors = Convert(varchar(4),Convert(int, @Year) - @YearPriorsCount)
			
					
		WHILE ( @YearPriorsCount <= @YearPriorsCountLimit )
				 BEGIN
				
				Set @SQL =  N'Declare @Name varchar(255), @ClientCode varchar(21), @ClientCodePrior varchar(21), @Office varchar(255), @OfficePrior varchar(255)
							Declare @ReturnCode varchar(255), @FormType varchar(255), @Title varchar(255), @GivenNames varchar(255), @Surname varchar(255)
							Declare @IsLodged varchar(255), @IsDeletedOnRollover varchar(255), @Count int
							
							Declare A_Cursor Cursor Static For
							Select C.Office, T.ClientCode,   C.[Name], T.ReturnCode , T.FormType,
							T.Title, T.GivenNames, T.Surname, T.IsLodged, T.IsDeletedOnRollover
							From zTaxDetailsPrior' + @YearPriors + N' T
							Inner Join zCDSDetails C on convert (varbinary(10), T.ClientCode) = convert (varbinary(10), C.[Code]) and C.[GROUP] = ''C''
							Inner Join 
								(	
								select ClientCode, COUNT(*) as [Returns]
								From zTaxDetailsPrior' + @YearPriors + N' T
								WHERE BackupOfReturn IS NULL
								GROUP BY ClientCode 
								HAVING COUNT(*) > 1
								) as TM on T.ClientCode = TM.ClientCode

							Where T.BackupOfReturn IS NULL and  T.ReturnCode not in
							(
								Select T.ReturnCode
								From zTaxDetails T
								Inner Join 
									(	
									select ClientCode, COUNT(*) as [Returns]
									FROM zTaxDetails
									WHERE BackupOfReturn IS NULL
									GROUP BY ClientCode 
									HAVING COUNT(*) > 1
									) as TM on T.ClientCode = TM.ClientCode
									Where T.BackupOfReturn IS NULL 
							)	
							Order By T.ClientCode, T.ReturnCode
							
					set @OfficePrior = ''***************''
					set @ClientCodePrior = 	''***************''						
					open A_Cursor
					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, ''********************************************************''
					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, ''Number of Returns in 1 to Many Groups for Tax ' + @YearPriors + N' not in Current Year = '' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, ''Client     : Return    : Return Name  ''

					Fetch next from A_Cursor into @Office, @ClientCode, @Name, @ReturnCode, @FormType, @Title, @GivenNames, @Surname, @IsLodged, @IsDeletedOnRollover

					 WHILE @@FETCH_STATUS = 0
					   BEGIN
										
							If @Office <> @OfficePrior
								Begin
										Set @OfficePrior = @Office
										Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  ''Office : '' + @Office
								End
				
							If @ClientCode <> @ClientCodePrior
								Begin
										Set @ClientCodePrior = @ClientCode
										Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + '': '' + IsNull(@Name,'''') 
								End

							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, ''   '' + IsNull(@ReturnCode,'''') + '': '' +   RTrim(IsNull(@Title,'''')) + '' : '' +  
								Case When @FormType = ''I'' Then RTrim(IsNull(@GivenNames,'''')) + '' : '' +  RTrim(IsNull(@Surname,''''))
														  Else  RTrim(IsNull(@Surname,'''')) + '' : '' +  RTrim(IsNull(@GivenNames,'''')) End +
								Case When @IsLodged = ''Y'' then '' ** Lodged ** ''  else '''' End +
								Case When @IsDeletedOnRollover = ''Y'' then '' ** Excluded from Rollover ** ''  else '''' End
						
						  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode, @Name, @ReturnCode, @FormType, @Title, @GivenNames, @Surname, @IsLodged, @IsDeletedOnRollover
					   END
					CLOSE A_cursor
					DEALLOCATE A_cursor		'		
												
					--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, ''Number of Returns in 1 to Many Groups for Tax ' + @YearPriors + N' not in Current Year = '' + Ltrim(RTrim(STR(@Count))) '
					
---------------------------------
			
					--Set @SQL =  N' Insert Into zAEPMPlusLog (Summary, ErrMsg)
					--				Select 2, T.ClientCode  + '' : '' + T.ReturnCode  + '' : '' +
					--				Case When T.FormType = ''I'' Then RTrim(IsNull(T.GivenNames,'''')) + '' : '' +  RTrim(IsNull(T.Surname,''''))
					--				Else  RTrim(IsNull(T.Surname,'''')) + '' : '' +  RTrim(IsNull(T.GivenNames,'''')) End +
					--				Case When T.IsLodged = ''Y'' then '' ** Lodged ** ''  else '''' End +
					--				Case When T.IsDeletedOnRollover = ''Y'' then '' ** Excluded from Rollover ** ''  else '''' End
					--				From zTaxDetailsPrior' + @YearPriors + N' T
					--				Inner Join 
					--					(	
					--					select ClientCode, COUNT(*) as [Returns]
					--					FROM zTaxDetailsPrior' + @YearPriors + 
					--					N' WHERE BackupOfReturn IS NULL
					--					GROUP BY ClientCode 
					--					HAVING COUNT(*) > 1
					--					) as TM on T.ClientCode = TM.ClientCode

					--				Where T.BackupOfReturn IS NULL and  T.ReturnCode not in
					--				(
					--					Select T.ReturnCode
					--					From zTaxDetails T
					--					Inner Join 
					--						(	
					--						select ClientCode, COUNT(*) as [Returns]
					--						FROM zTaxDetails
					--						WHERE BackupOfReturn IS NULL
					--						GROUP BY ClientCode 
					--						HAVING COUNT(*) > 1
					--						) as TM on T.ClientCode = TM.ClientCode
					--						Where T.BackupOfReturn IS NULL 
					--				)	
					--				Order By T.ClientCode, T.ReturnCode
									
					--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, ''Number of Returns in 1 to Many Groups for Tax ' + @YearPriors + N' not in Current Year = '' + Ltrim(RTrim(STR(@@ROWCOUNT))) '

					--Print @SQL   

					

					exec sp_executesql @SQL



					Set @YearPriorsCount = @YearPriorsCount + 1
					set @YearPriors = Convert(varchar(4),Convert(int, @Year) - @YearPriorsCount)
					

					
				END;


		--	1:1 Clients with different return code
		
		set @OfficePrior = '***************'
		
		declare A_Cursor Cursor Static For 
			SELECT CDS.Office, T.ClientCode , RTrim(IsNull(CDS.[Name],'')), T.ReturnCode, T.[FormType],
					RTrim(IsNull(T.Title,'')), RTrim(Replace(IsNull(T.GivenNames,''), '  ', ' ')), RTrim(IsNull(T.Surname,''))
			  FROM  zTaxDetails T
				Inner JOIN 
				  (		-- Obtain the client groups which are  one-one
						SELECT ClientCode
						FROM
						(
							  -- Obtain the unique returns
							  SELECT ReturnCode, ClientCode
							  FROM zTaxDetails --zTaxDetails for Tax 2010, DS6TA011 for 2009, DS6TA911 for 2008
							  WHERE BackupOfReturn IS NULL 
						) LastAmendedReturn
						GROUP BY ClientCode
						HAVING COUNT(ReturnCode) = 1
				  ) ClientGroups on T.ClientCode = ClientGroups.ClientCode
			  Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
			Where T.ClientCode <> T.ReturnCode and T.BackupOfReturn IS NULL 
			Order By CDS.Office, T.ClientCode ,  T.ReturnCode

		open A_Cursor
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of 1 to 1 Clients with different return code = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Client    : Return    : Client Name   : Return Name  '

		Fetch next from A_Cursor into  @Office, @ClientCode, @Name, @ReturnCode, @FormType, @Title, @GivenNames, @Surname

		 WHILE @@FETCH_STATUS = 0
		   BEGIN
				If @Office <> @OfficePrior
					Begin
							Set @OfficePrior = @Office
							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
					End
					
				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ' : ' + @ReturnCode + ' : ' +  'Client: ' +   @Name + ' : Tax: ' +
						  RTrim(IsNull(@Title,'')) + ' : ' +  
					Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
											  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End

			  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode, @Name, @ReturnCode, @FormType, @Title, @GivenNames, @Surname
		   END
		CLOSE A_cursor
		DEALLOCATE A_cursor


		----	Return Code in 1:1 Clients with different return code that already existing in CDS

		--set @Count = 0
		--set @OfficePrior = '***************'
			
		--declare A_Cursor Cursor Static For 
		--	SELECT CDS.Office, T.ClientCode ,  T.ReturnCode
		--	  FROM  zTaxDetails T
		--		Inner JOIN 
		--		  (		-- Obtain the client groups which are  one-one
		--				SELECT ClientCode
		--				FROM
		--				(
		--					  -- Obtain the unique returns
		--					  SELECT ReturnCode, ClientCode
		--					  FROM zTaxDetails 
		--					  WHERE BackupOfReturn IS NULL 
		--				) LastAmendedReturn
		--				GROUP BY ClientCode
		--				HAVING COUNT(ReturnCode) = 1
		--		  ) ClientGroups on T.ClientCode = ClientGroups.ClientCode
		--	  Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
		--	Where T.ClientCode <> T.ReturnCode and T.BackupOfReturn IS NULL 
		--	Order By CDS.Office, T.ClientCode ,  T.ReturnCode

		--open A_Cursor
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return Codes in 1:1 Clients with different return code that already exist in CDS ' 

		--Fetch next from A_Cursor into  @Office, @ClientCode, @ReturnCode

		-- WHILE @@FETCH_STATUS = 0
		--   BEGIN
		--		If @Office <> @OfficePrior
		--			Begin
		--					Set @OfficePrior = @Office
		--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
		--			End
									
		--		declare B_Cursor Cursor Static For 
		--			select T.ReturnCode, T.[FormType],
		--			RTrim(IsNull(T.Title,'')), RTrim(Replace(IsNull(T.GivenNames,''), '  ', ' ')), RTrim(IsNull(T.Surname,'')),
		--			RTrim(C.[Name]) 
		--			FROM zTaxDetails T
		--			Inner Join zCDSDetails C on convert (varbinary(10), T.ReturnCode) =  convert (varbinary(10), C.[Code])
		--			Where T.ClientCode = @ClientCode and T.ReturnCode is not null and T.ReturnCode <> T.ClientCode
		--			Order By T.ReturnCode


		--		open B_Cursor
		--		If @@CURSOR_ROWS > 0
		--			begin
		--					Insert Into zAEPMPlusLog (Summary, ErrMsg) 
		--					Select  2, @ClientCode + ': ' + RTrim([Name]) 
		--					From zCDSDetails 
		--					Where convert (varbinary(10), [Code]) =  convert (varbinary(10), @ClientCode)
		--					and [GROUP] = 'C'
							
		--			End
				
				
		--		Fetch next from B_Cursor into  @ReturnCode,  @FormType, @Title, @GivenNames, @Surname, @Name

		--		 WHILE @@FETCH_STATUS = 0
		--		   BEGIN
				   
		--		   		set @Count = @Count + 1
	
		--				Insert Into zAEPMPlusLog (Summary, ErrMsg) 
		--				Select  2, '   ' + IsNull(@ReturnCode,'') + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
		--				Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
		--										  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End +
		--				' : CDS Client Name : ' + @Name

		--			  FETCH NEXT FROM B_Cursor into  @ReturnCode, @FormType,  @Title, @GivenNames, @Surname, @Name
		--		   END
		--		CLOSE B_cursor
		--		DEALLOCATE B_cursor		
					
		--	  FETCH NEXT FROM A_Cursor into @Office,  @ClientCode,  @ReturnCode
		--   END
		--CLOSE A_cursor
		--DEALLOCATE A_cursor
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Return Codes in 1:1 Clients with different return code that already exist in CDS = ' + Ltrim(RTrim(STR(@Count)))




		----	Return Code in 1:1 Clients with different return code that already existing in AE PM

		--set @Count = 0
		--set @OfficePrior = '***************'

		--declare A_Cursor Cursor Static For 
		--	SELECT CDS.Office, T.ClientCode ,  T.ReturnCode
		--	  FROM  zTaxDetails T
		--		Inner JOIN 
		--		  (		-- Obtain the client groups which are  one-one
		--				SELECT ClientCode
		--				FROM
		--				(
		--					  -- Obtain the unique returns
		--					  SELECT ReturnCode, ClientCode
		--					  FROM zTaxDetails 
		--					  WHERE BackupOfReturn IS NULL 
		--				) LastAmendedReturn
		--				GROUP BY ClientCode
		--				HAVING COUNT(ReturnCode) = 1
		--		  ) ClientGroups on T.ClientCode = ClientGroups.ClientCode
		--	  Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
		--	Where T.ClientCode <> T.ReturnCode and T.BackupOfReturn IS NULL 
		--	Order By CDS.Office, T.ClientCode ,  T.ReturnCode

		--open A_Cursor
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return Codes in 1:1 Clients with different return code that already exist in AE PM'

		--Fetch next from A_Cursor into  @Office, @ClientCode, @ReturnCode

		-- WHILE @@FETCH_STATUS = 0
		--   BEGIN
		--		If @Office <> @OfficePrior
		--			Begin
		--					Set @OfficePrior = @Office
		--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
		--			End	
										
		--		declare B_Cursor Cursor Static For 
		--			select T.ReturnCode, T.[FormType],
		--			RTrim(IsNull(T.Title,'')), RTrim(Replace(IsNull(T.GivenNames,''), '  ', ' ')), RTrim(IsNull(T.Surname,'')),
		--			LTrim(IsNull(C.Pref,'') + ' ') + LTrim(IsNull(C.FName,'') + ' ') + LTrim(IsNull(C.MName,'') + ' ') +  RTrim(C.LName) 
		--			FROM zTaxDetails T
		--			Inner Join ClientSupplier CS on  T.ReturnCode Collate Database_Default =   CS.ClientCode
		--			Inner Join Contact C on CS.ContactId = C.ContactId
		--			Where T.ClientCode = @ClientCode and T.ReturnCode is not null and T.ReturnCode <> T.ClientCode
		--			Order By T.ReturnCode


		--		open B_Cursor
				
		--		If @@CURSOR_ROWS > 0
		--			begin
		--					Insert Into zAEPMPlusLog (Summary, ErrMsg) 
		--					Select  2, @ClientCode + ': ' + RTrim([Name]) 
		--					From zCDSDetails 
		--					Where convert (varbinary(10), [Code]) =  convert (varbinary(10), @ClientCode)
		--					and [GROUP] = 'C'
							
		--			End
					
		--		Fetch next from B_Cursor into  @ReturnCode,  @FormType, @Title, @GivenNames, @Surname, @Name

		--		 WHILE @@FETCH_STATUS = 0
		--		   BEGIN
				   
		--		   		set @Count = @Count + 1
		
		--				Insert Into zAEPMPlusLog (Summary, ErrMsg) 
		--				Select  2, '   ' + IsNull(@ReturnCode,'') + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
		--				Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
		--										  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End +
		--				' : AE PM Client Name : ' + @Name

		--			  FETCH NEXT FROM B_Cursor into  @ReturnCode, @FormType,  @Title, @GivenNames, @Surname, @Name
		--		   END
		--		CLOSE B_cursor
		--		DEALLOCATE B_cursor		
					
		--	  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode,  @ReturnCode
		--   END
		--CLOSE A_cursor
		--DEALLOCATE A_cursor
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Return Codes in 1:1 Clients with different return code that already exist in AE PM = ' + Ltrim(RTrim(STR(@Count)))





		--	CDS Codes not in AE PM

		--declare A_Cursor Cursor Static For 
		--	select CDS.Code, CDS.Title, CDS.[Given Names], CDS.Surname
		--	--,CDS.* 
		--	--,C.Pref, C.FName, C.MName, C.LName

		--	FROM  zCDSDetails CDS
		--	Left Join ClientSupplier CS on CDS.Code COLLATE Latin1_General_CI_AS = CS.ClientCode
		--	Left Join Contact C on CS.ContactId = C.ContactId
		--	Where  CDS.[Date Terminated] is null and CDS.[Type] = 'C' and CDS.[Group] = 'C'
		--	and CS.ContactId is null
		--	Order By CDS.Code

		--open A_Cursor
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of CDS Codes not in AE PM = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Client    : Title : Given Names/Entity Name : Surname/Entity Name line 2'

		--Fetch next from A_Cursor into  @ClientCode,  @Title, @GivenNames, @Surname

		-- WHILE @@FETCH_STATUS = 0
		--   BEGIN

		--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))

		--	  FETCH NEXT FROM A_Cursor into  @ClientCode,  @Title, @GivenNames, @Surname
		--   END
		--CLOSE A_cursor
		--DEALLOCATE A_cursor



		--declare A_Cursor Cursor Static For 
		--	select CDS.Code, CDS.Title, CDS.[Given Names], CDS.Surname , AffectedReturns
		--	--,CDS.* 
		--	--,C.Pref, C.FName, C.MName, C.LName  

		--	FROM  zCDSDetails CDS
		--	Left Join ClientSupplier CS on RTrim(CDS.Code) COLLATE database_default = RTrim(CS.ClientCode)
		--	Left Join Contact C on CS.ContactId = C.ContactId
		--	left Join			  (

		--			-- Obtain the client groups which are not one-one
		--			SELECT ClientCode AS ClientGroup,  COUNT(ReturnCode) AS AffectedReturns
		--			FROM
		--			(
		--				  -- Obtain the unique returns
		--				  SELECT ReturnCode, ClientCode
		--				  FROM zTaxDetails 
		--				  WHERE BackupOfReturn IS NULL 
		--			) LastAmendedReturn
		--			GROUP BY ClientCode
		--			HAVING COUNT(ReturnCode) > 0
		--	  ) ClientGroups on convert (varbinary(10),CDS.Code) =  convert (varbinary(10),ClientGroups.ClientGroup)
			
			
		--	Where  CDS.[Date Terminated] is null and CDS.[Type] = 'C' and CDS.[Group] = 'C'
		--	and CS.ContactId is null
		--	Order By CDS.Code


		--open A_Cursor
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of CDS Codes not in AE PM = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
		--Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Client    : Title : Given Names/Entity Name : Surname/Entity Name line 2'

		--Fetch next from A_Cursor into  @ClientCode,  @Title, @GivenNames, @Surname,  @AffectedReturns

		-- WHILE @@FETCH_STATUS = 0
		--   BEGIN

		--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
		--				+ ' : ' + 
		--				Case When @AffectedReturns is null  Then '' else 
		--				 LTrim(RTrim(Str(@AffectedReturns))) + ' return(s) attached' end

				
		--		declare B_Cursor Cursor Static For 
		--			select T.ReturnCode, T.[FormType],
		--			RTrim(IsNull(T.Title,'')), RTrim(Replace(IsNull(T.GivenNames,''), '  ', ' ')), RTrim(IsNull(T.Surname,'')), T.IsLodged
		--			FROM zTaxDetails T
		--			Where T.ClientCode = @ClientCode and T.ReturnCode is not null
		--			Order By T.ReturnCode


		--		open B_Cursor
		--		Fetch next from B_Cursor into  @ReturnCode,  @FormType, @Title, @GivenNames, @Surname, @IsLodged

		--		 WHILE @@FETCH_STATUS = 0
		--		   BEGIN

		--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '   ' + IsNull(@ReturnCode,'') + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
		--			Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
		--									  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End +
		--			Case When @IsLodged = 'Y' then ' ** Lodged **'  else '' End
						

		--			  FETCH NEXT FROM B_Cursor into  @ReturnCode, @FormType,  @Title, @GivenNames, @Surname, @IsLodged
		--		   END
		--		CLOSE B_cursor
		--		DEALLOCATE B_cursor		
					
		--	  FETCH NEXT FROM A_Cursor into  @ClientCode,  @Title, @GivenNames, @Surname,  @AffectedReturns
		--   END
		--CLOSE A_cursor
		--DEALLOCATE A_cursor	


		--  Tax return name different from AE PM client name
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Tax returns with different name in AE PM'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return    : Tax : Title : Given Names/Entity Name : Surname/Entity Name line 2 :' + 
--				'AEPM : Title : First Name : Middle Name : Last Name/Entity Name'
--		set @Count = 0
--		declare A_Cursor Cursor Static For 

--			select CDS.Office, T.ReturnCode, T.[FormType], 
--			RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), C.Pref, C.FName, C.MName, C.LName
--			FROM zTaxDetails T
--			Inner Join ClientSupplier CS on T.ReturnCode  COLLATE Latin1_General_CI_AS  = CS.ClientCode
--			Inner Join Contact C on CS.ContactId = C.ContactId
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
--			Order By CDS.Office,  T.ReturnCode

--		open A_Cursor
--		Fetch next from A_Cursor into  @Office, @ClientCode,  @FormType, @Title, @GivenNames, @Surname,
--				@Pref, @FName, @MName, @LName

--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN


--			If @FormType = 'I'
--				Begin 
--					If CharIndex(' ', @GivenNames, 1) > 0
--						Begin
--							Set @Middle = Substring(@GivenNames, CharIndex(' ', @GivenNames, 1) + 1, Len(@GivenNames)-CharIndex(' ', @GivenNames, 1)) 
--							Set @First = Left(@GivenNames, CharIndex(' ', @GivenNames, 1) - 1)
--							set @Last = RTrim(@Surname)
--						End
--					Else
--						Begin
--							set @First = RTrim(@GivenNames)
--							Set @Middle = Null
--							Set @Last = RTrim(@Surname)
--						End;

--				End
--			Else
--				Begin
--					Set @Last = LTrim(RTrim(@Surname)) + ' ' + LTrim(RTrim(@GivenNames))
--					Set @First = NULL
--					Set @Middle = Null
--				End;



--			IF  @FName <> @First or
--				@MName <> @Middle or
--				@LName <> @Last
--				Begin
				
--					If @Office <> @OfficePrior
--						Begin
--								Set @OfficePrior = @Office
--								Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
						
--						End
						
--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': Tax : ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--						Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--												  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End
--						+ ' : AEPM: ' +  RTrim(IsNull(@Pref,'')) + ' : ' +  RTrim(IsNull(@FName,'')) + ' : ' +  RTrim(IsNull(@MName,'')) + ' : ' +  RTrim(IsNull(@LName,''))

--					set @Count = @Count + 1
--				End

--			  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode, @FormType,  @Title, @GivenNames, @Surname,
--				@Pref, @FName, @MName, @LName
--		   END
--		CLOSE A_cursor
--		DEALLOCATE A_cursor
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Tax returns with different AE PM client name = ' + Ltrim(RTrim(STR(@Count)))




--		--  Client Type in AE PM does not match Form Type in AE Tax
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Client Type extra in AE PM does not match Form Type in AE Tax'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return    : Title : Given Names/Entity Name : Surname/Entity Name line 2 : Form Type : FormType Value : AEPM Contact Type : ContactType Value'
--		set @Count = 0
--		declare A_Cursor Cursor Static For 

--			select CDS.Office, T.ReturnCode, T.[FormType], 
--			RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), CS.ContactId
--			FROM zTaxDetails T
--			Inner Join ClientSupplier CS on T.ReturnCode COLLATE Latin1_General_CI_AS  = CS.ClientCode
--			Inner Join Contact C on CS.ContactId = C.ContactId
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
--			Order By CDS.Office,  T.ReturnCode

--		open A_Cursor
--		Fetch next from A_Cursor into  @Office, @ClientCode,  @FormType, @Title, @GivenNames, @Surname, @ContactId


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN


--			Set @FormTypeAEPM = (Select EV.Value
--								From ExtraContact EC
--								Inner Join ExtraValue EV on EC.ExtraValueId = EV.ExtraValueId
--								Inner Join ExtraField EF on EV.ExtraFieldId = EF.ExtraFieldId
--								Where EC.ContactId = @ContactId and EF.FieldName = 'Client Type')



--			IF  @FormType <> Left(@FormTypeAEPM,1)
--				Begin
--					If @Office <> @OfficePrior
--						Begin
--								Set @OfficePrior = @Office
--								Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
						
--						End
						
--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--						Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--												  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End
--						+ ' : Form Type : ' + RTrim(@FormType)  + ' : AEPM Client Type : ' +  + RTrim(@FormTypeAEPM)

--					set @Count = @Count + 1
--				End

--			  FETCH NEXT FROM A_Cursor into @Office, @ClientCode,  @FormType, @Title, @GivenNames, @Surname, @ContactId
--		   END
--		CLOSE A_cursor
--		DEALLOCATE A_cursor
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Tax returns with different AE PM client type = ' + Ltrim(RTrim(STR(@Count)))




--		--  Contact Type in AE PM does not match Form Type in AE Tax
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Contact Type in AE PM does not match Form Type in AE Tax'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return    : Title : Given Names/Entity Name : Surname/Entity Name line 2 : Form Type : FormType Value : AEPM Contact Type : ContactType Value'

--		declare A_Cursor Cursor Static For 

--			select CDS.Office, T.ReturnCode, T.[FormType], 
--			RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), CT.ContactType
			
--			FROM zTaxDetails T
--			Inner Join ClientSupplier CS on T.ReturnCode COLLATE Latin1_General_CI_AS  = CS.ClientCode
--			Inner Join Contact C on CS.ContactId = C.ContactId
--			Inner Join ContactType CT on C.ContactTypeId = CT.ContactTypeId
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)

--			Where T.[FormType] = 'I' and C.ContactTypeId <> 1 or
--				  T.[FormType] <> 'I' and C.ContactTypeId = 1 
--			Order By CDS.Office,  T.ReturnCode 


--		open A_Cursor
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Fetch next from A_Cursor into  @Office, @ClientCode,  @FormType, @Title, @GivenNames, @Surname, @ContactType


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN

--					If @Office <> @OfficePrior
--						Begin
--								Set @OfficePrior = @Office
--								Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
						
--						End

--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--						Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--												  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End
--						+ ' : Form Type : ' + RTrim(@FormType) + ' : ' + ' : AEPM Contact Type : ' + @ContactType



--			  FETCH NEXT FROM A_Cursor into @Office,  @ClientCode,  @FormType, @Title, @GivenNames, @Surname, @ContactType
--		   END
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of clients in AE PM with Contact Type different from Form Type in AE Tax = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		CLOSE A_cursor
--		DEALLOCATE A_cursor



--		--	Clientcodes > 10 characters in AE PM

--		set @OfficePrior = '***************'		

--		declare A_Cursor Cursor Static For 
--				select O.Officename, CS.ClientCode, 
--				LTrim(IsNull(C.Pref,'') + ' ') + LTrim(IsNull(C.FName,'') + ' ') + LTrim(IsNull(C.MName,'') + ' ') +  RTrim(C.LName),
--				Case When CS.Closed Is Not null Then 'Closed' else '' end 
--				From ClientSupplier CS
--				left Join Contact C on CS.ContactId = C.ContactId
--				Left Join Office O on CS.OfficeId = O.OfficeId
--				Where LEN(CS.ClientCode) > 10 and CS.ClientSupplierType = 1
--				Order By O.Officename, CS.ClientCode

--		open A_Cursor
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Client Codes > 10 characters in AE PM = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'AE PM Client    : Name'

--		Fetch next from A_Cursor into @Office, @ClientCode, @Name, @Status

--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
		  
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End

--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' + IsNull(@Name,'') + ' : ' + @Status
	
	
--				declare B_Cursor Cursor Static For
--						Select [Code], RTrim([Name]) , Case When [Date Terminated] Is Not null Then 'Closed' else '' end 
--							From zCDSDetails 
--							Where convert (varbinary(10), [Code]) =  convert (varbinary(10), Left(@ClientCode,10))
--							and [GROUP] = 'C'
							
--				open B_Cursor
--				Fetch next from B_Cursor into  @CDSCode,  @CDSName, @CDS_Status

--				 WHILE @@FETCH_STATUS = 0
--				   BEGIN						
							
--						Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '    CDS : ' + @CDSCode + ': ' + IsNull(@CDSName,'') + ' : ' + @CDS_Status
	

				
--						declare C_Cursor Cursor Static For 
--							select T.ReturnCode, T.[FormType],
--							RTrim(IsNull(T.Title,'')), RTrim(Replace(IsNull(T.GivenNames,''), '  ', ' ')), RTrim(IsNull(T.Surname,''))
--							FROM zTaxDetails T
--							Where T.ClientCode = @CDSCode and T.ReturnCode is not null
--							Order By T.ReturnCode


--						open C_Cursor
--						Fetch next from C_Cursor into  @ReturnCode,  @FormType, @Title, @GivenNames, @Surname

--						 WHILE @@FETCH_STATUS = 0
--						   BEGIN

--								Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '        Tax : ' + IsNull(@ReturnCode,'') + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--							Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--													  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End
													  
											  
--								  FETCH NEXT FROM C_Cursor into  @ReturnCode, @FormType,  @Title, @GivenNames, @Surname
--							   END
--							CLOSE C_cursor
--							DEALLOCATE C_cursor	

--					  FETCH NEXT FROM B_Cursor into  @CDSCode,  @CDSName, @CDS_Status
--				   END
--				CLOSE B_cursor
--				DEALLOCATE B_cursor		
					
--			  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode, @Name, @Status
--		   END
--		CLOSE A_cursor
--		DEALLOCATE A_cursor
		
----		second test for AE PM Client Codes > 10 characters based on Tax return view		
		
--		If Exists (
--						select O.Officename, CS.ClientCode, 
--						LTrim(IsNull(C.Pref,'') + ' ') + LTrim(IsNull(C.FName,'') + ' ') + LTrim(IsNull(C.MName,'') + ' ') +  RTrim(C.LName),
--						Case When CS.Closed Is Not null Then 'Closed' else '' end 
--						From ClientSupplier CS
--						left Join Contact C on CS.ContactId = C.ContactId
--						Left Join Office O on CS.OfficeId = O.OfficeId
--						Where LEN(CS.ClientCode) > 10 and CS.ClientSupplierType = 1
--					)
--			Begin

--					set @OfficePrior = '***************'		

--					declare A_Cursor Cursor Static For 
--							select O.Officename, 
--							T.ReturnCode, T.[FormType],
--							RTrim(IsNull(T.Title,'')), RTrim(Replace(IsNull(T.GivenNames,''), '  ', ' ')), RTrim(IsNull(T.Surname,'')),
--							T.ClientCode	
							 
--							From ClientSupplier CS
--							left Join Contact C on CS.ContactId = C.ContactId
--							Left Join Office O on CS.OfficeId = O.OfficeId
--							inner Join zTaxDetails T on Left(CS.ClientCode,10) collate database_default = T.ReturnCode
--							Where LEN(CS.ClientCode) > 10 and CS.ClientSupplierType = 1
--							Order By O.Officename, CS.ClientCode

--					open A_Cursor
--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Tax Returns that are linked to Client Codes > 10 characters in AE PM = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return    : Return Name'

--					Fetch next from A_Cursor into @Office, @ReturnCode,  @FormType, @Title, @GivenNames, @Surname, @CDSCode

--					 WHILE @@FETCH_STATUS = 0
--					   BEGIN
					  
--							If @Office <> @OfficePrior
--								Begin
--										Set @OfficePrior = @Office
--										Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
								
--								End
								
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  IsNull(@ReturnCode,'') + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--									Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--															  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End

				
				
--							declare B_Cursor Cursor Static For
--									Select  RTrim([Name]) , Case When [Date Terminated] Is Not null Then 'Closed' else '' end 
--										From zCDSDetails 
--										Where convert (varbinary(10), [Code]) =  convert (varbinary(10), Left(@CDSCode,10))
--										and [GROUP] = 'C'
										
--							open B_Cursor
--							Fetch next from B_Cursor into  @CDSName, @CDS_Status

--							 WHILE @@FETCH_STATUS = 0
--							   BEGIN						
										
--									Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '    CDS : ' + @CDSCode + ': ' + IsNull(@CDSName,'') + ' : ' + @CDS_Status
				
						
--									declare C_Cursor Cursor Static For 
--										select  
--										CS.ClientCode,  
--										LTrim(IsNull(C.Pref,'') + ' ') + LTrim(IsNull(C.FName,'') + ' ') + LTrim(IsNull(C.MName,'') + ' ') +  RTrim(C.LName),
--										Case When CS.Closed Is Not null Then 'Closed' else '' end
									
--										From ClientSupplier CS
--										Inner Join Contact C on CS.ContactId = C.ContactId
--										Where Left(CS.ClientCode,10) = @ReturnCode and CS.ClientSupplierType = 1
--										Order By CS.ClientCode


--									open C_Cursor
--									Fetch next from C_Cursor into  @ClientCode,   @Name, @Status

--									 WHILE @@FETCH_STATUS = 0
--									   BEGIN

--										Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '        AE PM: ' + @ClientCode + ': ' + IsNull(@Name,'') + ' : ' + @Status
																  
														  
--											  FETCH NEXT FROM C_Cursor into  @ClientCode,   @Name, @Status
--										   END
--										CLOSE C_cursor
--										DEALLOCATE C_cursor	

--								  FETCH NEXT FROM B_Cursor into  @CDSName, @CDS_Status
--							   END
--							CLOSE B_cursor
--							DEALLOCATE B_cursor		
								
--						  FETCH NEXT FROM A_Cursor into  @Office, @ReturnCode,  @FormType, @Title, @GivenNames, @Surname, @CDSCode
--					   END
--					CLOSE A_cursor
--					DEALLOCATE A_cursor
		
--			End

--	--	AE PM : Invalid characters in ClientCode

--		set @OfficePrior = '***************'		

--		declare A_Cursor Cursor Static For 
--				select O.Officename, CS.ClientCode, 
--				LTrim(IsNull(C.Pref,'') + ' ') + LTrim(IsNull(C.FName,'') + ' ') + LTrim(IsNull(C.MName,'') + ' ') +  RTrim(C.LName),
--				Case When CS.Closed Is Not null Then 'Closed' else '' end 
--				From ClientSupplier CS
--				left Join Contact C on CS.ContactId = C.ContactId
--				Left Join Office O on CS.OfficeId = O.OfficeId
--				Where (
--						CS.ClientCode like '%[%''_[\,.^/<>~+-{}#;:|?=*"&()]%' 
--						or not (CS.ClientCode = UPPER(CS.ClientCode) Collate Latin1_General_CS_AI )
--						)
--				and CS.ClientSupplierType = 1
--				Order By O.Officename, CS.ClientCode
				


--		open A_Cursor
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Client Codes with invalid characters in AE PM = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'AE PM Client    : Name'

--		Fetch next from A_Cursor into @Office, @ClientCode, @Name, @Status

--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
		  
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End

--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' + IsNull(@Name,'') + ' : ' + @Status
	
					
--			  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode, @Name, @Status
--		   END
--		CLOSE A_cursor
--		DEALLOCATE A_cursor

--	--	CDS : Invalid characters in ClientCode

--		set @OfficePrior = '***************'		

--		declare A_Cursor Cursor Static For 
--			select Office, Code , Name, Case When [Date Terminated] Is Not null Then 'Closed' else '' end
--			from zCDSDetails
--			Where Code like '%[%''_[\,.^/<>~+-{}#;:|?=*"()]%'
--				or not (Code = UPPER(Code) Collate Latin1_General_CS_AI )

--			Union

--			select Office, Code , Name, Case When [Date Terminated] Is Not null Then 'Closed' else '' end
--			from zCDSDetails
--			Where 
--			CHARINDEX(' ', RTrim(Code)) > 1
				


--		open A_Cursor
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Client Codes with invalid characters in CDS = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'CDS Code  : Name'

--		Fetch next from A_Cursor into @Office, @ClientCode, @Name, @Status

--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
		  
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End

--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' + IsNull(@Name,'') + ' : ' + @Status
	
					
--			  FETCH NEXT FROM A_Cursor into  @Office, @ClientCode, @Name, @Status
--		   END
--		CLOSE A_cursor
--		DEALLOCATE A_cursor
		

--	--	Tax : Invalid characters in Return Codes

--		set @OfficePrior = '***************'		

--		declare A_Cursor Cursor Static For 
--			select CDS.Office,  T.ReturnCode, 
--			Case When T.FormType = 'I' Then RTrim(IsNull(T.GivenNames,'')) + ' ' +  RTrim(IsNull(T.Surname,''))
--										Else  RTrim(IsNull(T.Surname,'')) + ' ' +  RTrim(IsNull(T.GivenNames,'')) End as ReturnName
--			from zTaxDetails T
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
--			Where 
--					T.ReturnCode like '%[%''_[\,.^/<>~+-{}#;:|?=*"&()]%'
--			or not (T.ReturnCode = UPPER(T.ReturnCode) Collate Latin1_General_CS_AI )


--			Union

--			select CDS.Office, T.ReturnCode, 
--			Case When T.FormType = 'I' Then RTrim(IsNull(T.GivenNames,'')) + ' ' +  RTrim(IsNull(T.Surname,''))
--										Else  RTrim(IsNull(T.Surname,'')) + ' ' +  RTrim(IsNull(T.GivenNames,'')) End as ReturnName
--			from zTaxDetails T
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
--			Where 
--			CHARINDEX(' ', RTrim(ReturnCode)) > 1

--			Order By  CDS.Office, T.ReturnCode
				


--		open A_Cursor
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Return Codes with invalid characters = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return  : Name'

--		Fetch next from A_Cursor into @Office, @ReturnCode, @Name

--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
		  
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End

--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ReturnCode + ': ' + IsNull(@Name,'') 
	
					
--			  FETCH NEXT FROM A_Cursor into  @Office, @ReturnCode, @Name
--		   END
--		CLOSE A_cursor
--		DEALLOCATE A_cursor
		

----  Completed but not lodged tax forms in AE Tax
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Completed but not lodged tax forms in AE Tax'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Return    : Form Type : Date Completed : Form Id : Title : Given Names/Entity Name : Surname/Entity Name line 2 '

--		declare A_Cursor Cursor Static For 

--			select CDS.Office,  T.ReturnCode, T.[FormType], T.[Year],
--			RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), 
--			T.DateCompleted, T.FormId
--			FROM  zLodgmentHistory T
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ReturnCode) = convert (varbinary(10),CDS.Code)
--			Order By CDS.Office, T.[Year], T.ReturnCode



--		open A_Cursor
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Fetch next from A_Cursor into  @Office, @ClientCode,  @FormType, @TaxYear, @Title, @GivenNames, @Surname, @DateCompleted, @FormId


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
--					If @Office <> @OfficePrior
--						Begin
--								Set @OfficePrior = @Office
--								Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
						
--						End
						
--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @ClientCode + ': ' +   RTrim(@FormType) + 
--						+ ' : ' + @TaxYear + ' : ' + @DateCompleted + ' : ' + @FormId  + ' : ' +  
--						RTrim(IsNull(@Title,'')) + ' : ' +  
--						Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--												  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End
						 



--			  FETCH NEXT FROM A_Cursor into @Office, @ClientCode,  @FormType, @TaxYear, @Title, @GivenNames, @Surname, @DateCompleted, @FormId
--		   END
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Completed but not lodged tax forms in AE Tax = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		CLOSE A_cursor
--		DEALLOCATE A_cursor

----	Phone Number Types = TFN, ABN or ACN
		
--		set @OfficePrior = '***************'
		
--		declare A_Cursor Cursor Static For 
--			select PNT.PhoneNumDesc, COUNT(*)
--			From PhoneNumType PNT
--			Left Join ContactPhone CP on PNT.PhoneNumTypeId = CP.PhoneNumTypeId
--			Where PNT.PhoneNumDesc in ('TFN', 'ABN', 'ACN')
--			Group By PNT.PhoneNumDesc

--		open A_Cursor
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Phone Number Types = TFN, ABN or ACN = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Phone Number Type    : Used    '

--		Fetch next from A_Cursor into  @PhoneNumDesc, @Count

--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
					
--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, @PhoneNumDesc + ' : ' + Ltrim(RTrim(STR(@Count)))  


--			  FETCH NEXT FROM A_Cursor into  @PhoneNumDesc, @Count
--		   END
--		CLOSE A_cursor
--		DEALLOCATE A_cursor

--		--  Responsibility Types

--		--	Check for Unmatched Responsibility Types in Tax and AE PM 

--		declare A_Cursor Cursor Static For 

--			select ResponsibilityType_Tax
--			FROM #ResponsibilityTypes 
--			Where ResponsibilityTypeId is null

--		open A_Cursor
		
--		IF @@CURSOR_ROWS > 0
--			Begin
--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, '********************************************************'
--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Unmatched Responsibility Types in Tax and AE PM '
--					Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Tax Responsibility Type : AE PM Responsibility Type '
--					Fetch next from A_Cursor into  @ResponsibilityType

--					 WHILE @@FETCH_STATUS = 0
--					   BEGIN

--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, @ResponsibilityType 

--						  FETCH NEXT FROM A_Cursor into @ResponsibilityType
--					   END
--				End
--		CLOSE A_cursor
--		DEALLOCATE A_cursor

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Responsibility Types in Tax and AE PM used for matching'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Tax Responsibility Type : AE PM Responsibility Type : AE PM Responsibility Type Id'
--		declare A_Cursor Cursor Static For 

--			select ResponsibilityType_Tax, IsNull(ResponsibilityType_AEPM,''), IsNull(ResponsibilityTypeId,'')
--			FROM #ResponsibilityTypes 
--			Where ResponsibilityTypeId is not null
			

--		open A_Cursor
--		Fetch next from A_Cursor into  @ResponsibilityType, @ResponsibilityType_AEPM, @ResponsibilityTypeId


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN

--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1,  @ResponsibilityType + ' : ' + @ResponsibilityType_AEPM  + ' : ' +  LTrim(RTrim(Str(@ResponsibilityTypeId)))


--			  FETCH NEXT FROM A_Cursor into  @ResponsibilityType, @ResponsibilityType_AEPM, @ResponsibilityTypeId
--		   END

--		CLOSE A_cursor
--		DEALLOCATE A_cursor



--		--  Returns with an invalid Partner 
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Returns with an invalid Partner '

--		declare A_Cursor Cursor Static For 

--			select CDS.Office, T.ReturnCode, T.[FormType], RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), T.Partner
--			FROM zTaxDetails T
--			Left Join Employee E on T.Partner COLLATE Latin1_General_CI_AS  = E.EmployeeCode
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
			
--			WHERE BackupOfReturn IS NULL and 
--				T.Partner is not null and T.Partner <> '' and 
--				E.EmployeeId is null
				
--			Order By CDS.Office,  T.ReturnCode


--		open A_Cursor
--		Fetch next from A_Cursor into  @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @EmployeeCode


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
		   
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End
					
--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Partner : ' + RTrim(@EmployeeCode) + ' : Return :' +   @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--					Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--											  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End
					


--			  FETCH NEXT FROM A_Cursor into @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @EmployeeCode
--		   END
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Returns with an invalid Partner = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		CLOSE A_cursor
--		DEALLOCATE A_cursor



--		--  Returns with an invalid Manager 
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Returns with an invalid Manager '

--		declare A_Cursor Cursor Static For 

--			select CDS.Office,  T.ReturnCode, T.[FormType], RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), T.Manager
--			FROM zTaxDetails T
--			Left Join Employee E on T.Manager COLLATE Latin1_General_CI_AS  = E.EmployeeCode
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
--			WHERE BackupOfReturn IS NULL and 
--				T.Manager is not null and T.Manager <> '' and 
--				E.EmployeeId is null
--			Order By CDS.Office,  T.ReturnCode


--		open A_Cursor
--		Fetch next from A_Cursor into  @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @EmployeeCode


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End
					
--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Manager : ' + RTrim(@EmployeeCode) + ' : Return :' +  @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--					Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--											  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End


--			  FETCH NEXT FROM A_Cursor into @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @EmployeeCode
--		   END
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Returns with an invalid Manager = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		CLOSE A_cursor
--		DEALLOCATE A_cursor



--		--  Returns with an invalid Employee 
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Returns with an invalid Employee '

--		declare A_Cursor Cursor Static For 

--			select CDS.Office, T.ReturnCode, T.[FormType], RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), T.Employee
--			FROM zTaxDetails T
--			Left Join Employee E on T.Employee COLLATE Latin1_General_CI_AS  = E.EmployeeCode
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
--			WHERE BackupOfReturn IS NULL and 
--				T.Employee is not null and T.Employee <> '' and 
--				E.EmployeeId is null
--			Order By CDS.Office,  T.ReturnCode


--		open A_Cursor
--		Fetch next from A_Cursor into  @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @EmployeeCode


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End
					
--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Employee : ' + RTrim(@EmployeeCode) + ' : Return :' + @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--					Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--											  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End


--			  FETCH NEXT FROM A_Cursor into @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @EmployeeCode
--		   END
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Returns with an invalid Employee = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		CLOSE A_cursor
--		DEALLOCATE A_cursor

--		--  Returns with a different Partner to AE PM
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Returns with a different Partner to AE PM '

--		declare A_Cursor Cursor Static For 

--			select CDS.Office, T.ReturnCode, T.[FormType], RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), T.Partner,
--			E.EmployeeCode
--			FROM zTaxDetails T
--			Inner Join ClientSupplier CS on T.ReturnCode COLLATE Database_Default  = CS.ClientCode
--			Inner Join CurrentClientPartner CCP on CS.ClientId = CCP.ClientId
--			Inner Join Employee E on CCP.EmployeeId  = E.EmployeeId
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
--			WHERE BackupOfReturn IS NULL 
--			and T.Partner COLLATE Database_Default  <> E.EmployeeCode
--			Order By CDS.Office,  T.ReturnCode

--		open A_Cursor
--		Fetch next from A_Cursor into  @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @TaxEmployee, @EmployeeCode


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End
					
--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Partner : Tax : '  + RTrim(@TaxEmployee) +  ' : AEPM : ' + RTrim(@EmployeeCode) + ' : Return :' + @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--					Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--											  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End


--			  FETCH NEXT FROM A_Cursor into @Office,  @ClientCode, @FormType, @Title, @GivenNames, @Surname, @TaxEmployee, @EmployeeCode
--		   END
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Returns with a different Partner to AE PM = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		CLOSE A_cursor
--		DEALLOCATE A_cursor


--		--  Returns with a different Manager to AE PM
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Returns with a different Manager to AE PM '

--		declare A_Cursor Cursor Static For 

--			select CDS.Office, T.ReturnCode, T.[FormType], RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), T.Manager,
--			E.EmployeeCode
--			FROM zTaxDetails T
--			Inner Join ClientSupplier CS on T.ReturnCode COLLATE Database_Default  = CS.ClientCode
--			Inner Join ContactTeam CT on CS.ContactID = CT.ContactId
--			Inner Join ResponsibilityType R on CT.ResponsibilityTypeID = R.ResponsibilityTypeID
--			Inner join #ResponsibilityTypes RT on R.[Description] COLLATE Database_Default  = RT.ResponsibilityType_AEPM
--			Inner Join Employee E on CT.EmployeeId  = E.EmployeeId
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
--			WHERE BackupOfReturn IS NULL and RT.ResponsibilityType_Tax = 'Manager'
--			and T.Manager COLLATE Database_Default  <> E.EmployeeCode
--			Order By CDS.Office,  T.ReturnCode
			

--		open A_Cursor
--		Fetch next from A_Cursor into  @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @TaxEmployee, @EmployeeCode


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End
					
--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Manager : Tax : '  + RTrim(@TaxEmployee) +  ' : AEPM : ' + RTrim(@EmployeeCode) + ' : Return :' + @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--					Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--											  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End


--			  FETCH NEXT FROM A_Cursor into @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @TaxEmployee, @EmployeeCode
--		   END
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Returns with a different Manager to AE PM = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		CLOSE A_cursor
--		DEALLOCATE A_cursor


--		--  Returns with a different Employee to AE PM
		
--		set @OfficePrior = '***************'

--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, '********************************************************'
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Returns with a different Employee to AE PM '

--		declare A_Cursor Cursor Static For 

--			select CDS.Office, T.ReturnCode, T.[FormType], RTrim(T.Title), RTrim(Replace(T.GivenNames, '  ', ' ')), RTrim(T.Surname), T.Employee,
--			E.EmployeeCode
--			FROM zTaxDetails T
--			Inner Join ClientSupplier CS on T.ReturnCode COLLATE Database_Default  = CS.ClientCode
--			Inner Join ContactTeam CT on CS.ContactID = CT.ContactId
--			Inner Join ResponsibilityType R on CT.ResponsibilityTypeID = R.ResponsibilityTypeID
--			Inner join #ResponsibilityTypes RT on R.[Description] COLLATE Database_Default  = RT.ResponsibilityType_AEPM
--			Inner Join Employee E on CT.EmployeeId  = E.EmployeeId
--			Left Join zCDSDetails CDS on convert (varbinary(10),T.ClientCode) = convert (varbinary(10),CDS.Code)
--			WHERE BackupOfReturn IS NULL and RT.ResponsibilityType_Tax = 'Employee'
--			and T.Employee COLLATE Database_Default  <> E.EmployeeCode
--			Order By CDS.Office,  T.ReturnCode


--		open A_Cursor
--		Fetch next from A_Cursor into  @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @TaxEmployee, @EmployeeCode


--		 WHILE @@FETCH_STATUS = 0
--		   BEGIN
--				If @Office <> @OfficePrior
--					Begin
--							Set @OfficePrior = @Office
--							Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2,  'Office : ' + @Office
					
--					End
					
--				Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  2, 'Employee : Tax : '  + RTrim(@TaxEmployee) +  ' : AEPM : ' + RTrim(@EmployeeCode) + ' : Return :' + @ClientCode + ': ' +   RTrim(IsNull(@Title,'')) + ' : ' +  
--					Case When @FormType = 'I' Then RTrim(IsNull(@GivenNames,'')) + ' : ' +  RTrim(IsNull(@Surname,''))
--											  Else  RTrim(IsNull(@Surname,'')) + ' : ' +  RTrim(IsNull(@GivenNames,'')) End


--			  FETCH NEXT FROM A_Cursor into @Office, @ClientCode, @FormType, @Title, @GivenNames, @Surname, @TaxEmployee, @EmployeeCode
--		   END
--		Insert Into zAEPMPlusLog (Summary, ErrMsg) Select  1, 'Number of Returns with a different Employee to AE PM = ' + Ltrim(RTrim(STR(@@CURSOR_ROWS)))
--		CLOSE A_cursor
--		DEALLOCATE A_cursor

End

--	Print log file summary

	declare A_Cursor Cursor Static For 

		select Errmsg
		FROM zAEPMPlusLog 
		Where Summary < 2
		Order by LogId
		

	open A_Cursor
	Fetch next from A_Cursor into  @ErrMsg


	 WHILE @@FETCH_STATUS = 0
	   BEGIN

			Print @ErrMsg


		  FETCH NEXT FROM A_Cursor into @ErrMsg
	   END

	CLOSE A_cursor
	DEALLOCATE A_cursor

--	Print log file details

	Print '********************************************************'

	declare A_Cursor Cursor Static For 

		select Errmsg
		FROM zAEPMPlusLog
				Where Summary between 1 and 8 
		Order by LogId
		

	open A_Cursor
	Fetch next from A_Cursor into  @ErrMsg


	 WHILE @@FETCH_STATUS = 0
	   BEGIN

			Print @ErrMsg


		  FETCH NEXT FROM A_Cursor into @ErrMsg
	   END

	CLOSE A_cursor
	DEALLOCATE A_cursor




IF @DeleteTempFiles = 1
	Begin
		declare A_Cursor Cursor Static For 
			Select [Name] From dbo.sysobjects 
			Where [Name] like N'zTaxDetails%' 
			and OBJECTPROPERTY(id, N'IsTable') = 1
			
		open A_Cursor
			Fetch next from A_Cursor into @TableToDelete

			 WHILE @@FETCH_STATUS = 0
			   BEGIN
					Set @SQL = N'Drop Table ' + @TableToDelete 

					exec sp_executesql @SQL
					
				  FETCH NEXT FROM A_Cursor into  @TableToDelete
			   END
				   
		CLOSE A_cursor
		DEALLOCATE A_cursor

		IF exists (Select * From dbo.sysobjects Where id = object_id(N'zTaxAddresses') and OBJECTPROPERTY(id, N'IsTable') = 1)
			Drop Table zTaxAddresses

		IF exists (Select * From dbo.sysobjects Where id = object_id(N'zCDSDetails') and OBJECTPROPERTY(id, N'IsTable') = 1)
			Drop Table zCDSDetails

		IF exists (Select * From dbo.sysobjects Where id = object_id(N'xCDSDetails') and OBJECTPROPERTY(id, N'IsTable') = 1)
			Drop Table xCDSDetails
			
		IF exists (Select * From dbo.sysobjects Where id = object_id(N'zLodgmentHistory') and OBJECTPROPERTY(id, N'IsTable') = 1)
			Drop Table zLodgmentHistory

		IF exists (Select * From dbo.sysobjects Where id = object_id(N'zAEPMPlusLog') and OBJECTPROPERTY(id, N'IsTable') = 1)
			Drop Table zAEPMPlusLog
			
	End
 GO