diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/et_tables.sql | 47 |
1 files changed, 47 insertions, 0 deletions
diff --git a/sql/et_tables.sql b/sql/et_tables.sql index bc430547..93a2682d 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -362,3 +362,50 @@ CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[]) ALTER FUNCTION et_cusip_details(varchar(10)[]) OWNER TO et_user; + +CREATE OR REPLACE FUNCTION et_deal_subordination(p_dealname varchar(10)) + RETURNS TABLE(p_cusip varchar(9), p_orig_subordination float, p_curr_subordination float) AS $$ + DECLARE + cusip_row RECORD; + curr_cumsum_cusip_bal float; + orig_cumsum_cusip_bal float; + curr_adjusted_collat_bal float; + orig_adjusted_collat_bal float; + curr_deal_bal float; + defaulted_price float:=0.7; + curr_subordination float[]; + orig_subordination float[]; + deal_info RECORD; + i int := 0; + previous_moody text; + cusips varchar(9)[]; + BEGIN + SELECT "Principal Bal", "Orig Collat Bal", "Curr Collat Bal", "Defaulted Bal", "Orig Deal Bal", "Curr Deal Bal" FROM latest_clo_universe where dealname = p_dealname INTO deal_info; + curr_adjusted_collat_bal := deal_info."Curr Collat Bal" + deal_info."Principal Bal" - + (1-defaulted_price)*deal_info."Defaulted Bal"; + orig_adjusted_collat_bal := deal_info."Orig Collat Bal"; + curr_cumsum_cusip_bal := 0; + orig_cumsum_cusip_bal := 0; + FOR cusip_row in SELECT a.* from cusip_universe a JOIN + (SELECT generate_subscripts("Deal Cusip List",1) AS cusip_id, + unnest("Deal Cusip List") AS cusip + FROM latest_clo_universe WHERE dealname=p_dealname) b + ON a.cusip = b.cusip ORDER BY b.cusip_id LOOP + curr_cumsum_cusip_bal := curr_cumsum_cusip_bal + cusip_row.curr_balance; + orig_cumsum_cusip_bal := orig_cumsum_cusip_bal + cusip_row.orig_balance; + curr_subordination := curr_subordination || (curr_adjusted_collat_bal-curr_cumsum_cusip_bal)/deal_info."Curr Deal Bal"; + orig_subordination := orig_subordination || (orig_adjusted_collat_bal - orig_cumsum_cusip_bal)/deal_info."Orig Deal Bal"; + cusips := cusips || cusip_row.Cusip; + i:=i+1; + IF i>0 THEN + IF cusip_row.orig_moody = previous_moody THEN + curr_subordination[i-1]:=curr_subordination[i]; + orig_subordination[i-1]:=orig_subordination[i]; + END IF; + END IF; + previous_moody := cusip_row.orig_moody; + END LOOP; + RETURN QUERY SELECT unnest(cusips), unnest(orig_subordination), unnest(curr_subordination); + END; + $$ LANGUAGE plpgsql; + |