DROP VIEW LH_ODS.VW_SHIPMENT_DET_EXT; /* Formatted on 2019/03/09 13:13 (Formatter Plus v4.8.8) */ CREATE OR REPLACE FORCE VIEW lh_ods.vw_shipment_det_ext (spp_shipment_key, referral_number, order_transaction_number, date_written, referral_source, referral_date, patient_first_ship_date, ship_date, full_product_name, product_strength, product_form, ndc_number, product_code, quantity_shipped, quantity_uom, vial_size, days_supplied, dosing_instructions, route_of_administration, maintenance_dose_prescribed, new_to_therapy_flag, frequency_of_infusions, rx_number, rx_type, authorized_refills, fill_number, refills_remaining, icd_9_codes, npi_#, dea#, hub_assigned_prescriber_id, physician_specialty, spp_physician_id, first_name, middle_name, last_name, suffix, physician_address1, physician_address2, physician_city, physician_state, physician_zip, physician_phone, physician_fax, primary_payer_bin, primary_payer_pcn, primary_group_#, primary_payer_id, primary_payer_name, primary_payer_type, primary_benefit_type, primary_plan_id, primary_plan_name, primary_plan_type, primary_pbm_name, primary_co_pay_amount, primary_co_insurance_amount, primary_deductible, pri_pat_out_of_pocket_amount, primary_max_out_of_pocket, primary_lifetime_max, primary_pa_required, primary_pa_expiration_date, secondary_payer_bin, secondary_payer_pcn, secondary_group_#, secondary_payer_id, secondary_payer_name, secondary_payer_type, secondary_benefit_type, secondary_plan_id, secondary_plan_name, secondary_plan_type, secondary_co_pay_amount, secondary_co_insurance_amount, secondary_deductible, patient_out_of_pocket_amount, secondary_max_out_of_pocket, secondary_plan_annual_max, secondary_lifetime_max, secondary_pa_required, secondary_pa_expiration_date, spp_patient_id, hub_identifier, patient_state, patient_yob, patient_gender, patient_age, age_range, patient_weight, patient_zip_code, patient_phi_consent_date, record_id, record_date_timestamp, hub_assigned_sp_id, pharmacy_name, pharmacy_location_name, location_dea, location_npi_number, location_ncpdp_number, lh_raw_presc_id, baxalta_customer_id, baxalta_customer_addr_id, lh_pat_id ) AS SELECT oss.spp_shipment_key, CASE WHEN osmx_rfn.mask_column_val IS NOT NULL THEN TO_CHAR (TO_NUMBER (osmx_rfn.mask_column_val) ) ELSE --TO_CHAR (oss.rfrl_no) (NVL (SUBSTR (oss.rfrl_no, 1, INSTR (oss.rfrl_no, '.', 1) - 1), oss.rfrl_no ) ) END referral_number, CASE WHEN osmx_otn.mask_column_val IS NOT NULL THEN TO_CHAR (osmx_otn.mask_column_val) ELSE --TO_CHAR (oss.order_tran_no) CASE WHEN INSTR ((CASE WHEN oss.src_key = 25 AND NVL (oss.order_tran_no, 'X') <> 'X' AND INSTR (oss.order_tran_no, '-', 1, 1) > 0 THEN SUBSTR (oss.order_tran_no, 1, INSTR (oss.order_tran_no, '-', 1, 1) - 1 ) ELSE oss.order_tran_no END ), '.', 1 ) > 0 THEN SUBSTR ((CASE WHEN oss.src_key = 25 AND NVL (oss.order_tran_no, 'X') <> 'X' AND INSTR (oss.order_tran_no, '-', 1, 1) > 0 THEN SUBSTR (oss.order_tran_no, 1, INSTR (oss.order_tran_no, '-', 1, 1 ) - 1 ) ELSE oss.order_tran_no END ), 1, INSTR ((CASE WHEN oss.src_key = 25 AND NVL (oss.order_tran_no, 'X') <> 'X' AND INSTR (oss.order_tran_no, '-', 1, 1) > 0 THEN SUBSTR (oss.order_tran_no, 1, INSTR (oss.order_tran_no, '-', 1, 1 ) - 1 ) ELSE oss.order_tran_no END ), '.', 1 ) ) ELSE CASE WHEN oss.src_key = 25 AND NVL (oss.order_tran_no, 'X') <> 'X' AND INSTR (oss.order_tran_no, '-', 1, 1) > 0 THEN SUBSTR (oss.order_tran_no, 1, INSTR (oss.order_tran_no, '-', 1, 1) - 1 ) ELSE oss.order_tran_no END END END order_transaction_number, TO_CHAR (oss.rx_written_dt, 'YYYYMMDD') date_written, CASE WHEN oss.src_key = 20 AND oss.rfrl_source = 'DIRECT' THEN 'PRESCRIBER' ELSE oss.rfrl_source END referral_source, TO_CHAR (oss.rfrl_dt_to_hub, 'YYYYMMDD') referral_date, TO_CHAR (pat_first_dis_dt.pat_first_dspnsd_dt, 'YYYYMMDD' ) patient_first_ship_date, TO_CHAR (oss.shipment_dt, 'YYYYMMDD') ship_date, omp.trade_name full_product_name, oss.prod_strength product_strength, omp.prod_form product_form, oss.ndc_no ndc_number, oss.client_prod_cd product_code, ---case when oss.src_key in (25,18) then oss_sum_qty.sum_shipped_qty else oss.shipped_qty end quantity_shipped, oss_sum_qty.sum_shipped_qty quantity_shipped, -- oss.shipped_qty_uom 'VIALS' quantity_uom, oss.shipped_vial_size vial_size, oss.days_supply days_supplied, oss.rx_dirctn dosing_instructions, CASE WHEN oss.route_of_adminstrn IN ('SU', 'SUBQ', 'SUB-Q', 'IM', 'SUBCUTANEOUS', 'SQI', 'SQ', 'SCIG', 'SC', 'IJ') THEN 'SC' WHEN oss.route_of_adminstrn IN ('IV/SQ') THEN 'IV/SC' WHEN oss.route_of_adminstrn IN ('IVP', 'PO', 'AP') THEN 'IV' ELSE oss.route_of_adminstrn END route_of_administration, oss.unique_dose maintenance_dose_prescribed, oss.pat_new_to_drug new_to_therapy_flag, oss.dose_freq frequency_of_infusions, CASE WHEN osmx_rn.mask_column_val IS NOT NULL THEN TO_CHAR (osmx_rn.mask_column_val) ELSE --TO_CHAR (oss.rx_no) (NVL (SUBSTR (oss.rx_no, 1, INSTR (oss.rx_no, '.', 1) - 1), oss.rx_no ) ) END rx_number, oss.rx_typ rx_type, oss.authorized_refills authorized_refills, oss.fill_no fill_number, oss.refill_remaining refills_remaining, oss.pat_diagnosis_cd_1 icd_9_codes, presc.presc_npi_no npi_#, presc.presc_dea_no dea#, presc.hub_presc_id hub_assigned_prescriber_id, DECODE (presc.presc_specialty, '1', 'Immunology', '2', 'Family Practice', '3', 'General Practitioner', '4', 'Internal Medicine', '5', 'Oncology', '6', 'Gastroenterology', '7', 'Infectious Disease', '8', 'Dermatology', '9', 'Nephrology', '10', 'Neurology', '11', 'Ophthalmology', '12', 'Psychiatry', '13', 'Pulmonology', '14', 'Rheumatology', '15', 'Women' || '''' || 's Health', '16', 'Urology', '17', 'Other', '18', 'Not Available', presc.presc_specialty ) physician_specialty, --presc.spp_presc_id NVL (SUBSTR (presc.spp_presc_id, 1, INSTR (presc.spp_presc_id, '.', 1) - 1 ), presc.spp_presc_id ) spp_physician_id, presc.presc_fst_nm first_name, presc.presc_mid_nm middle_name, presc.presc_lst_nm last_name, presc.presc_suffix suffix, presc.presc_addr_line1 physician_address1, presc.presc_addr_line2 physician_address2, presc.presc_city physician_city, presc.presc_state physician_state, presc.presc_pstl_id physician_zip, presc.presc_phone physician_phone, presc.presc_fax physician_fax, orp_pri.pyr_bin primary_payer_bin, orp_pri.pyr_pcn primary_payer_pcn, orp_pri.pyr_grp_id primary_group_#, orp_pri.pyr_id primary_payer_id, orp_pri.pyr_nm primary_payer_name, CASE WHEN UPPER (TRIM (orp_pri.pyr_typ)) = 'WORKERS COMP' THEN 'WORKER''S COMP' ELSE orp_pri.pyr_typ END primary_payer_type, orp_pri.bnft_typ primary_benefit_type, orp_pri.pln_id primary_plan_id, orp_pri.pln_nm primary_plan_name, orp_pri.pln_typ primary_plan_type, orp_pri.pbm_nm primary_pbm_name, oss.prmry_copay_amt primary_co_pay_amount, oss.prmry_coinsur_amt primary_co_insurance_amount, oss.prmry_deductible primary_deductible, oss.prmry_out_of_pckt_amt pri_pat_out_of_pocket_amount, oss.prmry_max_out_of_pckt_amt primary_max_out_of_pocket, oss.prmry_pln_lifetime_max_amt primary_lifetime_max, oss.prmry_pa_rqrd primary_pa_required, TO_CHAR (oss.prmry_pa_exp_dt, 'YYYYMMDD') primary_pa_expiration_date, orp_sec.pyr_bin secondary_payer_bin, orp_sec.pyr_pcn secondary_payer_pcn, orp_sec.pyr_grp_id secondary_group_#, orp_sec.pyr_id secondary_payer_id, orp_sec.pyr_nm secondary_payer_name, CASE WHEN UPPER (TRIM (orp_sec.pyr_typ)) = 'WORKERS COMP' THEN 'WORKER''S COMP' ELSE orp_sec.pyr_typ END secondary_payer_type, orp_sec.bnft_typ secondary_benefit_type, orp_sec.pln_id secondary_plan_id, orp_sec.pln_nm secondary_plan_name, orp_sec.pln_typ secondary_plan_type, oss.scndry_copay_amt secondary_co_pay_amount, oss.scndry_coinsur_amt secondary_co_insurance_amount, oss.scndry_deductible secondary_deductible, oss.scndry_out_of_pckt_amt patient_out_of_pocket_amount, oss.scndry_max_out_of_pckt_amt secondary_max_out_of_pocket, oss.scndry_pln_annual_max_amt secondary_plan_annual_max, oss.scndry_pln_lifetime_max_amt secondary_lifetime_max, oss.scndry_pa_rqrd secondary_pa_required, TO_CHAR (oss.scndry_pa_exp_dt, 'YYYYMMDD' ) secondary_pa_expiration_date, CASE WHEN osmx_spi.mask_column_val IS NOT NULL THEN TO_CHAR (osmx_spi.mask_column_val) ELSE --orp.spp_pat_id NVL (SUBSTR (orp.spp_pat_id, 1, INSTR (orp.spp_pat_id, '.', 1) - 1 ), orp.spp_pat_id ) END spp_patient_id, orp.hub_pat_id hub_identifier, orp.pat_state patient_state, orp.pat_yob patient_yob, orp.pat_gndr patient_gender, DECODE (orp.pat_age, 1, NULL, 2, NULL, 3, NULL, 4, NULL, orp.pat_age ) patient_age, (CASE WHEN lsp.src_provider_name = 'ACCREDO' AND orp.pat_age = 1 THEN 'less than 18' WHEN lsp.src_provider_name = 'ACCREDO' AND orp.pat_age = 2 THEN '18 to 39' WHEN lsp.src_provider_name = 'ACCREDO' AND orp.pat_age = 3 THEN '40 to 59' WHEN lsp.src_provider_name = 'ACCREDO' AND orp.pat_age = 4 THEN '60+' WHEN orp.pat_age NOT IN (1, 2, 3, 4) AND orp.pat_age < 18 THEN 'less than 18' WHEN orp.pat_age NOT IN (1, 2, 3, 4) AND (orp.pat_age >= 18 AND orp.pat_age <= 39) THEN '18 to 39' WHEN orp.pat_age NOT IN (1, 2, 3, 4) AND (orp.pat_age >= 40 AND orp.pat_age <= 59) THEN '40 to 59' WHEN orp.pat_age NOT IN (1, 2, 3, 4) AND (orp.pat_age >= 60) THEN '60+' ELSE TO_CHAR (orp.pat_age) END ) age_range, orp.pat_weight patient_weight, CASE WHEN osmx_ppi.mask_column_val IS NOT NULL THEN TO_CHAR (osmx_ppi.mask_column_val) ELSE orp.pat_pstl_id END patient_zip_code, TO_CHAR (orp.pat_hipaa_consent_strt_dt, 'YYYYMMDD' ) patient_phi_consent_date, /* OSS.OTHER_FLEX_COL_1 Comment_1, OSS.OTHER_FLEX_COL_2 Comment_2, OSS.OTHER_FLEX_COL_3 Comment_3, OSS.OTHER_FLEX_COL_4 Comment_4, OSS.OTHER_FLEX_COL_5 Comment_5, */ oss.src_rec_unq_id record_id, NULL record_date_timestamp, --NULL Restatement_Flag, orsl.hub_specialty_phrcmy_id hub_assigned_sp_id, CASE WHEN oss.src_key = 7 AND oss.spp_loc_key IN (5870, 5884) THEN 'COMFORT-INFUSION' WHEN oss.src_key = 7 AND oss.spp_loc_key IN (5893, 5881) THEN 'FOCUSRX' WHEN oss.src_key = 7 AND oss.spp_loc_key IN (186, 197, 196) THEN 'BIORX' WHEN oss.src_key = 7 AND oss.spp_loc_key IN (5886, 5818) THEN 'ACCURATERX' WHEN oss.src_key = 7 AND oss.spp_loc_key IN (5007) THEN 'DIPLOMAT-AFFINITY' WHEN oss.src_key = 7 AND oss.spp_loc_key IN (175, 188, 164, 5869, 75, 804, 379, 154, 190, 5003, 5868, 810, 843, 801, 163, 189, 3759, 5686, 373, 839, 149, 374, 173, 185, 406, 618, 840, 841, 3760, 724, 814, 807, 808, 723, 803, 806, 842, 3408, 3410) THEN 'DIPLOMAT' ELSE lsp.src_provider_name END pharmacy_name, orsl.phrcmy_loc_nm pharmacy_location_name, orsl.phrcmy_loc_dea_no location_dea, orsl.phrcmy_loc_npi_no location_npi_number, orsl.phrcmy_loc_ncpdp_no location_ncpdp_number, presc.presc_key lh_raw_presc_id, presc.veeva_mdm_presc_id baxalta_customer_id, presc.veeva_mdm_addr_id baxalta_customer_addr_id, orp.mdm_pat_id /* ---Layout1 ,PRESC.PRESC_ADDR_LINE3 Physician_Address3, OSS.INVOICE_NO Invoice_Number, */ --,OSS.INSERT_SRC_FILENAME FROM (SELECT a.* FROM lh_ods.ods_spp_shipment a, lh_ods.ods_master_prod b WHERE a.ndc_no = b.ndc_no AND a.file_typ_id < 40 AND NVL (a.SIGN, 'X') IN ('X', '+') AND a.shipment_dt <= TRUNC (SYSDATE) ----and a.ndc_no='00944270007' and src_key=10 and client_prod_cd='LE12Q062AB' and order_tran_no='12958927' ) oss INNER JOIN (SELECT MAX (a.spp_shipment_key) spp_shipment_key, a.src_key FROM lh_ods.ods_spp_shipment a, lh_ods.ods_master_prod b, lh_ods.ods_raw_patient c WHERE a.ndc_no = b.ndc_no AND a.file_typ_id < 40 AND a.src_key = c.src_key AND a.pat_key = c.pat_key --AND a.src_key IN (25) AND NVL (a.SIGN, 'X') IN ('X', '+') AND a.shipment_dt <= TRUNC (SYSDATE) GROUP BY a.src_key, c.spp_pat_id, a.ndc_no, a.shipment_dt, CASE WHEN NVL (a.order_tran_no, 'X') <> 'X' AND a.src_key = 25 AND INSTR (a.order_tran_no, '-', 1, 1) > 0 THEN SUBSTR (a.order_tran_no, 1, INSTR (a.order_tran_no, '-', 1, 1) - 1 ) WHEN a.src_key = 18 THEN '-786' ELSE NVL (a.order_tran_no, '-786') END, CASE WHEN a.src_key = 18 THEN '-786' ELSE NVL (a.client_prod_cd, 'X') END, CASE WHEN a.product_id IN (3, 4) THEN NVL (a.prod_strength, 'X') ELSE 'X' END, a.product_id) oss_otn ON oss.src_key = oss_otn.src_key AND oss.spp_shipment_key = oss_otn.spp_shipment_key LEFT JOIN (SELECT a.src_key, a.pat_key, a.ndc_no, a.shipment_dt, CASE WHEN a.src_key = 25 AND NVL (a.order_tran_no, 'X') <> 'X' AND INSTR (a.order_tran_no, '-', 1, 1) > 0 THEN SUBSTR (a.order_tran_no, 1, INSTR (a.order_tran_no, '-', 1, 1) - 1 ) WHEN a.src_key = 18 THEN '-786' ELSE NVL (a.order_tran_no, 'X') END order_tran_no, CASE WHEN a.src_key = 18 THEN '-786' ELSE NVL (a.client_prod_cd, 'X') END client_prod_cd, CASE WHEN a.product_id NOT IN (3, 4) THEN '-786' ELSE NVL (a.prod_strength, 'X') END prod_strength, a.product_id, SUM (a.shipped_qty) sum_shipped_qty FROM lh_ods.ods_spp_shipment a WHERE a.file_typ_id < 40 ---AND a.src_key IN (25, 18) GROUP BY a.src_key, a.pat_key, a.ndc_no, a.shipment_dt, CASE WHEN a.src_key = 25 AND NVL (a.order_tran_no, 'X') <> 'X' AND INSTR (a.order_tran_no, '-', 1, 1) > 0 THEN SUBSTR (a.order_tran_no, 1, INSTR (a.order_tran_no, '-', 1, 1) - 1 ) WHEN a.src_key = 18 THEN '-786' ELSE NVL (a.order_tran_no, 'X') END, CASE WHEN a.src_key = 18 THEN '-786' ELSE NVL (a.client_prod_cd, 'X') END, CASE WHEN a.product_id NOT IN (3, 4) THEN '-786' ELSE NVL (a.prod_strength, 'X') END, a.product_id) oss_sum_qty ON oss.src_key = oss_sum_qty.src_key AND oss.pat_key = oss_sum_qty.pat_key AND oss.ndc_no = oss_sum_qty.ndc_no AND oss.shipment_dt = oss_sum_qty.shipment_dt AND CASE WHEN oss.src_key = 25 AND NVL (oss.order_tran_no, 'X') <> 'X' AND INSTR (oss.order_tran_no, '-', 1, 1) > 0 THEN SUBSTR (oss.order_tran_no, 1, INSTR (oss.order_tran_no, '-', 1, 1) - 1 ) WHEN oss.src_key = 18 THEN '-786' ELSE NVL (oss.order_tran_no, 'X') END = NVL (oss_sum_qty.order_tran_no, 'X') AND CASE WHEN oss.src_key = 18 THEN '-786' ELSE NVL (oss.client_prod_cd, 'X') END = NVL (oss_sum_qty.client_prod_cd, 'X') AND CASE WHEN oss.product_id NOT IN (3, 4) THEN '-786' ELSE NVL (oss.prod_strength, 'X') END = CASE WHEN oss_sum_qty.product_id NOT IN (3, 4) THEN '-786' ELSE NVL (oss_sum_qty.prod_strength, 'X') END AND oss.product_id = oss_sum_qty.product_id LEFT JOIN lh_ods.ods_raw_patient orp ON oss.pat_key = orp.pat_key AND oss.src_key = orp.src_key LEFT JOIN lh_ods.ods_raw_prescriber presc ON oss.presc_key = presc.presc_key AND oss.src_key = presc.src_key LEFT JOIN lh_ods.ods_raw_payer orp_pri ON oss.prmry_pyr_key = orp_pri.pyr_key AND oss.src_key = orp_pri.src_key LEFT JOIN lh_ods.ods_raw_payer orp_sec ON oss.scndry_pyr_key = orp_sec.pyr_key AND oss.src_key = orp_sec.src_key LEFT JOIN lh_ods.ods_raw_spp_loc orsl ON oss.spp_loc_key = orsl.spp_loc_key AND oss.src_key = orsl.src_key LEFT JOIN lh_logging.lh_src_provider lsp ON oss.src_key = lsp.src_provider_id LEFT JOIN lh_ods.ods_master_prod omp ON oss.ndc_no = omp.ndc_no LEFT JOIN lh_logging.lh_file_type_master lftm ON oss.file_typ_id = lftm.file_typ_id LEFT JOIN (SELECT * FROM lh_ods.ods_spp_mask_xref WHERE column_nm = 'PAT_PSTL_ID') osmx_ppi ON oss.src_key = osmx_ppi.src_key AND osmx_ppi.actual_column_val = orp.pat_pstl_id LEFT JOIN (SELECT src_key, column_nm, actual_column_val, TO_NUMBER (mask_column_val) mask_column_val FROM lh_ods.ods_spp_mask_xref WHERE column_nm = 'ORDER_TRAN_NO') osmx_otn ON oss.src_key = osmx_otn.src_key AND osmx_otn.actual_column_val = oss.order_tran_no LEFT JOIN (SELECT src_key, column_nm, actual_column_val, TO_NUMBER (mask_column_val) mask_column_val FROM lh_ods.ods_spp_mask_xref WHERE column_nm = 'RX_NO') osmx_rn ON oss.src_key = osmx_rn.src_key AND osmx_rn.actual_column_val = oss.rx_no LEFT JOIN (SELECT * FROM lh_ods.ods_spp_mask_xref WHERE column_nm = 'RFRL_NO') osmx_rfn ON oss.src_key = osmx_rfn.src_key AND osmx_rfn.actual_column_val = oss.rfrl_no LEFT JOIN (SELECT * FROM lh_ods.ods_spp_mask_xref WHERE column_nm = 'SPP_PAT_ID') osmx_spi ON oss.src_key = osmx_spi.src_key AND osmx_spi.actual_column_val = orp.spp_pat_id LEFT JOIN (SELECT DISTINCT a.src_key, a.pat_key, a.product_id, CASE WHEN a.pat_first_ship_dt IS NULL THEN a.ship_dt WHEN a.pat_first_ship_dt IS NOT NULL AND a.ship_dt < a.pat_first_ship_dt THEN a.ship_dt ELSE a.pat_first_ship_dt END pat_first_dspnsd_dt FROM (SELECT a.src_key, a.product_id, a.pat_key, MIN (a.shipment_dt) OVER (PARTITION BY a.src_key, a.pat_key, a.product_id ORDER BY a.src_key, a.pat_key, a.product_id) ship_dt, MIN (CASE WHEN NVL (a.pat_first_dspnsd_dt, TO_DATE ('01/01/1900', 'MM/DD/YYYY' ) ) BETWEEN TO_DATE (b.launch_dt, 'MM/DD/YYYY' ) AND a.shipment_dt THEN a.pat_first_dspnsd_dt ELSE NULL END ) OVER (PARTITION BY a.src_key, a.pat_key, a.product_id ORDER BY a.src_key, a.pat_key, a.product_id) pat_first_ship_dt FROM lh_ods.ods_spp_shipment a, (SELECT qc_prod_grp_id product_id, col_val1 launch_dt FROM lh_logging.qc_checks_data_vw WHERE file_typ_id = 35 AND column_name = 'RFRL_STRT_DT' AND qc_code = 'RGDT1') b WHERE a.product_id = b.product_id AND a.file_typ_id < 40 AND NVL (SIGN, 'X') IN ('X', '+') AND a.shipment_dt <= TRUNC (SYSDATE)) a) pat_first_dis_dt ON oss.src_key = pat_first_dis_dt.src_key AND oss.product_id = pat_first_dis_dt.product_id AND oss.pat_key = pat_first_dis_dt.pat_key AND oss.file_typ_id < 40 ; GRANT SELECT ON LH_ODS.VW_SHIPMENT_DET_EXT TO PUBLIC; CREATE TABLE LH_ODS.ODS_SPP_SHIPMENT ( SPP_SHIPMENT_KEY NUMBER NOT NULL, SRC_KEY VARCHAR2(30 BYTE) NOT NULL, PRESC_KEY NUMBER, PAT_KEY NUMBER, SPP_LOC_KEY NUMBER, PRMRY_PYR_KEY NUMBER, SCNDRY_PYR_KEY NUMBER, PAT_DIAGNOSIS_DT DATE, PAT_DIAGNOSIS_CD_1 VARCHAR2(15 BYTE), PAT_DIAGNOSIS_CD_2 VARCHAR2(15 BYTE), PAT_DIAGNOSIS_CD_3 VARCHAR2(15 BYTE), PAT_DIAGNOSIS_CD_4 VARCHAR2(15 BYTE), PAT_NEW_TO_DRUG VARCHAR2(3 BYTE), PAT_PRIOR_THRPY_1 VARCHAR2(50 BYTE), PAT_PRIOR_THRPY_2 VARCHAR2(50 BYTE), PAT_PRIOR_THRPY_3 VARCHAR2(50 BYTE), PAT_PRIOR_THRPY_4 VARCHAR2(50 BYTE), PAT_PRIOR_THRPY_5 VARCHAR2(50 BYTE), PAT_PRIOR_REGIMEN VARCHAR2(50 BYTE), DUR_ON_PRIOR_THRPY VARCHAR2(50 BYTE), DUR_ON_PRIOR_THRPY_1 VARCHAR2(50 BYTE), DUR_ON_PRIOR_THRPY_2 VARCHAR2(50 BYTE), DUR_ON_PRIOR_THRPY_3 VARCHAR2(50 BYTE), DUR_ON_PRIOR_THRPY_4 VARCHAR2(50 BYTE), DISCONT_REAS_ON_PRIOR_THRPY VARCHAR2(50 BYTE), PAT_PREV_SITE_CARE VARCHAR2(50 BYTE), PAT_CURR_THRPY_1 VARCHAR2(50 BYTE), PAT_CURR_THRPY_2 VARCHAR2(50 BYTE), PAT_CURR_THRPY_3 VARCHAR2(50 BYTE), PAT_CURR_THRPY_4 VARCHAR2(50 BYTE), PAT_CURR_THRPY_5 VARCHAR2(50 BYTE), DUR_ON_NEW_THRPY VARCHAR2(50 BYTE), PAT_CURR_REGIMEN VARCHAR2(50 BYTE), PAT_INSUR_STAT VARCHAR2(50 BYTE), PAT_STUDY_ID VARCHAR2(30 BYTE), PAT_SERVICES_DT DATE, NDC_NO VARCHAR2(13 BYTE), CLIENT_PROD_CD VARCHAR2(30 BYTE), RFRL_NO VARCHAR2(50 BYTE), RX_NO VARCHAR2(50 BYTE), RX_TYP VARCHAR2(3 BYTE), AUTHORIZED_REFILLS NUMBER, RFRL_STRT_DT DATE, RFRL_DT_TO_HUB DATE, ROUTE_OF_ADMINSTRN VARCHAR2(50 BYTE), UNIQUE_DOSE NUMBER, RX_WRITTEN_DT DATE, DRUG_AUTH_ID VARCHAR2(30 BYTE), CASE_ID VARCHAR2(30 BYTE), ORDER_TRAN_NO VARCHAR2(50 BYTE), ORDER_TRAN_TYP VARCHAR2(30 BYTE), TRAN_TYP VARCHAR2(30 BYTE), ORDER_DT DATE, SHIPMENT_ID VARCHAR2(30 BYTE), INVOICE_COST VARCHAR2(30 BYTE), INVOICE_NO VARCHAR2(30 BYTE), SHIPMENT_DT DATE, LOT_NO VARCHAR2(30 BYTE), LOT_EXPRY_DT DATE, ACTUAL_POTENCY NUMBER, ORDER_LINE_NO NUMBER, TOT_ORDER_LINES NUMBER, SHIPPED_QTY_UOM VARCHAR2(30 BYTE), SHIPPED_QTY NUMBER, SHIPPED_VIAL_SIZE VARCHAR2(30 BYTE), UNIT_PRICE NUMBER, TOT_SHIP_PRICE NUMBER, DAYS_SUPPLY NUMBER, FILL_NO NUMBER, PAT_FIRST_DSPNSD_DT DATE, LAST_FILL_DT DATE, NEXT_FILL_DT DATE, REFILL_REMAINING NUMBER, ADDTNL_SHIPMENT_INFO VARCHAR2(100 BYTE), DOSE_FREQ VARCHAR2(30 BYTE), SELF_ADMINSTRD VARCHAR2(30 BYTE), SHIP_CARRIER VARCHAR2(100 BYTE), SHIP_TRACK_NO VARCHAR2(50 BYTE), SHIP_TO_ADDR_TYP VARCHAR2(30 BYTE), SHIP_FIRST_NM VARCHAR2(50 BYTE), SHIP_LAST_NM VARCHAR2(50 BYTE), SHIP_ADDR_LINE1 VARCHAR2(100 BYTE), SHIP_ADDR_LINE2 VARCHAR2(100 BYTE), SHIP_ADDR_LINE3 VARCHAR2(100 BYTE), SHIP_CITY VARCHAR2(100 BYTE), SHIP_STATE VARCHAR2(2 BYTE), SHIP_PSTL_ID VARCHAR2(15 BYTE), SHIP_PHONE VARCHAR2(20 BYTE), SHIP_FAX VARCHAR2(20 BYTE), RX_DIRCTN VARCHAR2(2000 BYTE), LEAVE_PHONE_MSG_IND VARCHAR2(3 BYTE), MEDICAL_GUIDE_PROVIDED VARCHAR2(3 BYTE), PRMRY_COPAY_TYP VARCHAR2(30 BYTE), PRMRY_COPAY_AMT NUMBER, PRMRY_DEDUCTIBLE NUMBER, PRMRY_COINSUR_RT NUMBER, PRMRY_COINSUR_AMT NUMBER, PRMRY_OUT_OF_PCKT_AMT NUMBER, PRMRY_MAX_OUT_OF_PCKT_AMT NUMBER, PRMRY_PLN_ANNUAL_MAX_AMT NUMBER, PRMRY_PLN_LIFETIME_MAX_AMT NUMBER, PRMRY_INSUR_COVERED VARCHAR2(3 BYTE), PRMRY_INSUR_EFCTV_DT DATE, PRMRY_DEDUCTIBLE_MET VARCHAR2(3 BYTE), PRMRY_DEDUCTIBLE_AMT_REMAIN NUMBER, PRMRY_PA_RQRD VARCHAR2(3 BYTE), PRMRY_PA_ID VARCHAR2(3 BYTE), PRMRY_PA_EXP_DT DATE, PRMRY_PA_OUTCOME VARCHAR2(30 BYTE), PRMRY_PA_OUTCOME_DT DATE, PRMRY_DENIAL_REAS VARCHAR2(30 BYTE), PRMRY_THRPY_REQ VARCHAR2(3 BYTE), PRMRY_APPEAL_BEGIN_DT DATE, PRMRY_APPEAL_OUTCOME VARCHAR2(30 BYTE), SCNDRY_COPAY_TYP VARCHAR2(30 BYTE), SCNDRY_COPAY_AMT NUMBER, SCNDRY_DEDUCTIBLE NUMBER, SCNDRY_COINSUR_RATE NUMBER, SCNDRY_COINSUR_AMT NUMBER, SCNDRY_OUT_OF_PCKT_AMT NUMBER, SCNDRY_MAX_OUT_OF_PCKT_AMT NUMBER, SCNDRY_PLN_ANNUAL_MAX_AMT NUMBER, SCNDRY_PLN_LIFETIME_MAX_AMT NUMBER, SCNDRY_INSUR_COVERED VARCHAR2(3 BYTE), SCNDRY_INSUR_EFFCT_DT DATE, SCNDRY_DEDUCTIBLE_MET VARCHAR2(3 BYTE), SCNDRY_DEDUCTIBLE_AMT_REMAIN NUMBER, SCNDRY_PA_RQRD VARCHAR2(3 BYTE), SCNDRY_PA_ID VARCHAR2(30 BYTE), SCNDRY_PA_EXP_DT DATE, SCNDRY_PA_OUTCOME VARCHAR2(30 BYTE), SCNDRY_PA_OUTCOME_DT DATE, SCNDRY_DEIAL_REAS VARCHAR2(30 BYTE), SCNDRY_THRPY_REQ VARCHAR2(3 BYTE), SCNDRY_APPEAL_BEGIN_DT DATE, SCNDRY_APPEAL_OUTCOME VARCHAR2(30 BYTE), RECORD_CD VARCHAR2(30 BYTE), SRC_REC_UNQ_ID VARCHAR2(30 BYTE), PAT_HIPAA_CONSENT_VRB_CONF_FLG VARCHAR2(15 BYTE), RFRL_SOURCE VARCHAR2(100 BYTE), OTHER_FLEX_COL_1 VARCHAR2(200 BYTE), OTHER_FLEX_COL_2 VARCHAR2(200 BYTE), OTHER_FLEX_COL_3 VARCHAR2(200 BYTE), OTHER_FLEX_COL_4 VARCHAR2(200 BYTE), OTHER_FLEX_COL_5 VARCHAR2(200 BYTE), OTHER_FLEX_COL_6 VARCHAR2(200 BYTE), OTHER_FLEX_COL_7 VARCHAR2(200 BYTE), OTHER_FLEX_COL_8 VARCHAR2(200 BYTE), OTHER_FLEX_COL_9 VARCHAR2(200 BYTE), OTHER_FLEX_COL_10 VARCHAR2(200 BYTE), OTHER_FLEX_COL_11 VARCHAR2(200 BYTE), OTHER_FLEX_COL_12 VARCHAR2(200 BYTE), OTHER_FLEX_COL_13 NUMBER, OTHER_FLEX_COL_14 NUMBER, OTHER_FLEX_COL_15 NUMBER, OTHER_FLEX_COL_16 NUMBER, OTHER_FLEX_COL_17 DATE, OTHER_FLEX_COL_18 DATE, OTHER_FLEX_COL_19 DATE, OTHER_FLEX_COL_20 DATE, SHIP_TO_SITE_ID VARCHAR2(100 BYTE), SHIP_TO_SITE_NM VARCHAR2(100 BYTE), STD_QTY_UOM VARCHAR2(30 BYTE), STD_SHIPPED_QTY NUMBER, FILE_TYP_ID NUMBER, CLIENT_ID VARCHAR2(15 BYTE) NOT NULL, PRODUCT_ID VARCHAR2(15 BYTE) NOT NULL, INSERT_LAND_RAW_ID NUMBER, INSERT_SRC_FILENAME VARCHAR2(250 BYTE) NOT NULL, INSERT_TIMESTAMP DATE NOT NULL, INSERT_PROCESS_NM VARCHAR2(250 BYTE) NOT NULL, UPDATE_LAND_RAW_ID NUMBER, UPDATE_SRC_FILENAME VARCHAR2(250 BYTE), UPDATE_TIMESTAMP DATE, UPDATE_PROCESS_NM VARCHAR2(250 BYTE), CONCURRENT_PRODUCTS VARCHAR2(100 BYTE), PROD_STRENGTH VARCHAR2(50 BYTE), SIGN VARCHAR2(1 BYTE), OTHER_FLEX_COL_23 VARCHAR2(200 BYTE) ) ; SET DEFINE OFF; Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (745585, '1', 22545, 39222, 394, 23636, 17541, 'G63.0', 'Y', '00944270006', '1000123250', '1000123250', 'R', 13, TO_DATE('08/25/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('08/25/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '4711628', TO_DATE('11/30/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 4, '20', 1, 4, TO_DATE('11/30/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 9, 'Q3W', 'GAMMAGARD 80GM/800ML INFUSE 80GM / 800MLS IV EVERY 3 WEEKS. INFUSE AT RATES PROVIDED ON DOSE RATE', 'Y', TO_DATE('09/01/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '26389379942', 'PRESCRIBER', 'VIAL', 4, 36, '100', '2', 573121, 'DISPENSE_AXELACARE_20160523.TXT', TO_DATE('06/28/2016 09:09:54', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '20', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (745586, '1', 22545, 39222, 394, 23636, 17541, 'G63.0', 'Y', '00944270006', '1000123250', '1000123250', 'R', 13, TO_DATE('08/25/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('08/25/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '4946529', TO_DATE('12/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 4, '20', 1, 5, TO_DATE('12/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 8, 'Q3W', 'GAMMAGARD 80GM/800ML INFUSE 80GM / 800MLS IV EVERY 3 WEEKS. INFUSE AT RATES PROVIDED ON DOSE RATE', 'Y', TO_DATE('09/01/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '26389320476', 'PRESCRIBER', 'VIAL', 4, 36, '100', '2', 573122, 'DISPENSE_AXELACARE_20160523.TXT', TO_DATE('06/28/2016 09:09:54', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '20', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, REFILL_REMAINING, RX_DIRCTN, PRMRY_PA_RQRD, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (3257115, '20', 51282, 67126, 755, 33715, -20, 'G61.81', 'N', '00944270004', '9005341093', '437441792', 'R', 11, TO_DATE('02/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('02/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '900534109383586347417927', TO_DATE('12/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 1, '5', 28, 7, 4, 'INFUSE 76GM (760ML) INTRAVENOUSLY EVERY 4 WEEKS. (FOR ADMINISTRATION AT INFUSION CENTER)', 'Y', '83586347417927', 'PRESCRIBER', 'VIAL', 1, 36, '100', '2', 2896302, 'DISPENSE_CVSCAREMARK_SP002B_20171218.TXT', TO_DATE('03/20/2018 10:58:03', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '1000', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, REFILL_REMAINING, RX_DIRCTN, PRMRY_PA_RQRD, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (3257116, '20', 51282, 67126, 755, 33715, -20, 'G61.81', 'N', '00944270004', '9005341093', '437441792', 'R', 11, TO_DATE('02/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('02/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '900534109383586347417928', TO_DATE('01/15/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 1, '5', 28, 8, 3, 'INFUSE 76GM (760ML) INTRAVENOUSLY EVERY 4 WEEKS. (FOR ADMINISTRATION AT INFUSION CENTER)', 'Y', '83586347417928', 'PRESCRIBER', 'VIAL', 1, 36, '100', '2', 3001611, 'DISPENSE_CVSCAREMARK_SP002B_20180122.TXT', TO_DATE('03/20/2018 10:58:04', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '1', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, REFILL_REMAINING, RX_DIRCTN, PRMRY_PA_RQRD, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (3257117, '20', 51282, 67126, 755, 33715, -20, 'G61.81', 'N', '00944270004', '9005341093', '437441792', 'R', 11, TO_DATE('02/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('02/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '900534109383586347417929', TO_DATE('02/06/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 1, '5', 28, 9, 2, 'INFUSE 76GM (760ML) INTRAVENOUSLY EVERY 4 WEEKS. (FOR ADMINISTRATION AT INFUSION CENTER)', 'Y', '83586347417929', 'PRESCRIBER', 'VIAL', 1, 36, '100', '2', 3085403, 'DISPENSE_CVSCAREMARK_SP002B_20180212.TXT', TO_DATE('03/20/2018 10:58:04', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '1', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, REFILL_REMAINING, RX_DIRCTN, PRMRY_PA_RQRD, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (3257118, '20', 51282, 67126, 755, 33715, -20, 'G61.81', 'N', '00944270004', '9005341093', '272212554', 'N', 12, TO_DATE('02/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('03/02/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '900534109312222610253372225540', TO_DATE('03/07/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 1, '5', 28, 0, 12, 'INFUSE 76GM (760ML) INTRAVENOUSLY EVERY 4 WEEKS. (FOR ADMINISTRATION AT INFUSION CENTER)', 'Y', '12222610253372225540', 'PRESCRIBER', 'VIAL', 1, 36, '100', '2', 3229183, 'DISPENSE_CVSCAREMARK_SP002B_20180312.TXT', TO_DATE('03/20/2018 10:58:04', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '1', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, REFILL_REMAINING, RX_DIRCTN, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (3257119, '20', 51282, 67126, 755, 33715, -20, 'G61.81', 'Y', '00944270005', '9005341093', '437441628', 'N', 0, TO_DATE('02/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('02/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '900534109383586347416280', TO_DATE('03/06/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 2, '10', 2, 0, 0, 'INFUSE 76GM (760ML) INTRAVENOUSLY, DAILY FOR 2 DAYS. (LOADING DOSE, FOR ADMINISTRATION AT INFUSION CENTER)', '83586347416280', 'PRESCRIBER', 'VIAL', 2, 36, '100', '2', 1528758, 'DISPENSE_CVSCAREMARK_SP002B_20170313.TXT', TO_DATE('03/20/2018 10:58:03', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '1000', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (745592, '1', 22545, 39222, 394, 23636, 17541, 'G63.0', 'Y', '00944270007', '1000114439', '1000114439', 'R', 8, TO_DATE('02/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('02/18/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '3658710', TO_DATE('08/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 2, '30', 1, 7, TO_DATE('08/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 'Q3W', 'GAMMAGARD 80GM/800ML INFUSE 80GM / 800MLS IV EVERY 3 WEEKS. INFUSE AT RATES PROVIDED ON DOSE RATE', 'Y', TO_DATE('06/04/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '17174866990', 'PRESCRIBER', 'VIAL', 2, 36, '100', '2', 573129, 'DISPENSE_AXELACARE_20160523.TXT', TO_DATE('06/28/2016 09:09:54', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '30', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, REFILL_REMAINING, RX_DIRCTN, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (3257120, '20', 51282, 67126, 755, 33715, -20, 'G61.81', 'N', '00944270005', '9005341093', '437441770', 'N', 11, TO_DATE('02/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('02/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '900534109383586347417700', TO_DATE('03/29/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 1, '10', 28, 0, 11, 'INFUSE 76GM (760ML) INTRAVENOUSLY EVERY 4 WEEKS. (FOR ADMINISTRATION AT INFUSION CENTER)', '83586347417700', 'PRESCRIBER', 'VIAL', 1, 36, '100', '2', 1718554, 'DISPENSE_CVSCAREMARK_SP002B_20170403.TXT', TO_DATE('03/20/2018 10:58:03', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '1000', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, UNIQUE_DOSE, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_OUT_OF_PCKT_AMT, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (1662305, '22', 36911, 35191, 368, 49123, 45337, 'M60.9', 'Y', '00944270004', '000275', '0020049979', 'R', 10, TO_DATE('09/26/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', 25, TO_DATE('01/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '310191', TO_DATE('03/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 5, '5', 14, 4, TO_DATE('01/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 'Q2W', 'INFUSE GAMMAGARD 25 GRAMS/250 MLS IV FOR 1 DAY', 0, 'N', TO_DATE('10/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '39', 'PRESCRIBER', 'VIAL', 5, 36, '100', '2', 1526653, 'DISPENSE_KABAFUSION_20170313.TXT', TO_DATE('03/13/2017 05:53:01', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '5', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, UNIQUE_DOSE, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_OUT_OF_PCKT_AMT, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (1662306, '22', 36911, 35192, 368, 46525, 52596, 'G61.81', 'Y', '00944270007', '000332', '0020049960', 'R', 24, TO_DATE('10/02/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', 90, TO_DATE('02/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '310208', TO_DATE('03/08/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 3, '30', 3, 6, TO_DATE('01/04/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 17, 'NO LINK', 'INFUSE GAMMAGARD 30 GRAMS/300MLS IV FOR 2', 0, 'Y', TO_DATE('06/30/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '61', 'PRESCRIBER', 'VIAL', 3, 36, '100', '2', 1526654, 'DISPENSE_KABAFUSION_20170313.TXT', TO_DATE('03/13/2017 05:53:01', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '30', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, UNIQUE_DOSE, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_OUT_OF_PCKT_AMT, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (1662307, '22', 17365, 35193, 368, 48854, 45337, 'M33.20', 'Y', '00944270005', '000447', '0020048933', 'R', 36, TO_DATE('10/25/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', 150, TO_DATE('10/04/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '310129', TO_DATE('03/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 15, '10', 3, 3, TO_DATE('01/14/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 30, 'NO LINK', 'GAMMAGARD LIQUID 50 GRAMS IV FOR 3 DAYS EVERY', 2319.26, 'N', TO_DATE('03/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '109', 'PRESCRIBER', 'VIAL', 15, 36, '100', '2', 1526655, 'DISPENSE_KABAFUSION_20170313.TXT', TO_DATE('03/13/2017 05:53:01', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '10', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, UNIQUE_DOSE, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_OUT_OF_PCKT_AMT, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (1662308, '22', 17387, 35208, 368, 50143, 45337, 'G61.81', 'Y', '00944270005', '001647', '0020049989', 'R', 6, TO_DATE('05/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', 50, TO_DATE('01/26/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '310234', TO_DATE('03/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 5, '10', 1, 4, TO_DATE('01/05/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 'QOW', 'INFUSE GAMMAGARD 50 GRAMS/500MLS IV', 8.25, 'Y', TO_DATE('06/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '85', 'PRESCRIBER', 'VIAL', 5, 36, '100', '2', 1526662, 'DISPENSE_KABAFUSION_20170313.TXT', TO_DATE('03/13/2017 05:53:01', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '10', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (745598, '1', 21695, 39225, 96, 21864, 30496, 'D83.9', 'Y', '00944270005', '7000115667', '7000115667', 'R', 13, TO_DATE('06/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('06/16/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '4732183', TO_DATE('11/27/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 1, '10', 1, 6, TO_DATE('11/27/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 'Q4W', 'GAMMAGARD LQ 10% 40GM VIALS (DOSE=40GM) TRANSFER 40 GRAMS (400ML) FROM VIALS INTO EMPTY BAG AND INFUSE I.V. ONCE EVERY 4 WEEKS VIA CADD PUMP.', 'Y', TO_DATE('02/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '22323529673', 'PRESCRIBER', 'VIAL', 1, 36, '100', '2', 599792, 'DISPENSE_AXELACARE_20160523.TXT', TO_DATE('06/28/2016 09:09:56', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '10', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (745599, '1', 21695, 39225, 96, 21864, 30496, 'D83.9', 'Y', '00944270005', '7000115667', '7000115667', 'R', 13, TO_DATE('06/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('06/16/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '5051113', TO_DATE('12/28/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 1, '10', 1, 7, TO_DATE('12/28/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6, 'Q4W', 'GAMMAGARD LQ 10% 40GM VIALS (DOSE=40GM) TRANSFER 40 GRAMS (400ML) FROM VIALS INTO EMPTY BAG AND INFUSE I.V. ONCE EVERY 4 WEEKS VIA CADD PUMP.', 'Y', TO_DATE('02/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '22323535672', 'PRESCRIBER', 'VIAL', 1, 36, '100', '2', 599794, 'DISPENSE_AXELACARE_20160523.TXT', TO_DATE('06/28/2016 09:09:56', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '10', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, UNIQUE_DOSE, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_OUT_OF_PCKT_AMT, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (1662309, '22', 17397, 35209, 368, 50723, 45960, 'G70.01', 'Y', '00944270004', '001682', '0020049519', 'R', 12, TO_DATE('05/16/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', 10, TO_DATE('12/06/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '309836', TO_DATE('03/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 2, '5', 2, 5, TO_DATE('01/12/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 'NO LINK', 'GAMMAGARD LIQUID 45 GRAMS/450ML IV DAILY', 0, 'N', TO_DATE('04/26/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '102', 'PRESCRIBER', 'VIAL', 2, 36, '100', '2', 1526664, 'DISPENSE_KABAFUSION_20170313.TXT', TO_DATE('03/13/2017 05:53:01', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '5', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (745601, '1', 21695, 39225, 96, 21864, 30496, 'D83.9', 'Y', '00944270006', '7000104213', '7000104213', 'R', 13, TO_DATE('07/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('06/18/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '2412722', TO_DATE('01/29/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 2, '20', 1, 7, TO_DATE('01/29/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6, 'Q4W', 'GAMMAGARD LQ 10% 40GM VIALS (DOSE=40GM) TRANSFER 40 GRAMS (400ML) FROM VIALS INTO EMPTY BAG AND INFUSE I.V. ONCE EVERY 4 WEEKS VIA CADD PUMP.', 'Y', TO_DATE('02/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '13198588603', 'PRESCRIBER', 'VIAL', 2, 36, '100', '2', 599805, 'DISPENSE_AXELACARE_20160523.TXT', TO_DATE('06/28/2016 09:09:56', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '20', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (745602, '1', 21695, 39225, 96, 21864, 30496, 'D83.9', 'Y', '00944270006', '7000104213', '7000104213', 'R', 13, TO_DATE('07/08/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('06/18/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '2970206', TO_DATE('05/20/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 2, '20', 1, 8, TO_DATE('05/20/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5, 'Q4W', 'GAMMAGARD LQ 10% 40GM VIALS (DOSE=40GM) TRANSFER 40 GRAMS (400ML) FROM VIALS INTO EMPTY BAG AND INFUSE I.V. ONCE EVERY 4 WEEKS VIA CADD PUMP.', 'Y', TO_DATE('02/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '13198513715', 'PRESCRIBER', 'VIAL', 2, 36, '100', '2', 599806, 'DISPENSE_AXELACARE_20160523.TXT', TO_DATE('06/28/2016 09:09:56', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '20', '+'); Insert into LH_ODS.ODS_SPP_SHIPMENT (SPP_SHIPMENT_KEY, SRC_KEY, PRESC_KEY, PAT_KEY, SPP_LOC_KEY, PRMRY_PYR_KEY, SCNDRY_PYR_KEY, PAT_DIAGNOSIS_CD_1, PAT_NEW_TO_DRUG, NDC_NO, RFRL_NO, RX_NO, RX_TYP, AUTHORIZED_REFILLS, RFRL_DT_TO_HUB, ROUTE_OF_ADMINSTRN, RX_WRITTEN_DT, ORDER_TRAN_NO, SHIPMENT_DT, SHIPPED_QTY_UOM, SHIPPED_QTY, SHIPPED_VIAL_SIZE, DAYS_SUPPLY, FILL_NO, PAT_FIRST_DSPNSD_DT, REFILL_REMAINING, DOSE_FREQ, RX_DIRCTN, PRMRY_PA_RQRD, PRMRY_PA_EXP_DT, SRC_REC_UNQ_ID, RFRL_SOURCE, STD_QTY_UOM, STD_SHIPPED_QTY, FILE_TYP_ID, CLIENT_ID, PRODUCT_ID, INSERT_LAND_RAW_ID, INSERT_SRC_FILENAME, INSERT_TIMESTAMP, INSERT_PROCESS_NM, PROD_STRENGTH, SIGN) Values (745603, '1', 21695, 39225, 96, 21864, 30496, 'D83.9', 'Y', '00944270006', '7000115667', '7000115667', 'N', 13, TO_DATE('06/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IV', TO_DATE('06/16/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '3142810', TO_DATE('06/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'VIAL', 2, '20', 1, 0, TO_DATE('06/17/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 13, 'Q4W', 'GAMMAGARD LQ 10% 40GM VIALS (DOSE=40GM) TRANSFER 40 GRAMS (400ML) FROM VIALS INTO EMPTY BAG AND INFUSE I.V. ONCE EVERY 4 WEEKS VIA CADD PUMP.', 'Y', TO_DATE('02/07/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '22323559364', 'PRESCRIBER', 'VIAL', 2, 36, '100', '2', 599807, 'DISPENSE_AXELACARE_20160523.TXT', TO_DATE('06/28/2016 09:09:56', 'MM/DD/YYYY HH24:MI:SS'), 'WF_LH_ODS_SPP_SHIPMENT_SPP.M_LH_ODS_SPP_SHIPMENT_SPP', '20', '+'); COMMIT;