USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
Generates CONSTITUENTACCOUNT records for BATCHREVENUECONSTITUENTACCOUNT belonging to the specified constituent.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @BATCHREVENUECONSTITUENTID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @BATCHREVENUECONSTITUENTACCOUNTID | uniqueidentifier | IN | |
| @CONSTITUENTACCOUNTID | uniqueidentifier | INOUT |
Definition
Copy
CREATE procedure dbo.USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
(
@BATCHREVENUECONSTITUENTID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@BATCHREVENUECONSTITUENTACCOUNTID uniqueidentifier = null,
@CONSTITUENTACCOUNTID uniqueidentifier = null output
)
as
set nocount on
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
-- Create CONSTITUENTACCOUNT records for any BATCHREVENUECONSTITUENTACCOUNT records
-- that correspond to this constituent
-- Using @CONSTITUENTACCOUNTIDS so that we can update the dbo.BATCHREVENUE table
-- to point to the generated CONSTITUENTACCOUNT records
declare @CONSTITUENTACCOUNTIDS table
(
BATCHREVENUECONSTITUENTACCOUNTID uniqueidentifier
)
-- Open the symmetric key for decryption
exec dbo.USP_GET_KEY_ACCESS;
insert into @CONSTITUENTACCOUNTIDS
(
BATCHREVENUECONSTITUENTACCOUNTID
)
select
BRCA.ID
from
dbo.BATCHREVENUECONSTITUENTACCOUNT BRCA
where
BRCA.CONSTITUENTID = @BATCHREVENUECONSTITUENTID
declare @CURRENTBATCHREVENUECONSTITUENTACCOUNTID uniqueidentifier
declare @FINANCIALINSTITUTIONID uniqueidentifier
declare @BANKINGSYSTEMID uniqueidentifier
declare @ROUTINGNUMBER nvarchar(9)
declare @SORTCODE nvarchar(6)
declare @BIC nvarchar(11)
declare @BANKCODE nvarchar(25)
declare @CURRENTCONSTITUENTID uniqueidentifier
declare @ACCOUNTNUMBER nvarchar(max)
declare @FINANCIALINSTITUTIONNAME nvarchar(100)
declare @BRANCHNAME nvarchar(100)
declare @COUNTRYID uniqueidentifier
declare @EXISTINGCONSTITUENTACCOUNTID uniqueidentifier
declare @EXISTINGFINANCIALINSTITUTIONID uniqueidentifier
declare CONSTITUENTACCOUNT_CURSOR cursor for
select
BATCHREVENUECONSTITUENTACCOUNTID
from
@CONSTITUENTACCOUNTIDS
open CONSTITUENTACCOUNT_CURSOR
fetch next from CONSTITUENTACCOUNT_CURSOR into @CURRENTBATCHREVENUECONSTITUENTACCOUNTID
while @@FETCH_STATUS = 0
begin
set @EXISTINGCONSTITUENTACCOUNTID = null
set @EXISTINGFINANCIALINSTITUTIONID = null
select
@FINANCIALINSTITUTIONID = BATCHREVENUECONSTITUENTACCOUNT.FINANCIALINSTITUTIONID,
@CURRENTCONSTITUENTID = BATCHREVENUECONSTITUENTACCOUNT.CONSTITUENTID,
@ACCOUNTNUMBER = coalesce(convert(nvarchar(50), DecryptByKey(BATCHREVENUECONSTITUENTACCOUNT.ACCOUNTNUMBER)),'')
from
dbo.BATCHREVENUECONSTITUENTACCOUNT
where
BATCHREVENUECONSTITUENTACCOUNT.ID = @CURRENTBATCHREVENUECONSTITUENTACCOUNTID
if (
select
count(FINANCIALINSTITUTION.ID)
from
dbo.FINANCIALINSTITUTION
where
FINANCIALINSTITUTION.ID = @FINANCIALINSTITUTIONID
) = 1
begin
set @EXISTINGFINANCIALINSTITUTIONID = @FINANCIALINSTITUTIONID
if (
select
count(ID)
from
dbo.CONSTITUENTACCOUNT
where
--modified to use @CONSTITUENTID in OR configuration -ken getch 1/20/2015
(CONSTITUENTACCOUNT.CONSTITUENTID = @CURRENTCONSTITUENTID OR
CONSTITUENTACCOUNT.CONSTITUENTID = @CONSTITUENTID)
and coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),'') = @ACCOUNTNUMBER
and CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = @FINANCIALINSTITUTIONID
) > 0
begin
select
@EXISTINGCONSTITUENTACCOUNTID = CONSTITUENTACCOUNT.ID
from
dbo.CONSTITUENTACCOUNT
where
--modified to use @CONSTITUENTID in OR configuration -ken getch 1/20/2015
(CONSTITUENTACCOUNT.CONSTITUENTID = @CURRENTCONSTITUENTID OR
CONSTITUENTACCOUNT.CONSTITUENTID = @CONSTITUENTID)
and coalesce(convert(nvarchar(50), DecryptByKey(CONSTITUENTACCOUNT.ACCOUNTNUMBER)),'') = @ACCOUNTNUMBER
and CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID = @FINANCIALINSTITUTIONID
end
end
else if (
select
count(ID)
from
dbo.BATCHREVENUEFINANCIALINSTITUTION
where
BATCHREVENUEFINANCIALINSTITUTION.ID = @FINANCIALINSTITUTIONID
) = 1
begin
select
@BANKINGSYSTEMID = BATCHREVENUEFINANCIALINSTITUTION.BANKINGSYSTEMID,
@ROUTINGNUMBER = BATCHREVENUEFINANCIALINSTITUTION.ROUTINGNUMBER,
@SORTCODE = BATCHREVENUEFINANCIALINSTITUTION.SORTCODE,
@BIC = BATCHREVENUEFINANCIALINSTITUTION.BIC,
@BANKCODE = BATCHREVENUEFINANCIALINSTITUTION.BANKCODE,
@FINANCIALINSTITUTIONNAME = BATCHREVENUEFINANCIALINSTITUTION.FINANCIALINSTITUTION,
@BRANCHNAME = BATCHREVENUEFINANCIALINSTITUTION.BRANCHNAME,
@COUNTRYID = BATCHREVENUEFINANCIALINSTITUTION.COUNTRYID
from
dbo.BATCHREVENUEFINANCIALINSTITUTION
where
BATCHREVENUEFINANCIALINSTITUTION.ID = @FINANCIALINSTITUTIONID
exec dbo.USP_REVENUEBATCH_GETCONSTITUENTACCOUNT
@CONSTITUENTACCOUNTID = @EXISTINGCONSTITUENTACCOUNTID output,
@FINANCIALINSTITUTIONID = @EXISTINGFINANCIALINSTITUTIONID output,
@BANKINGSYSTEMID = @BANKINGSYSTEMID output,
@ROUTINGNUMBER = @ROUTINGNUMBER,
@SORTCODE = @SORTCODE,
@BIC = @BIC,
@BANKCODE = @BANKCODE,
@CONSTITUENTID = @CONSTITUENTID,
@ACCOUNTNUMBER = @ACCOUNTNUMBER,
@FINANCIALINSTITUTIONNAME = @FINANCIALINSTITUTIONNAME output,
@BRANCHNAME = @BRANCHNAME
end
if @EXISTINGCONSTITUENTACCOUNTID is null
begin
if @EXISTINGFINANCIALINSTITUTIONID is null
begin
set @EXISTINGFINANCIALINSTITUTIONID = newid()
insert into dbo.FINANCIALINSTITUTION
(
ID,
FINANCIALINSTITUTION,
BRANCHNAME,
ROUTINGNUMBER,
SORTCODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
BANKINGSYSTEMID,
BIC,
BANKCODE,
COUNTRYID
)
values
(
@EXISTINGFINANCIALINSTITUTIONID,
@FINANCIALINSTITUTIONNAME,
@BRANCHNAME,
@ROUTINGNUMBER,
@SORTCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@BANKINGSYSTEMID,
@BIC,
@BANKCODE,
@COUNTRYID
)
end
set @EXISTINGCONSTITUENTACCOUNTID = newid()
insert into dbo.CONSTITUENTACCOUNT
(
ID,
CONSTITUENTID,
FINANCIALINSTITUTIONID,
ACCOUNTNUMBER,
ACCOUNTNUMBERINDEX,
ACCOUNTTYPECODE,
EFTSTATUSCODE,
ACCOUNTNAME,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
@EXISTINGCONSTITUENTACCOUNTID,
@CONSTITUENTID,
@EXISTINGFINANCIALINSTITUTIONID,
BRCA.ACCOUNTNUMBER,
dbo.UFN_GET_MAC_FOR_TEXT(BRCA.ACCOUNTNUMBER, 'dbo.CONSTITUENTACCOUNT'),
BRCA.ACCOUNTTYPECODE,
BRCA.EFTSTATUSCODE,
BRCA.ACCOUNTNAME,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.BATCHREVENUECONSTITUENTACCOUNT BRCA
where
BRCA.ID = @CURRENTBATCHREVENUECONSTITUENTACCOUNTID
end
if @CURRENTBATCHREVENUECONSTITUENTACCOUNTID = @BATCHREVENUECONSTITUENTACCOUNTID
begin
set @CONSTITUENTACCOUNTID = @EXISTINGCONSTITUENTACCOUNTID
end
update
dbo.BATCHREVENUE
set
CONSTITUENTACCOUNTID = @EXISTINGCONSTITUENTACCOUNTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.BATCHREVENUE
where
BATCHREVENUE.CONSTITUENTACCOUNTID = @CURRENTBATCHREVENUECONSTITUENTACCOUNTID
update dbo.SEPAMANDATE
set
CONSTITUENTACCOUNTID = @EXISTINGCONSTITUENTACCOUNTID,
BATCHREVENUECONSTITUENTACCOUNTID = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from dbo.SEPAMANDATE
where SEPAMANDATE.BATCHREVENUECONSTITUENTACCOUNTID = @CURRENTBATCHREVENUECONSTITUENTACCOUNTID;
declare @CURRENTBATCHREVENUEFINANCIALINSTITUTIONID uniqueidentifier
select
@CURRENTBATCHREVENUEFINANCIALINSTITUTIONID = BATCHREVENUECONSTITUENTACCOUNT.FINANCIALINSTITUTIONID
from
dbo.BATCHREVENUECONSTITUENTACCOUNT
where
BATCHREVENUECONSTITUENTACCOUNT.ID = @CURRENTBATCHREVENUECONSTITUENTACCOUNTID
exec dbo.USP_BATCHREVENUECONSTITUENTACCOUNT_DELETEBYID_WITHCHANGEAGENTID @CURRENTBATCHREVENUECONSTITUENTACCOUNTID, @CHANGEAGENTID;
exec dbo.USP_BATCHREVENUEFINANCIALINSTITUTION_DELETEBYID_WITHCHANGEAGENTID @CURRENTBATCHREVENUEFINANCIALINSTITUTIONID, @CHANGEAGENTID;
fetch next from CONSTITUENTACCOUNT_CURSOR into @CURRENTBATCHREVENUECONSTITUENTACCOUNTID
end
close CONSTITUENTACCOUNT_CURSOR
deallocate CONSTITUENTACCOUNT_CURSOR
close symmetric key sym_BBInfinity;