diff options
| -rw-r--r-- | sql/dawn.sql | 20 |
1 files changed, 19 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index ad72c3eb..b4ae0682 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -4149,4 +4149,22 @@ SELECT id, upfront, settle_date, swap_type -FROM irs JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC, folder;
\ No newline at end of file +FROM irs JOIN counterparties ON cp_code = code) ORDER BY trade_date DESC, folder; + +CREATE OR REPLACE FUNCTION baml_fcm_fx(p_date date) RETURNS TABLE (account TEXT, curr currency, "TotBal" NUMERIC, "HomeCurrency" currency, "fxRate" NUMERIC, "convTotBal" NUMERIC, "BuySell" TEXT, "ValueDate" date) LANGUAGE plpgsql +AS $function$ +BEGIN + RETURN query SELECT a.account, a.curr, round(a.totbal::NUMERIC, 2) , a.homecurrency, round(a.fxrate::NUMERIC, 5), round(a.convtotbal::NUMERIC, 2), a.buysell, a.valuedate +FROM(SELECT cash_account AS account, buy_currency AS curr,-buy_amount AS totbal, sell_currency AS homecurrency, spot_rate AS fxrate, sell_amount AS convtotbal, 'Buy' AS buysell, settle_date AS valuedate +FROM spots +WHERE sell_currency = 'USD' +AND trade_date = p_date +UNION SELECT cash_account AS account, sell_currency AS curr, sell_amount AS totbal, buy_currency AS homecurrency, spot_rate AS fxrate, sell_amount AS convtotbal, 'Sell' AS buysell, settle_date AS valuedate +FROM spots +WHERE buy_currency = 'USD' +AND trade_date = p_date) a; + +END; + +$function$ +;
\ No newline at end of file |