DECLARE

 SSID2 NUMBER;

 SSID NUMBER;

 SID NUMBER; 

 SAMOUNT NUMBER;

 STARTDATE NUMBER;  

 

BEGIN

delete from MISSIONARY_SUB_2;

delete from MISSIONARY_SUB2_2;

delete from MISSIONARY_YEAR2;

 

/* Select Missionary */

insert into MISSIONARY_SUB_2

Select 

MISSIONARY_ID,SUPPORTER_ID,AMOUNT,CONTRIBUTION_DATE, to_number(to_char(CONTRIBUTION_DATE,'MM')), 

to_number(to_char(CONTRIBUTION_DATE,'YYYY')),

to_number(to_char(CONTRIBUTION_DATE,'MM'))+ to_number(to_char(CONTRIBUTION_DATE,'YYYY'))*100

from CONTRIBUTION 

where UPPER(:P112_MISSIONARY_ID) = MISSIONARY_ID 

and CONTRIBUTION_DATE BETWEEN :P112_FROM AND :P112_TO;

 

/* Sum Supporter's Contribution */

insert into MISSIONARY_SUB2_2

Select 

SSID,SUM(SAMOUNT),SYYYYMM

from MISSIONARY_SUB_2 

Group by SSID,SYYYYMM;

/*COMMIT;

END;

STARTDATE := (Select MIN(SDATE2) FROM MISSIONARY_SUB2_2);*/

 

INSERT INTO MISSIONARY_YEAR2 (SID)

SELECT DISTINCT SSID FROM MISSIONARY_SUB_2;

/* Loop */

     FOR x IN (Select * from MISSIONARY_YEAR2) LOOP

 

     UPDATE MISSIONARY_YEAR2

     SET MON1 = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2_2 WHERE SSID2 = x.SID AND SDATE2 = (Select MIN(SDATE2) FROM MISSIONARY_SUB2_2))

     WHERE SID = x.SID;

 

     UPDATE MISSIONARY_YEAR2

     SET MON2 = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2_2 WHERE SSID2 = x.SID AND SDATE2 = (Select MIN(SDATE2) FROM MISSIONARY_SUB2_2)+1)

     WHERE SID = x.SID;

     

     END LOOP;

 

COMMIT;

END;

 

select * from MISSIONARY_YEAR2

select * from MISSIONARY_SUB_2

select * from MISSIONARY_SUB2_2

select to_date('092015','mmyyyy') from dual

 

    dt

1   09/01/2015