diff options
| -rw-r--r-- | sql/dawn.sql | 11 |
1 files changed, 7 insertions, 4 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index bd318bae..4e4824e7 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -154,7 +154,8 @@ CREATE TABLE cds(id serial primary key, corr_detach float DEFAULT NULL, account_code varchar(5) NOT NULL, cpty_id text, - globeop_id text, + globeop_id int, + full_globeop_id text GENERATED ALWAYS AS ('CDS'||lpad(cast(globeop_id as text), 6, '0')|| 'K00SCLMA') STORED, CONSTRAINT tranche_check CHECK (( swap_type IN ('CD_INDEX_TRANCHE', 'BESPOKE') AND (orig_attach IS NOT NULL AND orig_detach IS NOT NULL AND clearing_facility IS NULL)) @@ -234,7 +235,9 @@ CREATE TABLE swaptions(id serial PRIMARY KEY, termination_amount float, termination_cp varchar(12) REFERENCES counterparties(code) ON UPDATE CASCADE, cpty_id text, - globeop_id text); + globeop_id int, + full_globeop_id text GENERATED ALWAYS AS ('SWO'||lpad(cast(globeop_id as text), 6, '0')|| 'K00SCLMA') STORED, +); CREATE TRIGGER swaptions_dealid AFTER INSERT ON swaptions FOR EACH ROW EXECUTE PROCEDURE auto_dealid(); @@ -1117,7 +1120,7 @@ FROM swaptions a LEFT JOIN (SELECT * FROM swaption_marks where date <= p_date) b USING (dealid) LEFT JOIN index_desc c ON a.security_id=redindexcode AND a.maturity=c.maturity LEFT JOIN (SELECT * FROM index_quotes WHERE date=p_date) d USING (index, series, tenor) -LEFT JOIN valuation_reports ON invid = a.globeop_id +LEFT JOIN valuation_reports ON invid = a.full_globeop_id LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE termination_date <= p_date GROUP BY dealid) e USING (dealid) LEFT JOIN external_marks_deriv f ON identifier=cpty_id AND f.date=p_date @@ -1829,7 +1832,7 @@ RIGHT JOIN (SELECT invid, periodenddate, sum(endbooknav-endbookunrealincome) AS globeop_clean_nav, sum(endqty) AS globeop_notional FROM valuation_reports GROUP BY invid, periodenddate) a - ON (invid=globeop_id AND periodenddate=tranche_risk.date) + ON (invid=full_globeop_id AND periodenddate=tranche_risk.date) LEFT JOIN index_version ON (security_id=redindexcode) LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx USING (date, currency) LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date |