Untitled
unknown
plain_text
5 months ago
11 kB
6
Indexable
config = {
"esdl_trade": {
"source_query": "select * from table_name_param where\n"
"yyyy='year_param' and\n"
"mm='month_param' and\n"
"dd='date_param' and\n"
"hub_load_time_utc='hub_load_time_utc_param' and active='Yes' and\n"
"transdescription in "
"('Purchase','Sale', 'FX Forward Opening', 'FX Forward Closing', 'Maturity', "
"'Miscellaneous Income', 'Miscellaneous Expense', 'Shareholder Subscription', 'Shareholder Redemption') \n"
"and poststatusdesc ='Posted';",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"securities": {
"join_column": "securityid",
"filter_conditions": "transdescription in ('Purchase', 'Sale')",
},
"portfolio": {"join_column": "portfolioid"},
"brokercode": {
"join_column": "brokercode",
"broker_system_name": "ePAM SE ASIA",
},
},
"cc_emails": "DLMYinstitutionalsettlements@principal.com,DLMYinstitutionalreporting@principal.com",
},
"pfi_port_cash": {
"source_query": "SELECT asofdate,portfolio,securityid,bankaccountcurrency,units FROM pfi_port_cash where \n"
"yyyy='year_param' and \n"
"mm='month_param' and \n"
"dd ='date_param' and \n"
"hub_load_time_utc='hub_load_time_utc_param' and \n"
"active='Yes' and \n"
"units <> 0;",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"portfolio": {
"join_column": "portfolio"
}
},
},
"esdl_ffsdm_pos": {
"source_query": "select asofdate, portfolio, securityid, isocurrency,\n"
"sum(units) as units, sum(cost) as cost, sum(marketvalue) as marketvalue,\n"
"sum(unrealcapgainloss) as unrealcapgainloss,\n"
"sum(accruedinterest) as accruedinterest, majorassettypedesc\n"
"from table_name_param where\n"
"yyyy='year_param' and\n"
"mm='month_param' and\n"
"dd='date_param' and\n"
"hub_load_time_utc='hub_load_time_utc_param' and active='Yes' and\n"
"marketvalue <> 0 and majorassettypedesc in ('Fixed Income','Common Stock', 'Preferred Stock')\n"
"group by asofdate, portfolio, securityid, isocurrency, majorassettypedesc;",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"securities": {
"join_column": "securityid",
},
"portfolio": {
"join_column": "portfolio",
},
},
"cc_emails": "",
},
"esdl_ffsut_pos": {
"source_query": "select asofdate, portfolio, securityid, isocurrency,\n"
"sum(units) as units, sum(marketvalue) as marketvalue,\n"
"sum(accruedinterest) as accruedinterest,maturitydate, majorassettypedesc\n"
"from table_name_param where\n"
"yyyy='year_param' and\n"
"mm='month_param' and\n"
"dd='date_param' and\n"
"hub_load_time_utc='hub_load_time_utc_param' and active='Yes' and\n"
"majorassettypedesc in ('Fixed Income','Common Stock', 'Preferred Stock','Money Market')\n"
"group by asofdate, portfolio, securityid, isocurrency,maturitydate, majorassettypedesc ;",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"securities": {
"join_column": "securityid",
},
"portfolio": {
"join_column": "portfolio",
},
},
"cc_emails": "",
},
"esdl_tb": {
"source_query": "select codename, closedate, currency, \n"
"sum(cast(balance as double)) balance \n"
"from table_name_param where \n"
"yyyy='year_param' and \n"
"mm='month_param' and \n"
"dd ='date_param' and \n"
"hub_load_time_utc='hub_load_time_utc_param' and \n"
"active='Yes' \n"
"group by codename, closedate, currency;",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"portfolio": {"join_column": "codename"},
},
},
"esdl_bport_fx": {
"source_query": "select * from table_name_param where \n"
"yyyy='year_param' and \n"
"mm='month_param' and \n"
"dd ='date_param' and \n"
"hub_load_time_utc='hub_load_time_utc_param' and \n"
"active='Yes' and \n"
"majorassettypedesc = 'Forward Currency' and \n"
"transdescription = 'FX Forward Opening' and settlementdate >= tradedate and \n"
"purposeindicatordesc in ('Hedge','Speculative');",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"portfolio": {
"join_column": "portfolioid",
"filter_conditions": {"transdescription": ["FX Forward Opening"]},
},
"brokercode": {
"join_column": "brokercode",
"broker_system_name": "ePAM SE ASIA",
},
},
},
"esdl_bport_pos": {
"source_query": "select asofdate, portfolio, securityid, isocurrency,'' as acqtradedate, \n"
"sum(units) as units, sum(marketvalue) as marketvalue, \n"
"sum(accruedinterest) as accruedinterest, price, maturitydate, majorassettypedesc \n"
"from table_name_param where \n"
"yyyy='year_param' and \n"
"mm='month_param' and \n"
"dd ='date_param' and \n"
"hub_load_time_utc='hub_load_time_utc_param' and \n"
"active='Yes' and \n"
"majorassettypedesc in ('Fixed Income','Common Stock', 'Preferred Stock','MBS') \n"
"group by asofdate, portfolio, securityid, isocurrency,"
"price,maturitydate, majorassettypedesc \n"
"union \n"
"select asofdate, portfolio, securityid, isocurrency,acqtradedate, \n"
"sum(units) as units, sum(marketvalue) as marketvalue, \n"
"sum(accruedinterest) as accruedinterest, price, maturitydate, majorassettypedesc \n"
"from table_name_param where \n"
"yyyy='year_param' and \n"
"mm='month_param' and \n"
"dd ='date_param' and \n"
"hub_load_time_utc='hub_load_time_utc_param' and \n"
"active='Yes' and \n"
"majorassettypedesc in ('Money Market') \n"
"group by asofdate, portfolio, securityid, isocurrency, price, \n"
"maturitydate, majorassettypedesc, acqtradedate; ;",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"securities": {
"join_column": "securityid",
"filter_conditions": "majorassettypedesc in ('Fixed Income', 'Common Stock', 'Preferred Stock', \
'MBS', 'Money Market')",
},
"portfolio": {"join_column": "portfolio"},
},
},
"tax_lot_pos": {
"source_query": "select portfoliocode, securityid, price, \n"
"sum(units) as units, sum(marketvalue) as marketvalue, \n"
"sum(cost) as cost, sum(carryvaluegaap) as carryvaluegaap, \n"
"sum(bookvalue) as bookvalue, sum(accruedinterest) as accruedinterest \n"
"from table_name_param where \n"
"yyyy='year_param' and \n"
"mm='month_param' and \n"
"dd ='date_param' and \n"
"hub_load_time_utc='hub_load_time_utc_param' and \n"
"active='Yes' \n"
"group by portfoliocode, securityid, price;",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"securities": {"join_column": "securityid"},
"portfolio": {"join_column": "portfoliocode"},
},
},
# FNZ
"sub_red": {
"source_query": "select * from table_name_param where hub_load_time_utc='hub_load_time_utc_param'"
" and "
"yyyy='year_param' and "
"mm='month_param' and "
"dd='date_param';",
"db_name": "prinam_my_fa_fnz",
"system_name": "FNZ",
"references_master_data": {
"portfolio_share_class": {
"portfolio_join_column": "portfolioid",
"share_class_join_column": "class",
}
},
},
"esdl_bport_trade": {
"source_query": "select securityid, portfolioid, brokercode, transdescription from table_name_param where "
"yyyy ='year_param' and "
"mm ='month_param' and "
"dd ='date_param' and "
"hub_load_time_utc = 'hub_load_time_utc_param' and active = 'Yes' and "
"((transdescription in ('Purchase', 'Security Contribution','Sale') and "
"majorassettypedesc in ('Fixed Income','Money Market','MBS','Common Stock','Preferred Stock')) or "
"(transdescription = 'FX Forward Opening' "
"and purposeindicatordesc in ('Hedge','Speculative','Cover','Link Group')) or "
"(transdescription in ('Shareholder Subscription', 'Shareholder Redemption'))) "
"and poststatusdesc ='Posted';",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"securities": {
"join_column": "securityid",
"filter_conditions": "transdescription in ('Purchase', 'Security Contribution', 'Sale')",
},
"portfolio": {"join_column": "portfolioid"},
"brokercode": {
"join_column": "brokercode",
"broker_system_name": "ePAM SE ASIA",
},
},
},
"esdl_bport_cash": {
"source_query": "select portfolio from table_name_param "
"where yyyy='year_param' and mm='month_param' and dd='date_param' "
"and hub_load_time_utc='hub_load_time_utc_param' and units <> 0 and active='Yes';",
"db_name": "prinam_my_fa_epam",
"system_name": "ePAM",
"references_master_data": {
"portfolio": {"join_column": "portfolio"},
},
},
"crd_esdl": {
"source_query": "select acct_cd, sec_typ_cd, split_part(ext_sec_id, ' ', 1) ext_sec_id, "
"broker_code from table_name_param where "
"yyyy ='year_param' and "
"mm ='month_param' and "
"dd ='date_param' and "
"hub_load_time_utc = 'hub_load_time_utc_param' and "
"trans_type in ('SELLL', 'BUYL') and status in ('ACCT', 'CNCLACCT');",
"db_name": "prinam_my_fa_crims",
"system_name": "CRIMS",
"references_master_data": {
"securities": {
"join_column": "ext_sec_id",
"filter_conditions": "not sec_typ_cd.str.startswith('1CUR')",
},
"portfolio": {"join_column": "acct_cd"},
"brokercode": {
"join_column": "broker_code",
"broker_system_name": "CRIMS SE ASIA",
"is_fill_empty_broker": "No",
},
},
},
}
Editor is loading...
Leave a Comment