CA Service CatalogCA Service Management - Asset Portfolio ManagementCA Service Management - Service Desk Manager
Issue/Introduction
I need to find out the item_id of a specific service, so I can make a Web Service call involving it - how can I find that in the database?
Environment
Release: Component: USVCT
Resolution
We'll take one of the "out of the box" offerings, as an example, "Procure Desktop". There's nothing particularly special about it; it just has a few rate items to it and as an ootb item it will exist on any system by default. The query
select o.offering_id,o.offering_name, i.parent_id,i.rate_plan_id, i.child_id, rd.item_id,rd.rate_plan_id,rd.item_text from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd where o.offering_name = 'Procure Desktop' and i.parent_id = o.offering_id and rd.rate_plan_id = i.rate_plan_id and rd.status = 1 and i.child_id = rd.item_id
will return all the item_text information for every item_id associated with the given offering. However if all you're after is to get the list of item_id values for a given offering, then you can simplify this to:
select o.offering_id,o.offering_name, i.parent_id,i.rate_plan_id, i.child_id from usm_offering o,usm_offering_ratedef_inclusion i where o.offering_name = 'Procure Desktop' and i.parent_id = o.offering_id
replacing the offering_name with the one you need. There are a great many rate item IDs for each Service Offering; which one or ones you're after for any particular information is another question; this is where you need to use rate_row, rate_col, status and item_type columns in the usm_rate_definition table, so that bigger query may still be of use to you.