SELECT 'PRODUCT' AS CONTAINER_TYPE
, P.NAMECONTAINERINFO AS CONTAINER_NAME
, CASE P.PRIVATEACCESSCONTAINERINFO WHEN 1 THEN 'Yes' ELSE 'No' END AS PRIVATE_ACCESS
, R.NAME AS ROLE
, L.CLASSNAMEKEYROLEBOBJECTREF AS MEMBER_TYPE
, CASE WHEN L.CLASSNAMEKEYROLEBOBJECTREF = 'wt.org.WTGroup' THEN M1.NAME
WHEN L.CLASSNAMEKEYROLEBOBJECTREF = 'wt.org.WTUser' THEN M2.NAME
ELSE '{' || L.CLASSNAMEKEYROLEBOBJECTREF || ':' || L.IDA3B5 || '}' END AS MEMBER_NAME
, CASE WHEN L.CLASSNAMEKEYROLEBOBJECTREF = 'wt.org.WTGroup' THEN M1.NAME
WHEN L.CLASSNAMEKEYROLEBOBJECTREF = 'wt.org.WTUser' THEN M2.FULLNAME
ELSE '{' || L.CLASSNAMEKEYROLEBOBJECTREF || ':' || L.IDA3B5 || '}' END AS MEMBER_NAME2
FROM PDMLINKPRODUCT P
JOIN WTGROUP R ON P.IDA2A2 = R.IDA3CONTAINERREFERENCE
JOIN MEMBERSHIPLINK L ON R.IDA2A2 = L.IDA3A5
LEFT JOIN WTGROUP M1 ON L.IDA3B5=M1.IDA2A2 AND L.CLASSNAMEKEYROLEBOBJECTREF = 'wt.org.WTGroup'
LEFT JOIN WTUSER M2 ON L.IDA3B5=M2.IDA2A2 AND L.CLASSNAMEKEYROLEBOBJECTREF = 'wt.org.WTUser'
WHERE R.NAME NOT IN ('orgs','roleGroups','teamMembers')
AND R.NAME NOT LIKE '%\_ORG' ESCAPE '\'
ORDER BY P.NAMECONTAINERINFO, R.NAME DESC, L.CLASSNAMEKEYROLEBOBJECTREF, MEMBER_NAME2;