TkanpUQB

1DECLARE @Now datetime, @Position int
2SET @Now = GETDATE()
3SET @Position = 225
4
5INSERT INTO AccessRights(Code, Description, Position, Scope, Type, AP, GL)
6SELECT 'BILLING_FIELDS', 'Edit Billing Fields', @Position, 'B', 'S', 0, 0
7WHERE NOT EXISTS (
8	SELECT *
9	FROM AccessRights
10	WHERE Code = 'BILLING_FIELDS'
11)
12
13IF NOT EXISTS (
14	SELECT *
15	FROM sys.tables
16	WHERE name = 'AccessTemplates_BillingFieldUpdates'
17) BEGIN
18	CREATE TABLE AccessTemplates_BillingFieldUpdates (TemplateID int, BranchRights varchar(400), DateRan datetime)
19END
20
21
22INSERT INTO AccessTemplates_BillingFieldUpdates (TemplateID, BranchRights, DateRan)
23SELECT AT.TemplateID, AT.BranchRights, @Now
24FROM AccessTemplates AT
25-- No access for billing fields (excludes ADMN)
26WHERE SUBSTRING(AT.BranchRights, @Position, 1) = '0'
27-- Access to edit a program, setup, or order
28AND (
29	dbo.AccessRightComplexCheck(SUBSTRING(AT.BranchRights, (SELECT AR.Position FROM AccessRights AR WHERE AR.Code = 'ORDER'), 1), 'E') = 1 OR
30	dbo.AccessRightComplexCheck(SUBSTRING(AT.BranchRights, (SELECT AR.Position FROM AccessRights AR WHERE AR.Code = 'SETUP'), 1), 'E') = 1 OR
31	dbo.AccessRightComplexCheck(SUBSTRING(AT.BranchRights, (SELECT AR.Position FROM AccessRights AR WHERE AR.Code = 'PROGRAM'), 1), 'E') = 1
32)
33
34UPDATE AT SET AT.BranchRights = SUBSTRING(AT.BranchRights, 1, @Position - 1) + '1' + SUBSTRING(AT.BranchRights, @Position + 1, LEN(AT.BranchRights) - @Position)
35FROM AccessTemplates AT
36JOIN AccessTemplates_BillingFieldUpdates ATBF ON AT.TemplateID = ATBF.TemplateID AND ATBF.DateRan = @Now