if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[paQryTercerosDsoT]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 DROP PROCEDURE [dbo].[paQryTercerosDsoT]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[paQryTercerosDsoT] @pmIdEstado VARCHAR(4)=Null
 AS 
 SELECT T.IdTercero,T.RazonSocial,T.TipoId,CASE WHEN T.TipoId <> 'N' AND IniStgNom>1 THEN SUBSTRING(T.RazonSocial,1,IniStgNom-1) ELSE '' END AS Apellidos
 ,CASE WHEN T.TipoId <> 'N' AND IniStgNom>0 THEN SUBSTRING(T.RazonSocial,IniStgNom,(LEN(T.RazonSocial)-IniStgNom)+1) ELSE '' END Nombres
,T.FechaNac,T.Direccion,T.e_mail,T.Telefono,T.TelMovil,T.RecPublicos,T.CargoPublico,T.PersonaPub,T.FecInicioPP,T.FecFinalPP,T.IdSector,S.SectorEco
,T.RL_Nombre,T.IdLocal,L.Localidad,D.Departamento,P.NombrePais,T.VrIngresos,T.VrOtroIng,T.VrGastos,T.VrActivos,T.VrPasivos,T.OperActVirtual,T.TipoOperVirtual
,T.LavadoActivos,T.Inactivo AS Inactvo
,CASE WHEN T.EsCliente = 1 THEN TC.Referencia WHEN T.EsProveedor = 1 THEN TP.NomContac ELSE '' END AS NombreContacto
,CASE WHEN T.EsCliente = 1 THEN TC.TelRefncia WHEN T.EsProveedor = 1 THEN TP.TelContac ELSE '' END AS TelefonoContacto
,CASE WHEN T.EsCliente = 1 THEN TC.CargRefncia WHEN T.EsProveedor = 1 THEN TP.CargContac ELSE '' END AS CargoContacto
 FROM Terceros AS T INNER JOIN SectoresEco AS S ON T.IdSector=S.IdSector INNER JOIN Localidades AS L ON T.IdLocal=L.IdLocal
 INNER JOIN Departamentos AS D ON L.IdDep=D.IdDep INNER JOIN adm_Paises AS P ON D.IdPais=P.IdPais
 LEFT JOIN TercProvee AS TP ON T.IdTercero=TP.IdProv LEFT JOIN TercClieRef AS TC ON T.IdTercero=TC.IdClie 
 WHERE (T.IdEstado=@pmIdEstado OR @pmIdEstado IS NULL)