API tools faq
paste
Advertisement
fermiiii

odu_all

fermiiii
Aug 1st, 2017
160
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 9.47 KB | None | 0 0
raw download clone embed print report
  1.  
  2.        
  3.         SELECT
  4.         ODU_OBJECTS.OBJECT_ID,
  5. --        ODU_OBJECTS.OBJECT_ID,
  6.         ODU_OBJECTS.LINK_ID ODU_LINK_ID,
  7.         ODU_OBJECTS.LINK_ID_E_PLUS ODU_LINK_ID_E_PLUS,
  8.         -- ODU_OBJECTS.NE_A_NE_B NE_A_NE_B,
  9.         CASE
  10.         WHEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA, 'NODES', 'BASEKEY') IS NOT NULL THEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA, 'NODES', 'BASEKEY')
  11.         WHEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA, 'NODES', 'HOSTNAME') IS NOT NULL THEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA, 'NODES', 'HOSTNAME')
  12.         ELSE FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA, 'NODES', 'NAME') END || '-' ||
  13.         CASE
  14.         WHEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB, 'NODES', 'BASEKEY') IS NOT NULL THEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB, 'NODES', 'BASEKEY')
  15.         WHEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB, 'NODES', 'HOSTNAME') IS NOT NULL THEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB, 'NODES', 'HOSTNAME')
  16.         ELSE FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB, 'NODES', 'NAME') END ne_a_ne_b,
  17. --        ODU_OBJECTS.CAPACITY WELLENLANGE,
  18.         ODU_OBJECTS.CAPACITY    ,
  19.         ODU_OBJECTS.TAGTYPE_REAL TAGsTYPE,
  20.         ODU_OBJECTS.TAG1,
  21.         ODU_OBJECTS.TAG2,
  22.         ODU_OBJECTS.TAG3,
  23.         ODU_OBJECTS.TAG4,
  24.         -- -----------------------------------------------------------------------------------------------
  25.         CASE
  26.         WHEN ODU_OBJECTS.NEA_TYPE = 'WDM'
  27.         THEN WDM_NE_BASEKEY(ODU_OBJECTS.NEA, 'BASEKEY')
  28.         ELSE FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA,'NODES', 'BASEKEY')
  29.         END AS NEA_BK,
  30.         -- -----------------------------------------------------------------------------------------------
  31.         CASE
  32.         WHEN ODU_OBJECTS.NEA_TYPE = 'WDM'
  33.         THEN WDM_NE_BASEKEY(ODU_OBJECTS.NEA, 'BASEKEY_E_PLUS')
  34.         ELSE FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA,'NODES', 'BASEKEY_E_PLUS')
  35.         END ||
  36.         CASE WHEN  ODU_OBJECTS.NEA_OLD  IS NOT NULL
  37.         THEN '/'  || ODU_OBJECTS.NEA_OLD
  38.         ELSE NULL
  39.         END
  40.         AS NEA_BK_EPLUS_OLD,
  41.         --------------------------------------------------------------------------------------------------
  42.         CASE
  43.         WHEN ODU_OBJECTS.NEA_TYPE = 'WDM'
  44.         THEN NVL( ODU_OBJECTS.NEA_HOSTNAME, WDM_NE_BASEKEY(ODU_OBJECTS.NEA, 'NAME') )
  45.         ELSE NVL( ODU_OBJECTS.NEA_HOSTNAME, FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA,'NODES', 'NAME') )
  46.         END AS NEA_HOSTNAME,
  47.         -----------------------------------------------------------------------------------------------
  48.         FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA, 'NODES', 'PHYSICAL_POSITION') NEA_POS,
  49.         -----------------------------------------------------------------------------------------------
  50.         CASE
  51.         WHEN ODU_OBJECTS.NEA_TYPE = 'WDM'
  52.         THEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEA, 'NODES', 'LOGICAL_SHELF_NUMBER')
  53.         ELSE NULL
  54.         END NEA_LOG,
  55.         -----------------------------------------------------------------------------------------------
  56.         COALESCE(
  57.         FU_GET_OBJID_VALUE(ODU_OBJECTS.CARDA, 'NODES','SLOT_NO'), --SLOT_NO
  58.         FU_GET_OBJID_VALUE(ODU_OBJECTS.CARDA, 'NODES','SLOT_POSITION'), -- IF WDM
  59.         NULL
  60.         ) NEA_SLOT,
  61.         -----------------------------------------------------------------------------------------------
  62.         FU_GET_OBJID_VALUE(ODU_OBJECTS.PORTA, 'NODES', 'PORT_NUMBER') NEA_PORT,
  63.         -----------------------------------------------------------------------------------------------
  64.         COALESCE( ODU_OBJECTS.CARD_A_PC, ODU_OBJECTS.CARD_A_CT, NULL) NEA_CARDTYPE,
  65.         -----------------------------------------------------------------------------------------------
  66.         CASE
  67.         WHEN ODU_OBJECTS.NEB_TYPE = 'WDM'
  68.         THEN WDM_NE_BASEKEY(ODU_OBJECTS.NEB, 'BASEKEY')
  69.         ELSE FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB,'NODES', 'BASEKEY')
  70.         END AS NEB_BK,
  71.         -----------------------------------------------------------------------------------------------
  72.         CASE
  73.         WHEN ODU_OBJECTS.NEB_TYPE = 'WDM'
  74.         THEN WDM_NE_BASEKEY(ODU_OBJECTS.NEB, 'BASEKEY_E_PLUS')
  75.         ELSE FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB,'NODES', 'BASEKEY_E_PLUS')
  76.         END ||
  77.         CASE WHEN  ODU_OBJECTS.NEB_OLD  IS NOT NULL
  78.         THEN '/'  || ODU_OBJECTS.NEB_OLD
  79.         ELSE NULL
  80.         END
  81.         AS NEB_BK_EPLUS_OLD,
  82.         -----------------------------------------------------------------------------------------------
  83.         CASE
  84.         WHEN ODU_OBJECTS.NEB_TYPE = 'WDM'
  85.         THEN NVL( ODU_OBJECTS.NEB_HOSTNAME, WDM_NE_BASEKEY(ODU_OBJECTS.NEB, 'NAME'))
  86.         ELSE NVL( ODU_OBJECTS.NEB_HOSTNAME, FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB,'NODES', 'NAME'))
  87.         END AS NEB_HOSTNAME,
  88.         -----------------------------------------------------------------------------------------------
  89.         FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB,'NODES', 'PHYSICAL_POSITION') NEB_POS,
  90.         -----------------------------------------------------------------------------------------------
  91.         CASE
  92.         WHEN ODU_OBJECTS.NEB_TYPE = 'WDM'
  93.         THEN FU_GET_OBJID_VALUE(ODU_OBJECTS.NEB, 'NODES', 'LOGICAL_SHELF_NUMBER')
  94.         ELSE NULL
  95.         END NEB_LOG,
  96.         -----------------------------------------------------------------------------------------------
  97.         COALESCE(
  98.         FU_GET_OBJID_VALUE(ODU_OBJECTS.CARDB, 'NODES', 'SLOT_NO'), --SLOT_NO
  99.         FU_GET_OBJID_VALUE(ODU_OBJECTS.CARDB, 'NODES', 'SLOT_POSITION') -- IF WDM
  100.         ,NULL) NEB_SLOT,
  101.         -----------------------------------------------------------------------------------------------
  102.         FU_GET_OBJID_VALUE(ODU_OBJECTS.PORTB, 'NODES', 'PORT_NUMBER') NEB_PORT,
  103.         -----------------------------------------------------------------------------------------------
  104.         COALESCE( ODU_OBJECTS.CARD_B_PC, ODU_OBJECTS.CARD_B_CT, NULL) NEB_CARDTYPE,
  105.         -----------------------------------------------------------------------------------------------
  106.         ODU_OBJECTS.DEPLOYMENT_STATUS DEPLOYMENT_STATUS,
  107.         ODU_OBJECTS.CREATED_DATE CREATED_DATE,
  108.         ODU_OBJECTS.CHANGED_DATE CHANGED_DATE,
  109.         ODU_OBJECTS.IN_USE IN_USE,
  110.         ODU_OBJECTS.RESERVATION_STATUS RESERVATION_STATUS,
  111.         ODU_OBJECTS.REMARKS_FOR_RESERVATION REMARKS_FOR_RESERVATION,
  112.         NULL IMMELEER
  113.         -----------------------------------------------------------------------------------------------
  114.         FROM
  115.         (
  116.         SELECT
  117.         ODU.OBJECT_ID,
  118.         ODU.EPLUS_BASEKEY LINK_ID_E_PLUS,
  119.         ODU.BASEKEY LINK_ID,
  120.         ODU.CAPACITY,
  121.         ODU.NE_A_NE_B,
  122.         RPA_P.GETTYPENAME(ODU.OBJECT_ID)  TAGTYPE,
  123.         RPA_S.GETTYPENAME(S.SYSTEM_ID)  TAGTYPE_REAL,
  124.         NULL TAG1,
  125.         NULL TAG2,
  126.         NULL TAG3,
  127.         NULL TAG4,
  128.          -----------------------------------------------------------------------------------
  129.         F.A_NODE PORTA, -- OBJECT_ID PORTA OF NEXT LAYER
  130.         F.Z_NODE PORTB, -- OBJECT_ID PORTB OF NEXT LAYER
  131.         ND_A.NE NEA,   -- OBJECT_ID NEA OF NEXT LAYER (WDM,ROUTER,CC, ETC...)
  132.         ND_B.NE NEB,   -- OBJECT_ID NEB OF NEXT LAYER (WDM,ROUTER,CC, ETC...)
  133.         ND_A.PARENT_ID CARDA, -- IF IT IS A PORT, THEN THE PARENT MUST BE A CARD
  134.         ND_B.PARENT_ID CARDB,
  135.         -----------------------------------------------------------------------------------
  136.         ODU.REMARKS_FOR_RESERVATION,
  137.         ODU.CREATED_DATE,
  138.         ODU.CHANGED_DATE,
  139.         ST4.DESCRIPTION IN_USE,
  140.         ST2.DESCRIPTION DEPLOYMENT_STATUS,
  141.         ST1.DESCRIPTION RESERVATION_STATUS,
  142.         -----------------------------------------------------------------------------------
  143.         RPA_N.GETTYPENAME(ND_A.NE) NEA_TYPE ,
  144.         RPA_N.GETTYPENAME(ND_B.NE) NEB_TYPE ,
  145.         FU_GET_OBJID_VALUE(ND_A.NE,'NODES', 'OLD_SHELF_BASEKEY') NEA_OLD,
  146.         FU_GET_OBJID_VALUE(ND_B.NE,'NODES', 'OLD_SHELF_BASEKEY') NEB_OLD,
  147.         FU_GET_OBJID_VALUE(ND_A.NE,'NODES', 'HOSTNAME') NEA_HOSTNAME,
  148.         FU_GET_OBJID_VALUE(ND_B.NE,'NODES', 'HOSTNAME') NEB_HOSTNAME,
  149.         FU_GET_OBJID_VALUE(ND_A.PARENT_ID, 'NODES', 'PRODUCT_CODE') CARD_A_PC,
  150.         FU_GET_OBJID_VALUE(ND_A.PARENT_ID, 'NODES', 'CARD_TYPE') CARD_A_CT,
  151.         FU_GET_OBJID_VALUE(ND_B.PARENT_ID, 'NODES', 'PRODUCT_CODE') CARD_B_PC,
  152.         FU_GET_OBJID_VALUE(ND_B.PARENT_ID, 'NODES', 'CARD_TYPE') CARD_B_CT,
  153.         -----------------------------------------------------------------------------------
  154.         NULL IMMELEER
  155.         FROM
  156.         ODU_REC ODU
  157.         LEFT JOIN SYSTEM_TO_FPATH S  ON ODU.OBJECT_ID = S.FPATH_ID
  158.         ,FPATHS F
  159.         LEFT JOIN NODE_DETAILS ND_A ON ND_A.OBJECT_ID = F.A_NODE
  160.         LEFT JOIN NODE_DETAILS ND_B ON ND_B.OBJECT_ID = F.Z_NODE
  161. --        LEFT JOIN SYSTEMS SYS1 ON FF.PATH_ID = SYS1.PATH_ID
  162.         LEFT JOIN STATUS ST1 ON ST1.STATUS_ID = F.STATUS_1 -- RESERVATION
  163.         LEFT JOIN STATUS ST2 ON ST2.STATUS_ID = F.STATUS_2 -- DEPLOYMENT_STATUS
  164.         LEFT JOIN STATUS ST4 ON ST4.STATUS_ID = F.STATUS_4 -- IN_USE
  165.         WHERE
  166.         ODU.OBJECT_ID = F.OBJECT_ID
  167.          AND ODU.BASEKEY IN ( 'OD 0000005828','OD 0000005831','OD 0000005832','OD 0000005833','OD 0000005845','OD 0000005846')
  168.         -- AND FF.PATH_ID IS NOT NULL
  169.         ORDER BY ODU.BASEKEY
  170.         ) ODU_OBJECTS
  171. --        LEFT JOIN SYSTEM_TO_FPATH STF  ON ODU_OBJECTS.PATH_ID = STF.FPATH_ID
  172.         inner join node_details ndcheck ON odu_objects.PORTA = ndcheck.object_id
  173.         inner join node_details ndcheck2 ON ndcheck2.object_id = ndcheck.region
  174.         WHERE ndcheck2.parent_id = 250343 -- PSTARTPATHID
  175.         ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!