71 lines
2.7 KiB
SQL
71 lines
2.7 KiB
SQL
{% macro get_sql_t_collateral_nma_acc() %}
|
|
-- depends_on: {{ ref('m_MOPDB_C2D_T_COLLATERAL_ALL_SOURCES_OU_C2D_UC_NMA_ACC_SQ') }}
|
|
|
|
select 'NON_MARKET_ASSET' AS DATASET,
|
|
CAST (NULL AS DATE) AS DATABASE_ENTRY_TIMESTAMP,
|
|
SNAPSHOT_DATE,
|
|
REPORTING_NCB AS NCB,
|
|
MFI_ID,
|
|
CAST(NULL AS VARCHAR2(50 CHAR)) AS ISIN_CODE,
|
|
OTHER_REG_NO AS OTHER_REGISTRATION_NUMBER,
|
|
NOM_AMT_SUBMITTED AS NOMINAL_AMOUNT_SUBMITTED,
|
|
COLL_BEFORE_HAIRCUTS AS COLL_VALUE_BEFORE_HAIRCUTS,
|
|
COLL_AFTER_HAIRCUTS AS COLL_VALUE_AFTER_HAIRCUTS,
|
|
TYPE_OF_SYSTEM AS SYSTEM_TYPE,
|
|
TYPE_OF_OPERATION,
|
|
DOM_OR_XBORDER AS DOM_OR_CB,
|
|
CAST (NULL AS VARCHAR2(50 CHAR)) AS ISSUER_CRED_ASSESS_SOURCE,
|
|
CAST (NULL AS VARCHAR2(50 CHAR)) AS ISSUER_RATING_ENUM_VALUE,
|
|
CAST(NULL AS NUMBER(9,8)) ISSUER_RATING_NUMBER_VALUE,
|
|
NON_MKT_ASSET_TYPE AS NONMKT_ASSET_TYPE,
|
|
NO_AGGR_DEBTORS AS NO_OF_AGGREGATED_DEBTORS,
|
|
ELIGIBLE_VIA_GUAR AS ELIGIBLE_VIA_GUARANTOR,
|
|
DEBTOR_NAME,
|
|
DEBTOR_ID,
|
|
DEBTOR_TYPE AS DEBTOR_GROUP,
|
|
DEBTOR_CLASS,
|
|
DEBTOR_RESIDENCE,
|
|
DEBTOR_CAS AS DEBTOR_CRED_ASSESS_SOURCE,
|
|
DEBTOR_RATING_ENUM_VALUE,
|
|
DEBTOR_RATING_NUMBER_VALUE,
|
|
GUAR_NAME AS GUARANTOR_NAME,
|
|
GUAR_ID AS GUARANTOR_ID,
|
|
GUAR_RESIDENCE AS GUARANTOR_RESIDENCE,
|
|
GUAR_CRED_CAS AS GUARANTOR_CRED_ASSESS_SOURCE,
|
|
GUAR_RATING_ENUM_VALUE AS GUARANTOR_RATING_ENUM_VALUE,
|
|
GUAR_RATING_NUMBER_VALUE AS GUARANTOR_RATING_NUMBER_VALUE,
|
|
NO_AGGR_ASSETS AS NO_OF_AGGREGATED_ASSETS,
|
|
NCB_COMMENT AS NCB_COMMENTS,
|
|
MATURITY_DATE,
|
|
GUAR_CLASS AS GUARANTOR_CLASS,
|
|
CAST (NULL AS VARCHAR2(8 CHAR)) AS ISSUER_CLASS,
|
|
INTEREST_PAYMENT_TYPE,
|
|
RESIDUAL_MATURITY,
|
|
BUCKET_SIZE,
|
|
DEBTOR_CRED_PROV AS DEBTOR_CAS_PROVIDER,
|
|
GUAR_CRED_PROV AS GUARANTOR_CAS_PROVIDER,
|
|
CAST(NULL AS VARCHAR2(100 CHAR)) AS ISSUER_CAS_PROVIDER,
|
|
SECURED_FLAG AS SECURED,
|
|
DENOMINATION AS NON_MARKET_DENOMINATION,
|
|
MOBILISATION_CHANNEL,
|
|
CCB,
|
|
INVESTOR_SSS,
|
|
INTERMEDIARY_SSS,
|
|
ISSUER_SSS,
|
|
CAST(NULL AS VARCHAR2(6 CHAR)) AS TRIPARTY_AGENT,
|
|
DEBTOR_ID_TYPE AS DEBTOR_IDTYPE,
|
|
CAP,
|
|
REFERENCE_RATE,
|
|
REFERENCE_RATE_COMMENT,
|
|
RESET_PERIOD_MORE_ONE_YEAR,
|
|
OA_ID,
|
|
CONTRACT_ID,
|
|
INSTRMNT_ID,
|
|
'C2D_ACC' AS SOURCE,
|
|
{{ get_workflow_history_key() }} AS A_WORKFLOW_HISTORY_KEY
|
|
from {{ ref('m_MOPDB_C2D_T_COLLATERAL_ALL_SOURCES_OU_C2D_UC_NMA_ACC_SQ') }}
|
|
WHERE (reporting_ncb, SNAPSHOT_DATE) IN (
|
|
SELECT DISTINCT reporting_ncb, snapshot_date
|
|
FROM {{ ref('m_MOPDB_C2D_T_COLLATERAL_ALL_SOURCES_OU_C2D_UC_NMA_ACC_SQ') }})
|
|
AND A_WORKFLOW_HISTORY_KEY = MAX_A_WORKFLOW_HISTORY_KEY
|
|
{% endmacro %} |