Querying BBMS processing fees within CRM

Heather Templeton

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

Wesley Waters

@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

@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

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

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?

Wesley Waters

@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

@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.