here is the execution plan
|--Compute Scalar(DEFINE

[Expr1172]=If ([VOYAGER].[VOYAGER_AP_GET_STUDY_PRODUCTS_FLAG_FUN](Convert([A].[SVE_ID]), 'Study Agents')='Yes') then 'Yes' else [VOYAGER].[VOYAGER_AP_GET_STUDY_PRODUCTS_FLAG_FUN](Convert([A].[SVE_ID]), 'Study Vaccines')))
|--Sort(DISTINCT ORDER BY

[A].[SVE_ID] ASC, [A].[SVE_VERSION_NUMBER] ASC, [A].[SVE_TITLE_TEXT] ASC, [A].[SVE_SHORT_TITLE_TEXT] ASC, [Expr1119] ASC, [AE_REPORTING_MANUALS].[ARM_AE_MANUAL_NAME] ASC))
|--Filter(WHERE

([ses].[SES_RECORD_STATUS_FLAG]='Active' AND [ses].[SES_OTY_ID]=Convert([@p_In_OTY_ID_Int])) AND [ses].[SES_CURRENT_REGISTRATION_STATUS_TYPE]<>'Disapproved'))
|--Bookmark Lookup(BOOKMARK

[Bmk1041]), OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSION_SITES] AS [ses]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES

[STUDY_VERSIONS].[SVE_ID]) WITH PREFETCH)
|--Hash Match(Inner Join, HASH

[A].[SVE_ID])=([STUDY_VERSIONS].[SVE_ID]), RESIDUAL

[A].[SVE_ID]=[STUDY_VERSIONS].[SVE_ID]))
| |--Nested Loops(Inner Join)
| | |--Nested Loops(Inner Join, WHERE

[usr].[USE_PER_ID]=[Union1169]))
| | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Apparch].[USERS].[USE_ID_PK] AS [usr]), SEEK

[usr].[USE_ID]=Convert([@p_In_USER_ID_Int])), WHERE

[usr].[USE_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | |--Concatenation
| | | |--Table Scan(OBJECT

[Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE

((([PERSON_ROLES].[PRL_STU_ID]=Convert([@p_In_STU_ID_Int]) AND [PERSON_ROLES].[PRL_SITE_OTY_ID]=Convert([@p_In_OTY_ID_Int])) AND [PERSON_ROLES].[PRL_SITE_OTY_ID]<>NULL) AND [PERSON_ROLES].[PRL_STU_ID]<>NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | | |--Nested Loops(Inner Join)
| | | | |--Top(1)
| | | | | |--Filter(WHERE

[STUDY_VERSION_SITES].[SES_OTY_ID]=Convert([@p_In_OTY_ID_Int]) AND [STUDY_VERSION_SITES].[SES_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Bookmark Lookup(BOOKMARK

[Bmk1128]), OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSION_SITES]) WITH PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[STUDY_VERSIONS].[SVE_ID]))
| | | | | |--Nested Loops(Inner Join)
| | | | | | |--Nested Loops(Inner Join)
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK

[ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE

[ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK

[ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE

[ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDIES].[ix_STU_RECORD_STATUS_FLAG]), SEEK

[STUDIES].[STU_RECORD_STATUS_FLAG]='Active' AND [STUDIES].[STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | | |--Filter(WHERE

([STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=NULL AND [STUDY_VERSIONS].[SVE_DISPLAY_FOR_ALL_FLAG]='Yes') AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | | |--Bookmark Lookup(BOOKMARK

[Bmk1126]), OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS]))
| | | | | | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[ix_SVE_stu_ID]), SEEK

[STUDY_VERSIONS].[SVE_STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSION_SITES].[NIX_SES_SVE_ID]), SEEK

[STUDY_VERSION_SITES].[SES_SVE_ID]=[STUDY_VERSIONS].[SVE_ID]) ORDERED FORWARD)
| | | | |--Table Scan(OBJECT

[Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE

([PERSON_ROLES].[PRL_SITE_OTY_ID]=Convert([@p_In_OTY_ID_Int]) AND [PERSON_ROLES].[PRL_STU_ID]=NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | | |--Nested Loops(Inner Join)
| | | | |--Top(1)
| | | | | |--Filter(WHERE

[STUDY_VERSION_SITES].[SES_OTY_ID]=Convert([@p_In_OTY_ID_Int]) AND [STUDY_VERSION_SITES].[SES_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Bookmark Lookup(BOOKMARK

[Bmk1143]), OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSION_SITES]) WITH PREFETCH)
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[STUDY_VERSIONS].[SVE_ID]))
| | | | | |--Nested Loops(Inner Join)
| | | | | | |--Nested Loops(Inner Join)
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK

[ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE

[ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK

[ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE

[ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDIES].[ix_STU_RECORD_STATUS_FLAG]), SEEK

[STUDIES].[STU_RECORD_STATUS_FLAG]='Active' AND [STUDIES].[STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | | |--Filter(WHERE

([STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=NULL AND [STUDY_VERSIONS].[SVE_DISPLAY_FOR_ALL_FLAG]='Yes') AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | | |--Bookmark Lookup(BOOKMARK

[Bmk1141]), OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS]))
| | | | | | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[ix_SVE_stu_ID]), SEEK

[STUDY_VERSIONS].[SVE_STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSION_SITES].[NIX_SES_SVE_ID]), SEEK

[STUDY_VERSION_SITES].[SES_SVE_ID]=[STUDY_VERSIONS].[SVE_ID]) ORDERED FORWARD)
| | | | |--Table Scan(OBJECT

[Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE

(([PERSON_ROLES].[PRL_STU_ID]=Convert([@p_In_STU_ID_Int]) AND [PERSON_ROLES].[PRL_NETWORK_OTY_ID]=NULL) AND [PERSON_ROLES].[PRL_SITE_OTY_ID]=NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | | |--Hash Match(Inner Join, HASH

[ORGANIZATION_TYPES].[OTY_ID])=([PERSON_ROLES].[PRL_NETWORK_OTY_ID]), RESIDUAL

[ORGANIZATION_TYPES].[OTY_ID]=[PERSON_ROLES].[PRL_NETWORK_OTY_ID]))
| | | | |--Sort(DISTINCT ORDER BY

[ORGANIZATION_TYPES].[OTY_ID] ASC, [ORGANIZATIONS].[ORG_NAME] ASC))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[PARTY_PAIRS].[PPA_OTY_ESTABLISHED_FROM_ID]))
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_FROM_ID]))
| | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[PARTY_PAIR_RELATIONSHIPS].[PPR_CHA_ID]))
| | | | | | | | |--Hash Match(Inner Join, HASH

[PARTY_PAIRS].[PPA_ID])=([PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_TO_ID]), RESIDUAL

[PARTY_PAIRS].[PPA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_TO_ID]))
| | | | | | | | | |--Filter(WHERE

[PARTY_PAIRS].[PPA_OTY_ESTABLISHED_TO_ID]=Convert([@p_In_OTY_ID_Int]) AND [PARTY_PAIRS].[PPA_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | | | |--Bookmark Lookup(BOOKMARK

[Bmk1026]), OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIRS]) WITH PREFETCH)
| | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[PARTY_PAIR_COMBINATIONS].[PPC_ID]))
| | | | | | | | | | |--Hash Match(Inner Join, HASH

[PARTY_COMBINATION_ROLES].[PCR_ID])=([PARTY_PAIR_COMBINATIONS].[PPC_PCR_ROLE_DEFINED_TO_ID]), RESIDUAL

[PARTY_COMBINATION_ROLES].[PCR_ID]=[PARTY_PAIR_COMBINATIONS].[PPC_PCR_ROLE_DEFINED_TO_ID]))
| | | | | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK

[ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE

[ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK

[ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE

[ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | | | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Pegasus].[PARTY_COMBINATION_ROLES].[PCR_ID_PK]), WHERE

[PARTY_COMBINATION_ROLES].[PCR_RECORD_STATUS_FLAG]='Active' AND [PARTY_COMBINATION_ROLES].[PCR_ROLE_TEXT]='Clinical Research Site'))
| | | | | | | | | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIR_COMBINATIONS].[PPC_ID_PK]), WHERE

