SELECT
  Region.name,
  Abis.NAME,
  SiteBSCBAY.name,
  BSCBAY.name,
  PortBSCBAY.name,
  SiteBTS.name,
  BTS.name,
  PortBTS.name,
  AbisEtat.name,
  AbisBand.name,
  AbisResolt.name,
  Ima.NAME,
  Site1.name,
  Node1.name,
  Port1.name,
  Site2.name,
  Node2.name,
  Port2.name,
  decode(Ima.CIRCUIT2CIRCUITTYPE,1800510209,'Ethernet Bearer',1800510201,'MLPPP',1800510211,'Optical Bearer',1800510215,'Microwave Ethernet Bearer',1800510216,'VLAN') Lientype,--IPisation: added Microwave Ethernet and VLAN
  Bandwidth.name,
  decode(Ima.CIRCUIT2CIRCUITTYPE,1800510209,ImaDef.name,1800510201,ImaDef.name,1800510211,ImaDef.name,1800510215,ImaDef.name,1800510216,ImaDef.name) ImaDef,--IPisation: added Microwave Ethernet and VLAN
  EtatIma.NAME,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  BSCBAY.ALIAS2,
  BTS.ALIAS2,
  CirCir.routesequence,
  -- WIPRO Single RAN Ph-2 lot-1: Start:  include Alias_3G
  BTSX.Alias_3G,
-- WIPRO Single RAN Ph-2 lot-1: End:  include Alias_3G
-- IUBoverIP Start
  TR_IUBoverIPX.path,
  TR_IUBoverIPX.traffic,
  TR_IUBoverIPX.type,
  TR_IUBoverIPX.VLANID_NODEB,
  NodeBUPCP.name,
  NodeBIP1.name,
  TR_IUBoverIPX.NODEB_PORT, --PortBTS1.NAME,
  TR_IUBoverIPX.VLANID_RNC,
  RNCSubInt.name,
  RNCSubICSU.name,
  TR_IUBoverIPX.RNC_PORT,
  TR_IUBoverIPX.rnc_sign,
  TR_IUBoverIPX.CAC,
  BSCBAYType.NAME,
  BTSType.NAME,
  Abis.relativename,
 --WIPRO : MEV418 : 30/10/2013 :Start
  nbre.value       ,
  rep.value
  --WIPRO : MEV418 : 30/10/2013 :End
 -- IUBoverIP End
FROM
  CIRCUIT_CIRCUIT_BEB CirCir,
  CIRCUIT Abis,
  PORT PortBTS,
  PORT PortBSCBAY,
  LOCATION SiteBTS,
  LOCATION SiteBSCBAY,
  PROVISIONSTATUS AbisEtat,
  BANDWIDTH_M AbisBand,
  NODE BSCBAY,
  NODE BTS,
  NODENODEBX BTSX,
  NODETYPE_M BSCBAYType,
  NODETYPE_M BTSType,
  RESOLUTIONSTATUS AbisResolt,
  LOCATION Region,
  --LOCATIONX LocX,
  LOCATION SousRegion,
  LOCATION Departement,
  CIRCUIT Ima,
  PROVISIONSTATUS EtatIma,
  location Site1,
  location Site2,
  location Site1bis,
  location Site2bis,
  node Node1,
  node Node2,
  port Port1,
  port Port2,
  BANDWIDTH_M Bandwidth,
  circuitDEF_M ImaDef,
  -- IUBoverIP Start
  TMN_RELATIONIUBIPX TR_IUBoverIPX,
  DIMNUMBER NodeBUPCP,
  NUMBEROBJECT NONodeBUPCP,
  DIMNUMBER NodeBIP1,
  DIMNUMBER RNCSubInt,
  NUMBEROBJECT NORNCSubInt,
  DIMNUMBER RNCSubICSU,
  NUMBEROBJECT NORNCSubICSU,
    --WIPRO : MEV418 : 30/10/2013 :start
  circuitx cx                    ,
  (SELECT sequence               ,
    value
     FROM enumeration
    WHERE tablename = 'CIRCUITX'
  AND fieldname     ='Z_PAIRES_DE_CARTE_NPGEP'
  ) NBRE          ,
  (SELECT sequence,
    value
     FROM enumeration
    WHERE tablename = 'CIRCUITX'
  AND fieldname     ='Z_REPARTITION_STATIQUE'
  ) REP
  --WIPRO : MEV418 : 30/10/2013 :End
  -- IUBoverIP End
WHERE
  ((   Abis.CIRCUIT2STARTPORT = PortBTS.portid
  AND Abis.CIRCUIT2ENDPORT = PortBSCBAY.portid
  AND Abis.CIRCUIT2STARTLOCATION = SiteBTS.locationid
  AND Abis.CIRCUIT2ENDLOCATION = SiteBSCBAY.locationid
  AND Abis.CIRCUIT2STARTNODE = BTS.nodeid
  AND Abis.CIRCUIT2ENDNODE = BSCBAY.nodeid
  ) OR
  (   Abis.CIRCUIT2STARTPORT = PortBSCBAY.portid
  AND Abis.CIRCUIT2ENDPORT = PortBTS.portid
  AND Abis.CIRCUIT2STARTLOCATION = SiteBSCBAY.locationid
  AND Abis.CIRCUIT2ENDLOCATION = SiteBTS.locationid
  AND Abis.CIRCUIT2STARTNODE = BSCBAY.nodeid
  AND Abis.CIRCUIT2ENDNODE = BTS.nodeid
  ))
