Debugging Fast Formulae – ISOLATING FORMULA FROM THE CALLING APPLICATION

Posted on by

Sometimes when debugging formulae it is convenient to isolate the formula and run it separately. It can also be useful to analyze the PL/SQL code that is generated when a formula is compiled. This section discusses these topics.

Calling Formula from PL/SQL

Sometimes when debugging formulae it is convenient to isolate the formula and
run it separately. This can be achieved using the function defined in Appendix A – Formula Test Function.

This PL/SQL function accepts the FORMULA_ID and the values of any formula
contexts that are needed. The example formula above would be executed in

PL/SQL using the following statement:

Begin
  ExecuteFF(p_formula_id => 63208
  ,p_assignment_id => 12167
  ,p_date_earned => sysdate);
end;

 

Determining the contexts

In order to be able to use this technique it is necessary to know the correct context types that need to be passed into the formula, the following SQL provides this information:

select fdi.item_name
from ff_fdi_usages_f fdi
where fdi.formula_id = {formula Id}
and fdi.usage = ‘U’;

In order to replicate the behaviour of the formula in the calling application, it will
be necessary to pass in the same context values as used in the calling application.
In Appendix B there is a formula function named GetContext. This can be added
to the formula in the calling application to provide PYUPIP trace output that
includes the contexts and their values. Alternatively the FND_LOGGING
described above can also be used to determine this information.

 

Debugging Formula Using the Compiled Package
There are 2 types of PL/SQL package that are generated from Fast Formula, one
is generated when the formula is compiled the form or the FFXBCP command,
the other is generated when the Generate Formula Wrapper Formula concurrent
process is used.

Formula Execution Package
When a formula is compiled either through the form or the FFXBCP command a
PL/SQL package is generated that is used when a formula is executed from
PL/SQL. Its name will be made up as follows:
FFP + Formula ID + _ + DDMMYYYY e.g. FFP63208_17052005

If however a compilation error occurs in the Formula Definition form the package will not be created in the database. In order to generate the package for further analysis the following command needs to be used (N.B. the –k switch keeps the PL/SQL package even if errors are detected):

FFXBCP username/pwd@db 0 Y –k %% FormulaName

An example formula and its corresponding PL/SQL package is show in Appendix
C – Example PL/SQL package including pointers to the various sections of code
and how they relate the formula being generated.
If a formula compilation error occurs which cannot be resolved in the Formula
Definition form then analyzing the PL/SQL formula execution package can help
to diagnose the problem. Useful techniques include the following :

• Adding further trace statements
• Extracting individual SQL statements that have been generated to derive the values of any database items
• Isolating function calls for closer scrutiny.