SELECT TO_CHAR(orp.mdm_pat_id) AS "LH Patient ID", TO_CHAR(orps.presc_key) AS "LH Prescriber ID", 55555 || orps.presc_key AS "LH Account ID", -- TO_CHAR(orpr1.pyr_key) AS "LH Primary Payer Plan ID",/*MODIFIED FOR HIPAA*/ --CASE WHEN orpr1.pyr_key in (select pyr_key from LH_ODS.LH_pyr_pat_hipaa_flag) CASE WHEN ORP.HIPAA_FLAG ='N' AND ORPR1.REDACT_FLAG IS NOT NULL THEN TO_CHAR(orpr1.REDACT_PYR_KEY) ELSE TO_CHAR(orpr1.pyr_key) END AS "LH Primary Payer Plan ID",--FOR HIPAA TO_CHAR(nvl(ohd.prmry_mdcl_pyr_key,-1)) AS "LH Primary Medical Payer ID", TO_CHAR(nvl(ohd.scndry_mdcl_pyr_key,-1)) AS "LH Secondary Medical Payer ID", TO_CHAR(nvl(ohd.prmry_phrm_pyr_key,-1)) AS "LH Primary Pharmacy Payer ID", TO_CHAR(nvl(ohd.scndry_phrm_pyr_key,-1)) AS "LH Secondary Pharmacy Payer ID", --TO_CHAR(orpr2.pyr_key) AS "LH Secondary Payer Plan ID",/*MODIFIED FOR HIPAA*/ --CASE WHEN orpr2.pyr_key in (select pyr_key from LH_ODS.LH_pyr_pat_hipaa_flag) CASE WHEN ORP.HIPAA_FLAG ='N' AND ORPR2.REDACT_FLAG IS NOT NULL THEN TO_CHAR(orpr2.REDACT_PYR_KEY) ELSE TO_CHAR(orpr2.pyr_key) END AS "LH Secondary Payer Plan ID",--FOR HIPAA ohd.record_type AS "Record Type", --ohd.record_subtype AS "Subtype", DECODE(upper(ohd.record_subtype),'RAPIDSTART','ENTYVIO START',ohd.record_subtype) AS "Subtype", ohd.curr_rfrl_stat_reas_cd AS "Status Reason Code", initcap(st_rea.description) AS "Status Reason Descrption", TO_CHAR(ohd.curr_rfrl_stat_dt,'YYYYMMDD HH24:MI:SS') AS "Status Date / Time", --ohd.service_reqst_no AS "Service Request",/*MODIFIED FOR HIPAA*/ CASE WHEN ORP.HIPAA_FLAG='N' THEN ohd.HIPAA_service_reqst_no ELSE ohd.service_reqst_no END AS "Service Request",--FOR HIPAA ( CASE WHEN ohd.record_type = 'CM' THEN 'Closed' ELSE ohd.curr_rfrl_stat END ) AS "Status", ( CASE WHEN ohd.record_type = 'CM' THEN TO_CHAR(ohd.curr_rfrl_stat_dt,'YYYYMMDD') ELSE TO_CHAR(ohd.hub_sr_strt_dt,'YYYYMMDD') END ) AS "Start Date", ( CASE WHEN ohd.record_type = 'CM' THEN TO_CHAR(ohd.curr_rfrl_stat_dt,'YYYYMMDD') ELSE TO_CHAR(ohd.hub_sr_stat_dt,'YYYYMMDD') END ) AS "Status Date", ( CASE WHEN ohd.record_type = 'CM' THEN TO_CHAR(ohd.curr_rfrl_stat_dt,'YYYYMMDD') ELSE TO_CHAR(ohd.hub_sr_clse_dt,'YYYYMMDD') END ) AS "Close Date", ohd.brand_nm AS "Product", ohd.phrcmy_nm AS "Account Referred To", TO_CHAR(ohd.rfrl_strt_dt,'YYYYMMDD') AS "Referral Date", TO_CHAR(ohd.shipment_dt,'YYYYMMDD') AS "Shipment Date", ohd.pre_clncl_indctr AS "Clinical Trial", ohd.other_flex_col_2 AS "Clinical Trial ID", TO_CHAR(ohd.other_flex_col_26,'YYYYMMDD') AS "Provider Attestation", ohd.other_flex_col_4 AS "Consent Leave Msg", ohd.other_flex_col_5 AS "Preferred Contact Time", ohd.other_flex_col_7 AS "Primary Language", ohd.other_flex_col_8 AS "Alt Contact Available", ohd.other_flex_col_9 AS "Alt Contact Relationship", --ohd.pat_diagnosis_cd_1 AS "Primary Diagnosis Code",/*MODIFIED FOR HIPAA*/ case when orp.hipaa_flag='N' AND ohd.hipaa_pat_diagnosis_cd_1='EXCLUDE' THEN NULL else ohd.pat_diagnosis_cd_1 end AS "Primary Diagnosis Code",--FOR HIPAA --ohd.pat_diagnosis_cd_2 AS "Secondary Diagnosis Code",/*MODIFIED FOR HIPAA*/ case when orp.hipaa_flag='N' AND ohd.hipaa_pat_diagnosis_cd_2='EXCLUDE' THEN NULL else ohd.pat_diagnosis_cd_2 end AS "Secondary Diagnosis Code",--FOR HIPAA ohd.concurrent_products AS "Concurrent Treatment", ohd.pat_prev_site_care AS "Previous Treatment", TO_CHAR(ohd.pap_erolmnt_strt_dt,'YYYYMMDD') AS "Enrollment Start Date", TO_CHAR(ohd.pap_erolmnt_end_dt,'YYYYMMDD') AS "Enrollment End Date", --ohd.pat_household_size AS "Size of Household",/*MODIFIED FOR HIPAA*/ DECODE(orp.hipaa_flag,'N',ohd.HIPAA_PAT_HOUSEHOLD_SIZE,ohd.pat_household_size) AS "Size of Household",--FOR HIPAA --ohd.pat_annual_household_income AS "Annual household income",/*MODIFIED FOR HIPAA*/ DECODE(orp.hipaa_flag,'N',ohd.HIPAA_PAT_ANNUAL_HOUSEHOLD_INC,ohd.PAT_ANNUAL_HOUSEHOLD_INCOME) AS "Annual household income",--FOR HIPAA ohd.other_flex_col_22 AS "FPL Percentage", ohd.other_flex_col_10 AS "POI Type", CASE WHEN ohd.other_flex_col_11 = 'YES' THEN 'Y' WHEN ohd.other_flex_col_11 = 'NO' THEN 'N' END AS "Resides in US", ohd.other_flex_col_12 AS "Requestor Contact Type", orpr1.bnft_typ AS "Primary Benefit Type", ohd.ndc_no AS "Primary NDC", ohd.hcpcs_cd AS "Primary HCPCS", ohd.prcdr_cd1 AS "Primary Procedure Code", ohd.other_flex_col_13 AS "Primary CPT Modifier", ohd.prmry_copay_amt AS "Primary Copay", ohd.prmry_coinsur_amt AS "Primary Coinsurance", ohd.prmry_deductible AS "Primary Annual Deductible", ohd.prmry_deductible_met AS "Primary Deductible Met", ohd.prmry_max_out_of_pckt_amt AS "Priimary Out of Pocket Maximum", ohd.other_flex_col_14 AS "Prmry Limitations-Restrictions", orp1p.pbm_nm AS "Prmry Pharmacy Benefit Manager", ------------- orpr2.bnft_typ AS "Secondary Benefit Type", ohd.other_flex_col_15 AS "Secondary NDC", ohd.hcpcs_cd1 AS "Secondary HCPCS", ohd.prcdr_cd2 AS "Secondary Procedure Code", ohd.other_flex_col_16 AS "Secondary CPT Modifier", ohd.scndry_copay_amt AS "Secondary Copay", ohd.scndry_coinsur_amt AS "Secondary Coinsurance", ohd.scndry_deductible AS "Secondary Annual Deductible", ohd.scndry_deductible_met AS "Secondary Deductible Met", ohd.scndry_max_out_of_pckt_amt AS "Sndry Out of Pocket Maximum", ohd.other_flex_col_17 AS "Sndry Limitations-Restrictions", orp2p.pbm_nm AS "Sndry Pharmacy Benefit Manager", ohd.prmry_pa_rqrd AS "Required Information", ohd.other_flex_col_18 AS "Effective Timeframe", ohd.prmry_pa_id AS "Authorization Number", TO_CHAR(ohd.pa_effct_strt_dt,'YYYYMMDD') AS "Effective Start Date", TO_CHAR(ohd.prmry_pa_exp_dt,'YYYYMMDD') AS "Effective End Date", ohd.pa_units_allwd AS "Number of Units Approved", ohd.authorized_refills AS "Treatments Approved", ohd.prcdr_cd AS "Claims Procedure Code", ohd.other_flex_col_19 AS "Claims CPT Modifier", ohd.hcpcs_cd2 AS "Claims HCPCS Code", ohd.other_flex_col_20 AS "Claims HCPCS Modifier", ohd.clms_outcome AS "Claims Denial/Underpaid Res Cd", ohd.clms_outcome_reas AS "Claims Denial/Underpaid Reason", TO_CHAR(ohd.clms_outcome_dt,'YYYYMMDD') AS "Claims Date Outcome Determined", ohd.other_flex_col_34 AS "Claims Required Information", ohd.other_flex_col_31 AS "Claims Effective Timeframe", --ohd.claim_auth_no AS "Claims Authorization Number",/*MODIFIED FOR HIPAA*/ CASE WHEN ORP.HIPAA_FLAG='N' THEN ohd.HIPAA_claim_auth_no ELSE ohd.claim_auth_no END AS "Claims Authorization Number",--FOR HIPAA ohd.hub_key, --TO_CHAR(ohd.mrkt_opt_in,'YYYYMMDD') AS "Marketing Opt In Date", TO_CHAR(ohd.insert_timestamp,'YYYYMMDD HH24:MM:SS') AS insert_timestamp, TO_CHAR(ohd.update_timestamp,'YYYYMMDD HH24:MM:SS') AS update_timestamp, -- ROW_NUMBER () OVER (PARTITION BY orp.mdm_pat_id ORDER BY ohd.hub_key DESC) -- rn ohd.other_flex_col_35 AS "Requestor Contact First Name", ohd.other_flex_col_36 AS "Requestor Contact Last Name", ohd.other_flex_col_37 AS "Requestor Contact Phone", orp.hub_pat_id AS "Covance Patient ID", --TO_CHAR(ohd.nurse_opt_in,'YYYYMMDDHH24MISS') AS "Nurse Opt-In", ---newly added-------------------- phi1.home_infsn_prvdr_nm AS "Prmry_Home_Infusn_Provdr_Nm_1", phi1.hip_ntwrk_stat AS "Prmry_HIP_Network_Stat_1", phi1.hcp_rqstd_hip AS "Prmry_HCP_Reqstd_HIP_1", phi1.rqrd_infrmtn AS "Prmry_Required_Info_1", shi1.home_infsn_prvdr_nm AS "Scndry_Home_Infusn_Provdr_Nm_1", shi1.hip_ntwrk_stat AS "Scndry_HIP_Network_Stat_1", shi1.hcp_rqstd_hip AS "Scndry_HCP_Requested_HIP_1", shi1.rqrd_infrmtn AS "Scndry_Required_Info_1", phi2.home_infsn_prvdr_nm AS "Prmry_Home_Infusn_Provdr_Nm_2", phi2.hip_ntwrk_stat AS "Prmry_HIP_Network_Stat_2", phi2.hcp_rqstd_hip AS "Prmry_HCP_Reqstd_HIP_2", phi2.rqrd_infrmtn AS "Prmry_Required_Info_2", shi2.home_infsn_prvdr_nm AS "Scndry_Home_Infusn_Provdr_Nm_2", shi2.hip_ntwrk_stat AS "Scndry_HIP_Network_Stat_2", shi2.hcp_rqstd_hip AS "Scndry_HCP_Requested_HIP_2", shi2.rqrd_infrmtn AS "Scndry_Required_Info_2", phi3.home_infsn_prvdr_nm AS "Prmry_Home_Infusn_Provdr_Nm_3", phi3.hip_ntwrk_stat AS "Prmry_HIP_Network_Stat_3", phi3.hcp_rqstd_hip AS "Prmry_HCP_Reqstd_HIP_3", phi3.rqrd_infrmtn AS "Prmry_Required_Info_3", shi3.home_infsn_prvdr_nm AS "Scndry_Home_Infusn_Provdr_Nm_3", shi3.hip_ntwrk_stat AS "Scndry_HIP_Network_Stat_3", shi3.hcp_rqstd_hip AS "Scndry_HCP_Requested_HIP_3", shi3.rqrd_infrmtn AS "Scndry_Required_Info_3", phi4.home_infsn_prvdr_nm AS "Prmry_Home_Infusn_Provdr_Nm_4", phi4.hip_ntwrk_stat AS "Prmry_HIP_Network_Stat_4", phi4.hcp_rqstd_hip AS "Prmry_HCP_Reqstd_HIP_4", phi4.rqrd_infrmtn AS "Prmry_Required_Info_4", shi4.home_infsn_prvdr_nm AS "Scndry_Home_Infusn_Provdr_Nm_4", shi4.hip_ntwrk_stat AS "Scndry_HIP_Network_Stat_4", shi4.hcp_rqstd_hip AS "Scndry_HCP_Requested_HIP_4", shi4.rqrd_infrmtn AS "Scndry_Required_Info_4", phi5.home_infsn_prvdr_nm AS "Prmry_Home_Infusn_Provdr_Nm_5", phi5.hip_ntwrk_stat AS "Prmry_HIP_Network_Stat_5", phi5.hcp_rqstd_hip AS "Prmry_HCP_Reqstd_HIP_5", phi5.rqrd_infrmtn AS "Prmry_Required_Info_5", shi5.home_infsn_prvdr_nm AS "Scndry_Home_Infusn_Provdr_Nm_5", shi5.hip_ntwrk_stat AS "Scndry_HIP_Network_Stat_5", shi5.hcp_rqstd_hip AS "Scndry_HCP_Requested_HIP_5", shi5.rqrd_infrmtn AS "Scndry_Required_Info_5", phi6.home_infsn_prvdr_nm AS "Prmry_Home_Infusn_Provdr_Nm_6", phi6.hip_ntwrk_stat AS "Prmry_HIP_Network_Stat_6", phi6.hcp_rqstd_hip AS "Prmry_HCP_Reqstd_HIP_6", phi6.rqrd_infrmtn AS "Prmry_Required_Info_6", shi6.home_infsn_prvdr_nm AS "Scndry_Home_Infusn_Provdr_Nm_6", shi6.hip_ntwrk_stat AS "Scndry_HIP_Network_Stat_6", shi6.hcp_rqstd_hip AS "Scndry_HCP_Requested_HIP_6", shi6.rqrd_infrmtn AS "Scndry_Required_Info_6", --Added New columns As Part of Phase 4 Changes Starts orp.mdm_pat_id AS "Aggregator Patient ID", orps.hub_presc_id AS "Prescriber ID", ohd.other_flex_col_38 AS "Primary Setting of Care", DECODE(ohd.other_flex_col_39,'Y','YES','N','NO',ohd.other_flex_col_39) AS "Primary EntyvioHome Selected", DECODE(ohd.other_flex_col_88,'Y','YES','N','NO',ohd.other_flex_col_88) AS "Subsequent BI Flag", DECODE(ohd.flg1,'Y','YES','N','NO',ohd.flg1) AS "Informational BI Flag", ohd.other_flex_col_89 AS "BIS File Name", ohd.sr_outcome AS "SR Outcome", ohd.sr_outcome_reas AS "SR Outcome Reason", --Added New columns As Part of Phase 4 Changes Ends --Added below New columns As Part of Phase 6 Changes --Added below New columns As Part of Phase 6 Changes case when ohd.other_flex_col_23 is null then 'N' else ohd.other_flex_col_23 end AS "Copay Patient", ohd.ndc_no2 AS "Primary NDC Op2", ohd.hcpcs_cd3 AS "Primary Hcpcs Op2", ohd.prcdr_cd3 AS "Primary Procedure Code Op2", ohd.cpt_cd1 AS "Primary Cpt Modifier Op2", ohd.prmry_phrm_copay_amt AS "Primary Copay Op2", ohd.prmry_phrm_coinsur_amt AS "Primary Coinsurance Op2", ohd.prmry_phrm_deductible AS "Primary Annual Deductible Op2", ohd.prmry_phrm_deductible_met AS "Primary Deductible Met Op2", ohd.prmry_phrm_out_of_pckt_amt AS "Pri Out Of Pocket Maximum Op2", ohd.prmry_phrm_pa_rqrd AS "Pri Limit-Restrict Op2", ohd.bi_outcome1 AS "Primary BI Op2 Outcome", ohd.bi_outcome_reas1 AS "Primary BI Op2 Outcome Reason", ohd.ndc_no3 AS "Secondary NDC Op2", ohd.hcpcs_cd4 AS "Secondary Hcpcs Op2", ohd.prcdr_cd4 AS "Secondary Procedure Code Op2", ohd.cpt_cd2 AS "Secondary Cpt Modifier Op2", ohd.scndry_phrm_copay_amt AS "Secondary Copay Op2", ohd.scndry_phrm_coinsur_amt AS "Secondary Coinsurance Op2", ohd.scndry_phrm_deductible AS "Sndry Annual Deductible Op2", ohd.scndry_phrm_deductible_met AS "Secondary Deductible Met Op2", ohd.scndry_phrm_out_of_pckt_amt AS "Sndry Out Of Pocket Max Op2", ohd.scndry_phrm_pa_rqrd AS "Sndry Limit-Restrict Op2", ohd.bi_outcome2 AS "Secondary Bi Op2 Outcome", ohd.bi_outcome_reas2 AS "Sndry Bi Op2 Outcome Reason", ohd.bi_outcome3 AS "Primary Bi Op1 Outcome", ohd.bi_outcome_reas3 AS "Primary Bi Op1 Outcome Reason", ohd.bi_outcome4 AS "Secondary Bi Op1 Outcome", ohd.bi_outcome_reas4 AS "Sndry Bi Op1 Outcome Reason", --Newly Added as part of Phase 7 Starts TO_CHAR(ohd.PHONE_OPT_IN_STRT_DT,'YYYYMMDD') AS "Phone Opt-In Date", TO_CHAR(ohd.EMAIL_OPT_IN_DT,'YYYYMMDD') AS "Email Opt-In Date", TO_CHAR(ohd.VOICEMAIL_OPT_IN_DT,'YYYYMMDD') AS "VoiceMail Opt-In Date", --Newly Added as part of Phase 7 Ends org.ORG_CUST_NO AS "Practice Id",--- Phase 10 DWE changes --- org.ORG_CUST_NM AS "Practice Name", ohd.INTAKE_SRVC_NM AS "Source", --- Phase 10 DWE changes --- ROW_NUMBER() OVER( PARTITION BY orp.mdm_pat_id, ohd.record_type, ohd.record_subtype, ohd.curr_rfrl_stat_reas_cd, TO_CHAR(ohd.curr_rfrl_stat_dt,'YYYYMMDD HH24:MI:SS'), ohd.service_reqst_no, ohd.curr_rfrl_stat, orp.hub_pat_id ORDER BY ohd.hub_key DESC ) rn FROM lh_ods.ods_hub_data ohd, lh_ods.ods_raw_patient orp, lh_ods.ods_raw_prescriber orps, lh_ods.ods_raw_payer orpr1, lh_ods.ods_raw_payer orpr2, lh_ods.ods_raw_payer orp1p, lh_ods.ods_raw_payer orp2p, lh_ods.ods_raw_org org, --- Phase 10 DWE changes --- ---newly added-------------------- ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'PRIMARY_HOME_INFUSION_1' ) phi1, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'PRIMARY_HOME_INFUSION_2' ) phi2, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'PRIMARY_HOME_INFUSION_3' ) phi3, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'PRIMARY_HOME_INFUSION_4' ) phi4, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'PRIMARY_HOME_INFUSION_5' ) phi5, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'PRIMARY_HOME_INFUSION_6' ) phi6, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'SECONDARY_HOME_INFUSION_1' ) shi1, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'SECONDARY_HOME_INFUSION_2' ) shi2, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'SECONDARY_HOME_INFUSION_3' ) shi3, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'SECONDARY_HOME_INFUSION_4' ) shi4, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'SECONDARY_HOME_INFUSION_5' ) shi5, ( SELECT * FROM lh_ods.ods_infusion_data WHERE infsn_typ = 'SECONDARY_HOME_INFUSION_6' ) shi6, lh_ods.cov_status_reason_codes st_rea WHERE ohd.file_typ_id BETWEEN 1501 AND 1515 /*AND UPPER (ohd.record_type) IN ('BI', 'PA', 'AS', 'CS', 'AR', 'COPAY', 'PAP', 'NON-PAP', 'IC','BRIDGE')*/ AND ohd.record_type IS NOT NULL AND ohd.record_subtype IS NOT NULL AND ohd.curr_rfrl_stat_reas_cd IS NOT NULL AND ohd.curr_rfrl_stat_dt IS NOT NULL AND ( ohd.record_type <> 'CM' AND ohd.curr_rfrl_stat IS NOT NULL OR ohd.record_type = 'CM' AND ohd.curr_rfrl_stat IS NOT NULL OR ohd.record_type = 'CM' AND ohd.curr_rfrl_stat IS NULL ) AND ( ohd.record_type <> 'CM' AND ohd.hub_sr_strt_dt IS NOT NULL OR ohd.record_type = 'CM' AND ohd.hub_sr_strt_dt IS NOT NULL OR ohd.record_type = 'CM' AND ohd.hub_sr_strt_dt IS NULL ) AND ( ohd.record_type <> 'CM' AND ohd.hub_sr_stat_dt IS NOT NULL OR ohd.record_type = 'CM' AND ohd.hub_sr_stat_dt IS NOT NULL OR ohd.record_type = 'CM' AND ohd.hub_sr_stat_dt IS NULL ) AND UPPER( ohd.record_type) IN ( SELECT record_type FROM lh_ods.extract_record_type )--added on 11/28/2019 AND ohd.brand_nm IS NOT NULL AND ohd.service_reqst_no IS NOT NULL AND ohd.insert_timestamp IS NOT NULL AND ohd.pat_key = orp.pat_key AND orp.mdm_pat_id IS NOT NULL AND orp.hub_pat_id IS NOT NULL AND ohd.presc_key = orps.presc_key AND ohd.prmry_pyr_key = orpr1.pyr_key AND ohd.scndry_pyr_key = orpr2.pyr_key AND ohd.prmry_phrm_pyr_key = orp1p.pyr_key (+) AND ohd.scndry_phrm_pyr_key = orp2p.pyr_key (+) AND upper(ohd.curr_rfrl_stat_reas_cd) = upper(st_rea.status_reason_code(+) ) --Newly added Phase 4 AND ohd.hub_key = phi1.hub_key (+) AND ohd.hub_key = phi2.hub_key (+) AND ohd.hub_key = phi3.hub_key (+) AND ohd.hub_key = phi4.hub_key (+) AND ohd.hub_key = phi5.hub_key (+) AND ohd.hub_key = phi6.hub_key (+) AND ohd.hub_key = shi1.hub_key (+) AND ohd.hub_key = shi2.hub_key (+) AND ohd.hub_key = shi3.hub_key (+) AND ohd.hub_key = shi4.hub_key (+) AND ohd.hub_key = shi5.hub_key (+) AND ohd.hub_key = shi6.hub_key (+) AND ohd.ORG_KEY = ORG.ORG_KEY(+)