-- PUT THE REQUIRED VERSION NAME 
INSERT INTO DB_VIEW_CONF (VIEW_NAME, ATTRIBUTE_NAME, VALUE)
VALUES ('INVENTORYREPORTVIEW', 'VERSION_NAME', 'PUT REQUIRED VERSION NAME HERE');
 
 
UPDATE DB_VIEW_CONF
SET VALUE = 'PUT REQUIRED VERSION NAME HERE'
WHERE VIEW_NAME = 'NE_VIEW';
  


CREATE OR REPLACE FORCE EDITIONABLE VIEW "ZAINKSA_3X"."INVENTORYREPORTVIEW" ("ZONE", "SITE", "LONGITUDE", "LATITUDE", "SITE_STATUS", "NODE", "TECHNOLOGY_SUPPORTED","INSERTION_DATE", "NODE_TYPE", "MANUFACTURER", "ELEMENT", "MODEL", "PART_NUMBER", "INVENTORY_FLAG", "SERIAL_NUMBER", "DUMP_SERIAL_NUMBER", "DESCRIPTION", "MANUFACTURING_DATE", "ISSUE_NUMBER", "ACTUAL_SITE", "INSERT_DATE", "UPDATE_DATE"
) AS

WITH SELECTED_VERSION AS (
    SELECT versionid 
    FROM version 
    WHERE name = (SELECT value FROM DB_VIEW_CONF WHERE VIEW_NAME = 'INVENTORYREPORTVIEW' AND ATTRIBUTE_NAME = 'VERSION_NAME'
    )
)

SELECT 
    ZONE.NAME AS ZONE,
    SITE.NAME AS SITE,
    SITE.LONGITUDE AS LONGITUDE,
    SITE.LATITUDE AS LATITUDE,
    CASE 
        WHEN UPPER(NODE.NE_NAME) LIKE '%DIS%' THEN 'DIS' 
        ELSE 'ON-AIR'
    END AS SITE_STATUS,
    NODE.NE_NAME AS NODE,
    NODE.TECHNOLOGY_SUPPORTED AS TECHNOLOGY_SUPPORTED,
    serial_date.INSERTION_DATE AS INSERTION_DATE,
    NODE.NODE_TYPE AS NODE_TYPE,
    MANUFACTURER.NAME AS MANUFACTURER,
    'CABINET ' || CABINET.CABINETINDEX AS ELEMENT,
    CABINET.MODEL AS MODEL,
    CABINET.PARTNUMBER AS PART_NUMBER,
    CABINET.ININVENTORY AS INVENTORY_FLAG,
    CABINET.SERIALNUMBER AS SERIAL_NUMBER,
    CABINET.DUMPSERIALNUMBER AS DUMP_SERIAL_NUMBER,
    CABINET.DESCRIPTION AS DESCRIPTION,
    CABINET.MANUFACTURINGDATE AS MANUFACTURING_DATE,
    CABINET.ISSUENUMBER AS ISSUE_NUMBER,
    COALESCE(addInfo.VALUE, SITE.NAME) AS ACTUAL_SITE,
    CABINET.INSERTDATE AS INSERT_DATE,
    CABINET.CHANGEDATE AS UPDATE_DATE

FROM NODECABINET CABINET
INNER JOIN NODESHELF SHELF ON CABINET.CABINETID = SHELF.CABINETID
INNER JOIN NODESLOT SLOT ON SHELF.SHELFID = SLOT.SHELFID
LEFT JOIN CABINETTYPE CABINETTYPE ON CABINET.CABINETTYPE = CABINETTYPE.CABINETTYPEID
LEFT JOIN MANUFACTURER MANUFACTURER ON CABINETTYPE.MANUFACTURERID = MANUFACTURER.MANUFACTURERID
LEFT JOIN NE_VIEW NODE ON NODE.NE_ID = CABINET.NODEID
LEFT JOIN SITE SITE ON SITE.SITEID = NODE.SITE_ID
LEFT JOIN ZONE ZONE ON SITE.ZONE_ID = ZONE.ZONE_ID
LEFT JOIN elementadditionalinfo addInfo ON addInfo.ELEMENTID = NODE.NE_ID AND addInfo.ATTRIBUTE = 'Actual Site'
LEFT JOIN asset_serial_date_mv serial_date ON serial_date.SERIALNUMBER = CABINET.SERIALNUMBER
WHERE SLOT.versionid IN (SELECT versionid FROM SELECTED_VERSION)

