FUNCTION GET_PLATE_NO( ws_line_cd    IN NUMBER,
                       ws_subline_cd IN NUMBER,
                       ws_iss_cd     IN NUMBER,
                       ws_clm_yy     IN NUMBER,
                       ws_clm_seq_no IN NUMBER ) RETURN VARCHAR2 IS
  v_pol_seq_no     MOTCAR.POL_SEQ_NO%type;
  v_plate_no       MOTCAR.PLATE_NO%type;
  v_item_no        MOTCAR.ITEM_NO%type;
BEGIN
  begin
     select distinct pol_seq_no
       into v_pol_seq_no
       from claims a
       where 1=1
         and a.line_cd = ws_line_cd
         and a.subline_cd = ws_subline_cd
         and a.iss_cd = ws_iss_cd
         and a.clm_yy = ws_clm_yy
         and a.clm_seq_no = ws_clm_seq_no;
  exception
      when no_data_found then
          null;
      when too_many_rows then
          v_pol_seq_no := null;
  end;   

  begin
     select distinct item_no
       into v_item_no
       from clm_loss a, clm_hist b
       where a.line_cd = b.line_cd
         and a.subline_Cd = b.subline_cd
         and a.iss_cd = b.iss_cd
         and a.clm_yy = b.clm_yy
         and a.clm_seq_no = b.clm_seq_no
         and a.line_cd = ws_line_cd
         and a.subline_cd = ws_subline_cd
         and a.iss_cd = ws_iss_cd
         and a.clm_yy = ws_clm_yy
         and a.clm_seq_no = ws_clm_seq_no
         and b.delete_tag != 'Y';
  exception
      when no_data_found then
          null;
      when too_many_rows then
          v_item_no := null;
  end;  

  begin
      select distinct plate_no
        into v_plate_no
        from motcar c, polbasic d
        where c.line_cd = d.line_cd
          and c.subline_cd = d.subline_cd
          and c.iss_cd = d.iss_cd
          and c.pol_seq_no = d.pol_seq_no
          and d.eff_dt = (select max(eff_dt)
                            from polbasic a, motcar b
                            where a.line_cd = b.line_cd
                              and a.subline_cd = b.subline_cd
                              and a.iss_cd = b.iss_cd
                              and a.pol_seq_no = b.pol_seq_no
                              and a.endt_iss_cd = b.endt_iss_cd
                              and a.endt_yy = b.endt_yy
                              and a.endt_seq_no = b.endt_seq_no
                              and b.line_cd = c.line_cd
                              and b.subline_cd = c.subline_cd
                              and b.iss_cd = c.iss_cd
                              and b.pol_seq_no = c.pol_seq_no
                              and b.item_no = c.item_no
                              and b.plate_no is not null 
                              and nvl(a.pol_stat,0) != '5')
          and c.line_cd = ws_line_cd
          and c.subline_cd = ws_subline_cd
          and c.iss_cd = ws_iss_cd
          and c.pol_seq_no = v_pol_seq_no
          and c.item_no = v_item_no
          and c.plate_no is not null;
  exception 
       when no_data_found then
           null;
       when too_many_rows then
           v_plate_no := null;     
  end; 
RETURN(v_plate_no);
END;

COL_1   COL_2   NON-DATABASE-ITEM
-----   -----   -----------------
1       4       5
2       1       3
0       5       5
2       2       4