Hello, I have a report request show investment orders where at the trade level different commission rates are used. Background: We submit a buy order (order id 529414254) and that order is broken down to fill Account level trades ( trade id's 19581866, 19581867, 19581868). The commission rate should be the same across all trades, but occasionally we see examples where it's not and I'm looking to create a report to display these.
Select a.ACCT_CD, o.ORDER_ID, a.TRADE_ID, s.TICKER, o.TRADE_DATE, a.EXEC_QTY, a.EXEC_PRICE, a.COMMISION_AMT,
from TS_ORDER o join TS_ORDER_ALLOC a on o.ORDER_ID = a.ORDER_ID
join CSM_SECURITY s on o.sec_id = s.sec_id
join CS_FUND f on a.ACCT_CD = f.ACCT_CD
Where o.ORDER_ID = 529414254
Notes: the TS_Order table contains the order level data and TS_ORDER_ALLOC contains the trade level. Any idea how to pull back Orders where the different commission rates are used?