USP_SEARCHLIST_BILLINGTRANSACTION

Returns all of the billing transactions matching the search criteria.
Parameters

Parameter	Parameter Type	Mode	Description
@KEYNAME	nvarchar(100)	IN	Last/Org name
@FIRSTNAME	nvarchar(50)	IN	First name
@LOOKUPID	nvarchar(36)	IN	Lookup ID
@DATE	datetime	IN	Date
@DUEDATE	datetime	IN	Due date
@TRANSACTIONTYPE	int	IN	Transaction type
@INCLUDEINACTIVE	bit	IN	Include inactive
@INCLUDEDECEASED	bit	IN	Include deceased
@CHECKNICKNAME	bit	IN	Check nickname
@CHECKALIASES	bit	IN	Check aliases
@EXACTMATCHONLY	bit	IN	Match all criteria exactly
@CHECKALTERNATELOOKUPIDS	bit	IN	Check alternate lookup IDs
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
Definition

Copy


CREATE procedure dbo.USP_SEARCHLIST_BILLINGTRANSACTION
(
  @KEYNAME nvarchar(100) = null,
  @FIRSTNAME nvarchar(50) = null,
  @LOOKUPID nvarchar(36) = null,
  @DATE datetime = null,
  @DUEDATE datetime = null,
  @TRANSACTIONTYPE int = null,
  @INCLUDEINACTIVE bit = null,
  @INCLUDEDECEASED bit = null,
  @CHECKNICKNAME bit = null,
  @CHECKALIASES bit = null,
  @EXACTMATCHONLY bit = null,
  @CHECKALTERNATELOOKUPIDS bit = null,
  @MAXROWS smallint = 500
)
as
  set nocount on;

  set @INCLUDEINACTIVE = isnull(@INCLUDEINACTIVE, 0);
  set @INCLUDEDECEASED = isnull(@INCLUDEDECEASED, 0);
  set @CHECKALIASES = isnull(@CHECKALIASES, 0);
  set @CHECKNICKNAME = isnull(@CHECKNICKNAME, 0);
  set @EXACTMATCHONLY = isnull(@EXACTMATCHONLY, 0);
  set @CHECKALTERNATELOOKUPIDS = isnull(@CHECKALTERNATELOOKUPIDS, 0);

  if (@EXACTMATCHONLY = 0)
    begin

      set @FIRSTNAME = isnull(@FIRSTNAME,'') + '%';
      set @KEYNAME = isnull(@KEYNAME,'') + '%';
      set @LOOKUPID = isnull(@LOOKUPID,'') + '%';

      with CONSTIT_NAMEPOOL as
      (
        select 
          C.NAME as CONSTITUENTNAME, 
          C.NAME, 
          C.KEYNAME, 
          C.FIRSTNAME, 
          C.NICKNAME, 
          C.ID, 
          C.LOOKUPID,  
          C.ISINACTIVE, 
          C.ISORGANIZATION  
        from dbo.CONSTITUENT as C with (nolock)

        union all

        select 
          C.NAME + ' (' + ALIAS.NAME + ')' as CONSTITUENTNAME, 
          C.NAME, 
          ALIAS.KEYNAME, 
          ALIAS.FIRSTNAME, 
          '', 
          C.ID, 
          C.LOOKUPID, 
          C.ISINACTIVE, 
          C.ISORGANIZATION 
        from dbo.CONSTITUENT as C with (nolock) 
        inner join dbo.ALIAS on C.ID=ALIAS.CONSTITUENTID and @CHECKALIASES = 1
      ),
      CONSTIT_CTE as
      (
        select
          C.NAME,
          C.KEYNAME,
          C.FIRSTNAME,
          C.NICKNAME,
          C.ID,
          C.LOOKUPID,
          C.CONSTITUENTNAME,
          C.ISINACTIVE
        from CONSTIT_NAMEPOOL as C with (nolock)
      )
      select top(@MAXROWS)
        FINANCIALTRANSACTION.ID,
        CONSTIT_CTE.LOOKUPID,
        CONSTIT_CTE.CONSTITUENTNAME as NAME,
        FINANCIALTRANSACTION.TYPE,
        FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as TRANSACTIONID,
        FINANCIALTRANSACTION.TRANSACTIONAMOUNT as AMOUNT,
        FINANCIALTRANSACTION.DATE,
        FINANCIALTRANSACTION.DESCRIPTION
      from dbo.FINANCIALTRANSACTION with (nolock)
      inner join CONSTIT_CTE with (nolock) on FINANCIALTRANSACTION.CONSTITUENTID = CONSTIT_CTE.ID
      left outer join dbo.CHARGE with (nolock) on CHARGE.ID = FINANCIALTRANSACTION.ID
      left outer join dbo.DECEASEDCONSTITUENT on CONSTIT_CTE.ID = DECEASEDCONSTITUENT.ID
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      where ((@DATE is null) or (cast(FINANCIALTRANSACTION.DATE as date) = cast(@DATE as date))) -- Date criteria

      and ((@DUEDATE is null) or ((cast(CHARGE.DUEDATE as date) = cast(@DUEDATE as date)))) -- Due Date criteria

      and (((@TRANSACTIONTYPE is null) and (FINANCIALTRANSACTION.TYPECODE in (104, 105, 106))) or (FINANCIALTRANSACTION.TYPECODE = @TRANSACTIONTYPE)) -- Transaction type

      and ((@INCLUDEDECEASED = 1) or (DECEASEDCONSTITUENT.ID is null)) -- Include deceased

      and ((@INCLUDEINACTIVE = 1) or (CONSTIT_CTE.ISINACTIVE = 0)) -- Include inactive

      and (CONSTIT_CTE.KEYNAME like @KEYNAME) -- Key Name

      and ((CONSTIT_CTE.FIRSTNAME like @FIRSTNAME) or ((@CHECKNICKNAME = 1) and (CONSTIT_CTE.NICKNAME like @FIRSTNAME))) -- First Name, include nickname

      and ((CONSTIT_CTE.LOOKUPID like @LOOKUPID) or ((@CHECKALTERNATELOOKUPIDS = 1) and (CONSTIT_CTE.ID in (select ALTERNATELOOKUPID.CONSTITUENTID from dbo.ALTERNATELOOKUPID where ALTERNATELOOKUPID.ALTERNATELOOKUPID like @LOOKUPID)))) -- LookupID and check alternate lookupid

      and (FINANCIALTRANSACTION.DELETEDON is null);
    end
  else
    begin
      with CONSTIT_NAMEPOOL as
      (
        SELECT
          C.NAME as CONSTITUENTNAME, 
          C.NAME, 
          C.KEYNAME, 
          C.FIRSTNAME, 
          C.NICKNAME, 
          C.ID, 
          C.LOOKUPID,  
          C.ISINACTIVE, 
          C.ISORGANIZATION  
        from dbo.CONSTITUENT as C with (nolock)

        union all

        select
          C.NAME + ' (' + ALIAS.NAME + ')' as CONSTITUENTNAME, 
          C.NAME, 
          ALIAS.KEYNAME, 
          ALIAS.FIRSTNAME, 
          '', 
          C.ID, 
          C.LOOKUPID, 
          C.ISINACTIVE, 
          C.ISORGANIZATION 
        from dbo.CONSTITUENT as C with (nolock) 
        inner join dbo.ALIAS on C.ID=ALIAS.CONSTITUENTID and @CHECKALIASES = 1
      ),
      CONSTIT_CTE as
      (
        select
          C.NAME,
          C.KEYNAME,
          C.FIRSTNAME,
          C.NICKNAME,
          C.ID,
          C.LOOKUPID,
          C.CONSTITUENTNAME,
          C.ISINACTIVE
        from CONSTIT_NAMEPOOL as C with (nolock)
      )
      select top(@MAXROWS)
        FINANCIALTRANSACTION.ID,
        CONSTIT_CTE.LOOKUPID,
        CONSTIT_CTE.CONSTITUENTNAME as NAME,
        FINANCIALTRANSACTION.TYPE,
        FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as TRANSACTIONID,
        FINANCIALTRANSACTION.TRANSACTIONAMOUNT as AMOUNT,
        FINANCIALTRANSACTION.DATE,
        FINANCIALTRANSACTION.DESCRIPTION
      from dbo.FINANCIALTRANSACTION with (nolock)
      inner join CONSTIT_CTE with (nolock) on FINANCIALTRANSACTION.CONSTITUENTID = CONSTIT_CTE.ID
      left outer join dbo.CHARGE with (nolock) on CHARGE.ID = FINANCIALTRANSACTION.ID
      left outer join dbo.DECEASEDCONSTITUENT on CONSTIT_CTE.ID = DECEASEDCONSTITUENT.ID
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      where ((@DATE is null) or (cast(FINANCIALTRANSACTION.DATE as date) = cast(@DATE as date))) -- Date criteria

      and ((@DUEDATE is null) or ((cast(CHARGE.DUEDATE as date) = cast(@DUEDATE as date)))) -- Due Date criteria

      and (((@TRANSACTIONTYPE is null) and (FINANCIALTRANSACTION.TYPECODE in (104, 105, 106))) or (FINANCIALTRANSACTION.TYPECODE = @TRANSACTIONTYPE)) -- Transaction type

      and ((@INCLUDEDECEASED = 1) or (DECEASEDCONSTITUENT.ID is null)) -- Include deceased

      and ((@INCLUDEINACTIVE = 1) or (CONSTIT_CTE.ISINACTIVE = 0)) -- Include inactive

      and ((@KEYNAME is null) or (CONSTIT_CTE.KEYNAME = @KEYNAME)) -- Key Name

      and ((@FIRSTNAME is null) or (CONSTIT_CTE.FIRSTNAME = @FIRSTNAME) or ((@CHECKNICKNAME = 1) and (CONSTIT_CTE.NICKNAME = @FIRSTNAME))) -- First Name, include nickname

      and ((@LOOKUPID is null) or (CONSTIT_CTE.LOOKUPID = @LOOKUPID) or ((@CHECKALTERNATELOOKUPIDS = 1) and (CONSTIT_CTE.ID in (select ALTERNATELOOKUPID.CONSTITUENTID from dbo.ALTERNATELOOKUPID where ALTERNATELOOKUPID.ALTERNATELOOKUPID = @LOOKUPID)))) -- LookupID and check alternate lookupid

      and (FINANCIALTRANSACTION.DELETEDON is null);
    end

  return 0;