USP_REPORT_RECEIPT_MAIN

CREATE procedure dbo.USP_REPORT_RECEIPT_MAIN 
      (
        @DATETOUSE int = null,
        @STARTDATE datetime = null,
        @ENDDATE datetime = null,
        @MISCELLANEOUSPAYMENTQUERY uniqueidentifier = null,
        @ID uniqueidentifier = null,
        @REPORTUSERID nvarchar(128) = null,
        @CURRENCYCODE smallint = null,
        @ALTREPORTUSERID nvarchar(128) = null
      )
      with execute as owner
      as
          set nocount on;
          /*I couldn't get it to group over the subselect amount column, which was returning duplicate data. So I am now inserting into a temp table and
            group over the results from that temp table (which doesn't require the use of a subselect) */

          declare @CURRENTAPPUSERID uniqueidentifier;
          declare @ISADMIN bit;
          declare @APPUSER_IN_NONRACROLE bit;
          declare @APPUSER_IN_NOSECGROUPROLE bit;

          set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
          set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
          set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
          set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

          declare @SQLTOEXEC nvarchar(max);

          declare @DBOBJECTNAME nvarchar(128);
          declare @DBOBJECTTYPE smallint;

          if @MISCELLANEOUSPAYMENTQUERY is not null begin
              if not exists(select ID from dbo.IDSETREGISTER where ID = @MISCELLANEOUSPAYMENTQUERY) raiserror('ID set does not exist in the database.', 15, 1);
              select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @MISCELLANEOUSPAYMENTQUERY;
              if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
              else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @MISCELLANEOUSPAYMENTQUERY) + ''')';
          end

        set @SQLTOEXEC = 
            'select 
            REVENUE.ID,
            REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
            REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
            case @CURRENCYCODE when 0 then REVENUE.BASEAMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGAMOUNT end as AMOUNT,
            cast(REVENUE.DATE as datetime) as [DATE],
            isnull(REVENUEREFERENCE.REFERENCE, '''')  as [PAYMENTSOURCE],
            CURRENCYPROPERTIES.ID as CURRENCYID,
            CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
            CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
            CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
            from dbo.REVENUESPLIT_EXT as REVENUESPLIT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on REVENUESPLIT.ID = FTLI.ID
            inner join dbo.FINANCIALTRANSACTION as REVENUE on REVENUE.ID = FTLI.FINANCIALTRANSACTIONID
            inner join dbo.REVENUE_EXT as REVENUEREFERENCE on REVENUEREFERENCE.ID = REVENUE.ID             
            inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
      inner join dbo.PDACCOUNTSYSTEMFORREVENUE on REVENUE.ID = PDACCOUNTSYSTEMFORREVENUE.ID
            inner join dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) PDACCOUNTSYSTEMIDSFORUSER on PDACCOUNTSYSTEMFORREVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMIDSFORUSER.ID
            inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = PDACCOUNTSYSTEMIDSFORUSER.ID ';

        if @CURRENCYCODE = 0 --Base

            set @SQLTOEXEC = @SQLTOEXEC + '
                inner join  dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
                inner join dbo.CURRENCY as CURRENCYPROPERTIES on CURRENCYSET.BASECURRENCYID = CURRENCYPROPERTIES.ID ';
        else if @CURRENCYCODE = 1 --Org

            set @SQLTOEXEC = @SQLTOEXEC + '
                cross apply (select ID,ISO4217,DECIMALDIGITS,CURRENCYSYMBOL,SYMBOLDISPLAYSETTINGCODE from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1) as CURRENCYPROPERTIES ';
        else --Tran

            set @SQLTOEXEC = @SQLTOEXEC + '
                inner join dbo.CURRENCY as CURRENCYPROPERTIES on REVENUE.TRANSACTIONCURRENCYID = CURRENCYPROPERTIES.ID ';

        if @MISCELLANEOUSPAYMENTQUERY is not null
            set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVENUE.[ID] = SELECTION.[ID]' + nchar(13);

        set @SQLTOEXEC = @SQLTOEXEC + '
            where REVENUESPLIT.APPLICATIONCODE=11 and REVENUESPLIT.TYPECODE=8 ';
        if (@DATETOUSE = 0 or @DATETOUSE is null)
        begin
            if not @STARTDATE is null
                set @SQLTOEXEC = @SQLTOEXEC + '
                    and cast(REVENUE.DATE as datetime) >= @STARTDATE ';
            if not @ENDDATE is null
                set @SQLTOEXEC = @SQLTOEXEC + '
                    and cast(REVENUE.DATE as datetime) <= @ENDDATE ';
        end
        else
        begin
            if not @STARTDATE is null
                set @SQLTOEXEC = @SQLTOEXEC + '
                    and cast(REVENUE.POSTDATE as datetime) >= @STARTDATE ';
            if not @ENDDATE is null
                set @SQLTOEXEC = @SQLTOEXEC + '
                    and cast(REVENUE.POSTDATE as datetime) <= @ENDDATE ';
        end

        if not @ID is null
            set @SQLTOEXEC = @SQLTOEXEC + '
                and (REVENUE.ID = @ID) ';

        set @SQLTOEXEC = @SQLTOEXEC + '
            and (@ISADMIN = 1 or @APPUSER_IN_NONRACROLE = 1) 
            order by DATE desc';

        exec sp_executesql @SQLTOEXEC, 
            N'@CURRENCYCODE int, @DATETOUSE int, @STARTDATE datetime, @ENDDATE datetime, @ID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier',
            @CURRENCYCODE=@CURRENCYCODE, @DATETOUSE=@DATETOUSE, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @ID=@ID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID;