ALTER procedure [dbo].[GetPBXTNList]  
@BusinessId as int,  
@CustomerAcctNumber as Varchar(15)    
as  
  
Begin  
  
Declare @SalesPersonId as varchar(10)  
Declare @BusinessTicker as varchar(20)  
Declare @SwitchType as varchar(20)
Declare @BusinessProductId as int
  
  
Select @SalesPersonId=CGID, @BusinessTicker=BusinessTicker,@BusinessProductId=BusinessProductId from   
albhm01wsoinf12.serviceprofile.dbo.tblBusiness where BusinessId=@BusinessId  


SELECT * FROM 
((SELECT     
 convert(varchar(10),isnull(d.EffectiveDate,''),101) "EffectiveDate",   
   case isnull(d.DisconnectDate,'1/1/00') 
when '1/1/00' then '' 
else convert(varchar(10),d.DisconnectDate,101) End  
    "DisconnectDate", 

isnull(f.AdditionalListing,'') AS ListingType,  
isnull(d.ServiceNumber,'') ServiceNumber, 
isnull(s.svcdesc,'') "svcdesc", 
isnull(j.Line,'') "Line", 
@BusinessTicker "BusinessTicker",
isnull(nep.SipAuthenticationId,'') "ATAUserId",
isnull(nep.RootDeviceKey,'') "MACAddress",
isnull(right(servicenumber,4),'') "Extension",
isnull(nep.Password,'') "DevicePassword"
End "MACAddress"
from core.dbo.customer c  join core.dbo.usgsvc d on c.custid = d.custid  
left outer JOIN (select UsgSvcID, NonUsgSvcID, x.SvcTypeID, SvcDesc, x.EndDate  
from core.dbo.NonUsgSvc x inner join core.dbo.productcatalog y on x.SvcTypeID = y.SvcTypeID 
where x.EndDate is null  
and 
(x.SvcTypeId in (Select BusinessCatalogValue from albhm01wsoinf12.serviceprofile.dbo.tblBusinessCatalog where CGID=@SalesPersonId and BusinessCatalogKey='NUSC' and isnumeric(BusinessCatalogValue)=1) or vendornumber='1')
) 
s   

ON s.UsgSvcID = d.UsgSvcID   
left outer join core.dbo.vw_UsgSvcLocalCarrier h          
on d.UsgSvcID = h.UsgSvcID  
left outer join core.dbo.usgsvcATAInformation j   
on d.usgsvcId=j.usgsvcId  
LEFT OUTER JOIN core.dbo.UsgSvcProvOrderInfo f WITH (nolock)   
ON d.UsgSvcID = f.UsgSvcID          
left outer join core.dbo.VOIPListings i        
on f.AdditionalListing = i.ListingDescription  
left outer join GAATL01WSVINS.NEProvisioning.dbo.vw_LineRegistrations nep
on nep.UserId= d.name and nep.CustomerAcctNumber=c.CustomerAcctNumber 
where c.customeracctnumber = @CustomerAcctNumber and c.SalesId =@SalesPersonId  
and ISNULL(d.branch,'LINEDFLT') in (Select linetype from tblBusinessProductLineType where BusinessProductId=@BusinessProductId)
)
union
(SELECT     
convert(varchar(10),isnull(n.StartDate,''),101) "EffectiveDate",   
case isnull(n.EndDate,'1/1/00') when '1/1/00' then '' else convert(varchar(10),n.EndDate,101) End   "DisconnectDate", 
'' AS ListingType,  
'' ServiceNumber, 
isnull(n.svcdesc,'') "svcdesc", 
'' "Line",
@BusinessTicker "BusinessTicker",
'' "ATAUserId",
'' "MACAddress",
'' "Extension",
'' "DevicePassword"
from core.dbo.customer c left outer JOIN (select CustId, NonUsgSvcID, x.SvcTypeID, SvcDesc, x.StartDate,x.EndDate from core.dbo.NonUsgSvc x inner join core.dbo.productcatalog y on x.SvcTypeID = y.SvcTypeID  and y.vendornumber='1' ) n on c.CustId=n.CustId 
Where LEFT(c.customeracctnumber,10) = LEFT(@CustomerAcctNumber,10) and c.SalesId =@SalesPersonId AND ISNULL(SvcDesc,'')<>'') ) T 
order by line
End