USP_REPORT_FEES

create procedure dbo.USP_REPORT_FEES
(
    @SALESMETHODID uniqueidentifier = null,
    @FEEID uniqueidentifier = null,
    @FEESELECTIONID uniqueidentifier = null,
    @FROMDATE datetime = null, 
    @TODATE datetime = null
)
as
    set nocount on;

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

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

    with FEE_CTE as ( 
        select 
            SOIF.FEEID ID, 
            sum(SOI.TOTAL) TOTAL 
        from
            dbo.UFN_SALESORDER_BETWEENDATES(@FROMDATE, @TODATE) as FILTEREDSALESORDERS
        inner join
            dbo.SALESORDER on SALESORDER.ID = FILTEREDSALESORDERS.ID
        inner join
            dbo.SALESORDERITEM as SOI on SOI.SALESORDERID = SALESORDER.ID
        inner join
            dbo.SALESORDERITEMFEE as SOIF on SOIF.ID = SOI.ID
        where
            (@SALESMETHODTYPECODE is null or SALESORDER.SALESMETHODTYPECODE = @SALESMETHODTYPECODE)
            and (@FEEID is null or SOIF.FEEID = @FEEID)
            and (@FEESELECTIONID is null or SOIF.FEEID in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@FEESELECTIONID)))
            and SALESORDER.STATUSCODE in (1,3,4)
            and not exists(select * from dbo.CREDITITEM_EXT where CREDITITEM_EXT.SALESORDERITEMID = SOI.ID)
        group by
            SOIF.FEEID
    )
    select 
        F.NAME NAME,
        F.TYPECODE TYPECODE,
        case F.TYPECODE
            when 0 then F.AMOUNT
            when 1 then F.[PERCENT]
        end AMOUNT,
        F.APPLIESTO,
        FEE_CTE.TOTAL
    from dbo.FEE F
    inner join FEE_CTE on FEE_CTE.ID = F.ID
    option (recompile);

    return 0;