1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ceiling

Discussion in 'Oracle Forms and Reports' started by ksjj, Apr 18, 2011.

  1. ksjj

    ksjj Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    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/-.

    The ADW_AMT should not exceed Rs.10000/-

    What changes should i have to do to the above code to restrict it to the maximum limit.

    Thanks in advance
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Use LEAST Function as below.


    Code (SQL):
    SQL> SELECT LEAST ( CEIL(125000*(10/100)) ,10000) ADW_AMT FROM DUAL;

       ADW_AMT
    ----------
         10000

    SQL> SELECT LEAST ( CEIL(95000*(10/100)) ,10000)  ADW_AMT  FROM DUAL;

       ADW_AMT
    ----------
          9500

    SQL>