Cable Drum View
The Cable Drum View defines by means of SQL the source of cable drum information in SmartPlant 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 SmartPlant 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 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:
-
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 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 Import Data by Link page 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 select lists in Import Data by Link page 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 a select list, 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 a select list title in comments (eg., /* Your Title */) within your statement.
The "at" sign (@) is enhanced by the appropriate database link name at run time.