performance problem

[habesha]

can you suggest on this procedur to improve its performance


--procedure
@SessionGUID UNIQUEIDENTIFIER,
@ReportId int,
@SelColumn_STUDY_NUMBER varchar(1),
@SelColumn_SVB_TO_BE_VERSION_NUMBER varchar(1),
@SelColumn_INDNumber varchar(1),
@SelColumn_IN_DEVELOP_STATUS_DATE varchar(1),
@SelColumn_SRC_REVIEW_TYPE varchar(1),
@SelColumn_SRC_RCC_RECD_DATE varchar(1),
@SelColumn_SRC_ACTUAL_REVIEW_DATE varchar(1),
@SelColumn_SRC_DISTRIBUTED_DATE varchar(1),
@SelColumn_SRC_ELAPSED_CALENDAR_DAYS varchar(1),
@SelColumn_SRC_ELAPSED_CALENDAR_DAYS_EXT varchar(1),
@SelColumn_SRC_APPROVAL_DATE varchar(1),
@SelColumn_REG_REV_RECD_AT_RCC varchar(1),
@SelColumn_REG_CTS_SENT_DATE varchar(1),
@SelColumn_REG_ELAPSED_CALENDAR_DAYS varchar(1),
@SelColumn_REG_REV_WITH_TEAM varchar(1),
@SelColumn_MO_REV_RECD_AT_RCC varchar(1),
@SelColumn_MRS_MO_SUBMITTED_DATE varchar(1),
@SelColumn_MO_RECEIVED_FROM_MO varchar(1),
@SelColumn_MO_DECISION_TYPE varchar(1),
@SelColumn_MO_CTS_RETURNED_DATE varchar(1),
@SelColumn_MO_ELAPSED_CALENDAR_DAYS varchar(1),
@SelColumn_MO_COMMENTS_WITH_TEAM varchar(1),
@SelColumn_FINAL_RAB_RECD_AT_RCC varchar(1),
@SelColumn_FINAL_RAB_COMMENTS_ON_FINAL_PROTOCOL varchar(1),
@SelColumn_FRS_RAB_SENT_DATE varchar(1),
@SelColumn_FINAL_RAB_DECISION_TYPE varchar(1),
@SelColumn_FINAL_FROM_RAB varchar(1),
@SelColumn_VER_SUB_TO_OPS varchar(1),
@SelColumn_FINAL_RAB_ELAPSED_DAYS varchar(1),
@SelColumn_OPEN_TO_ACCRUAL_DATE varchar(1),
@SelColumn_TOTAL_DEVELOP_TIME varchar(1),
@SelColumn_TOTAL_DAIDS_REVIEW_TIME varchar(1),
@SelConstraint_Network varchar(1),
@SelConstraint_Network_id int,
@SelConstraint_ProtocolStatus varchar(1),
@SelConstraint_ProtocolStatus_id int,
@SelConstraint_RCSpecialist varchar(1),
@SelConstraint_RCSpecialist_id int,
@SelConstraint_STU_NUMBER varchar(1),
@SelConstraint_STU_NUMBER_id int,
@SelConstraint_RegRevRCCRecdDate varchar(1),
@SelConstraint_RegRevRCCRecdDate_From DATETIME,
@SelConstraint_RegRevRCCRecdDate_To DATETIME,
@SelConstraint_SRCRevRCCRecdDate varchar(1),
@SelConstraint_SRCRevRCCRecdDate_From DATETIME,
@SelConstraint_SRCRevRCCRecdDate_To DATETIME
)
AS
BEGIN

DECLARE @l_SVB_ID_Num NUMERIC(10,0)

DECLARE CR_STUDY_VERSIONS_TO_BE CURSOR FOR
SELECT SVB_ID FROM VOYAGER.STUDY_VERSIONS_TO_BE

-- Main Outer Cursor Start
OPEN CR_STUDY_VERSIONS_TO_BE
FETCH NEXT FROM CR_STUDY_VERSIONS_TO_BE INTO @l_SVB_ID_Num
WHILE (@@FETCH_STATUS = 0)
BEGIN
--PRINT @l_SVB_ID_Num

SELECT SEB_SVE_ID INTO #TEMP_STUDY_VERSIONS_FOR_TO_BE_VERSION
FROM VOYAGER.STUDY_VERSIONS_TO_BE_VERSIONS
WHERE SEB_SVB_ID=@l_SVB_ID_Num

-- CSRC Reviews
DECLARE @l_CSRC_REV_RECD_AT_RCC VARCHAR(12), @l_CSRC_ACTUAL_REVIEW_DATE VARCHAR(12)
DECLARE @l_CSRC_DISTRIBUTED_DATE VARCHAR(12),@l_CSRC_ELAPSED_CALENDAR_DAYS VARCHAR(12)
DECLARE @l_CSRC_APPROVAL_DATE VARCHAR(12),@l_CSRC_COMMENTS_WITH_TEAM VARCHAR(12)

DECLARE @l_CSRC_REV_RECD_AT_RCC_Vch VARCHAR(1000), @l_CSRC_ACTUAL_REVIEW_DATE_Vch VARCHAR(1000)
DECLARE @l_CSRC_DISTRIBUTED_DATE_Vch VARCHAR(1000),@l_CSRC_ELAPSED_CALENDAR_DAYS_Vch VARCHAR(1000)
DECLARE @l_CSRC_APPROVAL_DATE_Vch VARCHAR(1000),@l_CSRC_COMMENTS_WITH_TEAM_Vch VARCHAR(1000)
DECLARE @l_CSRC_ELAPSED_CALENDAR_DAYS_Int INT

SET @l_CSRC_REV_RECD_AT_RCC_Vch=''
SET @l_CSRC_ACTUAL_REVIEW_DATE_Vch=''
SET @l_CSRC_DISTRIBUTED_DATE_Vch=''
SET @l_CSRC_ELAPSED_CALENDAR_DAYS_Vch=''
SET @l_CSRC_APPROVAL_DATE_Vch=''
SET @l_CSRC_COMMENTS_WITH_TEAM_Vch=''
SET @l_CSRC_ELAPSED_CALENDAR_DAYS_Int=0

