Scribd
Upload
679 views

FND Lookup Values API

This document provides an example script for inserting lookup values into Oracle's standard FND_LOOKUP_VALUES and FND_LOOKUP_TYPES tables using the FND_LOOKUP_VALUES_PKG and FND_LOOKUP_TYPES_PKG APIs. The script loops through country names from a table and inserts each one as a new lookup value for a "Country" lookup type. It also shows an example of inserting a new lookup type and value record directly through the APIs.

Uploaded by

mrabdulbaqi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
679 views

FND Lookup Values API

This document provides an example script for inserting lookup values into Oracle's standard FND_LOOKUP_VALUES and FND_LOOKUP_TYPES tables using the FND_LOOKUP_VALUES_PKG and FND_LOOKUP_TYPES_PKG APIs. The script loops through country names from a table and inserts each one as a new lookup value for a "Country" lookup type. It also shows an example of inserting a new lookup type and value record directly through the APIs.

Uploaded by

mrabdulbaqi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

API to Create Lookup Values - "FND_LOOKUP_VALUES"

Note:
Before you start running the Below code, Kindly note we have only inserted recor
ds for Language as "US". If one has dual language, write a nested Loop to insert
the same.
DECLARE
CURSOR get_lookup_details
IS
SELECT ltype.application_id,
ltype.customization_level,
ltype.creation_date,
ltype.created_by,
ltype.last_update_date,
ltype.last_updated_by,
ltype.last_update_login,
tl.lookup_type,
tl.security_group_id,
tl.view_application_id,
tl.description,
tl.meaning
FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype
WHERE
ltype.lookup_type = 'XX_COUNTRY'
AND ltype.lookup_type = tl.lookup_type
AND language = 'US';
CURSOR get_country
IS
SELECT UPPER (country_name) country FROM xx_country;
l_rowid VARCHAR2 (100) := 0;
BEGIN
FOR i IN get_lookup_details
LOOP
FOR j IN get_country
LOOP
l_rowid := NULL;
BEGIN
fnd_lookup_values_pkg.insert_row (
x_rowid
=> l_rowid,
x_lookup_type
=> i.lookup_type,
x_security_group_id
=> i.security_group_id,
x_view_application_id => i.view_application_id,
x_lookup_code
=> j.country,
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
x_attribute8
x_attribute9
x_attribute10
x_attribute11
x_attribute12
x_attribute13
x_attribute14
x_attribute15
x_meaning
x_description
x_creation_date
x_created_by
x_last_update_date
x_last_updated_by
x_last_update_login

=>
=>
=>
=>
=>
=>
=>
=>
=>
=>
=>
=>
=>
=>
=>
=>

NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
j.country,
NULL,
SYSDATE,
i.created_by,
i.last_update_date,
i.last_updated_by,
i.last_update_login

);
COMMIT;
DBMS_OUTPUT.put_line (j.country || ' has been loaded');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
END;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
-----------------------------------------------------------------------------------------Creating Lookup and Lookup Values through API
The custom lookup parameters can be passed to the below API
acle standard tables fnd_lookup_values and fnd_lookup_types

s to insert data into Or

*fnd_lookup_types_pkg.insert_row
*fnd_lookup_values_pkg.insert_row
Sample script with description to upload the lookup and lookup values is explain
ed below
fnd_lookup_types_pkg.insert_row
(x_rowid
=> ln_rowid,
x_lookup_type
=> 'XXTEST',--Lookup Type
x_security_group_id
=> 0,
--Security Group Id
x_view_application_id
=> 0,
-- Application Id (0-AOL)
x_application_id
=> 0,
-- Application Id (0-AOL)
x_customization_level
=> 'U',
--User
x_meaning
=> 'XXTEST',--Meaning for Lookup
x_description
=> 'XXTEST',--Description for Lookup
x_creation_date
=> SYSDATE,
x_created_by
=> -1,
x_last_update_date
=> SYSDATE,
x_last_updated_by
=> -1,
x_last_update_login
=> -1
);

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',--Lookup Meaning
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
);

You might also like