How To Migrate Fast Formulas From One Instance To Another Instance

How to Compile Payroll Fast Formula

 

 

Here is the simple way to compile your payroll fast formula; you can compile single formula or maybe you can compile all formulas using bulk concurrent program.

Single Formula uses FFXBCP program to compile fast formula, this is recommended by oracle. Please refer my old post migrating Payroll Fast Formulas.

-- To Compile One Fast Formula

-- Please make sure you have given correct formula name, their is a possibility to have multiple records with same formula name

-- To avoid selecting multiple records please pass correct formula id or name by changing cursor query

 

DECLARE

 

 CURSOR csr_formula_name IS

   SELECT ff.formula_name,

          ft.formula_type_name

     FROM ff_formulas_f ff,

          ff_formula_types ft

    WHERE ff.formula_type_id = ft.formula_type_id

      AND ff.formula_NAME = 'ANIL_NI_CALC2013';

 

   ln_req_id        NUMBER(10);

   --Please change below values based on your user, responsibility and application

   ln_user_id       NUMBER:=0;

   ln_resp_id       NUMBER:=51800;

   ln_resp_appl_id  NUMBER:=800;

BEGIN

 

     -- Initialize your application here

  FND_GLOBAL.APPS_INITIALIZE(ln_user_id,ln_resp_id,ln_resp_appl_id);

  -- Submit concurrent Request, it will compile all formulas

 

   FOR csr_formula_name_rec IN csr_formula_name

   LOOP

     l_req_id := fnd_request.submit_request(

                        application    => 'FF',

                        program        => 'SINGLECOMPILE',

                        argument1      => csr_formula_name_rec.formula_type_name,

                        argument2      => csr_formula_name_rec.formula_name);

   COMMIT;

   DBMS_OUTPUT.PUT_LINE('Request ID :' || ln_req_id);

   END LOOP;

  

  

END ;

 

--// This program is used to compile all formulas

DECLARE

 

   ln_req_id        NUMBER(10);

  --Please change below values based on your user, responsibility and application

   ln_user_id       NUMBER:=0;

   ln_resp_id       NUMBER:=51800;

   ln_resp_appl_id  NUMBER:=800;

BEGIN

 

  -- Initialize your application here

  FND_GLOBAL.APPS_INITIALIZE(ln_user_id,ln_resp_id,ln_resp_appl_id);

  -- Submit concurrent Request, it will compile all formulas

 

  ln_req_id := fnd_request.submit_request(

                    application    => 'FF',

                    program        => 'BULKCOMPILE',

                    argument1      => '-W',

                    argument2      => '%',

                    argument3      => '%');

 

   COMMIT;

   DBMS_OUTPUT.PUT_LINE('Request ID :' || ln_req_id);    

                 

END;

How to Migrate Payroll Fast Formula From one instance to other

<!--[if gte mso 9]> Anil Bejugam Blog Anil Bejugam 3 70 2014-04-25T11:17:00Z 2014-04-25T12:27:00Z 1 427 2437 20 5 2859 12.00 <![endif]

How To Migrate Fast Formulas From One Instance To Another Instance

-->

There are couples of ways to migrates Oracle Payroll fast formulas from one instance to other instance.  We will discuss here only oracle suggested approach.

Before going to that one should know structure of Fast Formula, and how it stored in Database updating Date tracking and corrections things

Actually there are two components in Fast formula

<![if !supportLists]>1)    <![endif]>Formula Definition

Formula definition is simple; it has Name, description, Formula Type, legislation code, Business group, effective Start date, and Effective end date

<![if !supportLists]>2)    <![endif]>Formulas Text (i.e. SQL or PL/SQL script written in text area to write the logic.)

Text is to control or validate formula and it has some series of pl/sql statements to validate and calculate actual value

Fast formulas are stored in FF_FORMULA_F table, formula text also stored in same table,  one can think what happens if I insert record directly into this table, yes you can insert but after inserting you have compile the formula then only it works., but I am not sure this method and oracle also not recommends this method. So don’t try this

Date track mode

If you update record as date track mode with new effective date, it internally creates new record with same formula_id and end date old record one day before new effective date, so for each date track mode there will be one record in FF_FORMULA_F table

Correction Mode

Whenever you update fast formula in correction mode, it will be update FF_FORMULA_F table even though there is change in effective date and effective end date

How to migrate from one instance to other

<![if !supportLists]>1)   <![endif]>Copy the original formula text to a text file and name it Anil_Formula.txt for example

<![if !supportLists]>2)   <![endif]>In another text file on two lines

update ff_formulas_f set formula_text = :formula where formula_name = 'TEST_FORMULA' and effective_start_date = to_date('19-09-2008', 'DD/MM/YYYY')
LOCAL Anil_Formula.txt

Save the file as Anil_Formula.ctl

If it is new formula or date track mode then write insert script, if it is correction mode then write update command

For Date track mode always use same formula_ID, formula_name, type, and business group Id and legislation code

Note: Write update command in one line only

<![if !supportLists]>3)   <![endif]>In UNIX , in the same directory where .txt and .ctl are located

Run the following commands

$FF_TOP/bin/FFXMLC apps/apps 0 Y LOCAL Anil_Formula.ctl

* This command will upload the formula .
* Check the log file generated for any errors.

Now run the following command to compile the formula

$FF_TOP/bin/FFXBCP apps/apps 0 Y %% 'TEST_FORMULA'

* Check the log file generated for any errors.

<![if !supportLists]>4)   <![endif]>Query the formula in Write Formula screen and verify that Formula is uploaded and compiled
properly.

<![if !supportLists]>5)   <![endif]>Try this in a test instance, and when satisfied with the methodology you may be able to automate
it a bit more to make it more streamlined.