Report the Process Owner for each Pattern Generated Record of an Instrument Symbol when the Symbol is connected to a Nozzle

In the example, the project administrator has created a customized SQL script routine designed to modify the instrument table INSTLIST to report the process owner for each pattern generated record of an instrument symbol when the symbol is connected to a nozzle.

The drawing displays a vessel with a connected nozzle.

The nozzle symbol connects to an instrument symbol, which is referenced with a pattern definition that expands the instrument symbol to three records.

The instrument symbol tag letter TEW represents a combined thermowell, temperature element and temperature transmitter: TEW-2576-A.

The compose tag is assigned to pattern reference 22, generating instrument tags: TT-2576-A, TE-2576-A and TW-2576-A.

The example script will return the process owner for each pattern generated record of an instrument symbol when the symbol is connected to a nozzle.

-- Reset Owner Handle to match it to parent symbol owner handle

UPDATE INSTLIST

SET [Inst Owner Handle] = ''

where

[Handle] like '%*%'

UPDATE INSTLIST

SET [Inst Owner Handle] = SecTable.[Inst Owner Handle]

FROM (

SELECT Handle1, [DRG+SHT], [Inst Owner Handle], [Process Owner] , [Inst Process Owner]

FROM INSTLIST) AS SecTable

WHERE

INSTLIST.Handle1 like ('%*%')

and SecTable.[Handle1] = substring(INSTLIST.Handle1, 1, CHARINDEX('*',INSTLIST.Handle)-1)

and SecTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

-- Update instrument process owner where instrument is generated by a pattern

UPDATE INSTLIST

set [Inst Owner Handle] = SecTable.[Inst Owner Handle]

FROM (

SELECT Handle1, [DRG+SHT], [Inst Owner Handle], [Process Owner] , [Inst Process Owner]

FROM INSTLIST) AS SecTable

WHERE

INSTLIST.Handle1 like ('%*%')

and SecTable.[Handle1] = substring(INSTLIST.Handle1, 1, CHARINDEX('*',INSTLIST.Handle)-1)

and SecTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

and INSTLIST.[Process Owner] = ''

UPDATE INSTLIST

SET [Process Owner] = OtherTable.[Instrument Label] ,

[Inst Process Owner] = OtherTable.[Instrument Label]

FROM (

SELECT Handle1, [DRG+SHT], [Inst Owner Handle], [Process Owner] , [Inst Process Owner] , [Instrument Label]

FROM INSTLIST) AS OtherTable

WHERE

INSTLIST.Handle1 like ('%*%')

and OtherTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

and INSTLIST.[Inst Owner Handle] = OtherTable.[Handle1]

-- Update instrument process owner generated by patterns for equipment process owner getting specific nozzle tag.

UPDATE INSTLIST

SET [Process Owner] = NozTable.[FULL TAG] , [Inst Process Owner] = NozTable.[Equipment Label]

FROM (

SELECT Handle, [DRG+SHT], [FULL TAG] ,[Equipment Label]

FROM NOZLIST) As NozTable

WHERE

INSTLIST.Handle1 like '%*%'

and INSTLIST.[Inst Owner Handle] = NozTable.[Handle]

and NozTable.[DRG+SHT] = INSTLIST.[DRG+SHT]

At the database grid table, each pattern generated instrument is hosted by the same vessel and nozzle tag value.