UFN_PDACCOUNTCODEMAPPING_GETOFFICENUMBER

CREATE function dbo.UFN_PDACCOUNTCODEMAPPING_GETOFFICENUMBER
                (
                    @REVENUESPLITID as uniqueidentifier,
                    @REVENUETRANSACTIONTYPECODE tinyint,
                    @REVENUESPLITTYPECODE tinyint,
                    @APPLICATIONCODE tinyint,
                    @PAYMENTMETHODCODE tinyint,
                    @DESIGNATIONID uniqueidentifier
                )
returns integer
with execute as caller
as begin
    declare @OfficeID integer

    set @OfficeID =  3    --: Development - Payments

    if @REVENUESPLITTYPECODE = 9 and @REVENUETRANSACTIONTYPECODE <> 203 -- sponsorship and not gift fee

        return @OfficeID
    if @REVENUETRANSACTIONTYPECODE = 0 and @REVENUESPLITTYPECODE = 8 and @APPLICATIONCODE = 11
        begin
            set @OfficeID = 7;
            return @OfficeID;
        end

    --bug 147403 fix planned gifts office; This is fixed in 2.8 and later by bug 123128 in UFN_PDACCOUNTCODEMAPPING_GETOFFICENUMBER_2        

    if ((@PAYMENTMETHODCODE = 7) or (@PAYMENTMETHODCODE = 8) or (@PAYMENTMETHODCODE = 200) or (@PAYMENTMETHODCODE = 201) or (@PAYMENTMETHODCODE = 202) or (@PAYMENTMETHODCODE = 203) or (@PAYMENTMETHODCODE = 204))
        and    (@REVENUETRANSACTIONTYPECODE <>    4) -- Planned gifts payment method codes are actually vehicle types  

        begin
            set @OfficeID = 9
            return @OfficeID
        end        

    if @APPLICATIONCODE = 86 or @APPLICATIONCODE = 85
        begin
            set @OfficeID = 5    --: Development - Benefits

            return @OfficeID
        end 
    if @REVENUETRANSACTIONTYPECODE = 4    
        begin
            select  @OfficeID = 6 --: Development - Planned Gifts

            return @OfficeID        
        end
    if (@PAYMENTMETHODCODE = 9 and  @REVENUETRANSACTIONTYPECODE = 1) or (@PAYMENTMETHODCODE = 205 and  @REVENUETRANSACTIONTYPECODE = 1) or (@PAYMENTMETHODCODE = 99 and  @REVENUETRANSACTIONTYPECODE = 1) or (@APPLICATIONCODE = 87)
        begin
            set  @OfficeID  = 4  --: Development - Pledges

            return @OfficeID
        end
    if (@REVENUETRANSACTIONTYPECODE = 5) 
        begin
            set @OfficeID = 1 --officeid from PDACCOUNTCODEMAPOFFICE where officeid = 1  : Ticketing - Orders

            return @OfficeID
        end        
    if (@REVENUESPLITTYPECODE = 5) or (@REVENUESPLITTYPECODE = 6) or (@REVENUESPLITTYPECODE = 7) or (@REVENUESPLITTYPECODE = 10) or (@REVENUESPLITTYPECODE = 11)  or (@REVENUETRANSACTIONTYPECODE = 0 and @APPLICATIONCODE = 10)
        begin
            set  @OfficeID  = 2 --select  @OfficeID = officeid from PDACCOUNTCODEMAPOFFICE where officeid = 2 : Ticketing - Payments    

            return @OfficeID
        end
    if (@PAYMENTMETHODCODE = 9 and  @REVENUETRANSACTIONTYPECODE = 6) or (@PAYMENTMETHODCODE = 205 and  @REVENUETRANSACTIONTYPECODE = 6) --or (@APPLICATIONCODE = 8)

        begin
            set  @OfficeID  = 11  --: Development - Grant Awards

            return @OfficeID
        end

  if (@PAYMENTMETHODCODE = 206 and  @REVENUETRANSACTIONTYPECODE = 202 and @REVENUESPLITTYPECODE = 202 and @APPLICATIONCODE = 202)
        begin
            set  @OfficeID  = 12  --: Development - Gift Aid

            return @OfficeID
        end

    if (@PAYMENTMETHODCODE = 9 and @REVENUETRANSACTIONTYPECODE = 7 and @REVENUESPLITTYPECODE = 0 and @APPLICATIONCODE = 0) or (@PAYMENTMETHODCODE = 205 and @REVENUETRANSACTIONTYPECODE = 7) --Auction donation

        begin
            set @OFFICEID = 13 --: Development - Auction Donation

            return @OfficeID;
        end

    if (@APPLICATIONCODE = 12 and @REVENUESPLITTYPECODE in (203,204))
        begin
            set @OFFICEID = 14 --: Development - Auction Purchase Gain\Loss

            return @OfficeID;
        end

    if (@REVENUETRANSACTIONTYPECODE = 203)
        begin
            set @OfficeID = 15 --: Development - Gift Fees

            return @OfficeID;
        end

    if (@PAYMENTMETHODCODE = 207 or @PAYMENTMETHODCODE = 208)
        begin
            set @OfficeID = 17 --: Development - Unrealized gains and losses

            return @OfficeID;
        end

    return  @OfficeID
end