EcalCondDB/sql/pvss_hvboardlogicid_tb_func.sql

0001 CREATE OR REPLACE
0002 function HVBOARDLOGICID(DPEName in varchar2) return varchar2 is
0003
0004 alias varchar2(1000);
0005 superModuleLoc varchar2(100);
0006 superModuleNumber number;
0007 moduleNumber number;
0008 boardNumber number;
0009 invalid_board_name exception;
0010 currentDate date;
0011
0012 /*
0013  For the HV boards the logic_ids are
0014
0015  1061SS00CC
0016
0017  SS = SM number 1-36
0018  CC = board number 0, 2, 4 or 6
0019
0020  source string will by something like 'ECAL_HV/SM_H2/M2/board17'
0021
0022  the table 'PVSS_TB_SM_DAT' has a field DP_NAME with strings
0023  like 'pcethdcs2:GeneralStands.H4_BEAM.SMNum'
0024
0025 */
0026
0027 begin
0028   alias:=getAliasForDevice(DPEName);
0029   currentDate:=sysdate;
0030
0031   /* Second field */
0032   superModuleLoc:=regexp_substr(alias, '[^/]+', 1, 2);
0033
0034   /* Switch strings */
0035   if superModuleLoc = 'SM_BEAM' then
0036      superModuleLoc:='H4_BEAM';
0037   elsif superModuleLoc = 'SM_COSM' then
0038      superModuleLoc:='H4_COSM';
0039   elsif superModuleLoc = 'SM_H2' then
0040      superModuleLoc:='H2';
0041   end if;
0042
0043   /* Get SM Number */
0044   select to_number(substr(sm, 3)) into superModuleNumber from pvss_tb_sm_dat
0045    where dp_name like '%'||superModuleLoc||'.SMNum'
0046      and since <= currentDate and till > currentDate;
0047
0048   /* Third field, all the chars after 'board' */
0049   boardNumber:=to_number(substr(regexp_substr(alias, '[^/]+', 1, 3), 6));
0050
0051   if superModuleNumber is null
0052   or boardNumber is null
0053   or superModuleNumber>36
0054   or boardNumber>6 then
0055     raise invalid_board_name;
0056   end if;
0057
0058   return 1061000000+10000*superModuleNumber+boardNumber;
0059 end;
0060 /
0061 show errors;