Sources for Cable Drums - Intergraph Smart Materials - Version 10.1 - Administration & Configuration - Hexagon

Intergraph Smart Materials Integration (10.1)

Language
English
Product
Intergraph Smart Materials
Subproduct
Classic
Search by Category
Administration & Configuration
Smart Construction Version
8
Smart Materials/Smart Reference Data Version
10.1

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:

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

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