Lusid.Portfolio.ResolveInstrument

Type	Read/write	Author	Availability
Data provider	Write	Finbourne	Provided with LUSID

Providing you have sufficient access control permissions, the Lusid.Portfolio.ResolveInstrument provider enables you to write a Luminesce SQL query that resolves transactions in a particular portfolio to instruments mastered in LUSID. Why is this important?

You must construct a valid table of data to write, one original instrument identifier per record. Lusid.Portfolio.ResolveInstrument lists the fields (columns) available to populate with values for each record, and has a set of parameters to help you construct a valid table.

Note the following:

An original instrument identifier is one applied to transaction(s) at the time they were upserted to a portfolio (or holdings were adjusted or set). An instrument identifier identifies a single instrument if the type is unique (for example FIGI), but can identify more than one instrument if the type is non-unique (for example ISIN). More information.
Do not write to the ResolvedLuid field. This field returns the LUID of the instrument to which transaction(s) resolve if the operation is successful.

Basic usage

@table_of_data = <select-statement>;
select * from Lusid.Portfolio.ResolveInstrument where toWrite = @table_of_data;

Query parameters

Lusid.Portfolio.ResolveInstrument has parameters that help you construct a valid table of data to write.

Note: The ToWrite parameter is mandatory and used to actually write the table of data into LUSID.

To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Lusid.Portfolio.ResolveInstrument' and FieldType = 'Parameter';

Data fields

Lusid.Portfolio.ResolveInstrument lists the fields you can populate in your table of data to write, some of which are mandatory:

Mandatory fields in table of data to write

Notes

PortfolioScope

PortfolioCode

InstrumentIdentifierType

InstrumentIdentifierValue

The InstrumentIdentifierType and InstrumentIdentifierValue fields must reference the original instrument identifier that transaction(s) were upserted with.

The InstrumentIdentifierCollection field is optional but likely to be useful in cases where you wish to add more instrument identifiers to transaction(s) to increase the chances of successful resolution. Each must be an identifier type and value separated by an equals sign, with no spaces between multiple values. For example:
'Figi=BBG000NSXQ99,Isin=GB0031743007' as InstrumentIdentifierCollection.

Do not write to the ResolvedLuid field.

To list all available fields, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Lusid.Portfolio.ResolveInstrument' and FieldType = 'Column';

Write errors

We recommend examining the results of every write query using one or more of the WriteError, WriteErrorCode and WriteErrorDetail fields.

For each record in the table of data to write, Lusid.Portfolio.ResolveInstrument returns an error code. If the operation is successful, the error code is 0. If unsuccessful, a positive error code and explanation help you discover why LUSID considers the operation invalid.

Examples

Note: For more example Luminesce SQL queries, visit our Github repo.

Example 1: Resolve transactions to a new instrument

You can omit the InstrumentIdentifierCollection field if you have added an instrument with the original instrument identifier to the LUSID Security Master since you upserted transaction(s), in this case a Figi with a value of BBG000NSXQ99. If the operation is successful, the ResolvedLuid field returns the LUID of the instrument to which transaction(s) now resolve.

@table_of_data = select 'Finbourne-Examples' as PortfolioScope, 'UK-Equities' as PortfolioCode, 
'Figi' as InstrumentIdentifierType, 'BBG000NSXQ99' as InstrumentIdentifierValue;

select * from Lusid.Portfolio.ResolveInstrument where ToWrite = @table_of_data;

Example 2: Resolve transactions to an existing instrument by adding new identifiers to the transactions

In this example, a ClientInternal identifier with a value of id-12345 is added to transaction(s) in order to resolve to an instrument with that identifier already mastered in LUSID.

@table_of_data = select 'Finbourne-Examples' as PortfolioScope, 'UK-Equities' as PortfolioCode,
'Figi' as InstrumentIdentifierType, 'BBG000NSXQ99' as InstrumentIdentifierValue, 
'ClientInternal=id12345' as InstrumentIdentifierCollection;

select * from Lusid.Portfolio.ResolveInstrument where ToWrite = @table_of_data;