-- #New:01.07.2020/S.Gueth finds all Checked Out Objects
SELECT
 pi.puid              AS ObjectPuid,
	'I'                  AS ObjectClass,
	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.PPOM_USER us on us.puid = res.ruser_objectu
  JOIN infodba.PITEM pi ON (pi.puid = pdrel.rprimary_objectu)
Union ALL
SELECT
 pir.puid             AS ObjectPuid,
	'IR'                 AS ObjectClass,
	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.PPOM_USER us on us.puid = res.ruser_objectu
  JOIN infodba.PITEMREVISION pir ON (pir.puid = pdrel.rprimary_objectu)
Union ALL
SELECT
 pd.puid              AS ObjectPuid,
	'DS'                 AS ObjectClass,
	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.PPOM_USER us on us.puid = res.ruser_objectu
  JOIN infodba.PDATASET pd ON (pd.rrev_chain_anchoru = pdrel.rprimary_objectu)
Union ALL
SELECT
 pir.puid             AS ObjectPuid,
	'BVR'                AS ObjectClass,
	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.PPOM_USER us on us.puid = res.ruser_objectu
  JOIN infodba.PPSBOMVIEWREVISION pbvr ON (pbvr.puid = pdrel.rprimary_objectu)
  JOIN infodba.PSTRUCTURE_REVISIONS ps ON (ps.pvalu_0 = pbvr.puid)
  JOIN infodba.PITEMREVISION pir ON (ps.puid = pir.puid)
