07-20-2011 12:22 PM
We use validations to store a lot of information in PPM such as server names, application names, etc. The problem is validations do not sort alphabetically. You have to manually move the rows up or down to sort. We are running ver 7.5sp1. Does anyone know if ver 8 or 9 added enhancements to the validations to sort alphabetically? If not, has anyone run into this issue and have some suggestions on work arounds? We were thinking about sorting the rows (as in the query below) and then updating the table with the results. However, I am not sure how difficult this is. Any suggestions?
select seq,lookup_code,description from knta_lookups
where lookup_type like 'WINDOWS Prod%'
order by 3,2
07-20-2011 01:11 PM
We do something similar to what you are considering but we place it in its own validation with Component Type of Auto Complete List validated by SQL - Custom. This way the list is populated real time from the contents of the dropdown validation.
07-20-2011 01:26 PM
I second Jim: create a new Auto Complete List and plop your SQL in that, ordered however you like. Maintain the list of values in the original validation.
You also would typically do this so that you could use one validation that has a "enabled = true" clause for form field input, and a second validation for lookups that will show even the disabled values for reference so you can pick up requests that have field values that might not be currently enabled.
Incidentally, 9.1 includes a new tree view validation capability as well.
07-21-2011 03:42 AM
I think you are talking about the Drop Down list validation, in which the sequence is maintianed in the order of value entry.
In version 8.0 there is not such utility for sort, but you can acheive it by creating a new SQL list validation.
and then can select the sorted list by
SELECT lookup_code, meaning, default_flag FROM knta_lookups WHERE lookup_type = <List Validatio Name>' and enabled_flag = 'Y' ORDER BY Meaning
Hope this helps you.....
-- Remember to give Kudos to answers! (click the KUDOS star)
07-28-2011 11:35 AM
I posted a similar question just a few days ago. One of our local HP PPM contractors, Francois Schneider, provided this procedure example which is supposed to reorder the sequence of the rsc_location table.
- I have not tested this code so you may want to TEST it first and modify it to meet your needs
PROCEDURE refresh_rsc_location(p_lookup_type IN VARCHAR2) IS l_count NUMBER; TYPE listCodes IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; l_list listCodes; BEGIN -- Reorder the SEQ field SELECT lookup_code BULK COLLECT INTO l_list FROM knta_lookups_nls WHERE lookup_type = p_lookup_type ORDER BY meaning; IF l_list.FIRST IS NOT NULL THEN l_count := 0; FOR i IN l_list.FIRST..l_list.LAST LOOP l_count := l_count + 1; UPDATE knta_lookups_nls SET last_update_date = SYSDATE, seq = l_count WHERE lookup_type = p_lookup_type and lookup_code = l_list(i); END LOOP; COMMIT; END IF; END refresh_table_component; This is called with the code: refresh_rsc_location('RSC – Location');
11-15-2011 01:03 PM
With minor changes I was able to add the stored procedure listed here, refresh_table_component. Needed to make a couple of changes for the procedure to be successfully added:
1) PROCEDURE name needed to match END name. So PROCEDURE refresh_rsc_location became PROCEDURE refresh_table_component.
2) knta_lookups_nls does not exist in our database. References to table knta_lookups_nls were changed to knta_lookups.
Next when I ran the procedure, it did what I expected - the column seq was set according to the order by clause. However, in PPM, under validations, the order did not match. The row with seq=1 showed seq=5 in the validation list.
Am I missing something here?
11-15-2011 01:30 PM
Whenever you change something directly in the database tables, you need to be concerned about cached values. You can use the script kRunCacheManager.sh to clear one or all caches.
11-16-2011 12:21 AM
I would not recommend altering the rows in the database only for sorting purposes.
We always do it like this: we have a drop-down validation that contains the values, but we create extra auto-complete validations based on custom SQL in order to access the values.
--remember to kudos people who helped solve your problem
11-18-2011 09:32 AM
I agree that auto complete is the best way to sort validations when creating requests or packages. However the issue we face is how to manage the data in the validations. Example. We rely heavily on validations to store data - server names, application names, windows domain group names, etc. Some of the validations contain hundreds of entries. We will use one of the fields (code / meaning / description) to store the application name. We then sort that entry in the list alphabetically using the up/down arrows in the validation (a VERY painful process). That way all the entries for HR, as example, are grouped together This way when HR contacts us to add/modify or delete something for HR, we can find that entry in the validation and make the change. Otherwise, we would have to scroll thru all 200+ entries in the validation list. We could write a SQL query to find the rows, but that requires time and the skill set to do that. Part of our team is constantly making changes to the validations manually. They are not comfortable with SQL nor would I want them running updates to the tables.
How does everyone manage large lists of data required to fill in requests or packages? Example, we have a request form that requires the user to select an oracle role and database. We have about 75 different application teams each with their own role names & db names. We use the auto complete to sort & provide the user with an appropriate list of roles and DBs to choose from, depending upon their security groups. Does anyone have similar processes on their requests or packages? If so, how are you managing the data being used on the fields? It seems silly but I can use an external file and store all this information. Then using unix commands like grep, I can return the roles and DB information. However, I would prefer to use the build in tables in PPM to store this information. It doesnt seem right that I would need to store this information in a source external to PPM. By using external source, I also loose the ability to use tokens to resolve fields from the validations.
11-21-2011 12:46 AM
I haven't seen such an interesting post in a long time!
First of all: there is a best practice which says that specific data within the organization should have a single source of truth.
Therefore, it might be a good idea to integrate PPM with the IT Asset Management system you have (e.g. for servers, domain users, groups etc - you can even use the AD as a data source), and/or with the system that has the source data for the organizational chart (an HRMS, or some companies rely on AD to do this).
In this manner, you can integrate PPM with these systems in two ways:
1. offline integration - you can create batch jobs to synchronize data in PPM from the external system(s) at specified intervals.
2. online integration - you can have the field validations select directly from the external system using views over db links.
We have PPM implementations integrated as follows:
- using Oracle Identity Management, we bring in user information, we assign licenses and security groups;
- using Oracle Identity Management, we bring in organizational structure information and assign the appropriate users in order to match the company's organizational structure;
- we have integrated using the online method with Service Desk systems to be able to select and report based on data related to services, HW & SW systems and so on.
Having these approaches, we have:
- avoided the burden to maintain data for which PPM is not the core system;
- isolated PPM, in terms that we are using only the data that is published by the external system.
Please let me know if this helps.
--remember to kudos people who helped solve your problem