Querying BBMS processing fees within CRM

Heather Templeton
Heather Templeton Blackbaud Employee
Tenth Anniversary Name Dropper Participant Facilitator 1
in Blackbaud CRM™ General Discussions

Hello,

Has anyone found a way to pull BBMS processing fees from with CRM? either by a query, Infinity SDK, or within DB itself? I'm assisting a customer who would like to charge the fee to the department that received the revenue at the time of the payment/gift/revenue transaction.

If not, ideas on how you accomplish getting this information to your Finance department?

Kind Regards,

Heather

Comments

  • Wesley Waters
    Wesley Waters
    Tenth Anniversary Name Dropper Participant Facilitator 1

    @Heather Templeton
    Wake Forest would also like this functionality to facilitate auto-reconciliation in Workday finance. BBMS deposits net of fees necessitate us sending the fee data to Workday. This is currently a manual process due to the limitation you describe.

  • Rick Root
    Rick Root
    Tenth Anniversary Kudos 2 Name Dropper Participant

    @Heather Templeton The fees are are only pulled into the database when you run the reconciliation process.

    You might find this query useful. On average, our transactions have fees about 7 days after the gift is added.

    -- select avg(age) FROM (
    select
    ft.CALCULATEDUSERDEFINEDID as REVENUEID,
    fee.dateadded,
    fee.datechanged,
    datediff(day, fee.dateadded,fee.datechanged) as AGE,
    fee.fee,
    rpm.AMOUNT,
    credittypecode.DESCRIPTION as CARDTYPE,
    case when rpm.amount > 0 then CAST(fee.fee / rpm.amount * 100 as decimal(18,2))
    else null end as PCT
    from
    CREDITCARDPAYMENTMETHODDETAIL fee
    inner join CREDITTYPECODE on fee.CREDITTYPECODEID=CREDITTYPECODE.ID
    inner join REVENUEPAYMENTMETHOD rpm on fee.id=rpm.id
    inner join FINANCIALTRANSACTION ft on rpm.REVENUEID=ft.id
    where
    fee.fee > 0.00
    and ft.DATEADDED > '2024-07-01'

    --) X

  • Heather Templeton
    Heather Templeton Blackbaud Employee
    Tenth Anniversary Name Dropper Participant Facilitator 1

    @Wesley Waters the reconciliation process discussed ab does populate the fees so you can pull it out via a customization

  • Heather Templeton
    Heather Templeton Blackbaud Employee
    Tenth Anniversary Name Dropper Participant Facilitator 1

    @Rick Root do you have any issues where two revenue transactions are attached to only one BBMS transaction and therefore the fee is only one one revenue transaction?

  • Wesley Waters
    Wesley Waters
    Tenth Anniversary Name Dropper Participant Facilitator 1

    @Heather Templeton
    Thank you, I am aware of that. The recon process comes from the disbursement and BBMS fastest turn time is 3 days post donation. We are required to feed to finance day after, meaning we need the fee data no later than the day after the donation has been made.

  • Rick Root
    Rick Root
    Tenth Anniversary Kudos 2 Name Dropper Participant

    @Heather Templeton:

    @Rick Root do you have any issues where two revenue transactions are attached to only one BBMS transaction and therefore the fee is only one one revenue transaction?

    not that I've ever seen.

Categories