USP_SEARCHLIST_CREDITMEMO

Search for a credit memo.
Parameters

Parameter	Parameter Type	Mode	Description
@LASTNAME	nvarchar(100)	IN	Vendor last/Org name
@FIRSTNAME	nvarchar(100)	IN	First name
@LOOKUPID	nvarchar(100)	IN	Lookup ID
@CREDITMEMONUMBER	nvarchar(100)	IN	Credit memo number
@EXACTMATCHONLY	bit	IN	Match all criteria exactly
@CHECKALIASES	bit	IN	Check aliases
@CHECKALTERNATELOOKUPIDS	bit	IN	Check alternate lookup IDs
@DATEFROM	date	IN	From credit memo date
@DATETO	date	IN	To credit memo date
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
Definition

Copy


                CREATE procedure dbo.USP_SEARCHLIST_CREDITMEMO
                (
                    @LASTNAME nvarchar(100) = null,
                    @FIRSTNAME nvarchar(100) = null,
                    @LOOKUPID nvarchar(100) = null,
                    @CREDITMEMONUMBER 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 @CREDITMEMONUMBER = COALESCE(@CREDITMEMONUMBER,'') + '%';
                        set @FIRSTNAME = COALESCE(@FIRSTNAME,'') + '%';
                        set @LASTNAME = COALESCE(@LASTNAME,'') + '%';
                        set @LOOKUPID = COALESCE(@LOOKUPID,'') + '%';

                        select top(@MAXROWS)
                            CM.ID,
                            F.USERDEFINEDID as CREDITMEMONUMBER,
                            F.[DATE] as CREDITMEMODATE,
                            F.TRANSACTIONAMOUNT as CREDITMEMOTOTAL,
                            --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.CREDITMEMO CM
                        inner join dbo.FINANCIALTRANSACTION F on F.ID = CM.ID and F.TYPECODE = 102
                        inner join dbo.CONSTITUENT C on F.CONSTITUENTID = C.ID
                        where
                            (F.USERDEFINEDID LIKE @CREDITMEMONUMBER) 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)
                            CM.ID,
                            F.USERDEFINEDID as CREDITMEMONUMBER,
                            F.[DATE] as CREDITMEMODATE,
                            F.TRANSACTIONAMOUNT as CREDITMEMOTOTAL,
                            --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.CREDITMEMO CM
                        inner join dbo.FINANCIALTRANSACTION F on F.ID = CM.ID and F.TYPECODE = 102
                        inner join dbo.CONSTITUENT C on F.CONSTITUENTID = C.ID
                        where
                            ((@CREDITMEMONUMBER is null) or (F.USERDEFINEDID = @CREDITMEMONUMBER)) 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