-- note : may need to effect security also ; may need to run as dba

USE [CMSQL]
GO
/****** Object:  StoredProcedure [dbo].[Insert_Or_Update_Config_Values]    Script Date: 10/29/2011 01:41:42 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter TABLE [dbo].[ConfigValues] ALTER COLUMN [value] [nvarchar](999) NULL ;
go
aLTER PROCEDURE [dbo].[Insert_Or_Update_Config_Values]

	  @prac varchar(999),

      @ins varchar(999),

      @tag varchar(999),

      @val varchar(999)

AS
 
SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF LTRIM(RTRIM(@val)) = ''
BEGIN

	DELETE FROM ConfigValues WHERE practice = @prac AND tag = @tag AND ins = @ins

END

ELSE

BEGIN

	IF EXISTS(SELECT * FROM ConfigValues WITH (UPDLOCK, HOLDLOCK)  WHERE ins = @ins AND tag = @tag AND practice = @prac)
	BEGIN

      UPDATE ConfigValues

      SET [value] = @val

      WHERE ins = @ins

        AND
   
        tag = @tag

		AND

		practice = @prac

	END

	ELSE

	BEGIN

      INSERT INTO ConfigValues (ins, practice, tag, [value])

      VALUES (@ins, @prac, @tag, @val)

	END

END

COMMIT

RETURN @@ERROR

 go
ALTER PROCEDURE [dbo].[Insert_Or_Update_Ins_Overrides]

      @ins varchar(999),

      @tag varchar(999),

      @val varchar(999)

AS
 
SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF LTRIM(RTRIM(@val)) = ''
BEGIN

	DELETE FROM InsOverrides WHERE tag = @tag AND ins = @ins

END

ELSE

BEGIN

	IF EXISTS(SELECT * FROM InsOverrides WITH (UPDLOCK, HOLDLOCK)  WHERE ins = @ins AND tag = @tag)
	BEGIN

      UPDATE InsOverrides

      SET [value] = @val

      WHERE ins = @ins

        AND
   
        tag = @tag

	END

	ELSE

	BEGIN

      INSERT INTO InsOverrides (ins, tag, [value])

      VALUES (@ins, @tag, @val)

	END

END

COMMIT

RETURN @@ERROR
 go
 
ALTER PROCEDURE [dbo].[Insert_Or_Update_System_Overrides]

      @tag varchar(999),

      @val varchar(999)

AS
 
SET NOCOUNT, XACT_ABORT ON

BEGIN TRAN

IF LTRIM(RTRIM(@val)) = ''
BEGIN

	DELETE FROM SystemOverrides WHERE tag = @tag

END

ELSE

BEGIN

	IF EXISTS(SELECT * FROM SystemOverrides WITH (UPDLOCK, HOLDLOCK) WHERE tag = @tag)
	BEGIN

      UPDATE SystemOverrides

      SET [value] = @val

      WHERE tag = @tag      

	END

	ELSE

	BEGIN

      INSERT INTO SystemOverrides (tag, [value])

      VALUES (@tag, @val)

	END

END

COMMIT

RETURN @@ERROR
go

select top 10 * from configtag where configgroup like '%SEC%'
INSERT INTO [ConfigTag] ([tag],[prompt],[length],[valkey],[browseFlag],[mandatory],[configGroup],[help],[tagorder])
VALUES('secpayzeroisdeduct'
,'Primary paid 0 without custom allowed means a deductible was applied'
,1,'YN',0,0,'SEC',
'Y = Whenever the primary pays zero assume a deductible was the cause; N = pay zero does not imply deductible'
,60)
INSERT INTO [ConfigTag] ([tag],[prompt],[length],[valkey],[browseFlag],[mandatory],[configGroup],[help],[tagorder])
VALUES('totpayasline1'
,'Use claim payment as first line payment unless overridden'
,1,'YN',0,0,'SEC',
'Y = If no override on the claim payment, use the total claim payment as the first line payment'
,60)

INSERT INTO [ConfigTag] ([tag],[prompt],[length],[valkey],[browseFlag],[mandatory],[configGroup],[help],[tagorder])
VALUES('incclaimlvlpay'
,'Include claim level payments in total pay?'
,1,'YN',0,0,'SQLPAY',
'Y = claim level payments will be included in total paid; N = they will not be included'
,60)

INSERT INTO [ConfigTag] ([tag],[prompt],[length],[valkey],[browseFlag],[mandatory],[configGroup],[help],[tagorder])
VALUES('priminspayonly'
,'Only include primary insurance payment types?'
,1,'YN',0,0,'SQLPAY',
'Y = Secondary type payments will be ignored; N = all insurance payment types will be included in paid'
,60)

INSERT INTO [ConfigTag] ([tag],[prompt],[length],[valkey],[browseFlag],[mandatory],[configGroup],[help],[tagorder])
VALUES('sqlclaimpayonly'
,'Only get claim payments from Lytec SQL, not detail payments?'
,1,'YN',0,0,'SQLPAY',
'Y = detail payments will not come from Lytec SQL; N = If SQL payment is used, it is used for detail and claim level payments'
,60)

insert into validvalues values('CMVERSION','SET12','2011+')
go
update validvalues set [desc] = '2010' where value = 'SET11'
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