UNION ALL

SELECT 
    ZONE.NAME AS ZONE,
    SITE.NAME AS SITE,
    SITE.LONGITUDE AS LONGITUDE,
    SITE.LATITUDE AS LATITUDE,
    CASE 
        WHEN UPPER(NODE.NE_NAME) LIKE '%DIS%' THEN 'DIS' 
        ELSE 'ON-AIR'
    END AS SITE_STATUS,
    NODE.NE_NAME AS NODE,
    NODE.TECHNOLOGY_SUPPORTED AS TECHNOLOGY_SUPPORTED,
    serial_date.INSERTION_DATE AS INSERTION_DATE,
    NODE.NODE_TYPE AS NODE_TYPE,
    MANUFACTURER.NAME AS MANUFACTURER,
    'CABINET ' || CABINET.CABINETINDEX || '/ SHELF ' || SHELF.SHELFINDEX AS ELEMENT,
    SHELF.MODEL AS MODEL,
    SHELF.PARTNUMBER AS PART_NUMBER,
    SHELF.ININVENTORY AS INVENTORY_FLAG,
    SHELF.SERIALNUMBER AS SERIAL_NUMBER,
    SHELF.DUMPSERIALNUMBER AS DUMP_SERIAL_NUMBER,
    SHELF.DESCRIPTION AS DESCRIPTION,
    SHELF.MANUFACTURINGDATE AS MANUFACTURING_DATE,
    SHELF.ISSUENUMBER AS ISSUE_NUMBER,
    COALESCE(addInfo.VALUE, SITE.NAME) AS ACTUAL_SITE,
    SHELF.INSERTDATE AS INSERT_DATE,
    SHELF.CHANGEDATE AS UPDATE_DATE

FROM NODECABINET CABINET
INNER JOIN NODESHELF SHELF ON CABINET.CABINETID = SHELF.CABINETID
INNER JOIN NODESLOT SLOT ON SHELF.SHELFID = SLOT.SHELFID
LEFT JOIN SHELFTYPE SHELFTYPE ON SHELF.SHELFTYPE = SHELFTYPE.SHELFTYPEID
LEFT JOIN MANUFACTURER MANUFACTURER ON SHELFTYPE.MANUFACTURERID = MANUFACTURER.MANUFACTURERID
LEFT JOIN NE_VIEW NODE ON NODE.NE_ID = CABINET.NODEID
LEFT JOIN SITE SITE ON SITE.SITEID = NODE.SITE_ID
LEFT JOIN ZONE ZONE ON SITE.ZONE_ID = ZONE.ZONE_ID
LEFT JOIN elementadditionalinfo addInfo ON addInfo.ELEMENTID = NODE.NE_ID AND addInfo.ATTRIBUTE = 'Actual Site'
LEFT JOIN asset_serial_date_mv serial_date ON serial_date.SERIALNUMBER = SHELF.SERIALNUMBER
WHERE SLOT.versionid IN (SELECT versionid FROM SELECTED_VERSION)

UNION ALL

SELECT 
    ZONE.NAME AS ZONE,
    SITE.NAME AS SITE,
    SITE.LONGITUDE AS LONGITUDE,
    SITE.LATITUDE AS LATITUDE,
    CASE 
        WHEN UPPER(NODE.NE_NAME) LIKE '%DIS%' THEN 'DIS' 
        ELSE 'ON-AIR'
    END AS SITE_STATUS,
    NODE.NE_NAME AS NODE,
    NODE.TECHNOLOGY_SUPPORTED AS TECHNOLOGY_SUPPORTED,
    serial_date.INSERTION_DATE AS INSERTION_DATE,
    NODE.NODE_TYPE AS NODE_TYPE,
    MANUFACTURER.NAME AS MANUFACTURER,
    'CABINET ' || CABINET.CABINETINDEX || '/ SHELF ' || SHELF.SHELFINDEX || '/ SLOT' || SLOT.SLOTINDEX || '/ BOARD ' || BOARD.BOARDINDEXONSLOT AS ELEMENT,
    BOARD.MODEL AS MODEL,
    BOARD.PARTNUMBER AS PART_NUMBER,
    BOARD.ININVENTORY AS INVENTORY_FLAG,
    BOARD.BOARDSERIALNUMBER AS SERIAL_NUMBER,
    BOARD.DUMPS_SERIAL_NUMBER AS DUMP_SERIAL_NUMBER,
    BOARD.DESCRIPTION AS DESCRIPTION,
    BOARD.MANUFACTURINGDATE AS MANUFACTURING_DATE,
    BOARD.ISSUENUMBER AS ISSUE_NUMBER,
    COALESCE(addInfo.VALUE, SITE.NAME) AS ACTUAL_SITE,
    BOARD.INSERTDATE AS INSERT_DATE,
    BOARD.CHANGEDATE AS UPDATE_DATE

