Security Resolution for Reference and Accounting Objects

Overview

The following include files contain important elements around the code to implement the security resolution logic:

/eagle_default/in/xml/include/security_resolution_generic.inc
/eagle_default/in/xml/include/match_all_sec_resolution.inc
/eagle_default/in/xml/include/match_all_new_record_check.inc

The resolution of security is controlled by two parameters: Security Resolution Options and Batting Order

There are two modes of Security Resolution Logic : Find 1st Match and Match All

Security Resolution Options

Security Resolution Options are specified settings which are used for security resolution logic.

Valid values:

MATCH_ALL – security resolution mode, when security alias is found with using all xreference identifiers. If this value is not specified “Find 1st Match” mode is used
XREF_EXCHANGE – additional setting which defines will the Xref Exchange value be used for resolving security or not
MATURITY_DATE – additional setting which defines will the Maturity Date value be used for resolving security or not
ASSET_CURRENCY – additional setting which defines will the Asset Currency value be used for resolving security or not
FAIL_ON_UNMATCHED – additional option which defines will the new Security load or not if the Security has not been resolved based on incoming identifiers.
SKIP_PRIMARY_ID – additional option that allows to skip primary identifiers in security resolution logic. This option works in Find 1st Match mode only.
TICKER,CUSIP,SEDOL,ISIN,SWAPID,BBID,INTERNALID – list of xreference identifiers used for security resolution logic Note: These have no role in the MATCH_ALL mode. In MATCH_ALL mode, all identifiers are always involved.

The default preset xref identifiers used used for security resolution logic are:

‘XREF_EXCHANGE, MATURITY_DATE, ASSET_CURRENCY, TICKER, CUSIP, SEDOL, ISIN, SWAPID, BBID, INTERNALID,’.

Note

If you want a different set of xref identifiers to be used for security resolution, you would change this for all workflows by using the Config Security Resolution Option for all workflows or for one individual workflow by using the Specific Security Resolution Option or to a single record by customizing the assetResolutionOption in the incoming message.

Note

The order in which you list the identifiers in the security resolution option does not matter. Instead, the order for resolution is pulled from the Batting Order

The default security resolution logic uses the “Find 1st Match” mode of security resolution and contain all additional settings and xreference identifiers you have specified.

Security Resolution Logic

Modes of Data Loading

The security resolution can be performed in two modes: Find 1st Match and Match All.

This is configured by the Security Resolution Options parameter. By default, Find 1st Match mode is performed.

If MATCH_ALL option defined in Security Resolution Option, the Match All mode is performed.

Find 1st Match” mode implements logic when the security is resolved by the 1^st matching of xref identifiers.
Match All” mode implements logic when the security resolved by the matching of all identifiers.

Excluding Duplicates

Before executing of one of the security resolution modes, the duplicate types of the xref identifiers are found and excluded from the match.

The information about the types which can be used for multiple securities with the same xref ID is stored in the database in the SECURITYDBO.NON_UNIQUE_XREFID table.

For determining the list of the duplicate xref types the following SQL request is used and the duplicates are excluded from the Batting Order and thereby from the security resolution.

OCI: select distinct XREF_TYPE from SECURITYDBO.NON_UNIQUE_XREFID where XREF_QUALIFIER LIKE 'DUPLICATE%' and (INVESTMENT_TYPE is null OR INVESTMENT_TYPE IN (' + :tag11: +'))

OLEDB: select distinct XREF_TYPE from SECURITY.DBO.NON_UNIQUE_XREFID where XREF_QUALIFIER LIKE 'DUPLICATE%' and (INVESTMENT_TYPE is null OR INVESTMENT_TYPE IN (' + :tag11: +'))

Find 1st Match Mode Logic

In this mode the security is resolved using the eagle/star/pan-qxref-s-msgcntr.htm lookup based on incoming identifiers that are declared in Batting Order and setting that are specified in Security Resolution Options.

The lookup has the following in and out parameters:

In Parameters		Out Parameters
Name	Tag	Name	Tag
X Id 1	1952	Asset	10
X Id Type 1	1953	X Alias 1	1951
X Id 2	1955	X Alias 2	1954
X Id Type 2	1956	X Alias 3	1957
X Id 3	1958	X Alias 4	1960
X Id Type 3	1959	X Alias 5	1963
X Id 4	1961	X Alias 6	1966
X Id Type 4	1962	X Alias 7	1969
X Id 5	1964	X Alias 8	1972
X Id Type 5	1965	X Alias 9	1975
X Id 6	1967	X Alias 10	1978
X Id Type 6	1968
X Id 7	1970
X Id Type 7	1971
X Id 8	1973
X Id Type 8	1974
X Id 9	1976
X Id Type 9	1977
X Id 10	1979
X Id Type 10	1980
X Exchange	1981
Asset Currency	85
Maturity Date	38
Security Query Flag	1256

