use cmsql
go 

insert into  validvalues values(
'SPECID' , '005010X222A1', 'Professional 5010')
insert into  validvalues values(
'SPECID' , '005010X223A2', 'Institutional 5010')

update validvalues 
set validvalues.[desc] = value
where validvalues.[desc] is null

update 
validvalues
set  value = '<' + value + '>'
where valkey = 'VFIELDS'

insert into configtag values(
'faccodequalif'
,'Facility Code Qualifier'
,'5'
,''
, 0	
, 0	
, 'GEN'	
, 'B for physician and A for clinic. B = Place of service from FAO record of EMCNSF A = Uniform Billing Claim Form Bill Type'
,0)

insert into configtag values(
'patsigsource'
,'Patient Signature Source'
,'2'
,''
, 0	
, 0	
, 'GEN'	
, 'P = signature generated by provider because patient was not physically present for services.'
,0)

go
CREATE FUNCTION [dbo].[getDataSourceName] 
  (@udlin text )  
   returns nvarchar(250)
AS
BEGIN
	DECLARE @returnPracticeSource nvarchar(500)
-- extract practice source from middle of udl
set @returnPracticeSource = 
 substring (@udlin,charindex('Catalog=',@udlin )+8 ,
 ( 
case when   charindex(';',@udlin, charindex('Catalog=',@udlin )) > 0
then 
(charindex(';',@udlin,charindex('Catalog=',@udlin )) 
-  charindex('Catalog=',@udlin )  -8   )
else
 datalength ( @udlin) - charindex('Catalog=',@udlin )-7
end)
  ) 
	RETURN @returnPracticeSource
END
go
 CREATE view [dbo].[view_lytec_practice_list] as
select lytecpractice.[practice name] as practice_name, 
 dbo.getDataSourceName(  lytecpractice.udl) as practice_source
from [lytec practice list].dbo.[practice list] as lytecpractice

go
create view [dbo].[view_practices_used_and_available] as
select * from view_lytec_practice_list
union 
select 'Does not exist: ' + sqlcatalog as practice_name, 
sqlcatalog as practice_source from practice p left outer join 
view_lytec_practice_list v
on p.sqlcatalog = v.practice_source
where v.practice_source is   null


 

