1. Tables and Views for SCM
  2. EGI_PROD_INTF_TABLES_MAP_V

EGI_PROD_INTF_TABLES_MAP_V

Details

  • Schema: FUSION

  • Object owner: EGI

  • Object type: VIEW

Columns

Name

PROD_TABLE_NAME

COLUMN_NAME

INTF_TABLE_NAME

COLUMN_TYPE

WIDTH

NULL_ALLOWED_FLAG

TRANSLATE_FLAG

DESCRIPTION

PRECISION

SCALE

PROD_COL_NA

MAX_DECIMAL_DIGITS

Query

SQL_Statement

select prod_table_name, intf_col_data.column_name, intf_col_data.intf_table_name,

nvl( prod_col_data.column_type, intf_col_data.column_type) as column_type,

nvl( prod_col_data.width, intf_col_data.width) as width,

nvl( prod_col_data.null_allowed_flag, intf_col_data.null_allowed_flag) as null_allowed_flag,

nvl( prod_col_data.translate_flag, intf_col_data.translate_flag) as translate_flag,

nvl( prod_col_data.description, intf_col_data.description) as description,

nvl( prod_col_data.precision, intf_col_data.precision) as precision,

nvl( prod_col_data.scale, intf_col_data.scale) as scale,

case

when prod_table_name is null

then 'Y'

else null

end as PROD_COL_NA,

nvl( prod_col_data.max_decimal_digits, intf_col_data.max_decimal_digits) as max_decimal_digits

from

