Inserting values into fnd_lookup_values by fnd_lookup_value_pkg


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

Table -> fnd_lookup_values

To insert into fnd_lookup_values  table which is oracle seeded we can use the API fnd_lookup_values_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’);

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

DECLARE

xrow rowid;

j number;

CURSOR C IS select LOOKUP_TYPE, meaning, lookup_code, DESCRIPTION  from

xx4i_fnd_lookup_values

where lookup_type like ‘XXHLP%’;

BEGIN

FOR i IN c

LOOP

dbms_output.put_line(i.LOOKUP_TYPE||i.lookup_code||i.meaning||i.DESCRIPTION);

— Call the procedure

fnd_lookup_values_pkg.insert_row(x_rowid               => xrow,

x_lookup_type         => i.LOOKUP_TYPE,

x_security_group_id   => 1,

x_view_application_id => 0,

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   => null,

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.meaning,

x_description         => i.DESCRIPTION,

x_creation_date       => sysdate,

x_created_by          => 1318,

x_last_update_date    => sysdate,

x_last_updated_by     => 1318,

x_last_update_login   => 1318);

END loop;

COMMIT;

END;

Thank you for Reading

About pacesettergraam

A good and realistic person

Posted on September 30, 2013, in OAF, Oracle, SQL/PLSQL 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: