Total Pageviews

2014/06/03

Calling Stored Procedure using Spring Framework

Problem
Owing to our report has performance issue, so I need to adjust SQL statement into store procedure. But how do I call stored procedure via Spring framework?

Solution
Lets us consider stored procedure as:
CREATE OR REPLACE PROCEDURE PROC_FMS451_REPORT(FYR IN VARCHAR2) AS YMINUS6_01 VARCHAR2(5);
 YMINUS6_02 VARCHAR2(5);
 YMINUS6_03 VARCHAR2(5);
 YMINUS6_04 VARCHAR2(5);
 YMINUS6_05 VARCHAR2(5);
 YMINUS6_06 VARCHAR2(5);
 YMINUS6_07 VARCHAR2(5);
 YMINUS6_08 VARCHAR2(5);
 YMINUS6_09 VARCHAR2(5);
 YMINUS6_10 VARCHAR2(5);
 YMINUS6_11 VARCHAR2(5);
 YMINUS6_12 VARCHAR2(5);
 YMINUS5_01 VARCHAR2(5);
 YMINUS5_02 VARCHAR2(5);
 YMINUS5_03 VARCHAR2(5);
 YMINUS5_04 VARCHAR2(5);
 YMINUS5_05 VARCHAR2(5);
 YMINUS5_06 VARCHAR2(5);
 YMINUS5_07 VARCHAR2(5);
 YMINUS5_08 VARCHAR2(5);
 YMINUS5_09 VARCHAR2(5);
 YMINUS5_10 VARCHAR2(5);
 YMINUS5_11 VARCHAR2(5);
 YMINUS5_12 VARCHAR2(5);
 YMINUS4_01 VARCHAR2(5);
 YMINUS4_02 VARCHAR2(5);
 YMINUS4_03 VARCHAR2(5);
 YMINUS4_04 VARCHAR2(5);
 YMINUS4_05 VARCHAR2(5);
 YMINUS4_06 VARCHAR2(5);
 YMINUS4_07 VARCHAR2(5);
 YMINUS4_08 VARCHAR2(5);
 YMINUS4_09 VARCHAR2(5);
 YMINUS4_10 VARCHAR2(5);
 YMINUS4_11 VARCHAR2(5);
 YMINUS4_12 VARCHAR2(5);
 YMINUS3_01 VARCHAR2(5);
 YMINUS3_02 VARCHAR2(5);
 YMINUS3_03 VARCHAR2(5);
 YMINUS3_04 VARCHAR2(5);
 YMINUS3_05 VARCHAR2(5);
 YMINUS3_06 VARCHAR2(5);
 YMINUS3_07 VARCHAR2(5);
 YMINUS3_08 VARCHAR2(5);
 YMINUS3_09 VARCHAR2(5);
 YMINUS3_10 VARCHAR2(5);
 YMINUS3_11 VARCHAR2(5);
 YMINUS3_12 VARCHAR2(5);
 YMINUS2_01 VARCHAR2(5);
 YMINUS2_02 VARCHAR2(5);
 YMINUS2_03 VARCHAR2(5);
 YMINUS2_04 VARCHAR2(5);
 YMINUS2_05 VARCHAR2(5);
 YMINUS2_06 VARCHAR2(5);
 YMINUS2_07 VARCHAR2(5);
 YMINUS2_08 VARCHAR2(5);
 YMINUS2_09 VARCHAR2(5);
 YMINUS2_10 VARCHAR2(5);
 YMINUS2_11 VARCHAR2(5);
 YMINUS2_12 VARCHAR2(5);
 YMINUS1_01 VARCHAR2(5);
 YMINUS1_02 VARCHAR2(5);
 YMINUS1_03 VARCHAR2(5);
 YMINUS1_04 VARCHAR2(5);
 YMINUS1_05 VARCHAR2(5);
 YMINUS1_06 VARCHAR2(5);
 YMINUS1_07 VARCHAR2(5);
 YMINUS1_08 VARCHAR2(5);
 YMINUS1_09 VARCHAR2(5);
 YMINUS1_10 VARCHAR2(5);
 YMINUS1_11 VARCHAR2(5);
 YMINUS1_12 VARCHAR2(5);
 YMINUS0_01 VARCHAR2(5);
 YMINUS0_02 VARCHAR2(5);
 YMINUS0_03 VARCHAR2(5);
 YMINUS0_04 VARCHAR2(5);
 YMINUS0_05 VARCHAR2(5);
 YMINUS0_06 VARCHAR2(5);
 YMINUS0_07 VARCHAR2(5);
 YMINUS0_08 VARCHAR2(5);
 YMINUS0_09 VARCHAR2(5);
 YMINUS0_10 VARCHAR2(5);
 YMINUS0_11 VARCHAR2(5);
 YMINUS0_12 VARCHAR2(5);

CURSOR YYYMM_CURSOR IS
SELECT LPAD(FYR-6, 3, '0')||'01' YMINUS6_01,
       LPAD(FYR-6, 3, '0')||'02' YMINUS6_02,
       LPAD(FYR-6, 3, '0')||'03' YMINUS6_03,
       LPAD(FYR-6, 3, '0')||'04' YMINUS6_04,
       LPAD(FYR-6, 3, '0')||'05' YMINUS6_05,
       LPAD(FYR-6, 3, '0')||'06' YMINUS6_06,
       LPAD(FYR-6, 3, '0')||'07' YMINUS6_07,
       LPAD(FYR-6, 3, '0')||'08' YMINUS6_08,
       LPAD(FYR-6, 3, '0')||'09' YMINUS6_09,
       LPAD(FYR-6, 3, '0')||'10' YMINUS6_10,
       LPAD(FYR-6, 3, '0')||'11' YMINUS6_11,
       LPAD(FYR-6, 3, '0')||'12' YMINUS6_12,
       LPAD(FYR-5, 3, '0')||'01' YMINUS5_01,
       LPAD(FYR-5, 3, '0')||'02' YMINUS5_02,
       LPAD(FYR-5, 3, '0')||'03' YMINUS5_03,
       LPAD(FYR-5, 3, '0')||'04' YMINUS5_04,
       LPAD(FYR-5, 3, '0')||'05' YMINUS5_05,
       LPAD(FYR-5, 3, '0')||'06' YMINUS5_06,
       LPAD(FYR-5, 3, '0')||'07' YMINUS5_07,
       LPAD(FYR-5, 3, '0')||'08' YMINUS5_08,
       LPAD(FYR-5, 3, '0')||'09' YMINUS5_09,
       LPAD(FYR-5, 3, '0')||'10' YMINUS5_10,
       LPAD(FYR-5, 3, '0')||'11' YMINUS5_11,
       LPAD(FYR-5, 3, '0')||'12' YMINUS5_12,
       LPAD(FYR-4, 3, '0')||'01' YMINUS4_01,
       LPAD(FYR-4, 3, '0')||'02' YMINUS4_02,
       LPAD(FYR-4, 3, '0')||'03' YMINUS4_03,
       LPAD(FYR-4, 3, '0')||'04' YMINUS4_04,
       LPAD(FYR-4, 3, '0')||'05' YMINUS4_05,
       LPAD(FYR-4, 3, '0')||'06' YMINUS4_06,
       LPAD(FYR-4, 3, '0')||'07' YMINUS4_07,
       LPAD(FYR-4, 3, '0')||'08' YMINUS4_08,
       LPAD(FYR-4, 3, '0')||'09' YMINUS4_09,
       LPAD(FYR-4, 3, '0')||'10' YMINUS4_10,
       LPAD(FYR-4, 3, '0')||'11' YMINUS4_11,
       LPAD(FYR-4, 3, '0')||'12' YMINUS4_12,
       LPAD(FYR-3, 3, '0')||'01' YMINUS3_01,
       LPAD(FYR-3, 3, '0')||'02' YMINUS3_02,
       LPAD(FYR-3, 3, '0')||'03' YMINUS3_03,
       LPAD(FYR-3, 3, '0')||'04' YMINUS3_04,
       LPAD(FYR-3, 3, '0')||'05' YMINUS3_05,
       LPAD(FYR-3, 3, '0')||'06' YMINUS3_06,
       LPAD(FYR-3, 3, '0')||'07' YMINUS3_07,
       LPAD(FYR-3, 3, '0')||'08' YMINUS3_08,
       LPAD(FYR-3, 3, '0')||'09' YMINUS3_09,
       LPAD(FYR-3, 3, '0')||'10' YMINUS3_10,
       LPAD(FYR-3, 3, '0')||'11' YMINUS3_11,
       LPAD(FYR-3, 3, '0')||'12' YMINUS3_12,
       LPAD(FYR-2, 3, '0')||'01' YMINUS2_01,
       LPAD(FYR-2, 3, '0')||'02' YMINUS2_02,
       LPAD(FYR-2, 3, '0')||'03' YMINUS2_03,
       LPAD(FYR-2, 3, '0')||'04' YMINUS2_04,
       LPAD(FYR-2, 3, '0')||'05' YMINUS2_05,
       LPAD(FYR-2, 3, '0')||'06' YMINUS2_06,
       LPAD(FYR-2, 3, '0')||'07' YMINUS2_07,
       LPAD(FYR-2, 3, '0')||'08' YMINUS2_08,
       LPAD(FYR-2, 3, '0')||'09' YMINUS2_09,
       LPAD(FYR-2, 3, '0')||'10' YMINUS2_10,
       LPAD(FYR-2, 3, '0')||'11' YMINUS2_11,
       LPAD(FYR-2, 3, '0')||'12' YMINUS2_12,
       LPAD(FYR-1, 3, '0')||'01' YMINUS1_01,
       LPAD(FYR-1, 3, '0')||'02' YMINUS1_02,
       LPAD(FYR-1, 3, '0')||'03' YMINUS1_03,
       LPAD(FYR-1, 3, '0')||'04' YMINUS1_04,
       LPAD(FYR-1, 3, '0')||'05' YMINUS1_05,
       LPAD(FYR-1, 3, '0')||'06' YMINUS1_06,
       LPAD(FYR-1, 3, '0')||'07' YMINUS1_07,
       LPAD(FYR-1, 3, '0')||'08' YMINUS1_08,
       LPAD(FYR-1, 3, '0')||'09' YMINUS1_09,
       LPAD(FYR-1, 3, '0')||'10' YMINUS1_10,
       LPAD(FYR-1, 3, '0')||'11' YMINUS1_11,
       LPAD(FYR-1, 3, '0')||'12' YMINUS1_12,
       LPAD(FYR, 3, '0')||'01' YMINUS0_01,
       LPAD(FYR, 3, '0')||'02' YMINUS0_02,
       LPAD(FYR, 3, '0')||'03' YMINUS0_03,
       LPAD(FYR, 3, '0')||'04' YMINUS0_04,
       LPAD(FYR, 3, '0')||'05' YMINUS0_05,
       LPAD(FYR, 3, '0')||'06' YMINUS0_06,
       LPAD(FYR, 3, '0')||'07' YMINUS0_07,
       LPAD(FYR, 3, '0')||'08' YMINUS0_08,
       LPAD(FYR, 3, '0')||'09' YMINUS0_09,
       LPAD(FYR, 3, '0')||'10' YMINUS0_10,
       LPAD(FYR, 3, '0')||'11' YMINUS0_11,
       LPAD(FYR, 3, '0')||'12' YMINUS0_12
