Hookup View
The location of hookup data in Smart Instrumentation can be configured here. The Hookup 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 eight 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:
-
Plant name for hookup in WBS (maximum 20 digits)
-
Area name for hookup in WBS (maximum 20 digits)
-
Unit for hookup in WBS (maximum 20 digits)
-
Level 4 for hookup in WBS (maximum 20 digits)
-
Smart Materials spec code (maximum 16 digits)
-
Smart Materials short code (maximum 10 digits)
-
Smart Materials tag number (maximum 24 digits)
-
Flag (maximum 10 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. The flag field is an indicator of whether to include the component in the import run. However, the restriction to exclude certain components must be implemented by the user in this view.
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:
-
Plant name for hookup in WBS
-
Area name for hookup in WBS
-
Unit for hookup in WBS
-
Level 4 for hookup 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 influence of 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 an example (ORDER BY needs to be appended):
SELECT
plant.plant_name AS plant,
plant_area.area_name AS area,
plant_area_unit.unit_name AS unit,
hook_up_type.hu_type_name AS hu_category_level4,
NVL(udf_component.udf_c132, udf_component.udf_c131) AS pipe_or_instr_spec,
hook_up.hu_name AS short_code,
component.cmpnt_name AS tag_number,
hook_up_component.bom_include_flg AS flag
FROM
plant@,
plant_area@,
plant_area_unit@,
hook_up@,
hook_up_type@,
hook_up_component@,
component@,
udf_component@
WHERE
component.cmpnt_id = udf_component.cmpnt_id AND
component.plant_id = plant.plant_id AND
component.area_id = plant_area.area_id AND
plant_area_unit.unit_id = component.unit_id AND
hook_up_type.hu_type_id = hook_up.hu_type_id AND
hook_up.hu_id = hook_up_component.hu_id AND
hook_up_component.cmpnt_id = component.cmpnt_id AND
component.cmpnt_id <> 0 and
plant.plant_name LIKE :plant_name AND
plant_area.area_name LIKE :area_name AND
plant_area_unit.unit_name LIKE :unit_name AND
hook_up_type.hu_type_name LIKE :level4
Hookup Level4
The Hookup Level4 field may contain a source for the LOV in IN 20.01 for the 4th level node. You may omit this if you do not need the 4th level. To support the 4th level with an LOV, use a statement in the following format:
SELECT column_name
FROM table_name@
The column name 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
:Smart Instrumentation_selection.area_name
:Smart Instrumentation_selection.unit_name
Here is a valid example that lets the hookup type be selected as the 4th level node in the unit:
select distinct /*Hook Up Category*/ hu_type_name
FROM hook_up_type@
The string in comment format (Hook Up Category in the previous example) is used as a field and as a LOV title.