BOM explosion

[patrickbachmann]

Hi folks and cyborgs,

Has anybody simulated logic of BOM explosion similar to function module CS_BOM_EXPL_MAT_V2 in a HANA procedure?  I'm being asked if I could possibly recreate a particular abap program inside HANA and I've been digging through the abap and realize the program is fairly robust (no surprise) and uses a couple standard function modules including BOM explosion and unit conversion.  Of course as soon as this reality comes to light the ABAP resource says it does not make sense to recreate function modules in HANA and perhaps not in this case but I think it depends on what we are trying to do and just how complex the issue, the pro's and cons etc.  Anyway I am just wondering if anybody has done this?  Not asking for you to write the code for me.  I'm just curious and it will help me decide if I want to pursue this challenge or seek thrills elsewhere.  And please notice that I have used no unnecessary exclamation points in this request so be nice to me.

Thanks,

-Patrick

[lbreddemann · Accepted solution]

Hmm.. ok, I see two options for that.

One would require SQLScript recursion don't know how to do that, so not a winner.

The other would use hierarchy views with multiple parents.

Pity for you is that these hierarchy views are not officially released outside SAP.

However, the following example may still be interesting:

drop table boms;

create column table BOMS (node_id integer, parent_material varchar(30), child_material varchar(30));

truncate table boms;

INSERT INTO BOMS VALUES ( 10, null, 'CAR A1' );

INSERT INTO BOMS VALUES ( 20, 'CAR A1', 'TYRE 1');

INSERT INTO BOMS VALUES ( 21, 'TYRE 1', 'TYRE TYPE X11');

INSERT INTO BOMS VALUES ( 30, 'CAR A1', 'TYRE 2');

INSERT INTO BOMS VALUES ( 31, 'TYRE 2', 'TYRE TYPE X11');

INSERT INTO BOMS VALUES ( 40, 'CAR A1', 'TYRE 3');

INSERT INTO BOMS VALUES ( 41, 'TYRE 3', 'TYRE TYPE X11');

INSERT INTO BOMS VALUES ( 50, 'CAR A1', 'TYRE 4');

INSERT INTO BOMS VALUES ( 51, 'TYRE 4', 'TYRE TYPE X11');

INSERT INTO BOMS VALUES ( 60, 'CAR A1', 'BOOT');

INSERT INTO BOMS VALUES ( 70, 'BOOT', 'SPARE TYRE');

INSERT INTO BOMS VALUES ( 71, 'SPARE TYRE', 'TYRE TYPE X11');

INSERT INTO BOMS VALUES (100, NULL, 'MOONFACE');

INSERT INTO BOMS VALUES (200, 'MOONFACE', 'DOT 1');

INSERT INTO BOMS VALUES (201, 'DOT 1', 'DOT');

INSERT INTO BOMS VALUES (300, 'MOONFACE', 'DOT 2');

INSERT INTO BOMS VALUES (301, 'DOT 2', 'DOT');

INSERT INTO BOMS VALUES (400, 'MOONFACE', 'COMMA');

INSERT INTO BOMS VALUES (500, 'MOONFACE', 'LINE');

select * from boms

drop view h_boms;

-- hierarchy definition generally allowing multiple parents --

CREATE COLUMN VIEW h_boms TYPE HIERARCHY WITH PARAMETERS (

    'hierarchyDefinition' = '{

        "sourceType":"recursive",

        "nodeType":"string",

        "runtimeObjectType":"tree",

        "multiParent":true,

        "sourceQuery":"SELECT parent_material as pred, child_material as succ FROM boms" }' );

select * from h_boms;

-- what makes a moonface ?

SELECT level, result_node FROM h_boms ( 'expression'='Leaves("MOONFACE")' );

LEVEL	RESULT_NODE
2	DOT
2	DOT
1	COMMA
1	LINE

-- hierarchy of the car

SELECT level, rpad('+', level*2, '-') || result_node as tree

FROM h_boms( 'expression'='subtree("CAR A1")' );

LEVEL	TREE
0	CAR A1
1	+-TYRE 1
2	+---TYRE TYPE X11
1	+-TYRE 2
2	+---TYRE TYPE X11
1	+-TYRE 3
2	+---TYRE TYPE X11
1	+-TYRE 4
2	+---TYRE TYPE X11
1	+-BOOT
2	+---SPARE TYRE
3	+-----TYRE TYPE X11

-- get me the list of base material for the car

select sum (is_leaf) as "ITEM cnt", result_node from h_boms  ( 'expression'='descendants("CAR A1")' )

group by result_node

order by result_node;

ITEM cnt	RESULT_NODE
0	BOOT
0	SPARE TYRE
0	TYRE 1
0	TYRE 2
0	TYRE 3
0	TYRE 4
5	TYRE TYPE X11

As I wrote above: I don't see that in any of the official documentation, so as long as you don't do something like a co-innovation project or similar stuff then I assume, you cannot really use this.

Cheers,

Lars

[patrickbachmann · Accepted solution]

Interesting indeed, thanks for your reply Lars.  Although my mortal brain will take a few minutes to absorb this.  I was beginning to get a complex.  I mention BOM and explosions in the same sentence and suddenly everybody scatters with tail between legs....

[patrickbachmann · Accepted solution]

Hey it's now been several minutes and I actually understand your example! (note excellently placed single exclamation) Light bulb moment in my head is Tyre = Tire.  Now I see a car bom!  Again everyone scatters.  Suddenly your example makes complete sense! (again notice perfect punctuation!  It's uncanny!)    Now to rest my brain for 2 days... many thanks my friend.

[lbreddemann · Accepted solution]

Cheers, mate.

Enjoy the weekend!

[abhijnansaha]

Hello Patrick,

BOM Explosion on HANA is a delivered module in EHP7. It incorporates all functionalities of Standard ECC BOM Explosion with any level of complexity that you can think of.

You can get an idea of the modules below:

Thanks and Regards,

Abhijnan

[Former Member]

Hi Patrick,

Did you ever implement this to a working state with BOM? I have exactly the same requirement and I'm starting to think an ABAP report in ERP may be the way to go due to complexity.

Thanks!

Chris.

[patrickbachmann]

Hi Chris,

Unfortunately we did not pursue this project so I don't have a working example for you.  For now at least.

-Patrick