Aras Innovator – Project 展 WBS

SELECT DISTINCT PROJECT.ID
, PROJECT.PROJECT_NUMBER, PROJECT.[NAME], PROJECT.DATE_START_SCHED, PROJECT.DATE_DUE_SCHED
, EMT_1_NAME=EMT_1.[NAME]
, EMT_2_NAME=EMT_2.[NAME]
, ACT_2_NAME=ACT_2.[NAME], ACT_2_DATE_START_SCHED=ACT_2.DATE_START_SCHED, ACT_2_DATE_START_ACT=ACT_2.DATE_START_ACT, ACT_2_DATE_DUE_SCHED=ACT_2.DATE_DUE_SCHED, ACT_2_DATE_DUE_ACT=ACT_2.DATE_DUE_ACT
, EMT_3_NAME=EMT_3.[NAME]
, ACT_3_NAME=ACT_3.[NAME], ACT_3_DATE_START_SCHED=ACT_3.DATE_START_SCHED, ACT_3_DATE_START_ACT=ACT_3.DATE_START_ACT, ACT_3_DATE_DUE_SCHED=ACT_3.DATE_DUE_SCHED, ACT_3_DATE_DUE_ACT=ACT_3.DATE_DUE_ACT
, EMT_4_NAME=EMT_4.[NAME]
, ACT_4_NAME=ACT_4.[NAME], ACT_4_DATE_START_SCHED=ACT_4.DATE_START_SCHED, ACT_4_DATE_START_ACT=ACT_4.DATE_START_ACT, ACT_4_DATE_DUE_SCHED=ACT_4.DATE_DUE_SCHED, ACT_4_DATE_DUE_ACT=ACT_4.DATE_DUE_ACT
FROM PROJECT
INNER JOIN WBS_ELEMENT EMT_1 ON EMT_1.ID=PROJECT.WBS_ID    -- Level 1 item
-- WBS level 1 do not have activity items
--LEFT JOIN WBS_ACTIVITY2 WA_1 ON WA_1.SOURCE_ID=EMT_1.ID
--LEFT JOIN ACTIVITY2 ACT_1 ON ACT_1.ID=WA_1.RELATED_ID
LEFT JOIN SUB_WBS SW_1 ON SW_1.SOURCE_ID=EMT_1.ID
LEFT JOIN WBS_ELEMENT EMT_2 ON EMT_2.ID=SW_1.RELATED_ID    -- Level 2 item
LEFT JOIN WBS_ACTIVITY2 WA_2 ON WA_2.SOURCE_ID=EMT_2.ID
LEFT JOIN ACTIVITY2 ACT_2 ON ACT_2.ID=WA_2.RELATED_ID
LEFT JOIN SUB_WBS SW_2 ON SW_2.SOURCE_ID=EMT_2.ID
LEFT JOIN WBS_ELEMENT EMT_3 ON EMT_3.ID=SW_2.RELATED_ID    -- Level 3 item
LEFT JOIN WBS_ACTIVITY2 WA_3 ON WA_3.SOURCE_ID=EMT_3.ID
LEFT JOIN ACTIVITY2 ACT_3 ON ACT_3.ID=WA_3.RELATED_ID
LEFT JOIN SUB_WBS SW_3 ON SW_3.SOURCE_ID=EMT_3.ID
LEFT JOIN WBS_ELEMENT EMT_4 ON EMT_4.ID=SW_3.RELATED_ID    -- Level 4 item
LEFT JOIN WBS_ACTIVITY2 WA_4 ON WA_4.SOURCE_ID=EMT_4.ID
LEFT JOIN ACTIVITY2 ACT_4 ON ACT_4.ID=WA_4.RELATED_ID
LEFT JOIN SUB_WBS SW_4 ON SW_4.SOURCE_ID=EMT_4.ID
WHERE PROJECT.ID=?