Oracle apps api insert lookup : How to Create and Insert Lookup values through API

Oracle apps api insert lookup : How to Create and Insert Lookup values

Here in this post , we will discuss about the API to create and insert the Lookup and Lookup values in oracle apps. Lookups are very important in Oracle. Oracle applications has provided many standard Lookups for each module that are referring in the different forms of the applications. For Example , If We want to add create new pay group for AP invoices then , Oracle has already provided the standard lookup where we can see all the existing values of the Supplier Pay Group and also can create the New Pay Group by Adding the Values in that Lookup. So this is all about the standard Lookups. but if we have an Requirment to use the Custom Lookups to be uses in the Integration or in the Oracle reports then we can also create it in Oracle apps. Oracle has also provided the Standard API , to create and Manage the Lookups values in oracle and that I will discuss in this post.

 

 

2 Types of Lookups Available in Oracle apps

1.Standard Lookup

2.Custom Lookup

 

Standard Lookup :-

As I said , Oracle has also provided the standard lookups , Which Oracle is referring in their Oracle Forms , Reports and interfaces. We can also add some new values in these Lookups as per need but its not advisable to change the existing values and the Lookups Registrations Information's.

Custom Lookup :- 

When we have an requirement to completely new lookup with different name and we cannot use the existing one then we can also create the new lookup and insert the values as per our Requirment.

 

Important Components of Lookups

Lookup is divided in two Parts

1.Header

2.Line

 

In Header , We just has Lookup name and Description about the Lookup.

But in the Lines we can add the List of Values for this Lookup.

 

 

Oracle apps api to create and Insert Lookup Value

We have Two API's in Oracle Apps to Create and Insert the Lookup value.

 

For Lookup Creation Use this Below API

 

fnd_lookup_types_pkg.insert_row

 

For Lookup Values Creation Use this Below API

 

fnd_lookup_values_pkg.insert_row

 

 

API to Create the Lookup in Oracle

 

Declare
l_rowid number;
v_lookup_type varchar2(400):= 'XXAPSOURCE_LOOKUP'; -- Name of the Lookup---
v_lookup_meaning varchar2(400):= 'XXAPSOURCE_LOOKUP'; -- Meaning of the Lookup---
v_lookup_DESC varchar2(400):= 'XXAPSOURCE_LOOKUP'; -- Description of the Lookup---

Begin

fnd_lookup_types_pkg.insert_row
(x_rowid                  => l_rowid,                               

x_lookup_type             => v_lookup_type,

x_meaning                 => v_lookup_meaning ,

x_description             => v_lookup_DESC,

x_security_group_id       => 0,       --Security Group Id

x_view_application_id     => 0,       -- Application Id 0 for AOL----

x_application_id          => 0,       -- Application Id 0 for AOL----

x_customization_level     => 'U',     --User

x_creation_date           => SYSDATE,

x_created_by              => -1,

x_last_update_date        => SYSDATE,

x_last_updated_by         => -1,

x_last_update_login       => -1
);
end;

 

 

API to Create the Lookup Values in Oracle

Declare
l_rowid1 number;
v_lookup_type varchar2(400):= 'XXAPSOURCE_LOOKUP'; -- Name of the Lookup---
v_lookup_value_code varchar2(400):= 'External'; -- Value for Insert in the Lookup---
v_lookup_value_DESC varchar2(400):= 'System External Source'; -- Value Description of the Lookup---
begin
fnd_lookup_values_pkg.insert_row

 (x_rowid                   => l_rowid1,

 x_lookup_type              => v_lookup_type, 

 x_lookup_code              => v_lookup_value_code,

 x_meaning                  => v_lookup_value_DESC,

 x_security_group_id        => 0,

 x_view_application_id      => 0,

 x_enabled_flag             => 'Y',

 

 x_start_date_active        => TO_DATE ('01-JAN-1981','DD-MON-YYYY'),

 

 x_tag                      => NULL,

 x_attribute_category       => NULL,

 x_attribute1               => NULL,

 x_attribute2               => NULL,

 x_attribute3               => NULL,

 x_attribute4               => NULL,

 x_end_date_active          => NULL,

 x_territory_code           => NULL,

 x_attribute5               => NULL,

 x_description              => NULL,

 x_creation_date            => SYSDATE,

 x_created_by               => -1,

 x_last_update_date         => SYSDATE,

 x_last_updated_by          => -1,

 x_last_update_login        => -1

 );