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