USE [LegitimateNational]
GO
/****** Object:  StoredProcedure [App].[Pr_ApplicationsAwaitingInspectionsGet]    Script Date: 2026/05/22 16:01:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [App].[Pr_ApplicationsAwaitingInspectionsGet]
(
	@UserID BIGINT
)
AS
BEGIN
	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1000 /*Application*/
	DECLARE @ItemIDToCheck BIGINT = NULL
	DECLARE @Action VARCHAR(255) = 'Search'
	DECLARE @OfficeLocationID BIGINT, @DivisionTypeID SMALLINT, @ProvinceID SMALLINT, @RegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @OfficeLocationID OUTPUT, @DivisionTypeID OUTPUT, @ProvinceID OUTPUT, @RegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	
	
	/********************************************************************************************
	Perform search
	*********************************************************************************************/	
	SELECT I.*, AD.ApplicationNumber AS ReferenceNumber
	FROM App.ApplicationInspectionDetails I
		INNER JOIN App.ApplicationDetails AD ON AD.ApplicationDetailID = I.ApplicationDetailID
		INNER JOIN Wf.WorkflowInstances W ON W.WorkflowInstanceID = AD.WorkflowInstanceID
		INNER JOIN Wf.WorkflowInstanceTasks WT ON WT.WorkflowInstanceID = W.WorkflowInstanceID
	WHERE AD.OfficeLocationID = @OfficeLocationID
	AND InspectionStatusID = 1 /*Awaiting inspection date*/
	AND InspectorID IS NULL
	AND WT.WorkflowInstanceTaskStatusID = 1
	AND WT.WorkflowTemplateStepID = 1010

END
