/* liefert alle UGPART's Datasets einer IR überarbeitet 28.10.09 J.Fes/T.Nestler */
select
  -- i.pitem_id,
  -- ir.pitem_revision_id,
  pd.puid                 AS DataSetPuid ,
  wso.pobject_name        AS DataSetName ,
  wso.pobject_desc        AS DataSetDescription ,  -- description new 20.11.2008

  pdt.pdatasettype_name   AS DataSetType ,         -- UGMASTER.. UGPART.. UGALTREP.. UGSCENARIO
  ptype.ptype_name        AS DataSetPTYPE_NAME ,   -- neu 8.2.09 PType_Name: IMAN_specification. IMAN_UG_altrep IMAN_UG_scenario IMAN_specification IMAN_manifestation

  pu_Owni_ds.puser_id     As DataSetOwningUser ,
  pg_Owni_ds.pname        As DataSetOwningGroup ,
  pao.pcreation_date      AS DataSetCreateDate ,
  pu_lMod_ds.puser_id     As DataSetLastModUser ,
  
  pao.plast_mod_date      AS DataSetLastModDate ,
  
  imv.pvolume_name        AS imv_pvolume_name,     -- volume name
  imv.pmachine_type       As imv_pmachine_type,    -- Maschinen Type 0=winnt 1=
  imv.punix_path_name     As imv_punix_path_name , -- iman vol unix pathname Bspl.:
  imv.pwnt_path_name      As imv_pwnt_path_name ,  -- iman vol windows pathname Bspl.:\\jf-18-vmw2000\TceData\TCE_VOL2

  imf.psd_path_name       As imf_psd_path_name ,
  imf.pfile_name          AS imf_pfile_name  --
from
  infodba.PDATASET                pd,
  infodba.PDATASET                pd1,

  infodba.PIMANRELATION           pr,
  infodba.PIMANTYPE               ptype, -- PIMANTYPE,
  infodba.PWORKSPACEOBJECT        wso,
  infodba.PPOM_APPLICATION_OBJECT pao,
  infodba.PDATASETTYPE            pdt,
  infodba.PREVISIONANCHOR         a,
  infodba.PPOM_USER               pu_Owni_ds  ,  -- DataSet OwningUser
  infodba.PPOM_USER               pu_lMod_ds ,   -- DataSet LastModUser
  infodba.PPOM_GROUP              pg_Owni_ds  ,  -- DataSet OwningGroup

  infodba.PREVISIONS              rev,
  infodba.PREF_NAMES              rn,
  
  infodba.PREF_LIST               rl,
  
  infodba.PIMANFILE               imf,
  infodba.PIMANVOLUME             imv
where
  -- i.pitem_id = 'W2001195' and
  -- ir.pitem_revision_id = 'B' and
  -- i.puid=ir.ritems_tagu and
  -- and ir.puid=pr.rprimary_objectu
  
  -- para_puid tceng:[000823/A=hgNlChHaVe1piD]
  -- para_puid im9t:[W2001195/B=BUFlE0hmAjJ1aC]

      pr.rprimary_objectu      = :para_puid
  and pr.rsecondary_objectu    = pd.puid
  and ptype.puid               = pr.rrelation_typeu
  and pd.rdataset_typeu        = pdt.puid
  and wso.puid                 = pd.puid
  and pd.rrev_chain_anchoru    = a.puid
  and rev.puid                 = a.puid
  
  and pu_Owni_ds.puid          = pao.rowning_useru
  and pu_lMod_ds.puid          = pao.rlast_mod_useru
  and pg_Owni_ds.puid          = pao.rowning_groupu
  
  and rl.puid                  = pd.puid          -- ref auf PREF_LIST
  
  and imf.puid                 = rl.pvalu_0       -- PIMANFILE
  and imv.puid                 = imf.rvolume_tagu -- PIMANVOLUME

  and pao.puid                 = rev.pvalu_0
  and pd1.puid                 = rev.pvalu_0
  and rn.puid                  = pd1.puid
  and rn.pval_0                = 'UGPART'   -- nur UGPART
  and rev.pseq                 = 0          -- nur letztes Dokument
  and rn.pseq                  = rl.pseq    -- 12.11.2009 18:52 noch pruefen von Thomas ersetzt: [and imf.pfile_name like '%.prt']
  -- and imf.pfile_name        like '%.prt'    -- nur *.prt
