CREATE OR REPLACE FORCE EDITIONABLE VIEW "HPCOPY2025"."V_5G_CELL_REPORT" ("CIRCLE", "Cell Name", "Object ID", "CID", "GNodeB ID", "GNodeB Name", "Name", "Cell Technology", "PCID", "Cell Duplexing Mode", "Downlink Narfcn", "Uplink Narfcn", "Mimo Configuration", "Manufacturer", "Downlink Bandwidths", "Uplink Bandwidths", "TAC ID", "CGI", "Status", "MCC", "MNC", "Site ID", "Longitude", "Latitude", "Site Source", "Parent ID", "SRAN", "NR GSCN", "RSI", "AZIMUTH", "M TILT", "ANTENNA HEIGHT(m)", "AMT OPERATIONAL STATE", "AMT MODEL NO", "AMT SERIAL NO", "AMT MAKE", "RET E TILT", "Insert Date", "RET Operational State", "Antenna Model", "Beamset/Beam Pattern", "Update Date", "Cell Radius", "SSB Power", "SSB power Boost", "Sub frame Configuration", "Special sub frame Config", "RU Model", "RU serial number", "GPS Latitude", "GPS Longitude", "EMS IP", "EMS NAME", "Cell Type", "Maximum Transmission power", "Set ID", "SSB Frequency") AS
  with src as
  (select preferencesvalue from nepgeneralpreferences where preferencestype='57'),
  cb AS (   SELECT cellid, LISTAGG(channelBandwidth,'|') within group ( order by channelBandwidth) DOWNLINKS
  from
( select cell.cellid cellid ,RADIOCHANNELBANDWIDTH.CHANNELBANDWIDTHS channelBandwidth,row_number() over (partition by cell.cellid,RADIOCHANNELBANDWIDTH.CHANNELBANDWIDTHS order by cell.cellid) rn
   FROM NRCELLRADIOBANDWIDTH b LEFT JOIN CELL ON b.NRCELLID = CELL.CELLID LEFT JOIN RADIOCHANNELBANDWIDTH on RADIOCHANNELBANDWIDTH.BANDWIDTHID = b.bandwidthid
   WHERE b.BANDTYPE = 'DOWNLINK' and RADIOCHANNELBANDWIDTH.CHANNELBANDWIDTHS is not null) where rn=1 group by cellid),
  UCB AS (SELECT NRCELL.CELLID, LISTAGG( RADIOCHANNELBANDWIDTH.CHANNELBANDWIDTHS,'|') within group ( order by RADIOCHANNELBANDWIDTH.CHANNELBANDWIDTHS) DOWNLINKS
   FROM NRCELLRADIOBANDWIDTH b LEFT JOIN NRCELL ON b.NRCELLID = NRCELL.CELLID LEFT JOIN RADIOCHANNELBANDWIDTH on RADIOCHANNELBANDWIDTH.BANDWIDTHID = b.bandwidthid  WHERE b.BANDTYPE = 'UPLINK' group by NRCELL.CELLID)
  select 'HP' AS Circle,cell.cell_display_name "Cell Name",
  CELL.OBJECTID "Object ID",
  CELL.ELEMENTNODEID CID,
  GNODEB.OBJECTID "GNodeB ID",
  GNODEB.DISPLAYNAME "GNodeB Name",
   GNODEB.NAME "Name",
  'NR' "Cell Technology",
  NCELL.PCID,
  NCELL.CELLDUPLEXINGMODE "Cell Duplexing Mode",
  NCELL.DlNARFCN "Downlink Narfcn",
  NCELL.UlNARFCN "Uplink Narfcn",
  NCELL.MIMOCONFIGURATION "Mimo Configuration",
  NCELL.MANUFACTURER "Manufacturer",
  CB.DOWNLINKS "Downlink Bandwidths",
  UCB.DOWNLINKS "Uplink Bandwidths",
  TAC.ObjectID "TAC ID",
  (Version.MCC||'-'||Version.MNC||'-'||CELL.ELEMENTNODEID) CGI,
  status.NAME "Status",
  Version.MCC,
  Version.MNC,
  SITE.OBJECTID "Site ID",
  SITE.LONGITUDE "Longitude",
  SITE.LATITUDE "Latitude",
  manager.managername "Site Source",
  GNODEB.ELEMENTNODEID "Parent ID",
  RanBsNode.DISPLAYNAME  SRAN,
  NCELL.nrgscn "NR GSCN",
  NCELL.RSI "RSI",
  NCELL.AZIMUTH "AZIMUTH",
  ncell.mtilt "M TILT",
  ncell.antennaheight "ANTENNA HEIGHT(m)",
  ncell.amtoperationalstate "AMT OPERATIONAL STATE",
  ncell.amtmodelnumber "AMT MODEL NO",
  ncell.amtserialnumber "AMT SERIAL NO",
  ncell.amtmake "AMT MAKE",
  ncell.retetilt "RET E TILT",
  cell.insertdate "Insert Date",
  ncell.retoperationalstate "RET Operational State",
  ncell.antennamodel "Antenna Model",
  ncell.beamset "Beamset/Beam Pattern",
  cell.changedate "Update Date",
  ncell.cellradius "Cell Radius",
  ncell.ssbpower "SSB Power",
  ncell.ssbpowerboost "SSB power Boost",
  ncell.subframeconfiguration "Sub frame Configuration",
  ncell.specialsubframeconfiguration "Special sub frame Config",
  ncell.rumodel "RU Model",
  ncell.ruserialnumber "RU serial number",
  ncell.GPSLATITUDE "GPS Latitude",
  ncell.GPSLONGITUDE "GPS Longitude",
  AI.EMS_IP "EMS IP",
  AI.EMS_NAME "EMS NAME",
  ncell.celltype "Cell Type",
  ncell.maxTransmissionPower "Maximum Transmission power",
  ncell.setid "Set ID",
  ncell.SSBFREQUENCY "SSB Frequency"
