Create PROCEDURE [dbo].[usp_JRM_JR_History]
(
@accountID INT,
@entityID INT,
@jRID INT
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @TemplateID INT
SELECT @TemplateID=WorkflowTemplateID FROM JRM_JR_Info WHERE AccountID=@AccountID AND EntityID=@EntityID AND JRID=@jRID
SELECT *FROM (
SELECT JJF.JRID AS JRID,DateAdd(hour,8,JJF.CreatedDate) AS ActionDate,RequestName AS HandledBy,dbo.UDF_JRM_JR_GetActionStatusName(JJF.ActionID) AS ActionStatus,dbo.UDF_JRM_JR_GetStageName(@accountID,@entityID,JWD.JRStageID) AS StageID
FROM JRM_JR_ForwardDetails JJF
LEFT JOIN JRM_WorkflowTemplate_Details JWD
ON JJF.AccountID=JWD.AccountID AND JJF.EntityID=JWD.EntityID AND JWD.TemplateID=@TemplateID AND JJF.JRID=@JRID AND JJF.StepID=JWD.StepID
WHERE JJF.AccountID=@AccountID AND JJF.EntityID=@EntityID AND JJF.JRID=@JRID
UNION ALL
SELECT JRID,DateAdd(hour,8,ISNULL(FinalApproveDate,LastModifiedDate))AS ActionDate,FinalApproverName AS HandledBy,dbo.UDF_JRM_JR_GetActionStatusName(5) AS ActionStatus,dbo.UDF_JRM_JR_GetStageName(@accountID,@entityiD,StageID) AS StageID
FROM JRM_JR_Info WITH (NOLOCK) WHERE AccountID=@accountID AND EntityID=@entityID AND JRID=@jRID AND ISNULL(FinalApproverID,0) <>0
UNION ALL
SELECT JRID,DateAdd(hour,8,ISNULL(TransferDate,LastModifiedDate))AS ActionDate,TransferorName AS HandledBy,dbo.UDF_JRM_JR_GetActionStatusName(12)AS ActionStatus,dbo.UDF_JRM_JR_GetStageName(@accountID,@entityID,StageID)AS StageID
FROM JRM_JR_Info WITH(NOLOCK) WHERE AccountID=@accountID AND EntityID=@entityID AND JRID=@jRID AND ISNULL(TransferorID,0) <> 0) AS History
Order by History.ActionDate ASC
END