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



CREATE OR REPLACE FORCE EDITIONABLE VIEW GPON_INFOVISTA_VIEW
(
    KEYWORD,
    TYPE,
    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 = 'GPON_INFOVISTA_VIEW'
          AND ATTRIBUTE_NAME = 'VERSION_NAME'
    )
),

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.OBJECTID      AS SITE_OBJECTID,
           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
),

ALL_ELEMENTS AS (
    /* OLT/MSAN */
    SELECT mta.objectid AS NODE_ID,
           mta.name     AS NODE_NAME,
           mta.siteid   AS SITE_ID,
           mta_type.name AS MODEL,
           mta_type.category AS TAG
    FROM multitechnologyaccess mta
         JOIN multitechnologyaccesstype mta_type
           ON mta.multitechnologyaccesstypeid = mta_type.multitechnologyaccesstypeid
         LEFT JOIN manufacturer manuf
           ON mta_type.manufacturerid = manuf.manufacturerid
         JOIN SELECTED_VERSION V
           ON mta.versionid = V.VERSION_ID
    WHERE mta_type.category IN ('OLT','MSAN')

    UNION ALL

    /* MDU */
    SELECT mdu.objectid AS NODE_ID,
           mdu.name     AS NODE_NAME,
           mdu.siteid   AS SITE_ID,
           mdutype.name AS MODEL,
           'MDU'        AS TAG
    FROM mdu
         JOIN mdutype ON mdu.mdutypeid = mdutype.mdutypeid
         LEFT JOIN manufacturer manuf ON mdutype.manufacturerid = manuf.manufacturerid
         JOIN SELECTED_VERSION V ON mdu.versionid = V.VERSION_ID

    UNION ALL

    /* ONT */
    SELECT ont.objectid AS NODE_ID,
           ont.name     AS NODE_NAME,
           ont.siteid   AS SITE_ID,
           onttype.name AS MODEL,
           'ONT'        AS TAG
    FROM ont
         JOIN onttype ON ont.ont_type_id = onttype.onttypeid
         LEFT JOIN manufacturer manuf ON onttype.manufacturerid = manuf.manufacturerid
         JOIN SELECTED_VERSION V ON ont.versionid = V.VERSION_ID
)

SELECT
       'GPON' AS KEYWORD,
       AE.TAG AS TYPE,
       NVL(AI.Alias_Name, TRIM(AE.NODE_NAME)) AS NODE_NAME,
       AE.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.SITE_OBJECTID AS SITE_ID,
       SITE.SITE_NAME,
       AE.MODEL,
       SITE.CITY,
       SITE.WILAYAT,
       SITE.GOVERNORATE,
       SITE.LATITUDE,
       SITE.LONGITUDE
FROM ALL_ELEMENTS AE
     LEFT JOIN ADDITIONAL_INFO AI ON AI.EID = AE.NODE_ID
     LEFT JOIN SITE_INFO SITE     ON SITE.SITEID = AE.SITE_ID
WHERE TRIM(AE.NODE_ID) <> TRIM(AE.NODE_NAME);

select * from GPON_INFOVISTA_VIEW ;