How to solve Msg 4104 for SQl server in a Stored proc : I am  modifying a stored proc which is joining  some tables and giving the output.I want to include a new table that will perform a join operation and with those existing table.And  while doing that i am  getting an Msg  4104 while including that condition. I saw that it is for an alias problem which is in the select statement,but i am new to Sp can not figure out how it is occurring.I am posting my code please someone help.This is portion of the code which i am developing 


         CREATE TABLE #es_ediconsolidate 
    ( 
       [txtype]          [CHAR](2) NULL, 
       [txdesc]          [VARCHAR](40) NULL, 
       [txrefno]         [CHAR](25) NULL, 
       [clntcode]        [CHAR](5) NULL, 
       [polno]           [CHAR](10) NULL, 
       [certno]          [CHAR](10) NULL, 
       [depcode]         [DECIMAL](5, 0) NULL, 
       [deptype]         [CHAR](1) NULL, 
       [namefirst]       [CHAR](50) NULL, 
       [namelast]        [CHAR](50) NULL, 
       [namemid]         [CHAR](2) NULL, 
       [inieffdt]        [DATETIME] NULL, 
       [status]          [CHAR](1) NULL, 
       [chgeffdt]        [DATETIME] NULL, 
       [suboffcode]      [CHAR](3) NULL, 
       [departcd]        [CHAR](6) NULL, 
       [memidno]         [CHAR](12) NULL, 
       [dob]             [DATETIME] NULL, 
       [sex]             [CHAR](1) NULL, 
       [marital]         [CHAR](1) NULL, 
       [occupation]      [CHAR](20) NULL, 
       [empno]           [CHAR](10) NULL, 
       [empdt]           [DATETIME] NULL, 
       [salary]          [DECIMAL](13, 2) NULL, 
       [bankac]          [CHAR](40) NULL, 
       [memnation]       [CHAR](3) NULL, 
       [effdate]         [DATETIME] NULL, 
       [bankcode]        [CHAR](4) NULL, 
       [salarymode]      [CHAR](1) NULL, 
       [bankactype]      [CHAR](1) NULL, 
       [bankaceffdate]   [DATETIME] NULL, 
       [bankacstatus]    [CHAR](1) NULL, 
       [firstcovdt]      [DATETIME] NULL, 
       [maxageexemptind] [CHAR](1) NULL, 
       [vipstatus]       [CHAR](1) NULL, 
       [sponsoridno]     [VARCHAR](20) NULL, 
       [passportno]      [CHAR](15) NULL, 
       [nationalidno]    [CHAR](30) NULL, 
       [bankname]        [CHAR](40) NULL, 
       [branchname]      [CHAR](40) NULL, 
       [branchaddress]   [CHAR](40) NULL, 
       [bankcurrency]    [CHAR](3) NULL, 
       [swiftcode]       [CHAR](15) NULL, 
       [servicedeskid]   [CHAR](10) NULL, 
       [servicedeskdate] [DATETIME] NULL, 
       [dmpindicator]    [CHAR](1) NULL, 
       [salarychgeffdt]  [DATETIME] NULL, 
       [studentind]      [CHAR](1) NULL, 
       [outcntyind]      [CHAR](1) NULL, 
       [prodcode]        [CHAR](5) NULL, 
       [benplncd]        [CHAR](3) NULL, 
       [covgcode]        [CHAR](5) NULL, 
       [proposedsa]      [DECIMAL](11, 0) NULL, 
       [prdstatus]       [CHAR](1) NULL, 
       [benid]           [CHAR](20) NULL, 
       [benname]         [CHAR](40) NULL, 
       [benrelat]        [CHAR](15) NULL, 
       [beneffdt]        [DATETIME] NULL, 
       [benshare]        [DECIMAL](5, 4) NULL, 
       [remark]          [CHAR](16) NULL, 
       [rcdsts]          [CHAR](1) NULL, 
       [rcdusrid]        [CHAR](20) NULL, 
       [rcddtstmp]       [DATETIME] NULL, 
       [txstatus]        [CHAR](1) NULL, 
       [uploadusrid]     [VARCHAR](20) NULL, 
       [uploadtime]      [DATETIME] NULL, 
       [errorcode]       [CHAR](6) NULL, 
       [errormsg]        [VARCHAR](100) NULL, 
       [isprocessing]    [CHAR](1) NULL, 
       [parentclient]    [VARCHAR](40) NULL, 
       [agentname]       [VARCHAR](101) NULL, 
       [packagecode]     [CHAR](35) NULL, 
       [province]        [CHAR](20) NULL, 
       [cardprinteddate] [DATETIME] NULL, 
       [processedtime]   [DATETIME] NULL, 
       [adminuserid]     [VARCHAR](20) NULL, 
       [email]           [VARCHAR](60) NULL, 
       [mobileno]        [VARCHAR](20) NULL, 	   
	   [city]           [VARCHAR] (28),
       [iqamaExpDate]    [DATETIME] NULL,
	   [terminationReason] [VARCHAR] (1),
	   [LifeVestInd] [CHAR](1),
	   [LSBIndicator] [CHAR](1),
	   [UIDnumber] [VARCHAR] (20),
	   [VisaPlaceOfIssue] [VARCHAR] (50),
       [eligdays]        [DECIMAL](3, 0) NULL, 
       [certtype]        [VARCHAR](50) NULL,
	   [BENIDTYPE]		[VARCHAR](30) NULL, 
	   [BENADDR1]		[VARCHAR](30) NULL, 
	   [BENADDR2]		[VARCHAR](30) NULL, 
	   [BENADDR3]		[VARCHAR](30) NULL, 
	   [BENADDR4]		[VARCHAR](30) NULL, 
	   [BENNATIONALITY] [CHAR](3) NULL, 
	   [BENDOB]			[DATETIME] NULL, 
	   [BENFILE]		[CHAR](1) NULL,  
	   [FILETYPE]		[CHAR](5) NULL,  
	   [FILETYPEDESC]	[VARCHAR](255) NULL,  
	   [FILEFORMAT]		[VARCHAR](255) NULL,  
	   [FILESIZE]		[INT] NULL,  
	   [FILEPATH]		[CHAR](1) NULL,
	   [FILETYPEFLAG]	[CHAR](2) NULL,
	   [oldPolicyNo]    [CHAR](10) NULL,
	   [oldSubOffCode]  [CHAR](3) NULL,
	   [roleid]			[CHAR](20) NULL, 
	   [submitted by]    [char] (200) NULL,
	   [oldbenplncd]    [char] (3) NULL,
	   [SPONSORTYPE]    [INT] NULL  ,
       [GROSSSALARYRANGE][CHAR](1) NULL,
       [SALARYBASEDONCOMMISSION][CHAR](3) NULL,
       [RESIDINGEMIRATE][CHAR](20) NULL,
       [RESIDINGLOCATION][CHAR](20) NULL,
       [WORKLOCATIONEMIRATE][CHAR](20) NULL,
       [WORKLOCATION][CHAR](20) NULL,
       [MEMBERINSURANCEINSIXMONTH][CHAR](50) NULL,
       [VISASPONSORID][CHAR](20) NULL,
       [SPONSORCONTACTNUMBER][CHAR](50) NULL,
       [SPONSOREMAILID][CHAR](50) NULL,
	  
    ) 
      /* End Temp table for Distributed out put*/

      INSERT INTO  #es_ediconsolidate 
		SELECT DISTINCT A.txtype,X.txdesc,txrefno,A.clntcode,A.polno,A.certno,A.depcode,deptype,namefirst,namelast,
				namemid,A.inieffdt,A.status,A.chgeffdt,suboffcode,departcd,memidno,dob,sex,marital,occupation,empno, 
				empdt,salary,A.bankac,memnation,A.effdate,A.bankcode,A.salarymode,bankactype,bankaceffdate,bankacstatus, 
				firstcovdt,maxageexemptind,vipstatus,sponsoridno,passportno,nationalidno,bankname,branchname,branchaddress, 
				bankcurrency,swiftcode,servicedeskid,servicedeskdate,dmpindicator,salarychgeffdt,studentind, outcntyind,
				PRODCODE =NULL,BENPLNCD =NULL,COVGCODE =NULL,PROPOSEDSA =NULL,PRDSTATUS =NULL,BENID = NULL,BENNAME =NULL, 
				BENRELAT =NULL,BENEFFDT =NULL,BENSHARE = NULL,REMARK =NULL,A.rcdsts,A.rcdusrid,A.rcddtstmp,txstatus,uploadusrid, 
				uploadtime,errorcode,errormsg, isprocessing,TR.SPONSORTYPE,TR.GROSSSALARYRANGE,TR.SALARYBASEDONCOMMISSION,
				TR.RESIDINGEMIRATE,TR.RESIDINGLOCATION,TR.WORKLOCATIONEMIRATE,TR.WORKLOCATION,TR.MEMBERINSURANCEINSIXMONTH,
				TR.VISASPONSORID,TR.SPONSORCONTACTNUMBER,TR.SPONSOREMAILID,

    FROM   tmemtrans A  (NOLOCK)
					   INNER JOIN es_txtypes X  (NOLOCK)
							   ON A.txtype = X.txtype 
					   INNER JOIN tpolicy P  (NOLOCK)
							   ON A.polno = P.polno 
					   INNER JOIN tpolpdt TP (nolock) 
							   ON A.polno = tp.polno 
						
					   --INNER JOIN tpolpdtcom tcom (nolock)
			           --        ON tp.polno = tcom.polno and tp.prodcode = tcom.prodcode and tp.effdate = tcom.effdate and tcom.prducode in (select es_usercode.code from es_usercode where es_usercode.userid= a.UPLOADUSRID union select es_usercode.code from es_usercode, es_userrole where es_usercode.userid=es_userrole.supervisorid and es_userrole.userid=a.UPLOADUSRID) 
					   left JOIN tclient C (nolock) 
							   ON --A.profilecode = c.clntcode 
							   --AND
								P.clntcode = c.clntcode 
						--Here i am including the join ----
		
							  JOIN TMEMBERREGULATORYINFO TR (nolock)
					          ON TR.CLNTCODE = A.CLNTCODE
							  AND TR.CERTNO = A.CERTNO
							  AND TR.DEPCODE = A.DEPCODE
					   INNER JOIN @PolNosCanAccess polnos  on polnos.polno= A.polno 




 But i am getting the error 

    Msg 4104, Level 16, State 1, Procedure ES_CONSOLIDATELIST_ADMIN_MEMBER, Line 360
    The multi-part identifier "TR.SPONSORTYPE" could not be bound.

Somebody please help