[PARTY_PAIR_COMBINATIONS].[PPC_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | | | |--Index Seek(OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIRS].[NIX_PPA_PPC_ID]), SEEK

[PARTY_PAIRS].[PPA_PPC_ID]=[PARTY_PAIR_COMBINATIONS].[PPC_ID]) ORDERED FORWARD)
| | | | | | | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIR_RELATIONSHIPS].[PPR_ID_PK]), WHERE

[PARTY_PAIR_RELATIONSHIPS].[PPR_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[CHAINS].[CHA_ID_PK]), SEEK

[CHAINS].[CHA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_CHA_ID]), WHERE

[CHAINS].[CHA_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIRS].[PPA_ID_PK]), SEEK

[PARTY_PAIRS].[PPA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_FROM_ID]), WHERE

[PARTY_PAIRS].[PPA_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK

[ORGANIZATION_TYPES].[OTY_ID]=[PARTY_PAIRS].[PPA_OTY_ESTABLISHED_FROM_ID]), WHERE

[ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active' AND [ORGANIZATION_TYPES].[OTY_TYPE]='Network') ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK

[ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE

[ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | |--Table Scan(OBJECT

[Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE

(([PERSON_ROLES].[PRL_STU_ID]=Convert([@p_In_STU_ID_Int]) AND [PERSON_ROLES].[PRL_SITE_OTY_ID]=NULL) AND [PERSON_ROLES].[PRL_STU_ID]<>NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | | |--Hash Match(Inner Join, HASH

[ORGANIZATION_TYPES].[OTY_ID])=([PERSON_ROLES].[PRL_NETWORK_OTY_ID]), RESIDUAL

[ORGANIZATION_TYPES].[OTY_ID]=[PERSON_ROLES].[PRL_NETWORK_OTY_ID]))
| | | |--Sort(DISTINCT ORDER BY

[ORGANIZATION_TYPES].[OTY_ID] ASC, [ORGANIZATIONS].[ORG_NAME] ASC))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[PARTY_PAIRS].[PPA_OTY_ESTABLISHED_FROM_ID]))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_FROM_ID]))
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[PARTY_PAIR_RELATIONSHIPS].[PPR_CHA_ID]))
| | | | | | | |--Hash Match(Inner Join, HASH

[PARTY_PAIRS].[PPA_ID])=([PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_TO_ID]), RESIDUAL

[PARTY_PAIRS].[PPA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_TO_ID]))
| | | | | | | | |--Filter(WHERE

[PARTY_PAIRS].[PPA_OTY_ESTABLISHED_TO_ID]=Convert([@p_In_OTY_ID_Int]) AND [PARTY_PAIRS].[PPA_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | | |--Bookmark Lookup(BOOKMARK

[Bmk1006]), OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIRS]) WITH PREFETCH)
| | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[PARTY_PAIR_COMBINATIONS].[PPC_ID]))
| | | | | | | | | |--Hash Match(Inner Join, HASH

[PARTY_COMBINATION_ROLES].[PCR_ID])=([PARTY_PAIR_COMBINATIONS].[PPC_PCR_ROLE_DEFINED_TO_ID]), RESIDUAL

[PARTY_COMBINATION_ROLES].[PCR_ID]=[PARTY_PAIR_COMBINATIONS].[PPC_PCR_ROLE_DEFINED_TO_ID]))
| | | | | | | | | | |--Nested Loops(Inner Join)
| | | | | | | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK

[ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE

[ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK

[ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE

[ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | | | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Pegasus].[PARTY_COMBINATION_ROLES].[PCR_ID_PK]), WHERE

[PARTY_COMBINATION_ROLES].[PCR_RECORD_STATUS_FLAG]='Active' AND [PARTY_COMBINATION_ROLES].[PCR_ROLE_TEXT]='Clinical Research Site'))
| | | | | | | | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIR_COMBINATIONS].[PPC_ID_PK]), WHERE

[PARTY_PAIR_COMBINATIONS].[PPC_RECORD_STATUS_FLAG]='Active'))
| | | | | | | | | |--Index Seek(OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIRS].[NIX_PPA_PPC_ID]), SEEK

