Re: Way to transfer the managed Saved Searches to other users? (216 Views)
Reply
Advisor
NaveenKumarV
Posts: 22
Registered: ‎03-11-2014
Message 1 of 3 (254 Views)

Way to transfer the managed Saved Searches to other users?

Hi,

 

 

    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.

 

 

 

 

Thanks,

Naveen Kumar.

Frequent Advisor
Derek Giedd
Posts: 59
Registered: ‎03-16-2010
Message 2 of 3 (216 Views)

Re: Way to transfer the managed Saved Searches to other users?

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')

 

CREATE OR REPLACE
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;

BEGIN
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)
LOOP
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 LOOP;
Commit;
END CopySearch;

-- Remember to give Kudos to answers! (click the KUDOS star)
Super Advisor
pepdwill
Posts: 220
Registered: ‎07-12-2012
Message 3 of 3 (193 Views)

Re: Way to transfer the managed Saved Searches to other users?

Derek - that is awesome.

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.