from src,
(SELECT VERSIONID VERSION_ID, MNC,MCC FROM VERSION WHERE NAME =
    (SELECT VALUE FROM DB_VIEW_CONF WHERE VIEW_NAME = 'V_4G_CELL_REPORT' AND ATTRIBUTE_NAME = 'VERSION_NAME')) version,
NRCELL NCELL
LEFT JOIN CELL ON CELL.CELLID = NCELL.CELLID
LEFT JOIN GNODEB ON GNODEB.GNODEBID = NCELL.GNODEBID
LEFT JOIN RANBSNODE ON RANBSNODE.RanBsID = GNODEB.role_Container
LEFT JOIN SITE ON SITE.SITEID = GNODEB.SITEID
LEFT JOIN NepStatus status on status.id = cell.NET_STATUS
LEFT JOIN SITEMANAGER manager on site.managedby=manager.managerid
LEFT JOIN (SELECT *
  FROM (SELECT ELEMENTID EID,
    ATTRIBUTE,
    VALUE
  FROM ELEMENTADDITIONALINFO AI,
      (SELECT VERSIONID VERSION_ID, MNC,MCC
  FROM VERSION
  WHERE NAME =
  (SELECT VALUE
   FROM DB_VIEW_CONF
    WHERE VIEW_NAME    = 'V_4G_CELL_REPORT'
    AND ATTRIBUTE_NAME = 'VERSION_NAME'
   )
  ) v
  WHERE VERSIONID = v.VERSION_ID
  AND ELEMENTTYPE = 'NRCell' and ATTRIBUTE in ('EMS IP','EMS NAME')
  ) PIVOT (MAX(VALUE) FOR ATTRIBUTE IN ('EMS IP' EMS_IP,'EMS NAME' EMS_NAME))) AI ON CELL.CELLID = AI.EID
LEFT JOIN TRACKINGAREA TAC on TAC.TACID = NCELL.TACID
LEFT JOIN CB on (CB.cellid=cell.cellid)
LEFT JOIN UCB on (UCB.cellid=cell.cellid)
where version.version_id = GNODEB.versionid;
 