Rule tables (including Range/Select-Options) using Option, High, Low values are prevalent in the ABAP application stacks.
While Carine’s blog below goes into how to handle Select-Options and apply the filters within a Where clause within AMDP, I want to highlight more complex scenarios where you may want to carry the associated values of the range forward in your queries.
Handling of SELECT-OPTIONS parameters within AMDP | SAP Blogs
We recently re-wrote a standard routine to find associated Business Partners to Territories. As BP attribute data is not static, we had a daily job that was executed to update BP to Territory relationships based on delta master data changes that had been applied to BPs. This was taking an excessive long time to complete and was a hot watch item for our operations team.
As the standard routine was re-processing all territories regardless of whether any updates needed to be applied, we wanted to identify territories in advance based on BP delta changes. This allowed us to supply a drastically reduced list of territories to the standard program crm_terrman_proc_rel.
I will simplify down the SQL used, focusing specifically on how to aggregate the options in the rules table and query the within the AMDP.
First, lets look at the values used in the option column and how we can aggregate based on the Include/Exclude sign values.
*Comparison Values (10 options X 2 Sign values (‘I’,’E’)
EQ – is (equal)
NE – is not (not equal)
BT – is between (must have Low and High Values)
NB – is not between (must have Low and High Values)
LT – is less than
GT – is greater than
LE – is less than or equal to
GE – is greater than or equal to
CP – contains pattern
NP – does not contain pattern
As (‘I’ ‘EQ’) is equivalent to (‘E’, ‘NE’), we can aggregate the rules down to 10 distinct values.
lt_eq_rule_list = Select territory_guid, rule_id, attr_id, valuel, 'EQ_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'EQ'
Union ALL
Select territory_guid, rule_id, attr_id, valuel, 'EQ_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'NE';
lt_ne_rule_list = Select territory_guid, rule_id, attr_id, valuel, 'NE_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'NE'
Union ALL
Select territory_guid, rule_id, attr_id, valuel, 'NE_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'EQ';
*
lt_bt_rule_list = Select territory_guid, rule_id, attr_id, valuel, valueh, 'BT_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'BT'
Union ALL
Select territory_guid, rule_id, attr_id, valuel, valueh, 'BT_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'NB';
:
:
lt_cp_rule_list = Select territory_guid, rule_id, attr_id, replace(valuel,'*','%') as valuel, 'CP_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'CP'
Union ALL
Select territory_guid, rule_id, attr_id, replace(valuel,'*','%') as valuel, 'CP_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'NP';
*
lt_np_rule_list = Select territory_guid, rule_id, attr_id, replace(valuel,'*','%') as valuel, 'NP_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'NP'
Union ALL
Select territory_guid, rule_id, attr_id, replace(valuel,'*','%') as valuel, 'NP_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'CP';
Please note the replacement function used in the contains pattern rule, as we are now executing these in SQL statements on the HANA db direct in AMDP, we need to use % for wildcard pattern matching.
We had previously prepared an temp table of delta BP & attribute values that are relevant to Territory assignment. Based on this, we can apply the conditions above and capture both the partner guids plus the territory guids, along with rules & options that were applied.
-- Condition 1, Include 'EQ' & Exclude 'NE'
lt_cond1_terr_list = Select av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option
From :it_acc_attr_values av
Inner Join :lt_eq_rule_list rl ON av.attr_id = rl.attr_id And av.attr_value = rl.valuel
Group by av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option;
:
-- Condition 3, Include 'BT' & Exclude 'NB'
lt_cond3_terr_list = Select av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option
From :it_acc_attr_values av
Inner Join :lt_bt_rule_list rl ON av.attr_id = rl.attr_id And av.attr_value between rl.valuel and rl.valueh
Group by av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option;
:
:
-- Condition 9, Include 'CP' & Exclude 'NP'
lt_cond9_terr_list = Select av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option
From :it_acc_attr_values av
Inner Join :lt_cp_rule_list rl ON av.attr_id = rl.attr_id And av.attr_value like rl.valuel
Group by av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option;
:
Applying all 10 conditions against the BP attribute table, this gives us a complete list of territories that need to be processed based on the current BP attribute values.
Aside for proc_rel interested folks: This subset of BP values was from the delta list that had been modified since the last job run. This exercise above was to find new territories, existing territories can easily be found by looking at the current system TM relationships.
Take all the results, union them up and aggregate them before returning the result-set in the class method.
-- Put all these together
lt_terr_rule_acc_list = Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond1_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond2_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond3_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond4_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond5_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond6_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond7_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond8_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond9_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond10_terr_list;
-- once we determine there is a link between the territory, rule, attribute to partner account, we can aggregate here, drop the dups, actual values etc.
et_terr_rule_acc_list = Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_terr_rule_acc_list
Group by partner_guid, territory_guid, rule_id, attr_id, rule_option;
I hope this info above may prove useful for any folks looking to process similar rule tables directly in AMDP. If anyone would like more code from the class methods, please ping me directly on SCN.
Okumaya devam et...
While Carine’s blog below goes into how to handle Select-Options and apply the filters within a Where clause within AMDP, I want to highlight more complex scenarios where you may want to carry the associated values of the range forward in your queries.
Handling of SELECT-OPTIONS parameters within AMDP | SAP Blogs
We recently re-wrote a standard routine to find associated Business Partners to Territories. As BP attribute data is not static, we had a daily job that was executed to update BP to Territory relationships based on delta master data changes that had been applied to BPs. This was taking an excessive long time to complete and was a hot watch item for our operations team.
As the standard routine was re-processing all territories regardless of whether any updates needed to be applied, we wanted to identify territories in advance based on BP delta changes. This allowed us to supply a drastically reduced list of territories to the standard program crm_terrman_proc_rel.
I will simplify down the SQL used, focusing specifically on how to aggregate the options in the rules table and query the within the AMDP.
First, lets look at the values used in the option column and how we can aggregate based on the Include/Exclude sign values.
*Comparison Values (10 options X 2 Sign values (‘I’,’E’)
EQ – is (equal)
NE – is not (not equal)
BT – is between (must have Low and High Values)
NB – is not between (must have Low and High Values)
LT – is less than
GT – is greater than
LE – is less than or equal to
GE – is greater than or equal to
CP – contains pattern
NP – does not contain pattern
As (‘I’ ‘EQ’) is equivalent to (‘E’, ‘NE’), we can aggregate the rules down to 10 distinct values.
lt_eq_rule_list = Select territory_guid, rule_id, attr_id, valuel, 'EQ_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'EQ'
Union ALL
Select territory_guid, rule_id, attr_id, valuel, 'EQ_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'NE';
lt_ne_rule_list = Select territory_guid, rule_id, attr_id, valuel, 'NE_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'NE'
Union ALL
Select territory_guid, rule_id, attr_id, valuel, 'NE_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'EQ';
*
lt_bt_rule_list = Select territory_guid, rule_id, attr_id, valuel, valueh, 'BT_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'BT'
Union ALL
Select territory_guid, rule_id, attr_id, valuel, valueh, 'BT_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'NB';
:
:
lt_cp_rule_list = Select territory_guid, rule_id, attr_id, replace(valuel,'*','%') as valuel, 'CP_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'CP'
Union ALL
Select territory_guid, rule_id, attr_id, replace(valuel,'*','%') as valuel, 'CP_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'NP';
*
lt_np_rule_list = Select territory_guid, rule_id, attr_id, replace(valuel,'*','%') as valuel, 'NP_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'I'
And options = 'NP'
Union ALL
Select territory_guid, rule_id, attr_id, replace(valuel,'*','%') as valuel, 'NP_RULE' as rule_option
From :it_terr_rule_list
Where sign = 'E'
And options = 'CP';
Please note the replacement function used in the contains pattern rule, as we are now executing these in SQL statements on the HANA db direct in AMDP, we need to use % for wildcard pattern matching.
We had previously prepared an temp table of delta BP & attribute values that are relevant to Territory assignment. Based on this, we can apply the conditions above and capture both the partner guids plus the territory guids, along with rules & options that were applied.
-- Condition 1, Include 'EQ' & Exclude 'NE'
lt_cond1_terr_list = Select av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option
From :it_acc_attr_values av
Inner Join :lt_eq_rule_list rl ON av.attr_id = rl.attr_id And av.attr_value = rl.valuel
Group by av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option;
:
-- Condition 3, Include 'BT' & Exclude 'NB'
lt_cond3_terr_list = Select av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option
From :it_acc_attr_values av
Inner Join :lt_bt_rule_list rl ON av.attr_id = rl.attr_id And av.attr_value between rl.valuel and rl.valueh
Group by av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option;
:
:
-- Condition 9, Include 'CP' & Exclude 'NP'
lt_cond9_terr_list = Select av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option
From :it_acc_attr_values av
Inner Join :lt_cp_rule_list rl ON av.attr_id = rl.attr_id And av.attr_value like rl.valuel
Group by av.partner_guid, rl.territory_guid, rule_id, rl.attr_id, rule_option;
:
Applying all 10 conditions against the BP attribute table, this gives us a complete list of territories that need to be processed based on the current BP attribute values.
Aside for proc_rel interested folks: This subset of BP values was from the delta list that had been modified since the last job run. This exercise above was to find new territories, existing territories can easily be found by looking at the current system TM relationships.
Take all the results, union them up and aggregate them before returning the result-set in the class method.
-- Put all these together
lt_terr_rule_acc_list = Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond1_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond2_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond3_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond4_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond5_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond6_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond7_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond8_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond9_terr_list
Union All
Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_cond10_terr_list;
-- once we determine there is a link between the territory, rule, attribute to partner account, we can aggregate here, drop the dups, actual values etc.
et_terr_rule_acc_list = Select partner_guid, territory_guid, rule_id, attr_id, rule_option
From :lt_terr_rule_acc_list
Group by partner_guid, territory_guid, rule_id, attr_id, rule_option;
I hope this info above may prove useful for any folks looking to process similar rule tables directly in AMDP. If anyone would like more code from the class methods, please ping me directly on SCN.
Okumaya devam et...