G_SCRTY_MSTR_DT

Add:
– variables of the cube Owned securities group enriched (OWND_SCRTY_GRP_E) to the cube [simple_tooltip content='RGSTRY_TBL_SCRTS_E']Registry table of securities – Enriched[/simple_tooltip],
– variables of the cube [simple_tooltip content='CNTRPRTS_E']Counterparties – Enriched[/simple_tooltip] to the cube securities (SHSG4_SCRTY)

Calculate:
– difference of [simple_tooltip content='DT_MTRTY; Date on which the debt instrument is actually redeemed']Maturity date[/simple_tooltip] and [simple_tooltip content='DT_ISS; The date on which the securities are delivered to the underwriter by the issuer against payment. This is the date when the securities are available for delivery to investors for the first time. Note: For a strip this column indicates the date on which the coupon/principla is stripped']Issue date[/simple_tooltip] (in days)

Add variables:
– [simple_tooltip content='INSTR_CLASS; Classification of the security according to ESA 2010']Instrument ESA 2010 class[/simple_tooltip]
– [simple_tooltip content='ISSUER_SECTOR; Institutional sector of the issuer according to ESA 2010']Issuer ESA 2010 sector[/simple_tooltip]
– [simple_tooltip content='SENIORITY_TYPE; Instrument seniority type']Instrument seniority type[/simple_tooltip]

Manipulate the dataset to obtain the structure, and remove unnecessary variables

Extract:
– relevant instruments for Securities master data (SCRTY_MSTR_DT)

Scheme dependencies:

Transformation Scheme ID

G_SCRTY_MSTR_DT

Description

Setup of the dataset Security master data

Classification

Phase Type Subtype Related entity
Generation Generation SHS

Natural language

Add:
– variables of the cube Owned securities group enriched (OWND_SCRTY_GRP_E) to the cube Registry table of securities – Enriched,
– variables of the cube Counterparties – Enriched to the cube securities (SHSG4_SCRTY)

Calculate:
– difference of Maturity date and Issue date (in days)

Add variables:
Instrument ESA 2010 class
Issuer ESA 2010 sector
Instrument seniority type

Manipulate the dataset to obtain the structure, and remove unnecessary variables

Extract:
– relevant instruments for Securities master data (SCRTY_MSTR_DT)

Scheme dependencies:

VTL Syntax

