[AngularJS] How do I conditionally apply CSS styles in AngularJS?

Requirement
If the amount value of the last column is less than zero, it should be showed by red color.

Solution
Step1. create a css file, and define a pair of key and value.


Step2. add ng-class in the input text, and give a condition: 
when the value of row.amtc are less than zero, then apply negative-number css; 
when the value of row.amtc are equal or greater than zero, then apply nothing.

1:  <td class="text-left border-right" style="width:150px">  
2:    <input id="amtc" name="amtc" size="30" maxlength="16"  
3:        data-ng-readonly="true" ng-class="row.amtc < 0 ? 'negative-number' : ''"  
4:        data-ng-model="row.amtc" style="width:150px;text-align:right"   
5:        title="{{row.amtcf}}"/>  
6:  </td>  

Demo

2014/06 花蓮

太魯閣砂卡礑步道

夢幻湖 雲山水

林田山林業文化園區

花蓮港-麗娜輪

[Oracle] Use to_char to format a number type column to currency format

Requirement

Before formatting.....
User hopes to add 1000 separator in amount-related columns, including AMT_A, AMT_B, and AMT_C

SELECT A.YEAR AS YEAR,--年度\n
       A.YYY_MM AS YYY_MM, --資料年月\n
       A.ASP, --性質別\n
       A.ITEM_CD AS ITEM_CD, --特別預算支出項目代號\n
       TRIM(A.ITEM_NM) AS ITEM_NM, --特別預算支出項目名稱\n
       F_FMS420R_GET_ACCUM_AMTA('10201', '10302', A.ASP) AS AMT_A, --實支數\n
       F_FMS420R_GET_ACCUM_AMTB('10201', '10302', A.ASP) AS AMT_B,
       --年度經費滕餘數\n
       F_FMS420R_GET_ACCUM_AMTC('10201', '10302', A.ASP) AS AMT_C --淨數\n
FROM FMS420FA A
WHERE A.YEAR = '103' AND A.YYY_MM = '10302'
ORDER BY TRIM(A.ITEM_NM) ;

Solution
We can use Oracle build-in function, TO_CHAR, to do currency format.

SELECT A.YEAR AS YEAR,--年度\n
       A.YYY_MM AS YYY_MM, --資料年月\n
       A.ASP, --性質別\n
       A.ITEM_CD AS ITEM_CD,
       --特別預算支出項目代號\n
       TRIM(A.ITEM_NM) AS ITEM_NM,
       --特別預算支出項目名稱\n
       TRIM(TO_CHAR(F_FMS420R_GET_ACCUM_AMTA('10201', '10302', A.ASP), '999,999,999,990.9999')) AS AMT_A_F,
       --實支數\n
       TRIM(TO_CHAR(F_FMS420R_GET_ACCUM_AMTB('10201', '10302', A.ASP), '999,999,999,990.9999')) AS AMT_B_F,
       --年度經費滕餘數\n
       TRIM(TO_CHAR(F_FMS420R_GET_ACCUM_AMTC('10201', '10302', A.ASP), '999,999,999,990.9999')) AS AMT_C_F --淨數\n
FROM FMS420FA A
WHERE A.YEAR = '103' AND A.YYY_MM = '10302'
ORDER BY TRIM(A.ITEM_NM) ;

Parameter(s) [SPEC_FUND] are unresovlable in query string

Problem
Here has part of my SQL statement.
I faced a weird situation, this SQL statement can be executed successfully in SQL Developer, but fail to execute in Java program.

SELECT /*+USE_HASH (Y,LY,CALENDAR,LWD)*/ Y.PDATE ,
       NVL(Y.INCM_TAX,0) + NVL(LY.INCM_TAX,0) AS INCM_TAX ,
       NVL(Y.INCM_OTH,0) + NVL(LY.INCM_OTH,0) AS INCM_OTH ,
       Y.INCM_LOAN ,
       NVL(Y.INCM_NET_SALE,0) + NVL(LY.INCM_NET_SALE,0) AS INCM_NET_SALE ,
       NVL(Y.PAY_OTH,0) + NVL(LY.PAY_OTH,0) AS PAY_OTH ,
       Y.PAY_T_BILL ,
       NVL(Y.SPEC_FUND,0) - NVL(LWD.SPEC_FUND,0) AS SPEC_FUND,                        --:SPEC_FUND – ALLOT_TAX(自AVE107FB)
       NVL(Y.CHK_FUND,0) - NVL(LWD.CHK_FUND,0) AS CHK_FUND                     FROM .....

As I executed this SQL statement in Java program, it throws exception : Parameter(s) [SPEC_FUND] are unresovlable in query string

Root Cause
This problem result from ":SPEC_FUND" in remarks. 

SELECT /*+USE_HASH (Y,LY,CALENDAR,LWD)*/ Y.PDATE ,
       NVL(Y.INCM_TAX,0) + NVL(LY.INCM_TAX,0) AS INCM_TAX ,
       NVL(Y.INCM_OTH,0) + NVL(LY.INCM_OTH,0) AS INCM_OTH ,
       Y.INCM_LOAN ,
       NVL(Y.INCM_NET_SALE,0) + NVL(LY.INCM_NET_SALE,0) AS INCM_NET_SALE ,
       NVL(Y.PAY_OTH,0) + NVL(LY.PAY_OTH,0) AS PAY_OTH ,
       Y.PAY_T_BILL ,
       NVL(Y.SPEC_FUND,0) - NVL(LWD.SPEC_FUND,0) AS SPEC_FUND,                        --:SPEC_FUND – ALLOT_TAX(自AVE107FB)
                                          NVL(Y.CHK_FUND,0) - NVL(LWD.CHK_FUND,0) AS CHK_FUND                     FROM .....

Basically, program will ignore what I wrote in remarks. But I do not know why it will still interpret remarks in this case. Therefore, As I remove ":SPEC_FUND", this SQL statement will be working fine.

穩健投資三原則

最近閱讀了漫步華爾街這本書，內容提到穩健投資三原則，筆記一下

原則一、只買未來五年或五年以上，盈餘成長高於平均值的公司

原則二、絕不付出高過真實價值的股價

原則三、尋找有題材的成長股，讓投資人建造空中樓閣

其中一跟三，其實都不容易去做預測，比較能掌握的就是第二個原則了，文中用本益比來做為判斷的原則，只要買進本益比低，就算成長未實現且盈餘下跌，損失也有限；但是如果預測成真，就可以賺錢，藉此增加勝算

[iReport] How to adjust fileds' height dynamically

Problem

Here is my report template in iReport. I have 11 field elements in detail band. 

The "remarks field" may have more information need to show. Other fields' height need to increase based on "remark field's" height.

Solution

For each field, we need to set three attributes as bellows:

• Position type: float
• Stretch type: relative to tallest object
• Stretch with overflow: true (checked)

Click detail band

Set split type to Prevent

See....the problem had been resolved.

Reference

[1] http://stackoverflow.com/questions/2544746/how-to-increase-detail-band-height-dynamically

[AngularJS] How to set focus on an input element as loading form

Requirement

As we entering this page, user hopes to set focus on the first input element instead of clicking the first input element by mouse.

Solution

Just set autofocus attribute in the input element

1:  <label for="year" class="control-label">年月起迄 :</label>   
2:  <input type="text" class="form-control" style="width: 45%;"   
3:        data-ng-model="model.start" id="start" name="start"   
4:         autofocus required data-c-date-picker="{format : 'yyy/MM'}" >  

Reference
[1] https://groups.google.com/forum/#!topic/angular/UcArQh7dQfQ

如何計算基金的單位數以及每月分配金額

以復華南非幣長期收益基金B為例

最低認購額: 300000 南非幣

目前南非幣(ZAR)匯率是 2.8480，折合台幣約105337.0786516854

此檔基金淨值是9.07，單位數=105337.0786516854(投入金額) / 9.07(基金淨值) = 11613.79036953532

目前此檔基金每月每單位分配金額是0.063，這樣一個月會配的金額=11613.79036953532(持有單位數) * 0.063(每單位分配金額) = 731.6687932807254

Reference

[1] http://www.ptt.cc/man/Fund/D9F2/M.1273250099.A.1EF.html

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