ESP-358: STAR v2017 EagleML - Inbound Cash Tender, Cash Dividend and PIK Bond Corporate Action Announcement fails - required tagfailing with required To Security Alias Tag302
When loading Accounting Corporate Actions on version 2017 there were two errors related to changes made errors in the security resolution for certain transaction types. A <transType> node with values of ‘TENDER’ of ‘PIKBOND’ would cause the following error: 'To Security Alias: Value required'. Alternatively values of ‘ADDPUT’ and ‘ADDCALL’ give this would procedure the error: 'Security Does Not Have Call or Put or Refund or Lottery Bond Provisions and is not a Private Placement'.
Due to the changes to security resolution some required mappings were not present in the corresponding panels used for preocessing. For all the transTypes listed below, the mappings for Security Alias (tag10) and To Security Alias (tag302) were added. For the ‘ADDPUT’ and ‘ADDCALL’ transTypes the additional mappings for the Processing Security Type (tag3931), Call Flag (tag1182), Put Flag, (tag1546), Muni Refunding Indicator (tag3132), Lottery Flag (tag11303) and Private Placement (tag1421) were added to the pan-addcallput.htm panel.
• varTransType = CASHDIV - panel pan-acorpactcashdiv.htm
• varTransType = ADDPUT or ADDCALL - panel pan-addcallput.htm
• varTransType = TENDER - panel pan-addmcshtender.htm
• varTransType = STOCKDIV - panel pan-addstockdiv.htm
• varTransType = CREDITEVENT - panel pan-addcreditevt.htm
• varTransType = ADDFACTOR or FACTOR - panel pan-addfactorca.htm
• varTransType = BSSB - panel pan-addbssb.htm
• varTransType = ADMIN - panel pan-addnamechg.htm
• varTransType = NOMINAL or NOMVALCHG - panel pan-addnominal.htm
• varTransType = SPLIT - panel pan-addstocksplit.htm
• varTransType = SINKSCHED - panel pan-addscheduleca.htm
• varTransType = ROC - panel pan-addroc.htm
• varTransType = PIKBOND - panel pan-addpikbnd.htm
• varTransType = PIK - panel pan-addpayinkind.htm
...
ESP-359: EagleML: Re-design xml-ref_smf_factor to not to use the Corporate Actions loading load and regenerate binds based on env environments core SP stored procedure for Factor
There was a request to change the inbound logic for the factor object.
...
ESP-361: EagleML - eagle_wrf_generic_extract_accounting_objects.wrf missing missinga parameter for the accountingvalidationflag when processing the UNAPPROVEDVARMARGINEXTRACT
Both the stored procedure and dynamic SQL modes for the UNAPPROVEDVARMARGINEXTRACT were failing if the todate or enddate filters were present in the run task request.
This issue was related to the missing replace logic for the “-“ symbol in the date format. This was fixed so that corrected to apply the date filters are passed with correct date formats.
...
The parameters for transType, cpStatus, clientSpecificCAId, cpReason, sweepdate, fromsweepdate, and tosweepdate were all added to the core rules so that to enable the corresponding filters could to be used in the extract.Files changed:
- extract_generic_corporate_actions.xml
Example of RTR
Example of RTR:
<EagleML xsi:type="RunTaskRequestSync" eaglemlType="RunTaskRequest" eaglemlVersion="2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<header>
<messageId>CC59ADD40E1E069</messageId>
<sentBy>user</sentBy>
<sendTo>http://www.eagleinvsys.com/eagle_ml-2-0_default_cm_control_message</sendTo>
<replyTo>http://www.client.com/Eagle Control Messages</replyTo>
</header>
<taskIdentifier>
<correlationId>ESP-366_GENCORPACTIONSEXTRACT_XML</correlationId>
<businessTaskId>Generic_Corporate_Action_Extract</businessTaskId>
</taskIdentifier>
<taskTypeEnum>LOAD</taskTypeEnum>
<taskParameters>
<taskParameter>
<name>ActionType</name>
<dataType>S</dataType>
<value>EXTRACT</value>
</taskParameter>
<taskParameter>
<name>StreamName</name>
<dataType>S</dataType>
<value>eagle_ml-2-0_default_out_q</value>
</taskParameter>
<taskParameter>
<name>FeedType</name>
<dataType>S</dataType>
<value>GENCORPACTIONSEXTRACT</value>
</taskParameter>
<taskParameter>
<name>fromsweepdate</name>
<dataType>S</dataType>
<value>2017-01-01</value>
</taskParameter>
<taskParameter>
<name>tosweepdate</name>
<dataType>S</dataType>
<value>2018-06-02</value>
</taskParameter>
<taskParameter>
<name>transType</name>
<dataType>S</dataType>
<value>CASHDIV</value>
</taskParameter>
<taskParameter>
<name>cpReason</name>
<dataType>S</dataType>
<value>CASHDIV</value>
</taskParameter>
<taskParameter>
<name>cpStatus</name>
<dataType>S</dataType>
<value>RD</value>
</taskParameter>
<taskParameter>
<name>clientSpecificCAId</name>
<dataType>S</dataType>
<value> CASHDIV_1001</value>
</taskParameter>
<taskParameter>
<name>maxrows</name>
<dataType>S</dataType>
<value>20</value>
</taskParameter>
</taskParameters>
</EagleML>
Files changed:
- extract_generic_corporate_actions.xml
- extract_generic_corporate_actions.inc
- extract_filter_date.inc
...
The eagle_default/in/xml/xml-ref_issuerating_all.xml rules was updated with new logic to include the rating_data_model XS.
To accomplish this the The reference to an autoinclude was necessary added in the \xml-ref_issuerating_all.xml file.
...
ESP 373: EagleMl: Issuer Role - Skip returned record from the issuer_query_result if the returned issuer_alias doesn't does not match the incoming issuer alias
...
There was an issue with the local time and server time being different. This resulted in days off and specific file delivery times to be inconsistent.
To avoid this issue support Support for a time zone sensitive business calendar was introduced. This functionality is only available for the Unix platform. The values for this calendar’s parameter can be specified in the w_config_sustom.inc file.
...
ESP-380: EagleML - Enhancement Request to extract data from the securitydbo.NON_UNIQUE_XREFID table
The A new ‘instance’ field was added to the SMFIdentifierConfig interfaces.
An extract for this interface was then created by building an outbound rule. The feed type is XREFCONFIGEXTRACT and it is supported in both Oracle and MSSQL but is only available in streaming mode.
...
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\out\tagvalue\extracts.rsf
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\out\tagvalue\extract_ref_smfidentifierconfig.xml
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\out\tagvalue\extract_ref_smfidentifierconfig.inc
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\out\xml\t_eagleml-2-0_streaming.rsf
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\out\xml\t_ref_smfidentifierconfig_streaming.xml
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\out\xml\t_ref_smfidentifierconfig_streaming.inc
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\extract_upd_timestamp.inc
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\extract_filter_smf.inc
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\extract_fields_query.inc
estar\tpe\dynamic\msgcenter\eagle_ml-2-0_cm\w_config.inc
estar\tpe\dynamic\eagleml\schema\eagleml-shared-2-0.xsd
estar\tpe\dynamic\eagleml\schema\eagleml-ref-2-0.xsd
eagle\estar\tpe\dynamic\msgcenter\eagle_ml-2-0_workflows\eagle_wrf_generic_load
eagle\estar\tpe\dynamic\msgcenter\eagle_ml-2-0_workflows\eagle_wrf_generic_load_system_objects
eagle\estar\tpe\dynamic\msgcenter\eagle_ml-2-0_workflows\eagle_wrf_generic_extract
eagle\estar\tpe\dynamic\msgcenter\eagle_ml-2-0_workflows\eagle_wrf_generic_extract_reference_objects_ind
eagle\estar\tpe\dynamic\msgcenter\eagle_ml-2-0_workflows\eagle_wrf_generic_extract_system_objects
ESP-429: EagleML: Need to make Make issuer relution resolution consistent in all all issuer-related rules
The issuer resolution logic was changed for the elementOverride, entityIssuerRelationship, issuerRegulatory, and issuerRelationship objects.
...
There was a request to add the ability of loading price records to the RDC.into Reference Data Center (RD)
A new flag and global variable were introduced to allow loading prices to RDC. The flag is: useRdcPricing and the variable is W_USE_RDC_PRICING (located in w_config.inc). the following new elements were added to the inbound rule: openPriceCondition, dirtyPriceCondition, commentsOverrideFlag.
...
A new Chain Extract task was added to the generic extract workflow. This task can run multiple extracts sequentially which lowers the load on the system due to less files being created. For small datasets this is the preferred method of extraction especially targeted for a single entity being extracted.
To enable this functionality the run task request should include the following parameter:
<taskParameter>
<name>ChainExtract</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
...
To accomplish this the W_UNDERLYINGSMF_DELETE_REINSERT_SWITCH global variable and underlyingDeleteReinsertSwitch RTR parameter were introduced. With a value of “Y” these parameters allow deleting an underlying SMF record based on the security alias and issuer alias. The new records will then be loaded. .
<taskParameter>
<name>underlyingDeleteReinsertSwitch</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
Files changed:
xml-smf_underling_common.inc
xml-smf_underling.xml
...
ESP-985: EagleML - Load Add Entity failure for Control Center mapping
The functionality around adding an entity was failing when attempting to load Control Center related elements. The ccDailyMap and ccMeMap elements were mapped incorrectly in the Control Center data block in entity records.
The new ccDailyMapName and ccMeMapName elements were added. Mappings for the ccDailyMap and ccMeMap elements were added along with resolution logic for these fields based on their names. Finally the controlCenterEntityTests element was were moved from the <acctBasis> node to the <genericEntity> root node.
...
To resolve, moved the EAB_LEDGER_GRAN_RULE_NAME and the ESP_ENTITY_SOURCE_RULE_NAME fields definition under the IncludeEntityRuleKeys filter and set the profile check to include the IncludeEntityRuleKeys fields in common request. The inclusion of the IncludeEntityRuleKeys set to Y is required to return these elements.
Set
A profile check was added for the IncludeExtendedDetails filter.
...
ESP-1069: Duplicate messages from the generic extractor and compressing issuecompression
There was an issue with the compression and distribution logic which was causing producing duplicated archived files when using the JMS stream to run the extracts.
Compression and distribution logic was added introduced to the generic extract workflow which would override the global and RTR settings for these functionalities. This was accomplished by adding default values for the Compress and ExtractDeliveryMethod parameters.
...
A new option for specifying the compression type for a single extract file was introduced into Generic Extract Workflow. As a result each Each extract file will willnow support being able to be created in a separate archive.
<taskParameter>
<name>SingleExtractCompress</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
This parameter relates with option:
<taskParameter>
<name>Compress</name>
<dataType>S</dataType>
<value>GZIP</value>
</taskParameter>
...
There was an issue that would occur if the compression functionality was launched in SingleExtract mode. This would result in several extracts with the same name to write to the same files instead of creating individual ones.
To correct this the The files inside the archive now have the process Id added to the file name so that all the individual files are created with the proper contents.
...
The Accounting Rejection Extract which was recently added received new database binds for some of its filter parameters.
...
Files changed:
- extract_acct_rejection.inc
ESP-1420: EagleML - GLACTIVITYEXTRACT - @RecordCount on the GLACTIVITYEXTRACT is not matching the DB count
If the When ExtractNamePatternEx parameter is included in the RTR used to kick of the generic extract workflow the record count on the GLACTIVITYEXTRACT would not match the DB count.
This issue was introduced as part of the May2018 release and changes were rolled back this release to correct the logic for the :totalCount: variable which is responsible for the record count. These changes allowed the record count to once again match the DB count.
List of files changed:
eagle_ml-2-0_cm/extract_filter_gl_activity.inc
eagle_ml-2-0_cm/out/xml/t_acct_gl_activity.xml
eagle_ml-2-0_cm/out/xml/t_acct_gl_activity_streaming.inc
ESP-1426: allow to provide xrefId and xrefIdType instead of entityId in KEY files for warehouse objects
Warehouse objects rely on a file key file to o perform batch roll up and this key file had to include the entity_id and not the XrefAccountID.
An option to use the xref Id and xref type in the key file was added. The <ENTITY_XREF> node was introduced to allow resolution of the entity id based on the xrefs. The XMLDBAPI request now looks as follows:
<LOT_EGL_GEN_INTERFACE>
<EagleDBMLRequest>
<BATCH_KEY>ENTITYID^EFFECTIVE_DATE^SRC_INTFC_INST</BATCH_KEY>
<ENTITY_XREF>XREFACCOUNTID^XREFACCOUNTIDTYPE</ENTITY_XREF>
<REQUEST>
<MSG_TYPE>MTADMIN</MSG_TYPE>
<SUB_MSG_TYPE>ROLLUP_EGL_GEN_INTERFACE</SUB_MSG_TYPE>
</REQUEST>
</EagleDBMLRequest>
</LOT_EGL_GEN_INTERFACE>
The eofp rule will typically automatically run the roll up once the warehouse record has been loaded. To disable this functionality, set the W_ROLLUP_FLAG global variable was set to “N”. This allowed allows executing the roll up with the key file.
...
estar\tpe\dynamic\msgcenter\eagle_default\in\xml\ xml-dbdirect_invar_rollup.xml
ESP-1427: @recordCount in filename not matching test bed records for the WRHSOPENLOTEXTRACT when passing the <IncludeAccrualRate> task parameter
ESP-1543: Totalcount in extract file name mismatch the real records count
The AccrualIncome sub-query logic was changed to return only one value for any OPEN_EVENT_ID or TRADE_DT. This value will be derived by taking the larger value from the ESTAR_INCOME_ACTIVITY.TRADE_DT and ESTAR_INCOME_ACTIVITY.OPEN_EVENT_ID fields. fields. This logic is triggered when the IncludeAccrualRate parameter is included.
<taskParameter>
<name>IncludeAccrualRate</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
This change hindered performance slightly since neither one of the queried fields are indexed. If custom indexes for these fields are added the performance will be improved.
Before:
left outer join (SELECT COUPON,OPEN_EVENT_ID,TRADE_DT FROM ESTAR.ESTAR_INCOME_ACTIVITY
WHERE INSTANCE NOT IN (SELECT INSTANCE FROM ESTAR.ESTAR_CXL_INCOME_ACTIVITY) GROUP BY OPEN_EVENT_ID, TRADE_DT,COUPON ) ESTAR_INCOME_ACTIVITY
on LOT_LEVEL_POSITION.ORIG_LOT_NUMBER = ESTAR_INCOME_ACTIVITY.OPEN_EVENT_ID AND
LOT_LEVEL_POSITION.EFFECTIVE_DATE = ESTAR_INCOME_ACTIVITY.TRADE_DT
After:
left outer join (SELECT COUPON,OPEN_EVENT_ID,TRADE_DT FROM ESTAR.ESTAR_INCOME_ACTIVITY,
( SELECT max(INSTANCE) INSTANCE FROM ESTAR.ESTAR_INCOME_ACTIVITY
WHERE INSTANCE NOT IN (SELECT INSTANCE FROM ESTAR.ESTAR_CXL_INCOME_ACTIVITY)
GROUP BY OPEN_EVENT_ID, TRADE_DT) SUB_ESTAR_INCOME_ACTIVITY WHERE ESTAR.ESTAR_INCOME_ACTIVITY.INSTANCE = SUB_ESTAR_INCOME_ACTIVITY.INSTANCE) ESTAR_INCOME_ACTIVITY
on LOT_LEVEL_POSITION.ORIG_LOT_NUMBER = ESTAR_INCOME_ACTIVITY.OPEN_EVENT_ID AND
LOT_LEVEL_POSITION.EFFECTIVE_DATE = ESTAR_INCOME_ACTIVITY.TRADE_DT
ESP 1554: EagleM: Issue with logic for the tag3931/tag11/tag14/tag1432/85/961 in the underlying_smf_ins_upd.
...
The W_UNDERLYINGSMF_AUTO_ENRICH_DATA_SWITCH global variable and underlyingAutoEnrichDataSwitch RTR Control Message parameter were added to control this functionality. If either has a value of “Y” the underlying SMF will be created and loaded based on the generic SMF fields: tag3931, tag11, tag1432, tag85, tag961based on the generic SMF fields: tag3931, tag11, tag1432, tag85, tag961
Set in the w_config_custom.inc to run globally across the full environment:
<COL TAG=" W_UNDERLYINGSMF_AUTO_ENRICH_DATA_SWITCH" EXPRESSION="'Y'"/>
To set in an individual Control Message:
<underlyingAutoEnrichDataSwitch>Y</underlyingAutoEnrichDataSwitch>
Files changed:
underlying_smf_ins_upd.inc
ESP-1561: WRHSPOSITIONEXTRACT - Revise Portfolio Valuation Daily Query to maximize use of the Table Index
ESP-1562: WRHSOPENLOTEXTRACT - Revise Portfolio Valuation Daily Query to maximize use of the Table Index
ESP-1563: WRHSOPENLOTEXTRACT - Revise Portfolio Valuation Monthly Query to maximize use of the Table Index
ESP-1564: WRHSPOSITIONEXTRACT - Revise Portfolio Valuation Monthly Query to maximize use of the Table Index
All these items relate During throughput testing, an optimization was identified related to a single sub-query for the PREV_EFFECTIVE_DATE field in the Portfolio Valuation extract.
A left join in the sub-query was changed to an inner join which will increase performance. The join to the main table is now done based on the POSITION_DETAIL_ID field for the positions extract. Since this field is indexed it works quicker more efficiently than the Local Currency and Long Short Indicator fields were.
Changed files:
extract_holding_ext_port_valuation_fields.inc
...
ESP-1566: ENTITYACCOUNTINGSTATUSEXTRACT - need modification to resolve entityid to it's master when an individual sector id is requested
The entity extract was returning no data if the run task request included a sector filter.
An inner join in the sub-select as well as some conditional logic for the SECTOR_TYPE field were removed from the outbound rule. Incorrect mappings in this rule were also fixed.
This change enables the ability to pass in an individual sector and it will display the status of its Master
Files changed:
extract_entity_accounting_status.inc
extract_entity_fields.inc
ESP-1615: open Open lot with extension stored procedure is created incorrectly
...
To grab the core stored procedure there is a query to the source table, but since clients can have varying permission levels to certain database tables, this query would occasionally not return any data due to access rights.
To get around these restrictions a second Asecond table (dba_source) was created and is now queried in the case the first query returns no data. This allows a client to consistently be able to retrieve the core stored procedure regardless of the database table permissions.
All changes were made in the file:
generate_sp_on_fly.inc
ESP-1616: Add Newly Created Decryption Code to the Core EML Stream
Added decryption to the logic in the eagle_ml-2-0_default_cm_ndfa_exec stream. The stream is now capable of decrypting all files with gpg and pgp extensions.
By default this functionality is only applied to files that are using extensions but this can be controlled with the W_NDFA_INCOMING_ENCRYPTED global variable. The following values have the corresponding behaviors:
* EXTENSION BASED or no value - extensions onlyis only applied to files that are using extensions but this can be controlled with the W_NDFA_INCOMING_ENCRYPTED global variable. The following values have the corresponding behaviors:
* EXTENSION BASED or no value - extensions only. This enables mode when only files with .gpg or .pgp extension (case insensitive) are considered as encrypted files and decryption is performed on files with this kind of extension. Type of decryption utility to use (gpg or pgp) is determined based on the file extension.
* ALL - handle all files as
...
encrypted. The type of decryption is determined by region configuration.
* NONE - disable decryption of all incoming files.
ESP-1653: Support new field elements available in the WRHSTRADEEXTRACT
The following columns were requested as additions to the WRHSTRADEEXTRACT:TRADE_ID NUMBER(38) WRHSTRADEEXTRACT: from the TRADESDBO.TRADE_EXT table:
ElementName | DBField | Field Type / Length |
stGainSecLocal | ST_GAIN_SEC_L | NUMBER(38,12) |
...
stLossSecLocal | ST_LOSS_SEC_L | NUMBER(38,12) |
...
stGainSecBase | ST_GAIN_SEC_B | NUMBER(38,12) |
...
stLossSecBase | ST_LOSS_SEC_B | NUMBER(38,12) |
...
stGain | ST_GAIN | NUMBER(38,12) |
...
stLoss | ST_LOSS | NUMBER(38,12) |
...
stGainCurrency | ST_GAIN_CRRCY | NUMBER(38,12) |
...
stLossCurrency | ST_LOSS_CRRCY | NUMBER(38,12) |
...
ltGainSecLocal | LT_GAIN_SEC_L | NUMBER(38,12) |
...
ltLossSecLocal | LT_LOSS_SEC_L | NUMBER(38,12) |
...
ltGainSecBase | LT_GAIN_SEC_B | NUMBER(38,12) |
...
ltLossSecBase | LT_LOSS_SEC_B | NUMBER(38,12) |
...
ltGain | LT_GAIN | NUMBER(38,12) |
...
ltLoss | LT_LOSS | NUMBER(38,12) |
...
ltGainCurrency | LT_GAIN_CRRCY | NUMBER(38,12) |
...
ltLossCurrency | LT_LOSS_CRRCY | NUMBER(38,12) |
...
clsAmortLocal | CLS_AMORT_LOCAL | NUMBER(38,12) |
...
clsAmortBase | CLS_AMORT_BASE | NUMBER(38,12) |
...
clsOidLocal | CLS_OID_L | NUMBER(38,12) |
...
clsOidBase | CLS_OID_B | NUMBER(38,12) |
...
clsIlbIncLtdLocal | CLS_ILB_INC_LTD_LOCAL | NUMBER(38,12) |
...
clsIlbIncLtdBase | CLS_ILB_INC_LTD_BASE | NUMBER(38,12) |
...
gainCurrency988 | GAIN_CRRCY_988 | NUMBER(38,12) |
...
lossCurrency988 | LOSS_CRRCY_988 | NUMBER(38,12) |
...
gainLossCurrency988 | GAIN_LOSS_CRRCY_988 | NUMBER(38,12) |
...
washsaleDisallowedReversalLocal | WS_DISALLOW_REV_L | NUMBER(38,12) |
...
washsaleDisallowedReversalBase | WS_DISALLOW_REV_B | NUMBER(38,12) |
...
impairmentLtdLocalExt | IMPAIRMENT_LTD_LOCAL | NUMBER(38,12) |
...
impairmentLtdBaseExt | IMPAIRMENT_LTD_BASE | NUMBER(38,12) |
...
impairmentFxLtd | IMPAIRMENT_FX_LTD | NUMBER(38,12) |
...
Files changed:
extract_warehouse_trade.inc
...
The entitlement date element was added as entitlementTradeDate to the outbound rules for the Warehouse Cash Activity Extract.
...
ESP-1716: Regression: Warehouse Roll Up in Single Mode
The Warehouse Position and OpenLot loads were failing on several regions. The issue was caused by an inbound JMS streams that was changing the value in the msgcenter_dbo.msg_detail database table.
...
ESP-1757: Incorrect resolution of the primary key 'GLDP_ID' for joining of to an extension table
Currently the GLDP_ID resolution logic is based on an incorrect field. The assetcurrency field is actually being used in place of the localcurrency field. Additionally the extensions logic is unable to override the ‘LongShortIndicator’ and ‘ShareClass’ fields. In the core stored procedure these over rides are present which allows records to load successfully.
The logic from this stored procedure was replicated in the custom_field.inc to allow loading with extensions. The GLDP_ID resolution was also changed to use the localcurrency field instead of the assetcurrency.
To include the extension fields in the extract the EscapeExtensionExtract parameter has to have a value of “N”.
<taskParameter>
<name>EscapeExtensionExtract</name>
<dataType>S</dataType>
<value>N</value>
</taskParameter>
Files changes:
estar\tpe\dynamic\msgcenter\eagle_default\in\xml\xml-wh_gl_detail_posting.xml
estar\tpe\dynamic\msgcenter\eagle_default\in\xml\include\custom_fields.inc
...