Option Explicit
' Macro: Import Relationships From Excel.bas
'
' This macro will import relationships using information provided in Excel spreadsheet.
'
' The Excel spreadsheet ought to have the following column format:
'
'1. Relationship Name - Optional
'2. Parent Table Name - Mandatory
'3. Child Table Name - Mandatory
'4. Relationship Type - Mandatory. It needs to be one of the following numeric values:
' Identifying => 0
' NonIdentifying => 1
' NonSpecific => 2
' NonIdentifying Optional => 3
' Subtype Cluster => 99 (If added, next cell, can be Complete 0 and InComplete - 1)
'
' The first row in the spreadsheet is skipped as it can be the column titles.
' Version 1.0
'*******************************************************************************************************
Column A Combined Values
source_system 'Unused Limits' (literal), ifrs9.committed_unutilized_v.source_system, ifrs9.deals_funded_unfunded_report.type, nan
facility_id ifrs9.committed_unutilized_v.LCPF_CMTMNT_REF, ifrs9.committed_unutilized_v.lcpf_cmtmnt_ref, ifrs9.deals_funded_unfunded_report.facility_ref
customer_id ifrs9.committed_unutilized_v.LCPF_CSTMR_MNMNC, ifrs9.committed_unutilized_v.lcpf_cstmr_mnmnc, ifrs9.deals_funded_unfunded_report.customer_equation_id_new ifrs9.deals_funded_unfunded_report.bank_new
branch RAW_DATA_VAULT.BRANCHES.CAPF_BRNCH_NUMBER, ifrs9.deals_funded_unfunded_report.account_branch_number_new, raw_data_vault.branches.capf_brnch_number
basic ifrs9.committed_unutilized_v.LCPF_CSTMR_MNMNC, ifrs9.committed_unutilized_v.nepf_extrnl_accnt_nmbr, ifrs9.deals_funded_unfunded_report.external_account_number_new
suffix ifrs9.committed_unutilized_v.SUFFIX, ifrs9.committed_unutilized_v.suffix, ifrs9.deals_funded_unfunded_report.account_suffix_new
customer_group_name IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.customer_group_name, IFRS9.IFRS9_CUSTOMER_INFO.customer_group_name, ifrs9.customer_eom_info_unused_lim.customer_group_name, ifrs9.deals_funded_unfunded_report.customer_group_name
customer_name_arabic IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.full_name, ifrs9.customer_eom_info_unused_lim.full_name, ifrs9.deals_funded_unfunded_report.full_name_in_arabic ifrs9.orbis.bankdesc
customer_name_english IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.full_name_en, ifrs9.customer_eom_info_unused_lim.full_name_en, ifrs9.deals_funded_unfunded_report.full_name_in_english ifrs9.orbis.bankdesc
customer_segment IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.customer_segmant, ifrs9.customer_eom_info_unused_lim.customer_segmant, ifrs9.deals_funded_unfunded_report.customer_segment
industry_code IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.ECONOMIC_SECTOR, ifrs9.customer_eom_info_unused_lim.economic_sector, ifrs9.deals_funded_unfunded_report.economic_sector
industry_description IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.industry_sector, ifrs9.customer_eom_info_unused_lim.industry_sector, ifrs9.deals_funded_unfunded_report.economic_sector_description
country_of_risk IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.risk_country, ifrs9.customer_eom_info_unused_lim.risk_country, ifrs9.deals_funded_unfunded_report.bank_new ifrs9.ifrs9_customer_info.risk_country
ab_default_rating IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.DEFAULT_RISK_RATING, ifrs9.customer_eom_info_unused_lim.default_risk_rating, ifrs9.deals_funded_unfunded_report.default_risk_rating ifrs9.external_ratings.external_rating_int
mra_rating IFRS9.MRA_DATA.mra, ifrs9.mra_data.mra
mra_pd IFRS9.MRA_DATA.system_pd, ifrs9.mra_data.system_pd
restructured_flag IFRS9.rescheduling_restructuring_v.FLAG, ifrs9.rescheduling_restructuring_v.flag
rescheduled_flag IFRS9.rescheduling_restructuring_v.FLAG, ifrs9.rescheduling_restructuring_v.flag
customer_delinquency_status ifrs9.deals_funded_unfunded_report.customer_delinquency_status_new, ifrs9.ifrs9_monthly_run_data.customer_delinquency_status
classification ifrs9.committed_unutilized_v.CLASSIFICATION, ifrs9.committed_unutilized_v.classification, ifrs9.deals_funded_unfunded_report.customer_classification_new
classification_desc ifrs9.committed_unutilized_v.CLASSIFICATION, ifrs9.committed_unutilized_v.classification, ifrs9.deals_funded_unfunded_report.classification_desc_new, nan
facility_reference ifrs9.committed_unutilized_v.LCPF_CMTMNT_REF, ifrs9.committed_unutilized_v.lcpf_cmtmnt_ref, ifrs9.deals_funded_unfunded_report.facility_ref
product_segment 'Corporate' (literal), ifrs9.deals_funded_unfunded_report.product_name ifrs9.deals_funded_unfunded_report.customer_segment, literal:'Corporate', nan
facility_product_type RAW_DATA_VAULT.additional_account_info.b0pf_bsc, ifrs9.committed_unutilized_v.LCPF_CMTMNT_REF, ifrs9.committed_unutilized_v.FACILITY_TYPE, ifrs9.committed_unutilized_v.facility_type, ifrs9.deals_funded_unfunded_report.product_name ifrs9.deal_types_desc_v.field_value_description
facility_start_date ifrs9.committed_unutilized_v.FACILITY_START_DATE, ifrs9.committed_unutilized_v.facility_start_date, ifrs9.deals_funded_unfunded_report.start_full_date
facility_maturity_date ifrs9.committed_unutilized_v.FACILITY_MATURITY_DATE, ifrs9.committed_unutilized_v.facility_maturity_date, ifrs9.deals_funded_unfunded_report.maturity_full_date
facility_currency ifrs9.committed_unutilized_v.FACILITY_CURRENCY, ifrs9.committed_unutilized_v.facility_currency, ifrs9.deals_funded_unfunded_report.currency_code
outstanding_balance_usd ifrs9.committed_unutilized_v.OUTSNDING_BLNC_USD, ifrs9.committed_unutilized_v.outsnding_blnc_usd, ifrs9.deals_funded_unfunded_report.outstanding_balance_usd
outstanding_balance_original_curreny ifrs9.committed_unutilized_v.OUTSNDING_BLNC_ORG_CCY, ifrs9.committed_unutilized_v.outsnding_blnc_org_ccy, ifrs9.deals_funded_unfunded_report.outstanding_balance
outstanding_balance_base_currency ifrs9.committed_unutilized_v.OUTSNDING_BLNC_LCL_CCY, ifrs9.committed_unutilized_v.outsnding_blnc_lcl_ccy, ifrs9.deals_funded_unfunded_report.outstanding_balance_lcl ifrs9.deals_funded_unfunded_report.outstanding_balance_usd
limit_in_base_currency constant:'TBD', ifrs9.committed_unutilized_v.FCLTY_LMT_USD, ifrs9.committed_unutilized_v.FCLTY_LMT_LCL, ifrs9.committed_unutilized_v.FCLTY_LMT_USD, ifrs9.committed_unutilized_v.FCLTY_LMT_LCL, ifrs9.committed_unutilized_v.LCPF_LOAD_COUNTRY, ifrs9.committed_unutilized_v.fclty_lmt_usd; ifrs9.committed_unutilized_v.fclty_lmt_lcl
principal_repayment_frequency '0' (literal), ifrs9.deals_funded_unfunded_report.payment_frequency_description ifrs9.interest_rate_v.interestperiodicity_desc, literal:'0', nan
amortization_type 'Bullet' (literal), ifrs9.interest_rate_v.amortizationtype ifrs9.deals_funded_unfunded_report.deal_type ifrs9.deals_funded_unfunded_report.amortization_type_new ifrs9.deals_funded_unfunded_report.payment_frequency_description_new, literal:'Bullet', nan
interest_rate_type 'Fixed' (literal), ifrs9.interest_rate_v.interestratetype, literal:'Fixed', nan
interest_rate_spread '' (literal), ifrs9.interest_rate_v.clientratespread ifrs9.interest_rate_v.fixedrate, literal:'', nan
base_rate '' (literal), ifrs9.interest_rate_v.fixingatbsd, literal:'', nan
interest_rate_for_discounted_bills '' (literal), ifrs9.interestreceivedinadvanced_v.interest_rate, literal:'', nan
applied_interest_rate ifrs9.committed_unutilized_v.EFFECTIVE_INTEREST_RATE, ifrs9.committed_unutilized_v.effective_interest_rate, ifrs9.interest_rate_v.clientratespread ifrs9.interest_rate_v.fixingatbsd ifrs9.interestreceivedinadvanced_v.interest_rate ifrs9.interest_rate_v.fixedrate
interest_frequency '0' (literal), ifrs9.committed_unutilized_v.REPAYMENT_FREQUENCY, ifrs9.deals_funded_unfunded_report.payment_frequency_description ifrs9.interest_rate_v.interestperiodicity_desc, literal:'0'
facility_rating_at_origination ifrs9.external_ratings.external_ratings_origin_int, ifrs9.rtng_orign_cmtd_unutlzd_temp.gfpf_ab_rating
facility_rating_at_reporting_date IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.default_risk_rating, ifrs9.customer_eom_info_unused_lim.default_risk_rating, ifrs9.external_ratings.external_rating_int
facility_dpd ifrs9.committed_unutilized_v.FACILITY_DPD, ifrs9.committed_unutilized_v.facility_dpd, ifrs9.deals_funded_unfunded_report.facility_dpd
limit_category ifrs9.committed_unutilized_v.IMIT_CATEGORY, ifrs9.committed_unutilized_v.imit_category, ifrs9.deals_funded_unfunded_report.limit_category_new
collateral_cover ifrs9.collaterals_final.facility_collateral_cover, literal:'0', nan
cash_margin ifrs9.committed_unutilized_v.CASH_MARGIN_USD, ifrs9.committed_unutilized_v.cash_margin_usd, ifrs9.unfunded_cash_margin.mrgn_bal_usd
total_collateral_cover__usd_ '0' (literal), ifrs9.collaterals_final.cash_collateral ifrs9.collaterals_final.other_collateral ifrs9.unfunded_cash_margin.mrgn_bal_usd, literal:'0', nan
coa_item ifrs9.committed_unutilized_v.COA_ITEM, ifrs9.committed_unutilized_v.coa_item, ifrs9.deals_funded_unfunded_report.chart_of_account_key
stage derived from committed_unutilized_v.coa_item, default_risk_rating, restructure_flag, reschedule_flag, customer_delinquency_status, ifrs9.committed_unutilized_v.COA_ITEM, IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.DEFAULT_RISK_RATING, ifrs9.ifrs9_monthly_run_data.customer_delinquency_status, IFRS9.rescheduling_restructuring_v.FLAG, IFRS9.rescheduling_restructuring_v.LOAD_COUNTRY, ifrs9.rtng_orign_cmtd_unutlzd_temp.gfpf_ab_rating, ifrs9.committed_unutilized_v.COA_ITEM, ifrs9.committed_unutilized_v.LCPF_LOAD_COUNTRY, ifrs9.ifrs9_monthly_run_data.customer_delinquency_status, IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.default_risk_rating, ifrs9.rtng_orign_cmtd_unutlzd_temp.gfpf_ab_rating, IFRS9.rescheduling_restructuring_v.FLAG, ifrs9.deals_funded_unfunded_report.ab_default_rating ifrs9.deals_funded_unfunded_report.rescheduled_flag ifrs9.deals_funded_unfunded_report.restructured_flag ifrs9.deals_funded_unfunded_report.rating_at_origin ifrs9.deals_funded_unfunded_report.rating_at_reporting_date ifrs9.deals_funded_unfunded_report.customer_delinquency_status ifrs9.deals_funded_unfunded_report.stage, nan
staging_reason derived from default_risk_rating, restructure_flag, reschedule_flag, rating comparison, ifrs9.committed_unutilized_v.COA_ITEM, IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.DEFAULT_RISK_RATING, ifrs9.ifrs9_monthly_run_data.customer_delinquency_status, IFRS9.rescheduling_restructuring_v.FLAG, IFRS9.rescheduling_restructuring_v.LOAD_COUNTRY, ifrs9.rtng_orign_cmtd_unutlzd_temp.gfpf_ab_rating, ifrs9.committed_unutilized_v.COA_ITEM, ifrs9.committed_unutilized_v.LCPF_LOAD_COUNTRY, ifrs9.ifrs9_monthly_run_data.customer_delinquency_status, IFRS9.CUSTOMER_EOM_INFO_UNUSED_LIM.default_risk_rating, ifrs9.rtng_orign_cmtd_unutlzd_temp.gfpf_ab_rating, IFRS9.rescheduling_restructuring_v.FLAG, ifrs9.deals_funded_unfunded_report.ab_default_rating ifrs9.deals_funded_unfunded_report.rescheduled_flag ifrs9.deals_funded_unfunded_report.restructured_flag ifrs9.deals_funded_unfunded_report.rating_at_origin ifrs9.deals_funded_unfunded_report.rating_at_reporting_date ifrs9.deals_funded_unfunded_report.stage ifrs9.deals_funded_unfunded_report.coa_item, nan
lgd constant:'TBD', nan
moody_s_rating ifrs9.external_ratings.moody ifrs9.current_external_rating.moody, nan
s_p_s_rating ifrs9.external_ratings.sp ifrs9.current_external_rating.sp, nan
fitch_s_rating ifrs9.external_ratings.fitch ifrs9.current_external_rating.fitch, nan
previous_effective_stage_reason ifrs9.previous_stage.previous_stage_2_reason, ifrs9.previous_stage_details.previous_stage_2_reason
previous_effective_stage_date ifrs9.previous_stage.previous_stage_2_date, ifrs9.previous_stage_details.previous_stage_2_date
previous_effective_stage ifrs9.previous_stage.effective_stage, ifrs9.previous_stage.previous_stage_2_, ifrs9.previous_stage_details.effective_stage
effective_stage derived from stage, max_stage, previous_stage_2_, ifrs9.committed_unutilized_v.LCPF_LOAD_COUNTRY, stage, staging_reason, ifrs9.previous_stage_details.previous_stage_2_date, ifrs9.previous_stage.previous_stage_2_, max_stage, ifrs9.ifrs9_monthly_run_data.effective_stage, ifrs9.previous_stage_details.previous_stage_2_, ifrs9.previous_stage_details.previous_stage_2_date, ifrs9.committed_unutilized_v.LCPF_LOAD_COUNTRY, staging_reason, stage, MAX_STAGE.new_MAX_STAGE, ifrs9.previous_stage.previous_stage_2_ ifrs9.deals_funded_unfunded_report.staging_reason ifrs9.deals_funded_unfunded_report.stage, nan
effective_staging_date derived from partition_load_date, previous_stage_2_date, ifrs9.committed_unutilized_v.LCPF_LOAD_COUNTRY, stage, staging_reason, ifrs9.previous_stage_details.previous_stage_2_date, ifrs9.previous_stage.previous_stage_2_, max_stage, ifrs9.previous_stage.previous_stage_2_ ifrs9.previous_stage.previous_stage_2_date ifrs9.deals_funded_unfunded_report.stage ifrs9.deals_funded_unfunded_report.staging_reason ifrs9.deals_funded_unfunded_report.load_country ifrs9.deals_funded_unfunded_report.max_stage ifrs9.deals_funded_unfunded_report.load_date, ifrs9.previous_stage_details.previous_stage_2_date, ifrs9.committed_unutilized_v.LCPF_LOAD_COUNTRY, ifrs9.ifrs9_monthly_run_data.partition_load_date, nan
effective_staging_reason derived from stage, staging_reason, previous_stage_2_, ifrs9.committed_unutilized_v.LCPF_LOAD_COUNTRY, stage, staging_reason, ifrs9.previous_stage_details.previous_stage_2_date, ifrs9.previous_stage.previous_stage_2_, max_stage, ifrs9.previous_stage.previous_stage_2_ ifrs9.previous_stage.previous_stage_2_reason ifrs9.previous_stage.previous_stage_2_date ifrs9.deals_funded_unfunded_report.staging_reason ifrs9.deals_funded_unfunded_report.stage, ifrs9.previous_stage_details.previous_stage_2_reason, staging_reason, stage, MAX_STAGE.new_MAX_STAGE_REASON, nan
funded_unfunded_flag 'Unfunded' (literal), ifrs9.deals_funded_unfunded_report.type, literal:'Unfunded', nan
ttc_pd nan
ecl_1y nan
ecl_maturity nan
lossallowance nan
cash_collaterals '0' (literal), ifrs9.collaterals_final.cash_collateral, literal:'0', nan
other_collaterals '0' (literal), ifrs9.collaterals_final.other_collateral, literal:'0', nan
ead nan
bank ifrs9.banks_v.bankdesc ifrs9.deals_funded_unfunded_report.bank_new, nan, raw_data_vault.branches.capf_brnch_number
load_date ifrs9.committed_unutilized_v.LOAD_DATE, ifrs9.committed_unutilized_v.load_date, ifrs9.deals_funded_unfunded_report.load_date
load_country ifrs9.committed_unutilized_v.LCPF_LOAD_COUNTRY, ifrs9.committed_unutilized_v.lcpf_load_country, ifrs9.deals_funded_unfunded_report.load_country