T-Sql User Defined Function with Subquery Fails to Compile - Column Prefix error

I am using SQL Server 2000 and I have created a user defined function named TT_GET_MARKUP2.  The select statement of the function works in Microsoft's SQL Query Analyzer, but when I try to create the function using the same select statement the error messages "The column prefix 'ERN' does not match with a table name or alias name used in the query."  and "The column prefix "LINE" does not match with a table name or alias name used in the query."
are returned.

This is the SQL Statement that works when it is run outside of the function:
SELECT  
  ((ERN.REG_BILL_RATE - ERN.REG_PAY_RATE)/ERN.REG_PAY_RATE)*100
FROM
  PS_BI_HDR HDR WITH(NOLOCK)
  JOIN PS_BI_LINE AS LINE WITH(NOLOCK) ON
        HDR.BUSINESS_UNIT = LINE.BUSINESS_UNIT
    AND HDR.INVOICE = LINE.INVOICE
  JOIN PS_PROJ_ACTIVITY PRJACT WITH(NOLOCK) ON
        LINE.BUSINESS_UNIT = PRJACT.BUSINESS_UNIT
    AND LINE.PROJECT_ID = PRJACT.PROJECT_ID
    AND LINE.ACTIVITY_ID = PRJACT.ACTIVITY_ID
  JOIN FO880PRD.dbo.PS_FO_ASSIGNMENT FOASSN WITH(NOLOCK) ON
        PRJACT.PC_SCH_FIELD2 = FOASSN.ORDER_ID
    AND PRJACT.PC_SCH_FIELD1 = FOASSN.BUSINESS_UNIT
    AND PRJACT.PC_SCH_FIELD5 = FOASSN.FO_ASSIGNMENT_ID
  JOIN FO880PRD.dbo.PS_FO_ASGN_ERN_DTL ERN WITH(NOLOCK) ON
       ERN.BUSINESS_UNIT = FOASSN.BUSINESS_UNIT
   AND ERN.ORDER_ID = FOASSN.ORDER_ID
   AND ERN.FO_ASSIGNMENT_ID = FOASSN.FO_ASSIGNMENT_ID
   AND ERN.DETAIL_NO = 1
 WHERE
      HDR.INVOICE = '40175618' --@INVOICE in function
  AND LINE.EMPLID = '00091350' --@EMPLID in fucntion
  AND ERN.EFFDT = (SELECT MAX(B.EFFDT)
                   FROM FO880PRD.dbo.PS_FO_ASGN_ERN_DTL B WITH(NOLOCK)
                   WHERE B.BUSINESS_UNIT = ERN.BUSINESS_UNIT AND
                         B.FO_ASSIGNMENT_ID = ERN.FO_ASSIGNMENT_ID AND
                         B.ORDER_ID = ERN.ORDER_ID AND
                         B.DETAIL_NO = ERN.DETAIL_NO AND
                         B.EFFDT <= '2008-04-06 00:00:00.000' --LINE.CHARGE_FROM_DATE
                        )

This is the Function I am trying to compile using the above SQL statement:
CREATE FUNCTION TT_CALC_MARKUP2 (@INVOICE nvarchar(50), @EMPLID nvarchar(20))
RETURNS NUMERIC AS
BEGIN
     RETURN (
SELECT  
  ((ERN.REG_BILL_RATE - ERN.REG_PAY_RATE)/ERN.REG_PAY_RATE)*100
FROM
  PS_BI_HDR HDR WITH(NOLOCK)
  JOIN PS_BI_LINE LINE WITH(NOLOCK) ON
        HDR.BUSINESS_UNIT = LINE.BUSINESS_UNIT
    AND HDR.INVOICE = LINE.INVOICE
  JOIN PS_PROJ_ACTIVITY PRJACT WITH(NOLOCK) ON
        LINE.BUSINESS_UNIT = PRJACT.BUSINESS_UNIT
    AND LINE.PROJECT_ID = PRJACT.PROJECT_ID
    AND LINE.ACTIVITY_ID = PRJACT.ACTIVITY_ID
  JOIN FO880PRD.dbo.PS_FO_ASSIGNMENT FOASSN WITH(NOLOCK) ON
        PRJACT.PC_SCH_FIELD2 = FOASSN.ORDER_ID
    AND PRJACT.PC_SCH_FIELD1 = FOASSN.BUSINESS_UNIT
    AND PRJACT.PC_SCH_FIELD5 = FOASSN.FO_ASSIGNMENT_ID
  JOIN FO880PRD.dbo.PS_FO_ASGN_ERN_DTL ERN WITH(NOLOCK) ON
       ERN.BUSINESS_UNIT = FOASSN.BUSINESS_UNIT
   AND ERN.ORDER_ID = FOASSN.ORDER_ID
   AND ERN.FO_ASSIGNMENT_ID = FOASSN.FO_ASSIGNMENT_ID
   AND ERN.DETAIL_NO = 1
 WHERE
        HDR.INVOICE = @INVOICE
    AND LINE.EMPLID = @EMPLID
    AND ERN.EFFDT = (SELECT MAX(B.EFFDT)
                         FROM FO880PRD.dbo.PS_FO_ASGN_ERN_DTL B WITH(NOLOCK)
                         WHERE B.BUSINESS_UNIT = ERN.BUSINESS_UNIT AND
                               B.FO_ASSIGNMENT_ID = ERN.FO_ASSIGNMENT_ID AND
                               B.ORDER_ID = ERN.ORDER_ID AND
                               B.DETAIL_NO = ERN.DETAIL_NO AND
                               B.EFFDT <= LINE.CHARGE_FROM_DT
                        )
            )
END
GO
COMMIT

Can anyone explain how I should structure the select statement so that what appears to be a problem with the "scope" of the LINE and ERN aliases is resolved?Start Free Trial