PROCEDURE Calc_annuitet
( p_Loan_amount Number --сумма кредита  1000000
 ,p_Start_Date date   -- дата расчета   07.12.2015
 ,p_maturity_date date  -- дата окончания 07.12.2016
 ,p_time_period Number  -- количесиво месяца 12 месяцев
 ,p_pay_period number    --период погашения 1 месяц
 ,p_Spread_Rate number   --процентная ставка 45
 ,P_loan_id number     -- ключ договора 1
 
)
is
 Cursor c_c(p_type varchar2) is 
 select ADD_MONTHS( trunc(p_start_date), level*p_pay_period ) as pay_date
       ,((p_time_period)/p_pay_period) qnty_pay
    from dual
   where p_type='S'
  connect by level <=((p_time_period)/p_pay_period)
   v_koef Number;
   v_per_payment Number;
   v_debt_amount Number;
   v_debt_amount_Round Number;
   v_rest  Number:=0;
   v_rest_round  Number:=0;
   v_repay Number:=0;
   
   v_round Number:=0;
   i Number:=0;
   v_end_date date:=p_start_date;
   v_interest_amount Number:=p_Loan_amount;
   v_day Number;
   v_percent Number;
   v_period Number:=0;
 begin
        Delete 
        From LOAN_SCHEDULE  
        where loan_id=P_loan_id;
       Insert into LOAN_SCHEDULE(PAY_DATE,LOAN_REST,LOAN_ID)
       values (p_start_date,p_Loan_amount,P_loan_id); 
        
     if mod((p_time_period/p_pay_period), 1)=0 then
         v_koef:=((p_spread_rate/12)*p_pay_period)/100;
         v_per_payment:=(p_Loan_amount*v_koef)/(1-power((1+v_koef), -p_time_period));
             
        FOR v_doc IN c_c('S')
         LOOP
             if v_doc.pay_date>=p_maturity_date then 
                  i:=v_round;
                 else
                  i:=0;
             end if;  
               v_day := Get_days(v_end_date, v_doc.pay_date);  --количество дней -- 30 дней
               v_percent:=Get_Percent(v_interest_amount,p_Spread_rate,v_day);  --Round(((p_amount*p_Spread_rate)/36000)*p_days,2);
               v_debt_amount:=v_per_payment-v_percent;
               v_debt_amount_Round:=Round(v_per_payment-v_percent,2);
               v_rest:=v_rest+(v_debt_amount_Round+i);
              
            insert into LOAN_SCHEDULE(PAY_DATE     -- дата погашения
                                              ,LOAN_REST  --остаток основной суммы
                                              ,LOAN_REPAY  --погашение основной суммы
                                              ,LOAN_INT   --процент
                                              ,LOAN_ID   --ключ
                                             )
                                           
                         values (v_doc.pay_date
                                ,p_Loan_amount-v_rest
                                ,v_debt_amount_Round+i
                                ,v_percent
                                ,P_loan_id);
              v_round:=v_round+(v_debt_amount-v_debt_amount_Round);   
              v_end_date:=v_doc.pay_date;
              v_interest_amount:=p_Loan_amount-v_rest;
              
         END LOOP;    
    end if;
end Calc_annuitet;