EagleML Release Notes - July 2018

ESP-358: STAR v2017 EagleML - Inbound Cash Tender, Cash Dividend and PIK Bond Corporate Action Announcement failing with required To Security Alias Tag302

When loading Accounting Corporate Actions on version 2017 there were two 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’ 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

Rule changed:
• eagle_default/in/xml/xml-ref_ca.xml

ESP-359: EagleML: Re-design xml-ref_smf_factor to not to use the Corporate Actions load and regenerate binds based on environments core stored procedure for Factor

There was a request to change the inbound logic for the factor object.

A new rule was created that loads factors using the stored procedure directly instead of using panels. The default behavior for the load is still to use the old rule and call the panels. To use the new functionality the accountingValidationFlag has to be set as “N” in the incoming file.

List of files changed:

eagle/estar/tpe/dynamic/metadata/bind/eagleml_reference.xml
eagle/estar/tpe/dynamic/msgcenter/eagle_default/in/xml/xml-ref_objects.rsf
eagle/estar/tpe/dynamic/msgcenter/eagle_default/in/xml/xml-ref_smf_factor.xml
eagle/estar/tpe/dynamic/msgcenter/eagle_default/in/xml/xml-ref_smf_factor_generic.xml

ESP-361: EagleML - eagle_wrf_generic_extract_accounting_objects.wrf 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 corrected to apply the date filters are passed with correct date formats.

Example of run task request:
<EagleML xmlns="http://www.eagleinvsys.com/2011/EagleML-2-0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RunTaskRequest" eaglemlVersion="2-0" xsi:schemaLocation="http://www.eagleinvsys.com/2011/EagleML-2-0 eagleml-main-2-0.xsd" eaglemlType="RunTaskRequest">
<header>
<messageId>SMFEXTRACT</messageId>
<sentBy>http://www.eagleinvsys.com/</sentBy>
<sendTo>http://www.eagleinvsys.com/eagle_ml-2-0_default_cm_control_message</sendTo>
<creationTimestamp>2016-02-03T14:01:58-05:00</creationTimestamp>
</header>
<taskIdentifier>
<correlationId correlationIdScheme="correlationIdScheme">asUNAPPRVVARMARGIN_180618_SP1</correlationId>
<businessTaskId correlationIdScheme="businessTaskIdScheme">UNAPPROVEDVARMARGINEXTRACT</businessTaskId>
</taskIdentifier>
<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>UNAPPROVEDVARMARGINEXTRACT</value>
</taskParameter>
<taskParameter>
<name>accountingvalidationflag</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
<taskParameter>
<name>entityselectiontype</name>
<dataType>S</dataType>
<value>EntityID</value>
</taskParameter>
<taskParameter>
<name>entityselectionvalue</name>
<dataType>S</dataType>
<value>36136101</value>
</taskParameter>
<taskParameter>
<name>todate</name>
<dataType>S</dataType>
<value>2018-06-30</value>
</taskParameter>
</taskParameters>
</EagleML>

Rules changed:
• eagle_ml-2-0_cm/out/tagvalue/extract_acct_unapproved_var_margin.inc

ESP-366: EagleML - GENCORPACTIONSEXTRACT – support additional filters

The Generic Corporate Action extract required support for some new filters.

The parameters for transType, cpStatus, clientSpecificCAId, cpReason, sweepdate, fromsweepdate, and tosweepdate were all added to the core rules to enable the filters to be used in the extract.

Example of RTR

<sendTo>http://www.eagleinvsys.com/eagle_ml-2-0_default_cm_control_message</sendTo>

<replyTo>http://www.client.com/Eagle Control Messages</replyTo>

</header>

<correlationId>ESP-366_GENCORPACTIONSEXTRACT_XML</correlationId>

<businessTaskId>Generic_Corporate_Action_Extract</businessTaskId>

</taskIdentifier>

<name>ActionType</name>

