CREATE PROC Proc_GetCRFActivityStatusReport_BABE @vWorkSpaceId as varchar(1000), @iPeriod as varchar(10)=null, @vSubjectId as varchar(1000)=null, @iParentActivityNodeId as varchar(1000)=null, @iActivityNodeId as varchar(1000)=null , @cSubjectWiseFlag as varchar(5)=null, @cDataStatus as varchar(10)=null, @iWorkFlowStageId as varchar(10)=null AS BEGIN IF @cDataStatus=',E,' BEGIN SET @cDataStatus='' END --------------------------------------------- SELECT DISTINCT vMySubjectNo = CASE WHEN ActivityMst.cSubjectWiseFlag='N' THEN '0000' ELSE WorkSpaceSubjectMst.vMySubjectNo END, parent.vWorkSpaceId, parent.iNodeId AS parentid, parent.vNodeDisplayName AS parent, child.iNodeId as childid, child = CASE WHEN ISNULL(CRFInfo.iRepeatNo,1) = 1 THEN child.vNodeDisplayName ELSE child.vNodeDisplayName+'(Repeat-'+CONVERT(VARCHAR(50),CRFInfo.iRepeatNo)+')' END, STATUS = CASE WHEN CRFInfo.cDataStatus='F' AND CRFInfo.iWorkFlowstageId=30 THEN 'L' WHEN (CRFInfo.cDataStatus='E' Or CRFInfo.cDataStatus='D') and CRFInfo.iWorkFlowstageId=20 THEN 'FnlRP' WHEN (CRFInfo.cDataStatus='E' or CRFInfo.cDataStatus='D') and CRFInfo.iWorkFlowstageId=10 THEN 'SRP' WHEN CRFInfo.cDataStatus='D' and CRFInfo.iWorkFlowstageId=0 THEN 'FRP' WHEN CRFInfo.cDataStatus='B' and CRFInfo.iWorkFlowstageId=0 THEN 'DEC' WHEN CRFInfo.cDataStatus ='' THEN 'DEP' END, ISNULL(DCFMst.DCF,0) AS DCF, parent.iNodeNo, iMySubjectNo = CASE WHEN ActivityMst.cSubjectWiseFlag='Y' THEN WorkSpaceSubjectMst.iMySubjectNo WHEN ActivityMst.cSubjectWiseFlag='N' THEN 0 END, child.vActivityId, parent.iPeriod, vSubjectId = CASE WHEN ActivityMst.cSubjectWiseFlag='N' THEN '0000' ELSE WorkSpaceSubjectMst.vSubjectId END, ISNULL(WorkSpaceSubjectMst.cRejectionFlag,'') FROM WorkSpaceNodeDetail parent INNER JOIN WorkSpaceNodeDetail child ON parent.vWorkSpaceId=child.vWorkSpaceId AND (parent.iNodeId=child.iParentNodeId OR (parent.iNodeId=child.iNodeId and parent.iParentNodeId=1)) AND child.iPeriod=parent.iPeriod AND parent.cStatusIndi<>'D' AND child.cStatusIndi<>'D' INNER JOIN MedExWorkSpaceHdr ON MedExWorkSpaceHdr.vWorkspaceId=parent.vWorkSpaceId AND MedExWorkSpaceHdr.iNodeId=child.iNodeId INNER JOIN ActivityMst ON ActivityMst.vActivityId=child.vActivityId AND ActivityMst.cStatusIndi<>'D' LEFT JOIN WorkSpaceSubjectMst on child.vWorkSpaceId=WorkSpaceSubjectMst.vWorkspaceId AND WorkSpaceSubjectMst.iPeriod=child.iPeriod AND WorkSpaceSubjectMst.cStatusIndi <>'D' AND ActivityMst.cSubjectWiseFlag = 'Y' LEFT JOIN ( SELECT CRFHdr.iNodeId, CRFHdr.vWorkSpaceId, CRFDtl.vSubjectId, ActivityMst.cSubjectWiseFlag, CRFHdr.iPeriod, CRFDtl.iRepeatNo, CRFDtl.cDataStatus, CRFDtl.cStatusIndi, CRFDtl.iWorkFlowstageId, CRFDtl.nCRFDtlNo FROM CRFHdr INNER JOIN ActivityMst ON ActivityMst.vActivityId = CRFHdr.vActivityId AND ActivityMst.cStatusIndi<>'D' INNER JOIN CRFDtl ON CRFHdr.nCRFHdrNo=CRFDtl.nCRFHdrNo AND CRFDtl.cStatusIndi<>'D' AND CRFHdr.cStatusIndi<>'D' ) AS CRFInfo ON CRFInfo.iNodeId=child.iNodeId AND CRFInfo.vWorkSpaceId=parent.vWorkSpaceId AND (WorkSpaceSubjectMst.vSubjectId = CRFInfo.vSubjectId OR CRFInfo.vSubjectId='0000') AND CRFInfo.iPeriod=parent.iPeriod LEFT JOIN ( SELECT COUNT(nDCFNo) AS DCF, nCRFDtlNo, cDCFStatus FROM DCFMst GROUP BY nCRFDtlNo,cDCFStatus ) AS DCFMst ON CRFInfo.nCRFDtlno=DCFMst.nCRFDtlNo AND DCFMst.cDCFStatus in ('N','O') WHERE CHARINDEX(',' + (CAST(parent.vWorkSpaceId AS varchar(1000))) + ',', CASE WHEN @vWorkSpaceId <> '' THEN @vWorkSpaceId ELSE ',' + CAST(parent.vWorkSpaceId as VARCHAR(1000)) + ',' END) > 0 AND ActivityMst.cSubjectWiseFlag=@cSubjectWiseFlag AND ISNULL(parent.vTemplateId,0)<>'0001' AND parent.iNodeId<>1 AND parent.cStatusIndi<>'D' AND child.cStatusIndi<>'D' AND CASE @cSubjectWiseFlag WHEN 'Y' THEN CHARINDEX(',' + (CAST(WorkSpaceSubjectMst.vSubjectId AS varchar(1000))) + ',', CASE WHEN @vSubjectId <> '' THEN @vSubjectId ELSE ',' + CAST(WorkSpaceSubjectMst.vSubjectId as VARCHAR(1000)) + ',' END) ELSE 1 End > 0 AND CHARINDEX(',' + (CAST(parent.iNodeId AS varchar(1000))) + ',', CASE WHEN @iParentActivityNodeId <> '' THEN @iParentActivityNodeId ELSE ',' + CAST(parent.iNodeId as VARCHAR(1000)) + ',' END) > 0 AND CHARINDEX(',' + (CAST(child.iPeriod AS varchar(1000))) + ',', CASE WHEN @iPeriod <> '' THEN @iPeriod ELSE ',' + CAST(child.iPeriod as VARCHAR(1000)) + ',' END) > 0 AND CHARINDEX(',' + (CAST(child.iNodeId AS varchar(1000))) + ',', CASE WHEN @iActivityNodeId <> '' THEN @iActivityNodeId ELSE ',' + CAST(child.iNodeId as VARCHAR(1000)) + ',' END) > 0 AND CHARINDEX(',' + (CAST(isnull(CRFInfo.cDataStatus,'0') AS varchar(1000))) + ',', CASE WHEN @cDataStatus <> '' THEN @cDataStatus ELSE ',' + CAST(ISNULL(CRFInfo.cDataStatus,0) as VARCHAR(1000)) + ',' END) > 0 AND CHARINDEX(',' + (CAST(ISNULL(CRFInfo.iWorkFlowstageId,'0') AS varchar(1000))) + ',', CASE WHEN @iWorkFlowStageId <> '' THEN @iWorkFlowStageId ELSE ',' + CAST(isnull(CRFInfo.iWorkFlowstageId,'0') as VARCHAR(1000)) + ',' END) > 0 ORDER BY iMySubjectNo,parent.iNodeId,child.iNodeId,parent.iNodeNo END
CHARINDEX(',' + (CAST(ISNULL(CRFInfo.iWorkFlowstageId,'0') AS varchar(1000))) + ',', CASE WHEN @iWorkFlowStageId <> '' THEN @iWorkFlowStageId ELSE ',' + CAST(isnull(CRFInfo.iWorkFlowstageId,'0') as VARCHAR(1000)) + ',' END) > 0
CHARINDEX(',' + (CAST(parent.vWorkSpaceId AS varchar(1000))) + ','
exec Proc_GetCRFActivityStatusReport_BABE ',0000001611,0000001637,0000001460,0000001461,0000001747,0000001751,0000001445,0000001748,0000001877,0000001922,','',',TL11-00012,AH11-00514,TL10-00003,TL10-00004,PA11-00003,SU11-00032,TL10-00001,NA11-00020,SU11-00008,PA11-00001,PA11-00002,SU11-00009,NA12-00001,TL10-00002,SU11-00033,PA11-00005,TL10-00005,AH11-00518,TL11-00013,TL11-00014,AH11-00562,TL10-00006,PA11-00013,SU11-00034,NA12-00002,SU11-00010,SU11-00035,PA11-00014,TL11-00001,AH11-00563,TL11-00017,TL11-00018,AH11-00564,TL11-00002,PA11-00015,SU11-00036,SU11-00037,PA11-00018,TL11-00003,AH11-00565,TL11-00022,TL11-00023,AH11-00566,TL11-00004,PA11-00019,SU11-00084,SU11-00085,PA11-00020,TL11-00005,AH11-00567,TL11-00083,TL11-00085,AH11-00568,TL11-00006,SU11-00086,TL11-00007,AH11-00569,TL11-00092,TL11-00093,AH11-00571,TL11-00008,TL11-00009,AH11-00572,TL11-00094,TL11-00095,AH11-00604,TL11-00010,TL11-00011,AH11-00802,TL11-00103,TL11-00104,AH11-01349,AH11-01559,TL11-00105,TL11-00027,TL11-00028,TL11-00029,TL11-00030,TL11-00031,TL11-00033,TL11-00034,','','','Y','',''
