How to find the current item_id

Products

• CA Service Catalog
• CA Service Management - Asset Portfolio Management
• CA 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.