CREATE TABLE TEST_LOANS
(
  Loan_NO	NUMBER(3),
  Amount	NUMBER(15,3),
  undisbursed	NUMBER(15,3),
  Y5_WTD	NUMBER(15,3),
  Y1_WTD	NUMBER(15,3),
  WTD_Expiry	DATE,
  Bi_Annual_Month	NUMBER(1),
  Y1_expected_WTD	NUMBER(15,3),
  Period_after_Y1 NUMBER(3)
);

INSERT ALL 
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (1, 10000000,3000000,7000000,2000000, TO_DATE('01-01-2026','DD-MM-YYYY'),2,1760000,2)
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (2, 12000000,10005000,2000000,2000000, TO_DATE('01-09-2030','DD-MM-YYYY'),3,1360000,12)
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (3, 15000000,5000000,500000,4500000, TO_DATE('01-12-2024','DD-MM-YYYY'),6,2740000,0)
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (4, 1000000,750000,100000,650000, TO_DATE('01-01-2025','DD-MM-YYYY'),1,398000,0.5)
INTO TEST_LOANS(Loan_NO, Amount, undisbursed, Y5_WTD, Y1_WTD, WTD_Expiry, Bi_Annual_Month, Y1_expected_WTD, Period_after_Y1) VALUES (5, 15000000,5000000,500000,4500000, TO_DATE('01-06-2024','DD-MM-YYYY'),6,2740000,-0.5)

SELECT * FROM DUAL;

Loan_no WTD_date Amount
1 01-02-24 880000
1 01-08-24 880000
1 01-02-25 620000
1 01-08-25 620000
2 01-03-24 680000
2 01-09-24 680000
2 01-03-25 720416.6667
2 01-09-25 720416.6667
2 01-03-26 720416.6667
2 01-09-26 720416.6667
2 01-03-27 720416.6667
2 01-09-27 720416.6667
2 01-03-28 720416.6667
2 01-09-28 720416.6667
2 01-03-29 720416.6667
2 01-09-29 720416.6667
2 01-03-30 720416.6667
2 01-09-30 720416.6667
3 01-06-24 2500000
3 01-12-24 2500000
4 01-01-24 199000
4 01-06-24 199000
4 01-01-25 352000
5 01-06-24 5000000

select t.loan_no, t.wtd_expiry, t.bi_annual_month, t.undisbursed, t.y1_expected_wtd,
       l.wtd_date,
       case when l.wtd_no <= 2 then case when period_after_y1 <= 0 then undisbursed / (period_after_y1 + 2)
                                         else y1_expected_wtd / 2 end
            else (undisbursed - y1_expected_wtd) / period_after_y1 end as amount                             
from   test_loans t cross join lateral
       (
         select  level as wtd_no,
                 add_months(trunc(sysdate, 'yyyy'), bi_annual_month + 5 + 6 * level) as wtd_date
         from    dual
         connect by level <= period_after_y1 + 2
       ) l
order by loan_no, wtd_date    --  if needed
;

(SELECT ROWNUM  YEAR_NO FROM ALL_OBJECTS  WHERE  ROWNUM  <=  EXTRACT(YEAR FROM add_months(sysdate, 50*12)))

select t.loan_no, t.wtd_expiry, t.bi_annual_month, t.undisbursed, t.y1_expected_wtd,
       l.wtd_date,
       case when l.wtd_no <= 2 then case when period_after_y1 <= 0 then undisbursed / (period_after_y1 + 2)
                                         else y1_expected_wtd / 2 end
            else (undisbursed - y1_expected_wtd) / period_after_y1 end as amount                             
from   test_loans t cross join  lateral
       (
         select  level as wtd_no,
                 add_months(trunc(sysdate, 'yyyy'), bi_annual_month + 5 + 6 * level - (case when extract(month from sysdate) <=  bi_annual_month then 12 when extract(month from sysdate) <= bi_annual_month +6 then 6 else 0 end)) as wtd_date
         from    dual
         connect by level <= period_after_y1 + 2
       ) l 
order by loan_no, wtd_date    --  if needed
;

with
  user_inputs (ref_date) as (
    select sysdate from dual
  )
, prep (loan_no, undisbursed, wtd_expiry, bi_annual_month, y1_expected_wtd, first_wtd_date) as (
    select loan_no, undisbursed, wtd_expiry, bi_annual_month, y1_expected_wtd,
           add_months(trunc(ref_date, 'yyyy'), bi_annual_month +
               case when extract(month from ref_date) < bi_annual_month     then -1
                    when extract(month from ref_date) < bi_annual_month + 6 then  5
                    else                                                         11
               end
           )
    from   test_loans cross join user_inputs
  )
select p.loan_no, p.wtd_expiry, p.bi_annual_month, p.undisbursed, p.y1_expected_wtd,
       L.wtd_date, L.wtd_no,
       case when L.wtd_no <= 2 then case when L.ct <= 2 then p.undisbursed/L.ct
                                         else                p.y1_expected_wtd/2
                                    end
            else                    (p.undisbursed - p.y1_expected_wtd)/(L.ct - 2)
       end as amount                             
from   prep p cross join lateral
       (
         select  level as wtd_no, count(*) over () as ct,
                 add_months(p.first_wtd_date, 6 * (level - 1)) as wtd_date
         from    dual
         connect by level <= months_between(wtd_expiry, first_wtd_date)/6 + 1
       ) L
order  by loan_no, wtd_date
;