USP_SEARCHLIST_INVOICE

CREATE procedure dbo.USP_SEARCHLIST_INVOICE
(
    @LASTNAME nvarchar(100) = null,
    @FIRSTNAME nvarchar(100) = null,
    @LOOKUPID nvarchar(100) = null,
    @INVOICENUMBER nvarchar(100) = null,
    @EXACTMATCHONLY bit = null,
    @CHECKALIASES bit = null,
    @CHECKALTERNATELOOKUPIDS bit = null,
    @DATEFROM date = null,
    @DATETO date = null,
    @MAXROWS smallint = 500
)
as
    set @EXACTMATCHONLY = COALESCE(@EXACTMATCHONLY, 0)
    set @CHECKALIASES = COALESCE(@CHECKALIASES, 0)
    set @CHECKALTERNATELOOKUPIDS = COALESCE(@CHECKALTERNATELOOKUPIDS, 0)
    if @EXACTMATCHONLY = 0
    begin
        set @INVOICENUMBER = COALESCE(@INVOICENUMBER,'') + '%';
        set @FIRSTNAME = COALESCE(@FIRSTNAME,'') + '%';
        set @LASTNAME = COALESCE(@LASTNAME,'') + '%';
        set @LOOKUPID = COALESCE(@LOOKUPID,'') + '%';

        select top(@MAXROWS)
            I.ID,
            F.USERDEFINEDID as INVOICENUMBER,
            F.[DATE] as INVOICEDATE,
            F.TRANSACTIONAMOUNT as INVOICETOTAL,
            --C.LOOKUPID as LOOKUPID,

            dbo.UFN_NAMEFORMAT_08(C.ID, C.KEYNAME, C.FIRSTNAME, C.MIDDLENAME, null, null, null, null, null, null, null) as VENDORNAME
        from dbo.INVOICE I
        inner join dbo.FINANCIALTRANSACTION F on F.ID = I.ID and F.TYPECODE = 101
        inner join dbo.CONSTITUENT C on F.CONSTITUENTID = C.ID
        where
            (F.USERDEFINEDID LIKE @INVOICENUMBER) and
            ((C.LOOKUPID like @LOOKUPID) or ((C.ID in (select AL.CONSTITUENTID from dbo.ALTERNATELOOKUPID AL where AL.ALTERNATELOOKUPID like @LOOKUPID) and @CHECKALTERNATELOOKUPIDS <> 0))) and
            ((C.KEYNAME like @LASTNAME) or ((C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.KEYNAME like @LASTNAME) and @CHECKALIASES <> 0))) and
            ((C.FIRSTNAME like @FIRSTNAME) or ((C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.FIRSTNAME like @FIRSTNAME) and @CHECKALIASES <> 0))) and
            (((@DATEFROM is null) or (F.DATE >= @DATEFROM)) and ((@DATETO is null) or (F.DATE <= @DATETO))) and
      (F.DELETEDON IS NULL)
        order by 
            C.KEYNAME asc, F.USERDEFINEDID asc, C.LOOKUPID asc
    end
    else
        select top(@MAXROWS)
            I.ID,
            F.USERDEFINEDID as INVOICENUMBER,
            F.[DATE] as INVOICEDATE,
            F.TRANSACTIONAMOUNT as INVOICETOTAL,
            --C.LOOKUPID as LOOKUPID,

            dbo.UFN_NAMEFORMAT_08(C.ID, C.KEYNAME, C.FIRSTNAME, C.MIDDLENAME, null, null, null, null, null, null, null) as VENDORNAME
        from dbo.INVOICE I
        inner join dbo.FINANCIALTRANSACTION F on F.ID = I.ID and F.TYPECODE = 101
        inner join dbo.CONSTITUENT C on F.CONSTITUENTID = C.ID
        where
            ((@INVOICENUMBER is null) or (F.USERDEFINEDID = @INVOICENUMBER)) and
            ((@LOOKUPID is null) or (C.LOOKUPID = @LOOKUPID) or ((C.ID in (select AL.CONSTITUENTID from dbo.ALTERNATELOOKUPID AL where AL.ALTERNATELOOKUPID = @LOOKUPID) and @CHECKALTERNATELOOKUPIDS <> 0))) and
            ((@LASTNAME is null) or (C.KEYNAME = @LASTNAME) or ((C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.KEYNAME = @LASTNAME) and @CHECKALIASES <> 0))) and
            ((@FIRSTNAME is null) or (C.FIRSTNAME = @FIRSTNAME) or ((C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.FIRSTNAME = @FIRSTNAME) and @CHECKALIASES <> 0))) and
            (((@DATEFROM is null) or (F.DATE >= @DATEFROM)) and ((@DATETO is null) or (F.DATE <= @DATETO))) and
      (F.DELETEDON IS NULL)
        order by 
            C.KEYNAME asc, F.USERDEFINEDID asc, C.LOOKUPID asc