USP_CONSTITUENCYDATERANGE_REFRESHUSERDEFINEDCONSTITUENCIES
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CONSTITUENCYREFRESHPROCESSID | uniqueidentifier | IN | |
| @RECORDCOUNT | int | INOUT |
Definition
Copy
CREATE procedure dbo.USP_CONSTITUENCYDATERANGE_REFRESHUSERDEFINEDCONSTITUENCIES
(
@CONSTITUENTID uniqueidentifier = null,
@CONSTITUENCYREFRESHPROCESSID uniqueidentifier = null,
@RECORDCOUNT int = 0 output
)
as
begin
set nocount on;
set @RECORDCOUNT = 0;
declare @CURRENTDATE datetime = getdate();
begin try
if @CONSTITUENTID is not null and @CONSTITUENCYREFRESHPROCESSID is not null
begin
raiserror('ERR_REFRESH_CONSTITUENTANDSELECTION : A constituency refresh cannot be run with both a specific constituent and a selection.', 13, 1);
end
if @CONSTITUENCYREFRESHPROCESSID is null and @CONSTITUENTID is null
begin
insert into dbo.CONSTITUENCYDATERANGE
(
ID,
CONSTITUENTID,
CONSTITUENCYDEFINITIONID,
DATEFROM,
DATETO,
CONSTITUENCYRECORDIDENTIFIER,
REFRESHDATE,
DATEADDED,
DATECHANGED,
ADDEDBYAPPLICATIONNAME,
ADDEDBYUSERNAME,
CHANGEDBYAPPLICATIONNAME,
CHANGEDBYUSERNAME,
CONSTITUENCYTS
)
select
newid(),
CONSTITUENCY.CONSTITUENTID,
CONSTITUENCY.CONSTITUENCYCODEID,
CONSTITUENCY.DATEFROM,
CONSTITUENCY.DATETO,
CONSTITUENCY.ID,
@CURRENTDATE,
CONSTITUENCY.DATEADDED,
CONSTITUENCY.DATECHANGED,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
CONSTITUENCY.TS
from
dbo.CONSTITUENCY
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCY.CONSTITUENCYCODEID
left join dbo.CONSTITUENCYDATERANGE
on CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER = CONSTITUENCY.ID
inner join dbo.CHANGEAGENT as [ADDEDBY]
on ADDEDBY.ID = CONSTITUENCY.ADDEDBYID
inner join dbo.CHANGEAGENT as [CHANGEDBY]
on CHANGEDBY.ID = CONSTITUENCY.CHANGEDBYID
where
CONSTITUENCYDEFINITION.ISACTIVE = 1
-- IMPLEMENTATIONTYPECODE of 0 specifies no custom implementation SP or CLR
and CONSTITUENCYDEFINITION.IMPLEMENTATIONTYPECODE = 0
and CONSTITUENCYDATERANGE.ID is null
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
update dbo.CONSTITUENCYDATERANGE
set
CONSTITUENCYDATERANGE.CONSTITUENTID = CONSTITUENCY.CONSTITUENTID,
CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = CONSTITUENCY.CONSTITUENCYCODEID,
CONSTITUENCYDATERANGE.DATEFROM = CONSTITUENCY.DATEFROM,
CONSTITUENCYDATERANGE.DATETO = CONSTITUENCY.DATETO,
CONSTITUENCYDATERANGE.DATECHANGED = CONSTITUENCY.DATECHANGED,
CONSTITUENCYDATERANGE.CHANGEDBYAPPLICATIONNAME = CHANGEDBY.APPLICATIONNAME,
CONSTITUENCYDATERANGE.CHANGEDBYUSERNAME = CHANGEDBY.USERNAME,
CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE,
CONSTITUENCYDATERANGE.CONSTITUENCYTS = CONSTITUENCY.TS
from
dbo.CONSTITUENCYDATERANGE
inner join dbo.CONSTITUENCY
on CONSTITUENCY.ID = CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCY.CONSTITUENCYCODEID
inner join dbo.CHANGEAGENT as [CHANGEDBY]
on CHANGEDBY.ID = CONSTITUENCY.CHANGEDBYID
where
CONSTITUENCYDEFINITION.ISACTIVE = 1
-- IMPLEMENTATIONTYPECODE of 0 specifies no custom implementation SP or CLR
and CONSTITUENCYDEFINITION.IMPLEMENTATIONTYPECODE = 0
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
delete dbo.CONSTITUENCYDATERANGE
from
dbo.CONSTITUENCYDATERANGE
left join dbo.CONSTITUENCY
on CONSTITUENCY.ID = CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID
where
CONSTITUENCYDEFINITION.ISACTIVE = 1
-- IMPLEMENTATIONTYPECODE of 0 specifies no custom implementation SP or CLR
and CONSTITUENCYDEFINITION.IMPLEMENTATIONTYPECODE = 0
and CONSTITUENCYDEFINITION.ISSYSTEM = 0
and CONSTITUENCY.ID is null
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end
else if @CONSTITUENCYREFRESHPROCESSID is null
begin
insert into dbo.CONSTITUENCYDATERANGE
(
ID,
CONSTITUENTID,
CONSTITUENCYDEFINITIONID,
DATEFROM,
DATETO,
CONSTITUENCYRECORDIDENTIFIER,
REFRESHDATE,
DATEADDED,
DATECHANGED,
ADDEDBYAPPLICATIONNAME,
ADDEDBYUSERNAME,
CHANGEDBYAPPLICATIONNAME,
CHANGEDBYUSERNAME,
CONSTITUENCYTS
)
select
newid(),
CONSTITUENCY.CONSTITUENTID,
CONSTITUENCY.CONSTITUENCYCODEID,
CONSTITUENCY.DATEFROM,
CONSTITUENCY.DATETO,
CONSTITUENCY.ID,
@CURRENTDATE,
CONSTITUENCY.DATEADDED,
CONSTITUENCY.DATECHANGED,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
CONSTITUENCY.TS
from
dbo.CONSTITUENCY
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCY.CONSTITUENCYCODEID
left join dbo.CONSTITUENCYDATERANGE
on CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER = CONSTITUENCY.ID
inner join dbo.CHANGEAGENT as [ADDEDBY]
on ADDEDBY.ID = CONSTITUENCY.ADDEDBYID
inner join dbo.CHANGEAGENT as [CHANGEDBY]
on CHANGEDBY.ID = CONSTITUENCY.CHANGEDBYID
where
CONSTITUENCYDEFINITION.ISACTIVE = 1
-- IMPLEMENTATIONTYPECODE of 0 specifies no custom implementation SP or CLR
and CONSTITUENCYDEFINITION.IMPLEMENTATIONTYPECODE = 0
and CONSTITUENCYDATERANGE.ID is null
and CONSTITUENCY.CONSTITUENTID = @CONSTITUENTID
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
update dbo.CONSTITUENCYDATERANGE
set
CONSTITUENCYDATERANGE.CONSTITUENTID = CONSTITUENCY.CONSTITUENTID,
CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = CONSTITUENCY.CONSTITUENCYCODEID,
CONSTITUENCYDATERANGE.DATEFROM = CONSTITUENCY.DATEFROM,
CONSTITUENCYDATERANGE.DATETO = CONSTITUENCY.DATETO,
CONSTITUENCYDATERANGE.DATECHANGED = CONSTITUENCY.DATECHANGED,
CONSTITUENCYDATERANGE.CHANGEDBYAPPLICATIONNAME = CHANGEDBY.APPLICATIONNAME,
CONSTITUENCYDATERANGE.CHANGEDBYUSERNAME = CHANGEDBY.USERNAME,
CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE,
CONSTITUENCYDATERANGE.CONSTITUENCYTS = CONSTITUENCY.TS
from
dbo.CONSTITUENCYDATERANGE
inner join dbo.CONSTITUENCY
on CONSTITUENCY.ID = CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCY.CONSTITUENCYCODEID
inner join dbo.CHANGEAGENT as [CHANGEDBY]
on CHANGEDBY.ID = CONSTITUENCY.CHANGEDBYID
where
CONSTITUENCYDEFINITION.ISACTIVE = 1
-- IMPLEMENTATIONTYPECODE of 0 specifies no custom implementation SP or CLR
and CONSTITUENCYDEFINITION.IMPLEMENTATIONTYPECODE = 0
and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
delete dbo.CONSTITUENCYDATERANGE
from
dbo.CONSTITUENCYDATERANGE
left join dbo.CONSTITUENCY
on CONSTITUENCY.ID = CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID
where
CONSTITUENCYDEFINITION.ISACTIVE = 1
-- IMPLEMENTATIONTYPECODE of 0 specifies no custom implementation SP or CLR
and CONSTITUENCYDEFINITION.IMPLEMENTATIONTYPECODE = 0
and CONSTITUENCYDEFINITION.ISSYSTEM = 0
and CONSTITUENCY.ID is null
and CONSTITUENCYDATERANGE.CONSTITUENTID = @CONSTITUENTID
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end
else
begin
insert into dbo.CONSTITUENCYDATERANGE
(
ID,
CONSTITUENTID,
CONSTITUENCYDEFINITIONID,
DATEFROM,
DATETO,
CONSTITUENCYRECORDIDENTIFIER,
REFRESHDATE,
DATEADDED,
DATECHANGED,
ADDEDBYAPPLICATIONNAME,
ADDEDBYUSERNAME,
CHANGEDBYAPPLICATIONNAME,
CHANGEDBYUSERNAME,
CONSTITUENCYTS
)
select
newid(),
CONSTITUENCY.CONSTITUENTID,
CONSTITUENCY.CONSTITUENCYCODEID,
CONSTITUENCY.DATEFROM,
CONSTITUENCY.DATETO,
CONSTITUENCY.ID,
@CURRENTDATE,
CONSTITUENCY.DATEADDED,
CONSTITUENCY.DATECHANGED,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
CONSTITUENCY.TS
from
dbo.CONSTITUENCY
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCY.CONSTITUENCYCODEID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = CONSTITUENCY.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
left join dbo.CONSTITUENCYDATERANGE
on CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER = CONSTITUENCY.ID
inner join dbo.CHANGEAGENT as [ADDEDBY]
on ADDEDBY.ID = CONSTITUENCY.ADDEDBYID
inner join dbo.CHANGEAGENT as [CHANGEDBY]
on CHANGEDBY.ID = CONSTITUENCY.CHANGEDBYID
where
CONSTITUENCYDEFINITION.ISACTIVE = 1
-- IMPLEMENTATIONTYPECODE of 0 specifies no custom implementation SP or CLR
and CONSTITUENCYDEFINITION.IMPLEMENTATIONTYPECODE = 0
and CONSTITUENCYDATERANGE.ID is null
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
update dbo.CONSTITUENCYDATERANGE
set
CONSTITUENCYDATERANGE.CONSTITUENTID = CONSTITUENCY.CONSTITUENTID,
CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID = CONSTITUENCY.CONSTITUENCYCODEID,
CONSTITUENCYDATERANGE.DATEFROM = CONSTITUENCY.DATEFROM,
CONSTITUENCYDATERANGE.DATETO = CONSTITUENCY.DATETO,
CONSTITUENCYDATERANGE.DATECHANGED = CONSTITUENCY.DATECHANGED,
CONSTITUENCYDATERANGE.CHANGEDBYAPPLICATIONNAME = CHANGEDBY.APPLICATIONNAME,
CONSTITUENCYDATERANGE.CHANGEDBYUSERNAME = CHANGEDBY.USERNAME,
CONSTITUENCYDATERANGE.REFRESHDATE = @CURRENTDATE,
CONSTITUENCYDATERANGE.CONSTITUENCYTS = CONSTITUENCY.TS
from
dbo.CONSTITUENCYDATERANGE
inner join dbo.CONSTITUENCY
on CONSTITUENCY.ID = CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCY.CONSTITUENCYCODEID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = CONSTITUENCY.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
inner join dbo.CHANGEAGENT as [CHANGEDBY]
on CHANGEDBY.ID = CONSTITUENCY.CHANGEDBYID
where
CONSTITUENCYDEFINITION.ISACTIVE = 1
-- IMPLEMENTATIONTYPECODE of 0 specifies no custom implementation SP or CLR
and CONSTITUENCYDEFINITION.IMPLEMENTATIONTYPECODE = 0
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
delete dbo.CONSTITUENCYDATERANGE
from
dbo.CONSTITUENCYDATERANGE
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYDATERANGE.CONSTITUENCYDEFINITIONID
inner join dbo.CONSTITUENCYREFRESHPROCESSSELECTIONRESULT CONSTITUENTS
on CONSTITUENTS.CONSTITUENTID = CONSTITUENCYDATERANGE.CONSTITUENTID
and CONSTITUENTS.CONSTITUENCYREFRESHPROCESSID = @CONSTITUENCYREFRESHPROCESSID
left join dbo.CONSTITUENCY
on CONSTITUENCY.ID = CONSTITUENCYDATERANGE.CONSTITUENCYRECORDIDENTIFIER
where
CONSTITUENCYDEFINITION.ISACTIVE = 1
-- IMPLEMENTATIONTYPECODE of 0 specifies no custom implementation SP or CLR
and CONSTITUENCYDEFINITION.IMPLEMENTATIONTYPECODE = 0
and CONSTITUENCYDEFINITION.ISSYSTEM = 0
and CONSTITUENCY.ID is null
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end