Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

The PACE Lot Upload stream loads records to the POSITION (if a record does not exist for the Source, Entity, and Effective Date), LOT_LEVEL_POSITION, and POSITION_COST_LOT tables.

Note

This stream supports all fields defined on the pan-lot_egl_gen_interface.htm panel, whether listed below or not.

The following table describes several required and optional data fields for PACE Lot Upload message stream.

Tag Number/Name

Required/Optional

Data Type

Notes

PACE Table

PACE Field

55 – Event Type

Required

CHAR

Required – defaults on panel. Used by message stream for identification of the event. Must be UPLOAD_LOT.

n/a

n/a

1102 – Source Name

Conditional Required – if tag 1168 is null.

CHAR (15)

Used to resolve src_intfc_inst if tag 1168 is null

n/a

n/a

1168 – Src_Intfc_Inst

Conditionally Required – tag 1102 is null

INTEGER

 

POSITION

src_intfc_inst

1076 – Rollup Mode Flag

Optional

CHAR

Used by the procedure to determine if it should rollup lot level data to the position_detail level. Valid Values are Null – No, PD – Yes

n/a

n/a

7001 – Resolve in Panel Flag

Optional

CHAR

Used by message stream to determine if entity ID and security alias should be resolved in the panel or the procedure. Valid Values: 1 – Resolve All, 2 – Resolve Entity Only, 3 – Resolve None (resolve using the procedure).
Default is 1.

n/a

n/a

1233/14 – Security ID

Conditionally Required – tag 7001 = 1 or 2

CHAR (100)

Xreference lookup to resolve security alias.

n/a

n/a

1234 – Security ID Type

Conditionally Required – tag 7001 = 1 or 2

CHAR (30)

Xreference lookup to resolve security alias.

n/a

n/a

1952 – XID 1

Conditionally Required – tag 7001 = 3

CHAR (100)

Used to resolve security_alias.

n/a

n/a

1953 – XID Type 1

Optional

CHAR (30)

Used to resolve security_alias.

n/a

n/a

1955 – XID 2

Conditionally Required – tag 7001 = 3

CHAR (100)

Used to resolve security_alias.

n/a

n/a

1956 – XID Type 2

Optional

CHAR (30)

Used to resolve security_alias.

n/a

n/a

1958 – XID 3

Conditionally Required – tag 7001 = 3

CHAR (100)

Used to resolve security_alias.

n/a

n/a

1959 – XID Type 3

Optional

CHAR (30)

Used to resolve security_alias.

n/a

n/a

1961 – XID 4

Conditionally Required – tag 7001 = 3

CHAR (100)

Used to resolve security_alias.

n/a

n/a

1962 – XID Type 4

Optional

CHAR (30)

Used to resolve security_alias.

n/a

n/a

1964 – XID 5

Conditionally Required – tag 7001 = 3

CHAR

Used to resolve security_alias.

n/a

n/a

1965 – XID Type 5

Optional

CHAR

Used to resolve security_alias.

n/a

n/a

1967 – XID 6

Conditionally Required – tag 7001 = 3

CHAR

Used to resolve security_alias.

n/a

n/a

1968 – XID Type 6

Optional

CHAR

Used to resolve security_alias.

n/a

n/a

1970 – XID 7

Conditionally Required – tag 7001 = 3

CHAR

Used to resolve security_alias.

n/a

n/a

1971 – XID Type 7

Optional

CHAR

Used to resolve security_alias.

n/a

n/a

7052 – CUSIP Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7053 – Xref – ID Type for 7052

Optional

CHAR

Xref ID type associated with tag 7052; defaults to CUSIP.

n/a

n/a

7055 – ISIN Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7056 – Xref – ID Type for 7055

Optional

CHAR

Xref ID type associated with tag 7055; defaults to ISIN.

n/a

n/a

7058 – SEDOL Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7059 – Xref – ID Type for 7058

Optional

CHAR

Xref ID type associated with tag 7058; defaults to SEDOL.

n/a

n/a

7061 – REUTERS Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7062 – Xref – ID Type for 7061

Optional

CHAR

Xref ID type associated with tag 7061; defaults to RIC.

n/a

n/a

7064 – BLOOMBERG Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7065 – Xref – ID Type for 7064

Optional

CHAR

Xref ID type associated with tag 7064; defaults to BBID.

n/a

n/a

7067 – SICOVM Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7068 – Xref – ID Type for 7067

Optional

CHAR

Xref ID type associated with tag 7067; defaults to SICOVM.

n/a

n/a

7070 – VALOREN Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7071 – Xref – ID Type for 7070

Optional

CHAR