FROM DUAL ;

 BEGIN OPEN YYYMM_CURSOR;

 FETCH YYYMM_CURSOR INTO YMINUS6_01,YMINUS6_02,YMINUS6_03,YMINUS6_04,YMINUS6_05,
                         YMINUS6_06,YMINUS6_07,YMINUS6_08,YMINUS6_09,YMINUS6_10,
                         YMINUS6_11,YMINUS6_12,YMINUS5_01,YMINUS5_02,YMINUS5_03,
                         YMINUS5_04,YMINUS5_05,YMINUS5_06,YMINUS5_07,YMINUS5_08,
                         YMINUS5_09,YMINUS5_10,YMINUS5_11,YMINUS5_12,YMINUS4_01,
                         YMINUS4_02,YMINUS4_03,YMINUS4_04,YMINUS4_05,YMINUS4_06,
                         YMINUS4_07,YMINUS4_08,YMINUS4_09,YMINUS4_10,YMINUS4_11,
                         YMINUS4_12,YMINUS3_01,YMINUS3_02,YMINUS3_03,YMINUS3_04,
                         YMINUS3_05,YMINUS3_06,YMINUS3_07,YMINUS3_08,YMINUS3_09,
                         YMINUS3_10,YMINUS3_11,YMINUS3_12,YMINUS2_01,YMINUS2_02,
                         YMINUS2_03,YMINUS2_04,YMINUS2_05,YMINUS2_06,YMINUS2_07,
                         YMINUS2_08,YMINUS2_09,YMINUS2_10,YMINUS2_11,YMINUS2_12,
                         YMINUS1_01,YMINUS1_02,YMINUS1_03,YMINUS1_04,YMINUS1_05,
                         YMINUS1_06,YMINUS1_07,YMINUS1_08,YMINUS1_09,YMINUS1_10,
                         YMINUS1_11,YMINUS1_12,YMINUS0_01,YMINUS0_02,YMINUS0_03,
                         YMINUS0_04,YMINUS0_05,YMINUS0_06,YMINUS0_07,YMINUS0_08,
                         YMINUS0_09,YMINUS0_10,YMINUS0_11,YMINUS0_12 ;

 CLOSE YYYMM_CURSOR;


