EcalCondDB/sql/pvss_hvchannellogicid_tb_func.sql

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