Useful SQL/Query - EBS Descriptive Flex Fields (DFF)

Here are a few SQLs to help working with Oracle EBS (E-Business Suite) Descriptive Flex Fields. You may have to make appropriate changes for your environment.

 

 

All custom DFFs - contexts, fields, values by module/application

 

SELECT APL.APPLICATION_ID, APL.APPLICATION_NAME,

 FDF.TITLE                             "DFF TITLE",

 FDF.CONTEXT_COLUMN_NAME               "CONTEXT COLUMN NAME",

 FDF.APPLICATION_TABLE_NAME            "APPLICATION TABLE",

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE   "DFF CONTEXT CODE",

 FUSG.COLUMN_SEQ_NUM                  "SEQUENCE",

 FUSG.END_USER_COLUMN_NAME            "SEGMENT NAME",

 FUSG.APPLICATION_COLUMN_NAME         "COLUMN NAME",

 FFV.FLEX_VALUE_SET_NAME               "VALUE SET NAME",

 FUSG.ENABLED_FLAG   -- , APL.*

FROM FND_DESCR_FLEX_COL_USAGE_VL FUSG, FND_DESCRIPTIVE_FLEXS_VL FDF, FND_FLEX_VALUE_SETS FFV, FND_APPLICATION_VL APL

WHERE FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID(+)

 -- AND FDF.TITLE = 'Employee Confirmation Date'  -- Replace this with Flex Field Title

 -- AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US'

 -- AND FUSG.ENABLED_FLAG = 'Y'

 AND FUSG.CREATED_BY >= 1000

 AND FDF.APPLICATION_ID = APL.APPLICATION_ID

 AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME

 AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID

ORDER BY APL.APPLICATION_NAME, FDF.TITLE, FUSG.DESCRIPTIVE_FLEXFIELD_NAME, FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE, FUSG.COLUMN_SEQ_NUM;

 

All DFF definitions in the system, includes lot of standard fields

This may be useful, if you are more comfortable to filter in Excel

 

SELECT FDF.APPLICATION_TABLE_NAME            "APPLICATION TABLE",

 FDF.TITLE                             "DFF TITLE",

 FDF.CONTEXT_COLUMN_NAME               "CONTEXT COLUMN NAME",

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE   "DFF CONTEXT CODE",

 FUSG.COLUMN_SEQ_NUM                  "SEQUENCE",

 FUSG.END_USER_COLUMN_NAME            "SEGMENT NAME",

 FUSG.APPLICATION_COLUMN_NAME         "COLUMN NAME",

 FFV.FLEX_VALUE_SET_NAME               "VALUE SET NAME",

 FUSG.ENABLED_FLAG

FROM FND_DESCR_FLEX_COL_USAGE_VL   FUSG, FND_DESCRIPTIVE_FLEXS_VL      FDF, FND_FLEX_VALUE_SETS           FFV

WHERE FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID(+)

-- AND FDF.TITLE = 'Employee Confirmation Date'  -- Replace this with Flex Field Title

-- AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US'

-- AND FUSG.ENABLED_FLAG = 'Y'

AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME

AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID

ORDER BY FDF.APPLICATION_TABLE_NAME, FDF.TITLE, FUSG.DESCRIPTIVE_FLEXFIELD_NAME, FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE, FUSG.COLUMN_SEQ_NUM;

 

 

 

 

Query to find DFF information

 

SELECT FDF.TITLE                             "DFF TITLE",

 FDF.APPLICATION_TABLE_NAME            "APPLICATION TABLE",

 FDF.CONTEXT_COLUMN_NAME               "CONTEXT COLUMN NAME",

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE   "DFF CONTEXT CODE",

 FUSG.COLUMN_SEQ_NUM                  "SEQUENCE",

 FUSG.END_USER_COLUMN_NAME            "SEGMENT NAME",

 FUSG.APPLICATION_COLUMN_NAME         "COLUMN NAME",

 FFV.FLEX_VALUE_SET_NAME               "VALUE SET NAME"

FROM FND_DESCR_FLEX_COL_USAGE_VL   FUSG,

 FND_DESCRIPTIVE_FLEXS_VL      FDF,

 FND_FLEX_VALUE_SETS           FFV

WHERE FDF.TITLE = 'Employee Confirmation Date'         -- Replace this with Flex Field Title

AND FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'US' 

AND FUSG.ENABLED_FLAG = 'Y'

AND FUSG.FLEX_VALUE_SET_ID = FFV.FLEX_VALUE_SET_ID

AND FUSG.DESCRIPTIVE_FLEXFIELD_NAME = FDF.DESCRIPTIVE_FLEXFIELD_NAME

AND FUSG.APPLICATION_ID = FDF.APPLICATION_ID

ORDER BY

 FUSG.DESCRIPTIVE_FLEXFIELD_NAME,

 FUSG.DESCRIPTIVE_FLEX_CONTEXT_CODE,

 FUSG.COLUMN_SEQ_NUM;

java.sql.SQLException: ORA-04068: existing state of packages has been discarded

 

Symptom/Problem/Error:

Three possible reasons for this error:

Cause 1: Object used within a session

If the error comes from the same Database session that created or altered the object, most likely this is due to incomplete transaction. To solve this issue COMMIT or ROLLBACK.

Cause 2: Another Session used Object

If the error comes from Oracle Client (SQL Plus, TOAD, SQL Developer, etc), another session either has incomplete transaction or current session has incomplete transaction.

Resolution Steps:

Commit session that has incomplete transaction

Make sure you disconnect each session that has used the package or have the session do a DBMS_SESSION.RESET_PACKAGE to reset the package state.

BEGIN

DBMS_SESSION.RESET_PACKAGE;

END;

/

Cause 3: The Object is used indirectly within Java Servlet

When a package is called from a Java Servlet, the same error may happen even if the proper steps are done

Resolution Steps:

Close current browsers & clients

Do steps mentioned in “Cause 2”

Abort and Restart OA Core Servers

{ echo <pwd> ; }| admanagedsrvctl.sh abort oacore_server1 @-nopromptmsg

{ echo <pwd> ; }| admanagedsrvctl.sh start oacore_server1 @-nopromptmsg

 

Keywords:

SQLException, ORA-04068, existing state of packages has been discarded, ASADMIN, Transaction, COMMIT, ROLLBACK