DECLARE

  pl_id PARAMLIST;
  pl_name VARCHAR2(30) := 'tempdata';

  CURSOR get_stat is
  SELECT Distinct clm_no,clm_stat_cd1,awd_int_pay_st,awd_clm_prno
  FROM claim,benefit_payt,awd_benefit,award_ben_rates
  WHERE clm_no = bp_clm_no
  AND clm_proc_no = bp_proc_no
  AND clm_no = awd_clno
  AND clm_proc_no = awd_clm_prno
  AND awd_clno = bp_clm_no
  AND awd_clm_prno = bp_proc_no
  AND abr_clno = bp_clm_no
  AND abr_clm_prno = bp_proc_no
  AND awd_clno = abr_clno
  AND awd_clm_prno = abr_clm_prno
  AND bp_off between :p_off_1 and :p_off_2
  AND bp_clm_type = 'RB'
  AND bp_pay_reason_cd = 1
  AND bp_confirm between :vchr_cfm_date_fr and :vchr_cfm_date_to;

BEGIN

  FOR arec in get_stat LOOP
    pl_id := Create_Parameter_List(pl_name);
    ADD_PARAMETER(pl_id,'clm_num',TEXT_PARAMETER, TO_CHAR(arec.clm_no));
    ADD_PARAMETER(pl_id,'p_off_1',TEXT_PARAMETER,To_CHAR(:p_off_1));
    ADD_PARAMETER(pl_id,'p_off_2',TEXT_PARAMETER,To_CHAR(:p_off_2));
    ADD_PARAMETER(pl_id,'vchr_cfm_date_fr',TEXT_PARAMETER,:vchr_cfm_date_fr);
    ADD_PARAMETER(pl_id,'vchr_cfm_date_to',TEXT_PARAMETER,:vchr_cfm_date_to);
    ADD_PARAMETER(pl_id,'DESTYPE',TEXT_PARAMETER,:sdestination);
    ADD_PARAMETER(pl_id,'PARAMFORM',TEXT_PARAMETER,'NO');

      IF arec.clm_stat_cd1 = 95 AND
      	 to_char(arec.awd_int_pay_st,'YYYY-MON-DD')< '20040301' THEN
      	RUN_PRODUCT(REPORTS,'ret_allow_b.rep',SYNCHRONOUS,RUNTIME,FILESYSTEM,pl_id);
      End if;  
      
      IF arec.clm_stat_cd1 = 95 AND
      	 to_char(arec.awd_int_pay_st,'YYYY-MON-DD')>= '20040301' THEN  
        RUN_PRODUCT(REPORTS,'ret_allow.rep',SYNCHRONOUS,RUNTIME,FILESYSTEM,pl_id); 
      End if; 
      
      IF arec.clm_stat_cd1 = 93 THEN
        RUN_PRODUCT(REPORTS,'ret_allow_gr.rep',SYNCHRONOUS,RUNTIME,FILESYSTEM,pl_id);
      END IF;        
       
    DESTROY_PARAMETER_LIST(pl_id); 
  END LOOP;
 
 GO_BLOCK('MAIN_BLK');
 CLEAR_BLOCK;
 
END;