-- #New  05.08.2021/J.Fes get Drawing for CATPart CATProduct
SELECT
 iMe.pitem_id + '/' + irDrw.pitem_revision_id as CATMasterDrawingsIr,
 iMa.pitem_id AS CATMaster_ItemId,
 irMa.pitem_revision_id AS CATMaster_IrId,
 iMa.puid AS CATMaster_ItemPuid,
 irMa.puid AS CATMaster_IrPuid,
 iMe.pitem_id AS CATDrawing_ItemId,
 irDrw.pitem_revision_id AS CATDrawing_IrId,
 iMe.puid AS CATDrawing_ItemPuid,
 irDrw.puid AS CATDrawing_IrPuid,
 ptypeMa.ptype_name
FROM
 dbo.PITEMREVISION irMa
 LEFT JOIN dbo.PITEM iMa ON (iMa.puid = irMa.ritems_tagu)
 LEFT JOIN dbo.PIMANRELATION prMa ON (prMa.rsecondary_objectu = irMa.puid)
 LEFT JOIN dbo.PIMANTYPE ptypeMa ON (ptypeMa.puid = prMa.rrelation_typeu)
 LEFT JOIN dbo.PITEMREVISION irDrw ON (irDrw.puid = prMa.rprimary_objectu)
 LEFT JOIN dbo.PITEM iMe ON (iMe.puid = irDrw.ritems_tagu)
 LEFT JOIN dbo.PWORKSPACEOBJECT pwoMe ON (pwoMe.puid = irDrw.puid)
WHERE
  ptypeMa.ptype_name = 'TC_DrawingOf'
 AND pwoMe.pactive_seq = 1
 AND irMa.puid = :para_IrPuid
Order by
 CATMaster_ItemId,
 CATMaster_IrPuid
