USP_REPORT_OPENTRANSACTIONS

CREATE procedure dbo.USP_REPORT_OPENTRANSACTIONS  
(
  @DATETYPE smallint = null,
  @DATE datetime = null,
  @TRANSACTIONQUERY uniqueidentifier = null,
  @VENDORID uniqueidentifier = null,
  @REPORTUSERID nvarchar(128) = null,
  @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
set nocount on;

  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 @TRANSACTIONQUERY is not null begin
    if not exists(select ID from dbo.IDSETREGISTER where ID = @TRANSACTIONQUERY) raiserror('ID set does not exist in the database.', 15, 1);

    select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @TRANSACTIONQUERY;
        if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
        else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @TRANSACTIONQUERY) + ''')';
  end

  set @SQLTOEXEC = 
    'select 
      ''http://www.blackbaud.com?CONSTITUENTID='' + CONVERT(nvarchar(36),FT.CONSTITUENTID) as [CONSTITUENTID],
      dbo.UFN_NAMEFORMAT_08(C.ID, C.KEYNAME, C.FIRSTNAME, C.MIDDLENAME, null, null, null, null, null, null, null) as [VENDORNAME],
      FT.TYPE,
      FT.USERDEFINEDID,
      CASE WHEN FT.TYPECODE = 101 THEN ''http://www.blackbaud.com?INVOICEID='' + CONVERT(nvarchar(36), FT.ID) ELSE '''' END as [INVOICEID],
      CASE WHEN FT.TYPECODE = 102 THEN ''http://www.blackbaud.com?CREDITMEMOID='' + CONVERT(nvarchar(36), FT.ID) ELSE '''' END as [CREDITMEMOID],
      cast(FT.DATE as datetime) [DATE],
      FT.POSTDATE,
      CASE WHEN FT.TYPECODE=101 THEN FT.TRANSACTIONAMOUNT ELSE -1 * FT.TRANSACTIONAMOUNT END as [AMOUNT],
      CASE WHEN FT.TYPECODE=101 THEN I.BALANCE ELSE -1 * CM.BALANCE END as [BALANCE],
      CASE WHEN FT.TYPECODE=101 THEN I.DATEDUE ELSE cast(FT.DATE as datetime) END as [DUEDATE]
      from dbo.FINANCIALTRANSACTION FT with (nolock)
      inner join dbo.CONSTITUENT as  C on FT.CONSTITUENTID = C.ID
      left outer join dbo.ADDRESS as A on FT.CONSTITUENTID = A.CONSTITUENTID and A.ISPRIMARY = 1
      left outer join dbo.INVOICE I on I.ID = FT.ID and FT.TYPECODE = 101
      left outer join dbo.CREDITMEMO CM on CM.ID = FT.ID and FT.TYPECODE = 102 ' + nchar(13);

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

  set @SQLTOEXEC = @SQLTOEXEC + 
        'where
        ((@DATETYPE = 0 and (FT.DATE <= GETDATE()) and ((FT.POSTDATE is null) or (FT.POSTDATE <= GETDATE())) or 
        (@DATETYPE = 1 and (FT.DATE <= DateAdd("d", -1, GETDATE())) and ((FT.POSTDATE is null) or (FT.POSTDATE <= DateAdd("d", -1, GETDATE())))) or 
        (@DATETYPE = 2 and (FT.DATE <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))) and ((FT.POSTDATE is null) or (FT.POSTDATE <= DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))) or
        (@DATETYPE = 3 and (FT.DATE <= @DATE) and ((FT.POSTDATE is null) or (FT.POSTDATE <= @DATE)))))
      and (@VENDORID is null or FT.CONSTITUENTID = @VENDORID)
      and (FT.TYPECODE = 101 or FT.TYPECODE = 102)  
      and isnull(I.ZEROBALANCE, CM.ZEROBALANCE) = 0 '

  exec sp_executesql @SQLTOEXEC,  
    N'@DATETYPE smallint, @DATE datetime, @VENDORID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier',
      @DATETYPE=@DATETYPE, @DATE=@DATE, @VENDORID=@VENDORID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID;