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:
-
Component ID (primary key on the component table in Smart Instrumentation)
-
Tag Number (maximum 24 digits)
-
Plant name for instrument in WBS (maximum 20 digits)
-
Area name for instrument in WBS (maximum 20 digits)
-
Unit for instrument in WBS (maximum 20 digits)
-
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)
-
Smart Materials Commodity Group Code (maximum 10 digits)
-
Smart Materials Commodity Part Code (maximum 10 digits)
-
Level 5 for instrument in WBS (maximum 20 digits)
-
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:
-
Plant name for instrument in WBS
-
Area name for instrument in WBS
-
Unit for instrument in WBS
-
Level4 (terminal node) for instrument in WBS
-
Level5 for instrument in WBS
-
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.