USP_DATAFORMTEMPLATE_ADD_SEPAMANDATE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SEPAMANDATE
(
    @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

    --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'
    )
    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

    --Make the insert into the table
    insert into dbo.SEPAMANDATE
    (
        ID, 
        CONSTITUENTACCOUNTID, 
        CUSTOMIDENTIFIER, 
        SIGNATUREDATE, 
        TYPECODE,
        STATUSCODE,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED
    )
    values
    (
        @ID, 
        @CONSTITUENTACCOUNTID, 
        @CUSTOMIDENTIFIER, 
        @SIGNATUREDATE, 
        @TYPECODE,
        @STATUSCODE,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE
    );

end try

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

return 0;