Home » Developer & Programmer » Forms » function not working (oracle 10g, developer 2000)
function not working [message #512381] |
Sun, 19 June 2011 23:52 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
Hi,
I have a function written by software professional for calculating Dearness Allowance.
I am trying to explain the coding pattern in detail, in case of any doubt pl do ask me .
Dearness Allowance is calculated based on the percentage on the sanctioned pension amount. The percentage changes twice every year.
There is a function created to get the percentage which is mentioned below
FUNCTION f_get_da( i_p_dor IN DATE,
i_p_dod IN DATE,
i_p_catg IN VARCHAR2,
i_p_lpay IN NUMBER,
o_da_prcn OUT NUMBER
) RETURN NUMBER IS
l_da_rate NUMBER(15,4) :=0;
l_min_da NUMBER(15,4) :=0;
l_da NUMBER(15,4) :=0;
l_da_prcn VARCHAR(20);
BEGIN
SELECT TO_NUMBER(SUBSTR(LOV_ID,8)),TO_NUMBER(LOV_INFO_02)
INTO l_da_rate, l_min_da FROM M_LOV
WHERE lov_lov_id = 'DA_PERC_AT'
AND TO_DATE(SUBSTR(lov_id,1,6),'DDMMRR') =
(SELECT MAX(TO_DATE(SUBSTR(lov_id,1,6),'DDMMRR')) FROM M_LOV
WHERE lov_lov_id = 'DA_PERC_AT'
AND SUBSTR(lov_id, 7,1) = SUBSTR(i_p_catg,1,1)
AND TO_DATE(SUBSTR(lov_id,1,6),'DDMMRR') <= NVL(i_p_dor,i_p_dod)
AND TO_NUMBER(lov_info_01) >=i_p_lpay )
AND TO_NUMBER(SUBSTR(LOV_ID,8))=
(SELECT MAX(TO_NUMBER(SUBSTR(LOV_ID,8))) FROM M_LOV
WHERE lov_lov_id = 'DA_PERC_AT'
AND SUBSTR (lov_id, 7, 1) = 'S'
AND TO_DATE(SUBSTR(lov_id,1,6),'DDMMRR')<= NVL(i_p_dor,i_p_dod)
AND TO_NUMBER(lov_info_01) >=i_p_lpay)
AND SUBSTR(lov_id,7,1) = SUBSTR(i_p_catg,1,1) ;
---The above code gives the percentage to be considered based on the date of retirement / death
-- Calculate DA_PERCENTAGE
o_da_prcn:=l_da_rate;
l_da_prcn := i_p_lpay* (l_da_rate/100);
l_da := GREATEST(l_da_prcn, NVL(l_min_da,0));
RETURN (l_da);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Data is not available in the m_lov table
RETURN (-1);
WHEN TOO_MANY_ROWS THEN
-- Multiple rows returned
RETURN (-1);
END f_get_da;
This function has been called for getting the values in this field
For calculating DA on Pension / FP
IF NVL(NVL(:APB.APB_SANC_PENSION_AMT,NVL(:APB.APB_FP,:APB.APB_EFP)),0)>0 THEN
IF pkg_pension.f_get_da_pen(:APEN.APEN_DOR, :APEN.APEN_DOD, f_get_lov_id(:APEN.APEN_CATG), NVL(NVL(:APB.APB_SANC_PENSION_AMT,:APB.APB_FP),0),:APB.APB_DAPRCN_PEN)!= -1 THEN
:APB.APB_DA_PEN := ROUND(pkg_pension.f_get_da_pen(:APEN.APEN_DOR, :APEN.APEN_DOD, f_get_lov_id(:APEN.APEN_CATG), NVL(NVL(:APB.APB_SANC_PENSION_AMT,:APB.APB_FP),0),:APB.APB_DAPRCN_PEN));
But the above code does not get the value in the field provided for. I am new to this oracle and am not able to understand the above code. could any one please let me know whats the problem or else explain what the above code should give value.
thanks in advance
(I am not sure if i have explained the problem in detail.Pl do ask me if in doubt)
CM: swapped [colour] tags for [code] tags. In future please use code tags for all code, it's easier to read.
[Updated on: Mon, 20 June 2011 03:57] by Moderator Report message to a moderator
|
|
|
Re: function not working [message #512418 is a reply to message #512381] |
Mon, 20 June 2011 04:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
1) In future please use [code] tags rather than [colour] tags, makes code a lot easier to read. I've fixed it for you this time.
2) What in the above code do you not understand? It doesn't look particularly complicated?
3) What does it actually do when you run it?
|
|
|
Re: function not working [message #512453 is a reply to message #512418] |
Mon, 20 June 2011 07:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
|
hi,
I am sorry for using the wrong tag.
IF NVL(NVL(:APB.APB_SANC_PENSION_AMT,NVL(:APB.APB_FP,:APB.APB_EFP)),0)>0 THEN
IF pkg_pension.f_get_da_pen(:APEN.APEN_DOR, :APEN.APEN_DOD, f_get_lov_id(:APEN.APEN_CATG), NVL(NVL(:APB.APB_SANC_PENSION_AMT,:APB.APB_FP),0),:APB.APB_DAPRCN_PEN)!= -1 THEN
:APB.APB_DA_PEN := ROUND(pkg_pension.f_get_da_pen(:APEN.APEN_DOR, :APEN.APEN_DOD, f_get_lov_id(:APEN.APEN_CATG), NVL(NVL(:APB.APB_SANC_PENSION_AMT,:APB.APB_FP),0),:APB.APB_DAPRCN_PEN));
The function works perfectly fine for calculating the percentage of DA. What i am not able to understand is the above code.
DA is calculated by percentage * Sanctioned pension amount.
APB_DAPRCN_PEN= percentage
APB_SANC_PENSION_AMT= sanctioned pension amount.
When i run the above code there is no value gets calculated in the DA field where as the sanctioned pension amount gets calculated. But if i write a code this way
:APB.APB_DA_PEN := ROUND((50/100), NVL(NVL(:APB.APB_SANC_PENSION_AMT,:APB.APB_FP),0)));
then both DA and pension gets calculated.
i would like to understand what this line means and what value will it give.
:APB.APB_DA_PEN := ROUND(pkg_pension.f_get_da_pen(:APEN.APEN_DOR, :APEN.APEN_DOD, f_get_lov_id(:APEN.APEN_CATG), NVL(NVL(:APB.APB_SANC_PENSION_AMT,:APB.APB_FP),0),:APB.APB_DAPRCN_PEN));
sorry for the trouble. I am not able to decipher this code.
thanks once again
|
|
|
Re: function not working [message #512463 is a reply to message #512453] |
Mon, 20 June 2011 07:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
lacchhii wrote on Mon, 20 June 2011 13:30i would like to understand what this line means and what value will it give.
:APB.APB_DA_PEN := ROUND(pkg_pension.f_get_da_pen(:APEN.APEN_DOR, :APEN.APEN_DOD, f_get_lov_id(:APEN.APEN_CATG), NVL(NVL(:APB.APB_SANC_PENSION_AMT,:APB.APB_FP),0),:APB.APB_DAPRCN_PEN));
sorry for the trouble. I am not able to decipher this code.
What's to decipher?
It sets the item :APB.APB_DA_PEN to result of the function pkg_pension.f_get_da_pen rounded to the nearest whole number.
I have no idea what value it will give since I don't have:
1) your tables.
2) your data.
3) The input values you are passing to the function.
4) The function code - it's not the same as what you posted in your first post, the name is different.
I would also suggest formatting the code a bit better. Isn't this more readable:
:APB.APB_DA_PEN := ROUND(pkg_pension.f_get_da_pen(:APEN.APEN_DOR,
:APEN.APEN_DOD,
f_get_lov_id(:APEN.APEN_CATG),
NVL(NVL(:APB.APB_SANC_PENSION_AMT,
:APB.APB_FP
),
0
),
:APB.APB_DAPRCN_PEN)
);
|
|
|
Goto Forum:
Current Time: Thu Sep 12 09:17:13 CDT 2024
|