Sources for Instruments - Intergraph Smart Materials - 10.2 - Help - Hexagon

Intergraph Smart Materials Web Help (10.2)

Language
English
Product
Intergraph Smart Materials
Subproduct
Web
Search by Category
Help
Smart Materials/Smart Reference Data Version
10.2

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 the Import Data by Link page 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 the levels, but you must include all six 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 Import Data by Link page 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.