Error While getting Max Levels During Reverse Population (Doc ID 1578199.1)

Last updated on AUGUST 09, 2024

Applies to:

Symptoms

When attempting to run reverse population for a product hierarchy the following error occurs.

ERROR

FSI_MESSAGE_LOG shows

160 52 10 Insert query INSERT INTO OFSA_IDT_ROLLUP SELECT 236213 SYS_NUM_ID , LEAF_NODE , ROWNUM , ADHM_MGMT.getSequenceNo(1, ADHM_MGMT.getTokenValueAt(NODE_PATH,1,'&')) Seq_1 , 0 Seq_2 , 0 Seq_3 , 0 Seq_4 , 0 Seq_5 , 0 Seq_6 , 0 Seq_7 , 0 Seq_8 , 0 Seq_9 , 0 Seq_10 , 0 Seq_11 , 0 Seq_12 , 0 Seq_13 , 0 Seq_14 , ADHM_MGMT.getTokenValueAt(NODE_PATH,1,'&') NODE_1, -99001 NODE_2, -99001 NODE_3, -99001 NODE_4, -99001 NODE_5, -99001 NODE_6, -99001 NODE_7, -99001 NODE_8, -99001 NODE_9, -99001 NODE_10, -99001 NODE_11, -99001 NODE_12, -99001 NODE_13, -99001 NODE_14 from (SELECT ADHM_MGMT.FLATTENHIERARCHY (NODES_PATH, 2,lvl,'&',leaf_node) NODE_PATH , leaf_node from (SELECT LEVEL LVL, SYS_CONNECT_BY_PATH(CHILD_ID,'&' )NODES_PATH , CONNECT_BY_ISLEAF ISLEAF , CHILD_ID LEAF_NODE FROM (select DECODE(PARENT_ID, CHILD_ID, NULL, PARENT_ID ) PARENT_ID, CHILD_ID , display_order_num from DIM_PRODUCTS_HIER aa where hierarchy_id IN (236213) and single_depth_flag = 'Y')START WITH PARENT_ID IS NULL CONNECT BY PRIOR CHILD_ID = PARENT_ID order siblings by display_order_num ) aa , DIM_PRODUCTS_B bb where aa.leaf_node = bb.PRODUCT_ID and bb.leaf_only_flag = 'Y' and isleaf = 1 and bb.definition_language in (SELECT DECODE(installed_flg,1,mls_cd,'US') FROM fsi_mls WHERE mls_cd=USERENV('LANG'))) 1/31/2013 12:01:00
160 52 10 OFSA Hierarchies Count = 12 1/31/2013 12:01:00
160 53 10 Mis-match in Hierarchy Count  1/31/2013 12:01:00
160 53 10 Inserted 1 rows into OFSA_IDT_ROLLUP 1/31/2013 12:01:00
160 54 10 INSERTED 4253 Orphan Nodes 1/31/2013 12:01:00
160 55 30 Error. While getting max levels  1/31/2013 12:01:00



STEPS
1) Run Reverse Population using parameters: 4,null,2 (4 is product dimension id, null is to run for all, and 2 is to run in update vs. new insert mode)

2) Hierarchy with 236213 did not get inserted into OFSA_IDT_ROLLUP table

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.