create or replace PACKAGE BODY fpb as
  function split2array (p_in_string varchar2, p_delim varchar2) return t_array  is
    i       number := 0;
    pos     number := 0;
    lv_str  varchar2(300) := p_in_string;
    strings t_array;
   begin 
      -- determine first chuck of string
      pos := instr(lv_str,p_delim,1,1);
      if pos = 0 then
          strings(1) := lv_str;
      end if ; 
      -- while there are chunks left, loop
      while ( pos != 0) loop
         -- increment counter
         i := i + 1;       
         -- create array element for chuck of string
         strings(i) := substr(lv_str,1,pos-1);       
         -- remove chunk from string
         lv_str := substr(lv_str,pos+1,length(lv_str));       
         -- determine next chunk
         pos := instr(lv_str,p_delim,1,1);       
         -- no last chunk, add to array
         if pos = 0 then      
            strings(i+1) := lv_str;       
         end if;    
      end loop; 
      -- return array
      return strings;    
  end split2array;
  
  function validate_person_card(avc_card_no varchar2) return number as
    lnum_total number;
    lnum_tmp number;
    lnum_mod11 number;
    lnum_del11 number;
    lvc_bf_fin varchar2(10);
    lvc_check varchar2(2);
  begin
    lnum_total := 0;
    for i in 1 .. 12 loop
      lnum_tmp := to_number( substr(avc_card_no, i, 1) ) * (14 - i);
      lnum_total := lnum_total + lnum_tmp;
    end loop;
    lnum_mod11 := lnum_total mod 11;
    lnum_del11 := 11 - lnum_mod11;
    lvc_bf_fin := to_char(lnum_del11);
    if length(lvc_bf_fin) > 1 then
      lvc_bf_fin := substr(lvc_bf_fin , length(lvc_bf_fin), 1);
    end if;
    lvc_check := substr(avc_card_no , 13, 1);
    if lvc_check = lvc_bf_fin then
      return 1;
    else
      return 0;
    end if;
    exception when others then
        return 0;
  end validate_person_card;

  function fill(avc_text varchar2, an_count number) return varchar2 as
  begin
    declare
      lvc_f varchar2(100);
    begin
      lvc_f := '';
      for i in 1 .. an_count loop
        lvc_f := lvc_f || avc_text;
      end loop;
      return lvc_f;
    end;
  end;

  function right(avc_text varchar2, an_index1 number)return varchar2 as
  begin
    declare
      ln_length number;
      ln_begin number;
    begin
      ln_length := length(avc_text);
      ln_begin := ln_length - an_index1;
      return substr(avc_text, ln_begin + 1);
    end;
  end;
  
  function get_coopid(avc_coop_id varchar2, avc_coop_control varchar2) return varchar2 as
  begin
    return avc_coop_control;
  end;
  
  function cal_yearmonth(first_date date, last_date date) return varchar2 as
    first_year number;
    last_year number;
    total_year number;
    first_month number;
    last_month number;
    total_month number;
    lvc_result varchar2(10);
  begin
    lvc_result := '0';
    first_year := extract(year from first_date);
    last_year := extract(year from last_date);
    first_month := extract(month from first_date);
    last_month := extract(month from last_date);
    total_year := last_year - first_year;
    total_month := last_month - first_month;
    if (total_month < 0) then
      total_year := total_year - 1;
      total_month := total_month * -1;
      total_month := 12 - total_month;
    end if;
    if(total_month >= 10) then
      lvc_result := to_char( total_year ) || '.' || to_char( total_month );
    else
      lvc_result := to_char( total_year ) || '.' || '0' || to_char( total_month );
    end if;
    return lvc_result;
  end;
  
  procedure throw(avc_error_message varchar2) as
  begin
    raise_application_error(-20001, '#ERROR-20001#' || avc_error_message || '##20001##');
  end;
  
  function cal_period_payment(anum_loanrequest_amt number, anum_period number, lnum_intrate number, anum_roundpay number) return number as
    --r = (i / (1 - v)) * p
    r   number;     --งวดที่ต้องผ่อนชำระ
    i   number;     --i = rr / 1200
    v   number;     --v = power(1 / (1 + i)  , t)
    p   number;     --ยอดเงินกู้
    rr  number;     --อัตราดอกเบี้ย
    t   number;     --จำนวนงวด (เดือน)
  begin
    --วิธีคิด http://www.ghbank.co.th/th/content.php?id=19
    p := anum_loanrequest_amt;
    rr := lnum_intrate * 100;
    t := anum_period;
    i := rr / 1200;
    v := power(1 / (1 + i), t);
    r := (i / (1 - v)) * p;
    if(anum_roundpay > 0) then
      if r mod anum_roundpay > 0 then
        r := r + anum_roundpay - (r mod anum_roundpay);
      end if;
    end if;
    return r;
  end cal_period_payment;
  
  function ex return varchar2 as
    lvc_error_message varchar2(4000);
    ln_20001 number := -1;
  begin
    lvc_error_message := to_char(sqlerrm);
    ln_20001 := instr(lvc_error_message, '#ERROR-20001#');
    if lvc_error_message is not null and ln_20001 > 0 then
      lvc_error_message := substr(lvc_error_message, (ln_20001 + 13));
      ln_20001 := instr(lvc_error_message , '##20001##');
      lvc_error_message := substr(lvc_error_message, 1, (ln_20001 - 1));
    end if;
    return lvc_error_message;
  end ex;
  
  function len(avc_text varchar2) return number as
  begin
    if(avc_text is null)then
      return 0;
    else
      return length(avc_text);
    end if;
  end len;
  
  function ifnull(avc_value varchar2, avc_replace varchar2) return varchar2 as
  begin
    if avc_value is null then
      return avc_replace;
    else
      return avc_value;
    end if;
  end ifnull;
  
  function ifnull(an_value number, an_replace number) return number as
  begin
    if an_value is null then
      return an_replace;
    else
      return an_value;
    end if;
  end ifnull;
end fpb;