Home 
Technical Articles 
Training Articles 
Receive Email for New Articles 
Contributors 
Apps Book 
PL/SQL from Fast Formula   Print  
Written by Anil Passi  
Tuesday, 30 January 2007  
Lets say you already have a PL/SQL function written for Oracle Payroll. Lets further assume this PL/SQL function has a parameter named assignment_id, and this function returns a Cost Of Living Amount for an individual. If this is PL/SQLfunction based on complex calculations such as Employee's post code/City & number of dependents in family etc, then one will struggle to implement the calculation logic neatly within fast formula. Hence Oracle Payroll provides a user feature that allows us to call PL/SQL Function from Fast Formula. This feature has been available in Oracle Payroll for over a decade. Does this approach make Payroll Fast Formula run slower? Not really. In fact, Oracle stores every fast formula as a PL/SQL package. This happens when you compile a Payroll Fast Formula from Fast Formula definition screen. If you want to see the list of thosepackages, then run the below SQL SELECT object_type, object_name, status, last_ddl_time FROM all_objects WHERE owner = 'APPS' AND object_type LIKE 'PACKA%%' AND object_name LIKE 'FFP_%' OR object_name LIKE 'FFW_%' ORDER BY 1, 2 / What are the steps for using a PL/SQL in Oracle Payroll Fast Formula? Step 1: Navigate to Payroll[or HRMS] Manager/Superuser responsibility. Open Formula Function screen as below. Register your pl/sql as below in the screen. Step 2. Register the parameters of PL/SQL in Function in Formula definition screen There are two types of parameters Context parameters [see the context usages button above] To register these parameters, click on button labeled Context Usages. In our example, we are selecting two contexts, i.e. Business Group Id and Assignment Id. These are the parameters[as defined below] that will be internally passed to pl/sql function by Oracle Payroll engine. Think of these as errbuff and retcode, though not literally. Oracle provides a predefined list of contexts, you will have to pick one of those from LOV. To find the complete list, run the SQL below, alternately you can see the list from the Screen LOV itself. SELECT context_id ,context_name ,data_type "data_type_code" ,fl.meaning "data_type" FROM ff_contexts, hr_lookups fl WHERE fl.lookup_code = data_type AND fl.lookup_type = 'DATA_TYPE' ORDER BY context_name Register User defined parameters Click on Parameters window in Formula Functions screen. Usually you will pass Fast Formula variables to this parameter. Step 3 Call this from fast formula Within the fast formula, you will mention the named of the function as registered in the above Screen from Step 1. In order to make a call to our PL/SQL, within fast formula simply do XX_GET_ALLOWANCE(variable_for_effective_date_here) Note: We are simply passing the User Defined Parameter here. Oracle Payroll will internally pass the first two parameters. Step 4. Define your pl/sql function within package or standalone[this could have been the Step 1] CREATE OR REPLACE PACKAGE BODY xx_ff_functions AS    XX_LONDON_ALLOWANCE  get london allowance for the given person  for a given date   Input : p_effective_date : The effective date for allowance must be calculated   Output : None   Return : Number : Allowance amount value as at effective date   FUNCTION xx_london_allowance firstly define the parameters for the contect (p_business_group_id IN NUMBER ,p_assignment_id IN NUMBER ,p_allowance_date IN DATE) RETURN NUMBER IS v_allowance NUMBER; BEGIN your sql statements, pl/sql here EXCEPTION WHEN no_data_found THEN v_allowance := 0; WHEN OTHERS THEN RETURN  1; END; RETURN v_allowance; END xx_london_allowance; END xx_ff_functions; Can we do the reverse too, I.e. Call a fast formula from pl/sql? Indeed. I will try to cover that in latter article with some example. Comments
(7)
...
written by Kim , February 07, 2007
...
written by ruhulla , April 30, 2007
Hi,
i am trying to user tables...within fast formula.......which had rows about 2500rows.......but now i am trying to have more number rows which might be around 80000...do you have nay idea on how this may affect the payroll porcessing time,,,,,,,,,,,,
...
written by Sachin Jain , August 30, 2007
Hi Anil,
While writing fast formula for getting the inout values of that element we need to write "Inputs Are" and then list of input values. In some formula I have seen prorate_start is given in the section "Input Are". Eventhough it is not a input value for that element. Someone suggest me the value is coming from Oracle Engine. It stores these value. If this is correct where/how can I find the list of these variables. Thanks & regards Sachin Jain
To load Accrual Balances In Payroll
written by Hariom Pandey , October 03, 2007
Hi Anil,
Do you have any script or material which can help me to load Leave Balances to Oracle Payroll. I need to load Annual Leave,Sick Leave etc. Any suggetion which can help me on this. Thanks in advance. Regards Hari.
Formula returning a Parameter type mismatch
written by pratyush , December 04, 2007
Hi,
I am writing a formula for the PTO Accrual and created a user defined function (PL/SQL function) and calling that PL/SQL function from a Fast Formula Function already defined as you indicated above. When I am calling the Fast Formula Function from the Formula, its throwing a parameter type mismatch. I have 2 contexts and 2 parameters defined in the Fast Formula and 4 parameters in the PL/SQL Function. Could you please help me in this. I am stuck and I was following your instruction above only which looked very useful
fast formulas
written by PRATAP , January 29, 2008
hi,
The article was good but when defining the formula how we will call this function and how to pass the context and manuall parameters in formula.if anything is there it will be helpful to me. I defined function and i had given all r normal parameters that the formula function is working complinig the succesfull but when i give the context parameters that formula fails.
Facing error while running oracle progression job (using fast formulas)
written by sourabh porwal , February 08, 2008
Hi
i am Facing following error while running oracle progression job (using fast formulas) FFX22J_FORMULA_NOT_FOUND A compiled version of the formula 1817 cannot be found. Check that it exists and has been compiled before attempting to run it. Please help You must be logged in to a comment. Please register if you do not have an account yet.

A nice article.But i was looking for the other way i.e calling fast formula from pl/sql.I did try to do it so many times but as i am new to plsql i always stuck with lots of error.Also if you can mention some business requirements where we need to execute fast formula from plsql.
Regards
Kim