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 * DE = A /15
We
should write:
E = (C * D) / 15· Aliases always help. Although it increases in readability, there is no impact on Performance.
Examples of a Formula
/*===============================================================================FORMULA TYPE:
Participation and Rate EligibilityFORMULA 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 DEFAULTEDTHEN (lv_amt = lv_rate *
hours * factorlv_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 = 0THEN (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 ===== */
No comments :
Post a Comment