USP_SIMPLEDATALIST_SEPAMANDATEFORBATCHREVENUECONSTITUENTACCOUNT

create procedure dbo.USP_SIMPLEDATALIST_SEPAMANDATEFORBATCHREVENUECONSTITUENTACCOUNT
(
    @CONSTITUENTACCOUNTID uniqueidentifier
)
as
    --Include constituent account mandates as well since batch uses these

    --for existing constituents.

    select 
        SEPAMANDATE.ID as VALUE, 
        SEPAMANDATE.LOOKUPID as LABEL
    from 
        dbo.SEPAMANDATE
        inner join dbo.CONSTITUENTACCOUNT on CONSTITUENTACCOUNT.ID = SEPAMANDATE.CONSTITUENTACCOUNTID
    where
        CONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID

    union all

    --Include batch revenue constituent account mandate since this is how

    --they are stored for constituents being added in batch.

    select 
        SEPAMANDATE.ID as VALUE, 
        SEPAMANDATE.LOOKUPID as LABEL
    from 
        dbo.SEPAMANDATE
        inner join dbo.BATCHREVENUECONSTITUENTACCOUNT on BATCHREVENUECONSTITUENTACCOUNT.ID = SEPAMANDATE.BATCHREVENUECONSTITUENTACCOUNTID
    where
        BATCHREVENUECONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID
    order by
        SEPAMANDATE.LOOKUPID;