I'm struggling to optimize a report query. It's quite extensive.. When I use start and end dates of 1/1/2016 to today, it is taking hours to complete. My users are telling me this used to complete within 10-15 minutes.
WITH cteq_vars AS (
SELECT
TO_DATE(TO_CHAR(:From_Date, 'mm/dd/yyyy'), 'mm/dd/yyyy') From_Dt,
TO_DATE(TO_CHAR(:to_date, 'mm/dd/yyyy'), 'mm/dd/yyyy') To_Dt,
CASE WHEN NVL(UPPER(TRIM(:account_no)), '* ALL *') = ''
THEN '* ALL *'
ELSE NVL(UPPER(TRIM(:account_no)), '* ALL *')
END account_number,
CASE WHEN :Paid_From = '' OR :Paid_From IS NULL
THEN NULL
ELSE TO_DATE(:Paid_From, 'mm/dd/yyyy')
END Paid_From,
CASE WHEN :Paid_To = '' OR :Paid_To IS NULL
THEN NULL
ELSE TO_DATE(:Paid_To, 'mm/dd/yyyy')
END Paid_To,
CASE WHEN :Rsrv_Date = '' OR :Rsrv_Date IS NULL
THEN TRUNC(SYSDATE)
ELSE TO_DATE(:Rsrv_Date, 'mm/dd/yyyy')
END reserve_date
FROM DUAL
),
cteq_max_pob_no AS (
SELECT
MAX(ipob1.injr_pob_no) injr_pob_no,
ipob1.injr_id injr_id,
UPPER(TRIM(ipob1.pob_cd)) pob_cd,
UPPER(TRIM(ipob1.pob_loc_cd)) pob_loc_cd
FROM
INJURY_PART_OF_BODY ipob1
WHERE
UPPER(TRIM(ipob1.injr_pob_pri_ind)) = 'Y'
GROUP BY
ipob1.injr_id,
ipob1.pob_cd,
ipob1.pob_loc_cd
),
cteq_1 AS (
SELECT
c.injr_id injr_id,
TRIM(UPPER(TRIM(c.clm_no))|| ' ' ||
CASE UPPER(TRIM(cthvc.clm_typ_cd))
WHEN 'WAGEL' THEN 'T'
WHEN 'MED' THEN 'N'
WHEN 'X' THEN 'X'
ELSE NULL
END
) "Claim Number",
CASE WHEN UPPER(TRIM(c.CLM_NDWC_INTL_IND)) = 'Y'
THEN ''
ELSE INITCAP(TRIM(ph.prsn_hist_nm_lst)) || ', ' || INITCAP(TRIM(ph.prsn_hist_nm_fst))
END "IW Name",
INITCAP(TRIM(NVL(ot.occup_typ_nm, 'Unknown'))) "Occupation",
UPPER(TRIM(ph.gndr_cd)) "Claimant Gender",
TRUNC(MONTHS_BETWEEN(TRUNC(i.injr_dtm), TRUNC(ph.prsn_hist_brth_dt)) / 12, 0) "Claimant Age",
TRUNC(i.injr_dtm) "Injury Date",
TO_CHAR(i.injr_dtm, 'HH:MI AM') "Injury Time",
TRIM(REPLACE(REPLACE(REPLACE(i.INJR_ACDN_DESC_TEXT, CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')) "Injury Description",
INITCAP(TRIM(cs.CLM_STS_NM)) "Claim Status",
UPPER(TRIM(ipob.pob_cd)) || ' - ' || INITCAP(TRIM(pob.pob_nm)) "Primary Body Part",
UPPER(TRIM(ipob.POB_LOC_CD)) "Body Side",
UPPER(TRIM(i.noi_cd)) || ' - ' || INITCAP(TRIM(noi.noi_nm)) "Nature of Injury",
UPPER(TRIM(si.INJR_SRC_CD)) || ' - ' || INITCAP(TRIM(si.INJR_SRC_NM)) "Injury Cause",
pb.plcy_no "Account Number",
INITCAP(TRIM(NVL(le.lgl_enty_drv_nm_alt, le.lgl_enty_drv_nm))) "Employer",
TRUNC(c.clm_drv_fst_rpt_dt) "WSI Notify Date",
NVL(c.clm_clmt_empl_no_of_yrs, 0) "Years",
NVL(c.clm_clmt_empl_no_of_mm, 0) "Months",
NVL(c.clm_clmt_empl_no_of_dd, 0) "Days",
UPPER(TRIM(c.empl_cls_cd)) || ' - ' || INITCAP(TRIM(ec.EMPL_CLS_NM)) "Rate Class",
vars.From_Dt vars_From_Dt,
vars.To_Dt vars_To_Dt,
vars.account_number account_number,
vars.Paid_From vars_Paid_From,
vars.Paid_To vars_Paid_To,
vars.reserve_date reserve_date
FROM
cteq_vars vars
INNER JOIN INJURY i
ON TRUNC(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt
INNER JOIN CLAIM c
ON c.injr_id = i.injr_id
AND c.clm_clmt_empl_no_of_yrs IS NOT NULL
INNER JOIN POLICY_BUSINESS pb
ON pb.lgl_enty_id_busn = i.lgl_enty_id_emplr
AND (
LPAD(UPPER(TRIM(pb.plcy_no)), 7, '0') = LPAD(UPPER(TRIM(vars.account_number)), 7, '0')
OR vars.account_number = '* ALL *'
)
AND c.clm_clmt_empl_no_of_yrs < 200
INNER JOIN CLAIM_STATUS_HISTORY_VIEW_CUR cshv
ON cshv.injr_id = i.injr_id
AND cshv.clm_sts_cd != 'x'
INNER JOIN CLAIM_TYPE_HISTORY_VIEW_CUR cthvc
ON cthvc.injr_id = i.injr_id
INNER JOIN PERSON_HISTORY ph
ON ph.lgl_enty_id_prsn = i.lgl_enty_id_clmt
AND ph.prsn_hist_end_dt IS NULL
INNER JOIN CLAIM_STATUS cs
ON UPPER(TRIM(cs.CLM_STS_CD)) = UPPER(TRIM(cshv.clm_sts_cd))
LEFT OUTER JOIN EMPLOYMENT_CLASS ec
ON UPPER(TRIM(ec.EMPL_CLS_CD)) = UPPER(TRIM(c.empl_cls_cd))
INNER JOIN LEGAL_ENTITY le
ON le.LGL_ENTY_ID = i.lgl_enty_id_emplr
LEFT OUTER JOIN NATURE_OF_INJURY noi
ON UPPER(TRIM(noi.NOI_CD)) = UPPER(TRIM(i.NOI_CD))
LEFT OUTER JOIN OCCUPATION_TYPE ot
ON UPPER(TRIM(ot.occup_typ_cd)) = UPPER(TRIM(i.occup_typ_cd))
LEFT OUTER JOIN cteq_max_pob_no ipob
ON ipob.injr_id = i.injr_id
LEFT OUTER JOIN PART_OF_BODY pob
ON UPPER(TRIM(pob.pob_cd)) = UPPER(TRIM(ipob.pob_cd))
LEFT OUTER JOIN INJURY_SOURCE si
ON UPPER(TRIM(si.INJR_SRC_CD)) = UPPER(TRIM(i.INJR_SRC_CD))
UNION ALL
SELECT
c.injr_id injr_id,
TRIM(UPPER(TRIM(c.clm_no)) || ' ' ||
CASE UPPER(TRIM(cthvc.clm_typ_cd))
WHEN 'WAGEL' THEN 'T'
WHEN 'MED' THEN 'N'
WHEN 'X' THEN 'X'
ELSE NULL
END
) "Claim Number",
CASE WHEN UPPER(TRIM(c.CLM_NDWC_INTL_IND)) = 'Y'
THEN ''
ELSE INITCAP(TRIM(ph.prsn_hist_nm_lst)) || ', ' || INITCAP(TRIM(ph.prsn_hist_nm_fst))
END "IW Name",
INITCAP(TRIM(NVL(ot.occup_typ_nm, 'Unknown'))) "Occupation",
UPPER(TRIM(ph.gndr_cd)) "Claimant Gender",
TRUNC(MONTHS_BETWEEN (TRUNC(i.injr_dtm), TRUNC(ph.prsn_hist_brth_dt)) / 12, 0) "Claimant Age",
TRUNC(i.injr_dtm) "Injury Date",
TO_CHAR(i.injr_dtm, 'HH:MI AM') "Injury Time",
TRIM(REPLACE(REPLACE(REPLACE(REPLACE(i.injr_acdn_desc_text, CHR (10), ' '), CHR (13), ' '), CHR (9), ' '), '"', '''''')) "Injury Description",
INITCAP(TRIM(cs.CLM_STS_NM)) "Claim Status",
UPPER(TRIM(ipob.pob_cd)) || ' - ' || INITCAP(TRIM(pob.pob_nm)) "Primary Body Part",
UPPER(TRIM(ipob.POB_LOC_CD)) "Body Side",
UPPER(TRIM(i.noi_cd)) || ' - ' || INITCAP(TRIM(noi.noi_nm)) "Nature of Injury",
UPPER(TRIM(si.INJR_SRC_CD)) || ' - ' || INITCAP(TRIM(si.INJR_SRC_NM)) "Injury Cause",
pb.plcy_no "Account Number",
INITCAP(TRIM(NVL(le.lgl_enty_drv_nm_alt, le.lgl_enty_drv_nm))) "Employer",
TRUNC(c.clm_drv_fst_rpt_dt) "WSI Notify Date",
TRUNC(MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY')) / 12) "Years",
(
TRUNC(MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY'))
- TRUNC((MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY')) / 12)) * 12)
) "Months",
(
TRUNC(i.injr_dtm)
- TRUNC(ADD_MONTHS(TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY'), MONTHS_BETWEEN(i.injr_dtm, TO_DATE(GREATEST(NVL(c.clm_clmt_empl_no_of_dd, 0), 1) || '/' || GREATEST(NVL(c.clm_clmt_empl_no_of_mm, 0), 1) || '/' || NVL(c.clm_clmt_empl_no_of_yrs, 0), 'DD/MM/YYYY'))))
) "Days",
UPPER(TRIM(c.empl_cls_cd)) || ' - ' || INITCAP(TRIM(ec.EMPL_CLS_NM)) "Rate Class",
vars.From_Dt vars_From_Dt,
vars.To_Dt vars_To_Dt,
vars.account_number account_number,
vars.Paid_From vars_Paid_From,
vars.Paid_To vars_Paid_To,
vars.reserve_date reserve_date
FROM
cteq_vars vars
INNER JOIN INJURY i
ON Trunc(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt
INNER JOIN CLAIM c
ON c.injr_id = i.injr_id
AND c.clm_clmt_empl_no_of_yrs IS NOT NULL
AND c.clm_clmt_empl_no_of_yrs >= 200
INNER JOIN POLICY_BUSINESS pb
ON pb.lgl_enty_id_busn = i.lgl_enty_id_emplr
AND (
LPAD(UPPER(TRIM(pb.plcy_no)), 7, '0') = LPAD(UPPER(TRIM(vars.account_number)), 7, '0')
OR vars.account_number = '* ALL *'
)
INNER JOIN CLAIM_TYPE_HISTORY_VIEW_CUR cthvc
ON cthvc.injr_id = i.injr_id
INNER JOIN CLAIM_STATUS_HISTORY_VIEW_CUR cshv
ON cshv.injr_id = i.injr_id
AND UPPER(TRIM(cshv.clm_sts_cd)) NOT IN ('X')
LEFT OUTER JOIN NATURE_OF_INJURY noi
ON noi.noi_cd = i.noi_cd
INNER JOIN CLAIM_STATUS cs
ON UPPER(TRIM(cs.clm_sts_cd)) = UPPER(TRIM(cshv.clm_sts_cd))
INNER JOIN PERSON_HISTORY ph
ON ph.lgl_enty_id_prsn = i.lgl_enty_id_clmt
AND ph.prsn_hist_end_dt IS NULL
INNER JOIN LEGAL_ENTITY le
ON i.lgl_enty_id_emplr = le.lgl_enty_id
LEFT OUTER JOIN EMPLOYMENT_CLASS ec
ON UPPER(TRIM(ec.empl_cls_cd)) = UPPER(TRIM(c.empl_cls_cd))
LEFT OUTER JOIN INJURY_SOURCE si
ON UPPER(TRIM(si.injr_src_cd)) = UPPER(TRIM(i.injr_src_cd))
LEFT OUTER JOIN OCCUPATION_TYPE ot
ON UPPER(TRIM(ot.occup_typ_cd)) = UPPER(TRIM(i.occup_typ_cd))
LEFT OUTER JOIN cteq_max_pob_no ipob
ON ipob.injr_id = i.injr_id
LEFT OUTER JOIN PART_OF_BODY pob
ON UPPER(TRIM(pob.pob_cd)) = UPPER(TRIM(ipob.pob_cd))
UNION ALL
SELECT
c.injr_id injr_id,
TRIM(UPPER(TRIM(c.clm_no)) || ' ' ||
CASE UPPER(TRIM(cthvc.clm_typ_cd))
WHEN 'WAGEL' THEN 'T'
WHEN 'MED' THEN 'N'
WHEN 'X' THEN 'X'
ELSE NULL
END
) "Claim Number",
CASE WHEN UPPER(TRIM(c.CLM_NDWC_INTL_IND)) = 'Y'
THEN ''
ELSE INITCAP(TRIM(ph.prsn_hist_nm_lst)) || ', ' || INITCAP(TRIM(ph.prsn_hist_nm_fst))
END "IW Name",
INITCAP(TRIM(NVL(ot.occup_typ_nm, 'Unknown'))) "Occupation",
UPPER(TRIM(ph.gndr_cd)) "Claimant Gender",
TRUNC(MONTHS_BETWEEN (TRUNC(i.injr_dtm), TRUNC(ph.prsn_hist_brth_dt)) / 12, 0) "Claimant Age",
TRUNC(i.injr_dtm) "Injury Date",
TO_CHAR(i.injr_dtm, 'HH:MI AM') "Injury Time",
TRIM(REPLACE(REPLACE(REPLACE(REPLACE(i.injr_acdn_desc_text, CHR (10), ' '), CHR (13), ' '), CHR (9), ' '), '"', '''''')) "Injury Description",
INITCAP(TRIM(cs.CLM_STS_NM)) "Claim Status",
UPPER(TRIM(ipob.pob_cd)) || ' - ' || INITCAP(TRIM(pob.pob_nm)) "Primary Body Part",
UPPER(TRIM(ipob.POB_LOC_CD)) "Body Side",
UPPER(TRIM(i.noi_cd)) || ' - ' || INITCAP(TRIM(noi.noi_nm)) "Nature of Injury",
UPPER(TRIM(si.INJR_SRC_CD)) || ' - ' || INITCAP(TRIM(si.INJR_SRC_NM)) "Injury Cause",
pb.plcy_no "Account Number",
INITCAP(TRIM(NVL(le.lgl_enty_drv_nm_alt, le.lgl_enty_drv_nm))) "Employer",
TRUNC(c.clm_drv_fst_rpt_dt) "WSI Notify Date",
NVL(c.clm_clmt_empl_no_of_yrs, 0) "Years",
NVL(c.clm_clmt_empl_no_of_mm, 0) "Months",
NVL(c.clm_clmt_empl_no_of_dd, 0) "Days",
UPPER(TRIM(c.empl_cls_cd)) || ' - ' || INITCAP(TRIM(ec.EMPL_CLS_NM)) "Rate Class",
vars.From_Dt vars_From_Dt,
vars.To_Dt vars_To_Dt,
vars.account_number account_number,
vars.Paid_From vars_Paid_From,
vars.Paid_To vars_Paid_To,
vars.reserve_date reserve_date
FROM
cteq_vars vars
INNER JOIN INJURY i
ON Trunc(i.injr_dtm) BETWEEN vars.From_Dt AND vars.To_Dt
INNER JOIN CLAIM c
ON c.injr_id = i.injr_id
AND c.clm_clmt_empl_no_of_yrs IS NULL
INNER JOIN POLICY_BUSINESS pb
ON pb.lgl_enty_id_busn = i.lgl_enty_id_emplr
AND (
LPAD(UPPER(TRIM(pb.plcy_no)), 7, '0') = LPAD(UPPER(TRIM(vars.account_number)), 7, '0')
OR vars.account_number = '* ALL *'
)
INNER JOIN CLAIM_TYPE_HISTORY_VIEW_CUR cthvc
ON cthvc.injr_id = i.injr_id
INNER JOIN CLAIM_STATUS_HISTORY_VIEW_CUR cshv
ON cshv.injr_id = i.injr_id
AND UPPER(TRIM(cshv.clm_sts_cd)) NOT IN ('X')
LEFT OUTER JOIN NATURE_OF_INJURY noi
ON UPPER(TRIM(noi.noi_cd)) = UPPER(TRIM(i.noi_cd))
INNER JOIN CLAIM_STATUS cs
ON UPPER(TRIM(cs.clm_sts_cd)) = UPPER(TRIM(cshv.clm_sts_cd))
INNER JOIN PERSON_HISTORY ph
ON ph.lgl_enty_id_prsn = i.lgl_enty_id_clmt
AND ph.prsn_hist_end_dt IS NULL
INNER JOIN LEGAL_ENTITY le
ON i.lgl_enty_id_emplr = le.lgl_enty_id
LEFT OUTER JOIN EMPLOYMENT_CLASS ec
ON UPPER(TRIM(ec.empl_cls_cd)) = UPPER(TRIM(c.empl_cls_cd))
LEFT OUTER JOIN INJURY_SOURCE si
ON UPPER(TRIM(si.injr_src_cd)) = UPPER(TRIM(i.injr_src_cd))
LEFT OUTER JOIN OCCUPATION_TYPE ot
ON UPPER(TRIM(ot.occup_typ_cd)) = UPPER(TRIM(i.occup_typ_cd))
LEFT OUTER JOIN cteq_max_pob_no ipob
ON ipob.injr_id = i.injr_id
LEFT OUTER JOIN PART_OF_BODY pob
ON UPPER(TRIM(pob.pob_cd)) = UPPER(TRIM(ipob.pob_cd))
ORDER BY 1
),
--Query completes in 34 secs up to this point, same amount of rows. 19623
cteq_2_1 AS (
SELECT
cft.injr_id,
UPPER(TRIM(asda.bnft_typ_cd)) bnft,
((Trunc(asd.admis_sch_dtl_drv_prd_end_dt) - Trunc(asd.admis_sch_dtl_drv_prd_eff_dt)) + 1 ) ttdays,
0 tpdays
FROM
cteq_1 cte1
INNER JOIN CLAIM_FINANCIAL_TRAN cft
ON cft.injr_id = cte1.injr_id
AND UPPER(TRIM(cft.cft_typ_cd)) = 'INDM_PAY_DUE'
AND UPPER(TRIM(cft.bnft_typ_cd)) IN ('TT', 'ABP', 'PT', 'RHBT', 'RHBP')
AND (
(cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)
AND
(cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)
)
INNER JOIN ADMISSION_SCHEDULE_DETAIL_AMT asda
ON cft.cft_id = asda.cft_id
INNER JOIN ADMISSION_SCHEDULE_DETAIL asd
ON asd.admis_sch_id = asda.admis_sch_id
AND asd.admis_sch_dtl_no = asda.admis_sch_dtl_no
INNER JOIN ADMISSION_PERIOD ap
ON asd.admis_sch_id = ap.admis_sch_id
AND UPPER(TRIM(ap.admis_prd_void_ind)) = 'N'
INNER JOIN ADMISSION_PERIOD_RATE_HISTORY aprh
ON aprh.injr_id = ap.injr_id
AND aprh.admis_no = ap.admis_no
AND aprh.admis_prd_no = ap.admis_prd_no
AND UPPER(TRIM(aprh.admis_prd_rt_hist_void_ind)) = 'N'
AND aprh.admis_prd_rt_hist_no = (SELECT Max(aprh2.admis_prd_rt_hist_no)
FROM ADMISSION_PERIOD_RATE_HISTORY aprh2
WHERE aprh2.injr_id = aprh.injr_id
AND aprh2.admis_no = aprh.admis_no
AND aprh2.admis_prd_no = aprh.admis_prd_no)
),
--1m 17sec
cteq_2_2 AS (
SELECT
cft.injr_id injr_id,
UPPER(TRIM(asda.bnft_typ_cd)) bnft,
0 ttdays,
((Trunc(asd.admis_sch_dtl_drv_prd_end_dt) - Trunc(asd.admis_sch_dtl_drv_prd_eff_dt)) + 1) tpdays
FROM
cteq_1 cte1
INNER JOIN CLAIM_FINANCIAL_TRAN cft
ON cft.injr_id = cte1.injr_id
AND UPPER(TRIM(cft.cft_typ_cd)) = 'INDM_PAY_DUE'
AND UPPER(TRIM(cft.bnft_typ_cd)) IN ('PP', 'PDB', 'PRB', 'TP')
AND (
(cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)
AND
(cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)
)
INNER JOIN ADMISSION_PERIOD ap
ON ap.injr_id = cte1.injr_id
AND UPPER(TRIM(ap.admis_prd_void_ind)) = 'N'
INNER JOIN ADMISSION_PERIOD_RATE_HISTORY aprh
ON aprh.injr_id = ap.injr_id
AND aprh.admis_no = ap.admis_no
AND aprh.admis_prd_no = ap.admis_prd_no
AND UPPER(TRIM(aprh.admis_prd_rt_hist_void_ind)) = 'N'
AND aprh.admis_prd_rt_hist_no = (SELECT Max(aprh2.admis_prd_rt_hist_no)
FROM ADMISSION_PERIOD_RATE_HISTORY aprh2
WHERE aprh2.injr_id = aprh.injr_id
AND aprh2.admis_no = aprh.admis_no
AND aprh2.admis_prd_no = aprh.admis_prd_no)
INNER JOIN ADMISSION_SCHEDULE_DETAIL asd
ON asd.admis_sch_id = ap.admis_sch_id
INNER JOIN ADMISSION_SCHEDULE_DETAIL_AMT asda
ON asda.cft_id = cft.cft_id
AND asda.admis_sch_id = asd.admis_sch_id
AND asda.admis_sch_dtl_no = asd.admis_sch_dtl_no
),
cteq_2_union AS (
SELECT injr_id injr_id,
bnft bnft,
ttdays ttdays,
tpdays tpdays
FROM cteq_2_1
UNION ALL
SELECT injr_id injr_id,
bnft bnft,
ttdays ttdays,
tpdays tpdays
FROM cteq_2_2
),
cteq_2 AS (
SELECT
injr_id injr_id,
SUM(ttdays) ttdays,
SUM(tpdays) tpdays
FROM cteq_2_union
GROUP BY injr_id
ORDER BY injr_id
),
cteq_3_1 AS (
SELECT cft.injr_id injr_id,
TO_NUMBER(NVL(TO_CHAR(cft.cft_amt), 0)) cmppay,
0 medpay,
cft.cft_typ_cd,
cft.cft_dt,
vars.From_Dt,
vars.reserve_date,
cft.cft_id
FROM
cteq_1 cte1
CROSS JOIN cteq_vars vars
INNER JOIN CLAIM_FINANCIAL_TRAN cft
ON cft.injr_id = cte1.injr_id
AND UPPER(TRIM(cft.cft_typ_cd)) = 'INDM_PAY_DUE'
AND Trunc(cft.cft_dt) >= vars.From_Dt
AND (
(Trunc(cft.cft_dt) <= TRUNC(SYSDATE) AND vars.reserve_date IS NULL)
OR
(Trunc(cft.cft_dt) <= TRUNC(vars.reserve_date) AND vars.reserve_date IS NOT NULL)
)
AND (
(cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)
AND
(cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)
)
),
cteq_3_2 AS (
SELECT
cft.injr_id injr_id,
0 cmppay,
TO_NUMBER(Nvl(TO_CHAR(cft.cft_amt), 0)) medpay
FROM
cteq_1 cte1
CROSS JOIN cteq_vars vars
INNER JOIN CLAIM_FINANCIAL_TRAN cft
ON cft.injr_id = cte1.injr_id
AND UPPER(TRIM(cft.cft_typ_cd)) = 'MED_PAY_DUE'
AND Trunc(cft.cft_dt) >= vars.From_Dt
AND (
(Trunc(cft.cft_dt) <= TRUNC(SYSDATE) AND vars.reserve_date IS NULL)
OR
(Trunc(cft.cft_dt) <= TRUNC(vars.reserve_date) AND vars.reserve_date IS NOT NULL)
)
AND (
(cte1.vars_Paid_From IS NULL OR TRUNC(cft.cft_dt) >= cte1.vars_Paid_From)
AND
(cte1.vars_Paid_To IS NULL OR TRUNC(cft.cft_dt) <= cte1.vars_Paid_To)
)
),
cteq_3_union
AS
(
SELECT
injr_id injr_id,
cmppay cmppay,
medpay medpay
FROM cteq_3_1
UNION ALL
SELECT
injr_id injr_id,
cmppay cmppay,
medpay medpay
FROM cteq_3_2
),
cteq_3 AS (
SELECT
injr_id injr_id,
SUM(cmppay) cmppay,
SUM(medpay) medpay
FROM cteq_3_union
GROUP BY injr_id
),
cteq_rsrv_1 AS (
SELECT
cte1."Claim Number" "Claim Number",
a.injr_id injr_id,
SUM(a.rem_no_time_loss) NTLMed,
SUM(a.rem_time_loss) TLMed,
SUM(a.rem_comp_rsrv) TLRV,
SUM(a.rem_sup_rsrv) SUPRV
FROM
cteq_1 cte1
INNER JOIN REMAINING_RESERVES_HISTORY_TMP a
ON a.injr_id = cte1.injr_id
AND a.rem_rsrv_hist_date = (SELECT MAX(b.rem_rsrv_hist_date) max_hist_date
FROM REMAINING_RESERVES_HISTORY_TMP b
WHERE b.injr_id = a.injr_id
AND (
(TRUNC(b.rem_rsrv_hist_date) <= TRUNC(SYSDATE) AND cte1.reserve_date IS NULL)
OR
(TRUNC(b.rem_rsrv_hist_date) <= cte1.reserve_date AND cte1.reserve_date IS NOT NULL)
)
)
GROUP BY
cte1."Claim Number",
a.injr_id
ORDER BY cte1."Claim Number"
),
cteq_rsrv AS (
SELECT injr_id,
GREATEST(0, NVL(cte1.TLRV, 0)) + GREATEST(0, NVL(cte1.SupRV, 0)) CMP_RV,
GREATEST(0, NVL(cte1.NTLMed, 0)) + GREATEST(0, NVL(cte1.TLMed, 0)) MED_RV
FROM cteq_rsrv_1 cte1
),
cteq_paid AS (
SELECT
cte1.injr_id injr_id,
cte3.cmppay cmppay,
cter.CMP_RV CMP_RV,
cte3.medpay medpay,
cter.MED_RV MED_RV
FROM
cteq_1 cte1
LEFT OUTER JOIN cteq_3 cte3
ON cte3.injr_id = cte1.injr_id
LEFT OUTER JOIN cteq_rsrv cter
ON cter.injr_id = cte1.injr_id
),
cteq_outputfull1 AS (
SELECT
cte1.injr_id injr_id,
cte1."Claim Number" "Claim Number",
cte1."IW Name" "IW Name",
cte1."Occupation" "Occupation",
cte1."Claimant Gender" "Claimant Gender",
cte1."Claimant Age" "Claimant Age",
cte1."Injury Date" "Injury Date",
cte1."Injury Time" "Injury Time",
cte1."Injury Description" "Injury Description",
cte1."Claim Status" "Claim Status",
cte1."Primary Body Part" "Primary Body Part",
cte1."Body Side" "Body Side",
cte1."Nature of Injury" "Nature of Injury",
cte1."Injury Cause" "Injury Cause",
cte1."Account Number" "Account Number",
cte1."Employer" "Employer",
cte1."WSI Notify Date" "WSI Notify Date",
cte1."Years" "Years",
cte1."Months" "Months",
cte1."Days" "Days",
cte1."Rate Class" "Rate Class",
NVL(cte2.ttdays, 0) ttdays,
NVL(cte2.tpdays, 0) tpdays,
NVL(ctep.cmppay, 0) cmppay,
NVL(ctep.CMP_RV, 0) CMP_RV,
NVL(ctep.medpay, 0) medpay,
NVL(ctep.MED_RV, 0) MED_RV,
cte1.vars_From_Dt vars_From_Dt,
cte1.vars_To_Dt vars_To_Dt,
cte1.vars_Paid_From vars_Paid_From,
cte1.vars_Paid_To vars_Paid_To
FROM
cteq_1 cte1
LEFT OUTER JOIN cteq_2 cte2
ON cte1.injr_id = cte2.injr_id
LEFT OUTER JOIN cteq_paid ctep
ON ctep.injr_id = cte1.injr_id
)
SELECT
"Claim Number" "Claim Number",
"IW Name" "Claimant Name",
"Claimant Age" "Claimant Age",
"Claimant Gender" "Claimant Gender",
"Occupation" "Occupation",
"Years" "Employment Years",
"Months" "Employment Months",
"Days" "Employment Days",
"WSI Notify Date" "WSI Notify Date",
"Injury Date" "Injury Date",
"Injury Time" "Injury Time",
"Injury Description" "Injury Description",
"Claim Status" "Claim Status",
"Account Number" "Account Number",
"Employer" "Employer",
"Rate Class" "Rate Class",
"Primary Body Part" "Primary Body Part",
"Body Side" "Body Side",
"Injury Cause" "Injury Cause",
"Nature of Injury" "Nature of Injury",
cmppay "Comp Paid",
medpay "Med Paid",
CMP_RV "Comp Reserve",
MED_RV "Med Reserve",
cmppay + medpay + CMP_RV + MED_RV "Total Incurred",
ttdays + tpdays "Total TL Days"
FROM cteq_outputfull1
ORDER BY "Claim Number"
/**/