Xref ID type associated with tag 7070; defaults to VALOREN.

n/a

n/a

7073 – CEDEL Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7074 – Xref – ID Type for 7073

Optional

CHAR

Xref ID type associated with tag 7073; defaults to CEDEL.

n/a

n/a

7076 – INTERNAL Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7077 – Xref – ID Type for 7076

Optional

CHAR

Xref ID type associated with tag 7076; defaults to INTERNAL.

n/a

n/a

7079 – CINS Xref – ID

Not used if tag 7001 = 3

CHAR

Additional security identifier used to resolve security alias if tag 10 is null. Uses next tag to control xref ID type. Overrides the use of tags 1952 – 1980 for security resolution.

n/a

n/a

7080 – Xref – ID Type for 7079

Optional

CHAR

Xref ID type associated with tag 7079; defaults to CINS.

n/a

n/a

38 – Maturity Date

Optional

DATE

Date on which the security matures. Used by the Xref ID lookup to qualify security resolution.

 

 

11043 – Inactive flag

Optional

VARCHAR2 (255)

Valid values are Y = Yes or N = No. Used by the Xref ID lookup to qualify security resolution.

 

 

85 – Asset Currency

Optional

CHAR (30)

3-character currency code – Code Value. Used by the Xref ID lookup to qualify security resolution.

LOT_LEVEL_POSITION

currency

1981 –Xref Exchange

Optional

CHAR (8)

Used by the Xref ID lookup to further qualify the security resolution.

n/a

n/a

17 – Primary Exchange Code

Optional

CHAR (8)

Exchange on which the security is listed – Code Value.

n/a

n/a

21– Acct Basis

 

VARCHAR2(5)

 

POSITION

acct_basis

1105 – Entity_Type

Optional

CHAR (4)

 

POSITION

entity_flag_type

86 – Base_Currency

Optional

CHAR (3)

 

POSITION

base_currency

10 – Security_Alias

Optional

NUMBER

 

LOT_LEVEL_POSITION

security_alias

1163 – Entity Id

Required

CHAR (8)

 

HOLDING.POSITION

entity_id

1164 – Entity Name

Conditionally Required – if tag 1163 is null

CHAR (30)

 

n/a

n/a

1790 – Lot_Number

Required

VARCHAR2 (30)

Defaulted to 1 in panel

LOT_LEVEL_POSITION

lot_number

221 – Effective Date

Required

DATE

 

HOLDING.POSITION/LOT_LEVEL_POSITION/POSITION_COST_LOT

effective_date

15 – Long/Short Indicator

Required

CHAR

 

HOLDING.LOT_LEVEL_POSITION

long_short_ind

16 – Bank Alias

Optional

VARCHAR2 (25)

Instance assigned to a custodian bank relationship.

HOLDING.POSITION_COST_LOT

bank_alias

17 – Primary Exchange

Optional

CHAR (8)

Code assigned to the exchange on which the transaction was processed. Returned from lookup on Tag 14. Can be overridden.

HOLDING.LOT_LEVEL_POSITION

user_group2

2283 – Event Type

Optional

CHAR

 

HOLDING.LOT_LEVEL_POSITION

trans_type

24 – Cost_Basis_Type

Optional

CHAR

Contains a value defining the cost basis type provided on the transaction.

HOLDING.POSITION_COST_LOT

cost_basis_type

28 – Link_Event_Id

Optional

CHAR

Links multiple open and close lots together.

HOLDING.LOT_LEVEL_POSITION

link_event_id

30 – Post_Dt

Optional

DATE

Post date of the transaction.

HOLDING.POSITION_COST_LOT

pcl_user_date2

35 – Trade_Dt

Optional

DATE

Trade date of the transaction.

HOLDING.LOT_LEVEL_POSITION

trade_date

36 – Acctg_Dt

Optional

DATE

Accounting date of the transaction.

HOLDING.LOT_LEVEL_POSITION

accounting_date

37 – Settlement_Dt

Optional

DATE

Settlement date of the transaction.

HOLDING.LOT_LEVEL_POSITION

settlement_date

41 – Original_Face

Optional

NUMBER(38,12)

Original face value of the MBS transaction.

HOLDING.LOT_LEVEL_POSITION

orig_face

46 – Tax_Amt_L

Optional

MONEYL

Tax amount on the original transaction.

HOLDING.POSITION_COST_LOT

local_tax_unit_book_value

47 – Comm_Amt_L

Optional

MONEYL

Commission amount on the original transaction.

HOLDING.LOT_LEVEL_POSITION

commission

48 – Sec_Fee

Optional

MONEYL

Fee amount on the original transaction.

