DECLARE
SSID2 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;
