USP_REPORT_ADJUSTABLEDISCOUNT

create procedure dbo.USP_REPORT_ADJUSTABLEDISCOUNT
(
    @SALESMETHODID uniqueidentifier = null,
    @APPUSERID uniqueidentifier = null, 
    @APPUSERSELECTIONID uniqueidentifier = null,
    @INCLUDEREFUNDED bit = 1,
    @FROMDATE datetime = null,
    @TODATE datetime = null
) as
    set nocount on;

    if @FROMDATE is null begin
        select @FROMDATE = min(TRANSACTIONDATE) from dbo.SALESORDER where TRANSACTIONDATE is not null
    end

    if @TODATE is null begin
        set @TODATE = getdate();
    end

    set @FROMDATE = cast(@FROMDATE as date);
    set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

    declare @SALESMETHODTYPECODE tinyint = (select TYPECODE from dbo.SALESMETHOD where ID = @SALESMETHODID);

    select
        SO.SEQUENCEID ORDERNUMBER, 
        coalesce(cast(FT.DATE as datetime), [SO].[TRANSACTIONDATE]) ORDERDATE,
        SOAD.CALCULATIONTYPECODE,
        case SOAD.CALCULATIONTYPECODE 
            when 1 then SOAD.[PERCENT]
            else SOAD.[AMOUNT]
        end [VALUE],
        SOAD.CALCULATIONTYPE,
        U.USERNAME,
        DRC.[DESCRIPTION] REASON
    from dbo.UFN_SALESORDER_BETWEENDATES(@FROMDATE, @TODATE) as FILTEREDORDERS
    inner join dbo.SALESORDER SO with (nolock) on SO.ID = FILTEREDORDERS.ID
    inner join dbo.SALESORDERADJUSTABLEDISCOUNT SOAD with (nolock) on SOAD.SALESORDERID = SO.ID
    inner join dbo.DISCOUNTREASONCODE DRC with (nolock) on DRC.ID = SOAD.DISCOUNTREASONCODEID
    inner join dbo.APPUSER U with (nolock) on U.ID = SO.APPUSERID
    left join dbo.FINANCIALTRANSACTION as FT with (nolock) on FT.ID = SO.REVENUEID and FT.DELETEDON is null
    left join dbo.UFN_SALESORDER_GETREFUNDSTATUS_BULK() as REFUNDSTATUS on REFUNDSTATUS.ID = FILTEREDORDERS.ID
    where
        SO.STATUSCODE in (1,3,4)  -- Complete, Confirmed, Finalized

        and (@APPUSERID is null or U.ID = @APPUSERID)
        and (@APPUSERSELECTIONID is null or U.ID in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@APPUSERSELECTIONID)))
        and (@SALESMETHODID is null or SO.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
        and (@INCLUDEREFUNDED = 1 or REFUNDSTATUS.REFUNDSTATUS = 0)
    order by
        SO.SEQUENCEID
    option (recompile);