UFN_MERCHANDISEITEM_GETDISCOUNTS

CREATE function dbo.UFN_MERCHANDISEITEM_GETDISCOUNTS
(
    @MERCHANDISEPRODUCTID uniqueidentifier,
    @INCLUDEINACTIVE bit = 0
)
returns table
as
    return (
        select distinct
            DISCOUNT.ID,
            DISCOUNT.NAME,
            DISCOUNT.CALCULATIONTYPECODE,
            DISCOUNT.CALCULATIONTYPE,
            case when DISCOUNT.APPLIESTOCODE = 0 and DISCOUNT.DISCOUNTTYPECODE = 0
                then
                    DISCOUNT.AMOUNT
            else
                DISCOUNT.MERCHANDISEAMOUNT
            end as MERCHANDISEAMOUNT,
            case when DISCOUNT.APPLIESTOCODE = 0 and DISCOUNT.DISCOUNTTYPECODE = 0
                then
                    DISCOUNT.[PERCENT]
            else
                DISCOUNT.MERCHANDISEPERCENT
            end as MERCHANDISEPERCENT
        from
            dbo.DISCOUNTGROUPDETAIL
        inner join
            dbo.DISCOUNTGROUP on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
        inner join
            dbo.DISCOUNT on DISCOUNT.ID = DISCOUNTGROUP.DISCOUNTID
        left outer join
            dbo.DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT on DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.ID = DISCOUNTGROUPDETAIL.ID
        left outer join
            dbo.DISCOUNTGROUPDETAILMERCHANDISEITEM on DISCOUNTGROUPDETAILMERCHANDISEITEM.ID = DISCOUNTGROUPDETAIL.ID
        where
            (
                DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE in (0, 5, 6)  -- All discountable merchandise

                or (
                    DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 1  -- Merchandise department

                    and DISCOUNTGROUPDETAILMERCHANDISEDEPARTMENT.MERCHANDISEDEPARTMENTID = (select MERCHANDISEDEPARTMENTID from dbo.MERCHANDISEPRODUCT where ID = @MERCHANDISEPRODUCTID)
                )
                or (
                    DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE = 2  -- Merchandise item

                    and DISCOUNTGROUPDETAILMERCHANDISEITEM.MERCHANDISEITEMID = @MERCHANDISEPRODUCTID
                )
            )
            and exists (
                select 1
                from dbo.MERCHANDISEPRODUCT
                where
                    ID = @MERCHANDISEPRODUCTID
                    and ISDISCOUNTABLE = 1
                    and dbo.UFN_MERCHANDISEDEPARTMENT_ISDISCOUNTABLE(MERCHANDISEDEPARTMENTID) = 1
            )
            and DISCOUNT.SUPERSEDEDBYID is null
            and (@INCLUDEINACTIVE = 1 or DISCOUNT.ISACTIVE = 1)
    );