AND BTS.NODEID = BTSX.NODEID
AND CirCir.usedby2circuit = Abis.circuitid
AND CirCir.uses2circuit = Ima.circuitid
AND Abis.CIRCUIT2PROVISIONSTATUS = AbisEtat.provisionstatusid
AND Abis.CIRCUIT2BANDWIDTH = AbisBand.Bandwidthid(+)
AND Abis.CIRCUIT2RESOLUTIONSTATUS = AbisResolt.resolutionstatusid
AND BSCBAY.node2nodetype = BSCBAYType.nodetypeid
AND BTS.node2nodetype = BTSType.nodetypeid
--AND SiteBTS.locationid = LocX.locationx2juniorlocation
--AND SiteBTS.location2locationtype = LocX.locationx2juniorlocationtype
--AND Region.locationid = LocX.locationx2seniorlocation
--AND Region.location2locationtype = LocX.locationx2seniorlocationtype
--AND LocX.locationx2seniorlocationtype = 101
AND SousRegion.LOCATION2PARENTLOCATION = Region.LOCATIONID
AND Departement.LOCATION2PARENTLOCATION = SousRegion.LOCATIONID
AND SiteBTS.LOCATION2PARENTLOCATION = Departement.LOCATIONID
AND Ima.circuit2provisionstatus = EtatIma.provisionstatusid
AND Ima.CIRCUIT2STARTLOCATION = Site1bis.locationid
AND ((Site1bis.LOCATION2LOCATIONTYPE IN (118,104) AND
          Site1.LOCATIONID=Site1bis.LOCATIONID) OR
         (Site1bis.LOCATION2LOCATIONTYPE = 127 AND
          Site1.LOCATIONID=Site1bis.LOCATION2PARENTLOCATION))
AND Ima.CIRCUIT2STARTNODE = Node1.nodeid
AND Ima.CIRCUIT2STARTPORT = Port1.portid
AND Ima.CIRCUIT2ENDLOCATION = Site2bis.locationid
AND ((Site2bis.LOCATION2LOCATIONTYPE IN (118,104) AND
          Site2.LOCATIONID=Site2bis.LOCATIONID) OR
         (Site2bis.LOCATION2LOCATIONTYPE = 127 AND
          Site2.LOCATIONID=Site2bis.LOCATION2PARENTLOCATION))
AND Ima.CIRCUIT2ENDNODE = node2.nodeid
AND Ima.CIRCUIT2ENDPORT = port2.portid
AND Ima.CIRCUIT2BANDWIDTH = Bandwidth.BANDWIDTHID(+)
AND Ima.CIRCUIT2CIRCUITDEF = ImaDef.circuitdefid
AND BSCBAYType.class IN  ('RNC')
AND BTSType.class IN ('NODEB')
AND Abis.CIRCUIT2CIRCUITTYPE  = 1800510203 --Single RAN Ph-2 lot-1: Include IUB over IP circuit
AND Ima.CIRCUIT2CIRCUITTYPE IN (1800510209,1800510201,1800510211,1800510215,1800510216)--IPisation: added Microwave Ethernet and VLAN
AND SiteBTS.LOCATION2LOCATIONTYPE IN (118,104)
AND SiteBSCBAY.LOCATION2LOCATIONTYPE IN (118,104)
AND Region.LOCATION2LOCATIONTYPE = 101
AND SousRegion.LOCATION2LOCATIONTYPE = 125
AND Departement.LOCATION2LOCATIONTYPE = 126
--Single RAN Ph-2 lot-1:e
-- IUBoverIP Start
    AND TR_IUBoverIPX.CIRCUITID(+) = Abis.CIRCUITID
        AND  NONodeBUPCP.NUMBEROBJECT2OBJECT = BTS.nodeid
    AND NodeBUPCP.DIMNUMBER2DIMNUMBERTYPE = 21
        AND NONodeBUPCP.NUMBEROBJECT2NUMBER = NodeBUPCP.DIMNUMBERID
    AND NONodeBUPCP.NUMBEROBJECT2RELATION = 1800500204
   AND (NodeBIP1.PARENTDIMNUMBER2DIMNUMBER  = NodeBUPCP.DIMNUMBERID or NodeBIP1.name is null)
    AND NodeBIP1.DIMNUMBER2DIMNUMBERTYPE(+) = 1800000329
    AND NodeBIP1.name(+) = TR_IUBoverIPX.NODEB_IP
    AND NORNCSubInt.NUMBEROBJECT2OBJECT = BSCBAY.nodeid
        AND NORNCSubInt.NUMBEROBJECT2NUMBER = RNCSubInt.DIMNUMBERID
    AND RNCSubInt.DIMNUMBER2DIMNUMBERTYPE = 20
    AND NORNCSubInt.NUMBEROBJECT2RELATION = 1800500201
        AND NORNCSubICSU.NUMBEROBJECT2OBJECT = BSCBAY.nodeid
        AND NORNCSubICSU.NUMBEROBJECT2NUMBER = RNCSubICSU.DIMNUMBERID
    AND RNCSubICSU.DIMNUMBER2DIMNUMBERTYPE = 22
    AND NORNCSubICSU.NUMBEROBJECT2RELATION = 1800500206
        --WIPRO : MEV418 : 30/10/2013 :Start
   AND Abis.circuitid                        = cx.circuitid
   AND NBRE.sequence(+)                         = cx.Z_PAIRES_DE_CARTE_NPGEP
   AND REP.sequence(+)                          = cx.Z_REPARTITION_STATIQUE
--WIPRO : MEV418 : 30/10/2013 :End
    --ORDER BY Abis.name,circir.routesequence,CirCir.sequence,TR_IUBoverIPX.path
    ;