Cisco Prime Optical Database Schema, 9.3 - Chapter 3: Database Schema Functions [Cisco Prime Optical]

This section describes the functions that Prime Optical uses. Functions read parameters and return a value. The functions are listed in alphabetical order.

3.1.1 CollapsedDestNodeName

CREATE or replace function collapseddestnodename(CKTNODEIDIN varchar,CKTUNIQUEIDIN 
varchar) return varchar2

is

cursor cktdestnodename is

select CktDestNodeId

from circuit_dest_tbl

where CKTNODEID=CKTNODEIDIN

and CKTUNIQUEID =CKTUNIQUEIDIN

and CktDestNodeId is not null;

finaldestNode varchar2(10000);

DestNedbaccessid varchar2(1000);

BEGIN

for rec in cktdestnodename loop

    SELECT nedbaccessid INTO DestNedbaccessid

    FROM ne_info_table

    WHERE nenodeid= rec.CktDestNodeId and rec.CktDestNodeId != 0 ;

   if cktdestnodename%ROWCOUNT = 1 then

    finaldestNode:= DestNedbaccessid;

  else

    finaldestNode := finaldestNode||','||DestNedbaccessid;

  end if;

end loop;

return finaldestNode;

END;

3.1.2 CollapsedDestNodeName

CREATE or replace function collapseddestnodename (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    cursor cktdestnodename is

    select CktCtpNodeId

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID =CKTUNIQUEIDIN

    and ctp_type = 2

    and CktCtpNodeId is not null;

    finaldestNode varchar2(10000);

    DestNedbaccessid varchar2(1000);

BEGIN

    for rec in cktdestnodename loop

        SELECT nedbaccessid INTO DestNedbaccessid

        FROM ne_info_table

        WHERE nenodeid= rec.CktCtpNodeId and rec.CktCtpNodeId != 0 ;

       if cktdestnodename%ROWCOUNT = 1 then

           finaldestNode:= DestNedbaccessid;

       else

           finaldestNode := finaldestNode||','||DestNedbaccessid;

       end if;

    end loop;

    return finaldestNode;

END collapseddestnodename;

3.1.3 CollapsedEqptPortName

CREATE or replace function collapsedeqptportname(CKTNODEIDIN varchar,CKTUNIQUEIDIN 
varchar)

return varchar2

IS

   cursor eqptcollpasedport is

   select  CktCtpNodeId,

   cktCtpPhysicalloc,

   cktCtpIfindex,

   cktCtpModuleType

   from circuit_ctp_tbl

   where CKTNODEID=CKTNODEIDIN

   and CKTUNIQUEID =CKTUNIQUEIDIN

   and ctp_type = 2

   and CktCtpNodeId is not null

   and cktCtpPhysicalloc is not null

   and cktCtpIfindex is not null

   and cktCtpModuleType is not null;

   finaleqptinfoport varchar2(10000);

   DestNedbaccessid varchar2(1000);

BEGIN

   for rec in eqptcollpasedport loop

       SELECT nedbaccessid INTO DestNedbaccessid

       FROM ne_info_table

       WHERE nenodeid= rec.CktCtpNodeId and rec.CktCtpNodeId != 0;

       if eqptcollpasedport%ROWCOUNT = 1 then

           finaleqptinfoport:= '@'||eqptinfoport(DestNedbaccessid,rec.cktCtpPhysicalloc,0, 
rec.cktCtpModuleType);

       else

           finaleqptinfoport := 
finaleqptinfoport||','||'@'||eqptinfoport(DestNedbaccessid,rec.cktCtpPhysicalloc,0, 
rec.cktCtpModuleType);

       end if;

   end loop;

   return finaleqptinfoport;

END collapsedeqptportname;

3.1.4 CollapsedIfIndex

CREATE or replace function collapsedifindex (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    cursor cktobjectindexcursor is

    select CktCtpIfIndex

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID =CKTUNIQUEIDIN

    and ctp_type = 2

    and CktCtpIfIndex is not null;

    finalifindex varchar2(10000);

BEGIN

    for rec in cktobjectindexcursor loop

        if cktobjectindexcursor%ROWCOUNT = 1 then

            finalifindex:= rec.CktCtpIfIndex;

        else

            finalifindex := finalifindex||','||rec.CktCtpIfIndex;

        end if;

    end loop;

    return finalifindex;

END collapsedifindex;

3.1.5 CollapsedIfIndex

CREATE or replace function collapsedifindex (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    cursor cktobjectindexcursor is

    select CktCtpIfIndex

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID =CKTUNIQUEIDIN

    and ctp_type = 2

    and CktCtpIfIndex is not null;

    finalifindex varchar2(10000);

BEGIN

    for rec in cktobjectindexcursor loop

        if cktobjectindexcursor%ROWCOUNT = 1 then

            finalifindex:= rec.CktCtpIfIndex;

        else

            finalifindex := finalifindex||','||rec.CktCtpIfIndex;

        end if;

    end loop;

    return finalifindex;

END collapsedifindex;

3.1.6 CollapsedModelType

CREATE or replace function collapsedmodeltype (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    cursor cktmodeltypecursor is

    select CKTCTPMODELTYPE

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID =CKTUNIQUEIDIN

    and CTP_TYPE = 2

    and CKTCTPMODELTYPE is not null;

    finalmoduletype varchar2(10000);

BEGIN

    for rec in cktmodeltypecursor loop

        if cktmodeltypecursor%ROWCOUNT = 1 then

            finalmoduletype:= rec.CKTCTPMODELTYPE;

        else

            finalmoduletype := finalmoduletype||','||rec.CKTCTPMODELTYPE;

        end if;

    end loop;

    return finalmoduletype;

END collapsedmodeltype;

3.1.7 CollapsedModuleName

CREATE or replace function collapsedmodulename (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    cursor cktmodulenamecursor is

    select CKTCTPMODULETYPE

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID =CKTUNIQUEIDIN

    and ctp_type = 2

    and CKTCTPMODULETYPE is not null;

    finalcktdestModuleType varchar2(10000);

    slotmoduletype number;

BEGIN

    for rec in cktmodulenamecursor loop

	SELECT trunc(rec.CKTCTPMODULETYPE/power(2,16)) INTO slotmoduletype FROM dual;

        if cktmodulenamecursor%ROWCOUNT = 1 then

            finalcktdestModuleType:= slotmoduletype;

        else

            finalcktdestModuleType := finalcktdestModuleType||','||slotmoduletype;

        end if;

    end loop;

    return finalcktdestModuleType;

END collapsedmodulename;

3.1.8 CollapsedObjectIndex

CREATE or replace function collapsedobjectindex(CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    cursor cktifindexcursor is

    select CktCtpObjectType

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID =CKTUNIQUEIDIN

    and CTP_TYPE = 2;

    finalifindex varchar2(10000);

BEGIN

    for rec in cktifindexcursor loop

        if cktifindexcursor%ROWCOUNT = 1 then

            finalifindex:= rec.CktCtpObjectType;

        else

            finalifindex := finalifindex||','||rec.CktCtpObjectType;

        end if;

    end loop;

    return finalifindex;

END;

3.1.9 CollapsedPhy

CREATE or replace function collapsedphy (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    cursor cktphysicalloc_cursor is

    select CKTCTPPHYSICALLOC

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID =CKTUNIQUEIDIN

    and ctp_type = 2

    and CKTCTPPHYSICALLOC is not null;

    finalphy varchar2(10000);

BEGIN

    for rec in cktphysicalloc_cursor loop

        if cktphysicalloc_cursor%ROWCOUNT = 1 then

            finalphy:= rec.CKTCTPPHYSICALLOC;

        else

            finalphy := finalphy||','||rec.CKTCTPPHYSICALLOC;

        end if;

    end loop;

    return finalphy;

END collapsedphy;

3.1.10 CollapsedPort

CREATE or replace function collapsedport(CKTNODEIDIN varchar,CKTUNIQUEIDIN varchar) return 
varchar2

is

cursor cktdestcursor is

select cktdestportname

from circuit_dest_tbl

where CKTNODEID=CKTNODEIDIN

and CKTUNIQUEID =CKTUNIQUEIDIN

and cktdestportname is not null;

finaldest varchar2(10000);

BEGIN

for rec in cktdestcursor loop

   if cktdestcursor%ROWCOUNT = 1 then

    finaldest:= rec.cktdestportname;

  else

    finaldest := finaldest||','||rec.cktdestportname;

  end if;

end loop;

return finaldest;

END;

3.1.11 CollapsedPortModuleName

CREATE or replace function collapsedportmodulename(CKTNODEIDIN varchar,CKTUNIQUEIDIN 
varchar) return varchar2

is

cursor cktmodulenamecursor is

select cktctptModuleType

from circuit_ctp_tbl

where CKTNODEID=CKTNODEIDIN

and CKTUNIQUEID =CKTUNIQUEIDIN

and ctp_type = 2

and cktctptModuleType is not null;

finalcktdestModuleType varchar2(10000);

portmoduletype number;

BEGIN

for rec in cktmodulenamecursor loop

   SELECT trunc(mod(rec.cktctpModuleType,power(2,16))) INTO portmoduletype FROM dual;

   if cktmodulenamecursor%ROWCOUNT = 1 then

    finalcktdestModuleType:= portmoduletype;

  else

    finalcktdestModuleType := finalcktdestModuleType||','||portmoduletype;

  end if;

end loop;

return finalcktdestModuleType;

END;

3.1.12 ConvertAxxIfIndex

CREATE OR REPLACE function convertaxxifindex(ifindex in number)

RETURN number

IS

newifindex NUMBER ;

firstByte INTEGER ;

secondByte INTEGER ;

thirdByte INTEGER ;

forthByte INTEGER ;

fifthByte INTEGER ;

result number(20);

BEGIN

if ifindex = 0 then

    return 0;

else

    newifindex := ifindex - 1;

end if;

fifthByte := mod(newifindex, 4);

newifindex := floor(newifindex / 4);

forthByte := mod(newifindex, 8);

newifindex := floor(newifindex / 8);

thirdByte := mod(newifindex, 4);

newifindex := floor(newifindex / 4);

--secondByte := mod(newifindex, 4) + 1;

secondByte := mod(newifindex, 3) + 1;

newifindex := floor(newifindex / 3);

--firstByte := mod(newifindex, 4) + 1;

firstByte := mod(newifindex, 3) + 1;

result := 0;

result := result + (firstByte * POWER(2,56));

result := result + (secondByte * POWER(2,48));

result := result + (thirdByte * POWER(2,40));

result := result + (forthByte * POWER(2,32));

result := result + (fifthByte * POWER(2,24));

RETURN result;

END convertaxxifindex;

3.1.13 ConvertLinkModuleType

CREATE OR REPLACE function convertLinkModuleType (aModuleType number, aModelType number)

RETURN number

AS

    finalModuleType number(8);

BEGIN

    if (aModelType in (3,14,5,24,18,30)) then

        if(aModuleType < 0 or aModuleType > 2000) then

            finalModuleType := aModuleType;

        else

            finalModuleType := aModuleType * power(2, 16);

        end if;

    else

        finalModuleType := aModuleType;

    end if;

    return finalModuleType;

END;

3.1.14 CountCoSPh

CREATE OR REPLACE FUNCTION countCosPh(cosPhloc IN NUMBER)

RETURN NUMBER

IS CURSOR cntPh

IS SELECT UNIQUEID FROM TOPOLOGY_CIRCUIT_VIEW

WHERE SRCPHYSICALLOC=cosPhloc OR DESTPHYSICALLOC=cosPhloc;

BEGIN

FOR rec IN cntPh LOOP

IF cntPh%ROWCOUNT = 1 THEN       return 1;

ELSE       return 0;

END IF;   END LOOP;   return 0;

END countCosPh; / show errors;

3.1.15 CTMDuration

create or replace function ctmduration(p_ctmduration in number)

return varchar2

IS

       CTMDuration_time varchar2(50);

       dddd  varchar2(4);

       hh    number:=0;

       mm    number:=0;

       ss    number:=0;

       hh_c    varchar2(4);

       mm_c    varchar2(4);

       ss_c    varchar2(4);

BEGIN

       IF (p_ctmduration = 0 ) THEN

          CTMDuration_time:='';

       ELSE

          SELECT trunc(p_ctmduration) INTO dddd FROM dual;

          SELECT trunc((p_ctmduration-dddd)*24) INTO hh FROM dual;

          IF ( hh <10 and hh>0 ) THEN

             hh_c:='0'||hh||'h:';

          ELSIF (hh > 10) THEN

             hh_c:=hh||'h:';

          END IF;

          SELECT trunc((p_ctmduration-dddd)*24*60)-hh*60 INTO mm FROM dual;

          IF (mm <10 and mm>0) THEN

             mm_c:='0'||mm||'m:';

          ELSIF (mm>10) THEN

             mm_c:=mm||'m:';

          END IF;

          SELECT trunc((p_ctmduration-dddd)*24*60*60)-hh*60*60-mm*60 INTO ss from dual;

          IF (ss < 10 and ss>0 ) THEN

              ss_c:='0'||ss||'s';

          ELSIF (ss>10) THEN

              ss_c:=ss||'s';

          END IF;

          IF (dddd=0 or dddd is null) THEN

              CTMDuration_time:=rtrim(hh_c||mm_c||ss_c,':');

          ELSE

              CTMDuration_time:=rtrim(dddd||'d:'||hh_c||mm_c||ss_c,':');

          END IF;

       END IF;

return CTMDuration_time;

END ctmduration;

3.1.16 DisplayIf

The displayif function is used to translate neifindex.

CREATE or REPLACE function displayif (neifindex in number)

RETURN varchar2

IS

   iftype       number;

   if1          number;

   if2          number;

   o_if2        number;

   if3          number;

   o_if3        number;

   ifstring     varchar2(10);

   o_interface    varchar2(20);

   i_neifindex  number;

BEGIN

   IF neifindex < 0 THEN

      i_neifindex := neifindex+power(2,32);

   ELSE

      i_neifindex:=neifindex;

   END IF;

   SELECT trunc(i_neifindex/power(2,24)) INTO iftype FROM dual;

   SELECT decode(iftype,1,'System',

                        2,'Equipment',

                        3,'BITS',

                        4,'Sync-NE',

                        5,'Env Alarm',

                        6,'SDCC',

                        7,'Env CTL',

                        8,'Chassis',

                        9,'Ext-Sync',

                        10,'AIP',

                        11,'FAN',

                        12,'Port',

                        13,'CRS',

                        14,'RSMS',

                        15,'STM1E',

                        20,'EC-N',

                        21,'OC-N',

                        30,'DS-0',

                        31,'DS-1',

                        32,'DS-2',

                        33,'DS-3',

                        35,'STSRNG',

                        36,'AICI_AEP',

                        37,'AICI_AIE',

                        38,'UDC_A',

                        39,'UDC_B',

                        40,'VT-1',

                        45,'STS',

                        47,'UCP_CKT',

                        48,'UCP_IPCC',

                        49,'UCP_NBR',

                        55,'Ethernet',

                        56,'Ethernet-G',

                        57,'Ethernet-Brg',

                        58,'Ethernet-gige',

                        60,'PPP',

                        63,'E4',

                        65,'ATM',

                        66,'AAL5',

                        70,'VIDEO',

                        75,'XTC',

                        76,'CAP',

                        77,'CHANNEL',

                        80,'STM',

                        81,'E1',

                        82,'E3',

                        83,'DS3I',

                        90,'GENERIC',

                        100,'PIRELLI',

                        101,'STS',

                        102,'STS1',

                        103,'STS3',

                        104,'STS6',

                        105,'STS9',

                        106,'STS12',

                        107,'STS24',

                        108,'STS48',

                        109,'STS192',

                        110,'STS254',

                        111,'VC4-2C',

                        112,'VC4-3C',

                        113,'VC4-4C',

                        114,'VC4-8C',

                        115,'VC4-16C',

                        116,'VC4-64C',

                        117,'VC11',

                        118,'VC4',

                        119,'VC3',

                        120,'VC12',

                        201,'SER-DS1',

                        202,'E1-Port',

                        203,'SER-E1',

                        204,'STM1',

                        205,'VC4',

                        206,'VC3',

                        207,'VC2',

                        208,'VC12'

     INTO ifstring FROM dual;

     IF (ifstring='Equipment') THEN

         o_interface:= 'Equipment';

     ELSE

         SELECT trunc(mod(i_neifindex,power(2,8))) INTO if3 FROM dual;

         SELECT decode(if3,0,'',if3) INTO o_if3 FROM dual;

         SELECT mod(trunc(i_neifindex/power(2,8)),power(2,8))  INTO if2 FROM dual;

         SELECT decode(if2,0,'',if2) INTO o_if2 FROM dual;

         SELECT mod(trunc(i_neifindex/power(2,16)),power(2,8)) INTO if1 FROM dual;

         IF ( if1 != 0 ) THEN

             o_interface := ifstring||' #'||if1||' '||o_if2||' '||o_if3;

         ELSE

             o_interface := ifstring||' '||o_if2||' '||o_if3;

         END IF;

     END IF;

RETURN o_interface;

END displayif;

3.1.17 DisplayIPAll

CREATE OR REPLACE

function  displayIpAll (NEIpaddr6 in raw, NEIpaddr in number, ipv6 in number)

return varchar2

IS

       first_sec    number;

       second_sec   number;

       third_sec    number;

       fourth_sec   number;

       ipaddr       varchar2(100);

       neipaddr_o   number;

       isipv6       number;

BEGIN

--dbms_output.put_line(neipaddr);

if ipv6 = 1 then

   select rawtohex(NEIpaddr6) into ipaddr from dual;

else

  if neipaddr < 0 then

     neipaddr_o:=NEIpaddr+power(2,32);

  else

     neipaddr_o:=NEIpaddr;

  end if;

  SELECT trunc(mod(NEIPaddr_o/power(2,24), power(2,8))) into first_sec from dual;

  SELECT trunc(mod(NEIPaddr_o/power(2,16), power(2,8))) INTO  second_sec from dual;

  SELECT trunc(mod(NEIPaddr_o/power(2,8),power(2,8))) INTO third_sec from dual;

  SELECT trunc(mod(NEIPaddr_o,power(2,8))) INTO fourth_sec from dual;

  ipaddr:=first_sec||'.'||second_sec||'.'||third_sec||'.'||fourth_sec;

end if;

RETURN ipaddr;

END displayIpAll;

3.1.18 DisplayIP

Create or replace function  displayIp (NEIpaddr in number)

return varchar2

IS

       first_sec    number;

       second_sec   number;

       third_sec    number;

       fourth_sec   number;

       ipaddr       varchar2(100);

       neipaddr_o   number;

	isipv6 	number;

BEGIN

--dbms_output.put_line(neipaddr);

if neipaddr < 0 then

   neipaddr_o:=neipaddr+power(2,32);

else

   neipaddr_o:=neipaddr;

end if;

SELECT trunc(mod(NEIPaddr_o/power(2,24), power(2,8))) into first_sec from dual;

SELECT trunc(mod(NEIPaddr_o/power(2,16), power(2,8))) INTO  second_sec from dual;

SELECT trunc(mod(NEIPaddr_o/power(2,8),power(2,8))) INTO third_sec from dual;

SELECT trunc(mod(NEIPaddr_o,power(2,8))) INTO fourth_sec from dual;

ipaddr:=first_sec||'.'||second_sec||'.'||third_sec||'.'||fourth_sec;

RETURN ipaddr;

END displayip;

rem

rem return the string of the IP address in the two format ipv4 or ipv6

rem

3.1.19 DisplayPhyLoc

The displayphloc function is used to determine the physical location.

Create or replace function  displayphyloc (physicalloc in number)

return varchar2

IS

    o_physicalloc     varchar2(40);

    nemodelindex      number;

    port              number;

    slot              number;

    pim               number;

    ppm               number;

    rack              number;

    shelf             number;

    nemodel           varchar2(30);

BEGIN

SELECT trunc(mod(physicalloc,power(2,10))) INTO  port from dual;

SELECT mod(trunc(physicalloc/power(2,10)),power(2,6)) INTO ppm from dual;

SELECT mod(trunc(physicalloc/power(2,16)),power(2,8)) INTO pim from dual;

SELECT mod(trunc(physicalloc/power(2,24)),power(2,8)) INTO slot from dual;

SELECT mod(trunc(physicalloc/power(2,32)),power(2,8)) INTO shelf from dual;

SELECT mod(trunc(physicalloc/power(2,40)),power(2,8)) INTO rack from dual;

o_physicalloc :='Rack:'||rack||',Shelf:'||shelf||',Slot:'||slot||',Port:'||port;

RETURN o_physicalloc;

END displayphyloc;

3.1.20 EqptInfoPort

CREATE or replace function eqptinfoport(NEDBACCESSID_param number,

 PHYSICALLOC_param number,

 IFINDEX_param     number,

 MODULEINDEX_param  number

) return varchar2

is

 portnameselectedcol200 varchar2(4000);

 portnameselectedcol3 varchar2(4000);

 calc_OBJECTINDEX number;

 PHYSICALLOC_temp number;

 NEMODELTYPE_param number;

 NEMODELINDEX_param number;

 calc_Ifindex number(20);

 SlotModuleType number;

 PortModuleType number;

 PpmModuleType number;

BEGIN

  begin

  select

        NEMODELTYPE,

        NEMODELINDEX

  into

        NEMODELTYPE_param,

        NEMODELINDEX_param

   from ne_info_table

  where nedbaccessid = NEDBACCESSID_param;

  exception

    when no_data_found then

     null;

 end;

 SlotModuleType:= getSlotModuleType(MODULEINDEX_param);

 SELECT trunc(mod(MODULEINDEX_param,power(2,16))) INTO PortModuleType FROM dual;

 PpmModuleType:= getPpm(PHYSICALLOC_param);

 --create_file('/oraclesw9i','testf.log', 'NEMODELTYPE_param 
:'||NEMODELTYPE_param||'NEMODELINDEX_param :'||NEMODELINDEX_param);

 if (SlotModuleType = 157 or SlotModuleType = 146 or SlotModuleType = 232 or 
SlotModuleType = 233 or SlotModuleType = 234) then

     calc_OBJECTINDEX:= mapobjectIndex(neModelType_param , nemodelIndex_param , 
getport(PHYSICALLOC_param) ,0, PortModuleType);

 else

     if (PpmModuleType = 63) then

     -- ppm not used;

         calc_OBJECTINDEX:= mapobjectIndex(neModelType_param , nemodelIndex_param , 
getport(PHYSICALLOC_param) ,0, SlotModuleType);

     else

         calc_OBJECTINDEX:= mapobjectIndex(neModelType_param , nemodelIndex_param , 
PpmModuleType ,0, SlotModuleType);

     end if;

 -- append_file('/oraclesw9i', 'testf.log', 'calc_OBJECTINDEX: '||calc_OBJECTINDEX);

 --  append_file('/oraclesw9i','testf.log', 'PHYSICALLOC_param:'||PHYSICALLOC_param);

  -- append_file('/oraclesw9i','testf.log','calc_Ifindex: '||calc_Ifindex);

 PHYSICALLOC_temp :=  PHYSICALLOC_param;

 if  ( SlotModuleType = 47 ) then

     PHYSICALLOC_temp := getPort_xtc(PHYSICALLOC_param, SlotModuleType);

end if;

  if (NEMODELTYPE_param = 38) then

   if     (SlotModuleType = 233) then

      if ( calc_OBJECTINDEX = 341 or calc_OBJECTINDEX = 342) then

        PHYSICALLOC_temp := PHYSICALLOC_temp - 28;

      end if;

   elsif  (SlotModuleType = 234) then

      if ( calc_OBJECTINDEX = 341 or calc_OBJECTINDEX = 342) then

        PHYSICALLOC_temp := PHYSICALLOC_temp - 84;

      end if;

   end if;

 end if;

        begin

                select col200,col3 into portnameselectedcol200,portnameselectedcol3

                from eqpt_info_table

                where NEDBACCESSID = NEDBACCESSID_param

                and   PHYSICALLOC      = PHYSICALLOC_temp

                and   IFINDEX          = 0

                and   MODULETYPE       = SlotModuleType

                and   OBJECTINDEX      = calc_OBJECTINDEX;

        exception

                when no_data_found then

                   null;

       end;

       if (calc_OBJECTINDEX = 193 or calc_OBJECTINDEX = 194) then

          return portnameselectedcol3;

       end if;

       return portnameselectedcol200;

END;

3.1.21 Get_Enet_Value

CREATE OR REPLACE function get_enet_value(

   param1  IN  number,

   param2  IN  number,

   param3  IN  number default -4,

   param4  IN  number default -4,

   param5  IN  number default -4,

   param6  IN  number default -4)

return number

IS

       r_value number  ;

       val1 number  ;

       val2 number  ;

       val3 number  ;

       val4 number  ;

       val5 number  ;

       val6 number  ;

BEGIN

     IF(param3 =-4 and param4 =-4 and param5 =-4 and param6 =-4) THEN

     	IF(param1 < 0 and param2 < 0) THEN

     	   	IF ( (param1=param2)) THEN

            		r_value:=param1;

            	ELSIF (param1 = -3 or param2 = -3)THEN

			r_value:= -3;

		ELSIF ( param1 = -2 or param2 = -2 ) THEN

            		r_value:= -2;

     		ELSIF ( param1 = -1 or param2 = -1 ) THEN

            		r_value:= -1;

     		END IF;

     		return r_value;

     	END IF;

     ELSIF((param3 > -4) and (param4 =-4) and (param5 =-4) and (param6 =-4)) THEN

     	IF(param1 < 0 and param2 < 0 and param3 < 0) THEN

     		IF ( (param1=param2) and (param2=param3) ) THEN

            		r_value:=param1;

            	ELSIF (param1 = -3 or param2 = -3 or param3 = -3)THEN

			r_value:= -3;

		ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 ) THEN

            		r_value:= -2;

     		ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 ) THEN

            		r_value:= -1;

     		END IF;

     		return r_value;

     	END IF;

     ELSIF((param3 > -4) and (param4 > -4) and (param5 =-4) and (param6 =-4)) THEN

     	IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0) THEN

     		IF ( ((param1=param2) and (param2=param3) and (param3=param4))) THEN

            		r_value:=param1;

            	ELSIF (param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3)THEN

			r_value:= -3;

		ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 ) THEN

            		r_value:=-2;

     		ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 ) THEN

            		r_value:=-1;

     		END IF;

     		return r_value;

     	END IF;

     ELSIF((param3 > -4) and (param4 > -4) and (param5 >-4) and (param6 =-4)) THEN

     	IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0 and param5 < 0) THEN

     		IF ( ((param1=param2) and (param2=param3) and (param3=param4) and 
(param4=param5))) THEN

            		r_value:=param1;

            	ELSIF (param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3 or param5 
=-3)THEN

			r_value:= -3;

		ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 or param5 = -2) 
THEN

            		r_value:=-2;

     		ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 or param5 = -1) 
THEN

            		r_value:=-1;

            	END IF;

     		return r_value;

     	END IF;

     ELSIF((param3 > -4) and (param4 > -4) and (param5 >-4) and (param6 >-4)) THEN

	IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0 and param5 < 0 and param6 < 
0) THEN

		IF ( ((param1=param2) and (param2=param3) and (param3=param4) and (param4=param5) 
and (param5=param6))) THEN

            		r_value:=param1;

            	ELSIF(param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3 or param5 =-3 
or param6=-3) THEN

			r_value:= -3;

		ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 or param5 = -2 or 
