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 )