Query: List of all APIs in Oracle Applications R12.1.3

I was building APIs for a recent HRMS migration project and was searching all over iRep for the list of APIs, mandatory parameters, data types to actually build my mapping repository. It is a long and time consuming process, this project covers loads of standard as well as extensible migration entities using SIT / EITs & UDTs. So I built this script to find out all the available APIs in Oracle Applications R12.1.3, the below script is limited to HR but can be extended to other modules. I made this consideration as the excels become too huge to be maintained.

The below script would list down all HRMS APIs of PLSQL type and their description, which is very useful for your interface validations and to understand the use of this API.

SELECT   ff.TYPE, ff.irep_method_name, fif.function_id, ff.function_name, fic.class_name, irep_name, fif.description
    FROM fnd_form_functions_vl ff, fnd_irep_function_flavors fif, fnd_irep_classes fic
   WHERE 1 = 1
--and fif.function_id = 49293
     AND fif.function_id = ff.function_id
     AND ff.irep_class_id = fic.class_id
     AND function_name LIKE 'PLSQL%UPDATE_PERSON_ADDRESS'
     AND (irep_name LIKE 'HR%' OR irep_name LIKE 'PER%' OR irep_name LIKE 'PAY%')
ORDER BY 4

The below script would list down all HRMS APIs of PLSQL type and their mandatory parameters, this can be used to build the basic skeleton for your custom wrapper programs.

SELECT   irep_name || '.' || ff.irep_method_name api, fp.param_name, fp.param_direction,
         fp.param_optional, fp.DEFAULT_VALUE, fp.parameter_type
    FROM fnd_form_functions_vl ff,
         fnd_irep_function_flavors fif,
         fnd_irep_classes fic,
         fnd_parameters fp
   WHERE 1 = 1
--     AND fif.function_id = 49293                                                             --49333
     AND fif.function_id = ff.function_id
     AND ff.irep_class_id = fic.class_id
     AND function_name LIKE 'PLSQL%'
     AND (irep_name LIKE 'HR%' OR irep_name LIKE 'PER%' OR irep_name LIKE 'PAY%')
     AND fp.function_id = ff.function_id
     AND param_optional = 'N'
ORDER BY 1, param_sequence

The above script can be modified to list down all HRMS APIs of PLSQL type and the entire list of parameters which could run to lots of rows in your excel export. This can be done by commenting out the param_optional column.