Method "Load Scenario Data" Fails With Database internal error: ORA-20002:- 20003 ORA-20003: Error in PROCEDURE CREATE_INTEG_MISSING_COMBS line 896 executing ACTIVE_PROC_DYN('CREATE_COMBS_INT_397') (Doc ID 1463098.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Demantra Real-Time Sales and Operations Planning - Version 7.3.1 and later
Information in this document applies to any platform.

Symptoms

On : 7.3.1.1 version, LoadData

When attempting to run the method "Load Scenario Data" from the Demantra SnOP dashboard, it fails and throws an error "Database internal error: ORA-20002:- 20003 ORA-20003: Error in PROCEDURE CREATE_INTEG_MISSING_COMBS line 896 executing ACTIVE_PROC_DYN('CREATE_COMBS_INT_397')"

db_exception_log shows:
"-904 ORA-00904: ""LEVEL6"": invalid identifier Error in PROCEDURE CREATE_COMBS_INT_397 line 18

  dynamic_ddl( 'create table BIIO_PFLE397_ALL as select level2,l2.t_ep_item_EP_ID,level5,l5.t_ep_ebs_demand_class_EP_ID,level3,l3.t_ep_organization_EP_ID,level4,l4.t_ep_site_EP_ID,level6,l6.t_ep_ebs_sales_ch_EP_ID
  from (select distinct level2,level5,level3,level4,level6 from BIIO_Other_Plan_Data) bio ,t_ep_item l2,t_ep_ebs_demand_class l5,t_ep_organization l3,t_ep_site l4,t_ep_ebs_sales_ch l6
  where bio.level2 =l2.item
  and bio.level5 =l5.ebs_demand_class
  and bio.level3 =l3.organization
  and bio.level4 =l4.site
  and bio.level6 =l6.ebs_sales_ch AND NOT EXISTS (SELECT 1 FROM mdp_matrix WHERE mdp_matrix.t_ep_item_EP_ID = l2.t_ep_item_EP_ID AND mdp_matrix.t_ep_ebs_demand_class_EP_ID = l5.t_ep_ebs_demand_class_EP_ID AND mdp_matrix.t_ep_organization_EP_ID = l3.t_ep_organization_EP_ID AND mdp_matrix.t_ep_site_EP_ID = l4.t_ep_site_EP_ID AND mdp_matrix.t_ep_ebs_sales_ch_EP_ID = l6.t_ep_ebs_sales_ch_EP_ID ) ')"



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. run the method "Load Scenario Data" from the Demantra SnOP dashboard



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms