| ceiling [message #503555] |
Mon, 18 April 2011 01:22  |
|
|
Hi,
I use oracle 10g with front end as D2k 9i. We maintain the pension of the government staff. Each pensioners gets monthly pension and a lumpsum amount as gratuity after retirement / death.
While calculating Gratuity any amount withheld due to specific reason will be reduced and final amount authorised.
the calculation for gratuity is mentioned below
L_BAL_GRTY:=NVL(L_DCRG_AMT,:APB_DCRG_AMT)-NVL(:APB_PAID_UPTILL,0);
:ADW.ADW_AMT := CEIL(L_BAL_GRTY*(:ADW.ADW_PERCENT/100));
Here i have one problem. the amount to be withheld is calculated as 10% of the actual gratuity amount subject to a maximum of Rs.10000/-.
While the above code calculates 10% of the Gratuity amount but does not restrict it to Rs.10000/-.
What changes should i have to do to the above code to restrict it to the maximum limit.
Thanks in advance
|
|
|
|
| Re: ceiling [message #503580 is a reply to message #503555] |
Mon, 18 April 2011 03:06  |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
SQL> SELECT LEAST(10000, 239111) FROM dual;
LEAST(10000,239111)
-------------------
10000
SQL>
|
|
|
|
| Re: ceiling [message #503584 is a reply to message #503580] |
Mon, 18 April 2011 03:16 |
|
|
thanks for the reply.
sorry, i am not very much familiar with the coding in oracle.
i require to modify the code written here
:ADW.ADW_AMT := CEIL(L_BAL_GRTY*(:ADW.ADW_PERCENT/100));
this gives the actual amount without limiting it to 10000/-. Suppose if the GRatuity amount is 150000, then the with held amount should be 150000*10% =15000, restricted to 10000/-. it should restrict the ADW_AMT to 10000/- instead of 15000/-, but if it is less than 10000/- , then the actual amount should be entered.
How do i use the least condition as you had mentioned in the above code.
sorry for the trouble.
thanks once again
|
|
|
|
| Re: ceiling [message #503589 is a reply to message #503584] |
Mon, 18 April 2011 03:29 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Least takes two values, one would be 10000, the other would be the current calculation.
You really can work this one out.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
