-- #New:30.06.2020/J.Fes finds all Checked Out Datasets
SELECT
  i.pitem_id             AS I_Id,
  i.puid                 AS I_puid,
  ir.pitem_revision_id   AS Ir_Id,
  ir.puid                AS Ir_puid,
  pd.puid                AS DataSetPuid,
  pdwso.pobject_name     AS DataSetName,
  res.pdate              AS CheckOutDate,
  us.puser_id            AS CheckOutUser,
  res.pchange_id         AS CheckOutChangeId
FROM
  infodba.PIMANRESERVATION res
  JOIN infodba.PIMANRELATION pdrel ON (pdrel.rsecondary_objectu = res.puid)
  JOIN infodba.PIMANTYPE typ ON (pdrel.rrelation_typeu = typ.puid AND typ.ptype_name = 'IMAN_RES_checkout')
  JOIN infodba.PDATASET pd ON (pd.rrev_chain_anchoru = pdrel.rprimary_objectu)
  JOIN infodba.PREVISIONS pdrev ON (pdrev.puid = pd.rrev_chain_anchoru and pdrev.pseq = 0)
  JOIN infodba.PIMANRELATION irrel ON (irrel.rsecondary_objectu = pd.puid)
  JOIN infodba.PITEMREVISION ir ON (ir.puid = irrel.rprimary_objectu)
  JOIN infodba.PWORKSPACEOBJECT irpwo ON (ir.puid = irpwo.puid and irpwo.pactive_seq = 1)
  JOIN infodba.PITEM i ON (i.puid = ir.ritems_tagu)
  JOIN infodba.PWORKSPACEOBJECT pdwso ON (pdwso.puid = pd.puid)
  JOIN infodba.PPOM_USER us ON us.puid = res.ruser_objectu
-- where ir.puid IS NOT NULL
ORDER BY
  i.pitem_id,
  ir.pitem_revision_id,
  pdwso.pobject_name
