Script: Oracle Applications package to create and insert (FND) LOOKUP Types & Values

Below is a sample script to create lookup types in Oracle and also to load lookup values for the corresponding lookup types using standard Oracle APIs. I have used the FND Application ID to create lookups and customization level is user. There are many other ways to load data into lookups, we can also use FNDLOAD which is a very good option to migrate data lookups from one instance to another.

DECLARE
   ln_rowid    VARCHAR2 (1000);
   ln_rowid1   VARCHAR2 (1000);
BEGIN
   fnd_lookup_types_pkg.insert_row (x_rowid                    => ln_rowid,
                                    x_lookup_type              => 'XXTEST',
                                    x_security_group_id        => 0,
                                    x_view_application_id      => 0,
                                    x_application_id           => 0,
                                    x_customization_level      => 'U',
                                    x_meaning                  => 'XXTEST',
                                    x_description              => 'XXTEST',
                                    x_creation_date            => SYSDATE,
                                    x_created_by               => 0,
                                    x_last_update_date         => SYSDATE,
                                    x_last_updated_by          => 0,
                                    x_last_update_login        => -1
                                   );
   DBMS_OUTPUT.put_line (ln_rowid);
   fnd_lookup_values_pkg.insert_row (x_rowid                    => ln_rowid1,
                                     x_lookup_type              => 'XXTEST',
                                     x_security_group_id        => 0,
                                     x_view_application_id      => 0,
                                     x_lookup_code              => 'XXHR_INFO',
                                     x_tag                      => NULL,
                                     x_attribute_category       => NULL,
                                     x_attribute1               => NULL,
                                     x_attribute2               => NULL,
                                     x_attribute3               => NULL,
                                     x_attribute4               => NULL,
                                     x_enabled_flag             => 'Y',
                                     x_start_date_active        => TO_DATE ('01-JAN-1950',
                                                                            'DD-MON-YYYY'
                                                                           ),
                                     x_end_date_active          => NULL,
                                     x_territory_code           => NULL,
                                     x_attribute5               => NULL,
                                     x_attribute6               => NULL,
                                     x_attribute7               => NULL,
                                     x_attribute8               => NULL,
                                     x_attribute9               => NULL,
                                     x_attribute10              => NULL,
                                     x_attribute11              => NULL,
                                     x_attribute12              => NULL,
                                     x_attribute13              => NULL,
                                     x_attribute14              => NULL,
                                     x_attribute15              => NULL,
                                     x_meaning                  => 'XXHR Information',
                                     x_description              => NULL,
                                     x_creation_date            => SYSDATE,
                                     x_created_by               => 0,
                                     x_last_update_date         => SYSDATE,
                                     x_last_updated_by          => 0,
                                     x_last_update_login        => -1
                                    );
   DBMS_OUTPUT.put_line (ln_rowid1);
   COMMIT;
END;

HRMS Oracle Applications R12 - Change Start Date of a person

Oracle provides an API to change start dates of a person, the interesting fact is that this API even shifts the first assignment start dates. The API allows to shift the start dates within the range of first date track changes done at person & assignment level, if the new start date falls beyond the first date track change records effective end date then there is a error which pops up and does not allow changing the start date, this is logical and relevant.

APP-PAY-06841: Person changes exist between the old date and the new date

ORA-20001: Person changes exist between the old date and the new date.
ORA-06512: at "APPS.HR_CHANGE_START_DATE_API", line 3068

If you still want to change the start date, then all person and assignment changes should be removed from the system before trying to change the start date.

Below is the API which helps updating the start date

   DECLARE
      l_warn_ee   VARCHAR2 (100);
   BEGIN
      hr_change_start_date_api.update_start_date (p_validate            => FALSE,
                                                  p_person_id           => 8424,
                                                  p_old_start_date      => TO_DATE ('01-01-2000','MM-DD-YYYY'),
                                                  p_new_start_date      => TO_DATE ('01-01-2001','MM-DD-YYYY'),
                                                  p_update_type         => 'E',
                                                  p_warn_ee             => l_warn_ee
                                                 );
      DBMS_OUTPUT.put_line (l_warn_ee);
   END;

Oracle Applications R12 HRMS Functionality: Quick tips to start with Documents of Record

The below steps would help anyone to kick start the addition of documents to Documents of Records functionality in Oracle HRMS. I will be posting some codes to do it from the backend as well.

1. Create new / use Document Category in the existing HRMS Lookup
a. Navigate to Other Definitions > Application Utilities Lookups
b. Query for the lookup type DOCUMENT_CATEGORY
c. Create a new lookup meaning and description or use the existing ones

2. Create Subcategory for the Category created
a. Navigate to Other Definitions > User Types and Statuses
b. Enter the Name, DOCUMENT_CATEGORY and the business group you want use this Category
c. Enter the System Type (Document Category) defined in step 1
d. Enter the Description & Code which would form the subcategory for your documents

3. Register document type by running the program Register Document Types (EITs)
a. Navigate to Processes and Reports > Submit Processes and Reports > Register Document Types (EITs)
b. Enter Document Type parameter, this program creates & registers the document type with this name
c. Multiple Rows - Yes, Country Code - NULL, Description - <Document Type Description>
d. Category Code - Choose the category that was defined in step 1 and configured with subcategory code in step 2
e. Subcategory Code - Choose the subcategory code defined in step 2
d. Authorization Required - No, Warning Period - NULL and submit the program

4. Add the registered Document Type to the responsibility that requires this document type access
a. Navigate to Security > Information Types Security
b. Query for the responsibility you want to add this document type to
c. Add the document type to the list.

You are good now to add documents to Documents of Record under this document type, category & sub category.

Data Structures Revisited - Moving back to basics - Traversing a graph

The crux of writing this post was that I was posed a question recently which made me go to my books and re-read, re-work, re-learn or whatever you name it. The question was how would you traverse a graph with cyclic nodes to it, basically a cyclic graph, for which I did a quick visio and I have posted below.

The answer was depth first search and breadth first search, above are the results with the graph.

Depth First Search - Stack
Traverse through all the connected nodes, add to stack. Revisit node in stack when no more unvisited nodes
Result: ABEGFCHD

Breadth First Search - Queue
Traverse all the nodes attached to a vertex, add to the queue and then follow the next item in queue and make it as the vertex and reprocess
Result: ABDGEFCH

Tutorial: Creating WebService using Jdeveloper 11g for Starters

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.