7 Replies Latest reply: Aug 6, 2013 2:45 AM by A15997789456123 RSS

    Accrual Rate

    A15997789456123

      Dear Gurus,

       

      My client requirement is like they have Annual Rate is 30 Per year and Accrual Rate is different in different months like in Jan it will 2, in Feb it will 1, in Mar it will 3, in Apr it will 2 like this only every month it is different Accrual Rate but the total is (Annual Rate) is 30. Kindly suggest how i will capture it in system and in which Accrual Formula will use it. { Do I need to create a UDT like once it is JANUARY then Accrual_Rate is 2, by using function GET_TABLE_VALUE }

       

      Thanks,

      Shobhit

        • 1. Re: Accrual Rate
          A15997789456123

          Dear all,

          Any reply its urgent.

           

          Thanks,

          Shobhit

          • 2. Re: Accrual Rate
            A15997789456123

            Can any gentlemen do the needful help, its urgent by client.....

            • 3. Re: Accrual Rate
              Sanjay Singh

              Hi Shobhit,

               

              You are on the right track. You can create a UDT and capture the value for each month, you can codify months as 1 for jan, 2 for feb..etc. write a simple PL-SQL function which will have month as a parameter and internally it adds the accrual for each month and return the total value which needs to be accrued.

              Example:

              if the calculation date is for March, set the l_month_count as 3 and it means you need to get accrual for Jan, Feb and March month

                 in the function you can write a loop

                    within the loop fetch the details for each month Jan

                       l_value := 0;

                       l_count := 1;

                       l_accrual_val := xx_get_accrual_val(l_count)

                       l_value := l_accrual_val;  -- for Jan

                       l_value := l_value + l_accrual_val ;

                        l_count = l_count + 1;

                      exit when count < l_month_count +1;

               

              Register the PL-SQL function as a form function

               

              In your accrual formula write the logic as below:

              1. Get the calculation date and derive the month from it and number it

              2. Call your form function

              3. Return it as accrued value

               

              Try this it should work.

               

              Thanks,

              Sanjay

              • 4. Re: Accrual Rate
                A15997789456123

                Hi Sanjay,

                 

                Thank you very much to provide the guide on my client requirement. i have started to implemented this solution approach on this issue.

                Once i required any more assistance will again catch you in this same thread. thanks again.

                 

                Thanks & Regards,

                Shobhit

                • 5. Re: Accrual Rate
                  A15997789456123

                  Dear sanjay,

                   

                   

                  Thanks !!

                   

                   

                   

                  As per your reply we have created an UDT to store the values for Annual Rate based on Band and Accrual Frequency based on Months. My query is, might be in future if client is increasing the BAND (Now it is 11 but in future it could be 13) and will add this in Table will is it work correct or it will not. or if we are changing Frequency based on Months in that particular table will it work correct or it will not.

                   

                   

                   

                  Regards,

                  Shobhit

                  • 6. Re: Accrual Rate
                    Sanjay Singh

                    Hi Shobhit,

                     

                    As far as band value is considered, example in march if the current accrual rate is 3 and in future it changes to 5, your current function should work fine.

                    But i doubt that the change in frequency will be taken care automatically, in case of change in frequency the accrual formula will have to be revisited.

                    In order to handle this so that minimum change to be done in accrual formula, design your function in such a way that apart from only calculation month as input parameter it also takes frequency as a parameter and based on month and frequency you get the calculation done in the function and it returns you the value.

                     

                    Hope it helps. Feel free if you need any other details.

                     

                    Thanks,

                    Sanjay

                    • 7. Re: Accrual Rate
                      A15997789456123

                      Dear Sanjay sir,

                       

                      I have written the formula, for every BAND Ceiling (total entitlement) is working and in month JAN Accrual Rate is correct for every Bands but from FEB accrual rate is not working correct, it is accumulating some different amounts. Kindly find my Accrual Formula and please let me know where i am doing mistake , i will edit that code.

                       

                      FORMULA ********

                       

                       

                       

                      /* ------------------------------------------------------------------------

                          NAME : PTO_SIMPLE_BALANCE_MULTIPLIER

                          This formula calculates the start and end dates for out simple multiplier.

                          This formula calculates the dates between which an assignment is to accrue.

                          It is based on PTO_SIMPLE_MULTIPLIER, but has been amended to allow its

                          use in the new payroll balance functionality. It may not be used out of the box

                          but must be amended, according to the documentaion, by inserting the

                          name of the database item which was created with your defined balance.

                          This defined balance should have been created immediately after the

                          accrual plan.

                         ---------------------------------------------------------------------*/

                       

                       

                      DEFAULT FOR ACP_INELIGIBILITY_PERIOD_TYPE IS 'CM'

                      DEFAULT FOR ACP_INELIGIBILITY_PERIOD_LENGTH IS 0

                      DEFAULT FOR ACP_CONTINUOUS_SERVICE_DATE IS '4712/12/31 00:00:00' (date)

                      DEFAULT FOR ACP_ENROLLMENT_END_DATE IS '4712/12/31 00:00:00' (date)

                      DEFAULT FOR ACP_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)

                      DEFAULT FOR ACP_ENROLLMENT_START_DATE IS '4712/12/31 00:00:00' (date)

                      DEFAULT FOR ACP_SERVICE_START_DATE IS '4712/12/31 00:00:00' (date)

                      Default for Accrual_Start_Date is '4712/12/31 00:00:00' (date)

                      Default for Accrual_Latest_Balance is 0

                      DEFAULT FOR ASG_GRADE IS '0'

                       

                       

                       

                       

                       

                       

                      INPUTS ARE

                      Calculation_Date (date),

                      Accrual_Start_Date (date),

                      Accrual_Latest_Balance

                       

                       

                       

                       

                      MONT=SUBSTR(TO_CHAR(Calculation_Date,'DD-MON-YYYY'),4,3)

                       

                       

                       

                       

                      IF ASG_GRADE WAS NOT DEFAULTED THEN 

                       

                       

                       

                       

                      (x=TO_NUMBER(GET_TABLE_VALUE('BMC_NL_ACRL_CEILING2000','BAND',ASG_GRADE))) /* this table for entitlement for one year with their BAND */

                      E = SET_NUMBER('CEILING', X)

                       

                       

                      (A=TO_NUMBER(GET_TABLE_VALUE('BMC_NL_ACRL_RATING2000',MONT,ASG_GRADE))) /* this table for monthly accrual rate, like if BAND is 1 and month is JAN the Accrual Rate is 2 */

                      E = SET_NUMBER('ACCRUAL_RATE',A )

                       

                       

                       

                       

                      Accruing_Frequency = 'M'   /* Month */

                      Accruing_Multiplier = 1

                       

                       

                      E = SET_TEXT('ACCRUING_FREQUENCY', Accruing_Frequency)

                      E = SET_NUMBER('ACCRUING_MULTIPLIER', Accruing_Multiplier)

                       

                       

                      Beginning_Of_Calculation_Year = to_date('0101'||to_char(Calculation_Date,'YYYY'),

                                                              'DDMMYYYY')

                       

                       

                      IF (Beginning_Of_Calculation_Year > Calculation_Date) THEN

                      (

                        Beginning_of_Calculation_Year = ADD_MONTHS(Beginning_Of_Calculation_Year, -12)

                      )

                       

                       

                      IF Accrual_Start_Date < Beginning_Of_Calculation_Year THEN

                      (

                        Accrual_Start_Date = Beginning_Of_Calculation_Year

                      )

                       

                       

                      E = SET_DATE('BEGINNING_OF_CALCULATION_YEAR', Beginning_Of_Calculation_Year)

                       

                       

                      E = GET_PERIOD_DATES(Beginning_of_Calculation_Year,

                                           Accruing_Frequency,

                                           Beginning_Of_Calculation_Year,

                                           Accruing_Multiplier)

                       

                       

                      First_Period_SD = get_date('PERIOD_START_DATE')

                      First_Period_ED = get_date('PERIOD_END_DATE')

                       

                       

                      /* ------------------------------------------------------------------------

                         Set the Calculation_Date to the Termination Date if not null

                      -------------------------------------------------------------------------- */

                       

                       

                      IF NOT (ACP_TERMINATION_DATE WAS DEFAULTED) OR

                          NOT (ACP_ENROLLMENT_END_DATE WAS DEFAULTED) THEN

                      (

                        Early_End_Date = least(ACP_TERMINATION_DATE, ACP_ENROLLMENT_END_DATE)

                       

                       

                        IF (Early_End_Date < First_Period_ED) THEN

                        (

                          Total_Accrued_PTO = 0

                          E = PUT_MESSAGE('HR_52794_PTO_FML_ASG_TER')

                         )

                       

                       

                        IF (Early_End_Date < Calculation_Date) THEN

                        (

                          Calculation_Date = Early_End_Date

                        )

                      )

                       

                       

                      /* ------------------------------------------------------------------------

                         Get the last whole period prior to the Calculation Date and ensure that it is within the

                         Year (if the Calculation Date is the End of a Period then use that period)

                         ------------------------------------------------------------------------ */

                       

                       

                      E = GET_PERIOD_DATES(Calculation_Date,

                                           Accruing_Frequency,

                                           Beginning_of_Calculation_Year,

                                           Accruing_Multiplier)

                       

                       

                      Calculation_Period_SD  = get_date('PERIOD_START_DATE')

                      Calculation_Period_ED = get_date('PERIOD_END_DATE')

                       

                       

                      IF (Calculation_Date <> Calculation_Period_ED) THEN

                          (

                           E = GET_PERIOD_DATES(ADD_DAYS(Calculation_Period_SD,-1),

                                                                      Accruing_Frequency,

                                                                      Beginning_of_Calculation_Year,

                                                                      Accruing_Multiplier)

                       

                       

                          Calculation_Period_SD  = get_date('PERIOD_START_DATE')

                          Calculation_Period_ED = get_date('PERIOD_END_DATE')

                         )

                       

                       

                      If (Calculation_Period_ED < First_Period_ED) THEN

                         (

                          Total_Accrued_PTO = 0

                          E = PUT_MESSAGE('HR_52795_PTO_FML_CALC_DATE')

                          )

                       

                       

                      /* ------------------------------------------------------------------------

                         Set the Continuous Service Global Variable, whilst also

                         ensuring that the continuous service date is before the Calculation Period

                        ------------------------------------------------------------------------ */

                       

                       

                      IF (ACP_CONTINUOUS_SERVICE_DATE WAS DEFAULTED) THEN

                          (

                          E = set_date('CONTINUOUS_SERVICE_DATE', ACP_SERVICE_START_DATE)

                          )

                      ELSE IF(ACP_CONTINUOUS_SERVICE_DATE > Calculation_Period_SD) THEN

                         (

                          Total_Accrued_PTO = 0

                          E = PUT_MESSAGE('HR_52796_PTO_FML_CSD')

                          E = set_date('CONTINUOUS_SERVICE_DATE', ACP_CONTINUOUS_SERVICE_DATE)

                         )

                      ELSE

                        (

                          E = set_date('CONTINUOUS_SERVICE_DATE', ACP_CONTINUOUS_SERVICE_DATE)

                        )

                       

                       

                      Continuous_Service_Date = get_date('CONTINUOUS_SERVICE_DATE')

                       

                       

                      First_Eligible_To_Accrue_Date  = Continuous_Service_Date

                       

                       

                      /*------------------------------------------------------------------------

                         Determine the date on which accrued PTo may first be registered, i.e the date on which the

                         Ineligibility Period expires

                         ------------------------------------------------------------------------ */

                       

                       

                      Accrual_Ineligibility_Expired_Date = First_Eligible_To_Accrue_Date

                       

                       

                      IF (ACP_INELIGIBILITY_PERIOD_LENGTH > 0) THEN

                         (

                         IF ACP_INELIGIBILITY_PERIOD_TYPE = 'BM' THEN

                            (

                            Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,

                                                                            ACP_INELIGIBILITY_PERIOD_LENGTH*2)

                            )

                         ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = 'F' THEN

                            (

                            Accrual_Ineligibility_Expired_Date = add_days(Continuous_Service_Date,

                                                                          ACP_INELIGIBILITY_PERIOD_LENGTH*14)

                            )

                         ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = 'CM' THEN

                            (

                            Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,

                                                                            ACP_INELIGIBILITY_PERIOD_LENGTH)

                            )

                         ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = 'LM' THEN

                            (

                            Accrual_Ineligibility_Expired_Date = add_days(Continuous_Service_Date,

                                                                          ACP_INELIGIBILITY_PERIOD_LENGTH*28)

                            )

                         ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = 'Q' THEN

                            (

                            Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,

                                                                            ACP_INELIGIBILITY_PERIOD_LENGTH*3)

                            )

                         ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = 'SM' THEN

                            (

                            Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,

                                                                            ACP_INELIGIBILITY_PERIOD_LENGTH/2)

                            )

                         ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = 'SY' THEN

                            (

                            Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,

                                                                            ACP_INELIGIBILITY_PERIOD_LENGTH*6)

                            )

                         ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = 'W' THEN

                            (

                            Accrual_Ineligibility_Expired_Date = add_days(Continuous_Service_Date,

                                                                          ACP_INELIGIBILITY_PERIOD_LENGTH*7)

                            )

                         ELSE IF ACP_INELIGIBILITY_PERIOD_TYPE = 'Y' THEN

                            (

                            Accrual_Ineligibility_Expired_Date = add_months(Continuous_Service_Date,

                                                                            ACP_INELIGIBILITY_PERIOD_LENGTH*12)

                            )

                       

                       

                         IF Accrual_Ineligibility_Expired_Date > First_Eligible_To_Accrue_Date

                         AND Calculation_Date < Accrual_Ineligibility_Expired_Date THEN

                            (

                            First_Eligible_To_Accrue_Date = Accrual_Ineligibility_Expired_Date

                            )

                         )

                       

                       

                      /* ------------------------------------------------------------------------

                         Get the first full period following the First_Eligible_To_Accrue_Date

                         (if it falls on the beginning of the period then use that period)

                      ------------------------------------------------------------------------- */

                       

                       

                      Latest_Balance = 0

                       

                       

                      IF (not Accrual_Start_Date was defaulted) AND

                         ((Calculation_Date < Accrual_Ineligibility_Expired_Date) OR

                          (Accrual_Start_Date > Accrual_Ineligibility_Expired_Date)) THEN

                      (

                        Adjusted_Start_Date = Get_Start_Date(Accrual_Start_Date,

                                                             Beginning_Of_Calculation_Year)

                       

                       

                        /* Check whether RESET_PTO_ACCRUAL action parameter is defined */

                        /* If yes, then we need to calculate from the beginning         */

                       

                       

                        Reset_Accruals = Reset_PTO_Accruals()

                       

                       

                        IF ((Adjusted_Start_Date < Accrual_Start_Date) OR

                            (Reset_Accruals = 'TRUE'))  THEN

                        (

                          Process_Full_Term = 'Y'

                        )

                        ELSE

                        (

                          Process_Full_Term = 'N'

                        )

                      )

                      ELSE

                      (

                        Process_Full_Term = 'Y'

                      )

                       

                       

                      IF (Process_Full_Term = 'N') AND

                         (Accrual_Start_Date >= First_Eligible_To_Accrue_Date) THEN

                      (

                       

                       

                        E = GET_PERIOD_DATES(Adjusted_Start_Date,

                                             Accruing_Frequency,

                                             Beginning_Of_Calculation_Year,

                                             Accruing_Multiplier)

                       

                       

                        First_Eligible_To_Accrue_Period_SD = get_date('PERIOD_START_DATE')

                        First_Eligible_To_Accrue_Period_ED = get_date('PERIOD_END_DATE')

                       

                       

                        Latest_Balance = Accrual_Latest_Balance

                        Effective_Start_Date = Accrual_Start_Date

                       

                       

                      )

                      ELSE IF First_Eligible_To_Accrue_Date > Beginning_Of_Calculation_Year THEN

                      (

                        IF (not Accrual_Start_Date was defaulted) THEN

                        (

                          Latest_Balance = Accrual_Latest_Balance

                        )

                        ELSE

                        (

                          Latest_Balance = 0

                        )

                       

                       

                        E = GET_PERIOD_DATES(First_Eligible_To_Accrue_Date,

                                             Accruing_Frequency,

                                             Beginning_Of_Calculation_Year,

                                             Accruing_Multiplier)

                       

                       

                        First_Eligible_To_Accrue_Period_SD  = get_date('PERIOD_START_DATE')

                        First_Eligible_To_Accrue_Period_ED  = get_date('PERIOD_END_DATE')

                       

                       

                        IF First_Eligible_To_Accrue_Date <> First_Eligible_To_Accrue_Period_SD THEN

                            (

                             E = GET_PERIOD_DATES(add_days(First_Eligible_To_Accrue_Period_ED,1),

                                                                        Accruing_Frequency,

                                                                        Beginning_Of_Calculation_Year,

                                                                        Accruing_Multiplier)

                       

                       

                             First_Eligible_To_Accrue_Period_SD  = get_date('PERIOD_START_DATE')

                             First_Eligible_To_Accrue_Period_ED  = get_date('PERIOD_END_DATE')

                             )

                       

                       

                        IF (First_Eligible_To_Accrue_Period_SD > Calculation_Period_ED) THEN

                           (

                             Total_Accrued_PTO = 0

                             E = PUT_MESSAGE('HR_52793_PTO_FML_ASG_INELIG')

                          )

                       

                       

                        Effective_Start_Date = First_Eligible_To_Accrue_Date

                      )

                      ELSE

                      (

                        IF (not Accrual_Start_Date was defaulted) THEN

                        (

                          Latest_Balance = Accrual_Latest_Balance

                        )

                        ELSE

                        (

                          Latest_Balance = 0

                        )

                       

                       

                        First_Eligible_To_Accrue_Period_SD  = First_Period_SD

                        First_Eligible_To_Accrue_Period_ED  = First_Period_ED

                       

                       

                        Effective_Start_Date = Beginning_Of_Calculation_Year

                      )

                      /*start of 4733782*/

                      Effective_Start_Date = GREATEST(Effective_Start_Date, ACP_ENROLLMENT_START_DATE)

                      /*end of 4733782*/

                      /* ------------------------------------------------------------------------

                         Determine the date on which PTO actually starts accruing based on Hire Date,

                         Continuous Service Date and plan Enrollment Start Date. Remember, we have already determined

                         whether to user hire date or CSD earlier in the formula.

                         If this date is after the 1st period and the fisrt eligible date then establish the first full period

                         after this date (if the Actual Start Date falls on the beginning of a period then use this period)

                      ------------------------------------------------------------------------ */

                       

                       

                      IF Continuous_Service_date = ACP_CONTINUOUS_SERVICE_DATE THEN

                      (

                        Actual_Accrual_Start_Date = Continuous_service_Date

                      )

                      ELSE

                      (

                        Actual_Accrual_Start_Date = greatest(Continuous_Service_Date,

                                                             ACP_ENROLLMENT_START_DATE,

                                                             First_Period_SD)

                      )

                       

                       

                      /* -------------------------------------------------------------------------

                             Determine the actual start of the accrual calculation

                      -------------------------------------------------------------------------*/

                      IF (Actual_Accrual_Start_Date > First_Period_SD AND

                           Actual_Accrual_Start_Date > First_Eligible_To_Accrue_Period_SD) THEN

                          (

                           E = GET_PERIOD_DATES(Actual_Accrual_Start_Date,

                                                Accruing_Frequency,

                                                Beginning_Of_Calculation_Year,

                                                Accruing_Multiplier)

                       

                       

                           Accrual_Start_Period_SD = get_date('PERIOD_START_DATE')

                           Accrual_Start_Period_ED = get_date('PERIOD_END_DATE')

                       

                       

                           IF Actual_Accrual_Start_Date > Accrual_Start_Period_SD THEN

                               (

                                E = GET_PERIOD_DATES(add_days(Accrual_Start_Period_ED,1),

                                                     Accruing_Frequency,

                                                     Beginning_of_Calculation_Year,

                                                     Accruing_Multiplier)

                       

                       

                                Accrual_Start_Period_SD = get_date('PERIOD_START_DATE')

                                Accrual_Start_Period_ED = get_date('PERIOD_END_DATE')

                               )

                       

                       

                      /* -----------------------------------------------------------------

                              If the Actual Acrual Period is after the Calculation Period then end the processing.

                      ----------------------------------------------------------------- */

                              IF (Accrual_Start_Period_SD > Calculation_Period_ED) THEN

                                  (

                                  Total_Accrued_PTO = 0

                                  E = PUT_MESSAGE('HR_52797_PTO_FML_ACT_ACCRUAL')

                                  )

                           )

                       

                       

                      ELSE IF (First_Eligible_To_Accrue_Period_SD > First_Period_SD) THEN

                           (

                            Accrual_Start_Period_SD = First_Eligible_To_Accrue_Period_SD

                            Accrual_Start_Period_ED = First_Eligible_To_Accrue_Period_ED

                           )

                      ELSE

                          (

                            Accrual_Start_Period_SD = First_Period_SD

                            Accrual_Start_Period_ED = First_Period_ED

                          )

                       

                       

                      /* -------------------------------------------------------------------

                             Now set up the information that will be used in when looping through the periods

                      --------------------------------------------------------------------- */

                       

                       

                      IF Calculation_Period_ED >= Accrual_Start_Period_ED THEN

                      (

                        E = set_date('PERIOD_SD',Accrual_Start_Period_SD)

                        E = set_date('PERIOD_ED',Accrual_Start_Period_ED)

                        E = set_date('LAST_PERIOD_SD',Calculation_Period_SD)

                        E = set_date('LAST_PERIOD_ED',Calculation_Period_ED)

                       

                       

                        IF (Process_Full_Term = 'N') THEN

                        (

                          E = set_number('TOTAL_ACCRUED_PTO', Latest_Balance)

                        )

                        ELSE

                        (

                          E = set_number('TOTAL_ACCRUED_PTO', 0)

                        )

                       

                       

                        E = LOOP_CONTROL('PTO_SIMPLE_PERIOD_ACCRUAL')

                       

                       

                        Total_Accrued_PTO = get_number('TOTAL_ACCRUED_PTO') - Latest_Balance

                       

                       

                      )

                      ELSE

                      (

                        Total_Accrued_PTO = 0

                      )

                       

                       

                       

                       

                      IF Accrual_Start_Period_SD <= Calculation_Period_SD THEN

                      (

                        Accrual_end_date = Calculation_Period_ED

                      )

                       

                       

                      IF Process_Full_Term = 'Y' AND

                         Effective_Start_Date > Actual_Accrual_Start_Date THEN

                      (

                        Effective_Start_Date = Actual_Accrual_Start_Date

                      )

                       

                       

                      Effective_End_Date = Calculation_Date

                       

                       

                      IF Effective_Start_Date >= Effective_End_Date THEN

                      (

                        Effective_Start_Date = Effective_End_Date

                      )

                       

                       

                      RETURN Total_Accrued_PTO, Effective_start_date, Effective_end_date, Accrual_end_date

                       

                       

                       

                       

                      Thanks & Regards,

                      Shobhit