select 
      tab2.year year,
      tab2.pol pol,
      tab2.file_number file_number,
      sum(tab2.claims) CLAIMS,
      sum(tab2.medical_claims_counter) medical_claims_counter,
      SUM(MEDICAL) MEDICAL,
      SUM(medical_p-r_paid) MEDICAL_P,
      SUM(rehabilitation) rehabilitation,
      SUM(r_paid) R_PAID,
      SUM(compensation+non_rpt) compensation,
      SUM(c_amount_paid) c_amount_paid 
from
(
select  tab.year year,
        tab.poltyp pol,
        tab.file_number file_number,
       COUNT(tab.claims) CLAIMS,
        COUNT(tab.medical_claims_counter)medical_claims_counter,
       tab.injr_id injr_id,
      --  sum(tab.count_clm) count_clm,
        sum(NVL(pkg_swif_clm.f_clm_med_rsrv_get(tab.injr_id,:busn_dt),0))medical,
        sum( NVL(pkg_swif_clm.f_clm_med_paid_get(tab.injr_id,:busn_dt),0))medical_p,		
        sum(NVL(pkg_swif_clm.f_clm_voc_rsrv_get(tab.injr_id,:busn_dt),0))rehabilitation,
        sum( NVL(pkg_swif_clm.f_clm_voc_paid_get(tab.injr_id,:busn_dt),0)) r_paid,
        sum(NVL(pkg_swif_clm.f_clm_comp_rsrv_get(tab.injr_id,:busn_dt),0))compensation,
		sum(nvl(pkg_swif_clm.f_clm_nonrpt_comp_rsrv_get(tab.injr_id,:busn_dt),0))non_rpt,
        sum(NVL(pkg_swif_clm.f_clm_comp_paid_get(tab.injr_id,:busn_dt),0))c_amount_paid	   
from
(
select  to_char(trunc(i.injr_dtm),'yyyy') year,
       --  1 count_clm,
        c.injr_id injr_id,
        c.clm_no file_number,
 CASE                 
   	 WHEN p.mrkt_typ_cd = 'coal' AND pa.attr_sub_typ_cd = 'n' THEN '     COAL' 
   	 WHEN p.mrkt_typ_cd = 'voln' AND pa.attr_sub_typ_cd = 'n' THEN '    COMMERCIAL'
   	-- WHEN p.mrkt_typ_cd = 'voln' AND pa.attr_sub_typ_cd = 'y' THEN '   COMMONWEALTH'
   --	  WHEN p.mrkt_typ_cd = 'self' AND pa.attr_sub_typ_cd = 'y' THEN '  COM.SELF-INS'
   --	  WHEN p.mrkt_typ_cd = 'self' AND pa.attr_sub_typ_cd = 'n' THEN ' SELF-INSURED'
    -- when c.cls_typ_cd = 'f' then 'USLandH'
       END  poltyp,
		case when cth.clm_typ_cd = 'wagel' then 									  		   
                           c.clm_no
                    end claims,
                  case when cth.clm_typ_cd = 'med' then 									  		   
                          c.clm_no	  
                  end medical_claims_counter            
  from injury i,
       claim c,
	   policy p,
       policy_period pp,
	   claim_type_history cth,
	   policy_attribute	  pa 
 where trunc(i.injr_dtm) <= :busn_dt   
   and c.injr_id = i.injr_id
   and p.plcy_no = c.plcy_no
   and p.mrkt_typ_cd in ('coal','voln')
   and p.plcy_id = pp.plcy_id
   and cth.injr_id = c.injr_id           
   and cth.clm_typ_hist_eff_dtm <= :busn_dt            
   and cth.clm_typ_hist_eff_dtm = (select max(cth1.clm_typ_hist_eff_dtm)             			                     
                                       		 from claim_type_history cth1          				                    
				                              where cth1.injr_id = cth.injr_id       									      
				                               and cth1.clm_typ_hist_eff_dtm <=:busn_dt
		                                       and nvl(cth1.clm_typ_hist_end_dt,:busn_dt) >= :busn_dt)	
  --AND  pa.plcy_id = p.plcy_id
--  AND  pa.attr_typ_cd= 'cwlth'
   and pp.plcy_prd_eff_dt <= trunc(i.injr_dtm)
   and pp.plcy_prd_end_dt > trunc(i.injr_dtm)
   --and trunc(i.injr_dtm) between pp.plcy_prd_eff_dt and pp.plcy_prd_end_dt    
   and (exists  (select csh.injr_id injr_id              
                       from claim_status cs,
                     	    claim_status_history csh	   
                      where csh.injr_id = i.injr_id
					    and cs.clm_sts_cd = csh.clm_sts_cd
                        and cs.clm_sts_cd_par = 'opn' -- open claim .
                        and trunc(csh.clm_sts_hist_eff_dt) <= :busn_dt
                        and csh.clm_sts_hist_eff_dt = (select max(csh1.clm_sts_hist_eff_dt) 
                                                         from claim_status_history csh1
                     								    where csh.injr_id = csh1.injr_id
                     								      and trunc(csh1.clm_sts_hist_eff_dt) <=:busn_dt 
									     and nvl(trunc(csh1.clm_sts_hist_end_dt),:busn_dt) >= :busn_dt))))tab

where tab.year='1997'
GROUP BY
tab.year,
tab.poltyp,
tab.file_number,
tab.injr_id

union all


select  tab1.year year,
        tab1.poltyp pol,
        tab1.file_number file_number,
       COUNT(tab1.claims) CLAIMS,
        COUNT(tab1.medical_claims_counter)medical_claims_counter,
     tab1.injr_id injr_id,
      --  sum(tab.count_clm) count_clm,
        sum(NVL(pkg_swif_clm.f_clm_med_rsrv_get(tab.injr_id,:busn_dt),0))medical,
        sum( NVL(pkg_swif_clm.f_clm_med_paid_get(tab.injr_id,:busn_dt),0))medical_p,		
        sum(NVL(pkg_swif_clm.f_clm_voc_rsrv_get(tab.injr_id,:busn_dt),0))rehabilitation,
        sum( NVL(pkg_swif_clm.f_clm_voc_paid_get(tab.injr_id,:busn_dt),0)) r_paid,
        sum(NVL(pkg_swif_clm.f_clm_comp_rsrv_get(tab.injr_id,:busn_dt),0)compensation,
		sum(nvl(pkg_swif_clm.f_clm_nonrpt_comp_rsrv_get(tab.injr_id,:busn_dt),0))non_rpt,
        sum(NVL(pkg_swif_clm.f_clm_comp_paid_get(tab.injr_id,:busn_dt),0))c_amount_paid	 
from
(
select   to_char(trunc(i.injr_dtm),'yyyy') year,
       --  1 count_clm,
        c.injr_id injr_id,
      c.clm_no file_number,
 CASE                 
   	 WHEN p.mrkt_typ_cd = 'coal' AND pa.attr_sub_typ_cd = 'n' THEN '     COAL' 
   	 WHEN p.mrkt_typ_cd = 'voln' AND pa.attr_sub_typ_cd = 'n' THEN '    COMMERCIAL'
   --	 WHEN p.mrkt_typ_cd = 'voln' AND pa.attr_sub_typ_cd = 'y' THEN '   COMMONWEALTH'
   	 WHEN p.mrkt_typ_cd = 'self' AND pa.attr_sub_typ_cd = 'y' THEN '  COM.SELF-INS'
   	 WHEN p.mrkt_typ_cd = 'self' AND pa.attr_sub_typ_cd = 'n' THEN ' SELF-INSURED'
    -- when c.cls_typ_cd = 'f' then 'USLandH'
       END  poltyp,
		case when cth.clm_typ_cd = 'wagel' then 									  		   
                           c.clm_no
                    end claims,
                  case when cth.clm_typ_cd = 'med' then 									  		   
                          c.clm_no	  
                  end medical_claims_counter            
	   
	   
  from injury i,
       claim c,
	   policy p,
       policy_period pp,
	   claim_type_history cth,
	   policy_attribute	  pa 
 where trunc(i.injr_dtm) <=:busn_dt   
   and c.injr_id = i.injr_id
   and p.plcy_no = c.plcy_no
   and to_char(trunc(i.injr_dtm),'yyyy')='2005'
   and p.mrkt_typ_cd in ('coal','voln','self')
   and pa.plcy_id = p.plcy_id
 --  and pa.attr_typ_cd= 'cwlth'
   and p.plcy_id = pp.plcy_id
   and pp.plcy_prd_eff_dt <= trunc(i.injr_dtm)
   and pp.plcy_prd_end_dt > trunc(i.injr_dtm)
   --and trunc(i.injr_dtm) between pp.plcy_prd_eff_dt and pp.plcy_prd_end_dt    
   and (exists  (select csh.injr_id injr_id              
                       from claim_status cs,
                     	    claim_status_history csh	   
                      where csh.injr_id = i.injr_id
	      and cs.clm_sts_cd = csh.clm_sts_cd
                        and cs.clm_sts_cd_par = 'cls' -- open claim .
                        and trunc(csh.clm_sts_hist_eff_dt) <= :busn_dt
                        and csh.clm_sts_hist_eff_dt = (select max(csh1.clm_sts_hist_eff_dt) 
                                                         from claim_status_history csh1
                     		          where csh.injr_id = csh1.injr_id
                     		          and trunc(csh1.clm_sts_hist_eff_dt) <=:busn_dt 														  		         and nvl(trunc(csh1.clm_sts_hist_end_dt),:busn_dt) >=:busn_dt))))tab1

where 
-- tab1.accident_year='2005'
-- and
       ((medical-medical_p)+(rehabilation-r_paid)) > 0 or
	     ((compensation-c_amount_paid)+non_rpt) > 0)
GROUP BY
tab1.year,
tab1.poltyp,
tab1.file_number,
tab1.injr_id)tab2
group by
tab2.year,
tab2.pol,
tab2.file_number