Absent Member.
Absent Member.

Way to transfer the managed Saved Searches to other users?




    Can you please help me how to figure out a way to transfer some of my saved PPM queries to other users in my team.






Naveen Kumar.

Tags (1)
2 Replies
Vice Admiral
Vice Admiral

You can create the procedure below, then call it with the 4 parameters (Current Saved Search Name, New Saved Search Name, Current Username, New UserName) .  So, using this you can


1) Copy it to another user by using the format CopySearch('Saved Search Name', 'Saved Search Name', 'Current Username', 'New Username')

2) Make a copy for yourself by using the format CopySearch('Saved Search Name', 'New Search Name', 'Current Username', 'Current Username')


PROCEDURE CopySearch(from_search_name IN VARCHAR2, to_search_name IN VARCHAR2, from_user IN VARCHAR2, to_user IN VARCHAR2) IS

v_new_search_id NUMBER;
v_current_search_id NUMBER;
v_new_search_filter_id NUMBER;
v_max_seq NUMBER;
v_from_userid NUMBER;
v_to_userid NUMBER;

SELECT user_id INTO v_from_userid FROM knta_users WHERE upper(username) LIKE upper(from_user);
SELECT user_id INTO v_to_userid FROM knta_users WHERE upper(username) LIKE upper(to_user);
SELECT saved_search_id INTO v_current_search_id FROM knta_saved_searches WHERE upper(saved_search_name) = upper(from_search_name);
SELECT MAX(saved_search_id)+1 INTO v_new_search_id FROM knta_saved_searches;
SELECT MAX(seq)+1 into v_max_seq FROM knta_saved_searches where created_by = v_from_userid;
INSERT INTO knta_saved_searches
SELECT v_new_search_id saved_search_id, SYSDATE creation_date, v_to_userid created_by, SYSDATE last_update_date, v_to_userid last_udated_by,
to_search_name saved_search_name, search_category_id, v_max_seq seq, entity_id, search_type_id
FROM knta_saved_searches WHERE created_by = v_from_userid AND saved_search_id=v_current_search_id
GROUP BY search_category_id, seq, entity_id, search_type_id;

SELECT MAX(saved_search_filter_id)+1 INTO v_new_search_filter_id FROM knta_saved_search_filters;
FOR f IN (SELECT * FROM knta_saved_search_filters WHERE saved_search_id = v_current_search_id)
f.saved_search_filter_id := v_new_search_filter_id;
f.creation_date := SYSDATE;
f.created_by := v_to_userid;
f.last_update_date := SYSDATE;
f.last_updated_by := v_to_userid;
f.saved_search_id := v_new_search_id;
INSERT INTO knta_saved_search_filters VALUES f;
v_new_search_filter_id := v_new_search_filter_id + 1;
END CopySearch;

-- Remember to give Kudos to answers! (click the KUDOS star)
Vice Admiral
Vice Admiral

Derek - that is awesome.

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.