#Is Load Day?#

Your IsLoadDay function seems a little overcomplicated. Might I suggest trying something along the lines of this, which brings all the checks you want to do pretty nicely into a single coalesce:

CREATE FUNCTION [dbo].[IsLoadDay](@Dt DATE)
RETURNS BIT
AS
BEGIN

RETURN COALESCE
(
    --If it is a monday or sunday, it's not a load day
    CASE WHEN DATENAME(DW, @Dt) IN ('SUNDAY', 'MONDAY') THEN 0 END,

    --If it is a designated not load day, it's not a load day
    (
        SELECT 0 
        FROM [DBMGMT].[dbo].[afrs_daily_load_no_load_date]
        WHERE [load_date] = @Dt AND [load_ind] = 'N'
    ),
    
    --If it is a holiday, it's not a load day
    (
        SELECT 0
        FROM [master].[dbo].[HOLDY_WA_STATE]
        WHERE [HOLDY_DATE] = @Dt
    ),

    --Otherwise, it's a load day
    1
)
END
GO

Is Load Day?

Your IsLoadDay function seems a little overcomplicated. Might I suggest trying something along the lines of this, which brings all the checks you want to do pretty nicely into a single coalesce:

CREATE FUNCTION [dbo].[IsLoadDay](@Dt DATE)
RETURNS BIT
AS
BEGIN

RETURN COALESCE
(
    --If it is a monday or sunday, it's not a load day
    CASE WHEN DATENAME(DW, @Dt) IN ('SUNDAY', 'MONDAY') THEN 0 END,

    --If it is a designated not load day, it's not a load day
    (
        SELECT 0 
        FROM [DBMGMT].[dbo].[afrs_daily_load_no_load_date]
        WHERE [load_date] = @Dt AND [load_ind] = 'N'
    ),
    
    --If it is a holiday, it's not a load day
    (
        SELECT 0
        FROM [master].[dbo].[HOLDY_WA_STATE]
        WHERE [HOLDY_DATE] = @Dt
    ),

    --Otherwise, it's a load day
    1
)
END
GO