SQL Help - All parts linked to Projects

Epicor ERP 10
,
1

I’m trying to get all parts linked to projects. Instead of doing a indented bom of sales order parts and using the project ID linked to the sales order im just getting 1 level in on the PartMtl and union with Jobhead and jobmtl .
The result is a list of PartNums and Project ID. some parts can be in multiple projects. I want to take this list and make a 2 column temp table as a distinct list of partnum with a string of all the projectIDs.
Whats the best way to do this?

BEGIN TRY
			DROP TABLE #ProjectParts;
		END TRY

		BEGIN CATCH
		END CATCH

select distinct 
	od.PartNum
	,project.ProjectID
into #ProjectParts
from dbo.Project
inner join OrderDtl OD on
OD.ProjectID = project.ProjectID

union 

select distinct
 
 	pm.MtlPartNum as Partnum
	,project.ProjectID

from dbo.Project
inner join OrderDtl OD on
OD.ProjectID = project.ProjectID
inner join erp.partmtl pm on
pm.PartNum = od.partnum 



union 
select distinct 
jh.partnum
,jh.ProjectID
from jobhead jh


union 
select distinct
jm.partnum
,jh.projectid
from erp.JobMtl jm
inner join jobhead jh on
jh.jobnum = jm.jobnum
where jh.ProjectID != ' '
2

I would try a select distinct or group by on the partnum table and join it to a subquery with ()for xml path with the project id’s.

1 Like