Oracle Payroll

Calling PL/SQL from Fast Formula

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/SQL function 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 those packages, 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.

Anil Passi

Add comment