Fast Formula‎ > ‎

Managing Formula


Compiling a Formula



Like all other programming languages, fast formulae have their own compilers to translate the complex language to a standard system language. When we write the code in to the Write formula window and hit on “Save”, the compiler comes into action. It scans the entire formula and looks for the compilation errors.  The Compilation errors are usually the syntax related issues, or context related issues, where a function is being used without appropriate context related to the formula. Once the Compilation errors are field and the code is verified, it checks the Verified check box in the write formulas screen.

The compiler takes care of the Compilation errors, but not the runtime errors. There can be run time errors that appear on a formula during execution. To solve those, we can debug the formula from the error text or simply add messages to the formula text.

Here is a list of Common run time formula errors and the possible reasons.

Error

Possible Error Reasons

Invalid Number

If a DBI context expects a number and is passed with a non-numeric value.

Uninitialized Variables

One or more of the variables are not initialized properly.

Null Data Found

This error comes when a DBI has Null allowed flag as Y and is not defaulted.

Divide by Zero

The Famous mathematical error. There is a division with the second parameter having a value of zero.

No Data Found

Usually when a DBI does not find a row in Database

Too Many Rows

More than one rows returned by the Database


Efficiency of a Formula



We have known how to write a Formula, right? Let’s talk about the Dos and Don’ts to make sure the written formula runs efficiently. Here are few points:

·        Use the minimum possible Elements as possible, as the Elements take a lot of time to be fetched and processed.

·        In case we have to use more than one element, use the Input Values for just one element and use the DBI for all others. This makes the Formula run faster.

·        However if we are using just one element, Database Items run slower than the Input Values.

·        The number of statements (Excluding the Comments) in a Formula increases the processing duration. So we should always try to make the formula short.

So instead of writing:

A = C * D
E = A /15

We should write:           

E = (C * D) / 15

·         Usage of unnecessary functions is a killer. We should avoid the format functions, like TO_DATE, TO_CHAR etc. unless we really need them.

·         Aliases always help. Although it increases in readability, there is no impact on Performance.


Examples of a Formula



Let’s look at one example of a Fast Formula. The first one is a simple Participation and Rate Eligibility Formula, and the second one is a complex Payroll formula.


/*===============================================================================

FORMULA TYPE: Participation and Rate Eligibility

FORMULA DESCRIPTION: The Formula gets the Gender of the Participant being evaluated by the function get_gender(),Then it compares the Values, If It’s a Male then the Return Variable ‘ELIGIBLE’ is set to N else Y. So if a Participant is a Male he is denies Eligibility, Else he is granted.

================================================================================

================================================================================*/

l_gender = get_gender()

if l_gender = 'M' then

( ELIGIBLE = 'N' )

else

( ELIGIBLE = 'Y')

return ELIGIBLE

/*==============================================================================*/

 

 

/*===============================================================================

   FORMULA NAME:   DOUBLE_DISMANTLE_HOUR_X_RATE

   FORMULA TYPE:   Payroll

   DESCRIPTION:    THIS FORMULA CALCULATES EARNING AMOUNT, RATE, HOURS WORKED

                   HOURS PAID ETC, AND STAGE ALL THE VALUES IN TEMP TABLE FOR

                   FURTHER CALCULATING ACCRUALS AND DEDUCTIONS

 

   CREATED BY      DATE                  COMMENT 

 

**********************************************************************************/

   /* ===== Alias Section Begin ====== */

   ALIAS SCL_ASG_US_WORK_SCHEDULE AS Work_Schedule

   ALIAS SCL_ASG_US_TIMECARD_REQUIRED AS Timecard_Required

   /* ===== Alias Section End ====== */

   /* ===== Defaults Section Begin ===== */

   DEFAULT FOR PAY_PROC_PERIOD_START_DATE IS '0001/01/01 00:00:00' (DATE)

   DEFAULT FOR PAY_PROC_PERIOD_END_DATE     IS '0001/01/02 00:00:00' (DATE)

   DEFAULT FOR ASG_SALARY                       IS 0

   DEFAULT FOR ASG_SALARY_BASIS                 IS 'NOT ENTERED'

   DEFAULT FOR E_DOUBLE_TIME_DISMANTLE_REDUCE_REGULAR      IS 'N'

   DEFAULT FOR USER_ENTERED_TIME                      IS 'N'

   DEFAULT FOR Timecard_Required                IS 'N'

   DEFAULT FOR LABOR_RECORDING_COUNT              IS 0

   DEFAULT FOR E_DOUBLE_TIME_DISMANTLE_ASG_GRE_RUN           IS 0

   DEFAULT FOR E_DOUBLE_TIME_DISMANTLE_ASG_GRE_YTD          IS 0

   DEFAULT FOR E_DOUBLE_TIME_DISMANTLE_HOURS_ASG_GRE_RUN    IS 0

   DEFAULT FOR Work_Schedule                    IS 'NOT ENTERED'

   DEFAULT FOR ASG_HOURS                        IS 0

   DEFAULT FOR Hours                            IS 0

   DEFAULT FOR Factor                         IS 1

   DEFAULT FOR Rate                             IS 0

   DEFAULT FOR Rate_Code                        IS 'NOT ENTERED'

   DEFAULT FOR ASG_FREQ                         IS 'NOT ENTERED'

   DEFAULT FOR CURRENT_ELEMENT_TYPE_ID          IS 0

   DEFAULT FOR DOUBLE_TIME_DISMANTLE_ACCRUED_ASG_ITD  IS 0

   DEFAULT FOR  MAXIMUM_AMOUNT                  IS 0

   DEFAULT FOR  DOUBLE_TIME_DISMANTLE_ASG_GRE_ITD         IS 0

   DEFAULT FOR  AUTHORIZATION_END_DATE IS '0001/01/01 00:00:00' (DATE)

   DEFAULT FOR  PRORATE_START IS '0001/01/01 00:00:00' (DATE)

   DEFAULT FOR  PRORATE_END IS '0001/01/01 00:00:00' (DATE)

   DEFAULT FOR ENTRY_DATE_EARNED                IS '0001/01/01 00:00:00' (DATE)

 

 

   DEFAULT FOR  REDUCE_REGULAR_EARNINGS_ASG_GRE_RUN   IS 0

   DEFAULT FOR  REDUCE_REGULAR_HOURS_ASG_GRE_RUN   IS 0

 

   DEFAULT FOR  REDUCE_REGULAR_EARNINGS_ASG_GRE_TD_BD_RUN   IS 0

   DEFAULT FOR  REDUCE_REGULAR_HOURS_ASG_GRE_TD_BD_RUN   IS 0

 

   DEFAULT FOR  NMA_Percentage       IS ' '

   DEFAULT FOR  Project_number       is ' '

   DEFAULT FOR  task_number          is ' '

   DEFAULT FOR  union                is ' '

   DEFAULT FOR  Job                  is ' '

   DEFAULT FOR  Shift                IS ' '

   DEFAULT FOR  Rate                 IS 0

  

   /* ===== Inputs Section Begin ===== */

   INPUTS ARE project_number,

              task_number,

              union,

              Job,

              Shift,

              NMA_percentage,

              hours,

              rate,

              Factor

  

   lv_amt      = 0

   lv_rate     = 0

   lv_hrs_wrkd = 0

   lv_hrs_paid = 0

   lv_nma_flag = ' '

   mesg        = ' '

   lv_shift    = 0

   lv_nma_pct  = 1

   lv_flag = 0

   lv_job_nm   = ' '

   lv_union_nm = ' '

  

  

  IF job was defaulted then

    lv_job_nm  = ' '

  ELSE

    lv_job_nm    = FUNC_PAY_ID_CONV('JOB',to_number(job))

   

  

  IF union was defaulted then

     lv_union_nm = ' '

  ELSE

     lv_union_nm  = FUNC_PAY_ID_CONV('UNION',to_number(union))

     

   

   

  IF shift was DEFAULTED THEN

     (lv_shift = 1)

  ELSE

     IF (FUNC_PAY_ID_CONV('SHIFT',to_number(shift)) = '1st Shift') THEN

        (lv_shift = 1)

     ELSE

       IF (FUNC_PAY_ID_CONV('SHIFT',to_number(shift)) = '2nd Shift') THEN

           (lv_shift = 2)

       ELSE

          IF (FUNC_PAY_ID_CONV('SHIFT',to_number(shift)) = '3rd Shift') THEN

             (lv_shift = 3)

 

    

   ELEMENT_TYPE_ID_PASSED = CURRENT_ELEMENT_TYPE_ID

  

   /* Calculation starts */

   /* Determine if the employee is working under NMA or under International Agreement */

 

 

   IF NMA_percentage WAS DEFAULTED THEN

     (lv_nma_flag = 'N'

      lv_nma_pct  = 1

     )

   ELSE

     (lv_nma_flag = 'Y'

      lv_nma_pct  = to_number(FUNC_PAY_ID_CONV('NMA',to_number(NMA_percentage)))/100

     )  

 

/* Rate Calculation */

 

IF (lv_nma_pct = 1 AND

    lv_shift = 1)

THEN

   (lv_rate = rate)

ELSE

(

   IF (lv_shift = 1)

    THEN

      (lv_rate = rate*lv_nma_pct)

    ELSE

     (

      IF (lv_shift = 2)

       THEN

         (lv_rate = ((rate*lv_nma_pct) + FUNC_PAY_NMA_SHIFT2))

       ELSE

        (

         IF (lv_shift = 3)

          THEN

            (lv_rate = ((rate*lv_nma_pct) + FUNC_PAY_NMA_SHIFT3))

         )

      )

)

/* Amount, hours worked and hours paid calculation */

 

IF factor WAS NOT DEFAULTED

THEN

 (lv_amt      = lv_rate * hours * factor

lv_hrs_paid = hours * factor

)

ELSE

(lv_amt      = lv_rate * hours

  lv_hrs_paid = hours

  )


/* Calculate hours worked and hours paid */


lv_hrs_wrkd = hours


/* Storing the earning calculated info for further accrual and deduction calculations */

lv_flag =FUNC_EARN_JRNL(lv_amt,lv_rate,lv_hrs_wrkd,lv_hrs_paid,lv_nma_flag,project_number,task_number,job,union,lv_nma_pct,lv_job_nm,lv_union_nm)

  

IF lv_flag = 0

THEN

  (mesg = 'Successfully calculated and inserted into stage table')

ELSE

  (mesg = 'Error in inserting into stage table')


/*===================== RETURN SECTION ===========================*/

RETURN

      lv_amt,

      lv_rate,

      ELEMENT_TYPE_ID_PASSED,

      lv_hrs_wrkd,

      lv_hrs_paid,

      lv_nma_flag,

      hours,

      project_number,

      task_number,

      union,

      Job,

      NMA_percentage,

      mesg,

      rate,

      factor

 

/* ===== Returns Section End ===== */