use cmsql
go

/****** Object:  Table [dbo].[ScrubInsRules]    Script Date: 06/14/2014 11:37:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

INSERT INTO [CMSQL].[dbo].[ConfigTag]
           ([tag]
           ,[prompt]
           ,[length]
           ,[valkey]
           ,[browseFlag]
           ,[mandatory]
           ,[configGroup]
           ,[help]
           ,[tagorder])
     VALUES
           ( 'scrubber'
           ,'Scrub Claims?'
           ,1
           ,'YN'
           ,0
           ,0
           ,'SQLPAY'
           ,'Y = scrub Claims using information directly from Lytec'
           ,999)
GO
 

CREATE TABLE [dbo].[ScrubInsRules](
	[RuleID] [nvarchar](50) NOT NULL,
	[InsuranceCompany] [nvarchar](255) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[ScrubRuleTypes](
	[RuleType] [nvarchar](50) NOT NULL,
	[RuleDescription] [nvarchar](255) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[ScrubRuleDefinitions](
	[RuleID] [nvarchar](50) NOT NULL,
	[RuleType] [nvarchar](50) NULL,
	[ProcToTest] [nvarchar](50) NULL,
	[ProcToCompare] [nvarchar](50) NULL,
	[Modifier] [nvarchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[ScrubProcTypes](
	[ProcType] [nvarchar](50) NOT NULL,
	[Proc] [nvarchar](50) NULL
) ON [PRIMARY]


create view [dbo].[ScrubRulesToEnforce] as
select 
si.[InsuranceCompany]
,si.[RuleID]
,sr.[RuleType]
,rt.[RuleDescription]
 ,sr.[ProcToTest] as ProcToTestName
 
, case when testproc.[Proc] is null then sr.[ProcToTest] else testproc.[Proc] end as TestProcedure 
,sr.[ProcToCompare] as ProcToCompareName
 
, case when compproc.[Proc] is null then sr.[ProcToCompare] else compproc.[Proc] end as CompareProcedure 
,sr.Modifier as needed_mod


from
ScrubInsRules si
inner join
ScrubRuleDefinitions sr
on sr.[RuleID] = si.[RuleID]
inner join ScrubRuleTypes rt on rt.[RuleType] = sr.[RuleType]
left outer join ScrubProcTypes compproc
on sr.[ProcToCompare] = compproc.[ProcType]
left outer join ScrubProcTypes testproc
on sr.[ProcToTest] = testproc.[ProcType]


GO




