Technology

today:
611
yesterday:
719
Total:
1,770,395

Technology

IF THEN/ LOOP SAMPLE

admin 2022.04.10 18:22 Views : 97

DECLARE

 YEAR NUMBER;

 

BEGIN

IF :P131_REPORT_TYPE = 0 THEN

 

DELETE FROM CONTRIBUTION_YEAR_2;

INSERT INTO CONTRIBUTION_YEAR_2 (CTYPE,CID)

Select 

CONTRIBUTION_TYPE_NAME,CONTRIBUTION_TYPE_ID FROM CONTRIBUTION_TYPE

WHERE UPPER(ACTIVE) = 'A';

 

     FOR x IN (Select * from CONTRIBUTION_YEAR_2) LOOP

 

     UPDATE CONTRIBUTION_YEAR_2

     SET MON1 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 1 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

 

     UPDATE CONTRIBUTION_YEAR_2

     SET MON2 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 2 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

   

     UPDATE CONTRIBUTION_YEAR_2

     SET MON3 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 3 AND c.TYPE = x.CID)

      WHERE CID = x.CID;

    

     UPDATE CONTRIBUTION_YEAR_2

     SET MON4 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 4 AND c.TYPE = x.CID)

      WHERE CID = x.CID;   

   

     UPDATE CONTRIBUTION_YEAR_2

     SET MON5 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 5 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON6 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 6 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON7 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 7 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON8 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 8 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

   

     UPDATE CONTRIBUTION_YEAR_2

     SET MON9 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 9 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

   

     UPDATE CONTRIBUTION_YEAR_2

     SET MON10 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 10 AND c.TYPE = x.CID)

     WHERE CID = x.CID;    

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON11 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 11 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON12 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 12 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

      

  END LOOP;    

 

ELSE 

DELETE FROM CONTRIBUTION_YEAR_2;

INSERT INTO CONTRIBUTION_YEAR_2 (CTYPE,CID)

Select 

CONTRIBUTION_TYPE_NAME,CONTRIBUTION_TYPE_ID FROM CONTRIBUTION_TYPE

WHERE UPPER(ACTIVE) = 'A';

 

     FOR x IN (Select * from CONTRIBUTION_YEAR_2) LOOP

 

     UPDATE CONTRIBUTION_YEAR_2

     SET MON1 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 10 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

 

     UPDATE CONTRIBUTION_YEAR_2

     SET MON2 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 11 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

   

     UPDATE CONTRIBUTION_YEAR_2

     SET MON3 = (Select SUM(AMOUNT) from contribution c

WHERE TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 12 AND c.TYPE = x.CID)

      WHERE CID = x.CID;

    

     UPDATE CONTRIBUTION_YEAR_2

     SET MON4 = (Select SUM(AMOUNT) from contribution c

WHERE (TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR +1) AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 1 AND c.TYPE = x.CID)

      WHERE CID = x.CID;   

   

     UPDATE CONTRIBUTION_YEAR_2

     SET MON5 = (Select SUM(AMOUNT) from contribution c

WHERE (TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR +1) AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 2 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON6 = (Select SUM(AMOUNT) from contribution c

WHERE (TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR +1) AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 3 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON7 = (Select SUM(AMOUNT) from contribution c

WHERE (TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR +1) AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 4 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON8 = (Select SUM(AMOUNT) from contribution c

WHERE (TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR +1) AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 5 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

   

     UPDATE CONTRIBUTION_YEAR_2

     SET MON9 = (Select SUM(AMOUNT) from contribution c

WHERE (TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR +1) AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 6 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

   

     UPDATE CONTRIBUTION_YEAR_2

     SET MON10 = (Select SUM(AMOUNT) from contribution c

WHERE (TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR +1) AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 7 AND c.TYPE = x.CID)

     WHERE CID = x.CID;    

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON11 = (Select SUM(AMOUNT) from contribution c

WHERE (TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR +1) AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 8 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

     

     UPDATE CONTRIBUTION_YEAR_2

     SET MON12 = (Select SUM(AMOUNT) from contribution c

WHERE (TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'YYYY')) = :P131_YEAR +1) AND TO_NUMBER(TO_CHAR(CONTRIBUTION_DATE, 'MM')) = 9 AND c.TYPE = x.CID)

     WHERE CID = x.CID;

     

      END LOOP;   

 END IF;

 

COMMIT;

 

END;

 

I want to
List
Write Tag