106 lines
4.3 KiB
SQL
106 lines
4.3 KiB
SQL
{% macro get_sql_t_collateral_nma_dissem() %}
|
|
-- depends_on: {{ ref('m_MOPDB_C2D_T_COLLATERAL_ALL_SOURCES_OU_C2D_UC_NMA_DISSEM_SQ') }}
|
|
|
|
select 'NON_MARKET_ASSET' AS DATASET,
|
|
FILE_CREATION_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)) AS 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_UC' AS SOURCE,
|
|
{{ get_workflow_history_key() }} AS A_WORKFLOW_HISTORY_KEY
|
|
from {{ ref('m_MOPDB_C2D_T_COLLATERAL_ALL_SOURCES_OU_C2D_UC_NMA_DISSEM_SQ') }}
|
|
WHERE (reporting_ncb, SNAPSHOT_DATE) in (
|
|
SELECT DISTINCT
|
|
src.reporting_ncb,
|
|
src.snapshot_date
|
|
FROM
|
|
{{ ref('m_MOPDB_C2D_T_COLLATERAL_ALL_SOURCES_OU_C2D_UC_NMA_DISSEM_SQ') }} src
|
|
full outer join (SELECT DISTINCT
|
|
ncb,
|
|
snapshot_date,
|
|
DATABASE_ENTRY_TIMESTAMP
|
|
FROM
|
|
{{ this }}
|
|
WHERE
|
|
( ncb,
|
|
snapshot_date ) IN (
|
|
SELECT DISTINCT
|
|
reporting_ncb,
|
|
snapshot_date
|
|
FROM
|
|
{{ ref('m_MOPDB_C2D_T_COLLATERAL_ALL_SOURCES_OU_C2D_UC_NMA_DISSEM_SQ') }}
|
|
)
|
|
AND DATASET = 'NON_MARKET_ASSET'
|
|
AND SOURCE = 'C2D_UC'
|
|
GROUP BY
|
|
ncb,
|
|
snapshot_date,
|
|
DATABASE_ENTRY_TIMESTAMP
|
|
) trg on src.reporting_ncb = trg.ncb
|
|
and TRUNC(src.snapshot_date) = TRUNC(trg.SNAPSHOT_DATE)
|
|
WHERE
|
|
src.max_a_workflow_history_key = src.a_workflow_history_key
|
|
and src.FILE_CREATION_DATE > NVL(trg.DATABASE_ENTRY_TIMESTAMP,src.FILE_CREATION_DATE - 1)
|
|
)
|
|
AND A_WORKFLOW_HISTORY_KEY = MAX_A_WORKFLOW_HISTORY_KEY
|
|
AND (reporting_ncb, SNAPSHOT_DATE) NOT IN (
|
|
SELECT DISTINCT REPORTING_NCB, SNAPSHOT_DATE
|
|
FROM {{ this }}
|
|
WHERE SOURCE = 'C2D_ACC'
|
|
)
|
|
{% endmacro %} |