-- #New: 22.10.2021 /J.Fes SQL ideas find for 3D Master 2D Drawing
-- #Doc: Name: ObjectAttr_01_Ideas_for3D_find_2D_Drawing.sql
-- #Doc: If 3D has multibel 2D DRW then n line (2..3..) are created seperated by CRLF
select listagg(Ir2D, CHR(13)||CHR(10)) within group (order by Ir2D) As Ir2D
from (
select
      i2D.pitem_id || '/' || ir2D.pitem_revision_id as Ir2D
FROM
           infodba.pitem i2D
 LEFT JOIN infodba.PITEMREVISION ir2D     on (i2D.puid=ir2D.ritems_tagu)
 LEFT JOIN infodba.PIMANRELATION irel2D   on (ir2D.puid=irel2D.rprimary_objectu)
 LEFT JOIN infodba.PWORKSPACEOBJECT wso2D on (irel2D.rsecondary_objectu=wso2D.puid)
 LEFT JOIN infodba.PIMANRELATION irel3D   on (wso2D.puid=irel3D.rprimary_objectu)
 LEFT JOIN infodba.PITEMREVISION ir3D     on (irel3D.rsecondary_objectu=ir3D.puid)
 LEFT JOIN infodba.PITEM i3d              on (ir3D.ritems_tagu=i3d.puid)
WHERE
 wso2D.pobject_type='IdeasDrawing'
and ir3D.PUID = :para_IrPuid
) t
