EGP_SYSTEM_ITEMS_TL: Columns & Indexes

EGP_SYSTEM_ITEMS_TL: This table is primarily used by Oracle Fusion to store the Description of the item along with Long Description. Point to remember is that you’ll get a unique row for each inventory_item_id, Organization_id and Language. Or in other words we can say that each item can have a different Description depending on the inventory org as well as the language.

Here are a few important points:
1. This table will have at least one row for each item – inventory org combination from egp_system_items_b table.
2. You will have to query this table if you need to find out the description or long description of the item.
3. To get the language of the user, use the function USERNV(‘LANG’) in where clause. Example:
select description
from egp_system_items_tl
where inventory_item_id = 12345
AND organization_id = 987
AND LANGUAGE = USERENV(‘LANG’)

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Let’s discuss the Table Structure and indexes of the table:

EGP_SYSTEM_ITEMS_TL Table Structure

COLUMN_NAME	DATA_TYPE	NULLABLE
INVENTORY_ITEM_ID	NUMBER	N
HTML_LONG_DESCRIPTION	CLOB	Y
ORGANIZATION_ID	NUMBER	N
LANGUAGE	VARCHAR2(16)	N
SOURCE_LANG	VARCHAR2(16)	N
DESCRIPTION	VARCHAR2(960)	Y
LAST_UPDATE_DATE	TIMESTAMP(6)	N
LAST_UPDATED_BY	VARCHAR2(256)	N
CREATION_DATE	TIMESTAMP(6)	N
CREATED_BY	VARCHAR2(256)	N
LAST_UPDATE_LOGIN	VARCHAR2(128)	Y
LONG_DESCRIPTION	VARCHAR2(4000)	Y
OBJECT_VERSION_NUMBER	NUMBER	N
TEMPLATE_NAME	VARCHAR2(3840)	Y

EGP_SYSTEM_ITEMS_TL Indexes

INDEX_NAME	INDEX_TYPE	UNIQUENESS	COLUMN_NAME
EGP_SYSTEM_ITEMS_TL_N1	NORMAL	NONUNIQUE	ORGANIZATION_ID
EGP_SYSTEM_ITEMS_TL_N1	NORMAL	NONUNIQUE	LANGUAGE
EGP_SYSTEM_ITEMS_TL_N1	NORMAL	NONUNIQUE	DESCRIPTION
EGP_SYSTEM_ITEMS_TL_N2	FUNCTION-BASED NORMAL	NONUNIQUE	LANGUAGE
EGP_SYSTEM_ITEMS_TL_N2	FUNCTION-BASED NORMAL	NONUNIQUE	ORGANIZATION_ID
EGP_SYSTEM_ITEMS_TL_N2	FUNCTION-BASED NORMAL	NONUNIQUE	SYS_NC00015$
EGP_SYSTEM_ITEMS_TL_N3	FUNCTION-BASED NORMAL	NONUNIQUE	LANGUAGE
EGP_SYSTEM_ITEMS_TL_N3	FUNCTION-BASED NORMAL	NONUNIQUE	ORGANIZATION_ID
EGP_SYSTEM_ITEMS_TL_N3	FUNCTION-BASED NORMAL	NONUNIQUE	SYS_NC00016$
EGP_SYSTEM_ITEMS_TL_N4	NORMAL	NONUNIQUE	LAST_UPDATE_DATE
EGP_SYSTEM_ITEMS_TL_N5	FUNCTION-BASED NORMAL	NONUNIQUE	SYS_NC00015$
EGP_SYSTEM_ITEMS_TL_N5	FUNCTION-BASED NORMAL	NONUNIQUE	INVENTORY_ITEM_ID
EGP_SYSTEM_ITEMS_TL_N6	NORMAL	NONUNIQUE	TEMPLATE_NAME
EGP_SYSTEM_ITEMS_TL_N6	NORMAL	NONUNIQUE	ORGANIZATION_ID
EGP_SYSTEM_ITEMS_TL_N6	NORMAL	NONUNIQUE	LANGUAGE
EGP_SYSTEM_ITEMS_TL_N7	FUNCTION-BASED NORMAL	NONUNIQUE	SYS_NC00017$
EGP_SYSTEM_ITEMS_TL_N7	FUNCTION-BASED NORMAL	NONUNIQUE	ORGANIZATION_ID
EGP_SYSTEM_ITEMS_TL_N7	FUNCTION-BASED NORMAL	NONUNIQUE	LANGUAGE
EGP_SYSTEM_ITEMS_TL_U1	NORMAL	UNIQUE	INVENTORY_ITEM_ID
EGP_SYSTEM_ITEMS_TL_U1	NORMAL	UNIQUE	ORGANIZATION_ID
EGP_SYSTEM_ITEMS_TL_U1	NORMAL	UNIQUE	LANGUAGE

Related Posts

Item Table In Oracle Fusion
Querying Item Table In Oracle Fusion
Item Price Query

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com

Check out the Amazon Deals of the day based on your interests..