create or replace package body n_pk_lnnpl as
  function of_getcollmast (av_coopid varchar2, av_contno varchar2) return varchar2 as
    lv_coll varchar2(4000);
    lv_compute varchar2(1000);
    ln_loop number := 1;
    cursor lcur_sql is 
      select 
        lncontcoll.loancontract_no,
        lncollmaster.mortgage_date,
        lncontcoll.description,
        lnnplcollmast.jpk_estprice,
        lnnplcollmast.sale_price,
        lncontcoll.loancolltype_code,
        lncontcoll.ref_collno,
        lncollmaster.estimate_price
      from 
        lncollmaster,   
        lncontcoll,   
        lnnplcollmast  
      where
        lncollmaster.coop_id = lncontcoll.coop_id and
        lncollmaster.coop_id = lnnplcollmast.coop_id and
        lncontcoll.ref_collno = lncollmaster.collmast_no (+) and  
        lncontcoll.ref_collno = lnnplcollmast.ref_collno (+) and
        lncollmaster.coop_id = av_coopid and
        lncontcoll.loancontract_no = av_contno;
  begin
    lv_coll := 'ËÅÑ¡»ÃСѹ';
    
    for lrow_sql in lcur_sql
    loop
      --lv_coll := lv_coll + lrow_sql.monthly_income;
      
      lv_compute := '';
      if lrow_sql.loancolltype_code = '01' then
        lv_compute := lrow_sql.description || ' (' || lrow_sql.ref_collno || ')';
      elsif lrow_sql.loancolltype_code = '02' then
        lv_compute := lrow_sql.description || ' (' || lrow_sql.ref_collno || ')';
      elsif lrow_sql.loancolltype_code = '03' then
        lv_compute := 'à§Ô¹½Ò¡ (' || lrow_sql.description || ')';
      else
        lv_compute := lrow_sql.description;
      end if;
      
      if lrow_sql.estimate_price > 0 then
        lv_compute := lv_compute || ' Ê¡Ê. ' || lrow_sql.estimate_price;
      end if;
      
      if lrow_sql.jpk_estprice > 0 then
        lv_compute := lv_compute || ' ¨¾¤. ' ||  + lrow_sql.jpk_estprice;
      end if;
      
      if lrow_sql.sale_price > 0  then
        lv_compute := lv_compute || ' ¢ÒÂ ' || lrow_sql.sale_price;
      end if;
      
      if ln_loop = 1 then
        lv_coll := lv_coll || '   (1.)' || trim(lv_compute);
      else
        lv_coll := lv_coll || '  ,   (' || ln_loop || '.)' || trim(lv_compute);
      end if;
      ln_loop := ln_loop + 1;
    end loop;
    if ln_loop <= 1 then
      lv_coll := '';
    end if;
    return lv_coll;
  end of_getcollmast;
  
  function of_getpaystmt(av_coopid varchar2, av_contno varchar2, an_return number, an_mm number, an_yyyy number) return number as
    ldc_prn number;
    ldc_int number;
    ldc_prn_m number;
    ldc_int_m number;
    ls_type varchar2(100);
    ls_type2 varchar2(100);
    li_mm number;
    li_yyyy number;
    li_tmp number;
    li_count number;
    ldtm_slip date;
    ln_return number;
  begin
    ln_return := 0;
    li_mm := an_mm;
    li_yyyy := an_yyyy - 543;
    
    if an_return <= 0 then
      li_tmp := 0;
    else
      li_tmp := an_return;
    end if;
    
    li_mm := li_mm - li_tmp;
    
    if li_mm < 1 then 
      li_yyyy := li_yyyy - 1;
    end if;
    if li_mm = 0 then 
      li_mm := 12;
    end if;
    if li_mm = -1 then 
      li_mm := 11;
    end if;
    
    select    loanitemtype_code,    slip_date
    into	 		ls_type,							ldtm_slip
    from 		lncontstatement
    where   coop_id = av_coopid
    and     loancontract_no = av_contno
    and 		extract(month from slip_date) = li_mm
    and 		extract(year from slip_date) = li_yyyy
    and			loanitemtype_code in ('LPM' , 'LPX')
    and			rownum <= 1
    order by slip_date desc;
    
    if ls_type is not null then
      select		sum(principal_payment),		sum(interest_payment),			count(*)
      into	 		ldc_prn,							    ldc_int,								    li_count
      from 		  lncontstatement
      where 		coop_id = av_coopid
      and       loancontract_no = av_contno
      and 			extract(month from slip_date) = li_mm
      and 			extract(year from slip_date) = li_yyyy
      and			  loanitemtype_code in ('LPM' , 'LPX') ;
      
      select		sum(principal_payment),		sum(interest_payment),			count(*)
      into	 		ldc_prn_m,							  ldc_int_m,						      li_count
      from 		  lncontstatement
      where 		coop_id = av_coopid
      and       loancontract_no = av_contno
      and 			extract(month from slip_date) = li_mm
      and 			extract(year from slip_date) = li_yyyy
      and			  loanitemtype_code in ('RPM' , 'RPX') ;
      
      if ldc_prn is null then
        ldc_prn := 0;
      end if;
      if ldc_prn_m is null then
        ldc_prn_m := 0;
      end if;
      if ldc_int is null then
        ldc_int := 0;
      end if;
      if ldc_int_m is null then
        ldc_int_m := 0;
      end if;
      ldc_prn := ldc_prn - ldc_prn_m;
      ldc_int := ldc_int - ldc_int_m;
    else
      return 0;
    end if;
    
    if ldc_prn is null then
      ldc_prn := 0;
    end if;
    if ldc_int is null then
      ldc_int := 0;
    end if;
    if an_return = -1 then
      return ldc_int;
    end if;
    if an_return = 0 then
      ln_return := ldc_prn;
      if ln_return < 0 then
        ln_return := 0;
      end if;
      return ln_return;
    end if;
    if an_return = 1 or an_return = 2 then
      ln_return := ldc_prn + ldc_int;
      if ln_return < 0 then
        ln_return := 0;
      end if;
      return ln_return;
    end if;
    return 0;
  exception
    when others then
      return 0;
  end of_getpaystmt;
  
  function of_diff_year_month(ld_date1 date, ld_date2 date) return number as
    ln_y1 number;
    ln_y2 number;
    ln_m1 number;
    ln_m2 number;
    ln_yy number;
    ln_mm number;
    lv_mm varchar2(10);
    ln_result number;
  begin
    if ld_date1 is null or ld_date2 is null then
      return 0;
    end if;
    if ld_date2 > ld_date1 then
      return 0;
    end if;
    ln_y1 := extract(year from ld_date1);
    ln_y2 := extract(year from ld_date2);
    ln_yy := ln_y1 - ln_y2;
    
    ln_m1 := extract(month from ld_date1);
    ln_m2 := extract(month from ld_date2);
    ln_mm := ln_m1 - ln_m2;
    
    if ln_mm < 0 then
      ln_yy := ln_yy - 1;
      ln_mm := ln_mm + 12;
    end if;
    
    if ln_mm < 10 then
      lv_mm := '0' || to_char(ln_mm);
    elsif ln_mm > 12 then
      return 0;
    else
      lv_mm := to_char(ln_mm);
    end if;
    
    ln_result := to_number( to_char( ln_yy ) || '.' || lv_mm );
    
    return ln_result;
  end of_diff_year_month;
  
  function of_diff_year_month_thai(ld_date1 date, ld_date2 date) return varchar2 as
    lv_result varchar2(30);
    lv_diffed varchar2(30);
    ln_instr number;
    ln_year number;
    ln_month number;
  begin
    lv_diffed := to_char(of_diff_year_month(ld_date1, ld_date2), '99990.00');
    ln_instr := instr(lv_diffed, '.');
    ln_year := to_number(trim(substr(lv_diffed, 1, ln_instr - 1)));
    ln_month := to_number(trim(substr(lv_diffed, ln_instr + 1)));
    lv_result := ln_year || ' »Õ';
    if ln_month > 0 then
      lv_result := lv_result || ' ' || ln_month || ' à´×͹';
    end if;
    return lv_result;
  end;
end n_pk_lnnpl;