Hi,
please check if the following code will help you to get the expected results.
IMPORTANT NOTE:
Since there are no table definitions nor any sample data the code below is completely untested and should be only used a something to start with on your test system. Therefore, it does not contain any data modifaction.
SET @SQLStatement =
'SELECT AuditData.*, ''CallType=12'', ''TATCallUnit=1''
from ' + @TABLE + ' AuditData
inner join AuditMaster am on am.ID=AuditData.AuditMaster_ID
inner join HomeCircleMaster hcm on hcm.Ori_CircleMaster_ID=am.CircleMaster_ID
and hcm.Ori_ServiceTypeMaster_ID=1
and hcm.Dest_ServiceTypeMaster_ID=1
inner join AuditTaggingMaster atm on atm.AuditMaster_ID=am.ID
inner join NoSeriesMaster ns on (ns.CircleMaster_ID=am.CircleMaster_ID
or ns.CircleMaster_ID=hcm.Dest_CircleMaster_ID)
and ns.ProviderMaster_ID=am.ProviderMaster_ID
and ns.ServiceTypeMaster_ID=1
inner join ProviderMaster_CallTypeMaster pm_ctm on pm_ctm.ProviderMaster_ID=am.ProviderMaster_ID
and pm_ctm.CallTypeMaster_ID=101
and pm_ctm.CallTypeTagValue=AuditData.CallTypeTag
INNER JOIN NoSeriesMaster_Prefix nsp
ON nsp.PrefixNo = substring(AuditData.CallTo,1,convert(int,PrefixLen))
AND nsp.PrefixType=''SMS''
and len(AuditData.CallTo) = convert(varchar(10),convert(int,nsp.PrefixLen)+convert(int,nsp.AfterPrefixLen))
where AuditData.TATCallType is null
and nsp.PrefixNo + ns.NoSeries = Left(AuditData.CallTo,len(ns.NoSeries)+convert(int,PrefixLen))
and AuditData.AuditMaster_ID=' +@AuditMasterID + ''
print(@SQLStatement)