Sources for Cable Drum - Intergraph Smart Materials - Version 2020 (10.0) - Help - Hexagon

Intergraph Smart Materials Web Help (2020)

Language
English
Product
Intergraph Smart Materials
Subproduct
Web
Search by Category
Help
Smart Materials/Smart Reference Data Version
2020 (10.0)

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:

  1. Level 1 for cable drum in WBS (maximum 20 digits)

  2. Level 2 for cable drum in WBS (maximum 20 digits)

  3. Level 3 for cable drum in WBS (maximum 20 digits)

  4. Level 4 for cable drum in WBS (maximum 20 digits)

  5. Smart Materials commodity code (maximum 20 digits)

  6. Quantity (maximum 15 digits, 3 after decimal point)

  7. Quantity unit (maximum 10 digits)

  8. 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:

  1. Level 1 for cable drum in WBS

  2. Level 2 for cable drum in WBS

  3. Level 3 for cable drum in WBS

  4. 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.