<value>EXTRACT</value>

</taskParameter>

<name>StreamName</name>

<value>eagle_ml-2-0_default_out_q</value>

</taskParameter>

<name>FeedType</name>

<value>GENCORPACTIONSEXTRACT</value>

</taskParameter>

<name>fromsweepdate</name>

</taskParameter>

<name>tosweepdate</name>

</taskParameter>

<name>transType</name>

<value>CASHDIV</value>

</taskParameter>

<name>cpReason</name>

<value>CASHDIV</value>

</taskParameter>

<name>cpStatus</name>

</taskParameter>

<name>clientSpecificCAId</name>

<value> CASHDIV_1001</value>

</taskParameter>

<name>maxrows</name>

</taskParameter>

</taskParameters>

</EagleML>

Files changed:
- extract_generic_corporate_actions.xml
- extract_generic_corporate_actions.inc
- extract_filter_date.inc

ESP-372: EagleML: Add the link to the autoinclude before ref_issuerating_all after the xsl rating_data_model

The eagle_default/in/xml/xml-ref_issuerating_all.xml rules was updated with new logic to include the rating_data_model XS.

The reference to an autoinclude was added in the \xml-ref_issuerating_all.xml file.

File changed: \msgcenter\eagle_default\in\xml\xml-ref_issuerating_all.xml

ESP 373: EagleMl: Issuer Role - Skip returned record from the issuer_query_result if the returned issuer_alias does not match the incoming issuer alias

Logic for the W_ISSUEROLE_UPDATE_CLOSED_ALLOWED variable was updated to allow skipping certain files returned by the issuer_query_result.

The logic now uses the security alias, issuer alias, begin date and role type to retrieve the closed records. If the values for any of these do not match the values in the incoming file the record will not be updated.

Files changed:
xml-ref_issuerrole_common.inc

ESP-375: EJM: Enhance EAS Alert functionality to support time zones, business calendars and date offsets

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.

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.

The parameter names are:

- W_EAS_TIME_ZONE_NAME
- W_BUSINESS_CALENDAR_NAME

read_task_info.inc - changed
check_business_date.inc - added

ESP-379: EagleMl: Add new security columns.

New security columns were added to several model groups from the SECMASTER_DETAIL_EXT, SECTOR, DERIVATIVES and EQUITY_DETAIL tables.

The Derivatives.model, SMExtension.model, UDF.model, and Equity.model were all changed in the XSD to reflect these additions.

Files changed:

eagleml-sec-shared-2-0.xsd

t_smf.xml

t_smf_streaming_eml.inc

ESP-380: EagleML - Enhancement Request to extract data from the securitydbo.NON_UNIQUE_XREFID table

A new ‘instance’ field was added to the SMFIdentifierConfig interfaces.

An extract for this interface was 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.

Filters:
date filters (fromdate, todate, updatedate)
common filters (maxrow)
deltabased - Y
own filters (xrefqualifier, xrefidtype, investmenttype)

Support for the inbound and outbound interfaces for this object type were added to the generic extract and generic load solutions.

Generic Load - eagle_generic_load_system_objects - load_smfidentifier_config
File Mask - _SMFIDENTIFIERCONFIG_

Generic Extract - eagle_generic_extract_ref_independent_objects - eagle_generic_extract_system_objects - extract_smf_identifier_config

Example of RTR:

<EagleML xmlns="http://www.eagleinvsys.com/2011/EagleML-2-0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RunTaskRequest" eaglemlVersion="2-0" xsi:schemaLocation="http://www.eagleinvsys.com/2011/EagleML-2-0 eagleml-main-2-0.xsd" eaglemlType="RunTaskRequest">
<header>
<messageId>WRHSNAVEXTRACT001</messageId>
<sentBy>http://www.client.com</sentBy>
<sendTo>http://www.eagleinvsys.com/eagle_ml-2-0_default_cm_control_message</sendTo>
<replyTo>http://www.client.com/Eagle Control Messages</replyTo>
<creationTimestamp>2012-08-02T22:13:32.272-05:00</creationTimestamp>
</header>
<taskIdentifier>
<correlationId correlationIdScheme="correlationIdScheme">XREFCONFIGEXTRACT_tst_11</correlationId>
<businessTaskId correlationIdScheme="businessTaskIdScheme">XREFCONFIGEXTRACT</businessTaskId>
</taskIdentifier>
<taskTypeEnum>NEW</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>XREFCONFIGEXTRACT</value>
</taskParameter>
<taskParameter>
<name>fromdate</name>
<dataType>S</dataType>
<value>2000-06-29 00-00-00</value>
</taskParameter>
<taskParameter>
<name>maxrows</name>
<dataType>S</dataType>
<value>100</value>
</taskParameter>
<taskParameter>
<name>investmenttype</name>
<dataType>S</dataType>
<value>DERV</value>
</taskParameter>
<taskParameter>
<name>xrefqualifier</name>
<dataType>S</dataType>
<value>DUPLICATE</value>
</taskParameter>
<taskParameter>
<name>xrefidtype</name>
<dataType>S</dataType>
<value>INTERNAL</value>
</taskParameter>
<taskParameter>
<name>XSDValidation</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
</taskParameters>
</EagleML>

Rules affected:

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: Make issuer resolution consistent in all issuer-related rules

The issuer resolution logic was changed for the elementOverride, entityIssuerRelationship, issuerRegulatory, and issuerRelationship objects.

The issuer resolution logic for deleting issuerOrganization, issuerRelationship, and RoleModel (also for MI interface) was changed as well.

All issuer related objects are now using the issuer_xref_resolution.inc file.

Following files are changed:

issuer_xref_resolution.inc
t_eagle_ml-2-0_default_in_xml_delete_issuer.xml
t_eagle_ml-2-0_default_in_xml_delete_issuer_mi_issuer_id.xml
t_eagle_ml-2-0_default_in_xml_delete_issuer_mi_organization.xml
t_eagle_ml-2-0_default_in_xml_delete_issuer_mi_relationship.xml
t_eagle_ml-2-0_default_in_xml_delete_issuer_organization.xml
t_eagle_ml-2-0_default_in_xml_delete_issuer_relationship.xml
t_eagle_ml-2-0_default_in_xml_delete_issuer_role_model.xml
xml-ref_elementoverride.xml
xml-ref_issuerentityrelationship.xml
xml-ref_issuerregulatory.xml
xml-ref_issuerrelationship.xml

ESP-458: RDC Pricing - Eagle ML Upload Stream

There was a request to add the ability of loading price records 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.

Files changed:
xml-ref_issuepcprice.xml

ESP-805: Enable support of Chain Extracts to call multiple feed types within a single request

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>

Changed files:
eagle_wrf_generic_extract workflow(*.wrf, *.sln, *.ejmwf)
compress_extract.inc
extract_eofw.inc
extract2t.inc
get_w_custom_params.inc

Added new extract file:
extract_chain.xml

ESP 865: EagleMl: Adjust xml-smf_underling_common to have DELETE functionality by switch

There was a request to add functionality that would allow re-inserting underlying records.

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 were moved from the <acctBasis> node to the <genericEntity> root node.

Changed files:

csv-entity_columns.inc

entity_acct.inc

entity_aliases.inc

entity_panels.inc

eagleml_entity.xml

xml-ent_entitygeneric.xml

extract_entity.inc

t_entity_streaming.inc

t_entity_streaming_eml.inc

ESP 995: Security Fields to be included in Warehouse Extracts in Extended Model

The countryOfRisk and pricingCusip fields were added to the extended model which is present in the warehouse extracts.

Files changed:

extract_asset_properties_fields.inc

extract_std_table_names.inc

t_asset_properties_fields.inc

