﻿CREATE PROCEDURE [employer_account].[GetEmployerAgreementsLinkedToAccount]
	@accountId BIGINT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT 
	ea.Id,
	ea.StatusId AS [Status],
	ea.LegalEntityId, 
	le.Code as LegalEntityCode,
	le.Name AS LegalEntityName,
	ea.TemplateId,
	eat.PartialViewName AS TemplatePartialViewName,
	ea.SignedByName,
	ea.SignedDate
	FROM [employer_account].[LegalEntity] le
	JOIN [employer_account].[EmployerAgreement] ea ON ea.LegalEntityId = le.Id
	JOIN [employer_account].[AccountEmployerAgreement] aea ON aea.[EmployerAgreementId] = ea.Id
	JOIN [employer_account].[EmployerAgreementTemplate] eat ON eat.Id = ea.TemplateId
	WHERE aea.AccountId = @accountId
END

﻿CREATE PROCEDURE [employer_account].[GetEmployerAgreementsLinkedToAccount]
	@accountId BIGINT
AS
BEGIN
	SET NOCOUNT ON;

	SELECT 
	ea.Id,
	ea.StatusId AS [Status],
	ea.LegalEntityId, 
	le.Code as LegalEntityCode,
	le.Name AS LegalEntityName,
	ea.TemplateId,
	eat.PartialViewName AS TemplatePartialViewName,
	ea.SignedByName,
	ea.SignedDate
	FROM [employer_account].[LegalEntity] le
	JOIN [employer_account].[EmployerAgreement] ea ON ea.LegalEntityId = le.Id
	JOIN [employer_account].[AccountEmployerAgreement] aea ON aea.[EmployerAgreementId] = ea.Id
	JOIN [employer_account].[EmployerAgreementTemplate] eat ON eat.Id = ea.TemplateId
	WHERE aea.AccountId = @accountId
	and ea.StatusId <> 5
END

Update get agreements procedure to ignore removed
Update get agreements procedure to ignore removed
