<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content

Green

color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
This website has now moved to http://apps2fusion.com





PL/SQL from Fast Formula | Print |  E-mail
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 pre-defined 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)add
...
written by Kim , February 07, 2007
Hi
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
...
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.

busy