diff options
| -rw-r--r-- | sql/dawn.sql | 474 |
1 files changed, 473 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 960a87e9..3e1423ab 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3978,7 +3978,479 @@ AS SELECT tranche_risk.date, period_end_date date NOT NULL, knowledge_date timestamp NULL, ticker_ocs_code text null, - CONSTRAINT isosel_accrued_pk PRIMARY KEY (period_end_date, "row") + fund fund not null + CONSTRAINT isosel_accrued_pk PRIMARY KEY (period_end_date, "row", fund) +); + +CREATE TABLE public.citco_reports ( + subfund text NULL, + trader text NULL, + "prime_broker/clearing_broker" text NULL, + "strategy" text NULL, + citco_security_id text NULL, + symbol text NULL, + cusip text NULL, + isin text NULL, + sedol text NULL, + ric text NULL, + bloomberg_code text NULL, + "long/short" text NULL, + "quantity(start)" int8 NULL, + "quantity(end)" float8 NULL, + base_price float8 NULL, + base_market_value float8 NULL, + base_average_unit_cost float8 NULL, + base_total_cost float8 NULL, + base_commissions float8 NULL, + base_sec_fees float8 NULL, + "report_date_base_realized_p&l" int8 NULL, + "report_date_base_unrealized_p&l" float8 NULL, + "mtd_base_realized_p&l" int8 NULL, + "mtd_base_unrealized_p&l" float8 NULL, + "ytd_base_realized_p&l" int8 NULL, + "ytd_base_unrealized_p&l" float8 NULL, + base_ccy text NULL, + issue_price float8 NULL, + issue_market_value float8 NULL, + issue_average_unit_cost float8 NULL, + issue_total_cost float8 NULL, + issue_commissions float8 NULL, + issue_sec_fees float8 NULL, + "report_date_issue_realized_p&l" int8 NULL, + "report_date_issue_unrealized_p&l" float8 NULL, + "mtd_issue_realized_p&l" int8 NULL, + "mtd_issue_unrealized_p&l" float8 NULL, + "ytd_issue_realized_p&l" int8 NULL, + "ytd_issue_unrealized_p&l" float8 NULL, + issue_ccy text NULL, + security_description text NULL, + security_asset_name text NULL, + security_asset_class text NULL, + maturity_date date NULL, + "number_of_ticks(for_futures)" float8 NULL, + "tick_value(for_futures)" float8 NULL, + underlying_citco_security_id float8 NULL, + underlying_symbol text NULL, + underlying_cusip float8 NULL, + underlying_isin text NULL, + underlying_sedol float8 NULL, + underlying_bloomberg_code text NULL, + underlying_security_description text NULL, + underlying_security_asset_name text NULL, + underlying_security_asset_class text NULL, + region text NULL, + exchange text NULL, + exchange_symbol text NULL, + country_code text NULL, + industry float8 NULL, + industry_sector float8 NULL, + "dividend_accrued/payable" int8 NULL, + "dividend_income/loss" int8 NULL, + "mtd_dividend_income/loss" int8 NULL, + "ytd_dividend_income/loss" int8 NULL, + "issue_dividend_accrued/payable" int8 NULL, + "issue_dividend_income/loss" int8 NULL, + "issue_mtd_dividend_income/loss" int8 NULL, + "issue_ytd_dividend_income/loss" int8 NULL, + issue_trade_acquisition_interest float8 NULL, + mtd_issue_trade_acquisition_interest float8 NULL, + ytd_issue_trade_acquisition_interest float8 NULL, + issue_coupons_received float8 NULL, + mtd_issue_coupons_received float8 NULL, + ytd_issue_coupons_received float8 NULL, + issue_repo_realized_interest float8 NULL, + mtd_issue_repo_realized_interest float8 NULL, + ytd_issue_repo_realized_interest float8 NULL, + issue_bond_accrued_interest float8 NULL, + issue_start_bond_accrued_interest float8 NULL, + mtd_issue_start_bond_accrued_interest float8 NULL, + ytd_issue_start_bond_accrued_interest float8 NULL, + issue_repo_accrued_interest float8 NULL, + issue_start_repo_accrued_interest float8 NULL, + mtd_issue_start_repo_accrued_interest float8 NULL, + ytd_issue_start_repo_accrued_interest float8 NULL, + base_trade_acquisition_interest float8 NULL, + mtd_base_trade_acquisition_interest float8 NULL, + ytd_base_trade_acquisition_interest float8 NULL, + base_coupons_received float8 NULL, + mtd_base_coupons_received float8 NULL, + ytd_base_coupons_received float8 NULL, + base_repo_realized_interest float8 NULL, + mtd_base_repo_realized_interest float8 NULL, + ytd_base_repo_realized_interest float8 NULL, + base_bond_accrued_interest float8 NULL, + base_start_bond_accrued_interest float8 NULL, + mtd_base_start_bond_accrued_interest float8 NULL, + ytd_base_start_bond_accrued_interest float8 NULL, + base_repo_accrued_interest float8 NULL, + base_start_repo_accrued_interest float8 NULL, + mtd_base_start_repo_accrued_interest float8 NULL, + ytd_base_start_repo_accrued_interest float8 NULL, + total_fund_capital float8 NULL, + repo_start_quantity float8 NULL, + mtd_repo_start_quantity float8 NULL, + ytd_repo_start_quantity float8 NULL, + repo_end_quantity float8 NULL, + issue_start_loan_amount float8 NULL, + mtd_issue_start_loan_amount float8 NULL, + ytd_issue_start_loan_amount float8 NULL, + issue_end_loan_amount float8 NULL, + base_start_loan_amount float8 NULL, + mtd_base_start_loan_amount float8 NULL, + ytd_base_start_loan_amount float8 NULL, + base_end_loan_amount float8 NULL, + issue_total_repo_interest float8 NULL, + mtd_issue_total_repo_interest float8 NULL, + ytd_issue_total_repo_interest float8 NULL, + issue_total_bond_interest float8 NULL, + mtd_issue_total_bond_interest float8 NULL, + ytd_issue_total_bond_interest float8 NULL, + base_total_repo_interest float8 NULL, + mtd_base_total_repo_interest float8 NULL, + ytd_base_total_repo_interest float8 NULL, + repo_rate float8 NULL, + base_total_bond_interest float8 NULL, + mtd_base_total_bond_interest float8 NULL, + ytd_base_total_bond_interest float8 NULL, + spot_fx_rate int8 NULL, + standard_strategy float8 NULL, + client_instrument_id text NULL, + issue_start_price float8 NULL, + issue_mtd_start_price float8 NULL, + issue_ytd_start_price float8 NULL, + report_date_base_commissions float8 NULL, + mtd_base_commissions float8 NULL, + ytd_base_commissions float8 NULL, + base_commissions_paid_on_open_positions float8 NULL, + report_date_issue_commissions float8 NULL, + mtd_issue_commissions float8 NULL, + ytd_issue_commissions float8 NULL, + issue_commissions_paid_on_open_positions float8 NULL, + trader_group float8 NULL, + report_date_base_sec_fees float8 NULL, + mtd_base_sec_fees float8 NULL, + ytd_base_sec_fees float8 NULL, + report_date_issue_sec_fees float8 NULL, + mtd_issue_sec_fees float8 NULL, + ytd_issue_sec_fees float8 NULL, + base_deferred_fees_payable float8 NULL, + base_deferred_mgmt_fees_payable float8 NULL, + repo float8 NULL, + "option_type" text NULL, + "option_put/call_flag" text NULL, + option_strike_price float8 NULL, + option_expiration_date float8 NULL, + contract_size float8 NULL, + coupon_rate float8 NULL, + bloomberg_real_time_code text NULL, + bond_abbrev text NULL, + underlying_ric text NULL, + period_end_date date NOT NULL, + knowledge_date date NULL, + option_delta float8 NULL, + "daily_base_p/l" float8 NULL, + "monthly_base_p/l" float8 NULL, + "yearly_base_p/l" float8 NULL, + "daily_issue_p/l" float8 NULL, + "monthly_issue_p/l" float8 NULL, + "yearly_issue_p/l" float8 NULL, + daily_base_income float8 NULL, + monthly_base_income float8 NULL, + yearly_base_income float8 NULL, + daily_issue_income float8 NULL, + monthly_issue_income float8 NULL, + yearly_issue_income float8 NULL, + adjustment_date float8 NULL, + "fund" public."fund" NOT NULL, + future_contract_value float8 NULL, + future_contract_tick_size float8 NULL, + bond_series_code text NULL, + coupon_freq text NULL, + daycount float8 NULL, + day_count_fraction_repo text NULL, + feed_system_code float8 NULL, + factor float8 NULL, + coupon_start_date float8 NULL, + coupon_end_date float8 NULL, + number_of_days_accrued float8 NULL, + security_identifier float8 NULL, + previous_period_accrual_unsettled float8 NULL, + fixing_date float8 NULL, + issue_date float8 NULL, + settle_days float8 NULL, + redeem_price float8 NULL, + day_convention text NULL, + instrument_attributes text NULL, + underlying_currency_code text NULL, + swap_receiving_leg_rate_index text NULL, + swap_receiving_leg_coupon float8 NULL, + swap_receiving_leg_accrual_method text NULL, + swap_receiving_leg_payment_frequency text NULL, + swap_pay_leg_rate_index text NULL, + swap_pay_leg_coupon float8 NULL, + swap_pay_leg_accrual_method text NULL, + swap_pay_leg_payment_frequency text NULL, + bloomberg_id text NULL, + daily_tax_issue_currency int8 NULL, + mtd_tax_issue_currency int8 NULL, + ytd_tax_issue_currency int8 NULL, + daily_tax_base_currency int8 NULL, + mtd_tax_base_currency int8 NULL, + ytd_tax_base_currency int8 NULL, + odd_first_coupon float8 NULL, + odd_last_coupon float8 NULL, + end_ote_issue float8 NULL, + end_ote_base float8 NULL, + swap_start_date float8 NULL, + swap_maturity_date float8 NULL, + swap_counterparty float8 NULL, + swap_receiving_leg_currency text NULL, + swap_receiving_leg_principal float8 NULL, + swap_receiving_leg_payment_calendar text NULL, + swap_pay_leg_currency text NULL, + swap_pay_leg_principal float8 NULL, + swap_pay_leg_payment_calendar text NULL, + swap_pay_leg_rate_source text NULL, + price_factor float8 NULL, + notional_asset_value int8 NULL, + country_name text NULL, + swap_interest int8 NULL, + effective_coupon_rate float8 NULL, + moody_bond_rating text NULL, + "s&p_bond_rating" text NULL, + risk_country_attribute text NULL, + underlying_country_code text NULL, + underlying_country_name text NULL, + underlying_price float8 NULL, + underlying_bloomberg_id text NULL, + underlying_risk_country text NULL, + issue_miscellaneous_fees float8 NULL, + issue_miscellaneous_expenses float8 NULL, + issue_taxes float8 NULL, + base_miscellaneous_fees float8 NULL, + base_miscellaneous_expenses float8 NULL, + base_taxes float8 NULL, + commodity_swap_start_date float8 NULL, + commodity_swap_termination_date float8 NULL, + issue_sec_fees_for_zzinternal_trade float8 NULL, + base_sec_fees_for_zzinternal_trade float8 NULL, + beta_1d float8 NULL, + end_face_value float8 NULL, + "prime_broker/clearing_broker_long_name" text NULL, + "end_date_issue_unrealized_p&l" float8 NULL, + "end_date_base_unrealized_p&l" float8 NULL, + issue_nav_contribution float8 NULL, + base_nav_contribution float8 NULL, + cost_basis_flag text NULL, + occ_code float8 NULL, + underlying_occ_code float8 NULL, + interest_start_date float8 NULL, + trader_abbrev text NULL, + deal_id int8 NULL, + client_deal_id float8 NULL, + deal_type_id float8 NULL, + notes float8 NULL, + pb3 float8 NULL, + report_date_repo_fx float8 NULL, + mtd_repo_fx float8 NULL, + ytd_repo_fx float8 NULL, + report_date_issue_swap_coupon int8 NULL, + mtd_issue_swap_coupon int8 NULL, + ytd_issue_swap_coupon int8 NULL, + report_date_base_swap_coupon int8 NULL, + mtd_base_swap_coupon int8 NULL, + ytd_base_swap_coupon int8 NULL, + swaption_underlyer_maturity_date float8 NULL, + base_swap_interest float8 NULL, + mtd_issue_miscellaneous_fees float8 NULL, + ytd_issue_miscellaneous_fees float8 NULL, + mtd_base_miscellaneous_fees float8 NULL, + ytd_base_miscellaneous_fees float8 NULL, + mtd_issue_miscellaneous_expenses float8 NULL, + ytd_issue_miscellaneous_expenses float8 NULL, + mtd_base_miscellaneous_expenses float8 NULL, + ytd_base_miscellaneous_expenses float8 NULL, + future_death_date float8 NULL, + mtd_issue_taxes float8 NULL, + ytd_issue_taxes float8 NULL, + mtd_base_taxes float8 NULL, + ytd_base_taxes float8 NULL, + report_date_issue_start_acq_interest float8 NULL, + mtd_issue_start_acq_interest float8 NULL, + ytd_issue_start_acq_interest float8 NULL, + report_date_base_start_acq_interest float8 NULL, + mtd_base_start_acq_interest float8 NULL, + ytd_base_start_acq_interest float8 NULL, + "trading_base_unrealized_p&l" float8 NULL, + issue_bond_realized_interest float8 NULL, + mtd_issue_bond_realized_interest float8 NULL, + ytd_issue_bond_realized_interest float8 NULL, + issue_bond_unrealized_interest float8 NULL, + mtd_issue_bond_unrealized_interest float8 NULL, + ytd_issue_bond_unrealized_interest float8 NULL, + base_bond_realized_interest float8 NULL, + mtd_base_bond_realized_interest float8 NULL, + ytd_base_bond_realized_interest float8 NULL, + base_bond_unrealized_interest float8 NULL, + mtd_base_bond_unrealized_interest float8 NULL, + ytd_base_bond_unrealized_interest float8 NULL, + report_date_unrealized_repo_fx float8 NULL, + mtd_unrealized_repo_fx float8 NULL, + ytd_unrealized_repo_fx float8 NULL, + report_date_realized_repo_fx float8 NULL, + mtd_realized_repo_fx float8 NULL, + ytd_realized_repo_fx float8 NULL, + red text NULL, + user_id text NULL, + fund_name text NULL, + instrument_start_date float8 NULL, + exchange_principal text NULL, + tick_size float8 NULL, + underlying_tick_size float8 NULL, + swap_float_leg_coupon float8 NULL, + swap_reset_rate float8 NULL, + cds_attachment_points float8 NULL, + cds_detachment_points float8 NULL, + cds_credit_event float8 NULL, + barrier1 float8 NULL, + barrier2 float8 NULL, + barrier_option_window_1_start_date float8 NULL, + barrier_option_window_1_end_date float8 NULL, + swaption_underlyer_start_date float8 NULL, + swaption_underlyer_coupon_frequency float8 NULL, + option_expiration_value_date float8 NULL, + underlying_red text NULL, + swap_receive_leg_rate_source text NULL, + fas_157 text NULL, + swaption_receiving_leg_rate_source text NULL, + swaption_receiving_leg_payment_frequency text NULL, + swaption_receiving_leg_coupon_rate float8 NULL, + swaption_pay_leg_rate_source text NULL, + swaption_pay_leg_payment_frequency text NULL, + swaption_pay_leg_coupon_rate float8 NULL, + instrument_subtype text NULL, + close_price float8 NULL, + underlying_cds_maturity_date float8 NULL, + underlying2_citco_security_id float8 NULL, + underlying2_symbol text NULL, + underlying2_cusip float8 NULL, + underlying2_isin float8 NULL, + underlying2_sedol float8 NULL, + underlying2_bloomberg_code float8 NULL, + underlying2_ric float8 NULL, + underlying2_bloomberg_id float8 NULL, + underlying2_red float8 NULL, + "gross_dividend_income/loss" float8 NULL, + "mtd_gross_dividend_income/loss" float8 NULL, + "ytd_gross_dividend_income/loss" float8 NULL, + "issue_gross_dividend_income/loss" float8 NULL, + "issue_gross_mtd_dividend_income/loss" float8 NULL, + "issue_gross_ytd_dividend_income/loss" float8 NULL, + "repo_type" text NULL, + repo_start_date float8 NULL, + "gic_level_1_(sector)" float8 NULL, + "gic_level_2_(industry_group)" float8 NULL, + "gic_level_3_(industries)" float8 NULL, + "gic_level_4_(sub-industries)" float8 NULL, + repo_bcusip float8 NULL, + dividend_explorer_txn_id float8 NULL, + swap_div_eligibility_date float8 NULL, + issue_end_loan_amount_td float8 NULL, + base_end_loan_amount_td float8 NULL, + trade_ccy text NULL, + buy_cur_for_fxfwd text NULL, + sell_cur_for_fxfwd text NULL, + eod_rolled_nav_balance float8 NULL, + bloomberg_ticker text NULL, + start_fx_rate int8 NULL, + "p&l_(fx)" float8 NULL, + market_capital float8 NULL, + gl_account float8 NULL, + gl_account_name text NULL, + "p&l_type" text NULL, + net_cost float8 NULL, + "net_cost_[n]" float8 NULL, + issue_client_market_value float8 NULL, + custom_user_id text NULL, + pb_bloomberg_ticker float8 NULL, + "cost_[n]" float8 NULL, + "cost" float8 NULL, + bond_accr float8 NULL, + "bond_accr_[n]" float8 NULL, + swap_accrued int8 NULL, + "swap_accrued_[n]" int8 NULL, + instr_end_fx_rate float8 NULL, + death_date float8 NULL, + "tickr/loanx" float8 NULL, + strike_price float8 NULL, + global_client_instrument_id text NULL, + "daily_p&l_(fx)" int8 NULL, + "mtd_p&l_(fx)" int8 NULL, + "ytd_p&l_(fx)" int8 NULL, + mtd_swap_interest int8 NULL, + ytd_swap_interest int8 NULL, + repo_start_origin_date float8 NULL, + repo_end_date float8 NULL, + "attr/dm_template_name" float8 NULL, + "attr/pricing_frequency" float8 NULL, + "attr/fas_161_primary_risk_category" float8 NULL, + "attr/fas_161_tradinghedging" float8 NULL, + "attr/analyst" float8 NULL, + "attr/country_status" float8 NULL, + "attr/va_co_override" float8 NULL, + "attr/va_gic_exclude" float8 NULL, + "attr/va_industry_override" float8 NULL, + "attr/va_sector_override" float8 NULL, + "attr/beta" float8 NULL, + "attr/kcm_instrument" float8 NULL, + "attr/kcm_symbol" float8 NULL, + "attr/kcm_issuer" float8 NULL, + "attr/kcm_investment_type" float8 NULL, + "attr/kcm_investment_direc" float8 NULL, + "attr/kcm_risk_country" float8 NULL, + "attr/kcm_region" float8 NULL, + "attr/kcm_sr_pm" float8 NULL, + "attr/kcm_pm" float8 NULL, + "attr/kcm_sr_analyst" float8 NULL, + "attr/kcm_analyst" float8 NULL, + "attr/kcm_1st_trade_date" float8 NULL, + "attr/kcm_investment_type2" float8 NULL, + "attr/kcm_descr_by_issuer" float8 NULL, + "attr/kcm_attribution_tagg" float8 NULL, + "utckr/bloomberg_real_time_code" text NULL, + base_start_loan_amount_td float8 NULL, + issue_start_loan_amount_td float8 NULL, + rate_start_date float8 NULL, + rate_set_date float8 NULL, + bloomberg_global_id text NULL, + instrument_attribute_name text NULL, + issue_swap_interest int8 NULL, + mtd_issue_swap_interest int8 NULL, + ytd_issue_swap_interest int8 NULL, + "base_misc_bal_p&l" float8 NULL, + "mtd_base_misc_bal_p&l" float8 NULL, + "ytd_base_misc_bal_p&l" float8 NULL, + "issue_misc_bal_p&l" float8 NULL, + "mtd_issue_misc_bal_p&l" float8 NULL, + "ytd_issue_misc_bal_p&l" float8 NULL, + fund_tid text NULL, + repo_ccy float8 NULL, + "tckr/cins" float8 NULL, + bond_class text NULL, + revolver text NULL, + "total_p&l" float8 NULL, + repo_end_qty_td float8 NULL, + repo_start_qty_td float8 NULL, + fund_underlying_tid text NULL, + "daily_acq_int_(fx)" float8 NULL, + "mtd_acq_int_(fx)" float8 NULL, + "ytd_acq_int_(fx)" float8 NULL, + asc_820_level float8 NULL, + "attr/kcm_strategy" text NULL, + end_last_price_source text NULL, + strategy_group text NULL, + "row" int8 NOT NULL, + CONSTRAINT citco_reports_pkey PRIMARY KEY (period_end_date, "row", fund) ); |