COMMIT;

 --i. 取得前月的國庫收支現金結餘數, 並將前月的國庫收支現金結餘數搬到當月的期初餘額
--ii. 計算當月的國庫收支現金結餘數=期初餘額(1)+當月收入(2)合計-當月支出(3)
 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS6_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS6_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS5_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS5_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS4_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS4_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS3_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS3_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS2_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS2_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS1_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS1_12);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_01);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_01);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_02);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_02);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_03);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_03);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_04);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_04);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_05);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_05);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_06);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_06);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_07);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_07);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_08);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_08);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_09);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_09);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_10);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_10);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_11);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_11);

 PROC_FMS451_COMP_CUM_INIT_AMT(YMINUS0_12);
 PROC_FMS451_COMP_SUB_TOTAL(YMINUS0_12);

 END;

We will assume that required NamedParameterJdbcTemplate is injected in our DAO class.
 @Slf4j  
 public class Fms451faRepositoryImpl implements Fms451faRepositoryCustom {  
   @Autowired  
   private NamedParameterJdbcTemplate jdbcTemplate;  
   /**  
    * {@inheritDoc}  
    */  
   @Override  
   public void updateSubtotal(String yyyMM) {  
     jdbcTemplate.getJdbcOperations().update("call AP_PSR.PROC_FMS451_REPORT(?)", yyyMM);  
   }  
 }  

After moving Java code to stored procedure, the time spend to generate report from 3 minutes to 12 seconds. 


Reference
[1] http://docs.oracle.com/cd/B19306_01/appdev.102/a58231/ch3.htm

2014/05/31

2014/05 Travel

從挑夫古道遠眺望龍埤


望龍埤


基隆外木山





2014/05/27

[iReport] Exported Excel Report But Has Missing Data

Problem
I am using iReport 5.5 in my project, the requirement is to use iReport to design report template, and export the report with pdf and xls format.
The problem is as I export pdf format, I can see complete data

but as I export xls format, the xls report has lost data in page header band.


Solution
The missing data problem results from the overlapping elements (even 1 pixel).

After I fixed the overlapping problems (add one more space between elements)

I can see complete data in xls report.

2014/05/23

How do I sort an array of files according to their last modified dates?

Apache commons IO provide LastModifiedFileComparator to do file sorting based on its last modified date/time.
It provide two instance:
1. LASTMODIFIED_COMPARATOR: Last modified comparator instance
2. LASTMODIFIED_REVERSE: Reverse last modified comparator instance

Example
1. Read files from old to new
   /**  
    * The main method.  
    *   
    * @param args  
    *      the arguments  
    */  
   public static void main(String args[]) {  
     Collection files =   
       FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), // assign file path  
                 FileFilterUtils.prefixFileFilter("COP0407"), // assign file name  
                 FalseFileFilter.FALSE);// include subdirectories  
     File[] fileArr = files.toArray(new File[files.size()]);  
     Arrays.sort(fileArr, LastModifiedFileComparator.LASTMODIFIED_COMPARATOR);//from old to new  
     // print file name  
     for (File file : fileArr) {  
       System.out.println(file.getName()+"["+new Date(file.lastModified())+"]");  
     }  
   }  

The console will print
 COP0407_01_1030501.txt[Thu May 08 16:45:01 CST 2014]  
 COP0407_01_1030502.txt[Fri May 23 10:31:45 CST 2014]  

2. Read files from new to old
   /**  
    * The main method.  
    *   
    * @param args  
    *      the arguments  
    */  
   public static void main(String args[]) {  
     Collection files =   
       FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), // assign file path  
                 FileFilterUtils.prefixFileFilter("COP0407"), // assign file name  
                 FalseFileFilter.FALSE);// include subdirectories  
     File[] fileArr = files.toArray(new File[files.size()]);  
     Arrays.sort(fileArr, LastModifiedFileComparator.LASTMODIFIED_REVERSE);//from new to old  
     // print file name  
     for (File file : fileArr) {  
       System.out.println(file.getName()+"["+new Date(file.lastModified())+"]");  
     }  
   }  

