API tools faq
paste
Advertisement
fermiiii

odu all with tags

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