July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.
select qry1.visit_id, qry1.protocol_name, qry1.visit_status_name, qry1.start_date, qry1.end_date, qry1.facility_id, qry1.facility_name, qry1.city, qry1.country, ms_apps_utilities.get_user_full_name(qry1.facility_contact) facility_contact, k.user_firstname||chr(32)||k.user_lastname GSM, qry1.visit_type, qry1.include_cav, qry1.facility_profile_date_sent, qry1.facility_profile_date_submit, i.tpa_companies, ms_apps_utilities.get_user_full_name(qry1.sr_program_manager) sr_program_mgr, ms_apps_utilities.get_user_full_name(qry1.onsite_lead) onsite_lead, ms_apps_utilities.get_user_full_name(qry1.apple_lead) apple_lead, ms_apps_utilities.get_user_full_name(qry1.apple_verification_lead) verification_lead, nvl(h.findings_count,0) findings_count, nvl(j.ca_count, 0) + nvl(j.pa_count, 0) capa_count from (select a.visit_id, a.protocol_name, a.visit_status, get_display_value('MS SRA VISIT STATUS', a.visit_status) visit_status_name, a.start_date, a.end_date, a.gsm, b.facility_id, b.facility_name, b.facility_contact, b.city, b.country, INITCAP(a.surprise) surprise, get_display_value('MS SRA VISIT TYPE',a.visit_type) visit_type, INITCAP(a.include_cav) include_cav, a.facility_profile_date_sent, a.facility_profile_date_submit, a.sr_program_manager, a.onsite_lead, a.apple_lead, a.apple_verification_lead from ms_sra_visit_setup a inner join (SELECT * from (select VISIT_ID ,FACILITY_ID ,FACILITY_CONTACT ,DD_OBJECT_TYPE ,facility_name ,city ,state_province ,country ,facility_address ,FACILITY_LOCAL_NAME ,facility_local_address ,LONGITUDE ,LATITUDE ,Row_number() over ( PARTITION BY visit_id ORDER BY CASE WHEN DD_OBJECT_TYPE = 'MS_SRA_VISIT' THEN 1 WHEN DD_OBJECT_TYPE = 'MS_SRA_VISIT_SETUP' then 2 end) RN FROM sgm.ms_sra_facility_info where DD_OBJECT_TYPE in ( 'MS_SRA_VISIT', 'MS_SRA_VISIT_SETUP' )) where RN = 1) b on a.visit_id = b.visit_id) qry1 left outer join (select visit_id, count(finding_id) findings_count from ms_sra_visit_findings group by visit_id) h on qry1.visit_id = h.visit_id left outer join (select a.visit_id, wm_concat(b.org_entity_name) tpa_companies from ms_sra_visit_setup_tpa a inner join si_org_entities_t b on a.audit_company = b.org_entity_id group by a.visit_id) i on qry1.visit_id = i.visit_id left outer join (select visit_id, sum(case when act_type = 'C' then 1 else 0 end) ca_count, sum(case when act_type = 'P' then 1 else 0 end) pa_count from ms_sra_visit_actions group by visit_id) j on qry1.visit_id = j.visit_id left outer join (select distinct user_ds_id,user_firstname,user_lastname from ms_sra_sis_facilities_cnt) k on qry1.gsm = k.user_ds_id WHERE (qry1.visit_id = :Visit_ID or :Visit_ID is null) AND (qry1.protocol_name = :Protocol or :Protocol is null) AND (qry1.Start_Date >= :Start_Date OR :Start_Date IS NULL) AND (qry1.End_Date >= :Start_Date OR :Start_Date IS NULL) AND (qry1.Start_Date <= :End_Date OR :End_Date IS NULL) AND (qry1.End_Date <= :End_Date OR :End_Date IS NULL) AND (qry1.facility_id = :Facility_ID or :Facility_ID is null) AND (qry1.facility_name = :Facility_Name or :Facility_Name is null) AND (qry1.city = :Facility_City or :Facility_City is null) AND (qry1.country = :Facility_Country or :Facility_Country is null) AND (qry1.surprise = :Surprise_Visit or :Surprise_Visit is null) AND (qry1.include_cav = :Include_CAV or :Include_CAV is null) AND (qry1.visit_type = :Visit_Type or :Visit_Type is null) AND (qry1.apple_verification_lead = :Apple_Verification_Lead or :Apple_Verification_Lead is null) AND (qry1.apple_lead = :Apple_Lead or :Apple_Lead is null) AND (qry1.onsite_lead = :Onsite_Lead or :Onsite_Lead is null) AND (qry1.sr_program_manager = :Sr_Program_Manager or :Sr_Program_Manager is null) AND (qry1.gsm = :GSM or :GSM is null) AND (qry1.visit_status_name = :Status or :Status is null) AND (TO_CHAR(qry1.facility_profile_date_sent, 'mm/dd/yyyy') = TO_CHAR(:F_P_Last_Sent_Date, 'mm/dd/yyyy') OR :F_P_Last_Sent_Date is null) AND (TO_CHAR(qry1.facility_profile_date_submit, 'mm/dd/yyyy') = TO_CHAR(:F_P_Last_Comp_Date, 'mm/dd/yyyy') OR :F_P_Last_Comp_Date is null) AND ((i.tpa_companies like '%'||:TPA_Companies||'%') or (:TPA_Companies is null))
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.