The console will print
 COP0407_01_1030502.txt[Fri May 23 10:31:45 CST 2014]  
 COP0407_01_1030501.txt[Thu May 08 16:45:01 CST 2014]  

Reference
[1] http://commons.apache.org/proper/commons-io/apidocs/org/apache/commons/io/comparator/LastModifiedFileComparator.html

How to get all files with specific name in a directory(including subdirectories) using Commons IO

Requirement
1. Search specific directory called "COP0407" 
2. Search through all directories called "COP0407" 


Supposed the directory is D:\psrdata\transfer\ftp\tofms, and has one subdirectory D:\psrdata\transfer\ftp\tofms\done




Solution
Apache commons IO package provide FileUtils for general file manipulation, here has two sample code
1. Search specific directory called "COP0407"  (exclude subdirectory)
   public static void main(String args[]) {  
     Collection files=  
     FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), //assign file path  
                    FileFilterUtils.prefixFileFilter("COP0407"), //assign file name  
                    FalseFileFilter.FALSE);//exclude subdirectories  
     //print file name  
     for(File file : files) {  
       System.out.println(file.getName());  
     }  
   }  

The console will print
 COP0407_01_1030501.txt  
 COP0407_01_1030502.txt  

2. Search through all directories called "COP0407" 
   public static void main(String args[]) {  
     Collection files=  
     FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), //assign file path  
                    FileFilterUtils.prefixFileFilter("COP0407"), //assign file name  
                    TrueFileFilter.TRUE);//include subdirectories  
     //print file name  
     for(File file : files) {  
       System.out.println(file.getName());  
     }  
   }  

The console will print
 COP0407_01_1030501.txt  
 COP0407_01_1030502.txt  
 COP0407_01_1030501 (2).txt  

Reference
[1] http://commons.apache.org/proper/commons-io/apidocs/org/apache/commons/io/FileUtils.html#listFiles(java.io.File, org.apache.commons.io.filefilter.IOFileFilter, org.apache.commons.io.filefilter.IOFileFilter)

2014/05/22

Create custom display filter in AngularJS

Requirement
Assume we have a drop down list which used to control the currency unit display.
It has two options, the first one is dollar. You can see the amount had been used dollar as its currency unit display.

The second option is a hundred million. You can see the amount had been used hundred million as its currency unit display.

Custom display filter
AngularJS provide some build-in filter, ex. number filter can used to determine If the input is not a number an empty string is returned.
In order to create a new filter, you are going to create a fms421rCurrency module and register your custom filter with this module:
   //根據資料單位下拉單的選項,決定顯示於前端的金額單位是元或億  
   app.filter('fms421rCurrency', ['$filter', function ($filter, $scope) {  
     var fun = function(input, id) {  
       try{  
         input = BigDecimalROUND_HALF_UP(input, id);  
         return input;  
       }catch (e) {  
         return "";  
       }  
     };  
    return fun;  
   }]);  
  //根據資料單位下拉單的選項,決定金額是否要除以100000000  
   var BigDecimalROUND_HALF_UP = function(input, id){  
     if(input == 0){  
       return new BigDecimal("0");  
     }  
     var numb = new BigDecimal(input.toString());  
     var bil = new BigDecimal("100000000");   
     if ("02"==id) {//01:元, 02:億  
       numb = numb.divide(bil,0, BigDecimal.prototype.ROUND_HALF_UP);  
     }  
     return numb;  
   };  

The syntax for using filters in Angular templates is as follows:
{{ expression | filter }}
We can apply the filter in the fms421r tempate page:
 <td class="text-right border-right">   
 {{fms421rDto.dayRslt1|fms421rCurrency:model.dataunit.id|fms421rnative|number}}  
 </td>   


Reference




2014/05/21

虛擬股市交易系統


如果手上資金不夠,可以利用這個虛擬交易網站做股票進出、練功
順便觀察、參考其他人的進出 http://www.cmoney.tw/


Reference
[1] http://www.cmoney.tw/notes/note-detail.aspx?cid=22814&nid=11548&utm_source=Facebook&utm_medium=Timeline&utm_term=Notes&utm_content=nid%3D11548&utm_campaign=nid%3D11548