/* Description: 16.03.2010 J.Fes/T.Nestler
   Ermittelt alle IRs die ein UGMASTER beinhalten
   trans to join 24.01.2012 J.Fes */
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_TCE_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','UGMANIFESTATION','UGSCENARIO','NXSIMULATION','CAEGEOM','CAEMesh','CAESolution')
 AND rev.pseq = 0
