/* TC:SQL all DS of IR */
SELECT
  i.pitem_id               AS IrName ,
  ir.pitem_revision_id     AS IrRev ,
  -- paoIr.puid ,
  paoIr.PCREATION_DATE     AS IrCreateDate ,
  paoIr.PLAST_MOD_DATE     AS IrLastModDate ,
  puIrOwningUser.puser_id  AS IrOwningUser ,
  puIrLastModUser.puser_id As IrLastModUser ,
  pgIr.PNAME               As IrOwningGroup ,

  -- ir.puid ,
  pdt.pdatasettype_name    AS DataSetType ,      --
  -- wso.puid ,
  wso.pobject_name         AS DataSetName ,      --
  -- paoDs.puid ,
  paoDs.PCREATION_DATE     AS DataSetCreateDate ,  --
  paoDs.PLAST_MOD_DATE     AS DataSetLastModDate , --
  puDsOwningUser.puser_id  AS DataSetOwningUser ,  --
  puDsLastModUser.puser_id As DataSetLastModUser , --
  pgDs.PNAME               As DataSetOwningGroup , --

  s.pname                  AS OwningSite ,

  imv.pwnt_path_name       As DataSetpwnt_path_name ,
  imf.psd_path_name        As DataSetPsd_path_name ,
  imf.pfile_name           AS DataSetPfile_name
  -- imf.poriginal_file_name  As DataSetPoriginal_file_name ,
FROM
  infodba.PITEM                        i,
  infodba.PITEMREVISION                ir,
  infodba.pimanrelation                pr,
  infodba.pdataset                     pd,
  infodba.pdatasettype                 pdt,
  infodba.pref_names_0                 rn,
  infodba.pref_list_0                  rl,
  infodba.pimanfile                    imf,
  infodba.pimanvolume                  imv ,
  infodba.ppom_application_object      paoIr,
  infodba.ppom_application_object      paoDs,
  infodba.pworkspaceobject             wso,
  infodba.ppom_object                  o,
  infodba.ppom_imc                     s,
  infodba.ppom_user                    puDsOwningUser ,
  infodba.ppom_user                    puDsLastModUser ,
  infodba.ppom_user                    puIrOwningUser ,
  infodba.ppom_user                    puIrLastModUser ,
  infodba.PPOM_GROUP                   pgDs ,
  infodba.PPOM_GROUP                   pgIr ,
  infodba.previsions                   re  -- heisst infodba.previsions_0  oder infodba.previsions

WHERE i.puid = ir.ritems_tagu
 AND ir.puid = pr.rprimary_objectu
 AND pr.rsecondary_objectu = pd.puid
 AND pd.rdataset_typeu = pdt.puid
 AND pd.puid = rn.puid
 AND rn.pval_0 = 'UGPART'
 AND rn.pseq = rl.pseq
 AND pd.puid = rl.puid
 AND imf.puid = rl.pvalu_0
 AND imv.puid = imf.rvolume_tagu
 --  AND paoDs.puid = imf.puid
 AND paoDs.puid = rl.pvalu_0 -- eingefügt 18.07.2007 20:11:03
 AND pd.puid = wso.puid
 AND o.puid = ir.puid
 AND o.rowning_siteu = s.puid (+)
 AND puDsOwningUser.puid = paoDs.rowning_useru
 AND puDsLastModUser.puid = paoDs.rlast_mod_useru
 AND puIrOwningUser.puid = paoIr.rowning_useru
 AND puIrLastModUser.puid = paoIr.rlast_mod_useru
 AND pgDs.puid = paoDs.ROWNING_GROUPU
 AND pgIr.puid = paoIr.ROWNING_GROUPU
 AND paoIr.puid = ir.puid
 -- AND upper(wso.pobject_name) like upper('T12345%')
 -- AND upper(i.pitem_id) like upper('T1%')
 AND pd.puid=re.pvalu_0
 AND re.pseq=0
 -- And paoDs.plast_mod_date >= TO_DATE('16.06.2007 01:00', 'DD.MM.YYYY HH:MI')  -- das geht !!
