DECLARE

 SSID2 NUMBER;

 SSID NUMBER;

 SID NUMBER; 

 SAMOUNT NUMBER;

 

BEGIN

delete from MISSIONARY_SUB;

delete from MISSIONARY_SUB2;

delete from MISSIONARY_YEAR;

/* Select Missionary */

insert into MISSIONARY_SUB

Select 

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

from CONTRIBUTION 

where UPPER(:P9_MISSIONARY_ID) = MISSIONARY_ID 

and :P9_YEAR = to_number(to_char(CONTRIBUTION_DATE,'YYYY'));

/* Sum Supporter's Contribution */

insert into MISSIONARY_SUB2

Select 

SSID,SUM(SAMOUNT),SDATE 

from MISSIONARY_SUB 

Group by SSID,SDATE;

/* Monthly Supporter's Contribution Total */

/* Record Supporters List in Missionary_Year Table */

INSERT INTO MISSIONARY_YEAR (SID)

SELECT DISTINCT SSID FROM MISSIONARY_SUB;

/* Loop */

     FOR x IN (Select * from MISSIONARY_YEAR) LOOP

 

     UPDATE MISSIONARY_YEAR

     SET JAN = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 1)

     WHERE SID = x.SID;

     

     UPDATE MISSIONARY_YEAR

     SET FEB = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 2)

     WHERE SID = x.SID;

 

     UPDATE MISSIONARY_YEAR

     SET MAR = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 3)

     WHERE SID = x.SID;

     

     UPDATE MISSIONARY_YEAR

     SET APR = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 4)

     WHERE SID = x.SID;

 

     UPDATE MISSIONARY_YEAR

     SET MAY = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 5)

     WHERE SID = x.SID;

     

     UPDATE MISSIONARY_YEAR

     SET JUN = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 6)

     WHERE SID = x.SID;

 

     UPDATE MISSIONARY_YEAR

     SET JUL = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 7)

     WHERE SID = x.SID;

     

     UPDATE MISSIONARY_YEAR

     SET AUG = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 8)

     WHERE SID = x.SID;

 

     UPDATE MISSIONARY_YEAR

     SET SEP = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 9)

     WHERE SID = x.SID;

     

     UPDATE MISSIONARY_YEAR

     SET OCT = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 10)

     WHERE SID = x.SID;

 

     UPDATE MISSIONARY_YEAR

     SET NOV = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 11)

     WHERE SID = x.SID;

     

     UPDATE MISSIONARY_YEAR

     SET DEC = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 12)

     WHERE SID = x.SID;

 

     END LOOP;

 

COMMIT;

END;