As always, and I know and many have said, preached never (funny is the saying) say never and add, always to that.
Well, the always part, I draw my motivation to write one of these from being inspired by catch-phrases from TV shows, and this one comes from 'House of cards' quite a depiction of deceitfulness and portrait portrayal of the American dream. That's not the point here, anyway.
The point here is just 'try something new'.
And I would humbly like to offer credit to the seed for the idea to be writing a blog entry, to my boss, whom I admire about how beautifully he reads what people want to do, and leading to bringing in the positive in them.
In our regular pursuit to live, we often forget to embrace the brightness of the unknown from which emerges the institution of innovativeness. Not a suggestion to view the following as a new discovery of sorts technically, but yes I want to perceive it to be, of an idea, to expect more from oneself,stretching (I first thought of calling it stretch, but I don't believe in stretches, just living the idea, so the strike off) by trying multiple avenues even after you have attained the basic task of surviving or merely servicing.
I would like to add, to express the idea, about Darwinian theory for the survival of the fittest brought more often than we would imagine having heard of, but don't you think life, intelligent (we call ourselves) life as we know, evolution took millions to billions of years before it could see one. I take 2 things out of this, one of which is the time. We as individuals are not at such liberty of, and second more important in my eye is the part where Darwin chose to state the process as 'fittest', not the best. Once I heard it as biologist explanation of evolution, that our bodies are not built perfect, but just how it is built works and does not break down on its own. We can always argue about flaws, potent to our lives with minimalistic disturbances, but that I see to serve to our advantage towards the point.
So make something that can withstand time and tide, does not crumble under its own burden, and also think about, during the process of building that can there be better ways to accomplishing the task better than in all conceivable way. Have you tried to test the offering to break it down, identify the limits, is it multi-fold better than what you have started with, and that you have not only fulfilled the requirement as mere completion of the task in the simplest of execution.
Now, I have recently encountered with the task, yes the article is being self-righteous, for which I am requesting for pardon, but I say so as to have had the first-hand experience off, not a bystander. Back to the task ...
Starting with the ask, which was in a technical space of 'Configure to Order' process, to generate item descriptions based on the components selected. Since my exposure and experience was in Oracle, thence the source. In Oracle, there already exists a provision (let's call it guideline by Oracle) to do this task, with a limitation i.e. a situation where multiple selections with similar attributes forming the assembly cannot all be represented in the description.
In other more technical terms, configured items' description is built based on descriptive elements shared between the assembly item and its components, but if there exists more than one component have different values for those elements, especially that could have for non-mutually exclusive options under an option class, only one value passes up to the assembly, which could appear to be random. The solution offered in standard Oracle, uses MAX to identify that single choice for common elements shared by component items.
The answer was simple, at least at first. That, let's use LISTAGG to represent all element values. And the answer is LISTAGG, it was only the journey which happened to be even more interesting and what it revealed. It would not only have to be just aggregate the list, but the list has to be unique too, it needs to be ordered (at least by some means) which I chose it to be dependent on components' item number in the assembly structure.
This essentially gave me the solution, after struggling for several hours to understand the intricate details of how Oracle have had build the solution (I am calling guideline, here). I admired for several hours before and still do, how in a single SQL UPDATE statement, all the intricacies of rolling up of element value weaving over base models' assembly structure, are built into. Additionally, how efficient the solutions' response for the database call is, which is only a few hundred milliseconds.
I could have stopped here, I didn't, partly because I am never ( ;) ) easily satisfied, or least always have a knack of trying to find the efficiency of what is getting built, which often helps and make me explore and learn. More importantly, the timing of the solution was least to say fairly high.
Even though I chose the same methodology as it was earlier built on, that of using a single UPDATE SQL statement to have solution run for, it was tremendously costly (up to 200 times), and of course, cost here is time to run each time for every configured item when the description is generated.
The SQL statement when running as SELECT, was fairly effective, offering the result in similar few hundred millisecond response, which goes like this ...
But the same, when embedded to work as UPDATE ran into several 10's of seconds, which was not acceptable to me. I don't shy away from asking for help, as I don't consider myself to possess deep technological know-how. Thus, extended my hand to ask for help to the technical team. Kept tossing and turning overnight and weekend trying to figure out what could possibly make it efficient.
Two reasons there, one even if I ask for someone to help, I can't usually give up to keep trying on my own as well, also many times technical teams are thinly built with more emphasis on time to delivery than on the quality. If it works it's out of the queue - isn't that sounds like the course of nature. This by no means is to mean any disregard to anyone efforts, it seems to me this is how the nature of the enterprise mostly have had become.
Here, goes the UPDATE statement -
During this tossing and turning part, I even led myself to believe that I was able to reduce the execution time down to original timeline (around '00 ms) when doing some changes while tossing and turning over (that too). Well, the bubble pretty soon (and I am glad, soon) blew on my face, and back to square one, with a long execution time, just that it was under 10 sec, but non-acceptable.
The thought that struck me was, if the SELECT is fast enough, why not to use it to the advantage and transform the solution, instead of single DML, let it be a combination of SQL and PLSQL solution.
It resulted into SELECTing with BULK COLLECT (added advantage does not hurt) and using FORALL to UPDATE the final result back to the database. Something like this ...
The part that I am still not able to comprehend is and will most certainly appreciate someone enlightening me for my mistake on, why a single DML statement running everything within the database was costly, far so many folds than using a PL-SQL combination which involves 'context-switching', so to speak.
Finally, to share the results, the net solution is 33% faster than before I started with the problem statement.
And the learning curve leading me to affirm my belief on, not all battles can be won by same kinda strategy. Every scenario is unique to be tried upon, and experience to be gained from and journey to be lived, but only if you aspire to do something new, challenge your boundaries and wanted to do away from your routine.
Well, the always part, I draw my motivation to write one of these from being inspired by catch-phrases from TV shows, and this one comes from 'House of cards' quite a depiction of deceitfulness and portrait portrayal of the American dream. That's not the point here, anyway.
The point here is just 'try something new'.
And I would humbly like to offer credit to the seed for the idea to be writing a blog entry, to my boss, whom I admire about how beautifully he reads what people want to do, and leading to bringing in the positive in them.
In our regular pursuit to live, we often forget to embrace the brightness of the unknown from which emerges the institution of innovativeness. Not a suggestion to view the following as a new discovery of sorts technically, but yes I want to perceive it to be, of an idea, to expect more from oneself,
I would like to add, to express the idea, about Darwinian theory for the survival of the fittest brought more often than we would imagine having heard of, but don't you think life, intelligent (we call ourselves) life as we know, evolution took millions to billions of years before it could see one. I take 2 things out of this, one of which is the time. We as individuals are not at such liberty of, and second more important in my eye is the part where Darwin chose to state the process as 'fittest', not the best. Once I heard it as biologist explanation of evolution, that our bodies are not built perfect, but just how it is built works and does not break down on its own. We can always argue about flaws, potent to our lives with minimalistic disturbances, but that I see to serve to our advantage towards the point.
So make something that can withstand time and tide, does not crumble under its own burden, and also think about, during the process of building that can there be better ways to accomplishing the task better than in all conceivable way. Have you tried to test the offering to break it down, identify the limits, is it multi-fold better than what you have started with, and that you have not only fulfilled the requirement as mere completion of the task in the simplest of execution.
Now, I have recently encountered with the task, yes the article is being self-righteous, for which I am requesting for pardon, but I say so as to have had the first-hand experience off, not a bystander. Back to the task ...
Starting with the ask, which was in a technical space of 'Configure to Order' process, to generate item descriptions based on the components selected. Since my exposure and experience was in Oracle, thence the source. In Oracle, there already exists a provision (let's call it guideline by Oracle) to do this task, with a limitation i.e. a situation where multiple selections with similar attributes forming the assembly cannot all be represented in the description.
In other more technical terms, configured items' description is built based on descriptive elements shared between the assembly item and its components, but if there exists more than one component have different values for those elements, especially that could have for non-mutually exclusive options under an option class, only one value passes up to the assembly, which could appear to be random. The solution offered in standard Oracle, uses MAX to identify that single choice for common elements shared by component items.
The answer was simple, at least at first. That, let's use LISTAGG to represent all element values. And the answer is LISTAGG, it was only the journey which happened to be even more interesting and what it revealed. It would not only have to be just aggregate the list, but the list has to be unique too, it needs to be ordered (at least by some means) which I chose it to be dependent on components' item number in the assembly structure.
This essentially gave me the solution, after struggling for several hours to understand the intricate details of how Oracle have had build the solution (I am calling guideline, here). I admired for several hours before and still do, how in a single SQL UPDATE statement, all the intricacies of rolling up of element value weaving over base models' assembly structure, are built into. Additionally, how efficient the solutions' response for the database call is, which is only a few hundred milliseconds.
I could have stopped here, I didn't, partly because I am never ( ;) ) easily satisfied, or least always have a knack of trying to find the efficiency of what is getting built, which often helps and make me explore and learn. More importantly, the timing of the solution was least to say fairly high.
Even though I chose the same methodology as it was earlier built on, that of using a single UPDATE SQL statement to have solution run for, it was tremendously costly (up to 200 times), and of course, cost here is time to run each time for every configured item when the description is generated.
The SQL statement when running as SELECT, was fairly effective, offering the result in similar few hundred millisecond response, which goes like this ...
select
cat_grp,
mdev_assembly_ele_seq,
mdev_comp_ele_name,
Decode (
ele_aggr,
0, MAX (ele_val_applied),
LISTAGG (ele_val_applied, '.')
WITHIN GROUP (order by mdev_assembly_ele_seq)
) ele_val,
(LISTAGG (ele_val_applied, '.')
WITHIN GROUP (order by mdev_assembly_ele_seq)
) future_val,
MAX (ele_val_applied) current_val,
config_desc
from (
Select distinct
de_regEx.segment1 cat_grp,
mdev_assembly.element_sequence mdev_assembly_ele_seq,
bbom.assembly_item_id cfg_bom_assembly_item_id,
mdev_comp.element_name mdev_comp_ele_name,
nvl( regexp_count (
cg_assembly.attribute3,
'((,|^)'|| mdev_comp.element_name ||'(,|$)|^\[ALL\]$)'
),
0 ) ele_aggr ,
--mdev_comp.element_value ele_val_current,
DECODE (mdev_comp.element_name,
de_regEx.ele_name,
REGEXP_REPLACE (
mdev_comp.element_value
, de_regEx.pattern, de_regEx.replacement_string
) ,
mdev_comp.element_value
) ele_val_applied,
--de_regEx.pattern,
--de_regEx.replacement_string,
--de_regEx.group_element,
--de_regEx.match_element,
--de_regEx.item_catalog_group_id,
msib_assembly.description config_desc,
null
From
bom_bill_of_materials bbom, --- bi
mtl_system_items_b msib_assembly,
mtl_item_catalog_groups_b cg_assembly,
bom_inventory_components bic, --- bc1
mtl_descr_element_values mdev_comp, --- v
mtl_descr_element_values mdev_assembly, ---- i
bom_inventory_components bic_m_cls, --- bc2
bom_bill_of_materials bbom_m_cls, --- bi2
bom_dependent_desc_elements bdde_cls, --- be
(select
regexp_replace (flv.meaning, '(.*)\.(.*)', '\2' ) match_element,
micgb.item_catalog_group_id,
msib.inventory_item_id,
micgb.segment1,
flv.meaning group_element,
flv.description pattern,
flv.tag replacement_string,
regexp_replace (flv.lookup_code, '#*[[:digit:]]*', '' ) ele_name
from
fnd_lookup_values flv,
mtl_item_catalog_groups_b micgb,
mtl_system_items_b msib
where 1=1
and flv.lookup_type = 'XYZ_LOOKUP'
and sysdate between NVL(flv.start_date_active, sysdate)
and nvl(flv.end_date_active, sysdate)
and flv.enabled_flag = 'Y'
and (
(
regexp_replace (flv.meaning, '((.*)\.(.*)|(^\.(.*)))' , '\4' )
is null
and micgb.segment1 like
regexp_replace (flv.meaning, '((.*)\.(.*)|(^\.(.*)))', '\2' )
) Or
(
regexp_replace (flv.meaning, '((.*)\.(.*)|(^\.(.*)))' , '\4' )
is not null
)
)
and msib.item_catalog_group_id = micgb.item_catalog_group_id
and msib.organization_id = p_master_org
) de_regEx,
dual
where 1=1
and bbom.assembly_item_id = p_item_id
and bbom.organization_id = p_org_id
and bbom.assembly_item_id = msib_assembly.inventory_item_id
and bbom.organization_id = msib_assembly.organization_id
and msib_assembly.item_catalog_group_id = cg_assembly.item_catalog_group_id
and mdev_assembly.inventory_item_id = de_regEx.inventory_item_id (+)
and mdev_assembly.element_name = de_regEx.match_element (+)
and bbom.alternate_bom_Designator IS NULL
and bbom.source_bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = mdev_comp.inventory_item_id
and bbom.assembly_item_id = mdev_assembly.inventory_item_id
and mdev_comp.element_name = mdev_assembly.element_name
and ABS(bic.model_comp_seq_id) = bic_m_cls.component_sequence_id
and bic_m_cls.bill_sequence_id = bbom_m_cls.common_bill_sequence_id
and bbom.organization_id = bbom_m_cls.organization_id
and bbom_m_cls.source_bill_sequence_id = bdde_cls.bill_sequence_id
and bic_m_cls.bill_sequence_id = bdde_cls.bill_sequence_id
and mdev_comp.element_name = bdde_cls.element_name
)
Group by
cat_grp,
mdev_comp_ele_name,
mdev_assembly_ele_seq,
ele_aggr,
config_desc
order by config_desc, mdev_assembly_ele_seq
But the same, when embedded to work as UPDATE ran into several 10's of seconds, which was not acceptable to me. I don't shy away from asking for help, as I don't consider myself to possess deep technological know-how. Thus, extended my hand to ask for help to the technical team. Kept tossing and turning overnight and weekend trying to figure out what could possibly make it efficient.
Two reasons there, one even if I ask for someone to help, I can't usually give up to keep trying on my own as well, also many times technical teams are thinly built with more emphasis on time to delivery than on the quality. If it works it's out of the queue - isn't that sounds like the course of nature. This by no means is to mean any disregard to anyone efforts, it seems to me this is how the nature of the enterprise mostly have had become.
Here, goes the UPDATE statement -
UPDATE MTL_DESCR_ELEMENT_VALUES i
SET i.element_value = (
select
NVL(
decode (
ele_aggr,
0, MAX (ele_val_applied),
LISTAGG (ele_val_applied, '.')
WITHIN GROUP (order by mx_bic_item_num)
)
, i.element_value)
ele_val
from (
select distinct
max(bic.item_num) mx_bic_item_num,
...
...
...
and mdev_comp.element_name = bdde_cls.element_name
Group by
mdev_assembly.element_name,
nvl( regexp_count (
cg_assembly.attribute3,
'((,|^)'|| mdev_comp.element_name ||'(,|$)|^\[ALL\]$)'
),
0 ),
DECODE (mdev_comp.element_name,
de_regEx.ele_name,
REGEXP_REPLACE (
mdev_comp.element_value
, de_regEx.pattern, de_regEx.replacement_string
) ,
mdev_comp.element_value
)
)
where mdev_assembly_ele_name = i.element_name
Group by
mdev_assembly_ele_name,
ele_aggr
)
WHERE i.inventory_item_id = p_params.p_item_id;
During this tossing and turning part, I even led myself to believe that I was able to reduce the execution time down to original timeline (around '00 ms) when doing some changes while tossing and turning over (that too). Well, the bubble pretty soon (and I am glad, soon) blew on my face, and back to square one, with a long execution time, just that it was under 10 sec, but non-acceptable.
The thought that struck me was, if the SELECT is fast enough, why not to use it to the advantage and transform the solution, instead of single DML, let it be a combination of SQL and PLSQL solution.
It resulted into SELECTing with BULK COLLECT (added advantage does not hurt) and using FORALL to UPDATE the final result back to the database. Something like this ...
/*BULK COLLECT*/ select mdev_assembly_ele_name, decode ( ele_aggr, 0, MAX (ele_val_applied), LISTAGG (ele_val_applied, '.') WITHIN GROUP (order by mx_bic_item_num) ) ele_val BULK COLLECT INTO l_ConfigCompDEs from ( select distinct ... ... ... ) Group by mdev_assembly_ele_name, ele_aggr; /*Followed by FORALL*/ FORALL indx IN l_ConfigCompDEs.FIRST .. l_ConfigCompDEs.COUNT UPDATE MTL_DESCR_ELEMENT_VALUES i SET i.element_value = nvl(l_ConfigCompDEs (indx).ele_val, i.element_value) WHERE i.inventory_item_id = p_params.p_item_id AND i.element_name = l_ConfigCompDEs (indx).ele_name;
The part that I am still not able to comprehend is and will most certainly appreciate someone enlightening me for my mistake on, why a single DML statement running everything within the database was costly, far so many folds than using a PL-SQL combination which involves 'context-switching', so to speak.
Finally, to share the results, the net solution is 33% faster than before I started with the problem statement.
And the learning curve leading me to affirm my belief on, not all battles can be won by same kinda strategy. Every scenario is unique to be tried upon, and experience to be gained from and journey to be lived, but only if you aspire to do something new, challenge your boundaries and wanted to do away from your routine.