The lookup finds the Security by the 1^st non-empty pair XId and XIdType and the additional filters such as Asset Currency, Maturity Date, Exchange. Then the lookup based on found Security returns the Asset (Security Alias) and the XAlias’s for the remaining identifiers. If any of the remaining identifiers does not match the found Security or refers to the other Security, the lookup returns XAlias as 1.

If none of the identifiers does not refers to any of existing Securities, the lookup returns all the parameters as 0.
If any of XAlias’s has the value 1 and pair xId and xIdType does not match the found Security, the unmatched identifier will be added/updated for current Security.
If any of XAlias’s has the value 1 and pair xId and xIdType refers to the other Security, An Error will be displayed.

Match All Mode Logic

In this mode the security resolution process consists of 2 stages:

Check for ‘Full Unmatch’

At the step is the SQL request that covers all identifiers that from Batting Order and Security Resolution Options, such as Asset Currency, Maturity Date, Exchange, which returns the Security Alias and the count of matched identifiers. If none of identifiers is mapped and only primaryAssetId and primaryAssetIdType are mapped then this pair is used for SQL request.

select xref.security_alias,count(1) as cnt FROM (SELECT xr.security_alias, xr.xref_security_id, xr.xref_type, xr.xref_security_alias FROM securitydbo.xreference xr ,securitydbo.security_master sm WHERE xr.security_alias = sm.security_alias AND ((xr.xref_security_id = 'BB_DKTEST2MA' AND xr.xref_type = 'BBID') OR (xr.xref_security_id = 'SEDOL_DKTEST2MA' AND xr.xref_type = 'SEDOL') OR (xr.xref_security_id = 'TSTID2_DKTEST2MA' AND xr.xref_type = 'TESTID2')) AND rtrim(xr.exchange) = rtrim('TEST1234') AND sm.currency_code = 'USD' AND sm.mat_date='2014-10-20') xref group by xref.security_alias

If the count = 0 then detected ‘Full Unmatch’, none of the identifiers does not refers to any of existing Securities, the Security Alias = 0, the second stage is not performed. If none of identifiers is mapped and only primaryAssetId and primaryAssetIdType are mapped then this pair is used for SQL request.

Check for unmatched identifiers

At the step is the SQL request that covers all identifiers that from Batting Order and Security Resolution Options, such as Asset Currency, Maturity Date, Exchange, which returns Security Alias and the Xref Security Aliases.

select NVL(xBBID.security_alias, 0) SecurityAlias, xBBID.xref_security_alias BBID_security_alias, NVL(xSEDOL.xref_security_alias, 0) SEDOL_security_alias, NVL(xTESTID2.xref_security_alias, 0) TESTID2_security_alias FROM (SELECT xrBBID.security_alias, xrBBID.xref_security_id, xrBBID.xref_type, xrBBID.xref_security_alias, xrBBID.exchange FROM securitydbo.xreference xrBBID WHERE xrBBID.xref_security_id = 'BB_DKTEST2MA' AND xrBBID.xref_type = 'BBID') xBBID,(SELECT xrSEDOL.security_alias, xrSEDOL.xref_security_id, xrSEDOL.xref_type, xrSEDOL.xref_security_alias FROM securitydbo.xreference xrSEDOL WHERE xrSEDOL.xref_security_id = 'SEDOL_DKTEST2MA' AND xrSEDOL.xref_type = 'SEDOL') xSEDOL,(SELECT xrTESTID2.security_alias, xrTESTID2.xref_security_id, xrTESTID2.xref_type, xrTESTID2.xref_security_alias FROM securitydbo.xreference xrTESTID2 WHERE xrTESTID2.xref_security_id = 'TSTID2_DKTEST2MA' AND xrTESTID2.xref_type = 'TESTID2') xTESTID2,securitydbo.security_master sm WHERE xBBID.security_alias = sm.security_alias AND sm.currency_code = 'USD' AND sm.mat_date='2014-10-20'

If returned more than one row, more than Security Aliases is found, the error ‘Identifier with xref type [1^st identifier type] and xref id [1^st Identifier Id] is duplicate’ Is displayed.
If Security Aliases is 0, the error ‘Identifier with xref type [1^st identifier type] and xref id [1^st Identifier Id] is unmatched'
If Security Aliases is not 0, but one of Xref Security Aliases is 0, the error ‘Identifier with xref type [identifier type] and xref id [Identifier Id] is unmatched for SECURITY_ALIAS [Security Alias]'
FAIL_ON_UNMATCHED Option

If as a result of any security resolution mode is the Security has not been found by the incoming identifiers (Security Alias = 0) and the FAIL_ON_UNMATCHED is set, an error 'Security Alias not found. FAIL_ON_UNMATCHED is set' will be returned. Otherwise, if the Security has not been found and the FAIL_ON_UNMATCHED is not set, the incoming data will be loaded as new security.