I was able to solve it by joining nodetable and propertytable only. It seems that the list id is stored in SELECTION column of the propertytable having propertyid as 15.

Here is the final query. I hope it helps.

SELECT DISTINCT T.*,p.value as attribute_name,substr(p.value, 1, instr(p.value, ‘.’) -1) as attribute_tab_name FROM
(
WITH CLASSID AS (
SELECT n1.ID, n1.description classname,–NSCLS.ID AS SCLSID, NSCLS.INHERIT,
n3.id as subclassid, n3.description as subclassname,n5.id as baseclassid,n5.description as baseclassname
from nodetable n5

join nodetable n6 on n5.id=n6.inherit
join
NODETABLE n1 on n6.parentid=n1.id
join nodetable n2 on n2.parentid = n1.id
left outer join nodetable n3 on n3.parentid = n2.id

WHERE 1 = 1
AND n1.parentid = 5002
AND UPPER (n5.DESCRIPTION) = UPPER (‘Items’)
and n2.objtype=14 and n5.parentid = 900
and n6.inherit=901
— AND UPPER (DESCRIPTION) = UPPER (‘Items’)
)
, METADATA3 as (
SELECT –distinct na2.*
— nt.id,
plistid.selection,
classid.id ClassId,
baseclassname,ClassName,subclassname,subclassid,
na.id AttrId,
nt.description TableName,
LOWER(NVL(NA3.NAME,NA.NAME)) Name,
NVL(NA3.DESCRIPTION,na.description) DisplayName,
P.VALUE DTNum,
P.PROPERTYID DTPID,
TI.ORDERING,nt.inherit,apv.value vs,PV.VALUE vis
FROM
tableinfo ti join CLASSID on ti.classid=subclassid

INNER JOIN nodetable nt
ON ti.tabid = nt.id
INNER JOIN nodetable na
ON ti.att = na.id
INNER JOIN NODETABLE NA2
ON na2.PARENTid = ti.classid
LEFT OUTER JOIN NODETABLE NA3
ON NA3.PARENTID = NA2.ID
AND NA3.INHERIT = TI.ATT
LEFT OUTER JOIN propertytable p
ON P.PARENTID = na.id
AND p.propertyid = 2
LEFT OUTER JOIN propertytable pV
ON Pv.PARENTID = na3.id
AND pv.propertyid = 9
left outer join propertytable apv
on na.id = apv.parentid and apv.propertyid=9
left outer join propertytable plistid
on na3.id = plistid.PARENTID
and plistid.propertyid = 15
WHERE 1 = 1– and classid = 28949
AND nt.objtype = 9 –and nt.inherit = 1 –UPPER (nt.description) = UPPER (‘Page Two’)
AND na2.objtype = 4 –UPPER (na2.description) = UPPER (‘Attributes’)
), HEADINGS3 AS (
SELECT DISTINCT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME, 0 AS ORDERING, -1 AS PREV_ORDER, 0 AS SEQ
FROM METADATA3 M1

UNION ALL

SELECT M1.CLASSID, M1.CLASSNAME, M1.DISPLAYNAME, ORDERING, -1 AS PREV_ORDER, ROW_NUMBER() OVER (PARTITION BY CLASSID ORDER BY ORDERING ASC) AS SEQ
FROM METADATA3 M1
WHERE M1.DTNum IS NULL
AND M1.ORDERING < 999
), GROUPHEADINGS3 AS (
SELECT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME GROUPHEADING3, M1.ORDERING STARTORDER, NVL(M2.ORDERING,999) ENDORDER
FROM HEADINGS3 M1
LEFT OUTER JOIN HEADINGS3 M2
ON M1.CLASSID = M2.CLASSID
AND (M1.SEQ +1) = M2.SEQ
),pagethree as (
SELECT
M.SELECTION,
M.ClassId,baseclassname,
M.ClassName,subclassname,subclassid,
AttrId,
TableName,
NVL(GP.GROUPHEADING3,TableName) AS GroupName,
Name,
DisplayName,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘nvarchar’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘number’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘datetime’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘money’
ELSE
”
END AS Type,
NULL AS DeafultValue,
ORDERING AS DisplayOrder,
0 AS IsLink,
CASE WHEN ((vs=1 and vis is null) or vis=1) THEN 1 ELSE 0 END AS Visible,vs,vis,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘Text/Multitext’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘List’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘MultiList’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘Numeric’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘Date’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘Money/Cost’
WHEN DTNUM IS NULL– AND P.PROPERTYID = 2
THEN
‘Heading’
ELSE
”
END AS SourceType, 2 as inherit

FROM METADATA3 M
LEFT OUTER JOIN GROUPHEADINGS3 GP
ON M.CLASSID = GP.CLASSID
AND M.ORDERING > GP.STARTORDER
AND M.ORDERING < GP.ENDORDER
WHERE M.DTNUM IS NOT NULL
ORDER BY CLASSID,ORDERING)

, METADATA1 AS (select distinct * from (
SELECT –distinct na2.*
plistid.selection,
classid.id ClassId,baseclassname,
ClassName,subclassname,subclassid,
na.id AttrId,
nt.description TableName,
LOWER(NVL(NA3.NAME,NA.NAME)) Name,
NVL(NA3.DESCRIPTION,na.description) DisplayName,
P.VALUE DTNum,
P.PROPERTYID DTPID,
TI.ORDERING,nt.inherit,apv.value vs,PV.VALUE vis
FROM
classid
INNER JOIN tableinfo ti
ON ti.classid = classid.id
INNER JOIN nodetable nt
ON ti.tabid = nt.id
INNER JOIN nodetable na
ON ti.att = na.id
INNER JOIN NODETABLE NA2
ON na2.PARENTid = ti.classid
LEFT OUTER JOIN NODETABLE NA3
ON NA3.PARENTID = NA2.ID
AND NA3.INHERIT = TI.ATT
LEFT OUTER JOIN propertytable p
ON P.PARENTID = na.id
AND p.propertyid = 2
LEFT OUTER JOIN propertytable pV
ON Pv.PARENTID = na3.id
AND pv.propertyid = 9
left outer join propertytable apv
on na.id = apv.parentid and apv.propertyid=9
left outer join propertytable plistid
on na3.id = plistid.PARENTID
and plistid.propertyid = 15
WHERE 1 = 1
AND nt.objtype = 9 — –UPPER (nt.description) = UPPER (‘Page Two’)
AND na2.objtype = 4 and (nt.inherit = 1) and langid =0 –UPPER (na2.description) = UPPER (‘Attributes’)
)), metaData0 as (
select distinct * from (
SELECT –distinct na2.*
plistid.selection,
classid.id ClassId,baseclassname,
ClassName,subclassname,subclassid,
na.id AttrId,
nt.description TableName,
LOWER(NVL(NA3.NAME,NA.NAME)) Name,
NVL(NA3.DESCRIPTION,na.description) DisplayName,
P.VALUE DTNum,
P.PROPERTYID DTPID,
TI.ORDERING,nt.inherit,apv.value vs,PV.VALUE vis
FROM
classid
INNER JOIN tableinfo ti
ON ti.classid = classid.id
INNER JOIN nodetable nt
ON ti.tabid = nt.id
INNER JOIN nodetable na
ON ti.att = na.id
INNER JOIN NODETABLE NA2
ON na2.PARENTid = ti.classid
LEFT OUTER JOIN NODETABLE NA3
ON NA3.PARENTID = NA2.ID
AND NA3.INHERIT = TI.ATT
LEFT OUTER JOIN propertytable p
ON P.PARENTID = na.id
AND p.propertyid = 2
LEFT OUTER JOIN propertytable pV
ON Pv.PARENTID = na3.id
AND pv.propertyid = 9
left outer join propertytable apv
on na.id = apv.parentid and apv.propertyid=9
left outer join propertytable plistid
on na3.id = plistid.PARENTID
and plistid.propertyid = 15
WHERE 1 = 1
AND nt.objtype = 9 — –UPPER (nt.description) = UPPER (‘Page Two’)
AND na2.objtype = 4 and (nt.inherit = 0) and langid =0 –UPPER (na2.description) = UPPER (‘Attributes’)
–and nt.description = ‘Title Block’
)
),HEADINGS1 AS (
SELECT DISTINCT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME, 0 AS ORDERING, -1 AS PREV_ORDER, 0 AS SEQ
FROM METADATA1 M1
where inherit = 1

UNION ALL

SELECT M1.CLASSID, M1.CLASSNAME, M1.DISPLAYNAME, ORDERING, -1 AS PREV_ORDER, ROW_NUMBER() OVER (PARTITION BY CLASSID ORDER BY ORDERING ASC) AS SEQ
FROM METADATA1 M1
WHERE inherit = 1 and M1.DTNum IS NULL
AND M1.ORDERING < 999
), GROUPHEADINGS1 AS (
SELECT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME GROUPHEADING, M1.ORDERING STARTORDER, NVL(M2.ORDERING,999) ENDORDER
FROM HEADINGS1 M1
LEFT OUTER JOIN HEADINGS1 M2
ON M1.CLASSID = M2.CLASSID
AND (M1.SEQ +1) = M2.SEQ
), HEADINGS0 AS (
SELECT DISTINCT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME, 0 AS ORDERING, -1 AS PREV_ORDER, 0 AS SEQ
FROM METADATA0 M1
where inherit = 0

UNION ALL

SELECT M1.CLASSID, M1.CLASSNAME, M1.DISPLAYNAME, ORDERING, -1 AS PREV_ORDER, ROW_NUMBER() OVER (PARTITION BY CLASSID ORDER BY ORDERING ASC) AS SEQ
FROM METADATA0 M1
WHERE inherit = 0 and M1.DTNum IS NULL
AND M1.ORDERING < 999
), GROUPHEADINGS0 AS (
SELECT M1.CLASSID, M1.CLASSNAME, M1.TABLENAME GROUPHEADING, M1.ORDERING STARTORDER, NVL(M2.ORDERING,999) ENDORDER
FROM HEADINGS0 M1
LEFT OUTER JOIN HEADINGS0 M2
ON M1.CLASSID = M2.CLASSID
AND (M1.SEQ +1) = M2.SEQ
), pagetwo as (select * from (
SELECT
M.selection,
M.ClassId,baseclassname,
M.ClassName,subclassname,subclassid,
AttrId,
TableName,
NVL(GP.GROUPHEADING,TableName) AS GroupName,
Name,
DisplayName,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘nvarchar’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘number’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘datetime’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘money’
ELSE
”
END AS Type,
NULL AS DeafultValue,
ORDERING AS DisplayOrder,
0 AS IsLink,
CASE WHEN ((vs=1 and vis is null) or vis=1) THEN 1 ELSE 0 END AS Visible,vs,vis,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘Text/Multitext’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘List’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘MultiList’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘Numeric’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘Date’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘Money/Cost’
WHEN DTNUM IS NULL– AND P.PROPERTYID = 2
THEN
‘Heading’
ELSE
”
END AS SourceType,inherit

FROM METADATA1 M
LEFT OUTER JOIN GROUPHEADINGS1 GP
ON M.CLASSID = GP.CLASSID
AND M.ORDERING > GP.STARTORDER
AND M.ORDERING < GP.ENDORDER
WHERE M.DTNUM IS NOT NULL
ORDER BY CLASSID,ORDERING)
)
, titleblock as (select * from (
SELECT
M.selection,
M.ClassId,baseclassname,
M.ClassName,subclassname,subclassid,
AttrId,
TableName,
NVL(GP.GROUPHEADING,TableName) AS GroupName,
Name,
DisplayName,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘nvarchar’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘lookup’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘number’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘datetime’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘money’
ELSE
”
END AS Type,
NULL AS DeafultValue,
ORDERING AS DisplayOrder,
0 AS IsLink,
CASE WHEN ((vs=1 and vis is null)or vis=1) THEN 1 ELSE 0 END AS Visible,vs,vis,
CASE
WHEN DTNUM = 0 AND DTPID = 2
THEN
‘Text/Multitext’
WHEN DTNUM = 1 AND DTPID = 2
THEN
‘List’
WHEN DTNUM = 2 AND DTPID = 2
THEN
‘MultiList’
WHEN DTNUM = 3 AND DTPID = 2
THEN
‘Numeric’
WHEN DTNUM = 4 AND DTPID = 2
THEN
‘Date’
WHEN DTNUM = 5 AND DTPID = 2
THEN
‘Money/Cost’
WHEN DTNUM IS NULL– AND P.PROPERTYID = 2
THEN
‘Heading’
ELSE
”
END AS SourceType,inherit

FROM METADATA0 M
LEFT OUTER JOIN GROUPHEADINGS0 GP
ON M.CLASSID = GP.CLASSID
AND M.ORDERING > GP.STARTORDER
AND M.ORDERING < GP.ENDORDER
WHERE M.DTNUM IS NOT NULL
ORDER BY CLASSID,ORDERING
))

(
select selection,
ClassId,baseclassname,
ClassName,subclassid,subclassname,
AttrId,
TableName,
GroupName,
Name,
DisplayName,

Type,
‘ ‘DeafultValue,
ROW_NUMBER() OVER (PARTITION BY classid,subclassid ORDER BY inherit,Displayorder,attrid) as DisplayOrder,
IsLink,SourceType,visible,vs,vis from (
select *
from pagetwo
where visible=1
union
select *
from titleblock
where visible=1
union
select *
from pagethree
where visible=1
))
)T
inner join propertytable p
on t.ATTRID = P.PARENTID
and P.PROPERTYID = 10
WHERE BASECLASSNAME = ‘Items’
AND CLASSNAME = ‘Documents’
AND SUBCLASSNAME = ‘Bill of Materials’
AND NAME = ‘list08’
order by subclassname