HOLDING.LOT_LEVEL_POSITION

expenses

49 – Traded_Interest_Local

Optional

MONEYL

Total purchased accrued interest at the lot level. This value reflects the accrual provided at the time of the opening open lot.

HOLDING.LOT_LEVEL_POSITION

int_purchased

51 – Stamp_Duty_Tax_L

Optional

MONEYL

Tag for stamp duty.

HOLDING.POSITION_COST_LOT

other_charges

56 – Reason

Optional

VARCHAR2(255)

 

HOLDING.LOT_LEVEL_POSITION

reason_code

63 – Settlement Currency

Optional

CHAR (3)

Settlement currency of the settlement amount on the trade.

HOLDING.LOT_LEVEL_POSITION

user_group1

64 – Settle_Amt_S

Optional

MONEYS

Actual settlement amount on the original transaction.

HOLDING.LOT_LEVEL_POSITION

cash_equivalents

75 – Amort_Yield

Optional

DEC

Calculated amortization yield used to calculate daily amortization.

HOLDING.LOT_LEVEL_POSITION

pur_yld

79 – Xrate_Settle_Base

Optional

DEC

FX Rates between settlement amount and the base equivalent of the transaction.

HOLDING.LOT_LEVEL_POSITION

trade_fx_rate

87 – Trade_Date_FX_Rate

Optional

DEC

FX Rates at the time of the open transaction that was used to calculate the base equivalent.

HOLDING.LOT_LEVEL_POSITION

exchange_rate

88 – Broker_Code

Optional

CHAR (20)

Code assigned to the broker on the transaction.

HOLDING.LOT_LEVEL_POSITION

broker_code

114 – Maturity Interest

Optional

MONEYL

Original interest at maturity that was provided on the trade message.

HOLDING.LOT_LEVEL_POSITION

income_due

128 – Cost_Curr_Id_Local

Optional

MONEYL

Current local cost of the open lot.

HOLDING.POSITION_COST_LOT

local_gaap_cost_value

129 – Cost_Curr_Id_Base

Optional

MONEYB

Current base cost of the open lot.

HOLDING.POSITION_COST_LOT

gaap_cost_value

151 – Open_Event_Id

Optional

CHAR

Assigned unique event ID.

HOLDING.LOT_LEVEL_POSITION

orig_lot_number

159 – Quantity_Orig

Optional

DEC

Original quantity provided on the transaction.

HOLDING.LOT_LEVEL_POSITION

purchase_quantity

160 – Open_Unit_Price

Optional

DEC

Original price provided on the transaction.

HOLDING.LOT_LEVEL_POSITION

open_mkt_price

161 – Ucost_Id_L

Optional

DEC

Unit cost local of the open lot.

HOLDING.POSITION_COST_LOT

local_stat_unit_cost

162 – Original_Cost_Local

Optional

MONEYL

Original local cost of the open lot.

HOLDING.POSITION_COST_LOT

local_stat_cost_value

163 – Current_Quantity

Optional

DEC

Current quantity of the open lot.

HOLDING.LOT_LEVEL_POSITION

par_or_shares

164 – Open_Hld_Per_Date

Optional

DATE

Date used to determine the length of time a security is held.

HOLDING.POSITION_COST_LOT

pcl_user_date3

166 – Original_Cost_Base

Optional

MONEYB

Original base cost of the open lot.

HOLDING.POSITION_COST_LOT

stat_cost_value

216 – Orig_Acquisition_Date

Optional

DATE

Date the open lot was originally acquired.

HOLDING.LOT_LEVEL_POSITION

orig_acq_date

255 – Cls_Fee_B

Optional

MONEYB

Field for the cap fee base.

HOLDING.LOT_LEVEL_POSITION

cir_brk_fees

266 – Cls_Fee_L

Optional

MONEYL

Field for the cap fee local.

HOLDING.LOT_LEVEL_POSITION

exec_brk_fees

274 – Settled_Qty

Optional

DEC

Contractually settled quantity of the open lot as of the requested accounting date.

HOLDING.LOT_LEVEL_POSITION

settle_quantity

462 – Accr_Ltd_Local

Optional

MONEYL

Total period-to-date accrued income local of the open lot for the given effective date.

HOLDING.LOT_LEVEL_POSITION

accrued_income_local

465 – Accr_Ptd_Local

Optional

MONEYL

 

HOLDING.LOT_LEVEL_POSITION

interest_earned

475 – Traded_Interest_Base

Optional

MONEYB

Total purchased accrued interest at the lot level. Reflects the accrual provided at the time of the opening open lot.

HOLDING.LOT_LEVEL_POSITION

