-- #New:17.07.2019 / J.Fes
-- #Doc: finds all datasets with dublicated 'UGPART-BBOX' ,'UGPART-ATTR' entry in named references
Select
 count(i.pitem_id)
 , i.pitem_id AS ItemId
 , ir.pitem_revision_id AS IrId
 , rn.pval_0

From
 infodba.PDATASET pd
 Left Join infodba.PREF_LIST_0 rl   On (rl.puid = pd.puid)
 Left Join infodba.PREF_NAMES_0 rn  On (rl.puid = rn.puid And rl.pseq = rn.pseq)
 Left Join infodba.PDATASETTYPE pdt ON (pdt.puid = pd.rdataset_typeu)
 Left Join infodba.PREVISIONS rev ON (rev.pvalu_0 = pd.puid)
 Left Join infodba.PIMANRELATION pr ON (pr.rsecondary_objectu = pd.puid)
 Left Join infodba.PITEMREVISION ir ON (ir.puid = pr.rprimary_objectu)
 Left Join infodba.PITEM i ON (i.puid = ir.ritems_tagu)
 Left Join infodba.PWORKSPACEOBJECT wso on (wso.puid = pd.puid)

where
 pdt.pdatasettype_name IN ('UGMASTER')
 and rn.pval_0 IN ('UGPART-BBOX','UGPART-ATTR','UGPART-ATTRIBUTES')
 and rev.pseq = 0
 and wso.pactive_seq = 1
 and ir.pitem_revision_id is not null

GROUP BY
  i.pitem_id, ir.pitem_revision_id, rn.pval_0

HAVING
  COUNT(i.pitem_id) > 1

ORDER BY
  i.pitem_id, ir.pitem_revision_id desc, rn.pval_0
