fnTransactions_GetCustomTransactionForClient

CREATE  function [dbo].[fnTransactions_GetCustomTransactionForClient]
    (@ClientID int,
     @TransactionTypeGuid as uniqueidentifier,
     @UserIdFilter integer,
     @Filter as int,
     @TransactionID as int)
    returns @CustomTransTable table (
        TransactionTypeGuid uniqueidentifier,
           CustomTransactionID int,
        DateLastChanged datetime,
        DateProcessed datetime,
        AddedByUserID int,
        Data ntext,
        BackofficeID int
    ) 
as 
/*
    multipurpose get

    By TransTypeGuid/Processed filter/UserID:

        filter: 0=all
        filter: 1=unprocessed only

    or by TransID:

    if @transactionid > 0 then only that transaction is returned

*/
begin
        Insert Into @CustomTransTable
        select 
        ctran.TransactionTypeGuid,
        ctran.CustomTransactionID,
        ctran.DateLastChanged,
        ctran.DateProcessed,
        ctran.AddedByUserID,
        ctran.Data,
        dbo.fnGetRaisersEdgeRecordIDFromLinkedUserID(ctran.addedbyuserid)

        from dbo.CustomTransactions ctran

        where 
            ctran.ClientsID=@ClientID
            and (ctran.TransactionTypeGuid=@TransactionTypeGuid
            and ((ctran.dateProcessed is null and @filter=0) 
            or (@filter=1))
            and ((ctran.AddedByUserID=@UserIdFilter and @UserIdFilter>0) 
            or (@UserIdFilter=0))
            and ((ctran.CustomTransactionID = @TransactionID) or (@TransactionID=0))
            )
            or ((@TransactionID >0) and (ctran.CustomTransactionID = @TransactionID))
        order by ctran.CustomTransactionID desc
return
end