USE staging_namecheck;
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

IF DB_NAME() <> 'staging_namecheck'
BEGIN
    RAISERROR('Wrong database.', 16, 10);
    RETURN;
END;
GO

IF EXISTS (
       SELECT *
       FROM   sys.objects
       WHERE  OBJECT_ID = OBJECT_ID(N'dbo.v_TXM_Batch_Maerki_Profiling_raw')
              AND TYPE IN (N'V')
   )
BEGIN
    DROP VIEW dbo.v_TXM_Batch_Maerki_Profiling_raw
END
GO

CREATE VIEW [dbo].[v_TXM_Batch_Maerki_Profiling_raw]
AS
SELECT REC_ID,
       MSG,
       score_5F03C6ED22324CE3AD84DEC601D19223 AS score,
       sign_5F03C6ED22324CE3AD84DEC601D19223 AS isalert,
       class_5F03C6ED22324CE3AD84DEC601D19223 AS class,
       txm_chunk_id,
       dbo.gsf_float_to_datetime(txm_worker_tr_dt) AS proccesing_date,
       T_DATE AS t_date,
       [ZV_ZLG_T/USERBK_NR],
       [ZV_ZLG_T/ZV_ZLG_SYS_LNR],
       [KT_BUCH/KT_BUCH_LNR],
       [KT_BUCH_DET/KT_BUCH_DET_LNR],
       [ZV_ZLG_T/AUF_LNR],
       [OM_AUF/AUF_ID],
       [ZV_ZLG_T/TRAK_REF_LNR],
       [ZV_ZLG_T/TRAK_REF_TYP],
       [ZV_ZLG_T/KT_LNR_B],
       DEBIT,
       CREDIT,
       [ZV_AUF_T/ZV_EING_AUSG_CD],
       [ZV_ZLG_T/OM_TEXT_LNR],
       [ZV_ZLG_T/ZV_ADR_EFF_LNR],
       [OM_AUF/OM_TEXT_LNR],
       [ZV_AUF_T/ZV_ADR_EFF_LNR],
       [ZV_AUF_T/TRAK_REF_TYP],
       [ZV_AUF_T/TRAK_REF_LNR],
       [ZV_ZLG_T/LAND_LNR_DOM],
       [ZV_ZLG_T/LAND_LNR_DOM_ISO],
       [OM_AUF/LAND_LNR_DOM],
       [OM_AUF/LAND_LNR_DOM_ISO],
       INFLOW,
       OUTFLOW,
       INFLOW_EXT_BANK,
       OUTFLOW_EXT_BANK,
       [OM_AUF/SITZ_NR_AUSF],
       [OM_AUF/SITZ_NR_BEZ],
       [OM_AUF/AUSF_DT_IST],
       [OM_AUF/AUSF_DT_SOLL],
       [OM_AUF/AUF_DT_ERT],
       [ZV_ZLG_T/VALUTA],
       T_Time,
       [ZV_ZLG_T/BUCHTYP_CD],
       [ZV_ZLG_T/BETRAG_BWRG],
       [ZV_ZLG_T/BETRAG_ZWRG],
       [ZV_ZLG_T/ISO_WRG_CD],
       [ZV_ZLG_T/GF_ART],
       [ZV_ZLG_T/GF_NR],
       [KT_STAMM/KT_LNR],
       [KT_STAMM/EDIT_KT_NR],
       [KT_STAMM/KT_ART_NR],
       [KT_STAMM/EROEFF_DAT],
       KT_DURATION,
       [KT_SALDO/SALDO_BWRG],
       [KT_SALDO/SALDO_KWRG],
       KD_TOTAL_SALDO,
       [KD_STAMM/KD_LNR],
       [KD_STAMM/EDIT_KD_NR],
       [KD_STAMM/KD_HTYP_CD],
       [KD_STAMM/BANK_FLAG],
       [KD_STAMM/GESCHLECHT_CD],
       [KD_STAMM/VORNAME],
       [KD_STAMM/NAME_1],
       [KD_STAMM/KD_BEZ],
       [KD_STAMM/KD_RSTAT_CD],
       [KD_STAMM/KD_RSTAT_BEZ],
       [KD_STAMM/KD_RSTAT_AUSP_CD],
       [KD_STAMM/KD_RSTAT_AUSP_BEZ],
       [KD_STAMM/KDKAT_CD],
       [KD_STAMM/KDKAT_BEZ],
       [KD_STAMM/KD_SEGM_CD],
       [KD_STAMM/KD_SEGM_BEZ],
       [KD_STAMM/RFORM_CD],
       [KD_STAMM/RFORM_BEZ],
       [KD_STAMM/UID_NR],
       [KD_STAMM/GEBURT_DAT],
       [KD_STAMM/GEBURT_JAHR],
       [KD_STAMM/ALTER_JAHR],
       [KD_STAMM/TOD_DAT],
       [KD_STAMM/TOD_JAHR],
       [KD_STAMM/KD_SITZ_GSCH_CD],
       [KD_STAMM/TITEL],
       [KD_STAMM/GEBURT_ORT],
       [KD_STAMM/NOGA_CD],
       [KD_STAMM/FIN_VOLLM_CD],
       [KD_STAMM/NOGA_BEZ],
       [KD_STAMM/BERUF_CD],
       [KD_STAMM/BERUF_BEZ],
       [KD_STAMM/KAP],
       [KD_STAMM/EMAIL_ADR],
       [KD_STAMM/KD_VIP_CD],
       [KD_STAMM/KD_PEP_CD],
       [KD_STAMM/KD_PEP_CD_AUTO],
       [KD_STAMM/KD_PEP_DT_AUTO],
       PEP_RELATED_01,
       [KD_STAMM/KD_GRP_ART_CD],
       [KD_STAMM/LAND_LNR_NAT],
       [KD_STAMM/LAND_LNR_NAT_BEZ],
       [KD_STAMM/ISO_LAND_CODE_NAT],
       [KD_STAMM/LAND_LNR_NAT_2],
       [KD_STAMM/LAND_LNR_NAT_BEZ_2],
       [KD_STAMM/ISO_LAND_CODE_NAT_2],
       [KD_STAMM/LAND_LNR_AUSL],
       [KD_STAMM/LAND_LNR_AUSL_BEZ],
       [KD_STAMM/ISO_CODE_AUSL],
       [KD_STAMM/EROEFF_DAT],
       KD_DURATION,
       [KD_STAMM/KD_STAT_CD],
       [KD_GWV_STUF/KD_GWV_CD],
       [KD_GWV_STUF/SYS_DT],
       [KD_GWV_STUF/KD_GWV_CODE_BEZ],
       [KD_STAMM/KD_VSB_STAT_CD],
       [KD_STAMM/KD_VSB_STAT_BEZ],
       [KD_STAMM/VSB_STAT_DAT],
       [KD_STAMM/EB_LEGAL_KAT_CD],
       [KD_STAMM/BERFIRMA_NR],
       [KD_STAMM/KD_KOMPL_STRKT],
       [KD_STAMM/INTER_CMPY_CD],
       [KD_STAMM/KD_VERM_STAT_CD],
       [KD_STAMM_ZUS/LAND_LNR_GEBURT],
       [KD_STAMM_ZUS/KD_VERB_ZWECK_CD],
       [TX_KD_PROF/TX_FATCA_STAT_CD],
       [KD_VSB/KD_CHK_CD],
       [KD_KD_ERW/ANST_ART_CD_LIST],
       [KD_KD_EINN/EINN_ART_CD_LIST],
       KD_LAST_CHANGE,
       [KD_ADR/STRASSE],
       [KD_ADR/ADR_ORT_BEZ],
       [KD_ADR/REGION_CD],
       [KD_ADR/ADR_PLZ],
       [KD_ADR/ORT_ORT_BEZ],
       [KD_ADR/ORT_PLZ],
       [KD_ADR/ISO_LAND_CD],
       [KD_ADR/LAND_BEZ],
       KD_KD_01,
       KD_LNR_ZUW_LIST,
       VT_STAMM_01,
       VT_TYP_CD_LIST,
       SR_FELD_WERT_01,
       [SR_FELD_WERT/SR_FELD_BEZ],
       [SR_FELD_WERT/FELD_WERT],
       Cash,
       CNT_INFLOW_10d,
       CNT_INFLOW_1d,
       CNT_INFLOW_30d,
       CNT_INFLOW_3d,
       CNT_OUTFLOW_10d,
       CNT_OUTFLOW_1d,
       CNT_OUTFLOW_30d,
       CNT_OUTFLOW_3d,
       SUM_INFLOW_10d,
       SUM_INFLOW_1d,
       SUM_INFLOW_30d,
       SUM_INFLOW_3d,
       SUM_OUTFLOW_10d,
       SUM_OUTFLOW_1d,
       SUM_OUTFLOW_30d,
       SUM_OUTFLOW_3d,
       SUM_30d,
       SUM_INOUT_30d,
       MAX_KD_TOTAL_SALDO_30d,
       DAYS_FROM_LAST_TRANSACTION,
       TW_DATL_1d,
       TW_DATL_3d,
       TW_DATL_10d,
       TW_DATL_30d,
       [KT_STAMM/IBAN_ID],
       COUNTERPARTY_IBAN,
       KD_SCORING_RISK,
       KD_SCORING_RISKCLASS,
       KD_SCORING_RISK_SPILLOVER,
       KD_SCORING_RISKCLASS_SPILLOVER,
       CNT_TO_COUNTERPARTY_10d,
       SUM_TO_COUNTERPARTY_10d,
       CNT_FROM_COUNTERPARTY_10d,
       SUM_FROM_COUNTERPARTY_10d,
       COUNTERPARTY_IBAN_01,
       TRANSACTIONGROUP,
       ID,
       [KT_BUCH/BUCH_DAT_A],
       [KT_BUCH/BETRAG_KWRG],
       CounterpartyDetailsText,
       SUM_INOUT_1d,
       SUM_INOUT_3d,
       SUM_INOUT_10d,
       MAX_DAY_SUM_AMOUNT_1Y,
       MAX_CNT_MONTH_TRANS_1Y,
       AVG_CNT_MONTH_TRANS_1Y,
       CNT_MONTH_WITH_TRANS_1Y,
       MAX_TRANSACTION_1Y,
       MAX_MONTH_SUM_AMOUNT_1Y,
       MAX_MONTH_SUM_INFLOW_1Y,
       MAX_MONTH_SUM_OUTFLOW_1Y,
       AVG_KD_SALDO_1Y,
       MAX_KD_SALDO_1Y,
       CNT_INOUT_10d,
       CNT_INOUT_1d,
       CNT_INOUT_30d,
       CNT_INOUT_3d,
       Risk_country_flag,
       SUM_RiskCountries_INFLOW_30d,
       SUM_RiskCountries_OUTFLOW_30d,
       SUM_RiskCountries_INOUT_30d,
       CNT_RiskCountries_INFLOW_30d,
       CNT_RiskCountries_OUTFLOW_30d,
       CNT_RiskCountries_INOUT_30d,
       --kyc_indiv_bus_act_risk_country,
       --kyc_indiv_sensitive_industry2,
       --kyc_indiv_insider_information1,
       --kyc_indiv_expected_transactions,
       --kyc_corp_expected_transactions,
       --kd_verm_stat_cd_bez_lang,
       debug_dt_day,
       txm_chunk_id_internal,
       txm_debug_NumRec,
       txm_worker_comp_time,
       txm_worker_found_num,
       txm_worker_recno,
       TXM_CONFIG
FROM   dbo.TXM_Batch_Maerki_Profiling_raw
GO