Functions causing Serial plan.What to use instead? I have few stored procedures in my SQL Server database.I see that these stored procedures are running in serial mode from their execution plan.
For example :

CREATE PROCEDURE [dbo].[DemoProc]
    @Arg1 VARCHAR(20),
    @Arg2 varchar(9) = NULL
AS
SELECT
    r.Plant_Id AS [Plant_Id],
    @LNumber AS [Incident Number],
    COALESCE(idps.AlternateNumber, '') AS [tblFormNumber],
    CASE l.[UserName]
        WHEN NULL THEN 'OPEN'
        WHEN '' THEN 'UNLOCKED'
        ELSE l.UserName
        END AS UserName,
    l.UserId,
    l.Lock LockCode,
    l.LockTime
FROM
    dbo.tblDetailPages idp WITH (NOLOCK)
        INNER JOIN tblForm f WITH (NOLOCK)
            ON idp.tblFormId = f.Id
        INNER JOIN tblReport r WITH (NOLOCK)
            ON f.tblReportId = r.Id
        LEFT OUTER JOIN dbo.tblDetailsPage_Alternate idps WITH (NOLOCK)
            ON idp.Id = idps.PageId
        LEFT OUTER JOIN [dbo].[Monitor] l  WITH (NOLOCK) 
            ON l.LNumber = SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)
            AND l.Plant_Id = r.Plant_Id
WHERE
    SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber AND
    r.Plant_Id LIKE CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber)) END
ORDER BY [tblFormNumber]
OPTION (MAXDOP 6)
GO


In this stored procedure,i have a function in the where condition 

 WHERE --- 
AND r.Plant_Id LIKE CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber))


dbo.GetPlant_Id(@LNumber) is a function 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetPlant_Id]
(
    @LNumber varchar(50)
)
RETURNS VARCHAR(25)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM tblReport r WHERE (SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)) = @LNumber HAVING COUNT(r.LNumber) &gt; 1)
    BEGIN
        RETURN cast('Duplicate Incident number found :' + @LNumber as int)
    END

    RETURN
        (SELECT TOP 1 AgencyOri FROM tblReport r WHERE SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber)
END

GO


How do i avoid using functions.?I tried to convert to inline function,but since it is a big function,i cannot put it as an inline function.
To make my question clear,

I was asking how to avoid using function like i showed in the question or is there any way i can change it to improve the performance.?
A scalar UDF only forces a serial plan for the query that calls it. You can have a scalar UDF call in a stored procedure that also has queries eligible for parallelism as long as the queries don't call the function. Your function in this example is a constant so you can just store it in a local variable. I had to make a guess about the data type but something like the following should meet your needs:

CREATE PROCEDURE [dbo].[DemoProc]
    @Arg1 VARCHAR(20),
    @Arg2 varchar(9) = NULL
AS
DECLARE @Plant_Id_Filter VARCHAR(100);

SET @Plant_Id_Filter = CASE WHEN @Plant_Id = 'ALL' THEN '%' ELSE COALESCE(@Plant_Id, dbo.GetPlant_Id(@LNumber)) END;

SELECT
    r.Plant_Id AS [Plant_Id],
    @LNumber AS [Incident Number],
    COALESCE(idps.AlternateNumber, '') AS [tblFormNumber],
    CASE l.[UserName]
        WHEN NULL THEN 'OPEN'
        WHEN '' THEN 'UNLOCKED'
        ELSE l.UserName
        END AS UserName,
    l.UserId,
    l.Lock LockCode,
    l.LockTime
FROM
    dbo.tblDetailPages idp WITH (NOLOCK)
        INNER JOIN tblForm f WITH (NOLOCK)
            ON idp.tblFormId = f.Id
        INNER JOIN tblReport r WITH (NOLOCK)
            ON f.tblReportId = r.Id
        LEFT OUTER JOIN dbo.tblDetailsPage_Alternate idps WITH (NOLOCK)
            ON idp.Id = idps.PageId
        LEFT OUTER JOIN [dbo].[Monitor] l  WITH (NOLOCK) 
            ON l.LNumber = SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2)
            AND l.Plant_Id = r.Plant_Id
WHERE
    SUBSTRING(r.LNumber, 5, 3) + '-' + SUBSTRING(r.LNumber, 8, 7) + '-' + SUBSTRING(r.LNumber, 3, 2) = @LNumber AND
    r.Plant_Id LIKE @Plant_Id_Filter
ORDER BY [tblFormNumber]
OPTION (MAXDOP 6)
GO