Why do we care about technicality ...
I would like to classify the definition for the work in two contexts.
Going to a school to acquire skill, does that come down to people seeing it as a stamp for skillfulness. It's complicated than of how could we measure people's skill to their being suitable for being capable of conducting a job.
People often say and want to be 'not judge'-d, then what could possibly be a way we find of the measure of skills, keeping aside certifiability of an institution being appropriate for, of the measure.
Can we say does that, or can we bring it down to findings of newer means of assessing someone's capability leaving behind the need to carrying certificate ?
Prejudice - maybe it is our prejudice of our association to ... institutions, the emotion bond, after all being human is commonly expressed as about of emotional being ...
Phrase "love" in many ways may be overrated, well let's say when they are being explicit stated only, or even of the want to be listened to. This reminds of a read - if you like a flower you might take it away from its roots when you love it, you would nurture it without in ITS entirety of acceptance in return.
People getting caught up on the things that are going to happen to them, rather than perseverance on the continuance of our job - someone (would have) said 'life goes on', definitely without us, so shall we as well as our jobs / action shall continue our existence and not referencing only about survival, and honestly just while writing this, I felt existence does carry both meanings - to be godly and being evil. Those are the extremes of classifications to action one conducts', but as pretty much every culture talked about balances and co-existences of the yin-and-yang of nature, and we so 'being' being part of it so do we.
I watch too much TV - another reference I would mention is of 'Suits' is what attributed to this expression.
And the other important background and more so the task of why here ! is an event that I recently been through of completing (accomplishing / achieving) a task of investigating of facts to establish sanctity of the similarity, and I like to put it as findings 'absence of' differences (IF ANY), by my nature whatsoever there may be quantified more precisely.
And it is this task search help me learn, a little trick with "Connect by" prior.
The task was, or rather can be if simply broken to pieces, of where there was a need to have "Bill of Materials" structures' hierarchically, well this is the simple part (saying simple because I would invariably find a straight answer over the net to this for specific tree for a single organization), well I can bet and consider myself egotistical (not wanting to say pride - this is because I want someone to point to me an existing article solving bill-of-material for multiple organizations) in the complexity arising from it, where organization is not predetermined and source of input, let's say the primary initiating factor would provide with to sequence a few such hierarchies as a single output - and then to be used or join with another factor for achieving the final job.
After I learned it now, could say the answer being quite simple, maybe for some sound to be off in plain sight. A point, I want to emphasize could not find a single article providing the direct answer hence, here is it to offer all of it one place -
In CONNECT BY, PRIOR phrase can be used as many times as you want - here is how:
Since the building block of actual answer is having defined -
first each single assembly, and then running that structure to construct a hierarchy.
This is to achieve when there is a need for getting the hierarchy of all organization being prevented from intermingling.
The query for the complete and all bill of materials for all organizations - along with some of the nice and interesting enough keyword that is handy with connect by.
I also expressed that the initiating criteria contain the decision for the organization and consists of which (items') hierarchies to be built and that initiating factor in itself posed certain challenges of maintaining the hierarchy clean - for me it was sales order containing a configured model comprising of several ATO and PTO structures.
Some of the interesting finds for me were of OPTION_NUMBER and especially COMPONENT_NUMBER, and it optionally being populated, hence the need of an outer join.
Enjoy and keep sharing... The point where I asked for existing article on this same solution is with a pinch of salt, but my ask from the community is to teach me - how to perform better searches for faster answers.
I would like to classify the definition for the work in two contexts.
Going to a school to acquire skill, does that come down to people seeing it as a stamp for skillfulness. It's complicated than of how could we measure people's skill to their being suitable for being capable of conducting a job.
People often say and want to be 'not judge'-d, then what could possibly be a way we find of the measure of skills, keeping aside certifiability of an institution being appropriate for, of the measure.
Can we say does that, or can we bring it down to findings of newer means of assessing someone's capability leaving behind the need to carrying certificate ?
Prejudice - maybe it is our prejudice of our association to ... institutions, the emotion bond, after all being human is commonly expressed as about of emotional being ...
Phrase "love" in many ways may be overrated, well let's say when they are being explicit stated only, or even of the want to be listened to. This reminds of a read - if you like a flower you might take it away from its roots when you love it, you would nurture it without in ITS entirety of acceptance in return.
People getting caught up on the things that are going to happen to them, rather than perseverance on the continuance of our job - someone (would have) said 'life goes on', definitely without us, so shall we as well as our jobs / action shall continue our existence and not referencing only about survival, and honestly just while writing this, I felt existence does carry both meanings - to be godly and being evil. Those are the extremes of classifications to action one conducts', but as pretty much every culture talked about balances and co-existences of the yin-and-yang of nature, and we so 'being' being part of it so do we.
I watch too much TV - another reference I would mention is of 'Suits' is what attributed to this expression.
And the other important background and more so the task of why here ! is an event that I recently been through of completing (
And it is this task search help me learn, a little trick with "Connect by" prior.
The task was, or rather can be if simply broken to pieces, of where there was a need to have "Bill of Materials" structures' hierarchically, well this is the simple part (saying simple because I would invariably find a straight answer over the net to this for specific tree for a single organization), well I can bet and consider myself egotistical (not wanting to say pride - this is because I want someone to point to me an existing article solving bill-of-material for multiple organizations) in the complexity arising from it, where organization is not predetermined and source of input, let's say the primary initiating factor would provide with to sequence a few such hierarchies as a single output - and then to be used or join with another factor for achieving the final job.
After I learned it now, could say the answer being quite simple, maybe for some sound to be off in plain sight. A point, I want to emphasize could not find a single article providing the direct answer hence, here is it to offer all of it one place -
In CONNECT BY, PRIOR phrase can be used as many times as you want - here is how:
CONNECT BY PRIOR ASMBLY.COMPONENT_ITEM_ID = ASMBLY.ASSEMBLY_ITEM_ID
AND PRIOR ASMBLY.ORGANIZATION_ID = ASMBLY.ORGANIZATION_ID
Since the building block of actual answer is having defined -
first each single assembly, and then running that structure to construct a hierarchy.
This is to achieve when there is a need for getting the hierarchy of all organization being prevented from intermingling.
The query for the complete and all bill of materials for all organizations - along with some of the nice and interesting enough keyword that is handy with connect by.
SELECT
LEVEL BILL_LEVEL,
NVL(MODL_ITEM,COMPITEM)MODEL_TREE_REF,
ASMBLY.*,
CONNECT_BY_ROOT ASMBLYITEM ROOT,
CONNECT_BY_ROOT ASSEMBLY_ITEM_ID ROOT_ID,
SYS_CONNECT_BY_PATH(LPAD(ITEM_NUM,5,'0'), ' ') ITEM_NUM_PATH,
CONNECT_BY_ROOT ASLY_MODL_ITEM || SYS_CONNECT_BY_PATH(COMPITEM, '~') COMP_PATH,
SYS_CONNECT_BY_PATH(NVL(BASE_ITEM_ID,COMPONENT_ITEM_ID), '~') ITEM_ID_PATH,
ROWNUM BILL_TREE_SEQ
FROM (
SELECT
MP.ORGANIZATION_CODE,
MSIB_ASLY.SEGMENT1 ASMBLYITEM,
COMP.SEGMENT1 COMPITEM,
BIC.COMPONENT_QUANTITY,
BIC.COMPONENT_YIELD_FACTOR,
COMP.DESCRIPTION COMPDESC,
COMP.ITEM_TYPE,
COMP.INVENTORY_ITEM_STATUS_CODE COMP_STATUS,
COMP.BOM_ITEM_TYPE,
BIC.ITEM_NUM,
BIC.OPERATION_SEQ_NUM,
BIC.CHANGE_NOTICE,
BBOM.ORGANIZATION_ID,
COMP.BASE_ITEM_ID,
MODL.SEGMENT1 MODL_ITEM,
ASLY_MODL.SEGMENT1 ASLY_MODL_ITEM,
BBOM.ASSEMBLY_ITEM_ID,
BIC.COMPONENT_ITEM_ID,
BIC.DISABLE_DATE,
BBOM.CREATION_DATE
FROM
BOM_BILL_OF_MATERIALS BBOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS_B MSIB_ASLY,
MTL_SYSTEM_ITEMS_B COMP,
MTL_SYSTEM_ITEMS_B MODL,
MTL_SYSTEM_ITEMS_B ASLY_MODL,
MTL_PARAMETERS MP
WHERE 1=1
AND BBOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BIC.DISABLE_DATE IS NULL /*Effective components only*/
AND BBOM.ASSEMBLY_ITEM_ID = MSIB_ASLY.INVENTORY_ITEM_ID
AND BBOM.ORGANIZATION_ID = MSIB_ASLY.ORGANIZATION_ID
AND MSIB_ASLY.ORGANIZATION_ID = COMP.ORGANIZATION_ID
AND BIC.COMPONENT_ITEM_ID = COMP.INVENTORY_ITEM_ID
AND COMP.ORGANIZATION_ID = MODL.ORGANIZATION_ID (+)
AND COMP.BASE_ITEM_ID = MODL.INVENTORY_ITEM_ID (+)
AND MSIB_ASLY.ORGANIZATION_ID = ASLY_MODL.ORGANIZATION_ID (+)
AND MSIB_ASLY.BASE_ITEM_ID = ASLY_MODL.INVENTORY_ITEM_ID (+)
AND MSIB_ASLY.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND COMP.BOM_ITEM_TYPE = 4
) ASMBLY
START WITH ASMBLY.ASSEMBLY_ITEM_ID = :ITEM
CONNECT BY PRIOR ASMBLY.COMPONENT_ITEM_ID = ASMBLY.ASSEMBLY_ITEM_ID
AND PRIOR ASMBLY.ORGANIZATION_ID = ASMBLY.ORGANIZATION_ID
I also expressed that the initiating criteria contain the decision for the organization and consists of which (items') hierarchies to be built and that initiating factor in itself posed certain challenges of maintaining the hierarchy clean - for me it was sales order containing a configured model comprising of several ATO and PTO structures.
WITH ORD_LINES AS
(
SELECT
OEL.LINE_NUMBER,
OEL.OPTION_NUMBER,
OEL.COMPONENT_NUMBER,
OEL.ORDERED_ITEM,
OEL.ORDERED_QUANTITY,
OEL.INVENTORY_ITEM_ID,
OEL.ITEM_TYPE_CODE,
MSIB.ITEM_TYPE,
OEL.SHIPPABLE_FLAG,
OEL.FLOW_STATUS_CODE,
OEL.SOURCE_TYPE_CODE,
OEL.SHIP_FROM_ORG_ID,
OEL.TOP_MODEL_LINE_ID,
MDL_OEL.ATTRIBUTE1 CONVEYOR_NUM,
MDL_OEL.ORDERED_ITEM TOP_MODEL_ITEM,
NULL
FROM
OE_ORDER_LINES_ALL OEL,
OE_ORDER_LINES_ALL MDL_OEL,
MTL_SYSTEM_ITEMS_B MSIB
WHERE 1=1
AND OEL.TOP_MODEL_LINE_ID = MDL_OEL.LINE_ID
AND OEL.SHIP_FROM_ORG_ID = MSIB.ORGANIZATION_ID
AND OEL.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND OEL.SHIPPABLE_FLAG = 'Y'
AND OEL.HEADER_ID = :HDR
AND OEL.LINE_NUMBER = 1
ORDER BY
OEL.LINE_NUMBER,
OEL.OPTION_NUMBER,
NULL
),
BILL_STRUCT AS
(
SELECT
LEVEL BILL_LEVEL,
NVL(MODL_ITEM,COMPITEM)MODEL_TREE_REF,
ASMBLY.*,
CONNECT_BY_ROOT ASMBLYITEM ROOT,
CONNECT_BY_ROOT ASSEMBLY_ITEM_ID ROOT_ID,
SYS_CONNECT_BY_PATH(LPAD(ITEM_NUM,5,'0'), ' ') ITEM_NUM_PATH,
CONNECT_BY_ROOT ASLY_MODL_ITEM || SYS_CONNECT_BY_PATH(COMPITEM, '~') COMP_PATH,
SYS_CONNECT_BY_PATH(NVL(BASE_ITEM_ID,COMPONENT_ITEM_ID), '~') ITEM_ID_PATH,
ROWNUM BILL_TREE_SEQ
FROM (
SELECT
MP.ORGANIZATION_CODE,
MSIB_ASLY.SEGMENT1 ASMBLYITEM,
COMP.SEGMENT1 COMPITEM,
BIC.COMPONENT_QUANTITY,
BIC.COMPONENT_YIELD_FACTOR,
COMP.DESCRIPTION COMPDESC,
COMP.ITEM_TYPE,
COMP.INVENTORY_ITEM_STATUS_CODE COMP_STATUS,
COMP.BOM_ITEM_TYPE,
BIC.ITEM_NUM,
BIC.OPERATION_SEQ_NUM,
BIC.CHANGE_NOTICE,
BBOM.ORGANIZATION_ID,
COMP.BASE_ITEM_ID,
MODL.SEGMENT1 MODL_ITEM,
ASLY_MODL.SEGMENT1 ASLY_MODL_ITEM,
BBOM.ASSEMBLY_ITEM_ID,
BIC.COMPONENT_ITEM_ID,
BIC.DISABLE_DATE,
BBOM.CREATION_DATE
FROM
BOM_BILL_OF_MATERIALS BBOM,
BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS_B MSIB_ASLY,
MTL_SYSTEM_ITEMS_B COMP,
MTL_SYSTEM_ITEMS_B MODL,
MTL_SYSTEM_ITEMS_B ASLY_MODL,
MTL_PARAMETERS MP
WHERE 1=1
AND BBOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BIC.DISABLE_DATE IS NULL /*Effective components only*/
AND BBOM.ASSEMBLY_ITEM_ID = MSIB_ASLY.INVENTORY_ITEM_ID
AND BBOM.ORGANIZATION_ID = MSIB_ASLY.ORGANIZATION_ID
AND MSIB_ASLY.ORGANIZATION_ID = COMP.ORGANIZATION_ID
AND BIC.COMPONENT_ITEM_ID = COMP.INVENTORY_ITEM_ID
AND COMP.ORGANIZATION_ID = MODL.ORGANIZATION_ID (+)
AND COMP.BASE_ITEM_ID = MODL.INVENTORY_ITEM_ID (+)
AND MSIB_ASLY.ORGANIZATION_ID = ASLY_MODL.ORGANIZATION_ID (+)
AND MSIB_ASLY.BASE_ITEM_ID = ASLY_MODL.INVENTORY_ITEM_ID (+)
AND MSIB_ASLY.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND COMP.BOM_ITEM_TYPE = 4
) ASMBLY,
ORD_LINES
WHERE 1=1
AND ASMBLY.ORGANIZATION_ID = ORD_LINES.SHIP_FROM_ORG_ID
START WITH ASMBLY.ASSEMBLY_ITEM_ID = ORD_LINES.INVENTORY_ITEM_ID
CONNECT BY PRIOR ASMBLY.COMPONENT_ITEM_ID = ASMBLY.ASSEMBLY_ITEM_ID
AND PRIOR ASMBLY.ORGANIZATION_ID = ASMBLY.ORGANIZATION_ID
AND PRIOR ORD_LINES.LINE_NUMBER = ORD_LINES.LINE_NUMBER
AND PRIOR ORD_LINES.OPTION_NUMBER = ORD_LINES.OPTION_NUMBER
AND PRIOR ORD_LINES.COMPONENT_NUMBER = ORD_LINES.COMPONENT_NUMBER (+)
ORDER SIBLINGS BY ASMBLY.ITEM_NUM
)
SELECT
ORD_LINES.*,
BILL_STRUCT.*
FROM
ORD_LINES, BILL_STRUCT
WHERE 1=1
AND ORD_LINES.INVENTORY_ITEM_ID = BILL_STRUCT.ROOT_ID (+)
AND ORD_LINES.SHIP_FROM_ORG_ID = BILL_STRUCT.ORGANIZATION_ID (+)
ORDER BY LINE_NUMBER, OPTION_NUMBER , ITEM_NUM_PATH
Some of the interesting finds for me were of OPTION_NUMBER and especially COMPONENT_NUMBER, and it optionally being populated, hence the need of an outer join.
Enjoy and keep sharing... The point where I asked for existing article on this same solution is with a pinch of salt, but my ask from the community is to teach me - how to perform better searches for faster answers.
