/*%include "/opt/sas/spre/home/SASFoundation/ucmacros/amlcoresrv/x_coresrv_aml_preprocess.sas" ;*/ /*============================================================================== | SAS SAMPLE FILE | | NAME: x_fcf_transactions_prep.sas | PRODUCT: SAS Compliance Solutions | SYSTEM: UNIX/WINDOWS ================================================================================ | USAGE: | x_fcf_transactions_prep(run_asofdttm=,cash_fact=N, event_fact=N); | | Parameters: | | last_asofdttm = start timestamp for records to process | | runasofdttm = end timestamp for records to process | | cash_fact = [Y|N] Process cash fact table | | event_fact = [Y|N] Process event fact table +------------------------------------------------------------------------------+ PREP FILE DESCRIPTION --------------------- Creates party/account/associate transactions, household_transactions and insurance_transactions prep */ /*------------------------------------------------------------------- * Copyright (c) 2019-2021 by SAS Institute Inc., Cary, NC 27513 USA * ---All Rights Reserved. *-------------------------------------------------------------------*/ %macro x_fcf_transactions_prep(run_asofdate=, cash_fact=N, event_fact=N); /*==========================================================================* * Step : Per-Process *==========================================================================*/ /*------------------ * Set Module *------------------*/ options varlenchk=nowarn; %local _fcfmodule_; %let _fcfmodule_=X_FCF_TRANSACTIONS_PREP; /* %let run_asofdate=20230704; */ /*------------------ * Reset params *------------------*/ %let sysrc = 0; %let job_rc = 0; %let trans_rc = 0; %let sqlrc = 0; %let syscc = 0; %let sequence_no = 5; %let etls_startTime = %sysfunc(datetime(),datetime.); %let run_asofdate_str = %str(%')&run_asofdate.%str(%'); /*------------------- * Assign jobName *-------------------*/ %let etls_jobName = %nrquote(JOB_AGP_X_FCF_TRANSACTION_PREP) ; %let step_name = Run x_transaction_prep process ; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name [ &step_timestamp ] ; /*==========================================================================* * Step : Check skip job *==========================================================================*/ proc sql noprint; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); select param_value into :skip_flag from connection to sqlsvr ( select param_value from core.ctrl_parameter where param_name = 'AGP_TRANSACTION_PREP_SKIP' ); disconnect from sqlsvr; quit; %put &=skip_flag; %if &skip_flag. eq Y %then %do; %put NOTE: skip_flag is 'Y', the system will skip this step; %goto MACRO_SKIP; %end; proc sql noprint; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); select daily_flag into :load_daily_flg from connection to sqlsvr ( select daily_flag from core.ctrl_load_control where load_date = &run_asofdate_str. ); disconnect from sqlsvr; quit; %put &=load_daily_flg; %if &load_daily_flg. ne Y %then %do; %put NOTE: Daily_flag is 'N', the system will skip this step; %goto MACRO_SKIP; %end; /*==========================================================================* * Step : Delete the existing data that is equal to run_asofdate (Rerun) *==========================================================================*/ %let step_name = Delete existing data from amlprep.x_transactions ; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name [ &step_timestamp ] ; proc sql buffersize = 100000 threads; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute( delete from amlprep.x_transactions where cast(transaction_dttm as date) = &run_asofdate_str. ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); %if (&trans_rc eq 3 or &trans_rc ge 5) or (&sqlrc eq 3 or &sqlrc ge 5) %then %do; %let _fcfmsg_=ERROR:FCF:Unable to delete data from x_transaction_prep; %fcf_put_error; %goto MACRO_END; %end; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name [ &step_timestamp ] ; %if &cash_fact. = Y %then %do; /*==========================================================================* * Step : Create temp table for amlwatchlist *==========================================================================*/ %let step_name = create table amlprep.x_temp_wl_identifier ; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name [ &step_timestamp ] ; proc sql buffersize = 100000 threads; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute ( drop table if exists amlprep.x_temp_wl_identifier ) by sqlsvr; execute( select distinct identifier_number, identifier_type, case when sum(WL_TMP.watchlist_ind) = 1 then 'Y' else 'N' end watchlist_ind, case when sum(WL_TMP.greylist_ind) = 1 then 'Y' else 'N' end greylist_ind, case when sum(WL_TMP.risklist_ind) = 1 then 'Y' else 'N' end risklist_ind into amlprep.x_temp_wl_identifier from ( select identifier_number , identifier_type, case when watch_list_name = 'WTCH' and identifier_number is not null then 1 else 0 end as watchlist_ind, case when watch_list_name = 'GREY' and identifier_number is not null then 1 else 0 end as greylist_ind, case when watch_list_name = 'RISK' and identifier_number is not null then 1 else 0 end as risklist_ind from amlwatchlist.fsc_wl_entity WL_ENT inner join amlwatchlist.fsc_wl_identifier WL_ID on WL_ENT.entity_watch_list_key = WL_ID.entity_watch_list_key and WL_ENT.current_ind = 'Y' and WL_ENT.x_delete_flg = 'N' and WL_ID.current_ind = 'Y' and WL_ID.identifier_number is not null ) WL_TMP group by identifier_number , identifier_type ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); %if (&trans_rc eq 3 or &trans_rc ge 5) or (&sqlrc eq 3 or &sqlrc ge 5) %then %do; %let _fcfmsg_=ERROR:FCF:Cannot create table x_entity_watch_list_tmp; %fcf_put_error; %goto MACRO_END; %end; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name [ &step_timestamp ] ; /*==========================================================================* * Step : Create temp table for Remitter *==========================================================================*/ %let step_name = create table x_temp_remitter ; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name. [ &step_timestamp. ] ; proc sql buffersize = 100000 threads; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute ( drop table if exists amlprep.x_temp_remitter ) by sqlsvr; execute( select RB.bank_name, RCL.x_code AS cntry_wl_ind, RBL.x_code AS bank_wl_ind, BB.transaction_key into amlprep.x_temp_remitter from core.fsc_cash_flow_fact CFF inner join core.fsc_cash_flow_bank_bridge BB ON CFF.transaction_key = BB.transaction_key inner join core.fsc_bank_dim RB ON BB.bank_key = RB.bank_key left join amlwatchlist.x_fsc_wl_location RBL ON RBL.watch_list_name = 'HRFI' and RB.bank_number = RBL.x_code and RBL.change_current_ind = 'Y' and RBL.delete_flg = 'N' and RBL.x_code is not null left join amlwatchlist.x_fsc_wl_location RCL on RB.bank_country_code = RCL.x_code and RCL.watch_list_name = 'HRCT' and RCL.change_current_ind = 'Y' and RCL.delete_flg = 'N' and RCL.x_code is not null where BB.role_desc = 'REMITTER' and cast(CFF.transaction_dttm as date) = &run_asofdate_str. ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); %if (&trans_rc eq 3 or &trans_rc ge 5) or (&sqlrc eq 3 or &sqlrc ge 5) %then %do; %let _fcfmsg_=ERROR:FCF:Cannot create table x_temp_remitter; %fcf_put_error; %goto MACRO_END; %end; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name [ &step_timestamp ] ; /*==========================================================================* * Step : Create temp table for Beneficiary *==========================================================================*/ %let step_name = create table x_temp_beneficiary ; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name. [ &step_timestamp. ] ; proc sql buffersize = 100000 threads; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute ( drop table if exists amlprep.x_temp_beneficiary ) by sqlsvr; execute( select RB.bank_name, RCL.x_code AS cntry_wl_ind, RBL.x_code AS bank_wl_ind, BB.transaction_key into amlprep.x_temp_beneficiary from core.fsc_cash_flow_fact CFF inner join core.fsc_cash_flow_bank_bridge BB ON CFF.transaction_key = BB.transaction_key inner join core.fsc_bank_dim RB ON BB.bank_key = RB.bank_key left join amlwatchlist.x_fsc_wl_location RBL ON RBL.watch_list_name = 'HRFI' and RB.bank_number = RBL.x_code and RBL.change_current_ind = 'Y' and RBL.delete_flg = 'N' and RBL.x_code is not null left join amlwatchlist.x_fsc_wl_location RCL on RB.bank_country_code = RCL.x_code and RCL.watch_list_name = 'HRCT' and RCL.change_current_ind = 'Y' and RCL.delete_flg = 'N' and RCL.x_code is not null where BB.role_desc = 'BENEFICIARY' and cast(CFF.transaction_dttm as date) = &run_asofdate_str. ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); %if (&trans_rc eq 3 or &trans_rc ge 5) or (&sqlrc eq 3 or &sqlrc ge 5) %then %do; %let _fcfmsg_=ERROR:FCF:Cannot create table x_temp_remitter; %fcf_put_error; %goto MACRO_END; %end; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name [ &step_timestamp ] ; /*==========================================================================* /* Step : Create Sequency of temp CFF *==========================================================================*/ %let step_name = create table x_temp_cash_flow_fact_seq; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name [ &step_timestamp ] ; /* --------------------------------- */ proc sql buffersize = 100000 threads; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute ( drop table if exists amlprep.x_temp_cash_flow_fact_seq) by sqlsvr; execute ( select transaction_key ,row_number() over (order by transaction_key) % &sequence_no. + 1 as rownum into amlprep.x_temp_cash_flow_fact_seq from core.fsc_cash_flow_fact where cast(transaction_dttm as date) = &run_asofdate_str. ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); /* --------------------------------- */ %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name [ &step_timestamp ] ; /*==========================================================================* * Step : Create temp table for Cash flow fact *==========================================================================*/ %do sequence_id = 1 %to &sequence_no.; %let step_name = create table x_temp_cash_flow_fact_&sequence_id. ; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name. [ &step_timestamp. ] ; proc sql buffersize = 100000 threads; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute ( drop table if exists amlprep.x_temp_cash_flow_fact_&sequence_id. ) by sqlsvr; execute( select distinct ACCT_D.account_number, cast(ACCT_D.account_type_desc as varchar(20)) as account_type_desc, cast(ACCT_D.product_category_name as varchar(35)) as product_category_name, cast(ACCT_D.currency_based_account_ind as varchar(1)) as currency_based_account_ind, cast(ACCT_D.account_tax_state_code as varchar(3)) as account_tax_state_code, PTY_D.party_number, PTY_D.annual_income_amount, cast(PTY_D.politically_exposed_person_ind as varchar(1)) as politically_exposed_person_ind , cast(PTY_D.customer_since_date as date) as customer_since_date, KCEF.override_score_class_code as risk_classification, KCEF.last_susp_actv_rpt_date, KCEF.last_cash_trans_rpt_date, CF_FCT.currency_amount, CF_FCT.transaction_dttm, cast(CF_FCT.posted_dttm as date) as posted_date, TRN_TYP_D.transaction_cdi_code, TRN_TYP_D.primary_medium_desc, TRN_TYP_D.secondary_medium_desc, COALESCE(TRN_TYP_D.tertiary_medium_desc,'NULL') as tertiary_medium_desc, TRN_TYP_D.mechanism_desc, TRN_TYP_D.trancode, TRN_D.transaction_reference_number, TRN_D.transaction_description, cast(BRCH_D.street_state_code as varchar(3)) as branch_street_state_code, BRCH_D.branch_number, BRCH_D.branch_name, cast(BRCH_D.branch_type_desc as varchar(10)) as branch_type_desc, ASC_D.associate_number, ASC_D.associate_name, EXT_BEN_D.external_party_number as ext_beneficiary_party_number, EXT_BEN_D.full_name as ext_beneficiary_party_name, cast(EXT_BEN_D.ext_party_account_comp_key as varchar(150)) as ext_beneficiary_comp_key, EXT_REM_D.external_party_number as ext_remitter_party_number, EXT_REM_D.full_name as ext_remitter_party_name, cast(EXT_REM_D.ext_party_account_comp_key as varchar(150)) as ext_remitter_comp_key, cast(EXT_EXE_D.ext_party_account_comp_key as varchar(150)) as ext_executing_comp_key, cast(CF_FCT.country_code as varchar(2)) as txn_country_code, cast(CF_FCT.transaction_currency_code as varchar(3)) as txn_currency_code, TRN_ST_D.status_desc, TRN_ST_D.status_reason_desc, CF_FCT.transaction_key, CF_FCT.transaction_type_key, CF_FCT.remitter_ext_party_key, CF_FCT.beneficiary_ext_party_key, CF_FCT.executing_party_key, CF_FCT.executing_ext_party_key, PTY_D.party_key, ACCT_D.account_key, ACCT_D.account_name as x_account_name, ACCT_D.account_primary_branch_name, SEC_ACCT_D.account_number as x_secondary_account_number, SEC_ACCT_D.account_name as x_secondary_account_name, PTY_D.party_identification_id as x_party_identification_id, PTY_D.party_identification_type_desc as x_party_identification_type_desc, BRCH_D.street_postal_code as x_street_postal_code, datediff(day,PTY_D.customer_since_date,&run_asofdate_str.) as x_account_age_days, BRCH_D.street_state_name as x_txn_branch_province, ACCT_D.x_have_atm_ind, ACCT_D.account_open_date as x_account_open_date, PTY_D.x_occupation_code, PTY_D.x_occupation_desc, ACCT_D.x_account_purpose_desc, ACCT_D.x_apply_ibank_date, ACCT_D.x_balance_amount, ACCT_D.account_close_date as x_account_close_date, BRCH_D.x_region_name as x_region_name, &run_asofdate. as x_ptn_yyyymmdd, PTY_D.X_PARTY_TYPE_CODE as x_party_type_code, CF_FCT.X_MG_REM_COUNTRY_CODE as x_mg_rem_country_code, case when (substring(ACCT_D.account_type_desc,1,5)) = '' or (substring(ACCT_D.account_type_desc,1,5)) is null or (substring(ACCT_D.account_type_desc,1,5)) = 'UNKNO' then 'UNKNOWN' else (substring(ACCT_D.account_type_desc,1,5)) end as c_acc_segment_cd /* substring(ACCT_D.account_type_desc,1,5) as c_acc_segment_cd */ into amlprep.x_temp_cash_flow_fact_&sequence_id. from core.fsc_cash_flow_fact CF_FCT inner join core.fsc_account_dim ACCT_D on CF_FCT.account_key = ACCT_D.account_key and coalesce(ACCT_D.x_delete_flg,'N') = 'N' inner join core.fsc_party_account_bridge PTY_ACC_BR on ACCT_D.account_number = PTY_ACC_BR.account_number and PTY_ACC_BR.change_current_ind='Y' and coalesce(PTY_ACC_BR.x_delete_flg,'N') = 'N' and PTY_ACC_BR.role_key = 1 inner join core.fsc_party_dim PTY_D on PTY_ACC_BR.party_number = PTY_D.party_number and PTY_D.change_current_ind = 'Y' and coalesce(PTY_D.x_delete_flg,'N') = 'N' inner join core.fsc_transaction_dim TRN_D on CF_FCT.transaction_key = TRN_D.transaction_key inner join core.fsc_transaction_type_dim TRN_TYP_D on CF_FCT.transaction_type_key = TRN_TYP_D.transaction_type_key inner join core.fsc_transaction_status_dim TRN_ST_D on CF_FCT.transaction_status_key = TRN_ST_D.transaction_status_key inner join core.fsc_branch_dim BRCH_D on CF_FCT.branch_key = BRCH_D.branch_key and BRCH_D.change_current_ind = 'Y' inner join core.fsc_associate_dim ASC_D on CF_FCT.associate_key = ASC_D.associate_key and ASC_D.change_current_ind = 'Y' inner join core.fsc_ext_party_account_dim EXT_BEN_D on CF_FCT.beneficiary_ext_party_key=EXT_BEN_D.ext_party_account_key inner join core.fsc_ext_party_account_dim EXT_REM_D on CF_FCT.remitter_ext_party_key=EXT_REM_D.ext_party_account_key inner join core.fsc_ext_party_account_dim EXT_EXE_D on CF_FCT.executing_ext_party_key=EXT_EXE_D.ext_party_account_key /* New ootb table for AML83*/ inner join kc.entity_fact KCEF on PTY_ACC_BR.party_number = KCEF.primary_entity_number and KCEF.change_current_ind = 'Y' /* Custom table for SCB only*/ inner join core.fsc_account_dim SEC_ACCT_D on CF_FCT.secondary_account_key = SEC_ACCT_D.account_key and coalesce(SEC_ACCT_D.x_delete_flg,'N') = 'N' inner join amlprep.x_temp_cash_flow_fact_seq as CFF_SEQ on CF_FCT.transaction_key = CFF_SEQ.transaction_key and CFF_SEQ.rownum = &sequence_id. /* where CF_FCT.transaction_key in ( select transaction_key from amlprep.x_temp_cash_flow_fact_seq where rownum = &sequence_id.) */ ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); %if (&trans_rc eq 3 or &trans_rc ge 5) or (&sqlrc eq 3 or &sqlrc ge 5) %then %do; %let _fcfmsg_=ERROR:FCF:Cannot create table amlprep.x_temp_cash_flow_fact_&sequence_id.; %fcf_put_error; %goto MACRO_END; %end; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name [ &step_timestamp ] ; %end; /*==========================================================================* * Step : insert transaction data (FSC_CASH_FLOW_FACT) into Transaction prep *==========================================================================*/ %do sequence_id = 1 %to &sequence_no.; %let step_name = Insert data CASH_FLOW_FACT (&sequence_id.) into table amlprep.x_transactions ; %let step_StartTime = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name. [ &step_timestamp. ] ; proc sql buffersize = 100000 threads; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute ( insert into amlprep.x_transactions (account_number ,account_type_desc ,product_category_name ,currency_based_account_ind ,account_tax_state_code ,party_number ,annual_income_amount ,politically_exposed_person_ind ,customer_since_date ,risk_classification ,last_susp_actv_rpt_date ,last_cash_trans_rpt_date ,currency_amount ,transaction_dttm ,posted_date ,transaction_cdi_code ,primary_medium_desc ,secondary_medium_desc ,tertiary_medium_desc ,mechanism_desc ,trancode ,transaction_reference_number ,transaction_description ,branch_street_state_code ,branch_number ,branch_name ,branch_type_desc ,associate_number ,associate_name ,ext_beneficiary_party_number ,ext_beneficiary_party_name ,ext_beneficiary_comp_key ,ext_remitter_party_number ,ext_remitter_party_name ,ext_remitter_comp_key ,ext_executing_comp_key ,txn_country_code ,txn_currency_code ,status_desc ,status_reason_desc ,expected_incoming_amount ,expected_outgoing_amount ,expected_incoming_count ,expected_outgoing_count ,increased_behavior_credit_amt ,increased_behavior_debit_amt ,increased_behavior_credit_cnt ,increased_behavior_debit_cnt ,expected_credit_ceiling_amount ,expected_debit_ceiling_amount ,transaction_key ,transaction_type_key ,remitter_ext_party_key ,beneficiary_ext_party_key ,executing_party_key ,executing_ext_party_key ,party_key ,account_key ,segment_cd ,x_account_name ,x_account_primary_branch_name ,x_secondary_account_number ,x_secondary_account_name ,x_party_identification_id ,x_party_identification_type_desc ,x_street_postal_code ,x_org_bank_name ,x_des_bank_name ,x_account_age_days ,x_txn_branch_province ,x_acc_branch_province ,x_have_atm_ind ,x_account_open_date ,x_occupation_code ,x_occupation_desc ,x_account_purpose_desc ,x_apply_ibank_date ,x_balance_amount ,x_watchlist_ind ,x_greylist_ind ,x_risklist_ind ,x_hrct_ind ,x_hrfi_ind ,x_hrpc_ind ,x_account_close_date ,x_region_name ,x_ptn_yyyymmdd /* Added column for MG*/ ,x_party_type_code ,x_mg_rem_country_code ,c_segment_cd ,c_acc_segment_cd ) select distinct innr.account_number, innr.account_type_desc, innr.product_category_name, innr.currency_based_account_ind, innr.account_tax_state_code, innr.party_number, innr.annual_income_amount, innr.politically_exposed_person_ind , innr.customer_since_date, innr.risk_classification, innr.last_susp_actv_rpt_date, innr.last_cash_trans_rpt_date, innr.currency_amount, innr.transaction_dttm, innr.posted_date, innr.transaction_cdi_code, innr.primary_medium_desc, innr.secondary_medium_desc, innr.tertiary_medium_desc, innr.mechanism_desc, innr.trancode, innr.transaction_reference_number, innr.transaction_description, innr.branch_street_state_code, innr.branch_number, innr.branch_name, innr.branch_type_desc, innr.associate_number, innr.associate_name, innr.ext_beneficiary_party_number, innr.ext_beneficiary_party_name, innr.ext_beneficiary_comp_key, innr.ext_remitter_party_number, innr.ext_remitter_party_name, innr.ext_remitter_comp_key, innr.ext_executing_comp_key, innr.txn_country_code, innr.txn_currency_code, innr.status_desc, innr.status_reason_desc, ACCT_ANA.expected_incoming_amount, ACCT_ANA.expected_outgoing_amount, ACCT_ANA.expected_incoming_count, ACCT_ANA.expected_outgoing_count, ACCT_ANA.increased_behavior_credit_amt , ACCT_ANA.increased_behavior_debit_amt, ACCT_ANA.increased_behavior_credit_cnt, ACCT_ANA.increased_behavior_debit_cnt , ACCT_ANA.expected_credit_ceiling_amount, ACCT_ANA.expected_debit_ceiling_amount, innr.transaction_key, innr.transaction_type_key, innr.remitter_ext_party_key, innr.beneficiary_ext_party_key, innr.executing_party_key, innr.executing_ext_party_key, innr.party_key, innr.account_key, PTY_SEG.segment_cd, /* Custom add column for SCB only*/ innr.x_account_name, innr.account_primary_branch_name, innr.x_secondary_account_number, innr.x_secondary_account_name, innr.x_party_identification_id, innr.x_party_identification_type_desc, innr.x_street_postal_code, REM.bank_name as x_org_bank_name, BEN.bank_name as x_des_bank_name, innr.x_account_age_days, innr.x_txn_branch_province, COALESCE(ACCT_BRCH_D.street_state_name,'') as x_acc_branch_province, innr.x_have_atm_ind, innr.x_account_open_date, innr.x_occupation_code, innr.x_occupation_desc, innr.x_account_purpose_desc, innr.x_apply_ibank_date, innr.x_balance_amount, coalesce(WL_ENTY.watchlist_ind,'N') as x_watchlist_ind, coalesce(WL_ENTY.greylist_ind,'N') as x_greylist_ind, coalesce(WL_ENTY.risklist_ind,'N') as x_risklist_ind, case when REM.cntry_wl_ind is not null or BEN.cntry_wl_ind is not null then 'Y' else 'N' end as x_hrct_ind, case when REM.bank_wl_ind is not null or BEN.bank_wl_ind is not null then 'Y' else 'N' end as x_hrfi_ind, case when WL_LOCA.x_code is not null then 'Y' else 'N' end as x_hrpc_ind, innr.x_account_close_date, innr.x_region_name, innr.x_ptn_yyyymmdd, /* Added column for MG*/ innr.x_party_type_code, innr.x_mg_rem_country_code, case when (PTY_SEG.segment_cd is null or PTY_SEG.segment_cd = '') and innr.X_PARTY_TYPE_CODE is not null then concat(innr.X_PARTY_TYPE_CODE,'-UNDEFINE') when (PTY_SEG.segment_cd is null or PTY_SEG.segment_cd = '') and innr.X_PARTY_TYPE_CODE is null then 'UNKNOWN' else PTY_SEG.segment_cd end as c_segment_cd, innr.c_acc_segment_cd from amlprep.x_temp_cash_flow_fact_&sequence_id. innr /* New ootb table for AML83*/ left join amlkc.fsk_account_analysis_dim ACCT_ANA on innr.account_number = ACCT_ANA.account_number left join kc.party_segment PTY_SEG on innr.party_number = PTY_SEG.party_number and PTY_SEG.change_current_ind = 'Y' /* Custom table for SCB only*/ left join core.fsc_branch_dim ACCT_BRCH_D on innr.account_primary_branch_name = ACCT_BRCH_D.branch_number and ACCT_BRCH_D.change_current_ind = 'Y' left join amlwatchlist.x_fsc_wl_location WL_LOCA on WL_LOCA.watch_list_name = 'HRPT' and WL_LOCA.change_current_ind = 'Y' and WL_LOCA.delete_flg = 'N' and WL_LOCA.x_code is not null and innr.x_street_postal_code = WL_LOCA.x_code left join amlprep.x_temp_wl_identifier WL_ENTY on innr.x_street_postal_code = WL_ENTY.identifier_number and innr.x_party_identification_type_desc = WL_ENTY.identifier_type left join amlprep.x_temp_beneficiary BEN on innr.transaction_key = BEN.transaction_key left join amlprep.x_temp_remitter REM on innr.transaction_key = REM.transaction_key ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); %if (&trans_rc eq 3 or &trans_rc ge 5) or (&sqlrc eq 3 or &sqlrc ge 5) %then %do; %let _fcfmsg_=ERROR:FCF:Unable to insert data to amlprep.x_transactions from fsc_cash_flow_fact (&sequence_id.); %fcf_put_error; %goto MACRO_END; %end; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name. [ &step_timestamp. ] ; %end; %end; %if &event_fact. = Y %then %do; /*==========================================================================* /* Step : Create Sequency of temp AEF *==========================================================================*/ %let step_name = create table x_temp_account_event_fact_seq; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name [ &step_timestamp ] ; /* --------------------------------- */ proc sql buffersize = 100000 threads; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute ( drop table if exists amlprep.x_temp_account_event_fact_seq) by sqlsvr; execute ( select transaction_key ,row_number() over (order by transaction_key) % &sequence_no. + 1 as rownum into amlprep.x_temp_account_event_fact_seq from core.fsc_account_event_fact where cast(transaction_dttm as date) = &run_asofdate_str. ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); /* --------------------------------- */ %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name [ &step_timestamp ] ; /*==========================================================================* * Step : Create temp table for Account event fact *==========================================================================*/ %do sequence_id = 1 %to &sequence_no.; %let step_name = create table x_temp_account_event_fact_&sequence_id. ; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name. [ &step_timestamp. ] ; proc sql buffersize = 100000 threads; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute ( drop table if exists amlprep.x_temp_account_event_fact_&sequence_id. ) by sqlsvr; execute( select distinct ACCT_D.account_number, cast(ACCT_D.account_type_desc as varchar(20)) as account_type_desc, cast(ACCT_D.product_category_name as varchar(35)) as product_category_name, cast(ACCT_D.currency_based_account_ind as varchar(1)) as currency_based_account_ind, cast(ACCT_D.account_tax_state_code as varchar(3)) as account_tax_state_code, PTY_D.party_number, PTY_D.annual_income_amount, cast(PTY_D.politically_exposed_person_ind as varchar(1)) as politically_exposed_person_ind , cast(PTY_D.customer_since_date as date) as customer_since_date, KCEF.override_score_class_code as risk_classification, KCEF.last_susp_actv_rpt_date, KCEF.last_cash_trans_rpt_date, 0 as currency_amount, EV_FCT.transaction_dttm, cast(EV_FCT.transaction_dttm as date) as posted_date, TRN_TYP_D.transaction_cdi_code, TRN_TYP_D.primary_medium_desc, TRN_TYP_D.secondary_medium_desc, TRN_TYP_D.tertiary_medium_desc, TRN_TYP_D.mechanism_desc, TRN_TYP_D.trancode, TRN_D.transaction_reference_number, TRN_D.transaction_description, cast(BRCH_D.street_state_code as varchar(3)) as branch_street_state_code, BRCH_D.branch_number, BRCH_D.branch_name, cast(BRCH_D.branch_type_desc as varchar(10)) as branch_type_desc, '' as associate_number, '' as associate_name, '' as ext_beneficiary_party_number, '' as ext_beneficiary_party_name, '' as ext_beneficiary_comp_key, '' as ext_remitter_party_number, '' as ext_remitter_party_name, '' as ext_remitter_comp_key, '' as ext_executing_comp_key, '' as txn_country_code, '' as txn_currency_code, TRN_ST_D.status_desc, TRN_ST_D.status_reason_desc, EV_FCT.transaction_key, EV_FCT.transaction_type_key, -1 as remitter_ext_party_key, -1 as beneficiary_ext_party_key, -1 as executing_party_key, -1 as executing_ext_party_key, PTY_D.party_key, ACCT_D.account_key, /* Custom add column for SCB only*/ ACCT_D.account_name as x_account_name, ACCT_D.account_primary_branch_name, '' as x_secondary_account_number, '' as x_secondary_account_name, PTY_D.party_identification_id as x_party_identification_id, PTY_D.party_identification_type_desc as x_party_identification_type_desc, BRCH_D.street_postal_code as x_street_postal_code, '' as x_org_bank_name, '' as x_des_bank_name, datediff(day,PTY_D.customer_since_date,&run_asofdate_str.) as x_account_age_days, BRCH_D.street_state_name as x_txn_branch_province, '' as x_acc_branch_province, ACCT_D.x_have_atm_ind, cast(ACCT_D.account_open_date as date) as x_account_open_date, PTY_D.x_occupation_code, PTY_D.x_occupation_desc, ACCT_D.x_account_purpose_desc, cast(ACCT_D.x_apply_ibank_date as date) as x_apply_ibank_date, ACCT_D.x_balance_amount, 'N' as x_hrct_ind, 'N' as x_hrfi_ind, 'N' AS x_hrpc_ind, cast(ACCT_D.account_close_date as date) as x_account_close_date, BRCH_D.x_region_name as x_region_name, &run_asofdate. as x_ptn_yyyymmdd, PTY_D.X_PARTY_TYPE_CODE as x_party_type_code, '' as x_mg_rem_country_code, case when (substring(ACCT_D.account_type_desc,1,5)) = '' or (substring(ACCT_D.account_type_desc,1,5)) is null or (substring(ACCT_D.account_type_desc,1,5)) = 'UNKNO' then 'UNKNOWN' else (substring(ACCT_D.account_type_desc,1,5)) end as c_acc_segment_cd into amlprep.x_temp_account_event_fact_&sequence_id. from core.fsc_account_event_fact EV_FCT inner join core.fsc_account_dim ACCT_D on EV_FCT.account_key = ACCT_D.account_key and coalesce(ACCT_D.x_delete_flg,'N') = 'N' inner join core.fsc_party_account_bridge PTY_ACC_BR on ACCT_D.account_number = PTY_ACC_BR.account_number and PTY_ACC_BR.change_current_ind='Y' and coalesce(PTY_ACC_BR.x_delete_flg,'N') = 'N' and PTY_ACC_BR.role_key = 1 inner join core.fsc_party_dim PTY_D on PTY_ACC_BR.party_number = PTY_D.party_number and PTY_D.change_current_ind = 'Y' and coalesce(PTY_D.x_delete_flg,'N') = 'N' inner join core.fsc_transaction_dim TRN_D on EV_FCT.transaction_key = TRN_D.transaction_key inner join core.fsc_transaction_type_dim TRN_TYP_D on EV_FCT.transaction_type_key = TRN_TYP_D.transaction_type_key inner join core.fsc_transaction_status_dim TRN_ST_D on EV_FCT.transaction_status_key = TRN_ST_D.transaction_status_key inner join core.fsc_branch_dim BRCH_D on EV_FCT.branch_key = BRCH_D.branch_key and BRCH_D.change_current_ind='Y' /* New ootb table for AML83*/ inner join kc.entity_fact KCEF on PTY_ACC_BR.party_number = KCEF.primary_entity_number and KCEF.change_current_ind = 'Y' inner join amlprep.x_temp_account_event_fact_seq as AEF_SEQ on EV_FCT.transaction_key = AEF_SEQ.transaction_key and AEF_SEQ.rownum = &sequence_id. /* where EV_FCT.transaction_key in (select transaction_key */ /* from amlprep.x_temp_account_event_fact_seq */ /* where rownum = &sequence_id.) */ ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); %if (&trans_rc eq 3 or &trans_rc ge 5) or (&sqlrc eq 3 or &sqlrc ge 5) %then %do; %let _fcfmsg_=ERROR:FCF:Cannot create table x_temp_account_event_fact; %fcf_put_error; %goto MACRO_END; %end; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name [ &step_timestamp ] ; %end; /*==========================================================================* * Step : insert transaction data (FSC_ACCOUNT_EVENT_FACT) into Transaction prep *==========================================================================*/ %do sequence_id = 1 %to &sequence_no.; %let step_name = Insert data ACCOUNT_EVENT_FACT (&sequence_id.) into table amlprep.x_transactions ; %let step_StartTime = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name. [ &step_timestamp. ] ; proc sql; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute ( insert into amlprep.x_transactions (account_number ,account_type_desc ,product_category_name ,currency_based_account_ind ,account_tax_state_code ,party_number ,annual_income_amount ,politically_exposed_person_ind ,customer_since_date ,risk_classification ,last_susp_actv_rpt_date ,last_cash_trans_rpt_date ,currency_amount ,transaction_dttm ,posted_date ,transaction_cdi_code ,primary_medium_desc ,secondary_medium_desc ,tertiary_medium_desc ,mechanism_desc ,trancode ,transaction_reference_number ,transaction_description ,branch_street_state_code ,branch_number ,branch_name ,branch_type_desc ,associate_number ,associate_name ,ext_beneficiary_party_number ,ext_beneficiary_party_name ,ext_beneficiary_comp_key ,ext_remitter_party_number ,ext_remitter_party_name ,ext_remitter_comp_key ,ext_executing_comp_key ,txn_country_code ,txn_currency_code ,status_desc ,status_reason_desc ,expected_incoming_amount ,expected_outgoing_amount ,expected_incoming_count ,expected_outgoing_count ,increased_behavior_credit_amt ,increased_behavior_debit_amt ,increased_behavior_credit_cnt ,increased_behavior_debit_cnt ,expected_credit_ceiling_amount ,expected_debit_ceiling_amount ,transaction_key ,transaction_type_key ,remitter_ext_party_key ,beneficiary_ext_party_key ,executing_party_key ,executing_ext_party_key ,party_key ,account_key ,segment_cd ,x_account_name ,x_account_primary_branch_name ,x_secondary_account_number ,x_secondary_account_name ,x_party_identification_id ,x_party_identification_type_desc ,x_street_postal_code ,x_org_bank_name ,x_des_bank_name ,x_account_age_days ,x_txn_branch_province ,x_acc_branch_province ,x_have_atm_ind ,x_account_open_date ,x_occupation_code ,x_occupation_desc ,x_account_purpose_desc ,x_apply_ibank_date ,x_balance_amount ,x_watchlist_ind ,x_greylist_ind ,x_risklist_ind ,x_hrct_ind ,x_hrfi_ind ,x_hrpc_ind ,x_account_close_date ,x_region_name ,x_ptn_yyyymmdd /* Added column for MG*/ ,x_party_type_code ,x_mg_rem_country_code ,c_segment_cd ,c_acc_segment_cd ) select distinct innr.account_number, innr.account_type_desc, innr.product_category_name, innr.currency_based_account_ind, innr.account_tax_state_code, innr.party_number, innr.annual_income_amount, innr.politically_exposed_person_ind , innr.customer_since_date, innr.risk_classification, innr.last_susp_actv_rpt_date, innr.last_cash_trans_rpt_date, innr.currency_amount, innr.transaction_dttm, innr.posted_date, innr.transaction_cdi_code, innr.primary_medium_desc, innr.secondary_medium_desc, innr.tertiary_medium_desc, innr.mechanism_desc, innr.trancode, innr.transaction_reference_number, innr.transaction_description, innr.branch_street_state_code, innr.branch_number, innr.branch_name, innr.branch_type_desc, innr.associate_number, innr.associate_name, innr.ext_beneficiary_party_number, innr.ext_beneficiary_party_name, innr.ext_beneficiary_comp_key, innr.ext_remitter_party_number, innr.ext_remitter_party_name, innr.ext_remitter_comp_key, innr.ext_executing_comp_key, innr.txn_country_code, innr.txn_currency_code, innr.status_desc, innr.status_reason_desc, ACCT_ANA.expected_incoming_amount, ACCT_ANA.expected_outgoing_amount, ACCT_ANA.expected_incoming_count, ACCT_ANA.expected_outgoing_count, ACCT_ANA.increased_behavior_credit_amt , ACCT_ANA.increased_behavior_debit_amt, ACCT_ANA.increased_behavior_credit_cnt, ACCT_ANA.increased_behavior_debit_cnt , ACCT_ANA.expected_credit_ceiling_amount, ACCT_ANA.expected_debit_ceiling_amount, innr.transaction_key, innr.transaction_type_key, innr.remitter_ext_party_key, innr.beneficiary_ext_party_key, innr.executing_party_key, innr.executing_ext_party_key, innr.party_key, innr.account_key, PTY_SEG.segment_cd, /* Custom add column for SCB only*/ innr.x_account_name, innr.account_primary_branch_name, innr.x_secondary_account_number, innr.x_secondary_account_name, innr.x_party_identification_id, innr.x_party_identification_type_desc, innr.x_street_postal_code, innr.x_org_bank_name, innr.x_des_bank_name, innr.x_account_age_days, innr.x_txn_branch_province, innr.x_acc_branch_province, innr.x_have_atm_ind, innr.x_account_open_date, innr.x_occupation_code, innr.x_occupation_desc, innr.x_account_purpose_desc, innr.x_apply_ibank_date, innr.x_balance_amount, coalesce(WL_ENTY.watchlist_ind,'N') as x_watchlist_ind, coalesce(WL_ENTY.greylist_ind,'N') as x_greylist_ind, coalesce(WL_ENTY.risklist_ind,'N') as x_risklist_ind, innr.x_hrct_ind, innr.x_hrfi_ind, innr.x_hrpc_ind, innr.x_account_close_date, innr.x_region_name, innr.x_ptn_yyyymmdd, /* Added column for MG*/ innr.x_party_type_code, innr.x_mg_rem_country_code, case when (PTY_SEG.segment_cd is null or PTY_SEG.segment_cd = '') and innr.X_PARTY_TYPE_CODE is not null then concat(innr.X_PARTY_TYPE_CODE,'-UNDEFINE') when (PTY_SEG.segment_cd is null or PTY_SEG.segment_cd = '') and innr.X_PARTY_TYPE_CODE is null then 'UNKNOWN' else PTY_SEG.segment_cd end as c_segment_cd, innr.c_acc_segment_cd from amlprep.x_temp_account_event_fact_&sequence_id. innr /* New ootb table for AML83*/ left join amlkc.fsk_account_analysis_dim ACCT_ANA on innr.account_number = ACCT_ANA.account_number left join kc.party_segment PTY_SEG on innr.party_number = PTY_SEG.party_number and PTY_SEG.change_current_ind = 'Y' /* Custom table for SCB only*/ left join amlprep.x_temp_wl_identifier WL_ENTY on innr.x_party_identification_id = WL_ENTY.identifier_number and innr.x_party_identification_type_desc = WL_ENTY.identifier_type ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); %if (&trans_rc eq 3 or &trans_rc ge 5) or (&sqlrc eq 3 or &sqlrc ge 5) %then %do; %let _fcfmsg_=ERROR:FCF:Unable to insert data to amlprep.x_transactions from fsc_cash_flow_fact; %fcf_put_error; %goto MACRO_END; %end; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name. [ &step_timestamp. ] ; %end; %end; /*==========================================================================* * Step : Update current Information Transaction prep *==========================================================================*/ %let step_name = Update Current Info of table amlprep.x_transactions ; %let step_StartTime = %sysfunc(datetime(),datetime.); %put NOTE: Start &step_name. [ &step_timestamp. ] ; proc sql; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute( update txn set txn.x_account_age_days = datediff(day,txn.customer_since_date,&run_asofdate_str.) from amlprep.x_transactions as txn where cast(transaction_dttm as date) < &run_asofdate_str. ) by sqlsvr; disconnect from sqlsvr; quit; proc sql; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute( update txn set txn.c_acc_segment_cd = case when (substring(acc.account_type_desc,1,5)) = '' or (substring(acc.account_type_desc,1,5)) is null or (substring(acc.account_type_desc,1,5)) = 'UNKNO' then 'UNKNOWN' else (substring(acc.account_type_desc,1,5)) end from amlprep.x_transactions as txn inner join corevw.account_dim as acc on txn.account_number = acc.account_number and cast(acc.change_begin_date as date) = &run_asofdate_str. where cast(transaction_dttm as date) <> &run_asofdate_str. and txn.c_acc_segment_cd <> case when (substring(acc.account_type_desc,1,5)) = '' or (substring(acc.account_type_desc,1,5)) is null or (substring(acc.account_type_desc,1,5)) = 'UNKNO' then 'UNKNOWN' else (substring(acc.account_type_desc,1,5)) end ) by sqlsvr; disconnect from sqlsvr; quit; proc sql; connect to sqlsvr ( &dbConnDsnCore. AuthDomain="Cs.batch" ); execute( update txn set txn.c_segment_cd = case when (ps.segment_cd is null or ps.segment_cd = '') and txn.X_PARTY_TYPE_CODE is not null then concat(txn.X_PARTY_TYPE_CODE,'-UNDEFINE') when (ps.segment_cd is null or ps.segment_cd = '') and txn.X_PARTY_TYPE_CODE is null then 'UNKNOWN' else ps.segment_cd end from amlprep.x_transactions as txn inner join kc.party_segment as ps on txn.party_number = ps.party_number and cast(ps.assignment_dttm as date) = &run_asofdate_str. where cast(transaction_dttm as date) <> &run_asofdate_str. and txn.c_segment_cd <> case when (ps.segment_cd is null or ps.segment_cd = '') and txn.X_PARTY_TYPE_CODE is not null then concat(txn.X_PARTY_TYPE_CODE,'-UNDEFINE') when (ps.segment_cd is null or ps.segment_cd = '') and txn.X_PARTY_TYPE_CODE is null then 'UNKNOWN' else ps.segment_cd end ) by sqlsvr; disconnect from sqlsvr; quit; %rcSet(&syserr); %rcSet(&sqlrc); %if (&trans_rc eq 3 or &trans_rc ge 5) or (&sqlrc eq 3 or &sqlrc ge 5) %then %do; %let _fcfmsg_=ERROR:FCF:Unable to Update data to amlprep.x_transactions; %fcf_put_error; %goto MACRO_END; %end; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: Finish &step_name. [ &step_timestamp. ] ; /*==========================================================================* /* Step : Logging job executed status *==========================================================================*/ %MACRO_END: %let etls_endTime = %sysfunc(datetime(),datetime.); %let content_name = PREP_DATE=&run_asofdate. | DAILY_PREP; %job_status; %MACRO_SKIP: %let step_name = Run x_transaction_prep process ; %let step_timestamp = %sysfunc(datetime(),datetime.); %put NOTE: End &step_name [ &step_timestamp ] ; %mend x_fcf_transactions_prep; /* %x_fcf_asofdttm(action = GET, job_name='SOL'); */ /* %x_fcf_transactions_prep(run_asofdate=20230703, cash_fact=Y, event_fact=Y); */