Sources for Instruments - Intergraph Smart Materials - Version 2020 (10.0) - Administration & Configuration - Hexagon

Instrument View

The location of instrument data in Smart Instrumentation can be configured here. The Instrument View contains all information needed by means of SQL. The statement has the following form:

SELECT column_1,

column_2,

. . .

column_n

FROM table_1@,

table_2@

WHERE column6 = column3

AND column4 = column5

The following is fixed in this statement:

Select List

There must be exactly ten columns in the select list. If you want to leave one of them out, do not omit the column but enter NULL in the column. The columns are used in the following order and for the following purposes:

1. Component ID (primary key on the component table in Smart Instrumentation)

2. Tag Number (maximum 24 digits)

3. Plant name for instrument in WBS (maximum 20 digits)

4. Area name for instrument in WBS (maximum 20 digits)

5. Unit for instrument in WBS (maximum 20 digits)

6. Level 4 (formerly called terminal node, but it isn’t necessarily the terminal node in the true sense of the word) for instrument in WBS (maximum 20 digits)

7. Smart Materials Commodity Group Code (maximum 10 digits)

8. Smart Materials Commodity Part Code (maximum 10 digits)

9. Level 5 for instrument in WBS (maximum 20 digits)

10. Level 6 for instrument in WBS (maximum 20 digits)

The name of the columns is free. In Smart Instrumentation, some columns have a CHAR data type instead of VARCHAR2 data type. CHAR has a fixed length. If the actual value is not the same length as the column definition, the remaining digits are filled with blanks. To avoid problems in Smart Materials, use the RTRIM function to cut off trailing blanks from fields.

Where Condition

You must reflect the reference to the values selected in IN 20.01 by including placeholders in the WHERE section of your statement. Users can define a name for the placeholders. They must appear in the following order:

1. Plant name for instrument in WBS

2. Area name for instrument in WBS

3. Unit for instrument in WBS

4. Level4 (terminal node) for instrument in WBS

5. Level5 for instrument in WBS

6. Level6 for instrument in WBS

You do not need to use all of the levels, but you must include all four in your statement.

To include a level without using it, use an expression like the following:

WHERE decode(:level_4,'%','%','%')='%'

This expression replaces anything that might be selected in IN 20.01 for level_4 by a percent and compares it to percent, so the filtering for level_4 is switched off.

The "at" sign (@) appended to your table names in the statement is enhanced by the appropriate database link name at run time.

Note, that the ORDER BY clause in your view should contain all levels you plan to use. Otherwise the import might import fewer positions than expected.

Here is a valid example:

SELECT dflt.cmpnt_id,

dflt.cmpnt_name AS tag_number,

p.plant_name,

a.area_name,

u.unit_name,

substr(udf.udf_c01,1,4) AS terminal_node,

substr(udf.udf_c01,1,2) commodity_group,

substr(udf.udf_c01,3,4) AS commodity_part,

udf.udf_c02 AS level5_name,

udf.udf_c03 AS level6_name

FROM component@ dflt,

plant_area@ a,

plant@ p,

plant_area_unit@ u,

udf_component@ udf

WHERE p.plant_name like :plant_name

AND a.area_id = dflt.area_id

AND u.unit_id = dflt.unit_id

AND a.plant_id = dflt.plant_id

AND a.area_id = u.area_id

AND u.plant_id = p.plant_id

AND a.area_name LIKE :area_name

AND u.unit_name LIKE :unit_name

AND dflt.cmpnt_id>0

AND udf.compnt_id=dflt.compnt_id

AND udf.udf_c01 LIKE :level4_name

AND udf.udf_c02 LIKE :level5_name

AND udf.udf_c03 LIKE :level6_name

ORDER BY p.plant_name,a.area_name,u.unit_name,substr(udf.udf_c01,1,4),

udf_c02,udf_c03

Instrument LevelX

The Instrument LevelX fields may contain a source for the LOVs in IN 20.01 for the 4th to 6th level node. The first three levels are plant, area and unit. The fourth level is the terminal node. You may leave the fields blank if you do not need six levels. To support these levels with LOV, use a statement in the following format:

SELECT column_name AS column_alias

FROM table_name@

The column name, alias and the table_name can be chosen deliberately. Additionally you may place an LOV title in comments (eg., /* Your Title */) within your statement.

The "at" sign (@) is enhanced by the appropriate database link name at run time.

If you want your selection to depend on previous selections in IN 20.01, you can use the following variables to refer to the appropriate fields on the screen:

:Smart Instrumentation_selection.plant_name for instrument level 1

:Smart Instrumentation_selection.area_name for instrument level 2

:Smart Instrumentation_selection.unit_name for instrument level3

:Smart Instrumentation_selection.cft_name for instrument level4 (terminal node)

:Smart Instrumentation_selection.level5_name for instrument level5

:Smart Instrumentation_selection.level6_name for instrument level6

Here is an example:

SELECT substr(tn.udf_c05,1,1) tnode_name

/* Terminal Nodes */

FROM udf_component@ tn where tn.udf_c03=:SmartPlant Instrumentation_selection.unit_name