/****** Object: StoredProcedure [arvy].[GetLocalDispatchByDateBranch] Script Date: 10/13/2023 11:30:00 AM ******/
CREATE PROCEDURE [arvy].[GetEquipmentByLocalService]
    @inLocalServiceID INT,
    @inPageNo INT,
    @RowsPerPage INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        Equip.PriKey AS EquipID,
        EqType.Type AS Type,
        Equip.EqNo AS EquipmentNumber,
        Equip.Descrip AS EquipmentDescription,
        Equip.Notes AS Detail,
        Branch.Name AS DefaultBranchName,
        Branch.BranchID,
        Branch.BranchPriKey,
        CASE WHEN Equip.EqStatus = 2 THEN 1 ELSE 0 END AS InShop,
        SafetySystemEquipmentStatus.ColorCode,
        ServEq.LSPriKey AS LocalServiceID
    FROM Equip
    INNER JOIN EqType ON EqType.TypeNumber = Equip.Type
    LEFT OUTER JOIN EquipBranches ON EquipBranches.EquipPriKey = Equip.PriKey AND EquipBranches.DefaultBranch = 1
    LEFT OUTER JOIN Branch ON EquipBranches.BranchPriKey = Branch.BranchPriKey
    LEFT OUTER JOIN SafetySystemEquipmentStatus ON Equip.PriKey = SafetySystemEquipmentStatus.EquipmentFID
    -- This next outer join is actually an inner join due to the one WHERE clause condition.
    LEFT OUTER JOIN ServEq ON Equip.PriKey = ServEq.EqPriKey
    WHERE ServEq.LSPriKey = @inLocalServiceID
    ORDER BY 1 ASC
    OFFSET (@inPageNo-1)*@RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY
    FOR XML RAW
END
GO
GRANT EXECUTE ON  [arvy].[GetEquipmentByLocalService] TO [MssExec]
GO