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 )