3G huawei

SELECT
trig.nodeb_id,
trig.rncbk AS "RNC BK",
trig.basekey "nodeb_bk",
--nodeb.object_id,
ST.DESCRIPTION,
FU_GET_RLMNUMBERS_NODEB_BK(trig.basekey, 'TAG UNASSIGNED') RLMNUMBERS
FROM
(
WITH temp_query AS (
SELECT objs.BASEKEY, objs.nodeb_id, objs.rncbk
FROM (
SELECT  nodeb.basekey, nodeb.nodeb_id, rnc.basekey rncbk
  FROM nodeb_rec nodeb ,vi_nodeb_card nbc , nodes nbp
       , (SELECT * FROM vi_o2_vcs_both_ways_stat UNION ALL SELECT * FROM vi_o2_ip_srv_both_ways_stat) fpbw
       , (SELECT 'vc' objecttype, object_id, TYPE FROM vc_rec UNION ALL SELECT 'ip-service' objecttype, object_id, service_type TYPE FROM ip_service_rec) vc, node_details npr
       , (SELECT * FROM rnc_rec rnc WHERE ROWNUM > 0) rnc
WHERE
   nodeb.object_id = nbc.nb_object_id
   AND nbc.nb_status_deployment IN (pk_status_def.S_Toc, pk_status_def.S_Operational)  -- MKP_otn_99
   AND nbc.nbc_object_id = nbp.parent_id
   AND nbp.OBJECT_ID =  fpbw.A
   AND fpbw.Z = npr.OBJECT_ID
   AND npr.ne = rnc.OBJECT_ID
   AND fpbw.OBJECT_ID = vc.OBJECT_ID
   AND ((vc.objecttype = 'vc' AND vc.TYPE = 'OAM') OR vc.objecttype = 'ip-service')
   AND rnc.vendor IS NOT NULL) objs)
SELECT q1.nodeb_id, q1.rncbk, q1.basekey
  FROM temp_query q1,(
       SELECT nodeb_id, rncbk FROM temp_query
        GROUP BY nodeb_id, rncbk
       HAVING (COUNT(DISTINCT BASEKEY) > 1)
       ) q2
 WHERE q1.nodeb_id = q2.nodeb_id
   AND q1.rncbk = q2.rncbk
 ORDER BY nodeb_id, rncbk, basekey)
 trig
 inner join nodeb_rec nodeb ON trig.basekey = nodeb.basekey
inner join node_details nd ON nd.object_id = nodeb.object_id
-- left  join PA_PROJECT_REC ppr on   ppr.basekey = nodeb.basekey --nd.object_id = replace(ppr.OBJECT_ID_1, '''','')
left  join status st ON  st.status_id = nd.status
 ORDER BY trig.nodeb_id
 ;