[PARTY_PAIRS].[PPA_PPC_ID]=[PARTY_PAIR_COMBINATIONS].[PPC_ID]) ORDERED FORWARD)
| | | | | | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIR_RELATIONSHIPS].[PPR_ID_PK]), WHERE

[PARTY_PAIR_RELATIONSHIPS].[PPR_RECORD_STATUS_FLAG]='Active'))
| | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[CHAINS].[CHA_ID_PK]), SEEK

[CHAINS].[CHA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_CHA_ID]), WHERE

[CHAINS].[CHA_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[PARTY_PAIRS].[PPA_ID_PK]), SEEK

[PARTY_PAIRS].[PPA_ID]=[PARTY_PAIR_RELATIONSHIPS].[PPR_PPA_DEFINED_FROM_ID]), WHERE

[PARTY_PAIRS].[PPA_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK

[ORGANIZATION_TYPES].[OTY_ID]=[PARTY_PAIRS].[PPA_OTY_ESTABLISHED_FROM_ID]), WHERE

[ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active' AND [ORGANIZATION_TYPES].[OTY_TYPE]='Network') ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK

[ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE

[ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | |--Nested Loops(Inner Join)
| | | |--Top(1)
| | | | |--Filter(WHERE

[STUDY_VERSION_SITES].[SES_OTY_ID]=Convert([@p_In_OTY_ID_Int]) AND [STUDY_VERSION_SITES].[SES_RECORD_STATUS_FLAG]='Active'))
| | | | |--Bookmark Lookup(BOOKMARK

[Bmk1163]), OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSION_SITES]) WITH PREFETCH)
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[STUDY_VERSIONS].[SVE_ID]))
| | | | |--Nested Loops(Inner Join)
| | | | | |--Nested Loops(Inner Join)
| | | | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[ORGANIZATION_TYPES].[OTY_ORG_ID]))
| | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATION_TYPES].[OTY_ID_PK]), SEEK

[ORGANIZATION_TYPES].[OTY_ID]=Convert([@p_In_OTY_ID_Int])), WHERE

[ORGANIZATION_TYPES].[OTY_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | | |--Clustered Index Seek(OBJECT

[Daidsesxml].[Pegasus].[ORGANIZATIONS].[ORG_ID_PK]), SEEK

[ORGANIZATIONS].[ORG_ID]=[ORGANIZATION_TYPES].[OTY_ORG_ID]), WHERE

[ORGANIZATIONS].[ORG_RECORD_STATUS_FLAG]='Active') ORDERED FORWARD)
| | | | | | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDIES].[ix_STU_RECORD_STATUS_FLAG]), SEEK

[STUDIES].[STU_RECORD_STATUS_FLAG]='Active' AND [STUDIES].[STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | | |--Filter(WHERE

([STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=NULL AND [STUDY_VERSIONS].[SVE_DISPLAY_FOR_ALL_FLAG]='Yes') AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Bookmark Lookup(BOOKMARK

[Bmk1161]), OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS]))
| | | | | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[ix_SVE_stu_ID]), SEEK

[STUDY_VERSIONS].[SVE_STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| | | | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSION_SITES].[NIX_SES_SVE_ID]), SEEK

[STUDY_VERSION_SITES].[SES_SVE_ID]=[STUDY_VERSIONS].[SVE_ID]) ORDERED FORWARD)
| | | |--Table Scan(OBJECT

[Daidsesxml].[Apparch].[PERSON_ROLES]), WHERE

([PERSON_ROLES].[PRL_SITE_OTY_ID]=NULL AND [PERSON_ROLES].[PRL_STU_ID]=NULL) AND [PERSON_ROLES].[PRL_RECORD_STATUS_FLAG]='Active'))
| | |--Filter(WHERE

(([A].[SVE_DRAFT_APPROVED_FLAG]='Approved' AND [A].[SVE_CHANGE_TYPE]=NULL) AND [A].[SVE_CHANGE_NUMBER]=NULL) AND [A].[SVE_RECORD_STATUS_FLAG]='Active'))
| | |--Bookmark Lookup(BOOKMARK

[Bmk1040]), OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS] AS [A]))
| | |--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[ix_SVE_stu_ID] AS [A]), SEEK

