Formulas are generic expressions of calculations or comparisons you want to repeat with different input values. Formulas take input from a window, a database, or a process, such as a payroll run and they return values or messages.
Oracle FastFormula is a simple way to write formulas using English words and basic mathematical functions. You can use information from your database in formulas without learning the database structure or a programming language.
In HRMS, Oracle FastFormula is used for validation, to perform calculations, and to specify rules. Here are some examples.
In Payroll, you use formulas to:
Validate element inputs
Calculate element pay values and run results during payroll processing
Specify the rules for skipping an element during payroll processing
Perform legislative checks during a payroll run
In Compensation and Benefits Management, you use formulas to:
Specify the rules for Paid Time Off accrual plans, such as how much time is accrued and over what period, when new starters are eligible to begin accruing time, and how much time can be carried over to the next accrual term
Define custom calculations for benefits administration
Calculate the duration of an absence given the start and end dates and times
Create rules for benefits administration such as eligibility determination
In People Management, you use formulas to:
Check that element entry values are valid for an assignment
Specify the criteria for including an assignment in an assignment set and to edit assignment sets
Configure the people management templates in a number of ways such as supplying additional information to be available from fields on the template and validating field entries
Define collective agreements
Generate custom global person number sequences for employees, applicants, and contingent workers
You also use formulas to define Oracle Business Intelligence Systems reports, to select the database information you want to display in a QuickPaint report, and to perform calculations for the report.
You can easily create and maintain business rules by calling formulas from the Custom Library. For example, you can use formulas to validate data entry in forms by calling formulas from the Custom Library, and to check that entries made to a user table are valid. You can then call these business rules from other PL/SQL applications.
When you write a formula, you specify for which of these purposes you will use it.
Formulas are made up of a number of different components. These can include assignment statements, different types of input including database items, functions, nested expressions, and conditions. See: Introduction to Formula Components, Oracle HRMS FastFormula User Guide
When writing formulas, there are a number of techniques you should use to ensure your formulas are easy to use and understand. See: Formula Writing Techniques. There are also rules for using each type of component that you need to follow. See: Formula Reference, Oracle HRMS FastFormula User Guide
Oracle FastFormula provides functions that manipulate data in various ways. These include:
Text functions such as the GREATEST function that compares the values of all the text string operands and returns the value of the operand that is alphabetically last
Numeric functions such as the ROUND function that rounds off a numeric value to the specified number of decimal places
Date functions such as the ADD_DAYS function that adds a number of days to a date
Data conversion functions such as the CONVERT function that converts a character string from one character set to another
Other types of functions provided by Oracle FastFormula include functions:
To get values from tables such as the GET_LOOKUP_MEANING function that enables Oracle FastFormula to translate a lookup code into a meaning
For accrual type formulas such as the GET_START_DATE function that returns the date at which the accrual formula should start calculating accruals
That allow you to call another formula, either once or in a loop such as the CALL_FORMULA function that runs a named formula with no inputs and no outputs
To set and use globals in SQL*Plus from within your formulas such as the ISNULL set of three functions that test whether a text, numeric, or date value is NULL
See: Functions, Oracle HRMS FastFormula User Guide
Formulas are predefined for all the tax calculations required for Oracle Payroll. You should not edit these formulas. When tax rules change, you will automatically receive updates.
There are also a number of formulas predefined for accrual plans. You can use these formulas as supplied, or customize them to match the rules of your own plans.
Depending on the legislation, the system may automatically generate some payroll formulas to define earnings and deductions elements, and you can create as many other formulas as you require to process the elements you define.
Formulas for QuickPaint reports and assignment sets can be generated from criteria you enter in windows. You can edit these generated formulas to add more functionality.
There are two types of database items available to you in Oracle HRMS for writing formulas and defining QuickPaint reports. Static database items come as part of the system and you cannot modify them. Dynamic database items are created by Oracle HRMS processes whenever you define new elements or other related entities. See: Database Items, Oracle HRMS FastFormula User Guide
To use the FastFormula Assistant, you must ensure the FastFormula Transaction Manager concurrent manager is running on your environment. A system administrator can setup the menus, add the functions and activate the concurrent manager. See: Defining Concurrent Managers, Oracle E-Business Suite System Administrator's Guide - Configuration
You can use Oracle FastFormula to:
Calculate your payrolls
Define the rules for PTO accrual plans
Define custom calculations for benefits administration
Define QuickPaint reports
Validate element inputs or user tables
Edit assignment sets
Calculate absence duration
Configure people management templates
Set up business rules and call them from other PL/SQL applications
Define your Oracle Business Intelligence Systems reports
Define collective agreements
Define custom global person number sequences
Define employment categories for EEO reports (US only)
Calculate ratings for individual competencies and objectives, and calculate a total score for an appraisal
You can use predefined payroll formulas. When you receive Oracle Payroll, some formulas are predefined in your system. You cannot make any changes to these formulas, but you can copy them and modify the copies.
US and Canadian Payroll users can use Oracle FastFormula to edit the formulas the system generates for the earnings types, other payments and deductions you initiate in Oracle Payroll. You make these edits directly to the generated formula (not to a copy) using the Formula window.
All Oracle Payroll users can use FastFormula to write payroll calculations and skip rules for elements you define yourself to represent earnings and deductions. You can associate more than one formula with each element, to perform different processing for employee assignments with different statuses. US and Canadian Payroll users need only define their own elements and formulas for earnings and deductions with highly complex calculations requiring a number of different calls to the database.
You can write Payroll Run Proration formulas to run after the usual payroll formula and handle proration when employees start work or terminate mid-pay period, or when rates, grades, or other values change, requiring an element to be prorated.
You can use Oracle FastFormula to edit the seeded Accrual type formulas, or to write your own. Each accrual plan needs two formulas: one to calculate gross accrual, and the other to return information to the PTO Carry Over process. You can optionally create a third formula if you want to use the Batch Element Entry (BEE) to make absence entries against accrual plans. This formula is called by BEE to check whether an employee is eligible to use accrued PTO.
See: Accrual Formulas, Oracle HRMS Compensation and Benefits Management Guide
You can use Oracle FastFormula to augment the system's benefits administration features. Use formulas to configure your plan design to the requirements of your enterprise. For example, you can write a formula to calculate benefits eligibility for those cases where eligibility determination is most complex.
In the Define QuickPaint Report window, you can paste database items from a list into the Report Definition area and enter free text to label the data. When you save your QuickPaint Report definition, a formula is generated automatically. Formulas generated from QuickPaint do not include conditional logic or calculations. You may want to add these features, for example to sum up compensation elements or to report different data for different assignments.
You can use Oracle FastFormula to validate user entries into element input values using lookups or maximum and minimum values. However, if you need more complex validation, you can write a formula to check the entry.
You can also use a formula to validate entries into user tables that you define.
When you define assignment sets in the Assignment Set window, Oracle FastFormula generates a formula to define an assignment set from the criteria entered. However, you may want to change the sequence in which the set criteria are checked for each assignment.
You can write a formula to calculate the duration of an absence from the start and end dates and times. Your localization team may have created a default formula, but you can override this with your own version.
There are several ways you can use formulas to configure the people management templates:
A Template Validation formula can check values entered in a field.
A Template Information formula can specify information to be displayed from the right mouse menu when a user right-clicks in a field.
A QuickPaint formula can return a text string to display in the Assignment field on the Maintenance window and in the Data Organizer.
A QuickPaint formula can return message tokens that you can use in a notification message issued from template forms.
You can call formulas from PL/SQL applications. This enables direct access to data items and makes it possible to develop custom code for localized business rules.
More detailed information about calling FastFormula from PL/SQL can be found in the technical essay Calling FastFormula from PL/SQL, Oracle HRMS Implementation Guide.
Using formulas you can configure your HRMS BIS reports so that they answer the business questions which are important to your enterprise. You can:
Define how workforce should be counted within your enterprise for the Workforce reports.
Set up information about how you want to record and report on regular and overtime hours for the Hours Worked Analysis report.
Set up the standard hours for your enterprise for the Absence Analysis report.
Using formulas you can calculate whether a person is eligible to receive a collective agreement entitlement. This can be used when defining an eligibility profile to be used in conjunction with a collective agreement, instead of selecting criteria elements. You select the formula as a rule when defining the eligibility profile.
When you select automatic local numbering for employees, applicants, or contingent workers, Oracle HRMS allocates person numbers from a sequence that is specific to the business group. When you select global numbering, Oracle HRMS allocates person numbers from a single sequence to workers of the relevant type throughout the enterprise.
You can replace the default local or global person number sequence with a custom global sequence by defining a formula for each person type, as appropriate. You may want to use a formula to provide an alphanumeric numbering scheme, for example, or some other variation of the default scheme for any or all person types. For example, you could use a custom global sequence for employees but use the default local or global sequence for applicants and contingent workers. Alternatively, you could use a custom sequence for all three person types by defining three formulas.
For the EEO4 and EEO5 reports you can use Oracle FastFormula to create a formula of employment categories. You can define a formula for each business group and the EEO reports will pick the list of employment categories from the formula.
You can use Oracle FastFormula to:
Calculate a rating for each competency in a worker's appraisal.
Calculate a rating for each objective in a worker's appraisal.
Calculate a total score for the appraisal based on the final scores for competencies and objectives.
See: Writing Formulas for Rating Competencies and Objectives, Oracle HRMS FastFormula User Guide
When writing formulas there are a number of techniques you should follow to ensure your formulas are easy to read, use and understand.
It is good practice to include comments in your formulas to explain to other people what the formula does.
So, for example, you can name your formula:
/* Formula: Attendance Bonus */
and write an explanation of your formula:
/* Use this formula to calculate the annual bonus for
clerical staff. Employees receive either a percentage of
their salary (if they have been present for 183 or more
days in the last six months), or a pro rata bonus (if they
have been in attendance for less than 183 days in the last
six months). */
Oracle FastFormula ignores everything between the comment delimiters: /* and */. You can place comments anywhere in a formula without affecting the formula's performance.
Caution: Do not put a comment within a comment. This causes Oracle FastFormula to return a syntax error.
You can use a comment to explain what part of your formula does. So, for example, you might want a comment explaining who decides the bonus percentage:
INPUTS ARE salary_amount,
start_date (date),
end_period_date (date),
bonus_percentage /* decided at board level */
You can also use comments to `comment out' parts of the formula you do not currently want to use. So, for example, you can include a fifth input of employee_status, ensuring that employees with a status of full time are awarded a bonus. However, as you do not yet have a range of statuses, you do not currently need the fifth input.
INPUTS ARE salary_amount,
start_date (date),
end_period_date (date),
bonus_percentage /* decided at board level */
/* employee_status (text) */
Use comments and white space freely when entering formulas. This makes the formulas easier to read and understand, and has no effect on performance or memory usage. Use indentation for the same reason, especially when you are using brackets to control the order of processing.
It is good practice to include the following information in a comment at the beginning of a formula:
Formula title and short statement of its purpose
Description of formula inputs
List of variables and constants that may require updating
Description of the input values of the element that receives the formula's direct result
Explanation of the formula's calculations
Administrative information such as the name, address and telephone number of an office administering the earnings, deduction, or charge the formula affects
The dates of formula modifications, the names of those entering the edits, and possibly the reasons for change
Database items are named by the system when it creates them, and sometimes these names are too long to conveniently use in a formula. You cannot shorten the name of a database item (or a global value) itself, but you can set up an alternative shorter name to use within the formula. For example:
ALIAS as_overtime_qualifying_length_of_service AS ot_qls
In the rest of the formula, you can use the alias (in this example, ot_qls) as if it were the actual variable.
Important: Using an Alias is more efficient than assigning the database item to a local variable with a short name.
Use the Default statement to set a default value for an input value or a database item. The formula uses the default value if the database item is empty or no input value is provided when you run the formula. For example:
DEFAULT FOR hourly_rate IS 3.00
X = hours_worked * hourly_rate
IF hourly_rate WAS DEFAULTED
THEN
MESG = 'Warning: hourly rate defaulted'
This example sets a default of 3.00 for the database item hourly_rate. If hourly_rate is empty (NULL) in the database, the formula uses the default value of 3.00. The formula uses the 'WAS DEFAULTED' test to detect when a default value is used, in which case it issues a warning message.
Important: You must use the Default statement for database items that can be empty. The Database Items window includes a check box labelled Default Required. This check box is checked for database items that can be empty. The Database Items window appears when you choose the Show Items button on the Formulas window.
The following guidelines are generally true for typical payroll runs:
The longer an element's formula, the longer its processing time.
The more elements entered for an assignment, the longer its processing time.
One element associated with a lengthy formula usually processes faster than two related elements each associated with a short formula.
The overall number of elements and formulas in the system has little effect on processing efficiency. It is the number of elements per assignment that affects processing time.
To improve readability use names that are brief yet meaningful. Name length has no effect on performance or memory usage. Use Aliases if the names of database items or global values are long.
Use Input statements rather than database items whenever possible. This improves formula processing by as much as a factor of ten. It speeds up the running of your payroll by eliminating the need to access the database for the input values.
Inefficient:
Salary = Salary_annual_salary / 12
RETURN Salary
Efficient:
INPUTS ARE Annual_salary
Salary = Annual_salary / 12
RETURN Salary
Use the TO_DATE function only when the operand is a variable.
Inefficient:
Start_date = TO_DATE ( '1992-01-12 00:00:00' )
Efficient:
Start_date = '1992-01-12 00:00:00' (date)
Use a single expression in straightforward formulas where this does not lead to confusion.
Inefficient:
Temp = Salary / Annualizing_factor
Tax = Temp * 3
Efficient:
Tax = (Salary / Annualizing_factor) * 3
Do not refer to database items until you need them. People sometimes list at the top of a formula all the database items the formula might need, thinking this helps Oracle FastFormula process more quickly. However, this in fact slows processing by causing unnecessary database calls.
Inefficient:
S = Salary
A = Age
IF S < 20000 THEN
IF A < 20 THEN
Training_allowance = 30
ELSE
Training_allowance = 0
Efficient:
IF Salary < 20000 THEN
IF Age < 20 THEN
Training_allowance = 30
ELSE
Training_allowance = 0
The first example always causes a database fetch for Age whereas the second only fetches Age if Salary is less than 20000.
Wherever possible, use balance dimensions for single assignments only in formulas. Multiple assignments require more calculation, leading to slower processing time. The number of genuine multiple assignments in a payroll is not normally high, and the presence of a small number does not lead to any significant increase in overall processing time. There could be a problem, however, if you unnecessarily link balance dimensions for multiple assignments into general formulas.
You set up proration formulas to enable element values to be calculated accurately if they change during a payroll period, for example, if an employee leaves the company or if their pay rate changes.
For more detailed information on proration, see the Technical Essay entitled Proration available on My Oracle Support.
Oracle provides a predefined legislative formula KR_VALIDATE_YEA_DATA that you can use as a template to write a custom formula to validate YEA information for each business group. The formula lists employee information that you can use as input for the custom formula. If you require more information, you must write formula functions to query the saved data from the PER_KR_ASSIGNMENT_YEA_INFO table.
If you do not want to define the formula VALIDATE_YEA_DATA, then the application skips the custom validation step.
When you write the custom formula, you must observe the following rules:
Name the formula as VALIDATE_YEA_DATA
Ensure that the custom formula returns an output parameter named STATUS with value VALID if data passes the custom validation. If the formula returns any other value, then the validation fails
The custom formula can have a maximum of 10 error messages as output variables to display on the data entry pages, besides STATUS as the output
In the custom formula, you can use database items that use only the following contexts:
PERSON_ID
ASSIGNMENT_ID
DATE_EARNED
Formula Input
The YEA Information Entry passes the following parameters to the formula that you can choose or not, as inputs in the formula. If you want to use these parameters, then the INPUT statement within the custom formula must include the desired parameter with the same name as given in the following table:
| PARAMETER NAME | DESCRIPTION |
|---|---|
| Special Tax Exemption | |
| EE_EDUC_EXP | Employee Education Expense |
| HOUSING_SAVING_TYPE | Housing Saving Type |
| HOUSING_SAVING | Housing Saving |
| HOUSING_PURCHASE_DATE | Housing Purchase Date |
| HOUSING_LOAN_DATE | Housing Loan Date |
| HOUSING_LOAN_REPAY | Housing Loan Repay Longterm Housing Loan Date due |
| LT_HOUSING_LOAN_DATE | Long term Housing Loan Date due less than 15 Years |
| LT_HOUSING_LOAN_INTEREST_REPAY | Long term Housing Loan Interest Repay due less than 15 Years |
| DONATION1 | Statutory (100%) |
| POLITICAL_DONATION1 | Political Since 2004.3.12 |
| HI_PREM | Health Insurance Premium |
| POLITICAL_DONATION2 | Political Before 2004.3.12 |
| POLITICAL_DONATION3 | Political3 (Obsolete) |
| DONATION2 | Specified (10% Limit) |
| DONATION3 | Special (50% Limit) |
| MED_EXP_EMP | Medical Expense for Employee |
| LT_HOUSING_LOAN_DATE_1 | Long term Housing Loan Date due greater than 15 Yrs |
| LT_HOUSING_LOAN_INT_REPAY_1 | Long term Housing Loan Interest Repay due greater than 15 years |
| MFR_MARRIAGE_OCCASIONS | Marriage Exemption |
| MFR_FUNERAL_OCCASIONS | Funeral Exemption |
| MFR_RELOCATION_OCCASIONS | Relocation Exemption |
| EI_PREM | Employment Insurance Premium |
| ESOA_DONATION | ESOA (30% Limit) |
| PERS_INS_NAME | Personal Insurance Name |
| PERS_INS_PREM | Personal Insurance Premium |
| DISABLED_INS_PREM | Disabled Insurance Premium |
| MED_EXP | Medical Expense |
| MED_EXP_DISABLED | Medical Expense for Disabled |
| MED_EXP_AGED | Medical Expense for Aged |
| EE_OCCUPATION_EDUC_EXP | Employee Occupational Education Expense |
| Foreign Worker Tax Break | |
| IMMIGRATION_PURPOSE | Immigration Purpose |
| CONTRACT_DATE | Contract Date |
| STAX_APPLICABLE_FLAG | Special Tax Applicable |
| FWTB_APPLICATION_DATE | Application Date |
| FWTB_SUBMISSION_DATE | Submission Date |
| Overseas Tax Break | |
| TAX_PAID_DATE | Tax Paid Date |
| OTB_SUBMISSION_DATE | Submission Date |
| KR_OVS_LOCATION | Location Overseas |
| KR_OVS_WORK_PERIOD | Overseas Work Period |
| KR_OVS_RESPONSIBILITY | Responsibility of Overseas Work |
| TERRITORY_CODE | Country |
| CURRENCY_CODE | Currency |
| TAXABLE | Taxable Earnings |
| TAXABLE_SUBJ_TAX_BREAK | Taxable Earnings subject to Tax Break |
| TAX_BREAK_RATE | Tax Break Rate |
| TAX_FOREIGN_CURRENCY | Tax (Foreign Currency) |
| TAX | Tax (KRW) |
| OTB_APPLICATION_DATE | Application Date |
| Tax Break | |
| HOUSING_LOAN_INTEREST_REPAY | Housing Loan Interest Repay |
| STOCK_SAVING | Stock Saving |
| LT_STOCK_SAVING1 | Long term Stock Saving (1st Year) |
| LT_STOCK_SAVING2 | Long term Stock Saving (2nd Year) |
| Other Tax Exemptions | |
| DIRECT_CARD_EXP | Direct Payment for Employee |
| DPNT_DIRECT_EXP | Direct Payment for Spouse and Dependent |
| GIRO_TUITION_PAID_EXP | Tuition Paid in GIRO |
| CASH_RECEIPT_EXP | Cash Receipt Expenses |
| KR_MED_EXP_PAID_IN_CARD | Medical Expense Paid in card, direct payment, cash receipt |
| NP_PREM | National Pension Premium |
| PERS_PENSION_PREM | Personal Pension Premium |
| PERS_PENSION_SAVING | Personal Pension Saving |
| INVEST_PARTNERSHIP_FIN1 | Investment Partnership Financing Type1 |
| INVEST_PARTNERSHIP_FIN2 | Investment Partnership Financing Type2 |
| CREDIT_CARD_EXP | Credit Card for Employee |
| EMP_STOCK_OWN_PLAN_CONTRI | Employee Stock Ownership Plan Contribution |
| CREDIT_CARD_EXP_DPNT | Credit Card for Spouse and Dependents |
You can query these values from the table PER_KR_ASSIGNMENT_YEA_INFO. If you need any information other than the parameters specified above, then query the values from the table PER_KR_ASSIGNMENT_YEA_INFO.
Formula Outputs
If you pass an output variable STATUS in the RETURN statement of the formula and if value of STATUS is VALID, then the employee's YEA information is valid. Any other output value indicates that employee's YEA information is invalid.
You can return any error messages to display on incorrect data entry on the YEA Information Entry pages. You can pass up to a maximum of 10 error messages. This is an optional feature.
Oracle HRMS enables you to calculate the lump sum amount while processing separation pension for employees.
Oracle may change this formula in future releases. This formula is strictly for example or prototype uses only, and is not intended to provide a ready-made solution. You can make a copy of this formula but you should not change your copied version. Always write a formula of your own to meet your own requirements. This formula may contain certain hard-coded values for simplified use.
Oracle HRMS provides the sample formula KR_SEPARATION_PENSION that you can use to calculate the Separation Pension Lump Sum Amount and Separation Pension Lump Sum Non Taxable Amount. You can attach this formula to the Separation Pension Details element as a Standard processing rule. This creates indirect results for separation lump sum amount and non-taxable amount.
If you want to create your own formula, ensure to include the following validation logic. The payroll run will terminate with error message if any of the validations fail:
Principal and Interest is greater than 0
Total Received is greater than or equal to 0
Personal Contribution is greater than Pension Exemption
Principal and interest is greater than Personal Contribution
Lump sum amount is calculated only for Defined Contribution
Note: Formula Results are not predefined for this formula. If you decide to use this formula, you must create formula results at business group level as an implementation step.
Use this formula for calculating Lump Sum Amount then follow the steps below:
1. Attach this formula to the seeded element: "Separation Pension Details"
as "Standard" Processing Rule using Formula Result Form.
2. Create Formula Results for the return values as mentioned below:
o_lump_sum_amount -> Indirect Result to the seeded element
Separation Pension Lump Sum Amount
o_non_taxable_amount -> Indirect Result to the seeded element
Separation Pension Lump Sum Non Taxable Amount
o_incorrect_defined_type -> Fatal Error Message
o_incorrect_total_received -> Fatal Error Message
o_incorrect_prn_and_interest -> Fatal Error Message
o_incorrect_pension_exem -> Fatal Error Message
o_incorrect_pers_contribution -> Fatal Error Message
*************************************************************************/
default for PERSONAL_CONTRIBUTION is 0
default for PENSION_EXEMPTION is 0
default for TOTAL_RECEIVED is 0
default for PRINCIPAL_AND_INTEREST is 0
default for SEPARATION_PENSION_ACCOUNT_DETAILS_DEFINED_TYPE_ENTRY_VALUE is 'NA'
INPUTS ARE
TOTAL_RECEIVED (number),
PRINCIPAL_AND_INTEREST (number),
PERSONAL_CONTRIBUTION (number),
PENSION_EXEMPTION (number)
o_lump_sum_amount = 0
l_valid = 'Y'
/************ Validation ************/
if TOTAL_RECEIVED < 0 then (
l_dummy = set_message_name('PAY', 'PAY_KR_SEP_PEN_INV_TOTAL_RCVD')
o_incorrect_total_received = get_message()
l_valid = 'N'
)
if PRINCIPAL_AND_INTEREST <= 0 then(
l_dummy = set_message_name('PAY', 'PAY_KR_SEP_PEN_INV_PRINCPL_INT')
o_incorrect_prn_and_interest = get_message()
l_valid = 'N'
)
if PENSION_EXEMPTION > PERSONAL_CONTRIBUTION then(
l_dummy = set_message_name('PAY', 'PAY_KR_SEP_PEN_INV_PEN_EXEM')
o_incorrect_pension_exem = get_message()
l_valid = 'N'
)
if PERSONAL_CONTRIBUTION > PRINCIPAL_AND_INTEREST then (
l_dummy = set_message_name('PAY', 'PAY_KR_SEP_PEN_INV_PERS_CONTRI')
o_incorrect_pers_contribution = get_message()
l_valid = 'N'
)
if SEPARATION_PENSION_ACCOUNT_DETAILS_DEFINED_TYPE_ENTRY_VALUE <> 'DC' then(
l_dummy = set_message_name('PAY', 'PAY_KR_SEP_PEN_INV_DEF_TYPE')
o_incorrect_defined_type = get_message()
l_valid = 'N'
)
/********* end of validation *********/
if l_valid = 'Y' then(
o_lump_sum_amount = TOTAL_RECEIVED *
( 1 - (PERSONAL_CONTRIBUTION - PENSION_EXEMPTION)/PRINCIPAL_AND_INTEREST )
o_lump_sum_amount = greatest(0,trunc(o_lump_sum_amount))
o_non_taxable_amount = TOTAL_RECEIVED - o_lump_sum_amount
)
RETURN
o_lump_sum_amount,
o_non_taxable_amount,
o_incorrect_total_received,
o_incorrect_prn_and_interest,
o_incorrect_pension_exem,
o_incorrect_pers_contribution,
o_incorrect_defined_type