HI ,
I am using follwing version
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production.
I have attached code which is very long along with the ddl and insert statement.I am not able to get the explain plan for ythe same as its in prod and i am using dblink .
The task i am assigned is i need to make the code shorter .Please suggest me on this .
We have 2 databases and db link is used .
The same table name is present in both the db .
The code is written in such a way that its difficult to maintain . In need to rewrite the code .Please suggest on this .
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_N