LEFT OUTER JOIN
(
    SELECT TOP 100 PERCENT
    ...
    FROM
    ( 
        SELECT 
        ...
        FROM 
       (
           SELECT 1 Dummy
       ) Dual

ALTER PROCEDURE dbo.prc_rpt_exec_LienWaiver
(
	-- Uses IIS-supplied domain\username to determine data access priviledges.
	@UserLogin as varchar(60) 
	, @OUnitCode_UserLogin as varchar(8)
	, @GroupNo as varchar(10) = 'ALL' -- AuditGroup No
	, @UnitNo as varchar(10) = 'ALL' -- AuditUnitNo
	, @Supplier as varchar(20) = 'ALL' -- Address code
	, @CheckNo as varchar(10) = 'ALL' -- AuditUnitNo
	, @DateStart as varchar(10) = '1/1/1900'
)
AS

BEGIN

SET @GroupNo = REPLICATE('0', 8 - LEN(LTRIM(RTRIM(@GroupNo)))) + LTRIM(RTRIM(@GroupNo))
SET @UnitNo = REPLICATE('0', 4 - LEN(LTRIM(RTRIM(@UnitNo)))) + LTRIM(RTRIM(@UnitNo)) 

DECLARE @TempLW TABLE
(
	LWID int IDENTITY(1,1) NOT NULL,
	[GroupNo] [varchar] (16) NULL ,
	[UnitNo] [varchar] (8) NULL ,
	[DocNumber] [varchar] (16) NULL ,
	[IssueDate] [smalldatetime] NULL ,
	[CheckAmount] [decimal] (17,2) NULL,
	[Supplier] [int] NULL ,
	[OperUnit] [varchar] (10) NULL ,
	[Project] [varchar] (16) NULL ,
	[ProjectName] [varchar] (60) NULL ,
	[Category] [varchar] (30) NULL ,
	[CategoryDesc] [varchar] (60) NULL ,
	[MajorCode] [varchar] (30) NULL ,
	[MajorDesc] [varchar] (60) NULL ,
	[Phase] [varchar] (10) NULL ,
	[PONo] [varchar] (12) NULL ,
	[POStatus] [varchar] (50) NOT NULL, 
	[InvDate] [smalldatetime] NULL ,
	[InvNo] [varchar] (30) NULL ,
	[InvLine] [varchar] (8) NOT NULL,
	[Amount] [decimal](17, 2) NULL ,
	[Adjustments] [decimal](17, 2) NULL ,
	[NetAmount] [decimal](18, 2) NULL ,
	[RefAlloc] [varchar] (68) NULL ,
	[POID] [int] NULL ,
	[POLineNo] [varchar] (8) NULL ,
	[CraftCode] [varchar] (16) NULL ,
	[Craft6] [varchar] (6) NULL ,
	[Craft7] [varchar] (1) NULL ,
	[PayCode] [varchar] (30) NULL 
)
Primary Key definition essential

INSERT @TempLW
(
	GroupNo
	, UnitNo
	, DocNumber
	, IssueDate
	, CheckAmount
	, Supplier
	, OperUnit
	, Project
	, ProjectName
	, Category
	, CategoryDesc
	, MajorCode
	, MajorDesc
	, Phase
	, PONo
	, POStatus
	, InvDate
	, InvNo
	, InvLine
	, Amount
	, Adjustments
	, NetAmount
	, RefAlloc
	, POID
	, POLineNo
	, CraftCode
	, Craft6
	, Craft7
	, PayCode
)
( 
I cannot see that this TOP 100 PERCENT is doing anything.  
The bracket is superfluous too
	SELECT TOP 100 PERCENT
		DocHist.GroupNo
		, DocHist.UnitNo
		, DocHist.DocNumber
		, DocHist.IssueDate
		, DocHist.Amount CheckAmount
		, DocHist.AID Supplier
Try to replace all these SplitPart functions with some better quality "normalised" data
		, LEFT(IRIS.dbo.SplitPart(APItem.RefAlloc,',',1),10) OperUnit
		, IRIS.dbo.SplitPart(APItem.RefAlloc,',',2) Project
		, P.Name ProjectName
		, RAJTM.Category
		, RAJTMS.Descr CategoryDesc
		, RAJTM.MajorCode
		, RAJTM.Descr MajorDesc
		, IRIS.dbo.SplitPart(APItem.RefAlloc,',',3) Phase
		, PUHeader.PONo 
		, PUHeader.WFCode POStatus
		, APHeader.InvDate
		, APHeader.InvNo
		, APDetail.[LineNo] InvLine
		, - APDetail.Amount Amount
		, APDetail.DiscAmt Adjustments
		, - APDetail.Amount - APDetail.DiscAmt NetAmount
		, APItem.RefAlloc 
		, PUHeader.POID
		, PUDetailI.[LineNo] POLineNo
There are several occurences of things that convert blanks to null
Replace them all with NullIf{PUItem.CraftCode, '')
		, (case when PUItem.CraftCode = '' then null 
			else PUItem.CraftCode 
			end) CraftCode
		, LEFT((case when PUItem.CraftCode = '' then null 
				else PUItem.CraftCode 
				end) ,6) Craft6
		, SUBSTRING((case when PUItem.CraftCode = '' then null 
				else PUItem.CraftCode 
				end), 7, 1) Craft7
		, (case when PUItem.PayCode = '' then null 
			else PUItem.PayCode 
			end) PayCode
	FROM 
I cannot for the life of me figure out what the point of this dummy table is
	(
		SELECT 1 Dummy
	) Dual
These cartesian joins, if the parmeter is "ALL", will potentially result 
in some big resultsets
		INNER JOIN DataMartFL.dbo.AuditGroup AuditGroup 
			ON @GroupNo = '00000ALL' 
			OR AuditGroup.GroupNo = @GroupNo
		INNER JOIN DataMartFL.dbo.APDetail APDetail 
			ON APDetail.GroupNo = AuditGroup.GroupNo
			AND (@UnitNo = '0ALL' OR APDetail.UnitNo = @UnitNo)
		INNER JOIN DataMartFL.dbo.DocHist DocHist 
			 ON DocHist.GroupNo = APDetail.GroupNo
			AND DocHist.UnitNo = APDetail.UnitNo
			AND DocHist.Good = 1 
			AND (@CheckNo = 'All' OR DocHist.DocNumber = @CheckNo)
			AND (
The @DateStart should be coverted [at the top of this SProc] to a separate @Variable 
which has a datetime datatype
				   DocHist.IssueDate = CONVERT(datetime, @DateStart)
I would put good money on these two ORs defeating the optimiser, 
if either of these ALL tests are true I would do something with @DateStart parameter 
[at top of SProc] to satify this clause
				OR @GroupNo<>'ALL'
				OR @CheckNo<>'ALL'
			)
		INNER JOIN DataMartFL.dbo.APHeader APHeader 
			 ON APDetail.CompCode = APHeader.CompCode 
			AND APDetail.Voucher = APHeader.Voucher 
			AND APHeader.Invoice = 1
			AND APHeader.AID = DocHist.AID
		INNER JOIN DataMartFL.dbo.APItem APItem 
			 ON APDetail.CompCode = APItem.CompCode 
			AND APDetail.Voucher = APItem.Voucher 
			AND APDetail.[LineNo] = APItem.[LineNo] 
Need to avoid manipulation of joined table columns as it will prevent index selection
APItem.POID = NULL is going to fail this test anyway, isn't it? if so just use
AND APItem.POID > ''
			AND ISNULL(APItem.POID, '') > ''
		LEFT OUTER JOIN DataMartFL.dbo.JobTypeMajor RAJTM 
This will defeat the optimisers index selection too I expect
APItem.RefAlloc LIKE 'H%'
may be better
			 ON LEFT(APItem.RefAlloc, 1) = 'H' 
			AND RAJTM.MajorCode= IRIS.dbo.SplitPart(APItem.RefAlloc,',',4) 
			AND RAJTM.JobType = dbo.udf_JobType_ProjLot
These are scary beyond belief!  
Create a data source that can provide the componenets of APItem.RefAlloc without run-time splitting
					(
						  IRIS.dbo.SplitPart(APItem.RefAlloc,',',2) 
						, IRIS.dbo.SplitPart(APItem.RefAlloc,',',3)
					)
		LEFT OUTER JOIN DataMartFL.dbo.JobTypeMajor RAJTMS 
Ditto
			 ON LEFT(APItem.RefAlloc,1) = 'H' 
			AND RAJTMS.MajorCode=RAJTM.Category
			AND RAJTMS.JobType = RAJTM.JobType
		INNER JOIN DataMartFL.dbo.PUHeader PUHeader 
			ON APItem.POID = PUHeader.POID 
		LEFT OUTER JOIN DataMartFL.dbo.Project P
Ditto
			 ON P.ProjCode = IRIS.dbo.SplitPart(APItem.RefAlloc,',',2)
--			AND dbo.udf_OperUnitPermMatch (@OUnitCode_UserLogin,P.OUnitCode,1)=1
		INNER JOIN DataMartFL..PUDetail PUDetailI
			 ON PUDetailI.TransType = 'I'
			AND PUDetailI.POID = PUHeader.POID
			AND PUDetailI.APLineNo = APDetail.[LineNo] 
Ditto
			AND IRIS.dbo.SplitPart(PUDetailI.GUT,',',0) = APItem.GroupNo
			AND IRIS.dbo.SplitPart(PUDetailI.GUT,',',1) = APItem.UnitNo
		INNER JOIN DataMartFL..PUItem PUItem 
			ON PUItem.POID = PUDetailI.POID
			AND PUItem.[LineNo] = PUDetailI.[LineNo]
)
Really bad idea to use column numbers.  Are they ALL needed for a unique sort order?
This is just inserted in a temporary table, is the order being relied on? (Another bad idea!)
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18

I would put a SELECT COUNT(*) FROM @TempLW here to see how many rows are in the
temp table, compared to the number of results rows you get.

SELECT 
Name all the columns, otherwise the query plan is unlikely to be cached
	TempLW.* 
	, sub2.MaxCraft7
	, sub2.MaxPayCode
	, CONVERT(bit,
This BIT conversion is potentially going to add up three 1s - what is that supposed to convert to?
			(
			   case when len(TempLW.CraftCode)=6 
I do not like the Coalesce to 9999 and 0 as a mean of "not matching" 
- is that going to work safely with real values of these two columns
					and isnull(TempLW.PayCode,99999) >= isnull(sub2.MaxPayCode,0) 
				then 1 
				else 0
				end
			) 
			+ (
			   case when len(TempLW.CraftCode)=7
					and TempLW.Craft7 = sub2.MaxCraft7
				then 1
				else 0
				end
			) 
			+ (
			   case when isnull(TempLW.CraftCode,'') = ''
					and TempLW.POStatus = 'Closed'
				then 1
				else 0
				end
			)
		) FinalABC
	, JPA.StreetNo + ' ' + JPA.Street LotStreetAddr
What are these text strings "converted" to varchar??
	, convert(varchar(60),
			(
I would replace these with IsNull(NullIf(ProjLegal.LegalName, ''), 'CORP NAME NOT FOUND')
Is it really possible for the column to contain EITHER an empty string OR a NULL?  
I would clean up the data so its one or the other
			case when ProjLegal.LegalName>'' then ProjLegal.LegalName 
				else 'CORP NAME NOT FOUND' 
			end)
		) ProjCorp
Ditto
	, convert(varchar(50),
			(case when ProjLegal.ProvName>'' then ProjLegal.ProvName 
				else 'STATE NAME NOT FOUND' 
			end)
		) ProjStateName
Ditto
	, convert(varchar(5),
			(case when ProjLegal.ProvCode>'' then ProjLegal.ProvCode 
				else 'STATE CODE NOT FOUND'
			end)
		) ProjStateCode
Ditto
	, convert(varchar(30),
			(case when ProjLegal.County>'' then ProjLegal.County 
				else 'COUNTY NAME NOT FOUND' 
				end)
		) ProjCounty
Ditto
	, convert(varchar(50),
			(case when ProjLegal.CityName>'' then ProjLegal.CityName 
				else 'CITY NAME NOT FOUND' 
				end)
			) ProjSiteCity
CONVERT seems pointless - use LEFT() if you need to constrain length
	, convert(varchar(60), P.Name) ProjName
	, convert(varchar(60), PA.Street) ProjStreet
	, PA.Suite ProjSuite
	, convert(varchar(50), PC.CityName) ProjCity
	, PC.Provcode ProjState
	, PA.Postal ProjPostal
	, PayeeAddr.Code PayeeCode
	, convert(varchar(40), PayeeAddr.Name) PayeeName
	, convert(varchar(60), PayeeAddr.Street) PayeeStreet
	, convert(varchar(20), PayeeAddr.Suite) PayeeSuite
	, convert(varchar(50), PayeeC.CityName) PayeeCity
	, PayeeC.ProvCode PayeeState
	, PayeeAddr.Postal PayeePostal
FROM	@TempLW TempLW
Personally I would replace the ".." with ".dbo."
	LEFT OUTER JOIN DataMartFL..Project P
		ON P.ProjCode = TempLW.Project
	LEFT OUTER JOIN DataMartFL..Address PA 
		ON P.Aid = PA.Aid
	LEFT OUTER JOIN DataMartFL..City PC
		ON PC.Citycode = PA.CityCode
	LEFT OUTER JOIN DataMartFL..JobPhaseAddr JPA
		 ON JPA.ProjID = P.ProjID
		AND JPA.Phase = TempLW.Phase
	LEFT OUTER JOIN DataMartFL.dbo.Address PayeeAddr 
		ON PayeeAddr.AID = TempLW.Supplier
	LEFT OUTER JOIN DataMartFL..City PayeeC
		ON PayeeC.Citycode = PayeeAddr.CityCode
	LEFT OUTER JOIN 
	(
		-- ProjLegal
		SELECT 
			Project.ProjCode
			, Project.Name 
			, Address.Name LegalName
			, Province.Name ProvName
			, Province.ProvCode
			, dbo.SplitPart(Project.userdef,';',0) County
So, we are doing all this SUB SELECT work just to get the MAX CityName?
What real world use is the MAX(CityName)? Sounds utterly confusing to me
			, MAX(CityName) CityName
		FROM	DataMartFL..Project Project
All this complex JOINery is repeated here just to get a lookup list.  
I expect it would be much better to pre-prepare this and JOIN it direct
			LEFT OUTER JOIN DataMartFL..APCat APCat 
				ON Project.CompCode = APCat.CompCode
			LEFT OUTER JOIN DataMartFL..Company C 
				ON Project.CompCode = C.CompCode
			LEFT OUTER JOIN DataMartFL..BankAcct BankAcct 
LEFT may be faster here.  More scary delimited data
				ON substring(APCat.BankCode,1, 
						charindex(',',APCat.BankCode+',')-1) 
					= BankAcct.BankCode
			LEFT OUTER JOIN DataMartFL..Address Address 
				ON BankAcct.CAID = Address.AID
			LEFT OUTER JOIN DataMartFL..JobPhaseAddr JobPhaseAddr 
				ON JobPhaseAddr.ProjID = Project.ProjID
			LEFT OUTER JOIN DataMartFL..City City 
				ON JobPhaseAddr.CityCode = City.CityCode
			LEFT OUTER JOIN DataMartFL..Province Province 
				ON Province.ProvCode = City.ProvCode
		GROUP BY
			Project.ProjCode
			, Project.Name 
			, Address.Name 
			, Province.Name
			, Province.ProvCode
			, dbo.SplitPart(Project.userdef,';',0) 
	) ProjLegal ON ProjLegal.ProjCode = P.ProjCode
	LEFT OUTER JOIN 
	(
		-- sub2
Is there some reason for this TOP 100 PERCENT?  
It must be in order to ORDER this SUB SELECT, but I can not see what that achieves
		SELECT TOP 100 PERCENT
More scary splitting
			IRIS.dbo.SplitPart(OUnitProjectPhase,',',0) OperUnit
			, IRIS.dbo.SplitPart(OUnitProjectPhase,',',1) Project
			, IRIS.dbo.SplitPart(OUnitProjectPhase,',',2) Phase
			, Supplier
			, Craft6
			, MaxCraft7
			, MaxPayCode
		FROM
		( 
			SELECT 
The three parts of Alloc are split here, and then re-combined, 
to then be re-split in the outer SELECT; Why?
				IRIS.dbo.SplitPart(Alloc,',',1) 
					+ ',' + IRIS.dbo.SplitPart(Alloc,',',2) 
					+ ',' + IRIS.dbo.SplitPart(Alloc,',',3) OUnitProjectPhase
				, PUHeader.AID Supplier
This LEFT/SUBSTRING stuff is a bit scary too 
- does PUItem.CraftCode holds data for two separate things?
				, LEFT(PUItem.CraftCode,6) Craft6
				, MAX(substring(PUItem.CraftCode,7,1)) MaxCraft7
				, MAX(PUItem.PayCode) MaxPayCode
			FROM 
			(
Pointless?
				SELECT 1 Dummy
			) Dual
			INNER JOIN @TempLW TempLW
				ON TempLW.CraftCode > ''
			INNER JOIN DataMartFL..PUHeader PUHeader
				ON PUHeader.AID = TempLW.Supplier
			INNER JOIN DataMartFL..PUItem PUItem 
				 ON PUItem.POID = PUHeader.POID
				AND LEFT(PUItem.CraftCode,6) = TempLW.Craft6
Now this splitting is REALLY scary - unless the Optimiser is able to spot that the same
effort is happening gazzilions of times, which I doubt
Clearly PUItem.Alloc should be held in a number fo actual columns, but failing that it should
be split in a SUB SELECT
	AND TempLW.OperUnit = substring(PUItem.Alloc, 
					1+charindex(',',PUItem.Alloc), 
						1+charindex(',',PUItem.Alloc,
							1+charindex(',',PUItem.Alloc)
						)-(1+charindex(',',PUItem.Alloc))-1
					)
	AND TempLW.Project = substring(PUItem.Alloc, 
					1+charindex(',',PUItem.Alloc,
						1+charindex(',',PUItem.Alloc)
					), 1+charindex(',',PUItem.Alloc,
						1+charindex(',',PUItem.Alloc,
							1+charindex(',',PUItem.Alloc)
						)
					) - (1+charindex(',',PUItem.Alloc,
							1+charindex(',',PUItem.Alloc)
						)
					) -1 )
	AND TempLW.Phase = substring(PUItem.Alloc, 
					1+charindex(',', PUItem.Alloc,
						1+charindex(',',PUItem.Alloc,
							1+charindex(',',PUItem.Alloc)
						)
					), 1+charindex(',',PUItem.Alloc,
						1+charindex(',',PUItem.Alloc,
							1+charindex(',',PUItem.Alloc,
								1+charindex(',',PUItem.Alloc)
							)
						)
					) - (1+charindex(',',PUItem.Alloc,
							1+charindex(',',PUItem.Alloc,
								1+charindex(',',PUItem.Alloc)
							)
						)
					) -1 )
		GROUP BY
			IRIS.dbo.SplitPart(Alloc,',',1) 
				+ ',' + IRIS.dbo.SplitPart(Alloc,',',2) 
				+ ',' + IRIS.dbo.SplitPart(Alloc,',',3) 
			, PUHeader.AID
			, LEFT(PUItem.CraftCode,6) 
		) sub1
		ORDER BY
			OperUnit
			, Project
			, Phase 
			, Supplier 
			, Craft6
	) sub2 
		 ON sub2.OperUnit = TempLW.OperUnit
		AND sub2.Project = TempLW.Project
		AND sub2.Phase = TempLW.Phase
		AND sub2.Supplier = TempLW.Supplier
		AND sub2.Craft6 = TempLW.Craft6
I can't see the optimiser having trouble with "0=0", but I would n't chance it!
	WHERE	    (0=0)
		AND (
			   @Supplier = 'ALL' 
			OR PayeeAddr.Code = @Supplier
		)
Why the GROUP BY?  I did not see any aggregate functions in the SELECT.
So is this just a DISTINCT?
Is so I would be VERY scared that this is just to get rid of all the cartesian JOINs.
Try running with a COUNT(*) instead of the select and see how many rows were aggregated
If its gazzilions then thats probably the primary source of the performance issues 
- erroneously JOINing data multiple times.
GROUP BY
	LWID 
	, TempLW.GroupNo
	, TempLW.UnitNo
	, TempLW.DocNumber
	, TempLW.IssueDate
	, TempLW.CheckAmount
	, TempLW.Supplier
	, TempLW.OperUnit
	, TempLW.Project
	, TempLW.ProjectName 
	, TempLW.Category
	, TempLW.CategoryDesc
	, TempLW.MajorDesc
	, TempLW.MajorCode
	, TempLW.MajorDesc
	, TempLW.Phase
	, TempLW.PONo 
	, TempLW.POStatus
	, TempLW.InvDate
	, TempLW.InvNo
	, TempLW.InvLine
	, TempLW.Amount
	, TempLW.Adjustments
	, TempLW.NetAmount
	, TempLW.POID
	, TempLW.RefAlloc 
	, TempLW.POLineNo
	, TempLW.CraftCode
	, TempLW.Craft6
	, TempLW.Craft7
	, TempLW.PayCode
	, sub2.Craft6
	, sub2.MaxCraft7
	, sub2.MaxPayCode
	, JPA.Street
	, JPA.StreetNo
	, ProjLegal.LegalName 
	, ProjLegal.ProvName 
	, ProjLegal.ProvCode
	, ProjLegal.County
	, ProjLegal.CityName
	, P.Name
	, PA.Street
	, PA.Suite
	, PC.CityName 
	, PC.Provcode 
	, PA.Postal
	, PayeeAddr.Code 
	, PayeeAddr.Name
	, PayeeAddr.Street
	, PayeeAddr.Suite 
	, PayeeC.CityName
	, PayeeC.ProvCode 
	, PayeeAddr.Postal 
END