t_asset_properties_fields_streaming.inc

ESP-1040: EagleML ENTITYEXTRACT fails when using an Element Reduction profile that does not include any element referencing the Entity Star Processing table

The ENTITYEXTRACT was failing when applying an Element Reduction profile as a join to a required table was not present. The Element Reduction profile required the inclusion of an element from the RULESDBO.ENTITY_STAR_PROCESSING table.

Without a reference to the required table, the request failed when using an Element Reduction profile. The reason was that request contained tables in the SELECT section, but did not have the fields in the FROM section, as these tables were not described in the custom profile.

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.

A profile check was added for the IncludeExtendedDetails filter.

Files changed:
extract_entity.inc
extract_entity_fields.inc

ESP-1069: Duplicate messages from the generic extractor and compression

There was an issue with the compression and distribution logic which was producing duplicated archived files when using the JMS stream to run the extracts.

Compression and distribution logic was 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.

The default values for these parameters are: Compress = N, ExtractDeliveryMethod = FILE.

A new option for specifying the compression type for a single extract file was introduced into Generic Extract Workflow. Each extract file 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>

If Compress type is not specified the default value is ZIP. Any available compression can be used.
Please note, that if you want files to be sent to JMS or MQ, the compression type should include B64.
For example: GZIP+B64

Changed files:
eagle_wrf_generic_extract workflow(*.wrf, *.sln, *.ejmwf)

ESP-1187: Additional Characters in the extracted file

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.

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.

This option is controlled by the W_COMPRESS_WITH_CORR_ID global variable in the w_config.inc file.

Changed files:
compress_extract.inc

ESP-1228: Create Extract for Exceptions

The Accounting Rejection Extract received new database binds for some of its filter parameters.

Binds were added for:
eventType
updateSource
eventId.

Examples of bind parameters in the extract SQL:
WHERE (1=1)
AND ESTAR_EVENT_REJECTION.MESSAGE_SUBTYPE IN (:1) -- eventType
AND ESTAR_EVENT_REJECTION.REJECTION_SOURCE IN (:2) -- updateSource
AND ESTAR_EVENT_REJECTION.EVT_ID IN (:3) -- eventId

Files changed:
- extract_acct_rejection.inc

ESP-1420: EagleML - GLACTIVITYEXTRACT - @RecordCount on the GLACTIVITYEXTRACT is not matching the DB count

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 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 o perform batch roll up and this key 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 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 allows executing the roll up with the key file.

Files changes:

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. 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.

Logic for loading an underlying SMF record based on the main SMF data was added.

The W_UNDERLYINGSMF_AUTO_ENRICH_DATA_SWITCH global variable and underlyingAutoEnrichDataSwitch 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, 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

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 more efficiently than the Local Currency and Long Short Indicator fields.

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 lot with extension stored procedure is created incorrectly

To support loading records with extensions there is functionality that allows stored procedures to be generated dynamically based on the core stored procedure as well as additional pieces corresponding to the extension fields.

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.

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 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: from the TRADESDBO.TRADE_EXT table:

ElementName	DBField	Field Type / Length

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

t_warehouse_trade_streaming_eml.inc

t_warehouse_trade.xml

ESP-1673: Entitlement Trade Date missing from Cash Activity EML Extract

There was a request to include the entitlement date in the cash activity extract.

The entitlement date element was added as entitlementTradeDate to the outbound rules for the Warehouse Cash Activity Extract.

Changed files:
• t_warehouse_cash_activity_streaming_eml
• t_warehouse_cash_activity_streaming.inc

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.

The table value checks from the single load mode warehouse roll up query were removed for the msgcenter_dbo.msg_detail table.

Files changed:

xml-dbdirect_invar_eofp.xml

ESP-1757: Incorrect resolution of the primary key 'GLDP_ID' for joining to an extension table

Currently the GLDP_ID resolution logic is based on an incorrect field. The assetcurrency field is 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