5%macro fs_projection(input_data_rwa_key =
23 ds_in_aggregation_config
48 %let cd_var_bs = balance_sheet_cd;
49 %let cd_var_pl = profit_loss_cd;
50 %let schema_gl = st_gl_account;
51 %let schema_pl = st_profit_loss_account;
52 %let schema_of = st_own_funds_account;
53 %let ds_in_aggregation_config = ST_CFG.aggregation_rules_config;
54 %let tbls_info = ST_STG.tbls_info;
55 %let plan_data_of = ST_STG.st_own_funds_account;
56 %let plan_data_rwa = ST_STG.st_rwa_weights;
57 %let map_movement_type = ST_MAP.map_movement_type;
58 %let map_profit_loss_hierarchy = ST_MAP.map_profit_loss_hierarchy;
59 %let map_balance_sheet_hierarchy = ST_MAP.map_balance_sheet_hierarchy;
60 %let ds_acct_cd_lookup = ST_STG.account_cd_lookup;
65 %let of_growth_pct = 0.1;
69 data work.bep_summary_exp;
70 set &ds_in_bep_summary.;
71 original_targetVariable = targetVariable;
72 original_accountID = accountID;
73 original_planningDataKey = planningDataKey;
74 original_dataDefSchemaName = dataDefSchemaName;
75 original_dataDefSchemaVersion = dataDefSchemaVersion;
76 do i=1 to countw(original_targetVariable,
",");
77 targetVariable = scan(original_targetVariable,i,
",");
78 accountID = scan(original_accountID,i,
",");
79 planningDataKey = scan(original_planningDataKey,i,
",");
80 dataDefSchemaName = scan(original_dataDefSchemaName,i,
",");
81 dataDefSchemaVersion = scan(original_dataDefSchemaVersion,i,
",");
85 call symputx(
"fund_src_vars", fundSrcVars,
"L");
88 call symputx(
"interval_count", intervalCount,
"L");
89 call symputx(
"interval", interval,
"L");
96 set work.bep_summary_exp end=last;
97 retain gl_found pl_found of_found 0;
99 if upcase(dataDefSchemaName) not in (%upcase(
"&schema_gl."), %upcase(
"&schema_pl."), %upcase(
"&schema_of.")) then do;
100 put "ERROR: Expecting only planning data with schema name &schema_gl. or &schema_pl. or &schema_of..";
104 else if upcase(dataDefSchemaName) eq upcase("&schema_gl.") then do;
106 call symputx("schema_gl_version", dataDefSchemaVersion, "L");
109 else if upcase(dataDefSchemaName) eq upcase("&schema_of.") then do;
111 call symputx("schema_of_version", dataDefSchemaVersion, "L");
116 call symputx("schema_pl_version", dataDefSchemaVersion, "L");
119 if last and (_N_ ne 3 or gl_found = 0 or pl_found = 0 or of_found = 0) then do;
120 put "ERROR: Expecting exactly one planning data with schema name &schema_gl. and one with schema name &schema_pl. and one with schema name &schema_of..";
127 set work.bep_summary_exp;
130 if upcase(dataDefSchemaName) = upcase("&schema_gl.") then type = "bs";
131 else if upcase(dataDefSchemaName) = upcase("&schema_pl.") then type = "pl";
132 else if upcase(dataDefSchemaName) = upcase("&schema_of.") then type = "of";
133 call symputx(cats("id_var_",type), accountId, "L");
134 call symputx(cats("target_var_",type), targetVariable, "L");
135 call symputx(cats("plan_data_", type, "_key"), planningDataKey, "L");
139 %let httpSuccess = 0;
140 %let responseStatus =;
142 %irm_rgf_retrieve_analysis_data(key = &plan_data_bs_key.
144 , outds = work.plan_data_bs_tmp
145 , custom_code = length movement_desc $ 100.; movement_id = 1; movement_desc = "Opening Balance"
147 , outds_dataInfo = planning_dataInfo_bs
148 , outds_dataDef = planning_dataDef_bs
149 , host = &rgf_protocol.:
150 , server = &rgf_service.
153 , tgt_ticket = &tgt_ticket.
154 , outVarTicket = ticket
155 , outSuccess = httpSuccess
156 , outResponseStatus = responseStatus
160 %let httpSuccess = 0;
161 %let responseStatus =;
163 %irm_rgf_retrieve_analysis_data(key = &plan_data_pl_key.
165 , outds = work.plan_data_pl_tmp
166 , custom_code = length movement_desc $ 100.; movement_id = 1; movement_desc = "Opening Balance"
168 , outds_dataInfo = planning_dataInfo_pl
169 , outds_dataDef = planning_dataDef_pl
170 , host = &rgf_protocol.:
171 , server = &rgf_service.
174 , tgt_ticket = &tgt_ticket.
175 , outVarTicket = ticket
176 , outSuccess = httpSuccess
177 , outResponseStatus = responseStatus
184 data &ds_acct_cd_lookup;
185 length account_id $ 32.
187 keep account_cd account_id;
188 set work.plan_data_bs_tmp (keep = &id_var_bs. &cd_var_bs.)
189 work.plan_data_pl_tmp (keep = &id_var_pl. &cd_var_pl.);
190 account_id = coalescec(&id_var_bs., &id_var_pl.);
191 account_cd = coalescec(&cd_var_bs., &cd_var_pl.);
193 account_id = account_cd;
206 %if not (&cd_var_bs. = &id_var_bs. and &cd_var_pl. = &id_var_pl.) %then %do;
207 data work.ds_in_bep_details;
208 set &ds_in_bep_details. (where=(strip(planningDataKey) ne strip("&plan_data_of_key.")) rename=(accountId=account_id));
209 length account_cd $ 32. lookup_id $ 32. lookup_cd $ 32.;
212 declare hash hAcct(dataset: "&ds_acct_cd_lookup.");
213 hAcct.defineKey("account_id");
214 hAcct.defineData("account_cd");
217 declare hash hfundsrc(dataset: "&ds_acct_cd_lookup.(rename=(account_id=lookup_id account_cd=lookup_cd))");
218 hfundsrc.defineKey("lookup_id");
219 hfundsrc.defineData("lookup_cd");
220 hfundsrc.defineDone();
223 call missing(account_cd);
226 %do x=1 %to %sysfunc(countw(%quote(&fund_src_vars),%str(,)));
227 call missing(lookup_cd,lookup_id);
228 %let varname = %scan(%quote(&fund_src_vars),&x.);
229 lookup_id = &varname.;
230 _rc_ = hfundsrc.find();
231 if _rc_ eq 0 then do;
232 &varname. = lookup_cd;
236 drop lookup_id lookup_cd _rc_;
241 data work.ds_in_bep_details;
242 set &ds_in_bep_details. (where=(strip(planningDataKey) ne strip("&plan_data_of_key.")) rename=(accountId=account_id));
243 length account_cd $ 32. ;
244 account_cd = account_id;
249 data volAssump yldAssump valAssump;
250 set work.ds_in_bep_details;
251 if varFamily='volume' then output work.volAssump;
252 else if varFamily='yield' then output work.yldAssump;
253 else if varFamily='value' then output work.valAssump;
259 if tbl_name = "&schema_gl." then type = "BS";
260 else if tbl_name = "&schema_pl." then type = "PL";
261 call symputx(cats("plan_data_",type), cats(tbl_lib, ".", tbl_name), "L");
263 call symputx("scenario_name", scenario_name, "L");
267 %if(not %rsk_dsexist(work.plan_data_bs_tmp) or not %rsk_dsexist(work.plan_data_pl_tmp)) %then %do;
268 %put ERROR: Planning data does not exist. Could not perform financial statement projection.;
271 %if(not %symexist(plan_data_bs) or not %symexist(plan_data_pl)) %then %do;
272 %put ERROR: Aggregated planning data does not exist. Could not perform financial statement projection.;
275 %if(not %rsk_dsexist(&plan_data_bs.) or not %rsk_dsexist(&plan_data_pl.)) %then %do;
276 %put ERROR: Aggregated planning data does not exist. Could not perform financial statement projection.;
281 %let httpSuccess = 0;
282 %let responseStatus =;
284 %irm_rgf_retrieve_analysis_data(key = &plan_data_of_key.
286 , outds = &plan_data_of.
287 , custom_code = length movement_desc $ 100.; movement_id = 1; movement_desc = "Opening Balance"
289 , outds_dataInfo = planning_dataInfo_of
290 , outds_dataDef = planning_dataDef_of
291 , host = &rgf_protocol.:
292 , server = &rgf_service.
295 , tgt_ticket = &tgt_ticket.
296 , outVarTicket = ticket
297 , outSuccess = httpSuccess
298 , outResponseStatus = responseStatus
304 %if(not &httpSuccess. or not %rsk_dsexist(&plan_data_of.)) %then %do;
305 %put ERROR: Own Funds data with key &input_data_of_key. does not exist.;
310 %let httpSuccess = 0;
311 %let responseStatus =;
313 %irm_rgf_retrieve_analysis_data(key = &input_data_rwa_key.
315 , outds = &plan_data_rwa.
316 , custom_code = length movement_desc $ 100.; movement_id = 1; movement_desc = "Opening Balance"
317 , outds_dataInfo = planning_dataInfo_rwa
318 , outds_dataDef = planning_dataDef_rwa
319 , host = &rgf_protocol.:
320 , server = &rgf_service.
323 , tgt_ticket = &tgt_ticket.
324 , outVarTicket = ticket
325 , outSuccess = httpSuccess
326 , outResponseStatus = responseStatus
332 %if(not &httpSuccess. or not %rsk_dsexist(&plan_data_rwa.)) %then %do;
333 %put ERROR: RWA data with key &input_data_rwa_key. does not exist.;
340 create table fsStructureT0 as
341 select distinct &cd_var_bs. as account_cd
342 , &id_var_bs. as account_id
343 , sum(&target_var_bs.) as amountT0
344 , forecast_horizon as activationHorizon
345 ,'BS' as fs_type length=10
346 ,BALANCE_SHEET_LEV1 as accnt_type length=200
349 where forecast_horizon = 0
350 group by account_cd, account_id
352 select distinct &cd_var_pl. as account_cd
353 , &id_var_pl. as account_id
354 , sum(&target_var_pl.) as amountT0
355 , forecast_horizon as activationHorizon
356 , 'PL' as fs_type length=10
357 ,PROFIT_LOSS_LEV1 as accnt_type length=200
359 where forecast_horizon = 0
360 group by account_cd, account_id
365 create table ownFundsT0 as
366 select distinct &cd_var_bs. as account_cd
367 , &id_var_of. as account_id
368 , sum(&target_var_of.) as amountT0
369 , forecast_horizon as activationHorizon
371 where forecast_horizon = 0
372 group by account_cd, account_id
378 set &plan_data_bs. (rename=(&cd_var_bs. = account_cd
379 &target_var_bs. = amount_model))
380 &plan_data_pl. (rename=(&cd_var_pl. = account_cd
381 &target_var_pl. = amount_model))
383 length account_cd $32.;
391 %do %while(&i. le &interval_count.);
393 %let j = %eval(&i.-1);
394 %let actualsData = fsStructureT&j.;
401 select sum(amountT&j.) format=BEST12.
402 into :profit_loss_amt_yr&j.
403 from work.fsStructureT&j.
404 where account_cd in (select distinct &cd_var_pl.
406 and account_cd in (select distinct &cd_var_pl.
407 from &map_profit_loss_hierarchy.
408 where roll_up ne 'Yes')
412 %let profit_loss_amt_yr&j.=&&profit_loss_amt_yr&j.;
414 data work.bep_yr_bg_posting (rename=(account_cd = posting_account_id)
415 keep= posting_group_id
420 work.fsStructureT&j.(drop=posting_group_id movement:);
422 posting_group_id length = $50. label = "Posting Group Id"
423 movement_id length = 8. label = "Movement Id"
424 movement_desc length = $100. label = "Movement Description"
425 %irm_get_attrib_def(ds_in = &map_movement_type.)
427 set work.fsStructureT&j.(keep= account_cd account_id amountT&j. activationHorizon fs_type accnt_type);
428 posting_group_id = cats('PRE_',account_cd);
432 declare hash hMvmt(dataset: "&map_movement_type.");
433 hMvmt.defineKey("movement_type_cd");
434 hMvmt.definedata("movement_type", "movement_category");
438 movement_type_cd = "BS_MODEL";
439 movement_desc = "Period Opening Adjustments";
444 if account_cd = "&bsRetainRREAccnt." then do;
447 amountT&j. = sum(amountT&j.,&&profit_loss_amt_yr&j..);
448 variation = &&profit_loss_amt_yr&j..;
449 output work.bep_yr_bg_posting;
452 if account_cd = "&bsRetainYPFAccnt." then do;
455 amountT&j. = sum(amountT&j.,-&&profit_loss_amt_yr&j..);
456 variation= -&&profit_loss_amt_yr&j..;
457 output work.bep_yr_bg_posting;
459 if amountT&j. ne 0 then
460 put "WARNING: &bsRetainYPFAccnt. from period &j is not equal to total PL";
463 output work.fsStructureT&j.;
469 %irmst_apply_volume_calculations( map_movement_type = &map_movement_type.
470 , actualsData = &actualsData.
471 , activationHorizon = &i.
478 %irmst_apply_yield_calculations( map_movement_type = &map_movement_type.
479 , actualsData = &actualsData.
480 , activationHorizon = &i.
486 %irmst_apply_value_calculations( map_movement_type = &map_movement_type.
487 , actualsData = &actualsData.
488 , activationHorizon = &i.
497 select sum(amountT&i.) format=BEST12.
498 into :profit_loss_amt_yr&i.
499 from work.fsStructureT&i.
500 where account_cd in (select distinct &cd_var_pl.
502 and account_cd in (select distinct &cd_var_pl.
503 from &map_profit_loss_hierarchy.
504 where roll_up ne 'Yes')
508 %let profit_loss_amt_yr&i.=&&profit_loss_amt_yr&i.;
511 data work.bep_ynd_posting (rename=(account_cd = posting_account_id)
512 keep= posting_group_id
517 work.fsStructureT&i. (drop= posting_group_id
520 posting_group_id length = $50. label = "Posting Group Id"
521 movement_id length = 8. label = "Movement Id"
522 movement_desc length = $100. label = "Movement Description"
523 %irm_get_attrib_def(ds_in = &map_movement_type.)
525 set work.fsStructureT&i.;
526 posting_group_id = cats('YND_',account_cd);
530 declare hash hMvmt(dataset: "&map_movement_type.");
531 hMvmt.defineKey("movement_type_cd");
532 hMvmt.definedata("movement_type", "movement_category");
536 movement_type_cd = "BS_MODEL";
537 movement_desc = "Year end Adjustments";
542 if account_cd = "&bsRetainYPFAccnt." then do;
545 amountT&i. = &&profit_loss_amt_yr&i..;
546 variation= &&profit_loss_amt_yr&i..;
547 output work.bep_ynd_posting;
551 movement_desc = "BS Projection Model";
553 output work.fsStructureT&i.;
562 %let movement_out_vars = posting_group_id
570 work.bep_yr_bg_posting(keep= &movement_out_vars)
571 work.bep_vol_posting(keep= &movement_out_vars)
572 work.bep_yld_posting(keep= &movement_out_vars)
573 work.bep_val_posting(keep= &movement_out_vars)
574 %if(%rsk_dsexist(lineitem_rule_posting)) %then %do;
575 work.lineitem_rule_posting(keep= &movement_out_vars)
577 work.bep_ynd_posting(keep= &movement_out_vars)
579 rename posting_account_id = account_id;
580 length scenario_name $512. forecast_horizon 8.;
581 scenario_name = "&scenario_name.";
582 forecast_horizon = &i.;
591 set fsStructureT&i. end=last;
592 length forecast_horizon 8.
597 declare hash hRWA(dataset: "&plan_data_rwa.(rename=(&cd_var_bs=account_cd rwa=rwa_input forecast_horizon=activationHorizon))");
598 hRWA.defineKey("account_cd","activationHorizon");
599 hRWA.defineData("rwa_input");
602 declare hash hBSMap(dataset:"&map_balance_sheet_hierarchy.(rename=(&cd_var_bs=account_cd))");
603 hBSMap.defineKey("account_cd");
604 hBSMap.defineData("roll_up");
607 declare hash hModelDataBS(dataset: "&plan_data_bs.(rename=(&cd_var_bs=account_cd forecast_horizon=activationHorizon rwa=rwa_model))");
608 hModelDataBS.defineKey("account_cd","activationHorizon");
609 hModelDataBS.defineData("rwa_model");
610 hModelDataBS.defineDone();
612 call missing(rwa_input);
613 call missing(rwa_model);
615 forecast_horizon = activationHorizon;
618 _rc1_ = hModelDataBS.find();
619 _rc3_ = hBSMap.find();
621 rwa = coalesce(rwa_model, rwa_input * amountT&i.);
623 if roll_up ne 'Yes' then
624 rwa_total=sum(rwa_total,rwa);
627 call symputx("rwa_credit", rwa_total);
631 %put NOTE: RWA Credit = &rwa_credit.;
637 data work.OF_adjustments;
638 set &ds_in_bep_details. (where=(strip(planningDataKey) eq strip("&plan_data_of_key.") and varFamily eq 'value') rename=(accountId=account_id activationHorizon=forecast_horizon));
643 movement_id length = 8. label = "Movement Id"
644 movement_desc length = $100. label = "Movement Description"
645 forecast_horizon length = 8. label = "Forecast Horizon"
646 %irm_get_attrib_def(ds_in = &map_movement_type.)
648 forecast_horizon = &i.;
651 declare hash fsProjection(dataset: "work.fsStructureT&i.");
652 fsProjection.defineKey("account_cd");
653 fsProjection.definedata("amountT&i.");
654 fsProjection.defineDone();
656 declare hash hMvmt(dataset: "&map_movement_type.");
657 hMvmt.defineKey("movement_type_cd");
658 hMvmt.definedata("movement_type", "movement_category");
661 declare hash hValAdj(dataset: "OF_adjustments");
662 hValAdj.defineKey("forecast_horizon", "account_id");
663 hValAdj.definedata("value_adjust");
664 hValAdj.defineDone();
666 call missing(amountT&i.);
668 movement_type_cd = "BS_MODEL";
669 movement_desc = "BS Projection Model";
673 _rc1_ = hMvmt.find();
674 _rc2_ = fsProjection.find();
676 if _rc2_ ne 0 then do;
677 amountT&i.= amountT&j.;
678 call missing(value_adjust);
679 _rc3_ = hValAdj.find();
680 amountT&i. = amountT&i.*(1+coalesce(value_adjust,0));
684 if account_id = "&ofRWAAccnt." then
685 amountT&i.= &rwa_credit.;
692 forecast_horizon = &i.;
700 %let dropvars = account_cd account_id amountT&j. accnt_type;
703 create table work.plan_data_bs&i._out (drop=&dropvars.) as
705 from work.plan_data_bs_tmp (drop = &target_var_bs.
710 inner join fsStructureT&i.(rename=(amountT&i. = &target_var_bs
711 activationHorizon = forecast_horizon)) t2
712 on t1.&cd_var_bs. = t2.account_cd
718 create table work.plan_data_pl&i._out (drop = &dropvars.) as
720 from work.plan_data_pl_tmp (drop = &target_var_pl.
724 inner join fsStructureT&i.(rename=(amountT&i. = &target_var_pl
725 activationHorizon = forecast_horizon)) t2
726 on t1.&cd_var_pl. = t2.account_cd
732 create table work.plan_data_of&i._out (drop = &dropvars.) as
734 from &plan_data_of. (drop = &target_var_of.
739 left join work.ownFundsT&i (rename=(amountT&i. = &target_var_of)) t2
740 on t1.&id_var_of. = t2.account_id
744 %let i = %eval(&i.+1);
752 %irmc_map_data(dataMap_key = &data_map_gl.
757 , ds_out = _tmp_data_structure_
759 , outVarTicket = ticket
760 , outSuccess = httpSuccess
761 , outResponseStatus = responseStatus
763 %irmc_map_data(dataMap_key = &data_map_pl.
768 , ds_out = _tmp_data_structure_
770 , outVarTicket = ticket
771 , outSuccess = httpSuccess
772 , outResponseStatus = responseStatus
774 %irmc_map_data(dataMap_key = &data_map_of.
779 , ds_out = _tmp_data_structure_
781 , outVarTicket = ticket
782 , outSuccess = httpSuccess
783 , outResponseStatus = responseStatus
788 data glAnalysisDataOut;
789 set work.plan_data_bs_tmp
790 %do k=1 %to &interval_count.;
791 work.plan_data_bs&k._out
795 fs_type length = $10. label = "Financial Statement Type"
796 scenario_name length = $512. label = "Scenario Name"
797 forecast_period length = $32. label = "Forecast Period"
800 scenario_name = "&scenario_name.";
801 forecast_period = "&interval.";
805 data plAnalysisDataOut;
806 set work.plan_data_pl_tmp
807 %do k=1 %to &interval_count.;
808 work.plan_data_pl&k._out
812 fs_type length = $10. label = "Financial Statement Type"
813 scenario_name length = $512. label = "Scenario Name"
814 forecast_period length = $32. label = "Forecast Period"
817 scenario_name = "&scenario_name.";
818 forecast_period = "&interval.";
822 data ofAnalysisDataOut;
824 %do k=1 %to &interval_count.;
825 work.plan_data_of&k._out
829 fs_type length = $10. label = "Financial Statement Type"
830 scenario_name length = $512. label = "Scenario Name"
831 forecast_period length = $32. label = "Forecast Period"
834 scenario_name = "&scenario_name.";
835 forecast_period = "&interval.";
839 %irmc_apply_rollup_aggregation( aggregation_rules = &ds_in_aggregation_config.
840 ,schema_name = st_gl_account
841 ,schema_version = &content_version.
842 ,input_ds = work.glAnalysisDataOut
843 ,period_column = forecast_horizon
844 ,output_ds = work.glAnalysisDataOut
846 %irmc_apply_rollup_aggregation( aggregation_rules = &ds_in_aggregation_config.
847 ,schema_name = st_profit_loss_account
848 ,schema_version = &content_version.
849 ,input_ds = work.plAnalysisDataOut
850 ,period_column = forecast_horizon
851 ,output_ds = work.plAnalysisDataOut
853 %irmc_apply_rollup_aggregation( aggregation_rules = &ds_in_aggregation_config.
854 ,schema_name = st_own_funds_account
855 ,schema_version = &content_version.
856 ,input_ds = work.ofAnalysisDataOut
857 ,period_column = forecast_horizon
858 ,output_ds = work.ofAnalysisDataOut
862 data glAnalysisDataOut;
863 set glAnalysisDataOut;
864 %include fmap_gl / source2 lrecl = 32000;
867 data plAnalysisDataOut;
868 set plAnalysisDataOut;
869 %include fmap_pl / source2 lrecl = 32000;
872 data ofAnalysisDataOut;
873 set ofAnalysisDataOut;
874 %include fmap_of / source2 lrecl = 32000;
884 %include "&fa_path./source/sas/misc/ddl/reportmart/fs_projection.sas" / source2 lrecl = 32000;
888 proc transpose data=st_cfg.target_ratios_config
889 out=work.target_ratios_config_t(drop=_name_ _label_);
890 var target_ratio_value;
892 idlabel target_ratio_name;
897 create table ofAnalysisDataOut as
900 from ofAnalysisDataOut t1,
901 work.target_ratios_config_t t2;
904 data ds_out_bep_expectations;
905 attrib %irm_get_attrib_def(ds_in = work.fs_projection);
906 set glAnalysisDataOut
910 forecast_time = forecast_horizon;
911 analysis_run_name = "&analysis_run_name.";
912 analysis_run_id = &analysis_run_id.;
913 project_name = "&cycle_name.";
914 drop forecast_horizon;
922 data st_stg.movements;
923 set %do k=1 %to &interval_count.;