USP_SIMPLEDATALIST_SEPAMANDATEFORCONSTITUENTACCOUNT

CREATE procedure dbo.USP_SIMPLEDATALIST_SEPAMANDATEFORCONSTITUENTACCOUNT
(
    @CONSTITUENTACCOUNTID uniqueidentifier,
    @SEPAMANDATEID uniqueidentifier
)
as
    select
        SEPAMANDATE.ID,
        SEPAMANDATE.LOOKUPID,
        SEPAMANDATE.TYPECODE,
        SEPAMANDATE.STATUSCODE,
        SEPAMANDATE.SIGNATUREDATE
    from dbo.SEPAMANDATE
        inner join dbo.CONSTITUENTACCOUNT on CONSTITUENTACCOUNT.ID = SEPAMANDATE.CONSTITUENTACCOUNTID
    where
        CONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID and
        (
            (
                (
                    -- Recurrent and pending signature/active

                    SEPAMANDATE.TYPECODE = 1 and
                    SEPAMANDATE.STATUSCODE in (0,1)
                )
                or
                (
                    -- One-off and pending signature/active and no payment made

                    SEPAMANDATE.TYPECODE = 0 and
                    SEPAMANDATE.STATUSCODE in (0,1) and
                    SEPAMANDATE.PAYMENTCOUNT = 0
                )
            ) or
            -- Allow the just added/loaded mandate to be present in the list

            SEPAMANDATE.ID = @SEPAMANDATEID
        )

    union all

    --Include batch revenue constituent account mandates

    select
        SEPAMANDATE.ID,
        SEPAMANDATE.LOOKUPID,
        SEPAMANDATE.TYPECODE,
        SEPAMANDATE.STATUSCODE,
        SEPAMANDATE.SIGNATUREDATE
    from dbo.SEPAMANDATE
        inner join dbo.BATCHREVENUECONSTITUENTACCOUNT on BATCHREVENUECONSTITUENTACCOUNT.ID = SEPAMANDATE.BATCHREVENUECONSTITUENTACCOUNTID
    where
        BATCHREVENUECONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID and
        (
            (
                (
                    -- Recurrent and pending signature/active

                    SEPAMANDATE.TYPECODE = 1 and
                    SEPAMANDATE.STATUSCODE in (0,1)
                )
                or
                (
                    -- One-off and pending signature/active and no payment made

                    SEPAMANDATE.TYPECODE = 0 and
                    SEPAMANDATE.STATUSCODE in (0,1) and
                    SEPAMANDATE.PAYMENTCOUNT = 0
                )
            ) or
            -- Allow the just added/loaded mandate to be present in the list

            SEPAMANDATE.ID = @SEPAMANDATEID
        )

    union all

    --Include batch sponsorship constituent account mandates

    select
        SEPAMANDATE.ID,
        SEPAMANDATE.LOOKUPID,
        SEPAMANDATE.TYPECODE,
        SEPAMANDATE.STATUSCODE,
        SEPAMANDATE.SIGNATUREDATE
    from dbo.SEPAMANDATE
        inner join dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT on BATCHSPONSORSHIPCONSTITUENTACCOUNT.ID = SEPAMANDATE.BATCHSPONSORSHIPCONSTITUENTACCOUNTID
    where
        BATCHSPONSORSHIPCONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID and
        (
            (
                (
                    -- Recurrent and pending signature/active

                    SEPAMANDATE.TYPECODE = 1 and
                    SEPAMANDATE.STATUSCODE in (0,1)
                )
                or
                (
                    -- One-off and pending signature/active and no payment made

                    SEPAMANDATE.TYPECODE = 0 and
                    SEPAMANDATE.STATUSCODE in (0,1) and
                    SEPAMANDATE.PAYMENTCOUNT = 0
                )
            ) or
            -- Allow the just added/loaded mandate to be present in the list

            SEPAMANDATE.ID = @SEPAMANDATEID
        );