SELECT vw.spp_shipment_key AS row_wid, NULL AS gl_date, vw.ship_date AS ship_date, CASE WHEN Trim (vw.pharmacy_name) = 'ACCURATERX' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048931' WHEN Nvl (oss.update_src_filename, oss.insert_src_filename) LIKE '%SP002A%' THEN '55048959' WHEN Nvl (oss.update_src_filename, oss.insert_src_filename) LIKE '%SP002B%' THEN '55048960' WHEN Trim (vw.pharmacy_name) = 'BIORX' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048962' WHEN Trim (vw.pharmacy_name) = 'COMFORT-INFUSION' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048957' WHEN Trim (vw.pharmacy_name) = 'DIPLOMAT' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048962' WHEN Trim (vw.pharmacy_name) = 'FOCUSRX' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55050717' WHEN Trim (vw.pharmacy_name) = 'DIPLOMAT-AFFINITY' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048962' WHEN oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN t2.flex_cust_id ELSE NULL END AS parent_customer, 'NULL' AS customer_sold_to, vw.full_product_name AS product, vw.ndc_number AS ndc_code, CASE WHEN ndc.prod_id IN ( 1, 2, 5, 6 ) THEN Nvl (vw.quantity_shipped, 0) WHEN ndc.prod_id IN ( 3, 4 ) AND Upper (Trim (pharmacy_name)) NOT IN ( 'CIGNA TEL-DRUG' ) THEN Nvl (vw.quantity_shipped, 0) * To_number (Nvl (vw.product_strength, 1)) WHEN ndc.prod_id IN ( 3, 4 ) AND Upper (Trim (pharmacy_name)) IN ( 'CIGNA TEL-DRUG' ) THEN Nvl (vw.quantity_shipped, 0) END AS units, 'NULL' AS sales, ( CASE WHEN vw.full_product_name IN ( 'ARALAST', 'GLASSIA', 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'OBIZUR', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'CEPROTIN' ) THEN 'EU' WHEN vw.full_product_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN 'VI' ELSE vw.quantity_uom END ) AS um, CASE WHEN ndc.prod_id IN ( 1, 2, 5, 6 ) THEN NULL WHEN ndc.prod_id IN ( 3, 4 ) THEN To_number (Nvl (vw.product_strength, 1)) END AS activity_units, CASE WHEN Nvl (Upper (Trim (vw.physician_address1)), '-X') <> Nvl ( Upper (Trim (vw.physician_address2)), '-X') THEN vw.physician_address1 || ' ' || vw.physician_address2 ELSE vw.physician_address1 END AS address, vw.physician_city AS city, vw.physician_state AS state, vw.physician_zip AS zip_code, vw.product_code AS lot_number, vw.order_transaction_number AS invoice_number, NULL AS dist_customer_number, vw.dea# AS dea_number, 'NULL' AS hin_number, 'NULL' AS contract_id, 'NULL' AS buying_group, CASE WHEN vw.first_name || vw.last_name IS NULL THEN 'Physician Name not provided' ELSE vw.first_name || ' ' || vw.last_name END AS hospital_name, 'NULL' AS purchase_price, 'NULL' AS contract_price, 'NULL' AS comments_field, Nvl (oss.update_src_filename, oss.insert_src_filename) AS file_tag, 'NULL' AS original_buying_group, vw.first_name || ' ' || vw.last_name AS original_hospital_name, vw.spp_patient_id AS clean_agreement_id, vw.hub_identifier AS aatmosphere_number, 'NULL' AS novation_lic, vw.primary_payer_name payor, CASE WHEN vw.pharmacy_name = 'HEALIX' THEN CASE WHEN Substr (vw.icd_9_codes, 1, 1) = ',' AND Regexp_count (vw.icd_9_codes, ',') > 1 THEN Substr (vw.icd_9_codes, 2, Instr (vw.icd_9_codes, ',', 1, 2) - 2) WHEN Substr (vw.icd_9_codes, 1, 1) = ',' AND Regexp_count (vw.icd_9_codes, ',') = 1 THEN Substr (vw.icd_9_codes, 2) WHEN Substr (vw.icd_9_codes, 1, 1) <> ',' AND Regexp_count (vw.icd_9_codes, ',') >= 1 THEN Substr (vw.icd_9_codes, 1, Instr (vw.icd_9_codes, ',', 1, 1) - 1) ELSE vw.icd_9_codes END WHEN vw.full_product_name IN ( 'CUVITRU', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA' , 'HYQVIA' ) AND Nvl (vw.icd_9_codes, 'X') = 'X' THEN 'N/A' ELSE vw.icd_9_codes END AS icd9, 'NULL' AS gardian, 'SPP' AS SOURCE, pf.file_id AS file_key, Nvl (oss.update_src_filename, oss.insert_src_filename) AS file_name, 'LIQUIDHUB' AS created_by, To_char (oss.insert_timestamp, 'YYYYMMDD') AS created_date, 'NULL' AS modified_by, To_char (oss.update_timestamp, 'YYYYMMDD') AS modified_date, 'NULL' AS modification_desc, vw.spp_shipment_key AS row_num, 'P' AS action_flg, 'NULL' AS error_corrected_flg, NULL AS prescribed_dose, NULL AS number_of_doses, NULL AS total_dispensed_quantity, To_char (To_date (vw.patient_first_ship_date, 'YYYYMMDD'), 'YYYYMMDD') AS therapy_start_date, NULL AS discharge_date, To_char (vw.lh_pat_id) AS patient_id, CASE WHEN Nvl (vw.patient_age, '-123') = '-123' AND Length (vw.patient_yob) = 4 AND vw.full_product_name IN ( 'HYQVIA' ) THEN To_number (Substr (ship_date, 1, 4)) - To_number (vw.patient_yob) WHEN vw.patient_yob IN ( 1 ) AND pharmacy_name LIKE 'ACCREDO%' AND vw.full_product_name IN ( 'HYQVIA' ) THEN 180 WHEN vw.patient_yob IN ( 2 ) AND pharmacy_name LIKE 'ACCREDO%' AND vw.full_product_name IN ( 'HYQVIA' ) THEN 390 WHEN vw.patient_yob IN ( 3 ) AND pharmacy_name LIKE 'ACCREDO%' AND vw.full_product_name IN ( 'HYQVIA' ) THEN 590 WHEN vw.patient_yob IN ( 4 ) AND pharmacy_name LIKE 'ACCREDO%' AND vw.full_product_name IN ( 'HYQVIA' ) THEN 600 WHEN Nvl (vw.patient_age, '-123') <> '-123' AND pharmacy_name LIKE '%NUFACTOR%' AND vw.patient_age >= 16 THEN 999 WHEN Nvl (vw.patient_age, '-123') <> '-123' AND pharmacy_name LIKE '%NUFACTOR%' AND vw.patient_age < 16 THEN 1 ELSE To_number (vw.patient_age) END patient_age, vw.hub_identifier AS bx_hub_id, vw.npi_# AS npi_number, vw.baxalta_customer_id AS lh_prescriber_id, vw.baxalta_customer_addr_id AS lh_prescriber_address_id, 'NULL' AS lh_ship_to_org_id, 'NULL' AS lh_ship_to_address_id, CASE WHEN Upper (Trim (pharmacy_name)) = 'BIORX' THEN 'DIPLOMAT' ELSE pharmacy_name END pharmacy_name FROM (SELECT * FROM lh_ods.vw_shipment_det_ext@bax_logging WHERE To_number (Substr (ship_date, 1, 6)) >= '201801') vw, (SELECT * FROM lh_ods.ods_spp_shipment@bax_logging WHERE To_number (To_char (shipment_dt, 'YYYYMM')) >= '201801') oss, lh_logging.lh_ndc_master@bax_logging ndc, (SELECT Min (file_id) file_id, Upper (file_name) file_name FROM lh_logging.lh_procs_files@bax_logging GROUP BY Upper (file_name)) pf, (SELECT * FROM lh_ods.ods_spp_qty_conv_factor@bax_logging WHERE spp_key = 11) ods_conv_fact, (SELECT * FROM lh_support.parent_updated_customer_list) t2 WHERE vw.spp_shipment_key = oss.spp_shipment_key AND oss.ndc_no = ndc.ndc_no AND vw.ndc_number = ods_conv_fact.ndc_no AND Nvl (oss.update_src_filename, oss.insert_src_filename) = Upper ( pf.file_name(+)) AND oss.shipped_qty > 0 AND Trim (vw.pharmacy_name) = Trim (t2.src_name(+)) AND Trim (vw.full_product_name) = Trim (t2.product_name(+)) AND ( ( CASE WHEN vw.full_product_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD LIQUID' , 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN Nvl ( oss.shipped_qty, 0) END BETWEEN 1 AND 99 ) OR ( CASE WHEN vw.full_product_name IN ( 'ARALAST', 'GLASSIA' ) AND Upper (Trim (pharmacy_name)) NOT IN ( 'CIGNA TEL-DRUG' ) THEN Nvl ( oss.shipped_qty, 0) * To_number (Nvl (vw.product_strength, 1)) WHEN vw.full_product_name IN ( 'ARALAST', 'GLASSIA' ) AND Upper (Trim (pharmacy_name)) IN ( 'CIGNA TEL-DRUG' ) THEN Nvl (oss.shipped_qty, 0) END ) > 200 ) UNION SELECT row_wid, gl_date, ship_date, parent_customer, customer_sold_to, product, "ndc code", Round (units), sales, um, activity_units, address, city, state, zip_code, lot_number, invoice_number, dist_customer_number, dea_number, hin_number, contract_id, buying_group, hospital_name, purchase_price, contract_price, comments_field, file_tag, original_buying_group, original_hospital_name, clean_agreement_id, aatmosphere_number, novation_lic, payor, icd9, gardian, "source", file_key, file_name, created_by, created_date, modified_by, modified_date, modification_desc, row_num, action_flg, error_corrected_flg, prescribed_dose, number_of_doses, total_dispensed_quantity, therapy_start_date, discharge_date, patient_id, patient_age, bx_hub_id, npi_number, lh_prescriber_id, lh_prescriber_address_id, lh_ship_to_org_id, lh_ship_to_address_id, pharmacy_name FROM (SELECT oss.spp_shipment_key AS row_wid, CASE WHEN b.src_key IN ( 45 ) THEN oss.other_flex_col_1 ELSE NULL END AS gl_date, CASE WHEN file_typ_id = 815 AND Nvl (shipment_dt, To_date ('01/01/1900', 'MM/DD/YYYY') ) = To_date ('01/01/1900', 'MM/DD/YYYY') THEN To_char (pat_thrpy_end_dt, 'YYYYMMDD') ELSE To_char (shipment_dt, 'YYYYMMDD') END AS ship_date, CASE WHEN oss.file_typ_id = 799 AND oss.src_key = 27 AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55050717' WHEN Nvl (oss.update_src_filename, oss.insert_src_filename) LIKE '%SP002A%' THEN '55048959' WHEN Nvl (oss.update_src_filename, oss.insert_src_filename) LIKE '%SP002B%' THEN '55048960' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) LIKE '%NPHS%' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048954' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) NOT LIKE '%NPHS%' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048955' WHEN oss.file_typ_id = 710 AND Upper (Trim (oss.src_key)) = 97 AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048962' WHEN oss.file_typ_id = 710 AND Upper (Trim (oss.src_key)) = 98 AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048957' WHEN oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN t2.flex_cust_id ELSE NULL END parent_customer, 'NULL' AS customer_sold_to, f.trade_name AS product, oss.ndc_no AS "NDC CODE", CASE WHEN oss.file_typ_id IN ( 799, 740, 760 ) AND f.trade_name IN ( 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'OBIZUR', 'CEPROTIN' ) THEN Nvl (oss.shipped_qty, 0) WHEN oss.src_key IN ( 18, 25 ) AND f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA' , 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA' , 'HYQVIA' ) THEN SUM (oss.shipped_qty) over ( PARTITION BY oss.src_key, orp.mdm_pat_id, oss.ndc_no, oss.shipment_dt) WHEN oss.src_key IN ( 18, 25 ) AND f.trade_name IN ( 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'OBIZUR', 'CEPROTIN' ) THEN SUM (oss.tot_qty) over ( PARTITION BY oss.src_key, orp.mdm_pat_id, oss.ndc_no, oss.shipment_dt) WHEN oss.src_key NOT IN ( 18, 25 ) AND f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA' , 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA' , 'HYQVIA' ) THEN Nvl (oss.shipped_qty, 0) WHEN f.trade_name IN ( 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI' , 'OBIZUR', 'CEPROTIN' ) THEN Nvl (oss.tot_qty, 0) WHEN f.trade_name IN ( 'GLASSIA', 'ARALAST' ) THEN Nvl (oss.tot_qty, 0) ELSE NULL END AS units, 'NULL' AS sales, ( CASE WHEN f.trade_name IN ( 'ARALAST', 'GLASSIA', 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'OBIZUR', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'CEPROTIN' ) THEN 'EU' WHEN f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN 'VI' ELSE shipped_qty_uom END ) AS um, oss.actual_potency AS activity_units, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_addr_line1 WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_addr_line1 ELSE orps.presc_addr_line1 END ) AS address, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_city WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_city ELSE orps.presc_city END ) AS city, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_state WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_state ELSE orps.presc_state END ) AS state, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_pstl_id WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_pstl_id ELSE orps.presc_pstl_id END ) AS zip_code, lot_no AS lot_number, invoice_no AS invoice_number, other_flex_col_21 AS dist_customer_number, ( CASE WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_dea_no ELSE orps.presc_dea_no END ) AS dea_number, 'NULL' AS hin_number, 'NULL' AS contract_id, 'NULL' AS buying_group, ( CASE WHEN oss.file_typ_id = 783 AND oss.ship_to_cust_nm IS NULL THEN 'Physician Name not provided' WHEN oss.file_typ_id = 783 AND oss.ship_to_cust_nm IS NOT NULL THEN oss.ship_to_cust_nm WHEN oss.file_typ_id = 704 AND oss.other_flex_col_1 IS NULL THEN 'Physician Name not provided' WHEN oss.file_typ_id = 704 AND oss.other_flex_col_1 IS NOT NULL THEN oss.other_flex_col_1 WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN ( CASE WHEN oss.presc_fst_nm || oss.presc_lst_nm IS NULL THEN 'Physician Name not provided' ELSE oss.presc_fst_nm || ' ' || oss.presc_lst_nm END ) WHEN ( oss.presc_key NOT LIKE '-%' OR oss.presc_key IS NOT NULL ) THEN ( CASE WHEN orps.presc_fst_nm || orps.presc_lst_nm IS NULL THEN 'Physician Name not provided' ELSE orps.presc_fst_nm || ' ' || orps.presc_lst_nm END ) ELSE orps.presc_fst_nm || ' ' || orps.presc_lst_nm END ) AS hospital_name, 'NULL' AS purchase_price, 'NULL' AS contract_price, 'NULL' AS comments_field, oss.insert_src_filename AS file_tag, 'NULL' AS original_buying_group, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_cust_nm WHEN oss.file_typ_id = 704 THEN oss.other_flex_col_1 WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_fst_nm || ' ' || oss.presc_lst_nm ELSE orps.presc_fst_nm || ' ' || orps.presc_lst_nm END ) AS original_hospital_name, CASE WHEN oss.file_typ_id IN ( 722 ) AND f.trade_name = 'HYQVIA' AND Nvl (orp.spp_pat_id, 'X') = 'X' THEN 'NA' ELSE orp.spp_pat_id END AS clean_agreement_id , orp.hub_pat_id AS aatmosphere_number, 'NULL' AS novation_lic, oss.prmry_pyr_nm payor, CASE WHEN oss.file_typ_id IN ( 791, 787, 783, 740 ) AND Nvl (Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1 ), 'X') = 'X' THEN 'NA' WHEN oss.file_typ_id IN ( 709 ) AND f.trade_name IN ( 'RECOMBINATE', 'ADVATE' ) AND Nvl (Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1 ), 'X') = 'X' THEN 'NA' WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI', 'GAMMAGARD S/D LOW IGA', 'GAMMAGARD LIQUID', 'CUVITRU' ) AND oss.file_typ_id IN ( 783, 799 ) AND Nvl (Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1 ), 'X') = 'X' THEN 'N/A' ELSE Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1) END AS icd9, 'NULL' AS gardian, 'SPP' AS SOURCE, c.file_id file_key, oss.insert_src_filename AS file_name, 'LIQUIDHUB' AS created_by, To_char (oss.insert_timestamp, 'YYYYMMDD') AS created_date, 'NULL' AS modified_by, NULL AS modified_date, 'NULL' AS modification_desc, oss.spp_shipment_key AS row_num, 'P' AS action_flg, 'NULL' AS error_corrected_flg, CASE WHEN oss.src_key = 19 THEN oss.other_flex_col_4 ELSE To_char (oss.rx_qty) END AS prescribed_dose, To_char (oss.authorized_refills) AS number_of_doses, CASE WHEN oss.src_key IN ( 4, 24 ) AND oss.file_typ_id IN ( 703, 708 ) THEN other_flex_col_21 ELSE NULL END AS total_dispensed_quantity, To_char (oss.pat_thrpy_strt_dt, 'YYYYMMDD') AS therapy_start_date , To_char (oss.pat_thrpy_end_dt, 'YYYYMMDD') AS discharge_date, CASE WHEN ( oss.pat_key IS NULL OR oss.pat_key LIKE '-%' ) AND f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN 'Patient ID not provided' WHEN ( oss.pat_key IS NULL OR oss.pat_key LIKE '-%' ) AND f.trade_name NOT IN ( 'HYQVIA', 'VONVENDI' ) THEN NULL ELSE To_char (orp.mdm_pat_id) END AS patient_id, ( CASE WHEN oss.file_typ_id IN ( 776 ) AND f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN 999 WHEN oss.file_typ_id IN ( 707, 728 ) AND f.trade_name IN ( 'VONVENDI' ) THEN 999 WHEN oss.file_typ_id IN ( 775 ) AND f.trade_name IN ( 'VONVENDI' ) AND oss.pat_yob IS NULL THEN 999 WHEN oss.file_typ_id IN ( 775 ) AND f.trade_name IN ( 'VONVENDI' ) AND Length (oss.pat_yob) = 4 THEN To_char (oss.shipment_dt, 'YYYY') - oss.pat_yob WHEN orp.pat_age IS NULL THEN oss.pat_age ELSE orp.pat_age END ) AS patient_age, oss.hub_pat_id AS bx_hub_id, ( CASE WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_npi_no ELSE orps.presc_npi_no END ) AS npi_number, orps.veeva_mdm_presc_id lh_prescriber_id, orps.veeva_mdm_addr_id lh_prescriber_address_id, 'NULL' AS lh_ship_to_org_id, 'NULL' AS lh_ship_to_address_id, CASE WHEN Upper (Trim (b.src_nm)) = 'BIORX' THEN 'DIPLOMAT' WHEN oss.file_typ_id = 783 AND Upper (Trim (oss.ship_to_cust_nm)) LIKE '%PHS%' THEN 'NYBC_PHS' WHEN oss.file_typ_id = 783 AND Upper (Trim (oss.ship_to_cust_nm)) NOT LIKE '%PHS%' THEN 'NYBC_NONPHS' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) LIKE '%NPHS%' THEN 'CASCADE_NONPHS' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) NOT LIKE '%NPHS%' THEN 'CASCADE_PHS' WHEN oss.src_key = 97 AND oss.file_typ_id = 710 THEN 'DIPLOMAT-AFFINITY' WHEN oss.src_key = 98 AND oss.file_typ_id = 710 THEN 'COMFORT-INFUSION' WHEN oss.src_key = 27 AND oss.file_typ_id = 799 THEN 'FOCUSRX' WHEN oss.file_typ_id = 791 AND Upper (Trim (oss.other_flex_col_1)) = 'BDRN' THEN 'BDRN' WHEN oss.file_typ_id = 791 AND Upper (Trim (oss.other_flex_col_1)) = '340B-NBI' THEN 'BDRN-340B-NBI' WHEN oss.file_typ_id = 791 AND Upper (Trim (oss.other_flex_col_1)) = '340B-RWJ' THEN 'BDRN-340B-RWJ' ELSE b.src_nm END pharmacy_name, CASE WHEN oss.src_key IN ( 18, 25 ) AND f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA' , 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA' , 'HYQVIA', 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI' , 'OBIZUR', 'CEPROTIN' ) THEN Row_number () over ( PARTITION BY oss.src_key, orp.mdm_pat_id, oss.ndc_no, oss.shipment_dt ORDER BY oss.spp_shipment_key DESC) ELSE 1 END AS rank_1 FROM (SELECT * FROM lh_ods.ods_spp_shipment --where shipment_dt like '%-FEB-2018' WHERE CASE WHEN file_typ_id = 815 AND Nvl (shipment_dt, To_date ('01/01/1900', 'MM/DD/YYYY' )) = To_date ('01/01/1900', 'MM/DD/YYYY') THEN To_number (To_char (pat_thrpy_end_dt, 'YYYYMM')) ELSE To_number (To_char (shipment_dt, 'YYYYMM')) END >= '201801') oss, lh_ods.ods_master_source b, lh_ods.ods_raw_patient orp, lh_ods.ods_raw_prescriber orps, (SELECT Min (file_id) file_id, Upper (file_name) file_name FROM lh_logging.lh_procs_files GROUP BY Upper (file_name)) c, lh_ods.ods_master_prod f, lh_logging.lh_ndc_master ndc, lh_support.parent_updated_customer_list t2 WHERE oss.pat_key = orp.pat_key(+) AND oss.src_key = b.src_key(+) AND oss.presc_key = orps.presc_key(+) AND Upper (oss.insert_src_filename) = Upper (c.file_name(+)) AND oss.ndc_no = f.ndc_no(+) AND oss.ndc_no = ndc.ndc_no AND f.ndc_typ_cd = 'O' AND b.src_nm = t2.src_name AND Nvl (oss.other_flex_col_1, 'XXX') NOT IN ( '340B-NBI', '340B-RWJ' ) AND f.trade_name = t2.product_name AND ( ( ( oss.file_typ_id BETWEEN 700 AND 800 AND oss.file_typ_id NOT IN ( 753 ) ) AND ( ndc.prod_id IN ( 1, 2, 3, 4, 5, 6 ) OR ( ( ndc.prod_id BETWEEN 206 AND 215 ) OR ndc.prod_id = 217 ) ) ) OR ( oss.file_typ_id IN ( 753, 801, 814, 815 ) AND ( ( ndc.prod_id BETWEEN 206 AND 214 ) OR ndc.prod_id = 217 ) ) ) AND ( CASE WHEN oss.file_typ_id IN ( 709, 703, 799, 760 ) AND Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1) IS NULL THEN '-X' WHEN oss.file_typ_id IN ( 783, 791, 728, 707, 775, 752, 787, 740 ) AND Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1) IS NULL THEN '-X' WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI', 'GAMMAGARD S/D LOW IGA', 'GAMMAGARD LIQUID', 'CUVITRU' ) THEN Nvl2 ( Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1) , '-X', 'X') ELSE '-X' END ) = '-X' AND ( CASE WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN Nvl2 ( ( CASE WHEN oss.file_typ_id IN ( 776 ) AND f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN 999 WHEN oss.file_typ_id IN ( 707, 728 ) AND f.trade_name IN ( 'VONVENDI' ) THEN 999 WHEN f.trade_name IN ( 'VONVENDI' ) AND oss.pat_yob IS NULL THEN 999 WHEN oss.file_typ_id IN ( 775 ) AND f.trade_name IN ( 'VONVENDI' ) AND Length (oss.pat_yob) = 4 THEN To_char (oss.shipment_dt, 'YYYY') - oss.pat_yob WHEN orp.pat_age IS NULL THEN oss.pat_age ELSE orp.pat_age END ), '-X', 'X') ELSE '-X' END ) = '-X' /* AND ( (CASE WHEN f.trade_name IN ('BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA') THEN oss.shipped_qty END < 100 ) OR (CASE WHEN f.trade_name IN ('GLASSIA', 'ARALAST') THEN NVL (oss.tot_qty, 0) WHEN f.trade_name IN ('ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'OBIZUR', 'CEPROTIN') THEN NVL (oss.tot_qty, 0) ELSE NULL END ) > 200 OR (CASE WHEN oss.file_typ_id IN (799, 740, 760) THEN NVL (oss.shipped_qty, 0) ELSE NULL END ) > 200 ) */ --- AND oss.spp_shipment_key IN ('4190733','4445032','4190734','4445033','4443738','4443739') ) a WHERE rank_1 = 1 AND ( CASE WHEN product IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN Round (units) END BETWEEN 1 AND 99 OR CASE WHEN product NOT IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN units END > 200 ) UNION SELECT oss.spp_shipment_key AS row_wid, CASE WHEN b.src_key IN ( 45 ) THEN oss.other_flex_col_1 ELSE NULL END AS gl_date, CASE WHEN file_typ_id = 815 AND Nvl (shipment_dt, To_date ('01/01/1900', 'MM/DD/YYYY')) = To_date ('01/01/1900', 'MM/DD/YYYY') THEN To_char (pat_thrpy_end_dt, 'YYYYMMDD') ELSE To_char (shipment_dt, 'YYYYMMDD') END AS ship_date, ----TO_CHAR (shipment_dt, 'YYYYMMDD') AS ship_date, -- 'NULL' AS parent_customer, CASE WHEN oss.file_typ_id = 799 AND oss.src_key = 27 AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55050717' WHEN Nvl (oss.update_src_filename, oss.insert_src_filename) LIKE '%SP002A%' THEN '55048959' WHEN Nvl (oss.update_src_filename, oss.insert_src_filename) LIKE '%SP002B%' THEN '55048960' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) LIKE '%NPHS%' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048954' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) NOT LIKE '%NPHS%' AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048955' WHEN oss.file_typ_id = 710 AND Upper (Trim (oss.src_key)) = 97 AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048962' WHEN oss.file_typ_id = 710 AND Upper (Trim (oss.src_key)) = 98 AND oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN '55048957' WHEN oss.shipment_dt BETWEEN To_date ( lh_logging.lh_utils.Standardize_date (t2.eff_date), 'MM/DD/YYYY') AND To_date ( lh_logging.lh_utils.Standardize_date (t2.end_date), 'MM/DD/YYYY') THEN t2.flex_cust_id ELSE NULL END parent_customer, 'NULL' AS customer_sold_to, f.trade_name AS product, oss.ndc_no AS "NDC CODE", CASE WHEN oss.file_typ_id IN ( 799, 740, 760 ) AND f.trade_name IN ( 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'OBIZUR', 'CEPROTIN' ) THEN Nvl (oss.shipped_qty, 0) WHEN f.trade_name IN ( 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'OBIZUR', 'CEPROTIN' ) THEN Nvl (oss.tot_qty, 0) WHEN f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN Nvl (oss.shipped_qty, 0) WHEN f.trade_name IN ( 'GLASSIA', 'ARALAST' ) THEN Nvl (oss.tot_qty, 0) ELSE NULL END AS units, 'NULL' AS sales, ( CASE WHEN f.trade_name IN ( 'ARALAST', 'GLASSIA', 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'OBIZUR', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'CEPROTIN' ) THEN 'EU' WHEN f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN 'VI' ELSE shipped_qty_uom END ) AS um, oss.actual_potency AS activity_units, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_addr_line1 WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_addr_line1 ELSE orps.presc_addr_line1 END ) AS address, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_city WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_city ELSE orps.presc_city END ) AS city, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_state WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_state ELSE orps.presc_state END ) AS state, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_pstl_id WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_pstl_id ELSE orps.presc_pstl_id END ) AS zip_code, lot_no AS lot_number, invoice_no AS invoice_number, other_flex_col_21 AS dist_customer_number, ( CASE WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_dea_no ELSE orps.presc_dea_no END ) AS dea_number, 'NULL' AS hin_number, 'NULL' AS contract_id, 'NULL' AS buying_group, ( CASE WHEN oss.file_typ_id = 783 AND oss.ship_to_cust_nm IS NULL THEN 'Physician Name not provided' WHEN oss.file_typ_id = 783 AND oss.ship_to_cust_nm IS NOT NULL THEN oss.ship_to_cust_nm WHEN oss.file_typ_id = 704 AND oss.other_flex_col_1 IS NULL THEN 'Physician Name not provided' WHEN oss.file_typ_id = 704 AND oss.other_flex_col_1 IS NOT NULL THEN oss.other_flex_col_1 WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN ( CASE WHEN oss.presc_fst_nm || oss.presc_lst_nm IS NULL THEN 'Physician Name not provided' ELSE oss.presc_fst_nm || ' ' || oss.presc_lst_nm END ) WHEN ( oss.presc_key NOT LIKE '-%' OR oss.presc_key IS NOT NULL ) THEN ( CASE WHEN orps.presc_fst_nm || orps.presc_lst_nm IS NULL THEN 'Physician Name not provided' ELSE orps.presc_fst_nm || ' ' || orps.presc_lst_nm END ) ELSE orps.presc_fst_nm || ' ' || orps.presc_lst_nm END ) AS hospital_name, 'NULL' AS purchase_price, 'NULL' AS contract_price, 'NULL' AS comments_field, oss.insert_src_filename AS file_tag, 'NULL' AS original_buying_group, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_cust_nm WHEN oss.file_typ_id = 704 THEN oss.other_flex_col_1 WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_fst_nm || ' ' || oss.presc_lst_nm ELSE orps.presc_fst_nm || ' ' || orps.presc_lst_nm END ) AS original_hospital_name, CASE WHEN oss.file_typ_id IN ( 722 ) AND f.trade_name = 'HYQVIA' AND Nvl (orp.spp_pat_id, 'X') = 'X' THEN 'NA' WHEN oss.file_typ_id IN ( 729 ) AND f.trade_name = 'VONVENDI' AND Nvl (orp.spp_pat_id, 'X') = 'X' THEN 'NA' ELSE orp.spp_pat_id END AS clean_agreement_id, orp.hub_pat_id AS aatmosphere_number, 'NULL' AS novation_lic, oss.prmry_pyr_nm payor, CASE WHEN oss.file_typ_id IN ( 791, 787, 783, 790, 729 ) AND Nvl (Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1), 'X') = 'X' THEN 'NA' WHEN oss.file_typ_id IN ( 709 ) AND f.trade_name IN ( 'RECOMBINATE', 'ADVATE' ) AND Nvl (Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1), 'X') = 'X' THEN 'NA' WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI', 'GAMMAGARD S/D LOW IGA', 'GAMMAGARD LIQUID', 'CUVITRU' ) AND oss.file_typ_id IN ( 783, 799 ) AND Nvl (Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1), 'X') = 'X' THEN 'N/A' ELSE Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1) END AS icd9, 'NULL' AS gardian, 'SPP' AS SOURCE, c.file_id file_key, oss.insert_src_filename AS file_name, 'LIQUIDHUB' AS created_by, To_char (oss.insert_timestamp, 'YYYYMMDD') AS created_date, 'NULL' AS modified_by, NULL AS modified_date, 'NULL' AS modification_desc, oss.spp_shipment_key AS row_num, 'P' AS action_flg, 'NULL' AS error_corrected_flg, CASE WHEN oss.src_key = 19 THEN oss.other_flex_col_4 ELSE To_char (oss.rx_qty) END AS prescribed_dose, To_char (oss.authorized_refills) AS number_of_doses, CASE WHEN oss.src_key IN ( 4, 24 ) AND oss.file_typ_id IN ( 703, 708 ) THEN other_flex_col_21 ELSE NULL END AS total_dispensed_quantity, To_char (oss.pat_thrpy_strt_dt, 'YYYYMMDD') AS therapy_start_date, To_char (oss.pat_thrpy_end_dt, 'YYYYMMDD') AS discharge_date, CASE WHEN ( oss.pat_key IS NULL OR oss.pat_key LIKE '-%' ) AND f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN 'Patient ID not provided' WHEN ( oss.pat_key IS NULL OR oss.pat_key LIKE '-%' ) AND f.trade_name NOT IN ( 'HYQVIA', 'VONVENDI' ) THEN NULL ELSE To_char (orp.mdm_pat_id) END AS patient_id, ( CASE WHEN oss.file_typ_id IN ( 776, 729, 790, 722 ) AND f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN 999 WHEN oss.file_typ_id IN ( 707, 728 ) AND f.trade_name IN ( 'VONVENDI' ) THEN 999 WHEN oss.file_typ_id IN ( 775 ) AND f.trade_name IN ( 'VONVENDI' ) AND oss.pat_yob IS NULL THEN 999 WHEN oss.file_typ_id IN ( 775 ) AND f.trade_name IN ( 'VONVENDI' ) AND Length (oss.pat_yob) = 4 THEN To_char (oss.shipment_dt, 'YYYY') - oss.pat_yob WHEN oss.file_typ_id IN ( 777 ) AND orp.pat_age IS NULL AND oss.pat_age IS NULL THEN 999 WHEN orp.pat_age IS NULL THEN oss.pat_age ELSE orp.pat_age END ) AS patient_age, oss.hub_pat_id AS bx_hub_id, ( CASE WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_npi_no ELSE orps.presc_npi_no END ) AS npi_number, orps.veeva_mdm_presc_id lh_prescriber_id, orps.veeva_mdm_addr_id lh_prescriber_address_id, 'NULL' AS lh_ship_to_org_id, 'NULL' AS lh_ship_to_address_id, CASE WHEN Upper (Trim (b.src_nm)) = 'BIORX' THEN 'DIPLOMAT' WHEN oss.file_typ_id = 783 AND Upper (Trim (oss.ship_to_cust_nm)) LIKE '%PHS%' THEN 'NYBC_PHS' WHEN oss.file_typ_id = 783 AND Upper (Trim (oss.ship_to_cust_nm)) NOT LIKE '%PHS%' THEN 'NYBC_NONPHS' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) LIKE '%NPHS%' THEN 'CASCADE_NONPHS' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) NOT LIKE '%NPHS%' THEN 'CASCADE_PHS' WHEN oss.src_key = 97 AND oss.file_typ_id = 710 THEN 'DIPLOMAT-AFFINITY' WHEN oss.src_key = 98 AND oss.file_typ_id = 710 THEN 'COMFORT-INFUSION' WHEN oss.src_key = 27 AND oss.file_typ_id = 799 THEN 'FOCUSRX' /*WHEN oss.file_typ_id = 791 AND UPPER (TRIM (oss.other_flex_col_1)) = 'BDRN' THEN 'BDRN' WHEN oss.file_typ_id = 791 AND UPPER (TRIM (oss.other_flex_col_1)) = '340B-NBI' THEN 'BDRN-340B-NBI' WHEN oss.file_typ_id = 791 AND UPPER (TRIM (oss.other_flex_col_1)) = '340B-RWJ' THEN 'BDRN-340B-RWJ' */ ELSE b.src_nm END pharmacy_name FROM (SELECT * FROM lh_ods.ods_spp_alt_shipment WHERE CASE WHEN file_typ_id = 815 AND Nvl (shipment_dt, To_date ('01/01/1900', 'MM/DD/YYYY') ) = To_date ('01/01/1900', 'MM/DD/YYYY') THEN To_number (To_char (pat_thrpy_end_dt, 'YYYYMM')) ELSE To_number (To_char (shipment_dt, 'YYYYMM')) END >= '201801') oss, lh_ods.ods_master_source b, lh_ods.ods_raw_patient orp, lh_ods.ods_raw_prescriber orps, (SELECT Min (file_id) file_id, Upper (file_name) file_name FROM lh_logging.lh_procs_files GROUP BY Upper (file_name)) c, lh_ods.ods_master_prod f, lh_logging.lh_ndc_master ndc, lh_support.parent_updated_customer_list t2 WHERE oss.pat_key = orp.pat_key(+) AND oss.src_key = b.src_key(+) AND oss.presc_key = orps.presc_key(+) AND Upper (oss.insert_src_filename) = Upper (c.file_name(+)) AND oss.ndc_no = f.ndc_no(+) AND oss.ndc_no = ndc.ndc_no(+) AND f.ndc_typ_cd = 'O' AND b.src_nm = t2.src_name AND f.trade_name = t2.product_name AND ( ( ( oss.file_typ_id BETWEEN 700 AND 800 AND oss.file_typ_id NOT IN ( 753 ) ) AND ( ndc.prod_id IN ( 1, 2, 3, 4, 5, 6 ) OR ( ( ndc.prod_id BETWEEN 206 AND 215 ) OR ndc.prod_id = 217 ) ) ) OR ( oss.file_typ_id IN ( 753, 801, 814, 815 ) AND ( ( ndc.prod_id BETWEEN 206 AND 214 ) OR ndc.prod_id = 217 ) ) ) AND ( CASE WHEN oss.file_typ_id IN ( 709, 703, 799, 760, 729, 790 ) AND Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1) IS NULL THEN '-X' WHEN oss.file_typ_id IN ( 783, 791, 728, 707, 775, 752, 787 ) AND Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1) IS NULL THEN '-X' WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI', 'GAMMAGARD S/D LOW IGA', 'GAMMAGARD LIQUID', 'CUVITRU' ) THEN Nvl2 ( Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1), '-X', 'X') ELSE '-X' END ) = '-X' AND ( CASE WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN Nvl2 (( CASE WHEN oss.file_typ_id IN ( 776, 729, 790, 722 ) AND f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN 999 WHEN oss.file_typ_id IN ( 707, 728 ) AND f.trade_name IN ( 'VONVENDI' ) THEN 999 WHEN f.trade_name IN ( 'VONVENDI' ) AND oss.pat_yob IS NULL THEN 999 WHEN oss.file_typ_id IN ( 775 ) AND f.trade_name IN ( 'VONVENDI' ) AND Length (oss.pat_yob) = 4 THEN To_char (oss.shipment_dt, 'YYYY') - oss.pat_yob WHEN orp.pat_age IS NULL THEN oss.pat_age ELSE orp.pat_age END ), '-X', 'X') ELSE '-X' END ) = '-X' AND ( ( CASE WHEN f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA' , 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN Nvl (oss.shipped_qty, 0) END BETWEEN 1 AND 99 ) OR ( CASE WHEN f.trade_name IN ( 'GLASSIA', 'ARALAST' ) THEN Nvl (oss.tot_qty, 0) WHEN f.trade_name IN ( 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI' , 'OBIZUR', 'CEPROTIN' ) THEN Nvl (oss.tot_qty, 0) ELSE NULL END ) > 200 OR ( CASE WHEN oss.file_typ_id IN ( 799, 740, 760 ) THEN Nvl (oss.shipped_qty, 0) ELSE NULL END ) > 200 ) UNION SELECT oss.spp_shipment_key AS row_wid, CASE WHEN b.src_key IN ( 45 ) THEN oss.other_flex_col_1 ELSE NULL END AS gl_date, CASE WHEN file_typ_id = 815 AND Nvl (shipment_dt, To_date ('01/01/1900', 'MM/DD/YYYY')) = To_date ('01/01/1900', 'MM/DD/YYYY') THEN To_char (pat_thrpy_end_dt, 'YYYYMMDD') ELSE To_char (shipment_dt, 'YYYYMMDD') END AS ship_date, ----TO_CHAR (shipment_dt, 'YYYYMMDD') AS ship_date, 'NULL' AS parent_customer, 'NULL' AS customer_sold_to, f.trade_name AS product, oss.ndc_no AS "NDC CODE", CASE WHEN oss.file_typ_id IN ( 799, 740, 760 ) AND f.trade_name IN ( 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'OBIZUR', 'CEPROTIN' ) THEN Nvl (oss.shipped_qty, 0) WHEN f.trade_name IN ( 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'OBIZUR', 'CEPROTIN' ) THEN Nvl (oss.tot_qty, 0) WHEN f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN Nvl (oss.shipped_qty, 0) WHEN f.trade_name IN ( 'GLASSIA', 'ARALAST' ) THEN Nvl (oss.tot_qty, 0) ELSE NULL END AS units, 'NULL' AS sales, ( CASE WHEN f.trade_name IN ( 'ARALAST', 'GLASSIA', 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA', 'HEMOFIL', 'OBIZUR', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI', 'CEPROTIN' ) THEN 'EU' WHEN f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU', 'GAMMAGARD IGA', 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN 'VI' ELSE shipped_qty_uom END ) AS um, oss.actual_potency AS activity_units, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_addr_line1 WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_addr_line1 ELSE orps.presc_addr_line1 END ) AS address, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_city WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_city ELSE orps.presc_city END ) AS city, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_state WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_state ELSE orps.presc_state END ) AS state, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_pstl_id WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_pstl_id ELSE orps.presc_pstl_id END ) AS zip_code, lot_no AS lot_number, invoice_no AS invoice_number, other_flex_col_21 AS dist_customer_number, ( CASE WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_dea_no ELSE orps.presc_dea_no END ) AS dea_number, 'NULL' AS hin_number, 'NULL' AS contract_id, 'NULL' AS buying_group, ( CASE WHEN oss.file_typ_id = 783 AND oss.ship_to_cust_nm IS NULL THEN 'Physician Name not provided' WHEN oss.file_typ_id = 783 AND oss.ship_to_cust_nm IS NOT NULL THEN oss.ship_to_cust_nm WHEN oss.file_typ_id = 704 AND oss.other_flex_col_1 IS NULL THEN 'Physician Name not provided' WHEN oss.file_typ_id = 704 AND oss.other_flex_col_1 IS NOT NULL THEN oss.other_flex_col_1 WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN ( CASE WHEN oss.presc_fst_nm || oss.presc_lst_nm IS NULL THEN 'Physician Name not provided' ELSE oss.presc_fst_nm || ' ' || oss.presc_lst_nm END ) WHEN ( oss.presc_key NOT LIKE '-%' OR oss.presc_key IS NOT NULL ) THEN ( CASE WHEN orps.presc_fst_nm || orps.presc_lst_nm IS NULL THEN 'Physician Name not provided' ELSE orps.presc_fst_nm || ' ' || orps.presc_lst_nm END ) ELSE orps.presc_fst_nm || ' ' || orps.presc_lst_nm END ) AS hospital_name, 'NULL' AS purchase_price, 'NULL' AS contract_price, 'NULL' AS comments_field, oss.insert_src_filename AS file_tag, 'NULL' AS original_buying_group, ( CASE WHEN oss.file_typ_id = 783 THEN oss.ship_to_cust_nm WHEN oss.file_typ_id = 704 THEN oss.other_flex_col_1 WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_fst_nm || ' ' || oss.presc_lst_nm ELSE orps.presc_fst_nm || ' ' || orps.presc_lst_nm END ) AS original_hospital_name, orp.spp_pat_id AS clean_agreement_id, orp.hub_pat_id AS aatmosphere_number, 'NULL' AS novation_lic, oss.prmry_pyr_nm payor, CASE WHEN oss.file_typ_id = 799 AND Nvl (Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1), 'X') = 'X' THEN 'NA' WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI', 'GAMMAGARD S/D LOW IGA', 'GAMMAGARD LIQUID', 'CUVITRU' ) AND oss.file_typ_id = 783 AND Nvl (Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1), 'X') = 'X' THEN 'N/A' ELSE Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1) END AS icd9, 'NULL' AS gardian, 'SPP' AS SOURCE, c.file_id file_key, oss.insert_src_filename AS file_name, 'LIQUIDHUB' AS created_by, To_char (oss.insert_timestamp, 'YYYYMMDD') AS created_date, 'NULL' AS modified_by, NULL AS modified_date, 'NULL' AS modification_desc, oss.spp_shipment_key AS row_num, 'P' AS action_flg, 'NULL' AS error_corrected_flg, CASE WHEN oss.src_key = 19 THEN oss.other_flex_col_4 ELSE To_char (oss.rx_qty) END AS prescribed_dose, To_char (oss.authorized_refills) AS number_of_doses, CASE WHEN oss.src_key IN ( 4, 24 ) AND oss.file_typ_id IN ( 703, 708 ) THEN other_flex_col_21 ELSE NULL END AS total_dispensed_quantity, To_char (oss.pat_thrpy_strt_dt, 'YYYYMMDD') AS therapy_start_date, To_char (oss.pat_thrpy_end_dt, 'YYYYMMDD') AS discharge_date, CASE WHEN ( oss.pat_key IS NULL OR oss.pat_key LIKE '-%' ) AND f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN 'Patient ID not provided' WHEN ( oss.pat_key IS NULL OR oss.pat_key LIKE '-%' ) AND f.trade_name NOT IN ( 'HYQVIA', 'VONVENDI' ) THEN NULL ELSE To_char (orp.mdm_pat_id) END AS patient_id, ( CASE WHEN oss.file_typ_id = 776 AND f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN 999 WHEN orp.pat_age IS NULL THEN oss.pat_age ELSE orp.pat_age END ) AS patient_age, oss.hub_pat_id AS bx_hub_id, ( CASE WHEN ( oss.presc_key LIKE '-%' OR oss.presc_key IS NULL ) THEN oss.presc_npi_no ELSE orps.presc_npi_no END ) AS npi_number, orps.veeva_mdm_presc_id lh_prescriber_id, orps.veeva_mdm_addr_id lh_prescriber_address_id, 'NULL' AS lh_ship_to_org_id, 'NULL' AS lh_ship_to_address_id, CASE WHEN Upper (Trim (b.src_nm)) = 'BIORX' THEN 'DIPLOMAT' WHEN oss.file_typ_id = 783 AND Upper (Trim (oss.ship_to_cust_nm)) LIKE '%PHS%' THEN 'NYBC_PHS' WHEN oss.file_typ_id = 783 AND Upper (Trim (oss.ship_to_cust_nm)) NOT LIKE '%PHS%' THEN 'NYBC_NONPHS' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) LIKE '%NPHS%' THEN 'CASCADE_NONPHS' WHEN oss.file_typ_id = 787 AND Upper (Trim (oss.brand_nm)) NOT LIKE '%NPHS%' THEN 'CASCADE_PHS' WHEN oss.src_key = 97 AND oss.file_typ_id = 710 THEN 'DIPLOMAT-AFFINITY' WHEN oss.src_key = 98 AND oss.file_typ_id = 710 THEN 'COMFORT-INFUSION' WHEN oss.src_key = 27 AND oss.file_typ_id = 799 THEN 'FOCUSRX' ELSE b.src_nm END pharmacy_name FROM (SELECT a.*, SUM (shipped_qty) over ( PARTITION BY b.spp_pat_id, a.ndc_no, a.shipment_dt) fnl_tot_qty FROM lh_ods.ods_spp_shipment a, lh_ods.ods_raw_patient b WHERE a.pat_key = b.pat_key AND a.src_key IN ( 18, 25 )) oss, lh_ods.ods_master_source b, lh_ods.ods_raw_patient orp, lh_ods.ods_raw_prescriber orps, (SELECT Min (file_id) file_id, Upper (file_name) file_name FROM lh_logging.lh_procs_files GROUP BY Upper (file_name)) c, lh_ods.ods_master_prod f, lh_logging.lh_ndc_master ndc WHERE fnl_tot_qty > 0 AND oss.pat_key = orp.pat_key(+) AND oss.src_key = b.src_key(+) AND oss.presc_key = orps.presc_key(+) AND Upper (oss.insert_src_filename) = Upper (c.file_name(+)) AND oss.ndc_no = f.ndc_no(+) AND oss.ndc_no = ndc.ndc_no(+) AND f.ndc_typ_cd = 'O' AND ( ( ( oss.file_typ_id BETWEEN 700 AND 800 AND oss.file_typ_id NOT IN ( 753 ) ) AND ( ndc.prod_id IN ( 1, 2, 3, 4, 5, 6 ) OR ( ( ndc.prod_id BETWEEN 206 AND 215 ) OR ndc.prod_id = 217 ) ) ) OR ( oss.file_typ_id IN ( 753, 801, 814, 815 ) AND ( ( ndc.prod_id BETWEEN 206 AND 214 ) OR ndc.prod_id = 217 ) ) ) AND ( ( CASE WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI', 'GAMMAGARD S/D LOW IGA', 'GAMMAGARD LIQUID', 'CUVITRU' ) AND oss.file_typ_id = 783 AND Nvl (Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1 ), 'X' ) = 'X' THEN 'N/A' WHEN oss.file_typ_id IN ( 709, 703, 799 ) AND Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1) IS NULL THEN '-X' WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI', 'GAMMAGARD S/D LOW IGA', 'GAMMAGARD LIQUID', 'CUVITRU' ) AND oss.file_typ_id <> 783 THEN Nvl2 ( Coalesce (oss.pat_diagnosis_cd_2, oss.pat_diagnosis_cd_1), '-X' , 'X') ELSE '-X' END ) = 'X' OR ( CASE WHEN f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN Nvl2 (( CASE WHEN oss.file_typ_id IN ( 776 ) AND f.trade_name IN ( 'HYQVIA', 'VONVENDI' ) THEN 999 WHEN orp.pat_age IS NULL THEN oss.pat_age ELSE orp.pat_age END ), '-X', 'X') ELSE '-X' END ) = 'X' OR ( ( CASE WHEN f.trade_name IN ( 'BUMINATE', 'FLEXBUMIN', 'CUVITRU' , 'GAMMAGARD IGA' , 'GAMMAGARD LIQUID', 'GAMMAGARD S/D LOW IGA', 'HYQVIA' ) THEN Nvl (oss.shipped_qty, 0) END NOT BETWEEN 1 AND 99 ) AND ( CASE WHEN f.trade_name IN ( 'GLASSIA', 'ARALAST' ) THEN Nvl (oss.tot_qty, 0) WHEN f.trade_name IN ( 'ADVATE', 'ADYNOVATE', 'BEBULIN', 'FEIBA' , 'HEMOFIL', 'RECOMBINATE', 'RIXUBIS', 'VONVENDI' , 'OBIZUR', 'CEPROTIN' ) THEN Nvl (oss.tot_qty, 0) ELSE NULL END ) <= 200 AND ( CASE WHEN oss.file_typ_id IN ( 799, 740, 760 ) THEN Nvl (oss.shipped_qty, 0) ELSE NULL END ) <= 200 ) ); GRANT SELECT ON lh_ods.ods_cdit_format_vw TO PUBLIC;