Cable Drum View
The Cable Drum View defines by means of SQL the source of cable drum information in Smart Instrumentation. The statement is in the following format:
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:
-
Level 1 for cable drum in WBS (maximum 20 digits)
-
Level 2 for cable drum in WBS (maximum 20 digits)
-
Level 3 for cable drum in WBS (maximum 20 digits)
-
Level 4 for cable drum in WBS (maximum 20 digits)
-
Smart Materials commodity code (maximum 20 digits)
-
Quantity (maximum 15 digits, 3 after decimal point)
-
Quantity unit (maximum 10 digits)
-
Smart Materials tag number (maximum 24 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:
-
Level 1 for cable drum in WBS
-
Level 2 for cable drum in WBS
-
Level 3 for cable drum in WBS
-
Level 4 for cable drum 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 p.plant_name AS level1,
NULL AS level2,
NULL AS level3,
NULL AS level4,
ct.cable_type_name AS commodity_code,
c.cable_length AS qty,
rtrim(uc.cbl_udf_c01) AS unit_code,
substr(c.cable_num,1,24) AS tag_number
FROM cable@ c,
cable_type@ ct,
udf_cable@ uc,
plant@ p
WHERE c.cable_id = uc.cable_id AND
c.plant_id = p.plant_id AND
uc.cable_id = c.cable_id AND
ct.cable_type_id = c.cable_type_id AND
p.plant_name LIKE :plant_name AND
decode(:level2,'%','%','%')='%' AND -- making level 2 irrelevant
decode(:level3,'%','%','%')='%' AND -- making level 3 irrelevant
decode(:level4,'%','%','%')='%' -- making level 4 irrelevant
Cable Drum LevelX
The Cable Drum LevelX field may contain a source for the LOVs in IN 20.01 for the 1st to 4th level node. You may leave the fields blank if you do not need four levels. To support these levels with an 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 cable drum level 1
:Smart Instrumentation_selection.area_name for cable drum level 2
:Smart Instrumentation_selection.unit_name for cable_drum level3
Here is a valid example:
SELECT SUBSTR(cmpnt_func_type_desc,1,4) AS cft
FROM component_function_type@
WHERE cmpnt_func_type_name= :SmartPlant Instrumentation_selection.unit_name