Database Post Trade Calculations and email

starting point is the font office table everyday a new file is uploaded into the table and new records are added. Records can either be a new record or a record that is cancelling a previous one 3.1.2 Enter/Cancel/Amend using the State field New records are added with state = I (Insert). A previous record is cancelled by providing a new record with state = C (cancelled) and the same trade identifier as the record being cancelled. A previous record is amended using a cancel/replace approach. First, the previous record is cancelled, and then a new (replacement) record is written with state = I (insert). Note: Records with a state A are not provided by this stream although A is still a valid value on the end of day extracts. 3.1.3 Enter/Cancel/Amend using the BO_State field New records are added with state = I (Insert). A previous record is cancelled by providing a new record with state = C (cancelled) and the same trade identifier as the record being cancelled. Where a record has been provided with BO_State cancelled, no further records with the same trade identifier will be provided. A previous record is amended using the reversal/amend approach. First, the previous record is reversed with state = R (reversal), and then a new (amended) record is written with state = A (amend). we only need to work with records that have Back_Office_Report_Type <> "T" what we need to do is to calculate the p&l of an order. an order is generally 2 records that have the same order_id, one Buy and one Sell. sometimes the same order ide can have multiple Buy or multiple Sell the p&l is calculated in the following way (Mark + Commision – Brokerage)* Dealt_To_Settlement_Rate Where Mark = GrossConsideration of the Sell - GrossConsideration of the Buy order has a client side and a market side. For the client side an attached record is in the allocation table linked by the Booking_Trade_Id once the p&l of each order has been calculated (please bear in mind that a cancellation can occur on the next day file if there is a cancel replace) then we need to allocate this p&l to the different representative and send an email (directly from VBA not from outlook) to them for the day and a cumulative amount for the month. The allocation is based on the Bookid. For exemple for book OUB 50% of pl is for representatie1, 20% for representative2 and 30% for representative3