FROM NODECABINET CABINET
INNER JOIN NODESHELF SHELF ON CABINET.CABINETID = SHELF.CABINETID
INNER JOIN NODESLOT SLOT ON SHELF.SHELFID = SLOT.SHELFID
INNER JOIN NODEBOARD BOARD ON BOARD.SLOTID = SLOT.SLOTID
LEFT JOIN BOARDTYPE BOARDTYPE ON BOARD.BOARDTYPECODE = BOARDTYPE.BOARDTYPEID
LEFT JOIN MANUFACTURER MANUFACTURER ON BOARDTYPE.MANUFACTURERID = MANUFACTURER.MANUFACTURERID
LEFT JOIN NE_VIEW NODE ON NODE.NE_ID = CABINET.NODEID
LEFT JOIN SITE SITE ON SITE.SITEID = NODE.SITE_ID
LEFT JOIN ZONE ZONE ON SITE.ZONE_ID = ZONE.ZONE_ID
LEFT JOIN elementadditionalinfo addInfo ON addInfo.ELEMENTID = NODE.NE_ID AND addInfo.ATTRIBUTE = 'Actual Site'
LEFT JOIN asset_serial_date_mv serial_date ON serial_date.SERIALNUMBER = BOARD.BOARDSERIALNUMBER
WHERE SLOT.versionid IN (SELECT versionid FROM SELECTED_VERSION)

UNION ALL

SELECT 
    ZONE.NAME AS ZONE,
    SITE.NAME AS SITE,
    SITE.LONGITUDE AS LONGITUDE,
    SITE.LATITUDE AS LATITUDE,
    CASE 
        WHEN UPPER(NODE.NE_NAME) LIKE '%DIS%' THEN 'DIS' 
        ELSE 'ON-AIR'
    END AS SITE_STATUS,
    NODE.NE_NAME AS NODE,
    NODE.TECHNOLOGY_SUPPORTED AS TECHNOLOGY_SUPPORTED,
    serial_date.INSERTION_DATE AS INSERTION_DATE,
    NODE.NODE_TYPE AS NODE_TYPE,
    MANUFACTURER.NAME AS MANUFACTURER,
    'CABINET ' || CABINET.CABINETINDEX || '/ SHELF ' || SHELF.SHELFINDEX || '/ SLOT' || SLOT.SLOTINDEX || '/ BOARD ' || BOARD.BOARDINDEXONSLOT || '/ INTERFACE ' || nodeinterface.INTERFACEINDEX AS ELEMENT,
    nodeinterface.SFPDESCRIPTION AS MODEL,
    nodeinterface.PARTNUMBER AS PART_NUMBER,
    BOARD.ININVENTORY AS INVENTORY_FLAG,
    nodeinterface.SERIAL AS SERIAL_NUMBER,
    nodeinterface.DUMPSERIALNUMBER AS DUMP_SERIAL_NUMBER,
    nodeinterface.DESCRIPTION AS DESCRIPTION,
    NULL AS MANUFACTURING_DATE,
    NULL AS ISSUE_NUMBER,
    COALESCE(addInfo.VALUE, SITE.NAME) AS ACTUAL_SITE,
    nodeinterface.INSERTDATE AS INSERT_DATE,
    nodeinterface.CHANGEDATE AS UPDATE_DATE