int_received

481 – Market_Price

Optional

NUMBER (28,12)

 

HOLDING.LOT_LEVEL_POSITION

price

484 – FX_Rate

Optional

DEC

Procedure retrieves the value.

HOLDING.LOT_LEVEL_POSITION

curr_fx_rate

487 – Market_Value_Local

Optional

MONEYL

Local market value of the position for the given effective date.

HOLDING.LOT_LEVEL_POSITION

local_market_value

488 – Market_Value_Base

Optional

MONEYB

Base market value of the position for the given effective date.

HOLDING.LOT_LEVEL_POSITION

market_value

489 – Urgl_Security_Local

Optional

MONEYL

Total unrealized gain/loss local of the open lot for the given effective date.

HOLDING.LOT_LEVEL_POSITION

local_unrealized_gl

491 – Urgl_Security_Base

Optional

MONEYB

Total security unrealized gain/loss local of the open lot for the given effective date.

HOLDING.LOT_LEVEL_POSITION

unrealized_sec_gl

492 – Urgl_Total_Base

Optional

MONEYB

Total security unrealized gain/loss base of the open lot for the given effective date.

HOLDING.LOT_LEVEL_POSITION

base_unrealized_gl

493 – Urgl_Currency_Base

Optional

MONEYB

Total currency unrealized gain/loss of the open lot for the given effective date.

HOLDING.LOT_LEVEL_POSITION

unrealized_curr_gl

575 – Oid_Ltd_Local

Optional

MONEYL

Calculated life-to-date OID income in local currency.

HOLDING.LOT_LEVEL_POSITION

oid_ltd_income_local

580 – Amort_Ltd_Local

Optional

MONEYL

Local life-to-date amortization applied to the open lot as of the accounting date.

HOLDING.LOT_LEVEL_POSITION

amortization_ltd_local

585 – Accr_Ltd_Base

Optional

MONEYB

Total period-to-date accrued income base of the open lot for the given effective date.

HOLDING.LOT_LEVEL_POSITION

accrued_income

588 – Accr_Ptd_Base

Optional

MONEYB

Total period-to-date accrual base at the lot level.

HOLDING.LOT_LEVEL_POSITION

income

590 – Oid_Ltd_Base

Optional

MONEYB

Life-to-date OID income in base currency.

HOLDING.LOT_LEVEL_POSITION

oid_ltd_income

595 – Amort_Ltd_Base

Optional

MONEYB

Base life-to-date amortization applied to the open lot as of the accounting date.

HOLDING.LOT_LEVEL_POSITION

amortization_ltd_base

701 – Batch_Identifier

Optional

VARCHAR2 (55)

STAR provided ability to link transactions together for reporting and cancellation purposes. The user provides this value on the originating transaction.

HOLDING.LOT_LEVEL_POSITION

batch_event_id

761 – Trade_Ticket_Number

Optional

VARCHAR2 (55)

User defined field, typically populated with the source system internal ID.

HOLDING.LOT_LEVEL_POSITION

master_ticket_number

768 – Estimate_Vs_Final

Optional

CHAR

Indicator that determines if the Mutual Fund Price is: E = an Estimate, F = Actual

HOLDING.LOT_LEVEL_POSITION

user_group6

1237 – Clearing_Broker_Code

Optional

VARCHAR2 (12)

Code for the clearing broker on the transaction.

HOLDING.LOT_LEVEL_POSITION

clearing_broker_code

1632 – Client_Id

Optional

CHAR (20)

 

HOLDING.LOT_LEVEL_POSITION

client_id

1887 – User_Char1

Optional

VARCHAR2(255)

User defined field

HOLDING.LOT_LEVEL_POSITION

user_char1

1888 – User_Char2

Optional

VARCHAR2(255)

User defined field

HOLDING.LOT_LEVEL_POSITION

user_char2

1889 – User_Char3

Optional

VARCHAR2(255)

User defined field

HOLDING.LOT_LEVEL_POSITION

user_char3

1890 – User_Defined_Float1

Optional

NUMBER(28,12)

User defined field

HOLDING.LOT_LEVEL_POSITION

user_float1

1891 – User_Defined_Float2

Optional

NUMBER(28,12)

User defined field

HOLDING.LOT_LEVEL_POSITION

user_float2

1892 – User_Defined_Float3

Optional

NUMBER(28,12)

User defined field

HOLDING.LOT_LEVEL_POSITION

user_float3

3715 – Accrued_Interest_Type

Optional

CHAR

 

HOLDING.LOT_LEVEL_POSITION

accrued_interest_type

3746 – Original_Acquisition_Cost_Base

