USP_SPONSORSHIPBATCH_GENERATECONSTITUENTACCOUNTS

            CREATE procedure dbo.USP_SPONSORSHIPBATCH_GENERATECONSTITUENTACCOUNTS
            (
                @BATCHSPONSORSHIPCONSTITUENTID            uniqueidentifier,
                @CONSTITUENTID                        uniqueidentifier,
                @CHANGEAGENTID                        uniqueidentifier = null,
                @BATCHSPONSORSHIPCONSTITUENTACCOUNTID    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 BATCHSPONSORSHIPCONSTITUENTACCOUNT records

                -- that correspond to this constituent


                -- Using @CONSTITUENTACCOUNTIDS so that we can update the dbo.BATCHSPONSORSHIP table

                -- to point to the generated CONSTITUENTACCOUNT records

                declare @CONSTITUENTACCOUNTIDS table
                (
                    BATCHSPONSORSHIPCONSTITUENTACCOUNTID uniqueidentifier,
                    CONSTITUENTACCOUNTID uniqueidentifier
                )

                insert into @CONSTITUENTACCOUNTIDS
                (
                    BATCHSPONSORSHIPCONSTITUENTACCOUNTID,
                    CONSTITUENTACCOUNTID
                )
                select
                    ID,
                    newid()
                from dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT
                where
                    CONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID

                insert into dbo.CONSTITUENTACCOUNT
                (
                    ID,
                    CONSTITUENTID,
                    FINANCIALINSTITUTIONID,
                    ACCOUNTNUMBER,
                    ACCOUNTNUMBERINDEX,
                    ACCOUNTTYPECODE,
                    EFTSTATUSCODE,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED,
                    ACCOUNTNAME
                )
                select
                    CA.CONSTITUENTACCOUNTID,
                    @CONSTITUENTID,
                    BRCA.FINANCIALINSTITUTIONID,
                    BRCA.ACCOUNTNUMBER,
                    dbo.UFN_GET_MAC_FOR_TEXT(BRCA.ACCOUNTNUMBER, 'dbo.CONSTITUENTACCOUNT'),
                    BRCA.ACCOUNTTYPECODE,
                    BRCA.EFTSTATUSCODE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE,
                    BRCA.ACCOUNTNAME
                from dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT BRCA
                inner join @CONSTITUENTACCOUNTIDS CA on BRCA.ID = CA.BATCHSPONSORSHIPCONSTITUENTACCOUNTID
                where
                    CONSTITUENTID = @BATCHSPONSORSHIPCONSTITUENTID

                update dbo.SEPAMANDATE
                    set
                        CONSTITUENTACCOUNTID = CA.CONSTITUENTACCOUNTID,
                        BATCHSPONSORSHIPCONSTITUENTACCOUNTID = null,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                from dbo.SEPAMANDATE
                inner join @CONSTITUENTACCOUNTIDS CA on SEPAMANDATE.BATCHSPONSORSHIPCONSTITUENTACCOUNTID = CA.BATCHSPONSORSHIPCONSTITUENTACCOUNTID;

                update dbo.BATCHSPONSORSHIP
                set
                    CONSTITUENTACCOUNTID = CA.CONSTITUENTACCOUNTID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from dbo.BATCHSPONSORSHIP BR
                inner join @CONSTITUENTACCOUNTIDS CA on BR.CONSTITUENTACCOUNTID = CA.BATCHSPONSORSHIPCONSTITUENTACCOUNTID

                select
                    @CONSTITUENTACCOUNTID = CONSTITUENTACCOUNTID
                from @CONSTITUENTACCOUNTIDS
                where BATCHSPONSORSHIPCONSTITUENTACCOUNTID = @BATCHSPONSORSHIPCONSTITUENTACCOUNTID