My Oracle Support Banner

CTO Data (Bom Structure) Is Not Showing In Demantra Worksheet - ORA-00904: "T_EP_ITEM_ID": invalid identifier (Doc ID 1275168.1)

Last updated on DECEMBER 04, 2019

Applies to:

Oracle Demantra Demand Management - Version 7.3.0 and later
Information in this document applies to any platform.


On Demantra and VCP 12.1.3 ,

When attempting to load CTO, data is not available in demantra seeded worksheet.
the following error occurs.

java.sql.BatchUpdateException: error occurred during batching: ORA-00904: "T_EP_ITEM_ID": invalid identifier

The issue can be reproduced at will with the following steps:
1. Items are created in source with BOM
2. Sales Order are created for the item, scheduled and booked after selecting the different optional class.
2. Standard collection (ODS) is run for all default entities
The SOs are verified in msc tables. (select * from msc.MSC_SALES_ORDERS for INVENTORY_ITEM_ID). All items are also verified in ODS.
3. Run the “Collect Shipment and Booking History” . Staging tables T_SRC_ITEM_TMPL, T_SRC_LOC_TMPL and T_SRC_SALES_TMPL
4. To make sure data collected correctly, ran workflow EBS Full Download
but still CTO related data is not coming up in any seeded CTO tables
select * from T_EP_CTO_BASE_MODEL;
select * from T_EP_CTO_CHILD;
select * from T_EP_CTO_DATA;
select * from T_EP_CTO_DATES;
select * from T_EP_CTO_DEMAND_TYPE;
select * from T_EP_CTO_LEVELS;
select * from T_EP_CTO_MATRIX;
select * from T_EP_CTO_MEMBERS;
select * from T_EP_CTO_PARENT;
5. Checked the CTO structure in seeded worksheet CTO: Item Demand by BOM by week but it doesn’t show the structure
6. Then run the other CTO related flow one by one e.g.
Import CTO Level
Import CTO Child
Import CTO Option Price
Import CTO Base Model
Import CTO Data
CTO Upload Local Forecast - Org, Week
Calculate Dependent Demand

7. Then again checked, but there is any structure in the worksheet.
Profile options set
MSD_DEM: Include Dependent Demand =Yes
MSD_DEM: Calculate Planning Percentages:Yes, for all the Options and Option Classes
MSD_DEM: Explode Dependent Demand Method Type=Use organization specific Bill of Material

8. Checked the CTO staging tables and _err tables:
select * from BIIO_CTO_BASE_MODEL;
select * from BIIO_CTO_BASE_MODEL_ERR;
select * from BIIO_CTO_CHILD;
select * from BIIO_CTO_CHILD_ERR;
select * from BIIO_CTO_LEVEL;
select * from BIIO_CTO_LEVEL_ERR;
select * from BIIO_CTO_POPULATION;
select * from BIIO_CTO_DATA;
select * from BIIO_CTO_DATA_ERR;

[ODPM-00075: Invalid Member LEVEL1-CTO]

Checking BIIO_CTO_LEVEL_ERR got::
[ODPM-10416: Parent Level CTO Child, Data Column T_EP_CTO_CHILD_CODE, Error:ODPM-10418: Invalid Parent Member Value]

BIIO_CTO_CHILD_ERR has lots of records with:
java.sql.BatchUpdateException: error occurred during batching: ORA-00904: "T_EP_ITEM_ID": invalid identifier


To view full details, sign in with your My Oracle Support account.

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

In this Document

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