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.

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 ===== */