We are adding a facility fee in the upcoming season and ticketing would like our invoices to show that fee type on a seperate line from the other fees. Currently, we use a local SP to create the invoices that calls T_ORDER and just displays the total fees paid. Has anyone created anything that will break out fees and is willing to share, or can anyone point in the right direction to get started?
Thanks,Melissa
I've not broken out fees for invoices, but I do regularly break them out for sales reports.
When you say facility fee, do you mean a fee that appears in the fees tab of the order? If that's the case, then using T_SLI_FEE is your best bet.
We have facility fees set up as a layer in our ticket prices. These are included in the purchase section of the order total and you get to them via T_SLI_DETAIL. In order to define which detail rows are fee rows vs ticket rows, this cte will be your best friend:
;with pmap(pmap_no, price_category, price_type)as(select a.id, c.price_category_id, a.price_type from [dbo].T_PERF_PRICE_TYPE a JOIN [dbo].T_PERF_PRICE_LAYER b ON a.perf_price_layer = b.id JOIN [dbo].TR_PRICE_LAYER_TYPE c ON b.price_layer_type = c.id)
Hopefully, you've set up your ticket price with a different price category than your standard ticket price, so joining on pmap and filtering to the correct price category will give you your facility fee totals.
Hope this helps!
Dorothy