Optional

MONEYB

Original acquisition cost base of the open lot.

HOLDING.POSITION_COST_LOT

orig_acq_cost_base

3747 – Original_Acquisition_Cost_Local

Optional

MONEYL

Original acquisition cost local of the open lot.

HOLDING.POSITION_COST_LOT

orig_acq_cost_local

3752 – Other_Fee_Local

Optional

MONEYL

Miscellaneous tag for expenses, taxes, etc. which do not fit into one of the other transaction tags.

HOLDING.POSITION_COST_LOT

sec_fees

4440 – Tips_Ltd_Local

Optional

MONEYL

Life-to-date TIPS income in local currency.

HOLDING.LOT_LEVEL_POSITION

tip_ltd_income_local

4445 – Tips_Ltd_Base

Optional

MONEYB

Calculated life-to-date TIPS income in base currency.

HOLDING.LOT_LEVEL_POSITION

tip_ltd_income

4483 – Tips_Index_Ratio

Optional

DEC

Last applied index ratio for the open lot.

HOLDING.LOT_LEVEL_POSITION

indx_weight

4597 – Performance_Flow

Optional

MONEYL

Some events processed do not have a cost associated with them. Eagle Performance requires a value to accurately calculate the security's performance.

HOLDING.LOT_LEVEL_POSITION

performance_value

4598 – Performance_Price

Optional

DEC

Some events processed do not have a cost associated with them. Eagle Performance requires a value to accurately calculate the security's performance.

HOLDING.LOT_LEVEL_POSITION

performance_price

4601 – Amortized_Cost_Local

Optional

MONEYL

Total amortized cost local for the open lot. Includes original cost, amortization, TIPS income, and OID income local.

HOLDING.LOT_LEVEL_POSITION

book_value_local

n/a

Optional

DATE

Populate with current system time.

HOLDING.POSITION/LOT_LEVEL_POSITION/POSITION_COST_LOT

update_date

944 – Update Source

Optional

VARCHAR2 (8)

 

HOLDING.POSITION/LOT_LEVEL_POSITION/POSITION_COST_LOT

update_source

4602 – Amortized_Cost_Base

Optional

MONEYB

Total amortized cost local for the open lot. Includes original cost, amortization, TIPS income, and OID income local.

HOLDING.LOT_LEVEL_POSITION

book_value

9985 – Perform Flag

Optional

CHAR

 

HOLDING.POSITION

perform_flag

580 - Amortization LTD Local

Optional

MONEYL

 This value is derived as local life to date amortization of the lot minus close amortization value if the lot is closed (Amort_LTD_Local - Cls_Amort_Local)

HOLDING.LOT_LEVEL_POSITION

 amortization_ltd_local

595 - Amortization LTD Base

Optional

MONEYB

 This value is STAR derived as base life to date amortization of the lot minus close amortization value if the lot is closed (Amort_LTD_Base - Cls_Amort_Base)

HOLDING.LOT_LEVEL_POSITION

 amortization_ltd_base

583 - Amort PTD Local

Optional

MONEYL

 The total Period-to-Date accrued income local of the open lot for the given accounting date. HOLDING Position_cost_lot amort_ptd_local Estar_income_activity Amort_ptd_local

 

 

598 - Amort PTD Base

Optional

MONEYB

 The total Period-to-Date amortization base of the open lot for the given accounting date.

HOLDING.POSITION_COST_LOTESTAR.ESTAR_INCOME_ACTIVITY

 amort_ptd_base amort_ptd_base

585 - Accrual LTD Base ESTAR

Optional

MONEYB

 The total Life-to-Date accrued income base of the open lot for the given accounting date.

HOLDING.OSITION_COST_LOTESTAR.STAR_INCOME_ACTIVITY

accr_ltd_base  accr_ltd_base

462 - Accrual LTD Local ESTAR

Optional

MONEYL

 The total Life-to-Date accrued income local of the open lot for the given accounting date. HOLDING Position_cost_lot accr_ltd_local Estar_income_activity Accr_ltd_local

 

 

718 - Total Accrued Income PTD Local

Optional

MONEYL

 The total period-to-date accrued income local of the open lot for the given effective date - Interest_earned + Int_purchased - interest_sold

HOLDING.LOT_LEVEL_POSITION

 accrued_income_local

726 - Total Accrued Income PTD Base

Optional

MONEYB

 The total period-to-date accrued income base of the open lot for the given effective date - (Interest_earned + Int_purchased - sold inter¬est) / exchange_rate

HOLDING.LOT_LEVEL_POSITION

 accrued_income

back to top

  • No labels