USP_REPORT_LOCKBOXRECONCILIATION

create procedure dbo.USP_REPORT_LOCKBOXRECONCILIATION
(
    @STARTDATE datetime = null, 
    @ENDDATE datetime = null,
    @CURRENCYCODE tinyint = null, --3 = My base, (null, 1) = Organization

    @REPORTUSERID nvarchar(128) = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
    set nocount on;


    declare @CURRENTAPPUSERID uniqueidentifier = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    declare @SELECTEDCURRENCYID uniqueidentifier;

    if @CURRENCYCODE = 3
    begin
        if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
        begin                    
            select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
                from dbo.CURRENCYSET
                where
                    CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
        end
        else
        begin
            select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
                from dbo.CURRENCYSET
                where
                    CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
        end
    end
    else
        set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;

    declare @ISOCURRENCYCODE nvarchar(3);
    declare @CURRENCYSYMBOL nvarchar(5);
    declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;

    select
        @ISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
        @CURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
        @DECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
        @ROUNDINGTYPECODE = CURRENCYPROPERTIES.ROUNDINGTYPECODE
    from
        dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES

    select
        LOCKBOX.ID,
        LOCKBOX.NAME,
        REVENUE.DATE,
        REVENUELOCKBOX.BATCHNUMBER,
        isnull(sum(REVENUE.AMOUNTINCURRENCY), 0) TOTALAMOUNT,
        count(REVENUE.ID) TOTALCOUNT,
        @SELECTEDCURRENCYID SELECTEDCURRENCYID,
        @ISOCURRENCYCODE ISOCURRENCYCODE,
        @CURRENCYSYMBOL CURRENCYSYMBOL,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE CURRENCYSYMBOLDISPLAYSETTINGCODE,
        @DECIMALDIGITS DECIMALDIGITS
    from
        dbo.UFN_REVENUE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) REVENUE
    inner join
        dbo.REVENUELOCKBOX on REVENUELOCKBOX.ID = REVENUE.ID
    inner join
        dbo.LOCKBOX on LOCKBOX.ID = REVENUELOCKBOX.LOCKBOXID
    outer apply
        dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
    where
        (@STARTDATE is null or REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
        and (@ENDDATE is null or REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
    group by
        REVENUE.DATE, LOCKBOX.ID, LOCKBOX.NAME, REVENUELOCKBOX.BATCHNUMBER, CURRENCYPROPERTIES.ISO4217, 
        CURRENCYPROPERTIES.CURRENCYSYMBOL,CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, CURRENCYPROPERTIES.DECIMALDIGITS