Select
 i.puid               AS ItemPuid,
 i.pitem_id           AS ItemId,
 ir.puid              AS IrPuid,
 ir.pitem_revision_id AS IrId,
 pd.puid              As DataSet_Puid,
 wso.pobject_name     AS DataSetName,
 refs.pval_0          AS NameREf_REF
 -- refs.*
From
 infodba.PDATASET pd
 Left Join
 (
   SELECT rli.puid, rni.pval_0
   FROM infodba.PREF_LIST_0 rli
   INNER JOIN infodba.PREF_NAMES rni ON (rli.puid = rni.puid and rli.pseq = rni.pseq)
   WHERE rni.pval_0 = 'APPUID-OBJ'
 ) refs ON (refs.puid = pd.puid)

 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
 -- pd.puid = :para_puid
 -- pd.puid in ('xUMlg0$FTzDfjB','SfN9QuVdTzDfjB') -- tc83
     pdt.pdatasettype_name IN ('UGALTREP','UGPART')
 AND rev.pseq = 0
 AND refs.puid IS NULL
