Apps To Fusion

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_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.

< Prev		Next >