USP_DATAFORMTEMPLATE_ADD_BATCHREVENUESEPAMANDATE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHREVENUESEPAMANDATE
(
    @ID uniqueidentifier = null output,
    @CONSTITUENTACCOUNTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SIGNATUREDATE datetime = '',
    @LOOKUPID nvarchar(35) = '',
    @OVERRIDELOOKUPID bit = 0,
    @TYPECODE tinyint = 0,
    @STATUSCODE tinyint = 0
)
as

set nocount on;

if @ID is null
    set @ID = newid();

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();

begin try

    declare @BATCHREVENUECONSTITUENTACCOUNTID uniqueidentifier = null;
    declare @BATCHSPONSORSHIPCONSTITUENTACCOUNTID uniqueidentifier = null;

    if exists(select 1 from dbo.BATCHREVENUECONSTITUENTACCOUNT where BATCHREVENUECONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID)
    begin
        set @BATCHREVENUECONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID;
        set @CONSTITUENTACCOUNTID = null;
    end

    if exists(select 1 from dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT where BATCHSPONSORSHIPCONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID)
    begin
        set @BATCHSPONSORSHIPCONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID;
        set @CONSTITUENTACCOUNTID = null;
    end

    if @BATCHREVENUECONSTITUENTACCOUNTID is null and @BATCHSPONSORSHIPCONSTITUENTACCOUNTID is null
    begin
        if not exists(select 1 from dbo.CONSTITUENTACCOUNT where CONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID)
        begin
            raiserror('BBERR_CONSTITUENTACCOUNT_NOTFOUND', 13, 1);
        end
    end

    --Make sure that the mandate is getting added to an account that is tied to a financial institution that
    --has a SEPA banking system
    if not exists
    (
        select 1 
        from 
            dbo.CONSTITUENTACCOUNT
            inner join dbo.FINANCIALINSTITUTION on FINANCIALINSTITUTION.ID = CONSTITUENTACCOUNT.FINANCIALINSTITUTIONID
            inner join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = FINANCIALINSTITUTION.BANKINGSYSTEMID
        where 
            CONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID
            and BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-SEPA'

        union all

        select 1
        from
            dbo.BATCHREVENUECONSTITUENTACCOUNT
            inner join dbo.FINANCIALINSTITUTION on FINANCIALINSTITUTION.ID = BATCHREVENUECONSTITUENTACCOUNT.FINANCIALINSTITUTIONID
            inner join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = FINANCIALINSTITUTION.BANKINGSYSTEMID
        where 
            BATCHREVENUECONSTITUENTACCOUNT.ID = @BATCHREVENUECONSTITUENTACCOUNTID
            and BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-SEPA'

        union all

        select 1
        from
            dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT
            inner join dbo.FINANCIALINSTITUTION on FINANCIALINSTITUTION.ID = BATCHSPONSORSHIPCONSTITUENTACCOUNT.FINANCIALINSTITUTIONID
            inner join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = FINANCIALINSTITUTION.BANKINGSYSTEMID
        where 
            BATCHSPONSORSHIPCONSTITUENTACCOUNT.ID = @BATCHSPONSORSHIPCONSTITUENTACCOUNTID
            and BANKINGSYSTEM.CONDITIONSETTINGNAME = 'BankingSystem-SEPA'
    )
    begin
        raiserror('BBERR_SEPAMANDATE_SEPABANKINGSYSTEMFORACCOUNT', 13, 1);
    end

    --Setup the override for the mandate lookup ID
    declare @CUSTOMIDENTIFIER nvarchar(35) = '';
    if @OVERRIDELOOKUPID = 1
    begin
        if @LOOKUPID is not null and @LOOKUPID <> ''
        begin
            set @CUSTOMIDENTIFIER = @LOOKUPID;
        end
        else
        begin
            raiserror('BBERR_MANDATELOOKUPID_CANNOTBENULLOREMPTY', 13, 1);
        end
    end

    --We insert the constituent account ID into different columns base on whether the account already existed vs. newly added
    insert into dbo.SEPAMANDATE
    (
        ID, 
        CONSTITUENTACCOUNTID,
        BATCHREVENUECONSTITUENTACCOUNTID,
        BATCHSPONSORSHIPCONSTITUENTACCOUNTID,
        CUSTOMIDENTIFIER, 
        SIGNATUREDATE, 
        TYPECODE,
        STATUSCODE,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
    )
    values
    (
        @ID, 
        @CONSTITUENTACCOUNTID,
        @BATCHREVENUECONSTITUENTACCOUNTID,
        @BATCHSPONSORSHIPCONSTITUENTACCOUNTID,
        @CUSTOMIDENTIFIER, 
        @SIGNATUREDATE, 
        @TYPECODE, 
        @STATUSCODE,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE
    );

end try

begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;