FROM NODECABINET CABINET
INNER JOIN NODESHELF SHELF ON CABINET.CABINETID = SHELF.CABINETID
INNER JOIN NODESLOT SLOT ON SHELF.SHELFID = SLOT.SHELFID
INNER JOIN NODEBOARD BOARD ON BOARD.SLOTID = SLOT.SLOTID
INNER JOIN nodeinterface nodeinterface ON BOARD.nodeboardid = nodeinterface.nodeboardid
LEFT JOIN BOARDTYPE BOARDTYPE ON BOARD.BOARDTYPECODE = BOARDTYPE.BOARDTYPEID
LEFT JOIN MANUFACTURER MANUFACTURER ON BOARDTYPE.MANUFACTURERID = MANUFACTURER.MANUFACTURERID
LEFT JOIN NE_VIEW NODE ON NODE.NE_ID = CABINET.NODEID
LEFT JOIN SITE SITE ON SITE.SITEID = NODE.SITE_ID
LEFT JOIN ZONE ZONE ON SITE.ZONE_ID = ZONE.ZONE_ID
LEFT JOIN elementadditionalinfo addInfo ON addInfo.ELEMENTID = NODE.NE_ID AND addInfo.ATTRIBUTE = 'Actual Site'
LEFT JOIN asset_serial_date_mv serial_date ON serial_date.SERIALNUMBER = BOARD.BOARDSERIALNUMBER
WHERE COALESCE(nodeinterface.SERIAL, nodeinterface.SFPDESCRIPTION) IS NOT NULL
  AND SLOT.versionid IN (SELECT versionid FROM SELECTED_VERSION)

UNION ALL

SELECT 
    ZONE.NAME AS ZONE,
    SITE.NAME AS SITE,
    SITE.LONGITUDE AS LONGITUDE,
    SITE.LATITUDE AS LATITUDE,
    CASE 
        WHEN UPPER(radioantenna.RADIOANTENNANAME) LIKE '%DIS%' THEN 'DIS' 
        ELSE 'ON-AIR'
    END AS SITE_STATUS,
    radioantenna.RADIOANTENNANAME AS NODE,
    NULL AS TECHNOLOGY_SUPPORTED,
    serial_date.INSERTION_DATE AS INSERTION_DATE,
    'Radio Antenna' AS NODE_TYPE,
    MANUFACTURER.NAME AS MANUFACTURER,
    radioantenna.RADIOANTENNANAME AS ELEMENT,
    radioantennatype.MODEL AS MODEL,
    NULL AS PART_NUMBER,
    NULL AS INVENTORY_FLAG,
    serialNumInfo.VALUE AS SERIAL_NUMBER,
    serialNumInfo.VALUE AS DUMP_SERIAL_NUMBER,
    NULL AS DESCRIPTION,
    NULL AS MANUFACTURING_DATE,
    NULL AS ISSUE_NUMBER,
    COALESCE(actualSiteInfo.VALUE, SITE.NAME) AS ACTUAL_SITE,
    radioantenna.INSERTDATE AS INSERT_DATE,
    radioantenna.CHANGEDATE AS UPDATE_DATE

FROM radioantenna radioantenna
LEFT JOIN radioantennatype radioantennatype ON radioantenna.RADIOANTENNATYPEID = radioantennatype.RADIOANTENNATYPEID
LEFT JOIN MANUFACTURER MANUFACTURER ON radioantennatype.MANUFACTURERID = MANUFACTURER.MANUFACTURERID
LEFT JOIN SITE SITE ON SITE.SITEID = radioantenna.SITEID
LEFT JOIN ZONE ZONE ON SITE.ZONE_ID = ZONE.ZONE_ID
LEFT JOIN elementadditionalinfo serialNumInfo ON serialNumInfo.ELEMENTID = radioantenna.RADIOANTENNAID AND serialNumInfo.ATTRIBUTE = 'SerialNumberEx'
LEFT JOIN elementadditionalinfo actualSiteInfo ON actualSiteInfo.ELEMENTID = radioantenna.RADIOANTENNAID AND actualSiteInfo.ATTRIBUTE = 'Actual Site'
LEFT JOIN asset_serial_date_mv serial_date ON serial_date.SERIALNUMBER = serialNumInfo.VALUE
WHERE radioantenna.versionid IN (SELECT versionid FROM SELECTED_VERSION);