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

CREATE OR REPLACE FORCE EDITIONABLE VIEW SITE_DB_REPORT_VIEW (
    SITE_ID,
    SITE_NAME,
    "Region(Site)",
    FM_OFFICE,
    LONGITUDE,
    LATITUDE,
    SITE_STAGE_LABEL,
    SITE_TYPE_LABEL,
    SITE_PRIORITY_LABEL,
    ACCESS_TYPE_LABEL,
    AREA,
    POWER_TYPE,
    IS_HUB,
    SITE_STATE,
    CLUSTER_ID,
    COLLOCATION_ID
) AS
WITH SELECTED_VERSION AS (
    SELECT VERSIONID VERSION_ID
    FROM VERSION
    WHERE NAME = (
        SELECT VALUE
        FROM DB_VIEW_CONF
        WHERE VIEW_NAME = 'SITE_DB_REPORT_VIEW'
          AND ATTRIBUTE_NAME = 'VERSION_NAME'
    )
),
SITE_BASE AS (
    SELECT
        S.SITEID,
        S.OBJECTID AS OBJECT_ID,
        S.NAME AS SITE_NAME,
        S.LATITUDE,
        S.LONGITUDE,
        A1.NAME AS AREA,
        A2.NAME AS REGION_SITE,
        CASE
            WHEN S.ISHUBSITE = 1 THEN 'TRUE'
            ELSE 'FALSE'
        END AS IS_HUB
    FROM SITE S
         LEFT JOIN ZONE Z   ON S.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 S.VERSIONID = V.VERSION_ID
),
SITE_ADDITIONAL AS (
    SELECT
        AI.ELEMENTID AS SITE_ID,
        MAX(CASE WHEN AI.ATTRIBUTE = 'FM OFFICE' THEN AI.VALUE END) AS FM_OFFICE,
        MAX(CASE WHEN AI.ATTRIBUTE = 'Site Stage Label' THEN AI.VALUE END) AS SITE_STAGE_LABEL,
        MAX(CASE WHEN AI.ATTRIBUTE = 'Indoor/Outdoor' THEN AI.VALUE END) AS SITE_TYPE_LABEL,
        MAX(CASE WHEN AI.ATTRIBUTE = 'Site Priority Label' THEN AI.VALUE END) AS SITE_PRIORITY_LABEL,
        MAX(CASE WHEN AI.ATTRIBUTE = 'Access To location ?' THEN AI.VALUE END) AS ACCESS_TYPE_LABEL,
        MAX(CASE WHEN AI.ATTRIBUTE = 'Power Type' THEN AI.VALUE END) AS POWER_TYPE,
        MAX(CASE WHEN AI.ATTRIBUTE = 'Site State' THEN AI.VALUE END) AS SITE_STATE,
        MAX(CASE WHEN AI.ATTRIBUTE = 'Cluster ID' THEN AI.VALUE END) AS CLUSTER_ID,
        MAX(CASE WHEN AI.ATTRIBUTE = 'Collocation Id' THEN AI.VALUE END) AS COLLOCATION_ID
    FROM ELEMENTADDITIONALINFO AI
         JOIN SELECTED_VERSION V ON AI.VERSIONID = V.VERSION_ID
    WHERE AI.ATTRIBUTE IN (
        'FM OFFICE','Site Stage Label','Indoor/Outdoor',
        'Site Priority Label','Access To location ?',
        'Power Type','Site State','Cluster ID','Collocation Id'
    )
    GROUP BY AI.ELEMENTID
)
SELECT
    SB.OBJECT_ID AS SITE_ID,
    SB.SITE_NAME,
    SB.REGION_SITE AS "Region(Site)",
    SA.FM_OFFICE,
    SB.LONGITUDE,
    SB.LATITUDE,
    SA.SITE_STAGE_LABEL,
    SA.SITE_TYPE_LABEL,
    SA.SITE_PRIORITY_LABEL,
    SA.ACCESS_TYPE_LABEL,
    SB.AREA,
    SA.POWER_TYPE,
    SB.IS_HUB,
    SA.SITE_STATE,
    SA.CLUSTER_ID,
    SA.COLLOCATION_ID
FROM SITE_BASE SB
     LEFT JOIN SITE_ADDITIONAL SA ON SA.SITE_ID = SB.SITEID;

select * from site_db_report_view;