Bulk Loading of Lookups in Oracle Apps


When you have some lookups you can insert it into oracle apps by going to lookups and insert ahead. But when you have data in a large quantity it is a duplication to type and insert. We have some fnd_lookup_values_pkg, fnd_lookup_types_pkg API’s to rescue

So now lets go Step by Step to insert

The Steps are

Step-1 :  To identify the UI(oracle forms) from where user can be able to visualize

Step – 2 Digging into query

Step – 3 Inserting data into oracle seeded table
 Step – 1  : To identify the UI(oracle forms) from where user can be able to visualise

The path for navigation is

Application Developer -> Application  – > Lookups -> Common and you will get a page as shown below

1

Now Just go to insert mode by pressing F11 and put % symbol to Query and press Ctrl+F11 to Query.

2.

Now you will have records populated. Just click a record and go to Help -> Record History

You will get the table/view name from where the data is populated.

3.

If suppose you are not able to view the record history then try this

Help -> Diagnostics -> Examine

Then you will get the below screen and set the LOV’s as shown

Block  : System

Field   : Last_query

4.

And in Value you can get the SQL query. With this we can start our hard way.

Step – 2 Digging into query

Now we have to dig down the Query View Scripts from where the data comes.

SELECT text

FROM all_views

WHERE view_name = ‘%FND_LOOKUP_TYPES_VL%’;

By the above query we can get the scripts for the view.

Now the scripts will give a query which has joins to tables by this we will get to a idea that where it fetches the data.

Now on analyzing we get data from fnd_lookup_types table and on analysing the values we get the data from  fnd_lookup_values table.

Step – 3 Inserting data into oracle seeded table

When the requirement is to insert your data into oracle seeded table you have two options recommended. One is by Interface and another by API.

Here we are going to see the values to be inserted by API for a particular table.

API -> fnd_lookup_values_pkg, fnd_lookup_types_pkg

Table -> fnd_lookup_values, fnd_lookup_types

To insert into fnd_lookup_values, fnd_lookup_types  table which is oracle seeded we can use the API  fnd_lookup_values_pkg, fnd_lookup_types_pkg

which is a package.

Note:

To view the contents of the package

1 If you use TOAD place the cursor in the pkg  and press F4

2 Use the Below Query to find the content

select * from user_source where name=upper(‘fnd_lookup_values_pkg’);

select * from user_source where name=upper(‘fnd_lookup_types_pkg’);

Below is the script for inserting data into table by using fnd_lookup_values _pkg

DECLARE

XROW     ROWID;  –You cant directly give Row Id to the x_rowid parameter,

ln_rowid1   ROWID;

cursor c is     select * from xx_dept; –My Custom Query to have fetch in my custom table

cursor c1 is

select distinct lookup_type from xx_dept;

BEGIN

for i in c1   — For loop for Inserting lookup types

loop

fnd_lookup_types_pkg.insert_row (x_rowid                    => XROW,

x_lookup_type              => i.lookup_type,  –cursor values

x_security_group_id        => 0,

x_view_application_id      => 3,

x_application_id           => 20187,

x_customization_level      => ‘U’,

x_meaning                  => i.lookup_type,

x_description              => i.lookup_type,

x_creation_date            => SYSDATE,

x_created_by               => 1318,

x_last_update_date         => SYSDATE,

x_last_updated_by          => 0,

x_last_update_login        => 5513095

);

DBMS_OUTPUT.put_line (XROW);

end loop;  –Loop ends here

commit;

for i in c — For loop for Inserting lookup values

loop

fnd_lookup_values_pkg.insert_row (x_rowid                    => ln_rowid1,

x_lookup_type              => I.LOOKUP_TYPE,  –Secomd cursor values

x_security_group_id        => 0,

x_view_application_id      => 3,

x_lookup_code              => I.LOOKUP_CODE,

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                  => I.LOOKUP_MEANING,

x_description              => NULL,

x_creation_date            => SYSDATE,

x_created_by               => 1318,

x_last_update_date         => SYSDATE,

x_last_updated_by          => 1318,

x_last_update_login        => 5513095

);

DBMS_OUTPUT.put_line (ln_rowid1);

end loop;

exception

when others then dbms_output.put_line(‘Exception Occured’);

commit;

END;

About pacesettergraam

A good and realistic person

Posted on November 15, 2013, in OAF, Oracle, SQL/PLSQL, Uncategorized and tagged , , , , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: