IF NOT EXISTS (SELECT TOP 1 1 FROM [Verified].[PrintoutTypes] WHERE PrintoutTypeID = 7)
BEGIN
	INSERT INTO [Verified].[PrintoutTypes](PrintoutTypeID, [Description], IsCancelled)
	VALUES(7,'Annexure', 0)
END
GO

CREATE OR ALTER FUNCTION [App].[Fn_LicenceIssueStep4Checks]
(
	@LicenceIssueDetailID BIGINT,
	@UserID BIGINT
)
RETURNS @Outcomes TABLE(Title VARCHAR(500) NOT NULL, IsSuccessfull BIT NOT NULL, OutcomeDetails VARCHAR(2000) NULL, RequiredForSubmitting BIT NOT NULL)
AS
BEGIN
		
	DECLARE @LicenceID BIGINT
	
	SELECT @LicenceID = L.LicenceID		
	FROM App.LicenceIssueDetails Lid INNER JOIN Verified.Licences L 
	ON Lid.VerifiedLicenceID = L.LicenceID
	WHERE Lid.LicenceIssueDetailID = @LicenceIssueDetailID

	/****************************************************************************
	Certificate printing
	****************************************************************************/	
	IF EXISTS(SELECT TOP 1 1 
			FROM Verified.Licences
			WHERE LicenceID = @LicenceID AND (ISNULL(CertificateNumber,'') = '')) 
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the certificate been printed?', 0, '',1)			
	END
	ELSE
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the certificate been printed?', 1, '',1)			
	END

	/****************************************************************************
	Annexure printing
	****************************************************************************/	
	IF EXISTS(SELECT TOP 1 1 
			FROM Verified.Printouts
			WHERE ItemID = @LicenceID AND PrintoutTypeID = 7 AND PrintedSuccussfully = 1)
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the annexure been printed?', 1, '',1)			
	END
	ELSE
	BEGIN
		INSERT INTO @Outcomes(Title, IsSuccessfull, OutcomeDetails, RequiredForSubmitting)
		VALUES('Has the annexure been printed?', 0, '',1)			
	END

	RETURN
END
GO