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 |