This Document will show you how to retrieve the information nedded when making SQL Predicates on Custom Objects.

You will learn the needed SQL Queries  to find out where custom fields and custom objects are stored in the database, tables and columns.

This Document will not teach you how to build SQL Predicates but how to get all the info you need form the database regarding custom object

Solution #

1 - Find out in what table is a custom object been stored. #

a) If the Object is in Sales Area in Application Composer

select *

from fusion.adf_extensible_table_usage

where ENTITYDEF_FULLNAME like '%customobject_api_name%'

b ) If the Object is in Common Area in Application Composer

select * from adf_extensible_table_usage

where ENTITYDEF_FULLNAME like '%customobject_api_name%'

c) Find the Table in which the custom object is held

select TABLE_NAME from fusion.adf_extensible_table where TABLE_ID = <Table Id from either (a) or (b)>

2 - Find out in what columns are custom fields that are on a custom object #

SELECT T.*, CU.*, C.*  
FROM ADF_EXTENSION_COLUMN_USAGE CU  
INNER JOIN ADF_EXTENSION_COLUMN C ON C.Column_Id = CU.Column_Id  
INNER JOIN ADF_EXTENSIBLE_TABLE_USAGE TU ON TU.Usage_ID = CU.Table_Usage_Id  
INNER JOIN ADF_EXTENSIBLE_TABLE T ON T.Table_Id = TU.Table_Id  
WHERE TU.Context_Column_Value = '<Object_API_Name>'

Note: The below SQL will return data only if an import activity has been performed for the custom object

SELECT OBJECT_NAME AS OBJECT,OBJECT_ATTR_NAME AS ATRRIBUTE,OBJECT_ATTR_LEN AS LENGTH,  
SUBSTR(OBJECT_ATTR_TYPE, - INSTR(REVERSE(OBJECT_ATTR_TYPE), '.') + 1) AS DATA_TYPE,TXN_VO_ATTR_TABLE,TXN_VO_ATTR_COL  
FROM FUSION.MKT_IMP_OBJECT_DETAILS D,FUSION.MKT_IMP_OBJECT_ATTRS A  
WHERE D.OBJECT_DETAIL_ID=A.OBJECT_DETAIL_ID  
AND A.OBJECT_ATTR_COL LIKE '%EXTN_ATTRIBUTE%'  
AND A.CREATED_BY != 'SEED_DATA_FROM_APPLICATION'  
AND D.OBJECT_DETAIL_TABLE != 'MKT_IMP_JOBS' AND OBJECT_NAME LIKE '%<ObjectName>%'

3 - See Details of the Custom Object #

SELECT * FROM FND_OBJECTS WHERE Obj_Name LIKE '%<ObjectName>%'

4 - See the records in the custom object #

SELECT * FROM <DATABASE_OBJECT_NAME> WHERE Attribute_Category ='<OBJ_NAME>'

DATABASE_OBJECT_NAME - results of query 3

OBJ_NAME - results of query 3

Example #

Custom Object on Common Called OSCCommonObject

Go to Navigator > Reports and Analytics > Catalog(BI Publisher) > Create new Data Model > Create new SQL Query

1 - Find out in what table is a custom object been stored.

Object is in Common Area in Application Composer

SELECT * FROM ADF_EXTENSIBLE_TABLE_USAGE

WHERE ENTITYDEF_FULLNAME LIKE **'%OSCCommonObject_c%'**

Find the Table in which the custom object is held

SELECT TABLE_NAME FROM ADF_EXTENSIBLE_TABLE WHERE TABLE_ID = **100000020448001**

2 - Find out in what columns are custom fields that are on a custom object

SELECT T.*, CU.*, C.*  
FROM ADF_EXTENSION_COLUMN_USAGE CU  
INNER JOIN ADF_EXTENSION_COLUMN C ON C.Column_Id = CU.Column_Id  
INNER JOIN ADF_EXTENSIBLE_TABLE_USAGE TU ON TU.Usage_ID = CU.Table_Usage_Id  
INNER JOIN ADF_EXTENSIBLE_TABLE T ON T.Table_Id = TU.Table_Id  
WHERE TU.Context_Column_Value = '<Object_API_Name>'

Note: The below SQL will return data only if an import activity has been performed for the custom object

SELECT OBJECT_NAME AS OBJECT,OBJECT_ATTR_NAME AS ATRRIBUTE,OBJECT_ATTR_LEN AS LENGTH,  
SUBSTR(OBJECT_ATTR_TYPE, - INSTR(REVERSE(OBJECT_ATTR_TYPE), '.') + 1) AS DATA_TYPE,TXN_VO_ATTR_TABLE,TXN_VO_ATTR_COL  
FROM FUSION.MKT_IMP_OBJECT_DETAILS D,FUSION.MKT_IMP_OBJECT_ATTRS A  
WHERE D.OBJECT_DETAIL_ID=A.OBJECT_DETAIL_ID  
AND A.OBJECT_ATTR_COL LIKE '%EXTN_ATTRIBUTE%'  
AND A.CREATED_BY != 'SEED_DATA_FROM_APPLICATION'  
AND D.OBJECT_DETAIL_TABLE != 'MKT_IMP_JOBS' AND OBJECT_NAME LIKE '%<ObjectName>%'

3 - See Details of the Custom Object

SELECT * FROM FND_OBJECTS WHERE Obj_Name LIKE '%OSCCommonObject_c%'

4 - See the records in the custom object

SELECT * FROM **HZ_REF_ENTITIES** WHERE Attribute_Category =**'OSCCommonObject_c'**