[A].[SVE_STU_ID]=Convert([@p_In_STU_ID_Int])) ORDERED FORWARD)
| |--Filter(WHERE

(([@p_In_AE_REPORT_TYPE_Int]=0 AND ([Expr1119]='EAE' OR [Expr1119]='SAE')) OR ([@p_In_AE_REPORT_TYPE_Int]=1 AND [Expr1119]='EAE')) OR [@p_In_AE_REPORT_TYPE_Int]<>1 AND [@p_In_AE_REPORT_TYPE_Int]<>0))
| |--Compute Scalar(DEFINE

[STUDY_VERSIONS].[SVE_ID]=[STUDY_VERSIONS].[SVE_ID], [Expr1119]=If like(upper([DOMAIN_VALUES].[DVA_VALUE_TERM]), '%EAE%', NULL) then [DOMAIN_VALUES].[DVA_VALUE_TERM] else If [Expr1183] then 'EAE' else [DOMAIN_VALUES].[DVA_VALUE_TERM]))
| |--Nested Loops(Left Semi Join, WHERE

like(upper([DOMAIN_VALUES].[DVA_VALUE_TERM]), '%EAE%', NULL))OUTER REFERENCES

[STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), DEFINE

[Expr1183] = [PROBE VALUE]))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES

[STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]))
| | |--Hash Match(Right Outer Join, HASH

[AE_REPORTING_MANUALS].[ARM_ID])=([STUDY_VERSIONS].[SVE_ARM_ID]), RESIDUAL

[STUDY_VERSIONS].[SVE_ARM_ID]=[AE_REPORTING_MANUALS].[ARM_ID]))
| | | |--Table Scan(OBJECT

[Daidsesxml].[Voyager].[AE_REPORTING_MANUALS]))
| | | |--Hash Match(Inner Join, HASH

[DOMAIN_VALUES].[DVA_VALUE_CODE])=([STUDY_VERSIONS].[SVE_AE_REPORTING_METHOD_TYPE]), RESIDUAL

[STUDY_VERSIONS].[SVE_AE_REPORTING_METHOD_TYPE]=[DOMAIN_VALUES].[DVA_VALUE_CODE]))
| | | |--Bookmark Lookup(BOOKMARK

[Bmk1045]), OBJECT

[Daidsesxml].[Apparch].[DOMAIN_VALUES]))
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES

[DOMAIN_NAMES].[DNA_ID]))
| | | | |--Filter(WHERE

[DOMAIN_NAMES].[DNA_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Bookmark Lookup(BOOKMARK

[Bmk1047]), OBJECT

[Daidsesxml].[Apparch].[DOMAIN_NAMES]))
| | | | | |--Index Scan(OBJECT

[Daidsesxml].[Apparch].[DOMAIN_NAMES].[DNA_AVE_ID_NAME_UK]), WHERE

[DOMAIN_NAMES].[DNA_NAME]='Protocol AE Reporting Methods'))
| | | | |--Index Seek(OBJECT

[Daidsesxml].[Apparch].[DOMAIN_VALUES].[DVA_DNAID_VALUETERM_UK]), SEEK