param6 = -2) THEN

            		r_value:=-2;

     		ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 or param5 = -1 or 
param6 = -1) THEN

            		r_value:=-1;

	     	END IF;

	     	return r_value;

	END IF;

     END IF;

     IF ( param1 <0 ) THEN

     val1:= 0;

     ELSE

     val1:= param1;

     END IF;

     IF ( param2 <0 ) THEN

     val2:= 0;

     ELSE

     val2:= param2;

     END IF;

     IF ( param3 <0 ) THEN

     val3:= 0;

     ELSE

     val3:= param3;

     END IF;

     IF ( param4 <0 ) THEN

     val4:= 0;

     ELSE

     val4:= param4;

     END IF;

     IF ( param5 <0 ) THEN

     val5:= 0;

     ELSE

     val5:= param5;

     END IF;

     IF ( param6 <0 ) THEN

     val6:= 0;

     ELSE

     val6:= param6;

     END IF;

     r_value:=val1+val2+val3+val4+val5+val6;

return r_value;

END get_enet_value;

3.1.22 Get_Enet_Value2

CREATE OR REPLACE function get_enet_value2(

   param1  IN  number,

   param2  IN  number,

   param3  IN  number default -4,

   param4  IN  number default -4,

   param5  IN  number default -4,

   param6  IN  number default -4)

return varchar

IS

       r_value varchar(25)  ;

       val1 number  ;

       val2 number  ;

       val3 number  ;

       val4 number  ;

       val5 number  ;

       val6 number  ;

BEGIN

     IF(param3 =-4 and param4 =-4 and param5 =-4 and param6 =-4) THEN

     	IF(param1 < 0 and param2 < 0) THEN

     	   	IF ( (param1=param2)) THEN

     	   		r_value:=to_char(param1);

            	ELSIF (param1 = -3 or param2 = -3)THEN

			r_value:=to_char(-3);

		ELSIF ( param1 = -2 or param2 = -2 ) THEN

            		r_value:=to_char(-2);

     		ELSIF ( param1 = -1 or param2 = -1 ) THEN

            		r_value:=to_char(-1);

     		END IF;

     		return r_value;

     	END IF;

     ELSIF((param3 > -4) and (param4 =-4) and (param5 =-4) and (param6 =-4)) THEN

     	IF(param1 < 0 and param2 < 0 and param3 < 0) THEN

     		IF ( (param1=param2) and (param2=param3) ) THEN

            		r_value:=to_char(param1);

            	ELSIF (param1 = -3 or param2 = -3 or param3 = -3)THEN

			r_value:=to_char(-3);

		ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 ) THEN

            		r_value:=to_char(-2);

     		ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 ) THEN

            		r_value:=to_char(-1);

     		END IF;

     		return r_value;

     	END IF;

     ELSIF((param3 > -4) and (param4 > -4) and (param5 =-4) and (param6 =-4)) THEN

     	IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0) THEN

     		IF ( ((param1=param2) and (param2=param3) and (param3=param4))) THEN

            		r_value:=to_char(param1);

            	ELSIF (param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3)THEN

			r_value:=to_char(-3);

		ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 ) THEN

            		r_value:=to_char(-2);

     		ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 ) THEN

            		r_value:=to_char(-1);

     		END IF;

     		return r_value;

     	END IF;

     ELSIF((param3 > -4) and (param4 > -4) and (param5 >-4) and (param6 =-4)) THEN

     	IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0 and param5 < 0) THEN

     		IF ( ((param1=param2) and (param2=param3) and (param3=param4) and 
(param4=param5))) THEN

            		r_value:=to_char(param1);

            	ELSIF (param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3 or param5 
=-3)THEN

			r_value:=to_char(-3);

		ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 or param5 = -2) 
THEN

            		r_value:=to_char(-2);

     		ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 or param5 = -1) 
THEN

            		r_value:=to_char(-1);

            	END IF;

     		return r_value;

     	END IF;

     ELSIF((param3 > -4) and (param4 > -4) and (param5 >-4) and (param6 >-4)) THEN

	IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0 and param5 < 0 and param6 < 
0) THEN

		IF ( ((param1=param2) and (param2=param3) and (param3=param4) and (param4=param5) 
and (param5=param6))) THEN

            		r_value:=to_char(param1);

            	ELSIF(param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3 or param5 =-3 
or param6=-3) THEN

			r_value:=to_char(-3);

		ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 or param5 = -2 or 
param6 = -2) THEN

            		r_value:=to_char(-2);

     		ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 or param5 = -1 or 
param6 = -1) THEN

            		r_value:=to_char(-1);

	     	END IF;

	     	return r_value;

	END IF;

     END IF;

     IF ( param1 <0 ) THEN

     val1:= 0;

     ELSE

     val1:= param1;

     END IF;

     IF ( param2 <0 ) THEN

     val2:= 0;

     ELSE

     val2:= param2;

     END IF;

     IF ( param3 <0 ) THEN

     val3:= 0;

     ELSE

     val3:= param3;

     END IF;

     IF ( param4 <0 ) THEN

     val4:= 0;

     ELSE

     val4:= param4;

     END IF;

     IF ( param5 <0 ) THEN

     val5:= 0;

     ELSE

     val5:= param5;

     END IF;

     IF ( param6 <0 ) THEN

     val6:= 0;

     ELSE

     val6:= param6;

     END IF;

     r_value:=to_char(val1+val2+val3+val4+val5+val6);

return r_value;

END get_enet_value2;

3.1.23 GetBccBwInfo

CREATE OR REPLACE FUNCTION getBccBwInfo(neId IN NUMBER, phLoc IN NUMBER, objIdx IN NUMBER, 
COLUMN IN VARCHAR2)

RETURN VARCHAR2

IS

       retVal  VARCHAR2(256);

BEGIN

       SELECT COL16 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND 
ObjectIndex = objIdx AND

PHYSICALLOC = phLoc AND COL2 = COLUMN;

       RETURN retVal;

END getBccBwInfo;

3.1.24 GetBccClassInfo

CREATE OR REPLACE FUNCTION getBccClassInfo(neId IN NUMBER, phLoc IN NUMBER, objIdx IN 
NUMBER, COLUMN IN VARCHAR2)

RETURN VARCHAR2

IS

       retVal  VARCHAR2(256);

BEGIN

       SELECT COL19 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND 
ObjectIndex = objIdx AND

PHYSICALLOC = phLoc AND COL2 = COLUMN;

       RETURN retVal;

END getBccClassInfo;

3.1.25 GetBccClassRPR80217

CREATE OR REPLACE FUNCTION getBccClassRPR80217(neId IN NUMBER, phLoc IN NUMBER, objIdx IN 
NUMBER, COLUMN IN NUMBER)

RETURN NUMBER

IS

       retVal  NUMBER(3);

BEGIN

    IF (COLUMN = 1) THEN -- Select column called COL1

      SELECT COL1 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND 
ObjectIndex = objIdx AND

PHYSICALLOC = phLoc AND IFINDEX = 0;

    END IF;

    IF (COLUMN = 2) THEN -- Select column called COL2

      SELECT COL2 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND 
ObjectIndex = objIdx AND

PHYSICALLOC = phLoc AND IFINDEX = 0;

    END IF;

    IF (COLUMN = 3) THEN -- Select column called COL3

      SELECT COL3 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND 
ObjectIndex = objIdx AND

PHYSICALLOC = phLoc AND IFINDEX = 0;

    END IF;

    RETURN retVal;

END getBccClassRPR80217;

3.1.26 GetBccCosCommitGrp

CREATE OR REPLACE FUNCTION getBccCosCommitGrp(neId IN NUMBER, phLoc IN NUMBER, infoNum IN 
NUMBER, objIdx IN NUMBER)

RETURN VARCHAR2

IS

       retVal  VARCHAR2(256);

       ifIdx   NUMBER(20);

BEGIN

     IF infoNum = 0 THEN -- cos

       SELECT COL2 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND 
ObjectIndex=objIdx AND

PHYSICALLOC=phLoC AND COL2 IS NOT NULL;

     END IF;

     IF infoNum = 11 THEN -- Multicast Group 1

       ifIdx := getIfCosGrp(neId, phLoc, 1);

       SELECT COL4 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND 
ObjectIndex=objIdx AND

PHYSICALLOC=phLoC AND IFINDEX=ifIdx AND COL4 IS NOT NULL;

     END IF;

     IF infoNum = 12 THEN -- Class Off service Group 1

       ifIdx := getIfCosGrp(neId, phLoc, 1);

       SELECT COL3 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND 
ObjectIndex=objIdx AND

PHYSICALLOC=phLoC AND IFINDEX=ifIdx AND COL3 IS NOT NULL;

     END IF;

     IF infoNum = 21 THEN -- Multicast Group 2

       ifIdx := getIfCosGrp(neId, phLoc, 2);

       SELECT COL4 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND 
ObjectIndex=objIdx AND

PHYSICALLOC=phLoC AND IFINDEX=ifIdx AND COL4 IS NOT NULL;

     END IF;

     IF infoNum = 22 THEN -- Class Off service Group 2

       ifIdx := getIfCosGrp(neId, phLoc, 2);

       SELECT COL3 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND 
ObjectIndex=objIdx AND

PHYSICALLOC=phLoC AND IFINDEX=ifIdx AND COL3 IS NOT NULL;

     END IF;

     return retVal;

END getBccCosCommitGrp;

3.1.27 GetBccCosInfo

CREATE OR REPLACE FUNCTION getBccCosInfo(neId IN NUMBER, phLoc IN NUMBER, objIdx IN 
NUMBER, COLUMN IN VARCHAR2)

RETURN VARCHAR2

IS

       retVal  VARCHAR2(256);

BEGIN

       SELECT COL26 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND 
ObjectIndex = objIdx AND

PHYSICALLOC = phLoc AND COL2 = COLUMN;

       RETURN retVal;

END getBccCosInfo;

3.1.28 GetBGFromBGObj

Create or replace function  getBGFromBGObj(neifindex in number)

return number

IS

if1 number;

i_neifindex number;

BEGIN

IF neifindex < 0 THEN

i_neifindex := neifindex+power(2,32);

ELSE

i_neifindex:=neifindex;

END IF;

SELECT mod(trunc(i_neifindex/power(2,16)),power(2,8)) INTO if1 FROM dual;

RETURN if1;

END getBGFromBGObj;

3.1.29 GetBGFromQoSObj

Create or replace function  getBGFromQoSObj (neifindex in number)

return number

IS

if2 number;

o_if2 number;

i_neifindex number;

BEGIN

IF neifindex < 0 THEN

i_neifindex := neifindex+power(2,32);

ELSE

i_neifindex:=neifindex;

END IF;

SELECT mod(trunc(i_neifindex/power(2,8)),power(2,8)) INTO if2 FROM dual;

SELECT decode(if2,0,'',if2) INTO o_if2 FROM dual;

RETURN o_if2;

END getBGFromQoSObj;

3.1.30 GetCktDestNodeId

