WITH SecurityTransactionLatestOrderInstructedDate
	AS
	(
		SELECT soee.SecurityTransactionId, Max(o.EventDateTime) InstructedDate
		FROM OrderStatusAudit o
		INNER JOIN SecurityOrderAllocation soa ON soa.OrderId = o.OrderId
		INNER JOIN SecurityOrderExecutionElement soee ON soee.OrderAllocationId = soa.OrderAllocationId
		WHERE o.NewOrderStatus = 2
		GROUP BY soee.SecurityTransactionId
	)

	SELECT
		vse.InvestmentCollectiveReference AS AccountNo,
		vse.ModifiedDateTime AS ModifiedDate,
		vtc.ChargeTypeId,
		(COALESCE (vtc.OverrideAmount, vtc.Amount, 0) + COALESCE (vtc.OverrideTaxAmount, vtc.TaxAmount, 0)) / vtc.OrderToChargeExchangeRate AS ChargeAmount,
		vtc.Commission AS CommissionIndicator,
		vse.SecurityTransactionId AS TransactionId,
		vse.ClientReference AS ClientName,
		vse.PortfolioReference AS AccountId,
		vse.Reference AS ClientTradeRefNo,
		vse.TransactionStatus,
		vse.TransactionStatus AS Cancelled,
		vse.TransactionDate AS TradeDate,
		vse.SettlementDate,
		vse.Sedol,
		vse.ISIN,
		vse.SecurityClass AS sdSecurityClass,
		CASE vse.SecurityClass WHEN 1 THEN 'EQ' WHEN 2 THEN 'B' WHEN 3 THEN 'B' WHEN 4 THEN 'MF' ELSE 'Other' END AS AssetType,
		vse.OverrideName AS sdOverrideName,
		vse.IssuerName AS sdIssuerName,
		vse.ShareName AS sdShareName,
		vse.InterestRate AS sdInterestRate,
		vse.MaturityDate AS sdMaturityDate,
		vse.FundName AS sdFundName,
		vse.ShareClass AS sdShareClass,
		'N' AS FactoredBondYN,
		'N' AS PlacingYN,
		'N' AS PlacingBookedYN,
		vse.InterestRate AS CouponRate,
		vse.IssueDate,
		vse.MaturityDate,
		vse.TransactionTypeId AS TransactionCode,
		vse.Nominal AS Quantity,
		vse.Nominal AS CurrentFace,
		vse.Price,
		vse.Price * vse.Nominal / vse.PricingFactor AS GrossPrincipal,
		vse.TotalValue / vse.TransactionToSettlementExRate AS NetPrincipal,
		vse.AccruedInterest AS Interest,
		vse.TransactionCurrencyIsoCode AS TradeCurrency,
		vse.TransactionCurrencyIsoCode AS SettleCurrency,
		vse.TotalValue / vse.TransactionToSettlementExRate AS NetSettleAmount,
		cvp.BrokerAccount AS Broker,
		vse.Ticker,
		cvp.BrokerCode,
		cvp.Name AS BrokerDescription,
		'1' AS FXRate,
		stloid.InstructedDate AS ArrivalDate,
		stloid.InstructedDate AS ArrivalTime

	FROM	dbo.vSecurityTransactionsExtended AS vse
			LEFT JOIN   SecurityTransactionLatestOrderInstructedDate AS stloid on stloid.SecurityTransactionId = vse.SecurityTransactionId
			LEFT JOIN	dbo.vCounterparties AS cvp ON vse.CounterpartyId = cvp.CounterpartyId
			LEFT JOIN	dbo.vSecurityTransactionCharges AS vtc ON vtc.SecurityTransactionId = vse.SecurityTransactionId