[DOMAIN_VALUES].[DVA_DNA_ID]=[DOMAIN_NAMES].[DNA_ID]) ORDERED FORWARD)
| | | |--Filter(WHERE

((([STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved') OR ([Expr1185]=NULL AND [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1098])) OR [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1102]) OR [Expr1186]=NULL))
| | | |--Nested Loops(Left Semi Join, WHERE

(([STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved') OR ([Expr1185]=NULL AND [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1098])) OR [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1102])OUTER REFERENCES

[STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), DEFINE

[Expr1186] = [PROBE VALUE]))
| | | |--Nested Loops(Inner Join, WHERE

([STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved') OR ([Expr1185]=NULL AND [STUDY_VERSIONS].[SVE_CHANGE_NUMBER]=[Expr1098]))OUTER REFERENCES

[STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]))
| | | | |--Nested Loops(Inner Join, WHERE

[STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved')OUTER REFERENCES

[STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]))
| | | | | |--Nested Loops(Left Semi Join, WHERE

[STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved')OUTER REFERENCES

[STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), DEFINE

[Expr1185] = [PROBE VALUE]))
| | | | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]), WHERE

[STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | | |--Row Count Spool
| | | | | | |--Index Spool(SEEK

[STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved' AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| | | | | |--Hash Match(Cache, HASH

[STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), RESIDUAL

[STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID]))
| | | | | |--Stream Aggregate(DEFINE

[Expr1098]=MAX([STUDY_VERSIONS].[SVE_CHANGE_NUMBER])))
| | | | | |--Index Spool(SEEK

[STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved' AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| | | | |--Hash Match(Cache, HASH

[STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), RESIDUAL

[STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID]))
| | | | |--Stream Aggregate(DEFINE

[Expr1102]=MAX([STUDY_VERSIONS].[SVE_CHANGE_NUMBER])))
| | | | |--Index Spool(SEEK

[STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_CURRENT_VERSION_FLAG]='Yes' AND [STUDY_VERSIONS].[SVE_DRAFT_APPROVED_FLAG]='Approved' AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| | | |--Row Count Spool
| | | |--Filter(WHERE

[STUDY_VERSIONS].[SVE_CHANGE_TYPE]<>NULL))
| | | |--Index Spool(SEEK

[STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active'))
| | | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| | |--Hash Match(Cache, HASH

[STUDY_VERSIONS].[SVE_VERSION_NUMBER], [STUDY_VERSIONS].[SVE_STU_ID]), RESIDUAL

[STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID]))
| | |--Top(1)
| | |--Compute Scalar(DEFINE

[STUDY_VERSIONS].[SVE_ID]=[STUDY_VERSIONS].[SVE_ID]))
| | |--Index Spool(SEEK

[STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID] AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_CHANGE_TYPE]=NULL))
| | |--Clustered Index Scan(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
| |--Row Count Spool
| |--Nested Loops(Inner Join, WHERE

[STUDY_VERSIONS].[SVE_AE_REPORTING_METHOD_TYPE]=[DOMAIN_VALUES].[DVA_VALUE_CODE]))
| |--Bookmark Lookup(BOOKMARK

[Bmk1114]), OBJECT

[Daidsesxml].[Apparch].[DOMAIN_VALUES]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES

[DOMAIN_NAMES].[DNA_ID]))
| | |--Filter(WHERE

[DOMAIN_NAMES].[DNA_RECORD_STATUS_FLAG]='Active'))
| | | |--Bookmark Lookup(BOOKMARK

[Bmk1116]), OBJECT

[Daidsesxml].[Apparch].[DOMAIN_NAMES]))
| | | |--Index Scan(OBJECT

[Daidsesxml].[Apparch].[DOMAIN_NAMES].[DNA_AVE_ID_NAME_UK]), WHERE

[DOMAIN_NAMES].[DNA_NAME]='Protocol AE Reporting Methods'))
| | |--Index Seek(OBJECT

[Daidsesxml].[Apparch].[DOMAIN_VALUES].[DVA_DNAID_VALUETERM_UK]), SEEK

[DOMAIN_VALUES].[DVA_DNA_ID]=[DOMAIN_NAMES].[DNA_ID]), WHERE

like([DOMAIN_VALUES].[DVA_VALUE_TERM], '%EAE%', NULL)) ORDERED FORWARD)
| |--Index Spool(SEEK

[STUDY_VERSIONS].[SVE_RECORD_STATUS_FLAG]='Active' AND [STUDY_VERSIONS].[SVE_VERSION_NUMBER]=[STUDY_VERSIONS].[SVE_VERSION_NUMBER] AND [STUDY_VERSIONS].[SVE_STU_ID]=[STUDY_VERSIONS].[SVE_STU_ID]))
| |--Clustered Index Scan(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSIONS].[SVE_ID_PK]))
|--Index Seek(OBJECT

[Daidsesxml].[Voyager].[STUDY_VERSION_SITES].[NIX_SES_SVE_ID] AS [ses]), SEEK

[ses].[SES_SVE_ID]=[STUDY_VERSIONS].[SVE_ID]) ORDERED FORWARD)