CREATE or replace function getCktDestNodeId (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return number

IS

    cursor cktnodeidcursor is

    select CktCtpNodeId

    from circuit_ctp_tbl

    where CktNodeId = CKTNODEIDIN

      and CktUniqueId = CKTUNIQUEIDIN

      and ctp_type = 2;

    finalinodeids varchar2(10000);

BEGIN

    for rec in cktnodeidcursor loop

        if cktnodeidcursor%ROWCOUNT = 1 then

            finalinodeids:= rec.CktCtpNodeId;

        else

            finalinodeids := finalinodeids||','||rec.CktCtpNodeId;

        end if;

    end loop;

    return finalinodeids;

END getCktDestNodeId;

3.1.31 GetCktSecSrcIfIndex

CREATE or replace function getCktSecSrcIfIndex (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return number

IS

    o_CktSecSrcIfIndex number;

BEGIN

    select CktCtpIfIndex into o_CktSecSrcIfIndex

    from circuit_ctp_tbl

    where CktCtpNodeId = CKTNODEIDIN

      and CktUniqueId = CKTUNIQUEIDIN

      and ctp_type = 1;

    return o_CktSecSrcIfIndex;

END getCktSecSrcIfIndex;

3.1.32 GetCktSecSrcPhysicalLoc

CREATE or replace function getCktSecSrcPhysicalLoc (CKTNODEIDIN number,CKTUNIQUEIDIN 
number)return number

IS

    o_CktSecSrcPhysicalLoc number;

BEGIN

    select CktCtpPhysicalLoc into o_CktSecSrcPhysicalLoc

    from circuit_ctp_tbl

    where CktCtpNodeId = CKTNODEIDIN

      and CktUniqueId = CKTUNIQUEIDIN

      and ctp_type = 1;

    return o_CktSecSrcPhysicalLoc;

END getCktSecSrcPhysicalLoc;

3.1.33 GetCktSecSrcPortName

CREATE or replace function getCktSecSrcPortName (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    o_CktSecSrcPortName varchar2(64);

BEGIN

    select CKTCTPPORTNAME into o_CktSecSrcPortName

    from circuit_ctp_tbl

    where CktNodeId = CKTNODEIDIN

      and CktUniqueId = CKTUNIQUEIDIN

      and ctp_type = 1;

    return o_CktSecSrcPortName;

END getCktSecSrcPortName;

3.1.34 GetCktSrcIfIndex

CREATE or replace function getCktSrcIfIndex (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return number

IS

    o_CktSrcIfIndex number;

BEGIN

    select CktCtpIfIndex into o_CktSrcIfIndex

    from circuit_ctp_tbl

    where CktCtpNodeId = CKTNODEIDIN

      and CktUniqueId = CKTUNIQUEIDIN

      and ctp_type = 0;

    return o_CktSrcIfIndex;

END getCktSrcIfIndex;

3.1.35 GetCktSrcPhysicalLoc

CREATE or replace function getCktSrcPhysicalLoc (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return number

IS

    o_CktSrcPhysicalLoc number;

BEGIN

    select CktCtpPhysicalLoc into o_CktSrcPhysicalLoc

    from circuit_ctp_tbl

    where CktCtpNodeId = CKTNODEIDIN

      and CktUniqueId = CKTUNIQUEIDIN

      and ctp_type = 0;

    return o_CktSrcPhysicalLoc;

END getCktSrcPhysicalLoc;

3.1.36 GetCktSrcPortName

CREATE or replace function getCktSrcPortName (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    o_CktSrcPortName varchar2(64);

BEGIN

    select CKTCTPPORTNAME into o_CktSrcPortName

    from circuit_ctp_tbl

    where CktNodeId = CKTNODEIDIN

      and CktUniqueId = CKTUNIQUEIDIN

      and ctp_type = 0;

    return o_CktSrcPortName;

END getCktSrcPortName;

3.1.37 GetCollapsedPortname

CREATE or replace function getcollapsedPortname(CKTNODEIDIN number,CKTUNIQUEIDIN number)

return varchar2

IS

    cursor eqptcollpasedport is

    select  CktNodeId,

            CktCtpPhysicalLoc,

            CktCtpIfIndex,

            CktCtpModuleType

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID =CKTUNIQUEIDIN

    and CktNodeId is not null

    and CktCtpPhysicalLoc is not null

    and CktCtpIfIndex is not null

    and CktCtpModuleType is not null;

    finaleqptinfoport varchar2(10000);

    DestNedbaccessid varchar2(1000);

BEGIN

    for rec in eqptcollpasedport loop

        SELECT nedbaccessid INTO DestNedbaccessid

        FROM ne_info_table

        WHERE nenodeid= rec.CktNodeId and rec.CktNodeId != 0;

        if eqptcollpasedport%ROWCOUNT = 1 then

            finaleqptinfoport:= 
'@'||eqptinfoport(DestNedbaccessid,rec.CktCtpPhysicalLoc,rec.CktCtpIfIndex,rec.CktCtpModul
eType);

        else

            finaleqptinfoport := 
finaleqptinfoport||','||'@'||eqptinfoport(DestNedbaccessid,rec.CktCtpPhysicalLoc,rec.CktCt
pIfIndex,rec.CktCtpModuleType);

        end if;

    end loop;

    return finaleqptinfoport;

END;

3.1.38 GetEqptInfoPhysicalLoc

Create or replace function getEqptInfoPhysicalLoc(physicalLoc in number,moduleIndex in 
number)

return number

IS

    eqptphysicalLoc   number;

    eqptport          number;

    slot              number;

    port              number;

    pim               number;

    ppm               number;

    slotmoduletype    number;

    portmoduletype    number;

BEGIN

    slot:= getSlot(physicalLoc);

    port:= getPortWithPimPpm(physicalLoc);

    pim:= getPIM(physicalLoc);

    ppm:= getPPM(physicalLoc);

    slotmoduletype:=getSlotModuleType(moduleIndex);

    SELECT trunc(mod(moduleIndex,power(2,16))) INTO portmoduletype FROM dual;

    IF (pim = 0 AND slotmoduletype = 157 AND portmoduletype != 34 AND ppm = 0) THEN

        port:= port + 21;

        eqptphysicalLoc:= getPhysicalLoc(slot,port);

    ELSE

        eqptphysicalLoc:= physicalLoc;

    END IF;

RETURN eqptphysicalLoc;

END getEqptInfoPhysicalLoc;

3.1.39 GetIfCosGrp

CREATE OR REPLACE FUNCTION getIfCosGrp(neId IN NUMBER, phLoc IN NUMBER, first_last IN 
NUMBER )

  RETURN NUMBER

IS

    CURSOR MCursor IS

    SELECT IFINDEX

    FROM EQPT_INFO_TABLE

    WHERE NEDBACCESSID=neId AND OBJECTINDEX=189 AND PHYSICALLOC=phLoc AND COL3 IS NOT 
NULL;

    IfIndex NUMBER(20);

    find NUMBER(20);

BEGIN

    IF (first_last = 1) THEN -- find first

      find := 1;

    END IF;

    IF (first_last = 2) THEN -- find last

      find := 0;

    END IF;

    FOR rec IN MCursor LOOP

        IF MCursor%ROWCOUNT = first_last THEN

           IfIndex := rec.IFINDEX;

           return IfIndex;

        END IF;

        IfIndex := rec.IFINDEX;

    END LOOP;

    RETURN IfIndex;

END;

3.1.40 GetInterfaceId

create or replace function GetInterfaceId

       (p_nedbaccessid    IN number,

        p_moduleorIfIndex IN number,

        p_objectType	  IN number,

        p_type            IN varchar2)

return varchar2

IS

       o_interfaceId  varchar2(64);

       o_modeltype    number:=0;

BEGIN

       IF (p_moduleOrIfindex = 0 ) THEN

	    IF (p_objectType = 0 ) THEN

              select activevalue into o_interfaceId from ctm_config_table

		      where sectionname='transportManager' and propertyname='ems-sys-id';

	    ELSE

              SELECT nemodeltype INTO o_modeltype

              FROM ne_info_table

              WHERE nedbaccessid=p_nedbaccessid;

              IF (o_modeltype = 34) THEN

                 o_interfaceId:='N/A';

              ELSE

                 o_interfaceId:='0';

              END IF;

	    END IF;

       END IF;

       IF (p_moduleOrIfindex = -1) THEN

          o_interfaceId:='N/A';

       END IF;

       IF ((p_moduleOrIfindex != 0 ) and (p_moduleOrIfindex != -1)) THEN

           IF (p_nedbaccessid =0 ) THEN

              IF (p_type like 'Maximum login attempts exceeded%') THEN

                BEGIN

                 SELECT username INTO o_interfaceId

                   FROM user_table

                  WHERE userid=p_moduleorIfIndex;

                 exception

                  when no_data_found then

                  o_interfaceId:= displayIp(p_moduleOrIfindex);

                END;

              ELSIF (p_type like 'Server Monitor Threshold Crossed%') THEN

                    select displayname into o_interfaceId

                      FROM SERVER_PARAMETER_TABLE

                     WHERE ParameterIndex=p_moduleorIfIndex;

              ELSE

                 SELECT nesysid INTO o_interfaceId

                   FROM ne_info_table

                  WHERE nedbaccessid=p_moduleorIfIndex AND nemodeltype!=34;

              END IF;

           ELSE

               SELECT nemodeltype INTO o_modeltype

               FROM ne_info_table

               WHERE nedbaccessid=p_nedbaccessid;

               IF (o_modeltype = 34) THEN

          		  o_interfaceId:='N/A';

               ELSE

                  o_interfaceId:=p_moduleorIfIndex;

               END IF;

           END IF;

       END IF;

RETURN o_interfaceId;

END GetInterfaceId;

3.1.41 Get_Inventory_Note

CREATE OR REPLACE

FUNCTION "GET_INVENTORY_NOTE" (sn IN VARCHAR2) RETURN VARCHAR2

AS

    retVal  VARCHAR2(4000);

    cnt NUMBER;

BEGIN

  select count(*) into cnt from ONS15454_INVENTORY_NOTES where SERIALNUMBER = sn;

  if (cnt=0) THEN

    retVal:='';

  ELSE

    select NOTE into retVal from ONS15454_INVENTORY_NOTES where SERIALNUMBER = sn;

  END IF;

  RETURN retVal;

END GET_INVENTORY_NOTE;

3.1.42 GetLinkType

CREATE OR REPLACE function GetLinkType

       (p_circuitprot   IN number,

        p_circuittype IN number,

        p_linktype IN number,

        p_spanid IN number

return number

IS

       o_linkcount     number:=0;

       o_linktype_18   number:=18;

       o_linktype_13   number:=13;

       o_linktype_4    number:=4;

       o_linktype_0    number:=0;

       o_linktype      number:=p_linktype;

BEGIN

IF ((p_circuitprot = 5) OR (p_circuitprot = 6)) THEN

              select count(*) into o_linkcount from

               CIRCUIT_CTP_TBL ctp1, CIRCUIT_CTP_TBL ctp2, CIRCUIT_SPAN_BS_TBL spn, 
LINK_TABLE lnk, CIRCUIT_BS_TBL circuit

              where

               spn.CKTSPANSRCCTPACCESSID = ctp1.CTPACCESSID

AND

               spn.CKTSPANDSTCTPACCESSID = ctp2.CTPACCESSID

AND

               circuit.CKTNODEID = spn.CKTNODEID

AND

               circuit.CKTUNIQUEID = spn.CKTUNIQUEID

AND

                ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND 
ctp1.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE AND

                 ctp2.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND 
ctp2.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE )

OR

                ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND 
ctp1.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE AND

                 ctp2.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND 
ctp2.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE )

               AND lnk.linktype in (0, 4)

               AND spn.CKTSPANBSID = p_spanid;

                   if ( o_linkcount > 1 ) then

                   o_linktype := o_linktype_4;

               else

                   o_linktype := o_linktype_0;

               end if;

       else

               if (p_linktype = 4 ) THEN

                   o_linktype := o_linktype_0;

               end if;

       END IF;

if ( p_circuittype = 38 ) THEN

              select count(*) into o_linkcount from

               CIRCUIT_CTP_TBL ctp1, CIRCUIT_CTP_TBL ctp2, CIRCUIT_SPAN_BS_TBL spn, 
LINK_TABLE lnk, CIRCUIT_BS_TBL circuit

              where

               spn.CKTSPANSRCCTPACCESSID = ctp1.CTPACCESSID

AND

               spn.CKTSPANDSTCTPACCESSID = ctp2.CTPACCESSID

AND

               circuit.CKTNODEID = spn.CKTNODEID

AND

               circuit.CKTUNIQUEID = spn.CKTUNIQUEID

AND

                ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND 
ctp1.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE AND

                 ctp2.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND 
ctp2.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE )

OR

                ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND 
ctp1.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE AND

                 ctp2.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND 
ctp2.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE )

               AND lnk.linktype = o_linktype_18

               AND spn.CKTSPANBSID = p_spanid;

                   if ( o_linkcount > 0 ) then

                   o_linktype := o_linktype_18;

               else

                   select count(*) into o_linkcount from

                      CIRCUIT_CTP_TBL ctp1, CIRCUIT_CTP_TBL ctp2, CIRCUIT_SPAN_BS_TBL spn, 
LINK_TABLE lnk, CIRCUIT_BS_TBL circuit

                   where

                      spn.CKTSPANSRCCTPACCESSID = ctp1.CTPACCESSID

AND

                      spn.CKTSPANDSTCTPACCESSID = ctp2.CTPACCESSID

AND

                      circuit.CKTNODEID = spn.CKTNODEID

AND

                      circuit.CKTUNIQUEID = spn.CKTUNIQUEID

AND

                      (( ctp1.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND 
ctp1.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE AND

                           ctp2.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND 
ctp2.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE )

OR

                          ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND 
ctp1.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE AND

                            ctp2.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND 
ctp2.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE ))

                   AND lnk.linktype = o_linktype_13

                   AND spn.CKTSPANBSID = p_spanid;

                   if ( o_linkcount > 0 ) THEN

                      o_linktype := o_linktype_13;

                   end if;

               end if;

       END IF;

    return   o_linktype;

END GetLinkType;

3.1.43 GetLowBits

CREATE OR REPLACE function getLowBits (aNum number, numOfBits integer)

RETURN integer

AS

    result number;

    modVal number;

BEGIN

        modVal := power (2, numOfBits);

        result := mod (aNum, modVal);

        return result;

END;

3.1.44 GetMgx88xxMgmtState

create or replace function getMgx88xxMgmtState(p_isConnected in number)

return number

IS

      i_mstate      number;

BEGIN

      IF ( p_isConnected = 1 ) THEN

           i_mstate := 1;

      ELSE

           i_mstate := 2;

      END IF;

RETURN i_mstate;

END getMgx88xxMgmtState;

3.1.45 GetMgx88xxSyncMode

create or replace function getMgx88xxSyncMode(p_discoveryState in number)

return number

IS

      i_mode      number;

BEGIN

      IF ( p_discoveryState = 1 ) THEN

           i_mode := 5;

      ELSE IF ( p_discoveryState = 2 ) THEN

           i_mode := 1;

      ELSE IF ( p_discoveryState = 3 ) THEN

           i_mode := 2;

      ELSE IF ( p_discoveryState = 4 ) THEN

           i_mode := 3;

      ELSE

           i_mode := 1;

      END IF;

      END IF;

      END IF;

      END IF;

RETURN i_mode;

END getMgx88xxSyncMode;

3.1.46 GetMLcardAddnlInfo

This function indicates whether the ML card has been preprovisioned.

CREATE OR REPLACE function getMLcardAddnlInfo(AddnlInfo in varchar2, state in number)

RETURN varchar2

IS

BEGIN

       IF (state = 0) THEN

              RETURN 'EID-7077: ML Card was Pre-provisioned';

       END IF;

       RETURN AddnlInfo;

END getMLcardAddnlInfo;

3.1.47 GetModelTypeFromPhysicalLoc

CREATE OR REPLACE function getModelTypeFromPhysicalloc (aPHYSICALLOC number)

RETURN number

AS

    aModelType number(20);

BEGIN

    aModelType := removeLowBits(aPHYSICALLOC, 56);

    return aModelType;

END;

3.1.48 GetModuleName

create or replace function getModuleName(p_nedbaccessid in number, p_moduletype in number, 
p_eventType in number)

return varchar2

IS

      o_modulename   varchar2(64);

      i_modeltype      number;

BEGIN

      IF ( p_nedbaccessid = 0 or ( p_eventType >= 4 AND  p_moduletype = 9999) ) THEN

           o_modulename := 'N/A';

      ELSE

           SELECT nemodeltype INTO i_modeltype

             FROM ne_info_table

            WHERE nedbaccessid=p_nedbaccessid;

           SELECT ModuleName INTO o_modulename

             FROM module_type_table

            WHERE moduletype=p_moduletype

              AND modeltype=i_modeltype;

      END IF;

RETURN o_modulename;

Exception

      WHEN NO_DATA_FOUND

      THEN

      RETURN 'Unsupported';

END getModuleName;

3.1.49 GetModuleName_PM

create or replace function getModuleName_pm(p_nenodeid in number, p_moduletype in number)

return varchar2

IS

      o_modulename   varchar2(64);

      i_modeltype      number;

BEGIN

        IF (p_nenodeid=0 OR p_moduletype=0) THEN

           o_modulename:='SYSTEM';

        ELSE

           SELECT nemodeltype INTO i_modeltype

             FROM ne_info_table

            WHERE nenodeid=p_nenodeid;

           SELECT ModuleName INTO o_modulename

             FROM module_type_table

            WHERE moduletype=p_moduletype

              AND modeltype=i_modeltype;

       END IF;

RETURN o_modulename;

Exception

      WHEN NO_DATA_FOUND

      THEN

      RETURN 'Unsupported';

END getModuleName_pm;

3.1.50 GetModuleName1

create or replace function getModuleName1(p_nenodeid in number, p_moduletype in number)

return varchar2

IS

      o_modulename   varchar2(64);

      i_modeltype      number;

      p_slotmoduletype number;

BEGIN

        SELECT trunc(p_moduletype/power(2,16)) INTO p_slotmoduletype FROM dual;

        IF (p_nenodeid=0 OR p_slotmoduletype=0) THEN

           o_modulename:='SYSTEM';

        ELSE

           SELECT nemodeltype INTO i_modeltype

             FROM ne_info_table

            WHERE nenodeid=p_nenodeid;

           SELECT ModuleName INTO o_modulename

             FROM module_type_table

            WHERE moduletype=p_slotmoduletype

              AND modeltype=i_modeltype;

       END IF;

RETURN o_modulename;

Exception

      WHEN NO_DATA_FOUND

      THEN

      RETURN 'Unsupported';

END getModuleName1;

3.1.51 GetModuleName2

create or replace function getModuleName2 (p_moduletype number, p_modeltype number)

return varchar2

IS

    o_modulename   varchar2(64);

    p_slotmoduletype number;

BEGIN

    SELECT trunc(p_moduletype/power(2,16)) INTO p_slotmoduletype FROM dual;

    IF (p_slotmoduletype=0) THEN

        o_modulename:='SYSTEM';

    ELSE

        SELECT ModuleName INTO o_modulename

        FROM module_type_table

        WHERE moduletype=p_slotmoduletype

          AND modeltype=p_modeltype;

    END IF;

RETURN o_modulename;

Exception

    WHEN NO_DATA_FOUND

    THEN

    RETURN 'Unsupported';

END getModuleName2;

3.1.52 GetgetProtectMLcardConfigState

--This function returns the config status of a protected card, return -1 if not found.

CREATE OR REPLACE function getProtectMLcardConfigState(NEDbId in number, Slot in number)

RETURN number

IS

   confStValue number;

BEGIN

       IF (NEDbId = 0) THEN

              RETURN -1;

       END IF;

       Select configState into confStValue from MCard_Table where NeDbaccessID = NEDbId 
and SlotNumber = Slot;

       RETURN confStValue;

END getProtectMLcardConfigState;

3.1.53 GetNEDBAccessID

create or replace function getNEDBACCESSID(NodeId_param number)

return number

is

nedbaccessout number;

begin

  begin

	select

		nedbaccessid

			into nedbaccessout

		from

			ne_info_table

		where

			NENODEID = NodeId_param

			and NENODEID !=0;

  exception

		when no_data_found then

		null;

  end;

return nedbaccessout;

end;

show error;

rem

rem Function that return the IP address

rem

3.1.54 GetNEIPAddr

CREATE OR REPLACE function getNEIPAddr (p_NEDBAccessid number)

return raw

is

nedbIPaddr raw(16);

nedbIPaddrV4 raw(4);

c_nedbIPaddr number;

isipv6 number;

begin

  select is_ipv6 into isipv6 from ne_info_table where nedbaccessid = p_NEDBAccessid;

  if isipv6 = 1 then

     select neipv6addr into nedbIPaddr from ne_info_table where nedbaccessid = 
p_NEDBAccessid;

     return nedbIPaddr;

  else

     select neipaddr into c_nedbIPaddr from ne_info_table where  nedbaccessid = 
p_NEDBAccessid;

     -- dbms_output.put_line ('IPv4 =' || c_nedbIPaddr || 'IPv4 BIN =' || 
binary_utility.to_hex(c_nedbIPaddr));

     nedbIpaddrV4 := utl_raw.CAST_FROM_BINARY_INTEGER(c_nedbIPaddr);

     return nedbIPaddrV4;

  end if;

exception

      when no_data_found then

          return null;

end getNEIPAddr;

show error;

rem

rem Function that check if the IP is a IPv4 (return 0) or a IPv6 (return 1)

rem

3.1.55 GetObjectTypeFromIfIndex

CREATE OR REPLACE function getObjectTypeFromIfIndex (aIfindex number)

RETURN number

AS

    aObjectType number(10);

BEGIN

    if(sign(aIfindex) = -1) then

        aObjectType := removeLowBits(aIfindex+power(2,32), 24);

    else

        aObjectType := removeLowBits(aIfindex, 24);

    end if;

    return aObjectType;

END;

3.1.56 GetObjectTypeFromIfIndex2

CREATE OR REPLACE function getObjectTypeFromIfIndex2 ( amodelType number,aIfindex number)

RETURN number

AS

    aObjectType number(10);

BEGIN

    if(sign(aIfindex) = -1) then

        aObjectType := removeLowBits(aIfindex+power(2,32), 24);

    else

        aObjectType := removeLowBits(aIfindex, 24);

    end if;

    if (amodelType in (3, 14, 5, 29, 18, 30)) THEN

        select decode(aobjectType, 19, 5130,

                                   83, 5073,

                                       aobjectType+5000) into aobjectType from dual;

    end if;

    return aObjectType;

END;

3.1.57 GetOutIPAddr

CREATE OR REPLACE

function getOUTIPAddr (p_ossname varchar)

return raw

is

nedbIPaddr raw(16);

nedbIPaddrV4 raw(4);

c_nedbIPaddr number;

isipv6 number;

begin

  select is_ipv6 into isipv6 from oss_user_table where ossname = p_ossname;

  if isipv6 = 1 then

     select IPV6Addr into nedbIPaddr from oss_user_table where  ossname = p_ossname;

     return nedbIPaddr;

  else

     select IPAddress into c_nedbIPaddr from oss_user_table where   ossname = p_ossname;

     -- dbms_output.put_line ('IPv4 =' || c_nedbIPaddr || 'IPv4 BIN =' || 
binary_utility.to_hex(c_nedbIPaddr));

     nedbIpaddrV4 := utl_raw.CAST_FROM_BINARY_INTEGER(c_nedbIPaddr);

     return nedbIPaddrV4;

  end if;

exception

      when no_data_found then

          return null;

end getOUTIPAddr;

3.1.58 GetPhysicalLoc

Create or replace function getPhysicalLoc(slot in number,port in number)

return number

IS

    physicalLoc   number;

    physlot       number;

BEGIN

    physlot:= slot * power(2,16);

    physicalLoc:= physlot + port;

RETURN physicalLoc;

END getPhysicalLoc;

3.1.59 GetPhysicalLocWithPIMPPM

Create or replace function getPhysicalLocWithPimPpm(shelf in number, slot in number, pim 
in number, ppm in number, port in number)

  return number

IS

     physicalLoc   number;

     phyrack       number;

     physhelf      number;

     physlot       number;

     phypim        number;

     phyppm        number;

BEGIN

      phyrack:= 1 * power(2,40);

      physhelf:= shelf * power(2,32);

      physlot:= slot * power(2,24);

      phypim:= pim * power(2,16);

      phyppm:= ppm * power(2,10);

      physicalLoc:= phyrack + physhelf + physlot + phypim + phyppm + port;

RETURN physicalLoc;

END getPhysicalLocWithPimPpm;

3.1.60 GetPIM

Create or replace function  getPIM (physicalloc in number)

return number

IS

    pim                       number;

BEGIN

SELECT trunc(mod(trunc(physicalloc/power(2,16)),power(2,8))) INTO  pim from dual;

RETURN pim;

END getPIM;

3.1.61 GetPMParameterValue

CREATE OR REPLACE function GetPMParameterValue

       (p_paramvalue    IN number,

        l_paramvalue    IN number

return number

IS

       o_paramvalue  number;

BEGIN

       IF (p_paramvalue = -1 AND l_paramvalue <> -1) THEN

          o_paramvalue:= l_paramvalue;

       ELSIF (l_paramvalue = -1 AND p_paramvalue <> -1) THEN

          o_paramvalue:= p_paramvalue;

       ELSE

       	  o_paramvalue:= p_paramvalue;

       END IF;

RETURN o_paramvalue;

END GetPMParameterValue;

3.1.62 GetPolicyMapType

Create or replace function  getPolicyMapType(neifindex in number)

return number

IS

if1 number;

i_neifindex number;

BEGIN

IF neifindex < 0 THEN

i_neifindex := neifindex+power(2,32);

ELSE

i_neifindex:=neifindex;

END IF;

SELECT mod(trunc(i_neifindex/power(2,16)),power(2,8)) INTO if1 FROM dual;

RETURN if1;

END getPolicyMapType;

3.1.63 GetPort

Create or replace function  getPort (physicalloc in number)

return number

IS

    port                       number;

    ppm                        number;

BEGIN

    SELECT trunc(mod(physicalloc,power(2,10))) INTO  port from dual;

RETURN port;

END getPort;

3.1.64 GetPort_XTC

Create or replace function  getPort_xtc (physicalloc in number, moduletype_param  in 
number )

return number

IS

    ppm                        number;

    port                       number;

    final_physicalloc          number;

    two_bytes                  number := power(2,16);

BEGIN

ppm := getPPM(physicalloc);

port := getPort(physicalloc);

if ( moduletype_param = 47 ) then

 -- append_file('/oraclesw9i', 'testfjune9.log', 'Port No. Passed is  : '|| port);

 if ( port <= 28 ) then

   port := 1;

 else

    port := mod (port, 28);

    if (port = 0) then

        port := 28;

    end if;

  end if;

 -- append_file('/oraclesw9i', 'testfjune9.log', 'Returning port no. : '|| port);

end if;

final_physicalloc := trunc(physicalloc/two_bytes) * two_bytes;

final_physicalloc := final_physicalloc + port;

RETURN final_physicalloc;

END getPort_xtc;

3.1.65 GetPortModuleName

create or replace function getPortModuleName(p_nenodeid in number, p_moduletype in number)

return varchar2

IS

      o_modulename   varchar2(64);

      i_modeltype      number;

      p_portmoduletype number;

BEGIN

        SELECT trunc(mod(p_moduletype,power(2,16))) INTO p_portmoduletype FROM dual;

        IF (p_nenodeid=0 OR p_portmoduletype=0) THEN

           o_modulename:='SYSTEM';

        ELSE

          SELECT nemodeltype INTO i_modeltype

             FROM ne_info_table

            WHERE nenodeid=p_nenodeid;

           SELECT ModuleName INTO o_modulename

             FROM module_type_table

            WHERE moduletype=p_portmoduletype

              AND modeltype=i_modeltype;

       END IF;

RETURN o_modulename;

Exception

    WHEN NO_DATA_FOUND

    THEN

    RETURN 'Unsupported';

END getPortModuleName;

3.1.66 GetPortModuleName2

create or replace function getPortModuleName2(p_moduleindex number, p_modeltype number)

return varchar2

IS

    o_modulename   varchar2(64);

    p_portmoduletype number;

BEGIN

    SELECT mod(p_moduleindex, power(2,16))  INTO p_portmoduletype FROM dual;

    IF (p_portmoduletype=0 ) THEN

        o_modulename:= getModuleName2(p_moduleindex, p_modeltype);

    ELSE

        SELECT ModuleName INTO o_modulename

        FROM module_type_table

        WHERE moduletype=p_portmoduletype

              AND modeltype=p_modeltype;

    END IF;

RETURN o_modulename;

Exception

    WHEN NO_DATA_FOUND

    THEN

    RETURN 'Unsupported';

END getPortModuleName2;

3.1.67 GetPortName

CREATE or REPLACE function GetPortName

          (p_nedbaccessid IN number,

           p_physicalloc IN number,

           p_ifindex IN number,

           p_moduletype IN number)

RETURN varchar2

IS

   iftype       number;

   i_ifindex    number;

   o_portname   varchar2(256);

   model_type	number;

   module_type  number;

   physical_loc	number;

   ppm		number;

   slot 	number;

   port		number;

BEGIN

   IF p_ifindex < 0 THEN

      i_ifindex := p_ifindex+power(2,32);

   ELSE

      i_ifindex:=p_ifindex;

   END IF;

   module_type := p_moduletype;

   ppm := GetPpm(p_physicalloc);

   SELECT round(i_ifindex/power(2,24)) INTO iftype FROM dual;

   SELECT nemodeltype INTO model_type FROM ne_info_table WHERE nedbaccessid = 
p_nedbaccessid;

   BEGIN

   IF ( iftype = 31 and p_moduletype=47 ) THEN    /* 31=ds1 47=XTC */

        SELECT col200 INTO o_portname

          FROM eqpt_info_table

         WHERE nedbaccessid=p_nedbaccessid

           AND physicalloc=p_physicalloc

           AND objectindex=204

           AND moduletype=p_moduletype;

   ELSIF (iftype = 33 and p_moduletype = 47 ) THEN /* 33=ds3 47=XTC */

        SELECT col200 INTO o_portname

          FROM eqpt_info_table

         WHERE nedbaccessid=p_nedbaccessid

           AND physicalloc=p_physicalloc

           AND objectindex=208

           AND moduletype=p_moduletype;

   ELSIF (p_moduletype = 34 ) THEN /* 34 = ds1 port on XTC or 15310*/

        SELECT col200 INTO o_portname

          FROM eqpt_info_table

         WHERE nedbaccessid=p_nedbaccessid

           AND physicalloc=p_physicalloc

           AND objectindex in (204,340) /* 204=Ds1Line 340=Ds1LineModel */

           AND moduletype in (47,233,234); /* 47=XTC 233=DS1_28_DS3_EC1_3 
234=DS1_84_DS3_EC1_3 */

   ELSIF (p_moduletype = 32 ) THEN /* 32 = ds3 port on XTC or 15310*/

        SELECT col200 INTO o_portname

          FROM eqpt_info_table

         WHERE nedbaccessid=p_nedbaccessid

           AND physicalloc=p_physicalloc

           AND objectindex in (208,341) /* 208=Ds3Line 341=Ds3LineModel */

           AND moduletype in (47,233,234); /* 47=XTC 233=DS1_28_DS3_EC1_3 
234=DS1_84_DS3_EC1_3 */

   ELSIF (p_moduletype = 102 or p_moduletype = 103 or p_moduletype = 163 or p_moduletype = 
164 or p_moduletype = 187 ) THEN /* For ML series cards*/

        IF (p_ifindex = 1) THEN

             SELECT col200 INTO o_portname

               FROM eqpt_info_table

              WHERE nedbaccessid=p_nedbaccessid

                AND physicalloc=p_physicalloc

                AND objectindex in (136,257);

	ELSE

      	     SELECT col200 INTO o_portname

               FROM eqpt_info_table

              WHERE nedbaccessid=p_nedbaccessid

                AND physicalloc=p_physicalloc

                AND objectindex in (134,255);

	END IF;

   ELSE

        SELECT col200 INTO o_portname

          FROM eqpt_info_table

         WHERE nedbaccessid=p_nedbaccessid

           AND physicalloc=p_physicalloc

           AND moduleType=p_moduletype

           AND objectindex in 
(106,109,114,115,121,122,123,125,134,136,141,142,143,147,169,184,185,193,196,198,204,206,2
08,209,216,252,254,255,257,267,271,281);

   END IF;

  EXCEPTION

       WHEN TOO_MANY_ROWS

       THEN null;

  END;

RETURN o_portname;

END GetPortName;

3.1.68 GetPortWithPIMPPM

Create or replace function  getPortWithPimPpm (physicalloc in number)

return number

IS

    port                       number;

BEGIN

SELECT trunc(mod(physicalloc,power(2,13))) INTO  port from dual;

RETURN port;

END getPortWithPimPpm;

3.1.69 GetPosPortState

This function takes the topology type, NE ID, slot, and port numbers and returns the POS port state.

CREATE OR REPLACE function getPosPortState(topoType in number, neId in number, slotNo in 
number, portNo in number)

RETURN varchar2

IS

       state  varchar2(256);

BEGIN

      if (topoType !=3) then

       select COL5 into state from EQPT_INFO_TABLE where NeDbAccessId = neId and 
ObjectIndex = 166 and getSlot(physicalLoc) = slotNo and        getPort(physicalLoc) = 
portNo;

       return state;

     else

     state := 0;

     return state;

     end if;

END getPosPortState;

3.1.70 GetPosPortStateForRPR802

This function takes the topology type, NE ID, slot, and port numbers and returns the POS port state for 802.17 RPR topologies.

CREATE OR REPLACE function getPosPortStateForRPR802(topoType in number, neId in number, 
slotNo in number, portNo in number)

RETURN varchar2

IS

       state  varchar2(256);

BEGIN

      if (topoType =3) then

       select COL5 into state from EQPT_INFO_TABLE where NeDbAccessId = neId and 
ObjectIndex = 284 and getSlot(physicalLoc) = slotNo and

 IfIndex = portNo;

       return state;

     else

     state := 0;

     return state;

     end if;

END getPosPortStateForRPR802;

3.1.71 GetPPM

Create or replace function  getPPM (physicalloc in number)

return number

IS

    ppm                       number;

BEGIN

SELECT trunc(mod(trunc(physicalloc/power(2,10)),power(2,6))) INTO  ppm from dual;

RETURN ppm;

END getPPM;

3.1.72 GetProtectMLCardInfo

This function is used of the ML-series card is preprovisioned.

CREATE OR REPLACE function getProtectMLcardInfo(NEDbId in number, Slot in number)

RETURN varchar2

IS

   SysId        varchar2(256);

   CardModule   varchar2(256);

   ProtectCard  varchar2(256);

   equipType    number;

   model        number;

BEGIN

       IF (NEDbId = 0) THEN

              RETURN 'none';

       END IF;

       Select NESysID into SysId from Ne_Info_table where NeDbaccessID = NEDbId;

       Select NEModelType into model from Ne_Info_table where NeDbaccessID = NEDbId;

       Select EquipmentType into equipType from ONS15454_ne_inventory_table where 
NeDbaccessID = NEDbId and getSlot(PhysicalLoc) = Slot;

       Select ModuleName into CardModule from module_type_table where moduleType = 
equipType and modelType = model;

       ProtectCard := SysId || ':Slot ' || Slot || ' (' || CardModule || ')';

       RETURN ProtectCard;

END getProtectMLcardInfo;

3.1.73 GetQoSPhLoc

CREATE OR REPLACE FUNCTION Getqosphloc(slot IN NUMBER, cardPhloc IN NUMBER)

RETURN NUMBER

IS        PhLoc  NUMBER(20);        counter NUMBER;

BEGIN

PhLoc:=Getphysicallocwithpimppm(Getshelf(cardPhloc), slot, 255, 63, 0);

counter := countCosPh(PhLoc);

IF (counter = 0) THEN

PhLoc:=Getphysicallocwithpimppm(Getshelf(cardPhloc), slot, 255, 63, 1);

END IF;

RETURN PhLoc;

END Getqosphloc; /show errors;

3.1.74 GetShelf

Create or replace function  getShelf (physicalloc in number)

return number

IS

    shelf                       number;

BEGIN

SELECT trunc(mod(trunc(physicalloc/power(2,32)),power(2,8))) INTO  shelf from dual;

RETURN shelf;

END getShelf;

3.1.75 GetSlot

Create or replace function  getSlot (physicalloc in number)

return number

IS

    slot                       number;

BEGIN

SELECT mod(trunc(physicalloc/power(2,24)),power(2,8)) INTO slot from dual;

RETURN slot;

END getSlot;

3.1.76 GetSlotModuleName

create or replace function getSlotModuleName(p_nenodeid in number, p_moduletype in number)

return varchar2

IS

      o_modulename   varchar2(64);

      i_modeltype      number;

      p_slotmoduletype number;

BEGIN

        SELECT trunc(p_moduletype/power(2,16)) INTO p_slotmoduletype FROM dual;

        IF (p_nenodeid=0 OR p_slotmoduletype=0) THEN

           o_modulename:='SYSTEM';

        ELSE

          SELECT nemodeltype INTO i_modeltype

             FROM ne_info_table

            WHERE nenodeid=p_nenodeid;

           SELECT ModuleName INTO o_modulename

             FROM module_type_table

            WHERE moduletype=p_slotmoduletype

              AND modeltype=i_modeltype;

       END IF;

RETURN o_modulename;

Exception

    WHEN NO_DATA_FOUND

    THEN

    RETURN 'Unsupported';

END getSlotModuleName;

3.1.77 GetSlotModuleType

create or replace function getSlotModuleType(p_moduletype in number)

return varchar2

IS

      p_slotmoduletype number;

BEGIN

        SELECT trunc(p_moduletype/power(2,16)) INTO p_slotmoduletype FROM dual;

RETURN p_slotmoduletype;

END getSlotModuleType;

3.1.78 GetSpanTblCktSpanDestNodeId

CREATE or replace function getspantblCktSpanDestNodeId (CKTNODEIDIN number,CKTUNIQUEIDIN 
number)return varchar2

IS

    cursor ctpspandestnodeidcursor is

    select CKTCTPNODEID

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID =CKTUNIQUEIDIN

    and ctp_type=4;

    ctpspandestnodeid_list varchar2(10000);

BEGIN

    for rec in ctpspandestnodeidcursor loop

        if ctpspandestnodeidcursor%ROWCOUNT = 1 then

            ctpspandestnodeid_list:= rec.CKTCTPNODEID;

        else

            ctpspandestnodeid_list := ctpspandestnodeid_list||','||rec.CKTCTPNODEID;

        end if;

    end loop;

    return ctpspandestnodeid_list;

END getspantblCktSpanDestNodeId;

3.1.79 GetSpanTblCktSpanSrcNodeId

CREATE or replace function getspantblCktSpanSrcNodeId (CKTNODEIDIN number,CKTUNIQUEIDIN 
number)return varchar2

IS

    cursor ctpspansrcnodeidcursor is

    select CKTCTPNODEID

    from circuit_ctp_tbl

    where CKTNODEID=CKTNODEIDIN

    and CKTUNIQUEID=CKTUNIQUEIDIN

    and ctp_type=3;

    finalspansrcnodeid_list varchar2(10000);

BEGIN

    for rec in ctpspansrcnodeidcursor loop

        if ctpspansrcnodeidcursor%ROWCOUNT = 1 then

            finalspansrcnodeid_list:= rec.CKTCTPNODEID;

        else

            finalspansrcnodeid_list := finalspansrcnodeid_list||','||rec.CKTCTPNODEID;

        end if;

    end loop;

    return finalspansrcnodeid_list;

END getspantblCktSpanSrcNodeId;

3.1.80 GetSubIf

Create or replace function  getSubIf(neifindex in number)

return number

IS

i_neifindex number;

subifindex number;

BEGIN

IF neifindex < 0 THEN

i_neifindex := neifindex+power(2,32);

ELSE

i_neifindex:=neifindex;

END IF;

SELECT mod(trunc(i_neifindex/power(2,16)),power(2,16)) INTO subifindex FROM dual;

RETURN subifindex;

END getSubIf;

3.1.81 GetValidCktSecSrcNodeId

CREATE or replace function getValidCktSecSrcNodeId (CKTNODEIDIN number,CKTUNIQUEIDIN 
number)return number

IS

    o_CktSedSrcNodeId number;

BEGIN

    select CktCtpNodeId into o_CktSedSrcNodeId

    from circuit_ctp_tbl

    where CktNodeId = CKTNODEIDIN

      and CktUniqueId = CKTUNIQUEIDIN

      and ctp_type = 1;

    return o_CktSedSrcNodeId;

END getValidCktSecSrcNodeId;

3.1.82 GetValidCktSrcNodeId

CREATE or replace function getValidCktSrcnodeid (CKTNODEIDIN number,CKTUNIQUEIDIN number)

return number

IS

    o_ValidCktSrcnodeid number;

BEGIN

    select CktCtpNodeId into o_ValidCktSrcnodeid

    from circuit_ctp_tbl

    where CktNodeId = CKTNODEIDIN

      and CktUniqueId = CKTUNIQUEIDIN

      and ctp_type = 0;

    return o_ValidCktSrcnodeid;

END getValidCktSrcnodeid;

3.1.83 GetValidModelType

create or replace function getValidModelType (CktNodeIdIN number, CktUniqueIdIN number, 
ctp_typeIN number)

return number

IS

    o_ValidModelType number;

BEGIN

    select CKTCTPMODELTYPE into o_ValidModelType

    from circuit_ctp_tbl

    where CktNodeId = CktNodeIdIN

      and CktUniqueId = CktUniqueIdIN

      and ctp_type = ctp_typeIN;

    return o_ValidModelType;

END getValidModelType;

3.1.84 GetValidModuleType

create or replace function getValidModuleType (CktNodeIdIN number, CktUniqueIdIN number, 
ctp_typeIN number)

return number

IS

    o_ValidModuleType number;

BEGIN

    select CKTCTPMODULETYPE into o_ValidModuleType

    from circuit_ctp_tbl

    where CktNodeId = CktNodeIdIN

      and CktUniqueId = CktUniqueIdIN

      and ctp_type = ctp_typeIN;

    return o_ValidModuleType;

END getValidModuleType;

3.1.85 GetValidObjectType

create or replace function getValidObjectType (CktNodeIdIN number, CktUniqueIdIN number, 
ctp_typeIN number)return number

IS

    o_ValidObjectType number;

BEGIN

    select CKTCTPOBJECTTYPE into o_ValidObjectType

    from circuit_ctp_tbl

    where CktNodeId = CktNodeIdIN

      and CktUniqueId = CktUniqueIdIN

      and ctp_type = ctp_typeIN;

    return o_ValidObjectType;

END getValidObjectType;

3.1.86 Has_Next

- used by function ne_belongs_to_APC_domain to determine if a node belongs to an APC 
domain

CREATE OR REPLACE FUNCTION has_next

    (base_string       IN VARCHAR2,

     looking_for       IN VARCHAR2,

     start_looking_at  IN INTEGER,

	 found_at_position OUT INTEGER,

     token OUT VARCHAR2) RETURN BOOLEAN

IS

target VARCHAR2(1000);

BEGIN

    -- add delimiters at the end of the base string

	target := concat(base_string,looking_for);

	-- find position of next delimiter

	found_at_position := INSTR (target,looking_for,start_looking_at,1);

	-- read the token

	if found_at_position > start_looking_at then

	   token := SUBSTR(base_string,start_looking_at,found_at_position-start_looking_at);

	else

	   token := '-1';

	end if;

    RETURN (found_at_position <> 0);

END has_next;

3.1.87 IS_NUMBER

CREATE OR REPLACE function IS_NUMBER(str in varchar2, rv in number:=-1) return number IS

dummy number;

begin

dummy := to_number(str);

return dummy;

Exception WHEN OTHERS then

return rv;

end;

3.1.88 IsIPv6

CREATE OR REPLACE

function isIPV6 (NEDBid number)

return number

is

  isipv6 number;

begin

  select IS_IPV6 into isipv6 from ne_info_table where nedbaccessid = NEDBid;

return isipv6;

 Exception

	when no_data_found then

		return 0;

end isIPV6;

3.1.89 MapObjectIndex

CREATE or replace function mapobjectIndex(neModelType number,

nemodelIndex number,

portnumber number,

iftype number,

moduletype number) return number

is

ObjIndex number;

BEGIN

  if neModelType = 38 then

      if moduletype = 34 then

         return 340;

      elsif moduletype = 32 then

         return 341;

      elsif moduletype = 31 then

         return 342;

      end if;

  end if;

	if neModelType = 39 then

      if moduletype = 68 then

         return 340;

      elsif moduletype = 69 then

         return 340;

      elsif moduletype = 32 then

         return 341;

      elsif moduletype = 33 then

         return 341;

      end if;

  end if;

  if (neModelType = 38 or neModelType = 39) and

     (moduleType = 28 or moduleType = 29 or moduleType = 30) then

	 return 334;

  end if;

  if (neModelType = 18 or neModelType = 30) and

     (moduleType = 28 or moduleType = 29 or moduleType = 30) then

	 return 147;

  end if;

  if    moduleType = 9 or moduleType = 106 or moduleType = 10  or

        moduleType = 11 or moduleType = 12 or moduleType = 13 or

        moduleType = 28 or moduleType = 29 or moduleType = 30 or

		moduleType =60 or moduleType = 85 or moduleType = 86 or moduleType = 87 or 
moduleType = 113 then

                return 109;

  elsif neModelType = 3 and moduleType = 4  then

      return 114;

  elsif neModelType = 3 and moduleType = 5 then

      return 114;

  elsif neModelType = 5 and moduleType = 4  then

      return 204;

  elsif neModelType = 14 and moduleType = 4  then

      return 114;

  elsif neModelType = 14 and moduleType = 5 then

      return 114;

  elsif moduleType = 6 or moduleType = 7 or moduleType = 58  then

      return 121;

  elsif moduleType = 57 then

      return 122;

  elsif moduleType = 102 or moduleType = 103 then

      return 136;

  elsif moduleType = 8 then

      if nemodelIndex >= 61 then

         return 267;

      else

         return 123;

      end if;

  elsif moduletype = 47 and portnumber  <= 28 then

       return 204;

  elsif moduletype = 47 and portnumber >28  then

       return 208;

  elsif moduletype = 7 or moduletype = 8 then

     return 114;

  elsif moduletype = 14 then

      return 106;

  elsif moduletype = 15 then

     return 206;

  elsif moduletype = 31 then

     return 109;

  elsif moduletype = 32 then

     return 208;

  elsif moduletype = 34 then

     return 204;

  elsif moduletype = 62 then

    return 209;

  elsif moduletype = 118 and portnumber = 1 then

   return 141;

  elsif moduletype = 118 and portnumber = 2 then

    return 142;

  elsif moduletype = 119 and portnumber >= 1 and portnumber <= 4 then

    return 141;

  elsif moduletype = 119 and  portnumber =5 then

     return 142;

  elsif moduletype = 120 and portnumber = 1 then

    return  141;

  elsif moduletype = 120 and portnumber = 2 then

    return 142;

  elsif moduletype = 121 and portnumber = 1 then

    return 141;

  elsif (moduletype = 121 and (portnumber = 2 or portnumber = 3)) then

   return 142;

  elsif moduletype = 127 and portnumber = 1 then

        return 198;

  elsif (moduletype = 127  and (portnumber =  2 or portnumber = 3)) then

   return 193;

  elsif moduletype = 128 and portnumber = 1 then

    return 198;

  elsif moduletype = 128 and portnumber >= 2 and portnumber <= 7 then

   return 193;

  elsif moduletype = 129 and portnumber = 2 then

   return 196;

  elsif (moduletype = 129 and (portnumber = 1 or portnumber = 3 or portnumber = 4)) then

      return 193;

  elsif moduletype = 130 and portnumber = 6 then

    return 196;

  elsif moduletype = 130 and portnumber >= 1 and portnumber <= 5 then

    return 193;

  elsif moduletype = 131 and portnumber >= 1 and portnumber <= 32 then

    return 194;

  elsif moduletype = 131 and portnumber = 33 then

   return 193;

  elsif moduletype = 132 and portnumber >= 1 and portnumber <= 32 then

     return 194;

  elsif moduletype = 132 and portnumber = 33 then

    return 193;

  elsif moduletype = 133 and portnumber >= 1 and portnumber <= 8 then

     return 194;

  elsif moduletype = 133 and (portnumber = 9 or portnumber = 10) then

    return 195;

  elsif (moduletype = 134 and (portnumber = 1 or portnumber = 2)) then

    return 194;

  elsif moduletype = 134 and portnumber >= 3 and portnumber <= 6 then

    return 193;

 elsif moduletype = 135 and portnumber >= 1 and portnumber <= 4 then

   return 194;

 elsif moduletype = 135 and portnumber >= 5 and portnumber <= 8 then

   return 193;

 elsif moduletype = 136 and portnumber >= 1 and portnumber <= 8 then

  return 194;

elsif moduletype = 136 and portnumber >= 9 and portnumber <= 12 then

   return 193;

elsif moduletype = 137 and portnumber >= 1 and portnumber <= 2 then

  return 195;

elsif moduletype = 137 and portnumber >= 3 and portnumber <= 6 then

  return 193;

elsif moduletype = 138 and portnumber >= 1 and portnumber <= 8 then

   return 195;

elsif moduletype = 138 and portnumber >= 9 and portnumber <= 12 then

   return 193;

elsif moduletype = 143 then

      return 216;

elsif moduletype = 145 then

      return 267;

elsif moduletype = 163 or moduletype = 164  or moduletype = 187 or moduletype = 285 or 
moduletype = 283 or moduletype = 284 then

      return 257;

elsif moduletype = 108 or moduletype = 83 or moduletype = 64 or moduletype = 65 or 
moduletype =66 or

      moduletype = 67 then

    if nemodelindex = 33 then

      return 121;

    else

     return 114;

    end if;

elsif neModelType = 3 and moduleType = 218 then

      return 114;

elsif moduletype = 219 or moduletype = 253 or moduletype = 254 or moduletype = 220 then

      return 301;

elsif moduletype = 201 or moduletype = 202 or moduletype = 203 then

      return 109;

elsif moduletype = 204 then

      return 206;

elsif moduletype = 205 then

      return 106;

elsif moduletype = 206 or moduletype =207 or moduletype =208 then

      return 435;

elsif moduletype = 209 and portnumber<3 then

      return 436;

elsif moduletype = 209 and portnumber>=3 then

      return 434;

elsif moduletype = 210 or moduletype =211 then

      return 431;

elsif moduletype = 215 then

      return 437;

elsif moduletype = 183 and portnumber<9 then

      return 141;

elsif moduletype = 183 and portnumber>=9 then

      return 142;

elsif moduletype = 184 and portnumber<9 then

      return 141;

elsif moduletype = 184 and portnumber>=9 then

      return 184;

elsif moduletype = 181 then

      return 184;

elsif moduletype = 264 then

      return 194;

elsif moduletype = 265 then

      return 194;

elsif moduletype = 294 then

      return 193;

elsif moduletype = 295 then

      return 193;

elsif moduletype = 297 then

      return 193;

elsif moduletype =182 and portnumber<5 then

      return 184;

elsif moduletype =182 and portnumber=5 then

      return 142;

elsif moduletype = 271 and portnumber<3 then

      return 141;

elsif moduletype = 271 and portnumber>=3 then

      return 142;

elsif moduletype = 272 and portnumber<21 then

      return 141;

elsif moduletype = 272 and portnumber>=21 then

      return 142;

elsif moduletype = 273 and portnumber<17 then

      return 141;

elsif moduletype = 273 and portnumber>=17 then

      return 142;

elsif moduletype = 358 and portnumber<=1 then

      return 141;

elsif moduletype = 358 and portnumber>=2 then

      return 142;

elsif moduletype = 300 and portnumber<=1 then

      return 141;

elsif moduletype = 300 and portnumber>=2 then

      return 142;

elsif moduletype = 301 and portnumber<=4 then

      return 141;

elsif moduletype = 301 and portnumber>=5 then

      return 142;

elsif moduletype = 356 and portnumber<=4 then

      return 141;

elsif moduletype = 356 and portnumber>=5 then

      return 142;

elsif moduletype = 293 then

      return 142;

elsif moduletype = 248 and portnumber<9 then

      return 109;

elsif moduletype = 248 and portnumber>=9 then

    if neModelType = 3 then

      return 143;

    elsif neModelType = 14 then

      return 114;

    end if;

else

    return 0;

end if;

END;

3.1.90 MergedInvalidityList

CREATE OR REPLACE function mergedInvalidityList(lineTableValue in integer, 
lineInvalidityList in varchar2, pathInvalidityList in varchar2) return varchar2 as

  TYPE column_name IS RECORD (

    table_name      VARCHAR2(50),

    view_name	    VARCHAR2(50)

);

TYPE column_names  IS TABLE OF column_name index by binary_integer;

column_list column_names;

tblName USER_TAB_COLUMNS.TABLE_NAME%TYPE;

dbColumnNo USER_TAB_COLUMNS.COLUMN_ID%TYPE;

occurence integer;

value USER_TAB_COLUMNS.COLUMN_ID%TYPE;

startPos integer;

offSet integer;

mergedInvalidityList varchar2(100);

begin

if(lineInvalidityList is null ) then

	return pathInvalidityList;

end if;

if(length(trim(lineInvalidityList)) =0 ) then

	return pathInvalidityList;

end if;

if(lineTableValue = -1) then

	return pathInvalidityList;

end if;

	tblName := 'ONS15454_SONET_LINE_PM_TABLE';

	mergedInvalidityList := pathInvalidityList;

	column_list(1).table_name := 'PPJCPDET';

	column_list(1).view_name := 'PPJC_PDET';

	column_list(2).table_name := 'NPJCPDET';

	column_list(2).view_name := 'NPJC_PDET';

	column_list(3).table_name := 'PPJCPGEN';

	column_list(3).view_name := 'PPJC_PGEN';

	column_list(4).table_name := 'NPJCPGEN';

	column_list(4).view_name := 'NPJC_PGEN';

	column_list(5).table_name := 'PJDIFF';

	column_list(5).view_name := 'PJ_DIFF';

	column_list(6).table_name := 'PJPSEC';

	column_list(6).view_name := 'PJP_SEC';

	column_list(7).table_name := 'PJNSEC';

	column_list(7).view_name :=  'PJN_SEC';

for recordNumber in 1..column_list.count loop

	select column_id into dbColumnNo from USER_TAB_COLUMNS where table_name = tblName and 
column_name = column_list(recordNumber).table_name;

 	occurence := 1;

        startPos := 1;

        loop

        offSet := instr(lineInvalidityList,':',1,occurence);

        occurence := occurence + 1;

        exit when offSet = 0;

        if(startPos = 1) then

        value := to_number(substr(lineInvalidityList,startPos,offSet-startPos),9999);

        else

        value := to_number(substr(lineInvalidityList,startPos +1,offSet-startPos 
-1),9999);

        end if;

        if((dbColumnNo -1) = value) then

	select column_id into dbColumnNo from USER_TAB_COLUMNS where 
table_name='ONS15454_SONET_PATH_PM_TABLE' and 
column_name=column_list(recordNumber).table_name;

	mergedInvalidityList := mergedInvalidityList || (dbColumnNo -1)  || ':';

	exit;

	end if;

		startPos := offSet;

	end loop;

end loop;

	if(mergedInvalidityList is null) then

		return '';

	end if;

 return mergedInvalidityList;

end;

3.1.91 NE_Belongs_to_APC_Domain

-- to determine if a node belongs to an APC domain

CREATE OR REPLACE FUNCTION ne_belongs_to_APC_domain

    (sides IN VARCHAR2, neid in NUMBER) RETURN number

IS

token varchar2(15):='-1';

neid_in_APC_domain varchar2(15):='-1';

found_at integer;

found_in_token_at integer;

start_point integer := 1;

-- return value -1 menas that the NEDBID is not part of the domain

retval NUMBER:= -1;

BEGIN

    WHILE

      -- find if there is a next elemnt and assign the elemnt to token

	  (has_next(sides,',',start_point,found_at,token))

    LOOP

	   -- take from the elemnt the first part that is the NEDBID

	   if has_next(token,' ',1,found_in_token_at,neid_in_APC_domain) then

	      -- is the NEDBID we are looking fore

		  if to_number(neid_in_APC_domain) = neid then

             -- if yes return it by the function...to be used in the where cluse of the 
view

	             retval:= neid;

		     exit;

                  end if;

	   end if;

	   --go to next elemnt

	   start_point := found_at + 1;

    END LOOP;

	return retval;

END ne_belongs_to_APC_domain;

3.1.92 RemoveFirstByteIf

CREATE or REPLACE function removefirstbyteif (neifindex in number)

RETURN number

IS

   iftype       number;

   o_neifindex  number;

   i_neifindex  number;

BEGIN

   IF neifindex < 0 THEN

      i_neifindex := neifindex+power(2,32);

   ELSE

      i_neifindex:=neifindex;

   END IF;

     select mod(i_neifindex,power(2,24)) INTO  o_neifindex from dual;

return o_neifindex;

END removefirstbyteif;

3.1.93 RemoveLowBits

CREATE OR REPLACE function removeLowBits (aNum number, numOfBits integer)

RETURN integer

AS

    result number;

    modVal number;

BEGIN

        modVal := power (2, numOfBits);

        result := trunc (aNum / modVal);

        --result := result * modVal;

        return result;

END;

3.1.94 Time_Diff

CREATE or REPLACE FUNCTION time_diff (

DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1   NUMBER;

NDATE_2   NUMBER;

NSECOND_1 NUMBER(5,0);

NSECOND_2 NUMBER(5,0);

BEGIN

  -- Get Julian date number from first date (DATE_1)

  NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

  -- Get Julian date number from second date (DATE_2)

  NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

  -- Get seconds since midnight from first date (DATE_1)

  NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

  -- Get seconds since midnight from second date (DATE_2)

  NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

  RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));

END time_diff;

3.1.95 TYPE AlarmInfoListType

--create or replace TYPE AlarmInfoListType as table of AlarmInfoType;

create TYPE AlarmInfoListType as table of AlarmInfoType;

3.1.96 TYPE AlarmInfoType

--create or replace TYPE AlarmInfoType as OBJECT (

create TYPE AlarmInfoType as OBJECT (

        nedbaccessid                number,

        Alarmseqnum                 number,

        ModuleOrIfIndex             number,

        ActiveAlarmTimeStamp        date,

        ActiveAlarmType             number,

        ActiveAlarmSeverity         number,

        ActiveAlarmServEff          number,

        ActiveAlarmAdditionalInfo   varchar2(512),

        AckIndex                    number,

        ActiveAlarmFlag             number,

        ActiveAlarmComment          varchar2(2014),

        ClearAlarmTimeStamp         date,

        NEAlarmTimeStamp            date,

        NEAlarmClearTimeStamp       date,

        ModuleType                  number,

        Physicalloc                 number,

        alarmstatus                 number,

        linkid                      link_id_tabtype,

        actiontype                  number,

        externalcondition           varchar2(1024),

        ModelType                   number,

        ObjectType                  number,

        StrObjInstance              VARCHAR2(256),

        StrDirection                VARCHAR2(6),

        StrLocation                 VARCHAR2(4)

);

3.1.97 TYPE Link_Id_Tabtype

create or replace TYPE link_id_tabtype is Table of number;

--drop type AlarmInfoListType;

--drop type AlarmInfoType;

	Cisco Prime Optical Database Schema, 9.3
	Chapter 3: Database Schema Functions
Cisco Prime Optical Database Schema, 9.3 Preface Chapter 1: Database Schema Tables Chapter 2: Database Schema Views Chapter 3: Database Schema Functions Appendix A: Oracle Built-In Data Types Appendix B: Config_Table Parameters	Download this chapter Chapter 3: Database Schema Functions Download the complete book Cisco Prime Optical Database Schema, 9.3 (Book-length PDF version) (PDF - 5 MB) Feedback Table Of Contents Database Schema Functions 3.1 Prime Optical Functions 3.1.1 CollapsedDestNodeName 3.1.2 CollapsedDestNodeName 3.1.3 CollapsedEqptPortName 3.1.4 CollapsedIfIndex 3.1.5 CollapsedIfIndex 3.1.6 CollapsedModelType 3.1.7 CollapsedModuleName 3.1.8 CollapsedObjectIndex 3.1.9 CollapsedPhy 3.1.10 CollapsedPort 3.1.11 CollapsedPortModuleName 3.1.12 ConvertAxxIfIndex 3.1.13 ConvertLinkModuleType 3.1.14 CountCoSPh 3.1.15 CTMDuration 3.1.16 DisplayIf 3.1.17 DisplayIPAll 3.1.18 DisplayIP 3.1.19 DisplayPhyLoc 3.1.20 EqptInfoPort 3.1.21 Get_Enet_Value 3.1.22 Get_Enet_Value2 3.1.23 GetBccBwInfo 3.1.24 GetBccClassInfo 3.1.25 GetBccClassRPR80217 3.1.26 GetBccCosCommitGrp 3.1.27 GetBccCosInfo 3.1.28 GetBGFromBGObj 3.1.29 GetBGFromQoSObj 3.1.30 GetCktDestNodeId 3.1.31 GetCktSecSrcIfIndex 3.1.32 GetCktSecSrcPhysicalLoc 3.1.33 GetCktSecSrcPortName 3.1.34 GetCktSrcIfIndex 3.1.35 GetCktSrcPhysicalLoc 3.1.36 GetCktSrcPortName 3.1.37 GetCollapsedPortname 3.1.38 GetEqptInfoPhysicalLoc 3.1.39 GetIfCosGrp 3.1.40 GetInterfaceId 3.1.41 Get_Inventory_Note 3.1.42 GetLinkType 3.1.43 GetLowBits 3.1.44 GetMgx88xxMgmtState 3.1.45 GetMgx88xxSyncMode 3.1.46 GetMLcardAddnlInfo 3.1.47 GetModelTypeFromPhysicalLoc 3.1.48 GetModuleName 3.1.49 GetModuleName_PM 3.1.50 GetModuleName1 3.1.51 GetModuleName2 3.1.52 GetgetProtectMLcardConfigState 3.1.53 GetNEDBAccessID 3.1.54 GetNEIPAddr 3.1.55 GetObjectTypeFromIfIndex 3.1.56 GetObjectTypeFromIfIndex2 3.1.57 GetOutIPAddr 3.1.58 GetPhysicalLoc 3.1.59 GetPhysicalLocWithPIMPPM 3.1.60 GetPIM 3.1.61 GetPMParameterValue 3.1.62 GetPolicyMapType 3.1.63 GetPort 3.1.64 GetPort_XTC 3.1.65 GetPortModuleName 3.1.66 GetPortModuleName2 3.1.67 GetPortName 3.1.68 GetPortWithPIMPPM 3.1.69 GetPosPortState 3.1.70 GetPosPortStateForRPR802 3.1.71 GetPPM 3.1.72 GetProtectMLCardInfo 3.1.73 GetQoSPhLoc 3.1.74 GetShelf 3.1.75 GetSlot 3.1.76 GetSlotModuleName 3.1.77 GetSlotModuleType 3.1.78 GetSpanTblCktSpanDestNodeId 3.1.79 GetSpanTblCktSpanSrcNodeId 3.1.80 GetSubIf 3.1.81 GetValidCktSecSrcNodeId 3.1.82 GetValidCktSrcNodeId 3.1.83 GetValidModelType 3.1.84 GetValidModuleType 3.1.85 GetValidObjectType 3.1.86 Has_Next 3.1.87 IS_NUMBER 3.1.88 IsIPv6 3.1.89 MapObjectIndex 3.1.90 MergedInvalidityList 3.1.91 NE_Belongs_to_APC_Domain 3.1.92 RemoveFirstByteIf 3.1.93 RemoveLowBits 3.1.94 Time_Diff 3.1.95 TYPE AlarmInfoListType 3.1.96 TYPE AlarmInfoType 3.1.97 TYPE Link_Id_Tabtype Database Schema Functions This chapter describes the functions defined in Prime Optical tables. It contains the following section: •Prime Optical Functions 3.1 Prime Optical Functions This section describes the functions that Prime Optical uses. Functions read parameters and return a value. The functions are listed in alphabetical order. 3.1.1 CollapsedDestNodeName CREATE or replace function collapseddestnodename(CKTNODEIDIN varchar,CKTUNIQUEIDIN varchar) return varchar2 is cursor cktdestnodename is select CktDestNodeId from circuit_dest_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and CktDestNodeId is not null; finaldestNode varchar2(10000); DestNedbaccessid varchar2(1000); BEGIN for rec in cktdestnodename loop SELECT nedbaccessid INTO DestNedbaccessid FROM ne_info_table WHERE nenodeid= rec.CktDestNodeId and rec.CktDestNodeId != 0 ; if cktdestnodename%ROWCOUNT = 1 then finaldestNode:= DestNedbaccessid; else finaldestNode := finaldestNode\|\|','\|\|DestNedbaccessid; end if; end loop; return finaldestNode; END; 3.1.2 CollapsedDestNodeName CREATE or replace function collapseddestnodename (CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS cursor cktdestnodename is select CktCtpNodeId from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and ctp_type = 2 and CktCtpNodeId is not null; finaldestNode varchar2(10000); DestNedbaccessid varchar2(1000); BEGIN for rec in cktdestnodename loop SELECT nedbaccessid INTO DestNedbaccessid FROM ne_info_table WHERE nenodeid= rec.CktCtpNodeId and rec.CktCtpNodeId != 0 ; if cktdestnodename%ROWCOUNT = 1 then finaldestNode:= DestNedbaccessid; else finaldestNode := finaldestNode\|\|','\|\|DestNedbaccessid; end if; end loop; return finaldestNode; END collapseddestnodename; 3.1.3 CollapsedEqptPortName CREATE or replace function collapsedeqptportname(CKTNODEIDIN varchar,CKTUNIQUEIDIN varchar) return varchar2 IS cursor eqptcollpasedport is select CktCtpNodeId, cktCtpPhysicalloc, cktCtpIfindex, cktCtpModuleType from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and ctp_type = 2 and CktCtpNodeId is not null and cktCtpPhysicalloc is not null and cktCtpIfindex is not null and cktCtpModuleType is not null; finaleqptinfoport varchar2(10000); DestNedbaccessid varchar2(1000); BEGIN for rec in eqptcollpasedport loop SELECT nedbaccessid INTO DestNedbaccessid FROM ne_info_table WHERE nenodeid= rec.CktCtpNodeId and rec.CktCtpNodeId != 0; if eqptcollpasedport%ROWCOUNT = 1 then finaleqptinfoport:= '@'\|\|eqptinfoport(DestNedbaccessid,rec.cktCtpPhysicalloc,0, rec.cktCtpModuleType); else finaleqptinfoport := finaleqptinfoport\|\|','\|\|'@'\|\|eqptinfoport(DestNedbaccessid,rec.cktCtpPhysicalloc,0, rec.cktCtpModuleType); end if; end loop; return finaleqptinfoport; END collapsedeqptportname; 3.1.4 CollapsedIfIndex CREATE or replace function collapsedifindex (CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS cursor cktobjectindexcursor is select CktCtpIfIndex from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and ctp_type = 2 and CktCtpIfIndex is not null; finalifindex varchar2(10000); BEGIN for rec in cktobjectindexcursor loop if cktobjectindexcursor%ROWCOUNT = 1 then finalifindex:= rec.CktCtpIfIndex; else finalifindex := finalifindex\|\|','\|\|rec.CktCtpIfIndex; end if; end loop; return finalifindex; END collapsedifindex; 3.1.5 CollapsedIfIndex CREATE or replace function collapsedifindex (CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS cursor cktobjectindexcursor is select CktCtpIfIndex from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and ctp_type = 2 and CktCtpIfIndex is not null; finalifindex varchar2(10000); BEGIN for rec in cktobjectindexcursor loop if cktobjectindexcursor%ROWCOUNT = 1 then finalifindex:= rec.CktCtpIfIndex; else finalifindex := finalifindex\|\|','\|\|rec.CktCtpIfIndex; end if; end loop; return finalifindex; END collapsedifindex; 3.1.6 CollapsedModelType CREATE or replace function collapsedmodeltype (CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS cursor cktmodeltypecursor is select CKTCTPMODELTYPE from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and CTP_TYPE = 2 and CKTCTPMODELTYPE is not null; finalmoduletype varchar2(10000); BEGIN for rec in cktmodeltypecursor loop if cktmodeltypecursor%ROWCOUNT = 1 then finalmoduletype:= rec.CKTCTPMODELTYPE; else finalmoduletype := finalmoduletype\|\|','\|\|rec.CKTCTPMODELTYPE; end if; end loop; return finalmoduletype; END collapsedmodeltype; 3.1.7 CollapsedModuleName CREATE or replace function collapsedmodulename (CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS cursor cktmodulenamecursor is select CKTCTPMODULETYPE from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and ctp_type = 2 and CKTCTPMODULETYPE is not null; finalcktdestModuleType varchar2(10000); slotmoduletype number; BEGIN for rec in cktmodulenamecursor loop SELECT trunc(rec.CKTCTPMODULETYPE/power(2,16)) INTO slotmoduletype FROM dual; if cktmodulenamecursor%ROWCOUNT = 1 then finalcktdestModuleType:= slotmoduletype; else finalcktdestModuleType := finalcktdestModuleType\|\|','\|\|slotmoduletype; end if; end loop; return finalcktdestModuleType; END collapsedmodulename; 3.1.8 CollapsedObjectIndex CREATE or replace function collapsedobjectindex(CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS cursor cktifindexcursor is select CktCtpObjectType from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and CTP_TYPE = 2; finalifindex varchar2(10000); BEGIN for rec in cktifindexcursor loop if cktifindexcursor%ROWCOUNT = 1 then finalifindex:= rec.CktCtpObjectType; else finalifindex := finalifindex\|\|','\|\|rec.CktCtpObjectType; end if; end loop; return finalifindex; END; 3.1.9 CollapsedPhy CREATE or replace function collapsedphy (CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS cursor cktphysicalloc_cursor is select CKTCTPPHYSICALLOC from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and ctp_type = 2 and CKTCTPPHYSICALLOC is not null; finalphy varchar2(10000); BEGIN for rec in cktphysicalloc_cursor loop if cktphysicalloc_cursor%ROWCOUNT = 1 then finalphy:= rec.CKTCTPPHYSICALLOC; else finalphy := finalphy\|\|','\|\|rec.CKTCTPPHYSICALLOC; end if; end loop; return finalphy; END collapsedphy; 3.1.10 CollapsedPort CREATE or replace function collapsedport(CKTNODEIDIN varchar,CKTUNIQUEIDIN varchar) return varchar2 is cursor cktdestcursor is select cktdestportname from circuit_dest_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and cktdestportname is not null; finaldest varchar2(10000); BEGIN for rec in cktdestcursor loop if cktdestcursor%ROWCOUNT = 1 then finaldest:= rec.cktdestportname; else finaldest := finaldest\|\|','\|\|rec.cktdestportname; end if; end loop; return finaldest; END; 3.1.11 CollapsedPortModuleName CREATE or replace function collapsedportmodulename(CKTNODEIDIN varchar,CKTUNIQUEIDIN varchar) return varchar2 is cursor cktmodulenamecursor is select cktctptModuleType from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and ctp_type = 2 and cktctptModuleType is not null; finalcktdestModuleType varchar2(10000); portmoduletype number; BEGIN for rec in cktmodulenamecursor loop SELECT trunc(mod(rec.cktctpModuleType,power(2,16))) INTO portmoduletype FROM dual; if cktmodulenamecursor%ROWCOUNT = 1 then finalcktdestModuleType:= portmoduletype; else finalcktdestModuleType := finalcktdestModuleType\|\|','\|\|portmoduletype; end if; end loop; return finalcktdestModuleType; END; 3.1.12 ConvertAxxIfIndex CREATE OR REPLACE function convertaxxifindex(ifindex in number) RETURN number IS newifindex NUMBER ; firstByte INTEGER ; secondByte INTEGER ; thirdByte INTEGER ; forthByte INTEGER ; fifthByte INTEGER ; result number(20); BEGIN if ifindex = 0 then return 0; else newifindex := ifindex - 1; end if; fifthByte := mod(newifindex, 4); newifindex := floor(newifindex / 4); forthByte := mod(newifindex, 8); newifindex := floor(newifindex / 8); thirdByte := mod(newifindex, 4); newifindex := floor(newifindex / 4); --secondByte := mod(newifindex, 4) + 1; secondByte := mod(newifindex, 3) + 1; newifindex := floor(newifindex / 3); --firstByte := mod(newifindex, 4) + 1; firstByte := mod(newifindex, 3) + 1; result := 0; result := result + (firstByte * POWER(2,56)); result := result + (secondByte * POWER(2,48)); result := result + (thirdByte * POWER(2,40)); result := result + (forthByte * POWER(2,32)); result := result + (fifthByte * POWER(2,24)); RETURN result; END convertaxxifindex; 3.1.13 ConvertLinkModuleType CREATE OR REPLACE function convertLinkModuleType (aModuleType number, aModelType number) RETURN number AS finalModuleType number(8); BEGIN if (aModelType in (3,14,5,24,18,30)) then if(aModuleType < 0 or aModuleType > 2000) then finalModuleType := aModuleType; else finalModuleType := aModuleType * power(2, 16); end if; else finalModuleType := aModuleType; end if; return finalModuleType; END; 3.1.14 CountCoSPh CREATE OR REPLACE FUNCTION countCosPh(cosPhloc IN NUMBER) RETURN NUMBER IS CURSOR cntPh IS SELECT UNIQUEID FROM TOPOLOGY_CIRCUIT_VIEW WHERE SRCPHYSICALLOC=cosPhloc OR DESTPHYSICALLOC=cosPhloc; BEGIN FOR rec IN cntPh LOOP IF cntPh%ROWCOUNT = 1 THEN return 1; ELSE return 0; END IF; END LOOP; return 0; END countCosPh; / show errors; 3.1.15 CTMDuration create or replace function ctmduration(p_ctmduration in number) return varchar2 IS CTMDuration_time varchar2(50); dddd varchar2(4); hh number:=0; mm number:=0; ss number:=0; hh_c varchar2(4); mm_c varchar2(4); ss_c varchar2(4); BEGIN IF (p_ctmduration = 0 ) THEN CTMDuration_time:=''; ELSE SELECT trunc(p_ctmduration) INTO dddd FROM dual; SELECT trunc((p_ctmduration-dddd)24) INTO hh FROM dual; IF ( hh <10 and hh>0 ) THEN hh_c:='0'\|\|hh\|\|'h:'; ELSIF (hh > 10) THEN hh_c:=hh\|\|'h:'; END IF; SELECT trunc((p_ctmduration-dddd)2460)-hh60 INTO mm FROM dual; IF (mm <10 and mm>0) THEN mm_c:='0'\|\|mm\|\|'m:'; ELSIF (mm>10) THEN mm_c:=mm\|\|'m:'; END IF; SELECT trunc((p_ctmduration-dddd)246060)-hh6060-mm60 INTO ss from dual; IF (ss < 10 and ss>0 ) THEN ss_c:='0'\|\|ss\|\|'s'; ELSIF (ss>10) THEN ss_c:=ss\|\|'s'; END IF; IF (dddd=0 or dddd is null) THEN CTMDuration_time:=rtrim(hh_c\|\|mm_c\|\|ss_c,':'); ELSE CTMDuration_time:=rtrim(dddd\|\|'d:'\|\|hh_c\|\|mm_c\|\|ss_c,':'); END IF; END IF; return CTMDuration_time; END ctmduration; 3.1.16 DisplayIf The displayif function is used to translate neifindex. CREATE or REPLACE function displayif (neifindex in number) RETURN varchar2 IS iftype number; if1 number; if2 number; o_if2 number; if3 number; o_if3 number; ifstring varchar2(10); o_interface varchar2(20); i_neifindex number; BEGIN IF neifindex < 0 THEN i_neifindex := neifindex+power(2,32); ELSE i_neifindex:=neifindex; END IF; SELECT trunc(i_neifindex/power(2,24)) INTO iftype FROM dual; SELECT decode(iftype,1,'System', 2,'Equipment', 3,'BITS', 4,'Sync-NE', 5,'Env Alarm', 6,'SDCC', 7,'Env CTL', 8,'Chassis', 9,'Ext-Sync', 10,'AIP', 11,'FAN', 12,'Port', 13,'CRS', 14,'RSMS', 15,'STM1E', 20,'EC-N', 21,'OC-N', 30,'DS-0', 31,'DS-1', 32,'DS-2', 33,'DS-3', 35,'STSRNG', 36,'AICI_AEP', 37,'AICI_AIE', 38,'UDC_A', 39,'UDC_B', 40,'VT-1', 45,'STS', 47,'UCP_CKT', 48,'UCP_IPCC', 49,'UCP_NBR', 55,'Ethernet', 56,'Ethernet-G', 57,'Ethernet-Brg', 58,'Ethernet-gige', 60,'PPP', 63,'E4', 65,'ATM', 66,'AAL5', 70,'VIDEO', 75,'XTC', 76,'CAP', 77,'CHANNEL', 80,'STM', 81,'E1', 82,'E3', 83,'DS3I', 90,'GENERIC', 100,'PIRELLI', 101,'STS', 102,'STS1', 103,'STS3', 104,'STS6', 105,'STS9', 106,'STS12', 107,'STS24', 108,'STS48', 109,'STS192', 110,'STS254', 111,'VC4-2C', 112,'VC4-3C', 113,'VC4-4C', 114,'VC4-8C', 115,'VC4-16C', 116,'VC4-64C', 117,'VC11', 118,'VC4', 119,'VC3', 120,'VC12', 201,'SER-DS1', 202,'E1-Port', 203,'SER-E1', 204,'STM1', 205,'VC4', 206,'VC3', 207,'VC2', 208,'VC12' ) INTO ifstring FROM dual; IF (ifstring='Equipment') THEN o_interface:= 'Equipment'; ELSE SELECT trunc(mod(i_neifindex,power(2,8))) INTO if3 FROM dual; SELECT decode(if3,0,'',if3) INTO o_if3 FROM dual; SELECT mod(trunc(i_neifindex/power(2,8)),power(2,8)) INTO if2 FROM dual; SELECT decode(if2,0,'',if2) INTO o_if2 FROM dual; SELECT mod(trunc(i_neifindex/power(2,16)),power(2,8)) INTO if1 FROM dual; IF ( if1 != 0 ) THEN o_interface := ifstring\|\|' #'\|\|if1\|\|' '\|\|o_if2\|\|' '\|\|o_if3; ELSE o_interface := ifstring\|\|' '\|\|o_if2\|\|' '\|\|o_if3; END IF; END IF; RETURN o_interface; END displayif; 3.1.17 DisplayIPAll CREATE OR REPLACE function displayIpAll (NEIpaddr6 in raw, NEIpaddr in number, ipv6 in number) return varchar2 IS first_sec number; second_sec number; third_sec number; fourth_sec number; ipaddr varchar2(100); neipaddr_o number; isipv6 number; BEGIN --dbms_output.put_line(neipaddr); if ipv6 = 1 then select rawtohex(NEIpaddr6) into ipaddr from dual; else if neipaddr < 0 then neipaddr_o:=NEIpaddr+power(2,32); else neipaddr_o:=NEIpaddr; end if; SELECT trunc(mod(NEIPaddr_o/power(2,24), power(2,8))) into first_sec from dual; SELECT trunc(mod(NEIPaddr_o/power(2,16), power(2,8))) INTO second_sec from dual; SELECT trunc(mod(NEIPaddr_o/power(2,8),power(2,8))) INTO third_sec from dual; SELECT trunc(mod(NEIPaddr_o,power(2,8))) INTO fourth_sec from dual; ipaddr:=first_sec\|\|'.'\|\|second_sec\|\|'.'\|\|third_sec\|\|'.'\|\|fourth_sec; end if; RETURN ipaddr; END displayIpAll; 3.1.18 DisplayIP Create or replace function displayIp (NEIpaddr in number) return varchar2 IS first_sec number; second_sec number; third_sec number; fourth_sec number; ipaddr varchar2(100); neipaddr_o number; isipv6 number; BEGIN --dbms_output.put_line(neipaddr); if neipaddr < 0 then neipaddr_o:=neipaddr+power(2,32); else neipaddr_o:=neipaddr; end if; SELECT trunc(mod(NEIPaddr_o/power(2,24), power(2,8))) into first_sec from dual; SELECT trunc(mod(NEIPaddr_o/power(2,16), power(2,8))) INTO second_sec from dual; SELECT trunc(mod(NEIPaddr_o/power(2,8),power(2,8))) INTO third_sec from dual; SELECT trunc(mod(NEIPaddr_o,power(2,8))) INTO fourth_sec from dual; ipaddr:=first_sec\|\|'.'\|\|second_sec\|\|'.'\|\|third_sec\|\|'.'\|\|fourth_sec; RETURN ipaddr; END displayip; / rem rem return the string of the IP address in the two format ipv4 or ipv6 rem 3.1.19 DisplayPhyLoc The displayphloc function is used to determine the physical location. Create or replace function displayphyloc (physicalloc in number) return varchar2 IS o_physicalloc varchar2(40); nemodelindex number; port number; slot number; pim number; ppm number; rack number; shelf number; nemodel varchar2(30); BEGIN SELECT trunc(mod(physicalloc,power(2,10))) INTO port from dual; SELECT mod(trunc(physicalloc/power(2,10)),power(2,6)) INTO ppm from dual; SELECT mod(trunc(physicalloc/power(2,16)),power(2,8)) INTO pim from dual; SELECT mod(trunc(physicalloc/power(2,24)),power(2,8)) INTO slot from dual; SELECT mod(trunc(physicalloc/power(2,32)),power(2,8)) INTO shelf from dual; SELECT mod(trunc(physicalloc/power(2,40)),power(2,8)) INTO rack from dual; o_physicalloc :='Rack:'\|\|rack\|\|',Shelf:'\|\|shelf\|\|',Slot:'\|\|slot\|\|',Port:'\|\|port; RETURN o_physicalloc; END displayphyloc; 3.1.20 EqptInfoPort CREATE or replace function eqptinfoport(NEDBACCESSID_param number, PHYSICALLOC_param number, IFINDEX_param number, MODULEINDEX_param number ) return varchar2 is portnameselectedcol200 varchar2(4000); portnameselectedcol3 varchar2(4000); calc_OBJECTINDEX number; PHYSICALLOC_temp number; NEMODELTYPE_param number; NEMODELINDEX_param number; calc_Ifindex number(20); SlotModuleType number; PortModuleType number; PpmModuleType number; BEGIN begin select NEMODELTYPE, NEMODELINDEX into NEMODELTYPE_param, NEMODELINDEX_param from ne_info_table where nedbaccessid = NEDBACCESSID_param; exception when no_data_found then null; end; SlotModuleType:= getSlotModuleType(MODULEINDEX_param); SELECT trunc(mod(MODULEINDEX_param,power(2,16))) INTO PortModuleType FROM dual; PpmModuleType:= getPpm(PHYSICALLOC_param); --create_file('/oraclesw9i','testf.log', 'NEMODELTYPE_param :'\|\|NEMODELTYPE_param\|\|'NEMODELINDEX_param :'\|\|NEMODELINDEX_param); if (SlotModuleType = 157 or SlotModuleType = 146 or SlotModuleType = 232 or SlotModuleType = 233 or SlotModuleType = 234) then calc_OBJECTINDEX:= mapobjectIndex(neModelType_param , nemodelIndex_param , getport(PHYSICALLOC_param) ,0, PortModuleType); else if (PpmModuleType = 63) then -- ppm not used; calc_OBJECTINDEX:= mapobjectIndex(neModelType_param , nemodelIndex_param , getport(PHYSICALLOC_param) ,0, SlotModuleType); else calc_OBJECTINDEX:= mapobjectIndex(neModelType_param , nemodelIndex_param , PpmModuleType ,0, SlotModuleType); end if; -- append_file('/oraclesw9i', 'testf.log', 'calc_OBJECTINDEX: '\|\|calc_OBJECTINDEX); -- append_file('/oraclesw9i','testf.log', 'PHYSICALLOC_param:'\|\|PHYSICALLOC_param); -- append_file('/oraclesw9i','testf.log','calc_Ifindex: '\|\|calc_Ifindex); PHYSICALLOC_temp := PHYSICALLOC_param; if ( SlotModuleType = 47 ) then PHYSICALLOC_temp := getPort_xtc(PHYSICALLOC_param, SlotModuleType); end if; if (NEMODELTYPE_param = 38) then if (SlotModuleType = 233) then if ( calc_OBJECTINDEX = 341 or calc_OBJECTINDEX = 342) then PHYSICALLOC_temp := PHYSICALLOC_temp - 28; end if; elsif (SlotModuleType = 234) then if ( calc_OBJECTINDEX = 341 or calc_OBJECTINDEX = 342) then PHYSICALLOC_temp := PHYSICALLOC_temp - 84; end if; end if; end if; begin select col200,col3 into portnameselectedcol200,portnameselectedcol3 from eqpt_info_table where NEDBACCESSID = NEDBACCESSID_param and PHYSICALLOC = PHYSICALLOC_temp and IFINDEX = 0 and MODULETYPE = SlotModuleType and OBJECTINDEX = calc_OBJECTINDEX; exception when no_data_found then null; end; if (calc_OBJECTINDEX = 193 or calc_OBJECTINDEX = 194) then return portnameselectedcol3; end if; return portnameselectedcol200; END; / 3.1.21 Get_Enet_Value CREATE OR REPLACE function get_enet_value( param1 IN number, param2 IN number, param3 IN number default -4, param4 IN number default -4, param5 IN number default -4, param6 IN number default -4) return number IS r_value number ; val1 number ; val2 number ; val3 number ; val4 number ; val5 number ; val6 number ; BEGIN IF(param3 =-4 and param4 =-4 and param5 =-4 and param6 =-4) THEN IF(param1 < 0 and param2 < 0) THEN IF ( (param1=param2)) THEN r_value:=param1; ELSIF (param1 = -3 or param2 = -3)THEN r_value:= -3; ELSIF ( param1 = -2 or param2 = -2 ) THEN r_value:= -2; ELSIF ( param1 = -1 or param2 = -1 ) THEN r_value:= -1; END IF; return r_value; END IF; ELSIF((param3 > -4) and (param4 =-4) and (param5 =-4) and (param6 =-4)) THEN IF(param1 < 0 and param2 < 0 and param3 < 0) THEN IF ( (param1=param2) and (param2=param3) ) THEN r_value:=param1; ELSIF (param1 = -3 or param2 = -3 or param3 = -3)THEN r_value:= -3; ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 ) THEN r_value:= -2; ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 ) THEN r_value:= -1; END IF; return r_value; END IF; ELSIF((param3 > -4) and (param4 > -4) and (param5 =-4) and (param6 =-4)) THEN IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0) THEN IF ( ((param1=param2) and (param2=param3) and (param3=param4))) THEN r_value:=param1; ELSIF (param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3)THEN r_value:= -3; ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 ) THEN r_value:=-2; ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 ) THEN r_value:=-1; END IF; return r_value; END IF; ELSIF((param3 > -4) and (param4 > -4) and (param5 >-4) and (param6 =-4)) THEN IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0 and param5 < 0) THEN IF ( ((param1=param2) and (param2=param3) and (param3=param4) and (param4=param5))) THEN r_value:=param1; ELSIF (param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3 or param5 =-3)THEN r_value:= -3; ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 or param5 = -2) THEN r_value:=-2; ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 or param5 = -1) THEN r_value:=-1; END IF; return r_value; END IF; ELSIF((param3 > -4) and (param4 > -4) and (param5 >-4) and (param6 >-4)) THEN IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0 and param5 < 0 and param6 < 0) THEN IF ( ((param1=param2) and (param2=param3) and (param3=param4) and (param4=param5) and (param5=param6))) THEN r_value:=param1; ELSIF(param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3 or param5 =-3 or param6=-3) THEN r_value:= -3; ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 or param5 = -2 or param6 = -2) THEN r_value:=-2; ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 or param5 = -1 or param6 = -1) THEN r_value:=-1; END IF; return r_value; END IF; END IF; IF ( param1 <0 ) THEN val1:= 0; ELSE val1:= param1; END IF; IF ( param2 <0 ) THEN val2:= 0; ELSE val2:= param2; END IF; IF ( param3 <0 ) THEN val3:= 0; ELSE val3:= param3; END IF; IF ( param4 <0 ) THEN val4:= 0; ELSE val4:= param4; END IF; IF ( param5 <0 ) THEN val5:= 0; ELSE val5:= param5; END IF; IF ( param6 <0 ) THEN val6:= 0; ELSE val6:= param6; END IF; r_value:=val1+val2+val3+val4+val5+val6; return r_value; END get_enet_value; 3.1.22 Get_Enet_Value2 CREATE OR REPLACE function get_enet_value2( param1 IN number, param2 IN number, param3 IN number default -4, param4 IN number default -4, param5 IN number default -4, param6 IN number default -4) return varchar IS r_value varchar(25) ; val1 number ; val2 number ; val3 number ; val4 number ; val5 number ; val6 number ; BEGIN IF(param3 =-4 and param4 =-4 and param5 =-4 and param6 =-4) THEN IF(param1 < 0 and param2 < 0) THEN IF ( (param1=param2)) THEN r_value:=to_char(param1); ELSIF (param1 = -3 or param2 = -3)THEN r_value:=to_char(-3); ELSIF ( param1 = -2 or param2 = -2 ) THEN r_value:=to_char(-2); ELSIF ( param1 = -1 or param2 = -1 ) THEN r_value:=to_char(-1); END IF; return r_value; END IF; ELSIF((param3 > -4) and (param4 =-4) and (param5 =-4) and (param6 =-4)) THEN IF(param1 < 0 and param2 < 0 and param3 < 0) THEN IF ( (param1=param2) and (param2=param3) ) THEN r_value:=to_char(param1); ELSIF (param1 = -3 or param2 = -3 or param3 = -3)THEN r_value:=to_char(-3); ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 ) THEN r_value:=to_char(-2); ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 ) THEN r_value:=to_char(-1); END IF; return r_value; END IF; ELSIF((param3 > -4) and (param4 > -4) and (param5 =-4) and (param6 =-4)) THEN IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0) THEN IF ( ((param1=param2) and (param2=param3) and (param3=param4))) THEN r_value:=to_char(param1); ELSIF (param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3)THEN r_value:=to_char(-3); ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 ) THEN r_value:=to_char(-2); ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 ) THEN r_value:=to_char(-1); END IF; return r_value; END IF; ELSIF((param3 > -4) and (param4 > -4) and (param5 >-4) and (param6 =-4)) THEN IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0 and param5 < 0) THEN IF ( ((param1=param2) and (param2=param3) and (param3=param4) and (param4=param5))) THEN r_value:=to_char(param1); ELSIF (param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3 or param5 =-3)THEN r_value:=to_char(-3); ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 or param5 = -2) THEN r_value:=to_char(-2); ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 or param5 = -1) THEN r_value:=to_char(-1); END IF; return r_value; END IF; ELSIF((param3 > -4) and (param4 > -4) and (param5 >-4) and (param6 >-4)) THEN IF(param1 < 0 and param2 < 0 and param3 < 0 and param4 < 0 and param5 < 0 and param6 < 0) THEN IF ( ((param1=param2) and (param2=param3) and (param3=param4) and (param4=param5) and (param5=param6))) THEN r_value:=to_char(param1); ELSIF(param1 = -3 or param2 = -3 or param3 = -3 or param4 = -3 or param5 =-3 or param6=-3) THEN r_value:=to_char(-3); ELSIF ( param1 = -2 or param2 = -2 or param3 = -2 or param4 = -2 or param5 = -2 or param6 = -2) THEN r_value:=to_char(-2); ELSIF ( param1 = -1 or param2 = -1 or param3 = -1 or param4 = -1 or param5 = -1 or param6 = -1) THEN r_value:=to_char(-1); END IF; return r_value; END IF; END IF; IF ( param1 <0 ) THEN val1:= 0; ELSE val1:= param1; END IF; IF ( param2 <0 ) THEN val2:= 0; ELSE val2:= param2; END IF; IF ( param3 <0 ) THEN val3:= 0; ELSE val3:= param3; END IF; IF ( param4 <0 ) THEN val4:= 0; ELSE val4:= param4; END IF; IF ( param5 <0 ) THEN val5:= 0; ELSE val5:= param5; END IF; IF ( param6 <0 ) THEN val6:= 0; ELSE val6:= param6; END IF; r_value:=to_char(val1+val2+val3+val4+val5+val6); return r_value; END get_enet_value2; 3.1.23 GetBccBwInfo CREATE OR REPLACE FUNCTION getBccBwInfo(neId IN NUMBER, phLoc IN NUMBER, objIdx IN NUMBER, COLUMN IN VARCHAR2) RETURN VARCHAR2 IS retVal VARCHAR2(256); BEGIN SELECT COL16 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND ObjectIndex = objIdx AND PHYSICALLOC = phLoc AND COL2 = COLUMN; RETURN retVal; END getBccBwInfo; 3.1.24 GetBccClassInfo CREATE OR REPLACE FUNCTION getBccClassInfo(neId IN NUMBER, phLoc IN NUMBER, objIdx IN NUMBER, COLUMN IN VARCHAR2) RETURN VARCHAR2 IS retVal VARCHAR2(256); BEGIN SELECT COL19 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND ObjectIndex = objIdx AND PHYSICALLOC = phLoc AND COL2 = COLUMN; RETURN retVal; END getBccClassInfo; 3.1.25 GetBccClassRPR80217 CREATE OR REPLACE FUNCTION getBccClassRPR80217(neId IN NUMBER, phLoc IN NUMBER, objIdx IN NUMBER, COLUMN IN NUMBER) RETURN NUMBER IS retVal NUMBER(3); BEGIN IF (COLUMN = 1) THEN -- Select column called COL1 SELECT COL1 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND ObjectIndex = objIdx AND PHYSICALLOC = phLoc AND IFINDEX = 0; END IF; IF (COLUMN = 2) THEN -- Select column called COL2 SELECT COL2 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND ObjectIndex = objIdx AND PHYSICALLOC = phLoc AND IFINDEX = 0; END IF; IF (COLUMN = 3) THEN -- Select column called COL3 SELECT COL3 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND ObjectIndex = objIdx AND PHYSICALLOC = phLoc AND IFINDEX = 0; END IF; RETURN retVal; END getBccClassRPR80217; 3.1.26 GetBccCosCommitGrp CREATE OR REPLACE FUNCTION getBccCosCommitGrp(neId IN NUMBER, phLoc IN NUMBER, infoNum IN NUMBER, objIdx IN NUMBER) RETURN VARCHAR2 IS retVal VARCHAR2(256); ifIdx NUMBER(20); BEGIN IF infoNum = 0 THEN -- cos SELECT COL2 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND ObjectIndex=objIdx AND PHYSICALLOC=phLoC AND COL2 IS NOT NULL; END IF; IF infoNum = 11 THEN -- Multicast Group 1 ifIdx := getIfCosGrp(neId, phLoc, 1); SELECT COL4 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND ObjectIndex=objIdx AND PHYSICALLOC=phLoC AND IFINDEX=ifIdx AND COL4 IS NOT NULL; END IF; IF infoNum = 12 THEN -- Class Off service Group 1 ifIdx := getIfCosGrp(neId, phLoc, 1); SELECT COL3 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND ObjectIndex=objIdx AND PHYSICALLOC=phLoC AND IFINDEX=ifIdx AND COL3 IS NOT NULL; END IF; IF infoNum = 21 THEN -- Multicast Group 2 ifIdx := getIfCosGrp(neId, phLoc, 2); SELECT COL4 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND ObjectIndex=objIdx AND PHYSICALLOC=phLoC AND IFINDEX=ifIdx AND COL4 IS NOT NULL; END IF; IF infoNum = 22 THEN -- Class Off service Group 2 ifIdx := getIfCosGrp(neId, phLoc, 2); SELECT COL3 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId=neId AND ObjectIndex=objIdx AND PHYSICALLOC=phLoC AND IFINDEX=ifIdx AND COL3 IS NOT NULL; END IF; return retVal; END getBccCosCommitGrp; 3.1.27 GetBccCosInfo CREATE OR REPLACE FUNCTION getBccCosInfo(neId IN NUMBER, phLoc IN NUMBER, objIdx IN NUMBER, COLUMN IN VARCHAR2) RETURN VARCHAR2 IS retVal VARCHAR2(256); BEGIN SELECT COL26 INTO retVal FROM EQPT_INFO_TABLE WHERE NeDbAccessId = neId AND ObjectIndex = objIdx AND PHYSICALLOC = phLoc AND COL2 = COLUMN; RETURN retVal; END getBccCosInfo; 3.1.28 GetBGFromBGObj Create or replace function getBGFromBGObj(neifindex in number) return number IS if1 number; i_neifindex number; BEGIN IF neifindex < 0 THEN i_neifindex := neifindex+power(2,32); ELSE i_neifindex:=neifindex; END IF; SELECT mod(trunc(i_neifindex/power(2,16)),power(2,8)) INTO if1 FROM dual; RETURN if1; END getBGFromBGObj; 3.1.29 GetBGFromQoSObj Create or replace function getBGFromQoSObj (neifindex in number) return number IS if2 number; o_if2 number; i_neifindex number; BEGIN IF neifindex < 0 THEN i_neifindex := neifindex+power(2,32); ELSE i_neifindex:=neifindex; END IF; SELECT mod(trunc(i_neifindex/power(2,8)),power(2,8)) INTO if2 FROM dual; SELECT decode(if2,0,'',if2) INTO o_if2 FROM dual; RETURN o_if2; END getBGFromQoSObj; 3.1.30 GetCktDestNodeId CREATE or replace function getCktDestNodeId (CKTNODEIDIN number,CKTUNIQUEIDIN number) return number IS cursor cktnodeidcursor is select CktCtpNodeId from circuit_ctp_tbl where CktNodeId = CKTNODEIDIN and CktUniqueId = CKTUNIQUEIDIN and ctp_type = 2; finalinodeids varchar2(10000); BEGIN for rec in cktnodeidcursor loop if cktnodeidcursor%ROWCOUNT = 1 then finalinodeids:= rec.CktCtpNodeId; else finalinodeids := finalinodeids\|\|','\|\|rec.CktCtpNodeId; end if; end loop; return finalinodeids; END getCktDestNodeId; 3.1.31 GetCktSecSrcIfIndex CREATE or replace function getCktSecSrcIfIndex (CKTNODEIDIN number,CKTUNIQUEIDIN number) return number IS o_CktSecSrcIfIndex number; BEGIN select CktCtpIfIndex into o_CktSecSrcIfIndex from circuit_ctp_tbl where CktCtpNodeId = CKTNODEIDIN and CktUniqueId = CKTUNIQUEIDIN and ctp_type = 1; return o_CktSecSrcIfIndex; END getCktSecSrcIfIndex; 3.1.32 GetCktSecSrcPhysicalLoc CREATE or replace function getCktSecSrcPhysicalLoc (CKTNODEIDIN number,CKTUNIQUEIDIN number)return number IS o_CktSecSrcPhysicalLoc number; BEGIN select CktCtpPhysicalLoc into o_CktSecSrcPhysicalLoc from circuit_ctp_tbl where CktCtpNodeId = CKTNODEIDIN and CktUniqueId = CKTUNIQUEIDIN and ctp_type = 1; return o_CktSecSrcPhysicalLoc; END getCktSecSrcPhysicalLoc; 3.1.33 GetCktSecSrcPortName CREATE or replace function getCktSecSrcPortName (CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS o_CktSecSrcPortName varchar2(64); BEGIN select CKTCTPPORTNAME into o_CktSecSrcPortName from circuit_ctp_tbl where CktNodeId = CKTNODEIDIN and CktUniqueId = CKTUNIQUEIDIN and ctp_type = 1; return o_CktSecSrcPortName; END getCktSecSrcPortName; / 3.1.34 GetCktSrcIfIndex CREATE or replace function getCktSrcIfIndex (CKTNODEIDIN number,CKTUNIQUEIDIN number) return number IS o_CktSrcIfIndex number; BEGIN select CktCtpIfIndex into o_CktSrcIfIndex from circuit_ctp_tbl where CktCtpNodeId = CKTNODEIDIN and CktUniqueId = CKTUNIQUEIDIN and ctp_type = 0; return o_CktSrcIfIndex; END getCktSrcIfIndex; 3.1.35 GetCktSrcPhysicalLoc CREATE or replace function getCktSrcPhysicalLoc (CKTNODEIDIN number,CKTUNIQUEIDIN number) return number IS o_CktSrcPhysicalLoc number; BEGIN select CktCtpPhysicalLoc into o_CktSrcPhysicalLoc from circuit_ctp_tbl where CktCtpNodeId = CKTNODEIDIN and CktUniqueId = CKTUNIQUEIDIN and ctp_type = 0; return o_CktSrcPhysicalLoc; END getCktSrcPhysicalLoc; 3.1.36 GetCktSrcPortName CREATE or replace function getCktSrcPortName (CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS o_CktSrcPortName varchar2(64); BEGIN select CKTCTPPORTNAME into o_CktSrcPortName from circuit_ctp_tbl where CktNodeId = CKTNODEIDIN and CktUniqueId = CKTUNIQUEIDIN and ctp_type = 0; return o_CktSrcPortName; END getCktSrcPortName; 3.1.37 GetCollapsedPortname CREATE or replace function getcollapsedPortname(CKTNODEIDIN number,CKTUNIQUEIDIN number) return varchar2 IS cursor eqptcollpasedport is select CktNodeId, CktCtpPhysicalLoc, CktCtpIfIndex, CktCtpModuleType from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and CktNodeId is not null and CktCtpPhysicalLoc is not null and CktCtpIfIndex is not null and CktCtpModuleType is not null; finaleqptinfoport varchar2(10000); DestNedbaccessid varchar2(1000); BEGIN for rec in eqptcollpasedport loop SELECT nedbaccessid INTO DestNedbaccessid FROM ne_info_table WHERE nenodeid= rec.CktNodeId and rec.CktNodeId != 0; if eqptcollpasedport%ROWCOUNT = 1 then finaleqptinfoport:= '@'\|\|eqptinfoport(DestNedbaccessid,rec.CktCtpPhysicalLoc,rec.CktCtpIfIndex,rec.CktCtpModul eType); else finaleqptinfoport := finaleqptinfoport\|\|','\|\|'@'\|\|eqptinfoport(DestNedbaccessid,rec.CktCtpPhysicalLoc,rec.CktCt pIfIndex,rec.CktCtpModuleType); end if; end loop; return finaleqptinfoport; END; 3.1.38 GetEqptInfoPhysicalLoc Create or replace function getEqptInfoPhysicalLoc(physicalLoc in number,moduleIndex in number) return number IS eqptphysicalLoc number; eqptport number; slot number; port number; pim number; ppm number; slotmoduletype number; portmoduletype number; BEGIN slot:= getSlot(physicalLoc); port:= getPortWithPimPpm(physicalLoc); pim:= getPIM(physicalLoc); ppm:= getPPM(physicalLoc); slotmoduletype:=getSlotModuleType(moduleIndex); SELECT trunc(mod(moduleIndex,power(2,16))) INTO portmoduletype FROM dual; IF (pim = 0 AND slotmoduletype = 157 AND portmoduletype != 34 AND ppm = 0) THEN port:= port + 21; eqptphysicalLoc:= getPhysicalLoc(slot,port); ELSE eqptphysicalLoc:= physicalLoc; END IF; RETURN eqptphysicalLoc; END getEqptInfoPhysicalLoc; 3.1.39 GetIfCosGrp CREATE OR REPLACE FUNCTION getIfCosGrp(neId IN NUMBER, phLoc IN NUMBER, first_last IN NUMBER ) RETURN NUMBER IS CURSOR MCursor IS SELECT IFINDEX FROM EQPT_INFO_TABLE WHERE NEDBACCESSID=neId AND OBJECTINDEX=189 AND PHYSICALLOC=phLoc AND COL3 IS NOT NULL; IfIndex NUMBER(20); find NUMBER(20); BEGIN IF (first_last = 1) THEN -- find first find := 1; END IF; IF (first_last = 2) THEN -- find last find := 0; END IF; FOR rec IN MCursor LOOP IF MCursor%ROWCOUNT = first_last THEN IfIndex := rec.IFINDEX; return IfIndex; END IF; IfIndex := rec.IFINDEX; END LOOP; RETURN IfIndex; END; 3.1.40 GetInterfaceId create or replace function GetInterfaceId (p_nedbaccessid IN number, p_moduleorIfIndex IN number, p_objectType IN number, p_type IN varchar2) return varchar2 IS o_interfaceId varchar2(64); o_modeltype number:=0; BEGIN IF (p_moduleOrIfindex = 0 ) THEN IF (p_objectType = 0 ) THEN select activevalue into o_interfaceId from ctm_config_table where sectionname='transportManager' and propertyname='ems-sys-id'; ELSE SELECT nemodeltype INTO o_modeltype FROM ne_info_table WHERE nedbaccessid=p_nedbaccessid; IF (o_modeltype = 34) THEN o_interfaceId:='N/A'; ELSE o_interfaceId:='0'; END IF; END IF; END IF; IF (p_moduleOrIfindex = -1) THEN o_interfaceId:='N/A'; END IF; IF ((p_moduleOrIfindex != 0 ) and (p_moduleOrIfindex != -1)) THEN IF (p_nedbaccessid =0 ) THEN IF (p_type like 'Maximum login attempts exceeded%') THEN BEGIN SELECT username INTO o_interfaceId FROM user_table WHERE userid=p_moduleorIfIndex; exception when no_data_found then o_interfaceId:= displayIp(p_moduleOrIfindex); END; ELSIF (p_type like 'Server Monitor Threshold Crossed%') THEN select displayname into o_interfaceId FROM SERVER_PARAMETER_TABLE WHERE ParameterIndex=p_moduleorIfIndex; ELSE SELECT nesysid INTO o_interfaceId FROM ne_info_table WHERE nedbaccessid=p_moduleorIfIndex AND nemodeltype!=34; END IF; ELSE SELECT nemodeltype INTO o_modeltype FROM ne_info_table WHERE nedbaccessid=p_nedbaccessid; IF (o_modeltype = 34) THEN o_interfaceId:='N/A'; ELSE o_interfaceId:=p_moduleorIfIndex; END IF; END IF; END IF; RETURN o_interfaceId; END GetInterfaceId; 3.1.41 Get_Inventory_Note CREATE OR REPLACE FUNCTION "GET_INVENTORY_NOTE" (sn IN VARCHAR2) RETURN VARCHAR2 AS retVal VARCHAR2(4000); cnt NUMBER; BEGIN select count() into cnt from ONS15454_INVENTORY_NOTES where SERIALNUMBER = sn; if (cnt=0) THEN retVal:=''; ELSE select NOTE into retVal from ONS15454_INVENTORY_NOTES where SERIALNUMBER = sn; END IF; RETURN retVal; END GET_INVENTORY_NOTE; 3.1.42 GetLinkType CREATE OR REPLACE function GetLinkType (p_circuitprot IN number, p_circuittype IN number, p_linktype IN number, p_spanid IN number ) return number IS o_linkcount number:=0; o_linktype_18 number:=18; o_linktype_13 number:=13; o_linktype_4 number:=4; o_linktype_0 number:=0; o_linktype number:=p_linktype; BEGIN IF ((p_circuitprot = 5) OR (p_circuitprot = 6)) THEN select count() into o_linkcount from CIRCUIT_CTP_TBL ctp1, CIRCUIT_CTP_TBL ctp2, CIRCUIT_SPAN_BS_TBL spn, LINK_TABLE lnk, CIRCUIT_BS_TBL circuit where spn.CKTSPANSRCCTPACCESSID = ctp1.CTPACCESSID AND spn.CKTSPANDSTCTPACCESSID = ctp2.CTPACCESSID AND circuit.CKTNODEID = spn.CKTNODEID AND circuit.CKTUNIQUEID = spn.CKTUNIQUEID AND ( ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND ctp1.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE AND ctp2.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND ctp2.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE ) OR ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND ctp1.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE AND ctp2.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND ctp2.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE ) ) AND lnk.linktype in (0, 4) AND spn.CKTSPANBSID = p_spanid; if ( o_linkcount > 1 ) then o_linktype := o_linktype_4; else o_linktype := o_linktype_0; end if; else if (p_linktype = 4 ) THEN o_linktype := o_linktype_0; end if; END IF; if ( p_circuittype = 38 ) THEN select count() into o_linkcount from CIRCUIT_CTP_TBL ctp1, CIRCUIT_CTP_TBL ctp2, CIRCUIT_SPAN_BS_TBL spn, LINK_TABLE lnk, CIRCUIT_BS_TBL circuit where spn.CKTSPANSRCCTPACCESSID = ctp1.CTPACCESSID AND spn.CKTSPANDSTCTPACCESSID = ctp2.CTPACCESSID AND circuit.CKTNODEID = spn.CKTNODEID AND circuit.CKTUNIQUEID = spn.CKTUNIQUEID AND ( ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND ctp1.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE AND ctp2.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND ctp2.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE ) OR ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND ctp1.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE AND ctp2.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND ctp2.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE ) ) AND lnk.linktype = o_linktype_18 AND spn.CKTSPANBSID = p_spanid; if ( o_linkcount > 0 ) then o_linktype := o_linktype_18; else select count() into o_linkcount from CIRCUIT_CTP_TBL ctp1, CIRCUIT_CTP_TBL ctp2, CIRCUIT_SPAN_BS_TBL spn, LINK_TABLE lnk, CIRCUIT_BS_TBL circuit where spn.CKTSPANSRCCTPACCESSID = ctp1.CTPACCESSID AND spn.CKTSPANDSTCTPACCESSID = ctp2.CTPACCESSID AND circuit.CKTNODEID = spn.CKTNODEID AND circuit.CKTUNIQUEID = spn.CKTUNIQUEID AND (( ctp1.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND ctp1.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE AND ctp2.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND ctp2.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE ) OR ( ctp1.CKTCTPPHYSICALLOC = lnk.LINKDSTPHYSICALLOC AND ctp1.CKTCTPNEDBACCESSID = lnk.LINKDSTNODE AND ctp2.CKTCTPPHYSICALLOC = lnk.LINKSRCPHYSICALLOC AND ctp2.CKTCTPNEDBACCESSID = lnk.LINKSRCNODE )) AND lnk.linktype = o_linktype_13 AND spn.CKTSPANBSID = p_spanid; if ( o_linkcount > 0 ) THEN o_linktype := o_linktype_13; end if; end if; END IF; return o_linktype; END GetLinkType; 3.1.43 GetLowBits CREATE OR REPLACE function getLowBits (aNum number, numOfBits integer) RETURN integer AS result number; modVal number; BEGIN modVal := power (2, numOfBits); result := mod (aNum, modVal); return result; END; 3.1.44 GetMgx88xxMgmtState create or replace function getMgx88xxMgmtState(p_isConnected in number) return number IS i_mstate number; BEGIN IF ( p_isConnected = 1 ) THEN i_mstate := 1; ELSE i_mstate := 2; END IF; RETURN i_mstate; END getMgx88xxMgmtState; 3.1.45 GetMgx88xxSyncMode create or replace function getMgx88xxSyncMode(p_discoveryState in number) return number IS i_mode number; BEGIN IF ( p_discoveryState = 1 ) THEN i_mode := 5; ELSE IF ( p_discoveryState = 2 ) THEN i_mode := 1; ELSE IF ( p_discoveryState = 3 ) THEN i_mode := 2; ELSE IF ( p_discoveryState = 4 ) THEN i_mode := 3; ELSE i_mode := 1; END IF; END IF; END IF; END IF; RETURN i_mode; END getMgx88xxSyncMode; 3.1.46 GetMLcardAddnlInfo This function indicates whether the ML card has been preprovisioned. CREATE OR REPLACE function getMLcardAddnlInfo(AddnlInfo in varchar2, state in number) RETURN varchar2 IS BEGIN IF (state = 0) THEN RETURN 'EID-7077: ML Card was Pre-provisioned'; END IF; RETURN AddnlInfo; END getMLcardAddnlInfo; 3.1.47 GetModelTypeFromPhysicalLoc CREATE OR REPLACE function getModelTypeFromPhysicalloc (aPHYSICALLOC number) RETURN number AS aModelType number(20); BEGIN aModelType := removeLowBits(aPHYSICALLOC, 56); return aModelType; END; 3.1.48 GetModuleName create or replace function getModuleName(p_nedbaccessid in number, p_moduletype in number, p_eventType in number) return varchar2 IS o_modulename varchar2(64); i_modeltype number; BEGIN IF ( p_nedbaccessid = 0 or ( p_eventType >= 4 AND p_moduletype = 9999) ) THEN o_modulename := 'N/A'; ELSE SELECT nemodeltype INTO i_modeltype FROM ne_info_table WHERE nedbaccessid=p_nedbaccessid; SELECT ModuleName INTO o_modulename FROM module_type_table WHERE moduletype=p_moduletype AND modeltype=i_modeltype; END IF; RETURN o_modulename; Exception WHEN NO_DATA_FOUND THEN RETURN 'Unsupported'; END getModuleName; 3.1.49 GetModuleName_PM create or replace function getModuleName_pm(p_nenodeid in number, p_moduletype in number) return varchar2 IS o_modulename varchar2(64); i_modeltype number; BEGIN IF (p_nenodeid=0 OR p_moduletype=0) THEN o_modulename:='SYSTEM'; ELSE SELECT nemodeltype INTO i_modeltype FROM ne_info_table WHERE nenodeid=p_nenodeid; SELECT ModuleName INTO o_modulename FROM module_type_table WHERE moduletype=p_moduletype AND modeltype=i_modeltype; END IF; RETURN o_modulename; Exception WHEN NO_DATA_FOUND THEN RETURN 'Unsupported'; END getModuleName_pm; 3.1.50 GetModuleName1 create or replace function getModuleName1(p_nenodeid in number, p_moduletype in number) return varchar2 IS o_modulename varchar2(64); i_modeltype number; p_slotmoduletype number; BEGIN SELECT trunc(p_moduletype/power(2,16)) INTO p_slotmoduletype FROM dual; IF (p_nenodeid=0 OR p_slotmoduletype=0) THEN o_modulename:='SYSTEM'; ELSE SELECT nemodeltype INTO i_modeltype FROM ne_info_table WHERE nenodeid=p_nenodeid; SELECT ModuleName INTO o_modulename FROM module_type_table WHERE moduletype=p_slotmoduletype AND modeltype=i_modeltype; END IF; RETURN o_modulename; Exception WHEN NO_DATA_FOUND THEN RETURN 'Unsupported'; END getModuleName1; 3.1.51 GetModuleName2 create or replace function getModuleName2 (p_moduletype number, p_modeltype number) return varchar2 IS o_modulename varchar2(64); p_slotmoduletype number; BEGIN SELECT trunc(p_moduletype/power(2,16)) INTO p_slotmoduletype FROM dual; IF (p_slotmoduletype=0) THEN o_modulename:='SYSTEM'; ELSE SELECT ModuleName INTO o_modulename FROM module_type_table WHERE moduletype=p_slotmoduletype AND modeltype=p_modeltype; END IF; RETURN o_modulename; Exception WHEN NO_DATA_FOUND THEN RETURN 'Unsupported'; END getModuleName2; 3.1.52 GetgetProtectMLcardConfigState --This function returns the config status of a protected card, return -1 if not found. CREATE OR REPLACE function getProtectMLcardConfigState(NEDbId in number, Slot in number) RETURN number IS confStValue number; BEGIN IF (NEDbId = 0) THEN RETURN -1; END IF; Select configState into confStValue from MCard_Table where NeDbaccessID = NEDbId and SlotNumber = Slot; RETURN confStValue; END getProtectMLcardConfigState; 3.1.53 GetNEDBAccessID create or replace function getNEDBACCESSID(NodeId_param number) return number is nedbaccessout number; begin begin select nedbaccessid into nedbaccessout from ne_info_table where NENODEID = NodeId_param and NENODEID !=0; exception when no_data_found then null; end; return nedbaccessout; end; / show error; rem rem Function that return the IP address rem 3.1.54 GetNEIPAddr CREATE OR REPLACE function getNEIPAddr (p_NEDBAccessid number) return raw is nedbIPaddr raw(16); nedbIPaddrV4 raw(4); c_nedbIPaddr number; isipv6 number; begin select is_ipv6 into isipv6 from ne_info_table where nedbaccessid = p_NEDBAccessid; if isipv6 = 1 then select neipv6addr into nedbIPaddr from ne_info_table where nedbaccessid = p_NEDBAccessid; return nedbIPaddr; else select neipaddr into c_nedbIPaddr from ne_info_table where nedbaccessid = p_NEDBAccessid; -- dbms_output.put_line ('IPv4 =' \|\| c_nedbIPaddr \|\| 'IPv4 BIN =' \|\| binary_utility.to_hex(c_nedbIPaddr)); nedbIpaddrV4 := utl_raw.CAST_FROM_BINARY_INTEGER(c_nedbIPaddr); return nedbIPaddrV4; end if; exception when no_data_found then return null; end getNEIPAddr; / show error; rem rem Function that check if the IP is a IPv4 (return 0) or a IPv6 (return 1) rem 3.1.55 GetObjectTypeFromIfIndex CREATE OR REPLACE function getObjectTypeFromIfIndex (aIfindex number) RETURN number AS aObjectType number(10); BEGIN if(sign(aIfindex) = -1) then aObjectType := removeLowBits(aIfindex+power(2,32), 24); else aObjectType := removeLowBits(aIfindex, 24); end if; return aObjectType; END; 3.1.56 GetObjectTypeFromIfIndex2 CREATE OR REPLACE function getObjectTypeFromIfIndex2 ( amodelType number,aIfindex number) RETURN number AS aObjectType number(10); BEGIN if(sign(aIfindex) = -1) then aObjectType := removeLowBits(aIfindex+power(2,32), 24); else aObjectType := removeLowBits(aIfindex, 24); end if; if (amodelType in (3, 14, 5, 29, 18, 30)) THEN select decode(aobjectType, 19, 5130, 83, 5073, aobjectType+5000) into aobjectType from dual; end if; return aObjectType; END; 3.1.57 GetOutIPAddr CREATE OR REPLACE function getOUTIPAddr (p_ossname varchar) return raw is nedbIPaddr raw(16); nedbIPaddrV4 raw(4); c_nedbIPaddr number; isipv6 number; begin select is_ipv6 into isipv6 from oss_user_table where ossname = p_ossname; if isipv6 = 1 then select IPV6Addr into nedbIPaddr from oss_user_table where ossname = p_ossname; return nedbIPaddr; else select IPAddress into c_nedbIPaddr from oss_user_table where ossname = p_ossname; -- dbms_output.put_line ('IPv4 =' \|\| c_nedbIPaddr \|\| 'IPv4 BIN =' \|\| binary_utility.to_hex(c_nedbIPaddr)); nedbIpaddrV4 := utl_raw.CAST_FROM_BINARY_INTEGER(c_nedbIPaddr); return nedbIPaddrV4; end if; exception when no_data_found then return null; end getOUTIPAddr; 3.1.58 GetPhysicalLoc Create or replace function getPhysicalLoc(slot in number,port in number) return number IS physicalLoc number; physlot number; BEGIN physlot:= slot * power(2,16); physicalLoc:= physlot + port; RETURN physicalLoc; END getPhysicalLoc; 3.1.59 GetPhysicalLocWithPIMPPM Create or replace function getPhysicalLocWithPimPpm(shelf in number, slot in number, pim in number, ppm in number, port in number) return number IS physicalLoc number; phyrack number; physhelf number; physlot number; phypim number; phyppm number; BEGIN phyrack:= 1 * power(2,40); physhelf:= shelf * power(2,32); physlot:= slot * power(2,24); phypim:= pim * power(2,16); phyppm:= ppm * power(2,10); physicalLoc:= phyrack + physhelf + physlot + phypim + phyppm + port; RETURN physicalLoc; END getPhysicalLocWithPimPpm; 3.1.60 GetPIM Create or replace function getPIM (physicalloc in number) return number IS pim number; BEGIN SELECT trunc(mod(trunc(physicalloc/power(2,16)),power(2,8))) INTO pim from dual; RETURN pim; END getPIM; 3.1.61 GetPMParameterValue CREATE OR REPLACE function GetPMParameterValue (p_paramvalue IN number, l_paramvalue IN number ) return number IS o_paramvalue number; BEGIN IF (p_paramvalue = -1 AND l_paramvalue <> -1) THEN o_paramvalue:= l_paramvalue; ELSIF (l_paramvalue = -1 AND p_paramvalue <> -1) THEN o_paramvalue:= p_paramvalue; ELSE o_paramvalue:= p_paramvalue; END IF; RETURN o_paramvalue; END GetPMParameterValue; 3.1.62 GetPolicyMapType Create or replace function getPolicyMapType(neifindex in number) return number IS if1 number; i_neifindex number; BEGIN IF neifindex < 0 THEN i_neifindex := neifindex+power(2,32); ELSE i_neifindex:=neifindex; END IF; SELECT mod(trunc(i_neifindex/power(2,16)),power(2,8)) INTO if1 FROM dual; RETURN if1; END getPolicyMapType; 3.1.63 GetPort Create or replace function getPort (physicalloc in number) return number IS port number; ppm number; BEGIN SELECT trunc(mod(physicalloc,power(2,10))) INTO port from dual; RETURN port; END getPort; 3.1.64 GetPort_XTC Create or replace function getPort_xtc (physicalloc in number, moduletype_param in number ) return number IS ppm number; port number; final_physicalloc number; two_bytes number := power(2,16); BEGIN ppm := getPPM(physicalloc); port := getPort(physicalloc); if ( moduletype_param = 47 ) then -- append_file('/oraclesw9i', 'testfjune9.log', 'Port No. Passed is : '\|\| port); if ( port <= 28 ) then port := 1; else port := mod (port, 28); if (port = 0) then port := 28; end if; end if; -- append_file('/oraclesw9i', 'testfjune9.log', 'Returning port no. : '\|\| port); end if; final_physicalloc := trunc(physicalloc/two_bytes) * two_bytes; final_physicalloc := final_physicalloc + port; RETURN final_physicalloc; END getPort_xtc; 3.1.65 GetPortModuleName create or replace function getPortModuleName(p_nenodeid in number, p_moduletype in number) return varchar2 IS o_modulename varchar2(64); i_modeltype number; p_portmoduletype number; BEGIN SELECT trunc(mod(p_moduletype,power(2,16))) INTO p_portmoduletype FROM dual; IF (p_nenodeid=0 OR p_portmoduletype=0) THEN o_modulename:='SYSTEM'; ELSE SELECT nemodeltype INTO i_modeltype FROM ne_info_table WHERE nenodeid=p_nenodeid; SELECT ModuleName INTO o_modulename FROM module_type_table WHERE moduletype=p_portmoduletype AND modeltype=i_modeltype; END IF; RETURN o_modulename; Exception WHEN NO_DATA_FOUND THEN RETURN 'Unsupported'; END getPortModuleName; 3.1.66 GetPortModuleName2 create or replace function getPortModuleName2(p_moduleindex number, p_modeltype number) return varchar2 IS o_modulename varchar2(64); p_portmoduletype number; BEGIN SELECT mod(p_moduleindex, power(2,16)) INTO p_portmoduletype FROM dual; IF (p_portmoduletype=0 ) THEN o_modulename:= getModuleName2(p_moduleindex, p_modeltype); ELSE SELECT ModuleName INTO o_modulename FROM module_type_table WHERE moduletype=p_portmoduletype AND modeltype=p_modeltype; END IF; RETURN o_modulename; Exception WHEN NO_DATA_FOUND THEN RETURN 'Unsupported'; END getPortModuleName2; 3.1.67 GetPortName CREATE or REPLACE function GetPortName (p_nedbaccessid IN number, p_physicalloc IN number, p_ifindex IN number, p_moduletype IN number) RETURN varchar2 IS iftype number; i_ifindex number; o_portname varchar2(256); model_type number; module_type number; physical_loc number; ppm number; slot number; port number; BEGIN IF p_ifindex < 0 THEN i_ifindex := p_ifindex+power(2,32); ELSE i_ifindex:=p_ifindex; END IF; module_type := p_moduletype; ppm := GetPpm(p_physicalloc); SELECT round(i_ifindex/power(2,24)) INTO iftype FROM dual; SELECT nemodeltype INTO model_type FROM ne_info_table WHERE nedbaccessid = p_nedbaccessid; BEGIN IF ( iftype = 31 and p_moduletype=47 ) THEN /* 31=ds1 47=XTC / SELECT col200 INTO o_portname FROM eqpt_info_table WHERE nedbaccessid=p_nedbaccessid AND physicalloc=p_physicalloc AND objectindex=204 AND moduletype=p_moduletype; ELSIF (iftype = 33 and p_moduletype = 47 ) THEN / 33=ds3 47=XTC / SELECT col200 INTO o_portname FROM eqpt_info_table WHERE nedbaccessid=p_nedbaccessid AND physicalloc=p_physicalloc AND objectindex=208 AND moduletype=p_moduletype; ELSIF (p_moduletype = 34 ) THEN / 34 = ds1 port on XTC or 15310/ SELECT col200 INTO o_portname FROM eqpt_info_table WHERE nedbaccessid=p_nedbaccessid AND physicalloc=p_physicalloc AND objectindex in (204,340) / 204=Ds1Line 340=Ds1LineModel / AND moduletype in (47,233,234); / 47=XTC 233=DS1_28_DS3_EC1_3 234=DS1_84_DS3_EC1_3 / ELSIF (p_moduletype = 32 ) THEN / 32 = ds3 port on XTC or 15310/ SELECT col200 INTO o_portname FROM eqpt_info_table WHERE nedbaccessid=p_nedbaccessid AND physicalloc=p_physicalloc AND objectindex in (208,341) / 208=Ds3Line 341=Ds3LineModel / AND moduletype in (47,233,234); / 47=XTC 233=DS1_28_DS3_EC1_3 234=DS1_84_DS3_EC1_3 / ELSIF (p_moduletype = 102 or p_moduletype = 103 or p_moduletype = 163 or p_moduletype = 164 or p_moduletype = 187 ) THEN / For ML series cards/ IF (p_ifindex = 1) THEN SELECT col200 INTO o_portname FROM eqpt_info_table WHERE nedbaccessid=p_nedbaccessid AND physicalloc=p_physicalloc AND objectindex in (136,257); ELSE SELECT col200 INTO o_portname FROM eqpt_info_table WHERE nedbaccessid=p_nedbaccessid AND physicalloc=p_physicalloc AND objectindex in (134,255); END IF; ELSE SELECT col200 INTO o_portname FROM eqpt_info_table WHERE nedbaccessid=p_nedbaccessid AND physicalloc=p_physicalloc AND moduleType=p_moduletype AND objectindex in (106,109,114,115,121,122,123,125,134,136,141,142,143,147,169,184,185,193,196,198,204,206,2 08,209,216,252,254,255,257,267,271,281); END IF; EXCEPTION WHEN TOO_MANY_ROWS THEN null; END; RETURN o_portname; END GetPortName; 3.1.68 GetPortWithPIMPPM Create or replace function getPortWithPimPpm (physicalloc in number) return number IS port number; BEGIN SELECT trunc(mod(physicalloc,power(2,13))) INTO port from dual; RETURN port; END getPortWithPimPpm; 3.1.69 GetPosPortState This function takes the topology type, NE ID, slot, and port numbers and returns the POS port state. CREATE OR REPLACE function getPosPortState(topoType in number, neId in number, slotNo in number, portNo in number) RETURN varchar2 IS state varchar2(256); BEGIN if (topoType !=3) then select COL5 into state from EQPT_INFO_TABLE where NeDbAccessId = neId and ObjectIndex = 166 and getSlot(physicalLoc) = slotNo and getPort(physicalLoc) = portNo; return state; else state := 0; return state; end if; END getPosPortState; 3.1.70 GetPosPortStateForRPR802 This function takes the topology type, NE ID, slot, and port numbers and returns the POS port state for 802.17 RPR topologies. CREATE OR REPLACE function getPosPortStateForRPR802(topoType in number, neId in number, slotNo in number, portNo in number) RETURN varchar2 IS state varchar2(256); BEGIN if (topoType =3) then select COL5 into state from EQPT_INFO_TABLE where NeDbAccessId = neId and ObjectIndex = 284 and getSlot(physicalLoc) = slotNo and IfIndex = portNo; return state; else state := 0; return state; end if; END getPosPortStateForRPR802; 3.1.71 GetPPM Create or replace function getPPM (physicalloc in number) return number IS ppm number; BEGIN SELECT trunc(mod(trunc(physicalloc/power(2,10)),power(2,6))) INTO ppm from dual; RETURN ppm; END getPPM; 3.1.72 GetProtectMLCardInfo This function is used of the ML-series card is preprovisioned. CREATE OR REPLACE function getProtectMLcardInfo(NEDbId in number, Slot in number) RETURN varchar2 IS SysId varchar2(256); CardModule varchar2(256); ProtectCard varchar2(256); equipType number; model number; BEGIN IF (NEDbId = 0) THEN RETURN 'none'; END IF; Select NESysID into SysId from Ne_Info_table where NeDbaccessID = NEDbId; Select NEModelType into model from Ne_Info_table where NeDbaccessID = NEDbId; Select EquipmentType into equipType from ONS15454_ne_inventory_table where NeDbaccessID = NEDbId and getSlot(PhysicalLoc) = Slot; Select ModuleName into CardModule from module_type_table where moduleType = equipType and modelType = model; ProtectCard := SysId \|\| ':Slot ' \|\| Slot \|\| ' (' \|\| CardModule \|\| ')'; RETURN ProtectCard; END getProtectMLcardInfo; 3.1.73 GetQoSPhLoc CREATE OR REPLACE FUNCTION Getqosphloc(slot IN NUMBER, cardPhloc IN NUMBER) RETURN NUMBER IS PhLoc NUMBER(20); counter NUMBER; BEGIN PhLoc:=Getphysicallocwithpimppm(Getshelf(cardPhloc), slot, 255, 63, 0); counter := countCosPh(PhLoc); IF (counter = 0) THEN PhLoc:=Getphysicallocwithpimppm(Getshelf(cardPhloc), slot, 255, 63, 1); END IF; RETURN PhLoc; END Getqosphloc; /show errors; 3.1.74 GetShelf Create or replace function getShelf (physicalloc in number) return number IS shelf number; BEGIN SELECT trunc(mod(trunc(physicalloc/power(2,32)),power(2,8))) INTO shelf from dual; RETURN shelf; END getShelf; 3.1.75 GetSlot Create or replace function getSlot (physicalloc in number) return number IS slot number; BEGIN SELECT mod(trunc(physicalloc/power(2,24)),power(2,8)) INTO slot from dual; RETURN slot; END getSlot; 3.1.76 GetSlotModuleName create or replace function getSlotModuleName(p_nenodeid in number, p_moduletype in number) return varchar2 IS o_modulename varchar2(64); i_modeltype number; p_slotmoduletype number; BEGIN SELECT trunc(p_moduletype/power(2,16)) INTO p_slotmoduletype FROM dual; IF (p_nenodeid=0 OR p_slotmoduletype=0) THEN o_modulename:='SYSTEM'; ELSE SELECT nemodeltype INTO i_modeltype FROM ne_info_table WHERE nenodeid=p_nenodeid; SELECT ModuleName INTO o_modulename FROM module_type_table WHERE moduletype=p_slotmoduletype AND modeltype=i_modeltype; END IF; RETURN o_modulename; Exception WHEN NO_DATA_FOUND THEN RETURN 'Unsupported'; END getSlotModuleName; 3.1.77 GetSlotModuleType create or replace function getSlotModuleType(p_moduletype in number) return varchar2 IS p_slotmoduletype number; BEGIN SELECT trunc(p_moduletype/power(2,16)) INTO p_slotmoduletype FROM dual; RETURN p_slotmoduletype; END getSlotModuleType; 3.1.78 GetSpanTblCktSpanDestNodeId CREATE or replace function getspantblCktSpanDestNodeId (CKTNODEIDIN number,CKTUNIQUEIDIN number)return varchar2 IS cursor ctpspandestnodeidcursor is select CKTCTPNODEID from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID =CKTUNIQUEIDIN and ctp_type=4; ctpspandestnodeid_list varchar2(10000); BEGIN for rec in ctpspandestnodeidcursor loop if ctpspandestnodeidcursor%ROWCOUNT = 1 then ctpspandestnodeid_list:= rec.CKTCTPNODEID; else ctpspandestnodeid_list := ctpspandestnodeid_list\|\|','\|\|rec.CKTCTPNODEID; end if; end loop; return ctpspandestnodeid_list; END getspantblCktSpanDestNodeId; 3.1.79 GetSpanTblCktSpanSrcNodeId CREATE or replace function getspantblCktSpanSrcNodeId (CKTNODEIDIN number,CKTUNIQUEIDIN number)return varchar2 IS cursor ctpspansrcnodeidcursor is select CKTCTPNODEID from circuit_ctp_tbl where CKTNODEID=CKTNODEIDIN and CKTUNIQUEID=CKTUNIQUEIDIN and ctp_type=3; finalspansrcnodeid_list varchar2(10000); BEGIN for rec in ctpspansrcnodeidcursor loop if ctpspansrcnodeidcursor%ROWCOUNT = 1 then finalspansrcnodeid_list:= rec.CKTCTPNODEID; else finalspansrcnodeid_list := finalspansrcnodeid_list\|\|','\|\|rec.CKTCTPNODEID; end if; end loop; return finalspansrcnodeid_list; END getspantblCktSpanSrcNodeId; 3.1.80 GetSubIf Create or replace function getSubIf(neifindex in number) return number IS i_neifindex number; subifindex number; BEGIN IF neifindex < 0 THEN i_neifindex := neifindex+power(2,32); ELSE i_neifindex:=neifindex; END IF; SELECT mod(trunc(i_neifindex/power(2,16)),power(2,16)) INTO subifindex FROM dual; RETURN subifindex; END getSubIf; 3.1.81 GetValidCktSecSrcNodeId CREATE or replace function getValidCktSecSrcNodeId (CKTNODEIDIN number,CKTUNIQUEIDIN number)return number IS o_CktSedSrcNodeId number; BEGIN select CktCtpNodeId into o_CktSedSrcNodeId from circuit_ctp_tbl where CktNodeId = CKTNODEIDIN and CktUniqueId = CKTUNIQUEIDIN and ctp_type = 1; return o_CktSedSrcNodeId; END getValidCktSecSrcNodeId; 3.1.82 GetValidCktSrcNodeId CREATE or replace function getValidCktSrcnodeid (CKTNODEIDIN number,CKTUNIQUEIDIN number) return number IS o_ValidCktSrcnodeid number; BEGIN select CktCtpNodeId into o_ValidCktSrcnodeid from circuit_ctp_tbl where CktNodeId = CKTNODEIDIN and CktUniqueId = CKTUNIQUEIDIN and ctp_type = 0; return o_ValidCktSrcnodeid; END getValidCktSrcnodeid; 3.1.83 GetValidModelType create or replace function getValidModelType (CktNodeIdIN number, CktUniqueIdIN number, ctp_typeIN number) return number IS o_ValidModelType number; BEGIN select CKTCTPMODELTYPE into o_ValidModelType from circuit_ctp_tbl where CktNodeId = CktNodeIdIN and CktUniqueId = CktUniqueIdIN and ctp_type = ctp_typeIN; return o_ValidModelType; END getValidModelType; 3.1.84 GetValidModuleType create or replace function getValidModuleType (CktNodeIdIN number, CktUniqueIdIN number, ctp_typeIN number) return number IS o_ValidModuleType number; BEGIN select CKTCTPMODULETYPE into o_ValidModuleType from circuit_ctp_tbl where CktNodeId = CktNodeIdIN and CktUniqueId = CktUniqueIdIN and ctp_type = ctp_typeIN; return o_ValidModuleType; END getValidModuleType; 3.1.85 GetValidObjectType create or replace function getValidObjectType (CktNodeIdIN number, CktUniqueIdIN number, ctp_typeIN number)return number IS o_ValidObjectType number; BEGIN select CKTCTPOBJECTTYPE into o_ValidObjectType from circuit_ctp_tbl where CktNodeId = CktNodeIdIN and CktUniqueId = CktUniqueIdIN and ctp_type = ctp_typeIN; return o_ValidObjectType; END getValidObjectType; 3.1.86 Has_Next - used by function ne_belongs_to_APC_domain to determine if a node belongs to an APC domain CREATE OR REPLACE FUNCTION has_next (base_string IN VARCHAR2, looking_for IN VARCHAR2, start_looking_at IN INTEGER, found_at_position OUT INTEGER, token OUT VARCHAR2) RETURN BOOLEAN IS target VARCHAR2(1000); BEGIN -- add delimiters at the end of the base string target := concat(base_string,looking_for); -- find position of next delimiter found_at_position := INSTR (target,looking_for,start_looking_at,1); -- read the token if found_at_position > start_looking_at then token := SUBSTR(base_string,start_looking_at,found_at_position-start_looking_at); else token := '-1'; end if; RETURN (found_at_position <> 0); END has_next; 3.1.87 IS_NUMBER CREATE OR REPLACE function IS_NUMBER(str in varchar2, rv in number:=-1) return number IS dummy number; begin dummy := to_number(str); return dummy; Exception WHEN OTHERS then return rv; end; 3.1.88 IsIPv6 CREATE OR REPLACE function isIPV6 (NEDBid number) return number is isipv6 number; begin select IS_IPV6 into isipv6 from ne_info_table where nedbaccessid = NEDBid; return isipv6; Exception when no_data_found then return 0; end isIPV6; 3.1.89 MapObjectIndex CREATE or replace function mapobjectIndex(neModelType number, nemodelIndex number, portnumber number, iftype number, moduletype number) return number is ObjIndex number; BEGIN if neModelType = 38 then if moduletype = 34 then return 340; elsif moduletype = 32 then return 341; elsif moduletype = 31 then return 342; end if; end if; if neModelType = 39 then if moduletype = 68 then return 340; elsif moduletype = 69 then return 340; elsif moduletype = 32 then return 341; elsif moduletype = 33 then return 341; end if; end if; if (neModelType = 38 or neModelType = 39) and (moduleType = 28 or moduleType = 29 or moduleType = 30) then return 334; end if; if (neModelType = 18 or neModelType = 30) and (moduleType = 28 or moduleType = 29 or moduleType = 30) then return 147; end if; if moduleType = 9 or moduleType = 106 or moduleType = 10 or moduleType = 11 or moduleType = 12 or moduleType = 13 or moduleType = 28 or moduleType = 29 or moduleType = 30 or moduleType =60 or moduleType = 85 or moduleType = 86 or moduleType = 87 or moduleType = 113 then return 109; elsif neModelType = 3 and moduleType = 4 then return 114; elsif neModelType = 3 and moduleType = 5 then return 114; elsif neModelType = 5 and moduleType = 4 then return 204; elsif neModelType = 14 and moduleType = 4 then return 114; elsif neModelType = 14 and moduleType = 5 then return 114; elsif moduleType = 6 or moduleType = 7 or moduleType = 58 then return 121; elsif moduleType = 57 then return 122; elsif moduleType = 102 or moduleType = 103 then return 136; elsif moduleType = 8 then if nemodelIndex >= 61 then return 267; else return 123; end if; elsif moduletype = 47 and portnumber <= 28 then return 204; elsif moduletype = 47 and portnumber >28 then return 208; elsif moduletype = 7 or moduletype = 8 then return 114; elsif moduletype = 14 then return 106; elsif moduletype = 15 then return 206; elsif moduletype = 31 then return 109; elsif moduletype = 32 then return 208; elsif moduletype = 34 then return 204; elsif moduletype = 62 then return 209; elsif moduletype = 118 and portnumber = 1 then return 141; elsif moduletype = 118 and portnumber = 2 then return 142; elsif moduletype = 119 and portnumber >= 1 and portnumber <= 4 then return 141; elsif moduletype = 119 and portnumber =5 then return 142; elsif moduletype = 120 and portnumber = 1 then return 141; elsif moduletype = 120 and portnumber = 2 then return 142; elsif moduletype = 121 and portnumber = 1 then return 141; elsif (moduletype = 121 and (portnumber = 2 or portnumber = 3)) then return 142; elsif moduletype = 127 and portnumber = 1 then return 198; elsif (moduletype = 127 and (portnumber = 2 or portnumber = 3)) then return 193; elsif moduletype = 128 and portnumber = 1 then return 198; elsif moduletype = 128 and portnumber >= 2 and portnumber <= 7 then return 193; elsif moduletype = 129 and portnumber = 2 then return 196; elsif (moduletype = 129 and (portnumber = 1 or portnumber = 3 or portnumber = 4)) then return 193; elsif moduletype = 130 and portnumber = 6 then return 196; elsif moduletype = 130 and portnumber >= 1 and portnumber <= 5 then return 193; elsif moduletype = 131 and portnumber >= 1 and portnumber <= 32 then return 194; elsif moduletype = 131 and portnumber = 33 then return 193; elsif moduletype = 132 and portnumber >= 1 and portnumber <= 32 then return 194; elsif moduletype = 132 and portnumber = 33 then return 193; elsif moduletype = 133 and portnumber >= 1 and portnumber <= 8 then return 194; elsif moduletype = 133 and (portnumber = 9 or portnumber = 10) then return 195; elsif (moduletype = 134 and (portnumber = 1 or portnumber = 2)) then return 194; elsif moduletype = 134 and portnumber >= 3 and portnumber <= 6 then return 193; elsif moduletype = 135 and portnumber >= 1 and portnumber <= 4 then return 194; elsif moduletype = 135 and portnumber >= 5 and portnumber <= 8 then return 193; elsif moduletype = 136 and portnumber >= 1 and portnumber <= 8 then return 194; elsif moduletype = 136 and portnumber >= 9 and portnumber <= 12 then return 193; elsif moduletype = 137 and portnumber >= 1 and portnumber <= 2 then return 195; elsif moduletype = 137 and portnumber >= 3 and portnumber <= 6 then return 193; elsif moduletype = 138 and portnumber >= 1 and portnumber <= 8 then return 195; elsif moduletype = 138 and portnumber >= 9 and portnumber <= 12 then return 193; elsif moduletype = 143 then return 216; elsif moduletype = 145 then return 267; elsif moduletype = 163 or moduletype = 164 or moduletype = 187 or moduletype = 285 or moduletype = 283 or moduletype = 284 then return 257; elsif moduletype = 108 or moduletype = 83 or moduletype = 64 or moduletype = 65 or moduletype =66 or moduletype = 67 then if nemodelindex = 33 then return 121; else return 114; end if; elsif neModelType = 3 and moduleType = 218 then return 114; elsif moduletype = 219 or moduletype = 253 or moduletype = 254 or moduletype = 220 then return 301; elsif moduletype = 201 or moduletype = 202 or moduletype = 203 then return 109; elsif moduletype = 204 then return 206; elsif moduletype = 205 then return 106; elsif moduletype = 206 or moduletype =207 or moduletype =208 then return 435; elsif moduletype = 209 and portnumber<3 then return 436; elsif moduletype = 209 and portnumber>=3 then return 434; elsif moduletype = 210 or moduletype =211 then return 431; elsif moduletype = 215 then return 437; elsif moduletype = 183 and portnumber<9 then return 141; elsif moduletype = 183 and portnumber>=9 then return 142; elsif moduletype = 184 and portnumber<9 then return 141; elsif moduletype = 184 and portnumber>=9 then return 184; elsif moduletype = 181 then return 184; elsif moduletype = 264 then return 194; elsif moduletype = 265 then return 194; elsif moduletype = 294 then return 193; elsif moduletype = 295 then return 193; elsif moduletype = 297 then return 193; elsif moduletype =182 and portnumber<5 then return 184; elsif moduletype =182 and portnumber=5 then return 142; elsif moduletype = 271 and portnumber<3 then return 141; elsif moduletype = 271 and portnumber>=3 then return 142; elsif moduletype = 272 and portnumber<21 then return 141; elsif moduletype = 272 and portnumber>=21 then return 142; elsif moduletype = 273 and portnumber<17 then return 141; elsif moduletype = 273 and portnumber>=17 then return 142; elsif moduletype = 358 and portnumber<=1 then return 141; elsif moduletype = 358 and portnumber>=2 then return 142; elsif moduletype = 300 and portnumber<=1 then return 141; elsif moduletype = 300 and portnumber>=2 then return 142; elsif moduletype = 301 and portnumber<=4 then return 141; elsif moduletype = 301 and portnumber>=5 then return 142; elsif moduletype = 356 and portnumber<=4 then return 141; elsif moduletype = 356 and portnumber>=5 then return 142; elsif moduletype = 293 then return 142; elsif moduletype = 248 and portnumber<9 then return 109; elsif moduletype = 248 and portnumber>=9 then if neModelType = 3 then return 143; elsif neModelType = 14 then return 114; end if; else return 0; end if; END; 3.1.90 MergedInvalidityList CREATE OR REPLACE function mergedInvalidityList(lineTableValue in integer, lineInvalidityList in varchar2, pathInvalidityList in varchar2) return varchar2 as TYPE column_name IS RECORD ( table_name VARCHAR2(50), view_name VARCHAR2(50) ); TYPE column_names IS TABLE OF column_name index by binary_integer; column_list column_names; tblName USER_TAB_COLUMNS.TABLE_NAME%TYPE; dbColumnNo USER_TAB_COLUMNS.COLUMN_ID%TYPE; occurence integer; value USER_TAB_COLUMNS.COLUMN_ID%TYPE; startPos integer; offSet integer; mergedInvalidityList varchar2(100); begin if(lineInvalidityList is null ) then return pathInvalidityList; end if; if(length(trim(lineInvalidityList)) =0 ) then return pathInvalidityList; end if; if(lineTableValue = -1) then return pathInvalidityList; end if; tblName := 'ONS15454_SONET_LINE_PM_TABLE'; mergedInvalidityList := pathInvalidityList; column_list(1).table_name := 'PPJCPDET'; column_list(1).view_name := 'PPJC_PDET'; column_list(2).table_name := 'NPJCPDET'; column_list(2).view_name := 'NPJC_PDET'; column_list(3).table_name := 'PPJCPGEN'; column_list(3).view_name := 'PPJC_PGEN'; column_list(4).table_name := 'NPJCPGEN'; column_list(4).view_name := 'NPJC_PGEN'; column_list(5).table_name := 'PJDIFF'; column_list(5).view_name := 'PJ_DIFF'; column_list(6).table_name := 'PJPSEC'; column_list(6).view_name := 'PJP_SEC'; column_list(7).table_name := 'PJNSEC'; column_list(7).view_name := 'PJN_SEC'; for recordNumber in 1..column_list.count loop select column_id into dbColumnNo from USER_TAB_COLUMNS where table_name = tblName and column_name = column_list(recordNumber).table_name; occurence := 1; startPos := 1; loop offSet := instr(lineInvalidityList,':',1,occurence); occurence := occurence + 1; exit when offSet = 0; if(startPos = 1) then value := to_number(substr(lineInvalidityList,startPos,offSet-startPos),9999); else value := to_number(substr(lineInvalidityList,startPos +1,offSet-startPos -1),9999); end if; if((dbColumnNo -1) = value) then select column_id into dbColumnNo from USER_TAB_COLUMNS where table_name='ONS15454_SONET_PATH_PM_TABLE' and column_name=column_list(recordNumber).table_name; mergedInvalidityList := mergedInvalidityList \|\| (dbColumnNo -1) \|\| ':'; exit; end if; startPos := offSet; end loop; end loop; if(mergedInvalidityList is null) then return ''; end if; return mergedInvalidityList; end; 3.1.91 NE_Belongs_to_APC_Domain -- to determine if a node belongs to an APC domain CREATE OR REPLACE FUNCTION ne_belongs_to_APC_domain (sides IN VARCHAR2, neid in NUMBER) RETURN number IS token varchar2(15):='-1'; neid_in_APC_domain varchar2(15):='-1'; found_at integer; found_in_token_at integer; start_point integer := 1; -- return value -1 menas that the NEDBID is not part of the domain retval NUMBER:= -1; BEGIN WHILE -- find if there is a next elemnt and assign the elemnt to token (has_next(sides,',',start_point,found_at,token)) LOOP -- take from the elemnt the first part that is the NEDBID if has_next(token,' ',1,found_in_token_at,neid_in_APC_domain) then -- is the NEDBID we are looking fore if to_number(neid_in_APC_domain) = neid then -- if yes return it by the function...to be used in the where cluse of the view retval:= neid; exit; end if; end if; --go to next elemnt start_point := found_at + 1; END LOOP; return retval; END ne_belongs_to_APC_domain; 3.1.92 RemoveFirstByteIf CREATE or REPLACE function removefirstbyteif (neifindex in number) RETURN number IS iftype number; o_neifindex number; i_neifindex number; BEGIN IF neifindex < 0 THEN i_neifindex := neifindex+power(2,32); ELSE i_neifindex:=neifindex; END IF; select mod(i_neifindex,power(2,24)) INTO o_neifindex from dual; return o_neifindex; END removefirstbyteif; 3.1.93 RemoveLowBits CREATE OR REPLACE function removeLowBits (aNum number, numOfBits integer) RETURN integer AS result number; modVal number; BEGIN modVal := power (2, numOfBits); result := trunc (aNum / modVal); --result := result modVal; return result; END; 3.1.94 Time_Diff CREATE or REPLACE FUNCTION time_diff ( DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS NDATE_1 NUMBER; NDATE_2 NUMBER; NSECOND_1 NUMBER(5,0); NSECOND_2 NUMBER(5,0); BEGIN -- Get Julian date number from first date (DATE_1) NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J')); -- Get Julian date number from second date (DATE_2) NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J')); -- Get seconds since midnight from first date (DATE_1) NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS')); -- Get seconds since midnight from second date (DATE_2) NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS')); RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1)); END time_diff; 3.1.95 TYPE AlarmInfoListType --create or replace TYPE AlarmInfoListType as table of AlarmInfoType; create TYPE AlarmInfoListType as table of AlarmInfoType; 3.1.96 TYPE AlarmInfoType --create or replace TYPE AlarmInfoType as OBJECT ( create TYPE AlarmInfoType as OBJECT ( nedbaccessid number, Alarmseqnum number, ModuleOrIfIndex number, ActiveAlarmTimeStamp date, ActiveAlarmType number, ActiveAlarmSeverity number, ActiveAlarmServEff number, ActiveAlarmAdditionalInfo varchar2(512), AckIndex number, ActiveAlarmFlag number, ActiveAlarmComment varchar2(2014), ClearAlarmTimeStamp date, NEAlarmTimeStamp date, NEAlarmClearTimeStamp date, ModuleType number, Physicalloc number, alarmstatus number, linkid link_id_tabtype, actiontype number, externalcondition varchar2(1024), ModelType number, ObjectType number, StrObjInstance VARCHAR2(256), StrDirection VARCHAR2(6), StrLocation VARCHAR2(4) ); 3.1.97 TYPE Link_Id_Tabtype create or replace TYPE link_id_tabtype is Table of number; / --drop type AlarmInfoListType; --drop type AlarmInfoType;
	Terms & Conditions \| Privacy Statement \| Cookie Policy \| Trademarks

Table Of Contents

Database Schema Functions

3.1 Prime Optical Functions