ORA-00957: Duplicate Column Name Error on Line Item Attribute Transformation (Doc ID 1078905.1)

Last updated on SEPTEMBER 16, 2016

Applies to:

Oracle Profitability Manager - Version 11.5.10 to 12.0.6 [Release 11.5 to 12]
Oracle Enterprise Performance Foundation - Version 11.5.10 to 12.0.6 [Release 11.5 to 12]
Information in this document applies to any platform.
Corporate Performance Management Intelligence (CPMI)

Symptoms

In Corporate Performance Management Intelligence (CPMI) 11i or R12, the "Program - Enterprise Performance Foundation Dimension Attribute Transformation Engine" program ends with an error for the Line Item dimension.  The following error appears in the View Log:

FEM_BI_DIMENSION_UTILS_PKG.Run_Transformation started at 01-JAN-10 07:51:23
FEM_BI_DIMENSION_UTILS_PKG.Transformation.LINE_ITEM started at 01-JAN-10 07:51:23
FEM_BI_DIMENSION_UTILS_PKG.FEM_BI_DIMENSION_UTILS_PKG.Get_Dim_Attribute_Sql.FEM_LN_ITEMS_ATTR started at 01-JAN-10 07:51:23
FEM_BI_DIMENSION_UTILS_PKG.FEM_BI_DIMENSION_UTILS_PKG.Get_Dim_Attribute_Sql.FEM_LN_ITEMS_ATTR completed successfully at 01-JAN-10 07:51:23
FEM_BI_DIMENSION_UTILS_PKG.FEM_BI_DIMENSION_UTILS_PKG.Create_MV_Objects.FEM_LN_ITEMS_ATTRD started at 01-JAN-10 07:51:23
Unexpected error has occurred: ORA-00957: duplicate column name.
Please contact your system administrator.

FEM_BI_DIMENSION_UTILS_PKG.FEM_BI_DIMENSION_UTILS_PKG.Create_MV_Objects.FEM_LN_ITEMS_ATTRD completed with error at 01-JAN-10 07:51:23
FEM_BI_DIMENSION_UTILS_PKG.Transformation.LINE_ITEM completed with error at 01-JAN-10 07:51:23
FEM_BI_DIMENSION_UTILS_PKG.Run_Transformation completed with error at 01-JAN-10 07:51:23


The error prevents the user from re-creating the following CPMI objects:

FEM_LN_ITEMS_ATTRD_MV
FEM_LN_ITEMS_ATTRN_MV
FEM_LN_ITEMS_ATTRD_V
FEM_LN_ITEMS_ATTRN_V
FEM_LN_ITEMS_ATTRN_VL

Note: This issue originated when an end user tried to open an OBIEE report in CPMI and received a "table not found" error.  They then ran "Program - Enterprise Performance Foundation Dimension Attribute Transformation Engine" to try to re-create the missing objects.  The program dropped the existing views but failed to re-create them.

Steps to reproduce:

  1. Go to the Corporate Performance Management Intelligence responsibility
  2. Go to Requests > Submit
  3. Run "Program - Enterprise Performance Foundation Dimension Attribute Transformation Engine" for all dimensions or just the Line Item dimension.

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