Hello,
I tried modifying Transactions by Posting report. But the Transaction table does not appear to populate perf_no for fees even if the fees are at the performance level. There is no way to map a transaction line back to the individual fees in T_SLI_FEE as far as I can tell. I thought I could do it by order number and fee type but this does not necessarily identify the fees for that transaction because of return consideration. (It actually works pretty good until your run into returns and re-buys in the same order_no in a different batch)
Any ideas of where to start would be good.
Part of the problem I'm seeing is that the T_SUB_LINEITEM batch_no gets updated to new batch_no if it is opened and exchanged in new batch. So I can't see a way to tie that SLI to the batch it was originally purchased in.
(Maybe I'm missing table that could tell me this)
Thanks for any ideas. Let me know if you need some clarification on what exactly I'm trying to do.
Thanks
Matt
I don't have an answer for you off hand. But have you looked at the procedure behind the 'Fees by Performance' report? It may be useful.
I did see that procedure. Unfortunately while that does break down fees by performance there is no way to tie those fees to the post or batch they were in.
I think we will change our business practice and do exchanges in a new order. This seems to fix the issue. Because I can then break out the per perf fees per order. The problem was if the exchange happens in the same order I can't reliably say all the fee lines in T_SLI_FEE with that order number belong to that batch/transaction line.