SELECT CONVERT(VARCHAR(10),SVW_RCC_RECEIVED_DATE,101) AS SVW_RCC_RECEIVED_DATE,CONVERT(VARCHAR(10),SRI_ACTUAL_REVIEW_DATE,101) AS SRI_ACTUAL_REVIEW_DATE,
CONVERT(VARCHAR(10),SRI_DISTRIBUTED_DATE,101) AS SRI_DISTRIBUTED_DATE,
DATEDIFF(D,SRI_ACTUAL_REVIEW_DATE,SRI_DISTRIBUTED_DATE) AS CSRC_ELAPSED_CALENDAR_DAYS,
CONVERT(VARCHAR(10),SVV_REVIEW_DECISION_DATE,101) AS SVV_REVIEW_DECISION_DATE
INTO #TEMP_CSRC_REVIEWS
FROM VOYAGER.STUDY_VERSION_REVIEWS JOIN VOYAGER.SRC_REVIEW_SIGNOFFS ON SRI_SVV_ID=SVV_ID
JOIN VOYAGER.STUDY_VERSION_PREREVIEWS ON SVW_SVV_ID=SVV_ID
AND SRI_RECORD_STATUS_FLAG='Active' AND SVV_RECORD_STATUS_FLAG='Active'
WHERE SRI_PSRC_CSRC_FLAG='CSRC' AND SVW_PREREVIEW_DECISION_TYPE='Forwarded For Review'
AND SVV_SVE_ID IN (SELECT SEB_SVE_ID FROM #TEMP_STUDY_VERSIONS_FOR_TO_BE_VERSION)
ORDER BY SVW_RCC_RECEIVED_DATE

DECLARE CR_CSRC_REVIEWS CURSOR FOR
SELECT SVW_RCC_RECEIVED_DATE,SRI_ACTUAL_REVIEW_DATE,SRI_DISTRIBUTED_DATE,CSRC_ELAPSED_CALENDAR_DAYS,SVV_REVIEW_DECISION_DATE
FROM #TEMP_CSRC_REVIEWS

OPEN CR_CSRC_REVIEWS
FETCH NEXT FROM CR_CSRC_REVIEWS INTO @l_CSRC_REV_RECD_AT_RCC,@l_CSRC_ACTUAL_REVIEW_DATE,@l_CSRC_DISTRIBUTED_DATE,
@l_CSRC_ELAPSED_CALENDAR_DAYS,@l_CSRC_APPROVAL_DATE

WHILE (@@FETCH_STATUS = 0)
BEGIN

IF(LEN(@l_CSRC_REV_RECD_AT_RCC_Vch)=0)
SET @l_CSRC_REV_RECD_AT_RCC_Vch = ISNULL(@l_CSRC_REV_RECD_AT_RCC,'')
ELSE
SET @l_CSRC_REV_RECD_AT_RCC_Vch = @l_CSRC_REV_RECD_AT_RCC_Vch+CHAR(10)+ISNULL(@l_CSRC_REV_RECD_AT_RCC,'')

IF(LEN(@l_CSRC_ACTUAL_REVIEW_DATE_Vch)=0)
SET @l_CSRC_ACTUAL_REVIEW_DATE_Vch = ISNULL(@l_CSRC_ACTUAL_REVIEW_DATE,'')
ELSE
SET @l_CSRC_ACTUAL_REVIEW_DATE_Vch = @l_CSRC_ACTUAL_REVIEW_DATE_Vch+CHAR(10)+ISNULL(@l_CSRC_ACTUAL_REVIEW_DATE,'')

IF(LEN(@l_CSRC_DISTRIBUTED_DATE_Vch)=0)
SET @l_CSRC_DISTRIBUTED_DATE_Vch = ISNULL(@l_CSRC_DISTRIBUTED_DATE,'')
ELSE
SET @l_CSRC_DISTRIBUTED_DATE_Vch = @l_CSRC_DISTRIBUTED_DATE_Vch+CHAR(10)+ISNULL(@l_CSRC_DISTRIBUTED_DATE,'')

IF(LEN(@l_CSRC_ELAPSED_CALENDAR_DAYS_Vch)=0)
SET @l_CSRC_ELAPSED_CALENDAR_DAYS_Vch = ISNULL(@l_CSRC_ELAPSED_CALENDAR_DAYS,'')
ELSE
SET @l_CSRC_ELAPSED_CALENDAR_DAYS_Vch = @l_CSRC_ELAPSED_CALENDAR_DAYS_Vch+CHAR(10)+ISNULL(@l_CSRC_ELAPSED_CALENDAR_DAYS,'')

SET @l_CSRC_ELAPSED_CALENDAR_DAYS_Int = @l_CSRC_ELAPSED_CALENDAR_DAYS_Int + CONVERT(INT,ISNULL(@l_CSRC_ELAPSED_CALENDAR_DAYS,''))

IF(LEN(@l_CSRC_APPROVAL_DATE_Vch)=0)
SET @l_CSRC_APPROVAL_DATE_Vch = ISNULL(@l_CSRC_APPROVAL_DATE,'')
ELSE
SET @l_CSRC_APPROVAL_DATE_Vch = @l_CSRC_APPROVAL_DATE_Vch+CHAR(10)+ISNULL(@l_CSRC_APPROVAL_DATE,'')


FETCH NEXT FROM CR_CSRC_REVIEWS INTO @l_CSRC_REV_RECD_AT_RCC,@l_CSRC_ACTUAL_REVIEW_DATE,@l_CSRC_DISTRIBUTED_DATE,
@l_CSRC_ELAPSED_CALENDAR_DAYS,@l_CSRC_APPROVAL_DATE
END

CLOSE CR_CSRC_REVIEWS
DEALLOCATE CR_CSRC_REVIEWS

-- PSRC Reviews
DECLARE @l_PSRC_REV_RECD_AT_RCC VARCHAR(12), @l_PSRC_ACTUAL_REVIEW_DATE VARCHAR(12)
DECLARE @l_PSRC_DISTRIBUTED_DATE VARCHAR(12),@l_PSRC_ELAPSED_CALENDAR_DAYS VARCHAR(12)
DECLARE @l_PSRC_APPROVAL_DATE VARCHAR(12),@l_PSRC_COMMENTS_WITH_TEAM VARCHAR(12)

DECLARE @l_PSRC_REV_RECD_AT_RCC_Vch VARCHAR(1000), @l_PSRC_ACTUAL_REVIEW_DATE_Vch VARCHAR(1000)
DECLARE @l_PSRC_DISTRIBUTED_DATE_Vch VARCHAR(1000),@l_PSRC_ELAPSED_CALENDAR_DAYS_Vch VARCHAR(1000)
DECLARE @l_PSRC_APPROVAL_DATE_Vch VARCHAR(1000),@l_PSRC_COMMENTS_WITH_TEAM_Vch VARCHAR(1000)
DECLARE @l_PSRC_ELAPSED_CALENDAR_DAYS_Int INT

SET @l_PSRC_REV_RECD_AT_RCC_Vch=''
SET @l_PSRC_ACTUAL_REVIEW_DATE_Vch=''
SET @l_PSRC_DISTRIBUTED_DATE_Vch=''
SET @l_PSRC_ELAPSED_CALENDAR_DAYS_Vch=''
SET @l_PSRC_APPROVAL_DATE_Vch=''
SET @l_PSRC_COMMENTS_WITH_TEAM_Vch=''
SET @l_PSRC_ELAPSED_CALENDAR_DAYS_Int = 0


SELECT CONVERT(VARCHAR(10),SVW_RCC_RECEIVED_DATE,101) AS SVW_RCC_RECEIVED_DATE,CONVERT(VARCHAR(10),SRI_ACTUAL_REVIEW_DATE,101) AS SRI_ACTUAL_REVIEW_DATE,
CONVERT(VARCHAR(10),SRI_DISTRIBUTED_DATE,101) AS SRI_DISTRIBUTED_DATE,
DATEDIFF(D,SRI_ACTUAL_REVIEW_DATE,SRI_DISTRIBUTED_DATE) AS PSRC_ELAPSED_DAYS,
CONVERT(VARCHAR(10),SVV_REVIEW_DECISION_DATE,101) AS SVV_REVIEW_DECISION_DATE
INTO #TEMP_PSRC_REVIEWS
FROM VOYAGER.STUDY_VERSION_REVIEWS JOIN VOYAGER.SRC_REVIEW_SIGNOFFS ON SRI_SVV_ID=SVV_ID
JOIN VOYAGER.STUDY_VERSION_PREREVIEWS ON SVW_SVV_ID=SVV_ID
AND SRI_RECORD_STATUS_FLAG='Active' AND SVV_RECORD_STATUS_FLAG='Active'
WHERE SRI_PSRC_CSRC_FLAG='PSRC' AND SVW_PREREVIEW_DECISION_TYPE='Forwarded For Review'
AND SVV_SVE_ID IN (SELECT SEB_SVE_ID FROM #TEMP_STUDY_VERSIONS_FOR_TO_BE_VERSION)
ORDER BY SVW_RCC_RECEIVED_DATE

DECLARE CR_PSRC_REVIEWS CURSOR FOR
SELECT SVW_RCC_RECEIVED_DATE,SRI_ACTUAL_REVIEW_DATE,SRI_DISTRIBUTED_DATE,PSRC_ELAPSED_DAYS,SVV_REVIEW_DECISION_DATE
FROM #TEMP_PSRC_REVIEWS


OPEN CR_PSRC_REVIEWS
FETCH NEXT FROM CR_PSRC_REVIEWS INTO @l_PSRC_REV_RECD_AT_RCC,@l_PSRC_ACTUAL_REVIEW_DATE,@l_PSRC_DISTRIBUTED_DATE,
@l_PSRC_ELAPSED_CALENDAR_DAYS,@l_PSRC_APPROVAL_DATE


WHILE (@@FETCH_STATUS = 0)
BEGIN

IF(LEN(@l_PSRC_REV_RECD_AT_RCC_Vch)=0)
SET @l_PSRC_REV_RECD_AT_RCC_Vch = ISNULL(@l_PSRC_REV_RECD_AT_RCC,'')
ELSE
SET @l_PSRC_REV_RECD_AT_RCC_Vch = @l_PSRC_REV_RECD_AT_RCC_Vch+', '+ISNULL(@l_PSRC_REV_RECD_AT_RCC,'')

IF(LEN(@l_PSRC_ACTUAL_REVIEW_DATE_Vch)=0)
SET @l_PSRC_ACTUAL_REVIEW_DATE_Vch = ISNULL(@l_PSRC_ACTUAL_REVIEW_DATE,'')
ELSE
SET @l_PSRC_ACTUAL_REVIEW_DATE_Vch = @l_PSRC_ACTUAL_REVIEW_DATE_Vch+', '+ISNULL(@l_PSRC_ACTUAL_REVIEW_DATE,'')

IF(LEN(@l_PSRC_DISTRIBUTED_DATE_Vch)=0)
SET @l_PSRC_DISTRIBUTED_DATE_Vch = ISNULL(@l_PSRC_DISTRIBUTED_DATE,'')
ELSE
SET @l_PSRC_DISTRIBUTED_DATE_Vch = @l_PSRC_DISTRIBUTED_DATE_Vch+', '+ISNULL(@l_PSRC_DISTRIBUTED_DATE,'')

IF(LEN(@l_PSRC_ELAPSED_CALENDAR_DAYS_Vch)=0)
SET @l_PSRC_ELAPSED_CALENDAR_DAYS_Vch = ISNULL(@l_PSRC_ELAPSED_CALENDAR_DAYS,'')
ELSE
SET @l_PSRC_ELAPSED_CALENDAR_DAYS_Vch = @l_PSRC_ELAPSED_CALENDAR_DAYS_Vch+', '+ISNULL(@l_PSRC_ELAPSED_CALENDAR_DAYS,'')

SET @l_PSRC_ELAPSED_CALENDAR_DAYS_Int = @l_PSRC_ELAPSED_CALENDAR_DAYS_Int + CONVERT(INT,ISNULL(@l_PSRC_ELAPSED_CALENDAR_DAYS,''))

IF(LEN(@l_PSRC_APPROVAL_DATE_Vch)=0)
SET @l_PSRC_APPROVAL_DATE_Vch = ISNULL(@l_PSRC_APPROVAL_DATE,'')
ELSE
SET @l_PSRC_APPROVAL_DATE_Vch = @l_PSRC_APPROVAL_DATE_Vch+', '+ISNULL(@l_PSRC_APPROVAL_DATE,'')


FETCH NEXT FROM CR_PSRC_REVIEWS INTO @l_PSRC_REV_RECD_AT_RCC_Vch,@l_PSRC_ACTUAL_REVIEW_DATE_Vch,@l_PSRC_DISTRIBUTED_DATE_Vch,
@l_PSRC_ELAPSED_CALENDAR_DAYS_Vch,@l_PSRC_APPROVAL_DATE_Vch
END

CLOSE CR_PSRC_REVIEWS
DEALLOCATE CR_PSRC_REVIEWS

-- Regulatory Reviews
DECLARE @l_REG_REV_RECD_AT_RCC VARCHAR(12), @l_REG_CTS_SENT_DATE VARCHAR(12)
DECLARE @l_REG_ELAPSED_CALENDAR_DAYS VARCHAR(12), @l_REG_COMMENTS_WITH_TEAM VARCHAR(12)

DECLARE @l_REG_REV_RECD_AT_RCC_Vch VARCHAR(1000), @l_REG_CTS_SENT_DATE_Vch VARCHAR(1000)
DECLARE @l_REG_ELAPSED_CALENDAR_DAYS_Vch VARCHAR(1000), @l_REG_COMMENTS_WITH_TEAM_Vch VARCHAR(1000)
DECLARE @l_REG_ELAPSED_CALENDAR_DAYS_Int INT

SET @l_REG_REV_RECD_AT_RCC_Vch=''
SET @l_REG_CTS_SENT_DATE_Vch=''
SET @l_REG_ELAPSED_CALENDAR_DAYS_Vch=''
SET @l_REG_COMMENTS_WITH_TEAM_Vch=''
SET @l_REG_ELAPSED_CALENDAR_DAYS_Int = 0

SELECT CONVERT(VARCHAR(10),SVW_RCC_RECEIVED_DATE,101) AS SVW_RCC_RECEIVED_DATE,
CONVERT(VARCHAR(10),RRS_CTS_SENT_DATE,101) AS RRS_CTS_SENT_DATE,
DATEDIFF(D,SVW_RCC_RECEIVED_DATE,RRS_CTS_SENT_DATE) AS REG_REV_ELAPSED_DAYS
INTO #TEMP_REG_REVIEWS
FROM VOYAGER.STUDY_VERSION_REVIEWS JOIN VOYAGER.REGULATORY_REVIEW_SIGNOFFS ON RRS_SVV_ID=SVV_ID
JOIN VOYAGER.STUDY_VERSION_PREREVIEWS ON SVW_SVV_ID=SVV_ID
AND RRS_RECORD_STATUS_FLAG='Active' AND SVV_RECORD_STATUS_FLAG='Active'
WHERE SVW_PREREVIEW_DECISION_TYPE='Forwarded For Review'
AND SVV_SVE_ID IN (SELECT SEB_SVE_ID FROM #TEMP_STUDY_VERSIONS_FOR_TO_BE_VERSION)
ORDER BY SVW_RCC_RECEIVED_DATE

DECLARE CR_REG_REVIEWS CURSOR FOR
SELECT SVW_RCC_RECEIVED_DATE,RRS_CTS_SENT_DATE,REG_REV_ELAPSED_DAYS
FROM #TEMP_REG_REVIEWS

OPEN CR_REG_REVIEWS
FETCH NEXT FROM CR_REG_REVIEWS INTO @l_REG_REV_RECD_AT_RCC,@l_REG_CTS_SENT_DATE,@l_REG_ELAPSED_CALENDAR_DAYS

WHILE (@@FETCH_STATUS = 0)
BEGIN

IF(LEN(@l_REG_REV_RECD_AT_RCC_Vch)=0)
SET @l_REG_REV_RECD_AT_RCC_Vch = ISNULL(@l_REG_REV_RECD_AT_RCC,'')
ELSE
SET @l_REG_REV_RECD_AT_RCC_Vch = @l_REG_REV_RECD_AT_RCC_Vch+CHAR(10)+ISNULL(@l_REG_REV_RECD_AT_RCC,'')

IF(LEN(@l_REG_CTS_SENT_DATE_Vch)=0)
SET @l_REG_CTS_SENT_DATE_Vch = ISNULL(@l_REG_CTS_SENT_DATE,'')
ELSE
SET @l_REG_CTS_SENT_DATE_Vch = @l_REG_CTS_SENT_DATE_Vch+CHAR(10)+ISNULL(@l_REG_CTS_SENT_DATE,'')

IF(LEN(@l_REG_ELAPSED_CALENDAR_DAYS_Vch)=0)
SET @l_REG_ELAPSED_CALENDAR_DAYS_Vch = ISNULL(@l_REG_ELAPSED_CALENDAR_DAYS,'')
ELSE
SET @l_REG_ELAPSED_CALENDAR_DAYS_Vch = @l_REG_ELAPSED_CALENDAR_DAYS_Vch+CHAR(10)+ISNULL(@l_REG_ELAPSED_CALENDAR_DAYS,'')

SET @l_REG_ELAPSED_CALENDAR_DAYS_Int = @l_REG_ELAPSED_CALENDAR_DAYS_Int + CONVERT(INT,ISNULL(@l_REG_ELAPSED_CALENDAR_DAYS,''))


FETCH NEXT FROM CR_REG_REVIEWS INTO @l_REG_REV_RECD_AT_RCC,@l_REG_CTS_SENT_DATE,@l_REG_ELAPSED_CALENDAR_DAYS
END

CLOSE CR_REG_REVIEWS
DEALLOCATE CR_REG_REVIEWS

-- MO Reviews
DECLARE @l_MO_REV_RECD_AT_RCC VARCHAR(12),@l_MRS_MO_SUBMITTED_DATE VARCHAR(12),
@l_MO_RECEIVED_FROM_MO VARCHAR(12),@l_MO_DECISION_TYPE VARCHAR(75)
DECLARE @l_MO_CTS_RETURNED_DATE VARCHAR(12),@l_MO_ELAPSED_CALENDAR_DAYS VARCHAR(12),
@l_MO_COMMENTS_WITH_TEAM VARCHAR(12)

DECLARE @l_MO_REV_RECD_AT_RCC_Vch VARCHAR(1000), @l_MRS_MO_SUBMITTED_DATE_Vch VARCHAR(1000),
@l_MO_RECEIVED_FROM_MO_Vch VARCHAR(1000),@l_MO_DECISION_TYPE_Vch VARCHAR(1000)
DECLARE @l_MO_CTS_RETURNED_DATE_Vch VARCHAR(1000),@l_MO_ELAPSED_CALENDAR_DAYS_Vch VARCHAR(1000),
@l_MO_COMMENTS_WITH_TEAM_Vch VARCHAR(1000)
DECLARE @l_MO_ELAPSED_CALENDAR_DAYS_Int INT

SET @l_MO_REV_RECD_AT_RCC_Vch=''
SET @l_MRS_MO_SUBMITTED_DATE_Vch=''
SET @l_MO_RECEIVED_FROM_MO_Vch=''
SET @l_MO_DECISION_TYPE_Vch=''
SET @l_MO_CTS_RETURNED_DATE_Vch=''
SET @l_MO_ELAPSED_CALENDAR_DAYS_Vch=''
SET @l_MO_COMMENTS_WITH_TEAM_Vch=''
SET @l_MO_ELAPSED_CALENDAR_DAYS_Int=0


SELECT CONVERT(VARCHAR(10),SVW_RCC_RECEIVED_DATE,101) AS SVW_RCC_RECEIVED_DATE,
CONVERT(VARCHAR(10),MRS_MO_SUBMITTED_DATE,101) AS MRS_MO_SUBMITTED_DATE,
(SELECT CONVERT(VARCHAR(10),MAX(MRD_MO_DECISION_DATE),101)
FROM MO_REVIEW_DECISIONS WHERE MRD_MRS_ID=MRS_ID) AS MO_DECISION_DATE,SVV_REVIEW_DECISION_TYPE,
CONVERT(VARCHAR(10),MRS_CTS_RETURNED_DATE,101) AS MRS_CTS_RETURNED_DATE,
DATEDIFF(D,SVW_RCC_RECEIVED_DATE,MRS_CTS_RETURNED_DATE) AS MO_REV_ELAPSED_DAYS
INTO #TEMP_MO_REVIEWS
FROM VOYAGER.STUDY_VERSION_REVIEWS JOIN VOYAGER.MO_REVIEW_SIGNOFFS ON MRS_SVV_ID=SVV_ID
JOIN VOYAGER.STUDY_VERSION_PREREVIEWS ON SVW_SVV_ID=SVV_ID
AND MRS_RECORD_STATUS_FLAG='Active' AND SVV_RECORD_STATUS_FLAG='Active'
WHERE SVW_PREREVIEW_DECISION_TYPE='Forwarded For Review'
AND SVV_SVE_ID IN (SELECT SEB_SVE_ID FROM #TEMP_STUDY_VERSIONS_FOR_TO_BE_VERSION)
ORDER BY SVW_RCC_RECEIVED_DATE

DECLARE CR_MO_REVIEWS CURSOR FOR
SELECT SVW_RCC_RECEIVED_DATE,MRS_MO_SUBMITTED_DATE,MO_DECISION_DATE,SVV_REVIEW_DECISION_TYPE,MRS_CTS_RETURNED_DATE,MO_REV_ELAPSED_DAYS
FROM #TEMP_MO_REVIEWS

OPEN CR_MO_REVIEWS
FETCH NEXT FROM CR_MO_REVIEWS INTO @l_MO_REV_RECD_AT_RCC,@l_MRS_MO_SUBMITTED_DATE,@l_MO_RECEIVED_FROM_MO,@l_MO_DECISION_TYPE,
@l_MO_CTS_RETURNED_DATE,@l_MO_ELAPSED_CALENDAR_DAYS

WHILE (@@FETCH_STATUS = 0)
BEGIN

IF(LEN(@l_MO_REV_RECD_AT_RCC_Vch)=0)
SET @l_MO_REV_RECD_AT_RCC_Vch = ISNULL(@l_MO_REV_RECD_AT_RCC,'')
ELSE
SET @l_MO_REV_RECD_AT_RCC_Vch = @l_MO_REV_RECD_AT_RCC_Vch+CHAR(10)+ISNULL(@l_MO_REV_RECD_AT_RCC,'')

IF(LEN(@l_MRS_MO_SUBMITTED_DATE_Vch)=0)
SET @l_MRS_MO_SUBMITTED_DATE_Vch = ISNULL(@l_MRS_MO_SUBMITTED_DATE,'')
ELSE
SET @l_MRS_MO_SUBMITTED_DATE_Vch = @l_MRS_MO_SUBMITTED_DATE_Vch+CHAR(10)+ISNULL(@l_MRS_MO_SUBMITTED_DATE,'')

IF(LEN(@l_MO_RECEIVED_FROM_MO_Vch)=0)
SET @l_MO_RECEIVED_FROM_MO_Vch = ISNULL(@l_MO_RECEIVED_FROM_MO,'')
ELSE
SET @l_MO_RECEIVED_FROM_MO_Vch = @l_MO_RECEIVED_FROM_MO_Vch+CHAR(10)+ISNULL(@l_MO_RECEIVED_FROM_MO,'')

IF(LEN(@l_MO_DECISION_TYPE_Vch)=0)
SET @l_MO_DECISION_TYPE_Vch = ISNULL(@l_MO_DECISION_TYPE,'')
ELSE
SET @l_MO_DECISION_TYPE_Vch = @l_MO_DECISION_TYPE_Vch+CHAR(10)+ISNULL(@l_MO_DECISION_TYPE,'')

IF(LEN(@l_MO_CTS_RETURNED_DATE_Vch)=0)
SET @l_MO_CTS_RETURNED_DATE_Vch = ISNULL(@l_MO_CTS_RETURNED_DATE,'')
ELSE
SET @l_MO_CTS_RETURNED_DATE_Vch = @l_MO_CTS_RETURNED_DATE_Vch+CHAR(10)+ISNULL(@l_MO_CTS_RETURNED_DATE,'')

IF(LEN(@l_MO_ELAPSED_CALENDAR_DAYS_Vch)=0)
SET @l_MO_ELAPSED_CALENDAR_DAYS_Vch = ISNULL(@l_MO_ELAPSED_CALENDAR_DAYS,'')
ELSE
SET @l_MO_ELAPSED_CALENDAR_DAYS_Vch = @l_MO_ELAPSED_CALENDAR_DAYS_Vch+CHAR(10)+ISNULL(@l_MO_ELAPSED_CALENDAR_DAYS,'')

SET @l_MO_ELAPSED_CALENDAR_DAYS_Int = @l_MO_ELAPSED_CALENDAR_DAYS_Int + CONVERT(INT,ISNULL(@l_MO_ELAPSED_CALENDAR_DAYS,''))


FETCH NEXT FROM CR_MO_REVIEWS INTO @l_MO_REV_RECD_AT_RCC,@l_MRS_MO_SUBMITTED_DATE,@l_MO_RECEIVED_FROM_MO,@l_MO_DECISION_TYPE,
@l_MO_CTS_RETURNED_DATE,@l_MO_ELAPSED_CALENDAR_DAYS
END

CLOSE CR_MO_REVIEWS
DEALLOCATE CR_MO_REVIEWS

-- Final RAB Reviews
DECLARE @l_FINAL_RAB_RECD_AT_RCC VARCHAR(12),@l_FRS_RAB_SENT_DATE VARCHAR(12),@l_FINAL_FROM_RAB VARCHAR(12)
DECLARE @l_FRS_CTS_SENT_DATE VARCHAR(12),@l_FINAL_RAB_ELAPSED_DAYS VARCHAR(12)
DECLARE @l_FINAL_RAB_COMMENTS_WITH_TEAM VARCHAR(12),@l_FINAL_RAB_DECISION_TYPE VARCHAR(100)

DECLARE @l_FINAL_RAB_RECD_AT_RCC_Vch VARCHAR(1000), @l_FRS_RAB_SENT_DATE_Vch VARCHAR(1000)
DECLARE @l_FINAL_FROM_RAB_Vch VARCHAR(1000),@l_FRS_CTS_SENT_DATE_Vch VARCHAR(1000)
DECLARE @l_FINAL_RAB_ELAPSED_DAYS_Vch VARCHAR(1000),@l_FINAL_RAB_COMMENTS_WITH_TEAM_Vch VARCHAR(1000)
DECLARE @l_FINAL_RAB_ELAPSED_DAYS_Int INT,@l_FINAL_RAB_DECISION_TYPE_Vch VARCHAR(1000)

SET @l_FINAL_RAB_RECD_AT_RCC_Vch=''
SET @l_FRS_RAB_SENT_DATE_Vch=''
SET @l_FINAL_FROM_RAB_Vch=''
SET @l_FRS_CTS_SENT_DATE_Vch=''
SET @l_FINAL_RAB_ELAPSED_DAYS_Vch=''
SET @l_FINAL_RAB_COMMENTS_WITH_TEAM_Vch=''
SET @l_FINAL_RAB_ELAPSED_DAYS_Int=0
SET @l_FINAL_RAB_DECISION_TYPE_Vch=''

SELECT CONVERT(VARCHAR(10),SVW_RCC_RECEIVED_DATE,101) AS SVW_RCC_RECEIVED_DATE,
CONVERT(VARCHAR(10),FRS_RAB_SENT_DATE,101) AS FRS_RAB_SENT_DATE,
CONVERT(VARCHAR(10),SVV_REVIEW_DECISION_DATE,101) AS SVV_REVIEW_DECISION_DATE,
CONVERT(VARCHAR(10),FRS_CTS_SENT_DATE,101) AS FRS_CTS_SENT_DATE,
DATEDIFF(D,SVW_RCC_RECEIVED_DATE,FRS_CTS_SENT_DATE) AS FINAL_RAB_ELAPSED_DAYS,
SVV_REVIEW_DECISION_TYPE AS FINAL_RAB_DECISION_TYPE
INTO #TEMP_FINAL_RAB
FROM VOYAGER.STUDY_VERSION_REVIEWS JOIN VOYAGER.FINAL_RAB_SIGNOFFS ON FRS_SVV_ID=SVV_ID
JOIN VOYAGER.STUDY_VERSION_PREREVIEWS ON SVW_SVV_ID=SVV_ID
AND FRS_RECORD_STATUS_FLAG='Active' AND SVV_RECORD_STATUS_FLAG='Active'
WHERE SVW_PREREVIEW_DECISION_TYPE='Forwarded For Review'
AND SVV_SVE_ID IN (SELECT SEB_SVE_ID FROM #TEMP_STUDY_VERSIONS_FOR_TO_BE_VERSION)
ORDER BY SVW_RCC_RECEIVED_DATE

DECLARE CR_FINAL_RAB CURSOR FOR
SELECT SVW_RCC_RECEIVED_DATE,FRS_RAB_SENT_DATE,SVV_REVIEW_DECISION_DATE,FRS_CTS_SENT_DATE,
FINAL_RAB_ELAPSED_DAYS,FINAL_RAB_DECISION_TYPE
FROM #TEMP_FINAL_RAB

OPEN CR_FINAL_RAB
FETCH NEXT FROM CR_FINAL_RAB INTO @l_FINAL_RAB_RECD_AT_RCC,@l_FRS_RAB_SENT_DATE,@l_FINAL_FROM_RAB,
@l_FRS_CTS_SENT_DATE,@l_FINAL_RAB_ELAPSED_DAYS,@l_FINAL_RAB_DECISION_TYPE

WHILE (@@FETCH_STATUS = 0)
BEGIN

IF(LEN(@l_FINAL_RAB_RECD_AT_RCC_Vch)=0)
SET @l_FINAL_RAB_RECD_AT_RCC_Vch = ISNULL(@l_FINAL_RAB_RECD_AT_RCC,'')
ELSE
SET @l_FINAL_RAB_RECD_AT_RCC_Vch = @l_FINAL_RAB_RECD_AT_RCC_Vch+CHAR(10)+ISNULL(@l_FINAL_RAB_RECD_AT_RCC,'')

IF(LEN(@l_FRS_RAB_SENT_DATE_Vch)=0)
SET @l_FRS_RAB_SENT_DATE_Vch = ISNULL(@l_FRS_RAB_SENT_DATE,'')
ELSE
SET @l_FRS_RAB_SENT_DATE_Vch = @l_FRS_RAB_SENT_DATE_Vch+CHAR(10)+ISNULL(@l_FRS_RAB_SENT_DATE,'')

IF(LEN(@l_FINAL_FROM_RAB_Vch)=0)
SET @l_FINAL_FROM_RAB_Vch = ISNULL(@l_FINAL_FROM_RAB,'')
ELSE
SET @l_FINAL_FROM_RAB_Vch = @l_FINAL_FROM_RAB_Vch+CHAR(10)+ISNULL(@l_FINAL_FROM_RAB,'')

IF(LEN(@l_FRS_CTS_SENT_DATE_Vch)=0)
SET @l_FRS_CTS_SENT_DATE_Vch = ISNULL(@l_FRS_CTS_SENT_DATE,'')
ELSE
SET @l_FRS_CTS_SENT_DATE_Vch = @l_FRS_CTS_SENT_DATE_Vch+CHAR(10)+ISNULL(@l_FRS_CTS_SENT_DATE,'')

IF(LEN(@l_FINAL_RAB_ELAPSED_DAYS_Vch)=0)
SET @l_FINAL_RAB_ELAPSED_DAYS_Vch = ISNULL(@l_FINAL_RAB_ELAPSED_DAYS,'')
ELSE
SET @l_FINAL_RAB_ELAPSED_DAYS_Vch = @l_FINAL_RAB_ELAPSED_DAYS_Vch+CHAR(10)+ISNULL(@l_FINAL_RAB_ELAPSED_DAYS,'')

SET @l_FINAL_RAB_ELAPSED_DAYS_Int = @l_FINAL_RAB_ELAPSED_DAYS_Int + CONVERT(INT,ISNULL(@l_FINAL_RAB_ELAPSED_DAYS,''))


IF(LEN(@l_FINAL_RAB_DECISION_TYPE_Vch)=0)
SET @l_FINAL_RAB_DECISION_TYPE_Vch = ISNULL(@l_FINAL_RAB_DECISION_TYPE,'')
ELSE
SET @l_FINAL_RAB_DECISION_TYPE_Vch = @l_FINAL_RAB_DECISION_TYPE_Vch+CHAR(10)+ISNULL(@l_FINAL_RAB_DECISION_TYPE,'')

FETCH NEXT FROM CR_FINAL_RAB INTO @l_FINAL_RAB_RECD_AT_RCC,@l_FRS_RAB_SENT_DATE,@l_FINAL_FROM_RAB,
@l_FRS_CTS_SENT_DATE,@l_FINAL_RAB_ELAPSED_DAYS,@l_FINAL_RAB_DECISION_TYPE

END

CLOSE CR_FINAL_RAB
DEALLOCATE CR_FINAL_RAB


-- Protocol Closeout
DECLARE @l_VER_SUB_TO_OPS VARCHAR(12)
DECLARE @l_VER_SUB_TO_OPS_Vch VARCHAR(1000)

SET @l_VER_SUB_TO_OPS_Vch=''

DECLARE CR_PROTOCOL_CLOSEOUT CURSOR FOR
SELECT CONVERT(VARCHAR(10),SEG_OPS_SUBMISSION_DATE,101) AS SEG_OPS_SUBMISSION_DATE
FROM VOYAGER.STUDY_VERSION_REVIEWS JOIN VOYAGER.STUDY_VERSION_REGULATORY_REQUIREMENTS ON SEG_SVV_ID=SVV_ID
AND SEG_RECORD_STATUS_FLAG='Active' AND SVV_RECORD_STATUS_FLAG='Active'
WHERE SVV_SVE_ID IN (SELECT SEB_SVE_ID FROM #TEMP_STUDY_VERSIONS_FOR_TO_BE_VERSION)

OPEN CR_PROTOCOL_CLOSEOUT
FETCH NEXT FROM CR_PROTOCOL_CLOSEOUT INTO @l_VER_SUB_TO_OPS

WHILE (@@FETCH_STATUS = 0)
BEGIN

IF(LEN(@l_VER_SUB_TO_OPS_Vch)=0)
SET @l_VER_SUB_TO_OPS_Vch = ISNULL(@l_VER_SUB_TO_OPS,'')
ELSE
SET @l_VER_SUB_TO_OPS_Vch = @l_VER_SUB_TO_OPS_Vch+CHAR(10)+ISNULL(@l_VER_SUB_TO_OPS,'')

FETCH NEXT FROM CR_PROTOCOL_CLOSEOUT INTO @l_VER_SUB_TO_OPS
END

CLOSE CR_PROTOCOL_CLOSEOUT
DEALLOCATE CR_PROTOCOL_CLOSEOUT

-- Calculate Review with Team fields
DECLARE @l_CSRC_REV_WITH_TEAM_Vch VARCHAR(50)
DECLARE @l_PSRC_REV_WITH_TEAM_Vch VARCHAR(50)
DECLARE @l_REG_REV_WITH_TEAM_Vch VARCHAR(50)
DECLARE @l_MO_REV_WITH_TEAM_Vch VARCHAR(50)

SET @l_CSRC_REV_WITH_TEAM_Vch = ''
SET @l_PSRC_REV_WITH_TEAM_Vch = ''
SET @l_REG_REV_WITH_TEAM_Vch = ''
SET @l_MO_REV_WITH_TEAM_Vch = ''

SELECT @l_REG_REV_WITH_TEAM_Vch=DATEDIFF(D,(SELECT MIN(RRS_CTS_SENT_DATE) FROM #TEMP_REG_REVIEWS),
(SELECT MAX(SVW_RCC_RECEIVED_DATE) FROM #TEMP_MO_REVIEWS))

SELECT @l_MO_REV_WITH_TEAM_Vch=DATEDIFF(D,(SELECT MIN(@l_MO_CTS_RETURNED_DATE) FROM #TEMP_MO_REVIEWS),
(SELECT MAX(@l_FRS_CTS_SENT_DATE) FROM #TEMP_FINAL_RAB))

-- Calculate Total DAIDS Review Time
DECLARE @l_TOTAL_DAIDS_REVIEW_TIME_Vch VARCHAR(50)
SET @l_TOTAL_DAIDS_REVIEW_TIME_Vch=''

SET @l_TOTAL_DAIDS_REVIEW_TIME_Vch = @l_CSRC_ELAPSED_CALENDAR_DAYS_Int+@l_PSRC_ELAPSED_CALENDAR_DAYS_Int+@l_REG_ELAPSED_CALENDAR_DAYS_Int+
@l_MO_ELAPSED_CALENDAR_DAYS_Int+@l_FINAL_RAB_ELAPSED_DAYS_Int


IF EXISTS(SELECT SRI_ACTUAL_REVIEW_DATE FROM #TEMP_CSRC_REVIEWS)
BEGIN
SELECT STU_ID,SVB_ID,CONVERT(VARCHAR(12),STU_NUMBER) +' ('+STU_LEGACY_PROTOCOL_NUMBER_TEXT+')' AS STUDY_NUMBER,
STU_NUMBER,STU_LEGACY_PROTOCOL_NUMBER_TEXT,SSO_OTY_ID,STU_CURRENT_STUDY_STATUS_TYPE,
SVB_TO_BE_VERSION_NUMBER,VOYAGER.VOYAGER_AP_GET_IND_NUMBERS_GIVEN_STU_ID_FUN(STU_ID)AS 'INDNumber',
(SELECT MAX(CONVERT(VARCHAR(10),SST_STATUS_DATE,101)) FROM STUDY_STATUSES
JOIN STATUSES ON STT_ID=SST_STT_ID AND STT_STATUS_NAME='In Development' WHERE SST_STU_ID=STU_ID)
AS IN_DEVELOP_STATUS_DATE,
(SELECT MAX(CONVERT(VARCHAR(10),SST_STATUS_DATE,101)) FROM STUDY_STATUSES
JOIN STATUSES ON STT_ID=SST_STT_ID AND STT_STATUS_NAME='Open to Accrual' WHERE SST_STU_ID=STU_ID)
AS OPEN_TO_ACCRUAL_DATE,
DATEDIFF(D,(SELECT MAX(SST_STATUS_DATE) FROM STUDY_STATUSES JOIN STATUSES ON STT_ID=SST_STT_ID
AND STT_STATUS_NAME='In Development' WHERE SST_STU_ID=STU_ID),
(SELECT MAX(SST_STATUS_DATE) FROM STUDY_STATUSES JOIN STATUSES ON STT_ID=SST_STT_ID
AND STT_STATUS_NAME='Open to Accrual' WHERE SST_STU_ID=STU_ID)) AS TOTAL_DEVELOP_TIME,
(SELECT TOP 1 TME_PER_ID FROM VOYAGER.TEAM_MEMBERS JOIN DAIDSES.PARTY_CONTEXT_ROLES ON TME_PCL_ID = PCL_ID
AND PCL_ROLE_TYPE = 'Regulatory Specialist' AND PCL_ROLE_CATEGORY_TYPE = 'Regulatory Team' WHERE TME_STU_ID=STU_ID) AS RC_SPECIALIST_ID,
'CSRC' AS SRC_REVIEW_TYPE,@l_CSRC_REV_RECD_AT_RCC_Vch AS SRC_RCC_RECD_DATE,
@l_CSRC_ACTUAL_REVIEW_DATE_Vch AS SRC_ACTUAL_REVIEW_DATE,@l_CSRC_DISTRIBUTED_DATE_Vch AS SRC_DISTRIBUTED_DATE,
@l_CSRC_ELAPSED_CALENDAR_DAYS_Vch AS SRC_ELAPSED_CALENDAR_DAYS,'' AS SRC_ELAPSED_CALENDAR_DAYS_EXT,
@l_CSRC_APPROVAL_DATE_Vch AS SRC_APPROVAL_DATE,@l_CSRC_COMMENTS_WITH_TEAM_Vch AS SRC_COMMENTS_WITH_TEAM,
@l_REG_REV_RECD_AT_RCC_Vch AS REG_REV_RECD_AT_RCC,@l_REG_CTS_SENT_DATE_Vch AS REG_CTS_SENT_DATE,
@l_REG_ELAPSED_CALENDAR_DAYS_Vch AS REG_ELAPSED_CALENDAR_DAYS,@l_MO_REV_RECD_AT_RCC_Vch AS MO_REV_RECD_AT_RCC,
@l_MRS_MO_SUBMITTED_DATE_Vch AS MRS_MO_SUBMITTED_DATE,@l_MO_RECEIVED_FROM_MO_Vch AS MO_RECEIVED_FROM_MO,
@l_MO_DECISION_TYPE_Vch AS MO_DECISION_TYPE,@l_MO_CTS_RETURNED_DATE_Vch AS MO_CTS_RETURNED_DATE,
@l_MO_ELAPSED_CALENDAR_DAYS_Vch AS MO_ELAPSED_CALENDAR_DAYS,
@l_MO_REV_WITH_TEAM_Vch AS MO_COMMENTS_WITH_TEAM,@l_FINAL_RAB_RECD_AT_RCC_Vch AS FINAL_RAB_RECD_AT_RCC,
@l_FRS_RAB_SENT_DATE_Vch AS FRS_RAB_SENT_DATE,@l_FINAL_FROM_RAB_Vch AS FINAL_FROM_RAB,@l_FRS_CTS_SENT_DATE_Vch AS
FRS_CTS_SENT_DATE,'' AS FINAL_RAB_COMMENTS_ON_FINAL_PROTOCOL,
@l_FINAL_RAB_DECISION_TYPE_Vch AS FINAL_RAB_DECISION_TYPE,@l_FINAL_RAB_ELAPSED_DAYS_Vch AS FINAL_RAB_ELAPSED_DAYS,
@l_VER_SUB_TO_OPS_Vch AS VER_SUB_TO_OPS,@l_REG_REV_WITH_TEAM_Vch AS REG_REV_WITH_TEAM,
@l_TOTAL_DAIDS_REVIEW_TIME_Vch AS TOTAL_DAIDS_REVIEW_TIME
FROM VOYAGER.STUDIES JOIN VOYAGER.STUDY_VERSIONS_TO_BE ON SVB_STU_ID=STU_ID JOIN VOYAGER.STUDY_STAKEHOLDER_ORGANIZATIONS ON SSO_STU_ID=STU_ID
AND SSO_ROLE_TYPE='Network'
WHERE SVB_ID=@l_SVB_ID_Num
AND
(
(@SelConstraint_Network='Y' AND SSO_OTY_ID IN
( SELECT RUV_SELECTED_VALUE_TEXT FROM DAIDSES.REPORT_USER_VALUES
WHERE RUV_SESSION_GUID = @SessionGUID
AND RUV_RCO_ID = @SelConstraint_Network_id
AND RUV_RECORD_STATUS_FLAG = 'Active'
)
)
OR
(@SelConstraint_Network='N')
)
AND
(
(@SelConstraint_ProtocolStatus='Y' AND STU_CURRENT_STUDY_STATUS_TYPE IN
( SELECT RUV_SELECTED_VALUE_TEXT FROM DAIDSES.REPORT_USER_VALUES
WHERE RUV_SESSION_GUID = @SessionGUID
AND RUV_RCO_ID = @SelConstraint_ProtocolStatus_id
AND RUV_RECORD_STATUS_FLAG = 'Active'
)
)
OR
(@SelConstraint_ProtocolStatus='N')
)
AND
(
(@SelConstraint_RCSpecialist='Y' AND (SELECT COUNT(TME_PER_ID) FROM VOYAGER.TEAM_MEMBERS JOIN DAIDSES.PARTY_CONTEXT_ROLES ON TME_PCL_ID = PCL_ID
AND PCL_ROLE_TYPE = 'Regulatory Specialist' AND PCL_ROLE_CATEGORY_TYPE = 'Regulatory Team' WHERE TME_STU_ID=STU_ID AND TME_PER_ID IN
( SELECT RUV_SELECTED_VALUE_TEXT FROM DAIDSES.REPORT_USER_VALUES
WHERE RUV_SESSION_GUID = @SessionGUID
AND RUV_RCO_ID = @SelConstraint_RCSpecialist_id
AND RUV_RECORD_STATUS_FLAG = 'Active'
))>0
)
OR
(@SelConstraint_RCSpecialist='N')
)
AND
(
(@SelConstraint_STU_NUMBER='Y' AND STU_LEGACY_PROTOCOL_NUMBER_TEXT IN
( SELECT RUV_SELECTED_VALUE_TEXT FROM DAIDSES.REPORT_USER_VALUES
WHERE RUV_SESSION_GUID = @SessionGUID
AND RUV_RCO_ID = @SelConstraint_STU_NUMBER_id
AND RUV_RECORD_STATUS_FLAG = 'Active'
)
)
OR
(@SelConstraint_STU_NUMBER='N')
)
AND
(
(@SelConstraint_RegRevRCCRecdDate='Y' AND (SELECT COUNT(SVW_RCC_RECEIVED_DATE) FROM #TEMP_REG_REVIEWS WHERE SVW_RCC_RECEIVED_DATE BETWEEN
@SelConstraint_RegRevRCCRecdDate_From AND @SelConstraint_RegRevRCCRecdDate_To) >0
)
OR
(@SelConstraint_RegRevRCCRecdDate='N')
)
AND
(
(@SelConstraint_SRCRevRCCRecdDate='Y' AND (SELECT COUNT(SVW_RCC_RECEIVED_DATE) FROM #TEMP_CSRC_REVIEWS WHERE SVW_RCC_RECEIVED_DATE BETWEEN
@SelConstraint_SRCRevRCCRecdDate_From AND @SelConstraint_SRCRevRCCRecdDate_To) >0
)
OR
(@SelConstraint_SRCRevRCCRecdDate='N')
)

END
ELSE
BEGIN
IF EXISTS (SELECT SRI_ACTUAL_REVIEW_DATE FROM #TEMP_PSRC_REVIEWS)
BEGIN
SELECT STU_ID,SVB_ID,CONVERT(VARCHAR(12),STU_NUMBER) +' ('+STU_LEGACY_PROTOCOL_NUMBER_TEXT+')' AS STUDY_NUMBER,
STU_NUMBER,STU_LEGACY_PROTOCOL_NUMBER_TEXT,SSO_OTY_ID,STU_CURRENT_STUDY_STATUS_TYPE,
SVB_TO_BE_VERSION_NUMBER,VOYAGER.VOYAGER_AP_GET_IND_NUMBERS_GIVEN_STU_ID_FUN(STU_ID)AS 'INDNumber',
(SELECT MAX(CONVERT(VARCHAR(10),SST_STATUS_DATE,101)) FROM STUDY_STATUSES
JOIN STATUSES ON STT_ID=SST_STT_ID AND STT_STATUS_NAME='In Development' WHERE SST_STU_ID=STU_ID)
AS IN_DEVELOP_STATUS_DATE,
(SELECT MAX(CONVERT(VARCHAR(10),SST_STATUS_DATE,101)) FROM STUDY_STATUSES
JOIN STATUSES ON STT_ID=SST_STT_ID AND STT_STATUS_NAME='Open to Accrual' WHERE SST_STU_ID=STU_ID)
AS OPEN_TO_ACCRUAL_DATE,
DATEDIFF(D,(SELECT MAX(SST_STATUS_DATE) FROM STUDY_STATUSES JOIN STATUSES ON STT_ID=SST_STT_ID
AND STT_STATUS_NAME='In Development' WHERE SST_STU_ID=STU_ID),
(SELECT MAX(SST_STATUS_DATE) FROM STUDY_STATUSES JOIN STATUSES ON STT_ID=SST_STT_ID
AND STT_STATUS_NAME='Open to Accrual' WHERE SST_STU_ID=STU_ID)) AS TOTAL_DEVELOP_TIME,
'PSRC' AS SRC_REVIEW_TYPE,@l_PSRC_REV_RECD_AT_RCC_Vch AS SRC_RCC_RECD_DATE,
@l_PSRC_ACTUAL_REVIEW_DATE_Vch AS SRC_ACTUAL_REVIEW_DATE,
@l_PSRC_DISTRIBUTED_DATE_Vch AS SRC_DISTRIBUTED_DATE,
@l_PSRC_ELAPSED_CALENDAR_DAYS_Vch AS SRC_ELAPSED_CALENDAR_DAYS,'' AS SRC_ELAPSED_CALENDAR_DAYS_EXT,
@l_PSRC_APPROVAL_DATE_Vch AS SRC_APPROVAL_DATE,@l_PSRC_COMMENTS_WITH_TEAM_Vch AS SRC_COMMENTS_WITH_TEAM,
@l_REG_REV_RECD_AT_RCC_Vch AS REG_REV_RECD_AT_RCC,@l_REG_CTS_SENT_DATE_Vch AS REG_CTS_SENT_DATE,
@l_REG_ELAPSED_CALENDAR_DAYS_Vch AS REG_ELAPSED_CALENDAR_DAYS,@l_MO_REV_RECD_AT_RCC_Vch AS MO_REV_RECD_AT_RCC,
@l_MRS_MO_SUBMITTED_DATE_Vch AS MRS_MO_SUBMITTED_DATE,@l_MO_RECEIVED_FROM_MO_Vch AS MO_RECEIVED_FROM_MO,
@l_MO_DECISION_TYPE_Vch AS MO_DECISION_TYPE,@l_MO_CTS_RETURNED_DATE_Vch AS MO_CTS_RETURNED_DATE,
@l_MO_ELAPSED_CALENDAR_DAYS_Vch AS MO_ELAPSED_CALENDAR_DAYS,
@l_MO_REV_WITH_TEAM_Vch AS MO_COMMENTS_WITH_TEAM,@l_FINAL_RAB_RECD_AT_RCC_Vch AS FINAL_RAB_RECD_AT_RCC,
@l_FRS_RAB_SENT_DATE_Vch AS FRS_RAB_SENT_DATE,@l_FINAL_FROM_RAB_Vch AS FINAL_FROM_RAB,
@l_FRS_CTS_SENT_DATE_Vch AS FRS_CTS_SENT_DATE,'' AS FINAL_RAB_COMMENTS_ON_FINAL_PROTOCOL,
@l_FINAL_RAB_DECISION_TYPE_Vch AS FINAL_RAB_DECISION_TYPE,
@l_FINAL_RAB_ELAPSED_DAYS_Vch AS FINAL_RAB_ELAPSED_DAYS,@l_VER_SUB_TO_OPS_Vch AS VER_SUB_TO_OPS,
@l_REG_REV_WITH_TEAM_Vch AS REG_REV_WITH_TEAM,@l_TOTAL_DAIDS_REVIEW_TIME_Vch AS TOTAL_DAIDS_REVIEW_TIME
FROM VOYAGER.STUDIES JOIN VOYAGER.STUDY_VERSIONS_TO_BE ON SVB_STU_ID=STU_ID JOIN VOYAGER.STUDY_STAKEHOLDER_ORGANIZATIONS ON SSO_STU_ID=STU_ID
AND SSO_ROLE_TYPE='Network'
WHERE SVB_ID=@l_SVB_ID_Num
AND
(
(@SelConstraint_Network='Y' AND SSO_OTY_ID IN
( SELECT RUV_SELECTED_VALUE_TEXT FROM DAIDSES.REPORT_USER_VALUES
WHERE RUV_SESSION_GUID = @SessionGUID
AND RUV_RCO_ID = @SelConstraint_Network_id
AND RUV_RECORD_STATUS_FLAG = 'Active'
)
)
OR
(@SelConstraint_Network='N')
)
AND
(
(@SelConstraint_ProtocolStatus='Y' AND STU_CURRENT_STUDY_STATUS_TYPE IN
( SELECT RUV_SELECTED_VALUE_TEXT FROM DAIDSES.REPORT_USER_VALUES
WHERE RUV_SESSION_GUID = @SessionGUID
AND RUV_RCO_ID = @SelConstraint_ProtocolStatus_id
AND RUV_RECORD_STATUS_FLAG = 'Active'
)
)
OR
(@SelConstraint_ProtocolStatus='N')
)
AND
(
(@SelConstraint_RCSpecialist='Y' AND (SELECT COUNT(TME_PER_ID) FROM VOYAGER.TEAM_MEMBERS JOIN DAIDSES.PARTY_CONTEXT_ROLES ON TME_PCL_ID = PCL_ID
AND PCL_ROLE_TYPE = 'Regulatory Specialist' AND PCL_ROLE_CATEGORY_TYPE = 'Regulatory Team' WHERE TME_STU_ID=STU_ID AND TME_PER_ID IN
( SELECT RUV_SELECTED_VALUE_TEXT FROM DAIDSES.REPORT_USER_VALUES
WHERE RUV_SESSION_GUID = @SessionGUID
AND RUV_RCO_ID = @SelConstraint_RCSpecialist_id
AND RUV_RECORD_STATUS_FLAG = 'Active'
))>0
)
OR
(@SelConstraint_RCSpecialist='N')
)
AND
(
(@SelConstraint_STU_NUMBER='Y' AND STU_LEGACY_PROTOCOL_NUMBER_TEXT IN
( SELECT RUV_SELECTED_VALUE_TEXT FROM DAIDSES.REPORT_USER_VALUES
WHERE RUV_SESSION_GUID = @SessionGUID
AND RUV_RCO_ID = @SelConstraint_STU_NUMBER_id
AND RUV_RECORD_STATUS_FLAG = 'Active'
)
)
OR
(@SelConstraint_STU_NUMBER='N')
)
AND
(
(@SelConstraint_RegRevRCCRecdDate='Y' AND (SELECT COUNT(SVW_RCC_RECEIVED_DATE) FROM #TEMP_REG_REVIEWS WHERE SVW_RCC_RECEIVED_DATE BETWEEN
@SelConstraint_RegRevRCCRecdDate_From AND @SelConstraint_RegRevRCCRecdDate_To) >0
)
OR
(@SelConstraint_RegRevRCCRecdDate='N')
)
AND
(
(@SelConstraint_SRCRevRCCRecdDate='Y' AND (SELECT COUNT(SVW_RCC_RECEIVED_DATE) FROM #TEMP_CSRC_REVIEWS WHERE SVW_RCC_RECEIVED_DATE BETWEEN
@SelConstraint_SRCRevRCCRecdDate_From AND @SelConstraint_SRCRevRCCRecdDate_To) >0
)
OR
(@SelConstraint_SRCRevRCCRecdDate='N')
)
END

END

DROP TABLE #TEMP_STUDY_VERSIONS_FOR_TO_BE_VERSION
DROP TABLE #TEMP_CSRC_REVIEWS
DROP TABLE #TEMP_PSRC_REVIEWS
DROP TABLE #TEMP_REG_REVIEWS
DROP TABLE #TEMP_MO_REVIEWS
DROP TABLE #TEMP_FINAL_RAB

FETCH NEXT FROM CR_STUDY_VERSIONS_TO_BE INTO @l_SVB_ID_Num
END
CLOSE CR_STUDY_VERSIONS_TO_BE
DEALLOCATE CR_STUDY_VERSIONS_TO_BE
-- Main Outer Cursor End

END

thanks

 

[AlexCuse]

Dude, try posting that inside code blocks [ignore]

[/ignore]. It is a tall enough order to ask someone to read all that, may as well make it a little easier on the eyes.

It also may help to include why the performance is unacceptable (I presume it is a speed issue from your cursor?)

Good Luck,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[habesha]

having a look at the proc what is the first thing you suggest to change in the proc and by what

thanks

 

[AlexCuse]

as stated before, I am having trouble reading it.

I think the first thing you might look into is replacing your cursor with a while loop, as I'm pretty sure this can be done most of the time when cursors are used. This is simply based off my observation that you are using a cursor, not on any in depth analysis of your function. This may not be possible in your case, but you should check.

Good Luck,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[habesha]

how do we replace this with a while loop?

OPEN CR_STUDY_VERSIONS_TO_BE
FETCH NEXT FROM CR_STUDY_VERSIONS_TO_BE INTO @l_SVB_ID_Num
WHILE (@@FETCH_STATUS = 0)
BEGIN
--PRINT @l_SVB_ID_Num
FETCH NEXT FROM CR_STUDY_VERSIONS_TO_BE INTO @l_SVB_ID_Num
end

thanks

 

[SQLSister]

Replace the cursor not with a while loop (while loops are faster than cursors but set-based processing is still faster because while loops still act one record at a time)but with a set-based insert that inserts teh whole set at once. The insert will use case for the processing that you used with if statments. Simple version of a set-based insert with case processing to use to give you ideas as to what to do:

insert into table1 (field1, field2, field3)
select field1, case when field2 is null then 'Unknown' else field2 end,
case when field4 >10 then 'yes' when field4 = 5 then 'maybe' else 'no' end from  table2

Let this be a lesson to you that no code doing an insert or update or delete should ever be done with a cursor.

Questions about posting. See faq183-874

 

[habesha]

not clear what you saying, can you change and show me this code to set based.

DECLARE @l_SVB_ID_Num numeric

DECLARE CR_STUDY_VERSIONS_TO_BE CURSOR FOR
SELECT col1 FROM table1


OPEN CR_STUDY_VERSIONS_TO_BE
FETCH NEXT FROM CR_STUDY_VERSIONS_TO_BE INTO @l_SVB_ID_Num
WHILE (@@FETCH_STATUS = 0)
BEGIN
--PRINT @l_SVB_ID_Num
FETCH NEXT FROM CR_STUDY_VERSIONS_TO_BE INTO @l_SVB_ID_Num
end

thanks