Oracle HRMS : Location Address DFF
I am presenting the steps how to add 'Telangana' state in the Location Address
Finding the value set name
Navigation path
Flexfield->Descriptive->Segments->
Query for the <Location Address>
 |
Get the Value set Name
Query:
select * from
HR_LOOKUPS HL
WHERE LOOKUP_TYPE='IN_STATES'
Below the code to add 'Telangana' state
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 = 'IN_STATES'
AND ltype.lookup_type = tl.lookup_type
AND language = 'US';
l_rowid VARCHAR2 (100) := 0;
BEGIN
FOR i IN get_lookup_details
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 => 'TS',
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 ('02-JUN-2014',
'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 => 'Telangana',
x_description => NULL,
x_creation_date => SYSDATE,
x_created_by => i.created_by,
x_last_update_date => i.last_update_date,
x_last_updated_by => i.last_updated_by,
x_last_update_login => i.last_update_login
);
COMMIT;
DBMS_OUTPUT.put_line ('Telangana'|| ' has been loaded');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
Note :Start date needs to be '02-JUN-2014' as 'Telangana' has been formed on that particular date.
This is sample code only.You have to follow the client's guidelines while executing the above said program