(select tab.table_name as prod_table_name,

case

when tab.table_name = 'EGP_SYSTEM_ITEMS_B'

then 'EGP_SYSTEM_ITEMS_INTERFACE'

when tab.table_name = 'EGP_SYSTEM_ITEMS_TL'

then 'EGP_SYSTEM_ITEMS_TL_INTF'

when tab.table_name = 'EGP_ITEM_REVISIONS_B'

then 'EGP_ITEM_REVISIONS_INTERFACE'

when tab.table_name = 'EGP_ITEM_REVISIONS_TL'

then 'EGP_ITEM_REVISIONS_INTERFACE'

when tab.table_name = 'EGP_ITEM_RELATIONSHIPS_B'

then 'EGP_ITEM_RELATIONSHIPS_INTF'

when tab.table_name = 'EGP_ITEM_RELATIONSHIPS_TL'

then 'EGP_ITEM_RELATIONSHIPS_INTF'

when tab.table_name = 'EGP_CATEGORIES_B'

then 'EGP_ITEM_CATEGORIES_INTERFACE'

when tab.table_name = 'EGP_CATEGORIES_TL'

then 'EGP_ITEM_CATEGORIES_INTERFACE'

when tab.table_name = 'EGP_COMPONENTS_B'

then 'EGP_COMPONENTS_INTERFACE'

when tab.table_name = 'EGP_STRUCTURES_B'

then 'EGP_STRUCTURES_INTERFACE'

when tab.table_name = 'EGO_ITEM_ASSOCIATIONS'

then 'EGO_ITEM_ASSOCIATIONS_INTF'

when tab.table_name = 'EGP_TRADING_PARTNER_ITEMS'

then 'EGP_TRADING_PARTNER_ITEMS_INTF'

when tab.table_name = 'EGO_STYLE_VARIANT_ATTR_VS'

then 'EGO_STYLE_VARIANT_ATTR_VS_INTF'

when tab.table_name = 'EGP_SUBSTITUTE_COMPONENTS'

then 'EGP_SUB_COMPS_INTERFACE'

when tab.table_name = 'EGP_REFERENCE_DESIGNATORS'

then 'EGP_REF_DESGS_INTERFACE'

when tab.table_name = 'EGO_ITEM_EFF_B'

then 'EGO_ITEM_INTF_EFF_B'

when tab.table_name = 'EGO_ITEM_EFF_TL'

then 'EGO_ITEM_INTF_EFF_TL'

when tab.table_name = 'EGO_ITEM_REVISION_EFF_B'

then 'EGO_ITEM_REVISION_INTF_EFF_B'

when tab.table_name = 'EGO_ITEM_REVISION_EFF_TL'

then 'EGO_ITEM_REVISION_INTF_EFF_TL'

when tab.table_name = 'EGO_ITEM_SUPPLIER_EFF_B'

then 'EGO_ITEM_SUPPLIER_INTF_EFF_B'

when tab.table_name = 'EGO_ITEM_SUPPLIER_EFF_TL'

then 'EGO_ITEM_SUPPLIER_INTF_EFF_TL'

else null

end as interface_table_name,

col.column_name as column_name, col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale ,

CASE when tab.table_name = 'EGP_SYSTEM_ITEMS_B' and col.column_type = 'N' THEN

(select max_decimal_digits from EGP_ITEM_ATTRIBUTES_VL where interface_table_name = 'EGP_SYSTEM_ITEMS_INTERFACE' and attr_name = col.column_name and attr_data_type = 'NUMBER')

ELSE null END AS max_decimal_digits

from fusion.fnd_tables tab, fusion.fnd_columns col

where

tab.table_id = col.table_id

and tab.table_name in (

'EGP_SYSTEM_ITEMS_B',

'EGP_SYSTEM_ITEMS_TL',

'EGP_ITEM_REVISIONS_B',

'EGP_ITEM_REVISIONS_TL',

'EGP_ITEM_RELATIONSHIPS_B',

'EGP_ITEM_RELATIONSHIPS_TL',

'EGO_ITEM_ASSOCIATIONS',

'EGP_CATEGORIES_B',

'EGP_CATEGORIES_TL',

'EGP_COMPONENTS_B',

'EGP_STRUCTURES_B',

'EGP_TRADING_PARTNER_ITEMS',

'EGO_STYLE_VARIANT_ATTR_VS',

'EGP_SUBSTITUTE_COMPONENTS',

'EGP_REFERENCE_DESIGNATORS',

'EGO_ITEM_EFF_B',

'EGO_ITEM_EFF_TL',

'EGO_ITEM_REVISION_EFF_B',

'EGO_ITEM_REVISION_EFF_TL',

'EGO_ITEM_SUPPLIER_EFF_B',

'EGO_ITEM_SUPPLIER_EFF_TL'

)) prod_col_data right join (

select tab.table_name as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale,

CASE WHEN tab.table_name = 'EGP_SYSTEM_ITEMS_INTERFACE' and col.column_type = 'N' THEN

(select max_decimal_digits from EGP_ITEM_ATTRIBUTES_VL where interface_table_name = 'EGP_SYSTEM_ITEMS_INTERFACE' and attr_name = col.column_name and attr_data_type = 'NUMBER')

ELSE null END AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in (

'EGP_SYSTEM_ITEMS_INTERFACE',

'EGP_SYSTEM_ITEMS_TL_INTF',

'EGP_ITEM_REVISIONS_INTERFACE',

'EGP_ITEM_RELATIONSHIPS_INTF',

'EGP_ITEM_CATEGORIES_INTERFACE',

'EGP_COMPONENTS_INTERFACE',

'EGP_STRUCTURES_INTERFACE',

'EGO_ITEM_ASSOCIATIONS_INTF',

'EGP_TRADING_PARTNER_ITEMS_INTF',

'EGO_STYLE_VARIANT_ATTR_VS_INTF',

'EGP_ITEM_ATTACHMENTS_INTF',

'EGP_SUB_COMPS_INTERFACE',

'EGP_REF_DESGS_INTERFACE',

'EGO_ITEM_INTF_EFF_B',

'EGO_ITEM_INTF_EFF_TL',

'EGO_ITEM_REVISION_INTF_EFF_B',

'EGO_ITEM_REVISION_INTF_EFF_TL',

'EGO_ITEM_SUPPLIER_INTF_EFF_B',

'EGO_ITEM_SUPPLIER_INTF_EFF_TL')

union

(

select 'RELATIONSHIPS-MFG_PART_NUM' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' )

)

union

(

select 'RELATIONSHIPS-GTIN_XREF' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' )

)

union

(

select 'RELATIONSHIPS-RELATED_ITEM' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' )

)

union

(

select 'RELATIONSHIPS-ITEM_XREF' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' )

)

union

(

select 'RELATION-SUPPLIER_ITEM_XREF' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' )

)

union

(

select 'RELATION-COMPETITOR_ITEM_XREF' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' )

)

union

(

select 'RELATION-CUSTOMER_ITEM_XREF' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' )

)

union

(

select 'ITEM_RELATIONSHIPS_DUMMY_TABLE' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' )

)

union

(

select 'ATTACHMENTS_DUMMY_TABLE' as intf_table_name, 'RELATIONSHIPDFF#ENTITY_TYPE' as column_name,

'V' as column_type, 150 as width, 'N' as null_allowed_flag, 'N' as translate_flag, 'User can specify which Relationship type the DFF attributes belong to

: Related Item, GTIN and Cross-Reference' as decription, null as precision, null as scale, null as max_decimal_digits

from fusion.fnd_tables tab

where tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' ) and rownum = 1

)

union

(

select 'TRADING_PARTNER_DUMMY_TABLE' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_TRADING_PARTNER_ITEMS_INTF' )

)

union

(

select 'ATTACHMENTS_DUMMY_TABLE' as intf_table_name, 'TRADINGPARTNERDFF#ENTITY_TYPE' as column_name,

'V' as column_type, 150 as width, 'N' as null_allowed_flag, 'N' as translate_flag, 'User can specify which TPI Relationship type or TPI Type the DFF attributes belong to

: MPN, Supplier Item, Customer Item, and Competitor Item' as decription, null as precision, null as scale, null as max_decimal_digits

from fusion.fnd_tables tab

where tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' ) and rownum = 1

)

union

(

select 'STRUCTURE_DUMMY_TABLE' as intf_table_name, col.column_name,

col.column_type, col.width, col.null_allowed_flag, col.translate_flag, col.description, col.precision, col.scale, null AS max_decimal_digits

from fusion.fnd_columns col, fusion.fnd_tables tab

where col.table_id = tab.table_id and tab.table_name in ('EGP_STRUCTURES_INTERFACE' )

)

union

(

select 'ATTACHMENTS_DUMMY_TABLE' as intf_table_name, 'STRUCTUREDFF#ENTITY_TYPE' as column_name,

'V' as column_type, 150 as width, 'N' as null_allowed_flag, 'N' as translate_flag, 'User can specify which Structure entity the DFF attributes belong to

:Item Structure, Component, Substitute Component, and Reference Designator' as decription, null as precision, null as scale, null as max_decimal_digits

from fusion.fnd_tables tab

where tab.table_name in ('EGP_ITEM_RELATIONSHIPS_INTF' ) and rownum = 1

)

)intf_col_data on intf_col_data.intf_table_name = prod_col_data.interface_table_name and intf_col_data.column_name = prod_col_data.column_name