USP_DATALIST_MEMBERSHIPPROMOCODEBYPROMOCODE

CREATE procedure dbo.USP_DATALIST_MEMBERSHIPPROMOCODEBYPROMOCODE
(
    @PROMOCODE nvarchar(100),
    @TRANSACTIONCURRENCYID uniqueidentifier, 
    @TRANSACTIONDATE date = null
)
    as
    set nocount on;

    if @TRANSACTIONDATE is null
        set @TRANSACTIONDATE = getdate()

    select 
        MP.ID,
        MP.NAME, 
        MP.DESCRIPTION, 
        MP.PROMOTIONTYPECODE, 
        MP.EXTENSIONCALCULATIONTYPECODE, 
        MP.DISCOUNTCALCULATIONTYPECODE, 
        MP.APPLICATIONTYPE, 
        MP.[PERCENT], 
        MP.AMOUNT, 
        MP.EXTENSIONVALUE, 
        MP.ISACTIVE, 
        MP.FORMATTEDVALUE,
        MP.BASECURRENCYID
    from 
        dbo.MEMBERSHIPPROMO MP 
    inner join 
        dbo.MEMBERSHIPPROMOCODE MPC    on MP.ID = MPC.MEMBERSHIPPROMOID
    where 
        MPC.PROMOTIONALCODE = @PROMOCODE
        and (MP.BASECURRENCYID = @TRANSACTIONCURRENCYID or PROMOTIONTYPECODE = 1 or (PROMOTIONTYPECODE = 0 and DISCOUNTCALCULATIONTYPECODE = 1))
        and (VALIDFROM is null or @TRANSACTIONDATE >= convert(date, VALIDFROM)) 
        and (VALIDTO is null or @TRANSACTIONDATE <= convert(date, VALIDTO))