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
          )

select to_date(sysdate,'MM/DD/YYYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

     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 TO_NUMBER (TO_CHAR (shipment_dt, 'YYYYMM')) >= '201801') oss,

where shipment_date >= to_date('201801','yyyymm')