USP_CREATECMSSCHEDULES
Used by the CMS to create business process schedules.
Definition
Copy
CREATE procedure dbo.USP_CREATECMSSCHEDULES
as
begin
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
------------------------Create schedules for manually scheduled processes-----------------------------------
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
declare @SCHEDULEUPGRADE table (BUSINESSPROCESSID uniqueidentifier, SCHEDULEID uniqueidentifier, BPSCHEDULEID uniqueidentifier, HOURLY bit, SETTINGNAME nvarchar(100))
insert into @SCHEDULEUPGRADE values('6F6A65C4-15C8-4412-99B8-D6BB3C66E412', '2C7A2515-3EE6-4C9E-8307-03D630B986DE', '9DDD6003-D258-468C-8EDD-8CACC8AC31A0', 0, 'EmailSchedule')
insert into @SCHEDULEUPGRADE values('2b566163-8b91-428c-b4b6-b90ab1434d58', 'A9D6CE0B-78AC-4ef1-ABBE-7518843B1E1D', '404CBAE1-E9A5-441e-8B70-68EA5065CE2F', 0, 'ListRefreshSchedule')
insert into @SCHEDULEUPGRADE values('21670ef3-992b-4fd3-ac83-320691e7f827', 'FDCD4403-1849-425f-AF5D-FF91BD969CF8', 'DBD27F7C-19B7-4649-A567-61E7B09E07AB', 1, 'RoleRefreshSchedule')
insert into @SCHEDULEUPGRADE values('23796b55-70f5-40f0-9ac8-3c199e73a172', 'AC768B79-5487-459c-8A74-AF1D0B653966', '499B143D-DA00-40ad-BA50-C35480ECCA43', 0, 'REIntegrationSchedule')
insert into @SCHEDULEUPGRADE values('2f72adcf-a190-4f40-8178-740c85fdc02e', '3905B4F0-0DBF-4c8f-9136-3B820A9E0D3A', '5136AEF6-F2A0-48de-AA1B-D4416AA0D1FA', 1, 'ImportSchedule')
insert into @SCHEDULEUPGRADE values('4c52cc2e-69cb-47ac-96da-d056a7676627', '16E39EA8-085E-42c7-964F-A7A322A016D0', '011BB285-D505-4ea4-A656-04A0D68F9881', 0, 'AdvocacyStatusUpdateSchedule')
insert into @SCHEDULEUPGRADE values('08bd6029-02d7-46bf-ba05-0607f95e77e2', 'E0B4DE7D-2849-4deb-A6FD-195A9253F898', '1041AF4F-D371-475c-BEE5-6600FB345910', 1, 'FundRaiserSyncSchedule')
declare @DATE date = GETDATE();
declare @CHANGEAGENTID uniqueidentifier;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID out;
declare @BUSINESSPROCESSID uniqueidentifier
declare @SCHEDULEID uniqueidentifier
declare @BPSCHEDULEID uniqueidentifier
declare @HOURLY bit
declare @SETTINGNAME nvarchar(100)
declare @PRODUCTFLAGID uniqueidentifier;
declare @CORRECTPRODUCTINSTALLED bit;
declare SCHEDULECURSOR cursor local FAST_FORWARD for
select BUSINESSPROCESSID, SCHEDULEID, BPSCHEDULEID, HOURLY, SETTINGNAME from @SCHEDULEUPGRADE;
open SCHEDULECURSOR;
fetch next from SCHEDULECURSOR into @BUSINESSPROCESSID, @SCHEDULEID, @BPSCHEDULEID, @HOURLY, @SETTINGNAME;
while @@FETCH_STATUS = 0
begin
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
-- Create schedule for this ID
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE)
values (@SCHEDULEID, 4, 1, 2, 60, '00:00:00', '23:59:59', @DATE);
-- Business Process schedule for these IDs
insert into BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (@BPSCHEDULEID, @BUSINESSPROCESSID, @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
--Load value for the setting
declare @VAL nvarchar(50)
select @VAL = VALUE from dbo.Setting where Name = @SETTINGNAME
--Parse setting
declare @SCHEDULETYPE tinyint = 0
declare @FREQUENCY int = 1
declare @FREQUENCYTYPE tinyint = 4
declare @TIME time(0)
if @HOURLY = 1 set @FREQUENCYTYPE = 8
if LEN(@VAL) > 0
begin
begin try
select @SCHEDULETYPE = SUBSTRING(@VAL, 1,1)
if @SCHEDULETYPE = 1
begin
select @FREQUENCY = SUBSTRING(@VAL, 3, LEN(@VAL) - 2)
set @FREQUENCY = @FREQUENCY / 60
if @HOURLY = 1
set @FREQUENCY = @FREQUENCY / 60
end
else if @SCHEDULETYPE = 2
begin
select @TIME = SUBSTRING(@VAL, 3, LEN(@VAL) - 4)
declare @PM bit = 0
select @PM = SUBSTRING(@VAL, LEN(@VAL), 1)
if @PM = 1
set @TIME = DATEADD(HOUR, 12, @TIME)
end
end try
begin catch
--if we can't interpret the schedule, we'll use the default
end catch
end
--Update schedule based on setting
exec USP_DATAFORMTEMPLATE_EDIT_SCHEDULE_SIMPLECMSSCHEDULE @SCHEDULEID, @SCHEDULETYPE, @FREQUENCY, @FREQUENCYTYPE, @TIME
--Actively disable the advocacy schedules for clients without the product flag.
if @SCHEDULEID = '16E39EA8-085E-42c7-964F-A7A322A016D0'
begin
set @PRODUCTFLAGID = '4560756A-E63C-4804-82F0-9D7D0AB36997'; --advocacy product flag
set @CORRECTPRODUCTINSTALLED = 0;
EXEC dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT;
if @CORRECTPRODUCTINSTALLED = 0
update dbo.SCHEDULE set ENABLED=0 where ID=@SCHEDULEID;
end
end
fetch next from SCHEDULECURSOR into @BUSINESSPROCESSID, @SCHEDULEID, @BPSCHEDULEID, @HOURLY, @SETTINGNAME;
end
close SCHEDULECURSOR
deallocate SCHEDULECURSOR
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
------------------------Create schedules for behind the scenes processes------------------------------------
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
declare @DIFF int
set @TIME = '00:00:00';
select @DIFF = RAND() * 300;
select @TIME = DATEADD(second, @DIFF, @TIME);
declare @STATSCHEDULEID uniqueidentifier = '597C44EF-6291-486B-827B-8C515E0E7EC9'
------------------------------------------------------------------------------------------------------------
--Create email stats schedule to mirror the the regular email bp schedule--------------------------
------------------------------------------------------------------------------------------------------------
if not exists (select * from dbo.SCHEDULE where ID=@STATSCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE)
values (@STATSCHEDULEID, 4, 1, 4, 5, @TIME, '23:59:59', @DATE);
end
------------------------------------------------------------------------------------------------------------
--Create email stats bp schedule to use the same schedule as the regular email bp --------------------------
------------------------------------------------------------------------------------------------------------
if not exists (select * from dbo.BUSINESSPROCESSSCHEDULE where ID='A391A41D-32DC-45e1-95E6-2427EB5EFD2B')
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('A391A41D-32DC-45e1-95E6-2427EB5EFD2B', 'defd98e8-b915-4c32-9ecf-05447ea5b5b1', @STATSCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
------------------------------------------------------------------------------------------------------------
--Create content comparison BP to run once a day at a random time
------------------------------------------------------------------------------------------------------------
set @TIME = '00:00:00';
select @DIFF = RAND() * 86400;
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = 'D9A608AF-62D7-4329-95A6-3D42E28D39C8';
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE, TIME)
values (@SCHEDULEID, 4, 1, 0, 0, null, null, @DATE, @TIME);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('B0BC30B5-16EE-4151-BF96-3DB703ACD067', '59015008-4351-454f-98ed-c89f65b7ac90', @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
------------------------------------------------------------------------------------------------------------
--Create code table refresh bp schedule to run hourly at a random time--------------------------------------
------------------------------------------------------------------------------------------------------------
select @DIFF = RAND() * 3600;
set @TIME = '00:00:00';
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = '4FE30020-4991-4233-B0B5-730AD77B3110';
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE)
values (@SCHEDULEID, 4, 1, 8, 1, @TIME, '23:59:59', @DATE);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('2A704B73-0F45-4AE1-A98B-66CA5D2A7AD8', '275800a9-a0b7-477d-bd6f-87defd16b2ec', @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
------------------------------------------------------------------------------------------------------------
--Create transaction sync bp schedule to run every 2 minutes at a random time-------------------------------
------------------------------------------------------------------------------------------------------------
select @DIFF = RAND() * 120;
set @TIME = '00:00:00';
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = 'F3DC1530-CDF2-4989-BF6F-FABF0E958277';
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE)
values (@SCHEDULEID, 4, 1, 4, 2, @TIME, '23:59:59', @DATE);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('D0B5C491-E86C-438c-B3E7-2D98B2056D9B', '13c22921-de59-4122-accf-aeb6d9ec3ad0', @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
------------------------------------------------------------------------------------------------------------
--Create OA transaction sync bp schedule to run every 2 minutes at a random time----------------------------
------------------------------------------------------------------------------------------------------------
select @DIFF = RAND() * 120;
set @TIME = '00:00:00';
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = 'E6D18F61-8094-4115-ACF6-19934E5FCA64';
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE)
values (@SCHEDULEID, 4, 1, 4, 2, @TIME, '23:59:59', @DATE);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('19159103-1384-44bd-B425-C61D157EB980', '9c98eda7-8411-4ff6-9640-735200c82359', @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
--enable or disable schedule depending on specific product flag
set @PRODUCTFLAGID = '2321fbe7-6e6f-42f1-85aa-3580763b4613' --OA product flag
set @CORRECTPRODUCTINSTALLED = 0
EXEC dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT
update dbo.SCHEDULE set ENABLED=@CORRECTPRODUCTINSTALLED where ID=@SCHEDULEID
------------------------------------------------------------------------------------------------------------
--Create CMS Migrate Anonymous Email Preferences bp schedule to run daily at a random time------------------
------------------------------------------------------------------------------------------------------------
set @TIME = '00:00:00';
select @DIFF = RAND() * 86400;
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = 'CD04B171-3702-403B-A05A-2F10CA9CD91D';
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE, TIME)
values (@SCHEDULEID, 4, 1, 0, 0, null, null, @DATE, @TIME);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('1F15AD42-E25B-404e-B210-2F6A4E198FFD', 'BC2D6505-302A-4E89-8C51-69492105E75D', @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
------------------------------------------------------------------------------------------------------------
--DELETE BBSP Template Upload Business Process Schedule------------------
------------------------------------------------------------------------------------------------------------
delete from SCHEDULE where ID = 'C1882D51-8D93-44BC-844F-E96A80FB7C59';
delete from BUSINESSPROCESSSCHEDULE where SCHEDULEID= 'C1882D51-8D93-44BC-844F-E96A80FB7C59';
------------------------------------------------------------------------------------------------------------
--Create CMS Temp Social Media User Data Sync Business Process schedule to run daily at a random time------------------
------------------------------------------------------------------------------------------------------------
set @TIME = '00:00:00';
select @DIFF = RAND() * 86400;
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = 'C36D5137-6D9B-407A-80B6-1DEBBA1EF063';
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE, TIME)
values (@SCHEDULEID, 4, 1, 0, 0, null, null, @DATE, @TIME);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('3C425317-9DF2-4455-B757-8260B39486A6', '9b33caab-2901-4a3a-8334-f154f558d008', @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
------------------------------------------------------------------------------------------------------------
--Create CMS Data Purge Business Process to run weekly at a random time ------------------------------------
------------------------------------------------------------------------------------------------------------
set @TIME = '00:00:00';
select @DIFF = RAND() * 86400;
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = '4110DC68-6CB7-4AF6-9B11-0AE86B40E95E';
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, DAYSOFWEEK, DAYOFWEEKCODE, STARTDATE, TIME)
values (@SCHEDULEID, 8, 1, 0, 0, null, null, 1, 0, @DATE, @TIME);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('09CD860D-6EB0-42D0-B206-B08A83E4999B', '57299ed7-bc8c-4c69-a3a7-849ac694a8d6', @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
------------------------------------------------------------------------------------------------------------
--Create Reconcile Event2 orphaned gifts to run once a day -------------------------------------------------
------------------------------------------------------------------------------------------------------------
set @TIME = '00:00:00';
select @DIFF = RAND() * 86400;
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = '58526ff3-3136-47b0-92f5-1ab27fe50122';
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE, TIME)
values (@SCHEDULEID, 4, 1, 0, 0, null, null, @DATE, @TIME);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('1a9dc8a1-dd0e-46be-90ac-95991d3709c9', '12d02995-1599-4927-9895-68d22a9c7c57', @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
--enable or disable schedule depending on specific product flag
set @PRODUCTFLAGID = 'F238E8FE-06AE-4FDC-BEAF-FDF6637E1982'; -- Full CMS only
set @CORRECTPRODUCTINSTALLED = 0;
exec dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT
update dbo.SCHEDULE set ENABLED = @CORRECTPRODUCTINSTALLED where ID = @SCHEDULEID
------------------------------------------------------------------------------------------------------------
-- SHL BBIS User Story 400447; Gets any incomplete Shopping Cart sessions and processes those sessions if possible and deletes thos sessions from dbo.CMS_SESSIONVARIABLEBACKUP ------------------------------------
-- Runs daily at a random time
------------------------------------------------------------------------------------------------------------
set @TIME = '00:00:00';
select @DIFF = RAND() * 86400;
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = 'BCCC5894-F460-49FA-AAA8-E6C88219F2FC';
if not exists (select * from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE)
values (@SCHEDULEID, 4, 1, 8, 1, '00:00:00', '23:59:59', @DATE);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values ('FC2197CB-F750-4D72-8047-B558152008C1', '0e45368c-c265-4677-b147-fab6949c8a83', @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
--enable or disable schedule depending on specific product flag
set @PRODUCTFLAGID = 'F238E8FE-06AE-4FDC-BEAF-FDF6637E1982'; -- Full CMS only
set @CORRECTPRODUCTINSTALLED = 0;
exec dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT;
update dbo.SCHEDULE set ENABLED = @CORRECTPRODUCTINSTALLED where ID = @SCHEDULEID;
------------------------------------------------------------------------------------------------------------
--Friends Asking Friends business process schedules --------------------------
------------------------------------------------------------------------------------------------------------
exec dbo.USP_CREATE_FAFSCHEDULE
declare
@SCHEDULERCHANGEAGENTID uniqueidentifier,
@SYSTEMROLEID uniqueidentifier = '83E0479E-71FD-4645-8A62-B7288290A436' -- Blackbaud Built-In AppFx System User Role
declare @CHARGETRANSACTIONSCHEDULEID uniqueidentifier = 'd3944ce2-7656-4557-81db-652f03e15007'
declare @BUSINESSPROCESSSCHEDULEID uniqueidentifier ='5ec43495-db85-477e-aede-1b61687268cb'
declare @SPECID uniqueidentifier = 'f226bf61-4245-4426-8a71-f4db4062bccc'; -- CMS Charge Donation Transaction Business Process
if not exists (select ID from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @SPECID)
begin
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID, BUSINESSPROCESSCATALOGID, GRANTORDENY, ADDEDBYID, CHANGEDBYID)
values (@SYSTEMROLEID, @SPECID, 1, @CHANGEAGENTID, @CHANGEAGENTID);
end
if not exists(select ID from dbo.SCHEDULE where ID = @CHARGETRANSACTIONSCHEDULEID)
begin
insert into dbo.SCHEDULE
(ID,[ENABLED],TYPECODE,FREQUENCY,SUBDAYFREQUENCYTYPECODE,[TIME],SUBDAYFREQUENCY,DAYSOFWEEK,[DAYOFMONTH],WEEKCODE,DAYOFWEEKCODE,MONTHCODE,STARTDATE,SUBDAYSTARTINGTIME,SUBDAYENDINGTIME)
VALUES
(@CHARGETRANSACTIONSCHEDULEID,1,4,1,4,null,5,0,0,0,0,0,'2015-01-01','00:00:00','23:59:59');
end
if not exists(select top 1 1 from BUSINESSPROCESSSCHEDULE where SCHEDULEID=@CHARGETRANSACTIONSCHEDULEID)
begin
insert into dbo.BUSINESSPROCESSSCHEDULE (ID ,BUSINESSPROCESSID ,SCHEDULEID , ADDEDBYID , CHANGEDBYID, DATEADDED ,DATECHANGED)
values
(@BUSINESSPROCESSSCHEDULEID,@SPECID,@CHARGETRANSACTIONSCHEDULEID,@CHANGEAGENTID,@CHANGEAGENTID, getdate(), getdate());
end
-------------------------------------------------------------------------------------------------------------
--Sync CMS SolicitCode Data ------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
declare @SYNCSOLICITCODESCHEDULEID uniqueidentifier = '2D030A62-C894-4C79-B610-9F14D269E00E'
set @BUSINESSPROCESSSCHEDULEID ='C9F81601-C354-4879-9FF0-31B690EFDC11'
set @SPECID = '37360084-5E2E-4FFA-9C5F-210DB19DDC61' -- CMS Charge Donation Transaction Business Process
set @SYSTEMROLEID = '83E0479E-71FD-4645-8A62-B7288290A436'; -- Blackbaud Built-In AppFx System User Role
if not exists (select ID from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @SPECID)
begin
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID, BUSINESSPROCESSCATALOGID, GRANTORDENY, ADDEDBYID, CHANGEDBYID)
values (@SYSTEMROLEID, @SPECID, 1, @CHANGEAGENTID, @CHANGEAGENTID);
end
if not exists(select ID from dbo.SCHEDULE where ID = @SYNCSOLICITCODESCHEDULEID)
begin
insert into dbo.SCHEDULE
(ID,[ENABLED],TYPECODE,FREQUENCY,SUBDAYFREQUENCYTYPECODE,[TIME],SUBDAYFREQUENCY,DAYSOFWEEK,[DAYOFMONTH],WEEKCODE,DAYOFWEEKCODE,MONTHCODE,STARTDATE,SUBDAYSTARTINGTIME,SUBDAYENDINGTIME)
VALUES
(@SYNCSOLICITCODESCHEDULEID,1,4,1,4,null,5,0,0,0,0,0, getdate() ,'00:00:00','23:59:59');
end
if not exists(select top 1 1 from BUSINESSPROCESSSCHEDULE where SCHEDULEID=@SYNCSOLICITCODESCHEDULEID)
begin
insert into dbo.BUSINESSPROCESSSCHEDULE (ID ,BUSINESSPROCESSID ,SCHEDULEID , ADDEDBYID , CHANGEDBYID, DATEADDED ,DATECHANGED)
values
(@BUSINESSPROCESSSCHEDULEID,@SPECID,@SYNCSOLICITCODESCHEDULEID,@CHANGEAGENTID,@CHANGEAGENTID, getdate(), getdate());
end
-------------------------------------------------------------------------------------------------------------
--Sync CMS SolicitCode Data End ------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
--Sync CMS Inactive Recipient Data ------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
declare @SYNCINACTIVERECIPIENTSCHEDULEID uniqueidentifier = '9922232E-609B-4BC7-A233-7946257011AE'
set @BUSINESSPROCESSSCHEDULEID ='CE6202F2-EFF2-4454-AC74-2C02C20F218B'
set @SPECID = '1AF781F9-BA9F-41B8-B994-921AA1DE7619' -- CMS Inactive Recipient Business Process
set @SYSTEMROLEID = '83E0479E-71FD-4645-8A62-B7288290A436'; -- Blackbaud Built-In AppFx System User Role
if not exists (select ID from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @SPECID)
begin
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID, BUSINESSPROCESSCATALOGID, GRANTORDENY, ADDEDBYID, CHANGEDBYID)
values (@SYSTEMROLEID, @SPECID, 1, @CHANGEAGENTID, @CHANGEAGENTID);
end
if not exists(select ID from dbo.SCHEDULE where ID = @SYNCINACTIVERECIPIENTSCHEDULEID)
begin
insert into dbo.SCHEDULE
(ID,[ENABLED],TYPECODE,FREQUENCY,SUBDAYFREQUENCYTYPECODE,[TIME],SUBDAYFREQUENCY,DAYSOFWEEK,[DAYOFMONTH],WEEKCODE,DAYOFWEEKCODE,MONTHCODE,STARTDATE,SUBDAYSTARTINGTIME,SUBDAYENDINGTIME)
VALUES
(@SYNCINACTIVERECIPIENTSCHEDULEID,1,4,1,0,'00:00:00',0,0,0,0,0,0,getdate() ,null,null);
end
if not exists(select top 1 1 from BUSINESSPROCESSSCHEDULE where SCHEDULEID=@SYNCINACTIVERECIPIENTSCHEDULEID)
begin
insert into dbo.BUSINESSPROCESSSCHEDULE (ID ,BUSINESSPROCESSID ,SCHEDULEID , ADDEDBYID , CHANGEDBYID, DATEADDED ,DATECHANGED)
values
(@BUSINESSPROCESSSCHEDULEID,@SPECID,@SYNCINACTIVERECIPIENTSCHEDULEID,@CHANGEAGENTID,@CHANGEAGENTID, getdate(), getdate());
end
-------------------------------------------------------------------------------------------------------------
--Sync CMS Inactive Recipients Data End ------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
--CMS Charge Event Transaction ------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
declare @EVENTCHARGETRANSACTIONSCHEDULEID uniqueidentifier = '5DA9C9E3-47E1-47C0-8B40-B70F58A4DCAA'
set @BUSINESSPROCESSSCHEDULEID ='894AEE5A-3FC9-484F-AC4C-A0C539C6053C'
set @SPECID = '7c997d69-d87c-41bb-b5ec-60fbfc3a6261'; -- CMS Charge Event Transaction Business Process
if not exists (select ID from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @SPECID)
begin
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID, BUSINESSPROCESSCATALOGID, GRANTORDENY, ADDEDBYID, CHANGEDBYID)
values (@SYSTEMROLEID, @SPECID, 1, @CHANGEAGENTID, @CHANGEAGENTID);
end
if not exists(select ID from dbo.SCHEDULE where ID = @EVENTCHARGETRANSACTIONSCHEDULEID)
begin
insert into dbo.SCHEDULE
(ID,[ENABLED],TYPECODE,FREQUENCY,SUBDAYFREQUENCYTYPECODE,[TIME],SUBDAYFREQUENCY,DAYSOFWEEK,[DAYOFMONTH],WEEKCODE,DAYOFWEEKCODE,MONTHCODE,STARTDATE,SUBDAYSTARTINGTIME,SUBDAYENDINGTIME)
VALUES
(@EVENTCHARGETRANSACTIONSCHEDULEID,1,4,1,4,null,5,0,0,0,0,0,'2015-01-01','00:00:00','23:59:59');
end
if not exists(select top 1 1 from BUSINESSPROCESSSCHEDULE where SCHEDULEID=@EVENTCHARGETRANSACTIONSCHEDULEID)
begin
insert into dbo.BUSINESSPROCESSSCHEDULE (ID ,BUSINESSPROCESSID ,SCHEDULEID , ADDEDBYID , CHANGEDBYID, DATEADDED ,DATECHANGED)
values
(@BUSINESSPROCESSSCHEDULEID,@SPECID,@EVENTCHARGETRANSACTIONSCHEDULEID,@CHANGEAGENTID,@CHANGEAGENTID, getdate(), getdate());
end
--------------------------------------------------------------------------------------------------------------------
--CMS Charge Event Transaction End ------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
--Create Reconcile Donation orphaned gifts to run once a day -------------------------------------------------
------------------------------------------------------------------------------------------------------------
set @TIME = '00:00:00';
select @DIFF = RAND() * 86400;
select @TIME = DATEADD(second, @DIFF, @TIME);
set @SCHEDULEID = '92d6cc9f-4cc9-4212-b071-e973a4b13a86';
declare @DONATION_RECONCILE_BUSINESSPROCESSID as uniqueidentifier = 'e08c03ea-f6ca-462d-9edb-c775834f1a8b';
declare @DONATION_RECONCILE_BUSINESSPROCESSSCHEDULEID as uniqueidentifier = 'e1ce3048-4cfe-450b-a028-baa357f4862c';
if not exists (select ID from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @DONATION_RECONCILE_BUSINESSPROCESSID)
begin
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID, BUSINESSPROCESSCATALOGID, GRANTORDENY, ADDEDBYID, CHANGEDBYID)
values (@SYSTEMROLEID, @DONATION_RECONCILE_BUSINESSPROCESSID, 1, @CHANGEAGENTID, @CHANGEAGENTID);
end
if not exists (select 1 from dbo.SCHEDULE where ID=@SCHEDULEID)
begin
insert into dbo.SCHEDULE (ID, TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE, TIME)
values (@SCHEDULEID, 4, 1, 0, 0, null, null, @DATE, @TIME);
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, SCHEDULEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (@DONATION_RECONCILE_BUSINESSPROCESSSCHEDULEID, @DONATION_RECONCILE_BUSINESSPROCESSID, @SCHEDULEID, @CHANGEAGENTID, @CHANGEAGENTID, @DATE, @DATE);
end
--enable or disable schedule depending on specific product flag
set @PRODUCTFLAGID = 'F238E8FE-06AE-4FDC-BEAF-FDF6637E1982'; -- Full CMS only
set @CORRECTPRODUCTINSTALLED = 0;
exec dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT;
update dbo.SCHEDULE set ENABLED = @CORRECTPRODUCTINSTALLED where ID = @SCHEDULEID;
end
-----------------------------------------------------------------------------------------------------------------
--Charge unprocessed or incompleted transaction from CMSTransactions ------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------
declare @CMSTRANSACTIONSCHEDULEID uniqueidentifier = 'A32A3A10-3740-4D10-A548-1CC84708FD27'
set @BUSINESSPROCESSSCHEDULEID ='8CA823B1-E29A-4273-B04A-0C021BCE3C28'
set @SPECID = 'DD3D6872-8993-4F39-BF46-450E601CED2A'; -- CMS Charge failed donation Transaction Business Process
if not exists (select ID from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @SPECID)
begin
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID, BUSINESSPROCESSCATALOGID, GRANTORDENY, ADDEDBYID, CHANGEDBYID)
values (@SYSTEMROLEID, @SPECID, 1, @CHANGEAGENTID, @CHANGEAGENTID);
end
if not exists(select ID from dbo.SCHEDULE where ID = @CMSTRANSACTIONSCHEDULEID)
begin
insert into dbo.SCHEDULE
(ID,[ENABLED],TYPECODE,FREQUENCY,SUBDAYFREQUENCYTYPECODE,[TIME],SUBDAYFREQUENCY,DAYSOFWEEK,[DAYOFMONTH],WEEKCODE,DAYOFWEEKCODE,MONTHCODE,STARTDATE,SUBDAYSTARTINGTIME,SUBDAYENDINGTIME)
VALUES
(@CMSTRANSACTIONSCHEDULEID,1,4,1,4,null,15,0,0,0,0,0,'2015-01-01','00:00:00','23:59:59');
end
if not exists(select top 1 1 from BUSINESSPROCESSSCHEDULE where SCHEDULEID=@CMSTRANSACTIONSCHEDULEID)
begin
insert into dbo.BUSINESSPROCESSSCHEDULE (ID ,BUSINESSPROCESSID ,SCHEDULEID , ADDEDBYID , CHANGEDBYID, DATEADDED ,DATECHANGED)
values
(@BUSINESSPROCESSSCHEDULEID,@SPECID,@CMSTRANSACTIONSCHEDULEID,@CHANGEAGENTID,@CHANGEAGENTID, getdate(), getdate());
end
--------------------------------------------------------------------------------------------------------------------
--Charge unprocessed or incompleted transaction from CMSTransactions End ------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------