OraFAQ Forum: Forms » function not working

Home » Developer & Programmer » Forms » function not working (oracle 10g, developer 2000)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

function not working [message #512381]

Sun, 19 June 2011 23:52

lacchhii
Messages: 151
Registered: May 2009
Location: bangalore

Senior Member

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

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?

Report message to a moderator

Re: function not working [message #512453 is a reply to message #512418]

Mon, 20 June 2011 07:30

lacchhii
Messages: 151
Registered: May 2009
Location: bangalore

Senior Member

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

Report message to a moderator

Re: function not working [message #512463 is a reply to message #512453]

Mon, 20 June 2011 07:59

cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester

Senior Member

lacchhii wrote on Mon, 20 June 2011 13:30

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.

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)
                        );

Report message to a moderator

Previous Topic:	master details
Next Topic:	LOV

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 07 16:11:58 CDT 2024