INSERT INTO DB_VIEW_CONF (VIEW_NAME, ATTRIBUTE_NAME, VALUE)
VALUES ('IP_CORE_INFOVISTA_VIEW', 'VERSION_NAME', 'Actual Network');

CREATE OR REPLACE FORCE EDITIONABLE VIEW IP_CORE_INFOVISTA_VIEW
(
    TAG,
    NODE_NAME,
    NODE_ID,
    SNMP_COMMUNITY,
    SITE_ID,
    SITE_NAME,
    MODEL,
    CITY,
    WILAYAT,
    GOVERNORATE,
    LATITUDE,
    LONGITUDE
) AS
WITH SELECTED_VERSION AS (
    SELECT VERSIONID VERSION_ID
    FROM VERSION
    WHERE NAME = (
        SELECT VALUE
        FROM DB_VIEW_CONF
        WHERE VIEW_NAME = 'NE_DETAILS'
          AND ATTRIBUTE_NAME = 'VERSION_NAME'
    )
),

NETWORK_ELEMENTS AS (
    SELECT 
           R.ROUTERID         AS NE_ID,
           R.NAME             AS NE_NAME,
           R.OBJECTID         AS NODE_ID,
           R.SITEID           AS SITE_ID,
           RT.NAME            AS MODEL_NAME
    FROM ROUTER R
         JOIN ROUTERTYPE RT   ON R.ROUTERTYPEID = RT.ROUTERTYPEID
         JOIN SELECTED_VERSION V ON R.VERSIONID = V.VERSION_ID
    WHERE RT.NAME LIKE 'NE40E%'
),

ADDITIONAL_INFO AS (
    SELECT 
           AI.ELEMENTID AS EID,
           MAX(CASE WHEN AI.ATTRIBUTE = 'SNMP_COMMUNITY' THEN AI.VALUE END)              AS SNMP_COMMUNITY,
           MAX(CASE WHEN AI.ATTRIBUTE = 'User name' THEN AI.VALUE END)                   AS UserName,
           MAX(CASE WHEN AI.ATTRIBUTE = 'Context Name' THEN AI.VALUE END)                AS ContextName,
           MAX(CASE WHEN AI.ATTRIBUTE = 'Authentication Protocol' THEN AI.VALUE END)     AS AuthProtocol,
           MAX(CASE WHEN AI.ATTRIBUTE = 'Encryption protocol' THEN AI.VALUE END)         AS PrivProtocol,
           MAX(CASE WHEN AI.ATTRIBUTE = 'Authentication Password' THEN AI.VALUE END)     AS AuthPassword,
           MAX(CASE WHEN AI.ATTRIBUTE = 'Encryption password' THEN AI.VALUE END)         AS PrivPassword,
           MAX(CASE WHEN AI.ATTRIBUTE = 'Alias Name' THEN AI.VALUE END)                  AS Alias_Name
    FROM ELEMENTADDITIONALINFO AI
         JOIN SELECTED_VERSION V ON AI.VERSIONID = V.VERSION_ID
    WHERE AI.ATTRIBUTE IN (
        'SNMP_COMMUNITY','User name','Context Name',
        'Authentication Protocol','Encryption protocol',
        'Authentication Password','Encryption password','Alias Name'
    )
    GROUP BY AI.ELEMENTID
),

SITE_INFO AS (
    SELECT SITE.SITEID,
           SITE.NAME          AS SITE_NAME,
           SITE.LATITUDE,
           SITE.LONGITUDE,
           Z.NAME             AS CITY,
           A1.NAME            AS WILAYAT,
           A2.NAME            AS GOVERNORATE
    FROM SITE
         LEFT JOIN ZONE Z     ON SITE.ZONE_ID = Z.ZONE_ID
         LEFT JOIN AREA A1    ON Z.AREA_ID = A1.AREA_ID
         LEFT JOIN AREA A2    ON A1.PARENT_AREA_ID = A2.AREA_ID
         JOIN SELECTED_VERSION V ON SITE.VERSIONID = V.VERSION_ID
)

SELECT
       'IP CORE' AS TAG,
       NVL(AI.Alias_Name, NE.NE_NAME) AS NODE_NAME,
       NE.NODE_ID,
       CASE
           WHEN (AI.UserName IS NOT NULL AND UPPER(AI.UserName) <> 'ALL'
             AND AI.ContextName IS NOT NULL AND UPPER(AI.ContextName) <> 'ALL'
             AND AI.AuthProtocol IS NOT NULL AND UPPER(AI.AuthProtocol) <> 'ALL'
             AND AI.AuthPassword IS NOT NULL AND UPPER(AI.AuthPassword) <> 'ALL'
             AND AI.PrivProtocol IS NOT NULL AND UPPER(AI.PrivProtocol) <> 'ALL'
             AND AI.PrivPassword IS NOT NULL AND UPPER(AI.PrivPassword) <> 'ALL')
           THEN AI.UserName || ',' || AI.ContextName || ',' ||
                AI.AuthProtocol || '_' || AI.PrivProtocol || ',' ||
                AI.AuthPassword || ',' || AI.PrivPassword
           ELSE AI.SNMP_COMMUNITY
       END AS SNMP_COMMUNITY,
       SITE.SITEID,
       SITE.SITE_NAME,
       NE.MODEL_NAME AS MODEL,
       SITE.CITY,
       SITE.WILAYAT,
       SITE.GOVERNORATE,
       SITE.LATITUDE,
       SITE.LONGITUDE
FROM NETWORK_ELEMENTS NE
     LEFT JOIN ADDITIONAL_INFO AI ON AI.EID = NE.NE_ID
     LEFT JOIN SITE_INFO SITE     ON SITE.SITEID = NE.SITE_ID;
     
     

select * from IP_CORE_INFOVISTA;