SELECT DISTINCT 
  i.PUID               AS ItemPuid,
  i.PITEM_ID           AS ItemName,
  ir.PUID              AS IrPuid,
  ir.PITEM_REVISION_ID AS IrRev,
  paoIr.PLAST_MOD_DATE AS IrLastModDate,
  s.PSITE_ID           AS IrOwningSite_TC_pSite_ID
FROM infodba.PDATASET pd
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.PPOM_OBJECT o ON (ir.PUID = o.PUID)
FULL JOIN infodba.PPOM_IMC s ON (o.ROWNING_SITEU = s.PUID)
LEFT JOIN infodba.PPOM_APPLICATION_OBJECT paoIr ON (paoIr.PUID = ir.puid)
LEFT JOIN infodba.PDATASETTYPE pdt ON (pdt.PUID = pd.RDATASET_TYPEU)
LEFT JOIN infodba.PREVISIONANCHOR a ON (a.PUID = pd.RREV_CHAIN_ANCHORU)
LEFT JOIN infodba.PREVISIONS rev ON (rev.PVALU_0 = pd.PUID)
WHERE 
 pdt.PDATASETTYPE_NAME IN ('UGMASTER', 'UGPART','UGALTREP')
 AND rev.PSEQ = 0