select
	AccountNo as CardIdentification 
		,Name as LastName 
		,'' as FirstName 
		,DB_Main.Address1 as AddressLine1
		,DB_Main.Address2 as AddressLine2 
		,'' as AddressLine3 
		,'' as AddressLine4
		,DB_Main.Address3 as AddressCity 
		,Address4 as AddressState 
		,DB_Main.PostCode as AddressPostcode 
 		,'' as AddressCountry 
		,DB_Main.Phone as AddressPhone 
		,DB_Main.Fax as AddressFax 
		,DB_Main.EmailAddress as AddressEmail 
		,'' as AddressContactName 
	    ,isnull(HR_Staff.StaffID,'ZZZZZZZZZZ0000000000') as SalesPersonID
		,isnull(HR_Staff.Username,'Admin') as SalesPersonFirstName
FROM DB_Main
	left join 
	CN_Main 
		on DB_Main.DebtorID = CN_Main.DebtorID
		left join 
	CN_GroupLink 
		on CN_Main.ProspectId = CN_GroupLink.ProspectId
		and CN_GroupLink.DefaultItem = 1
		left join 
	HR_Staff 
		on CN_GroupLink.StaffID = HR_Staff.StaffID
WHERE AccountNo='{CustomerCode}'
--WHERE AltAccountNo='{CustomerCode}'

--use this one if using custom fields
--select
--*
--FROM DB_Main
--INNER JOIN DB_CustomSettingValues ON DB_Main.DebtorID = DB_CustomSettingValues.DebtorID
--INNER JOIN DB_CustomSetting ON DB_CustomSetting.SettingID = DB_CustomSettingValues.SettingID 
--INNER JOIN SY_Branch ON SY_Branch.BranchID = DB_CustomSettingValues.Contents
--WHERE AccountNo='{CustomerCode}'