0 SCRTY_MSTR_DT := [inner RGSTRY_TBL_SCRTS_E as "A", OWND_SCRTY_GRP_E as "B" on A.SCRTY_ID = B.SCRTY_ID] {keep (A.CLLTRL_LCTN, A.CRRNCY_DNMNTN, A.DT_ISS, A.DT_MTRTY, A.DT_SCRTY_STTS, A.GRNTR_ID, A.IS_LSTD, A.ISIN, A.ISSR_ID, A.PRMRY_ASST_CLSSFCTN, A.SCRTY_GRNT_LVL, A.SCRTY_LVL, A.SCRTY_RNK_LVL, A.SCRTY_STTS, A.TYP_ASST_SCRTSTN, A.TYP_INSTRMNT, A.UNT_MSR_NV, B.OWNR_INTRNL_ID, B.FRBRNC_STTS, B.DT_FRBRNC_STTS, B.ARRRS, B.DT_PST_D, B.LGD_DWNTRNS, B.LGD_NRML)};
1 SCRTY_MSTR_DT := [inner SCRTY_MSTR_DT as "A", CNTRPRTS_E as "B" on A.ISSR_ID = B.CNTRPRTY_ID] {keep (A.CLLTRL_LCTN, A.CRRNCY_DNMNTN, A.DT_ISS, A.DT_MTRTY, A.DT_SCRTY_STTS, A.GRNTR_ID, A.IS_LSTD, A.ISIN, A.ISSR_ID, A.PRMRY_ASST_CLSSFCTN, A.SCRTY_GRNT_LVL, A.SCRTY_LVL, A.SCRTY_RNK_LVL, A.SCRTY_STTS, A.TYP_ASST_SCRTSTN, A.TYP_INSTRMNT, A.UNT_MSR_NV, A.OWNR_INTRNL_ID, A.FRBRNC_STTS, A.DT_FRBRNC_STTS, A.ARRRS, A.DT_PST_D, A.LGD_DWNTRNS, A.LGD_NRML, B.CNTRY, B.CRDT_QLTY_STTS, B.DT_DFLT_STTS, B.DT_PRFRMNG_STTS, B.ECNMC_ACTVTY, B.INSTTTNL_SCTR, B.INSTTTNL_SCTR_CNTRL, B.INTRNTNL_ORGNSTN, B.LEI, B.NM_ENTTY, B.STTS, B.DT_STTS)};
2 SCRTY_MSTR_DT := SCRTY_MSTR_DT [calc DT_MTRTY - DT_ISS as "NMBR_OF_DYS"];
3 SCRTY_MSTR_DT := SCRTY_MSTR_DT [calc (G_INSTR_CLASS1 (TYP_INSTRMNT, IS_LSTD, INSTTTNL_SCTR, NMBR_OF_DYS)) as "INSTR_CLASS"];
4 SCRTY_MSTR_DT := SCRTY_MSTR_DT [calc (G_ISSUER_SECTOR1 (INSTTTNL_SCTR, INSTTTNL_SCTR_CNTRL)) as "ISSUER_SECTOR"];
5 SCRTY_MSTR_DT := SCRTY_MSTR_DT [calc (G_SENIORITY_TYPE (SCRTY_GRNT_LVL, SCRTY_RNK_LVL, SCRTY_LVL)) as "SENIORITY_TYPE"];
6 SCRTY_MSTR_DT := SCRTY_MSTR_DT [calc (M_ISSUER_NACE_SECTOR (ECNMC_ACTVTY)) as "ISSUER_NACE_SECTOR"];
7 CNTRPRTS_PD_SUB := CNTRPRTS_PD [filter (PRSPCTV = "1"), keep (OBSRVD_AGNT_ID, CNTRPRTY_ID, PD)];
8 SCRTY_MSTR_DT := [inner SCRTY_MSTR_DT as "A", CNTRPRTS_PD_SUB as "B" on A.OWNR_INTRNL_ID = B.OBSRVD_AGNT_ID and A.ISSR = B.CNTRPRTY_ID] {keep (A.ALL_VARIABLES, B.OBSRVD_AGNT_ID, PD)};
9 SCRTY_MSTR_DT := SCRTY_MSTR_DT [keep (OWNR_INTRNL_ID role Measure, SCRTY_ID role Identifier, ISIN, INSTR_CLASS, DT_ISS as "ISSUE_DATE", DT_MTRTY as "MAT_DATE", CRRNCY_DNMNTN as "NOM_CURR", PRMRY_ASST_CLSSFCTN as "PRIMARY_ASSET_CLASS", SENIORITY_TYPE, TYP_ASST_SCRTSTN as "ASSET_SECURITISATION_TYPE", SCRTY_STTS as "SECURITY_STATUS", DT_SCRTY_STTS as "SECURITY_STATUS_DATE", ARRRS as "ARREARS", DT_PST_D as "ARREARS_DATE", CLLTRL_LCTN as "COLLATERAL_AREA", GRNTR_ID as "GUARANTOR_ID", ISSR_ID as "ISSUER_ID", LEI as "ISSUER_LEI", NM_ENTTY as "ISSUER_NAME", CNTRY as "ISSUER_COUNTRY", ISSUER_SECTOR, ISSUER_NACE_SECTOR, STTS as "ENTITY_STATUS", DT_STTS as "ENTITY_STATUS_DATE", CRDT_QLTY_STTS, IS_SHRT_PSTN, DT_DFLT_STTS, DT_PRFRMNG_STTS, LGD_DWNTRNS, LGD_NRML, PD)];
10 SCRTY_MSTR_DT := SCRTY_MSTR_DT [filter (INSTR_CLASS in ("F_31", "F_32", "F_511", "F_521", "F_522"))];